-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: May 12, 2026 at 01:15 PM
-- Server version: 10.4.32-MariaDB
-- PHP Version: 8.2.12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `silkhea2_jlfc`
--

-- --------------------------------------------------------

--
-- Table structure for table `audit_logs`
--

CREATE TABLE `audit_logs` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `action` varchar(150) NOT NULL,
  `module` varchar(100) NOT NULL,
  `record_id` bigint(20) UNSIGNED DEFAULT NULL,
  `old_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`old_data`)),
  `new_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`new_data`)),
  `ip_address` varchar(45) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `audit_logs`
--

INSERT INTO `audit_logs` (`id`, `user_id`, `action`, `module`, `record_id`, `old_data`, `new_data`, `ip_address`, `created_at`) VALUES
(1, 1, 'login', 'auth', 1, NULL, '{\"email\":\"admin@jlfc.com\"}', '::1', '2026-05-12 07:53:25'),
(2, 1, 'login', 'auth', 1, NULL, '{\"email\":\"admin@jlfc.com\"}', '127.0.0.1', '2026-05-12 07:56:00'),
(3, 1, 'create_season', 'seasons', 1, NULL, '{\"name\":\"2026 SEASON\",\"start_date\":\"2026-05-12\",\"end_date\":\"2027-12-31\",\"status\":\"active\"}', '127.0.0.1', '2026-05-12 08:18:30'),
(4, 1, 'create_competition', 'competitions', 1, NULL, '{\"season_id\":1,\"name\":\"Unity Cup\",\"sequence_order\":1,\"type\":\"knockout\",\"number_of_teams\":16,\"group_count\":0,\"qualifiers_per_group\":0,\"points_win\":3,\"points_draw\":1,\"points_loss\":0,\"tie_breaker_type\":\"goal_difference\",\"status\":\"active\"}', '127.0.0.1', '2026-05-12 08:19:24'),
(5, 1, 'create_club', 'clubs', 18, NULL, '{\"name\":\"Jalingo Stars\",\"short_name\":\"JSS\",\"founded_year\":2000,\"stadium_name\":\"\",\"contact_person\":\"\",\"phone\":\"\",\"email\":\"\",\"license_status\":\"approved\",\"payment_status\":\"paid\",\"status\":\"active\",\"logo\":null}', '127.0.0.1', '2026-05-12 08:22:59'),
(6, 1, 'update_competition', 'competitions', 1, '{\"id\":1,\"season_id\":1,\"name\":\"Unity Cup\",\"sequence_order\":1,\"type\":\"knockout\",\"number_of_teams\":16,\"group_count\":0,\"qualifiers_per_group\":0,\"points_win\":3,\"points_draw\":1,\"points_loss\":0,\"tie_breaker_type\":\"goal_difference\",\"status\":\"active\",\"created_at\":\"2026-05-12 09:19:24\",\"season_name\":\"2026 SEASON\",\"season_status\":\"active\"}', '{\"season_id\":1,\"name\":\"Unity Cup\",\"sequence_order\":1,\"type\":\"knockout\",\"number_of_teams\":18,\"group_count\":0,\"qualifiers_per_group\":0,\"points_win\":3,\"points_draw\":1,\"points_loss\":0,\"tie_breaker_type\":\"goal_difference\",\"status\":\"active\"}', '127.0.0.1', '2026-05-12 08:23:10'),
(7, 1, 'set_club_license_status', 'clubs', 18, '{\"license_status\":\"approved\"}', '{\"license_status\":\"suspended\"}', '127.0.0.1', '2026-05-12 08:24:51'),
(8, 1, 'set_club_license_status', 'clubs', 15, '{\"license_status\":\"approved\"}', '{\"license_status\":\"suspended\"}', '127.0.0.1', '2026-05-12 08:25:03'),
(9, 1, 'update_competition', 'competitions', 1, '{\"id\":1,\"season_id\":1,\"name\":\"Unity Cup\",\"sequence_order\":1,\"type\":\"knockout\",\"number_of_teams\":18,\"group_count\":0,\"qualifiers_per_group\":0,\"points_win\":3,\"points_draw\":1,\"points_loss\":0,\"tie_breaker_type\":\"goal_difference\",\"status\":\"active\",\"created_at\":\"2026-05-12 09:19:24\",\"season_name\":\"2026 SEASON\",\"season_status\":\"active\"}', '{\"season_id\":1,\"name\":\"Unity Cup\",\"sequence_order\":1,\"type\":\"knockout\",\"number_of_teams\":16,\"group_count\":0,\"qualifiers_per_group\":0,\"points_win\":3,\"points_draw\":1,\"points_loss\":0,\"tie_breaker_type\":\"goal_difference\",\"status\":\"active\"}', '127.0.0.1', '2026-05-12 08:25:16'),
(10, 1, 'generate_fixtures', 'competitions', 1, NULL, '{\"competition_id\":1,\"start_date\":\"2026-05-14\",\"match_time\":\"16:00\",\"days_between_matchdays\":1,\"matchdays_of_week\":[],\"double_round\":false,\"created_count\":8}', '127.0.0.1', '2026-05-12 08:25:41');

-- --------------------------------------------------------

--
-- Table structure for table `awards`
--

CREATE TABLE `awards` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `season_id` bigint(20) UNSIGNED NOT NULL,
  `competition_id` bigint(20) UNSIGNED DEFAULT NULL,
  `award_type` enum('champion','runner_up','top_scorer','best_goalkeeper','best_player','fair_play','best_coach') NOT NULL,
  `club_id` bigint(20) UNSIGNED DEFAULT NULL,
  `player_id` bigint(20) UNSIGNED DEFAULT NULL,
  `title` varchar(150) NOT NULL,
  `description` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `clubs`
--

CREATE TABLE `clubs` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(150) NOT NULL,
  `short_name` varchar(25) NOT NULL,
  `logo` varchar(255) DEFAULT NULL,
  `founded_year` year(4) 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()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `clubs`
