CREATE TABLE IF NOT EXISTS `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 IF NOT EXISTS `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 IF NOT EXISTS `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 IF NOT EXISTS `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 IF NOT EXISTS `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 IF NOT EXISTS `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 IF NOT EXISTS `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') NOT NULL DEFAULT 'general',
    `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`),
    CONSTRAINT `fk_news_posts_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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