CREATE TABLE users_without_email (
    id TEXT PRIMARY KEY CHECK (length(id) = 36),
    username TEXT NOT NULL UNIQUE
        CHECK (
            length(username) BETWEEN 1 AND 32
            AND username = lower(username)
            AND substr(username, 1, 1) GLOB '[a-z0-9]'
            AND username NOT GLOB '*[^a-z0-9._-]*'
        ),
    display_name TEXT NOT NULL
        CHECK (length(CAST(display_name AS BLOB)) <= 128),
    password_hash BLOB NOT NULL CHECK (length(password_hash) > 0),
    disabled INTEGER NOT NULL CHECK (disabled IN (0, 1)),
    uid_number INTEGER NOT NULL UNIQUE
        CHECK (uid_number BETWEEN 10000 AND 19999),
    created_at INTEGER NOT NULL,
    last_login_at INTEGER,
    credentials_epoch INTEGER NOT NULL DEFAULT 0
) STRICT;

INSERT INTO users_without_email (
    id,
    username,
    display_name,
    password_hash,
    disabled,
    uid_number,
    created_at,
    last_login_at,
    credentials_epoch
)
SELECT
    id,
    username,
    display_name,
    password_hash,
    disabled,
    uid_number,
    created_at,
    last_login_at,
    credentials_epoch
FROM users;

DROP TRIGGER pending_confirmations_need_disabled;
DROP TABLE users;
ALTER TABLE users_without_email RENAME TO users;

CREATE TRIGGER pending_confirmations_need_disabled
BEFORE INSERT ON pending_confirmations
WHEN (SELECT disabled FROM users WHERE id = NEW.user_id) IS NOT 1
BEGIN
    SELECT RAISE(ABORT, 'a pending account must be disabled');
END;

CREATE TRIGGER users_pending_stays_disabled
BEFORE UPDATE OF disabled ON users
WHEN NEW.disabled = 0 AND EXISTS (SELECT 1 FROM pending_confirmations WHERE user_id = NEW.id)
BEGIN
    SELECT RAISE(ABORT, 'a pending account is activated only by its confirmation');
END;

ALTER TABLE google_links DROP COLUMN google_email_snapshot;

ALTER TABLE invitations DROP COLUMN email;