-- Kepr Postgres schema — version 1.
--
-- A faithful translation of the cumulative SQLite schema (migrations 1..39,
-- as materialised by db.applyAll) into idiomatic Postgres. It is NOT derived
-- from prose: it was produced from `.schema` of a database with all 39
-- migrations applied, so every column, constraint, CHECK, and index matches
-- what the live SQLite database actually holds.
--
-- Translation rules (chosen for an exact, value-preserving migration):
--   * SQLite INTEGER  -> BIGINT          (SQLite ints are 64-bit; size_bytes
--                                          and byte counts exceed int32)
--   * SQLite REAL     -> DOUBLE PRECISION
--   * SQLite TEXT     -> TEXT
--   * Integer 0/1 booleans are KEPT as BIGINT 0/1 — not converted to BOOLEAN —
--     so the migration is a verbatim value copy and db.zig reads them exactly
--     as it does today (i64, != 0 == true).
--   * INTEGER PRIMARY KEY AUTOINCREMENT -> BIGINT GENERATED BY DEFAULT AS
--     IDENTITY (BY DEFAULT, not ALWAYS, so the migration tool can insert the
--     existing ids; the sequence is advanced past MAX(id) after bulk load).
--   * All timestamps remain Unix epoch seconds (BIGINT), unconverted.
--
-- Foreign keys are declared as in SQLite. SQLite did not enforce them at
-- runtime, so the one-time migration tool loads with
-- `SET session_replication_role = replica` to bypass FK/trigger checks, then
-- the constraints guard all subsequently-written data.

BEGIN;

CREATE TABLE schema_version (
    version INTEGER NOT NULL
);
INSERT INTO schema_version (version) VALUES (1);

-- ── Repositories and saves ───────────────────────────────────────────

CREATE TABLE repos (
    name               TEXT PRIMARY KEY,
    created_at         BIGINT NOT NULL,
    description        TEXT,
    languages          TEXT DEFAULT '',
    details            TEXT,
    categories         TEXT DEFAULT '',
    count_extensions   TEXT DEFAULT '',
    contributor_handle TEXT,
    issues_enabled     BIGINT NOT NULL DEFAULT 1,
    languages_cloud    TEXT,
    project_id         TEXT,
    trusted            BIGINT NOT NULL DEFAULT 0,
    trust_computed_at  BIGINT,
    private            BIGINT NOT NULL DEFAULT 0,
    builds_enabled     BIGINT NOT NULL DEFAULT 1,
    display_languages  TEXT
);

CREATE TABLE saves (
    id            TEXT NOT NULL,
    repo          TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,
    lane          TEXT NOT NULL CHECK (lane IN ('main','dev')),
    message       TEXT NOT NULL,
    timestamp     BIGINT NOT NULL,
    size_bytes    BIGINT NOT NULL,
    parent_id     TEXT,
    is_checkpoint BIGINT NOT NULL DEFAULT 0,
    checksum      TEXT NOT NULL,
    manifest_path TEXT NOT NULL,
    file_count    BIGINT NOT NULL DEFAULT 0,
    line_count    BIGINT NOT NULL DEFAULT 0,
    session       TEXT,
    agent         TEXT,
    PRIMARY KEY (id, repo)
);
CREATE INDEX saves_repo_lane_ts ON saves(repo, lane, timestamp);

CREATE TABLE object_refs (
    hex_hash TEXT NOT NULL,
    save_id  TEXT NOT NULL,
    repo     TEXT NOT NULL,
    PRIMARY KEY (hex_hash, save_id, repo)
);
CREATE INDEX object_refs_hash ON object_refs(hex_hash);

-- Reserved advisory-lock id: Postgres advisory locks (pg_advisory_xact_lock /
-- pg_advisory_xact_lock_shared) are a single flat bigint namespace per
-- cluster, not scoped to any table, but the id kepr reserves here exists to
-- protect this table specifically. storage GC's sweep (storage_admin.gc)
-- holds it exclusive around deciding which objects/ files are unreferenced;
-- every offer-complete transaction that promotes rows into object_refs
-- (src/web/api.zig: handleComplete, acceptPending) takes it
-- shared first, so a commit that references an object already on disk can
-- never race a concurrent sweep into deleting that object (review F12,
-- decision D3). Shared holders never block each other. See
-- db_mod.STORAGE_GC_ADVISORY_LOCK_ID in src/db.zig for the actual id and the
-- lockGcExclusive/lockGcShared helpers — if kepr ever reserves another
-- advisory-lock id, pick a value nowhere near this one and document it here
-- too.

