# ISSUE: Clinician ID conflicts with KCH?
# ISSUE: Right now we assume there is no patient data in OpenMRS. If there were, we would need to worry about merging...
# ISSUE: None of the migrated patients have a new national id. The check digit algorithm thwarted us. Update this in Ruby.
# ISSUE: Should we add a Clinician Role for the Clinician users? What about Provider?
# see ISSUE below
# see FIRSTRUN below

# Tables to be incorporated in the concept dictionary (no SQL migration required)
# 1. arv_arvs | concept, drug
# 2. arv_stages | concept
# 3. arv_stage_defining_conditions | concept
#
# Other tables (SQL migration required):
# 1. Clinician | users
# 2. Site | location - use existing locations?
# 3. MasterPatientRecord | patient, patient_name, patient_identifier
# 4. arv_hiv_testing_centers | location - use existing locations?
# 5. arv_patients | encounter, patient, patient_identifier
# 6. arv_patient_arvs | obs
# 7. arv_patient_stage_defining_conditions | obs
# 8. arv_visits | encounter, obs

#  FIRSTRUN: ALTER TABLE qech.Site ADD COLUMN OpenMRSSiteID INT(11);
#  FIRSTRUN: ALTER TABLE qech.arv_hiv_testing_centers ADD COLUMN OpenMRSSiteID INT(11);
#  FIRSTRUN: INSERT INTO qech.Clinician (Clinician_F_Name, Clinician_L_Name, Clinician_ID) VALUES ("", "", 222222);;
#  FIRSTRUN: UPDATE MasterPatientRecord SET Site_ID = 101 WHERE (Site_ID <> 101 AND Site_ID <> 102);;
#  DELETE FROM patient_identifier;
#  DELETE FROM patient_name;
#  DELETE FROM patient_address;
#  DELETE FROM relationship;
#  DELETE FROM person;
#  DELETE FROM obs;
#  DELETE FROM encounter;
#  DELETE FROM patient;
class Migrate

  def self.migrate()

    ActiveRecord::Base.connection.execute("UNLOCK TABLES")

    # Set up the defaults so that the foreign keys are okay...
    User.current_user = User.find_by_username "migration"
    Location.current_location = Location.find_by_name "Baobab Health Programming Room"

    # Migrate the Sites
    # We assign the OpenMRSSiteID in qech to simplify the migration later
    kch_location = Location.find_by_name("Kamuzu Central Hospital")
    qech_location = Location.find_by_name("Queen Elizabeth Central Hospital")
    pih_location = Location.find_by_name("Moyo Clinic")
    ActiveRecord::Base.connection.update("UPDATE qech.Site SET OpenMRSSiteID = #{kch_location.id} WHERE Description = 'Kamuzu Central Hospital'")
    ActiveRecord::Base.connection.update("UPDATE qech.Site SET OpenMRSSiteID = #{qech_location.id} WHERE Description = 'Queen Elizabeth Central Hospital'")
    ActiveRecord::Base.connection.update("UPDATE qech.Site SET OpenMRSSiteID = #{pih_location.id} WHERE Description = 'Partners In Hope'")

    # Migrate the arv_hiv_testing_centers | location
    # We assign the OpenMRSSiteID in qech to simplify the migration later
    # HIV testing centers map existing ids to new openmrs ids in migration of arv_patients
    # | id | name | openmrs location name (and details)
    # +____+____________________________________________________________________
    # |  1 | MACRO | "MACRO" (city:Blantyre)
    # |  2 | Lepra VCT | "Lepra" (desc:Voluntary Counseling and Testing, parent:QECH)
    # |  3 | BAH - Blantyre Adventist Hospital | "Adventist Hospital" (city:Blantyre)
    # |  4 | Tiyanjane | "Tiyanjane"
    # |  5 | Napham | "Napham"
    # |  6 | Unknown | "Unknown"
    # |  7 | College of Medicine | "College of Medicine" (parent:QECH)
    # |  8 | QECH | "Queen Elizabeth Central Hospital"
    # |  9 | Lighthouse | "Lighthouse"
    # | 10 | Mwaiwathu | "Mwaiwathu" (parent:QECH)
    # | 11 | John Hopkins | "John Hopkins"
    # | 12 | Other | "Other"
    # +____+____________________________________________________________________
    ActiveRecord::Base.connection.update("UPDATE qech.arv_hiv_testing_centers \
      SET OpenMRSSiteID = #{Location.find(:first, :conditions => ["name = ? AND city_village = ?", "MACRO", "Blantyre"]).id} \
      WHERE name = 'MACRO'")
    ActiveRecord::Base.connection.update("UPDATE qech.arv_hiv_testing_centers \
      SET OpenMRSSiteID = #{Location.find(:first, :conditions => ["name = ?", "Lepra"]).id} \
      WHERE name = 'Lepra VCT'")
    ActiveRecord::Base.connection.update("UPDATE qech.arv_hiv_testing_centers \
      SET OpenMRSSiteID = #{Location.find(:first, :conditions => ["name = ? AND city_village = ?", "Adventist Hospital", "Blantyre"]).id} \
      WHERE name = 'BAH'")
    ActiveRecord::Base.connection.update("UPDATE qech.arv_hiv_testing_centers \
      SET OpenMRSSiteID = #{Location.find(:first, :conditions => ["name = ?", "Tiyanjane"]).id} \
      WHERE name = 'Tiyanjane'")
    ActiveRecord::Base.connection.update("UPDATE qech.arv_hiv_testing_centers \
      SET OpenMRSSiteID = #{Location.find(:first, :conditions => ["name = ?", "Napham"]).id} \
      WHERE name = 'Napham'")
    ActiveRecord::Base.connection.update("UPDATE qech.arv_hiv_testing_centers \
      SET OpenMRSSiteID = #{Location.find(:first, :conditions => ["name = ?", "Unknown"]).id} \
      WHERE name = 'Unknown'")
    ActiveRecord::Base.connection.update("UPDATE qech.arv_hiv_testing_centers \
      SET OpenMRSSiteID = #{Location.find(:first, :conditions => ["name = ?", "College of Medicine"]).id} \
      WHERE name = 'College of Medicine'")
    ActiveRecord::Base.connection.update("UPDATE qech.arv_hiv_testing_centers \
      SET OpenMRSSiteID = #{Location.find(:first, :conditions => ["name = ?", "Queen Elizabeth Central Hospital"]).id} \
      WHERE name = 'QECH'")
    ActiveRecord::Base.connection.update("UPDATE qech.arv_hiv_testing_centers \
      SET OpenMRSSiteID = #{Location.find(:first, :conditions => ["name = ?", "Lighthouse"]).id} \
      WHERE name = 'Lighthouse'")
    ActiveRecord::Base.connection.update("UPDATE qech.arv_hiv_testing_centers \
      SET OpenMRSSiteID = #{Location.find(:first, :conditions => ["name = ?", "Mwaiwathu"]).id} \
      WHERE name = 'Mwaiwathu'")
    ActiveRecord::Base.connection.update("UPDATE qech.arv_hiv_testing_centers \
      SET OpenMRSSiteID = #{Location.find(:first, :conditions => ["name = ?", "John Hopkins"]).id} \
      WHERE name = 'John Hopkins'")
    ActiveRecord::Base.connection.update("UPDATE qech.arv_hiv_testing_centers \
      SET OpenMRSSiteID = #{Location.find(:first, :conditions => ["name = ?", "Other"]).id} \
      WHERE name = 'Other'")

    # Migrate the Clinicians
    ActiveRecord::Base.connection.select_all("SELECT * FROM qech.Clinician").each { |clinician|
      migrate_clinician_to_user(clinician)
    }

    legacy_nat = PatientIdentifierType.find(:first, :conditions => ["name = ?", "legacy national id"])
    legacy_ped = PatientIdentifierType.find(:first, :conditions => ["name = ?", "legacy pediatric id"])
    birth_ta = PatientIdentifierType.find(:first, :conditions => ["name = ?", "birth traditional authority"])
    mprs = ActiveRecord::Base.connection.select_all("SELECT * FROM qech.MasterPatientRecord")

    @@start = Patient.count()

    ActiveRecord::Base.connection.execute("ALTER TABLE `patient` DISABLE KEYS")
    ActiveRecord::Base.connection.execute("ALTER TABLE `patient_identifier` DISABLE KEYS")
    ActiveRecord::Base.connection.execute("ALTER TABLE `patient_name` DISABLE KEYS")
    ActiveRecord::Base.connection.execute("ALTER TABLE `patient_address` DISABLE KEYS")

