-- =====================================================
-- ALADIN B2B PLUS - COMPLETE DATABASE RESET & FIX
-- This script will completely reset and recreate all tables
-- =====================================================

-- Drop all existing tables to avoid conflicts



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

-- Create companies table
CREATE TABLE `companies` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `industry` varchar(100) DEFAULT NULL,
  `website` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `country` varchar(2) DEFAULT NULL,
  `postal_code` varchar(20) DEFAULT NULL,
  `logo` varchar(255) DEFAULT NULL,
  `cover_image` varchar(255) DEFAULT NULL,
  `established_year` int(11) DEFAULT NULL,
  `employee_count` varchar(50) DEFAULT NULL,
  `annual_revenue` varchar(50) DEFAULT NULL,
  `business_type` enum('manufacturer','distributor','retailer','service_provider','other') DEFAULT 'manufacturer',
  `verification_status` enum('pending','verified','rejected') DEFAULT 'pending',
  `verification_documents` json DEFAULT NULL,
  `is_featured` tinyint(1) NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `rating` decimal(3,2) DEFAULT 0.00,
  `reviews_count` int(11) DEFAULT 0,
  `products_count` int(11) DEFAULT 0,
  `followers_count` int(11) DEFAULT 0,
  `views_count` int(11) DEFAULT 0,
  `social_links` json DEFAULT NULL,
  `certifications` 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`),
  KEY `companies_user_id_foreign` (`user_id`),
  KEY `companies_country_index` (`country`),
  KEY `companies_industry_index` (`industry`),
  KEY `companies_is_active_index` (`is_active`),
  KEY `companies_is_featured_index` (`is_featured`),
  CONSTRAINT `companies_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create categories table
CREATE TABLE `categories` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `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` tinyint(1) NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `products_count` int(11) DEFAULT 0,
  `views_count` int(11) DEFAULT 0,
  `meta_title` varchar(255) DEFAULT NULL,
  `meta_description` text DEFAULT NULL,
  `commission_rate` decimal(5,2) DEFAULT 0.00,
  `min_price` decimal(10,2) DEFAULT NULL,
  `max_price` decimal(10,2) 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 `categories_slug_unique` (`slug`),
  KEY `categories_parent_id_foreign` (`parent_id`),
  KEY `categories_is_active_index` (`is_active`),
  KEY `categories_is_featured_index` (`is_featured`),
  CONSTRAINT `categories_parent_id_foreign` FOREIGN KEY (`parent_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create products table
CREATE TABLE `products` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `category_id` bigint(20) unsigned DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `short_description` varchar(500) DEFAULT NULL,
  `sku` varchar(100) DEFAULT NULL,
  `price` decimal(10,2) NOT NULL,
  `compare_price` decimal(10,2) DEFAULT NULL,
  `cost_price` decimal(10,2) DEFAULT NULL,
  `stock_quantity` int(11) DEFAULT 0,
  `min_order_quantity` int(11) DEFAULT 1,
  `max_order_quantity` int(11) DEFAULT NULL,
  `weight` decimal(8,2) DEFAULT NULL,
  `dimensions` varchar(100) DEFAULT NULL,
  `status` enum('draft','published','archived') DEFAULT 'draft',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `is_featured` tinyint(1) NOT NULL DEFAULT 0,
  `is_digital` tinyint(1) NOT NULL DEFAULT 0,
  `requires_shipping` tinyint(1) NOT NULL DEFAULT 1,
  `track_inventory` tinyint(1) NOT NULL DEFAULT 1,
  `allow_backorder` tinyint(1) NOT NULL DEFAULT 0,
  `published_at` timestamp NULL DEFAULT NULL,
  `rating` decimal(3,2) DEFAULT 0.00,
  `reviews_count` int(11) DEFAULT 0,
  `views_count` int(11) DEFAULT 0,
  `sales_count` int(11) DEFAULT 0,
  `meta_title` varchar(255) DEFAULT NULL,
  `meta_description` text DEFAULT NULL,
  `tags` json DEFAULT NULL,
  `attributes` json DEFAULT NULL,
  `variants` 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 `products_slug_unique` (`slug`),
  KEY `products_company_id_foreign` (`company_id`),
  KEY `products_category_id_foreign` (`category_id`),
  KEY `products_status_index` (`status`),
  KEY `products_is_active_index` (`is_active`),
  KEY `products_is_featured_index` (`is_featured`),
  CONSTRAINT `products_company_id_foreign` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `products_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create orders table
CREATE TABLE `orders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `company_id` bigint(20) unsigned DEFAULT NULL,
  `order_number` varchar(100) NOT NULL,
  `status` enum('pending','processing','shipped','delivered','cancelled','refunded') DEFAULT 'pending',
  `subtotal` decimal(10,2) NOT NULL DEFAULT 0.00,
  `tax_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `shipping_cost` decimal(10,2) NOT NULL DEFAULT 0.00,
  `discount_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `total_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `currency` varchar(3) DEFAULT 'USD',
  `shipping_address_id` bigint(20) unsigned DEFAULT NULL,
  `billing_address_id` bigint(20) unsigned DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `order_date` timestamp NULL DEFAULT NULL,
  `shipped_at` timestamp NULL DEFAULT NULL,
  `delivered_at` timestamp NULL DEFAULT NULL,
  `cancelled_at` timestamp NULL DEFAULT NULL,
  `cancellation_reason` text 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`),
  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 `order_items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) unsigned NOT NULL,
  `product_id` bigint(20) unsigned NOT NULL,
  `quantity` int(11) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `total` decimal(10,2) NOT NULL,
  `product_name` varchar(255) NOT NULL,
  `product_sku` varchar(100) DEFAULT NULL,
  `product_attributes` 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 `payments` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `currency` varchar(3) NOT NULL DEFAULT 'USD',
  `payment_method` varchar(50) NOT NULL,
  `payment_details` json DEFAULT NULL,
  `status` enum('pending','processing','completed','failed','cancelled','refunded') DEFAULT 'pending',
  `transaction_id` varchar(255) DEFAULT NULL,
  `gateway_transaction_id` varchar(255) DEFAULT NULL,
  `gateway_response` json DEFAULT NULL,
  `processed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `payments_order_id_foreign` (`order_id`),
  KEY `payments_user_id_foreign` (`user_id`),
  KEY `payments_status_index` (`status`),
  CONSTRAINT `payments_order_id_foreign` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  CONSTRAINT `payments_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create exhibitions table
