using Dapper;
using Microsoft.AspNetCore.Mvc.ViewFeatures;
using Microsoft.Extensions.Logging;
using Performance.DtoModels;
using Performance.EntityModels;
using Performance.Infrastructure;
using Performance.Repository;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

namespace Performance.Services.ExtractExcelService
{
    public enum ExDataDict
    {
        ExModule = 1,
        ExItem = 2,
        ExSpecial = 3,
        AccountingBasic = 4,
        IncomeFactor = 5,
    }

    public class QueryService : IAutoInjection
    {
        private readonly ILogger logger;
        private readonly LogManageService logService;
        private readonly PerforExmoduleRepository exmoduleRepository;
        private readonly PerforExitemRepository exitemRepository;
        private readonly PerforExspecialRepository exspecialRepository;
        private readonly PerforExscriptRepository exscriptRepository;
        private readonly PerforExresultRepository exresultRepository;
        private readonly PerforHospitalconfigRepository hosconfigRepository;
        private readonly PerforPerallotRepository perallotRepository;

        public QueryService(
            ILogger<QueryService> logger,
            LogManageService logService,
            PerforExmoduleRepository exmoduleRepository,
            PerforExitemRepository exitemRepository,
            PerforExspecialRepository exspecialRepository,
            PerforExscriptRepository exscriptRepository,
            PerforExresultRepository exresultRepository,
            PerforHospitalconfigRepository hosconfigRepository,
            PerforPerallotRepository perallotRepository
            )
        {
            this.logger = logger;
            this.logService = logService;
            this.exmoduleRepository = exmoduleRepository;
            this.exitemRepository = exitemRepository;
            this.exspecialRepository = exspecialRepository;
            this.exscriptRepository = exscriptRepository;
            this.exresultRepository = exresultRepository;
            this.hosconfigRepository = hosconfigRepository;
            this.perallotRepository = perallotRepository;
        }

        private readonly DateTime CreateTime = DateTime.Now;
        private static Dictionary<int, IDbConnection> pools = new Dictionary<int, IDbConnection>();

        /// <summary>
        /// 获取抽取数据
        /// </summary>
        /// <param name="hospitalId"></param>
        /// <param name="allot"></param>
        /// <returns></returns>
        public List<ex_result> Handler(int hospitalId, per_allot allot, string groupName, bool isSingle, ref Dictionary<ExDataDict, object> dict)
        {
            try
            {
                var configs = hosconfigRepository.GetEntities(t => t.HospitalId == hospitalId);
                if (configs == null || !configs.Any()) throw new Exception("医院未配置绩效抽取信息");

                dict = new Dictionary<ExDataDict, object>
                {
                    { ExDataDict.ExModule, new List<ex_module>() },
                    { ExDataDict.ExItem, new List<ex_item>() },
                    { ExDataDict.ExSpecial, new List<ex_script>() }
                };
                var extypeIds = GetQueryScriptIds(hospitalId, ref dict);

                var data = new List<ex_result>();

                logService.ReturnTheLog(allot.ID, groupName, 2, "提取数据", $"开始提取数据", isSingle: isSingle);

                var scripts = exscriptRepository.GetEntities(t => extypeIds.Contains(t.TypeId));
                if (scripts != null && scripts.Any())
                {
                    var allmodules = dict[ExDataDict.ExModule] as List<ex_module>;

                    foreach (var pair in dict)
                    {
                        switch (pair.Key)
                        {
                            case ExDataDict.ExModule:
                                data.AddRange(ExtractModuleData(allot, groupName, isSingle, scripts, configs, pair.Value));
                                break;

                            case ExDataDict.ExItem:
                                data.AddRange(ExtractItemData(allot, groupName, isSingle, scripts, configs, allmodules, pair.Value));
                                break;

                            case ExDataDict.ExSpecial:
                                data.AddRange(ExtractSpecialData(allot, groupName, isSingle, scripts, configs, pair.Value));
                                break;
                        }
                    }
                }
                return data;
            }
            catch (Exception)
            {
                logger.LogError("获取数据时发生异常");
                throw;
            }
        }

