with params as (
	select 
		cast($1 as bigint) as lpu_id, 
		cast($2 as bigint) as lpubuilding_id,
		cast($3 as bigint) as lpuunit_id,
		cast($4 as bigint) as lpusection_id,
		cast($5 as bigint) as lpuattachtype_id,
		cast($6 as bigint) as lpuregiontype_id,
		cast($7 as bigint) as lpuregion_id,
		cast($8 as bigint) as beg_person_age,
		cast($9 as bigint) as end_person_age,
		cast(to_timestamp($10,'dd.mm.yyyy') as timestamp) - interval '6 months' as begdate, --дата начала проверки вакцинирования
		cast(to_timestamp($11,'dd.mm.yyyy') as timestamp) as setdate,
		cast(to_timestamp('01.12.2020','dd.mm.yyyy') as timestamp) as covidvac_begdate, --официальная дата начала вакцинации от ковид
		cast($12 as bigint) as region_id
	limit 1
),

uc_vac as (
	select 
		uc.uslugacomplex_id
	from v_uslugacomplex uc 
	where uc.uslugacomplex_code = 'B04.014.004.007'
),
vac_vaccine as ( --карта профилактических прививок: старый модуль
	select 
		v.Vaccine_id
	from vac.S_Vaccine v 
	where v.Vaccine_Name ilike '%ковид%'  
	   or v.Vaccine_Name ilike '%covid%'
),
vac_diag as (
	select 
		diag.diag_id
	from dbo.diag diag 
	inner join params p on true
	where diag.diag_code = 'U11.9' or (diag.diag_code = 'Z25.8' and p.region_id = 10)
),
nkvi_diag as (
	select 
		diag.diag_id 
	from dbo.diag diag 
	where diag.diag_code in ('U07.1','U07.2')
),

pcs as (
	select 
		pcs.PersonCard_id,
		pcs.person_id,
		pcs.LpuRegion_id,
		pcs.LpuRegionType_id,
		pcs.LpuAttachType_id
	from params p
	inner join dbo.PersonCardState pcs on pcs.lpu_id = p.lpu_id
	where pcs.lpu_id = p.lpu_id 
		and pcs.PersonCardState_begDate <= p.setdate 
		and (pcs.PersonCardState_endDate >= p.setdate or pcs.PersonCardState_endDate is null)
), f_personcard as ( --прикрепление со всеми фильтрами
	select
		pcs.PersonCard_id as personcard_id,
		pcs.person_id,
		lrt.LpuRegionType_name,
		lr.lpuregion_name
	from pcs
	inner join params p on true 
	left join dbo.LpuRegion lr  on pcs.LpuRegion_id = lr.LpuRegion_id
	left join dbo.LpuRegionType lrt  on coalesce(lr.LpuRegionType_id, pcs.LpuRegionType_id) = lrt.LpuRegionType_id
	left join v_lpusection ls  on ls.lpusection_id = lr.lpusection_id

	where (pcs.LpuAttachType_id = p.lpuattachtype_id or p.lpuattachtype_id is null)
		and (lrt.LpuRegionType_id = p.lpuregiontype_id or p.lpuregiontype_id is null)
		and (lr.lpuregion_id = p.lpuregion_id or p.lpuregion_id is null)
		and (ls.lpusection_id = p.lpusection_id or p.lpusection_id is null)
		and (ls.lpuunit_id = p.lpuunit_id or p.lpuunit_id is null)
		and (ls.lpubuilding_id = p.lpubuilding_id or p.lpubuilding_id is null)
),

