-- HelperLink Production Baseline Schema & Seed Data
-- Database: helperlink

SET FOREIGN_KEY_CHECKS = 0;

-- =========================
-- 1. ADMINS
-- =========================
DROP TABLE IF EXISTS `admins`;
CREATE TABLE IF NOT EXISTS `admins` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(150) NOT NULL,
  `email` varchar(150) NOT NULL UNIQUE,
  `password` varchar(255) NOT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `admins` (`id`, `name`, `email`, `password`, `status`) VALUES
(1, 'Super Admin', 'admin@helperlink.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'active');

-- =========================
-- 2. CUSTOMERS
-- =========================
DROP TABLE IF EXISTS `customer_cities`;
DROP TABLE IF EXISTS `customer_notification_settings`;
DROP TABLE IF EXISTS `customers`;
CREATE TABLE IF NOT EXISTS `customers` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `full_name` varchar(150) NOT NULL,
  `email` varchar(150) DEFAULT NULL UNIQUE,
  `phone_code` varchar(10) DEFAULT NULL,
  `phone` varchar(30) DEFAULT NULL UNIQUE,
  `password` varchar(255) NOT NULL,
  `profile_image` varchar(255) DEFAULT NULL,
  `preferred_language` varchar(10) DEFAULT 'en',
  `country_id` bigint(20) UNSIGNED DEFAULT NULL,
  `city_id` bigint(20) UNSIGNED DEFAULT NULL,
  `status` enum('active','inactive','blocked') DEFAULT 'active',
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `phone_verified_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================
-- 3. VENDORS
-- =========================
DROP TABLE IF EXISTS `vendors`;
CREATE TABLE IF NOT EXISTS `vendors` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `agency_name` varchar(180) NOT NULL,
  `tagline` varchar(255) DEFAULT NULL,
  `email` varchar(150) NOT NULL UNIQUE,
  `phone_code` varchar(10) DEFAULT NULL,
  `phone` varchar(30) DEFAULT NULL UNIQUE,
  `password` varchar(255) NOT NULL,
  `logo` varchar(255) DEFAULT NULL,
  `cover_image` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `trade_license_number` varchar(100) DEFAULT NULL,
  `country_id` bigint(20) UNSIGNED DEFAULT NULL,
  `city_id` bigint(20) UNSIGNED DEFAULT NULL,
  `address` text DEFAULT NULL,
  `latitude` decimal(10,8) DEFAULT NULL,
  `longitude` decimal(11,8) DEFAULT NULL,
  `preferred_language` varchar(10) DEFAULT 'en',
  `status` enum('pending','approved','rejected','suspended') DEFAULT 'pending',
  `rejection_reason` text DEFAULT NULL,
  `is_verified` tinyint(1) DEFAULT 0,
  `is_featured` tinyint(1) DEFAULT 0,
  `approved_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================
-- 4. LOCATIONS
-- =========================
DROP TABLE IF EXISTS `cities`;
DROP TABLE IF EXISTS `countries`;
CREATE TABLE IF NOT EXISTS `countries` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name_en` varchar(100) NOT NULL,
  `name_ar` varchar(100) DEFAULT NULL,
  `phone_code` varchar(10) DEFAULT NULL,
  `flag` varchar(255) DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `countries` (`id`, `name_en`, `name_ar`, `phone_code`, `status`) VALUES
(1, 'United Arab Emirates', 'الإمارات العربية المتحدة', '+971', 'active');

CREATE TABLE IF NOT EXISTS `cities` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `country_id` bigint(20) UNSIGNED NOT NULL,
  `name_en` varchar(100) NOT NULL,
  `name_ar` varchar(100) DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  KEY `country_id` (`country_id`),
  FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `cities` (`id`, `country_id`, `name_en`, `name_ar`, `status`) VALUES
(1, 1, 'Dubai', 'دبي', 'active'),
(2, 1, 'Abu Dhabi', 'أبو ظبي', 'active'),
(3, 1, 'Sharjah', 'الشارقة', 'active'),
(4, 1, 'Ajman', 'عجمان', 'active'),
(5, 1, 'Ras Al Khaimah', 'رأس الخيمة', 'active'),
(6, 1, 'Fujairah', 'الفجيرة', 'active'),
(7, 1, 'Umm Al Quwain', 'أم القيوين', 'active');

CREATE TABLE IF NOT EXISTS `customer_cities` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `customer_id` bigint(20) UNSIGNED NOT NULL,
  `city_id` bigint(20) UNSIGNED NOT NULL,
  UNIQUE KEY `unique_customer_city` (`customer_id`,`city_id`),
  KEY `city_id` (`city_id`),
  FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`city_id`) REFERENCES `cities` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================
-- 5. LOOKUP TABLES
-- =========================
DROP TABLE IF EXISTS `nationalities`;
CREATE TABLE IF NOT EXISTS `nationalities` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name_en` varchar(100) NOT NULL,
  `name_ar` varchar(100) DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `nationalities` (`id`, `name_en`, `name_ar`, `status`) VALUES
(1, 'Filipino', 'فلبيني', 'active'),
(2, 'Indonesian', 'إندونيسي', 'active'),
(3, 'Sri Lankan', 'سريلانكي', 'active'),
(4, 'Ethiopian', 'إثيوبي', 'active'),
(5, 'Nepalese', 'نيبالي', 'active'),
(6, 'Indian', 'هندي', 'active'),
(7, 'Ugandan', 'أوغندي', 'active'),
(8, 'Kenyan', 'كيني', 'active');

DROP TABLE IF EXISTS `languages`;
CREATE TABLE IF NOT EXISTS `languages` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name_en` varchar(100) NOT NULL,
  `name_ar` varchar(100) DEFAULT NULL,
  `code` varchar(10) DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `languages` (`id`, `name_en`, `name_ar`, `code`, `status`) VALUES
(1, 'Arabic', 'العربية', 'ar', 'active'),
(2, 'English', 'إنجليزية', 'en', 'active'),
(3, 'Tagalog', 'تاغالوغية', 'tl', 'active'),
(4, 'Hindi', 'هندية', 'hi', 'active'),
(5, 'Urdu', 'أوردو', 'ur', 'active');

DROP TABLE IF EXISTS `specialties`;
CREATE TABLE IF NOT EXISTS `specialties` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name_en` varchar(120) NOT NULL,
  `name_ar` varchar(120) DEFAULT NULL,
  `icon` varchar(255) DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `specialties` (`id`, `name_en`, `name_ar`, `status`) VALUES
(1, 'Baby Care', 'رعاية الأطفال', 'active'),
(2, 'Elderly Care', 'رعاية كبار السن', 'active'),
(3, 'Pet Care', 'رعاية الحيوانات الأليفة', 'active'),
(4, 'Cooking', 'الطبخ', 'active'),
(5, 'Housekeeping & Cleaning', 'التنظيف والتنسيق المنزلي', 'active');

DROP TABLE IF EXISTS `skills`;
CREATE TABLE IF NOT EXISTS `skills` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name_en` varchar(120) NOT NULL,
  `name_ar` varchar(120) DEFAULT NULL,
  `icon` varchar(255) DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `skills` (`id`, `name_en`, `name_ar`, `status`) VALUES
(1, 'Ironing', 'الكي', 'active'),
(2, 'Washing & Laundry', 'الغسيل', 'active'),
(3, 'Driving', 'القيادة', 'active'),
(4, 'Tutoring / Homework Support', 'مساعدة في الدروس', 'active'),
(5, 'First Aid', 'الإسعافات الأولية', 'active');

DROP TABLE IF EXISTS `service_models`;
CREATE TABLE IF NOT EXISTS `service_models` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name_en` varchar(120) NOT NULL,
  `name_ar` varchar(120) DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `service_models` (`id`, `name_en`, `name_ar`, `status`) VALUES
(1, 'Live-in', 'مقيم', 'active'),
(2, 'Live-out', 'غير مقيم', 'active'),
(3, 'Part-time', 'دوام جزئي', 'active'),
(4, 'Full-time', 'دوام كامل', 'active');

DROP TABLE IF EXISTS `accommodation_types`;
CREATE TABLE IF NOT EXISTS `accommodation_types` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name_en` varchar(120) NOT NULL,
  `name_ar` varchar(120) DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `accommodation_types` (`id`, `name_en`, `name_ar`, `status`) VALUES
(1, 'Apartment', 'شقة', 'active'),
(2, 'Villa', 'فيلا', 'active'),
(3, 'Townhouse', 'تاون هاوس', 'active'),
(4, 'Studio', 'استوديو', 'active');

-- =========================
-- 6. MAIDS
-- =========================
DROP TABLE IF EXISTS `maid_work_experience`;
DROP TABLE IF EXISTS `maid_gallery`;
DROP TABLE IF EXISTS `maid_skills`;
DROP TABLE IF EXISTS `maid_specialties`;
DROP TABLE IF EXISTS `maid_languages`;
DROP TABLE IF EXISTS `maids`;
CREATE TABLE IF NOT EXISTS `maids` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `vendor_id` bigint(20) UNSIGNED NOT NULL,
  `full_name` varchar(150) NOT NULL,
  `profile_image` varchar(255) DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `gender` enum('female','male') DEFAULT 'female',
  `marital_status` enum('single','married','divorced','widowed') DEFAULT NULL,
  `visa_status` enum('sponsored','own_visa','visit_visa','none') DEFAULT NULL,
  `emirates_id_verified` tinyint(1) DEFAULT 0,
  `reliability_score` int(11) DEFAULT 100,
  `nationality_id` bigint(20) UNSIGNED DEFAULT NULL,
  `country_id` bigint(20) UNSIGNED DEFAULT NULL,
  `city_id` bigint(20) UNSIGNED DEFAULT NULL,
  `bio` text DEFAULT NULL,
  `custom_speciality` varchar(255) DEFAULT NULL,
  `years_experience` int(11) DEFAULT 0,
  `experience_location` varchar(255) DEFAULT NULL,
  `monthly_salary` decimal(10,2) DEFAULT 0.00,
  `currency` varchar(10) DEFAULT 'AED',
  `accommodation_type_id` bigint(20) UNSIGNED DEFAULT NULL,
  `service_model_id` bigint(20) UNSIGNED DEFAULT NULL,
  `availability_status` enum('available','booked','unavailable') DEFAULT 'available',
  `available_from` date DEFAULT NULL,
  `verification_status` enum('pending','approved','rejected') DEFAULT 'pending',
  `status` enum('draft','published','hidden','suspended') DEFAULT 'draft',
  `views` int(11) DEFAULT 0,
  `rejection_reason` text DEFAULT NULL,
  `published_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL,
  `is_recommended` tinyint(1) DEFAULT 0,
  KEY `vendor_id` (`vendor_id`),
  KEY `nationality_id` (`nationality_id`),
  KEY `country_id` (`country_id`),
  KEY `city_id` (`city_id`),
  KEY `accommodation_type_id` (`accommodation_type_id`),
  KEY `service_model_id` (`service_model_id`),
  FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`nationality_id`) REFERENCES `nationalities` (`id`) ON DELETE SET NULL,
  FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON DELETE SET NULL,
  FOREIGN KEY (`city_id`) REFERENCES `cities` (`id`) ON DELETE SET NULL,
  FOREIGN KEY (`accommodation_type_id`) REFERENCES `accommodation_types` (`id`) ON DELETE SET NULL,
  FOREIGN KEY (`service_model_id`) REFERENCES `service_models` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `maid_languages` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `maid_id` bigint(20) UNSIGNED NOT NULL,
  `language_id` bigint(20) UNSIGNED NOT NULL,
  `proficiency` enum('basic','conversational','fluent','native') DEFAULT 'conversational',
  KEY `maid_id` (`maid_id`),
  KEY `language_id` (`language_id`),
  FOREIGN KEY (`maid_id`) REFERENCES `maids` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`language_id`) REFERENCES `languages` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `maid_specialties` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `maid_id` bigint(20) UNSIGNED NOT NULL,
  `specialty_id` bigint(20) UNSIGNED NOT NULL,
  KEY `maid_id` (`maid_id`),
  KEY `specialty_id` (`specialty_id`),
  FOREIGN KEY (`maid_id`) REFERENCES `maids` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`specialty_id`) REFERENCES `specialties` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `maid_skills` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `maid_id` bigint(20) UNSIGNED NOT NULL,
  `skill_id` bigint(20) UNSIGNED NOT NULL,
  KEY `maid_id` (`maid_id`),
  KEY `skill_id` (`skill_id`),
  FOREIGN KEY (`maid_id`) REFERENCES `maids` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`skill_id`) REFERENCES `skills` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `maid_gallery` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `maid_id` bigint(20) UNSIGNED NOT NULL,
  `image` varchar(255) NOT NULL,
  `sort_order` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  KEY `maid_id` (`maid_id`),
  FOREIGN KEY (`maid_id`) REFERENCES `maids` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `maid_work_experience` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `maid_id` bigint(20) UNSIGNED NOT NULL,
  `job_title` varchar(150) NOT NULL,
  `employer` varchar(150) DEFAULT NULL,
  `location` varchar(150) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `date_from` date DEFAULT NULL,
  `date_to` date DEFAULT NULL,
  `is_current` tinyint(1) DEFAULT 0,
  `sort_order` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  KEY `maid_id` (`maid_id`),
  FOREIGN KEY (`maid_id`) REFERENCES `maids` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================
