-- Ricoding Game API schema.
-- Run carefully in cPanel / phpMyAdmin after checking whether these tables already exist.

CREATE TABLE IF NOT EXISTS game_sessions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  session_id VARCHAR(128) NOT NULL UNIQUE,
  game_key VARCHAR(50) NOT NULL,
  status ENUM('playing', 'finished') NOT NULL DEFAULT 'playing',
  current_question INT NOT NULL DEFAULT 0,
  score INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_game_sessions_session_game (session_id, game_key),
  INDEX idx_game_sessions_status (status)
);

CREATE TABLE IF NOT EXISTS scores (
  id INT AUTO_INCREMENT PRIMARY KEY,
  game_key VARCHAR(50) NOT NULL,
  player_name VARCHAR(30) NOT NULL,
  score INT NOT NULL,
  rounds INT NOT NULL,
  accuracy DECIMAL(5,2) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_scores_game_score (game_key, score, accuracy, created_at)
);

-- If your existing game_sessions table is missing columns, add only the missing ones:
--
-- ALTER TABLE game_sessions ADD COLUMN status ENUM('playing', 'finished') NOT NULL DEFAULT 'playing';
-- ALTER TABLE game_sessions ADD COLUMN current_question INT NOT NULL DEFAULT 0;
-- ALTER TABLE game_sessions ADD COLUMN score INT NOT NULL DEFAULT 0;
--
-- If your existing scores table is missing columns:
--
-- ALTER TABLE scores ADD COLUMN rounds INT NOT NULL DEFAULT 0;
-- ALTER TABLE scores ADD COLUMN accuracy DECIMAL(5,2) NOT NULL DEFAULT 0;