require 'csv'

class ReportController < ApplicationController

  before_action :check_valid_login

  before_action :admin_authorization, only: [:login_stat,:submission, :submission_query, 
                                             :login, :login_detail_query, :login_summary_query,
                                             :stuck, :cheat_report, :cheat_scrutinize, :show_max_score, :current_score]

  before_action(only: [:problem_hof]) { |c|
    return false unless check_valid_login

    admin_authorization unless GraderConfiguration["right.user_view_submission"]
  }

  def max_score
  end

  def current_score
    @problems = Problem.available_problems
    if params[:group_id] && params[:users] == 'group'
      @group = Group.find(params[:group_id])
      @users = @group.users.where(enabled: true)
    else
      @users = User.includes(:contests).includes(:contest_stat).where(enabled: true)
    end
    @scorearray = calculate_max_score(@problems, @users,0,0,true)

    #rencer accordingly
    if params[:button] == 'download' then
      csv = gen_csv_from_scorearray(@scorearray,@problems)
      send_data csv, filename: 'max_score.csv'
    else
      #render template: 'user_admin/user_stat'
      render 'current_score'
    end
  end

  def show_max_score
    #process parameters
    #problems
    @problems = []
    if params[:problem_id]
      params[:problem_id].each do |id|
        next unless id.strip != ""
        pid = Problem.find_by_id(id.to_i)
        @problems << pid if pid
      end
    end

    #users
    @users = if params[:users] == "group" then 
               Group.find(params[:group_id]).users.all
             elsif params[:users] == 'enabled'
               User.includes(:contests).includes(:contest_stat).where(enabled: true)
             else
               User.includes(:contests).includes(:contest_stat)
             end

    #set up range from param
    @since_id = params.fetch(:from_id, 0).to_i
    @until_id = params.fetch(:to_id, 0).to_i
    @since_id = nil if @since_id == 0
    @until_id = nil if @until_id == 0

    #calculate the routine
    @scorearray = calculate_max_score(@problems, @users, @since_id, @until_id)

    #rencer accordingly
    if params[:button] == 'download' then
      csv = gen_csv_from_scorearray(@scorearray,@problems)
      send_data csv, filename: 'max_score.csv'
    else
      #render template: 'user_admin/user_stat'
      render 'max_score'
    end

  end

  def score
    if params[:commit] == 'download csv'
      @problems = Problem.all
    else
      @problems = Problem.available_problems
    end
    @users = User.includes(:contests, :contest_stat).where(enabled: true) 
    @scorearray = Array.new
    @users.each do |u|
      ustat = Array.new
      ustat[0] = u
      @problems.each do |p|
        sub = Submission.find_last_by_user_and_problem(u.id,p.id)
        if (sub!=nil) and (sub.points!=nil) and p and p.full_score
          ustat << [(sub.points.to_f*100/p.full_score).round, (sub.points>=p.full_score)]
        else
          ustat << [0,false]
        end
      end
      @scorearray << ustat
    end
    if params[:commit] == 'download csv' then
      csv = gen_csv_from_scorearray(@scorearray,@problems)
      send_data csv, filename: 'last_score.csv'
    else
      render template: 'user_admin/user_stat'
    end

  end

  def login
  end

  def login_summary_query
    @users = Array.new

    date_and_time = '%Y-%m-%d %H:%M'
    begin
      md = params[:since_datetime].match(/(\d+)-(\d+)-(\d+) (\d+):(\d+)/)
      @since_time = Time.zone.local(md[1].to_i,md[2].to_i,md[3].to_i,md[4].to_i,md[5].to_i)
    rescue
      @since_time = DateTime.new(1000,1,1)
    end
    begin
      md = params[:until_datetime].match(/(\d+)-(\d+)-(\d+) (\d+):(\d+)/)
      @until_time = Time.zone.local(md[1].to_i,md[2].to_i,md[3].to_i,md[4].to_i,md[5].to_i)
    rescue
      @until_time = DateTime.new(3000,1,1)
    end

    record = User
      .left_outer_joins(:logins).group('users.id')
      .where("logins.created_at >= ? AND logins.created_at <= ?",@since_time, @until_time)
    case params[:users]
    when 'enabled'
      record = record.where(enabled: true)
    when 'group'
      record = record.joins(:groups).where(groups: {id: params[:groups]}) if params[:groups]
    end

    record = record.pluck("users.id,users.login,users.full_name,count(logins.created_at),min(logins.created_at),max(logins.created_at)")
    record.each do |user|
      x = Login.where("user_id = ? AND created_at >= ? AND created_at <= ?",
                                      user[0],@since_time,@until_time)
        .pluck(:ip_address).uniq
      @users << { id: user[0],
                   login: user[1],
                   full_name: user[2],
                   count: user[3],
                   min: user[4],
                   max: user[5],
                   ip: x
                 }
    end
  end

  def login_detail_query
    @logins = Array.new

    date_and_time = '%Y-%m-%d %H:%M'
    begin
      md = params[:since_datetime].match(/(\d+)-(\d+)-(\d+) (\d+):(\d+)/)
      @since_time = Time.zone.local(md[1].to_i,md[2].to_i,md[3].to_i,md[4].to_i,md[5].to_i)
    rescue
      @since_time = DateTime.new(1000,1,1)
    end
    begin
      md = params[:until_datetime].match(/(\d+)-(\d+)-(\d+) (\d+):(\d+)/)
      @until_time = Time.zone.local(md[1].to_i,md[2].to_i,md[3].to_i,md[4].to_i,md[5].to_i)
    rescue
      @until_time = DateTime.new(3000,1,1)
    end

    @logins = Login.includes(:user).where("logins.created_at >= ? AND logins.created_at <= ?",@since_time, @until_time)
    case params[:users]
    when 'enabled'
      @logins = @logins.where(users: {enabled: true})
    when 'group'
      @logins = @logins.joins(user: :groups).where(user: {groups: {id: params[:groups]}}) if params[:groups]
    end
  end

  def submission
  end

  def submission_query
    @submissions = Submission
      .includes(:problem).includes(:user).includes(:language)

    case params[:users]
    when 'enabled'
      @submissions = @submissions.where(users: {enabled: true})
    when 'group'
      @submissions = @submissions.joins(user: :groups).where(user: {groups: {id: params[:groups]}}) if params[:groups]
    end

    case params[:problems]
    when 'enabled'
      @submissions = @submissions.where(problems: {available: true})
    when 'selected'
      @submissions = @submissions.where(problem_id: params[:problem_id])
    end

    #set default
    params[:since_datetime] = Date.today.to_s if params[:since_datetime].blank?

    @submissions, @recordsTotal, @recordsFiltered = process_query_record( @submissions,
      global_search: ['user.login','user.full_name','problem.name','problem.full_name','points'],
      date_filter: 'submitted_at',
      date_param_since: 'since_datetime',
      date_param_until: 'until_datetime',
      hard_limit: 100_000
    )
  end

  def login
  end

  def problem_hof
    # gen problem list
    @user = User.find(session[:user_id])
    @problems = @user.available_problems

    # get selected problems or the default
    if params[:id]
      begin
        @problem = Problem.available.find(params[:id])
      rescue
        redirect_to action: :problem_hof
        flash[:notice] = 'Error: submissions for that problem are not viewable.'
        return
      end
    end

    return unless @problem

    @by_lang = {} #aggregrate by language

    range =65
    @histogram = { data: Array.new(range,0), summary: {} }
    @summary = {count: 0, solve: 0, attempt: 0}
    user = Hash.new(0)
    Submission.where(problem_id: @problem.id).find_each do |sub|
      #histogram
      d = (DateTime.now.in_time_zone - sub.submitted_at) / 24 / 60 / 60
      @histogram[:data][d.to_i] += 1 if d < range

      next unless sub.points
      @summary[:count] += 1
      user[sub.user_id] = [user[sub.user_id], (sub.points >= @problem.full_score) ? 1 : 0].max

      lang = Language.find_by_id(sub.language_id)
      next unless lang
      next unless sub.points >= @problem.full_score

      #initialize
      unless @by_lang.has_key?(lang.pretty_name)
        @by_lang[lang.pretty_name] = {
          runtime: { avail: false, value: 2**30-1 },
          memory: { avail: false, value: 2**30-1 },
          length: { avail: false, value: 2**30-1 },
          first: { avail: false, value: DateTime.new(3000,1,1) }
        }
      end

      if sub.max_runtime and sub.max_runtime < @by_lang[lang.pretty_name][:runtime][:value]
        @by_lang[lang.pretty_name][:runtime] = { avail: true, user_id: sub.user_id, value: sub.max_runtime, sub_id: sub.id }
      end

      if sub.peak_memory and sub.peak_memory < @by_lang[lang.pretty_name][:memory][:value]
        @by_lang[lang.pretty_name][:memory] = { avail: true, user_id: sub.user_id, value: sub.peak_memory, sub_id: sub.id }
      end

      if sub.submitted_at and sub.submitted_at < @by_lang[lang.pretty_name][:first][:value] and sub.user and
          !sub.user.admin?
        @by_lang[lang.pretty_name][:first] = { avail: true, user_id: sub.user_id, value: sub.submitted_at, sub_id: sub.id }
      end

      if @by_lang[lang.pretty_name][:length][:value] > sub.effective_code_length
        @by_lang[lang.pretty_name][:length] = { avail: true, user_id: sub.user_id, value: sub.effective_code_length, sub_id: sub.id }
      end
    end

    #process user_id
    @by_lang.each do |lang,prop|
      prop.each do |k,v|
        v[:user] = User.exists?(v[:user_id]) ? User.find(v[:user_id]).full_name : "(NULL)"
      end
    end

    #sum into best
    if @by_lang and @by_lang.first
      @best = @by_lang.first[1].clone
      @by_lang.each do |lang,prop|
        if @best[:runtime][:value] >= prop[:runtime][:value]
          @best[:runtime] = prop[:runtime]
          @best[:runtime][:lang] = lang
        end
        if @best[:memory][:value] >= prop[:memory][:value]
          @best[:memory] = prop[:memory]
          @best[:memory][:lang] = lang
        end
        if @best[:length][:value] >= prop[:length][:value]
          @best[:length] = prop[:length]
          @best[:length][:lang] = lang
        end
        if @best[:first][:value] >= prop[:first][:value]
          @best[:first] = prop[:first]
          @best[:first][:lang] = lang
        end
      end
    end

    @histogram[:summary][:max] = [@histogram[:data].max,1].max
    @summary[:attempt] = user.count
    user.each_value { |v| @summary[:solve] += 1 if v == 1 }
  end

  def stuck #report struggling user,problem
    # init
    user,problem = nil
    solve = true
    tries = 0
    @struggle = Array.new
    record = {}
    Submission.includes(:problem,:user).order(:problem_id,:user_id).find_each do |sub|
      next unless sub.problem and sub.user
      if user != sub.user_id or problem != sub.problem_id
        @struggle << { user: record[:user], problem: record[:problem], tries: tries } unless solve
        record = {user: sub.user, problem: sub.problem}
        user,problem = sub.user_id, sub.problem_id
        solve = false
        tries = 0
      end
      if sub.points >= sub.problem.full_score
        solve = true
      else
        tries += 1
      end
    end
    @struggle.sort!{|a,b| b[:tries] <=> a[:tries] }
    @struggle = @struggle[0..50]
  end


  def multiple_login
    #user with multiple IP
    raw = Submission.joins(:user).joins(:problem).where("problems.available != 0").group("login,ip_address").order(:login)
    last,count = 0,0
    first = 0
    @users = []
    raw.each do |r|
      if last != r.user.login
        count = 1
        last = r.user.login
        first = r
      else
        @users << first if count == 1
        @users << r
        count += 1
      end
    end

    #IP with multiple user
    raw = Submission.joins(:user).joins(:problem).where("problems.available != 0").group("login,ip_address").order(:ip_address)
    last,count = 0,0
    first = 0
    @ip = []
    raw.each do |r|
      if last != r.ip_address
        count = 1
        last = r.ip_address
        first = r
      else
        @ip << first if count == 1
        @ip << r
        count += 1
      end
    end
  end

  def cheat_report
    date_and_time = '%Y-%m-%d %H:%M'
    begin
      md = params[:since_datetime].match(/(\d+)-(\d+)-(\d+) (\d+):(\d+)/)
      @since_time = Time.zone.local(md[1].to_i,md[2].to_i,md[3].to_i,md[4].to_i,md[5].to_i)
    rescue
      @since_time = Time.zone.now.ago( 90.minutes)
    end
    begin
      md = params[:until_datetime].match(/(\d+)-(\d+)-(\d+) (\d+):(\d+)/)
      @until_time = Time.zone.local(md[1].to_i,md[2].to_i,md[3].to_i,md[4].to_i,md[5].to_i)
    rescue
      @until_time = Time.zone.now
    end

    #multi login
    @ml = Login.joins(:user).where("logins.created_at >= ? and logins.created_at <= ?",@since_time,@until_time).select('users.login,count(distinct ip_address) as count,users.full_name').group("users.id").having("count > 1")

    st = <<-SQL
  SELECT l2.*
    FROM logins l2 INNER JOIN
    (SELECT u.id,COUNT(DISTINCT ip_address) as count,u.login,u.full_name
      FROM logins l
      INNER JOIN users u ON l.user_id =  u.id
      WHERE l.created_at >= '#{@since_time.in_time_zone("UTC")}' and l.created_at <= '#{@until_time.in_time_zone("UTC")}'
      GROUP BY u.id
      HAVING count > 1
    ) ml ON l2.user_id = ml.id
    WHERE l2.created_at >= '#{@since_time.in_time_zone("UTC")}' and l2.created_at <= '#{@until_time.in_time_zone("UTC")}'
