SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `chatbot_logs`;
DROP TABLE IF EXISTS `collections`;
DROP TABLE IF EXISTS `contact_messages`;
DROP TABLE IF EXISTS `coupons`;
DROP TABLE IF EXISTS `email_queue`;
DROP TABLE IF EXISTS `order_items`;
DROP TABLE IF EXISTS `orders`;
DROP TABLE IF EXISTS `otp_verifications`;
DROP TABLE IF EXISTS `products`;
DROP TABLE IF EXISTS `reviews`;
DROP TABLE IF EXISTS `settings`;
DROP TABLE IF EXISTS `users`;
DROP TABLE IF EXISTS `verification_logs`;
SET FOREIGN_KEY_CHECKS = 1;

-- TABLE: users
CREATE TABLE `users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(120) NOT NULL,
  `email` VARCHAR(160) NOT NULL UNIQUE,
  `phone` VARCHAR(30) NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `is_admin` TINYINT(1) NOT NULL DEFAULT 0,
  `quiz_answers` JSON DEFAULT NULL,
  `email_verified` TINYINT(1) NOT NULL DEFAULT 0,
  `email_verified_at` DATETIME DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- TABLE: collections
CREATE TABLE `collections` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `slug` VARCHAR(80) NOT NULL UNIQUE,
  `name` VARCHAR(160) NOT NULL,
  `description` TEXT,
  `banner` VARCHAR(255) DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- TABLE: products
CREATE TABLE `products` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(160) NOT NULL,
  `arabicName` VARCHAR(160) DEFAULT NULL,
  `subtitle` VARCHAR(160) DEFAULT NULL,
  `chamber` VARCHAR(50) NOT NULL,
  `brand` VARCHAR(120) NOT NULL DEFAULT 'ALFARAS',
  `brandId` VARCHAR(120) NOT NULL DEFAULT 'alfaras',
  `artifactCode` VARCHAR(50) NOT NULL,
  `heritageYear` VARCHAR(30) NOT NULL,
  `originRegion` VARCHAR(120) NOT NULL,
  `price` INT NOT NULL,
  `mrp` INT DEFAULT NULL,
  `stock` INT NOT NULL DEFAULT 10,
  `sizes` JSON NOT NULL,
  `concentration` VARCHAR(50) NOT NULL,
  `rating` DECIMAL(3,1) NOT NULL DEFAULT 5.0,
  `reviews` INT NOT NULL DEFAULT 0,
  `isNew` TINYINT(1) NOT NULL DEFAULT 0,
  `isLimited` TINYINT(1) NOT NULL DEFAULT 0,
  `fragranceType` VARCHAR(50) NOT NULL,
  `topNotes` JSON NOT NULL,
  `heartNotes` JSON NOT NULL,
  `baseNotes` JSON NOT NULL,
  `description` TEXT NOT NULL,
  `perfumersChronicle` TEXT,
  `arabicStory` TEXT,
  `image` TEXT NOT NULL,
  `sealColor` VARCHAR(30) DEFAULT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `collection_id` VARCHAR(80) DEFAULT NULL,
  `scent_family` VARCHAR(120) DEFAULT NULL,
  `folder_name` VARCHAR(80) DEFAULT NULL,
  `gallery` JSON DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`collection_id`) REFERENCES `collections` (`slug`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- TABLE: orders
CREATE TABLE `orders` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `order_code` VARCHAR(30) NOT NULL UNIQUE,
  `order_id` VARCHAR(30) DEFAULT NULL,
  `verification_token_hash` VARCHAR(255) DEFAULT NULL,
  `user_id` INT DEFAULT NULL,
  `customer_name` VARCHAR(120) NOT NULL,
  `phone` VARCHAR(30) NOT NULL,
  `customer_email` VARCHAR(160) DEFAULT NULL,
  `address` TEXT NOT NULL,
  `city` VARCHAR(80) NOT NULL,
  `state` VARCHAR(80) NOT NULL,
  `pincode` VARCHAR(12) NOT NULL,
  `payment_method` VARCHAR(30) NOT NULL,
  `subtotal` INT NOT NULL,
  `shipping` INT NOT NULL,
  `cod_fee` INT NOT NULL,
  `total` INT NOT NULL,
  `item_count` INT NOT NULL,
  `status` VARCHAR(30) NOT NULL DEFAULT 'Processing',
  `email_status` ENUM('NOT_SENT','PENDING','SENT','FAILED') NOT NULL DEFAULT 'NOT_SENT',
  `email_sent_at` DATETIME DEFAULT NULL,
  `email_failure_reason` TEXT,
  `sales_channel` ENUM('online','offline') NOT NULL DEFAULT 'online',
  `bill_reference` VARCHAR(80) DEFAULT NULL,
  `sale_date` DATETIME DEFAULT NULL,
  `coupon_code` VARCHAR(50) DEFAULT NULL,
  `coupon_discount` INT NOT NULL DEFAULT 0,
  `is_hidden` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- TABLE: order_items
CREATE TABLE `order_items` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `order_id` INT NOT NULL,
  `product_id` INT NOT NULL,
  `product_name` VARCHAR(160) NOT NULL,
  `size_ml` INT NOT NULL,
  `price` INT NOT NULL,
  `quantity` INT NOT NULL,
  FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- TABLE: contact_messages
CREATE TABLE `contact_messages` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(120) NOT NULL,
  `email` VARCHAR(160) NOT NULL,
  `subject` VARCHAR(120) NOT NULL,
  `message` TEXT NOT NULL,
  `status` VARCHAR(30) NOT NULL DEFAULT 'New',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- TABLE: coupons
CREATE TABLE `coupons` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `code` VARCHAR(50) NOT NULL UNIQUE,
  `discount_type` VARCHAR(30) NOT NULL DEFAULT 'percentage',
  `discount_value` DECIMAL(10,2) NOT NULL,
  `min_cart_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `expiry_date` DATE DEFAULT NULL,
  `usage_limit` INT DEFAULT NULL,
  `used_count` INT NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- TABLE: email_queue
