-- Atlas SQLite Database Schema
-- Version: 1.0.0

-- Core project management
CREATE TABLE IF NOT EXISTS projects (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT,
    config TEXT NOT NULL DEFAULT '{}',
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch())
);

-- Agile Management Tables
CREATE TABLE IF NOT EXISTS agile_sprints (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    name TEXT NOT NULL,
    goal TEXT,
    status TEXT NOT NULL DEFAULT 'planning',
    start_date INTEGER,
    end_date INTEGER,
    duration INTEGER DEFAULT 14,
    team TEXT DEFAULT '[]',
    story_points_planned INTEGER DEFAULT 0,
    story_points_completed INTEGER DEFAULT 0,
    stories_total INTEGER DEFAULT 0,
    stories_completed INTEGER DEFAULT 0,
    velocity INTEGER DEFAULT 0,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS agile_stories (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    sprint_id TEXT,
    epic_id TEXT,
    title TEXT NOT NULL,
    description TEXT,
    status TEXT NOT NULL DEFAULT 'todo',
    story_points INTEGER DEFAULT 0,
    priority TEXT NOT NULL DEFAULT 'medium',
    assignee TEXT,
    tags TEXT DEFAULT '[]',
    acceptance_criteria TEXT DEFAULT '[]',
    design_document_url TEXT,
    implementation_document_url TEXT,
    documentation_status TEXT DEFAULT 'pending',
    documentation_last_reviewed INTEGER,
    documentation_reviewers TEXT DEFAULT '[]',
    groomed_with_user_feedback BOOLEAN DEFAULT FALSE,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (sprint_id) REFERENCES agile_sprints(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS agile_epics (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    title TEXT NOT NULL,
    description TEXT,
    goals TEXT DEFAULT '[]',
    owner TEXT,
    status TEXT NOT NULL DEFAULT 'planning',
    story_points INTEGER DEFAULT 0,
    priority TEXT NOT NULL DEFAULT 'medium',
    target_date INTEGER,
    target_quarter TEXT,
    repositories TEXT DEFAULT '[]',
    business_value TEXT,
    success_criteria TEXT DEFAULT '[]',
    dependencies TEXT DEFAULT '[]',
    risks TEXT DEFAULT '[]',
    completion_notes TEXT,
    business_value_realized TEXT,
    closed_by TEXT,
    closed_at INTEGER,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

-- Agile Story Comments for collaboration
CREATE TABLE IF NOT EXISTS agile_story_comments (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    story_id TEXT NOT NULL,
    author TEXT NOT NULL,
    content TEXT NOT NULL,
    thread_id TEXT,
    parent_comment_id TEXT,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (story_id) REFERENCES agile_stories(id) ON DELETE CASCADE,
    FOREIGN KEY (parent_comment_id) REFERENCES agile_story_comments(id) ON DELETE CASCADE
);

-- Agile Story Blockers for workflow management
CREATE TABLE IF NOT EXISTS agile_story_blockers (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    blocked_story_id TEXT NOT NULL,
    blocker_story_id TEXT,
    blocker_type TEXT NOT NULL DEFAULT 'dependency', -- dependency, external, technical, etc.
    reason TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'active', -- active, resolved
    resolution TEXT,
    resolved_by TEXT,
    resolved_at INTEGER,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (blocked_story_id) REFERENCES agile_stories(id) ON DELETE CASCADE,
    FOREIGN KEY (blocker_story_id) REFERENCES agile_stories(id) ON DELETE SET NULL
);

-- Sprint Completion Tracking
CREATE TABLE IF NOT EXISTS agile_sprint_completions (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    sprint_id TEXT NOT NULL,
    carryover_stories TEXT DEFAULT '[]', -- JSON array of story IDs
    completion_notes TEXT,
    velocity_achieved INTEGER DEFAULT 0,
    story_points_completed INTEGER DEFAULT 0,
    stories_completed INTEGER DEFAULT 0,
    stories_carried_over INTEGER DEFAULT 0,
    completed_by TEXT,
    completed_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (sprint_id) REFERENCES agile_sprints(id) ON DELETE CASCADE
);

-- Kanban Board Management
CREATE TABLE IF NOT EXISTS kanban_boards (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    name TEXT NOT NULL,
    description TEXT,
    columns TEXT NOT NULL DEFAULT '[]',
    workflow_type TEXT NOT NULL DEFAULT 'simple',
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS kanban_tasks (
    id TEXT PRIMARY KEY,
    board_id TEXT NOT NULL,
    project_id TEXT NOT NULL,
    title TEXT NOT NULL,
    description TEXT DEFAULT '',
    assigned_to TEXT,
    priority TEXT NOT NULL DEFAULT 'medium',
    tags TEXT DEFAULT '[]',
    due_date INTEGER,
    story_points INTEGER,
    column_name TEXT NOT NULL,
    position INTEGER NOT NULL,
    status TEXT NOT NULL DEFAULT 'active',
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (board_id) REFERENCES kanban_boards(id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

-- Documentation Management
CREATE TABLE IF NOT EXISTS documents (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    type TEXT NOT NULL DEFAULT 'markdown',
    path TEXT,
    tags TEXT DEFAULT '[]',
    status TEXT NOT NULL DEFAULT 'draft',
    author TEXT,
    version INTEGER DEFAULT 1,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

-- Human Approval System
CREATE TABLE IF NOT EXISTS approval_requests (
    id TEXT PRIMARY KEY,
    action TEXT NOT NULL,
    context TEXT NOT NULL,
    description TEXT,
    urgency TEXT NOT NULL DEFAULT 'medium',
    status TEXT NOT NULL DEFAULT 'pending',
    requested_by TEXT,
    requested_at INTEGER NOT NULL DEFAULT (unixepoch()),
    resolved_by TEXT,
    resolved_at INTEGER,
    timeout_at INTEGER,
    response_data TEXT,
    notes TEXT
);

-- Memory Management
CREATE TABLE IF NOT EXISTS memories (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    title TEXT,
    type TEXT NOT NULL,
    content TEXT NOT NULL,
    tags TEXT DEFAULT '[]',
    metadata TEXT DEFAULT '{}',
    importance TEXT DEFAULT 'medium',
    category TEXT,
    source TEXT,
    created_by TEXT,
    embedding_vector TEXT,
    similarity_score REAL,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

-- Business Guidance
CREATE TABLE IF NOT EXISTS business_plans (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    plan_type TEXT NOT NULL,
    content TEXT NOT NULL,
    metadata TEXT DEFAULT '{}',
    status TEXT NOT NULL DEFAULT 'draft',
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS market_analyses (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    industry TEXT NOT NULL,
    target_market TEXT NOT NULL,
    geographic_scope TEXT NOT NULL DEFAULT 'global',
    market_size TEXT,
    growth_rate TEXT,
    trends TEXT DEFAULT '[]', -- JSON array
    opportunities TEXT DEFAULT '[]', -- JSON array
    challenges TEXT DEFAULT '[]', -- JSON array
    recommendations TEXT DEFAULT '[]', -- JSON array
    competitors TEXT DEFAULT '[]', -- JSON array
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS competitor_analyses (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    industry TEXT NOT NULL,
    business_idea TEXT NOT NULL,
    competition_level TEXT,
    competitors TEXT DEFAULT '[]', -- JSON array
    gaps TEXT DEFAULT '[]', -- JSON array
    opportunities TEXT DEFAULT '[]', -- JSON array
    recommendations TEXT DEFAULT '[]', -- JSON array
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS financial_projections (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    business_model TEXT NOT NULL,
    timeline INTEGER NOT NULL,
    currency TEXT NOT NULL DEFAULT 'USD',
    revenue TEXT DEFAULT '{}', -- JSON object
    expenses TEXT DEFAULT '{}', -- JSON object
    net_income TEXT DEFAULT '{}', -- JSON object
    cash_flow TEXT DEFAULT '{}', -- JSON object
    break_even_month INTEGER,
    total_investment_needed REAL,
    roi REAL,
    scenarios TEXT, -- JSON object
    assumptions TEXT DEFAULT '[]', -- JSON array
    recommendations TEXT DEFAULT '[]', -- JSON array
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS startup_assessments (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    stage TEXT NOT NULL,
    score INTEGER NOT NULL,
    categories TEXT DEFAULT '[]', -- JSON array
    strengths TEXT DEFAULT '[]', -- JSON array
    weaknesses TEXT DEFAULT '[]', -- JSON array
    next_steps TEXT DEFAULT '[]', -- JSON array
    stage_recommendations TEXT DEFAULT '[]', -- JSON array
    overall TEXT,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS pitch_decks (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    business_idea TEXT NOT NULL,
    template TEXT NOT NULL,
    slides TEXT DEFAULT '[]', -- JSON array
    presentation_tips TEXT DEFAULT '[]', -- JSON array
    key_messages TEXT DEFAULT '[]', -- JSON array
    markdown TEXT NOT NULL,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS startup_metrics (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    metrics_type TEXT NOT NULL,
    current_metrics TEXT DEFAULT '{}', -- JSON object
    goals TEXT DEFAULT '{}', -- JSON object
    timeframe TEXT NOT NULL,
    trends TEXT DEFAULT '[]', -- JSON array
    benchmarks TEXT DEFAULT '[]', -- JSON array
    recommendations TEXT DEFAULT '[]', -- JSON array
    health_score INTEGER,
    alerts TEXT DEFAULT '[]', -- JSON array
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS business_reviews (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    business_name TEXT NOT NULL,
    current_stage TEXT NOT NULL,
    overall_health_score INTEGER NOT NULL,
    strengths TEXT DEFAULT '[]', -- JSON array
    gaps TEXT DEFAULT '[]', -- JSON array
    strategic_paths TEXT DEFAULT '[]', -- JSON array
    immediate_actions TEXT DEFAULT '[]', -- JSON array
    data_collected TEXT DEFAULT '{}', -- JSON object
    review_date TEXT NOT NULL,
    next_review_date TEXT NOT NULL,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_market_analyses_project ON market_analyses(project_id);
CREATE INDEX IF NOT EXISTS idx_competitor_analyses_project ON competitor_analyses(project_id);
CREATE INDEX IF NOT EXISTS idx_financial_projections_project ON financial_projections(project_id);
CREATE INDEX IF NOT EXISTS idx_startup_assessments_project ON startup_assessments(project_id);
CREATE INDEX IF NOT EXISTS idx_pitch_decks_project ON pitch_decks(project_id);
CREATE INDEX IF NOT EXISTS idx_startup_metrics_project ON startup_metrics(project_id);
CREATE INDEX IF NOT EXISTS idx_business_reviews_project ON business_reviews(project_id);

-- Architecture Decision Records (ADR)
CREATE TABLE IF NOT EXISTS adr_records (
    id TEXT PRIMARY KEY, -- Format: ADR-XXXX
    project_id TEXT NOT NULL,
    title TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'proposed',
    date INTEGER NOT NULL,
    deciders TEXT NOT NULL DEFAULT '[]', -- JSON array of decider names
    template TEXT NOT NULL DEFAULT 'nygard',
    context TEXT NOT NULL,
    decision TEXT NOT NULL,
    consequences TEXT NOT NULL,
    tags TEXT DEFAULT '[]', -- JSON array of tags
    decision_drivers TEXT DEFAULT '[]', -- JSON array for MADR
    considered_options TEXT DEFAULT '[]', -- JSON array for MADR
    pros_and_cons TEXT DEFAULT '[]', -- JSON array for MADR
    supersedes TEXT DEFAULT '[]', -- JSON array of ADR IDs
    superseded_by TEXT, -- Single ADR ID
    related_to TEXT DEFAULT '[]', -- JSON array of related ADR IDs
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS adr_status_history (
    id TEXT PRIMARY KEY,
    adr_id TEXT NOT NULL,
    project_id TEXT NOT NULL,
    from_status TEXT,
    to_status TEXT NOT NULL,
    changed_at INTEGER NOT NULL DEFAULT (unixepoch()),
    changed_by TEXT NOT NULL,
    reason TEXT,
    FOREIGN KEY (adr_id) REFERENCES adr_records(id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

-- Security and Audit Logs
CREATE TABLE IF NOT EXISTS security_events (
    id TEXT PRIMARY KEY,
    event_type TEXT NOT NULL,
    tool_name TEXT,
    user_id TEXT,
    context TEXT DEFAULT '{}',
    risk_level TEXT NOT NULL DEFAULT 'low',
    details TEXT DEFAULT '{}',
    created_at INTEGER NOT NULL DEFAULT (unixepoch())
);

-- Performance Monitoring
CREATE TABLE IF NOT EXISTS performance_metrics (
    id TEXT PRIMARY KEY,
    tool_name TEXT NOT NULL,
    execution_time INTEGER NOT NULL,
    memory_usage INTEGER,
    success BOOLEAN NOT NULL,
    error_details TEXT,
    user_id TEXT,
    request_id TEXT,
    created_at INTEGER NOT NULL DEFAULT (unixepoch())
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_agile_stories_sprint ON agile_stories(sprint_id);
CREATE INDEX IF NOT EXISTS idx_agile_stories_epic ON agile_stories(epic_id);
CREATE INDEX IF NOT EXISTS idx_agile_stories_status ON agile_stories(status);
CREATE INDEX IF NOT EXISTS idx_agile_stories_project ON agile_stories(project_id);
CREATE INDEX IF NOT EXISTS idx_agile_sprints_project ON agile_sprints(project_id);
CREATE INDEX IF NOT EXISTS idx_agile_sprints_status ON agile_sprints(status);
CREATE INDEX IF NOT EXISTS idx_agile_epics_project ON agile_epics(project_id);
CREATE INDEX IF NOT EXISTS idx_agile_story_comments_story ON agile_story_comments(story_id);
CREATE INDEX IF NOT EXISTS idx_agile_story_comments_thread ON agile_story_comments(thread_id);
CREATE INDEX IF NOT EXISTS idx_agile_story_blockers_blocked ON agile_story_blockers(blocked_story_id);
CREATE INDEX IF NOT EXISTS idx_agile_story_blockers_blocker ON agile_story_blockers(blocker_story_id);
CREATE INDEX IF NOT EXISTS idx_agile_story_blockers_status ON agile_story_blockers(status);
CREATE INDEX IF NOT EXISTS idx_agile_sprint_completions_sprint ON agile_sprint_completions(sprint_id);
CREATE INDEX IF NOT EXISTS idx_kanban_boards_project ON kanban_boards(project_id);
CREATE INDEX IF NOT EXISTS idx_kanban_tasks_board ON kanban_tasks(board_id);
CREATE INDEX IF NOT EXISTS idx_kanban_tasks_project ON kanban_tasks(project_id);
CREATE INDEX IF NOT EXISTS idx_kanban_tasks_column ON kanban_tasks(board_id, column_name);
CREATE INDEX IF NOT EXISTS idx_kanban_tasks_status ON kanban_tasks(status);
CREATE INDEX IF NOT EXISTS idx_documents_project ON documents(project_id);
CREATE INDEX IF NOT EXISTS idx_documents_type ON documents(type);
CREATE INDEX IF NOT EXISTS idx_approval_requests_status ON approval_requests(status);
CREATE INDEX IF NOT EXISTS idx_approval_requests_requested_by ON approval_requests(requested_by);
CREATE INDEX IF NOT EXISTS idx_memories_project ON memories(project_id);
CREATE INDEX IF NOT EXISTS idx_memories_type ON memories(type);
CREATE INDEX IF NOT EXISTS idx_business_plans_project ON business_plans(project_id);
CREATE INDEX IF NOT EXISTS idx_security_events_tool ON security_events(tool_name);
CREATE INDEX IF NOT EXISTS idx_security_events_user ON security_events(user_id);
CREATE INDEX IF NOT EXISTS idx_performance_metrics_tool ON performance_metrics(tool_name);
CREATE INDEX IF NOT EXISTS idx_performance_metrics_created ON performance_metrics(created_at);
CREATE INDEX IF NOT EXISTS idx_adr_records_project ON adr_records(project_id);
CREATE INDEX IF NOT EXISTS idx_adr_records_status ON adr_records(status);
CREATE INDEX IF NOT EXISTS idx_adr_records_template ON adr_records(template);
CREATE INDEX IF NOT EXISTS idx_adr_status_history_adr ON adr_status_history(adr_id);
CREATE INDEX IF NOT EXISTS idx_adr_status_history_project ON adr_status_history(project_id);

-- Development Module (TDD Enforcement)
CREATE TABLE IF NOT EXISTS development_features (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    name TEXT NOT NULL,
    description TEXT,
    status TEXT NOT NULL DEFAULT 'planning', -- planning, testing, implementing, completed
    test_coverage REAL DEFAULT 0.0,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS development_test_cases (
    id TEXT PRIMARY KEY,
    feature_id TEXT NOT NULL,
    project_id TEXT NOT NULL,
    name TEXT NOT NULL,
    description TEXT,
    type TEXT NOT NULL DEFAULT 'unit', -- unit, integration, e2e
    status TEXT NOT NULL DEFAULT 'pending', -- pending, passing, failing, skipped
    expected_behavior TEXT,
    actual_behavior TEXT,
    error_message TEXT,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (feature_id) REFERENCES development_features(id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS development_tdd_sessions (
    id TEXT PRIMARY KEY,
    feature_id TEXT NOT NULL,
    project_id TEXT NOT NULL,
    current_phase TEXT NOT NULL DEFAULT 'red', -- red, green, refactor
    started_at INTEGER NOT NULL DEFAULT (unixepoch()),
    completed_at INTEGER,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (feature_id) REFERENCES development_features(id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS development_tdd_config (
    project_id TEXT PRIMARY KEY,
    enforce_test_first BOOLEAN DEFAULT TRUE,
    minimum_test_coverage REAL DEFAULT 80.0,
    require_tests_before_implementation BOOLEAN DEFAULT TRUE,
    auto_generate_test_templates BOOLEAN DEFAULT TRUE,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_development_features_project ON development_features(project_id);
CREATE INDEX IF NOT EXISTS idx_development_features_status ON development_features(status);
CREATE INDEX IF NOT EXISTS idx_development_test_cases_feature ON development_test_cases(feature_id);
CREATE INDEX IF NOT EXISTS idx_development_test_cases_status ON development_test_cases(status);
CREATE INDEX IF NOT EXISTS idx_development_tdd_sessions_feature ON development_tdd_sessions(feature_id);

-- Workspace Module (Multi-repository management)
CREATE TABLE IF NOT EXISTS workspaces (
    id TEXT PRIMARY KEY, -- Format: ws-<uuid>
    name TEXT NOT NULL,
    description TEXT,
    root_path TEXT NOT NULL,
    active BOOLEAN DEFAULT FALSE,
    settings TEXT DEFAULT '{}', -- JSON settings
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch())
);

CREATE TABLE IF NOT EXISTS workspace_repositories (
    id TEXT PRIMARY KEY, -- Format: repo-<uuid>
    workspace_id TEXT NOT NULL,
    name TEXT NOT NULL,
    path TEXT NOT NULL,
    type TEXT NOT NULL DEFAULT 'git', -- git, svn, mercurial, local
    is_primary BOOLEAN DEFAULT FALSE,
    remote_url TEXT,
    current_branch TEXT,
    last_sync INTEGER,
    dependencies TEXT DEFAULT '[]', -- JSON array of repository IDs
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_workspaces_active ON workspaces(active);
CREATE INDEX IF NOT EXISTS idx_workspace_repositories_workspace ON workspace_repositories(workspace_id);
CREATE INDEX IF NOT EXISTS idx_workspace_repositories_primary ON workspace_repositories(is_primary);

-- Process Automation Module
CREATE TABLE IF NOT EXISTS process_definitions (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    name TEXT NOT NULL,
    description TEXT,
    version TEXT NOT NULL DEFAULT '1.0.0',
    persona TEXT,
    category TEXT,
    tags TEXT DEFAULT '[]', -- JSON array
    variables TEXT DEFAULT '{}', -- JSON object
    on_success TEXT, -- JSON array of activities
    on_failure TEXT, -- JSON array of activities
    is_active BOOLEAN DEFAULT TRUE,
    created_by TEXT,
    updated_by TEXT,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS process_activities (
    id TEXT PRIMARY KEY,
    process_id TEXT NOT NULL,
    activity_order INTEGER NOT NULL,
    activity_id TEXT NOT NULL,
    type TEXT NOT NULL, -- tool, human, agent, conditional, loop, parallel, external
    name TEXT NOT NULL,
    description TEXT,
    config TEXT NOT NULL, -- JSON configuration
    retry_policy TEXT, -- JSON retry configuration
    timeout INTEGER,
    required BOOLEAN DEFAULT TRUE,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (process_id) REFERENCES process_definitions(id) ON DELETE CASCADE
);

-- Git Workflow Module (DEPRECATED - Will be removed in v2.0.0)
-- These tables are maintained for migration purposes only
CREATE TABLE IF NOT EXISTS git_branches (
    id TEXT PRIMARY KEY, -- Format: branch-<uuid>
    project_id TEXT NOT NULL,
    name TEXT NOT NULL,
    is_current BOOLEAN DEFAULT FALSE,
    remote TEXT,
    ahead INTEGER DEFAULT 0,
    behind INTEGER DEFAULT 0,
    is_protected BOOLEAN DEFAULT FALSE,
    last_commit_hash TEXT,
    last_commit_date INTEGER,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    UNIQUE(project_id, name)
);

CREATE TABLE IF NOT EXISTS git_tags (
    id TEXT PRIMARY KEY, -- Format: tag-<uuid>
    project_id TEXT NOT NULL,
    name TEXT NOT NULL,
    commit_hash TEXT NOT NULL,
    author TEXT,
    date INTEGER,
    message TEXT,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    UNIQUE(project_id, name)
);

CREATE TABLE IF NOT EXISTS git_remotes (
    id TEXT PRIMARY KEY, -- Format: remote-<uuid>
    project_id TEXT NOT NULL,
    name TEXT NOT NULL,
    url TEXT NOT NULL,
    type TEXT NOT NULL, -- 'fetch', 'push', 'both'
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    UNIQUE(project_id, name)
);

CREATE TABLE IF NOT EXISTS git_workflows (
    id TEXT PRIMARY KEY, -- Format: workflow-<uuid>
    project_id TEXT NOT NULL,
    name TEXT NOT NULL,
    type TEXT NOT NULL, -- 'feature', 'release', 'hotfix', 'custom'
    source_branch TEXT NOT NULL,
    target_branch TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'active', -- 'active', 'completed', 'cancelled'
    current_step INTEGER DEFAULT 0,
    steps TEXT NOT NULL DEFAULT '[]', -- JSON array of workflow steps
    metadata TEXT DEFAULT '{}', -- JSON metadata
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    completed_at INTEGER,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS git_pull_requests (
    id TEXT PRIMARY KEY, -- Format: pr-<uuid>
    project_id TEXT NOT NULL,
    number INTEGER NOT NULL,
    title TEXT NOT NULL,
    description TEXT,
    source_branch TEXT NOT NULL,
    target_branch TEXT NOT NULL,
    author TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'open', -- 'open', 'closed', 'merged'
    reviewers TEXT DEFAULT '[]', -- JSON array of reviewer objects
    labels TEXT DEFAULT '[]', -- JSON array of labels
    checks TEXT DEFAULT '[]', -- JSON array of status checks
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    merged_at INTEGER,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    UNIQUE(project_id, number)
);

CREATE TABLE IF NOT EXISTS git_hooks (
    id TEXT PRIMARY KEY, -- Format: hook-<uuid>
    project_id TEXT NOT NULL,
    name TEXT NOT NULL, -- 'pre-commit', 'commit-msg', 'pre-push', 'post-merge'
    script TEXT NOT NULL,
    enabled BOOLEAN DEFAULT TRUE,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    UNIQUE(project_id, name)
);

CREATE TABLE IF NOT EXISTS git_config (
    project_id TEXT PRIMARY KEY,
    user_name TEXT,
    user_email TEXT,
    remote_origin TEXT,
    default_branch TEXT,
    auto_setup_merge BOOLEAN DEFAULT TRUE,
    feature_prefix TEXT DEFAULT 'feature/',
    release_prefix TEXT DEFAULT 'release/',
    hotfix_prefix TEXT DEFAULT 'hotfix/',
    commit_template TEXT,
    commit_convention_type TEXT DEFAULT 'conventional', -- 'conventional', 'gitmoji', 'custom'
    commit_convention_types TEXT DEFAULT '[]', -- JSON array of allowed types
    commit_convention_scopes TEXT DEFAULT '[]', -- JSON array of allowed scopes
    commit_convention_validate BOOLEAN DEFAULT TRUE,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_git_branches_project ON git_branches(project_id);
CREATE INDEX IF NOT EXISTS idx_git_branches_current ON git_branches(is_current);
CREATE INDEX IF NOT EXISTS idx_git_tags_project ON git_tags(project_id);
CREATE INDEX IF NOT EXISTS idx_git_workflows_project ON git_workflows(project_id);
CREATE INDEX IF NOT EXISTS idx_git_workflows_status ON git_workflows(status);
CREATE INDEX IF NOT EXISTS idx_git_pull_requests_project ON git_pull_requests(project_id);
CREATE INDEX IF NOT EXISTS idx_git_pull_requests_status ON git_pull_requests(status);

CREATE TABLE IF NOT EXISTS process_triggers (
    id TEXT PRIMARY KEY,
    process_id TEXT NOT NULL,
    trigger_id TEXT NOT NULL,
    type TEXT NOT NULL, -- manual, schedule, event, webhook, condition, chain
    name TEXT NOT NULL,
    enabled BOOLEAN DEFAULT TRUE,
    config TEXT NOT NULL, -- JSON configuration
    ai_suggested BOOLEAN DEFAULT FALSE,
    reasoning TEXT,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (process_id) REFERENCES process_definitions(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS process_executions (
    id TEXT PRIMARY KEY,
    process_id TEXT NOT NULL,
    project_id TEXT NOT NULL,
    process_version TEXT NOT NULL,
    status TEXT NOT NULL, -- pending, running, completed, failed, cancelled
    triggered_by TEXT NOT NULL,
    trigger_type TEXT,
    variables TEXT DEFAULT '{}', -- JSON object
    error_message TEXT,
    error_code TEXT,
    started_at INTEGER NOT NULL,
    completed_at INTEGER,
    duration INTEGER,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (process_id) REFERENCES process_definitions(id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS process_activity_results (
    id TEXT PRIMARY KEY,
    execution_id TEXT NOT NULL,
    activity_id TEXT NOT NULL,
    status TEXT NOT NULL, -- pending, running, completed, failed, skipped
    started_at INTEGER,
    completed_at INTEGER,
    duration INTEGER,
    output TEXT, -- JSON output data
    error TEXT, -- JSON error data
    retry_count INTEGER DEFAULT 0,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (execution_id) REFERENCES process_executions(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS process_execution_logs (
    id TEXT PRIMARY KEY,
    execution_id TEXT NOT NULL,
    activity_id TEXT,
    timestamp INTEGER NOT NULL,
    level TEXT NOT NULL, -- debug, info, warning, error
    message TEXT NOT NULL,
    data TEXT, -- JSON additional data
    FOREIGN KEY (execution_id) REFERENCES process_executions(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS process_templates (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    name TEXT NOT NULL,
    description TEXT,
    persona TEXT,
    category TEXT NOT NULL,
    variables TEXT DEFAULT '{}', -- JSON default variables
    process_definition TEXT NOT NULL, -- JSON process definition
    is_system BOOLEAN DEFAULT FALSE,
    created_by TEXT,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_process_definitions_project ON process_definitions(project_id);
CREATE INDEX IF NOT EXISTS idx_process_definitions_active ON process_definitions(is_active);
CREATE INDEX IF NOT EXISTS idx_process_activities_process ON process_activities(process_id);
CREATE INDEX IF NOT EXISTS idx_process_triggers_process ON process_triggers(process_id);
CREATE INDEX IF NOT EXISTS idx_process_triggers_enabled ON process_triggers(enabled);
CREATE INDEX IF NOT EXISTS idx_process_executions_process ON process_executions(process_id);
CREATE INDEX IF NOT EXISTS idx_process_executions_status ON process_executions(status);
CREATE INDEX IF NOT EXISTS idx_process_executions_started ON process_executions(started_at);
CREATE INDEX IF NOT EXISTS idx_process_activity_results_execution ON process_activity_results(execution_id);
CREATE INDEX IF NOT EXISTS idx_process_execution_logs_execution ON process_execution_logs(execution_id);
CREATE INDEX IF NOT EXISTS idx_process_execution_logs_timestamp ON process_execution_logs(timestamp);
CREATE INDEX IF NOT EXISTS idx_process_templates_category ON process_templates(category);

-- Issue Tracking Module
CREATE TABLE IF NOT EXISTS issue_tracker_issues (
    id TEXT PRIMARY KEY, -- Format: ISSUE-XXXX
    project_id TEXT NOT NULL,
    type TEXT NOT NULL, -- bug, feature, enhancement, documentation, question
    title TEXT NOT NULL,
    description TEXT,
    status TEXT NOT NULL DEFAULT 'open', -- open, in-progress, resolved, closed, wont-fix
    priority TEXT NOT NULL DEFAULT 'medium', -- critical, high, medium, low
    created_by TEXT NOT NULL,
    assigned_to TEXT,
    labels TEXT DEFAULT '[]', -- JSON array
    affected_modules TEXT DEFAULT '[]', -- JSON array
    related_issues TEXT DEFAULT '[]', -- JSON array
    resolution TEXT,
    closed_at INTEGER,
    metadata TEXT DEFAULT '{}', -- JSON object
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS issue_tracker_comments (
    id TEXT PRIMARY KEY,
    issue_id TEXT NOT NULL,
    project_id TEXT NOT NULL,
    author TEXT NOT NULL,
    content TEXT NOT NULL,
    type TEXT NOT NULL DEFAULT 'comment', -- comment, status-change, assignment, resolution
    edited_at INTEGER,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (issue_id) REFERENCES issue_tracker_issues(id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS issue_tracker_attachments (
    id TEXT PRIMARY KEY,
    issue_id TEXT NOT NULL,
    project_id TEXT NOT NULL,
    filename TEXT NOT NULL,
    path TEXT NOT NULL,
    size INTEGER NOT NULL,
    mime_type TEXT,
    uploaded_by TEXT NOT NULL,
    uploaded_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (issue_id) REFERENCES issue_tracker_issues(id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_issue_tracker_issues_project ON issue_tracker_issues(project_id);
CREATE INDEX IF NOT EXISTS idx_issue_tracker_issues_status ON issue_tracker_issues(status);
CREATE INDEX IF NOT EXISTS idx_issue_tracker_issues_priority ON issue_tracker_issues(priority);
CREATE INDEX IF NOT EXISTS idx_issue_tracker_issues_type ON issue_tracker_issues(type);
CREATE INDEX IF NOT EXISTS idx_issue_tracker_issues_assigned ON issue_tracker_issues(assigned_to);
CREATE INDEX IF NOT EXISTS idx_issue_tracker_comments_issue ON issue_tracker_comments(issue_id);
CREATE INDEX IF NOT EXISTS idx_issue_tracker_attachments_issue ON issue_tracker_attachments(issue_id);

-- Product Roadmap Module
CREATE TABLE IF NOT EXISTS product_roadmaps (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    name TEXT NOT NULL,
    vision TEXT NOT NULL,
    time_horizon TEXT NOT NULL, -- quarterly, annual, multi-year
    status TEXT NOT NULL DEFAULT 'draft', -- draft, active, archived
    owner TEXT NOT NULL,
    stakeholders TEXT DEFAULT '[]', -- JSON array
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS roadmap_themes (
    id TEXT PRIMARY KEY,
    roadmap_id TEXT NOT NULL,
    name TEXT NOT NULL,
    description TEXT NOT NULL,
    objectives TEXT NOT NULL DEFAULT '[]', -- JSON array
    priority TEXT NOT NULL, -- must-have, should-have, nice-to-have
    start_quarter TEXT NOT NULL, -- e.g., Q1 2024
    end_quarter TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'planned', -- planned, in-progress, completed, deferred
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (roadmap_id) REFERENCES product_roadmaps(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS roadmap_initiatives (
    id TEXT PRIMARY KEY,
    theme_id TEXT NOT NULL,
    roadmap_id TEXT NOT NULL,
    title TEXT NOT NULL,
    description TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'ideation', -- ideation, validated, scheduled, in-development, launched
    -- Value metrics
    user_impact TEXT NOT NULL, -- low, medium, high, critical
    revenue_impact REAL DEFAULT 0,
    cost_savings REAL DEFAULT 0,
    strategic_value INTEGER DEFAULT 0, -- 1-10
    customer_satisfaction INTEGER DEFAULT 0, -- NPS impact
    -- Effort estimates
    development_weeks REAL DEFAULT 0,
    design_weeks REAL DEFAULT 0,
    qa_weeks REAL DEFAULT 0,
    effort_confidence TEXT DEFAULT 'medium', -- low, medium, high
    -- Other fields
    epic_ids TEXT DEFAULT '[]', -- JSON array linking to agile epics
    risks TEXT DEFAULT '[]', -- JSON array of risk objects
    dependencies TEXT DEFAULT '[]', -- JSON array of dependency objects
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (theme_id) REFERENCES roadmap_themes(id) ON DELETE CASCADE,
    FOREIGN KEY (roadmap_id) REFERENCES product_roadmaps(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS roadmap_features (
    id TEXT PRIMARY KEY,
    initiative_id TEXT NOT NULL,
    roadmap_id TEXT NOT NULL,
    name TEXT NOT NULL,
    description TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'proposed', -- proposed, approved, in-progress, completed, cancelled
    priority INTEGER DEFAULT 50, -- 1-100 score
    -- Business value
    business_value_score INTEGER DEFAULT 0, -- 1-100
    business_value_rationale TEXT,
    business_value_metrics TEXT DEFAULT '[]', -- JSON array of success metrics
    -- Technical details
    technical_complexity TEXT NOT NULL, -- low, medium, high, very-high
    user_stories TEXT DEFAULT '[]', -- JSON array linking to agile stories
    target_release TEXT, -- Release ID
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (initiative_id) REFERENCES roadmap_initiatives(id) ON DELETE CASCADE,
    FOREIGN KEY (roadmap_id) REFERENCES product_roadmaps(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS roadmap_milestones (
    id TEXT PRIMARY KEY,
    roadmap_id TEXT NOT NULL,
    name TEXT NOT NULL,
    date INTEGER NOT NULL,
    type TEXT NOT NULL, -- release, business, technical, regulatory
    description TEXT NOT NULL,
    deliverables TEXT NOT NULL DEFAULT '[]', -- JSON array
    status TEXT NOT NULL DEFAULT 'upcoming', -- upcoming, at-risk, completed, missed
    dependencies TEXT DEFAULT '[]', -- JSON array of milestone IDs
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (roadmap_id) REFERENCES product_roadmaps(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS roadmap_releases (
    id TEXT PRIMARY KEY,
    roadmap_id TEXT NOT NULL,
    version TEXT NOT NULL,
    name TEXT NOT NULL,
    date INTEGER NOT NULL,
    features TEXT NOT NULL DEFAULT '[]', -- JSON array of feature IDs
    themes TEXT NOT NULL DEFAULT '[]', -- JSON array of theme IDs
    goals TEXT NOT NULL DEFAULT '[]', -- JSON array
    status TEXT NOT NULL DEFAULT 'planning', -- planning, development, testing, released
    notes TEXT,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (roadmap_id) REFERENCES product_roadmaps(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS roadmap_reviews (
    id TEXT PRIMARY KEY,
    roadmap_id TEXT NOT NULL,
    review_date INTEGER NOT NULL,
    participants TEXT NOT NULL DEFAULT '[]', -- JSON array
    decisions TEXT NOT NULL DEFAULT '[]', -- JSON array of decision objects
    feedback TEXT NOT NULL DEFAULT '[]', -- JSON array of feedback objects
    next_review_date INTEGER,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (roadmap_id) REFERENCES product_roadmaps(id) ON DELETE CASCADE
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_product_roadmaps_project ON product_roadmaps(project_id);
CREATE INDEX IF NOT EXISTS idx_product_roadmaps_status ON product_roadmaps(status);
CREATE INDEX IF NOT EXISTS idx_roadmap_themes_roadmap ON roadmap_themes(roadmap_id);
CREATE INDEX IF NOT EXISTS idx_roadmap_themes_status ON roadmap_themes(status);
CREATE INDEX IF NOT EXISTS idx_roadmap_initiatives_theme ON roadmap_initiatives(theme_id);
CREATE INDEX IF NOT EXISTS idx_roadmap_initiatives_roadmap ON roadmap_initiatives(roadmap_id);
CREATE INDEX IF NOT EXISTS idx_roadmap_initiatives_status ON roadmap_initiatives(status);
CREATE INDEX IF NOT EXISTS idx_roadmap_features_initiative ON roadmap_features(initiative_id);
CREATE INDEX IF NOT EXISTS idx_roadmap_features_roadmap ON roadmap_features(roadmap_id);
CREATE INDEX IF NOT EXISTS idx_roadmap_features_status ON roadmap_features(status);
CREATE INDEX IF NOT EXISTS idx_roadmap_milestones_roadmap ON roadmap_milestones(roadmap_id);
CREATE INDEX IF NOT EXISTS idx_roadmap_milestones_date ON roadmap_milestones(date);
CREATE INDEX IF NOT EXISTS idx_roadmap_releases_roadmap ON roadmap_releases(roadmap_id);
CREATE INDEX IF NOT EXISTS idx_roadmap_releases_date ON roadmap_releases(date);
CREATE INDEX IF NOT EXISTS idx_roadmap_reviews_roadmap ON roadmap_reviews(roadmap_id);

-- Product Requirements Module
CREATE TABLE IF NOT EXISTS product_requirements (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    name TEXT NOT NULL,
    type TEXT NOT NULL, -- functional, non-functional, business, technical, user-interface, security, performance
    description TEXT NOT NULL,
    acceptance_criteria TEXT DEFAULT '[]', -- JSON array
    priority TEXT DEFAULT 'medium', -- low, medium, high, critical
    status TEXT DEFAULT 'draft', -- draft, approved, in_development, implemented, deprecated
    related_stories TEXT DEFAULT '[]', -- JSON array of story IDs
    tags TEXT DEFAULT '[]', -- JSON array
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    created_by TEXT,
    approved_by TEXT,
    approved_at INTEGER,
    version INTEGER DEFAULT 1,
    parent_requirement_id TEXT,
    child_requirement_ids TEXT DEFAULT '[]', -- JSON array
    dependencies TEXT DEFAULT '[]', -- JSON array
    business_value TEXT,
    technical_notes TEXT,
    test_criteria TEXT DEFAULT '[]', -- JSON array
    compliance_requirements TEXT DEFAULT '[]', -- JSON array
    estimated_effort TEXT,
    actual_effort TEXT,
    target_release TEXT,
    documentation_links TEXT DEFAULT '[]', -- JSON array
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (parent_requirement_id) REFERENCES product_requirements(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS requirement_repositories (
    id TEXT PRIMARY KEY,
    requirement_id TEXT NOT NULL,
    project_id TEXT NOT NULL,
    name TEXT NOT NULL,
    implementation_status TEXT NOT NULL DEFAULT 'not_started', -- not_started, in_progress, completed, blocked
    branch TEXT,
    pr_url TEXT,
    notes TEXT,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (requirement_id) REFERENCES product_requirements(id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS requirement_story_links (
    id TEXT PRIMARY KEY,
    requirement_id TEXT NOT NULL,
    story_id TEXT NOT NULL,
    project_id TEXT NOT NULL,
    link_type TEXT DEFAULT 'implements', -- implements, relates_to, depends_on
    notes TEXT,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (requirement_id) REFERENCES product_requirements(id) ON DELETE CASCADE,
    FOREIGN KEY (story_id) REFERENCES agile_stories(id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS requirement_changes (
    id TEXT PRIMARY KEY,
    requirement_id TEXT NOT NULL,
    project_id TEXT NOT NULL,
    field TEXT NOT NULL,
    old_value TEXT,
    new_value TEXT,
    changed_by TEXT NOT NULL,
    changed_at INTEGER NOT NULL DEFAULT (unixepoch()),
    reason TEXT,
    FOREIGN KEY (requirement_id) REFERENCES product_requirements(id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS requirement_templates (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    name TEXT NOT NULL,
    type TEXT NOT NULL, -- functional, non-functional, business, technical, user-interface, security, performance
    description_template TEXT NOT NULL,
    acceptance_criteria_template TEXT DEFAULT '[]', -- JSON array
    test_criteria_template TEXT DEFAULT '[]', -- JSON array
    tags TEXT DEFAULT '[]', -- JSON array
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_product_requirements_project ON product_requirements(project_id);
CREATE INDEX IF NOT EXISTS idx_product_requirements_type ON product_requirements(type);
CREATE INDEX IF NOT EXISTS idx_product_requirements_status ON product_requirements(status);
CREATE INDEX IF NOT EXISTS idx_product_requirements_priority ON product_requirements(priority);
CREATE INDEX IF NOT EXISTS idx_product_requirements_parent ON product_requirements(parent_requirement_id);
CREATE INDEX IF NOT EXISTS idx_requirement_repositories_requirement ON requirement_repositories(requirement_id);
CREATE INDEX IF NOT EXISTS idx_requirement_repositories_status ON requirement_repositories(implementation_status);
CREATE INDEX IF NOT EXISTS idx_requirement_story_links_requirement ON requirement_story_links(requirement_id);
CREATE INDEX IF NOT EXISTS idx_requirement_story_links_story ON requirement_story_links(story_id);
CREATE INDEX IF NOT EXISTS idx_requirement_changes_requirement ON requirement_changes(requirement_id);
CREATE INDEX IF NOT EXISTS idx_requirement_templates_project ON requirement_templates(project_id);
CREATE INDEX IF NOT EXISTS idx_requirement_templates_type ON requirement_templates(type);

-- Testing Framework Module
CREATE TABLE IF NOT EXISTS test_results (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    timestamp INTEGER NOT NULL DEFAULT (unixepoch()),
    branch TEXT,
    commit_hash TEXT,
    framework TEXT NOT NULL DEFAULT 'unknown', -- jest, mocha, vitest, pytest, rspec, unknown
    command TEXT NOT NULL,
    duration INTEGER NOT NULL,
    summary_total INTEGER NOT NULL,
    summary_passed INTEGER NOT NULL,
    summary_failed INTEGER NOT NULL,
    summary_skipped INTEGER DEFAULT 0,
    summary_pending INTEGER DEFAULT 0,
    summary_success_rate REAL NOT NULL,
    coverage_data TEXT, -- JSON coverage report
    tags TEXT DEFAULT '[]', -- JSON array
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS test_suites (
    id TEXT PRIMARY KEY,
    result_id TEXT NOT NULL,
    name TEXT NOT NULL,
    path TEXT NOT NULL,
    status TEXT NOT NULL, -- pending, running, passed, failed, skipped
    duration INTEGER,
    timestamp INTEGER NOT NULL,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (result_id) REFERENCES test_results(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS test_cases (
    id TEXT PRIMARY KEY,
    suite_id TEXT NOT NULL,
    result_id TEXT NOT NULL,
    name TEXT NOT NULL,
    status TEXT NOT NULL, -- pending, running, passed, failed, skipped
    duration INTEGER,
    error_message TEXT,
    error_stack TEXT,
    error_expected TEXT,
    error_actual TEXT,
    error_type TEXT,
    assertions INTEGER,
    skipped BOOLEAN DEFAULT FALSE,
    tags TEXT DEFAULT '[]', -- JSON array
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (suite_id) REFERENCES test_suites(id) ON DELETE CASCADE,
    FOREIGN KEY (result_id) REFERENCES test_results(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS test_baselines (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    result_id TEXT NOT NULL,
    name TEXT NOT NULL DEFAULT 'baseline',
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (result_id) REFERENCES test_results(id) ON DELETE CASCADE,
    UNIQUE(project_id, name)
);

CREATE TABLE IF NOT EXISTS flaky_tests (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    test_name TEXT NOT NULL,
    suite_name TEXT NOT NULL,
    failure_rate REAL NOT NULL,
    total_runs INTEGER NOT NULL DEFAULT 0,
    failures INTEGER NOT NULL DEFAULT 0,
    passes INTEGER NOT NULL DEFAULT 0,
    first_seen INTEGER NOT NULL,
    last_seen INTEGER NOT NULL,
    recent_runs TEXT DEFAULT '[]', -- JSON array of recent run results
    error_patterns TEXT DEFAULT '[]', -- JSON array
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    UNIQUE(project_id, test_name, suite_name)
);

CREATE TABLE IF NOT EXISTS test_watchers (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    patterns TEXT NOT NULL DEFAULT '[]', -- JSON array of file patterns
    command TEXT NOT NULL,
    active BOOLEAN DEFAULT TRUE,
    auto_rerun BOOLEAN DEFAULT TRUE,
    debounce_ms INTEGER DEFAULT 1000,
    last_run_id TEXT,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (last_run_id) REFERENCES test_results(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS test_coverage_files (
    id TEXT PRIMARY KEY,
    result_id TEXT NOT NULL,
    path TEXT NOT NULL,
    lines_total INTEGER NOT NULL,
    lines_covered INTEGER NOT NULL,
    lines_skipped INTEGER DEFAULT 0,
    lines_percentage REAL NOT NULL,
    statements_total INTEGER NOT NULL,
    statements_covered INTEGER NOT NULL,
    statements_skipped INTEGER DEFAULT 0,
    statements_percentage REAL NOT NULL,
    functions_total INTEGER NOT NULL,
    functions_covered INTEGER NOT NULL,
    functions_skipped INTEGER DEFAULT 0,
    functions_percentage REAL NOT NULL,
    branches_total INTEGER NOT NULL,
    branches_covered INTEGER NOT NULL,
    branches_skipped INTEGER DEFAULT 0,
    branches_percentage REAL NOT NULL,
    uncovered_lines TEXT DEFAULT '[]', -- JSON array of line numbers
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (result_id) REFERENCES test_results(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS test_configurations (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    framework TEXT NOT NULL, -- jest, mocha, vitest, pytest, rspec
    command TEXT NOT NULL,
    config_file TEXT,
    test_match TEXT DEFAULT '[]', -- JSON array
    coverage_threshold_global TEXT, -- JSON object with lines, statements, functions, branches
    coverage_threshold_paths TEXT DEFAULT '{}', -- JSON object mapping paths to thresholds
    setup_files TEXT DEFAULT '[]', -- JSON array
    test_environment TEXT,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    UNIQUE(project_id, framework)
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_test_results_project ON test_results(project_id);
CREATE INDEX IF NOT EXISTS idx_test_results_timestamp ON test_results(timestamp);
CREATE INDEX IF NOT EXISTS idx_test_results_branch ON test_results(branch);
CREATE INDEX IF NOT EXISTS idx_test_results_commit ON test_results(commit_hash);
CREATE INDEX IF NOT EXISTS idx_test_suites_result ON test_suites(result_id);
CREATE INDEX IF NOT EXISTS idx_test_cases_suite ON test_cases(suite_id);
CREATE INDEX IF NOT EXISTS idx_test_cases_result ON test_cases(result_id);
CREATE INDEX IF NOT EXISTS idx_test_cases_status ON test_cases(status);
CREATE INDEX IF NOT EXISTS idx_test_baselines_project ON test_baselines(project_id);
CREATE INDEX IF NOT EXISTS idx_flaky_tests_project ON flaky_tests(project_id);
CREATE INDEX IF NOT EXISTS idx_test_watchers_project ON test_watchers(project_id);
CREATE INDEX IF NOT EXISTS idx_test_watchers_active ON test_watchers(active);
CREATE INDEX IF NOT EXISTS idx_test_coverage_files_result ON test_coverage_files(result_id);
CREATE INDEX IF NOT EXISTS idx_test_configurations_project ON test_configurations(project_id);

-- Code Analysis Module
CREATE TABLE IF NOT EXISTS code_analysis_results (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    timestamp INTEGER NOT NULL DEFAULT (unixepoch()),
    files_analyzed INTEGER NOT NULL DEFAULT 0,
    average_complexity REAL DEFAULT 0.0,
    average_maintainability REAL DEFAULT 0.0,
    total_issues INTEGER DEFAULT 0,
    critical_issues INTEGER DEFAULT 0,
    duplication_percentage REAL DEFAULT 0.0,
    overall_score INTEGER DEFAULT 0,
    overall_grade TEXT DEFAULT 'F',
    options TEXT DEFAULT '{}', -- JSON analysis options
    recommendations TEXT DEFAULT '[]', -- JSON array of recommendations
    baseline_comparison TEXT DEFAULT '{}', -- JSON baseline comparison data
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS code_analysis_file_results (
    id TEXT PRIMARY KEY,
    analysis_id TEXT NOT NULL,
    project_id TEXT NOT NULL,
    file_path TEXT NOT NULL,
    file_size INTEGER DEFAULT 0,
    language TEXT,
    last_modified INTEGER,
    -- Complexity metrics
    cyclomatic_complexity REAL DEFAULT 0.0,
    cognitive_complexity REAL DEFAULT 0.0,
    halstead_difficulty REAL DEFAULT 0.0,
    halstead_volume REAL DEFAULT 0.0,
    halstead_effort REAL DEFAULT 0.0,
    max_complexity REAL DEFAULT 0.0,
    total_complexity REAL DEFAULT 0.0,
    function_complexities TEXT DEFAULT '[]', -- JSON array of function complexity data
    -- Maintainability metrics
    maintainability_index REAL DEFAULT 0.0,
    maintainability_rating TEXT DEFAULT 'F',
    line_count INTEGER DEFAULT 0,
    comment_ratio REAL DEFAULT 0.0,
    test_coverage REAL DEFAULT 0.0,
    -- Duplication metrics
    duplication_percentage REAL DEFAULT 0.0,
    duplicate_blocks TEXT DEFAULT '[]', -- JSON array of duplicate blocks
    duplicated_lines INTEGER DEFAULT 0,
    total_lines INTEGER DEFAULT 0,
    -- Quality metrics
    total_issues INTEGER DEFAULT 0,
    high_issues INTEGER DEFAULT 0,
    medium_issues INTEGER DEFAULT 0,
    low_issues INTEGER DEFAULT 0,
    code_smells INTEGER DEFAULT 0,
    technical_debt INTEGER DEFAULT 0,
    quality_score INTEGER DEFAULT 0,
    quality_grade TEXT DEFAULT 'F',
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (analysis_id) REFERENCES code_analysis_results(id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS code_analysis_issues (
    id TEXT PRIMARY KEY,
    analysis_id TEXT NOT NULL,
    file_result_id TEXT NOT NULL,
    project_id TEXT NOT NULL,
    issue_type TEXT NOT NULL, -- error, warning, info, style
    severity TEXT NOT NULL, -- critical, high, medium, low
    rule TEXT NOT NULL,
    message TEXT NOT NULL,
    file_path TEXT NOT NULL,
    line_number INTEGER NOT NULL,
    column_number INTEGER DEFAULT 1,
    end_line_number INTEGER,
    end_column_number INTEGER,
    is_fixable BOOLEAN DEFAULT FALSE,
    category TEXT NOT NULL, -- security, performance, maintainability, reliability, style, complexity, duplication
    suggestion TEXT,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (analysis_id) REFERENCES code_analysis_results(id) ON DELETE CASCADE,
    FOREIGN KEY (file_result_id) REFERENCES code_analysis_file_results(id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS code_reviews (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    file_path TEXT NOT NULL,
    file_hash TEXT,
    file_name TEXT,
    timestamp INTEGER NOT NULL DEFAULT (unixepoch()),
    overall_score INTEGER DEFAULT 0,
    summary TEXT,
    approved BOOLEAN DEFAULT FALSE,
    comparison_data TEXT DEFAULT '{}', -- JSON comparison with previous version
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS code_review_findings (
    id TEXT PRIMARY KEY,
    review_id TEXT NOT NULL,
    project_id TEXT NOT NULL,
    finding_type TEXT NOT NULL, -- bug, improvement, style
    severity TEXT NOT NULL, -- critical, high, medium, low
    title TEXT NOT NULL,
    description TEXT NOT NULL,
    file_path TEXT NOT NULL,
    line_number INTEGER NOT NULL,
    column_number INTEGER DEFAULT 1,
    end_line_number INTEGER,
    end_column_number INTEGER,
    suggested_fix TEXT,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (review_id) REFERENCES code_reviews(id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS code_review_suggestions (
    id TEXT PRIMARY KEY,
    review_id TEXT NOT NULL,
    project_id TEXT NOT NULL,
    title TEXT NOT NULL,
    description TEXT NOT NULL,
    priority TEXT DEFAULT 'medium', -- high, medium, low
    suggestion_type TEXT,
    file_path TEXT,
    line_number INTEGER,
    column_number INTEGER,
    example TEXT,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (review_id) REFERENCES code_reviews(id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS code_metrics_history (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    timestamp INTEGER NOT NULL DEFAULT (unixepoch()),
    branch TEXT,
    commit_hash TEXT,
    tags TEXT DEFAULT '[]', -- JSON array of tags
    -- Aggregated metrics
    average_complexity REAL DEFAULT 0.0,
    max_complexity REAL DEFAULT 0.0,
    total_complexity REAL DEFAULT 0.0,
    average_maintainability REAL DEFAULT 0.0,
    min_maintainability REAL DEFAULT 100.0,
    duplication_percentage REAL DEFAULT 0.0,
    total_issues INTEGER DEFAULT 0,
    high_issues INTEGER DEFAULT 0,
    medium_issues INTEGER DEFAULT 0,
    low_issues INTEGER DEFAULT 0,
    quality_score INTEGER DEFAULT 0,
    quality_grade TEXT DEFAULT 'F',
    -- Raw metrics data
    raw_metrics TEXT DEFAULT '{}', -- JSON full metrics data
    summary_data TEXT DEFAULT '{}', -- JSON summary data
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS code_metrics_baselines (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    metrics_history_id TEXT NOT NULL,
    name TEXT NOT NULL DEFAULT 'baseline',
    commit_hash TEXT,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (metrics_history_id) REFERENCES code_metrics_history(id) ON DELETE CASCADE,
    UNIQUE(project_id, name)
);

CREATE TABLE IF NOT EXISTS code_analysis_custom_rules (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    name TEXT NOT NULL,
    description TEXT NOT NULL,
    pattern TEXT NOT NULL, -- regex pattern
    severity TEXT NOT NULL DEFAULT 'warning', -- error, warning, info
    category TEXT NOT NULL, -- security, performance, maintainability, etc.
    message TEXT NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_code_analysis_results_project ON code_analysis_results(project_id);
CREATE INDEX IF NOT EXISTS idx_code_analysis_results_timestamp ON code_analysis_results(timestamp);
CREATE INDEX IF NOT EXISTS idx_code_analysis_file_results_analysis ON code_analysis_file_results(analysis_id);
CREATE INDEX IF NOT EXISTS idx_code_analysis_file_results_project ON code_analysis_file_results(project_id);
CREATE INDEX IF NOT EXISTS idx_code_analysis_file_results_path ON code_analysis_file_results(file_path);
CREATE INDEX IF NOT EXISTS idx_code_analysis_issues_analysis ON code_analysis_issues(analysis_id);
CREATE INDEX IF NOT EXISTS idx_code_analysis_issues_file_result ON code_analysis_issues(file_result_id);
CREATE INDEX IF NOT EXISTS idx_code_analysis_issues_severity ON code_analysis_issues(severity);
CREATE INDEX IF NOT EXISTS idx_code_analysis_issues_category ON code_analysis_issues(category);
CREATE INDEX IF NOT EXISTS idx_code_reviews_project ON code_reviews(project_id);
CREATE INDEX IF NOT EXISTS idx_code_reviews_timestamp ON code_reviews(timestamp);
CREATE INDEX IF NOT EXISTS idx_code_reviews_file_path ON code_reviews(file_path);
CREATE INDEX IF NOT EXISTS idx_code_review_findings_review ON code_review_findings(review_id);
CREATE INDEX IF NOT EXISTS idx_code_review_findings_severity ON code_review_findings(severity);
CREATE INDEX IF NOT EXISTS idx_code_review_suggestions_review ON code_review_suggestions(review_id);
CREATE INDEX IF NOT EXISTS idx_code_metrics_history_project ON code_metrics_history(project_id);
CREATE INDEX IF NOT EXISTS idx_code_metrics_history_timestamp ON code_metrics_history(timestamp);
CREATE INDEX IF NOT EXISTS idx_code_metrics_history_branch ON code_metrics_history(branch);
CREATE INDEX IF NOT EXISTS idx_code_metrics_history_commit ON code_metrics_history(commit_hash);
CREATE INDEX IF NOT EXISTS idx_code_metrics_baselines_project ON code_metrics_baselines(project_id);
CREATE INDEX IF NOT EXISTS idx_code_analysis_custom_rules_project ON code_analysis_custom_rules(project_id);
CREATE INDEX IF NOT EXISTS idx_code_analysis_custom_rules_active ON code_analysis_custom_rules(is_active);

-- Deployment Management Module
CREATE TABLE IF NOT EXISTS deployment_configs (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    provider TEXT NOT NULL, -- vercel, netlify, heroku, aws, docker, kubernetes, custom
    settings TEXT NOT NULL DEFAULT '{}', -- JSON provider settings
    hooks TEXT DEFAULT '{}', -- JSON deployment hooks
    health_checks TEXT DEFAULT '[]', -- JSON array of health checks
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS deployment_environments (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    config_id TEXT NOT NULL,
    name TEXT NOT NULL,
    type TEXT NOT NULL, -- development, staging, production, custom
    url TEXT,
    branch TEXT,
    auto_deploy_branch TEXT,
    variables TEXT DEFAULT '[]', -- JSON array of environment variables
    status TEXT NOT NULL DEFAULT 'inactive', -- active, inactive, deploying, failed, maintenance
    last_deployment_id TEXT,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (config_id) REFERENCES deployment_configs(id) ON DELETE CASCADE,
    UNIQUE(project_id, name)
);

CREATE TABLE IF NOT EXISTS deployments (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    environment_id TEXT NOT NULL,
    version TEXT NOT NULL,
    commit_hash TEXT,
    branch TEXT,
    author TEXT,
    status TEXT NOT NULL, -- pending, building, deploying, success, failed, rolled_back, cancelled
    type TEXT NOT NULL, -- manual, auto, rollback, hotfix
    started_at INTEGER NOT NULL,
    completed_at INTEGER,
    duration INTEGER,
    logs TEXT DEFAULT '[]', -- JSON array of deployment logs
    rollback_from TEXT, -- deployment ID if this is a rollback
    metadata TEXT DEFAULT '{}', -- JSON metadata
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (environment_id) REFERENCES deployment_environments(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS deployment_plans (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    environment TEXT NOT NULL,
    version TEXT NOT NULL,
    changes TEXT NOT NULL DEFAULT '[]', -- JSON array of change items
    estimated_duration INTEGER NOT NULL,
    risks TEXT NOT NULL DEFAULT '[]', -- JSON array of risks
    approval_required BOOLEAN DEFAULT FALSE,
    rollback_plan TEXT, -- JSON rollback plan
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS deployment_metrics (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    environment_id TEXT,
    total_deployments INTEGER DEFAULT 0,
    success_rate REAL DEFAULT 0.0,
    average_duration INTEGER DEFAULT 0,
    failure_reasons TEXT DEFAULT '{}', -- JSON object
    deployments_by_type TEXT DEFAULT '{}', -- JSON object
    calculated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (environment_id) REFERENCES deployment_environments(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS release_notes (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    version TEXT NOT NULL,
    date INTEGER NOT NULL,
    features TEXT DEFAULT '[]', -- JSON array
    fixes TEXT DEFAULT '[]', -- JSON array
    breaking_changes TEXT DEFAULT '[]', -- JSON array
    deprecated TEXT DEFAULT '[]', -- JSON array
    contributors TEXT DEFAULT '[]', -- JSON array
    acknowledgments TEXT DEFAULT '[]', -- JSON array
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    UNIQUE(project_id, version)
);

CREATE TABLE IF NOT EXISTS migrations (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    version TEXT NOT NULL,
    name TEXT NOT NULL,
    description TEXT,
    type TEXT NOT NULL, -- schema, data, seed, index, procedure, config
    status TEXT NOT NULL DEFAULT 'pending', -- pending, running, completed, failed, rolled_back, skipped
    checksum TEXT NOT NULL,
    up_script TEXT NOT NULL, -- JSON migration script
    down_script TEXT, -- JSON migration script
    dependencies TEXT DEFAULT '[]', -- JSON array
    tags TEXT DEFAULT '[]', -- JSON array
    author TEXT,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    executed_at INTEGER,
    duration INTEGER,
    error TEXT,
    logs TEXT DEFAULT '[]', -- JSON array of logs
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    UNIQUE(project_id, version)
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_deployment_configs_project ON deployment_configs(project_id);
CREATE INDEX IF NOT EXISTS idx_deployment_environments_project ON deployment_environments(project_id);
CREATE INDEX IF NOT EXISTS idx_deployment_environments_config ON deployment_environments(config_id);
CREATE INDEX IF NOT EXISTS idx_deployment_environments_status ON deployment_environments(status);
CREATE INDEX IF NOT EXISTS idx_deployments_project ON deployments(project_id);
CREATE INDEX IF NOT EXISTS idx_deployments_environment ON deployments(environment_id);
CREATE INDEX IF NOT EXISTS idx_deployments_status ON deployments(status);
CREATE INDEX IF NOT EXISTS idx_deployments_started ON deployments(started_at);
CREATE INDEX IF NOT EXISTS idx_deployment_plans_project ON deployment_plans(project_id);
CREATE INDEX IF NOT EXISTS idx_deployment_metrics_project ON deployment_metrics(project_id);
CREATE INDEX IF NOT EXISTS idx_release_notes_project ON release_notes(project_id);
CREATE INDEX IF NOT EXISTS idx_release_notes_version ON release_notes(version);
CREATE INDEX IF NOT EXISTS idx_migrations_project ON migrations(project_id);
CREATE INDEX IF NOT EXISTS idx_migrations_status ON migrations(status);
CREATE INDEX IF NOT EXISTS idx_migrations_version ON migrations(version);

-- Security Module Tables
CREATE TABLE IF NOT EXISTS security_policies (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    name TEXT NOT NULL,
    description TEXT,
    require_approval_for TEXT DEFAULT '[]', -- JSON array of operations
    roles TEXT DEFAULT '{}', -- JSON object of role definitions
    risk_thresholds TEXT DEFAULT '{}', -- JSON object of risk thresholds
    log_level TEXT NOT NULL DEFAULT 'info',
    enabled BOOLEAN DEFAULT TRUE,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS security_scans (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    scan_type TEXT NOT NULL, -- quick, comprehensive, deep
    status TEXT NOT NULL, -- pass, warning, fail
    total_issues INTEGER DEFAULT 0,
    critical_issues INTEGER DEFAULT 0,
    high_issues INTEGER DEFAULT 0,
    medium_issues INTEGER DEFAULT 0,
    low_issues INTEGER DEFAULT 0,
    findings TEXT DEFAULT '[]', -- JSON array of findings
    recommendations TEXT DEFAULT '[]', -- JSON array
    scan_time INTEGER NOT NULL,
    scanned_at INTEGER NOT NULL DEFAULT (unixepoch()),
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS security_vulnerabilities (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    package TEXT NOT NULL,
    version TEXT NOT NULL,
    severity TEXT NOT NULL, -- critical, high, moderate, low
    cve TEXT,
    cvss REAL,
    description TEXT,
    recommendation TEXT,
    status TEXT NOT NULL DEFAULT 'active', -- active, patched, ignored
    patched_at INTEGER,
    ignored_reason TEXT,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS security_approvals (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    operation TEXT NOT NULL,
    tool_name TEXT NOT NULL,
    request_data TEXT NOT NULL, -- JSON request data
    requester_id TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending', -- pending, approved, denied, expired
    decision_by TEXT,
    decision_at INTEGER,
    decision_reason TEXT,
    expires_at INTEGER,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

-- Indexes for security tables
CREATE INDEX IF NOT EXISTS idx_security_policies_project ON security_policies(project_id);
CREATE INDEX IF NOT EXISTS idx_security_policies_enabled ON security_policies(enabled);
CREATE INDEX IF NOT EXISTS idx_security_scans_project ON security_scans(project_id);
CREATE INDEX IF NOT EXISTS idx_security_scans_status ON security_scans(status);
CREATE INDEX IF NOT EXISTS idx_security_scans_scanned ON security_scans(scanned_at);
CREATE INDEX IF NOT EXISTS idx_security_vulnerabilities_project ON security_vulnerabilities(project_id);
CREATE INDEX IF NOT EXISTS idx_security_vulnerabilities_status ON security_vulnerabilities(status);
CREATE INDEX IF NOT EXISTS idx_security_vulnerabilities_severity ON security_vulnerabilities(severity);
CREATE INDEX IF NOT EXISTS idx_security_approvals_project ON security_approvals(project_id);
CREATE INDEX IF NOT EXISTS idx_security_approvals_status ON security_approvals(status);
CREATE INDEX IF NOT EXISTS idx_security_approvals_requester ON security_approvals(requester_id);

-- Error Analysis Module
CREATE TABLE IF NOT EXISTS error_logs (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    timestamp INTEGER NOT NULL DEFAULT (unixepoch()),
    tool_name TEXT NOT NULL,
    error_type TEXT NOT NULL, -- validation, network, permission, resource, logic, external
    severity TEXT NOT NULL, -- low, medium, high, critical
    message TEXT NOT NULL,
    stack_trace TEXT,
    context TEXT DEFAULT '{}', -- JSON context data
    user_action TEXT,
    system_state TEXT,
    root_cause TEXT DEFAULT '{}', -- JSON root cause analysis
    suggestions TEXT DEFAULT '[]', -- JSON array of recovery suggestions
    recovery_method TEXT,
    recovery_successful BOOLEAN DEFAULT FALSE,
    resolution_id TEXT,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS error_patterns (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    pattern_name TEXT NOT NULL,
    error_type TEXT NOT NULL,
    frequency TEXT NOT NULL, -- Daily, Weekly, Frequent, Occasional
    severity TEXT NOT NULL,
    description TEXT,
    affected_tools TEXT DEFAULT '[]', -- JSON array
    first_seen INTEGER NOT NULL,
    last_seen INTEGER NOT NULL,
    occurrence_count INTEGER DEFAULT 0,
    time_pattern TEXT,
    common_context TEXT,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS error_resolutions (
    id TEXT PRIMARY KEY,
    error_id TEXT NOT NULL,
    suggestion_id TEXT NOT NULL,
    project_id TEXT NOT NULL,
    implementation TEXT,
    effectiveness INTEGER, -- 1-10 rating
    notes TEXT,
    resolved_by TEXT NOT NULL,
    resolved_at INTEGER NOT NULL,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (error_id) REFERENCES error_logs(id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS error_simulations (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    error_type TEXT NOT NULL,
    severity TEXT NOT NULL,
    dry_run BOOLEAN DEFAULT TRUE,
    steps TEXT NOT NULL DEFAULT '[]', -- JSON array of simulation steps
    success BOOLEAN NOT NULL,
    duration INTEGER NOT NULL,
    effectiveness INTEGER, -- Percentage
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS error_reports (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    report_type TEXT NOT NULL, -- summary, detailed, debugging, trends
    time_range TEXT NOT NULL,
    format TEXT NOT NULL, -- markdown, json, csv
    content TEXT NOT NULL,
    file_path TEXT,
    summary_data TEXT DEFAULT '{}', -- JSON summary statistics
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

-- Indexes for error analysis tables
CREATE INDEX IF NOT EXISTS idx_error_logs_project ON error_logs(project_id);
CREATE INDEX IF NOT EXISTS idx_error_logs_timestamp ON error_logs(timestamp);
CREATE INDEX IF NOT EXISTS idx_error_logs_tool ON error_logs(tool_name);
CREATE INDEX IF NOT EXISTS idx_error_logs_type ON error_logs(error_type);
CREATE INDEX IF NOT EXISTS idx_error_logs_severity ON error_logs(severity);
CREATE INDEX IF NOT EXISTS idx_error_patterns_project ON error_patterns(project_id);
CREATE INDEX IF NOT EXISTS idx_error_patterns_type ON error_patterns(error_type);
CREATE INDEX IF NOT EXISTS idx_error_resolutions_error ON error_resolutions(error_id);
CREATE INDEX IF NOT EXISTS idx_error_resolutions_project ON error_resolutions(project_id);
CREATE INDEX IF NOT EXISTS idx_error_simulations_project ON error_simulations(project_id);
CREATE INDEX IF NOT EXISTS idx_error_reports_project ON error_reports(project_id);

-- RAG Retrieval Module
CREATE TABLE IF NOT EXISTS rag_documents (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    path TEXT NOT NULL,
    content TEXT NOT NULL,
    title TEXT NOT NULL,
    size INTEGER NOT NULL,
    last_modified INTEGER NOT NULL,
    author TEXT,
    tags TEXT DEFAULT '[]', -- JSON array
    version TEXT,
    language TEXT,
    custom_metadata TEXT DEFAULT '{}', -- JSON object
    embedding_status TEXT DEFAULT 'pending', -- pending, processing, completed, failed
    chunk_count INTEGER DEFAULT 0,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    UNIQUE(project_id, path)
);

CREATE TABLE IF NOT EXISTS rag_chunks (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    document_id TEXT NOT NULL,
    content TEXT NOT NULL,
    chunk_index INTEGER NOT NULL, -- Position in document
    start_offset INTEGER NOT NULL,
    end_offset INTEGER NOT NULL,
    chunk_type TEXT NOT NULL, -- paragraph, header, code, list, blockquote
    chunk_level INTEGER, -- For headers
    language TEXT, -- For code blocks
    metadata TEXT DEFAULT '{}', -- JSON object for custom metadata
    embedding_vector BLOB, -- Stored as binary
    embedding_model TEXT,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (document_id) REFERENCES rag_documents(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS rag_collections (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    name TEXT NOT NULL,
    description TEXT,
    paths TEXT NOT NULL DEFAULT '[]', -- JSON array of paths
    document_count INTEGER DEFAULT 0,
    chunk_count INTEGER DEFAULT 0,
    metadata TEXT DEFAULT '{}', -- JSON object
    last_indexed INTEGER,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    UNIQUE(project_id, name)
);

CREATE TABLE IF NOT EXISTS rag_collection_documents (
    collection_id TEXT NOT NULL,
    document_id TEXT NOT NULL,
    added_at INTEGER NOT NULL DEFAULT (unixepoch()),
    PRIMARY KEY (collection_id, document_id),
    FOREIGN KEY (collection_id) REFERENCES rag_collections(id) ON DELETE CASCADE,
    FOREIGN KEY (document_id) REFERENCES rag_documents(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS rag_embeddings_cache (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    text_hash TEXT NOT NULL, -- SHA256 hash of the text
    model_name TEXT NOT NULL,
    embedding_vector BLOB NOT NULL, -- Stored as binary
    dimension INTEGER NOT NULL,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    last_used INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    UNIQUE(project_id, text_hash, model_name)
);

CREATE TABLE IF NOT EXISTS rag_search_history (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    query TEXT NOT NULL,
    limit_count INTEGER NOT NULL,
    threshold REAL,
    filters TEXT DEFAULT '{}', -- JSON object
    result_count INTEGER NOT NULL,
    execution_time INTEGER NOT NULL, -- milliseconds
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS rag_config (
    project_id TEXT PRIMARY KEY,
    enabled BOOLEAN DEFAULT TRUE,
    index_paths TEXT DEFAULT '[]', -- JSON array
    chunk_size INTEGER DEFAULT 500,
    chunk_overlap INTEGER DEFAULT 50,
    embedding_model TEXT DEFAULT 'all-MiniLM-L6-v2',
    auto_index BOOLEAN DEFAULT FALSE,
    cache_embeddings BOOLEAN DEFAULT TRUE,
    persistence_path TEXT,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

-- Indexes for RAG performance
CREATE INDEX IF NOT EXISTS idx_rag_documents_project ON rag_documents(project_id);
CREATE INDEX IF NOT EXISTS idx_rag_documents_path ON rag_documents(path);
CREATE INDEX IF NOT EXISTS idx_rag_documents_status ON rag_documents(embedding_status);
CREATE INDEX IF NOT EXISTS idx_rag_chunks_document ON rag_chunks(document_id);
CREATE INDEX IF NOT EXISTS idx_rag_chunks_project ON rag_chunks(project_id);
CREATE INDEX IF NOT EXISTS idx_rag_chunks_type ON rag_chunks(chunk_type);
CREATE INDEX IF NOT EXISTS idx_rag_collections_project ON rag_collections(project_id);
CREATE INDEX IF NOT EXISTS idx_rag_collection_documents_collection ON rag_collection_documents(collection_id);
CREATE INDEX IF NOT EXISTS idx_rag_collection_documents_document ON rag_collection_documents(document_id);
CREATE INDEX IF NOT EXISTS idx_rag_embeddings_cache_project ON rag_embeddings_cache(project_id);
CREATE INDEX IF NOT EXISTS idx_rag_embeddings_cache_hash ON rag_embeddings_cache(text_hash);
CREATE INDEX IF NOT EXISTS idx_rag_embeddings_cache_model ON rag_embeddings_cache(model_name);
CREATE INDEX IF NOT EXISTS idx_rag_search_history_project ON rag_search_history(project_id);
CREATE INDEX IF NOT EXISTS idx_rag_search_history_created ON rag_search_history(created_at);

CREATE TABLE IF NOT EXISTS performance_quality_evaluations (
    id TEXT PRIMARY KEY,
    tool_name TEXT NOT NULL,
    execution_id TEXT,
    output_quality INTEGER NOT NULL CHECK (output_quality >= 1 AND output_quality <= 10),
    appropriateness_score INTEGER NOT NULL CHECK (appropriateness_score >= 1 AND appropriateness_score <= 10),
    completeness_score INTEGER NOT NULL CHECK (completeness_score >= 1 AND completeness_score <= 10),
    accuracy_score INTEGER NOT NULL CHECK (accuracy_score >= 1 AND accuracy_score <= 10),
    comments TEXT,
    evaluation_criteria TEXT DEFAULT '[]', -- JSON array
    timestamp INTEGER NOT NULL DEFAULT (unixepoch()),
    created_at INTEGER NOT NULL DEFAULT (unixepoch())
);

CREATE TABLE IF NOT EXISTS performance_alerts (
    id TEXT PRIMARY KEY,
    type TEXT NOT NULL, -- performance, errors, quality, costs, security
    severity TEXT NOT NULL, -- low, medium, high, critical
    title TEXT NOT NULL,
    message TEXT NOT NULL,
    suggestion TEXT,
    tool_name TEXT,
    metric_value REAL,
    threshold_value REAL,
    acknowledged BOOLEAN DEFAULT FALSE,
    acknowledged_by TEXT,
    acknowledged_at INTEGER,
    created_at INTEGER NOT NULL DEFAULT (unixepoch())
);

CREATE TABLE IF NOT EXISTS performance_exports (
    id TEXT PRIMARY KEY,
    format TEXT NOT NULL, -- csv, json
    time_range TEXT NOT NULL,
    include_quality BOOLEAN DEFAULT TRUE,
    file_path TEXT NOT NULL,
    export_data BLOB,
    created_at INTEGER NOT NULL DEFAULT (unixepoch())
);

CREATE INDEX IF NOT EXISTS idx_performance_quality_evaluations_tool ON performance_quality_evaluations(tool_name);
CREATE INDEX IF NOT EXISTS idx_performance_quality_evaluations_timestamp ON performance_quality_evaluations(timestamp);
CREATE INDEX IF NOT EXISTS idx_performance_quality_evaluations_execution ON performance_quality_evaluations(execution_id);

CREATE INDEX IF NOT EXISTS idx_performance_alerts_type ON performance_alerts(type);
CREATE INDEX IF NOT EXISTS idx_performance_alerts_severity ON performance_alerts(severity);
CREATE INDEX IF NOT EXISTS idx_performance_alerts_acknowledged ON performance_alerts(acknowledged);
CREATE INDEX IF NOT EXISTS idx_performance_alerts_created ON performance_alerts(created_at);

CREATE INDEX IF NOT EXISTS idx_performance_exports_created ON performance_exports(created_at);

-- Workflow Recipes Module
CREATE TABLE IF NOT EXISTS workflow_recipes (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    name TEXT NOT NULL,
    description TEXT NOT NULL,
    category TEXT NOT NULL, -- development, planning, security, quality, deployment, custom
    risk_level TEXT NOT NULL DEFAULT 'medium', -- low, medium, high
    steps TEXT NOT NULL DEFAULT '[]', -- JSON array of step objects
    prerequisites TEXT DEFAULT '[]', -- JSON array of prerequisite strings
    created_by TEXT NOT NULL,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    UNIQUE(project_id, name)
);

CREATE TABLE IF NOT EXISTS workflow_executions (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    workflow_recipe_id TEXT NOT NULL,
    workflow_name TEXT NOT NULL,
    parameters TEXT DEFAULT '{}', -- JSON object of execution parameters
    dry_run BOOLEAN DEFAULT FALSE,
    auto_execute BOOLEAN DEFAULT FALSE,
    status TEXT NOT NULL DEFAULT 'pending', -- pending, in_progress, completed, failed, cancelled, validated
    current_step INTEGER DEFAULT 0,
    total_steps INTEGER NOT NULL,
    results TEXT DEFAULT '[]', -- JSON array of step results
    error_message TEXT,
    started_at INTEGER,
    completed_at INTEGER,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (workflow_recipe_id) REFERENCES workflow_recipes(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS workflow_step_history (
    id TEXT PRIMARY KEY,
    execution_id TEXT NOT NULL,
    step_index INTEGER NOT NULL,
    step_name TEXT NOT NULL,
    status TEXT NOT NULL, -- started, completed, failed, skipped
    started_at INTEGER NOT NULL,
    completed_at INTEGER,
    duration INTEGER, -- milliseconds
    output TEXT,
    error_message TEXT,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (execution_id) REFERENCES workflow_executions(id) ON DELETE CASCADE
);

-- Indexes for workflow recipes
CREATE INDEX IF NOT EXISTS idx_workflow_recipes_project ON workflow_recipes(project_id);
CREATE INDEX IF NOT EXISTS idx_workflow_recipes_category ON workflow_recipes(category);
CREATE INDEX IF NOT EXISTS idx_workflow_recipes_risk ON workflow_recipes(risk_level);
CREATE INDEX IF NOT EXISTS idx_workflow_recipes_name ON workflow_recipes(name);

CREATE INDEX IF NOT EXISTS idx_workflow_executions_project ON workflow_executions(project_id);
CREATE INDEX IF NOT EXISTS idx_workflow_executions_recipe ON workflow_executions(workflow_recipe_id);
CREATE INDEX IF NOT EXISTS idx_workflow_executions_status ON workflow_executions(status);
CREATE INDEX IF NOT EXISTS idx_workflow_executions_created ON workflow_executions(created_at);

CREATE INDEX IF NOT EXISTS idx_workflow_step_history_execution ON workflow_step_history(execution_id);
CREATE INDEX IF NOT EXISTS idx_workflow_step_history_status ON workflow_step_history(status);

-- Data Management Module
CREATE TABLE IF NOT EXISTS data_exports (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    export_path TEXT NOT NULL,
    format TEXT NOT NULL DEFAULT 'json', -- json, sql
    total_items INTEGER NOT NULL DEFAULT 0,
    modules TEXT NOT NULL DEFAULT '[]', -- JSON array of exported modules
    include_config BOOLEAN DEFAULT TRUE,
    include_media BOOLEAN DEFAULT FALSE,
    status TEXT NOT NULL DEFAULT 'pending', -- pending, in_progress, completed, completed_with_errors, failed
    file_size INTEGER, -- Size in bytes
    compression_used BOOLEAN DEFAULT FALSE,
    encryption_used BOOLEAN DEFAULT FALSE,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    completed_at INTEGER,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS data_imports (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    source_path TEXT NOT NULL,
    source_type TEXT NOT NULL DEFAULT 'atlas_export', -- atlas_export, csv, json, xml, sql, api
    merge_strategy TEXT NOT NULL DEFAULT 'skip_existing', -- overwrite, skip_existing, merge
    imported_items INTEGER NOT NULL DEFAULT 0,
    imported_modules TEXT NOT NULL DEFAULT '[]', -- JSON array of imported modules
    backup_path TEXT, -- Path to backup created before import
    mapping TEXT DEFAULT '{}', -- JSON object for field mapping
    validation_results TEXT DEFAULT '{}', -- JSON object with validation results
    status TEXT NOT NULL DEFAULT 'pending', -- pending, in_progress, completed, completed_with_errors, failed
    error_details TEXT, -- JSON array of errors
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    completed_at INTEGER,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS storage_configs (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    mode TEXT NOT NULL, -- project-local, user-home, custom
    custom_path TEXT,
    isolation TEXT NOT NULL DEFAULT 'project', -- project, user, global
    active BOOLEAN DEFAULT TRUE,
    settings TEXT DEFAULT '{}', -- JSON object for additional settings
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS module_exports (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    module_name TEXT NOT NULL,
    format TEXT NOT NULL DEFAULT 'json', -- json, markdown, csv, sql
    file_path TEXT NOT NULL,
    item_count INTEGER NOT NULL DEFAULT 0,
    include_history BOOLEAN DEFAULT FALSE,
    date_range TEXT, -- JSON object with start/end dates
    filters TEXT DEFAULT '{}', -- JSON object with applied filters
    file_size INTEGER, -- Size in bytes
    checksum TEXT, -- File integrity checksum
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS data_transformations (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    source_module TEXT NOT NULL,
    target_module TEXT NOT NULL,
    transformation_type TEXT NOT NULL, -- migrate, duplicate, merge, convert
    mapping TEXT NOT NULL DEFAULT '{}', -- JSON object mapping source to target fields
    custom_transform TEXT, -- Custom transformation logic
    source_count INTEGER DEFAULT 0,
    target_count INTEGER DEFAULT 0,
    success_count INTEGER DEFAULT 0,
    error_count INTEGER DEFAULT 0,
    validation_errors TEXT DEFAULT '[]', -- JSON array of validation errors
    status TEXT NOT NULL DEFAULT 'pending', -- pending, in_progress, completed, failed
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    completed_at INTEGER,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS data_backups (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    backup_path TEXT NOT NULL,
    backup_type TEXT NOT NULL DEFAULT 'full', -- full, incremental, differential
    modules TEXT DEFAULT '[]', -- JSON array of included modules
    include_files BOOLEAN DEFAULT FALSE,
    compression_used BOOLEAN DEFAULT FALSE,
    encryption_used BOOLEAN DEFAULT FALSE,
    retention_days INTEGER DEFAULT 30,
    file_size INTEGER, -- Size in bytes
    checksum TEXT, -- Backup integrity checksum
    status TEXT NOT NULL DEFAULT 'pending', -- pending, in_progress, completed, failed
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    expires_at INTEGER, -- Auto-deletion timestamp
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS data_integrity_checks (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    check_type TEXT NOT NULL, -- full, incremental, relationships, constraints
    modules TEXT DEFAULT '[]', -- JSON array of checked modules
    issues_found INTEGER DEFAULT 0,
    issues_fixed INTEGER DEFAULT 0,
    check_results TEXT DEFAULT '{}', -- JSON object with detailed results
    auto_fix_applied BOOLEAN DEFAULT FALSE,
    status TEXT NOT NULL DEFAULT 'pending', -- pending, running, completed, failed
    started_at INTEGER NOT NULL DEFAULT (unixepoch()),
    completed_at INTEGER,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS data_sync_logs (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    sync_type TEXT NOT NULL, -- to_project, from_project, cloud, remote
    destination TEXT NOT NULL,
    modules TEXT DEFAULT '[]', -- JSON array of synced modules
    format TEXT NOT NULL DEFAULT 'json', -- json, markdown
    file_count INTEGER DEFAULT 0,
    total_size INTEGER DEFAULT 0, -- Size in bytes
    status TEXT NOT NULL DEFAULT 'pending', -- pending, in_progress, completed, failed
    error_details TEXT, -- JSON array of errors
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    completed_at INTEGER,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

-- Indexes for data management performance
CREATE INDEX IF NOT EXISTS idx_data_exports_project ON data_exports(project_id);
CREATE INDEX IF NOT EXISTS idx_data_exports_status ON data_exports(status);
CREATE INDEX IF NOT EXISTS idx_data_exports_created ON data_exports(created_at);

CREATE INDEX IF NOT EXISTS idx_data_imports_project ON data_imports(project_id);
CREATE INDEX IF NOT EXISTS idx_data_imports_status ON data_imports(status);
CREATE INDEX IF NOT EXISTS idx_data_imports_created ON data_imports(created_at);
CREATE INDEX IF NOT EXISTS idx_data_imports_source_type ON data_imports(source_type);

CREATE INDEX IF NOT EXISTS idx_storage_configs_project ON storage_configs(project_id);
CREATE INDEX IF NOT EXISTS idx_storage_configs_active ON storage_configs(active);
CREATE INDEX IF NOT EXISTS idx_storage_configs_mode ON storage_configs(mode);

CREATE INDEX IF NOT EXISTS idx_module_exports_project ON module_exports(project_id);
CREATE INDEX IF NOT EXISTS idx_module_exports_module ON module_exports(module_name);
CREATE INDEX IF NOT EXISTS idx_module_exports_created ON module_exports(created_at);

CREATE INDEX IF NOT EXISTS idx_data_transformations_project ON data_transformations(project_id);
CREATE INDEX IF NOT EXISTS idx_data_transformations_status ON data_transformations(status);
CREATE INDEX IF NOT EXISTS idx_data_transformations_type ON data_transformations(transformation_type);

CREATE INDEX IF NOT EXISTS idx_data_backups_project ON data_backups(project_id);
CREATE INDEX IF NOT EXISTS idx_data_backups_status ON data_backups(status);
CREATE INDEX IF NOT EXISTS idx_data_backups_expires ON data_backups(expires_at);

CREATE INDEX IF NOT EXISTS idx_data_integrity_checks_project ON data_integrity_checks(project_id);
CREATE INDEX IF NOT EXISTS idx_data_integrity_checks_status ON data_integrity_checks(status);
CREATE INDEX IF NOT EXISTS idx_data_integrity_checks_started ON data_integrity_checks(started_at);

CREATE INDEX IF NOT EXISTS idx_data_sync_logs_project ON data_sync_logs(project_id);
CREATE INDEX IF NOT EXISTS idx_data_sync_logs_status ON data_sync_logs(status);
CREATE INDEX IF NOT EXISTS idx_data_sync_logs_created ON data_sync_logs(created_at);

-- Local AI Module (Semantic Search and Code Intelligence)
CREATE TABLE IF NOT EXISTS ai_embeddings (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    file_path TEXT NOT NULL,
    content TEXT NOT NULL,
    language TEXT NOT NULL,
    features TEXT DEFAULT '[]', -- JSON array of extracted features
    functions TEXT DEFAULT '[]', -- JSON array of function names
    imports TEXT DEFAULT '[]', -- JSON array of import statements
    embedding_data TEXT NOT NULL, -- JSON embedding vector and metadata
    file_size INTEGER NOT NULL DEFAULT 0,
    last_modified INTEGER NOT NULL,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    UNIQUE(project_id, file_path)
);

CREATE TABLE IF NOT EXISTS ai_indexing_sessions (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    directory TEXT NOT NULL,
    files_found INTEGER NOT NULL DEFAULT 0,
    files_indexed INTEGER NOT NULL DEFAULT 0,
    files_failed INTEGER NOT NULL DEFAULT 0,
    extensions TEXT DEFAULT '[]', -- JSON array of file extensions
    errors TEXT DEFAULT '[]', -- JSON array of error messages
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS ai_search_sessions (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    query TEXT NOT NULL,
    results_found INTEGER NOT NULL DEFAULT 0,
    top_similarity REAL DEFAULT 0.0,
    filters TEXT DEFAULT '{}', -- JSON object of search filters
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS ai_code_changes (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    file_path TEXT NOT NULL,
    change_type TEXT NOT NULL, -- create, modify, delete, legacy_import
    language TEXT,
    features_added TEXT DEFAULT '[]', -- JSON array of features
    functions_added TEXT DEFAULT '[]', -- JSON array of functions
    metadata TEXT DEFAULT '{}', -- JSON object of additional metadata
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS ai_dependency_analyses (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    target_file TEXT NOT NULL,
    depth INTEGER NOT NULL DEFAULT 2,
    dependencies TEXT DEFAULT '[]', -- JSON array of dependencies
    circular_deps TEXT DEFAULT '[]', -- JSON array of circular dependencies
    hotspots TEXT DEFAULT '[]', -- JSON array of code hotspots
    analysis_data TEXT DEFAULT '{}', -- JSON object of full analysis
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

-- Indexes for Local AI performance
CREATE INDEX IF NOT EXISTS idx_ai_embeddings_project ON ai_embeddings(project_id);
CREATE INDEX IF NOT EXISTS idx_ai_embeddings_file_path ON ai_embeddings(file_path);
CREATE INDEX IF NOT EXISTS idx_ai_embeddings_language ON ai_embeddings(language);
CREATE INDEX IF NOT EXISTS idx_ai_embeddings_modified ON ai_embeddings(last_modified);

CREATE INDEX IF NOT EXISTS idx_ai_indexing_sessions_project ON ai_indexing_sessions(project_id);
CREATE INDEX IF NOT EXISTS idx_ai_indexing_sessions_created ON ai_indexing_sessions(created_at);

CREATE INDEX IF NOT EXISTS idx_ai_search_sessions_project ON ai_search_sessions(project_id);
CREATE INDEX IF NOT EXISTS idx_ai_search_sessions_created ON ai_search_sessions(created_at);
CREATE INDEX IF NOT EXISTS idx_ai_search_sessions_similarity ON ai_search_sessions(top_similarity);

CREATE INDEX IF NOT EXISTS idx_ai_code_changes_project ON ai_code_changes(project_id);
CREATE INDEX IF NOT EXISTS idx_ai_code_changes_file ON ai_code_changes(file_path);
CREATE INDEX IF NOT EXISTS idx_ai_code_changes_type ON ai_code_changes(change_type);
CREATE INDEX IF NOT EXISTS idx_ai_code_changes_created ON ai_code_changes(created_at);

CREATE INDEX IF NOT EXISTS idx_ai_dependency_analyses_project ON ai_dependency_analyses(project_id);
CREATE INDEX IF NOT EXISTS idx_ai_dependency_analyses_file ON ai_dependency_analyses(target_file);
CREATE INDEX IF NOT EXISTS idx_ai_dependency_analyses_created ON ai_dependency_analyses(created_at);

-- Phase 2 Agile Management Tables

-- Epic Metrics for tracking progress over time
CREATE TABLE IF NOT EXISTS agile_epic_metrics (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    epic_id TEXT NOT NULL,
    calculation_date INTEGER NOT NULL DEFAULT (unixepoch()),
    stories_total INTEGER DEFAULT 0,
    stories_completed INTEGER DEFAULT 0,
    stories_in_progress INTEGER DEFAULT 0,
    stories_blocked INTEGER DEFAULT 0,
    story_points_total INTEGER DEFAULT 0,
    story_points_completed INTEGER DEFAULT 0,
    completion_percentage REAL DEFAULT 0.0,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (epic_id) REFERENCES agile_epics(id) ON DELETE CASCADE
);

-- Sprint Reports for advanced analytics
CREATE TABLE IF NOT EXISTS agile_sprint_reports (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    sprint_id TEXT NOT NULL,
    report_type TEXT NOT NULL, -- burndown, velocity, cycle_time, retrospective
    report_data TEXT NOT NULL, -- JSON data
    generated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    generated_by TEXT,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (sprint_id) REFERENCES agile_sprints(id) ON DELETE CASCADE
);

-- Story Lifecycle Events for cycle time analysis
CREATE TABLE IF NOT EXISTS agile_story_lifecycle (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    story_id TEXT NOT NULL,
    from_status TEXT,
    to_status TEXT NOT NULL,
    changed_by TEXT,
    changed_at INTEGER NOT NULL DEFAULT (unixepoch()),
    duration_in_status INTEGER, -- seconds in previous status
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (story_id) REFERENCES agile_stories(id) ON DELETE CASCADE
);

-- Team Velocity Tracking
CREATE TABLE IF NOT EXISTS agile_team_velocity (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    sprint_id TEXT NOT NULL,
    team_id TEXT,
    planned_points INTEGER DEFAULT 0,
    completed_points INTEGER DEFAULT 0,
    velocity_ratio REAL DEFAULT 0.0,
    calculated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (sprint_id) REFERENCES agile_sprints(id) ON DELETE CASCADE
);

-- Phase 2 Indexes
CREATE INDEX IF NOT EXISTS idx_agile_epic_metrics_epic ON agile_epic_metrics(epic_id);
CREATE INDEX IF NOT EXISTS idx_agile_epic_metrics_date ON agile_epic_metrics(calculation_date);
CREATE INDEX IF NOT EXISTS idx_agile_sprint_reports_sprint ON agile_sprint_reports(sprint_id);
CREATE INDEX IF NOT EXISTS idx_agile_sprint_reports_type ON agile_sprint_reports(report_type);
CREATE INDEX IF NOT EXISTS idx_agile_story_lifecycle_story ON agile_story_lifecycle(story_id);
CREATE INDEX IF NOT EXISTS idx_agile_story_lifecycle_status ON agile_story_lifecycle(to_status);
CREATE INDEX IF NOT EXISTS idx_agile_team_velocity_sprint ON agile_team_velocity(sprint_id);
CREATE INDEX IF NOT EXISTS idx_agile_team_velocity_team ON agile_team_velocity(team_id);