CREATE
DEFINER=`sherzodr`@`%`
TRIGGER `talibro`.`invoice_content_BEFORE_UPDATE`
BEFORE UPDATE ON `talibro`.`invoice_content`
FOR EACH ROW
update_fifo_layer: BEGIN
    DECLARE invoice_date DATETIME;
    DECLARE invoice_type CHAR(16);
    DECLARE item_type CHAR(16);
    DECLARE fu_layer INT;
    DECLARE fu_item INT;
    DECLARE fu_cost FLOAT;
    DECLARE fu_qty FLOAT;
    DECLARE eoi INT DEFAULT 0;
    DECLARE fu_cursor CURSOR FOR SELECT layer, item, cost, qty FROM fifo_utilization WHERE invoice_content=OLD.id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET eoi = 1;

    SELECT `type` INTO item_type FROM item WHERE id=OLD.item;

    IF (item_type != "inventory") THEN
        LEAVE update_fifo_layer;
    END IF;

    
    

    
    

    SELECT `date`, `type` INTO invoice_date, invoice_type FROM invoice WHERE id=OLD.invoice;

    IF (invoice_type = "sale") THEN
        
        OPEN fu_cursor;
        fu_block: 
        LOOP
            FETCH fu_cursor INTO fu_layer, fu_item, fu_cost, fu_qty;

            IF eoi THEN 
                LEAVE fu_block;
            END IF;

            INSERT INTO fifo_layer (date, item, cost, qty)
                VALUES(invoice_date, OLD.item, fu_cost, fu_qty) ON DUPLICATE KEY UPDATE qty=qty+fu_qty;

        END LOOP;
        CLOSE fu_cursor;
        DELETE FROM fifo_utilization WHERE invoice_content=OLD.id;

    ELSE

        INSERT INTO fifo_layer (`date`, item, cost, qty) 
            VALUES(invoice_date, OLD.item, lifo_cost(OLD.item), (-1)* OLD.qty) ON DUPLICATE KEY UPDATE qty=qty-OLD.qty;
    END IF;

END