UNION
  SELECT l2.*
    FROM logins l2 INNER JOIN
    (SELECT l.ip_address,COUNT(DISTINCT u.id) as count
      FROM logins l
      INNER JOIN users u ON l.user_id =  u.id
      WHERE l.created_at >= '#{@since_time.in_time_zone("UTC")}' and l.created_at <= '#{@until_time.in_time_zone("UTC")}'
      GROUP BY l.ip_address
      HAVING count > 1
    ) ml on ml.ip_address = l2.ip_address
    INNER JOIN users u ON l2.user_id = u.id
    WHERE l2.created_at >= '#{@since_time.in_time_zone("UTC")}' and l2.created_at <= '#{@until_time.in_time_zone("UTC")}'
ORDER BY ip_address,created_at
              SQL
    @mld = Login.find_by_sql(st)

    st = <<-SQL
  SELECT s.id,s.user_id,s.ip_address,s.submitted_at,s.problem_id
    FROM submissions s INNER JOIN
    (SELECT u.id,COUNT(DISTINCT ip_address) as count,u.login,u.full_name
      FROM logins l
      INNER JOIN users u ON l.user_id =  u.id
      WHERE l.created_at >= ? and l.created_at <= ?
      GROUP BY u.id
      HAVING count > 1
    ) ml ON s.user_id = ml.id
    WHERE s.submitted_at >= ? and s.submitted_at <= ?