--

INSERT INTO `clubs` (`id`, `name`, `short_name`, `logo`, `founded_year`, `stadium_name`, `contact_person`, `phone`, `email`, `license_status`, `payment_status`, `status`, `created_at`, `updated_at`) VALUES
(1, 'Super Stars FC', 'SSFC', NULL, NULL, NULL, NULL, NULL, NULL, 'approved', 'paid', 'active', '2026-05-12 08:07:08', '2026-05-12 08:07:08'),
(2, 'Tiki Taka FC', 'TTFC', NULL, NULL, NULL, NULL, NULL, NULL, 'approved', 'paid', 'active', '2026-05-12 08:07:08', '2026-05-12 08:07:08'),
(3, 'Black Scorpion FC', 'BSFC', NULL, NULL, NULL, NULL, NULL, NULL, 'approved', 'paid', 'active', '2026-05-12 08:07:08', '2026-05-12 08:07:08'),
(4, 'SilkHeart FC', 'SH-FC', NULL, NULL, NULL, NULL, NULL, NULL, 'approved', 'paid', 'active', '2026-05-12 08:07:08', '2026-05-12 08:07:08'),
(5, 'Arsenal Academy', 'AA', NULL, NULL, NULL, NULL, NULL, NULL, 'approved', 'paid', 'active', '2026-05-12 08:07:08', '2026-05-12 08:07:08'),
(6, 'FC Takanabang', 'FCT', NULL, NULL, NULL, NULL, NULL, NULL, 'approved', 'paid', 'active', '2026-05-12 08:07:08', '2026-05-12 08:07:08'),
(7, 'Golden Eaglet', 'GE', NULL, NULL, NULL, NULL, NULL, NULL, 'approved', 'paid', 'active', '2026-05-12 08:07:08', '2026-05-12 08:07:08'),
(8, 'Alianze FC', 'AFC', NULL, NULL, NULL, NULL, NULL, NULL, 'approved', 'paid', 'active', '2026-05-12 08:07:08', '2026-05-12 08:07:08'),
(9, 'Barade FC', 'BFC', NULL, NULL, NULL, NULL, NULL, NULL, 'approved', 'paid', 'active', '2026-05-12 08:07:08', '2026-05-12 08:07:08'),
(10, 'Barade United', 'BU', NULL, NULL, NULL, NULL, NULL, NULL, 'approved', 'paid', 'active', '2026-05-12 08:07:08', '2026-05-12 08:07:08'),
(11, 'Nunkai FC', 'NKFC', NULL, NULL, NULL, NULL, NULL, NULL, 'approved', 'paid', 'active', '2026-05-12 08:07:08', '2026-05-12 08:07:08'),
(12, 'Kona FC', 'KFC', NULL, NULL, NULL, NULL, NULL, NULL, 'approved', 'paid', 'active', '2026-05-12 08:07:08', '2026-05-12 08:07:08'),
(13, 'SB United FC', 'SBUF', NULL, NULL, NULL, NULL, NULL, NULL, 'approved', 'paid', 'active', '2026-05-12 08:07:08', '2026-05-12 08:07:08'),
(14, 'Ade Stars FC', 'ASFC', NULL, NULL, NULL, NULL, NULL, NULL, 'approved', 'paid', 'active', '2026-05-12 08:07:08', '2026-05-12 08:07:08'),
(15, '11 Bullet FC', '11B', NULL, NULL, NULL, NULL, NULL, NULL, 'suspended', 'paid', 'active', '2026-05-12 08:07:08', '2026-05-12 08:25:03'),
(16, 'Jalingo City FC', 'JCFC', NULL, NULL, NULL, NULL, NULL, NULL, 'approved', 'paid', 'active', '2026-05-12 08:07:08', '2026-05-12 08:07:08'),
(17, 'Turaki City FC', 'TCFC', NULL, NULL, NULL, NULL, NULL, NULL, 'approved', 'paid', 'active', '2026-05-12 08:07:08', '2026-05-12 08:07:08'),
(18, 'Jalingo Stars', 'JSS', NULL, '2000', '', '', '', '', 'suspended', 'paid', 'active', '2026-05-12 08:22:59', '2026-05-12 08:24:51');

-- --------------------------------------------------------

--
-- Table structure for table `club_payments`
--

CREATE TABLE `club_payments` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `club_id` bigint(20) UNSIGNED NOT NULL,
  `season_id` bigint(20) 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()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `club_staff`
--

CREATE TABLE `club_staff` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `club_id` bigint(20) 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()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `competitions`
--

CREATE TABLE `competitions` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `season_id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(150) NOT NULL,
  `sequence_order` int(10) UNSIGNED NOT NULL DEFAULT 1,
  `type` enum('league','knockout','group_stage','mixed') NOT NULL DEFAULT 'league',
  `number_of_teams` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `group_count` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `qualifiers_per_group` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `points_win` int(11) NOT NULL DEFAULT 3,
  `points_draw` int(11) NOT NULL DEFAULT 1,
  `points_loss` int(11) 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()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `competitions`
--

INSERT INTO `competitions` (`id`, `season_id`, `name`, `sequence_order`, `type`, `number_of_teams`, `group_count`, `qualifiers_per_group`, `points_win`, `points_draw`, `points_loss`, `tie_breaker_type`, `status`, `created_at`) VALUES
(1, 1, 'Unity Cup', 1, 'knockout', 16, 0, 0, 3, 1, 0, 'goal_difference', 'active', '2026-05-12 08:19:24');

-- --------------------------------------------------------

--
-- Table structure for table `disciplinary_cases`
--

CREATE TABLE `disciplinary_cases` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `fixture_id` bigint(20) UNSIGNED DEFAULT NULL,
  `club_id` bigint(20) UNSIGNED DEFAULT NULL,
  `player_id` bigint(20) 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()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fixtures`
--