-- Content-addressed cache of a countable file's line count, keyed by its
-- Blake3 hash. A hash's line count never changes, so once computed it never
-- needs decompressing again on a later save that re-references the same
-- unchanged file.
CREATE TABLE object_line_counts (
    hex_hash TEXT PRIMARY KEY,
    lines    BIGINT NOT NULL
);

-- Operator-purged paths: the server-side equivalent of .kohignore (which kepr
-- strips from manifests, so it cannot be edited server-side). A path here was
-- stripped from every save's manifest by an operator; future offers that
-- re-introduce it are filtered on ingest. Matches a file exactly or as a dir.
CREATE TABLE repo_purged_paths (
    repo_id   TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,
    path      TEXT NOT NULL,
    purged_at BIGINT NOT NULL,
    PRIMARY KEY (repo_id, path)
);

CREATE TABLE offer_tokens (
    offer_id    TEXT PRIMARY KEY,
    repo        TEXT NOT NULL,
    manifest_id TEXT NOT NULL,
    missing     TEXT NOT NULL,
    manifest    TEXT NOT NULL,
    key_type    TEXT NOT NULL CHECK (key_type IN ('operator','contributor')),
    expires_at  BIGINT NOT NULL,
    -- Account id that negotiated this token; complete must be performed by the
    -- same account (§8.9). NULL only for tokens predating protocol 2.0.
    negotiated_account TEXT
);

CREATE TABLE pending_offers (
    offer_id      TEXT PRIMARY KEY,
    repo          TEXT NOT NULL,
    manifest_id   TEXT NOT NULL,
    manifest      TEXT NOT NULL,
    contributor   TEXT,
    submitted_at  BIGINT NOT NULL,
    status        TEXT NOT NULL DEFAULT 'pending'
        CHECK (status IN ('draft','pending','accepted','declined')),
    reviewed_at   BIGINT,
    review_note   TEXT,
    agent_verdict TEXT,
    agent_status  TEXT DEFAULT 'pending'
        CHECK (agent_status IN ('pending','running','complete','failed','unavailable')),
    findings_json TEXT,
    scan_status   TEXT CHECK (scan_status IN ('ok','failed')),
    -- Contributor's human-readable note to the reviewer (separate from save
    -- messages); 'draft' status holds an offer out of the review queue until
    -- the contributor promotes it.
    description   TEXT
);

CREATE TABLE pending_offer_identity_quarantine (
    offer_id           TEXT PRIMARY KEY REFERENCES pending_offers(offer_id) ON DELETE CASCADE,
    legacy_fingerprint TEXT NOT NULL,
    reason             TEXT NOT NULL,
    recorded_at        BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW())::BIGINT
);
CREATE INDEX pending_offer_identity_quarantine_fp
    ON pending_offer_identity_quarantine(legacy_fingerprint);

CREATE TABLE steals (
    id        TEXT PRIMARY KEY,
    repo      TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,
    timestamp BIGINT NOT NULL
);
CREATE INDEX steals_repo_ts ON steals(repo, timestamp);

CREATE TABLE storage_snapshots (
    date           TEXT PRIMARY KEY,
    total_bytes    BIGINT NOT NULL,
    object_count   BIGINT NOT NULL,
    repo_count     BIGINT NOT NULL,
    save_count     BIGINT NOT NULL,
    languages      TEXT,
    first_received BIGINT
);

CREATE TABLE instance_images (
    filename     TEXT PRIMARY KEY,
    created_at   BIGINT NOT NULL,
    size_bytes   BIGINT NOT NULL,
    checksum     TEXT NOT NULL,
    repo_count   BIGINT NOT NULL,
    save_count   BIGINT NOT NULL,
    kepr_version TEXT NOT NULL
);

CREATE TABLE diff_cache (
    repo        TEXT NOT NULL,
    save_id     TEXT NOT NULL,
    diff_json   TEXT NOT NULL,
    computed_at BIGINT NOT NULL,
    PRIMARY KEY (repo, save_id)
);

CREATE TABLE ops_credentials (
    key   TEXT PRIMARY KEY,
    value TEXT NOT NULL
);

-- ── Contributor handles ──────────────────────────────────────────────

