TEAMModelOS/TEAMModelOS/Controllers/School/StudentController.cs

using System;
using System.Collections;
using System.Collections.Generic;
using System.Dynamic;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Text.Json;
using System.Threading.Tasks;
using Azure;
using Azure.Cosmos;
using Azure.Storage.Sas;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Cryptography.KeyDerivation;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Options;
using TEAMModelOS.Filter;
using TEAMModelOS.Models;
using TEAMModelOS.SDK.DI;
using TEAMModelOS.SDK.Extension;
using TEAMModelOS.SDK.Models;
using TEAMModelOS.SDK.Models.Cosmos;
using TEAMModelOS.SDK.Models.Cosmos.Common;

namespace TEAMModelOS.Controllers
{
    [Route("student")]
    [ApiController]
    public class StudentController : Controller
    {
        private readonly AzureCosmosFactory _azureCosmos;
        private readonly AzureStorageFactory _azureStorage;
        private readonly DingDing _dingDing;
        private readonly Option _option;

        public StudentController(
            AzureCosmosFactory azureCosmos,
            AzureStorageFactory azureStorage,
            DingDing dingDing,
            IOptionsSnapshot<Option> option
            )
        {
            _azureCosmos = azureCosmos;
            _azureStorage = azureStorage;
            _dingDing = dingDing;
            _option = option?.Value;
        }

