using Furion.ClayObject.Extensions;
using Furion.DatabaseAccessor.Extensions;
using NPOI.SS.UserModel;
using YBEE.EQM.Core;

namespace YBEE.EQM.Application;

/// <summary>
/// 学生成绩导入服务
/// </summary>
public class ExamScoreImportService : IExamScoreImportService, ITransient
{
    private readonly IRepository<ExamScore> _rep;
    private readonly ISchoolClassService _schoolClassService;
    private readonly IExamGradeService _examGradeService;

    public ExamScoreImportService(IRepository<ExamScore> rep, ISchoolClassService schoolClassService, IExamGradeService examGradeService)
    {
        _rep = rep;
        _schoolClassService = schoolClassService;
        _examGradeService = examGradeService;
    }

    /// <summary>
    /// 批量导入前期未上报学生名单的各科成绩（初始化）
    /// </summary>
    /// <param name="filePath"></param>
    /// <param name="examPlanId"></param>
    /// <returns></returns>
    public async Task UploadImportWithoutStudentTotalScore(string filePath, int examPlanId)
    {
        /* ----------------------------------------------------------
         * 处理步骤：
         * 1.验证表头
         * 2.读取数据
         * 3.处理班级
         * 4.处理学生
         * 5.批量插入
        ---------------------------------------------------------- */

        try
        {
            #region 1.验证表结构
            using FileStream fs = new(filePath, FileMode.Open, FileAccess.Read);
            IWorkbook workbook = ExcelUtil.GetWorkbook(filePath, fs);
            var sheet = workbook.GetSheetAt(0);
            var rows = sheet.GetRowEnumerator();

            // 少于2行验证
            if (sheet.LastRowNum < 2)
            {
                //"第一行应为填写说明，第二行应为标题行，请勿修改模板结构。"
                return;
            }

            // 读取表头
            rows.MoveNext();
            IRow headerRow = (IRow)rows.Current;
            int index = 0;
            int SAMPLE_TYPE_INDEX = index++;
            int SCHOOL_ID_INDEX = index++;
            int SCHOOL_NAME_INDEX = index++;
            int SCHOOL_BID_INDEX = index++;
            int SCHOOL_BNAME_INDEX = index++;
            int NAME_INDEX = index++;
            int CERT_TYPE_INDEX = index++;
            int ID_NUM_INDEX = index++;
            int EXAM_NUMBER_INDEX = index++;
            int COURSE_COMB_INDEX = index++;
            int GRADE_INDEX = index++;
            int CLASS_INDEX = index++;
            int COURSE_START_INDEX = index;
            Dictionary<int, string> headers = new()
            {
                { SAMPLE_TYPE_INDEX, "抽样类型" },
                { SCHOOL_ID_INDEX, "学校ID" },
                { SCHOOL_NAME_INDEX, "学校" },
                { SCHOOL_BID_INDEX, "校区ID" },
                { SCHOOL_BNAME_INDEX, "校区" },
                { NAME_INDEX, "姓名" },
                { CERT_TYPE_INDEX, "证件类型" },
                { ID_NUM_INDEX, "证件号码" },
                { EXAM_NUMBER_INDEX, "考号" },
                { COURSE_COMB_INDEX, "选科组合" },
                { GRADE_INDEX, "年级" },
                { CLASS_INDEX, "班级" },
            };
            List<string> headerErrors = new();
            for (int i = 0; i < COURSE_START_INDEX; i++)
            {
                if (headerRow.GetCell(i)?.ToString() != headers[i])
                {
                    char letter = (char)('A' + i);
                    headerErrors.Add(letter.ToString());
                }
            }
            if (headerErrors.Any())
            {
                string columnErrors = string.Join("、", headerErrors);
                //result.ErrorMessage.Add($"第1行标题行{columnErrors}列名错误。从A列开始依次应为抽样类型、学校ID、学校、姓名、证件类型、证件号码、考号、年级、班级。");
                //return result;
                return;
            }
            //result.StructureCorrect = true;
            #endregion

            #region 2.读取数据
            var examPlan = await _rep.Change<ExamPlan>().DetachedEntities.FirstOrDefaultAsync(t => t.Id == examPlanId) ?? throw Oops.Oh(ErrorCode.E2001, "监测计划");
            var examGradeDict = (await _examGradeService.GetListByExamPlanId(examPlanId)).ToDictionary(t => t.Grade.GradeNumber);
            var courseDict = (await _rep.Change<Course>().DetachedEntities.ToListAsync()).ToDictionary(t => t.Name);

            // 获取需要导入的科目列表
            Dictionary<int, Course> courses = new();
            for (int gi = COURSE_START_INDEX; gi < headerRow.LastCellNum; gi++)
            {
                var cn = headerRow.GetCell(gi).ToString() ?? "";
                if (string.IsNullOrEmpty(cn))
                {
                    continue;
                }
                courses.Add(gi, courseDict[cn]);
            }

            int rn = 1;
            List<ExamScoreImportDto> sourceItems = new();
            while (rows.MoveNext())
            {
                rn++;
                IRow row = (IRow)rows.Current;
                string rv = row.GetCell(0)?.ToString().Trim() ?? "";
                if (rv == "")
                {
                    break;
                }

                for (int gi = COURSE_START_INDEX; gi < headerRow.LastCellNum; gi++)
                {
                    var course = courses[gi];
                    var sbid = row.GetCell(SCHOOL_BID_INDEX)?.NumericCellValue;
                    var ccid = row.GetCell(COURSE_COMB_INDEX)?.NumericCellValue;
                    ExamScoreImportDto item = new()
                    {
                        RowNumber = rn,
                        SampleType = (ExamSampleType)row.GetCell(SAMPLE_TYPE_INDEX).NumericCellValue,
                        SysOrgId = (short)row.GetCell(SCHOOL_ID_INDEX).NumericCellValue,
                        SysOrgBranchId = sbid > 0 ? (short)sbid : null,
                        StudentName = StringUtil.ClearWhite(row.GetCell(NAME_INDEX)?.ToString() ?? ""),
                        CertificateType = (CertificateType)row.GetCell(CERT_TYPE_INDEX).NumericCellValue,
                        IdNumber = StringUtil.ClearIdNumber(row.GetCell(ID_NUM_INDEX)?.ToString() ?? ""),
                        ExamNumber = row.GetCell(EXAM_NUMBER_INDEX)?.ToString() ?? "",
                        GradeNumber = (short)row.GetCell(GRADE_INDEX).NumericCellValue,
                        ClassNumber = (short)row.GetCell(CLASS_INDEX).NumericCellValue,
                        CourseId = course.Id,
                        NceeCourseCombId = ccid > 0 ? (short)ccid : null,
                    };
                    string scoreStr = row.GetCell(gi)?.ToString() ?? "";
                    if (decimal.TryParse(scoreStr, out decimal score))
                    {
                        item.Score = score;
                    }
                    else if (scoreStr.Contains('特') || scoreStr.Contains('缺'))
                    {
                        item.IsExcluded = true;
                    }

                    if (item.IdNumber.Length == 18)
                    {
                        if (int.TryParse(item.IdNumber.AsSpan(16, 1), out int gn))
                        {
                            item.Gender = (short)(gn % 2 == 0 ? 2 : 1);
                        }
                    }

                    var eg = examGradeDict[item.GradeNumber];
                    item.ExamGradeId = eg.Id;
                    item.GradeId = eg.GradeId;
                    item.GradeBeginYear = eg.GradeBeginYear;
                    item.GradeEndYear = eg.GradeEndYear;
                    item.SchoolClassId = _schoolClassService.GetSchoolClassId(examPlan.EducationStage, item.SysOrgId, eg.GradeBeginYear, item.ClassNumber);

                    sourceItems.Add(item);
                }
            }
            workbook.Close();
            fs.Close();
            #endregion

            #region 3.处理班级
            var scs = sourceItems.GroupBy(t => t.SchoolClassId).Select(t => new BatchInsertSchoolClassInput
            {
                Id = t.Key,
                SysOrgId = t.FirstOrDefault().SysOrgId,
                SysOrgBranchId = t.FirstOrDefault().SysOrgBranchId,
                EducationStage = examPlan.EducationStage,
                EducationYears = examPlan.EducationYears,
                ClassNumber = t.FirstOrDefault().ClassNumber,
                GradeBeginYear = t.FirstOrDefault().GradeBeginYear,
                GradeEndYear = t.FirstOrDefault().GradeEndYear,
            }).Distinct().ToList();
            // 插入没有的班级
            await _schoolClassService.BatchInsert(scs);
            #endregion

            #region 4.处理学生
            // 清理学生
            string deleteStudentSql = $"DELETE FROM exam_student WHERE exam_plan_id = {examPlanId};";
            await _rep.SqlNonQueryAsync(deleteStudentSql);

            var uid = CurrentSysUserInfo.SysUserId;
            if (uid == 0)
            {
                uid = 1;
            }

            var stus = sourceItems.GroupBy(t => t.RowNumber).Select(t => t.FirstOrDefault()).ToList();
            List<string> insertValues = new();
            int scount = stus.Count;
            for (int i = 0; i < scount; i++)
            {
                var t = stus[i];

                string sobid = "NULL";
                if (t.SysOrgBranchId.HasValue)
                {
                    sobid = t.SysOrgBranchId.ToString();
                }

                insertValues.Add($"({examPlanId}, {t.SysOrgId}, {sobid}, {t.ExamGradeId}, {t.GradeId}, {t.SchoolClassId}, {t.ClassNumber}, '{t.ExamNumber}', '{t.StudentName}', {(short)t.CertificateType}, '{t.IdNumber}', {t.Gender}, {uid}, CURRENT_TIMESTAMP, 0)");
                if ((i + 1) % 1000 == 0 || i == scount - 1)
                {
                    string insertSql = $@"
INSERT INTO exam_student(exam_plan_id, sys_org_id, sys_org_branch_id, exam_grade_id, grade_id, school_class_id, class_number, exam_number, `name`, certificate_type, id_number, gender, create_sys_user_id, create_time, is_deleted) VALUES 
{string.Join(",", insertValues)}
";
                    await _rep.SqlNonQueryAsync(insertSql);
                    insertValues.Clear();
                }
            }
            #endregion

            #region 5.批量导入
            // 清理成绩
            string deleteScoreSql = $"DELETE FROM exam_score WHERE exam_plan_id = {examPlanId};";
            await _rep.SqlNonQueryAsync(deleteScoreSql);

            List<string> selects = new();
            scount = sourceItems.Count;
            for (int i = 0; i < scount; i++)
            {
                var t = sourceItems[i];

                string sobid = "NULL";
                if (t.SysOrgBranchId.HasValue)
                {
                    sobid = t.SysOrgBranchId.ToString();
                }
                string ccid = "NULL";
                if (t.NceeCourseCombId.HasValue)
                {
                    ccid = t.NceeCourseCombId.ToString();
                }
                var ied = t.IsExcluded ? 1 : 0;

                selects.Add($@"
SELECT {(short)t.SampleType} est, {t.SysOrgId} soid, {sobid} as sobid, {t.GradeId} gid, {examPlan.SemesterId} smid, {t.SchoolClassId} scid, {t.ClassNumber} cn, '{t.ExamNumber}' en, {ccid} ccid, {t.CourseId} cid, {t.Score} s, {ied} ied ");

                if ((i + 1) % 500 == 0 || i == scount - 1)
                {
                    string insertSql = $@"
INSERT INTO exam_score(exam_plan_id, education_stage, exam_sample_type, sys_org_id, sys_org_branch_id, grade_id, semester_id, school_class_id, class_number, exam_student_id, ncee_course_comb_id, course_id, score, is_excluded)
SELECT {examPlanId}, {(short)examPlan.EducationStage}, T1.est, T1.soid, T1.sobid, T1.gid, T1.smid, T1.scid, T1.cn, T2.id, T1.ccid, T1.cid, T1.s, T1.ied
FROM
({string.Join("UNION ALL", selects)}
) AS T1
JOIN (SELECT id, school_class_id, exam_number FROM exam_student WHERE exam_plan_id = {examPlanId}) AS T2
ON T1.scid = T2.school_class_id AND T1.en = T2.exam_number
";
                    await _rep.SqlNonQueryAsync(insertSql);
                    selects.Clear();
                }
            }
            #endregion
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            File.Delete(filePath);
        }
    }

    /// <summary>
    /// 批量导入学生总成绩
    /// </summary>
    /// <param name="filePath"></param>
    /// <param name="examPlanId"></param>
    /// <returns></returns>
    public async Task UploadImportStudentTotalScore(string filePath, int examPlanId)
    {
        /* ----------------------------------------------------------
         * 处理步骤：
         * 1.验证表头
         * 2.读取数据
         * 3.处理班级
         * 4.批量插入
        ---------------------------------------------------------- */

        try
        {
            #region 1.验证表结构
            using FileStream fs = new(filePath, FileMode.Open, FileAccess.Read);
            IWorkbook workbook = ExcelUtil.GetWorkbook(filePath, fs);
            var sheet = workbook.GetSheetAt(0);
            var rows = sheet.GetRowEnumerator();

            // 少于2行验证
            if (sheet.LastRowNum < 2)
            {
                //"第一行应为填写说明，第二行应为标题行，请勿修改模板结构。"
                return;
            }

            // 读取表头
            rows.MoveNext();
            IRow headerRow = (IRow)rows.Current;
            int index = 0;
            int SAMPLE_TYPE_INDEX = index++;
            int SCHOOL_ID_INDEX = index++;
            int SCHOOL_NAME_INDEX = index++;
            int SCHOOL_BID_INDEX = index++;
            int SCHOOL_BNAME_INDEX = index++;
            int NAME_INDEX = index++;
            int CERT_TYPE_INDEX = index++;
            int ID_NUM_INDEX = index++;
            int EXAM_NUMBER_INDEX = index++;
            int COURSE_COMB_INDEX = index++;
            int GRADE_INDEX = index++;
            int CLASS_INDEX = index++;
            int COURSE_START_INDEX = index;
            Dictionary<int, string> headers = new()
            {
                { SAMPLE_TYPE_INDEX, "抽样类型" },
                { SCHOOL_ID_INDEX, "学校ID" },
                { SCHOOL_NAME_INDEX, "学校" },
                { SCHOOL_BID_INDEX, "校区ID" },
                { SCHOOL_BNAME_INDEX, "校区" },
                { NAME_INDEX, "姓名" },
                { CERT_TYPE_INDEX, "证件类型" },
                { ID_NUM_INDEX, "证件号码" },
                { EXAM_NUMBER_INDEX, "考号" },
                { COURSE_COMB_INDEX, "选科组合" },
                { GRADE_INDEX, "年级" },
                { CLASS_INDEX, "班级" },
            };
            List<string> headerErrors = new();
            for (int i = 0; i < COURSE_START_INDEX; i++)
            {
                if (headerRow.GetCell(i)?.ToString() != headers[i])
                {
                    char letter = (char)('A' + i);
                    headerErrors.Add(letter.ToString());
                }
            }
            if (headerErrors.Any())
            {
                string columnErrors = string.Join("、", headerErrors);
                //result.ErrorMessage.Add($"第1行标题行{columnErrors}列名错误。从A列开始依次应为抽样类型、学校ID、学校、姓名、证件类型、证件号码、考号、年级、班级。");
                //return result;
                return;
            }
            //result.StructureCorrect = true;
            #endregion

            #region 2.读取数据
            var examPlan = await _rep.Change<ExamPlan>().DetachedEntities.FirstOrDefaultAsync(t => t.Id == examPlanId) ?? throw Oops.Oh(ErrorCode.E2001, "监测计划");
            var examGradeDict = (await _examGradeService.GetListByExamPlanId(examPlanId)).ToDictionary(t => t.Grade.GradeNumber);
            var courseDict = (await _rep.Change<Course>().DetachedEntities.ToListAsync()).ToDictionary(t => t.Name);
            var examCourses = await _rep.Change<ExamCourse>().DetachedEntities.Where(t => t.ExamPlanId == examPlanId).Select(t => new { t.GradeId, t.CourseId }).ToListAsync();

            // 获取需要导入的科目列表
            Dictionary<int, Course> courses = new();
            for (int gi = COURSE_START_INDEX; gi < headerRow.LastCellNum; gi++)
            {
                var cn = headerRow.GetCell(gi).ToString() ?? "";
                if (string.IsNullOrEmpty(cn))
                {
                    continue;
                }
                courses.Add(gi, courseDict[cn]);
            }

            int rn = 1;
            List<ExamScoreImportDto> sourceItems = new();
            while (rows.MoveNext())
            {
                rn++;
                IRow row = (IRow)rows.Current;
                string rv = row.GetCell(0)?.ToString().Trim() ?? "";
                if (rv == "")
                {
                    break;
                }

                var gradeNumber = (short)row.GetCell(GRADE_INDEX).NumericCellValue;
                for (int gi = COURSE_START_INDEX; gi < headerRow.LastCellNum; gi++)
                {
                    var course = courses[gi];
                    var eg = examGradeDict[gradeNumber];

                    // 跳过未监测科目
                    if (!examCourses.Any(t => t.GradeId == eg.GradeId && t.CourseId == course.Id))
                    {
                        continue;
                    }

                    var sbid = row.GetCell(SCHOOL_BID_INDEX)?.NumericCellValue;
                    var ccid = row.GetCell(COURSE_COMB_INDEX)?.NumericCellValue;
                    ExamScoreImportDto item = new()
                    {
                        RowNumber = rn,
                        SampleType = (ExamSampleType)row.GetCell(SAMPLE_TYPE_INDEX).NumericCellValue,
                        SysOrgId = (short)row.GetCell(SCHOOL_ID_INDEX).NumericCellValue,
                        SysOrgBranchId = sbid > 0 ? (short)sbid : null,
                        StudentName = StringUtil.ClearWhite(row.GetCell(NAME_INDEX)?.ToString() ?? ""),
                        CertificateType = (CertificateType)row.GetCell(CERT_TYPE_INDEX).NumericCellValue,
                        IdNumber = StringUtil.ClearIdNumber(row.GetCell(ID_NUM_INDEX)?.ToString() ?? ""),
                        ExamNumber = row.GetCell(EXAM_NUMBER_INDEX)?.ToString() ?? "",
                        GradeNumber = gradeNumber,
                        ClassNumber = (short)row.GetCell(CLASS_INDEX).NumericCellValue,
                        CourseId = course.Id,
                        NceeCourseCombId = ccid > 0 ? (short)ccid : null,
                    };
                    string scoreStr = row.GetCell(gi)?.ToString() ?? "";
                    if (decimal.TryParse(scoreStr, out decimal score))
                    {
                        item.Score = score;
                    }
                    else if (scoreStr.Contains('特') || scoreStr.Contains('缺'))
                    {
                        item.IsExcluded = true;
                    }

                    if (item.IdNumber.Length == 18)
                    {
                        if (int.TryParse(item.IdNumber.AsSpan(16, 1), out int gn))
                        {
                            item.Gender = (short)(gn % 2 == 0 ? 2 : 1);
                        }
                    }

                    item.ExamGradeId = eg.Id;
                    item.GradeId = eg.GradeId;
                    item.GradeBeginYear = eg.GradeBeginYear;
                    item.GradeEndYear = eg.GradeEndYear;
                    item.SchoolClassId = _schoolClassService.GetSchoolClassId(examPlan.EducationStage, item.SysOrgId, eg.GradeBeginYear, item.ClassNumber);

                    sourceItems.Add(item);
                }
            }
            workbook.Close();
            fs.Close();
            #endregion

            #region 3.处理班级
            var scs = sourceItems.GroupBy(t => t.SchoolClassId).Select(t => new BatchInsertSchoolClassInput
            {
                Id = t.Key,
                SysOrgId = t.FirstOrDefault().SysOrgId,
                SysOrgBranchId = t.FirstOrDefault().SysOrgBranchId,
                EducationStage = examPlan.EducationStage,
                EducationYears = examPlan.EducationYears,
                ClassNumber = t.FirstOrDefault().ClassNumber,
                GradeBeginYear = t.FirstOrDefault().GradeBeginYear,
                GradeEndYear = t.FirstOrDefault().GradeEndYear,
            }).Distinct().ToList();
            // 插入没有的班级
            await _schoolClassService.BatchInsert(scs);
            #endregion

            #region 4.批量导入
            // 清理成绩
            string deleteScoreSql = $"DELETE FROM exam_score WHERE exam_plan_id = {examPlanId};";
            await _rep.SqlNonQueryAsync(deleteScoreSql);

            List<string> selects = new();
            var scount = sourceItems.Count;
            for (int i = 0; i < scount; i++)
            {
                var t = sourceItems[i];

                string sobid = "NULL";
                if (t.SysOrgBranchId.HasValue)
                {
                    sobid = t.SysOrgBranchId.ToString();
                }
                string ccid = "NULL";
                if (t.NceeCourseCombId.HasValue)
                {
                    ccid = t.NceeCourseCombId.ToString();
                }
                var ied = t.IsExcluded ? 1 : 0;

                selects.Add($@"
SELECT {(short)t.SampleType} est, {t.SysOrgId} soid, {sobid} as sobid, {t.GradeId} gid, {examPlan.SemesterId} smid, {t.SchoolClassId} scid, {t.ClassNumber} cn, '{t.ExamNumber}' en, {ccid} ccid, {t.CourseId} cid, {t.Score} s, {ied} ied ");

                if ((i + 1) % 500 == 0 || i == scount - 1)
                {
                    string insertSql = $@"
SET @examSampleId = (SELECT MAX(id) FROM exam_sample WHERE exam_plan_id = {examPlanId} AND is_selected = 1);
INSERT INTO exam_score(exam_plan_id, education_stage, exam_sample_type, sys_org_id, sys_org_branch_id, grade_id, semester_id, school_class_id, class_number, exam_student_id, ncee_course_comb_id, course_id, score, is_excluded)
SELECT {examPlanId}, {(short)examPlan.EducationStage}, T1.est, T1.soid, T1.sobid, T1.gid, T1.smid, T1.scid, T1.cn, T2.id, T1.ccid, T1.cid, T1.s, T1.ied
FROM
({string.Join("UNION ALL", selects)}
) AS T1
JOIN (SELECT exam_number, exam_student_id AS id FROM exam_sample_student WHERE exam_sample_id = @examSampleId) AS T2
ON T1.en = T2.exam_number;
";
                    await _rep.SqlNonQueryAsync(insertSql);
                    selects.Clear();
                }
            }
            #endregion

            #region 5.计算总分和分段
            await $@"
-- 更新各科成绩分段
UPDATE exam_score AS T1 
JOIN 
(
	SELECT T1.id, T1.grade_id, T1.course_id, get_exam_score_range_id(T2.exam_score_range_type, T1.score) AS exam_score_range_id
	FROM exam_score AS T1
	JOIN exam_course AS T2 ON T1.exam_plan_id = T2.exam_plan_id AND T1.grade_id = T2.grade_id AND T1.course_id = T2.course_id
	WHERE T1.exam_plan_id = {examPlanId}
) AS T2 ON T1.id = T2.id
SET T1.exam_score_range_id = T2.exam_score_range_id
WHERE T1.exam_plan_id = {examPlanId};

-- 更新总成绩和分段
DELETE FROM exam_score_total WHERE exam_plan_id = {examPlanId};
INSERT INTO exam_score_total(
	exam_plan_id,
	education_stage, 
	exam_sample_type, 
	sys_org_id, 
	sys_org_branch_id, 
	grade_id, semester_id, 
	school_class_id, 
	class_number, 
	exam_student_id, 
	ncee_course_comb_id, 
	course_count, 
	score,
	exam_score_range_id,
	is_excluded
)
SELECT {examPlanId}, T1.*, get_exam_score_range_id(T2.exam_score_range_type, T1.score) AS exam_score_range_id, 0
FROM
(
	SELECT 
		education_stage, 
		exam_sample_type, 
		sys_org_id, 
		sys_org_branch_id, 
		grade_id, semester_id, 
		school_class_id, 
		class_number, 
		exam_student_id, 
		ncee_course_comb_id, 
		COUNT(1) AS course_count, 
		SUM(IFNULL(score, 0)) AS score
	FROM exam_score
	WHERE exam_plan_id = {examPlanId}
	GROUP BY education_stage, exam_sample_type, sys_org_id, sys_org_branch_id, grade_id, semester_id, school_class_id, class_number, exam_student_id, ncee_course_comb_id
) AS T1
JOIN
(
	SELECT grade_id, exam_score_range_type FROM exam_grade WHERE exam_plan_id = {examPlanId}
) AS T2 ON T1.grade_id = T2.grade_id;
".SetCommandTimeout(6000).SqlNonQueryAsync();
            #endregion
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            File.Delete(filePath);
        }
    }
}