-- Triggers faits spécialement pour SALTO à l'ESSEC

--
-- MAJ 09-02-2022 JL
--

--
-- reservations
--
CREATE TRIGGER `pileSIGmajLoc` AFTER UPDATE ON `locataires` FOR EACH ROW
BEGIN
  IF (NEW.idNouvelleResa <=> OLD.idNouvelleResa) THEN
    IF (!(NEW.etat <=> OLD.etat)) THEN
      IF (NEW.etat = '3') THEN
        INSERT INTO pile_sig(idLocataire, idResa, idApiSIG, dateCreation, flag)
        VALUES(new.idLocataire, new.id, (SELECT id FROM api_sig WHERE api_sig.className = 'SALTO'), NOW(), 'confirm');
      ELSEIF (NEW.etat = '13' AND OLD.etat != '7') THEN
        INSERT INTO pile_sig(idLocataire, idResa, idApiSIG, dateCreation, flag)
        VALUES(new.idLocataire, new.id, (SELECT id FROM api_sig WHERE api_sig.className = 'SALTO'), NOW(), 'desist');
      ELSEIF (NEW.etat = '6' AND OLD.etat = '7') THEN
	INSERT INTO pile_sig(idLocataire, idResa, idApiSIG, dateCreation, flag)
        VALUES(new.idLocataire, new.id, (SELECT id FROM api_sig WHERE api_sig.className = 'SALTO'), NOW(), 'checkOut');
      ELSEIF (NEW.etat = '7' AND OLD.etat = '3') THEN
	IF (!(NEW.dateArrivee <=> OLD.dateArrivee) OR !(NEW.dateDepart <=> OLD.dateDepart)) THEN
          INSERT INTO pile_sig(idLocataire, idResa, idApiSIG, dateCreation, flag)
          VALUES(NEW.idLocataire, NEW.id, (SELECT id FROM api_sig WHERE api_sig.className = 'SALTO'), NOW(), 'majDate');
	END IF;
      END IF;
    ELSEIF (!(NEW.dateArrivee <=> OLD.dateArrivee) OR !(NEW.dateDepart <=> OLD.dateDepart)) THEN
      IF (NEW.etat IN ('3','7','15')) THEN
        INSERT INTO pile_sig(idLocataire, idResa, idApiSIG, dateCreation, flag)
        VALUES(NEW.idLocataire, NEW.id, (SELECT id FROM api_sig WHERE api_sig.className = 'SALTO'), NOW(), 'majDate');
      END IF;
    ELSEIF (!(NEW.idLogement <=> OLD.idLogement)) THEN
      INSERT INTO pile_sig(idLocataire, idResa, idApiSIG, dateCreation, flag)
      VALUES(new.idLocataire, new.id, (SELECT id FROM api_sig WHERE api_sig.className = 'SALTO'), NOW(), 'majDate');
    END IF;

  ELSEIF (!(NEW.idNouvelleResa <=> OLD.idNouvelleResa)) THEN
    IF ((NEW.idNouvelleResa != 0) OR (NEW.idNouvelleResa IS NOT NULL)) THEN
      UPDATE pile_sig SET dateCreation=NOW(),flag='chgtLoge' WHERE idResa=NEW.id;
    ELSEIF ((NEW.idNouvelleResa = 0) OR (NEW.idNouvelleResa IS NULL)) THEN
      INSERT INTO pile_sig(idLocataire, idResa, idApiSIG, dateCreation, flag)
      VALUES(NEW.idLocataire, NEW.id, (SELECT id FROM api_sig WHERE api_sig.className = 'SALTO'), NOW(), 'annulChgtLoge');
    END IF;
  END IF;
END

--
-- locataires
--
CREATE TRIGGER `pileSIGmajLoc` AFTER UPDATE ON `locataires` FOR EACH ROW
BEGIN
  IF(!(NEW.telephoneMobile <=> OLD.telephoneMobile)) THEN
    INSERT INTO pile_sig(idLocataire, idApiSIG, dateCreation, flag, oldNumMobile, newNumMobile)
    VALUES(NEW.id, (SELECT id FROM api_sig WHERE api_sig.className = 'SALTO'), NOW(), 'chgtTel', OLD.telephoneMobile, NEW.telephoneMobile);
  ELSEIF(!(NEW.typeAccesLoge <=> OLD.typeAccesLoge)) THEN
    INSERT INTO pile_sig(idLocataire, idApiSIG, dateCreation, flag)
    VALUES(NEW.id, (SELECT id FROM api_sig WHERE api_sig.className = 'SALTO'), NOW(), 'chgtSupport');
  ELSEIF( !(NEW.nom <=> OLD.nom) OR !(NEW.prenom <=> OLD.prenom) ) THEN
    INSERT INTO pile_sig(idLocataire, idApiSIG, dateCreation, flag)
    VALUES(NEW.id, (SELECT id FROM api_sig WHERE api_sig.className = 'SALTO'), NOW(), 'majLoc');
  END IF;
END