-- Damascus orchestrator — PostgreSQL 16 schema.
-- Atomic claim is implemented with `SELECT ... FOR UPDATE SKIP LOCKED`
-- on `work_items`, gated by a per-row `phase` enum.
--
-- Postgres is the authoritative scheduler (design doc §3). This file is
-- applied by `damascus init`, which creates the `damascus` database first
-- (CREATE DATABASE cannot run inside a transaction/DO block) and then
-- executes this whole file in a single execute() call.

-- --- enum types (idempotent; CREATE TYPE has no IF NOT EXISTS) ------------

DO $$ BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'work_item_phase') THEN
    CREATE TYPE work_item_phase AS ENUM
      ('spec','build','review','merged','blocked','awaiting_human');
  END IF;
END $$;

DO $$ BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'gate_kind') THEN
    CREATE TYPE gate_kind AS ENUM ('and','or','first');
  END IF;
END $$;

DO $$ BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'issue_status') THEN
    CREATE TYPE issue_status AS ENUM ('open','answered','resolved');
  END IF;
END $$;

DO $$ BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'verdict_kind') THEN
    -- design doc §5: pass | tests_failed | rebase_conflict |
    -- spec_ambiguous | spec_wrong | no_pr
    CREATE TYPE verdict_kind AS ENUM
      ('pass','tests_failed','rebase_conflict','spec_ambiguous','spec_wrong','no_pr');
  END IF;
END $$;

-- --- shared trigger function: keep updated_at honest (no ON UPDATE in PG) --

CREATE OR REPLACE FUNCTION fn_touch_updated_at() RETURNS trigger AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- --- tables ---------------------------------------------------------------

-- work_items: one row per story, across all projects.
CREATE TABLE IF NOT EXISTS work_items (
  id              CHAR(36) NOT NULL PRIMARY KEY,
  project         VARCHAR(64) NOT NULL,
  story_id        VARCHAR(128) NOT NULL,
  title           VARCHAR(255) NOT NULL DEFAULT '',
  phase           work_item_phase NOT NULL DEFAULT 'spec',
  file_scope      JSONB NOT NULL DEFAULT '[]'::jsonb,
  attempts        INT NOT NULL DEFAULT 0,
  budget_cycles   INT NOT NULL DEFAULT 3,        -- amendment §4: default N=3 (was 5). Per-row configurable; this is just the default for new rows.
  priority        INT NOT NULL DEFAULT 100,
  base_commit     VARCHAR(64) DEFAULT NULL,
  branch          VARCHAR(128) DEFAULT NULL,
  pr_url          VARCHAR(512) DEFAULT NULL,
  last_verdict    verdict_kind DEFAULT NULL,
  last_feedback   JSONB DEFAULT NULL,
  spec_path       VARCHAR(512) DEFAULT NULL,
  wiki_pin        VARCHAR(64) DEFAULT NULL,
  claimed_by      VARCHAR(64) DEFAULT NULL,
  claimed_at      TIMESTAMPTZ DEFAULT NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  merged_at       TIMESTAMPTZ DEFAULT NULL,
  -- ADR-005: set by claim_for_* on first claim; used by cycle.py to escalate
  -- persistent transient retries to blocked after 24h.
  first_attempted_at TIMESTAMPTZ DEFAULT NULL,
  UNIQUE (project, story_id)
);

DROP TRIGGER IF EXISTS trg_work_items_touch ON work_items;
CREATE TRIGGER trg_work_items_touch BEFORE UPDATE ON work_items
  FOR EACH ROW EXECUTE FUNCTION fn_touch_updated_at();

CREATE INDEX IF NOT EXISTS idx_work_items_phase_priority
  ON work_items (phase, priority, updated_at);

-- coordination_gates: AND/OR/FIRST dependency edges between stories.
CREATE TABLE IF NOT EXISTS coordination_gates (
  parent_id   CHAR(36) NOT NULL,
  child_id    CHAR(36) NOT NULL,
  kind        gate_kind NOT NULL DEFAULT 'and',
  PRIMARY KEY (parent_id, child_id)
);

CREATE INDEX IF NOT EXISTS idx_coord_gates_child ON coordination_gates (child_id);

-- human_issues: async channel for open questions / approvals.
CREATE TABLE IF NOT EXISTS human_issues (
  id           CHAR(36) NOT NULL PRIMARY KEY,
  work_item_id CHAR(36) NOT NULL,
  question     TEXT NOT NULL,
  answer       TEXT DEFAULT NULL,
  status       issue_status NOT NULL DEFAULT 'open',
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  answered_at  TIMESTAMPTZ DEFAULT NULL
);

CREATE INDEX IF NOT EXISTS idx_human_issues_status ON human_issues (status, created_at);
CREATE INDEX IF NOT EXISTS idx_human_issues_item ON human_issues (work_item_id);

-- cost_ledger: per-call token + dollar spend.
CREATE TABLE IF NOT EXISTS cost_ledger (
  id            BIGSERIAL PRIMARY KEY,
  work_item_id  CHAR(36) DEFAULT NULL,
  project       VARCHAR(64) DEFAULT NULL,
  phase         VARCHAR(32) DEFAULT NULL,
  model         VARCHAR(128) DEFAULT NULL,
  input_tokens  INT DEFAULT NULL,
  output_tokens INT DEFAULT NULL,
  usd           DECIMAL(10,6) DEFAULT NULL,
  recorded_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_cost_ledger_item ON cost_ledger (work_item_id);
CREATE INDEX IF NOT EXISTS idx_cost_ledger_recorded ON cost_ledger (recorded_at);

-- events_outbox: per-cycle transition log. Written in the same transaction
-- as the state update (transactional outbox, design doc §8). A drainer
-- delivers these to the overseer with idempotency keys (Phase 3).
CREATE TABLE IF NOT EXISTS events_outbox (
  id           BIGSERIAL PRIMARY KEY,
  work_item_id CHAR(36) DEFAULT NULL,
  kind         VARCHAR(64) NOT NULL,
  payload      JSONB NOT NULL,
  delivered    BOOLEAN NOT NULL DEFAULT FALSE,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_events_outbox_delivered
  ON events_outbox (delivered, created_at);

-- A handy view for the external concurrency tracker.
CREATE OR REPLACE VIEW v_active_claims AS
  SELECT id, project, story_id, phase, claimed_by, claimed_at, updated_at
    FROM work_items
   WHERE phase IN ('spec','build','review');