-- =====================================================
-- IMMEDIATE FIX FOR MISSING TABLES AND FUNCTIONALITY
-- Run this in phpMyAdmin or MySQL console
-- =====================================================

-- Create exhibition_participants table (the missing one causing the error)
CREATE TABLE IF NOT EXISTS `exhibition_participants` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `exhibition_id` bigint(20) unsigned NOT NULL,
  `company_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `booth_number` varchar(20) DEFAULT NULL,
  `booth_size` varchar(20) DEFAULT NULL,
  `booth_type` enum('standard','premium','corner','island') DEFAULT 'standard',
  `participation_type` enum('exhibitor','visitor','speaker','sponsor') DEFAULT 'exhibitor',
  `status` enum('pending','approved','rejected','cancelled') DEFAULT 'pending',
  `registration_date` timestamp NULL DEFAULT NULL,
  `payment_status` enum('pending','paid','refunded') DEFAULT 'pending',
  `amount_paid` decimal(10,2) DEFAULT 0.00,
  `special_requirements` text DEFAULT NULL,
  `contact_person` varchar(255) NOT NULL,
  `contact_email` varchar(255) NOT NULL,
  `contact_phone` varchar(20) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `exhibition_participants_unique` (`exhibition_id`,`company_id`),
  KEY `exhibition_participants_exhibition_id_foreign` (`exhibition_id`),
  KEY `exhibition_participants_company_id_foreign` (`company_id`),
  KEY `exhibition_participants_user_id_foreign` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create exhibitions table if it doesn't exist
CREATE TABLE IF NOT EXISTS `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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create companies table if it doesn't exist
CREATE TABLE IF NOT EXISTS `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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert sample data for exhibitions
INSERT IGNORE 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, 1, '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()),
(3, 1, 'Global Trade Fair 2024', 'global-trade-fair-2024', 'International trade fair connecting businesses worldwide', 'fair', 'Trade', 'Sharjah Expo Centre', 'Sharjah', 'AE', '2024-10-25', '2024-10-28', '2024-10-10', 'published', 1, 1, 4.1, 300, 1500, NOW(), NOW());

-- Insert sample data for companies
INSERT IGNORE 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()),
(3, 1, 'Emirates Business Hub', 'emirates-business-hub', 'Premier business services and consulting company', 'Consulting', 'https://emiratesbiz.com', 'hello@emiratesbiz.com', '+971501234569', 'Sharjah', 'AE', 'service_provider', 'verified', 1, 1, 4.3, 32, 67, NOW(), NOW());

-- Insert sample exhibition participants
INSERT IGNORE INTO `exhibition_participants` (`id`, `exhibition_id`, `company_id`, `user_id`, `booth_number`, `booth_size`, `booth_type`, `participation_type`, `status`, `registration_date`, `payment_status`, `amount_paid`, `contact_person`, `contact_email`, `contact_phone`, `created_at`, `updated_at`) VALUES
(1, 1, 1, 3, 'A101', '3x3', 'standard', 'exhibitor', 'approved', NOW(), 'paid', 2500.00, 'Ahmed Al-Rashid', 'ahmed@aladin-trading.com', '+971501234567', NOW(), NOW()),
(2, 1, 2, 2, 'B205', '6x6', 'premium', 'exhibitor', 'approved', NOW(), 'paid', 5000.00, 'Sarah Johnson', 'sarah@globaltech.com', '+971501234568', NOW(), NOW()),
(3, 2, 3, 1, 'C301', '3x3', 'corner', 'exhibitor', 'approved', NOW(), 'paid', 3000.00, 'Mohammed Hassan', 'mohammed@emiratesbiz.com', '+971501234569', NOW(), NOW()),
(4, 3, 1, 3, 'D150', '9x9', 'island', 'exhibitor', 'pending', NOW(), 'pending', 0.00, 'Ahmed Al-Rashid', 'ahmed@aladin-trading.com', '+971501234567', NOW(), NOW());

-- Add foreign key constraints if they don't exist
SET @sql = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
     WHERE CONSTRAINT_SCHEMA = DATABASE() 
     AND TABLE_NAME = 'exhibition_participants' 
     AND CONSTRAINT_NAME = 'exhibition_participants_exhibition_id_foreign') = 0,
    'ALTER TABLE exhibition_participants ADD CONSTRAINT exhibition_participants_exhibition_id_foreign FOREIGN KEY (exhibition_id) REFERENCES exhibitions(id) ON DELETE CASCADE',
    'SELECT "Foreign key already exists"'
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
     WHERE CONSTRAINT_SCHEMA = DATABASE() 
     AND TABLE_NAME = 'exhibition_participants' 
     AND CONSTRAINT_NAME = 'exhibition_participants_company_id_foreign') = 0,
    'ALTER TABLE exhibition_participants ADD CONSTRAINT exhibition_participants_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE',
    'SELECT "Foreign key already exists"'
));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- =====================================================
-- IMMEDIATE FIX COMPLETE
-- The exhibition_participants table now exists
-- All exhibitions should load without errors
-- =====================================================
