-- Grudge Match schema. Run once:
--   mysql -u root -p < server/schema.sql

CREATE DATABASE IF NOT EXISTS scoreForCouples CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE scoreForCouples;

CREATE TABLE IF NOT EXISTS games (
  id VARCHAR(64) PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  emoji VARCHAR(16) NOT NULL DEFAULT '🎲',
  scoring ENUM('highWins', 'lowWins') NOT NULL DEFAULT 'highWins',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS matches (
  id INT AUTO_INCREMENT PRIMARY KEY,
  game_id VARCHAR(64) NOT NULL,
  p1_score INT NOT NULL,
  p2_score INT NOT NULL,
  winner_id VARCHAR(8) DEFAULT NULL, -- 'p1' | 'p2' | NULL = tie
  note TEXT,
  cheat_check_flagged TINYINT(1) NOT NULL DEFAULT 0,
  played_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (game_id) REFERENCES games(id),
  INDEX idx_matches_game (game_id, played_at)
);

CREATE TABLE IF NOT EXISTS disputes (
  match_id INT PRIMARY KEY,
  statement TEXT NOT NULL,
  filed_by VARCHAR(8) NOT NULL,
  status ENUM('open', 'score-stands', 'overturned') NOT NULL DEFAULT 'open',
  filed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (match_id) REFERENCES matches(id)
);

-- key/value settings, same shape as the ECI settings table
CREATE TABLE IF NOT EXISTS settings (
  field VARCHAR(64) PRIMARY KEY,
  value VARCHAR(255) NOT NULL
);

INSERT IGNORE INTO games (id, name, emoji, scoring) VALUES
  ('five-crowns', 'Five Crowns', '👑', 'lowWins'),
  ('cribbage', 'Cribbage', '🃏', 'highWins'),
  ('jenga', 'Jenga', '🧱', 'highWins'),
  ('bowling', 'Bowling', '🎳', 'highWins'),
  ('checkers', 'Checkers', '⚫', 'highWins');

INSERT IGNORE INTO settings (field, value) VALUES ('chaos', 'medium');
