-- ==============================================================================
-- CLK (clk.ma) & Seller AI - Schéma Consolidé Unifié (MySQL 8.0 / MariaDB 10.4+)
-- Base de données commune Production Ready pour OVH / phpMyAdmin
-- ==============================================================================


-- ==============================================================================
-- SECTION 1 : COEUR E-COMMERCE CLK (clk.ma)
-- ==============================================================================

-- 1. Table des Marchands (Merchants)
CREATE TABLE merchants (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    slug VARCHAR(150) NOT NULL UNIQUE,
    api_key_hash CHAR(64) NOT NULL,
    segment ENUM('b2c','b2b') NOT NULL DEFAULT 'b2c',
    plan VARCHAR(50) NOT NULL DEFAULT 'decouverte',
    currency CHAR(3) NOT NULL DEFAULT 'MAD',
    vat_rate DECIMAL(5,2) NOT NULL DEFAULT 20.00,
    b2b_credit_limit INT NULL COMMENT 'centimes, NULL = unlimited',
    b2b_payment_days INT NULL COMMENT '30/60/90 net',
    b2b_discount_rate DECIMAL(5,2) NULL DEFAULT 0.00 COMMENT 'percent off',
    b2b_min_order INT NULL COMMENT 'centimes',
    settings_json JSON NULL,
    webhook_url VARCHAR(255) NULL,
    status ENUM('active','suspended') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_merchants_slug (slug),
    INDEX idx_merchants_api_key (api_key_hash, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Table des Entrepôts (Warehouses)
CREATE TABLE warehouses (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    merchant_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(150) NOT NULL,
    city VARCHAR(100) NULL,
    is_default BOOLEAN NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE,
    INDEX idx_warehouses_merchant (merchant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Table des Produits (Products)
CREATE TABLE products (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    merchant_id BIGINT UNSIGNED NOT NULL,
    warehouse_id BIGINT UNSIGNED NULL,
    external_sku VARCHAR(100) NOT NULL,
    name VARCHAR(255) NOT NULL,
    price INT NOT NULL COMMENT 'prix en centimes (ex: 15000 = 150.00 MAD)',
    cost_price INT NULL COMMENT 'coût de revient en centimes (interne)',
    image_url VARCHAR(500) NULL,
    images JSON NULL,
    is_declarative BOOLEAN NOT NULL DEFAULT 0,
    status ENUM('active','archived') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_merchant_sku (merchant_id, external_sku),
    FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE,
    FOREIGN KEY (warehouse_id) REFERENCES warehouses(id) ON DELETE SET NULL,
    INDEX idx_products_lookup (merchant_id, external_sku, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Grilles Tarifaires B2B (Price Lists)
CREATE TABLE price_list_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    merchant_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    price INT NOT NULL COMMENT 'centimes',
    min_quantity INT UNSIGNED NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_b2b_price (merchant_id, product_id, min_quantity),
    FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Table des Clients (Customers)
CREATE TABLE customers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    merchant_id BIGINT UNSIGNED NOT NULL,
    phone VARCHAR(30) NOT NULL,
    email VARCHAR(150) NULL,
    name VARCHAR(150) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_merchant_phone (merchant_id, phone),
    FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. Table des Paniers (Carts)
CREATE TABLE carts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    token CHAR(36) NOT NULL UNIQUE,
    merchant_id BIGINT UNSIGNED NOT NULL,
    status ENUM('active','converted','abandoned','expired') NOT NULL DEFAULT 'active',
    source VARCHAR(50) NOT NULL DEFAULT 'widget',
    currency CHAR(3) NOT NULL DEFAULT 'MAD',
    subtotal INT NOT NULL DEFAULT 0,
    shipping_total INT NOT NULL DEFAULT 0,
    discount_total INT NOT NULL DEFAULT 0,
    grand_total INT NOT NULL DEFAULT 0,
    applied_promo_code VARCHAR(50) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME NOT NULL,
    abandoned_at DATETIME NULL,
    recovery_sent_at DATETIME NULL,
    FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE,
    INDEX idx_carts_token (token, status),
    INDEX idx_carts_expiry (status, expires_at),
    INDEX idx_carts_abandoned (merchant_id, status, abandoned_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. Articles de Panier (Cart Items)
CREATE TABLE cart_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cart_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NULL,
    external_sku VARCHAR(100) NOT NULL,
    name VARCHAR(255) NOT NULL,
    unit_price INT NOT NULL COMMENT 'centimes',
    quantity INT UNSIGNED NOT NULL DEFAULT 1,
    line_total INT NOT NULL COMMENT 'centimes',
    options_json JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (cart_id) REFERENCES carts(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. Adresses de Livraison
CREATE TABLE addresses (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cart_id BIGINT UNSIGNED NOT NULL,
    full_name VARCHAR(150) NOT NULL,
    phone VARCHAR(30) NOT NULL,
    city VARCHAR(100) NOT NULL,
    address_line TEXT NOT NULL,
    postal_code VARCHAR(20) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (cart_id) REFERENCES carts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 9. Modes de Livraison (Shipping Methods)
CREATE TABLE shipping_methods (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    merchant_id BIGINT UNSIGNED NOT NULL,
    carrier VARCHAR(100) NOT NULL,
    city VARCHAR(100) NOT NULL,
    price INT NOT NULL DEFAULT 0 COMMENT 'centimes',
    eta_days INT NOT NULL DEFAULT 2,
    active BOOLEAN NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE,
    INDEX idx_shipping_lookup (merchant_id, city, active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 10. Devis B2B (Quotes)
CREATE TABLE quotes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_id CHAR(36) NOT NULL UNIQUE,
    merchant_id BIGINT UNSIGNED NOT NULL,
    customer_id BIGINT UNSIGNED NOT NULL,
    status ENUM('draft','sent','accepted','expired','converted','cancelled') NOT NULL DEFAULT 'draft',
    subtotal INT NOT NULL DEFAULT 0,
    discount_total INT NOT NULL DEFAULT 0,
    grand_total INT NOT NULL DEFAULT 0,
    currency CHAR(3) NOT NULL DEFAULT 'MAD',
    valid_until DATE NULL,
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    INDEX idx_quotes_merchant (merchant_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE quote_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    quote_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NULL,
    external_sku VARCHAR(100) NULL,
    name VARCHAR(255) NOT NULL,
    unit_price INT NOT NULL COMMENT 'centimes',
    quantity INT UNSIGNED NOT NULL DEFAULT 1,
    line_total INT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (quote_id) REFERENCES quotes(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 11. Factures B2B (Invoices)
CREATE TABLE invoices (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_id CHAR(36) NOT NULL UNIQUE,
    merchant_id BIGINT UNSIGNED NOT NULL,
    order_id BIGINT UNSIGNED NOT NULL,
    status ENUM('pending','paid','overdue','cancelled') NOT NULL DEFAULT 'pending',
    amount_due INT NOT NULL COMMENT 'centimes',
    amount_paid INT NOT NULL DEFAULT 0,
    currency CHAR(3) NOT NULL DEFAULT 'MAD',
    due_date DATE NOT NULL,
    paid_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE,
    INDEX idx_invoices_merchant (merchant_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 12. Table des Commandes (Orders)
CREATE TABLE orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_id CHAR(36) NOT NULL UNIQUE,
    cart_id BIGINT UNSIGNED NOT NULL,
    quote_id BIGINT UNSIGNED NULL,
    invoice_id BIGINT UNSIGNED NULL,
    merchant_id BIGINT UNSIGNED NOT NULL,
    customer_id BIGINT UNSIGNED NOT NULL,
    status ENUM('pending','processing','paid','delivered','cancelled','refunded') NOT NULL DEFAULT 'pending',
    paid_at DATETIME NULL,
    address_id BIGINT UNSIGNED NOT NULL,
    shipping_method_id BIGINT UNSIGNED NOT NULL,
    payment_method VARCHAR(30) NOT NULL DEFAULT 'cmi',
    payment_terms INT NULL COMMENT 'net days',
    subtotal INT NOT NULL DEFAULT 0,
    discount_total INT NOT NULL DEFAULT 0,
    shipping_total INT NOT NULL DEFAULT 0,
    grand_total INT NOT NULL DEFAULT 0,
    currency CHAR(3) NOT NULL DEFAULT 'MAD',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    FOREIGN KEY (cart_id) REFERENCES carts(id) ON DELETE CASCADE,
    FOREIGN KEY (address_id) REFERENCES addresses(id) ON DELETE CASCADE,
    FOREIGN KEY (quote_id) REFERENCES quotes(id) ON DELETE SET NULL,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE SET NULL,
    INDEX idx_orders_merchant_status (merchant_id, status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 13. Transactions de Paiement (Payment Transactions)
CREATE TABLE payment_transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    psp ENUM('cmi','cod','manual') NOT NULL DEFAULT 'cmi',
    psp_reference VARCHAR(100) NOT NULL,
    amount INT NOT NULL COMMENT 'centimes',
    status ENUM('pending','succeeded','failed','refunded') NOT NULL DEFAULT 'pending',
    raw_payload JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 14. Mouvements de Stock (Stock Movements)
CREATE TABLE stock_movements (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    merchant_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    warehouse_id BIGINT UNSIGNED NULL,
    quantity INT NOT NULL COMMENT 'negatif = sortie/vente, positif = entree/retour',
    movement VARCHAR(30) NOT NULL COMMENT 'sale, return, adjustment, initial',
    reference VARCHAR(100) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (warehouse_id) REFERENCES warehouses(id) ON DELETE SET NULL,
    INDEX idx_stock_movements_merchant (merchant_id, product_id),
    INDEX idx_stock_movements_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 15. Métadonnées & Clics des Liens Partagés (Share Links)
CREATE TABLE share_link_meta (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    token_hash CHAR(64) NOT NULL UNIQUE,
    merchant_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NULL,
    external_sku VARCHAR(100) NULL,
    payload_json JSON NOT NULL,
    webhook_url VARCHAR(500) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME NOT NULL,
    FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL,
    INDEX idx_merchant_expires (merchant_id, expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE share_link_clicks (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    token_hash CHAR(64) NOT NULL,
    merchant_id BIGINT UNSIGNED NOT NULL,
    referrer VARCHAR(500) NULL,
    user_agent VARCHAR(500) NULL,
    ip_hash CHAR(64) NULL,
    utm_source VARCHAR(100) NULL,
    utm_medium VARCHAR(100) NULL,
    utm_campaign VARCHAR(100) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE,
    INDEX idx_clicks_token (token_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 16. Pages Produits Hébergées (Product Pages)
CREATE TABLE product_pages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    merchant_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    slug VARCHAR(200) NOT NULL UNIQUE,
    theme VARCHAR(50) NOT NULL DEFAULT 'default',
    template_config JSON NOT NULL,
    views_count INT UNSIGNED NOT NULL DEFAULT 0,
    shares_count INT UNSIGNED NOT NULL DEFAULT 0,
    orders_count INT UNSIGNED NOT NULL DEFAULT 0,
    status ENUM('active','archived') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    INDEX idx_merchant_pages (merchant_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 17. Webhooks Outbox & Notifications CLK
CREATE TABLE webhooks_outbox (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    merchant_id BIGINT UNSIGNED NOT NULL,
    event_type VARCHAR(100) NOT NULL,
    payload JSON NOT NULL,
    status ENUM('pending','processing','delivered','failed') NOT NULL DEFAULT 'pending',
    attempts INT UNSIGNED NOT NULL DEFAULT 0,
    last_error TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    processed_at DATETIME NULL,
    FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE,
    INDEX idx_webhooks_status (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE notifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    merchant_id BIGINT UNSIGNED NOT NULL,
    cart_id BIGINT UNSIGNED NULL,
    customer_phone VARCHAR(30) NULL,
    channel ENUM('whatsapp','email') NOT NULL DEFAULT 'whatsapp',
    event_type VARCHAR(100) NOT NULL,
    content_json JSON NOT NULL,
    status ENUM('pending','sent','failed','cancelled') NOT NULL DEFAULT 'pending',
    sent_at DATETIME NULL,
    webhook_outbox_id BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE,
    FOREIGN KEY (webhook_outbox_id) REFERENCES webhooks_outbox(id) ON DELETE SET NULL,
    INDEX idx_notifications_status (merchant_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE cart_recovery_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cart_id BIGINT UNSIGNED NOT NULL,
    merchant_id BIGINT UNSIGNED NOT NULL,
    notification_id BIGINT UNSIGNED NULL,
    status ENUM('sent','opened','converted','expired') NOT NULL DEFAULT 'sent',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (cart_id) REFERENCES carts(id) ON DELETE CASCADE,
    FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE,
    INDEX idx_recovery_cart (cart_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 18. Clés d'Idempotence (Idempotency Keys)
CREATE TABLE idempotency_keys (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    idempotency_key VARCHAR(100) NOT NULL UNIQUE,
    merchant_id BIGINT UNSIGNED NOT NULL,
    response_code INT NOT NULL,
    response_body JSON NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- SECTION 2 : MODULES SELLER AI (Préfixe seller_*)
-- ==============================================================================

-- 19. Articles IA & SEO Enrichis (Seller Articles)
CREATE TABLE seller_articles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    merchant_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NULL COMMENT 'Lien direct vers products(id) CLK',
    sku VARCHAR(100) NOT NULL,
    nom VARCHAR(255) NOT NULL,
    categorie VARCHAR(100) NULL,
    prix DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Prix en MAD',
    stock INT NOT NULL DEFAULT 0,
    description TEXT NULL,
    image_url VARCHAR(500) NULL,
    images JSON NULL,
    slug VARCHAR(255) NULL,
    meta_title VARCHAR(255) NULL,
    meta_description VARCHAR(500) NULL,
    schema_json JSON NULL COMMENT 'schema.org/Product JSON-LD',
    seo_score TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Score SEO 0-100',
    synced_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_seller_articles_merchant_sku (merchant_id, sku),
    FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    INDEX idx_seller_articles_cat (merchant_id, categorie)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 20. Historique & Agrégats Ventes IA (Seller Ventes)
CREATE TABLE seller_ventes_historique (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    merchant_id BIGINT UNSIGNED NOT NULL,
    article_id BIGINT UNSIGNED NULL,
    order_id BIGINT UNSIGNED NULL COMMENT 'Lien direct vers orders(id) CLK',
    order_public_id CHAR(36) NULL,
    quantite INT NOT NULL DEFAULT 1,
    prix_unitaire DECIMAL(10,2) NOT NULL,
    total_ligne DECIMAL(10,2) NOT NULL,
    date_vente DATE NOT NULL,
    statut VARCHAR(50) NOT NULL DEFAULT 'paid',
    source VARCHAR(50) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE,
    FOREIGN KEY (article_id) REFERENCES seller_articles(id) ON DELETE SET NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    INDEX idx_seller_ventes_date (merchant_id, date_vente)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 21. Contenu Marketing & Posts Générés par IA (Seller Contenu IA)
CREATE TABLE seller_contenu_ia (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    merchant_id BIGINT UNSIGNED NOT NULL,
    article_id BIGINT UNSIGNED NULL,
    plateforme ENUM('instagram', 'tiktok', 'facebook', 'google_business', 'whatsapp') NOT NULL,
    type ENUM('post', 'image_rendu', 'story', 'reel', 'short', 'ad_copy') NOT NULL DEFAULT 'post',
    contenu TEXT NULL COMMENT 'Texte ou JSON multi-champs',
    media_url VARCHAR(500) NULL,
    media_path VARCHAR(500) NULL,
    prompt_utilise TEXT NULL,
    provider VARCHAR(50) NULL,
    tokens_in INT UNSIGNED NULL,
    tokens_out INT UNSIGNED NULL,
    statut ENUM('draft', 'scheduled', 'published', 'rejected', 'archived') NOT NULL DEFAULT 'draft',
    scheduled_at DATETIME NULL,
    published_at DATETIME NULL,
    published_by BIGINT UNSIGNED NULL,
    external_id VARCHAR(100) NULL,
    external_url VARCHAR(500) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE,
    FOREIGN KEY (article_id) REFERENCES seller_articles(id) ON DELETE SET NULL,
    INDEX idx_seller_contenu_sched (merchant_id, statut, scheduled_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 22. File d'attente Asynchrone IA (Seller Jobs Queue)
CREATE TABLE seller_jobs_queue (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    merchant_id BIGINT UNSIGNED NOT NULL,
    type VARCHAR(50) NOT NULL COMMENT 'render_pro, social_copy, vision_describe, seo_generate, cart_recovery',
    payload JSON NOT NULL,
    priority TINYINT UNSIGNED NOT NULL DEFAULT 10,
    statut ENUM('pending', 'processing', 'done', 'failed') NOT NULL DEFAULT 'pending',
    tentatives TINYINT UNSIGNED NOT NULL DEFAULT 0,
    max_tentatives TINYINT UNSIGNED NOT NULL DEFAULT 5,
    result JSON NULL,
    error_message TEXT NULL,
    started_at DATETIME NULL,
    completed_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE,
    INDEX idx_seller_jobs_status (statut, priority, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 23. Compteur d'Usage Tokens IA (Seller AI Usage)
CREATE TABLE seller_ai_usage (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    merchant_id BIGINT UNSIGNED NOT NULL,
    date_usage DATE NOT NULL,
    provider VARCHAR(50) NOT NULL,
    tokens_in BIGINT UNSIGNED NOT NULL DEFAULT 0,
    tokens_out BIGINT UNSIGNED NOT NULL DEFAULT 0,
    total_tokens BIGINT UNSIGNED NOT NULL DEFAULT 0,
    image_calls INT UNSIGNED NOT NULL DEFAULT 0,
    requests_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,
    UNIQUE KEY uk_seller_ai_usage (merchant_id, date_usage, provider),
    FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 24. Campagnes Ads IA (Seller Campagnes Ads)
CREATE TABLE seller_campagnes_ads (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    merchant_id BIGINT UNSIGNED NOT NULL,
    article_id BIGINT UNSIGNED NULL,
    plateforme ENUM('google', 'meta', 'tiktok') NOT NULL,
    strategie_json JSON NOT NULL,
    budget_mensuel DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    statut ENUM('draft', 'pending_validation', 'active', 'paused', 'ended', 'rejected') NOT NULL DEFAULT 'draft',
    cpa_cible DECIMAL(10,2) NULL,
    external_campaign_id VARCHAR(100) NULL,
    metrics JSON NULL,
    validated_by BIGINT UNSIGNED NULL,
    validated_at DATETIME NULL,
    started_at DATETIME NULL,
    ended_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE,
    FOREIGN KEY (article_id) REFERENCES seller_articles(id) ON DELETE SET NULL,
    INDEX idx_seller_ads_status (merchant_id, statut)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 25. Personas & Vendeurs Virtuels IA (Seller Personas)
CREATE TABLE seller_personas (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    merchant_id BIGINT UNSIGNED NOT NULL,
    nom VARCHAR(100) NOT NULL,
    system_prompt TEXT NOT NULL,
    ton VARCHAR(100) NOT NULL DEFAULT 'chaleureux et direct',
    langue_defaut ENUM('fr', 'darija', 'auto') NOT NULL DEFAULT 'auto',
    contexte_rag JSON NULL,
    actif BOOLEAN NOT NULL DEFAULT 1,
    est_defaut BOOLEAN NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE,
    INDEX idx_seller_personas_merchant (merchant_id, actif)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 26. Conversations Vendeur Virtuel / Chatbot (Seller Conversations)
CREATE TABLE seller_conversations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    merchant_id BIGINT UNSIGNED NOT NULL,
    persona_id BIGINT UNSIGNED NOT NULL,
    canal ENUM('whatsapp', 'instagram_dm', 'widget_web', 'facebook_messenger') NOT NULL,
    client_identifiant VARCHAR(100) NOT NULL,
    client_nom VARCHAR(100) NULL,
    client_email VARCHAR(255) NULL,
    customer_id BIGINT UNSIGNED NULL COMMENT 'Lien direct vers customers(id) CLK',
    statut ENUM('bot', 'escaladed_human', 'closed', 'pending_human') NOT NULL DEFAULT 'bot',
    contexte JSON NULL,
    escalade_raison VARCHAR(255) NULL,
    escalade_a BIGINT UNSIGNED NULL,
    started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ended_at DATETIME NULL,
    last_message_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_seller_conversations_client (merchant_id, canal, client_identifiant),
    FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE,
    FOREIGN KEY (persona_id) REFERENCES seller_personas(id) ON DELETE RESTRICT,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
    INDEX idx_seller_conversations_last (merchant_id, last_message_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 27. Messages des Conversations (Seller Messages)
CREATE TABLE seller_messages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    conversation_id BIGINT UNSIGNED NOT NULL,
    emetteur ENUM('client', 'bot', 'humain') NOT NULL,
    contenu TEXT NOT NULL,
    contenu_type ENUM('text', 'image', 'audio', 'location', 'contact', 'product', 'template', 'interactive') NOT NULL DEFAULT 'text',
    media_url VARCHAR(500) NULL,
    metadata JSON NULL,
    tokens_in INT UNSIGNED NULL,
    tokens_out INT UNSIGNED NULL,
    provider VARCHAR(50) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (conversation_id) REFERENCES seller_conversations(id) ON DELETE CASCADE,
    INDEX idx_seller_msg_created (conversation_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ==============================================================================
-- Données Initiales / Seed de Démarrage (Arty Déco & Seller AI)
-- ==============================================================================

-- Marchand 1 : Arty Déco (B2C & Boutiques HTML / Widgets)
INSERT INTO merchants (id, name, slug, api_key_hash, segment, plan, currency, vat_rate, status)
VALUES (
    1,
    'Arty Déco',
    'arty-deco',
    SHA2('clk_demo_dev_key_2026_xxxxxxxxxxxxxxxxxxxxxxxx', 256),
    'b2c',
    'pro',
    'MAD',
    20.00,
    'active'
) ON DUPLICATE KEY UPDATE name=VALUES(name);

-- Entrepôt principal
INSERT INTO warehouses (id, merchant_id, name, city, is_default)
VALUES (1, 1, 'Entrepôt Principal Casablanca', 'Casablanca', 1)
ON DUPLICATE KEY UPDATE name=VALUES(name);

-- Produits Arty Déco
INSERT INTO products (id, merchant_id, warehouse_id, external_sku, name, price, cost_price, image_url, status)
VALUES 
(1, 1, 1, 'TABLE-BOIS-01', 'Table Basse Chêne Massif', 145000, 80000, 'https://images.unsplash.com/photo-1533090161767-e6ffed986c88?w=500', 'active'),
(2, 1, 1, 'LAMPE-DESIGN-02', 'Lampe à Poser Céramique', 48000, 22000, 'https://images.unsplash.com/photo-1507473885765-e6ed057f782c?w=500', 'active'),
(3, 1, 1, 'TAPIS-BERBERE-03', 'Tapis Berbère Artisanal 200x300', 290000, 150000, 'https://images.unsplash.com/photo-1600121848594-d8644e57abab?w=500', 'active'),
(4, 1, 1, 'VASE-TERRACOTTA-04', 'Vase Terracotta Fait Main', 25000, 9000, 'https://images.unsplash.com/photo-1612196808214-b8e1d6145a8c?w=500', 'active')
ON DUPLICATE KEY UPDATE name=VALUES(name), price=VALUES(price);

-- Persona Vendeur Virtuel par Défaut pour Arty Déco
INSERT INTO seller_personas (id, merchant_id, nom, system_prompt, ton, langue_defaut, actif, est_defaut)
VALUES (
    1,
    1,
    'Kenza (Conseillère Déco)',
    'Tu es Kenza, conseillère de vente et décoratrice d\'intérieur pour Arty Déco. Tu es chaleureuse, experte et accueillante. Tu réponds en Français ou en Darija selon la langue du client. Ton rôle est de conseiller les clients sur les meubles et objets de déco, de les guider et de leur proposer le lien de paiement direct clk.ma pour commander facilement.',
    'chaleureux, expert et serviable',
    'auto',
    1,
    1
) ON DUPLICATE KEY UPDATE nom=VALUES(nom);

-- Modes de livraison Casablanca & Villes du Maroc
INSERT INTO shipping_methods (merchant_id, carrier, city, price, eta_days, active)
VALUES 
(1, 'Livraison Express Casa', 'Casablanca', 2500, 1, 1),
(1, 'Livraison Standard Maroc', 'Rabat', 3500, 2, 1),
(1, 'Livraison Standard Maroc', 'Marrakech', 4000, 2, 1),
(1, 'Livraison Standard Maroc', 'Tanger', 4000, 2, 1)
ON DUPLICATE KEY UPDATE carrier=VALUES(carrier);
