CREATE DATABASE IF NOT EXISTS softball_broadcast_pro
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE softball_broadcast_pro;

CREATE TABLE IF NOT EXISTS teams (
  id INT AUTO_INCREMENT PRIMARY KEY,
  team_name VARCHAR(150) NOT NULL,
  short_name VARCHAR(20) NOT NULL,
  logo VARCHAR(255) DEFAULT '',
  team_color VARCHAR(30) DEFAULT '#1c64e8',
  contact_number VARCHAR(50) DEFAULT '',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS players (
  id INT AUTO_INCREMENT PRIMARY KEY,
  player_name VARCHAR(150) NOT NULL,
  short_name VARCHAR(30) DEFAULT '',
  role VARCHAR(50) DEFAULT 'Player',
  batting_style VARCHAR(80) DEFAULT '',
  bowling_style VARCHAR(80) DEFAULT '',
  jersey_number VARCHAR(20) DEFAULT '',
  photo VARCHAR(255) DEFAULT '',
  notes TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS team_players (
  id INT AUTO_INCREMENT PRIMARY KEY,
  team_id INT NOT NULL,
  player_id INT NOT NULL,
  status VARCHAR(20) DEFAULT 'active',
  assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY unique_team_player (team_id, player_id),
  INDEX idx_team_id (team_id),
  INDEX idx_player_id (player_id)
);

CREATE TABLE IF NOT EXISTS tournaments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tournament_name VARCHAR(180) NOT NULL,
  location VARCHAR(180) DEFAULT '',
  logo VARCHAR(255) DEFAULT '',
  status VARCHAR(30) DEFAULT 'draft',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS tournament_matches (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tournament_id INT NOT NULL,
  match_title VARCHAR(180) DEFAULT '',
  team_a_id INT NOT NULL,
  team_b_id INT NOT NULL,
  batting_team_id INT DEFAULT NULL,
  bowling_team_id INT DEFAULT NULL,
  striker_id INT DEFAULT NULL,
  non_striker_id INT DEFAULT NULL,
  opening_bowler_id INT DEFAULT NULL,
  current_striker_id INT DEFAULT NULL,
  current_non_striker_id INT DEFAULT NULL,
  current_bowler_id INT DEFAULT NULL,
  overs_per_innings INT DEFAULT 10,
  balls_per_over INT DEFAULT 6,
  match_order INT DEFAULT 1,
  innings_no INT DEFAULT 1,
  innings_completed TINYINT DEFAULT 0,
  match_status VARCHAR(30) DEFAULT 'scheduled',
  stats_saved TINYINT DEFAULT 0,
  winner_team_id INT DEFAULT NULL,
  result_text VARCHAR(255) DEFAULT '',
  ended_at DATETIME DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_tournament_id (tournament_id)
);

CREATE TABLE IF NOT EXISTS match_state (
  id INT PRIMARY KEY AUTO_INCREMENT,
  tournament_id INT DEFAULT NULL,
  tournament_name VARCHAR(180) DEFAULT '',
  tournament_location VARCHAR(180) DEFAULT '',
  tournament_logo VARCHAR(255) DEFAULT '',
  match_id INT DEFAULT NULL,
  innings_no INT DEFAULT 1,
  first_innings_runs INT DEFAULT 0,
  team_a_short VARCHAR(20) DEFAULT 'BAT',
  team_b_short VARCHAR(20) DEFAULT 'BOWL',
  team_a_logo VARCHAR(255) DEFAULT '',
  team_b_logo VARCHAR(255) DEFAULT '',
  batting_team VARCHAR(20) DEFAULT '',
  runs INT DEFAULT 0,
  wickets INT DEFAULT 0,
  overs INT DEFAULT 0,
  balls INT DEFAULT 0,
  total_balls INT DEFAULT 0,
  total_overs INT DEFAULT 10,
  balls_per_over INT DEFAULT 6,
  target INT DEFAULT 0,
  innings_completed TINYINT DEFAULT 0,
  striker VARCHAR(100) DEFAULT '',
  non_striker VARCHAR(100) DEFAULT '',
  striker_runs INT DEFAULT 0,
  striker_balls INT DEFAULT 0,
  non_striker_runs INT DEFAULT 0,
  non_striker_balls INT DEFAULT 0,
  bowler VARCHAR(100) DEFAULT '',
  bowler_runs INT DEFAULT 0,
  bowler_wickets INT DEFAULT 0,
  bowler_balls INT DEFAULT 0,
  bowler_overs VARCHAR(10) DEFAULT '0.0',
  partnership_runs INT DEFAULT 0,
  partnership_balls INT DEFAULT 0,
  last_wicket VARCHAR(150) DEFAULT 'MATCH READY',
  last_over_runs INT DEFAULT 0,
  last_event VARCHAR(20) DEFAULT 'LIVE',
  replay_event VARCHAR(20) DEFAULT '',
  status VARCHAR(20) DEFAULT 'READY',
  result_text VARCHAR(255) DEFAULT '',
  next_match_overlay_visible TINYINT DEFAULT 0,
  next_match_overlay_match_id INT DEFAULT NULL,
  toss_winner_team_id INT DEFAULT NULL,
  toss_choice VARCHAR(30) DEFAULT '',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO match_state (id) VALUES (1)
ON DUPLICATE KEY UPDATE id=id;



CREATE TABLE IF NOT EXISTS match_snapshots (
  match_id INT PRIMARY KEY,
  tournament_id INT DEFAULT NULL,
  tournament_name VARCHAR(180) DEFAULT '',
  tournament_location VARCHAR(180) DEFAULT '',
  tournament_logo VARCHAR(255) DEFAULT '',
  innings_no INT DEFAULT 1,
  first_innings_runs INT DEFAULT 0,
  team_a_short VARCHAR(20) DEFAULT 'BAT',
  team_b_short VARCHAR(20) DEFAULT 'BOWL',
  team_a_logo VARCHAR(255) DEFAULT '',
  team_b_logo VARCHAR(255) DEFAULT '',
  batting_team VARCHAR(20) DEFAULT '',
  runs INT DEFAULT 0,
  wickets INT DEFAULT 0,
  overs INT DEFAULT 0,
  balls INT DEFAULT 0,
  total_balls INT DEFAULT 0,
  total_overs INT DEFAULT 10,
  balls_per_over INT DEFAULT 6,
  target INT DEFAULT 0,
  innings_completed TINYINT DEFAULT 0,
  striker VARCHAR(100) DEFAULT '',
  non_striker VARCHAR(100) DEFAULT '',
  striker_runs INT DEFAULT 0,
  striker_balls INT DEFAULT 0,
  non_striker_runs INT DEFAULT 0,
  non_striker_balls INT DEFAULT 0,
  bowler VARCHAR(100) DEFAULT '',
  bowler_runs INT DEFAULT 0,
  bowler_wickets INT DEFAULT 0,
  bowler_balls INT DEFAULT 0,
  bowler_overs VARCHAR(10) DEFAULT '0.0',
  partnership_runs INT DEFAULT 0,
  partnership_balls INT DEFAULT 0,
  last_wicket VARCHAR(150) DEFAULT 'MATCH READY',
  last_over_runs INT DEFAULT 0,
  last_event VARCHAR(20) DEFAULT 'LIVE',
  replay_event VARCHAR(20) DEFAULT '',
  status VARCHAR(20) DEFAULT 'READY',
  result_text VARCHAR(255) DEFAULT '',
  next_match_overlay_visible TINYINT DEFAULT 0,
  next_match_overlay_match_id INT DEFAULT NULL,
  toss_winner_team_id INT DEFAULT NULL,
  toss_choice VARCHAR(30) DEFAULT '',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_tournament_id (tournament_id)
);

CREATE TABLE IF NOT EXISTS ball_events (
  id INT PRIMARY KEY AUTO_INCREMENT,
  match_id INT DEFAULT NULL,
  innings_no INT DEFAULT 1,
  event_type VARCHAR(20),
  runs INT DEFAULT 0,
  is_legal TINYINT DEFAULT 1,
  is_wicket TINYINT DEFAULT 0,
  over_no INT DEFAULT 0,
  ball_no INT DEFAULT 0,
  striker VARCHAR(100) DEFAULT '',
  bowler VARCHAR(100) DEFAULT '',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS match_player_stats (
  id INT AUTO_INCREMENT PRIMARY KEY,
  match_id INT NOT NULL,
  innings_no INT DEFAULT 1,
  player_id INT NOT NULL,
  team_id INT DEFAULT NULL,
  is_batting TINYINT DEFAULT 0,
  is_bowling TINYINT DEFAULT 0,
  runs INT DEFAULT 0,
  balls INT DEFAULT 0,
  fours INT DEFAULT 0,
  sixes INT DEFAULT 0,
  strike_rate DECIMAL(8,2) DEFAULT 0.00,
  overs_balls INT DEFAULT 0,
  runs_conceded INT DEFAULT 0,
  wickets INT DEFAULT 0,
  economy DECIMAL(8,2) DEFAULT 0.00,
  catches INT DEFAULT 0,
  runouts INT DEFAULT 0,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY unique_match_player_innings (match_id, innings_no, player_id)
);

CREATE TABLE IF NOT EXISTS player_career_stats (
  id INT AUTO_INCREMENT PRIMARY KEY,
  player_id INT NOT NULL UNIQUE,
  matches INT DEFAULT 0,
  innings INT DEFAULT 0,
  runs INT DEFAULT 0,
  balls INT DEFAULT 0,
  fours INT DEFAULT 0,
  sixes INT DEFAULT 0,
  highest_score INT DEFAULT 0,
  wickets INT DEFAULT 0,
  overs DECIMAL(8,1) DEFAULT 0.0,
  runs_conceded INT DEFAULT 0,
  strike_rate DECIMAL(8,2) DEFAULT 0.00,
  economy DECIMAL(8,2) DEFAULT 0.00,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);