-- =====================================================
-- ALADIN B2B Plus Ultimate Platform - Complete Database Schema
-- Version: 2.0.0
-- Date: October 2024
-- Description: Complete MySQL database schema for B2B marketplace
-- =====================================================

START TRANSACTION;

-- Create database if not exists
CREATE DATABASE IF NOT EXISTS `aladin_b2b_ultimate` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `aladin_b2b_ultimate`;

-- User profiles for extended information
CREATE TABLE `user_profiles` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `company_name` varchar(255) DEFAULT NULL,
  `company_type` enum('manufacturer','wholesaler','retailer','trading_company','service_provider') DEFAULT NULL,
  `business_license` varchar(255) DEFAULT NULL,
  `tax_id` varchar(100) DEFAULT NULL,
  `website` varchar(255) DEFAULT NULL,
  `description` text,
  `address_line_1` varchar(255) DEFAULT NULL,
  `address_line_2` varchar(255) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `state` varchar(100) DEFAULT NULL,
  `postal_code` varchar(20) DEFAULT NULL,
  `country` varchar(2) DEFAULT NULL,
  `latitude` decimal(10,8) DEFAULT NULL,
  `longitude` decimal(11,8) DEFAULT NULL,
  `established_year` year DEFAULT NULL,
  `employee_count` int(11) DEFAULT NULL,
  `annual_revenue` decimal(15,2) DEFAULT NULL,
  `main_products` text,
  `main_markets` text,
  `certifications` json DEFAULT NULL,
  `social_media` 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`),
  CONSTRAINT `user_profiles_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- User sessions
