TEAMModelOS/TEAMModelOS/Controllers/Client/HiTeachController.cs

using Azure.Cosmos;
using Azure.Storage.Blobs.Models;
using Azure.Storage.Sas;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Options;
using System;
using System.Collections.Generic;
using System.IdentityModel.Tokens.Jwt;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.Json;
using System.Threading.Tasks;
using TEAMModelOS.Models;
using TEAMModelOS.Filter;
using TEAMModelOS.SDK.DI;
using TEAMModelOS.SDK.Extension;
using Microsoft.Azure.Cosmos.Table;
using TEAMModelOS.SDK.Models;
using System.Dynamic;

namespace TEAMModelOS.Controllers.Client
{
    [ProducesResponseType(StatusCodes.Status200OK)]
    [ProducesResponseType(StatusCodes.Status400BadRequest)]
    //[Authorize(Roles = "HiTeach")]
    [Route("hiteach")]
    [ApiController]
    
    public class HiTeachController : ControllerBase
    {
        private readonly AzureStorageFactory _azureStorage;
        private readonly AzureRedisFactory _azureRedis;
        private readonly AzureCosmosFactory _azureCosmos;
        private readonly DingDing _dingDing;
        private readonly Option _option;
        private readonly SnowflakeId _snowflakeId;

        public HiTeachController(
            AzureStorageFactory azureStorage,
            AzureRedisFactory azureRedis,
            AzureCosmosFactory azureCosmos,
            DingDing dingDing,
            SnowflakeId snowflakeId,
            IOptionsSnapshot<Option> option)
        {
            _azureStorage = azureStorage;
            _azureRedis = azureRedis;
            _azureCosmos = azureCosmos;
            _dingDing = dingDing;
            _snowflakeId = snowflakeId;
            _option = option?.Value;
        }

