-- Keep one canonical archived forecast per event_id.
-- The newest row wins because reruns can refine the forecast before lock.
WITH ranked AS (
  SELECT id,
         event_id,
         blog_post_id,
         ROW_NUMBER() OVER (
           PARTITION BY event_id
           ORDER BY created_at DESC NULLS LAST, updated_at DESC NULLS LAST, id DESC
         ) AS rn
  FROM rm_archived_forecasts
),
keepers AS (
  SELECT event_id, id AS keeper_id
  FROM ranked
  WHERE rn = 1
),
keeper_blog_posts AS (
  SELECT k.keeper_id,
         (ARRAY_AGG(r.blog_post_id) FILTER (WHERE r.blog_post_id IS NOT NULL))[1] AS blog_post_id
  FROM keepers k
  JOIN ranked r ON r.event_id = k.event_id
  GROUP BY k.keeper_id
)
UPDATE rm_archived_forecasts af
SET blog_post_id = kb.blog_post_id,
    updated_at = NOW()
FROM keeper_blog_posts kb
WHERE af.id = kb.keeper_id
  AND kb.blog_post_id IS NOT NULL
  AND af.blog_post_id IS NULL;

WITH ranked AS (
  SELECT id,
         event_id,
         ROW_NUMBER() OVER (
           PARTITION BY event_id
           ORDER BY created_at DESC NULLS LAST, updated_at DESC NULLS LAST, id DESC
         ) AS rn
  FROM rm_archived_forecasts
),
keepers AS (
  SELECT event_id, id AS keeper_id
  FROM ranked
  WHERE rn = 1
),
duplicates AS (
  SELECT r.id AS duplicate_id, k.keeper_id
  FROM ranked r
  JOIN keepers k ON k.event_id = r.event_id
  WHERE r.rn > 1
)
UPDATE rm_blog_posts bp
SET archived_forecast_id = d.keeper_id,
    updated_at = NOW()
FROM duplicates d
WHERE bp.archived_forecast_id = d.duplicate_id
  AND bp.archived_forecast_id IS DISTINCT FROM d.keeper_id;

WITH ranked AS (
  SELECT id,
         ROW_NUMBER() OVER (
           PARTITION BY event_id
           ORDER BY created_at DESC NULLS LAST, updated_at DESC NULLS LAST, id DESC
         ) AS rn
  FROM rm_archived_forecasts
)
DELETE FROM rm_archived_forecasts af
USING ranked r
WHERE af.id = r.id
  AND r.rn > 1;

DROP INDEX IF EXISTS idx_rm_archived_event_id;

CREATE UNIQUE INDEX IF NOT EXISTS idx_rm_archived_event_id_unique
  ON rm_archived_forecasts (event_id);
