using System.Collections.Generic;
using System.IO;
using OfficeOpenXml;
using MDL.Models;

namespace Common.Util
{
    /// <summary>
    /// Excel处理工具类
    /// </summary>
    public static class ExcelHelper
    {
        // Excel模板标题行
        private const string NEUTRAL_WAYBILL_COLUMN = "中性面单单号*";
        private const string MASTER_PACKAGE_COLUMN = "大包号";
        private const string BILL_OF_LADING_COLUMN = "提单号";
        
        /// <summary>
        /// 从Excel文件流中读取数据
        /// </summary>
        /// <param name="excelStream">Excel文件流</param>
        /// <returns>Excel数据行列表</returns>
        public static List<ExcelImportRow> ReadCargoDataFromExcel(Stream excelStream)
        {
            var result = new List<ExcelImportRow>();
            
            // 设置EPPlus许可证上下文（用于非商业用途）
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            
            using (var package = new ExcelPackage(excelStream))
            {
                // 获取第一个工作表
                var worksheet = package.Workbook.Worksheets[0];
                if (worksheet == null)
                {
                    return result;
                }
                
                // 获取行数和列数
                int rowCount = worksheet.Dimension.Rows;
                int colCount = worksheet.Dimension.Columns;
                
                // 查找标题行索引（默认第一行）
                int headerRowIndex = 1;
                
                // 查找各列的索引
                int neutralWaybillColumnIndex = 0;
                int masterPackageColumnIndex = 0;
                int billOfLadingColumnIndex = 0;
                
                for (int col = 1; col <= colCount; col++)
                {
                    string cellValue = worksheet.Cells[headerRowIndex, col].Text.Trim();
                    
                    if (cellValue == NEUTRAL_WAYBILL_COLUMN || cellValue == "中性面单单号")
                    {
                        neutralWaybillColumnIndex = col;
                    }
                    else if (cellValue == MASTER_PACKAGE_COLUMN || cellValue == "大包号")
                    {
                        masterPackageColumnIndex = col;
                    }
                    else if (cellValue == BILL_OF_LADING_COLUMN || cellValue == "提单号")
                    {
                        billOfLadingColumnIndex = col;
                    }
                }
                
                // 如果没有找到必要的列，返回空列表
                if (neutralWaybillColumnIndex == 0)
                {
                    return result;
                }
                
                // 从数据行开始读取（标题行+1）
                for (int row = headerRowIndex + 1; row <= rowCount; row++)
                {
                    string neutralWaybillNumber = worksheet.Cells[row, neutralWaybillColumnIndex].Text.Trim();
                    
                    // 如果中性面单单号为空，跳过该行
                    if (string.IsNullOrEmpty(neutralWaybillNumber))
                    {
                        continue;
                    }
                    
                    // 读取其他列数据
                    string masterPackageNumber = string.Empty;
                    if (masterPackageColumnIndex > 0)
                    {
                        masterPackageNumber = worksheet.Cells[row, masterPackageColumnIndex].Text.Trim();
                    }
                    
                    string billOfLadingNumber = string.Empty;
                    if (billOfLadingColumnIndex > 0)
                    {
                        billOfLadingNumber = worksheet.Cells[row, billOfLadingColumnIndex].Text.Trim();
                    }
                    
                    // 创建Excel数据行对象
                    result.Add(new ExcelImportRow
                    {
                        RowNumber = row,
                        NeutralWaybillNumber = neutralWaybillNumber,
                        MasterPackageNumber = string.IsNullOrEmpty(masterPackageNumber) ? null : masterPackageNumber,
                        BillOfLadingNumber = string.IsNullOrEmpty(billOfLadingNumber) ? null : billOfLadingNumber
                    });
                }
            }
            
            return result;
        }
        
        /// <summary>
        /// 创建Excel模板
        /// </summary>
        /// <returns>Excel模板文件字节数组</returns>
        public static byte[] CreateCargoDataTemplate()
        {
            // 设置EPPlus许可证上下文
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            
            using (var package = new ExcelPackage())
            {
                // 创建新工作表
                var worksheet = package.Workbook.Worksheets.Add("货物数据导入模板");
                
                // 设置标题行
                worksheet.Cells[1, 1].Value = NEUTRAL_WAYBILL_COLUMN;
                worksheet.Cells[1, 2].Value = MASTER_PACKAGE_COLUMN;
                worksheet.Cells[1, 3].Value = BILL_OF_LADING_COLUMN;
                
                // 设置列宽
                worksheet.Column(1).Width = 20;
                worksheet.Column(2).Width = 20;
                worksheet.Column(3).Width = 20;
                
                // 设置标题行样式
                using (var headerRange = worksheet.Cells[1, 1, 1, 3])
                {
                    headerRange.Style.Font.Bold = true;
                    headerRange.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                    headerRange.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray);
                    headerRange.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                }
                
                // 添加示例数据
                worksheet.Cells[2, 1].Value = "1234567890";
                worksheet.Cells[2, 2].Value = "MP12345";
                worksheet.Cells[2, 3].Value = "BL98765";
                
                worksheet.Cells[3, 1].Value = "0987654321";
                worksheet.Cells[3, 2].Value = "MP67890";
                worksheet.Cells[3, 3].Value = "";
                
                worksheet.Cells[4, 1].Value = "5678901234";
                worksheet.Cells[4, 2].Value = "";
                worksheet.Cells[4, 3].Value = "BL45678";
                
                // 添加注释
                worksheet.Cells[2, 1].AddComment("示例中性面单单号（必填）", "System");
                worksheet.Cells[2, 2].AddComment("示例大包号（可选，与提单号不能同时为空）", "System");
                worksheet.Cells[2, 3].AddComment("示例提单号（可选，与大包号不能同时为空）", "System");
                
                // 保存到字节数组
                return package.GetAsByteArray();
            }
        }
        
        /// <summary>
        /// 验证Excel数据行
        /// </summary>
        /// <param name="excelRow">Excel数据行</param>
        /// <returns>验证结果（true为通过，false为失败）</returns>
        public static bool ValidateExcelRow(ExcelImportRow excelRow)
        {
            // 中性面单单号不能为空
            if (string.IsNullOrEmpty(excelRow.NeutralWaybillNumber))
            {
                return false;
            }
            
            // 大包号和提单号不能同时为空
            if (string.IsNullOrEmpty(excelRow.MasterPackageNumber) && string.IsNullOrEmpty(excelRow.BillOfLadingNumber))
            {
                return false;
            }
            
            return true;
        }
    }
}