        /// <summary>
        /// 學生帳號管理
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpPost("student-manage")]
        public async Task<IActionResult> StudentManage(JsonElement request)
        {
            try
            {
                //TODO : 權限檢查、學校檢查。
                if (!request.TryGetProperty("grant_type", out JsonElement grant_type) || !request.TryGetProperty("schoolId", out JsonElement schoolId)) return BadRequest();

                switch (grant_type.GetString())
                {
                    case "create":
                        //單人創建 創建學生->將學生加入教室 檢查學生ID是否重複，欲加入的班級存不存在，座號是否重複。
                        //id pw name classId no year
                        //retrun 如果有重複則回{ existNo } , 成功則{ id, name, year, classId, no }
                        var importStuds = request.GetProperty("students").EnumerateArray();
                        while (importStuds.MoveNext())
                        {
                            JsonElement currStud = importStuds.Current;
                            string id = null, name = null, pw = null,  no = null, classId = null;
                            int year = 0;
                            //讀取輸入的資料
                            if (!currStud.TryGetProperty("id", out var tmpId) || !currStud.TryGetProperty("name", out var tmpName)) continue;
                            id = tmpId.GetString();
                            name = tmpName.GetString();
                            if (currStud.TryGetProperty("pw", out var tmpPw)) pw = tmpPw.GetString();
                            if (currStud.TryGetProperty("year", out var tmpYear)) year = tmpYear.GetInt32();
                            if (currStud.TryGetProperty("no", out var tmpNo)) no = tmpNo.GetString();
                            if (currStud.TryGetProperty("classId", out var tmpClassId)) classId = tmpClassId.GetString();
                            //要檢查座號使否已被使用
                            var existNo = await checkStudNo(schoolId.GetString(), classId, new List<string>() { id });
                            if (existNo.Count != 0) return this.Ok(new { code = $"Base-{schoolId.GetString()}", existNo = existNo.Select(o => o.id).ToList() });

                            //建立學生
                            studCreateInfo studCreateInfo = new studCreateInfo(id, name, "M", year, pw, classId, no);
                            var isCreateSuc = await createStudent(schoolId.GetString(), studCreateInfo);
                            if (isCreateSuc) return this.Ok(new { code = $"Base-{schoolId.GetString()}", id, name, year, classId, no });
                            else return this.Ok(new { code = $"Base-{schoolId.GetString()}", errorId = id });
                        }
                        break;
                    case "import":
                        //只有ClassNo可以比對
                        var retUpsert = await upsertStudents(schoolId.GetString(), request.GetProperty("students").EnumerateArray());
                        return this.Ok(new { code = $"Base-{schoolId.GetString()}", students = retUpsert.studs, retUpsert.classDuplNos, retUpsert.errorIds });
                    case "read":
                        //讀取該間學校所有的學生資訊
                        var students = await getAllStudent(schoolId.GetString());
                        return this.Ok(new { code = $"Base-{schoolId.GetString()}", students });
                    case "update":
                        //更新學生資料，批量密碼重置，基本資訊更新(姓名、教室ID、性別、學年及座號)
                        var retUpdate = await updateStudents(schoolId.GetString(), request.GetProperty("students").EnumerateArray());
                        return this.Ok(new { code = $"Base-{schoolId.GetString()}", students = retUpdate.studs, retUpdate.classDuplNos, retUpdate.nonexistentIds, retUpdate.errorNos, retUpdate.errorClassId });
                    case "delete":
                        //刪除學生資料及從教室學生名單內移除該學生
                        var sucDelIds = await deleteStudents(schoolId.GetString(), request.GetProperty("students").EnumerateArray());
                        return this.Ok(new { code = $"Base-{schoolId.GetString()}", ids = sucDelIds });
                    case "remove":
                        //將學生基本資料內的classId、no、groupId及groupName寫入null
                       ( List<string> studs, List< string > nonexistentIds, List<string> errorIds) retRemove = await removeStudentClassInfo(schoolId.GetString(), request.GetProperty("students").EnumerateArray());
                       
                        return  Ok(new { code = $"Base-{schoolId.GetString()}", ids = retRemove.studs, retRemove.nonexistentIds, retRemove.errorIds });
                    default:
                        return BadRequest();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/StudentManage()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            return BadRequest();
        }

        /// <summary>
        /// 整理前端匯入的學生資訊
        /// </summary>
        /// <param name="schoolId"></param>
        /// <param name="students"></param>
        /// <returns></returns>
        private (Dictionary<string, (string name, string no, int year, string salt, string pw, string classNo, string className, string periodId)> studs,
            Dictionary<string, (string className, string periodId,  int year, string no )> classInfo,
            Dictionary<string, List<(string id, string no)>> classStudNo,
            List<string> errorYear,
            List<string> duplId) doSortImpStuds(string schoolId, JsonElement.ArrayEnumerator students)
        {
            //批量匯入 檢查輸入數據 確認座號 確認教室(創建教室) 確認學生存不存在或是要不要更新

            //存放輸入的學生資訊 key:stud id value:學生詳細資料
            Dictionary<string, (string name, string no, int  year, string salt, string pw, string classNo, string className, string periodId)> dicStuds = new Dictionary<string, (string name, string no, int year, string salt, string pw, string classNo, string className, string periodId)>();

            //存放教室資訊用 key:classNo value:className
            Dictionary<string, (string className, string periodId, int year,string classNo)> dicClassInfo = new Dictionary<string, (string className, string periodId,  int year,string classNo)>();
            //存放欲加入該間教室的學生座號清單 key:classNo value:no list
            Dictionary<string, List<(string id, string no)>> dicClassStudNo = new Dictionary<string, List<(string id, string no)>>();
            //存放輸入id重複
            List<string> duplId = new List<string>();
            List<string> errorYear = new List<string>();

            while (students.MoveNext())
            {
                //string id = null, no = null, name = null, year = null, pw = null, classNo = null, className = null;
                JsonElement student = students.Current;

                //ClassNo內的座號
                //欲加入的教室
                //查學生 
                //該間教室的座號與目前欲更新的是否有重複 有些可能是同教室換座號 反正都要將學生讀出來
                //舊學生完整資料+新學生資料

                //進行輸入資料的整理
                if (student.TryGetProperty("id", out var tmpId))
                {
                    string id = tmpId.GetString();
                    //如果id欄位是空的，則跳過該筆資料
                    if (string.IsNullOrWhiteSpace(id)) continue;

                    //輸入的id不應有重複
                    if (dicStuds.ContainsKey(id))
                    {
                        //如果id重複，則將之從整理清單內清除
                        duplId.Add(id);
                        dicStuds.Remove(id);
                    }

                    (string name, string no, int year, string salt, string pw, string classNo, string className, string periodId) studentInfo = (null, null, 0, null, null, null, null, null);

                    if (student.TryGetProperty("name", out var tmpName) && !string.IsNullOrWhiteSpace(tmpName.GetString())) studentInfo.name = tmpName.GetString();


                    //入學學年為必須，故若是無給值則將之紀錄並跳過該筆資料
                    if (student.TryGetProperty("year", out var tmpYear) && !string.IsNullOrWhiteSpace(Convert.ToString(tmpYear))) studentInfo.year = tmpYear.GetInt32();
                    else
                    {
                        errorYear.Add(id);
                        continue;
                    }

                    //Password，若沒給則使用學號當密碼
                    studentInfo.salt = Utils.CreatSaltString(8);
                    studentInfo.pw = student.TryGetProperty("pw", out var tmpPw) && !string.IsNullOrWhiteSpace(tmpPw.GetString())
                                ? Utils.HashedPassword(tmpPw.GetString(), studentInfo.salt)
                                : Utils.HashedPassword(id, studentInfo.salt);

                    if (student.TryGetProperty("periodId", out var tmpPeriodId) && !string.IsNullOrWhiteSpace(tmpPeriodId.GetString())) studentInfo.periodId = tmpPeriodId.GetString();
                  //  if (student.TryGetProperty("gradeIndex", out var tmpGradeIndex)) studentInfo.gradeIndex = tmpGradeIndex.GetInt32();


                    if (student.TryGetProperty("classNo", out var tmpClassNo) && !string.IsNullOrWhiteSpace(tmpClassNo.GetString()))
                    {
                        studentInfo.classNo = tmpClassNo.GetString();

                        //在新建帳號上，應要給classNo才能設定no，但是若只是已存在的帳號要進行座號更新呢? 為避免使用者出錯，故已存在的帳號也應當給classNo。
                        if (student.TryGetProperty("no", out var tmpNo) && !string.IsNullOrWhiteSpace(tmpNo.GetString()))
                        {
                            studentInfo.no = tmpNo.GetString();

                            //這邊先將該間教室欲使用到的no整理出來
                            if (dicClassStudNo.ContainsKey(tmpClassNo.GetString())) dicClassStudNo[tmpClassNo.GetString()].Add((id, tmpNo.GetString()));
                            else dicClassStudNo.Add(tmpClassNo.GetString(), new List<(string id, string no)>() { (id, tmpNo.GetString()) });
                        }

                        //有給classNo才會紀錄className，classNo屬於實體教室門牌號，全校理當只會有一個。 
                        int year =0;
                        if (student.TryGetProperty("classYear", out var tmpClassYear) && tmpClassYear.TryGetInt32(out int syear))
                        {
                            year = syear;
                        }
                        if (student.TryGetProperty("className", out var tmpClassName) && !string.IsNullOrWhiteSpace(tmpClassName.GetString()))
                        {
                           
                            studentInfo.className = tmpClassName.GetString();
                            if (!dicClassInfo.ContainsKey($"{studentInfo.periodId}_{year}_{tmpClassNo.GetString()}"))
                            { dicClassInfo.Add($"{studentInfo.periodId}_{year}_{tmpClassNo.GetString()}", (tmpClassName.GetString(), studentInfo.periodId, year, tmpClassNo.GetString())); }
                        }
                    }
                    
                    //將最後結果加到字典內
                    dicStuds.Add(id, studentInfo);
                }
            }
            return (dicStuds, dicClassInfo, dicClassStudNo, errorYear, duplId);
        }

        /// <summary>
        /// 更新或是新增學生
        /// </summary>
        /// <param name="schoolId"></param>
        /// <param name="students"></param>
        /// <returns></returns>
        private async Task<(List<object> studs, Dictionary<string ,List<string>> classDuplNos, List<string> errorIds)> upsertStudents(
            string schoolId,
            JsonElement.ArrayEnumerator students)
        {
            try
            {
                var sortedImpData = doSortImpStuds(schoolId, students);

                //var   classNos = sortedImpData.classInfo.Select(o => new {key= o.Key, periodId=o.Value.periodId,index= o.Value.gradeIndex,year = o.Value.year }).ToList();
                //抓到教室資訊
                var classInfos = await getClassInfoUseNo(schoolId, sortedImpData.classInfo);
                //取出已存在教室的classId，後面查座號要用。

                List<Task> tasks = new List<Task>();
                //Key:ClassNo Value:No 匯入時只有ClassNo
                Dictionary<string, List<(string id, string no)>> classStudNos = new Dictionary<string, List<(string id, string no)>>();

                //Key:ClassNo Value:ClassId 存放教室no及id的變數
                Dictionary<string, (string classId, string className, string periodId, string gradeId, int year)> classNoId = new Dictionary<string, (string classId, string className, string periodId, string gradeId, int year)>();
                foreach (var classInfo in classInfos)
                {
                    string classGradeId = classInfo.Value.gradeId;
                    int classYear = classInfo.Value.year;
                    classNoId.Add(classInfo.Value.no,
                        (classInfo.Value.id, classInfo.Value.name, classInfo.Value.periodId, classGradeId, classYear));
                    tasks.Add(
                        Task.Run(
                            async () =>
                            {
                                //(id,no)
                                var studNo = await checkStudNo(schoolId, classInfo.Value.id);
                                classStudNos.Add(classInfo.Value.no, studNo);
                            }));
                }

                //這邊整理出不存在的教室，之後創建新教室用(比對classNo)。
                //var nonexistentClassNo = classNos.Except(classInfos.Select(o => o.Key).ToList());
                List<string> exsitkey =  new List<string>();
                foreach (var classInfo in classInfos)
                {
                    //$"{studentInfo.periodId}_{year}_{tmpClassNo.GetString()}"
                    var key = $"{classInfo.Value.periodId}_{classInfo.Value.year}_{classInfo.Value.no}";
                    exsitkey.Add(key);
                }
                List<KeyValuePair<string, (string className, string periodId, int year, string no)>> nonexistentClassNo =new List<KeyValuePair<string, (string className, string periodId, int year, string no)>>();
                foreach (var key in sortedImpData.classInfo.Keys) {
                    if (!exsitkey.Contains(key)) {
                        nonexistentClassNo.Add(new KeyValuePair<string, (string className, string periodId, int year, string no)> (key, sortedImpData.classInfo[key]));
                    }
                }

               // var nonexistentClassNo = exsitkey.Except(sortedImpData.classInfo.Select(o => o.Key).ToList());
                if (nonexistentClassNo.Count() != 0)
                {
                    var gradesInfo = await getGrades(schoolId);
                    foreach (var item in nonexistentClassNo)
                    {
                        string gradeId = string.Empty;
                        string periodId = periodId = sortedImpData.classInfo[item.Key].periodId;
                        int  year  = sortedImpData.classInfo[item.Key].year;
                        //確認該學段存在及輸入的年級index正確(-1後大於等於0)
                        //if (gradesInfo.ContainsKey(sortedImpData.classInfo[item].periodId) && sortedImpData.classInfo[item].gradeIndex - 1>=0)
                       // {
                           // periodId = sortedImpData.classInfo[item].periodId;
                           
                            //gradeId = gradesInfo[sortedImpData.classInfo[item].periodId][sortedImpData.classInfo[item].gradeIndex - 1].gradeId;
                        //}
                        //建立新教室
                        (string classId, string classNo, string className, string periodId, string gradeId) retCreateClassInfo = 
                        await createClassInfo(
                            schoolId,
                            null,
                            sortedImpData.classInfo[item.Key].className,
                            sortedImpData.classInfo[item.Key].no,
                            periodId,
                            gradeId, 
                            year);

                        classStudNos.Add(retCreateClassInfo.classNo, new List<(string id, string no)>());
                        classNoId.Add(retCreateClassInfo.classNo, (retCreateClassInfo.classId, retCreateClassInfo.className, periodId, gradeId, year));
                    }
                }

                var taskWhenAll = Task.WhenAll(tasks);
                taskWhenAll.Wait();

                //-------------------------------------------------------------------------

                //建立學生或是更新學生，並且要確認座號是否重複
                //每間教室的全部座號 欲更新的教室座號
                //先新建帳號若出現409則進行資料更新
                
                //紀錄有重複做號的id
                Dictionary<string, List<string>> duplNos = new Dictionary<string, List<string>>();
                List<string> errorIds = new List<string>();
                List<object> retStuds = new List<object>();


                CosmosContainer cosmosContainer = _azureCosmos.GetCosmosClient().GetContainer("TEAMModelOS", "Student");

                //並行處理
                foreach (var stud in sortedImpData.studs)
                {
                    //這邊一樣要確認已存在和欲加入還有欲修改的座號。
                    //欲修改的不會有重複
                    string classId = null;
                    bool isContinue = false;
                    if (!string.IsNullOrWhiteSpace(stud.Value.no) && !string.IsNullOrWhiteSpace(stud.Value.classNo))
                    {
                        classId = classNoId[stud.Value.classNo].classId;
                        (string id, string no) existNoInfo = (null, null);
                        //檢查要更新的座號是否已存在於雲端座號(已被其他同學使用)
                        classStudNos[stud.Value.classNo].ForEach(
                            o =>
                            {
                                if (o.no.Equals(stud.Value.no) && !o.id.Equals(stud.Key))
                                {
                                    existNoInfo = (o.id, o.no);
                                    if (duplNos.ContainsKey(stud.Value.classNo)) duplNos[stud.Value.classNo].Add(stud.Key);
                                    else duplNos.Add(stud.Value.classNo, new List<string>() { stud.Key });
                                }
                            });

                        //如果不是空的代表有座號重覆到，此時要再確認這個重複座號的id，是否存在於這次的更新，而且是要更新座號的。
                        if (!string.IsNullOrWhiteSpace(existNoInfo.id))
                        {
                            isContinue = true;
                            //輪巡所有匯入的學生資料，並檢查匯入的座號。
                            sortedImpData.classStudNo[stud.Value.classNo].ForEach(
                                o =>
                                {
                                    if (o.id.Equals(existNoInfo.id) && !o.no.Equals(existNoInfo.no))
                                    {
                                        //可以更新該座號
                                        isContinue = false;
                                        duplNos[stud.Value.classNo].Remove(stud.Key);
                                        return;
                                    }
                                });
                        }
                    }
                    if (isContinue) continue;

                    (string id, string name, string pic, int year, string no, string classId, string classNo, string className, string gradeId, string periodId) tmpStudInfo
                       = (stud.Key, stud.Value.name, null, stud.Value.year, stud.Value.no, null, stud.Value.classNo, null, null, null);

                    using var memoryStream = new MemoryStream();
                    using var writer = new Utf8JsonWriter(memoryStream);
                    writer.WriteStartObject();

                    writer.WriteString("id", stud.Key);
                    writer.WriteString("pk", $"Base");
                    writer.WriteString("code", $"Base-{schoolId}");
                    writer.WriteString("schoolId", schoolId);
                    writer.WriteNumber("year", stud.Value.year);
                    writer.WriteString("salt", stud.Value.salt);
                    writer.WriteString("pw", stud.Value.pw);
                    if (string.IsNullOrWhiteSpace(stud.Value.name)) writer.WriteNull("name");
                    else writer.WriteString("name", stud.Value.name);
                    writer.WriteString("gender", "M");
                    writer.WriteNull("picture");
                    writer.WriteNull("mail");
                    writer.WriteNull("mobile");
                    writer.WriteNull("country");

                    if (string.IsNullOrWhiteSpace(stud.Value.classNo)) writer.WriteNull("classId");
                    else
                    {
                        writer.WriteString("classId", classId);
                        tmpStudInfo.classId = classId;
                        tmpStudInfo.className = classNoId[stud.Value.classNo].className;
                        tmpStudInfo.gradeId = classNoId[stud.Value.classNo].gradeId;
                        tmpStudInfo.periodId = classNoId[stud.Value.classNo].periodId;
                    }
                    if (string.IsNullOrWhiteSpace(stud.Value.no)) writer.WriteNull("no");
                    else writer.WriteString("no", stud.Value.no);
                    writer.WriteNull("groupId");
                    writer.WriteNull("groupName");

                    writer.WriteEndObject();
                    writer.Flush();

                    var response = await cosmosContainer.CreateItemStreamAsync(memoryStream, new PartitionKey($"Base-{schoolId}"));

                    if (response.Status == (int)HttpStatusCode.Created)
                    {
                        //如果是Created則啥都不做，讓他去下面進行資料的彙整。
                    }
                    //查到已存在的id，則進行基本資料更新。
                    else if (response.Status == (int)HttpStatusCode.Conflict)
                    {
                        response = await _azureCosmos
                                   .GetCosmosClient()
                                   .GetContainer("TEAMModelOS", "Student")
                                   .ReadItemStreamAsync(stud.Key, new PartitionKey($"Base-{schoolId}"));
                        if (response.Status == (int)HttpStatusCode.OK)
                        {
                            memoryStream.Position = 0;
                            using Stream streamId = response.ContentStream;
                            var jsonDoc = await JsonDocument.ParseAsync(streamId);

                            //重置
                            writer.Reset();
                            writer.WriteStartObject();
                            bool isUpPwDone = false;
                            foreach (var element in jsonDoc.RootElement.EnumerateObject())
                            {
                                if (!element.Name.StartsWith("_"))
                                {
                                    switch (element.Name)
                                    {
                                        case "name":
                                            if (string.IsNullOrWhiteSpace(stud.Value.name)) goto default;
                                            else writer.WriteString("name", stud.Value.name);
                                            break;
                                        case "year":
                                            if (stud.Value.year==0) goto default;
                                            else writer.WriteNumber("year", stud.Value.year);
                                            break;
                                        case "no":
                                            if (string.IsNullOrWhiteSpace(stud.Value.no)) goto default;
                                            else writer.WriteString("no", stud.Value.no);
                                            break;
                                        case "classId":
                                            if (string.IsNullOrWhiteSpace(stud.Value.classNo)) goto default;
                                            else writer.WriteString("classId", classId);
                                            break;
                                        case "pw":
                                        case "salt":
                                            if (!isUpPwDone)
                                            {
                                                writer.WriteString("pw", stud.Value.pw);
                                                writer.WriteString("salt", stud.Value.salt);
                                                isUpPwDone = true;
                                            }
                                            break;
                                        case "picture":
                                            tmpStudInfo.pic = element.Value.GetString();
                                            goto default;
                                        default:
                                            element.WriteTo(writer);
                                            break;
                                    }
                                }
                            }
                            writer.WriteEndObject();
                            writer.Flush();

                            //此處用Replace，不使用Upsert
                            response = await cosmosContainer.ReplaceItemStreamAsync(memoryStream, stud.Key, new PartitionKey($"Base-{schoolId}"));
                            if (response.Status != (int)HttpStatusCode.OK)
                            {
                                errorIds.Add(stud.Key);
                                continue;
                            }
                        }
                        else
                        {
                            errorIds.Add(stud.Key);
                            continue;
                        }
                    }
                    else
                    {
                        errorIds.Add(stud.Key);
                        continue;
                    }

                    //整理輸出用資料
                    retStuds.Add(
                        new
                        {
                            tmpStudInfo.id,
                            tmpStudInfo.name,
                            tmpStudInfo.pic,
                            tmpStudInfo.year,
                            tmpStudInfo.no,
                            tmpStudInfo.classId,
                            tmpStudInfo.classNo,
                            tmpStudInfo.className,
                            tmpStudInfo.gradeId,
                            tmpStudInfo.periodId
                        });
                }
                return (retStuds, duplNos.Where(o => o.Value.Count != 0).ToDictionary(o=>o.Key,o=>o.Value), errorIds);
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg(
                            $"IES5，{_option.Location}，StudentController/upsertStudents()\nex:{ex.Message}{ex.StackTrace}",
                            GroupNames.醍摩豆服務運維群組);
            }
            return (null, null, null);
        }

        /// <summary>
        /// 單純建立單一學生
        /// </summary>
        /// <param name="schoolId"></param>
        /// <param name="studCreateInfo"></param>
        private async Task<bool> createStudent(string schoolId, studCreateInfo studCreateInfo)
        {
            try
            {
                using var stream = new MemoryStream();
                using var writer = new Utf8JsonWriter(stream);

                writer.WriteStartObject();
                writer.WriteString("pk", $"Base");
                writer.WriteString("code", $"Base-{schoolId}");
                writer.WriteString("id", studCreateInfo.id);
                if (string.IsNullOrWhiteSpace(studCreateInfo.name)) writer.WriteNull("name");
                else writer.WriteString("name", studCreateInfo.name);
                if (string.IsNullOrWhiteSpace(studCreateInfo.gender)) writer.WriteNull("gender");
                else writer.WriteString("gender", studCreateInfo.gender);
                writer.WriteString("schoolId", schoolId);
                writer.WriteNumber("year", studCreateInfo.year);
                writer.WriteNull("picture");
                writer.WriteNull("mail");
                writer.WriteNull("mobile");
                writer.WriteNull("country");

                //Password，若沒給則使用學號當密碼
                string salt = Utils.CreatSaltString(8);
                string hashPw = string.IsNullOrWhiteSpace(studCreateInfo.pw) 
                                    ? Utils.HashedPassword(studCreateInfo.id, salt) 
                                    : Utils.HashedPassword(studCreateInfo.pw, salt);
                writer.WriteString("salt", salt);
                writer.WriteString("pw", hashPw);

                if (string.IsNullOrWhiteSpace(studCreateInfo.classId)) writer.WriteNull("classId");
                else writer.WriteString("classId", studCreateInfo.classId);
                if (string.IsNullOrWhiteSpace(studCreateInfo.no)) writer.WriteNull("no");
                else writer.WriteString("no", studCreateInfo.no);
                writer.WriteNull("groupId"); 
                writer.WriteNull("groupName");

                writer.WriteEndObject();
                writer.Flush();
                var response = await _azureCosmos
                                .GetCosmosClient()
                                .GetContainer("TEAMModelOS", "Student")
                                .CreateItemStreamAsync(stream, new PartitionKey($"Base-{schoolId}"));
                if (response.Status == (int)HttpStatusCode.Created || response.Status == (int)HttpStatusCode.OK) return true;
                if (response.Status == (int)HttpStatusCode.Conflict) return false;
                else
                {
                    await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/createStudent()\nCosmosDB Create response status = {response.Status}\nID:{studCreateInfo.id}", GroupNames.醍摩豆服務運維群組);
                    return false;
                }
            }
            catch(Exception ex)
            {
                await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/createStudent()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
                return false;
            }
        }

        /// <summary>
        /// 生成Class資料
        /// </summary>
        /// <param name="schoolId"></param>
        /// <param name="classId"></param>
        /// <param name="className"></param>
        /// <param name="students"></param>
        /// <returns></returns>
        private async Task<(string classId, string classNo, string className, string periodId, string gradeId)> createClassInfo(string schoolId, string classId, string className, string classNo, string periodId, string gradeId,int year)
        {
            //組Class JSON
            try
            {
                string cId = classId;
                using var memoryStream = new MemoryStream();
                using var writer = new Utf8JsonWriter(memoryStream);
                writer.WriteStartObject();
                writer.WriteString("pk", "Class");
                writer.WriteString("code", $"Class-{schoolId}");
                //如果classId是空的，則生成一組GUID。
                if (string.IsNullOrWhiteSpace(classId))
                {
                    cId = Guid.NewGuid().ToString();
                    writer.WriteString("id", cId);
                }
                else writer.WriteString("id", classId);
                if (string.IsNullOrWhiteSpace(classNo)) writer.WriteNull("no");
                else writer.WriteString("no", classNo);
                writer.WriteNull("x");
                writer.WriteNull("y");
                if (string.IsNullOrWhiteSpace(className)) writer.WriteNull("name");
                else writer.WriteString("name", className);

                writer.WritePropertyName("teacher");
                writer.WriteStartObject();
                writer.WriteNull("id");
                writer.WriteNull("name");
                writer.WriteEndObject();

                //if (string.IsNullOrWhiteSpace(gradeId)) writer.WriteNull("gradeId");
                //else writer.WriteString("gradeId", gradeId);

                if (string.IsNullOrWhiteSpace(periodId)) writer.WriteNull("periodId");
                else writer.WriteString("periodId", periodId);
                writer.WriteNumber("year", year);
                writer.WriteNull("sn");
                writer.WriteString("style", "smart");
                writer.WriteString("openType", "1");
                writer.WriteString("scope", "school");
                writer.WriteEndObject();
                writer.Flush();

                var ret = await _azureCosmos.GetCosmosClient().GetContainer("TEAMModelOS", "School").CreateItemStreamAsync(memoryStream, new PartitionKey($"Class-{schoolId}"));
                if (ret.Status != (int)HttpStatusCode.Created)
                {
                    await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/createClassInfo()\nStatus:{ret.Status}\nSchoolId:{schoolId},ClassId:{classId}", GroupNames.醍摩豆服務運維群組);
                }
                return (cId, classNo, className, periodId, gradeId);
            }
            catch (CosmosException ex)
            {
                await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/createClassInfo()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/createClassInfo()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            return (null, null, null, null, null);
        }

        /// <summary>
        /// 使用學校代碼查詢該校所有學生，並且在查詢該學生所屬的教室及座號，支援offset和limit操作已及ContinuationToken，若有ContinuationToken，則會優先使用ContinuationToken。
        /// </summary>
        /// <param name="schoolId"></param>
        /// <param name="byNameOrId">透過Name或Id來查，所以不會管學制、學級和教室</param>
        /// <param name="byPeriod"></param>
        /// <param name="byGrade"></param>
        /// <param name="byClassId"></param>
        /// <param name="offset"></param>
        /// <param name="limit"></param>
        /// <param name="token"></param>
        /// <returns></returns>
        private async Task<(List<object> students, string continuationToken)> getStudents(string schoolId, string byNameOrId = null, string byPeriod = null, string byGrade = null, string byClassId = null, int offset = -1, int limit = -1, string token = default)
        {
            try
            {   
                //以學校學生角度去抓資料
                List<(string id, string name, string pic, int year)> listStudent = new List<(string id, string name, string pic, int year)>();
                string queryText = $"SELECT c.id, c.name, c.picture, c.year FROM c WHERE c.code = 'Base-{schoolId}'";

                //如果有選擇ClassId的話，則先取得該教室內的學生。
                List<string> searchId = new List<string>();
                if (!string.IsNullOrWhiteSpace(byClassId))
                {
                    var classInfos = await getClassInfoUseId(schoolId, new List<string>() { byClassId });
                    foreach (var classInfo in classInfos)
                    {
                        var students = classInfo.Value.GetProperty("students").EnumerateArray();
                        while (students.MoveNext())
                        {
                            JsonElement stud = students.Current;
                            string id = stud.GetProperty("id").GetString();
                            searchId.Add(id);
                        }
                    }
                    //將使用者過濾classId所取得的學生ID加入sql字串內
                    if (searchId.Count != 0)
                    {
                        queryText = $"{queryText} AND c.id IN ({string.Join(",", searchId.Select(o => $"'{o}'"))})";
                    }
                }

                //檢查是否有接續token及是否要在sql語法內多增加offset及limit
                if (string.IsNullOrWhiteSpace(token))
                {
                    token = default;
                    if (offset != -1 && limit != -1) queryText = $"{queryText} OFFSET {offset} LIMIT {limit}";
                }

                //回傳用ContinuationToken
                string continuationToken = string.Empty;
                //進行學生資料的查詢
                await foreach (var item in _azureCosmos.GetCosmosClient().GetContainer("TEAMModelOS", "Student")
                   .GetItemQueryStreamIterator(
                       queryText: queryText,
                       continuationToken: token,
                       requestOptions: new QueryRequestOptions()
                       { PartitionKey = new PartitionKey($"Base-{schoolId}") }))
                {
                    continuationToken = item.GetContinuationToken();
                    using var json = await JsonDocument.ParseAsync(item.ContentStream);
                    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                    {
                        var accounts = json.RootElement.GetProperty("Documents").EnumerateArray();
                        while (accounts.MoveNext())
                        {
                            JsonElement account = accounts.Current;
                            listStudent.Add((account.GetProperty("id").GetString(), account.GetProperty("name").GetString(), account.GetProperty("picture").GetString(), account.GetProperty("year").GetInt32()));
                        }
                    }
                    //單筆查詢上限為100條，所以查完一次即返回，並且給接續token。
                    break;
                }

                //查學生所屬的教室及座號
                List<object> ret = new List<object>();
                //查教室資訊，使用上面的學生id並透過子查詢查詢。
                queryText = $"SELECT c.id, c.name, c.gradeId, c.students FROM c JOIN (SELECT VALUE t FROM t IN c.students WHERE t.id IN ({string.Join(",", listStudent.Select(o => $"'{o.id}'"))}))";
                await foreach (Response item in _azureCosmos.GetCosmosClient().GetContainer("TEAMModelOS", "School")
                   .GetItemQueryStreamIterator(
                   queryText: queryText,
                   //continuationToken: token,
                   requestOptions: new QueryRequestOptions()
                   { PartitionKey = new PartitionKey($"Class-{schoolId}") }))
                {
                    using var json = await JsonDocument.ParseAsync(item.ContentStream);
                    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                    {
                        var classrooms = json.RootElement.GetProperty("Documents").EnumerateArray();
                        while (classrooms.MoveNext())
                        {
                            JsonElement classroom = classrooms.Current;

                            var studs = classroom.GetProperty("students").EnumerateArray();
                            while (studs.MoveNext())
                            {
                                JsonElement stud = studs.Current;
                                string id = stud.GetProperty("id").GetString();
                                //整理出前端所需的資訊
                                var tmp = listStudent
                                    .Where(o => o.id.Equals(id, StringComparison.Ordinal))
                                    .Select(o =>
                                        new
                                        {
                                            o.id,
                                            o.name,
                                            o.pic,
                                            o.year,
                                            no = stud.GetProperty("no").GetString(),
                                            gradeId = classroom.GetProperty("gradeId").GetString(),
                                            className = classroom.GetProperty("name").GetString()
                                        });
                                ret.AddRange(tmp);
                                //刪除已整理完的ID
                                listStudent.RemoveAll(o => o.id.Equals(id, StringComparison.Ordinal));
                            }
                        }
                    }
                }
                var notJoinClassStuds = listStudent.Select(o =>
                                        new
                                        {
                                            o.id,
                                            o.name,
                                            o.pic,
                                            o.year,
                                            no = (string)null,
                                            gradeId = (string)null,
                                            className = (string)null
                                        });
                ret.AddRange(notJoinClassStuds);
                return (ret, continuationToken);
            }
            catch (CosmosException ex)
            {
                await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/getStudents()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/getStudents()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            return (null, null);
        }

        /// <summary>
        /// 取得該學校的所有學生。
        /// </summary>
        /// <param name="schoolId"></param>
        /// <returns> [{id,name,pic,year,no,classId,classNo,className,gradeId,periodId},{id,name,pic,..}..]</returns>
        private async Task<List<object>> getAllStudent(string schoolId)
        {
            try
            {   
                //TODO : 進階查詢選項調整、部分地方可用並行處理
                //以學校學生角度去抓資料
                Dictionary<string, List<(string id, string name, string pic, int year, string no)>> dicClassStuds = new Dictionary<string, List<(string id, string name, string pic, int year, string no)>>();
                List<(string id, string name, string pic, int year, string no)> notJoinClassStuds = new List<(string id, string name, string pic, int year, string no)>();
                
                string queryText = $"SELECT c.id, c.name, c.picture, c.year, c.classId, c.no FROM c WHERE c.code = 'Base-{schoolId}'";

                //回傳用ContinuationToken
                string continuationToken = string.Empty;
                var container = _azureCosmos.GetCosmosClient().GetContainer("TEAMModelOS", "Student");

                //進行學生資料的查詢 TEAMModelOS-Student
                await foreach (var item in _azureCosmos.GetCosmosClient().GetContainer("TEAMModelOS", "Student")
                   .GetItemQueryStreamIterator(
                       queryText: queryText,
                       requestOptions: new QueryRequestOptions()
                       { PartitionKey = new PartitionKey($"Base-{schoolId}"), MaxItemCount = -1 }))
                {
                    continuationToken = item.GetContinuationToken();
                    using var json = await JsonDocument.ParseAsync(item.ContentStream);
                    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                    {
                        List<(string id, string name, string pic, int year,string no)> students = new List<(string id, string name, string pic, int year, string no)>();

                        var accounts = json.RootElement.GetProperty("Documents").EnumerateArray();
                        while (accounts.MoveNext())
                        {
                            JsonElement acc = accounts.Current;

                            string classId = acc.GetProperty("classId").GetString();

                            if (string.IsNullOrWhiteSpace(classId))
                            {
                                notJoinClassStuds.Add(
                                        (
                                            acc.GetProperty("id").GetString(),
                                            acc.GetProperty("name").GetString(),
                                            acc.GetProperty("picture").GetString(),
                                            acc.GetProperty("year").GetInt32(),
                                            acc.GetProperty("no").GetString()
                                        )
                                    );
                            }
                            else if(dicClassStuds.ContainsKey(classId))
                            {
                                dicClassStuds[classId].Add(
                                        (
                                            acc.GetProperty("id").GetString(), 
                                            acc.GetProperty("name").GetString(),
                                            acc.GetProperty("picture").GetString(),
                                            acc.GetProperty("year").GetInt32(),
                                            acc.GetProperty("no").GetString()
                                        )
                                    );
                            }
                            else
                            {
                                dicClassStuds.Add(classId, 
                                        new List<(string id, string name, string pic, int year, string no)>() 
                                        {
                                            (
                                                acc.GetProperty("id").GetString(),
                                                acc.GetProperty("name").GetString(),
                                                acc.GetProperty("picture").GetString(),
                                                acc.GetProperty("year").GetInt32(),
                                                acc.GetProperty("no").GetString()
                                            )
                                        }
                                    );
                            }
                        }
                    }
                }

                //查學生所屬的教室及座號
                List<object> ret = new List<object>();

                //查教室的資訊，用以取得gradeId,periodId資訊。
                var classInfos = await getClassInfoUseId(schoolId, dicClassStuds.Keys.ToList());
                
                //輪循所有教室學生的資料
                foreach (var classStud in dicClassStuds)
                {
                    string classId = null, classNo = null, className = null, gradeId = null, periodId = null;
                    int classYear = -1; 
                    if (classInfos.ContainsKey(classStud.Key))
                    {
                        classId = classInfos[classStud.Key].GetProperty("id").GetString();
                        classNo = classInfos[classStud.Key].GetProperty("no").GetString();
                        className = classInfos[classStud.Key].GetProperty("name").GetString();
                        //gradeId = classInfos[classStud.Key].GetProperty("gradeId").GetString();
                        periodId = classInfos[classStud.Key].GetProperty("periodId").GetString();
                        if (classInfos[classStud.Key].TryGetProperty("year", out JsonElement year)) {
                            if (year.ValueKind.Equals(JsonValueKind.Number)) {
                                classYear = classInfos[classStud.Key].GetProperty("year").GetInt32();
                            }
                        }
                    }

                    var tmp = classStud.Value.Select(o =>
                                     new
                                     {
                                         o.id,
                                         o.name,
                                         o.pic,
                                         o.year,
                                         o.no,
                                         classId,
                                         classNo,
                                         className,
                                         gradeId,
                                         periodId,
                                         classYear

                                     });
                    ret.AddRange(tmp);
                }

                //彙整沒有加入教室的學生
                notJoinClassStuds.ForEach(o=> ret.Add(
                    new
                    {
                        o.id,
                        o.name,
                        o.pic,
                        o.year,
                        o.no,
                        classId = (string)null,
                        classNo = (string)null,
                        className = (string)null,
                        gradeId = (string)null,
                        periodId = (string)null,
                        classYear = -1
                    }));

                return ret;
            }
            
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/getStudents()\n{ex.Message},{ex.StackTrace}", GroupNames.醍摩豆服務運維群組);
            }
            return null;
        }

        /// <summary>
        /// 取得該校所有教室內的名單
        /// </summary>
        /// <param name="schoolId"></param>
        /// <returns></returns>
        public async Task<Dictionary<string, JsonElement>> getClassStudent(string schoolId, string classId = null)
        {
            try
            {
                string queryText = $"SELECT VALUE FROM c WHERE c.";
                //if (!string.IsNullOrWhiteSpace(classId)) queryText += $" AND c.id = '{classId}'";
                Dictionary<string, JsonElement> listStudent = new Dictionary<string, JsonElement>();

                await foreach (Response item in _azureCosmos.GetCosmosClient().GetContainer("TEAMModelOS", "School")
                    .GetItemQueryStreamIterator(queryText: queryText, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Class-{schoolId}") }))
                {
                    using var json = await JsonDocument.ParseAsync(item.ContentStream);
                    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                    {
                        JsonElement.ArrayEnumerator accounts = json.RootElement.GetProperty("Documents").EnumerateArray();
                        while (accounts.MoveNext())
                        {
                            JsonElement account = accounts.Current;
                            string cId = account.GetProperty("id").GetString();
                            var students = account.GetProperty("students").Clone();
                            listStudent.Add(cId, students);
                        }
                    }
                }
                return listStudent;
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/getClassStudentAsync()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            return null;
        }

        /// <summary>
        /// 刪除學生，非透過批量刪除方法。
        /// </summary>
        /// <param name="schoolId"></param>
        /// <param name="students"></param>
        /// <returns></returns>
        private async Task<List<string>> deleteStudents(string schoolId, JsonElement.ArrayEnumerator students)
        {
            List<string> sucIds = new List<string>();
            try
            {
                var exceptions = new List<Exception>();

                var container = _azureCosmos.GetCosmosClient().GetContainer("TEAMModelOS", "Student");
                while (students.MoveNext())
                {
                    string id = string.Empty;
                    try
                    {
                        JsonElement student = students.Current;
                        id = student.GetProperty("id").GetString();
                        var ret = await container.DeleteItemStreamAsync(id, new PartitionKey($"Base-{schoolId}"));
                        if (ret.Status == (int)HttpStatusCode.NoContent) sucIds.Add(id);
                    }
                    catch (CosmosException ex)
                    {
                        exceptions.Add(ex);
                    }
                    catch (Exception ex)
                    {
                        exceptions.Add(ex);
                    }
                }
                if (exceptions.Count == 0) return sucIds;
                else if (exceptions.Count > 1) throw new AggregateException(exceptions);
                else if (exceptions.Count == 1) throw exceptions.Single();
            }
            catch (CosmosException ex)
            {
                await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/deleteStudents()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/deleteStudents()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            return sucIds;
        }

        /// <summary>
        /// 將學生基本資料內的classId、no、groupId及groupName清為null。
        /// </summary>
        /// <param name="schoolId"></param>
        /// <param name="students">["id1","id2",...]</param>
        /// <returns></returns>
        private async Task<(List<string> studs, List<string> nonexistentIds, List<string> errorIds)> removeStudentClassInfo(string schoolId, JsonElement.ArrayEnumerator students)
        {
            //紀錄輸入的學生
            List<string> impStuds = new List<string>();
            //紀錄更新成功的學生
            List<string> sucStuds = new List<string>();
            //記錄沒查到的學生
            List<string> nonexistentIds = new List<string>();
            //紀錄更新出錯的學生
            List<string> errorIds = new List<string>();

            //整理輸入的學生資訊
            while (students.MoveNext())
            {
                JsonElement student = students.Current;
                impStuds.Add(student.GetString());
            }

            if (impStuds.Count == 0) return (null, null, null);

            string queryText = $"SELECT VALUE c FROM c WHERE c.id IN ({string.Join(",", impStuds.Select(o => $"'{o}'"))})";
            await foreach (Response item in _azureCosmos.GetCosmosClient().GetContainer("TEAMModelOS", "Student")
                            .GetItemQueryStreamIterator(
                                queryText: queryText,
                                requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Base-{schoolId}") }))
            {
                using var json = await JsonDocument.ParseAsync(item.ContentStream);
                if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                {
                    JsonElement.ArrayEnumerator docs = json.RootElement.GetProperty("Documents").EnumerateArray();
                    while (docs.MoveNext())
                    {
                        JsonElement doc = docs.Current;

                        doc.TryGetProperty("id", out var tmpId);
                        var id = tmpId.GetString();

                        using var stream = new MemoryStream();
                        using var writer = new Utf8JsonWriter(stream);

                        writer.WriteStartObject();
                        foreach (var element in doc.EnumerateObject())
                        {
                            Console.WriteLine(element.ToJsonString());
                            //將教室相關欄位清空
                            switch (true)
                            {
                                case bool _ when element.Name.Equals("classId", StringComparison.Ordinal):
                                    writer.WriteNull("classId");
                                    break;
                                case bool _ when element.Name.Equals("no", StringComparison.Ordinal):
                                    writer.WriteNull("no");
                                    break;
                                case bool _ when element.Name.Equals("groupId", StringComparison.Ordinal):
                                    writer.WriteNull("groupId");
                                    break;
                                case bool _ when element.Name.Equals("groupName", StringComparison.Ordinal):
                                    writer.WriteNull("groupName");
                                    break;
                                case bool _ when element.Name.StartsWith("_", StringComparison.Ordinal):
                                    break;
                                default:
                                    element.WriteTo(writer);
                                    break;
                            }
                        }
                        writer.WriteEndObject();
                        writer.Flush();

                        Console.WriteLine(1111);
                        var ret = await _azureCosmos
                                  .GetCosmosClient()
                                  .GetContainer("TEAMModelOS", "Student")
                                  .ReplaceItemStreamAsync(stream, id, new PartitionKey($"Base-{schoolId}"));

                        if (ret.Status == (int)HttpStatusCode.OK)
                        {
                            sucStuds.Add(id);
                        }
                        else
                        {
                            impStuds.Remove(id);
                            errorIds.Add(id);
                            await _dingDing.SendBotMsg(
                                $"IES5，{_option.Location}，StudentController/removeStudentClassInfo()，CosmosDB response:{ret.Status}\nBase-{schoolId}，id:{id}",
                                GroupNames.醍摩豆服務運維群組);
                        }
                        Console.WriteLine(2222);
                    }
                }
            }
            Console.WriteLine(333);
            //將impStuds內的資料移除sucStuds及errorIds，所得的結果就是不存在於資料庫的id。
            sucStuds.ForEach(o => impStuds.Remove(o));
            errorIds.ForEach(o => impStuds.Remove(o));
            Console.WriteLine(444);
            return (sucStuds, impStuds, errorIds);
        }

        /// <summary>
        /// 取得教室資訊，使用classId進行查詢。
        /// </summary>
        /// <returns></returns>
        private async Task<Dictionary<string, JsonElement>> getClassInfoUseId(string schoolId, List<string> classIds)
        {
            try
            {
                if (!(classIds == null || classIds.Count == 0))
                {
                    string queryText = $"SELECT * FROM c WHERE c.code = 'Class-{schoolId}' AND c.id IN ({string.Join(",", classIds.Select(o => $"'{o}'"))})";

                    Dictionary<string, JsonElement> dicClassInfo = new Dictionary<string, JsonElement>();

                    await foreach (Response item in _azureCosmos.GetCosmosClient().GetContainer("TEAMModelOS", "School")
                        .GetItemQueryStreamIterator(queryText: queryText, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Class-{schoolId}") }))
                    {
                        using var json = await JsonDocument.ParseAsync(item.ContentStream);
                        if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                        {
                            var classInfos = json.RootElement.GetProperty("Documents").EnumerateArray();
                            while (classInfos.MoveNext())
                            {
                                JsonElement account = classInfos.Current;
                                string id = account.GetProperty("id").GetString();
                                dicClassInfo.Add(id, account.Clone());
                            }
                        }
                    }
                    return dicClassInfo;
                }
                else return null;

            }
            catch (CosmosException ex)
            {
                await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/getClassInfoUseId()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/getClassInfoUseId()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            return null;
        }

        /// <summary>
        /// 取得教室資訊，使用classNo進行查詢。
        /// </summary>
        /// <returns></returns>
        private async Task<Dictionary<string, Class>> getClassInfoUseNo(string schoolId, Dictionary<string, (string className, string periodId,  int year,string no)> classNos)
        {
            try
            {
                Dictionary<string, Class> dicClassInfo = new Dictionary<string, Class>();
                if (!(classNos == null || classNos.Count == 0))
                {
                   
                    foreach (var key in classNos.Keys) {
                        string queryText = $"SELECT * FROM c WHERE c.code = 'Class-{schoolId}' AND c.no='{classNos[key].no}' and c.year={classNos[key].year} and c.periodId='{classNos[key].periodId}' ";
                        await foreach (var item in _azureCosmos.GetCosmosClient().GetContainer("TEAMModelOS", "School")
                       .GetItemQueryIterator<Class>(queryText: queryText, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Class-{schoolId}") }))
                        {
                            dicClassInfo[item.id] = item;
                        }
                    }
                    return dicClassInfo;
                }
                else return null;

            }
            catch (CosmosException ex)
            {
                await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/getClassInfoUseId()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/getClassInfoUseId()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            return null;
        }
        
        /// <summary>
        /// 批量更新學生資訊，目前支持更新姓名、密碼、座號、性別及教室id，匯入時ClassId為必填。
        /// </summary>
        /// <param name="schoolId"></param>
        /// <param name="students"></param>
        /// <returns></returns>
        private async Task<(List<object> studs, Dictionary<string, List<string>> classDuplNos, List<string> nonexistentIds, List<string> errorIds, Dictionary<string, List<string>> errorNos, List<string> errorClassId)> 
            updateStudents(string schoolId, JsonElement.ArrayEnumerator students)
        {
            try
            {
                //整理輸入的資料->檢查輸入資料有沒有重複座號->取得欲加入的教室資訊->查詢學生並將資料更新並寫入

                //Key:id Value:學生基本資訊
                var studentInfos
                    = new Dictionary<string, (string salt, string pw, string name, int year, string pic, string gender, string mail, string mobile, string classId, string no)>();
                //用於進行座號是否重複查詢時使用
                var classStuds
                    = new Dictionary<string, List<(string id, string salt, string pw, string name, int year, string pic, string gender, string mail, string mobile, string classId, string no)>>();
                //紀錄教室"輸入"的學生座號是否有重複
                var impClassDuplNo = new Dictionary<string, List<string>>();
                //紀錄不存在的學生id
                var nonexistentIds = new List<string>();
                //紀錄跟現有雲端學生座號重複的
                var errorNos = new Dictionary<string, List<string>>();
                //紀錄處理錯誤的id，cosmosdb寫入時錯誤等...
                var errorIds = new List<string>();
                //紀錄沒找到的classId
                var errorClassId = new List<string>();
                //紀錄輸出結果
                var retStuds = new List<object>();

                //整理輸入資料
                while (students.MoveNext())
                {
                    JsonElement student = students.Current;
                    if (student.TryGetProperty("id", out var id))
                    {
                        //確認是否有id欄位，並且確認是否有給pw欄位，若無給或是null empty等，則使用id當密碼。
                        if (!string.IsNullOrWhiteSpace(id.GetString()))
                        {
                            string salt = null,
                                   pw = null,
                                   name = null,
                                  
                                   gender = null,
                                   mail = null,
                                   mobile = null,
                                   classId = null,
                                   no = null;
                            int year = 0;
                            //有給pw欄位才進行處理
                            if (student.TryGetProperty("pw", out var tmpPw))
                            {
                                salt = Utils.CreatSaltString(8);
                                pw = !string.IsNullOrWhiteSpace(tmpPw.GetString())
                                    ? Utils.HashedPassword(tmpPw.GetString(), salt)
                                    : Utils.HashedPassword(id.GetString(), salt);
                            }
                            if (student.TryGetProperty("name", out var tmpName)) name = tmpName.GetString();                            
                            if (student.TryGetProperty("gender", out var tmpGender)) gender = tmpGender.GetString();
                            if (student.TryGetProperty("mail", out var tmpMail)) mail = tmpMail.GetString();
                            if (student.TryGetProperty("mobile", out var tmpMobile)) mobile = tmpMobile.GetString();
                            if (student.TryGetProperty("year", out var tmpYear)) year = tmpYear.GetInt32();
                            if (student.TryGetProperty("classId", out var tmpclassId)) classId = tmpclassId.GetString();
                            else
                            {
                                errorClassId.Add(id.GetString());
                                continue;
                            }

                            //如果有給該欄位，且是給空的，代表要清空
                            if (student.TryGetProperty("no", out var tmpNo)) no = tmpNo.GetString();
                            
                            if (!studentInfos.ContainsKey(id.GetString()))
                            {
                                //如果有給classId且是給空的，則也將no設為空，後續才能將no欄位清空。
                                if (classId != null && classId.Length == 0) no = string.Empty;

                                //classId => 沒給欄位(null) 有給欄位("") 但更新一定得給教室?
                                if (classStuds.ContainsKey(classId))
                                {
                                    classStuds[classId].Add((id.GetString(), salt, pw, name, year, null, gender, null, null, classId, no));
                                }
                                else
                                {
                                    classStuds.Add(
                                        classId,
                                        new List<(string id, string salt, string pw, string name, int year, string pic, string gender, string mail, string mobile, string classId, string no)>()
                                        { (id.GetString(), salt, pw, name, year, null, gender, null, null, classId, no) });
                                }

                                //pic,mail,mobile暫不支持批量更新
                                studentInfos.Add(id.GetString(), (salt, pw, name, year, null, gender, null, null, classId, no));

                                //先將id加進去後面再做刪除動作
                                nonexistentIds.Add(id.GetString());
                            }
                        }
                    }
                }

                //檢查所有輸入的班級資料內，學生座號是否有重複。
                for (int ii = 0; ii <= classStuds.Values.Count - 1; ii++)
                {
                    var duplicateNo = classStuds.ElementAt(ii).Value.GroupBy(o => o.no).Where(o => o.Count() > 1).Select(o => o.Key).ToList();
                    duplicateNo.Remove("");
                    duplicateNo.Remove(null);
                    var wrongStuds = classStuds.ElementAt(ii).Value.Where(o => duplicateNo.Contains(o.no)).Select(o => o).ToList();
                    impClassDuplNo.Add(classStuds.ElementAt(ii).Key, wrongStuds.Select(o => o.id).ToList());

                    wrongStuds.ForEach(o => { classStuds.ElementAt(ii).Value.Remove(o); nonexistentIds.Remove(o.id); });
                    classStuds[classStuds.ElementAt(ii).Key] = classStuds.ElementAt(ii).Value;
                }

                //查詢欲加入的教室資訊。
                var classInfo = await getClassInfoUseId(schoolId, classStuds.Keys.ToList());

                //準備查詢db資料
                CosmosContainer cosmosContainer = _azureCosmos.GetCosmosClient().GetContainer("TEAMModelOS", "Student");

                //查要移除教室或是沒加入教室的學生
                
                foreach (var item in classStuds)
                {
                    //如果沒有任何學生要更新，則跳過該間教室。
                    if (item.Value.Count == 0) continue;

                    string classId = null, className = null, classNo = null, gradeId = null, periodId = null;

                    //如果教室不存在的話(填錯教室之類的狀況)，則記錄教室的id及學生id。
                   
                    if (classInfo.ContainsKey(item.Key))
                    {
                        classId = item.Key;
                        className = classInfo[item.Key].GetProperty("name").GetString();
                        classNo = classInfo[item.Key].GetProperty("no").GetString();
                        gradeId = classInfo[item.Key].GetProperty("gradeId").GetString();
                        periodId = classInfo[item.Key].GetProperty("periodId").GetString();
                    }
                    else if (item.Key.Length == 0)
                    {
                    }
                    else
                    {
                        //沒查到有該間教室的資訊，故將該間教室的ID及學生資料清單記起來，並且跳過不處理該資料。
                        errorClassId.Add(item.Key);
                        item.Value.ForEach(o => nonexistentIds.Remove(o.id));
                        continue;
                    }

                    //檢查座號是否有重複
                    //若只是改基本資料，該處還是會查到相同的座號。
                    var sutdNos = item.Value.Select(o => o.no).ToList();
                    var existNos = await checkStudNo(schoolId, item.Key, sutdNos);

                    //更新並寫入學生資料
                    if (item.Value.Count != 0)
                    {
                        //查學生的基本資料(該間教室全部的學生)
                        string queryText = $"SELECT * FROM c WHERE c.id IN ({string.Join(",", item.Value.Select(o => $"'{o.id}'"))})";

                        List<JsonElement> listStudent = new List<JsonElement>();

                        await foreach (Response responseItem in cosmosContainer
                            .GetItemQueryStreamIterator(
                                queryText: queryText,
                                requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Base-{schoolId}") }))
                        {
                            using var json = await JsonDocument.ParseAsync(responseItem.ContentStream);
                            if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                            {
                                var accounts = json.RootElement.GetProperty("Documents").EnumerateArray();
                                while (accounts.MoveNext())
                                {
                                    JsonElement account = accounts.Current;
                                    string id = account.GetProperty("id").GetString();
                                    nonexistentIds.Remove(id);
                                    //舊的座號，基本上不會重複，但可能會是空的
                                    string no = account.GetProperty("no").GetString();

                                    //用來記錄最後更改完的資料
                                    (string salt, string pw, string name, int year, string pic, string gender, string mail, string mobile, string classId, string no) tmpData
                                     = (studentInfos[id].salt, studentInfos[id].pw, studentInfos[id].name, studentInfos[id].year, studentInfos[id].pic, studentInfos[id].gender, studentInfos[id].mail, studentInfos[id].mobile, studentInfos[id].classId, studentInfos[id].no);

                                    bool isUpPwDone = false;
                                    bool isWrong = false;

                                    //開始組Json
                                    using var memoryStream = new MemoryStream();
                                    using var writer = new Utf8JsonWriter(memoryStream);
                                    writer.WriteStartObject();
                                    foreach (var element in account.EnumerateObject())
                                    {
                                        if (isWrong) break;
                                        switch (true)
                                        {
                                            case bool _ when element.Name.Equals("name", StringComparison.Ordinal):
                                                if (string.IsNullOrWhiteSpace(studentInfos[id].name))
                                                {
                                                    element.WriteTo(writer);
                                                    tmpData.name = element.Value.GetString();
                                                }
                                                else
                                                {
                                                    writer.WriteString("name", studentInfos[id].name);
                                                }
                                                break;
                                            case bool _ when element.Name.Equals("pw", StringComparison.Ordinal):
                                            case bool _ when element.Name.Equals("salt", StringComparison.Ordinal):
                                                if (!isUpPwDone && !string.IsNullOrWhiteSpace(studentInfos[id].salt) && !string.IsNullOrWhiteSpace(studentInfos[id].pw))
                                                {
                                                    writer.WriteString("salt", studentInfos[id].salt);
                                                    writer.WriteString("pw", studentInfos[id].pw);
                                                    isUpPwDone = true;
                                                }
                                                break;
                                            case bool _ when element.Name.Equals("gender", StringComparison.Ordinal):
                                                if (string.IsNullOrWhiteSpace(studentInfos[id].gender))
                                                {
                                                    element.WriteTo(writer);
                                                    tmpData.gender = element.Value.GetString();
                                                }
                                                else
                                                {
                                                    writer.WriteString("gender", studentInfos[id].gender);
                                                }
                                                break;
                                            case bool _ when element.Name.Equals("year", StringComparison.Ordinal):
                                                if (studentInfos[id].year==0)
                                                {
                                                    element.WriteTo(writer);
                                                    tmpData.year = element.Value.GetInt32();
                                                }
                                                else
                                                {
                                                    writer.WriteNumber("year", studentInfos[id].year);
                                                }
                                                break;
                                            case bool _ when element.Name.Equals("classId", StringComparison.Ordinal):
                                                if (studentInfos[id].classId != null && studentInfos[id].classId.Length == 0)
                                                {
                                                    writer.WriteNull("classId");
                                                    writer.WriteNull("groupId");
                                                    writer.WriteNull("groupName");
                                                    tmpData.classId = null;
                                                }
                                                else if (string.IsNullOrWhiteSpace(studentInfos[id].classId))
                                                {
                                                    element.WriteTo(writer);
                                                    tmpData.classId = element.Value.GetString();
                                                }
                                                else
                                                {
                                                    writer.WriteString("classId", studentInfos[id].classId);
                                                }
                                                break;
                                            case bool _ when element.Name.Equals("no", StringComparison.Ordinal):
                                                //移除座號的話會給空的
                                                if (studentInfos[id].no != null && studentInfos[id].no.Length == 0)
                                                {
                                                    writer.WriteNull("no");
                                                    tmpData.no = null;
                                                }
                                                else if (string.IsNullOrWhiteSpace(studentInfos[id].no))
                                                {
                                                    element.WriteTo(writer);
                                                    tmpData.no = element.Value.GetString();
                                                }
                                                else
                                                {
                                                    //如果要更新的座號，跟已存在的座號相同，則不進行更新。
                                                    //沒有設定過舊no，或舊座號與新座號不同，則要進行重複座號的檢查。 舊no=null or 舊no!=新no
                                                    if (string.IsNullOrWhiteSpace(no) || (!string.IsNullOrWhiteSpace(no) && !no.Equals(studentInfos[id].no)))
                                                    {
                                                        //如果有檢查到新座號和舊座號重複
                                                        if (existNos.Any(o => o.Item2.Contains(studentInfos[id].no))) //.Contains(studentInfos[id].no))
                                                        {
                                                            if (errorNos.ContainsKey(id))
                                                            {
                                                                errorNos[id].Add(studentInfos[id].no);
                                                            }
                                                            else
                                                            {
                                                                errorNos.Add(id, new List<string>() { studentInfos[id].no });
                                                            }
                                                            isWrong = true;
                                                            break;
                                                        }
                                                    }
                                                    writer.WriteString("no", studentInfos[id].no);
                                                }
                                                break;
                                            case bool _ when element.Name.StartsWith("_", StringComparison.Ordinal):
                                                break;
                                            default:
                                                element.WriteTo(writer);
                                                break;
                                        }
                                    }

                                    //如果有錯誤，如座號重覆等，就會跳過該次更新。
                                    if (isWrong)
                                    {
                                        await writer.DisposeAsync();
                                        continue;
                                    }

                                    //若密碼和鹽沒有更新，就把舊的資料寫回去
                                    if (!isUpPwDone)
                                    {
                                        writer.WriteString("salt", account.GetProperty("salt").GetString());
                                        writer.WriteString("pw", account.GetProperty("pw").GetString());
                                    }
                                    writer.WriteEndObject();
                                    writer.Flush();

                                    try
                                    {
                                        var ret = await cosmosContainer.ReplaceItemStreamAsync(memoryStream, id, new PartitionKey($"Base-{schoolId}"));
                                        //將更新完的id從字典內移除，保留沒查到的。
                                        if (ret.Status == (int)HttpStatusCode.OK)
                                        {
                                            nonexistentIds.Remove(id);
                                            retStuds.Add(new { id, tmpData.name, tmpData.pic, tmpData.year, tmpData.no, classId, classNo, className, gradeId, periodId });
                                        }
                                        else errorIds.Add(id);
                                    }
                                    catch (CosmosException ex)
                                    {
                                        await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/updateStudents()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
                                        errorIds.Add(id);
                                    }
                                    catch (Exception ex)
                                    {
                                        await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/updateStudents()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
                                        errorIds.Add(id);
                                    }
                                }
                            }
                            //將輸入不存在的資料移除。
                            nonexistentIds.ForEach(o => studentInfos.Remove(o));
                        }
                    }
                }
                errorClassId.ForEach(o => impClassDuplNo.Remove(o));

                return (retStuds, impClassDuplNo.Where(o => o.Value.Count != 0).ToDictionary(o => o.Key, o => o.Value), nonexistentIds, errorIds, errorNos, errorClassId);
            }
            catch (CosmosException ex)
            {
                await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/updateStudents()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/updateStudents()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            return (null, null, null, null, null, null);
        }

        /// <summary>
        /// 創建學生帳號，目前SDK4.0預覽版還不支援批量創建(TransactionalBatch)，待SDK正式發行時在優化此代碼。
        /// </summary>
        /// <param name="userStudents"></param>
        /// <returns>已存在的ID</returns>
        private async Task<(bool isSuc, List<string> existId)> createStudents(List<Student> userStudents)
        {
            var existId = new List<string>();
            var exceptions = new List<Exception>();
            try
            {
                var container = _azureCosmos.GetCosmosClient().GetContainer("TEAMModelOS", "Student");

                Parallel.ForEach(userStudents, async item =>
                {
                    try
                    {
                        await container.CreateItemAsync(item);
                    }
                    catch (CosmosException ex)
                    {
                        if (ex.Status == (int)HttpStatusCode.Conflict) existId.Add(item.id);
                        else exceptions.Add(ex);
                    }
                    catch (Exception ex)
                    {
                        exceptions.Add(ex);
                    }
                });
                if (exceptions.Count == 0) return (true, existId);
                else if (exceptions.Count > 1) throw new AggregateException(exceptions);
                else if (exceptions.Count == 1) throw exceptions.Single();
            }
            catch (AggregateException ex)
            {
                Console.WriteLine(ex.Message);
                await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/createStudents()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/createStudents()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
            }
            return (false, existId);
        }

        /// <summary>
        /// 取得該教室的學生座號，若有給座號LIST，則座號存在才會被查到；反之，若沒給則會將該間教室所有座號抓出來。
        /// </summary>
        /// <param name="schoolId"></param>
        /// <param name="classId"></param>
        /// <param name="nos"></param>
        /// <returns></returns>
        private async Task<List<(string id,string no)>> checkStudNo(string schoolId, string classId, List<string> nos = null)
        {
            List<(string id, string no)> ret = new List<(string id, string no)>();

            string queryText = $"SELECT c.id, c.no FROM c WHERE c.classId = '{classId}' AND c.code = 'Base-{schoolId}'";
            if (nos != null) queryText += $"AND c.no IN ({string.Join(",", nos.Select(o => $"'{o}'"))})";
            await foreach (Response item in _azureCosmos.GetCosmosClient().GetContainer("TEAMModelOS", "Student")
                .GetItemQueryStreamIterator(queryText: queryText, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Base-{schoolId}") }))
            {
                using var json = await JsonDocument.ParseAsync(item.ContentStream);
                if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                {
                    var classInfos = json.RootElement.GetProperty("Documents").EnumerateArray();
                    while (classInfos.MoveNext())
                    {
                        JsonElement account = classInfos.Current;
                        string id = account.GetProperty("id").GetString();
                        string no = account.GetProperty("no").GetString();
                        ret.Add((id, no));
                    }
                }
            }
            return ret;
        }

        /// <summary>
        /// 取得年級資訊
        /// </summary>
        /// <param name="schoolId"></param>
        /// <returns>Key:periodId  Vaule:list gradeInfo</returns>
        private async Task<Dictionary<string, List<(int gradeId, string gradeName)>>> getGrades(string schoolId)
        {
            try
            {
                //Key:學制 Value:年級資訊list
                Dictionary<string, List<(int gradeId, string gradeName)>> dicPeriod = new Dictionary<string, List<(int gradeId, string gradeName)>>();

                var response = await _azureCosmos
                    .GetCosmosClient()
                    .GetContainer("TEAMModelOS", "School")
                    .ReadItemStreamAsync(schoolId, new PartitionKey("Base"));

                if (response.Status != (int)HttpStatusCode.OK) return null;

                using Stream stream = response.ContentStream;
                var jsonDoc = await JsonDocument.ParseAsync(stream);
                var emumObject = jsonDoc.RootElement.EnumerateObject();
                var period = jsonDoc.RootElement.GetProperty("period").EnumerateArray();

                while (period.MoveNext())
                {
                    List<(int gradeId, string gradeName)> gradeInfos = new List<(int gradeId, string gradeName)>();
                    JsonElement jsonPeriod = period.Current;
                    var periodId = jsonPeriod.GetProperty("id").GetString();
                    var grades = jsonPeriod.GetProperty("grades").ToObject<List<string>>();
                    for (int index = 0; index < grades.Count; index++) {
                        gradeInfos.Add((index, grades[index]));
                    }
                    //var grades = jsonPeriod.GetProperty("grades").EnumerateArray();
                    //while (grades.MoveNext())
                    //{
                    //    JsonElement grade = grades.Current;
                    //    var gradeId = grade.GetInt32();
                    //    var gradeName = grade.GetProperty("name").GetString();
                    //    gradeInfos.Add((gradeId, gradeName));
                    //}

                    dicPeriod.Add(periodId, gradeInfos);
                }
                return dicPeriod;
            }
            catch(Exception ex)
            {
                await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/getGrades()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
                return null;
            }
        }

        private struct studCreateInfo
        {
            public studCreateInfo(string id, string name, string gender, int year, string pw, string classId, string no,string periodId)
            {
                this.id = id;
                this.name = name;
                this.gender = gender;
                this.year = year;
                this.pw = pw;
                this.classId = classId;
                this.no = no;
                this.periodId = periodId;
            }
            public string id { get; }
            public string name { get; }
            public string gender { get; }
            public int year { get; }
            public string pw { get; }
            public string classId { get; }
            public string no { get; }
            public string periodId { get; set; }
        }

        /// <summary>
        /// 學生登入
        /// </summary>
        /// <param name = "request" ></ param >
        [AllowAnonymous]
        [HttpPost("login")]
        public async Task<IActionResult> Login(JsonElement request)
        {
            try
            {
                var client = _azureCosmos.GetCosmosClient();
                var schoolClient = client.GetContainer("TEAMModelOS", "School");
                var teacherClient = client.GetContainer("TEAMModelOS", "Teacher");
                var studentClient = client.GetContainer("TEAMModelOS", "Student");
                //參數取得
                if (!request.TryGetProperty("school_code", out JsonElement school_code)) return BadRequest();
                if (!request.TryGetProperty("id", out JsonElement id)) return BadRequest();
                if (!request.TryGetProperty("pw", out JsonElement pw)) return BadRequest();  

                var response = await studentClient.ReadItemStreamAsync(id.GetString(), new PartitionKey($"Base-{school_code.GetString().ToLower()}"));
                if (response.Status == 200)
                {
                    var rjson = await JsonDocument.ParseAsync(response.ContentStream);
                    rjson.RootElement.TryGetProperty("salt", out JsonElement salt);
                    rjson.RootElement.TryGetProperty("pw", out JsonElement dbpw);
                    rjson.RootElement.TryGetProperty("name", out JsonElement name);
                    rjson.RootElement.TryGetProperty("picture", out JsonElement picture);
                    rjson.RootElement.TryGetProperty("classId", out JsonElement classId);
                    rjson.RootElement.TryGetProperty("no", out JsonElement no);
                    rjson.RootElement.TryGetProperty("groupId", out JsonElement groupId);
                    rjson.RootElement.TryGetProperty("groupName", out JsonElement groupName);

                    var HashedPW = Utils.HashedPassword(pw.ToString(), salt.ToString());
                    if (HashedPW.Equals(dbpw.GetString()))
                    {
                        //班級課程
                        object classinfo = null;
                        List<object> courses = new List<object>();
                        ////校本
                        //取得所屬預設班級信息
                        if (!classId.ValueKind.Equals(JsonValueKind.Null) && classId.ValueKind.Equals(JsonValueKind.String))
                        {
                            var query = $"SELECT c.code, c.id, c.name, c.periodId, c.gradeId FROM c WHERE c.id = '{classId.GetString()}'";
                            await foreach (var item in schoolClient.GetItemQueryStreamIterator(queryText: query, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Class-{school_code}") }))
                            {
                                using var json = await JsonDocument.ParseAsync(item.ContentStream);
                                if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                                {
                                    foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                                    {
                                        classinfo = obj.ToObject<object>();
                                    }
                                }
                            }
                        }
                        //取得該學生跑班課名單ID
                        List<string> stulistidsSch = new List<string>();
                        var querysl = $"SELECT c.id FROM c JOIN students IN c.students WHERE students.id = '{id.GetString()}' AND students.code = 'Base-{school_code}'";
                        await foreach (var item in schoolClient.GetItemQueryStreamIterator(queryText: querysl, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"StuList-{school_code}") }))
                        {
                            using var json = await JsonDocument.ParseAsync(item.ContentStream);
                            if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                            {
                                foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                                {
                                    stulistidsSch.Add(obj.GetProperty("id").ToString());
                                }
                            }
                        }
                        //取得該學生的學校課程名單
                        var queryc = $"SELECT DISTINCT c.id, c.name, schedule.class, schedule.time, schedule.notice, c.scope FROM c JOIN schedule IN c.schedule WHERE (schedule.class.id = '{classId}' AND schedule.stulist = null) OR (ARRAY_CONTAINS({JsonSerializer.Serialize(stulistidsSch)}, schedule.stulist, true))";
                        await foreach (var item in schoolClient.GetItemQueryStreamIterator(queryText: queryc, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Course-{school_code}") }))
                        {
                            using var json = await JsonDocument.ParseAsync(item.ContentStream);
                            if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                            {
                                foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                                {
                                    courses.Add(obj.ToObject<object>());
                                }
                            }
                        }
                        ////個人
                        //取得該學生跑班課名單ID
                        Dictionary<string, Dictionary<string, string>> stulistidsTea = new Dictionary<string, Dictionary<string, string>>();
                        var queryslt = $"SELECT c.id, c.course.id as courseId, c.course.code as courseCode FROM c JOIN students IN c.students WHERE students.id = '{id.GetString()}' AND students.code = 'Base-{school_code}'";
                        await foreach (var item in teacherClient.GetItemQueryStreamIterator(queryText: queryslt, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("StuList") }))
                        {
                            using var json = await JsonDocument.ParseAsync(item.ContentStream);
                            var js = json.RootElement.ToJsonString();
                            if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                            {
                                foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                                {
                                    string courseCode = "";
                                    if (obj.TryGetProperty("courseCode", out var code)) {
                                        courseCode = code.GetString();
                                    }
                                    string courseId = "";
                                    if (obj.TryGetProperty("courseId", out var cosid))
                                    {
                                        courseId = cosid.GetString();
                                    }
                                    string stulistId = "";
                                    if (obj.TryGetProperty("id", out var listId))
                                    {
                                        stulistId = listId.GetString();
                                    }
                                    if (!string.IsNullOrEmpty(courseCode)) {
                                        if (!stulistidsTea.ContainsKey(courseCode))
                                        {
                                            Dictionary<string, string> pCourseIdDic = new Dictionary<string, string>();
                                            pCourseIdDic.Add(courseId, stulistId);
                                            stulistidsTea.Add(courseCode, pCourseIdDic);
                                        }
                                        else
                                        {
                                            if (!stulistidsTea[courseCode].ContainsKey(courseId))
                                            {
                                                stulistidsTea[courseCode].Add(courseId, stulistId);
                                            }
                                        }
                                    }
                                }
                            }
                        }
                        //取得該學生的老師個人課程名單
                        foreach (KeyValuePair<string, Dictionary<string, string>> item in stulistidsTea)
                        {
                            string courseCode = item.Key;
                            Dictionary<string, string> courseIdDic = item.Value;
                            string stucourseWhere = string.Empty;
                            foreach (KeyValuePair<string, string> itemDic in courseIdDic) {
                                string courseId = itemDic.Key;
                                string stuListId = itemDic.Value;
                                if(!string.IsNullOrWhiteSpace(stucourseWhere))
                                {
                                    stucourseWhere += " OR ";
                                }
                                stucourseWhere += $"( c.id = '{courseId}' AND schedule.stulist = '{stuListId}' )";
                            }
                            var querycst = $"SELECT DISTINCT c.id, c.name, schedule.class, schedule.time, schedule.notice, c.scope FROM c JOIN schedule IN c.schedule WHERE {stucourseWhere}";
                            await foreach (var itemcs in teacherClient.GetItemQueryStreamIterator(queryText: querycst, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"{courseCode}") }))
                            {
                                using var json = await JsonDocument.ParseAsync(itemcs.ContentStream);
                                if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                                {
                                    foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                                    {
                                        courses.Add(obj.ToObject<object>());
                                    }
                                }
                            }
                        }

                        // BLOB(學校，唯讀)
                        var (blob_uri, blob_sas) = _azureStorage.GetBlobContainerSAS(school_code.GetString().ToLower(), BlobContainerSasPermissions.Read);
                        
                        //換取AuthToken，提供給前端
                        var auth_token = JwtAuthExtension.CreateAuthToken(_option.HostName, id.GetString(), name.GetString(), picture.GetString(), _option.JwtSecretKey, schoolID: school_code.GetString(), roles: new[] { "student" });
                        
                        return Ok(new { error = 0, auth_token, blob_uri, blob_sas, classinfo, courses });
                    }
                    else
                    {
                        return Ok(new { error = 1, message = "账号或密码错误" });
                    }
                }
                else
                {
                    return Ok(new { error = 2, message = "無此帳號存在" });
                }                
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/login()\n{ex.Message}{ex.StackTrace}", GroupNames.醍摩豆服務運維群組);
                return BadRequest();
            }
        }

        //查询学生名单详情
        [ProducesDefaultResponseType]
        //[AuthToken(Roles = "Teacher")]
        [HttpPost("get-summary-student")]
        public async Task<IActionResult> getSummary(JsonElement requert)
        {
            try
            {
                if (!requert.TryGetProperty("students", out JsonElement students)) return BadRequest();
                //if (!requert.TryGetProperty("scope", out JsonElement scope)) return BadRequest();
                var client = _azureCosmos.GetCosmosClient();
                List<Students> stuList = students.ToObject<List<Students>>();
                List<object> stus = new List<object>();               
                foreach (Students stu in stuList) {
                    var query = $"select c.id,c.name,c.picture,c.classId,c.code,c.groupId,c.groupName,c.no from c where c.id = '{stu.id}'";
                    await foreach (var item in client.GetContainer("TEAMModelOS", "Student").GetItemQueryStreamIterator(queryText: query, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"{stu.code}") }))
                    {
                        using var json = await JsonDocument.ParseAsync(item.ContentStream);
                        if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                        {
                            foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                            {
                                stus.Add(obj.ToObject<object>());
                            }
                        }
                    }
                }              
                return Ok(new { stus });
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"OS，{_option.Location}，student/get-summary-student()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
                return BadRequest();
            }

        }

        /// <summary>
        /// 學生簡易登入
        /// </summary>
        /// <param name = "request" ></ param >
        [AllowAnonymous]
        [HttpPost("login-simple")]
        public async Task<IActionResult> LoginSimple(JsonElement request)
        {
            try
            {
                var client = _azureCosmos.GetCosmosClient();
                var schoolClient = client.GetContainer("TEAMModelOS", "School");
                var studentClient = client.GetContainer("TEAMModelOS", "Student");
                //參數取得
                if (!request.TryGetProperty("school_code", out JsonElement school_code)) return BadRequest();
                if (!request.TryGetProperty("id", out JsonElement id)) return BadRequest();
                if (!request.TryGetProperty("pw", out JsonElement pw)) return BadRequest();

                var response = await studentClient.ReadItemStreamAsync(id.GetString(), new PartitionKey($"Base-{school_code.GetString().ToLower()}"));
                if (response.Status == 200)
                {
                    var rjson = await JsonDocument.ParseAsync(response.ContentStream);
                    rjson.RootElement.TryGetProperty("salt", out JsonElement salt);
                    rjson.RootElement.TryGetProperty("pw", out JsonElement dbpw);
                    rjson.RootElement.TryGetProperty("name", out JsonElement name);
                    rjson.RootElement.TryGetProperty("picture", out JsonElement picture);
                    rjson.RootElement.TryGetProperty("classId", out JsonElement classId);
                    rjson.RootElement.TryGetProperty("no", out JsonElement no);
                    rjson.RootElement.TryGetProperty("groupId", out JsonElement groupId);
                    rjson.RootElement.TryGetProperty("groupName", out JsonElement groupName);
                    dynamic user = new ExpandoObject();
                    user.no = no;
                    user.groupId = groupId;
                    user.groupName = groupName;

                    var HashedPW = Utils.HashedPassword(pw.ToString(), salt.ToString());
                    if (HashedPW.Equals(dbpw.GetString()))
                    {
                        //取得所屬預設班級信息
                        object classinfo = null;
                        if (!classId.ValueKind.Equals(JsonValueKind.Null) && classId.ValueKind.Equals(JsonValueKind.String))
                        {
                            var query = $"SELECT c.id, c.no, c.name FROM c WHERE c.id = '{classId.GetString()}'";
                            await foreach (var item in schoolClient.GetItemQueryStreamIterator(queryText: query, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Class-{school_code}") }))
                            {
                                using var json = await JsonDocument.ParseAsync(item.ContentStream);
                                if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                                {
                                    foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                                    {
                                        classinfo = obj.ToObject<object>();
                                    }
                                }
                            }
                        }
                        //換取AuthToken，提供給前端
                        var auth_token = JwtAuthExtension.CreateAuthToken(_option.HostName, id.GetString(), name.GetString(), picture.GetString(), _option.JwtSecretKey, schoolID: school_code.GetString(), roles: new[] { "student" });
                        //其他訊息
                        dynamic school = new ExpandoObject();
                        //回傳
                        return Ok(new { error = 0, auth_token, classinfo, user });
                    }
                    else
                    {
                        return Ok(new { error = 1, message = "Invalid account or password" });
                    }
                }
                else
                {
                    return Ok(new { error = 2, message = "Invalid account" });
                }
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"IES5，{_option.Location}，StudentController/login-simple()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
                return BadRequest();
            }
        }
        //TODO 此API需處理對應前端返回的相關數據
        [ProducesDefaultResponseType]
       [ AuthToken(Roles = "student")]
        [HttpPost("get-school-info")]

        public async Task<IActionResult> GetSchoolInfo(JsonElement requert)
        {
            try
            {
                var (id, _, _, school) = HttpContext.GetAuthTokenInfo();
                var client = _azureCosmos.GetCosmosClient();
                List<string> roles = new List<string>();
                List<string> permissions = new List<string>();
                int size = 0;
                var response = await client.GetContainer("TEAMModelOS", "Student").ReadItemStreamAsync(id, new PartitionKey($"Base-{school}"));
                if (response.Status == 200)
                {
                    using var json = await JsonDocument.ParseAsync(response.ContentStream);
                    if (json.RootElement.TryGetProperty("size", out JsonElement _size) && _size.ValueKind == JsonValueKind.Number)
                    {
                        size = _size.GetInt32();
                    }
                    if (json.RootElement.TryGetProperty("roles", out JsonElement _roles) && _roles.ValueKind != JsonValueKind.Null)
                    {
                        foreach (var obj in _roles.EnumerateArray())
                        {
                            roles.Add(obj.GetString());
                        }
                    }
                    if (json.RootElement.TryGetProperty("permissions", out JsonElement _permissions) && _permissions.ValueKind != JsonValueKind.Null)
                    {
                        foreach (var obj in _permissions.EnumerateArray())
                        {
                            permissions.Add(obj.GetString());
                        }
                    }
                }
                else //無此學校資料
                {
                    return Ok(new { status = 404 });
                }
                if (roles.Count == 0)
                {

                    roles.Add("student");
                }
               
                //TODO JJ，调整为取得学校基础设置数据，取代下方學校學制、年級
                object school_base = null;
                response = await client.GetContainer("TEAMModelOS", "School").ReadItemStreamAsync(school, new PartitionKey("Base"));
                if (response.Status == 200)
                {
                    using var json = await JsonDocument.ParseAsync(response.ContentStream);
                    school_base = json.RootElement.ToObject<object>();
                }

                //取得班级
                List<object> school_classes = new List<object>();
                await foreach (var item in client.GetContainer("TEAMModelOS", "School").GetItemQueryStreamIterator(queryText: $"SELECT c.id,c.x,c.y,c.name,c.year,c.teacher,c.periodId,c.gradeId,c.room,c.sn,c.no,c.style,c.status,c.openType,c.scope, ARRAY_LENGTH(c.students) AS studCount FROM c", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Class-{school}") }))
                {
                    var jsonc = await JsonDocument.ParseAsync(item.ContentStream);
                    foreach (var classeinfo in jsonc.RootElement.GetProperty("Documents").EnumerateArray())
                    {
                        school_classes.Add(classeinfo.ToObject<object>());
                    }
                }
                //取得教室
                List<Room> school_rooms = new List<Room>();
                await foreach (var item in client.GetContainer("TEAMModelOS", "School").GetItemQueryIterator<Room>(queryText: $"select value(c) from c ",
                requestOptions: new QueryRequestOptions() { PartitionKey = new Azure.Cosmos.PartitionKey($"Room-{school}") }))
                {
                    school_rooms.Add(item);
                }

                return Ok(new { school_base, school_classes, school_rooms, status =200});
            }
            catch(CosmosException ex) {
                return Ok(new { status=ex.Status }); ;
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"IES5，{_option.Location}，Student/get-school-info()\n{ex.Message}{ex.StackTrace}", GroupNames.醍摩豆服務運維群組);
                return BadRequest();
            }
        }
    }
}