CREATE TABLE `fixtures` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `competition_id` bigint(20) UNSIGNED NOT NULL,
  `season_id` bigint(20) UNSIGNED NOT NULL,
  `matchday` int(10) 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(20) UNSIGNED NOT NULL,
  `away_club_id` bigint(20) UNSIGNED NOT NULL,
  `venue_id` bigint(20) 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(20) 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()
) ;

--
-- Dumping data for table `fixtures`
--

INSERT INTO `fixtures` (`id`, `competition_id`, `season_id`, `matchday`, `stage_type`, `group_label`, `knockout_round`, `home_club_id`, `away_club_id`, `venue_id`, `match_date`, `match_time`, `status`, `referee_id`, `assistant_referee_1`, `assistant_referee_2`, `fourth_official`, `match_commissioner`, `created_at`, `updated_at`) VALUES
(1, 1, 1, 1, 'knockout', NULL, 'Round of 16', 14, 8, NULL, '2026-05-14', '16:00:00', 'scheduled', NULL, NULL, NULL, NULL, '', '2026-05-12 08:25:41', '2026-05-12 08:25:41'),
(2, 1, 1, 1, 'knockout', NULL, 'Round of 16', 5, 9, NULL, '2026-05-14', '16:00:00', 'scheduled', NULL, NULL, NULL, NULL, '', '2026-05-12 08:25:41', '2026-05-12 08:25:41'),
(3, 1, 1, 1, 'knockout', NULL, 'Round of 16', 10, 3, NULL, '2026-05-14', '16:00:00', 'scheduled', NULL, NULL, NULL, NULL, '', '2026-05-12 08:25:41', '2026-05-12 08:25:41'),
(4, 1, 1, 1, 'knockout', NULL, 'Round of 16', 6, 7, NULL, '2026-05-14', '16:00:00', 'scheduled', NULL, NULL, NULL, NULL, '', '2026-05-12 08:25:41', '2026-05-12 08:25:41'),
(5, 1, 1, 1, 'knockout', NULL, 'Round of 16', 16, 12, NULL, '2026-05-14', '16:00:00', 'scheduled', NULL, NULL, NULL, NULL, '', '2026-05-12 08:25:41', '2026-05-12 08:25:41'),
(6, 1, 1, 1, 'knockout', NULL, 'Round of 16', 11, 13, NULL, '2026-05-14', '16:00:00', 'scheduled', NULL, NULL, NULL, NULL, '', '2026-05-12 08:25:41', '2026-05-12 08:25:41'),
(7, 1, 1, 1, 'knockout', NULL, 'Round of 16', 4, 1, NULL, '2026-05-14', '16:00:00', 'scheduled', NULL, NULL, NULL, NULL, '', '2026-05-12 08:25:41', '2026-05-12 08:25:41'),
(8, 1, 1, 1, 'knockout', NULL, 'Round of 16', 2, 17, NULL, '2026-05-14', '16:00:00', 'scheduled', NULL, NULL, NULL, NULL, '', '2026-05-12 08:25:41', '2026-05-12 08:25:41');

-- --------------------------------------------------------

--
-- Table structure for table `fixture_formations`
--

CREATE TABLE `fixture_formations` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `fixture_id` bigint(20) UNSIGNED NOT NULL,
  `club_id` bigint(20) UNSIGNED NOT NULL,
  `formation` varchar(20) NOT NULL,
  `tactical_notes` text DEFAULT NULL,
  `submitted_by` bigint(20) 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()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fixture_formation_players`
--

CREATE TABLE `fixture_formation_players` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `formation_id` bigint(20) UNSIGNED NOT NULL,
  `player_id` bigint(20) UNSIGNED NOT NULL,
  `squad_role` enum('starter','substitute') NOT NULL,
  `display_order` tinyint(3) UNSIGNED NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `match_events`
--

CREATE TABLE `match_events` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `fixture_id` bigint(20) UNSIGNED NOT NULL,
  `club_id` bigint(20) UNSIGNED NOT NULL,
  `player_id` bigint(20) UNSIGNED DEFAULT NULL,
  `event_type` enum('goal','assist','yellow_card','red_card','substitution','own_goal','penalty_goal','penalty_miss') NOT NULL,
  `minute` int(10) UNSIGNED NOT NULL,
  `extra_minute` int(10) UNSIGNED DEFAULT NULL,
  `related_player_id` bigint(20) UNSIGNED DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `match_results`
--

CREATE TABLE `match_results` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `fixture_id` bigint(20) UNSIGNED NOT NULL,
  `home_score` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `away_score` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `result_status` enum('draft','submitted','verified','disputed') NOT NULL DEFAULT 'draft',
  `submitted_by` bigint(20) UNSIGNED DEFAULT NULL,
  `verified_by` bigint(20) 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()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `media_gallery`
--

CREATE TABLE `media_gallery` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `title` varchar(200) NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `media_type` enum('image','video') NOT NULL DEFAULT 'image',
  `fixture_id` bigint(20) UNSIGNED DEFAULT NULL,
  `club_id` bigint(20) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `news_posts`
--

CREATE TABLE `news_posts` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `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(20) UNSIGNED DEFAULT NULL,
  `published_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `permissions`
--

CREATE TABLE `permissions` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `permission_key` varchar(100) NOT NULL,
  `permission_name` varchar(150) NOT NULL,
  `module_name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `permissions`
--