-- 7. DOCUMENTS
-- =========================
DROP TABLE IF EXISTS `vendor_documents`;
DROP TABLE IF EXISTS `maid_documents`;
DROP TABLE IF EXISTS `document_types`;
CREATE TABLE IF NOT EXISTS `document_types` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `owner_type` enum('vendor','maid') NOT NULL,
  `name_en` varchar(150) NOT NULL,
  `name_ar` varchar(150) DEFAULT NULL,
  `is_required` tinyint(1) DEFAULT 1,
  `status` enum('active','inactive') DEFAULT 'active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `document_types` (`id`, `owner_type`, `name_en`, `name_ar`, `is_required`, `status`) VALUES
(1, 'vendor', 'Trade License', 'الرخصة التجارية', 1, 'active'),
(2, 'vendor', 'Manager\'s Emirates ID', 'الهوية الإماراتية للمدير', 1, 'active'),
(3, 'vendor', 'MOHRE Approval', 'موافقة وزارة الموارد البشرية والتوطين', 0, 'active'),
(4, 'maid', 'Passport Copy', 'جواز السفر', 1, 'active'),
(5, 'maid', 'Visa Copy', 'تأشيرة الإقامة', 1, 'active'),
(6, 'maid', 'Medical Certificate', 'الشهادة الطبية', 0, 'active');

