王明 / Food-Labeling-Management-Platform

using System.Globalization;
using ClosedXML.Excel;
using FoodLabeling.Application.Contracts.Dtos.Location;

namespace FoodLabeling.Application.Helpers;

/// <summary>
/// Location 批量导入/导出 Excel（列名与 Web「Location Manager」表头对齐，兼容中英文表头别名）
/// </summary>
public static class LocationBatchExcelHelper
{
    /// <summary>导出表头顺序（与模板一致）</summary>
    public static readonly string[] ExportHeaders =
    {
        "Company", "Region", "Location ID", "Location Name", "Street", "City", "State", "Country",
        "Zip Code", "Phone", "Email", "Latitude", "Longitude", "Active"
    };

    /// <summary>
    /// 将门店列表写入 xlsx 内存流。
    /// </summary>
    public static MemoryStream BuildExportWorkbook(IReadOnlyList<LocationGetListOutputDto> rows)
    {
        var ms = new MemoryStream();
        using var wb = new XLWorkbook();
        var ws = wb.AddWorksheet("Locations");
        for (var i = 0; i < ExportHeaders.Length; i++)
        {
            ws.Cell(1, i + 1).Value = ExportHeaders[i];
            ws.Cell(1, i + 1).Style.Font.Bold = true;
        }

        var r = 2;
        foreach (var x in rows)
        {
            ws.Cell(r, 1).Value = x.Partner ?? string.Empty;
            ws.Cell(r, 2).Value = x.GroupName ?? string.Empty;
            ws.Cell(r, 3).Value = x.LocationCode ?? string.Empty;
            ws.Cell(r, 4).Value = x.LocationName ?? string.Empty;
            ws.Cell(r, 5).Value = x.Street ?? string.Empty;
            ws.Cell(r, 6).Value = x.City ?? string.Empty;
            ws.Cell(r, 7).Value = x.StateCode ?? string.Empty;
            ws.Cell(r, 8).Value = x.Country ?? string.Empty;
            ws.Cell(r, 9).Value = x.ZipCode ?? string.Empty;
            ws.Cell(r, 10).Value = x.Phone ?? string.Empty;
            ws.Cell(r, 11).Value = x.Email ?? string.Empty;
            ws.Cell(r, 12).Value = x.Latitude?.ToString(CultureInfo.InvariantCulture) ?? string.Empty;
            ws.Cell(r, 13).Value = x.Longitude?.ToString(CultureInfo.InvariantCulture) ?? string.Empty;
            ws.Cell(r, 14).Value = x.State ? "TRUE" : "FALSE";
            r++;
        }

        ws.Columns().AdjustToContents();
        wb.SaveAs(ms);
        ms.Position = 0;
        return ms;
    }

