-- Finishes what migration 023 started: every admin form now creates/picks a
-- real users.id instead of typing a free-text creator_tag, so this backfills
-- a users row for any existing claim_links row that still only has a
-- creator_tag (never deduplicated against an existing users.name, same rule
-- UserFactory::create() follows everywhere else -- two rows can legitimately
-- share a display name), points that row's user_id at it, then makes user_id
-- required and drops the now-unused creator_tag column.
--
-- The backfill correlates each new users row back to its exact source
-- claim_links row via a temporary id column rather than matching on
-- name+created_at, which could pair rows ambiguously if two links were
-- created with the same creator_tag in the same second.

ALTER TABLE users ADD COLUMN _backfill_link_id INT UNSIGNED NULL;

INSERT INTO users (name, created_at, _backfill_link_id)
SELECT cl.creator_tag, cl.created_at, cl.id
FROM claim_links cl
WHERE cl.user_id IS NULL;

UPDATE claim_links cl
JOIN users u ON u._backfill_link_id = cl.id
SET cl.user_id = u.id
WHERE cl.user_id IS NULL;

ALTER TABLE users DROP COLUMN _backfill_link_id;

-- The existing FK is ON DELETE SET NULL, which MySQL refuses to keep once
-- the column below becomes NOT NULL ("Column 'user_id' cannot be NOT NULL:
-- needed in a foreign key constraint ... SET NULL") -- swap it to RESTRICT
-- first. user_id being required means a user can no longer be silently
-- detached to NULL on delete anyway; deleting one that still has claim
-- links must be blocked instead (there is no "delete user" feature, only
-- the personal-data-only anonymize on the Users page, but the constraint
-- should still be correct).
ALTER TABLE claim_links DROP FOREIGN KEY fk_link_user;
ALTER TABLE claim_links ADD CONSTRAINT fk_link_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT;

ALTER TABLE claim_links MODIFY COLUMN user_id BIGINT UNSIGNED NOT NULL;
ALTER TABLE claim_links DROP COLUMN creator_tag;
