-- priority-forge-hub — shared_tasks schema for the OrchestrationHub plane.
--
-- Hub-canonical entity for cross-forge work: identity + audit + active
-- lease. Carries only what the SharedTask interface declares — NO task
-- content (priority/effort/notes/body) and NO learning signals beyond the
-- claim audit. See packages/priority-forge-hub/src/types.ts for the
-- SharedTask interface; this table mirrors it 1:1.
--
-- Run once per Postgres database that will host orchestration. Idempotent.

CREATE TABLE IF NOT EXISTS shared_tasks (
  id                    TEXT        PRIMARY KEY,
  team_id               TEXT        NOT NULL,
  title                 TEXT        NOT NULL,
  description           TEXT,
  -- deadline: optional ISO date string, frozen at publish as a projection
  -- seed. Advisory only — the claimer's forge recomputes time-sensitivity.
  deadline              TEXT,
  -- priority: optional P0-P3 label, frozen at publish as a projection seed.
  -- Advisory only — the claimer adopts it as a base label then re-ranks locally.
  priority              TEXT,
  originator_agent_id   TEXT        NOT NULL,
  originator_team_id    TEXT        NOT NULL,
  created_at            TIMESTAMPTZ NOT NULL DEFAULT now(),
  -- current_claim is jsonb-encoded ClaimInfo or NULL when claimable.
  -- Shape: { holderAgentId, holderTeamId, claimedAt, leaseUntil }.
  current_claim         JSONB,
  -- claim_history is an append-only array of ClaimHistoryEntry objects.
  -- Shape per entry: { event, agentId, agentTeamId, at, outcome? }.
  claim_history         JSONB       NOT NULL DEFAULT '[]'::jsonb,
  status                TEXT        NOT NULL
                                    CHECK (status IN ('claimable', 'claimed', 'completed', 'cancelled'))
);

-- Migration for databases created before deadline became a projection seed.
ALTER TABLE shared_tasks ADD COLUMN IF NOT EXISTS deadline TEXT;
-- Migration for databases created before priority became a projection seed.
ALTER TABLE shared_tasks ADD COLUMN IF NOT EXISTS priority TEXT;

-- list() filter pushdown: (team_id, status).
CREATE INDEX IF NOT EXISTS idx_shared_tasks_team_status
  ON shared_tasks (team_id, status);

-- getMyClaims() filter pushdown: current claim holder.
CREATE INDEX IF NOT EXISTS idx_shared_tasks_current_holder
  ON shared_tasks ((current_claim->>'holderAgentId'))
  WHERE current_claim IS NOT NULL;

-- ─── Hardening (optional): per-agent token gate ────────────────────────
--
-- When populated, the pgvector adapter verifies that each WRITE / claim /
-- release / renewLease is accompanied by a token whose SHA-256 hash
-- matches the row registered for (team_id, agent_id). Read methods stay
-- unauthenticated (anyone with the conn string can read; only mutations
-- are agent-gated). Bootstrap via xmem_register_agent or directly via
-- INSERT INTO agent_identities (...).
--
-- The token_hash column is BYTEA storing the raw 32-byte SHA-256 digest.
-- Leaking the table reveals hashes only; brute-forcing a sufficiently
-- entropic 32-byte token is infeasible. For production deployments,
-- rotate tokens periodically and revoke departed members by DELETE.

CREATE TABLE IF NOT EXISTS agent_identities (
  team_id        TEXT        NOT NULL,
  agent_id       TEXT        NOT NULL,
  token_hash     BYTEA       NOT NULL,
  registered_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (team_id, agent_id)
);
