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,
    "RemovalAllowed" boolean = false
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_id                uuid    := "Id";
    p_removalallowed    boolean := "RemovalAllowed";
    v_unloaded          boolean;
BEGIN

    SELECT unloaded
        INTO v_unloaded
    FROM rds.t_export_frllo_recipe
    WHERE exportfrllorecipeid = p_id;

    if (v_unloaded = TRUE AND p_removalallowed = FALSE)
    then
        RAISE EXCEPTION 'Запрещено удаление выгруженных данных!';
        RETURN;
    end if;

    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;

    CREATE TEMP TABLE tmp_rec_id (id uuid);
    CREATE INDEX ix_tmp_rec_id_id_000001 ON tmp_rec_id(id);
    CREATE TEMP TABLE tmp_d_rec_table (id uuid);
    CREATE INDEX ix_rec_table_id_000001 ON tmp_d_rec_table(id);
    CREATE TEMP TABLE tmp_d_elem_table (id uuid);
    CREATE INDEX ix_elem_table_id_000001 ON tmp_d_elem_table(id);

    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;

    WHILE EXISTS (
        SELECT 1
        FROM tmp_rec_id a
        JOIN rds.t_recipe b ON a.id = b.recipeid
    ) LOOP
        DELETE FROM tmp_d_rec_table;

        WITH to_update AS (
            SELECT rec.ctid
            FROM rds.t_recipe rec
            JOIN rds.t_export_frllo_recipe_data efrd
                ON efrd.objid = rec.recipeid
            JOIN tmp_rec_id dr
                ON dr.id = rec.recipeid
            LIMIT 1000
        ),
        updated AS (
            UPDATE rds.t_recipe rec
            SET frllotype = Case WHEN efrd.FrlloStatus is null and rec.FrlloType = 0 THEN null 
                                 WHEN efrd.FrlloStatus is null and rec.FrlloType = 2 THEN 0 
                                 WHEN efrd.FrlloStatus in (1,400) and rec.FrlloType = 1 then null
                                 WHEN efrd.FrlloStatus in (1,400) and rec.FrlloType = 3 then 0
                                 ELSE rec.FrlloType
                            END
            FROM rds.t_export_frllo_recipe_data efrd
            WHERE rec.ctid IN (SELECT ctid FROM to_update)
              AND efrd.objid = rec.recipeid
            RETURNING rec.recipeid
        )
        INSERT INTO tmp_d_rec_table(id)
        SELECT recipeid FROM updated;

        DELETE
        FROM tmp_rec_id r
        WHERE EXISTS (SELECT 1 FROM tmp_d_rec_table e WHERE e.id = r.id);
    END LOOP;

    WHILE EXISTS (
        SELECT 1
        FROM tmp_rec_id a
        JOIN rds.t_recipe_element b ON a.id = b.recipeelementid
    ) LOOP
        DELETE FROM tmp_d_elem_table;

        WITH to_update AS (
            SELECT rec_elem.ctid
            FROM rds.t_recipe_element rec_elem
            JOIN rds.t_export_frllo_recipe_data efrd
                ON efrd.objid = rec_elem.recipeelementid
            JOIN tmp_rec_id dr
                ON dr.id = rec_elem.recipeelementid
            LIMIT 1000
        ),
        updated AS (
            UPDATE rds.t_recipe_element rec_elem
            SET frllotype = CASE  WHEN efrd.FrlloStatus is null and rec_elem.FrlloType = 0 THEN null 
                                  WHEN efrd.FrlloStatus is null and rec_elem.FrlloType = 2 THEN 0 
                                  WHEN efrd.FrlloStatus in (1,400) and rec_elem.FrlloType = 1 then null
                                  WHEN efrd.FrlloStatus in (1,400) and rec_elem.FrlloType = 3 then 0
                                  ELSE rec_elem.FrlloType
                            END
            FROM rds.t_export_frllo_recipe_data efrd
            WHERE rec_elem.ctid IN (SELECT ctid FROM to_update)
              AND efrd.objid = rec_elem.recipeelementid
            RETURNING rec_elem.recipeelementid
        )
        INSERT INTO tmp_d_elem_table(id)
        SELECT recipeelementid FROM updated;

        DELETE
        FROM tmp_rec_id r
        WHERE EXISTS(SELECT 1 FROM tmp_d_elem_table  e WHERE e.id = r.id);
    END LOOP;

    LOOP
        IF NOT EXISTS (SELECT 1 FROM rds.t_export_frllo_recipe_data WHERE exportfrllorecipe = p_id)
        THEN
            EXIT;
        END IF;
        DELETE
        FROM rds.t_export_frllo_recipe_data
        WHERE exportfrllorecipe = p_id
          AND ctid IN (SELECT ctid FROM rds.t_export_frllo_recipe_data WHERE exportfrllorecipe = p_id LIMIT 1000);
    END LOOP;

    LOOP
        IF NOT EXISTS (SELECT 1 FROM rds.t_export_frllo_recipe WHERE exportfrllorecipeid = p_id)
        THEN
            EXIT;
        END IF;
        DELETE
        FROM rds.t_export_frllo_recipe
        WHERE exportfrllorecipeid = p_id
          AND ctid IN (SELECT ctid FROM rds.t_export_frllo_recipe WHERE exportfrllorecipeid = p_id LIMIT 1000);
    END LOOP;
END;
$$;