-- Fix trend_windows to upsert instead of accumulating rows.
-- First, preserve all historical data in trend_history before deduplicating.

-- Step 1: Create trend_history table if it doesn't exist yet
-- (migration 024 also creates it, but we need it here first to preserve data)
CREATE TABLE IF NOT EXISTS trend_history (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    entity_type VARCHAR(50) NOT NULL DEFAULT 'company',
    entity_id VARCHAR(100) NOT NULL,
    "window" VARCHAR(20) NOT NULL,
    trend_direction VARCHAR(20) NOT NULL DEFAULT 'neutral',
    trend_strength FLOAT DEFAULT 0.5,
    confidence FLOAT DEFAULT 0.5,
    contradiction_score FLOAT DEFAULT 0.0,
    dominant_catalysts JSONB DEFAULT '[]',
    material_risks JSONB DEFAULT '[]',
    generated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_trend_history_lookup
    ON trend_history (entity_id, "window", generated_at DESC);

CREATE INDEX IF NOT EXISTS idx_trend_history_generated
    ON trend_history (generated_at DESC);

-- Step 2: Copy ALL existing trend_windows rows into trend_history
-- before we delete any. This preserves the full history for charting.
INSERT INTO trend_history (
    entity_type, entity_id, "window", trend_direction,
    trend_strength, confidence, contradiction_score,
    dominant_catalysts, material_risks, generated_at
)
SELECT
    entity_type, entity_id, "window", trend_direction,
    trend_strength, confidence, contradiction_score,
    dominant_catalysts, material_risks, generated_at
FROM trend_windows
WHERE NOT EXISTS (
    SELECT 1 FROM trend_history th
    WHERE th.entity_id = trend_windows.entity_id
      AND th."window" = trend_windows."window"
      AND th.generated_at = trend_windows.generated_at
);

-- Step 3: Keep only the most recent row per (entity_type, entity_id, window)
-- in trend_windows (it becomes the "latest snapshot" table)
DELETE FROM trend_windows
WHERE id NOT IN (
    SELECT DISTINCT ON (entity_type, entity_id, "window") id
    FROM trend_windows
    ORDER BY entity_type, entity_id, "window", generated_at DESC
);

-- Step 4: Add unique constraint for upsert
CREATE UNIQUE INDEX IF NOT EXISTS idx_trend_windows_entity_window
    ON trend_windows (entity_type, entity_id, "window");

-- Step 5: Clean up old competitive signal records (keep last 30 days)
DELETE FROM competitive_signal_records
WHERE computed_at < NOW() - INTERVAL '30 days';

-- Step 6: Add a partial index to speed up the NOT EXISTS check in the
-- aggregation propagation query
CREATE INDEX IF NOT EXISTS idx_competitive_signals_source_doc_ticker
    ON competitive_signal_records (source_document_id, source_ticker);