-- Update SKU to Auto-Increment and Add Enhanced Columns
-- This script modifies the products table for enhanced bulk import functionality

-- Disable foreign key checks for modifications

-- Add a new auto-increment SKU column
ALTER TABLE `products` 
ADD COLUMN `sku_sequence` INT(11) NOT NULL AUTO_INCREMENT UNIQUE FIRST;

-- Set the auto-increment starting value
ALTER TABLE `products` AUTO_INCREMENT = 100001;

-- Modify the SKU column to be generated automatically
-- We'll keep the existing sku column but make it optional initially
ALTER TABLE `products` 
MODIFY COLUMN `sku` VARCHAR(100) NULL;

-- Add new columns for enhanced functionality
ALTER TABLE `products` 
ADD COLUMN `auto_assigned_category` TINYINT(1) DEFAULT 0 COMMENT 'Whether category was AI-assigned',
ADD COLUMN `category_confidence` DECIMAL(3,2) DEFAULT NULL COMMENT 'AI confidence score for category assignment',
ADD COLUMN `keywords` TEXT DEFAULT NULL COMMENT 'Extracted keywords for categorization',
ADD COLUMN `bulk_import_batch` VARCHAR(50) DEFAULT NULL COMMENT 'Batch ID for bulk imports';

-- Create index for better performance
ALTER TABLE `products`
ADD INDEX `idx_bulk_batch` (`bulk_import_batch`),
ADD INDEX `idx_auto_category` (`auto_assigned_category`),
ADD INDEX `idx_sku_sequence` (`sku_sequence`);

-- Create a trigger to auto-generate SKU if not provided
DELIMITER $$

DROP TRIGGER IF EXISTS `generate_sku_before_insert`$$

CREATE TRIGGER `generate_sku_before_insert`
BEFORE INSERT ON `products`
FOR EACH ROW
BEGIN
    -- Generate SKU if not provided
    IF NEW.sku IS NULL OR NEW.sku = '' THEN
        SET NEW.sku = CONCAT('SKU-', LPAD(NEW.sku_sequence, 6, '0'));
    END IF;
    
    -- Set batch ID if not provided
    IF NEW.bulk_import_batch IS NULL THEN
        SET NEW.bulk_import_batch = CONCAT('BATCH-', DATE_FORMAT(NOW(), '%Y%m%d-%H%i%s'));
    END IF;
END$$

DELIMITER ;

-- Re-enable foreign key checks

-- Display current schema
DESCRIBE products;