-- ALADIN B2B Plus Ultimate Platform - Complete Database Schema
-- Version: 2.0.0 with Global Geodata and Complete Translations
-- Author: ALADIN B2B Plus Ultimate Platform Team
-- Date: 2024


-- =====================================================
-- CORE SYSTEM TABLES
-- =====================================================

-- Countries table with comprehensive data
CREATE TABLE IF NOT EXISTS `countries` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `iso_code` varchar(2) NOT NULL,
  `iso3_code` varchar(3) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `native_name` text DEFAULT NULL,
  `capital` varchar(255) DEFAULT NULL,
  `region` varchar(100) DEFAULT NULL,
  `subregion` varchar(100) DEFAULT NULL,
  `population` bigint(20) DEFAULT NULL,
  `area` decimal(15,2) DEFAULT NULL,
  `languages` json DEFAULT NULL,
  `currencies` json DEFAULT NULL,
  `timezones` json DEFAULT NULL,
  `borders` json DEFAULT NULL,
  `latitude` decimal(10,8) DEFAULT NULL,
  `longitude` decimal(11,8) DEFAULT NULL,
  `flag_emoji` varchar(10) DEFAULT NULL,
  `flag_svg` text DEFAULT NULL,
  `coat_of_arms` text DEFAULT NULL,
  `phone_code` varchar(10) DEFAULT NULL,
  `tld` varchar(10) DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `countries_iso_code_unique` (`iso_code`),
  KEY `countries_region_index` (`region`),
  KEY `countries_status_index` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Postal codes and localities table
