drop PROCEDURE IF EXISTS stor.sp_deleteremain;

CREATE OR REPLACE PROCEDURE stor.sp_deleteremain(
    remainsid uuid
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_remainsid uuid := remainsid;
BEGIN
    
    DELETE
    FROM stor.t_remains_link_package_type pt
        USING stor.t_remains r
    WHERE r.remainsid = pt.remains
      AND r.remainsid = p_remainsid
      AND r.amount = 0
      AND NOT EXISTS (SELECT 1
                      FROM stor.t_invoice_item ii
                      WHERE (ii.remains = r.remainsid OR ii.prepareremains = r.remainsid)
                     )
      AND NOT EXISTS(SELECT 1 FROM stor.t_storage_rest_on_day rest WHERE rest.remain = p_remainsid);
    
    DELETE
    FROM stor.t_remains r
    WHERE r.remainsid = p_remainsid
      AND r.amount = 0
      AND NOT EXISTS (SELECT 1
                      FROM stor.t_invoice_item ii
                      WHERE (ii.remains = r.remainsid OR ii.prepareremains = r.remainsid)
                     )
      AND NOT EXISTS (SELECT 1 FROM stor.t_storage_rest_on_day rest WHERE rest.remain = p_remainsid);
    
    IF (EXISTS (SELECT 1 FROM vclib.t_object_modification_action WHERE name = 'SP_GetSetStorRemains' AND active = 1
               )) THEN
        BEGIN
            CALL public.sp_getsetstorremains(NULL, 0, 0, p_remainsid);
        END;
    
    END IF;
    
END;
$$