-- Schéma MySQL du dashboard central de gestion des leads
-- Exécuter dans phpMyAdmin ou via `mysql -u root < schema.sql`

CREATE DATABASE IF NOT EXISTS ecom_leads CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE ecom_leads;

CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(64) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    currency VARCHAR(8) NOT NULL DEFAULT 'MAD',
    currency_label VARCHAR(16) NOT NULL DEFAULT 'د.م',
    price_tiers JSON NOT NULL,
    meta_pixel_id VARCHAR(64) NULL,
    meta_access_token VARCHAR(255) NULL,
    meta_test_event_code VARCHAR(64) NULL,
    meta_event_name VARCHAR(32) NOT NULL DEFAULT 'Lead',
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS leads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    full_name VARCHAR(255) NOT NULL,
    phone VARCHAR(32) NOT NULL,
    city VARCHAR(120) NOT NULL,
    address VARCHAR(500) NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    unit_price DECIMAL(10,2) NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    currency VARCHAR(8) NOT NULL DEFAULT 'MAD',
    status ENUM('nouveau','confirme','expedie','annule') NOT NULL DEFAULT 'nouveau',
    notes TEXT NULL,
    event_id VARCHAR(64) NULL,
    source_url VARCHAR(500) NULL,
    ip_address VARCHAR(64) NULL,
    user_agent VARCHAR(500) NULL,
    fbp VARCHAR(128) NULL,
    fbc VARCHAR(128) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_leads_product FOREIGN KEY (product_id) REFERENCES products(id),
    INDEX idx_leads_product (product_id),
    INDEX idx_leads_status (status),
    INDEX idx_leads_created (created_at),
    INDEX idx_leads_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;