-- KanBot v0 schema — column names quoted.
-- `column` is a reserved keyword in PostgreSQL, so every reference to the
-- cards.column / trigger NEW.column / OLD.column must be double-quoted.
-- This file is the version used by the HF Space Docker image.
-- Idempotent: IF NOT EXISTS / DROP TRIGGER IF EXISTS / DO $$ ... EXCEPTION.

BEGIN;

-- --------------------------------------------------------------------------- --
-- enums mirror core/contract.py exactly. Keep them in sync.
-- --------------------------------------------------------------------------- --
DO $$ BEGIN
  CREATE TYPE kb_column AS ENUM
    ('backlog','ready','in-flight','review','blocked','done');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
  CREATE TYPE kb_role AS ENUM ('planner','executor','critic','human');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

-- --------------------------------------------------------------------------- --
-- cards = the atoms. thread holds inter-agent comms as an ordered JSONB array.
-- --------------------------------------------------------------------------- --
CREATE TABLE IF NOT EXISTS cards (
  id                   TEXT PRIMARY KEY,             -- CARD-42
  title                TEXT NOT NULL,
  "column"             kb_column NOT NULL DEFAULT 'backlog',
  agent_role           kb_role  NOT NULL DEFAULT 'executor',
  spec                 TEXT NOT NULL DEFAULT '',
  acceptance_criteria  JSONB NOT NULL DEFAULT '[]'::jsonb,
  assigned_worker      TEXT,
  parent               TEXT REFERENCES cards(id) ON DELETE SET NULL,
  children             JSONB NOT NULL DEFAULT '[]'::jsonb,
  thread               JSONB NOT NULL DEFAULT '[]'::jsonb,
  lease_expires_at     TIMESTAMPTZ,                  -- drives the reaper
  attempt              INT  NOT NULL DEFAULT 0,
  requires_human       BOOLEAN NOT NULL DEFAULT FALSE,
  tags                 JSONB NOT NULL DEFAULT '[]'::jsonb,  -- ["heavy"] -> cloud
  created_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at           TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_cards_column ON cards("column");
CREATE INDEX IF NOT EXISTS idx_cards_lease  ON cards(lease_expires_at)
  WHERE "column" = 'in-flight';

-- --------------------------------------------------------------------------- --
-- board_events = the append-only transition log. Also the idempotency ledger.
-- --------------------------------------------------------------------------- --
CREATE TABLE IF NOT EXISTS board_events (
  id         BIGSERIAL PRIMARY KEY,
  card_id    TEXT NOT NULL REFERENCES cards(id) ON DELETE CASCADE,
  from_col   kb_column,
  to_col     kb_column NOT NULL,
  attempt    INT NOT NULL DEFAULT 0,
  actor      TEXT NOT NULL,       -- worker id / "human:remy" / "system:reaper"
  reason     TEXT,
  ts         TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Replays of the same transition are no-ops (ON CONFLICT DO NOTHING upstream).
CREATE UNIQUE INDEX IF NOT EXISTS uq_transition
  ON board_events(card_id, from_col, to_col, attempt);

-- --------------------------------------------------------------------------- --
-- NOTIFY trigger: any column change publishes a JSON payload on 'kb_events'.
-- The Conductor LISTENs on this channel and dispatches an agent per event.
-- --------------------------------------------------------------------------- --
CREATE OR REPLACE FUNCTION notify_card_transition() RETURNS trigger AS $$
BEGIN
  IF (TG_OP = 'UPDATE' AND NEW."column" IS DISTINCT FROM OLD."column")
     OR TG_OP = 'INSERT' THEN
    PERFORM pg_notify(
      'kb_events',
      json_build_object(
        'card_id',   NEW.id,
        'from',      CASE WHEN TG_OP='INSERT' THEN NULL ELSE OLD."column" END,
        'to',        NEW."column",
        'role',      NEW.agent_role,
        'attempt',   NEW.attempt,
        'tags',      NEW.tags,
        'ts',        extract(epoch from now())
      )::text
    );
  END IF;
  NEW.updated_at := now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_card_transition ON cards;
CREATE TRIGGER trg_card_transition
  BEFORE INSERT OR UPDATE ON cards
  FOR EACH ROW EXECUTE FUNCTION notify_card_transition();

COMMIT;