CREATE TABLE users (
    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._-]*'
        ),
    email TEXT NOT NULL UNIQUE
        CHECK (length(email) > 0 AND email = lower(email)),
    email_verified INTEGER NOT NULL CHECK (email_verified IN (0, 1)),
    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
) STRICT;

CREATE TABLE groups (
    id TEXT PRIMARY KEY CHECK (length(id) = 36),
    name TEXT NOT NULL UNIQUE
        CHECK (
            length(name) BETWEEN 1 AND 32
            AND name = lower(name)
            AND substr(name, 1, 1) GLOB '[a-z0-9]'
            AND name NOT GLOB '*[^a-z0-9_-]*'
        ),
    description TEXT NOT NULL,
    gid_number INTEGER NOT NULL UNIQUE
        CHECK (gid_number BETWEEN 20000 AND 2147483647),
    created_at INTEGER NOT NULL
) STRICT;

CREATE TABLE memberships (
    user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    group_id TEXT NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    PRIMARY KEY (user_id, group_id)
) STRICT;

CREATE INDEX memberships_group_id_idx ON memberships(group_id, user_id);

CREATE TABLE google_links (
    user_id TEXT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    google_sub TEXT NOT NULL UNIQUE CHECK (length(google_sub) > 0),
    google_email_snapshot TEXT NOT NULL
        CHECK (
            length(google_email_snapshot) > 0
            AND google_email_snapshot = lower(google_email_snapshot)
        ),
    linked_at INTEGER NOT NULL
) STRICT;

CREATE TABLE invitations (
    id TEXT PRIMARY KEY CHECK (length(id) = 36),
    token_hash BLOB NOT NULL UNIQUE CHECK (length(token_hash) > 0),
    email TEXT CHECK (email IS NULL OR (length(email) > 0 AND email = lower(email))),
    created_by TEXT REFERENCES users(id) ON DELETE SET NULL,
    created_at INTEGER NOT NULL,
    expires_at INTEGER NOT NULL,
    used_by TEXT REFERENCES users(id) ON DELETE SET NULL,
    used_at INTEGER,
    revoked_at INTEGER,
    CHECK (used_at IS NULL OR revoked_at IS NULL)
) STRICT;

CREATE INDEX invitations_created_at_idx ON invitations(created_at DESC, id);

CREATE TABLE audit_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    actor TEXT NOT NULL CHECK (length(actor) > 0),
    action TEXT NOT NULL CHECK (length(action) > 0),
    subject TEXT NOT NULL CHECK (length(subject) > 0),
    occurred_at INTEGER NOT NULL,
    detail TEXT NOT NULL
) STRICT;

CREATE INDEX audit_log_occurred_at_idx ON audit_log(occurred_at DESC, id DESC);
CREATE INDEX audit_log_actor_idx ON audit_log(actor, occurred_at DESC, id DESC);
CREATE INDEX audit_log_action_idx ON audit_log(action, occurred_at DESC, id DESC);
CREATE INDEX audit_log_subject_idx ON audit_log(subject, occurred_at DESC, id DESC);

CREATE TABLE forward_sessions (
    id TEXT PRIMARY KEY CHECK (length(id) > 0),
    user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    app_domain TEXT NOT NULL CHECK (length(app_domain) > 0),
    created_at INTEGER NOT NULL,
    expires_at INTEGER NOT NULL,
    revoked INTEGER NOT NULL CHECK (revoked IN (0, 1))
) STRICT;

CREATE INDEX forward_sessions_user_id_idx
    ON forward_sessions(user_id, created_at DESC, id);

CREATE TABLE settings (
    id INTEGER PRIMARY KEY CHECK (id = 1),
    config_commit TEXT CHECK (config_commit IS NULL OR length(config_commit) > 0),
    userbase_commit TEXT CHECK (userbase_commit IS NULL OR length(userbase_commit) > 0),
    next_uid_number INTEGER NOT NULL
        CHECK (next_uid_number BETWEEN 10000 AND 20000),
    next_gid_number INTEGER NOT NULL
        CHECK (next_gid_number BETWEEN 20000 AND 2147483648)
) STRICT;

INSERT INTO settings (
    id,
    config_commit,
    userbase_commit,
    next_uid_number,
    next_gid_number
) VALUES (1, NULL, NULL, 10000, 20000);