-- 0012_jobs_restore_diff_kind.sql
--
-- Add 'restore' and 'diff' to the jobs.kind CHECK constraint so the
-- restore wizard (P3-01) and the snapshot-diff endpoint (P3-09) can
-- persist their job rows. SQLite can't ALTER a CHECK in place, so we
-- rebuild the table.
--
-- Rebuild safety: jobs has an inbound FK from job_logs (ON DELETE
-- CASCADE) and from schedules.jobs is referenced via scheduled_id.
-- CLAUDE.md flags DROP TABLE on a parent as risky under
-- foreign_keys=ON; we mitigate two ways:
--
--   1. Stash job_logs into a temp table BEFORE rebuilding jobs, then
--      restore the rows after the rebuild settles. If a cascade
--      misbehaves we can still recover.
--   2. Use the safe rebuild order from 0005: create jobs_new with the
--      wider CHECK → copy data → DROP jobs → RENAME jobs_new TO jobs.
--      Do NOT rename the original first (the dangling-FK trap that
--      0005's first draft hit and 0006 cleaned up).

CREATE TEMPORARY TABLE _job_logs_backup AS
  SELECT job_id, seq, ts, stream, payload FROM job_logs;

CREATE TABLE jobs_new (
  id              TEXT PRIMARY KEY,
  host_id         TEXT NOT NULL REFERENCES hosts(id) ON DELETE CASCADE,
  kind            TEXT NOT NULL CHECK (kind IN
                       ('backup','init','forget','prune','check','unlock','restore','diff')),
  status          TEXT NOT NULL CHECK (status IN ('queued','running','succeeded','failed','cancelled')),
  scheduled_id    TEXT REFERENCES schedules(id) ON DELETE SET NULL,
  actor_kind      TEXT NOT NULL CHECK (actor_kind IN ('user','schedule','system')),
  actor_id        TEXT,
  started_at      TEXT,
  finished_at     TEXT,
  exit_code       INTEGER,
  stats           TEXT,
  error           TEXT,
  created_at      TEXT NOT NULL
);

INSERT INTO jobs_new
  SELECT id, host_id, kind, status, scheduled_id, actor_kind, actor_id,
         started_at, finished_at, exit_code, stats, error, created_at
  FROM jobs;

DROP TABLE jobs;

ALTER TABLE jobs_new RENAME TO jobs;

CREATE INDEX jobs_host_id ON jobs(host_id);
CREATE INDEX jobs_status ON jobs(status);
CREATE INDEX jobs_created_at ON jobs(created_at);

-- Defensive: if cascade-on-DROP wiped job_logs (it shouldn't with the
-- foreign_keys behaviour SQLite documents, but the codebase has hit
-- "lost rows" before during rebuilds), restore from the temp backup.
-- INSERT OR IGNORE so re-running is harmless.
INSERT OR IGNORE INTO job_logs (job_id, seq, ts, stream, payload)
  SELECT job_id, seq, ts, stream, payload FROM _job_logs_backup;

DROP TABLE _job_logs_backup;
