-- ============================================================
-- À exécuter UNE SEULE FOIS sur la base de production (rashdeals_ecom)
-- via phpMyAdmin (cPanel) ou `mysql` en SSH.
--
-- Contrairement à schema.sql (utilisé en local avec XAMPP), ce fichier
-- ne contient PAS de CREATE DATABASE / USE : sur un hébergement mutualisé
-- cPanel, la base "rashdeals_ecom" existe déjà et l'utilisateur MySQL
-- fourni n'a généralement pas le droit de créer une nouvelle base.
--
-- Ce fichier crée les tables si elles n'existent pas encore, puis insère
-- les 15 produits avec leur Pixel ID + token Conversions API déjà
-- configurés (mêmes valeurs que la base de dev locale 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;

INSERT INTO products (id, slug, name, currency, currency_label, price_tiers, meta_pixel_id, meta_access_token, meta_test_event_code, meta_event_name, active) VALUES
(1,'appareil-manicure-pedicure','جهاز صالون الأظافر الكهربائي','MAD','د.م','[{"qty":1,"price":119},{"qty":2,"price":219},{"qty":3,"price":329}]','28149217874696968','EAAYdCZBJQXywBSEazJHU9w0P2N1bpNkrdllTXKbcZAVWM54ZCS55eu6x5Dyapa4T0HEFNEZBFphPKi2tlZBd9RudKtdH84aebneoD6YncVXybKErGrBNdsyWbTOxuN0HUIhAtB8nrlNmHtu5EbpyfDYH0aZCl8uinbPSLncSgiCjsULsK0Mpw1St5yZCXimHjE8swZDZD',NULL,'Purchase',1),
(2,'chargeur-induction','شاحن السيارة المغناطيسي اللاسلكي السريع 15W','MAD','د.م','[{"qty":1,"price":109},{"qty":2,"price":205},{"qty":3,"price":305}]','28149217874696968','EAAYdCZBJQXywBSEazJHU9w0P2N1bpNkrdllTXKbcZAVWM54ZCS55eu6x5Dyapa4T0HEFNEZBFphPKi2tlZBd9RudKtdH84aebneoD6YncVXybKErGrBNdsyWbTOxuN0HUIhAtB8nrlNmHtu5EbpyfDYH0aZCl8uinbPSLncSgiCjsULsK0Mpw1St5yZCXimHjE8swZDZD',NULL,'Lead',1),
(3,'cle-a-cliquet','مفتاح الرانشيت الذكي لرافعة السيارة','MAD','د.م','[{"qty":1,"price":99},{"qty":2,"price":179},{"qty":3,"price":259}]','28149217874696968','EAAYdCZBJQXywBSEazJHU9w0P2N1bpNkrdllTXKbcZAVWM54ZCS55eu6x5Dyapa4T0HEFNEZBFphPKi2tlZBd9RudKtdH84aebneoD6YncVXybKErGrBNdsyWbTOxuN0HUIhAtB8nrlNmHtu5EbpyfDYH0aZCl8uinbPSLncSgiCjsULsK0Mpw1St5yZCXimHjE8swZDZD',NULL,'Lead',1),
(4,'coussin-voyage-massage','وسادة الرقبة بالتدليك الكهربائي','MAD','درهم','[{"qty":1,"price":199},{"qty":2,"price":349},{"qty":3,"price":499}]','28149217874696968','EAAYdCZBJQXywBSEazJHU9w0P2N1bpNkrdllTXKbcZAVWM54ZCS55eu6x5Dyapa4T0HEFNEZBFphPKi2tlZBd9RudKtdH84aebneoD6YncVXybKErGrBNdsyWbTOxuN0HUIhAtB8nrlNmHtu5EbpyfDYH0aZCl8uinbPSLncSgiCjsULsK0Mpw1St5yZCXimHjE8swZDZD',NULL,'Lead',1),
(5,'cuisiniere-camping','موقد غاز محمول للتخييم','MAD','د.م','[{"qty":1,"price":199},{"qty":2,"price":379},{"qty":3,"price":549}]','28149217874696968','EAAYdCZBJQXywBSEazJHU9w0P2N1bpNkrdllTXKbcZAVWM54ZCS55eu6x5Dyapa4T0HEFNEZBFphPKi2tlZBd9RudKtdH84aebneoD6YncVXybKErGrBNdsyWbTOxuN0HUIhAtB8nrlNmHtu5EbpyfDYH0aZCl8uinbPSLncSgiCjsULsK0Mpw1St5yZCXimHjE8swZDZD',NULL,'Lead',1),
(6,'gonfleur-auto','جهاز نفخ الإطارات الرقمي 4 في 1','MAD','د.م','[{"qty":1,"price":429},{"qty":2,"price":799},{"qty":3,"price":1199}]','28149217874696968','EAAYdCZBJQXywBSEazJHU9w0P2N1bpNkrdllTXKbcZAVWM54ZCS55eu6x5Dyapa4T0HEFNEZBFphPKi2tlZBd9RudKtdH84aebneoD6YncVXybKErGrBNdsyWbTOxuN0HUIhAtB8nrlNmHtu5EbpyfDYH0aZCl8uinbPSLncSgiCjsULsK0Mpw1St5yZCXimHjE8swZDZD',NULL,'Purchase',1),
(7,'machine-a-popcorn','آلة صنع الفشار المنزلية','MAD','د.م','[{"qty":1,"price":199},{"qty":2,"price":379},{"qty":3,"price":549}]','28149217874696968','EAAYdCZBJQXywBSEazJHU9w0P2N1bpNkrdllTXKbcZAVWM54ZCS55eu6x5Dyapa4T0HEFNEZBFphPKi2tlZBd9RudKtdH84aebneoD6YncVXybKErGrBNdsyWbTOxuN0HUIhAtB8nrlNmHtu5EbpyfDYH0aZCl8uinbPSLncSgiCjsULsK0Mpw1St5yZCXimHjE8swZDZD',NULL,'Lead',1),
(8,'mini-ventilateur','المروحة الذكية 3 في 1','MAD','درهم','[{"qty":1,"price":129},{"qty":2,"price":239},{"qty":3,"price":359}]','28149217874696968','EAAYdCZBJQXywBSEazJHU9w0P2N1bpNkrdllTXKbcZAVWM54ZCS55eu6x5Dyapa4T0HEFNEZBFphPKi2tlZBd9RudKtdH84aebneoD6YncVXybKErGrBNdsyWbTOxuN0HUIhAtB8nrlNmHtu5EbpyfDYH0aZCl8uinbPSLncSgiCjsULsK0Mpw1St5yZCXimHjE8swZDZD',NULL,'Lead',1),
(9,'sache-power-bank','الشنطة الذكية المضادة للسرقة','MAD','د.م.','[{"qty":1,"price":149},{"qty":2,"price":279},{"qty":3,"price":429}]','28149217874696968','EAAYdCZBJQXywBSEazJHU9w0P2N1bpNkrdllTXKbcZAVWM54ZCS55eu6x5Dyapa4T0HEFNEZBFphPKi2tlZBd9RudKtdH84aebneoD6YncVXybKErGrBNdsyWbTOxuN0HUIhAtB8nrlNmHtu5EbpyfDYH0aZCl8uinbPSLncSgiCjsULsK0Mpw1St5yZCXimHjE8swZDZD',NULL,'Lead',1),
(10,'tire-lait-maternel','شفاط الحليب الكهربائي الذكي','MAD','د.م','[{"qty":1,"price":199},{"qty":2,"price":398},{"qty":3,"price":597}]','28149217874696968','EAAYdCZBJQXywBSEazJHU9w0P2N1bpNkrdllTXKbcZAVWM54ZCS55eu6x5Dyapa4T0HEFNEZBFphPKi2tlZBd9RudKtdH84aebneoD6YncVXybKErGrBNdsyWbTOxuN0HUIhAtB8nrlNmHtu5EbpyfDYH0aZCl8uinbPSLncSgiCjsULsK0Mpw1St5yZCXimHjE8swZDZD',NULL,'Purchase',1),
(11,'chargeur-allume-cigare','شاحن السيارة 4 في 1 بإسقاط السماء المرصعة بالنجوم','MAD','درهم','[{"qty":1,"price":149},{"qty":2,"price":279},{"qty":3,"price":429}]','28149217874696968','EAAYdCZBJQXywBSEazJHU9w0P2N1bpNkrdllTXKbcZAVWM54ZCS55eu6x5Dyapa4T0HEFNEZBFphPKi2tlZBd9RudKtdH84aebneoD6YncVXybKErGrBNdsyWbTOxuN0HUIhAtB8nrlNmHtu5EbpyfDYH0aZCl8uinbPSLncSgiCjsULsK0Mpw1St5yZCXimHjE8swZDZD',NULL,'Lead',1),
(12,'coupe-legumes','مقطعة الخضر الذكية 12 في 1','MAD','د.م','[{"qty":1,"price":119},{"qty":2,"price":219},{"qty":3,"price":329}]','28149217874696968','EAAYdCZBJQXywBSEazJHU9w0P2N1bpNkrdllTXKbcZAVWM54ZCS55eu6x5Dyapa4T0HEFNEZBFphPKi2tlZBd9RudKtdH84aebneoD6YncVXybKErGrBNdsyWbTOxuN0HUIhAtB8nrlNmHtu5EbpyfDYH0aZCl8uinbPSLncSgiCjsULsK0Mpw1St5yZCXimHjE8swZDZD',NULL,'Lead',1),
(13,'lampe-solaire-camping','مصباح التخييم الشمسي متعدد الوظائف','MAD','د.م','[{"qty":1,"price":149},{"qty":2,"price":279},{"qty":3,"price":429}]','28149217874696968','EAAYdCZBJQXywBSEazJHU9w0P2N1bpNkrdllTXKbcZAVWM54ZCS55eu6x5Dyapa4T0HEFNEZBFphPKi2tlZBd9RudKtdH84aebneoD6YncVXybKErGrBNdsyWbTOxuN0HUIhAtB8nrlNmHtu5EbpyfDYH0aZCl8uinbPSLncSgiCjsULsK0Mpw1St5yZCXimHjE8swZDZD',NULL,'Purchase',1),
(14,'lunch-box','طقم لانش بوكس عائلي معزول الحرارة','MAD','د.م','[{"qty":1,"price":189},{"qty":2,"price":349},{"qty":3,"price":519}]','28149217874696968','EAAYdCZBJQXywBSEazJHU9w0P2N1bpNkrdllTXKbcZAVWM54ZCS55eu6x5Dyapa4T0HEFNEZBFphPKi2tlZBd9RudKtdH84aebneoD6YncVXybKErGrBNdsyWbTOxuN0HUIhAtB8nrlNmHtu5EbpyfDYH0aZCl8uinbPSLncSgiCjsULsK0Mpw1St5yZCXimHjE8swZDZD',NULL,'Lead',1),
(15,'tente-camping','خيمة التخييم العائلية المقاومة للماء','MAD','د.م','[{"qty":1,"price":349},{"qty":2,"price":669},{"qty":3,"price":959}]','28149217874696968','EAAYdCZBJQXywBSEazJHU9w0P2N1bpNkrdllTXKbcZAVWM54ZCS55eu6x5Dyapa4T0HEFNEZBFphPKi2tlZBd9RudKtdH84aebneoD6YncVXybKErGrBNdsyWbTOxuN0HUIhAtB8nrlNmHtu5EbpyfDYH0aZCl8uinbPSLncSgiCjsULsK0Mpw1St5yZCXimHjE8swZDZD',NULL,'Purchase',1),
(16,'clignotants-moto','إشارات LED ذكية للدراجة النارية DALION','MAD','د.م','[{"qty":1,"price":129},{"qty":2,"price":239},{"qty":3,"price":360}]','28149217874696968','EAAYdCZBJQXywBSEazJHU9w0P2N1bpNkrdllTXKbcZAVWM54ZCS55eu6x5Dyapa4T0HEFNEZBFphPKi2tlZBd9RudKtdH84aebneoD6YncVXybKErGrBNdsyWbTOxuN0HUIhAtB8nrlNmHtu5EbpyfDYH0aZCl8uinbPSLncSgiCjsULsK0Mpw1St5yZCXimHjE8swZDZD',NULL,'Purchase',1)
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    price_tiers = VALUES(price_tiers),
    meta_pixel_id = VALUES(meta_pixel_id),
    meta_access_token = VALUES(meta_access_token),
    meta_event_name = VALUES(meta_event_name),
    active = VALUES(active);