-- Zoe V1 schema
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE IF NOT EXISTS churches (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT NOT NULL,
  denomination TEXT,
  timezone TEXT DEFAULT 'America/New_York',
  plan_tier TEXT,
  statement_of_faith TEXT,
  theological_guardrails JSONB DEFAULT '{}'::jsonb,
  admin_user_id UUID,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  phone TEXT UNIQUE NOT NULL,
  name TEXT,
  timezone TEXT DEFAULT 'America/New_York',
  status TEXT DEFAULT 'active',
  church_id UUID REFERENCES churches(id),
  preferences JSONB DEFAULT '{}'::jsonb,
  onboarding_state JSONB DEFAULT '{}'::jsonb,
  trial_started_at TIMESTAMPTZ,
  subscription_status TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);

UPDATE users
SET subscription_status = 'trialing'
WHERE subscription_status = 'trial';

UPDATE users
SET subscription_status = 'expired_trial'
WHERE subscription_status = 'expired';

UPDATE users
SET subscription_status = 'past_due'
WHERE subscription_status IN ('unpaid', 'incomplete', 'incomplete_expired', 'paused');

CREATE TABLE IF NOT EXISTS church_memberships (
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  church_id UUID REFERENCES churches(id) ON DELETE CASCADE,
  role TEXT DEFAULT 'member',
  joined_at TIMESTAMPTZ DEFAULT now(),
  PRIMARY KEY (user_id, church_id)
);

