-- Simplifies the role model to admin/user (the future-organisation roles
-- owner/recruiter/viewer were never used by any UI). Existing 'owner' and
-- 'admin' rows become 'admin' (they were the account holder); anything
-- else becomes 'user'.
UPDATE users SET role = 'admin' WHERE role IN ('owner', 'admin');
UPDATE users SET role = 'user' WHERE role NOT IN ('admin');

ALTER TABLE users
    MODIFY COLUMN role ENUM('admin','user') NOT NULL DEFAULT 'user';

-- If no admin exists at all after the migration (shouldn't normally
-- happen), promote whoever registered first so someone can manage users.
UPDATE users
    SET role = 'admin'
    WHERE id = (SELECT id FROM (SELECT id FROM users ORDER BY created_at ASC LIMIT 1) t)
      AND NOT EXISTS (SELECT 1 FROM (SELECT id FROM users WHERE role = 'admin') a);