-- Accounts, organisations, roles, tokens, linked identities and the audit

-- log (hub spec §5). Repository content stays in the object stores.

CREATE TABLE accounts (

    id             INTEGER PRIMARY KEY,

    username       TEXT NOT NULL UNIQUE COLLATE NOCASE,

    display_name   TEXT NOT NULL DEFAULT '',

    email          TEXT NOT NULL DEFAULT '',

    email_verified INTEGER NOT NULL DEFAULT 0,

    password_hash  TEXT NOT NULL,

    session_salt   TEXT NOT NULL,

    totp_secret    TEXT,

    recovery_codes TEXT NOT NULL DEFAULT '',

    is_admin       INTEGER NOT NULL DEFAULT 0,

    banned         INTEGER NOT NULL DEFAULT 0,

    created_ms     INTEGER NOT NULL,

    last_seen_ms   INTEGER NOT NULL DEFAULT 0

);

CREATE TABLE organisations (

    id           INTEGER PRIMARY KEY,

    name         TEXT NOT NULL UNIQUE COLLATE NOCASE,

    display_name TEXT NOT NULL DEFAULT '',

    description  TEXT NOT NULL DEFAULT '',

    default_role TEXT NOT NULL DEFAULT 'read' CHECK (default_role IN ('none', 'read', 'triage', 'write', 'maintain', 'admin')),

    created_ms   INTEGER NOT NULL

);

CREATE TABLE org_members (

    org_id     INTEGER NOT NULL REFERENCES organisations(id) ON DELETE CASCADE,

    account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,

    role       TEXT NOT NULL DEFAULT 'member' CHECK (role IN ('owner', 'member')),

    PRIMARY KEY (org_id, account_id)

);

CREATE TABLE teams (

    id     INTEGER PRIMARY KEY,

    org_id INTEGER NOT NULL REFERENCES organisations(id) ON DELETE CASCADE,

    name   TEXT NOT NULL COLLATE NOCASE,

    UNIQUE (org_id, name)

);

CREATE TABLE team_members (

    team_id    INTEGER NOT NULL REFERENCES teams(id) ON DELETE CASCADE,

    account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,

    PRIMARY KEY (team_id, account_id)

);

-- A collaborator is an account or a team with a role on one repository.

CREATE TABLE collaborators (

    repo_id    INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,

    account_id INTEGER REFERENCES accounts(id) ON DELETE CASCADE,

    team_id    INTEGER REFERENCES teams(id) ON DELETE CASCADE,

    role       TEXT NOT NULL CHECK (role IN ('read', 'triage', 'write', 'maintain', 'admin')),

    CHECK ((account_id IS NULL) <> (team_id IS NULL))

);

CREATE UNIQUE INDEX collaborators_account ON collaborators (repo_id, account_id) WHERE account_id IS NOT NULL;

CREATE UNIQUE INDEX collaborators_team ON collaborators (repo_id, team_id) WHERE team_id IS NOT NULL;

CREATE TABLE tokens (

    id           INTEGER PRIMARY KEY,

    account_id   INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,

    name         TEXT NOT NULL,

    hash         TEXT NOT NULL UNIQUE,

    scopes       TEXT NOT NULL,

    repos        TEXT NOT NULL DEFAULT '',

    created_ms   INTEGER NOT NULL,

    expires_ms   INTEGER,

    last_used_ms INTEGER

);

-- Oxygen identities an account has proven it holds.

CREATE TABLE identities (

    key        TEXT PRIMARY KEY,

    account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,

    name       TEXT NOT NULL,

    email      TEXT NOT NULL,

    object     BLOB NOT NULL,

    linked_ms  INTEGER NOT NULL

);

CREATE TABLE challenges (

    id         INTEGER PRIMARY KEY,

    account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,

    bytes      TEXT NOT NULL,

    object     BLOB NOT NULL DEFAULT X'',

    created_ms INTEGER NOT NULL

);

CREATE TABLE invitations (

    code       TEXT PRIMARY KEY,

    created_by INTEGER REFERENCES accounts(id) ON DELETE SET NULL,

    used_by    INTEGER REFERENCES accounts(id) ON DELETE SET NULL,

    created_ms INTEGER NOT NULL

);

CREATE TABLE audit (

    id         INTEGER PRIMARY KEY,

    at_ms      INTEGER NOT NULL,

    actor_id   INTEGER REFERENCES accounts(id) ON DELETE SET NULL,

    actor      TEXT NOT NULL DEFAULT '',

    action     TEXT NOT NULL,

    target     TEXT NOT NULL DEFAULT '',

    detail     TEXT NOT NULL DEFAULT ''

);

CREATE INDEX audit_by_time ON audit (at_ms DESC);

-- Site settings that an administrator changes at run time.

CREATE TABLE settings (

    key   TEXT PRIMARY KEY,

    value TEXT NOT NULL

);

ALTER TABLE pushes ADD COLUMN account_id INTEGER REFERENCES accounts(id) ON DELETE SET NULL;

ALTER TABLE pushes ADD COLUMN pusher TEXT NOT NULL DEFAULT '';