-- =============================================================================
-- XSular / Abirpara — full database schema + seed data (single source of truth)
--
-- phpMyAdmin / cPanel (recommended):
--   1. In cPanel → MySQL® Databases: create a database (and a user with ALL PRIVILEGES on it).
--   2. Open phpMyAdmin → select that database in the left sidebar (do not import “into” server root).
--   3. Import → Choose this file → Go.
--   This file does NOT run CREATE DATABASE or USE; it only runs in whichever database you selected.
--
-- CLI (optional): mysql -u YOUR_USER -p YOUR_DATABASE_NAME < database.sql
--
-- TABLES (application — in DROP order, children before parents):
--   rate_limits, search_logs
--   order_lines → orders (guest/checkout; links optional dashboard users + landing users)
--   landing_wishlist_items, wishlist_items, product_reviews
--   landing_refresh_tokens, landing_user_addresses → landing_users (storefront accounts)
--   refresh_tokens → users (dashboard staff)
--   products, featured_products, featured_bundle, blog_articles, testimonials
--   categories, concerns, hero_slides, faq_items
--
-- DEFAULT DASHBOARD LOGIN (change after first deploy):
--   username: admin
--   password: Admin123!
--
-- After import, set the same database name, user, and password in `BackEnd/src/config.php`
-- (or `config.local.php`). JWT / API secrets are not in this file. Align CORS with your front-end.
--
-- WARNING: This script DROPS the listed tables in the currently selected database, then recreates them.
-- =============================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `order_lines`;
DROP TABLE IF EXISTS `orders`;
DROP TABLE IF EXISTS `landing_wishlist_items`;
DROP TABLE IF EXISTS `wishlist_items`;
DROP TABLE IF EXISTS `product_reviews`;
DROP TABLE IF EXISTS `landing_refresh_tokens`;
DROP TABLE IF EXISTS `landing_user_addresses`;
DROP TABLE IF EXISTS `landing_users`;
DROP TABLE IF EXISTS `refresh_tokens`;
DROP TABLE IF EXISTS `search_logs`;
DROP TABLE IF EXISTS `rate_limits`;
DROP TABLE IF EXISTS `testimonials`;
DROP TABLE IF EXISTS `blog_articles`;
DROP TABLE IF EXISTS `featured_bundle`;
DROP TABLE IF EXISTS `featured_products`;
DROP TABLE IF EXISTS `products`;
DROP TABLE IF EXISTS `concerns`;
DROP TABLE IF EXISTS `categories`;
DROP TABLE IF EXISTS `hero_slides`;
DROP TABLE IF EXISTS `faq_items`;
DROP TABLE IF EXISTS `users`;

SET FOREIGN_KEY_CHECKS = 1;

