AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/DataPlatform/Wms/CreatePickBillService.cs

using System.Net.Sockets;
using System.Text.Json;
using Admin.NET.Plugin.AiDOP.DataPlatform.Executors;
using Admin.NET.Plugin.AiDOP.DataPlatform.Sequence;
using Admin.NET.Plugin.AiDOP.Entity.DataPlatform;
using Admin.NET.Plugin.AiDOP.WorkOrder;
using Microsoft.Extensions.Logging;
using SqlSugar;

namespace Admin.NET.Plugin.AiDOP.DataPlatform.Wms;

/// <summary>
/// WP4 / WP10 · 下达建领料单。
/// S1：165 事务、幂等三分类、重放收敛、本库+Outbox 原子、业务幂等键、工序走 Outbox。
/// S2：统一返回契约、错误码、D11-A fail-fast。
/// </summary>
[ApiDescriptionSettings(Order = 327, Description = "下达建领料单")]
[Route("api/aidop/wms-pick")]
[AllowAnonymous]
[NonUnify]
public class CreatePickBillService : IDynamicApiController, ITransient
{
    public const string TargetSource = "DOPDEMORQ_SQLSERVER";
    private const string NbrTypeBill = "SM";
    private const string NbrTypeAllocate = "sm";

    private readonly ISqlSugarClient _db;
    private readonly MdpSourceScopeFactory _scopeFactory;
    private readonly INbrSequenceService _seq;
    private readonly KeeperResolveService _keeper;
    private readonly MdpOutboxEnqueueService _enqueue;
    private readonly MdpOutboxWakeSignal _wake;
    private readonly WorkOrdMesPushService _workOrdMesPush;
    private readonly ILogger<CreatePickBillService> _logger;

    public CreatePickBillService(
        ISqlSugarClient db,
        MdpSourceScopeFactory scopeFactory,
        INbrSequenceService seq,
        KeeperResolveService keeper,
        MdpOutboxEnqueueService enqueue,
        MdpOutboxWakeSignal wake,
        WorkOrdMesPushService workOrdMesPush,
        ILogger<CreatePickBillService> logger)
    {
        _db = db;
        _scopeFactory = scopeFactory;
        _seq = seq;
        _keeper = keeper;
        _enqueue = enqueue;
        _wake = wake;
        _workOrdMesPush = workOrdMesPush;
        _logger = logger;
    }

    public sealed class CreatePickBillInput
    {
        public string? WorkOrd { get; set; }
        public string Domain { get; set; } = "8010";
        public string UserAccount { get; set; } = "aidop";
        public long TenantId { get; set; }
    }

    public sealed class PickBillItemResult
    {
        public string WorkOrd { get; set; } = "";
        public string Result { get; set; } = ""; // created | existed | skipped | failed
        public string? Nbr { get; set; }
        public int? DetailCount { get; set; }
        public string? ReasonCode { get; set; }
        public string? Reason { get; set; }
        public string? Hint { get; set; }
    }

    public sealed class PickBillSummary
    {
        public int Requested { get; set; }
        public int Created { get; set; }
        public int Existed { get; set; }
        public int Skipped { get; set; }
        public int Failed { get; set; }
    }

    public sealed class PickBillWriteback
    {
        public int Enqueued { get; set; }
        public string State { get; set; } = "none";
        public bool? Reconciled { get; set; }
    }

    public sealed class PickBillResponse
    {
        public bool Ok { get; set; }
        public string Code { get; set; } = "FAILED";
        public PickBillSummary Summary { get; set; } = new();
        public List<PickBillItemResult> Items { get; set; } = new();
        public PickBillWriteback Writeback { get; set; } = new();
        public string? Trace { get; set; }
        public string? Message { get; set; }
    }

    /// <summary>WP9 · 单工单建单，映射为 <see cref="PickBillResult"/>（供 IPickBillCreator / Mes 路径）。</summary>
    public async Task<PickBillResult> CreateForOneAsResultAsync(
        long tenantId,
        string domain,
        string workOrd,
        string account,
        CancellationToken ct = default)
    {
        if (string.IsNullOrWhiteSpace(workOrd))
            throw Oops.Oh("工单号不能为空");

        var resp = await Create(new CreatePickBillInput
        {
            WorkOrd = workOrd.Trim(),
            Domain = string.IsNullOrWhiteSpace(domain) ? "8010" : domain.Trim(),
            UserAccount = string.IsNullOrWhiteSpace(account) ? "aidop" : account.Trim(),
            TenantId = tenantId
        }, ct);

        var item = resp.Items.FirstOrDefault(i =>
            string.Equals(i.WorkOrd, workOrd.Trim(), StringComparison.OrdinalIgnoreCase))
            ?? resp.Items.FirstOrDefault();

        if (item == null)
            throw Oops.Oh(PickBillUserMessages.ForPickCreateFailure(null, resp.Message ?? "没有可建领料的工单", "请刷新后重试。"));

        if (item.Result is "failed" or "skipped")
            throw Oops.Oh(PickBillUserMessages.ForPickCreateFailure(item.ReasonCode, item.Reason, item.Hint));

        return new PickBillResult
        {
            Nbr = item.Nbr ?? "",
            Created = item.Result == "created",
            LineCount = item.DetailCount ?? 0,
            Message = item.Result == "existed" ? "领料单已存在" : (resp.Message ?? "领料单已生成"),
            Source = "Mes"
        };
    }

