using Azure.Cosmos;
using DocumentFormat.OpenXml.Drawing.Charts;
using HTEXLib.COMM.Helpers;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.Json;
using System.Threading.Tasks;
using TEAMModelOS.SDK.DI;
using TEAMModelOS.SDK.Extension;

namespace TEAMModelOS.SDK.Models
{
    public static class OverallEducationService
    {
        public static Dictionary<string, object> GetQueryCond(JsonElement request) {
            Dictionary<string, object> dict = new Dictionary<string, object>();
            if (request.TryGetProperty("periodId", out JsonElement periodId) && !string.IsNullOrWhiteSpace($"{periodId}"))
            {
                dict.Add("periodId", periodId);
            }
            if (request.TryGetProperty("classIds", out JsonElement classIds))
            {
                dict.Add("classId", classIds);
            }
            return dict;
        }

        public async static Task<(List<Class> classes, List<Student> students, List<Class> grade_classes, List<Student> grade_students, List<OverallEducation> overallEducations)> 
            GetGradeData(CosmosClient client, HashSet<int> years,string _periodId,string _school,HashSet<string> classIds,string _studyYear,string _semesterId, AzureRedisFactory _azureRedis) {
            List<Class> classes = new List<Class>();
            List<Student> students = new List<Student>();
            List<Class> grade_classes = new List<Class>();
            List<Student> grade_students = new List<Student>();
            //StringBuilder sql = new StringBuilder($"select value c from  c where c.year in ({string.Join(",", years)}) and c.periodId='{_periodId}' ");
            //if (classIds.Any()  && classIds.Count>0)
            //{
            //    sql.Append($" and c.id in ({string.Join(",", classIds.Select(z => $"'{z}'"))})");
            //}
            //当前条件的班级
          //  var result = await client.GetContainer(Constant.TEAMModelOS, Constant.School).GetList<Class>(sql.ToString(), $"Class-{_school}");
            //classes = result.list;
            //StringBuilder studentCountSQL = new StringBuilder($"select c.id,c.name ,c.picture, c.classId,c.periodId ,c.year from  c  " +
              //  $"where  ( c.graduate = 0 or  IS_DEFINED(c.graduate) = false) and c.classId  in ({string.Join(",", classes.Select(z => $"'{z.id}'"))})   ");
            //当前条件的学生
           // var data = await client.GetContainer(Constant.TEAMModelOS, Constant.Student).GetList<Student>(studentCountSQL.ToString(), $"Base-{_school}");


            //获取年级的所有的班级
            StringBuilder gradeSql = new StringBuilder($"select value c from  c where c.year in ({string.Join(",", years)}) and c.periodId='{_periodId}' ");
            var result_grade_classes = await client.GetContainer(Constant.TEAMModelOS, Constant.School).GetList<Class>(gradeSql.ToString(), $"Class-{_school}");
            grade_classes = result_grade_classes.list;
            //------获取当前条件的班级
            classes= grade_classes.FindAll(z => classIds!=null  &&  classIds.Any() && classIds.Count>0 ? classIds.Contains(z.id):true && z.periodId.Equals(_periodId) && years.Contains(z.year));
            StringBuilder grade_studentCountSQL = new StringBuilder($"select c.id,c.name ,c.picture, c.classId,c.periodId ,c.year from  c " +
                $" where c.classId in ( {string.Join(",", grade_classes.Select(z => $"'{z.id}'"))}) and c.periodId='{_periodId}'  and   ( c.graduate = 0 or  IS_DEFINED(c.graduate) = false) ");
            //获取年级所有学生
            var gradeData = await client.GetContainer(Constant.TEAMModelOS, Constant.Student).GetList<Student>(grade_studentCountSQL.ToString(), $"Base-{_school}");
            grade_students = gradeData.list;
            //------获取当前条件的学生
            students = grade_students.FindAll(z => classes.Select(x => x.id).Contains(z.classId) && z.periodId.Equals(_periodId)); 

            //获取缓存的五育数据
            //1.先从redis获取部分。
            List<OverallEducation> overallEducations = new List<OverallEducation>();
            foreach (var grade_class in grade_classes)
            {
                string key = $"OverallEducation:{_school}:{_school}:{_studyYear}:{_semesterId}:{grade_class.id}";
                var records = await _azureRedis.GetRedisClient(8).HashGetAllAsync(key);
                foreach (var rcd in records)
                {
                    var value = rcd.Value.ToString().ToObject<OverallEducation>();
                    if (value != null && string.IsNullOrWhiteSpace(value.studentId))
                    {
                        overallEducations.Add(value);
                    }
                }
            }
            //2.整个年级的学生基础信息，对比redis获取的差，看是否是有部门未缓存在Redis,缓存没有数据的学生。
            var no_overall_stduents = grade_students.ExceptBy(overallEducations.Select(z => z.studentId), v => v.id);
            if (no_overall_stduents.Any() && no_overall_stduents.Count() > 0)
            {
                string oesql = $"select value c from c where c.semesterId='{_semesterId}' and  c.year={_studyYear} and c.periodId='{_periodId}' and c.studentId in ({string.Join(",", no_overall_stduents.Select(z => $"'{z.id}'"))}) ";
                var oeresults = await client.GetContainer(Constant.TEAMModelOS, Constant.Student).GetList<OverallEducation>(oesql, $"OverallEducation-{_school}");
                if (oeresults.list.IsNotEmpty())
                {
                    overallEducations.AddRange(oeresults.list);
                    //将获取到的最新数据缓存在redis中。下次则不需要再次获取
                    foreach (var item in oeresults.list)
                    {
                        string key = $"OverallEducation:{item.schoolCode}:{item.periodId}:{item.year}:{item.semesterId}:{item.classId}";
                        await _azureRedis.GetRedisClient(8).HashSetAsync(key, item.studentId, item.ToJsonString());
                        await _azureRedis.GetRedisClient(8).KeyExpireAsync(key, new TimeSpan(180 * 24, 0, 0));
                    }
                }
            }
            return (classes, students, grade_classes, grade_students, overallEducations);
        }
    }
}