patch_treatments.rb

#PASS #1
# treatment_id that don't exist
treatment_ids = Appointment.includes(:treatment).where("treatments.id IS NULL").map {|a| a.treatment_id }.uniq.reject {|t| t.nil?}

# rebuild with the most common treatment length
treat_staff_duration = treatment_ids.map {|t| Appointment.where(:treatment_id => t).map {|a| [a.treatment_id, a.staff_member_id, a.end_at-a.start_at]}.max {|a| a.last } }

# treatments for the same practitioner that have the same length
matchable_replacements = treat_staff_duration.map {|tuple| [*tuple, StaffMember.find(tuple[1]).treatments.detect {|t| t.duration == tuple.last && t.name != "Break" }] }.reject {|t| t.last.nil? }

# perform the replacement
matchable_replacements.each {|tuple| ap Appointment.where(:treatment_id => tuple[0]).where(:staff_member_id => tuple[1]).update_all(:treatment_id => tuple.last.id) }

# reduces from 21 to 9

# PASSS #2
# treatment_id that don't exist
treatment_ids = Appointment.includes(:treatment).where("treatments.id IS NULL").map {|a| a.treatment_id }.uniq.reject {|t| t.nil?}

# rebuild with the most common treatment length
treat_staff_duration = treatment_ids.map {|t| Appointment.where(:treatment_id => t).map {|a| [a.treatment_id, a.staff_member_id, a.end_at-a.start_at]}.max {|a| a.last } }

# treatments for the same practitioner that are not a break as we cannot match the length
matchable_replacements = treat_staff_duration.map {|tuple| [*tuple, StaffMember.find(tuple[1]).treatments.detect {|t| t.name != "Break"} ] }.reject {|t| t.last.nil? }

# perform the replacement
matchable_replacements.each {|tuple| ap Appointment.where(:treatment_id => tuple[0]).where(:staff_member_id => tuple[1]).update_all(:treatment_id => tuple.last.id) }

# reduces from 9 to 2
# PASS #3
treatment_ids = Appointment.includes(:treatment).where("treatments.id IS NULL").map {|a| a.treatment_id }.uniq.reject {|t| t.nil?}
treat_staff = treatment_ids.map {|t| Appointment.where(:treatment_id => t).map {|a| [a.treatment_id, a.staff_member_id]}.max {|a| a.last } }

# this staff member has no treatments
treat_staff.map {|treatment, staff| StaffMember.find(staff).treatments }