CREATE TABLE handles (
    id            TEXT PRIMARY KEY,
    handle        TEXT UNIQUE NOT NULL,
    created_at    BIGINT NOT NULL,
    recovery_hash TEXT NOT NULL
);

-- ── Assets, dependencies, builds, releases ───────────────────────────

CREATE TABLE repo_assets (
    id          TEXT PRIMARY KEY,
    repo_id     TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,
    filename    TEXT NOT NULL,
    mime_type   TEXT NOT NULL,
    size_bytes  BIGINT NOT NULL,
    uploaded_at BIGINT NOT NULL
);

CREATE TABLE repo_dependencies (
    from_repo       TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,
    to_repo         TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,
    first_stolen_at BIGINT NOT NULL,
    last_stolen_at  BIGINT NOT NULL,
    PRIMARY KEY (from_repo, to_repo)
);

CREATE TABLE build_runs (
    id               TEXT PRIMARY KEY,
    repo_id          TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,
    save_id          TEXT NOT NULL,
    version          TEXT NOT NULL,
    trigger          TEXT NOT NULL,
    status           TEXT NOT NULL,
    started_at       BIGINT NOT NULL,
    finished_at      BIGINT,
    log              TEXT,
    release_notes    TEXT,
    platform_results TEXT NOT NULL DEFAULT '{}',
    duration_ms      BIGINT,
    lane             TEXT NOT NULL DEFAULT 'main'
);

CREATE TABLE release_artifacts (
    id          TEXT PRIMARY KEY,
    build_run   TEXT NOT NULL REFERENCES build_runs(id),
    repo_id     TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,
    version     TEXT NOT NULL,
    platform    TEXT NOT NULL,
    filename    TEXT NOT NULL,
    size_bytes  BIGINT NOT NULL,
    sha256      TEXT NOT NULL,
    blake3      TEXT NOT NULL,
    signature   TEXT NOT NULL DEFAULT '',
    built_at    BIGINT NOT NULL
);
CREATE UNIQUE INDEX release_artifacts_repo_version_platform
    ON release_artifacts (repo_id, version, platform);

-- ── Issues ───────────────────────────────────────────────────────────

CREATE TABLE issues (
    id            BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    repo          TEXT    NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,
    title         TEXT    NOT NULL,
    description   TEXT    NOT NULL,
    contact       TEXT,
    filed_at      BIGINT  NOT NULL,
    filed_save    TEXT    NOT NULL,
    filed_ver     TEXT,
    status        TEXT    NOT NULL DEFAULT 'open',
    closed_at     BIGINT,
    closed_save   TEXT,
    ip_hash       TEXT,
    number        BIGINT  NOT NULL DEFAULT 0,
    type          TEXT    NOT NULL DEFAULT 'bug',
    severity      TEXT,
    face_id       TEXT,
    resolution_face TEXT,
    account_id    TEXT,
    contact_email TEXT,
    updated_at    BIGINT  NOT NULL DEFAULT 0,
    reporter_name TEXT
);
CREATE UNIQUE INDEX issues_repo_number ON issues(repo, number);
CREATE INDEX issues_repo_status_created
    ON issues(repo, status, filed_at DESC);
CREATE INDEX issues_repo_type_status_created
    ON issues(repo, type, status, filed_at DESC);

CREATE TABLE issue_notes (
    id        BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    issue_id  BIGINT  NOT NULL REFERENCES issues(id),
    body      TEXT    NOT NULL,
    posted_at BIGINT  NOT NULL,
    is_op     BIGINT  NOT NULL DEFAULT 0
);

CREATE TABLE issue_entries (
    id         TEXT PRIMARY KEY,
    issue_id   BIGINT NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
    account_id TEXT,
    entry_type TEXT NOT NULL CHECK (entry_type IN ('reply','status_change','face_linked')),
    body       TEXT,
    created_at BIGINT NOT NULL
);
CREATE INDEX issue_entries_issue ON issue_entries(issue_id, created_at);

CREATE TABLE issue_attachments (
    id          TEXT PRIMARY KEY,
    issue_id    BIGINT REFERENCES issues(id) ON DELETE CASCADE,
    repo_id     TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,
    filename    TEXT NOT NULL,
    mime_type   TEXT NOT NULL,
    size_bytes  BIGINT NOT NULL,
    path        TEXT NOT NULL,
    tmp_token   TEXT,
    tmp_expires BIGINT,
    created_at  BIGINT NOT NULL
);
CREATE INDEX issue_attachments_issue ON issue_attachments(issue_id, created_at);
CREATE INDEX issue_attachments_repo ON issue_attachments(repo_id, created_at);