UNION
  SELECT s.id,s.user_id,s.ip_address,s.submitted_at,s.problem_id
    FROM submissions s INNER JOIN
    (SELECT l.ip_address,COUNT(DISTINCT u.id) as count
      FROM logins l
      INNER JOIN users u ON l.user_id =  u.id
      WHERE l.created_at >= ? and l.created_at <= ?
      GROUP BY l.ip_address
      HAVING count > 1
    ) ml on ml.ip_address = s.ip_address
    WHERE s.submitted_at >= ? and s.submitted_at <= ?
ORDER BY ip_address,submitted_at
            SQL
    @subs = Submission.joins(:problem).find_by_sql([st,@since_time,@until_time,
                                       @since_time,@until_time,
                                       @since_time,@until_time,
                                       @since_time,@until_time])

  end

  def cheat_scrutinize
    #convert date & time
    date_and_time = '%Y-%m-%d %H:%M'
    begin
      md = params[:since_datetime].match(/(\d+)-(\d+)-(\d+) (\d+):(\d+)/)
      @since_time = Time.zone.local(md[1].to_i,md[2].to_i,md[3].to_i,md[4].to_i,md[5].to_i)
    rescue
      @since_time = Time.zone.now.ago( 90.minutes)
    end
    begin
      md = params[:until_datetime].match(/(\d+)-(\d+)-(\d+) (\d+):(\d+)/)
      @until_time = Time.zone.local(md[1].to_i,md[2].to_i,md[3].to_i,md[4].to_i,md[5].to_i)
    rescue
      @until_time = Time.zone.now
    end

    #convert sid
    @sid = params[:SID].split(/[,\s]/) if params[:SID]
    unless @sid and @sid.size > 0
      return 
      redirect_to actoin: :cheat_scrutinize
      flash[:notice] = 'Please enter at least 1 student id'
    end
    mark = Array.new(@sid.size,'?')
    condition = "(u.login = " + mark.join(' OR u.login = ') + ')'

    @st = <<-SQL
  SELECT l.created_at as submitted_at ,-1 as id,u.login,u.full_name,l.ip_address,"" as problem_id,"" as points,l.user_id
  FROM logins l INNER JOIN users u on l.user_id  = u.id
  WHERE l.created_at >= ? AND l.created_at <= ? AND #{condition}
