AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/Production/ProductionSchedulingActionService.cs

using System.Globalization;
using System.Text.Json;
using Admin.NET.Plugin.AiDOP.DataPlatform.Wms;
using Admin.NET.Plugin.AiDOP.Infrastructure;
using Admin.NET.Plugin.AiDOP.Order;
using Admin.NET.Plugin.AiDOP.WorkOrder;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;

namespace Admin.NET.Plugin.AiDOP.Production;

/// <summary>工单排产动作：生成排程、优先级调整并重检。</summary>
[ApiDescriptionSettings(Order = 264, Description = "工单排产动作")]
[Route("api/Production")]
[AllowAnonymous]
[NonUnify]
public class ProductionSchedulingActionService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly UserManager _userManager;
    private readonly ProductionScheduleGenerationService _scheduleGen;
    private readonly WorkOrderRoutingSyncService _routingSync;
    private readonly AidopActionRunLogWriter _runLog;
    private readonly MdpRefreshDispatcher _dispatcher;
    private readonly PickBillNbrSyncService _pickNbrSync;
    private readonly ILogger<ProductionSchedulingActionService> _logger;

    /// <summary>RUNNING 超过此时长视为僵死并自动关闭（防单飞永久堵死）。</summary>
    private static readonly TimeSpan StaleRunningAge = TimeSpan.FromHours(3);

    public ProductionSchedulingActionService(
        ISqlSugarClient db,
        UserManager userManager,
        ProductionScheduleGenerationService scheduleGen,
        WorkOrderRoutingSyncService routingSync,
        AidopActionRunLogWriter runLog,
        MdpRefreshDispatcher dispatcher,
        PickBillNbrSyncService pickNbrSync,
        ILogger<ProductionSchedulingActionService> logger)
    {
        _db = db;
        _userManager = userManager;
        _scheduleGen = scheduleGen;
        _routingSync = routingSync;
        _runLog = runLog;
        _dispatcher = dispatcher;
        _pickNbrSync = pickNbrSync;
        _logger = logger;
    }

    public const string ActionScheduleGenerate = "S2_SCHEDULE_GENERATE";

    /// <summary>
    /// 提交全量生产排程（异步）。立即返回 runId，后台执行；前端轮询 status。
    /// </summary>
    [DisplayName("生成生产排程")]
    [HttpPost("scheduling/generate")]
    public async Task<object> GenerateSchedule([FromQuery] string domain, [FromQuery] bool enableCapacityConstraint = false)
    {
        var tenantId = ResolveTenantId(domain);
        var account = _userManager.Account ?? "system";
        return await SubmitGenerateAsync(tenantId, domain, account, enableCapacityConstraint, trigger: "manual");
    }

    /// <summary>查询排程运行状态（供前端轮询）。</summary>
    [DisplayName("生产排程运行状态")]
    [HttpGet("scheduling/generate-status")]
    public async Task<object> GetGenerateStatus([FromQuery] long runId)
    {
        var tenantId = AidopTenantHelper.Resolve(App.HttpContext);
        var row = (await _db.Ado.SqlQueryAsync<ScheduleRunStatusRow>(
            """
            SELECT id AS Id, status AS Status, message AS Message, detail_json AS DetailJson,
                   start_time AS StartTime, end_time AS EndTime
            FROM aidop_action_run_log
            WHERE id = @Id AND tenant_id = @TenantId AND action_code = @ActionCode
            LIMIT 1
            """,
            new SugarParameter("@Id", runId),
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@ActionCode", ActionScheduleGenerate))).FirstOrDefault()
            ?? throw Oops.Oh("排程运行记录不存在");

        object? detail = null;
        if (!string.IsNullOrWhiteSpace(row.DetailJson))
        {
            try { detail = JsonSerializer.Deserialize<JsonElement>(row.DetailJson); }
            catch { detail = row.DetailJson; }
        }

        return new
        {
            runId = row.Id,
            status = row.Status,
            message = row.Message,
            detail,
            startTime = row.StartTime,
            endTime = row.EndTime
        };
    }

    /// <summary>
    /// 供 HTTP 与定时作业共用：单飞校验 → 写 RUNNING → 后台派发。
    /// </summary>
    public async Task<object> SubmitGenerateAsync(
        long tenantId,
        string domain,
        string account,
        bool enableCapacityConstraint,
        string trigger)
    {
        await _runLog.FailStaleRunningAsync(ActionScheduleGenerate, tenantId, StaleRunningAge);

        if (await _runLog.HasRunningAsync(ActionScheduleGenerate, tenantId))
            throw Oops.Oh("生产排程进行中，请稍后再试（同租户同时只允许一个排程运行）");

        var logId = await _runLog.StartAsync(
            ActionScheduleGenerate, tenantId, "WorkOrdMaster", null, $"domain={domain};trigger={trigger}");

        await _runLog.UpdateProgressAsync(logId, "已提交，后台执行中…", new
        {
            tenantId,
            domain,
            runId = logId,
            trigger,
            phase = "queued"
        });

        // 按租户保留各自委托与运行日志；底层生成服务使用全局门闩串行分配旧表主键号段。
        var key = $"S2_SCHEDULE:{tenantId}";
        _dispatcher.Enqueue(key, async (sp, _) =>
        {
            var runLog = sp.GetRequiredService<AidopActionRunLogWriter>();
            var gen = sp.GetRequiredService<ProductionScheduleGenerationService>();
            var routingSync = sp.GetRequiredService<WorkOrderRoutingSyncService>();
            var log = sp.GetRequiredService<ILogger<ProductionSchedulingActionService>>();
            try
            {
                await runLog.UpdateProgressAsync(logId, "排程计算中…", new
                {
                    tenantId,
                    domain,
                    runId = logId,
                    trigger,
                    phase = "running"
                });

                await routingSync.EnsureAllPendingAsync(tenantId, account);
                var result = await gen.GenerateAsync(tenantId, domain, account, enableCapacityConstraint, runId: logId);
                await runLog.SuccessAsync(logId, result.Message, new
                {
                    tenantId,
                    domain,
                    runId = logId,
                    trigger,
                    phase = "done",
                    workOrderCount = result.WorkOrderCount,
                    scheduleRowCount = result.ScheduleRowCount,
                    pushEnqueuedCount = result.PushEnqueuedCount,
                    frozenWorkOrderCount = result.FrozenWorkOrderCount,
                    frozenOpCount = result.FrozenOpCount,
                    usedWorkCenterCalendar = result.UsedWorkCenterCalendar,
                    skippedWorkOrders = result.SkippedWorkOrders,
                    psdMismatchCount = result.PsdMismatchCount,
                    psdDuplicateActiveCount = result.PsdDuplicateActiveCount,
                    outboxDeadCount = result.OutboxDeadCount,
                    mesLineMissingCount = result.MesLineMissingCount,
                    mesRoutingMissingCount = result.MesRoutingMissingCount
                });
            }
            catch (Exception ex)
            {
                log.LogError(ex, "S2 异步排程失败 tenant={TenantId} runId={RunId}", tenantId, logId);
                await runLog.FailedAsync(logId, ex.Message, new { tenantId, domain, runId = logId, trigger });
            }
        });

        _logger.LogInformation("S2 排程已提交异步执行 tenant={TenantId} runId={RunId} trigger={Trigger}", tenantId, logId, trigger);

        return new
        {
            message = "生产排程已提交，后台执行中",
            runId = logId,
            status = "RUNNING",
            async = true
        };
    }

    private sealed class ScheduleRunStatusRow
    {
        public long Id { get; set; }
        public string? Status { get; set; }
        public string? Message { get; set; }
        public string? DetailJson { get; set; }
        public DateTime? StartTime { get; set; }
        public DateTime? EndTime { get; set; }
    }

    /// <summary>优先级/数量/交期调整并重做资源检查。</summary>
    [DisplayName("优先级调整并重检")]
    [HttpPost("scheduling/update-priority-and-recheck")]
    public async Task<object> UpdatePriorityAndRecheck([FromBody] WorkOrderPriorityRecheckInput input)
    {
        var tenantId = AidopTenantHelper.Resolve(App.HttpContext);
        var account = string.IsNullOrWhiteSpace(input.UserAccount)
            ? (_userManager.Account ?? "system")
            : input.UserAccount.Trim();
        var workOrd = input.Workord.Trim();
        var logId = await _runLog.StartAsync("S2_PRIORITY_RECHECK", tenantId, "WorkOrdMaster", null, workOrd);

        try
        {
            var before = await LoadWorkOrderSnapshotAsync(tenantId, workOrd);

            DateTime? dueDate = null;
            if (!string.IsNullOrWhiteSpace(input.Instockdate))
                dueDate = DateTime.Parse(input.Instockdate.Trim(), CultureInfo.InvariantCulture).Date;

            decimal? qty = null;
            if (!string.IsNullOrWhiteSpace(input.Qty) && decimal.TryParse(input.Qty.Trim(), NumberStyles.Any, CultureInfo.InvariantCulture, out var q))
                qty = q;

            var pars = new List<SugarParameter>
            {
                new("@WorkOrd", workOrd),
                new("@TenantId", tenantId),
                new("@Priority", string.IsNullOrWhiteSpace(input.Priority) ? (object)DBNull.Value : input.Priority.Trim()),
                new("@LotSerial", string.IsNullOrWhiteSpace(input.LotSerial) ? (object)DBNull.Value : input.LotSerial.Trim()),
                new("@Qty", qty ?? (object)DBNull.Value),
                new("@DueDate", dueDate ?? (object)DBNull.Value),
                new("@User", account),
                new("@Now", DateTime.Now)
            };

            var affected = await _db.Ado.ExecuteCommandAsync(
                """
                UPDATE WorkOrdMaster
                SET Priority = COALESCE(@Priority, Priority),
                    LotSerial = COALESCE(@LotSerial, LotSerial),
                    QtyOrded = COALESCE(@Qty, QtyOrded),
                    DueDate = COALESCE(@DueDate, DueDate),
                    UpdateUser = @User,
                    UpdateTime = @Now
                WHERE tenant_id = @TenantId AND WorkOrd = @WorkOrd
                """,
                pars);

            if (affected == 0)
                throw Oops.Oh("工单不存在或未更新");

            var qtyChanged = qty.HasValue && before?.QtyOrded != qty;
            var dueChanged = dueDate.HasValue && before?.DueDate?.Date != dueDate.Value.Date;
            var priorityChanged = !string.IsNullOrWhiteSpace(input.Priority)
                && !string.Equals(before?.Priority?.Trim(), input.Priority.Trim(), StringComparison.Ordinal);
            var warnings = new List<string>();

            // ── 同步 crm_seorderentry，避免后续齐套检查重新加载时读到旧值 ──
            if (qtyChanged || dueChanged)
            {
                var link = await LoadWorkOrderEntryLinkAsync(tenantId, workOrd);
                if (link is not null)
                {
                    await _db.Ado.ExecuteCommandAsync(
                        """
                        UPDATE crm_seorderentry
                        SET qty = COALESCE(@Qty, qty),
                            sys_capacity_date = COALESCE(@SysCapacityDate, sys_capacity_date),
                            update_time = NOW()
                        WHERE Id = @EntryId AND tenant_id = @TenantId AND IsDeleted = 0
                        """,
                        new SugarParameter("@Qty", qty ?? (object)DBNull.Value),
                        new SugarParameter("@SysCapacityDate", dueDate ?? (object)DBNull.Value),
                        new SugarParameter("@EntryId", link.Entry.Id),
                        new SugarParameter("@TenantId", tenantId));
                }
            }

            // 数量变更时同步更新 mes_morder 和 mes_moentry
            if (qtyChanged && qty.HasValue)
            {
                await UpdateMesOrderQuantityAsync(tenantId, workOrd, qty.Value, account);

                // 同步更新 WorkOrdRouting.QtyOrded（EnsureFromRoutingAsync 已有数据时不会更新数量）
                await _db.Ado.ExecuteCommandAsync(
                    """
                    UPDATE WorkOrdRouting
                    SET QtyOrded = @Qty,
                        UpdateUser = @User, UpdateTime = @Now
                    WHERE tenant_id = @TenantId AND WorkOrd = @WorkOrd
                      AND IFNULL(IsActive, 0) = 1
                    """,
                    new SugarParameter("@Qty", qty.Value),
                    new SugarParameter("@User", account),
                    new SugarParameter("@Now", DateTime.Now),
                    new SugarParameter("@TenantId", tenantId),
                    new SugarParameter("@WorkOrd", workOrd));
            }

            // 数量变更时同步更新领料单明细（NbrDetail）
            if (qtyChanged)
            {
                await SyncPickingListDetailsAsync(tenantId, workOrd, account, warnings);
            }

            ProductionScheduleGenerationService.ScheduleGenerationResult? reschedule = null;
            if (qtyChanged || dueChanged)
            {
                reschedule = await _scheduleGen.RegenerateForWorkOrderAsync(tenantId, workOrd, input.Domain, account, input.EnableCapacityConstraint);
                warnings.Add(reschedule.Message);

                // 排程后确保工艺路线数据存在
                await _routingSync.EnsureFromRoutingAsync(tenantId, workOrd, account);
            }
            else if (priorityChanged)
            {
                warnings.Add("优先级已更新，将在下次全量重排时参与排序（现有日计划保持不变）");
            }

            var pendingReorder = priorityChanged && !qtyChanged && !dueChanged;

            await _runLog.SuccessAsync(logId, "优先级调整并重检完成", new
            {
                workOrd,
                before,
                after = new
                {
                    qty,
                    dueDate,
                    priority = input.Priority,
                    lotSerial = input.LotSerial
                },
                qtyChanged,
                dueChanged,
                priorityChanged,
                pendingReorder,
                resourceRechecked = qtyChanged || dueChanged,
                reschedule = reschedule is null
                    ? null
                    : new
                    {
                        reschedule.WorkOrderCount,
                        reschedule.ScheduleRowCount,
                        reschedule.UsedWorkCenterCalendar
                    },
                warnings
            });

            return new
            {
                message = "ok",
                warnings,
                resourceRechecked = qtyChanged || dueChanged,
                rescheduleTriggered = reschedule is not null,
                scheduleRowCount = reschedule?.ScheduleRowCount ?? 0,
                pendingReorder
            };
        }
        catch (Exception ex)
        {
            await _runLog.FailedAsync(logId, ex.Message, new { workOrd, tenantId });
            throw;
        }
    }

    private long ResolveTenantId(string? domain)
    {
        if (!string.IsNullOrWhiteSpace(domain) && long.TryParse(domain.Trim(), out var tid) && tid > 0)
            return tid;
        return AidopTenantHelper.Resolve(App.HttpContext);
    }

    /// <summary>
    /// 数量变更时同步更新 mes_morder（制造工单）和 mes_moentry（工单明细）的数量字段。
    /// </summary>
    private async Task UpdateMesOrderQuantityAsync(long tenantId, string workOrd, decimal newQty, string account)
    {
        var now = DateTime.Now;

        // 更新 mes_morder
        await _db.Ado.ExecuteCommandAsync(
            """
            UPDATE mes_morder
            SET need_number = @Qty,
                morder_production_number = @Qty,
                update_by_name = @User,
                update_time = @Now
            WHERE morder_no = @MorderNo
              AND tenant_id = @TenantId
              AND IsDeleted = 0
            """,
            new SugarParameter("@Qty", newQty),
            new SugarParameter("@User", account),
            new SugarParameter("@Now", now),
            new SugarParameter("@MorderNo", workOrd),
            new SugarParameter("@TenantId", tenantId));

        // 更新 mes_moentry（remaining_number 仅在未开始生产时才更新）
        await _db.Ado.ExecuteCommandAsync(
            """
            UPDATE mes_moentry
            SET need_number = @Qty,
                morder_production_number = @Qty,
                remaining_number = CASE
                    WHEN IFNULL(remaining_number, 0) = IFNULL(need_number, 0)
                         OR IFNULL(morder_production_number, 0) = 0
                    THEN @Qty
                    ELSE remaining_number
                END,
                update_by_name = @User,
                update_time = @Now
            WHERE moentry_mono = @Mono
              AND tenant_id = @TenantId
              AND IsDeleted = 0
            """,
            new SugarParameter("@Qty", newQty),
            new SugarParameter("@User", account),
            new SugarParameter("@Now", now),
            new SugarParameter("@Mono", workOrd),
            new SugarParameter("@TenantId", tenantId));
    }

    /// <summary>
    /// 数量变更后同步领料单明细（NbrDetail），按物料逐行比对更新。
    /// 规则：
    ///   已发料(QtyRec>0) + 新需求>已发料 → 更新 QtyOrd/CurrQtyOpened
    ///   已发料(QtyRec>0) + 新需求≤已发料 → 关闭该行
    ///   未发料 → 直接修改 QtyOrd/CurrQtyOpened
    ///   工单明细有但领料单无 → 新增
    ///   领料单有但工单明细无 → 关闭
    /// </summary>
    private async Task SyncPickingListDetailsAsync(
        long tenantId, string workOrd, string account, List<string> warnings)
    {
        // 获取工单状态
        var status = await _db.Ado.GetStringAsync(
            """
            SELECT IFNULL(LOWER(TRIM(Status)), '') FROM WorkOrdMaster
            WHERE tenant_id = @TenantId AND WorkOrd = @WorkOrd
            LIMIT 1
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrd", workOrd));

        // 仅处理 下达(R)/投产(W)/暂停(S) 状态的工单
        if (status is not ("r" or "w" or "s"))
        {
            // 初始(P)状态无领料单，无需同步
            return;
        }

        var woDomain = await _db.Ado.GetStringAsync(
            """
            SELECT IFNULL(`Domain`, '') FROM WorkOrdMaster
            WHERE tenant_id = @TenantId AND WorkOrd = @WorkOrd
            LIMIT 1
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrd", workOrd));
        if (await _pickNbrSync.TryEnqueueAfterPlanChangeAsync(tenantId, workOrd, woDomain, account))
        {
            warnings.Add($"工单 {workOrd} 领料单已入队回写 165");
            return;
        }

        // 查找领料单主记录
        var nbrRows = await _db.Ado.SqlQueryAsync<NbrMasterRow>(
            """
            SELECT RecID, Nbr, `Domain` FROM NbrMaster
            WHERE tenant_id = @TenantId AND WorkOrd = @WorkOrd AND Type = 'SM'
              AND IFNULL(TransType, '') = ''
              AND IFNULL(IsActive, 0) = 1
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrd", workOrd));

        if (nbrRows.Count == 0)
        {
            warnings.Add($"工单 {workOrd} 状态为 {status.ToUpper()} 但无领料单，跳过领料单同步");
            return;
        }

        var now = DateTime.Now;

        // 从 WorkOrdDetail 汇总最新物料需求
        var details = await _db.Ado.SqlQueryAsync<PickDetailRow>(
            """
            SELECT
                d.ItemNum,
                SUM(d.QtyRequired) AS QtyRequired,
                MAX(IFNULL(d.UM, im.Um)) AS Unit,
                MAX(im.Descr) AS ItemName
            FROM WorkOrdDetail d
            LEFT JOIN ItemMaster im ON d.ItemNum = im.ItemNum AND im.tenant_id = d.tenant_id
            WHERE d.tenant_id = @TenantId AND d.WorkOrd = @WorkOrd AND IFNULL(d.IsActive, 0) = 1
            GROUP BY d.ItemNum
            HAVING SUM(d.QtyRequired) > 0
            ORDER BY d.ItemNum
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrd", workOrd));

        foreach (var nbr in nbrRows)
        {
            var domain = string.IsNullOrWhiteSpace(nbr.Domain) ? tenantId.ToString() : nbr.Domain!.Trim();
            if (domain.Length > 8) domain = domain[..8];

            // 加载当前领料单明细行（仅未关闭的）
            var existingDetails = await _db.Ado.SqlQueryAsync<NbrDetailRow>(
                """
                SELECT RecID, ItemNum, QtyOrd, QtyRec, CurrQtyOpened, Line
                FROM NbrDetail
                WHERE tenant_id = @TenantId AND Nbr = @Nbr AND Type = 'SM'
                  AND IFNULL(IsActive, 0) = 1
                """,
                new SugarParameter("@TenantId", tenantId),
                new SugarParameter("@Nbr", nbr.Nbr));

            var detailMap = details.ToDictionary(d => d.ItemNum.Trim(), d => d);
            var existingMap = existingDetails.ToDictionary(d => (d.ItemNum ?? "").Trim(), d => d);

            // ── 1. 处理已有明细行：按 ItemNum 匹配 ──
            foreach (var existing in existingDetails)
            {
                var key = (existing.ItemNum ?? "").Trim();
                if (detailMap.TryGetValue(key, out var newDetail))
                {
                    var newQty = newDetail.QtyRequired;
                    if (existing.QtyRec > 0)
                    {
                        if (newQty > existing.QtyRec)
                        {
                            // 新需求 > 已发料 → 更新需求数
                            await _db.Ado.ExecuteCommandAsync(
                                """
                                UPDATE NbrDetail
                                SET QtyOrd = @QtyOrd, CurrQtyOpened = @CurrQtyOpened,
                                    UM = @UM, ItemName = @ItemName,
                                    UpdateUser = @User, UpdateTime = @Now
                                WHERE RecID = @RecId
                                """,
                                new SugarParameter("@QtyOrd", newQty),
                                new SugarParameter("@CurrQtyOpened", newQty),
                                new SugarParameter("@UM", (object?)newDetail.Unit ?? DBNull.Value),
                                new SugarParameter("@ItemName", (object?)newDetail.ItemName ?? DBNull.Value),
                                new SugarParameter("@User", account),
                                new SugarParameter("@Now", now),
                                new SugarParameter("@RecId", existing.RecID));
                        }
                        else
                        {
                            // 新需求 <= 已发料 → 关闭当前行
                            await _db.Ado.ExecuteCommandAsync(
                                """
                                UPDATE NbrDetail
                                SET Status = 'C', IsActive = 0,
                                    UpdateUser = @User, UpdateTime = @Now
                                WHERE RecID = @RecId
                                """,
                                new SugarParameter("@User", account),
                                new SugarParameter("@Now", now),
                                new SugarParameter("@RecId", existing.RecID));
                        }
                    }
                    else
                    {
                        // 未发料 → 直接修改需求数
                        await _db.Ado.ExecuteCommandAsync(
                            """
                            UPDATE NbrDetail
                            SET QtyOrd = @QtyOrd, CurrQtyOpened = @CurrQtyOpened,
                                UM = @UM, ItemName = @ItemName,
                                UpdateUser = @User, UpdateTime = @Now
                            WHERE RecID = @RecId
                            """,
                            new SugarParameter("@QtyOrd", newQty),
                            new SugarParameter("@CurrQtyOpened", newQty),
                            new SugarParameter("@UM", (object?)newDetail.Unit ?? DBNull.Value),
                            new SugarParameter("@ItemName", (object?)newDetail.ItemName ?? DBNull.Value),
                            new SugarParameter("@User", account),
                            new SugarParameter("@Now", now),
                            new SugarParameter("@RecId", existing.RecID));
                    }
                }
                else
                {
                    // 物料明细中没有，但领料单明细有 → 关闭当前领料单明细行
                    await _db.Ado.ExecuteCommandAsync(
                        """
                        UPDATE NbrDetail
                        SET Status = 'C', IsActive = 0,
                            UpdateUser = @User, UpdateTime = @Now
                        WHERE RecID = @RecId
                        """,
                        new SugarParameter("@User", account),
                        new SugarParameter("@Now", now),
                        new SugarParameter("@RecId", existing.RecID));
                }
            }

            // ── 2. 新增：物料明细中有，领料单明细没有的 ──
            var nextDetailId = await _db.Ado.GetIntAsync("SELECT IFNULL(MAX(RecID), 0) + 1 FROM NbrDetail");
            short newLine = (short)(existingDetails.Count > 0 ? existingDetails.Max(d => d.Line) + 1 : 1);

            foreach (var d in details)
            {
                var key = d.ItemNum.Trim();
                if (existingMap.ContainsKey(key))
                    continue;

                await _db.Ado.ExecuteCommandAsync(
                    """
                    INSERT INTO NbrDetail (
                        RecID, `Domain`, Type, Nbr, Line, ItemNum, Dimension1, Dimension2,
                        LocationFrom, LocationTo, QtyFrom, QtyTo, QtyOrd, QtyRec,
                        CurrQtyOpened, UM, WorkOrd, ItemName, Status,
                        IsActive, IsConfirm, IsChanged, BusinessID, NbrRecID,
                        CreateUser, CreateTime, UpdateUser, UpdateTime, tenant_id
                    ) VALUES (
                        @RecId, @Domain, 'SM', @Nbr, @Line, @ItemNum, '', '',
                        '', '', 0, 0, @QtyOrd, 0,
                        @CurrQtyOpened, @UM, @WorkOrd, @ItemName, '',
                        1, 0, 1, 0, @NbrRecId,
                        @User, @Now, @User, @Now, @TenantId
                    )
                    """,
                    new SugarParameter("@RecId", nextDetailId++),
                    new SugarParameter("@Domain", domain),
                    new SugarParameter("@Nbr", nbr.Nbr),
                    new SugarParameter("@Line", newLine++),
                    new SugarParameter("@ItemNum", d.ItemNum),
                    new SugarParameter("@QtyOrd", d.QtyRequired),
                    new SugarParameter("@CurrQtyOpened", d.QtyRequired),
                    new SugarParameter("@UM", (object?)d.Unit ?? DBNull.Value),
                    new SugarParameter("@WorkOrd", workOrd),
                    new SugarParameter("@ItemName", (object?)d.ItemName ?? DBNull.Value),
                    new SugarParameter("@NbrRecId", nbr.RecID),
                    new SugarParameter("@User", account.Length > 24 ? account[..24] : account),
                    new SugarParameter("@Now", now),
                    new SugarParameter("@TenantId", tenantId));
            }

            // ── 3. 更新领料单主记录的更新时间和数量 ──
            await _db.Ado.ExecuteCommandAsync(
                """
                UPDATE NbrMaster
                SET QtyOrd = (SELECT IFNULL(SUM(QtyOrd), 0) FROM NbrDetail
                              WHERE Nbr = @Nbr AND Type = 'SM' AND IFNULL(IsActive, 1) = 1),
                    UpdateUser = @User, UpdateTime = @Now
                WHERE RecID = @RecId
                """,
                new SugarParameter("@Nbr", nbr.Nbr),
                new SugarParameter("@User", account),
                new SugarParameter("@Now", now),
                new SugarParameter("@RecId", nbr.RecID));
        }
    }

    // ══════════════════════════════════════════════════════════════
    // 内部 DTO
    // ══════════════════════════════════════════════════════════════

    private sealed class NbrMasterRow
    {
        public int RecID { get; set; }
        public string Nbr { get; set; } = string.Empty;
        public string? Domain { get; set; }
    }

    private sealed class NbrDetailRow
    {
        public int RecID { get; set; }
        public string? ItemNum { get; set; }
        public decimal QtyOrd { get; set; }
        public decimal QtyRec { get; set; }
        public decimal CurrQtyOpened { get; set; }
        public short Line { get; set; }
    }

    private sealed class PickDetailRow
    {
        public string ItemNum { get; set; } = string.Empty;
        public decimal QtyRequired { get; set; }
        public string? Unit { get; set; }
        public string? ItemName { get; set; }
    }

    private async Task<WorkOrderSnapshotRow?> LoadWorkOrderSnapshotAsync(long tenantId, string workOrd)
    {
        var rows = await _db.Ado.SqlQueryAsync<WorkOrderSnapshotRow>(
            """
            SELECT WorkOrd, Priority, QtyOrded, DueDate, LotSerial, `Domain`
            FROM WorkOrdMaster
            WHERE tenant_id = @TenantId AND WorkOrd = @WorkOrd
            LIMIT 1
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrd", workOrd));
        return rows.FirstOrDefault();
    }

    private async Task<WorkOrderEntryLink?> LoadWorkOrderEntryLinkAsync(long tenantId, string workOrd)
    {
        var rows = await _db.Ado.SqlQueryAsync<WorkOrderEntryLinkRow>(
            """
            SELECT
                w.BusinessID AS EntryId,
                e.seorder_id AS SeOrderId,
                e.bill_no AS BillNo,
                e.entry_seq AS EntrySeq,
                e.item_number AS ItemNumber,
                e.item_name AS ItemName,
                e.specification AS Specification,
                e.unit AS Unit,
                e.bom_number AS BomNumber,
                e.qty AS Qty,
                e.plan_date AS PlanDate,
                e.sys_capacity_date AS SysCapacityDate,
                e.progress AS Progress,
                e.urgent AS Urgent,
                e.factory_id AS FactoryId,
                e.company_id AS CompanyId,
                e.tenant_id AS TenantId,
                o.Id AS OrderId,
                o.bill_no AS OrderBillNo,
                o.custom_no AS CustomNo,
                o.urgent AS OrderUrgent,
                o.factory_id AS OrderFactoryId
            FROM WorkOrdMaster w
            INNER JOIN crm_seorderentry e ON e.Id = w.BusinessID AND e.tenant_id = w.tenant_id AND e.IsDeleted = 0
            INNER JOIN crm_seorder o ON o.Id = e.seorder_id AND o.tenant_id = e.tenant_id AND o.IsDeleted = 0
            WHERE w.tenant_id = @TenantId AND w.WorkOrd = @WorkOrd
            LIMIT 1
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrd", workOrd));

        var row = rows.FirstOrDefault();
        if (row is null || row.EntryId <= 0)
            return null;

        return new WorkOrderEntryLink
        {
            Order = new OrderWorkOrderGenerationService.OrderHeader
            {
                Id = row.OrderId,
                BillNo = row.OrderBillNo ?? row.BillNo,
                CustomNo = row.CustomNo,
                Urgent = row.OrderUrgent,
                FactoryId = row.OrderFactoryId,
                TenantId = tenantId
            },
            Entry = new OrderWorkOrderGenerationService.OrderEntryLine
            {
                Id = row.EntryId,
                SeOrderId = row.SeOrderId,
                BillNo = row.BillNo,
                EntrySeq = row.EntrySeq,
                ItemNumber = row.ItemNumber,
                ItemName = row.ItemName,
                Specification = row.Specification,
                Unit = row.Unit,
                BomNumber = row.BomNumber,
                Qty = row.Qty,
                PlanDate = row.PlanDate,
                SysCapacityDate = row.SysCapacityDate,
                Progress = row.Progress,
                Urgent = row.Urgent,
                FactoryId = row.FactoryId,
                CompanyId = row.CompanyId,
                TenantId = row.TenantId
            }
        };
    }

    private sealed class WorkOrderSnapshotRow
    {
        public string? WorkOrd { get; set; }
        public string? Priority { get; set; }
        public decimal? QtyOrded { get; set; }
        public DateTime? DueDate { get; set; }
        public string? LotSerial { get; set; }
        public string? Domain { get; set; }
    }

    private sealed class WorkOrderEntryLink
    {
        public OrderWorkOrderGenerationService.OrderHeader Order { get; set; } = new();
        public OrderWorkOrderGenerationService.OrderEntryLine Entry { get; set; } = new();
    }

    private sealed class WorkOrderEntryLinkRow
    {
        public long EntryId { get; set; }
        public long SeOrderId { get; set; }
        public string? BillNo { get; set; }
        public int? EntrySeq { get; set; }
        public string? ItemNumber { get; set; }
        public string? ItemName { get; set; }
        public string? Specification { get; set; }
        public string? Unit { get; set; }
        public string? BomNumber { get; set; }
        public decimal? Qty { get; set; }
        public DateTime? PlanDate { get; set; }
        public DateTime? SysCapacityDate { get; set; }
        public string? Progress { get; set; }
        public int? Urgent { get; set; }
        public long? FactoryId { get; set; }
        public long? CompanyId { get; set; }
        public long TenantId { get; set; }
        public long OrderId { get; set; }
        public string? OrderBillNo { get; set; }
        public string? CustomNo { get; set; }
        public int? OrderUrgent { get; set; }
        public long? OrderFactoryId { get; set; }
    }
}

public class WorkOrderPriorityRecheckInput
{
    public string Workord { get; set; } = string.Empty;
    public string? Qty { get; set; }
    public string? Instockdate { get; set; }
    public string? Priority { get; set; }
    public string Domain { get; set; } = string.Empty;
    public string? UserAccount { get; set; }
    public string? LotSerial { get; set; }
    public bool EnableCapacityConstraint { get; set; }
}