TEAMModelOS/TEAMModelOS/Controllers/Teacher/InitController.cs

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

namespace TEAMModelOS.Controllers
{
    [ProducesResponseType(StatusCodes.Status200OK)]
    [ProducesResponseType(StatusCodes.Status400BadRequest)]
    //[Authorize(Roles = "IES5")]
    [Route("teacher/init")]
    [ApiController]
    public class InitController : ControllerBase
    {
        private readonly AzureCosmosFactory _azureCosmos;
        private readonly AzureStorageFactory _azureStorage;
        private readonly DingDing _dingDing;
        private readonly Option _option;
        private readonly IConfiguration _configuration; 
        private readonly NotificationService _notificationService;
        public InitController(AzureCosmosFactory azureCosmos, AzureStorageFactory azureStorage, DingDing dingDing, IOptionsSnapshot<Option> option, IConfiguration configuration, NotificationService notificationService)
        {
            _azureCosmos = azureCosmos;
            _azureStorage = azureStorage;
            _dingDing = dingDing;
            _option = option?.Value;
            _configuration = configuration;
            _notificationService = notificationService;
        }

        /// <summary>
        /// 修改教师信息
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [HttpPost("set-teacher-info")]
        [AuthToken(Roles = "admin,teacher,student")]

        public async Task<IActionResult> SetTeacherInfo(JsonElement request) {
            var client = _azureCosmos.GetCosmosClient();
            if (!request.TryGetProperty("defaultSchool", out JsonElement _defaultSchool))
            {
                return BadRequest();
            }
            if (!request.TryGetProperty("opt", out JsonElement _opt))
            {
                return BadRequest();
            }
            var (userid, _, _, _) = HttpContext.GetAuthTokenInfo();
            try { 
                Teacher teacher = await client.GetContainer(Constant.TEAMModelOS, "Teacher").ReadItemAsync<Teacher>(userid, new PartitionKey("Base"));
                switch (true) {
                    //修改默认学校
                    case bool when  $"{_opt}".Equals("UpdateDefaultSchool", StringComparison.OrdinalIgnoreCase) && !string.IsNullOrEmpty($"{_defaultSchool}"):
                        if (teacher.schools.Select(x => x.schoolId).Contains($"{_defaultSchool}"))
                        {
                            teacher.defaultSchool = $"{_defaultSchool}";
                            await client.GetContainer(Constant.TEAMModelOS, "Teacher").ReplaceItemAsync<Teacher>(teacher, userid, new PartitionKey("Base"));
                            return Ok(new { teacher, status = 1 });
                        }
                        else {
                            return BadRequest(new { status=-1,msg="您未加入该学校！"});
                        }
                        
                }
            }
            catch(Exception ex)
            {
                return BadRequest("teacher not exist");

            }
            return Ok();
        
        }
        //TODO 此API需處理對應前端返回的相關數據
        [ProducesDefaultResponseType]
        [HttpPost("get-teacher-info")]
        public async Task<IActionResult> GetTeacherInfo(JsonElement request)
        {
            try
            {
                if (!request.TryGetProperty("id_token", out JsonElement id_token)) return BadRequest();
                var jwt = new JwtSecurityToken(id_token.GetString());
                //TODO 此驗證IdToken先簡單檢查，後面需向Core ID新API，驗證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;
                //TODO 取得Teacher 個人相關數據(課程清單、虛擬教室清單、歷史紀錄清單等)，學校數據另外API處理，多校切換時不同
                var client = _azureCosmos.GetCosmosClient();
                int total = 0;
                int tsize = 0;
                try {
                    Teacher teacher = await client.GetContainer(Constant.TEAMModelOS, "Teacher").ReadItemAsync<Teacher>(id, new PartitionKey("Base"));
                    teacher.name = $"{name}";
                    teacher.picture = $"{picture}";
                    ///教师的个人空间
                    tsize = teacher.size;
                    ///教师的总空间 包含 个人空间和学校赠送的空间累加
                    total = teacher.size;
                    //检查是否有加入学校，如果加入学校，则当个人空间size是0G的时候，则免费获得一个G空间，但无论加入多少个学校，只能获取一次 1G的免费空间。没有加入学校则默认0G空间,除非自己购买空间
                  
                    if (teacher.schools.IsNotEmpty()) {
                        foreach (var sc in teacher.schools) {
                            string statusNow = sc.status!=null?sc.status:"";
                            if (statusNow .Equals("join") || statusNow .Equals("invite") || statusNow.Equals("request")) {
                                dynamic schoolExtobj = new ExpandoObject();
                                var schoolJson = await client.GetContainer(Constant.TEAMModelOS, "School").ReadItemStreamAsync($"{sc.schoolId}", new PartitionKey("Base"));
                                var school = await JsonDocument.ParseAsync(schoolJson.ContentStream);
                                schoolExtobj.schoolId = sc.schoolId;
                                schoolExtobj.name = school.RootElement.GetProperty("name") ;
                                schoolExtobj.status =sc.status;
                                schoolExtobj.time = sc.time;
                                schoolExtobj.picture = school.RootElement.GetProperty("picture");
                                
                                var sctch = await client.GetContainer(Constant.TEAMModelOS, "School").ReadItemStreamAsync(id, new PartitionKey($"Teacher-{sc.schoolId}"));
                                if (sctch.Status == 200)
                                {
                                    var jsonDoc = await JsonDocument.ParseAsync(sctch.ContentStream);
                                    SchoolTeacher schoolTeacher = jsonDoc.RootElement.ToObject<SchoolTeacher>();
                                    schoolTeacher.name = $"{name}";
                                    schoolTeacher.picture = $"{picture}";
                                    await  client.GetContainer(Constant.TEAMModelOS, "School").ReplaceItemAsync(schoolTeacher, id, new PartitionKey($"Teacher-{sc.schoolId}"));
                                    if (jsonDoc.RootElement.TryGetProperty("size", out JsonElement _size) && _size.ValueKind.Equals(JsonValueKind.Number))
                                    {
                                        total += _size.GetInt32();
                                        schoolExtobj.size = _size.GetInt32();
                                    }
                                    else { schoolExtobj.size = 0; }
                                }
                                else {
                                    schoolExtobj.size=0;
                                }
                                schools.Add(schoolExtobj);
                            }
                        }
                        //如果包含任何申请，邀请，加入学校的记录 且个人空间未分配2G  则默认分配个人空间至2G.
                        if ( teacher.size<2 && teacher.schools.Count > 0)
                        {
                            teacher.size = 2;
                        }
                        //如果未包含任何申请，邀请，加入学校的记录 且 个人空间没有分配1G 则默认赠送一个G 
                        if (teacher.schools.Count == 0 && teacher.size<1) {
                            teacher.size = 1;
                        }
                    }
                    await client.GetContainer(Constant.TEAMModelOS, "Teacher").ReplaceItemAsync<Teacher>(teacher, id, new PartitionKey("Base"));
                    //預設學校ID
                    defaultschool = teacher.defaultSchool;
                } catch(CosmosException ex) 
                { 
                    if (ex.Status == 404) {
                        //如果沒有，則初始化Teacher基本資料到Cosmos
                        Teacher teacher = new Teacher
                        {
                            id = id, 
                            pk = "Base",
                            code = "Base", 
                            name = name?.ToString(),
                            picture = picture?.ToString(),
                            //创建账号并第一次登录IES5则默认赠送1G
                            size = 1,
                            defaultSchool = null,
                            schools = new List<Teacher.School>(),
                        };
                        var container = _azureStorage.GetBlobContainerClient(id);
                        await container.CreateIfNotExistsAsync(PublicAccessType.None); //嘗試創建Teacher私有容器，如存在則不做任何事，保障容器一定存在
                        teacher = await _azureCosmos.GetCosmosClient().GetContainer(Constant.TEAMModelOS, "Teacher").CreateItemAsync<Teacher>(teacher, new PartitionKey("Base"));
                        total = teacher.size;
                        tsize = teacher.size;
                    } 
                }
                //私人課程
                List<object> courses = new List<object>();
                await foreach (var item in client.GetContainer(Constant.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>());
                        }
                    }
                }

