--Optum Panther DDL

CREATE TABLE _version
(
   version_id    integer   NOT NULL,
   version_date  date      NOT NULL
);

CREATE TABLE allergy
(
   ptid          varchar(11)    NOT NULL,
   onset_date    date,
   allergentype  varchar(18),
   allergendesc  varchar(60),
   drug_class    varchar(150),
   ndc           varchar(11),
   ndc_source    varchar(7),
   sourceid      varchar(5)
);

CREATE TABLE care_area
(
   ptid           char(11)      NOT NULL,
   encid          varchar(17)   NOT NULL,
   carearea       varchar(60),
   carearea_date  date,
   carearea_time  varchar(8),
   sourceid       varchar(5)
);


CREATE TABLE cost_factor
(
   tos                 varchar(25),
   tos_desc            varchar(50),
   cdm_std_price_year  smallint,
   cumulative_factor   numeric(15,14)
);


CREATE TABLE diagnosis
(
   ptid                 char(11)      NOT NULL,
   encid                varchar(17),
   diag_date            date          NOT NULL,
   diag_time            varchar(8)    NOT NULL,
   diagnosis_cd         varchar(15),
   diagnosis_cd_type    varchar(10),
   diagnosis_status     varchar(24),
   poa                  char(1),
   admitting_diagnosis  char(1),
   discharge_diagnosis  char(1),
   primary_diagnosis    char(1),
   problem_list         char(1)       NOT NULL,
   sourceid             varchar(5)
);


CREATE TABLE encounter
(
   ptid                     char(11)      NOT NULL,
   visitid                  bigint,
   encid                    varchar(17)   NOT NULL,
   interaction_type         varchar(43)   NOT NULL,
   interaction_date         date          NOT NULL,
   interaction_time         varchar(8)    NOT NULL,
   academic_community_flag  varchar(3)    NOT NULL,
   sourceid                 varchar(5)
);


CREATE TABLE encounter_provider
(
   encid          varchar(17)   NOT NULL,
   provid         varchar(8)    NOT NULL,
   provider_role  varchar(15),
   sourceid       varchar(5)
);


CREATE TABLE immunizations
(
   ptid               char(11)       NOT NULL,
   immunization_date  date,
   immunization_desc  varchar(156),
   mapped_name        varchar(100),
   ndc                varchar(11),
   ndc_source         varchar(7),
   pt_reported        char(1),
   sourceid           varchar(5),
   _gpi               varchar(14)
);


CREATE TABLE insurance
(
   ptid            char(11)      NOT NULL,
   encid           varchar(17)   NOT NULL,
   insurance_date  date          NOT NULL,
   insurance_time  varchar(8)    NOT NULL,
   ins_type        varchar(16),
   sourceid        varchar(5)
);


CREATE TABLE labs
(
   ptid                 char(11)       NOT NULL,
   encid                varchar(17),
   test_name            varchar(94)    NOT NULL,
   test_type            varchar(17)    NOT NULL,
   order_date           date,
   order_time           varchar(8),
   collected_date       date,
   collected_time       varchar(8),
   result_date          date,
   result_time          varchar(8),
   test_result          varchar(250),
   relative_indicator   varchar(2),
   result_unit          varchar(100),
   normal_range         varchar(300),
   evaluated_for_range  char(1)        NOT NULL,
   value_within_range   char(1)        NOT NULL,
   sourceid             varchar(5)
);


CREATE TABLE medication_administrations
(
   ptid                char(11)       NOT NULL,
   encid               varchar(17),
   orderid             varchar(16),
   drug_name           varchar(120),
   ndc                 varchar(11),
   ndc_source          varchar(7),
   order_date          date           NOT NULL,
   order_time          varchar(8)     NOT NULL,
   admin_date          date,
   admin_time          varchar(8),
   provid              varchar(8),
   route               varchar(25),
   quantity_of_dose    varchar(65),
   strength            varchar(90),
   strength_unit       varchar(40),
   dosage_form         varchar(110),
   dose_frequency      varchar(30),
   generic_desc        varchar(60),
   drug_class          varchar(150),
   discontinue_reason  varchar(46),
   sourceid            varchar(5),
   _gpi                varchar(14)
);


