DROP PROCEDURE IF EXISTS dbo.sp_deletefrlloremainsprocess;
DROP PROCEDURE IF EXISTS public.sp_deletefrlloremainsprocess;
-- =============================================
-- Author:      Vitacore (с) Anatoliy
-- Create date: 2024 11 26
-- Description: Удаление данных ФРЛЛО
-- =============================================
CREATE OR REPLACE PROCEDURE rds.sp_deletefrlloremainsprocess(
    "Id" uuid,
    "OnlyClean" boolean,
    "RemovalAllowed" boolean
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_id                uuid    := "Id";
    p_onlyclean         boolean := "OnlyClean";
    p_removalallowed    boolean := "RemovalAllowed";
BEGIN
    if (exists (select 1 from RDS.t_frllo_message where exportfrlloremains = p_id))
        then
        if (p_removalallowed = TRUE) 
            then
                delete from rds.t_frllo_message where exportfrlloremains = p_id;
            else
                raise exception 'Нельзя удалить запись, так как существуют связанные сообщения';
                return;
        end if;
    end if;

    if (p_removalallowed = FALSE)
            then
                IF EXISTS (SELECT 1 FROM rds.t_export_frllo_remains WHERE exportfrlloremainsid = p_id AND status = 2)
                then
                    RAISE EXCEPTION 'Удаление запрещено!';
                    return;
                end if;            
                IF EXISTS (SELECT 1 FROM rds.t_export_frllo_remains_data WHERE exportfrlloremains = p_id AND frllostatus <> 400 AND frllostatus IS NOT NULL)
                then
                    RAISE 'Есть успешно принятые в ФРЛЛО данные. Удаление запрещено!';
                    return;
                end if;
            end if;

    LOOP
        IF NOT EXISTS (SELECT 1 FROM rds.t_export_frllo_remains_data WHERE exportfrlloremains = p_id)
        THEN
            EXIT;
        END IF;
        DELETE
        FROM rds.t_export_frllo_remains_data
        WHERE exportfrlloremains = p_id
          AND ctid IN (SELECT ctid FROM rds.t_export_frllo_remains_data WHERE exportfrlloremains = p_id LIMIT 1000);
    END LOOP;
    
    IF (p_onlyclean = TRUE)
    THEN
        UPDATE rds.t_export_frllo_remains SET status = 0 WHERE exportfrlloremainsid = p_id;
    ELSE
        DELETE FROM rds.t_export_frllo_remains WHERE exportfrlloremainsid = p_id;
    END IF;
END;
$$;