    [DisplayName("下达生成领料单")]
    [HttpPost("create")]
    public async Task<PickBillResponse> Create([FromBody] CreatePickBillInput input, CancellationToken ct = default)
    {
        if (input == null)
            return FailAll("body 不能为空", "MES_WRITE_FAILED");

        var domain = string.IsNullOrWhiteSpace(input.Domain) ? "8010" : input.Domain.Trim();
        var user = string.IsNullOrWhiteSpace(input.UserAccount) ? "aidop" : input.UserAccount.Trim();
        var trace = $"pick|{domain}|{DateTime.Now:yyyyMMddHHmmss}|{Guid.NewGuid():N}"[..48];

        var masters = await LoadCandidatesAsync(domain, input.WorkOrd, ct);
        if (masters.Count == 0)
            return BuildResponse(new List<PickBillItemResult>(), 0, 0, false, trace, "没有需要下达的工单。");

        var items = new List<PickBillItemResult>();
        var toCreate = new List<WorkOrdMaster>();
        var toReconcile = new List<(WorkOrdMaster Wo, string Nbr)>();
        int enqueued = 0;
        bool reconciled = false;

        ISqlSugarClient ss;
        try
        {
            ss = await _scopeFactory.GetScopeAsync(TargetSource, ct);
        }
        catch (Exception ex)
        {
            var code = ClassifyMesError(ex);
            return FailAllBatch(masters, code, DescribeMes(code, ex), HintFor(code), trace, ex, domain);
        }

        Dictionary<string, (string Nbr, bool HasDetail)> probe;
        try
        {
            probe = await ProbeExistingAsync(ss, domain, masters.Select(m => m.WorkOrd!).Distinct().ToList(), ct);
        }
        catch (Exception ex)
        {
            var code = ClassifyMesError(ex);
            return FailAllBatch(masters, code, DescribeMes(code, ex), HintFor(code), trace, ex, domain);
        }

        var workOrds = masters.Select(m => m.WorkOrd!).Distinct().ToList();
        var details = await _db.Queryable<WorkOrdDetail>()
            .Where(d => d.IsActive && workOrds.Contains(d.WorkOrd!))
            .ToListAsync(ct);
        var detailsByWo = details.GroupBy(d => d.WorkOrd!, StringComparer.OrdinalIgnoreCase)
            .ToDictionary(g => g.Key, g => g.ToList(), StringComparer.OrdinalIgnoreCase);

        var itemNums = details.Select(d => d.ItemNum!).Where(x => !string.IsNullOrWhiteSpace(x)).Distinct().ToList();
        var itemMap = await LoadItemsAsync(itemNums, ct);

        KeeperResolveService.KeeperResolveResult? keeperResult = null;
        if (itemNums.Count > 0)
        {
            try
            {
                keeperResult = await _keeper.ResolveByItemsAsync(domain, itemNums, ct);
            }
            catch (Exception ex)
            {
                var code = ClassifyMesError(ex);
                return FailAllBatch(masters, code, DescribeMes(code, ex), HintFor(code), trace, ex, domain);
            }
        }
        else
        {
            keeperResult = new KeeperResolveService.KeeperResolveResult();
        }

        var keeperByItem = keeperResult.Hits
            .GroupBy(h => h.ItemNum, StringComparer.OrdinalIgnoreCase)
            .ToDictionary(g => g.Key, g => g.First(), StringComparer.OrdinalIgnoreCase);

        // 按工单分类（保管员硬校验在写库前对「待建」工单整体执行）
        foreach (var wo in masters)
        {
            var woNo = wo.WorkOrd ?? "";
            if (string.IsNullOrWhiteSpace(woNo)) continue;

            var st = (wo.Status ?? "").ToLowerInvariant();
            if (st is "c" or "w")
            {
                items.Add(new PickBillItemResult
                {
                    WorkOrd = woNo, Result = "skipped", ReasonCode = "WO_STATE_INVALID",
                    Reason = "工单状态不允许下达", Hint = "检查工单状态后重试"
                });
                continue;
            }

            if (!detailsByWo.TryGetValue(woNo, out var wods) || wods.Count == 0)
            {
                items.Add(new PickBillItemResult
                {
                    WorkOrd = woNo, Result = "skipped", ReasonCode = "NO_DETAIL",
                    Reason = "工单无物料明细", Hint = "请先在工单明细中维护物料后重试"
                });
                continue;
            }

            // WP4：下达/建领料前要求 165 已有完整工单（评审推送）
            try
            {
                var ready = await _workOrdMesPush.ProbeReadyOnMesAsync(domain, woNo, ct);
                if (!ready.Ready)
                {
                    items.Add(new PickBillItemResult
                    {
                        WorkOrd = woNo, Result = "failed", ReasonCode = "WO_MES_NOT_READY",
                        Reason = $"MES 端工单未就绪（头={ready.MasterCnt},明细={ready.DetailCnt},工序={ready.RoutingCnt}）",
                        Hint = "请先订单评审/计划刷新推送，或 POST /api/aidop/wms-workord/push"
                    });
                    continue;
                }
            }
            catch (Exception ex)
            {
                var code = ClassifyMesError(ex);
                items.Add(new PickBillItemResult
                {
                    WorkOrd = woNo, Result = "failed", ReasonCode = code,
                    Reason = DescribeMes(code, ex), Hint = HintFor(code)
                });
                continue;
            }

            if (probe.TryGetValue(woNo, out var p) && p.HasDetail)
            {
                toReconcile.Add((wo, p.Nbr));
                continue;
            }

            // 孤儿：有主无明细 → 允许重建
            toCreate.Add(wo);
        }

        // 保管员校验：对待建工单涉及的物料
        if (toCreate.Count > 0)
        {
            var createItemNums = toCreate
                .SelectMany(w => detailsByWo.TryGetValue(w.WorkOrd!, out var ds) ? ds : new List<WorkOrdDetail>())
                .Select(d => d.ItemNum!)
                .Where(x => !string.IsNullOrWhiteSpace(x))
                .Distinct(StringComparer.OrdinalIgnoreCase)
                .ToList();

            var createKeeper = createItemNums.Count == 0
                ? new KeeperResolveService.KeeperResolveResult()
                : await FilterKeeperForItems(keeperResult, createItemNums);

            if (createKeeper.UnsafeItems.Count > 0)
            {
                foreach (var wo in toCreate)
                {
                    items.Add(new PickBillItemResult
                    {
                        WorkOrd = wo.WorkOrd!, Result = "failed", ReasonCode = "KEEPER_RULE_UNSAFE",
                        Reason = "物料号或职责区间含约定外字符，区间匹配口径不可靠",
                        Hint = "请人工指派保管员；见 WP7 §3.6"
                    });
                }
                toCreate.Clear();
            }
            else if (createKeeper.EmptyLocationItems.Count > 0)
            {
                foreach (var wo in toCreate)
                {
                    items.Add(new PickBillItemResult
                    {
                        WorkOrd = wo.WorkOrd!, Result = "failed", ReasonCode = "NO_KEEPER",
                        Reason = "物料保管员的库位不能为空：" + string.Join(",", createKeeper.EmptyLocationItems),
                        Hint = "见 WP7 §3，补齐 EmpWorkDutyMaster.Location"
                    });
                }
                toCreate.Clear();
            }
            else if (createKeeper.MissingItems.Count > 0)
            {
                foreach (var wo in toCreate)
                {
                    items.Add(new PickBillItemResult
                    {
                        WorkOrd = wo.WorkOrd!, Result = "failed", ReasonCode = "NO_KEEPER",
                        Reason = "以下物料未设置保管员：" + string.Join(",", createKeeper.MissingItems),
                        Hint = "见 WP7 §3，补齐 EmpWorkDutyMaster"
                    });
                }
                toCreate.Clear();
            }
        }

        var draftMasters = new List<DraftNbrMaster>();
        var draftDetails = new List<DraftNbrDetail>();
        var orphanNotes = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase);

