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

-- =====================================================
-- EXISTING TABLES (Previous Schema)
-- =====================================================

-- Users table with all roles
CREATE TABLE IF NOT EXISTS `users` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(50) UNIQUE NOT NULL,
  `email` VARCHAR(100) UNIQUE NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `first_name` VARCHAR(50),
  `last_name` VARCHAR(50),
  `phone` VARCHAR(20),
  `role` ENUM('super_admin', 'country_admin', 'seller', 'buyer', 'trustee', 'employee') NOT NULL DEFAULT 'buyer',
  `status` ENUM('active', 'inactive', 'suspended', 'pending') DEFAULT 'pending',
  `country_code` VARCHAR(2),
  `language` VARCHAR(5) DEFAULT 'en',
  `currency` VARCHAR(3) DEFAULT 'EUR',
  `timezone` VARCHAR(50) DEFAULT 'UTC',
  `avatar` VARCHAR(255),
  `email_verified_at` TIMESTAMP NULL,
  `phone_verified_at` TIMESTAMP NULL,
  `two_factor_enabled` BOOLEAN DEFAULT FALSE,
  `two_factor_secret` VARCHAR(255),
  `last_login_at` TIMESTAMP NULL,
  `last_login_ip` VARCHAR(45),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_email` (`email`),
  INDEX `idx_role` (`role`),
  INDEX `idx_status` (`status`),
  INDEX `idx_country_code` (`country_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

