WITH ranked AS (
  SELECT id, first_value(id) OVER (PARTITION BY code ORDER BY created_at, id) AS canonical_id,
    row_number() OVER (PARTITION BY code ORDER BY created_at, id) AS position
  FROM requesters WHERE code IS NOT NULL
)
UPDATE invoices i SET default_requester_id = r.canonical_id
FROM ranked r WHERE i.default_requester_id = r.id AND r.position > 1;

WITH ranked AS (
  SELECT id, first_value(id) OVER (PARTITION BY code ORDER BY created_at, id) AS canonical_id,
    row_number() OVER (PARTITION BY code ORDER BY created_at, id) AS position
  FROM requesters WHERE code IS NOT NULL
)
UPDATE delivery_requests d SET requester_id = r.canonical_id
FROM ranked r WHERE d.requester_id = r.id AND r.position > 1;

WITH ranked AS (
  SELECT id, row_number() OVER (PARTITION BY code ORDER BY created_at, id) AS position
  FROM requesters WHERE code IS NOT NULL
)
DELETE FROM requesters r USING ranked x WHERE r.id = x.id AND x.position > 1;

CREATE UNIQUE INDEX requesters_code_unique_idx ON requesters(code) WHERE code IS NOT NULL;