INSERT INTO `permissions` (`id`, `permission_key`, `permission_name`, `module_name`) VALUES
(1, 'manage_match_preparation', 'Manage match preparation', 'fixtures'),
(1010, 'view_dashboard', 'View dashboard', 'dashboard'),
(1011, 'manage_users', 'Manage users', 'users'),
(1012, 'manage_roles', 'Manage roles', 'roles'),
(1013, 'manage_permissions', 'Manage permissions', 'roles'),
(1014, 'manage_seasons', 'Manage seasons', 'seasons'),
(1015, 'manage_competitions', 'Manage competitions', 'competitions'),
(1016, 'manage_clubs', 'Manage clubs', 'clubs'),
(1017, 'approve_clubs', 'Approve clubs', 'clubs'),
(1018, 'manage_players', 'Manage players', 'players'),
(1019, 'approve_players', 'Approve players', 'players'),
(1020, 'manage_club_staff', 'Manage club staff', 'staff'),
(1021, 'manage_venues', 'Manage venues', 'venues'),
(1022, 'manage_referees', 'Manage referees', 'referees'),
(1023, 'assign_referees', 'Assign referees', 'referees'),
(1024, 'manage_fixtures', 'Manage fixtures', 'fixtures'),
(1026, 'generate_fixtures', 'Generate fixtures', 'fixtures'),
(1027, 'submit_results', 'Submit results', 'matches'),
(1028, 'verify_results', 'Verify results', 'matches'),
(1029, 'manage_table', 'Manage table', 'table'),
(1030, 'recalculate_table', 'Recalculate table', 'table'),
(1031, 'manage_statistics', 'Manage statistics', 'statistics'),
(1032, 'manage_discipline', 'Manage discipline', 'discipline'),
(1033, 'manage_suspensions', 'Manage suspensions', 'discipline'),
(1034, 'manage_finance', 'Manage finance', 'finance'),
(1035, 'record_payments', 'Record payments', 'finance'),
(1036, 'manage_protests', 'Manage protests', 'protests'),
(1037, 'decide_protests', 'Decide protests', 'protests'),
(1038, 'manage_awards', 'Manage awards', 'awards'),
(1039, 'manage_media', 'Manage media', 'media'),
(1040, 'publish_news', 'Publish news', 'media'),
(1041, 'manage_transfers', 'Manage transfers', 'transfers'),
(1042, 'approve_transfers', 'Approve transfers', 'transfers'),
(1043, 'view_reports', 'View reports', 'reports'),
(1044, 'export_reports', 'Export reports', 'reports'),
(1045, 'manage_settings', 'Manage settings', 'settings');

-- --------------------------------------------------------

--
-- Table structure for table `players`
--

CREATE TABLE `players` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `club_id` bigint(20) 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(10) 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()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `player_documents`
--

CREATE TABLE `player_documents` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `player_id` bigint(20) 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()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `protests`
--

CREATE TABLE `protests` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `fixture_id` bigint(20) UNSIGNED NOT NULL,
  `protesting_club_id` bigint(20) UNSIGNED NOT NULL,
  `against_club_id` bigint(20) 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
) ;

-- --------------------------------------------------------

--
-- Table structure for table `referees`
--

CREATE TABLE `referees` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `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()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `roles`
--

CREATE TABLE `roles` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `roles`
--

INSERT INTO `roles` (`id`, `name`, `description`, `created_at`) VALUES
(1, 'Coach', 'Receives match preparation alerts and submits team formations for assigned club fixtures.', '2026-05-12 07:44:32'),
(254, 'Super Admin', 'Full access to the JLFC platform.', '2026-05-12 07:52:44'),
(256, 'League Admin', 'Manages competitions, clubs, fixtures, results, discipline, protests, awards, media, and reports.', '2026-05-12 07:53:25'),
(257, 'Club Admin', 'Manages own club profile, players, staff, and views fixtures and results.', '2026-05-12 07:53:25'),
(259, 'Match Commissioner', 'Submits match reports and match results.', '2026-05-12 07:53:25'),
(260, 'Referee Coordinator', 'Assigns referees and updates officiating details.', '2026-05-12 07:53:25'),
(261, 'Finance Officer', 'Manages club payments, fines, appeal fees, and financial reports.', '2026-05-12 07:53:25'),
(262, 'Media Officer', 'Publishes news, gallery content, and match media.', '2026-05-12 07:53:25'),
(263, 'Viewer', 'Read-only backend access.', '2026-05-12 07:53:25');

-- --------------------------------------------------------

--
-- Table structure for table `role_permissions`
--

CREATE TABLE `role_permissions` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `role_id` bigint(20) UNSIGNED NOT NULL,
  `permission_id` bigint(20) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `role_permissions`
--

INSERT INTO `role_permissions` (`id`, `role_id`, `permission_id`) VALUES
(1, 1, 1),
(2594, 1, 1010),
(2537, 254, 1),
(2522, 254, 1010),
(2523, 254, 1011),
(2524, 254, 1012),
(2525, 254, 1013),
(2526, 254, 1014),
(2527, 254, 1015),
(2528, 254, 1016),
(2529, 254, 1017),
(2530, 254, 1018),
(2531, 254, 1019),
(2532, 254, 1020),
(2533, 254, 1021),
(2534, 254, 1022),
(2535, 254, 1023),
(2536, 254, 1024),
(2538, 254, 1026),
(2539, 254, 1027),
(2540, 254, 1028),
(2541, 254, 1029),
(2542, 254, 1030),
(2543, 254, 1031),
(2544, 254, 1032),
(2545, 254, 1033),
(2546, 254, 1034),
(2547, 254, 1035),
(2548, 254, 1036),
(2549, 254, 1037),
(2550, 254, 1038),
(2551, 254, 1039),
(2552, 254, 1040),
(2553, 254, 1041),
(2554, 254, 1042),
(2555, 254, 1043),
(2556, 254, 1044),
(2557, 254, 1045),
(2570, 256, 1),
(2558, 256, 1010),
(2559, 256, 1014),
(2560, 256, 1015),
(2561, 256, 1016),
(2562, 256, 1017),
(2563, 256, 1018),
(2564, 256, 1019),
(2565, 256, 1020),
(2566, 256, 1021),
(2567, 256, 1022),
(2568, 256, 1023),
(2569, 256, 1024),
(2571, 256, 1026),
(2572, 256, 1027),
(2573, 256, 1028),
(2574, 256, 1029),
(2575, 256, 1030),
(2576, 256, 1031),
(2577, 256, 1032),
(2578, 256, 1033),
(2579, 256, 1036),
(2580, 256, 1037),
(2581, 256, 1038),
(2582, 256, 1039),
(2583, 256, 1040),
(2584, 256, 1041),
(2585, 256, 1042),
(2586, 256, 1043),
(2587, 256, 1044),
(2593, 257, 1),
(2588, 257, 1010),
(2589, 257, 1016),
(2590, 257, 1018),
(2591, 257, 1020),
(2592, 257, 1041),
(2596, 259, 1010),
(2597, 259, 1027),
(2598, 260, 1010),
(2599, 260, 1022),
(2600, 260, 1023),
(2601, 260, 1024),
(2602, 261, 1010),
(2603, 261, 1034),
(2604, 261, 1035),
(2605, 261, 1043),
(2606, 261, 1044),
(2607, 262, 1010),
(2608, 262, 1039),
(2609, 262, 1040),
(2610, 263, 1010),
(2611, 263, 1043);

-- --------------------------------------------------------

--
-- Table structure for table `seasons`
--

CREATE TABLE `seasons` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `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()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `seasons`
--