-- ── Caches and challenges ────────────────────────────────────────────

CREATE TABLE graph_cache (
    repo         TEXT NOT NULL,
    head_save_id TEXT NOT NULL,
    json         TEXT NOT NULL,
    computed_at  BIGINT NOT NULL,
    PRIMARY KEY (repo, head_save_id)
);

CREATE TABLE archive_cache (
    repo_id    TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,
    save_id    TEXT NOT NULL,
    path       TEXT NOT NULL,
    size_bytes BIGINT NOT NULL,
    created_at BIGINT NOT NULL,
    -- Vestigial: live archive nar hashes are kept in archive_hashes now. The
    -- column still exists in the SQLite source schema, so it is mirrored here so
    -- the one-time SQLite→Postgres migration (which copies every source column
    -- via PRAGMA table_info) does not fail on a missing target column.
    nar_hash   TEXT,
    PRIMARY KEY (repo_id, save_id)
);

CREATE TABLE archive_hashes (
    repo_id     TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,
    save_id     TEXT NOT NULL,
    blake3      TEXT NOT NULL,
    sha256      TEXT NOT NULL,
    nar_hash    TEXT,
    computed_at BIGINT NOT NULL,
    PRIMARY KEY (repo_id, save_id)
);

CREATE TABLE pow_challenges (
    id         TEXT PRIMARY KEY,
    nonce      TEXT NOT NULL,
    difficulty BIGINT NOT NULL DEFAULT 4,
    created_at BIGINT NOT NULL,
    expires_at BIGINT NOT NULL,
    used       BIGINT NOT NULL DEFAULT 0
);

-- ── Accounts, sessions, auth ─────────────────────────────────────────

CREATE TABLE accounts (
    id                   TEXT PRIMARY KEY,
    username             TEXT NOT NULL UNIQUE,
    bio                  TEXT,
    location             TEXT,
    website              TEXT,
    email                TEXT,
    recovery_hash        TEXT NOT NULL,
    created_at           BIGINT NOT NULL,
    tier                 TEXT NOT NULL DEFAULT 'contributor'
        CHECK (tier IN ('operator', 'team', 'contributor')),
    -- Operator web-login credentials (bcrypt password + base32 TOTP secret).
    -- NULL until the operator enrols at /login; only the operator-tier row uses them.
    password_hash        TEXT,
    totp_secret          TEXT,
    notif_offer_accepted BIGINT NOT NULL DEFAULT 1,
    notif_offer_rejected BIGINT NOT NULL DEFAULT 1,
    notif_issue_response BIGINT NOT NULL DEFAULT 1,
    notif_issue_resolved BIGINT NOT NULL DEFAULT 1,
    notif_issue_status   BIGINT NOT NULL DEFAULT 1
);
CREATE INDEX accounts_username ON accounts(username);
-- Partial unique: one account per non-null email, enforced at the database
-- so the join handler's check-then-act can't lose a race under concurrent
-- registrations (review F4). Existing databases get this via db.zig's
-- ensureUniqueEmailIndex idempotent migration instead, which must detect
-- pre-existing duplicates first (D4).
CREATE UNIQUE INDEX accounts_email_unique ON accounts(email) WHERE email IS NOT NULL;

-- ── Internal tickets ─────────────────────────────────────────────────
-- Operator/team-only ticket tracker. created_by/assigned_to reference
-- accounts(id) (TEXT, not the int ids the original brief assumed).
-- resolving_save_id is a plain TEXT column with no FK: saves has a
-- composite PRIMARY KEY (id, repo), so a single-column FK to saves(id)
-- is not expressible; integrity is enforced in the application layer.

CREATE TABLE tickets (
    id                BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    title             TEXT NOT NULL,
    body              TEXT NOT NULL DEFAULT '',
    state             TEXT NOT NULL DEFAULT 'open'
        CHECK (state IN ('open', 'in_progress', 'closed')),
    repo              TEXT REFERENCES repos(name) ON UPDATE CASCADE ON DELETE SET NULL,
    resolving_save_id TEXT,
    created_by        TEXT NOT NULL REFERENCES accounts(id),
    assigned_to       TEXT REFERENCES accounts(id) ON DELETE SET NULL,
    created_at        BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW())::BIGINT,
    updated_at        BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW())::BIGINT,
    closed_at         BIGINT
);
CREATE INDEX tickets_state ON tickets(state, created_at DESC);
CREATE INDEX tickets_repo ON tickets(repo);
CREATE INDEX tickets_assigned ON tickets(assigned_to);
CREATE INDEX tickets_created_by ON tickets(created_by);

