46 lines
2.2 KiB
SQL
46 lines
2.2 KiB
SQL
CREATE TABLE IF NOT EXISTS sheet_templates (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
code VARCHAR(80) NOT NULL UNIQUE,
|
|
name VARCHAR(160) NOT NULL,
|
|
image_path VARCHAR(255) NOT NULL,
|
|
page_width INT UNSIGNED NOT NULL,
|
|
page_height INT UNSIGNED NOT NULL,
|
|
config_json JSON NOT NULL,
|
|
active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE IF NOT EXISTS tournament_sheet_templates (
|
|
tournament_id BIGINT UNSIGNED NOT NULL,
|
|
sheet_template_id BIGINT UNSIGNED NOT NULL,
|
|
is_default BOOLEAN NOT NULL DEFAULT TRUE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (tournament_id, sheet_template_id),
|
|
CONSTRAINT fk_tournament_sheet_tournament FOREIGN KEY (tournament_id) REFERENCES tournaments(id) ON DELETE CASCADE,
|
|
CONSTRAINT fk_tournament_sheet_template FOREIGN KEY (sheet_template_id) REFERENCES sheet_templates(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE IF NOT EXISTS match_sheet_template_overrides (
|
|
match_id BIGINT UNSIGNED PRIMARY KEY,
|
|
sheet_template_id BIGINT UNSIGNED NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT fk_match_sheet_override_match FOREIGN KEY (match_id) REFERENCES matches(id) ON DELETE CASCADE,
|
|
CONSTRAINT fk_match_sheet_override_template FOREIGN KEY (sheet_template_id) REFERENCES sheet_templates(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB;
|
|
|
|
CREATE TABLE IF NOT EXISTS match_sheet_exports (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
match_id BIGINT UNSIGNED NOT NULL,
|
|
sheet_template_id BIGINT UNSIGNED NOT NULL,
|
|
export_type ENUM('html','pdf','image') NOT NULL DEFAULT 'html',
|
|
file_path VARCHAR(255),
|
|
export_hash CHAR(64),
|
|
created_by BIGINT UNSIGNED,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT fk_sheet_exports_match FOREIGN KEY (match_id) REFERENCES matches(id) ON DELETE CASCADE,
|
|
CONSTRAINT fk_sheet_exports_template FOREIGN KEY (sheet_template_id) REFERENCES sheet_templates(id),
|
|
CONSTRAINT fk_sheet_exports_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
|
|
INDEX idx_sheet_exports_match (match_id, created_at)
|
|
) ENGINE=InnoDB;
|