BEGIN;

SET ROLE continuity_role;

CREATE TABLE IF NOT EXISTS continuity.attention_items (
  attention_item_id TEXT PRIMARY KEY,
  owner_principal_id TEXT NOT NULL,
  scope_id TEXT NOT NULL,
  source_kind TEXT NOT NULL,
  source_prospective_item_id TEXT NOT NULL,
  level TEXT NOT NULL,
  state TEXT NOT NULL,
  summary TEXT NOT NULL,
  due_at_unix_millis BIGINT NOT NULL,
  created_at_unix_millis BIGINT NOT NULL,
  delivered_at_unix_millis BIGINT NOT NULL DEFAULT 0,
  action_intent_id TEXT NOT NULL DEFAULT '',
  authorized_action_id TEXT NOT NULL DEFAULT '',
  action_attempt_id TEXT NOT NULL DEFAULT '',
  delivery_outcome TEXT NOT NULL DEFAULT '',
  revision BIGINT NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (source_kind, source_prospective_item_id),
  CHECK (source_kind IN ('PROSPECTIVE_REMINDER')),
  CHECK (level IN ('URGENT','ACTION_REQUIRED','DUE','IMPORTANT_CHANGE','FYI','BACKGROUND')),
  CHECK (state IN ('PENDING','DELIVERING','DELIVERED','SUPPRESSED','EXPIRED','FAILED')),
  CHECK (summary <> ''),
  CHECK (due_at_unix_millis > 0),
  CHECK (created_at_unix_millis > 0),
  CHECK (revision > 0),
  CHECK (state <> 'DELIVERED' OR (delivered_at_unix_millis > 0 AND action_intent_id <> '' AND authorized_action_id <> '' AND action_attempt_id <> '' AND delivery_outcome <> ''))
);

RESET ROLE;
COMMIT;