/* Need to replace  
@vocabulary_database_schema
@cdm_database_schema
@target_database_schema
*/

/* patient_dxenct */

	IF OBJECT_ID('tempdb.dbo.#icdcm_enct', 'U') IS NOT NULL DROP TABLE #icdcm_enct; 
	CREATE TABLE #icdcm_enct (
	  person_id bigint NOT NULL,
	  visit_occurrence_id bigint NOT NULL,
	  enc_date date NOT NULL,
	  enct_order_desc int NOT NULL
	)
	;

	--get encounters with an ICD dx code
	INSERT INTO #icdcm_enct (person_id, visit_occurrence_id, enc_date, enct_order_desc)
	SELECT distinct person_id, visit_occurrence_id, enc_date, ROW_NUMBER() over (partition by person_id order by enc_date desc) enct_order_desc
	FROM (
		--observation
		select distinct o.person_id, visit_occurrence_id, observation_date enc_date
		from @cdm_database_schema.observation o inner join @vocabulary_database_schema.concept c
			on c.concept_id = o.observation_SOURCE_concept_id 
			and vocabulary_id in ('ICD9CM','ICD10CM')
		group by o.person_id, visit_occurrence_id, observation_date

	UNION

		--condition_occurrence
		select distinct co.person_id, visit_occurrence_id, condition_start_date enc_date
		from @cdm_database_schema.condition_occurrence co inner join @vocabulary_database_schema.concept c
			on c.concept_id = co.condition_SOURCE_concept_id 
			and vocabulary_id in ('ICD9CM','ICD10CM')
		group by co.person_id, visit_occurrence_id, condition_start_date

	UNION

		--meausrement
		select distinct m.person_id, visit_occurrence_id, measurement_date enc_date
		from @cdm_database_schema.measurement m inner join @vocabulary_database_schema.concept c
			on c.concept_id = m.measurement_SOURCE_concept_id 
			and vocabulary_id in ('ICD9CM','ICD10CM')
		group by m.person_id, visit_occurrence_id, measurement_date

	UNION

		--procedure_occurence
		select distinct po.person_id, visit_occurrence_id, procedure_date enc_date
		from @cdm_database_schema.procedure_occurrence po inner join @vocabulary_database_schema.concept c
			on c.concept_id = po.procedure_SOURCE_concept_id 
			and vocabulary_id in ('ICD9CM','ICD10CM')
		group by po.person_id, visit_occurrence_id, procedure_date

		) E
	;

	IF OBJECT_ID('tempdb.dbo.#patient_dxenct', 'U') IS NOT NULL DROP TABLE #patient_dxenct; 
	CREATE TABLE #patient_dxenct (
	  person_id bigint NOT NULL,
	  patient_dxenct int NOT NULL,
	  dx_start_year int NULL,
	  dx_end_year int NULL,
	  recent_start_date date NULL
	)
	;

	--get patient listing with encounter counts and encounter attributes
	INSERT INTO #patient_dxenct (person_id, patient_dxenct, dx_start_year, dx_end_year, recent_start_date)
	SELECT p.person_id, isnull(count(distinct e.visit_occurrence_id),0) patient_dxenct, year(min(e.enc_date)) dx_start_year, year(max(e.enc_date)) dx_end_year, m.enc_date recent_start_date
	FROM @cdm_database_schema.person p 
		left join #icdcm_enct e on e.person_id = p.person_id
		left join (select person_id, visit_occurrence_id, enc_date from #icdcm_enct x where enct_order_desc = 1) m on m.person_id = p.person_id
	GROUP BY p.person_id, m.visit_occurrence_id, m.enc_date
	;


/* Feature counts */
	IF OBJECT_ID('tempdb.dbo.#codesets', 'U') IS NOT NULL DROP TABLE #codesets; 
	CREATE TABLE #Codesets (
	  codeset_id int NOT NULL,
	  codeset_name varchar(25) NOT NULL,
	  concept_id bigint NOT NULL
	)
	;

	--T2DM_DX codes
	INSERT INTO #Codesets (codeset_id, codeset_name, concept_id)
	SELECT 0 as codeset_id, 'T2DM_DX', c.concept_id FROM (select distinct I.concept_id FROM
	( 
	  select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (4140466,43531588,45769888,45763582,37018912,43531578,43531559,43531566,43531653,43531577,43531562,37016163,45769894,45757474,37016768,4221495,43531616,43531564,443733,43530689,4226121,36712686,36712687,43531608,43531597,443732,45757280,45769906,4177050,4223463,43530690,45769890,37018728,45772019,45769889,37016349,45770880,45757392,45771064,45757447,45757446,45757445,45757444,45757363,45772060,36714116,45769875,4130162,45771072,45770830,45769905,45757435,43531651,45770881,4222415,45769828,376065,45757450,45770883,45757255,37016354,43530656,45769836,443729,45757278,37017432,4063043,43531010,4129519,43530685,45770831,45757499,443731,45770928,45757075,45769872,45769835,36712670,46274058,4142579,45770832,45773064,201826,4230254,4304377,4321756,4196141,4099217,201530,4151282,4099216,4198296,4193704,4200875,4099651,45766052,40482801,45757277,45757449,443734)and invalid_reason is null
	UNION  select c.concept_id
	  from @vocabulary_database_schema.CONCEPT c
	  join @vocabulary_database_schema.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
	  and ca.ancestor_concept_id in (4140466,43531588,45769888,45763582,37018912,43531578,43531559,43531566,43531653,43531577,43531562,37016163,45769894,45757474,37016768,4221495,43531616,43531564,443733,43530689,4226121,36712686,36712687,43531608,43531597,443732,45757280,45769906,4177050,4223463,43530690,45769890,37018728,45772019,45769889,37016349,45770880,45757392,45771064,45757447,45757446,45757445,45757444,45757363,45772060,36714116,45769875,4130162,45771072,45770830,45769905,45757435,43531651,45770881,4222415,45769828,376065,45757450,45770883,45757255,37016354,43530656,45769836,443729,45757278,37017432,4063043,43531010,4129519,43530685,45770831,45757499,443731,45770928,45757075,45769872,45769835,36712670,46274058,4142579,45770832,45773064,201826,4230254,4304377,4321756,4196141,4099217,201530,4151282,4099216,4198296,4193704,4200875,4099651,45766052,40482801,45757277,45757449,443734)and invalid_reason is null
	  and c.invalid_reason is null

	) I
	) C;

	--T1DM_DX codes
	INSERT INTO #Codesets (codeset_id, codeset_name, concept_id)
	SELECT 1 as codeset_id, 'T1DM_DX', c.concept_id FROM (select distinct I.concept_id FROM
	( 
	  select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (36715571,45769891,37016767,45763585,4225656,45773688,45773576,45769901,45771075,45769902,45769903,45769837,45769832,45757674,435216,377821,37016353,45769904,43531565,37017429,37016348,45757432,443592,201531,45757393,45771067,45769876,4228112,45757362,4047906,4102018,36717215,45757073,439770,4224254,4143857,45757535,37016179,43530660,37016180,4225055,4224709,45769829,45769830,45763583,45769834,36713094,318712,37018566,4222687,4222553,37017431,4063042,43531008,43531009,45763584,45757604,200687,45757266,4227210,45771533,45773567,45769833,46269764,4143689,45769873,201254,4099215,40484648,4152858,4099214,443412,45766051,45757507,45769892,45770902,45757074)
	UNION  select c.concept_id
	  from @vocabulary_database_schema.CONCEPT c
	  join @vocabulary_database_schema.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
	  and ca.ancestor_concept_id in (36715571,45769891,37016767,45763585,4225656,45773688,45773576,45769901,45771075,45769902,45769903,45769837,45769832,45757674,435216,377821,37016353,45769904,43531565,37017429,37016348,45757432,443592,201531,45757393,45771067,45769876,4228112,45757362,4047906,4102018,36717215,45757073,439770,4224254,4143857,45757535,37016179,43530660,37016180,4225055,4224709,45769829,45769830,45763583,45769834,36713094,318712,37018566,4222687,4222553,37017431,4063042,43531008,43531009,45763584,45757604,200687,45757266,4227210,45771533,45773567,45769833,46269764,4143689,45769873,201254,4099215,40484648,4152858,4099214,443412,45766051,45757507,45769892,45770902,45757074)
	  and c.invalid_reason is null

	) I
	) C;

	--T1DM_MED
	INSERT INTO #Codesets (codeset_id, codeset_name, concept_id)
	SELECT 2 as codeset_id, 'T1DM_MED', c.concept_id FROM (select distinct I.concept_id FROM
	( 
	  select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (35198096,1531601,1567198,35602717,1516976,1502905,1544838,1588986,46221581,1550023,1513876,42899447,19013951,1590165,1596977,1586346,1586369)
	UNION  select c.concept_id
	  from @vocabulary_database_schema.CONCEPT c
	  join @vocabulary_database_schema.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
	  and ca.ancestor_concept_id in (35198096,1531601,1567198,35602717,1516976,1502905,1544838,1588986,46221581,1550023,1513876,42899447,19013951,1590165,1596977,1586346,1586369)
	  and c.invalid_reason is null

	) I
	) C;

	--T2DM_MED
	INSERT INTO #Codesets (codeset_id, codeset_name, concept_id)
	--includes metformin
	SELECT 3 as codeset_id, 'T2DM_MED', c.concept_id FROM (select distinct I.concept_id FROM
	( 
	  select concept_id from @vocabulary_database_schema.CONCEPT where concept_id in (1529331,1530014,44816332,43013884,43526465,1594973,44785829,45774435,45774751,1583722,1597756,1560171,1559684,40239216,40170911,44506754,1503297,1510202,1502826,1525215,1516766,1547504,40166035,1580747,1502809,1502855,1515249,596554,19059796,19059796,19001441,1517998)
	UNION  select c.concept_id
	  from @vocabulary_database_schema.CONCEPT c
	  join @vocabulary_database_schema.CONCEPT_ANCESTOR ca on c.concept_id = ca.descendant_concept_id
	  and ca.ancestor_concept_id in (1529331,1530014,44816332,43013884,43526465,1594973,44785829,45774435,45774751,1583722,1597756,1560171,1559684,40239216,40170911,44506754,1503297,1510202,1502826,1525215,1516766,1547504,40166035,1580747,1502809,1502855,1515249,596554,19059796,19059796,19001441,1517998)
	  and c.invalid_reason is null

	) I
	) C;

	CREATE NONCLUSTERED INDEX ix_codesets_concept_id ON #Codesets (concept_id);



	IF OBJECT_ID('tempdb.dbo.#features', 'U') IS NOT NULL DROP TABLE #features; 
	SELECT 
		p.person_id
		,p.gender_concept_id sex
		, p.race_concept_id race
		, p.ethnicity_concept_id ethnicity
		, p.year_of_birth year_birth
		, case when T2DM.person_id is null then 0 else 1 end Meets_ICD9_10_Filter
		, NULL Meets_DataFloor --need to calculate outside OMOP (Has at least 3 diagnosis codes for any ICD9/10 diagnosis With 30 days or more between first and last date of diagnosis code With at least one diagnosis code after ‘1/1/2005’ and At least one clinical note)
		, case when patient_dxenct.person_id is null then 0 else patient_dxenct.patient_dxenct end patient_dxenct
		, case when T2dm.person_id is null then 0 else T2DM.T2DM_DX end cod_dx_t2dm
		, case when T1dm.person_id is null then 0 else T1DM.T1DM_DX end cod_dx_t1dm
		, case when diabetes_med.person_id is null then 0 else diabetes_med.diabetes_med end cod_med_antidiabetics
	INTO #features
	FROM 

		--patient set
		(SELECT *
		FROM @cdm_database_schema.person
		) P LEFT OUTER JOIN

		(SELECT *
		FROM #patient_dxenct
		) patient_dxenct ON P.person_id = patient_dxenct.person_id LEFT OUTER JOIN

		--T2DM DX
		(SELECT person_id, count(distinct visit_occurrence_id) T2DM_DX, 'Y' Meets_ICD9_10_Filter
		FROM @cdm_database_schema.CONDITION_OCCURRENCE co
		WHERE co.condition_concept_id in (SELECT concept_id from  #Codesets where codeset_name = 'T2DM_DX')  
		GROUP BY person_id
		) T2DM ON P.person_id = T2DM.person_id LEFT OUTER JOIN

		--T1DM DX
		(SELECT person_id, count(distinct visit_occurrence_id) T1DM_DX
		FROM @cdm_database_schema.CONDITION_OCCURRENCE co
		WHERE co.condition_concept_id in (SELECT concept_id from  #Codesets where codeset_name = 'T1DM_DX')  
		GROUP BY person_id
		) T1DM ON P.person_id = T1DM.person_id  LEFT OUTER JOIN

		--diabetes_med
		(SELECT person_id, count(distinct visit_occurrence_id)  diabetes_med
		FROM @cdm_database_schema.DRUG_EXPOSURE de
		WHERE drug_concept_id in (SELECT concept_id from  #Codesets where codeset_name In ('T2DM_MED','T1DM_MED'))
		GROUP BY person_id
		) diabetes_med ON P.person_id = diabetes_med.person_id
	;


select *
from #features



/*
person_id	sex	race	ethnicity	year_of_birth	Meets_ICD9_10_Filter	Meets_DataFloor	patient_dxenct	cod_dx_t2dm	cod_dx_t1dm	cod_med_antidiabetics
39745		8507	8552	0		1940		1			NULL		1350		52		6		55
*/

/*
NOTE: Data Floor flag needs to be added to file requires access to clinical notes to determine if subject has at least one clinical note

Data Floor:

•	Subject Has at least 3 diagnosis codes for ANY ICD9/10 diagnosis
	•	With 30 days or more between first and last date of diagnosis code
	•	With at least one diagnosis code after ‘1/1/2005’
•	At least one clinical note
*/