/*********************************************************
*         SCRIPT TO CREATE DATA TABLES
*           Mike M	6/16/2013
*           Dan C Aug 2016 revised
**********************************************************/


------------------------------------------------------------------------------
-- create CODE_LOOKUP table with clustered PK on TABLE_CD, COLUMN_CD, CODE_CD 
------------------------------------------------------------------------------

create table code_lookup ( 
    table_cd            varchar(100) not null,
    column_cd           varchar(100) not null,
    code_cd             varchar(50) not null,
    name_char           varchar(650) null,
    lookup_blob         longvarchar null,
    upload_date       	timestamp null,
    update_date         timestamp null,
    download_date     	timestamp null,
    import_date         timestamp null,
    sourcesystem_cd   	varchar(50) null,
    upload_id         	int null,
	constraint code_lookup_pk primary key(table_cd, column_cd, code_cd)
	)
;

/* add index on name_char field */
create index cl_idx_name_char on code_lookup(name_char)
;
create index cl_idx_uploadid on code_lookup(upload_id)
;


--------------------------------------------------------------------
-- create concept_dimension table with clustered pk on concept_path 
--------------------------------------------------------------------

create table concept_dimension ( 
	concept_path   		varchar(700) not null,
	concept_cd     		varchar(50) null,
	name_char      		varchar(2000) null,
	concept_blob   		longvarchar null,
	update_date    		timestamp null,
	download_date  		timestamp null,
	import_date    		timestamp null,
	sourcesystem_cd		varchar(50) null,
    upload_id			int null,
    constraint concept_dimension_pk primary key(concept_path)
	)
;
create index cd_idx_uploadid on concept_dimension(upload_id)
;


---------------------------------------------------------------------------------------------------------------------------------------
-- create observation_fact table with nonclustered pk on encounter_num, concept_cd, provider_id, start_date, modifier_cd, instance_num 
---------------------------------------------------------------------------------------------------------------------------------------

create table observation_fact ( 
	encounter_num  		int not null,
	patient_num    		int not null,
	concept_cd     		varchar(50) not null,
	provider_id    		varchar(50) not null,
	start_date     		timestamp not null,
	modifier_cd    		varchar(100) default '@' not null,
	instance_num		int default 1 not null,
	valtype_cd     		varchar(50) null,
	tval_char      		varchar(255) null,
	nval_num       		decimal(18,5) null,
	valueflag_cd   		varchar(50) null,
	quantity_num   		decimal(18,5) null,
	units_cd       		varchar(50) null,
	end_date       		timestamp null,
	location_cd    		varchar(50) null,
	observation_blob	longvarchar null,
	confidence_num 		decimal(18,5) null,
	update_date    		timestamp null,
	download_date  		timestamp null,
	import_date    		timestamp null,
	sourcesystem_cd		varchar(50) null, 
    upload_id         	int null,     --
    text_search_index   integer null,  -- should be serial
    sub_encounter       varchar(200) null,
    constraint observation_fact_pk primary key  (patient_num, concept_cd,  modifier_cd, start_date, encounter_num, instance_num, provider_id)
	)
;

/* add index on concept_cd */
create  index of_idx_clusteredconcept on observation_fact
(
	concept_cd 
)
;

/* add additional indexes on observation_fact fields */
create index of_idx_start_date on observation_fact(start_date, patient_num)
;
create index of_idx_modifier on observation_fact(modifier_cd)
;
create index of_idx_encounter_patient on observation_fact(encounter_num, patient_num, instance_num)
;
create index of_idx_uploadid on observation_fact(upload_id)
;
create index of_idx_sourcesystem_cd on observation_fact(sourcesystem_cd)
;
create unique index of_text_search_unique on observation_fact(text_search_index)
;




-------------------------------------------------------------------
-- create patient_dimension table with clustered pk on patient_num 
-------------------------------------------------------------------

