-- Connect to main database
\c dev_pipeline;

-- Projects table
CREATE TABLE projects (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    user_requirements TEXT NOT NULL,
    processed_requirements JSONB,
    technical_prd TEXT,
    architecture_type VARCHAR(50) DEFAULT 'monolithic',
    complexity_score INTEGER DEFAULT 1,
    status VARCHAR(50) DEFAULT 'initializing',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by VARCHAR(255),
    estimated_completion_time INTERVAL,
    actual_completion_time INTERVAL,
    git_repository_url VARCHAR(500),
    local_dev_url VARCHAR(500),
    staging_url VARCHAR(500),
    production_url VARCHAR(500),
    metadata JSONB DEFAULT '{}'::jsonb,
    
    CONSTRAINT valid_architecture_type CHECK (architecture_type IN ('monolithic', 'microservices', 'serverless')),
    CONSTRAINT valid_complexity_score CHECK (complexity_score >= 1 AND complexity_score <= 10)
);

-- Technology stack decisions
CREATE TABLE tech_stack_decisions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
    backend_framework VARCHAR(100),
    backend_language VARCHAR(50),
    backend_version VARCHAR(20),
    frontend_framework VARCHAR(100),
    frontend_language VARCHAR(50),
    frontend_version VARCHAR(20),
    primary_database VARCHAR(50),
    cache_database VARCHAR(50),
    search_database VARCHAR(50),
    containerization VARCHAR(50) DEFAULT 'docker',
    orchestration VARCHAR(50),
    cloud_provider VARCHAR(50) DEFAULT 'cloudtopiaa',
    message_queue VARCHAR(50),
    real_time_service VARCHAR(50),
    file_storage VARCHAR(50),
    decision_factors JSONB,
    ai_confidence_score DECIMAL(3,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT valid_confidence_score CHECK (ai_confidence_score >= 0.0 AND ai_confidence_score <= 1.0)
);

-- System architectures
CREATE TABLE system_architectures (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
    architecture_type VARCHAR(50) NOT NULL,
    services JSONB,
    databases JSONB,
    apis JSONB,
    ui_components JSONB,
    infrastructure_components JSONB,
    deployment_strategy JSONB,
    scaling_strategy JSONB,
    security_design JSONB,
    architecture_diagram_url VARCHAR(500),
    component_diagram_url VARCHAR(500),
    database_schema_url VARCHAR(500),
    api_documentation_url VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by VARCHAR(100) DEFAULT 'ai_architect'
);

-- Code generations
CREATE TABLE code_generations (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
    architecture_id UUID REFERENCES system_architectures(id) ON DELETE CASCADE,
    generation_type VARCHAR(50) NOT NULL,
    framework VARCHAR(100),
    language VARCHAR(50),
    component_name VARCHAR(255),
    file_path VARCHAR(1000),
    generated_code TEXT,
    prompt_used TEXT,
    ai_model_used VARCHAR(100),
    generation_metadata JSONB,
    status VARCHAR(50) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT valid_generation_type CHECK (generation_type IN ('backend', 'frontend', 'database', 'infrastructure', 'tests'))
);

-- Test results
CREATE TABLE test_results (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    code_generation_id UUID REFERENCES code_generations(id) ON DELETE CASCADE,
    test_type VARCHAR(50) NOT NULL,
    test_framework VARCHAR(100),
    test_output TEXT,
    passed BOOLEAN DEFAULT FALSE,
    coverage_percentage DECIMAL(5,2),
    performance_metrics JSONB,
    executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    execution_time_ms INTEGER,
    
    CONSTRAINT valid_test_type CHECK (test_type IN ('unit', 'integration', 'e2e', 'performance', 'security'))
);

-- Deployment logs
CREATE TABLE deployment_logs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
    environment VARCHAR(50) NOT NULL,
    deployment_type VARCHAR(50),
    status VARCHAR(50),
    log_output TEXT,
    deployment_config JSONB,
    deployed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deployment_url VARCHAR(500),
    rollback_url VARCHAR(500),
    
    CONSTRAINT valid_environment CHECK (environment IN ('local', 'development', 'staging', 'production'))
);

-- Service health monitoring
CREATE TABLE service_health (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    service_name VARCHAR(100) NOT NULL,
    status VARCHAR(20) DEFAULT 'unknown',
    last_health_check TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    response_time_ms INTEGER,
    error_message TEXT,
    metadata JSONB,
    
    CONSTRAINT valid_status CHECK (status IN ('healthy', 'unhealthy', 'unknown', 'starting'))
);

-- Project state transitions (for audit trail)
CREATE TABLE project_state_transitions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
    from_state VARCHAR(50),
    to_state VARCHAR(50),
    transition_reason TEXT,
    transition_data JSONB,
    transitioned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    transitioned_by VARCHAR(255)
);

-- Create indexes for performance
CREATE INDEX idx_projects_status ON projects(status);
CREATE INDEX idx_projects_created_at ON projects(created_at);
CREATE INDEX idx_tech_stack_project_id ON tech_stack_decisions(project_id);
CREATE INDEX idx_system_arch_project_id ON system_architectures(project_id);
CREATE INDEX idx_code_gen_project_id ON code_generations(project_id);
CREATE INDEX idx_code_gen_status ON code_generations(status);
CREATE INDEX idx_test_results_code_gen_id ON test_results(code_generation_id);
CREATE INDEX idx_deployment_logs_project_id ON deployment_logs(project_id);
CREATE INDEX idx_service_health_name ON service_health(service_name);
CREATE INDEX idx_state_transitions_project_id ON project_state_transitions(project_id);

-- Insert initial data
INSERT INTO service_health (service_name, status) VALUES 
('api-gateway', 'unknown'),
('requirement-processor', 'unknown'),
('tech-stack-selector', 'unknown'),
('architecture-designer', 'unknown'),
('code-generator', 'unknown'),
('test-generator', 'unknown'),
('deployment-manager', 'unknown');

-- Create sample project for testing
INSERT INTO projects (name, description, user_requirements, status, created_by) VALUES 
('Sample TODO App', 'A simple todo application for testing the pipeline', 
 'I want to create a simple todo application where users can add, edit, delete and mark tasks as complete. Users should be able to register and login.', 
 'initializing', 'system_test');