TEAMModelOS/TEAMModelOS.SDK/Models/Service/JointService.cs

using Azure.Messaging.ServiceBus;
using Azure.Storage.Blobs.Models;
using Azure;
using Microsoft.Azure.Cosmos;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Text.Json;
using System.Threading.Tasks;
using TEAMModelOS.SDK.DI;
using TEAMModelOS.SDK.Extension;
using TEAMModelOS.SDK.Models.Service.BI;
using TEAMModelOS.SDK.Services;
using static TEAMModelOS.SDK.Models.JointEventGroupBase;
using Azure.Core;
using Microsoft.Extensions.Configuration;
using static TEAMModelOS.SDK.Models.JointEvent;
using TEAMModelOS.SDK.Models.Dtos;

namespace TEAMModelOS.SDK.Models.Service
{
    public static class JointService
    {
        //取得JointExam生成Exam
        public static async Task GenerateExamFromJointExamAsync(CosmosClient client, AzureStorageFactory _azureStorage, AzureServiceBusFactory _serviceBus, CoreAPIHttpService _coreAPIHttpService, AzureRedisFactory _azureRedis, IConfiguration _configuration, DingDing _dingDing, JointExam jointExam, string creatorId)
        {
            try
            {
                long now = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds();
                //取得JointExam
                List<JointEventClassBase> classes = new List<JointEventClassBase>();
                List<JointEventGroupBase> stuLists = new List<JointEventGroupBase>();
                //取得JointCourse ※examType == "custom" 之後再處理
                List<JointEventGroupDb> jointCourses = new List<JointEventGroupDb>();
                if (!jointExam.examType.Equals("custom")) //熱身賽:老師報名名單
                {
                    string jointCourseSql = $"SELECT * FROM c WHERE c.jointEventId = '{jointExam.jointEventId}' AND c.jointGroupId = '{jointExam.jointGroupId}' AND ( IS_DEFINED(c.type) = false OR c.type = 'regular' )";
                    if (!string.IsNullOrWhiteSpace(creatorId)) jointCourseSql += $" AND c.creatorId = '{creatorId}' ";
                    await foreach (var item in client.GetContainer("TEAMModelOS", Constant.Teacher).GetItemQueryIteratorSql<JointEventGroupDb>(queryText: jointCourseSql, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"JointCourse") }))
                    {
                        jointCourses.Add(item);
                    }
                }
                else //決賽:決賽名單
                {
                    string jointCourseSql = $"SELECT * FROM c WHERE c.jointEventId = '{jointExam.jointEventId}' AND c.jointGroupId = '{jointExam.jointGroupId}' AND IS_DEFINED(c.type) = true AND c.type = 'custom' ";
                    if (!string.IsNullOrWhiteSpace(creatorId)) jointCourseSql += $" AND c.creatorId = '{creatorId}' ";
                    await foreach (var item in client.GetContainer("TEAMModelOS", Constant.Teacher).GetItemQueryIteratorSql<JointEventGroupDb>(queryText: jointCourseSql, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"JointCourse") }))
                    {
                        jointCourses.Add(item);
                    }
                }
                //評量資料生成 ExamInfo actExamInfo ※一個課程一個Exam
                List<ExamInfo> examList = new List<ExamInfo>();
                foreach (JointEventGroupDb jointCourse in jointCourses)
                {
                    string actExamCreatorId = jointCourse.creatorId;
                    //個人課程
                    if(jointCourse.scope.Equals("private"))
                    {
                        foreach (JointEventGroupCourse jointExamGroupCourse in jointCourse.courseLists)
                        {
                            string actExamCourseId = jointExamGroupCourse.courseId;
                            string actExamCourseName = jointExamGroupCourse.courseName;
                            //評量資料生成
                            ExamInfo actExamInfo = new ExamInfo();
                            ///取得已生成的Exam ※
                            string examSql = $"SELECT DISTINCT c.id, c.source, c.name, c.jointExamId, c.subjects, c.stuLists, c.targets, c.papers, c.year, c.startTime, c.endTime, c.code, c.owner, c.scope, c.creatorId FROM c JOIN s IN c.subjects WHERE c.jointExamId = '{jointExam.id}' AND s.id = '{actExamCourseId}'";
                            await foreach (var item in client.GetContainer("TEAMModelOS", Constant.Common).GetItemQueryIteratorSql<ExamInfo>(queryText: examSql, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Exam-{actExamCreatorId}") }))
                            {
                                actExamInfo = item;
                            }
                            ///尚無評量資料
                            if (string.IsNullOrWhiteSpace(actExamInfo.id))
                            {
                                actExamInfo.code = $"Exam-{actExamCreatorId}";
                                actExamInfo.owner = "teacher";
                                actExamInfo.scope = jointCourse.scope;
                                actExamInfo.creatorId = actExamCreatorId;
                                actExamInfo.id = Guid.NewGuid().ToString();
                            }
                            actExamInfo.source = jointExam.source;
                            actExamInfo.name = jointExam.name;
                            actExamInfo.jointExamId = jointExam.id;
                            actExamInfo.subjects = new List<ExamSubject>() { new ExamSubject() { id = actExamCourseId, name = actExamCourseName, classCount = jointExamGroupCourse.groupLists.Count } };
                            ///評量stuLists
                            foreach (JointEventGroupCourseGroup actGroup in jointExamGroupCourse.groupLists)
                            {
                                if(!actExamInfo.stuLists.Contains(actGroup.id))
                                {
                                    actExamInfo.stuLists.Add(actGroup.id);
                                }
                                List<string> targetRow = new List<string>() { actExamCourseId, actGroup.id };
                                var targetRowJson = JsonSerializer.SerializeToElement(targetRow);
                                bool add = true;
                                foreach(JsonElement target in actExamInfo.targets)
                                {
                                    if(target.ToJsonString().Equals(targetRowJson.ToJsonString()))
                                    {
                                        add = false;
                                        break;
                                    }
                                }
                                if(add)
                                {
                                    actExamInfo.targets.Add(targetRowJson);
                                }
                            }
                            ///試卷
                            actExamInfo.papers = Newtonsoft.Json.JsonConvert.DeserializeObject<List<PaperSimple>>(Newtonsoft.Json.JsonConvert.SerializeObject(jointExam.papers));
                            ///時間
                            actExamInfo.year = DateTimeOffset.UtcNow.Year;
                            actExamInfo.startTime = jointExam.startTime;
                            actExamInfo.endTime = jointExam.endTime;
                            ///是否重複作答
                            actExamInfo.overwriteDisable = (jointExam.examOverwrite.Equals(false)) ? true : false;
                            ///(前端)是否可見 ※只有決賽不可見
                            actExamInfo.jointVisiable = (jointExam.examType.Equals("custom")) ? false : true;
                            ///cloudas ※只有決賽有cloudas
                            actExamInfo.cloudas = (jointExam.examType.Equals("custom")) ? true : false;
                            examList.Add(actExamInfo);
                        }
                    }
                    //[待做] 學校班級
                }

                //生成評量
                if (examList.Count > 0)
                {
                    foreach (ExamInfo exam in examList)
                    {
                        await GenerateExam(client, _azureStorage, _serviceBus, _coreAPIHttpService, _azureRedis, _configuration, _dingDing, jointExam, exam);
                    }
                }
            }
            catch (Exception)
            {
                
            }
        }

        //生成評量(單)
        private static async Task<string> GenerateExam(CosmosClient client, AzureStorageFactory _azureStorage, AzureServiceBusFactory _serviceBus, CoreAPIHttpService _coreAPIHttpService, AzureRedisFactory _azureRedis, IConfiguration _configuration, DingDing _dingDing, JointExam jointExam, ExamInfo exam)
        {
            long now = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds();
            string Result = string.Empty;
            exam.createTime = now;
            if (exam.startTime <= 0) exam.startTime = now;
            List<(string pId, List<string> gid)> ps = new();
            var group = exam.groupLists;
            if (group.Count > 0)
            {
                foreach (var keys in group)
                {
                    foreach (KeyValuePair<string, List<string>> pp in keys)
                    {
                        ps.Add((pp.Key, pp.Value));
                    }
                }
            }
            List<string> classes = ExamService.getClasses(exam.classes, exam.stuLists);
            (List<RMember> tchList, List<RGroupList> classLists) = await GroupListService.GetMemberByListids(_coreAPIHttpService, client, _dingDing, classes, exam.school, ps);
            exam.stuCount = tchList.Count;
            string mode = string.Empty;
            ResponseMessage response = null;
            if (string.IsNullOrEmpty(exam.id))
            {
                mode = "add";
            }
            else
            {
                response = await client.GetContainer("TEAMModelOS", "Common").ReadItemStreamAsync(exam.id, new PartitionKey($"{exam.code}"));
                if (response.StatusCode == System.Net.HttpStatusCode.OK) mode = "upd";
                else mode = "add";
            }
            //DB操作
            if (mode.Equals("add")) //新建
            {
                if (string.IsNullOrWhiteSpace(exam.id)) exam.id = Guid.NewGuid().ToString();
                exam.progress = (exam.startTime > now) ? "pending" : "going";
                var messageBlob = new ServiceBusMessage();
                if (exam.scope.Equals("school") && !string.IsNullOrWhiteSpace(exam.school))
                {
                    exam.size = await _azureStorage.GetBlobContainerClient(exam.school).GetBlobsSize($"exam/{exam.id}");
                    await BlobService.RefreshBlobRoot(new BlobRefreshMessage { progress = "insert", root = $"exam", name = exam.school }, _serviceBus, _configuration, _azureRedis);
                }
                else
                {
                    exam.size = await _azureStorage.GetBlobContainerClient(exam.creatorId).GetBlobsSize($"exam/{exam.id}");
                    await BlobService.RefreshBlobRoot(new BlobRefreshMessage { progress = "insert", root = $"exam", name = exam.creatorId }, _serviceBus, _configuration, _azureRedis);
                }
                int n = 0;
                List<string> sheetIds = new List<string>();
                foreach (PaperSimple simple in exam.papers)
                {
                    simple.blob = $"/exam/{exam.id}/paper/{exam.subjects[n].id}";
                    n++;
                    simple.sheet = null;
                }
                exam = await client.GetContainer(Constant.TEAMModelOS, "Common").CreateItemAsync(exam, new PartitionKey($"{exam.code}"));
                await BIStats.SetTypeAddStats(client, _dingDing, exam.school, "Exam", 1);//BI统计增/减量
            }
            else if (response != null) //更新
            {
                using var json = await JsonDocument.ParseAsync(response.Content);
                ExamInfo info = json.ToObject<ExamInfo>();
                if (info.progress.Equals("going"))
                {
                    Result = "活动正在进行中，无法修改";
                }
                var messageBlob = new ServiceBusMessage();
                if (exam.scope.Equals("school") && !string.IsNullOrWhiteSpace(exam.school))
                {
                    exam.size = await _azureStorage.GetBlobContainerClient(exam.school).GetBlobsSize($"exam/{exam.id}");
                    await BlobService.RefreshBlobRoot(new BlobRefreshMessage { progress = "update", root = $"exam", name = exam.school }, _serviceBus, _configuration, _azureRedis);
                }
                else
                {
                    exam.size = await _azureStorage.GetBlobContainerClient(exam.creatorId).GetBlobsSize($"exam/{exam.id}");
                    await BlobService.RefreshBlobRoot(new BlobRefreshMessage { progress = "update", root = $"exam", name = exam.creatorId }, _serviceBus, _configuration, _azureRedis);
                }
                exam.progress = info.progress;
                int n = 0;
                List<string> sheetIds = new List<string>();
                foreach (PaperSimple simple in exam.papers)
                {
                    if (!string.IsNullOrEmpty(simple.subjectId))
                    {
                        simple.blob = $"/exam/{exam.id}/paper/{simple.subjectId}/{simple.id}";
                    }
                    else
                    {
                        simple.blob = $"/exam/{exam.id}/paper/{exam.subjects[n].id}";
                        n++;
                    }
                    simple.sheet = null;
                }
                exam = await client.GetContainer(Constant.TEAMModelOS, "Common").ReplaceItemAsync(exam, exam.id, new PartitionKey($"{exam.code}"));
            }
            //Blob操作 ※取得試卷源(blob)、複製到評測紀錄下
            ///試卷源字典
            List<Dictionary<string, string>> sourcePaperInfo = new List<Dictionary<string, string>>();
            foreach (PaperSimple paperInfo in jointExam.papers)
            {
                string paperBlobPath = (!paperInfo.blob.EndsWith("/")) ? paperInfo.blob + "/" : paperInfo.blob;
                paperBlobPath = (paperInfo.blob.StartsWith("/")) ? paperBlobPath.Remove(0, 1) : paperBlobPath;
                sourcePaperInfo.Add(new Dictionary<string, string>() { { "id", paperInfo.id }, { "blob", paperBlobPath }, { "itemcount", paperInfo.point.Count.ToString() } });
            }
            bool paperDataCopyErrFlg = false; //試卷資料拷貝錯誤Flag  true:拷貝錯誤
            //Blob拷貝程序
            int paperIndex = 0;
            foreach (Dictionary<string, string> sourcePaperInfoDic in sourcePaperInfo)
            {
                //拷貝源：Container => jointExam.creatorId  Path:papers.blob
                //拷貝對象：Container => exam.creatorId, Path:exam/{exam.id}/paper/{exam.subjects[paperIndex].id}/
                string targetScope = exam.scope; //評測對象 school：校本班級  private：私人課程
                var sourceBlobContainer = _azureStorage.GetBlobContainerClient(jointExam.creatorId); //統測活動來源一定是個人
                var blobPrivateContainer = (targetScope.Equals("school")) ? _azureStorage.GetBlobContainerClient(exam.school) : _azureStorage.GetBlobContainerClient(exam.creatorId);
                string sourceBlobPath = sourcePaperInfoDic["blob"];
                string subjectId = exam.subjects[paperIndex].id;
                string destBlobPath = $"exam/{exam.id}/paper/{subjectId}/"; //拷貝對象路徑 path:exam/{評測ID}/paper/{subjectID}/
                Pageable<BlobItem> sourceBlobs = sourceBlobContainer.GetBlobs(prefix: sourceBlobPath);
                if (sourceBlobs.Count() > 0)
                {
                    foreach (var blob in sourceBlobs)
                    {
                        var sourceFileBlob = sourceBlobContainer.GetBlobClient(blob.Name);
                        if (sourceFileBlob.Exists())
                        {
                            var sourceFileUri = sourceBlobContainer.GetBlobClient(blob.Name).Uri;
                            string fileName = blob.Name.Replace(sourceBlobPath, "");
                            string destBlobFilePath = $"{destBlobPath}{fileName}";
                            await blobPrivateContainer.GetBlobClient(destBlobFilePath).StartCopyFromUriAsync(sourceFileUri);
                        }
                        else
                        {
                            paperDataCopyErrFlg = true;
                        }
                    }
                }
                paperIndex++;
            }

            return Result;
        }

        //以JointSchedule為單位，判斷班級/課程名單是否完成並生成決賽名單
        /// <param name="mode">updDb true:更新DB false:不更新DB</param>
        /// <param name="classCnt"> 班級流水號 (此次活動參加的班級數)
        public static async Task<object> CreatePassJointCourseBySchedule(CosmosClient client, string jointEventId, string jointGroupId, string jointScheduleId, string scope, bool updDb, int classCnt)
        {
            List<JointEventGroupDb> result = new List<JointEventGroupDb>();
            //0. 取得jointEvent、JointEventSchedule
            JointEvent jointEvent = await client.GetContainer(Constant.TEAMModelOS, Constant.Teacher).ReadItemAsync<JointEvent>(jointEventId, new PartitionKey("JointEvent"));
            if (jointEvent == null)
            {
                return result;
            }
            JointEventSchedule jointEventSchedule = jointEvent.schedule.Where(s => s.id.Equals(jointScheduleId)).FirstOrDefault();
            if (jointEventSchedule == null)
            {
                return result;
            }
            //1. 用jointEventId、jointGroupId 取得所有老師報名的 班級/課程名單
            List<JointEventGroupDb> jointEventCourse = new List<JointEventGroupDb>();
            StringBuilder stringBuilderJointCourse = new($"SELECT * FROM c WHERE c.jointEventId = '{jointEventId}' AND c.jointGroupId = '{jointGroupId}' AND c.scope = '{scope}' AND (c.type = 'regular' OR NOT IS_DEFINED(c.type) OR IS_NULL(c.type)) ");
            string container = (scope.Equals("school")) ? Constant.School : Constant.Teacher;
            await foreach (var item in client.GetContainer(Constant.TEAMModelOS, container).GetItemQueryStreamIteratorSql(queryText: stringBuilderJointCourse.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("JointCourse") }))
            {
                using var json = await JsonDocument.ParseAsync(item.Content);
                if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                {
                    foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                    {
                        jointEventCourse.Add(obj.ToObject<JointEventGroupDb>());
                    }
                }
            }
            //2. 取得本Schedule的所有JointExam
            List<string> jointExamIdList = new List<string>();
            List<JointExam> jointEventExam = new List<JointExam>();
            StringBuilder stringBuilderJointExam = new($"SELECT * FROM c WHERE c.jointEventId = '{jointEventId}' AND c.jointGroupId = '{jointGroupId}' AND c.jointScheduleId = '{jointScheduleId}' ");
            await foreach (var item in client.GetContainer(Constant.TEAMModelOS, Constant.Common).GetItemQueryStreamIteratorSql(queryText: stringBuilderJointExam.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("JointExam") }))
            {
                using var json = await JsonDocument.ParseAsync(item.Content);
                if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                {
                    foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                    {
                        JointExam jointExamRow = obj.ToObject<JointExam>();
                        jointEventExam.Add(jointExamRow);
                        if (!jointExamIdList.Contains(jointExamRow.id))
                        {
                            jointExamIdList.Add(jointExamRow.id);
                        }
                    }
                }
            }
            //3. 取得所有JointExam關聯的Exam
            List<ExamInfo> exam = new List<ExamInfo>();
            StringBuilder stringBuilderExam = new($"SELECT * FROM c WHERE c.pk = 'Exam' AND ARRAY_CONTAINS({JsonSerializer.Serialize(jointExamIdList)}, c.jointExamId) ");
            await foreach (var item in client.GetContainer(Constant.TEAMModelOS, Constant.Common).GetItemQueryStreamIteratorSql(queryText: stringBuilderExam.ToString(), requestOptions: null))
            {
                using var json = await JsonDocument.ParseAsync(item.Content);
                if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                {
                    foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                    {
                        exam.Add(obj.ToObject<ExamInfo>());
                    }
                }
            }
            //4. 用 IfJointExamComplete 列出完成評量的 班級/課程名單
            List<JointEventGroupPassDto> passGroupsBindJointexam = new List<JointEventGroupPassDto>(); //已完成的課程名單和已完成的JointExam對照表
            JointEventSchedule finalSchedule = jointEvent.schedule.Where(s => s.type.Equals("exam") && s.examType.Equals("custom")).FirstOrDefault(); //取schedule中的決賽為jointScheduleId ※應該只會有一個決賽
            if (finalSchedule != null) //可取得挑戰賽才繼續
            {
                foreach (JointExam jointExamRow in jointEventExam)
                {
                    ///第1層 JointExam
                    List<ExamInfo> examsNow = exam.Where(e => e.jointExamId.Equals(jointExamRow.id)).ToList();
                    foreach (JointEventGroupDb jointEventCourseRow in jointEventCourse)
                    {
                        ///第2層 jointEventCourse
                        string creatorIdRow = jointEventCourseRow.creatorId;
                        foreach (JointEventGroupBase.JointEventGroupCourse jointCourseIn in jointEventCourseRow.courseLists)
                        {
                            ///第3層 老師報名的course
                            string courseId = jointCourseIn.courseId;
                            string courseName = jointCourseIn.courseName;
                            List<string> groupIds = jointCourseIn.groupLists.Select(c => c.id).ToList();
                            ExamInfo examNow = examsNow.Where(e => e.creatorId.Equals(creatorIdRow) && e.subjects[0].id.Equals(courseId)).FirstOrDefault();
                            if (examNow != null)
                            {
                                string examId = examNow.id;
                                string schoolId = string.Empty;
                                string classId = string.Empty;
                                foreach (string groupId in groupIds)
                                {
                                    JointEventGroupBase.JointEventGroupCourseGroup passGroupInfo = await IfExamComplete(client, examId, scope, creatorIdRow, schoolId, classId, groupId);
                                    if (passGroupInfo != null)
                                    {
                                        //生成決賽通過的老師課程名單用中間model
                                        JointEventGroupPassDto passGroupRow = passGroupsBindJointexam.Where(p => p.creatorId.Equals(jointEventCourseRow.creatorId) && p.courseId.Equals(courseId) && p.groupId.Equals(groupId)).FirstOrDefault();
                                        if (passGroupRow == null)
                                        {
                                            passGroupsBindJointexam.Add(new JointEventGroupPassDto()
                                            {
                                                creatorId = jointEventCourseRow.creatorId,
                                                courseId = courseId,
                                                groupId = groupId
                                            });
                                            passGroupRow = passGroupsBindJointexam.Where(p => p.creatorId.Equals(jointEventCourseRow.creatorId) && p.courseId.Equals(courseId) && p.groupId.Equals(groupId)).FirstOrDefault();
                                        }
                                        if (!passGroupRow.jointExamId.Contains(jointExamRow.id))
                                        {
                                            passGroupRow.jointExamId.Add(jointExamRow.id);
                                        }
                                        //if (passGroupRow.jointExamId.Count.Equals(jointExamIdList.Count)) //[舊] 所有評量都完成 => pass
                                        //{
                                        //    passGroupRow.pass = true;
                                        //}
                                        if(passGroupRow.jointExamId.Count > 0) //[新] 有任一評量完成 => pass
                                        {
                                            passGroupRow.pass = true;
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }

            //5. 決賽課程生成
            ///生成資料製作
            List<JointEventGroupDb> jointCourseCreates = new List<JointEventGroupDb>(); //要生成的老師課程名單
            foreach (JointEventGroupDb jointEventCourseRow in jointEventCourse)
            {
                foreach (JointEventGroupBase.JointEventGroupCourse course in jointEventCourseRow.courseLists)
                {
                    foreach (JointEventGroupBase.JointEventGroupCourseGroup group in course.groupLists)
                    {
                        JointEventGroupPassDto passGroupRow = passGroupsBindJointexam.Where(g => g.creatorId.Equals(jointEventCourseRow.creatorId) && g.courseId.Equals(course.courseId) && g.groupId.Equals(group.id) && g.pass.Equals(true)).FirstOrDefault();
                        if (passGroupRow != null)
                        {
                            string courseId = course.courseId;
                            string courseName = course.courseName;
                            JointEventGroupDb jointCourseCreateRow = jointCourseCreates.Where(c => c.jointEventId.Equals(jointEventCourseRow.jointEventId) && c.jointGroupId.Equals(jointEventCourseRow.jointGroupId) && c.creatorId.Equals(jointEventCourseRow.creatorId)).FirstOrDefault();
                            if (jointCourseCreateRow == null)
                            {
                                ///DB 老師決賽document
                                JointEventGroupDb finalEventCourse = new JointEventGroupDb();
                                finalEventCourse.jointEventId = jointEventCourseRow.jointEventId;
                                finalEventCourse.jointGroupId = jointEventCourseRow.jointGroupId;
                                finalEventCourse.code = jointEventCourseRow.code;
                                finalEventCourse.pk = jointEventCourseRow.pk;
                                finalEventCourse.scope = jointEventCourseRow.scope;
                                finalEventCourse.type = "custom"; //決賽
                                finalEventCourse.creatorId = jointEventCourseRow.creatorId;
                                finalEventCourse.creatorName = jointEventCourseRow.creatorName;
                                finalEventCourse.creatorEmail = jointEventCourseRow.creatorEmail;
                                finalEventCourse.schoolId = jointEventCourseRow.schoolId;
                                finalEventCourse.schoolName = jointEventCourseRow.schoolName;
                                finalEventCourse.countryId = jointEventCourseRow.countryId;
                                finalEventCourse.countryName = jointEventCourseRow.countryName;
                                finalEventCourse.provinceId = jointEventCourseRow.provinceId;
                                finalEventCourse.provinceName = jointEventCourseRow.provinceName;
                                finalEventCourse.cityId = jointEventCourseRow.cityId;
                                finalEventCourse.cityName = jointEventCourseRow.cityName;
                                finalEventCourse.jointScheduleId = finalSchedule.id;
                                classCnt++;
                                finalEventCourse.courseLists.Add(new JointEventGroupBase.JointEventGroupCourse()
                                {
                                    courseId = courseId,
                                    courseName = courseName,
                                    groupLists = new List<JointEventGroupBase.JointEventGroupCourseGroup>() {
                                        new() { id = group.id, name = group.name, no = classCnt.ToString() }
                                    }
                                });
                                finalEventCourse.createTime = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds();
                                jointCourseCreates.Add(finalEventCourse);
                            }
                            else
                            {
                                JointEventGroupBase.JointEventGroupCourse courseRowNow = jointCourseCreateRow.courseLists.Where(c => c.courseId.Equals(courseId)).FirstOrDefault();
                                if (courseRowNow == null)
                                {
                                    ///DB 老師決賽課程資料
                                    classCnt++;
                                    jointCourseCreateRow.courseLists.Add(
                                        new JointEventGroupBase.JointEventGroupCourse
                                        {
                                            courseId = courseId,
                                            courseName = courseName,
                                            groupLists = new List<JointEventGroupBase.JointEventGroupCourseGroup>() {
                                                            new() { id = group.id, name = group.name, no = classCnt.ToString() }
                                            }
                                        }
                                    );
                                }
                                else
                                {
                                    JointEventGroupBase.JointEventGroupCourseGroup groupRowNow = courseRowNow.groupLists.Where(g => g.id.Equals(group.id)).FirstOrDefault();
                                    if (groupRowNow == null)
                                    {
                                        ///DB 老師決賽班級資料
                                        classCnt++;
                                        courseRowNow.groupLists.Add(
                                            new() { id = group.id, name = group.name, no = classCnt.ToString() }
                                        );
                                    }
                                }
                            }
                        }
                    }
                }
            }
            ///DB
            if (jointCourseCreates.Count > 0)
            {
                //取得所有已存在的決賽課程名單
                List<JointEventGroupDb> jointCourseFinalExist = new List<JointEventGroupDb>();
                StringBuilder stringBuilderJointCourseCreates = new($"SELECT * FROM c WHERE c.jointEventId = '{jointEventId}' AND c.jointGroupId = '{jointGroupId}' AND c.scope = '{scope}' AND c.type = 'custom' AND c.jointScheduleId = '{finalSchedule.id}' ");
                await foreach (var item in client.GetContainer(Constant.TEAMModelOS, container).GetItemQueryStreamIteratorSql(queryText: stringBuilderJointCourseCreates.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("JointCourse") }))
                {
                    using var json = await JsonDocument.ParseAsync(item.Content);
                    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                    {
                        foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                        {
                            jointCourseFinalExist.Add(obj.ToObject<JointEventGroupDb>());
                        }
                    }
                }
                //生成決賽課程名單
                foreach (JointEventGroupDb jointCourseCreateRow in jointCourseCreates)
                {
                    JointEventGroupDb jointCourseFinalExistRow = jointCourseFinalExist.Where(j => j.creatorId.Equals(jointCourseCreateRow.creatorId)).FirstOrDefault();
                    jointCourseCreateRow.id = (jointCourseFinalExistRow != null) ? jointCourseFinalExistRow.id : Guid.NewGuid().ToString();
                    if(updDb)
                    {
                        await client.GetContainer(Constant.TEAMModelOS, container).UpsertItemAsync(jointCourseCreateRow);
                    }
                    result.Add(jointCourseCreateRow);
                }
            }

            return new { result = result, classCnt = classCnt };
        }
        //判斷某課程名單是否已完成評量
        //回傳值: 班級ID 或 課程名單ID 列表
        private static async Task<JointEventGroupCourseGroup> IfExamComplete(CosmosClient client, string examId, string scope, string creatorId, string school, string classId, string groupId)
        {
            JointEventGroupCourseGroup result = null;
            try
            {
                if (string.IsNullOrWhiteSpace(classId) && string.IsNullOrWhiteSpace(groupId)) return result;
                if ((scope.Equals("private") && string.IsNullOrWhiteSpace(creatorId)) || (scope.Equals("school") && string.IsNullOrWhiteSpace(school))) return result;
                string examClassResultCode = (scope.Equals("school") && !string.IsNullOrWhiteSpace(school)) ? $"ExamClassResult-{school}" : $"ExamClassResult-{creatorId}";
                StringBuilder stringBuilder = new($"SELECT c.info.id AS infoId, c.info.name AS infoName, c.studentIds, c.studentAnswers FROM c WHERE c.examId = '{examId}' ");
                if (!string.IsNullOrWhiteSpace(classId))
                {
                    stringBuilder.Append($" AND c.info.id = '{classId}' ");
                }
                else
                {
                    stringBuilder.Append($" AND c.info.id = '{groupId}' ");
                }
                await foreach (var item in client.GetContainer(Constant.TEAMModelOS, Constant.Common).GetItemQueryStreamIteratorSql(queryText: stringBuilder.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"{examClassResultCode}") }))
                {
                    using var json = await JsonDocument.ParseAsync(item.Content);
                    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                    {
                        foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                        {
                            string infoId = obj.GetProperty("infoId").GetString();
                            string infoName = obj.GetProperty("infoName").GetString();
                            List<string> studentIds = obj.GetProperty("studentIds").ToObject<List<string>>();
                            List<List<string>> studentAnswers = obj.GetProperty("studentAnswers").ToObject<List<List<string>>>();
                            bool hasAnswer = false;
                            foreach (List<string> studentAnswer in studentAnswers)
                            {
                                if (studentAnswer.Count > 0)
                                {
                                    hasAnswer = true;
                                    break;
                                }
                            }
                            if (hasAnswer)
                            {
                                result = new JointEventGroupBase.JointEventGroupCourseGroup() { id = infoId, name = infoName };
                            }
                        }
                    }
                }
                return result;
            }
            catch (Exception e)
            {
                return result;
            }

        }

        /// <summary>
        /// 計算統測活動各報名班級的各活動階段進行狀況
        /// </summary>
        /// <param name="jointEventId"></param>
        /// <param name="jointGroupId"></param>
        /// <param name="creatorId"></param>
        /// <param name="jointEventGroupDb">要取得的報名課程，若給null則會從DB取</param>
        /// <param name="schedule">若給null則所有schedule的status(報名、熱身賽、決賽)都計算</param>
        /// <param name="update">是否更新DB true:更新 false:不更新</param>
        public static async Task<JointEventGroupDb> CalJointCourseGroupScheduleStatusAsync(CosmosClient client, string jointEventId, string jointGroupId, string creatorId, JointEventGroupDb jointEventGroupDb = null, JointEventSchedule schedule = null)
        {
            long now = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds();
            string scope = "private"; //先只指定個人
            string container = (scope.Equals("school")) ? Constant.School : Constant.Teacher;
            //取得統測活動
            JointEvent jointEvent = await client.GetContainer(Constant.TEAMModelOS, "Teacher").ReadItemAsync<JointEvent>(jointEventId, new PartitionKey("JointEvent"));
            if (jointEvent == null)
            {
                return null;
            }
            //取得報名名單
            if (jointEventGroupDb == null)
            {
                StringBuilder stringBuilder = new($"SELECT * FROM c WHERE c.jointEventId = '{jointEventId}' AND c.jointGroupId = '{jointGroupId}' AND c.creatorId = '{creatorId}' AND c.type = 'regular' ");
                await foreach (var item in client.GetContainer(Constant.TEAMModelOS, container).GetItemQueryStreamIteratorSql(queryText: stringBuilder.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("JointCourse") }))
                {
                    using var json = await JsonDocument.ParseAsync(item.Content);
                    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                    {
                        foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                        {
                            jointEventGroupDb = obj.ToObject<JointEventGroupDb>();
                        }
                    }
                }
            }
            if (jointEventGroupDb == null)
            {
                return null;
            }
            //計算各階段Status
            List<JointEventSchedule> jointScheduleList = new List<JointEventSchedule>(); //要計算的活動階段
            if (schedule != null) jointScheduleList.Add(schedule);
            else jointScheduleList = jointEvent.schedule;
            ///計算邏輯
            foreach (JointEventGroupBase.JointEventGroupCourse courseRow in jointEventGroupDb.courseLists) //各報名課程
            {
                string courseId = courseRow.courseId;
                foreach (JointEventGroupBase.JointEventGroupCourseGroup courseGroup in courseRow.groupLists) //各報名課程名單(各班)
                {
                    string groupId = courseGroup.id;
                    foreach (JointEventSchedule eventSchedule in jointScheduleList) //各活動階段
                    {
                        //活動階段篩選：報名、熱身賽、決賽 才記入
                        if(eventSchedule.type.Equals("join") || eventSchedule.type.Equals("exam"))
                        {
                            JointEventGroupBase.JointEventGroupCourseGroup.JointEventGroupCourseGroupSchedule courseGroupSchedule = courseGroup.schedule.FirstOrDefault(s => s.id.Equals(eventSchedule.id));
                            if (courseGroupSchedule == null) //不存在 > 新建
                            {
                                courseGroupSchedule = new JointEventGroupBase.JointEventGroupCourseGroup.JointEventGroupCourseGroupSchedule()
                                {
                                    id = eventSchedule.id,
                                    status = "undo"
                                };
                                courseGroup.schedule.Add(courseGroupSchedule);
                                courseGroupSchedule = courseGroup.schedule.FirstOrDefault(s => s.id.Equals(eventSchedule.id));
                            }
                            courseGroupSchedule.status = await GetGroupJointScheduleStatus(client, jointEventId, jointGroupId, groupId, eventSchedule, jointEventGroupDb, creatorId);
                        }
                    }
                }
            }

            return jointEventGroupDb;
        }

        //判斷某groupId在某JointSchedule是否完成
        /// <summary>
        /// 判斷某groupId在某JointSchedule是否完成
        /// </summary>
        /// <param name="jointEventId"></param>
        /// <param name="jointGroupId"></param>
        /// <param name="groupId"></param>
        /// <param name="schedule"></param>
        /// <param name="jointCourse">統測報名班級DB document 若為null則由DB取</param>
        /// <param name="creatorId"></param>
        /// <returns></returns>
        private static async Task<string> GetGroupJointScheduleStatus(CosmosClient client, string jointEventId, string jointGroupId, string groupId, JointEventSchedule schedule, JointEventGroupDb jointCourse, string creatorId)
        {
            long now = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds();
            string result = "undo";
            string scope = "private"; //先只指定個人
            string container = (scope.Equals("school")) ? Constant.School : Constant.Teacher;
            if (schedule.startTime <= now && now <= schedule.endTime) //活動階段在進行中才續行做Status判斷
            {
                switch (schedule.type)
                {
                    //報名
                    ///算出邏輯：(1)可取得該報名課程名單 -> complete (2)無法取得 -> undo
                    case "join":
                        if (jointCourse == null)
                        {
                            StringBuilder stringBuilderJointCourse = new($"SELECT * FROM c WHERE c.jointEventId = '{jointEventId}' AND c.jointGroupId = '{jointGroupId}' AND c.creatorId = '{creatorId}' AND c.type = 'regular' ");
                            await foreach (var item in client.GetContainer(Constant.TEAMModelOS, container).GetItemQueryStreamIteratorSql(queryText: stringBuilderJointCourse.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("JointCourse") }))
                            {
                                using var json = await JsonDocument.ParseAsync(item.Content);
                                if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                                {
                                    foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                                    {
                                        jointCourse = obj.ToObject<JointEventGroupDb>();
                                    }
                                }
                            }
                        }
                        if (jointCourse == null)
                        {
                            break;
                        }
                        foreach (JointEventGroupBase.JointEventGroupCourse courseInfo in jointCourse.courseLists)
                        {
                            JointEventGroupBase.JointEventGroupCourseGroup groupExist = courseInfo.groupLists.FirstOrDefault(g => g.id.Equals(groupId));
                            if (groupExist != null)
                            {
                                result = "complete";
                            }
                        }
                        break;
                    //競賽 
                    ///算出邏輯： (1)無法取得報名班級(決賽班級) -> 資格不符(disqualify) 
                    ///         (2)統測評量數 > 0 && 個人評量完成數(學生有一人有作答則是為完成) == 統測評量數 -> 完成(complete)
                    ///         (3)統測評量數 > 0 && 個人評量完成數 > 0 && 統測評量數 > 個人評量完成數 -> 進行中(doing)
                    ///         (4)default -> undo
                    case "exam":
                        //取得老師報名課程或決賽老師課程
                        List<JointEventGroupDb> jointEventGroup = new List<JointEventGroupDb>(); //個人課程
                        StringBuilder stringBuilderEventGroup = new($"SELECT * FROM c WHERE c.jointEventId = '{jointEventId}' AND c.jointGroupId = '{jointGroupId}' ");
                        if (schedule.examType.Equals("regular")) //熱身賽
                        {
                            stringBuilderEventGroup.Append($" AND (c.type = 'regular' OR NOT IS_DEFINED(c.type) OR IS_NULL(c.type)) ");
                        }
                        else if (schedule.examType.Equals("custom")) //決賽
                        {
                            stringBuilderEventGroup.Append($" AND c.type = 'custom' AND c.jointScheduleId = '{schedule.id}' ");
                        }
                        if (!string.IsNullOrWhiteSpace(creatorId))
                        {
                            stringBuilderEventGroup.Append($" AND c.creatorId = '{creatorId}' ");
                        }
                        await foreach (var item in client.GetContainer(Constant.TEAMModelOS, Constant.Teacher).GetItemQueryStreamIteratorSql(queryText: stringBuilderEventGroup.ToString(), requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("JointCourse") }))
                        {
                            using var json = await JsonDocument.ParseAsync(item.Content);
                            if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                            {
                                foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                                {
                                    jointEventGroup.Add(obj.ToObject<JointEventGroupDb>());
                                }
                            }
                        }
                        //取得本Schedule的所有JointExam
                        List<string> jointExamIdList = new List<string>();
                        StringBuilder stringBuilderJointExam = new($"SELECT DISTINCT VALUE c.id FROM c WHERE c.jointEventId = '{jointEventId}' AND c.jointGroupId = '{jointGroupId}' AND c.jointScheduleId = '{schedule.id}' ");
                        var resultJointExam = await client.GetContainer(Constant.TEAMModelOS, Constant.Common).GetList<string>(stringBuilderJointExam.ToString(), $"JointExam");
                        if (resultJointExam.list.IsNotEmpty())
                        {
                            jointExamIdList = new List<string>(resultJointExam.list);
                        }
                        //取得所有個人評量
                        List<string> examIdList = new List<string>();
                        string sqlExam = $"SELECT DISTINCT VALUE c.id FROM c WHERE ARRAY_CONTAINS({JsonSerializer.Serialize(jointExamIdList)}, c.jointExamId) AND ARRAY_CONTAINS(c.stuLists, '{groupId}') AND CONTAINS(c.code, 'Exam-')";
                        await foreach (var item in client.GetContainer(Constant.TEAMModelOS, Constant.Common).GetItemQueryIteratorSql<string>(queryText: sqlExam, requestOptions: new QueryRequestOptions { }))
                        {
                            examIdList.Add(item);
                        }
                        //取得所有考試的作答結果
                        List<string> finishExamIdList = new List<string>();
                        string sqlExamClassResult = $"SELECT c.examId, c.info.id as classId, c.studentAnswers FROM c WHERE ARRAY_CONTAINS({JsonSerializer.Serialize(examIdList)}, c.examId) AND c.info.id = '{groupId}' AND c.progress=true AND CONTAINS(c.code, 'ExamClassResult')";
                        await foreach (var item in client.GetContainer(Constant.TEAMModelOS, Constant.Common).GetItemQueryStreamIteratorSql(queryText: sqlExamClassResult, requestOptions: new QueryRequestOptions() { }))
                        {
                            using var json = await JsonDocument.ParseAsync(item.Content);
                            if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                            {
                                foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                                {
                                    string examId = obj.GetProperty("examId").ToString();
                                    string classId = obj.GetProperty("classId").ToString();
                                    List<List<string>> studentAnswers = obj.GetProperty("studentAnswers").ToObject<List<List<string>>>();
                                    bool isFinish = false; //評量是否已完成 ※有任一學生有作答則視為已完成
                                    foreach (List<string> studentAnswer in studentAnswers)
                                    {
                                        if (studentAnswer.Count > 0) { isFinish = true; break; }
                                    }
                                    if (isFinish)
                                    {
                                        finishExamIdList.Add(examId);
                                    }
                                }
                            }
                        }
                        //結果判斷
                        JointEventGroupBase.JointEventGroupCourseGroup classInGroup = new JointEventGroupBase.JointEventGroupCourseGroup(); //取得該班級的報名/決賽資訊
                        foreach (JointEventGroupDb eventGroup in jointEventGroup)
                        {
                            foreach (JointEventGroupBase.JointEventGroupCourse eventCourse in eventGroup.courseLists)
                            {
                                foreach (JointEventGroupBase.JointEventGroupCourseGroup group in eventCourse.groupLists)
                                {
                                    if (group.id.Equals(groupId))
                                    {
                                        classInGroup = group;
                                    }
                                }
                            }
                        }
                        if (jointEventGroup.Count.Equals(0) || string.IsNullOrWhiteSpace(classInGroup.id)) //資格不符
                        {
                            result = "disqualify";
                        }
                        else if (jointExamIdList.Count > 0 && jointExamIdList.Count.Equals(finishExamIdList.Count))
                        {
                            result = "complete";
                        }
                        else if (jointExamIdList.Count > 0 && finishExamIdList.Count > 0 && jointExamIdList.Count > finishExamIdList.Count)
                        {
                            result = "doing";
                        }
                        break;
                }
            }

            return result;
        }


        /// <summary>
        /// 計算決賽通過的老師課程名單用中間model
        /// </summary>
        public class JointEventGroupPassDto
        {
            public string creatorId { get; set; }
            public string courseId { get; set; }
            public string groupId { get; set; }
            public bool pass { get; set; }
            public List<string> jointExamId { get; set; } = new(); //已完成的活動評量ID

        }
    }
}