#    ActiveRecord::Base.connection.execute("LOCK TABLES `patient` WRITE")
#    ActiveRecord::Base.connection.execute("LOCK TABLES `patient_identifier` WRITE")
#    ActiveRecord::Base.connection.execute("LOCK TABLES `patient_name` WRITE")
#    ActiveRecord::Base.connection.execute("LOCK TABLES `patient_address` WRITE")

    @@bi = ""
    @@count = 0
    mprs.each { |mpr|
      @@count = @@count + 1
      migrate_master_patient_record_to_patient(mpr, legacy_nat, legacy_ped, birth_ta)
    }

#    ActiveRecord::Base.connection.execute("UNLOCK TABLES")
    ActiveRecord::Base.connection.execute("ALTER TABLE `patient` ENABLE KEYS")
    ActiveRecord::Base.connection.execute("ALTER TABLE `patient_identifier` ENABLE KEYS")
    ActiveRecord::Base.connection.execute("ALTER TABLE `patient_name` ENABLE KEYS")
    ActiveRecord::Base.connection.execute("ALTER TABLE `patient_address` ENABLE KEYS")

    return "Migration complete"
  end


  def self.migrate_clinician_to_user(clinician)

    # Feedback
    puts ":Migrating Clinician " + clinician["Clinician_F_Name"] + " " + clinician["Clinician_L_Name"] + " (" + clinician["Clinician_ID"] + ")\n"

    # Try to see if this user already exists
    user = User.find_by_username(clinician["Clinician_ID"])

    # If not add the clinician as a user
    if (user.nil?)
      user = User.new
      user.first_name = clinician["Clinician_F_Name"]
      user.last_name = clinician["Clinician_L_Name"]
      user.username = clinician["Clinician_ID"]
      user.password = clinician["Clinician_ID"]
      user.date_created = DateTime.now
      user.save
    end

    # If the user is there, update the last used
    unless (user.nil? or clinician["Date_Last_Used"].nil?)
      user_prop = UserProperty.find(:first, :conditions => ["user_id = ? AND property = ?", user.id, "Date Last Used"])
      user_prop = UserProperty.new if user_prop.nil?
      user_prop.user_id = user.user_id
      user_prop.property = "Date last used"
      user_prop.property_value = clinician["Date_Last_Used"]
      user_prop.save
    end
  end

  def self.migrate_master_patient_record_to_patient(mpr, legacy_nat, legacy_ped, birth_ta)

    # Feedback
