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
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_id        uuid    := "Id";
    p_onlyclean boolean := "OnlyClean";
BEGIN
    IF EXISTS (SELECT 1 FROM rds.t_export_frllo_remains WHERE exportfrlloremainsid = "Id" AND status = 2)
    THEN
        RAISE EXCEPTION 'Удаление запрещено!';
        RETURN;
    END IF;
    
    IF EXISTS (SELECT 1
               FROM rds.t_export_frllo_remains_data
               WHERE exportfrlloremains = "Id"
                 AND frllostatus <> 400
                 AND frllostatus IS NOT NULL
              )
    THEN
        RAISE 'Есть успешно принятые в ФРЛЛО данные. Удаление запрещено!';
        RETURN;
    END IF;
    
    LOOP
        IF NOT EXISTS (SELECT 1 FROM rds.t_export_frllo_remains_data WHERE exportfrlloremains = "Id")
        THEN
            EXIT;
        END IF;
        DELETE
        FROM rds.t_export_frllo_remains_data
        WHERE exportfrlloremains = "Id"
          AND ctid IN (SELECT ctid FROM rds.t_export_frllo_remains_data WHERE exportfrlloremains = "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 = "Id";
    END IF;
END;
$$;