-- Payment methods including Wise Business
CREATE TABLE IF NOT EXISTS `payment_methods` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `slug` VARCHAR(100) UNIQUE NOT NULL,
  `provider` VARCHAR(100) NOT NULL,
  `type` ENUM('card', 'bank_transfer', 'digital_wallet', 'cryptocurrency', 'buy_now_pay_later') NOT NULL,
  `supported_currencies` JSON,
  `supported_countries` JSON,
  `transaction_fee_percentage` DECIMAL(5,4) DEFAULT 0.0000,
  `transaction_fee_fixed` DECIMAL(10,2) DEFAULT 0.00,
  `min_amount` DECIMAL(10,2) DEFAULT 0.01,
  `max_amount` DECIMAL(15,2),
  `processing_time` VARCHAR(50),
  `api_credentials` JSON,
  `webhook_url` VARCHAR(255),
  `is_active` BOOLEAN DEFAULT TRUE,
  `is_featured` BOOLEAN DEFAULT FALSE,
  `logo_url` VARCHAR(255),
  `description` TEXT,
  `setup_instructions` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_slug` (`slug`),
  INDEX `idx_provider` (`provider`),
  INDEX `idx_type` (`type`),
  INDEX `idx_is_active` (`is_active`),
  INDEX `idx_is_featured` (`is_featured`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Translations for all system components
CREATE TABLE IF NOT EXISTS `translations` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `key` VARCHAR(255) NOT NULL,
  `language` VARCHAR(5) NOT NULL,
  `value` TEXT NOT NULL,
  `context` VARCHAR(100),
  `is_validated` BOOLEAN DEFAULT FALSE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `unique_key_language` (`key`, `language`),
  INDEX `idx_language` (`language`),
  INDEX `idx_context` (`context`),
  INDEX `idx_is_validated` (`is_validated`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- System settings
CREATE TABLE IF NOT EXISTS `system_settings` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `key` VARCHAR(100) UNIQUE NOT NULL,
  `value` TEXT,
  `type` ENUM('string', 'integer', 'boolean', 'json', 'text') DEFAULT 'string',
  `category` VARCHAR(50) DEFAULT 'general',
  `description` TEXT,
  `is_public` BOOLEAN DEFAULT FALSE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_category` (`category`),
  INDEX `idx_is_public` (`is_public`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- INSERT DEFAULT PAYMENT METHODS INCLUDING WISE
-- =====================================================

INSERT INTO `payment_methods` (`name`, `slug`, `provider`, `type`, `supported_currencies`, `supported_countries`, `transaction_fee_percentage`, `transaction_fee_fixed`, `processing_time`, `is_active`, `is_featured`, `logo_url`, `description`) VALUES
('Wise Business', 'wise-business', 'Wise', 'bank_transfer', '["EUR", "USD", "GBP", "CAD", "AUD", "CHF", "SEK", "NOK", "DKK", "PLN", "CZK", "HUF", "RON", "BGN", "HRK", "JPY", "SGD", "HKD", "NZD", "ZAR"]', '["DE", "FR", "GB", "US", "CA", "AU", "CH", "SE", "NO", "DK", "PL", "CZ", "HU", "RO", "BG", "HR", "JP", "SG", "HK", "NZ", "ZA", "BR", "TR", "EG", "AE", "SA", "CN"]', 0.0050, 0.00, '1-2 business days', TRUE, TRUE, '/assets/payment-methods/wise-logo.svg', 'Globale Geschäftsüberweisungen mit echten Wechselkursen und transparenten Gebühren'),
('PayPal Business', 'paypal-business', 'PayPal', 'digital_wallet', '["EUR", "USD", "GBP", "CAD", "AUD", "JPY"]', '["DE", "FR", "GB", "US", "CA", "AU", "JP", "IT", "ES", "NL", "BE", "AT", "CH", "SE", "NO", "DK", "FI", "PL"]', 0.0290, 0.35, 'Instant', TRUE, TRUE, '/assets/payment-methods/paypal-logo.svg', 'Sichere Online-Zahlungen für Geschäftskunden weltweit'),
('Stripe', 'stripe', 'Stripe', 'card', '["EUR", "USD", "GBP", "CAD", "AUD", "CHF", "SEK", "NOK", "DKK", "PLN", "JPY"]', '["DE", "FR", "GB", "US", "CA", "AU", "CH", "SE", "NO", "DK", "FI", "NL", "BE", "AT", "IT", "ES", "PL", "JP"]', 0.0290, 0.30, 'Instant', TRUE, TRUE, '/assets/payment-methods/stripe-logo.svg', 'Kreditkarten- und Online-Zahlungsabwicklung für Unternehmen'),
('SEPA Direct Debit', 'sepa-direct-debit', 'SEPA', 'bank_transfer', '["EUR"]', '["DE", "FR", "IT", "ES", "NL", "BE", "AT", "PT", "IE", "GR", "FI", "LU", "SK", "SI", "EE", "LV", "LT", "CY", "MT"]', 0.0080, 0.00, '1-3 business days', TRUE, FALSE, '/assets/payment-methods/sepa-logo.svg', 'Europäische Lastschriftverfahren für wiederkehrende Zahlungen'),
('Bitcoin', 'bitcoin', 'BitPay', 'cryptocurrency', '["BTC", "EUR", "USD"]', '["GLOBAL"]', 0.0100, 0.00, '10-60 minutes', TRUE, FALSE, '/assets/payment-methods/bitcoin-logo.svg', 'Kryptowährungszahlungen mit Bitcoin'),
('Alipay', 'alipay', 'Alipay', 'digital_wallet', '["CNY", "USD", "EUR"]', '["CN", "HK", "MO", "TW", "SG", "MY", "TH", "PH", "ID", "VN", "KR", "JP"]', 0.0200, 0.00, 'Instant', TRUE, FALSE, '/assets/payment-methods/alipay-logo.svg', 'Chinesische Digital Wallet für asiatische Märkte'),
('WeChat Pay', 'wechat-pay', 'WeChat', 'digital_wallet', '["CNY", "USD", "EUR"]', '["CN", "HK", "MO", "TW", "SG", "MY", "TH", "PH", "ID", "VN", "KR", "JP"]', 0.0200, 0.00, 'Instant', TRUE, FALSE, '/assets/payment-methods/wechat-logo.svg', 'Chinesische Mobile Payment Lösung'),
('Apple Pay', 'apple-pay', 'Apple', 'digital_wallet', '["EUR", "USD", "GBP", "CAD", "AUD", "JPY"]', '["US", "CA", "GB", "AU", "FR", "DE", "IT", "ES", "CH", "SE", "NO", "DK", "FI", "NL", "BE", "AT", "JP"]', 0.0290, 0.30, 'Instant', TRUE, FALSE, '/assets/payment-methods/apple-pay-logo.svg', 'Mobile Zahlungen für iOS-Geräte'),
('Google Pay', 'google-pay', 'Google', 'digital_wallet', '["EUR", "USD", "GBP", "CAD", "AUD", "JPY"]', '["US", "CA", "GB", "AU", "FR", "DE", "IT", "ES", "CH", "SE", "NO", "DK", "FI", "NL", "BE", "AT", "JP", "IN", "SG"]', 0.0290, 0.30, 'Instant', TRUE, FALSE, '/assets/payment-methods/google-pay-logo.svg', 'Mobile Zahlungen für Android-Geräte'),
('Klarna', 'klarna', 'Klarna', 'buy_now_pay_later', '["EUR", "USD", "GBP", "SEK", "NOK", "DKK"]', '["DE", "AT", "NL", "BE", "CH", "FR", "IT", "ES", "FI", "NO", "SE", "DK", "GB", "US"]', 0.0300, 0.30, 'Instant', TRUE, FALSE, '/assets/payment-methods/klarna-logo.svg', 'Kauf auf Rechnung und Ratenzahlung für B2B-Kunden');

-- =====================================================
-- 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);

