CREATE TABLE IF NOT EXISTS `fixture_formation_players` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `formation_id` BIGINT UNSIGNED NOT NULL,
    `player_id` BIGINT UNSIGNED NOT NULL,
    `squad_role` ENUM('starter', 'substitute') NOT NULL,
    `display_order` TINYINT UNSIGNED NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_fixture_formation_players_role_order` (`formation_id`, `squad_role`, `display_order`),
    UNIQUE KEY `uq_fixture_formation_players_player` (`formation_id`, `player_id`),
    KEY `idx_fixture_formation_players_player_id` (`player_id`),
    CONSTRAINT `fk_fixture_formation_players_formation_id` FOREIGN KEY (`formation_id`) REFERENCES `fixture_formations` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_fixture_formation_players_player_id` FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;