-- ALADIN B2B Plus Ultimate Platform - Complete Database Schema with Website Customization
-- Version: 2.0.0 with Website Customization System
-- Generated: 2024

-- =====================================================
-- WEBSITE CUSTOMIZATION TABLES
-- =====================================================

-- Website customizations storage
CREATE TABLE IF NOT EXISTS `website_customizations` (
  `id` VARCHAR(36) PRIMARY KEY,
  `template_id` VARCHAR(50) NOT NULL DEFAULT 'default',
  `customizations` JSON NOT NULL,
  `is_active` BOOLEAN DEFAULT FALSE,
  `created_by` BIGINT UNSIGNED,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_template_id` (`template_id`),
  INDEX `idx_is_active` (`is_active`),
  INDEX `idx_created_by` (`created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Website templates
CREATE TABLE IF NOT EXISTS `website_templates` (
  `id` VARCHAR(50) PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `description` TEXT,
  `preview_image` VARCHAR(255),
  `template_data` JSON NOT NULL,
  `is_active` BOOLEAN DEFAULT TRUE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Theme configurations
CREATE TABLE IF NOT EXISTS `theme_configurations` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `slug` VARCHAR(100) NOT NULL UNIQUE,
  `config_data` JSON NOT NULL,
  `css_variables` JSON,
  `is_default` BOOLEAN DEFAULT FALSE,
  `is_active` BOOLEAN DEFAULT TRUE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_slug` (`slug`),
  INDEX `idx_is_default` (`is_default`),
  INDEX `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Logo and branding assets
CREATE TABLE IF NOT EXISTS `branding_assets` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `asset_type` ENUM('logo', 'favicon', 'banner', 'background') NOT NULL,
  `file_name` VARCHAR(255) NOT NULL,
  `file_path` VARCHAR(500) NOT NULL,
  `file_size` INT UNSIGNED,
  `mime_type` VARCHAR(100),
  `dimensions` JSON,
  `is_active` BOOLEAN DEFAULT TRUE,
  `uploaded_by` BIGINT UNSIGNED,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_asset_type` (`asset_type`),
  INDEX `idx_is_active` (`is_active`),
  INDEX `idx_uploaded_by` (`uploaded_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Color schemes
CREATE TABLE IF NOT EXISTS `color_schemes` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `primary_color` VARCHAR(7) NOT NULL,
  `secondary_color` VARCHAR(7) NOT NULL,
  `accent_color` VARCHAR(7) NOT NULL,
  `background_color` VARCHAR(7) NOT NULL,
  `text_color` VARCHAR(7) NOT NULL,
  `success_color` VARCHAR(7) DEFAULT '#10B981',
  `warning_color` VARCHAR(7) DEFAULT '#F59E0B',
  `error_color` VARCHAR(7) DEFAULT '#EF4444',
  `is_predefined` BOOLEAN DEFAULT FALSE,
  `is_active` BOOLEAN DEFAULT TRUE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_is_predefined` (`is_predefined`),
  INDEX `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Typography settings
CREATE TABLE IF NOT EXISTS `typography_settings` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `font_family` VARCHAR(100) NOT NULL,
  `font_size_base` INT DEFAULT 16,
  `line_height` DECIMAL(3,1) DEFAULT 1.5,
  `heading_weight` INT DEFAULT 600,
  `body_weight` INT DEFAULT 400,
  `letter_spacing` DECIMAL(4,2) DEFAULT 0.00,
  `is_active` BOOLEAN DEFAULT TRUE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Layout configurations
CREATE TABLE IF NOT EXISTS `layout_configurations` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `container_width` INT DEFAULT 1200,
  `border_radius` INT DEFAULT 8,
  `spacing_unit` INT DEFAULT 16,
  `header_height` INT DEFAULT 80,
  `footer_height` INT DEFAULT 60,
  `sidebar_width` INT DEFAULT 250,
  `is_active` BOOLEAN DEFAULT TRUE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Component styles
CREATE TABLE IF NOT EXISTS `component_styles` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `component_name` VARCHAR(100) NOT NULL,
  `style_name` VARCHAR(100) NOT NULL,
  `css_properties` JSON NOT NULL,
  `is_active` BOOLEAN DEFAULT TRUE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_component_name` (`component_name`),
  INDEX `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Preview sessions
CREATE TABLE IF NOT EXISTS `preview_sessions` (
  `id` VARCHAR(36) PRIMARY KEY,
  `customizations` JSON NOT NULL,
  `template_id` VARCHAR(50) NOT NULL,
  `expires_at` TIMESTAMP NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_expires_at` (`expires_at`),
  INDEX `idx_template_id` (`template_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- INSERT DEFAULT DATA FOR WEBSITE CUSTOMIZATION
-- =====================================================

-- Default website templates
INSERT INTO `website_templates` (`id`, `name`, `description`, `preview_image`, `template_data`) VALUES
('default', 'Standard Template', 'Modernes, sauberes Design für B2B-Plattformen', '/assets/templates/default-preview.jpg', '{"layout": "standard", "features": ["header", "navigation", "content", "footer"]}'),
('corporate', 'Corporate Template', 'Professionelles Design für Unternehmen', '/assets/templates/corporate-preview.jpg', '{"layout": "corporate", "features": ["header", "navigation", "sidebar", "content", "footer"]}'),
('modern', 'Modern Template', 'Trendy, minimalistisches Design', '/assets/templates/modern-preview.jpg', '{"layout": "modern", "features": ["header", "hero", "content", "footer"]}');

-- Default color schemes
INSERT INTO `color_schemes` (`name`, `primary_color`, `secondary_color`, `accent_color`, `background_color`, `text_color`, `is_predefined`) VALUES
('ALADIN Blue', '#3B82F6', '#10B981', '#F59E0B', '#FFFFFF', '#1F2937', TRUE),
('Corporate Gray', '#6B7280', '#10B981', '#F59E0B', '#F9FAFB', '#111827', TRUE),
('Modern Purple', '#8B5CF6', '#06B6D4', '#F59E0B', '#FFFFFF', '#1F2937', TRUE),
('Professional Green', '#10B981', '#3B82F6', '#F59E0B', '#FFFFFF', '#1F2937', TRUE),
('Elegant Black', '#1F2937', '#10B981', '#F59E0B', '#FFFFFF', '#111827', TRUE);

-- Default typography settings
INSERT INTO `typography_settings` (`name`, `font_family`, `font_size_base`, `line_height`, `heading_weight`, `body_weight`) VALUES
('Inter Modern', 'Inter', 16, 1.5, 600, 400),
('Roboto Clean', 'Roboto', 16, 1.6, 500, 400),
('Open Sans Friendly', 'Open Sans', 16, 1.5, 600, 400),
('Lato Professional', 'Lato', 16, 1.5, 700, 400),
('Poppins Trendy', 'Poppins', 16, 1.4, 600, 400);

-- Default layout configurations
INSERT INTO `layout_configurations` (`name`, `container_width`, `border_radius`, `spacing_unit`, `header_height`, `footer_height`) VALUES
('Standard Layout', 1200, 8, 16, 80, 60),
('Wide Layout', 1400, 12, 20, 90, 70),
('Compact Layout', 1000, 6, 12, 70, 50),
('Full Width Layout', 1920, 0, 24, 100, 80);

-- Default component styles
INSERT INTO `component_styles` (`component_name`, `style_name`, `css_properties`) VALUES
('button', 'default', '{"border-radius": "8px", "padding": "12px 24px", "font-weight": "500"}'),
('button', 'rounded', '{"border-radius": "50px", "padding": "12px 24px", "font-weight": "500"}'),
('button', 'square', '{"border-radius": "0", "padding": "12px 24px", "font-weight": "500"}'),
('card', 'default', '{"border-radius": "8px", "box-shadow": "0 4px 6px -1px rgba(0, 0, 0, 0.1)", "padding": "16px"}'),
('card', 'elevated', '{"border-radius": "12px", "box-shadow": "0 10px 15px -3px rgba(0, 0, 0, 0.1)", "padding": "20px"}'),
('input', 'outlined', '{"border": "1px solid #D1D5DB", "border-radius": "8px", "padding": "12px 16px"}'),
('input', 'filled', '{"background-color": "#F3F4F6", "border": "none", "border-radius": "8px", "padding": "12px 16px"}');

-- Default theme configuration
INSERT INTO `theme_configurations` (`name`, `slug`, `config_data`, `css_variables`, `is_default`) VALUES
('ALADIN B2B Plus Default', 'aladin-default', 
'{"colors": {"primary": "#3B82F6", "secondary": "#10B981", "accent": "#F59E0B"}, "typography": {"family": "Inter", "size": 16}, "layout": {"width": 1200, "spacing": 16}}',
'{"--color-primary": "#3B82F6", "--color-secondary": "#10B981", "--color-accent": "#F59E0B", "--font-family": "Inter", "--font-size-base": "16px", "--layout-container-width": "1200px", "--layout-spacing-unit": "16px"}',
TRUE);

-- Countries with flags and metadata
CREATE TABLE IF NOT EXISTS `countries` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `code` VARCHAR(2) UNIQUE NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `native_name` VARCHAR(100),
  `flag_emoji` VARCHAR(10),
  `flag_svg` TEXT,
  `capital` VARCHAR(100),
  `region` VARCHAR(50),
  `subregion` VARCHAR(50),
  `population` BIGINT,
  `area` DECIMAL(15,2),
  `currencies` JSON,
  `languages` JSON,
  `timezones` JSON,
  `coordinates` JSON,
  `borders` JSON,
  `calling_codes` JSON,
  `tld` VARCHAR(10),
  `is_active` BOOLEAN DEFAULT TRUE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_code` (`code`),
  INDEX `idx_region` (`region`),
  INDEX `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Postal codes and localities
CREATE TABLE IF NOT EXISTS `postal_codes` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `country_code` VARCHAR(2) NOT NULL,
  `postal_code` VARCHAR(20) NOT NULL,
  `place_name` VARCHAR(180) NOT NULL,
  `admin_name1` VARCHAR(100),
  `admin_code1` VARCHAR(20),
  `admin_name2` VARCHAR(100),
  `admin_code2` VARCHAR(20),
  `admin_name3` VARCHAR(100),
  `admin_code3` VARCHAR(20),
  `latitude` DECIMAL(10, 8),
  `longitude` DECIMAL(11, 8),
  `accuracy` TINYINT,
  `is_active` BOOLEAN DEFAULT TRUE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_country_postal` (`country_code`, `postal_code`),
  INDEX `idx_place_name` (`place_name`),
  INDEX `idx_coordinates` (`latitude`, `longitude`),
  INDEX `idx_is_active` (`is_active`),
  FOREIGN KEY (`country_code`) REFERENCES `countries`(`code`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Product categories with Alibaba/Amazon compatibility
CREATE TABLE IF NOT EXISTS `product_categories` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `parent_id` BIGINT UNSIGNED NULL,
  `name` VARCHAR(100) NOT NULL,
  `slug` VARCHAR(100) NOT NULL,
  `description` TEXT,
  `icon_b2b` VARCHAR(255),
  `icon_b2c` VARCHAR(255),
  `alibaba_category_id` VARCHAR(50),
  `amazon_category_id` VARCHAR(50),
  `commission_rate_b2b` DECIMAL(5,2) DEFAULT 2.50,
  `commission_rate_b2c` DECIMAL(5,2) DEFAULT 5.00,
  `is_featured` BOOLEAN DEFAULT FALSE,
  `is_active` BOOLEAN DEFAULT TRUE,
  `sort_order` INT DEFAULT 0,
  `seo_title` VARCHAR(200),
  `seo_description` TEXT,
  `seo_keywords` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_parent_id` (`parent_id`),
  INDEX `idx_slug` (`slug`),
  INDEX `idx_is_featured` (`is_featured`),
  INDEX `idx_is_active` (`is_active`),
  INDEX `idx_sort_order` (`sort_order`),
  FOREIGN KEY (`parent_id`) REFERENCES `product_categories`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Exhibitions and trade fairs (365-day system)
CREATE TABLE IF NOT EXISTS `exhibitions` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(200) NOT NULL,
  `description` TEXT,
  `country_code` VARCHAR(2) NOT NULL,
  `city` VARCHAR(100) NOT NULL,
  `address` TEXT NOT NULL,
  `coordinates` JSON,
  `total_area` DECIMAL(10,2),
  `available_area` DECIMAL(10,2),
  `booth_sizes` JSON,
  `pricing_structure` JSON,
  `facilities` JSON,
  `opening_hours` JSON,
  `contact_info` JSON,
  `images` JSON,
  `status` ENUM('active', 'maintenance', 'closed') DEFAULT 'active',
  `managed_by` BIGINT UNSIGNED,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_country_code` (`country_code`),
  INDEX `idx_city` (`city`),
  INDEX `idx_status` (`status`),
  INDEX `idx_managed_by` (`managed_by`),
  FOREIGN KEY (`country_code`) REFERENCES `countries`(`code`),
  FOREIGN KEY (`managed_by`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Exhibition booths
CREATE TABLE IF NOT EXISTS `exhibition_booths` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `exhibition_id` BIGINT UNSIGNED NOT NULL,
  `booth_number` VARCHAR(20) NOT NULL,
  `size_category` ENUM('small', 'medium', 'large', 'premium') NOT NULL,
  `dimensions` JSON,
  `area_sqm` DECIMAL(8,2) NOT NULL,
  `base_price_monthly` DECIMAL(10,2) NOT NULL,
  `location_premium` DECIMAL(5,2) DEFAULT 0.00,
  `facilities_included` JSON,
  `max_occupancy` INT DEFAULT 1,
  `status` ENUM('available', 'reserved', 'occupied', 'maintenance') DEFAULT 'available',
  `floor_plan_position` JSON,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `unique_exhibition_booth` (`exhibition_id`, `booth_number`),
  INDEX `idx_size_category` (`size_category`),
  INDEX `idx_status` (`status`),
  INDEX `idx_base_price` (`base_price_monthly`),
  FOREIGN KEY (`exhibition_id`) REFERENCES `exhibitions`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- INSERT DEFAULT PRODUCT CATEGORIES
-- =====================================================

-- Main categories
INSERT INTO `product_categories` (`id`, `parent_id`, `name`, `slug`, `description`, `icon_b2b`, `icon_b2c`, `alibaba_category_id`, `amazon_category_id`, `commission_rate_b2b`, `commission_rate_b2c`, `is_featured`, `sort_order`) VALUES
(1, NULL, 'Electronics & Technology', 'electronics-technology', 'Electronic devices, components, and technology solutions', '/assets/category-icons/b2b/electronics-technology.png', '/assets/category-icons/b2c/electronics-gadgets.png', 'electronic-components-supplies', 'Electronics', 3.50, 6.00, TRUE, 1),
(2, NULL, 'Machinery & Industrial Equipment', 'machinery-industrial', 'Industrial machinery, manufacturing equipment, and tools', '/assets/category-icons/b2b/machinery-industrial.png', '/assets/category-icons/b2c/tools-hardware.png', 'machinery', 'Industrial & Scientific', 2.50, 5.50, TRUE, 2),
(3, NULL, 'Textiles & Apparel', 'textiles-apparel', 'Clothing, fabrics, and textile products', '/assets/category-icons/b2b/textiles-apparel.png', '/assets/category-icons/b2c/fashion-clothing.png', 'apparel', 'Clothing, Shoes & Jewelry', 4.00, 7.00, TRUE, 3),
(4, NULL, 'Home & Garden', 'home-garden', 'Home improvement, furniture, and garden products', '/assets/category-icons/b2b/home-garden.png', '/assets/category-icons/b2c/home-garden.png', 'home-garden', 'Home & Garden', 3.00, 6.50, TRUE, 4),
(5, NULL, 'Health & Beauty', 'health-beauty', 'Healthcare products, cosmetics, and wellness items', '/assets/category-icons/b2b/health-beauty.png', '/assets/category-icons/b2c/health-beauty.png', 'beauty-personal-care', 'Health & Personal Care', 4.50, 8.00, TRUE, 5),
(6, NULL, 'Food & Beverage', 'food-beverage', 'Food products, beverages, and agricultural goods', '/assets/category-icons/b2b/food-beverage.png', '/assets/category-icons/b2c/food-beverage.png', 'food-beverage', 'Grocery & Gourmet Food', 2.00, 5.00, TRUE, 6),
(7, NULL, 'Automotive & Transportation', 'automotive-transportation', 'Vehicle parts, automotive accessories, and transportation equipment', '/assets/category-icons/b2b/automotive.png', '/assets/category-icons/b2c/automotive.png', 'automobiles-motorcycles', 'Automotive', 3.00, 6.00, TRUE, 7),
(8, NULL, 'Sports & Recreation', 'sports-recreation', 'Sports equipment, recreational products, and fitness gear', '/assets/category-icons/b2b/sports-recreation.png', '/assets/category-icons/b2c/sports-outdoors.png', 'sports-entertainment', 'Sports & Outdoors', 3.50, 7.50, TRUE, 8),
(9, NULL, 'Office & School Supplies', 'office-school-supplies', 'Office equipment, stationery, and educational supplies', '/assets/category-icons/b2b/office-school-supplies.png', '/assets/category-icons/b2c/office-supplies.png', 'office-school-supplies', 'Office Products', 2.50, 5.50, FALSE, 9),
(10, NULL, 'Gifts & Crafts', 'gifts-crafts', 'Gift items, handicrafts, and promotional products', '/assets/category-icons/b2b/gifts-crafts.png', '/assets/category-icons/b2c/gifts-crafts.png', 'gifts-crafts', 'Arts, Crafts & Sewing', 4.00, 8.50, FALSE, 10);

-- Sub-categories for Electronics & Technology
INSERT INTO `product_categories` (`parent_id`, `name`, `slug`, `description`, `alibaba_category_id`, `amazon_category_id`, `commission_rate_b2b`, `commission_rate_b2c`, `sort_order`) VALUES
(1, 'Computer Hardware', 'computer-hardware', 'Computers, servers, and hardware components', 'computer-hardware-software', 'Computers & Accessories', 3.00, 5.50, 1),
(1, 'Mobile Devices', 'mobile-devices', 'Smartphones, tablets, and mobile accessories', 'telecommunications', 'Cell Phones & Accessories', 4.00, 7.00, 2),
(1, 'Electronic Components', 'electronic-components', 'Semiconductors, circuits, and electronic parts', 'electronic-components-supplies', 'Industrial & Scientific', 2.50, 4.50, 3),
(1, 'Audio & Video Equipment', 'audio-video-equipment', 'Sound systems, cameras, and multimedia devices', 'consumer-electronics', 'Electronics', 3.50, 6.50, 4);

-- Sub-categories for Machinery & Industrial Equipment
INSERT INTO `product_categories` (`parent_id`, `name`, `slug`, `description`, `alibaba_category_id`, `amazon_category_id`, `commission_rate_b2b`, `commission_rate_b2c`, `sort_order`) VALUES
(2, 'Manufacturing Equipment', 'manufacturing-equipment', 'Production machinery and manufacturing tools', 'machinery', 'Industrial & Scientific', 2.00, 4.00, 1),
(2, 'Construction Equipment', 'construction-equipment', 'Building and construction machinery', 'construction-real-estate', 'Tools & Home Improvement', 2.50, 5.00, 2),
(2, 'Agricultural Machinery', 'agricultural-machinery', 'Farming equipment and agricultural tools', 'agriculture', 'Patio, Lawn & Garden', 2.00, 4.50, 3),
(2, 'Power Tools', 'power-tools', 'Electric and pneumatic tools for professionals', 'tools-hardware', 'Tools & Home Improvement', 3.00, 6.00, 4);
CREATE INDEX `idx_categories_parent_active` ON `product_categories` (`parent_id`, `is_active`);
CREATE INDEX `idx_exhibitions_country_status` ON `exhibitions` (`country_code`, `status`);
CREATE INDEX `idx_booths_exhibition_status` ON `exhibition_booths` (`exhibition_id`, `status`);
CREATE INDEX `idx_customizations_active` ON `website_customizations` (`is_active`, `updated_at`);

-- =====================================================
-- FOREIGN KEY CONSTRAINTS
-- =====================================================

-- Add foreign key constraints where missing
ALTER TABLE `website_customizations` ADD CONSTRAINT `fk_customizations_created_by` FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL;
ALTER TABLE `branding_assets` ADD CONSTRAINT `fk_branding_uploaded_by` FOREIGN KEY (`uploaded_by`) REFERENCES `users`(`id`) ON DELETE SET NULL;

-- =====================================================
-- TRIGGERS FOR AUDIT LOGGING
-- =====================================================

DELIMITER $$

CREATE TRIGGER `tr_website_customizations_updated` 
BEFORE UPDATE ON `website_customizations`
FOR EACH ROW
BEGIN
    SET NEW.updated_at = CURRENT_TIMESTAMP;
END$$

CREATE TRIGGER `tr_theme_configurations_updated` 
BEFORE UPDATE ON `theme_configurations`
FOR EACH ROW
BEGIN
    SET NEW.updated_at = CURRENT_TIMESTAMP;
END$$

DELIMITER ;

-- Product categories hierarchy view
CREATE OR REPLACE VIEW `v_category_hierarchy` AS
SELECT 
    c.id,
    c.name,
    c.slug,
    c.parent_id,
    p.name as parent_name,
    c.commission_rate_b2b,
    c.commission_rate_b2c,
    c.is_featured,
    c.is_active,
    c.sort_order
FROM `product_categories` c
LEFT JOIN `product_categories` p ON c.parent_id = p.id
WHERE c.is_active = TRUE
ORDER BY c.parent_id, c.sort_order;

-- Exhibition availability view
CREATE OR REPLACE VIEW `v_exhibition_availability` AS
SELECT 
    e.id as exhibition_id,
    e.name as exhibition_name,
    e.country_code,
    e.city,
    COUNT(eb.id) as total_booths,
    COUNT(CASE WHEN eb.status = 'available' THEN 1 END) as available_booths,
    COUNT(CASE WHEN eb.status = 'occupied' THEN 1 END) as occupied_booths,
    ROUND((COUNT(CASE WHEN eb.status = 'occupied' THEN 1 END) / COUNT(eb.id)) * 100, 2) as occupancy_rate
FROM `exhibitions` e
LEFT JOIN `exhibition_booths` eb ON e.id = eb.exhibition_id
WHERE e.status = 'active'
GROUP BY e.id, e.name, e.country_code, e.city;

-- =====================================================
-- STORED PROCEDURES
-- =====================================================

DELIMITER $$

-- Procedure to activate website customization
CREATE PROCEDURE `sp_activate_customization`(IN customization_id VARCHAR(36))
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    -- Deactivate all current customizations
    UPDATE `website_customizations` SET `is_active` = FALSE WHERE `is_active` = TRUE;
    
    -- Activate the specified customization
    UPDATE `website_customizations` SET `is_active` = TRUE WHERE `id` = customization_id;
    
    COMMIT;
END$$

-- Procedure to get available booths for exhibition
CREATE PROCEDURE `sp_get_available_booths`(IN exhibition_id BIGINT, IN size_category VARCHAR(20))
BEGIN
    SELECT 
        eb.*,
        e.name as exhibition_name,
        e.city,
        e.country_code
    FROM `exhibition_booths` eb
    JOIN `exhibitions` e ON eb.exhibition_id = e.id
    WHERE eb.exhibition_id = exhibition_id
    AND (size_category IS NULL OR eb.size_category = size_category)
    AND eb.status = 'available'
    ORDER BY eb.booth_number;
END$$

DELIMITER ;

-- =====================================================
-- FINAL OPTIMIZATIONS
-- =====================================================

-- Optimize tables
OPTIMIZE TABLE `website_customizations`;
OPTIMIZE TABLE `website_templates`;
OPTIMIZE TABLE `theme_configurations`;
OPTIMIZE TABLE `branding_assets`;
OPTIMIZE TABLE `color_schemes`;
OPTIMIZE TABLE `typography_settings`;
OPTIMIZE TABLE `layout_configurations`;
OPTIMIZE TABLE `component_styles`;
OPTIMIZE TABLE `countries`;
OPTIMIZE TABLE `postal_codes`;
OPTIMIZE TABLE `product_categories`;
OPTIMIZE TABLE `exhibitions`;
OPTIMIZE TABLE `exhibition_booths`;

-- =====================================================
-- COMPLETION MESSAGE
-- =====================================================

SELECT 'ALADIN B2B Plus Ultimate Platform Database Schema with Website Customization - Successfully Created!' as Status;
