-- Thank You Ma'am Listening Exercise schema
-- Run once on tmudd MariaDB. Safe to run inside your existing student admin DB —
-- all tables are tym_ prefixed.

CREATE TABLE IF NOT EXISTS tym_sessions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  session_code VARCHAR(8) NOT NULL UNIQUE,
  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,
  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
) 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;

-- If upgrading from v1 (you already ran the previous schema), run instead:
--   ALTER TABLE tym_students
--     ADD COLUMN student_user_id INT NULL AFTER session_id,
--     ADD COLUMN is_guest TINYINT(1) NOT NULL DEFAULT 1 AFTER student_user_id,
--     ADD INDEX (student_user_id);