-- -----------------------------------------------------------------------------
-- Auth & sessions
-- -----------------------------------------------------------------------------
CREATE TABLE `users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(255) NOT NULL UNIQUE,
  `password` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NULL,
  `display_name` VARCHAR(150) NULL,
  `is_admin` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Dashboard staff; storefront accounts are in landing_users',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `refresh_tokens` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `token` VARCHAR(255) NOT NULL UNIQUE,
  `expires_at` DATETIME NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_refresh_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `rate_limits` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `ip_address` VARCHAR(45) NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_rate_limits_ip_time` (`ip_address`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Landing storefront accounts (email login) — same database as catalog; distinct from dashboard `users`.
-- Profile: every field is required at registration and on update (no nullable profile columns).
CREATE TABLE `landing_users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(255) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `first_name` VARCHAR(120) NOT NULL,
  `last_name` VARCHAR(120) NOT NULL,
  `phone` VARCHAR(40) NOT NULL,
  `name_changed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Last change to first/last name; 15-day edit cooldown',
  `phone_changed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Last phone change; 30-day cooldown',
  `email_changed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Last email change; 30-day cooldown',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_landing_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Up to two saved delivery addresses per customer (slots 1 and 2). All columns required per row.
CREATE TABLE `landing_user_addresses` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `slot` TINYINT UNSIGNED NOT NULL COMMENT '1 = primary, 2 = secondary',
  `label` VARCHAR(100) NOT NULL COMMENT 'e.g. Domicile, Bureau',
  `recipient_full_name` VARCHAR(200) NOT NULL,
  `phone` VARCHAR(40) NOT NULL,
  `street_address` VARCHAR(300) NOT NULL,
  `city` VARCHAR(100) NOT NULL,
  `region` VARCHAR(100) NOT NULL COMMENT 'Wilaya / région',
  `postal_code` VARCHAR(20) NOT NULL,
  `country` VARCHAR(100) NOT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_landing_addr_user_slot` (`user_id`, `slot`),
  CONSTRAINT `chk_landing_addr_slot` CHECK (`slot` IN (1, 2)),
  CONSTRAINT `fk_landing_addr_user` FOREIGN KEY (`user_id`) REFERENCES `landing_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `landing_refresh_tokens` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `token` VARCHAR(64) NOT NULL,
  `expires_at` DATETIME NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_landing_rt_token` (`token`),
  KEY `idx_landing_rt_user` (`user_id`),
  CONSTRAINT `fk_landing_rt_user` FOREIGN KEY (`user_id`) REFERENCES `landing_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Default dashboard admin — password: Admin123! (bcrypt; rotate in production)
INSERT INTO `users` (`username`, `password`, `email`, `display_name`, `is_admin`) VALUES
('admin', '$2y$12$YTKXoaP3/GVXCD321s5jU.HEIRsaQ/XD1foceW6Z5Nn8uqCRHrlzG', 'admin@localhost', 'Administrator', 1);

-- -----------------------------------------------------------------------------
-- Catalog: products (shop + product detail page — all merchandising fields)
-- -----------------------------------------------------------------------------
CREATE TABLE `products` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `sku` VARCHAR(64) NULL UNIQUE,
  `name` VARCHAR(255) NOT NULL,
  `brand` VARCHAR(100) NOT NULL DEFAULT '',
  `category` VARCHAR(100) NOT NULL,
  `concern` VARCHAR(100) NULL,
  `subtitle` VARCHAR(255) NULL,
  `price` DECIMAL(10,2) NOT NULL COMMENT 'Current / promo price',
  `original_price` DECIMAL(10,2) NULL COMMENT 'Strike-through price when on promotion',
  `image` VARCHAR(500) NULL COMMENT 'Primary image URL (fallback if images JSON empty)',
  `images` JSON NULL COMMENT 'Gallery URLs (UI uses max 3)',
  `description` TEXT NULL COMMENT 'Short intro + SEO; full detail also in tabs JSON',
  `categories` JSON NULL COMMENT 'Up to 20 merchandising labels (API enforces max 20)',
  `full_description` LONGTEXT NULL COMMENT 'Rich HTML for PDP (sanitize on output in the app)',
  `tabs` JSON NULL COMMENT 'Accordion blocks: id, title, content',
  `rating` DECIMAL(2,1) NOT NULL DEFAULT 5.0 COMMENT 'Denormalized average; synced from product_reviews',
  `reviews_count` INT NOT NULL DEFAULT 0 COMMENT 'Denormalized count; synced from product_reviews',
  `stock` INT NOT NULL DEFAULT 0,
  `active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx_products_category_active` (`category`, `active`),
  KEY `idx_products_brand` (`brand`),
  KEY `idx_products_concern` (`concern`),
  KEY `idx_products_active_id` (`active`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Individual reviews (optional API / future “avis détaillés”)
CREATE TABLE `product_reviews` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `product_id` INT NOT NULL,
  `user_id` INT NULL,
  `reviewer_display_name` VARCHAR(100) NOT NULL,
  `rating` TINYINT NOT NULL,
  `title` VARCHAR(255) NULL,
  `body` TEXT NOT NULL,
  `verified_purchase` TINYINT(1) NOT NULL DEFAULT 0,
  `active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_review_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_review_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `chk_review_rating` CHECK (`rating` BETWEEN 1 AND 5),
  KEY `idx_product_reviews_product_active` (`product_id`, `active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Logged-in wishlist (localStorage in app; this is the server-side mirror)
CREATE TABLE `wishlist_items` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `product_id` INT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_wishlist_user_product` (`user_id`, `product_id`),
  CONSTRAINT `fk_wishlist_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_wishlist_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Storefront wishlist (landing shoppers) — synced with the app when signed in.
CREATE TABLE `landing_wishlist_items` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `landing_user_id` INT UNSIGNED NOT NULL,
  `product_id` INT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_landing_wishlist_user_product` (`landing_user_id`, `product_id`),
  KEY `idx_landing_wishlist_user` (`landing_user_id`),
  CONSTRAINT `fk_landing_wishlist_user` FOREIGN KEY (`landing_user_id`) REFERENCES `landing_users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_landing_wishlist_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Guest / COD checkout; `landing_user_id` set when the buyer is logged in on the storefront.
CREATE TABLE `orders` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `order_number` VARCHAR(40) NOT NULL UNIQUE,
  `customer_name` VARCHAR(255) NOT NULL,
  `customer_email` VARCHAR(255) NULL,
  `phone` VARCHAR(50) NOT NULL,
  `city` VARCHAR(100) NOT NULL,
  `address_line` TEXT NOT NULL,
  `payment_method` ENUM('cod','card') NOT NULL DEFAULT 'cod',
  `subtotal` DECIMAL(10,2) NOT NULL,
  `shipping_fee` DECIMAL(10,2) NOT NULL DEFAULT 0,
  `total` DECIMAL(10,2) NOT NULL,
  `status` ENUM('pending','paid','shipped','cancelled') NOT NULL DEFAULT 'pending',
  `user_id` INT NULL,
  `landing_user_id` INT UNSIGNED NULL,
  `notes` TEXT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_orders_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_orders_landing_user` FOREIGN KEY (`landing_user_id`) REFERENCES `landing_users` (`id`) ON DELETE SET NULL,
  KEY `idx_orders_landing_created` (`landing_user_id`, `created_at`),
  KEY `idx_orders_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `order_lines` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `order_id` INT NOT NULL,
  `product_id` INT NULL,
  `product_name` VARCHAR(255) NOT NULL,
  `brand` VARCHAR(100) NULL,
  `unit_price` DECIMAL(10,2) NOT NULL,
  `quantity` INT NOT NULL,
  `image_url` VARCHAR(500) NULL,
  CONSTRAINT `fk_line_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_line_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `search_logs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `query` VARCHAR(255) NOT NULL,
  `user_id` INT NULL,
  `ip_address` VARCHAR(45) NOT NULL,
  `results_count` INT DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_search_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  KEY `idx_search_logs_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- CMS / homepage
-- -----------------------------------------------------------------------------
CREATE TABLE `faq_items` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `question` VARCHAR(500) NOT NULL,
  `answer` TEXT NOT NULL,
  `sort_order` INT DEFAULT 0,
  `active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `hero_slides` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `desktop_img` VARCHAR(500) NOT NULL,
  `mobile_img` VARCHAR(500) NOT NULL,
  `desktop_text_alignment` ENUM('left','center','right') DEFAULT 'left',
  `mobile_text_alignment` ENUM('top','center','bottom') DEFAULT 'bottom',
  `badge` VARCHAR(100) NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT NOT NULL,
  `button_text` VARCHAR(100) NOT NULL,
  `button_link` VARCHAR(255) NOT NULL DEFAULT '#products',
  `sort_order` INT DEFAULT 0,
  `active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `categories` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(100) NOT NULL,
  `image` VARCHAR(500) NOT NULL,
  `link` VARCHAR(255) NOT NULL DEFAULT '',
  `sort_order` INT DEFAULT 0,
  `active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `concerns` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(150) NOT NULL,
  `image` VARCHAR(500) NOT NULL,
  `link` VARCHAR(255) NOT NULL DEFAULT '#concerns',
  `sort_order` INT DEFAULT 0,
  `active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Homepage cards; mirrors catalog fields for API fallback when `products` is absent
CREATE TABLE `featured_products` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `category` VARCHAR(100) NOT NULL,
  `brand` VARCHAR(100) NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `subtitle` VARCHAR(255) NULL,
  `concern` VARCHAR(100) NULL,
  `price` DECIMAL(10,2) NOT NULL,
  `original_price` DECIMAL(10,2) NULL,
  `image` VARCHAR(500) NOT NULL,
  `images` JSON NULL,
  `description` TEXT NULL,
  `categories` JSON NULL,
  `full_description` LONGTEXT NULL,
  `tabs` JSON NULL,
  `rating` DECIMAL(2,1) NOT NULL DEFAULT 5.0,
  `reviews_count` INT NOT NULL DEFAULT 0,
  `stock` INT NOT NULL DEFAULT 0,
  `sort_order` INT DEFAULT 0,
  `active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `featured_bundle` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `tag_text` VARCHAR(100) NOT NULL DEFAULT 'Limited Edition',
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT NOT NULL,
  `items` JSON NOT NULL,
  `images` JSON NOT NULL,
  `original_price` DECIMAL(10,2) NOT NULL,
  `sale_price` DECIMAL(10,2) NOT NULL,
  `savings_text` VARCHAR(100) NOT NULL,
  `button_text` VARCHAR(100) NOT NULL DEFAULT 'Add Bundle to Cart',
  `button_link` VARCHAR(500) NULL DEFAULT '/shop' COMMENT 'In-app path or full URL for bundle CTA',
  `active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `blog_articles` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `category` VARCHAR(100) NOT NULL,
  `category_color` VARCHAR(100) NOT NULL DEFAULT 'text-zinc-500',
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT NOT NULL,
  `image` VARCHAR(500) NOT NULL,
  `hero_image` VARCHAR(500) NULL,
  `author` VARCHAR(100) NULL,
  `read_time` VARCHAR(50) NULL,
  `content` JSON NULL,
  `link` VARCHAR(255) NOT NULL DEFAULT '#blog',
  `sort_order` INT DEFAULT 0,
  `active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `testimonials` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `product` VARCHAR(255) NOT NULL,
  `review` TEXT NOT NULL,
  `rating` TINYINT DEFAULT 5,
  `sort_order` INT DEFAULT 0,
  `active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- Seed: products (3 gallery URLs each, description + tabs, promo where noted)
-- =============================================================================
INSERT INTO `products` (`sku`,`name`,`brand`,`category`,`concern`,`subtitle`,`price`,`original_price`,`image`,`images`,`description`,`tabs`,`rating`,`reviews_count`,`stock`) VALUES
('CRV-FM001','Hydrating Facial Cleanser','CERAVE','Cleansers','Hydration','Gentle daily cleanser',145.00,175.00,'https://images.unsplash.com/photo-1556228578-0d85b1a4d571?q=80&w=800&auto=format&fit=crop','["https://images.unsplash.com/photo-1556228578-0d85b1a4d571?q=80&w=1200&auto=format&fit=crop","https://images.unsplash.com/photo-1616683693504-3ea7e9ad6fec?q=80&w=1200&auto=format&fit=crop","https://images.unsplash.com/photo-1596755389378-c31d21fd1273?q=80&w=1200&auto=format&fit=crop"]','A gentle, non-foaming cleanser that cleanses and hydrates without disrupting the protective skin barrier. Contains 3 essential ceramides and hyaluronic acid.','[{"id":"desc","title":"Description","content":"A gentle, non-foaming cleanser that cleanses and hydrates without disrupting the protective skin barrier. Contains 3 essential ceramides and hyaluronic acid."},{"id":"usage","title":"Conseils d''utilisation","content":"Massage onto wet skin in gentle circular motions. Rinse thoroughly. Avoid direct eye contact. Suitable morning and evening."},{"id":"ingredients","title":"Ingrédients","content":"Aqua, Glycerin, Cetearyl Alcohol, Ceramide NP, Ceramide AP, Ceramide EOP, Hyaluronic Acid, Cholesterol, Phenoxyethanol."}]',5.0,124,50),
('VCH-M89-290','Mineral 89 Booster Serum','VICHY','Moisturizers','Anti-Aging','Hyaluronic acid booster',290.00,320.00,'https://images.unsplash.com/photo-1616683693504-3ea7e9ad6fec?q=80&w=800&auto=format&fit=crop','["https://images.unsplash.com/photo-1616683693504-3ea7e9ad6fec?q=80&w=1200&auto=format&fit=crop","https://images.unsplash.com/photo-1556228578-0d85b1a4d571?q=80&w=1200&auto=format&fit=crop","https://images.unsplash.com/photo-1535585209827-a15fcdbc4c2d?q=80&w=1200&auto=format&fit=crop"]','Hyaluronic acid booster for hydrated, plump skin. Minimal formula with Vichy volcanic water.','[{"id":"desc","title":"Description","content":"Mineralizing water plus hyaluronic acid for daily reinforcement of the skin barrier."},{"id":"usage","title":"Utilisation","content":"Apply 2 drops to clean face before moisturizer, morning and/or evening."}]',4.8,89,30),
('LRP-EDP-195','Effaclar Duo+ Anti-Blemish','LA ROCHE-POSAY','Exfoliants','Acne','Anti-imperfection care',195.00,NULL,'https://images.unsplash.com/photo-1596755389378-c31d21fd1273?q=80&w=800&auto=format&fit=crop','["https://images.unsplash.com/photo-1596755389378-c31d21fd1273?q=80&w=1200&auto=format&fit=crop","https://images.unsplash.com/photo-1556228578-0d85b1a4d571?q=80&w=1200&auto=format&fit=crop","https://images.unsplash.com/photo-1577401239170-897942555fb3?q=80&w=1200&auto=format&fit=crop"]','Corrective care for oily, acne-prone skin. Reduces blemishes and marks.','[{"id":"desc","title":"Description","content":"Targets imperfections and helps prevent recurrence with a light gel texture."},{"id":"usage","title":"Utilisation","content":"Apply to affected areas once or twice daily after cleansing."}]',4.7,156,40),
('BDM-SEN-160','Sensibio H2O Micellar Water','BIODERMA','Cleansers','Hydration','Gentle micellar cleanser',160.00,185.00,'https://images.unsplash.com/photo-1535585209827-a15fcdbc4c2d?q=80&w=800&auto=format&fit=crop','["https://images.unsplash.com/photo-1535585209827-a15fcdbc4c2d?q=80&w=1200&auto=format&fit=crop","https://images.unsplash.com/photo-1596755389378-c31d21fd1273?q=80&w=1200&auto=format&fit=crop","https://images.unsplash.com/photo-1616683693504-3ea7e9ad6fec?q=80&w=1200&auto=format&fit=crop"]','Micellar solution for sensitive skin: cleanses and removes makeup without rinsing.','[{"id":"desc","title":"Description","content":"Soak a cotton pad and wipe face and eyes. No rinse needed."}]',4.9,201,60),
('AVN-CCM-210','Cleanance Comedomed Concentrate','AVÈNE','Exfoliants','Acne','Anti-blemish concentrate',210.00,245.00,'https://images.unsplash.com/photo-1556228578-0d85b1a4d571?q=80&w=800&auto=format&fit=crop','["https://images.unsplash.com/photo-1556228578-0d85b1a4d571?q=80&w=1200&auto=format&fit=crop","https://images.unsplash.com/photo-1596755389378-c31d21fd1273?q=80&w=1200&auto=format&fit=crop","https://images.unsplash.com/photo-1535585209827-a15fcdbc4c2d?q=80&w=1200&auto=format&fit=crop"]','Concentrated anti-blemish treatment with Comedoclastin for persistent imperfections.','[{"id":"desc","title":"Description","content":"Reduces blackheads and limits bacterial adhesion."},{"id":"usage","title":"Utilisation","content":"Apply a thin layer to the whole face or locally in the evening."}]',4.6,78,25),
('EUC-HFN-340','Hyaluron-Filler Night Cream','EUCERIN','Anti-Aging','Anti-Aging','Anti-wrinkle night cream',340.00,NULL,'https://images.unsplash.com/photo-1577401239170-897942555fb3?q=80&w=800&auto=format&fit=crop','["https://images.unsplash.com/photo-1577401239170-897942555fb3?q=80&w=1200&auto=format&fit=crop","https://images.unsplash.com/photo-1616683693504-3ea7e9ad6fec?q=80&w=1200&auto=format&fit=crop","https://images.unsplash.com/photo-1556228578-0d85b1a4d571?q=80&w=1200&auto=format&fit=crop"]','Night cream with hyaluronic acid to fill wrinkles and improve elasticity.','[{"id":"desc","title":"Description","content":"Supports skin regeneration overnight."}]',4.8,112,20),
('CRV-MC175','Moisturizing Cream 340g','CERAVE','Body Care','Hydration','Deep moisturizing cream',175.00,195.00,'https://images.unsplash.com/photo-1596755389378-c31d21fd1273?q=80&w=800&auto=format&fit=crop','["https://images.unsplash.com/photo-1596755389378-c31d21fd1273?q=80&w=1200&auto=format&fit=crop","https://images.unsplash.com/photo-1556228578-0d85b1a4d571?q=80&w=1200&auto=format&fit=crop","https://images.unsplash.com/photo-1535585209827-a15fcdbc4c2d?q=80&w=1200&auto=format&fit=crop"]','Rich cream with ceramides for dry to very dry skin on face and body.','[{"id":"desc","title":"Description","content":"MVE technology for 24h hydration."}]',4.9,234,45),
('VCH-LAC-420','LiftActiv Collagen Specialist','VICHY','Anti-Aging','Anti-Aging','Anti-aging day cream',420.00,480.00,'https://images.unsplash.com/photo-1616683693504-3ea7e9ad6fec?q=80&w=800&auto=format&fit=crop','["https://images.unsplash.com/photo-1616683693504-3ea7e9ad6fec?q=80&w=1200&auto=format&fit=crop","https://images.unsplash.com/photo-1577401239170-897942555fb3?q=80&w=1200&auto=format&fit=crop","https://images.unsplash.com/photo-1596755389378-c31d21fd1273?q=80&w=1200&auto=format&fit=crop"]','Day care targeting wrinkles, firmness and radiance.','[{"id":"desc","title":"Description","content":"Peptides and vitamin Cg for collagen support."}]',4.7,95,15),
('LRP-ANT-230','Anthelios UVmune 400 SPF50+','LA ROCHE-POSAY','Suncare','Hydration','Ultra protection sunscreen',230.00,NULL,'https://images.unsplash.com/photo-1535585209827-a15fcdbc4c2d?q=80&w=800&auto=format&fit=crop','["https://images.unsplash.com/photo-1535585209827-a15fcdbc4c2d?q=80&w=1200&auto=format&fit=crop","https://images.unsplash.com/photo-1556228578-0d85b1a4d571?q=80&w=1200&auto=format&fit=crop","https://images.unsplash.com/photo-1616683693504-3ea7e9ad6fec?q=80&w=1200&auto=format&fit=crop"]','Very high UVA/UVB protection fluid, invisible finish.','[{"id":"desc","title":"Description","content":"Shake before use. Apply generously before sun exposure."}]',4.9,178,55),
('BDM-ATO-185','Atoderm Intensive Balm','BIODERMA','Body Care','Hydration','Ultra-soothing balm',185.00,NULL,'https://images.unsplash.com/photo-1556228578-0d85b1a4d571?q=80&w=800&auto=format&fit=crop','["https://images.unsplash.com/photo-1556228578-0d85b1a4d571?q=80&w=1200&auto=format&fit=crop","https://images.unsplash.com/photo-1596755389378-c31d21fd1273?q=80&w=1200&auto=format&fit=crop","https://images.unsplash.com/photo-1577401239170-897942555fb3?q=80&w=1200&auto=format&fit=crop"]','Lipid-replenishing balm for very dry, atopy-prone skin.','[{"id":"desc","title":"Description","content":"Apply twice daily or as needed on dry areas."}]',4.8,143,35),
('AVN-THM-095','Thermal Spring Water Spray 300ml','AVÈNE','Moisturizers','Hydration','Soothing thermal spray',95.00,110.00,'https://images.unsplash.com/photo-1577401239170-897942555fb3?q=80&w=800&auto=format&fit=crop','["https://images.unsplash.com/photo-1577401239170-897942555fb3?q=80&w=1200&auto=format&fit=crop","https://images.unsplash.com/photo-1596755389378-c31d21fd1273?q=80&w=1200&auto=format&fit=crop","https://images.unsplash.com/photo-1535585209827-a15fcdbc4c2d?q=80&w=1200&auto=format&fit=crop"]','Soothing, anti-irritating mist for sensitive skin.','[{"id":"desc","title":"Description","content":"Spray from 20 cm, leave on or pat dry."}]',4.5,67,80),
('EUC-DPS-265','DermoPure Oil Control Serum','EUCERIN','Exfoliants','Acne','Oil control serum',265.00,299.00,'https://images.unsplash.com/photo-1596755389378-c31d21fd1273?q=80&w=800&auto=format&fit=crop','["https://images.unsplash.com/photo-1596755389378-c31d21fd1273?q=80&w=1200&auto=format&fit=crop","https://images.unsplash.com/photo-1556228578-0d85b1a4d571?q=80&w=1200&auto=format&fit=crop","https://images.unsplash.com/photo-1616683693504-3ea7e9ad6fec?q=80&w=1200&auto=format&fit=crop"]','Salicylic acid serum to refine texture and reduce post-acne marks.','[{"id":"desc","title":"Description","content":"Use in the evening on cleansed skin under moisturizer."}]',4.6,88,30);

UPDATE `products` SET `categories` = '["Skincare","Hydration","Nettoyant visage","Céramides","Peau sensible","Routine quotidienne"]',
`full_description` = CONCAT('<h2>HYDRATING FACIAL CLEANSER — CERAVE</h2>',
'<p>Nettoyant non moussant qui nettoie en douceur tout en préservant la barrière cutanée. Enrichi en <strong>3 céramides essentiels</strong> et en <em>acide hyaluronique</em>.</p>',
'<figure><img src="https://images.unsplash.com/photo-1556228578-0d85b1a4d571?q=80&w=900&auto=format&fit=crop" alt="Gamme CeraVe" width="520" height="390" /></figure>',
'<h3>Conseils d''utilisation</h3><p>Masser sur peau humide, rincer abondamment. Matin et/ou soir.</p>',
'<p>Pour découvrir nos offres et promotions du moment, <a href="https://abirpara.ma/shop" target="_blank" rel="noopener noreferrer"><strong>cliquez ici</strong></a>.</p>')
WHERE `id` = 1;

UPDATE `products` SET `categories` = '["Skincare","Sérum","Anti-âge","Acide hyaluronique","Vichy","Volcanic water"]' WHERE `id` = 2;
UPDATE `products` SET `categories` = '["Skincare","Acné","La Roche-Posay","Gel","Imperfections","Peau grasse"]' WHERE `id` = 3;
UPDATE `products` SET `categories` = '["Skincare","Démaquillant","Micellaire","Bioderma","Sensitive","Sans rinçage"]' WHERE `id` = 4;
UPDATE `products` SET `categories` = '["Skincare","Acné","Avène","Soin concentré","Anti-imperfections"]' WHERE `id` = 5;
UPDATE `products` SET `categories` = '["Skincare","Anti-âge","Nuit","Eucerin","Rides","Acide hyaluronique","Fermeté","Réparation"]',
`full_description` = CONCAT('<h2>HYALURON-FILLER NIGHT CREAM — EUCERIN</h2>',
'<p>Crème de nuit anti-rides qui comble les ridules grâce à l''acide hyaluronique à <strong>molecular weight</strong> multiple. Texture fondante, <em>non grasse</em>.</p>',
'<figure><img src="https://images.unsplash.com/photo-1577401239170-897942555fb3?q=80&w=900&auto=format&fit=crop" alt="Soin nuit Eucerin" width="520" height="347" /></figure>',
'<h3>Hydratation intense et texture légère</h3><p>Absorption rapide, idéale sous le maquillage le lendemain.</p>',
'<ul><li>Application le soir sur visage nettoyé</li><li>Éviter le contour des yeux si sensibilité</li></ul>',
'<p>Offres du moment : <a href="https://abirpara.ma/shop" target="_blank" rel="noopener noreferrer"><strong>Cliquez ici</strong></a> · Réseaux : ',
'<a href="#" target="_blank" rel="noopener noreferrer">TikTok</a>, ',
'<a href="#" target="_blank" rel="noopener noreferrer">Instagram</a>, ',
'<a href="#" target="_blank" rel="noopener noreferrer">Facebook</a>.</p>')
WHERE `id` = 6;
UPDATE `products` SET `categories` = '["Body","Hydratation","CeraVe","Grand format","Ceramides","Famille"]' WHERE `id` = 7;
UPDATE `products` SET `categories` = '["Skincare","Jour","Anti-âge","Vichy","Collagène","Fermeté"]' WHERE `id` = 8;
UPDATE `products` SET `categories` = '["Suncare","SPF50+","UVA","UVB","La Roche-Posay","Fluide","Invisible","Sport","Mer","Ville","Peau sensible","Anthelios","Protection","Quotidien","Enfant","Adulte","Non gras","Eau","Dermatologique","Pharmacie"]' WHERE `id` = 9;
UPDATE `products` SET `categories` = '["Body","Bioderma","Atoderm","Très sec","Apaisant","Bebe"]' WHERE `id` = 10;
UPDATE `products` SET `categories` = '["Skincare","Brume","Avène","Eau thermale","Apaisant","Sport"]' WHERE `id` = 11;
UPDATE `products` SET `categories` = '["Skincare","Sérum","Acné","Eucerin","Matifiant","Salicylique"]' WHERE `id` = 12;

-- Sample reviews (syncs aggregates below)
INSERT INTO `product_reviews` (`product_id`,`user_id`,`reviewer_display_name`,`rating`,`title`,`body`,`verified_purchase`) VALUES
(1,NULL,'Samira E.',5,'Parfait pour ma peau sensible','Nettoyant doux, pas de tiraillement après la douche.',1),
(1,NULL,'Karim L.',5,NULL,'Commande reçue scellée, texture agréable.',1),
(1,NULL,'Leila M.',4,'Très bien','Bon rapport qualité prix au Maroc.',0),
(2,NULL,'Houda B.',5,'Boost d''hydratation','La peau est plus lisse au réveil.',1),
(2,NULL,'Omar T.',4,NULL,'Un peu cher mais efficace.',0),
(3,NULL,'Yasmine K.',5,'Effaclar indispensable','Moins de boutons en deux semaines.',1),
(4,NULL,'Nadia R.',5,'Démaquillage rapide','Sensibio ne pique pas les yeux.',1),
(5,NULL,'Mehdi A.',4,'Bon concentré','Texture légère, bon sous SPF.',0),
(6,NULL,'Salma I.',5,'Nuit réparatrice','Rides d''expression atténuées.',1),
(7,NULL,'Imane F.',5,'Famille entière','Gros pot, très économique.',1),
(8,NULL,'Rachid D.',4,'Bon lift','Parfum discret, bonne base maquillage.',0),
(9,NULL,'Fatima Z.',5,'Indispensable été','Pas de film blanc sur ma peau mate.',1),
(10,NULL,'Hassan M.',5,'Apaise les démangeaisons','Bébé et adulte: validé.',1),
(11,NULL,'Aicha L.',4,'Brume rafraîchissante','Spray fin, idéal après sport.',0),
(12,NULL,'Souad P.',5,'Texture gel','Peau moins brillante à la mi-journée.',1);

-- Denormalized PLP fields on `products` match active reviews (rating average + count).
UPDATE `products` p
INNER JOIN (
  SELECT `product_id`, COUNT(*) AS `c`, ROUND(AVG(`rating`), 1) AS `a`
  FROM `product_reviews`
  WHERE `active` = 1
  GROUP BY `product_id`
) r ON r.`product_id` = p.`id`
SET p.`reviews_count` = r.`c`, p.`rating` = r.`a`;

INSERT INTO `wishlist_items` (`user_id`, `product_id`) VALUES (1, 1);

-- =============================================================================
-- Seed: homepage & content (unchanged narrative, aligned types)
-- =============================================================================
INSERT INTO `faq_items` (`question`, `answer`, `sort_order`) VALUES
('Quels sont les délais de livraison ?', 'Nous livrons dans tout le Maroc. La livraison express est effectuée en 24 à 48 heures pour Casablanca, Rabat, Marrakech et les grandes villes. Pour les autres régions, comptez 3 à 5 jours ouvrables. Vous recevrez un SMS de confirmation avec le suivi de votre commande.', 1),
('Quelle est votre politique de retour ?', 'Votre satisfaction est notre priorité. Si un produit ne vous convient pas, vous disposez de 14 jours après réception pour effectuer un retour. Le produit doit être non ouvert et dans son emballage d''origine. Contactez notre service client pour initier la procédure de retour.', 2),
('Comment garantissez-vous l''authenticité des produits ?', 'Tous nos produits sont sourcés directement auprès des laboratoires officiels et des distributeurs agréés au Maroc. Chaque article dispose d''un numéro de lot traçable. Nous ne travaillons jamais avec des revendeurs tiers non autorisés. Authenticité garantie à 100%.', 3);

INSERT INTO `hero_slides` (`desktop_img`, `mobile_img`, `desktop_text_alignment`, `mobile_text_alignment`, `badge`, `title`, `description`, `button_text`, `button_link`, `sort_order`) VALUES
('https://images.unsplash.com/photo-1556228578-0d85b1a4d571?q=80&w=2000&auto=format&fit=crop', 'https://parachezvous.ma/wp-content/uploads/2023/07/cat-2-500x500.jpg', 'left', 'bottom', 'New Arrival', 'Premium Skincare', 'Dermatologist tested and approved formulation for radiant complexions.', 'Shop Collection', '#products', 1),
('https://images.unsplash.com/photo-1577401239170-897942555fb3?q=80&w=2000&auto=format&fit=crop', 'https://parachezvous.ma/wp-content/uploads/2023/07/cat-28-500x500.jpg', 'center', 'center', 'Natural Vitality', 'Holistic Health', 'Boost your daily vitality with our pure, pharmaceutical-grade supplements.', 'Explore Supplements', '#products', 2),
('https://images.unsplash.com/photo-1596755389378-c31d21fd1273?q=80&w=2000&auto=format&fit=crop', 'https://parachezvous.ma/wp-content/uploads/2023/07/cat-67-500x500.jpg', 'right', 'top', 'Clinical Solutions', 'Expert Treatments', 'Targeted, potent solutions crafted by skin specialists for noticeable results.', 'Discover Treatments', '#products', 3);

INSERT INTO `categories` (`title`, `image`, `link`, `sort_order`) VALUES
('Cleansers', 'https://parachezvous.ma/wp-content/uploads/2023/07/cat-2-500x500.jpg', '', 1),
('Anti-Aging', 'https://parachezvous.ma/wp-content/uploads/2023/07/cat-6-500x500.jpg', '', 2),
('Dark Spots', 'https://parachezvous.ma/wp-content/uploads/2023/07/cat-6-500x500.jpg', '', 3),
('Moisturizers', 'https://parachezvous.ma/wp-content/uploads/2023/07/cat-19-500x500.jpg', '', 4),
('Exfoliants', 'https://parachezvous.ma/wp-content/uploads/2023/07/cat-20-500x500.jpg', '', 5),
('Suncare', 'https://parachezvous.ma/wp-content/uploads/2023/07/cat-21-500x500.jpg', '', 6),
('Shower Gels', 'https://parachezvous.ma/wp-content/uploads/2023/07/cat-39-500x500.jpg', '', 7),
('Hand Care', 'https://parachezvous.ma/wp-content/uploads/2023/07/cat-40-500x500.jpg', '', 8),
('Intimate', 'https://parachezvous.ma/wp-content/uploads/2023/07/cat-67-500x500.jpg', '', 9),
('Shampoos', 'https://images.unsplash.com/photo-1535585209827-a15fcdbc4c2d?q=80&w=400&auto=format&fit=crop', '', 10),
('Dental', 'https://parachezvous.ma/wp-content/uploads/2023/07/cat-68-500x500.jpg', '', 11),
('Body Care', 'https://parachezvous.ma/wp-content/uploads/2023/07/cat-19-500x500.jpg', '', 12);

INSERT INTO `concerns` (`title`, `image`, `link`, `sort_order`) VALUES
('Anti-Aging & Wrinkles', 'https://images.unsplash.com/photo-1596755389378-c31d21fd1273?q=80&w=600&auto=format&fit=crop', '#concerns', 1),
('Acne & Blemishes', 'https://images.unsplash.com/photo-1556228578-0d85b1a4d571?q=80&w=600&auto=format&fit=crop', '#concerns', 2),
('Deep Hydration', 'https://images.unsplash.com/photo-1616683693504-3ea7e9ad6fec?q=80&w=600&auto=format&fit=crop', '#concerns', 3),
('Dark Spots & Radiance', 'https://images.unsplash.com/photo-1615397323204-6252d6a711bd?q=80&w=600&auto=format&fit=crop', '#concerns', 4);

INSERT INTO `featured_products` (`category`,`brand`,`title`,`subtitle`,`concern`,`price`,`original_price`,`image`,`images`,`description`,`categories`,`full_description`,`tabs`,`rating`,`reviews_count`,`stock`,`sort_order`) VALUES
('Moisturizers','La Roche-Posay','Hydraphase HA Riche','Pure Hyaluronic Acid','Hydration',245.00,310.00,'https://images.unsplash.com/photo-1556228578-0d85b1a4d571?q=80&w=1000&auto=format&fit=crop','["https://images.unsplash.com/photo-1556228578-0d85b1a4d571?q=80&w=1200&auto=format&fit=crop","https://images.unsplash.com/photo-1616683693504-3ea7e9ad6fec?q=80&w=1200&auto=format&fit=crop","https://images.unsplash.com/photo-1596755389378-c31d21fd1273?q=80&w=1200&auto=format&fit=crop"]','Intensive hydration with hyaluronic acid fragments.',NULL,NULL,'[{"id":"desc","title":"Description","content":"Long-lasting moisture for dehydrated sensitive skin."}]',4.9,42,25,1),
('Suncare','Vichy','Capital Soleil UV','Anti-Photo Aging','Hydration',190.00,NULL,'https://images.unsplash.com/photo-1577401239170-897942555fb3?q=80&w=1000&auto=format&fit=crop','["https://images.unsplash.com/photo-1577401239170-897942555fb3?q=80&w=1200&auto=format&fit=crop","https://images.unsplash.com/photo-1535585209827-a15fcdbc4c2d?q=80&w=1200&auto=format&fit=crop","https://images.unsplash.com/photo-1556228578-0d85b1a4d571?q=80&w=1200&auto=format&fit=crop"]','High protection fluid for daily urban use.',NULL,NULL,'[{"id":"usage","title":"Utilisation","content":"Renew every 2 hours in strong sun."}]',4.8,31,40,2),
('Body Care','CeraVe','Moisturizing Balm','Deep nourishment','Hydration',175.00,195.00,'https://images.unsplash.com/photo-1596755389378-c31d21fd1273?q=80&w=1000&auto=format&fit=crop','["https://images.unsplash.com/photo-1596755389378-c31d21fd1273?q=80&w=1200&auto=format&fit=crop","https://images.unsplash.com/photo-1556228578-0d85b1a4d571?q=80&w=1200&auto=format&fit=crop","https://images.unsplash.com/photo-1577401239170-897942555fb3?q=80&w=1200&auto=format&fit=crop"]','Barrier repair with 3 essential ceramides.',NULL,NULL,'[{"id":"desc","title":"Description","content":"Suitable for very dry skin."}]',4.9,210,35,3),
('Shower Gels','Bioderma','Atoderm Oil','Ultra-Nourishing Cleanser','Hydration',210.00,NULL,'https://images.unsplash.com/photo-1535585209827-a15fcdbc4c2d?q=80&w=1000&auto=format&fit=crop','["https://images.unsplash.com/photo-1535585209827-a15fcdbc4c2d?q=80&w=1200&auto=format&fit=crop","https://images.unsplash.com/photo-1596755389378-c31d21fd1273?q=80&w=1200&auto=format&fit=crop","https://images.unsplash.com/photo-1616683693504-3ea7e9ad6fec?q=80&w=1200&auto=format&fit=crop"]','Shower oil for dry sensitive skin.',NULL,NULL,'[{"id":"desc","title":"Description","content":"Soap-free lipid-replenishing formula."}]',4.7,56,20,4),
('Exfoliants','Avene','Cleanance Comedomed','Anti-Blemish Concentrate','Acne',195.00,260.00,'https://images.unsplash.com/photo-1556228578-0d85b1a4d571?q=80&w=1000&auto=format&fit=crop','["https://images.unsplash.com/photo-1556228578-0d85b1a4d571?q=80&w=1200&auto=format&fit=crop","https://images.unsplash.com/photo-1596755389378-c31d21fd1273?q=80&w=1200&auto=format&fit=crop","https://images.unsplash.com/photo-1535585209827-a15fcdbc4c2d?q=80&w=1200&auto=format&fit=crop"]','Targets blemishes and residual marks.',NULL,NULL,'[{"id":"usage","title":"Utilisation","content":"Evening application on clean skin."}]',4.6,88,22,5),
('Shampoos','Phyto','Phytocyane Serum','Anti Hair Loss','Anti-Aging',460.00,NULL,'https://images.unsplash.com/photo-1577401239170-897942555fb3?q=80&w=1000&auto=format&fit=crop','["https://images.unsplash.com/photo-1577401239170-897942555fb3?q=80&w=1200&auto=format&fit=crop","https://images.unsplash.com/photo-1596755389378-c31d21fd1273?q=80&w=1200&auto=format&fit=crop","https://images.unsplash.com/photo-1556228578-0d85b1a4d571?q=80&w=1200&auto=format&fit=crop"]','Revitalizing scalp treatment.',NULL,NULL,'[{"id":"desc","title":"Description","content":"Massage into scalp 3x per week."}]',4.5,19,12,6),
('Body Care','Eucerin','UreaRepair PLUS','10% Urea Lotion','Hydration',235.00,NULL,'https://images.unsplash.com/photo-1596755389378-c31d21fd1273?q=80&w=1000&auto=format&fit=crop','["https://images.unsplash.com/photo-1596755389378-c31d21fd1273?q=80&w=1200&auto=format&fit=crop","https://images.unsplash.com/photo-1577401239170-897942555fb3?q=80&w=1200&auto=format&fit=crop","https://images.unsplash.com/photo-1535585209827-a15fcdbc4c2d?q=80&w=1200&auto=format&fit=crop"]','Keratosis-prone skin smoothing lotion.',NULL,NULL,'[{"id":"desc","title":"Description","content":"Urea and ceramides for rough areas."}]',4.8,74,18,7),
('Anti-Aging','Filorga','Meso-Mask 50ml','Smoothing Illuminating Mask','Anti-Aging',580.00,620.00,'https://images.unsplash.com/photo-1535585209827-a15fcdbc4c2d?q=80&w=1000&auto=format&fit=crop','["https://images.unsplash.com/photo-1535585209827-a15fcdbc4c2d?q=80&w=1200&auto=format&fit=crop","https://images.unsplash.com/photo-1616683693504-3ea7e9ad6fec?q=80&w=1200&auto=format&fit=crop","https://images.unsplash.com/photo-1556228578-0d85b1a4d571?q=80&w=1200&auto=format&fit=crop"]','Weekly radiance mask.',NULL,NULL,'[{"id":"usage","title":"Utilisation","content":"Leave 15–30 minutes, rinse."}]',4.9,33,8,8);

INSERT INTO `featured_bundle` (`tag_text`, `title`, `description`, `items`, `images`, `original_price`, `sale_price`, `savings_text`, `button_text`, `button_link`) VALUES
('Limited Edition', 'The Suncare Routine Pack', 'Get exactly what your skin craves this summer. A curated trio providing high-spectrum defense, instant refreshment, and deep overnight repair.', '["1x Sunscreen 50+ (50ml)", "1x Mineral Thermal Water", "1x Repair After-Sun Balm"]', '["https://images.unsplash.com/photo-1556228578-0d85b1a4d571?q=80&w=1200&auto=format&fit=crop", "https://images.unsplash.com/photo-1616683693504-3ea7e9ad6fec?q=80&w=1200&auto=format&fit=crop", "https://images.unsplash.com/photo-1596755389378-c31d21fd1273?q=80&w=1200&auto=format&fit=crop"]', 450.00, 350.00, 'Save 100 MAD', 'Add Bundle to Cart', '/shop');

INSERT INTO `blog_articles` (`category`, `category_color`, `title`, `description`, `image`, `hero_image`, `author`, `read_time`, `content`, `link`, `sort_order`) VALUES
('Suncare', 'text-amber-500', 'How to Choose the Right Sunscreen for Your Skin', 'SPF, UVA, UVB — understanding the key filters and textures to find your ideal daily sun protection.', 'https://images.unsplash.com/photo-1532012197267-da84d127e765?q=80&w=800&auto=format&fit=crop', 'https://images.unsplash.com/photo-1532012197267-da84d127e765?q=80&w=1600&auto=format&fit=crop', 'Dr. Amina Belkacem', '4 min de lecture', '[{"type":"paragraph","text":"Choosing the right sunscreen is essential for protecting your skin from harmful UV rays."},{"type":"heading","text":"Understanding SPF Ratings"},{"type":"paragraph","text":"SPF measures protection against UVB rays. SPF 30 blocks 97% of UVB rays, while SPF 50 blocks 98%."},{"type":"heading","text":"UVA vs UVB Protection"},{"type":"paragraph","text":"Look for broad-spectrum protection that shields against both UVA (aging) and UVB (burning) rays."},{"type":"callout","text":"Expert tip: Reapply sunscreen every 2 hours, especially after swimming or sweating."}]', '#blog', 1),
('Skincare', 'text-[var(--primary)]', 'Evening Routine: 3 Steps to Flawless Skin', 'Cleanse, treat, hydrate — the essential nightly ritual recommended by dermatologists for radiant mornings.', 'https://images.unsplash.com/photo-1596755389378-c31d21fd1273?q=80&w=800&auto=format&fit=crop', 'https://images.unsplash.com/photo-1596755389378-c31d21fd1273?q=80&w=1600&auto=format&fit=crop', 'Dr. Amina Belkacem', '5 min de lecture', '[{"type":"paragraph","text":"La routine du soir est le pilier d''une peau saine et éclatante."},{"type":"heading","text":"Étape 1 : Le Double Nettoyage"},{"type":"paragraph","text":"Commencez par un nettoyant à base d''huile pour dissoudre le maquillage et le sébum."},{"type":"heading","text":"Étape 2 : Le Sérum Ciblé"},{"type":"paragraph","text":"Le sérum est l''arme secrète de toute routine efficace."},{"type":"heading","text":"Étape 3 : L''Hydratation Réparatrice"},{"type":"paragraph","text":"Scellez tous les actifs avec une crème de nuit riche en céramides."},{"type":"callout","text":"Conseil d''expert : Attendez 60 secondes entre chaque couche de produit pour permettre une absorption optimale."}]', '#blog', 2),
('Anti-Aging', 'text-[var(--secondary)]', 'The Secrets of Vitamin C Finally Revealed', 'Why this powerful antioxidant is the gold standard for brightening, firming, and protecting mature skin.', 'https://images.unsplash.com/photo-1556228578-0d85b1a4d571?q=80&w=800&auto=format&fit=crop', 'https://images.unsplash.com/photo-1556228578-0d85b1a4d571?q=80&w=1600&auto=format&fit=crop', 'Dr. Amina Belkacem', '6 min de lecture', '[{"type":"paragraph","text":"Vitamin C is one of the most researched ingredients in dermatology."},{"type":"heading","text":"Why Vitamin C?"},{"type":"paragraph","text":"It neutralizes free radicals, stimulates collagen production, and brightens the skin."},{"type":"callout","text":"Store vitamin C serums in a cool, dark place to maintain potency."}]', '#blog', 3),
('Hair Care', 'text-emerald-600', 'Transform Your Hair: Expert Tips for Density', 'The science behind hair thinning and the effective scalp treatments to boost volume and health naturally.', 'https://images.unsplash.com/photo-1596755389378-c31d21fd1273?q=80&w=800&auto=format&fit=crop', 'https://images.unsplash.com/photo-1596755389378-c31d21fd1273?q=80&w=1600&auto=format&fit=crop', 'Dr. Amina Belkacem', '5 min de lecture', '[{"type":"paragraph","text":"Hair thinning affects millions of people worldwide."},{"type":"heading","text":"Understanding Hair Loss"},{"type":"paragraph","text":"The most common causes include genetics, stress, hormonal changes, and nutritional deficiencies."},{"type":"heading","text":"Effective Treatments"},{"type":"paragraph","text":"Scalp serums with peptides and biotin can significantly improve hair density over 3-6 months."}]', '#blog', 4);

INSERT INTO `testimonials` (`name`, `product`, `review`, `rating`, `sort_order`) VALUES
('Sarah M.', 'CeraVe Foaming Cleanser', '100% original products. I received my order in 24 hours. The prices are excellent and the packaging is pristine.', 5, 1),
('Nadia K.', 'Mela B3 Serum', 'The customer service is exceptional! They guided me perfectly to choose my ideal anti-dark spot routine. Highly recommend.', 5, 2),
('Amina B.', 'Effaclar Duo+M', 'I''ve been buying all my dermatological products here for a year. Perfect quality, everything is sealed, and delivery is always fast.', 5, 3),
('Youssef T.', 'Vichy Dercos', 'Extremely reliable site for supplements. Everything is genuine, the expiration dates are well ahead. Very reassuring.', 5, 4),
('Hind R.', 'UreaRepair PLUS', 'This is my 4th order and there''s nothing to complain about. Unbeatable prices for Eucerin and truly professional service.', 5, 5),
('Meriam L.', 'Avene Thermal Water', 'Cash on delivery is super convenient! The courier was very professional and the products are exactly as you''d find in a pharmacy.', 5, 6);

-- =============================================================================
-- Appendix A — upgrades for databases created from an older dump (run manually)
-- =============================================================================
-- Profile edit cooldowns (landing storefront). Skip if columns already exist.
--
-- ALTER TABLE `landing_users`
--   ADD COLUMN `name_changed_at` DATETIME NULL DEFAULT NULL AFTER `phone`,
--   ADD COLUMN `phone_changed_at` DATETIME NULL DEFAULT NULL AFTER `name_changed_at`,
--   ADD COLUMN `email_changed_at` DATETIME NULL DEFAULT NULL AFTER `phone_changed_at`;
-- UPDATE `landing_users` SET `name_changed_at` = `created_at`, `phone_changed_at` = `created_at`, `email_changed_at` = `created_at`
--   WHERE `name_changed_at` IS NULL;
-- ALTER TABLE `landing_users`
--   MODIFY `name_changed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
--   MODIFY `phone_changed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
--   MODIFY `email_changed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;
--
-- Storefront wishlist persistence. Skip if table already exists.
--
-- CREATE TABLE `landing_wishlist_items` (
--   `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
--   `landing_user_id` INT UNSIGNED NOT NULL,
--   `product_id` INT NOT NULL,
--   `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
--   PRIMARY KEY (`id`),
--   UNIQUE KEY `uq_landing_wishlist_user_product` (`landing_user_id`, `product_id`),
--   KEY `idx_landing_wishlist_user` (`landing_user_id`),
--   CONSTRAINT `fk_landing_wishlist_user` FOREIGN KEY (`landing_user_id`) REFERENCES `landing_users` (`id`) ON DELETE CASCADE,
--   CONSTRAINT `fk_landing_wishlist_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- =============================================================================