CREATE DATABASE IF NOT EXISTS `silkhea2_jlfc`
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE `silkhea2_jlfc`;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `media_gallery`;
DROP TABLE IF EXISTS `news_posts`;
DROP TABLE IF EXISTS `transfer_notifications`;
DROP TABLE IF EXISTS `transfer_timeline_events`;
DROP TABLE IF EXISTS `transfer_approvals`;
DROP TABLE IF EXISTS `transfer_offers`;
DROP TABLE IF EXISTS `transfers`;
DROP TABLE IF EXISTS `transfer_windows`;
DROP TABLE IF EXISTS `awards`;
DROP TABLE IF EXISTS `club_payments`;
DROP TABLE IF EXISTS `protests`;
DROP TABLE IF EXISTS `disciplinary_cases`;
DROP TABLE IF EXISTS `suspensions`;
DROP TABLE IF EXISTS `player_statistics`;
DROP TABLE IF EXISTS `league_table`;
DROP TABLE IF EXISTS `match_events`;
DROP TABLE IF EXISTS `match_results`;
DROP TABLE IF EXISTS `fixtures`;
DROP TABLE IF EXISTS `player_documents`;
DROP TABLE IF EXISTS `players`;
DROP TABLE IF EXISTS `club_staff`;
DROP TABLE IF EXISTS `referees`;
DROP TABLE IF EXISTS `venues`;
DROP TABLE IF EXISTS `competitions`;
DROP TABLE IF EXISTS `seasons`;
DROP TABLE IF EXISTS `role_permissions`;
DROP TABLE IF EXISTS `users`;
DROP TABLE IF EXISTS `permissions`;
DROP TABLE IF EXISTS `roles`;
DROP TABLE IF EXISTS `clubs`;
DROP TABLE IF EXISTS `audit_logs`;
DROP TABLE IF EXISTS `system_settings`;

