-- 0009_admin_creds_and_repo_stats.sql
--
-- Phase 5 of the P2 redesign needs two things in the schema:
--
-- 1. A second credential row per host. Today host_credentials is
--    1:1 with hosts. For prune (and any future destructive op) we
--    want a rest-server admin user whose password gives delete
--    access — separate from the append-only user used on every
--    backup. Add a `kind` column with default 'repo'; existing rows
--    become kind='repo'. Future admin rows live alongside.
--
-- 2. A small singleton-per-host projection for repo size, snapshot
--    count, last-prune freed bytes, lock state, and last-check
--    result. Backed by `restic stats --json` + sniffed `restic
--    check` stderr.
--
-- Use column-level ALTERs only; host_credentials has no inbound
-- FKs but the rule from CLAUDE.md still applies.

ALTER TABLE host_credentials ADD COLUMN kind TEXT NOT NULL DEFAULT 'repo';

-- The PK on host_credentials is currently (host_id) — we need a
-- composite (host_id, kind). SQLite has no ALTER TABLE …
-- ADD/CHANGE PRIMARY KEY, so this is the one place a rebuild is
-- justified. host_credentials has no inbound FKs, so the cascade
-- trap doesn't apply here. Verified against schema/0002.

CREATE TABLE host_credentials_new (
  host_id        TEXT NOT NULL REFERENCES hosts(id) ON DELETE CASCADE,
  kind           TEXT NOT NULL DEFAULT 'repo'
                   CHECK (kind IN ('repo', 'admin')),
  enc_repo_creds TEXT NOT NULL,
  updated_at     TEXT NOT NULL,
  PRIMARY KEY (host_id, kind)
);
INSERT INTO host_credentials_new (host_id, kind, enc_repo_creds, updated_at)
  SELECT host_id, kind, enc_repo_creds, updated_at FROM host_credentials;
DROP TABLE host_credentials;
ALTER TABLE host_credentials_new RENAME TO host_credentials;

-- Repo stats projection. One row per host, upserted by the agent's
-- stats.report envelope (which fires after every successful backup
-- and after every check / prune). All fields nullable so a freshly
-- enrolled host with no jobs yet is representable.

CREATE TABLE host_repo_stats (
  host_id                 TEXT PRIMARY KEY REFERENCES hosts(id) ON DELETE CASCADE,
  total_size_bytes        INTEGER,
  raw_size_bytes          INTEGER,
  unique_files            INTEGER,
  snapshot_count          INTEGER,
  last_check_at           TEXT,
  last_check_status       TEXT CHECK (last_check_status IS NULL OR last_check_status IN ('ok', 'errors_found', 'failed')),
  lock_present            INTEGER NOT NULL DEFAULT 0,
  last_prune_at           TEXT,
  last_prune_freed_bytes  INTEGER,
  updated_at              TEXT NOT NULL
);
