-- ============================================================================
-- BASE DE DONNÉES UNIFIÉE - MULTI-MARQUES
-- Version: 3.0.0
-- Date: 2026-01-25
--
-- Couleurs officielles:
-- - Admin: #b261b3 | URI: #6060b9 | Q4NA: #41b8d2 | LEI: #6ec84f
-- - SSO: #ffa42f | Quiz: #f27d3e | CLI: #e53c51
-- ============================================================================


-- ============================================================================
-- DATABASE CREATION
-- ============================================================================
CREATE DATABASE IF NOT EXISTS `uri_admin`
DEFAULT CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE `uri_admin`;

-- Table: admin_logs
CREATE TABLE `admin_logs` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `brand_id` INT UNSIGNED DEFAULT NULL,
    `user_id` INT UNSIGNED DEFAULT NULL,
    `action` VARCHAR(100) NOT NULL,
    `entity_type` VARCHAR(100) DEFAULT NULL,
    `entity_id` INT UNSIGNED DEFAULT NULL,
    `module` VARCHAR(50) DEFAULT NULL,
    `description` TEXT DEFAULT NULL,
    `old_values` JSON DEFAULT NULL,
    `new_values` JSON DEFAULT NULL,
    `ip_address` VARCHAR(45) DEFAULT NULL,
    `user_agent` TEXT DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_admin_logs_brand` (`brand_id`),
    KEY `idx_admin_logs_user` (`user_id`),
    KEY `idx_admin_logs_created` (`created_at`),
    KEY `idx_admin_logs_action` (`action`),
    KEY `idx_admin_logs_entity` (`entity_type`, `entity_id`),
    CONSTRAINT `fk_admin_logs_brand` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_admin_logs_user` FOREIGN KEY (`user_id`) REFERENCES `admin_users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: admin_notifications
CREATE TABLE `admin_notifications` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `brand_id` INT UNSIGNED DEFAULT NULL,
    `user_id` INT UNSIGNED DEFAULT NULL,
    `type` VARCHAR(50) NOT NULL DEFAULT 'info',
    `title` VARCHAR(255) NOT NULL,
    `message` TEXT DEFAULT NULL,
    `link` VARCHAR(500) DEFAULT NULL,
    `icon` VARCHAR(50) DEFAULT 'bi-bell',
    `is_read` TINYINT(1) NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_admin_notifications_brand` (`brand_id`),
    KEY `idx_admin_notifications_user` (`user_id`),
    KEY `idx_admin_notifications_read` (`is_read`),
    CONSTRAINT `fk_admin_notifications_brand` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_admin_notifications_user` FOREIGN KEY (`user_id`) REFERENCES `admin_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: settings
CREATE TABLE `settings` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `brand_id` INT UNSIGNED DEFAULT NULL,
    `setting_key` VARCHAR(100) NOT NULL,
    `setting_value` TEXT DEFAULT NULL,
    `setting_type` ENUM('string', 'int', 'float', 'bool', 'json') NOT NULL DEFAULT 'string',
    `category` VARCHAR(50) DEFAULT 'general',
    `description` VARCHAR(255) DEFAULT NULL,
    `is_sensitive` TINYINT(1) NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_settings_key_brand` (`setting_key`, `brand_id`),
    KEY `idx_settings_brand_category` (`brand_id`, `category`),
    CONSTRAINT `fk_settings_brand` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: modules
CREATE TABLE `modules` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `brand_id` INT UNSIGNED DEFAULT NULL,
    `name` VARCHAR(50) NOT NULL,
    `display_name` VARCHAR(100) NOT NULL,
    `icon` VARCHAR(50) DEFAULT NULL,
    `route` VARCHAR(100) DEFAULT NULL,
    `position` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_modules_name_brand` (`name`, `brand_id`),
    KEY `idx_modules_brand` (`brand_id`),
    KEY `idx_modules_position` (`position`),
    CONSTRAINT `fk_modules_brand` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: customer_tags
CREATE TABLE `customer_tags` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `brand_id` INT UNSIGNED NOT NULL,
    `name` VARCHAR(50) NOT NULL,
    `color` VARCHAR(7) DEFAULT '#6b7280',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_customer_tags_name_brand` (`name`, `brand_id`),
    KEY `idx_customer_tags_brand` (`brand_id`),
    CONSTRAINT `fk_customer_tags_brand` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: customer_tag_relations
CREATE TABLE `customer_tag_relations` (
    `customer_id` INT UNSIGNED NOT NULL,
    `tag_id` INT UNSIGNED NOT NULL,
    PRIMARY KEY (`customer_id`, `tag_id`),
    KEY `idx_customer_tag_relations_tag` (`tag_id`),
    CONSTRAINT `fk_customer_tag_relations_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_customer_tag_relations_tag` FOREIGN KEY (`tag_id`) REFERENCES `customer_tags` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- PARTIE 4 : TABLES EBOOKS & CONTENU
-- ============================================================================

-- Table: ebook_categories
CREATE TABLE `ebook_categories` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `brand_id` INT UNSIGNED NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `slug` VARCHAR(120) NOT NULL,
    `description` TEXT DEFAULT NULL,
    `icon` VARCHAR(50) DEFAULT NULL,
    `color` VARCHAR(7) DEFAULT NULL,
    `display_order` INT NOT NULL DEFAULT 0,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_ebook_cat_slug_brand` (`brand_id`, `slug`),
    KEY `idx_ebook_cat_brand` (`brand_id`),
    KEY `idx_ebook_cat_order` (`display_order`),
    CONSTRAINT `fk_ebook_cat_brand` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: ebooks
