-- Dracon Gaming: Call of Duty Hub
-- Import in phpMyAdmin. Works with DB name `dracongaming` (or any DB; this file only creates tables).

CREATE TABLE IF NOT EXISTS cod_server_status (
  id INT AUTO_INCREMENT PRIMARY KEY,
  server_label VARCHAR(120) NOT NULL,
  game_title VARCHAR(120) NOT NULL,
  host VARCHAR(120) NOT NULL,
  port INT NOT NULL,
  is_up TINYINT(1) NOT NULL DEFAULT 0,
  players_online INT NOT NULL DEFAULT 0,
  players_max INT NOT NULL DEFAULT 0,
  last_checked DATETIME NULL,
  UNIQUE KEY uniq_server (server_label, host, port)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cod_player_rankings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  player_tag VARCHAR(64) NOT NULL,
  platform ENUM('PC','Xbox','PlayStation','Other') NOT NULL DEFAULT 'PC',
  cod_version VARCHAR(120) NOT NULL DEFAULT 'All',
  elo INT NOT NULL DEFAULT 1000,
  wins INT NOT NULL DEFAULT 0,
  losses INT NOT NULL DEFAULT 0,
  last_match_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_player (player_tag, platform, cod_version),
  KEY idx_elo (elo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cod_matches (
  id INT AUTO_INCREMENT PRIMARY KEY,
  cod_version VARCHAR(120) NOT NULL,
  map_name VARCHAR(120) NOT NULL,
  mode VARCHAR(80) NOT NULL,
  submitted_by VARCHAR(80) NOT NULL DEFAULT 'system',
  payload_json JSON NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
