-- Remove duplicate user/event pick rows before enforcing uniqueness.
WITH ranked AS (
  SELECT id,
         ROW_NUMBER() OVER (
           PARTITION BY user_id, event_id
           ORDER BY created_at ASC, id ASC
         ) AS rn
  FROM rm_user_picks
)
DELETE FROM rm_user_picks p
USING ranked r
WHERE p.id = r.id
  AND r.rn > 1;

DROP INDEX IF EXISTS idx_rm_picks_user_event;

CREATE UNIQUE INDEX IF NOT EXISTS idx_rm_picks_user_event_unique
  ON rm_user_picks (user_id, event_id);
