BEGIN;

DO $$
DECLARE
  canonical_user uuid;
  duplicate_user uuid;
BEGIN
  SELECT id
  INTO canonical_user
  FROM rm_users
  WHERE lower(email) = 'mikecarnavale@gmail.com'
  ORDER BY email_verified DESC, created_at ASC
  LIMIT 1;

  SELECT id
  INTO duplicate_user
  FROM rm_users
  WHERE lower(email) = 'mikecarnavale@gmail.com'
    AND id <> canonical_user
  ORDER BY created_at DESC
  LIMIT 1;

  IF canonical_user IS NOT NULL AND duplicate_user IS NOT NULL THEN
    UPDATE rm_users keeper
    SET single_picks = keeper.single_picks + duplicate.single_picks,
        daily_free_forecasts = keeper.daily_free_forecasts + duplicate.daily_free_forecasts,
        updated_at = NOW()
    FROM rm_users duplicate
    WHERE keeper.id = canonical_user
      AND duplicate.id = duplicate_user;

    UPDATE rm_forecast_ledger SET user_id = canonical_user WHERE user_id = duplicate_user;
    UPDATE rm_insight_unlocks SET user_id = canonical_user WHERE user_id = duplicate_user;
    UPDATE rm_player_prop_unlocks SET user_id = canonical_user WHERE user_id = duplicate_user;
    UPDATE rm_purchases SET user_id = canonical_user WHERE user_id = duplicate_user;
    UPDATE rm_push_subscriptions SET user_id = canonical_user WHERE user_id = duplicate_user;
    UPDATE rm_survey_responses SET user_id = canonical_user WHERE user_id = duplicate_user;
    UPDATE rm_team_props_unlocks SET user_id = canonical_user WHERE user_id = duplicate_user;
    UPDATE rm_tourist_passes SET user_id = canonical_user WHERE user_id = duplicate_user;
    UPDATE rm_user_clips SET user_id = canonical_user WHERE user_id = duplicate_user;
    UPDATE rm_user_picks SET user_id = canonical_user WHERE user_id = duplicate_user;

    INSERT INTO rm_user_profiles (
      user_id,
      favorite_sports_ranked,
      interest_buckets,
      market_participant,
      profile_version,
      created_at,
      updated_at
    )
    SELECT
      canonical_user,
      favorite_sports_ranked,
      interest_buckets,
      market_participant,
      profile_version,
      created_at,
      NOW()
    FROM rm_user_profiles
    WHERE user_id = duplicate_user
    ON CONFLICT (user_id) DO NOTHING;

    DELETE FROM rm_user_profiles WHERE user_id = duplicate_user;
    DELETE FROM rm_users WHERE id = duplicate_user;
  END IF;
END $$;

DROP INDEX IF EXISTS idx_rm_users_email;
ALTER TABLE rm_users DROP CONSTRAINT IF EXISTS rm_users_email_key;

CREATE UNIQUE INDEX rm_users_email_lower_key
ON rm_users (lower(email));

COMMIT;
