
module JamRuby
  class ScoreHistory < ActiveRecord::Base

    self.table_name = 'score_histories'

    belongs_to :from_user, class_name: 'JamRuby::User', foreign_key: 'from_user_id'
    belongs_to :to_user, class_name: 'JamRuby::User', foreign_key: 'to_user_id'

    def self.migrate_scores

      generic_state = GenericState.singleton
      last_imported_at = generic_state.score_history_last_imported_at
      pretty_close_to_now = Time.now - 5.seconds # give the db some time to have entered all scores

      result = connection.execute(
          "INSERT INTO score_histories
            (from_client_id, from_user_id, from_latency_tester_id, from_addr, from_locidispid, from_isp, from_country, from_region, from_city, from_postal, from_latitude, from_longitude,
             to_client_id, to_user_id, to_latency_tester_id, to_addr, to_locidispid, to_isp, to_country, to_region, to_city, to_postal, to_latitude, to_longitude,
             score, score_dt, scoring_data)
          SELECT
            s.anodeid     AS from_client_id,
            s.auserid     AS from_user_id,
            s.alatencytestid AS from_latency_tester_id,
            s.aaddr       AS from_addr,
            s.alocidispid AS from_locidispid,
            x.company     AS from_isp,
            a.countrycode AS from_country,
            a.region      AS from_region,
            a.city        AS from_city,
            a.postalcode  AS from_postal,
            a.latitude    AS from_latitude,
            a.longitude   AS from_longitude,
            s.bnodeid     AS to_client_id,
            s.buserid     AS to_user_id,
            s.blatencytestid AS to_latency_tester_id,
            s.baddr       AS to_addr,
            s.blocidispid AS to_locidispid,
            y.company     AS to_isp,
            b.countrycode AS to_country,
            b.region      AS to_region,
            b.city        AS to_city,
            b.postalcode  AS to_postal,
            b.latitude    AS to_latitude,
            b.longitude   AS to_longitude,
            s.score       AS score,
            s.score_dt    AS created_at,
            s.scoring_data AS scoring_data
          FROM scores s
          JOIN geoiplocations a ON a.locid = (s.alocidispid / 1000000)
          JOIN geoiplocations b ON b.locid = (s.blocidispid / 1000000)
          JOIN jamcompany x     ON x.coid = (s.alocidispid % 1000000)
          JOIN jamcompany y     ON y.coid = (s.blocidispid % 1000000)
          WHERE s.scorer = 0
            AND s.created_at BETWEEN TIMESTAMP '#{last_imported_at}' AND TIMESTAMP '#{pretty_close_to_now}'")

      result.check

      generic_state.score_history_last_imported_at = pretty_close_to_now
      generic_state.save!
      return result.cmd_tuples, pretty_close_to_now
    end
  end
end
