-- Migration 001: Add opportunities table and wire up channel_analysis_runs
-- Created: 2026-02-12
-- Purpose: Persist Layer 3/3.5/4 analysis results in database

-- ============================================================================
-- STEP 1: Extend channel_analysis_runs with additional tracking columns
-- ============================================================================

ALTER TABLE channel_analysis_runs ADD COLUMN clusters_formed INTEGER;
ALTER TABLE channel_analysis_runs ADD COLUMN layer2_passed INTEGER;
ALTER TABLE channel_analysis_runs ADD COLUMN completed_at TEXT;
ALTER TABLE channel_analysis_runs ADD COLUMN error_message TEXT;

-- ============================================================================
-- STEP 2: Create opportunities table
-- ============================================================================

CREATE TABLE IF NOT EXISTS opportunities (
    id INTEGER PRIMARY KEY AUTOINCREMENT,

    -- Relationships
    channel_id TEXT NOT NULL,
    analysis_run_id INTEGER NOT NULL,
    cluster_id INTEGER,

    -- Core identification
    theme TEXT,
    suggested_title TEXT,
    source_item_count INTEGER,  -- How many raw items were in the cluster

    -- Scoring
    verdict TEXT,  -- HIGH_PRIORITY, WORTH_MAKING, CONDITIONAL, SKIP
    weighted_score REAL,
    scores_json TEXT,  -- Full scoring breakdown (demand_signal, content_gap, etc.)

    -- Layer 3 analysis output (stored as JSON for flexibility)
    analysis_json TEXT,  -- confusion_analysis, existing_content, missing_angle,
                         -- target_audience, structure, opening_hook, etc.

    -- Layer 3.5 research
    research_status TEXT DEFAULT 'pending',  -- pending, completed, failed, skipped
    research_json TEXT,  -- Full research report + metadata

    -- Layer 4 video generation
    video_gen_status TEXT DEFAULT 'pending',  -- pending, completed, failed, skipped
    video_concepts_json TEXT,   -- Layer 4a output
    production_spec_json TEXT,  -- Layer 4b output
    asset_specs_json TEXT,      -- Layer 4c output

    -- Metadata
    prompt_version TEXT,
    created_at TEXT NOT NULL,
    updated_at TEXT,

    -- Foreign keys
    FOREIGN KEY (channel_id) REFERENCES channels(id),
    FOREIGN KEY (analysis_run_id) REFERENCES channel_analysis_runs(id)
);

-- ============================================================================
-- STEP 3: Create indexes for common query patterns
-- ============================================================================

-- Filter by channel (dashboard default view)
CREATE INDEX IF NOT EXISTS idx_opportunities_channel ON opportunities(channel_id);

-- Filter by analysis run (view historical runs)
CREATE INDEX IF NOT EXISTS idx_opportunities_run ON opportunities(analysis_run_id);

-- Filter by verdict (prioritization)
CREATE INDEX IF NOT EXISTS idx_opportunities_verdict ON opportunities(verdict);

-- Sort by score (ranking)
CREATE INDEX IF NOT EXISTS idx_opportunities_score ON opportunities(weighted_score DESC);

-- Find opportunities needing research
CREATE INDEX IF NOT EXISTS idx_opportunities_research_status ON opportunities(research_status);

-- Find opportunities needing video gen
CREATE INDEX IF NOT EXISTS idx_opportunities_video_status ON opportunities(video_gen_status);

-- Compound index for common dashboard query: latest run for channel, sorted by score
CREATE INDEX IF NOT EXISTS idx_opportunities_channel_run_score
    ON opportunities(channel_id, analysis_run_id, weighted_score DESC);