CREATE TABLE `roles` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `description` VARCHAR(255) DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_roles_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `permissions` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `permission_key` VARCHAR(100) NOT NULL,
    `permission_name` VARCHAR(150) NOT NULL,
    `module_name` VARCHAR(100) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_permissions_key` (`permission_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clubs` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(150) NOT NULL,
    `short_name` VARCHAR(25) NOT NULL,
    `logo` VARCHAR(255) DEFAULT NULL,
    `founded_year` YEAR DEFAULT NULL,
    `stadium_name` VARCHAR(150) DEFAULT NULL,
    `contact_person` VARCHAR(150) DEFAULT NULL,
    `phone` VARCHAR(30) DEFAULT NULL,
    `email` VARCHAR(150) DEFAULT NULL,
    `license_status` ENUM('pending', 'approved', 'rejected', 'suspended') NOT NULL DEFAULT 'pending',
    `payment_status` ENUM('unpaid', 'partial', 'paid') NOT NULL DEFAULT 'unpaid',
    `status` ENUM('active', 'inactive', 'suspended') NOT NULL DEFAULT 'active',
    `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_clubs_name` (`name`),
    UNIQUE KEY `uq_clubs_short_name` (`short_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `users` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `full_name` VARCHAR(150) NOT NULL,
    `email` VARCHAR(150) NOT NULL,
    `phone` VARCHAR(30) DEFAULT NULL,
    `password_hash` VARCHAR(255) NOT NULL,
    `role_id` BIGINT UNSIGNED NOT NULL,
    `club_id` BIGINT UNSIGNED DEFAULT NULL,
    `status` ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    `last_login` 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_users_email` (`email`),
    KEY `idx_users_role_id` (`role_id`),
    KEY `idx_users_club_id` (`club_id`),
    CONSTRAINT `fk_users_role_id` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON UPDATE CASCADE,
    CONSTRAINT `fk_users_club_id` FOREIGN KEY (`club_id`) REFERENCES `clubs` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `role_permissions` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `role_id` BIGINT UNSIGNED NOT NULL,
    `permission_id` BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_role_permissions` (`role_id`, `permission_id`),
    KEY `idx_role_permissions_permission_id` (`permission_id`),
    CONSTRAINT `fk_role_permissions_role_id` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_role_permissions_permission_id` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `seasons` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `start_date` DATE NOT NULL,
    `end_date` DATE NOT NULL,
    `status` ENUM('upcoming', 'active', 'paused', 'completed', 'cancelled') NOT NULL DEFAULT 'upcoming',
    `paused_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_seasons_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `competitions` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `season_id` BIGINT UNSIGNED NOT NULL,
    `name` VARCHAR(150) NOT NULL,
    `sequence_order` INT UNSIGNED NOT NULL DEFAULT 1,
    `type` ENUM('league', 'knockout', 'group_stage', 'mixed') NOT NULL DEFAULT 'league',
    `number_of_teams` INT UNSIGNED NOT NULL DEFAULT 0,
    `group_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `qualifiers_per_group` INT UNSIGNED NOT NULL DEFAULT 0,
    `points_win` INT NOT NULL DEFAULT 3,
    `points_draw` INT NOT NULL DEFAULT 1,
    `points_loss` INT NOT NULL DEFAULT 0,
    `tie_breaker_type` ENUM('goal_difference', 'head_to_head') NOT NULL DEFAULT 'goal_difference',
    `status` ENUM('draft', 'open', 'active', 'paused', 'completed', 'cancelled') NOT NULL DEFAULT 'draft',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_competitions_season_name` (`season_id`, `name`),
    UNIQUE KEY `uq_competitions_season_sequence_order` (`season_id`, `sequence_order`),
    CONSTRAINT `fk_competitions_season_id` FOREIGN KEY (`season_id`) REFERENCES `seasons` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `venues` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(150) NOT NULL,
    `location` VARCHAR(255) NOT NULL,
    `capacity` INT UNSIGNED DEFAULT NULL,
    `pitch_type` VARCHAR(100) DEFAULT NULL,
    `status` VARCHAR(50) NOT NULL DEFAULT 'active',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_venues_name_location` (`name`, `location`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `referees` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `full_name` VARCHAR(150) NOT NULL,
    `phone` VARCHAR(30) DEFAULT NULL,
    `email` VARCHAR(150) DEFAULT NULL,
    `level` VARCHAR(100) DEFAULT NULL,
    `status` VARCHAR(50) NOT NULL DEFAULT 'active',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_referees_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `club_staff` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `club_id` BIGINT UNSIGNED NOT NULL,
    `full_name` VARCHAR(150) NOT NULL,
    `role` ENUM('coach', 'assistant_coach', 'doctor', 'physiotherapist', 'manager', 'media_officer') NOT NULL,
    `phone` VARCHAR(30) DEFAULT NULL,
    `email` VARCHAR(150) DEFAULT NULL,
    `photo` VARCHAR(255) DEFAULT NULL,
    `status` VARCHAR(50) NOT NULL DEFAULT 'active',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_club_staff_club_id` (`club_id`),
    CONSTRAINT `fk_club_staff_club_id` FOREIGN KEY (`club_id`) REFERENCES `clubs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `players` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `club_id` BIGINT UNSIGNED NOT NULL,
    `first_name` VARCHAR(100) NOT NULL,
    `last_name` VARCHAR(100) NOT NULL,
    `date_of_birth` DATE NOT NULL,
    `nationality` VARCHAR(100) DEFAULT NULL,
    `position` ENUM('GK', 'CB', 'LB', 'RB', 'DM', 'CM', 'AM', 'LW', 'RW', 'ST') NOT NULL,
    `jersey_number` INT UNSIGNED DEFAULT NULL,
    `photo` VARCHAR(255) DEFAULT NULL,
    `nin` VARCHAR(20) DEFAULT NULL,
    `registration_number` VARCHAR(100) NOT NULL,
    `medical_status` ENUM('pending', 'fit', 'unfit') NOT NULL DEFAULT 'pending',
    `eligibility_status` ENUM('pending', 'eligible', 'ineligible', 'suspended') NOT NULL DEFAULT 'pending',
    `transfer_status` ENUM('active', 'free_agent', 'transferred', 'released') NOT NULL DEFAULT 'active',
    `status` ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    `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_players_nin` (`nin`),
    UNIQUE KEY `uq_players_registration_number` (`registration_number`),
    KEY `idx_players_club_id` (`club_id`),
    CONSTRAINT `fk_players_club_id` FOREIGN KEY (`club_id`) REFERENCES `clubs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `player_documents` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `player_id` BIGINT UNSIGNED NOT NULL,
    `document_type` ENUM('id_card', 'passport', 'medical_certificate', 'clearance_letter', 'contract', 'other') NOT NULL DEFAULT 'other',
    `title` VARCHAR(150) NOT NULL,
    `file_path` VARCHAR(255) NOT NULL,
    `uploaded_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_player_documents_player_id` (`player_id`),
    CONSTRAINT `fk_player_documents_player_id` FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `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 `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;

