-- Story Arc Game Database Setup
-- Run this once on tmudd.net via phpMyAdmin or MySQL CLI
-- Database: story_arc_game (create this first, or use existing db)

CREATE TABLE IF NOT EXISTS sag_games (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  game_code     CHAR(4) NOT NULL UNIQUE,
  story         VARCHAR(50) NOT NULL DEFAULT 'emperor',
  cards_json    TEXT NOT NULL,
  status        ENUM('waiting','active','paused','finished') NOT NULL DEFAULT 'waiting',
  feedback_mode ENUM('none','after_card','end') NOT NULL DEFAULT 'after_card',
  teacher_note  TEXT,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_code (game_code)
);

CREATE TABLE IF NOT EXISTS sag_players (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  game_code     CHAR(4) NOT NULL,
  name          VARCHAR(80) NOT NULL,
  session_code  CHAR(6) NOT NULL UNIQUE,
  score         DECIMAL(6,2) NOT NULL DEFAULT 0,
  card_index    SMALLINT NOT NULL DEFAULT 0,
  progress_json TEXT,
  calibrated    TINYINT(1) NOT NULL DEFAULT 0,
  completed     TINYINT(1) NOT NULL DEFAULT 0,
  joined_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_game (game_code),
  INDEX idx_session (session_code)
);

CREATE TABLE IF NOT EXISTS sag_responses (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  player_id     INT NOT NULL,
  card_index    SMALLINT NOT NULL,
  attempt       TINYINT NOT NULL,
  response_text TEXT NOT NULL,
  ib_level      TINYINT,
  points        DECIMAL(4,2),
  ai_reply      TEXT,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_player (player_id)
);