CREATE TABLE `email_queue` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT DEFAULT NULL,
  `order_id` INT DEFAULT NULL,
  `email` VARCHAR(160) NOT NULL,
  `to_name` VARCHAR(120) NOT NULL DEFAULT '',
  `subject` VARCHAR(255) NOT NULL,
  `html_body` LONGTEXT NOT NULL,
  `attachment_path` VARCHAR(500) DEFAULT NULL,
  `status` ENUM('PENDING','SENT','FAILED','RETRYING') NOT NULL DEFAULT 'PENDING',
  `retry_count` INT NOT NULL DEFAULT 0,
  `last_attempt_at` DATETIME DEFAULT NULL,
  `failure_reason` TEXT,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE SET NULL,
  KEY `idx_eq_status` (`status`),
  KEY `idx_eq_order` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- TABLE: otp_verifications
CREATE TABLE `otp_verifications` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `email` VARCHAR(160) NOT NULL,
  `otp_hash` VARCHAR(64) NOT NULL,
  `purpose` ENUM('register','login','forgot_password') NOT NULL DEFAULT 'register',
  `expires_at` DATETIME NOT NULL,
  `verified` TINYINT(1) NOT NULL DEFAULT 0,
  `attempts` INT NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_otp_email` (`email`),
  KEY `idx_otp_expires` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- TABLE: reviews
CREATE TABLE `reviews` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `product_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  `customer_name` VARCHAR(120) NOT NULL,
  `rating` INT NOT NULL,
  `text` TEXT NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `unique_product_user_review` (`product_id`,`user_id`),
  FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- TABLE: settings
CREATE TABLE `settings` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `setting_key` VARCHAR(100) NOT NULL UNIQUE,
  `setting_value` TEXT,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- TABLE: verification_logs
