BEGIN;

SET ROLE continuity_role;

CREATE TABLE IF NOT EXISTS continuity.human_responses (
  human_response_id TEXT PRIMARY KEY,
  owner_principal_id TEXT NOT NULL,
  scope_id TEXT NOT NULL,
  source_utterance_id TEXT NOT NULL,
  source_intent_id TEXT NOT NULL,
  operation TEXT NOT NULL,
  target_kind TEXT NOT NULL,
  target_attention_id TEXT NOT NULL DEFAULT '',
  target_prospective_item_id TEXT NOT NULL DEFAULT '',
  target_external_ref TEXT NOT NULL DEFAULT '',
  resolution_state TEXT NOT NULL,
  outcome TEXT NOT NULL,
  clarification TEXT NOT NULL DEFAULT '',
  snooze_until_unix_millis BIGINT NOT NULL DEFAULT 0,
  user_reported_not_verified BOOLEAN NOT NULL DEFAULT FALSE,
  recorded_at_unix_millis BIGINT NOT NULL,
  revision BIGINT NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHECK (owner_principal_id <> ''),
  CHECK (scope_id <> ''),
  CHECK (source_utterance_id <> ''),
  CHECK (source_intent_id <> ''),
  CHECK (operation IN ('ACKNOWLEDGE','COMPLETE','SNOOZE','CANCEL','DISMISS','CONTINUE','REJECT','UNKNOWN')),
  CHECK (target_kind IN ('ATTENTION_ITEM','PROSPECTIVE_ITEM','WORK_TASK','CAPABILITY_GAP','INTERACTION_CONTEXT','UNKNOWN')),
  CHECK (resolution_state IN ('RESOLVED','AMBIGUOUS','NO_TARGET','NOOP')),
  CHECK (outcome IN ('USER_REPORTED_COMPLETE','SNOOZED','CANCELLED','DISMISSED','CLARIFICATION_NEEDED','NOOP')),
  CHECK (recorded_at_unix_millis > 0),
  CHECK (revision > 0),
  CHECK (operation <> 'SNOOZE' OR snooze_until_unix_millis > recorded_at_unix_millis),
  CHECK (resolution_state <> 'RESOLVED' OR target_kind <> 'UNKNOWN')
);

ALTER TABLE continuity.attention_items
  ADD COLUMN IF NOT EXISTS response_id TEXT NOT NULL DEFAULT '',
  ADD COLUMN IF NOT EXISTS response_utterance_id TEXT NOT NULL DEFAULT '',
  ADD COLUMN IF NOT EXISTS response_operation TEXT NOT NULL DEFAULT '',
  ADD COLUMN IF NOT EXISTS response_outcome TEXT NOT NULL DEFAULT '',
  ADD COLUMN IF NOT EXISTS responded_at_unix_millis BIGINT NOT NULL DEFAULT 0;

DO $$
DECLARE
  constraint_record RECORD;
BEGIN
  FOR constraint_record IN
    SELECT conname
    FROM pg_constraint
    WHERE conrelid = 'continuity.attention_items'::regclass
      AND contype = 'c'
      AND pg_get_constraintdef(oid) LIKE '%state%'
      AND pg_get_constraintdef(oid) LIKE '%DELIVERED%'
      AND pg_get_constraintdef(oid) LIKE '%FAILED%'
  LOOP
    EXECUTE format('ALTER TABLE continuity.attention_items DROP CONSTRAINT %I', constraint_record.conname);
  END LOOP;
END $$;

ALTER TABLE continuity.attention_items
  ADD CONSTRAINT attention_items_state_check
  CHECK (state IN ('PENDING','DELIVERING','DELIVERED','RESOLVED','SUPPRESSED','EXPIRED','FAILED'));

ALTER TABLE continuity.attention_items
  ADD CONSTRAINT attention_items_delivered_evidence_check
  CHECK (state <> 'DELIVERED' OR (delivered_at_unix_millis > 0 AND action_intent_id <> '' AND authorized_action_id <> '' AND action_attempt_id <> '' AND delivery_outcome <> ''));

ALTER TABLE continuity.attention_items
  ADD CONSTRAINT attention_items_response_projection_check
  CHECK (
    state <> 'RESOLVED'
    OR (
      response_id <> ''
      AND response_utterance_id <> ''
      AND response_operation IN ('ACKNOWLEDGE','COMPLETE','SNOOZE','CANCEL','DISMISS','CONTINUE','REJECT')
      AND response_outcome IN ('USER_REPORTED_COMPLETE','SNOOZED','CANCELLED','DISMISSED','NOOP')
      AND responded_at_unix_millis > 0
    )
  );

RESET ROLE;
COMMIT;