BEGIN;

SET ROLE continuity_role;

ALTER TABLE continuity.attention_items
  ADD COLUMN IF NOT EXISTS source_external_ref TEXT NOT NULL DEFAULT '';

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint
    WHERE conname = 'attention_items_source_kind_check_v2'
  ) THEN
    ALTER TABLE continuity.attention_items DROP CONSTRAINT IF EXISTS attention_items_source_kind_check;
    ALTER TABLE continuity.attention_items
      ADD CONSTRAINT attention_items_source_kind_check_v2
      CHECK (source_kind IN ('PROSPECTIVE_REMINDER','AGENT_DELEGATION'));
  END IF;
END $$;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint
    WHERE conname = 'human_responses_outcome_check_v2'
  ) THEN
    ALTER TABLE continuity.human_responses DROP CONSTRAINT IF EXISTS human_responses_outcome_check;
    ALTER TABLE continuity.human_responses
      ADD CONSTRAINT human_responses_outcome_check_v2
      CHECK (outcome IN ('USER_REPORTED_COMPLETE','SNOOZED','CANCELLED','DISMISSED','CLARIFICATION_NEEDED','CONTINUED','REJECTED','NOOP'));
  END IF;
END $$;

CREATE UNIQUE INDEX IF NOT EXISTS attention_items_source_external_ref_unique
  ON continuity.attention_items(source_kind, source_external_ref)
  WHERE source_external_ref <> '';

RESET ROLE;

SET ROLE work_role;

CREATE TABLE IF NOT EXISTS work.delegation_verifications (
  verification_id TEXT PRIMARY KEY,
  delegation_id TEXT NOT NULL,
  run_id TEXT NOT NULL,
  result_id TEXT NOT NULL,
  verified BOOLEAN NOT NULL,
  summary TEXT NOT NULL,
  evidence_refs TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
  recorded_at_unix_millis BIGINT NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHECK (delegation_id <> ''),
  CHECK (run_id <> ''),
  CHECK (result_id <> ''),
  CHECK (summary <> ''),
  CHECK (recorded_at_unix_millis > 0)
);

RESET ROLE;
COMMIT;