CREATE TABLE `user_sessions` (
  `id` varchar(40) NOT NULL,
  `user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text,
  `payload` longtext NOT NULL,
  `last_activity` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_sessions_user_id_index` (`user_id`),
  KEY `user_sessions_last_activity_index` (`last_activity`),
  CONSTRAINT `user_sessions_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- PRODUCT MANAGEMENT TABLES
-- =====================================================

-- Product categories
CREATE TABLE `categories` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `description` text,
  `image` varchar(255) DEFAULT NULL,
  `icon` varchar(100) DEFAULT NULL,
  `parent_id` bigint(20) UNSIGNED DEFAULT NULL,
  `sort_order` int(11) DEFAULT 0,
  `is_active` tinyint(1) DEFAULT 1,
  `meta_title` varchar(255) DEFAULT NULL,
  `meta_description` text,
  `meta_keywords` text,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `categories_slug_unique` (`slug`),
  KEY `categories_parent_id_foreign` (`parent_id`),
  KEY `categories_is_active_index` (`is_active`),
  CONSTRAINT `categories_parent_id_foreign` FOREIGN KEY (`parent_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Products
CREATE TABLE `products` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` varchar(36) NOT NULL,
  `sku` varchar(100) NOT NULL,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `description` longtext,
  `short_description` text,
  `category_id` bigint(20) UNSIGNED NOT NULL,
  `supplier_id` bigint(20) UNSIGNED NOT NULL,
  `brand` varchar(100) DEFAULT NULL,
  `model` varchar(100) DEFAULT NULL,
  `unit_price` decimal(10,2) NOT NULL,
  `min_order_quantity` int(11) DEFAULT 1,
  `max_order_quantity` int(11) DEFAULT NULL,
  `stock_quantity` int(11) DEFAULT 0,
  `weight` decimal(8,2) DEFAULT NULL,
  `dimensions` json DEFAULT NULL,
  `currency` varchar(3) DEFAULT 'USD',
  `lead_time` int(11) DEFAULT NULL,
  `warranty_period` int(11) DEFAULT NULL,
  `origin_country` varchar(2) DEFAULT NULL,
  `hs_code` varchar(20) DEFAULT NULL,
  `certifications` json DEFAULT NULL,
  `specifications` json DEFAULT NULL,
  `features` json DEFAULT NULL,
  `status` enum('draft','active','inactive','out_of_stock','discontinued') DEFAULT 'draft',
  `is_featured` tinyint(1) DEFAULT 0,
  `views_count` int(11) DEFAULT 0,
  `favorites_count` int(11) DEFAULT 0,
  `orders_count` int(11) DEFAULT 0,
  `rating_average` decimal(3,2) DEFAULT 0.00,
  `rating_count` int(11) DEFAULT 0,
  `meta_title` varchar(255) DEFAULT NULL,
  `meta_description` text,
  `meta_keywords` text,
  `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_category_id_foreign` (`category_id`),
  KEY `products_supplier_id_foreign` (`supplier_id`),
  KEY `products_status_index` (`status`),
  KEY `products_is_featured_index` (`is_featured`),
  CONSTRAINT `products_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE,
  CONSTRAINT `products_supplier_id_foreign` FOREIGN KEY (`supplier_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Product images and videos
CREATE TABLE `product_media` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `product_id` bigint(20) UNSIGNED NOT NULL,
  `type` enum('image','video','document','3d_model') NOT NULL,
  `url` varchar(500) NOT NULL,
  `thumbnail_url` varchar(500) DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `alt_text` varchar(255) DEFAULT NULL,
  `description` text,
  `file_size` bigint(20) DEFAULT NULL,
  `mime_type` varchar(100) DEFAULT NULL,
  `sort_order` int(11) DEFAULT 0,
  `is_primary` tinyint(1) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `product_media_product_id_foreign` (`product_id`),
  KEY `product_media_type_index` (`type`),
  CONSTRAINT `product_media_product_id_foreign` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Product variants (size, color, etc.)
CREATE TABLE `product_variants` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `product_id` bigint(20) UNSIGNED NOT NULL,
  `sku` varchar(100) NOT NULL,
  `name` varchar(255) NOT NULL,
  `attributes` json NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `stock_quantity` int(11) DEFAULT 0,
  `weight` decimal(8,2) DEFAULT NULL,
  `dimensions` json DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `product_variants_sku_unique` (`sku`),
  KEY `product_variants_product_id_foreign` (`product_id`),
  CONSTRAINT `product_variants_product_id_foreign` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Product reviews and ratings
CREATE TABLE `product_reviews` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `product_id` bigint(20) UNSIGNED NOT NULL,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `order_id` bigint(20) UNSIGNED DEFAULT NULL,
  `rating` tinyint(4) NOT NULL,
  `title` varchar(255) DEFAULT NULL,
  `review` text,
  `pros` text,
  `cons` text,
  `images` json DEFAULT NULL,
  `is_verified_purchase` tinyint(1) DEFAULT 0,
  `is_approved` tinyint(1) DEFAULT 0,
  `helpful_count` 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`),
  KEY `product_reviews_product_id_foreign` (`product_id`),
  KEY `product_reviews_user_id_foreign` (`user_id`),
  KEY `product_reviews_order_id_foreign` (`order_id`),
  CONSTRAINT `product_reviews_product_id_foreign` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
  CONSTRAINT `product_reviews_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- ORDER MANAGEMENT TABLES
-- =====================================================

-- Orders
CREATE TABLE `orders` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` varchar(36) NOT NULL,
  `order_number` varchar(50) NOT NULL,
  `buyer_id` bigint(20) UNSIGNED NOT NULL,
  `supplier_id` bigint(20) UNSIGNED NOT NULL,
  `status` enum('pending','confirmed','processing','shipped','delivered','cancelled','refunded','disputed') DEFAULT 'pending',
  `payment_status` enum('pending','paid','partially_paid','failed','refunded') DEFAULT 'pending',
  `subtotal` decimal(12,2) NOT NULL,
  `tax_amount` decimal(12,2) DEFAULT 0.00,
  `shipping_amount` decimal(12,2) DEFAULT 0.00,
  `discount_amount` decimal(12,2) DEFAULT 0.00,
  `total_amount` decimal(12,2) NOT NULL,
  `currency` varchar(3) DEFAULT 'USD',
  `payment_method` varchar(50) DEFAULT NULL,
  `payment_reference` varchar(255) DEFAULT NULL,
  `shipping_method` varchar(100) DEFAULT NULL,
  `tracking_number` varchar(100) DEFAULT NULL,
  `notes` text,
  `internal_notes` text,
  `billing_address` json DEFAULT NULL,
  `shipping_address` json DEFAULT NULL,
  `estimated_delivery_date` date DEFAULT NULL,
  `delivered_at` timestamp NULL DEFAULT NULL,
  `cancelled_at` timestamp NULL DEFAULT NULL,
  `cancellation_reason` text,
  `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_foreign` (`buyer_id`),
  KEY `orders_supplier_id_foreign` (`supplier_id`),
  KEY `orders_status_index` (`status`),
  KEY `orders_payment_status_index` (`payment_status`),
  CONSTRAINT `orders_buyer_id_foreign` FOREIGN KEY (`buyer_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `orders_supplier_id_foreign` FOREIGN KEY (`supplier_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Order items
CREATE TABLE `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_variant_id` bigint(20) UNSIGNED DEFAULT NULL,
  `product_name` varchar(255) NOT NULL,
  `product_sku` varchar(100) NOT NULL,
  `quantity` int(11) NOT NULL,
  `unit_price` decimal(10,2) NOT NULL,
  `total_price` decimal(12,2) NOT NULL,
  `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_foreign` (`order_id`),
  KEY `order_items_product_id_foreign` (`product_id`),
  KEY `order_items_product_variant_id_foreign` (`product_variant_id`),
  CONSTRAINT `order_items_order_id_foreign` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  CONSTRAINT `order_items_product_id_foreign` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
  CONSTRAINT `order_items_product_variant_id_foreign` FOREIGN KEY (`product_variant_id`) REFERENCES `product_variants` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Order status history
CREATE TABLE `order_status_history` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) UNSIGNED NOT NULL,
  `status` varchar(50) NOT NULL,
  `comment` text,
  `user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `order_status_history_order_id_foreign` (`order_id`),
  KEY `order_status_history_user_id_foreign` (`user_id`),
  CONSTRAINT `order_status_history_order_id_foreign` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  CONSTRAINT `order_status_history_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- PAYMENT MANAGEMENT TABLES
-- =====================================================

-- Payment methods
CREATE TABLE `payment_methods` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `code` varchar(50) NOT NULL,
  `type` enum('credit_card','debit_card','bank_transfer','digital_wallet','cryptocurrency','buy_now_pay_later','mobile_money') NOT NULL,
  `provider` varchar(100) DEFAULT NULL,
  `countries` json DEFAULT NULL,
  `currencies` json DEFAULT NULL,
  `min_amount` decimal(10,2) DEFAULT NULL,
  `max_amount` decimal(10,2) DEFAULT NULL,
  `fee_percentage` decimal(5,4) DEFAULT 0.0000,
  `fee_fixed` decimal(10,2) DEFAULT 0.00,
  `processing_time` varchar(100) DEFAULT NULL,
  `description` text,
  `logo_url` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `sort_order` int(11) DEFAULT 0,
  `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 `payment_methods_code_unique` (`code`),
  KEY `payment_methods_type_index` (`type`),
  KEY `payment_methods_is_active_index` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Payments
CREATE TABLE `payments` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` varchar(36) NOT NULL,
  `order_id` bigint(20) UNSIGNED NOT NULL,
  `payment_method_id` bigint(20) UNSIGNED NOT NULL,
  `transaction_id` varchar(255) DEFAULT NULL,
  `reference_id` varchar(255) DEFAULT NULL,
  `amount` decimal(12,2) NOT NULL,
  `currency` varchar(3) NOT NULL,
  `exchange_rate` decimal(10,6) DEFAULT 1.000000,
  `fee_amount` decimal(10,2) DEFAULT 0.00,
  `net_amount` decimal(12,2) NOT NULL,
  `status` enum('pending','processing','completed','failed','cancelled','refunded','partially_refunded') DEFAULT 'pending',
  `gateway_response` json DEFAULT NULL,
  `failure_reason` text,
  `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_foreign` (`order_id`),
  KEY `payments_payment_method_id_foreign` (`payment_method_id`),
  KEY `payments_status_index` (`status`),
  CONSTRAINT `payments_order_id_foreign` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  CONSTRAINT `payments_payment_method_id_foreign` FOREIGN KEY (`payment_method_id`) REFERENCES `payment_methods` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Payment refunds
CREATE TABLE `payment_refunds` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `payment_id` bigint(20) UNSIGNED NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `reason` text,
  `status` enum('pending','processing','completed','failed') DEFAULT 'pending',
  `gateway_response` json 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`),
  KEY `payment_refunds_payment_id_foreign` (`payment_id`),
  CONSTRAINT `payment_refunds_payment_id_foreign` FOREIGN KEY (`payment_id`) REFERENCES `payments` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- SHIPPING MANAGEMENT TABLES
-- =====================================================

-- Shipping methods
CREATE TABLE `shipping_methods` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `code` varchar(50) NOT NULL,
  `carrier` varchar(100) NOT NULL,
  `service_type` enum('standard','express','overnight','economy','premium') DEFAULT 'standard',
  `countries_from` json DEFAULT NULL,
  `countries_to` json DEFAULT NULL,
  `min_weight` decimal(8,2) DEFAULT NULL,
  `max_weight` decimal(8,2) DEFAULT NULL,
  `min_dimensions` json DEFAULT NULL,
  `max_dimensions` json DEFAULT NULL,
  `base_cost` decimal(10,2) DEFAULT 0.00,
  `cost_per_kg` decimal(10,2) DEFAULT 0.00,
  `cost_calculation` enum('flat_rate','weight_based','dimension_based','zone_based','api_based') DEFAULT 'flat_rate',
  `estimated_days_min` int(11) DEFAULT NULL,
  `estimated_days_max` int(11) DEFAULT NULL,
  `tracking_available` tinyint(1) DEFAULT 0,
  `insurance_available` tinyint(1) DEFAULT 0,
  `signature_required` tinyint(1) DEFAULT 0,
  `description` text,
  `logo_url` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `sort_order` int(11) DEFAULT 0,
  `api_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 `shipping_methods_code_unique` (`code`),
  KEY `shipping_methods_carrier_index` (`carrier`),
  KEY `shipping_methods_is_active_index` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Shipments
CREATE TABLE `shipments` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` varchar(36) NOT NULL,
  `order_id` bigint(20) UNSIGNED NOT NULL,
  `shipping_method_id` bigint(20) UNSIGNED NOT NULL,
  `tracking_number` varchar(100) DEFAULT NULL,
  `carrier_reference` varchar(100) DEFAULT NULL,
  `status` enum('pending','picked_up','in_transit','out_for_delivery','delivered','failed','returned') DEFAULT 'pending',
  `weight` decimal(8,2) DEFAULT NULL,
  `dimensions` json DEFAULT NULL,
  `shipping_cost` decimal(10,2) DEFAULT 0.00,
  `insurance_cost` decimal(10,2) DEFAULT 0.00,
  `total_cost` decimal(10,2) DEFAULT 0.00,
  `pickup_address` json DEFAULT NULL,
  `delivery_address` json DEFAULT NULL,
  `estimated_delivery_date` date DEFAULT NULL,
  `actual_delivery_date` date DEFAULT NULL,
  `delivery_notes` text,
  `tracking_events` 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`),
  KEY `shipments_order_id_foreign` (`order_id`),
  KEY `shipments_shipping_method_id_foreign` (`shipping_method_id`),
  KEY `shipments_tracking_number_index` (`tracking_number`),
  KEY `shipments_status_index` (`status`),
  CONSTRAINT `shipments_order_id_foreign` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  CONSTRAINT `shipments_shipping_method_id_foreign` FOREIGN KEY (`shipping_method_id`) REFERENCES `shipping_methods` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dropshipping products
