DROP PROCEDURE IF EXISTS rds.sp_deletefrllo;
-- =============================================
-- Author:      Vitacore (с) Anatoliy
-- Create date: 2021 02 17
-- Description: Удаление данных ФРЛЛО
-- =============================================
CREATE OR REPLACE PROCEDURE rds.sp_deletefrllo(
    "Id" uuid DEFAULT NULL::uuid,
    "RemovalAllowed" boolean = false
)
    LANGUAGE plpgsql
AS
$$
declare 
    p_id                uuid := "Id";
    p_removalallowed    boolean  := "RemovalAllowed";
BEGIN

    if (exists (select 1 from RDS.T_FRLLO_MESSAGE where ExportFrlloElectronicCertificate = p_id))
    then
        if (p_removalallowed = true)
        then
            delete from rds.t_frllo_message where ExportFrlloElectronicCertificate = p_id;
        else 
            raise exception 'Нельзя удалить запись, так как существуют связанные сообщения';
            return;
        end if;
    end if;

    LOOP
        EXIT WHEN NOT EXISTS(SELECT 1 FROM rds.T_EXPORT_FRLLO_EC_DATA WHERE ExportFrlloElectronicCertificate = "Id" LIMIT 1)
        ;WITH to_delete AS (SELECT ExportFrlloEcDataID AS id
                           FROM rds.T_EXPORT_FRLLO_EC_DATA
                           WHERE ExportFrlloElectronicCertificate = "Id"
                           LIMIT 10000
                          )
        DELETE
        FROM rds.T_EXPORT_FRLLO_EC_DATA d
            USING to_delete
        WHERE d.ExportFrlloEcDataID = to_delete.id;
    END LOOP;
    
    LOOP
        EXIT WHEN NOT EXISTS(SELECT 1 FROM rds.T_EXPORT_FRLLO_ELECTRONIC_CERTIFICATE WHERE ExportFrlloElectronicCertificateId = "Id" LIMIT 1)
        
        ;WITH to_delete AS (SELECT f.ExportFrlloElectronicCertificateId
                           FROM rds.T_EXPORT_FRLLO_ELECTRONIC_CERTIFICATE f
                           WHERE f.ExportFrlloElectronicCertificateId = "Id"
                           LIMIT 10000
                          )
        DELETE
        FROM rds.T_EXPORT_FRLLO_ELECTRONIC_CERTIFICATE f
            USING to_delete
        WHERE f.ExportFrlloElectronicCertificateId = to_delete.ExportFrlloElectronicCertificateId;
    
    
    END LOOP;
END;
$$;