-- Complete Enhanced Bulk Import Setup
-- This script creates all necessary tables and structures for the enhanced bulk import system

-- Disable foreign key checks

-- 1. Create bulk_import_logs table
CREATE TABLE IF NOT EXISTS `bulk_import_logs` (
    `log_id` INT(11) NOT NULL AUTO_INCREMENT,
    `trader_id` INT(11) NOT NULL,
    `batch_id` VARCHAR(50) NOT NULL,
    `file_name` VARCHAR(255) NOT NULL,
    `total_records` INT(11) NOT NULL DEFAULT 0,
    `successful_records` INT(11) NOT NULL DEFAULT 0,
    `failed_records` INT(11) NOT NULL DEFAULT 0,
    `ai_assigned_count` INT(11) NOT NULL DEFAULT 0,
    `file_size` INT(11) DEFAULT NULL COMMENT 'File size in bytes',
    `processing_time` DECIMAL(10,3) DEFAULT NULL COMMENT 'Processing time in seconds',
    `error_message` TEXT DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`log_id`),
    INDEX `idx_trader_id` (`trader_id`),
    INDEX `idx_batch_id` (`batch_id`),
    INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Log of all bulk import operations';

-- 2. Create sku_sequences 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;

-- 3. Modify products table - make SKU optional
ALTER TABLE `products` MODIFY COLUMN `sku` VARCHAR(100) NULL;

-- 4. Add enhanced columns to products table (check if they exist first)
-- Check and add auto_assigned_category
SET @column_exists = (
    SELECT COUNT(*) 
    FROM information_schema.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'products' 
    AND COLUMN_NAME = 'auto_assigned_category'
);

SET @sql = IF(@column_exists = 0,
    'ALTER TABLE `products` ADD COLUMN `auto_assigned_category` TINYINT(1) DEFAULT 0 COMMENT "Whether category was AI-assigned"',
    'SELECT "auto_assigned_category column already exists" as message'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Check and add category_confidence
SET @column_exists = (
    SELECT COUNT(*) 
    FROM information_schema.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'products' 
    AND COLUMN_NAME = 'category_confidence'
);

SET @sql = IF(@column_exists = 0,
    'ALTER TABLE `products` ADD COLUMN `category_confidence` DECIMAL(3,2) DEFAULT NULL COMMENT "AI confidence score for category assignment"',
    'SELECT "category_confidence column already exists" as message'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Check and add keywords
SET @column_exists = (
    SELECT COUNT(*) 
    FROM information_schema.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'products' 
    AND COLUMN_NAME = 'keywords'
);

SET @sql = IF(@column_exists = 0,
    'ALTER TABLE `products` ADD COLUMN `keywords` TEXT DEFAULT NULL COMMENT "Extracted keywords for categorization"',
    'SELECT "keywords column already exists" as message'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Check and add bulk_import_batch
SET @column_exists = (
    SELECT COUNT(*) 
    FROM information_schema.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'products' 
    AND COLUMN_NAME = 'bulk_import_batch'
);

SET @sql = IF(@column_exists = 0,
    'ALTER TABLE `products` ADD COLUMN `bulk_import_batch` VARCHAR(50) DEFAULT NULL COMMENT "Batch ID for bulk imports"',
    'SELECT "bulk_import_batch column already exists" as message'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 5. Add indexes for better performance (check if they exist first)
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 = IF(@index_exists = 0,
    'ALTER TABLE `products` ADD INDEX `idx_bulk_batch` (`bulk_import_batch`)',
    'SELECT "idx_bulk_batch index already exists" as message'
);
PREPARE stmt FROM @index_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @index_exists = (
    SELECT COUNT(*) 
    FROM information_schema.STATISTICS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'products' 
    AND INDEX_NAME = 'idx_auto_category'
);

SET @index_sql = IF(@index_exists = 0,
    'ALTER TABLE `products` ADD INDEX `idx_auto_category` (`auto_assigned_category`)',
    'SELECT "idx_auto_category index already exists" as message'
);
PREPARE stmt FROM @index_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 6. Create function for SKU generation
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 ;

-- 7. Create trigger for automatic 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 ;

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

-- Re-enable foreign key checks

-- Show completion status
SELECT 'Complete Enhanced Bulk Import Setup completed successfully!' as status;

-- Show summary
SELECT 'Tables created/verified:' as info;
SELECT 
    TABLE_NAME,
    TABLE_COMMENT
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_NAME IN ('bulk_import_logs', 'sku_sequences', 'products');

SELECT 'Enhanced columns added to products:' as info;
SELECT 
    COLUMN_NAME,
    COLUMN_TYPE,
    IS_NULLABLE,
    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;