113 lines
5.3 KiB
SQL
113 lines
5.3 KiB
SQL
ALTER TABLE match_events
|
|
MODIFY event_type ENUM('serve','point','error','ace','block','attack','rotation','substitution','timeout','libero','yellow_card','red_card','mvp','audit','signature') NOT NULL;
|
|
|
|
SET @column_exists := (
|
|
SELECT COUNT(*)
|
|
FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = 'rotations'
|
|
AND COLUMN_NAME = 'is_libero'
|
|
);
|
|
SET @ddl := IF(
|
|
@column_exists = 0,
|
|
'ALTER TABLE rotations ADD COLUMN is_libero BOOLEAN NOT NULL DEFAULT FALSE',
|
|
'SELECT "rotations.is_libero already exists"'
|
|
);
|
|
PREPARE stmt FROM @ddl;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
CREATE TABLE IF NOT EXISTS match_liberos (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
match_id BIGINT UNSIGNED NOT NULL,
|
|
team_id BIGINT UNSIGNED NOT NULL,
|
|
player_id BIGINT UNSIGNED NOT NULL,
|
|
set_number TINYINT UNSIGNED NOT NULL,
|
|
is_starting BOOLEAN NOT NULL DEFAULT FALSE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT fk_liberos_match FOREIGN KEY (match_id) REFERENCES matches(id) ON DELETE CASCADE,
|
|
CONSTRAINT fk_liberos_team FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE,
|
|
CONSTRAINT fk_liberos_player FOREIGN KEY (player_id) REFERENCES players(id) ON DELETE CASCADE,
|
|
UNIQUE KEY uq_match_team_set_libero (match_id, team_id, set_number, player_id)
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE IF NOT EXISTS substitutions (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
match_id BIGINT UNSIGNED NOT NULL,
|
|
team_id BIGINT UNSIGNED NOT NULL,
|
|
set_number TINYINT UNSIGNED NOT NULL,
|
|
player_out_id BIGINT UNSIGNED NOT NULL,
|
|
player_in_id BIGINT UNSIGNED NOT NULL,
|
|
reason VARCHAR(120),
|
|
created_by BIGINT UNSIGNED,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT fk_substitutions_match FOREIGN KEY (match_id) REFERENCES matches(id) ON DELETE CASCADE,
|
|
CONSTRAINT fk_substitutions_team FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE,
|
|
CONSTRAINT fk_substitutions_out FOREIGN KEY (player_out_id) REFERENCES players(id),
|
|
CONSTRAINT fk_substitutions_in FOREIGN KEY (player_in_id) REFERENCES players(id),
|
|
CONSTRAINT fk_substitutions_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
|
|
INDEX idx_substitutions_match_set (match_id, set_number, team_id)
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE IF NOT EXISTS timeouts (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
match_id BIGINT UNSIGNED NOT NULL,
|
|
team_id BIGINT UNSIGNED NOT NULL,
|
|
set_number TINYINT UNSIGNED NOT NULL,
|
|
points_home TINYINT UNSIGNED NOT NULL DEFAULT 0,
|
|
points_away TINYINT UNSIGNED NOT NULL DEFAULT 0,
|
|
requested_by VARCHAR(120),
|
|
created_by BIGINT UNSIGNED,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT fk_timeouts_match FOREIGN KEY (match_id) REFERENCES matches(id) ON DELETE CASCADE,
|
|
CONSTRAINT fk_timeouts_team FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE,
|
|
CONSTRAINT fk_timeouts_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
|
|
INDEX idx_timeouts_match_set (match_id, set_number, team_id)
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE IF NOT EXISTS rally_history (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
match_id BIGINT UNSIGNED NOT NULL,
|
|
set_number TINYINT UNSIGNED NOT NULL,
|
|
rally_number INT UNSIGNED NOT NULL,
|
|
serving_team_id BIGINT UNSIGNED,
|
|
winning_team_id BIGINT UNSIGNED,
|
|
result_type ENUM('point','error','ace','block','attack') NOT NULL DEFAULT 'point',
|
|
points_home TINYINT UNSIGNED NOT NULL DEFAULT 0,
|
|
points_away TINYINT UNSIGNED NOT NULL DEFAULT 0,
|
|
notes VARCHAR(255),
|
|
created_by BIGINT UNSIGNED,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT fk_rally_match FOREIGN KEY (match_id) REFERENCES matches(id) ON DELETE CASCADE,
|
|
CONSTRAINT fk_rally_serving_team FOREIGN KEY (serving_team_id) REFERENCES teams(id) ON DELETE SET NULL,
|
|
CONSTRAINT fk_rally_winning_team FOREIGN KEY (winning_team_id) REFERENCES teams(id) ON DELETE SET NULL,
|
|
CONSTRAINT fk_rally_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
|
|
UNIQUE KEY uq_rally_number (match_id, set_number, rally_number)
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE IF NOT EXISTS referee_audit_logs (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
match_id BIGINT UNSIGNED NOT NULL,
|
|
user_id BIGINT UNSIGNED,
|
|
action VARCHAR(80) NOT NULL,
|
|
payload JSON,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT fk_audit_match FOREIGN KEY (match_id) REFERENCES matches(id) ON DELETE CASCADE,
|
|
CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
|
|
INDEX idx_audit_match_created (match_id, created_at)
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE IF NOT EXISTS referee_signatures (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
match_id BIGINT UNSIGNED NOT NULL,
|
|
referee_id BIGINT UNSIGNED,
|
|
signer_name VARCHAR(140) NOT NULL,
|
|
role VARCHAR(80) NOT NULL DEFAULT 'principal',
|
|
signature_hash CHAR(64) NOT NULL,
|
|
signed_payload JSON,
|
|
signed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT fk_signatures_match FOREIGN KEY (match_id) REFERENCES matches(id) ON DELETE CASCADE,
|
|
CONSTRAINT fk_signatures_referee FOREIGN KEY (referee_id) REFERENCES referees(id) ON DELETE SET NULL,
|
|
UNIQUE KEY uq_signature_match_role (match_id, role)
|
|
) ENGINE=InnoDB;
|