BEGIN;

SET ROLE work_role;

CREATE TABLE IF NOT EXISTS work.delegations (
  delegation_id TEXT PRIMARY KEY,
  goal_id TEXT NOT NULL,
  work_plan_id TEXT NOT NULL,
  plan_candidate_id TEXT NOT NULL,
  capability_requirement_id TEXT NOT NULL,
  capability_kind TEXT NOT NULL,
  selected_implementation_id TEXT NOT NULL,
  selected_implementation_revision BIGINT NOT NULL,
  selected_implementation_kind TEXT NOT NULL,
  protocol TEXT NOT NULL,
  source_intent_id TEXT NOT NULL,
  source_utterance_id TEXT NOT NULL,
  conversation_id TEXT NOT NULL DEFAULT '',
  project_id TEXT NOT NULL DEFAULT '',
  project_display_name TEXT NOT NULL DEFAULT '',
  desired_outcome TEXT NOT NULL,
  constraint_summary TEXT NOT NULL DEFAULT '',
  verification_summary TEXT NOT NULL DEFAULT '',
  context_summary TEXT NOT NULL DEFAULT '',
  context_refs TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
  state TEXT NOT NULL,
  latest_run_id TEXT NOT NULL DEFAULT '',
  latest_result_id TEXT NOT NULL DEFAULT '',
  created_at_unix_millis BIGINT NOT NULL,
  updated_at_unix_millis BIGINT NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHECK (delegation_id <> ''),
  CHECK (goal_id <> ''),
  CHECK (work_plan_id <> ''),
  CHECK (plan_candidate_id <> ''),
  CHECK (capability_requirement_id <> ''),
  CHECK (capability_kind <> ''),
  CHECK (selected_implementation_id <> ''),
  CHECK (selected_implementation_revision > 0),
  CHECK (selected_implementation_kind = 'AGENT'),
  CHECK (protocol IN ('CODEX_BRIDGE_V1')),
  CHECK (source_intent_id <> ''),
  CHECK (source_utterance_id <> ''),
  CHECK (desired_outcome <> ''),
  CHECK (state IN ('PENDING','RUNNING','WAITING','BLOCKED','VERIFYING','COMPLETED','FAILED','CANCELLED')),
  CHECK (created_at_unix_millis > 0),
  CHECK (updated_at_unix_millis >= created_at_unix_millis)
);

CREATE TABLE IF NOT EXISTS work.agent_runs (
  run_id TEXT PRIMARY KEY,
  delegation_id TEXT NOT NULL,
  implementation_id TEXT NOT NULL,
  implementation_revision BIGINT NOT NULL,
  protocol TEXT NOT NULL,
  state TEXT NOT NULL,
  derived_instruction_summary TEXT NOT NULL DEFAULT '',
  started_at_unix_millis BIGINT NOT NULL,
  updated_at_unix_millis BIGINT NOT NULL,
  completed_at_unix_millis BIGINT NOT NULL DEFAULT 0,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHECK (run_id <> ''),
  CHECK (delegation_id <> ''),
  CHECK (implementation_id <> ''),
  CHECK (implementation_revision > 0),
  CHECK (protocol IN ('CODEX_BRIDGE_V1')),
  CHECK (state IN ('STARTED','RUNNING','WAITING','CHECKPOINTED','EXECUTION_WINDOW_EXHAUSTED','BLOCKED','FAILED','COMPLETED','CANCELLED')),
  CHECK (started_at_unix_millis > 0),
  CHECK (updated_at_unix_millis >= started_at_unix_millis),
  CHECK (completed_at_unix_millis >= 0)
);

CREATE TABLE IF NOT EXISTS work.agent_checkpoints (
  checkpoint_id TEXT PRIMARY KEY,
  delegation_id TEXT NOT NULL,
  run_id TEXT NOT NULL,
  state TEXT NOT NULL,
  blocker_class TEXT NOT NULL,
  completed_summary TEXT NOT NULL DEFAULT '',
  remaining_summary TEXT NOT NULL DEFAULT '',
  test_summary TEXT NOT NULL DEFAULT '',
  branch_name TEXT NOT NULL DEFAULT '',
  commit_hash TEXT NOT NULL DEFAULT '',
  next_step TEXT NOT NULL DEFAULT '',
  artifact_refs TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
  recorded_at_unix_millis BIGINT NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHECK (checkpoint_id <> ''),
  CHECK (delegation_id <> ''),
  CHECK (run_id <> ''),
  CHECK (state IN ('STARTED','RUNNING','WAITING','CHECKPOINTED','EXECUTION_WINDOW_EXHAUSTED','BLOCKED','FAILED','COMPLETED','CANCELLED')),
  CHECK (blocker_class IN ('NONE','HUMAN_DECISION_REQUIRED','MISSING_EXTERNAL_CREDENTIAL','ARCHITECTURE_DECISION_REQUIRED','SECURITY_CONFLICT','TECHNICAL_FAILURE','EXECUTION_WINDOW_EXHAUSTED')),
  CHECK (recorded_at_unix_millis > 0)
);

