-- Migration: Add featured and banner fields
-- Run this after schema.sql if database already exists
-- This script adds the new fields for featured products and category banners
-- Note: If columns already exist, you may need to comment out the ALTER TABLE statements

-- Add featured fields to products table
ALTER TABLE `products` 
ADD COLUMN `is_featured` tinyint(1) DEFAULT 0 AFTER `is_gift`,
ADD COLUMN `featured_order` int(11) DEFAULT 0 AFTER `is_featured`,
ADD KEY `idx_featured` (`is_featured`, `featured_order`);

-- Add banner fields to categories table
ALTER TABLE `categories` 
ADD COLUMN `banner_image` varchar(255) DEFAULT NULL AFTER `image`,
ADD COLUMN `banner_product_id` int(11) DEFAULT NULL AFTER `banner_image`,
ADD KEY `idx_banner_product` (`banner_product_id`);

-- Add foreign key constraint (run this after products table exists)
ALTER TABLE `categories` 
ADD CONSTRAINT `fk_categories_banner_product` FOREIGN KEY (`banner_product_id`) REFERENCES `products`(`id`) ON DELETE SET NULL;