INSERT INTO `seasons` (`id`, `name`, `start_date`, `end_date`, `status`, `paused_at`, `created_at`) VALUES
(1, '2026 SEASON', '2026-05-12', '2027-12-31', 'active', NULL, '2026-05-12 08:18:30');

-- --------------------------------------------------------

--
-- Table structure for table `suspensions`
--

CREATE TABLE `suspensions` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `player_id` bigint(20) UNSIGNED NOT NULL,
  `club_id` bigint(20) UNSIGNED NOT NULL,
  `reason` varchar(255) NOT NULL,
  `start_fixture_id` bigint(20) UNSIGNED DEFAULT NULL,
  `number_of_matches` int(10) UNSIGNED NOT NULL DEFAULT 1,
  `matches_served` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `status` enum('active','served','cancelled') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `system_settings`
--

CREATE TABLE `system_settings` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `setting_key` varchar(120) NOT NULL,
  `setting_value` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `system_settings`
--

INSERT INTO `system_settings` (`id`, `setting_key`, `setting_value`) VALUES
(1, 'league_name', 'JLFC - Jalingo Local Football Council'),
(2, 'league_short_name', 'JLFC'),
(3, 'organization_name', 'Jalingo Local Football Council'),
(4, 'contact_email', 'admin@jlfc.com'),
(5, 'contact_phone', '+2340000000000'),
(6, 'primary_color', 'dark green'),
(7, 'accent_color', 'gold'),
(15, 'season_mode', 'single'),
(16, 'points_win', '3'),
(17, 'points_draw', '1'),
(18, 'points_loss', '0'),
(19, 'yellow_card_suspension_threshold', '5'),
(20, 'red_card_suspension_default', '1'),
(21, 'protest_deadline_hours', '48'),
(22, 'require_payment_before_approval', '0'),
(23, 'enable_public_registration', '0'),
(24, 'enable_multiple_active_seasons', '0');

-- --------------------------------------------------------

--
-- Table structure for table `transfers`
--

CREATE TABLE `transfers` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `player_id` bigint(20) UNSIGNED NOT NULL,
  `selling_club_id` bigint(20) UNSIGNED NOT NULL,
  `buying_club_id` bigint(20) UNSIGNED NOT NULL,
  `initiated_by_user_id` bigint(20) UNSIGNED NOT NULL,
  `window_id` bigint(20) 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(10) 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()
) ;

-- --------------------------------------------------------

--
-- Table structure for table `transfer_approvals`
--

CREATE TABLE `transfer_approvals` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `transfer_id` bigint(20) 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(20) UNSIGNED DEFAULT NULL,
  `acted_at` datetime DEFAULT NULL,
  `notes` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `transfer_notifications`
--

CREATE TABLE `transfer_notifications` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `transfer_id` bigint(20) UNSIGNED NOT NULL,
  `recipient_user_id` bigint(20) 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()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `transfer_offers`
--

CREATE TABLE `transfer_offers` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `transfer_id` bigint(20) UNSIGNED NOT NULL,
  `offered_by_club_id` bigint(20) UNSIGNED NOT NULL,
  `offered_by_user_id` bigint(20) UNSIGNED NOT NULL,
  `offered_fee` decimal(12,2) DEFAULT NULL,
  `contract_years` int(10) 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()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `transfer_timeline_events`
--

CREATE TABLE `transfer_timeline_events` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `transfer_id` bigint(20) 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(20) UNSIGNED DEFAULT NULL,
  `created_by_user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `transfer_windows`
--

CREATE TABLE `transfer_windows` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `season_id` bigint(20) 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()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `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(20) UNSIGNED NOT NULL,
  `club_id` bigint(20) 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()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `full_name`, `email`, `phone`, `password_hash`, `role_id`, `club_id`, `status`, `last_login`, `created_at`, `updated_at`) VALUES
(1, 'JLFC Super Administrator', 'admin@jlfc.com', '+2340000000000', '$2y$10$Faw7mDLBEj/6DeeT2dGqyusUgWF8uqjsEocvWrTMc7fUyIEbUc2Ma', 254, NULL, 'active', '2026-05-12 08:56:00', '2026-05-12 07:52:44', '2026-05-12 07:56:00');

-- --------------------------------------------------------

--
-- Table structure for table `venues`
--

CREATE TABLE `venues` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(150) NOT NULL,
  `location` varchar(255) NOT NULL,
  `capacity` int(10) UNSIGNED DEFAULT NULL,
  `pitch_type` varchar(100) DEFAULT NULL,
  `status` varchar(50) NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `audit_logs`
--
ALTER TABLE `audit_logs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_audit_logs_user_id` (`user_id`),
  ADD KEY `idx_audit_logs_module` (`module`);

