using Microsoft.Extensions.Logging;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using Performance.DtoModels;
using Performance.EntityModels;
using Performance.Infrastructure;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text.RegularExpressions;

namespace Performance.Services.ExtractExcelService.SheetDataWrite
{
    public class SpecialUnitDataWrite : ISheetDataWrite
    {
        private readonly ILogger logger;

        public SpecialUnitDataWrite(ILogger logger)
        {
            this.logger = logger;
        }

        public void WriteCollectData(ISheet sheet, PerSheetPoint point, SheetType sheetType, ExcelStyle style, List<collect_data> collects, Dictionary<ExDataDict, object> exdict = null)
        {
            if (collects == null || !collects.Any()) return;

            var data = collects.Select(t => new ExtractTransDto
            {
                Department = t.Department,
                SpecialAccounting = t.Department,
                Category = t.TypeName,
                Value = ConvertHelper.To<decimal>(t.CellValue)
            }).ToList();

            WriteSheetData(sheet, point, sheetType, style, data, exdict);
        }

        public void WriteSheetData(ISheet sheet, PerSheetPoint point, SheetType sheetType, ExcelStyle style, object data, Dictionary<ExDataDict, object> exdict = null)
        {
            try
            {
                var columns = sheet.GetOrCreate(point.HeaderFirstRowNum.Value).GetCellValues();
                int dataFirstRowNum = point.DataFirstRowNum.Value;
                ClearSheetPartialData(sheet, columns, dataFirstRowNum, SpecialUnitColumns.Quantity);

                var exSpecials = exdict[ExDataDict.ExSpecial] as List<ex_special>;
                if (exSpecials == null || !exSpecials.Any()) return;

                var extractDto = (data as List<ExtractTransDto>) ?? new List<ExtractTransDto>();
                logger.LogInformation("特殊科室数据1：" + JsonHelper.Serialize(extractDto));

                var mergedRegions = new List<SpecialCellRange>();
                RemoveMergedRegion(sheet, ref mergedRegions);
                SupplyMergedRegionData(sheet, style, mergedRegions);

                var index = columns.IndexOf(SpecialUnitColumns.Department);
                var needMergedRegions = mergedRegions.Where(t => t.FirstColumn == index && t.LastColumn == index)?.ToList() ?? new List<SpecialCellRange>();

                var specials = exSpecials.GroupJoin(extractDto, outer => new { outer.Department, outer.Target }, inner => new { Department = inner.Department, Target = inner.Category },
                    (outer, inner) => new SpecialDto
                    {
                        Department = outer.Department,
                        Target = outer.Target,
                        TargetFactor = outer.TargetFactor,
                        AdjustFactor = outer.AdjustFactor,
                        Quantity = inner.FirstOrDefault()?.Value
                    }).ToList();

                SupplySpecialQuantity(sheet, style, specials, needMergedRegions, columns, ref dataFirstRowNum);
                AddNewSpecialConfig(sheet, style, specials, needMergedRegions, columns, dataFirstRowNum);
                AddMergedRegion(sheet, needMergedRegions, columns);
            }
            catch (Exception)
            {
            }
        }

        /// <summary>
        /// 清除特殊科室数据(数量)
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="columns"></param>
        /// <param name="dataFirstRowNum"></param>
        /// <param name="clear"></param>
        private void ClearSheetPartialData(ISheet sheet, List<string> columns, int dataFirstRowNum, params string[] clear)
        {
            if (sheet == null) return;

            if (columns == null || !columns.Any()) return;
            if (clear == null || !clear.Any()) return;

            var dict = clear.Select(t => columns.IndexOf(t))?.ToList();
            if (dict == null || !dict.Any(t => t > -1)) return;

            dict.RemoveAll(t => t == -1);
            for (int rowIndex = dataFirstRowNum; rowIndex < sheet.LastRowNum + 1; rowIndex++)
            {
                var row = sheet.GetOrCreate(rowIndex);
                foreach (var cellIndex in dict)
                {
                    var cell = row.GetCell(cellIndex);
                    if (cell != null && cell.CellType != CellType.Formula) row.RemoveCell(cell);
                }
            }
        }

        /// <summary>
        /// 取消合并单元格
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="mergedRegions"></param>
        private void RemoveMergedRegion(ISheet sheet, ref List<SpecialCellRange> mergedRegions)
        {
            if (sheet.NumMergedRegions > 0)
            {
                for (int mergedIndex = sheet.NumMergedRegions - 1; mergedIndex >= 0; mergedIndex--)
                {
                    var mergedRegion = sheet.GetMergedRegion(mergedIndex);
                    if (mergedRegion.FirstRow == 0 && mergedRegion.LastRow == 0) continue;

                    mergedRegions.Add(new SpecialCellRange(mergedRegion));
                    sheet.RemoveMergedRegion(mergedIndex);
                }
            }
        }

