CREATE DATABASE IF NOT EXISTS esbsc_suspensions CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE esbsc_suspensions; CREATE TABLE IF NOT EXISTS teams ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL, short_name VARCHAR(30) NOT NULL, level_label VARCHAR(80) NULL, sort_order TINYINT UNSIGNED NOT NULL DEFAULT 1, active TINYINT(1) NOT NULL DEFAULT 1, UNIQUE KEY uq_team_short (short_name) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS players ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, licence_number VARCHAR(30) NULL, last_name VARCHAR(100) NOT NULL, first_name VARCHAR(100) NOT NULL, active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_licence (licence_number), KEY idx_player_name (last_name, first_name) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS matches ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, fff_match_id VARCHAR(40) NULL, team_id INT UNSIGNED NOT NULL, competition VARCHAR(180) NULL, round_label VARCHAR(80) NULL, match_date DATE NOT NULL, match_time TIME NULL, opponent VARCHAR(180) NULL, home_away ENUM('home','away','unknown') NOT NULL DEFAULT 'unknown', status ENUM('scheduled','played','postponed','forfeit_win','forfeit_loss','abandoned','exempt','cancelled') NOT NULL DEFAULT 'scheduled', counts_for_purge TINYINT(1) NOT NULL DEFAULT 0, notes TEXT NULL, source_import_id BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_match_team FOREIGN KEY (team_id) REFERENCES teams(id), UNIQUE KEY uq_match_fff_team (fff_match_id, team_id), KEY idx_match_team_date (team_id, match_date) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS disciplinary_events ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, player_id INT UNSIGNED NOT NULL, match_id BIGINT UNSIGNED NULL, event_date DATE NOT NULL, event_type ENUM('yellow','red','second_yellow_red','other') NOT NULL, team_id INT UNSIGNED NULL, source VARCHAR(40) NOT NULL DEFAULT 'manual', source_ref VARCHAR(120) NULL, notes TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_event_player FOREIGN KEY (player_id) REFERENCES players(id), CONSTRAINT fk_event_match FOREIGN KEY (match_id) REFERENCES matches(id) ON DELETE SET NULL, CONSTRAINT fk_event_team FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE SET NULL, KEY idx_event_player_date (player_id, event_date) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS sanctions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, player_id INT UNSIGNED NOT NULL, sanction_type ENUM('red_provisional','yellow_accumulation','commission','other') NOT NULL DEFAULT 'commission', status ENUM('provisional','official','revoked','served') NOT NULL DEFAULT 'official', decision_date DATE NULL, effective_date DATE NOT NULL, matches_required SMALLINT UNSIGNED NOT NULL DEFAULT 1, source VARCHAR(40) NOT NULL DEFAULT 'manual', external_ref VARCHAR(120) NULL, label VARCHAR(255) NULL, notes TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_sanction_player FOREIGN KEY (player_id) REFERENCES players(id), KEY idx_sanction_player_effective (player_id, effective_date), KEY idx_sanction_status (status) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS imports ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, import_type ENUM('calendar','discipline','players') NOT NULL, filename VARCHAR(255) NOT NULL, file_hash CHAR(64) NOT NULL, imported_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, row_count INT UNSIGNED NOT NULL DEFAULT 0, inserted_count INT UNSIGNED NOT NULL DEFAULT 0, updated_count INT UNSIGNED NOT NULL DEFAULT 0, error_count INT UNSIGNED NOT NULL DEFAULT 0, notes TEXT NULL, UNIQUE KEY uq_import_hash_type (file_hash, import_type) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS import_errors ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, import_id BIGINT UNSIGNED NOT NULL, `row_number` INT UNSIGNED NULL, raw_payload LONGTEXT NULL, error_message TEXT NOT NULL, CONSTRAINT fk_import_error FOREIGN KEY (import_id) REFERENCES imports(id) ON DELETE CASCADE ) ENGINE=InnoDB; INSERT INTO teams (name, short_name, level_label, sort_order) VALUES ('ES Beaumont Saint-Cyr 1','E1','Senior 1',1), ('ES Beaumont Saint-Cyr 2','E2','Senior 2',2), ('ES Beaumont Saint-Cyr 3','E3','Senior 3',3), ('ES Beaumont Saint-Cyr 4','E4','Senior 4',4) ON DUPLICATE KEY UPDATE name=VALUES(name), level_label=VALUES(level_label), sort_order=VALUES(sort_order);