-- creo una function che prnde un medico e dice se questo ha
-- effettuato un numero mensile di visite crescenti per almeno
-- tre mesi consecutivi nello stesso anno, in uno tra gli
-- ultimi 10 anni
DROP FUNCTION IF EXISTS ha_tre_visite_crescenti;
DELIMITER $$
CREATE FUNCTION ha_tre_visite_crescenti(_medico CHAR(50))
RETURNS BOOL NOT DETERMINISTIC READS SQL DATA
BEGIN
  DECLARE anno INT;
  DECLARE mese INT;
  DECLARE res BOOL DEFAULT FALSE;
  DECLARE numVcurr INT;
  DECLARE numVprec INT;
  DECLARE numVprecprec INT;


  SET anno = YEAR(CURRENT_DATE - INTERVAL 10 YEAR);

  -- faccio un doppio ciclo in cui scorro tutti gli anni
  -- da 10 anni fa ad ora, e dal mese di marzo a dicembre
  WHILE anno < YEAR(CURRENT_DATE) DO
    SET mese = 3;
    -- sto attento alla condizione di uscita,
    -- se mi trovo nell'anno correne devo assicuramri di
    -- non superare il mese corrente
    WHILE (mese < 13 AND
        NOT( mese > MONTH(CURRENT_DATE)
        AND anno = YEAR(CURRENT_DATE))) DO
      -- calcolo le visite di questo mese
      SET numVcurr = (
                    SELECT COUNT(*)
                    FROM visita
                    WHERE Medico=_medico AND
                          YEAR(Data)=anno AND
                          MONTH(Data)=mese
                      );
      -- calcolo le visite del mese prima
      SET numVprec = (
                    SELECT COUNT(*)
                    FROM visita
                    WHERE Medico=_medico AND
                          YEAR(Data)=anno AND
                          MONTH(Data)=mese -1
                      );
      -- calcolo le visite del mese prima ancora
      SET numVprecprec = (
                    SELECT COUNT(*)
                    FROM visita
                    WHERE Medico=_medico AND
                          YEAR(Data)=anno AND
                          MONTH(Data)=mese-2
                      );

      -- scrivo sul risultato se le visite sono
      -- in ordine crescente
      SET res = res OR
    (numVcurr > numVprec AND numVprec > numVprecprec);

      SET mese = mese+1;
    END WHILE;

    SET anno = anno +1;
  END WHILE;

  RETURN res;
END $$
DELIMITER ;


-- controllo per ogni medico, se ha fatto tre visite crescenti
-- e ne esiste un altro della stessa specializzazione ma di
-- citta diverse che ha anche lui ha fatto tre visite
-- consecutive, se esistono stampo la specializzazione
SELECT DISTINCT m.Specializzazione
FROM medico m
WHERE ha_tre_visite_crescenti(m.Matricola) AND
      EXISTS (SELECT *
              FROM medico s
              WHERE s.Specializzazione=m.Specializzazione AND
                    s.Matricola=m.Matricola AND -- <- sono un coglionazzo
                    s.Citta<>m.Citta AND
                    ha_tre_visite_crescenti(s.Matricola)
              );