-- ALADIN B2B Plus Ultimate Platform - Complete Database Schema with 365-Day Exhibitions
-- Version: 2.0.0 with Exhibition System
-- Author: NETT-TEL LTD
-- License: Master License Agreement

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`;

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

-- Countries table
CREATE TABLE `countries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `code` varchar(3) NOT NULL,
  `currency_code` varchar(3) NOT NULL DEFAULT 'USD',
  `timezone` varchar(50) NOT NULL DEFAULT 'UTC',
  `default_language` varchar(5) NOT NULL DEFAULT 'en',
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- EXHIBITION SYSTEM TABLES
-- =====================================================

-- Exhibitions table (365-day permanent exhibitions)
CREATE TABLE `exhibitions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `description` text,
  `country_id` int(11) NOT NULL,
  `city` varchar(100) NOT NULL,
  `venue` varchar(200) NOT NULL,
  `venue_address` text NOT NULL,
  `contact_info` json DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `is_permanent` tinyint(1) NOT NULL DEFAULT 1,
  `categories` json DEFAULT NULL,
  `total_booths` int(11) NOT NULL DEFAULT 0,
  `available_booths` int(11) NOT NULL DEFAULT 0,
  `price_range_min` decimal(10,2) DEFAULT NULL,
  `price_range_max` decimal(10,2) DEFAULT NULL,
  `currency` varchar(3) NOT NULL DEFAULT 'USD',
  `rating` decimal(3,2) DEFAULT 0.00,
  `total_reviews` int(11) DEFAULT 0,
  `features` json DEFAULT NULL,
  `images` json DEFAULT NULL,
  `safety_certified` tinyint(1) NOT NULL DEFAULT 0,
  `fire_safety_approved` tinyint(1) NOT NULL DEFAULT 0,
  `insurance_verified` tinyint(1) NOT NULL DEFAULT 0,
  `local_permits_obtained` tinyint(1) NOT NULL DEFAULT 0,
  `status` enum('active','upcoming','completed','cancelled') NOT NULL DEFAULT 'active',
  `managed_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `country_id` (`country_id`),
  KEY `managed_by` (`managed_by`),
  KEY `status` (`status`),
  KEY `is_permanent` (`is_permanent`),
  CONSTRAINT `exhibitions_country_fk` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON DELETE CASCADE,
  CONSTRAINT `exhibitions_managed_by_fk` FOREIGN KEY (`managed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Exhibition booth sizes and types
CREATE TABLE `exhibition_booth_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `size` varchar(20) NOT NULL,
  `area_sqm` decimal(8,2) NOT NULL,
  `base_price` decimal(10,2) NOT NULL,
  `currency` varchar(3) NOT NULL DEFAULT 'USD',
  `features` json DEFAULT NULL,
  `max_occupancy` int(11) DEFAULT NULL,
  `setup_time_hours` int(11) DEFAULT 24,
  `description` text,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Individual exhibition booths
CREATE TABLE `exhibition_booths` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `exhibition_id` int(11) NOT NULL,
  `booth_type_id` int(11) NOT NULL,
  `booth_number` varchar(20) NOT NULL,
  `floor_plan_position` json DEFAULT NULL,
  `monthly_price` decimal(10,2) NOT NULL,
  `setup_fee` decimal(10,2) DEFAULT 0.00,
  `utilities_included` json DEFAULT NULL,
  `special_features` json DEFAULT NULL,
  `status` enum('available','booked','occupied','maintenance') NOT NULL DEFAULT 'available',
  `current_occupant_id` int(11) DEFAULT NULL,
  `occupied_from` date DEFAULT NULL,
  `occupied_until` date DEFAULT NULL,
  `last_maintenance` date DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `exhibition_booth_number` (`exhibition_id`, `booth_number`),
  KEY `exhibition_id` (`exhibition_id`),
  KEY `booth_type_id` (`booth_type_id`),
  KEY `current_occupant_id` (`current_occupant_id`),
  KEY `status` (`status`),
  CONSTRAINT `booths_exhibition_fk` FOREIGN KEY (`exhibition_id`) REFERENCES `exhibitions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `booths_type_fk` FOREIGN KEY (`booth_type_id`) REFERENCES `exhibition_booth_types` (`id`) ON DELETE CASCADE,
  CONSTRAINT `booths_occupant_fk` FOREIGN KEY (`current_occupant_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Exhibition bookings
CREATE TABLE `exhibition_bookings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `booth_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `booking_reference` varchar(50) NOT NULL,
  `duration_months` int(11) NOT NULL DEFAULT 1,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `monthly_price` decimal(10,2) NOT NULL,
  `setup_fee` decimal(10,2) DEFAULT 0.00,
  `total_price` decimal(10,2) NOT NULL,
  `currency` varchar(3) NOT NULL DEFAULT 'USD',
  `payment_status` enum('pending','paid','partial','overdue','cancelled') NOT NULL DEFAULT 'pending',
  `booking_status` enum('pending','confirmed','active','completed','cancelled') NOT NULL DEFAULT 'pending',
  `special_requirements` text DEFAULT NULL,
  `company_info` json DEFAULT NULL,
  `contact_person` json DEFAULT NULL,
  `approved_by` int(11) DEFAULT NULL,
  `approved_at` timestamp NULL DEFAULT NULL,
  `cancelled_at` timestamp NULL DEFAULT NULL,
  `cancellation_reason` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `booking_reference` (`booking_reference`),
  KEY `booth_id` (`booth_id`),
  KEY `user_id` (`user_id`),
  KEY `approved_by` (`approved_by`),
  KEY `booking_status` (`booking_status`),
  KEY `payment_status` (`payment_status`),
  CONSTRAINT `bookings_booth_fk` FOREIGN KEY (`booth_id`) REFERENCES `exhibition_booths` (`id`) ON DELETE CASCADE,
  CONSTRAINT `bookings_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `bookings_approved_by_fk` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- MEMBERSHIP SYSTEM TABLES
-- =====================================================

-- Membership packages
CREATE TABLE `membership_packages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` text,
  `price` decimal(10,2) NOT NULL,
  `currency` varchar(3) NOT NULL DEFAULT 'USD',
  `billing_period` enum('monthly','quarterly','yearly') NOT NULL DEFAULT 'monthly',
  `max_exhibitions` int(11) DEFAULT NULL,
  `max_booths` int(11) DEFAULT NULL,
  `discount_percentage` decimal(5,2) DEFAULT 0.00,
  `features` json DEFAULT NULL,
  `is_popular` tinyint(1) NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- User memberships
CREATE TABLE `user_memberships` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `package_id` int(11) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `price_paid` decimal(10,2) NOT NULL,
  `currency` varchar(3) NOT NULL DEFAULT 'USD',
  `payment_reference` varchar(100) DEFAULT NULL,
  `auto_renew` tinyint(1) NOT NULL DEFAULT 1,
  `status` enum('active','expired','cancelled','suspended') NOT NULL DEFAULT 'active',
  `exhibitions_used` int(11) DEFAULT 0,
  `booths_used` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `package_id` (`package_id`),
  KEY `status` (`status`),
  CONSTRAINT `memberships_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `memberships_package_fk` FOREIGN KEY (`package_id`) REFERENCES `membership_packages` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- MULTILINGUAL CONTENT SYSTEM
-- =====================================================

-- Product translations
CREATE TABLE `product_translations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `language_code` varchar(5) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text,
  `short_description` varchar(500) DEFAULT NULL,
  `specifications` json DEFAULT NULL,
  `keywords` text DEFAULT NULL,
  `meta_title` varchar(255) DEFAULT NULL,
  `meta_description` varchar(500) DEFAULT NULL,
  `is_auto_translated` tinyint(1) NOT NULL DEFAULT 0,
  `translation_quality_score` decimal(3,2) DEFAULT NULL,
  `translated_by` int(11) DEFAULT NULL,
  `reviewed_by` int(11) DEFAULT NULL,
  `reviewed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `product_language` (`product_id`, `language_code`),
  KEY `language_code` (`language_code`),
  KEY `translated_by` (`translated_by`),
  KEY `reviewed_by` (`reviewed_by`),
  CONSTRAINT `product_translations_translated_by_fk` FOREIGN KEY (`translated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `product_translations_reviewed_by_fk` FOREIGN KEY (`reviewed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seller profile translations
CREATE TABLE `seller_profile_translations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `seller_id` int(11) NOT NULL,
  `language_code` varchar(5) NOT NULL,
  `company_description` text,
  `business_overview` text,
  `specializations` json DEFAULT NULL,
  `certifications` json DEFAULT NULL,
  `awards` json DEFAULT NULL,
  `company_history` text DEFAULT NULL,
  `mission_statement` text DEFAULT NULL,
  `is_auto_translated` tinyint(1) NOT NULL DEFAULT 0,
  `translation_quality_score` decimal(3,2) DEFAULT NULL,
  `translated_by` int(11) DEFAULT NULL,
  `reviewed_by` int(11) DEFAULT NULL,
  `reviewed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `seller_language` (`seller_id`, `language_code`),
  KEY `language_code` (`language_code`),
  KEY `translated_by` (`translated_by`),
  KEY `reviewed_by` (`reviewed_by`),
  CONSTRAINT `seller_translations_seller_fk` FOREIGN KEY (`seller_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `seller_translations_translated_by_fk` FOREIGN KEY (`translated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `seller_translations_reviewed_by_fk` FOREIGN KEY (`reviewed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- ADMIN MANAGEMENT SYSTEM
-- =====================================================

-- Country admin profiles
CREATE TABLE `country_admin_profiles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `country_id` int(11) NOT NULL,
  `admin_level` enum('regional','national','multi_country') NOT NULL DEFAULT 'regional',
  `permissions` json DEFAULT NULL,
  `responsibilities` json DEFAULT NULL,
  `contact_info` json DEFAULT NULL,
  `working_hours` json DEFAULT NULL,
  `managed_regions` json DEFAULT NULL,
  `reporting_to` int(11) DEFAULT NULL,
  `start_date` date NOT NULL,
  `salary_info` json DEFAULT NULL,
  `performance_metrics` json DEFAULT NULL,
  `status` enum('active','inactive','on_leave') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_country` (`user_id`, `country_id`),
  KEY `country_id` (`country_id`),
  KEY `reporting_to` (`reporting_to`),
  CONSTRAINT `admin_profiles_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `admin_profiles_country_fk` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON DELETE CASCADE,
  CONSTRAINT `admin_profiles_reporting_to_fk` FOREIGN KEY (`reporting_to`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Exhibition admin assignments
CREATE TABLE `exhibition_admin_assignments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `exhibition_id` int(11) NOT NULL,
  `admin_user_id` int(11) NOT NULL,
  `role` enum('country_manager','exhibition_manager','booth_coordinator') NOT NULL DEFAULT 'exhibition_manager',
  `permissions` json DEFAULT NULL,
  `assigned_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `assigned_by` int(11) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `exhibition_admin_role` (`exhibition_id`, `admin_user_id`, `role`),
  KEY `admin_user_id` (`admin_user_id`),
  KEY `assigned_by` (`assigned_by`),
  CONSTRAINT `admin_assignments_exhibition_fk` FOREIGN KEY (`exhibition_id`) REFERENCES `exhibitions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `admin_assignments_admin_fk` FOREIGN KEY (`admin_user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `admin_assignments_assigned_by_fk` FOREIGN KEY (`assigned_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Admin activity logs
CREATE TABLE `admin_activity_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `admin_user_id` int(11) NOT NULL,
  `action` varchar(100) NOT NULL,
  `description` text,
  `metadata` json DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `admin_user_id` (`admin_user_id`),
  KEY `action` (`action`),
  KEY `created_at` (`created_at`),
  CONSTRAINT `activity_logs_admin_fk` FOREIGN KEY (`admin_user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- SELLER APPROVAL SYSTEM
-- =====================================================

-- Seller approval requests
CREATE TABLE `seller_approval_requests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `country_id` int(11) NOT NULL,
  `business_documents` json DEFAULT NULL,
  `verification_documents` json DEFAULT NULL,
  `business_type` varchar(100) DEFAULT NULL,
  `annual_revenue` decimal(15,2) DEFAULT NULL,
  `employee_count` int(11) DEFAULT NULL,
  `years_in_business` int(11) DEFAULT NULL,
  `primary_products` json DEFAULT NULL,
  `target_markets` json DEFAULT NULL,
  `references` json DEFAULT NULL,
  `status` enum('pending','under_review','approved','rejected','requires_info') NOT NULL DEFAULT 'pending',
  `reviewed_by` int(11) DEFAULT NULL,
  `reviewed_at` timestamp NULL DEFAULT NULL,
  `approved_by` int(11) DEFAULT NULL,
  `approved_at` timestamp NULL DEFAULT NULL,
  `rejection_reason` text DEFAULT NULL,
  `admin_notes` text DEFAULT NULL,
  `conditions` json DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `country_id` (`country_id`),
  KEY `status` (`status`),
  KEY `reviewed_by` (`reviewed_by`),
  KEY `approved_by` (`approved_by`),
  CONSTRAINT `approval_requests_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `approval_requests_country_fk` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON DELETE CASCADE,
  CONSTRAINT `approval_requests_reviewed_by_fk` FOREIGN KEY (`reviewed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `approval_requests_approved_by_fk` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seller profiles
CREATE TABLE `seller_profiles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `country_id` int(11) NOT NULL,
  `verification_status` enum('pending','verified','rejected') NOT NULL DEFAULT 'pending',
  `verification_date` timestamp NULL DEFAULT NULL,
  `verified_by` int(11) DEFAULT NULL,
  `business_license_verified` tinyint(1) NOT NULL DEFAULT 0,
  `tax_registration_verified` tinyint(1) NOT NULL DEFAULT 0,
  `quality_certifications` json DEFAULT NULL,
  `compliance_status` enum('compliant','non_compliant','under_review') NOT NULL DEFAULT 'under_review',
  `last_compliance_check` timestamp NULL DEFAULT NULL,
  `rating` decimal(3,2) DEFAULT 0.00,
  `total_reviews` int(11) DEFAULT 0,
  `total_sales` decimal(15,2) DEFAULT 0.00,
  `total_orders` int(11) DEFAULT 0,
  `response_time_hours` decimal(5,2) DEFAULT NULL,
  `response_rate_percentage` decimal(5,2) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`),
  KEY `country_id` (`country_id`),
  KEY `verified_by` (`verified_by`),
  KEY `verification_status` (`verification_status`),
  KEY `compliance_status` (`compliance_status`),
  CONSTRAINT `seller_profiles_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `seller_profiles_country_fk` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON DELETE CASCADE,
  CONSTRAINT `seller_profiles_verified_by_fk` FOREIGN KEY (`verified_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- COMPLIANCE AND MONITORING
-- =====================================================

-- Compliance issues
CREATE TABLE `compliance_issues` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `country_id` int(11) NOT NULL,
  `entity_type` enum('seller','exhibition','booth','booking') NOT NULL,
  `entity_id` int(11) NOT NULL,
  `issue_type` varchar(100) NOT NULL,
  `severity` enum('low','medium','high','critical') NOT NULL DEFAULT 'medium',
  `description` text NOT NULL,
  `detected_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `detected_by` int(11) DEFAULT NULL,
  `status` enum('open','investigating','resolved','closed') NOT NULL DEFAULT 'open',
  `assigned_to` int(11) DEFAULT NULL,
  `resolution_notes` text DEFAULT NULL,
  `resolved_at` timestamp NULL DEFAULT NULL,
  `resolved_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `country_id` (`country_id`),
  KEY `entity_type_id` (`entity_type`, `entity_id`),
  KEY `severity` (`severity`),
  KEY `status` (`status`),
  KEY `detected_by` (`detected_by`),
  KEY `assigned_to` (`assigned_to`),
  KEY `resolved_by` (`resolved_by`),
  CONSTRAINT `compliance_issues_country_fk` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON DELETE CASCADE,
  CONSTRAINT `compliance_issues_detected_by_fk` FOREIGN KEY (`detected_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `compliance_issues_assigned_to_fk` FOREIGN KEY (`assigned_to`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `compliance_issues_resolved_by_fk` FOREIGN KEY (`resolved_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Compliance audits
CREATE TABLE `compliance_audits` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `country_id` int(11) NOT NULL,
  `audit_type` enum('routine','targeted','complaint_based','regulatory') NOT NULL DEFAULT 'routine',
  `audit_date` date NOT NULL,
  `auditor_id` int(11) NOT NULL,
  `scope` json DEFAULT NULL,
  `findings` json DEFAULT NULL,
  `compliance_score` decimal(5,2) DEFAULT NULL,
  `recommendations` text DEFAULT NULL,
  `follow_up_required` tinyint(1) NOT NULL DEFAULT 0,
  `follow_up_date` date DEFAULT NULL,
  `status` enum('scheduled','in_progress','completed','cancelled') NOT NULL DEFAULT 'scheduled',
  `report_file_path` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `country_id` (`country_id`),
  KEY `auditor_id` (`auditor_id`),
  KEY `audit_date` (`audit_date`),
  KEY `status` (`status`),
  CONSTRAINT `compliance_audits_country_fk` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON DELETE CASCADE,
  CONSTRAINT `compliance_audits_auditor_fk` FOREIGN KEY (`auditor_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Admin feedback
CREATE TABLE `admin_feedback` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `admin_user_id` int(11) NOT NULL,
  `feedback_from_user_id` int(11) NOT NULL,
  `rating` int(1) NOT NULL CHECK (`rating` >= 1 AND `rating` <= 5),
  `feedback_text` text DEFAULT NULL,
  `category` enum('responsiveness','helpfulness','professionalism','knowledge','overall') NOT NULL DEFAULT 'overall',
  `is_anonymous` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `admin_user_id` (`admin_user_id`),
  KEY `feedback_from_user_id` (`feedback_from_user_id`),
  KEY `rating` (`rating`),
  KEY `category` (`category`),
  CONSTRAINT `admin_feedback_admin_fk` FOREIGN KEY (`admin_user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `admin_feedback_from_user_fk` FOREIGN KEY (`feedback_from_user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- ADDITIONAL CORE TABLES (from original system)
-- =====================================================

-- Products table
CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `seller_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text,
  `short_description` varchar(500) DEFAULT NULL,
  `sku` varchar(100) DEFAULT NULL,
  `category_id` int(11) DEFAULT NULL,
  `price` decimal(10,2) NOT NULL,
  `currency` varchar(3) NOT NULL DEFAULT 'USD',
  `stock_quantity` int(11) DEFAULT 0,
  `min_order_quantity` int(11) DEFAULT 1,
  `max_order_quantity` int(11) DEFAULT NULL,
  `weight` decimal(8,3) DEFAULT NULL,
  `dimensions` json DEFAULT NULL,
  `images` json DEFAULT NULL,
  `videos` json DEFAULT NULL,
  `specifications` json DEFAULT NULL,
  `certifications` json DEFAULT NULL,
  `shipping_info` json DEFAULT NULL,
  `status` enum('active','inactive','out_of_stock','discontinued') NOT NULL DEFAULT 'active',
  `featured` tinyint(1) NOT NULL DEFAULT 0,
  `views_count` int(11) DEFAULT 0,
  `sales_count` int(11) DEFAULT 0,
  `rating` decimal(3,2) DEFAULT 0.00,
  `reviews_count` int(11) DEFAULT 0,
  `seo_title` varchar(255) DEFAULT NULL,
  `seo_description` varchar(500) DEFAULT NULL,
  `seo_keywords` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `seller_id` (`seller_id`),
  KEY `category_id` (`category_id`),
  KEY `status` (`status`),
  KEY `featured` (`featured`),
  CONSTRAINT `products_seller_fk` FOREIGN KEY (`seller_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Categories table
CREATE TABLE `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `slug` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `sort_order` int(11) DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`),
  KEY `parent_id` (`parent_id`),
  KEY `is_active` (`is_active`),
  CONSTRAINT `categories_parent_fk` FOREIGN KEY (`parent_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

-- Insert default countries
INSERT INTO `countries` (`name`, `code`, `currency_code`, `timezone`, `default_language`) VALUES
('United States', 'USA', 'USD', 'America/New_York', 'en'),
('Germany', 'DEU', 'EUR', 'Europe/Berlin', 'de'),
('China', 'CHN', 'CNY', 'Asia/Shanghai', 'zh'),
('United Kingdom', 'GBR', 'GBP', 'Europe/London', 'en'),
('France', 'FRA', 'EUR', 'Europe/Paris', 'fr'),
('Japan', 'JPN', 'JPY', 'Asia/Tokyo', 'ja'),
('India', 'IND', 'INR', 'Asia/Kolkata', 'en'),
('Brazil', 'BRA', 'BRL', 'America/Sao_Paulo', 'pt'),
('Canada', 'CAN', 'CAD', 'America/Toronto', 'en'),
('Australia', 'AUS', 'AUD', 'Australia/Sydney', 'en');

-- Insert default booth types
INSERT INTO `exhibition_booth_types` (`name`, `size`, `area_sqm`, `base_price`, `features`, `max_occupancy`, `description`) VALUES
('Small Booth', '3x3m', 9.00, 500.00, '["Basic Setup", "Power Supply", "WiFi", "Basic Lighting"]', 4, 'Perfect for startups and small businesses'),
('Medium Booth', '6x6m', 36.00, 1200.00, '["Enhanced Setup", "Storage Area", "Meeting Space", "Premium Lighting", "Sound System"]', 8, 'Ideal for growing businesses'),
('Large Booth', '9x9m', 81.00, 2500.00, '["Premium Setup", "Private Office", "Demo Area", "Conference Space", "Advanced AV Equipment"]', 15, 'Great for established companies'),
('Premium Booth', '12x12m', 144.00, 5000.00, '["Luxury Setup", "Conference Room", "VIP Lounge", "Kitchen Area", "Full AV Suite", "Dedicated Staff"]', 25, 'Ultimate exhibition experience');

-- Insert default membership packages
INSERT INTO `membership_packages` (`name`, `description`, `price`, `billing_period`, `max_exhibitions`, `max_booths`, `discount_percentage`, `features`, `is_popular`) VALUES
('Basic Package', 'Perfect for small businesses starting their global expansion', 99.00, 'monthly', 1, 2, 10.00, '["1 Exhibition Access", "2 Booth Bookings", "Basic Support", "Standard Listing", "Email Support"]', 0),
('Professional Package', 'Ideal for growing businesses with multiple market presence', 299.00, 'monthly', 3, 5, 25.00, '["3 Exhibition Access", "5 Booth Bookings", "Priority Support", "Featured Listing", "Analytics Dashboard", "Phone Support"]', 1),
('Enterprise Package', 'Complete solution for large corporations and enterprises', 999.00, 'monthly', NULL, NULL, 40.00, '["Unlimited Exhibitions", "Unlimited Booths", "24/7 Support", "Premium Listing", "Custom Branding", "Dedicated Account Manager", "API Access"]', 0);

-- Insert sample exhibitions
INSERT INTO `exhibitions` (`name`, `description`, `country_id`, `city`, `venue`, `venue_address`, `categories`, `total_booths`, `available_booths`, `price_range_min`, `price_range_max`, `features`, `is_permanent`) VALUES
('Global Tech Expo Berlin', 'The world\'s largest technology exhibition featuring cutting-edge innovations and digital solutions', 2, 'Berlin', 'Messe Berlin', 'Messedamm 22, 14055 Berlin, Germany', '["Technology", "Electronics", "Software", "AI", "IoT"]', 500, 127, 500.00, 5000.00, '["365 Days Open", "Physical Samples", "B2B Networking", "Live Demonstrations", "Multilingual Support"]', 1),
('International Trade Fair NYC', 'Premier B2B trade fair connecting global manufacturers and buyers in the heart of New York', 1, 'New York', 'Jacob K. Javits Convention Center', '429 11th Ave, New York, NY 10001, USA', '["Manufacturing", "Industrial", "B2B", "Import/Export"]', 800, 234, 800.00, 8000.00, '["365 Days Open", "Multilingual Support", "Escrow Services", "Quality Assurance", "Shipping Solutions"]', 1),
('Asian Business Summit Shanghai', 'Asia\'s premier business networking and trade exhibition connecting East and West', 3, 'Shanghai', 'Shanghai International Expo Center', '2345 Longyang Rd, Pudong, Shanghai, China', '["Business", "Finance", "Trade", "Manufacturing", "Technology"]', 600, 89, 3000.00, 30000.00, '["365 Days Open", "Translation Services", "Cultural Bridge", "Payment Gateway", "Logistics Support"]', 1);

-- Create country admin profile
INSERT INTO `country_admin_profiles` (`user_id`, `country_id`, `admin_level`, `permissions`, `start_date`) VALUES
(2, 2, 'national', '["manage_exhibitions", "approve_sellers", "manage_booths", "handle_disputes", "view_analytics", "manage_pricing", "compliance_monitoring", "generate_reports"]', '2024-01-01');

COMMIT;

-- =====================================================
-- INDEXES FOR PERFORMANCE
-- =====================================================

-- Additional indexes for better performance
CREATE INDEX idx_exhibitions_country_status ON exhibitions(country_id, status);
CREATE INDEX idx_booths_exhibition_status ON exhibition_booths(exhibition_id, status);
CREATE INDEX idx_bookings_user_status ON exhibition_bookings(user_id, booking_status);
CREATE INDEX idx_bookings_dates ON exhibition_bookings(start_date, end_date);
CREATE INDEX idx_memberships_user_status ON user_memberships(user_id, status);
CREATE INDEX idx_products_seller_status ON products(seller_id, status);
CREATE INDEX idx_translations_product_lang ON product_translations(product_id, language_code);
CREATE INDEX idx_seller_translations_lang ON seller_profile_translations(seller_id, language_code);
CREATE INDEX idx_admin_logs_date ON admin_activity_logs(created_at);
CREATE INDEX idx_compliance_country_status ON compliance_issues(country_id, status);

-- =====================================================
-- VIEWS FOR COMMON QUERIES
-- =====================================================

-- Exhibition overview view
CREATE VIEW exhibition_overview AS
SELECT 
    e.id,
    e.name,
    e.city,
    c.name as country_name,
    e.total_booths,
    e.available_booths,
    e.price_range_min,
    e.price_range_max,
    e.currency,
    e.rating,
    e.status,
    COUNT(eb.id) as actual_booth_count,
    COUNT(CASE WHEN eb.status = 'available' THEN 1 END) as actual_available_booths
FROM exhibitions e
LEFT JOIN countries c ON e.country_id = c.id
LEFT JOIN exhibition_booths eb ON e.id = eb.exhibition_id
WHERE e.status = 'active'
GROUP BY e.id;

-- Country admin dashboard view
CREATE VIEW country_admin_dashboard AS
SELECT 
    cap.user_id as admin_id,
    c.name as country_name,
    COUNT(DISTINCT e.id) as total_exhibitions,
    COUNT(DISTINCT CASE WHEN e.status = 'active' THEN e.id END) as active_exhibitions,
    COUNT(DISTINCT eb.id) as total_booths,
    COUNT(DISTINCT CASE WHEN eb.status = 'available' THEN eb.id END) as available_booths,
    COUNT(DISTINCT s.user_id) as total_sellers,
    COUNT(DISTINCT CASE WHEN s.verification_status = 'verified' THEN s.user_id END) as verified_sellers,
    COALESCE(SUM(exb.total_price), 0) as total_revenue
FROM country_admin_profiles cap
LEFT JOIN countries c ON cap.country_id = c.id
LEFT JOIN exhibitions e ON c.id = e.country_id
LEFT JOIN exhibition_booths eb ON e.id = eb.exhibition_id
LEFT JOIN seller_profiles s ON c.id = s.country_id
LEFT JOIN exhibition_bookings exb ON eb.id = exb.booth_id AND exb.payment_status = 'paid'
WHERE cap.status = 'active'
GROUP BY cap.user_id, c.id;
