-- Reports (oxygenhub#127, docs/reports.md): a directory a Flow job writes

-- with report.toml at its top. Its files are blobs of the repository and

-- its manifest is the job's artifact of that name; these tables index it

-- for the Reports tab, its history and checks, and name every file blob

-- so garbage collection keeps it.

CREATE TABLE reports (

    id          INTEGER PRIMARY KEY,

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

    name        TEXT NOT NULL,

    kind        TEXT NOT NULL,

    title       TEXT NOT NULL,

    summary     TEXT NOT NULL,

    context     TEXT NOT NULL,

    run_id      INTEGER REFERENCES flow_runs(id) ON DELETE CASCADE,

    job_id      INTEGER,

    run_number  INTEGER,

    job         TEXT NOT NULL,

    bookmark    TEXT NOT NULL,

    state       TEXT NOT NULL,

    manifest    TEXT,

    files       INTEGER NOT NULL,

    bytes       INTEGER NOT NULL,

    created_ms  INTEGER NOT NULL

);

CREATE UNIQUE INDEX reports_job ON reports (job_id, name);

CREATE INDEX reports_history ON reports (repo_id, name, bookmark, created_ms);

CREATE TABLE report_metrics (

    report_id   INTEGER NOT NULL REFERENCES reports(id) ON DELETE CASCADE,

    key         TEXT NOT NULL,

    value       REAL NOT NULL,

    unit        TEXT NOT NULL,

    better      TEXT NOT NULL,

    fail_above  REAL,

    fail_below  REAL,

    PRIMARY KEY (report_id, key)

);

CREATE TABLE report_files (

    report_id   INTEGER NOT NULL REFERENCES reports(id) ON DELETE CASCADE,

    path        TEXT NOT NULL,

    blob        TEXT NOT NULL,

    size        INTEGER NOT NULL,

    PRIMARY KEY (report_id, path)

);

CREATE INDEX report_files_blob ON report_files (blob);