CREATE TABLE `ebooks` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `brand_id` INT UNSIGNED NOT NULL,
    `category_id` INT UNSIGNED DEFAULT NULL,
    `title` VARCHAR(255) NOT NULL,
    `slug` VARCHAR(255) NOT NULL,
    `subtitle` VARCHAR(255) DEFAULT NULL,
    `description_short` VARCHAR(500) DEFAULT NULL,
    `description_long` TEXT DEFAULT NULL,
    `price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `compare_price` DECIMAL(10,2) DEFAULT NULL,
    `currency` VARCHAR(3) NOT NULL DEFAULT 'EUR',
    `cover_image` VARCHAR(255) DEFAULT NULL,
    `pdf_file` VARCHAR(255) DEFAULT NULL,
    `pdf_preview` VARCHAR(255) DEFAULT NULL,
    `workbook_pdf` VARCHAR(255) DEFAULT NULL,
    `kdp_link` VARCHAR(500) DEFAULT NULL,
    `kdp_workbook_qrcode` VARCHAR(255) DEFAULT NULL,
    `stripe_product_id` VARCHAR(255) DEFAULT NULL,
    `stripe_price_id` VARCHAR(255) DEFAULT NULL,
    `meta_title` VARCHAR(255) DEFAULT NULL,
    `meta_description` TEXT DEFAULT NULL,
    `total_sales` INT UNSIGNED NOT NULL DEFAULT 0,
    `total_revenue` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `status` ENUM('draft', 'active', 'archived') NOT NULL DEFAULT 'draft',
    `is_featured` TINYINT(1) NOT NULL DEFAULT 0,
    `display_order` INT NOT NULL DEFAULT 0,
    `publish_date` DATE DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_ebooks_slug_brand` (`slug`, `brand_id`),
    KEY `idx_ebooks_brand` (`brand_id`),
    KEY `idx_ebooks_brand_status` (`brand_id`, `status`),
    KEY `idx_ebooks_status` (`status`),
    KEY `idx_ebooks_category` (`category_id`),
    CONSTRAINT `fk_ebooks_brand` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_ebooks_category` FOREIGN KEY (`category_id`) REFERENCES `ebook_categories` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: chapters
CREATE TABLE `chapters` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `ebook_id` INT UNSIGNED NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `position` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `is_free` TINYINT(1) NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_chapters_ebook` (`ebook_id`),
    KEY `idx_chapters_position` (`position`),
    CONSTRAINT `fk_chapters_ebook` FOREIGN KEY (`ebook_id`) REFERENCES `ebooks` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: exercises
CREATE TABLE `exercises` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `chapter_id` INT UNSIGNED NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `instructions` TEXT DEFAULT NULL,
    `exercise_type` ENUM('text', 'qcm_single', 'qcm_multiple', 'scale', 'date') NOT NULL DEFAULT 'text',
    `options` JSON DEFAULT NULL,
    `is_required` TINYINT(1) NOT NULL DEFAULT 0,
    `is_premium` TINYINT(1) NOT NULL DEFAULT 0,
    `position` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_exercises_chapter` (`chapter_id`),
    KEY `idx_exercises_position` (`position`),
    CONSTRAINT `fk_exercises_chapter` FOREIGN KEY (`chapter_id`) REFERENCES `chapters` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: promo_codes
CREATE TABLE `promo_codes` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `brand_id` INT UNSIGNED NOT NULL,
    `code` VARCHAR(50) NOT NULL,
    `description` VARCHAR(255) DEFAULT NULL,
    `type` ENUM('percentage', 'fixed', 'percent') NOT NULL DEFAULT 'percentage',
    `value` DECIMAL(10,2) NOT NULL,
    `min_order_amount` DECIMAL(10,2) DEFAULT NULL,
    `max_discount_amount` DECIMAL(10,2) DEFAULT NULL,
    `applies_to` ENUM('all', 'ebooks', 'subscriptions', 'products') NOT NULL DEFAULT 'all',
    `specific_item_ids` JSON DEFAULT NULL,
    `usage_limit` INT UNSIGNED DEFAULT NULL,
    `usage_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `usage_limit_per_customer` TINYINT UNSIGNED DEFAULT NULL,
    `is_cumulative` TINYINT(1) NOT NULL DEFAULT 0,
    `starts_at` DATETIME DEFAULT NULL,
    `expires_at` DATETIME DEFAULT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_promo_codes_code_brand` (`code`, `brand_id`),
    KEY `idx_promo_codes_brand` (`brand_id`),
    KEY `idx_promo_codes_brand_active` (`brand_id`, `is_active`),
    CONSTRAINT `fk_promo_codes_brand` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: promo_code_usage
CREATE TABLE `promo_code_usage` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `promo_code_id` INT UNSIGNED NOT NULL,
    `customer_id` INT UNSIGNED NOT NULL,
    `order_id` INT UNSIGNED DEFAULT NULL,
    `discount_amount` DECIMAL(10,2) NOT NULL,
    `used_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_promo_code_usage_promo` (`promo_code_id`),
    KEY `idx_promo_code_usage_customer` (`customer_id`),
    CONSTRAINT `fk_promo_code_usage_promo` FOREIGN KEY (`promo_code_id`) REFERENCES `promo_codes` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_promo_code_usage_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: subscriptions (abonnements)
CREATE TABLE `subscriptions` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `brand_id` INT UNSIGNED NOT NULL,
    `customer_id` INT UNSIGNED DEFAULT NULL,
    `user_id` INT UNSIGNED DEFAULT NULL COMMENT 'Pour Q4NA users',
    `email` VARCHAR(255) NOT NULL,
    `plan_type` ENUM('monthly', 'yearly', 'lifetime') NOT NULL DEFAULT 'monthly',
    `plan_name` VARCHAR(100) DEFAULT NULL,
    `amount` DECIMAL(10,2) NOT NULL,
    `currency` VARCHAR(3) NOT NULL DEFAULT 'EUR',
    `status` ENUM('active', 'past_due', 'cancelled', 'expired', 'trialing', 'paused') NOT NULL DEFAULT 'active',
    `stripe_subscription_id` VARCHAR(255) DEFAULT NULL,
    `stripe_customer_id` VARCHAR(255) DEFAULT NULL,
    `current_period_start` DATETIME DEFAULT NULL,
    `current_period_end` DATETIME DEFAULT NULL,
    `trial_ends_at` DATETIME DEFAULT NULL,
    `cancelled_at` DATETIME DEFAULT NULL,
    `cancel_at_period_end` TINYINT(1) NOT NULL DEFAULT 0,
    `expires_at` DATETIME DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_subscriptions_brand` (`brand_id`),
    KEY `idx_subscriptions_brand_status` (`brand_id`, `status`),
    KEY `idx_subscriptions_customer` (`customer_id`),
    KEY `idx_subscriptions_user` (`user_id`),
    KEY `idx_subscriptions_stripe` (`stripe_subscription_id`),
    KEY `idx_subscriptions_status` (`status`),
    KEY `idx_subscriptions_expires` (`expires_at`),
    CONSTRAINT `fk_subscriptions_brand` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_subscriptions_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: abandoned_carts (paniers abandonnés)
