# Script to create and add categories for all headquarters and offices
locations = Office.joins(:iso_country).joins(:iso_region).select('btrim(offices.city) as city, btrim(iso_regions.name) as state, btrim(iso_countries.name) as country, iso_countries.region,iso_countries.sub_region, offices.iso_region_id, offices.iso_country_id').group('city, state, country, region, sub_region, offices.iso_region_id, offices.iso_country_id')
locations.each{|loc|
  puts "'#{loc.city}', '#{loc.state}', '#{loc.country}', '#{loc.region}', '#{loc.sub_region}'"
  city_cat = Category.find_by(:tag => loc.city.downcase, :tag_type => "LocationTag") unless loc.city.blank?
  state_cat = Category.find_by(:tag => loc.state.downcase, :tag_type => "LocationTag") unless loc.state.blank?
  country_cat = Category.find_by(:tag => loc.country.downcase, :tag_type => "LocationTag") unless loc.country.blank?
  region_cat = Category.find_by(:tag => loc.region.downcase, :tag_type => "LocationTag") unless loc.region.blank?
  sub_region_cat = Category.find_by(:tag => loc.sub_region.downcase, :tag_type => "LocationTag") unless loc.sub_region.blank?

  # Add all companies based on matching offices
  offices = Office.where(city: loc.city, iso_region_id: loc.iso_region_id, iso_country_id: loc.iso_country_id).select(:company_id).distinct
  offices.each{|off|
    company = Company.find(off.company_id)
    company.clean_data_bypass = true
    company.denormalize_bypass = true
    company.reindex_bypass = true

    unless city_cat.blank? || loc.city.blank?
      if CategoryCompany.where("category_id = #{city_cat.id} AND company_id = #{company.id}").blank?
        city_cat.companies << company
        city_cat.companies_count_bypass = true
        city_cat.save
      end
    end
    unless state_cat.id.blank? || loc.state.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?
        state_cat.companies << company
        state_cat.companies_count_bypass = true
        state_cat.save
      end
    end
    unless country_cat.blank? || loc.country.blank?
      if CategoryCompany.where("category_id = #{country_cat.id} AND company_id = #{company.id}").blank?
        country_cat.companies << company
        country_cat.companies_count_bypass = true
        country_cat.save
      end
    end
    unless region_cat.blank? || loc.region.blank?
      if CategoryCompany.where("category_id = #{region_cat.id} AND company_id = #{company.id}").blank?
        region_cat.companies << company
        region_cat.companies_count_bypass = true
        region_cat.save
      end
    end
    unless sub_region_cat.blank? || loc.sub_region.blank?
      if CategoryCompany.where("category_id = #{sub_region_cat.id} AND company_id = #{company.id}").blank?
        sub_region_cat.companies << company
        sub_region_cat.companies_count_bypass = true
        sub_region_cat.save
      end
    end
  }
}