<commit_before>BEGIN;

-- Ideally we'd have the foreign keys described here, but foreign keys to
-- system tables aren't allowed

DROP TABLE IF EXISTS trisano.core_tables;

CREATE TABLE trisano.core_tables (
    table_name NAME PRIMARY KEY,
    table_description TEXT,
    target_table TEXT NOT NULL, -- The actual table name this should point at
    order_num INTEGER NOT NULL,
    make_category BOOLEAN DEFAULT true,
    formbuilder_prefix TEXT
);

DROP TABLE IF EXISTS trisano.core_columns;

CREATE TABLE trisano.core_columns (
    target_column NAME,
    target_table TEXT, -- The actual table this column belongs in
    PRIMARY KEY (target_column, target_table),
    column_name TEXT,
    column_description TEXT,
    make_category_column BOOLEAN DEFAULT true
);

INSERT INTO trisano.core_tables (make_category, table_name, table_description, target_table, order_num, formbuilder_prefix) VALUES
(true,  'dw_morbidity_events_view',                  'Morbidity Events',                  'trisano.dw_morbidity_events_view',                  1,  'Morbidity'),
(true,  'dw_morbidity_clinicians_view',              'Morbidity Clinicians',              'trisano.dw_morbidity_clinicians_view',              2,  NULL),
(true,  'dw_morbidity_diagnostic_facilities_view',   'Morbidity Diagnostic Facilities',   'trisano.dw_morbidity_diagnostic_facilities_view',   2,  NULL),
(true,  'dw_morbidity_email_addresses_view',         'Morbidity Email Addresses',         'trisano.dw_morbidity_email_addresses_view',         2,  NULL),
(true,  'dw_morbidity_hospitals_view',               'Morbidity Hospitals',               'trisano.dw_morbidity_hospitals_view',               2,  NULL),
(true,  'dw_morbidity_lab_results_view',             'Morbidity Lab Results',             'trisano.dw_morbidity_lab_results_view',             2,  NULL),
(true,  'dw_morbidity_patients_races_view',          'Morbidity Patients Races',          'trisano.dw_morbidity_patients_races_view',          2,  NULL),
(true,  'dw_morbidity_secondary_jurisdictions_view', 'Morbidity Secondary Jurisdictions', 'trisano.dw_morbidity_secondary_jurisdictions_view', 2,  NULL),
(true,  'dw_morbidity_telephones_view',              'Morbidity Telephone Numbers',       'trisano.dw_morbidity_telephones_view',              2,  NULL),
(true,  'dw_morbidity_treatments_events_view',       'Morbidity Treatments Events',       'trisano.dw_morbidity_treatments_events_view',       2,  NULL),
(true,  'dw_contact_events_view',                    'Contact Events',                    'trisano.dw_contact_events_view',                    3,  'Contact'),
(true,  'dw_contact_clinicians_view',                'Contact Clinicians',                'trisano.dw_contact_clinicians_view',                4,  NULL),
(true,  'dw_contact_diagnostic_facilities_view',     'Contact Diagnostic Facilities',     'trisano.dw_contact_diagnostic_facilities_view',     4,  NULL),
(true,  'dw_contact_email_addresses_view',           'Contact Email Addresses',           'trisano.dw_contact_email_addresses_view',           4,  NULL),
(true,  'dw_contact_hospitals_view',                 'Contact Hospitals',                 'trisano.dw_contact_hospitals_view',                 4,  NULL),
(true,  'dw_contact_lab_results_view',               'Contact Lab Results',               'trisano.dw_contact_lab_results_view',               4,  NULL),
(true,  'dw_contact_patients_races_view',            'Contact Patients Races',            'trisano.dw_contact_patients_races_view',            4,  NULL),
(true,  'dw_contact_secondary_jurisdictions_view',   'Contact Secondary Jurisdictions',   'trisano.dw_contact_secondary_jurisdictions_view',   4,  NULL),
(true,  'dw_contact_telephones_view',                'Contact Telephone Numbers',         'trisano.dw_contact_telephones_view',                4,  NULL),
(true,  'dw_contact_treatments_events_view',         'Contact Treatments Events',         'trisano.dw_contact_treatments_events_view',         4,  NULL),
(true,  'dw_encounter_events_view',                  'Encounters',                        'trisano.dw_encounter_events_view',                  5,  'Encounter'),
(true,  'dw_encounter_email_addresses_view',         'Encounter Email Addresses',         'trisano.dw_encounter_email_addresses_view',         6,  NULL),
(true,  'dw_encounters_lab_results_view',            'Encounters Lab Results',            'trisano.dw_encounters_lab_results_view',            6,  NULL),
(true,  'dw_encounter_patients_races_view',          'Encounter Patients Races',          'trisano.dw_encounter_patients_races_view',          6,  NULL),
(true,  'dw_encounter_telephones_view',              'Encounter Telephone Numbers',       'trisano.dw_encounter_telephones_view',              6,  NULL),
(true,  'dw_encounters_treatments_events_view',      'Encounters Treatments Events',      'trisano.dw_encounters_treatments_events_view',      6,  NULL),
(true,  'dw_place_events_view',                      'Place Events',                      'trisano.dw_place_events_view',                      7,  'Place'),
(true,  'avr_groups_view',                           'Disease Groups',                    'trisano.avr_groups_view',                           9,  NULL),
(false, 'avr_groups_diseases_view',                  'avr_groups_diseases_view',          'trisano.avr_groups_diseases_view',                  9,  NULL);

