-- Ensure every existing member has a stable public profile handle.
-- Prefer a readable member-{id} handle when available, then use an id+hash
-- fallback for the rare case where that handle was already claimed manually.
--
-- The collision guard is a LEFT JOIN against a derived table, not a
-- correlated NOT EXISTS: MySQL rejects a subquery that reads the table being
-- updated with error 1093, which MariaDB accepts. The inner SELECT is nested
-- twice so the optimiser cannot merge the derived table back into the UPDATE
-- and reintroduce the same error.
UPDATE users u
LEFT JOIN (
    SELECT username FROM (
        SELECT username FROM users WHERE username IS NOT NULL AND TRIM(username) <> ''
    ) claimed_handles
) taken ON taken.username = CONCAT('member-', u.id)
SET u.username = CONCAT('member-', u.id)
WHERE (u.username IS NULL OR TRIM(u.username) = '')
  AND taken.username IS NULL;

UPDATE users
SET username = CONCAT('u-', id, '-', SUBSTRING(SHA2(CONCAT(id, ':', email), 256), 1, 8))
WHERE username IS NULL OR TRIM(username) = '';