CREATE TABLE ticket_attachments (
    id          TEXT PRIMARY KEY,
    ticket_id   BIGINT REFERENCES tickets(id) ON DELETE CASCADE,
    repo_id     TEXT REFERENCES repos(name) ON UPDATE CASCADE,
    filename    TEXT NOT NULL,
    mime_type   TEXT NOT NULL,
    size_bytes  BIGINT NOT NULL,
    path        TEXT NOT NULL,
    created_at  BIGINT NOT NULL
);
CREATE INDEX ticket_attachments_ticket ON ticket_attachments(ticket_id, created_at);
CREATE INDEX ticket_attachments_repo ON ticket_attachments(repo_id, created_at);

-- ── Collaboration layer + notifications ──────────────────────────────
-- author/account columns reference accounts(id) (TEXT). Comment ids are
-- BIGINT identity to match the project convention (the brief assumed
-- SQLite-era INTEGER identity). save_comments carries both save_id and
-- repo so a composite FK to saves(id, repo) is expressible; the other
-- save-keyed tables hold only save_id (plain TEXT, no FK — saves has a
-- composite PK) and rely on application-layer integrity. Boolean-ish
-- columns are BIGINT 0/1 to match the existing schema (accounts.notif_*,
-- repos.*), since the pg shim binds/reads integers, not SQL BOOLEAN.

CREATE TABLE save_comments (
    id                BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    save_id           TEXT NOT NULL,
    repo              TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE ON DELETE CASCADE,
    author_account_id TEXT NOT NULL REFERENCES accounts(id),
    body              TEXT NOT NULL,
    created_at        BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW())::BIGINT,
    updated_at        BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW())::BIGINT,
    edited            BIGINT NOT NULL DEFAULT 0,
    FOREIGN KEY (save_id, repo) REFERENCES saves(id, repo) ON DELETE CASCADE
);
CREATE INDEX save_comments_save_id ON save_comments(save_id, created_at);
CREATE INDEX save_comments_repo ON save_comments(repo, created_at DESC);
CREATE INDEX save_comments_author ON save_comments(author_account_id);

CREATE TABLE save_acknowledgements (
    save_id    TEXT NOT NULL,
    account_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
    kind       TEXT NOT NULL DEFAULT 'ack'
        CHECK (kind IN ('ack', 'flag')),
    created_at BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW())::BIGINT,
    PRIMARY KEY (save_id, account_id)
);
CREATE INDEX save_acks_save_id ON save_acknowledgements(save_id);

CREATE TABLE draft_comments (
    id                BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    offer_id          TEXT NOT NULL REFERENCES pending_offers(offer_id) ON DELETE CASCADE,
    author_account_id TEXT NOT NULL REFERENCES accounts(id),
    body              TEXT NOT NULL,
    created_at        BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW())::BIGINT
);
CREATE INDEX draft_comments_offer ON draft_comments(offer_id, created_at);

CREATE TABLE notifications (
    id           BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    account_id   TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
    kind         TEXT NOT NULL CHECK (kind IN (
        'save_comment', 'save_comment_reply', 'save_mention', 'save_ack',
        'save_flag', 'contribution_accepted', 'contribution_declined',
        'contribution_pending', 'draft_comment', 'issue_filed', 'issue_reply',
        'issue_response', 'issue_resolved', 'issue_status',
        'ticket_assigned', 'ticket_state_changed', 'team_promoted',
        'milestone_saves', 'milestone_downloads', 'milestone_first_steal',
        'milestone_first_contributor', 'milestone_trusted'
    )),
    payload_json TEXT NOT NULL DEFAULT '{}',
    preview      TEXT NOT NULL DEFAULT '',
    href         TEXT NOT NULL DEFAULT '/',
    read         BIGINT NOT NULL DEFAULT 0,
    created_at   BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW())::BIGINT,
    read_at      BIGINT
);
CREATE INDEX notifications_account_unread ON notifications(account_id, read, created_at DESC);
CREATE INDEX notifications_account_all ON notifications(account_id, created_at DESC);
CREATE INDEX notifications_created_at ON notifications(created_at DESC);

