using DevExpress.CodeParser;
using HStation.Assets;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.IO;

namespace HStation.WinFrmUI
{
    public class TranslationExcelHelper
    {
        public static string ParseTranslationExcel(string fileName, out List<HStation.Vmo.AssetsTranslationMainVmo> list)
        {
            list = new List<Vmo.AssetsTranslationMainVmo>();
            if (!File.Exists(fileName))
                return "文件不存在";
            int line = 0;

            IWorkbook workbook;
            using (FileStream file = new(fileName, FileMode.Open, FileAccess.Read))
            {
                if (Path.GetExtension(fileName).ToLower() == ".xls")
                {
                    workbook = new HSSFWorkbook(file);
                }
                else if (Path.GetExtension(fileName).ToLower() == ".xlsx")
                {
                    workbook = new XSSFWorkbook(file);
                }
                else
                {
                    return "不支持的文件格式";
                }
            }

            ISheet sheet_ql = workbook.GetSheet("sheet1");
            if (sheet_ql == null)
            {
                return "表格不符合格式";
            }
            //标题行
            int title_line_index = 0;
            //名称列
            int col_index_name = 0;
            //类型列
            int col_index_type = 1;
            //材料列
            int col_index_materials = 2;
            //系数列
            int col_index_coefficient = 3;
            //上游直径列
            int col_index_start_diameter = 4;

            //下游直径列
            int col_index_end_diameter = 5;

            //备注列
            int col_index_descriptions = 6;

            var row_title = sheet_ql.GetRow(title_line_index);
            if (row_title == null)
            {
                return ("第一行第一列不能空");
            }

            //开始读取的行
            int start_line = title_line_index + 1;
            var cell_0 = row_title.GetCell(0);
            if (cell_0 == null)
            {
                return ("无法读取表头文件");
            }

            NPOI.SS.UserModel.IRow row_temp = null;
            NPOI.SS.UserModel.ICell cell;

            for (line = start_line; line < 1000; line++)
            {
                row_temp = sheet_ql.GetRow(line);
                if (row_temp == null)
                    break;
                var name = row_temp.GetCell(col_index_name);
                if (name == null)
                    break;
                var type = row_temp.GetCell(col_index_type);
                eTranslationType? eTranslationType = null;
                switch (type.StringCellValue)
                {
                    case "渐缩变径":
                        eTranslationType = HStation.Assets.eTranslationType.GradualContraction;
                        break;

                    case "偏心渐缩":
                        eTranslationType = HStation.Assets.eTranslationType.EccentricContraction;
                        break;

                    case "偏心渐扩":
                        eTranslationType = HStation.Assets.eTranslationType.EccentricExpansion;
                        break;

                    case "渐扩变径":
                        eTranslationType = HStation.Assets.eTranslationType.GradualExpansion;
                        break;
                }

                var material = row_temp.GetCell(col_index_materials);
                if (material == null)
                    break;
                cell = row_temp.GetCell(col_index_coefficient);
                if (cell == null)
                    break;
                if (!ParseCellValue(cell, out double coefficient))
                    break;
                if (coefficient < 0)
                    break;
                cell = row_temp.GetCell(col_index_start_diameter);
                if (cell == null)
                    break;
                if (!ParseCellValue(cell, out double start_diameter))
                    break;
                if (start_diameter < 0)
                    break;
                cell = row_temp.GetCell(col_index_end_diameter);
                if (cell == null)
                    break;
                if (!ParseCellValue(cell, out double end_diameter))
                    break;
                if (end_diameter < 0)
                    break;
                list.Add(new Vmo.AssetsTranslationMainVmo
                {
                    Name = name.StringCellValue,
                    TranslationType = eTranslationType,
                    Material = material.StringCellValue,
                    MinorLoss = coefficient,
                    StartDiameter = start_diameter,
                    EndDiameter = end_diameter,
                    Roughness = 120,
                    Description = row_temp.GetCell(col_index_descriptions)?.StringCellValue,
                });
            }

            if (list == null || !list.Any())
            {
                return ("表格不符合格式");
            }
            return "";
        }

        private static bool ParseCellValue(NPOI.SS.UserModel.ICell cell, out double cell_value)
        {
            cell_value = 0;
            if (cell.CellType == NPOI.SS.UserModel.CellType.Numeric)
            {
                cell_value = cell.NumericCellValue;
                return true;
            }

            if (cell.CellType == NPOI.SS.UserModel.CellType.String)
            {
                if (double.TryParse(cell.StringCellValue, out double value))
                {
                    cell_value = value;
                    return true;
                }
            }

            return false;
        }

        //转换可以为空
        private static bool ParseCellValue(NPOI.SS.UserModel.ICell cell, out double? cell_value)
        {
            cell_value = null;
            if (cell.CellType == NPOI.SS.UserModel.CellType.Numeric)
            {
                cell_value = cell.NumericCellValue;
                return true;
            }

            if (cell.CellType == NPOI.SS.UserModel.CellType.String)
            {
                if (double.TryParse(cell.StringCellValue, out double value))
                {
                    cell_value = value;
                    return true;
                }
                else if (cell.StringCellValue == null)
                {
                    return true;
                }
            }
            if (cell.CellType == NPOI.SS.UserModel.CellType.Blank)
            {
                return true;
            }

            return false;
        }

        private static bool ParseCellValue(NPOI.SS.UserModel.ICell cell, out int cell_value)
        {
            cell_value = 0;
            if (cell.CellType == NPOI.SS.UserModel.CellType.Numeric)
            {
                cell_value = (int)cell.NumericCellValue;
                return true;
            }

            if (cell.CellType == NPOI.SS.UserModel.CellType.String)
            {
                if (int.TryParse(cell.StringCellValue, out int value))
                {
                    cell_value = value;
                    return true;
                }
            }

            return false;
        }

        public static void ExportTranslationTemplate(string file_path)
        {
            NPOI.HSSF.UserModel.HSSFWorkbook theBook = new NPOI.HSSF.UserModel.HSSFWorkbook();

            var theSheet1 = theBook.CreateSheet("Sheet1");
            NPOI.SS.UserModel.IRow rowTile1 = theSheet1.CreateRow(0);
            rowTile1.CreateCell(0).SetCellValue("名称");
            rowTile1.CreateCell(1).SetCellValue("类型");
            rowTile1.CreateCell(2).SetCellValue("材料");
            rowTile1.CreateCell(3).SetCellValue("局阻系数");
            rowTile1.CreateCell(4).SetCellValue("上游直径");
            rowTile1.CreateCell(5).SetCellValue("下游直径");
            rowTile1.CreateCell(6).SetCellValue("备注");
            theSheet1.ForceFormulaRecalculation = true;

            using FileStream fs = File.OpenWrite(file_path);
            theBook.Write(fs);
        }
    }
}