-- LOVE-U Essentials Database Backup
-- Generated: 2026-05-19 09:46:37

SET FOREIGN_KEY_CHECKS=0;


-- Table structure for `abandoned_cart_items`
DROP TABLE IF EXISTS `abandoned_cart_items`;
CREATE TABLE `abandoned_cart_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `abandoned_cart_id` int NOT NULL,
  `product_id` int DEFAULT NULL,
  `product_name` varchar(200) NOT NULL,
  `quantity` int NOT NULL,
  `unit_price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `abandoned_cart_id` (`abandoned_cart_id`),
  KEY `product_id` (`product_id`),
  CONSTRAINT `abandoned_cart_items_ibfk_1` FOREIGN KEY (`abandoned_cart_id`) REFERENCES `abandoned_carts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `abandoned_cart_items_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data for `abandoned_cart_items`


-- Table structure for `abandoned_carts`
DROP TABLE IF EXISTS `abandoned_carts`;
CREATE TABLE `abandoned_carts` (
  `id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int DEFAULT NULL,
  `session_id` varchar(255) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  `cart_total` decimal(10,2) DEFAULT '0.00',
  `recovery_token` varchar(255) DEFAULT NULL,
  `email_sent_count` int DEFAULT '0',
  `last_email_sent_at` datetime DEFAULT NULL,
  `status` enum('pending','recovered','expired') DEFAULT 'pending',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `customer_id` (`customer_id`),
  CONSTRAINT `abandoned_carts_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data for `abandoned_carts`


-- Table structure for `admin_activity_logs`
DROP TABLE IF EXISTS `admin_activity_logs`;
CREATE TABLE `admin_activity_logs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `admin_id` int DEFAULT NULL,
  `action` varchar(180) NOT NULL,
  `description` text,
  `ip_address` varchar(100) DEFAULT NULL,
  `user_agent` text,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `admin_id` (`admin_id`),
  CONSTRAINT `admin_activity_logs_ibfk_1` FOREIGN KEY (`admin_id`) REFERENCES `admin_users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data for `admin_activity_logs`


-- Table structure for `admin_users`
DROP TABLE IF EXISTS `admin_users`;
CREATE TABLE `admin_users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `role_id` int DEFAULT NULL,
  `full_name` varchar(150) NOT NULL,
  `email` varchar(150) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `avatar` varchar(255) DEFAULT NULL,
  `two_factor_enabled` tinyint(1) DEFAULT '0',
  `two_factor_secret` varchar(255) DEFAULT NULL,
  `status` enum('active','inactive','blocked') DEFAULT 'active',
  `last_login_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  KEY `role_id` (`role_id`),
  CONSTRAINT `admin_users_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data for `admin_users`
INSERT INTO `admin_users` (`id`, `role_id`, `full_name`, `email`, `password_hash`, `phone`, `avatar`, `two_factor_enabled`, `two_factor_secret`, `status`, `last_login_at`, `created_at`, `updated_at`) VALUES ('1', '1', 'Pasan Udawatte', 'admin@loveuessentials.com', '$2y$10$NuLEwrN8WHllrr.cZ.Pn7O1uQGWWgIjYhkoE7Ad90JnEFu1IL697m', NULL, NULL, '0', NULL, 'active', '2026-05-19 10:24:06', '2026-05-19 10:16:59', '2026-05-19 10:24:06');


-- Table structure for `backups`
DROP TABLE IF EXISTS `backups`;
CREATE TABLE `backups` (
  `id` int NOT NULL AUTO_INCREMENT,
  `backup_name` varchar(180) NOT NULL,
  `backup_path` varchar(255) NOT NULL,
  `backup_type` enum('database','files','full') DEFAULT 'database',
  `created_by` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  CONSTRAINT `backups_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `admin_users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data for `backups`


-- Table structure for `banners`
DROP TABLE IF EXISTS `banners`;
CREATE TABLE `banners` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(180) NOT NULL,
  `subtitle` varchar(255) DEFAULT NULL,
  `image_path` varchar(255) NOT NULL,
  `button_text` varchar(100) DEFAULT NULL,
  `button_link` varchar(255) DEFAULT NULL,
  `placement` enum('home_hero','home_promo','category','popup') DEFAULT 'home_hero',
  `sort_order` int DEFAULT '0',
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data for `banners`


-- Table structure for `cart_items`
DROP TABLE IF EXISTS `cart_items`;
CREATE TABLE `cart_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `cart_id` int NOT NULL,
  `product_id` int NOT NULL,
  `variation_id` int DEFAULT NULL,
  `quantity` int NOT NULL DEFAULT '1',
  `unit_price` decimal(10,2) NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `cart_id` (`cart_id`),
  KEY `product_id` (`product_id`),
  KEY `variation_id` (`variation_id`),
  CONSTRAINT `cart_items_ibfk_1` FOREIGN KEY (`cart_id`) REFERENCES `carts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `cart_items_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
  CONSTRAINT `cart_items_ibfk_3` FOREIGN KEY (`variation_id`) REFERENCES `product_variations` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data for `cart_items`


-- Table structure for `carts`
DROP TABLE IF EXISTS `carts`;
CREATE TABLE `carts` (
  `id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int DEFAULT NULL,
  `session_id` varchar(255) DEFAULT NULL,
  `status` enum('active','converted','abandoned') DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_carts_customer` (`customer_id`),
  CONSTRAINT `carts_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data for `carts`


-- Table structure for `categories`
DROP TABLE IF EXISTS `categories`;
CREATE TABLE `categories` (
  `id` int NOT NULL AUTO_INCREMENT,
  `parent_id` int DEFAULT NULL,
  `name` varchar(150) NOT NULL,
  `slug` varchar(180) NOT NULL,
  `description` text,
  `image` varchar(255) DEFAULT NULL,
  `seo_title` varchar(180) DEFAULT NULL,
  `seo_description` varchar(255) DEFAULT NULL,
  `sort_order` int DEFAULT '0',
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`),
  KEY `parent_id` (`parent_id`),
  CONSTRAINT `categories_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data for `categories`
INSERT INTO `categories` (`id`, `parent_id`, `name`, `slug`, `description`, `image`, `seo_title`, `seo_description`, `sort_order`, `status`, `created_at`, `updated_at`) VALUES ('1', NULL, 'Body Care', 'body-care', '', NULL, 'Body Care | LOVE-U Essentials', 'Shop premium body care essentials from LOVE-U Essentials with secure checkout and smooth delivery.', '0', 'active', '2026-05-19 10:46:26', NULL);
INSERT INTO `categories` (`id`, `parent_id`, `name`, `slug`, `description`, `image`, `seo_title`, `seo_description`, `sort_order`, `status`, `created_at`, `updated_at`) VALUES ('2', NULL, 'Fragrance', 'fragrance', '', NULL, 'Fragrance | LOVE-U Essentials', 'Shop premium fragrances from LOVE-U Essentials with secure checkout and smooth delivery.', '0', 'active', '2026-05-19 10:47:13', NULL);
INSERT INTO `categories` (`id`, `parent_id`, `name`, `slug`, `description`, `image`, `seo_title`, `seo_description`, `sort_order`, `status`, `created_at`, `updated_at`) VALUES ('3', NULL, 'Gift Sets', 'gift-sets', '', NULL, 'Gift Sets | LOVE-U Essentials', 'Shop premium Gift Sets from LOVE-U Essentials with secure checkout and smooth delivery.', '0', 'active', '2026-05-19 10:47:42', NULL);
INSERT INTO `categories` (`id`, `parent_id`, `name`, `slug`, `description`, `image`, `seo_title`, `seo_description`, `sort_order`, `status`, `created_at`, `updated_at`) VALUES ('4', NULL, 'Self Care', 'self-care', '', NULL, 'Self Care | LOVE-U Essentials', 'Shop premium self care essentials from LOVE-U Essentials with secure checkout and smooth delivery.', '0', 'active', '2026-05-19 10:48:01', NULL);
INSERT INTO `categories` (`id`, `parent_id`, `name`, `slug`, `description`, `image`, `seo_title`, `seo_description`, `sort_order`, `status`, `created_at`, `updated_at`) VALUES ('5', NULL, 'New Arrivals', 'new-arrivals', '', NULL, 'New Arrivals | LOVE-U Essentials', 'Shop new arrivals from LOVE-U Essentials with secure checkout and smooth delivery.', '0', 'active', '2026-05-19 10:48:27', NULL);
INSERT INTO `categories` (`id`, `parent_id`, `name`, `slug`, `description`, `image`, `seo_title`, `seo_description`, `sort_order`, `status`, `created_at`, `updated_at`) VALUES ('6', NULL, 'Best Sellers', 'best-sellers', '', NULL, 'Best Sellers | LOVE-U Essentials', 'Shop best sellers from LOVE-U Essentials with secure checkout and smooth delivery.', '0', 'active', '2026-05-19 10:48:48', NULL);


-- Table structure for `coupon_usages`
DROP TABLE IF EXISTS `coupon_usages`;
CREATE TABLE `coupon_usages` (
  `id` int NOT NULL AUTO_INCREMENT,
  `coupon_id` int NOT NULL,
  `customer_id` int DEFAULT NULL,
  `order_id` int DEFAULT NULL,
  `used_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `coupon_id` (`coupon_id`),
  KEY `customer_id` (`customer_id`),
  CONSTRAINT `coupon_usages_ibfk_1` FOREIGN KEY (`coupon_id`) REFERENCES `coupons` (`id`) ON DELETE CASCADE,
  CONSTRAINT `coupon_usages_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data for `coupon_usages`


-- Table structure for `coupons`
DROP TABLE IF EXISTS `coupons`;
CREATE TABLE `coupons` (
  `id` int NOT NULL AUTO_INCREMENT,
  `coupon_code` varchar(80) NOT NULL,
  `coupon_type` enum('percentage','fixed') NOT NULL,
  `coupon_value` decimal(10,2) NOT NULL,
  `min_order_amount` decimal(10,2) DEFAULT '0.00',
  `max_discount_amount` decimal(10,2) DEFAULT NULL,
  `usage_limit` int DEFAULT NULL,
  `used_count` int DEFAULT '0',
  `starts_at` datetime DEFAULT NULL,
  `expires_at` datetime DEFAULT NULL,
  `status` enum('active','inactive','expired') DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `coupon_code` (`coupon_code`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data for `coupons`
INSERT INTO `coupons` (`id`, `coupon_code`, `coupon_type`, `coupon_value`, `min_order_amount`, `max_discount_amount`, `usage_limit`, `used_count`, `starts_at`, `expires_at`, `status`, `created_at`) VALUES ('1', 'WELCOME10', 'percentage', '9.98', '0.00', NULL, NULL, '0', NULL, NULL, 'active', '2026-05-19 12:45:31');


-- Table structure for `currencies`
DROP TABLE IF EXISTS `currencies`;
CREATE TABLE `currencies` (
  `id` int NOT NULL AUTO_INCREMENT,
  `currency_code` varchar(10) NOT NULL,
  `currency_name` varchar(100) NOT NULL,
  `symbol` varchar(10) NOT NULL,
  `exchange_rate` decimal(12,6) DEFAULT '1.000000',
  `is_default` tinyint(1) DEFAULT '0',
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `currency_code` (`currency_code`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data for `currencies`
INSERT INTO `currencies` (`id`, `currency_code`, `currency_name`, `symbol`, `exchange_rate`, `is_default`, `status`, `created_at`) VALUES ('1', 'GBP', 'British Pound', '£', '1.000000', '1', 'active', '2026-05-19 10:09:46');
INSERT INTO `currencies` (`id`, `currency_code`, `currency_name`, `symbol`, `exchange_rate`, `is_default`, `status`, `created_at`) VALUES ('2', 'USD', 'US Dollar', '$', '1.270000', '0', 'active', '2026-05-19 10:09:46');
INSERT INTO `currencies` (`id`, `currency_code`, `currency_name`, `symbol`, `exchange_rate`, `is_default`, `status`, `created_at`) VALUES ('3', 'XCD', 'Eastern Caribbean Dollar', 'EC$', '3.430000', '0', 'active', '2026-05-19 10:09:46');
INSERT INTO `currencies` (`id`, `currency_code`, `currency_name`, `symbol`, `exchange_rate`, `is_default`, `status`, `created_at`) VALUES ('4', 'LKR', 'Sri Lankan Rupee', 'Rs.', '390.000000', '0', 'inactive', '2026-05-19 10:09:46');


-- Table structure for `customer_addresses`
DROP TABLE IF EXISTS `customer_addresses`;
CREATE TABLE `customer_addresses` (
  `id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int NOT NULL,
  `address_type` enum('billing','shipping') DEFAULT 'shipping',
  `full_name` varchar(150) NOT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `address_line_1` varchar(255) NOT NULL,
  `address_line_2` varchar(255) DEFAULT NULL,
  `city` varchar(100) NOT NULL,
  `state_region` varchar(100) DEFAULT NULL,
  `postal_code` varchar(50) DEFAULT NULL,
  `country` varchar(100) NOT NULL,
  `is_default` tinyint(1) DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `customer_id` (`customer_id`),
  CONSTRAINT `customer_addresses_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data for `customer_addresses`


-- Table structure for `customer_loyalty`
DROP TABLE IF EXISTS `customer_loyalty`;
CREATE TABLE `customer_loyalty` (
  `id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int NOT NULL,
  `points_balance` int DEFAULT '0',
  `lifetime_points` int DEFAULT '0',
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `customer_id` (`customer_id`),
  CONSTRAINT `customer_loyalty_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data for `customer_loyalty`
INSERT INTO `customer_loyalty` (`id`, `customer_id`, `points_balance`, `lifetime_points`, `updated_at`) VALUES ('1', '1', '29', '29', '2026-05-19 12:02:57');


-- Table structure for `customers`
DROP TABLE IF EXISTS `customers`;
CREATE TABLE `customers` (
  `id` int NOT NULL AUTO_INCREMENT,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) DEFAULT NULL,
  `email` varchar(150) NOT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `password_hash` varchar(255) DEFAULT NULL,
  `google_id` varchar(255) DEFAULT NULL,
  `avatar` varchar(255) DEFAULT NULL,
  `referral_code` varchar(50) DEFAULT NULL,
  `referred_by_customer_id` int DEFAULT NULL,
  `two_factor_enabled` tinyint(1) DEFAULT '0',
  `two_factor_secret` varchar(255) DEFAULT NULL,
  `email_verified_at` datetime DEFAULT NULL,
  `status` enum('active','inactive','blocked') DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `referral_code` (`referral_code`),
  KEY `referred_by_customer_id` (`referred_by_customer_id`),
  CONSTRAINT `customers_ibfk_1` FOREIGN KEY (`referred_by_customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data for `customers`
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `email`, `phone`, `password_hash`, `google_id`, `avatar`, `referral_code`, `referred_by_customer_id`, `two_factor_enabled`, `two_factor_secret`, `email_verified_at`, `status`, `created_at`, `updated_at`) VALUES ('1', 'Pasan', 'Udawatte', 'ayodigital.management@gmail.com', '0781657927', '$2y$10$AvxhFleohbVxj0cgBKi.B.ZHex51i9EH7o6tRfnR7aTUAJCpialVG', NULL, NULL, 'PASAN666601', NULL, '0', NULL, NULL, 'active', '2026-05-19 11:28:12', NULL);


