CREATE TABLE IF NOT EXISTS `player_licenses` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `player_id` BIGINT UNSIGNED NOT NULL,
    `license_number` VARCHAR(120) NOT NULL,
    `verification_code` VARCHAR(32) NOT NULL,
    `status` ENUM('active', 'suspended', 'revoked') NOT NULL DEFAULT 'active',
    `issued_by_user_id` BIGINT UNSIGNED DEFAULT NULL,
    `issued_at` DATETIME NOT NULL,
    `expires_at` DATE NOT NULL,
    `notes` TEXT 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_player_licenses_player_id` (`player_id`),
    UNIQUE KEY `uq_player_licenses_license_number` (`license_number`),
    UNIQUE KEY `uq_player_licenses_verification_code` (`verification_code`),
    KEY `idx_player_licenses_issued_by_user_id` (`issued_by_user_id`),
    CONSTRAINT `fk_player_licenses_player_id` FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_player_licenses_issued_by_user_id` FOREIGN KEY (`issued_by_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `player_license_checks` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `fixture_id` BIGINT UNSIGNED NOT NULL,
    `player_id` BIGINT UNSIGNED NOT NULL,
    `license_id` BIGINT UNSIGNED NOT NULL,
    `verified_by_user_id` BIGINT UNSIGNED DEFAULT NULL,
    `verified_at` DATETIME NOT NULL,
    `notes` TEXT 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_player_license_checks_fixture_player` (`fixture_id`, `player_id`),
    KEY `idx_player_license_checks_license_id` (`license_id`),
    KEY `idx_player_license_checks_verified_by_user_id` (`verified_by_user_id`),
    CONSTRAINT `fk_player_license_checks_fixture_id` FOREIGN KEY (`fixture_id`) REFERENCES `fixtures` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_player_license_checks_player_id` FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_player_license_checks_license_id` FOREIGN KEY (`license_id`) REFERENCES `player_licenses` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_player_license_checks_verified_by_user_id` FOREIGN KEY (`verified_by_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;