TYPE=TRIGGERS
triggers='CREATE DEFINER=`root`@`localhost` TRIGGER `update_supply_price_after_batch_change` AFTER UPDATE ON `supply_batches` FOR EACH ROW BEGIN
\n    DECLARE v_total_qty INT;
\n    DECLARE v_total_value DECIMAL(12,2);
\n    DECLARE v_avg_price DECIMAL(10,2);
\n    
\n        SELECT 
\n        COALESCE(SUM(quantity_remaining), 0),
\n        COALESCE(SUM(quantity_remaining * unit_price), 0)
\n    INTO v_total_qty, v_total_value
\n    FROM supply_batches 
\n    WHERE supply_id = NEW.supply_id AND status = \'active\' AND quantity_remaining > 0;
\n    
\n    IF v_total_qty > 0 THEN
\n        SET v_avg_price = v_total_value / v_total_qty;
\n    ELSE
\n        SET v_avg_price = 0;
\n        SET v_total_value = 0;
\n    END IF;
\n    
\n    UPDATE supplies 
\n    SET average_unit_price = v_avg_price,
\n        total_value = v_total_value
\n    WHERE id = NEW.supply_id;
\nEND'
sql_modes=524288
definers='root@localhost'
client_cs_names='utf8mb4'
connection_cl_names='utf8mb4_general_ci'
db_cl_names='utf8_general_ci'
created=176174187186