Find Phone Numbers in Dirty Database

phone_finder.rb

# SQL_PHONE_NORMALIZER Example:
# Lead.find_by_sql(PhoneFinder::SQL_PHONE_NORMALIZER.call(table: 'leads', column_name: 'phone', phone: '(555) 760-2012'))
# User.find_by_sql(PhoneFinder::SQL_PHONE_NORMALIZER.call(table: 'users', column_name: 'phone', phone: '555-223-4027'))
#
# AREL_PHONE_NORMALIZER Example:
# PhoneFinder::AREL_PHONE_NORMALIZER.call(rel: Lead.where(email: '[email protected]'), table: 'leads', column_name: 'phone', phone: '(555) 760-2012')
# PhoneFinder::AREL_PHONE_NORMALIZER.call(rel: User.where(email: '[email protected]'), table: 'users', column_name: 'phone', phone: '555-223-4027')
#
# See: https://coderbits.com/posts/pCl7og
module PhoneFinder

  def self.phone_variations(phone)
    phone_just_numbers = phone.gsub(/[^0-9]/, '')
    phone_with_dashes = ActiveRecord::Base.sanitize(ActionController::Base.helpers.number_to_phone(phone_just_numbers))
    phone_with_area = ActiveRecord::Base.sanitize(ActionController::Base.helpers.number_to_phone(phone_just_numbers, area_code: true))
    phone_just_numbers = ActiveRecord::Base.sanitize(phone_just_numbers)
    return phone_just_numbers, phone_with_dashes, phone_with_area
  end

  def self.where_clause(table:, column_name:, phone:)
    phone_just_numbers, phone_with_dashes, phone_with_area = PhoneFinder.phone_variations(phone)
    <<eos
    ( substring("#{table}"."#{column_name}" from '.?[[:digit:]]{3}.{0,2}[[:digit:]]{3}-?[[:digit:]]{4}') )
      IN (#{phone_just_numbers},#{phone_with_dashes},#{phone_with_area})
eos
  end

  SQL_PHONE_NORMALIZER = -> (table:, column_name:, phone:) {
    sql = <<eos
      SELECT * from #{table}
      WHERE #{PhoneFinder.where_clause(table: table, column_name: column_name, phone: phone)}
eos
    sql
  }

  AREL_PHONE_NORMALIZER = -> (rel:, table:, column_name:, phone:) {
    rel.where(PhoneFinder.where_clause(table: table, column_name: column_name, phone: phone))
  }

end