CREATE TABLE `verification_logs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `order_code` VARCHAR(50) NOT NULL,
  `is_verified` TINYINT(1) NOT NULL,
  `differences` TEXT,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- TABLE: chatbot_logs
CREATE TABLE `chatbot_logs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `session_id` VARCHAR(120) NOT NULL,
  `user_id` INT DEFAULT NULL,
  `question` TEXT NOT NULL,
  `response` TEXT NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ── DEFAULT SEEDS ──────────────────────────────────────────────────────────

-- Default User & Admin
INSERT INTO users (id, name, email, phone, password_hash, is_admin)
VALUES 
(1, 'Admin User', 'admin@essencia.in', '+965 9876 5432', '$2y$10$GfCR5oV1Rn01JYHrYg4dauAKWXfu63uWB4CDE6fR0bq4O.c2wJxr.', 1),
(2, 'Sri Bhavana', 'sribhavana3125@gmail.com', '+91 98765 43210', '$2y$10$GfCR5oV1Rn01JYHrYg4dauAKWXfu63uWB4CDE6fR0bq4O.c2wJxr.', 0)
ON DUPLICATE KEY UPDATE is_admin = VALUES(is_admin);

-- Default Settings
INSERT INTO settings (setting_key, setting_value) VALUES
('store_name', 'ALFARAS'),
('store_contact_phone', '+965 9876 5432'),
('support_email', 'support@alfaras.in'),
('company_name', 'ALFARAS Perfumes Ltd.'),
('company_address', 'Kuwait City, Kuwait'),
('gst_number', ''),
('currency', 'KWD'),
('shipping_fee_default', '2000'),
('free_shipping_threshold', '15000'),
('cod_fee', '1000'),
('payment_methods_enabled', 'cod,card'),
('whatsapp_number', '918015038981'),
('whatsapp_custom_message_template', 'Dear ALFARAS, I would like to acquire Order {ORDER_CODE} for {TOTAL} KWD.'),
('brevo_sender_email', 'treasury@alfaras.in'),
('brevo_sender_name', 'ALFARAS Perfumes'),
('invoice_footer', 'Thank you for choosing the Royal House of ALFARAS.'),
('razorpay_key_id', 'rzp_test_keyseedid'),
('razorpay_key_secret', 'secretseedkey'),
('notify_new_order', '1'),
('notify_order_shipped', '1'),
('notify_low_stock', '1'),
('notify_contact_form', '1')
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value);

