SELECT --Pers.Person_id, eps.EvnPS_id,epl.evnpl_id
	Person.Person_Guid AS local_guid,
	--person
	ps.Person_id,
	ps.Person_Snils AS snils ,
	rtrim(ltrim(ps.Person_SurName)) as lastname,
	rtrim(ltrim(ps.Person_FirName)) as firstname,
	rtrim(ltrim(ps.Person_SecName)) as patronymic,
	ps.Person_BirthDay AS birth_date,
	ps.Sex_id AS gender,
	Country.FRMR_id citizenship_country,
	--person_persdocs
	CASE WHEN doctype.DocumentType_Code in (3,14) THEN concat(ps.Person_id, '_doc') ELSE NULL END AS document_id,
	CASE WHEN doctype.DocumentType_Code in (3,14) THEN doctype.Frmr_id ELSE NULL END AS persdoc_type,
	CASE WHEN doctype.DocumentType_Code in (3,14) THEN nullif(rtrim(ltrim(ps.Document_Ser)), '') ELSE NULL END as pd_series,
	CASE WHEN doctype.DocumentType_Code in (3,14) THEN nullif(rtrim(ltrim(ps.Document_Num)), '') ELSE NULL END as pd_number,
	CASE WHEN doctype.DocumentType_Code in (3,14) THEN doc.Document_begDate ELSE NULL END AS date_begin,
	--person_contacts
	CASE WHEN nullif(rtrim(ltrim(ps.Person_Phone)), '') IS NOT NULL THEN 'mobile' ELSE null END AS contact_type,
	'+7'  ||  nullif(rtrim(ltrim(ps.Person_Phone)), '') AS contact,
	--person_addr
	adr.addr_type,
	adr.region,
	adr.area_guid,
	adr.street_guid,
	adr.area_name,
	adr.street_name,
	adr.house,
	adr.flat,
	--person_insurance
	polistype.PolisType_CodeF008 AS policy_type,
	case when polistype.PolisType_CodeF008 is null then null else nullif(rtrim(ltrim(ps.Polis_Ser)), '') end as policy_series,
	case when polistype.PolisType_CodeF008 is null then null else nullif(rtrim(ltrim(ps.Polis_Num)), '') end as policy_number,
	case when polistype.PolisType_CodeF008 is null then null else OrgSMO.Orgsmo_f002smocod end AS smo_cod,
	--reg
	pastok.PassportToken_tid AS mo,
	dbo.tzGetDate() AS include_date,
	evnCv.Evn_id local_id,
	evnCv.ambulatory_treatment,
	evnCv.Diag_Code disease,
	case when evnCv.Diag_CGroup = 1 then evnCv.DiagSop_Code else null end disease_sop,
	evnCv.diag_exreason disease_exreason,
	evnCv.Evn_setDT disease_date,
	evnCv.Evn_setDT hospitalization_date,
	--symptoms.symptomsDT symptom_date,
	case 
		when PersonPregnancy.PersonPregnancy_dispDate is not null then 
			case 
				 
				when ceiling(DATE_PART('day', PersonPregnancy.PersonPregnancy_dispDate - evnCv.Evn_setDT) / 7.0) + coalesce(PersonPregnancy_Period,0) < 13 then 1 
				when ceiling(DATE_PART('day', PersonPregnancy.PersonPregnancy_dispDate - evnCv.Evn_setDT) / 7.0) + coalesce(PersonPregnancy_Period,0) < 28 then 2 
					 
				else 3 
			end
		else null 
	end pregnancy_trimester, 
	case when PersonPregnancy.PersonPregnancy_dispDate is not null then 1 when ps.Sex_id = 2 then 0 else null end pregnancy,
	case when flu.Person_id is not null then 1 else 0 end vaccination_information_flu,
	case when air.Person_id is not null then 1 else 0 end vaccination_information_air,
	case  
		when evnCv.Evn_disDT is null then null 
		when evnCv.ResultDesease in (1,2,4,6,7) then evnCv.ResultDesease
		else null
	end disease_outcome,
	case 
		when evnCv.Evn_disDT is null then null
		when evnCv.ResultDesease = 1  then COALESCE(Pers.Person_deadDT, evnCv.Evn_disDT)  
		when evnCv.ResultDesease in (2,4,6,7)  then evnCv.Evn_disDT 
		else null 
	end disease_outcome_date,
	case when evnCv.ResultDesease = 1 then (select DeathCause_Name from dbo.v_DeathCause dc where dc.DeathCause_id = DeathSvid.DeathCause_id) else null end death_reason