        foreach (var wo in toCreate)
        {
            var wods = detailsByWo[wo.WorkOrd!];
            var invalidItems = wods.Where(d => !string.IsNullOrWhiteSpace(d.ItemNum) && !itemMap.ContainsKey(d.ItemNum!))
                .Select(d => d.ItemNum!).Distinct().ToList();
            if (invalidItems.Count > 0 && wods.All(d => string.IsNullOrWhiteSpace(d.ItemNum) || !itemMap.ContainsKey(d.ItemNum!)))
            {
                items.Add(new PickBillItemResult
                {
                    WorkOrd = wo.WorkOrd!, Result = "skipped", ReasonCode = "ITEM_INVALID",
                    Reason = "物料主数据缺失", Hint = "补 ItemMaster"
                });
                continue;
            }

            var before = draftMasters.Count;
            BuildOne(wo, wods, itemMap, keeperByItem, user, draftMasters, draftDetails);
            if (draftMasters.Count == before)
            {
                items.Add(new PickBillItemResult
                {
                    WorkOrd = wo.WorkOrd!, Result = "skipped", ReasonCode = "ITEM_INVALID",
                    Reason = "未生成有效明细", Hint = "检查工单明细与物料主数据"
                });
                continue;
            }

            if (probe.TryGetValue(wo.WorkOrd!, out var orphan) && !orphan.HasDetail)
                orphanNotes[wo.WorkOrd!] = orphan.Nbr;
        }