CREATE TABLE IF NOT EXISTS `vendor_documents` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `vendor_id` bigint(20) UNSIGNED NOT NULL,
  `document_type_id` bigint(20) UNSIGNED NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `expiry_date` date DEFAULT NULL,
  `status` enum('pending','approved','rejected','expired') DEFAULT 'pending',
  `admin_note` text DEFAULT NULL,
  `reviewed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  KEY `vendor_id` (`vendor_id`),
  KEY `document_type_id` (`document_type_id`),
  FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`document_type_id`) REFERENCES `document_types` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `maid_documents` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `maid_id` bigint(20) UNSIGNED NOT NULL,
  `document_type_id` bigint(20) UNSIGNED NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `expiry_date` date DEFAULT NULL,
  `status` enum('pending','approved','rejected','expired') DEFAULT 'pending',
  `admin_note` text DEFAULT NULL,
  `reviewed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  KEY `maid_id` (`maid_id`),
  KEY `document_type_id` (`document_type_id`),
  FOREIGN KEY (`maid_id`) REFERENCES `maids` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`document_type_id`) REFERENCES `document_types` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================
-- 8. FAVORITES
-- =========================
DROP TABLE IF EXISTS `favorite_vendors`;
CREATE TABLE IF NOT EXISTS `favorite_vendors` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `customer_id` bigint(20) UNSIGNED NOT NULL,
  `vendor_id` bigint(20) UNSIGNED NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  UNIQUE KEY `unique_customer_vendor` (`customer_id`,`vendor_id`),
  KEY `vendor_id` (`vendor_id`),
  FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `favorite_maids`;
