-- reactivem trigger 
DROP TRIGGER IF EXISTS `vn`.`buy_afterUpdate`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` TRIGGER `vn`.`buy_afterUpdate`
	AFTER UPDATE ON `buy`
	FOR EACH ROW
trig: BEGIN
	DECLARE vLanded DATE;
	DECLARE vBuyerFk INT;
	DECLARE vIsBuyerToBeEmailed BOOL;
    DECLARE vItemName VARCHAR(50);
	DECLARE vIsInventory BOOL;
	DECLARE vEmail VARCHAR(255);
    
    IF !(NEW.id <=> OLD.id)
    OR !(NEW.entryFk <=> OLD.entryFk)
    OR !(NEW.itemFk <=> OLD.itemFk)
    OR !(NEW.quantity <=> OLD.quantity)
    OR !(NEW.created <=> OLD.created) THEN
		CALL stock.log_add('buy', NEW.id, OLD.id);
	END IF;
	
	IF @isModeInventory THEN
		LEAVE trig;
	END IF;

    CALL buy_afterUpsert(NEW.id);

	IF !(NEW.weight <=> OLD.weight) AND NEW.weight THEN
		UPDATE item
			SET density = NEW.weight / (item_getVolume(NEW.itemFk, NEW.packageFk) / 1000000)
			WHERE id = NEW.itemFk;
	END IF;
    
	SELECT w.isBuyerToBeEmailed, t.landed
		INTO vIsBuyerToBeEmailed, vLanded
		FROM entry e
			JOIN travel t ON t.id = e.travelFk
			JOIN warehouse w ON w.id = t.warehouseInFk 
		WHERE e.id = NEW.entryFk;
	
	SELECT it.workerFk, i.longName 
		INTO vBuyerFk, vItemName
		FROM itemCategory k
			JOIN itemType it ON it.categoryFk = k.id
			JOIN item i ON i.typeFk = it.id
		WHERE i.id = OLD.itemFk;
            
    IF vIsBuyerToBeEmailed AND	
	   vBuyerFk != account.myUser_getId() AND 
	   vLanded = util.CURDATE() THEN
			IF !(NEW.itemFk <=> OLD.itemFk) OR 
			   !(NEW.quantity <=> OLD.quantity) OR 
			   !(NEW.packing <=> OLD.packing) OR
			   !(NEW.grouping <=> OLD.grouping) OR 
			   !(NEW.packageFk <=> OLD.packageFk) OR 
			   !(NEW.weight <=> OLD.weight) THEN
					CALL vn.mail_insert(
						CONCAT(account.user_getNameFromId(vBuyerFk),'@verdnatura.es'),
						CONCAT(account.myUser_getName(),'@verdnatura.es'),
						CONCAT('E ', NEW.entryFk ,' Se ha modificado item ', NEW.itemFk, ' ', vItemName),
						'Este email se ha generado automáticamente'
					);
			END IF;
	END IF;

	SELECT e.isInventory INTO vIsInventory
		FROM entry e
		WHERE e.id = NEW.entryFk;
		
	SELECT e.mailToNotify INTO vEmail
		FROM entryConfig e 
		LIMIT 1;

	IF vIsInventory AND 
		(!(NEW.quantity <=> OLD.quantity) 
		OR !(NEW.price1<=>OLD.price1)
		OR !(NEW.price2<=>OLD.price2)
		OR !(NEW.price3<=>OLD.price3)) THEN
		
		CALL mail_insert(
			vEmail,
			NULL,
			CONCAT('Se ha modificado la entrada ', NEW.entryFk ,' del Artículo ', NEW.itemFk, ' siendo inventario'),
			CONCAT(
				'Antes:  Cantidad: ', OLD.quantity, '  Precio1: ', OLD.price1,  '  Precio2: ', OLD.price2,  '  Precio3: ', OLD.price3,
				'<br>',
				'Despues:  Cantidad: ', NEW.quantity, '  Precio1: ', NEW.price1,  '  Precio2: ', NEW.price2,  '  Precio3: ', NEW.price3
			)
		);
	END IF; 

END$$
DELIMITER ;