-- Add missing columns to products table for enhanced bulk import

-- Disable foreign key checks
SET FOREIGN_KEY_CHECKS = 0;

-- Add bin_id column if it doesn't exist
SET @column_exists = (
    SELECT COUNT(*) 
    FROM information_schema.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'products' 
    AND COLUMN_NAME = 'bin_id'
);

SET @sql = IF(@column_exists = 0,
    'ALTER TABLE `products` ADD COLUMN `bin_id` INT(11) DEFAULT NULL COMMENT "Warehouse bin assignment"',
    'SELECT "bin_id column already exists" as message'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Create warehouse_bins table if it doesn't exist
CREATE TABLE IF NOT EXISTS `warehouse_bins` (
    `bin_id` INT(11) NOT NULL AUTO_INCREMENT,
    `bin_code` VARCHAR(20) NOT NULL,
    `location` VARCHAR(100) DEFAULT NULL,
    `category_id` INT(11) DEFAULT NULL,
    `max_capacity` INT(11) DEFAULT 100,
    `current_occupancy` INT(11) DEFAULT 0,
    `bin_type` ENUM('small', 'medium', 'large', 'oversized') DEFAULT 'medium',
    `is_active` TINYINT(1) DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`bin_id`),
    UNIQUE KEY `unique_bin_code` (`bin_code`),
    INDEX `idx_category_id` (`category_id`),
    INDEX `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Warehouse bin locations for inventory management';

-- Add some sample warehouse bins
INSERT IGNORE INTO `warehouse_bins` (`bin_code`, `location`, `category_id`, `max_capacity`, `bin_type`) VALUES
-- Electronics bins
('A1-001', 'Aisle A1, Shelf 1', 1, 50, 'small'),
('A1-002', 'Aisle A1, Shelf 2', 1, 50, 'small'),
('A1-003', 'Aisle A1, Shelf 3', 1, 100, 'medium'),
('A2-001', 'Aisle A2, Shelf 1', 1, 200, 'large'),

-- Fashion & Apparel bins
('B1-001', 'Aisle B1, Shelf 1', 2, 150, 'medium'),
('B1-002', 'Aisle B1, Shelf 2', 2, 150, 'medium'),
('B1-003', 'Aisle B1, Shelf 3', 2, 300, 'large'),

-- Home & Garden bins
('C1-001', 'Aisle C1, Shelf 1', 3, 100, 'medium'),
('C1-002', 'Aisle C1, Shelf 2', 3, 200, 'large'),
('C1-003', 'Aisle C1, Shelf 3', 3, 500, 'oversized'),

-- Sports & Outdoors bins
('D1-001', 'Aisle D1, Shelf 1', 4, 75, 'small'),
('D1-002', 'Aisle D1, Shelf 2', 4, 150, 'medium'),
('D1-003', 'Aisle D1, Shelf 3', 4, 300, 'large'),

-- Health & Beauty bins
('E1-001', 'Aisle E1, Shelf 1', 5, 100, 'small'),
('E1-002', 'Aisle E1, Shelf 2', 5, 200, 'medium'),

-- Automotive bins
('F1-001', 'Aisle F1, Shelf 1', 6, 100, 'medium'),
('F1-002', 'Aisle F1, Shelf 2', 6, 200, 'large'),

-- Books & Media bins
('G1-001', 'Aisle G1, Shelf 1', 7, 300, 'medium'),
('G1-002', 'Aisle G1, Shelf 2', 7, 500, 'large'),

-- Toys & Games bins
('H1-001', 'Aisle H1, Shelf 1', 8, 200, 'medium'),
('H1-002', 'Aisle H1, Shelf 2', 8, 400, 'large'),

-- General purpose bins
('Z1-001', 'General Storage Area 1', NULL, 100, 'medium'),
('Z1-002', 'General Storage Area 2', NULL, 200, 'large'),
('Z1-003', 'General Storage Area 3', NULL, 500, 'oversized');

-- Add foreign key constraint for bin_id (if not exists)
SET @constraint_exists = (
    SELECT COUNT(*) 
    FROM information_schema.TABLE_CONSTRAINTS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'products' 
    AND CONSTRAINT_NAME = 'fk_products_bin_id'
);

SET @sql = IF(@constraint_exists = 0,
    'ALTER TABLE `products` ADD CONSTRAINT `fk_products_bin_id` FOREIGN KEY (`bin_id`) REFERENCES `warehouse_bins`(`bin_id`) ON DELETE SET NULL',
    'SELECT "bin_id foreign key already exists" as message'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add index for bin_id if not exists
SET @index_exists = (
    SELECT COUNT(*) 
    FROM information_schema.STATISTICS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'products' 
    AND INDEX_NAME = 'idx_bin_id'
);

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

-- Re-enable foreign key checks
SET FOREIGN_KEY_CHECKS = 1;

-- Show completion status
SELECT 'Missing columns added successfully!' as status;

-- Show bin summary
SELECT 'Sample warehouse bins created:' as info;
SELECT COUNT(*) as total_bins FROM warehouse_bins;
SELECT bin_type, COUNT(*) as count FROM warehouse_bins GROUP BY bin_type;

-- Verify the bin_id column was added
SELECT 'bin_id column verification:' 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 = 'bin_id';