CREATE TABLE microbiology
(
   ptid                      char(11)       NOT NULL,
   encid                     varchar(17),
   order_date                date,
   order_time                varchar(8),
   collect_date              date,
   collect_time              varchar(8),
   receive_date              date,
   receive_time              varchar(8),
   result_date               date           NOT NULL,
   result_time               varchar(8)     NOT NULL,
   result_status             varchar(11),
   specimen_source           varchar(30),
   organism                  varchar(300),
   mapped_organism_found     varchar(75),
   mapped_organism_excluded  varchar(75),
   culture_growth            varchar(15),
   culture_value             varchar(20),
   culture_unit              varchar(10),
   antibiotic                varchar(50),
   mapped_antibiotic         varchar(35),
   sensitivity               varchar(255),
   sourceid                  varchar(5)
);


CREATE TABLE nlp_biomarkers
(
   ptid              char(11)       NOT NULL,
   encid             varchar(17),
   note_date         date           NOT NULL,
   biomarker         varchar(25)    NOT NULL,
   variation_detail  varchar(100),
   biomarker_status  varchar(25)    NOT NULL,
   sourceid          varchar(5)
);


CREATE TABLE nlp_custom
(
   ptid                 char(11)      NOT NULL,
   encid                varchar(17),
   note_date            date          NOT NULL,
   nlp_term             varchar(27),
   nlp_term_attribute1  varchar(20),
   nlp_term_attribute2  varchar(74),
   nlp_term_qualifier   varchar(41),
   note_section         varchar(41)
);


CREATE TABLE nlp_drug_rationale
(
   ptid                     char(11)       NOT NULL,
   encid                    varchar(17),
   note_date                date           NOT NULL,
   note_section             varchar(60),
   drug_name                varchar(500),
   drug_action              varchar(19),
   drug_action_preposition  varchar(4),
   reason_general           varchar(12),
   sentiment                varchar(60),
   sentiment_who            varchar(24),
   sourceid                 varchar(5)
);


CREATE TABLE nlp_measurement
(
   ptid                   char(11)       NOT NULL,
   encid                  varchar(17),
   note_date              date           NOT NULL,
   measurement_type       varchar(300),
   measurement_value      varchar(150),
   measurement_detail     varchar(300),
   note_section           varchar(60),
   measurement_year       smallint,
   measurement_monthyear  varchar(7),
   measurement_date       date,
   sourceid               varchar(5)
);


CREATE TABLE nlp_sds
(
   ptid                  varchar(11)    NOT NULL,
   encid                 varchar(17),
   note_date             date           NOT NULL,
   sds_term              varchar(350),
   sds_location          varchar(320),
   sds_attribute         varchar(270),
   sds_sentiment         varchar(60),
   occurrence_year       smallint,
   occurrence_monthyear  varchar(7),
   occurrence_date       date,
   note_section          varchar(60),
   sds_concept           varchar(20),
   sds_timing            varchar(50),
   sds_severity          varchar(50),
   sds_extent            varchar(50),
   sds_duration          varchar(30),
   sds_change            varchar(60),
   sds_quality           varchar(50),
   sourceid              varchar(5)
);


CREATE TABLE nlp_sds_family
(
   ptid               char(11)       NOT NULL,
   encid              varchar(17),
   note_date          date           NOT NULL,
   sds_term           varchar(350),
   sds_location       varchar(320),
   sds_family_member  varchar(500),
   sds_sentiment      varchar(60),
   note_section       varchar(60),
   sourceid           varchar(5)
);


CREATE TABLE nlp_targeted
(
   ptid                      varchar(11),
   note_date                 date,
   concept_name              varchar(31),
   concept_value             varchar(14),
   concept_value_attribute1  varchar(15),
   concept_value_attribute2  varchar(9),
   concept_value_attribute3  varchar(38),
   concept_value_attribute4  varchar(21),
   concept_value_attribute5  varchar(14),
   concept_date_value        varchar(10),
   concept_date_type         varchar(10),
   sourceid                  varchar(5)
);