        /// <summary>
        /// 补充取消单元格后的剩余单元格数据
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="style"></param>
        /// <param name="mergedRegions"></param>
        private void SupplyMergedRegionData(ISheet sheet, ExcelStyle style, List<SpecialCellRange> mergedRegions)
        {
            if (mergedRegions == null || !mergedRegions.Any(t => t.FirstRow > 1)) return;

            var basicStyle = style.GetCellStyle();
            foreach (var merged in mergedRegions.Where(t => t.FirstRow > 1))
            {
                var valueCell = sheet.GetRow(merged.FirstRow)?.GetCell(merged.FirstColumn);

                string value = valueCell != null ? valueCell.ToString() : "";

                merged.Single = value;
                if (string.IsNullOrEmpty(value)) continue;

                for (int rowIndex = merged.FirstRow; rowIndex < merged.LastRow + 1; rowIndex++)
                {
                    var row = sheet.GetRow(rowIndex);
                    if (row == null) row = sheet.CreateRow(rowIndex);
                    for (int cellIndex = merged.FirstColumn; cellIndex < merged.LastColumn + 1; cellIndex++)
                    {
                        var cell = row.GetCell(cellIndex);
                        if (cell == null) cell = row.CreateCell(cellIndex);
                        if (!string.IsNullOrEmpty(value)) SetCellValue(cell, value);
                    }
                    row.SetRowStyle(basicStyle);
                }
            }
        }

        /// <summary>
        /// 补充已存在数据的抽取数据
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="style"></param>
        /// <param name="specials"></param>
        /// <param name="ranges"></param>
        /// <param name="columns"></param>
        /// <param name="dataFirstRowNum"></param>
        private void SupplySpecialQuantity(ISheet sheet, ExcelStyle style, List<SpecialDto> specials, List<SpecialCellRange> ranges, List<string> columns, ref int dataFirstRowNum)
        {
            if (sheet == null) return;

            var rowStyle = style.GetCellStyle();
            var cellStyle = style.SetBgkColorAndFormat(style.GetCellStyle(), StyleType.数据);
            var quantityIndex = columns.IndexOf(SpecialUnitColumns.Quantity);

            var dict = new Dictionary<string, int>();
            var fixedColumns = new List<string> { SpecialUnitColumns.Department, SpecialUnitColumns.Target };
            fixedColumns.ForEach(t =>
            {
                int index = columns.IndexOf(t);
                if (!dict.ContainsKey(t) && index > -1) dict.Add(t, index);
            });
            if (fixedColumns.Count != dict.Count) return;

            for (int rowIndex = dataFirstRowNum; rowIndex < sheet.LastRowNum + 1; rowIndex++)
            {
                var row = sheet.GetOrCreate(rowIndex);
                var target = row.GetCell(dict[SpecialUnitColumns.Target]).GetDecodeEscapes();
                if (!string.IsNullOrEmpty(target))
                {
                    row.SetRowStyle(rowStyle);
                    var department = row.GetCell(dict[SpecialUnitColumns.Department]).GetDecodeEscapes();

                    CheckMergedRegions(rowIndex, department, ranges, dict[SpecialUnitColumns.Department]);

                    var special = specials.FirstOrDefault(t => t.Department == department && t.Target == target);
                    if (special != null)
                    {
                        var cell = row.GetOrCreate(quantityIndex);

                        //if (cell.CellType != CellType.Formula)
                        //{
                        cell.SetCellType(CellType.Numeric);
                        cell.SetCellValue<double>(special.Quantity);
                        cell.CellStyle = cellStyle;
                        //}
                        specials.Remove(special);
                    }
                    dataFirstRowNum = rowIndex + 1;
                }
            }
        }

        /// <summary>
        /// 获取需要合并的单元格
        /// </summary>
        /// <param name="rowIndex"></param>
        /// <param name="department"></param>
        /// <param name="ranges"></param>
        private void CheckMergedRegions(int rowIndex, string department, List<SpecialCellRange> ranges, int departmentColumnIndex)
        {
            try
            {
                var range = ranges.FirstOrDefault(t => t.FirstRow <= rowIndex && t.LastRow >= rowIndex);
                if (range == null)
                {
                    if (rowIndex == 0) return;

                    int prevRowIndex = rowIndex - 1;
                    range = ranges.FirstOrDefault(t => t.FirstRow <= prevRowIndex && t.LastRow >= prevRowIndex);
                    if (range != null && range.Single == department)
                        range.LastRow = rowIndex;
                    else
                    {
                        var columnIndex = ranges.FirstOrDefault()?.FirstColumn ?? departmentColumnIndex;
                        ranges.Add(new SpecialCellRange(new CellRangeAddress(rowIndex, rowIndex, columnIndex, columnIndex))
                        {
                            Single = department
                        });
                    }
                }
            }
            catch (Exception ex)
            {
                logger.LogError("CheckMergedRegions: " + ex);
            }
        }

