-- baseball_ai スキーマ
CREATE DATABASE IF NOT EXISTS baseball_ai
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE baseball_ai;

CREATE TABLE IF NOT EXISTS teams (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(128) NOT NULL,
  league ENUM('CL', 'PL', 'OTHER') NOT NULL DEFAULT 'OTHER',
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_teams_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS games (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  game_id VARCHAR(32) NOT NULL,
  season SMALLINT UNSIGNED NOT NULL,
  game_date DATE NOT NULL,
  game_type VARCHAR(32) NOT NULL DEFAULT 'Reg Season',
  home_team_id INT UNSIGNED NOT NULL,
  away_team_id INT UNSIGNED NOT NULL,
  home_score TINYINT UNSIGNED NOT NULL,
  away_score TINYINT UNSIGNED NOT NULL,
  home_hits SMALLINT UNSIGNED DEFAULT NULL,
  away_hits SMALLINT UNSIGNED DEFAULT NULL,
  home_errors SMALLINT UNSIGNED DEFAULT NULL,
  away_errors SMALLINT UNSIGNED DEFAULT NULL,
  ballpark VARCHAR(128) DEFAULT NULL,
  audience INT UNSIGNED DEFAULT NULL,
  game_start TIME DEFAULT NULL,
  game_length_min SMALLINT UNSIGNED DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_games_game_id (game_id),
  KEY idx_games_date (game_date),
  KEY idx_games_season_date (season, game_date),
  KEY idx_games_home (home_team_id),
  KEY idx_games_away (away_team_id),
  CONSTRAINT fk_games_home FOREIGN KEY (home_team_id) REFERENCES teams (id),
  CONSTRAINT fk_games_away FOREIGN KEY (away_team_id) REFERENCES teams (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS game_predictions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  game_id VARCHAR(32) NOT NULL,
  profile_key VARCHAR(64) NOT NULL DEFAULT 'pythag_v1',
  home_win_prob DECIMAL(6, 4) NOT NULL,
  away_win_prob DECIMAL(6, 4) NOT NULL,
  home_runs_est DECIMAL(4, 2) DEFAULT NULL COMMENT '目安得点',
  away_runs_est DECIMAL(4, 2) DEFAULT NULL COMMENT '目安得点',
  predicted_at DATETIME NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_pred_game_profile (game_id, profile_key),
  KEY idx_pred_predicted_at (predicted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