CREATE TABLE IF NOT EXISTS `postal_codes_localities` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `country_code` varchar(2) NOT NULL,
  `postal_code` varchar(20) NOT NULL,
  `locality` varchar(255) NOT NULL,
  `region` varchar(255) DEFAULT NULL,
  `type` enum('city','town','village','district','arrondissement','emirate','municipality') DEFAULT 'city',
  `latitude` decimal(10,8) DEFAULT NULL,
  `longitude` decimal(11,8) DEFAULT NULL,
  `status` enum('active','inactive','deleted') DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `postal_codes_country_code_index` (`country_code`),
  KEY `postal_codes_postal_code_index` (`postal_code`),
  KEY `postal_codes_locality_index` (`locality`),
  KEY `postal_codes_coordinates_index` (`latitude`,`longitude`),
  KEY `postal_codes_status_index` (`status`),
  FOREIGN KEY (`country_code`) REFERENCES `countries` (`iso_code`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Timezones table
CREATE TABLE IF NOT EXISTS `timezones` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `timezone_code` varchar(10) NOT NULL,
  `timezone_name` varchar(255) NOT NULL,
  `utc_offset` varchar(10) NOT NULL,
  `dst_offset` varchar(10) DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `timezones_code_unique` (`timezone_code`),
  KEY `timezones_status_index` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Currencies table
CREATE TABLE IF NOT EXISTS `currencies` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `currency_code` varchar(3) NOT NULL,
  `currency_name` varchar(255) NOT NULL,
  `currency_symbol` varchar(10) DEFAULT NULL,
  `decimal_places` tinyint(4) DEFAULT 2,
  `exchange_rate` decimal(15,8) DEFAULT 1.00000000,
  `last_updated` timestamp NULL DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `currencies_code_unique` (`currency_code`),
  KEY `currencies_status_index` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- User profiles table
CREATE TABLE IF NOT EXISTS `user_profiles` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `company_name` varchar(255) DEFAULT NULL,
  `company_registration` varchar(255) DEFAULT NULL,
  `tax_number` varchar(255) DEFAULT NULL,
  `website` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `business_type` enum('individual','company','corporation','partnership') DEFAULT 'individual',
  `industry` varchar(255) DEFAULT NULL,
  `employee_count` varchar(50) DEFAULT NULL,
  `annual_revenue` varchar(50) DEFAULT NULL,
  `verification_status` enum('unverified','pending','verified','rejected') DEFAULT 'unverified',
  `verification_documents` json DEFAULT NULL,
  `kyc_status` enum('not_started','pending','approved','rejected') DEFAULT 'not_started',
  `kyc_documents` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_profiles_user_id_unique` (`user_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- EXHIBITION AND TRADE FAIR TABLES
-- =====================================================

-- Exhibitions table
CREATE TABLE IF NOT EXISTS `exhibitions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(36) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `country_code` varchar(2) NOT NULL,
  `city` varchar(255) NOT NULL,
  `address` text NOT NULL,
  `postal_code` varchar(20) DEFAULT NULL,
  `latitude` decimal(10,8) DEFAULT NULL,
  `longitude` decimal(11,8) DEFAULT NULL,
  `total_area` decimal(10,2) DEFAULT NULL,
  `available_area` decimal(10,2) DEFAULT NULL,
  `total_booths` int(11) DEFAULT 0,
  `available_booths` int(11) DEFAULT 0,
  `opening_hours` json DEFAULT NULL,
  `contact_info` json DEFAULT NULL,
  `facilities` json DEFAULT NULL,
  `images` json DEFAULT NULL,
  `status` enum('active','inactive','maintenance','closed') DEFAULT 'active',
  `managed_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `exhibitions_uuid_unique` (`uuid`),
  KEY `exhibitions_country_code_index` (`country_code`),
  KEY `exhibitions_city_index` (`city`),
  KEY `exhibitions_status_index` (`status`),
  KEY `exhibitions_managed_by_index` (`managed_by`),
  FOREIGN KEY (`country_code`) REFERENCES `countries` (`iso_code`) ON DELETE CASCADE,
  FOREIGN KEY (`managed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Exhibition booths table
CREATE TABLE IF NOT EXISTS `exhibition_booths` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `exhibition_id` bigint(20) unsigned NOT NULL,
  `booth_number` varchar(50) NOT NULL,
  `size_category` enum('small','medium','large','premium','custom') NOT NULL,
  `width` decimal(5,2) NOT NULL,
  `height` decimal(5,2) NOT NULL,
  `area` decimal(8,2) NOT NULL,
  `position_x` decimal(8,2) DEFAULT NULL,
  `position_y` decimal(8,2) DEFAULT NULL,
  `floor_level` int(11) DEFAULT 1,
  `features` json DEFAULT NULL,
  `base_price_monthly` decimal(10,2) NOT NULL,
  `utilities_included` json DEFAULT NULL,
  `max_occupancy` int(11) DEFAULT 1,
  `status` enum('available','occupied','reserved','maintenance','disabled') DEFAULT 'available',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `exhibition_booths_exhibition_booth_unique` (`exhibition_id`,`booth_number`),
  KEY `exhibition_booths_size_category_index` (`size_category`),
  KEY `exhibition_booths_status_index` (`status`),
  FOREIGN KEY (`exhibition_id`) REFERENCES `exhibitions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Exhibition bookings table
CREATE TABLE IF NOT EXISTS `exhibition_bookings` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(36) NOT NULL,
  `exhibition_id` bigint(20) unsigned NOT NULL,
  `booth_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `booking_type` enum('monthly','quarterly','yearly','permanent') DEFAULT 'monthly',
  `start_date` date NOT NULL,
  `end_date` date DEFAULT NULL,
  `monthly_price` decimal(10,2) NOT NULL,
  `total_amount` decimal(12,2) NOT NULL,
  `currency` varchar(3) DEFAULT 'USD',
  `payment_status` enum('pending','paid','partial','overdue','cancelled') DEFAULT 'pending',
  `booking_status` enum('pending','confirmed','active','expired','cancelled') DEFAULT 'pending',
  `auto_renewal` boolean DEFAULT FALSE,
  `special_requirements` text DEFAULT NULL,
  `contract_terms` json DEFAULT NULL,
  `approved_by` bigint(20) unsigned DEFAULT NULL,
  `approved_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `exhibition_bookings_uuid_unique` (`uuid`),
  KEY `exhibition_bookings_exhibition_id_index` (`exhibition_id`),
  KEY `exhibition_bookings_booth_id_index` (`booth_id`),
  KEY `exhibition_bookings_user_id_index` (`user_id`),
  KEY `exhibition_bookings_booking_status_index` (`booking_status`),
  KEY `exhibition_bookings_payment_status_index` (`payment_status`),
  FOREIGN KEY (`exhibition_id`) REFERENCES `exhibitions` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`booth_id`) REFERENCES `exhibition_booths` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- PRODUCT AND CATALOG TABLES
-- =====================================================

-- Categories table
CREATE TABLE IF NOT EXISTS `categories` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(36) NOT NULL,
  `parent_id` bigint(20) unsigned DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `icon` varchar(255) DEFAULT NULL,
  `sort_order` int(11) DEFAULT 0,
  `is_featured` boolean DEFAULT FALSE,
  `status` enum('active','inactive') DEFAULT 'active',
  `seo_title` varchar(255) DEFAULT NULL,
  `seo_description` text DEFAULT NULL,
  `seo_keywords` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `categories_uuid_unique` (`uuid`),
  UNIQUE KEY `categories_slug_unique` (`slug`),
  KEY `categories_parent_id_index` (`parent_id`),
  KEY `categories_status_index` (`status`),
  KEY `categories_sort_order_index` (`sort_order`),
  FOREIGN KEY (`parent_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Products table
CREATE TABLE IF NOT EXISTS `products` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(36) NOT NULL,
  `seller_id` bigint(20) unsigned NOT NULL,
  `category_id` bigint(20) unsigned DEFAULT NULL,
  `sku` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `short_description` text DEFAULT NULL,
  `description` longtext DEFAULT NULL,
  `specifications` json DEFAULT NULL,
  `images` json DEFAULT NULL,
  `videos` json DEFAULT NULL,
  `documents` json DEFAULT NULL,
  `price` decimal(12,2) NOT NULL,
  `compare_price` decimal(12,2) DEFAULT NULL,
  `cost_price` decimal(12,2) DEFAULT NULL,
  `currency` varchar(3) DEFAULT 'USD',
  `min_order_quantity` int(11) DEFAULT 1,
  `max_order_quantity` int(11) DEFAULT NULL,
  `stock_quantity` int(11) DEFAULT 0,
  `stock_status` enum('in_stock','out_of_stock','low_stock','pre_order') DEFAULT 'in_stock',
  `weight` decimal(8,3) DEFAULT NULL,
  `dimensions` json DEFAULT NULL,
  `shipping_class` varchar(255) DEFAULT NULL,
  `tax_class` varchar(255) DEFAULT NULL,
  `status` enum('draft','active','inactive','archived') DEFAULT 'draft',
  `visibility` enum('public','private','password_protected') DEFAULT 'public',
  `featured` boolean DEFAULT FALSE,
  `digital` boolean DEFAULT FALSE,
  `downloadable` boolean DEFAULT FALSE,
  `virtual` boolean DEFAULT FALSE,
  `seo_title` varchar(255) DEFAULT NULL,
  `seo_description` text DEFAULT NULL,
  `seo_keywords` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `products_uuid_unique` (`uuid`),
  UNIQUE KEY `products_sku_unique` (`sku`),
  UNIQUE KEY `products_slug_unique` (`slug`),
  KEY `products_seller_id_index` (`seller_id`),
  KEY `products_category_id_index` (`category_id`),
  KEY `products_status_index` (`status`),
  KEY `products_featured_index` (`featured`),
  KEY `products_price_index` (`price`),
  FOREIGN KEY (`seller_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Product translations table
CREATE TABLE IF NOT EXISTS `product_translations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` bigint(20) unsigned NOT NULL,
  `language_code` varchar(2) NOT NULL,
  `name` varchar(255) NOT NULL,
  `short_description` text DEFAULT NULL,
  `description` longtext DEFAULT NULL,
  `specifications` json DEFAULT NULL,
  `seo_title` varchar(255) DEFAULT NULL,
  `seo_description` text DEFAULT NULL,
  `seo_keywords` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `product_translations_product_language_unique` (`product_id`,`language_code`),
  KEY `product_translations_language_code_index` (`language_code`),
  FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- ORDER AND TRANSACTION TABLES
-- =====================================================

-- Orders table
CREATE TABLE IF NOT EXISTS `orders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(36) NOT NULL,
  `order_number` varchar(255) NOT NULL,
  `buyer_id` bigint(20) unsigned NOT NULL,
  `seller_id` bigint(20) unsigned DEFAULT NULL,
  `order_type` enum('b2c','b2b','wholesale','dropshipping') DEFAULT 'b2c',
  `status` enum('pending','confirmed','processing','shipped','delivered','cancelled','refunded') DEFAULT 'pending',
  `payment_status` enum('pending','paid','partial','failed','refunded') DEFAULT 'pending',
  `subtotal` decimal(12,2) NOT NULL DEFAULT 0.00,
  `tax_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `shipping_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `discount_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `total_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `currency` varchar(3) DEFAULT 'USD',
  `billing_address` json DEFAULT NULL,
  `shipping_address` json DEFAULT NULL,
  `shipping_method` varchar(255) DEFAULT NULL,
  `tracking_number` varchar(255) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `metadata` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `orders_uuid_unique` (`uuid`),
  UNIQUE KEY `orders_order_number_unique` (`order_number`),
  KEY `orders_buyer_id_index` (`buyer_id`),
  KEY `orders_seller_id_index` (`seller_id`),
  KEY `orders_status_index` (`status`),
  KEY `orders_payment_status_index` (`payment_status`),
  KEY `orders_order_type_index` (`order_type`),
  FOREIGN KEY (`buyer_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`seller_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Order items table
CREATE TABLE IF NOT EXISTS `order_items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) unsigned NOT NULL,
  `product_id` bigint(20) unsigned NOT NULL,
  `product_name` varchar(255) NOT NULL,
  `product_sku` varchar(255) NOT NULL,
  `quantity` int(11) NOT NULL,
  `unit_price` decimal(12,2) NOT NULL,
  `total_price` decimal(12,2) NOT NULL,
  `currency` varchar(3) DEFAULT 'USD',
  `product_data` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `order_items_order_id_index` (`order_id`),
  KEY `order_items_product_id_index` (`product_id`),
  FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- PAYMENT AND FINANCIAL TABLES
-- =====================================================

-- Payment methods table
CREATE TABLE IF NOT EXISTS `payment_methods` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(50) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `provider` varchar(100) NOT NULL,
  `type` enum('credit_card','debit_card','bank_transfer','digital_wallet','cryptocurrency','bnpl','mobile_money') NOT NULL,
  `supported_countries` json DEFAULT NULL,
  `supported_currencies` json DEFAULT NULL,
  `configuration` json DEFAULT NULL,
  `fees` json DEFAULT NULL,
  `limits` json DEFAULT NULL,
  `status` enum('active','inactive','maintenance') DEFAULT 'active',
  `sort_order` int(11) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `payment_methods_code_unique` (`code`),
  KEY `payment_methods_provider_index` (`provider`),
  KEY `payment_methods_type_index` (`type`),
  KEY `payment_methods_status_index` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Payments table
CREATE TABLE IF NOT EXISTS `payments` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(36) NOT NULL,
  `order_id` bigint(20) unsigned DEFAULT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `payment_method_id` bigint(20) unsigned NOT NULL,
  `transaction_id` varchar(255) DEFAULT NULL,
  `external_transaction_id` varchar(255) DEFAULT NULL,
  `amount` decimal(12,2) NOT NULL,
  `currency` varchar(3) NOT NULL,
  `exchange_rate` decimal(15,8) DEFAULT 1.00000000,
  `status` enum('pending','processing','completed','failed','cancelled','refunded') DEFAULT 'pending',
  `gateway_response` json DEFAULT NULL,
  `failure_reason` text DEFAULT NULL,
  `processed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `payments_uuid_unique` (`uuid`),
  KEY `payments_order_id_index` (`order_id`),
  KEY `payments_user_id_index` (`user_id`),
  KEY `payments_payment_method_id_index` (`payment_method_id`),
  KEY `payments_status_index` (`status`),
  KEY `payments_transaction_id_index` (`transaction_id`),
  FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE SET NULL,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`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;

-- =====================================================
-- SHIPPING AND LOGISTICS TABLES
-- =====================================================

-- Shipping carriers table
CREATE TABLE IF NOT EXISTS `shipping_carriers` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(50) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `country_code` varchar(2) DEFAULT NULL,
  `region` varchar(100) DEFAULT NULL,
  `service_types` json DEFAULT NULL,
  `supported_countries` json DEFAULT NULL,
  `tracking_url` varchar(500) DEFAULT NULL,
  `api_configuration` json DEFAULT NULL,
  `pricing_model` enum('flat_rate','weight_based','zone_based','api_calculated') DEFAULT 'flat_rate',
  `status` enum('active','inactive','maintenance') DEFAULT 'active',
  `sort_order` int(11) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `shipping_carriers_code_unique` (`code`),
  KEY `shipping_carriers_country_code_index` (`country_code`),
  KEY `shipping_carriers_region_index` (`region`),
  KEY `shipping_carriers_status_index` (`status`),
  FOREIGN KEY (`country_code`) REFERENCES `countries` (`iso_code`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Shipments table
CREATE TABLE IF NOT EXISTS `shipments` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(36) NOT NULL,
  `order_id` bigint(20) unsigned NOT NULL,
  `carrier_id` bigint(20) unsigned NOT NULL,
  `tracking_number` varchar(255) NOT NULL,
  `service_type` varchar(100) DEFAULT NULL,
  `status` enum('pending','picked_up','in_transit','out_for_delivery','delivered','failed','returned') DEFAULT 'pending',
  `shipped_at` timestamp NULL DEFAULT NULL,
  `estimated_delivery` timestamp NULL DEFAULT NULL,
  `delivered_at` timestamp NULL DEFAULT NULL,
  `shipping_cost` decimal(10,2) DEFAULT NULL,
  `weight` decimal(8,3) DEFAULT NULL,
  `dimensions` json DEFAULT NULL,
  `origin_address` json DEFAULT NULL,
  `destination_address` json DEFAULT NULL,
  `tracking_events` json DEFAULT NULL,
  `metadata` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `shipments_uuid_unique` (`uuid`),
  UNIQUE KEY `shipments_tracking_number_unique` (`tracking_number`),
  KEY `shipments_order_id_index` (`order_id`),
  KEY `shipments_carrier_id_index` (`carrier_id`),
  KEY `shipments_status_index` (`status`),
  FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`carrier_id`) REFERENCES `shipping_carriers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- EMPLOYEE AND HR MANAGEMENT TABLES
-- =====================================================

-- Employees table
CREATE TABLE IF NOT EXISTS `employees` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(36) NOT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `employee_id` varchar(50) NOT NULL,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `department` varchar(100) DEFAULT NULL,
  `position` varchar(100) DEFAULT NULL,
  `employment_type` enum('full_time','part_time','contract','freelance','intern') DEFAULT 'full_time',
  `salary_type` enum('monthly','hourly','commission','mixed') DEFAULT 'monthly',
  `base_salary` decimal(12,2) DEFAULT NULL,
  `hourly_rate` decimal(8,2) DEFAULT NULL,
  `commission_rate` decimal(5,2) DEFAULT NULL,
  `currency` varchar(3) DEFAULT 'USD',
  `hire_date` date DEFAULT NULL,
  `termination_date` date DEFAULT NULL,
  `manager_id` bigint(20) unsigned DEFAULT NULL,
  `country_code` varchar(2) DEFAULT NULL,
  `region` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `territory_restrictions` json DEFAULT NULL,
  `permissions` json DEFAULT NULL,
  `status` enum('active','inactive','terminated','suspended') DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `employees_uuid_unique` (`uuid`),
  UNIQUE KEY `employees_employee_id_unique` (`employee_id`),
  UNIQUE KEY `employees_email_unique` (`email`),
  KEY `employees_user_id_index` (`user_id`),
  KEY `employees_manager_id_index` (`manager_id`),
  KEY `employees_country_code_index` (`country_code`),
  KEY `employees_department_index` (`department`),
  KEY `employees_status_index` (`status`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  FOREIGN KEY (`manager_id`) REFERENCES `employees` (`id`) ON DELETE SET NULL,
  FOREIGN KEY (`country_code`) REFERENCES `countries` (`iso_code`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Employee teams table
CREATE TABLE IF NOT EXISTS `employee_teams` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `team_leader_id` bigint(20) unsigned NOT NULL,
  `team_member_id` bigint(20) unsigned NOT NULL,
  `role` varchar(100) DEFAULT NULL,
  `commission_split` decimal(5,2) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `employee_teams_leader_member_unique` (`team_leader_id`,`team_member_id`),
  KEY `employee_teams_team_leader_id_index` (`team_leader_id`),
  KEY `employee_teams_team_member_id_index` (`team_member_id`),
  KEY `employee_teams_status_index` (`status`),
  FOREIGN KEY (`team_leader_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`team_member_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Payroll table
CREATE TABLE IF NOT EXISTS `payroll` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` bigint(20) unsigned NOT NULL,
  `pay_period_start` date NOT NULL,
  `pay_period_end` date NOT NULL,
  `base_salary` decimal(12,2) DEFAULT NULL,
  `overtime_hours` decimal(8,2) DEFAULT NULL,
  `overtime_rate` decimal(8,2) DEFAULT NULL,
  `overtime_amount` decimal(12,2) DEFAULT NULL,
  `commission_amount` decimal(12,2) DEFAULT NULL,
  `bonus_amount` decimal(12,2) DEFAULT NULL,
  `gross_pay` decimal(12,2) NOT NULL,
  `tax_deductions` decimal(12,2) DEFAULT NULL,
  `social_security` decimal(12,2) DEFAULT NULL,
  `insurance_deductions` decimal(12,2) DEFAULT NULL,
  `other_deductions` decimal(12,2) DEFAULT NULL,
  `total_deductions` decimal(12,2) DEFAULT NULL,
  `net_pay` decimal(12,2) NOT NULL,
  `currency` varchar(3) DEFAULT 'USD',
  `payment_date` date DEFAULT NULL,
  `payment_method` varchar(50) DEFAULT NULL,
  `payment_reference` varchar(255) DEFAULT NULL,
  `status` enum('draft','calculated','approved','paid','cancelled') DEFAULT 'draft',
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `payroll_employee_id_index` (`employee_id`),
  KEY `payroll_pay_period_index` (`pay_period_start`,`pay_period_end`),
  KEY `payroll_status_index` (`status`),
  FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dropshipping products table
CREATE TABLE IF NOT EXISTS `dropshipping_products` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `supplier_id` bigint(20) unsigned NOT NULL,
  `external_id` varchar(255) NOT NULL,
  `sku` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` longtext DEFAULT NULL,
  `images` json DEFAULT NULL,
  `price` decimal(12,2) NOT NULL,
  `compare_price` decimal(12,2) DEFAULT NULL,
  `currency` varchar(3) DEFAULT 'USD',
  `stock_quantity` int(11) DEFAULT 0,
  `min_order_quantity` int(11) DEFAULT 1,
  `weight` decimal(8,3) DEFAULT NULL,
  `dimensions` json DEFAULT NULL,
  `category` varchar(255) DEFAULT NULL,
  `tags` json DEFAULT NULL,
  `variants` json DEFAULT NULL,
  `shipping_info` json DEFAULT NULL,
  `status` enum('active','inactive','out_of_stock') DEFAULT 'active',
  `last_updated` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `dropshipping_products_supplier_external_unique` (`supplier_id`,`external_id`),
  UNIQUE KEY `dropshipping_products_sku_unique` (`sku`),
  KEY `dropshipping_products_supplier_id_index` (`supplier_id`),
  KEY `dropshipping_products_status_index` (`status`),
  KEY `dropshipping_products_category_index` (`category`),
  FOREIGN KEY (`supplier_id`) REFERENCES `dropshipping_suppliers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- COMMUNICATION AND MESSAGING TABLES
-- =====================================================

-- Chat conversations table
CREATE TABLE IF NOT EXISTS `chat_conversations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(36) NOT NULL,
  `type` enum('direct','group','support','business') DEFAULT 'direct',
  `title` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `participants` json NOT NULL,
  `created_by` bigint(20) unsigned NOT NULL,
  `status` enum('active','archived','closed') DEFAULT 'active',
  `last_message_at` timestamp NULL DEFAULT NULL,
  `metadata` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `chat_conversations_uuid_unique` (`uuid`),
  KEY `chat_conversations_created_by_index` (`created_by`),
  KEY `chat_conversations_type_index` (`type`),
  KEY `chat_conversations_status_index` (`status`),
  FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Chat messages table
CREATE TABLE IF NOT EXISTS `chat_messages` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(36) NOT NULL,
  `conversation_id` bigint(20) unsigned NOT NULL,
  `sender_id` bigint(20) unsigned NOT NULL,
  `message_type` enum('text','image','video','audio','file','system') DEFAULT 'text',
  `content` longtext DEFAULT NULL,
  `attachments` json DEFAULT NULL,
  `reply_to_id` bigint(20) unsigned DEFAULT NULL,
  `edited_at` timestamp NULL DEFAULT NULL,
  `read_by` json DEFAULT NULL,
  `metadata` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `chat_messages_uuid_unique` (`uuid`),
  KEY `chat_messages_conversation_id_index` (`conversation_id`),
  KEY `chat_messages_sender_id_index` (`sender_id`),
  KEY `chat_messages_reply_to_id_index` (`reply_to_id`),
  KEY `chat_messages_message_type_index` (`message_type`),
  FOREIGN KEY (`conversation_id`) REFERENCES `chat_conversations` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`sender_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`reply_to_id`) REFERENCES `chat_messages` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- ESCROW AND TRUSTEE TABLES
