-- MariaDB Compatible SKU Auto-Generation Setup
-- This script creates a separate sequence system to avoid the "only one auto column" limitation

-- Disable foreign key checks

-- Create a separate sequence table for SKU generation
CREATE TABLE `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 auto_assigned_category exists before adding
SET @column_exists = (
    SELECT COUNT(*) 
    FROM information_schema.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'products' 
    AND COLUMN_NAME = 'auto_assigned_category'
);

-- Add columns only if they don't exist
SET @sql = CASE 
    WHEN @column_exists = 0 THEN
        '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"'
    ELSE
        'SELECT "Enhanced columns already exist" as message'
END;

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

-- Add indexes for better performance
-- Check if idx_bulk_batch exists before adding
SET @index_exists = (
    SELECT COUNT(*) 
    FROM information_schema.STATISTICS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'products' 
    AND INDEX_NAME = 'idx_bulk_batch'
);

SET @index_sql = CASE 
    WHEN @index_exists = 0 THEN
        'ALTER TABLE `products`
         ADD INDEX `idx_bulk_batch` (`bulk_import_batch`),
         ADD INDEX `idx_auto_category` (`auto_assigned_category`)'
    ELSE
        'SELECT "Indexes already exist" as message'
END;

PREPARE stmt FROM @index_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Create function to get next SKU number
DROP FUNCTION IF EXISTS `get_next_sku_number`;

DELIMITER $$
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 trigger for SKU generation
DROP TRIGGER IF EXISTS `generate_sku_before_insert`;

DELIMITER $$
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`;

-- Show success message
SELECT 'MariaDB-compatible SKU system setup completed successfully!' as status;

-- Show the sequence table
SELECT 'SKU sequences initialized:' as info;
SELECT * FROM `sku_sequences` LIMIT 5;

-- Show relevant columns from products table
SELECT 'Enhanced product table columns:' as info;
SELECT 
    COLUMN_NAME,
    COLUMN_TYPE,
    IS_NULLABLE,
    COLUMN_DEFAULT
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;