DROP PROCEDURE IF EXISTS rds.sp_CleanFrlloCertificateProcess;

CREATE OR REPLACE PROCEDURE rds.sp_CleanFrlloCertificateProcess(
    "ExportId" uuid,
    "All" boolean
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_exportid uuid := "ExportId";
    v_num      int  := 1000;
BEGIN
    CREATE TEMP TABLE tmp_obj
    (
        id  uuid NOT NULL UNIQUE,
        num serial
    );
    
    INSERT INTO
        tmp_obj (id)
    SELECT DISTINCT fed.ExportFrlloEcDataID
    FROM rds.T_EXPORT_FRLLO_EC_DATA fed
    WHERE fed.ExportFrlloElectronicCertificate = p_exportid;
    
    LOOP
        IF
            NOT EXISTS (SELECT 1 FROM tmp_obj LIMIT 1)
        THEN
            EXIT;
        END IF;
        UPDATE rds.T_EXPORT_FRLLO_EC_DATA fed
        SET frlloid     = NULL,
            frllostatus = case when "All" = '0' then frllostatus else NULL end,
            frlloerror  = case when "All" = '0' then frlloerror else NULL end,
            Recipe_id = null,
            Recipe_release_id = null
        WHERE EXISTS(SELECT 1 FROM tmp_obj t WHERE t.id = ExportFrlloEcDataID AND t.num <= v_num)
          AND ExportFrlloElectronicCertificate = p_exportid;
        
        DELETE FROM tmp_obj WHERE num <= v_num;
        v_num := v_num + 1000;
    END LOOP;

END;
$$