CREATE TABLE IF NOT EXISTS `fixture_formations` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `fixture_id` BIGINT UNSIGNED NOT NULL,
    `club_id` BIGINT UNSIGNED NOT NULL,
    `formation` VARCHAR(20) NOT NULL,
    `tactical_notes` TEXT DEFAULT NULL,
    `submitted_by` BIGINT UNSIGNED DEFAULT NULL,
    `submitted_at` DATETIME 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_formations_fixture_club` (`fixture_id`, `club_id`),
    KEY `idx_fixture_formations_club_id` (`club_id`),
    KEY `idx_fixture_formations_submitted_by` (`submitted_by`),
    CONSTRAINT `fk_fixture_formations_fixture_id` FOREIGN KEY (`fixture_id`) REFERENCES `fixtures` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_fixture_formations_club_id` FOREIGN KEY (`club_id`) REFERENCES `clubs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_fixture_formations_submitted_by` FOREIGN KEY (`submitted_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;