DROP VIEW IF EXISTS AKUZ.V_BRONCHIAL_ACTHMA_REGISTER;
CREATE VIEW AKUZ.V_BRONCHIAL_ACTHMA_REGISTER
AS
with cte
as (
	select coalesce(ac.Patient, acc.Patient) as Patient, null::uuid as DispanserisationInit, bac.MedicalRecordID as CheckList
	from AKUZ.T_BRONCHIAL_ACTHMA_CHECKLIST bac
		join AKUZ.T_DISPANSERISATION_DYNAMIC_OBSERVATION ddo on ddo.MedicalRecordID = bac.MedicalRecordID
		left join AKUZ.T_AMBULANCE_CARD_RECORD acr on acr.MedicalRecordID = ddo.DispanserisationInit
		left join AKUZ.T_AMBULANCE_CARD ac on ac.AmbulanceCardID = acr.AmbulanceCard
		left join AKUZ.T_MEDICAL_RECOMENDATION mrec on mrec.MedicalRecomendationID = bac.BronchialActhmaRecomendation
		left join AKUZ.T_MEDICAL_EPISODE me on me.MedicalEpisodeID = mrec.MedicalEpisode
		left join AKUZ.T_AMBULANCE_CARD acc on acc.AmbulanceCardID = me.AmbulanceCard
	where bac.Status = 3 and not exists(select * 
						from AKUZ.T_BRONCHIAL_ACTHMA_CHECKLIST bacI
							join AKUZ.T_DISPANSERISATION_DYNAMIC_OBSERVATION ddoI on ddoI.MedicalRecordID = bacI.MedicalRecordID
							left join AKUZ.T_AMBULANCE_CARD_RECORD acrI on acrI.MedicalRecordID = ddoI.DispanserisationInit
							left join AKUZ.T_AMBULANCE_CARD acI on acI.AmbulanceCardID = acrI.AmbulanceCard
							left join AKUZ.T_MEDICAL_RECOMENDATION mrecI on mrecI.MedicalRecomendationID = bacI.BronchialActhmaRecomendation
							left join AKUZ.T_MEDICAL_EPISODE meI on meI.MedicalEpisodeID = mrecI.MedicalEpisode
							left join AKUZ.T_AMBULANCE_CARD accI on accI.AmbulanceCardID = meI.AmbulanceCard
							where bacI.Status > 3 and coalesce(ac.Patient, acc.Patient) = coalesce(acI.Patient, accI.Patient)
							and bacI.MedicalRecordID <> bac.MedicalRecordID and ddoI.RecordDate >= ddo.RecordDate
					)
	union select ac.Patient, di.MedicalRecordID as DispanserisationInit, null::uuid as CheckList
	from AKUZ.T_DISPANSERISATION_INIT di
		join AKUZ.T_AMBULANCE_CARD_RECORD acr on acr.MedicalRecordID = di.MedicalRecordID
		join AKUZ.T_AMBULANCE_CARD ac on ac.AmbulanceCardID = acr.AmbulanceCard
		left join AKUZ.T_DISPANSERISATION_END de on de.DispanserisationInit = di.MedicalRecordID
	where di.Mkb like 'J45%' and de.MedicalRecordID is null
		and not exists(select * 
						from AKUZ.T_DISPANSERISATION_INIT diI
							join AKUZ.T_AMBULANCE_CARD_RECORD acrI on acrI.MedicalRecordID = diI.MedicalRecordID
							join AKUZ.T_AMBULANCE_CARD acI on acI.AmbulanceCardID = acrI.AmbulanceCard
							left join AKUZ.T_DISPANSERISATION_END deI on deI.DispanserisationInit = diI.MedicalRecordID
						where diI.Mkb like 'J45%' and deI.MedicalRecordID is null
							and acI.Patient = ac.Patient and diI.MedicalRecordID <> di.MedicalRecordID and Coalesce(diI.DateDiagnosis, acrI.PostingDate) >  Coalesce(di.DateDiagnosis, acr.PostingDate)
								)
)
select e.Patient as BronchialActhmaRegisterID, e.Patient, max(e.DispanserisationInit) as DispanserisationInit, max(e.CheckList) as BronchialActhmaChecklist
from cte e
where e.Patient is not null  
    and not exists(select * 
					from AKUZ.T_DEATH_INFO di
					join AKUZ.T_AMBULANCE_CARD acD on acD.AmbulanceCardID = di.AmbulanceCard
					where acD.Patient = e.Patient)
	and not exists(select * 
					from AKUZ.T_MEDICAL_DEATH_CERTIFICATE mdc
					join AKUZ.T_AMBULANCE_CARD acD on acD.AmbulanceCardID = mdc.AmbulanceCard
					where acD.Patient = e.Patient)
group by e.Patient;


