-- =============================================
-- Author:		Anatoliy Pokus
-- Create date: 2017-06-13
-- Description:	Удаление пустого остатка на складе
-- =============================================
DROP PROCEDURE IF EXISTS stor.sp_deleteremain;

CREATE PROCEDURE stor.sp_deleteremain(
    "invoiceID" uuid
)
    LANGUAGE plpgsql
AS
$$
BEGIN
    DROP TABLE IF EXISTS tempdata;
    CREATE TEMP TABLE tempdata
    (
        id uuid
    );
    
    INSERT INTO tempdata
    SELECT sr.sentremainid
    FROM aps.t_sent_remain sr
    JOIN stor.t_remains    r ON r.remainsid = sr.remains
    WHERE r.storageinvoiceadd = "invoiceID";
    
    DELETE
    FROM aps.t_sent_remain sr
        USING tempdata d
    WHERE d.id = sr.sentremainid;
    
    TRUNCATE TABLE tempdata;
    
    INSERT INTO tempdata
    SELECT re.recipeelementsid
    FROM aps.t_recipe_elements re
    JOIN aps.t_recipe          rec ON rec.recipeid = re.recipe
    JOIN stor.t_remains        r ON r.remainsid = re.remains
    WHERE rec.status <> 1
      AND r.storageinvoiceadd = "invoiceID";
    
    DELETE
    FROM aps.t_recipe_elements re
        USING tempdata d
    WHERE d.id = re.recipeelementsid;
    
    TRUNCATE TABLE tempdata;
    
    INSERT INTO tempdata
    SELECT pt.remainslinkpackagetypeid AS id
    FROM stor.t_remains_link_package_type pt
    JOIN stor.t_remains                   r ON r.remainsid = pt.remains
    WHERE r.storageinvoiceadd = "invoiceID"
      AND r.amount = 0;
    
    DELETE
    FROM stor.t_remains_link_package_type pt
        USING tempdata dd
    WHERE dd.id = pt.remainslinkpackagetypeid;
    
    TRUNCATE TABLE tempdata;
    
    INSERT INTO tempdata
    SELECT pdd.patientdrugdeliveryid
    FROM stor.t_patient_drug_delivery pdd
    JOIN stor.t_remains               r ON r.remainsid = pdd.remains
    WHERE r.storageinvoiceadd = "invoiceID"
      AND r.amount = 0;
    
    DELETE
    FROM stor.t_patient_drug_delivery pdd
        USING tempdata dd
    WHERE dd.id = pdd.patientdrugdeliveryid;
    
    TRUNCATE TABLE tempdata;
    
    INSERT INTO tempdata
    SELECT r.remainsid
    FROM stor.t_remains r
    WHERE r.storageinvoiceadd = "invoiceID"
      AND r.amount = 0;
    
    DELETE
    FROM stor.t_remains r
        USING tempdata dd
    WHERE dd.id = r.remainsid;
    
    DROP TABLE tempdata;
END;
$$;
