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.classes, c.notice ,c.scope from c ", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Course-{id}") }))
                {
                    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>());
                        }
                    }
                }

                //取得老師個人評測 (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();
                }

                //該老師排定的學校課程
                Dictionary<string, int> classStuDic = new Dictionary<string, int>(); //所有班級學生數
                var querycst = $"SELECT (IS_ARRAY(c.students)) ? ARRAY_LENGTH(c.students) : 0 as stuCnt, c.id FROM c";
                await foreach (var item in client.GetContainer("TEAMModelOS", "School").GetItemQueryStreamIterator(queryText: querycst, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Class-{school_code}") }))
                {
                    var jsoncst = await JsonDocument.ParseAsync(item.ContentStream);
                    foreach (var obj in jsoncst.RootElement.GetProperty("Documents").EnumerateArray())
                    {
                        string classId = obj.GetProperty("id").GetString();
                        int stuCnt = obj.GetProperty("stuCnt").GetInt32();
                        classStuDic.Add(classId, stuCnt);
                    }
                }
                Dictionary<string, int> classGrpDic = new Dictionary<string, int>(); //所有班級分組數
                var querycgp = $"SELECT Count(1) as grpCnt, c.id FROM c JOIN(SELECT DISTINCT t.groupId FROM t IN c.students WHERE t.groupId != null) GROUP BY c.id";
                await foreach (var item in client.GetContainer("TEAMModelOS", "School").GetItemQueryStreamIterator(queryText: querycgp, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Class-{school_code}") }))
                {
                    var jsoncgp = await JsonDocument.ParseAsync(item.ContentStream);
                    foreach (var obj in jsoncgp.RootElement.GetProperty("Documents").EnumerateArray())
                    {
                        string classId = obj.GetProperty("id").GetString();
                        int grpCnt = obj.GetProperty("grpCnt").GetInt32();
                        classGrpDic.Add(classId, grpCnt);
                    }
                }

                Dictionary<string, object> schoolCoursesDic = new Dictionary<string, object>(); //課程與科目對應表
                var querysc = $"SELECT c.id, c.name, c.subject FROM c";
                await foreach (var item in client.GetContainer("TEAMModelOS", "School").GetItemQueryStreamIterator(queryText: querysc, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Course-{school_code}") }))
                {
                    var jsonsc = await JsonDocument.ParseAsync(item.ContentStream);
                    if (jsonsc.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                    {
                        foreach (var obj in jsonsc.RootElement.GetProperty("Documents").EnumerateArray())
                        {
                            string courseIdNow = obj.GetProperty("id").ToString();
                            object subjectNow = obj.GetProperty("subject");
                            schoolCoursesDic.Add(courseIdNow, subjectNow);
                        }
                    }
                }

                List<object> courses = new List<object>(); //老師被安排的課程列表
                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($"CourseManagement-{school_code}") }))
                {
                    var jsoncm = await JsonDocument.ParseAsync(item.ContentStream);
                    if (jsoncm.RootElement.TryGetProperty("_count", out JsonElement count) && count.GetUInt16() > 0)
                    {
                        foreach (var obj in jsoncm.RootElement.GetProperty("Documents").EnumerateArray())
                        {
                            //班級
                            string classIdNow = obj.GetProperty("id").ToString();
                            dynamic classExtobj = new ExpandoObject();
                            classExtobj.code = $"Class-{school_code}";
                            classExtobj.id = classIdNow;
                            classExtobj.name = obj.GetProperty("name").ToString();
                            classExtobj.teacher = obj.GetProperty("teacher").ToObject<object>();
                            classExtobj.scope = obj.GetProperty("scope").ToString();
                            classExtobj.stuCnt = (classStuDic.ContainsKey(classIdNow)) ? classStuDic[classIdNow] : 0;
                            classExtobj.grpCnt = (classGrpDic.ContainsKey(classIdNow)) ? classGrpDic[classIdNow] : 0;
                            //課程
                            obj.TryGetProperty("course", out JsonElement courseNow);
                            string courseIdNow = courseNow.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 = courseNow.GetProperty("name").ToString();
                                courseExtobj.scope = courseNow.GetProperty("scope").ToString();
                                courseExtobj.classes = new List<object>();
                                courseExtobj.subject = schoolCoursesDic[courseIdNow];
                                courseExtobj.classes.Add(classExtobj);
                                courses.Add(courseExtobj);
                            }
                            else
                            {
                                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 requert)
        {
            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 (!requert.TryGetProperty("grant_type", out JsonElement grant_type)) return BadRequest();           
            if (!requert.TryGetProperty("class_code", out JsonElement class_code)) return BadRequest();
            requert.TryGetProperty("school_code", out JsonElement school_code);
            if (grant_type.GetString() == "school" && string.IsNullOrEmpty(school_code.GetString())) return BadRequest();

            var client = _azureCosmos.GetCosmosClient();
            List<object> students = new List<object>();
            var query = $"SELECT c.students FROM c WHERE c.id = '{class_code}' AND c.scope = '{grant_type}'";
            string pk = (grant_type.GetString() == "school") ? $"Class-{school_code}" : $"Class-{id}";
            string container = (grant_type.GetString() == "school") ? "School" : "Teacher";
            await foreach (var item in client.GetContainer("TEAMModelOS", container).GetItemQueryStreamIterator(queryText: query, 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 classObj in json.RootElement.GetProperty("Documents").EnumerateArray())
                    {
                        foreach (var studentObj in classObj.GetProperty("students").EnumerateArray())
                        {
                            dynamic studentsExtobj = new ExpandoObject();
                            studentsExtobj.id = studentObj.GetProperty("id");
                            studentsExtobj.name = studentObj.GetProperty("name");
                            studentsExtobj.no = studentObj.GetProperty("no");
                            students.Add(studentsExtobj);
                        }
                    }
                }
                else
                {
                    return Ok(new { error = 1, message = "No class found!" });
                }
            }

            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("examResult", out JsonElement examResult)) return BadRequest();
                if (!request.TryGetProperty("examClassResult", out JsonElement examClassResult)) return BadRequest();

                ExamInfo dbExamInfo = exam.ToObject<ExamInfo>();
                //ExamResult dbExamResult = examResult.ToObject<ExamResult>();
                List<ExamClassResult> dbExamClassResultList = examClassResult.ToObject<List<ExamClassResult>>();

                var examResponse = _azureCosmos.GetCosmosClient().GetContainer("TEAMModelOS", "Common").UpsertItemAsync(dbExamInfo, new PartitionKey(dbExamInfo.code)).GetAwaiter().GetResult();
                //var examResultResponse = await _azureCosmos.GetCosmosClient().GetContainer("TEAMModelOS", "Common").UpsertItemAsync(dbExamResult, new PartitionKey(dbExamResult.code));
                foreach (ExamClassResult examClassResultRow in dbExamClassResultList)
                {
                    var examClassResultResponse = await _azureCosmos.GetCosmosClient().GetContainer("TEAMModelOS", "Common").UpsertItemAsync(examClassResultRow, new PartitionKey(examClassResultRow.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>();
            }
        }

    }
}