--
-- Indexes for table `awards`
--
ALTER TABLE `awards`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_awards_season_id` (`season_id`),
  ADD KEY `fk_awards_competition_id` (`competition_id`),
  ADD KEY `fk_awards_club_id` (`club_id`),
  ADD KEY `fk_awards_player_id` (`player_id`);

--
-- Indexes for table `clubs`
--
ALTER TABLE `clubs`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_clubs_name` (`name`),
  ADD UNIQUE KEY `uq_clubs_short_name` (`short_name`);

--
-- Indexes for table `club_payments`
--
ALTER TABLE `club_payments`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_club_payments_reference` (`payment_reference`),
  ADD KEY `fk_club_payments_club_id` (`club_id`),
  ADD KEY `fk_club_payments_season_id` (`season_id`);

--
-- Indexes for table `club_staff`
--
ALTER TABLE `club_staff`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_club_staff_club_id` (`club_id`);

--
-- Indexes for table `competitions`
--
ALTER TABLE `competitions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_competitions_season_name` (`season_id`,`name`),
  ADD UNIQUE KEY `uq_competitions_season_sequence_order` (`season_id`,`sequence_order`);

--
-- Indexes for table `disciplinary_cases`
--
ALTER TABLE `disciplinary_cases`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_disciplinary_cases_fixture_id` (`fixture_id`),
  ADD KEY `fk_disciplinary_cases_club_id` (`club_id`),
  ADD KEY `fk_disciplinary_cases_player_id` (`player_id`);

--
-- Indexes for table `fixtures`
--
ALTER TABLE `fixtures`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_fixtures_competition_pair_round` (`competition_id`,`matchday`,`home_club_id`,`away_club_id`),
  ADD KEY `idx_fixtures_season_id` (`season_id`),
  ADD KEY `idx_fixtures_home_club_id` (`home_club_id`),
  ADD KEY `idx_fixtures_away_club_id` (`away_club_id`),
  ADD KEY `idx_fixtures_venue_id` (`venue_id`),
  ADD KEY `idx_fixtures_referee_id` (`referee_id`);

--
-- Indexes for table `fixture_formations`
--
ALTER TABLE `fixture_formations`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_fixture_formations_fixture_club` (`fixture_id`,`club_id`),
  ADD KEY `idx_fixture_formations_club_id` (`club_id`),
  ADD KEY `idx_fixture_formations_submitted_by` (`submitted_by`);

--
-- Indexes for table `fixture_formation_players`
--
ALTER TABLE `fixture_formation_players`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_fixture_formation_players_role_order` (`formation_id`,`squad_role`,`display_order`),
  ADD UNIQUE KEY `uq_fixture_formation_players_player` (`formation_id`,`player_id`),
  ADD KEY `idx_fixture_formation_players_player_id` (`player_id`);

--
-- Indexes for table `match_events`
--
ALTER TABLE `match_events`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_match_events_fixture_id` (`fixture_id`),
  ADD KEY `idx_match_events_club_id` (`club_id`),
  ADD KEY `idx_match_events_player_id` (`player_id`),
  ADD KEY `idx_match_events_related_player_id` (`related_player_id`);

--
-- Indexes for table `match_results`
--
ALTER TABLE `match_results`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_match_results_fixture_id` (`fixture_id`),
  ADD KEY `idx_match_results_submitted_by` (`submitted_by`),
  ADD KEY `idx_match_results_verified_by` (`verified_by`);

--
-- Indexes for table `media_gallery`
--
ALTER TABLE `media_gallery`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_media_gallery_fixture_id` (`fixture_id`),
  ADD KEY `fk_media_gallery_club_id` (`club_id`);

--
-- Indexes for table `news_posts`
--
ALTER TABLE `news_posts`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_news_posts_slug` (`slug`),
  ADD KEY `fk_news_posts_created_by` (`created_by`);

--
-- Indexes for table `permissions`
--
ALTER TABLE `permissions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_permissions_key` (`permission_key`);

--
-- Indexes for table `players`
--
ALTER TABLE `players`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_players_registration_number` (`registration_number`),
  ADD UNIQUE KEY `uq_players_nin` (`nin`),
  ADD KEY `idx_players_club_id` (`club_id`);

--
-- Indexes for table `player_documents`
--
ALTER TABLE `player_documents`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_player_documents_player_id` (`player_id`);

--
-- Indexes for table `protests`
--
ALTER TABLE `protests`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_protests_fixture_id` (`fixture_id`),
  ADD KEY `fk_protests_protesting_club_id` (`protesting_club_id`),
  ADD KEY `fk_protests_against_club_id` (`against_club_id`);

--
-- Indexes for table `referees`
--
ALTER TABLE `referees`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_referees_email` (`email`);

--
-- Indexes for table `roles`
--
ALTER TABLE `roles`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_roles_name` (`name`);

--
-- Indexes for table `role_permissions`
--
ALTER TABLE `role_permissions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_role_permissions` (`role_id`,`permission_id`),
  ADD KEY `idx_role_permissions_permission_id` (`permission_id`);

--
-- Indexes for table `seasons`
--
ALTER TABLE `seasons`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_seasons_name` (`name`);

--
-- Indexes for table `suspensions`
--
ALTER TABLE `suspensions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_suspensions_player_id` (`player_id`),
  ADD KEY `idx_suspensions_club_id` (`club_id`),
  ADD KEY `idx_suspensions_start_fixture_id` (`start_fixture_id`);

--
-- Indexes for table `system_settings`
--
ALTER TABLE `system_settings`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_system_settings_key` (`setting_key`);

--
-- Indexes for table `transfers`
--
ALTER TABLE `transfers`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_transfers_player_id` (`player_id`),
  ADD KEY `idx_transfers_selling_club_id` (`selling_club_id`),
  ADD KEY `idx_transfers_buying_club_id` (`buying_club_id`),
  ADD KEY `idx_transfers_initiated_by_user_id` (`initiated_by_user_id`),
  ADD KEY `idx_transfers_window_id` (`window_id`);

