# frozen_string_literal: true

#
# Copyright (C) 2012 - present Instructure, Inc.
#
# This file is part of Canvas.
#
# Canvas is free software: you can redistribute it and/or modify it under
# the terms of the GNU Affero General Public License as published by the Free
# Software Foundation, version 3 of the License.
#
# Canvas is distributed in the hope that it will be useful, but WITHOUT ANY
# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
# A PARTICULAR PURPOSE. See the GNU Affero General Public License for more
# details.
#
# You should have received a copy of the GNU Affero General Public License along
# with this program. If not, see <http://www.gnu.org/licenses/>.
#

require_relative "report_spec_helper"

describe "Default Account Reports" do
  include ReportSpecHelper

  def create_some_users_with_pseudonyms
    sis = @account.sis_batches.create
    @user1 = user_with_pseudonym(active_all: true,
                                 account: @account,
                                 name: "John St. Clair",
                                 sortable_name: "St. Clair,John",
                                 username: "john@stclair.com")
    @user.update(pronouns: "human/being")
    @user.pseudonym.update(sis_batch_id: sis.id, sis_user_id: "user_sis_id_01")
    @user2 = user_with_pseudonym(active_all: true,
                                 username: "micheal@michaelbolton.com",
                                 name: "Michael Bolton",
                                 account: @account)
    @user.pseudonym.sis_user_id = "user_sis_id_02"
    @user.update(pronouns: "she_her")
    @user.pseudonym.sis_batch_id = sis.id
    @user.pseudonym.save!
    @user3 = user_with_pseudonym(active_all: true,
                                 account: @account,
                                 name: "Rick Astley",
                                 sortable_name: "Astley,Rick",
                                 username: "rick@roll.com")
    @user.pseudonym.sis_user_id = "user_sis_id_03"
    @user.pseudonym.sis_batch_id = sis.id
    @user.pseudonym.save!
    @user4 = user_with_pseudonym(active_all: true,
                                 username: "jason@donovan.com",
                                 name: "Jason Donovan",
                                 account: @account)
    @user.pseudonym.sis_user_id = "user_sis_id_04"
    @user.pseudonym.save!
    @user5 = user_with_pseudonym(name: "James Brown", account: @account)
    @user.pseudonym.sis_user_id = "user_sis_id_05"
    @user.pseudonym.sis_batch_id = sis.id
    @user.pseudonym.save!
    @user5.destroy
    @user6 = user_with_pseudonym(active_all: true,
                                 username: "john@smith.com",
                                 name: "John Smith",
                                 sortable_name: "Smith,John",
                                 account: @account)
    @user7 = user_with_pseudonym(active_all: true,
                                 username: "jony@apple.com",
                                 name: "Jony Ive",
                                 account: @account)
    @user8 = user_with_pseudonym(active_all: true,
                                 username: "steve@apple.com",
                                 name: "Steve Jobs",
                                 account: @account)
    @user8.destroy
    @user9 = user_with_pseudonym(active_all: true,
                                 username: "tim@apple.com",
                                 name: "Tim Cook",
                                 account: @account)
    @user9.pseudonym.update!(workflow_state: "suspended")
  end

  def create_an_account
    @sis = @account.sis_batches.create
    @sub_account = Account.create(parent_account: @account, name: "English")
    @sub_account.sis_source_id = "sub1"
    @sub_account.sis_batch_id = @sis.id
    @sub_account.save!
  end

  def create_some_accounts
    create_an_account
    @sub_sub_account = Account.create(parent_account: @sub_account, name: "sESL")
    @sub_sub_account.sis_source_id = "subsub1"
    @sub_sub_account.sis_batch_id = @sis.id
    @sub_sub_account.save!
    @sub_account3 = Account.create(parent_account: @account, name: "math")
    @sub_account3.sis_source_id = "sub3"
    @sub_account3.sis_batch_id = @sis.id
    @sub_account3.save!
    @sub_account4 = Account.create(parent_account: @account, name: "deleted sis account")
    @sub_account4.sis_source_id = "sub4"
    @sub_account4.sis_batch_id = @sis.id
    @sub_account4.save!
    @sub_account4.destroy
    @sub_account5 = Account.create(parent_account: @account, name: "other")
    @sub_account6 = Account.create(parent_account: @account, name: "the deleted account")
    @sub_account6.destroy
  end

  def create_a_term
    @sis = @account.sis_batches.create
    @term1 = EnrollmentTerm.create(name: "Fall",
                                   start_at: 6.months.ago,
                                   end_at: 1.year.from_now)
    @term1.root_account = @account
    @term1.sis_source_id = "fall12"
    @term1.sis_batch_id = @sis.id
    @term1.save!
  end

  def create_some_group_categories
    create_some_courses
    @group_category1 = GroupCategory.create(
      name: "Test Group Category",
      account: @account
    )
    @group_category2 = GroupCategory.create(
      name: "Test Group Category2",
      account: @account
    )
    @group_category1.save!
    @group_category2.auto_leader = "first"
    @group_category2.group_limit = 2
    @group_category2.save!
    @group_category3 = GroupCategory.create(
      name: "Test Group Category Deleted",
      course: @course3
    )
    @group_category4 = GroupCategory.create(
      name: "Test Group Category Course",
      course: @course3
    )
    @account.group_categories << @group_category1
    @account.group_categories << @group_category2
    @account.group_categories << @group_category3
    @course3.group_categories << @group_category4
    @group_category3.destroy
    @account.save!
  end

  def create_some_terms
    create_a_term
    @term2 = EnrollmentTerm.create(name: "Winter",
                                   start_at: 3.weeks.ago,
                                   end_at: 2.years.from_now)
    @term2.root_account = @account
    @term2.sis_source_id = "winter13"
    @term2.sis_batch_id = @sis.id
    @term2.save!
    @term2.destroy
    @term3 = EnrollmentTerm.create(name: "Spring",
                                   start_at: 1.week.ago,
                                   end_at: 6.months.from_now)
    @term3.root_account = @account
    @term3.save!
  end

  def create_some_courses
    create_an_account
    create_a_term
    start_at = 1.day.ago
    end_at = 3.months.from_now
    @course1 = Course.new(name: "English 101",
                          course_code: "ENG101",
                          start_at:,
                          conclude_at: end_at)
    @course1.account_id = @sub_account.id
    @course1.enrollment_term_id = @term1.id
    @course1.workflow_state = "available"
    @course1.sis_source_id = "SIS_COURSE_ID_1"
    @course1.restrict_enrollments_to_course_dates = true
    @course1.sis_batch_id = @sis.id
    @course1.course_format = "on_campus"
    @course1.save!

    @course2 = Course.new(name: "Math 101",
                          course_code: "MAT101",
                          conclude_at: end_at,
                          account: @account)
    @course2.workflow_state = "available"
    @course2.sis_source_id = "SIS_COURSE_ID_2"
    @course2.restrict_enrollments_to_course_dates = true
    @course2.sis_batch_id = @sis.id
    @course2.course_format = "online"
    @course2.save!

    @course3 = Course.new(name: "Science 101",
                          course_code: "SCI101",
                          account: @account)
    @course3.workflow_state = "available"
    @course3.sis_source_id = "SIS_COURSE_ID_3"
    @course3.sis_batch_id = @sis.id
    @course3.save!

    @course4 = Course.new(name: "self help",
                          course_code: "self",
                          account: @account)
    @course4.workflow_state = "claimed"
    @course4.save!

    @course5 = Course.new(name: "Sd Math 100",
                          course_code: "ENG101",
                          start_at:,
                          conclude_at: end_at)
    @course5.account_id = @sub_account.id
    @course5.enrollment_term_id = @term1.id
    @course5.sis_source_id = "SIS_COURSE_ID_5"
    @course5.workflow_state = "deleted"
    @course5.sis_batch_id = @sis.id
    @course5.save!

    @course6 = Course.new(name: "talking 101",
                          course_code: "Tal101",
                          account: @account)
    @course6.workflow_state = "completed"
    @course6.save!
  end

  def create_some_courses_and_sections
    create_some_courses

    @section1 = CourseSection.new(name: "English_01",
                                  course: @course1,
                                  start_at: @course1.start_at,
                                  end_at: @course1.conclude_at)
    @section1.sis_source_id = "english_section_1"
    @section1.restrict_enrollments_to_section_dates = true
    @section1.sis_batch_id = @sis.id
    @section1.save!

    @section2 = CourseSection.new(name: "English_02",
                                  course: @course1,
                                  end_at: @course1.conclude_at)
    @section2.sis_source_id = "english_section_2"
    @section2.root_account_id = @account.id
    @section2.restrict_enrollments_to_section_dates = true
    @section2.sis_batch_id = @sis.id
    @section2.save!

    @section3 = CourseSection.new(name: "Math_01",
                                  course: @course2,
                                  end_at: @course2.conclude_at)
    @section3.sis_source_id = "english_section_3"
    @section3.root_account_id = @account.id
    @section3.restrict_enrollments_to_section_dates = true
    @section3.sis_batch_id = @sis.id
    @section3.save!

    @section4 = CourseSection.new(name: "Math_02", course: @course2)
    @section4.root_account_id = @account.id
    @section4.save!

    @section5 = CourseSection.new(name: "Science_01", course: @course3)
    @section5.root_account_id = @account.id
    @section5.save!
    @section5.destroy

    @section6 = CourseSection.new(name: "CS_01", course: @course4)
    @section6.sis_source_id = "cs_section_1"
    @section6.root_account_id = @account.id
    @section6.save!
  end

  def create_some_enrolled_users
    create_some_courses_and_sections
    create_some_users_with_pseudonyms

    @role = @account.roles.build name: "Pixel Engineer"
    @role.base_role_type = "DesignerEnrollment"
    @role.save!

    @enrollment1 = create_enrollment(@course1, @user1, sis_batch_id: @sis.id, enrollment_type: "ObserverEnrollment")
    @enrollment2 = create_enrollment(@course3, @user2, sis_batch_id: @sis.id)
    @enrollment3 = create_enrollment(@course1, @user2, sis_batch_id: @sis.id, enrollment_type: "TaEnrollment")
    @enrollment4 = create_enrollment(@course1, @user3, sis_batch_id: @sis.id)
    @enrollment5 = create_enrollment(@course2, @user3, sis_batch_id: @sis.id)
    @enrollment6 = create_enrollment(@course1,
                                     @user4,
                                     sis_batch_id: @sis.id,
                                     enrollment_type: "TeacherEnrollment",
                                     enrollment_state: "deleted")
    @enrollment7 = create_enrollment(@course2,
                                     @user1,
                                     sis_batch_id: @sis.id,
                                     enrollment_type: "ObserverEnrollment",
                                     associated_user_id: @user3.id)
    @enrollment8 = create_enrollment(@course4, @user5, enrollment_type: "TeacherEnrollment")
    @enrollment9 = create_enrollment(@course1,
                                     @user4,
                                     sis_batch_id: @sis.id,
                                     enrollment_type: "TeacherEnrollment",
                                     section: @section1)
    @enrollment10 = create_enrollment(@course1,
                                      @user6,
                                      enrollment_type: "TeacherEnrollment",
                                      enrollment_state: "completed")
    @enrollment11 = create_enrollment(@course2,
                                      @user4,
                                      sis_batch_id: @sis.id,
                                      enrollment_type: "DesignerEnrollment",
                                      role: @role)
    @enrollment12 = create_enrollment(@course4, @user4, enrollment_state: "creation_pending")
  end

  def create_some_groups
    create_some_group_categories
    @group1 = @account.groups.create(name: "group1name")
    @group1.group_category = @group_category1
    @group1.sis_source_id = "group1sis"
    @group1.sis_batch_id = @sis.id
    @group1.save!
    @group2 = @sub_account.groups.create(name: "group2name")
    @group2.sis_source_id = "group2sis"
    @group2.group_category = @group_category2
    @group2.sis_batch_id = @sis.id
    @group2.save!
    @group3 = @sub_account.groups.create(name: "group3name")
    @group3.save!
    @group4 = @account.groups.create(name: "group4name")
    @group4.sis_source_id = "group4sis"
    @group4.sis_batch_id = @sis.id
    @group4.save!
    @group4.destroy
    @group5 = @course1.groups.create(name: "group5name")
    @group5.sis_source_id = "group5sis"
    @group5.sis_batch_id = @sis.id
    @group5.save!
  end

  def create_some_group_memberships_n_stuff
    create_some_users_with_pseudonyms
    create_some_groups
    batch = @group1.root_account.sis_batches.create!
    @gm1 = GroupMembership.create(group: @group1, user: @user1, workflow_state: "accepted")
    @gm1.sis_batch_id = batch.id
    @gm1.save!
    @gm2 = GroupMembership.create(group: @group2, user: @user2, workflow_state: "accepted")
    @gm2.sis_batch_id = batch.id
    @gm2.save!
    @gm3 = GroupMembership.create(group: @group3, user: @user3, workflow_state: "accepted")
    @gm3.save!
    @gm4 = GroupMembership.create(group: @group2, user: @user3, workflow_state: "accepted")
    @gm4.sis_batch_id = batch.id
    @gm4.save!
    @gm4.destroy
  end

  def create_some_blueprint_course_stuff
    @bc1 = Course.create!(name: "bc1", account: @account, sis_source_id: "SIS_BLUEPRINT_1")
    @template1 = MasterCourses::MasterTemplate.set_as_master_course(@bc1)
    @bc2 = Course.create!(name: "bc2", account: @account, sis_source_id: "SIS_BLUEPRINT_2")
    @template2 = MasterCourses::MasterTemplate.set_as_master_course(@bc2)
    @ac1_a = Course.create!(name: "ac1 a", account: @account, sis_source_id: "SIS_CHILD_BC1_A")
    @ac1_b = Course.create!(name: "ac1 b", account: @account, sis_source_id: "SIS_CHILD_BC1_B")
    [@ac1_a, @ac1_b].each { |ac| @template1.add_child_course!(ac) }
    @ac2 = Course.create!(name: "ac2", account: @account, sis_source_id: "SIS_CHILD_BC2")
    @template2.add_child_course!(@ac2)
  end

  def user_headers(format: "sis")
    @report ||= AccountReports::SisExporter.new(@account.account_reports.new(user: @admin), {})

    header = case format
             when "sis"
               %w[user_id
                  integration_id
                  authentication_provider_id
                  login_id
                  password
                  first_name
                  last_name
                  full_name
                  sortable_name
                  short_name
                  email
                  status]
             when "provisioning"
               %w[canvas_user_id
                  user_id
                  integration_id
                  authentication_provider_id
                  login_id
                  first_name
                  last_name
                  full_name
                  sortable_name
                  short_name
                  email
                  status
                  created_by_sis]
             end
    header << "pronouns" if @report.should_add_pronouns?
    header << "uuid" if format == "provisioning"
    header
  end

  def expected_user(user, pseudonym: nil, format: "sis")
    pseudonym ||= user.pseudonyms.take
    name_parts = User.name_parts(user.sortable_name, likely_already_surname_first: true)
    first_name = name_parts[0]
    last_name = name_parts[1]
    state = pseudonym.workflow_state
    @report ||= AccountReports::SisExporter.new(@account.account_reports.new(user: @admin), {})
    row = case format
          when "sis"
            [pseudonym&.sis_user_id,
             pseudonym&.integration_id,
             pseudonym&.authentication_provider_id,
             pseudonym&.unique_id,
             nil,
             first_name,
             last_name,
             user.name,
             user.sortable_name,
             nil,
             user.email,
             state]
          when "provisioning"
            [user.id.to_s,
             pseudonym&.sis_user_id,
             pseudonym&.integration_id,
             pseudonym&.authentication_provider_id,
             pseudonym&.unique_id,
             first_name,
             last_name,
             user.name,
             user.sortable_name,
             nil,
             user.email,
             state,
             pseudonym&.sis_batch_id?&.to_s]
          end
    row << user.pronouns if @report.should_add_pronouns?
    row << user.uuid if format == "provisioning"
    row
  end

  describe "SIS export and Provisioning reports" do
    before(:once) do
      Notification.where(name: "Report Generated").first_or_create
      Notification.where(name: "Report Generation Failed").first_or_create
      @account = Account.create(name: "New Account", default_time_zone: "UTC")
      @admin = account_admin_user(account: @account, name: "default admin")
      @default_term = @account.default_enrollment_term
    end

    describe "#should_add_pronouns" do
      before(:once) do
        @account.settings[:can_add_pronouns] = true
        @account.save!
        @report = AccountReports::SisExporter.new(@account.account_reports.new(user: @admin), {})
      end

      it "evaluates to true when enabled" do
        expect(@report.should_add_pronouns?).to be(true)
      end

      it "evaluates to false when not enabled" do
        @account.settings[:can_add_pronouns] = false
        @account.save!
        expect(@report.should_add_pronouns?).to be(false)
      end

      it "evaluates to false when disabled for just report" do
        @account.settings[:enable_sis_export_pronouns] = false
        @account.save!
        expect(@report.should_add_pronouns?).to be(false)
      end
    end

    describe "Users" do
      before(:once) do
        create_some_users_with_pseudonyms
      end

      it "runs sis report with term parameter and include deleted users" do
        parameters = {}
        parameters["enrollment_term"] = @default_term.id
        # term does not impact user report
        parameters["include_deleted"] = true
        parameters["created_by_sis"] = true
        parameters["users"] = true
        parsed = read_report("sis_export_csv", { params: parameters, header: true, order: 0 })
        headers = parsed.shift
        expect(headers).to eq user_headers
        expect(parsed.length).to eq 4
        expect(parsed).to eq([@user1, @user2, @user3, @user5].map { |u| expected_user(u) })
      end

      it "runs sis report" do
        parameters = {}
        parameters["users"] = true
        parsed = read_report("sis_export_csv", { params: parameters, order: 0 })
        expect(parsed.length).to eq 4

        expect(parsed).to eq([@user1, @user2, @user3, @user4].map { |u| expected_user(u) })
      end

      it "does not add pronouns if not enabled for the account" do
        @account.settings[:can_add_pronouns] = false
        @account.save!
        parameters = {}
        parameters["users"] = true
        parsed = read_report("sis_export_csv", { params: parameters, order: 0 })
        expect(parsed.first.last).to eq("active")
      end

      it "adds pronouns if enabled for the account" do
        @account.settings[:can_add_pronouns] = true
        @account.save!
        parameters = {}
        parameters["users"] = true
        parsed = read_report("sis_export_csv", { params: parameters, order: 0 })
        expect(parsed.shift.last).to eq "human/being"
        expect(parsed.shift.last).to eq "She/Her"
        expect(parsed).to eq([@user3, @user4].map { |u| expected_user(u) })
      end

      it "runs sis report on a sub_account" do
        create_an_account
        @course1 = Course.new(name: "English 101", course_code: "ENG101")
        @course1.account_id = @sub_account.id
        @course1.workflow_state = "available"
        @course1.save!
        @enrollment1 = @course1.enroll_user(@user1, "StudentEnrollment", enrollment_state: :active)

        parameters = {}
        parameters["users"] = true
        parsed = read_report("sis_export_csv", { params: parameters, account: @sub_account })
        expect(parsed.length).to eq 1
        expect(parsed).to match_array([expected_user(@user1)])
      end

      it "runs provisioning report" do
        parameters = {}
        parameters["users"] = true
        parsed = read_report("provisioning_csv", { params: parameters, order: [1, 2], header: true })

        headers = parsed.shift
        expect(headers).to eq user_headers(format: "provisioning")
        expect(parsed.length).to eq 7
        expect(parsed).to eq([@user6, @user7, @user9, @user1, @user2, @user3, @user4].map { |u| expected_user(u, format: "provisioning") })
      end

      it "interprets created_by_sis=0 correctly in a provisioning report" do
        parameters = {}
        parameters["users"] = "1"
        parameters["created_by_sis"] = "0"
        parsed = read_report("provisioning_csv", { params: parameters, order: [1, 2], header: true })

        headers = parsed.shift
        expect(headers).to eq user_headers(format: "provisioning")
        expect(parsed.length).to eq 7
        expect(parsed).to eq([@user6, @user7, @user9, @user1, @user2, @user3, @user4].map { |u| expected_user(u, format: "provisioning") })
      end

      it "runs provisioning report including deleted users for course" do
        c = Course.create(name: "course1")
        c.student_view_student
        parameters = {}
        parameters["users"] = true
        parameters["include_deleted"] = true
        parameters["created_by_sis"] = true
        parsed = read_report("provisioning_csv", { params: parameters, order: [1, 2] })
        expect(parsed.length).to eq 4

        expect(parsed).to eq([@user1, @user2, @user3, @user5].map { |u| expected_user(u, format: "provisioning") })
      end
    end

    describe "Accounts" do
      before(:once) do
        create_some_accounts
      end

      it "runs the SIS report" do
        parameters = {}
        parameters["enrollment_term"] = @default_term.id
        parameters["accounts"] = true
        parsed = read_report("sis_export_csv", { params: parameters, order: 0 })

        expect(parsed.length).to eq 3
        expect(parsed).to match_array [["sub1", nil, "English", "active"],
                                       ["sub3", nil, "math", "active"],
                                       %w[subsub1 sub1 sESL active]]
      end

      it "runs the SIS report on a sub account" do
        parameters = {}
        parameters["accounts"] = true
        parsed = read_report("sis_export_csv", { params: parameters, account: @sub_account })

        expect(parsed.length).to eq 1
        expect(parsed).to match_array [%w[subsub1 sub1 sESL active]]
      end

      it "runs the SIS report including deleted accounts" do
        parameters = {}
        parameters["accounts"] = true
        parameters["include_deleted"] = true
        parsed = read_report("sis_export_csv", { params: parameters, order: 0 })

        expect(parsed.length).to eq 4
        expect(parsed).to match_array [["sub1", nil, "English", "active"],
                                       ["sub3", nil, "math", "active"],
                                       ["sub4", nil, "deleted sis account", "deleted"],
                                       %w[subsub1 sub1 sESL active]]
      end

      it "runs the provisioning report including deleted accounts" do
        parameters = {}
        parameters["accounts"] = true
        parameters["enrollment_term"] = ""
        parameters["include_deleted"] = true
        parsed = read_report("provisioning_csv", { params: parameters, order: 4 })

        expect(parsed.length).to eq 5
        expect(parsed).to match_array [[@sub_account.id.to_s,
                                        "sub1",
                                        @account.id.to_s,
                                        nil,
                                        "English",
                                        "active",
                                        "true"],
                                       [@sub_account4.id.to_s,
                                        "sub4",
                                        @account.id.to_s,
                                        nil,
                                        "deleted sis account",
                                        "deleted",
                                        "true"],
                                       [@sub_account3.id.to_s,
                                        "sub3",
                                        @account.id.to_s,
                                        nil,
                                        "math",
                                        "active",
                                        "true"],
                                       [@sub_account5.id.to_s,
                                        nil,
                                        @account.id.to_s,
                                        nil,
                                        "other",
                                        "active",
                                        "false"],
                                       [@sub_sub_account.id.to_s,
                                        "subsub1",
                                        @sub_account.id.to_s,
                                        "sub1",
                                        "sESL",
                                        "active",
                                        "true"]]
      end
    end

    describe "Terms" do
      before(:once) do
        create_some_terms
      end

      it "runs the SIS report" do
        parameters = {}
        parameters["enrollment_term"] = @term3.id
        parameters["include_deleted"] = true
        parameters["terms"] = true
        parsed = read_report("sis_export_csv", { params: parameters, order: 0 })

        expect(parsed.length).to eq 2
        expect(parsed).to match_array [["fall12",
                                        "Fall",
                                        "active",
                                        @term1.start_at.iso8601,
                                        @term1.end_at.iso8601],
                                       ["winter13",
                                        "Winter",
                                        "deleted",
                                        @term2.start_at.iso8601,
                                        @term2.end_at.iso8601]]
      end

      it "runs the provisioning report" do
        parameters = {}
        parameters["terms"] = true
        parsed = read_report("provisioning_csv", { params: parameters, order: 2 })

        expect(parsed.length).to eq 3
        expect(parsed).to match_array [[@default_term.id.to_s, nil, "Default Term", "active", nil, nil, "false"],
                                       [@term1.id.to_s,
                                        "fall12",
                                        "Fall",
                                        "active",
                                        @term1.start_at.iso8601,
                                        @term1.end_at.iso8601,
                                        "true"],
                                       [@term3.id.to_s,
                                        nil,
                                        "Spring",
                                        "active",
                                        @term3.start_at.iso8601,
                                        @term3.end_at.iso8601,
                                        "false"]]
      end

      it "runs the provisioning report with deleted terms" do
        parameters = {}
        parameters["terms"] = true
        parameters["include_deleted"] = true
        parsed = read_report("provisioning_csv", { params: parameters, order: 2 })

        expect(parsed.length).to eq 4
        expect(parsed).to match_array [[@default_term.id.to_s, nil, "Default Term", "active", nil, nil, "false"],
                                       [@term1.id.to_s,
                                        "fall12",
                                        "Fall",
                                        "active",
                                        @term1.start_at.iso8601,
                                        @term1.end_at.iso8601,
                                        "true"],
                                       [@term3.id.to_s,
                                        nil,
                                        "Spring",
                                        "active",
                                        @term3.start_at.iso8601,
                                        @term3.end_at.iso8601,
                                        "false"],
                                       [@term2.id.to_s,
                                        "winter13",
                                        "Winter",
                                        "deleted",
                                        @term2.start_at.iso8601,
                                        @term2.end_at.iso8601,
                                        "true"]]
      end
    end

    describe "Courses" do
      before(:once) do
        create_some_courses
      end

      it "runs the SIS report" do
        parameters = {}
        parameters["enrollment_term"] = ""
        parameters["courses"] = true
        parsed = read_report("sis_export_csv", { params: parameters, order: 0 })

        expect(parsed.length).to eq 3
        expect(parsed).to match_array [[@course1.sis_source_id,
                                        nil,
                                        @course1.course_code,
                                        @course1.name,
                                        @sub_account.sis_source_id,
                                        @term1.sis_source_id,
                                        "active",
                                        @course1.start_at.iso8601,
                                        @course1.end_at.iso8601,
                                        @course1.course_format,
                                        nil],
                                       ["SIS_COURSE_ID_2",
                                        nil,
                                        "MAT101",
                                        "Math 101",
                                        nil,
                                        nil,
                                        "active",
                                        nil,
                                        @course2.end_at.iso8601,
                                        @course2.course_format,
                                        nil],
                                       ["SIS_COURSE_ID_3", nil, "SCI101", "Science 101", nil, nil, "active", nil, nil, nil, nil]]
      end

      it "runs the SIS report with sis term and deleted courses" do
        @course1.complete
        parameters = {}
        parameters["enrollment_term_id"] = "sis_term_id:fall12"
        parameters["include_deleted"] = true
        parameters["courses"] = true
        parsed = read_report("sis_export_csv", { params: parameters, order: 0 })

        expect(parsed.length).to eq 2
        expect(parsed).to match_array [[@course1.sis_source_id,
                                        nil,
                                        @course1.course_code,
                                        @course1.name,
                                        @sub_account.sis_source_id,
                                        @term1.sis_source_id,
                                        "completed",
                                        @course1.start_at.iso8601,
                                        @course1.conclude_at.iso8601,
                                        @course1.course_format,
                                        nil],
                                       ["SIS_COURSE_ID_5",
                                        nil,
                                        "ENG101",
                                        "Sd Math 100",
                                        "sub1",
                                        "fall12",
                                        "deleted",
                                        nil,
                                        nil,
                                        nil,
                                        nil]]
      end

      it "runs the provisioning report" do
        @course6.destroy
        @course4.destroy
        Course.where(id: @course6.id).update_all(updated_at: 122.days.ago)
        parameters = {}
        parameters["include_deleted"] = true
        parameters["courses"] = true
        parsed = read_report("provisioning_csv", { params: parameters, order: 3 })

        expect(parsed).to match_array [[@course5.id.to_s,
                                        @course5.sis_source_id,
                                        nil,
                                        @course5.course_code,
                                        @course5.name,
                                        @sub_account.id.to_s,
                                        "sub1",
                                        @term1.id.to_s,
                                        "fall12",
                                        "deleted",
                                        nil,
                                        nil,
                                        nil,
                                        nil,
                                        nil,
                                        "true"],
                                       [@course1.id.to_s,
                                        "SIS_COURSE_ID_1",
                                        nil,
                                        "ENG101",
                                        "English 101",
                                        @course1.account_id.to_s,
                                        @sub_account.sis_source_id,
                                        @term1.id.to_s,
                                        @term1.sis_source_id,
                                        "active",
                                        @course1.start_at.iso8601,
                                        @course1.conclude_at.iso8601,
                                        @course1.course_format,
                                        nil,
                                        nil,
                                        "true"],
                                       [@course2.id.to_s,
                                        "SIS_COURSE_ID_2",
                                        nil,
                                        "MAT101",
                                        "Math 101",
                                        @course2.account_id.to_s,
                                        nil,
                                        @default_term.id.to_s,
                                        nil,
                                        "active",
                                        nil,
                                        @course2.conclude_at.iso8601,
                                        @course2.course_format,
                                        nil,
                                        nil,
                                        "true"],
                                       [@course3.id.to_s,
                                        "SIS_COURSE_ID_3",
                                        nil,
                                        "SCI101",
                                        "Science 101",
                                        @course3.account_id.to_s,
                                        nil,
                                        @default_term.id.to_s,
                                        nil,
                                        "active",
                                        nil,
                                        nil,
                                        nil,
                                        nil,
                                        nil,
                                        "true"],
                                       [@course4.id.to_s,
                                        nil,
                                        nil,
                                        "self",
                                        "self help",
                                        @course4.account_id.to_s,
                                        nil,
                                        @default_term.id.to_s,
                                        nil,
                                        "deleted",
                                        nil,
                                        nil,
                                        nil,
                                        nil,
                                        nil,
                                        "false"]]
        expect(parsed.length).to eq 5
      end

      it "runs the sis report on a sub account" do
        parameters = {}
        parameters["courses"] = true
        # all I care about is that it didn't throw a database error due to ambiguous columns
        expect do
          read_report("sis_export_csv", { params: parameters, account: @sub_account })
        end.not_to raise_error
      end

      it "runs the provisioning report on a sub account" do
        parameters = {}
        parameters["courses"] = true
        parsed = read_report("provisioning_csv", { params: parameters, account: @sub_account, order: 3 })

        expect(parsed.length).to eq 1
        expect(parsed).to match_array [[@course1.id.to_s,
                                        @course1.sis_source_id,
                                        nil,
                                        @course1.course_code,
                                        @course1.name,
                                        @sub_account.id.to_s,
                                        @sub_account.sis_source_id,
                                        @term1.id.to_s,
                                        @term1.sis_source_id,
                                        "active",
                                        @course1.start_at.iso8601,
                                        @course1.conclude_at.iso8601,
                                        @course1.course_format,
                                        nil,
                                        nil,
                                        "true"]]
      end

      it "runs the sis report with the default term" do
        parameters = {}
        parameters["enrollment_term_id"] = @default_term.id
        parameters["courses"] = true
        parsed = read_report("sis_export_csv", { params: parameters, order: 0 })

        expect(parsed.length).to eq 2
        expect(parsed).to match_array [["SIS_COURSE_ID_2",
                                        nil,
                                        "MAT101",
                                        "Math 101",
                                        nil,
                                        nil,
                                        "active",
                                        nil,
                                        @course2.end_at.iso8601,
                                        @course2.course_format,
                                        nil],
                                       ["SIS_COURSE_ID_3", nil, "SCI101", "Science 101", nil, nil, "active", nil, nil, nil, nil]]
      end

      it "exports blueprint course stuff for sis export" do
        create_some_blueprint_course_stuff
        parsed = read_report("sis_export_csv",
                             { params: { "enrollment_term_id" => @default_term.id, "courses" => true }, order: 0, parse_header: true })
        expect(parsed.detect { |r| r["course_id"] == @ac1_a.sis_source_id }["blueprint_course_id"]).to eq @bc1.sis_source_id
        expect(parsed.detect { |r| r["course_id"] == @ac1_b.sis_source_id }["blueprint_course_id"]).to eq @bc1.sis_source_id
        expect(parsed.detect { |r| r["course_id"] == @ac2.sis_source_id }["blueprint_course_id"]).to eq @bc2.sis_source_id
      end

      it "exports blueprint course stuff for provisioning csv" do
        create_some_blueprint_course_stuff
        parsed = read_report("provisioning_csv",
                             { params: { "enrollment_term_id" => @default_term.id, "courses" => true }, order: 0, parse_header: true })
        expect(parsed.detect { |r| r["course_id"] == @ac1_a.sis_source_id }["blueprint_course_id"]).to eq @bc1.sis_source_id
        expect(parsed.detect { |r| r["course_id"] == @ac1_b.sis_source_id }["blueprint_course_id"]).to eq @bc1.sis_source_id
        expect(parsed.detect { |r| r["course_id"] == @ac2.sis_source_id }["blueprint_course_id"]).to eq @bc2.sis_source_id
        expect(parsed.detect { |r| r["course_id"] == @ac2.sis_source_id }["canvas_blueprint_course_id"]).to eq @bc2.id.to_s
      end
    end

    describe "Sections" do
      before(:once) do
        create_some_courses_and_sections
      end

      it "runs the SIS report for a term" do
        parameters = {}
        parameters["enrollment_term_id"] = @default_term.id
        parameters["sections"] = true
        parsed = read_report("sis_export_csv", { params: parameters })

        expect(parsed.length).to eq 1
        expect(parsed).to match_array [[@section3.sis_source_id,
                                        @course2.sis_source_id,
                                        nil,
                                        @section3.name,
                                        "active",
                                        nil,
                                        @course2.conclude_at.iso8601]]
      end

      it "does not include sections from deleted courses" do
        @course2.destroy
        parameters = {}
        parameters["sections"] = true
        parsed = read_report("sis_export_csv", { params: parameters, order: 0 })

        expect(parsed.length).to eq 2
        expect(parsed).to match_array [[@section1.sis_source_id,
                                        @course1.sis_source_id,
                                        nil,
                                        @section1.name,
                                        "active",
                                        @course1.start_at.iso8601,
                                        @course1.conclude_at.iso8601],
                                       [@section2.sis_source_id,
                                        @course1.sis_source_id,
                                        nil,
                                        @section2.name,
                                        "active",
                                        nil,
                                        @course1.conclude_at.iso8601]]
      end

      it "runs the provisioning report" do
        @section1.crosslist_to_course(@course2)
        parameters = {}
        parameters["sections"] = true
        parsed = read_report("provisioning_csv", { params: parameters, order: 5 })
        expect(parsed.length).to eq 5
        expect(parsed).to match_array [[@section6.id.to_s,
                                        @section6.sis_source_id,
                                        @course4.id.to_s,
                                        nil,
                                        nil,
                                        @section6.name,
                                        "active",
                                        nil,
                                        nil,
                                        @account.id.to_s,
                                        nil,
                                        "false"],
                                       [@section1.id.to_s,
                                        @section1.sis_source_id,
                                        @course2.id.to_s,
                                        @course2.sis_source_id,
                                        nil,
                                        @section1.name,
                                        "active",
                                        @section1.start_at.iso8601,
                                        @section1.end_at.iso8601,
                                        @account.id.to_s,
                                        nil,
                                        "true"],
                                       [@section4.id.to_s,
                                        nil,
                                        @course2.id.to_s,
                                        @course2.sis_source_id,
                                        nil,
                                        @section4.name,
                                        "active",
                                        nil,
                                        nil,
                                        @account.id.to_s,
                                        nil,
                                        "false"],
                                       [@section3.id.to_s,
                                        @section3.sis_source_id,
                                        @course2.id.to_s,
                                        "SIS_COURSE_ID_2",
                                        nil,
                                        "Math_01",
                                        "active",
                                        nil,
                                        @course2.conclude_at.iso8601,
                                        @account.id.to_s,
                                        nil,
                                        "true"],
                                       [@section2.id.to_s,
                                        @section2.sis_source_id,
                                        @course1.id.to_s,
                                        "SIS_COURSE_ID_1",
                                        nil,
                                        "English_02",
                                        "active",
                                        nil,
                                        @course2.conclude_at.iso8601,
                                        @sub_account.id.to_s,
                                        "sub1",
                                        "true"]]
      end

      it "runs the provisioning report with deleted sections" do
        @section1.destroy
        @section6.destroy
        parameters = {}
        parameters["sections"] = true
        parameters["include_deleted"] = true
        parsed = read_report("provisioning_csv", { params: parameters, order: 5 })
        expect(parsed.length).to eq 5
        expect(parsed).to match_array [[@section6.id.to_s,
                                        @section6.sis_source_id,
                                        @course4.id.to_s,
                                        nil,
                                        nil,
                                        @section6.name,
                                        "deleted",
                                        nil,
                                        nil,
                                        @account.id.to_s,
                                        nil,
                                        "false"],
                                       [@section4.id.to_s,
                                        nil,
                                        @course2.id.to_s,
                                        "SIS_COURSE_ID_2",
                                        nil,
                                        "Math_02",
                                        "active",
                                        nil,
                                        nil,
                                        @account.id.to_s,
                                        nil,
                                        "false"],
                                       [@section3.id.to_s,
                                        @section3.sis_source_id,
                                        @course2.id.to_s,
                                        "SIS_COURSE_ID_2",
                                        nil,
                                        "Math_01",
                                        "active",
                                        nil,
                                        @course2.conclude_at.iso8601,
                                        @account.id.to_s,
                                        nil,
                                        "true"],
                                       [@section2.id.to_s,
                                        @section2.sis_source_id,
                                        @course1.id.to_s,
                                        @course1.sis_source_id,
                                        nil,
                                        @section2.name,
                                        "active",
                                        nil,
                                        @course1.conclude_at.iso8601,
                                        @sub_account.id.to_s,
                                        "sub1",
                                        "true"],
                                       [@section1.id.to_s,
                                        @section1.sis_source_id,
                                        @course1.id.to_s,
                                        @course1.sis_source_id,
                                        nil,
                                        @section1.name,
                                        "deleted",
                                        @course1.start_at.iso8601,
                                        @course1.conclude_at.iso8601,
                                        @sub_account.id.to_s,
                                        "sub1",
                                        "true"]]
      end

      it "runs the provisioning report with deleted sections on a sub account" do
        @section2.destroy

        parameters = {}
        parameters["sections"] = true
        parameters["include_deleted"] = true
        parsed = read_report("provisioning_csv", { params: parameters, account: @sub_account, order: 4 })
        expect(parsed.length).to eq 2

        expect(parsed).to match_array [[@section2.id.to_s,
                                        @section2.sis_source_id,
                                        @course1.id.to_s,
                                        @course1.sis_source_id,
                                        nil,
                                        @section2.name,
                                        "deleted",
                                        nil,
                                        @course1.conclude_at.iso8601,
                                        @sub_account.id.to_s,
                                        "sub1",
                                        "true"],
                                       [
                                         @section1.id.to_s,
                                         @section1.sis_source_id,
                                         @course1.id.to_s,
                                         @course1.sis_source_id,
                                         nil,
                                         @section1.name,
                                         "active",
                                         @course1.start_at.iso8601,
                                         @course1.conclude_at.iso8601,
                                         @sub_account.id.to_s,
                                         "sub1",
                                         "true"
                                       ]]
      end
    end

    describe "Enrollments" do
      before(:once) do
        create_some_enrolled_users
      end

      it "runs the SIS report" do
        parameters = {}
        parameters["enrollments"] = true
        parsed = read_report("sis_export_csv", { params: parameters, order: [1, 0] })
        # should ignore creation pending enrollments on sis_export
        expect(parsed.length).to eq 8
        expect(parsed).to match_array [["SIS_COURSE_ID_1",
                                        "user_sis_id_01",
                                        "observer",
                                        observer_role(root_account_id: @account.id).id.to_s,
                                        nil,
                                        "active",
                                        nil,
                                        "false"],
                                       ["SIS_COURSE_ID_2",
                                        "user_sis_id_01",
                                        "observer",
                                        observer_role(root_account_id: @account.id).id.to_s,
                                        nil,
                                        "active",
                                        "user_sis_id_03",
                                        "false"],
                                       ["SIS_COURSE_ID_1",
                                        "user_sis_id_02",
                                        "ta",
                                        ta_role(root_account_id: @account.id).id.to_s,
                                        nil,
                                        "active",
                                        nil,
                                        "false"],
                                       ["SIS_COURSE_ID_3",
                                        "user_sis_id_02",
                                        "student",
                                        student_role(root_account_id: @account.id).id.to_s,
                                        nil,
                                        "active",
                                        nil,
                                        "false"],
                                       ["SIS_COURSE_ID_1",
                                        "user_sis_id_03",
                                        "student",
                                        student_role(root_account_id: @account.id).id.to_s,
                                        nil,
                                        "active",
                                        nil,
                                        "false"],
                                       ["SIS_COURSE_ID_2",
                                        "user_sis_id_03",
                                        "student",
                                        student_role(root_account_id: @account.id).id.to_s,
                                        nil,
                                        "active",
                                        nil,
                                        "false"],
                                       ["SIS_COURSE_ID_1",
                                        "user_sis_id_04",
                                        "teacher",
                                        teacher_role(root_account_id: @account.id).id.to_s,
                                        "english_section_1",
                                        "active",
                                        nil,
                                        "false"],
                                       ["SIS_COURSE_ID_2",
                                        "user_sis_id_04",
                                        "Pixel Engineer",
                                        @role.id.to_s,
                                        nil,
                                        "active",
                                        nil,
                                        "false"]]
      end

      it "runs sis report for a term" do
        parameters = {}
        parameters["enrollment_term_id"] = @default_term.id
        parameters["enrollments"] = true
        # this extra pseudonym should not cause an extra row in the output
        @user2.pseudonyms.create!(unique_id: "pseudonym2@instructure.com")
        parsed = read_report("sis_export_csv", { params: parameters, order: [1, 0] })
        expect(parsed.length).to eq 4

        expect(parsed).to match_array [["SIS_COURSE_ID_2",
                                        "user_sis_id_01",
                                        "observer",
                                        observer_role(root_account_id: @account.id).id.to_s,
                                        nil,
                                        "active",
                                        "user_sis_id_03",
                                        "false"],
                                       ["SIS_COURSE_ID_3",
                                        "user_sis_id_02",
                                        "student",
                                        student_role(root_account_id: @account.id).id.to_s,
                                        nil,
                                        "active",
                                        nil,
                                        "false"],
                                       ["SIS_COURSE_ID_2",
                                        "user_sis_id_03",
                                        "student",
                                        student_role(root_account_id: @account.id).id.to_s,
                                        nil,
                                        "active",
                                        nil,
                                        "false"],
                                       ["SIS_COURSE_ID_2",
                                        "user_sis_id_04",
                                        "Pixel Engineer",
                                        @role.id.to_s,
                                        nil,
                                        "active",
                                        nil,
                                        "false"]]
      end

      it "runs the provisioning report with deleted enrollments" do
        @enrollment2.enrollment_state.update!(state: "completed")
        c = Course.create(name: "course1")
        c.student_view_student
        Course.where(id: @course2.id).update_all(workflow_state: "deleted")
        parameters = {}
        parameters["enrollments"] = true
        parameters["include_deleted"] = true
        parsed = read_report("provisioning_csv", { params: parameters, order: "skip" })
        expect(parsed).to match_array [[@course1.id.to_s,
                                        "SIS_COURSE_ID_1",
                                        @user6.id.to_s,
                                        nil,
                                        "teacher",
                                        teacher_role(root_account_id: @account.id).id.to_s,
                                        @enrollment10.course_section_id.to_s,
                                        nil,
                                        "concluded",
                                        nil,
                                        nil,
                                        "false",
                                        "TeacherEnrollment",
                                        "false",
                                        @enrollment10.id.to_s],
                                       [@course1.id.to_s,
                                        "SIS_COURSE_ID_1",
                                        @user1.id.to_s,
                                        "user_sis_id_01",
                                        "observer",
                                        observer_role(root_account_id: @account.id).id.to_s,
                                        @enrollment1.course_section_id.to_s,
                                        nil,
                                        "active",
                                        nil,
                                        nil,
                                        "true",
                                        "ObserverEnrollment",
                                        "false",
                                        @enrollment1.id.to_s],
                                       [@course2.id.to_s,
                                        "SIS_COURSE_ID_2",
                                        @user1.id.to_s,
                                        "user_sis_id_01",
                                        "observer",
                                        observer_role(root_account_id: @account.id).id.to_s,
                                        @enrollment7.course_section_id.to_s,
                                        nil,
                                        "deleted",
                                        @user3.id.to_s,
                                        "user_sis_id_03",
                                        "true",
                                        "ObserverEnrollment",
                                        "false",
                                        @enrollment7.id.to_s],
                                       [@course1.id.to_s,
                                        "SIS_COURSE_ID_1",
                                        @user2.id.to_s,
                                        "user_sis_id_02",
                                        "ta",
                                        ta_role(root_account_id: @account.id).id.to_s,
                                        @enrollment3.course_section_id.to_s,
                                        nil,
                                        "active",
                                        nil,
                                        nil,
                                        "true",
                                        "TaEnrollment",
                                        "false",
                                        @enrollment3.id.to_s],
                                       [@course3.id.to_s,
                                        "SIS_COURSE_ID_3",
                                        @user2.id.to_s,
                                        "user_sis_id_02",
                                        "student",
                                        student_role(root_account_id: @account.id).id.to_s,
                                        @enrollment2.course_section_id.to_s,
                                        nil,
                                        "concluded",
                                        nil,
                                        nil,
                                        "true",
                                        "StudentEnrollment",
                                        "false",
                                        @enrollment2.id.to_s],
                                       [@course1.id.to_s,
                                        "SIS_COURSE_ID_1",
                                        @user3.id.to_s,
                                        "user_sis_id_03",
                                        "student",
                                        student_role(root_account_id: @account.id).id.to_s,
                                        @enrollment4.course_section_id.to_s,
                                        nil,
                                        "active",
                                        nil,
                                        nil,
                                        "true",
                                        "StudentEnrollment",
                                        "false",
                                        @enrollment4.id.to_s],
                                       [@course2.id.to_s,
                                        "SIS_COURSE_ID_2",
                                        @user3.id.to_s,
                                        "user_sis_id_03",
                                        "student",
                                        student_role(root_account_id: @account.id).id.to_s,
                                        @enrollment5.course_section_id.to_s,
                                        nil,
                                        "deleted",
                                        nil,
                                        nil,
                                        "true",
                                        "StudentEnrollment",
                                        "false",
                                        @enrollment5.id.to_s],
                                       [@course1.id.to_s,
                                        "SIS_COURSE_ID_1",
                                        @user4.id.to_s,
                                        "user_sis_id_04",
                                        "teacher",
                                        teacher_role(root_account_id: @account.id).id.to_s,
                                        @enrollment9.course_section_id.to_s,
                                        "english_section_1",
                                        "active",
                                        nil,
                                        nil,
                                        "true",
                                        "TeacherEnrollment",
                                        "false",
                                        @enrollment9.id.to_s],
                                       [@course1.id.to_s,
                                        "SIS_COURSE_ID_1",
                                        @user4.id.to_s,
                                        "user_sis_id_04",
                                        "teacher",
                                        teacher_role(root_account_id: @account.id).id.to_s,
                                        @enrollment6.course_section_id.to_s,
                                        nil,
                                        "deleted",
                                        nil,
                                        nil,
                                        "true",
                                        "TeacherEnrollment",
                                        "false",
                                        @enrollment6.id.to_s],
                                       [@course2.id.to_s,
                                        "SIS_COURSE_ID_2",
                                        @user4.id.to_s,
                                        "user_sis_id_04",
                                        "Pixel Engineer",
                                        @role.id.to_s,
                                        @enrollment11.course_section_id.to_s,
                                        nil,
                                        "deleted",
                                        nil,
                                        nil,
                                        "true",
                                        "DesignerEnrollment",
                                        "false",
                                        @enrollment11.id.to_s],
                                       [@course4.id.to_s,
                                        nil,
                                        @user4.id.to_s,
                                        "user_sis_id_04",
                                        "student",
                                        student_role(root_account_id: @account.id).id.to_s,
                                        @enrollment12.course_section_id.to_s,
                                        nil,
                                        "invited",
                                        nil,
                                        nil,
                                        "false",
                                        "StudentEnrollment",
                                        "false",
                                        @enrollment12.id.to_s],
                                       [@course4.id.to_s,
                                        nil,
                                        @user5.id.to_s,
                                        "user_sis_id_05",
                                        "teacher",
                                        teacher_role(root_account_id: @account.id).id.to_s,
                                        @enrollment8.course_section_id.to_s,
                                        nil,
                                        "active",
                                        nil,
                                        nil,
                                        "false",
                                        "TeacherEnrollment",
                                        "false",
                                        @enrollment8.id.to_s]]
      end

      it "runs the provisioning report on a term and sub account with deleted enrollments" do
        @course2.account = @sub_account
        @course2.save
        parameters = {}
        parameters["enrollments"] = true
        parameters["include_deleted"] = true
        parameters["enrollment_term_id"] = @term1.id
        parsed = read_report("provisioning_csv", { params: parameters, order: [3, 1, 8] })

        expect(parsed.length).to eq 6

        expect(parsed).to match_array [[@course1.id.to_s,
                                        "SIS_COURSE_ID_1",
                                        @user6.id.to_s,
                                        nil,
                                        "teacher",
                                        teacher_role(root_account_id: @account.id).id.to_s,
                                        @enrollment10.course_section_id.to_s,
                                        nil,
                                        "concluded",
                                        nil,
                                        nil,
                                        "false",
                                        "TeacherEnrollment",
                                        "false",
                                        @enrollment10.id.to_s],
                                       [@course1.id.to_s,
                                        "SIS_COURSE_ID_1",
                                        @user1.id.to_s,
                                        "user_sis_id_01",
                                        "observer",
                                        observer_role(root_account_id: @account.id).id.to_s,
                                        @enrollment1.course_section_id.to_s,
                                        nil,
                                        "active",
                                        nil,
                                        nil,
                                        "true",
                                        "ObserverEnrollment",
                                        "false",
                                        @enrollment1.id.to_s],
                                       [@course1.id.to_s,
                                        "SIS_COURSE_ID_1",
                                        @user2.id.to_s,
                                        "user_sis_id_02",
                                        "ta",
                                        ta_role(root_account_id: @account.id).id.to_s,
                                        @enrollment3.course_section_id.to_s,
                                        nil,
                                        "active",
                                        nil,
                                        nil,
                                        "true",
                                        "TaEnrollment",
                                        "false",
                                        @enrollment3.id.to_s],
                                       [@course1.id.to_s,
                                        "SIS_COURSE_ID_1",
                                        @user3.id.to_s,
                                        "user_sis_id_03",
                                        "student",
                                        student_role(root_account_id: @account.id).id.to_s,
                                        @enrollment4.course_section_id.to_s,
                                        nil,
                                        "active",
                                        nil,
                                        nil,
                                        "true",
                                        "StudentEnrollment",
                                        "false",
                                        @enrollment4.id.to_s],
                                       [@course1.id.to_s,
                                        "SIS_COURSE_ID_1",
                                        @user4.id.to_s,
                                        "user_sis_id_04",
                                        "teacher",
                                        teacher_role(root_account_id: @account.id).id.to_s,
                                        @enrollment9.course_section_id.to_s,
                                        "english_section_1",
                                        "active",
                                        nil,
                                        nil,
                                        "true",
                                        "TeacherEnrollment",
                                        "false",
                                        @enrollment9.id.to_s],
                                       [@course1.id.to_s,
                                        "SIS_COURSE_ID_1",
                                        @user4.id.to_s,
                                        "user_sis_id_04",
                                        "teacher",
                                        teacher_role(root_account_id: @account.id).id.to_s,
                                        @enrollment6.course_section_id.to_s,
                                        nil,
                                        "deleted",
                                        nil,
                                        nil,
                                        "true",
                                        "TeacherEnrollment",
                                        "false",
                                        @enrollment6.id.to_s]]
      end

      it "runs the provisioning report and return active teachers and ta's" do
        parameters = {}
        parameters["enrollments"] = true
        parameters["enrollment_filter"] = "TeacherEnrollment,TaEnrollment"
        parameters["enrollment_states"] = "active"
        parsed = read_report("provisioning_csv", { params: parameters, order: [3, 1, 8] })

        expect(parsed.length).to eq 2

        expect(parsed).to match_array [[@course1.id.to_s,
                                        "SIS_COURSE_ID_1",
                                        @user2.id.to_s,
                                        "user_sis_id_02",
                                        "ta",
                                        ta_role(root_account_id: @account.id).id.to_s,
                                        @enrollment3.course_section_id.to_s,
                                        nil,
                                        "active",
                                        nil,
                                        nil,
                                        "true",
                                        "TaEnrollment",
                                        "false",
                                        @enrollment3.id.to_s],
                                       [@course1.id.to_s,
                                        "SIS_COURSE_ID_1",
                                        @user4.id.to_s,
                                        "user_sis_id_04",
                                        "teacher",
                                        teacher_role(root_account_id: @account.id).id.to_s,
                                        @enrollment9.course_section_id.to_s,
                                        "english_section_1",
                                        "active",
                                        nil,
                                        nil,
                                        "true",
                                        "TeacherEnrollment",
                                        "false",
                                        @enrollment9.id.to_s]]
      end

      it "runs the provisioning report with active enrollments" do
        parameters = {}
        parameters["enrollments"] = true
        parameters["enrollment_states"] = "active"
        parsed = read_report("provisioning_csv", { params: parameters, order: [3, 1, 8] })

        expect(parsed.length).to eq 8

        expect(parsed).to match_array [[@course1.id.to_s,
                                        "SIS_COURSE_ID_1",
                                        @user1.id.to_s,
                                        "user_sis_id_01",
                                        "observer",
                                        observer_role(root_account_id: @account.id).id.to_s,
                                        @enrollment1.course_section_id.to_s,
                                        nil,
                                        "active",
                                        nil,
                                        nil,
                                        "true",
                                        "ObserverEnrollment",
                                        "false",
                                        @enrollment1.id.to_s],
                                       [@course2.id.to_s,
                                        "SIS_COURSE_ID_2",
                                        @user1.id.to_s,
                                        "user_sis_id_01",
                                        "observer",
                                        observer_role(root_account_id: @account.id).id.to_s,
                                        @enrollment7.course_section_id.to_s,
                                        nil,
                                        "active",
                                        @user3.id.to_s,
                                        "user_sis_id_03",
                                        "true",
                                        "ObserverEnrollment",
                                        "false",
                                        @enrollment7.id.to_s],
                                       [@course1.id.to_s,
                                        "SIS_COURSE_ID_1",
                                        @user2.id.to_s,
                                        "user_sis_id_02",
                                        "ta",
                                        ta_role(root_account_id: @account.id).id.to_s,
                                        @enrollment3.course_section_id.to_s,
                                        nil,
                                        "active",
                                        nil,
                                        nil,
                                        "true",
                                        "TaEnrollment",
                                        "false",
                                        @enrollment3.id.to_s],
                                       [@course3.id.to_s,
                                        "SIS_COURSE_ID_3",
                                        @user2.id.to_s,
                                        "user_sis_id_02",
                                        "student",
                                        student_role(root_account_id: @account.id).id.to_s,
                                        @enrollment2.course_section_id.to_s,
                                        nil,
                                        "active",
                                        nil,
                                        nil,
                                        "true",
                                        "StudentEnrollment",
                                        "false",
                                        @enrollment2.id.to_s],
                                       [@course1.id.to_s,
                                        "SIS_COURSE_ID_1",
                                        @user3.id.to_s,
                                        "user_sis_id_03",
                                        "student",
                                        student_role(root_account_id: @account.id).id.to_s,
                                        @enrollment4.course_section_id.to_s,
                                        nil,
                                        "active",
                                        nil,
                                        nil,
                                        "true",
                                        "StudentEnrollment",
                                        "false",
                                        @enrollment4.id.to_s],
                                       [@course2.id.to_s,
                                        "SIS_COURSE_ID_2",
                                        @user3.id.to_s,
                                        "user_sis_id_03",
                                        "student",
                                        student_role(root_account_id: @account.id).id.to_s,
                                        @enrollment5.course_section_id.to_s,
                                        nil,
                                        "active",
                                        nil,
                                        nil,
                                        "true",
                                        "StudentEnrollment",
                                        "false",
                                        @enrollment5.id.to_s],
                                       [@course1.id.to_s,
                                        "SIS_COURSE_ID_1",
                                        @user4.id.to_s,
                                        "user_sis_id_04",
                                        "teacher",
                                        teacher_role(root_account_id: @account.id).id.to_s,
                                        @enrollment9.course_section_id.to_s,
                                        "english_section_1",
                                        "active",
                                        nil,
                                        nil,
                                        "true",
                                        "TeacherEnrollment",
                                        "false",
                                        @enrollment9.id.to_s],
                                       [@course2.id.to_s,
                                        "SIS_COURSE_ID_2",
                                        @user4.id.to_s,
                                        "user_sis_id_04",
                                        "Pixel Engineer",
                                        @role.id.to_s,
                                        @enrollment11.course_section_id.to_s,
                                        nil,
                                        "active",
                                        nil,
                                        nil,
                                        "true",
                                        "DesignerEnrollment",
                                        "false",
                                        @enrollment11.id.to_s]]
      end

      it "runs the provisioning report and return only ta's" do
        parameters = {}
        parameters["enrollments"] = true
        parameters["enrollment_filter"] = "TaEnrollment"
        parsed = read_report("provisioning_csv", { params: parameters, order: [3, 1, 8] })

        expect(parsed.length).to eq 1

        expect(parsed).to match_array [[@course1.id.to_s,
                                        "SIS_COURSE_ID_1",
                                        @user2.id.to_s,
                                        "user_sis_id_02",
                                        "ta",
                                        ta_role(root_account_id: @account.id).id.to_s,
                                        @enrollment3.course_section_id.to_s,
                                        nil,
                                        "active",
                                        nil,
                                        nil,
                                        "true",
                                        "TaEnrollment",
                                        "false",
                                        @enrollment3.id.to_s]]
      end

      it "handles cross listed enrollments" do
        sub = @account.sub_accounts.create!
        course = sub.courses.create!(name: "the course", sis_source_id: "sis1")
        @section1.crosslist_to_course(course)
        parsed = read_report("provisioning_csv", { params: { "enrollments" => true }, account: sub, order: 0 })
        expect(parsed).to eq [[course.id.to_s,
                               "sis1",
                               @user4.id.to_s,
                               "user_sis_id_04",
                               "teacher",
                               teacher_role(root_account_id: @account.id).id.to_s,
                               @enrollment9.course_section_id.to_s,
                               "english_section_1",
                               "active",
                               nil,
                               nil,
                               "true",
                               "TeacherEnrollment",
                               "false",
                               @enrollment9.id.to_s]]
      end

      describe "temporary enrollments" do
        before(:once) do
          @account.enable_feature!(:temporary_enrollments)
          temporary_enrollment_pairing = TemporaryEnrollmentPairing.create!(root_account: @account, created_by: @admin)
          @enrollment = @course1.reload.enroll_user(
            @user2,
            "TeacherEnrollment",
            {
              role: teacher_role,
              temporary_enrollment_source_user_id: @user4.id,
              temporary_enrollment_pairing_id: temporary_enrollment_pairing.id
            }
          )
        end

        it "returns data with feature enabled in provisioning report" do
          parameters = {}
          parameters["enrollments"] = true
          parameters["enrollment_filter"] = "TeacherEnrollment"
          parsed = read_report("provisioning_csv", { params: parameters, order: [1, 0] })

          expect(parsed.length).to eq 2
          expect(parsed).to match_array [[@course1.id.to_s,
                                          "SIS_COURSE_ID_1",
                                          @user4.id.to_s,
                                          @user4.pseudonyms.first.sis_user_id,
                                          "teacher",
                                          teacher_role(root_account_id: @account.id).id.to_s,
                                          @enrollment9.course_section_id.to_s,
                                          @section1.sis_source_id.to_s,
                                          "active",
                                          nil,
                                          nil,
                                          "true",
                                          "TeacherEnrollment",
                                          "false",
                                          @enrollment9.id.to_s,
                                          nil],
                                         [@course1.id.to_s,
                                          "SIS_COURSE_ID_1",
                                          @user2.id.to_s,
                                          @user2.pseudonyms.first.sis_user_id,
                                          "teacher",
                                          teacher_role(root_account_id: @account.id).id.to_s,
                                          @enrollment.course_section_id.to_s,
                                          nil,
                                          "active",
                                          nil,
                                          nil,
                                          "false",
                                          "TeacherEnrollment",
                                          "false",
                                          @enrollment.id.to_s,
                                          @user4.id.to_s]]
        end

        it "does not return data with feature disabled in provisioning report" do
          @account.disable_feature!(:temporary_enrollments)
          parameters = {}
          parameters["enrollments"] = true
          parameters["enrollment_filter"] = "TeacherEnrollment"
          parsed = read_report("provisioning_csv", { params: parameters, order: [1, 0] })

          expect(parsed.length).to eq 2
          expect(parsed).to match_array [[@course1.id.to_s,
                                          "SIS_COURSE_ID_1",
                                          @user4.id.to_s,
                                          @user4.pseudonyms.first.sis_user_id,
                                          "teacher",
                                          teacher_role(root_account_id: @account.id).id.to_s,
                                          @enrollment9.course_section_id.to_s,
                                          @section1.sis_source_id.to_s,
                                          "active",
                                          nil,
                                          nil,
                                          "true",
                                          "TeacherEnrollment",
                                          "false",
                                          @enrollment9.id.to_s],
                                         [@course1.id.to_s,
                                          "SIS_COURSE_ID_1",
                                          @user2.id.to_s,
                                          @user2.pseudonyms.first.sis_user_id,
                                          "teacher",
                                          teacher_role(root_account_id: @account.id).id.to_s,
                                          @enrollment.course_section_id.to_s,
                                          nil,
                                          "active",
                                          nil,
                                          nil,
                                          "false",
                                          "TeacherEnrollment",
                                          "false",
                                          @enrollment.id.to_s]]
        end

        it "returns data with feature enabled in SIS export" do
          parameters = {}
          parameters["enrollments"] = true
          parameters["enrollment_filter"] = "TeacherEnrollment"
          parsed = read_report("sis_export_csv", { params: parameters, order: [1, 0] })

          expect(parsed.length).to eq 2
          expect(parsed).to match_array [["SIS_COURSE_ID_1",
                                          @user2.pseudonyms.first.sis_user_id,
                                          "teacher",
                                          teacher_role(root_account_id: @account.id).id.to_s,
                                          nil,
                                          "active",
                                          nil,
                                          "false",
                                          @user4.pseudonyms.first.sis_user_id],
                                         ["SIS_COURSE_ID_1",
                                          @user4.pseudonyms.first.sis_user_id,
                                          "teacher",
                                          teacher_role(root_account_id: @account.id).id.to_s,
                                          @section1.sis_source_id,
                                          "active",
                                          nil,
                                          "false",
                                          nil]]
        end

        it "does not return data with feature disabled in SIS export" do
          @account.disable_feature!(:temporary_enrollments)

          parameters = {}
          parameters["enrollments"] = true
          parameters["enrollment_filter"] = "TeacherEnrollment"
          parsed = read_report("sis_export_csv", { params: parameters, order: [1, 0] })

          expect(parsed.length).to eq 2
          expect(parsed).to match_array [["SIS_COURSE_ID_1",
                                          @user2.pseudonyms.first.sis_user_id,
                                          "teacher",
                                          teacher_role(root_account_id: @account.id).id.to_s,
                                          nil,
                                          "active",
                                          nil,
                                          "false"],
                                         ["SIS_COURSE_ID_1",
                                          @user4.pseudonyms.first.sis_user_id,
                                          "teacher",
                                          teacher_role(root_account_id: @account.id).id.to_s,
                                          @section1.sis_source_id,
                                          "active",
                                          nil,
                                          "false"]]
        end
      end

      describe "sharding" do
        specs_require_sharding

        it "runs with cross shard pseudonyms" do
          @shard1.activate do
            @root = Account.create
            @user1 = user_with_managed_pseudonym(active_all: true,
                                                 account: @root,
                                                 name: "Jimmy John",
                                                 username: "other_shard@example.com",
                                                 sis_user_id: "other_shard")
            @user2 = user_with_managed_pseudonym(active_all: true,
                                                 account: @root,
                                                 name: "James John",
                                                 username: "other_shar2d@example.com",
                                                 sis_user_id: "other_shard2")
          end
          allow_any_instantiation_of(@account).to receive(:trusted_account_ids).and_return([@account.id, @root.id])
          allow_any_instantiation_of(@account).to receive(:trust_exists?).and_return(true)
          @e1 = @course1.enroll_user(@user1)
          @course1.enroll_user(@user2)

          parameters = {}
          parameters["enrollments"] = true
          parsed = read_report("provisioning_csv", { params: parameters, order: [3, 1, 8] })
          expect(parsed.length).to eq 11

          expect(parsed[0]).to eq [@course1.id.to_s,
                                   "SIS_COURSE_ID_1",
                                   @user1.id.to_s,
                                   "other_shard",
                                   "student",
                                   student_role(root_account_id: @account.id).id.to_s,
                                   @course1.enrollments.find_by(user_id: @user1).course_section_id.to_s,
                                   nil,
                                   "invited",
                                   nil,
                                   nil,
                                   "false",
                                   "StudentEnrollment",
                                   "false",
                                   @e1.id.to_s,
                                   HostUrl.context_host(@root)]
        end
      end
    end

    describe "Groups" do
      before(:once) do
        create_some_groups
      end

      it "runs the SIS report" do
        GroupCategory.where(id: @group_category1).update_all(sis_source_id: "gc101", sis_batch_id: @sis.id)
        GroupCategory.where(id: @group_category2).update_all(sis_source_id: "gc102", sis_batch_id: @sis.id)
        parameters = {}
        parameters["enrollment_term_id"] = @default_term.id
        parameters["groups"] = true
        parsed = read_report("sis_export_csv", { params: parameters, order: 2 })
        expect(parsed.length).to eq 3
        expect(parsed).to match_array [["group1sis", "gc101", nil, nil, "group1name", "available"],
                                       ["group2sis", "gc102", "sub1", nil, "group2name", "available"],
                                       ["group5sis", nil, nil, "SIS_COURSE_ID_1", "group5name", "available"]]
      end

      it "runs the SIS report with deleted groups" do
        parameters = {}
        parameters["include_deleted"] = true
        parameters["groups"] = true
        parsed = read_report("sis_export_csv", { params: parameters, order: 2 })
        expect(parsed.length).to eq 4
        expect(parsed).to match_array [["group1sis", nil, nil, nil, "group1name", "available"],
                                       ["group2sis", nil, "sub1", nil, "group2name", "available"],
                                       ["group4sis", nil, nil, nil, "group4name", "deleted",],
                                       ["group5sis", nil, nil, "SIS_COURSE_ID_1", "group5name", "available"]]
      end

      it "runs the provisioning report" do
        parameters = {}
        parameters["groups"] = true
        parsed = read_report("provisioning_csv", { params: parameters, order: 4 })
        expect(parsed.length).to eq 4
        expect(parsed).to match_array [[@group1.id.to_s,
                                        "group1sis",
                                        @group1.group_category_id.to_s,
                                        nil,
                                        @account.id.to_s,
                                        nil,
                                        nil,
                                        nil,
                                        "group1name",
                                        "available",
                                        "true",
                                        @account.id.to_s,
                                        "Account",
                                        nil],
                                       [@group2.id.to_s,
                                        "group2sis",
                                        @group2.group_category_id.to_s,
                                        nil,
                                        @sub_account.id.to_s,
                                        "sub1",
                                        nil,
                                        nil,
                                        "group2name",
                                        "available",
                                        "true",
                                        @sub_account.id.to_s,
                                        "Account",
                                        "2"],
                                       [@group3.id.to_s,
                                        nil,
                                        nil,
                                        nil,
                                        @sub_account.id.to_s,
                                        "sub1",
                                        nil,
                                        nil,
                                        "group3name",
                                        "available",
                                        "false",
                                        @sub_account.id.to_s,
                                        "Account",
                                        nil],
                                       [@group5.id.to_s,
                                        "group5sis",
                                        @group5.group_category_id.to_s,
                                        nil,
                                        nil,
                                        nil,
                                        @course1.id.to_s,
                                        "SIS_COURSE_ID_1",
                                        "group5name",
                                        "available",
                                        "true",
                                        @course1.id.to_s,
                                        "Course",
                                        nil]]
      end

      it "runs the provisioning report on a sub account" do
        parameters = {}
        parameters["groups"] = true
        parsed = read_report("provisioning_csv", { params: parameters, account: @sub_account, order: 4 })
        expect(parsed.length).to eq 3
        expect(parsed).to match_array [[@group2.id.to_s,
                                        "group2sis",
                                        @group2.group_category_id.to_s,
                                        nil,
                                        @sub_account.id.to_s,
                                        "sub1",
                                        nil,
                                        nil,
                                        "group2name",
                                        "available",
                                        "true",
                                        @sub_account.id.to_s,
                                        "Account",
                                        "2"],
                                       [@group3.id.to_s,
                                        nil,
                                        nil,
                                        nil,
                                        @sub_account.id.to_s,
                                        "sub1",
                                        nil,
                                        nil,
                                        "group3name",
                                        "available",
                                        "false",
                                        @sub_account.id.to_s,
                                        "Account",
                                        nil],
                                       [@group5.id.to_s,
                                        "group5sis",
                                        @group5.group_category_id.to_s,
                                        nil,
                                        nil,
                                        nil,
                                        @course1.id.to_s,
                                        "SIS_COURSE_ID_1",
                                        "group5name",
                                        "available",
                                        "true",
                                        @course1.id.to_s,
                                        "Course",
                                        nil]]
      end

      it "includes sub-sub-account groups when run on a sub account" do
        sub_sub_account = Account.create(parent_account: @sub_account, name: "sESL")
        group6 = sub_sub_account.groups.create!(name: "group6name")
        parameters = {}
        parameters["groups"] = true
        parsed = read_report("provisioning_csv", { params: parameters, account: @sub_account, order: 4 })
        expect(parsed.length).to eq 4
        expect(parsed).to match_array [[@group2.id.to_s,
                                        "group2sis",
                                        @group2.group_category_id.to_s,
                                        nil,
                                        @sub_account.id.to_s,
                                        "sub1",
                                        nil,
                                        nil,
                                        "group2name",
                                        "available",
                                        "true",
                                        @sub_account.id.to_s,
                                        "Account",
                                        "2"],
                                       [@group3.id.to_s,
                                        nil,
                                        nil,
                                        nil,
                                        @sub_account.id.to_s,
                                        "sub1",
                                        nil,
                                        nil,
                                        "group3name",
                                        "available",
                                        "false",
                                        @sub_account.id.to_s,
                                        "Account",
                                        nil],
                                       [@group5.id.to_s,
                                        "group5sis",
                                        @group5.group_category_id.to_s,
                                        nil,
                                        nil,
                                        nil,
                                        @course1.id.to_s,
                                        "SIS_COURSE_ID_1",
                                        "group5name",
                                        "available",
                                        "true",
                                        @course1.id.to_s,
                                        "Course",
                                        nil],
                                       [group6.id.to_s,
                                        nil,
                                        nil,
                                        nil,
                                        sub_sub_account.id.to_s,
                                        nil,
                                        nil,
                                        nil,
                                        "group6name",
                                        "available",
                                        "false",
                                        sub_sub_account.id.to_s,
                                        "Account",
                                        nil]]
      end
    end

    describe "Group Categories" do
      before(:once) do
        create_some_groups
        @student_category = GroupCategory.where(name: "Student Groups").first
      end

      it "runs the provisioning report" do
        parameters = {}
        parameters["group_categories"] = true
        parsed = read_report("provisioning_csv", { params: parameters, order: 5 })
        expect(parsed.length).to eq 4
        expect(parsed).to match_array [[@group_category1.id.to_s, @group_category1.sis_source_id, @account.id.to_s, "Account", "Test Group Category", nil, nil, nil, nil, "active"],
                                       [@group_category2.id.to_s, @group_category2.sis_source_id, @account.id.to_s, "Account", "Test Group Category2", nil, nil, "2", "first", "active"],
                                       [@group_category4.id.to_s, nil, @course3.id.to_s, "Course", "Test Group Category Course", nil, nil, nil, nil, "active"],
                                       [@student_category.id.to_s, nil, @course1.id.to_s, "Course", "Student Groups", "student_organized", nil, nil, nil, "active"]]
      end

      it "runs the sis report" do
        GroupCategory.where(id: @group_category1).update_all(sis_source_id: "gc101", sis_batch_id: @sis.id)
        GroupCategory.where(id: @group_category2).update_all(sis_source_id: "gc102", sis_batch_id: @sis.id)
        GroupCategory.where(id: @group_category4).update_all(sis_source_id: "gc104", sis_batch_id: @sis.id)
        parameters = {}
        parameters["group_categories"] = true
        parsed = read_report("sis_export_csv", { params: parameters, header: true, order: 4 })
        expect(parsed.length).to eq 4
        expect(parsed).to match_array [%w[group_category_id account_id course_id category_name status],
                                       ["gc101", @account.sis_source_id, nil, "Test Group Category", "active"],
                                       ["gc102", @account.sis_source_id, nil, "Test Group Category2", "active"],
                                       ["gc104", nil, "SIS_COURSE_ID_3", "Test Group Category Course", "active"]]
      end

      it "runs the provisioning report for a sub account" do
        parameters = {}
        parameters["group_categories"] = true
        parsed = read_report("provisioning_csv", { params: parameters, order: 4, account: @sub_account })
        expect(parsed.length).to eq 1
        expect(parsed).to match_array [[@student_category.id.to_s, nil, @course1.id.to_s, "Course", "Student Groups", "student_organized", nil, nil, nil, "active"]]
      end

      it "runs the report for deleted categories" do
        parameters = {}
        parameters["group_categories"] = true
        parameters["include_deleted"] = true
        parsed = read_report("provisioning_csv", { params: parameters, order: 5 })
        expect(parsed.length).to eq 5
        expect(parsed).to match_array [[@group_category1.id.to_s, @group_category1.sis_source_id, @account.id.to_s, "Account", "Test Group Category", nil, nil, nil, nil, "active"],
                                       [@group_category2.id.to_s, @group_category2.sis_source_id, @account.id.to_s, "Account", "Test Group Category2", nil, nil, "2", "first", "active"],
                                       [@group_category3.id.to_s, @group_category3.sis_source_id, @account.id.to_s, "Account", "Test Group Category Deleted", nil, nil, nil, nil, "deleted"],
                                       [@group_category4.id.to_s, nil, @course3.id.to_s, "Course", "Test Group Category Course", nil, nil, nil, nil, "active"],
                                       [@student_category.id.to_s, nil, @course1.id.to_s, "Course", "Student Groups", "student_organized", nil, nil, nil, "active"]]
      end

      it "includes account_id column even if there isn't one for any rows" do
        process_csv_data_cleanly(
          "course_id,short_name,long_name,status",
          "C1,COUR,SIS Import Course,active"
        )
        process_csv_data_cleanly(
          "group_category_id,course_id,category_name,status",
          "GC1,C1,Some Group Category,active"
        )
        parameters = {}
        parameters["group_categories"] = true
        parsed = read_report("sis_export_csv", { params: parameters, header: true, order: 0 })
        expect(parsed).to match_array [%w[group_category_id account_id course_id category_name status],
                                       ["GC1", nil, "C1", "Some Group Category", "active"]]
      end
    end

    describe "Group Memberships" do
      before(:once) do
        create_some_group_memberships_n_stuff
      end

      it "runs the sis report" do
        parameters = {}
        parameters["enrollment_term_id"] = @default_term.id
        parameters["group_membership"] = true
        parsed = read_report("sis_export_csv", { params: parameters, order: 0 })
        expect(parsed.length).to eq 2
        expect(parsed).to match_array [[@group1.sis_source_id, "user_sis_id_01", "accepted"],
                                       [@group2.sis_source_id, "user_sis_id_02", "accepted"]]
      end

      it "runs the sis report with deleted group memberships" do
        parameters = {}
        parameters["group_membership"] = true
        parameters["include_deleted"] = true
        parsed = read_report("sis_export_csv", { params: parameters, order: [0, 1] })
        expect(parsed.length).to eq 3
        expect(parsed).to match_array [[@group1.sis_source_id, "user_sis_id_01", "accepted"],
                                       [@group2.sis_source_id, "user_sis_id_02", "accepted"],
                                       [@group2.sis_source_id, "user_sis_id_03", "deleted"]]
      end

      it "runs the provisioning report" do
        parameters = {}
        parameters["group_membership"] = true
        parsed = read_report("provisioning_csv", { params: parameters, order: "skip" })
        expect(parsed).to match_array([
                                        [@group1.id.to_s,
                                         @group1.sis_source_id,
                                         @user1.id.to_s,
                                         "user_sis_id_01",
                                         "accepted",
                                         "true"],
                                        [@group2.id.to_s,
                                         @group2.sis_source_id,
                                         @user2.id.to_s,
                                         "user_sis_id_02",
                                         "accepted",
                                         "true"],
                                        [@group3.id.to_s,
                                         nil,
                                         @user3.id.to_s,
                                         "user_sis_id_03",
                                         "accepted",
                                         "false"]
                                      ])
      end

      it "runs the provisioning report for a subaccount" do
        @gm5 = GroupMembership.create(group: @group5, user: @user3, workflow_state: "accepted")
        parameters = {}
        parameters["group_membership"] = true
        parsed = read_report("provisioning_csv", { params: parameters, account: @sub_account, order: [1, 3] })
        expect(parsed.length).to eq 3
        expect(parsed).to match_array [[@group3.id.to_s,
                                        nil,
                                        @user3.id.to_s,
                                        "user_sis_id_03",
                                        "accepted",
                                        "false"],
                                       [@group2.id.to_s,
                                        @group2.sis_source_id,
                                        @user2.id.to_s,
                                        "user_sis_id_02",
                                        "accepted",
                                        "true"],
                                       [@group5.id.to_s,
                                        @group5.sis_source_id,
                                        @user3.id.to_s,
                                        "user_sis_id_03",
                                        "accepted",
                                        "false"]]
      end
    end

    describe "Cross List" do
      before(:once) do
        create_some_courses_and_sections
        @section1.crosslist_to_course(@course2)
        @section3.crosslist_to_course(@course1)
        @section5.crosslist_to_course(@course6)
      end

      it "runs the sis report with the default term" do
        parameters = {}
        parameters["enrollment_term_id"] = @default_term.id
        parameters["xlist"] = true
        parameters["include_deleted"] = false
        report = run_report("sis_export_csv", { params: parameters })
        expect(report.parameters["extra_text"]).to eq "Term: Default Term; Reports: xlist "
        parsed = parse_report(report, { header: true })
        headers = parsed.shift
        expect(headers).to eq %w[xlist_course_id section_id status]
        expect(parsed).to match_array [%w[SIS_COURSE_ID_2
                                          english_section_1
                                          active]]
        expect(parsed.length).to eq 1
      end

      it "runs sis report with deleted sections" do
        @section3.destroy
        parameters = {}
        parameters["xlist"] = true
        parameters["include_deleted"] = true
        parsed = read_report("sis_export_csv", { params: parameters, order: 0 })
        expect(parsed).to match_array [%w[SIS_COURSE_ID_1
                                          english_section_3
                                          deleted],
                                       %w[SIS_COURSE_ID_2
                                          english_section_1
                                          active]]
        expect(parsed.length).to eq 2
      end

      it "runs sis report with deleted sections on a sub account" do
        @section3.destroy
        parameters = {}
        parameters["xlist"] = true
        parameters["include_deleted"] = true
        report = run_report("sis_export_csv", { params: parameters, account: @sub_account })
        expect(report.parameters["extra_text"]).to eq "Term: All Terms; Include Deleted/Concluded Objects; Reports: xlist "
        parsed = parse_report(report)
        expect(parsed).to match_array [%w[SIS_COURSE_ID_1
                                          english_section_3
                                          deleted]]
        expect(parsed.length).to eq 1
      end

      it "runs the provisioning report" do
        parameters = {}
        parameters["xlist"] = true
        parsed = read_report("provisioning_csv", { params: parameters, order: 1 })
        expect(parsed).to match_array [[@course1.id.to_s,
                                        "SIS_COURSE_ID_1",
                                        @section3.id.to_s,
                                        "english_section_3",
                                        "active",
                                        @course2.id.to_s,
                                        @course2.sis_source_id],
                                       [@course2.id.to_s,
                                        "SIS_COURSE_ID_2",
                                        @section1.id.to_s,
                                        "english_section_1",
                                        "active",
                                        @course1.id.to_s,
                                        @course1.sis_source_id]]
        expect(parsed.length).to eq 2
      end

      it "runs the provisioning report with deleted sections" do
        parameters = {}
        parameters["include_deleted"] = true
        parameters["xlist"] = true
        parsed = read_report("provisioning_csv", { params: parameters, order: 1 })
        expect(parsed).to match_array [[@course1.id.to_s,
                                        "SIS_COURSE_ID_1",
                                        @section3.id.to_s,
                                        "english_section_3",
                                        "active",
                                        @course2.id.to_s,
                                        @course2.sis_source_id],
                                       [@course2.id.to_s,
                                        "SIS_COURSE_ID_2",
                                        @section1.id.to_s,
                                        "english_section_1",
                                        "active",
                                        @course1.id.to_s,
                                        @course1.sis_source_id]]
        expect(parsed.length).to eq 2
      end
    end

    describe "user_observers" do
      before(:once) do
        create_an_account
        create_some_users_with_pseudonyms
        @uo1 = UserObservationLink.create_or_restore(student: @user1, observer: @user2, root_account: @account)
        uo2 = UserObservationLink.create_or_restore(student: @user3, observer: @user4, root_account: @account)
        UserObservationLink.create_or_restore(student: @user6, observer: @user7, root_account: @account)
        UserObservationLink.where(id: [@uo1.id, uo2.id]).update_all(sis_batch_id: @sis.id)
      end

      it "runs user_observer provisioning report" do
        parameters = {}
        parameters["user_observers"] = true
        parsed = read_report("provisioning_csv", { params: parameters, order: 0, header: true })
        expect(parsed).to match_array [%w[canvas_observer_id
                                          observer_id
                                          canvas_student_id
                                          student_id
                                          status
                                          created_by_sis],
                                       [@user2.id.to_s,
                                        "user_sis_id_02",
                                        @user1.id.to_s,
                                        "user_sis_id_01",
                                        "active",
                                        "true"],
                                       [@user4.id.to_s,
                                        "user_sis_id_04",
                                        @user3.id.to_s,
                                        "user_sis_id_03",
                                        "active",
                                        "true"],
                                       [@user7.id.to_s,
                                        nil,
                                        @user6.id.to_s,
                                        nil,
                                        "active",
                                        "false"]]
        expect(parsed.length).to eq 4
      end

      it "runs user_observer sis_export report" do
        parameters = {}
        parameters["user_observers"] = true
        parsed = read_report("sis_export_csv", { params: parameters, order: 0, header: true })
        expect(parsed).to match_array [%w[observer_id student_id status],
                                       %w[user_sis_id_02 user_sis_id_01 active],
                                       %w[user_sis_id_04 user_sis_id_03 active]]
        expect(parsed.length).to eq 3
      end

      it "excludes deleted observers by default" do
        @uo1.destroy
        parameters = {}
        parameters["user_observers"] = true
        parsed = read_report("sis_export_csv", { params: parameters, order: 0 })
        expect(parsed).to match_array [%w[user_sis_id_04 user_sis_id_03 active]]
        expect(parsed.length).to eq 1
      end

      it "includes deleted observers by when param is present" do
        @uo1.destroy
        parameters = {}
        parameters["user_observers"] = true
        parameters["include_deleted"] = true
        parsed = read_report("sis_export_csv", { params: parameters, order: 0 })
        expect(parsed).to match_array [%w[user_sis_id_02 user_sis_id_01 deleted],
                                       %w[user_sis_id_04 user_sis_id_03 active]]
        expect(parsed.length).to eq 2
      end

      it "does not include unassociated observers when running from a sub-account" do
        parameters = {}
        parameters["user_observers"] = true
        parsed = read_report("sis_export_csv", { account: @sub_account, params: parameters, order: 0, header: true })
        expect(parsed).to match_array [%w[observer_id student_id status]]
      end

      it "includes associated observers when running from a sub-account" do
        course_with_student(account: @sub_account, user: @user1)
        parameters = {}
        parameters["user_observers"] = true
        parsed = read_report("sis_export_csv", { account: @sub_account, params: parameters, order: 0, header: true })
        expect(parsed).to match_array [%w[observer_id student_id status],
                                       %w[user_sis_id_02 user_sis_id_01 active]]
      end

      it "does not duplicate user rows for instructure identity pseudonyms" do
        skip unless Pseudonym.column_names.include?("is_inst_id")

        @account.pseudonyms.create!(user: @user1, unique_id: "inst_id_student", is_inst_id: true)
        @account.pseudonyms.create!(user: @user2, unique_id: "inst_id_observer", is_inst_id: true)

        parameters = {}
        parameters["user_observers"] = true
        parsed = read_report("provisioning_csv", { params: parameters, order: 0, header: true })

        # Should still only have one row per observer relationship, not duplicated for each pseudonym combination
        student_rows = parsed.select { |row| row[3] == "user_sis_id_01" }
        expect(student_rows.length).to eq 1
        expect(student_rows[0][1]).to eq "user_sis_id_02" # should be the regular SIS ID, not the Identity ID
      end
    end

    describe "admins" do
      before(:once) do
        create_an_account
        @u1 = user_with_managed_pseudonym(account: @account, sis_user_id: "U001", name: "user 1")
        @u2 = user_with_managed_pseudonym(account: @account, sis_user_id: "U002", name: "user 2")
        @admin2 = @sub_account.account_users.create(user: @u1)
        @admin2.sis_batch_id = @sis.id
        @admin2.save!
        @role1 = custom_account_role("role1", account: @account)
        @admin3 = @account.account_users.create(user: @u2, role: @role1)
        @admin3.sis_batch_id = @sis.id
        @admin3.save!
      end

      it "runs sis" do
        parameters = {}
        parameters["admins"] = true
        parameters["include_deleted"] = true
        parsed = read_report("sis_export_csv", { params: parameters, order: 3, header: true })
        expect(parsed).to match_array [%w[user_id account_id role_id role status],
                                       ["U001", "sub1", admin_role(root_account_id: @account.id).id.to_s, "AccountAdmin", "active"],
                                       ["U002", nil, @role1.id.to_s, "role1", "active"]]
      end

      it "runs provisioning" do
        parameters = {}
        parameters["admins"] = true
        parameters["include_deleted"] = true
        @admin.pseudonyms.create!(account: @account, unique_id: "deleted").destroy
        parsed = read_report("provisioning_csv", { params: parameters, order: [1, 5], header: true })
        expect(parsed).to match_array [%w[admin_user_name
                                          canvas_user_id
                                          user_id
                                          canvas_account_id
                                          account_id
                                          role_id
                                          role
                                          status
                                          created_by_sis],
                                       ["user 1",
                                        @u1.id.to_s,
                                        "U001",
                                        @sub_account.id.to_s,
                                        "sub1",
                                        admin_role(root_account_id: @account.id).id.to_s,
                                        "AccountAdmin",
                                        "active",
                                        "true"],
                                       ["user 2",
                                        @u2.id.to_s,
                                        "U002",
                                        @account.id.to_s,
                                        nil,
                                        @role1.id.to_s,
                                        "role1",
                                        "active",
                                        "true"],
                                       ["default admin",
                                        @admin.id.to_s,
                                        nil,
                                        @account.id.to_s,
                                        nil,
                                        admin_role(root_account_id: @account.id).id.to_s,
                                        "AccountAdmin",
                                        "active",
                                        "false"]]
      end

      describe "sharding" do
        specs_require_sharding

        it "runs with cross shard pseudonyms" do
          @shard1.activate do
            @root = Account.create
            @user = user_with_managed_pseudonym(active_all: true,
                                                account: @root,
                                                name: "Jimmy John",
                                                username: "other_shard@example.com",
                                                sis_user_id: "other_shard")
          end
          allow(@account).to receive_messages(trusted_account_ids: [@account.id, @root.id], trust_exists?: true)
          @admin4 = @account.account_users.create(user: @user)
          @admin4.sis_batch_id = @sis.id
          @admin4.save!

          parameters = {}
          parameters["admins"] = true
          parsed = read_report("sis_export_csv", { params: parameters, order: [3, 0], header: true })

          expect(parsed).to match_array [%w[user_id account_id role_id role status root_account],
                                         ["U001",
                                          "sub1",
                                          admin_role(root_account_id: @account.id).id.to_s,
                                          "AccountAdmin",
                                          "active",
                                          HostUrl.context_host(@account)],
                                         ["U002",
                                          nil,
                                          @role1.id.to_s,
                                          "role1",
                                          "active",
                                          HostUrl.context_host(@account)],
                                         ["other_shard",
                                          nil,
                                          admin_role(root_account_id: @account.id).id.to_s,
                                          "AccountAdmin",
                                          "active",
                                          HostUrl.context_host(@root)]]
        end
      end
    end

    it "runs multiple SIS Export reports" do
      create_some_users_with_pseudonyms
      create_some_accounts

      parameters = {}
      parameters["enrollment_term_id"] = @default_term.id
      parameters["accounts"] = true
      parameters["users"] = true
      parameters["courses"] = true
      parsed = read_report("sis_export_csv", { params: parameters, header: true, order: "skip" })

      accounts_report = parsed["accounts.csv"][1..].sort_by { |r| r[0] }
      expect(accounts_report[0]).to eq ["sub1", nil, "English", "active"]
      expect(accounts_report[1]).to eq ["sub3", nil, "math", "active"]
      expect(accounts_report[2]).to eq %w[subsub1 sub1 sESL active]

      users_report = parsed["users.csv"][1..].sort_by { |r| r[0] }
      expect(users_report.length).to eq 4
      expect(users_report).to eq([@user1, @user2, @user3, @user4].map { |u| expected_user(u) })
    end

    it "runs the SIS Export reports with no data" do
      parameters = {}
      parameters["accounts"] = true
      parameters["users"] = true
      parameters["terms"] = true
      parameters["courses"] = true
      parameters["sections"] = true
      parameters["enrollments"] = true
      parameters["groups"] = true
      parameters["group_categories"] = true
      parameters["group_membership"] = true
      parameters["xlist"] = true
      expect(AccountReports).to receive(:message_recipient).once
      parsed = read_report("sis_export_csv", { params: parameters, header: true })

      expect(parsed["accounts.csv"]).to eq [%w[account_id parent_account_id name status]]
      expect(parsed["terms.csv"]).to eq [%w[term_id name status start_date end_date]]
      expect(parsed["users.csv"]).to eq [user_headers]
      expect(parsed["courses.csv"]).to eq [%w[course_id
                                              integration_id
                                              short_name
                                              long_name
                                              account_id
                                              term_id
                                              status
                                              start_date
                                              end_date
                                              course_format
                                              blueprint_course_id]]
      expect(parsed["sections.csv"]).to eq [%w[section_id
                                               course_id
                                               integration_id
                                               name
                                               status
                                               start_date
                                               end_date]]
      expect(parsed["enrollments.csv"]).to eq [%w[course_id
                                                  user_id
                                                  role
                                                  role_id
                                                  section_id
                                                  status
                                                  associated_user_id
                                                  limit_section_privileges]]
      expect(parsed["groups.csv"]).to eq [%w[group_id group_category_id account_id course_id name status]]
      expect(parsed["group_categories.csv"]).to eq [%w[group_category_id account_id course_id category_name status]]
      expect(parsed["group_membership.csv"]).to eq [%w[group_id user_id status]]
      expect(parsed["xlist.csv"]).to eq [%w[xlist_course_id section_id status]]
    end

    it "does not return reports passed as false" do
      parameters = {}
      parameters["accounts"] = 0
      parameters["users"] = 1
      parameters["terms"] = true
      parameters["courses"] = false
      parameters["sections"] = false
      parameters["enrollments"] = false
      parameters["groups"] = true
      parameters["group_membership"] = true
      parameters["xlist"] = true
      parsed = read_report("sis_export_csv", { params: parameters, header: true })

      expect(parsed["accounts.csv"]).to be_nil
      expect(parsed["terms.csv"]).to eq [%w[term_id name status start_date end_date]]
      expect(parsed["users.csv"]).to eq [user_headers]
      expect(parsed["courses.csv"]).to be_nil
      expect(parsed["sections.csv"]).to be_nil
      expect(parsed["enrollments.csv"]).to be_nil
      expect(parsed["groups.csv"]).to eq [%w[group_id group_category_id account_id course_id name status]]
      expect(parsed["group_membership.csv"]).to eq [%w[group_id user_id status]]
      expect(parsed["xlist.csv"]).to eq [%w[xlist_course_id section_id status]]
    end
  end
end