CREATE TABLE IF NOT EXISTS `favorite_maids` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `customer_id` bigint(20) UNSIGNED NOT NULL,
  `maid_id` bigint(20) UNSIGNED NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  UNIQUE KEY `unique_customer_maid` (`customer_id`,`maid_id`),
  KEY `maid_id` (`maid_id`),
  FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`maid_id`) REFERENCES `maids` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================
-- 9. INTERVIEWS
-- =========================
DROP TABLE IF EXISTS `interview_reschedules`;
DROP TABLE IF EXISTS `interview_requests`;
DROP TABLE IF EXISTS `vendor_interview_availability`;
DROP TABLE IF EXISTS `vendor_interview_settings`;
DROP TABLE IF EXISTS `interview_platforms`;

CREATE TABLE IF NOT EXISTS `interview_platforms` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(100) NOT NULL,
  `icon` varchar(255) DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `interview_platforms` (`id`, `name`, `status`) VALUES
(1, 'Zoom', 'active'),
(2, 'Google Meet', 'active'),
(3, 'WhatsApp Video', 'active'),
(4, 'In-person', 'active');

CREATE TABLE IF NOT EXISTS `vendor_interview_settings` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `vendor_id` bigint(20) UNSIGNED NOT NULL,
  `timezone` varchar(100) DEFAULT 'Asia/Dubai',
  `duration_minutes` int(11) DEFAULT 45,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL,
  KEY `vendor_id` (`vendor_id`),
  FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `vendor_interview_availability` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `vendor_id` bigint(20) UNSIGNED NOT NULL,
  `day_of_week` enum('monday','tuesday','wednesday','thursday','friday','saturday','sunday') NOT NULL,
  `is_available` tinyint(1) DEFAULT 1,
  `time_from` time DEFAULT NULL,
  `time_to` time DEFAULT NULL,
  KEY `vendor_id` (`vendor_id`),
  FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `interview_requests` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `customer_id` bigint(20) UNSIGNED NOT NULL,
  `vendor_id` bigint(20) UNSIGNED NOT NULL,
  `maid_id` bigint(20) UNSIGNED NOT NULL,
  `platform_id` bigint(20) UNSIGNED DEFAULT NULL,
  `interview_date` date NOT NULL,
  `time_from` time NOT NULL,
  `time_to` time NOT NULL,
  `meeting_link` varchar(255) DEFAULT NULL,
  `meeting_room_id` varchar(150) DEFAULT NULL,
  `status` enum('pending','confirmed','rescheduled','cancelled','completed','rejected') DEFAULT 'pending',
  `cancel_reason` text DEFAULT NULL,
  `customer_notes` text DEFAULT NULL,
  `vendor_notes` text DEFAULT NULL,
  `translation_enabled` tinyint(1) DEFAULT 0,
  `customer_language` varchar(50) DEFAULT NULL,
  `helper_language` varchar(50) DEFAULT NULL,
  `notify_app` tinyint(1) DEFAULT 1,
  `notify_whatsapp` tinyint(1) DEFAULT 0,
  `notify_email` tinyint(1) DEFAULT 0,
  `notify_call` tinyint(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL,
  KEY `customer_id` (`customer_id`),
  KEY `vendor_id` (`vendor_id`),
  KEY `maid_id` (`maid_id`),
  KEY `platform_id` (`platform_id`),
  FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`maid_id`) REFERENCES `maids` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`platform_id`) REFERENCES `interview_platforms` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `interview_reschedules` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `interview_request_id` bigint(20) UNSIGNED NOT NULL,
  `requested_by` enum('customer','vendor') NOT NULL,
  `old_date` date NOT NULL,
  `old_time_from` time NOT NULL,
  `old_time_to` time NOT NULL,
  `new_date` date NOT NULL,
  `new_time_from` time NOT NULL,
  `new_time_to` time NOT NULL,
  `status` enum('pending','accepted','rejected') DEFAULT 'pending',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  KEY `interview_request_id` (`interview_request_id`),
  FOREIGN KEY (`interview_request_id`) REFERENCES `interview_requests` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================
-- 10. QUOTATIONS
-- =========================
DROP TABLE IF EXISTS `quotation_inclusions`;
DROP TABLE IF EXISTS `quotations`;
DROP TABLE IF EXISTS `quotation_request_skills`;
DROP TABLE IF EXISTS `quotation_request_specialties`;
DROP TABLE IF EXISTS `quotation_requests`;

