-- Migration 028: Backfill recommendation_evidence for existing recommendations
--
-- The recommendation worker had a bug where non-UUID document IDs (synthetic
-- "pattern:..." IDs from competitive signal propagation) caused the entire
-- executemany INSERT to fail, silently dropping ALL evidence rows.
--
-- This migration re-links recommendations to their evidence using the
-- trend_windows data. For each recommendation with no evidence, we find
-- the closest matching trend_window and insert its document references.

-- Step 1: Create a temp table with the backfill data
CREATE TEMP TABLE _backfill_evidence AS
WITH recs_without_evidence AS (
    SELECT r.id AS rec_id, r.ticker, r.time_horizon, r.generated_at
    FROM recommendations r
    WHERE NOT EXISTS (
        SELECT 1 FROM recommendation_evidence re WHERE re.recommendation_id = r.id
    )
),
matched_trends AS (
    SELECT DISTINCT ON (rwe.rec_id)
        rwe.rec_id,
        tw.top_supporting_evidence,
        tw.top_opposing_evidence
    FROM recs_without_evidence rwe
    JOIN trend_windows tw
      ON tw.entity_id = rwe.ticker
     AND tw.window = rwe.time_horizon
    ORDER BY rwe.rec_id, ABS(EXTRACT(EPOCH FROM (tw.generated_at - rwe.generated_at)))
),
supporting AS (
    SELECT
        mt.rec_id,
        elem.doc_id,
        'supporting'::text AS evidence_type,
        elem.idx
    FROM matched_trends mt,
    LATERAL jsonb_array_elements_text(COALESCE(mt.top_supporting_evidence, '[]'::jsonb))
        WITH ORDINALITY AS elem(doc_id, idx)
    WHERE elem.doc_id ~ '^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$'
),
opposing AS (
    SELECT
        mt.rec_id,
        elem.doc_id,
        'opposing'::text AS evidence_type,
        elem.idx
    FROM matched_trends mt,
    LATERAL jsonb_array_elements_text(COALESCE(mt.top_opposing_evidence, '[]'::jsonb))
        WITH ORDINALITY AS elem(doc_id, idx)
    WHERE elem.doc_id ~ '^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$'
),
all_evidence AS (
    SELECT * FROM supporting
    UNION ALL
    SELECT * FROM opposing
)
SELECT
    ae.rec_id AS recommendation_id,
    ae.doc_id::uuid AS document_id,
    ae.evidence_type,
    ROUND((1.0 / (1.0 + (ae.idx - 1) * 0.1))::numeric, 4) AS weight
FROM all_evidence ae
WHERE EXISTS (SELECT 1 FROM documents d WHERE d.id = ae.doc_id::uuid);

-- Step 2: Insert the backfill data
INSERT INTO recommendation_evidence (recommendation_id, document_id, evidence_type, weight)
SELECT recommendation_id, document_id, evidence_type, weight
FROM _backfill_evidence
ON CONFLICT DO NOTHING;

-- Step 3: Clean up
DROP TABLE _backfill_evidence;