DROP PROCEDURE IF EXISTS sp_GenerateRecipeNumber;

CREATE PROCEDURE sp_GenerateRecipeNumber
(
    "EntityID" integer,
    "NumberName" varchar(64),
    "StartNum" integer,
	"EndNum" integer
,   INOUT _NextNumber integer DEFAULT -1
)
    language plpgsql
as
$$
Begin

	select t.NextNumber 
	from akuz.T_GEN_NUMBERS t
	into _NextNumber
	where t.EntityID = _EntityID 
	and t.NumberName like _NumberName
	for update;
	
	if _NextNumber is null 
	then
		_NextNumber := "StartNum";
		insert into akuz.T_GEN_NUMBERS (GenNumbersID, EntityID, NumberName, NextNumber) 
		select gen_random_uuid(), "EntityID", _NumberName, _NextNumber;
	else
		_NextNumber := _NextNumber + 1;
		update akuz.T_GEN_NUMBERS 
		set NextNumber = _NextNumber
		where EntityID = "EntityID" 
		and NumberName = _NumberName;
	end if;
	
	if _NextNumber > "EndNum" or _NextNumber < "StartNum"
	then
		Raise Exception Using Message = 'Превышен диапазон номеров.';
		_NextNumber := -1;
        RETURN;
	end if;
	
	return;
	Exception
		WHEN Others 
		THEN Raise Exception Using Message = 'Ошибка генерации номера.';
        RETURN;
End;
$$;