CREATE TABLE `fixtures` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `competition_id` BIGINT UNSIGNED NOT NULL,
    `season_id` BIGINT UNSIGNED NOT NULL,
    `matchday` INT UNSIGNED NOT NULL,
    `stage_type` ENUM('league', 'group_stage', 'knockout') NOT NULL DEFAULT 'league',
    `group_label` VARCHAR(50) DEFAULT NULL,
    `knockout_round` VARCHAR(50) DEFAULT NULL,
    `home_club_id` BIGINT UNSIGNED NOT NULL,
    `away_club_id` BIGINT UNSIGNED NOT NULL,
    `venue_id` BIGINT UNSIGNED DEFAULT NULL,
    `match_date` DATE DEFAULT NULL,
    `match_time` TIME DEFAULT NULL,
    `status` ENUM('scheduled', 'postponed', 'live', 'completed', 'cancelled') NOT NULL DEFAULT 'scheduled',
    `referee_id` BIGINT UNSIGNED DEFAULT NULL,
    `assistant_referee_1` VARCHAR(150) DEFAULT NULL,
    `assistant_referee_2` VARCHAR(150) DEFAULT NULL,
    `fourth_official` VARCHAR(150) DEFAULT NULL,
    `match_commissioner` VARCHAR(150) 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_fixtures_competition_pair_round` (`competition_id`, `matchday`, `home_club_id`, `away_club_id`),
    KEY `idx_fixtures_season_id` (`season_id`),
    KEY `idx_fixtures_home_club_id` (`home_club_id`),
    KEY `idx_fixtures_away_club_id` (`away_club_id`),
    KEY `idx_fixtures_venue_id` (`venue_id`),
    KEY `idx_fixtures_referee_id` (`referee_id`),
    CONSTRAINT `chk_fixture_clubs_different` CHECK (`home_club_id` <> `away_club_id`),
    CONSTRAINT `fk_fixtures_competition_id` FOREIGN KEY (`competition_id`) REFERENCES `competitions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_fixtures_season_id` FOREIGN KEY (`season_id`) REFERENCES `seasons` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_fixtures_home_club_id` FOREIGN KEY (`home_club_id`) REFERENCES `clubs` (`id`) ON UPDATE CASCADE,
    CONSTRAINT `fk_fixtures_away_club_id` FOREIGN KEY (`away_club_id`) REFERENCES `clubs` (`id`) ON UPDATE CASCADE,
    CONSTRAINT `fk_fixtures_venue_id` FOREIGN KEY (`venue_id`) REFERENCES `venues` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `fk_fixtures_referee_id` FOREIGN KEY (`referee_id`) REFERENCES `referees` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `match_results` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `fixture_id` BIGINT UNSIGNED NOT NULL,
    `home_score` INT UNSIGNED NOT NULL DEFAULT 0,
    `away_score` INT UNSIGNED NOT NULL DEFAULT 0,
    `result_status` ENUM('draft', 'submitted', 'verified', 'disputed') NOT NULL DEFAULT 'draft',
    `submitted_by` BIGINT UNSIGNED DEFAULT NULL,
    `verified_by` BIGINT UNSIGNED DEFAULT NULL,
    `submitted_at` DATETIME DEFAULT NULL,
    `verified_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_match_results_fixture_id` (`fixture_id`),
    KEY `idx_match_results_submitted_by` (`submitted_by`),
    KEY `idx_match_results_verified_by` (`verified_by`),
    CONSTRAINT `fk_match_results_fixture_id` FOREIGN KEY (`fixture_id`) REFERENCES `fixtures` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_match_results_submitted_by` FOREIGN KEY (`submitted_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `fk_match_results_verified_by` FOREIGN KEY (`verified_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `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;

CREATE TABLE `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;

CREATE TABLE `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;

CREATE TABLE `match_events` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `fixture_id` BIGINT UNSIGNED NOT NULL,
    `club_id` BIGINT UNSIGNED NOT NULL,
    `player_id` BIGINT UNSIGNED DEFAULT NULL,
    `event_type` ENUM('goal', 'assist', 'yellow_card', 'red_card', 'substitution', 'own_goal', 'penalty_goal', 'penalty_miss') NOT NULL,
    `minute` INT UNSIGNED NOT NULL,
    `extra_minute` INT UNSIGNED DEFAULT NULL,
    `related_player_id` BIGINT UNSIGNED DEFAULT NULL,
    `notes` TEXT DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_match_events_fixture_id` (`fixture_id`),
    KEY `idx_match_events_club_id` (`club_id`),
    KEY `idx_match_events_player_id` (`player_id`),
    KEY `idx_match_events_related_player_id` (`related_player_id`),
    CONSTRAINT `fk_match_events_fixture_id` FOREIGN KEY (`fixture_id`) REFERENCES `fixtures` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_match_events_club_id` FOREIGN KEY (`club_id`) REFERENCES `clubs` (`id`) ON UPDATE CASCADE,
    CONSTRAINT `fk_match_events_player_id` FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `fk_match_events_related_player_id` FOREIGN KEY (`related_player_id`) REFERENCES `players` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `league_table` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `competition_id` BIGINT UNSIGNED NOT NULL,
    `season_id` BIGINT UNSIGNED NOT NULL,
    `club_id` BIGINT UNSIGNED NOT NULL,
    `played` INT UNSIGNED NOT NULL DEFAULT 0,
    `won` INT UNSIGNED NOT NULL DEFAULT 0,
    `drawn` INT UNSIGNED NOT NULL DEFAULT 0,
    `lost` INT UNSIGNED NOT NULL DEFAULT 0,
    `goals_for` INT UNSIGNED NOT NULL DEFAULT 0,
    `goals_against` INT UNSIGNED NOT NULL DEFAULT 0,
    `goal_difference` INT NOT NULL DEFAULT 0,
    `points` INT NOT NULL DEFAULT 0,
    `position` INT UNSIGNED NOT NULL DEFAULT 0,
    `form` VARCHAR(20) DEFAULT NULL,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_league_table_competition_club` (`competition_id`, `season_id`, `club_id`),
    CONSTRAINT `fk_league_table_competition_id` FOREIGN KEY (`competition_id`) REFERENCES `competitions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_league_table_season_id` FOREIGN KEY (`season_id`) REFERENCES `seasons` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_league_table_club_id` FOREIGN KEY (`club_id`) REFERENCES `clubs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `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;

CREATE TABLE `suspensions` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `player_id` BIGINT UNSIGNED NOT NULL,
    `club_id` BIGINT UNSIGNED NOT NULL,
    `reason` VARCHAR(255) NOT NULL,
    `start_fixture_id` BIGINT UNSIGNED DEFAULT NULL,
    `number_of_matches` INT UNSIGNED NOT NULL DEFAULT 1,
    `matches_served` INT UNSIGNED NOT NULL DEFAULT 0,
    `status` ENUM('active', 'served', 'cancelled') NOT NULL DEFAULT 'active',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_suspensions_player_id` (`player_id`),
    KEY `idx_suspensions_club_id` (`club_id`),
    KEY `idx_suspensions_start_fixture_id` (`start_fixture_id`),
    CONSTRAINT `fk_suspensions_player_id` FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_suspensions_club_id` FOREIGN KEY (`club_id`) REFERENCES `clubs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_suspensions_start_fixture_id` FOREIGN KEY (`start_fixture_id`) REFERENCES `fixtures` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `disciplinary_cases` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `fixture_id` BIGINT UNSIGNED DEFAULT NULL,
    `club_id` BIGINT UNSIGNED DEFAULT NULL,
    `player_id` BIGINT UNSIGNED DEFAULT NULL,
    `case_type` VARCHAR(100) NOT NULL,
    `description` TEXT NOT NULL,
    `decision` TEXT DEFAULT NULL,
    `fine_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `status` ENUM('pending', 'reviewed', 'closed') NOT NULL DEFAULT 'pending',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_disciplinary_cases_fixture_id` FOREIGN KEY (`fixture_id`) REFERENCES `fixtures` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `fk_disciplinary_cases_club_id` FOREIGN KEY (`club_id`) REFERENCES `clubs` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `fk_disciplinary_cases_player_id` FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `protests` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `fixture_id` BIGINT UNSIGNED NOT NULL,
    `protesting_club_id` BIGINT UNSIGNED NOT NULL,
    `against_club_id` BIGINT UNSIGNED NOT NULL,
    `title` VARCHAR(200) NOT NULL,
    `complaint` TEXT NOT NULL,
    `evidence_file` VARCHAR(255) DEFAULT NULL,
    `status` ENUM('submitted', 'under_review', 'accepted', 'rejected', 'resolved') NOT NULL DEFAULT 'submitted',
    `decision` TEXT DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `resolved_at` DATETIME DEFAULT NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_protests_fixture_id` FOREIGN KEY (`fixture_id`) REFERENCES `fixtures` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_protests_protesting_club_id` FOREIGN KEY (`protesting_club_id`) REFERENCES `clubs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_protests_against_club_id` FOREIGN KEY (`against_club_id`) REFERENCES `clubs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `chk_protests_clubs_different` CHECK (`protesting_club_id` <> `against_club_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `club_payments` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `club_id` BIGINT UNSIGNED NOT NULL,
    `season_id` BIGINT UNSIGNED NOT NULL,
    `payment_type` ENUM('registration', 'fine', 'appeal_fee', 'other') NOT NULL,
    `amount` DECIMAL(12,2) NOT NULL,
    `amount_paid` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `payment_status` ENUM('unpaid', 'partial', 'paid') NOT NULL DEFAULT 'unpaid',
    `payment_reference` VARCHAR(100) NOT NULL,
    `payment_date` DATE DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_club_payments_reference` (`payment_reference`),
    CONSTRAINT `fk_club_payments_club_id` FOREIGN KEY (`club_id`) REFERENCES `clubs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_club_payments_season_id` FOREIGN KEY (`season_id`) REFERENCES `seasons` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `transfer_windows` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `season_id` BIGINT UNSIGNED DEFAULT NULL,
    `name` VARCHAR(150) NOT NULL,
    `start_date` DATE NOT NULL,
    `end_date` DATE NOT NULL,
    `status` ENUM('draft', 'open', 'closed') NOT NULL DEFAULT 'draft',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_transfer_windows_season_id` (`season_id`),
    CONSTRAINT `fk_transfer_windows_season_id` FOREIGN KEY (`season_id`) REFERENCES `seasons` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `transfers` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `player_id` BIGINT UNSIGNED NOT NULL,
    `selling_club_id` BIGINT UNSIGNED NOT NULL,
    `buying_club_id` BIGINT UNSIGNED NOT NULL,
    `initiated_by_user_id` BIGINT UNSIGNED NOT NULL,
    `window_id` BIGINT UNSIGNED DEFAULT NULL,
    `current_stage` ENUM('market', 'interest_registered', 'club_review', 'negotiation', 'verbal_agreement', 'awaiting_club_approval', 'awaiting_league_approval', 'approved', 'medical', 'completed', 'rejected', 'cancelled') NOT NULL DEFAULT 'market',
    `market_visibility` ENUM('open', 'restricted', 'closed') NOT NULL DEFAULT 'open',
    `interest_message` TEXT DEFAULT NULL,
    `proposed_release_date` DATE DEFAULT NULL,
    `proposed_fee` DECIMAL(12,2) DEFAULT NULL,
    `agreed_fee` DECIMAL(12,2) DEFAULT NULL,
    `contract_years` INT UNSIGNED DEFAULT NULL,
    `medical_status` ENUM('not_started', 'pending', 'completed', 'failed') NOT NULL DEFAULT 'not_started',
    `effective_date` DATE DEFAULT NULL,
    `completed_at` DATETIME DEFAULT NULL,
    `rejection_reason` 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`),
    KEY `idx_transfers_player_id` (`player_id`),
    KEY `idx_transfers_selling_club_id` (`selling_club_id`),
    KEY `idx_transfers_buying_club_id` (`buying_club_id`),
    KEY `idx_transfers_initiated_by_user_id` (`initiated_by_user_id`),
    KEY `idx_transfers_window_id` (`window_id`),
    CONSTRAINT `chk_transfers_clubs_different` CHECK (`selling_club_id` <> `buying_club_id`),
    CONSTRAINT `fk_transfers_player_id` FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_transfers_selling_club_id` FOREIGN KEY (`selling_club_id`) REFERENCES `clubs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_transfers_buying_club_id` FOREIGN KEY (`buying_club_id`) REFERENCES `clubs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_transfers_initiated_by_user_id` FOREIGN KEY (`initiated_by_user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_transfers_window_id` FOREIGN KEY (`window_id`) REFERENCES `transfer_windows` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `transfer_offers` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `transfer_id` BIGINT UNSIGNED NOT NULL,
    `offered_by_club_id` BIGINT UNSIGNED NOT NULL,
    `offered_by_user_id` BIGINT UNSIGNED NOT NULL,
    `offered_fee` DECIMAL(12,2) DEFAULT NULL,
    `contract_years` INT UNSIGNED DEFAULT NULL,
    `release_date` DATE DEFAULT NULL,
    `status` ENUM('proposed', 'accepted', 'rejected', 'withdrawn') NOT NULL DEFAULT 'proposed',
    `notes` TEXT DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_transfer_offers_transfer_id` (`transfer_id`),
    KEY `idx_transfer_offers_offered_by_club_id` (`offered_by_club_id`),
    KEY `idx_transfer_offers_offered_by_user_id` (`offered_by_user_id`),
    CONSTRAINT `fk_transfer_offers_transfer_id` FOREIGN KEY (`transfer_id`) REFERENCES `transfers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_transfer_offers_offered_by_club_id` FOREIGN KEY (`offered_by_club_id`) REFERENCES `clubs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_transfer_offers_offered_by_user_id` FOREIGN KEY (`offered_by_user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `transfer_approvals` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `transfer_id` BIGINT UNSIGNED NOT NULL,
    `approval_type` ENUM('selling_club', 'buying_club', 'league') NOT NULL,
    `status` ENUM('pending', 'accepted', 'rejected') NOT NULL DEFAULT 'pending',
    `acted_by_user_id` BIGINT UNSIGNED DEFAULT NULL,
    `acted_at` DATETIME DEFAULT NULL,
    `notes` TEXT DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_transfer_approval_scope` (`transfer_id`, `approval_type`),
    KEY `idx_transfer_approvals_acted_by_user_id` (`acted_by_user_id`),
    CONSTRAINT `fk_transfer_approvals_transfer_id` FOREIGN KEY (`transfer_id`) REFERENCES `transfers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_transfer_approvals_acted_by_user_id` FOREIGN KEY (`acted_by_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `transfer_timeline_events` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `transfer_id` BIGINT UNSIGNED NOT NULL,
    `event_type` VARCHAR(100) NOT NULL,
    `headline` VARCHAR(220) NOT NULL,
    `body` TEXT DEFAULT NULL,
    `publish_news` TINYINT(1) NOT NULL DEFAULT 0,
    `news_post_id` BIGINT UNSIGNED DEFAULT NULL,
    `created_by_user_id` BIGINT UNSIGNED DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_transfer_timeline_events_transfer_id` (`transfer_id`),
    KEY `idx_transfer_timeline_events_news_post_id` (`news_post_id`),
    KEY `idx_transfer_timeline_events_created_by_user_id` (`created_by_user_id`),
    CONSTRAINT `fk_transfer_timeline_events_transfer_id` FOREIGN KEY (`transfer_id`) REFERENCES `transfers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_transfer_timeline_events_news_post_id` FOREIGN KEY (`news_post_id`) REFERENCES `news_posts` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `fk_transfer_timeline_events_created_by_user_id` FOREIGN KEY (`created_by_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `transfer_notifications` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `transfer_id` BIGINT UNSIGNED NOT NULL,
    `recipient_user_id` BIGINT UNSIGNED NOT NULL,
    `notification_type` VARCHAR(100) NOT NULL,
    `title` VARCHAR(200) NOT NULL,
    `body` TEXT DEFAULT NULL,
    `is_read` TINYINT(1) NOT NULL DEFAULT 0,
    `read_at` DATETIME DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_transfer_notifications_transfer_id` (`transfer_id`),
    KEY `idx_transfer_notifications_recipient_user_id` (`recipient_user_id`),
    CONSTRAINT `fk_transfer_notifications_transfer_id` FOREIGN KEY (`transfer_id`) REFERENCES `transfers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_transfer_notifications_recipient_user_id` FOREIGN KEY (`recipient_user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `awards` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `season_id` BIGINT UNSIGNED NOT NULL,
    `competition_id` BIGINT UNSIGNED DEFAULT NULL,
    `award_type` ENUM('champion', 'runner_up', 'top_scorer', 'best_goalkeeper', 'best_player', 'fair_play', 'best_coach') NOT NULL,
    `club_id` BIGINT UNSIGNED DEFAULT NULL,
    `player_id` BIGINT UNSIGNED DEFAULT NULL,
    `title` VARCHAR(150) NOT NULL,
    `description` TEXT DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_awards_season_id` FOREIGN KEY (`season_id`) REFERENCES `seasons` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_awards_competition_id` FOREIGN KEY (`competition_id`) REFERENCES `competitions` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `fk_awards_club_id` FOREIGN KEY (`club_id`) REFERENCES `clubs` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `fk_awards_player_id` FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `news_posts` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(200) NOT NULL,
    `slug` VARCHAR(220) NOT NULL,
    `category` ENUM('general', 'competition_update', 'fixture_postponement', 'fixture_reschedule', 'transfer_update', 'match_report', 'match_update', 'fixture_kit_confirmation') NOT NULL DEFAULT 'general',
    `fixture_id` BIGINT UNSIGNED DEFAULT NULL,
    `body` LONGTEXT NOT NULL,
    `featured_image` VARCHAR(255) DEFAULT NULL,
    `status` ENUM('draft', 'published') NOT NULL DEFAULT 'draft',
    `created_by` BIGINT UNSIGNED DEFAULT NULL,
    `published_at` DATETIME DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_news_posts_slug` (`slug`),
    KEY `idx_news_posts_fixture_id` (`fixture_id`),
    CONSTRAINT `fk_news_posts_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `fk_news_posts_fixture_id` FOREIGN KEY (`fixture_id`) REFERENCES `fixtures` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `media_gallery` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(200) NOT NULL,
    `file_path` VARCHAR(255) NOT NULL,
    `media_type` ENUM('image', 'video') NOT NULL DEFAULT 'image',
    `fixture_id` BIGINT UNSIGNED DEFAULT NULL,
    `club_id` BIGINT UNSIGNED DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_media_gallery_fixture_id` FOREIGN KEY (`fixture_id`) REFERENCES `fixtures` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `fk_media_gallery_club_id` FOREIGN KEY (`club_id`) REFERENCES `clubs` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `audit_logs` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` BIGINT UNSIGNED DEFAULT NULL,
    `action` VARCHAR(150) NOT NULL,
    `module` VARCHAR(100) NOT NULL,
    `record_id` BIGINT UNSIGNED DEFAULT NULL,
    `old_data` JSON DEFAULT NULL,
    `new_data` JSON DEFAULT NULL,
    `ip_address` VARCHAR(45) DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_audit_logs_user_id` (`user_id`),
    KEY `idx_audit_logs_module` (`module`),
    CONSTRAINT `fk_audit_logs_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `system_settings` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `setting_key` VARCHAR(120) NOT NULL,
    `setting_value` TEXT DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_system_settings_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