-- Default Collections
INSERT INTO collections (id, slug, name, description, banner) VALUES
(1, 'oud', 'Royal Oud Collection', 'distilled from sovereign agarwood', 'collections/royal-oud.jpg'),
(2, 'rose', 'Rose of Damascus', 'sacred oil from high Taif valleys', 'collections/rose-taif.jpg'),
(3, 'amber', 'Zanzibar Ambergris', 'ancient deposits along the frankincense road', 'collections/ambergris.jpg'),
(4, 'musk', 'Sacred Musk', 'clean, sacred, and eternal', 'collections/musk.jpg'),
(5, 'bakhoor', 'Sultanate Bakhoor', 'traditional ceremony of smoke', 'collections/bakhoor.jpg')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- Default Products
INSERT INTO products (id, name, arabicName, subtitle, chamber, brand, brandId, artifactCode, heritageYear, originRegion, price, mrp, stock, sizes, concentration, rating, reviews, isNew, isLimited, fragranceType, topNotes, heartNotes, baseNotes, description, perfumersChronicle, arabicStory, image, sealColor, is_active, collection_id, scent_family, folder_name, gallery) VALUES
(1, 'Oud Al Muluk', 'عود الملوك', 'Oud of Kings', 'oud', 'ALFARAS', 'alfaras', 'ALF-OUD-001', '743 AH', 'Borneo & Cambodia', 12000, 15000, 35, '[{"ml":15,"price":12000,"mrp":15000,"stock":20,"outOfStock":false},{"ml":30,"price":28000,"mrp":32000,"stock":15,"outOfStock":false},{"ml":50,"price":45000,"mrp":50000,"stock":0,"outOfStock":true}]', 'Pure Attar', 5.0, 312, 0, 0, 'oud', '["Cambodian Oud","Saffron","Smoke"]', '["Royal Musk","Sandalwood","Amber Resin"]', '["Dark Oud","Vetiver","Sacred Incense"]', 'Recovered from the personal vault of a 14th-century Arabian sultan, Oud Al Muluk is the darkest, most sovereign wood we have ever distilled. Worn only by kings.', 'In the year 743 AH, a Yemeni perfumer named Ibn Rashid discovered a grove of infected Aquilaria trees in the mountains of Dhofar. From their resin, he distilled what courts would call the scent of divine authority. This formula has never been altered.', 'في عام ٧٤٣ للهجرة، اكتشف عطار يمني يدعى ابن راشد بستاناً من أشجار العقيلاريا المصابة في جبال ظفار.', 'https://images.unsplash.com/photo-1540555700478-4be289fbecef?w=600&h=800&fit=crop', '#C49B3A', 1, 'oud', 'Oud, Woody, Oriental', 'oud_al_muluk', '[]'),
(2, 'Oud Al Layl', 'عود الليل', 'Oud of the Night', 'oud', 'ALFARAS', 'alfaras', 'ALF-OUD-002', '891 AH', 'India', 8000, 10000, 35, '[{"ml":15,"price":8000,"mrp":10000,"stock":20,"outOfStock":false},{"ml":30,"price":18500,"mrp":20000,"stock":15,"outOfStock":false},{"ml":50,"price":32000,"mrp":35000,"stock":0,"outOfStock":true}]', 'Pure Attar', 4.9, 187, 0, 0, 'oud', '["Indian Oud","Cardamom","Clove"]', '["Dark Rose","Amber","Jasmine"]', '["Sandalwood","Musk","Incense"]', 'A nocturnal composition. Oud Al Layl was formulated for moonlit ceremonies in the Mughal court, where incense burned through the hours of darkness.', 'This formula was carried from Delhi to Mecca in sealed copper vessels during the pilgrimage of 891 AH. The perfumer who created it never revealed his name.', 'حُمل هذا العطر من دلهي إلى مكة المكرمة في أوعية نحاسية مختومة خلال موسم الحج عام ٨٩١ هجرية.', 'https://images.unsplash.com/photo-1547887538-e3a2f32cb1cc?w=600&h=800&fit=crop', '#C49B3A', 1, 'oud', 'Oud, Spicy, Floral', 'oud_al_layl', '[]'),
(3, 'Oud Al Sultani', 'عود السلطاني', 'The Sultan\'s Oud', 'oud', 'ALFARAS', 'alfaras', 'ALF-OUD-003', '1102 AH', 'Brunei', 25000, 28000, 0, '[{"ml":12,"price":25000,"mrp":28000,"stock":0,"outOfStock":true},{"ml":30,"price":52000,"mrp":55000,"stock":0,"outOfStock":true}]', 'Royal Attar', 5.0, 48, 1, 1, 'oud', '["Brunei Oud","Rose Absolute","Saffron"]', '["Ambergris","Civet","Frankincense"]', '["Oud Resin","Dark Amber","Royal Musk"]', 'The rarest formula in the ALFARAS treasury. Oud Al Sultani was presented to the Ottoman Sultan Suleiman as a diplomatic gift in 1102 AH. Only 12 bottles exist.', 'Gifted by the Omani ambassador to Constantinople, this singular composition caused the Sultan to declare: "This is not perfume — this is sovereignty made visible."', 'قدّمه السفير العماني هدية دبلوماسية للسلطان سليمان في القسطنطينية، فأعلن السلطان: "هذا ليس عطراً - بل هو السيادة مرئيةً."', 'https://images.unsplash.com/photo-1594938298603-c8148c4b4769?w=600&h=800&fit=crop', '#C49B3A', 1, 'oud', 'Oud, Royal, Ambery', 'oud_al_sultani', '[]'),
(4, 'Ward Al Taif', 'ورد الطائف', 'Rose of Taif', 'rose', 'ALFARAS', 'alfaras', 'ALF-WRD-001', '820 AH', 'Taif, Arabia', 8000, 9000, 118, '[{"ml":20,"price":8000,"mrp":9000,"stock":18,"outOfStock":false},{"ml":50,"price":15800,"mrp":17000,"stock":50,"outOfStock":false},{"ml":100,"price":28000,"mrp":30000,"stock":50,"outOfStock":false}]', 'EDP', 4.9, 423, 0, 0, 'rose', '["Taif Rose","Saffron","Bergamot"]', '["Damask Rose","Oud","Sandalwood"]', '["Musk","Amber","Vanilla"]', 'Grown in the mountain gardens of Taif at 1800 meters above sea level, where roses have bloomed since the time of the Prophet. The most sacred rose in Arabia.', 'The Taif rose blooms for only 3 weeks each year, at dawn, before the desert sun can claim its oils. Our farmers have harvested this garden since 820 AH.', 'تتفتح وردة الطائف لثلاثة أسابيع فقط كل عام، عند الفجر، قبل أن تستولي شمس الصحراء على زيوتها.', 'https://images.unsplash.com/photo-1518709268805-4e9042af9f23?w=600&h=800&fit=crop', '#8B2252', 1, 'rose', 'Rose, Taif, Floral', 'ward_al_taif', '[]'),
(5, 'Qamar Al Ward', 'قمر الورد', 'Moon Rose', 'rose', 'ALFARAS', 'alfaras', 'ALF-WRD-002', '956 AH', 'Persia & Arabia', 7500, 8000, 160, '[{"ml":30,"price":7500,"mrp":8000,"stock":60,"outOfStock":false},{"ml":50,"price":12500,"mrp":14000,"stock":50,"outOfStock":false},{"ml":100,"price":22000,"mrp":25000,"stock":50,"outOfStock":false}]', 'EDP', 4.8, 289, 1, 0, 'rose', '["Persian Rose","Jasmine","Neroli"]', '["Taif Rose","Iris","Musk"]', '["White Amber","Sandalwood","Vetiver"]', 'A composition conceived for moonlit garden ceremonies in the Persian court. Qamar Al Ward is worn by women at dawn and dusk, never in daylight.', 'A Persian court poet described this scent as "the garden after the moon has kissed it." The formula was smuggled from Isfahan to Mecca in a scholar\'s robes.', 'وصف شاعر فارسي هذا العطر بأنه "الحديقة بعد أن قبّلها القمر". نُقلت الوصفة من أصفهان إلى مكة المكرمة في أردية عالم.', 'https://images.unsplash.com/photo-1587017539504-67cfbddac569?w=600&h=800&fit=crop', '#8B2252', 1, 'rose', 'Rose, Fresh, Musky', 'qamar_al_ward', '[]'),
(6, 'Anbar Al Aswad', 'عنبر الأسود', 'Black Amber', 'amber', 'ALFARAS', 'alfaras', 'ALF-ANB-001', '788 AH', 'Zanzibar & Yemen', 10000, 12000, 0, '[{"ml":20,"price":10000,"mrp":12000,"stock":0,"outOfStock":true},{"ml":50,"price":22000,"mrp":25000,"stock":0,"outOfStock":true},{"ml":100,"price":38000,"mrp":40000,"stock":0,"outOfStock":true}]', 'Extrait', 4.9, 156, 0, 0, 'amber', '["Black Pepper","Cardamom","Saffron"]', '["Labdanum","Ambergris","Oud"]', '["Dark Amber","Musk","Frankincense"]', 'Anbar Al Aswad is the darkest amber in the ALFARAS treasury, sourced from ancient resin deposits along the frankincense road between Zanzibar and the Yemeni coast.', 'Amber was once weighed against gold in the spice markets of Aden. ALFARAS acquired this ancient stockpile from a merchant family who had kept it sealed since 788 AH.', 'كان العنبر يوازن الذهب وزناً في أسواق التوابل في عدن. استحوذت الفرس على هذا المخزون العتيق من عائلة تجار احتفظت به مختوماً منذ ٧٨٨ هجرية.', 'https://images.unsplash.com/photo-1608248543803-ba4f8c70ae0b?w=600&h=800&fit=crop', '#C49B3A', 1, 'amber', 'Amber, Dark, Smoky', 'anbar_al_aswad', '[]'),
(7, 'Anbar Al Dhahab', 'عنبر الذهب', 'Golden Amber', 'amber', 'ALFARAS', 'alfaras', 'ALF-ANB-002', '1024 AH', 'Oman', 9000, 10000, 160, '[{"ml":30,"price":9000,"mrp":10000,"stock":60,"outOfStock":false},{"ml":50,"price":16500,"mrp":18000,"stock":50,"outOfStock":false},{"ml":100,"price":30000,"mrp":32000,"stock":50,"outOfStock":false}]', 'Extrait', 4.8, 201, 0, 0, 'amber', '["Frankincense","Bergamot","Ginger"]', '["Amber Resin","Benzoin","Beeswax"]', '["Sandalwood","Vanilla","Musk"]', 'Golden, warm, and ancient. Anbar Al Dhahab evokes the warmth of a desert at sunset, where caravans once rested and merchants traded in secrets.', 'The Omani formula masters who blended this composition were known as "the alchemists of scent." They believed amber was sunlight captured in earth over millennia.', 'اعتقد أساتذة التركيب العمانيون أن العنبر هو ضوء الشمس محبوس في الأرض على مدى آلاف السنين.', 'https://images.unsplash.com/photo-1619994403073-2cec844b8e63?w=600&h=800&fit=crop', '#C49B3A', 1, 'amber', 'Amber, Golden, Sweet', 'anbar_al_dhahab', '[]'),
(8, 'Misk Al Abyad', 'مسك الأبيض', 'White Musk', 'musk', 'ALFARAS', 'alfaras', 'ALF-MSK-001', '902 AH', 'Arabian Peninsula', 5500, 6000, 160, '[{"ml":30,"price":5500,"mrp":6000,"stock":60,"outOfStock":false},{"ml":50,"price":9800,"mrp":11000,"stock":50,"outOfStock":false},{"ml":100,"price":17500,"mrp":19000,"stock":50,"outOfStock":false}]', 'EDP', 4.7, 534, 0, 0, 'musk', '["White Flowers","Bergamot","Light Aldehydes"]', '["White Musk","Iris","Rose"]', '["Sandalwood","Amber","Vanilla"]', 'The Prophet declared musk to be the finest of all fragrances. Misk Al Abyad is ALFARAS\'s interpretation of pure divine refinement — clean, sacred, and eternal.', 'Worn by scholars and saints throughout history. This musk formula was transcribed in a medieval manuscript found in the library of Córdoba.', 'ارتداه العلماء والأولياء عبر التاريخ. تمت كتابة هذه الوصفة في مخطوطة من القرون الوسطى وُجدت في مكتبة قرطبة.', 'https://images.unsplash.com/photo-1523293182086-7651a899d37f?w=600&h=800&fit=crop', '#7B7BAA', 1, 'musk', 'Musk, Powdery, Fresh', 'misk_al_abyad', '[]'),
(9, 'Misk Al Aswad', 'مسك الأسود', 'Black Musk', 'musk', 'ALFARAS', 'alfaras', 'ALF-MSK-002', '1150 AH', 'Harar, Africa', 7000, 8000, 0, '[{"ml":15,"price":7000,"mrp":8000,"stock":0,"outOfStock":true},{"ml":30,"price":13500,"mrp":15000,"stock":0,"outOfStock":true},{"ml":50,"price":22000,"mrp":24000,"stock":0,"outOfStock":true}]', 'Extrait', 4.9, 178, 1, 0, 'musk', '["Black Pepper","Smoky Incense","Cardamom"]', '["Black Musk","Oud","Dark Amber"]', '["Vetiver","Labdanum","Smoke"]', 'Dark, mysterious, and commanding. Black Musk was the chosen scent of African sultans who ruled the incense trade routes. It announces presence before words are spoken.', 'Sourced from the ancient perfume markets of Harar, where traders gathered from three continents. This musk has been worn by rulers of lands now forgotten.', 'مصدره من أسواق العطور القديمة in هرر، حيث كان التجار يجتمعون من ثلاث قارات.', 'https://images.unsplash.com/photo-1592945403244-b3fbafd7f539?w=600&h=800&fit=crop', '#7B7BAA', 1, 'musk', 'Musk, Bold, Animalic', 'misk_al_aswad', '[]'),
(10, 'Bakhoor Al Sultani', 'بخور السلطاني', 'The Sultan\'s Bakhoor', 'bakhoor', 'ALFARAS', 'alfaras', 'ALF-BKH-001', '1089 AH', 'Oman & India', 4500, 5000, 110, '[{"ml":100,"price":4500,"mrp":5000,"stock":50,"outOfStock":false},{"ml":200,"price":8500,"mrp":9500,"stock":60,"outOfStock":false}]', 'Bakhoor', 5.0, 672, 0, 0, 'bakhoor', '["Sandalwood Chips","Oud Wood","Rose Petals"]', '["Frankincense","Benzoin","Ambergris"]', '["Musk","Vanilla","Dark Resin"]', 'Bakhoor is not a perfume — it is a ceremony. Burned in the courts of sultans to consecrate gatherings and honor sacred occasions. The smoke carries prayers upward.', 'This formula was used to perfume the royal court of Muscat before every audience with the Imam. The recipe was sealed in wax and locked in a silver box for 300 years.', 'استُخدمت هذه الوصفة لتعطير البلاط الملكي في مسقط قبل كل مثول أمام الإمام. أُحكم إغلاق الوصفة بالشمع وقُفلت في صندوق فضي لمدة ٣٠٠ عام.', 'https://images.unsplash.com/photo-1559056199-641a0ac8b55e?w=600&h=800&fit=crop', '#6B5A2D', 1, 'bakhoor', 'Woody, Smoky, Traditional', 'bakhoor_al_sultani', '[]')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- Default Coupons
INSERT INTO coupons (id, code, discount_type, discount_value, min_cart_amount, expiry_date, usage_limit, used_count, is_active) VALUES
(1, 'ALFARAS10', 'percentage', 10.00, 5000.00, '2027-12-31', 1000, 12, 1),
(2, 'ROYAL5', 'flat', 5000.00, 20000.00, '2027-12-31', 500, 4, 1)
ON DUPLICATE KEY UPDATE code = VALUES(code);

