using Dapper;
using Microsoft.Extensions.Logging;
using NPOI.SS.UserModel;
using Performance.DtoModels;
using Performance.EntityModels;
using Performance.Infrastructure;
using Performance.Repository;
using Performance.Services.ExtractExcelService.SheetDataWrite;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;

namespace Performance.Services.ExtractExcelService
{
    public class ExtractService : IAutoInjection
    {
        private readonly ILogger logger;
        private readonly LogManageService logService;
        private readonly QueryService queryService;
        private readonly PersonService personService;
        private readonly PerSheetService perSheetService;
        private readonly CostTransferDataWrite costTransfer;
        private readonly DictionaryService dictionaryService;
        private readonly CustomDataWrite customDataWrite;
        private readonly PerforPerallotRepository perallotRepository;
        private readonly PerforCollectdataRepository collectdataRepository;
        private readonly PerforExtypeRepository extypeRepository;
        private readonly PerforPeremployeeRepository peremployeeRepository;
        private readonly PerforPerdeptdicRepository perdeptdicRepository;
        private readonly PerforCofdrugtypefactorRepository drugtypefactorRepository;

        public ExtractService(
            ILogger<ExtractService> logger,
            LogManageService logService,
            QueryService queryService,
            PersonService personService,
            PerSheetService perSheetService,
            CostTransferDataWrite costTransfer,
            DictionaryService dictionaryService,
            CustomDataWrite customDataWrite,
            PerforPerallotRepository perallotRepository,
            PerforCollectdataRepository collectdataRepository,
            PerforExtypeRepository extypeRepository,
            PerforPeremployeeRepository peremployeeRepository,
            PerforPerdeptdicRepository perdeptdicRepository,
            PerforCofdrugtypefactorRepository drugtypefactorRepository
            )
        {
            this.logger = logger;
            this.logService = logService;
            this.queryService = queryService;
            this.personService = personService;
            this.perSheetService = perSheetService;
            this.costTransfer = costTransfer;
            this.dictionaryService = dictionaryService;
            this.customDataWrite = customDataWrite;
            this.perallotRepository = perallotRepository;
            this.collectdataRepository = collectdataRepository;
            this.extypeRepository = extypeRepository;
            this.peremployeeRepository = peremployeeRepository;
            this.perdeptdicRepository = perdeptdicRepository;
            this.drugtypefactorRepository = drugtypefactorRepository;
        }

        /// <summary>
        /// 抽取绩效文件
        /// </summary>
        /// <param name="allotId">抽取绩效Id</param>
        /// <param name="hospitalId">医院Id</param>
        /// <param name="email">邮箱地址</param>
        /// <param name="groupName">即时日志分组名称</param>
        /// <param name="filePath">历史提交文件地址</param>
        /// <param name="isSingle">抽取是否在同一项目</param>
        public string Main(int allotId, int hospitalId, string email, string groupName, string filePath = null, bool isSingle = false)
        {
            groupName = allotId.ToString();
            string extractFilePath = "";
            per_allot allot = null;
            IWorkbook workbook = null;
            IDbConnection connection = peremployeeRepository.GetDbConnection();
            try
            {
                logService.ClearExtractLog(allotId);

                logService.ReturnTheLog(allotId, groupName, 2, "等待提取", $"确认配置信息是否可完成数据提取...", 1, isSingle);

                var allots = perallotRepository.GetEntities(t => t.HospitalId == hospitalId);
                if (allots == null || !allots.Any(t => t.ID == allotId)) throw new Exception("绩效不存在");

                allot = allots.First(t => t.ID == allotId);
                var dict = new Dictionary<ExDataDict, object>();

                logService.ReturnTheLog(allotId, groupName, 3, "", 5, 1, isSingle);

                queryService.ClearHistoryData(allot.ID, groupName, isSingle);

                var data = queryService.Handler(hospitalId, allot, groupName, isSingle, ref dict);
                var standData = StandDataFormat(hospitalId, data);

                dictionaryService.Handler(hospitalId, allot, groupName, isSingle);

                var statesArray = new int[] { (int)AllotStates.GenerateSucceed, (int)AllotStates.Archive };

                var templateFilePath = ExtractHelper.GetExtractFile(hospitalId, allot, ref extractFilePath, filePath);
                logService.ReturnTheLog(allotId, groupName, 2, "创建文件", $"模板文件: {templateFilePath}", 1, isSingle);

                if (!FileHelper.IsExistFile(templateFilePath)) throw new PerformanceException("抽取文件创建失败");

                workbook = ExcelHelper.GetWorkbook(templateFilePath);
                if (workbook == null) throw new PerformanceException("文件读取失败");

                WriteDataToFile(workbook, allot, dict, standData, groupName, isSingle);
                logService.ReturnTheLog(allotId, groupName, 2, "提取完成", $"绩效数据提取成功", 5, isSingle);

                allot.IsExtracting = isSingle ? 2 : 0;
                allot.ExtractPath = extractFilePath;

                workbook.EvaluateAll();
                using (FileStream file = new FileStream(extractFilePath, FileMode.OpenOrCreate))
                {
                    workbook.Write(file);
                }
                workbook.Close();
            }
            catch (Exception ex)
            {
                allot.IsExtracting = 3;
                logService.ReturnTheLog(allotId, groupName, 2, "提取完成", $"绩效数据提取失败", 4, isSingle);
                logger.LogError("提取数据中发生异常: " + ex.ToString());
            }
            finally
            {
                logService.ReturnTheLog(allotId, groupName, 3, "", 100, 5, isSingle);
                UpdateAllot(connection, allot);
            }
            return extractFilePath;
        }