        /// <summary>
        /// 获取医院抽取项配置
        /// </summary>
        /// <param name="hospitalId"></param>
        /// <param name="dict"></param>
        /// <returns></returns>
        private List<int> GetQueryScriptIds(int hospitalId, ref Dictionary<ExDataDict, object> dict)
        {
            var extypeIds = new List<int>();

            var modules = exmoduleRepository.GetEntities(t => t.HospitalId == hospitalId && t.SheetType != (int)SheetType.Custom);
            var items = new List<ex_item>();
            if (modules != null && modules.Any())
            {
                dict[ExDataDict.ExModule] = modules;
                extypeIds.AddRange(modules.Select(t => t.TypeId ?? 0));

                items = exitemRepository.GetEntities(t => t.ModuleId.HasValue && modules.Select(m => m.Id).Contains(t.ModuleId.Value));
                if (items != null && items.Any(t => t.TypeId.HasValue))
                {
                    dict[ExDataDict.ExItem] = items;
                    extypeIds.AddRange(items.Select(t => t.TypeId ?? 0));
                }
            }

            var specials = exspecialRepository.GetEntities(t => t.HospitalId == hospitalId);
            if (specials != null && specials.Any())
            {
                dict[ExDataDict.ExSpecial] = specials;
                extypeIds.AddRange(specials.Select(t => t.TypeId ?? 0));
            }

            var exids = extypeIds.Where(t => t > 0);
            if (exids == null || !exids.Any()) return new List<int>();

            return exids.Distinct().ToList();
        }

        /// <summary>
        /// 清除历史抽取数据
        /// </summary>
        /// <param name="allotId"></param>
        public void ClearHistoryData(int allotId, string groupName, bool isSingle)
        {
            logService.ReturnTheLog(allotId, groupName, 2, "清除数据", $"开始清除历史提取数据", 1, isSingle);
            perallotRepository.ClearResultData(allotId);
            logService.ReturnTheLog(allotId, groupName, 2, "清除数据", $"清除历史提取数据已完成", 1, isSingle);
        }

        #region ExResultData

        /// <summary>
        /// 获取收入费用
        /// </summary>
        /// <param name="allot"></param>
        /// <param name="scripts"></param>
        /// <param name="configs"></param>
        /// <param name="dictValue"></param>
        /// <returns></returns>
        private List<ex_result> ExtractModuleData(per_allot allot, string groupName, bool isSingle, List<ex_script> scripts, List<sys_hospitalconfig> configs, object dictValue)
        {
            var data = new List<ex_result>();

            if (dictValue is List<ex_module> modules && modules != null && modules.Any(t => t.TypeId.HasValue && t.TypeId > 0))
            {
                var typeIds = modules.Where(t => t.TypeId.HasValue && t.TypeId > 0)?.Select(t => t.TypeId.Value).Distinct().ToList();
                if (typeIds == null || typeIds.Count == 0) return data;

                decimal ratio = 5m;
                foreach (var typeId in typeIds)
                {
                    var thisModules = modules.Where(t => t.TypeId == typeId).ToList();

                    ratio += 15m / typeIds.Count();
                    logService.ReturnTheLog(allot.ID, groupName, 3, "", ratio > 20 ? 20 : ratio, 1, isSingle);
                    logService.ReturnTheLog(allot.ID, groupName, 2, "提取数据", $"开始提取模块“{string.Join("、", thisModules.Select(t => t.ModuleName))}”的数据", 1, isSingle);

                    foreach (var script in scripts.Where(t => t.TypeId == typeId))
                    {
                        var config = configs.FirstOrDefault(t => t.Id == script.ConfigId);
                        if (config == null)
                        {
                            logService.ReturnTheLog(allot.ID, groupName, 2, "提取数据", $"未正确配置数据库连接", 2, isSingle);
                            continue;
                        }

                        try
                        {
                            var querydata = QueryData<ExtractDto>(config, script.ExecScript, allot, isSingle);

                            if (querydata != null && querydata.Any())
                            {
                                thisModules.ForEach(f =>
                                {
                                    var result = querydata.Select(t => new ex_result
                                    {
                                        Department = t.Department,
                                        Category = t.Category?.Trim(),
                                        Fee = t.Value,
                                        DoctorName = t.DoctorName,
                                        PersonnelNumber = t.PersonnelNumber,
                                        Source = f.ModuleName,
                                        TypeId = typeId,
                                        DatabaseType = config.DataBaseType,
                                        ConfigId = config.Id,
                                        AllotId = allot.ID,
                                        CreateTime = CreateTime,
                                    }).ToList();
                                    exresultRepository.InsertExecute(result.ToArray());
                                    data.AddRange(result);
                                });
                            }

                            logService.ReturnTheLog(allot.ID, groupName, 2, "提取数据", $"模块“{string.Join("、", thisModules.Select(t => t.ModuleName))}”的数据已完成提取", 1, isSingle);
                        }
                        catch (Exception ex)
                        {
                            logger.LogError($"typeId: {typeId}提取数据异常{ex}， {JsonHelper.Serialize(script)}");
                            throw;
                        }
                    }
                }
            }

            return data;
        }

