-- ALADIN B2B Plus Ultimate Platform - Complete Database Creation Script
-- This script creates ALL required tables from scratch including exhibitions


-- Create users table
CREATE TABLE IF NOT EXISTS `users` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `password` varchar(255) NOT NULL,
  `remember_token` varchar(100) DEFAULT NULL,
  `role` enum('super_admin','landes_admin','seller','trustee','customer') DEFAULT 'customer',
  `is_active` tinyint(1) DEFAULT 1,
  `phone` varchar(20) DEFAULT NULL,
  `avatar` varchar(255) DEFAULT NULL,
  `company_id` bigint unsigned DEFAULT NULL,
  `last_login_at` timestamp NULL DEFAULT NULL,
  `two_factor_secret` text,
  `two_factor_recovery_codes` text,
  `two_factor_confirmed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`),
  KEY `users_role_index` (`role`),
  KEY `users_is_active_index` (`is_active`),
  KEY `users_company_id_index` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create companies table
CREATE TABLE IF NOT EXISTS `companies` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `legal_name` varchar(255) DEFAULT NULL,
  `description` text,
  `business_type` enum('manufacturer','supplier','distributor','retailer','wholesaler','importer','exporter','trading_company','service_provider') DEFAULT 'manufacturer',
  `industry` varchar(255) DEFAULT NULL,
  `registration_number` varchar(255) DEFAULT NULL,
  `tax_number` varchar(255) DEFAULT NULL,
  `vat_number` varchar(255) DEFAULT NULL,
  `website` varchar(255) DEFAULT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `address` text,
  `city` varchar(255) DEFAULT NULL,
  `state` varchar(255) DEFAULT NULL,
  `country` varchar(255) DEFAULT NULL,
  `postal_code` varchar(20) DEFAULT NULL,
  `logo` varchar(255) DEFAULT NULL,
  `banner` varchar(255) DEFAULT NULL,
  `social_links` json DEFAULT NULL,
  `status` enum('pending','active','suspended','rejected','under_review') DEFAULT 'pending',
  `is_verified` tinyint(1) DEFAULT 0,
  `is_featured` tinyint(1) DEFAULT 0,
  `is_premium` tinyint(1) DEFAULT 0,
  `verified_at` timestamp NULL DEFAULT NULL,
  `employees_count` int DEFAULT NULL,
  `annual_revenue` decimal(15,2) DEFAULT NULL,
  `founded_year` int DEFAULT NULL,
  `business_hours` json DEFAULT NULL,
  `languages_spoken` json DEFAULT NULL,
  `certifications` json DEFAULT NULL,
  `awards` json DEFAULT NULL,
  `payment_terms` json DEFAULT NULL,
  `shipping_methods` json DEFAULT NULL,
  `return_policy` text,
  `warranty_policy` text,
  `minimum_order_value` decimal(10,2) DEFAULT NULL,
  `lead_time` varchar(255) DEFAULT NULL,
  `production_capacity` varchar(255) DEFAULT NULL,
  `quality_standards` json DEFAULT NULL,
  `export_markets` json DEFAULT NULL,
  `main_products` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `companies_slug_unique` (`slug`),
  UNIQUE KEY `companies_email_unique` (`email`),
  KEY `companies_status_index` (`status`),
  KEY `companies_is_verified_index` (`is_verified`),
  KEY `companies_is_featured_index` (`is_featured`),
  KEY `companies_business_type_index` (`business_type`),
  KEY `companies_country_index` (`country`),
  KEY `companies_featured_verified_index` (`is_featured`, `is_verified`),
  KEY `companies_status_verified_index` (`status`, `is_verified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create categories table
CREATE TABLE IF NOT EXISTS `categories` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `description` text,
  `image` varchar(255) DEFAULT NULL,
  `icon` varchar(255) DEFAULT NULL,
  `color` varchar(255) DEFAULT NULL,
  `parent_id` bigint unsigned DEFAULT NULL,
  `sort_order` int DEFAULT 0,
  `is_active` tinyint(1) DEFAULT 1,
  `is_featured` tinyint(1) DEFAULT 0,
  `meta_title` varchar(255) DEFAULT NULL,
  `meta_description` text DEFAULT NULL,
  `meta_keywords` json DEFAULT NULL,
  `products_count` int DEFAULT 0,
  `views_count` int DEFAULT 0,
  `attributes` json DEFAULT NULL,
  `filters` json DEFAULT NULL,
  `commission_rate` decimal(5,2) DEFAULT NULL,
  `min_price` decimal(10,2) DEFAULT NULL,
  `max_price` decimal(10,2) DEFAULT NULL,
  `display_type` enum('grid','list','masonry','carousel') DEFAULT 'grid',
  `banner_image` varchar(255) DEFAULT NULL,
  `featured_image` varchar(255) DEFAULT NULL,
  `gallery_images` json DEFAULT NULL,
  `video_url` varchar(255) DEFAULT NULL,
  `external_url` varchar(255) DEFAULT NULL,
  `custom_fields` json DEFAULT NULL,
  `seo_settings` json DEFAULT NULL,
  `display_settings` json DEFAULT NULL,
  `filter_settings` json DEFAULT NULL,
  `promotion_settings` json DEFAULT NULL,
  `level` int DEFAULT 0,
  `path` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `categories_slug_unique` (`slug`),
  KEY `categories_parent_id_is_active_index` (`parent_id`, `is_active`),
  KEY `categories_is_active_is_featured_index` (`is_active`, `is_featured`),
  KEY `categories_sort_order_name_index` (`sort_order`, `name`),
  KEY `categories_products_count_is_active_index` (`products_count`, `is_active`),
  KEY `categories_views_count_is_active_index` (`views_count`, `is_active`),
  KEY `categories_level_index` (`level`),
  FULLTEXT KEY `categories_name_description_fulltext` (`name`,`description`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create exhibitions table
CREATE TABLE IF NOT EXISTS `exhibitions` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `description` text,
  `short_description` text,
  `venue_name` varchar(255) NOT NULL,
  `venue_address` text,
  `city` varchar(255) NOT NULL,
  `state` varchar(255) DEFAULT NULL,
  `country` varchar(255) NOT NULL,
  `postal_code` varchar(20) DEFAULT NULL,
  `start_date` datetime NOT NULL,
  `end_date` datetime NOT NULL,
  `registration_start` datetime DEFAULT NULL,
  `registration_end` datetime DEFAULT NULL,
  `status` enum('draft','published','registration_open','registration_closed','ongoing','completed','cancelled','postponed') DEFAULT 'draft',
  `type` enum('trade_show','conference','expo','fair','summit','convention','workshop','seminar','networking','product_launch') DEFAULT 'trade_show',
  `is_featured` tinyint(1) DEFAULT 0,
  `is_active` tinyint(1) DEFAULT 1,
  `participation_fee` decimal(10,2) DEFAULT 0.00,
  `currency` varchar(3) DEFAULT 'USD',
  `max_participants` int DEFAULT NULL,
  `current_participants` int DEFAULT 0,
  `categories` json DEFAULT NULL,
  `amenities` json DEFAULT NULL,
  `banner_image` varchar(255) DEFAULT NULL,
  `gallery_images` json DEFAULT NULL,
  `contact_email` varchar(255) DEFAULT NULL,
  `contact_phone` varchar(20) DEFAULT NULL,
  `website` varchar(255) DEFAULT NULL,
  `social_links` json DEFAULT NULL,
  `booth_types` json DEFAULT NULL,
  `pricing_tiers` json DEFAULT NULL,
  `terms_conditions` text,
  `cancellation_policy` text,
  `organizer_name` varchar(255) DEFAULT NULL,
  `organizer_email` varchar(255) DEFAULT NULL,
  `organizer_phone` varchar(20) DEFAULT NULL,
  `target_audience` text,
  `expected_visitors` int DEFAULT NULL,
  `exhibition_size` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `exhibitions_slug_unique` (`slug`),
  KEY `exhibitions_status_is_active_index` (`status`, `is_active`),
  KEY `exhibitions_start_date_end_date_index` (`start_date`, `end_date`),
  KEY `exhibitions_is_featured_start_date_index` (`is_featured`, `start_date`),
  KEY `exhibitions_country_city_index` (`country`, `city`),
  KEY `exhibitions_type_status_index` (`type`, `status`),
  FULLTEXT KEY `exhibitions_name_description_venue_name_city_fulltext` (`name`,`description`,`venue_name`,`city`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create exhibition_participants table
CREATE TABLE IF NOT EXISTS `exhibition_participants` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `exhibition_id` bigint unsigned NOT NULL,
  `company_id` bigint unsigned NOT NULL,
  `booth_number` varchar(255) DEFAULT NULL,
  `booth_size` varchar(255) DEFAULT NULL,
  `booth_type` varchar(255) DEFAULT NULL,
  `participation_type` enum('exhibitor','sponsor','visitor','speaker','media') DEFAULT 'exhibitor',
  `status` enum('pending','confirmed','cancelled','waitlist') DEFAULT 'pending',
  `registration_date` datetime NOT NULL,
  `payment_status` enum('pending','paid','failed','refunded') DEFAULT 'pending',
  `amount_paid` decimal(10,2) DEFAULT 0.00,
  `special_requirements` text,
  `contact_person` varchar(255) DEFAULT NULL,
  `contact_email` varchar(255) DEFAULT NULL,
  `contact_phone` varchar(20) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `exhibition_participants_exhibition_id_company_id_unique` (`exhibition_id`, `company_id`),
  KEY `exhibition_participants_exhibition_id_status_index` (`exhibition_id`, `status`),
  KEY `exhibition_participants_company_id_status_index` (`company_id`, `status`),
  KEY `exhibition_participants_participation_type_index` (`participation_type`),
  KEY `exhibition_participants_payment_status_index` (`payment_status`),
  CONSTRAINT `exhibition_participants_exhibition_id_foreign` FOREIGN KEY (`exhibition_id`) REFERENCES `exhibitions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `exhibition_participants_company_id_foreign` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create products table
CREATE TABLE IF NOT EXISTS `products` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `description` text,
  `short_description` text,
  `sku` varchar(255) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `compare_price` decimal(10,2) DEFAULT NULL,
  `cost_price` decimal(10,2) DEFAULT NULL,
  `stock_quantity` int DEFAULT 0,
  `stock_status` enum('in_stock','out_of_stock','low_stock','backorder') DEFAULT 'in_stock',
  `track_inventory` tinyint(1) DEFAULT 1,
  `allow_backorders` tinyint(1) DEFAULT 0,
  `weight` decimal(8,3) DEFAULT NULL,
  `dimensions` json DEFAULT NULL,
  `status` enum('draft','published','archived','out_of_stock') DEFAULT 'draft',
  `is_active` tinyint(1) DEFAULT 1,
  `is_featured` tinyint(1) DEFAULT 0,
  `is_digital` tinyint(1) DEFAULT 0,
  `product_type` enum('simple','variable','grouped','external','digital') DEFAULT 'simple',
  `category_id` bigint unsigned NOT NULL,
  `company_id` bigint unsigned NOT NULL,
  `created_by` bigint unsigned NOT NULL,
  `updated_by` bigint unsigned DEFAULT NULL,
  `attributes` json DEFAULT NULL,
  `metadata` json DEFAULT NULL,
  `average_rating` decimal(3,2) DEFAULT 0.00,
  `reviews_count` int DEFAULT 0,
  `views_count` int DEFAULT 0,
  `sales_count` int DEFAULT 0,
  `published_at` timestamp NULL DEFAULT NULL,
  `min_order_quantity` int DEFAULT 1,
  `max_order_quantity` int DEFAULT NULL,
  `tax_class` varchar(255) DEFAULT NULL,
  `shipping_class` varchar(255) DEFAULT NULL,
  `warranty_period` int DEFAULT NULL,
  `return_policy` text,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `products_slug_unique` (`slug`),
  UNIQUE KEY `products_sku_unique` (`sku`),
  KEY `products_category_id_foreign` (`category_id`),
  KEY `products_company_id_foreign` (`company_id`),
  KEY `products_created_by_foreign` (`created_by`),
  KEY `products_updated_by_foreign` (`updated_by`),
  KEY `products_status_is_active_index` (`status`, `is_active`),
  KEY `products_category_id_company_id_index` (`category_id`, `company_id`),
  KEY `products_is_featured_published_at_index` (`is_featured`, `published_at`),
  KEY `products_price_stock_status_index` (`price`, `stock_status`),
  FULLTEXT KEY `products_name_description_sku_fulltext` (`name`,`description`,`sku`),
  CONSTRAINT `products_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE,
  CONSTRAINT `products_company_id_foreign` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `products_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `products_updated_by_foreign` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create orders table
CREATE TABLE IF NOT EXISTS `orders` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `order_number` varchar(255) NOT NULL,
  `user_id` bigint unsigned NOT NULL,
  `company_id` bigint unsigned DEFAULT NULL,
  `status` enum('pending','confirmed','processing','shipped','delivered','cancelled','refunded') DEFAULT 'pending',
  `order_type` enum('b2c','b2b') DEFAULT 'b2c',
  `subtotal` decimal(10,2) NOT NULL DEFAULT 0.00,
  `tax_amount` decimal(10,2) DEFAULT 0.00,
  `shipping_amount` decimal(10,2) DEFAULT 0.00,
  `discount_amount` decimal(10,2) DEFAULT 0.00,
  `total_amount` decimal(10,2) NOT NULL,
  `currency` varchar(3) DEFAULT 'USD',
  `payment_status` enum('pending','paid','failed','refunded','partially_refunded') DEFAULT 'pending',
  `payment_method` varchar(255) DEFAULT NULL,
  `shipping_method` varchar(255) DEFAULT NULL,
  `billing_address` json DEFAULT NULL,
  `shipping_address` json DEFAULT NULL,
  `notes` text,
  `internal_notes` text,
  `shipped_at` timestamp NULL DEFAULT NULL,
  `delivered_at` timestamp NULL DEFAULT NULL,
  `cancelled_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `orders_order_number_unique` (`order_number`),
  KEY `orders_user_id_foreign` (`user_id`),
  KEY `orders_company_id_foreign` (`company_id`),
  KEY `orders_status_index` (`status`),
  KEY `orders_payment_status_index` (`payment_status`),
  KEY `orders_order_type_index` (`order_type`),
  CONSTRAINT `orders_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `orders_company_id_foreign` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create order_items table
CREATE TABLE IF NOT EXISTS `order_items` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `order_id` bigint unsigned NOT NULL,
  `product_id` bigint unsigned NOT NULL,
  `quantity` int NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `total` decimal(10,2) NOT NULL,
  `product_name` varchar(255) NOT NULL,
  `product_sku` varchar(255) NOT NULL,
  `product_options` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `order_items_order_id_foreign` (`order_id`),
  KEY `order_items_product_id_foreign` (`product_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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create payments table
CREATE TABLE IF NOT EXISTS `payments` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `order_id` bigint unsigned NOT NULL,
  `payment_method` varchar(255) NOT NULL,
  `payment_gateway` varchar(255) NOT NULL,
  `transaction_id` varchar(255) DEFAULT NULL,
  `gateway_transaction_id` varchar(255) DEFAULT NULL,
  `amount` decimal(10,2) NOT NULL,
  `currency` varchar(3) NOT NULL DEFAULT 'USD',
  `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,
  `refunded_at` timestamp NULL DEFAULT NULL,
  `refund_amount` decimal(10,2) DEFAULT NULL,
  `fees` decimal(10,2) DEFAULT NULL,
  `net_amount` decimal(10,2) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `payments_transaction_id_unique` (`transaction_id`),
  KEY `payments_order_id_foreign` (`order_id`),
  KEY `payments_status_index` (`status`),
  KEY `payments_payment_gateway_index` (`payment_gateway`),
  KEY `payments_gateway_transaction_id_index` (`gateway_transaction_id`),
  CONSTRAINT `payments_order_id_foreign` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create migrations table
CREATE TABLE IF NOT EXISTS `migrations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `migration` varchar(255) NOT NULL,
  `batch` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create password_reset_tokens table
CREATE TABLE IF NOT EXISTS `password_reset_tokens` (
  `email` varchar(255) NOT NULL,
  `token` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create personal_access_tokens table
CREATE TABLE IF NOT EXISTS `personal_access_tokens` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `tokenable_type` varchar(255) NOT NULL,
  `tokenable_id` bigint unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `token` varchar(64) NOT NULL,
  `abilities` text,
  `last_used_at` timestamp NULL DEFAULT NULL,
  `expires_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `personal_access_tokens_token_unique` (`token`),
  KEY `personal_access_tokens_tokenable_type_tokenable_id_index` (`tokenable_type`,`tokenable_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add foreign key constraint for users.company_id
ALTER TABLE `users` ADD CONSTRAINT `users_company_id_foreign` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE SET NULL;


-- Insert sample data
INSERT IGNORE INTO `categories` (`id`, `name`, `slug`, `description`, `is_active`, `is_featured`, `products_count`, `views_count`, `icon`, `color`, `created_at`, `updated_at`) VALUES
(1, 'Electronics', 'electronics', 'Electronic products and devices', 1, 1, 150, 2500, 'fas fa-laptop', '#007bff', NOW(), NOW()),
(2, 'Clothing', 'clothing', 'Fashion and apparel', 1, 1, 200, 3200, 'fas fa-tshirt', '#28a745', NOW(), NOW()),
(3, 'Home & Garden', 'home-garden', 'Home improvement and garden supplies', 1, 1, 120, 1800, 'fas fa-home', '#ffc107', NOW(), NOW()),
(4, 'Automotive', 'automotive', 'Car parts and accessories', 1, 0, 80, 1200, 'fas fa-car', '#dc3545', NOW(), NOW()),
(5, 'Sports & Outdoors', 'sports-outdoors', 'Sports equipment and outdoor gear', 1, 1, 95, 1600, 'fas fa-running', '#17a2b8', NOW(), NOW()),
(6, 'Beauty & Health', 'beauty-health', 'Beauty products and health supplements', 1, 1, 110, 2100, 'fas fa-heart', '#e83e8c', NOW(), NOW()),
(7, 'Books & Media', 'books-media', 'Books, movies, music and digital media', 1, 0, 75, 900, 'fas fa-book', '#6f42c1', NOW(), NOW()),
(8, 'Toys & Games', 'toys-games', 'Toys, games and entertainment products', 1, 1, 65, 1400, 'fas fa-gamepad', '#fd7e14', NOW(), NOW());

-- Insert sample company
INSERT IGNORE INTO `companies` (`id`, `name`, `slug`, `legal_name`, `email`, `phone`, `business_type`, `industry`, `country`, `city`, `status`, `is_verified`, `is_featured`, `is_premium`, `verified_at`, `created_at`, `updated_at`) VALUES
(1, 'Sample Company', 'sample-company', 'Sample Company Ltd.', 'company@aladin.com', '+1234567890', 'manufacturer', 'electronics', 'United States', 'New York', 'active', 1, 0, 0, NOW(), NOW(), NOW());

-- Insert sample exhibitions
INSERT IGNORE INTO `exhibitions` (`id`, `name`, `slug`, `description`, `venue_name`, `city`, `country`, `start_date`, `end_date`, `status`, `type`, `is_featured`, `is_active`, `created_at`, `updated_at`) VALUES
(1, 'Global Electronics Expo 2025', 'global-electronics-expo-2025', 'The world\'s largest electronics trade show featuring the latest innovations in consumer electronics, industrial automation, and smart technologies.', 'Las Vegas Convention Center', 'Las Vegas', 'United States', '2025-03-15 09:00:00', '2025-03-18 18:00:00', 'registration_open', 'expo', 1, 1, NOW(), NOW()),
(2, 'International Fashion Week', 'international-fashion-week', 'Premier fashion event showcasing the latest trends from top designers and emerging brands worldwide.', 'Milan Fashion Center', 'Milan', 'Italy', '2025-04-20 10:00:00', '2025-04-25 20:00:00', 'registration_open', 'fair', 1, 1, NOW(), NOW()),
(3, 'Automotive Innovation Summit', 'automotive-innovation-summit', 'Leading automotive conference focusing on electric vehicles, autonomous driving, and sustainable transportation solutions.', 'Detroit Convention Center', 'Detroit', 'United States', '2025-05-10 08:00:00', '2025-05-12 17:00:00', 'published', 'summit', 0, 1, NOW(), NOW());

-- Insert sample users (password is 'password')
INSERT IGNORE INTO `users` (`id`, `name`, `email`, `password`, `role`, `is_active`, `email_verified_at`, `company_id`, `created_at`, `updated_at`) VALUES
(1, 'Admin User', 'admin@aladin.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'super_admin', 1, NOW(), 1, NOW(), NOW()),
(2, 'Seller User', 'seller@aladin.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'seller', 1, NOW(), 1, NOW(), NOW()),
(3, 'Customer User', 'customer@aladin.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'customer', 1, NOW(), NOW(), NOW());

-- Insert migration records
INSERT IGNORE INTO `migrations` (`migration`, `batch`) VALUES
('2024_01_01_000001_create_users_table', 1),
('2024_01_01_000002_create_companies_table', 1),
('2024_01_01_000003_create_categories_table', 1),
('2024_01_01_000004_create_products_table', 1),
('2024_01_01_000005_create_orders_table', 1),
('2024_01_01_000006_create_order_items_table', 1),
('2024_01_01_000007_create_payments_table', 1),
('2024_01_01_000008_fix_products_table_missing_columns', 1),
('2024_01_01_000009_fix_companies_table_missing_columns', 1),
('2024_01_01_000010_create_exhibitions_table', 1),
('2024_01_01_000011_create_exhibition_participants_table', 1),
('2024_01_01_000012_fix_categories_table_missing_columns', 1);

SELECT 'All tables including exhibitions created successfully! You can now use the ALADIN B2B Plus platform with full exhibition functionality.' as message;


-- Create reviews table
CREATE TABLE `reviews` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `product_id` bigint unsigned NOT NULL,
  `rating` tinyint unsigned NOT NULL COMMENT '1-5 star rating',
  `title` varchar(255) DEFAULT NULL,
  `comment` text NOT NULL,
  `is_approved` tinyint(1) NOT NULL DEFAULT '0',
  `is_verified_purchase` tinyint(1) NOT NULL DEFAULT '0',
  `helpful_count` int NOT NULL DEFAULT '0',
  `not_helpful_count` int NOT NULL DEFAULT '0',
  `images` json DEFAULT NULL COMMENT 'Array of review image URLs',
  `pros` json DEFAULT NULL COMMENT 'Array of positive points',
  `cons` json DEFAULT NULL COMMENT 'Array of negative points',
  `recommended` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `reviews_user_id_foreign` (`user_id`),
  KEY `reviews_product_id_foreign` (`product_id`),
  KEY `reviews_product_id_is_approved_index` (`product_id`,`is_approved`),
  KEY `reviews_user_id_product_id_index` (`user_id`,`product_id`),
  KEY `reviews_rating_is_approved_index` (`rating`,`is_approved`),
  KEY `reviews_created_at_index` (`created_at`),
  CONSTRAINT `reviews_product_id_foreign` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
  CONSTRAINT `reviews_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create product_reviews table
CREATE TABLE `product_reviews` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `product_id` bigint unsigned NOT NULL,
  `rating` tinyint unsigned NOT NULL COMMENT '1-5 star rating',
  `title` varchar(255) DEFAULT NULL,
  `comment` text NOT NULL,
  `is_approved` tinyint(1) NOT NULL DEFAULT '0',
  `is_verified_purchase` tinyint(1) NOT NULL DEFAULT '0',
  `helpful_count` int NOT NULL DEFAULT '0',
  `not_helpful_count` int NOT NULL DEFAULT '0',
  `images` json DEFAULT NULL COMMENT 'Array of review image URLs',
  `pros` json DEFAULT NULL COMMENT 'Array of positive points',
  `cons` json DEFAULT NULL COMMENT 'Array of negative points',
  `recommended` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_product_review` (`user_id`,`product_id`),
  KEY `product_reviews_user_id_foreign` (`user_id`),
  KEY `product_reviews_product_id_foreign` (`product_id`),
  KEY `product_reviews_product_id_is_approved_index` (`product_id`,`is_approved`),
  KEY `product_reviews_rating_is_approved_index` (`rating`,`is_approved`),
  KEY `product_reviews_created_at_index` (`created_at`),
  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;

-- Create company_reviews table
CREATE TABLE `company_reviews` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `company_id` bigint unsigned NOT NULL,
  `rating` tinyint unsigned NOT NULL COMMENT '1-5 star overall rating',
  `title` varchar(255) DEFAULT NULL,
  `comment` text NOT NULL,
  `is_approved` tinyint(1) NOT NULL DEFAULT '0',
  `is_verified_business` tinyint(1) NOT NULL DEFAULT '0',
  `helpful_count` int NOT NULL DEFAULT '0',
  `not_helpful_count` int NOT NULL DEFAULT '0',
  `service_quality` tinyint unsigned DEFAULT NULL COMMENT '1-5 star service quality rating',
  `communication` tinyint unsigned DEFAULT NULL COMMENT '1-5 star communication rating',
  `delivery_time` tinyint unsigned DEFAULT NULL COMMENT '1-5 star delivery time rating',
  `product_quality` tinyint unsigned DEFAULT NULL COMMENT '1-5 star product quality rating',
  `recommended` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_company_review` (`user_id`,`company_id`),
  KEY `company_reviews_user_id_foreign` (`user_id`),
  KEY `company_reviews_company_id_foreign` (`company_id`),
  KEY `company_reviews_company_id_is_approved_index` (`company_id`,`is_approved`),
  KEY `company_reviews_rating_is_approved_index` (`rating`,`is_approved`),
  KEY `company_reviews_created_at_index` (`created_at`),
  CONSTRAINT `company_reviews_company_id_foreign` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `company_reviews_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create exhibition_reviews table
CREATE TABLE `exhibition_reviews` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `exhibition_id` bigint unsigned NOT NULL,
  `rating` tinyint unsigned NOT NULL COMMENT '1-5 star overall rating',
  `title` varchar(255) DEFAULT NULL,
  `comment` text NOT NULL,
  `is_approved` tinyint(1) NOT NULL DEFAULT '0',
  `is_verified_participant` tinyint(1) NOT NULL DEFAULT '0',
  `helpful_count` int NOT NULL DEFAULT '0',
  `not_helpful_count` int NOT NULL DEFAULT '0',
  `organization_rating` tinyint unsigned DEFAULT NULL COMMENT '1-5 star organization rating',
  `venue_rating` tinyint unsigned DEFAULT NULL COMMENT '1-5 star venue rating',
  `networking_rating` tinyint unsigned DEFAULT NULL COMMENT '1-5 star networking rating',
  `value_rating` tinyint unsigned DEFAULT NULL COMMENT '1-5 star value for money rating',
  `recommended` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_exhibition_review` (`user_id`,`exhibition_id`),
  KEY `exhibition_reviews_user_id_foreign` (`user_id`),
  KEY `exhibition_reviews_exhibition_id_foreign` (`exhibition_id`),
  KEY `exhibition_reviews_exhibition_id_is_approved_index` (`exhibition_id`,`is_approved`),
  KEY `exhibition_reviews_rating_is_approved_index` (`rating`,`is_approved`),
  KEY `exhibition_reviews_created_at_index` (`created_at`),
  CONSTRAINT `exhibition_reviews_exhibition_id_foreign` FOREIGN KEY (`exhibition_id`) REFERENCES `exhibitions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `exhibition_reviews_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert sample reviews
INSERT INTO `product_reviews` (`user_id`, `product_id`, `rating`, `title`, `comment`, `is_approved`, `is_verified_purchase`, `helpful_count`, `recommended`, `created_at`, `updated_at`) VALUES
(1, 1, 5, 'Excellent Product!', 'This product exceeded my expectations. Great quality and fast delivery.', 1, 1, 5, 1, NOW(), NOW()),
(2, 1, 4, 'Good value for money', 'Solid product with good features. Would recommend to others.', 1, 1, 3, 1, NOW(), NOW()),
(1, 2, 5, 'Outstanding quality', 'Premium product with excellent build quality. Very satisfied with the purchase.', 1, 1, 8, 1, NOW(), NOW());

INSERT INTO `company_reviews` (`user_id`, `company_id`, `rating`, `title`, `comment`, `is_approved`, `is_verified_business`, `service_quality`, `communication`, `delivery_time`, `product_quality`, `recommended`, `created_at`, `updated_at`) VALUES
(1, 1, 5, 'Excellent Business Partner', 'Professional service and high-quality products. Great communication throughout.', 1, 1, 5, 5, 4, 5, 1, NOW(), NOW()),
(2, 2, 4, 'Reliable Supplier', 'Good products and reasonable prices. Delivery could be faster but overall satisfied.', 1, 1, 4, 4, 3, 4, 1, NOW(), NOW());

INSERT INTO `exhibition_reviews` (`user_id`, `exhibition_id`, `rating`, `title`, `comment`, `is_approved`, `is_verified_participant`, `organization_rating`, `venue_rating`, `networking_rating`, `value_rating`, `recommended`, `created_at`, `updated_at`) VALUES
(1, 1, 5, 'Great Exhibition Experience', 'Well organized event with excellent networking opportunities. Highly recommend for business development.', 1, 1, 5, 4, 5, 4, 1, NOW(), NOW()),
(2, 2, 4, 'Good Business Event', 'Professional exhibition with good attendance. Venue facilities were adequate.', 1, 1, 4, 4, 4, 4, 1, NOW(), NOW());

-- Update migration records
INSERT INTO `migrations` (`migration`, `batch`) VALUES
('2024_01_01_000013_create_reviews_table', 1),
('2024_01_01_000014_create_product_reviews_table', 1),
('2024_01_01_000015_create_company_reviews_table', 1),
('2024_01_01_000016_create_exhibition_reviews_table', 1);
