using Microsoft.Extensions.Caching.Memory;
using Newtonsoft.Json;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using Performance.DtoModels;
using Performance.EntityModels;
using Performance.Infrastructure;
using Polly;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace Performance.Services.OnlineExcel
{
    public partial class OnlineExcelService : IAutoInjection
    {
        public static Dictionary<string, string> HorizontalMapps = new Dictionary<string, string>()
        {
            { "Left", "htLeft" },
            { "Center", "htCenter" },
            { "Right", "htRight" },
            { "Justify", "htJustify" },
        };
        public static Dictionary<string, string> VerticalMapps = new Dictionary<string, string>()
        {
            { "Top", "htTop" },
            { "Center", "htMiddle" },
            { "Bottom", "htBottom" },
        };

        public static TimeSpan absoluteExpirationRelativeToNow = new TimeSpan(0, 30, 0);

        private readonly IMemoryCache _cache;
        private readonly PerSheetService _sheetService;

        public OnlineExcelService(
            IMemoryCache cache,
            PerSheetService sheetService)
        {
            _cache = cache;
            _sheetService = sheetService;
        }
        /// <summary>
        /// 读取Sheet名称
        /// </summary>
        /// <param name="allot"></param>
        /// <returns></returns>
        public List<ExcelSheetInfo> GetExcelSheetName(per_allot allot)
        {
            // 优先返回缓存内容
            var key = $"SheetName:{allot.Path}";
            if (_cache.TryGetValue(key, out List<ExcelSheetInfo> sheetNames))
                return sheetNames;

            sheetNames = sheetNames ?? new List<ExcelSheetInfo>();
            FileInfo file = new FileInfo(allot.Path);

            var version = file.LastWriteTimeUtc.ToTimeStamp();

            using (ExcelPackage package = new ExcelPackage(file))
            {
                foreach (var sheet in package.Workbook.Worksheets)
                {
                    SheetType sheetType = _sheetService.GetSheetType(sheet.Name);

                    if (!sheetNames.Exists(w => w.Name == sheet.Name))
                    {
                        sheetNames.Add(new ExcelSheetInfo
                        {
                            Name = sheet.Name,
                            Row = sheet.Dimension?.End.Row ?? 0,
                            Column = sheet.Dimension?.End.Column ?? 0,
                            SheetType = sheetType,
                            ModuleName = EnumHelper.GetDescription(sheetType),
                            Version = version.ToString(),
                        });
                    }
                }
            }
            sheetNames = sheetNames
                .OrderBy(w => w.SheetType == SheetType.Unidentifiable ? 1 : 0).ToList();

            _cache.Set(key, sheetNames, absoluteExpirationRelativeToNow);

            return sheetNames;
        }
        /// <summary>
        /// 读取Sheet信息
        /// </summary>
        /// <param name="allot"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public EpSheet ReadSheet(per_allot allot, string sheetName)
        {
            // 优先返回缓存内容
            var key = $"SheetData-{sheetName}:{allot.Path}";
            if (_cache.TryGetValue(key, out EpSheet cacheSheet))
                return cacheSheet;

            FileInfo file = new FileInfo(allot.Path);

            using (ExcelPackage package = new ExcelPackage(file))
            {
                var sheet = package.Workbook.Worksheets.FirstOrDefault(w => w.Name == sheetName);
                if (sheet == null) return null;

                IEnumerable<EpMerge> mergeCells = GetMergeCells(sheet);
                List<double> colWidths = GetColWidths(sheet);

                #region data

                List<Dictionary<string, object>> datas = new List<Dictionary<string, object>>();
                List<EpCellClass> cells = new List<EpCellClass>();
                List<EpColumn> renders = new List<EpColumn>();

                for (int row = 1, n = sheet.Dimension.End.Row; row <= n; row++)
                {
                    Dictionary<string, object> data = new Dictionary<string, object>();

                    for (int col = 1, k = sheet.Dimension.End.Column; col <= k; col++)
                    {
                        var cell = sheet.Cells[row, col];
                        if (cell == null) continue;

                        var value = GetCellValue(cell);
                        var colName = Regex.Replace(cell.Address, "[0-9]", "", RegexOptions.IgnoreCase);
                        data.Add(colName, value);

                        var cellStyle = GetCellClass(cell, row, col);
                        if (cellStyle != null)
                            cells.Add(cellStyle);

                        var render = GetCellRender(cell, row, col);
                        if (render != null)
                            renders.Add(render);
                    }
                    datas.Add(data);
                }
                #endregion

                SheetType sheetType = _sheetService.GetSheetType(sheet.Name);
                var handler = PerSheetDataFactory.GetDataRead(sheetType);

                EpSheet epSheet = new EpSheet()
                {
                    fixedColumnsLeft = handler?.Point.DataFirstCellNum ?? 0,
                    fixedRowsTop = handler?.Point.DataFirstRowNum ?? 0,
                    cell = cells,
                    colWidths = colWidths,
                    data = datas,
                    renders = renders,
                    mergeCells = mergeCells,
                };

                _cache.Set(key, epSheet, absoluteExpirationRelativeToNow);

                return epSheet;

            }
        }

        #region
        //private static List<double> GetRowHeights(ExcelWorksheet sheet)
        //{
        //    List<double> rowHeights = new List<double>();
        //    for (int row = 1, k = sheet.Dimension.End.Row; row <= k; row++)
        //    {
        //        rowHeights.Add(Math.Round(sheet.Row(row).Height));
        //    }

        //    return rowHeights;
        //}

        //private EpCustomBorders GetCellBorders(ExcelRange cell, int row, int col)
        //{
        //    if (cell.Style.Border.Top.Style != ExcelBorderStyle.None
        //         || cell.Style.Border.Bottom.Style != ExcelBorderStyle.None
        //         || cell.Style.Border.Left.Style != ExcelBorderStyle.None
        //         || cell.Style.Border.Right.Style != ExcelBorderStyle.None)
        //    {
        //        EpCustomBorders borders = new EpCustomBorders()
        //        {
        //            row = row - 1,
        //            col = col - 1
        //        };

        //        borders.top = new EpCustomBorders.Style { width = 2, color = "#000" };
        //        borders.bottom = new EpCustomBorders.Style { width = 2, color = "#000" };
        //        borders.left = new EpCustomBorders.Style { width = 2, color = "#000" };
        //        borders.right = new EpCustomBorders.Style { width = 2, color = "#000" };
        //        return borders;
        //    }
        //    return null;
        //} 
        #endregion

        /// <summary>
        /// 读取列宽度
        /// </summary>
        /// <param name="sheet"></param>
        /// <returns></returns>
        private static List<double> GetColWidths(ExcelWorksheet sheet)
        {
            List<double> colWidths = new List<double>();
            for (int col = 1, k = sheet.Dimension.End.Column; col <= k; col++)
            {
                colWidths.Add(Math.Round(sheet.Column(col).Width * 10));
            }

            return colWidths;
        }
        /// <summary>
        /// 读取单元格渲染方式
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="row"></param>
        /// <param name="col"></param>
        /// <returns></returns>
        private EpColumn GetCellRender(ExcelRange cell, int row, int col)
        {
            var column = new EpColumn
            {
                row = row - 1,
                col = col - 1,
            };


            if (cell.Value is ExcelErrorValue)
            {
                column.renderer = "customExcelErrorValueStylesRenderer";
            }
            else if (!string.IsNullOrEmpty(cell.Formula))
            {
                column.renderer = "customFormulaStylesRenderer";
            }
            else if (cell.Style.Border.Top.Style != ExcelBorderStyle.None
                || cell.Style.Border.Bottom.Style != ExcelBorderStyle.None
                || cell.Style.Border.Left.Style != ExcelBorderStyle.None
                || cell.Style.Border.Right.Style != ExcelBorderStyle.None)
            {
                column.renderer = "customExcelBorderStylesRenderer";
            }
            if (string.IsNullOrEmpty(column.renderer))
                return null;

            return column;
        }
        /// <summary>
        /// 读取类Class
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="row"></param>
        /// <param name="col"></param>
        /// <returns></returns>
        private EpCellClass GetCellClass(ExcelRange cell, int row, int col)
        {
            var cellStyle = new EpCellClass
            {
                row = row - 1,
                col = col - 1,
                editor = false
            };
            //if (HorizontalMapps.ContainsKey(cell.Style.HorizontalAlignment.ToString()))
            //{
            //    cellStyle.AddClassName(HorizontalMapps[cell.Style.HorizontalAlignment.ToString()]);
            //}
            //if (VerticalMapps.ContainsKey(cell.Style.VerticalAlignment.ToString()))
            //{
            //    cellStyle.AddClassName(VerticalMapps[cell.Style.VerticalAlignment.ToString()]);
            //}
            cellStyle.AddClassName("htCenter");
            cellStyle.AddClassName("htMiddle");
            return cellStyle;
        }
        /// <summary>
        /// 读取值
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private object GetCellValue(ExcelRange cell)
        {
            //return (cell.Value is ExcelErrorValue || !string.IsNullOrEmpty(cell.Formula)) ? cell.Text : cell.Value;
            return cell.Text;
        }

        /// <summary>
        /// 读取合并
        /// </summary>
        /// <param name="sheet"></param>
        /// <returns></returns>
        private IEnumerable<EpMerge> GetMergeCells(ExcelWorksheet sheet)
        {
            var mergeCells = sheet.MergedCells.Select(merged =>
            {
                ExcelAddress address = new ExcelAddress(merged);
                return new EpMerge
                {
                    row = address.Start.Row - 1,
                    rowspan = address.End.Row - address.Start.Row + 1,
                    col = address.Start.Column - 1,
                    colspan = address.End.Column - address.Start.Column + 1,
                };
            });
            return mergeCells;
        }
    }
}