        /// <summary>
        /// 获取工作量、成本等费用
        /// </summary>
        /// <param name="allot"></param>
        /// <param name="scripts"></param>
        /// <param name="configs"></param>
        /// <param name="modules"></param>
        /// <param name="dictValue"></param>
        /// <returns></returns>
        private List<ex_result> ExtractItemData(per_allot allot, string groupName, bool isSingle, List<ex_script> scripts, List<sys_hospitalconfig> configs, List<ex_module> modules, object dictValue)
        {
            var data = new List<ex_result>();

            var incomeModuleIds = modules.Where(w => w.SheetType == (int)SheetType.Income)?.Select(s => s.Id).ToList() ?? new List<int>();
            if (dictValue is List<ex_item> items && items != null && items.Any(t => t.TypeId.HasValue && t.TypeId > 0 && !incomeModuleIds.Contains(t.ModuleId ?? 0)))
            {
                var typeIds = items.Where(t => t.TypeId.HasValue && t.TypeId > 0 && !incomeModuleIds.Contains(t.ModuleId ?? 0))?.Select(t => t.TypeId.Value).Distinct().ToList();
                if (typeIds == null || typeIds.Count == 0) return data;

                decimal ratio = 20m;
                foreach (var typeId in typeIds)
                {
                    var thisItems = items.Where(t => t.TypeId == typeId).ToList();

                    ratio += 30m / typeIds.Count();
                    logService.ReturnTheLog(allot.ID, groupName, 3, "", ratio > 50 ? 50 : ratio, 1, isSingle);
                    logService.ReturnTheLog(allot.ID, groupName, 2, "提取数据", $"开始提取项目“{string.Join("、", thisItems.Select(t => t.ItemName))}”的数据", 1, isSingle);

                    foreach (var script in scripts.Where(t => t.TypeId == typeId))
                    {
                        var config = configs.FirstOrDefault(t => t.Id == script.ConfigId);
                        if (config == null)
                        {
                            logService.ReturnTheLog(allot.ID, groupName, 2, "提取数据", $"未正确配置数据库连接", 2, isSingle);
                            continue;
                        }

                        try
                        {
                            var querydata = QueryData<ExtractDto>(config, script.ExecScript, allot, isSingle);
                            if (querydata != null && querydata.Any())
                            {
                                thisItems.ForEach(f =>
                                {
                                    var modulename = modules.FirstOrDefault(t => t.Id == f.ModuleId)?.ModuleName;
                                    var result = querydata.Select(t => new ex_result
                                    {
                                        Department = t.Department,
                                        Category = f.ItemName,
                                        Fee = t.Value,
                                        DoctorName = t.DoctorName,
                                        PersonnelNumber = t.PersonnelNumber,
                                        Source = modulename,
                                        TypeId = typeId,
                                        DatabaseType = config.DataBaseType,
                                        ConfigId = config.Id,
                                        AllotId = allot.ID,
                                        CreateTime = CreateTime,
                                    }).ToList();
                                    exresultRepository.InsertExecute(result.ToArray());
                                    data.AddRange(result);
                                });
                            }
                        }
                        catch (Exception ex)
                        {
                            logger.LogError($"typeId: {typeId}提取数据异常{ex}， {JsonHelper.Serialize(script)}");
                            throw;
                        }
                    }
                    logService.ReturnTheLog(allot.ID, groupName, 2, "提取数据", $"项目“{string.Join("、", thisItems.Select(t => t.ItemName))}”的数据已完成提取", 1, isSingle);
                }
            }

            return data;
        }

