BEGIN;

SET ROLE work_role;

CREATE TABLE IF NOT EXISTS work.goals (
  goal_id TEXT PRIMARY KEY,
  source_intent_id TEXT NOT NULL,
  source_utterance_id TEXT NOT NULL,
  actor_id TEXT NOT NULL,
  principal_id TEXT NOT NULL,
  scope_id TEXT NOT NULL,
  project_id TEXT NOT NULL DEFAULT '',
  project_display_name TEXT NOT NULL DEFAULT '',
  desired_outcome TEXT NOT NULL,
  constraints_text TEXT NOT NULL,
  state TEXT NOT NULL,
  provenance TEXT NOT NULL DEFAULT '',
  revision BIGINT NOT NULL,
  created_at_unix_millis BIGINT NOT NULL,
  updated_at_unix_millis BIGINT NOT NULL,
  CHECK (desired_outcome <> ''),
  CHECK (state IN ('PROPOSED','UNDERSTOOD','PLANNING','READY','IN_PROGRESS','BLOCKED','COMPLETED','FAILED','CANCELLED','SUPERSEDED')),
  CHECK (revision > 0)
);

CREATE TABLE IF NOT EXISTS work.plan_candidates (
  plan_candidate_id TEXT PRIMARY KEY,
  goal_id TEXT NOT NULL,
  source TEXT NOT NULL,
  state TEXT NOT NULL,
  summary TEXT NOT NULL,
  validation_summary TEXT NOT NULL,
  requires_clarification BOOLEAN NOT NULL,
  accepted_at_unix_millis BIGINT NOT NULL DEFAULT 0,
  recorded_at_unix_millis BIGINT NOT NULL,
  CHECK (goal_id <> ''),
  CHECK (source <> ''),
  CHECK (summary <> ''),
  CHECK (state IN ('PROPOSED','VALIDATED','ACCEPTED','REJECTED'))
);

CREATE TABLE IF NOT EXISTS work.work_plans (
  work_plan_id TEXT PRIMARY KEY,
  goal_id TEXT NOT NULL,
  accepted_candidate_id TEXT NOT NULL,
  revision BIGINT NOT NULL,
  state TEXT NOT NULL,
  summary TEXT NOT NULL,
  recorded_at_unix_millis BIGINT NOT NULL,
  CHECK (goal_id <> ''),
  CHECK (accepted_candidate_id <> ''),
  CHECK (revision > 0),
  CHECK (state IN ('PROPOSED','VALIDATED','ACCEPTED','REJECTED'))
);

CREATE TABLE IF NOT EXISTS work.capability_requirements (
  capability_requirement_id TEXT PRIMARY KEY,
  plan_candidate_id TEXT NOT NULL,
  capability_kind TEXT NOT NULL,
  capability_id TEXT NOT NULL DEFAULT '',
  purpose TEXT NOT NULL,
  state TEXT NOT NULL,
  epistemic_precondition TEXT NOT NULL,
  recorded_at_unix_millis BIGINT NOT NULL,
  CHECK (plan_candidate_id <> ''),
  CHECK (capability_kind <> ''),
  CHECK (purpose <> ''),
  CHECK (state IN ('REQUIRED','AVAILABLE','MISSING','BLOCKED')),
  CHECK (epistemic_precondition IN ('UNVERIFIED','VERIFIED','REJECTED','UNKNOWN','NOT_APPLICABLE'))
);

CREATE TABLE IF NOT EXISTS work.plan_steps (
  plan_step_id TEXT PRIMARY KEY,
  plan_candidate_id TEXT NOT NULL,
  ordinal INTEGER NOT NULL,
  kind TEXT NOT NULL,
  description TEXT NOT NULL,
  requires_capability_id TEXT NOT NULL DEFAULT '',
  recorded_at_unix_millis BIGINT NOT NULL,
  CHECK (plan_candidate_id <> ''),
  CHECK (ordinal > 0),
  CHECK (kind IN ('INVESTIGATE','OBSERVE','VERIFY','REASON','REPORT','PREPARE','COMMUNICATE','SCHEDULE','MODIFY','RUN_CHECKS','CLARIFY','NO_EXECUTION')),
  CHECK (description <> '')
);

CREATE TABLE IF NOT EXISTS work.delegation_candidates (
  delegation_candidate_id TEXT PRIMARY KEY,
  plan_candidate_id TEXT NOT NULL,
  capability_requirement_id TEXT NOT NULL,
  kind TEXT NOT NULL,
  executor_hint TEXT NOT NULL DEFAULT '',
  state TEXT NOT NULL,
  reason TEXT NOT NULL,
  recorded_at_unix_millis BIGINT NOT NULL,
  CHECK (plan_candidate_id <> ''),
  CHECK (capability_requirement_id <> ''),
  CHECK (kind IN ('AGENT','TOOL','ADAPTER','HUMAN','UNAVAILABLE')),
  CHECK (state IN ('PROPOSED','SELECTED','BLOCKED')),
  CHECK (reason <> '')
);

RESET ROLE;
COMMIT;