-- /db/schema.sql
CREATE TABLE IF NOT EXISTS participants (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  phone VARCHAR(32) NOT NULL,
  display_name VARCHAR(80) DEFAULT NULL,
  status ENUM('idle','playing','finished') NOT NULL DEFAULT 'idle',
  started_at DATETIME DEFAULT NULL,
  finished_at DATETIME DEFAULT NULL,
  total_time_ms INT UNSIGNED DEFAULT NULL,
  score INT UNSIGNED DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS questions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  qtext VARCHAR(255) NOT NULL,
  a1 VARCHAR(120) NOT NULL,
  a2 VARCHAR(120) NOT NULL,
  a3 VARCHAR(120) NOT NULL,
  a4 VARCHAR(120) NOT NULL,
  correct TINYINT(1) NOT NULL, -- 1..4
  active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS game_runs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  participant_id INT UNSIGNED NOT NULL,
  question_id INT UNSIGNED NOT NULL,
  qindex TINYINT UNSIGNED NOT NULL, -- 1..10
  sent_at DATETIME NOT NULL,
  answered_at DATETIME DEFAULT NULL,
  answer_option TINYINT(1) DEFAULT NULL, -- 1..4
  is_correct TINYINT(1) DEFAULT NULL,
  time_ms INT UNSIGNED DEFAULT NULL,
  FOREIGN KEY (participant_id) REFERENCES participants(id),
  FOREIGN KEY (question_id) REFERENCES questions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- opcional: seed de 10 perguntas (exemplo; você pode editar depois)
INSERT INTO questions (qtext,a1,a2,a3,a4,correct) VALUES
('Capital do Brasil?', 'Brasília','Rio','SP','BH',1),
('Planeta vermelho?', 'Vênus','Marte','Júpiter','Saturno',2),
('Cor primária?', 'Verde','Roxo','Vermelho','Laranja',3),
('2+2=?', '3','4','5','6',2),
('Oceano maior?', 'Índico','Atlântico','Ártico','Pacífico',4),
('Criador do Linux?', 'Jobs','Gates','Torvalds','Musk',3),
('Linguagem do navegador?', 'PHP','C#','Go','JavaScript',4),
('Sigla de Inteligência Artificial?', 'IA','AI','AR','IR',2),
('Continente do Egito?', 'Ásia','Europa','África','Oceania',3),
('Elemento H2O?', 'Água','Fogo','Ar','Terra',1);