        /// <summary>
        /// 数据写入
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="allot"></param>
        /// <param name="exdict"></param>
        /// <param name="extractDto"></param>
        /// <param name="groupName"></param>
        /// <param name="isSingle"></param>
        private void WriteDataToFile(IWorkbook workbook, per_allot allot, Dictionary<ExDataDict, object> exdict, List<ExtractTransDto> extractDto, string groupName, bool isSingle)
        {
            ExcelStyle style = new ExcelStyle(workbook);

            var models = exdict[ExDataDict.ExModule] as List<ex_module>;
            ExtractHelper.CreateNotExistSheet(models, workbook);

            var employeeDict = peremployeeRepository.GetEntities(t => t.AllotId == allot.ID);
            var collectData = collectdataRepository.GetEntities(t => t.AllotID == allot.ID && t.Status == 3);

            var drugfactorData = drugtypefactorRepository.GetEntities(t => t.AllotID == allot.ID) ?? new List<cof_drugtype_factor>();
            if (!exdict.ContainsKey(ExDataDict.IncomeFactor))
                exdict.Add(ExDataDict.IncomeFactor, drugfactorData);

            logger.LogInformation($"allotId: {allot.ID}: 总金额 - {extractDto?.Sum(s => s.Value ?? 0)}");

            WriteDataFactory factory = new WriteDataFactory();
            var types = new List<SheetType> { SheetType.OtherIncome, SheetType.Income, SheetType.Expend, SheetType.Workload, SheetType.OtherWorkload/*, SheetType.AccountBasic*/ };
            types.AddRange(OtherConfigSheet);
            decimal ratio = 60m;

            //string accountBasicSheetName = "";
            //for (int sheetIndex = 0; sheetIndex < workbook.NumberOfSheets; sheetIndex++)
            //{
            //    var sheetName = workbook.GetSheetAt(sheetIndex).SheetName;
            //    if (!sheetName.StartsWith("4.1"))
            //        continue;

            //    accountBasicSheetName = sheetName;
            //    break;
            //}

            //var accountBasicSheet = workbook.GetSheet(accountBasicSheetName);
            //HandleSheet(workbook, allot, exdict, extractDto, groupName, isSingle, accountBasicSheet, ratio, types, factory, style, employeeDict, collectData);

            for (int sheetIndex = 0; sheetIndex < workbook.NumberOfSheets; sheetIndex++)
            {
                var sheet = workbook.GetSheetAt(sheetIndex);

                HandleSheet(workbook, allot, exdict, extractDto, groupName, isSingle, sheet, ratio, types, factory, style, employeeDict, collectData);
            }

            customDataWrite.WriteDataToCustom(workbook, allot, style);
        }

