DROP EVENT IF EXISTS Controllo_Magazzino;
DELIMITER $$
CREATE EVENT Controllo_Magazzino
ON SCHEDULE EVERY 14 DAY
DO
BEGIN
    CALL Scrivi_Ordine(02);
END $$
DELIMITER ;

DROP PROCEDURE IF EXISTS Scrivi_Ordine;
DELIMITER $$
CREATE PROCEDURE Scrivi_Ordine(IN Input_Sede INT)
BEGIN
    CREATE TABLE IF NOT EXISTS Ordine(
        Nome_Ingrediente INT,
        Quantita_Ordine INT
    );
    TRUNCATE TABLE Ordine;
    INSERT INTO Ordine
    SELECT F.Ingrediente_Usato, CAST(((1.2*SUM(F.Dose)-(SELECT SUM(CC.Quantita)
                                                           FROM Sede SS INNER JOIN Magazzino MM ON SS.ID_Sede = MM.Sede
                                                               INNER JOIN Confezione CC ON CC.Magazzino = MM.ID_Magazzino
                                                           WHERE CC.Ingrediente = F.Ingrediente_Usato
                                                           AND SS.ID_Sede = Input_Sede)))AS SIGNED) AS Da_Ordinare
    FROM Comanda C INNER JOIN Piatto_Ordinato PO ON PO.Comanda = C.ID_Comanda
        INNER JOIN Ricetta R ON R.ID_Ricetta = PO.Ricetta
        INNER JOIN Fase F ON F.Ricetta = R.ID_Ricetta
    WHERE ((C.Time_Stamp BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 14 DAY) AND CURRENT_DATE)
            OR (C.Partenza_Takeaway BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 14 DAY) AND CURRENT_DATE))
        AND C.Sede = Input_Sede
        AND F.Ingrediente_Usato IS NOT NULL
    GROUP BY F.Ingrediente_Usato
    HAVING SUM(F.Dose) > (SELECT SUM(CCC.Quantita)
                           FROM Sede SSS INNER JOIN Magazzino MMM ON SSS.ID_Sede = MMM.Sede
                               INNER JOIN Confezione CCC ON CCC.Magazzino = MMM.ID_Magazzino
                           WHERE CCC.Ingrediente = F.Ingrediente_Usato
                           AND SSS.ID_Sede = Input_Sede);
END; $$
DELIMITER ;