CREATE TABLE `abandoned_carts` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `brand_id` INT UNSIGNED NOT NULL,
    `customer_id` INT UNSIGNED DEFAULT NULL,
    `email` VARCHAR(255) DEFAULT NULL,
    `cart_data` JSON DEFAULT NULL COMMENT 'Contenu du panier',
    `total_amount` DECIMAL(10,2) DEFAULT NULL,
    `currency` VARCHAR(3) NOT NULL DEFAULT 'EUR',
    `source_url` VARCHAR(500) DEFAULT NULL,
    `utm_source` VARCHAR(100) DEFAULT NULL,
    `utm_medium` VARCHAR(100) DEFAULT NULL,
    `utm_campaign` VARCHAR(100) DEFAULT NULL,
    `recovered` TINYINT(1) NOT NULL DEFAULT 0,
    `recovered_at` DATETIME DEFAULT NULL,
    `recovered_order_id` INT UNSIGNED DEFAULT NULL,
    `reminder_1_sent` DATETIME DEFAULT NULL,
    `reminder_2_sent` DATETIME DEFAULT NULL,
    `reminder_3_sent` DATETIME DEFAULT NULL,
    `ip_address` VARCHAR(45) DEFAULT NULL,
    `user_agent` TEXT DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_abandoned_carts_brand` (`brand_id`),
    KEY `idx_abandoned_carts_customer` (`customer_id`),
    KEY `idx_abandoned_carts_email` (`email`),
    KEY `idx_abandoned_carts_recovered` (`recovered`),
    KEY `idx_abandoned_carts_created` (`created_at`),
    CONSTRAINT `fk_abandoned_carts_brand` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_abandoned_carts_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- PARTIE 6 : TABLES QUIZ / LEADMAGNETS
-- ============================================================================

-- Table: quizzes
CREATE TABLE `quizzes` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `brand_id` INT UNSIGNED NOT NULL,
    `ebook_id` INT UNSIGNED DEFAULT NULL,
    `title` VARCHAR(255) NOT NULL,
    `slug` VARCHAR(255) NOT NULL,
    `description` TEXT DEFAULT NULL,
    `intro_text` TEXT DEFAULT NULL,
    `qr_code` VARCHAR(255) DEFAULT NULL,
    `email_capture` ENUM('before_result', 'after_result', 'before', 'after', 'disabled') NOT NULL DEFAULT 'after',
    `email_capture_text` VARCHAR(500) DEFAULT NULL,
    `special_offer_enabled` TINYINT(1) NOT NULL DEFAULT 1,
    `special_offer_discount` INT NOT NULL DEFAULT 20,
    `special_offer_duration_hours` INT NOT NULL DEFAULT 24,
    `total_started` INT UNSIGNED NOT NULL DEFAULT 0,
    `total_completed` INT UNSIGNED NOT NULL DEFAULT 0,
    `total_emails` INT UNSIGNED NOT NULL DEFAULT 0,
    `status` ENUM('draft', 'active', 'archived') NOT NULL DEFAULT 'draft',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_quizzes_slug_brand` (`slug`, `brand_id`),
    KEY `idx_quizzes_brand` (`brand_id`),
    KEY `idx_quizzes_brand_status` (`brand_id`, `status`),
    KEY `idx_quizzes_ebook` (`ebook_id`),
    CONSTRAINT `fk_quizzes_brand` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_quizzes_ebook` FOREIGN KEY (`ebook_id`) REFERENCES `ebooks` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: quiz_questions
CREATE TABLE `quiz_questions` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `quiz_id` INT UNSIGNED NOT NULL,
    `question` TEXT NOT NULL,
    `question_text` TEXT DEFAULT NULL,
    `question_type` ENUM('single_choice', 'multiple_choice', 'scale', 'single', 'multiple') NOT NULL DEFAULT 'single_choice',
    `options` JSON DEFAULT NULL,
    `sort_order` INT NOT NULL DEFAULT 0,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_quiz_questions_quiz` (`quiz_id`),
    KEY `idx_quiz_questions_order` (`quiz_id`, `sort_order`),
    CONSTRAINT `fk_quiz_questions_quiz` FOREIGN KEY (`quiz_id`) REFERENCES `quizzes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: quiz_profiles
CREATE TABLE `quiz_profiles` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `quiz_id` INT UNSIGNED NOT NULL,
    `profile_key` VARCHAR(50) NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `color` VARCHAR(7) NOT NULL DEFAULT '#6060b9',
    `emoji` VARCHAR(10) DEFAULT NULL,
    `short_description` VARCHAR(255) DEFAULT NULL,
    `long_description` TEXT DEFAULT NULL,
    `strengths` JSON DEFAULT NULL,
    `challenges` JSON DEFAULT NULL,
    `recommendations` JSON DEFAULT NULL,
    `cta_text` VARCHAR(100) DEFAULT NULL,
    `cta_url` VARCHAR(255) DEFAULT NULL,
    `logic` JSON DEFAULT NULL,
    `display_order` INT NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_quiz_profiles_quiz` (`quiz_id`),
    CONSTRAINT `fk_quiz_profiles_quiz` FOREIGN KEY (`quiz_id`) REFERENCES `quizzes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: quiz_responses
CREATE TABLE `quiz_responses` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `quiz_id` INT UNSIGNED NOT NULL,
    `customer_id` INT UNSIGNED DEFAULT NULL,
    `email` VARCHAR(255) DEFAULT NULL,
    `firstname` VARCHAR(100) DEFAULT NULL,
    `answers` JSON DEFAULT NULL,
    `result_data` JSON DEFAULT NULL,
    `profile_id` INT UNSIGNED DEFAULT NULL,
    `status` ENUM('started', 'completed', 'abandoned') NOT NULL DEFAULT 'started',
    `completed_at` DATETIME DEFAULT NULL,
    `ip_address` VARCHAR(45) DEFAULT NULL,
    `user_agent` TEXT DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_quiz_responses_quiz` (`quiz_id`),
    KEY `idx_quiz_responses_email` (`email`),
    KEY `idx_quiz_responses_customer` (`customer_id`),
    KEY `idx_quiz_responses_status` (`status`),
    KEY `idx_quiz_responses_created` (`created_at`),
    CONSTRAINT `fk_quiz_responses_quiz` FOREIGN KEY (`quiz_id`) REFERENCES `quizzes` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_quiz_responses_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_quiz_responses_profile` FOREIGN KEY (`profile_id`) REFERENCES `quiz_profiles` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- PARTIE 7 : TABLES EMAILING
-- ============================================================================

-- Table: email_templates
CREATE TABLE `email_templates` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `brand_id` INT UNSIGNED NOT NULL,
    `slug` VARCHAR(100) NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `type` ENUM('transactional', 'marketing') NOT NULL DEFAULT 'transactional',
    `subject` VARCHAR(255) NOT NULL,
    `body_html` LONGTEXT NOT NULL,
    `body_text` TEXT DEFAULT NULL,
    `available_variables` JSON DEFAULT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_email_templates_slug_brand` (`slug`, `brand_id`),
    KEY `idx_email_templates_brand` (`brand_id`),
    KEY `idx_email_templates_type` (`type`),
    CONSTRAINT `fk_email_templates_brand` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: email_campaigns
