-- Squared game schema

-- A game: one date/location/show, played in 4 rounds.
CREATE TABLE IF NOT EXISTS `squared_games` (
  `game_ID` INT(11) NOT NULL AUTO_INCREMENT,
  `game_date` DATE NOT NULL,
  `loc_ID` INT(11) NOT NULL,
  `show_ID` INT(11) DEFAULT NULL,
  `game_status` VARCHAR(20) NOT NULL DEFAULT 'pending', -- pending, running, ended
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`game_ID`),
  KEY `date_loc` (`game_date`, `loc_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- The 4 rounds within a game.
CREATE TABLE IF NOT EXISTS `squared_rounds` (
  `round_ID` INT(11) NOT NULL AUTO_INCREMENT,
  `game_ID` INT(11) NOT NULL,
  `round_number` TINYINT(4) NOT NULL, -- 1-4
  `round_name` VARCHAR(50) DEFAULT NULL,
  PRIMARY KEY (`round_ID`),
  UNIQUE KEY `game_round` (`game_ID`, `round_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- The 25 questions for a round, each mapped to a square (1-25) on the card.
CREATE TABLE IF NOT EXISTS `squared_rounds_questions` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `round_ID` INT(11) NOT NULL,
  `question_ID` INT(11) NOT NULL, -- FK -> questions.id
  `square` TINYINT(4) NOT NULL, -- 1-25
  `square_answer` VARCHAR(255) DEFAULT NULL, -- card-safe answer text; overrides questions.answer's host notes/brackets
  PRIMARY KEY (`id`),
  UNIQUE KEY `round_square` (`round_ID`, `square`),
  UNIQUE KEY `round_question` (`round_ID`, `question_ID`),
  KEY `question_lookup` (`question_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- If squared_rounds_questions already exists on the server, run this once instead:
-- ALTER TABLE `squared_rounds_questions` ADD COLUMN `square_answer` VARCHAR(255) DEFAULT NULL AFTER `square`;

-- Pre-generated deck of cards. Static inventory, reused across every game.
CREATE TABLE IF NOT EXISTS `squared_cards` (
  `card_ID` INT(11) NOT NULL AUTO_INCREMENT,
  `b_one` TINYINT(4) NOT NULL,
  `b_two` TINYINT(4) NOT NULL,
  `b_three` TINYINT(4) NOT NULL,
  `b_four` TINYINT(4) NOT NULL,
  `b_five` TINYINT(4) NOT NULL,
  `i_one` TINYINT(4) NOT NULL,
  `i_two` TINYINT(4) NOT NULL,
  `i_three` TINYINT(4) NOT NULL,
  `i_four` TINYINT(4) NOT NULL,
  `i_five` TINYINT(4) NOT NULL,
  `n_one` TINYINT(4) NOT NULL,
  `n_two` TINYINT(4) NOT NULL,
  `n_three` TINYINT(4) NOT NULL,
  `n_four` TINYINT(4) NOT NULL,
  `n_five` TINYINT(4) NOT NULL,
  `g_one` TINYINT(4) NOT NULL,
  `g_two` TINYINT(4) NOT NULL,
  `g_three` TINYINT(4) NOT NULL,
  `g_four` TINYINT(4) NOT NULL,
  `g_five` TINYINT(4) NOT NULL,
  `o_one` TINYINT(4) NOT NULL,
  `o_two` TINYINT(4) NOT NULL,
  `o_three` TINYINT(4) NOT NULL,
  `o_four` TINYINT(4) NOT NULL,
  `o_five` TINYINT(4) NOT NULL,
  PRIMARY KEY (`card_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Which card a device/team is playing, per game. random_key = per-person/device
-- cookie token (no login yet — a nullable user_ID can be added later for the app).
CREATE TABLE IF NOT EXISTS `squared_game_cards` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `game_ID` INT(11) NOT NULL,
  `card_ID` INT(11) NOT NULL,
  `random_key` VARCHAR(20) NOT NULL,
  `assigned_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `game_random_key` (`game_ID`, `random_key`),
  KEY `card_lookup` (`card_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Live state for a game: which round is showing and whether it's actually
-- playable right now. Players don't see any card contents (the square-to-answer
-- mapping) until round_status = 'active' — this is the gate that keeps the
-- answers hidden until the host starts the round.
CREATE TABLE IF NOT EXISTS `squared_game_status` (
  `game_ID` INT(11) NOT NULL,
  `current_round` TINYINT(4) DEFAULT NULL, -- 1-4, NULL before the host starts round 1
  `round_status` VARCHAR(20) NOT NULL DEFAULT 'pending', -- pending, active, paused, complete
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`game_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
