-- Projects table
CREATE TABLE user_projects (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID,
    project_name VARCHAR(255) NOT NULL,
    project_type VARCHAR(100) NOT NULL,
    description TEXT,
    selected_features JSONB,
    ai_analysis JSONB,
    status VARCHAR(50) DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Custom categories table
CREATE TABLE custom_categories (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID,
    category_name VARCHAR(255) NOT NULL,
    description TEXT,
    icon VARCHAR(10),
    features JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Feature analysis cache
CREATE TABLE feature_analysis_cache (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    feature_description_hash VARCHAR(64) UNIQUE,
    project_type VARCHAR(100),
    ai_analysis JSONB,
    confidence_score DECIMAL(3,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);