--
-- Indexes for table `transfer_approvals`
--
ALTER TABLE `transfer_approvals`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_transfer_approval_scope` (`transfer_id`,`approval_type`),
  ADD KEY `idx_transfer_approvals_acted_by_user_id` (`acted_by_user_id`);

--
-- Indexes for table `transfer_notifications`
--
ALTER TABLE `transfer_notifications`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_transfer_notifications_transfer_id` (`transfer_id`),
  ADD KEY `idx_transfer_notifications_recipient_user_id` (`recipient_user_id`);

--
-- Indexes for table `transfer_offers`
--
ALTER TABLE `transfer_offers`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_transfer_offers_transfer_id` (`transfer_id`),
  ADD KEY `idx_transfer_offers_offered_by_club_id` (`offered_by_club_id`),
  ADD KEY `idx_transfer_offers_offered_by_user_id` (`offered_by_user_id`);

--
-- Indexes for table `transfer_timeline_events`
--
ALTER TABLE `transfer_timeline_events`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_transfer_timeline_events_transfer_id` (`transfer_id`),
  ADD KEY `idx_transfer_timeline_events_news_post_id` (`news_post_id`),
  ADD KEY `idx_transfer_timeline_events_created_by_user_id` (`created_by_user_id`);

--
-- Indexes for table `transfer_windows`
--
ALTER TABLE `transfer_windows`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_transfer_windows_season_id` (`season_id`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_users_email` (`email`),
  ADD KEY `idx_users_role_id` (`role_id`),
  ADD KEY `idx_users_club_id` (`club_id`);

--
-- Indexes for table `venues`
--
ALTER TABLE `venues`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_venues_name_location` (`name`,`location`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `audit_logs`
--
ALTER TABLE `audit_logs`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;

--
-- AUTO_INCREMENT for table `awards`
--
ALTER TABLE `awards`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `clubs`
--
ALTER TABLE `clubs`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=19;

--
-- AUTO_INCREMENT for table `club_payments`
--
ALTER TABLE `club_payments`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `club_staff`
--
ALTER TABLE `club_staff`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `competitions`
--
ALTER TABLE `competitions`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `disciplinary_cases`
--
ALTER TABLE `disciplinary_cases`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `fixtures`
--
ALTER TABLE `fixtures`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `fixture_formations`
--
ALTER TABLE `fixture_formations`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `fixture_formation_players`
--
ALTER TABLE `fixture_formation_players`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `match_events`
--
ALTER TABLE `match_events`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `match_results`
--
ALTER TABLE `match_results`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `media_gallery`
--
ALTER TABLE `media_gallery`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `news_posts`
--
ALTER TABLE `news_posts`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `permissions`
--
ALTER TABLE `permissions`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1046;

--
-- AUTO_INCREMENT for table `players`
--
ALTER TABLE `players`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `player_documents`
--
ALTER TABLE `player_documents`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `protests`
--
ALTER TABLE `protests`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `referees`
--
ALTER TABLE `referees`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `roles`
--
ALTER TABLE `roles`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=264;

--
-- AUTO_INCREMENT for table `role_permissions`
--
ALTER TABLE `role_permissions`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2612;

--
-- AUTO_INCREMENT for table `seasons`
--
ALTER TABLE `seasons`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `suspensions`
--
ALTER TABLE `suspensions`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `system_settings`
--
ALTER TABLE `system_settings`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=25;

--
-- AUTO_INCREMENT for table `transfers`
--
ALTER TABLE `transfers`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `transfer_approvals`
--
ALTER TABLE `transfer_approvals`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `transfer_notifications`
--
ALTER TABLE `transfer_notifications`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `transfer_offers`
--
ALTER TABLE `transfer_offers`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `transfer_timeline_events`
--
ALTER TABLE `transfer_timeline_events`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `transfer_windows`
--
ALTER TABLE `transfer_windows`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `venues`
--
ALTER TABLE `venues`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `audit_logs`
--
ALTER TABLE `audit_logs`
  ADD CONSTRAINT `fk_audit_logs_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

--
-- Constraints for table `awards`
--
ALTER TABLE `awards`
  ADD CONSTRAINT `fk_awards_club_id` FOREIGN KEY (`club_id`) REFERENCES `clubs` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_awards_competition_id` FOREIGN KEY (`competition_id`) REFERENCES `competitions` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_awards_player_id` FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_awards_season_id` FOREIGN KEY (`season_id`) REFERENCES `seasons` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `club_payments`
--
ALTER TABLE `club_payments`
  ADD CONSTRAINT `fk_club_payments_club_id` FOREIGN KEY (`club_id`) REFERENCES `clubs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_club_payments_season_id` FOREIGN KEY (`season_id`) REFERENCES `seasons` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `club_staff`
--
ALTER TABLE `club_staff`
  ADD CONSTRAINT `fk_club_staff_club_id` FOREIGN KEY (`club_id`) REFERENCES `clubs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `competitions`
--
ALTER TABLE `competitions`
  ADD CONSTRAINT `fk_competitions_season_id` FOREIGN KEY (`season_id`) REFERENCES `seasons` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `disciplinary_cases`
--
ALTER TABLE `disciplinary_cases`
  ADD CONSTRAINT `fk_disciplinary_cases_club_id` FOREIGN KEY (`club_id`) REFERENCES `clubs` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_disciplinary_cases_fixture_id` FOREIGN KEY (`fixture_id`) REFERENCES `fixtures` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_disciplinary_cases_player_id` FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

--
-- Constraints for table `fixtures`
--
ALTER TABLE `fixtures`
  ADD CONSTRAINT `fk_fixtures_away_club_id` FOREIGN KEY (`away_club_id`) REFERENCES `clubs` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_fixtures_competition_id` FOREIGN KEY (`competition_id`) REFERENCES `competitions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_fixtures_home_club_id` FOREIGN KEY (`home_club_id`) REFERENCES `clubs` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_fixtures_referee_id` FOREIGN KEY (`referee_id`) REFERENCES `referees` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_fixtures_season_id` FOREIGN KEY (`season_id`) REFERENCES `seasons` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_fixtures_venue_id` FOREIGN KEY (`venue_id`) REFERENCES `venues` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