#    puts ":Migrating Master Patient Record " + mpr["First_Name"] + " " + mpr["Last_Name"] + " (" + @@count.to_s + ")\n"

    # The Day_Of_Birth and Month_Of_Birth are used to contain the displayed
    # birthdate, actual or estimated. If the birth day is unknown, the
    # Day_Of_Birth field contains the string "??" instead of a number. This
    # is always displayed in the application as "??" but for date
    # calculations, the Birth_Date field is used (see below). The Birth_Date
    # field always contains the "15" for and unknown date and "Jun" for an
    # unknown month,  or the actual birth date or month if available. So for
    # example, if a patient has no idea what time of year they were born and
    # an estimated year of 1981 is given, the following two fields contain
    # "??" and "???" and the Birth_Date field is assigned "15-Jun-1981".

    # In openMRS this will need to be stored as patient.birthdate with
    # patient.birthdate_estimated = 1 (treated as boolean even though the
    # data type is SMALLINT(5) in the case of an estimated date). If only
    # day of month is unknown it gets set to 15. If month is also unknown
    # then month gets set to July and day of month gets set to 1. With this
    # in place we will be able to determine which component of the date was
    # estimated, which is important.
    birthdate_estimated = 0

    # If both are unknown
    if (mpr["Day_Of_Birth"] = "??" and mpr["Month_Of_Birth"] = "??")
      mpr["Day_Of_Birth"] = "01"
      mpr["Month_Of_Birth"] = "06"
      birthdate_estimated = 1
    # If only day is unknown
    elsif (mpr["Day_Of_Birth"] = "??")
      mpr["Day_Of_Birth"] = "15"
      birthdate_estimated = 1
    # If only month is unknown (rare?)
    elsif (mpr["Month_Of_Birth"] = "??")
      mpr["Month_Of_Birth"] = "06"
      birthdate_estimated = 1
    end

#    location_id = ActiveRecord::Base.connection.select_one("SELECT OpenMRSSiteID FROM qech.Site WHERE SiteID = #{mpr['Site_ID']}")["OpenMRSSiteID"]
#    user = User.find_by_username(mpr["Reg_By"])
    user = User.current_user
    location_id = 619

    # Add the patient...
    # NOTE: the openmrs.patient table is assumed to not have conflicts!
    patient = Patient.new
    patient.birthdate = birthdate = DateTime::parse(mpr["Year_Of_Birth"] + "-" + mpr["Month_Of_Birth"] + "-" + mpr["Day_Of_Birth"])
    patient.birthdate_estimated = birthdate_estimated
    patient.creator = user.user_id
    patient.date_created = convert_date(mpr["Date_Reg"], "")
    patient.health_center = location_id
    if (mpr["Sex"] == "M")
      patient.gender = "Male"
    else
      patient.gender = "Female"
    end
    unless (@@bi == "")
      @@bi << ","
    end
    @@bi << "(" + (@@start+@@count).to_s + "," + \
            "'" + patient.gender + "'," + \
            "NULL," + \
            "'" + patient.birthdate.to_s + "'," + \
            birthdate_estimated.to_s + "," + \
            "NULL," + \
            "NULL," + \
            "NULL," + \
            "NULL," + \
            "NULL," + \
            "NULL," + \
            "NULL," + \
            "NULL," + \
            "NULL," + \
            patient.health_center.to_s + "," + \
            patient.creator.to_s + "," + \
            "'" + patient.date_created.to_s + "'," + \
            "NULL," + \
            "NULL," + \
            "NULL," + \
            "NULL," + \
            "NULL," + \
            "NULL)"

    if (@@count % 100 == 0)
      puts "Buffering... " + @@count.to_s
    end

    if (@@count % 10000 == 0)
      puts "Inserting block " + @@count.to_s
      ActiveRecord::Base.connection.execute("LOCK TABLES `patient` WRITE")
      ActiveRecord::Base.connection.execute("INSERT INTO patient VALUES " + @@bi)
      ActiveRecord::Base.connection.execute("UNLOCK TABLES")
      @@bi = ""
    end