UNION
  SELECT s.submitted_at,s.id,u.login,u.full_name,s.ip_address,s.problem_id,s.points,s.user_id
  FROM submissions s INNER JOIN users u ON s.user_id = u.id
  WHERE s.submitted_at >= ? AND s.submitted_at <= ? AND #{condition}
ORDER BY submitted_at
  SQL
    
    p = [@st,@since_time,@until_time] + @sid + [@since_time,@until_time] + @sid
    @logs = Submission.joins(:problem).find_by_sql(p)





  end

  protected

  def calculate_max_score(problems, users,since_id,until_id, get_last_score = false)
    #scorearray[i] = user #i's user stat where i is the index (not id)
    scorearray = Array.new
    users.each do |u|
      ustat = Array.new
      ustat[0] = u
      problems.each do |p|
        unless get_last_score
          #get max score
          max_points = 0
          Submission.find_in_range_by_user_and_problem(u.id,p.id,since_id,until_id).each do |sub|
            max_points = sub.points if sub and sub.points and (sub.points > max_points)
          end
          ustat << [(max_points.to_f*100/p.full_score).round, (max_points>=p.full_score)]
        else
          #get latest score
          sub = Submission.find_last_by_user_and_problem(u.id,p.id)
          if (sub!=nil) and (sub.points!=nil) and p and p.full_score
            ustat << [(sub.points.to_f*100/p.full_score).round, (sub.points>=p.full_score)]
          else
            ustat << [0,false]
          end
        end
      end
      scorearray << ustat
    end
    return scorearray
  end

  def gen_csv_from_scorearray(scorearray,problem)
    CSV.generate do |csv|
      #add header
      header = ['User','Name', 'Activated?', 'Logged in', 'Contest']
      problem.each { |p| header << p.name }
      header += ['Total','Passed']
      csv << header
      #add data
      scorearray.each do |sc|
        total = num_passed = 0
        row = Array.new
        sc.each_index do |i|
          if i == 0
            row << sc[i].login
            row << sc[i].full_name
            row << sc[i].activated
            row << (sc[i].try(:contest_stat).try(:started_at)!=nil ? 'yes' : 'no')
            row << sc[i].contests.collect {|c| c.name}.join(', ')
          else
            row << sc[i][0]
            total += sc[i][0]
            num_passed += 1 if sc[i][1]
          end
        end
        row << total 
        row << num_passed
        csv << row
      end
    end
  end

end