-- Table structure for `email_logs`
DROP TABLE IF EXISTS `email_logs`;
CREATE TABLE `email_logs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int DEFAULT NULL,
  `order_id` int DEFAULT NULL,
  `email_to` varchar(150) NOT NULL,
  `subject` varchar(200) NOT NULL,
  `status` enum('pending','sent','failed') DEFAULT 'pending',
  `error_message` text,
  `sent_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `customer_id` (`customer_id`),
  KEY `order_id` (`order_id`),
  CONSTRAINT `email_logs_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL,
  CONSTRAINT `email_logs_ibfk_2` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data for `email_logs`


-- Table structure for `email_templates`
DROP TABLE IF EXISTS `email_templates`;
CREATE TABLE `email_templates` (
  `id` int NOT NULL AUTO_INCREMENT,
  `template_key` varchar(100) NOT NULL,
  `subject` varchar(200) NOT NULL,
  `body` longtext NOT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `template_key` (`template_key`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data for `email_templates`
INSERT INTO `email_templates` (`id`, `template_key`, `subject`, `body`, `status`, `updated_at`) VALUES ('1', 'order_confirmation', 'Your LOVE-U Essentials order confirmation', 'Thank you for your order. We are preparing it now.', 'active', NULL);
INSERT INTO `email_templates` (`id`, `template_key`, `subject`, `body`, `status`, `updated_at`) VALUES ('2', 'order_status_update', 'Your LOVE-U Essentials order has been updated', 'Your order status has been updated.', 'active', NULL);
INSERT INTO `email_templates` (`id`, `template_key`, `subject`, `body`, `status`, `updated_at`) VALUES ('3', 'abandoned_cart', 'You left something you love behind', 'Complete your order before your cart expires.', 'active', NULL);
INSERT INTO `email_templates` (`id`, `template_key`, `subject`, `body`, `status`, `updated_at`) VALUES ('4', 'welcome_customer', 'Welcome to LOVE-U Essentials', 'Thank you for creating your account.', 'active', NULL);


-- Table structure for `faqs`
DROP TABLE IF EXISTS `faqs`;
CREATE TABLE `faqs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `question` varchar(255) NOT NULL,
  `answer` text NOT NULL,
  `sort_order` int DEFAULT '0',
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data for `faqs`
INSERT INTO `faqs` (`id`, `question`, `answer`, `sort_order`, `status`, `created_at`) VALUES ('1', 'How long does delivery take?', 'Delivery time depends on your selected shipping region and shipping method.', '1', 'active', '2026-05-19 10:09:46');
INSERT INTO `faqs` (`id`, `question`, `answer`, `sort_order`, `status`, `created_at`) VALUES ('2', 'Can I send an order as a gift?', 'Yes, you can select the gift option during checkout where available.', '2', 'active', '2026-05-19 10:09:46');
INSERT INTO `faqs` (`id`, `question`, `answer`, `sort_order`, `status`, `created_at`) VALUES ('3', 'Do you offer refunds or returns?', 'Refund and return eligibility will follow the official return policy.', '3', 'active', '2026-05-19 10:09:46');
INSERT INTO `faqs` (`id`, `question`, `answer`, `sort_order`, `status`, `created_at`) VALUES ('4', 'How do I place an order?', 'Browse our shop, open your preferred product, add it to your cart, and continue to checkout. Once payment gateway integration is completed, you will be able to pay securely online.', '0', 'active', '2026-05-19 12:20:44');
INSERT INTO `faqs` (`id`, `question`, `answer`, `sort_order`, `status`, `created_at`) VALUES ('5', 'Do you offer gift orders?', 'Yes. During checkout, you can select the gift option, add recipient details, and include a personalised gift message.', '5', 'active', '2026-05-19 12:21:11');
INSERT INTO `faqs` (`id`, `question`, `answer`, `sort_order`, `status`, `created_at`) VALUES ('6', 'How do loyalty points work?', 'Loyalty points are added after an order payment is confirmed. The current rule is £1 equals 1 loyalty point.', '4', 'active', '2026-05-19 12:21:30');


