CREATE TABLE IF NOT EXISTS `fixture_kits` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `fixture_id` BIGINT UNSIGNED NOT NULL,
    `club_id` BIGINT UNSIGNED NOT NULL,
    `shirt_color` VARCHAR(60) NOT NULL,
    `shorts_color` VARCHAR(60) NOT NULL,
    `socks_color` VARCHAR(60) NOT NULL,
    `goalkeeper_shirt_color` VARCHAR(60) NOT NULL,
    `goalkeeper_shorts_color` VARCHAR(60) NOT NULL,
    `submitted_by` BIGINT UNSIGNED DEFAULT NULL,
    `submitted_at` DATETIME DEFAULT NULL,
    `confirmed_by` BIGINT UNSIGNED DEFAULT NULL,
    `confirmed_at` DATETIME DEFAULT 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_kits_fixture_club` (`fixture_id`, `club_id`),
    KEY `idx_fixture_kits_club_id` (`club_id`),
    KEY `idx_fixture_kits_submitted_by` (`submitted_by`),
    KEY `idx_fixture_kits_confirmed_by` (`confirmed_by`),
    CONSTRAINT `fk_fixture_kits_fixture_id` FOREIGN KEY (`fixture_id`) REFERENCES `fixtures` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_fixture_kits_club_id` FOREIGN KEY (`club_id`) REFERENCES `clubs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_fixture_kits_submitted_by` FOREIGN KEY (`submitted_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `fk_fixture_kits_confirmed_by` FOREIGN KEY (`confirmed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `news_posts`
    MODIFY COLUMN `category` ENUM('general', 'competition_update', 'fixture_postponement', 'fixture_reschedule', 'transfer_update', 'match_report', 'match_update', 'fixture_kit_confirmation') NOT NULL DEFAULT 'general';