CREATE TABLE IF NOT EXISTS work.agent_results (
  result_id TEXT PRIMARY KEY,
  delegation_id TEXT NOT NULL,
  run_id TEXT NOT NULL,
  state TEXT NOT NULL,
  summary TEXT NOT NULL,
  outcome_ref TEXT NOT NULL DEFAULT '',
  verification_required BOOLEAN NOT NULL,
  verification_summary TEXT NOT NULL DEFAULT '',
  artifact_refs TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
  recorded_at_unix_millis BIGINT NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHECK (result_id <> ''),
  CHECK (delegation_id <> ''),
  CHECK (run_id <> ''),
  CHECK (state IN ('STARTED','RUNNING','WAITING','CHECKPOINTED','EXECUTION_WINDOW_EXHAUSTED','BLOCKED','FAILED','COMPLETED','CANCELLED')),
  CHECK (summary <> ''),
  CHECK (recorded_at_unix_millis > 0)
);

RESET ROLE;

SET ROLE capability_role;

INSERT INTO capability.capability_descriptors(capability_id, capability_kind, display_name, description, revision, recorded_at_unix_millis)
VALUES
  ('modify-code', 'MODIFY_CODE', 'Code modification', 'Executor-neutral software engineering capability that changes repository code inside an authorized scope.', 1, 1),
  ('run-tests', 'RUN_TESTS', 'Run tests', 'Executor-neutral software engineering capability that runs repository validation and test commands.', 1, 1),
  ('inspect-project', 'INSPECT_PROJECT', 'Inspect project', 'Executor-neutral software engineering capability that inspects repository structure and relevant files.', 1, 1)
ON CONFLICT (capability_id) DO NOTHING;

INSERT INTO capability.capability_implementations(
  implementation_id, capability_id, capability_kind, owner_principal_id, revision, implementation_kind, target_ref,
  credential_ref, qualification, activation, health, authority_policy_ref, data_constraints,
  cost_score_millis, latency_score_millis, reliability_millis, observed_at_unix_millis)
VALUES
  ('codex-agent-modify-code-v1', 'modify-code', 'MODIFY_CODE', 'domovoy-creator-principal', 1, 'AGENT', 'codex://assistant/modify-code', '', 'VALIDATED', 'CANARY', 'AVAILABLE', 'creator-directive-v1', ARRAY['LOCAL_ONLY','NO_CREDENTIAL_FORWARD'], 5, 80, 820, 1),
  ('codex-agent-run-tests-v1', 'run-tests', 'RUN_TESTS', 'domovoy-creator-principal', 1, 'AGENT', 'codex://assistant/run-tests', '', 'VALIDATED', 'CANARY', 'AVAILABLE', 'creator-directive-v1', ARRAY['LOCAL_ONLY','NO_CREDENTIAL_FORWARD'], 5, 120, 860, 1),
  ('codex-agent-inspect-project-v1', 'inspect-project', 'INSPECT_PROJECT', 'domovoy-creator-principal', 1, 'AGENT', 'codex://assistant/inspect-project', '', 'VALIDATED', 'CANARY', 'AVAILABLE', 'creator-directive-v1', ARRAY['LOCAL_ONLY','NO_CREDENTIAL_FORWARD'], 5, 40, 900, 1)
ON CONFLICT DO NOTHING;

INSERT INTO capability.capability_implementation_heads(implementation_id, revision, capability_kind, owner_principal_id)
VALUES
  ('codex-agent-modify-code-v1', 1, 'MODIFY_CODE', 'domovoy-creator-principal'),
  ('codex-agent-run-tests-v1', 1, 'RUN_TESTS', 'domovoy-creator-principal'),
  ('codex-agent-inspect-project-v1', 1, 'INSPECT_PROJECT', 'domovoy-creator-principal')
ON CONFLICT (implementation_id) DO NOTHING;

RESET ROLE;
COMMIT;