-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Dec 09, 2025 at 10:09 PM
-- Server version: 10.4.32-MariaDB
-- PHP Version: 8.2.12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `the_perfect_vape`
--

-- --------------------------------------------------------

--
-- Table structure for table `admin_users`
--

CREATE TABLE `admin_users` (
  `id` int(11) UNSIGNED NOT NULL,
  `username` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `full_name` varchar(100) DEFAULT NULL,
  `role` enum('super_admin','admin','editor') DEFAULT 'admin',
  `avatar` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `last_login` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `admin_users`
--

INSERT INTO `admin_users` (`id`, `username`, `email`, `password_hash`, `full_name`, `role`, `avatar`, `is_active`, `last_login`, `created_at`, `updated_at`) VALUES
(1, 'admin', 'admin@gmail.com', '$2y$10$BXXqfRrCMJbac59m1nmm8.kLUfqxvdX6rqFfaqfG.C6IrL8z8Ypmy', 'admin', 'admin', NULL, 1, '2025-12-09 21:02:13', '2025-12-08 17:29:04', '2025-12-09 16:02:13');

-- --------------------------------------------------------

--
-- Table structure for table `blogs`
--

CREATE TABLE `blogs` (
  `id` int(11) UNSIGNED NOT NULL,
  `title` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `content` longtext NOT NULL,
  `excerpt` text DEFAULT NULL,
  `category` varchar(100) DEFAULT NULL,
  `featured_image` varchar(255) DEFAULT NULL,
  `author_id` int(11) UNSIGNED DEFAULT NULL,
  `visibility` enum('visible','hidden') DEFAULT 'visible',
  `seo_title` varchar(70) DEFAULT NULL,
  `seo_description` varchar(320) DEFAULT NULL,
  `seo_keywords` varchar(255) DEFAULT NULL,
  `published_at` datetime DEFAULT NULL,
  `views_count` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `blogs`
--

INSERT INTO `blogs` (`id`, `title`, `slug`, `content`, `excerpt`, `category`, `featured_image`, `author_id`, `visibility`, `seo_title`, `seo_description`, `seo_keywords`, `published_at`, `views_count`, `created_at`, `updated_at`) VALUES
(8, 'Top 10 Disposable Vapes of 2024', 'top-10-disposable-vapes-2024', '<p>Full blog content about top disposable vapes...</p>', 'Discover the best disposable vapes available this year', NULL, NULL, NULL, 'visible', NULL, NULL, NULL, '2025-12-09 23:24:58', 0, '2025-12-09 18:24:58', '2025-12-09 18:24:58'),
(9, 'How to Choose the Right Nicotine Strength', 'choose-right-nicotine-strength', '<p>Full blog content about nicotine strength...</p>', 'A comprehensive guide to selecting your ideal nicotine level', NULL, NULL, NULL, 'visible', NULL, NULL, NULL, '2025-12-09 23:24:58', 0, '2025-12-09 18:24:58', '2025-12-09 18:24:58'),
(10, 'Vape Juice Flavors Guide', 'vape-juice-flavors-guide', '<p>Full blog content about vape juice flavors...</p>', 'Explore different flavor profiles and find your favorite', NULL, NULL, NULL, 'visible', NULL, NULL, NULL, '2025-12-09 23:24:58', 0, '2025-12-09 18:24:58', '2025-12-09 18:24:58');

-- --------------------------------------------------------

--
-- Table structure for table `blog_images`
--

CREATE TABLE `blog_images` (
  `id` int(11) UNSIGNED NOT NULL,
  `blog_id` int(11) UNSIGNED NOT NULL,
  `image_path` varchar(255) NOT NULL,
  `image_url` varchar(500) DEFAULT NULL,
  `alt_text` varchar(255) DEFAULT NULL,
  `sort_order` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `collections`
--

CREATE TABLE `collections` (
  `id` int(11) UNSIGNED NOT NULL,
  `name` varchar(100) NOT NULL,
  `slug` varchar(100) NOT NULL,
  `parent_id` int(11) UNSIGNED DEFAULT NULL,
  `url_path` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `banner_image` varchar(255) DEFAULT NULL,
  `sort_order` int(11) DEFAULT 0,
  `is_active` tinyint(1) DEFAULT 1,
  `is_featured` tinyint(1) DEFAULT 0,
  `product_count` int(11) DEFAULT 0,
  `seo_title` varchar(70) DEFAULT NULL,
  `seo_description` varchar(320) DEFAULT NULL,
  `seo_keywords` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `collections`
--

INSERT INTO `collections` (`id`, `name`, `slug`, `parent_id`, `url_path`, `description`, `image`, `banner_image`, `sort_order`, `is_active`, `is_featured`, `product_count`, `seo_title`, `seo_description`, `seo_keywords`, `created_at`, `updated_at`) VALUES
(24, 'Disposable Vapes', 'disposable-vapes', NULL, 'disposable-vapes', 'Premium disposable vape devices', '/collection/geek-bar-pulse-15k-planet-edition-cover_500x500.webp', NULL, 0, 1, 0, 0, NULL, NULL, NULL, '2025-12-09 18:24:58', '2025-12-09 20:48:41'),
(25, 'E-Liquids', 'e-liquids', NULL, 'e-liquids', 'High-quality vape juices and salts', NULL, NULL, 0, 1, 0, 0, NULL, NULL, NULL, '2025-12-09 18:24:58', '2025-12-09 18:24:58'),
(26, 'Nicotine Pouches', 'nicotine-pouches', NULL, 'nicotine-pouches', 'Tobacco-free nicotine pouches', NULL, NULL, 0, 1, 0, 0, NULL, NULL, NULL, '2025-12-09 18:24:58', '2025-12-09 18:24:58'),
(27, 'Hardware', 'hardware', NULL, 'hardware', 'Mods, tanks, and coils', NULL, NULL, 0, 1, 0, 0, NULL, NULL, NULL, '2025-12-09 18:24:58', '2025-12-09 18:24:58'),
(28, 'Lost Mary', 'lost-mary', 24, 'disposable-vapes/lost-mary', 'Lost Mary Disposables', NULL, NULL, 0, 1, 0, 0, NULL, NULL, NULL, '2025-12-09 18:24:58', '2025-12-09 18:24:58'),
(29, 'Geek Bar', 'geek-bar', 24, 'disposable-vapes/geek-bar', 'Geek Bar Pulse & others', 'geek-bar-pulse-15k-planet-edition-cover_500x500.webp', NULL, 0, 1, 0, 0, NULL, NULL, NULL, '2025-12-09 18:24:58', '2025-12-09 20:52:08'),
(30, 'Raz Vape', 'raz-vape', 24, 'disposable-vapes/raz-vape', 'Raz Disposables', 'geek-bar-pulse-15k-planet-edition-cover_500x500.webp', NULL, 0, 1, 0, 0, NULL, NULL, NULL, '2025-12-09 18:24:58', '2025-12-09 20:52:06'),
(31, 'Salt Nic', 'salt-nic', 25, 'e-liquids/salt-nic', 'Salt Nicotine E-Liquids', 'geek-bar-pulse-15k-planet-edition-cover_500x500.webp', NULL, 0, 1, 0, 0, NULL, NULL, NULL, '2025-12-09 18:24:58', '2025-12-09 20:52:04'),
(32, 'Freebase', 'freebase', 25, 'e-liquids/freebase', 'Traditional Freebase E-Liquids', 'geek-bar-pulse-15k-planet-edition-cover_500x500.webp', NULL, 0, 1, 0, 0, NULL, NULL, NULL, '2025-12-09 18:24:58', '2025-12-09 20:52:02'),
(33, 'ZYN', 'zyn', 26, 'nicotine-pouches/zyn', 'ZYN Pouches', 'geek-bar-pulse-15k-planet-edition-cover_500x500.webp', NULL, 0, 1, 0, 0, NULL, NULL, NULL, '2025-12-09 18:24:58', '2025-12-09 20:51:59'),
(34, 'ON!', 'on-pouches', 26, 'nicotine-pouches/on', 'ON! Pouches', 'geek-bar-pulse-15k-planet-edition-cover_500x500.webp', NULL, 0, 1, 0, 0, NULL, NULL, NULL, '2025-12-09 18:24:58', '2025-12-09 20:51:56'),
(35, 'New Arrivals', 'new-arrivals', NULL, 'new-arrivals', 'Latest', 'geek-bar-pulse-15k-planet-edition-cover_500x500.webp', NULL, 0, 1, 0, 0, NULL, NULL, NULL, '2025-12-09 18:24:58', '2025-12-09 20:51:54'),
(36, 'Best Sellers', 'best-sellers', NULL, 'best-sellers', 'Popular', 'geek-bar-pulse-15k-planet-edition-cover_500x500.webp', NULL, 0, 1, 0, 0, NULL, NULL, NULL, '2025-12-09 18:24:58', '2025-12-09 20:51:32');

-- --------------------------------------------------------

--
-- Table structure for table `customers`
--

CREATE TABLE `customers` (
  `id` int(11) UNSIGNED NOT NULL,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `password_hash` varchar(255) DEFAULT NULL,
  `avatar` varchar(255) DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `gender` enum('male','female','other') DEFAULT NULL,
  `address_line1` varchar(255) DEFAULT NULL,
  `address_line2` varchar(255) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `state` varchar(100) DEFAULT NULL,
  `postal_code` varchar(20) DEFAULT NULL,
  `country` varchar(100) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `email_verified` tinyint(1) DEFAULT 0,
  `email_verified_at` datetime DEFAULT NULL,
  `last_login` datetime DEFAULT NULL,
  `total_orders` int(11) DEFAULT 0,
  `total_spent` decimal(10,2) DEFAULT 0.00,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `customers`
--

INSERT INTO `customers` (`id`, `first_name`, `last_name`, `email`, `phone`, `password_hash`, `avatar`, `date_of_birth`, `gender`, `address_line1`, `address_line2`, `city`, `state`, `postal_code`, `country`, `is_active`, `email_verified`, `email_verified_at`, `last_login`, `total_orders`, `total_spent`, `created_at`, `updated_at`) VALUES
(17, 'John', 'Doe', 'john.doe@example.com', '555-0101', '$2y$10$wf5MuHo/WqDJdTi4kFx3luydvKRuMzQDkm0gaNy248k/UkpfKC7x6', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, 1, NULL, NULL, 0, 0.00, '2025-12-09 18:24:58', '2025-12-09 18:24:58'),
(18, 'Jane', 'Smith', 'jane.smith@example.com', '555-0102', '$2y$10$rgHSH9HYMO52wLEhmwXp2uAa8DoZCMV9fmU95nAMM3kJlPqYu6sM.', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, 1, NULL, NULL, 0, 0.00, '2025-12-09 18:24:58', '2025-12-09 18:24:58'),
(19, 'Mike', 'Johnson', 'mike.j@example.com', '555-0103', '$2y$10$K3paHZHOCEQccy3Paj/MR.RhcUbn02teaKdHbOgnHy44LutEEYDgW', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, 1, NULL, NULL, 0, 0.00, '2025-12-09 18:24:58', '2025-12-09 18:24:58'),
(20, 'Sarah', 'Williams', 'sarah.w@example.com', '555-0104', '$2y$10$3oS4QkEhYc5dW0tsxC706.hAOvlKGas2oqPR6WFoPwv86qJnjMf0u', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, 1, NULL, NULL, 0, 0.00, '2025-12-09 18:24:58', '2025-12-09 18:24:58'),
(21, 'David', 'Brown', 'david.b@example.com', '555-0105', '$2y$10$xE8lK9KDj3ztuqwoP/l4wuH7H/b4CqVLI3GaLHbirRpjZodQJI64S', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, 1, NULL, NULL, 0, 0.00, '2025-12-09 18:24:58', '2025-12-09 18:24:58');

-- --------------------------------------------------------

--
-- Table structure for table `orders`
--

CREATE TABLE `orders` (
  `id` int(11) UNSIGNED NOT NULL,
  `order_number` varchar(50) NOT NULL,
  `customer_id` int(11) UNSIGNED DEFAULT NULL,
  `customer_email` varchar(100) NOT NULL,
  `customer_first_name` varchar(50) NOT NULL,
  `customer_last_name` varchar(50) NOT NULL,
  `customer_phone` varchar(20) DEFAULT NULL,
  `shipping_address_line1` varchar(255) NOT NULL,
  `shipping_address_line2` varchar(255) DEFAULT NULL,
  `shipping_city` varchar(100) NOT NULL,
  `shipping_state` varchar(100) NOT NULL,
  `shipping_postal_code` varchar(20) NOT NULL,
  `shipping_country` varchar(100) NOT NULL,
  `billing_address_line1` varchar(255) DEFAULT NULL,
  `billing_address_line2` varchar(255) DEFAULT NULL,
  `billing_city` varchar(100) DEFAULT NULL,
  `billing_state` varchar(100) DEFAULT NULL,
  `billing_postal_code` varchar(20) DEFAULT NULL,
  `billing_country` varchar(100) DEFAULT NULL,
  `subtotal` decimal(10,2) NOT NULL DEFAULT 0.00,
  `tax_amount` decimal(10,2) DEFAULT 0.00,
  `shipping_cost` decimal(10,2) DEFAULT 0.00,
  `discount_amount` decimal(10,2) DEFAULT 0.00,
  `total_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `currency` varchar(3) DEFAULT 'USD',
  `payment_status` enum('pending','paid','partially_refunded','refunded','voided') DEFAULT 'pending',
  `payment_method` varchar(50) DEFAULT NULL,
  `payment_transaction_id` varchar(255) DEFAULT NULL,
  `fulfillment_status` enum('unfulfilled','partial','fulfilled','scheduled','on_hold') DEFAULT 'unfulfilled',
  `delivery_status` enum('pending','processing','in_transit','delivered','cancelled','scheduled') DEFAULT 'pending',
  `delivery_method` varchar(50) DEFAULT 'standard',
  `tracking_number` varchar(100) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `orders`
--

INSERT INTO `orders` (`id`, `order_number`, `customer_id`, `customer_email`, `customer_first_name`, `customer_last_name`, `customer_phone`, `shipping_address_line1`, `shipping_address_line2`, `shipping_city`, `shipping_state`, `shipping_postal_code`, `shipping_country`, `billing_address_line1`, `billing_address_line2`, `billing_city`, `billing_state`, `billing_postal_code`, `billing_country`, `subtotal`, `tax_amount`, `shipping_cost`, `discount_amount`, `total_amount`, `currency`, `payment_status`, `payment_method`, `payment_transaction_id`, `fulfillment_status`, `delivery_status`, `delivery_method`, `tracking_number`, `notes`, `created_at`, `updated_at`) VALUES
(46, 'ORD-00001', 18, 'jane.smith@example.com', 'Jane', 'Smith', '555-0102', '123 Main St', NULL, 'New York', 'NY', '10001', 'USA', NULL, NULL, NULL, NULL, NULL, NULL, 67.04, 5.36, 5.99, 0.00, 78.39, 'USD', 'pending', 'credit_card', NULL, 'unfulfilled', 'cancelled', 'standard', NULL, NULL, '2025-12-09 18:24:58', '2025-12-09 18:24:58'),
(47, 'ORD-00002', 19, 'mike.j@example.com', 'Mike', 'Johnson', '555-0103', '123 Main St', NULL, 'New York', 'NY', '10001', 'USA', NULL, NULL, NULL, NULL, NULL, NULL, 102.71, 8.22, 5.99, 0.00, 116.92, 'USD', 'pending', 'credit_card', NULL, 'unfulfilled', 'pending', 'standard', NULL, NULL, '2025-12-09 18:24:58', '2025-12-09 18:24:58'),
(48, 'ORD-00003', 19, 'mike.j@example.com', 'Mike', 'Johnson', '555-0103', '123 Main St', NULL, 'New York', 'NY', '10001', 'USA', NULL, NULL, NULL, NULL, NULL, NULL, 80.72, 6.46, 5.99, 0.00, 93.17, 'USD', 'paid', 'credit_card', NULL, 'unfulfilled', 'in_transit', 'standard', NULL, NULL, '2025-12-09 18:24:58', '2025-12-09 18:24:58'),
(49, 'ORD-00004', 17, 'john.doe@example.com', 'John', 'Doe', '555-0101', '123 Main St', NULL, 'New York', 'NY', '10001', 'USA', NULL, NULL, NULL, NULL, NULL, NULL, 106.35, 8.51, 5.99, 0.00, 120.85, 'USD', 'refunded', 'credit_card', NULL, 'unfulfilled', 'delivered', 'standard', NULL, NULL, '2025-12-09 18:24:58', '2025-12-09 18:24:58'),
(50, 'ORD-00005', 21, 'david.b@example.com', 'David', 'Brown', '555-0105', '123 Main St', NULL, 'New York', 'NY', '10001', 'USA', NULL, NULL, NULL, NULL, NULL, NULL, 79.98, 6.40, 5.99, 0.00, 92.37, 'USD', 'pending', 'credit_card', NULL, 'unfulfilled', 'pending', 'standard', NULL, NULL, '2025-12-09 18:24:58', '2025-12-09 18:24:58'),
(51, 'ORD-00006', 18, 'jane.smith@example.com', 'Jane', 'Smith', '555-0102', '123 Main St', NULL, 'New York', 'NY', '10001', 'USA', NULL, NULL, NULL, NULL, NULL, NULL, 127.33, 10.19, 5.99, 0.00, 143.51, 'USD', 'refunded', 'credit_card', NULL, 'unfulfilled', 'in_transit', 'standard', NULL, NULL, '2025-12-09 18:24:58', '2025-12-09 18:24:58'),
(52, 'ORD-00007', 21, 'david.b@example.com', 'David', 'Brown', '555-0105', '123 Main St', NULL, 'New York', 'NY', '10001', 'USA', NULL, NULL, NULL, NULL, NULL, NULL, 52.86, 4.23, 5.99, 0.00, 63.08, 'USD', 'refunded', 'credit_card', NULL, 'unfulfilled', 'cancelled', 'standard', NULL, NULL, '2025-12-09 18:24:58', '2025-12-09 18:24:58'),
(53, 'ORD-00008', 17, 'john.doe@example.com', 'John', 'Doe', '555-0101', '123 Main St', NULL, 'New York', 'NY', '10001', 'USA', NULL, NULL, NULL, NULL, NULL, NULL, 49.70, 3.98, 5.99, 0.00, 59.67, 'USD', 'refunded', 'credit_card', NULL, 'unfulfilled', 'delivered', 'standard', NULL, NULL, '2025-12-09 18:24:58', '2025-12-09 18:24:58'),
(54, 'ORD-00009', 21, 'david.b@example.com', 'David', 'Brown', '555-0105', '123 Main St', NULL, 'New York', 'NY', '10001', 'USA', NULL, NULL, NULL, NULL, NULL, NULL, 134.80, 10.78, 5.99, 0.00, 151.57, 'USD', 'refunded', 'credit_card', NULL, 'unfulfilled', 'processing', 'standard', NULL, NULL, '2025-12-09 18:24:58', '2025-12-09 18:24:58'),
(55, 'ORD-00010', 17, 'john.doe@example.com', 'John', 'Doe', '555-0101', '123 Main St', NULL, 'New York', 'NY', '10001', 'USA', NULL, NULL, NULL, NULL, NULL, NULL, 89.78, 7.18, 5.99, 0.00, 102.95, 'USD', 'pending', 'credit_card', NULL, 'unfulfilled', 'processing', 'standard', NULL, NULL, '2025-12-09 18:24:58', '2025-12-09 18:24:58'),
(56, 'ORD-00011', 17, 'john.doe@example.com', 'John', 'Doe', '555-0101', '123 Main St', NULL, 'New York', 'NY', '10001', 'USA', NULL, NULL, NULL, NULL, NULL, NULL, 112.42, 8.99, 5.99, 0.00, 127.40, 'USD', 'pending', 'credit_card', NULL, 'unfulfilled', 'processing', 'standard', NULL, NULL, '2025-12-09 18:24:58', '2025-12-09 18:24:58'),
(57, 'ORD-00012', 21, 'david.b@example.com', 'David', 'Brown', '555-0105', '123 Main St', NULL, 'New York', 'NY', '10001', 'USA', NULL, NULL, NULL, NULL, NULL, NULL, 43.13, 3.45, 5.99, 0.00, 52.57, 'USD', 'pending', 'credit_card', NULL, 'unfulfilled', 'in_transit', 'standard', NULL, NULL, '2025-12-09 18:24:58', '2025-12-09 18:24:58'),
(58, 'ORD-00013', 19, 'mike.j@example.com', 'Mike', 'Johnson', '555-0103', '123 Main St', NULL, 'New York', 'NY', '10001', 'USA', NULL, NULL, NULL, NULL, NULL, NULL, 46.17, 3.69, 5.99, 0.00, 55.85, 'USD', 'pending', 'credit_card', NULL, 'unfulfilled', 'pending', 'standard', NULL, NULL, '2025-12-09 18:24:58', '2025-12-09 18:24:58'),
(59, 'ORD-00014', 18, 'jane.smith@example.com', 'Jane', 'Smith', '555-0102', '123 Main St', NULL, 'New York', 'NY', '10001', 'USA', NULL, NULL, NULL, NULL, NULL, NULL, 71.81, 5.74, 5.99, 0.00, 83.54, 'USD', 'pending', 'credit_card', NULL, 'unfulfilled', 'in_transit', 'standard', NULL, NULL, '2025-12-09 18:24:58', '2025-12-09 18:24:58'),
(60, 'ORD-00015', 17, 'john.doe@example.com', 'John', 'Doe', '555-0101', '123 Main St', NULL, 'New York', 'NY', '10001', 'USA', NULL, NULL, NULL, NULL, NULL, NULL, 106.28, 8.50, 5.99, 0.00, 120.77, 'USD', 'pending', 'credit_card', NULL, 'unfulfilled', 'processing', 'standard', NULL, NULL, '2025-12-09 18:24:58', '2025-12-09 18:24:58');

-- --------------------------------------------------------

--
-- Table structure for table `order_items`
--

CREATE TABLE `order_items` (
  `id` int(11) UNSIGNED NOT NULL,
  `order_id` int(11) UNSIGNED NOT NULL,
  `product_id` int(11) UNSIGNED DEFAULT NULL,
  `variant_id` int(11) UNSIGNED DEFAULT NULL,
  `product_title` varchar(255) NOT NULL,
  `variant_title` varchar(255) DEFAULT NULL,
  `sku` varchar(100) DEFAULT NULL,
  `quantity` int(11) NOT NULL DEFAULT 1,
  `unit_price` decimal(10,2) NOT NULL,
  `total_price` decimal(10,2) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `order_items`
--

INSERT INTO `order_items` (`id`, `order_id`, `product_id`, `variant_id`, `product_title`, `variant_title`, `sku`, `quantity`, `unit_price`, `total_price`, `created_at`) VALUES
(99, 46, 53, NULL, 'VIHO SUPERCHARGE 20K', NULL, NULL, 2, 19.99, 39.98, '2025-12-09 18:24:58'),
(100, 46, 57, NULL, 'Strawberry Cream - 60ml', NULL, NULL, 1, 19.99, 19.99, '2025-12-09 18:24:58'),
(101, 47, 59, NULL, 'ON! Citrus 4mg', NULL, NULL, 2, 4.99, 9.98, '2025-12-09 18:24:58'),
(102, 48, 60, NULL, 'Geek Bar Pulse X', NULL, NULL, 2, 23.99, 47.98, '2025-12-09 18:24:58'),
(103, 48, 60, NULL, 'Geek Bar Pulse X', NULL, NULL, 1, 23.99, 23.99, '2025-12-09 18:24:58'),
(104, 48, 59, NULL, 'ON! Citrus 4mg', NULL, NULL, 3, 4.99, 14.97, '2025-12-09 18:24:58'),
(105, 49, 54, NULL, 'Pachamama 25K', NULL, NULL, 3, 21.99, 65.97, '2025-12-09 18:24:58'),
(106, 50, 53, NULL, 'VIHO SUPERCHARGE 20K', NULL, NULL, 2, 19.99, 39.98, '2025-12-09 18:24:58'),
(107, 50, 58, NULL, 'ZYN Wintergreen 6mg', NULL, NULL, 1, 5.99, 5.99, '2025-12-09 18:24:58'),
(108, 51, 51, NULL, 'Lost Mary MT15000 Turbo', NULL, NULL, 2, 18.99, 37.98, '2025-12-09 18:24:58'),
(109, 51, 55, NULL, 'Spaceman Prism 20K', NULL, NULL, 1, 20.99, 20.99, '2025-12-09 18:24:58'),
(110, 52, 58, NULL, 'ZYN Wintergreen 6mg', NULL, NULL, 1, 5.99, 5.99, '2025-12-09 18:24:58'),
(111, 53, 57, NULL, 'Strawberry Cream - 60ml', NULL, NULL, 3, 19.99, 59.97, '2025-12-09 18:24:58'),
(112, 53, 56, NULL, 'Blue Razz Ice - 100ml', NULL, NULL, 3, 24.99, 74.97, '2025-12-09 18:24:58'),
(113, 53, 58, NULL, 'ZYN Wintergreen 6mg', NULL, NULL, 1, 5.99, 5.99, '2025-12-09 18:24:58'),
(114, 54, 54, NULL, 'Pachamama 25K', NULL, NULL, 2, 21.99, 43.98, '2025-12-09 18:24:58'),
(115, 55, 60, NULL, 'Geek Bar Pulse X', NULL, NULL, 2, 23.99, 47.98, '2025-12-09 18:24:58'),
(116, 55, 60, NULL, 'Geek Bar Pulse X', NULL, NULL, 2, 23.99, 47.98, '2025-12-09 18:24:58'),
(117, 55, 56, NULL, 'Blue Razz Ice - 100ml', NULL, NULL, 1, 24.99, 24.99, '2025-12-09 18:24:58'),
(118, 56, 51, NULL, 'Lost Mary MT15000 Turbo', NULL, NULL, 3, 18.99, 56.97, '2025-12-09 18:24:58'),
(119, 56, 59, NULL, 'ON! Citrus 4mg', NULL, NULL, 3, 4.99, 14.97, '2025-12-09 18:24:58'),
(120, 56, 58, NULL, 'ZYN Wintergreen 6mg', NULL, NULL, 3, 5.99, 17.97, '2025-12-09 18:24:58'),
(121, 57, 57, NULL, 'Strawberry Cream - 60ml', NULL, NULL, 3, 19.99, 59.97, '2025-12-09 18:24:58'),
(122, 58, 51, NULL, 'Lost Mary MT15000 Turbo', NULL, NULL, 1, 18.99, 18.99, '2025-12-09 18:24:58'),
(123, 58, 52, NULL, 'RAZ LTX 25000', NULL, NULL, 1, 22.99, 22.99, '2025-12-09 18:24:58'),
(124, 58, 55, NULL, 'Spaceman Prism 20K', NULL, NULL, 1, 20.99, 20.99, '2025-12-09 18:24:58'),
(125, 59, 55, NULL, 'Spaceman Prism 20K', NULL, NULL, 1, 20.99, 20.99, '2025-12-09 18:24:58'),
(126, 60, 60, NULL, 'Geek Bar Pulse X', NULL, NULL, 2, 23.99, 47.98, '2025-12-09 18:24:58');

-- --------------------------------------------------------

--
-- Table structure for table `password_reset_tokens`
--

CREATE TABLE `password_reset_tokens` (
  `id` int(11) UNSIGNED NOT NULL,
  `email` varchar(100) NOT NULL,
  `token` varchar(255) NOT NULL,
  `user_type` enum('admin','customer') DEFAULT 'customer',
  `expires_at` datetime NOT NULL,
  `used` tinyint(1) DEFAULT 0,
  `used_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `products`
--

CREATE TABLE `products` (
  `id` int(11) UNSIGNED NOT NULL,
  `title` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `description` longtext DEFAULT NULL,
  `status` enum('active','draft','archived') DEFAULT 'draft',
  `sku` varchar(100) DEFAULT NULL,
  `price` decimal(10,2) DEFAULT 0.00,
  `compare_at_price` decimal(10,2) DEFAULT NULL,
  `cost_per_item` decimal(10,2) DEFAULT NULL,
  `track_inventory` tinyint(1) DEFAULT 1,
  `weight` decimal(8,2) DEFAULT NULL,
  `seo_title` varchar(70) DEFAULT NULL,
  `seo_description` varchar(320) DEFAULT NULL,
  `seo_keywords` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `products`
--

INSERT INTO `products` (`id`, `title`, `slug`, `description`, `status`, `sku`, `price`, `compare_at_price`, `cost_per_item`, `track_inventory`, `weight`, `seo_title`, `seo_description`, `seo_keywords`, `created_at`, `updated_at`) VALUES
(51, 'Lost Mary MT15000 Turbo', 'lost-mary-mt15000-turbo', 'Premium disposable vape with 15000 puffs', 'active', 'LM-MT15K', 18.99, 24.99, NULL, 1, NULL, NULL, NULL, NULL, '2025-12-09 18:24:58', '2025-12-09 18:24:58'),
(52, 'RAZ LTX 25000', 'raz-ltx-25000', 'Long-lasting disposable with 25000 puffs', 'active', 'RAZ-LTX25K', 22.99, 29.99, NULL, 1, NULL, NULL, NULL, NULL, '2025-12-09 18:24:58', '2025-12-09 18:24:58'),
(53, 'VIHO SUPERCHARGE 20K', 'viho-supercharge-20k', 'Supercharged disposable vape device', 'active', 'VIHO-SC20K', 19.99, 25.99, NULL, 1, NULL, NULL, NULL, NULL, '2025-12-09 18:24:58', '2025-12-09 18:24:58'),
(54, 'Pachamama 25K', 'pachamama-25k', 'Exotic flavors with 25000 puffs', 'active', 'PACH-25K', 21.99, 27.99, NULL, 1, NULL, NULL, NULL, NULL, '2025-12-09 18:24:58', '2025-12-09 18:24:58'),
(55, 'Spaceman Prism 20K', 'spaceman-prism-20k', 'Futuristic design with premium flavors', 'active', 'SPACE-PR20K', 20.99, 26.99, NULL, 1, NULL, NULL, NULL, NULL, '2025-12-09 18:24:58', '2025-12-09 18:24:58'),
(56, 'Blue Razz Ice - 100ml', 'blue-razz-ice---100ml', 'Refreshing blue raspberry with menthol', 'active', 'NK-BRZI-100', 24.99, 29.99, NULL, 1, NULL, NULL, NULL, NULL, '2025-12-09 18:24:58', '2025-12-09 18:24:58'),
(57, 'Strawberry Cream - 60ml', 'strawberry-cream---60ml', 'Sweet strawberry with creamy notes', 'active', 'JM-STCR-60', 19.99, 24.99, NULL, 1, NULL, NULL, NULL, NULL, '2025-12-09 18:24:58', '2025-12-09 18:24:58'),
(58, 'ZYN Wintergreen 6mg', 'zyn-wintergreen-6mg', 'Tobacco-free nicotine pouches', 'active', 'ZYN-WG-6', 5.99, 7.99, NULL, 1, NULL, NULL, NULL, NULL, '2025-12-09 18:24:58', '2025-12-09 18:24:58'),
(59, 'ON! Citrus 4mg', 'on-citrus-4mg', 'Refreshing citrus nicotine pouches', 'active', 'ON-CIT-4', 4.99, 6.99, NULL, 1, NULL, NULL, NULL, NULL, '2025-12-09 18:24:58', '2025-12-09 18:24:58'),
(60, 'Geek Bar Pulse X', 'geek-bar-pulse-x', 'Next-gen disposable with smart features', 'active', 'GB-PULSEX', 23.99, 29.99, NULL, 1, NULL, NULL, NULL, NULL, '2025-12-09 18:24:58', '2025-12-09 18:24:58');

-- --------------------------------------------------------

--
-- Table structure for table `product_collections`
--

CREATE TABLE `product_collections` (
  `id` int(11) UNSIGNED NOT NULL,
  `product_id` int(11) UNSIGNED NOT NULL,
  `collection_id` int(11) UNSIGNED NOT NULL,
  `sort_order` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `product_collections`
--

INSERT INTO `product_collections` (`id`, `product_id`, `collection_id`, `sort_order`, `created_at`) VALUES
(79, 51, 24, 0, '2025-12-09 18:24:58'),
(80, 51, 28, 0, '2025-12-09 18:24:58'),
(81, 51, 36, 0, '2025-12-09 18:24:58'),
(82, 51, 35, 0, '2025-12-09 18:24:58'),
(83, 52, 24, 0, '2025-12-09 18:24:58'),
(84, 52, 30, 0, '2025-12-09 18:24:58'),
(85, 52, 35, 0, '2025-12-09 18:24:58'),
(86, 53, 24, 0, '2025-12-09 18:24:58'),
(87, 53, 36, 0, '2025-12-09 18:24:58'),
(88, 54, 24, 0, '2025-12-09 18:24:58'),
(89, 54, 35, 0, '2025-12-09 18:24:58'),
(90, 55, 24, 0, '2025-12-09 18:24:58'),
(91, 56, 25, 0, '2025-12-09 18:24:58'),
(92, 56, 32, 0, '2025-12-09 18:24:58'),
(93, 56, 36, 0, '2025-12-09 18:24:58'),
(94, 57, 25, 0, '2025-12-09 18:24:58'),
(95, 57, 32, 0, '2025-12-09 18:24:58'),
(96, 58, 26, 0, '2025-12-09 18:24:58'),
(97, 58, 33, 0, '2025-12-09 18:24:58'),
(98, 58, 36, 0, '2025-12-09 18:24:58'),
(99, 59, 26, 0, '2025-12-09 18:24:58'),
(100, 59, 34, 0, '2025-12-09 18:24:58'),
(101, 60, 24, 0, '2025-12-09 18:24:58'),
(102, 60, 29, 0, '2025-12-09 18:24:58'),
(103, 60, 35, 0, '2025-12-09 18:24:58');

-- --------------------------------------------------------

--
-- Table structure for table `product_images`
--

CREATE TABLE `product_images` (
  `id` int(11) UNSIGNED NOT NULL,
  `product_id` int(11) UNSIGNED NOT NULL,
  `image_path` varchar(255) NOT NULL,
  `image_url` varchar(500) DEFAULT NULL,
  `alt_text` varchar(255) DEFAULT NULL,
  `is_featured` tinyint(1) DEFAULT 0,
  `sort_order` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `product_metafields`
--

CREATE TABLE `product_metafields` (
  `id` int(11) UNSIGNED NOT NULL,
  `product_id` int(11) UNSIGNED NOT NULL,
  `key` varchar(100) NOT NULL,
  `value` text DEFAULT NULL,
  `type` enum('text','number','boolean','json','image','url') DEFAULT 'text',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `product_options`
--

CREATE TABLE `product_options` (
  `id` int(11) UNSIGNED NOT NULL,
  `product_id` int(11) UNSIGNED NOT NULL,
  `name` varchar(100) NOT NULL,
  `position` int(11) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `product_option_values`
--

CREATE TABLE `product_option_values` (
  `id` int(11) UNSIGNED NOT NULL,
  `product_option_id` int(11) UNSIGNED NOT NULL,
  `value` varchar(255) NOT NULL,
  `position` int(11) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `product_reviews`
--

CREATE TABLE `product_reviews` (
  `id` int(11) UNSIGNED NOT NULL,
  `product_id` int(11) UNSIGNED NOT NULL,
  `customer_id` int(11) UNSIGNED DEFAULT NULL,
  `customer_name` varchar(100) NOT NULL,
  `customer_email` varchar(100) NOT NULL,
  `rating` tinyint(1) NOT NULL CHECK (`rating` >= 1 and `rating` <= 5),
  `title` varchar(255) DEFAULT NULL,
  `review_text` text DEFAULT NULL,
  `is_verified_purchase` tinyint(1) DEFAULT 0,
  `is_approved` tinyint(1) DEFAULT 0,
  `is_featured` tinyint(1) DEFAULT 0,
  `helpful_count` int(11) DEFAULT 0,
  `unhelpful_count` int(11) DEFAULT 0,
  `admin_response` text DEFAULT NULL,
  `responded_by` int(11) UNSIGNED DEFAULT NULL,
  `responded_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `product_variants`
--

CREATE TABLE `product_variants` (
  `id` int(11) UNSIGNED NOT NULL,
  `product_id` int(11) UNSIGNED NOT NULL,
  `variant_name` varchar(255) NOT NULL,
  `sku` varchar(100) DEFAULT NULL,
  `price` decimal(10,2) NOT NULL,
  `compare_at_price` decimal(10,2) DEFAULT NULL,
  `cost_per_item` decimal(10,2) DEFAULT NULL,
  `inventory_quantity` int(11) DEFAULT 0,
  `track_inventory` tinyint(1) DEFAULT 1,
  `weight` decimal(8,2) DEFAULT NULL,
  `image_path` varchar(255) DEFAULT NULL,
  `option1` varchar(100) DEFAULT NULL,
  `option2` varchar(100) DEFAULT NULL,
  `option3` varchar(100) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `product_variant_option_values`
--

CREATE TABLE `product_variant_option_values` (
  `variant_id` int(11) UNSIGNED NOT NULL,
  `option_value_id` int(11) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `refunds`
--

CREATE TABLE `refunds` (
  `id` int(11) UNSIGNED NOT NULL,
  `refund_number` varchar(50) NOT NULL,
  `order_id` int(11) UNSIGNED NOT NULL,
  `customer_id` int(11) UNSIGNED DEFAULT NULL,
  `refund_amount` decimal(10,2) NOT NULL,
  `refund_reason` varchar(255) DEFAULT NULL,
  `refund_type` enum('full','partial') DEFAULT 'full',
  `status` enum('pending','approved','refunded','declined','cancelled') DEFAULT 'pending',
  `payment_method` varchar(50) DEFAULT NULL,
  `transaction_id` varchar(255) DEFAULT NULL,
  `admin_notes` text DEFAULT NULL,
  `processed_by` int(11) UNSIGNED DEFAULT NULL,
  `processed_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `sessions`
--

CREATE TABLE `sessions` (
  `id` varchar(128) NOT NULL,
  `user_id` int(11) UNSIGNED DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `payload` text NOT NULL,
  `last_activity` int(11) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `admin_users`
--
ALTER TABLE `admin_users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `username` (`username`),
  ADD UNIQUE KEY `email` (`email`),
  ADD KEY `idx_is_active` (`is_active`);

--
-- Indexes for table `blogs`
--
ALTER TABLE `blogs`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `slug` (`slug`),
  ADD KEY `idx_author_id` (`author_id`),
  ADD KEY `idx_visibility` (`visibility`),
  ADD KEY `idx_published_at` (`published_at`),
  ADD KEY `idx_visibility_published` (`visibility`,`published_at`),
  ADD KEY `idx_author_published` (`author_id`,`published_at`),
  ADD KEY `idx_category_published` (`category`,`published_at`);
ALTER TABLE `blogs` ADD FULLTEXT KEY `idx_search` (`title`,`content`,`excerpt`);

--
-- Indexes for table `blog_images`
--
ALTER TABLE `blog_images`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_blog_id` (`blog_id`),
  ADD KEY `idx_sort_order` (`sort_order`);

--
-- Indexes for table `collections`
--
ALTER TABLE `collections`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `url_path` (`url_path`),
  ADD KEY `idx_slug` (`slug`),
  ADD KEY `idx_parent_id` (`parent_id`),
  ADD KEY `idx_is_active` (`is_active`),
  ADD KEY `idx_parent_active` (`parent_id`,`is_active`),
  ADD KEY `idx_url_active` (`url_path`,`is_active`),
  ADD KEY `idx_featured_sort` (`is_featured`,`sort_order`);

--
-- Indexes for table `customers`
--
ALTER TABLE `customers`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `email` (`email`),
  ADD KEY `idx_is_active` (`is_active`),
  ADD KEY `idx_country` (`country`),
  ADD KEY `idx_country_active` (`country`,`is_active`),
  ADD KEY `idx_orders_spent` (`total_orders`,`total_spent`),
  ADD KEY `idx_active_verified` (`is_active`,`email_verified`);

--
-- Indexes for table `orders`
--
ALTER TABLE `orders`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `order_number` (`order_number`),
  ADD KEY `idx_customer_id` (`customer_id`),
  ADD KEY `idx_customer_email` (`customer_email`),
  ADD KEY `idx_payment_status` (`payment_status`),
  ADD KEY `idx_fulfillment_status` (`fulfillment_status`),
  ADD KEY `idx_delivery_status` (`delivery_status`),
  ADD KEY `idx_created_at` (`created_at`),
  ADD KEY `idx_customer_payment` (`customer_id`,`payment_status`),
  ADD KEY `idx_customer_created` (`customer_id`,`created_at`),
  ADD KEY `idx_payment_created` (`payment_status`,`created_at`),
  ADD KEY `idx_fulfillment_created` (`fulfillment_status`,`created_at`),
  ADD KEY `idx_delivery_created` (`delivery_status`,`created_at`);

--
-- Indexes for table `order_items`
--
ALTER TABLE `order_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_order_id` (`order_id`),
  ADD KEY `idx_product_id` (`product_id`),
  ADD KEY `idx_variant_id` (`variant_id`),
  ADD KEY `idx_product_created` (`product_id`,`created_at`),
  ADD KEY `idx_variant_created` (`variant_id`,`created_at`);

--
-- Indexes for table `password_reset_tokens`
--
ALTER TABLE `password_reset_tokens`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_email` (`email`),
  ADD KEY `idx_token` (`token`),
  ADD KEY `idx_expires_at` (`expires_at`),
  ADD KEY `idx_used` (`used`),
  ADD KEY `idx_email_used_expires` (`email`,`used`,`expires_at`),
  ADD KEY `idx_expires_used` (`expires_at`,`used`);

--
-- Indexes for table `products`
--
ALTER TABLE `products`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `slug` (`slug`),
  ADD UNIQUE KEY `sku` (`sku`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_status_price` (`status`,`price`),
  ADD KEY `idx_status_created` (`status`,`created_at`),
  ADD KEY `idx_price` (`price`);
ALTER TABLE `products` ADD FULLTEXT KEY `idx_search` (`title`,`description`);

--
-- Indexes for table `product_collections`
--
ALTER TABLE `product_collections`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_product_collection` (`product_id`,`collection_id`),
  ADD KEY `idx_product_id` (`product_id`),
  ADD KEY `idx_collection_id` (`collection_id`),
  ADD KEY `idx_collection_sort` (`collection_id`,`sort_order`),
  ADD KEY `idx_collection_product` (`collection_id`,`product_id`);

--
-- Indexes for table `product_images`
--
ALTER TABLE `product_images`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_product_id` (`product_id`),
  ADD KEY `idx_is_featured` (`is_featured`),
  ADD KEY `idx_sort_order` (`sort_order`),
  ADD KEY `idx_product_featured_sort` (`product_id`,`is_featured`,`sort_order`);

--
-- Indexes for table `product_metafields`
--
ALTER TABLE `product_metafields`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_product_key` (`product_id`,`key`),
  ADD KEY `idx_product_id` (`product_id`),
  ADD KEY `idx_key` (`key`);

--
-- Indexes for table `product_options`
--
ALTER TABLE `product_options`
  ADD PRIMARY KEY (`id`),
  ADD KEY `product_id` (`product_id`);

--
-- Indexes for table `product_option_values`
--
ALTER TABLE `product_option_values`
  ADD PRIMARY KEY (`id`),
  ADD KEY `product_option_id` (`product_option_id`);

--
-- Indexes for table `product_reviews`
--
ALTER TABLE `product_reviews`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_product_id` (`product_id`),
  ADD KEY `idx_customer_id` (`customer_id`),
  ADD KEY `idx_rating` (`rating`),
  ADD KEY `idx_is_approved` (`is_approved`),
  ADD KEY `idx_is_featured` (`is_featured`),
  ADD KEY `idx_created_at` (`created_at`),
  ADD KEY `idx_product_approved` (`product_id`,`is_approved`),
  ADD KEY `idx_product_featured` (`product_id`,`is_featured`),
  ADD KEY `idx_product_approved_rating` (`product_id`,`is_approved`,`rating`),
  ADD KEY `idx_customer_created` (`customer_id`,`created_at`),
  ADD KEY `responded_by` (`responded_by`);

--
-- Indexes for table `product_variants`
--
ALTER TABLE `product_variants`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `sku` (`sku`),
  ADD KEY `idx_product_id` (`product_id`),
  ADD KEY `idx_is_active` (`is_active`),
  ADD KEY `idx_product_active` (`product_id`,`is_active`),
  ADD KEY `idx_product_price` (`product_id`,`price`),
  ADD KEY `idx_product_inventory` (`product_id`,`inventory_quantity`),
  ADD KEY `idx_active_price` (`is_active`,`price`);

--
-- Indexes for table `product_variant_option_values`
--
ALTER TABLE `product_variant_option_values`
  ADD PRIMARY KEY (`variant_id`,`option_value_id`),
  ADD KEY `option_value_id` (`option_value_id`);

--
-- Indexes for table `refunds`
--
ALTER TABLE `refunds`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `refund_number` (`refund_number`),
  ADD KEY `idx_order_id` (`order_id`),
  ADD KEY `idx_customer_id` (`customer_id`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_created_at` (`created_at`),
  ADD KEY `processed_by` (`processed_by`);

--
-- Indexes for table `sessions`
--
ALTER TABLE `sessions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_user_id` (`user_id`),
  ADD KEY `idx_last_activity` (`last_activity`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `admin_users`
--
ALTER TABLE `admin_users`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `blogs`
--
ALTER TABLE `blogs`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;

--
-- AUTO_INCREMENT for table `blog_images`
--
ALTER TABLE `blog_images`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `collections`
--
ALTER TABLE `collections`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=37;

--
-- AUTO_INCREMENT for table `customers`
--
ALTER TABLE `customers`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=23;

--
-- AUTO_INCREMENT for table `orders`
--
ALTER TABLE `orders`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=61;

--
-- AUTO_INCREMENT for table `order_items`
--
ALTER TABLE `order_items`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=127;

--
-- AUTO_INCREMENT for table `password_reset_tokens`
--
ALTER TABLE `password_reset_tokens`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `products`
--
ALTER TABLE `products`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=63;

--
-- AUTO_INCREMENT for table `product_collections`
--
ALTER TABLE `product_collections`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=104;

--
-- AUTO_INCREMENT for table `product_images`
--
ALTER TABLE `product_images`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

--
-- AUTO_INCREMENT for table `product_metafields`
--
ALTER TABLE `product_metafields`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `product_options`
--
ALTER TABLE `product_options`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

--
-- AUTO_INCREMENT for table `product_option_values`
--
ALTER TABLE `product_option_values`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=23;

--
-- AUTO_INCREMENT for table `product_reviews`
--
ALTER TABLE `product_reviews`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `product_variants`
--
ALTER TABLE `product_variants`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=31;

--
-- AUTO_INCREMENT for table `refunds`
--
ALTER TABLE `refunds`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `blogs`
--
ALTER TABLE `blogs`
  ADD CONSTRAINT `blogs_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `admin_users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `blog_images`
--
ALTER TABLE `blog_images`
  ADD CONSTRAINT `blog_images_ibfk_1` FOREIGN KEY (`blog_id`) REFERENCES `blogs` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `collections`
--
ALTER TABLE `collections`
  ADD CONSTRAINT `collections_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `collections` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `orders`
--
ALTER TABLE `orders`
  ADD CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `order_items`
--
ALTER TABLE `order_items`
  ADD CONSTRAINT `order_items_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `order_items_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `order_items_ibfk_3` FOREIGN KEY (`variant_id`) REFERENCES `product_variants` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `product_collections`
--
ALTER TABLE `product_collections`
  ADD CONSTRAINT `product_collections_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `product_collections_ibfk_2` FOREIGN KEY (`collection_id`) REFERENCES `collections` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `product_images`
--
ALTER TABLE `product_images`
  ADD CONSTRAINT `product_images_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `product_metafields`
--
ALTER TABLE `product_metafields`
  ADD CONSTRAINT `product_metafields_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `product_options`
--
ALTER TABLE `product_options`
  ADD CONSTRAINT `product_options_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `product_option_values`
--
ALTER TABLE `product_option_values`
  ADD CONSTRAINT `product_option_values_ibfk_1` FOREIGN KEY (`product_option_id`) REFERENCES `product_options` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `product_reviews`
--
ALTER TABLE `product_reviews`
  ADD CONSTRAINT `product_reviews_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `product_reviews_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `product_reviews_ibfk_3` FOREIGN KEY (`responded_by`) REFERENCES `admin_users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `product_variants`
--
ALTER TABLE `product_variants`
  ADD CONSTRAINT `product_variants_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `product_variant_option_values`
--
ALTER TABLE `product_variant_option_values`
  ADD CONSTRAINT `product_variant_option_values_ibfk_1` FOREIGN KEY (`variant_id`) REFERENCES `product_variants` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `product_variant_option_values_ibfk_2` FOREIGN KEY (`option_value_id`) REFERENCES `product_option_values` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `refunds`
--
ALTER TABLE `refunds`
  ADD CONSTRAINT `refunds_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`),
  ADD CONSTRAINT `refunds_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `refunds_ibfk_3` FOREIGN KEY (`processed_by`) REFERENCES `admin_users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `sessions`
--
ALTER TABLE `sessions`
  ADD CONSTRAINT `sessions_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `admin_users` (`id`) ON DELETE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