#    patient.save


    return
    puts "Not here"

    # Add in the identifiers and traditional authority
    unless (mpr["Pat_ID"].nil?)
      pi = PatientIdentifier.new
      pi.patient_id = patient.patient_id
      pi.identifier = mpr["Pat_ID"]
      pi.identifier_type = legacy_nat.patient_identifier_type_id
      pi.location_id = location_id
      pi.creator = user.user_id
      pi.date_created = patient.date_created
      pi.save
    else
      puts "   Pat_ID is null (" + @@count.to_s + ")"
    end

    unless (mpr["Legacy_Pat_Num"].nil?)
      pi = PatientIdentifier.new
      pi.patient_id = patient.patient_id
      pi.identifier = mpr["Legacy_Pat_Num"]
      pi.identifier_type = legacy_ped.patient_identifier_type_id
      pi.location_id = location_id
      pi.creator = user.user_id
      pi.date_created = patient.date_created
      pi.save
    else
      puts "   Legacy_Pat_Num is null (" + @@count.to_s + ")"
    end

    unless (mpr["Legacy_Pat_Num"].nil?)
      pi = PatientIdentifier.new
      pi.patient_id = patient.patient_id
      pi.identifier = mpr["Birth_TA"]
      pi.identifier_type = birth_ta.patient_identifier_type_id
      pi.location_id = location_id
      pi.creator = user.user_id
      pi.date_created = patient.date_created
      pi.save
    else
      puts "   Birth_TA is null (" + @@count.to_s + ")"
    end


    # Add in first and last name
    pn = PatientName.new
    pn.patient_id = patient.patient_id
    pn.given_name = mpr["First_Name"]
    pn.family_name = mpr["Last_Name"]
    pn.creator = user.user_id
    pn.date_created = patient.date_created
    pn.save

    # Add the address
    pa = PatientAddress.new
    pa.patient_id = patient.patient_id
    pa.address1 = mpr["Address"]
    pa.creator = user.user_id
    pa.date_created = patient.date_created
    pa.save

  end


  # Convert a 12-JUL-2005 date to a Ruby DateTime
  # Yes, I know this function is longer than it could be, but it also faster
  # Than a Hash by 50% in some cases, so it is what it is
  def self.convert_date(old_date, old_time)
    old_day = old_date.slice(0, 2)
    old_month = old_date.slice(3, 3)
    old_year = old_date.slice(7, 4)
    case old_month
      when "JAN"
        DateTime::parse(old_year + "-01-" + old_day + "T" + old_time)
      when "FEB"
        DateTime::parse(old_year + "-02-" + old_day + "T" + old_time)
      when "MAR"
        DateTime::parse(old_year + "-03-" + old_day + "T" + old_time)
      when "APR"
        DateTime::parse(old_year + "-04-" + old_day + "T" + old_time)
      when "MAY"
        DateTime::parse(old_year + "-05-" + old_day + "T" + old_time)
      when "JUN"
        DateTime::parse(old_year + "-06-" + old_day + "T" + old_time)
      when "JUL"
        DateTime::parse(old_year + "-07-" + old_day + "T" + old_time)
      when "AUG"
        DateTime::parse(old_year + "-08-" + old_day + "T" + old_time)
      when "SEP"
        DateTime::parse(old_year + "-09-" + old_day + "T" + old_time)
      when "OCT"
        DateTime::parse(old_year + "-10-" + old_day + "T" + old_time)
      when "NOV"
        DateTime::parse(old_year + "-11-" + old_day + "T" + old_time)
      when "DEC"
        DateTime::parse(old_year + "-12-" + old_day + "T" + old_time)
      else
        raise "Invalid date for conversion: " + old_date + "T" + old_time
    end
  end


end