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" bit = 0
)
language plpgsql
as
$$
begin
	declare _Unloaded bit := (select Unloaded FROM RDS.T_EXPORT_FRLLO_RECIPE WHERE ExportFrlloRecipeID = "Id");
	if (_Unloaded = 1 and "ForseDelete" = 0)
		then raise notice('Запрещенно удаление выгруженных данных!', 16, 10);
	end if;
	if(exists(select 1 from RDS.T_EXPORT_FRLLO_RECIPE_DATA where ExportFrlloRecipe = "Id" and FrlloStatus <> 400 and FrlloStatus is not null))
		then raise notice('Есть успешно принятые в ФРЛЛО данные. Удаление запрещенно!', 16, 10);
	end if;
	if (_Unloaded = 1)
		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 = "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);

			declare _count int := select count(*) FROM Tmp_REC_ID;
			loop
				if (_count <= 0) 
					then exit;
				end if;
				update RDS.T_RECIPE rec
				set rec.FrlloType = CASE WHEN efrd.RecipeType = 2 THEN 0 ELSE NULL END
				from T_EXPORT_FRLLO_RECIPE_DATA efrd on efrd.ObjId = rec.RecipeID
				join Tmp_REC_ID tempRecId on tempRecId.id = rec.RecipeID

				delete r from Tmp_REC_ID r where exists(select 1 from Tmp_D_REC_TABLE e where e.id = r.id);
			end loop;

			loop
				if (_count <= 0) 
					then exit;
				end if;
				update RDS.T_RECIPE_ELEMENT rec_elem
				set rec_elem.FrlloType = CASE WHEN efrd.RecipeType = 3 THEN 1 ELSE NULL END
				from T_EXPORT_FRLLO_RECIPE_DATA efrd ON efrd.ObjId = rec_elem.RecipeElementID
				join Tmp_REC_ID tempRecId on tempRecId.id = rec_elem.RecipeElementID

				delete r from Tmp_REC_ID r where exists(select 1 from Tmp_D_REC_TABLE e where e.id = r.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;
$$;