-- =============================================
-- Parfumerie HOUD - Database Schema
-- =============================================

CREATE DATABASE IF NOT EXISTS `parfsrob_houd` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `parfsrob_houd`;

-- ─── CATEGORIES ───
CREATE TABLE `categories` (
  `id` VARCHAR(50) PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `icon` VARCHAR(50) DEFAULT '📦',
  `image` VARCHAR(500) DEFAULT '/images/placeholder.jpg',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `categories` (`id`, `name`, `icon`, `image`) VALUES
('perfume-men', 'عطور رجالية', '🧔', '/images/perfume-men.jpg'),
('perfume-women', 'عطور نسائية', '👩', '/images/perfume-women.jpg'),
('makeup', 'مكياج', '💄', '/images/makeup.jpg'),
('parapharmacy', 'باراصيدالية', '🧴', '/images/parapharmacy.jpg');

-- ─── PRODUCTS ───
CREATE TABLE `products` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `price` DECIMAL(10,2) NOT NULL,
  `oldPrice` DECIMAL(10,2) DEFAULT NULL,
  `image` VARCHAR(500) NOT NULL,
  `category` VARCHAR(50) NOT NULL,
  `description` TEXT,
  `badge` VARCHAR(100) DEFAULT NULL,
  `rating` DECIMAL(2,1) DEFAULT 4.5,
  `reviews` INT DEFAULT 0,
  `inStock` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`category`) REFERENCES `categories`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── ORDERS ───
CREATE TABLE `orders` (
  `id` VARCHAR(20) PRIMARY KEY,
  `customer_name` VARCHAR(255) NOT NULL,
  `customer_phone` VARCHAR(50) NOT NULL,
  `customer_city` VARCHAR(100) NOT NULL,
  `customer_address` TEXT NOT NULL,
  `customer_notes` TEXT,
  `total` DECIMAL(10,2) NOT NULL,
  `shipping` DECIMAL(10,2) DEFAULT 0,
  `status` ENUM('pending','confirmed','shipped','delivered','cancelled') DEFAULT 'pending',
  `is_paid` TINYINT(1) DEFAULT 0,
  `tracking_number` VARCHAR(100) DEFAULT NULL,
  `date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── ORDER ITEMS ───
CREATE TABLE `order_items` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `order_id` VARCHAR(20) NOT NULL,
  `product_id` INT NOT NULL,
  `product_name` VARCHAR(255) NOT NULL,
  `product_price` DECIMAL(10,2) NOT NULL,
  `quantity` INT NOT NULL DEFAULT 1,
  FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── ORDER NOTES ───
CREATE TABLE `order_notes` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `order_id` VARCHAR(20) NOT NULL,
  `text` TEXT NOT NULL,
  `type` ENUM('internal','status_change','whatsapp') DEFAULT 'internal',
  `date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── SETTINGS ───
CREATE TABLE `settings` (
  `key_name` VARCHAR(100) PRIMARY KEY,
  `value` TEXT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `settings` (`key_name`, `value`) VALUES
('storeName', 'بارفيوميري هود'),
('storeNameEn', 'Parfumerie Houd'),
('phone', '+212 6XX-XXXXXX'),
('whatsapp', '212600000000'),
('email', 'contact@parfumeriehoud.com'),
('freeShippingMin', '300'),
('shippingCost', '30'),
('currency', 'د.م'),
('announcement', '🚚 توصيل مجاني للطلبات فوق 300 درهم | الدفع عند الاستلام 💰'),
('adminPassword', 'admin123');