INSERT INTO trisano.core_columns (target_table, target_column, column_name, column_description, make_category_column) VALUES
('trisano.avr_groups_diseases_view',                  'avr_group_id',                       'avr_group_id',                       '',                          false),
('trisano.avr_groups_diseases_view',                  'created_at',                         'created_at',                         '',                          false),
('trisano.avr_groups_diseases_view',                  'disease_id',                         'disease_id',                         '',                          false),
('trisano.avr_groups_diseases_view',                  'updated_at',                         'updated_at',                         '',                          false),
('trisano.avr_groups_view',                           'created_at',                         'created_at',                         '',                          false),
('trisano.avr_groups_view',                           'id',                                 'id',                                 '',                          false),
('trisano.avr_groups_view',                           'name',                               'name',                               'Dis Grp Nm',                true),
('trisano.avr_groups_view',                           'updated_at',                         'updated_at',                         '',                          false),
('trisano.dw_contact_clinicians_view',                'dw_contact_events_id',               'dw_contact_events_id',               '',                          false),
('trisano.dw_contact_clinicians_view',                'first_name',                         'first_name',                         'CC Frst Nm',                true),
('trisano.dw_contact_clinicians_view',                'id',                                 'id',                                 '',                          false),
('trisano.dw_contact_clinicians_view',                'last_name',                          'last_name',                          'CC Lst Nm',                 true),
('trisano.dw_contact_clinicians_view',                'middle_name',                        'middle_name',                        'CC Mid Nm',                 true),
('trisano.dw_contact_clinicians_view',                'phones',                             'phones',                             'CC Phn Nbrs',               true),
('trisano.dw_contact_diagnostic_facilities_view',     'dw_contact_events_id',               'dw_contact_events_id',               '',                          false),
('trisano.dw_contact_diagnostic_facilities_view',     'dw_morbidity_events_id',             'dw_morbidity_events_id',             '',                          false),
('trisano.dw_contact_diagnostic_facilities_view',     'id',                                 'id',                                 '',                          false),
('trisano.dw_contact_diagnostic_facilities_view',     'name',                               'name',                               'CDF Nm',                    true),
('trisano.dw_contact_diagnostic_facilities_view',     'place_id',                           'place_id',                           '',                          false),
('trisano.dw_contact_diagnostic_facilities_view',     'place_type',                         'place_type',                         'CDF Typ',                   true),
('trisano.dw_contact_email_addresses_view',           'email_address',                      'email_address',                      'CP Email Addr',             true),
('trisano.dw_contact_email_addresses_view',           'entity_id',                          'entity_id',                          '',                          false),
('trisano.dw_contact_events_view',                    'active',                             'active',                             'CD Act',                    true),
('trisano.dw_contact_events_view',                    'actual_age_at_onset',                'actual_age_at_onset',                'CE Actl Age at Onst',       true),
('trisano.dw_contact_events_view',                    'actual_age_type',                    'actual_age_type',                    'CE Actl Age Typ',           true),
('trisano.dw_contact_events_view',                    'additional_risk_factors',            'additional_risk_factors',            'CE Addtl Risk Fctrs',       true),
('trisano.dw_contact_events_view',                    'age_in_years',                       'age_in_years',                       'CE Age in Yrs',             true),
('trisano.dw_contact_events_view',                    'event_onset_age_days',               'event_onset_age_days',               'CE Onst Age in Days',       true),
('trisano.dw_contact_events_view',                    'always_one',                         'always_one',                         '',                          false),
('trisano.dw_contact_events_view',                    'birth_date',                         'birth_date',                         'CP Brth Dt',                true),
('trisano.dw_contact_events_view',                    'birth_gender',                       'birth_gender',                       'CP Brth Gndr',              true),
('trisano.dw_contact_events_view',                    'cdc_code',                           'cdc_code',                           'CD CDC Cd',                 true),
('trisano.dw_contact_events_view',                    'city',                               'city',                               'CE City',                   true),
('trisano.dw_contact_events_view',                    'contact_lead_in',                    'contact_lead_in',                    'CD Cntct Lead-in',          true),
('trisano.dw_contact_events_view',                    'contact_type',                       'contact_type',                       'CE Cntct Typ',              true),
('trisano.dw_contact_events_view',                    'county',                             'county',                             'CE Cnty',                   true),
('trisano.dw_contact_events_view',                    'date_created',                       'date_created',                       '',                          false),
('trisano.dw_contact_events_view',                    'date_deleted',                       'date_deleted',                       '',                          false),
('trisano.dw_contact_events_view',                    'date_disease_diagnosed',             'date_disease_diagnosed',             'CE Dt Dis Diag',            true),
('trisano.dw_contact_events_view',                    'date_disease_onset',                 'date_disease_onset',                 'CE Dt Dis Onst',            true),
('trisano.dw_contact_events_view',                    'date_entered_into_system',           'date_entered_into_system',           'CE Event Date',             true),
('trisano.dw_contact_events_view',                    'date_investigation_completed',       'date_investigation_completed',       'CE Dt Inv Cmpl',            true),
('trisano.dw_contact_events_view',                    'date_investigation_started',         'date_investigation_started',         'CE Dt Inv Strtd',           true),
('trisano.dw_contact_events_view',                    'date_of_death',                      'date_of_death',                      'CP Dt of Death',            true),
('trisano.dw_contact_events_view',                    'date_updated',                       'date_updated',                       '',                          false),
('trisano.dw_contact_events_view',                    'day_care_association',               'day_care_association',               'CE Day Care Assoc',         true),
('trisano.dw_contact_events_view',                    'disease_event_died',                 'disease_event_died',                 'CE Died',                   true),
('trisano.dw_contact_events_view',                    'disease_event_hospitalized',         'disease_event_hospitalized',         'CE Hospitalized',           true),
('trisano.dw_contact_events_view',                    'disease_id',                         'disease_id',                         '',                          false),
('trisano.dw_contact_events_view',                    'disease_name',                       'disease_name',                       'CD Dis Nm',                 true),
('trisano.dw_contact_events_view',                    'disposition',                        'disposition',                        'CE Dsp',                    true),
('trisano.dw_contact_events_view',                    'dw_patients_id',                     'dw_patients_id',                     '',                          false),
('trisano.dw_contact_events_view',                    'entity_id',                          'entity_id',                          '',                          false),
('trisano.dw_contact_events_view',                    'estimated_age_at_onset',             'estimated_age_at_onset',             'CE Est Age at Onst',        true),
('trisano.dw_contact_events_view',                    'estimated_age_type',                 'estimated_age_type',                 'CE Est Age Typ',            true),
('trisano.dw_contact_events_view',                    'ethnicity',                          'ethnicity',                          'CP Ethncty',                true),
('trisano.dw_contact_events_view',                    'event_queue_id',                     'event_queue_id',                     '',                          false),
('trisano.dw_contact_events_view',                    'first_name',                         'first_name',                         'CP Frst Nm',                true),
('trisano.dw_contact_events_view',                    'first_name_soundex',                 'first_name_soundex',                 '',                          false),
('trisano.dw_contact_events_view',                    'food_handler',                       'food_handler',                       'CE Food Hdlr',              true),
('trisano.dw_contact_events_view',                    'group_living',                       'group_living',                       'CE Grp Lvng',               true),
('trisano.dw_contact_events_view',                    'healthcare_worker',                  'healthcare_worker',                  'CE Hlthcare Wrkr',          true),
('trisano.dw_contact_events_view',                    'ibis_updated_at',                    'ibis_updated_at',                    'CE IBIS Updtd',             true),
('trisano.dw_contact_events_view',                    'id',                                 'id',                                 '',                          false),
('trisano.dw_contact_events_view',                    'imported_from_code',                 'imported_from_code',                 '',                          false),
('trisano.dw_contact_events_view',                    'investigating_jurisdiction',         'investigating_jurisdiction',         'CE Inv Jur',                true),
('trisano.dw_contact_events_view',                    'investigating_jurisdiction_id',      'investigating_jurisdiction_id',      '',                          false),
('trisano.dw_contact_events_view',                    'investigator',                       'investigator',                       'CE Invstgtr',               true),
('trisano.dw_contact_events_view',                    'jurisdiction_of_residence',          'jurisdiction_of_residence',          'CE Jur of Res',             true),
('trisano.dw_contact_events_view',                    'jurisdiction_of_residence_id',       'jurisdiction_of_residence_id',       '',                          false),
('trisano.dw_contact_events_view',                    'last_name',                          'last_name',                          'CP Lst Nm',                 true),
('trisano.dw_contact_events_view',                    'last_name_soundex',                  'last_name_soundex',                  '',                          false),
('trisano.dw_contact_events_view',                    'middle_name',                        'middle_name',                        'CP Mid Nm',                 true),
('trisano.dw_contact_events_view',                    'occupation',                         'occupation',                         'CE Occ',                    true),
('trisano.dw_contact_events_view',                    'other_data_1',                       'other_data_1',                       'CE Othr Data 1',            true),
('trisano.dw_contact_events_view',                    'other_data_2',                       'other_data_2',                       'CE Othr Data 2',            true),
('trisano.dw_contact_events_view',                    'parent_id',                          'parent_id',                          '',                          false),
('trisano.dw_contact_events_view',                    'patient_entity_id',                  'patient_entity_id',                  '',                          false),
('trisano.dw_contact_events_view',                    'place_lead_in',                      'place_lead_in',                      'CD Plc Lead-in',            true),
('trisano.dw_contact_events_view',                    'postal_code',                        'postal_code',                        'CE Pstl Cd',                true),
('trisano.dw_contact_events_view',                    'pregnancy_due_date',                 'pregnancy_due_date',                 'CE Pregnancy Due Dt',       true),
('trisano.dw_contact_events_view',                    'pregnant',                           'pregnant',                           'CE Preg',                   true),
('trisano.dw_contact_events_view',                    'primary_language',                   'primary_language',                   'CP Prim Lng',               true),
('trisano.dw_contact_events_view',                    'public_health_status',               'public_health_status',               'CE PH Stat',                true),
('trisano.dw_contact_events_view',                    'record_number',                      'record_number',                      'CE Rec Nbr',                true),
('trisano.dw_contact_events_view',                    'review_completed_by_state_date',     'review_completed_by_state_date',     'CE Rvw Cmpl by St',         true),
('trisano.dw_contact_events_view',                    'risk_factor_details',                'risk_factor_details',                'CE Risk Fctr Dtls',         true),
('trisano.dw_contact_events_view',                    'sent_to_ibis',                       'sent_to_ibis',                       'CE Snt to IBIS',            true),
('trisano.dw_contact_events_view',                    'state',                              'state',                              'CE St',                     true),
('trisano.dw_contact_events_view',                    'street_name',                        'street_name',                        'CE Street Nm',              true),
('trisano.dw_contact_events_view',                    'street_number',                      'street_number',                      'CE Street Nbr',             true),
('trisano.dw_contact_events_view',                    'treatment_lead_in',                  'treatment_lead_in',                  'CD Trtmnt Lead-in',         true),
('trisano.dw_contact_events_view',                    'unit_number',                        'unit_number',                        'CE Unit Nbr',               true),
('trisano.dw_contact_hospitals_view',                 'admission_date',                     'admission_date',                     'CH Admsn Dt',               true),
('trisano.dw_contact_hospitals_view',                 'discharge_date',                     'discharge_date',                     'CH Dschg Dt',               true),
('trisano.dw_contact_hospitals_view',                 'dw_contact_events_id',               'dw_contact_events_id',               '',                          false),
('trisano.dw_contact_hospitals_view',                 'dw_morbidity_events_id',             'dw_morbidity_events_id',             '',                          false),
('trisano.dw_contact_hospitals_view',                 'hospital_name',                      'hospital_name',                      'CH Hosp Nm',                true),
('trisano.dw_contact_hospitals_view',                 'hospital_record_number',             'hospital_record_number',             'CH Hosp Rec Nbr',           true),
('trisano.dw_contact_hospitals_view',                 'id',                                 'id',                                 '',                          false),
('trisano.dw_contact_hospitals_view',                 'medical_record_number',              'medical_record_number',              'CH Med Rec Nbr',            true),
('trisano.dw_contact_lab_results_view',               'collection_date',                    'collection_date',                    'CLR Clct Dt',               true),
('trisano.dw_contact_lab_results_view',               'comment',                            'comment',                            'CLR Cmt',                   true),
('trisano.dw_contact_lab_results_view',               'dw_contact_events_id',               'dw_contact_events_id',               '',                          false),
('trisano.dw_contact_lab_results_view',               'dw_encounter_events_id',             'dw_encounter_events_id',             '',                          false),
('trisano.dw_contact_lab_results_view',               'dw_morbidity_events_id',             'dw_morbidity_events_id',             '',                          false),
('trisano.dw_contact_lab_results_view',               'hl7_message',                        'hl7_message',                        'CLR HL7 Msg',               true),
('trisano.dw_contact_lab_results_view',               'id',                                 'id',                                 '',                          false),
('trisano.dw_contact_lab_results_view',               'lab_test_date',                      'lab_test_date',                      'CLR Lab Tst Dt',            true),
('trisano.dw_contact_lab_results_view',               'lab_type',                           'lab_type',                           'CLR Lab Typ',               true),
('trisano.dw_contact_lab_results_view',               'loinc_code',                         'loinc_code',                         'CLR LOINC Cd',              true),
('trisano.dw_contact_lab_results_view',               'name',                               'name',                               'CLR Nm',                    true),
('trisano.dw_contact_lab_results_view',               'organism_name',                      'organism_name',                      'CLR Org Nm',                true),
('trisano.dw_contact_lab_results_view',               'reference_range',                    'reference_range',                    'CLR Ref Rng',               true),
('trisano.dw_contact_lab_results_view',               'result_value',                       'result_value',                       'CLR Rslt Val',              true),
--('trisano.dw_contact_lab_results_view',               'snomed_code',                        'snomed_code',                        'CLR Snmd Cd',               true),
--('trisano.dw_contact_lab_results_view',               'snomed_id',                          'snomed_id',                          'CLR Snmd Id',               true),
--('trisano.dw_contact_lab_results_view',               'snomed_name',                        'snomed_name',                        'CLR Snmd Nm',               true),
('trisano.dw_contact_lab_results_view',               'specimen_sent_to_state',             'specimen_sent_to_state',             'CLR Spec Snt to St',        true),
('trisano.dw_contact_lab_results_view',               'specimen_source',                    'specimen_source',                    'CLR Spec Src',              true),
('trisano.dw_contact_lab_results_view',               'staged_message_note',                'staged_message_note',                'CLR Stgd Msg Note',         true),
('trisano.dw_contact_lab_results_view',               'staged_message_state',               'staged_message_state',               'CLR Stgd Msg St',           true),
('trisano.dw_contact_lab_results_view',               'test_result',                        'test_result',                        'CLR Tst Rslt',              true),
('trisano.dw_contact_lab_results_view',               'test_status',                        'test_status',                        'CLR Tst Stat',              true),
('trisano.dw_contact_lab_results_view',               'test_type',                          'test_type',                          'CLR Tst Typ',               true),
('trisano.dw_contact_lab_results_view',               'units',                              'units',                              'CLR Units',                 true),
('trisano.dw_contact_patients_races_view',            'id',                                 'id',                                 '',                          false),
('trisano.dw_contact_patients_races_view',            'person_id',                          'person_id',                          '',                          false),
('trisano.dw_contact_patients_races_view',            'race',                               'race',                               'CP Race',                   true),
('trisano.dw_contact_secondary_jurisdictions_view',   'dw_contact_events_id',               'dw_contact_events_id',               '',                          false),
('trisano.dw_contact_secondary_jurisdictions_view',   'dw_morbidity_events_id',             'dw_morbidity_events_id',             '',                          false),
('trisano.dw_contact_secondary_jurisdictions_view',   'id',                                 'id',                                 '',                          false),
('trisano.dw_contact_secondary_jurisdictions_view',   'jurisdiction_id',                    'jurisdiction_id',                    '',                          false),
('trisano.dw_contact_secondary_jurisdictions_view',   'name',                               'name',                               'CSJ Nm',                    true),
('trisano.dw_contact_telephones_view',                'area_code',                          'area_code',                          'CP Area Cd',                true),
('trisano.dw_contact_telephones_view',                'country_code',                       'country_code',                       'CP Cntry Cd',               true),
('trisano.dw_contact_telephones_view',                'entity_id',                          'entity_id',                          '',                          false),
('trisano.dw_contact_telephones_view',                'extension',                          'extension',                          'CP Ext',                    true),
('trisano.dw_contact_telephones_view',                'phone_number',                       'phone_number',                       'CP Phn Nbr',                true),
('trisano.dw_contact_telephones_view',                'phone_type',                         'phone_type',                         'CP Phn Typ',                true),
('trisano.dw_contact_treatments_events_view',         'date_of_treatment',                  'date_of_treatment',                  'CT Dt of Trtmnt',           true),
('trisano.dw_contact_treatments_events_view',         'dw_contact_events_id',               'dw_contact_events_id',               '',                          false),
('trisano.dw_contact_treatments_events_view',         'dw_encounter_events_id',             'dw_encounter_events_id',             '',                          false),
('trisano.dw_contact_treatments_events_view',         'dw_morbidity_events_id',             'dw_morbidity_events_id',             '',                          false),
('trisano.dw_contact_treatments_events_view',         'id',                                 'id',                                 '',                          false),
('trisano.dw_contact_treatments_events_view',         'stop_treatment_date',                'stop_treatment_date',                'CT Dt Trtmnt Stppd',        true),
('trisano.dw_contact_treatments_events_view',         'treatment_given',                    'treatment_given',                    'CT Trtmnt Gvn',             true),
('trisano.dw_contact_treatments_events_view',         'treatment_id',                       'treatment_id',                       '',                          false),
('trisano.dw_contact_treatments_events_view',         'treatment_notes',                    'treatment_notes',                    'CT Trtmnt Notes',           true),
('trisano.dw_encounter_email_addresses_view',         'email_address',                      'email_address',                      'EP Email Addr',             true),
('trisano.dw_encounter_email_addresses_view',         'entity_id',                          'entity_id',                          '',                          false),
('trisano.dw_encounter_events_view',                  'birth_date',                         'birth_date',                         'EP Brth Dt',                true),
('trisano.dw_encounter_events_view',                  'birth_gender',                       'birth_gender',                       'EP Brth Gndr',              true),
('trisano.dw_encounter_events_view',                  'date_of_death',                      'date_of_death',                      'EP Dt of Death',            true),
('trisano.dw_encounter_events_view',                  'description',                        'description',                        'EE Desc',                   true),
('trisano.dw_encounter_events_view',                  'dw_patients_id',                     'dw_patients_id',                     '',                          false),
('trisano.dw_encounter_events_view',                  'dw_morbidity_events_id',             'dw_morbidity_events_id',             '',                          false),
('trisano.dw_encounter_events_view',                  'encounter_date',                     'encounter_date',                     'EE Dt',                     true),
('trisano.dw_encounter_events_view',                  'encounter_event_id',                 'encounter_event_id',                 '',                          false),
('trisano.dw_encounter_events_view',                  'ethnicity',                          'ethnicity',                          'EP Ethncty',                true),
('trisano.dw_encounter_events_view',                  'first_name',                         'first_name',                         'EP Frst Nm',                true),
('trisano.dw_encounter_events_view',                  'id',                                 'id',                                 '',                          false),
('trisano.dw_encounter_events_view',                  'investigator_id',                    'investigator_id',                    'EE Invstgtr',               true),
('trisano.dw_encounter_events_view',                  'last_name',                          'last_name',                          'EP Lst Nm',                 true),
('trisano.dw_encounter_events_view',                  'location',                           'location',                           'EE Loc',                    true),
('trisano.dw_encounter_events_view',                  'middle_name',                        'middle_name',                        'EP Mid Nm',                 true),
('trisano.dw_encounter_events_view',                  'patient_entity_id',                  'patient_entity_id',                  '',                          false),
('trisano.dw_encounter_events_view',                  'primary_language',                   'primary_language',                   'EP Prim Lng',               true),
('trisano.dw_encounter_patients_races_view',          'id',                                 'id',                                 '',                          false),
('trisano.dw_encounter_patients_races_view',          'person_id',                          'person_id',                          '',                          false),
('trisano.dw_encounter_patients_races_view',          'race',                               'race',                               'EP Race',                   true),
('trisano.dw_encounters_lab_results_view',            'collection_date',                    'collection_date',                    'ELR Clct Dt',               true),
('trisano.dw_encounters_lab_results_view',            'comment',                            'comment',                            'ELR Cmt',                   true),
('trisano.dw_encounters_lab_results_view',            'dw_contact_events_id',               'dw_contact_events_id',               '',                          false),
('trisano.dw_encounters_lab_results_view',            'dw_encounter_events_id',             'dw_encounter_events_id',             '',                          false),
('trisano.dw_encounters_lab_results_view',            'dw_morbidity_events_id',             'dw_morbidity_events_id',             '',                          false),
('trisano.dw_encounters_lab_results_view',            'hl7_message',                        'hl7_message',                        'ELR HL7 Msg',               true),
('trisano.dw_encounters_lab_results_view',            'id',                                 'id',                                 'EE Lab Rslts ',             true),
('trisano.dw_encounters_lab_results_view',            'lab_test_date',                      'lab_test_date',                      'ELR Lab Tst Dt',            true),
('trisano.dw_encounters_lab_results_view',            'lab_type',                           'lab_type',                           'ELR Lab Typ',               true),
('trisano.dw_encounters_lab_results_view',            'loinc_code',                         'loinc_code',                         'ELR LOINC Cd',              true),
('trisano.dw_encounters_lab_results_view',            'name',                               'name',                               'ELR Lab Nm',                true),
('trisano.dw_encounters_lab_results_view',            'organism_name',                      'organism_name',                      'ELR Org Nm',                true),
('trisano.dw_encounters_lab_results_view',            'reference_range',                    'reference_range',                    'ELR Ref Rng',               true),
('trisano.dw_encounters_lab_results_view',            'result_value',                       'result_value',                       'ELR Rslt Val',              true),
--('trisano.dw_encounters_lab_results_view',            'snomed_code',                        'snomed_code',                        'ELR Snmd Cd',               true),
--('trisano.dw_encounters_lab_results_view',            'snomed_id',                          'snomed_id',                          'ELR Snmd Id',               true),
--('trisano.dw_encounters_lab_results_view',            'snomed_name',                        'snomed_name',                        'ELR Snmd Nm',               true),
('trisano.dw_encounters_lab_results_view',            'specimen_sent_to_state',             'specimen_sent_to_state',             'ELR Spec Snt to St',        true),
('trisano.dw_encounters_lab_results_view',            'specimen_source',                    'specimen_source',                    'ELR Spec Src',              true),
('trisano.dw_encounters_lab_results_view',            'staged_message_note',                'staged_message_note',                'ELR Stgd Msg Note',         true),
('trisano.dw_encounters_lab_results_view',            'staged_message_state',               'staged_message_state',               'ELR Stgd Msg St',           true),
('trisano.dw_encounters_lab_results_view',            'test_result',                        'test_result',                        'ELR Tst Rslt',              true),
('trisano.dw_encounters_lab_results_view',            'test_status',                        'test_status',                        'ELR Tst Stat',              true),
('trisano.dw_encounters_lab_results_view',            'test_type',                          'test_type',                          'ELR Tst Typ',               true),
('trisano.dw_encounters_lab_results_view',            'units',                              'units',                              'ELR Units',                 true),
('trisano.dw_encounters_treatments_events_view',      'date_of_treatment',                  'date_of_treatment',                  'ET Dt of Trtmnt',           true),
('trisano.dw_encounters_treatments_events_view',      'dw_contact_events_id',               'dw_contact_events_id',               '',                          false),
('trisano.dw_encounters_treatments_events_view',      'dw_encounter_events_id',             'dw_encounter_events_id',             '',                          false),
('trisano.dw_encounters_treatments_events_view',      'dw_morbidity_events_id',             'dw_morbidity_events_id',             '',                          false),
('trisano.dw_encounters_treatments_events_view',      'id',                                 'id',                                 '',                          false),
('trisano.dw_encounters_treatments_events_view',      'stop_treatment_date',                'stop_treatment_date',                'ET Dt Trtmnt Stppd',        true),
('trisano.dw_encounters_treatments_events_view',      'treatment_given',                    'treatment_given',                    'ET Trtmnt Gvn',             true),
('trisano.dw_encounters_treatments_events_view',      'treatment_id',                       'treatment_id',                       '',                          false),
('trisano.dw_encounters_treatments_events_view',      'treatment_notes',                    'treatment_notes',                    'ET Trtmnt Notes',           true),
('trisano.dw_encounter_telephones_view',              'area_code',                          'area_code',                          'EP Area Cd',                true),
('trisano.dw_encounter_telephones_view',              'country_code',                       'country_code',                       'EP Cntry Cd',               true),
('trisano.dw_encounter_telephones_view',              'entity_id',                          'entity_id',                          '',                          false),
('trisano.dw_encounter_telephones_view',              'extension',                          'extension',                          'EP Ext',                    true),
('trisano.dw_encounter_telephones_view',              'phone_number',                       'phone_number',                       'EP Phn Nbr',                true),
('trisano.dw_encounter_telephones_view',              'phone_type',                         'phone_type',                         'EP Phn Typ',                true),
('trisano.dw_morbidity_clinicians_view',              'dw_morbidity_events_id',             'dw_morbidity_events_id',             '',                          false),
('trisano.dw_morbidity_clinicians_view',              'first_name',                         'first_name',                         'MC Frst Nm',                true),
('trisano.dw_morbidity_clinicians_view',              'id',                                 'id',                                 '',                          false),
('trisano.dw_morbidity_clinicians_view',              'last_name',                          'last_name',                          'MC L<NAME>',                 true),
('trisano.dw_morbidity_clinicians_view',              'middle_name',                        'middle_name',                        'MC Mid Nm',                 true),
('trisano.dw_morbidity_clinicians_view',              'phones',                             'phones',                             'MC Phn Nbrs',               true),
('trisano.dw_morbidity_diagnostic_facilities_view',   'dw_contact_events_id',               'dw_contact_events_id',               '',                          false),
('trisano.dw_morbidity_diagnostic_facilities_view',   'dw_morbidity_events_id',             'dw_morbidity_events_id',             '',                          false),
('trisano.dw_morbidity_diagnostic_facilities_view',   'id',                                 'id',                                 '',                          false),
('trisano.dw_morbidity_diagnostic_facilities_view',   'name',                               'name',                               'MDF Nm',                    true),
('trisano.dw_morbidity_diagnostic_facilities_view',   'place_id',                           'place_id',                           '',                          false),
('trisano.dw_morbidity_diagnostic_facilities_view',   'place_type',                         'place_type',                         'MDF Typ',                   true),
('trisano.dw_morbidity_email_addresses_view',         'email_address',                      'email_address',                      'MP Email Addr',             true),
('trisano.dw_morbidity_email_addresses_view',         'entity_id',                          'entity_id',                          '',                          false),
('trisano.dw_morbidity_events_view',                  'active',                             'active',                             'MD Act',                    true),
('trisano.dw_morbidity_events_view',                  'actual_age_at_onset',                'actual_age_at_onset',                'ME Actl Age at Onst',       true),
('trisano.dw_morbidity_events_view',                  'actual_age_type',                    'actual_age_type',                    'ME Actl Age Typ',           true),
('trisano.dw_morbidity_events_view',                  'acuity',                             'acuity',                             'ME Acuity',                 true),
('trisano.dw_morbidity_events_view',                  'additional_risk_factors',            'additional_risk_factors',            'ME Addtl Risk Fctrs',       true),
('trisano.dw_morbidity_events_view',                  'age_in_years',                       'age_in_years',                       'ME Age in Yrs',             true),
('trisano.dw_morbidity_events_view',                  'event_onset_age_days',               'event_onset_age_days',               'ME Onst Age in Days',       true),
('trisano.dw_morbidity_events_view',                  'always_one',                         'always_one',                         '',                          false),
('trisano.dw_morbidity_events_view',                  'birth_date',                         'birth_date',                         'MP Brth Dt',                true),
('trisano.dw_morbidity_events_view',                  'birth_gender',                       'birth_gender',                       'MP Brth Gndr',              true),
('trisano.dw_morbidity_events_view',                  'cdc_code',                           'cdc_code',                           'MD CDC Cd',                 true),
('trisano.dw_morbidity_events_view',                  'city',                               'city',                               'ME City',                   true),
('trisano.dw_morbidity_events_view',                  'contact_lead_in',                    'contact_lead_in',                    'MD Cntct Lead-in',          true),
('trisano.dw_morbidity_events_view',                  'contact_type_if_once_a_contact',     'contact_type_if_once_a_contact',     'ME Cntct Typ',              true),
('trisano.dw_morbidity_events_view',                  'county',                             'county',                             'ME Cnty',                   true),
('trisano.dw_morbidity_events_view',                  'date_created',                       'date_created',                       '',                          false),
('trisano.dw_morbidity_events_view',                  'date_deleted',                       'date_deleted',                       '',                          false),
('trisano.dw_morbidity_events_view',                  'date_disease_diagnosed',             'date_disease_diagnosed',             'ME Dt Dis Diag',            true),
('trisano.dw_morbidity_events_view',                  'date_disease_onset',                 'date_disease_onset',                 'ME Dt Dis Onst',            true),
('trisano.dw_morbidity_events_view',                  'date_entered_into_system',           'date_entered_into_system',           'ME Event Date',             true),
('trisano.dw_morbidity_events_view',                  'date_investigation_completed',       'date_investigation_completed',       'ME Dt Inv Cmpl',            true),
('trisano.dw_morbidity_events_view',                  'date_investigation_started',         'date_investigation_started',         'ME Dt Inv Strtd',           true),
('trisano.dw_morbidity_events_view',                  'date_of_death',                      'date_of_death',                      'MP Dt of Death',            true),
('trisano.dw_morbidity_events_view',                  'date_reported_to_public_health',     'date_reported_to_public_health',     'ME Dt Rptd to PH',          true),
('trisano.dw_morbidity_events_view',                  'date_updated',                       'date_updated',                       'ME Dt Updtd',               true),
('trisano.dw_morbidity_events_view',                  'day_care_association',               'day_care_association',               'ME Day Care Assoc',         true),
('trisano.dw_morbidity_events_view',                  'disease_event_died',                 'disease_event_died',                 'ME Died',                   true),
('trisano.dw_morbidity_events_view',                  'disease_event_hospitalized',         'disease_event_hospitalized',         'ME Hospitalized',           true),
('trisano.dw_morbidity_events_view',                  'disease_id',                         'disease_id',                         '',                          false),<commit_msg>Date event record was created and deleted is not available in avr<commit_after>BEGIN;

