# Script to create and add categories for all headquarters and offices
locations = Office.joins(:country).joins(:region).select('btrim(offices.city) as city, btrim(regions.name) as state_name, btrim(countries.name) as country_name, offices.region_id, offices.country_id').group('city, state_name, country_name, offices.region_id, offices.country_id')
locations.each{|loc|
  puts "'#{loc.city}', '#{loc.state_name}', '#{loc.country_name}''"
  city_cat = Category.find_by(:tag => loc.city.downcase.strip, :tag_type => "LocationTag") unless loc.city.blank?
  state_cat = Category.find_by(:tag => loc.state_name.downcase.strip, :tag_type => "LocationTag") unless loc.state_name.blank?
  country_cat = Category.find_by(:tag => loc.country_name.downcase.strip, :tag_type => "LocationTag") unless loc.country_name.blank?

  # Add all companies based on matching offices
  offices = Office.where(city: loc.city, region_id: loc.region_id, country_id: loc.country_id).select(:company_id).distinct
  offices.each{|off|
    next if off.company_id.blank?
    company = Company.find_by(id: off.company_id)
    next if company.blank? || company.id.blank?

    unless city_cat.blank? || loc.city.blank?
      if CategoryCompany.where("category_id = #{city_cat.id} AND company_id = #{company.id}").blank?
        CategoryCompany.create({:category_id => city_cat.id, :company_id => company.id})
      end
    end
    unless state_cat.id.blank? || loc.state_name.blank? || (!city_cat.blank? && !state_cat.blank? && city_cat.id == state_cat.id)
      if CategoryCompany.where("category_id = #{state_cat.id} AND company_id = #{company.id}").blank?
        CategoryCompany.create({:category_id => state_cat.id, :company_id => company.id})
      end
    end
    unless country_cat.blank? || loc.country_name.blank?
      if CategoryCompany.where("category_id = #{country_cat.id} AND company_id = #{company.id}").blank?
        CategoryCompany.create({:category_id => country_cat.id, :company_id => company.id})
      end
    end
  }
}