-- Create bulk_import_logs table for tracking bulk upload history
-- This table stores information about each bulk import operation

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`),
    FOREIGN KEY (`trader_id`) REFERENCES `traders`(`trader_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Log of all bulk import operations';

-- Display success message
SELECT 'bulk_import_logs table created successfully!' as status;

-- Show the table structure
DESCRIBE bulk_import_logs;