-- Ideally we'd have the foreign keys described here, but foreign keys to
-- system tables aren't allowed

DROP TABLE IF EXISTS trisano.core_tables;

CREATE TABLE trisano.core_tables (
    table_name NAME PRIMARY KEY,
    table_description TEXT,
    target_table TEXT NOT NULL, -- The actual table name this should point at
    order_num INTEGER NOT NULL,
    make_category BOOLEAN DEFAULT true,
    formbuilder_prefix TEXT
);

DROP TABLE IF EXISTS trisano.core_columns;

CREATE TABLE trisano.core_columns (
    target_column NAME,
    target_table TEXT, -- The actual table this column belongs in
    PRIMARY KEY (target_column, target_table),
    column_name TEXT,
    column_description TEXT,
    make_category_column BOOLEAN DEFAULT true
);

INSERT INTO trisano.core_tables (make_category, table_name, table_description, target_table, order_num, formbuilder_prefix) VALUES
(true,  'dw_morbidity_events_view',                  'Morbidity Events',                  'trisano.dw_morbidity_events_view',                  1,  'Morbidity'),
(true,  'dw_morbidity_clinicians_view',              'Morbidity Clinicians',              'trisano.dw_morbidity_clinicians_view',              2,  NULL),
(true,  'dw_morbidity_diagnostic_facilities_view',   'Morbidity Diagnostic Facilities',   'trisano.dw_morbidity_diagnostic_facilities_view',   2,  NULL),
(true,  'dw_morbidity_email_addresses_view',         'Morbidity Email Addresses',         'trisano.dw_morbidity_email_addresses_view',         2,  NULL),
(true,  'dw_morbidity_hospitals_view',               'Morbidity Hospitals',               'trisano.dw_morbidity_hospitals_view',               2,  NULL),
(true,  'dw_morbidity_lab_results_view',             'Morbidity Lab Results',             'trisano.dw_morbidity_lab_results_view',             2,  NULL),
(true,  'dw_morbidity_patients_races_view',          'Morbidity Patients Races',          'trisano.dw_morbidity_patients_races_view',          2,  NULL),
(true,  'dw_morbidity_secondary_jurisdictions_view', 'Morbidity Secondary Jurisdictions', 'trisano.dw_morbidity_secondary_jurisdictions_view', 2,  NULL),
(true,  'dw_morbidity_telephones_view',              'Morbidity Telephone Numbers',       'trisano.dw_morbidity_telephones_view',              2,  NULL),
(true,  'dw_morbidity_treatments_events_view',       'Morbidity Treatments Events',       'trisano.dw_morbidity_treatments_events_view',       2,  NULL),
(true,  'dw_contact_events_view',                    'Contact Events',                    'trisano.dw_contact_events_view',                    3,  'Contact'),
(true,  'dw_contact_clinicians_view',                'Contact Clinicians',                'trisano.dw_contact_clinicians_view',                4,  NULL),
(true,  'dw_contact_diagnostic_facilities_view',     'Contact Diagnostic Facilities',     'trisano.dw_contact_diagnostic_facilities_view',     4,  NULL),
(true,  'dw_contact_email_addresses_view',           'Contact Email Addresses',           'trisano.dw_contact_email_addresses_view',           4,  NULL),
(true,  'dw_contact_hospitals_view',                 'Contact Hospitals',                 'trisano.dw_contact_hospitals_view',                 4,  NULL),
(true,  'dw_contact_lab_results_view',               'Contact Lab Results',               'trisano.dw_contact_lab_results_view',               4,  NULL),
(true,  'dw_contact_patients_races_view',            'Contact Patients Races',            'trisano.dw_contact_patients_races_view',            4,  NULL),
(true,  'dw_contact_secondary_jurisdictions_view',   'Contact Secondary Jurisdictions',   'trisano.dw_contact_secondary_jurisdictions_view',   4,  NULL),
(true,  'dw_contact_telephones_view',                'Contact Telephone Numbers',         'trisano.dw_contact_telephones_view',                4,  NULL),
(true,  'dw_contact_treatments_events_view',         'Contact Treatments Events',         'trisano.dw_contact_treatments_events_view',         4,  NULL),
(true,  'dw_encounter_events_view',                  'Encounters',                        'trisano.dw_encounter_events_view',                  5,  'Encounter'),
(true,  'dw_encounter_email_addresses_view',         'Encounter Email Addresses',         'trisano.dw_encounter_email_addresses_view',         6,  NULL),
(true,  'dw_encounters_lab_results_view',            'Encounters Lab Results',            'trisano.dw_encounters_lab_results_view',            6,  NULL),
(true,  'dw_encounter_patients_races_view',          'Encounter Patients Races',          'trisano.dw_encounter_patients_races_view',          6,  NULL),
(true,  'dw_encounter_telephones_view',              'Encounter Telephone Numbers',       'trisano.dw_encounter_telephones_view',              6,  NULL),
(true,  'dw_encounters_treatments_events_view',      'Encounters Treatments Events',      'trisano.dw_encounters_treatments_events_view',      6,  NULL),
(true,  'dw_place_events_view',                      'Place Events',                      'trisano.dw_place_events_view',                      7,  'Place'),
(true,  'avr_groups_view',                           'Disease Groups',                    'trisano.avr_groups_view',                           9,  NULL),
(false, 'avr_groups_diseases_view',                  'avr_groups_diseases_view',          'trisano.avr_groups_diseases_view',                  9,  NULL);