                //老師個人課綱  TODO 暂时不取
                List<SyllabusRole> syllabus = new List<SyllabusRole>();
                //await foreach (var item in client.GetContainer(Constant.TEAMModelOS, "Teacher").GetItemQueryStreamIterator(queryText: $"SELECT c.id, c.name, c.scope, c.resourceCount, c.itemCount, c.children from c ", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Syllabus-{id}") }))
                //{
                //    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.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);
                //    }
                //}

                //換取AuthToken，提供給前端
                var auth_token = JwtAuthExtension.CreateAuthToken(_option.HostName, id, name?.ToString(), picture?.ToString(), _option.JwtSecretKey, roles: new[] { "teacher" });
                //取得Teacher Blob 容器位置及SAS 
                var (blob_uri, blob_sas) = _azureStorage.GetBlobContainerSAS(id, BlobContainerSasPermissions.Write | BlobContainerSasPermissions.Read | BlobContainerSasPermissions.List | BlobContainerSasPermissions.Delete);
                return Ok(new { auth_token, blob_uri, blob_sas, schools, defaultschool, courses, total, tsize });

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

        //TODO 此API需處理對應前端返回的相關數據
        [ProducesDefaultResponseType]
        [HttpPost("get-school-info")]        
        public async Task<IActionResult> GetSchoolInfo(JsonElement requert)
        {
            try
            {
                if (!requert.TryGetProperty("id_token", out JsonElement id_token)) return BadRequest();
                if (!requert.TryGetProperty("school_code", out JsonElement school_code)) return BadRequest();
                var jwt = new JwtSecurityToken(id_token.GetString());
                if (!jwt.Payload.Iss.Equals("account.teammodel", StringComparison.Ordinal)) return BadRequest();
                var id = jwt.Payload.Sub;

                var client = _azureCosmos.GetCosmosClient();

                //權限token
                jwt.Payload.TryGetValue("name", out object name);
                jwt.Payload.TryGetValue("picture", out object picture);
                List<string> roles = new List<string>();
                List<string> permissions = new List<string>();
                int size = 0;
                var response = await client.GetContainer(Constant.TEAMModelOS, "School").ReadItemStreamAsync(id, new PartitionKey($"Teacher-{school_code}"));
                if (response.Status == 200)
                {
                    using var json = await JsonDocument.ParseAsync(response.ContentStream);
                    if (json.RootElement.TryGetProperty("size", out JsonElement _size) && _size.ValueKind == JsonValueKind.Number)
                    {
                        size = _size.GetInt32();
                    }
                    if (json.RootElement.TryGetProperty("roles", out JsonElement _roles) && _roles.ValueKind != JsonValueKind.Null)
                    {
                        foreach (var obj in _roles.EnumerateArray())
                        {
                            roles.Add(obj.GetString());
                        }
                    }
                    if (json.RootElement.TryGetProperty("permissions", out JsonElement _permissions) && _permissions.ValueKind != JsonValueKind.Null)
                    {
                        foreach (var obj in _permissions.EnumerateArray())
                        {
                            permissions.Add(obj.GetString());
                        }
                    }
                }
                else //無此學校資料
                {
                    return Ok(new { status=404});
                }
                if (roles.Count == 0)
                {
                    roles.Add("teacher");
                    roles.Add("student");
                }
                //TODO JJ，更新Token时，在取得学校资讯时，没有传入schoolId
                var auth_token = JwtAuthExtension.CreateAuthToken(_option.HostName, id, name?.ToString(), picture?.ToString(), _option.JwtSecretKey, schoolID: school_code.ToString(), roles: roles.ToArray(), permissions: permissions.ToArray());

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

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

                //該老師排定的學校課程
                List<object> school_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(Constant.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();
                            //課程
                            obj.TryGetProperty("course", out JsonElement courseNow);
                            string courseIdNow = courseNow.GetProperty("id").ToString();
                            var courseExist = school_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.classes.Add(classExtobj);
                                school_courses.Add(courseExtobj);
                            }
                            else
                            {
                                courseExist.classes.Add(classExtobj);
                            }
                        }
                    }
                }

