-- Enable foreign keys for all connections
PRAGMA foreign_keys = ON;

-- Projects table - core entity for lifecycle management
CREATE TABLE IF NOT EXISTS projects (
    id TEXT PRIMARY KEY NOT NULL,
    name TEXT NOT NULL,
    version TEXT NOT NULL,
    description TEXT NOT NULL,
    project_type TEXT NOT NULL DEFAULT 'MultiLang',
    current_phase TEXT NOT NULL DEFAULT 'Creation',
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

-- Checklist items - tasks for each phase of a project
CREATE TABLE IF NOT EXISTS checklist_items (
    id TEXT PRIMARY KEY NOT NULL,
    project_id TEXT NOT NULL,
    phase TEXT NOT NULL,
    task_id TEXT NOT NULL,
    description TEXT NOT NULL,
    completed BOOLEAN NOT NULL DEFAULT 0,
    completed_at TEXT,
    created_at TEXT NOT NULL,
    task_type TEXT DEFAULT '',
    task_priority TEXT DEFAULT '',
    task_due TEXT,
    task_estimation TEXT,
    task_deps TEXT DEFAULT '',
    task_note TEXT,
    task_name TEXT,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

-- Phase transitions - audit trail of phase changes
CREATE TABLE IF NOT EXISTS phase_transitions (
    id TEXT PRIMARY KEY NOT NULL,
    project_id TEXT NOT NULL,
    from_phase TEXT NOT NULL,
    to_phase TEXT NOT NULL,
    timestamp TEXT NOT NULL,
    reason TEXT,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

-- Security assessments - security scan results
CREATE TABLE IF NOT EXISTS security_assessments (
    id TEXT PRIMARY KEY NOT NULL,
    project_id TEXT NOT NULL,
    profile TEXT NOT NULL,
    risk_level TEXT NOT NULL,
    passed_rules INTEGER NOT NULL,
    failed_rules INTEGER NOT NULL,
    critical_issues INTEGER NOT NULL,
    assessment_date TEXT NOT NULL,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);
