SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_policy_situation_delete]
	@situation_id int

as

set nocount on

declare @policy_id int
declare @last_policy_situation_id int  

select	@policy_id = pols_policy_id,
		@last_policy_situation_id = pp.pol_last_policy_situation_id
from pol_policies_situations pps
	inner join pol_policies pp on pp.pol_policy_id = pps.pols_policy_id
where pols_policy_situation_id = @situation_id

if isnull(@policy_id,0) > 0
begin

	/*
	if @last_policy_situation_id != @situation_id
	begin
		RAISERROR('Only the last policy situation can be deleted',16,1)
	end
	*/ 

	--invoices
	declare @invoices type_table_id

	insert into @invoices(id)
	select	distinct inv_invoice_id
	from	acc_invoices
	where	inv_policy_situation_id = @situation_id

	if @@ROWCOUNT > 0 
	begin 

		exec p_acc_invoices_delete_by_table_id @invoices

	end -- invoices

	--risks
	declare @risks_situations table(risk_id int, risk_situation_id int)

	insert into @risks_situations (	risk_id, risk_situation_id)
	select							riss_risk_id, riss_risk_situation_id
	from ris_risks_situations rrs
	where rrs.riss_policy_situation_id = @situation_id

	if @@ROWCOUNT > 0
	begin
	
		delete dst
		from ris_cpl dst
			inner join @risks_situations rrs on dst.rcpl_risk_situation_id = rrs.risk_situation_id

		delete dst
		from ris_variables_taxes dst
			inner join @risks_situations rrs on dst.risvt_risk_situation_id = rrs.risk_situation_id

		delete dst
		from ris_variables dst
			inner join @risks_situations rrs on dst.risv_risk_situation_id = rrs.risk_situation_id

		update dst
			set ris_last_risk_situation_id = null
		from ris_risks dst
			inner join @risks_situations rrs on rrs.risk_id = dst.ris_risk_id
		delete dst
		from ris_risks_situations dst
			inner join @risks_situations rrs on dst.riss_risk_situation_id = rrs.risk_situation_id

		--delete risks without situations
		delete dst
		from ris_risks dst
			inner join @risks_situations rrs on rrs.risk_id = dst.ris_risk_id 
		where not exists(select 1 from ris_risks_situations where riss_risk_id = rrs.risk_id)

		--select * from ris_risks

		--update last situation for remaining risks
		update rr
			set ris_last_risk_situation_id = t.maxsit,
				ris_situations_count = t.cnt		
		from ris_risks rr
			inner join (
							select	rr.ris_risk_id, 
									maxsit = max(rrs.riss_risk_situation_id), 
									cnt = count(*)
							from ris_risks_situations rrs
								inner join ris_risks rr on rrs.riss_risk_id = rr.ris_risk_id
							where rr.ris_policy_id = @policy_id
							group by rr.ris_risk_id
						) as t on rr.ris_risk_id = t.ris_risk_id

	end 

	--policies
	--last situation
	if @situation_id = @last_policy_situation_id
	begin
		update pol_policies 
			set pol_last_policy_situation_id = null 
		where pol_policy_id = @policy_id
	end


	delete dst
	from pol_cpl dst
	where dst.pcpl_policy_situation_id = @situation_id

	delete dst
	from pol_policies_situations_introducers dst
	where dst.polsi_policy_situation_id = @situation_id

	delete dst
	from pol_variables_taxes dst
	where dst.polvt_policy_situation_id = @situation_id

	delete dst
	from pol_variables dst
	where dst.polv_policy_situation_id = @situation_id

	delete dst
	from pol_policies_situations dst
	where dst.pols_policy_situation_id = @situation_id


	declare @t type_table_id

	--to be seen if possible to delete policy or the single situation due to referential integrity
	if not exists(select 1 from pol_policies_situations where pols_policy_id = @policy_id)
	begin

		delete 
		from	pol_policies
		where	pol_policy_id = @policy_id
	
	end
	else
	begin

		declare @situations_count int

		select	@last_policy_situation_id = max(pols_policy_situation_id),
				@situations_count = count(1)
		from pol_policies_situations
		where pols_policy_id = @policy_id
	 
		--update last situation
		update pp 
			set		pol_last_policy_situation_id = @last_policy_situation_id,
					pol_situations_count = @situations_count
		from pol_policies pp
			--inner join pol_policies_situations pps on		pps.pols_policy_id = pp.pol_policy_id
			--											and pps.pols_policy_situation_id = @last_policy_situation_id
		where pp.pol_policy_id = @policy_id


		--  return policy_id
		insert into @t(id) values(@policy_id)

	end

	select	* 
	from	@t

end









GO
