113 lines
4.7 KiB
SQL
113 lines
4.7 KiB
SQL
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);
|