-- Initial schema for syntaxis manager

-- Projects table
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
);

-- Create index on project ID for faster lookups
CREATE INDEX IF NOT EXISTS idx_projects_created ON projects(created_at DESC);

-- Checklist items table
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,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

-- Create indices for checklist queries
CREATE INDEX IF NOT EXISTS idx_checklist_project ON checklist_items(project_id);
CREATE INDEX IF NOT EXISTS idx_checklist_phase ON checklist_items(project_id, phase);
CREATE INDEX IF NOT EXISTS idx_checklist_completed ON checklist_items(completed);

-- Phase transitions table
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
);

-- Create indices for phase queries
CREATE INDEX IF NOT EXISTS idx_transitions_project ON phase_transitions(project_id);
CREATE INDEX IF NOT EXISTS idx_transitions_timestamp ON phase_transitions(project_id, timestamp DESC);

-- Security assessments table
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
);

-- Create indices for security queries
CREATE INDEX IF NOT EXISTS idx_assessments_project ON security_assessments(project_id);
CREATE INDEX IF NOT EXISTS idx_assessments_date ON security_assessments(project_id, assessment_date DESC);

-- Tool configurations table
CREATE TABLE IF NOT EXISTS tool_configurations (
    id TEXT PRIMARY KEY NOT NULL,
    project_id TEXT NOT NULL,
    tool_name TEXT NOT NULL,
    enabled BOOLEAN NOT NULL DEFAULT 0,
    config_data TEXT,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

-- Create indices for tool queries
CREATE INDEX IF NOT EXISTS idx_tools_project ON tool_configurations(project_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_tools_unique ON tool_configurations(project_id, tool_name);

-- Activity log table
CREATE TABLE IF NOT EXISTS activity_logs (
    id TEXT PRIMARY KEY NOT NULL,
    project_id TEXT NOT NULL,
    action TEXT NOT NULL,
    description TEXT,
    timestamp TEXT NOT NULL,
    user_id TEXT,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

-- Create indices for activity queries
CREATE INDEX IF NOT EXISTS idx_activity_project ON activity_logs(project_id);
CREATE INDEX IF NOT EXISTS idx_activity_timestamp ON activity_logs(project_id, timestamp DESC);

-- Backup history table
CREATE TABLE IF NOT EXISTS backup_history (
    id TEXT PRIMARY KEY NOT NULL,
    project_id TEXT NOT NULL,
    backup_path TEXT NOT NULL,
    created_at TEXT NOT NULL,
    restored_at TEXT,
    reason TEXT,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

-- Create indices for backup queries
CREATE INDEX IF NOT EXISTS idx_backups_project ON backup_history(project_id);
CREATE INDEX IF NOT EXISTS idx_backups_created ON backup_history(created_at DESC);
