-- Fixed approach: Use a separate sequence table for SKU generation
-- This avoids the "only one auto column" limitation

-- Disable foreign key checks

-- Create a separate sequence table for SKU generation
CREATE TABLE IF NOT EXISTS `sku_sequences` (
    `sequence_id` INT(11) NOT NULL AUTO_INCREMENT,
    `trader_id` INT(11) NOT NULL,
    `last_sku_number` INT(11) NOT NULL DEFAULT 100000,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`sequence_id`),
    UNIQUE KEY `unique_trader` (`trader_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Modify the SKU column to be optional
ALTER TABLE `products` 
MODIFY COLUMN `sku` VARCHAR(100) NULL;

-- Add new columns for enhanced functionality (check if they exist first)
SET @sql = CONCAT(
    'ALTER TABLE `products` ',
    IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'auto_assigned_category') = 0,
       'ADD COLUMN `auto_assigned_category` TINYINT(1) DEFAULT 0 COMMENT "Whether category was AI-assigned", ', ''),
    IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'category_confidence') = 0,
       'ADD COLUMN `category_confidence` DECIMAL(3,2) DEFAULT NULL COMMENT "AI confidence score for category assignment", ', ''),
    IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'keywords') = 0,
       'ADD COLUMN `keywords` TEXT DEFAULT NULL COMMENT "Extracted keywords for categorization", ', ''),
    IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'bulk_import_batch') = 0,
       'ADD COLUMN `bulk_import_batch` VARCHAR(50) DEFAULT NULL COMMENT "Batch ID for bulk imports"', '')
);

-- Remove trailing comma if present
SET @sql = TRIM(TRAILING ', ' FROM @sql);

-- Only execute if we have columns to add
SET @sql = IF(@sql = 'ALTER TABLE `products` ', 'SELECT "All columns already exist" as message', @sql);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add indexes (check if they exist first)
SET @index_sql = '';

-- Check and add idx_bulk_batch
IF (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND INDEX_NAME = 'idx_bulk_batch') = 0 THEN
    SET @index_sql = CONCAT(@index_sql, 'ADD INDEX `idx_bulk_batch` (`bulk_import_batch`), ');
END IF;

-- Check and add idx_auto_category  
IF (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND INDEX_NAME = 'idx_auto_category') = 0 THEN
    SET @index_sql = CONCAT(@index_sql, 'ADD INDEX `idx_auto_category` (`auto_assigned_category`), ');
END IF;

-- Remove trailing comma and execute if we have indexes to add
IF @index_sql != '' THEN
    SET @index_sql = TRIM(TRAILING ', ' FROM @index_sql);
    SET @index_sql = CONCAT('ALTER TABLE `products` ', @index_sql);
    
    PREPARE stmt FROM @index_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END IF;

-- Create function to get next SKU number
DELIMITER $$

DROP FUNCTION IF EXISTS `get_next_sku_number`$$

CREATE FUNCTION `get_next_sku_number`(trader_id_param INT) 
RETURNS INT
DETERMINISTIC
MODIFIES SQL DATA
BEGIN
    DECLARE next_number INT DEFAULT 100001;
    
    -- Insert or update the sequence for this trader
    INSERT INTO `sku_sequences` (`trader_id`, `last_sku_number`) 
    VALUES (trader_id_param, next_number)
    ON DUPLICATE KEY UPDATE 
        `last_sku_number` = `last_sku_number` + 1,
        `updated_at` = NOW();
    
    -- Get the current number
    SELECT `last_sku_number` INTO next_number 
    FROM `sku_sequences` 
    WHERE `trader_id` = trader_id_param;
    
    RETURN next_number;
END$$

DELIMITER ;

-- Create enhanced trigger for SKU generation
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 @sku_number = get_next_sku_number(NEW.trader_id);
        SET NEW.sku = CONCAT('SKU-', LPAD(@sku_number, 6, '0'));
    END IF;
    
    -- Set batch ID if not provided
    IF NEW.bulk_import_batch IS NULL OR NEW.bulk_import_batch = '' 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

-- Initialize sequences for existing traders
INSERT IGNORE INTO `sku_sequences` (`trader_id`, `last_sku_number`)
SELECT `trader_id`, 100000 FROM `traders`;

-- Display success message and current structure
SELECT 'Enhanced SKU system setup completed successfully!' as message;
SELECT 'SKU sequences initialized for traders:' as info, COUNT(*) as count FROM `sku_sequences`;

-- Show relevant columns from products table
SELECT 
    COLUMN_NAME,
    COLUMN_TYPE,
    IS_NULLABLE,
    COLUMN_DEFAULT,
    COLUMN_COMMENT
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_NAME = 'products' 
AND COLUMN_NAME IN ('sku', 'auto_assigned_category', 'category_confidence', 'keywords', 'bulk_import_batch')
ORDER BY ORDINAL_POSITION;