class CreateCabarrusTables < ActiveRecord::Migration
  def change

    create_table :cabarrus_demographics do |t|
      t.integer :patient_id
      t.string  :first_name
      t.string  :middle_name
      t.string  :last_name
      t.string  :dob
      t.string  :sex
      t.string  :race
      t.string  :ethnicity
      t.integer :death_indicator
      t.string  :death_date
      t.string  :marital_status
      t.timestamps null: false
    end
    add_column :cabarrus_demographics, :load_event_id, :integer, references: :load_events

    create_table :cabarrus_encounters do |t|
      t.integer :encounter_id
      t.date    :encounter_date
      t.string  :encounter_reason
      t.string  :encounter_type
      t.string  :encounter_location
      t.string  :encounter_notes
      t.string  :provider_first_name
      t.string  :provider_last_name
      t.integer :provider_npi
      t.string  :residential_address_street
      t.string  :residential_address2_street
      t.string  :residential_address_city
      t.string  :residential_address_state
      t.string  :residential_address_street_zip
      t.string  :mailing_address_street
      t.string  :mailing_address2_street
      t.string  :mailing_address_city
      t.string  :mailing_address_state
      t.string  :mailing_address_zip
      t.string  :primary_payor
      t.string  :secondary_payor
      t.string  :primary_subscriber_no
      t.string  :secondary_subscriber_no
      t.timestamps null: false
    end
    add_column :cabarrus_encounters, :patient_id, :integer, references: :cabarrus_demographics
    add_column :cabarrus_encounters, :load_event_id, :integer, references: :load_events

    create_table :cabarrus_diagnoses do |t|
      t.date    :diag_date
      t.string  :diag_code
      t.string  :diag_code_desc
      t.integer :provider_id
      t.string  :provider_first_name
      t.string  :provider_last_name
      t.integer :provider_npi
      t.integer :diag_sequence
      t.integer :diag_primary_flag
      t.timestamps null: false
    end
    add_column :cabarrus_diagnoses, :patient_id, :integer, references: :cabarrus_demographics
    add_column :cabarrus_diagnoses, :encounter_id, :integer, references: :cabarrus_encounters
    add_column :cabarrus_diagnoses, :load_event_id, :integer, references: :load_events

    create_table :cabarrus_labs do |t|
      t.date    :encounter_date
      t.date    :collection_date
      t.integer :lab_test_item_id
      t.string  :test_name
      t.string  :test_results
      t.integer :report_id
      t.string  :hl7id1
      t.string  :hl7id2
      t.string  :hl7id3
      t.integer :profile_id
      t.timestamps null: false
    end
    add_column :cabarrus_labs, :patient_id, :integer, references: :cabarrus_demographics
    add_column :cabarrus_labs, :encounter_id, :integer, references: :cabarrus_encounters
    add_column :cabarrus_labs, :load_event_id, :integer, references: :load_events

    create_table :cabarrus_hl7_lab_data do |t|
      t.integer   :report_id
      t.string    :performing_site_id
      t.string    :profileid
      t.string    :lab_id
      t.integer   :hl7id
      t.string    :name
      t.string    :value
      t.string    :units
      t.string    :range
      t.string    :flag
      t.string    :notes
      t.string    :result_status
      t.string    :serialno
      t.string    :orbcode
      t.string    :delete_flag
      t.date      :enc_date
      t.timestamps null: false
    end
    add_column :cabarrus_hl7_lab_data, :encounter_id, :integer, references: :cabarrus_encounters
    add_column :cabarrus_hl7_lab_data, :load_event_id, :integer, references: :load_events

    create_table :cabarrus_medications do |t|
      t.date    :start_date
      t.date    :stop_date
      t.integer :med_id
      t.string  :med_name
      t.string  :rx_take
      t.string  :rx_size
      t.string  :rx_frequency
      t.string  :rx_route
      t.string  :rx_duration
      t.string  :med_status
      t.timestamps null: false
    end
    add_column :cabarrus_medications, :patient_id, :integer, references: :cabarrus_demographics
    add_column :cabarrus_medications, :encounter_id, :integer, references: :cabarrus_encounters
    add_column :cabarrus_medications, :load_event_id, :integer, references: :load_events

    create_table :cabarrus_procedures do |t|
      t.date    :procedure_date
      t.string  :procedure_code
      t.integer :provider_id
      t.string  :procedure_provider
      t.string  :provider_first_name
      t.string  :provider_last_name
      t.integer :provider_npi
      t.integer :procedure_sequence
      t.timestamps null: false
    end
    add_column :cabarrus_procedures, :patient_id, :integer, references: :cabarrus_demographics
    add_column :cabarrus_procedures, :encounter_id, :integer, references: :cabarrus_encounters
    add_column :cabarrus_procedures, :load_event_id, :integer, references: :load_events

    create_table :cabarrus_social_histories do |t|
      t.date    :encounter_date
      t.string  :category
      t.string  :social_detail
      t.string  :value
      t.timestamps null: false
    end
    add_column :cabarrus_social_histories, :patient_id, :integer, references: :cabarrus_demographics
    add_column :cabarrus_social_histories, :encounter_id, :integer, references: :cabarrus_encounters
    add_column :cabarrus_social_histories, :load_event_id, :integer, references: :load_events
    
    create_table :cabarrus_vitals do |t|
      t.string  :vital_name
      t.string  :vital_value
      t.timestamps null: false
    end
    add_column :cabarrus_vitals, :patient_id, :integer, references: :cabarrus_demographics
    add_column :cabarrus_vitals, :encounter_id, :integer, references: :cabarrus_encounters
    add_column :cabarrus_vitals, :load_event_id, :integer, references: :load_events
    
    create_table :load_events do |t|
      t.string   :file_name
      t.integer  :file_size
      t.integer  :number_of_rows
      t.string   :data_type
      t.string   :data_source
      t.string   :extract_type
      t.string   :extracted_by
      t.string   :requestor
      t.date     :requested_on
      t.date     :extracted_on
      t.date     :transferred_on
      t.date     :range_start_date
      t.date     :range_end_date
      t.string   :status
      t.string   :comment
      t.timestamps null: false
    end
    
  end
end
