-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `eydp_full_system`
--
CREATE DATABASE IF NOT EXISTS `eydp_full_system` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE `eydp_full_system`;

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

--
-- Table structure for table `applicants`
--

CREATE TABLE `applicants` (
  `id` bigint UNSIGNED NOT NULL,
  `user_id` bigint UNSIGNED DEFAULT NULL,
  `full_name_ar` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `full_name_en` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `national_id` varchar(14) COLLATE utf8mb4_unicode_ci NOT NULL,
  `passport_number` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `applicant_code` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` enum('submitted','reviewed','rejected','accepted') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'submitted',
  `dob` date NOT NULL,
  `gender` enum('male','female') COLLATE utf8mb4_unicode_ci NOT NULL,
  `military_status` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `mobile` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `whatsapp` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `address` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `district` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `governorate_id` bigint UNSIGNED NOT NULL,
  `birth_place` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `degree` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `major` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `college` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `university` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_studying` tinyint(1) NOT NULL,
  `study_year` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `grad_year` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `current_job` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `employer` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `experience_public` longtext COLLATE utf8mb4_unicode_ci,
  `experience_ministry` longtext COLLATE utf8mb4_unicode_ci,
  `experience_other_orgs` longtext COLLATE utf8mb4_unicode_ci,
  `visited_countries` longtext COLLATE utf8mb4_unicode_ci,
  `center_memberships` longtext COLLATE utf8mb4_unicode_ci,
  `club_memberships` longtext COLLATE utf8mb4_unicode_ci,
  `sports` longtext COLLATE utf8mb4_unicode_ci,
  `skills_hobbies` longtext COLLATE utf8mb4_unicode_ci,
  `motivation` longtext COLLATE utf8mb4_unicode_ci,
  `strengths` longtext COLLATE utf8mb4_unicode_ci,
  `weaknesses` longtext COLLATE utf8mb4_unicode_ci,
  `facebook` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `instagram` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `linkedin` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `height` int NOT NULL,
  `weight` int NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `applicant_degrees`
--

CREATE TABLE `applicant_degrees` (
  `id` bigint UNSIGNED NOT NULL,
  `applicant_id` bigint UNSIGNED NOT NULL,
  `degree_title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `institution` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `year` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `applicant_documents`
--

CREATE TABLE `applicant_documents` (
  `id` bigint UNSIGNED NOT NULL,
  `applicant_id` bigint UNSIGNED NOT NULL,
  `type` enum('national_id','cv','photo_casual','photo_official') COLLATE utf8mb4_unicode_ci NOT NULL,
  `file_path` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `applicant_languages`
--

CREATE TABLE `applicant_languages` (
  `id` bigint UNSIGNED NOT NULL,
  `applicant_id` bigint UNSIGNED NOT NULL,
  `language_id` bigint UNSIGNED NOT NULL,
  `proficiency` enum('excellent','very_good','good','fair') COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `applicant_nationalities`
--

CREATE TABLE `applicant_nationalities` (
  `id` bigint UNSIGNED NOT NULL,
  `applicant_id` bigint UNSIGNED NOT NULL,
  `country_id` bigint UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `applicant_submissions`
--

CREATE TABLE `applicant_submissions` (
  `id` bigint UNSIGNED NOT NULL,
  `applicant_id` bigint UNSIGNED DEFAULT NULL,
  `ip_address` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `browser_fingerprint` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_agent` text COLLATE utf8mb4_unicode_ci,
  `device_type` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `os` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `browser` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `categories`
--

CREATE TABLE `categories` (
  `id` bigint UNSIGNED NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `category_user`
--

CREATE TABLE `category_user` (
  `id` bigint UNSIGNED NOT NULL,
  `category_id` bigint UNSIGNED NOT NULL,
  `user_id` bigint UNSIGNED NOT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `countries`
--

CREATE TABLE `countries` (
  `id` bigint UNSIGNED NOT NULL,
  `name_en` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name_ar` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `iso_code` varchar(5) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `countries`
--

INSERT INTO `countries` (`id`, `name_en`, `name_ar`, `iso_code`, `created_at`, `updated_at`, `deleted_at`) VALUES
(1, 'Afghanistan', 'أفغانستان', 'AF', NULL, NULL, NULL),
(2, 'Albania', 'ألبانيا', 'AL', NULL, NULL, NULL),
(3, 'Algeria', 'الجزائر', 'DZ', NULL, NULL, NULL),
(4, 'Andorra', 'أندورا', 'AD', NULL, NULL, NULL),
(5, 'Angola', 'أنغولا', 'AO', NULL, NULL, NULL),
(6, 'Antigua and Barbuda', 'أنتيغوا وباربودا', 'AG', NULL, NULL, NULL),
(7, 'Argentina', 'الأرجنتين', 'AR', NULL, NULL, NULL),
(8, 'Armenia', 'أرمينيا', 'AM', NULL, NULL, NULL),
(9, 'Australia', 'أستراليا', 'AU', NULL, NULL, NULL),
(10, 'Austria', 'النمسا', 'AT', NULL, NULL, NULL),
(11, 'Azerbaijan', 'أذربيجان', 'AZ', NULL, NULL, NULL),
(12, 'Bahamas', 'جزر البهاما', 'BS', NULL, NULL, NULL),
(13, 'Bahrain', 'البحرين', 'BH', NULL, NULL, NULL),
(14, 'Bangladesh', 'بنغلاديش', 'BD', NULL, NULL, NULL),
(15, 'Barbados', 'باربادوس', 'BB', NULL, NULL, NULL),
(16, 'Belarus', 'بيلاروسيا', 'BY', NULL, NULL, NULL),
(17, 'Belgium', 'بلجيكا', 'BE', NULL, NULL, NULL),
(18, 'Belize', 'بليز', 'BZ', NULL, NULL, NULL),
(19, 'Benin', 'بنين', 'BJ', NULL, NULL, NULL),
(20, 'Bhutan', 'بوتان', 'BT', NULL, NULL, NULL),
(21, 'Bolivia', 'بوليفيا', 'BO', NULL, NULL, NULL),
(22, 'Bosnia and Herzegovina', 'البوسنة والهرسك', 'BA', NULL, NULL, NULL),
(23, 'Botswana', 'بوتسوانا', 'BW', NULL, NULL, NULL),
(24, 'Brazil', 'البرازيل', 'BR', NULL, NULL, NULL),
(25, 'Brunei', 'بروناي', 'BN', NULL, NULL, NULL),
(26, 'Bulgaria', 'بلغاريا', 'BG', NULL, NULL, NULL),
(27, 'Burkina Faso', 'بوركينا فاسو', 'BF', NULL, NULL, NULL),
(28, 'Burundi', 'بوروندي', 'BI', NULL, NULL, NULL),
(29, 'Cabo Verde', 'الرأس الأخضر', 'CV', NULL, NULL, NULL),
(30, 'Cambodia', 'كمبوديا', 'KH', NULL, NULL, NULL),
(31, 'Cameroon', 'الكاميرون', 'CM', NULL, NULL, NULL),
(32, 'Canada', 'كندا', 'CA', NULL, NULL, NULL),
(33, 'Central African Republic', 'جمهورية أفريقيا الوسطى', 'CF', NULL, NULL, NULL),
(34, 'Chad', 'تشاد', 'TD', NULL, NULL, NULL),
(35, 'Chile', 'تشيلي', 'CL', NULL, NULL, NULL),
(36, 'China', 'الصين', 'CN', NULL, NULL, NULL),
(37, 'Colombia', 'كولومبيا', 'CO', NULL, NULL, NULL),
(38, 'Comoros', 'جزر القمر', 'KM', NULL, NULL, NULL),
(39, 'Congo (Republic)', 'الكونغو', 'CG', NULL, NULL, NULL),
(40, 'Congo (DRC)', 'جمهورية الكونغو الديمقراطية', 'CD', NULL, NULL, NULL),
(41, 'Costa Rica', 'كوستاريكا', 'CR', NULL, NULL, NULL),
(42, 'Croatia', 'كرواتيا', 'HR', NULL, NULL, NULL),
(43, 'Cuba', 'كوبا', 'CU', NULL, NULL, NULL),
(44, 'Cyprus', 'قبرص', 'CY', NULL, NULL, NULL),
(45, 'Czech Republic', 'التشيك', 'CZ', NULL, NULL, NULL),
(46, 'Denmark', 'الدنمارك', 'DK', NULL, NULL, NULL),
(47, 'Djibouti', 'جيبوتي', 'DJ', NULL, NULL, NULL),
(48, 'Dominica', 'دومينيكا', 'DM', NULL, NULL, NULL),
(49, 'Dominican Republic', 'جمهورية الدومينيكان', 'DO', NULL, NULL, NULL),
(50, 'Ecuador', 'الإكوادور', 'EC', NULL, NULL, NULL),
(51, 'Egypt', 'مصر', 'EG', NULL, NULL, NULL),
(52, 'El Salvador', 'السلفادور', 'SV', NULL, NULL, NULL),
(53, 'Equatorial Guinea', 'غينيا الاستوائية', 'GQ', NULL, NULL, NULL),
(54, 'Eritrea', 'إريتريا', 'ER', NULL, NULL, NULL),
(55, 'Estonia', 'إستونيا', 'EE', NULL, NULL, NULL),
(56, 'Eswatini', 'إسواتيني', 'SZ', NULL, NULL, NULL),
(57, 'Ethiopia', 'إثيوبيا', 'ET', NULL, NULL, NULL),
(58, 'Fiji', 'فيجي', 'FJ', NULL, NULL, NULL),
(59, 'Finland', 'فنلندا', 'FI', NULL, NULL, NULL),
(60, 'France', 'فرنسا', 'FR', NULL, NULL, NULL),
(61, 'Gabon', 'الغابون', 'GA', NULL, NULL, NULL),
(62, 'Gambia', 'غامبيا', 'GM', NULL, NULL, NULL),
(63, 'Georgia', 'جورجيا', 'GE', NULL, NULL, NULL),
(64, 'Germany', 'ألمانيا', 'DE', NULL, NULL, NULL),
(65, 'Ghana', 'غانا', 'GH', NULL, NULL, NULL),
(66, 'Greece', 'اليونان', 'GR', NULL, NULL, NULL),
(67, 'Grenada', 'غرينادا', 'GD', NULL, NULL, NULL),
(68, 'Guatemala', 'غواتيمالا', 'GT', NULL, NULL, NULL),
(69, 'Guinea', 'غينيا', 'GN', NULL, NULL, NULL),
(70, 'Guinea-Bissau', 'غينيا بيساو', 'GW', NULL, NULL, NULL),
(71, 'Guyana', 'غيانا', 'GY', NULL, NULL, NULL),
(72, 'Haiti', 'هايتي', 'HT', NULL, NULL, NULL),
(73, 'Honduras', 'هندوراس', 'HN', NULL, NULL, NULL),
(74, 'Hungary', 'المجر', 'HU', NULL, NULL, NULL),
(75, 'Iceland', 'آيسلندا', 'IS', NULL, NULL, NULL),
(76, 'India', 'الهند', 'IN', NULL, NULL, NULL),
(77, 'Indonesia', 'إندونيسيا', 'ID', NULL, NULL, NULL),
(78, 'Iran', 'إيران', 'IR', NULL, NULL, NULL),
(79, 'Iraq', 'العراق', 'IQ', NULL, NULL, NULL),
(80, 'Ireland', 'أيرلندا', 'IE', NULL, NULL, NULL),
(81, 'Italy', 'إيطاليا', 'IT', NULL, NULL, NULL),
(82, 'Jamaica', 'جامايكا', 'JM', NULL, NULL, NULL),
(83, 'Japan', 'اليابان', 'JP', NULL, NULL, NULL),
(84, 'Jordan', 'الأردن', 'JO', NULL, NULL, NULL),
(85, 'Kazakhstan', 'كازاخستان', 'KZ', NULL, NULL, NULL),
(86, 'Kenya', 'كينيا', 'KE', NULL, NULL, NULL),
(87, 'Kiribati', 'كيريباتي', 'KI', NULL, NULL, NULL),
(88, 'Kuwait', 'الكويت', 'KW', NULL, NULL, NULL),
(89, 'Kyrgyzstan', 'قرغيزستان', 'KG', NULL, NULL, NULL),
(90, 'Laos', 'لاوس', 'LA', NULL, NULL, NULL),
(91, 'Latvia', 'لاتفيا', 'LV', NULL, NULL, NULL),
(92, 'Lebanon', 'لبنان', 'LB', NULL, NULL, NULL),
(93, 'Lesotho', 'ليسوتو', 'LS', NULL, NULL, NULL),
(94, 'Liberia', 'ليبيريا', 'LR', NULL, NULL, NULL),
(95, 'Libya', 'ليبيا', 'LY', NULL, NULL, NULL),
(96, 'Liechtenstein', 'ليختنشتاين', 'LI', NULL, NULL, NULL),
(97, 'Lithuania', 'ليتوانيا', 'LT', NULL, NULL, NULL),
(98, 'Luxembourg', 'لوكسمبورغ', 'LU', NULL, NULL, NULL),
(99, 'Madagascar', 'مدغشقر', 'MG', NULL, NULL, NULL),
(100, 'Malawi', 'مالاوي', 'MW', NULL, NULL, NULL),
(101, 'Malaysia', 'ماليزيا', 'MY', NULL, NULL, NULL),
(102, 'Maldives', 'المالديف', 'MV', NULL, NULL, NULL),
(103, 'Mali', 'مالي', 'ML', NULL, NULL, NULL),
(104, 'Malta', 'مالطا', 'MT', NULL, NULL, NULL),
(105, 'Marshall Islands', 'جزر مارشال', 'MH', NULL, NULL, NULL),
(106, 'Mauritania', 'موريتانيا', 'MR', NULL, NULL, NULL),
(107, 'Mauritius', 'موريشيوس', 'MU', NULL, NULL, NULL),
(108, 'Mexico', 'المكسيك', 'MX', NULL, NULL, NULL),
(109, 'Micronesia', 'ميكرونيزيا', 'FM', NULL, NULL, NULL),
(110, 'Moldova', 'مولدوفا', 'MD', NULL, NULL, NULL),
(111, 'Monaco', 'موناكو', 'MC', NULL, NULL, NULL),
(112, 'Mongolia', 'منغوليا', 'MN', NULL, NULL, NULL),
(113, 'Montenegro', 'الجبل الأسود', 'ME', NULL, NULL, NULL),
(114, 'Morocco', 'المغرب', 'MA', NULL, NULL, NULL),
(115, 'Mozambique', 'موزمبيق', 'MZ', NULL, NULL, NULL),
(116, 'Myanmar', 'ميانمار', 'MM', NULL, NULL, NULL),
(117, 'Namibia', 'ناميبيا', 'NA', NULL, NULL, NULL),
(118, 'Nauru', 'ناورو', 'NR', NULL, NULL, NULL),
(119, 'Nepal', 'نيبال', 'NP', NULL, NULL, NULL),
(120, 'Netherlands', 'هولندا', 'NL', NULL, NULL, NULL),
(121, 'New Zealand', 'نيوزيلندا', 'NZ', NULL, NULL, NULL),
(122, 'Nicaragua', 'نيكاراغوا', 'NI', NULL, NULL, NULL),
(123, 'Niger', 'النيجر', 'NE', NULL, NULL, NULL),
(124, 'Nigeria', 'نيجيريا', 'NG', NULL, NULL, NULL),
(125, 'North Korea', 'كوريا الشمالية', 'KP', NULL, NULL, NULL),
(126, 'North Macedonia', 'مقدونيا الشمالية', 'MK', NULL, NULL, NULL),
(127, 'Norway', 'النرويج', 'NO', NULL, NULL, NULL),
(128, 'Oman', 'عمان', 'OM', NULL, NULL, NULL),
(129, 'Pakistan', 'باكستان', 'PK', NULL, NULL, NULL),
(130, 'Palau', 'بالاو', 'PW', NULL, NULL, NULL),
(131, 'Panama', 'بنما', 'PA', NULL, NULL, NULL),
(132, 'Papua New Guinea', 'بابوا غينيا الجديدة', 'PG', NULL, NULL, NULL),
(133, 'Paraguay', 'باراغواي', 'PY', NULL, NULL, NULL),
(134, 'Peru', 'بيرو', 'PE', NULL, NULL, NULL),
(135, 'Philippines', 'الفلبين', 'PH', NULL, NULL, NULL),
(136, 'Poland', 'بولندا', 'PL', NULL, NULL, NULL),
(137, 'Portugal', 'البرتغال', 'PT', NULL, NULL, NULL),
(138, 'Qatar', 'قطر', 'QA', NULL, NULL, NULL),
(139, 'Romania', 'رومانيا', 'RO', NULL, NULL, NULL),
(140, 'Russia', 'روسيا', 'RU', NULL, NULL, NULL),
(141, 'Rwanda', 'رواندا', 'RW', NULL, NULL, NULL),
(142, 'Saint Kitts and Nevis', 'سانت كيتس ونيفيس', 'KN', NULL, NULL, NULL),
(143, 'Saint Lucia', 'سانت لوسيا', 'LC', NULL, NULL, NULL),
(144, 'Saint Vincent and the Grenadines', 'سانت فنسنت وجزر غرينادين', 'VC', NULL, NULL, NULL),
(145, 'Samoa', 'ساموا', 'WS', NULL, NULL, NULL),
(146, 'San Marino', 'سان مارينو', 'SM', NULL, NULL, NULL),
(147, 'Sao Tome and Principe', 'ساو تومي وبرينسيبي', 'ST', NULL, NULL, NULL),
(148, 'Saudi Arabia', 'السعودية', 'SA', NULL, NULL, NULL),
(149, 'Senegal', 'السنغال', 'SN', NULL, NULL, NULL),
(150, 'Serbia', 'صربيا', 'RS', NULL, NULL, NULL),
(151, 'Seychelles', 'سيشل', 'SC', NULL, NULL, NULL),
(152, 'Sierra Leone', 'سيراليون', 'SL', NULL, NULL, NULL),
(153, 'Singapore', 'سنغافورة', 'SG', NULL, NULL, NULL),
(154, 'Slovakia', 'سلوفاكيا', 'SK', NULL, NULL, NULL),
(155, 'Slovenia', 'سلوفينيا', 'SI', NULL, NULL, NULL),
(156, 'Solomon Islands', 'جزر سليمان', 'SB', NULL, NULL, NULL),
(157, 'Somalia', 'الصومال', 'SO', NULL, NULL, NULL),
(158, 'South Africa', 'جنوب أفريقيا', 'ZA', NULL, NULL, NULL),
(159, 'South Korea', 'كوريا الجنوبية', 'KR', NULL, NULL, NULL),
(160, 'South Sudan', 'جنوب السودان', 'SS', NULL, NULL, NULL),
(161, 'Spain', 'إسبانيا', 'ES', NULL, NULL, NULL),
(162, 'Sri Lanka', 'سريلانكا', 'LK', NULL, NULL, NULL),
(163, 'Sudan', 'السودان', 'SD', NULL, NULL, NULL),
(164, 'Suriname', 'سورينام', 'SR', NULL, NULL, NULL),
(165, 'Sweden', 'السويد', 'SE', NULL, NULL, NULL),
(166, 'Switzerland', 'سويسرا', 'CH', NULL, NULL, NULL),
(167, 'Syria', 'سوريا', 'SY', NULL, NULL, NULL),
(168, 'Taiwan', 'تايوان', 'TW', NULL, NULL, NULL),
(169, 'Tajikistan', 'طاجيكستان', 'TJ', NULL, NULL, NULL),
(170, 'Tanzania', 'تنزانيا', 'TZ', NULL, NULL, NULL),
(171, 'Thailand', 'تايلاند', 'TH', NULL, NULL, NULL),
(172, 'Timor-Leste', 'تيمور الشرقية', 'TL', NULL, NULL, NULL),
(173, 'Togo', 'توغو', 'TG', NULL, NULL, NULL),
(174, 'Tonga', 'تونغا', 'TO', NULL, NULL, NULL),
(175, 'Trinidad and Tobago', 'ترينيداد وتوباغو', 'TT', NULL, NULL, NULL),
(176, 'Tunisia', 'تونس', 'TN', NULL, NULL, NULL),
(177, 'Turkey', 'تركيا', 'TR', NULL, NULL, NULL),
(178, 'Turkmenistan', 'تركمانستان', 'TM', NULL, NULL, NULL),
(179, 'Tuvalu', 'توفالو', 'TV', NULL, NULL, NULL),
(180, 'Uganda', 'أوغندا', 'UG', NULL, NULL, NULL),
(181, 'Ukraine', 'أوكرانيا', 'UA', NULL, NULL, NULL),
(182, 'United Arab Emirates', 'الإمارات', 'AE', NULL, NULL, NULL),
(183, 'United Kingdom', 'المملكة المتحدة', 'GB', NULL, NULL, NULL),
(184, 'United States', 'الولايات المتحدة', 'US', NULL, NULL, NULL),
(185, 'Uruguay', 'الأوروغواي', 'UY', NULL, NULL, NULL),
(186, 'Uzbekistan', 'أوزبكستان', 'UZ', NULL, NULL, NULL),
(187, 'Vanuatu', 'فانواتو', 'VU', NULL, NULL, NULL),
(188, 'Vatican City', 'الفاتيكان', 'VA', NULL, NULL, NULL),
(189, 'Venezuela', 'فنزويلا', 'VE', NULL, NULL, NULL),
(190, 'Vietnam', 'فيتنام', 'VN', NULL, NULL, NULL),
(191, 'Yemen', 'اليمن', 'YE', NULL, NULL, NULL),
(192, 'Zambia', 'زامبيا', 'ZM', NULL, NULL, NULL),
(193, 'Zimbabwe', 'زيمبابوي', 'ZW', NULL, NULL, NULL);

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

--
-- Table structure for table `evaluations`
--

CREATE TABLE `evaluations` (
  `id` bigint UNSIGNED NOT NULL,
  `evaluation_sheet_id` bigint UNSIGNED NOT NULL,
  `judge_id` bigint UNSIGNED NOT NULL,
  `evaluated_user_id` bigint UNSIGNED NOT NULL,
  `answers` json NOT NULL,
  `score` decimal(5,2) DEFAULT NULL,
  `submitted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `evaluation_aggregates`
--

CREATE TABLE `evaluation_aggregates` (
  `id` bigint UNSIGNED NOT NULL,
  `evaluated_user_id` bigint UNSIGNED NOT NULL,
  `total_evaluations` int NOT NULL DEFAULT '0',
  `criterion_averages` json NOT NULL,
  `overall_score` decimal(5,2) DEFAULT NULL,
  `completed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `evaluation_sheets`
--

CREATE TABLE `evaluation_sheets` (
  `id` bigint UNSIGNED NOT NULL,
  `created_by` bigint UNSIGNED NOT NULL,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `criteria` json NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `exams`
--

CREATE TABLE `exams` (
  `id` bigint UNSIGNED NOT NULL,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `form_schema` json NOT NULL,
  `is_published` tinyint(1) NOT NULL DEFAULT '0',
  `published_at` timestamp NULL DEFAULT NULL,
  `deadline_at` timestamp NULL DEFAULT NULL,
  `max_attempts` int NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `exam_attempts`
--

CREATE TABLE `exam_attempts` (
  `id` bigint UNSIGNED NOT NULL,
  `exam_id` bigint UNSIGNED NOT NULL,
  `user_id` bigint UNSIGNED NOT NULL,
  `answers` json NOT NULL,
  `score` decimal(5,2) DEFAULT NULL,
  `started_at` timestamp NULL DEFAULT NULL,
  `completed_at` timestamp NULL DEFAULT NULL,
  `auto_submitted` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `failed_jobs`
--

CREATE TABLE `failed_jobs` (
  `id` bigint UNSIGNED NOT NULL,
  `uuid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `connection` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `queue` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `payload` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `exception` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `failed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `governorates`
--

CREATE TABLE `governorates` (
  `id` bigint UNSIGNED NOT NULL,
  `name_en` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name_ar` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `code` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `governorates`
--

INSERT INTO `governorates` (`id`, `name_en`, `name_ar`, `code`, `created_at`, `updated_at`, `deleted_at`) VALUES
(1, 'Cairo', 'القاهرة', 'C', NULL, NULL, NULL),
(2, 'Giza', 'الجيزة', 'G', NULL, NULL, NULL),
(3, 'Alexandria', 'الإسكندرية', 'ALX', NULL, NULL, NULL),
(4, 'Aswan', 'أسوان', 'ASN', NULL, NULL, NULL),
(5, 'Asyut', 'أسيوط', 'AST', NULL, NULL, NULL),
(6, 'Beheira', 'البحيرة', 'BH', NULL, NULL, NULL),
(7, 'Beni Suef', 'بني سويف', 'BS', NULL, NULL, NULL),
(8, 'Dakahlia', 'الدقهلية', 'DK', NULL, NULL, NULL),
(9, 'Damietta', 'دمياط', 'DM', NULL, NULL, NULL),
(10, 'Faiyum', 'الفيوم', 'FY', NULL, NULL, NULL),
(11, 'Sharqia', 'الشرقية', 'SHR', NULL, NULL, NULL),
(12, 'Gharbia', 'الغربية', 'GHB', NULL, NULL, NULL),
(13, 'Ismailia', 'الإسماعيلية', 'IS', NULL, NULL, NULL),
(14, 'Kafr El Sheikh', 'كفر الشيخ', 'KS', NULL, NULL, NULL),
(15, 'Luxor', 'الأقصر', 'LX', NULL, NULL, NULL),
(16, 'Matrouh', 'مرسى مطروح', 'MT', NULL, NULL, NULL),
(17, 'Minya', 'المنيا', 'MN', NULL, NULL, NULL),
(18, 'Monufia', 'المنوفية', 'MF', NULL, NULL, NULL),
(19, 'New Valley', 'الوادي الجديد', 'NV', NULL, NULL, NULL),
(20, 'North Sinai', 'شمال سيناء', 'NS', NULL, NULL, NULL),
(21, 'Port Said', 'بورسعيد', 'PS', NULL, NULL, NULL),
(22, 'Qalyubia', 'القليوبية', 'QLY', NULL, NULL, NULL),
(23, 'Qena', 'قنا', 'QN', NULL, NULL, NULL),
(24, 'Red Sea', 'البحر الأحمر', 'RS', NULL, NULL, NULL),
(25, 'Sohag', 'سوهاج', 'SHG', NULL, NULL, NULL),
(26, 'South Sinai', 'جنوب سيناء', 'SS', NULL, NULL, NULL),
(27, 'Suez', 'السويس', 'SW', NULL, NULL, NULL);

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

--
-- Table structure for table `jobs`
--

CREATE TABLE `jobs` (
  `id` bigint UNSIGNED NOT NULL,
  `queue` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `payload` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `attempts` tinyint UNSIGNED NOT NULL,
  `reserved_at` int UNSIGNED DEFAULT NULL,
  `available_at` int UNSIGNED NOT NULL,
  `created_at` int UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `job_batches`
--

CREATE TABLE `job_batches` (
  `id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `total_jobs` int NOT NULL,
  `pending_jobs` int NOT NULL,
  `failed_jobs` int NOT NULL,
  `failed_job_ids` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `options` mediumtext COLLATE utf8mb4_unicode_ci,
  `cancelled_at` int DEFAULT NULL,
  `created_at` int NOT NULL,
  `finished_at` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `languages`
--

CREATE TABLE `languages` (
  `id` bigint UNSIGNED NOT NULL,
  `name_en` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name_ar` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `iso_code` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `languages`
--

INSERT INTO `languages` (`id`, `name_en`, `name_ar`, `iso_code`, `created_at`, `updated_at`, `deleted_at`) VALUES
(1, 'Arabic', 'العربية', 'AR', NULL, NULL, NULL),
(2, 'English', 'الإنجليزية', 'EN', NULL, NULL, NULL),
(3, 'French', 'الفرنسية', 'FR', NULL, NULL, NULL),
(4, 'German', 'الألمانية', 'DE', NULL, NULL, NULL),
(5, 'Spanish', 'الإسبانية', 'ES', NULL, NULL, NULL),
(6, 'Italian', 'الإيطالية', 'IT', NULL, NULL, NULL),
(7, 'Chinese', 'الصينية', 'ZH', NULL, NULL, NULL),
(8, 'Japanese', 'اليابانية', 'JA', NULL, NULL, NULL),
(9, 'Russian', 'الروسية', 'RU', NULL, NULL, NULL),
(10, 'Portuguese', 'البرتغالية', 'PT', NULL, NULL, NULL),
(11, 'Hindi', 'الهندية', 'HI', NULL, NULL, NULL),
(12, 'Bengali', 'البنغالية', 'BN', NULL, NULL, NULL),
(13, 'Turkish', 'التركية', 'TR', NULL, NULL, NULL),
(14, 'Korean', 'الكورية', 'KO', NULL, NULL, NULL),
(15, 'Dutch', 'الهولندية', 'NL', NULL, NULL, NULL),
(16, 'Swedish', 'السويدية', 'SV', NULL, NULL, NULL),
(17, 'Greek', 'اليونانية', 'EL', NULL, NULL, NULL),
(18, 'Hebrew', 'العبرية', 'HE', NULL, NULL, NULL),
(19, 'Swahili', 'السواحيلية', 'SW', NULL, NULL, NULL);

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

--
-- Table structure for table `migrations`
--

CREATE TABLE `migrations` (
  `id` int UNSIGNED NOT NULL,
  `migration` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `batch` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `migrations`
--

INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES
(1, '0001_01_01_000001_create_cache_table', 1),
(2, '0001_01_01_000002_create_jobs_table', 1),
(3, '2025_01_01_000001_create_platform_tables', 1),
(4, '2025_11_09_011020_create_personal_access_tokens_table', 1),
(5, '2025_11_20_200304_create_languages_table', 1),
(6, '2025_11_20_200323_create_countries_table', 1),
(7, '2025_11_20_200332_create_governorates_table', 1),
(8, '2025_11_22_191744_create_applicants_table', 1),
(9, '2025_11_23_191657_create_applicant_languages_table', 1),
(10, '2025_11_23_191701_create_applicant_degrees_table', 1),
(11, '2025_11_23_191721_create_applicant_documents_table', 1),
(12, '2025_11_23_192315_create_applicant_nationalities_table', 1),
(13, '2025_11_23_201424_create_laravel_tables', 1),
(14, '2025_11_28_000001_add_status_to_users_table', 1),
(15, '2025_11_28_000002_add_user_id_to_applicants_table', 1),
(16, '2025_11_28_183258_add_ip_and_user_agent_to_system_logs_table', 1),
(17, '2025_11_28_184833_create_settings_table', 1),
(18, '2025_11_28_190130_add_login_attempts_to_users_table', 1),
(19, '2025_11_28_191616_add_auto_submitted_to_exam_attempts_table', 1),
(20, '2025_11_28_222504_add_last_login_ip_to_users_table', 1),
(21, '2025_11_29_000000_make_causer_nullable_in_system_logs', 1),
(22, '2025_11_29_020958_change_evaluation_sheets_judge_id_to_created_by', 1),
(23, '2025_11_29_add_soft_deletes_to_all_tables', 1),
(24, '2025_11_30_add_status_to_applicants_table', 1),
(25, '2025_11_30_create_applicant_submissions_table', 1);

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

--
-- Table structure for table `sessions`
--

CREATE TABLE `sessions` (
  `id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_id` bigint UNSIGNED DEFAULT NULL,
  `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_agent` text COLLATE utf8mb4_unicode_ci,
  `payload` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `last_activity` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `system_logs`
--

CREATE TABLE `system_logs` (
  `id` bigint UNSIGNED NOT NULL,
  `causer_type` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `causer_id` bigint UNSIGNED DEFAULT NULL,
  `action` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `subject_type` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `subject_id` bigint UNSIGNED DEFAULT NULL,
  `meta` json DEFAULT NULL,
  `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_agent` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `applicants`
--
ALTER TABLE `applicants`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `applicants_national_id_unique` (`national_id`),
  ADD UNIQUE KEY `applicants_applicant_code_unique` (`applicant_code`),
  ADD UNIQUE KEY `applicants_email_unique` (`email`),
  ADD KEY `applicants_governorate_id_foreign` (`governorate_id`),
  ADD KEY `applicants_user_id_foreign` (`user_id`);

--
-- Indexes for table `applicant_degrees`
--
ALTER TABLE `applicant_degrees`
  ADD PRIMARY KEY (`id`),
  ADD KEY `applicant_degrees_applicant_id_foreign` (`applicant_id`);

--
-- Indexes for table `applicant_documents`
--
ALTER TABLE `applicant_documents`
  ADD PRIMARY KEY (`id`),
  ADD KEY `applicant_documents_applicant_id_foreign` (`applicant_id`);

--
-- Indexes for table `applicant_languages`
--
ALTER TABLE `applicant_languages`
  ADD PRIMARY KEY (`id`),
  ADD KEY `applicant_languages_applicant_id_foreign` (`applicant_id`),
  ADD KEY `applicant_languages_language_id_foreign` (`language_id`);

--
-- Indexes for table `applicant_nationalities`
--
ALTER TABLE `applicant_nationalities`
  ADD PRIMARY KEY (`id`),
  ADD KEY `applicant_nationalities_applicant_id_foreign` (`applicant_id`),
  ADD KEY `applicant_nationalities_country_id_foreign` (`country_id`);

--
-- Indexes for table `applicant_submissions`
--
ALTER TABLE `applicant_submissions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `applicant_submissions_applicant_id_foreign` (`applicant_id`),
  ADD KEY `applicant_submissions_ip_address_index` (`ip_address`),
  ADD KEY `applicant_submissions_browser_fingerprint_index` (`browser_fingerprint`),
  ADD KEY `applicant_submissions_created_at_index` (`created_at`);

--
-- Indexes for table `categories`
--
ALTER TABLE `categories`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `category_user`
--
ALTER TABLE `category_user`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `category_user_category_id_user_id_unique` (`category_id`,`user_id`),
  ADD KEY `category_user_user_id_foreign` (`user_id`);

--
-- Indexes for table `countries`
--
ALTER TABLE `countries`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `evaluations`
--
ALTER TABLE `evaluations`
  ADD PRIMARY KEY (`id`),
  ADD KEY `evaluations_evaluation_sheet_id_foreign` (`evaluation_sheet_id`),
  ADD KEY `evaluations_judge_id_foreign` (`judge_id`),
  ADD KEY `evaluations_evaluated_user_id_foreign` (`evaluated_user_id`);

--
-- Indexes for table `evaluation_aggregates`
--
ALTER TABLE `evaluation_aggregates`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `evaluation_aggregates_evaluated_user_id_unique` (`evaluated_user_id`);

--
-- Indexes for table `evaluation_sheets`
--
ALTER TABLE `evaluation_sheets`
  ADD PRIMARY KEY (`id`),
  ADD KEY `evaluation_sheets_judge_id_foreign` (`created_by`);

--
-- Indexes for table `exams`
--
ALTER TABLE `exams`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `exam_attempts`
--
ALTER TABLE `exam_attempts`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `exam_attempts_exam_id_user_id_unique` (`exam_id`,`user_id`),
  ADD KEY `exam_attempts_user_id_foreign` (`user_id`);

--
-- Indexes for table `failed_jobs`
--
ALTER TABLE `failed_jobs`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`);

--
-- Indexes for table `governorates`
--
ALTER TABLE `governorates`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `jobs`
--
ALTER TABLE `jobs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `jobs_queue_index` (`queue`);

--
-- Indexes for table `job_batches`
--
ALTER TABLE `job_batches`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `languages`
--
ALTER TABLE `languages`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `migrations`
--
ALTER TABLE `migrations`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `sessions`
--
ALTER TABLE `sessions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `sessions_user_id_index` (`user_id`),
  ADD KEY `sessions_last_activity_index` (`last_activity`);

--
-- Indexes for table `system_logs`
--
ALTER TABLE `system_logs`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `applicants`
--
ALTER TABLE `applicants`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `applicant_degrees`
--
ALTER TABLE `applicant_degrees`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `applicant_documents`
--
ALTER TABLE `applicant_documents`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `applicant_languages`
--
ALTER TABLE `applicant_languages`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `applicant_nationalities`
--
ALTER TABLE `applicant_nationalities`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `applicant_submissions`
--
ALTER TABLE `applicant_submissions`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `categories`
--
ALTER TABLE `categories`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `category_user`
--
ALTER TABLE `category_user`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `countries`
--
ALTER TABLE `countries`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=194;

--
-- AUTO_INCREMENT for table `evaluations`
--
ALTER TABLE `evaluations`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `evaluation_aggregates`
--
ALTER TABLE `evaluation_aggregates`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `evaluation_sheets`
--
ALTER TABLE `evaluation_sheets`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `exams`
--
ALTER TABLE `exams`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `exam_attempts`
--
ALTER TABLE `exam_attempts`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `failed_jobs`
--
ALTER TABLE `failed_jobs`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `governorates`
--
ALTER TABLE `governorates`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=28;

--
-- AUTO_INCREMENT for table `jobs`
--
ALTER TABLE `jobs`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `languages`
--
ALTER TABLE `languages`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=20;

--
-- AUTO_INCREMENT for table `migrations`
--
ALTER TABLE `migrations`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=26;

--
-- AUTO_INCREMENT for table `system_logs`
--
ALTER TABLE `system_logs`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `applicants`
--
ALTER TABLE `applicants`
  ADD CONSTRAINT `applicants_governorate_id_foreign` FOREIGN KEY (`governorate_id`) REFERENCES `governorates` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `applicants_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `applicant_degrees`
--
ALTER TABLE `applicant_degrees`
  ADD CONSTRAINT `applicant_degrees_applicant_id_foreign` FOREIGN KEY (`applicant_id`) REFERENCES `applicants` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `applicant_documents`
--
ALTER TABLE `applicant_documents`
  ADD CONSTRAINT `applicant_documents_applicant_id_foreign` FOREIGN KEY (`applicant_id`) REFERENCES `applicants` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `applicant_languages`
--
ALTER TABLE `applicant_languages`
  ADD CONSTRAINT `applicant_languages_applicant_id_foreign` FOREIGN KEY (`applicant_id`) REFERENCES `applicants` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `applicant_languages_language_id_foreign` FOREIGN KEY (`language_id`) REFERENCES `languages` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `applicant_nationalities`
--
ALTER TABLE `applicant_nationalities`
  ADD CONSTRAINT `applicant_nationalities_applicant_id_foreign` FOREIGN KEY (`applicant_id`) REFERENCES `applicants` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `applicant_nationalities_country_id_foreign` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `applicant_submissions`
--
ALTER TABLE `applicant_submissions`
  ADD CONSTRAINT `applicant_submissions_applicant_id_foreign` FOREIGN KEY (`applicant_id`) REFERENCES `applicants` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `category_user`
--
ALTER TABLE `category_user`
  ADD CONSTRAINT `category_user_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `category_user_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `evaluations`
--
ALTER TABLE `evaluations`
  ADD CONSTRAINT `evaluations_evaluated_user_id_foreign` FOREIGN KEY (`evaluated_user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `evaluations_evaluation_sheet_id_foreign` FOREIGN KEY (`evaluation_sheet_id`) REFERENCES `evaluation_sheets` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `evaluations_judge_id_foreign` FOREIGN KEY (`judge_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `evaluation_aggregates`
--
ALTER TABLE `evaluation_aggregates`
  ADD CONSTRAINT `evaluation_aggregates_evaluated_user_id_foreign` FOREIGN KEY (`evaluated_user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `evaluation_sheets`
--
ALTER TABLE `evaluation_sheets`
  ADD CONSTRAINT `evaluation_sheets_judge_id_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `exam_attempts`
--
ALTER TABLE `exam_attempts`
  ADD CONSTRAINT `exam_attempts_exam_id_foreign` FOREIGN KEY (`exam_id`) REFERENCES `exams` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `exam_attempts_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;
COMMIT;