        /// <summary>
        /// 添加新的特殊科室配置
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="style"></param>
        /// <param name="specials"></param>
        /// <param name="ranges"></param>
        /// <param name="columns"></param>
        /// <param name="dataFirstRowNum"></param>
        private void AddNewSpecialConfig(ISheet sheet, ExcelStyle style, List<SpecialDto> specials, List<SpecialCellRange> ranges, List<string> columns, int dataFirstRowNum)
        {
            if (specials == null || !specials.Any()) return;

            int colIndex = ranges.FirstOrDefault()?.FirstColumn ?? 0;
            var rowStyle = style.SetBgkColorAndFormat(style.GetCellStyle(), StyleType.数据);
            foreach (var item in specials.GroupBy(t => t.Department))
            {
                var deptSpecials = item.ToList();
                if (ranges != null && ranges.Select(t => t.Single).Contains(item.Key))
                {
                    var range = ranges.First(t => t.Single == item.Key);
                    sheet.ShiftRows(range.LastRow + 1, sheet.LastRowNum + specials.Count, item.Count(), true, false);
                    for (int i = 1; i < item.Count() + 1; i++)
                    {
                        var shiftRow = sheet.CreateRow(range.LastRow + i);
                        WriteSpecialData(shiftRow, rowStyle, deptSpecials[i - 1], columns);
                    }
                    dataFirstRowNum += item.Count();
                    range.LastRow += item.Count();
                    foreach (var nextRange in ranges.Where(t => t.FirstRow > range.FirstRow))
                    {
                        nextRange.FirstRow += item.Count();
                        nextRange.LastRow += item.Count();
                    }
                }
                else
                {
                    var range = new SpecialCellRange(new CellRangeAddress(dataFirstRowNum, dataFirstRowNum + item.Count() - 1, colIndex, colIndex))
                    {
                        Single = deptSpecials.First().Department
                    };
                    ranges.Add(range);

                    foreach (var special in deptSpecials)
                    {
                        var shiftRow = sheet.CreateRow(dataFirstRowNum);
                        WriteSpecialData(shiftRow, rowStyle, special, columns);
                        dataFirstRowNum++;
                    }
                }
            }
        }

        private void WriteSpecialData(IRow row, ICellStyle style, SpecialDto special, List<string> columns)
        {
            foreach (var item in specialField)
            {
                if (!columns.Contains(item.Key)) continue;

                var index = columns.IndexOf(item.Key);
                var cell = row.GetOrCreate(index);
                if (cell != null /*&& cell.CellType != CellType.Formula*/)
                {
                    if (new string[] { SpecialUnitColumns.Department, SpecialUnitColumns.Target }.Contains(item.Key))
                        cell.SetCellValue(item.Value.Invoke(special)?.ToString());
                    else
                    {
                        cell.SetCellType(CellType.Numeric);
                        cell.SetCellValue<double>(item.Value.Invoke(special));
                    }
                    cell.CellStyle = style;
                }
            }
        }

        /// <summary>
        /// 合并单元格
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="ranges"></param>
        /// <param name="columns"></param>
        private void AddMergedRegion(ISheet sheet, List<SpecialCellRange> ranges, List<string> columns)
        {
            if (columns == null || !columns.Any()) return;
            if (ranges == null || !ranges.Any()) return;

            var columnIndexs = new string[]
            {
                SpecialUnitColumns.Department,
                SpecialUnitColumns.PeopleNumber,
                SpecialUnitColumns.AdjustFactor,
                //SpecialUnitColumns.AssessBefore,
                SpecialUnitColumns.Avg,
                SpecialUnitColumns.AssessBeforeTotal,
                SpecialUnitColumns.NewAvg,
                SpecialUnitColumns.OldPerformanceTotal,
                SpecialUnitColumns.OldAvg,
                SpecialUnitColumns.AvgDifference
            }.Select(t => columns.IndexOf(t));
            if (columnIndexs == null || !columnIndexs.Any(t => t > -1)) return;

            foreach (var index in columnIndexs.Where(t => t > -1))
            {
                var address = ranges.Where(t => t.LastRow > t.FirstRow)
                    ?.Select(t => new CellRangeAddress(t.FirstRow, t.LastRow, index, index));
                if (address == null || !address.Any()) continue;

                foreach (var item in address)
                {
                    sheet.AddMergedRegion(item);
                }
            }
        }

        public void SetCellValue(ICell cell, string value)
        {
            if (string.IsNullOrEmpty(value)) return;

            switch (value.Trim())
            {
                case string reg when Regex.IsMatch(reg, @"^[+-]?\d*[.]?\d*$"):
                    cell.SetCellType(CellType.Numeric);
                    cell.SetCellValue(ConvertHelper.To<double>(value));
                    break;

                case string reg when Regex.IsMatch(reg, @"^[+-]?\d*$"):
                    cell.SetCellType(CellType.Numeric);
                    cell.SetCellValue(ConvertHelper.To<int>(value));
                    break;

                default:
                    cell.SetCellValue(value);
                    break;
            }
        }

        private Dictionary<string, Func<SpecialDto, object>> specialField = new Dictionary<string, Func<SpecialDto, object>>
        {
            { SpecialUnitColumns.Department, (t) => t.Department },
            { SpecialUnitColumns.Target, (t) => t.Target },
            { SpecialUnitColumns.TargetFactor, (t) => t.TargetFactor },
            { SpecialUnitColumns.Quantity, (t) => t.Quantity },
            { SpecialUnitColumns.AdjustFactor, (t) => t.AdjustFactor },
        };
    }
}