CREATE TABLE `email_campaigns` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `brand_id` INT UNSIGNED NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `subject` VARCHAR(255) NOT NULL,
    `content` TEXT DEFAULT NULL,
    `segment` ENUM('all', 'customers', 'subscribers', 'prospects', 'inactive', 'tag') NOT NULL DEFAULT 'all',
    `segment_tag_id` INT UNSIGNED DEFAULT NULL,
    `status` ENUM('draft', 'scheduled', 'sending', 'sent') NOT NULL DEFAULT 'draft',
    `scheduled_at` DATETIME DEFAULT NULL,
    `sent_at` DATETIME DEFAULT NULL,
    `total_recipients` INT UNSIGNED NOT NULL DEFAULT 0,
    `total_sent` INT UNSIGNED NOT NULL DEFAULT 0,
    `total_opened` INT UNSIGNED NOT NULL DEFAULT 0,
    `total_clicked` INT UNSIGNED NOT NULL DEFAULT 0,
    `total_unsubscribed` INT UNSIGNED NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_email_campaigns_brand` (`brand_id`),
    KEY `idx_email_campaigns_status` (`status`),
    KEY `idx_email_campaigns_scheduled` (`scheduled_at`),
    CONSTRAINT `fk_email_campaigns_brand` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_email_campaigns_tag` FOREIGN KEY (`segment_tag_id`) REFERENCES `customer_tags` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: email_queue
CREATE TABLE `email_queue` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `brand_id` INT UNSIGNED NOT NULL,
    `campaign_id` INT UNSIGNED DEFAULT NULL,
    `template_id` INT UNSIGNED DEFAULT NULL,
    `customer_id` INT UNSIGNED DEFAULT NULL,
    `recipient_email` VARCHAR(255) NOT NULL,
    `recipient_name` VARCHAR(255) DEFAULT NULL,
    `subject` VARCHAR(255) NOT NULL,
    `body_html` LONGTEXT DEFAULT NULL,
    `body_text` TEXT DEFAULT NULL,
    `priority` TINYINT NOT NULL DEFAULT 5,
    `status` ENUM('pending', 'processing', 'sent', 'failed') NOT NULL DEFAULT 'pending',
    `attempts` TINYINT NOT NULL DEFAULT 0,
    `max_attempts` TINYINT NOT NULL DEFAULT 3,
    `error_message` TEXT DEFAULT NULL,
    `scheduled_at` DATETIME DEFAULT NULL,
    `sent_at` DATETIME DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_email_queue_brand` (`brand_id`),
    KEY `idx_email_queue_status` (`status`, `scheduled_at`),
    KEY `idx_email_queue_campaign` (`campaign_id`),
    KEY `idx_email_queue_priority` (`priority`, `created_at`),
    CONSTRAINT `fk_email_queue_brand` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_email_queue_campaign` FOREIGN KEY (`campaign_id`) REFERENCES `email_campaigns` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: email_logs
CREATE TABLE `email_logs` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `brand_id` INT UNSIGNED NOT NULL,
    `template_id` INT UNSIGNED DEFAULT NULL,
    `campaign_id` INT UNSIGNED DEFAULT NULL,
    `customer_id` INT UNSIGNED DEFAULT NULL,
    `recipient_email` VARCHAR(255) NOT NULL,
    `recipient_name` VARCHAR(255) DEFAULT NULL,
    `subject` VARCHAR(255) NOT NULL,
    `status` ENUM('pending', 'sent', 'failed', 'bounced', 'opened', 'clicked') NOT NULL DEFAULT 'pending',
    `sent_at` DATETIME DEFAULT NULL,
    `opened_at` DATETIME DEFAULT NULL,
    `clicked_at` DATETIME DEFAULT NULL,
    `error_message` TEXT DEFAULT NULL,
    `metadata` JSON DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_email_logs_brand` (`brand_id`),
    KEY `idx_email_logs_brand_status` (`brand_id`, `status`),
    KEY `idx_email_logs_brand_date` (`brand_id`, `created_at`),
    KEY `idx_email_logs_customer` (`customer_id`),
    KEY `idx_email_logs_campaign` (`campaign_id`),
    CONSTRAINT `fk_email_logs_brand` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_email_logs_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_email_logs_campaign` FOREIGN KEY (`campaign_id`) REFERENCES `email_campaigns` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- PARTIE 8 : TABLES STATISTIQUES & CRON
-- ============================================================================

-- Table: daily_stats
CREATE TABLE `daily_stats` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `brand_id` INT UNSIGNED NOT NULL,
    `stat_date` DATE NOT NULL,
    `visitors` INT UNSIGNED NOT NULL DEFAULT 0,
    `page_views` INT UNSIGNED NOT NULL DEFAULT 0,
    `new_customers` INT UNSIGNED NOT NULL DEFAULT 0,
    `new_subscribers` INT UNSIGNED NOT NULL DEFAULT 0,
    `orders_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `orders_revenue` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `emails_sent` INT UNSIGNED NOT NULL DEFAULT 0,
    `emails_opened` INT UNSIGNED NOT NULL DEFAULT 0,
    `emails_clicked` INT UNSIGNED NOT NULL DEFAULT 0,
    `quiz_started` INT UNSIGNED NOT NULL DEFAULT 0,
    `quiz_completed` INT UNSIGNED NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_daily_stats_brand_date` (`brand_id`, `stat_date`),
    KEY `idx_daily_stats_date` (`stat_date`),
    CONSTRAINT `fk_daily_stats_brand` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: cron_logs