CREATE TABLE observations
(
   ptid                 char(11)       NOT NULL,
   encid                varchar(17),
   obs_type             varchar(100)   NOT NULL,
   nlp                  char(1)        NOT NULL,
   obs_date             date           NOT NULL,
   obs_time             varchar(8)     NOT NULL,
   obs_result           varchar(200),
   obs_unit             varchar(20),
   evaluated_for_range  char(1)        NOT NULL,
   value_within_range   char(1)        NOT NULL,
   result_date          date,
   sourceid             varchar(5)
);


CREATE TABLE patient
(
   ptid                 char(11)      NOT NULL,
   birth_yr             varchar(16),
   gender               varchar(7),
   race                 varchar(16),
   ethnicity            varchar(12),
   region               varchar(13),
   division             varchar(30),
   avg_hh_income        float8,
   pct_college_educ     float8,
   deceased_indicator   varchar(1),
   date_of_death        char(6),
   provid_pcp           varchar(8),
   idn_indicator        char(1)       NOT NULL,
   first_month_active   varchar(6)    NOT NULL,
   last_month_active    varchar(6)    NOT NULL,
   notes_eligible       char(1)       NOT NULL,
   has_notes            char(1)       NOT NULL,
   sourceid             varchar(60)   NOT NULL,
   source_data_through  varchar(6)    NOT NULL,
   integrated           varchar(1)
);


CREATE TABLE patient_reported_medications
(
   ptid              char(11)       NOT NULL,
   reported_date     date           NOT NULL,
   drug_name         varchar(120),
   ndc               varchar(11),
   ndc_source        varchar(7),
   provid            varchar(8),
   route             varchar(25),
   quantity_of_dose  varchar(65),
   strength          varchar(90),
   strength_unit     varchar(40),
   dosage_form       varchar(110),
   dose_frequency    varchar(30),
   generic_desc      varchar(60),
   drug_class        varchar(150),
   sourceid          varchar(5),
   _gpi              varchar(14)
);


CREATE TABLE prescriptions_written
(
   ptid                char(11)       NOT NULL,
   rxdate              date           NOT NULL,
   rxtime              varchar(8)     NOT NULL,
   drug_name           varchar(120),
   ndc                 varchar(11),
   ndc_source          varchar(7),
   provid              varchar(8),
   route               varchar(25),
   quantity_of_dose    varchar(65),
   strength            varchar(90),
   strength_unit       varchar(40),
   dosage_form         varchar(110),
   daily_dose          float8,
   dose_frequency      varchar(30),
   quantity_per_fill   varchar(160),
   num_refills         integer,
   days_supply         float8,
   generic_desc        varchar(60),
   drug_class          varchar(150),
   discontinue_reason  varchar(46),
   sourceid            varchar(5),
   _gpi                varchar(14)
);


CREATE TABLE procedure
(
   ptid            char(11)       NOT NULL,
   encid           varchar(17),
   proc_date       date           NOT NULL,
   proc_time       varchar(8)     NOT NULL,
   proc_code       varchar(40),
   proc_desc       varchar(255),
   proc_code_type  varchar(12),
   provid_perform  varchar(8),
   provid_order    varchar(8),
   betos_code      varchar(3),
   betos_desc      varchar(60),
   sourceid        varchar(5)
);


CREATE TABLE provider
(
   provid         varchar(8)    NOT NULL,
   specialty      varchar(36),
   prim_spec_ind  char(1),
   sourceid       varchar(5)
);


CREATE TABLE visit
(
   ptid                   char(11)       NOT NULL,
   visitid                bigint         NOT NULL,
   visit_type             varchar(40)    NOT NULL,
   visit_start_date       date           NOT NULL,
   visit_start_time       varchar(8),
   visit_end_date         date,
   visit_end_time         varchar(8),
   discharge_disposition  varchar(100),
   admission_source       varchar(80),
   drg                    varchar(3),
   sourceid               varchar(5)
);


