-- Podcast Helper Schema v1
-- Show-agnostic core. DTFH-specific data lives in tables, not code.

CREATE TABLE IF NOT EXISTS show (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT
);

CREATE TABLE IF NOT EXISTS episode (
  id TEXT PRIMARY KEY,
  show_id TEXT NOT NULL REFERENCES show(id),
  episode_number INTEGER,
  title TEXT NOT NULL,
  pub_date TEXT,
  duration_ms INTEGER,
  spotify_id TEXT,
  mg_linked INTEGER NOT NULL DEFAULT 0,
  mg_episode INTEGER,
  is_solo INTEGER NOT NULL DEFAULT 0,
  is_live INTEGER NOT NULL DEFAULT 0,
  is_comedy_forward INTEGER NOT NULL DEFAULT 0,
  depth REAL,
  intensity_5 REAL,
  humor REAL,
  accessibility REAL,
  rewatchability REAL,
  guest_fame REAL,
  gateway_potential INTEGER NOT NULL DEFAULT 0,
  gateway_reason TEXT,
  summary TEXT,
  emotional_register TEXT,
  key_concepts TEXT,       -- JSON array
  schema_version INTEGER DEFAULT 2
);

CREATE TABLE IF NOT EXISTS theme (
  id TEXT PRIMARY KEY,
  show_id TEXT NOT NULL REFERENCES show(id),
  name TEXT NOT NULL,
  description TEXT,
  sort_order INTEGER
);

CREATE TABLE IF NOT EXISTS episode_theme (
  episode_id TEXT NOT NULL REFERENCES episode(id),
  theme_id TEXT NOT NULL REFERENCES theme(id),
  strength REAL NOT NULL DEFAULT 1.0,
  is_primary INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (episode_id, theme_id)
);

CREATE TABLE IF NOT EXISTS episode_stage (
  episode_id TEXT NOT NULL REFERENCES episode(id),
  theme_id TEXT NOT NULL REFERENCES theme(id),
  stage TEXT NOT NULL CHECK(stage IN ('gateway','foundation','deepening','peak','aftercare')),
  stage_rank INTEGER,
  PRIMARY KEY (episode_id, theme_id)
);

CREATE TABLE IF NOT EXISTS guest (
  id TEXT PRIMARY KEY,
  canonical_name TEXT NOT NULL,
  aliases TEXT,           -- JSON array
  background TEXT,
  fame_score REAL
);

CREATE TABLE IF NOT EXISTS episode_guest (
  episode_id TEXT NOT NULL REFERENCES episode(id),
  guest_id TEXT NOT NULL REFERENCES guest(id),
  PRIMARY KEY (episode_id, guest_id)
);

-- Hard locks (blocks access until unlock condition met)
CREATE TABLE IF NOT EXISTS episode_lock (
  episode_id TEXT NOT NULL REFERENCES episode(id),
  lock_type TEXT NOT NULL,   -- 'mg_episode', 'multipart'
  lock_key TEXT NOT NULL,    -- e.g. 'mg:3', 'multipart:my_mom'
  reason TEXT,
  PRIMARY KEY (episode_id, lock_type, lock_key)
);

CREATE TABLE IF NOT EXISTS user_unlock (
  user_id TEXT NOT NULL DEFAULT 'mark',
  lock_key TEXT NOT NULL,
  unlocked_at TEXT NOT NULL,
  PRIMARY KEY (user_id, lock_key)
);

-- Soft episode-to-episode edges
CREATE TABLE IF NOT EXISTS episode_edge (
  prereq_episode_id TEXT NOT NULL REFERENCES episode(id),
  episode_id TEXT NOT NULL REFERENCES episode(id),
  edge_type TEXT NOT NULL,   -- 'guest_arc','concept_primer','emotional_readiness','aftercare','multipart'
  blocking INTEGER NOT NULL DEFAULT 0,
  weight REAL NOT NULL DEFAULT 0.5,
  confidence REAL NOT NULL DEFAULT 0.8,
  reason TEXT NOT NULL,
  source TEXT NOT NULL DEFAULT 'rule',
  PRIMARY KEY (prereq_episode_id, episode_id, edge_type)
);

-- Theme-stage cross-theme logic
CREATE TABLE IF NOT EXISTS theme_stage_edge (
  prereq_theme_id TEXT NOT NULL REFERENCES theme(id),
  prereq_stage TEXT NOT NULL,
  target_theme_id TEXT NOT NULL REFERENCES theme(id),
  target_stage TEXT NOT NULL,
  min_completed INTEGER NOT NULL DEFAULT 1,
  weight REAL NOT NULL DEFAULT 0.3,
  reason TEXT NOT NULL,
  PRIMARY KEY (prereq_theme_id, prereq_stage, target_theme_id, target_stage)
);

CREATE TABLE IF NOT EXISTS listen_history (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id TEXT NOT NULL DEFAULT 'mark',
  episode_id TEXT NOT NULL REFERENCES episode(id),
  listened_at TEXT,
  progress_pct REAL,
  fully_played INTEGER NOT NULL DEFAULT 0,
  source TEXT DEFAULT 'spotify',
  UNIQUE(user_id, episode_id)
);

CREATE TABLE IF NOT EXISTS feedback (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id TEXT NOT NULL DEFAULT 'mark',
  episode_id TEXT NOT NULL REFERENCES episode(id),
  rated_at TEXT NOT NULL,
  rating INTEGER CHECK(rating BETWEEN 1 AND 5),
  too_heavy INTEGER DEFAULT 0,
  loved_it INTEGER DEFAULT 0,
  more_like_this INTEGER DEFAULT 0,
  skip_reason TEXT,
  notes TEXT
);

CREATE TABLE IF NOT EXISTS recommendation_log (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id TEXT NOT NULL DEFAULT 'mark',
  recommended_at TEXT NOT NULL,
  episode_id TEXT NOT NULL REFERENCES episode(id),
  rank INTEGER NOT NULL,
  score REAL,
  score_breakdown TEXT,   -- JSON
  reason TEXT,
  was_played INTEGER DEFAULT 0,
  was_rated INTEGER DEFAULT 0
);

CREATE TABLE IF NOT EXISTS mg_progress (
  user_id TEXT NOT NULL DEFAULT 'mark',
  mg_episode INTEGER NOT NULL,
  watched_at TEXT NOT NULL,
  PRIMARY KEY (user_id, mg_episode)
);

CREATE INDEX IF NOT EXISTS idx_episode_show ON episode(show_id);
CREATE INDEX IF NOT EXISTS idx_listen_user ON listen_history(user_id, fully_played);
CREATE INDEX IF NOT EXISTS idx_episode_theme_primary ON episode_theme(episode_id, is_primary);
CREATE INDEX IF NOT EXISTS idx_episode_stage ON episode_stage(theme_id, stage, stage_rank);
CREATE INDEX IF NOT EXISTS idx_episode_gateway ON episode(gateway_potential, accessibility);