CREATE TABLE IF NOT EXISTS conversations (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  started_at TIMESTAMPTZ DEFAULT now(),
  last_message_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS conversation_session_summaries (
  conversation_id UUID PRIMARY KEY REFERENCES conversations(id) ON DELETE CASCADE,
  summary_text TEXT NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_conversation_session_summaries_updated_at
  ON conversation_session_summaries(updated_at DESC);

CREATE TABLE IF NOT EXISTS messages (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  conversation_id UUID REFERENCES conversations(id) ON DELETE CASCADE,
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  direction TEXT NOT NULL CHECK (direction IN ('in','out')),
  body TEXT NOT NULL,
  provider TEXT DEFAULT 'twilio',
  twilio_sid TEXT,
  provider_message_id TEXT UNIQUE,
  sent_at TIMESTAMPTZ,
  cost_cents INT,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS memory_items (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  type TEXT NOT NULL,
  content TEXT NOT NULL,
  confidence REAL DEFAULT 0.5,
  source_message_id UUID REFERENCES messages(id),
  tags JSONB DEFAULT '{}'::jsonb,
  embedding vector(768),
  embedding_model TEXT,
  embedding_dimensions INT,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS memory_events (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  operation TEXT NOT NULL,
  success BOOLEAN NOT NULL DEFAULT true,
  error TEXT,
  metadata JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_memory_events_user_created ON memory_events(user_id, created_at DESC);

CREATE TABLE IF NOT EXISTS sermon_series (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  church_id UUID REFERENCES churches(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  start_date DATE,
  end_date DATE,
  theme TEXT
);

CREATE TABLE IF NOT EXISTS sermon_assets (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  series_id UUID REFERENCES sermon_series(id) ON DELETE CASCADE,
  type TEXT NOT NULL,
  content TEXT NOT NULL,
  embedding vector(768),
  embedding_model TEXT,
  embedding_dimensions INT,
  metadata JSONB DEFAULT '{}'::jsonb
);

CREATE TABLE IF NOT EXISTS subscriptions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES users(id),
  church_id UUID REFERENCES churches(id),
  stripe_customer_id TEXT,
  stripe_subscription_id TEXT UNIQUE,
  status TEXT,
  plan TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS escalations (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  church_id UUID REFERENCES churches(id) ON DELETE CASCADE,
  type TEXT NOT NULL,
  status TEXT DEFAULT 'open',
  severity TEXT DEFAULT 'high',
  trigger_message_id UUID REFERENCES messages(id) ON DELETE SET NULL,
  escalated_to JSONB DEFAULT '[]'::jsonb,
  escalated_at TIMESTAMPTZ,
  resolved_at TIMESTAMPTZ,
  resolution_notes TEXT,
  metadata JSONB DEFAULT '{}'::jsonb,
  notes TEXT,
  updated_at TIMESTAMPTZ DEFAULT now(),
  created_at TIMESTAMPTZ DEFAULT now()
);

ALTER TABLE escalations ADD COLUMN IF NOT EXISTS severity TEXT DEFAULT 'high';
ALTER TABLE escalations ADD COLUMN IF NOT EXISTS trigger_message_id UUID REFERENCES messages(id) ON DELETE SET NULL;
ALTER TABLE escalations ADD COLUMN IF NOT EXISTS escalated_to JSONB DEFAULT '[]'::jsonb;
ALTER TABLE escalations ADD COLUMN IF NOT EXISTS escalated_at TIMESTAMPTZ;
ALTER TABLE escalations ADD COLUMN IF NOT EXISTS resolved_at TIMESTAMPTZ;
ALTER TABLE escalations ADD COLUMN IF NOT EXISTS resolution_notes TEXT;
ALTER TABLE escalations ADD COLUMN IF NOT EXISTS metadata JSONB DEFAULT '{}'::jsonb;
ALTER TABLE escalations ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ DEFAULT now();

-- Church admin + dashboard support
ALTER TABLE churches ADD COLUMN IF NOT EXISTS slug TEXT UNIQUE;
ALTER TABLE churches ADD COLUMN IF NOT EXISTS theological_config JSONB DEFAULT '{}'::jsonb;
ALTER TABLE churches ADD COLUMN IF NOT EXISTS escalation_policy JSONB DEFAULT '{}'::jsonb;
ALTER TABLE churches ADD COLUMN IF NOT EXISTS welcome_message TEXT;
ALTER TABLE churches ADD COLUMN IF NOT EXISTS stripe_customer_id TEXT;
ALTER TABLE churches ADD COLUMN IF NOT EXISTS stripe_subscription_id TEXT;
ALTER TABLE churches ADD COLUMN IF NOT EXISTS active_member_limit INT;
ALTER TABLE churches ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ DEFAULT now();
ALTER TABLE churches ADD COLUMN IF NOT EXISTS onboarding_state JSONB DEFAULT '{}'::jsonb;

ALTER TABLE users ADD COLUMN IF NOT EXISTS last_responded_at TIMESTAMPTZ;
ALTER TABLE users ADD COLUMN IF NOT EXISTS dormant_since TIMESTAMPTZ;
ALTER TABLE users ADD COLUMN IF NOT EXISTS tone_preference TEXT;
ALTER TABLE users ADD COLUMN IF NOT EXISTS cadence TEXT;
ALTER TABLE users ADD COLUMN IF NOT EXISTS morning_time TIME;
ALTER TABLE users ADD COLUMN IF NOT EXISTS evening_time TIME;
ALTER TABLE users ADD COLUMN IF NOT EXISTS last_active_at TIMESTAMPTZ;
ALTER TABLE users ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ DEFAULT now();

ALTER TABLE sermon_series ADD COLUMN IF NOT EXISTS created_at TIMESTAMPTZ DEFAULT now();
ALTER TABLE sermon_assets ADD COLUMN IF NOT EXISTS created_at TIMESTAMPTZ DEFAULT now();
ALTER TABLE sermon_assets ADD COLUMN IF NOT EXISTS metadata JSONB DEFAULT '{}'::jsonb;
ALTER TABLE sermon_assets ADD COLUMN IF NOT EXISTS embedding_model TEXT;
ALTER TABLE sermon_assets ADD COLUMN IF NOT EXISTS embedding_dimensions INT;

CREATE TABLE IF NOT EXISTS church_content (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  church_id UUID REFERENCES churches(id) ON DELETE CASCADE,
  type TEXT NOT NULL,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  series_name TEXT,
  active BOOLEAN DEFAULT true,
  uploaded_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS planned_messages (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  type TEXT NOT NULL,
  scheduled_for TIMESTAMPTZ NOT NULL,
  content TEXT,
  metadata JSONB DEFAULT '{}'::jsonb,
  status TEXT NOT NULL DEFAULT 'pending',
  sent_at TIMESTAMPTZ,
  twilio_sid TEXT
);

CREATE TABLE IF NOT EXISTS commitments (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  commitment_type TEXT NOT NULL CHECK (commitment_type IN ('check_in', 'study_followup', 'prayer', 'custom')),
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'paused', 'completed', 'canceled', 'failed')),
  source TEXT NOT NULL DEFAULT 'ai' CHECK (source IN ('ai', 'user_command', 'system')),
  summary_text TEXT NOT NULL,
  intent_raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  schedule_kind TEXT NOT NULL CHECK (schedule_kind IN ('one_off', 'recurring')),
  timezone TEXT NOT NULL DEFAULT 'America/New_York',
  scheduled_at_utc TIMESTAMPTZ,
  local_time TIME,
  recurrence_rule TEXT,
  next_run_at_utc TIMESTAMPTZ NOT NULL,
  last_run_at_utc TIMESTAMPTZ,
  run_count INT NOT NULL DEFAULT 0,
  idempotency_key TEXT NOT NULL,
  scheduler_job_id TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (
    (schedule_kind = 'one_off' AND scheduled_at_utc IS NOT NULL) OR
    (schedule_kind = 'recurring' AND local_time IS NOT NULL)
  )
);

CREATE TABLE IF NOT EXISTS commitment_events (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  commitment_id UUID NOT NULL REFERENCES commitments(id) ON DELETE CASCADE,
  event_type TEXT NOT NULL CHECK (event_type IN (
    'created',
    'scheduled',
    'triggered',
    'sent',
    'canceled',
    'tz_updated',
    'rescheduled',
    'failed'
  )),
  payload JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE planned_messages ADD COLUMN IF NOT EXISTS metadata JSONB DEFAULT '{}'::jsonb;
ALTER TABLE planned_messages ADD COLUMN IF NOT EXISTS dispatch_key TEXT;
ALTER TABLE planned_messages ADD COLUMN IF NOT EXISTS processing_started_at TIMESTAMPTZ;
ALTER TABLE planned_messages ADD COLUMN IF NOT EXISTS processing_worker_id TEXT;

CREATE TABLE IF NOT EXISTS dead_letter_jobs (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  queue_name TEXT NOT NULL,
  job_name TEXT NOT NULL,
  job_id TEXT NOT NULL,
  payload JSONB DEFAULT '{}'::jsonb,
  error_message TEXT NOT NULL,
  retry_count INT NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS system_alert_events (
  alert_key TEXT PRIMARY KEY,
  last_sent_at TIMESTAMPTZ NOT NULL,
  metadata JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS reading_plans (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  book TEXT NOT NULL,
  total_sections INT NOT NULL CHECK (total_sections > 0),
  current_section INT NOT NULL DEFAULT 0 CHECK (current_section >= 0),
  pace TEXT NOT NULL DEFAULT 'normal' CHECK (pace IN ('light', 'normal', 'intensive')),
  delivery_time TIME,
  timezone TEXT,
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'paused', 'completed')),
  started_at TIMESTAMPTZ DEFAULT now(),
  completed_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS reading_plan_sections (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  plan_id UUID NOT NULL REFERENCES reading_plans(id) ON DELETE CASCADE,
  section_number INT NOT NULL CHECK (section_number > 0),
  passage_ref TEXT NOT NULL,
  context_notes TEXT,
  delivered_at TIMESTAMPTZ,
  user_response TEXT,
  created_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE (plan_id, section_number)
);

CREATE TABLE IF NOT EXISTS study_proposals (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  kind TEXT NOT NULL CHECK (kind IN ('book', 'topical')),
  title TEXT NOT NULL,
  payload JSONB NOT NULL DEFAULT '{}'::jsonb,
  total_days INT NOT NULL CHECK (total_days > 0),
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'activated', 'expired', 'dismissed')),
  activated_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS active_studies (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  proposal_id UUID REFERENCES study_proposals(id) ON DELETE SET NULL,
  kind TEXT NOT NULL CHECK (kind IN ('book', 'topical')),
  title TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'paused', 'completed', 'closed')),
  timezone TEXT,
  delivery_time TIME,
  is_primary_morning BOOLEAN NOT NULL DEFAULT true,
  current_day INT NOT NULL DEFAULT 1,
  closed_at TIMESTAMPTZ,
  close_reason TEXT,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS study_schedule_items (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  active_study_id UUID NOT NULL REFERENCES active_studies(id) ON DELETE CASCADE,
  day_number INT NOT NULL CHECK (day_number > 0),
  scheduled_for DATE NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'sent', 'skipped')),
  sent_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE (active_study_id, day_number)
);

CREATE TABLE IF NOT EXISTS study_generated_content (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  active_study_id UUID NOT NULL REFERENCES active_studies(id) ON DELETE CASCADE,
  day_number INT NOT NULL CHECK (day_number > 0),
  content TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'ready' CHECK (status IN ('ready', 'sent', 'superseded')),
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE (active_study_id, day_number)
);

CREATE TABLE IF NOT EXISTS church_analytics_daily (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  church_id UUID REFERENCES churches(id) ON DELETE CASCADE,
  date DATE NOT NULL,
  active_members INT DEFAULT 0,
  messages_sent INT DEFAULT 0,
  messages_received INT DEFAULT 0,
  prayer_topics JSONB DEFAULT '{}'::jsonb,
  sentiment_avg FLOAT,
  nlp_insights JSONB DEFAULT '{}'::jsonb,
  practice_adoption JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE (church_id, date)
);

ALTER TABLE church_analytics_daily ADD COLUMN IF NOT EXISTS nlp_insights JSONB DEFAULT '{}'::jsonb;

CREATE TABLE IF NOT EXISTS church_monthly_usage (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  church_id UUID REFERENCES churches(id) ON DELETE CASCADE,
  month_start DATE NOT NULL,
  active_members INT NOT NULL DEFAULT 0,
  member_limit INT NOT NULL DEFAULT 0,
  overage_members INT NOT NULL DEFAULT 0,
  overage_rate NUMERIC(10,2) NOT NULL DEFAULT 0,
  estimated_overage_cents INT NOT NULL DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'open',
  stripe_invoice_item_id TEXT,
  charged_at TIMESTAMPTZ,
  last_synced_at TIMESTAMPTZ DEFAULT now(),
  created_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE (church_id, month_start)
);

-- Privacy + trust controls
ALTER TABLE messages ADD COLUMN IF NOT EXISTS retention_expires_at TIMESTAMPTZ;
ALTER TABLE messages ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ;
ALTER TABLE messages ADD COLUMN IF NOT EXISTS user_id UUID REFERENCES users(id) ON DELETE CASCADE;
ALTER TABLE messages ADD COLUMN IF NOT EXISTS twilio_sid TEXT;
ALTER TABLE messages ADD COLUMN IF NOT EXISTS sent_at TIMESTAMPTZ;
ALTER TABLE messages ADD COLUMN IF NOT EXISTS cost_cents INT;
ALTER TABLE memory_items ADD COLUMN IF NOT EXISTS expires_at TIMESTAMPTZ;
ALTER TABLE memory_items ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ;
ALTER TABLE memory_items ADD COLUMN IF NOT EXISTS embedding_model TEXT;
ALTER TABLE memory_items ADD COLUMN IF NOT EXISTS embedding_dimensions INT;

UPDATE messages m
SET user_id = c.user_id
FROM conversations c
WHERE m.user_id IS NULL
  AND c.id = m.conversation_id;

UPDATE messages
SET twilio_sid = provider_message_id
WHERE twilio_sid IS NULL
  AND provider = 'twilio'
  AND provider_message_id IS NOT NULL;

UPDATE messages
SET sent_at = COALESCE(created_at, now())
WHERE sent_at IS NULL;

UPDATE memory_items
SET embedding_model = COALESCE(embedding_model, 'gemini-embedding-001'),
    embedding_dimensions = COALESCE(embedding_dimensions, 768)
WHERE embedding IS NOT NULL
  AND (embedding_model IS NULL OR embedding_dimensions IS NULL);

UPDATE sermon_assets
SET embedding_model = COALESCE(embedding_model, 'gemini-embedding-001'),
    embedding_dimensions = COALESCE(embedding_dimensions, 768)
WHERE embedding IS NOT NULL
  AND (embedding_model IS NULL OR embedding_dimensions IS NULL);

CREATE TABLE IF NOT EXISTS privacy_settings (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID UNIQUE NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  memory_mode TEXT NOT NULL DEFAULT 'standard' CHECK (memory_mode IN ('off', 'standard', 'deep')),
  ai_improvement_opt_in BOOLEAN NOT NULL DEFAULT false,
  allow_human_support_access BOOLEAN NOT NULL DEFAULT false,
  human_support_access_expires_at TIMESTAMPTZ,
  church_sharing_mode TEXT NOT NULL DEFAULT 'aggregated_only' CHECK (church_sharing_mode IN ('aggregated_only', 'explicit_share')),
  retention_profile TEXT NOT NULL DEFAULT 'standard' CHECK (retention_profile IN ('short', 'standard', 'extended', 'custom')),
  raw_message_retention_days INT NOT NULL DEFAULT 365 CHECK (raw_message_retention_days BETWEEN 1 AND 3650),
  memory_retention_days INT NOT NULL DEFAULT 730 CHECK (memory_retention_days BETWEEN 1 AND 3650),
  max_privacy_mode BOOLEAN NOT NULL DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS consent_events (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  actor_type TEXT NOT NULL DEFAULT 'user' CHECK (actor_type IN ('user', 'admin', 'system')),
  actor_id TEXT,
  consent_key TEXT NOT NULL,
  consent_status TEXT NOT NULL CHECK (consent_status IN ('granted', 'revoked', 'denied', 'expired')),
  source TEXT NOT NULL DEFAULT 'app',
  scope JSONB DEFAULT '{}'::jsonb,
  legal_text_version TEXT,
  expires_at TIMESTAMPTZ,
  request_id TEXT,
  ip_address INET,
  user_agent TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS access_audit_logs (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  occurred_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  actor_type TEXT NOT NULL CHECK (actor_type IN ('admin', 'support', 'system')),
  actor_id TEXT,
  action TEXT NOT NULL,
  target_type TEXT NOT NULL,
  target_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
  target_id TEXT,
  reason TEXT,
  access_result TEXT NOT NULL DEFAULT 'allowed' CHECK (access_result IN ('allowed', 'denied', 'error')),
  request_id TEXT,
  metadata JSONB DEFAULT '{}'::jsonb,
  ip_address INET,
  user_agent TEXT
);

-- Scripture study sessions + spiritual journeys (Phase A)
CREATE TABLE IF NOT EXISTS journey_templates (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  slug TEXT UNIQUE NOT NULL,
  title TEXT NOT NULL,
  description TEXT NOT NULL,
  duration_days INT NOT NULL,
  difficulty TEXT NOT NULL DEFAULT 'introductory',
  tags JSONB DEFAULT '[]'::jsonb,
  category TEXT NOT NULL,
  cover_image_url TEXT,
  is_published BOOLEAN DEFAULT false,
  church_id UUID REFERENCES churches(id) ON DELETE SET NULL,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS journey_template_days (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  template_id UUID NOT NULL REFERENCES journey_templates(id) ON DELETE CASCADE,
  day_number INT NOT NULL,
  title TEXT,
  passage_ref TEXT,
  practice_type TEXT NOT NULL,
  practice_instructions TEXT NOT NULL,
  practice_duration_minutes INT,
  discussion_seeds JSONB DEFAULT '[]'::jsonb,
  context_notes TEXT,
  connection_to_previous TEXT,
  created_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE (template_id, day_number)
);

CREATE TABLE IF NOT EXISTS user_journeys (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  template_id UUID NOT NULL REFERENCES journey_templates(id) ON DELETE CASCADE,
  current_day INT NOT NULL DEFAULT 1,
  status TEXT NOT NULL DEFAULT 'active',
  started_at TIMESTAMPTZ DEFAULT now(),
  paused_at TIMESTAMPTZ,
  completed_at TIMESTAMPTZ,
  delivery_time TIME,
  timezone TEXT,
  streak_current INT DEFAULT 0,
  streak_longest INT DEFAULT 0,
  notes JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT now(),
  CHECK (status IN ('active', 'paused', 'completed', 'abandoned'))
);

CREATE TABLE IF NOT EXISTS study_sessions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  passage_ref TEXT NOT NULL,
  phase TEXT NOT NULL DEFAULT 'prime',
  discussion_index INT NOT NULL DEFAULT 0,
  prime_context TEXT,
  user_observation TEXT,
  discussion_notes JSONB DEFAULT '[]'::jsonb,
  commitment TEXT,
  journey_id UUID REFERENCES user_journeys(id) ON DELETE SET NULL,
  journey_day_id UUID REFERENCES journey_template_days(id) ON DELETE SET NULL,
  started_at TIMESTAMPTZ DEFAULT now(),
  completed_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT now(),
  CHECK (phase IN ('prime', 'engage', 'discuss', 'land')),
  CHECK (discussion_index BETWEEN 0 AND 3)
);

CREATE TABLE IF NOT EXISTS conversation_states (
  user_id UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  conversation_id UUID REFERENCES conversations(id) ON DELETE SET NULL,
  pending_intent_type TEXT,
  pending_intent_payload JSONB NOT NULL DEFAULT '{}'::jsonb,
  pending_action_source TEXT,
  last_question_asked TEXT,
  clarification_state JSONB NOT NULL DEFAULT '{}'::jsonb,
  active_topic TEXT,
  active_thread TEXT,
  expires_at TIMESTAMPTZ,
  cleared_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_conversation_states_conversation
  ON conversation_states(conversation_id)
  WHERE conversation_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_conversation_states_expires
  ON conversation_states(expires_at)
  WHERE expires_at IS NOT NULL AND cleared_at IS NULL;

CREATE TABLE IF NOT EXISTS intent_events (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  conversation_id UUID REFERENCES conversations(id) ON DELETE SET NULL,
  message_id UUID REFERENCES messages(id) ON DELETE SET NULL,
  event_type TEXT NOT NULL CHECK (event_type IN (
    'intent_detected',
    'intent_unknown',
    'clarification_asked',
    'clarification_resolved',
    'fallback_to_chat'
  )),
  router_tier TEXT CHECK (router_tier IS NULL OR router_tier IN ('exact', 'deterministic', 'llm', 'fallback')),
  intent_type TEXT,
  intent_source TEXT,
  confidence REAL,
  fallback_path TEXT,
  metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS unknown_utterances (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  intent_event_id UUID REFERENCES intent_events(id) ON DELETE SET NULL,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  conversation_id UUID REFERENCES conversations(id) ON DELETE SET NULL,
  message_id UUID REFERENCES messages(id) ON DELETE SET NULL,
  utterance_text TEXT NOT NULL,
  utterance_hash TEXT NOT NULL,
  token_count INT NOT NULL DEFAULT 0,
  fallback_reason TEXT,
  nearest_intents JSONB NOT NULL DEFAULT '[]'::jsonb,
  expires_at TIMESTAMPTZ NOT NULL DEFAULT (now() + interval '30 days'),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_intent_events_created ON intent_events(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_intent_events_event_type_created ON intent_events(event_type, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_intent_events_user_created ON intent_events(user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_intent_events_tier_created ON intent_events(router_tier, created_at DESC)
  WHERE router_tier IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_unknown_utterances_created ON unknown_utterances(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_unknown_utterances_hash_created ON unknown_utterances(utterance_hash, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_unknown_utterances_expires ON unknown_utterances(expires_at);

CREATE TABLE IF NOT EXISTS feature_discovery_events (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  conversation_id UUID REFERENCES conversations(id) ON DELETE SET NULL,
  message_id UUID REFERENCES messages(id) ON DELETE SET NULL,
  event_type TEXT NOT NULL CHECK (event_type IN ('suggested', 'accepted', 'dismissed')),
  feature_type TEXT NOT NULL CHECK (feature_type IN ('journey', 'reading_plan', 'reminder')),
  feature_key TEXT NOT NULL,
  score REAL,
  metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_feature_discovery_events_user_created
  ON feature_discovery_events(user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_feature_discovery_events_feature_created
  ON feature_discovery_events(feature_key, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_feature_discovery_events_event_created
  ON feature_discovery_events(event_type, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_messages_conversation ON messages(conversation_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_messages_user_sent ON messages(user_id, sent_at DESC);
CREATE INDEX IF NOT EXISTS idx_memory_user ON memory_items(user_id);
CREATE INDEX IF NOT EXISTS idx_memory_embedding ON memory_items USING ivfflat (embedding vector_cosine_ops);
CREATE INDEX IF NOT EXISTS idx_sermon_assets_embedding ON sermon_assets USING ivfflat (embedding vector_cosine_ops);
CREATE INDEX IF NOT EXISTS idx_users_church ON users(church_id);
CREATE INDEX IF NOT EXISTS idx_messages_created_at ON messages(created_at);
CREATE INDEX IF NOT EXISTS idx_planned_messages_user ON planned_messages(user_id, scheduled_for);
CREATE UNIQUE INDEX IF NOT EXISTS idx_planned_messages_dispatch_key
  ON planned_messages(dispatch_key)
  WHERE dispatch_key IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_planned_messages_followups_due ON planned_messages(type, status, scheduled_for);
-- Migration note: scheduler status lookups rely on (user_id, status, scheduled_for/scheduled_at) for pending follow-ups.
CREATE INDEX IF NOT EXISTS idx_planned_messages_followups_user_status_scheduled
  ON planned_messages(user_id, status, scheduled_for)
  WHERE type = 'follow_up';
CREATE INDEX IF NOT EXISTS idx_commitments_user ON commitments(user_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_commitments_user_idempotency_active
  ON commitments(user_id, idempotency_key)
  WHERE status IN ('active', 'paused');
CREATE INDEX IF NOT EXISTS idx_commitments_user_status_next_run
  ON commitments(user_id, status, next_run_at_utc);
CREATE INDEX IF NOT EXISTS idx_commitments_next_run ON commitments(next_run_at_utc);
CREATE INDEX IF NOT EXISTS idx_commitment_events_commitment_created
  ON commitment_events(commitment_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_reading_plans_user_status ON reading_plans(user_id, status, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_reading_plan_sections_plan_num ON reading_plan_sections(plan_id, section_number);
CREATE INDEX IF NOT EXISTS idx_reading_plan_sections_delivered ON reading_plan_sections(delivered_at);
CREATE INDEX IF NOT EXISTS idx_study_proposals_user_status ON study_proposals(user_id, status, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_active_studies_user_status ON active_studies(user_id, status, created_at DESC);
CREATE UNIQUE INDEX IF NOT EXISTS idx_active_studies_one_primary_morning
  ON active_studies(user_id)
  WHERE status = 'active' AND is_primary_morning = true;
CREATE INDEX IF NOT EXISTS idx_study_schedule_due
  ON study_schedule_items(active_study_id, status, scheduled_for, day_number);
CREATE INDEX IF NOT EXISTS idx_study_generated_content_lookup
  ON study_generated_content(active_study_id, day_number);
CREATE INDEX IF NOT EXISTS idx_church_analytics_daily ON church_analytics_daily(church_id, date DESC);
CREATE INDEX IF NOT EXISTS idx_church_monthly_usage_church ON church_monthly_usage(church_id, month_start DESC);
CREATE INDEX IF NOT EXISTS idx_escalations_church_status ON escalations(church_id, status, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_escalations_user_status ON escalations(user_id, status, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_messages_retention_expires ON messages(retention_expires_at);
CREATE INDEX IF NOT EXISTS idx_memory_items_expires ON memory_items(expires_at);
CREATE INDEX IF NOT EXISTS idx_dead_letter_jobs_queue_created ON dead_letter_jobs(queue_name, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_consent_events_user_created ON consent_events(user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_consent_events_key_status ON consent_events(consent_key, consent_status, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_access_audit_logs_target_user ON access_audit_logs(target_user_id, occurred_at DESC);
CREATE INDEX IF NOT EXISTS idx_access_audit_logs_actor ON access_audit_logs(actor_type, actor_id, occurred_at DESC);
CREATE INDEX IF NOT EXISTS idx_study_sessions_user_active
  ON study_sessions(user_id, phase)
  WHERE completed_at IS NULL;
CREATE UNIQUE INDEX IF NOT EXISTS idx_study_sessions_one_active_per_user
  ON study_sessions(user_id)
  WHERE completed_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_user_journeys_active
  ON user_journeys(user_id, status)
  WHERE status = 'active';
CREATE UNIQUE INDEX IF NOT EXISTS idx_user_journeys_single_active
  ON user_journeys(user_id)
  WHERE status = 'active';

-- Prayer Items (Phase 1)
CREATE TABLE IF NOT EXISTS prayer_items (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  content TEXT NOT NULL,
  category TEXT CHECK (category IN ('person', 'situation', 'gratitude', 'self')),
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'answered', 'archived')),
  source TEXT NOT NULL DEFAULT 'conversation' CHECK (source IN ('onboarding', 'conversation', 'prompted')),
  last_nudged_at TIMESTAMPTZ,
  nudge_count INT NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  answered_at TIMESTAMPTZ,
  archived_at TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_prayer_items_user_active
  ON prayer_items(user_id) WHERE status = 'active';

CREATE INDEX IF NOT EXISTS idx_prayer_items_user_status
  ON prayer_items(user_id, status);
