-- Sassly Auth: link local users to Sassly accounts (MySQL 8 / MariaDB 10.6+).
-- ADAPT table/column names to the app. Run the checks first, then the ALTER.

-- Check 1: emails that appear more than once. Inside ONE tenant this must be 0 rows
-- (fix duplicates first). Across tenants it is allowed: the same person gets a chooser.
SELECT LOWER(email) AS email_lc, COUNT(*) AS n
FROM users
GROUP BY LOWER(email)
HAVING COUNT(*) > 1;

-- Check 2: users without an email cannot be linked automatically (admin must fill it in).
SELECT id FROM users WHERE email IS NULL OR email = '';

-- Add the link column. It stays NULL until the user's first Sassly login.
ALTER TABLE users
    ADD COLUMN sassly_sub VARCHAR(36) CHARACTER SET ascii COLLATE ascii_bin NULL DEFAULT NULL;

-- Pick ONE index:
-- (a) one row per person in this app:
CREATE UNIQUE INDEX uq_users_sassly_sub ON users (sassly_sub);
-- (b) the same person may own rows in several tenants (chooser) — use this instead of (a):
-- CREATE INDEX ix_users_sassly_sub ON users (sassly_sub);

-- Admin fix for a person whose Google account was re-created (new sub, same email):
-- UPDATE users SET sassly_sub = NULL WHERE id = <id>;   -- relinks by email on next login