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;

namespace Performance.Services.ExtractExcelService.SheetDataWrite
{
    public class SpecialUnitDataWrite : ISheetDataWrite
    {
        public void WriteCollectData(ISheet sheet, PerSheetPoint point, SheetType sheetType, ExcelStyle style, List<collect_data> collects)
        {

        }

        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;

                if (data is List<ExtractTransDto> extractDto && extractDto.Any())
                {
                    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();

                    var specials = exSpecials.GroupJoin(extractDto, outer => new { outer.Department, outer.Target }, inner => new { 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, columns, ref dataFirstRowNum);
                    AddNewSpecialConfig(sheet, style, specials, needMergedRegions, columns, dataFirstRowNum);
                    AddMergedRegion(sheet, needMergedRegions, columns);
                }

            }
            catch (Exception ex)
            {

            }
        }

        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) row.RemoveCell(cell);
                }
            }
        }

        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);
                }
            }
        }

        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))
            {
                string value = ""; CellType cellType = CellType.Unknown;
                var valueCell = sheet.GetRow(merged.FirstRow)?.GetCell(merged.FirstColumn);
                if (valueCell != null)
                {
                    cellType = valueCell.CellType;
                    valueCell.SetCellType(CellType.String);
                    value = valueCell.StringCellValue;
                }

                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, cellType, value);
                    }
                    row.SetRowStyle(basicStyle);
                }
            }
        }

        private void SupplySpecialQuantity(ISheet sheet, ExcelStyle style, List<SpecialDto> specials, List<string> columns, ref int dataFirstRowNum)
        {
            if (sheet == null) return;

            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);

                bool hasValue = false;
                foreach (var item in row.GetCellValues())
                    if (!string.IsNullOrEmpty(item))
                    {
                        hasValue = true; break;
                    }

                if (hasValue)
                {
                    var department = row.GetCell(dict[SpecialUnitColumns.Department]).GetDecodeEscapes();
                    var target = row.GetCell(dict[SpecialUnitColumns.Target]).GetDecodeEscapes();

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

                        specials.Remove(special);
                    }

                    dataFirstRowNum = rowIndex + 1;
                }
            }
        }

        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.SetCellValue<double>(item.Value.Invoke(special));
                    cell.CellStyle = style;
                }
            }
        }

        private void AddMergedRegion(ISheet sheet, List<SpecialCellRange> ranges, List<string> columns)
        {
            if (columns == null || !columns.Any()) return;

            var columnIndexs = new string[] { SpecialUnitColumns.Department, SpecialUnitColumns.PeopleNumber, SpecialUnitColumns.AdjustFactor }.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);
                }
            }
        }

        private void SetCellValue(ICell cell, CellType cellType, string value)
        {
            if (string.IsNullOrEmpty(value))
            {
                cell.SetCellValue("");
                return;
            }

            switch (cellType)
            {
                case CellType.Numeric:
                    cell.SetCellValue(ConvertHelper.To<double>(value));
                    break;
                case CellType.String:
                    cell.SetCellValue(value.ToString());
                    break;
                case CellType.Formula:
                    cell.SetCellType(CellType.String);
                    cell.SetCellValue(value.ToString());
                    break;
                case CellType.Boolean:
                    cell.SetCellValue(ConvertHelper.To<int>(value));
                    break;
                case CellType.Unknown:
                case CellType.Blank:
                case CellType.Error:
                default:
                    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 },
        };
    }
}