        [ProducesDefaultResponseType]
        [HttpPost("get-teacher-info")]
        public async Task<IActionResult> GetTeacherInfo()
        {
            //Debug
            //string json = System.Text.Json.JsonSerializer.Serialize(id_token);
            try
            {
                string id_token = HttpContext.GetXAuth("IdToken");
                if(string.IsNullOrEmpty(id_token)) return BadRequest();
                var jwt = new JwtSecurityToken(id_token);
                if (!jwt.Payload.Iss.Equals("account.teammodel", StringComparison.OrdinalIgnoreCase)) return BadRequest();
                var id = jwt.Payload.Sub;
                jwt.Payload.TryGetValue("name", out object name);
                jwt.Payload.TryGetValue("picture", out object picture);

                List<object> schools = new List<object>();
                string defaultschool = null;
                //TODK 取得Teacher 個人相關數據(課程清單、虛擬教室清單、歷史紀錄清單等)，學校數據另外API處理，多校切換時不同
                var client = _azureCosmos.GetCosmosClient();
                var response = await client.GetContainer("TEAMModelOS", "Teacher").ReadItemStreamAsync(id, new PartitionKey("Base"));
                if (response.Status == 200)
                {
                    var jsonsc = await JsonDocument.ParseAsync(response.ContentStream);
                    if (jsonsc.RootElement.TryGetProperty("schools", out JsonElement value))
                    {
                        foreach (var obj in value.EnumerateArray())
                        {
                            string statusNow = obj.GetProperty("status").ToString();
                            if(statusNow == "join") //成為老師的才放入
                            {
                                dynamic schoolExtobj = new ExpandoObject();
                                schoolExtobj.schoolId = obj.GetProperty("schoolId");
                                schoolExtobj.name = obj.GetProperty("name");
                                schoolExtobj.status = obj.GetProperty("status");
                                schools.Add(schoolExtobj);
                            }
                        }
                    }
                    //預設學校ID
                    if (jsonsc.RootElement.TryGetProperty("defaultSchool", out JsonElement valueD) && !string.IsNullOrEmpty(valueD.ToString()))
                    {
                        defaultschool = valueD.ToString();
                    }
                }
                else
                {
                    //如果沒有，則初始化Teacher基本資料到Cosmos
                    using var stream = new MemoryStream();
                    using var writer = new Utf8JsonWriter(stream); //new JsonWriterOptions() { Indented = true }
                    writer.WriteStartObject();
                    writer.WriteString("code", "Base");
                    writer.WriteString("id", id);
                    writer.WriteString("name", name?.ToString());
                    writer.WriteString("picture", picture?.ToString());
                    writer.WriteStartArray("schools");
                    writer.WriteEndArray();
                    writer.WriteEndObject();
                    writer.Flush();
                    //Debug
                    //string teacher = Encoding.UTF8.GetString(stream.ToArray());
                    response = await _azureCosmos.GetCosmosClient().GetContainer("TEAMModelOS", "Teacher").CreateItemStreamAsync(stream, new PartitionKey("Base"));
                }

                //老師個人課程清單
                List<object> courses = new List<object>();
                await foreach (var item in client.GetContainer("TEAMModelOS", "Teacher").GetItemQueryStreamIterator(queryText: $"SELECT c.id, c.name, c.schedule, c.scope FROM c ", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Course-{id}") }))
                {
                    var jsontcs = await JsonDocument.ParseAsync(item.ContentStream);
                    if (jsontcs.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                    {
                        foreach (var obj in jsontcs.RootElement.GetProperty("Documents").EnumerateArray())
                        {
                            dynamic courseExtobj = new ExpandoObject();
                            courseExtobj.id = Convert.ToString(obj.GetProperty("id"));
                            courseExtobj.name = Convert.ToString(obj.GetProperty("name"));
                            courseExtobj.scope = Convert.ToString(obj.GetProperty("scope"));
                            List<object> classes = new List<object>();
                            if(obj.TryGetProperty("schedule", out JsonElement schedule))
                            {
                                foreach (var scheduleobj in schedule.EnumerateArray())
                                {
                                    dynamic classExtobj = new ExpandoObject();
                                    classExtobj.id = null;
                                    classExtobj.code = null;
                                    classExtobj.teacher = null;
                                    if (scheduleobj.TryGetProperty("teacherId", out JsonElement teacherId) && !string.IsNullOrWhiteSpace(Convert.ToString(teacherId)))
                                    {
                                        await foreach (var teaitem in client.GetContainer("TEAMModelOS", "Teacher").GetItemQueryStreamIterator(queryText: $"SELECT c.id, c.name FROM c WHERE c.id = '{teacherId}'", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base") }))
                                        {
                                            var jsontea = await JsonDocument.ParseAsync(teaitem.ContentStream);
                                            foreach (var teaobj in jsontea.RootElement.GetProperty("Documents").EnumerateArray())
                                            {
                                                classExtobj.teacher = new ExpandoObject();
                                                classExtobj.teacher = teaobj.ToObject<object>();
                                            }
                                        }
                                    }
                                    classExtobj.scope = "private";
                                    int stuCount = 0;
                                    string stuListId = Convert.ToString(scheduleobj.GetProperty("stulist"));
                                    classExtobj.stuListId = stuListId;
                                    await foreach (var stuitem in client.GetContainer("TEAMModelOS", "Teacher").GetItemQueryStreamIterator(queryText: $"SELECT c.id, c.name, ARRAY_LENGTH(c.students) as stucnt FROM c WHERE c.id = '{stuListId}'", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("StuList") }))
                                    {
                                        var jsonstu = await JsonDocument.ParseAsync(stuitem.ContentStream);
                                        foreach (var stuobj in jsonstu.RootElement.GetProperty("Documents").EnumerateArray())
                                        {
                                            classExtobj.name = stuobj.GetProperty("name");
                                            stuCount = stuobj.GetProperty("stucnt").GetInt32();
                                        }
                                    }
                                    classExtobj.stuCnt = stuCount;
                                    classExtobj.grpCnt = 0;
                                    classExtobj.gradeId = null;

                                    classes.Add(classExtobj);
                                }
                            }
                            courseExtobj.classes = classes;

                            courses.Add(courseExtobj);
                        }
                    }
                }

                //取得老師個人評測 (HiTeach只取source='1'(課中评量))
                List<object> exams = new List<object>();
                await foreach (var exam in client.GetContainer("TEAMModelOS", "Common").GetItemQueryStreamIterator(queryText: $"SELECT c.code, c.id, c.name, c.startTime, c.endTime ,c.year, c.source, c.type, c.progress, c.stuCount, c.scope FROM c WHERE c.source = '1'", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Exam-{id}") }))
                {
                    using var json = await JsonDocument.ParseAsync(exam.ContentStream);
                    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                    {
                        foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                        {
                            exams.Add(obj.ToObject<object>());
                        }
                    }
                }

                //取得Teacher Blob 容器位置及SAS 
                var container = _azureStorage.GetBlobContainerClient(id);
                await container.CreateIfNotExistsAsync(PublicAccessType.None); //嘗試創建Teacher私有容器，如存在則不做任何事，保障容器一定存在
                var (blob_uri, blob_sas) = _azureStorage.GetBlobContainerSAS(id, BlobContainerSasPermissions.Write | BlobContainerSasPermissions.Read | BlobContainerSasPermissions.List | BlobContainerSasPermissions.Delete);
                return Ok(new { blob_uri, blob_sas, schools, defaultschool, courses, exams });

            }
            catch (Exception ex)
            {
               // await _dingDing.SendBotMsg($"CoreAPI2，{_option.Location}，hiteach/GetTeacherInfo()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
                return BadRequest();
            }
        }

                
        [ProducesDefaultResponseType]
        [HttpPost("get-school-info")]       
        public async Task<IActionResult> GetSchoolInfo(JsonElement requert)
        {
            try
            {
                string id_token = HttpContext.GetXAuth("IdToken");
                if (string.IsNullOrEmpty(id_token)) return BadRequest();
                if (!requert.TryGetProperty("school_code", out JsonElement school_code)) return BadRequest();       
                var jwt = new JwtSecurityToken(id_token);
                if (!jwt.Payload.Iss.Equals("account.teammodel", StringComparison.Ordinal)) return BadRequest();
                var id = jwt.Payload.Sub;

                var client = _azureCosmos.GetCosmosClient();

                //取得學校學段、年級、科目、考試類型
                List<object> periods = new List<object>();
                List<object> grades = new List<object>();
                List<object> subjects = new List<object>();
                List<object> examTypes = new List<object>();
                var responsesch = await client.GetContainer("TEAMModelOS", "School").ReadItemStreamAsync(school_code.ToString(), new PartitionKey($"Base"));
                if (responsesch.Status == 200)
                {
                    var jsons = await JsonDocument.ParseAsync(responsesch.ContentStream);
                    if (jsons.RootElement.TryGetProperty("period", out JsonElement periodJobj))
                    {
                        foreach (var periodinfo in periodJobj.EnumerateArray())
                        {
                            dynamic periodExtobj = new ExpandoObject();
                            periodExtobj.id = periodinfo.GetProperty("id");
                            periodExtobj.name = periodinfo.GetProperty("name");
                            periods.Add(periodExtobj);
                            if (periodinfo.TryGetProperty("grades", out JsonElement gradesJobj))
                            {
                                foreach (var gradeinfo in gradesJobj.EnumerateArray())
                                {
                                    dynamic gradeExtobj = new ExpandoObject();
                                    gradeExtobj.id = gradeinfo.GetProperty("id");
                                    gradeExtobj.name = gradeinfo.GetProperty("name");
                                    gradeExtobj.periodId = periodinfo.GetProperty("id");
                                    grades.Add(gradeExtobj);
                                }
                            }
                            if (periodinfo.TryGetProperty("subjects", out JsonElement subjectsJobj))
                            {
                                foreach (var subjectinfo in subjectsJobj.EnumerateArray())
                                {
                                    dynamic subjectExtobj = new ExpandoObject();
                                    subjectExtobj.id = subjectinfo.GetProperty("id");
                                    subjectExtobj.name = subjectinfo.GetProperty("name");
                                    subjectExtobj.periodId = periodinfo.GetProperty("id");
                                    subjects.Add(subjectExtobj);
                                }
                            }
                            if (periodinfo.TryGetProperty("analysis", out JsonElement periodanalysisJobj))
                            {
                                if (periodanalysisJobj.TryGetProperty("type", out JsonElement examTypeJobj))
                                {
                                    foreach (var examType in examTypeJobj.EnumerateArray())
                                    {
                                        examTypes.Add(examType.ToObject<object>());
                                    }
                                }
                            }
                        }
                    }
                }
                else //無此學校資料
                {
                    return BadRequest();
                }

                //該老師排定的學校課程
                List<object> courses = new List<object>();
                var query = $"SELECT c.id, c.name, c.scope, c.subject, schedule.classId AS scheduleClassId, schedule.teacher AS scheduleTeacher, schedule.stulist AS scheduleStulist, schedule.time AS scheduleTime, schedule.notice AS scheduleNotice FROM c JOIN schedule IN c.schedule WHERE schedule.teacherId = '{id}'";
                //var query = $"SELECT c.id, c.name, c.teacher, cc.course, c.scope FROM c JOIN cc IN c.courses JOIN cct IN cc.teachers WHERE cct.id = '{id}'";
                await foreach (var item in client.GetContainer("TEAMModelOS", "School").GetItemQueryStreamIterator(queryText: query, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Course-{school_code}") }))
                {
                    var jsoncs = await JsonDocument.ParseAsync(item.ContentStream);
                    if (jsoncs.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                    {
                        foreach (var obj in jsoncs.RootElement.GetProperty("Documents").EnumerateArray())
                        {
                            //班級
                            string classIdNow = string.Empty;
                            if(obj.TryGetProperty("scheduleClassId", out JsonElement scheduleClassId))
                            {
                                classIdNow = Convert.ToString(scheduleClassId);
                            }
                            dynamic classExtobj = new ExpandoObject();
                            classExtobj.id = null;
                            classExtobj.code = null;
                            classExtobj.name = null;
                            classExtobj.scope = null;
                            classExtobj.gradeId = null;
                            classExtobj.teacher = null;
                            classExtobj.stuListId = null;
                            classExtobj.stuCnt = 0;
                            classExtobj.grpCnt = 0;
                            if (!string.IsNullOrWhiteSpace(classIdNow))
                            {
                                var querycl = $"SELECT c.code, c.id, c.name, c.teacher, c.scope, c.gradeId FROM c WHERE c.id = '{classIdNow}'";
                                await foreach (var itemcl in client.GetContainer("TEAMModelOS", "School").GetItemQueryStreamIterator(queryText: querycl, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Class-{school_code}") }))
                                {
                                    var jsoncl = await JsonDocument.ParseAsync(itemcl.ContentStream);
                                    foreach (var objcl in jsoncl.RootElement.GetProperty("Documents").EnumerateArray())
                                    {
                                        classExtobj.id = Convert.ToString(objcl.GetProperty("id"));
                                        classExtobj.code = Convert.ToString(objcl.GetProperty("code"));
                                        classExtobj.name = Convert.ToString(objcl.GetProperty("name"));
                                        classExtobj.scope = Convert.ToString(objcl.GetProperty("scope"));
                                        classExtobj.gradeId = Convert.ToString(objcl.GetProperty("gradeId"));
                                        classExtobj.teacher = objcl.GetProperty("teacher");
                                        classExtobj.stuCnt = 0;
                                        classExtobj.grpCnt = 0;
                                        var queryclstc = $"SELECT Count(1) AS stuCnt FROM c WHERE c.classId = '{classIdNow}'";
                                        await foreach (var itemclstc in client.GetContainer("TEAMModelOS", "Student").GetItemQueryStreamIterator(queryText: queryclstc, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Base-{school_code}") }))
                                        {
                                            var jsonclstc = await JsonDocument.ParseAsync(itemclstc.ContentStream);
                                            foreach (var objstc in jsonclstc.RootElement.GetProperty("Documents").EnumerateArray())
                                            {
                                                classExtobj.stuCnt = objstc.GetProperty("stuCnt").GetUInt32();
                                            }
                                        }
                                        var queryclgp = $"SELECT c.groupId FROM c WHERE c.classId = '{classIdNow}' AND IS_NULL(c.groupId)=false GROUP BY c.groupId";
                                        await foreach (var itemgp in client.GetContainer("TEAMModelOS", "Student").GetItemQueryStreamIterator(queryText: queryclgp, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Base-{school_code}") }))
                                        {
                                            var jsongp = await JsonDocument.ParseAsync(itemgp.ContentStream);
                                            if (jsongp.RootElement.TryGetProperty("_count", out JsonElement gpcount) && gpcount.GetUInt32() > 0)
                                            {
                                                classExtobj.grpCnt = gpcount.GetUInt32();
                                            }
                                        }
                                    }
                                }
                            }
                            var stuListId = obj.GetProperty("scheduleStulist");
                            if (!stuListId.ValueKind.Equals(JsonValueKind.Null))
                            {
                                classExtobj.stuListId = Convert.ToString(stuListId);
                                await foreach (var stuitem in client.GetContainer("TEAMModelOS", "School").GetItemQueryStreamIterator(queryText: $"SELECT ARRAY_LENGTH(c.students) as stucnt FROM c WHERE c.id = '{stuListId}'", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"StuList-{school_code}") }))
                                {
                                    var jsonstu = await JsonDocument.ParseAsync(stuitem.ContentStream);
                                    foreach (var stuobj in jsonstu.RootElement.GetProperty("Documents").EnumerateArray())
                                    {
                                        classExtobj.stuCnt = stuobj.GetProperty("stucnt").GetInt32();
                                    }
                                }
                            }
                            //課程
                            //var scheduleTeacherInfo = obj.GetProperty("scheduleTeacher");
                            string courseIdNow = obj.GetProperty("id").ToString();
                            var courseExist = courses.Where((dynamic x) => x.id == courseIdNow).FirstOrDefault();
                            if (courseExist == null)
                            {
                                dynamic courseExtobj = new ExpandoObject();
                                courseExtobj.id = courseIdNow;
                                courseExtobj.name = obj.GetProperty("name").ToString();
                                courseExtobj.scope = obj.GetProperty("scope").ToString();
                                courseExtobj.classes = new List<object>();
                                courseExtobj.subject = obj.GetProperty("subject");
                                //classExtobj.teacher = scheduleTeacherInfo;
                                courseExtobj.classes.Add(classExtobj);
                                courses.Add(courseExtobj);
                            }
                            else
                            {
                                //classExtobj.teacher = scheduleTeacherInfo;
                                courseExist.classes.Add(classExtobj);
                            }
                        }
                    }
                }

                //取得校園評測 (HiTeach只取source='1'(課中评量))
                List<object> exams = new List<object>();
                await foreach (var exam in client.GetContainer("TEAMModelOS", "Common").GetItemQueryStreamIterator(queryText: $"SELECT c.code, c.id, c.name, c.startTime, c.endTime ,c.year, c.source, c.type, c.progress, c.stuCount, c.scope FROM c WHERE c.source = '1'", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Exam-{school_code}") }))
                {
                    using var json = await JsonDocument.ParseAsync(exam.ContentStream);
                    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                    {
                        foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                        {
                            exams.Add(obj.ToObject<object>());
                        }
                    }
                }

                //取得School Blob 容器位置及SAS
                string school_code_blob = school_code.GetString().ToLower();
                var (blob_uri, blob_sas) = _azureStorage.GetBlobContainerSAS(school_code_blob, BlobContainerSasPermissions.Read | BlobContainerSasPermissions.List);

                return Ok(new { blob_uri, blob_sas, periods, grades, subjects, courses, examTypes, exams });
            }
            catch (Exception ex)
            {
                // await _dingDing.SendBotMsg($"CoreAPI2，{_option.Location}，hiteach/GetTeacherInfo()\n{ex.Message}", GroupNames.醍摩豆服務運維群組);
                return BadRequest();
            }
        }

        //取得試卷
        [ProducesDefaultResponseType]
        [HttpPost("get-paper")]
        public async Task<IActionResult> GetPaperList(JsonElement request)
        {
            try
            {
                //Header驗證
                string id_token = HttpContext.GetXAuth("IdToken");
                if (string.IsNullOrEmpty(id_token)) return BadRequest();
                var jwt = new JwtSecurityToken(id_token);
                if (!jwt.Payload.Iss.Equals("account.teammodel", StringComparison.Ordinal)) return BadRequest();
                var id = jwt.Payload.Sub;
                var client = _azureCosmos.GetCosmosClient();
                //參數
                if (!request.TryGetProperty("grant_type", out JsonElement grant_type)) return BadRequest();
                string partitionid = string.Empty;
                string container = string.Empty;
                if (grant_type.ToString() == "school")
                {
                    if (!request.TryGetProperty("school_code", out JsonElement school_code_json))
                    {
                        return BadRequest();
                    }
                    else
                    {
                        partitionid = school_code_json.ToString();
                        container = "School";
                    }
                }
                else
                {
                    partitionid = id.ToString();
                    container = "Teacher";
                }
                //SQL文
                List<object> papers = new List<object>();
                string queryWhere = " WHERE 1=1 ";
                string queryOption = string.Empty;
                //學段
                if (request.TryGetProperty("periodId", out JsonElement periodId) && container == "School")
                {
                    queryWhere += $" AND c.periodId = '{periodId}'";
                }
                //年級
                if (request.TryGetProperty("gradeId", out JsonElement gradeIds) && gradeIds.GetArrayLength() > 0 && container == "School")
                {
                    string queryOptionForGrade = string.Empty;
                    for (int i = 0; i < gradeIds.GetArrayLength(); i++)
                    {
                        if(!string.IsNullOrWhiteSpace(queryOptionForGrade))
                        {
                            queryOptionForGrade += " OR ";
                        }
                        queryOptionForGrade += $" ARRAY_CONTAINS(c.gradeIds, '{gradeIds[i]}', true)";
                    }
                    queryWhere += $" AND ( {queryOptionForGrade} )"; 
                }
                //科目ID
                if (request.TryGetProperty("subjectId", out JsonElement subjectId) && container == "School")
                {
                    queryWhere += $" AND c.subjectId = '{subjectId}'";
                }
                //科目名稱
                if (request.TryGetProperty("subjectName", out JsonElement subjectName))
                {
                    queryWhere += $" AND c.subjectName = '{subjectName}'";
                }
                int perpage = 0; //每頁幾條
                if (request.TryGetProperty("perpage", out JsonElement perpage_json)) perpage = perpage_json.GetInt32();
                int page = 0; //目前第幾頁
                if (request.TryGetProperty("page", out JsonElement page_json))
                {
                    if(page_json.GetInt32() > 0)
                    {
                        page = page_json.GetInt32() - 1;
                    }
                }
                string order = "createTime"; //排序項目
                if (request.TryGetProperty("order", out JsonElement order_json))
                {
                    if(order_json.ToString() == "useCount")
                    {
                        order = order_json.ToString();
                    }
                }    
                queryOption += $" Order By c." + order + " DESC ";
                if(perpage > 0 )
                {
                    queryOption += $" OFFSET {perpage * page} LIMIT {perpage}";
                }

                //資料取得
                await foreach (var item in client.GetContainer("TEAMModelOS", container).GetItemQueryStreamIterator(queryText: $"SELECT c.code, c.id, c.periodId, c.gradeIds, c.subjectId, c.subjectName, c.name, REPLACE(c.blob, 'index.json', '') AS blob, c.score, c.useCount, ARRAY_LENGTH(c.scoring) AS itemCount, c.createTime From c {queryWhere + queryOption}", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Paper-{partitionid}") }))
                {
                    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())
                        {
                            papers.Add(obj.ToObject<object>());
                        }
                    }
                }

                //總件數
                int totalCount = 0;
                await foreach (var item in client.GetContainer("TEAMModelOS", container).GetItemQueryStreamIterator(queryText: $"SELECT VALUE COUNT(1) From c {queryWhere}", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Paper-{partitionid}") }))
                {
                    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())
                        {
                            totalCount = obj.GetInt32();
                        }
                    }
                }

                return Ok(new { papers, totalCount });
            }
            catch (Exception ex)
            {
                return BadRequest();
            }
        }

        //取得試題
        [ProducesDefaultResponseType]
        [HttpPost("get-item")]
        public async Task<IActionResult> GetItemList(JsonElement request)
        {
            try
            {
                //Header驗證
                string id_token = HttpContext.GetXAuth("IdToken");
                if (string.IsNullOrEmpty(id_token)) return BadRequest();
                var jwt = new JwtSecurityToken(id_token);
                if (!jwt.Payload.Iss.Equals("account.teammodel", StringComparison.Ordinal)) return BadRequest();
                var id = jwt.Payload.Sub;
                var client = _azureCosmos.GetCosmosClient();
                //參數
                if (!request.TryGetProperty("grant_type", out JsonElement grant_type)) return BadRequest();
                string partitionid = string.Empty;
                string container = string.Empty;
                if (grant_type.ToString() == "school")
                {
                    if (!request.TryGetProperty("school_code", out JsonElement school_code_json))
                    {
                        return BadRequest();
                    }
                    else
                    {
                        partitionid = school_code_json.ToString();
                        container = "School";
                    }
                }
                else
                {
                    partitionid = id.ToString();
                    container = "Teacher";
                }
                //SQL文
                List<object> items = new List<object>();
                string queryWhere = " WHERE 1=1 ";
                string queryOption = string.Empty;
                //學段
                if (request.TryGetProperty("periodId", out JsonElement periodId) && container == "School")
                {
                    queryWhere += $" AND c.periodId = '{periodId}'";
                }
                //年級
                if (request.TryGetProperty("gradeId", out JsonElement gradeIds) && gradeIds.GetArrayLength() > 0 && container == "School")
                {
                    string queryOptionForGrade = string.Empty;
                    for (int i = 0; i < gradeIds.GetArrayLength(); i++)
                    {
                        if (!string.IsNullOrWhiteSpace(queryOptionForGrade))
                        {
                            queryOptionForGrade += " OR ";
                        }
                        queryOptionForGrade += $" ARRAY_CONTAINS(c.gradeIds, '{gradeIds[i]}', true)";
                    }
                    queryWhere += $" AND ( {queryOptionForGrade} )";
                }
                //科目ID
                if (request.TryGetProperty("subjectId", out JsonElement subjectId) && container == "School")
                {
                    queryWhere += $" AND c.subjectId = '{subjectId}'";
                }
                //科目名稱
                if (request.TryGetProperty("subjectName", out JsonElement subjectName))
                {
                    queryWhere += $" AND c.subjectName = '{subjectName}'";
                }
                //題型
                int dummy = 0;
                if (request.TryGetProperty("type", out JsonElement type))
                {
                    queryWhere += $" AND c.type = '{type}'";
                }
                //難度
                dummy = 0;
                if (request.TryGetProperty("level", out JsonElement level) && int.TryParse(level.ToString(), out dummy))
                {
                    queryWhere += $" AND c.level = {level}";
                }
                //層次
                if (request.TryGetProperty("field", out JsonElement field) && int.TryParse(field.ToString(), out dummy))
                {
                    queryWhere += $" AND c.field = {field}";
                }
                int perpage = 0; //每頁幾條
                if (request.TryGetProperty("perpage", out JsonElement perpage_json)) perpage = perpage_json.GetInt32();
                int page = 0; //目前第幾頁
                if (request.TryGetProperty("page", out JsonElement page_json))
                {
                    if (page_json.GetInt32() > 0)
                    {
                        page = page_json.GetInt32() - 1;
                    }
                }
                string order = "createTime"; //排序項目
                if (request.TryGetProperty("order", out JsonElement order_json))
                {
                    if (order_json.ToString() == "useCount")
                    {
                        order = order_json.ToString();
                    }
                }
                queryOption += $" Order By c." + order + " DESC ";
                if (perpage > 0)
                {
                    queryOption += $" OFFSET {perpage * page} LIMIT {perpage}";
                }

                //資料取得
                await foreach (var item in client.GetContainer("TEAMModelOS", container).GetItemQueryStreamIterator(queryText: $"SELECT c.id, c.periodId, c.gradeIds, c.subjectId, c.subjectName, c.blob, c.field, c.level, c.type, c.useCount, c.createTime From c {queryWhere + queryOption}", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Item-{partitionid}") }))
                {
                    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())
                        {
                            items.Add(obj.ToObject<object>());
                        }
                    }
                }

                //總件數
                int totalCount = 0;
                await foreach (var item in client.GetContainer("TEAMModelOS", container).GetItemQueryStreamIterator(queryText: $"SELECT VALUE COUNT(1) From c {queryWhere}", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Item-{partitionid}") }))
                {
                    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())
                        {
                            totalCount = obj.GetInt32();
                        }
                    }
                }

                return Ok(new { items, totalCount });
            }
            catch (Exception ex)
            {
                return BadRequest();
            }
        }

        //取得知識點
        [ProducesDefaultResponseType]
        [HttpPost("get-knowledge")]
        public async Task<IActionResult> GetKnowledgePointList(JsonElement request)
        {
            var client = _azureCosmos.GetCosmosClient();
            if (!request.TryGetProperty("school_code", out JsonElement school_code)) return BadRequest();
            //知識點
            List<object> points = new List<object>();
            await foreach (var item in client.GetContainer("TEAMModelOS", "School").GetItemQueryStreamIterator(queryText: $"SELECT c.id, c.name, c.subjectId FROM c WHERE c.type = 'point'", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Knowledge-{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())
                    {
                        points.Add(obj.ToObject<object>());
                    }
                }
            }

            return Ok(points);
        }

        //取得課綱
        [ProducesDefaultResponseType]
        [HttpPost("get-syllabus")]
        public async Task<IActionResult> GetSyllabusList(JsonElement request)
        {
            var client = _azureCosmos.GetCosmosClient();
            if (!request.TryGetProperty("school_code", out JsonElement school_code)) return BadRequest();
            //校本課綱
            List<SyllabusRole> syllabus = new List<SyllabusRole>();
            await foreach (var item in client.GetContainer("TEAMModelOS", "School").GetItemQueryStreamIterator(queryText: $"SELECT c.id, c.name, c.period, c.grade, c.semester, c.subject, c.scope, c.resourceCount, c.itemCount, c.children from c ", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Syllabus-{school_code}") }))
            {
                var jsons = await JsonDocument.ParseAsync(item.ContentStream);
                if (jsons.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                {
                    foreach (var obj in jsons.RootElement.GetProperty("Documents").EnumerateArray())
                    {
                        SyllabusRole syllabusRole = new SyllabusRole();
                        syllabusRole.id = obj.GetProperty("id").ToString();
                        syllabusRole.name = obj.GetProperty("name").ToString();
                        syllabusRole.period = obj.GetProperty("period");
                        syllabusRole.grade = obj.GetProperty("grade");
                        syllabusRole.semester = obj.GetProperty("semester");
                        syllabusRole.subject = obj.GetProperty("subject");
                        syllabusRole.resourceCount = obj.GetProperty("resourceCount").GetUInt16();
                        syllabusRole.itemCount = obj.GetProperty("itemCount").GetUInt16();
                        List<Syllabus> syllabusList = obj.GetProperty("children").ToObject<List<Syllabus>>();
                        syllabusList.Insert(0, new Syllabus { id = syllabusRole.id, name = syllabusRole.name, pid = "", order = 0 });
                        syllabusList = syllabusList.OrderBy(x => x.order).ToList();
                        syllabusRole.structure = CreateSyllabusTree(syllabusList);
                        syllabus.Add(syllabusRole);
                    }
                    //[DEBUG] string jsonString = System.Text.Json.JsonSerializer.Serialize(syllabusRoles);
                }
            }

            return Ok(syllabus);
        }

        //取得某班級的學生成員
        [ProducesDefaultResponseType]
        [HttpPost("get-students-list")]
        public async Task<IActionResult> GetStudentsList(JsonElement request)
        {
            string id_token = HttpContext.GetXAuth("IdToken");
            if (string.IsNullOrWhiteSpace(id_token)) return BadRequest();
            var jwt = new JwtSecurityToken(id_token);
            if (!jwt.Payload.Iss.Equals("account.teammodel", StringComparison.Ordinal)) return BadRequest();
            var id = jwt.Payload.Sub;
            if (!request.TryGetProperty("grant_type", out JsonElement grant_type)) return BadRequest();
            request.TryGetProperty("class_code", out JsonElement class_code);
            request.TryGetProperty("stulist_id", out JsonElement stulist_id);
            string classId = Convert.ToString(class_code);
            string stulist = Convert.ToString(stulist_id);
            if(string.IsNullOrWhiteSpace(classId) && string.IsNullOrWhiteSpace(stulist)) return BadRequest();
            request.TryGetProperty("school_code", out JsonElement school_code);
            if (grant_type.GetString() == "school" && string.IsNullOrEmpty(Convert.ToString(school_code))) return BadRequest();

            var client = _azureCosmos.GetCosmosClient();
            List<object> students = new List<object>();
            string container = (grant_type.GetString() == "school") ? "School" : "Teacher";
            //Case 1 取得stulist成員 (有stulist_id則優先取)
            if (!string.IsNullOrWhiteSpace(stulist))
            {
                string pk = (grant_type.GetString() == "school") ? $"StuList-{school_code}" : $"StuList";
                var querystl = $"SELECT c.students FROM c WHERE c.id = '{stulist}'";
                Dictionary<string, List<string>> stuDic = new Dictionary<string, List<string>>();
                await foreach (var item in client.GetContainer("TEAMModelOS", container).GetItemQueryStreamIterator(queryText: querystl, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey(pk) }))
                {
                    var json = await JsonDocument.ParseAsync(item.ContentStream);
                    if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                    {
                        foreach (var stuObj in json.RootElement.GetProperty("Documents").EnumerateArray())
                        {
                            foreach (var studentObj in stuObj.GetProperty("students").EnumerateArray())
                            {
                                string stuCode = studentObj.GetProperty("code").ToString();
                                string stuId = studentObj.GetProperty("id").ToString();
                                if (stuDic.ContainsKey(stuCode) && !stuDic[stuCode].Contains(stuId))
                                {
                                    if (!stuDic[stuCode].Contains(stuId))
                                    {
                                        stuDic[stuCode].Add(stuId);
                                    }
                                }
                                else
                                {
                                    List<string> stuIdList = new List<string>();
                                    stuIdList.Add(stuId);
                                    stuDic.Add(stuCode, stuIdList);
                                }
                                
                            }
                        }
                    }
                }
                if(stuDic.Count > 0)
                {
                    foreach (KeyValuePair<string, List<string>> stuDicRow in stuDic)
                    {
                        string stuCode = stuDicRow.Key;
                        List<string> stuIdList = stuDicRow.Value;
                        var queryst = $"SELECT c.id, c.name, null AS no, c.schoolId FROM c WHERE ARRAY_CONTAINS({JsonSerializer.Serialize(stuIdList)}, c.id)";
                        await foreach (var item in client.GetContainer("TEAMModelOS", "Student").GetItemQueryStreamIterator(queryText: queryst, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey(stuCode) }))
                        {
                            using var json = await JsonDocument.ParseAsync(item.ContentStream);
                            if (json.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                            {
                                foreach (var stuObj in json.RootElement.GetProperty("Documents").EnumerateArray())
                                {
                                    students.Add(stuObj.ToObject<object>());
                                }
                            }   
                        }
                    }
                }
            }
            //Case 2 取得班級固定成員
            if(grant_type.GetString() == "school" && students.Count == 0 && !string.IsNullOrWhiteSpace(classId) && string.IsNullOrWhiteSpace(stulist))
            {
                var query = $"SELECT c.id, c.name, c.no, c.schoolId, c.groupId, c.groupName FROM c WHERE c.classId = '{classId}'";
                await foreach (var item in client.GetContainer("TEAMModelOS", "Student").GetItemQueryStreamIterator(queryText: query, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Base-{school_code}") }))
                {
                    using var jsonst = await JsonDocument.ParseAsync(item.ContentStream);
                    if (jsonst.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                    {
                        foreach (var stuObj in jsonst.RootElement.GetProperty("Documents").EnumerateArray())
                        {
                            students.Add(stuObj.ToObject<object>());
                        }
                    }
                }
            }

            return Ok(new { students });
        }

        [ProducesDefaultResponseType]
        [HttpPost("start-lesson")]
        public async Task<IActionResult> StartLesson(JsonElement request)
        {
            //取得授課ID
            string lesson_code = (request.TryGetProperty("lesson_id", out JsonElement lesson_id) && !string.IsNullOrWhiteSpace(Convert.ToString(lesson_id))) ? Convert.ToString(lesson_id) : _snowflakeId.NextId().ToString();
            bool get_lesson_id = (string.IsNullOrWhiteSpace(Convert.ToString(lesson_id)) || Convert.ToString(lesson_id) != lesson_code) ? false : true;
            //取得醍摩豆ID
            string teacherId = string.Empty;
            string id_token = HttpContext.GetXAuth("IdToken");
            if (!string.IsNullOrWhiteSpace(id_token))
            {
                var jwt = new JwtSecurityToken(id_token);
                if (!jwt.Payload.Iss.Equals("account.teammodel", StringComparison.OrdinalIgnoreCase)) return BadRequest();
                teacherId = jwt.Payload.Sub;
            }
            if (string.IsNullOrWhiteSpace(teacherId) && get_lesson_id) return BadRequest(); //有授課ID無醍摩豆ID，BadRequest
            //DB操作
            if (!string.IsNullOrWhiteSpace(teacherId))
            {
                string tableName = "TeacherLesson";
                CloudTable table = _azureStorage.GetCloudTableClient().GetTableReference(tableName);
                Lesson lessonEntity = new Lesson(teacherId, lesson_code);
                TableOperation operation = TableOperation.InsertOrReplace(lessonEntity);
                TableResult result = await table.ExecuteAsync(operation);
            }

            return Ok(new { lesson_code });
        }

        [ProducesDefaultResponseType]
        [HttpPost("upd-exam-result")]
        public async Task<IActionResult> UploadExamResult(JsonElement request)
        {
            try
            {
                string message = "";
                int error = 0;

                string id_token = HttpContext.GetXAuth("IdToken");
                if (string.IsNullOrWhiteSpace(id_token)) return BadRequest();
                var jwt = new JwtSecurityToken(id_token);
                if (!jwt.Payload.Iss.Equals("account.teammodel", StringComparison.Ordinal)) return BadRequest();
                var id = jwt.Payload.Sub;

                if (!request.TryGetProperty("exam", out JsonElement exam)) return BadRequest();
                if (!request.TryGetProperty("examClassResult", out JsonElement examClassResult)) return BadRequest();

                ExamInfo dbExamInfo = exam.ToObject<ExamInfo>();
                List<ExamClassResultStudentAnswerArray> dbExamClassResultList = examClassResult.ToObject<List<ExamClassResultStudentAnswerArray>>();

                //ExamInfo內容取得、調整
                string blobContainer = (dbExamInfo.range == "school" && dbExamInfo.scope == "school" && dbExamInfo.school != null) ? dbExamInfo.school : id; //blob容器
                dbExamInfo.source = "1"; //評測來源固定為  1:課中評量 
                //UPDATE
                var examResponse = _azureCosmos.GetCosmosClient().GetContainer("TEAMModelOS", "Common").UpsertItemAsync(dbExamInfo, new PartitionKey(dbExamInfo.code)).GetAwaiter().GetResult();
                
                //ExamClassResult內容調整
                foreach (ExamClassResultStudentAnswerArray examClassResultRow in dbExamClassResultList)
                {
                    ExamClassResult examClassResultUpd = new ExamClassResult();
                    examClassResultUpd.pk = examClassResultRow.pk;
                    examClassResultUpd.code = examClassResultRow.code;
                    examClassResultUpd.id = examClassResultRow.id;
                    examClassResultUpd.school = examClassResultRow.school;
                    examClassResultUpd.examId = examClassResultRow.examId;
                    examClassResultUpd.subjectId = examClassResultRow.subjectId;
                    examClassResultUpd.gradeId = examClassResultRow.gradeId;
                    examClassResultUpd.year = examClassResultRow.year;
                    examClassResultUpd.info = examClassResultRow.info;
                    examClassResultUpd.progress = examClassResultRow.progress;
                    examClassResultUpd.studentIds = examClassResultRow.studentIds;
                    examClassResultUpd.studentAnswers = new List<List<string>>();
                    examClassResultUpd.studentScores = examClassResultRow.studentScores;
                    examClassResultUpd.scope = examClassResultRow.scope;
                    examClassResultUpd.sum = examClassResultRow.sum;

                    string examId = examClassResultRow.examId;
                    string subjectId = examClassResultRow.subjectId;
                    //examClassResult.studentAnswers 將學生答案上傳blob後轉換內容為blob路徑
                    if (examClassResultRow.studentIds != null && examClassResultRow.studentIds.Count > 0 && examClassResultRow.studentAnswersArray != null && examClassResultRow.studentAnswersArray.Count > 0)
                    {
                        for (int i = 0; i < examClassResultRow.studentAnswersArray.Count; i++)
                        {
                            string studentId = examClassResultRow.studentIds[i];
                            string fileName = examId + "/" + subjectId + "/" + studentId;
                            string blob = fileName + "/" + "ans.json";
                            await _azureStorage.UploadFileByContainer(blobContainer, examClassResultRow.studentAnswersArray[i].ToJsonString(), "exam", blob, false);
                            List<string> studenrAnswerRow = new List<string>();
                            studenrAnswerRow.Add(blob);
                            examClassResultUpd.studentAnswers.Add(studenrAnswerRow);
                        }
                    }
                    //UPDATE
                    var examClassResultResponse = await _azureCosmos.GetCosmosClient().GetContainer("TEAMModelOS", "Common").UpsertItemAsync(examClassResultUpd, new PartitionKey(examClassResultUpd.code));
                }

                return Ok(new { error, message });
            }
            catch (Exception ex)
            {
                return BadRequest();
            }
        }



        //課綱的model先記在下面，待式樣確定後再轉換
        private List<SyllabusNode> CreateSyllabusTree(List<Syllabus> syllabuses)
        {
            List<SyllabusNode> nodes = new List<SyllabusNode>();

            foreach (var syllabus in syllabuses)
            {
                if (syllabus.pid == "")
                    nodes.Add(new SyllabusNode { id = syllabus.id, name = syllabus.name });
                else
                {
                    CreateNode(nodes, syllabus);
                }
            }
            return nodes;
        }

        private void CreateNode(List<SyllabusNode> nodes, Syllabus parent)
        {
            foreach (var node in nodes)
            {
                if (node.id == parent.pid)
                {
                    node.children.Add(new SyllabusNode { id = parent.id, name = parent.name });
                }
                else
                {
                    CreateNode(node.children, parent);
                }
            }
        }

        public class SyllabusRole
        {
            public string id { get; set; }
            public string name { get; set; }
            public object period { get; set; }
            public object semester { get; set; }
            public object grade { get; set; }
            public object subject { get; set; }
            public int resourceCount { get; set; }
            public int itemCount { get; set; }
            public object structure { get; set; }
        }

        public class Syllabus
        {
            public string id { get; set; }
            public string name { get; set; }
            public string pid { get; set; }
            public int order { get; set; }
        }

        public class SyllabusNode
        {
            public string id { get; set; }
            public string name { get; set; }
            public List<SyllabusNode> children { get; set; }

            public SyllabusNode()
            {
                children = new List<SyllabusNode>();
            }
        }

        public class ExamClassResultStudentAnswerArray : ExamClassResult
        {
            public List<List<List<string>>> studentAnswersArray { get; set; }
        }

    }
}