FROM (select Person_id, Person_IsDead, Person_deadDT from v_PersonState   where Person_id = $1 ) Pers
 left join lateral  (
	SELECT  
		evnCv.Evn_id, evnCv.Evn_setDT, evnCv.Evn_disDT, evnCv.Lpu_id, evnCv.Diag_id, evnCv.ResultDesease, evnCv.ambulatory_treatment, evnCv.diag_exreason,
		case when evnCv.Diag_CGroup >= DopDiag.Diag_CGroup then DopDiag.Diag_CGroup else evnCv.Diag_CGroup end Diag_CGroup,
		case when evnCv.Diag_CGroup >= DopDiag.Diag_CGroup then DopDiag.Diag_Code else evnCv.Diag_Code end Diag_Code,
		case when evnCv.Diag_CGroup >= DopDiag.Diag_CGroup then evnCv.Diag_Code else DopDiag.Diag_Code end DiagSop_Code
	FROM (
		SELECT 
			eps.EvnPS_id Evn_id, eps.EvnPS_setDT Evn_setDT, eps.EvnPS_disDT Evn_disDT, eps.Lpu_id, eps.Diag_id, 0 ambulatory_treatment, 
			de.Diag_SCode diag_exreason,
			 d.Diag_SCode  Diag_Code,
			 
	case when d.Diag_SCode in ('U07.1','U07.2') then 1 when d.Diag_SCode in ('Z03.8','Z11.5','Z20.8','Z22.8') then 2 else 3 end
  Diag_CGroup,
			case 
				when flt.LeaveType_Code in (101, 201, 301) then 2
				when flt.LeaveType_Code in (102, 202) then 4
				when flt.LeaveType_Code in (103, 203, 305, 306, 307) then 6
				when flt.LeaveType_Code in (107, 110, 207, 302) then 7
				when flt.LeaveType_Code in (105, 106, 205, 206, 313, 405, 406, 411) then 1 
				else null 
			end ResultDesease,
			(
				SELECT   es.EvnSection_id 
				FROM dbo.v_EvnSection es  
				JOIN v_Evn Evn   ON Evn.Evn_id = es.EvnSection_id and Evn.Evn_pid = eps.EvnPS_id
				order by case when es.EvnSection_IsPriem = 2 then 1 else 0 end, Evn.Evn_setDT desc, Evn.Evn_insDT desc
				 LIMIT 1 
			) EvnSub_id 
		FROM dbo.v_EvnPS eps   
		INNER JOIN dbo.v_Diag d   ON d.Diag_id = eps.Diag_id 
		LEFT JOIN dbo.v_Diag de   ON de.Diag_id = eps.Diag_eid 
		LEFT JOIN dbo.LeaveType lt on lt.LeaveType_id  = eps.LeaveType_id
		LEFT JOIN fed.LeaveType flt on flt.LeaveType_id = lt.LeaveType_fedid
		WHERE eps.Person_id = Pers.Person_id
		  AND  d.Diag_SCode  IN  ('Z03.8','Z11.5','Z20.8','Z22.8','U07.1','U07.2','J12.0','J12.1','J12.2','J12.3','J12.8','J12.9',
'J13', 'J14', 'J15.0', 'J15.1', 'J15.2', 'J15.3', 'J15.4', 'J15.5', 'J15.6', 'J15.7', 'J15.8', 'J15.9', 'J16.0', 'J16.8', 'J17.0', 
'J17.1','J17.2','J17.3', 'J17.8', 'J18.0', 'J18.1', 'J18.2', 'J18.8', 'J18.9')
 
		UNION ALL		  
		SELECT  
			epl.EvnPL_id, epl.EvnPL_setDT, epl.EvnPL_disDT, epl.Lpu_id, epl.Diag_id, 1 ambulatory_treatment, null,  
			 d.Diag_SCode  Diag_Code, 
			 
	case when d.Diag_SCode in ('U07.1','U07.2') then 1 when d.Diag_SCode in ('Z03.8','Z11.5','Z20.8','Z22.8') then 2 else 3 end
  Diag_CGroup,
			case 
				when flt.LeaveType_Code in (101, 201, 301) then 2
				when flt.LeaveType_Code in (102, 202) then 4
				when flt.LeaveType_Code in (103, 203, 305, 306, 307) then 6
				when flt.LeaveType_Code in (107, 110, 207, 302) then 7
				when flt.LeaveType_Code in (105, 106, 205, 206, 313, 405, 406, 411) then 1 
				else null 
			end ResultDesease,
			(
				SELECT   es.EvnVizitPL_id  
				FROM dbo.v_EvnVizitPL es  
				JOIN v_Evn Evn   ON Evn.Evn_id = es.EvnVizitPL_id and Evn.Evn_pid = epl.EvnPL_id
				order by Evn.Evn_setDT desc, Evn.Evn_insDT desc 
				 LIMIT 1 
			) EvnSub_id
		FROM dbo.v_EvnPL epl     
		INNER JOIN dbo.v_Diag d  ON d.Diag_id = epl.Diag_id 
		LEFT JOIN fed.LeaveType flt on flt.LeaveType_id = epl.LeaveType_fedid 
		WHERE epl.Person_id = Pers.Person_id
		AND  d.Diag_SCode  IN  ('Z03.8','Z11.5','Z20.8','Z22.8','U07.1','U07.2','J12.0','J12.1','J12.2','J12.3','J12.8','J12.9',
'J13', 'J14', 'J15.0', 'J15.1', 'J15.2', 'J15.3', 'J15.4', 'J15.5', 'J15.6', 'J15.7', 'J15.8', 'J15.9', 'J16.0', 'J16.8', 'J17.0', 
'J17.1','J17.2','J17.3', 'J17.8', 'J18.0', 'J18.1', 'J18.2', 'J18.8', 'J18.9')
 
	) evnCv 		
	 left join lateral  (
		SELECT  
			d.Diag_SCode Diag_Code,  
	case when d.Diag_SCode in ('U07.1','U07.2') then 1 when d.Diag_SCode in ('Z03.8','Z11.5','Z20.8','Z22.8') then 2 else 3 end
  Diag_CGroup
		FROM dbo.v_Evn Evn    
		JOIN dbo.v_EvnDiag EvnDiag    ON Evn.Evn_id = EvnDiag.EvnDiag_id 
		JOIN dbo.v_Diag d   ON d.Diag_id = EvnDiag.Diag_id
		WHERE evnCv.EvnSub_id = Evn.Evn_pid AND  d.Diag_SCode  IN  ('Z03.8','Z11.5','Z20.8','Z22.8','U07.1','U07.2','J12.0','J12.1','J12.2','J12.3','J12.8','J12.9',
'J13', 'J14', 'J15.0', 'J15.1', 'J15.2', 'J15.3', 'J15.4', 'J15.5', 'J15.6', 'J15.7', 'J15.8', 'J15.9', 'J16.0', 'J16.8', 'J17.0', 
'J17.1','J17.2','J17.3', 'J17.8', 'J18.0', 'J18.1', 'J18.2', 'J18.8', 'J18.9')
 
		  AND evnCv.Diag_id <> EvnDiag.Diag_id
		ORDER BY  
	case when d.Diag_SCode in ('U07.1','U07.2') then 1 when d.Diag_SCode in ('Z03.8','Z11.5','Z20.8','Z22.8') then 2 else 3 end
 , EvnDiag.EvnDiag_setDT 
		 LIMIT 1 
	) DopDiag  ON TRUE 
	ORDER BY case when evnCv.ResultDesease in (4,6) then 1 else 0 end, case when evnCv.Diag_CGroup >= DopDiag.Diag_CGroup then DopDiag.Diag_CGroup else evnCv.Diag_CGroup end, evnCv.Evn_setDT desc
	 LIMIT 1 
) AS evnCv  ON TRUE 
INNER JOIN dbo.v_Person Person  on Pers.Person_id = Person.Person_id
INNER JOIN dbo.v_PersonState ps   ON ps.Person_id = Pers.Person_id
LEFT JOIN v_KLCountry Country    ON Country.KLCountry_id = coalesce(ps.KLCountry_id, 643) 
LEFT JOIN dbo.v_Document doc   ON doc.Document_id = ps.Document_id
LEFT JOIN dbo.v_DocumentType doctype   ON doctype.DocumentType_id = doc.DocumentType_id
LEFT JOIN dbo.v_Polis polis   ON polis.Polis_id = ps.Polis_id
LEFT JOIN dbo.v_PolisType polistype   ON polistype.PolisType_id = polis.PolisType_id
LEFT JOIN dbo.v_OrgSMO OrgSMO   ON OrgSMO.OrgSMO_id = polis.OrgSmo_id
LEFT JOIN fed.v_PassportToken pastok   ON pastok.Lpu_id = evnCv.Lpu_id
 left join lateral  (
	select z.* 
	from (
		select  
			case when ps.UAddress_id = Address.Address_id then 'reg' else 'live' end addr_type,
			case 
				when Address.KLRgn_id is not null then Address.KLRgn_id 
				when a1.KLArea_id is not null then cast(substring(a1.KLAdr_Code, 1, 2) as int)
				when a2.KLArea_id is not null then cast(substring(a2.KLAdr_Code, 1, 2) as int)
			end region,
			case when a1.KLArea_AOGUID is not null then a1.KLArea_AOGUID else a2.KLArea_AOGUID end area_guid,
			st.KLStreet_AOGUID street_guid,
			case when a1.KLArea_id is not null then concat(socr1.KLSocr_Nick, ' ', a1.KLArea_Name) else concat(socr2.KLSocr_Nick, ' ', a2.KLArea_Name) end area_name,
			concat(socr3.KLSocr_Nick, ' ', st.KLStreet_Name) street_name,
			address.Address_House house,
			address.Address_Flat flat
		from v_Address Address  
		left join v_KLArea a1   on address.KLTown_id = a1.KLArea_id 
		left join v_KLArea a2   on address.KLCity_id = a2.KLArea_id 
		left join v_KLStreet st   on address.KLStreet_id = st.KLStreet_id 
		left join v_KLSocr socr1   on a1.KLSocr_id = socr1.KLSocr_id 
		left join v_KLSocr socr2   on a2.KLSocr_id = socr2.KLSocr_id 
		left join v_KLSocr socr3   on st.KLSocr_id = socr3.KLSocr_id 
		where ps.UAddress_id = Address.Address_id or ps.PAddress_id = Address.Address_id
	) z
	where z.area_guid is not null or z.street_guid is not null 
		or nullif(rtrim(ltrim(z.area_name)), '') is not null 
		or nullif(rtrim(ltrim(z.street_name)), '') is not null
		or nullif(rtrim(ltrim(z.house)), '') is not null 
		or nullif(rtrim(ltrim(z.flat)), '') is not null
) as adr  ON TRUE 
/*
 left join lateral  (
	SELECT   
		ro.RepositoryObserv_ClinicalSymptomsDT symptomsDT
	FROM RepositoryObserv ro 
	WHERE ro.RepositoryObserv_ClinicalSymptomsDT is not null and ro.Person_id = Pers.Person_id
		and ro.RepositoryObserv_setDT <= coalesce(evnCv.Evn_disDT, ro.RepositoryObserv_setDT)
		and ro.RepositoryObserv_setDT >= dateadd(month, -3, evnCv.Evn_setDT)
	 LIMIT 1 
) symptoms  ON TRUE 
*/
LEFT JOIN (
	select 
		Person_id, PersonPregnancy_dispDate, PersonPregnancy_Period, PregnancyResult_id,
		row_number() over(partition by Person_id order by PersonPregnancy_dispDate desc) rn
	from dbo.v_PersonPregnancy  
) PersonPregnancy on PersonPregnancy.Person_id = Pers.Person_id and PregnancyResult_id IS NULL  
LEFT JOIN (
	SELECT JournalAccount.Person_id FROM vac.v_JournalAccount JournalAccount  
	INNER JOIN vac.v_Inoculation_all   ON JournalAccount.vacJournalAccount_id = v_Inoculation_all.vacJournalAccount_id
	WHERE v_Inoculation_all.VaccineType_id = 100 and JournalAccount.vacJournalAccount_DateVac >=  $2::date + interval '1 year' 
	group by JournalAccount.Person_id
) flu on flu.Person_id = Pers.Person_id
LEFT JOIN (
	SELECT JournalAccount.Person_id FROM vac.v_JournalAccount JournalAccount  
	INNER JOIN vac.v_Inoculation_all   ON JournalAccount.vacJournalAccount_id = v_Inoculation_all.vacJournalAccount_id
	WHERE v_Inoculation_all.VaccineType_id = 124 and JournalAccount.vacJournalAccount_DateVac >=  $3::date + interval '5 year' 
	group by JournalAccount.Person_id
) air on air.Person_id = Pers.Person_id
LEFT JOIN v_DeathSvid DeathSvid  on DeathSvid.Person_id = Pers.Person_id