CREATE TABLE IF NOT EXISTS `quotation_requests` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `customer_id` bigint(20) UNSIGNED NOT NULL,
  `vendor_id` bigint(20) UNSIGNED NOT NULL,
  `maid_id` bigint(20) UNSIGNED DEFAULT NULL,
  `request_type` enum('specific_maid','general_vendor') NOT NULL,
  `service_model_id` bigint(20) UNSIGNED DEFAULT NULL,
  `accommodation_type_id` bigint(20) UNSIGNED DEFAULT NULL,
  `preferred_nationality_id` bigint(20) UNSIGNED DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `duration_months` int(11) DEFAULT NULL,
  `work_days` int(11) DEFAULT NULL,
  `city_id` bigint(20) UNSIGNED DEFAULT NULL,
  `number_of_helpers` int(11) DEFAULT 1,
  `estimated_monthly_payment` decimal(10,2) DEFAULT NULL,
  `estimated_total_value` decimal(10,2) DEFAULT NULL,
  `additional_notes` text DEFAULT NULL,
  `status` enum('pending','vendor_reviewing','quoted','accepted','declined','expired','cancelled') DEFAULT 'pending',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL,
  KEY `customer_id` (`customer_id`),
  KEY `vendor_id` (`vendor_id`),
  KEY `maid_id` (`maid_id`),
  KEY `service_model_id` (`service_model_id`),
  KEY `accommodation_type_id` (`accommodation_type_id`),
  KEY `preferred_nationality_id` (`preferred_nationality_id`),
  KEY `city_id` (`city_id`),
  FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`maid_id`) REFERENCES `maids` (`id`) ON DELETE SET NULL,
  FOREIGN KEY (`service_model_id`) REFERENCES `service_models` (`id`) ON DELETE SET NULL,
  FOREIGN KEY (`accommodation_type_id`) REFERENCES `accommodation_types` (`id`) ON DELETE SET NULL,
  FOREIGN KEY (`preferred_nationality_id`) REFERENCES `nationalities` (`id`) ON DELETE SET NULL,
  FOREIGN KEY (`city_id`) REFERENCES `cities` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `quotation_request_specialties` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `quotation_request_id` bigint(20) UNSIGNED NOT NULL,
  `specialty_id` bigint(20) UNSIGNED NOT NULL,
  KEY `quotation_request_id` (`quotation_request_id`),
  KEY `specialty_id` (`specialty_id`),
  FOREIGN KEY (`quotation_request_id`) REFERENCES `quotation_requests` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`specialty_id`) REFERENCES `specialties` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `quotation_request_skills` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `quotation_request_id` bigint(20) UNSIGNED NOT NULL,
  `skill_id` bigint(20) UNSIGNED NOT NULL,
  KEY `quotation_request_id` (`quotation_request_id`),
  KEY `skill_id` (`skill_id`),
  FOREIGN KEY (`quotation_request_id`) REFERENCES `quotation_requests` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`skill_id`) REFERENCES `skills` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `quotations` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `quotation_request_id` bigint(20) UNSIGNED NOT NULL,
  `vendor_id` bigint(20) UNSIGNED NOT NULL,
  `customer_id` bigint(20) UNSIGNED NOT NULL,
  `maid_id` bigint(20) UNSIGNED DEFAULT NULL,
  `base_salary` decimal(10,2) DEFAULT 0.00,
  `visa_processing` decimal(10,2) DEFAULT 0.00,
  `health_insurance` decimal(10,2) DEFAULT 0.00,
  `service_fee` decimal(10,2) DEFAULT 0.00,
  `total_amount` decimal(10,2) DEFAULT 0.00,
  `currency` varchar(10) DEFAULT 'AED',
  `notes` text DEFAULT NULL,
  `valid_until` date DEFAULT NULL,
  `status` enum('draft','sent','accepted','declined','expired','signed') DEFAULT 'draft',
  `sent_at` timestamp NULL DEFAULT NULL,
  `accepted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL,
  KEY `quotation_request_id` (`quotation_request_id`),
  KEY `vendor_id` (`vendor_id`),
  KEY `customer_id` (`customer_id`),
  KEY `maid_id` (`maid_id`),
  FOREIGN KEY (`quotation_request_id`) REFERENCES `quotation_requests` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`maid_id`) REFERENCES `maids` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `quotation_inclusions` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `quotation_id` bigint(20) UNSIGNED NOT NULL,
  `title` varchar(150) NOT NULL,
  `description` text DEFAULT NULL,
  `is_included` tinyint(1) DEFAULT 1,
  KEY `quotation_id` (`quotation_id`),
  FOREIGN KEY (`quotation_id`) REFERENCES `quotations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================
