-- ============================================================
--  Live Quiz  ·  MySQL / MariaDB schema
--  Run this once against your cPanel database.
--  Keep this file in your repo — it is the definition of your
--  data model, and it belongs under version control like code.
-- ============================================================

SET NAMES utf8mb4;

-- utf8mb4 (not plain utf8) is used everywhere below. MySQL's
-- historical "utf8" only stores 3-byte characters, which breaks
-- on emoji and some Indic text. Trainee nicknames will contain
-- both. Get this right now; migrating later is painful.


-- ------------------------------------------------------------
-- 1. QUIZZES — a reusable set of questions you author once
-- ------------------------------------------------------------
CREATE TABLE quizzes (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title       VARCHAR(200)  NOT NULL,
  created_at  DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------------------
-- 2. QUESTIONS — belong to one quiz, displayed in sort_order
-- ------------------------------------------------------------
CREATE TABLE questions (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  quiz_id         INT UNSIGNED      NOT NULL,
  sort_order      SMALLINT UNSIGNED NOT NULL,
  question_text   TEXT              NOT NULL,
  time_limit_sec  SMALLINT UNSIGNED NOT NULL DEFAULT 20,
  max_points      SMALLINT UNSIGNED NOT NULL DEFAULT 1000,

  CONSTRAINT fk_questions_quiz
    FOREIGN KEY (quiz_id) REFERENCES quizzes(id) ON DELETE CASCADE,

  -- Two questions cannot occupy the same slot in the same quiz.
  UNIQUE KEY uq_quiz_order (quiz_id, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ON DELETE CASCADE: deleting a quiz deletes its questions
-- automatically. Without it you accumulate orphan rows —
-- questions pointing at a quiz that no longer exists.


-- ------------------------------------------------------------
-- 3. ANSWER_OPTIONS — the choices shown for each question
--    (named answer_options, not options: OPTION is a reserved
--     word in MySQL and will bite you eventually)
-- ------------------------------------------------------------
CREATE TABLE answer_options (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  question_id  INT UNSIGNED NOT NULL,
  label        CHAR(1)      NOT NULL,          -- 'A', 'B', 'C', 'D'
  option_text  VARCHAR(300) NOT NULL,
  is_correct   BOOLEAN      NOT NULL DEFAULT 0,

  CONSTRAINT fk_options_question
    FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE,

  UNIQUE KEY uq_question_label (question_id, label)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ------------------------------------------------------------
-- 4. SESSIONS — one live run of a quiz with a real audience.
--    A quiz is the script; a session is the performance.
-- ------------------------------------------------------------
CREATE TABLE sessions (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  quiz_id     INT UNSIGNED NOT NULL,
  join_code   VARCHAR(8)   NOT NULL,           -- goes inside the QR code
  status      ENUM('lobby','running','ended') NOT NULL DEFAULT 'lobby',
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  started_at  DATETIME     NULL,
  ended_at    DATETIME     NULL,

  CONSTRAINT fk_sessions_quiz
    FOREIGN KEY (quiz_id) REFERENCES quizzes(id),

  -- Two live sessions must never share a join code, or trainees
  -- end up in the wrong room. The database guarantees it.
  UNIQUE KEY uq_join_code (join_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Note: no ON DELETE CASCADE here. Deleting a quiz should NOT
-- silently erase the record of sessions you already ran. MySQL
-- will block the delete instead — which is the safer default
-- for historical data.


-- ------------------------------------------------------------
-- 5. PARTICIPANTS — one row per trainee per session
-- ------------------------------------------------------------
CREATE TABLE participants (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  session_id  INT UNSIGNED NOT NULL,
  nickname    VARCHAR(40)  NOT NULL,
  score       INT UNSIGNED NOT NULL DEFAULT 0,
  joined_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT fk_participants_session
    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE,

  -- No two people in the same room can pick the same nickname.
  -- The leaderboard would be unreadable otherwise.
  UNIQUE KEY uq_session_nickname (session_id, nickname)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 'score' is denormalised on purpose: it could be recalculated
-- by summing the answers table every time, but the leaderboard
-- is read constantly during a session. Storing the running
-- total is a deliberate trade — slight redundancy for speed.


-- ------------------------------------------------------------
-- 6. ANSWERS — the heart of the system.
--    This table is where requirement 5 (who answered first)
--    actually lives.
-- ------------------------------------------------------------
CREATE TABLE answers (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  session_id      INT UNSIGNED      NOT NULL,
  question_id     INT UNSIGNED      NOT NULL,
  participant_id  INT UNSIGNED      NOT NULL,

  option_id       INT UNSIGNED      NULL,      -- NULL = ran out of time
  latency_ms      INT UNSIGNED      NULL,      -- arrival time minus broadcast time
  is_correct      BOOLEAN           NOT NULL DEFAULT 0,
  points_awarded  SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  answer_rank     SMALLINT UNSIGNED NULL,      -- 1 = first correct answer
                                               -- ('rank' is reserved in MySQL 8)
  answered_at     DATETIME(3)       NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

  CONSTRAINT fk_answers_session
    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE,
  CONSTRAINT fk_answers_question
    FOREIGN KEY (question_id) REFERENCES questions(id),
  CONSTRAINT fk_answers_participant
    FOREIGN KEY (participant_id) REFERENCES participants(id) ON DELETE CASCADE,

  -- THE important constraint: one answer per person per question.
  -- Your application code will check this too. This is the
  -- backstop for when the code is wrong.
  UNIQUE KEY uq_one_answer (session_id, question_id, participant_id),

  -- Index for the query you run after every single question:
  -- "give me all answers to this question in this session".
  KEY idx_session_question (session_id, question_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- DATETIME(3) stores milliseconds. Plain DATETIME rounds to the
-- second, which is useless when twelve people answer inside the
-- same second and you have to rank them.


-- ============================================================
--  SAMPLE DATA — one quiz, two questions, so you can see the
--  shape of real rows. Delete once you have the admin panel.
-- ============================================================

INSERT INTO quizzes (title) VALUES ('Networking Fundamentals');

INSERT INTO questions (quiz_id, sort_order, question_text, time_limit_sec) VALUES
  (1, 1, 'Which OSI layer does a switch primarily operate at?', 15),
  (1, 2, 'What is the default administrative distance of OSPF on Cisco IOS?', 20);

INSERT INTO answer_options (question_id, label, option_text, is_correct) VALUES
  (1, 'A', 'Layer 1 - Physical',   0),
  (1, 'B', 'Layer 2 - Data Link',  1),
  (1, 'C', 'Layer 3 - Network',    0),
  (1, 'D', 'Layer 4 - Transport',  0),
  (2, 'A', '90',   0),
  (2, 'B', '100',  0),
  (2, 'C', '110',  1),
  (2, 'D', '120',  0);


-- ============================================================
--  TRY THESE after loading, to get a feel for SQL.
--  Run them in phpMyAdmin's SQL tab.
-- ============================================================

-- Every question with its options, in display order:
--
--   SELECT q.sort_order, q.question_text, o.label, o.option_text, o.is_correct
--   FROM questions q
--   JOIN answer_options o ON o.question_id = q.id
--   WHERE q.quiz_id = 1
--   ORDER BY q.sort_order, o.label;
--
-- That JOIN is the price of normalising options into their own
-- table — and it is a price worth paying.

-- The leaderboard query you will use in Phase 3:
--
--   SELECT nickname, score
--   FROM participants
--   WHERE session_id = 1
--   ORDER BY score DESC, joined_at ASC
--   LIMIT 10;