        /// <summary>
        /// 获取特殊核算费用
        /// </summary>
        /// <param name="allot"></param>
        /// <param name="scripts"></param>
        /// <param name="configs"></param>
        /// <param name="dictValue"></param>
        /// <returns></returns>
        private List<ex_result> ExtractSpecialData(per_allot allot, string groupName, bool isSingle, List<ex_script> scripts, List<sys_hospitalconfig> configs, object dictValue)
        {
            var data = new List<ex_result>();

            if (dictValue is List<ex_special> specials && specials != null && specials.Any(t => t.TypeId.HasValue && t.TypeId > 0))
            {
                var typeIds = specials.Where(t => t.TypeId.HasValue && t.TypeId > 0)?.Select(t => t.TypeId.Value).Distinct().ToList();
                if (typeIds == null || typeIds.Count == 0) return data;

                decimal ratio = 50m;
                foreach (var typeId in typeIds)
                {
                    var thisSpecials = specials.Where(t => t.TypeId == typeId).ToList();

                    ratio += 10m / typeIds.Count();
                    logService.ReturnTheLog(allot.ID, groupName, 3, "", ratio > 60 ? 60 : ratio, 1, isSingle);
                    logService.ReturnTheLog(allot.ID, groupName, 2, "提取数据", $"开始提取项目“{string.Join("、", thisSpecials.Select(t => t.Target))}”的数据", 1, isSingle);

                    foreach (var script in scripts.Where(t => t.TypeId == typeId))
                    {
                        var config = configs.FirstOrDefault(t => t.Id == script.ConfigId);
                        if (config == null)
                        {
                            logService.ReturnTheLog(allot.ID, groupName, 2, "提取数据", $"未正确配置数据库连接", 2, isSingle);
                            continue;
                        }

                        try
                        {
                            var querydata = QueryData<ExtractDto>(config, script.ExecScript, allot, isSingle);
                            if (querydata != null && querydata.Any())
                            {
                                thisSpecials.ForEach(f =>
                                {
                                    var result = querydata.Select(t => new ex_result
                                    {
                                        Department = f.Department,
                                        Category = f.Target,
                                        Fee = t.Value,
                                        DoctorName = t.DoctorName,
                                        PersonnelNumber = t.PersonnelNumber,
                                        Source = "4.2 特殊核算单元绩效测算表",
                                        TypeId = typeId,
                                        DatabaseType = config.DataBaseType,
                                        ConfigId = config.Id,
                                        AllotId = allot.ID,
                                        CreateTime = CreateTime,
                                    }).ToList();
                                    exresultRepository.InsertExecute(result.ToArray());
                                    data.AddRange(result);
                                });
                            }
                        }
                        catch (Exception ex)
                        {
                            logger.LogError($"typeId: {typeId}提取数据异常{ex}， {JsonHelper.Serialize(script)}");
                            throw;
                        }
                    }

                    logService.ReturnTheLog(allot.ID, groupName, 2, "提取数据", $"项目“{string.Join("、", thisSpecials.Select(t => t.Target))}”的数据已完成提取", 1, isSingle);
                }
            }

            return data;
        }

        #endregion ExResultData

        #region QueryData

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="config"></param>
        /// <param name="allot"></param>
        /// <param name="execsql"></param>
        /// <param name="source"></param>
        /// <param name="category"></param>
        /// <returns></returns>
        public IEnumerable<T> QueryData<T>(sys_hospitalconfig config, string execsql, per_allot allot, bool isSingle)
        {
            var parameters = GetParameters(allot);
            foreach (var item in parameters)
            {
                execsql = Regex.Replace(execsql, item.Key, item.Value, RegexOptions.IgnoreCase);
            }

            IDbConnection connection = null;
            try
            {
                if (!pools.ContainsKey(config.Id))
                    pools.Add(config.Id, ConnectionBuilder.Create((DatabaseType)config.DataBaseType, config.DbSource, config.DbName, config.DbUser, config.DbPassword));
                connection = pools[config.Id];

                if (connection == null) return Enumerable.Empty<T>();

                if (connection.State == ConnectionState.Closed)
                    connection.Open();
            }
            catch
            {
                logService.ReturnTheLog(allot.ID, allot.ID.ToString(), 2, "数据库连接", $"数据库“{config.DbName}”连接失败", 3, isSingle);
            }

            logger.LogInformation($"提取绩效数据SQL脚本{execsql}");
            var result = connection.Query<T>(execsql, commandTimeout: 20000);
            logger.LogInformation($"提取绩效数据执行脚本获取数据{result?.Count() ?? 0}条记录");

            return result;
        }

        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="allot"></param>
        /// <returns></returns>
        private Dictionary<string, string> GetParameters(per_allot allot)
        {
            DateTime beginTime = new DateTime(allot.Year, allot.Month, 1);

            Dictionary<string, string> pairs = new Dictionary<string, string>
            {
                { "@beginTime", $"'{beginTime.ToString("yyyy-MM-dd")}'" },
                { "@endTime", $"'{beginTime.AddMonths(1).ToString("yyyy-MM-dd")}'"},
            };
            return pairs;
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="config"></param>
        /// <param name="execsql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public IEnumerable<T> QueryData<T>(sys_hospitalconfig config, string execsql, object param)
        {
            var connection = ConnectionBuilder.Create((DatabaseType)config.DataBaseType, config.DbSource, config.DbName, config.DbUser, config.DbPassword);

            if (connection == null) return new List<T>();

            if (connection.State == ConnectionState.Closed)
                connection.Open();
            var result = connection.Query<T>(execsql, param, commandTimeout: 20000);
            return result;
        }

        #endregion QueryData
    }
}
