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

USE champion_vmix;

CREATE TABLE IF NOT EXISTS competitions (
  competition_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  competition_name VARCHAR(255) NOT NULL,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS squads (
  squad_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  squad_name VARCHAR(255) NOT NULL,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS matches (
  match_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  competition_id BIGINT UNSIGNED NULL,
  match_number INT NULL,
  round_number INT NULL,
  match_status VARCHAR(40) NULL,
  home_squad_id BIGINT UNSIGNED NULL,
  home_squad_name VARCHAR(255) NULL,
  away_squad_id BIGINT UNSIGNED NULL,
  away_squad_name VARCHAR(255) NULL,
  venue_id BIGINT UNSIGNED NULL,
  venue_name VARCHAR(255) NULL,
  venue_code VARCHAR(40) NULL,
  venue_timezone VARCHAR(80) NULL,
  utc_start_time DATETIME NULL,
  local_start_time VARCHAR(40) NULL,
  toss_won_home CHAR(1) NULL,
  toss_decision VARCHAR(40) NULL,
  last_feed_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_matches_status (match_status),
  INDEX idx_matches_competition_round (competition_id, round_number),
  INDEX idx_matches_start (utc_start_time),
  INDEX idx_matches_home_squad (home_squad_id),
  INDEX idx_matches_away_squad (away_squad_id),
  CONSTRAINT fk_matches_competitions FOREIGN KEY (competition_id) REFERENCES competitions (competition_id) ON DELETE SET NULL,
  CONSTRAINT fk_matches_home_squad FOREIGN KEY (home_squad_id) REFERENCES squads (squad_id) ON DELETE SET NULL,
  CONSTRAINT fk_matches_away_squad FOREIGN KEY (away_squad_id) REFERENCES squads (squad_id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS players (
  player_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  first_name VARCHAR(120) NULL,
  surname VARCHAR(120) NULL,
  display_name VARCHAR(160) NULL,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS match_players (
  match_id BIGINT UNSIGNED NOT NULL,
  player_id BIGINT UNSIGNED NOT NULL,
  squad_id BIGINT UNSIGNED NULL,
  jumper_number INT NULL,
  first_name VARCHAR(120) NULL,
  surname VARCHAR(120) NULL,
  display_name VARCHAR(160) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (match_id, player_id),
  INDEX idx_match_players_squad (match_id, squad_id),
  CONSTRAINT fk_match_players_match FOREIGN KEY (match_id) REFERENCES matches (match_id) ON DELETE CASCADE,
  CONSTRAINT fk_match_players_player FOREIGN KEY (player_id) REFERENCES players (player_id) ON DELETE CASCADE,
  CONSTRAINT fk_match_players_squad FOREIGN KEY (squad_id) REFERENCES squads (squad_id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ingest_files (
  ingest_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  payload_hash CHAR(64) NOT NULL,
  report_name VARCHAR(100) NULL,
  match_id BIGINT UNSIGNED NULL,
  job_id BIGINT NULL,
  remote_addr VARCHAR(45) NULL,
  payload_json JSON NULL,
  received_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_ingest_payload_hash (payload_hash),
  INDEX idx_ingest_match (match_id),
  INDEX idx_ingest_received (received_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS match_transactions (
  event_hash CHAR(64) NOT NULL PRIMARY KEY,
  match_id BIGINT UNSIGNED NOT NULL,
  seq_no INT NOT NULL,
  trx_code VARCHAR(60) NOT NULL,
  period INT NULL,
  period_seconds INT NULL,
  utc_time DATETIME NULL,
  local_time VARCHAR(40) NULL,
  squad_id BIGINT UNSIGNED NULL,
  squad_name VARCHAR(255) NULL,
  player_id BIGINT UNSIGNED NULL,
  first_name VARCHAR(120) NULL,
  surname VARCHAR(120) NULL,
  display_name VARCHAR(160) NULL,
  points TINYINT NOT NULL DEFAULT 0,
  is_score TINYINT(1) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  first_seen_ingest_id BIGINT UNSIGNED NOT NULL,
  last_seen_ingest_id BIGINT UNSIGNED NOT NULL,
  raw_json JSON NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_trx_match_active_time (match_id, is_active, period, period_seconds, seq_no),
  INDEX idx_trx_match_code (match_id, trx_code),
  INDEX idx_trx_squad (match_id, squad_id),
  INDEX idx_trx_player (match_id, player_id),
  INDEX idx_trx_seen (last_seen_ingest_id),
  CONSTRAINT fk_trx_match FOREIGN KEY (match_id) REFERENCES matches (match_id) ON DELETE CASCADE,
  CONSTRAINT fk_trx_squad FOREIGN KEY (squad_id) REFERENCES squads (squad_id) ON DELETE SET NULL,
  CONSTRAINT fk_trx_player FOREIGN KEY (player_id) REFERENCES players (player_id) ON DELETE SET NULL,
  CONSTRAINT fk_trx_first_ingest FOREIGN KEY (first_seen_ingest_id) REFERENCES ingest_files (ingest_id) ON DELETE CASCADE,
  CONSTRAINT fk_trx_last_ingest FOREIGN KEY (last_seen_ingest_id) REFERENCES ingest_files (ingest_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS player_period_stats (
  match_id BIGINT UNSIGNED NOT NULL,
  player_id BIGINT UNSIGNED NOT NULL,
  period INT NOT NULL,
  squad_id BIGINT UNSIGNED NULL,
  squad_name VARCHAR(255) NULL,
  first_name VARCHAR(120) NULL,
  surname VARCHAR(120) NULL,
  kicks INT NOT NULL DEFAULT 0,
  handballs INT NOT NULL DEFAULT 0,
  marks INT NOT NULL DEFAULT 0,
  hitouts INT NOT NULL DEFAULT 0,
  tackles INT NOT NULL DEFAULT 0,
  frees_for INT NOT NULL DEFAULT 0,
  frees_against INT NOT NULL DEFAULT 0,
  goals INT NOT NULL DEFAULT 0,
  behinds INT NOT NULL DEFAULT 0,
  dreamteam_points INT NOT NULL DEFAULT 0,
  raw_json JSON NOT NULL,
  last_seen_ingest_id BIGINT UNSIGNED NOT NULL,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (match_id, player_id, period),
  INDEX idx_player_period_squad (match_id, squad_id, period),
  CONSTRAINT fk_pps_match FOREIGN KEY (match_id) REFERENCES matches (match_id) ON DELETE CASCADE,
  CONSTRAINT fk_pps_player FOREIGN KEY (player_id) REFERENCES players (player_id) ON DELETE CASCADE,
  CONSTRAINT fk_pps_squad FOREIGN KEY (squad_id) REFERENCES squads (squad_id) ON DELETE SET NULL,
  CONSTRAINT fk_pps_ingest FOREIGN KEY (last_seen_ingest_id) REFERENCES ingest_files (ingest_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP VIEW IF EXISTS v_match_team_scores;
CREATE VIEW v_match_team_scores AS
SELECT
  match_id,
  squad_id,
  SUM(CASE WHEN LOWER(trx_code) = 'goal' THEN 1 ELSE 0 END) AS goals,
  SUM(CASE WHEN LOWER(trx_code) IN ('behind', 'rushed', 'rushedbehind', 'rushed_behind') THEN 1 ELSE 0 END) AS behinds,
  SUM(points) AS points
FROM match_transactions
WHERE is_active = 1
  AND squad_id IS NOT NULL
  AND points > 0
GROUP BY match_id, squad_id;

DROP VIEW IF EXISTS v_match_scoreboard;
CREATE VIEW v_match_scoreboard AS
SELECT
  m.match_id,
  m.competition_id,
  c.competition_name,
  m.round_number,
  m.match_number,
  m.match_status,
  m.venue_name,
  m.venue_code,
  m.utc_start_time,
  m.local_start_time,
  m.last_feed_at,
  m.home_squad_id,
  m.home_squad_name,
  COALESCE(h.goals, 0) AS home_goals,
  COALESCE(h.behinds, 0) AS home_behinds,
  COALESCE(h.points, 0) AS home_points,
  CONCAT(COALESCE(h.goals, 0), '.', COALESCE(h.behinds, 0), ' (', COALESCE(h.points, 0), ')') AS home_score_text,
  m.away_squad_id,
  m.away_squad_name,
  COALESCE(a.goals, 0) AS away_goals,
  COALESCE(a.behinds, 0) AS away_behinds,
  COALESCE(a.points, 0) AS away_points,
  CONCAT(COALESCE(a.goals, 0), '.', COALESCE(a.behinds, 0), ' (', COALESCE(a.points, 0), ')') AS away_score_text,
  CASE
    WHEN COALESCE(h.points, 0) > COALESCE(a.points, 0) THEN m.home_squad_name
    WHEN COALESCE(a.points, 0) > COALESCE(h.points, 0) THEN m.away_squad_name
    ELSE 'Draw'
  END AS leader_name,
  ABS(COALESCE(h.points, 0) - COALESCE(a.points, 0)) AS margin
FROM matches m
LEFT JOIN competitions c ON c.competition_id = m.competition_id
LEFT JOIN v_match_team_scores h ON h.match_id = m.match_id AND h.squad_id = m.home_squad_id
LEFT JOIN v_match_team_scores a ON a.match_id = m.match_id AND a.squad_id = m.away_squad_id;