-- https://www.phpmyadmin.net/
--

START TRANSACTION;



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

--
-- Table structure for table `api_cache`
--

CREATE TABLE `api_cache` (
  `cache_id` int(11) NOT NULL,
  `cache_key` varchar(255) NOT NULL,
  `cache_data` longtext NOT NULL,
  `fetched_at` datetime NOT NULL DEFAULT current_timestamp(),
  `expires_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

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

--
-- Table structure for table `blog_categories`
--

CREATE TABLE `blog_categories` (
  `blog_cat_id` int(11) NOT NULL,
  `cat_name` varchar(100) NOT NULL,
  `cat_slug` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `icon` varchar(10) DEFAULT '',
  `sort_order` int(11) DEFAULT 0,
  `created_at` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

--
-- Dumping data for table `blog_categories`
--

INSERT INTO `blog_categories` (`blog_cat_id`, `cat_name`, `cat_slug`, `description`, `icon`, `sort_order`, `created_at`) VALUES
(1, 'Skincare Tips', 'skincare-tips', 'Expert advice for your daily skincare routine', '✨', 1, '2026-03-24 18:03:23'),
(2, 'Mind & Body', 'mind-body', 'The connection between inner peace and outer radiance', '🧘', 2, '2026-03-24 18:03:23'),
(3, 'Nutrition & Glow', 'nutrition-glow', 'What you eat shows on your skin — fuel your glow from the inside out', '🍊', 3, '2026-03-24 18:03:23'),
(4, 'Self-Care Rituals', 'self-care', 'Intentional moments of care that nourish your whole self', '🌿', 4, '2026-03-24 18:03:23');

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

--
-- Table structure for table `blog_posts`
--

CREATE TABLE `blog_posts` (
  `post_id` int(11) NOT NULL,
  `title` varchar(300) NOT NULL,
  `slug` varchar(300) NOT NULL,
  `excerpt` text DEFAULT NULL,
  `content` longtext NOT NULL,
  `featured_image` varchar(255) DEFAULT NULL,
  `blog_cat_id` int(11) NOT NULL,
  `status` enum('draft','published','archived') DEFAULT 'draft',
  `author_name` varchar(100) DEFAULT 'KathyJane Team',
  `view_count` int(11) DEFAULT 0,
  `published_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

--
-- Dumping data for table `blog_posts`
--

INSERT INTO `blog_posts` (`post_id`, `title`, `slug`, `excerpt`, `content`, `featured_image`, `blog_cat_id`, `status`, `author_name`, `view_count`, `published_at`, `created_at`, `updated_at`) VALUES
(1, 'Why Vitamin C is Your Skin\'s Best Friend', 'vitamin-c-skins-best-friend', 'Discover why dermatologists call Vitamin C the gold standard of skincare.', '<p>If there\'s one ingredient that dermatologists universally agree on, it\'s <strong>Vitamin C</strong>. This powerhouse antioxidant doesn\'t just brighten your skin — it actively protects it from environmental damage, boosts collagen production, and fades dark spots.</p><h3>How Vitamin C Works</h3><p>Vitamin C neutralizes free radicals — unstable molecules caused by UV exposure and pollution that damage skin cells. Think of it as a shield.</p><h3>When to Apply</h3><p>Morning is ideal. After cleansing, apply your Vitamin C serum to clean skin. Wait 60 seconds, then follow with moisturizer and sunscreen.</p><h3>The KathyJane Difference</h3><p>Most Vitamin C serums use synthetic ascorbic acid. Ours is derived from real citrus — sun-ripened oranges from orchards our founder personally visits. Combined with Vitamin E and Ferulic Acid, it\'s stable, potent, and gentle enough for daily use.</p>', NULL, 1, 'published', 'Dr. Sarah Mitchell', 342, '2025-01-10 09:00:00', '2026-03-24 18:03:23', '2026-03-24 18:03:23'),
(2, 'The 5-Step Evening Routine That Changed My Skin', 'five-step-evening-routine', 'A dermatologist-approved nighttime skincare routine using just 5 products.', '<p>Your skin does most of its repair work while you sleep. That means your evening routine is <strong>the most important skincare window of your day</strong>.</p><h3>Step 1: Double Cleanse</h3><p>Start with an oil-based cleanser, then follow with a gentle water-based cleanser like our Rose Petal Gentle Cleanser.</p><h3>Step 2: Treatment Serum</h3><p>At night, switch to a retinol-based serum like our Retinol Night Serum. It accelerates cell turnover while you sleep.</p><h3>Step 3: Rich Moisturizer</h3><p>Seal everything in with our Midnight Recovery Cream — it creates a moisture barrier that locks in all your serums overnight.</p>', NULL, 1, 'published', 'KathyJane Team', 287, '2025-01-25 09:00:00', '2026-03-24 18:03:23', '2026-03-24 18:03:23'),
(3, 'How Stress Shows Up on Your Skin', 'stress-shows-on-skin', 'That breakout before a big meeting isn\'t a coincidence. Here\'s the science behind the stress-skin connection.', '<p>Stress doesn\'t just feel bad — it <strong>shows up on your face</strong>. When you\'re stressed, your body produces cortisol, which triggers excess oil, breaks down collagen, and impairs your skin barrier.</p><h3>5 Ways to Break the Cycle</h3><p><strong>1. Move your body.</strong> Even 20 minutes of walking reduces cortisol. <strong>2. Protect your sleep.</strong> Skin repairs between 10 PM and 2 AM. <strong>3. Simplify your routine.</strong> Stressed skin is sensitive skin. <strong>4. Breathe intentionally.</strong> Box breathing reduces cortisol measurably. <strong>5. Nourish from within.</strong> Omega-3s, antioxidants, and zinc all support skin repair.</p>', NULL, 2, 'published', 'KathyJane Team', 198, '2025-02-05 09:00:00', '2026-03-24 18:03:23', '2026-03-24 18:03:23'),
(4, 'Morning Meditation for Glowing Skin', 'morning-meditation-glowing-skin', 'A 10-minute guided meditation designed specifically for beauty — because radiance starts from within.', '<p>At KathyJane, we believe your skincare routine should be a <strong>moment of intention</strong>. This 10-minute meditation is designed to do while your Vitamin C serum absorbs.</p><h3>The Practice</h3><p><strong>Minutes 1-3:</strong> Settle. Five deep breaths. <strong>Minutes 4-6:</strong> Body scan — relax your jaw, forehead, shoulders. <strong>Minutes 7-9:</strong> Set an intention for how you want to feel today. <strong>Minute 10:</strong> Place your hands on your face and express gratitude for your skin.</p><h3>Why This Works</h3><p>Meditation reduces cortisol, lowers inflammation, and improves sleep quality. Regular meditators have measurably healthier skin barriers.</p>', NULL, 2, 'published', 'KathyJane Team', 156, '2025-02-20 09:00:00', '2026-03-24 18:03:23', '2026-03-24 18:03:23'),
(5, '7 Foods That Make Your Skin Glow', 'seven-foods-for-glowing-skin', 'What you eat shows on your face. Here are 7 foods dermatologists and nutritionists agree on.', '<p>Which foods actually make a difference? Here are the <strong>7 with the strongest evidence</strong>:</p><p><strong>1. Oranges & Citrus</strong> — Vitamin C boosts collagen. <strong>2. Wild Salmon</strong> — Omega-3s reduce inflammation. <strong>3. Avocado</strong> — Vitamin E works synergistically with Vitamin C. <strong>4. Sweet Potatoes</strong> — Beta-carotene is food-based retinol. <strong>5. Green Tea</strong> — Catechins protect against sun damage. <strong>6. Walnuts</strong> — Best plant source of omega-3s. <strong>7. Dark Leafy Greens</strong> — Vitamins A, C, K for cell turnover.</p>', NULL, 3, 'published', 'Nutrition Team', 423, '2025-02-12 09:00:00', '2026-03-24 18:03:23', '2026-03-24 18:03:23'),
(6, 'The Smoothie Recipe Our Founder Drinks Every Morning', 'founder-morning-smoothie', 'Orange, turmeric, collagen, and a secret ingredient — the smoothie that powers our founder\'s glow.', '<p>Our founder calls it the <strong>\"Glow Fuel\"</strong>. Here\'s the recipe:</p><p><strong>Ingredients:</strong> 1 fresh orange, 1/2 cup frozen mango, 1 tbsp collagen peptides, 1/2 tsp turmeric, 1/4 tsp black pepper, 1 tbsp almond butter, 1 cup oat milk, 3 ice cubes.</p><p><strong>Blend until smooth. Drink immediately.</strong></p><h3>Why Each Ingredient Matters</h3><p><strong>Orange + Mango:</strong> Vitamin C powerhouse. <strong>Collagen peptides:</strong> The protein your skin is made of. <strong>Turmeric:</strong> Anti-inflammatory. Black pepper increases absorption by 2000%. <strong>Almond butter:</strong> Vitamin E + healthy fats.</p>', NULL, 3, 'published', 'KathyJane Team', 568, '2025-03-01 09:00:00', '2026-03-24 18:03:23', '2026-03-24 23:20:46'),
(7, 'Sunday Reset: A Full-Body Self-Care Ritual', 'sunday-reset-ritual', 'One hour on Sunday that sets up your entire week — skincare, wellness, and mental health.', '<p>The <strong>Sunday Reset</strong> is a 60-minute ritual: part skincare, part wellness, part mental health check-in.</p><h3>Phase 1: The Body (20 min)</h3><p>Dry brushing, then a warm epsom salt bath with lavender oil.</p><h3>Phase 2: The Skin (20 min)</h3><p>Apply a generous layer of our Shea Butter Hydration Mask. While it works, do a gentle facial massage with upward strokes.</p><h3>Phase 3: The Mind (20 min)</h3><p>Brew tea. Sit quietly. Journal three questions: What went well? What drained me? What\'s one intention for the week?</p><h3>Why Rituals Matter</h3><p>A ritual is different from a routine. A routine is something you do. A ritual is something you <em>experience</em>.</p>', NULL, 4, 'published', 'KathyJane Team', 312, '2025-02-16 09:00:00', '2026-03-24 18:03:23', '2026-03-24 18:03:23'),
(8, 'Sheet Masks vs. Wash-Off Masks: Which is Better?', 'sheet-masks-vs-washoff', 'The great mask debate — pros and cons of each type.', '<p>Draft content — this post is still being written.</p>', NULL, 1, 'draft', 'KathyJane Team', 0, NULL, '2026-03-24 18:03:23', '2026-03-24 18:03:23');

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

--
-- Table structure for table `bls_employment`
--

CREATE TABLE `bls_employment` (
  `id` int(11) NOT NULL,
  `series_id` varchar(50) NOT NULL,
  `series_label` varchar(200) DEFAULT NULL,
  `data_year` smallint(6) NOT NULL,
  `period` char(3) NOT NULL,
  `period_name` varchar(20) DEFAULT NULL,
  `value` decimal(12,2) NOT NULL,
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

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

--
-- Table structure for table `categories`
--

CREATE TABLE `categories` (
  `category_id` int(11) NOT NULL,
  `category_name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

--
-- Dumping data for table `categories`
--

INSERT INTO `categories` (`category_id`, `category_name`, `description`, `created_at`) VALUES
(1, 'Cleansers', 'Gentle cleansers to remove impurities and prep your skin', '2026-03-24 18:03:23'),
(2, 'Moisturizers', 'Hydrating formulas for all-day softness', '2026-03-24 18:03:23'),
(3, 'Serums', 'Concentrated treatments for targeted skin concerns', '2026-03-24 18:03:23'),
(4, 'Masks', 'Weekly treatments for deep nourishment', '2026-03-24 18:03:23'),
(5, 'Sunscreen', 'Broad-spectrum protection for every skin type', '2026-03-24 18:03:23');

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

--
-- Table structure for table `census_demographics`
--

CREATE TABLE `census_demographics` (
  `id` int(11) NOT NULL,
  `state_fips` char(2) NOT NULL,
  `state_name` varchar(100) NOT NULL,
  `total_population` bigint(20) DEFAULT NULL,
  `median_income` decimal(10,2) DEFAULT NULL,
  `median_age` decimal(5,2) DEFAULT NULL,
  `female_pct` decimal(5,2) DEFAULT NULL,
  `data_year` smallint(6) NOT NULL,
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

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

--
-- Table structure for table `inventory_receipts`
--

CREATE TABLE `inventory_receipts` (
  `receipt_id` int(11) NOT NULL,
  `producer_id` int(11) NOT NULL,
  `receipt_date` date NOT NULL,
  `reference_number` varchar(50) DEFAULT NULL,
  `status` enum('pending','received','inspected','stocked') DEFAULT 'received',
  `total_units` int(11) DEFAULT 0,
  `total_cost` decimal(10,2) DEFAULT 0.00,
  `received_by` varchar(100) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

--
-- Dumping data for table `inventory_receipts`
--

INSERT INTO `inventory_receipts` (`receipt_id`, `producer_id`, `receipt_date`, `reference_number`, `status`, `total_units`, `total_cost`, `received_by`, `notes`, `created_at`, `updated_at`) VALUES
(1, 1, '2025-01-15', 'BW-2025-0042', 'stocked', 200, 3050.00, 'KJ Admin', NULL, '2026-03-24 18:03:23', '2026-03-24 18:03:23'),
(2, 2, '2025-01-20', 'PF-2025-0118', 'stocked', 150, 2150.00, 'KJ Admin', NULL, '2026-03-24 18:03:23', '2026-03-24 18:03:23'),
(3, 4, '2025-02-01', 'SG-2025-0007', 'stocked', 120, 2260.00, 'KJ Admin', NULL, '2026-03-24 18:03:23', '2026-03-24 18:03:23'),
(4, 3, '2025-02-10', 'CS-2025-0033', 'inspected', 80, 760.00, 'KJ Admin', NULL, '2026-03-24 18:03:23', '2026-03-24 18:03:23'),
(5, 1, '2025-03-01', 'BW-2025-0089', 'pending', 100, 1550.00, 'KJ Admin', NULL, '2026-03-24 18:03:23', '2026-03-24 18:03:23');

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

--
-- Table structure for table `inventory_receipt_items`
--

CREATE TABLE `inventory_receipt_items` (
  `item_id` int(11) NOT NULL,
  `receipt_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  `cost_per_unit` decimal(10,2) NOT NULL,
  `line_cost` decimal(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

--
-- Dumping data for table `inventory_receipt_items`
--

INSERT INTO `inventory_receipt_items` (`item_id`, `receipt_id`, `product_id`, `quantity`, `cost_per_unit`, `line_cost`) VALUES
(1, 1, 1, 60, 12.50, 750.00),
(2, 1, 2, 80, 18.00, 1440.00),
(3, 1, 7, 60, 11.00, 660.00),
(4, 2, 3, 50, 16.00, 800.00),
(5, 2, 4, 40, 14.00, 560.00),
(6, 2, 9, 60, 13.00, 780.00),
(7, 3, 5, 70, 11.00, 770.00),
(8, 3, 8, 50, 22.00, 1100.00),
(9, 4, 6, 50, 10.50, 525.00),
(10, 4, 10, 30, 8.00, 240.00);

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

--
-- Table structure for table `newsletter_subscribers`
--

CREATE TABLE `newsletter_subscribers` (
  `subscriber_id` int(11) NOT NULL,
  `email` varchar(200) NOT NULL,
  `first_name` varchar(100) DEFAULT NULL,
  `subscribed_at` datetime DEFAULT current_timestamp(),
  `is_active` tinyint(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

--
-- Dumping data for table `newsletter_subscribers`
--

INSERT INTO `newsletter_subscribers` (`subscriber_id`, `email`, `first_name`, `subscribed_at`, `is_active`) VALUES
(1, 'jane.doe@example.com', 'Jane', '2026-03-24 18:03:23', 1),
(2, 'sam.smith@example.com', 'Sam', '2026-03-24 18:03:23', 1),
(3, 'aisha.khan@example.com', 'Aisha', '2026-03-24 18:03:23', 1);

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

--
-- Table structure for table `oem_producers`
--

CREATE TABLE `oem_producers` (
  `producer_id` int(11) NOT NULL,
  `company_name` varchar(200) NOT NULL,
  `contact_name` varchar(100) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  `phone` varchar(30) DEFAULT NULL,
  `address` varchar(300) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `state` varchar(50) DEFAULT NULL,
  `country` varchar(100) DEFAULT 'USA',
  `specialization` varchar(200) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `notes` text DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

--
-- Dumping data for table `oem_producers`
--

INSERT INTO `oem_producers` (`producer_id`, `company_name`, `contact_name`, `email`, `phone`, `address`, `city`, `state`, `country`, `specialization`, `is_active`, `notes`, `created_at`, `updated_at`) VALUES
(1, 'BeautyWorks Manufacturing', 'Diana Reyes', 'diana@beautyworks.com', '555-7001', '1200 Innovation Blvd', 'Los Angeles', 'CA', 'USA', 'Serums and liquid formulations', 1, NULL, '2026-03-24 18:03:23', '2026-03-24 18:03:23'),
(2, 'PureForm Labs', 'Thomas Grant', 'tgrant@pureformlabs.com', '555-7002', '88 Pharma Drive', 'Newark', 'NJ', 'USA', 'Creams, masks, and moisturizers', 1, NULL, '2026-03-24 18:03:23', '2026-03-24 18:03:23'),
(3, 'CoastalSun Packaging', 'Marie Dubois', 'marie@coastalsun.com', '555-7003', '340 Harbor Way', 'San Diego', 'CA', 'USA', 'Sunscreen and SPF products', 1, NULL, '2026-03-24 18:03:23', '2026-03-24 18:03:23'),
(4, 'SeoulGlow Co.', 'Minji Kim', 'minji@seoulglow.co.kr', '555-7004', '25 Gangnam-daero', 'Seoul', '', 'South Korea', 'K-beauty formulations and green tea products', 1, NULL, '2026-03-24 18:03:23', '2026-03-24 18:03:23');

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

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

CREATE TABLE `orders` (
  `order_id` int(11) NOT NULL,
  `customer_id` int(11) NOT NULL,
  `order_date` datetime DEFAULT current_timestamp(),
  `status` enum('pending','processing','shipped','delivered','cancelled') DEFAULT 'pending',
  `subtotal` decimal(10,2) NOT NULL DEFAULT 0.00,
  `tax_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `total_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `shipping_name` varchar(200) DEFAULT NULL,
  `shipping_address` varchar(300) DEFAULT NULL,
  `shipping_city` varchar(100) DEFAULT NULL,
  `shipping_state` varchar(50) DEFAULT NULL,
  `shipping_zip` varchar(20) DEFAULT NULL,
  `payment_method` varchar(50) DEFAULT 'simulated',
  `payment_status` enum('pending','paid','refunded') DEFAULT 'paid',
  `notes` text DEFAULT NULL,
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

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

INSERT INTO `orders` (`order_id`, `customer_id`, `order_date`, `status`, `subtotal`, `tax_amount`, `total_amount`, `shipping_name`, `shipping_address`, `shipping_city`, `shipping_state`, `shipping_zip`, `payment_method`, `payment_status`, `notes`, `updated_at`) VALUES
(1, 1, '2026-03-24 18:03:23', 'delivered', 96.00, 7.68, 103.68, 'Elena Rodriguez', '742 Evergreen Terrace', 'Austin', 'TX', '73301', 'simulated', 'paid', NULL, '2026-03-24 18:03:23'),
(2, 2, '2026-03-24 18:03:23', 'shipped', 54.00, 4.32, 58.32, 'Marcus Chen', '1600 Pennsylvania Ave', 'Philadelphia', 'PA', '19103', 'simulated', 'paid', NULL, '2026-03-24 18:03:23'),
(3, 1, '2026-03-24 18:03:23', 'processing', 70.00, 5.60, 75.60, 'Elena Rodriguez', '742 Evergreen Terrace', 'Austin', 'TX', '73301', 'simulated', 'paid', NULL, '2026-03-24 18:03:23'),
(4, 3, '2026-03-24 18:03:23', 'pending', 116.00, 9.28, 125.28, 'Aisha Johnson', '350 Fifth Avenue', 'New York', 'NY', '10118', 'simulated', 'paid', NULL, '2026-03-24 18:03:23'),
(5, 4, '2026-03-24 20:23:40', 'pending', 62.00, 4.96, 66.96, 'Leena Nair', '123 Testing', 'Testing', 'PA', '55555', 'simulated', 'paid', NULL, '2026-03-24 20:23:40');

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

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

CREATE TABLE `order_items` (
  `item_id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `quantity` int(11) NOT NULL DEFAULT 1,
  `price_each` decimal(10,2) NOT NULL,
  `line_total` decimal(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

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

INSERT INTO `order_items` (`item_id`, `order_id`, `product_id`, `quantity`, `price_each`, `line_total`) VALUES
(1, 1, 1, 1, 28.00, 28.00),
(2, 1, 3, 1, 42.00, 42.00),
(3, 1, 5, 1, 32.00, 32.00),
(4, 2, 2, 1, 54.00, 54.00),
(5, 3, 1, 1, 28.00, 28.00),
(6, 3, 3, 1, 42.00, 42.00),
(7, 4, 2, 1, 54.00, 54.00),
(8, 4, 8, 1, 62.00, 62.00),
(9, 5, 8, 1, 62.00, 62.00);

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

--
-- Table structure for table `post_products`
--

CREATE TABLE `post_products` (
  `post_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

--
-- Dumping data for table `post_products`
--

INSERT INTO `post_products` (`post_id`, `product_id`) VALUES
(2, 1),
(3, 1),
(1, 2),
(4, 2),
(6, 2),
(2, 3),
(7, 3),
(7, 4),
(3, 5),
(5, 5),
(1, 6),
(2, 8),
(7, 9);

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

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

CREATE TABLE `products` (
  `product_id` int(11) NOT NULL,
  `product_name` varchar(200) NOT NULL,
  `description` text DEFAULT NULL,
  `price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `cost_price` decimal(10,2) DEFAULT 0.00,
  `stock_qty` int(11) NOT NULL DEFAULT 0,
  `reorder_level` int(11) DEFAULT 10,
  `category_id` int(11) DEFAULT NULL,
  `supplier_id` int(11) DEFAULT NULL,
  `producer_id` int(11) DEFAULT NULL,
  `image_url` varchar(255) DEFAULT 'default.jpg',
  `is_featured` tinyint(1) DEFAULT 0,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `image_file` varchar(100) DEFAULT NULL,
  `long_description` text DEFAULT NULL,
  `ingredients` text DEFAULT NULL,
  `usage_instructions` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

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

INSERT INTO `products` (`product_id`, `product_name`, `description`, `price`, `cost_price`, `stock_qty`, `reorder_level`, `category_id`, `supplier_id`, `producer_id`, `image_url`, `is_featured`, `created_at`, `updated_at`, `image_file`, `long_description`, `ingredients`, `usage_instructions`) VALUES
(1, 'Rose Petal Gentle Cleanser', 'A soothing rose-infused cleanser for sensitive skin.', 28.00, 12.50, 120, 15, 1, 1, 1, 'rose-cleanser.jpg', 1, '2026-03-24 18:03:23', '2026-03-24 20:35:23', 'product-01.png', 'Our Rose Petal Gentle Cleanser transforms your daily cleansing ritual into a luxurious experience. Infused with real rose petal extract and soothing rosewater.', 'Purified Water, Rosa Damascena Flower Water, Glycerin, Sodium Cocoyl Glutamate, Rosa Centifolia Flower Extract, Hyaluronic Acid, Aloe Barbadensis Leaf Juice, Vitamin E, Chamomile Extract', 'Apply 1-2 pumps to damp skin morning and evening. Massage gently in circular motions for 30 seconds. Rinse thoroughly.'),
(2, 'Vitamin C Brightening Serum', 'Powerful antioxidant serum with 15% vitamin C for radiant skin.', 54.00, 18.00, 85, 20, 3, 2, 1, 'vitc-serum.jpg', 1, '2026-03-24 18:03:23', '2026-03-24 20:35:23', 'product-02.png', 'Reveal your brightest complexion with our Vitamin C Brightening Serum. 15% L-Ascorbic Acid with Vitamin E and Ferulic Acid creates a powerful antioxidant shield.', 'Purified Water, L-Ascorbic Acid (15%), Ethoxydiglycol, Vitamin E (Tocopherol), Ferulic Acid, Panthenol, Sodium Hyaluronate, Citrus Peel Oil', 'Apply 4-5 drops to clean, dry skin every morning. Wait 1-2 minutes before moisturizer. Always follow with SPF 30+.'),
(3, 'Midnight Recovery Cream', 'Rich overnight moisturizer with lavender and evening primrose oil.', 42.00, 16.00, 95, 15, 2, 1, 2, 'midnight-cream.jpg', 1, '2026-03-24 18:03:23', '2026-03-24 20:35:23', 'product-03.png', 'Let your skin recover while you sleep. Rich, velvety night cream with bakuchiol, peptide complexes, and lavender essential oil.', 'Purified Water, Shea Butter, Squalane, Bakuchiol, Palmitoyl Tripeptide-1, Lavender Oil, Ceramide NP, Jojoba Seed Oil, Evening Primrose Oil, Vitamin E', 'Apply generously to clean face and neck as the last step of your evening routine. Use nightly.'),
(4, 'Shea Butter Hydration Mask', 'Deep-conditioning mask with raw shea butter from West Africa.', 36.00, 14.00, 60, 10, 4, 3, 2, 'shea-mask.jpg', 0, '2026-03-24 18:03:23', '2026-03-24 20:35:23', 'product-04.png', 'Drench your skin in deep, lasting hydration with ethically-sourced raw shea butter, coconut oil, and colloidal oatmeal.', 'Purified Water, Shea Butter, Coconut Oil, Oat Kernel Extract, Glycerin, Mango Seed Butter, Allantoin, Ceramide AP, Squalane, Chamomile Extract', 'Apply a thick layer to clean skin 2-3 times per week. Leave on for 15-20 minutes, then remove with a warm cloth.'),
(5, 'Green Tea Daily Moisturizer', 'Lightweight moisturizer with green tea extract and hyaluronic acid.', 32.00, 11.00, 110, 15, 2, 4, 4, 'greentea-moist.jpg', 1, '2026-03-24 18:03:23', '2026-03-24 20:35:23', 'product-05.png', 'Balance and protect your skin with antioxidant-rich green tea extract, niacinamide, and aloe vera. Lightweight, fast-absorbing.', 'Purified Water, Green Tea Leaf Extract, Niacinamide (5%), Aloe Vera, Hyaluronic Acid, Glycerin, Squalane, Centella Asiatica, Zinc PCA, Vitamin E', 'Apply 1-2 pumps to clean face and neck every morning. Layers perfectly under sunscreen and makeup.'),
(6, 'Ocean Mineral Sunscreen SPF 50', 'Reef-safe mineral sunscreen with a silky, non-greasy finish.', 38.00, 10.50, 75, 12, 5, 2, 3, 'ocean-spf.jpg', 0, '2026-03-24 18:03:23', '2026-03-24 20:35:23', 'product-06.png', 'Broad-spectrum mineral sunscreen with zinc oxide, titanium dioxide, ocean minerals, and sea kelp. Zero white cast, reef-safe.', 'Purified Water, Zinc Oxide (20%), Titanium Dioxide (5%), Sea Kelp Extract, Dead Sea Minerals, Hyaluronic Acid, Aloe Vera, Squalane, Vitamin E', 'Apply generously 15 minutes before sun exposure. Reapply every 2 hours.'),
(7, 'Charcoal Deep Pore Cleanser', 'Activated charcoal cleanser that draws out impurities.', 26.00, 11.00, 90, 12, 1, 1, 1, 'charcoal-cleanser.jpg', 0, '2026-03-24 18:03:23', '2026-03-24 20:35:23', 'product-07.png', 'Activated bamboo charcoal draws out deep-seated impurities while tea tree oil and salicylic acid provide gentle exfoliation.', 'Purified Water, Activated Bamboo Charcoal, Salicylic Acid (2%), Tea Tree Oil, Aloe Vera, Green Tea Extract, Glycerin, Kaolin Clay, Witch Hazel', 'Apply 1-2 pumps to damp skin in the evening. Massage gently, focusing on the T-zone. Rinse with lukewarm water.'),
(8, 'Retinol Night Serum', 'Age-defying retinol serum for smoother, firmer skin overnight.', 62.00, 22.00, 39, 10, 3, 4, 4, 'retinol-serum.jpg', 1, '2026-03-24 18:03:23', '2026-03-24 20:35:23', 'product-08.png', 'Encapsulated retinol (0.5%) in a time-release system that minimizes irritation while maximizing results. Smoother skin overnight.', 'Purified Water, Encapsulated Retinol (0.5%), Squalane, Palmitoyl Tripeptide-5, Rosemary Extract, Niacinamide, Hyaluronic Acid, Jojoba Seed Oil, Ceramide NP', 'Apply 3-4 drops every evening. Start every other night for the first two weeks. Always use SPF 30+ the next morning.'),
(9, 'Honey Glow Enzyme Mask', 'Exfoliating enzyme mask with Manuka honey for a natural glow.', 34.00, 13.00, 55, 10, 4, 3, 2, 'honey-mask.jpg', 0, '2026-03-24 18:03:23', '2026-03-24 20:35:23', 'product-09.png', 'Raw Manuka honey and papaya enzymes gently dissolve dead skin cells for an instant healthy glow and silky-smooth texture.', 'Raw Manuka Honey, Papaya Fruit Extract, Propolis Extract, Royal Jelly, Chamomile Extract, Glycerin, Pineapple Extract, Turmeric, Vitamin C, Vitamin E', 'Apply a generous layer to clean skin. Leave on for 10-15 minutes. Rinse with warm water. Use 1-2 times per week.'),
(10, 'Daily Defense SPF 30 Lotion', 'Everyday sun protection with added moisturizing benefits.', 24.00, 8.00, 130, 20, 5, 2, 3, 'daily-spf.jpg', 0, '2026-03-24 18:03:23', '2026-03-24 20:35:23', 'product-10.png', 'Lightweight daily protection with broad-spectrum UVA/UVB protection, hyaluronic acid, aloe vera, and vitamin E. Invisible, non-greasy.', 'Purified Water, Zinc Oxide (15%), Hyaluronic Acid, Aloe Vera, Vitamin E, Glycerin, Squalane, Niacinamide, Green Tea Extract, Chamomile, Jojoba Seed Oil', 'Apply liberally every morning. Reapply every 2 hours during sun exposure. Suitable for all skin types.');

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

--
-- Table structure for table `suppliers`
--

CREATE TABLE `suppliers` (
  `supplier_id` int(11) NOT NULL,
  `company_name` varchar(150) NOT NULL,
  `contact_name` varchar(100) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  `phone` varchar(30) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `country` varchar(100) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

--
-- Dumping data for table `suppliers`
--

INSERT INTO `suppliers` (`supplier_id`, `company_name`, `contact_name`, `email`, `phone`, `city`, `country`, `created_at`) VALUES
(1, 'GlowLab Ingredients', 'Maria Chen', 'maria@glowlab.com', '555-0101', 'San Francisco', 'USA', '2026-03-24 18:03:23'),
(2, 'PureSkin Europe', 'Luca Bertrand', 'luca@pureskineu.com', '555-0202', 'Lyon', 'France', '2026-03-24 18:03:23'),
(3, 'Botanical Source Co.', 'Amara Osei', 'amara@botanicalsrc.com', '555-0303', 'Accra', 'Ghana', '2026-03-24 18:03:23'),
(4, 'K-Beauty Direct', 'Jina Park', 'jina@kbeautyd.com', '555-0404', 'Seoul', 'South Korea', '2026-03-24 18:03:23');

--
-- Indexes for table `api_cache`
--
ALTER TABLE `api_cache`
  ADD PRIMARY KEY (`cache_id`),
  ADD UNIQUE KEY `cache_key` (`cache_key`),
  ADD KEY `idx_key` (`cache_key`),
  ADD KEY `idx_expires` (`expires_at`);

--
-- Indexes for table `blog_categories`
--
ALTER TABLE `blog_categories`
  ADD PRIMARY KEY (`blog_cat_id`),
  ADD UNIQUE KEY `cat_slug` (`cat_slug`);

--
-- Indexes for table `blog_posts`
--
ALTER TABLE `blog_posts`
  ADD PRIMARY KEY (`post_id`),
  ADD UNIQUE KEY `slug` (`slug`),
  ADD KEY `blog_cat_id` (`blog_cat_id`),
  ADD KEY `idx_status_date` (`status`,`published_at`),
  ADD KEY `idx_slug` (`slug`);

--
-- Indexes for table `bls_employment`
--
ALTER TABLE `bls_employment`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_series_period` (`series_id`,`data_year`,`period`);

--
-- Indexes for table `categories`
--
ALTER TABLE `categories`
  ADD PRIMARY KEY (`category_id`);

--
-- Indexes for table `census_demographics`
--
ALTER TABLE `census_demographics`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_state_year` (`state_fips`,`data_year`);

--
-- Indexes for table `inventory_receipts`
--
ALTER TABLE `inventory_receipts`
  ADD PRIMARY KEY (`receipt_id`),
  ADD KEY `producer_id` (`producer_id`);

--
-- Indexes for table `inventory_receipt_items`
--
ALTER TABLE `inventory_receipt_items`
  ADD PRIMARY KEY (`item_id`),
  ADD KEY `receipt_id` (`receipt_id`),
  ADD KEY `product_id` (`product_id`);

--
-- Indexes for table `newsletter_subscribers`
--
ALTER TABLE `newsletter_subscribers`
  ADD PRIMARY KEY (`subscriber_id`),
  ADD UNIQUE KEY `email` (`email`);

--
-- Indexes for table `oem_producers`
--
ALTER TABLE `oem_producers`
  ADD PRIMARY KEY (`producer_id`);

--
-- Indexes for table `orders`
--
ALTER TABLE `orders`
  ADD PRIMARY KEY (`order_id`),
  ADD KEY `customer_id` (`customer_id`);

--
-- Indexes for table `order_items`
--
ALTER TABLE `order_items`
  ADD PRIMARY KEY (`item_id`),
  ADD KEY `order_id` (`order_id`),
  ADD KEY `product_id` (`product_id`);

--
-- Indexes for table `post_products`
--
ALTER TABLE `post_products`
  ADD PRIMARY KEY (`post_id`,`product_id`),
  ADD KEY `product_id` (`product_id`);

--
-- Indexes for table `products`
--
ALTER TABLE `products`
  ADD PRIMARY KEY (`product_id`),
  ADD KEY `category_id` (`category_id`),
  ADD KEY `supplier_id` (`supplier_id`),
  ADD KEY `producer_id` (`producer_id`);

--
-- Indexes for table `suppliers`
--
ALTER TABLE `suppliers`
  ADD PRIMARY KEY (`supplier_id`);

--
-- AUTO_INCREMENT for table `api_cache`
--
ALTER TABLE `api_cache`
  MODIFY `cache_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `blog_categories`
--
ALTER TABLE `blog_categories`
  MODIFY `blog_cat_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `blog_posts`
--
ALTER TABLE `blog_posts`
  MODIFY `post_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

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

--
-- AUTO_INCREMENT for table `categories`
--
ALTER TABLE `categories`
  MODIFY `category_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

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

--
-- AUTO_INCREMENT for table `inventory_receipts`
--
ALTER TABLE `inventory_receipts`
  MODIFY `receipt_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `inventory_receipt_items`
--
ALTER TABLE `inventory_receipt_items`
  MODIFY `item_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;

--
-- AUTO_INCREMENT for table `newsletter_subscribers`
--
ALTER TABLE `newsletter_subscribers`
  MODIFY `subscriber_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `oem_producers`
--
ALTER TABLE `oem_producers`
  MODIFY `producer_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `orders`
--
ALTER TABLE `orders`
  MODIFY `order_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `order_items`
--
ALTER TABLE `order_items`
  MODIFY `item_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;

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

--
-- AUTO_INCREMENT for table `suppliers`
--
ALTER TABLE `suppliers`
  MODIFY `supplier_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `blog_posts`
--
ALTER TABLE `blog_posts`
  ADD CONSTRAINT `blog_posts_ibfk_1` FOREIGN KEY (`blog_cat_id`) REFERENCES `blog_categories` (`blog_cat_id`);

--
-- Constraints for table `inventory_receipts`
--
ALTER TABLE `inventory_receipts`
  ADD CONSTRAINT `inventory_receipts_ibfk_1` FOREIGN KEY (`producer_id`) REFERENCES `oem_producers` (`producer_id`);

--
-- Constraints for table `inventory_receipt_items`
--
ALTER TABLE `inventory_receipt_items`
  ADD CONSTRAINT `inventory_receipt_items_ibfk_1` FOREIGN KEY (`receipt_id`) REFERENCES `inventory_receipts` (`receipt_id`),
  ADD CONSTRAINT `inventory_receipt_items_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`product_id`);

--
-- Constraints for table `orders`
--
ALTER TABLE `orders`
  ADD CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`);

--
-- Constraints for table `order_items`
--
ALTER TABLE `order_items`
  ADD CONSTRAINT `order_items_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`),
  ADD CONSTRAINT `order_items_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`product_id`);

--
-- Constraints for table `post_products`
--
ALTER TABLE `post_products`
  ADD CONSTRAINT `post_products_ibfk_1` FOREIGN KEY (`post_id`) REFERENCES `blog_posts` (`post_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `post_products_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`product_id`) ON DELETE CASCADE;

--
-- Constraints for table `products`
--
ALTER TABLE `products`
  ADD CONSTRAINT `products_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`category_id`),
  ADD CONSTRAINT `products_ibfk_2` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`supplier_id`),
  ADD CONSTRAINT `products_ibfk_3` FOREIGN KEY (`producer_id`) REFERENCES `oem_producers` (`producer_id`);
COMMIT;

