82 lines
3.8 KiB
SQL
82 lines
3.8 KiB
SQL
-- Apply once via php bin/console.php migrate. Backup first.
|
|
ALTER TABLE players ADD category VARCHAR(60) NULL, ADD usual_team_id INT UNSIGNED NULL,
|
|
ADD CONSTRAINT fk_player_usual_team FOREIGN KEY (usual_team_id) REFERENCES teams(id);
|
|
ALTER TABLE sanctions ADD event_id BIGINT UNSIGNED NULL, ADD end_date DATE NULL,
|
|
ADD duration_kind ENUM('matches','period','indefinite') NOT NULL DEFAULT 'matches',
|
|
ADD purge_from DATE NULL,
|
|
ADD exclusion_date DATE NULL,
|
|
ADD CONSTRAINT fk_sanction_event FOREIGN KEY (event_id) REFERENCES disciplinary_events(id);
|
|
-- The MVP's global 'served' flag must not hide a sanction for another team.
|
|
UPDATE sanctions SET status='official' WHERE status='served';
|
|
UPDATE sanctions SET exclusion_date=effective_date WHERE sanction_type='red_provisional' AND exclusion_date IS NULL;
|
|
ALTER TABLE imports ADD team_id INT UNSIGNED NULL, ADD actor_id INT UNSIGNED NULL;
|
|
ALTER TABLE matches ADD purge_override TINYINT(1) NOT NULL DEFAULT 0, ADD is_official TINYINT(1) NOT NULL DEFAULT 1;
|
|
CREATE TABLE users (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(120) NOT NULL, email VARCHAR(190) NOT NULL UNIQUE,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
role ENUM('admin','secretary','coach','viewer') NOT NULL,
|
|
active TINYINT(1) NOT NULL DEFAULT 1,
|
|
must_change_password TINYINT(1) NOT NULL DEFAULT 1,
|
|
auth_version INT UNSIGNED NOT NULL DEFAULT 1,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB;
|
|
CREATE TABLE login_attempts (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
bucket CHAR(64) NOT NULL, attempted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
KEY idx_attempt(bucket,attempted_at)
|
|
) ENGINE=InnoDB;
|
|
CREATE TABLE app_state (id INT PRIMARY KEY,revision BIGINT UNSIGNED NOT NULL DEFAULT 1);
|
|
INSERT INTO app_state VALUES(1,1);
|
|
CREATE TABLE data_checks (
|
|
scope VARCHAR(40) PRIMARY KEY,
|
|
checked_at DATETIME NOT NULL,
|
|
checked_by INT UNSIGNED NULL,
|
|
note VARCHAR(255) NOT NULL
|
|
) ENGINE=InnoDB;
|
|
CREATE TABLE squads (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
match_id BIGINT UNSIGNED NOT NULL UNIQUE,
|
|
updated_by INT UNSIGNED NOT NULL,
|
|
version INT UNSIGNED NOT NULL DEFAULT 1,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
CONSTRAINT fk_squad_match FOREIGN KEY(match_id) REFERENCES matches(id)
|
|
) ENGINE=InnoDB;
|
|
CREATE TABLE squad_players (
|
|
squad_id BIGINT UNSIGNED NOT NULL,
|
|
player_id INT UNSIGNED NOT NULL,
|
|
PRIMARY KEY(squad_id,player_id),
|
|
CONSTRAINT fk_sp_squad FOREIGN KEY(squad_id) REFERENCES squads(id) ON DELETE CASCADE,
|
|
CONSTRAINT fk_sp_player FOREIGN KEY(player_id) REFERENCES players(id)
|
|
) ENGINE=InnoDB;
|
|
CREATE TABLE reports (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
title VARCHAR(255) NOT NULL,
|
|
match_id BIGINT UNSIGNED NULL,
|
|
generated_by INT UNSIGNED NULL,
|
|
generated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
revision BIGINT UNSIGNED NOT NULL,
|
|
snapshot LONGTEXT NOT NULL
|
|
) ENGINE=InnoDB;
|
|
CREATE TABLE audit_log (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
actor_id INT UNSIGNED NULL, action VARCHAR(50) NOT NULL, entity VARCHAR(50) NOT NULL,
|
|
entity_id BIGINT UNSIGNED NOT NULL,
|
|
before_json LONGTEXT NULL, after_json LONGTEXT NULL, reason TEXT NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB;
|
|
CREATE TABLE import_batches (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
actor_id INT UNSIGNED NOT NULL,
|
|
kind VARCHAR(30) NOT NULL,
|
|
team_id INT UNSIGNED NULL,
|
|
filename VARCHAR(255) NOT NULL,
|
|
fingerprint CHAR(64) NOT NULL,
|
|
payload LONGTEXT NOT NULL,
|
|
base_revision BIGINT UNSIGNED NOT NULL,
|
|
status ENUM('preview','applied') NOT NULL DEFAULT 'preview',
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB;
|
|
CREATE TABLE schema_migrations(version VARCHAR(30) PRIMARY KEY,applied_at DATETIME DEFAULT CURRENT_TIMESTAMP);
|
|
INSERT INTO schema_migrations(version) VALUES('002_v2');
|