                //校本課綱 [式樣未定 先不取]
                List<SyllabusRole> school_syllabus = new List<SyllabusRole>();
                //await foreach (var item in client.GetContainer(Constant.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);
                //    }
                //}

                //取得School Blob 容器位置及SAS
                string school_code_blob = school_code.GetString().ToLower();
                var container = _azureStorage.GetBlobContainerClient(school_code_blob);
                await container.CreateIfNotExistsAsync(PublicAccessType.None); //嘗試創建School容器，如存在則不做任何事，保障容器一定存在
                var (blob_uri, blob_sas) = (roles.Contains("admin") || permissions.Contains("schoolAc-upd")) ? _azureStorage.GetBlobContainerSAS(school_code_blob, BlobContainerSasPermissions.Write | BlobContainerSasPermissions.Read | BlobContainerSasPermissions.List | BlobContainerSasPermissions.Delete) : _azureStorage.GetBlobContainerSAS(school_code_blob, BlobContainerSasPermissions.Read | BlobContainerSasPermissions.List | BlobContainerSasPermissions.Write);

                return Ok(new { auth_token, blob_uri, blob_sas, school_base, school_courses, school_syllabus, school_classes, school_rooms, size });
            
            } catch (Exception ex) {
                await _dingDing.SendBotMsg($"IES5，{_option.Location}，Teacher/init/get-school-info()\n{ex.Message}{ex.StackTrace}", GroupNames.醍摩豆服務運維群組);
                return BadRequest();
            }
        }

        /// <summary>
        /// 取得學校所有列表
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        //[AuthToken(Roles = "teacher")]
        [HttpPost("get-school-list")]
        public async Task<IActionResult> GetSchoolList(JsonElement request)
        {
            try
            {
                //輸入值
                string sqlSchoolId = (request.TryGetProperty("schoolId", out JsonElement reqSchoolId)) ? reqSchoolId.GetString() : string.Empty;
                bool sqlHasManager = (request.TryGetProperty("manager", out JsonElement reqHasManager)) ? reqHasManager.GetBoolean() : false;
                bool sqlHasMBaseModule = (request.TryGetProperty("base", out JsonElement reqHasBaseModule)) ? reqHasBaseModule.GetBoolean() : false;

                var client = _azureCosmos.GetCosmosClient();
                //有管理者的學校
                Dictionary<string, List<Dictionary<string, string>>> managerSchDic = new Dictionary<string, List<Dictionary<string, string>>>();
                string managerWhereOption = (!string.IsNullOrWhiteSpace(sqlSchoolId)) ? $" AND c.code = 'Teacher-{sqlSchoolId}'" : string.Empty;
                string managerSql = $"SELECT DISTINCT REPLACE(c.code, 'Teacher-', '') AS schoolId, c.id, c.name FROM c WHERE ARRAY_CONTAINS(c.roles, 'admin', true) AND c.pk = 'Teacher' AND c.status = 'join'{managerWhereOption}";
                await foreach (var item in client.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryStreamIterator(queryText: managerSql, requestOptions: new QueryRequestOptions() {} ))
                {
                    using var json = await JsonDocument.ParseAsync(item.ContentStream);
                    foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                    {
                        string id =  obj.GetProperty("id").GetString(); //管理者ID
                        string name = obj.GetProperty("name").GetString(); //管理者姓名
                        string schoolId = obj.GetProperty("schoolId").GetString(); //學校ID
                        Dictionary<string, string> managerDic = new Dictionary<string, string>();
                        managerDic.Add("id", id);
                        managerDic.Add("name", name);
                        if (managerSchDic.ContainsKey(schoolId))
                        {
                            managerSchDic[schoolId].Add(managerDic);
                        }
                        else
                        {
                            List<Dictionary<string, string>> managerList = new List<Dictionary<string, string>>();
                            managerList.Add(managerDic);
                            managerSchDic.Add(schoolId, managerList);
                        }
                    }
                }
                //有管理模組的學校
                List<string> baseModuleSchList = new List<string>();
                string baseModuleWhereOption = (!string.IsNullOrWhiteSpace(sqlSchoolId)) ? $" AND c.id = '{sqlSchoolId}'" : string.Empty;
                await foreach (var item in client.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryStreamIterator(queryText: $"SELECT c.id FROM c JOIN serviceProduct IN c.service.product WHERE serviceProduct.prodCode = 'IPDYZYLC'{baseModuleWhereOption}", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Product") }))
                {
                    using var json = await JsonDocument.ParseAsync(item.ContentStream);
                    foreach (var obj in json.RootElement.GetProperty("Documents").EnumerateArray())
                    {
                        string schoolId = obj.GetProperty("id").GetString(); //學校ID
                        baseModuleSchList.Add(schoolId);
                    }
                }

                //學校資料
                List<object> schools = new List<object>();
                string schoolWhereOption = (!string.IsNullOrWhiteSpace(sqlSchoolId)) ? $" WHERE c.id = '{sqlSchoolId}'" : string.Empty;
                await foreach (var item in client.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryStreamIterator(queryText: $"SELECT c.id, c.name, c.region, c.province, c.city, c.picture FROM c {schoolWhereOption}", requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Base") }))
                {
                    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())
                        {
                            dynamic schoolExtobj = new ExpandoObject();
                            schoolExtobj.id = obj.GetProperty("id").GetString(); //學校ID
                            schoolExtobj.name = obj.GetProperty("name");
                            schoolExtobj.region = obj.GetProperty("region");
                            schoolExtobj.province = obj.GetProperty("province");
                            schoolExtobj.city = obj.GetProperty("city");
                            schoolExtobj.picture = obj.GetProperty("picture");
                            if (managerSchDic.ContainsKey(schoolExtobj.id))
                            {
                                schoolExtobj.hasManager = true;
                                schoolExtobj.managers = managerSchDic[schoolExtobj.id];
                            }
                            else
                            {
                                schoolExtobj.hasManager = false;
                                schoolExtobj.managers = new List<object>();
                            }
                            schoolExtobj.hasBaseModule = (baseModuleSchList.Contains(schoolExtobj.id)) ? true : false;
                            //學校輸出結果加入篩選
                            if( (sqlHasManager && sqlHasMBaseModule) && (schoolExtobj.hasManager && schoolExtobj.hasBaseModule) )
                            {
                                schools.Add(schoolExtobj);
                            }
                            else if(sqlHasManager && schoolExtobj.hasManager)
                            {
                                schools.Add(schoolExtobj);
                            }
                            else if(sqlHasMBaseModule && schoolExtobj.hasBaseModule)
                            {
                                schools.Add(schoolExtobj);
                            }
                            else if(!sqlHasManager && !sqlHasMBaseModule)
                            {
                                schools.Add(schoolExtobj);
                            }
                        }
                    }
                }

                return Ok(new { schools });
            }
            catch (Exception ex)
            {
                return BadRequest();
            }
        }

        /// <summary>
        /// 申請或同意邀請加入學校
        /// </summary>
        /// 
        /// <param name="requert"></param>
        /// <returns></returns>
        [ProducesDefaultResponseType]
        [AuthToken(Roles = "teacher")]
        [HttpPost("join-school")]
        public async Task<IActionResult> JoinSchool(JsonElement requert)
        {
            try
            {
                if (!requert.TryGetProperty("grant_type", out JsonElement grant_type)) return BadRequest();  //"invite":學校邀請 "request":老師申請 "join":"成為學校老師"，leave 离开，cancel 取消。
                if (!requert.TryGetProperty("school_code", out JsonElement school_code)) return BadRequest();
                if (!requert.TryGetProperty("school_name", out JsonElement school_name)) return BadRequest();
                ///当邀请某个老师加入学校则需要知道是谁邀请的
                //if (!requert.TryGetProperty("school_admin", out JsonElement school_admin)) return BadRequest();
                string authtoken = HttpContext.GetXAuth("AuthToken");
                if (string.IsNullOrEmpty(authtoken)) return BadRequest();
                var jwt = new JwtSecurityToken(authtoken);
                var id = jwt.Payload.Sub;
                var schoolcode = jwt.Payload.Azp;
                var Claims = jwt.Payload.Claims;
                jwt.Payload.TryGetValue("name", out object name);
                jwt.Payload.TryGetValue("picture", out object picture);
                var client = _azureCosmos.GetCosmosClient();
                //在老師表找出老師，處理該學校狀態 (老師基本資料應該要存在)
                Teacher teacher = await client.GetContainer(Constant.TEAMModelOS, "Teacher").ReadItemAsync<Teacher>(id, new PartitionKey("Base"));
                if (teacher.schools == null)
                    teacher.schools = new List<Teacher.School>();
                var school = teacher.schools?.FirstOrDefault(x => x.schoolId.Equals(school_code.GetString(), StringComparison.OrdinalIgnoreCase));
                if (school != null)
                    school.status = grant_type.GetString();
                else
                {
                    long now = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds();
                    teacher.schools.Add(new Teacher.School() { schoolId = school_code.GetString(), name = school_name.GetString(), status = grant_type.GetString() ,time= now });
                }
                if (grant_type.GetString() .Equals("leave") || grant_type.GetString() .Equals("cancel"))
                {
                    if (teacher.schools.IsNotEmpty())
                    {
                        //获取之前已经加入的学校或者申请的学校
                        var inSchools = teacher.schools.Where(x => x.schoolId == school_code.GetString()).ToList();
                        if (inSchools.IsNotEmpty())
                        {
                            inSchools.ForEach(x => teacher.schools.Remove(x));
                        }
                    }
                    
                    if (teacher.schools.Count > 0 && teacher.size <= 1)
                    {
                        teacher.size = 2;
                    }
                    await client.GetContainer(Constant.TEAMModelOS, "Teacher").ReplaceItemAsync<Teacher>(teacher, id, new PartitionKey("Base"));
                    var sresponse = await client.GetContainer(Constant.TEAMModelOS, "School").DeleteItemStreamAsync(id, new PartitionKey($"Teacher-{school_code}"));
                    return Ok(new { stauts = 1 });
                }
                else {
                    var dft = new List<string>() { "content-read", "exercise-read", "knowledge-read", "syllabus-read" };
                    if (teacher.schools.Count > 0 && teacher.size <= 1)
                    {
                        teacher.size = 2;
                    }
                    await client.GetContainer(Constant.TEAMModelOS, "Teacher").ReplaceItemAsync<Teacher>(teacher, id, new PartitionKey("Base"));
                    //在學校表處理該學校教師帳號的狀態
                    var sresponse = await client.GetContainer(Constant.TEAMModelOS, "School").ReadItemStreamAsync(id, new PartitionKey($"Teacher-{school_code}"));
                    if (sresponse.Status == 200)
                    {
                        using var json = await JsonDocument.ParseAsync(sresponse.ContentStream);
                        SchoolTeacher steacher = json.ToObject<SchoolTeacher>();
                        steacher.status = grant_type.GetString();
                        if (grant_type.ToString().Equals("join")) {
                            if (steacher.permissions.IsNotEmpty())
                            {
                                foreach (var d in dft) {
                                    if (!steacher.permissions.Contains(d))
                                    {
                                        steacher.permissions.Add(d);
                                    }
                                }
                            }
                            else {
                                steacher.permissions = dft;
                            }
                        }
                        var response = await client.GetContainer(Constant.TEAMModelOS, "School").ReplaceItemAsync(steacher, id, new PartitionKey($"Teacher-{school_code}"));
                    }
                    else
                    {
                        SchoolTeacher st = new SchoolTeacher()
                        {
                            pk = "Teacher",
                            code = $"Teacher-{school_code}",
                            createTime = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds(),
                            id = id,
                            name = name.ToString(),
                            picture = picture?.ToString(),
                            permissions = grant_type.ToString().Equals("join")? dft : null,
                            roles = new List<string>() { "teacher" },
                            size = 0,
                            status = grant_type.GetString()
                        };
                        var response = await client.GetContainer(Constant.TEAMModelOS, "School").CreateItemAsync(st, new PartitionKey($"Teacher-{school_code}"));
                    }
                    Notification notification = null;
                    
                    List<SchoolTeacher> teachers = new List<SchoolTeacher>();
                    var queryslt = $"SELECT  value(c)  FROM c join A1 in c.roles   where  A1 in ('admin')";
                    await foreach (var item in client.GetContainer(Constant.TEAMModelOS, "School").GetItemQueryIterator<SchoolTeacher>(queryText: queryslt, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey($"Teacher-{school_code}") }))
                    {
                        teachers.Add(item);
                    }
                    if (teachers.IsNotEmpty()) {
                        string code = grant_type.GetString();
                        if (grant_type.GetString() .Equals("join")) {
                            code = "invite-join";
                        }
                        notification = new Notification
                        {
                            hubName = "hita",
                            type = "msg",
                            from = $"ies5:private",
                            to = teachers.Select(x => x.id).ToList(),
                            label = $"{code}_school",
                            body = new {biz= code, tmdid= id ,tmdname= name.ToString(), schoolcode=$"{school_code}", schoolname=$"{school_name}", status =1,time= DateTimeOffset.UtcNow .ToUnixTimeMilliseconds()}.ToJsonString(),
                            expires= DateTimeOffset.UtcNow.AddDays(7).ToUnixTimeSeconds()
                        };
                    }
                    
                    if (notification != null) {
                        var url= _configuration.GetValue<string>("HaBookAuth:CoreService:sendnotification");
                        var clientID= _configuration.GetValue<string>("HaBookAuth:CoreService:clientID");
                        var clientSecret = _configuration.GetValue<string>("HaBookAuth:CoreService:clientSecret");
                        var location = _option.Location;
                        var code = await _notificationService .SendNotification(clientID, clientSecret, location, url, notification);
                    }
                    return Ok(new { stauts = 1 });
                }
            }
            catch (Exception ex)
            {
                await _dingDing.SendBotMsg($"TEAMModel，{_option.Location}，Init/JoinSchool()\n{ex.Message}{ex.StackTrace}", GroupNames.醍摩豆服務運維群組);
                return BadRequest();
            }
        }


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

            foreach (var syllabus in syllabuses)
            {
                if (syllabus.pid .Equals(""))
                    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>();
            }
        }
    }
}