CREATE TABLE IF NOT EXISTS `player_statistics` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `season_id` BIGINT UNSIGNED NOT NULL,
    `competition_id` BIGINT UNSIGNED NOT NULL,
    `player_id` BIGINT UNSIGNED NOT NULL,
    `club_id` BIGINT UNSIGNED NOT NULL,
    `appearances` INT UNSIGNED NOT NULL DEFAULT 0,
    `goals` INT UNSIGNED NOT NULL DEFAULT 0,
    `assists` INT UNSIGNED NOT NULL DEFAULT 0,
    `yellow_cards` INT UNSIGNED NOT NULL DEFAULT 0,
    `red_cards` INT UNSIGNED NOT NULL DEFAULT 0,
    `clean_sheets` INT UNSIGNED NOT NULL DEFAULT 0,
    `own_goals` INT UNSIGNED NOT NULL DEFAULT 0,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_player_statistics_scope` (`season_id`, `competition_id`, `player_id`, `club_id`),
    CONSTRAINT `fk_player_statistics_season_id` FOREIGN KEY (`season_id`) REFERENCES `seasons` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_player_statistics_competition_id` FOREIGN KEY (`competition_id`) REFERENCES `competitions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_player_statistics_player_id` FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_player_statistics_club_id` FOREIGN KEY (`club_id`) REFERENCES `clubs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;