-- Backups (docs/backups.md, oxygenhub#169): what each user and organisation

-- backs up, where and how often, and how each run went.

CREATE TABLE backup_settings (

    owner       TEXT PRIMARY KEY,

    -- 'user' or 'org'

    kind        TEXT NOT NULL,

    -- 'off', 'hourly' or 'daily'

    every       TEXT NOT NULL DEFAULT 'off',

    -- 'dir' (a directory hub.toml allows) or 'gcs'

    destination TEXT NOT NULL DEFAULT 'dir',

    -- the directory, or the bucket

    target      TEXT NOT NULL DEFAULT '',

    prefix      TEXT NOT NULL DEFAULT '',

    -- for 'gcs': the workload identity provider's resource name

    provider    TEXT NOT NULL DEFAULT '',

    keep_days   INTEGER NOT NULL DEFAULT 30,

    updated_ms  INTEGER NOT NULL

);

CREATE TABLE backup_runs (

    id          INTEGER PRIMARY KEY,

    owner       TEXT NOT NULL,

    started_ms  INTEGER NOT NULL,

    finished_ms INTEGER,

    -- 'running', 'ok', 'partial' or 'failed'

    status      TEXT NOT NULL,

    -- the run's name at its destination (runs/<at>/)

    at          TEXT NOT NULL DEFAULT '',

    repos       INTEGER NOT NULL DEFAULT 0,

    files       INTEGER NOT NULL DEFAULT 0,

    sent        INTEGER NOT NULL DEFAULT 0,

    bytes       INTEGER NOT NULL DEFAULT 0,

    detail      TEXT NOT NULL DEFAULT '',

    -- 'schedule' or 'now'

    trigger     TEXT NOT NULL DEFAULT 'schedule'

);

CREATE INDEX backup_runs_by_owner ON backup_runs (owner, id DESC);