-- Seed Mock Orders spanning the last 7 days for sales chart
INSERT INTO orders (id, order_code, order_id, verification_token_hash, user_id, customer_name, phone, customer_email, address, city, state, pincode, payment_method, subtotal, shipping, cod_fee, total, item_count, status, email_status, sales_channel, bill_reference, sale_date, created_at) VALUES
(1, 'ALF08425771', 'pay_mock1', NULL, 2, 'Sri Bhavana', '+919876543210', 'sribhavana3125@gmail.com', '123 Royal Palace Road', 'Kuwait City', 'Capital', '12345', 'Card', 12000, 2000, 0, 14000, 1, 'Confirmed', 'SENT', 'online', NULL, DATE_SUB(NOW(), INTERVAL 6 DAY), DATE_SUB(NOW(), INTERVAL 6 DAY)),
(2, 'ALF08425772', 'pay_mock2', NULL, 2, 'Sri Bhavana', '+919876543210', 'sribhavana3125@gmail.com', '123 Royal Palace Road', 'Kuwait City', 'Capital', '12345', 'Card', 25000, 3000, 0, 28000, 1, 'Paid', 'SENT', 'online', NULL, DATE_SUB(NOW(), INTERVAL 4 DAY), DATE_SUB(NOW(), INTERVAL 4 DAY)),
(3, 'ALF08425773', NULL, NULL, NULL, 'Walkin Guest', '+96511223344', 'walkin@example.com', 'Offline Store', 'Kuwait', 'Kuwait', '00000', 'Cash', 18500, 0, 0, 18500, 1, 'Paid', 'SENT', 'offline', 'BILL-992', DATE_SUB(NOW(), INTERVAL 3 DAY), DATE_SUB(NOW(), INTERVAL 3 DAY)),
(4, 'ALF08425774', 'pay_mock4', NULL, 2, 'Sri Bhavana', '+919876543210', 'sribhavana3125@gmail.com', '123 Royal Palace Road', 'Kuwait City', 'Capital', '12345', 'Card', 45000, 0, 0, 45000, 1, 'Shipped', 'PENDING', 'online', NULL, DATE_SUB(NOW(), INTERVAL 2 DAY), DATE_SUB(NOW(), INTERVAL 2 DAY)),
(5, 'ALF08425775', 'pay_mock5', NULL, 2, 'Sri Bhavana', '+919876543210', 'sribhavana3125@gmail.com', '123 Royal Palace Road', 'Kuwait City', 'Capital', '12345', 'Card', 8000, 0, 0, 8000, 1, 'Delivered', 'SENT', 'online', NULL, DATE_SUB(NOW(), INTERVAL 1 DAY), DATE_SUB(NOW(), INTERVAL 1 DAY)),
(6, 'ALF08425776', NULL, NULL, NULL, 'Walkin VIP', '+96599887766', 'vip@example.com', 'Offline Store', 'Kuwait', 'Kuwait', '00000', 'Knet', 12000, 0, 0, 12000, 1, 'Paid', 'NOT_SENT', 'offline', 'BILL-995', NOW(), NOW())
ON DUPLICATE KEY UPDATE order_code = VALUES(order_code);

