DROP FUNCTION IF EXISTS akuz.Milk_Recipe_GenNumber_Insert CASCADE;

CREATE FUNCTION AKUZ.Milk_Recipe_GenNumber_Insert()
RETURNS trigger 
AS $Milk_Recipe_GenNumber_Insert$
Begin
declare	_num int;
	_minNumber int;
	_maxNumber int;
	_seria varchar(4);
BEGIN	
	select into
		_minNumber, _maxNumber, _seria
		ls.MilkNumberBegin, ls.MilkNumberEnd, case when ls.RegionCode = '1600000000000' then ls.LpuMainCode else ls.MilkSeria::varchar(4) end
	from AKUZ.T_MILK_RECIPE i 
	Join AKUZ.T_MEDICAL_RECORD mr On i.MedicalRecordID = mr.MedicalRecordID
	inner join AKUZ.T_RESOURCE r on mr.Post = r.ResourceID
	inner join AKUZ.T_DIVISION d on r.Division = d.DivisionID
	inner join AKUZ.T_BRANCH br on d.Branch = br.LpuID
	inner join AKUZ.T_LPU_SETTINGS ls on br.LpuMain = ls.LpuMain
	where i.MedicalRecordID = mr.MedicalRecordID and i.MedicalRecordID = new.MedicalRecordID;
	
	if _minNumber >= _maxNumber
	then
		Raise Exception Using Message = 'Неправильно задан диапазон номеров';
		return null;
	end if;

	-- проверка такого же диапазона по этому пациенту
	if exists 
	(
		select * from AKUZ.T_AMBULANCE_CARD_RECORD acr 
		where acr.MedicalRecordID = new.MedicalRecordID
		-- исключаем дополнительный (его проверять не надо) 
		and new.Additional = '0'
		and exists 
		(
			select * from AKUZ.T_MILK_RECIPE mr
			inner join 
				AKUZ.T_AMBULANCE_CARD_RECORD acr2 
				on mr.MedicalRecordID = acr2.MedicalRecordID
			where mr.MedicalRecordID <> new.MedicalRecordID -- исключаем себя
			-- исключаем отбракованные
			and not exists(select* from AKUZ.T_RECIPE_DISCARD rd where rd.MilkRecipe = mr.MedicalRecordID)
			-- связка по этому пациенту
			and acr2.AmbulanceCard = acr.AmbulanceCard
			-- дополнительный
			and mr.Additional = '0'
			And mr.ProductType = new.ProductType
			-- проверка диапозона начала и конца дейтсвия
			and i.BeginDate <= mr.EndDate 
			and i.EndDate >= mr.BeginDate
		)
	)
	then
		Raise Exception Using Message = 'У данного пациента существует выписанный рецепт за указанный период.';
		return null;
	end if;

	if new.Number = '-1'
	then
		select max(mr.Number) + 1 
		from AKUZ.T_MILK_RECIPE mr
		into _num
		where mr.number >= _minNumber 
		and mr.number <= _maxNumber
		and mr.Seria = _seria
		for update;
		
		if _num is null 
		then
			_num := _minNumber;
		end if;
		
		if _num is null or _num > _maxNumber
		then
			Raise Exception Using Message = 'Нет свободных номеров в диапазоне';
			return null;
		end if;

		update AKUZ.T_MILK_RECIPE 
		set Number = _num 
		where MedicalRecordID = new.MedicalRecordID;
	else
		_num = new.Number;
		if exists 
		(
			select * from AKUZ.T_MILK_RECIPE mr
			where mr.Number = _num 
			and not exists(select * from AKUZ.T_RECIPE_DISCARD rd where rd.MilkRecipe = mr.MedicalRecordID)
			and mr.MedicalRecordID <> new.MedicalRecordID
		)
		then
			Raise Exception Using Message = 'Заданный номер уже используется';
			return null;
		end if;
	end if;
	end;
END;
$Milk_Recipe_GenNumber_Insert$ LANGUAGE plpgsql;

CREATE TRIGGER Milk_Recipe_GenNumber 
after INSERT ON AKUZ.T_MILK_RECIPE
FOR EACH ROW
EXECUTE FUNCTION akuz.Milk_Recipe_GenNumber_Insert();
