module EducodeSales
  class UserStatService

    attr_reader :o_type, :school_ids
    def initialize
      @o_type = EducodeSales::Common.find_by(extras: EducodeSales::Common::OTYPE)&.id

      part_a_ids = EducodeSales::CustomerFollow.all.pluck(:school_id)
      part_b_ids = EducodeSales::Business.pluck(:school_id)
      @school_ids = (part_a_ids + part_b_ids + EducodeSales::CustomerAdd.all.pluck(:school_id)).uniq
    end

    def table_1
      # 公有云
      user_years_1 = public_user
      # 私有云
      user_years_2 = {} #year_users(2)
      school_years_1 = public_school
      school_years_2 = {}# school_users(2)
      department_years_1 = public_department
      department_years_2 = {} #department_users(2)

      years = {}
      (2018..Time.current.year).each do |d|
        years[d] = {
          'year' => d,
          'user_1' => (user_years_1[d.to_s] || 0) + (years[d-1] && years[d-1]['user_1']).to_i,
          'user_2' => user_years_2[d.to_s] || 0,
          'school_1' => (school_years_1[d.to_s] || 0) + (years[d-1] && years[d-1]['school_1']).to_i,
          'school_2' => school_years_2[d.to_s] || 0,
          'department_1' => (department_years_1[d.to_s] || 0) + (years[d-1] && years[d-1]['department_1']).to_i,
          'department_2' => department_years_2[d.to_s] || 0
        }
      end

      years.values
    end

    def table_2(clazz)
      users = {'1-9' => [0, 0, 0], '10-99' => [0, 0, 0], '100-999' => [0, 0, 0], '1000-9999' => [0, 0, 0], '10000-49999' => [0, 0, 0] }
      signed_users = {'1-9' => [0, 0, 0], '10-99' => [0, 0, 0], '100-999' => [0, 0, 0], '1000-9999' => [0, 0, 0], '10000-49999' => [0, 0, 0] }
      schools(clazz).each do |d|
        if d[1] <= 9
          users['1-9'][0] += 1
         elsif d[1] <= 99
           users['10-99'][0] += 1
         elsif d[1] <= 999
           users['100-999'][0] += 1
         elsif d[1] <= 9999
           users['1000-9999'][0] += 1
         elsif d[1] <= 49999
           users['10000-49999'][0] += 1
         end
      end

      departments(clazz).each do |d|
        if d[1] <= 9
         users['1-9'][1] += 1
        elsif d[1] <= 99
          users['10-99'][1] += 1
        elsif d[1] <= 999
          users['100-999'][1] += 1
        elsif d[1] <= 9999
          users['1000-9999'][1] += 1
        elsif d[1] <= 49999
          users['10000-49999'][1] += 1
        end
      end

      signed_schools(clazz).each do |d|
        if d[1] <= 9
          signed_users['1-9'][0] += 1
         elsif d[1] <= 99
          signed_users['10-99'][0] += 1
         elsif d[1] <= 999
          signed_users['100-999'][0] += 1
         elsif d[1] <= 9999
          signed_users['1000-9999'][0] += 1
         elsif d[1] <= 49999
          signed_users['10000-49999'][0] += 1
         end
      end

      signed_departments(clazz).each do |d|
        if d[1] <= 9
          signed_users['1-9'][1] += 1
        elsif d[1] <= 99
          signed_users['10-99'][1] += 1
        elsif d[1] <= 999
          signed_users['100-999'][1] += 1
        elsif d[1] <= 9999
          signed_users['1000-9999'][1] += 1
        elsif d[1] <= 49999
          signed_users['10000-49999'][1] += 1
        end
      end


      ['1-9', '10-99', '100-999', '1000-9999', '10000-49999'].map do |d|
        {
          'num' => d,
          'schools' => users[d][0],
          'departments' => users[d][1],
          'majors' => 0,
          'signed_schools' => signed_users[d][0],
          'signed_departments' => signed_users[d][1],
          'signed_majors' => 0,
        }
      end
    end


    def public_user
      UserExtension.where(school_id: @school_ids).
      group("DATE_FORMAT(user_extensions.created_at, '%Y')").
      select("count(*) as count, DATE_FORMAT(user_extensions.created_at, '%Y') as year").count("*")
    end

    def public_school
      School.where(id: @school_ids).
      group("DATE_FORMAT(schools.created_at, '%Y')").
      select("count(*) as count, DATE_FORMAT(schools.created_at, '%Y') as year").count("*")
    end

    def public_department
      Department.where(school_id: @school_ids).
      group("DATE_FORMAT(departments.created_at, '%Y')").
      select("count(*) as count, DATE_FORMAT(departments.created_at, '%Y') as year").count("*")
    end

    def year_users(clazz)
      EducodeSales::Business.joins(:last_follow_up).
      joins("join user_extensions ON user_extensions.department_id = educode_sales_businesses.department_id").
      where("educode_sales_follow_ups.o_business_deployment = #{clazz}").
      where(school_id: @school_ids).
      group("DATE_FORMAT(user_extensions.created_at, '%Y')").
      select("count(*) as count, DATE_FORMAT(user_extensions.created_at, '%Y') as year").
      distinct.count("user_extensions.id")
    end

    def school_users(clazz)
      EducodeSales::Business.joins(:last_follow_up).
      joins("join schools ON schools.id = educode_sales_businesses.school_id").
      where("educode_sales_follow_ups.o_business_deployment = #{clazz}").
      where(school_id: @school_ids).
      group("DATE_FORMAT(schools.created_at, '%Y')").
      select("count(*) as count, DATE_FORMAT(schools.created_at, '%Y') as year").
      distinct.count("school_id")
    end

    def department_users(clazz)
      EducodeSales::Business.joins(:last_follow_up).
      joins("join departments ON departments.id = educode_sales_businesses.department_id").
      where("educode_sales_follow_ups.o_business_deployment = #{clazz}").
      where(school_id: @school_ids).
      group("DATE_FORMAT(departments.created_at, '%Y')").
      select("count(*) as count, DATE_FORMAT(departments.created_at, '%Y') as year").
      distinct.count("school_id")
    end

    def schools(clazz)
      School.joins("JOIN user_extensions ON schools.id = user_extensions.school_id AND user_extensions.identity = #{clazz}").
      where(id: @school_ids).group(:id).count
    end

    def departments(clazz)
      School.joins("JOIN user_extensions ON schools.id = user_extensions.school_id AND user_extensions.identity = #{clazz}").
      where(id: @school_ids).group("user_extensions.department_id").count
    end

    def signed_schools(clazz)
      School.joins("JOIN user_extensions on schools.id = user_extensions.school_id AND user_extensions.identity = #{clazz}
        JOIN educode_sales_businesses ON educode_sales_businesses.clazz_id = #{@o_type} AND educode_sales_businesses.school_id = schools.id
      ").where(id: @school_ids).group(:id).distinct.count("user_extensions.id")
    end

    def signed_departments(clazz)
      School.joins("JOIN user_extensions on schools.id = user_extensions.school_id AND user_extensions.identity = #{clazz}
        JOIN educode_sales_businesses ON educode_sales_businesses.clazz_id = #{@o_type} AND educode_sales_businesses.school_id = schools.id
      ").where(id: @school_ids).where("user_extensions.department_id > 0").
      group("user_extensions.department_id").
      distinct.count("user_extensions.id")
    end
  end

end