-- =====================================================
-- DEFAULT SYSTEM SETTINGS
-- =====================================================

INSERT INTO `system_settings` (`key`, `value`, `type`, `category`, `description`, `is_public`) VALUES
('site_name', 'ALADIN B2B Plus Ultimate Platform', 'string', 'general', 'Website name', TRUE),
('site_description', 'Revolutionary 365-day B2B marketplace with physical exhibitions', 'text', 'general', 'Website description', TRUE),
('default_language', 'en', 'string', 'localization', 'Default system language', TRUE),
('default_currency', 'EUR', 'string', 'financial', 'Default system currency', TRUE),
('default_timezone', 'UTC', 'string', 'general', 'Default system timezone', FALSE),
('maintenance_mode', 'false', 'boolean', 'system', 'Enable maintenance mode', FALSE),
('registration_enabled', 'true', 'boolean', 'user', 'Allow new user registrations', TRUE),
('email_verification_required', 'true', 'boolean', 'user', 'Require email verification for new users', FALSE),
('two_factor_enabled', 'true', 'boolean', 'security', 'Enable two-factor authentication', FALSE),
('commission_rate_default_b2b', '2.50', 'string', 'financial', 'Default B2B commission rate (%)', FALSE),
('commission_rate_default_b2c', '5.00', 'string', 'financial', 'Default B2C commission rate (%)', FALSE),
('wise_business_enabled', 'true', 'boolean', 'payment', 'Enable Wise Business payments', FALSE),
('exhibition_booking_enabled', 'true', 'boolean', 'exhibition', 'Enable exhibition booth booking', TRUE),
('max_upload_size', '10485760', 'integer', 'system', 'Maximum file upload size in bytes (10MB)', FALSE),
('supported_languages', '["en", "de", "fr", "es", "it", "pt", "ru", "zh", "ja", "ar", "tr", "nl"]', 'json', 'localization', 'Supported system languages', TRUE),
('supported_currencies', '["EUR", "USD", "GBP", "CAD", "AUD", "CHF", "SEK", "NOK", "DKK", "PLN", "JPY", "CNY"]', 'json', 'financial', 'Supported currencies', TRUE);

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

-- Additional indexes for better performance
CREATE INDEX `idx_users_role_status` ON `users` (`role`, `status`);
CREATE INDEX `idx_users_country_language` ON `users` (`country_code`, `language`);
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_payments_provider_active` ON `payment_methods` (`provider`, `is_active`);
CREATE INDEX `idx_translations_key_context` ON `translations` (`key`, `context`);
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 ;

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

-- Active website customizations view
CREATE OR REPLACE VIEW `v_active_customizations` AS
SELECT 
    wc.*,
    u.username as created_by_username,
    u.email as created_by_email
FROM `website_customizations` wc
LEFT JOIN `users` u ON wc.created_by = u.id
WHERE wc.is_active = TRUE
ORDER BY wc.updated_at DESC;

-- 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 `users`;
OPTIMIZE TABLE `countries`;
OPTIMIZE TABLE `postal_codes`;
OPTIMIZE TABLE `product_categories`;
OPTIMIZE TABLE `exhibitions`;
OPTIMIZE TABLE `exhibition_booths`;
OPTIMIZE TABLE `payment_methods`;
OPTIMIZE TABLE `translations`;
OPTIMIZE TABLE `system_settings`;

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

SELECT 'ALADIN B2B Plus Ultimate Platform Database Schema with Website Customization - Successfully Created!' as Status;
SELECT COUNT(*) as 'Total Tables Created' FROM information_schema.tables WHERE table_schema = DATABASE();
SELECT COUNT(*) as 'Total Indexes Created' FROM information_schema.statistics WHERE table_schema = DATABASE();
SELECT COUNT(*) as 'Total Views Created' FROM information_schema.views WHERE table_schema = DATABASE();
