-- This Source Code Form is subject to the terms of the Mozilla Public
-- License, v. 2.0. If a copy of the MPL was not distributed with this
-- file, You can obtain one at https://mozilla.org/MPL/2.0/.

-- Initial schema (SQLite dialect).
--
-- This must stay structurally equivalent to migrations/postgres/0001_init.sql;
-- the only permitted differences are the boolean column type (INTEGER 0/1 here,
-- BOOLEAN there) and dialect punctuation. Timestamps are BIGINT Unix
-- milliseconds UTC; identifiers are 26-char ULID TEXT (lexicographically
-- sortable — order by them). Case-insensitive uniqueness is enforced with a
-- normalized `*_lower` column plus a unique index, never collation.

CREATE TABLE users (
    id                   TEXT PRIMARY KEY,
    username             TEXT NOT NULL,
    username_lower       TEXT NOT NULL UNIQUE,
    email                TEXT NOT NULL,
    email_lower          TEXT NOT NULL UNIQUE,
    display_name         TEXT,
    password_hash        TEXT,               -- NULL until an invite is completed
    must_change_password INTEGER NOT NULL DEFAULT 0,
    is_admin             INTEGER NOT NULL DEFAULT 0,
    disabled_at          BIGINT,
    created_at           BIGINT NOT NULL,
    updated_at           BIGINT NOT NULL
);

CREATE TABLE invites (
    id         TEXT PRIMARY KEY,
    user_id    TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    token_hash TEXT NOT NULL UNIQUE,         -- SHA-256 of the emailed token
    purpose    TEXT NOT NULL,                -- 'activate' | 'reset'
    expires_at BIGINT NOT NULL,
    used_at    BIGINT,
    created_at BIGINT NOT NULL
);

CREATE TABLE sessions (
    id           TEXT PRIMARY KEY,           -- SHA-256 of the cookie value
    user_id      TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    user_agent   TEXT,
    ip           TEXT,
    created_at   BIGINT NOT NULL,
    last_seen_at BIGINT NOT NULL,
    expires_at   BIGINT NOT NULL
);

CREATE TABLE api_tokens (
    id           TEXT PRIMARY KEY,           -- the JWT `jti`
    user_id      TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name         TEXT NOT NULL,
    scopes       TEXT NOT NULL,              -- comma-separated
    expires_at   BIGINT,
    revoked_at   BIGINT,
    last_used_at BIGINT,
    created_at   BIGINT NOT NULL
);

CREATE TABLE keys (
    id           TEXT PRIMARY KEY,
    user_id      TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    kind         TEXT NOT NULL,              -- 'ssh' | 'gpg'
    name         TEXT,
    fingerprint  TEXT NOT NULL,              -- SHA256:… for ssh, full hex fp for gpg
    public_key   TEXT NOT NULL,              -- authorized_keys line or armored pgp block
    ordinal      INTEGER NOT NULL,           -- stable 1-based index per (user, kind)
    last_used_at BIGINT,
    created_at   BIGINT NOT NULL,
    UNIQUE (kind, fingerprint),
    UNIQUE (user_id, kind, ordinal)
);

CREATE TABLE groups (
    id          TEXT PRIMARY KEY,
    owner_id    TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    parent_id   TEXT REFERENCES groups(id) ON DELETE CASCADE,
    name        TEXT NOT NULL,
    path        TEXT NOT NULL,               -- materialized 'group/sub/leaf'
    description TEXT,
    created_at  BIGINT NOT NULL,
    UNIQUE (owner_id, path)
);

CREATE TABLE repos (
    id             TEXT PRIMARY KEY,
    owner_id       TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    group_id       TEXT REFERENCES groups(id) ON DELETE SET NULL,
    name           TEXT NOT NULL,
    path           TEXT NOT NULL,            -- materialized 'group/sub/repo' (== name when ungrouped)
    description    TEXT,
    is_private     INTEGER NOT NULL DEFAULT 1,
    default_branch TEXT NOT NULL,
    archived_at    BIGINT,
    size_bytes     BIGINT NOT NULL DEFAULT 0,
    pushed_at      BIGINT,
    created_at     BIGINT NOT NULL,
    updated_at     BIGINT NOT NULL,
    UNIQUE (owner_id, path)
);

CREATE TABLE repo_collaborators (
    repo_id    TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
    user_id    TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    permission TEXT NOT NULL,                -- 'read' | 'write' | 'admin'
    created_at BIGINT NOT NULL,
    PRIMARY KEY (repo_id, user_id)
);

-- Reserved and inert until the CI ships (phase 18+). No behaviour reads these
-- yet; they exist so the seams are stable.
CREATE TABLE runs (
    id          TEXT PRIMARY KEY,
    repo_id     TEXT NOT NULL,
    ref         TEXT NOT NULL,
    commit_sha  TEXT NOT NULL,
    status      TEXT NOT NULL,
    started_at  BIGINT,
    finished_at BIGINT,
    created_at  BIGINT NOT NULL
);

CREATE TABLE jobs (
    id          TEXT PRIMARY KEY,
    run_id      TEXT NOT NULL REFERENCES runs(id) ON DELETE CASCADE,
    stage       TEXT NOT NULL,
    name        TEXT NOT NULL,
    status      TEXT NOT NULL,
    log_path    TEXT,
    started_at  BIGINT,
    finished_at BIGINT
);

-- Indexes. The UNIQUE(owner_id, path) constraint on repos already provides the
-- (owner_id, path) lookup index, so it is not repeated here.
CREATE INDEX idx_repos_visibility_pushed ON repos (is_private, pushed_at DESC);
CREATE INDEX idx_groups_owner_parent     ON groups (owner_id, parent_id);
CREATE INDEX idx_keys_user_kind          ON keys (user_id, kind);
CREATE INDEX idx_sessions_user           ON sessions (user_id);
CREATE INDEX idx_sessions_expires        ON sessions (expires_at);
CREATE INDEX idx_api_tokens_user         ON api_tokens (user_id);