-- Listening engine v3 schema
-- Safe to run inside an existing student admin DB — all tym_ prefixed.

CREATE TABLE IF NOT EXISTS tym_sessions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  session_code VARCHAR(8) NOT NULL UNIQUE,
  lesson_slug VARCHAR(64) NOT NULL DEFAULT 'thank_you_maam',
  state VARCHAR(32) NOT NULL DEFAULT 'lobby',
  current_chapter INT NOT NULL DEFAULT 0,
  audio_command VARCHAR(16) NOT NULL DEFAULT 'idle',
  audio_command_at DOUBLE NOT NULL DEFAULT 0,
  audio_play_at DOUBLE NOT NULL DEFAULT 0,
  buzzed_student_id INT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX (lesson_slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS tym_students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  session_id INT NOT NULL,
  student_user_id INT NULL,
  is_guest TINYINT(1) NOT NULL DEFAULT 1,
  display_name VARCHAR(64) NOT NULL,
  client_token VARCHAR(64) NOT NULL,
  joined_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_seen DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX (session_id),
  INDEX (client_token),
  INDEX (student_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS tym_answers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  session_id INT NOT NULL,
  student_id INT NOT NULL,
  chapter INT NOT NULL,
  question_id VARCHAR(16) NOT NULL,
  answer_text TEXT NOT NULL,
  submitted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_answer (session_id, student_id, chapter, question_id),
  INDEX (session_id, chapter)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Focus/tab-switch events (student leaves the browser tab during a chapter)
CREATE TABLE IF NOT EXISTS tym_focus_events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  session_id INT NOT NULL,
  student_id INT NOT NULL,
  chapter INT NOT NULL DEFAULT 0,
  event_type VARCHAR(16) NOT NULL,   -- 'hidden' or 'visible'
  away_seconds FLOAT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX (session_id, student_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------------------------
-- Upgrading from v2 (single-lesson schema)?  Run these instead of the
-- CREATE statements above:
--
--   ALTER TABLE tym_sessions
--     ADD COLUMN lesson_slug VARCHAR(64) NOT NULL DEFAULT 'thank_you_maam' AFTER session_code,
--     ADD COLUMN audio_play_at DOUBLE NOT NULL DEFAULT 0 AFTER audio_command_at,
--     ADD INDEX (lesson_slug);
--
-- Then create tym_focus_events fresh (it's new in v3):
--   (run the CREATE TABLE IF NOT EXISTS tym_focus_events block above)
-- -----------------------------------------------------------------------