using NPOI.SS.UserModel;
using OfficeOpenXml;
using Performance.DtoModels;
using Performance.EntityModels;
using Performance.Infrastructure;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;

namespace Performance.Services.ExtractExcelService
{
    public class ExtractHelper
    {
        public static string GetExtractFile(int hospitalId, ref string newFilePath, string allotFilePath = "")
        {
            string originalPath = string.IsNullOrEmpty(allotFilePath)
                ? Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Template", "医院绩效模板.xlsx")
                : allotFilePath;
            CloseAutoFilter(originalPath);
            var (tempPath, filePath) = CopyOriginalFile(hospitalId, originalPath);
            newFilePath = filePath;
            return tempPath;
        }

        public static string GetExtractFile(int hospitalId, string prefix = "绩效提取数据")
        {
            var dpath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Files", $"{hospitalId}", "autoextract");
            FileHelper.CreateDirectory(dpath);
            return Path.Combine(dpath, $"{prefix}{DateTime.Now.ToString("yyyyMMddHHmmssfff")}.xlsx");
        }

        private static (string TempPath, string FilePath) CopyOriginalFile(int hospitalId, string originalPath)
        {
            var ext = FileHelper.GetExtension(originalPath);
            var dpath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Files", $"{hospitalId}", "autoextract");
            FileHelper.CreateDirectory(dpath);
            string tempPath = Path.Combine(dpath, $"Template{DateTime.Now.ToString("yyyyMMddHHmmssfff")}{ext}");
            FileHelper.Copy(originalPath, tempPath);
            string filePath = Path.Combine(dpath, $"绩效提取数据{DateTime.Now.ToString("yyyyMMddHHmmssfff")}{ext}");
            return (tempPath, filePath);
        }

        public static void CreateNotExistSheet(List<ex_module> modulesList, IWorkbook workbook)
        {
            SortedDictionary<string, int> pairs = new SortedDictionary<string, int>();
            for (int i = 0; i < workbook.NumberOfSheets; i++)
            {
                var sheetname = workbook.GetSheetAt(i).SheetName;
                if (!pairs.Keys.Contains(sheetname))
                    pairs.Add(workbook.GetSheetAt(i).SheetName, i);
            }

            foreach (var module in modulesList.Where(t => t.SheetType == (int)SheetType.Income)?.OrderBy(t => t.ModuleName))
            {
                var sheet = workbook.GetSheet(module.ModuleName) ?? workbook.GetSheet(module.ModuleName.NoBlank());
                if (sheet == null)
                {
                    string[] keyArray = new string[] { "开单", "执行" };
                    if (keyArray.Any(key => module.ModuleName.Contains(key)))
                    {
                        var item = pairs.Where(t => t.Key.StartsWith("1.")).OrderByDescending(t => t.Key).First();
                        var copysheet = workbook.GetSheet(item.Key);
                        var newSheet = copysheet.CopySheet(module.ModuleName, true);
                        workbook.SetSheetOrder(newSheet.SheetName, workbook.NumberOfSheets - 1);
                    }
                }
            }

            foreach (var module in modulesList.Where(t => new int[] { (int)SheetType.OtherWorkload, (int)SheetType.Assess }
            .Contains(t.SheetType.Value))?.OrderBy(t => t.ModuleName))
            {
                var sheet = workbook.GetSheet(module.ModuleName) ?? workbook.GetSheet(module.ModuleName.NoBlank());
                if (sheet == null)
                {
                    //string key = "工作量";
                    //if (module.ModuleName.Contains(key))
                    //{
                    var item = pairs.Where(t => t.Key.StartsWith("3.")).OrderByDescending(t => t.Key).First();
                    var copysheet = workbook.GetSheet(item.Key);
                    var newSheet = copysheet.CopySheet(module.ModuleName, true);
                    workbook.SetSheetOrder(newSheet.SheetName, workbook.NumberOfSheets - 1);

                    var point = PerSheetDataFactory.GetDataRead(SheetType.Workload)?.Point;
                    ClearSheetTemplate(newSheet, point, (SheetType)module.SheetType);
                    //}
                }
            }
        }

        public static void ClearSheetPartialData(ISheet sheet, PerSheetPoint point, SheetType sheetType)
        {
            if (sheet == null)
                return;

            for (int i = point.DataFirstRowNum.Value; i < sheet.LastRowNum + 1; i++)
            {
                var row = sheet.GetRow(i);
                if (row != null)
                {
                    row.ZeroHeight = false; //取消隐藏行
                    int dataFirstCellRowNum = point.DataFirstCellNum.Value;
                    //跳过核算单元和科室
                    for (int j = dataFirstCellRowNum; j < row.LastCellNum; j++)
                    {
                        var cell = row.GetCell(j);
                        if (cell != null && (cell.CellType != CellType.Formula || sheetType == SheetType.Income))
                        {
                            cell.RemoveCellComment();
                            row.RemoveCell(cell);
                        }
                    }
                }
            }
        }
        public static void ClearSheetTemplate(ISheet sheet, PerSheetPoint point, SheetType sheetType)
        {
            if (sheet == null)
                return;

            for (int i = point.HeaderFirstRowNum.Value; i < sheet.LastRowNum + 1; i++)
            {
                var row = sheet.GetRow(i);
                if (row != null)
                {
                    row.ZeroHeight = false; //取消隐藏行
                    int dataFirstCellRowNum = point.DataFirstCellNum.Value;
                    //跳过核算单元和科室
                    for (int j = dataFirstCellRowNum; j < row.LastCellNum; j++)
                    {
                        var cell = row.GetCell(j);
                        if (cell != null)
                        {
                            cell.RemoveCellComment(); //删除批注
                            row.RemoveCell(cell);
                        }
                    }
                }
            }

            if (!new SheetType[] { SheetType.Workload, SheetType.OtherWorkload }.Contains(sheetType))
            {
                if (sheet.NumMergedRegions > 0)
                {
                    for (int mergedIndex = sheet.NumMergedRegions - 1; mergedIndex >= 0; mergedIndex--)
                    {
                        sheet.RemoveMergedRegion(mergedIndex);
                    }
                }

                var row = sheet.GetRow(0);
                if (row != null) sheet.RemoveRow(row);
                sheet.ShiftRows(point.DataFirstRowNum.Value, sheet.LastRowNum + 1, -1);
            }
        }
        
        public static void CloseAutoFilter(string path)
        {
            try
            {
                var fileInfo = new FileInfo(path);
                using (ExcelPackage package = new ExcelPackage(fileInfo))
                {
                    ExcelWorkbook workbook = package.Workbook;

                    if (workbook == null) return;
                    foreach (var sheet in workbook.Worksheets)
                    {
                        if (sheet.AutoFilterAddress != null)
                        {
                            sheet.Cells[sheet.AutoFilterAddress.Address].AutoFilter = false;
                        }
                    }
                    package.Save();
                }
            }
            catch (Exception)
            {
            }
        }

        /// <summary>
        /// 判断文件是否是xlsx文件
        /// </summary>
        /// <param name="filename">文件名称、文件扩展名</param>
        /// <returns></returns>
        public static bool IsXlsxFile(string filename)
        {
            if (string.IsNullOrEmpty(filename)) return false;

            string ext = filename;
            if (ext.Contains("."))
            {
                int start = filename.LastIndexOf('.') + 1;
                ext = filename.Substring(start, filename.Length - start);
            }
            return ext.ToLower() == ExcelVersion.xlsx.ToString().ToLower();
        }

    }
}