        if (draftMasters.Count > 0)
        {
            try
            {
                var nbrs = await _seq.AllocateAsync(NbrTypeAllocate, draftMasters.Count, domain, user, ct);
                if (nbrs.Count < draftMasters.Count)
                {
                    foreach (var m in draftMasters)
                    {
                        items.Add(new PickBillItemResult
                        {
                            WorkOrd = m.WorkOrd, Result = "failed", ReasonCode = "SEQ_FAILED",
                            Reason = $"取号不足：需要 {draftMasters.Count}，得到 {nbrs.Count}",
                            Hint = "联系运维查 NbrDayInfo"
                        });
                    }
                    draftMasters.Clear();
                    draftDetails.Clear();
                }
                else
                {
                    for (var i = 0; i < draftMasters.Count; i++)
                    {
                        var nbr = nbrs[i];
                        draftMasters[i].Nbr = nbr;
                        var woTail = Tail(draftMasters[i].WorkOrd);
                        draftMasters[i].Address = woTail;
                        draftMasters[i].Ufld1 = woTail;
                        foreach (var d in draftDetails.Where(x => x.TempKey == draftMasters[i].TempKey))
                        {
                            d.Nbr = nbr;
                            d.Address = woTail;
                            d.OrdNbr = woTail;
                            d.ERPfld1 = woTail;
                            d.OrdLine = d.Line;
                            d.ERPfld2 = d.Line.ToString();
                        }
                    }

                    await InsertNbrOn165Async(ss, draftMasters, draftDetails, ct);

                    foreach (var m in draftMasters)
                    {
                        var detailCount = draftDetails.Count(d => d.Nbr == m.Nbr);
                        if (orphanNotes.TryGetValue(m.WorkOrd, out var orphanNbr))
                        {
                            items.Add(new PickBillItemResult
                            {
                                WorkOrd = m.WorkOrd, Result = "created", Nbr = m.Nbr, DetailCount = detailCount,
                                ReasonCode = "ORPHAN_DETECTED",
                                Reason = $"检出孤儿单 {orphanNbr}，已用新号重建",
                                Hint = $"孤儿单号 {orphanNbr} 仍保留，交 DBA 决定是否清理"
                            });
                        }
                        else
                        {
                            items.Add(new PickBillItemResult
                            {
                                WorkOrd = m.WorkOrd, Result = "created", Nbr = m.Nbr, DetailCount = detailCount
                            });
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                var code = ClassifyMesError(ex);
                // D11-A：MES 不可达整批 fail-fast，不开启本库事务
                if (code == "MES_UNREACHABLE")
                    return FailAllBatch(masters, code, DescribeMes(code, ex), HintFor(code), trace, ex, domain);

                _logger.LogError(
                    ex,
                    "[CreatePickBill] MES write failed code={Code} domain={Domain} trace={Trace} draftCount={Count}",
                    code, domain, trace, draftMasters.Count);

                foreach (var m in draftMasters)
                {
                    items.Add(new PickBillItemResult
                    {
                        WorkOrd = m.WorkOrd, Result = "failed", ReasonCode = code,
                        Reason = DescribeMes(code, ex), Hint = HintFor(code)
                    });
                }
                draftMasters.Clear();
            }
        }

        // 本库状态 + Outbox（新建 + 重放补做）
        var localTargets = new List<WorkOrdMaster>();
        localTargets.AddRange(draftMasters.Select(m => masters.First(x =>
            string.Equals(x.WorkOrd, m.WorkOrd, StringComparison.OrdinalIgnoreCase))));
        localTargets.AddRange(toReconcile.Select(x => x.Wo));
        localTargets = localTargets
            .GroupBy(x => x.WorkOrd!, StringComparer.OrdinalIgnoreCase)
            .Select(g => g.First())
            .ToList();

        if (localTargets.Count > 0)
        {
            var pendingPulse = false;
            try
            {
                await _db.Ado.BeginTranAsync();
                try
                {
                    var now = DateTime.Now;
                    var touchedOrds = localTargets.Select(x => x.WorkOrd!).ToList();

                    foreach (var wo in localTargets)
                    {
                        var batch = Tail(wo.WorkOrd!);
                        await _db.Updateable<WorkOrdMaster>()
                            .SetColumns(m => new WorkOrdMaster
                            {
                                Status = "r",
                                Batch = batch,
                                UpdateUser = user,
                                UpdateTime = now
                            })
                            .Where(m => m.WorkOrd == wo.WorkOrd)
                            .ExecuteCommandAsync(ct);
                    }

                    await _db.Updateable<WorkOrdRouting>()
                        .SetColumns(r => new WorkOrdRouting { Status = "r", UpdateUser = user, UpdateTime = now })
                        .Where(r => touchedOrds.Contains(r.WorkOrd!) && r.IsActive
                                    && r.Status != null && r.Status.ToLower() != "c" && r.Status.ToUpper() != "W")
                        .ExecuteCommandAsync(ct);

                    foreach (var wo in localTargets)
                    {
                        var batch = Tail(wo.WorkOrd!);
                        var insert = BuildWorkOrdInsert(domain, wo.WorkOrd!, batch, user, now, wo);
                        var update = new Dictionary<string, object?>
                        {
                            ["Status"] = "r",
                            ["Batch"] = batch,
                            ["UpdateUser"] = Trunc(user, 24),
                            ["UpdateTime"] = now.ToString("yyyy-MM-dd HH:mm:ss")
                        };
                        if (await EnqueueAsync(input.TenantId, $"pick|{domain}|{wo.WorkOrd}|wom", "PICK_WOM_UPSERT", new
                        {
                            op = "UPSERT",
                            table = "WorkOrdMaster",
                            keys = new Dictionary<string, object?> { ["Domain"] = domain, ["WorkOrd"] = wo.WorkOrd },
                            update,
                            expect = new Dictionary<string, object?>(),
                            insert
                        }, ct, pulse: false))
                            enqueued++;

                        // 165 里程碑工序：先查 OP，再逐条入队 UPDATE
                        List<int> ops;
                        try
                        {
                            ops = await ss.Ado.SqlQueryAsync<int>(
                                @"SELECT OP FROM WorkOrdRouting
                                   WHERE Domain=@d AND WorkOrd=@w
                                     AND MilestoneOp=1
                                     AND ISNULL(Status,'') NOT IN ('C','c')",
                                new SugarParameter("@d", domain),
                                new SugarParameter("@w", wo.WorkOrd));
                        }
                        catch
                        {
                            ops = new List<int>();
                        }

                        foreach (var op in ops)
                        {
                            if (await EnqueueAsync(input.TenantId, $"pick|{domain}|{wo.WorkOrd}|{op}|wor", "PICK_WOR_STATUS", new
                            {
                                op = "UPDATE",
                                table = "WorkOrdRouting",
                                keys = new Dictionary<string, object?>
                                {
                                    ["Domain"] = domain,
                                    ["WorkOrd"] = wo.WorkOrd,
                                    ["OP"] = op
                                },
                                update = new Dictionary<string, object?>
                                {
                                    ["Status"] = "r",
                                    ["UpdateUser"] = Trunc(user, 24),
                                    ["UpdateTime"] = now.ToString("yyyy-MM-dd HH:mm:ss")
                                },
                                expect = new Dictionary<string, object?>()
                            }, ct, pulse: false))
                                enqueued++;
                        }
                    }

                    await _db.Ado.CommitTranAsync();
                    pendingPulse = true;
                    reconciled = toReconcile.Count > 0;
                }
                catch
                {
                    await _db.Ado.RollbackTranAsync();
                    throw;
                }

                if (pendingPulse) _wake.Pulse();
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "[CreatePickBill] 本库事务失败 trace={Trace}", trace);
                foreach (var wo in localTargets)
                {
                    if (items.Any(i => i.WorkOrd == wo.WorkOrd && i.Result is "created" or "existed"))
                        continue;
                    items.Add(new PickBillItemResult
                    {
                        WorkOrd = wo.WorkOrd!, Result = "failed", ReasonCode = "MES_WRITE_FAILED",
                        Reason = "本库状态更新失败：" + Trunc(ex.Message, 200),
                        Hint = "查看服务日志"
                    });
                }
            }
        }

        foreach (var (wo, nbr) in toReconcile)
        {
            if (items.Any(i => string.Equals(i.WorkOrd, wo.WorkOrd, StringComparison.OrdinalIgnoreCase)))
                continue;
            items.Add(new PickBillItemResult
            {
                WorkOrd = wo.WorkOrd!, Result = "existed", Nbr = nbr, ReasonCode = "EXISTS",
                Reason = "165 上已有有效领料单"
            });
        }

        _logger.LogInformation(
            "[CreatePickBill] domain={Domain} requested={Req} created={C} existed={E} outbox={Out} trace={Trace}",
            domain, masters.Count, items.Count(i => i.Result == "created"), items.Count(i => i.Result == "existed"),
            enqueued, trace);

        return BuildResponse(items, enqueued, reconciled ? 1 : 0, reconciled, trace);
    }

    private static Task<KeeperResolveService.KeeperResolveResult> FilterKeeperForItems(
        KeeperResolveService.KeeperResolveResult full, List<string> itemNums)
    {
        var set = new HashSet<string>(itemNums, StringComparer.OrdinalIgnoreCase);
        return Task.FromResult(new KeeperResolveService.KeeperResolveResult
        {
            Hits = full.Hits.Where(h => set.Contains(h.ItemNum)).ToList(),
            MissingItems = full.MissingItems.Where(x => set.Contains(x)).ToList(),
            EmptyLocationItems = full.EmptyLocationItems.Where(x => set.Contains(x)).ToList(),
            UnsafeItems = full.UnsafeItems.Where(x => set.Contains(x)).ToList()
        });
    }

    private async Task<Dictionary<string, (string Nbr, bool HasDetail)>> ProbeExistingAsync(
        ISqlSugarClient ss, string domain, List<string> workOrds, CancellationToken ct)
    {
        var result = new Dictionary<string, (string Nbr, bool HasDetail)>(StringComparer.OrdinalIgnoreCase);
        if (workOrds.Count == 0) return result;

        var inSql = string.Join(",", workOrds.Select((_, k) => "@w" + k));
        var pars = workOrds.Select((w, k) => new SugarParameter("@w" + k, w)).ToList();
        pars.Add(new SugarParameter("@d", domain));
        pars.Add(new SugarParameter("@t", NbrTypeBill));

        var rows = await ss.Ado.SqlQueryAsync<ProbeRow>($@"
            SELECT m.WorkOrd, m.Nbr,
                   CASE WHEN EXISTS (SELECT 1 FROM NbrDetail d WHERE d.NbrRecID = m.RecID)
                        THEN 1 ELSE 0 END AS HasDetail
              FROM NbrMaster m
             WHERE m.Domain = @d AND m.Type = @t
               AND ISNULL(m.IsActive, 1) = 1
               AND ISNULL(m.TransType, '') = ''
               AND m.WorkOrd IN ({inSql})", pars.ToArray());

        foreach (var g in rows.GroupBy(r => r.WorkOrd ?? "", StringComparer.OrdinalIgnoreCase))
        {
            var best = g.OrderByDescending(x => x.HasDetail).ThenBy(x => x.Nbr).First();
            result[g.Key] = (best.Nbr ?? "", best.HasDetail != 0);
        }
        return result;
    }

    private async Task<Dictionary<string, ItemLite>> LoadItemsAsync(List<string> itemNums, CancellationToken ct)
    {
        var itemMap = new Dictionary<string, ItemLite>(StringComparer.OrdinalIgnoreCase);
        if (itemNums.Count == 0) return itemMap;
        var inSql = string.Join(",", itemNums.Select((_, k) => "@i" + k));
        var pars = itemNums.Select((n, k) => new SugarParameter("@i" + k, n)).ToArray();
        var items = await _db.Ado.SqlQueryAsync<ItemLite>(
            $"SELECT ItemNum, Descr, UM, Location FROM ItemMaster WHERE ItemNum IN ({inSql})", pars);
        foreach (var it in items)
        {
            if (!string.IsNullOrWhiteSpace(it.ItemNum) && !itemMap.ContainsKey(it.ItemNum))
                itemMap[it.ItemNum] = it;
        }
        return itemMap;
    }

    private async Task<List<WorkOrdMaster>> LoadCandidatesAsync(string domain, string? workOrdCsv, CancellationToken ct)
    {
        if (string.IsNullOrWhiteSpace(workOrdCsv))
        {
            var from = DateTime.Now.Date.AddDays(1);
            var to = DateTime.Now.Date.AddDays(4);
            return await _db.Queryable<WorkOrdMaster>()
                .Where(m => m.Domain == domain
                            && m.OrdDate >= from && m.OrdDate < to
                            && m.Status != null && m.Status.ToLower() == "p")
                .ToListAsync(ct);
        }

        var list = workOrdCsv.Split(',', StringSplitOptions.RemoveEmptyEntries | StringSplitOptions.TrimEntries);
        if (list.Length == 0) return new List<WorkOrdMaster>();
        return await _db.Queryable<WorkOrdMaster>()
            .Where(m => list.Contains(m.WorkOrd!)
                        && m.Status != null
                        && m.Status.ToLower() != "c"
                        && m.Status.ToLower() != "w")
            .ToListAsync(ct);
    }

    private static Dictionary<string, object?> BuildWorkOrdInsert(
        string domain, string workOrd, string batch, string user, DateTime now, WorkOrdMaster? local)
    {
        var insert = new Dictionary<string, object?>(StringComparer.OrdinalIgnoreCase)
        {
            ["Domain"] = domain,
            ["WorkOrd"] = workOrd,
            ["ID"] = workOrd,
            ["Status"] = "r",
            ["Batch"] = batch,
            ["ItemNum"] = local?.ItemNum ?? "",
            ["Site"] = local?.Site ?? "",
            ["Typed"] = local?.Typed ?? "",
            ["QtyOrded"] = local?.QtyOrded ?? 0m,
            ["ProdLine"] = local?.ProdLine ?? "",
            ["Location"] = local?.Location ?? "",
            ["Priority"] = local?.Priority ?? 0m,
            ["Urgent"] = local?.Urgent ?? 0,
            ["ItemName"] = local?.ItemName ?? "",
            ["Department"] = local?.Department ?? "",
            ["RoutingCode"] = local?.RoutingCode ?? "",
            ["BOMFormula"] = local?.BOMFormula ?? "",
            ["Project"] = local?.Project ?? "",
            ["Drawing"] = local?.Drawing ?? "",
            ["Rev"] = local?.Rev ?? "",
            ["CustNo"] = local?.CustNo ?? "",
            ["BusinessID"] = local?.BusinessID ?? 0L,
            ["IsActive"] = true,
            ["IsConfirm"] = local?.IsConfirm ?? false,
            ["CreateUser"] = Trunc(local?.CreateUser ?? user, 24),
            ["UpdateUser"] = Trunc(user, 24),
            ["CreateTime"] = (local?.CreateTime ?? now).ToString("yyyy-MM-dd HH:mm:ss"),
            ["UpdateTime"] = now.ToString("yyyy-MM-dd HH:mm:ss")
        };
        if (local?.OrdDate != null)
            insert["OrdDate"] = local.OrdDate.Value.ToString("yyyy-MM-dd HH:mm:ss");
        if (local?.DueDate != null)
            insert["DueDate"] = local.DueDate.Value.ToString("yyyy-MM-dd HH:mm:ss");
        if (local?.ReleaseDate != null)
            insert["ReleaseDate"] = local.ReleaseDate.Value.ToString("yyyy-MM-dd HH:mm:ss");
        return insert;
    }

    private static void BuildOne(
        WorkOrdMaster wo,
        List<WorkOrdDetail> wods,
        Dictionary<string, ItemLite> itemMap,
        Dictionary<string, KeeperResolveService.KeeperHit> keeperByItem,
        string userAccount,
        List<DraftNbrMaster> masters,
        List<DraftNbrDetail> details)
    {
        var today = DateTime.Now.Date;
        var ord = (wo.OrdDate ?? today).Date;
        var eff = ord.AddDays(-1);
        if (eff < today) eff = today;
        var start = eff;
        var temp = Guid.NewGuid().ToString("N");
        var keepers = new List<string>();
        short line = 1;
        var agg = new Dictionary<string, DraftNbrDetail>(StringComparer.OrdinalIgnoreCase);

        foreach (var a in wods)
        {
            if (string.IsNullOrWhiteSpace(a.ItemNum)) continue;
            itemMap.TryGetValue(a.ItemNum, out var im);
            var locFrom = !string.IsNullOrWhiteSpace(a.Location)
                ? a.Location!
                : (im?.Location ?? "");
            if (keeperByItem.TryGetValue(a.ItemNum, out var kh) && !string.IsNullOrWhiteSpace(kh.Employee))
                keepers.Add(kh.Employee);

            if (agg.TryGetValue(a.ItemNum, out var exist))
            {
                exist.QtyOrd += a.QtyRequired;
                exist.CurrQtyOpened += a.QtyRequired;
                continue;
            }

            var d = new DraftNbrDetail
            {
                TempKey = temp,
                Domain = wo.Domain ?? "8010",
                Type = NbrTypeBill,
                ItemNum = a.ItemNum,
                QtyFrom = 0,
                QtyTo = 0,
                LocationFrom = locFrom,
                LocationTo = wo.Location ?? "",
                WorkOrd = wo.WorkOrd ?? "",
                QtyOrd = a.QtyRequired,
                CurrQtyOpened = a.QtyRequired,
                Line = line++,
                UM = im?.UM ?? "",
                LotSerial = a.LotSerial ?? "",
                ItemName = im?.Descr ?? "",
                Status = "",
                CreateUser = userAccount,
                UpdateUser = userAccount,
                CreateTime = DateTime.Now,
                UpdateTime = DateTime.Now
            };
            agg[a.ItemNum] = d;
            details.Add(d);
        }

        if (agg.Count == 0) return;

        var (user1, user2) = KeeperResolveService.FormatKeeperUsers(
            keepers.Distinct().Select(e => new KeeperResolveService.KeeperHit { Employee = e, ItemNum = "" }));

        masters.Add(new DraftNbrMaster
        {
            TempKey = temp,
            Domain = wo.Domain ?? "8010",
            Type = NbrTypeBill,
            Remark = "下达自动领料",
            ProdLine = wo.ProdLine ?? "",
            EffDate = eff,
            Date = start,
            Status = "",
            WorkOrd = wo.WorkOrd ?? "",
            QtyOrd = wo.QtyOrded,
            IsActive = true,
            IsChanged = true,
            Name = userAccount.Length > 12 ? userAccount[..12] : userAccount,
            Department = "101",
            CreateUser = userAccount,
            UpdateUser = userAccount,
            CreateTime = DateTime.Now,
            UpdateTime = DateTime.Now,
            TransType = "",
            User1 = user1,
            User2 = user2
        });
    }

    private static async Task InsertNbrOn165Async(
        ISqlSugarClient ss,
        List<DraftNbrMaster> masters,
        List<DraftNbrDetail> details,
        CancellationToken ct)
    {
        await ss.Ado.BeginTranAsync();
        try
        {
            foreach (var m in masters)
            {
                var recIdObj = await ss.Ado.SqlQuerySingleAsync<int?>(
                    @"INSERT INTO NbrMaster
                        (Domain, Type, Nbr, [Print], Status, Remark, Date, User1, User2,
                         ProdLine, EffDate, WorkOrd, QtyOrd, QtyRec, Address, BusinessID,
                         CreateUser, UpdateUser, CreateTime, UpdateTime, IsActive, IsConfirm,
                         IsChanged, Department, Name, TransType, Ufld1, IsGP12Checked, IsPlan, IsReturn, Op)
                      OUTPUT INSERTED.RecID
                      VALUES
                        (@Domain, @Type, @Nbr, 0, @Status, @Remark, @Date, @User1, @User2,
                         @ProdLine, @EffDate, @WorkOrd, @QtyOrd, 0, @Address, 0,
                         @CreateUser, @UpdateUser, @CreateTime, @UpdateTime, 1, 0,
                         1, @Department, @Name, @TransType, @Ufld1, 0, 0, 0, 0)",
                    new SugarParameter("@Domain", m.Domain),
                    new SugarParameter("@Type", m.Type),
                    new SugarParameter("@Nbr", m.Nbr),
                    new SugarParameter("@Status", m.Status ?? ""),
                    new SugarParameter("@Remark", m.Remark ?? ""),
                    new SugarParameter("@Date", m.Date),
                    new SugarParameter("@User1", m.User1 ?? ""),
                    new SugarParameter("@User2", m.User2 ?? "[]"),
                    new SugarParameter("@ProdLine", m.ProdLine ?? ""),
                    new SugarParameter("@EffDate", m.EffDate),
                    new SugarParameter("@WorkOrd", m.WorkOrd),
                    new SugarParameter("@QtyOrd", m.QtyOrd),
                    new SugarParameter("@Address", m.Address ?? ""),
                    new SugarParameter("@CreateUser", Trunc(m.CreateUser, 24)),
                    new SugarParameter("@UpdateUser", Trunc(m.UpdateUser, 24)),
                    new SugarParameter("@CreateTime", m.CreateTime),
                    new SugarParameter("@UpdateTime", m.UpdateTime),
                    new SugarParameter("@Department", m.Department ?? ""),
                    new SugarParameter("@Name", m.Name ?? ""),
                    new SugarParameter("@TransType", m.TransType ?? ""),
                    new SugarParameter("@Ufld1", m.Ufld1 ?? ""));

                if (recIdObj is null or 0)
                    throw new InvalidOperationException($"NbrMaster 插入未返回 RecID：{m.Nbr}");
                var recId = recIdObj.Value;

                foreach (var d in details.Where(x => x.TempKey == m.TempKey))
                {
                    await ss.Ado.ExecuteCommandAsync(
                        @"INSERT INTO NbrDetail
                            (Domain, Type, Nbr, Line, ItemNum, Dimension1, Dimension2,
                             LocationFrom, LocationTo, QtyFrom, QtyTo, UM, [Print], Status, LotSerial,
                             WorkOrd, QtyOrd, QtyRec, Address, BusinessID, CreateUser, UpdateUser,
                             CreateTime, UpdateTime, IsActive, IsConfirm, QtyCache, CurrQtyOpened,
                             IsChanged, NbrRecID, OrdNbr, ItemName, ERPfld1, ERPfld2, OrdLine,
                             IsGP12Demand, IsGP12Checked, Material, SeqID)
                          VALUES
                            (@Domain, @Type, @Nbr, @Line, @ItemNum, '', '',
                             @LocationFrom, @LocationTo, 0, 0, @UM, 0, @Status, @LotSerial,
                             @WorkOrd, @QtyOrd, 0, @Address, 0, @CreateUser, @UpdateUser,
                             @CreateTime, @UpdateTime, 1, 0, 0, @CurrQtyOpened,
                             1, @NbrRecID, @OrdNbr, @ItemName, @ERPfld1, @ERPfld2, @OrdLine,
                             0, 0, 0, 0)",
                        new SugarParameter("@Domain", d.Domain),
                        new SugarParameter("@Type", d.Type),
                        new SugarParameter("@Nbr", d.Nbr),
                        new SugarParameter("@Line", d.Line),
                        new SugarParameter("@ItemNum", d.ItemNum),
                        new SugarParameter("@LocationFrom", Trunc(d.LocationFrom, 8)),
                        new SugarParameter("@LocationTo", Trunc(d.LocationTo, 8)),
                        new SugarParameter("@UM", Trunc(d.UM, 8)),
                        new SugarParameter("@Status", d.Status ?? ""),
                        new SugarParameter("@LotSerial", d.LotSerial ?? ""),
                        new SugarParameter("@WorkOrd", d.WorkOrd),
                        new SugarParameter("@QtyOrd", d.QtyOrd),
                        new SugarParameter("@Address", Trunc(d.Address, 60)),
                        new SugarParameter("@CreateUser", Trunc(d.CreateUser, 24)),
                        new SugarParameter("@UpdateUser", Trunc(d.UpdateUser, 24)),
                        new SugarParameter("@CreateTime", d.CreateTime),
                        new SugarParameter("@UpdateTime", d.UpdateTime),
                        new SugarParameter("@CurrQtyOpened", d.CurrQtyOpened),
                        new SugarParameter("@NbrRecID", recId),
                        new SugarParameter("@OrdNbr", Trunc(d.OrdNbr, 48)),
                        new SugarParameter("@ItemName", Trunc(d.ItemName, 1000)),
                        new SugarParameter("@ERPfld1", Trunc(d.ERPfld1, 200)),
                        new SugarParameter("@ERPfld2", Trunc(d.ERPfld2, 200)),
                        new SugarParameter("@OrdLine", d.OrdLine));
                }
            }
            await ss.Ado.CommitTranAsync();
        }
        catch
        {
            await ss.Ado.RollbackTranAsync();
            throw;
        }
    }

    private async Task<bool> EnqueueAsync(long tenantId, string idem, string action, object payload, CancellationToken ct, bool pulse = true)
    {
        var json = JsonSerializer.Serialize(payload);
        var item = new MdpOutbox
        {
            TenantId = tenantId,
            TargetSourceCode = TargetSource,
            ActionCode = action,
            IdemKey = idem.Length > 200 ? idem[..200] : idem,
            PayloadJson = json
        };
        return await _enqueue.TryEnqueueAsync(item, ct, pulse);
    }

    private static string ClassifyMesError(Exception ex)
    {
        for (var e = ex; e != null; e = e.InnerException!)
        {
            if (e is TimeoutException or SocketException) return "MES_UNREACHABLE";
            // SqlSugar / 驱动可能抛 Microsoft.Data.SqlClient.SqlException 或 System.Data.SqlClient.SqlException
            if (e.GetType().Name == "SqlException")
            {
                var numObj = e.GetType().GetProperty("Number")?.GetValue(e);
                if (numObj is int n && n is -2 or 53 or 4060 or 18456 or 10060 or 10061)
                    return "MES_UNREACHABLE";
                return "MES_WRITE_FAILED";
            }
            var msg = e.Message ?? "";
            if (msg.Contains("timeout", StringComparison.OrdinalIgnoreCase)
                || msg.Contains("network", StringComparison.OrdinalIgnoreCase)
                || msg.Contains("连不上", StringComparison.OrdinalIgnoreCase)
                || msg.Contains("无法连接", StringComparison.OrdinalIgnoreCase))
                return "MES_UNREACHABLE";
        }
        return "MES_WRITE_FAILED";
    }

    private static string DescribeMes(string code, Exception ex) =>
        code == "MES_UNREACHABLE"
            ? "无法连接 MES 端数据库"
            : "MES 端写入失败：" + Trunc(ex.Message, 160);

    private static string HintFor(string code) => code switch
    {
        "MES_UNREACHABLE" => "请联系系统管理员后重试",
        "MES_WRITE_FAILED" => "请联系系统管理员",
        "SEQ_FAILED" => "请联系系统管理员检查单号配置",
        _ => "请联系系统管理员"
    };

    private static PickBillResponse FailAll(string message, string code) => new()
    {
        Ok = false,
        Code = "FAILED",
        Message = message,
        Summary = new PickBillSummary { Requested = 0, Failed = 1 },
        Items =
        {
            new PickBillItemResult
            {
                WorkOrd = "", Result = "failed", ReasonCode = code, Reason = message, Hint = HintFor(code)
            }
        }
    };

    private PickBillResponse FailAllBatch(
        List<WorkOrdMaster> masters,
        string code,
        string reason,
        string hint,
        string trace,
        Exception? techEx = null,
        string? domain = null)
    {
        var userMsg = PickBillUserMessages.ForPickCreateFailure(code, reason, hint);
        if (techEx != null)
        {
            _logger.LogError(
                techEx,
                "[CreatePickBill] fail-all code={Code} domain={Domain} trace={Trace} workOrds={WorkOrds} userMsg={UserMsg}",
                code,
                domain,
                trace,
                string.Join(",", masters.Select(m => m.WorkOrd).Where(x => !string.IsNullOrWhiteSpace(x)).Take(20)),
                userMsg);
        }
        else
        {
            _logger.LogWarning(
                "[CreatePickBill] fail-all code={Code} domain={Domain} trace={Trace} reason={Reason}",
                code, domain, trace, reason);
        }

        var items = masters.Select(m => new PickBillItemResult
        {
            WorkOrd = m.WorkOrd ?? "", Result = "failed", ReasonCode = code, Reason = userMsg, Hint = HintFor(code)
        }).ToList();
        return BuildResponse(items, 0, 0, false, trace, userMsg);
    }

    private static PickBillResponse BuildResponse(
        List<PickBillItemResult> items, int enqueued, int _, bool reconciled, string? trace, string? message = null)
    {
        // 失败/跳过行统一成面向业务用户的中文原因（保留 ReasonCode 供前端跳转）
        foreach (var i in items)
        {
            if (i.Result is not ("failed" or "skipped")) continue;
            if (string.IsNullOrWhiteSpace(i.ReasonCode)) continue;
            if (!string.IsNullOrWhiteSpace(i.Reason) &&
                (i.Reason.Contains("下达失败", StringComparison.Ordinal) ||
                 i.Reason.Contains("建领料失败", StringComparison.Ordinal)))
                continue;
            i.Reason = PickBillUserMessages.ForPickCreateFailure(i.ReasonCode, i.Reason, i.Hint);
            if (string.IsNullOrWhiteSpace(i.Hint))
                i.Hint = HintFor(i.ReasonCode);
        }

        var summary = new PickBillSummary
        {
            Requested = items.Count,
            Created = items.Count(i => i.Result == "created"),
            Existed = items.Count(i => i.Result == "existed"),
            Skipped = items.Count(i => i.Result == "skipped"),
            Failed = items.Count(i => i.Result == "failed")
        };
        var code = "FAILED";
        if (summary.Failed == 0 && summary.Skipped == 0 && summary.Created > 0) code = "OK";
        else if (summary.Created > 0 && (summary.Failed > 0 || summary.Skipped > 0)) code = "PARTIAL";
        else if (summary.Created == 0 && summary.Failed == 0) code = "NOOP";
        else code = "FAILED";

        var topMsg = message;
        if (string.IsNullOrWhiteSpace(topMsg) && code == "FAILED")
        {
            var firstFail = items.FirstOrDefault(i => i.Result == "failed");
            topMsg = firstFail?.Reason;
        }

        return new PickBillResponse
        {
            Ok = summary.Failed == 0 && summary.Created + summary.Existed > 0,
            Code = code,
            Summary = summary,
            Items = items,
            Writeback = new PickBillWriteback
            {
                Enqueued = enqueued,
                State = enqueued > 0 ? "pending" : "none",
                Reconciled = reconciled ? true : null
            },
            Trace = trace,
            Message = topMsg
        };
    }

    private static string Tail(string workOrd) =>
        workOrd.Length > 1 ? workOrd[1..] : workOrd;

    private static string Trunc(string? s, int max) =>
        string.IsNullOrEmpty(s) ? "" : (s.Length <= max ? s : s[..max]);

    private sealed class ProbeRow
    {
        public string? WorkOrd { get; set; }
        public string? Nbr { get; set; }
        public int HasDetail { get; set; }
    }

    private sealed class ItemLite
    {
        public string ItemNum { get; set; } = "";
        public string? Descr { get; set; }
        public string? UM { get; set; }
        public string? Location { get; set; }
    }

    private sealed class DraftNbrMaster
    {
        public string TempKey { get; set; } = "";
        public string Domain { get; set; } = "";
        public string Type { get; set; } = "";
        public string Nbr { get; set; } = "";
        public string? Remark { get; set; }
        public string? ProdLine { get; set; }
        public DateTime EffDate { get; set; }
        public DateTime Date { get; set; }
        public string? Status { get; set; }
        public string WorkOrd { get; set; } = "";
        public decimal QtyOrd { get; set; }
        public bool IsActive { get; set; }
        public bool IsChanged { get; set; }
        public string? Name { get; set; }
        public string? Department { get; set; }
        public string? CreateUser { get; set; }
        public string? UpdateUser { get; set; }
        public DateTime CreateTime { get; set; }
        public DateTime UpdateTime { get; set; }
        public string? TransType { get; set; }
        public string? User1 { get; set; }
        public string? User2 { get; set; }
        public string? Address { get; set; }
        public string? Ufld1 { get; set; }
    }

    private sealed class DraftNbrDetail
    {
        public string TempKey { get; set; } = "";
        public string Domain { get; set; } = "";
        public string Type { get; set; } = "";
        public string Nbr { get; set; } = "";
        public string ItemNum { get; set; } = "";
        public decimal QtyFrom { get; set; }
        public decimal QtyTo { get; set; }
        public string LocationFrom { get; set; } = "";
        public string LocationTo { get; set; } = "";
        public string WorkOrd { get; set; } = "";
        public decimal QtyOrd { get; set; }
        public decimal CurrQtyOpened { get; set; }
        public short Line { get; set; }
        public string UM { get; set; } = "";
        public string LotSerial { get; set; } = "";
        public string ItemName { get; set; } = "";
        public string Status { get; set; } = "";
        public string? CreateUser { get; set; }
        public string? UpdateUser { get; set; }
        public DateTime CreateTime { get; set; }
        public DateTime UpdateTime { get; set; }
        public string? Address { get; set; }
        public string? OrdNbr { get; set; }
        public string? ERPfld1 { get; set; }
        public string? ERPfld2 { get; set; }
        public short OrdLine { get; set; }
    }
}