CREATE TABLE `exhibitions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `organizer_id` bigint(20) unsigned DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `short_description` varchar(500) DEFAULT NULL,
  `type` enum('trade_show','conference','expo','fair','summit','other') DEFAULT 'trade_show',
  `industry` varchar(100) DEFAULT NULL,
  `venue` varchar(255) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `country` varchar(2) DEFAULT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `registration_start` date DEFAULT NULL,
  `registration_deadline` date DEFAULT NULL,
  `max_participants` int(11) DEFAULT NULL,
  `entry_fee` decimal(10,2) DEFAULT 0.00,
  `booth_fee` decimal(10,2) DEFAULT 0.00,
  `currency` varchar(3) DEFAULT 'USD',
  `status` enum('draft','published','ongoing','completed','cancelled') DEFAULT 'draft',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `is_featured` tinyint(1) NOT NULL DEFAULT 0,
  `is_online` tinyint(1) NOT NULL DEFAULT 0,
  `website` varchar(255) DEFAULT NULL,
  `contact_email` varchar(255) DEFAULT NULL,
  `contact_phone` varchar(20) DEFAULT NULL,
  `images` json DEFAULT NULL,
  `floor_plan` varchar(255) DEFAULT NULL,
  `brochure` varchar(255) DEFAULT NULL,
  `rating` decimal(3,2) DEFAULT 0.00,
  `reviews_count` int(11) DEFAULT 0,
  `participants_count` int(11) DEFAULT 0,
  `views_count` int(11) DEFAULT 0,
  `meta_title` varchar(255) DEFAULT NULL,
  `meta_description` text 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_organizer_id_foreign` (`organizer_id`),
  KEY `exhibitions_country_index` (`country`),
  KEY `exhibitions_industry_index` (`industry`),
  KEY `exhibitions_status_index` (`status`),
  KEY `exhibitions_start_date_index` (`start_date`),
  CONSTRAINT `exhibitions_organizer_id_foreign` FOREIGN KEY (`organizer_id`) REFERENCES `companies` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create exhibition_participants table
CREATE TABLE `exhibition_participants` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `exhibition_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `company_id` bigint(20) unsigned DEFAULT NULL,
  `type` enum('exhibitor','visitor','speaker','sponsor') DEFAULT 'visitor',
  `company_name` varchar(255) NOT NULL,
  `job_title` varchar(255) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `booth_number` varchar(20) DEFAULT NULL,
  `booth_size` varchar(20) DEFAULT NULL,
  `booth_preference` varchar(100) DEFAULT NULL,
  `special_requirements` text DEFAULT NULL,
  `status` enum('pending','approved','rejected','cancelled') DEFAULT 'pending',
  `payment_status` enum('pending','paid','refunded') DEFAULT 'pending',
  `amount_paid` decimal(10,2) DEFAULT 0.00,
  `registered_at` timestamp NULL DEFAULT NULL,
  `approved_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `exhibition_participants_unique` (`exhibition_id`,`user_id`),
  KEY `exhibition_participants_user_id_foreign` (`user_id`),
  KEY `exhibition_participants_company_id_foreign` (`company_id`),
  CONSTRAINT `exhibition_participants_exhibition_id_foreign` FOREIGN KEY (`exhibition_id`) REFERENCES `exhibitions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `exhibition_participants_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `exhibition_participants_company_id_foreign` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create reviews table