CREATE TABLE `dropshipping_products` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `supplier_id` bigint(20) UNSIGNED NOT NULL,
  `product_id` bigint(20) UNSIGNED DEFAULT NULL,
  `external_id` varchar(100) NOT NULL,
  `external_sku` varchar(100) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `description` longtext,
  `category` varchar(255) DEFAULT NULL,
  `brand` varchar(100) DEFAULT NULL,
  `price` decimal(10,2) NOT NULL,
  `compare_price` decimal(10,2) DEFAULT NULL,
  `currency` varchar(3) DEFAULT 'USD',
  `stock_quantity` int(11) DEFAULT 0,
  `min_order_quantity` int(11) DEFAULT 1,
  `weight` decimal(8,2) DEFAULT NULL,
  `dimensions` json DEFAULT NULL,
  `images` json DEFAULT NULL,
  `variants` json DEFAULT NULL,
  `attributes` json DEFAULT NULL,
  `shipping_info` json DEFAULT NULL,
  `is_available` tinyint(1) DEFAULT 1,
  `last_updated_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 `dropshipping_products_supplier_external_unique` (`supplier_id`,`external_id`),
  KEY `dropshipping_products_product_id_foreign` (`product_id`),
  KEY `dropshipping_products_is_available_index` (`is_available`),
  CONSTRAINT `dropshipping_products_supplier_id_foreign` FOREIGN KEY (`supplier_id`) REFERENCES `dropshipping_suppliers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `dropshipping_products_product_id_foreign` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dropshipping orders