-- =====================================================

-- Escrow transactions table
CREATE TABLE IF NOT EXISTS `escrow_transactions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(36) NOT NULL,
  `order_id` bigint(20) unsigned DEFAULT NULL,
  `buyer_id` bigint(20) unsigned NOT NULL,
  `seller_id` bigint(20) unsigned NOT NULL,
  `trustee_id` bigint(20) unsigned DEFAULT NULL,
  `amount` decimal(12,2) NOT NULL,
  `currency` varchar(3) NOT NULL,
  `escrow_fee` decimal(12,2) DEFAULT NULL,
  `status` enum('pending','funded','released','disputed','cancelled','refunded') DEFAULT 'pending',
  `terms` text DEFAULT NULL,
  `conditions` json DEFAULT NULL,
  `funded_at` timestamp NULL DEFAULT NULL,
  `released_at` timestamp NULL DEFAULT NULL,
  `dispute_reason` text DEFAULT NULL,
  `resolution` text DEFAULT NULL,
  `resolved_at` timestamp NULL DEFAULT NULL,
  `resolved_by` bigint(20) unsigned DEFAULT NULL,
  `metadata` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `escrow_transactions_uuid_unique` (`uuid`),
  KEY `escrow_transactions_order_id_index` (`order_id`),
  KEY `escrow_transactions_buyer_id_index` (`buyer_id`),
  KEY `escrow_transactions_seller_id_index` (`seller_id`),
  KEY `escrow_transactions_trustee_id_index` (`trustee_id`),
  KEY `escrow_transactions_status_index` (`status`),
  FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE SET NULL,
  FOREIGN KEY (`buyer_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`seller_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`trustee_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  FOREIGN KEY (`resolved_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Marketing campaigns table
CREATE TABLE IF NOT EXISTS `marketing_campaigns` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(36) NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `type` enum('email','social_media','seo','ppc','content','influencer') NOT NULL,
  `platforms` json DEFAULT NULL,
  `target_audience` json DEFAULT NULL,
  `budget` decimal(12,2) DEFAULT NULL,
  `currency` varchar(3) DEFAULT 'USD',
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `status` enum('draft','active','paused','completed','cancelled') DEFAULT 'draft',
  `metrics` json DEFAULT NULL,
  `settings` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `marketing_campaigns_uuid_unique` (`uuid`),
  KEY `marketing_campaigns_user_id_index` (`user_id`),
  KEY `marketing_campaigns_type_index` (`type`),
  KEY `marketing_campaigns_status_index` (`status`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- LICENSING AND SECURITY TABLES
-- =====================================================

-- Licenses table
CREATE TABLE IF NOT EXISTS `licenses` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(36) NOT NULL,
  `license_key` varchar(255) NOT NULL,
  `license_type` enum('master','enterprise','professional','basic','trial') NOT NULL,
  `holder_name` varchar(255) NOT NULL,
  `holder_email` varchar(255) NOT NULL,
  `domain_restrictions` json DEFAULT NULL,
  `feature_restrictions` json DEFAULT NULL,
  `user_limit` int(11) DEFAULT NULL,
  `transaction_limit` int(11) DEFAULT NULL,
  `issued_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `expires_at` timestamp NULL DEFAULT NULL,
  `status` enum('active','expired','suspended','revoked') DEFAULT 'active',
  `hardware_fingerprint` varchar(255) DEFAULT NULL,
  `last_validation` timestamp NULL DEFAULT NULL,
  `validation_count` int(11) DEFAULT 0,
  `metadata` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `licenses_uuid_unique` (`uuid`),
  UNIQUE KEY `licenses_license_key_unique` (`license_key`),
  KEY `licenses_license_type_index` (`license_type`),
  KEY `licenses_status_index` (`status`),
  KEY `licenses_holder_email_index` (`holder_email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- System logs table
CREATE TABLE IF NOT EXISTS `system_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `level` enum('emergency','alert','critical','error','warning','notice','info','debug') NOT NULL,
  `message` text NOT NULL,
  `context` json DEFAULT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `url` varchar(500) DEFAULT NULL,
  `method` varchar(10) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `system_logs_level_index` (`level`),
  KEY `system_logs_user_id_index` (`user_id`),
  KEY `system_logs_created_at_index` (`created_at`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert master license
INSERT IGNORE INTO `licenses` (`uuid`, `license_key`, `license_type`, `holder_name`, `holder_email`, `status`, `issued_at`) VALUES
(UUID(), 'MASTER-OWNER-UNLIMITED-GLOBAL-2024', 'master', 'NETT-TEL LTD', 'admin@nett-tel.com', 'active', NOW());

CREATE INDEX IF NOT EXISTS `idx_products_seller_status` ON `products` (`seller_id`, `status`);
CREATE INDEX IF NOT EXISTS `idx_orders_buyer_status` ON `orders` (`buyer_id`, `status`);
CREATE INDEX IF NOT EXISTS `idx_payments_user_status` ON `payments` (`user_id`, `status`);
CREATE INDEX IF NOT EXISTS `idx_employees_country_status` ON `employees` (`country_code`, `status`);
CREATE INDEX IF NOT EXISTS `idx_exhibitions_country_status` ON `exhibitions` (`country_code`, `status`);
CREATE INDEX IF NOT EXISTS `idx_chat_messages_conversation_created` ON `chat_messages` (`conversation_id`, `created_at`);
CREATE INDEX IF NOT EXISTS `idx_system_logs_level_created` ON `system_logs` (`level`, `created_at`);

-- View for exhibition statistics
CREATE OR REPLACE VIEW `v_exhibition_stats` AS
SELECT 
    e.id,
    e.name,
    e.country_code,
    e.city,
    COUNT(eb.id) as total_booths,
    COUNT(CASE WHEN eb.status = 'available' THEN 1 END) as available_booths,
    COUNT(CASE WHEN eb.status = 'occupied' THEN 1 END) as occupied_booths,
    COUNT(CASE WHEN eb.status = 'reserved' THEN 1 END) as reserved_booths,
    ROUND((COUNT(CASE WHEN eb.status = 'occupied' THEN 1 END) / COUNT(eb.id)) * 100, 2) as occupancy_rate
FROM `exhibitions` e
LEFT JOIN `exhibition_booths` eb ON e.id = eb.exhibition_id
WHERE e.status = 'active'
GROUP BY e.id, e.name, e.country_code, e.city;

-- =====================================================
-- STORED PROCEDURES
-- =====================================================

DELIMITER //

-- Procedure to update exhibition booth availability
CREATE PROCEDURE IF NOT EXISTS `UpdateBoothAvailability`(
    IN exhibition_id BIGINT,
    IN booth_id BIGINT,
    IN new_status ENUM('available','occupied','reserved','maintenance','disabled')
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;
    
    UPDATE `exhibition_booths` 
    SET `status` = new_status, `updated_at` = NOW()
    WHERE `id` = booth_id AND `exhibition_id` = exhibition_id;
    
    COMMIT;
END //

-- Procedure to calculate employee commission
CREATE PROCEDURE IF NOT EXISTS `CalculateEmployeeCommission`(
    IN employee_id BIGINT,
    IN start_date DATE,
    IN end_date DATE,
    OUT commission_amount DECIMAL(12,2)
)
BEGIN
    DECLARE total_sales DECIMAL(12,2) DEFAULT 0;
    DECLARE commission_rate DECIMAL(5,2) DEFAULT 0;
    
    -- Get employee commission rate
    SELECT e.commission_rate INTO commission_rate
    FROM `employees` e
    WHERE e.id = employee_id;
    
    -- Calculate commission
    SET commission_amount = total_sales * (commission_rate / 100);
END //

DELIMITER ;

-- =====================================================
-- TRIGGERS
-- =====================================================

DELIMITER //

-- Trigger to update product search index
CREATE TRIGGER IF NOT EXISTS `tr_products_search_update`
AFTER UPDATE ON `products`
FOR EACH ROW
BEGIN
    IF NEW.status != OLD.status OR NEW.name != OLD.name OR NEW.description != OLD.description THEN
        -- Update search index (placeholder for search engine integration)
        INSERT INTO `system_logs` (`level`, `message`, `context`, `created_at`)
        VALUES ('info', 'Product search index update required', JSON_OBJECT('product_id', NEW.id), NOW());
    END IF;
    END IF;
END //

DELIMITER ;

-- =====================================================
-- FINAL OPTIMIZATIONS
-- =====================================================

-- Optimize tables
OPTIMIZE TABLE `countries`;
OPTIMIZE TABLE `postal_codes_localities`;
OPTIMIZE TABLE `products`;
OPTIMIZE TABLE `orders`;
OPTIMIZE TABLE `exhibitions`;
OPTIMIZE TABLE `employees`;

-- Analyze tables for better query planning
ANALYZE TABLE `countries`;
ANALYZE TABLE `postal_codes_localities`;
ANALYZE TABLE `products`;
ANALYZE TABLE `orders`;
ANALYZE TABLE `exhibitions`;
ANALYZE TABLE `employees`;

-- =====================================================
-- COMPLETION MESSAGE
-- =====================================================

SELECT 'ALADIN B2B Plus Ultimate Platform Database Schema v2.0.0 with Global Geodata and Complete Translations - Installation Complete!' as 'Status';
