-- MariaDB/MySQL Compatible SKU Auto-Increment Setup
-- This script modifies the products table for enhanced bulk import functionality

-- Disable foreign key checks for modifications

-- Step 1: Check if sku_sequence column exists, if not add it
SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() 
     AND TABLE_NAME = 'products' 
     AND COLUMN_NAME = 'sku_sequence') = 0,
    'ALTER TABLE `products` ADD COLUMN `sku_sequence` INT(11) NOT NULL AUTO_INCREMENT UNIQUE FIRST',
    'SELECT "sku_sequence column already exists" as message'
);

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

-- Step 2: Set auto-increment starting value if table is empty
SET @count = (SELECT COUNT(*) FROM products);
SET @sql = IF(@count = 0,
    'ALTER TABLE `products` AUTO_INCREMENT = 100001',
    'SELECT "Products exist, keeping current AUTO_INCREMENT value" as message'
);

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

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

-- Step 4: Add new columns for enhanced functionality (only if they don't exist)
SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() 
     AND TABLE_NAME = 'products' 
     AND COLUMN_NAME = 'auto_assigned_category') = 0,
    '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"',
    'SELECT "Enhanced columns already exist" as message'
);

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

-- Step 5: Create indexes for better performance (if they don't exist)
SET @sql = IF(
    (SELECT COUNT(*) FROM information_schema.STATISTICS 
     WHERE TABLE_SCHEMA = DATABASE() 
     AND TABLE_NAME = 'products' 
     AND INDEX_NAME = 'idx_bulk_batch') = 0,
    '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`)',
    'SELECT "Indexes already exist" as message'
);

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

-- Step 6: Create 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 information
SELECT 'Enhanced products table setup completed!' as message;

-- Show the current table structure
DESCRIBE products;