DROP PROCEDURE IF EXISTS public.sp_deletefrllorecipeprocess;
DROP PROCEDURE IF EXISTS dbo.sp_deletefrllorecipeprocess;
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2024 11 25
-- Description: Удаление данных ФРЛЛО
-- =============================================
CREATE OR REPLACE PROCEDURE rds.sp_deletefrllorecipeprocess(
    "Id" uuid DEFAULT NULL::uuid,
    "ForseDelete" boolean = false,
    "RemovalAllowed" boolean = false
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_id                uuid    := "Id";
    p_forsedelete       boolean := "ForseDelete";
    p_removalallowed    boolean := "RemovalAllowed";
    v_unloaded          boolean;
    v_count             boolean;
BEGIN
    
    SELECT unloaded
    INTO v_unloaded
    FROM rds.t_export_frllo_recipe
    WHERE exportfrllorecipeid = p_id;
    
    if (exists (select 1 from RDS.t_frllo_message where exportfrllorecipe = p_id))
    then
        if (p_removalallowed = true)
        then
            delete from rds.t_frllo_message where exportfrllorecipe = p_id;
        else           
            raise exception 'Нельзя удалить запись, так как существуют связанные сообщения';
            return;
        end if;
    end if;

    if (v_unloaded = TRUE AND p_forsedelete = FALSE)
    then
        RAISE EXCEPTION 'Запрещено удаление выгруженных данных!';
        RETURN;
    end if;
    if (exists (select 1 from rds.t_export_frllo_recipe_data where exportfrllorecipe = p_id and frllostatus <> 400 and frllostatus is not null))
    then
        RAISE EXCEPTION 'Есть успешно принятые в ФРЛЛО данные. Удаление запрещено!';
        RETURN;
    end if;

    IF (v_unloaded = TRUE)
    THEN
        CREATE TEMP TABLE tmp_rec_id
        (
            "id" uuid
        );
        
        INSERT INTO tmp_rec_id("id")
        SELECT efrd.objid AS recipeid
        FROM rds.t_export_frllo_recipe_data efrd
        WHERE efrd.exportfrllorecipe = p_id
          AND (CASE
                   WHEN efrd.recipetype IN (0, 2)
                       AND EXISTS(SELECT * FROM rds.t_recipe r WHERE r.recipeid = efrd.objid)
                       THEN 1
                   WHEN efrd.recipetype IN (1, 3) AND
                        EXISTS(SELECT * FROM rds.t_recipe_element r WHERE r.recipeelementid = efrd.objid) THEN 1
                   ELSE 0
               END) = 1;
        
        CREATE TEMP TABLE tmp_d_rec_table
        (
            "id" uuid
        );
        CREATE TEMP TABLE tmp_d_elem_table
        (
            "id" uuid
        );
        
        SELECT COUNT(*)
        INTO v_count
        FROM tmp_rec_id;
        
        LOOP
            IF (v_count <= 0) THEN
                EXIT;
            END IF;
            
            UPDATE rds.t_recipe rec
            SET frllotype = CASE
                                WHEN efrd.recipetype = 2 THEN 0
                                ELSE NULL
                            END
            FROM t_export_frllo_recipe_data efrd
            JOIN tmp_rec_id temprecid ON temprecid.id = rec.recipeid
            WHERE efrd.objid = rec.recipeid;
            
            DELETE
            FROM tmp_rec_id r
            WHERE EXISTS (SELECT 1 FROM tmp_d_rec_table e WHERE e.id = r.id);
            
            SELECT COUNT(*)
            INTO v_count
            FROM tmp_rec_id;
        
        END LOOP;
        
        LOOP
            IF (v_count <= 0) THEN
                EXIT;
            END IF;
            
            UPDATE rds.t_recipe_element rec_elem
            SET frllotype = CASE WHEN efrd.recipetype = 3 THEN 1 ELSE NULL END
            FROM t_export_frllo_recipe_data efrd
            JOIN tmp_rec_id temprecid ON temprecid.id = rec_elem.recipeelementid
            WHERE efrd.objid = rec_elem.recipeelementid;
            
            DELETE
            FROM tmp_rec_id r
            WHERE EXISTS(SELECT 1 FROM tmp_d_rec_table e WHERE e.id = r.id);
            
            SELECT COUNT(*)
            INTO v_count
            FROM tmp_rec_id;
            
        END LOOP;
        
        LOOP
            IF NOT EXISTS (SELECT 1 FROM rds.t_export_frllo_recipe_data WHERE exportfrllorecipe = "Id")
            THEN
                EXIT;
            END IF;
            DELETE
            FROM rds.t_export_frllo_recipe_data
            WHERE exportfrllorecipe = "Id"
              AND ctid IN (SELECT ctid FROM rds.t_export_frllo_recipe_data WHERE exportfrllorecipe = "Id" LIMIT 1000);
        END LOOP;
        
        LOOP
            IF NOT EXISTS (SELECT 1 FROM rds.t_export_frllo_recipe WHERE exportfrllorecipeid = "Id")
            THEN
                EXIT;
            END IF;
            DELETE
            FROM rds.t_export_frllo_recipe
            WHERE exportfrllorecipeid = "Id"
              AND ctid IN (SELECT ctid FROM rds.t_export_frllo_recipe WHERE exportfrllorecipeid = "Id" LIMIT 1000);
        END LOOP;
    END IF;
END;
$$;