not_vac_personcard as (
	select
		pc.*,

		last_epl_vizit.vac_date as vac_date1,
		coalesce(last_vac_journal_new.vac_date, last_vac_journal_old.vac_date) as vac_date2,

		last_nkvi_epl.EvnPL_id as last_nkvi1,
		last_nkvi_eps.EvnSection_id as last_nkvi2,
		last_nkvi_epl.evnpl_disdt as last_nkvi1_date,
		last_nkvi_eps.evnps_disdt as last_nkvi2_date

	from f_personcard pc
	inner join params p on true

	left join lateral ( --закрытые ТАП: посещения с диагнозом/последняя вакцинация
		select
			evnvizitpl.EvnVizitPL_id,
			case when evnvizitpl.EvnVizitPL_setDT is not null and vac_usluga.EvnUsluga_setDT is not null then 
				case when evnvizitpl.EvnVizitPL_setDT > vac_usluga.EvnUsluga_setDT then evnvizitpl.EvnVizitPL_setDT else vac_usluga.EvnUsluga_setDT end
				else coalesce(evnvizitpl.EvnVizitPL_setDT, vac_usluga.EvnUsluga_setDT) 
				end as vac_date
				
		from v_evnvizitpl evnvizitpl 
		inner join params p on evnvizitpl.evnvizitpl_setdt between p.CovidVac_begdate and p.setdate + interval '1 day' - interval '1 second'
		inner join v_evnpl epl_close on epl_close.evnpl_id = evnvizitpl.evnvizitpl_pid
			and epl_close.evnpl_isfinish = 2 
		left join vac_diag diag  on diag.diag_id = evnvizitpl.Diag_id
		left join lateral (
			select
				vac_diag.Diag_id
			from v_EvnDiagPLSop diag_pl
			inner join vac_diag on vac_diag.diag_id = diag_pl.Diag_id	
			where diag_pl.EvnDiagPLSop_pid = evnvizitpl.EvnVizitPL_id
			limit 1
		) diag_s on true --сопутствующий диагноз
		
		left join lateral (
			select 
				eu.evnusluga_id,
				eu.evnusluga_setDT
			from v_evnusluga eu 
			inner join uc_vac on uc_vac.UslugaComplex_id = eu.UslugaComplex_id
			where 
				eu.Evnusluga_pid = evnvizitpl.EvnVizitPL_id
				--and eu.EvnClass_id in (22,43,47) --из v_EvnUsluga
				and eu.Evnusluga_setDT between p.CovidVac_begdate and p.setdate + interval '1 day' - interval '1 second'
			order by eu.Evnusluga_setDT desc
			limit 1
		) vac_usluga on true --услуги/последняя вакцинация
		
		where epl_close.person_id = pc.person_id
			and (diag.diag_id is not null 
			  or diag_s.diag_id is not null
			  or vac_usluga.evnusluga_id is not null
			)
		order by 
			case when evnvizitpl.EvnVizitPL_setDT is not null and vac_usluga.EvnUsluga_setDT is not null then 
				case when evnvizitpl.EvnVizitPL_setDT > vac_usluga.EvnUsluga_setDT then evnvizitpl.EvnVizitPL_setDT else vac_usluga.EvnUsluga_setDT end
				else coalesce(evnvizitpl.EvnVizitPL_setDT, vac_usluga.EvnUsluga_setDT) 
		   		end
					desc 
		limit 1
	) last_epl_vizit on true

	left join lateral ( --карта профилактических прививок: старый модуль
		select
			ac.vacJournalAccount_id,
			ac.vacJournalAccount_DateVac as vac_date
		from vac.vac_JournalAccount ac 
		inner join vac_vaccine v  on v.Vaccine_id = ac.vaccine_id
		where ac.Person_id = pc.Person_id
			--and ac.vacJournalAccount_DateVac between p.CovidVac_begdate and p.setdate
		order by ac.vacJournalAccount_DateVac desc
		limit 1
	) last_vac_journal_old on true 
	left join lateral ( --карта профилактический прививок: новый модуль
		select
			ev.EvnVaccination_setDT as vac_date
		from v_EvnVaccination ev 
		inner join vc.v_VaccinationData as vd on ev.EvnVaccination_id = vd.EvnVaccination_id
			and cast(ev.EvnVaccination_setDT as date) between p.CovidVac_begdate and p.setdate
		inner join vc.v_Vaccination as v on v.Vaccination_id = vd.Vaccination_id
		inner join vc.vaccinationtype as vt on vt.vaccinationtype_id = v.vaccinationtype_id
		where vt.VaccinationClass_id = 30
			and ev.Person_id = pc.Person_id
		order by ev.EvnVaccination_setDT desc
		limit 1
	) last_vac_journal_new on true 
	
	--НКВИ
	left join lateral ( --закрытые КВС
		select
			es.evnsection_id,
			eps.EvnPS_DisDT
		from v_EvnPS eps
		inner join v_evnsection es on es.evnsection_pid = eps.evnps_id
			and eps.EvnPS_DisDT between p.begdate and p.setdate + interval '1 day' - interval '1 second'
			and eps.EvnPS_DisDT is not null
			and eps.Person_id = pc.Person_id
		left join nkvi_diag diag on diag.diag_id = eps.Diag_id
		left join lateral (
			select 
				nkvi_diag.Diag_id
			from v_EvnDiagPS diag_ps 
			inner join nkvi_diag on nkvi_diag.diag_id = diag_ps.Diag_id	
			where diag_ps.EvnDiagPS_pid = es.evnsection_id
				and diag_ps.DiagSetClass_id in (2, 3)
			limit 1
		) diag_s on true --сопутствующий диагноз/осложнение основного
		where (diag.diag_id is not null or diag_s.diag_id is not null)
		order by eps.EvnPS_DisDT desc
		limit 1
	) last_nkvi_eps on true 
	
	left join lateral ( --закрытые ТАП
		select
			epl_close.EvnPl_id,
			epl_close.EvnPL_disDT
		from v_evnpl epl_close 
		inner join v_evnvizitpl evpl on evpl.evnvizitpl_pid = epl_close.evnpl_id
			and epl_close.person_id = pc.person_id 
			and epl_close.evnpl_isfinish = 2
			and epl_close.evnpl_disdt between p.begdate and p.setdate + interval '1 day' - interval '1 second'
		left join nkvi_diag diag  on diag.diag_id = epl_close.Diag_id
		left join lateral (
			select
				nkvi_diag.Diag_id
			from v_EvnDiagPLSop diag_pl
			inner join nkvi_diag on nkvi_diag.diag_id = diag_pl.Diag_id	
			where diag_pl.EvnDiagPLSop_pid = evpl.EvnVizitPL_id
			limit 1
		) diag_s on true --сопутствующий диагноз
		where (diag.diag_id is not null or diag_s.diag_id is not null)
			and epl_close.person_id = pc.person_id 
		order by epl_close.EvnPL_disDT desc
		limit 1
	) last_nkvi_epl on true

	where  
		(last_epl_vizit.vac_date < p.begdate or last_epl_vizit.EvnVizitPL_id is null)
		and (coalesce(last_vac_journal_new.vac_date, last_vac_journal_old.vac_date) between p.CovidVac_begdate and p.begdate 
			or coalesce(last_vac_journal_new.vac_date, last_vac_journal_old.vac_date) is null)
)

	select
		row_number() over(order by coalesce(person.person_surname || ' ', '') || coalesce(person.person_firname || ' ', '') || coalesce(person.person_secname, '')) as g1_row,
		coalesce(person.person_surname || ' ', '') || coalesce(person.person_firname || ' ', '') || coalesce(person.person_secname, '') as g2_person_fio,
		sex.sex_name as g3_sex,
		to_char(person.person_birthday, 'dd.mm.yyyy') as g4_person_birthday,
		uaddr.Address_Address as g5_uaddr,
		paddr.Address_Address as g6_paddr,
		person.Person_Phone as g7_phone,

		to_char(case when npc.vac_date1 is not null and npc.vac_date2 is not null then 
			case when npc.vac_date1 > npc.vac_date2 then npc.vac_date1 else npc.vac_date2 end
			else coalesce(npc.vac_date1, npc.vac_date2)
			end, 'dd.mm.yyyy') as g8_last_vac_date,

		case when coalesce(npc.last_nkvi1, npc.last_nkvi2) is not null then 'Да' else 'Нет' end g9_nkvi,
		to_char(case when npc.last_nkvi1_date is not null and npc.last_nkvi2_date is not null then 
			case when npc.last_nkvi1_date > npc.last_nkvi2_date then npc.last_nkvi1_date else npc.last_nkvi2_date end 
			else coalesce(npc.last_nkvi1_date, npc.last_nkvi2_date)
			end, 'dd.mm.yyyy')
			as g10_nkvi_date,

		npc.LpuRegionType_Name as g11_lpuregiontype_name,
		npc.LpuRegion_Name as g12_lpuregion_name,
		person.Person_Snils as g13_snils

	from not_vac_personcard npc
	inner join params p on true
	inner join v_personstate person on npc.person_id = person.person_id
	left join v_sex sex on sex.sex_id = person.sex_id
	left join v_address uaddr on uaddr.address_id = person.uaddress_id 
	left join v_address paddr on paddr.address_id = person.PAddress_id

	where 
		(dbo.AgeYMD(person.Person_BirthDay, p.setdate, 1) >= p.beg_person_age or p.beg_person_age is null)
		and (dbo.AgeYMD(person.Person_BirthDay, p.setdate, 1) <= p.end_person_age or p.end_person_age is null)