CREATE TABLE `dropshipping_orders` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) UNSIGNED NOT NULL,
  `supplier_id` bigint(20) UNSIGNED NOT NULL,
  `external_order_id` varchar(100) DEFAULT NULL,
  `status` enum('pending','submitted','processing','shipped','delivered','cancelled','failed') DEFAULT 'pending',
  `tracking_number` varchar(100) DEFAULT NULL,
  `shipping_cost` decimal(10,2) DEFAULT 0.00,
  `total_cost` decimal(10,2) NOT NULL,
  `supplier_response` json DEFAULT NULL,
  `error_message` text,
  `submitted_at` timestamp NULL DEFAULT NULL,
  `shipped_at` timestamp NULL DEFAULT NULL,
  `delivered_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`),
  KEY `dropshipping_orders_order_id_foreign` (`order_id`),
  KEY `dropshipping_orders_supplier_id_foreign` (`supplier_id`),
  KEY `dropshipping_orders_status_index` (`status`),
  CONSTRAINT `dropshipping_orders_order_id_foreign` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  CONSTRAINT `dropshipping_orders_supplier_id_foreign` FOREIGN KEY (`supplier_id`) REFERENCES `dropshipping_suppliers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- COMMUNICATION TABLES
-- =====================================================

-- Chat conversations
CREATE TABLE `chat_conversations` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` varchar(36) NOT NULL,
  `type` enum('buyer_supplier','support','group') DEFAULT 'buyer_supplier',
  `subject` varchar(255) DEFAULT NULL,
  `status` enum('active','closed','archived') DEFAULT 'active',
  `created_by` bigint(20) UNSIGNED NOT NULL,
  `last_message_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 `chat_conversations_uuid_unique` (`uuid`),
  KEY `chat_conversations_created_by_foreign` (`created_by`),
  KEY `chat_conversations_status_index` (`status`),
  CONSTRAINT `chat_conversations_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Chat participants
