-- Create tables for enhanced bulk import functionality
-- Run this after the main category tables are created

-- 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,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`log_id`),
    KEY `idx_trader` (`trader_id`),
    KEY `idx_batch` (`batch_id`),
    KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Update traders table to add missing columns if they don't exist
ALTER TABLE `traders` 
ADD COLUMN IF NOT EXISTS `total_products_listed` INT(11) DEFAULT 0,
ADD COLUMN IF NOT EXISTS `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

-- Sample trader data if table is empty
INSERT IGNORE INTO `traders` (`trader_id`, `business_name`, `contact_person`, `email`, `phone`, `address`, `subscription_status`, `monthly_upload_limit`, `current_month_uploads`) VALUES
(1, 'Tech Solutions Ltd', 'John Smith', 'john@techsolutions.com', '+1-555-0101', '123 Tech Street, Silicon Valley, CA 94000', 'active', 500, 0),
(2, 'Fashion Forward Co', 'Sarah Johnson', 'sarah@fashionforward.com', '+1-555-0102', '456 Fashion Ave, New York, NY 10001', 'active', 1000, 0),
(3, 'Home & Garden Plus', 'Mike Williams', 'mike@homegardenplus.com', '+1-555-0103', '789 Garden Road, Portland, OR 97201', 'active', 750, 0),
(4, 'Sports Central', 'Lisa Brown', 'lisa@sportscentral.com', '+1-555-0104', '321 Sports Blvd, Denver, CO 80202', 'active', 600, 0),
(5, 'Book Haven', 'David Wilson', 'david@bookhaven.com', '+1-555-0105', '654 Literary Lane, Boston, MA 02101', 'active', 300, 0);

-- Display table creation status
SELECT 'Enhanced bulk import tables created successfully!' as message;
SELECT 'Traders available:' as info, COUNT(*) as count FROM traders;
SELECT 'Categories available:' as info, COUNT(*) as count FROM categories;
SELECT 'Warehouse bins available:' as info, COUNT(*) as count FROM warehouse_bins;