-- ============================================
-- AmeasyShop Products Database Schema
-- ============================================

-- Create products table
CREATE TABLE IF NOT EXISTS `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `sku` varchar(100) NOT NULL,
  `brand` varchar(100) DEFAULT NULL,
  `category` varchar(100) NOT NULL,
  `subcategory` varchar(100) DEFAULT NULL,
  `short_description` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  
  -- Pricing
  `regular_price` decimal(10,2) NOT NULL,
  `sale_price` decimal(10,2) DEFAULT NULL,
  `cost_price` decimal(10,2) DEFAULT NULL,
  
  -- Inventory
  `stock_quantity` int(11) NOT NULL DEFAULT 0,
  `low_stock_alert` int(11) DEFAULT 10,
  `stock_status` enum('in_stock','out_of_stock','on_backorder') DEFAULT 'in_stock',
  
  -- Product Details
  `weight` decimal(10,2) DEFAULT NULL COMMENT 'in kg',
  `length` decimal(10,2) DEFAULT NULL COMMENT 'in cm',
  `width` decimal(10,2) DEFAULT NULL COMMENT 'in cm',
  `height` decimal(10,2) DEFAULT NULL COMMENT 'in cm',
  `material` varchar(100) DEFAULT NULL,
  `color` varchar(100) DEFAULT NULL,
  `size` varchar(50) DEFAULT NULL,
  `warranty` varchar(100) DEFAULT NULL,
  `country_origin` varchar(100) DEFAULT NULL,
  
  -- SEO
  `meta_title` varchar(255) DEFAULT NULL,
  `meta_description` varchar(255) DEFAULT NULL,
  `tags` text DEFAULT NULL,
  
  -- Status
  `status` enum('active','inactive','draft') DEFAULT 'draft',
  `featured` enum('yes','no') DEFAULT 'no',
  `show_homepage` tinyint(1) DEFAULT 0,
  `allow_reviews` tinyint(1) DEFAULT 1,
  
  -- Tracking
  `views_count` int(11) DEFAULT 0,
  `sales_count` int(11) DEFAULT 0,
  
  -- Timestamps
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_by` int(11) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  
  PRIMARY KEY (`id`),
  UNIQUE KEY `sku` (`sku`),
  KEY `slug` (`slug`),
  KEY `category` (`category`),
  KEY `status` (`status`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================

-- Create product_images table
CREATE TABLE IF NOT EXISTS `product_images` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `image_url` varchar(255) NOT NULL,
  `image_alt` varchar(255) DEFAULT NULL,
  `is_primary` tinyint(1) DEFAULT 0,
  `sort_order` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `product_id` (`product_id`),
  CONSTRAINT `fk_product_images` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================

-- Create product_reviews table (optional - for future use)
CREATE TABLE IF NOT EXISTS `product_reviews` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `customer_name` varchar(100) DEFAULT NULL,
  `customer_email` varchar(100) DEFAULT NULL,
  `rating` tinyint(1) NOT NULL CHECK (`rating` >= 1 AND `rating` <= 5),
  `title` varchar(255) DEFAULT NULL,
  `comment` text DEFAULT NULL,
  `status` enum('pending','approved','rejected') DEFAULT 'pending',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `product_id` (`product_id`),
  KEY `status` (`status`),
  CONSTRAINT `fk_product_reviews` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================

-- Create product_variants table (optional - for products with variations like size/color)
CREATE TABLE IF NOT EXISTS `product_variants` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `variant_name` varchar(100) NOT NULL COMMENT 'e.g., Size, Color',
  `variant_value` varchar(100) NOT NULL COMMENT 'e.g., Large, Red',
  `sku` varchar(100) DEFAULT NULL,
  `price_modifier` decimal(10,2) DEFAULT 0.00,
  `stock_quantity` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `product_id` (`product_id`),
  CONSTRAINT `fk_product_variants` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================

-- Insert sample products (optional)
INSERT INTO `products` 
(`product_name`, `slug`, `sku`, `brand`, `category`, `subcategory`, `short_description`, `description`, 
`regular_price`, `sale_price`, `stock_quantity`, `status`, `featured`, `material`, `color`, `warranty`) 
VALUES
('Wireless Bluetooth Headphones', 'wireless-bluetooth-headphones', 'WBH-001', 'TechSound', 'electronics', 'Audio', 
'Premium wireless headphones with noise cancellation', 
'Experience crystal clear sound with our premium wireless Bluetooth headphones. Features active noise cancellation, 30-hour battery life, and comfortable over-ear design.', 
99.99, 79.99, 50, 'active', 'yes', 'Plastic/Metal', 'Black', '1 year'),

('Organic Cotton T-Shirt', 'organic-cotton-tshirt', 'OCT-001', 'EcoWear', 'clothing', 'Mens', 
'Comfortable 100% organic cotton t-shirt', 
'Made from 100% certified organic cotton, this t-shirt offers superior comfort and breathability. Perfect for everyday wear.', 
29.99, 24.99, 100, 'active', 'no', 'Organic Cotton', 'White', '6 months'),

('Stainless Steel Water Bottle', 'stainless-steel-water-bottle', 'SSWB-001', 'HydroLife', 'home', 'Kitchen', 
'Insulated water bottle keeps drinks cold for 24 hours', 
'Double-wall vacuum insulated stainless steel bottle. Keeps beverages cold for 24 hours and hot for 12 hours. BPA-free and eco-friendly.', 
34.99, NULL, 75, 'active', 'yes', 'Stainless Steel', 'Blue', '5 years');

-- ============================================
-- END OF SQL SCHEMA
-- ============================================
