class CalculateLeaderboardService

  def initialize(challenge_round_id:)
    @round = ChallengeRound.find(challenge_round_id)
    @order_by = get_order_by
    @base_order_by = get_base_order_by
    @conn = ActiveRecord::Base.connection
  end

  def call
    if @round.submissions.blank?
      purge_leaderboard
      return true
    end

    ActiveRecord::Base.transaction do
      truncate_scores
      purge_leaderboard
      create_leaderboard(leaderboard_type: 'leaderboard')
      create_leaderboard(leaderboard_type: 'ongoing')
      create_leaderboard(leaderboard_type: 'previous')
      create_leaderboard(leaderboard_type: 'previous_ongoing')
      update_leaderboard_rankings(
        leaderboard: 'leaderboard',
        prev: 'previous')
      update_leaderboard_rankings(
        leaderboard: 'ongoing',
        prev: 'previous_ongoing')
      insert_baseline_rows(leaderboard_type: 'leaderboard')
      insert_baseline_rows(leaderboard_type: 'ongoing')
      set_leaderboard_sequences(leaderboard_type: 'leaderboard')
      set_leaderboard_sequences(leaderboard_type: 'ongoing')
    end
    return true
  end

  def truncate_scores
    sql = %Q[
      update submissions
      set
        score_display = round(score::numeric,#{@round.score_precision}),
        score_secondary_display = round(score_secondary::numeric,#{@round.score_secondary_precision})
      where challenge_round_id = #{@round.id}
    ]
    @conn.execute sql
  end

  def window_border_dttm
    most_recent = Submission
      .where(challenge_round_id: @round.id)
      .order(created_at: :desc)
      .limit(1)
      .first
    window_border = most_recent.created_at - @round.ranking_window.hours
    return "'#{window_border.to_s(:db)}'"
  end

  def get_order_by
    challenge = @round.challenge
    if (challenge.secondary_sort_order_cd.blank? || challenge.secondary_sort_order_cd == 'not_used')
        return "score_display #{sort_map(challenge.primary_sort_order_cd)}"
    else
      return "score_display #{sort_map(challenge.primary_sort_order_cd)}, score_secondary_display #{sort_map(challenge.secondary_sort_order_cd)}"
    end
  end

  # TODO refactor this out
  def get_base_order_by
    challenge = @round.challenge
    if (challenge.secondary_sort_order_cd.blank? || challenge.secondary_sort_order_cd == 'not_used')
        return "score #{sort_map(challenge.primary_sort_order_cd)}"
    else
      return "score #{sort_map(challenge.primary_sort_order_cd)}, score_secondary #{sort_map(challenge.secondary_sort_order_cd)}"
    end
  end
  def sort_map(sort_field)
    case sort_field
    when 'ascending'
      return 'asc'
    when 'descending'
      return 'desc'
    else
      return nil
    end
  end

  def purge_leaderboard
    ActiveRecord::Base.connection.execute "delete from base_leaderboards where challenge_round_id = #{@round.id};"
  end

  def leaderboard_params(leaderboard_type:)
    case leaderboard_type
    when 'leaderboard'
      post_challenge = '(FALSE)'
      cuttoff_dttm = 'current_timestamp'
    when 'ongoing'
      post_challenge = '(TRUE,FALSE)'
      cuttoff_dttm = 'current_timestamp'
    when 'previous'
      post_challenge = '(FALSE)'
      cuttoff_dttm = window_border_dttm
    when 'previous_ongoing'
      post_challenge = '(TRUE,FALSE)'
      cuttoff_dttm = window_border_dttm
    end
    return [post_challenge,cuttoff_dttm]
  end

  def create_leaderboard(leaderboard_type:)
    post_challenge, cuttoff_dttm = leaderboard_params(leaderboard_type: leaderboard_type)

    sql = %Q[
      INSERT INTO base_leaderboards (
        id,
        challenge_id,
        challenge_round_id,
        participant_id,
        submission_id,
        seq,
        row_num,
        previous_row_num,
        slug,
        name,
        entries,
        score,
        score_secondary,
        media_large,
        media_thumbnail,
        media_content_type,
        description,
        description_markdown,
        leaderboard_type_cd,
        post_challenge,
        refreshed_at,
        created_at,
        updated_at
      )
      SELECT
        nextval('base_leaderboards_id_seq'::regclass),
        l.challenge_id,
        l.challenge_round_id,
        l.participant_id,
        l.id,
        0 as SEQ,
        ROW_NUMBER() OVER (
          PARTITION by l.challenge_id,
                       l.challenge_round_id
          ORDER BY #{@order_by} ) AS ROW_NUM,
        0 as PREVIOUS_ROW_NUM,
        l.slug,
        l.name,
        l.entries,
        l.score_display,
        l.score_secondary_display,
        l.media_large,
        l.media_thumbnail,
        l.media_content_type,
        l.description,
        l.description_markdown,
        '#{leaderboard_type}',
        l.post_challenge,
        '#{DateTime.now.to_s(:db)}',
        l.created_at,
        l.updated_at
      FROM (SELECT
              row_number() OVER (
                PARTITION BY s.challenge_id,
                             s.challenge_round_id,
                             s.participant_id
                ORDER BY #{@order_by}) AS submission_ranking,
              s.id,
              s.challenge_id,
              s.challenge_round_id,
              s.participant_id,
              p.slug,
              p.name,
              cnt.entries,
              s.score_display,
              s.score_secondary_display,
              s.media_large,
              s.media_thumbnail,
              s.media_content_type,
              s.description,
              s.description_markdown,
              s.post_challenge,
              s.created_at,
              s.updated_at
            FROM submissions s,
                 challenges c,
                 participants p,
                  ( SELECT c_1.challenge_id,
                           c_1.challenge_round_id,
                           c_1.participant_id,
                           count(c_1.*) AS entries
                    FROM submissions c_1
                    WHERE c_1.challenge_round_id = #{@round.id}
                    AND c_1.post_challenge IN #{post_challenge}
                    AND c_1.created_at <= #{cuttoff_dttm}
                    AND c_1.baseline IS FALSE
                    GROUP BY c_1.challenge_id,
                             c_1.challenge_round_id,
                             c_1.participant_id) cnt
            WHERE s.challenge_round_id = #{@round.id}
            AND s.grading_status_cd = 'graded'
            AND s.post_challenge IN #{post_challenge}
            AND s.created_at <= #{cuttoff_dttm}
            AND p.id = s.participant_id
            AND s.challenge_id = c.id
            AND cnt.challenge_id = s.challenge_id
            AND cnt.challenge_round_id = s.challenge_round_id
            AND cnt.participant_id = s.participant_id
            AND s.baseline IS FALSE) l,
          challenges c
        WHERE l.submission_ranking = 1
        AND c.id = l.challenge_id
        AND c.id = #{@round.challenge_id}
    ]
    @conn.execute sql
  end

  def update_leaderboard_rankings(leaderboard:, prev:)
    sql = %Q[
      WITH lb AS (
        SELECT l.row_num,
               p.row_num AS prev_row_num,
               l.challenge_id,
               l.challenge_round_id,
               l.participant_id
        FROM base_leaderboards l,
             base_leaderboards p
        WHERE l.challenge_id = p.challenge_id
        AND l.challenge_round_id = p.challenge_round_id
        AND l.challenge_round_id = #{@round.id}
        AND l.participant_id = p.participant_id
        AND l.leaderboard_type_cd = '#{leaderboard}'
        AND p.leaderboard_type_cd = '#{prev}')
      UPDATE base_leaderboards
      SET previous_row_num = lb.prev_row_num
      FROM lb
      WHERE base_leaderboards.leaderboard_type_cd = '#{leaderboard}'
      AND base_leaderboards.challenge_id = lb.challenge_id
      AND base_leaderboards.challenge_round_id = lb.challenge_round_id
      AND base_leaderboards.challenge_round_id = #{@round.id}
      AND base_leaderboards.participant_id = lb.participant_id
    ]
    @conn.execute sql
  end

  def insert_baseline_rows(leaderboard_type:)
    post_challenge, cuttoff_dttm = leaderboard_params(leaderboard_type: leaderboard_type)
    sql = %Q[
      INSERT INTO base_leaderboards (
        id,
        challenge_id,
        challenge_round_id,
        participant_id,
        submission_id,
        seq,
        row_num,
        previous_row_num,
        slug,
        name,
        entries,
        score,
        score_secondary,
        media_large,
        media_thumbnail,
        media_content_type,
        description,
        description_markdown,
        leaderboard_type_cd,
        post_challenge,
        baseline,
        baseline_comment,
        refreshed_at,
        created_at,
        updated_at
      )
      SELECT
        nextval('base_leaderboards_id_seq'::regclass),
        s.challenge_id,
        s.challenge_round_id,
        s.participant_id,
        s.id as submission_id,
        0 as seq,
        0 as row_num,
        0 as previous_row_num,
        NULL as slug,
        NULL as name,
        0 as entries,
        s.score,
        s.score_secondary,
        s.media_large,
        s.media_thumbnail,
        s.media_content_type,
        s.description,
        s.description_markdown,
        '#{leaderboard_type}',
        s.post_challenge,
        s.baseline,
        s.baseline_comment,
        '#{DateTime.now.to_s(:db)}',
        s.created_at,
        s.updated_at
      FROM submissions s
      WHERE s.challenge_round_id = #{@round.id}
      AND s.grading_status_cd = 'graded'
      AND s.post_challenge IN #{post_challenge}
      AND s.created_at <= #{cuttoff_dttm}
      AND s.baseline IS TRUE
    ]
    @conn.execute sql
  end

  def set_leaderboard_sequences(leaderboard_type:)
    post_challenge, cuttoff_dttm = leaderboard_params(leaderboard_type: leaderboard_type)
    sql = %Q[
        WITH lb AS (
        SELECT
          l.id,
          l.submission_id,
          l.row_num,
          ROW_NUMBER() OVER (
            PARTITION by l.challenge_id,
                         l.challenge_round_id,
                         l.leaderboard_type_cd
            ORDER BY #{@base_order_by},l.row_num asc) AS SEQ
        FROM base_leaderboards l
        WHERE l.challenge_round_id = #{@round.id})
      UPDATE base_leaderboards
      SET seq = lb.seq
      FROM lb
      WHERE base_leaderboards.id = lb.id
    ]
    @conn.execute sql
  end

end