CREATE TABLE `chat_participants` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `conversation_id` bigint(20) UNSIGNED NOT NULL,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `role` enum('participant','moderator','admin') DEFAULT 'participant',
  `joined_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `left_at` timestamp NULL DEFAULT NULL,
  `last_read_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `chat_participants_conversation_user_unique` (`conversation_id`,`user_id`),
  KEY `chat_participants_user_id_foreign` (`user_id`),
  CONSTRAINT `chat_participants_conversation_id_foreign` FOREIGN KEY (`conversation_id`) REFERENCES `chat_conversations` (`id`) ON DELETE CASCADE,
  CONSTRAINT `chat_participants_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Chat messages
CREATE TABLE `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` text,
  `message_type` enum('text','image','video','audio','document','system') DEFAULT 'text',
  `attachments` json DEFAULT NULL,
  `metadata` json DEFAULT NULL,
  `is_edited` tinyint(1) DEFAULT 0,
  `edited_at` timestamp NULL DEFAULT NULL,
  `is_deleted` tinyint(1) DEFAULT 0,
  `deleted_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 `chat_messages_uuid_unique` (`uuid`),
  KEY `chat_messages_conversation_id_foreign` (`conversation_id`),
  KEY `chat_messages_sender_id_foreign` (`sender_id`),
  KEY `chat_messages_message_type_index` (`message_type`),
  CONSTRAINT `chat_messages_conversation_id_foreign` FOREIGN KEY (`conversation_id`) REFERENCES `chat_conversations` (`id`) ON DELETE CASCADE,
  CONSTRAINT `chat_messages_sender_id_foreign` FOREIGN KEY (`sender_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Support tickets
CREATE TABLE `support_tickets` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` varchar(36) NOT NULL,
  `ticket_number` varchar(50) NOT NULL,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `assigned_to` bigint(20) UNSIGNED DEFAULT NULL,
  `category` enum('general','technical','billing','shipping','product','account','other') DEFAULT 'general',
  `priority` enum('low','medium','high','urgent') DEFAULT 'medium',
  `status` enum('open','in_progress','waiting_customer','resolved','closed') DEFAULT 'open',
  `subject` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `attachments` json DEFAULT NULL,
  `resolution` text,
  `satisfaction_rating` tinyint(4) DEFAULT NULL,
  `satisfaction_comment` text,
  `resolved_at` timestamp NULL DEFAULT NULL,
  `closed_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 `support_tickets_uuid_unique` (`uuid`),
  UNIQUE KEY `support_tickets_ticket_number_unique` (`ticket_number`),
  KEY `support_tickets_user_id_foreign` (`user_id`),
  KEY `support_tickets_assigned_to_foreign` (`assigned_to`),
  KEY `support_tickets_status_index` (`status`),
  KEY `support_tickets_priority_index` (`priority`),
  CONSTRAINT `support_tickets_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `support_tickets_assigned_to_foreign` FOREIGN KEY (`assigned_to`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- ESCROW TABLES
-- =====================================================

-- Escrow transactions
CREATE TABLE `escrow_transactions` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` varchar(36) NOT NULL,
  `order_id` bigint(20) UNSIGNED NOT NULL,
  `buyer_id` bigint(20) UNSIGNED NOT NULL,
  `supplier_id` bigint(20) UNSIGNED NOT NULL,
  `agent_id` bigint(20) UNSIGNED DEFAULT NULL,
  `amount` decimal(12,2) NOT NULL,
  `currency` varchar(3) NOT NULL,
  `fee_amount` decimal(10,2) DEFAULT 0.00,
  `status` enum('pending','funded','released','disputed','cancelled','refunded') DEFAULT 'pending',
  `terms` text,
  `milestone_conditions` json DEFAULT NULL,
  `funded_at` timestamp NULL DEFAULT NULL,
  `released_at` timestamp NULL DEFAULT NULL,
  `dispute_reason` text,
  `resolution` text,
  `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`),
  UNIQUE KEY `escrow_transactions_order_id_unique` (`order_id`),
  KEY `escrow_transactions_buyer_id_foreign` (`buyer_id`),
  KEY `escrow_transactions_supplier_id_foreign` (`supplier_id`),
  KEY `escrow_transactions_agent_id_foreign` (`agent_id`),
  KEY `escrow_transactions_status_index` (`status`),
  CONSTRAINT `escrow_transactions_order_id_foreign` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  CONSTRAINT `escrow_transactions_buyer_id_foreign` FOREIGN KEY (`buyer_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `escrow_transactions_supplier_id_foreign` FOREIGN KEY (`supplier_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `escrow_transactions_agent_id_foreign` FOREIGN KEY (`agent_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Escrow disputes
CREATE TABLE `escrow_disputes` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `escrow_transaction_id` bigint(20) UNSIGNED NOT NULL,
  `initiated_by` bigint(20) UNSIGNED NOT NULL,
  `reason` enum('product_not_received','product_not_as_described','quality_issues','shipping_issues','other') NOT NULL,
  `description` text NOT NULL,
  `evidence` json DEFAULT NULL,
  `status` enum('open','under_review','resolved','closed') DEFAULT 'open',
  `resolution` text,
  `resolved_by` bigint(20) UNSIGNED DEFAULT NULL,
  `resolved_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`),
  KEY `escrow_disputes_escrow_transaction_id_foreign` (`escrow_transaction_id`),
  KEY `escrow_disputes_initiated_by_foreign` (`initiated_by`),
  KEY `escrow_disputes_resolved_by_foreign` (`resolved_by`),
  KEY `escrow_disputes_status_index` (`status`),
  CONSTRAINT `escrow_disputes_escrow_transaction_id_foreign` FOREIGN KEY (`escrow_transaction_id`) REFERENCES `escrow_transactions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `escrow_disputes_initiated_by_foreign` FOREIGN KEY (`initiated_by`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `escrow_disputes_resolved_by_foreign` FOREIGN KEY (`resolved_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Social media posts
CREATE TABLE `social_media_posts` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `product_id` bigint(20) UNSIGNED DEFAULT NULL,
  `platforms` json NOT NULL,
  `content` text NOT NULL,
  `media_urls` json DEFAULT NULL,
  `hashtags` json DEFAULT NULL,
  `scheduled_at` timestamp NULL DEFAULT NULL,
  `status` enum('draft','scheduled','published','failed') DEFAULT 'draft',
  `post_ids` json DEFAULT NULL,
  `engagement_stats` json DEFAULT NULL,
  `error_message` text,
  `published_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`),
  KEY `social_media_posts_user_id_foreign` (`user_id`),
  KEY `social_media_posts_product_id_foreign` (`product_id`),
  KEY `social_media_posts_status_index` (`status`),
  CONSTRAINT `social_media_posts_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `social_media_posts_product_id_foreign` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Marketing campaigns
CREATE TABLE `marketing_campaigns` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(255) NOT NULL,
  `type` enum('social_media','email','seo','ppc','influencer','content') NOT NULL,
  `status` enum('draft','active','paused','completed','cancelled') DEFAULT 'draft',
  `budget` decimal(10,2) DEFAULT NULL,
  `spent_amount` decimal(10,2) DEFAULT 0.00,
  `target_audience` json DEFAULT NULL,
  `platforms` json DEFAULT NULL,
  `content` json DEFAULT NULL,
  `schedule` json DEFAULT NULL,
  `goals` json DEFAULT NULL,
  `metrics` json DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `marketing_campaigns_user_id_foreign` (`user_id`),
  KEY `marketing_campaigns_type_index` (`type`),
  KEY `marketing_campaigns_status_index` (`status`),
  CONSTRAINT `marketing_campaigns_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Module functions (for on/off switches)
CREATE TABLE `module_functions` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `module_name` varchar(100) NOT NULL,
  `function_name` varchar(100) NOT NULL,
  `display_name` varchar(255) NOT NULL,
  `description` text,
  `is_enabled` tinyint(1) DEFAULT 1,
  `dependencies` json DEFAULT NULL,
  `settings` json DEFAULT NULL,
  `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 `module_functions_module_function_unique` (`module_name`,`function_name`),
  KEY `module_functions_is_enabled_index` (`is_enabled`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- System backups
CREATE TABLE `system_backups` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `type` enum('full','database','files','incremental') NOT NULL,
  `status` enum('pending','running','completed','failed') DEFAULT 'pending',
  `file_path` varchar(500) DEFAULT NULL,
  `file_size` bigint(20) DEFAULT NULL,
  `checksum` varchar(64) DEFAULT NULL,
  `created_by` bigint(20) UNSIGNED DEFAULT NULL,
  `started_at` timestamp NULL DEFAULT NULL,
  `completed_at` timestamp NULL DEFAULT NULL,
  `error_message` text,
  `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`),
  KEY `system_backups_created_by_foreign` (`created_by`),
  KEY `system_backups_status_index` (`status`),
  CONSTRAINT `system_backups_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Audit logs
CREATE TABLE `audit_logs` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `event` varchar(255) NOT NULL,
  `auditable_type` varchar(255) DEFAULT NULL,
  `auditable_id` bigint(20) UNSIGNED DEFAULT NULL,
  `old_values` json DEFAULT NULL,
  `new_values` json DEFAULT NULL,
  `url` varchar(500) DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text,
  `tags` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `audit_logs_user_id_foreign` (`user_id`),
  KEY `audit_logs_auditable_type_auditable_id_index` (`auditable_type`,`auditable_id`),
  KEY `audit_logs_event_index` (`event`),
  CONSTRAINT `audit_logs_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- LOCALIZATION TABLES
-- =====================================================

-- Languages
CREATE TABLE `languages` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` varchar(5) NOT NULL,
  `name` varchar(100) NOT NULL,
  `native_name` varchar(100) NOT NULL,
  `flag` varchar(10) DEFAULT NULL,
  `is_rtl` tinyint(1) DEFAULT 0,
  `is_active` tinyint(1) DEFAULT 1,
  `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 `languages_code_unique` (`code`),
  KEY `languages_is_active_index` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- COMMISSION AND MEMBERSHIP TABLES
-- =====================================================

-- Commission distributions
CREATE TABLE `commission_distributions` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `order_id` bigint(20) UNSIGNED NOT NULL,
  `commission_type` enum('referral','sales','performance','bonus') NOT NULL,
  `percentage` decimal(5,2) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `currency` varchar(3) DEFAULT 'USD',
  `status` enum('pending','paid','cancelled') DEFAULT 'pending',
  `paid_at` timestamp NULL DEFAULT NULL,
  `notes` text,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `commission_distributions_user_id_foreign` (`user_id`),
  KEY `commission_distributions_order_id_foreign` (`order_id`),
  KEY `commission_distributions_status_index` (`status`),
  CONSTRAINT `commission_distributions_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `commission_distributions_order_id_foreign` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Shop memberships
CREATE TABLE `shop_memberships` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `plan_name` varchar(100) NOT NULL,
  `plan_type` enum('basic','premium','gold','platinum','enterprise') NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `currency` varchar(3) DEFAULT 'USD',
  `billing_cycle` enum('monthly','quarterly','yearly','lifetime') NOT NULL,
  `features` json DEFAULT NULL,
  `limits` json DEFAULT NULL,
  `status` enum('active','expired','cancelled','suspended') DEFAULT 'active',
  `starts_at` timestamp NOT NULL,
  `expires_at` timestamp NULL DEFAULT NULL,
  `auto_renew` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `shop_memberships_user_id_foreign` (`user_id`),
  KEY `shop_memberships_status_index` (`status`),
  CONSTRAINT `shop_memberships_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- INSERT DEFAULT DATA
-- =====================================================

-- Insert default languages
INSERT INTO `languages` (`code`, `name`, `native_name`, `flag`, `is_rtl`, `is_active`, `sort_order`) VALUES
('en', 'English', 'English', '🇺🇸', 0, 1, 1),
('de', 'German', 'Deutsch', '🇩🇪', 0, 1, 2),
('fr', 'French', 'Français', '🇫🇷', 0, 1, 3),
('es', 'Spanish', 'Español', '🇪🇸', 0, 1, 4),
('it', 'Italian', 'Italiano', '🇮🇹', 0, 1, 5),
('pt', 'Portuguese', 'Português', '🇵🇹', 0, 1, 6),
('ru', 'Russian', 'Русский', '🇷🇺', 0, 1, 7),
('zh', 'Chinese', '中文', '🇨🇳', 0, 1, 8),
('ja', 'Japanese', '日本語', '🇯🇵', 0, 1, 9),
('ar', 'Arabic', 'العربية', '🇸🇦', 1, 1, 10),
('tr', 'Turkish', 'Türkçe', '🇹🇷', 0, 1, 11),
('nl', 'Dutch', 'Nederlands', '🇳🇱', 0, 1, 12);

-- Insert default categories
INSERT INTO `categories` (`name`, `slug`, `description`, `icon`, `sort_order`, `is_active`) VALUES
('Electronics & Technology', 'electronics-technology', 'Consumer electronics, computers, mobile devices, and technology products', '📱', 1, 1),
('Machinery & Equipment', 'machinery-equipment', 'Industrial machinery, manufacturing equipment, and tools', '⚙️', 2, 1),
('Textiles & Apparel', 'textiles-apparel', 'Clothing, fabrics, fashion accessories, and textile products', '👕', 3, 1),
('Automotive & Transportation', 'automotive-transportation', 'Vehicles, auto parts, transportation equipment, and accessories', '🚗', 4, 1),
('Construction & Real Estate', 'construction-real-estate', 'Building materials, construction equipment, and real estate services', '🏗️', 5, 1),
('Chemicals & Materials', 'chemicals-materials', 'Chemical products, raw materials, and industrial chemicals', '🧪', 6, 1),
('Home & Garden', 'home-garden', 'Home improvement, furniture, garden supplies, and household items', '🏠', 7, 1),
('Health & Medical', 'health-medical', 'Medical equipment, healthcare products, and wellness items', '🏥', 8, 1),
('Food & Beverage', 'food-beverage', 'Food products, beverages, and food processing equipment', '🍎', 9, 1),
('Sports & Entertainment', 'sports-entertainment', 'Sports equipment, entertainment products, and recreational items', '⚽', 10, 1);

-- Insert default payment methods
INSERT INTO `payment_methods` (`name`, `code`, `type`, `provider`, `countries`, `currencies`, `fee_percentage`, `is_active`, `sort_order`) VALUES
('Visa Credit Card', 'visa', 'credit_card', 'Stripe', '["US","CA","GB","DE","FR","AU","JP"]', '["USD","EUR","GBP","CAD","AUD","JPY"]', 2.9000, 1, 1),
('Mastercard', 'mastercard', 'credit_card', 'Stripe', '["US","CA","GB","DE","FR","AU","JP"]', '["USD","EUR","GBP","CAD","AUD","JPY"]', 2.9000, 1, 2),
('PayPal', 'paypal', 'digital_wallet', 'PayPal', '["US","CA","GB","DE","FR","AU","JP","CN"]', '["USD","EUR","GBP","CAD","AUD","JPY","CNY"]', 3.4000, 1, 3),
('Apple Pay', 'apple_pay', 'digital_wallet', 'Stripe', '["US","CA","GB","DE","FR","AU","JP"]', '["USD","EUR","GBP","CAD","AUD","JPY"]', 2.9000, 1, 4),
('Google Pay', 'google_pay', 'digital_wallet', 'Stripe', '["US","CA","GB","DE","FR","AU","JP","IN"]', '["USD","EUR","GBP","CAD","AUD","JPY","INR"]', 2.9000, 1, 5),
('Bitcoin', 'bitcoin', 'cryptocurrency', 'BitPay', '["US","CA","GB","DE","FR","AU","JP"]', '["BTC","USD","EUR"]', 1.0000, 1, 6),
('Ethereum', 'ethereum', 'cryptocurrency', 'BitPay', '["US","CA","GB","DE","FR","AU","JP"]', '["ETH","USD","EUR"]', 1.0000, 1, 7),
('Bank Transfer', 'bank_transfer', 'bank_transfer', 'Wise', '["US","CA","GB","DE","FR","AU","JP"]', '["USD","EUR","GBP","CAD","AUD","JPY"]', 0.5000, 1, 8),
('Alipay', 'alipay', 'digital_wallet', 'Alipay', '["CN","HK","SG","MY"]', '["CNY","USD","EUR"]', 1.2000, 1, 9),
('WeChat Pay', 'wechat_pay', 'digital_wallet', 'WeChat', '["CN","HK","SG","MY"]', '["CNY","USD","EUR"]', 1.2000, 1, 10);

-- Insert default shipping methods
INSERT INTO `shipping_methods` (`name`, `code`, `carrier`, `service_type`, `countries_from`, `countries_to`, `base_cost`, `estimated_days_min`, `estimated_days_max`, `tracking_available`, `is_active`, `sort_order`) VALUES
('DHL Express', 'dhl_express', 'DHL', 'express', '["US","CN","DE","GB","FR"]', '["*"]', 25.00, 1, 3, 1, 1, 1),
('FedEx International', 'fedex_intl', 'FedEx', 'express', '["US","CN","DE","GB","FR"]', '["*"]', 30.00, 2, 5, 1, 1, 2),
('UPS Worldwide', 'ups_worldwide', 'UPS', 'standard', '["US","CN","DE","GB","FR"]', '["*"]', 20.00, 3, 7, 1, 1, 3),
('China Post', 'china_post', 'China Post', 'economy', '["CN"]', '["*"]', 5.00, 15, 30, 1, 1, 4),
('USPS Priority', 'usps_priority', 'USPS', 'standard', '["US"]', '["*"]', 15.00, 5, 10, 1, 1, 5),
('Royal Mail', 'royal_mail', 'Royal Mail', 'standard', '["GB"]', '["*"]', 12.00, 7, 14, 1, 1, 6),
('Deutsche Post', 'deutsche_post', 'Deutsche Post', 'standard', '["DE"]', '["*"]', 10.00, 5, 12, 1, 1, 7),
('La Poste', 'la_poste', 'La Poste', 'standard', '["FR"]', '["*"]', 11.00, 6, 13, 1, 1, 8),
('Japan Post', 'japan_post', 'Japan Post', 'standard', '["JP"]', '["*"]', 18.00, 7, 14, 1, 1, 9),
('Australia Post', 'australia_post', 'Australia Post', 'standard', '["AU"]', '["*"]', 16.00, 8, 15, 1, 1, 10);

-- Insert default module functions
INSERT INTO `module_functions` (`module_name`, `function_name`, `display_name`, `description`, `is_enabled`, `sort_order`) VALUES
('payment', 'credit_cards', 'Credit Card Payments', 'Accept credit card payments via Stripe', 1, 1),
('payment', 'digital_wallets', 'Digital Wallets', 'Accept PayPal, Apple Pay, Google Pay', 1, 2),
('payment', 'cryptocurrencies', 'Cryptocurrency Payments', 'Accept Bitcoin, Ethereum, and other cryptocurrencies', 1, 3),
('payment', 'bank_transfers', 'Bank Transfers', 'Accept direct bank transfers and wire transfers', 1, 4),
('shipping', 'express_shipping', 'Express Shipping', 'DHL, FedEx, UPS express shipping options', 1, 5),
('shipping', 'standard_shipping', 'Standard Shipping', 'Regular postal and courier services', 1, 6),
('shipping', 'tracking', 'Package Tracking', 'Real-time package tracking and notifications', 1, 7),
('dropshipping', 'alibaba_integration', 'Alibaba Integration', 'Connect with Alibaba suppliers', 1, 8),
('dropshipping', 'aliexpress_integration', 'AliExpress Integration', 'Connect with AliExpress suppliers', 1, 9),
('dropshipping', 'auto_fulfillment', 'Auto Fulfillment', 'Automatically fulfill orders through dropshipping', 1, 10),
('social_media', 'facebook_integration', 'Facebook Integration', 'Connect and post to Facebook', 1, 11),
('social_media', 'instagram_integration', 'Instagram Integration', 'Connect and post to Instagram', 1, 12),
('social_media', 'auto_posting', 'Auto Social Media Posting', 'Automatically post products to social media', 1, 13),
('communication', 'live_chat', 'Live Chat', 'Real-time chat between buyers and suppliers', 1, 14),
('communication', 'video_calls', 'Video Calls', 'Video conferencing for business meetings', 1, 15),
('communication', 'file_sharing', 'File Sharing', 'Share documents, images, and files in chat', 1, 16),
('escrow', 'trade_assurance', 'Trade Assurance', 'Secure escrow service for transactions', 1, 17),
('escrow', 'dispute_resolution', 'Dispute Resolution', 'Automated dispute resolution system', 1, 18),
('analytics', 'sales_analytics', 'Sales Analytics', 'Detailed sales and revenue analytics', 1, 19),
('analytics', 'user_behavior', 'User Behavior Analytics', 'Track user behavior and engagement', 1, 20),
('seo', 'auto_seo', 'Auto SEO Optimization', 'Automatically optimize product pages for SEO', 1, 21),
('seo', 'sitemap_generation', 'Sitemap Generation', 'Automatically generate XML sitemaps', 1, 22);

COMMIT;

-- =====================================================
-- END OF SCHEMA
-- =====================================================
