ALTER TABLE audit_log ADD COLUMN address TEXT
    CHECK (
        address IS NULL
        OR (length(address) BETWEEN 2 AND 45 AND address NOT GLOB '*[^0-9a-f.:]*')
    );

CREATE INDEX audit_log_address_idx ON audit_log(address, occurred_at DESC, id DESC);

CREATE TABLE reset_links (
    user_id TEXT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    token_hash BLOB NOT NULL UNIQUE CHECK (length(token_hash) = 32),
    created_by TEXT REFERENCES users(id) ON DELETE SET NULL,
    created_at INTEGER NOT NULL,
    expires_at INTEGER NOT NULL,
    CHECK (expires_at > created_at)
) STRICT;

ALTER TABLE invitations ADD COLUMN label TEXT NOT NULL DEFAULT ''
    CHECK (length(CAST(label AS BLOB)) <= 128);
ALTER TABLE invitations ADD COLUMN batch_id TEXT
    CHECK (batch_id IS NULL OR length(batch_id) = 36);

CREATE INDEX invitations_batch_id_idx ON invitations(batch_id) WHERE batch_id IS NOT NULL;

CREATE TABLE invitation_groups (
    invitation_id TEXT NOT NULL REFERENCES invitations(id) ON DELETE CASCADE,
    group_id TEXT REFERENCES groups(id) ON DELETE SET NULL,
    group_name TEXT NOT NULL
        CHECK (
            length(group_name) BETWEEN 1 AND 32
            AND group_name = lower(group_name)
            AND substr(group_name, 1, 1) GLOB '[a-z0-9]'
            AND group_name NOT GLOB '*[^a-z0-9_-]*'
        ),
    PRIMARY KEY (invitation_id, group_name)
) STRICT;

CREATE INDEX invitation_groups_group_id_idx ON invitation_groups(group_id);

CREATE TABLE pending_confirmations (
    user_id TEXT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    code_hash BLOB NOT NULL CHECK (length(code_hash) = 32),
    issued_at INTEGER NOT NULL,
    expires_at INTEGER NOT NULL,
    wrong_codes INTEGER NOT NULL DEFAULT 0 CHECK (wrong_codes >= 0),
    CHECK (expires_at > issued_at)
) STRICT;

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;