CREATE TABLE `reviews` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `reviewable_type` varchar(255) NOT NULL,
  `reviewable_id` bigint(20) unsigned NOT NULL,
  `rating` tinyint(4) NOT NULL,
  `title` varchar(255) DEFAULT NULL,
  `comment` text DEFAULT NULL,
  `pros` text DEFAULT NULL,
  `cons` text DEFAULT NULL,
  `images` json DEFAULT NULL,
  `is_verified` tinyint(1) NOT NULL DEFAULT 0,
  `is_approved` tinyint(1) NOT NULL DEFAULT 1,
  `helpful_count` int(11) DEFAULT 0,
  `not_helpful_count` int(11) DEFAULT 0,
  `would_recommend` tinyint(1) DEFAULT NULL,
  `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_reviewable_type_reviewable_id_index` (`reviewable_type`,`reviewable_id`),
  KEY `reviews_rating_index` (`rating`),
  CONSTRAINT `reviews_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `companies` (`id`, `user_id`, `name`, `slug`, `description`, `industry`, `website`, `email`, `phone`, `city`, `country`, `business_type`, `verification_status`, `is_featured`, `is_active`, `rating`, `reviews_count`, `products_count`, `created_at`, `updated_at`) VALUES
(1, 3, 'ALADIN Trading LLC', 'aladin-trading-llc', 'Leading B2B marketplace and trading company in the Middle East', 'Trading', 'https://aladin-trading.com', 'info@aladin-trading.com', '+971501234567', 'Dubai', 'AE', 'distributor', 'verified', 1, 1, 4.5, 25, 150, NOW(), NOW()),
(2, 2, 'Global Tech Solutions', 'global-tech-solutions', 'Technology solutions provider for businesses worldwide', 'Technology', 'https://globaltech.com', 'contact@globaltech.com', '+971501234568', 'Abu Dhabi', 'AE', 'manufacturer', 'verified', 1, 1, 4.2, 18, 89, NOW(), NOW());

INSERT INTO `categories` (`id`, `parent_id`, `name`, `slug`, `description`, `is_featured`, `is_active`, `products_count`, `created_at`, `updated_at`) VALUES
(1, NULL, 'Electronics', 'electronics', 'Electronic devices and components', 1, 1, 45, NOW(), NOW()),
(2, NULL, 'Machinery', 'machinery', 'Industrial machinery and equipment', 1, 1, 32, NOW(), NOW()),
(3, NULL, 'Textiles', 'textiles', 'Fabrics and textile products', 1, 1, 28, NOW(), NOW()),
(4, 1, 'Smartphones', 'smartphones', 'Mobile phones and accessories', 0, 1, 15, NOW(), NOW()),
(5, 1, 'Computers', 'computers', 'Laptops, desktops and computer accessories', 0, 1, 20, NOW(), NOW());

INSERT INTO `products` (`id`, `company_id`, `category_id`, `name`, `slug`, `description`, `short_description`, `sku`, `price`, `stock_quantity`, `status`, `is_active`, `is_featured`, `published_at`, `rating`, `reviews_count`, `views_count`, `sales_count`, `created_at`, `updated_at`) VALUES
(1, 1, 4, 'Premium Smartphone Pro Max', 'premium-smartphone-pro-max', 'Latest flagship smartphone with advanced features and premium build quality', 'High-end smartphone with excellent camera and performance', 'PHONE-001', 899.99, 50, 'published', 1, 1, NOW(), 4.3, 12, 245, 8, NOW(), NOW()),
(2, 1, 5, 'Business Laptop Ultra', 'business-laptop-ultra', 'Professional laptop designed for business users with enterprise features', 'Powerful laptop for business professionals', 'LAPTOP-001', 1299.99, 25, 'published', 1, 1, NOW(), 4.1, 8, 189, 5, NOW(), NOW()),
(3, 2, 2, 'Industrial Printer 3000', 'industrial-printer-3000', 'Heavy-duty industrial printer for manufacturing environments', 'Robust printer for industrial applications', 'PRINTER-001', 2499.99, 10, 'published', 1, 0, NOW(), 4.0, 3, 67, 2, NOW(), NOW());

INSERT INTO `exhibitions` (`id`, `organizer_id`, `name`, `slug`, `description`, `type`, `industry`, `venue`, `city`, `country`, `start_date`, `end_date`, `registration_deadline`, `status`, `is_active`, `is_featured`, `rating`, `participants_count`, `views_count`, `created_at`, `updated_at`) VALUES
(1, 1, 'Dubai Tech Expo 2024', 'dubai-tech-expo-2024', 'Annual technology exhibition showcasing latest innovations', 'expo', 'Technology', 'Dubai World Trade Centre', 'Dubai', 'AE', '2024-12-15', '2024-12-18', '2024-12-01', 'published', 1, 1, 4.2, 150, 1250, NOW(), NOW()),
(2, 2, 'Middle East Business Summit', 'middle-east-business-summit', 'Premier business networking event for the Middle East region', 'summit', 'Business', 'Abu Dhabi National Exhibition Centre', 'Abu Dhabi', 'AE', '2024-11-20', '2024-11-22', '2024-11-05', 'published', 1, 1, 4.0, 200, 980, NOW(), NOW());

-- Insert migration records
INSERT 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_create_exhibitions_table', 1),
('2024_01_01_000009_create_exhibition_participants_table', 1),
('2024_01_01_000010_create_reviews_table', 1);

-- =====================================================
-- DATABASE RESET COMPLETE
-- All tables created with sample data
-- Default login: admin@aladin.com / password
-- =====================================================