        private void HandleSheet(IWorkbook workbook, per_allot allot, Dictionary<ExDataDict, object> exdict, List<ExtractTransDto> extractDto, string groupName, bool isSingle,
            ISheet sheet, decimal ratio, List<SheetType> types, WriteDataFactory factory, ExcelStyle style, List<per_employee> employeeDict, List<collect_data> collectData)
        {
            try
            {
                if (sheet == null) return;

                string sheetName = sheet.SheetName.NoBlank();

                ratio += 40m / workbook.NumberOfSheets;
                var sheetType = perSheetService.GetSheetType(sheet.SheetName);
                if (sheetType == SheetType.Unidentifiable) return;

                logService.ReturnTheLog(allot.ID, groupName, 3, "", ratio > 99 ? 99 : ratio, 1, isSingle);
                logService.ReturnTheLog(allot.ID, groupName, 2, "写入数据", $"sheet“{sheet.SheetName}”开始写入数据", 1, isSingle);

                var point = PerSheetDataFactory.GetDataRead(sheetType)?.Point;
                if (sheetType == SheetType.OtherWorkload) point = PerSheetDataFactory.GetDataRead(SheetType.Workload)?.Point;

                if (types.Contains(sheetType) && point != null && point.DataFirstCellNum.HasValue)
                {
                    logger.LogInformation($"{sheetName}开始清除历史数据。point：{JsonHelper.Serialize(point)}");
                    ExtractHelper.ClearSheetPartialData(sheet, point, sheetType);
                }

                var customer = factory.GetWriteData(sheetType, logger);
                if (customer != null)
                {
                    var collects = collectData?.Where(t => t.SheetName.NoBlank() == sheetName).ToList();
                    customer.WriteCollectData(sheet, point, sheetType, style, collects, exdict);

                    var exdata = extractDto.Where(t => t.SheetName.NoBlank() == sheetName)?.ToList();
                    if (exdata != null)
                    {
                        logger.LogInformation($"{sheetName}: 总金额 - {exdata.Sum(s => s.Value ?? 0)}; 科室 - {string.Join(",", exdata.Select(s => s.Department).Distinct())}");
                    }
                    var data = GetDataBySheetType(allot.HospitalId, sheetType, exdata, employeeDict);
                    customer.WriteSheetData(sheet, point, sheetType, style, data, exdict);
                }

                if (sheetName.StartsWith("1.0.1"))
                    costTransfer.WriteSheetData(sheet, point, style, allot.ID, allot.HospitalId);

                logService.ReturnTheLog(allot.ID, groupName, 2, "写入数据", $"sheet“{sheet.SheetName}”已完成数据写入", 1, isSingle);
            }
            catch (Exception ex)
            {
                logger.LogError("写入数据时发生异常：" + ex);
            }
        }

        private object GetDataBySheetType(int hospitalId, SheetType sheetType, List<ExtractTransDto> extractDto, List<per_employee> employeeDict)
        {
            switch (sheetType)
            {
                case SheetType.Employee:
                case SheetType.ClinicEmployee:
                    return employeeDict;

                case SheetType.AccountBasic:
                    return perdeptdicRepository.GetAccountBasicAccountingUnit(hospitalId);

                default:
                    return extractDto;
            }
        }