-- 11. CONTRACTS
-- =========================
DROP TABLE IF EXISTS `contract_status_logs`;
DROP TABLE IF EXISTS `contracts`;
CREATE TABLE IF NOT EXISTS `contracts` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `quotation_id` bigint(20) UNSIGNED DEFAULT NULL,
  `quotation_request_id` bigint(20) UNSIGNED NOT NULL,
  `customer_id` bigint(20) UNSIGNED NOT NULL,
  `vendor_id` bigint(20) UNSIGNED NOT NULL,
  `maid_id` bigint(20) UNSIGNED DEFAULT NULL,
  `contract_number` varchar(100) DEFAULT NULL UNIQUE,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `duration_months` int(11) DEFAULT NULL,
  `monthly_salary` decimal(10,2) DEFAULT 0.00,
  `total_amount` decimal(10,2) DEFAULT 0.00,
  `currency` varchar(10) DEFAULT 'AED',
  `terms` text DEFAULT NULL,
  `status` enum('pending','approved','active','completed','cancelled','rejected') DEFAULT 'pending',
  `signed_by_customer_at` timestamp NULL DEFAULT NULL,
  `signed_by_vendor_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL,
  KEY `quotation_id` (`quotation_id`),
  KEY `quotation_request_id` (`quotation_request_id`),
  KEY `customer_id` (`customer_id`),
  KEY `vendor_id` (`vendor_id`),
  KEY `maid_id` (`maid_id`),
  FOREIGN KEY (`quotation_id`) REFERENCES `quotations` (`id`) ON DELETE SET NULL,
  FOREIGN KEY (`quotation_request_id`) REFERENCES `quotation_requests` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`maid_id`) REFERENCES `maids` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `contract_status_logs` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `contract_id` bigint(20) UNSIGNED NOT NULL,
  `status` varchar(100) NOT NULL,
  `note` text DEFAULT NULL,
  `created_by_type` enum('admin','customer','vendor') DEFAULT NULL,
  `created_by_id` bigint(20) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  KEY `contract_id` (`contract_id`),
  FOREIGN KEY (`contract_id`) REFERENCES `contracts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================
-- 12. SUBSCRIPTIONS & ADS
-- =========================
DROP TABLE IF EXISTS `subscription_plan_features`;
DROP TABLE IF EXISTS `vendor_subscriptions`;
DROP TABLE IF EXISTS `subscription_plans`;
DROP TABLE IF EXISTS `vendor_ads`;
DROP TABLE IF EXISTS `ad_packages`;

CREATE TABLE IF NOT EXISTS `subscription_plans` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name_en` varchar(100) NOT NULL,
  `name_ar` varchar(100) DEFAULT NULL,
  `price` decimal(10,2) NOT NULL,
  `currency` varchar(10) DEFAULT 'AED',
  `billing_cycle` enum('monthly','yearly') DEFAULT 'monthly',
  `max_maids` int(11) DEFAULT NULL,
  `interview_requests_limit` int(11) DEFAULT NULL,
  `ads_quota` int(11) DEFAULT 0,
  `is_popular` tinyint(1) DEFAULT 0,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `subscription_plans` (`id`, `name_en`, `name_ar`, `price`, `billing_cycle`, `max_maids`, `interview_requests_limit`, `ads_quota`, `is_popular`, `status`) VALUES
(1, 'Basic Starter', 'الباقة الأساسية', 299.00, 'monthly', 10, 20, 1, 0, 'active'),
(2, 'Pro Agency', 'الباقة الاحترافية', 599.00, 'monthly', 50, 100, 5, 1, 'active'),
(3, 'Enterprise Unlimited', 'الباقة الذهبية', 999.00, 'monthly', NULL, NULL, 15, 0, 'active');

CREATE TABLE IF NOT EXISTS `subscription_plan_features` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `plan_id` bigint(20) UNSIGNED NOT NULL,
  `feature_text_en` varchar(255) NOT NULL,
  `feature_text_ar` varchar(255) DEFAULT NULL,
  `sort_order` int(11) DEFAULT 0,
  KEY `plan_id` (`plan_id`),
  FOREIGN KEY (`plan_id`) REFERENCES `subscription_plans` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `vendor_subscriptions` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `vendor_id` bigint(20) UNSIGNED NOT NULL,
  `plan_id` bigint(20) UNSIGNED NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `auto_renew` tinyint(1) DEFAULT 1,
  `status` enum('active','expired','cancelled','pending_payment') DEFAULT 'pending_payment',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  KEY `vendor_id` (`vendor_id`),
  KEY `plan_id` (`plan_id`),
  FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`plan_id`) REFERENCES `subscription_plans` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ad_packages` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name_en` varchar(120) NOT NULL,
  `name_ar` varchar(120) DEFAULT NULL,
  `price` decimal(10,2) DEFAULT 0.00,
  `duration_days` int(11) DEFAULT 30,
  `description` text DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `ad_packages` (`id`, `name_en`, `name_ar`, `price`, `duration_days`, `description`, `status`) VALUES
(1, 'Standard Feature (30 Days)', 'إعلان تميز أساسي (30 يوم)', 150.00, 30, 'Promote profile on homepage and search results for 30 days', 'active');

CREATE TABLE IF NOT EXISTS `vendor_ads` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `vendor_id` bigint(20) UNSIGNED NOT NULL,
  `maid_id` bigint(20) UNSIGNED NOT NULL,
  `ad_package_id` bigint(20) UNSIGNED DEFAULT NULL,
  `title` varchar(180) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `impressions` int(11) DEFAULT 0,
  `clicks` int(11) DEFAULT 0,
  `status` enum('pending','active','expired','rejected') DEFAULT 'pending',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  KEY `vendor_id` (`vendor_id`),
  KEY `maid_id` (`maid_id`),
  KEY `ad_package_id` (`ad_package_id`),
  FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`maid_id`) REFERENCES `maids` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`ad_package_id`) REFERENCES `ad_packages` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================
-- 13. PAYMENTS & REVIEWS
-- =========================
DROP TABLE IF EXISTS `vendor_payment_methods`;
DROP TABLE IF EXISTS `payment_methods`;
DROP TABLE IF EXISTS `payments`;
DROP TABLE IF EXISTS `reviews`;

CREATE TABLE IF NOT EXISTS `payment_methods` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(100) NOT NULL,
  `type` enum('card','apple_pay','google_pay','cash','bank_transfer') NOT NULL,
  `status` enum('active','inactive') DEFAULT 'active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `vendor_payment_methods` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `vendor_id` bigint(20) UNSIGNED NOT NULL,
  `payment_method_id` bigint(20) UNSIGNED NOT NULL,
  `card_last_four` varchar(10) DEFAULT NULL,
  `card_brand` varchar(50) DEFAULT NULL,
  `expiry_month` varchar(10) DEFAULT NULL,
  `expiry_year` varchar(10) DEFAULT NULL,
  `is_default` tinyint(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  KEY `vendor_id` (`vendor_id`),
  KEY `payment_method_id` (`payment_method_id`),
  FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`payment_method_id`) REFERENCES `payment_methods` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `payments` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `payer_type` enum('customer','vendor') NOT NULL,
  `payer_id` bigint(20) UNSIGNED NOT NULL,
  `related_type` enum('subscription','ad','contract','quotation') NOT NULL,
  `related_id` bigint(20) UNSIGNED NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `currency` varchar(10) DEFAULT 'AED',
  `payment_method` varchar(100) DEFAULT NULL,
  `transaction_reference` varchar(180) DEFAULT NULL,
  `status` enum('pending','paid','failed','refunded') DEFAULT 'pending',
  `paid_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `reviews` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `customer_id` bigint(20) UNSIGNED NOT NULL,
  `vendor_id` bigint(20) UNSIGNED DEFAULT NULL,
  `maid_id` bigint(20) UNSIGNED DEFAULT NULL,
  `rating` decimal(2,1) NOT NULL,
  `comment` text DEFAULT NULL,
  `status` enum('pending','approved','rejected') DEFAULT 'approved',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  KEY `customer_id` (`customer_id`),
  KEY `vendor_id` (`vendor_id`),
  KEY `maid_id` (`maid_id`),
  FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`maid_id`) REFERENCES `maids` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================
