DELIMITER $$;
CREATE TRIGGER on_cloture_entete_achat 
AFTER UPDATE ON entete_achat
    FOR EACH ROW 
        IF NEW.cloture = 1 AND OLD.cloture = 0 AND NEW.ru <> "commande" THEN
        BEGIN
            DECLARE done INT DEFAULT FALSE;
            DECLARE var_produit_code, var_type CHAR(100);
            DECLARE var_user_code CHAR(100);
            DECLARE var_soussociete_code CHAR(100);
            DECLARE var_quantite, old_quantite, var_stock_quantite, var_stock_dlc, var_stock_casse , var_stock_securite INT;
            DECLARE var_prix_achat_ttc, var_prix_ttc DOUBLE;
            DECLARE lignes CURSOR FOR SELECT l.produit_code, l.quantite, l.type , e.user_code,e.soussociete_code, 
                                             l.prix_achat_ttc, l.prix_ttc, 
                                             sd.quantite as stock_quantite,
                                             sd.dlc as stock_dlc,
                                             sd.casse as stock_casse,
                                             sd.securite as stock_securite
                                        FROM  entete_achat  as e 
                                        INNER JOIN ligne_achat as l ON e.code = l.entete_achat_code
                                        LEFT JOIN stock_depots as sd ON sd.produit_code = l.produit_code AND e.soussociete_code = sd.soussociete_code
                                        WHERE e.code=NEW.code;
            DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

            OPEN lignes;

            read_loop: LOOP
                
                FETCH lignes INTO var_produit_code, var_quantite, var_type, var_user_code,var_soussociete_code, var_prix_achat_ttc, var_prix_ttc, 
                                    var_stock_quantite,  var_stock_dlc, var_stock_casse, var_stock_securite;
                IF done THEN
                    LEAVE read_loop;
                END IF;
                
                IF var_type='dlc' THEN
                    UPDATE stock_depots SET dlc=(dlc - var_quantite) WHERE produit_code=var_produit_code and soussociete_code=var_soussociete_code;
                    INSERT INTO 
                        mouvements (
                                    magasinier_code, depot_code, commercial_code,soussociete_code, produit_code, 
                                    prix_achat_ttc, prix_ttc, quantite, type, numero, num_serie,
                                    configuration, etat, isSync, wavesoft, date, created_at, updated_at
                                    )
                        values ( 
                                    var_user_code, NULL, var_user_code,var_soussociete_code, var_produit_code, var_prix_achat_ttc,
                                    var_prix_ttc, var_quantite, 
                                    'dlc_usine', NEW.code, NULL,NULL,'accepted', 1,0, NOW(),NOW(), NOW()
                                );
                    -- insert stock_depots_historique
                    INSERT INTO 
                            stock_depot_info (
                                user_code, commercial_code, soussociete_code, code, 
                                produit_code, old_quantite, diff_quantite, new_quantite, 
                                module_nomadis, type_mouvement, cause, etat, latitude, longitude, date, created_at, updated_at
                            ) 
                        VALUES (
                            var_user_code, var_user_code, var_soussociete_code, NEW.code,  
                            var_produit_code, var_stock_dlc, var_quantite, var_stock_dlc - var_quantite,
                            'Achat', 'dlc_usine', 'Triggers:UPD:dlc_usine', 'accepted', NULL, NULL, NOW(), NOW(), NOW()
                        );

                ELSEIF var_type='casse' THEN
                    UPDATE stock_depots SET casse=(casse - var_quantite) WHERE produit_code=var_produit_code and soussociete_code=var_soussociete_code;
                    INSERT INTO 
                        mouvements (
                                    magasinier_code, depot_code, commercial_code,soussociete_code, produit_code, 
                                    prix_achat_ttc, prix_ttc, quantite,type, numero, num_serie,
                                    configuration, etat, isSync, wavesoft,date, created_at, updated_at
                                    )
                        values ( 
                                    var_user_code, NULL, var_user_code,var_soussociete_code,var_produit_code, var_prix_achat_ttc,
                                    var_prix_ttc, var_quantite, 
                                    'casse_usine', NEW.code, NULL,NULL,'accepted', 1,0, NOW(), NOW(), NOW()
                                );

                    -- insert stock_depots_historique
                    INSERT INTO 
                            stock_depot_info (
                                user_code, commercial_code, soussociete_code, code, 
                                produit_code, old_quantite, diff_quantite, new_quantite, 
                                module_nomadis, type_mouvement, cause, etat, latitude, longitude, date, created_at, updated_at
                            ) 
                        VALUES (
                            var_user_code, var_user_code, var_soussociete_code, NEW.code,  
                            var_produit_code, var_stock_casse, var_quantite, var_stock_casse - var_quantite,
                            'Achat', 'casse_usine', 'Triggers:UPD:casse_usine', 'accepted', NULL, NULL, NOW(), NOW(), NOW()
                    );

                ELSEIF var_type='casse_stock' THEN
                    UPDATE stock_depots SET casse=(casse + var_quantite),quantite=quantite - var_quantite WHERE produit_code=var_produit_code and soussociete_code=var_soussociete_code;
                    INSERT INTO 
                        mouvements (
                                    magasinier_code, depot_code, commercial_code,soussociete_code, produit_code, 
                                    prix_achat_ttc, prix_ttc, quantite,type, numero, num_serie,
                                    configuration, etat, isSync, wavesoft,date, created_at, updated_at
                                    )
                        values ( 
                                    var_user_code, NULL, var_user_code,var_soussociete_code,var_produit_code, var_prix_achat_ttc,
                                    var_prix_ttc, var_quantite, 
                                    'casse_stock', NEW.code, NULL,NULL,'accepted', 1,0, NOW(), NOW(), NOW()
                                );
                    -- insert stock_depots_historique 
                    INSERT INTO 
                            stock_depot_info (
                                user_code, commercial_code, soussociete_code, code, 
                                produit_code, old_quantite, diff_quantite, new_quantite, 
                                module_nomadis, type_mouvement, cause, etat, latitude, longitude, date, created_at, updated_at
                            ) 
                        VALUES (
                            var_user_code, var_user_code, var_soussociete_code, NEW.code,  
                            var_produit_code, var_stock_casse, var_quantite, var_stock_casse + var_quantite,
                            'Achat', 'casse_stock', 'Triggers:UPD:casse_stock:casse', 'accepted', NULL, NULL, NOW(), NOW(), NOW()
                    );

                    -- insert stock_depots_historique 
                    INSERT INTO 
                            stock_depot_info (
                                user_code, commercial_code, soussociete_code, code, 
                                produit_code, old_quantite, diff_quantite, new_quantite, 
                                module_nomadis, type_mouvement, cause, etat, latitude, longitude, date, created_at, updated_at
                            ) 
                        VALUES (
                            var_user_code, var_user_code, var_soussociete_code, NEW.code,  
                            var_produit_code, var_stock_quantite, var_quantite, var_stock_quantite - var_quantite,
                            'Achat', 'casse_stock', 'Triggers:UPD:casse_stock:stock', 'accepted', NULL, NULL, NOW(), NOW(), NOW()
                    );

                ELSEIF var_type='dlc_stock' THEN
                    UPDATE stock_depots SET dlc=(dlc + var_quantite),quantite=quantite - var_quantite WHERE produit_code=var_produit_code and soussociete_code=var_soussociete_code;
                    INSERT INTO 
                        mouvements (
                                    magasinier_code, depot_code, commercial_code,soussociete_code, produit_code, 
                                    prix_achat_ttc, prix_ttc, quantite,type, numero, num_serie,
                                    configuration, etat, isSync, wavesoft,date, created_at, updated_at
                                    )
                        values ( 
                                    var_user_code, NULL, var_user_code,var_soussociete_code,var_produit_code, var_prix_achat_ttc,
                                    var_prix_ttc, var_quantite, 
                                    'dlc_stock', NEW.code, NULL,NULL,'accepted', 1,0, NOW(), NOW(), NOW()
                                );

                    -- insert stock_depots_historique 
                    INSERT INTO 
                            stock_depot_info (
                                user_code, commercial_code, soussociete_code, code, 
                                produit_code, old_quantite, diff_quantite, new_quantite, 
                                module_nomadis, type_mouvement, cause, etat, latitude, longitude, date, created_at, updated_at
                            ) 
                        VALUES (
                            var_user_code, var_user_code, var_soussociete_code, NEW.code,  
                            var_produit_code, var_stock_dlc, var_quantite, var_stock_dlc + var_quantite,
                            'Achat', 'dlc_stock', 'Triggers:UPD:dlc_stock:dlc', 'accepted', NULL, NULL, NOW(), NOW(), NOW()
                    );

                    -- insert stock_depots_historique 
                    INSERT INTO 
                            stock_depot_info (
                                user_code, commercial_code, soussociete_code, code, 
                                produit_code, old_quantite, diff_quantite, new_quantite, 
                                module_nomadis, type_mouvement, cause, etat, latitude, longitude, date, created_at, updated_at
                            ) 
                        VALUES (
                            var_user_code, var_user_code, var_soussociete_code, NEW.code,  
                            var_produit_code, var_stock_quantite, var_quantite, var_stock_quantite - var_quantite,
                            'Achat', 'dlc_stock', 'Triggers:UPD:dlc_stock:stock', 'accepted', NULL, NULL, NOW(), NOW(), NOW()
                    );

                ELSEIF var_type='securite_stock' THEN
                    UPDATE stock_depots SET securite=(securite - var_quantite),quantite=quantite + var_quantite WHERE produit_code=var_produit_code and soussociete_code=var_soussociete_code;
                    INSERT INTO 
                        mouvements (
                                    magasinier_code, depot_code, commercial_code,soussociete_code, produit_code, 
                                    prix_achat_ttc, prix_ttc, quantite,type, numero, num_serie,
                                    configuration, etat, isSync, wavesoft,date, created_at, updated_at
                                    )
                        values ( 
                                    var_user_code, NULL, var_user_code,var_soussociete_code,var_produit_code, var_prix_achat_ttc,
                                    var_prix_ttc, var_quantite, 
                                    'securite_stock', NEW.code, NULL,NULL,'accepted', 1,0, NOW(), NOW(), NOW()
                                );

                    -- insert stock_depots_historique 
                    INSERT INTO 
                            stock_depot_info (
                                user_code, commercial_code, soussociete_code, code, 
                                produit_code, old_quantite, diff_quantite, new_quantite, 
                                module_nomadis, type_mouvement, cause, etat, latitude, longitude, date, created_at, updated_at
                            ) 
                        VALUES (
                            var_user_code, var_user_code, var_soussociete_code, NEW.code,  
                            var_produit_code, var_stock_securite, var_quantite, var_stock_securite - var_quantite,
                            'Achat', 'securite_stock', 'Triggers:UPD:securite_stock:securite', 'accepted', NULL, NULL, NOW(), NOW(), NOW()
                    );

                    -- insert stock_depots_historique 
                    INSERT INTO 
                            stock_depot_info (
                                user_code, commercial_code, soussociete_code, code, 
                                produit_code, old_quantite, diff_quantite, new_quantite, 
                                module_nomadis, type_mouvement, cause, etat, latitude, longitude, date, created_at, updated_at
                            ) 
                        VALUES (
                            var_user_code, var_user_code, var_soussociete_code, NEW.code,  
                            var_produit_code, var_stock_quantite, var_quantite, var_stock_quantite + var_quantite,
                            'Achat', 'securite_stock', 'Triggers:UPD:securite_stock:stock', 'accepted', NULL, NULL, NOW(), NOW(), NOW()
                    );

                ELSEIF var_type='stock_securite' THEN
                    UPDATE stock_depots SET securite=(securite + var_quantite),quantite=quantite - var_quantite WHERE produit_code=var_produit_code and soussociete_code=var_soussociete_code;
                    INSERT INTO 
                        mouvements (
                                    magasinier_code, depot_code, commercial_code,soussociete_code, produit_code, 
                                    prix_achat_ttc, prix_ttc, quantite,type, numero, num_serie,
                                    configuration, etat, isSync, wavesoft,date, created_at, updated_at
                                    )
                        values ( 
                                    var_user_code, NULL, var_user_code,var_soussociete_code,var_produit_code, var_prix_achat_ttc,
                                    var_prix_ttc, var_quantite, 
                                    'stock_securite', NEW.code, NULL,NULL,'accepted', 1,0, NOW(), NOW(), NOW()
                                );

                    -- insert stock_depots_historique 
                    INSERT INTO 
                            stock_depot_info (
                                user_code, commercial_code, soussociete_code, code, 
                                produit_code, old_quantite, diff_quantite, new_quantite, 
                                module_nomadis, type_mouvement, cause, etat, latitude, longitude, date, created_at, updated_at
                            ) 
                        VALUES (
                            var_user_code, var_user_code, var_soussociete_code, NEW.code,  
                            var_produit_code, var_stock_securite, var_quantite, var_stock_securite + var_quantite,
                            'Achat', 'stock_securite', 'Triggers:UPD:stock_securite:securite', 'accepted', NULL, NULL, NOW(), NOW(), NOW()
                    );

                    -- insert stock_depots_historique 
                    INSERT INTO 
                            stock_depot_info (
                                user_code, commercial_code, soussociete_code, code, 
                                produit_code, old_quantite, diff_quantite, new_quantite, 
                                module_nomadis, type_mouvement, cause, etat, latitude, longitude, date, created_at, updated_at
                            ) 
                        VALUES (
                            var_user_code, var_user_code, var_soussociete_code, NEW.code,  
                            var_produit_code, var_stock_quantite, var_quantite, var_stock_quantite - var_quantite,
                            'Achat', 'stock_securite', 'Triggers:UPD:stock_securite:stock', 'accepted', NULL, NULL, NOW(), NOW(), NOW()
                    );

                ELSEIF var_type='retour' THEN
                    UPDATE stock_depots SET quantite=quantite - var_quantite WHERE produit_code=var_produit_code and soussociete_code=var_soussociete_code;
                    INSERT INTO 
                        mouvements (
                                    magasinier_code, depot_code, commercial_code,soussociete_code, produit_code, 
                                    prix_achat_ttc, prix_ttc, quantite,type, numero, num_serie,
                                    configuration, etat, isSync, wavesoft,date, created_at, updated_at
                                    )
                        values ( 
                                    var_user_code, NULL, var_user_code,var_soussociete_code,var_produit_code, var_prix_achat_ttc,
                                    var_prix_ttc, var_quantite, 
                                    'retour_usine', NEW.code, NULL,NULL,'accepted', 1,0, NOW(),NOW(), NOW()
                                );

                    -- insert stock_depots_historique 
                    INSERT INTO 
                            stock_depot_info (
                                user_code, commercial_code, soussociete_code, code, 
                                produit_code, old_quantite, diff_quantite, new_quantite, 
                                module_nomadis, type_mouvement, cause, etat, latitude, longitude, date, created_at, updated_at
                            ) 
                        VALUES (
                            var_user_code, var_user_code, var_soussociete_code, NEW.code,  
                            var_produit_code, var_stock_quantite, var_quantite, var_stock_quantite - var_quantite,
                            'Achat', 'retour_usine', 'Triggers:UPD:retour_usine', 'accepted', NULL, NULL, NOW(), NOW(), NOW()
                    );

                ELSEIF var_type='achat' THEN
                    UPDATE stock_depots SET quantite=quantite + var_quantite WHERE produit_code=var_produit_code and soussociete_code=var_soussociete_code;
                    INSERT INTO 
                        mouvements (
                                    magasinier_code, depot_code, commercial_code, soussociete_code,produit_code, 
                                    prix_achat_ttc, prix_ttc, quantite,type, numero, num_serie,
                                    configuration, etat, isSync, wavesoft,date, created_at, updated_at
                                    )
                        values ( 
                                    var_user_code, NULL, var_user_code, var_soussociete_code,var_produit_code, var_prix_achat_ttc,
                                    var_prix_ttc, var_quantite, 
                                    'achat', NEW.code, NULL,NULL,'accepted', 1,0, NOW(), NOW(), NOW()
                                );
                    -- insert stock_depots_historique 
                    INSERT INTO 
                            stock_depot_info (
                                user_code, commercial_code, soussociete_code, code, 
                                produit_code, old_quantite, diff_quantite, new_quantite, 
                                module_nomadis, type_mouvement, cause, etat, latitude, longitude, date, created_at, updated_at
                            ) 
                        VALUES (
                            var_user_code, var_user_code, var_soussociete_code, NEW.code,  
                            var_produit_code, var_stock_quantite, var_quantite, var_stock_quantite + var_quantite,
                            'Achat', 'achat', 'Triggers:UPD:achat', 'accepted', NULL, NULL, NOW(), NOW(), NOW()
                    );

                END IF;
                
            END LOOP;
            CLOSE lignes;
        END;
        END IF;