-- ============================================================
--  Quiz Engine — Database Schema
--  Install on tmudd.net MySQL database
--  Run this file once to set up all tables
-- ============================================================

SET NAMES utf8mb4;
SET time_zone = '+00:00';

-- ------------------------------------------------------------
--  TEACHERS
--  One row per teacher account
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS teachers (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username      VARCHAR(60)  NOT NULL UNIQUE,
    display_name  VARCHAR(120) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,          -- PHP password_hash()
    email         VARCHAR(255),
    created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
--  QUIZZES
--  One row per quiz/game
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS quizzes (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    teacher_id    INT UNSIGNED NOT NULL,
    slug          VARCHAR(80)  NOT NULL UNIQUE,   -- URL-safe identifier e.g. "gauss-2026"
    title         VARCHAR(255) NOT NULL,
    subtitle      VARCHAR(255),
    description   TEXT,
    -- Level labels (comma-separated, 5 values)
    level_labels  VARCHAR(255) NOT NULL DEFAULT 'Starter,Explorer,Challenger,Advanced,Expert',
    -- Score caps per level (comma-separated percentages, 5 values)
    level_caps    VARCHAR(50)  NOT NULL DEFAULT '60,70,80,90,100',
    -- Visual theme stored as JSON: {"primary":"#1a6fa8","bg":"#f2f2f7",...}
    theme         JSON,
    -- Whether mid-quiz level switching is enabled
    allow_level_switch TINYINT(1) NOT NULL DEFAULT 1,
    -- Passcode to clear leaderboard
    clear_passcode VARCHAR(60) NOT NULL DEFAULT 'changeme',
    published     TINYINT(1)  NOT NULL DEFAULT 0,
    created_at    DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
--  STAGES
--  5 story stages + 1 synthesis per quiz
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS stages (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    quiz_id       INT UNSIGNED NOT NULL,
    stage_num     TINYINT UNSIGNED NOT NULL,      -- 1–5 for story, 6 = synthesis
    stage_name    VARCHAR(100) NOT NULL,           -- e.g. "Beginning"
    arc_label     VARCHAR(100),                   -- e.g. "Setting & Characters"
    is_synthesis  TINYINT(1)  NOT NULL DEFAULT 0,
    sort_order    TINYINT UNSIGNED NOT NULL DEFAULT 0,
    -- Narrative cards stored as JSON array: [{"heading":"...","text":"..."},...]
    narrative     JSON,
    FOREIGN KEY (quiz_id) REFERENCES quizzes(id) ON DELETE CASCADE,
    UNIQUE KEY uq_quiz_stage (quiz_id, stage_num)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
--  QUESTIONS
--  Per stage, per level
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS questions (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    stage_id      INT UNSIGNED NOT NULL,
    level         TINYINT UNSIGNED NOT NULL,      -- 1=Starter … 5=Expert
    sort_order    TINYINT UNSIGNED NOT NULL DEFAULT 0,
    question_type ENUM('mc','open') NOT NULL DEFAULT 'mc',
    question_text TEXT NOT NULL,
    -- For open questions: placeholder hint text
    placeholder   TEXT,
    -- For MC questions: index of correct option (0-based)
    correct_index TINYINT,
    -- Feedback shown after answering MC question
    feedback      TEXT,
    FOREIGN KEY (stage_id) REFERENCES stages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
--  OPTIONS
--  MC answer options, 2–4 per question
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS options (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    question_id   INT UNSIGNED NOT NULL,
    sort_order    TINYINT UNSIGNED NOT NULL DEFAULT 0,
    option_text   TEXT NOT NULL,
    FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
--  SESSIONS
--  One row per student play-through
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS sessions (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    quiz_id       INT UNSIGNED NOT NULL,
    token         VARCHAR(32)  NOT NULL UNIQUE,   -- random hex, used as studentId
    student_name  VARCHAR(255) NOT NULL,
    started_at    DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at  DATETIME,                        -- NULL until quiz finished
    -- Running totals (updated after each stage)
    total_mc      SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    total_correct SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    raw_pct       TINYINT UNSIGNED NOT NULL DEFAULT 0,
    adjusted_pct  TINYINT UNSIGNED NOT NULL DEFAULT 0,
    FOREIGN KEY (quiz_id) REFERENCES quizzes(id) ON DELETE CASCADE,
    INDEX idx_quiz_token (quiz_id, token),
    INDEX idx_quiz_score (quiz_id, adjusted_pct)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
--  STAGE RESULTS
--  One row per stage per session
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS stage_results (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    session_id    INT UNSIGNED NOT NULL,
    stage_id      INT UNSIGNED NOT NULL,
    level         TINYINT UNSIGNED NOT NULL,      -- level played at this stage
    level_label   VARCHAR(60)  NOT NULL,
    level_cap     TINYINT UNSIGNED NOT NULL,      -- cap % at this stage
    score         SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    mc_total      SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    pct           TINYINT UNSIGNED NOT NULL DEFAULT 0,
    -- Open responses stored as JSON array of strings
    open_responses JSON,
    submitted_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE,
    FOREIGN KEY (stage_id)   REFERENCES stages(id)   ON DELETE CASCADE,
    UNIQUE KEY uq_session_stage (session_id, stage_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
--  SEED DATA — example teacher account
--  Password: "changeme" — CHANGE THIS IMMEDIATELY after install
-- ============================================================
INSERT IGNORE INTO teachers (username, display_name, password_hash, email)
VALUES (
    'admin',
    'Administrator',
    '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', -- "password"
    'admin@example.com'
);