-- CI: runners, registration tokens, runs, jobs, artifacts and sealed repo secrets.
-- Runner, registration and job tokens are stored as sha256 hex only. Timestamps are RFC 3339
-- UTC stamps from time.rs. See docs/ci-interfaces.md section 2.

CREATE TABLE ci_runners (
    id           TEXT PRIMARY KEY,
    name         TEXT NOT NULL,
    labels       TEXT NOT NULL,
    secret_hash  TEXT NOT NULL UNIQUE,
    created_at   TEXT NOT NULL,
    last_seen_at TEXT NULL,
    disabled     INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE ci_registration_tokens (
    token_hash  TEXT PRIMARY KEY,
    created_by  TEXT NOT NULL REFERENCES users(id),
    created_at  TEXT NOT NULL,
    expires_at  TEXT NOT NULL,
    used_at     TEXT NULL
);

CREATE TABLE ci_runs (
    id            TEXT PRIMARY KEY,
    repo_id       TEXT NOT NULL REFERENCES repos(id),
    number        INTEGER NOT NULL,
    trigger       TEXT NOT NULL,
    git_ref       TEXT NOT NULL,
    commit_oid    TEXT NOT NULL,
    pull_number   INTEGER NULL,
    actor_id      TEXT NOT NULL REFERENCES users(id),
    state         TEXT NOT NULL,
    config_error  TEXT NULL,
    created_at    TEXT NOT NULL,
    finished_at   TEXT NULL,
    UNIQUE (repo_id, number)
);

CREATE TABLE ci_jobs (
    id                TEXT PRIMARY KEY,
    run_id            TEXT NOT NULL REFERENCES ci_runs(id) ON DELETE CASCADE,
    name              TEXT NOT NULL,
    spec              TEXT NOT NULL,
    state             TEXT NOT NULL,
    runner_id         TEXT NULL,
    job_token_hash    TEXT NULL,
    exit_code         INTEGER NULL,
    message           TEXT NULL,
    queued_at         TEXT NULL,
    started_at        TEXT NULL,
    finished_at       TEXT NULL,
    heartbeat_at      TEXT NULL,
    cancel_requested  INTEGER NOT NULL DEFAULT 0,
    log_bytes         INTEGER NOT NULL DEFAULT 0,
    UNIQUE (run_id, name)
);

CREATE INDEX ci_jobs_queue ON ci_jobs(state, queued_at);

CREATE TABLE ci_artifacts (
    id          TEXT PRIMARY KEY,
    job_id      TEXT NOT NULL REFERENCES ci_jobs(id) ON DELETE CASCADE,
    name        TEXT NOT NULL,
    size        INTEGER NOT NULL,
    sha256      TEXT NOT NULL,
    created_at  TEXT NOT NULL,
    UNIQUE (job_id, name)
);

CREATE TABLE ci_secrets (
    repo_id     TEXT NOT NULL REFERENCES repos(id),
    name        TEXT NOT NULL,
    sealed      BLOB NOT NULL,
    created_at  TEXT NOT NULL,
    updated_at  TEXT NOT NULL,
    PRIMARY KEY (repo_id, name)
);