-- Migration 019: Add item_type column to bom_items table
-- This migration adds the item_type enum column to bom_items for categorizing raw materials

-- Check if 'item_type' column exists and add it if not
DELIMITER //
CREATE PROCEDURE AddItemTypeColumnIfNotExists()
BEGIN
    IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
                   WHERE TABLE_SCHEMA = DATABASE() 
                   AND TABLE_NAME = 'bom_items' 
                   AND COLUMN_NAME = 'item_type') THEN
        ALTER TABLE `bom_items` 
        ADD COLUMN `item_type` ENUM('FABRIC', 'BUTTONS', 'ZIPPER', 'LINING', 'ELASTIC') NULL 
        COMMENT 'Type of raw material item (FABRIC, BUTTONS, ZIPPER, LINING, ELASTIC)';
    END IF;
END //
DELIMITER ;

CALL AddItemTypeColumnIfNotExists();
DROP PROCEDURE AddItemTypeColumnIfNotExists;

-- Add index on item_type for faster queries
ALTER TABLE `bom_items` 
ADD INDEX `idx_item_type` (`item_type`);

-- Verify the table structure
DESCRIBE bom_items;