--
-- Constraints for table `fixture_formations`
--
ALTER TABLE `fixture_formations`
  ADD CONSTRAINT `fk_fixture_formations_club_id` FOREIGN KEY (`club_id`) REFERENCES `clubs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_fixture_formations_fixture_id` FOREIGN KEY (`fixture_id`) REFERENCES `fixtures` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_fixture_formations_submitted_by` FOREIGN KEY (`submitted_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

--
-- Constraints for table `fixture_formation_players`
--
ALTER TABLE `fixture_formation_players`
  ADD CONSTRAINT `fk_fixture_formation_players_formation_id` FOREIGN KEY (`formation_id`) REFERENCES `fixture_formations` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_fixture_formation_players_player_id` FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `match_events`
--
ALTER TABLE `match_events`
  ADD CONSTRAINT `fk_match_events_club_id` FOREIGN KEY (`club_id`) REFERENCES `clubs` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_match_events_fixture_id` FOREIGN KEY (`fixture_id`) REFERENCES `fixtures` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_match_events_player_id` FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_match_events_related_player_id` FOREIGN KEY (`related_player_id`) REFERENCES `players` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

--
-- Constraints for table `match_results`
--
ALTER TABLE `match_results`
  ADD CONSTRAINT `fk_match_results_fixture_id` FOREIGN KEY (`fixture_id`) REFERENCES `fixtures` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_match_results_submitted_by` FOREIGN KEY (`submitted_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_match_results_verified_by` FOREIGN KEY (`verified_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

--
-- Constraints for table `media_gallery`
--
ALTER TABLE `media_gallery`
  ADD CONSTRAINT `fk_media_gallery_club_id` FOREIGN KEY (`club_id`) REFERENCES `clubs` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_media_gallery_fixture_id` FOREIGN KEY (`fixture_id`) REFERENCES `fixtures` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

--
-- Constraints for table `news_posts`
--
ALTER TABLE `news_posts`
  ADD CONSTRAINT `fk_news_posts_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

--
-- Constraints for table `players`
--
ALTER TABLE `players`
  ADD CONSTRAINT `fk_players_club_id` FOREIGN KEY (`club_id`) REFERENCES `clubs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `player_documents`
--
ALTER TABLE `player_documents`
  ADD CONSTRAINT `fk_player_documents_player_id` FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `protests`
--
ALTER TABLE `protests`
  ADD CONSTRAINT `fk_protests_against_club_id` FOREIGN KEY (`against_club_id`) REFERENCES `clubs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_protests_fixture_id` FOREIGN KEY (`fixture_id`) REFERENCES `fixtures` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_protests_protesting_club_id` FOREIGN KEY (`protesting_club_id`) REFERENCES `clubs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `role_permissions`
--
ALTER TABLE `role_permissions`
  ADD CONSTRAINT `fk_role_permissions_permission_id` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_role_permissions_role_id` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `suspensions`
--
ALTER TABLE `suspensions`
  ADD CONSTRAINT `fk_suspensions_club_id` FOREIGN KEY (`club_id`) REFERENCES `clubs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_suspensions_player_id` FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_suspensions_start_fixture_id` FOREIGN KEY (`start_fixture_id`) REFERENCES `fixtures` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

--
-- Constraints for table `transfers`
--
ALTER TABLE `transfers`
  ADD CONSTRAINT `fk_transfers_buying_club_id` FOREIGN KEY (`buying_club_id`) REFERENCES `clubs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_transfers_initiated_by_user_id` FOREIGN KEY (`initiated_by_user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_transfers_player_id` FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_transfers_selling_club_id` FOREIGN KEY (`selling_club_id`) REFERENCES `clubs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_transfers_window_id` FOREIGN KEY (`window_id`) REFERENCES `transfer_windows` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

--
-- Constraints for table `transfer_approvals`
--
ALTER TABLE `transfer_approvals`
  ADD CONSTRAINT `fk_transfer_approvals_acted_by_user_id` FOREIGN KEY (`acted_by_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_transfer_approvals_transfer_id` FOREIGN KEY (`transfer_id`) REFERENCES `transfers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `transfer_notifications`
--
ALTER TABLE `transfer_notifications`
  ADD CONSTRAINT `fk_transfer_notifications_recipient_user_id` FOREIGN KEY (`recipient_user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_transfer_notifications_transfer_id` FOREIGN KEY (`transfer_id`) REFERENCES `transfers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `transfer_offers`
--
ALTER TABLE `transfer_offers`
  ADD CONSTRAINT `fk_transfer_offers_offered_by_club_id` FOREIGN KEY (`offered_by_club_id`) REFERENCES `clubs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_transfer_offers_offered_by_user_id` FOREIGN KEY (`offered_by_user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_transfer_offers_transfer_id` FOREIGN KEY (`transfer_id`) REFERENCES `transfers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `transfer_timeline_events`
--
ALTER TABLE `transfer_timeline_events`
  ADD 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,
  ADD CONSTRAINT `fk_transfer_timeline_events_news_post_id` FOREIGN KEY (`news_post_id`) REFERENCES `news_posts` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_transfer_timeline_events_transfer_id` FOREIGN KEY (`transfer_id`) REFERENCES `transfers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `transfer_windows`
--
ALTER TABLE `transfer_windows`
  ADD CONSTRAINT `fk_transfer_windows_season_id` FOREIGN KEY (`season_id`) REFERENCES `seasons` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

--
-- Constraints for table `users`
--
ALTER TABLE `users`
  ADD CONSTRAINT `fk_users_club_id` FOREIGN KEY (`club_id`) REFERENCES `clubs` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_users_role_id` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON UPDATE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
