-- Repo webhooks and their deliveries. Applied by store/migrate.rs.
-- secret_sealed holds the secret::seal output (UTF-8 "v1:..." text stored as a BLOB, like
-- users.totp_secret_sealed). The plaintext secret never reaches the database.
-- events is a JSON array of WebhookEvent names. active is 0 or 1.
-- Timestamps are RFC 3339 UTC stamps from time.rs, so text comparison is time order.

CREATE TABLE webhooks (
    id              TEXT PRIMARY KEY,
    repo_id         TEXT NOT NULL REFERENCES repos(id),
    url             TEXT NOT NULL,
    secret_sealed   BLOB NOT NULL,
    events          TEXT NOT NULL,
    active          INTEGER NOT NULL DEFAULT 1,
    created_by      TEXT NULL REFERENCES users(id) ON DELETE SET NULL,
    created_at      TEXT NOT NULL,
    updated_at      TEXT NOT NULL
);
CREATE INDEX webhooks_repo_id ON webhooks(repo_id);

-- state: pending (queued, not yet attempted), ok (2xx received), failed (an attempt failed and a
-- retry is scheduled at next_attempt_at), gave_up (no further attempts).
-- Claimable rows are state IN ('pending', 'failed') with next_attempt_at <= now.
-- guid is the X-Forge-Delivery value. The request columns are immutable once written.
CREATE TABLE webhook_deliveries (
    id                TEXT PRIMARY KEY,
    hook_id           TEXT NOT NULL REFERENCES webhooks(id),
    guid              TEXT NOT NULL UNIQUE,
    event             TEXT NOT NULL,
    action            TEXT NOT NULL DEFAULT '',
    request_headers   TEXT NOT NULL,
    request_body      TEXT NOT NULL,
    state             TEXT NOT NULL CHECK (state IN ('pending', 'ok', 'failed', 'gave_up')),
    attempt           INTEGER NOT NULL DEFAULT 0,
    next_attempt_at   TEXT NULL,
    response_status   INTEGER NULL,
    response_headers  TEXT NULL,
    response_body     TEXT NULL,            -- first 64 KiB of the body
    error             TEXT NULL,
    duration_ms       INTEGER NULL,
    created_at        TEXT NOT NULL,
    delivered_at      TEXT NULL
);
CREATE INDEX webhook_deliveries_state_next ON webhook_deliveries(state, next_attempt_at);
CREATE INDEX webhook_deliveries_hook_created ON webhook_deliveries(hook_id, created_at);