CREATE TABLE `cron_logs` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `job_name` VARCHAR(100) NOT NULL,
    `status` ENUM('running', 'success', 'failed') NOT NULL,
    `started_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `finished_at` DATETIME DEFAULT NULL,
    `duration_seconds` INT UNSIGNED DEFAULT NULL,
    `items_processed` INT UNSIGNED DEFAULT NULL,
    `error_message` TEXT DEFAULT NULL,
    `details` JSON DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_cron_logs_job` (`job_name`),
    KEY `idx_cron_logs_status` (`status`),
    KEY `idx_cron_logs_started` (`started_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- PARTIE 9 : TABLES EBOOK BUILDER (MODULE URI)
-- ============================================================================

-- Table: ebook_templates
CREATE TABLE `ebook_templates` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `brand_id` INT UNSIGNED NOT NULL DEFAULT 1,
    `name` VARCHAR(100) NOT NULL DEFAULT 'Template Principal',
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `kdp_format` JSON NOT NULL,
    `typography_styles` JSON NOT NULL,
    `image_frame` JSON NOT NULL,
    `page_types` JSON NOT NULL,
    `headers_footers` JSON NOT NULL,
    `claude_writing_rules` JSON NOT NULL,
    `claude_api_config` JSON NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_ebook_templates_brand` (`brand_id`),
    KEY `idx_ebook_templates_active` (`is_active`),
    CONSTRAINT `fk_ebook_templates_brand` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: ebook_projects
CREATE TABLE `ebook_projects` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `brand_id` INT UNSIGNED NOT NULL DEFAULT 1,
    `template_id` INT UNSIGNED DEFAULT NULL,
    `title` VARCHAR(255) NOT NULL,
    `subtitle` VARCHAR(255) DEFAULT NULL,
    `slug` VARCHAR(255) NOT NULL,
    `description` TEXT DEFAULT NULL,
    `creation_mode` ENUM('title_only', 'title_brief', 'import_pdf') NOT NULL DEFAULT 'title_only',
    `brief` TEXT DEFAULT NULL,
    `target_chapters` INT UNSIGNED DEFAULT NULL,
    `target_pages` INT UNSIGNED DEFAULT NULL,
    `include_images` TINYINT(1) NOT NULL DEFAULT 1,
    `include_toc` TINYINT(1) NOT NULL DEFAULT 1,
    `status` ENUM('draft', 'generating', 'reviewing', 'ready', 'exported') NOT NULL DEFAULT 'draft',
    `generation_progress` INT UNSIGNED NOT NULL DEFAULT 0,
    `generation_error` TEXT DEFAULT NULL,
    `total_pages` INT UNSIGNED NOT NULL DEFAULT 0,
    `validated_pages` INT UNSIGNED NOT NULL DEFAULT 0,
    `total_words` INT UNSIGNED NOT NULL DEFAULT 0,
    `total_images` INT UNSIGNED NOT NULL DEFAULT 0,
    `cover_front` VARCHAR(500) DEFAULT NULL,
    `cover_back` VARCHAR(500) DEFAULT NULL,
    `author` VARCHAR(255) DEFAULT NULL,
    `isbn` VARCHAR(20) DEFAULT NULL,
    `publisher` VARCHAR(255) DEFAULT NULL,
    `last_export_at` DATETIME DEFAULT NULL,
    `export_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `created_by` INT UNSIGNED DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_ebook_projects_brand` (`brand_id`),
    KEY `idx_ebook_projects_status` (`status`),
    UNIQUE KEY `uk_ebook_projects_slug` (`brand_id`, `slug`),
    CONSTRAINT `fk_ebook_projects_brand` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_ebook_projects_template` FOREIGN KEY (`template_id`) REFERENCES `ebook_templates` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_ebook_projects_creator` FOREIGN KEY (`created_by`) REFERENCES `admin_users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: ebook_chapters
CREATE TABLE `ebook_chapters` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `project_id` INT UNSIGNED NOT NULL,
    `chapter_number` INT UNSIGNED NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `subtitle` VARCHAR(255) DEFAULT NULL,
    `description` TEXT DEFAULT NULL,
    `start_page` INT UNSIGNED DEFAULT NULL,
    `end_page` INT UNSIGNED DEFAULT NULL,
    `status` ENUM('pending', 'generating', 'generated', 'validated') NOT NULL DEFAULT 'pending',
    `sort_order` INT UNSIGNED NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_ebook_chapters_project` (`project_id`),
    KEY `idx_ebook_chapters_order` (`project_id`, `sort_order`),
    CONSTRAINT `fk_ebook_chapters_project` FOREIGN KEY (`project_id`) REFERENCES `ebook_projects` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: ebook_pages
CREATE TABLE `ebook_pages` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `project_id` INT UNSIGNED NOT NULL,
    `chapter_id` INT UNSIGNED DEFAULT NULL,
    `page_number` INT UNSIGNED NOT NULL,
    `page_type` ENUM('title', 'copyright', 'dedication', 'toc', 'chapter_start', 'content', 'chapter_end', 'blank', 'back_matter') NOT NULL DEFAULT 'content',
    `content_original` LONGTEXT DEFAULT NULL,
    `content_current` LONGTEXT DEFAULT NULL,
    `content_html` LONGTEXT DEFAULT NULL,
    `has_images` TINYINT(1) NOT NULL DEFAULT 0,
    `word_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `status` ENUM('generated', 'modified', 'validated') NOT NULL DEFAULT 'generated',
    `validated_at` DATETIME DEFAULT NULL,
    `validated_by` INT UNSIGNED DEFAULT NULL,
    `is_odd_page` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_ebook_pages_project` (`project_id`),
    KEY `idx_ebook_pages_chapter` (`chapter_id`),
    KEY `idx_ebook_pages_number` (`project_id`, `page_number`),
    KEY `idx_ebook_pages_status` (`status`),
    CONSTRAINT `fk_ebook_pages_project` FOREIGN KEY (`project_id`) REFERENCES `ebook_projects` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_ebook_pages_chapter` FOREIGN KEY (`chapter_id`) REFERENCES `ebook_chapters` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_ebook_pages_validator` FOREIGN KEY (`validated_by`) REFERENCES `admin_users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: ebook_page_history
CREATE TABLE `ebook_page_history` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `page_id` INT UNSIGNED NOT NULL,
    `version_number` INT UNSIGNED NOT NULL,
    `content_before` LONGTEXT DEFAULT NULL,
    `content_after` LONGTEXT NOT NULL,
    `change_type` ENUM('generation', 'manual_edit', 'restoration', 'ai_regeneration') NOT NULL DEFAULT 'manual_edit',
    `change_summary` VARCHAR(500) DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `created_by` INT UNSIGNED DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_ebook_page_history_page` (`page_id`),
    KEY `idx_ebook_page_history_version` (`page_id`, `version_number`),
    CONSTRAINT `fk_ebook_page_history_page` FOREIGN KEY (`page_id`) REFERENCES `ebook_pages` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_ebook_page_history_creator` FOREIGN KEY (`created_by`) REFERENCES `admin_users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: ebook_images
CREATE TABLE `ebook_images` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `brand_id` INT UNSIGNED NOT NULL DEFAULT 1,
    `filename` VARCHAR(255) NOT NULL,
    `original_filename` VARCHAR(255) NOT NULL,
    `file_path` VARCHAR(500) NOT NULL,
    `file_size` INT UNSIGNED NOT NULL,
    `mime_type` VARCHAR(50) NOT NULL,
    `width` INT UNSIGNED NOT NULL,
    `height` INT UNSIGNED NOT NULL,
    `dpi` INT UNSIGNED DEFAULT NULL,
    `title` VARCHAR(255) DEFAULT NULL,
    `alt_text` VARCHAR(255) DEFAULT NULL,
    `description` TEXT DEFAULT NULL,
    `category` VARCHAR(100) DEFAULT NULL,
    `tags` JSON DEFAULT NULL,
    `is_print_quality` TINYINT(1) NOT NULL DEFAULT 0,
    `quality_warning` VARCHAR(255) DEFAULT NULL,
    `usage_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `uploaded_by` INT UNSIGNED DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_ebook_images_brand` (`brand_id`),
    KEY `idx_ebook_images_category` (`category`),
    KEY `idx_ebook_images_quality` (`is_print_quality`),
    FULLTEXT INDEX `ft_ebook_images_search` (`title`, `alt_text`, `description`),
    CONSTRAINT `fk_ebook_images_brand` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_ebook_images_uploader` FOREIGN KEY (`uploaded_by`) REFERENCES `admin_users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- PARTIE 10 : TABLES E-COMMERCE (MODULE LEI)
-- ============================================================================

-- Table: product_categories
CREATE TABLE `product_categories` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `brand_id` INT UNSIGNED NOT NULL,
    `parent_id` INT UNSIGNED DEFAULT NULL,
    `name` VARCHAR(100) NOT NULL,
    `slug` VARCHAR(120) NOT NULL,
    `description` TEXT DEFAULT NULL,
    `image` VARCHAR(255) DEFAULT NULL,
    `display_order` INT NOT NULL DEFAULT 0,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `idx_category_slug_brand` (`brand_id`, `slug`),
    KEY `idx_category_parent` (`parent_id`),
    KEY `idx_category_active` (`brand_id`, `is_active`),
    CONSTRAINT `fk_category_brand` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_category_parent` FOREIGN KEY (`parent_id`) REFERENCES `product_categories` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: products
CREATE TABLE `products` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `brand_id` INT UNSIGNED NOT NULL,
    `sku` VARCHAR(50) NOT NULL,
    `name` VARCHAR(200) NOT NULL,
    `slug` VARCHAR(220) NOT NULL,
    `description_short` TEXT DEFAULT NULL,
    `description_long` LONGTEXT DEFAULT NULL,
    `category_id` INT UNSIGNED DEFAULT NULL,
    `base_price` DECIMAL(10,2) NOT NULL,
    `compare_price` DECIMAL(10,2) DEFAULT NULL,
    `currency` CHAR(3) NOT NULL DEFAULT 'EUR',
    `tax_rate` DECIMAL(4,2) NOT NULL DEFAULT 20.00,
    `weight` INT UNSIGNED NOT NULL DEFAULT 0,
    `meta_title` VARCHAR(70) DEFAULT NULL,
    `meta_description` VARCHAR(160) DEFAULT NULL,
    `status` ENUM('draft', 'active', 'archived') NOT NULL DEFAULT 'draft',
    `has_variants` TINYINT(1) NOT NULL DEFAULT 0,
    `is_featured` TINYINT(1) NOT NULL DEFAULT 0,
    `display_order` INT NOT NULL DEFAULT 0,
    `total_stock` INT NOT NULL DEFAULT 0,
    `total_sales` INT UNSIGNED NOT NULL DEFAULT 0,
    `total_revenue` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `idx_product_sku_brand` (`brand_id`, `sku`),
    UNIQUE KEY `idx_product_slug_brand` (`brand_id`, `slug`),
    KEY `idx_product_status` (`brand_id`, `status`),
    KEY `idx_product_category` (`category_id`),
    KEY `idx_product_featured` (`brand_id`, `is_featured`, `status`),
    CONSTRAINT `fk_product_brand` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_product_category` FOREIGN KEY (`category_id`) REFERENCES `product_categories` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: product_variants
CREATE TABLE `product_variants` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `product_id` INT UNSIGNED NOT NULL,
    `sku_suffix` VARCHAR(30) NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `attributes` JSON NOT NULL,
    `price_adjustment` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `stock_quantity` INT NOT NULL DEFAULT 0,
    `low_stock_threshold` INT NOT NULL DEFAULT 5,
    `image_id` INT UNSIGNED DEFAULT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `idx_variant_sku` (`product_id`, `sku_suffix`),
    KEY `idx_variant_stock` (`stock_quantity`, `low_stock_threshold`),
    CONSTRAINT `fk_variant_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: product_images
CREATE TABLE `product_images` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `product_id` INT UNSIGNED NOT NULL,
    `variant_id` INT UNSIGNED DEFAULT NULL,
    `filename` VARCHAR(255) NOT NULL,
    `alt_text` VARCHAR(200) DEFAULT NULL,
    `position` INT NOT NULL DEFAULT 0,
    `is_primary` TINYINT(1) NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_image_product` (`product_id`, `position`),
    KEY `idx_image_variant` (`variant_id`),
    CONSTRAINT `fk_image_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_image_variant` FOREIGN KEY (`variant_id`) REFERENCES `product_variants` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: shipping_methods
CREATE TABLE `shipping_methods` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `brand_id` INT UNSIGNED NOT NULL,
    `code` VARCHAR(50) NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `description` TEXT DEFAULT NULL,
    `price` DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    `free_threshold` DECIMAL(8,2) DEFAULT NULL,
    `delivery_estimate` VARCHAR(50) DEFAULT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `display_order` INT NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `idx_shipping_code_brand` (`brand_id`, `code`),
    KEY `idx_shipping_active` (`brand_id`, `is_active`),
    CONSTRAINT `fk_shipping_brand` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: shipments
CREATE TABLE `shipments` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `order_id` INT UNSIGNED NOT NULL,
    `delivery_type` ENUM('point_relais', 'domicile') NOT NULL,
    `mondial_relay_expedition_num` VARCHAR(30) DEFAULT NULL,
    `tracking_number` VARCHAR(50) DEFAULT NULL,
    `label_url` VARCHAR(500) DEFAULT NULL,
    `pickup_point_id` VARCHAR(20) DEFAULT NULL,
    `pickup_point_name` VARCHAR(100) DEFAULT NULL,
    `pickup_point_address` JSON DEFAULT NULL,
    `recipient_address` JSON DEFAULT NULL,
    `weight` INT UNSIGNED NOT NULL DEFAULT 0,
    `status` ENUM('pending', 'label_created', 'shipped', 'in_transit', 'delivered', 'returned') NOT NULL DEFAULT 'pending',
    `shipped_at` DATETIME DEFAULT NULL,
    `delivered_at` DATETIME DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_shipment_order` (`order_id`),
    KEY `idx_shipment_status` (`status`),
    KEY `idx_shipment_tracking` (`tracking_number`),
    CONSTRAINT `fk_shipment_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- PARTIE 11 : TABLES DIVERSES
-- ============================================================================

-- Table: testimonials
CREATE TABLE `testimonials` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `brand_id` INT UNSIGNED NOT NULL,
    `customer_id` INT UNSIGNED DEFAULT NULL,
    `ebook_id` INT UNSIGNED DEFAULT NULL,
    `product_id` INT UNSIGNED DEFAULT NULL,
    `author_name` VARCHAR(100) DEFAULT NULL,
    `rating` TINYINT UNSIGNED DEFAULT NULL,
    `comment` TEXT DEFAULT NULL,
    `photo` VARCHAR(255) DEFAULT NULL,
    `admin_response` TEXT DEFAULT NULL,
    `status` ENUM('pending', 'approved', 'rejected') NOT NULL DEFAULT 'pending',
    `is_featured` TINYINT(1) NOT NULL DEFAULT 0,
    `approved_at` DATETIME DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_testimonials_brand` (`brand_id`),
    KEY `idx_testimonials_status` (`status`),
    KEY `idx_testimonials_featured` (`is_featured`),
    CONSTRAINT `fk_testimonials_brand` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_testimonials_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_testimonials_ebook` FOREIGN KEY (`ebook_id`) REFERENCES `ebooks` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_testimonials_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: calendar_events
CREATE TABLE `calendar_events` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `brand_id` INT UNSIGNED DEFAULT NULL,
    `title` VARCHAR(255) NOT NULL,
    `description` TEXT DEFAULT NULL,
    `event_type` ENUM('ebook', 'campaign', 'promo', 'email', 'other') NOT NULL DEFAULT 'other',
    `related_id` INT UNSIGNED DEFAULT NULL,
    `start_date` DATETIME NOT NULL,
    `end_date` DATETIME DEFAULT NULL,
    `status` ENUM('idea', 'in_progress', 'ready', 'published', 'done') NOT NULL DEFAULT 'idea',
    `checklist` JSON DEFAULT NULL,
    `notes` TEXT DEFAULT NULL,
    `color` VARCHAR(7) DEFAULT '#6060b9',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_calendar_events_brand` (`brand_id`),
    KEY `idx_calendar_events_dates` (`start_date`, `end_date`),
    KEY `idx_calendar_events_type` (`event_type`),
    CONSTRAINT `fk_calendar_events_brand` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: ae_declarations (Auto-entrepreneur)
CREATE TABLE `ae_declarations` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `brand_id` INT UNSIGNED DEFAULT NULL,
    `period_type` ENUM('monthly', 'quarterly') NOT NULL DEFAULT 'monthly',
    `period_start` DATE NOT NULL,
    `period_end` DATE NOT NULL,
    `revenue` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `contributions` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `status` ENUM('pending', 'declared') NOT NULL DEFAULT 'pending',
    `declared_at` DATETIME DEFAULT NULL,
    `notes` TEXT DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_ae_declarations_brand` (`brand_id`),
    KEY `idx_ae_declarations_period` (`period_start`, `period_end`),
    KEY `idx_ae_declarations_status` (`status`),
    CONSTRAINT `fk_ae_declarations_brand` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: backups
CREATE TABLE `backups` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `filename` VARCHAR(255) NOT NULL,
    `filesize` BIGINT UNSIGNED DEFAULT NULL,
    `type` ENUM('manual', 'auto') NOT NULL DEFAULT 'manual',
    `status` ENUM('completed', 'failed') NOT NULL DEFAULT 'completed',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_backups_type` (`type`),
    KEY `idx_backups_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Paramètres globaux
INSERT INTO `settings` (`brand_id`, `setting_key`, `setting_value`, `setting_type`, `category`, `description`, `is_sensitive`) VALUES
(NULL, 'app_timezone', 'Europe/Paris', 'string', 'general', 'Fuseau horaire', 0),
(NULL, 'date_format', 'd/m/Y', 'string', 'general', 'Format de date', 0),
(NULL, 'currency_default', 'EUR', 'string', 'general', 'Devise par défaut', 0),
(NULL, 'session_lifetime', '3600', 'int', 'security', 'Durée de session (secondes)', 0),
(NULL, 'max_login_attempts', '5', 'int', 'security', 'Tentatives max avant verrouillage', 0),
(NULL, 'lockout_duration', '900', 'int', 'security', 'Durée de verrouillage (secondes)', 0),
(NULL, 'backup_auto_enabled', '0', 'bool', 'backup', 'Sauvegardes automatiques', 0),
(NULL, 'backup_frequency', 'daily', 'string', 'backup', 'Fréquence des sauvegardes', 0),
(NULL, 'backup_retention', '7', 'int', 'backup', 'Nombre de sauvegardes conservées', 0);

-- Paramètres par marque (URI)
INSERT INTO `settings` (`brand_id`, `setting_key`, `setting_value`, `setting_type`, `category`, `description`) VALUES
(1, 'company_name', 'Un Regard Intérieur', 'string', 'company', 'Nom de l''entreprise'),
(1, 'company_email', 'contact@unregardinterieur.fr', 'string', 'company', 'Email de contact'),
(1, 'subscription_price', '2.99', 'float', 'payment', 'Prix abonnement mensuel'),
(1, 'rgpd_retention_months', '36', 'int', 'rgpd', 'Conservation données (mois)');

-- Modules globaux
INSERT INTO `modules` (`brand_id`, `name`, `display_name`, `icon`, `route`, `position`, `is_active`) VALUES
(NULL, 'dashboard', 'Tableau de bord', 'bi-speedometer2', '/pages/dashboard/', 1, 1),
(NULL, 'settings', 'Paramètres', 'bi-gear', '/pages/settings/', 99, 1);

-- Modules URI
INSERT INTO `modules` (`brand_id`, `name`, `display_name`, `icon`, `route`, `position`, `is_active`) VALUES
(1, 'ebooks', 'Ebooks', 'bi-book', '/pages/uri/ebooks/', 2, 1),
(1, 'quizzes', 'Quiz / Leadmagnets', 'bi-question-circle', '/pages/uri/quizzes/', 3, 1),
(1, 'ebook_builder', 'Suivi Ebooks', 'bi-book-half', '/pages/uri/ebook-builder/', 4, 1),
(1, 'orders_uri', 'Commandes', 'bi-receipt', '/pages/uri/orders/', 5, 1),
(1, 'customers_uri', 'Clients', 'bi-people', '/pages/uri/customers/', 6, 1),
(1, 'promos_uri', 'Codes promo', 'bi-tag', '/pages/uri/promos/', 7, 1),
(1, 'emailing_uri', 'Emailing', 'bi-envelope', '/pages/uri/emailing/', 8, 1),
(1, 'analytics_uri', 'Analytics', 'bi-bar-chart-line', '/pages/uri/analytics/', 9, 1);

-- Modules LEI
INSERT INTO `modules` (`brand_id`, `name`, `display_name`, `icon`, `route`, `position`, `is_active`) VALUES
(2, 'products', 'Produits', 'bi-box-seam', '/pages/lei/products/', 2, 1),
(2, 'stock', 'Stock', 'bi-boxes', '/pages/lei/stock/', 3, 1),
(2, 'orders_lei', 'Commandes', 'bi-receipt', '/pages/lei/orders/', 4, 1),
(2, 'shipments', 'Expéditions', 'bi-truck', '/pages/lei/shipments/', 5, 1),
(2, 'customers_lei', 'Clients', 'bi-people', '/pages/lei/customers/', 6, 1),
(2, 'emailing_lei', 'Emailing', 'bi-envelope', '/pages/lei/emailing/', 7, 1);

-- Modules Q4NA
INSERT INTO `modules` (`brand_id`, `name`, `display_name`, `icon`, `route`, `position`, `is_active`) VALUES
(3, 'users_q4na', 'Utilisateurs', 'bi-person-badge', '/pages/q4na/users/', 2, 1),
(3, 'subscriptions_q4na', 'Abonnements', 'bi-credit-card-2-front', '/pages/q4na/subscriptions/', 3, 1),
(3, 'astro_content', 'Contenu Astro', 'bi-stars', '/pages/q4na/astro-content/', 4, 1),
(3, 'ai_config', 'Configuration IA', 'bi-robot', '/pages/q4na/ai-config/', 5, 1),
(3, 'emailing_q4na', 'Emailing', 'bi-envelope', '/pages/q4na/emailing/', 6, 1);

-- Modules SSO
INSERT INTO `modules` (`brand_id`, `name`, `display_name`, `icon`, `route`, `position`, `is_active`) VALUES
(4, 'projects_sso', 'Projets', 'bi-palette', '/pages/sso/projects/', 2, 1),
(4, 'customers_sso', 'Clients', 'bi-people', '/pages/sso/customers/', 3, 1),
(4, 'emailing_sso', 'Emailing', 'bi-envelope', '/pages/sso/emailing/', 4, 1);

-- Modules CLI
INSERT INTO `modules` (`brand_id`, `name`, `display_name`, `icon`, `route`, `position`, `is_active`) VALUES
(5, 'events_cli', 'Événements', 'bi-calendar-event', '/pages/cli/events/', 2, 1),
(5, 'members_cli', 'Membres', 'bi-people', '/pages/cli/members/', 3, 1),
(5, 'emailing_cli', 'Emailing', 'bi-envelope', '/pages/cli/emailing/', 4, 1);

-- Méthodes de livraison LEI
INSERT INTO `shipping_methods` (`brand_id`, `code`, `name`, `description`, `price`, `free_threshold`, `delivery_estimate`, `is_active`, `display_order`) VALUES
(2, 'mondial_relay_point', 'Point Relais Mondial Relay', 'Livraison dans le Point Relais de votre choix', 4.90, 60.00, '3-5 jours ouvrés', 1, 1),
(2, 'mondial_relay_home', 'Livraison à domicile', 'Livraison directement chez vous', 8.90, 80.00, '3-5 jours ouvrés', 1, 2);


-- ============================================================================
-- FIN DU SCRIPT - BASE DE DONNÉES UNIFIÉE
-- ============================================================================
