-- shepherd v6.5.7 — crash-safe singleton publication intents.
-- The nonce is the durable join between the SQLite intent and the filesystem
-- preparing/published/quarantined artifacts. A logical singleton claim points
-- at at most one current nonce; old nonces remain auditable history. The
-- immutable claim snapshot makes nonce replay exact even after quarantine
-- releases the live claim row.
PRAGMA foreign_keys = ON;

BEGIN;

CREATE TABLE dispatch_singleton_publications (
  nonce              TEXT PRIMARY KEY CHECK(
    length(nonce) BETWEEN 8 AND 128 AND
    nonce NOT GLOB '*[^a-z0-9-]*'
  ),
  project_id         TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE ON UPDATE CASCADE,
  run_id             TEXT NOT NULL CHECK(length(run_id) BETWEEN 1 AND 64 AND run_id NOT GLOB '*[^a-z0-9-]*'),
  role               TEXT NOT NULL CHECK(role IN ('engineer', 'conductor')),
  lane_key           TEXT NOT NULL CHECK(length(lane_key) BETWEEN 1 AND 64 AND lane_key NOT GLOB '*[^a-z0-9_-]*'),
  claim_json         TEXT NOT NULL CHECK(json_valid(claim_json) AND json_type(claim_json) = 'object'),
  record_path        TEXT NOT NULL CHECK(
    length(record_path) BETWEEN 1 AND 512 AND
    record_path NOT LIKE '/%' AND
    record_path NOT LIKE '%\\%' AND
    record_path NOT LIKE '%..%'
  ),
  record_sha256      TEXT NOT NULL CHECK(
    length(record_sha256) = 64 AND
    record_sha256 NOT GLOB '*[^0-9a-f]*'
  ),
  record_json        TEXT NOT NULL CHECK(
    length(record_json) > 0 AND
    json_valid(record_json) AND
    json_type(record_json) = 'object'
  ),
  publication_state  TEXT NOT NULL CHECK(publication_state IN ('preparing', 'published', 'quarantined')),
  prepared_at        INTEGER NOT NULL CHECK(prepared_at >= 0),
  published_at       INTEGER CHECK(published_at IS NULL OR published_at >= prepared_at),
  quarantine_reason  TEXT CHECK(quarantine_reason IS NULL OR length(quarantine_reason) BETWEEN 1 AND 512),
  updated_at         INTEGER NOT NULL CHECK(updated_at >= prepared_at),
  CHECK (
    (publication_state = 'preparing' AND published_at IS NULL AND quarantine_reason IS NULL) OR
    (publication_state = 'published' AND published_at IS NOT NULL AND quarantine_reason IS NULL) OR
    (publication_state = 'quarantined' AND published_at IS NULL AND quarantine_reason IS NOT NULL)
  )
) WITHOUT ROWID;

CREATE UNIQUE INDEX idx_singleton_publications_preparing
  ON dispatch_singleton_publications(project_id, run_id, role, lane_key)
  WHERE publication_state = 'preparing';

CREATE INDEX idx_singleton_publications_key
  ON dispatch_singleton_publications(project_id, run_id, role, lane_key, updated_at DESC);

ALTER TABLE dispatch_singleton_claims
  ADD COLUMN publication_nonce TEXT REFERENCES dispatch_singleton_publications(nonce) ON DELETE SET NULL;

CREATE INDEX idx_singleton_claims_publication_nonce
  ON dispatch_singleton_claims(publication_nonce);

COMMIT;
