using Dapper;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using NPOI.SS.UserModel;
using Performance.DtoModels;
using Performance.DtoModels.AppSettings;
using Performance.EntityModels;
using Performance.Infrastructure;
using Performance.Repository;
using Performance.Services.ExtractExcelService;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text.RegularExpressions;

namespace Performance.Services
{
    public class CustomExtractService : IAutoInjection
    {
        private readonly ILogger<CustomExtractService> _logger;
        private readonly IOptions<Application> _options;
        private readonly UserService _userService;
        private readonly RoleService _roleService;
        private readonly PerforPerallotRepository _perallotRepository;
        private readonly PerforPerdeptdicRepository _perforPerdeptdicRepository;
        private readonly PerforHospitalconfigRepository _perforHospitalconfigRepository;
        private readonly PerforcustscriptRepository _perforcustscriptRepository;

        public CustomExtractService(
            ILogger<CustomExtractService> logger,
            IOptions<Application> options,
            UserService userService,
            RoleService roleService,
            PerforPerallotRepository perallotRepository,
            PerforPerdeptdicRepository perforPerdeptdicRepository,
            PerforHospitalconfigRepository perforHospitalconfigRepository,
            PerforcustscriptRepository perforcustscriptRepository)
        {
            _logger = logger;
            _options = options;
            _userService = userService;
            _roleService = roleService;
            _perallotRepository = perallotRepository;
            _perforPerdeptdicRepository = perforPerdeptdicRepository;
            _perforHospitalconfigRepository = perforHospitalconfigRepository;
            _perforcustscriptRepository = perforcustscriptRepository;
        }
        public bool CheckConfigScript(int userId, int allotId)
        {
            var allot = _perallotRepository.GetEntity(w => w.ID == allotId)
               ?? throw new PerformanceException("绩效ID无效");

            var scripts = _perforcustscriptRepository.GetEntities(w => w.HospitalId == allot.HospitalId && w.IsEnable == 1);

            scripts = (IsSecondAdmin(userId, out string[] unitType))
                ? scripts?.Where(w => w.IsSecondAllot == 1).ToList()
                : scripts?.Where(w => w.IsOnceAllot == 1).ToList();

            return scripts?.Count() > 0;
        }

        public bool ExtractData(int userId, int allotId, out string resultFilePath)
        {
            bool result = true;
            var allot = _perallotRepository.GetEntity(w => w.ID == allotId)
                ?? throw new PerformanceException("绩效ID无效");

            var filePath = ExtractHelper.GetExtractFile(allot.HospitalId);
            resultFilePath = filePath;

            var scripts = _perforcustscriptRepository.GetEntities(w => w.HospitalId == allot.HospitalId && w.IsEnable == 1);

            scripts = (IsSecondAdmin(userId, out string[] unitType))
                ? scripts?.Where(w => w.IsSecondAllot == 1).ToList()
                : scripts?.Where(w => w.IsOnceAllot == 1).ToList();

            if (scripts == null || scripts.Count == 0)
            {
                result = false;
                return result;
            }
            var depts = _perforPerdeptdicRepository.GetEntities(w => w.HospitalId == allot.HospitalId);

            IWorkbook workbook = null;
            try
            {
                workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
                ExcelStyle style = new ExcelStyle(workbook);

                WriteDataToFile(userId, allot, scripts, workbook, depts);
            }
            catch (Exception ex)
            {
                allot.IsExtracting = 3;
                resultFilePath = "";
                result = false;
                _logger.LogError("提取数据中发生异常: " + ex.ToString());
            }
            finally
            {
                using (FileStream file = new FileStream(filePath, FileMode.OpenOrCreate))
                {
                    workbook.Write(file);
                }
                workbook.Close();
            }
            return result;
        }

        /// <summary>
        /// 返回需要关注的列头索引，找不到-1
        /// </summary>
        /// <param name="heads"></param>
        /// <returns></returns>
        private (int deptIndex, int sourceIndex, int unitIndex) GetCruxHeader(IEnumerable<string> heads)
        {
            var deptIndex = heads.ToList().IndexOf("科室名称");
            var sourceIndex = heads.ToList().IndexOf("来源");
            var unitIndex = heads.ToList().IndexOf("组别");
            return (deptIndex, sourceIndex, unitIndex);
        }

