-- 002_study_sessions_and_journeys

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 journey_day_completions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_journey_id UUID NOT NULL REFERENCES user_journeys(id) ON DELETE CASCADE,
  day_number INT NOT NULL,
  study_session_id UUID REFERENCES study_sessions(id) ON DELETE SET NULL,
  practice_completed BOOLEAN DEFAULT false,
  reflection_summary TEXT,
  completed_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE (user_journey_id, day_number)
);

CREATE TABLE IF NOT EXISTS study_session_events (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  session_id UUID NOT NULL REFERENCES study_sessions(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  event_type TEXT NOT NULL CHECK (event_type IN (
    'created',
    'discussion_progressed',
    'phase_advanced',
    'completed',
    'stale_auto_completed',
    'auto_completed_for_new_session'
  )),
  from_phase TEXT CHECK (from_phase IS NULL OR from_phase IN ('prime', 'engage', 'discuss', 'land')),
  to_phase TEXT CHECK (to_phase IS NULL OR to_phase IN ('prime', 'engage', 'discuss', 'land')),
  metadata JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT now()
);

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 INDEX IF NOT EXISTS idx_study_session_events_session_created
  ON study_session_events(session_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_study_session_events_user_created
  ON study_session_events(user_id, created_at DESC);