    /// <summary>
    /// 从上传的 Excel 解析为创建入参列表（行号从 2 起为数据行）。
    /// </summary>
    public static List<(int RowNumber, LocationCreateInputVo Input)> ParseImportWorkbook(Stream stream,
        int maxRows,
        out List<LocationBatchImportErrorDto> parseErrors)
    {
        parseErrors = new List<LocationBatchImportErrorDto>();
        var result = new List<(int, LocationCreateInputVo)>();

        using var wb = new XLWorkbook(stream);
        var ws = wb.Worksheets.FirstOrDefault();
        if (ws is null)
        {
            parseErrors.Add(new LocationBatchImportErrorDto
            {
                RowNumber = 0,
                Message = "Excel 中无工作表"
            });
            return result;
        }

        var headerRow = ws.Row(1);
        if (!headerRow.CellsUsed().Any())
        {
            parseErrors.Add(new LocationBatchImportErrorDto { RowNumber = 1, Message = "表头为空" });
            return result;
        }

        var colMap = BuildHeaderColumnMap(headerRow);
        if (!colMap.ContainsKey("locationcode"))
        {
            parseErrors.Add(new LocationBatchImportErrorDto
            {
                RowNumber = 1,
                Message = "未找到「Location ID」列（或同义表头），请使用官方模板"
            });
            return result;
        }

        var lastRow = ws.LastRowUsed()?.RowNumber() ?? 1;
        var dataRowCount = 0;
        for (var rowNum = 2; rowNum <= lastRow; rowNum++)
        {
            if (dataRowCount >= maxRows)
            {
                parseErrors.Add(new LocationBatchImportErrorDto
                {
                    RowNumber = rowNum,
                    Message = $"已超过单次导入上限 {maxRows} 行，后续行已忽略"
                });
                break;
            }

            var locCode = GetCellByField(colMap, ws, rowNum, "locationcode");
            if (string.IsNullOrWhiteSpace(locCode) && IsRowEmpty(colMap, ws, rowNum))
            {
                continue;
            }

            dataRowCount++;
            var errPrefix = $"第 {rowNum} 行";
            try
            {
                var input = BuildCreateInputFromRow(colMap, ws, rowNum, out var rowErrs);
                if (rowErrs.Count > 0)
                {
                    foreach (var e in rowErrs)
                    {
                        parseErrors.Add(new LocationBatchImportErrorDto
                        {
                            RowNumber = rowNum,
                            LocationCode = locCode,
                            Message = $"{errPrefix}：{e}"
                        });
                    }

                    continue;
                }

                result.Add((rowNum, input!));
            }
            catch (Exception ex)
            {
                parseErrors.Add(new LocationBatchImportErrorDto
                {
                    RowNumber = rowNum,
                    LocationCode = locCode,
                    Message = $"{errPrefix}：{ex.Message}"
                });
            }
        }

        return result;
    }

    private static Dictionary<string, int> BuildHeaderColumnMap(IXLRow headerRow)
    {
        var map = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
        foreach (var cell in headerRow.CellsUsed())
        {
            var key = NormalizeHeaderKey(cell.GetString());
            if (string.IsNullOrEmpty(key))
            {
                continue;
            }

            var field = MapHeaderToField(key);
            if (field is null)
            {
                continue;
            }

            if (!map.ContainsKey(field))
            {
                map[field] = cell.Address.ColumnNumber;
            }
        }

        return map;
    }

    private static string? MapHeaderToField(string normalizedHeader)
    {
        return normalizedHeader switch
        {
            "company" or "partner" or "合作伙伴" or "公司" => "partner",
            "region" or "groupname" or "group" or "区域" or "组织" => "groupname",
            "locationid" or "locationcode" or "门店编码" or "门店id" => "locationcode",
            "locationname" or "门店名称" or "name" => "locationname",
            "street" or "地址" => "street",
            "city" or "城市" => "city",
            "state" or "statecode" or "省州" => "statecode",
            "country" or "国家" => "country",
            "zipcode" or "zip" or "邮编" or "邮政编码" => "zipcode",
            "phone" or "电话" or "手机" => "phone",
            "email" or "邮箱" => "email",
            "latitude" or "lat" or "纬度" => "latitude",
            "longitude" or "lng" or "lon" or "经度" => "longitude",
            "active" or "启用" or "状态" or "isactive" => "active",
            _ => null
        };
    }

    private static string NormalizeHeaderKey(string raw)
    {
        var s = raw.Trim();
        if (s.Length > 0 && s[0] == '\uFEFF')
        {
            s = s.TrimStart('\uFEFF');
        }

        s = s.Trim();
        return string.Concat(s.Where(c => !char.IsWhiteSpace(c))).ToLowerInvariant();
    }

    private static bool IsRowEmpty(Dictionary<string, int> colMap, IXLWorksheet ws, int rowNum)
    {
        foreach (var col in colMap.Values)
        {
            var t = ws.Cell(rowNum, col).GetString().Trim();
            if (!string.IsNullOrEmpty(t))
            {
                return false;
            }
        }

        return true;
    }

    private static string GetCellByField(Dictionary<string, int> colMap, IXLWorksheet ws, int row, string field)
    {
        if (!colMap.TryGetValue(field, out var col))
        {
            return string.Empty;
        }

        return ws.Cell(row, col).GetString().Trim();
    }