        private void WriteDataToFile(int userId, per_allot allot, List<cust_script> scripts, IWorkbook workbook, List<per_dept_dic> depts)
        {
            var configs = _perforHospitalconfigRepository.GetEntities(t => t.HospitalId == allot.HospitalId)
                ?? throw new PerformanceException("当前医院没有数据库地址配置");

            var parameters = GetParameters(allot);
            var (isSecondAdmin, department, unitType) = GetUserDepartment(userId);
            parameters.Add("@department", $"'{department}'");

            foreach (var item in scripts)
            {
                var conf = configs.FirstOrDefault(w => w.Id == item.ConfigId);
                if (conf != null)
                {
                    var execsql = item.Script;
                    var dynamics = QueryData(conf, execsql, parameters);

                    try
                    {
                        for (int i = 0; i < workbook.NumberOfSheets; i++)
                        {
                            if (item.Name == workbook.GetSheetAt(i).SheetName)
                                workbook.RemoveSheetAt(i);
                        }

                        var sheet = workbook.CreateSheet(item.Name);
                        // 没数据跳过
                        if (dynamics == null || dynamics.Count() == 0)
                            continue;

                        var headers = ((IDictionary<string, object>)dynamics.ElementAt(0)).Keys;
                        for (int col = 0; col < headers.Count; col++)
                        {
                            sheet.SetValue(1, col + 4, headers.ElementAt(col));
                        }
                        // 列头坐标信息
                        var (deptIndex, sourceIndex, unitIndex) = GetCruxHeader(headers);

                        int row = 2;
                        for (int r = 0; r < dynamics.Count(); r++)
                        {
                            var temp = (IDictionary<string, object>)dynamics.ElementAt(r); // 行数据

                            #region 替换原始科室名称及跳过写入EXCEL逻辑

                            string accountUnit = "";
                            // “科室名称”必须存在 “来源”必须存在
                            if (deptIndex > -1)
                            {
                                string atDepartment = temp.ElementAt(deptIndex).Value?.ToString() ?? ""; // 当前行“科室名称” 
                                string atUnitType = (unitIndex > -1) ? temp.ElementAt(unitIndex).Value?.ToString() : "";  // 当前行“核算单元类别” 

                                // 如果是科主任护士长 则取角色的 核算单元类别
                                // 如果是核算办 则取数据中 核算单元类别
                                string[] atUnitTypeList = new string[] { };
                                if (isSecondAdmin && unitType.Any())
                                    atUnitTypeList = unitType;
                                else if (unitIndex > -1)
                                    atUnitTypeList = new string[] { atUnitType };
                                // 替换原始科室名称
                                if (atUnitTypeList.Any() && !string.IsNullOrEmpty(atDepartment))
                                {
                                    var tempDepts = depts.Where(w => atUnitTypeList.Contains(w.UnitType) && w.HISDeptName == atDepartment);
                                    if (isSecondAdmin && unitType.Any(w => w == UnitType.特殊核算组.ToString() || w == UnitType.行政后勤.ToString()))
                                    {
                                        accountUnit = tempDepts.FirstOrDefault()?.AccountingUnit ?? "";
                                    }
                                    else
                                    {
                                        string atSource = temp.ElementAt(sourceIndex).Value?.ToString() ?? ""; // 当前行“来源” 
                                        accountUnit = tempDepts.FirstOrDefault(w => w.Source == atSource)?.AccountingUnit ?? "";
                                    }
                                }
                                // 跳过写入EXCEL逻辑
                                if (isSecondAdmin)
                                {
                                    if (string.IsNullOrEmpty(accountUnit) || accountUnit != department) continue;
                                    if (string.IsNullOrEmpty(atUnitType) || !atUnitTypeList.Contains(atUnitType)) continue;
                                }
                            }
                            #endregion

                            int col = 4;
                            for (int c = 0; c < headers.Count; c++)
                            {
                                var value = temp.ElementAt(c).Value;

                                // 替换原始科室名称
                                if (deptIndex > -1 && deptIndex == c)
                                    value = accountUnit;

                                sheet.SetValue(row, col, value);
                                col++;
                            }
                            row++;
                        }
                    }
                    catch (Exception ex)
                    {
                        _logger.LogError($"提取绩效数据写入错误：{item.Name}：{ex}");
                    }
                }
            }
        }

        /// <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<dynamic> QueryData(sys_hospitalconfig config, string execsql, Dictionary<string, string> parameters)
        {
            try
            {
                using (var connection = ConnectionBuilder.Create((DatabaseType)config.DataBaseType, config.DbSource, config.DbName, config.DbUser, config.DbPassword))
                {
                    foreach (var item in parameters)
                    {
                        execsql = Regex.Replace(execsql, item.Key, item.Value, RegexOptions.IgnoreCase);
                    }

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

                    return result;
                }
            }
            catch (Exception ex)
            {
                _logger.LogError($"提取绩效数据SQL脚本查询错误:{execsql} 错误原因：{ex}");
            }
            return null;
        }

        /// <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>
        /// 是否是二次分配管理员  是 true 否 false
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        private bool IsSecondAdmin(int userId, out string[] unitType)
        {
            Dictionary<int, string[]> pairs = new Dictionary<int, string[]>
            {
                { _options.Value.NurseRole, new string[] { UnitType.护理组.ToString() } },
                { _options.Value.DirectorRole, new string[] { UnitType.医生组.ToString(), UnitType.医技组.ToString() } },
                { _options.Value.SpecialRole, new string[] { UnitType.特殊核算组.ToString() } },
                { _options.Value.OfficeRole, new string[] { UnitType.行政后勤.ToString() } },
            };

            var roleId = _roleService.GetRole(userId)?.FirstOrDefault().ID ?? 0;
            if (pairs.ContainsKey(roleId))
            {
                unitType = pairs[roleId];
                return true;
            }
            unitType = new string[] { };
            return false;
        }

        /// <summary>
        /// 返回二次分配管理科室 默认 返回 空值
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        private (bool isSecondAdmin, string department, string[] unitType) GetUserDepartment(int userId)
        {
            var user = _userService.GetUser(userId)
                ?? throw new PerformanceException("当前用户信息无效");
            var isSecondAdmin = IsSecondAdmin(userId, out string[] unitType);
            var department = isSecondAdmin ? (user.Department ?? "") : string.Empty;
            return (isSecondAdmin, department, unitType);
        }
    }
}