-- Webhooks with a delivery log, and checks on state hashes (hub spec

-- §12.2, §13.1).

CREATE TABLE hooks (

    id         INTEGER PRIMARY KEY,

    repo_id    INTEGER REFERENCES repositories(id) ON DELETE CASCADE,

    org_id     INTEGER REFERENCES organisations(id) ON DELETE CASCADE,

    url        TEXT NOT NULL,

    secret     TEXT NOT NULL DEFAULT '',

    events     TEXT NOT NULL DEFAULT '*',

    active     INTEGER NOT NULL DEFAULT 1,

    created_ms INTEGER NOT NULL,

    CHECK ((repo_id IS NULL) <> (org_id IS NULL))

);

CREATE TABLE deliveries (

    id          INTEGER PRIMARY KEY,

    hook_id     INTEGER NOT NULL REFERENCES hooks(id) ON DELETE CASCADE,

    delivery    TEXT NOT NULL,

    event       TEXT NOT NULL,

    payload     TEXT NOT NULL,

    created_ms  INTEGER NOT NULL,

    attempts    INTEGER NOT NULL DEFAULT 0,

    next_ms     INTEGER NOT NULL,

    status      INTEGER,

    response    TEXT NOT NULL DEFAULT '',

    delivered_ms INTEGER

);

CREATE INDEX deliveries_due ON deliveries (next_ms) WHERE delivered_ms IS NULL;

CREATE INDEX deliveries_by_hook ON deliveries (hook_id, created_ms DESC);

CREATE TABLE checks (

    id          INTEGER PRIMARY KEY,

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

    state       TEXT NOT NULL,

    name        TEXT NOT NULL,

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

    summary     TEXT NOT NULL DEFAULT '',

    url         TEXT NOT NULL DEFAULT '',

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

    reporter    TEXT NOT NULL DEFAULT '',

    seal        TEXT NOT NULL DEFAULT '',

    started_ms  INTEGER NOT NULL,

    finished_ms INTEGER,

    UNIQUE (repo_id, state, name)

);

CREATE INDEX checks_by_state ON checks (repo_id, state);

ALTER TABLE repositories ADD COLUMN required_checks TEXT NOT NULL DEFAULT '';

ALTER TABLE tokens ADD COLUMN scopes_note TEXT NOT NULL DEFAULT '';