-- 14. NOTIFICATIONS & CHAT
-- =========================
DROP TABLE IF EXISTS `notifications`;
DROP TABLE IF EXISTS `device_tokens`;
DROP TABLE IF EXISTS `chat_references`;

CREATE TABLE IF NOT EXISTS `notifications` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `receiver_type` enum('customer','vendor','admin') NOT NULL,
  `receiver_id` bigint(20) UNSIGNED NOT NULL,
  `title` varchar(180) NOT NULL,
  `body` text DEFAULT NULL,
  `type` varchar(100) DEFAULT NULL,
  `related_type` varchar(100) DEFAULT NULL,
  `related_id` bigint(20) UNSIGNED DEFAULT NULL,
  `is_read` tinyint(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `device_tokens` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `user_type` enum('customer','vendor') NOT NULL,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `device_token` text NOT NULL,
  `platform` enum('android','ios','web') DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `customer_notification_settings` (
  `customer_id` bigint(20) UNSIGNED NOT NULL PRIMARY KEY,
  `enable_push` tinyint(1) DEFAULT 1,
  `booking_updates` tinyint(1) DEFAULT 1,
  `messages` tinyint(1) DEFAULT 1,
  `account_activity` tinyint(1) DEFAULT 1,
  `updated_at` timestamp NULL DEFAULT NULL,
  FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `chat_references` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `firebase_chat_id` varchar(180) NOT NULL UNIQUE,
  `customer_id` bigint(20) UNSIGNED NOT NULL,
  `vendor_id` bigint(20) UNSIGNED NOT NULL,
  `maid_id` bigint(20) UNSIGNED DEFAULT NULL,
  `quotation_request_id` bigint(20) UNSIGNED DEFAULT NULL,
  `contract_id` bigint(20) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  KEY `customer_id` (`customer_id`),
  KEY `vendor_id` (`vendor_id`),
  KEY `maid_id` (`maid_id`),
  KEY `quotation_request_id` (`quotation_request_id`),
  KEY `contract_id` (`contract_id`),
  FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`maid_id`) REFERENCES `maids` (`id`) ON DELETE SET NULL,
  FOREIGN KEY (`quotation_request_id`) REFERENCES `quotation_requests` (`id`) ON DELETE SET NULL,
  FOREIGN KEY (`contract_id`) REFERENCES `contracts` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================
-- 15. CMS & SETTINGS
-- =========================
DROP TABLE IF EXISTS `faqs`;
DROP TABLE IF EXISTS `faq_categories`;
DROP TABLE IF EXISTS `app_snippets`;
DROP TABLE IF EXISTS `app_sliders`;
DROP TABLE IF EXISTS `onboarding_screens`;
DROP TABLE IF EXISTS `home_sections`;
DROP TABLE IF EXISTS `support_tickets`;
DROP TABLE IF EXISTS `app_settings`;
DROP TABLE IF EXISTS `audit_logs`;

CREATE TABLE IF NOT EXISTS `app_sliders` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `title_en` varchar(180) DEFAULT NULL,
  `title_ar` varchar(180) DEFAULT NULL,
  `badge_en` varchar(100) DEFAULT NULL,
  `badge_ar` varchar(100) DEFAULT NULL,
  `subtitle_en` text DEFAULT NULL,
  `subtitle_ar` text DEFAULT NULL,
  `button_text_en` varchar(100) DEFAULT NULL,
  `button_text_ar` varchar(100) DEFAULT NULL,
  `image` varchar(255) NOT NULL,
  `target_type` varchar(100) DEFAULT NULL,
  `target_id` bigint(20) UNSIGNED DEFAULT NULL,
  `target_url` varchar(255) DEFAULT NULL,
  `sort_order` int(11) DEFAULT 0,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `app_snippets` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `title_en` varchar(180) DEFAULT NULL,
  `title_ar` varchar(180) DEFAULT NULL,
  `image` varchar(255) NOT NULL,
  `sort_order` int(11) DEFAULT 0,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `onboarding_screens` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `user_type` enum('customer','vendor') NOT NULL,
  `title_en` varchar(180) NOT NULL,
  `title_ar` varchar(180) DEFAULT NULL,
  `description_en` text DEFAULT NULL,
  `description_ar` text DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `sort_order` int(11) DEFAULT 0,
  `status` enum('active','inactive') DEFAULT 'active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `onboarding_screens` (`id`, `user_type`, `title_en`, `title_ar`, `description_en`, `description_ar`, `image`, `sort_order`, `status`) VALUES
(1, 'customer', 'Welcome to HelperLink', 'مرحباً بك في هلبر لينك', 'Find the best domestic helpers easily.', 'اعثر على أفضل العاملات المنزليات بسهولة.', 'onboarding_1.png', 1, 'active'),
(2, 'customer', 'Trusted Vendors', 'وكالات موثوقة', 'We partner with verified agencies.', 'نتعامل مع وكالات معتمدة وموثوقة.', 'onboarding_2.png', 2, 'active'),
(3, 'customer', 'Easy Process', 'إجراءات سهلة', 'Interview, request quotation, and sign contract in app.', 'قم بالمقابلة وطلب عرض السعر وتوقيع العقد داخل التطبيق.', 'onboarding_3.png', 3, 'active');

CREATE TABLE IF NOT EXISTS `home_sections` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `user_type` enum('customer','vendor') NOT NULL,
  `title_en` varchar(180) NOT NULL,
  `title_ar` varchar(180) DEFAULT NULL,
  `section_key` varchar(100) NOT NULL,
  `sort_order` int(11) DEFAULT 0,
  `status` enum('active','inactive') DEFAULT 'active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `faq_categories` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name_en` varchar(150) NOT NULL,
  `name_ar` varchar(150) DEFAULT NULL,
  `description_en` varchar(255) DEFAULT NULL,
  `description_ar` varchar(255) DEFAULT NULL,
  `icon` varchar(100) DEFAULT NULL,
  `sort_order` int(11) DEFAULT 0,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `faq_categories` (`id`, `name_en`, `name_ar`, `description_en`, `description_ar`, `icon`, `sort_order`, `status`) VALUES
(1, 'Contracts & Legal', 'العقود والشؤون القانونية', 'Visas, Sponsorships', 'التأشيرات، الكفالات', 'fas fa-file-contract', 1, 'active');

CREATE TABLE IF NOT EXISTS `faqs` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `category_id` bigint(20) UNSIGNED DEFAULT NULL,
  `category` varchar(120) DEFAULT NULL,
  `question_en` varchar(255) NOT NULL,
  `question_ar` varchar(255) DEFAULT NULL,
  `answer_en` text NOT NULL,
  `answer_ar` text DEFAULT NULL,
  `sort_order` int(11) DEFAULT 0,
  `status` enum('active','inactive') DEFAULT 'active',
  KEY `fk_faqs_category` (`category_id`),
  FOREIGN KEY (`category_id`) REFERENCES `faq_categories` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `support_tickets` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `user_type` enum('customer','vendor') NOT NULL,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `subject` varchar(180) NOT NULL,
  `message` text NOT NULL,
  `status` enum('open','in_progress','closed') DEFAULT 'open',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `app_settings` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `setting_key` varchar(150) NOT NULL UNIQUE,
  `setting_value` text DEFAULT NULL,
  `setting_type` enum('text','number','boolean','json') DEFAULT 'text',
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `app_settings` (`setting_key`, `setting_value`, `setting_type`) VALUES
('app_name', 'HelperLink', 'text'),
('support_email', 'support@helperlink.com', 'text'),
('support_phone', '+971500000000', 'text'),
('terms_conditions', 'Terms and conditions text here...', 'text');

CREATE TABLE IF NOT EXISTS `audit_logs` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `admin_id` bigint(20) UNSIGNED DEFAULT NULL,
  `action` varchar(255) NOT NULL,
  `target_type` varchar(50) NOT NULL,
  `target_id` bigint(20) UNSIGNED DEFAULT NULL,
  `details` text DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  KEY `admin_id` (`admin_id`),
  FOREIGN KEY (`admin_id`) REFERENCES `admins` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;