    private static LocationCreateInputVo? BuildCreateInputFromRow(Dictionary<string, int> colMap, IXLWorksheet ws,
        int rowNum, out List<string> errors)
    {
        errors = new List<string>();
        var partner = GetCellByField(colMap, ws, rowNum, "partner");
        var groupName = GetCellByField(colMap, ws, rowNum, "groupname");
        var locationCode = GetCellByField(colMap, ws, rowNum, "locationcode");
        var locationName = GetCellByField(colMap, ws, rowNum, "locationname");
        var street = GetCellByField(colMap, ws, rowNum, "street");
        var city = GetCellByField(colMap, ws, rowNum, "city");
        var stateCode = GetCellByField(colMap, ws, rowNum, "statecode");
        var country = GetCellByField(colMap, ws, rowNum, "country");
        var zipCode = GetCellByField(colMap, ws, rowNum, "zipcode");
        var phone = GetCellByField(colMap, ws, rowNum, "phone");
        var email = GetCellByField(colMap, ws, rowNum, "email");
        var latStr = GetCellByField(colMap, ws, rowNum, "latitude");
        var lngStr = GetCellByField(colMap, ws, rowNum, "longitude");
        var activeStr = GetCellByField(colMap, ws, rowNum, "active");

        if (string.IsNullOrWhiteSpace(locationCode))
        {
            errors.Add("Location ID 不能为空");
        }

        if (string.IsNullOrWhiteSpace(locationName))
        {
            errors.Add("Location Name 不能为空");
        }

        decimal? lat = null;
        if (!string.IsNullOrWhiteSpace(latStr))
        {
            if (!decimal.TryParse(latStr, NumberStyles.Any, CultureInfo.InvariantCulture, out var latVal))
            {
                errors.Add("Latitude 格式不正确");
            }
            else
            {
                lat = latVal;
            }
        }

        decimal? lng = null;
        if (!string.IsNullOrWhiteSpace(lngStr))
        {
            if (!decimal.TryParse(lngStr, NumberStyles.Any, CultureInfo.InvariantCulture, out var lngVal))
            {
                errors.Add("Longitude 格式不正确");
            }
            else
            {
                lng = lngVal;
            }
        }

        if (errors.Count > 0)
        {
            return null;
        }

        var state = ParseBool(activeStr, defaultValue: true);
        return new LocationCreateInputVo
        {
            Partner = NullIfEmpty(partner),
            GroupName = NullIfEmpty(groupName),
            LocationCode = locationCode,
            LocationName = locationName,
            Street = NullIfEmpty(street),
            City = NullIfEmpty(city),
            StateCode = NullIfEmpty(stateCode),
            Country = NullIfEmpty(country),
            ZipCode = NullIfEmpty(zipCode),
            Phone = NullIfEmpty(phone),
            Email = NullIfEmpty(email),
            Latitude = lat,
            Longitude = lng,
            State = state
        };
    }

    private static string? NullIfEmpty(string s)
    {
        var t = s.Trim();
        return string.IsNullOrEmpty(t) ? null : t;
    }

    private static bool ParseBool(string? raw, bool defaultValue)
    {
        if (string.IsNullOrWhiteSpace(raw))
        {
            return defaultValue;
        }

        var s = raw.Trim();
        if (bool.TryParse(s, out var b))
        {
            return b;
        }

        if (int.TryParse(s, out var n))
        {
            return n != 0;
        }

        if (string.Equals(s, "是", StringComparison.Ordinal) ||
            string.Equals(s, "Y", StringComparison.OrdinalIgnoreCase) ||
            string.Equals(s, "Yes", StringComparison.OrdinalIgnoreCase))
        {
            return true;
        }

        if (string.Equals(s, "否", StringComparison.Ordinal) ||
            string.Equals(s, "N", StringComparison.OrdinalIgnoreCase) ||
            string.Equals(s, "No", StringComparison.OrdinalIgnoreCase))
        {
            return false;
        }

        return defaultValue;
    }
}