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

namespace FoodLabeling.Application.Helpers;

/// <summary>
/// Team Member 批量导入 Excel（列名与 Account Management 表格对齐，兼容常见别名）
/// </summary>
public static class TeamMemberBatchExcelHelper
{
    /// <summary>导入/下载模板表头（与 PDF 导出 Region 列对齐）</summary>
    public static readonly string[] ImportTemplateHeaders =
    {
        "Name",
        "User Name",
        "Password",
        "Email",
        "Phone",
        "Role Id",
        "Role Name",
        "Region",
        "Assigned Location Ids",
        "Status"
    };

    /// <summary>生成批量导入模板 xlsx（含 Region 可选列说明行）</summary>
    public static MemoryStream BuildImportTemplateWorkbook()
    {
        var ms = new MemoryStream();
        using var wb = new XLWorkbook();
        var ws = wb.AddWorksheet("TeamMembers");
        for (var i = 0; i < ImportTemplateHeaders.Length; i++)
        {
            ws.Cell(1, i + 1).Value = ImportTemplateHeaders[i];
            ws.Cell(1, i + 1).Style.Font.Bold = true;
        }

        ws.Cell(2, 1).Value = "John Doe";
        ws.Cell(2, 2).Value = "john.doe";
        ws.Cell(2, 3).Value = "ChangeMe123!";
        ws.Cell(2, 4).Value = "john@example.com";
        ws.Cell(2, 5).Value = "789654444";
        ws.Cell(2, 6).Value = "";
        ws.Cell(2, 7).Value = "Staff";
        ws.Cell(2, 8).Value = "";
        ws.Cell(2, 9).Value = "LOC001;LOC002";
        ws.Cell(2, 10).Value = "TRUE";

        ws.Cell(3, 7).Value = "（Role Name 与系统角色名一致；Company Admin 可只填 Region 或留空由 Company 规则处理）";
        ws.Cell(4, 8).Value = "（可选）Region 名称或 fl_group.Id，多个用 ; 分隔";
        ws.Cell(5, 9).Value = "（可选）门店 LocationCode 或 location.Id(Guid)，多个用 ; 分隔；与 Region 至少填一项";

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

    /// <summary>
    /// 从上传的 Excel 解析为创建入参列表（行号从 2 起为数据行）。
    /// </summary>
    /// <param name="stream">xlsx 流</param>
    /// <param name="maxRows">最多数据行</param>
    /// <param name="roleNameToId">角色名（忽略大小写、去空白）到角色 Id</param>
    /// <param name="defaultPassword">未填 Password 列时使用</param>
    /// <param name="parseErrors">表头或解析错误</param>
    public static List<(int RowNumber, TeamMemberCreateInputVo Input)> ParseImportWorkbook(
        Stream stream,
        int maxRows,
        IReadOnlyDictionary<string, Guid> roleNameToId,
        string defaultPassword,
        out List<TeamMemberBatchImportErrorDto> parseErrors)
    {
        parseErrors = new List<TeamMemberBatchImportErrorDto>();
        var result = new List<(int, TeamMemberCreateInputVo)>();

        if (string.IsNullOrWhiteSpace(defaultPassword))
        {
            parseErrors.Add(new TeamMemberBatchImportErrorDto
            {
                RowNumber = 0,
                Message = "未配置默认导入密码 FoodLabeling:BatchImport:TeamMemberImportDefaultPassword"
            });
            return result;
        }

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

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

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

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

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

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

                    continue;
                }

                result.Add((rowNum, input!));
            }
            catch (Exception ex)
            {
                parseErrors.Add(new TeamMemberBatchImportErrorDto
                {
                    RowNumber = rowNum,
                    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
        {
            "name" or "fullname" or "姓名" or "成员姓名" => "fullname",
            "email" or "邮箱" or "e-mail" => "email",
            "username" or "login" or "userid" or "账号" or "用户名" => "username",
            "password" or "pwd" or "密码" => "password",
            "phone" or "mobile" or "电话" or "手机" => "phone",
            "role" or "rolename" or "角色" => "rolename",
            "roleid" or "角色id" => "roleid",
            "region" or "regions" or "group" or "groupname" or "groupid" or "区域" => "regions",
            "assignedlocations" or "assignedlocationids" or "locationids" or "locationcodes" or
                "locations" or "location" or "分配门店" or "门店" or "门店id" => "locations",
            "status" or "active" or "state" or "启用" => "status",
            _ => 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().TrimStart('*');
        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 TeamMemberCreateInputVo? BuildCreateInputFromRow(
        Dictionary<string, int> colMap,
        IXLWorksheet ws,
        int rowNum,
        IReadOnlyDictionary<string, Guid> roleNameToId,
        string defaultPassword,
        out List<string> errors,
        out string? userNameHint)
    {
        errors = new List<string>();
        userNameHint = null;

        var fullName = GetCellByField(colMap, ws, rowNum, "fullname");
        var email = GetCellByField(colMap, ws, rowNum, "email");
        var userName = GetCellByField(colMap, ws, rowNum, "username");
        var password = GetCellByField(colMap, ws, rowNum, "password");
        var phoneStr = GetCellByField(colMap, ws, rowNum, "phone");
        var roleIdCell = GetCellByField(colMap, ws, rowNum, "roleid");
        var roleName = GetCellByField(colMap, ws, rowNum, "rolename");
        var regionsCell = GetCellByField(colMap, ws, rowNum, "regions");
        var locationsCell = GetCellByField(colMap, ws, rowNum, "locations");
        var statusStr = GetCellByField(colMap, ws, rowNum, "status");

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

        if (string.IsNullOrWhiteSpace(email))
        {
            errors.Add("Email 不能为空");
        }

        var login = string.IsNullOrWhiteSpace(userName) ? email.Trim() : userName.Trim();
        userNameHint = login;

        if (string.IsNullOrWhiteSpace(login))
        {
            errors.Add("登录账号不能为空（可填 UserName 列，否则使用 Email）");
        }

        var pwd = string.IsNullOrWhiteSpace(password) ? defaultPassword : password.Trim();
        if (string.IsNullOrWhiteSpace(pwd))
        {
            errors.Add("Password 不能为空且未配置默认密码");
        }

        long? phone = null;
        if (!string.IsNullOrWhiteSpace(phoneStr))
        {
            if (!long.TryParse(RegexDigitsOnly(phoneStr), NumberStyles.Integer, CultureInfo.InvariantCulture,
                    out var p))
            {
                errors.Add("Phone 格式不正确（需为数字）");
            }
            else
            {
                phone = p;
            }
        }

        Guid? roleIdResolved = null;
        if (Guid.TryParse(roleIdCell?.Trim(), out var roleGuid))
        {
            roleIdResolved = roleGuid;
        }
        else if (string.IsNullOrWhiteSpace(roleName))
        {
            errors.Add("Role Name 不能为空（或填写有效的 Role Id Guid）");
        }
        else if (!roleNameToId.TryGetValue(NormalizeRoleKey(roleName), out var rid))
        {
            errors.Add($"未找到角色「{roleName.Trim()}」，请与系统角色名称一致");
        }
        else
        {
            roleIdResolved = rid;
        }

        var regionTokens = SplitMultiValueTokens(regionsCell);
        var locationTokens = SplitMultiValueTokens(locationsCell);
        var isCompanyAdmin = TeamMemberRoleHelper.IsCompanyAdminRoleName(roleName);

        if (regionTokens.Count == 0 && locationTokens.Count == 0 && !isCompanyAdmin)
        {
            errors.Add("Region 与 Assigned Location Ids 至少填一项（均可留空时仅适用于 Company Admin 且已在 Web 端配置 Company）");
        }

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

        var state = ParseBool(statusStr, defaultValue: true);
        return new TeamMemberCreateInputVo
        {
            FullName = fullName.Trim(),
            Email = string.IsNullOrWhiteSpace(email) ? null : email.Trim(),
            UserName = login,
            Password = pwd,
            Phone = phone,
            RoleId = roleIdResolved,
            RegionIds = regionTokens,
            LocationIds = locationTokens,
            State = state
        };
    }

    public static string NormalizeRoleKey(string roleName)
    {
        return string.Concat(roleName.Trim().Where(c => !char.IsWhiteSpace(c))).ToLowerInvariant();
    }

    /// <summary>
    /// 拆分单元格为多个 token（门店/区域等；后续由服务层解析为 Id）。
    /// </summary>
    public static List<string> SplitMultiValueTokens(string cell)
    {
        if (string.IsNullOrWhiteSpace(cell))
        {
            return new List<string>();
        }

        return cell
            .Split(new[] { ';', '|', '\n', '\r', '，' }, StringSplitOptions.RemoveEmptyEntries)
            .Select(x => x.Trim())
            .Where(x => !string.IsNullOrEmpty(x))
            .ToList();
    }

    /// <summary>
    /// 兼容旧调用。
    /// </summary>
    public static List<string> SplitLocationTokens(string locationsCell) => SplitMultiValueTokens(locationsCell);

    private static string RegexDigitsOnly(string s)
    {
        return new string(s.Where(char.IsDigit).ToArray());
    }

    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, "active", StringComparison.OrdinalIgnoreCase) ||
            string.Equals(s, "是", StringComparison.Ordinal) ||
            string.Equals(s, "Y", StringComparison.OrdinalIgnoreCase) ||
            string.Equals(s, "Yes", StringComparison.OrdinalIgnoreCase))
        {
            return true;
        }

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

        return defaultValue;
    }
}