DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` TRIGGER `vn`.`ticket_beforeUpdate`
	BEFORE UPDATE ON `ticket`
	FOR EACH ROW
BEGIN
	DECLARE vNewTime TIME;

	SET NEW.editorFk = account.myUser_getId();

	IF NOT (NEW.routeFk <=> OLD.routeFk) THEN
		IF NEW.isSigned AND NOT (
			SELECT (COUNT(s.id) = COUNT(cb.saleFk)
					AND SUM(s.quantity) = SUM(cb.quantity))
				FROM sale s
					LEFT JOIN claimBeginning cb ON cb.saleFk = s.id
				WHERE s.ticketFk = NEW.id
		) THEN
			CALL util.throw('A signed ticket cannot be rerouted');
		END IF;
		INSERT IGNORE INTO routeRecalc(routeFk)
			SELECT id
				FROM `route`
				WHERE NOT isOk
					AND id IN (OLD.routeFk, NEW.routeFk)
					AND created >= util.VN_CURDATE()
				GROUP BY id;
	END IF;

	IF NOT (DATE(NEW.shipped) <=> DATE(OLD.shipped)) THEN
		IF YEAR(NEW.shipped) < 2000 THEN
			SIGNAL SQLSTATE '46000'
				SET MESSAGE_TEXT = 'Year cannot be lesser than 2000';
		END IF;

		IF YEAR(NEW.shipped) = 2000 THEN
			SET NEW.isDeleted = TRUE;
		END IF;

	END IF;

	IF NOT (NEW.isDeleted <=> OLD.isDeleted) AND NEW.isDeleted THEN
		INSERT IGNORE INTO `vn`.`routeRecalc` (`routeFk`)
			SELECT r.id
				FROM vn.ticket t
					JOIN vn.route r ON r.id = t.routeFk
				WHERE r.isOk = FALSE
					AND t.id = NEW.id
					AND r.created >= util.VN_CURDATE()
				GROUP BY r.id;
		SET NEW.shipped = DATE_FORMAT(NEW.shipped, '2000-%m-%d %T');
		SET NEW.landed = DATE_FORMAT(NEW.landed, '2000-%m-%d %T');
		SET NEW.routeFk = NULL;
		SET NEW.zoneFk = NULL;
	END IF;

	 IF NEW.routeFk  AND NEW.isDeleted   THEN
		CALL util.throw ('This ticket is deleted');
	END IF;

	IF NOT (NEW.routeFk <=> OLD.routeFk) AND NEW.routeFk IS NOT NULL THEN
		SELECT MAX(TIME(shipped)) INTO vNewTime
			FROM vn.ticket
			WHERE routeFk = NEW.routeFk
			HAVING MAX(TIME(shipped)) > TIME(NEW.shipped);

		IF vNewTime THEN
			SET NEW.shipped = TIMESTAMP(DATE(NEW.shipped), vNewTime);
		END IF;
		INSERT IGNORE INTO zoneAgencyMode (agencyModeFk,zoneFk)
			SELECT r.agencyModeFk, NEW.zoneFk FROM route r
				WHERE r.id = NEW.routeFk;

		CALL vn.routeUpdateM3(NEW.routeFk);
	END IF;
END$$
DELIMITER ;