create table patient_dimension ( 
	patient_num      	int not null,
	vital_status_cd  	varchar(50) null,
	birth_date       	timestamp null,
	death_date       	timestamp null,
	sex_cd           	varchar(50) null,
	age_in_years_num	int null,
	language_cd      	varchar(50) null,
	race_cd          	varchar(50) null,
	marital_status_cd	varchar(50) null,
	religion_cd      	varchar(50) null,
	zip_cd           	varchar(10) null,
	statecityzip_path	varchar(700) null,
	income_cd			varchar(50) null,
	patient_blob     	longvarchar null,
	update_date      	timestamp null,
	download_date    	timestamp null,
	import_date      	timestamp null,
	sourcesystem_cd  	varchar(50) null,
    upload_id         	int null, 
    constraint patient_dimension_pk primary key(patient_num)
	)
;

/* add indexes on additional patient_dimension fields */
create  index pd_idx_dates on patient_dimension(patient_num, vital_status_cd, birth_date, death_date)
;
create  index pd_idx_statecityzip on patient_dimension (statecityzip_path, patient_num)
;
create index pa_idx_uploadid on patient_dimension(upload_id)
;


-----------------------------------------------------------------------------------
-- create provider_dimension table with clustered pk on provider_path, provider_id 
-----------------------------------------------------------------------------------

create table provider_dimension ( 
	provider_id    		varchar(50) not null,
	provider_path  		varchar(700) not null,
	name_char      		varchar(850) null,
	provider_blob  		longvarchar null,
	update_date    		timestamp null,
	download_date  		timestamp null,
	import_date    		timestamp null,
	sourcesystem_cd		varchar(50) null ,
    upload_id         	int null,
    constraint provider_dimension_pk primary key(provider_path, provider_id)
	)
;

/* add index on provider_id, name_char */
create index pd_idx_name_char on provider_dimension(provider_id, name_char)
;
create index pd_idx_uploadid on provider_dimension(upload_id)
;


-------------------------------------------------------------------
-- create visit_dimension table with clustered pk on encounter_num 
-------------------------------------------------------------------

create table visit_dimension ( 
	encounter_num  		int not null,
	patient_num    		int not null,
	active_status_cd	varchar(50) null,
	start_date     		timestamp null,
	end_date       		timestamp null,
	inout_cd       		varchar(50) null,
	location_cd    		varchar(50) null,
    location_path  		varchar(900) null,
	length_of_stay		int null,
	visit_blob     		longvarchar null,
	update_date    		timestamp null,
	download_date  		timestamp null,
	import_date    		timestamp null,
	sourcesystem_cd		varchar(50) null ,
    upload_id         	int null, 
    constraint visit_dimension_pk primary key(encounter_num, patient_num)
	)
;

/* add indexes on addtional visit_dimension fields */
create  index vd_idx_dates on visit_dimension(encounter_num, start_date, end_date)
;
create  index vd_idx_uploadid on visit_dimension(upload_id)
;


------------------------------------------------------------
-- create modifier_dimension table with pk on modifier_path 
------------------------------------------------------------

create table modifier_dimension ( 
	modifier_path   	varchar(700) not null,
	modifier_cd     	varchar(50) null,
	name_char      		varchar(2000) null,
	modifier_blob   	longvarchar null,
	update_date    		timestamp null,
	download_date  		timestamp null,
	import_date    		timestamp null,
	sourcesystem_cd		varchar(50) null,
    upload_id			int null,
    constraint modifier_dimension_pk primary key(modifier_path)
	)
;
create index md_idx_uploadid on modifier_dimension(upload_id)
;


create table job (
	pset integer not null, 
	label varchar, 
	concepts varchar not null, 
	modifers varchar, 
	date_lo varchar, 
	date_hi varchar, 
	name varchar not null
);

create table variable (
	id integer, 
	item_key varchar, 
	concept_path varchar, 
	name_char varchar, 
	name varchar, 
	short_name varchar, 
	section varchar, 
	redundant boolean, 
	check (redundant in (0, 1))
);


create view data_summary as
select v.name_char,
  count(distinct patient_num) pat_qty, count(*) fact_qty
from observation_fact f
join concept_dimension cd
on cd.concept_cd = f.concept_cd
join variable v
on cd.concept_path like (v.concept_path || '%')
group by v.concept_path, v.name_char
;