-- Table structure for `gift_orders`
DROP TABLE IF EXISTS `gift_orders`;
CREATE TABLE `gift_orders` (
  `id` int NOT NULL AUTO_INCREMENT,
  `order_id` int NOT NULL,
  `recipient_name` varchar(150) NOT NULL,
  `recipient_email` varchar(150) DEFAULT NULL,
  `recipient_phone` varchar(50) DEFAULT NULL,
  `gift_message` text,
  `hide_price` tinyint(1) DEFAULT '1',
  `gift_wrap` tinyint(1) DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `order_id` (`order_id`),
  CONSTRAINT `gift_orders_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data for `gift_orders`


-- Table structure for `loyalty_transactions`
DROP TABLE IF EXISTS `loyalty_transactions`;
CREATE TABLE `loyalty_transactions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int NOT NULL,
  `order_id` int DEFAULT NULL,
  `transaction_type` enum('earn','redeem','adjustment','expired') NOT NULL,
  `points` int NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `customer_id` (`customer_id`),
  KEY `order_id` (`order_id`),
  CONSTRAINT `loyalty_transactions_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `loyalty_transactions_ibfk_2` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data for `loyalty_transactions`
INSERT INTO `loyalty_transactions` (`id`, `customer_id`, `order_id`, `transaction_type`, `points`, `description`, `created_at`) VALUES ('1', '1', NULL, 'earn', '29', 'Points earned from order LUE-20260519-921B5A', '2026-05-19 12:02:57');


-- Table structure for `newsletter_subscribers`
DROP TABLE IF EXISTS `newsletter_subscribers`;
CREATE TABLE `newsletter_subscribers` (
  `id` int NOT NULL AUTO_INCREMENT,
  `email` varchar(150) NOT NULL,
  `first_name` varchar(100) DEFAULT NULL,
  `source` varchar(100) DEFAULT NULL,
  `discount_code` varchar(80) DEFAULT NULL,
  `status` enum('subscribed','unsubscribed') DEFAULT 'subscribed',
  `subscribed_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data for `newsletter_subscribers`


-- Table structure for `order_items`
DROP TABLE IF EXISTS `order_items`;
CREATE TABLE `order_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `order_id` int NOT NULL,
  `product_id` int DEFAULT NULL,
  `variation_id` int DEFAULT NULL,
  `product_name` varchar(200) NOT NULL,
  `sku` varchar(100) DEFAULT NULL,
  `quantity` int NOT NULL,
  `unit_price` decimal(10,2) NOT NULL,
  `total_price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `order_id` (`order_id`),
  KEY `product_id` (`product_id`),
  KEY `variation_id` (`variation_id`),
  CONSTRAINT `order_items_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  CONSTRAINT `order_items_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE SET NULL,
  CONSTRAINT `order_items_ibfk_3` FOREIGN KEY (`variation_id`) REFERENCES `product_variations` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data for `order_items`
INSERT INTO `order_items` (`id`, `order_id`, `product_id`, `variation_id`, `product_name`, `sku`, `quantity`, `unit_price`, `total_price`) VALUES ('1', '1', '1', NULL, 'LOVE-U Signature Body Oil', NULL, '1', '24.99', '24.99');
INSERT INTO `order_items` (`id`, `order_id`, `product_id`, `variation_id`, `product_name`, `sku`, `quantity`, `unit_price`, `total_price`) VALUES ('2', '2', '1', NULL, 'LOVE-U Signature Body Oil', NULL, '1', '24.99', '24.99');


-- Table structure for `orders`
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
  `id` int NOT NULL AUTO_INCREMENT,
  `order_number` varchar(80) NOT NULL,
  `customer_id` int DEFAULT NULL,
  `customer_email` varchar(150) NOT NULL,
  `customer_phone` varchar(50) DEFAULT NULL,
  `billing_address` text NOT NULL,
  `shipping_address` text NOT NULL,
  `subtotal` decimal(10,2) NOT NULL DEFAULT '0.00',
  `discount_amount` decimal(10,2) DEFAULT '0.00',
  `shipping_fee` decimal(10,2) DEFAULT '0.00',
  `tax_amount` decimal(10,2) DEFAULT '0.00',
  `total_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `currency_code` varchar(10) DEFAULT 'GBP',
  `coupon_code` varchar(80) DEFAULT NULL,
  `order_notes` text,
  `is_gift` tinyint(1) DEFAULT '0',
  `payment_status` enum('pending','paid','failed','refunded','cancelled') DEFAULT 'pending',
  `order_status` enum('pending','processing','shipped','completed','cancelled','refunded') DEFAULT 'pending',
  `shipping_status` enum('not_shipped','packed','shipped','delivered') DEFAULT 'not_shipped',
  `tracking_number` varchar(150) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `order_number` (`order_number`),
  KEY `idx_orders_number` (`order_number`),
  KEY `idx_orders_customer` (`customer_id`),
  KEY `idx_orders_status` (`order_status`),
  CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data for `orders`
INSERT INTO `orders` (`id`, `order_number`, `customer_id`, `customer_email`, `customer_phone`, `billing_address`, `shipping_address`, `subtotal`, `discount_amount`, `shipping_fee`, `tax_amount`, `total_amount`, `currency_code`, `coupon_code`, `order_notes`, `is_gift`, `payment_status`, `order_status`, `shipping_status`, `tracking_number`, `created_at`, `updated_at`) VALUES ('1', 'LUE-20260519-3731C9', NULL, 'ayodigital.management@gmail.com', '0781657927', 'AYO Digital
0781657927
374 D
Temple Road
Thalawathugida
Colombo
10116
Sri Lanka', 'AYO Digital
0781657927
374 D
Temple Road
Thalawathugoda
Colombo
10116
Sri Lanka', '24.99', '0.00', '4.99', '0.00', '29.98', 'GBP', NULL, '', '0', 'paid', 'processing', 'packed', 'TEST12345', '2026-05-19 11:13:58', '2026-05-19 11:19:45');
INSERT INTO `orders` (`id`, `order_number`, `customer_id`, `customer_email`, `customer_phone`, `billing_address`, `shipping_address`, `subtotal`, `discount_amount`, `shipping_fee`, `tax_amount`, `total_amount`, `currency_code`, `coupon_code`, `order_notes`, `is_gift`, `payment_status`, `order_status`, `shipping_status`, `tracking_number`, `created_at`, `updated_at`) VALUES ('2', 'LUE-20260519-921B5A', '1', 'ayodigital.management@gmail.com', '0781657927', 'Pasan Udawatte
0781657927
374 D
Temple Road
Thalawathugoda
Colombo
10116
Sri Lanka', 'Pasan Udawatte
0781657927
374 D
Temple Road
Thalawathugoda
Colombo
10116
Sri Lanka', '24.99', '0.00', '4.99', '0.00', '29.98', 'GBP', NULL, '', '0', 'pending', 'processing', 'packed', NULL, '2026-05-19 11:50:48', '2026-05-19 14:04:17');


-- Table structure for `pages`
DROP TABLE IF EXISTS `pages`;
CREATE TABLE `pages` (
  `id` int NOT NULL AUTO_INCREMENT,
  `page_key` varchar(100) NOT NULL,
  `title` varchar(180) NOT NULL,
  `slug` varchar(180) NOT NULL,
  `content` longtext,
  `seo_title` varchar(180) DEFAULT NULL,
  `seo_description` varchar(255) DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `page_key` (`page_key`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data for `pages`


-- Table structure for `payments`
DROP TABLE IF EXISTS `payments`;
CREATE TABLE `payments` (
  `id` int NOT NULL AUTO_INCREMENT,
  `order_id` int NOT NULL,
  `payment_gateway` enum('paypal','stripe','google_pay','manual') NOT NULL,
  `transaction_id` varchar(255) DEFAULT NULL,
  `payment_reference` varchar(255) DEFAULT NULL,
  `amount` decimal(10,2) NOT NULL,
  `currency_code` varchar(10) DEFAULT 'GBP',
  `status` enum('pending','success','failed','refunded') DEFAULT 'pending',
  `gateway_response` longtext,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `order_id` (`order_id`),
  CONSTRAINT `payments_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data for `payments`
INSERT INTO `payments` (`id`, `order_id`, `payment_gateway`, `transaction_id`, `payment_reference`, `amount`, `currency_code`, `status`, `gateway_response`, `created_at`) VALUES ('1', '1', 'manual', NULL, NULL, '29.98', 'GBP', 'pending', NULL, '2026-05-19 11:13:58');
INSERT INTO `payments` (`id`, `order_id`, `payment_gateway`, `transaction_id`, `payment_reference`, `amount`, `currency_code`, `status`, `gateway_response`, `created_at`) VALUES ('2', '2', 'manual', 'TEST-TXN-001', 'MANUAL-TEST-001', '29.98', 'GBP', 'pending', NULL, '2026-05-19 11:50:48');


-- Table structure for `product_images`
DROP TABLE IF EXISTS `product_images`;
CREATE TABLE `product_images` (
  `id` int NOT NULL AUTO_INCREMENT,
  `product_id` int NOT NULL,
  `image_path` varchar(255) NOT NULL,
  `alt_text` varchar(180) DEFAULT NULL,
  `sort_order` int DEFAULT '0',
  `is_primary` tinyint(1) DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `product_id` (`product_id`),
  CONSTRAINT `product_images_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data for `product_images`
INSERT INTO `product_images` (`id`, `product_id`, `image_path`, `alt_text`, `sort_order`, `is_primary`, `created_at`) VALUES ('1', '1', 'uploads/products/product-1-1779168542-d9505d35.png', 'LOVE-U Signature Body Oil', '0', '1', '2026-05-19 10:59:02');
INSERT INTO `product_images` (`id`, `product_id`, `image_path`, `alt_text`, `sort_order`, `is_primary`, `created_at`) VALUES ('2', '1', 'uploads/products/product-1-1779168542-9bd0a040.png', 'LOVE-U Signature Body Oil', '1', '0', '2026-05-19 10:59:02');
INSERT INTO `product_images` (`id`, `product_id`, `image_path`, `alt_text`, `sort_order`, `is_primary`, `created_at`) VALUES ('3', '1', 'uploads/products/product-1-1779168542-cfb570ab.png', 'LOVE-U Signature Body Oil', '2', '0', '2026-05-19 10:59:02');


-- Table structure for `product_tag_map`
DROP TABLE IF EXISTS `product_tag_map`;
CREATE TABLE `product_tag_map` (
  `product_id` int NOT NULL,
  `tag_id` int NOT NULL,
  PRIMARY KEY (`product_id`,`tag_id`),
  KEY `tag_id` (`tag_id`),
  CONSTRAINT `product_tag_map_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
  CONSTRAINT `product_tag_map_ibfk_2` FOREIGN KEY (`tag_id`) REFERENCES `product_tags` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data for `product_tag_map`


-- Table structure for `product_tags`
DROP TABLE IF EXISTS `product_tags`;
CREATE TABLE `product_tags` (
  `id` int NOT NULL AUTO_INCREMENT,
  `tag_name` varchar(100) NOT NULL,
  `slug` varchar(120) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tag_name` (`tag_name`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data for `product_tags`


-- Table structure for `product_variations`
DROP TABLE IF EXISTS `product_variations`;
CREATE TABLE `product_variations` (
  `id` int NOT NULL AUTO_INCREMENT,
  `product_id` int NOT NULL,
  `variation_name` varchar(120) NOT NULL,
  `variation_value` varchar(150) NOT NULL,
  `sku` varchar(100) DEFAULT NULL,
  `price_adjustment` decimal(10,2) DEFAULT '0.00',
  `stock_quantity` int DEFAULT '0',
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `product_id` (`product_id`),
  CONSTRAINT `product_variations_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data for `product_variations`


-- Table structure for `products`
DROP TABLE IF EXISTS `products`;
CREATE TABLE `products` (
  `id` int NOT NULL AUTO_INCREMENT,
  `category_id` int DEFAULT NULL,
  `product_name` varchar(200) NOT NULL,
  `slug` varchar(220) NOT NULL,
  `short_description` text,
  `description` longtext,
  `sku` varchar(100) DEFAULT NULL,
  `regular_price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `sale_price` decimal(10,2) DEFAULT NULL,
  `currency_code` varchar(10) DEFAULT 'GBP',
  `stock_quantity` int DEFAULT '0',
  `stock_status` enum('in_stock','only_few_left','out_of_stock','back_in_stock') DEFAULT 'in_stock',
  `badge` varchar(80) DEFAULT NULL,
  `is_featured` tinyint(1) DEFAULT '0',
  `is_best_seller` tinyint(1) DEFAULT '0',
  `is_new_arrival` tinyint(1) DEFAULT '0',
  `weight` decimal(10,2) DEFAULT NULL,
  `scent_family` varchar(150) DEFAULT NULL,
  `ingredients` text,
  `how_to_use` text,
  `shipping_return_info` text,
  `seo_title` varchar(180) DEFAULT NULL,
  `seo_description` varchar(255) DEFAULT NULL,
  `status` enum('active','inactive','draft') DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`),
  UNIQUE KEY `sku` (`sku`),
  KEY `idx_products_slug` (`slug`),
  KEY `idx_products_status` (`status`),
  KEY `idx_products_category` (`category_id`),
  CONSTRAINT `products_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data for `products`
INSERT INTO `products` (`id`, `category_id`, `product_name`, `slug`, `short_description`, `description`, `sku`, `regular_price`, `sale_price`, `currency_code`, `stock_quantity`, `stock_status`, `badge`, `is_featured`, `is_best_seller`, `is_new_arrival`, `weight`, `scent_family`, `ingredients`, `how_to_use`, `shipping_return_info`, `seo_title`, `seo_description`, `status`, `created_at`, `updated_at`) VALUES ('1', '1', 'LOVE-U Signature Body Oil', 'love-u-signature-body-oil', '', '', NULL, '24.99', NULL, 'GBP', '18', 'in_stock', 'Best Seller', '1', '1', '0', NULL, '', '', '', '', '', '', 'active', '2026-05-19 10:59:02', '2026-05-19 11:50:48');


-- Table structure for `referral_rewards`
DROP TABLE IF EXISTS `referral_rewards`;
CREATE TABLE `referral_rewards` (
  `id` int NOT NULL AUTO_INCREMENT,
  `referral_id` int NOT NULL,
  `reward_type` enum('coupon','points','fixed_amount') NOT NULL,
  `reward_value` decimal(10,2) NOT NULL,
  `issued_at` datetime DEFAULT NULL,
  `status` enum('pending','issued','used','cancelled') DEFAULT 'pending',
  PRIMARY KEY (`id`),
  KEY `referral_id` (`referral_id`),
  CONSTRAINT `referral_rewards_ibfk_1` FOREIGN KEY (`referral_id`) REFERENCES `referrals` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data for `referral_rewards`


-- Table structure for `referrals`
DROP TABLE IF EXISTS `referrals`;
CREATE TABLE `referrals` (
  `id` int NOT NULL AUTO_INCREMENT,
  `referrer_customer_id` int NOT NULL,
  `referred_customer_id` int DEFAULT NULL,
  `referral_code` varchar(50) NOT NULL,
  `reward_status` enum('pending','approved','rejected','paid') DEFAULT 'pending',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `referrer_customer_id` (`referrer_customer_id`),
  KEY `referred_customer_id` (`referred_customer_id`),
  CONSTRAINT `referrals_ibfk_1` FOREIGN KEY (`referrer_customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `referrals_ibfk_2` FOREIGN KEY (`referred_customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data for `referrals`


-- Table structure for `review_media`
DROP TABLE IF EXISTS `review_media`;
CREATE TABLE `review_media` (
  `id` int NOT NULL AUTO_INCREMENT,
  `review_id` int NOT NULL,
  `media_type` enum('image','video') NOT NULL,
  `media_path` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `review_id` (`review_id`),
  CONSTRAINT `review_media_ibfk_1` FOREIGN KEY (`review_id`) REFERENCES `reviews` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data for `review_media`


-- Table structure for `reviews`
DROP TABLE IF EXISTS `reviews`;
CREATE TABLE `reviews` (
  `id` int NOT NULL AUTO_INCREMENT,
  `product_id` int NOT NULL,
  `customer_id` int DEFAULT NULL,
  `order_id` int DEFAULT NULL,
  `rating` tinyint NOT NULL,
  `review_title` varchar(180) DEFAULT NULL,
  `review_text` text,
  `verified_purchase` tinyint(1) DEFAULT '0',
  `status` enum('pending','approved','rejected') DEFAULT 'pending',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `customer_id` (`customer_id`),
  KEY `order_id` (`order_id`),
  KEY `idx_reviews_product` (`product_id`),
  CONSTRAINT `reviews_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
  CONSTRAINT `reviews_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL,
  CONSTRAINT `reviews_ibfk_3` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data for `reviews`
INSERT INTO `reviews` (`id`, `product_id`, `customer_id`, `order_id`, `rating`, `review_title`, `review_text`, `verified_purchase`, `status`, `created_at`) VALUES ('1', '1', '1', '2', '5', 'Good', 'Goood', '1', 'approved', '2026-05-19 13:43:05');


-- Table structure for `roles`
DROP TABLE IF EXISTS `roles`;
CREATE TABLE `roles` (
  `id` int NOT NULL AUTO_INCREMENT,
  `role_name` varchar(100) NOT NULL,
  `description` text,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `role_name` (`role_name`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data for `roles`
INSERT INTO `roles` (`id`, `role_name`, `description`, `created_at`) VALUES ('1', 'Super Admin', 'Full system access', '2026-05-19 10:09:46');
INSERT INTO `roles` (`id`, `role_name`, `description`, `created_at`) VALUES ('2', 'Admin', 'Manage website, products, orders and customers', '2026-05-19 10:09:46');
INSERT INTO `roles` (`id`, `role_name`, `description`, `created_at`) VALUES ('3', 'Order Manager', 'Manage customer orders', '2026-05-19 10:09:46');
INSERT INTO `roles` (`id`, `role_name`, `description`, `created_at`) VALUES ('4', 'Product Manager', 'Manage products and categories', '2026-05-19 10:09:46');
INSERT INTO `roles` (`id`, `role_name`, `description`, `created_at`) VALUES ('5', 'Marketing Manager', 'Manage coupons, reviews, banners and marketing features', '2026-05-19 10:09:46');


-- Table structure for `shipping_rates`
DROP TABLE IF EXISTS `shipping_rates`;
CREATE TABLE `shipping_rates` (
  `id` int NOT NULL AUTO_INCREMENT,
  `zone_id` int NOT NULL,
  `rate_name` varchar(150) NOT NULL,
  `min_order_amount` decimal(10,2) DEFAULT '0.00',
  `max_order_amount` decimal(10,2) DEFAULT NULL,
  `shipping_fee` decimal(10,2) DEFAULT '0.00',
  `free_shipping_threshold` decimal(10,2) DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `zone_id` (`zone_id`),
  CONSTRAINT `shipping_rates_ibfk_1` FOREIGN KEY (`zone_id`) REFERENCES `shipping_zones` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data for `shipping_rates`
INSERT INTO `shipping_rates` (`id`, `zone_id`, `rate_name`, `min_order_amount`, `max_order_amount`, `shipping_fee`, `free_shipping_threshold`, `status`, `created_at`) VALUES ('3', '3', 'International Shipping', '0.00', NULL, '15.00', '150.00', 'active', '2026-05-19 10:09:46');
INSERT INTO `shipping_rates` (`id`, `zone_id`, `rate_name`, `min_order_amount`, `max_order_amount`, `shipping_fee`, `free_shipping_threshold`, `status`, `created_at`) VALUES ('4', '4', 'Standard Shipping', '0.00', NULL, '4.99', '50.00', 'active', '2026-05-19 13:19:45');
INSERT INTO `shipping_rates` (`id`, `zone_id`, `rate_name`, `min_order_amount`, `max_order_amount`, `shipping_fee`, `free_shipping_threshold`, `status`, `created_at`) VALUES ('5', '5', 'Local Delivery', '0.00', NULL, '3.50', '40.00', 'active', '2026-05-19 13:20:03');


-- Table structure for `shipping_zones`
DROP TABLE IF EXISTS `shipping_zones`;
CREATE TABLE `shipping_zones` (
  `id` int NOT NULL AUTO_INCREMENT,
  `zone_name` varchar(150) NOT NULL,
  `country` varchar(100) NOT NULL,
  `region` varchar(100) DEFAULT NULL,
  `message` varchar(255) DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data for `shipping_zones`
INSERT INTO `shipping_zones` (`id`, `zone_name`, `country`, `region`, `message`, `status`, `created_at`) VALUES ('3', 'International', 'International', NULL, 'International shipping rates may vary by location.', 'active', '2026-05-19 10:09:46');
INSERT INTO `shipping_zones` (`id`, `zone_name`, `country`, `region`, `message`, `status`, `created_at`) VALUES ('4', 'United Kingdom', 'United Kingdom', 'All', 'Delivery usually takes 2-4 working days.
Status: Active', 'active', '2026-05-19 13:18:08');
INSERT INTO `shipping_zones` (`id`, `zone_name`, `country`, `region`, `message`, `status`, `created_at`) VALUES ('5', 'Grenada', 'Grenada', 'All', 'Local delivery options are available for Grenada orders.', 'active', '2026-05-19 13:18:36');


-- Table structure for `site_settings`
DROP TABLE IF EXISTS `site_settings`;
CREATE TABLE `site_settings` (
  `id` int NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(150) NOT NULL,
  `setting_value` longtext,
  `setting_group` varchar(100) DEFAULT 'general',
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data for `site_settings`
INSERT INTO `site_settings` (`id`, `setting_key`, `setting_value`, `setting_group`, `updated_at`) VALUES ('1', 'site_name', 'LOVE-U Essentials', 'general', NULL);
INSERT INTO `site_settings` (`id`, `setting_key`, `setting_value`, `setting_group`, `updated_at`) VALUES ('2', 'site_email', 'info@loveuessentials.com', 'general', NULL);
INSERT INTO `site_settings` (`id`, `setting_key`, `setting_value`, `setting_group`, `updated_at`) VALUES ('3', 'site_phone', '', 'general', NULL);
INSERT INTO `site_settings` (`id`, `setting_key`, `setting_value`, `setting_group`, `updated_at`) VALUES ('4', 'default_currency', 'GBP', 'commerce', NULL);
INSERT INTO `site_settings` (`id`, `setting_key`, `setting_value`, `setting_group`, `updated_at`) VALUES ('5', 'free_shipping_message', 'Free shipping on selected orders.', 'commerce', NULL);
INSERT INTO `site_settings` (`id`, `setting_key`, `setting_value`, `setting_group`, `updated_at`) VALUES ('6', 'popup_offer_enabled', '1', 'marketing', NULL);
INSERT INTO `site_settings` (`id`, `setting_key`, `setting_value`, `setting_group`, `updated_at`) VALUES ('7', 'popup_offer_text', 'Sign up and unlock an exclusive offer.', 'marketing', NULL);
INSERT INTO `site_settings` (`id`, `setting_key`, `setting_value`, `setting_group`, `updated_at`) VALUES ('8', 'seo_default_title', 'LOVE-U Essentials | Luxury Essentials Online', 'seo', NULL);
INSERT INTO `site_settings` (`id`, `setting_key`, `setting_value`, `setting_group`, `updated_at`) VALUES ('9', 'seo_default_description', 'Shop premium LOVE-U Essentials products online with secure checkout and smooth delivery.', 'seo', NULL);


-- Table structure for `wishlist_items`
DROP TABLE IF EXISTS `wishlist_items`;
CREATE TABLE `wishlist_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int NOT NULL,
  `product_id` int NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_customer_product` (`customer_id`,`product_id`),
  KEY `product_id` (`product_id`),
  CONSTRAINT `wishlist_items_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `wishlist_items_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Data for `wishlist_items`
INSERT INTO `wishlist_items` (`id`, `customer_id`, `product_id`, `created_at`) VALUES ('1', '1', '1', '2026-05-19 12:10:22');

SET FOREIGN_KEY_CHECKS=1;