-- ZEZ Lucky Six Demo (no-money home game) schema
-- MySQL 5.7+ / MariaDB 10.2+

CREATE TABLE IF NOT EXISTS rounds (
  id INT AUTO_INCREMENT PRIMARY KEY,
  day_date DATE NOT NULL,
  round_number INT NOT NULL,
  status ENUM('open','drawing','finished') NOT NULL DEFAULT 'open',
  started_at DATETIME NULL,
  finished_at DATETIME NULL,
  UNIQUE KEY uniq_day_round (day_date, round_number),
  KEY idx_day_status (day_date, status),
  KEY idx_day_round (day_date, round_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS draws (
  id INT AUTO_INCREMENT PRIMARY KEY,
  round_id INT NOT NULL,
  ball_index TINYINT NOT NULL,
  number TINYINT NOT NULL,
  drawn_at DATETIME NOT NULL,
  UNIQUE KEY uniq_round_ball (round_id, ball_index),
  UNIQUE KEY uniq_round_number (round_id, number),
  KEY idx_round (round_id),
  CONSTRAINT fk_draws_rounds FOREIGN KEY (round_id) REFERENCES rounds(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS predictions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  day_date DATE NOT NULL,
  round_number INT NOT NULL,
  player_name VARCHAR(60) NOT NULL,
  numbers_json VARCHAR(255) NOT NULL, -- e.g. "[1,7,13,22,33,48]"
  created_at DATETIME NOT NULL,
  KEY idx_day_round (day_date, round_number),
  KEY idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;