-- Add waste_percentage column to product_ingredients table
ALTER TABLE product_ingredients 
ADD COLUMN waste_percentage DECIMAL(5,2) DEFAULT 0.00 CHECK (waste_percentage >= 0 AND waste_percentage <= 100);

-- Add comment to explain the column
COMMENT ON COLUMN product_ingredients.waste_percentage IS 'Waste percentage for this ingredient (0-100). Used to calculate gross quantity needed including waste.';
