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;