-- Prayer Items table for the Prayer List feature (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);
