DROP FUNCTION IF EXISTS therapy_failures;
DELIMITER $$
CREATE FUNCTION therapy_failures(Cod VARCHAR(4),Farmaco VARCHAR(50))
RETURNS VARCHAR(50) NOT DETERMINISTIC
BEGIN
declare settore varchar(50);

        SELECT P.SettoreMedico into settore
        FROM Terapia T INNER JOIN Patologia P ON P.Nome = T.Patologia
        WHERE T.Paziente = Cod
        AND MONTH(T.DataInizioTerapia) = CURRENT_DATE() - INTERVAL 1 MONTH
        AND T.DataFineTerapia IS NOT NULL
        AND MONTH(T.DataFineTerapia) = MONTH(T.DataInizioTerapia)
        AND YEAR(T.DataFineTerapia) = YEAR(T.DataInizioTerapia)
        AND T.Farmaco = Farmaco
        AND NOT EXISTS ( SELECT * FROM Esordio E
                         WHERE E.Patologia = T.Patologia
                         AND E.Paziente = T.Paziente
                         AND T.DataEsordio = E.DataEsordio
                         AND E.DataGuarigione IS NOT NULL)
        GROUP BY T.Paziente,P.SettoreMedico
    	HAVING count(*) > ALL(
            SELECT COUNT(*)
            FROM Terapia T1 INNER JOIN Patologia P1 ON P1.Nome = T1.Patologia
            WHERE T1.Paziente = Cod
            AND MONTH(T1.DataInizioTerapia) = CURRENT_DATE() - INTERVAL 1 MONTH
            AND T1.DataFineTerapia IS NOT NULL
            AND MONTH(T1.DataFineTerapia) = MONTH(T1.DataInizioTerapia)
            AND YEAR(T1.DataFineTerapia) = YEAR(T1.DataInizioTerapia)
            AND T1.Farmaco = Farmaco
            AND P1.SettoreMedico != p.SettoreMedico
            AND NOT EXISTS ( SELECT *
                            FROM Esordio E
                            WHERE E.Patologia = T1.Patologia
                            AND E.Paziente = T1.Paziente
                            AND T1.DataEsordio = E.DataEsordio
                            AND E.DataGuarigione IS NOT NULL)
            GROUP BY T1.Paziente,P1.SettoreMedico

        );

    /* PER IL PAZIENTE FORNITO E PER OGNI SETTORE MEDICO, CONTO LE TERAPIE FATTE CON IL FARMACO FORNITO, FALLITE,
    OSSIA QUELLE CHE HANNO UN ESORDIO RELATIVO CON DATAGUARIGIONE A NULL */


    IF(Settore IS NULL) THEN /* SE NON E' STATO TROVATO ALCUN SETTORE NON RITORNA NULL E INTERROMPE L'ESECUZIONE */
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = "Nessun settore medico trovato";
    ELSE
        RETURN Settore;  /* ALTRIMENTI RITORNO IL SETTORE */
    END IF;

END $$
DELIMITER ;