-- Seed Order Items
INSERT INTO order_items (id, order_id, product_id, product_name, size_ml, price, quantity) VALUES
(1, 1, 1, 'Oud Al Muluk', 15, 12000, 1),
(2, 2, 3, 'Oud Al Sultani', 12, 25000, 1),
(3, 3, 2, 'Oud Al Layl', 30, 18500, 1),
(4, 4, 1, 'Oud Al Muluk', 50, 45000, 1),
(5, 5, 2, 'Oud Al Layl', 15, 8000, 1),
(6, 6, 1, 'Oud Al Muluk', 15, 12000, 1)
ON DUPLICATE KEY UPDATE product_name = VALUES(product_name);

-- Seed Verification Logs
INSERT INTO verification_logs (id, order_code, is_verified, differences, created_at) VALUES
(1, 'ALF08425771', 1, NULL, DATE_SUB(NOW(), INTERVAL 5 DAY)),
(2, 'ALF08425772', 1, NULL, DATE_SUB(NOW(), INTERVAL 3 DAY)),
(3, 'ALF08425774', 0, 'Total mismatch: expected 45000 fils, got 42000 fils', DATE_SUB(NOW(), INTERVAL 2 DAY)),
(4, 'ALF08425775', 1, NULL, DATE_SUB(NOW(), INTERVAL 1 DAY))
ON DUPLICATE KEY UPDATE order_code = VALUES(order_code);