CREATE TABLE notification_preferences (
    account_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
    kind       TEXT NOT NULL,
    in_app     BIGINT NOT NULL DEFAULT 1,
    email      BIGINT NOT NULL DEFAULT 0,
    PRIMARY KEY (account_id, kind)
);

CREATE TABLE notification_category_prefs (
    account_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
    category   TEXT NOT NULL CHECK (category IN (
        'contributions', 'discussion', 'issues', 'tickets', 'milestones'
    )),
    in_app     BIGINT NOT NULL DEFAULT 1,
    email      BIGINT NOT NULL DEFAULT 0,
    PRIMARY KEY (account_id, category)
);

CREATE TABLE sessions (
    id         TEXT PRIMARY KEY,
    account_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
    created_at BIGINT NOT NULL,
    expires_at BIGINT NOT NULL,
    last_used  BIGINT NOT NULL,
    user_agent TEXT
);
CREATE INDEX sessions_account ON sessions(account_id);
CREATE INDEX sessions_expiry ON sessions(expires_at);

CREATE TABLE account_recovery_codes (
    id          TEXT PRIMARY KEY,
    account_id  TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
    code_hash   TEXT NOT NULL,
    created_at  BIGINT NOT NULL,
    expires_at  BIGINT NOT NULL,
    used        BIGINT NOT NULL DEFAULT 0
);
CREATE INDEX account_recovery_codes_account ON account_recovery_codes(account_id, expires_at);

-- ── Health, safety, trust ────────────────────────────────────────────

CREATE TABLE repo_health (
    repo_id        TEXT PRIMARY KEY REFERENCES repos(name) ON UPDATE CASCADE,
    save_id        TEXT NOT NULL,
    computed_at    BIGINT NOT NULL,
    secrets_status TEXT NOT NULL DEFAULT 'clean',
    secrets_detail TEXT,
    deps_status    TEXT NOT NULL DEFAULT 'none',
    deps_detail    TEXT,
    builds_status  TEXT NOT NULL DEFAULT 'none',
    builds_detail  TEXT,
    agents_status  TEXT NOT NULL DEFAULT 'none',
    agents_detail  TEXT,
    license_status TEXT NOT NULL DEFAULT 'none',
    license_detail TEXT,
    trust_status   TEXT NOT NULL DEFAULT 'clean',
    trust_detail   TEXT,
    collapsed_html TEXT NOT NULL DEFAULT ''
);

CREATE TABLE secret_scan_results (
    id          TEXT PRIMARY KEY,
    repo_id     TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,
    save_id     TEXT NOT NULL,
    file_path   TEXT NOT NULL,
    line_number BIGINT,
    pattern     TEXT NOT NULL,
    entropy     DOUBLE PRECISION,
    severity    TEXT NOT NULL,
    status      TEXT NOT NULL,
    detected_at BIGINT NOT NULL
);
CREATE INDEX secret_scan_results_repo_save
    ON secret_scan_results(repo_id, save_id, detected_at DESC);

CREATE TABLE secret_false_positives (
    repo_id   TEXT NOT NULL,
    file_path TEXT NOT NULL,
    marked_at BIGINT NOT NULL,
    PRIMARY KEY (repo_id, file_path)
);

CREATE TABLE code_finding_false_positives (
    repo_id   TEXT NOT NULL,
    code      TEXT NOT NULL,
    path      TEXT NOT NULL,
    marked_at BIGINT NOT NULL,
    PRIMARY KEY (repo_id, code, path)
);

-- Operator-dismissed safety findings. A suppressed finding is filtered from
-- the public repo page and the admin panel; the dismissed list remains
-- visible and reversible. Keyed by (repo_id, file_path, rule, fingerprint)
-- so a suppression only matches when the same content fires the same rule.
CREATE TABLE safety_suppressions (
    id           TEXT PRIMARY KEY,
    repo_id      TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE ON DELETE CASCADE,
    file_path    TEXT NOT NULL,
    rule         TEXT NOT NULL,
    fingerprint  TEXT NOT NULL,
    dismissed_at BIGINT NOT NULL,
    dismissed_by TEXT NOT NULL REFERENCES accounts(id),
    note         TEXT
);
CREATE INDEX safety_suppressions_repo ON safety_suppressions(repo_id, file_path, rule);