        /// <summary>
        /// 标准数据格式, 匹配科室字典
        /// </summary>
        /// <param name="hospitalId"></param>
        /// <param name="results"></param>
        /// <returns></returns>
        private List<ExtractTransDto> StandDataFormat(int hospitalId, List<ex_result> results)
        {
            if (results == null || !results.Any()) return new List<ExtractTransDto>();

            results.ForEach(t =>
            {
                t.Category = string.IsNullOrEmpty(t.Category) ? "（空白）" : t.Category;
                t.Department = string.IsNullOrEmpty(t.Department) ? "（空白）" : t.Department;
            });

            var types = extypeRepository.GetEntities(w => w.HospitalId == hospitalId) ?? new List<ex_type>();

            var dict = personService.GetDepartments(hospitalId)?.ToList();
            if (dict == null || !dict.Any())
            {
                return results.GroupBy(t => new { t.Department, t.Category, t.Source }).Select(t => new ExtractTransDto
                {
                    SheetName = t.Key.Source,
                    Department = t.Key.Department,
                    Category = t.Key.Category,
                    Value = t.Sum(group => group.Fee) == 0 ? null : t.Sum(group => group.Fee),
                    EName = types.FirstOrDefault(w => w.Id == t.FirstOrDefault().TypeId)?.EName
                }).ToList();
            }

            dict.ForEach(t =>
            {
                t.HISDeptName = WriteDataHelper.HasValue(t.HISDeptName, t.Department);
            });

            var data = results.GroupJoin(dict, outer => new { Department = outer.Department }, inner => new { Department = inner.HISDeptName }, (outer, inner) => new { outer, inner })
                .Select(t =>
                {
                    var dept = !string.IsNullOrEmpty(t.inner.FirstOrDefault()?.Department) ? t.inner.FirstOrDefault()?.Department : t.outer.Department;
                    return new ExtractTransDto
                    {
                        SheetName = t.outer.Source,
                        Department = dept,
                        Category = t.outer.Category,
                        DoctorName = t.outer.DoctorName,
                        PersonnelNumber = t.outer.PersonnelNumber,
                        Value = t.outer.Fee ?? 0,
                        OutDoctorAccounting = t.inner.FirstOrDefault(f => f.Department == dept)?.OutDoctorAccounting?.AccountingUnit,
                        OutNurseAccounting = t.inner.FirstOrDefault(f => f.Department == dept)?.OutNurseAccounting?.AccountingUnit,
                        OutTechnicAccounting = t.inner.FirstOrDefault(f => f.Department == dept)?.OutTechnicAccounting?.AccountingUnit,
                        InpatDoctorAccounting = t.inner.FirstOrDefault(f => f.Department == dept)?.InpatDoctorAccounting?.AccountingUnit,
                        InpatNurseAccounting = t.inner.FirstOrDefault(f => f.Department == dept)?.InpatNurseAccounting?.AccountingUnit,
                        InpatTechnicAccounting = t.inner.FirstOrDefault(f => f.Department == dept)?.InpatTechnicAccounting?.AccountingUnit,
                        SpecialAccounting = t.inner.FirstOrDefault(f => f.Department == dept)?.SpecialAccounting?.AccountingUnit ?? dept,
                        EName = types.FirstOrDefault(w => w.Id == t.outer.TypeId)?.EName,
                    };
                });

            var groupdata = data.GroupBy(t => new { t.Department, t.Category, t.SheetName }).Select(t => new ExtractTransDto
            {
                SheetName = t.Key.SheetName,
                Department = t.Key.Department,
                Category = t.Key.Category,
                Value = t.Sum(group => group.Value) == 0 ? null : t.Sum(group => group.Value),
                OutDoctorAccounting = t.FirstOrDefault()?.OutDoctorAccounting,
                OutNurseAccounting = t.FirstOrDefault()?.OutNurseAccounting,
                OutTechnicAccounting = t.FirstOrDefault()?.OutTechnicAccounting,
                InpatDoctorAccounting = t.FirstOrDefault()?.InpatDoctorAccounting,
                InpatNurseAccounting = t.FirstOrDefault()?.InpatNurseAccounting,
                InpatTechnicAccounting = t.FirstOrDefault()?.InpatTechnicAccounting,
                SpecialAccounting = t.FirstOrDefault()?.SpecialAccounting,
                EName = t.FirstOrDefault(w => !string.IsNullOrEmpty(w.EName))?.EName
            });

            return groupdata.ToList();
        }

        private void UpdateAllot(IDbConnection connection, per_allot allot)
        {
            try
            {
                string sql = "update per_allot set isextracting = @isextracting, extractpath = @extractpath where id = @id";

                if (connection == null) return;

                if (connection.State == ConnectionState.Closed)
                    connection.Open();

                connection.Execute(sql, allot);
            }
            catch (Exception ex)
            {
                logger.LogError(ex.Message);
            }
        }

        private readonly List<SheetType> OtherConfigSheet = new List<SheetType>
        {
            SheetType.AccountExtra,
            SheetType.PersonExtra,
            SheetType.AccountDrugAssess,
            SheetType.AccountMaterialsAssess,
            SheetType.AccountScoreAverage,
            SheetType.AccountAdjustLaterOtherFee,
            SheetType.PersonAdjustLaterOtherFee,
            SheetType.BudgetRatio,
            SheetType.AssessBeforeOtherFee,
            SheetType.PersonOtherManagePerforFee,
            SheetType.PersonAdjustLaterOtherManagePerforFee,
            SheetType.PersonPostCoefficient,
            SheetType.WorkloadMedicineProp,
            SheetType.WorkloadCMI,
            SheetType.WorkloadIncline,
        };
    }
}
