-- Cancelled runs and jobs (oxygenflow#32). SQLite cannot change a CHECK

-- constraint in place, so both tables are rebuilt. Migrations run with

-- foreign keys off (db::migrate), or dropping flow_jobs would take its

-- logs and artifacts with it.

CREATE TABLE flow_runs_new (

    id            INTEGER PRIMARY KEY,

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

    bookmark      TEXT NOT NULL,

    -- The bookmark's state hash (checks are keyed by it) and its heads, joined with '+'.

    state         TEXT NOT NULL,

    heads         TEXT NOT NULL,

    pipeline_hash TEXT NOT NULL DEFAULT '',

    status        TEXT NOT NULL CHECK (status IN ('queued', 'running', 'passed', 'failed', 'error', 'cancelled')),

    -- Pipeline problems, one per line, for a run in error.

    error         TEXT NOT NULL DEFAULT '',

    actor         TEXT NOT NULL DEFAULT '',

    created_ms    INTEGER NOT NULL,

    finished_ms   INTEGER,

    number        INTEGER NOT NULL DEFAULT 0,

    trigger       TEXT NOT NULL DEFAULT 'push'

);

INSERT INTO flow_runs_new (id, repo_id, bookmark, state, heads, pipeline_hash, status, error, actor, created_ms, finished_ms, number, trigger)

    SELECT id, repo_id, bookmark, state, heads, pipeline_hash, status, error, actor, created_ms, finished_ms, number, trigger FROM flow_runs;

DROP TABLE flow_runs;

ALTER TABLE flow_runs_new RENAME TO flow_runs;

CREATE INDEX flow_runs_by_repo ON flow_runs (repo_id, id DESC);

CREATE UNIQUE INDEX flow_runs_number ON flow_runs (repo_id, number);

CREATE TABLE flow_jobs_new (

    id             INTEGER PRIMARY KEY,

    run_id         INTEGER NOT NULL REFERENCES flow_runs(id) ON DELETE CASCADE,

    name           TEXT NOT NULL,

    depth          INTEGER NOT NULL,

    -- JSON: the job's dependencies, compiled form and sandbox (flow-core types).

    depends        TEXT NOT NULL,

    spec           TEXT NOT NULL,

    sandbox        TEXT NOT NULL,

    runtime        TEXT NOT NULL,

    status         TEXT NOT NULL CHECK (status IN ('queued', 'running', 'passed', 'failed', 'skipped', 'error', 'cancelled')),

    runner         TEXT NOT NULL DEFAULT '',

    token_hash     TEXT NOT NULL DEFAULT '',

    started_ms     INTEGER,

    finished_ms    INTEGER,

    exit_code      INTEGER,

    error          TEXT NOT NULL DEFAULT '',

    receipt        TEXT NOT NULL DEFAULT '',

    receipt_hash   TEXT NOT NULL DEFAULT '',

    log_count      INTEGER NOT NULL DEFAULT 0,

    also_read      TEXT NOT NULL DEFAULT '',

    receipt_attest TEXT NOT NULL DEFAULT '',

    receipt_signer TEXT NOT NULL DEFAULT '',

    receipt_note   TEXT NOT NULL DEFAULT '',

    runner_id      INTEGER,

    timeout_s      INTEGER NOT NULL DEFAULT 0,

    UNIQUE (run_id, name)

);

INSERT INTO flow_jobs_new (id, run_id, name, depth, depends, spec, sandbox, runtime, status, runner, token_hash, started_ms, finished_ms,

        exit_code, error, receipt, receipt_hash, log_count, also_read, receipt_attest, receipt_signer, receipt_note, runner_id, timeout_s)

    SELECT id, run_id, name, depth, depends, spec, sandbox, runtime, status, runner, token_hash, started_ms, finished_ms,

        exit_code, error, receipt, receipt_hash, log_count, also_read, receipt_attest, receipt_signer, receipt_note, runner_id, timeout_s FROM flow_jobs;

DROP TABLE flow_jobs;

ALTER TABLE flow_jobs_new RENAME TO flow_jobs;

CREATE INDEX flow_jobs_queued ON flow_jobs (status, id);

CREATE INDEX flow_jobs_token ON flow_jobs (token_hash) WHERE token_hash <> '';

CREATE INDEX flow_jobs_runner ON flow_jobs (runner_id);