CREATE TABLE dep_advisories (
    id          TEXT PRIMARY KEY,
    repo_id     TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,
    save_id     TEXT NOT NULL,
    dep_name    TEXT NOT NULL,
    dep_version TEXT NOT NULL,
    cve_id      TEXT,
    severity    TEXT NOT NULL,
    summary     TEXT NOT NULL,
    fix_version TEXT,
    detected_at BIGINT NOT NULL
);
CREATE INDEX dep_advisories_repo_save
    ON dep_advisories(repo_id, save_id, detected_at DESC);

CREATE TABLE binary_sizes (
    id         TEXT PRIMARY KEY,
    repo_id    TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,
    save_id    TEXT NOT NULL,
    platform   TEXT NOT NULL,
    size_bytes BIGINT NOT NULL,
    built_at   BIGINT NOT NULL
);
CREATE INDEX binary_sizes_repo_save
    ON binary_sizes(repo_id, save_id, built_at DESC);

CREATE TABLE trust_results (
    repo_id             TEXT PRIMARY KEY REFERENCES repos(name) ON UPDATE CASCADE,
    save_id             TEXT NOT NULL,
    trusted             BIGINT NOT NULL,
    secrets_clean       BIGINT NOT NULL,
    no_binary_blobs     BIGINT NOT NULL,
    build_scripts_clean BIGINT NOT NULL,
    no_trojan_source    BIGINT NOT NULL,
    supply_chain_stable BIGINT NOT NULL,
    licenses_compatible BIGINT NOT NULL,
    has_attribution     BIGINT NOT NULL,
    detail_json         TEXT NOT NULL DEFAULT '{}',
    computed_at         BIGINT NOT NULL
);

-- ── Engagement and notifications ─────────────────────────────────────

CREATE TABLE engagement_events (
    id          TEXT PRIMARY KEY,
    event_type  TEXT NOT NULL,
    repo_id     TEXT,
    save_id     TEXT,
    platform    TEXT,
    occurred_at BIGINT NOT NULL
);
CREATE INDEX eng_repo_type ON engagement_events(repo_id, event_type, occurred_at DESC);
CREATE INDEX eng_type_time ON engagement_events(event_type, occurred_at DESC);

CREATE TABLE notification_queue (
    id         TEXT PRIMARY KEY,
    account_id TEXT,
    type       TEXT NOT NULL,
    payload    TEXT NOT NULL,
    status     TEXT NOT NULL DEFAULT 'pending',
    attempts   BIGINT NOT NULL DEFAULT 0,
    next_retry BIGINT,
    created_at BIGINT NOT NULL,
    sent_at    BIGINT
);
CREATE INDEX notification_queue_pending
    ON notification_queue(status, next_retry);

-- ┌─────────────────────────────────────────────────────────────────┐
-- │  JOB QUEUE — Postgres-native, SELECT FOR UPDATE SKIP LOCKED.      │
-- │  New in the Postgres architecture; has no SQLite predecessor.     │
-- └─────────────────────────────────────────────────────────────────┘

CREATE TABLE jobs (
    id           BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    kind         TEXT NOT NULL,
    payload_json TEXT NOT NULL DEFAULT '{}',
    status       TEXT NOT NULL DEFAULT 'pending'
        CHECK (status IN ('pending','running','done','failed')),
    -- Only one pending/running job per (kind, dedup_key) — collapses the
    -- thundering herd of a 1000-save offer into one job of each kind per repo.
    dedup_key    TEXT,
    priority     BIGINT NOT NULL DEFAULT 5,
    attempts     BIGINT NOT NULL DEFAULT 0,
    max_attempts BIGINT NOT NULL DEFAULT 3,
    created_at   BIGINT NOT NULL,
    started_at   BIGINT,
    completed_at BIGINT,
    error_text   TEXT,
    run_after    BIGINT NOT NULL DEFAULT 0
);

-- Worker poll path: lowest priority then oldest, pending only.
CREATE INDEX jobs_pending ON jobs(priority, created_at)
    WHERE status = 'pending';

-- Deduplication: enforce one live job per (kind, dedup_key).
CREATE UNIQUE INDEX jobs_dedup ON jobs(kind, dedup_key)
    WHERE status IN ('pending','running') AND dedup_key IS NOT NULL;

-- Cleanup scan for pruning finished jobs.
CREATE INDEX jobs_completed_at ON jobs(completed_at)
    WHERE status IN ('done','failed');

COMMIT;