INSERT INTO trisano.core_columns (target_table, target_column, column_name, column_description, make_category_column) VALUES
('trisano.avr_groups_diseases_view',                  'avr_group_id',                       'avr_group_id',                       '',                          false),
('trisano.avr_groups_diseases_view',                  'created_at',                         'created_at',                         '',                          false),
('trisano.avr_groups_diseases_view',                  'disease_id',                         'disease_id',                         '',                          false),
('trisano.avr_groups_diseases_view',                  'updated_at',                         'updated_at',                         '',                          false),
('trisano.avr_groups_view',                           'created_at',                         'created_at',                         '',                          false),
('trisano.avr_groups_view',                           'id',                                 'id',                                 '',                          false),
('trisano.avr_groups_view',                           'name',                               'name',                               'Dis Grp Nm',                true),
('trisano.avr_groups_view',                           'updated_at',                         'updated_at',                         '',                          false),
('trisano.dw_contact_clinicians_view',                'dw_contact_events_id',               'dw_contact_events_id',               '',                          false),
('trisano.dw_contact_clinicians_view',                'first_name',                         'first_name',                         'CC Frst Nm',                true),
('trisano.dw_contact_clinicians_view',                'id',                                 'id',                                 '',                          false),
('trisano.dw_contact_clinicians_view',                'last_name',                          'last_name',                          'CC Lst Nm',                 true),
('trisano.dw_contact_clinicians_view',                'middle_name',                        'middle_name',                        'CC Mid Nm',                 true),
('trisano.dw_contact_clinicians_view',                'phones',                             'phones',                             'CC Phn Nbrs',               true),
('trisano.dw_contact_diagnostic_facilities_view',     'dw_contact_events_id',               'dw_contact_events_id',               '',                          false),
('trisano.dw_contact_diagnostic_facilities_view',     'dw_morbidity_events_id',             'dw_morbidity_events_id',             '',                          false),
('trisano.dw_contact_diagnostic_facilities_view',     'id',                                 'id',                                 '',                          false),
('trisano.dw_contact_diagnostic_facilities_view',     'name',                               'name',                               'CDF Nm',                    true),
('trisano.dw_contact_diagnostic_facilities_view',     'place_id',                           'place_id',                           '',                          false),
('trisano.dw_contact_diagnostic_facilities_view',     'place_type',                         'place_type',                         'CDF Typ',                   true),
('trisano.dw_contact_email_addresses_view',           'email_address',                      'email_address',                      'CP Email Addr',             true),
('trisano.dw_contact_email_addresses_view',           'entity_id',                          'entity_id',                          '',                          false),
('trisano.dw_contact_events_view',                    'active',                             'active',                             'CD Act',                    true),
('trisano.dw_contact_events_view',                    'actual_age_at_onset',                'actual_age_at_onset',                'CE Actl Age at Onst',       true),
('trisano.dw_contact_events_view',                    'actual_age_type',                    'actual_age_type',                    'CE Actl Age Typ',           true),
('trisano.dw_contact_events_view',                    'additional_risk_factors',            'additional_risk_factors',            'CE Addtl Risk Fctrs',       true),
('trisano.dw_contact_events_view',                    'age_in_years',                       'age_in_years',                       'CE Age in Yrs',             true),
('trisano.dw_contact_events_view',                    'event_onset_age_days',               'event_onset_age_days',               'CE Onst Age in Days',       true),
('trisano.dw_contact_events_view',                    'always_one',                         'always_one',                         '',                          false),
('trisano.dw_contact_events_view',                    'birth_date',                         'birth_date',                         'CP Brth Dt',                true),
('trisano.dw_contact_events_view',                    'birth_gender',                       'birth_gender',                       'CP Brth Gndr',              true),
('trisano.dw_contact_events_view',                    'cdc_code',                           'cdc_code',                           'CD CDC Cd',                 true),
('trisano.dw_contact_events_view',                    'city',                               'city',                               'CE City',                   true),
('trisano.dw_contact_events_view',                    'contact_lead_in',                    'contact_lead_in',                    'CD Cntct Lead-in',          true),
('trisano.dw_contact_events_view',                    'contact_type',                       'contact_type',                       'CE Cntct Typ',              true),
('trisano.dw_contact_events_view',                    'county',                             'county',                             'CE Cnty',                   true),
('trisano.dw_contact_events_view',                    'date_created',                       'date_created',                       'CE Dt Crtd',                          false),
('trisano.dw_contact_events_view',                    'date_deleted',                       'date_deleted',                       'CE Dt Dltd',                          false),
('trisano.dw_contact_events_view',                    'date_disease_diagnosed',             'date_disease_diagnosed',             'CE Dt Dis Diag',            true),
('trisano.dw_contact_events_view',                    'date_disease_onset',                 'date_disease_onset',                 'CE Dt Dis Onst',            true),
('trisano.dw_contact_events_view',                    'date_entered_into_system',           'date_entered_into_system',           'CE Event Date',             true),
('trisano.dw_contact_events_view',                    'date_investigation_completed',       'date_investigation_completed',       'CE Dt Inv Cmpl',            true),
('trisano.dw_contact_events_view',                    'date_investigation_started',         'date_investigation_started',         'CE Dt Inv Strtd',           true),
('trisano.dw_contact_events_view',                    'date_of_death',                      'date_of_death',                      'CP Dt of Death',            true),
('trisano.dw_contact_events_view',                    'date_updated',                       'date_updated',                       'CE Dt Updtd',                          false),
('trisano.dw_contact_events_view',                    'day_care_association',               'day_care_association',               'CE Day Care Assoc',         true),
('trisano.dw_contact_events_view',                    'disease_event_died',                 'disease_event_died',                 'CE Died',                   true),
('trisano.dw_contact_events_view',                    'disease_event_hospitalized',         'disease_event_hospitalized',         'CE Hospitalized',           true),
('trisano.dw_contact_events_view',                    'disease_id',                         'disease_id',                         '',                          false),
('trisano.dw_contact_events_view',                    'disease_name',                       'disease_name',                       '<NAME>',                 true),
('trisano.dw_contact_events_view',                    'disposition',                        'disposition',                        'CE Dsp',                    true),
('trisano.dw_contact_events_view',                    'dw_patients_id',                     'dw_patients_id',                     '',                          false),
('trisano.dw_contact_events_view',                    'entity_id',                          'entity_id',                          '',                          false),
('trisano.dw_contact_events_view',                    'estimated_age_at_onset',             'estimated_age_at_onset',             'CE Est Age at Onst',        true),
('trisano.dw_contact_events_view',                    'estimated_age_type',                 'estimated_age_type',                 'CE Est Age Typ',            true),
('trisano.dw_contact_events_view',                    'ethnicity',                          'ethnicity',                          'CP Ethncty',                true),
('trisano.dw_contact_events_view',                    'event_queue_id',                     'event_queue_id',                     '',                          false),
('trisano.dw_contact_events_view',                    'first_name',                         'first_name',                         'CP Frst Nm',                true),
('trisano.dw_contact_events_view',                    'first_name_soundex',                 'first_name_soundex',                 '',                          false),
('trisano.dw_contact_events_view',                    'food_handler',                       'food_handler',                       'CE Food Hdlr',              true),
('trisano.dw_contact_events_view',                    'group_living',                       'group_living',                       'CE Grp Lvng',               true),
('trisano.dw_contact_events_view',                    'healthcare_worker',                  'healthcare_worker',                  'CE Hlthcare Wrkr',          true),
('trisano.dw_contact_events_view',                    'ibis_updated_at',                    'ibis_updated_at',                    'CE IBIS Updtd',             true),
('trisano.dw_contact_events_view',                    'id',                                 'id',                                 '',                          false),
('trisano.dw_contact_events_view',                    'imported_from_code',                 'imported_from_code',                 '',                          false),
('trisano.dw_contact_events_view',                    'investigating_jurisdiction',         'investigating_jurisdiction',         'CE Inv Jur',                true),
('trisano.dw_contact_events_view',                    'investigating_jurisdiction_id',      'investigating_jurisdiction_id',      '',                          false),
('trisano.dw_contact_events_view',                    'investigator',                       'investigator',                       'CE Invstgtr',               true),
('trisano.dw_contact_events_view',                    'jurisdiction_of_residence',          'jurisdiction_of_residence',          'CE Jur of Res',             true),
('trisano.dw_contact_events_view',                    'jurisdiction_of_residence_id',       'jurisdiction_of_residence_id',       '',                          false),
('trisano.dw_contact_events_view',                    'last_name',                          'last_name',                          'CP Lst Nm',                 true),
('trisano.dw_contact_events_view',                    'last_name_soundex',                  'last_name_soundex',                  '',                          false),
('trisano.dw_contact_events_view',                    'middle_name',                        'middle_name',                        'CP Mid Nm',                 true),
('trisano.dw_contact_events_view',                    'occupation',                         'occupation',                         'CE Occ',                    true),
('trisano.dw_contact_events_view',                    'other_data_1',                       'other_data_1',                       'CE Othr Data 1',            true),
('trisano.dw_contact_events_view',                    'other_data_2',                       'other_data_2',                       'CE Othr Data 2',            true),
('trisano.dw_contact_events_view',                    'parent_id',                          'parent_id',                          '',                          false),
('trisano.dw_contact_events_view',                    'patient_entity_id',                  'patient_entity_id',                  '',                          false),
('trisano.dw_contact_events_view',                    'place_lead_in',                      'place_lead_in',                      'CD Plc Lead-in',            true),
('trisano.dw_contact_events_view',                    'postal_code',                        'postal_code',                        'CE Pstl Cd',                true),
('trisano.dw_contact_events_view',                    'pregnancy_due_date',                 'pregnancy_due_date',                 'CE Pregnancy Due Dt',       true),
('trisano.dw_contact_events_view',                    'pregnant',                           'pregnant',                           'CE Preg',                   true),
('trisano.dw_contact_events_view',                    'primary_language',                   'primary_language',                   'CP Prim Lng',               true),
('trisano.dw_contact_events_view',                    'public_health_status',               'public_health_status',               'CE PH Stat',                true),
('trisano.dw_contact_events_view',                    'record_number',                      'record_number',                      'CE Rec Nbr',                true),
('trisano.dw_contact_events_view',                    'review_completed_by_state_date',     'review_completed_by_state_date',     'CE Rvw Cmpl by St',         true),
('trisano.dw_contact_events_view',                    'risk_factor_details',                'risk_factor_details',                'CE Risk Fctr Dtls',         true),
('trisano.dw_contact_events_view',                    'sent_to_ibis',                       'sent_to_ibis',                       'CE Snt to IBIS',            true),
('trisano.dw_contact_events_view',                    'state',                              'state',                              'CE St',                     true),
('trisano.dw_contact_events_view',                    'street_name',                        'street_name',                        'CE Street Nm',              true),
('trisano.dw_contact_events_view',                    'street_number',                      'street_number',                      'CE Street Nbr',             true),
('trisano.dw_contact_events_view',                    'treatment_lead_in',                  'treatment_lead_in',                  'CD Trtmnt Lead-in',         true),
('trisano.dw_contact_events_view',                    'unit_number',                        'unit_number',                        'CE Unit Nbr',               true),
('trisano.dw_contact_hospitals_view',                 'admission_date',                     'admission_date',                     'CH Admsn Dt',               true),
('trisano.dw_contact_hospitals_view',                 'discharge_date',                     'discharge_date',                     'CH Dschg Dt',               true),
('trisano.dw_contact_hospitals_view',                 'dw_contact_events_id',               'dw_contact_events_id',               '',                          false),
('trisano.dw_contact_hospitals_view',                 'dw_morbidity_events_id',             'dw_morbidity_events_id',             '',                          false),
('trisano.dw_contact_hospitals_view',                 'hospital_name',                      'hospital_name',                      'CH Hosp Nm',                true),
('trisano.dw_contact_hospitals_view',                 'hospital_record_number',             'hospital_record_number',             'CH Hosp Rec Nbr',           true),
('trisano.dw_contact_hospitals_view',                 'id',                                 'id',                                 '',                          false),
('trisano.dw_contact_hospitals_view',                 'medical_record_number',              'medical_record_number',              'CH Med Rec Nbr',            true),
('trisano.dw_contact_lab_results_view',               'collection_date',                    'collection_date',                    'CLR Clct Dt',               true),
('trisano.dw_contact_lab_results_view',               'comment',                            'comment',                            'CLR Cmt',                   true),
('trisano.dw_contact_lab_results_view',               'dw_contact_events_id',               'dw_contact_events_id',               '',                          false),
('trisano.dw_contact_lab_results_view',               'dw_encounter_events_id',             'dw_encounter_events_id',             '',                          false),
('trisano.dw_contact_lab_results_view',               'dw_morbidity_events_id',             'dw_morbidity_events_id',             '',                          false),
('trisano.dw_contact_lab_results_view',               'hl7_message',                        'hl7_message',                        'CLR HL7 Msg',               true),
('trisano.dw_contact_lab_results_view',               'id',                                 'id',                                 '',                          false),
('trisano.dw_contact_lab_results_view',               'lab_test_date',                      'lab_test_date',                      'CLR Lab Tst Dt',            true),
('trisano.dw_contact_lab_results_view',               'lab_type',                           'lab_type',                           'CLR Lab Typ',               true),
('trisano.dw_contact_lab_results_view',               'loinc_code',                         'loinc_code',                         'CLR LOINC Cd',              true),
('trisano.dw_contact_lab_results_view',               'name',                               'name',                               'CLR Nm',                    true),
('trisano.dw_contact_lab_results_view',               'organism_name',                      'organism_name',                      'CLR Org Nm',                true),
('trisano.dw_contact_lab_results_view',               'reference_range',                    'reference_range',                    'CLR Ref Rng',               true),
('trisano.dw_contact_lab_results_view',               'result_value',                       'result_value',                       'CLR Rslt Val',              true),
--('trisano.dw_contact_lab_results_view',               'snomed_code',                        'snomed_code',                        'CLR Snmd Cd',               true),
--('trisano.dw_contact_lab_results_view',               'snomed_id',                          'snomed_id',                          'CLR Snmd Id',               true),
--('trisano.dw_contact_lab_results_view',               'snomed_name',                        'snomed_name',                        'CLR Snmd Nm',               true),
('trisano.dw_contact_lab_results_view',               'specimen_sent_to_state',             'specimen_sent_to_state',             'CLR Spec Snt to St',        true),
('trisano.dw_contact_lab_results_view',               'specimen_source',                    'specimen_source',                    'CLR Spec Src',              true),
('trisano.dw_contact_lab_results_view',               'staged_message_note',                'staged_message_note',                'CLR Stgd Msg Note',         true),
('trisano.dw_contact_lab_results_view',               'staged_message_state',               'staged_message_state',               'CLR Stgd Msg St',           true),
('trisano.dw_contact_lab_results_view',               'test_result',                        'test_result',                        'CLR Tst Rslt',              true),
('trisano.dw_contact_lab_results_view',               'test_status',                        'test_status',                        'CLR Tst Stat',              true),
('trisano.dw_contact_lab_results_view',               'test_type',                          'test_type',                          'CLR Tst Typ',               true),
('trisano.dw_contact_lab_results_view',               'units',                              'units',                              'CLR Units',                 true),
('trisano.dw_contact_patients_races_view',            'id',                                 'id',                                 '',                          false),
('trisano.dw_contact_patients_races_view',            'person_id',                          'person_id',                          '',                          false),
('trisano.dw_contact_patients_races_view',            'race',                               'race',                               'CP Race',                   true),
('trisano.dw_contact_secondary_jurisdictions_view',   'dw_contact_events_id',               'dw_contact_events_id',               '',                          false),
('trisano.dw_contact_secondary_jurisdictions_view',   'dw_morbidity_events_id',             'dw_morbidity_events_id',             '',                          false),
('trisano.dw_contact_secondary_jurisdictions_view',   'id',                                 'id',                                 '',                          false),
('trisano.dw_contact_secondary_jurisdictions_view',   'jurisdiction_id',                    'jurisdiction_id',                    '',                          false),
('trisano.dw_contact_secondary_jurisdictions_view',   'name',                               'name',                               'CSJ Nm',                    true),
('trisano.dw_contact_telephones_view',                'area_code',                          'area_code',                          'CP Area Cd',                true),
('trisano.dw_contact_telephones_view',                'country_code',                       'country_code',                       'CP Cntry Cd',               true),
('trisano.dw_contact_telephones_view',                'entity_id',                          'entity_id',                          '',                          false),
('trisano.dw_contact_telephones_view',                'extension',                          'extension',                          'CP Ext',                    true),
('trisano.dw_contact_telephones_view',                'phone_number',                       'phone_number',                       'CP Phn Nbr',                true),
('trisano.dw_contact_telephones_view',                'phone_type',                         'phone_type',                         'CP Phn Typ',                true),
('trisano.dw_contact_treatments_events_view',         'date_of_treatment',                  'date_of_treatment',                  'CT Dt of Trtmnt',           true),
('trisano.dw_contact_treatments_events_view',         'dw_contact_events_id',               'dw_contact_events_id',               '',                          false),
('trisano.dw_contact_treatments_events_view',         'dw_encounter_events_id',             'dw_encounter_events_id',             '',                          false),
('trisano.dw_contact_treatments_events_view',         'dw_morbidity_events_id',             'dw_morbidity_events_id',             '',                          false),
('trisano.dw_contact_treatments_events_view',         'id',                                 'id',                                 '',                          false),
('trisano.dw_contact_treatments_events_view',         'stop_treatment_date',                'stop_treatment_date',                'CT Dt Trtmnt Stppd',        true),
('trisano.dw_contact_treatments_events_view',         'treatment_given',                    'treatment_given',                    'CT Trtmnt Gvn',             true),
('trisano.dw_contact_treatments_events_view',         'treatment_id',                       'treatment_id',                       '',                          false),
('trisano.dw_contact_treatments_events_view',         'treatment_notes',                    'treatment_notes',                    'CT Trtmnt Notes',           true),
('trisano.dw_encounter_email_addresses_view',         'email_address',                      'email_address',                      'EP Email Addr',             true),
('trisano.dw_encounter_email_addresses_view',         'entity_id',                          'entity_id',                          '',                          false),
('trisano.dw_encounter_events_view',                  'birth_date',                         'birth_date',                         'EP Brth Dt',                true),
('trisano.dw_encounter_events_view',                  'birth_gender',                       'birth_gender',                       'EP Brth Gndr',              true),
('trisano.dw_encounter_events_view',                  'date_of_death',                      'date_of_death',                      'EP Dt of Death',            true),
('trisano.dw_encounter_events_view',                  'description',                        'description',                        'EE Desc',                   true),
('trisano.dw_encounter_events_view',                  'dw_patients_id',                     'dw_patients_id',                     '',                          false),
('trisano.dw_encounter_events_view',                  'dw_morbidity_events_id',             'dw_morbidity_events_id',             '',                          false),
('trisano.dw_encounter_events_view',                  'encounter_date',                     'encounter_date',                     'EE Dt',                     true),
('trisano.dw_encounter_events_view',                  'encounter_event_id',                 'encounter_event_id',                 '',                          false),
('trisano.dw_encounter_events_view',                  'ethnicity',                          'ethnicity',                          'EP Ethncty',                true),
('trisano.dw_encounter_events_view',                  'first_name',                         'first_name',                         'EP Frst Nm',                true),
('trisano.dw_encounter_events_view',                  'id',                                 'id',                                 '',                          false),
('trisano.dw_encounter_events_view',                  'investigator_id',                    'investigator_id',                    'EE Invstgtr',               true),
('trisano.dw_encounter_events_view',                  'last_name',                          'last_name',                          'EP Lst Nm',                 true),
('trisano.dw_encounter_events_view',                  'location',                           'location',                           'EE Loc',                    true),
('trisano.dw_encounter_events_view',                  'middle_name',                        'middle_name',                        'EP Mid Nm',                 true),
('trisano.dw_encounter_events_view',                  'patient_entity_id',                  'patient_entity_id',                  '',                          false),
('trisano.dw_encounter_events_view',                  'primary_language',                   'primary_language',                   'EP Prim Lng',               true),
('trisano.dw_encounter_patients_races_view',          'id',                                 'id',                                 '',                          false),
('trisano.dw_encounter_patients_races_view',          'person_id',                          'person_id',                          '',                          false),
('trisano.dw_encounter_patients_races_view',          'race',                               'race',                               'EP Race',                   true),
('trisano.dw_encounters_lab_results_view',            'collection_date',                    'collection_date',                    'ELR Clct Dt',               true),
('trisano.dw_encounters_lab_results_view',            'comment',                            'comment',                            'ELR Cmt',                   true),
('trisano.dw_encounters_lab_results_view',            'dw_contact_events_id',               'dw_contact_events_id',               '',                          false),
('trisano.dw_encounters_lab_results_view',            'dw_encounter_events_id',             'dw_encounter_events_id',             '',                          false),
('trisano.dw_encounters_lab_results_view',            'dw_morbidity_events_id',             'dw_morbidity_events_id',             '',                          false),
('trisano.dw_encounters_lab_results_view',            'hl7_message',                        'hl7_message',                        'ELR HL7 Msg',               true),
('trisano.dw_encounters_lab_results_view',            'id',                                 'id',                                 'EE Lab Rslts ',             true),
('trisano.dw_encounters_lab_results_view',            'lab_test_date',                      'lab_test_date',                      'ELR Lab Tst Dt',            true),
('trisano.dw_encounters_lab_results_view',            'lab_type',                           'lab_type',                           'ELR Lab Typ',               true),
('trisano.dw_encounters_lab_results_view',            'loinc_code',                         'loinc_code',                         'ELR LOINC Cd',              true),
('trisano.dw_encounters_lab_results_view',            'name',                               'name',                               'ELR Lab Nm',                true),
('trisano.dw_encounters_lab_results_view',            'organism_name',                      'organism_name',                      'ELR Org Nm',                true),
('trisano.dw_encounters_lab_results_view',            'reference_range',                    'reference_range',                    'ELR Ref Rng',               true),
('trisano.dw_encounters_lab_results_view',            'result_value',                       'result_value',                       'ELR Rslt Val',              true),
--('trisano.dw_encounters_lab_results_view',            'snomed_code',                        'snomed_code',                        'ELR Snmd Cd',               true),
--('trisano.dw_encounters_lab_results_view',            'snomed_id',                          'snomed_id',                          'ELR Snmd Id',               true),
--('trisano.dw_encounters_lab_results_view',            'snomed_name',                        'snomed_name',                        'ELR Snmd Nm',               true),
('trisano.dw_encounters_lab_results_view',            'specimen_sent_to_state',             'specimen_sent_to_state',             'ELR Spec Snt to St',        true),
('trisano.dw_encounters_lab_results_view',            'specimen_source',                    'specimen_source',                    'ELR Spec Src',              true),
('trisano.dw_encounters_lab_results_view',            'staged_message_note',                'staged_message_note',                'ELR Stgd Msg Note',         true),
('trisano.dw_encounters_lab_results_view',            'staged_message_state',               'staged_message_state',               'ELR Stgd Msg St',           true),
('trisano.dw_encounters_lab_results_view',            'test_result',                        'test_result',                        'ELR Tst Rslt',              true),
('trisano.dw_encounters_lab_results_view',            'test_status',                        'test_status',                        'ELR Tst Stat',              true),
('trisano.dw_encounters_lab_results_view',            'test_type',                          'test_type',                          'ELR Tst Typ',               true),
('trisano.dw_encounters_lab_results_view',            'units',                              'units',                              'ELR Units',                 true),
('trisano.dw_encounters_treatments_events_view',      'date_of_treatment',                  'date_of_treatment',                  'ET Dt of Trtmnt',           true),
('trisano.dw_encounters_treatments_events_view',      'dw_contact_events_id',               'dw_contact_events_id',               '',                          false),
('trisano.dw_encounters_treatments_events_view',      'dw_encounter_events_id',             'dw_encounter_events_id',             '',                          false),
('trisano.dw_encounters_treatments_events_view',      'dw_morbidity_events_id',             'dw_morbidity_events_id',             '',                          false),
('trisano.dw_encounters_treatments_events_view',      'id',                                 'id',                                 '',                          false),
('trisano.dw_encounters_treatments_events_view',      'stop_treatment_date',                'stop_treatment_date',                'ET Dt Trtmnt Stppd',        true),
('trisano.dw_encounters_treatments_events_view',      'treatment_given',                    'treatment_given',                    'ET Trtmnt Gvn',             true),
('trisano.dw_encounters_treatments_events_view',      'treatment_id',                       'treatment_id',                       '',                          false),
('trisano.dw_encounters_treatments_events_view',      'treatment_notes',                    'treatment_notes',                    'ET Trtmnt Notes',           true),
('trisano.dw_encounter_telephones_view',              'area_code',                          'area_code',                          'EP Area Cd',                true),
('trisano.dw_encounter_telephones_view',              'country_code',                       'country_code',                       'EP Cntry Cd',               true),
('trisano.dw_encounter_telephones_view',              'entity_id',                          'entity_id',                          '',                          false),
('trisano.dw_encounter_telephones_view',              'extension',                          'extension',                          'EP Ext',                    true),
('trisano.dw_encounter_telephones_view',              'phone_number',                       'phone_number',                       'EP Phn Nbr',                true),
('trisano.dw_encounter_telephones_view',              'phone_type',                         'phone_type',                         'EP Phn Typ',                true),
('trisano.dw_morbidity_clinicians_view',              'dw_morbidity_events_id',             'dw_morbidity_events_id',             '',                          false),
('trisano.dw_morbidity_clinicians_view',              'first_name',                         'first_name',                         'MC Frst Nm',                true),
('trisano.dw_morbidity_clinicians_view',              'id',                                 'id',                                 '',                          false),
('trisano.dw_morbidity_clinicians_view',              'last_name',                          'last_name',                          'MC Lst Nm',                 true),
('trisano.dw_morbidity_clinicians_view',              'middle_name',                        'middle_name',                        'MC Mid Nm',                 true),
('trisano.dw_morbidity_clinicians_view',              'phones',                             'phones',                             'MC Phn Nbrs',               true),
('trisano.dw_morbidity_diagnostic_facilities_view',   'dw_contact_events_id',               'dw_contact_events_id',               '',                          false),
('trisano.dw_morbidity_diagnostic_facilities_view',   'dw_morbidity_events_id',             'dw_morbidity_events_id',             '',                          false),
('trisano.dw_morbidity_diagnostic_facilities_view',   'id',                                 'id',                                 '',                          false),
('trisano.dw_morbidity_diagnostic_facilities_view',   'name',                               'name',                               'MDF Nm',                    true),
('trisano.dw_morbidity_diagnostic_facilities_view',   'place_id',                           'place_id',                           '',                          false),
('trisano.dw_morbidity_diagnostic_facilities_view',   'place_type',                         'place_type',                         'MDF Typ',                   true),
('trisano.dw_morbidity_email_addresses_view',         'email_address',                      'email_address',                      'MP Email Addr',             true),
('trisano.dw_morbidity_email_addresses_view',         'entity_id',                          'entity_id',                          '',                          false),
('trisano.dw_morbidity_events_view',                  'active',                             'active',                             'MD Act',                    true),
('trisano.dw_morbidity_events_view',                  'actual_age_at_onset',                'actual_age_at_onset',                'ME Actl Age at Onst',       true),
('trisano.dw_morbidity_events_view',                  'actual_age_type',                    'actual_age_type',                    'ME Actl Age Typ',           true),
('trisano.dw_morbidity_events_view',                  'acuity',                             'acuity',                             'ME Acuity',                 true),
('trisano.dw_morbidity_events_view',                  'additional_risk_factors',            'additional_risk_factors',            'ME Addtl Risk Fctrs',       true),
('trisano.dw_morbidity_events_view',                  'age_in_years',                       'age_in_years',                       'ME Age in Yrs',             true),
('trisano.dw_morbidity_events_view',                  'event_onset_age_days',               'event_onset_age_days',               'ME Onst Age in Days',       true),
('trisano.dw_morbidity_events_view',                  'always_one',                         'always_one',                         '',                          false),
('trisano.dw_morbidity_events_view',                  'birth_date',                         'birth_date',                         'MP Brth Dt',                true),
('trisano.dw_morbidity_events_view',                  'birth_gender',                       'birth_gender',                       'MP Brth Gndr',              true),
('trisano.dw_morbidity_events_view',                  'cdc_code',                           'cdc_code',                           'MD CDC Cd',                 true),
('trisano.dw_morbidity_events_view',                  'city',                               'city',                               'ME City',                   true),
('trisano.dw_morbidity_events_view',                  'contact_lead_in',                    'contact_lead_in',                    'MD Cntct Lead-in',          true),
('trisano.dw_morbidity_events_view',                  'contact_type_if_once_a_contact',     'contact_type_if_once_a_contact',     'ME Cntct Typ',              true),
('trisano.dw_morbidity_events_view',                  'county',                             'county',                             'ME Cnty',                   true),
('trisano.dw_morbidity_events_view',                  'date_created',                       'date_created',                       'ME Dt Crtd',                          false),
('trisano.dw_morbidity_events_view',                  'date_deleted',                       'date_deleted',                       'ME Dt Dltd',                          false),
('trisano.dw_morbidity_events_view',                  'date_disease_diagnosed',             'date_disease_diagnosed',             'ME Dt Dis Diag',            true),
('trisano.dw_morbidity_events_view',                  'date_disease_onset',                 'date_disease_onset',                 'ME Dt Dis Onst',            true),
('trisano.dw_morbidity_events_view',                  'date_entered_into_system',           'date_entered_into_system',           'ME Event Date',             true),
('trisano.dw_morbidity_events_view',                  'date_investigation_completed',       'date_investigation_completed',       'ME Dt Inv Cmpl',            true),
('trisano.dw_morbidity_events_view',                  'date_investigation_started',         'date_investigation_started',         'ME Dt Inv Strtd',           true),
('trisano.dw_morbidity_events_view',                  'date_of_death',                      'date_of_death',                      'MP Dt of Death',            true),
('trisano.dw_morbidity_events_view',                  'date_reported_to_public_health',     'date_reported_to_public_health',     'ME Dt Rptd to PH',          true),
('trisano.dw_morbidity_events_view',                  'date_updated',                       'date_updated',                       'ME Dt Updtd',               true),
('trisano.dw_morbidity_events_view',                  'day_care_association',               'day_care_association',               'ME Day Care Assoc',         true),
('trisano.dw_morbidity_events_view',                  'disease_event_died',                 'disease_event_died',                 'ME Died',                   true),
('trisano.dw_morbidity_events_view',                  'disease_event_hospitalized',         'disease_event_hospitalized',         'ME Hospitalized',           true),
('trisano.dw_morbidity_events_view',                  'disease_id',                         'disease_id',                         '',                          false),
{'fraction_non_alphanumeric': 0.09575880489666203, 'fraction_numerical': 0.0008572294761491579, 'mean_word_length': 1.1911262798634812, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 419, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '10270', 'n_tokens_mistral': 25360, 'n_tokens_neox': 23726, 'n_words': 3802}