-- Create affiliate products table
-- IMPORTANT: Select your database in phpMyAdmin before running this!
-- Or replace 'your_database_name' below with your actual database name

-- USE your_database_name;

CREATE TABLE IF NOT EXISTS `products` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_title` varchar(255) NOT NULL,
  `product_description` text DEFAULT NULL,
  `product_image` varchar(255) DEFAULT NULL,
  `product_image_cdn_url` varchar(500) DEFAULT NULL,
  `product_use_cdn` tinyint(1) DEFAULT 0,
  `product_store` int(11) NOT NULL,
  `product_category` int(11) DEFAULT NULL,
  `product_affiliate_link` text NOT NULL,
  `product_original_price` decimal(10,2) DEFAULT NULL,
  `product_sale_price` decimal(10,2) DEFAULT NULL,
  `product_discount_percentage` int(11) DEFAULT NULL,
  `product_cashback` varchar(100) DEFAULT NULL,
  `product_featured` tinyint(1) DEFAULT 0,
  `product_status` tinyint(1) DEFAULT 1,
  `product_order` int(11) DEFAULT 0,
  `product_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `product_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`product_id`),
  KEY `product_store` (`product_store`),
  KEY `product_category` (`product_category`),
  KEY `product_status` (`product_status`),
  KEY `product_featured` (`product_featured`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add indexes for better performance
CREATE INDEX idx_product_store_status ON products(product_store, product_status);
CREATE INDEX idx_product_category_status ON products(product_category, product_status);
CREATE INDEX idx_product_featured_status ON products(product_featured, product_status);
