AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/Order/OrderReviewOrchestrationService.cs

using Admin.NET.Plugin.AiDOP.DataPlatform.S1Refresh;
using Admin.NET.Plugin.AiDOP.DataPlatform.Wms;
using Admin.NET.Plugin.AiDOP.Infrastructure;
using Admin.NET.Plugin.AiDOP.Production;
using Admin.NET.Plugin.AiDOP.Supply;
using Admin.NET.Plugin.AiDOP.WorkOrder;
using Microsoft.Extensions.DependencyInjection;
using Yitter.IdGenerator;

namespace Admin.NET.Plugin.AiDOP.Order;

/// <summary>
/// S1 订单评审编排：资源检查、状态更新、工单生成、运行日志。
/// </summary>
public class OrderReviewOrchestrationService : ITransient
{
    public const string ActionReview = "S1_ORDER_REVIEW";
    public const string ActionConfirm = "S1_DELIVERY_CONFIRM";
    public const string ActionRefresh = "S1_ORDER_REFRESH_PLAN";

    /// <summary>当前评审批次 run_id（雪花），用于跨工单库存占用递减；每次评审/刷新重新分配。</summary>
    private long _currentReviewBangId;

    private readonly ISqlSugarClient _db;
    private readonly UserManager _userManager;
    private readonly OrderWorkOrderGenerationService _workOrderGen;
    private readonly OrderResourceCheckService _resourceCheck;
    private readonly WorkOrderMaterialDetailSyncService _materialDetailSync;
    private readonly WorkOrderRoutingSyncService _routingSync;
    private readonly MdpRefreshDispatcher _refreshDispatcher;
    private readonly AidopActionRunLogWriter _runLog;
    private readonly ProductionScheduleGenerationService _scheduleGen;
    private readonly ProcurementPipelineService _pipeline;
    private readonly WorkOrdMesPushService _workOrdMesPush;
    private readonly MaterialRequirementCalculator _calculator;
    private readonly WorkOrderKittingCheckService _kittingCheckService;
    private readonly PickBillNbrSyncService _pickNbrSync;

    public OrderReviewOrchestrationService(
        ISqlSugarClient db,
        UserManager userManager,
        OrderWorkOrderGenerationService workOrderGen,
        OrderResourceCheckService resourceCheck,
        WorkOrderMaterialDetailSyncService materialDetailSync,
        WorkOrderRoutingSyncService routingSync,
        MdpRefreshDispatcher refreshDispatcher,
        AidopActionRunLogWriter runLog,
        ProductionScheduleGenerationService scheduleGen,
        ProcurementPipelineService pipeline,
        WorkOrdMesPushService workOrdMesPush,
        MaterialRequirementCalculator calculator,
        WorkOrderKittingCheckService kittingCheckService,
        PickBillNbrSyncService pickNbrSync)
    {
        _db = db;
        _userManager = userManager;
        _workOrderGen = workOrderGen;
        _resourceCheck = resourceCheck;
        _materialDetailSync = materialDetailSync;
        _routingSync = routingSync;
        _refreshDispatcher = refreshDispatcher;
        _runLog = runLog;
        _scheduleGen = scheduleGen;
        _pipeline = pipeline;
        _workOrdMesPush = workOrdMesPush;
        _calculator = calculator;
        _kittingCheckService = kittingCheckService;
        _pickNbrSync = pickNbrSync;
    }

    public Task<SeOrderReviewExecuteResult> ReviewAsync(IReadOnlyList<long> orderIds) =>
        ExecuteBatchAsync(ActionReview, orderIds, ReviewOneOrderAsync);

    public Task<SeOrderReviewExecuteResult> ConfirmDeliveryAsync(IReadOnlyList<long> orderIds) =>
        ExecuteBatchAsync(ActionConfirm, orderIds, ConfirmOneOrderAsync);

    public Task<SeOrderReviewExecuteResult> RefreshPlanAsync(long orderId, string? reason) =>
        ExecuteSingleAsync(ActionRefresh, orderId, async (order, result, warnings, account) =>
        {
            // ── 第0步：物料编码变更校验 ──
            await ValidateMaterialNotChangedAsync(order.Id, order.TenantId);

            // ── 第1步：使用新 run_id 写入占用（旧版本在批次结束后清理，崩溃安全） ──
            EnsureReviewBangId();

            // ── 第2步：加载可重排明细行（确认 / 再评审） ──
            var entries = await LoadReviewableEntriesAsync(order.Id, order.TenantId, ["3", "0"]);
            if (entries.Count == 0)
                throw Oops.Oh("订单没有可重排的确认/再评审明细行");

            // ── 第3步：逐条处理（工单 + 资源检查 + 领料单 + 交期更新） ──
            foreach (var entry in entries)
            {
                ValidateEntryForResourceCheck(entry);
                var wo = await _workOrderGen.CreateOrUpdateForEntryAsync(order, entry, account, warnings);
                if (wo.Created) result.WorkOrderCreatedCount++;
                else result.WorkOrderUpdatedCount++;
                result.WorkOrders.Add(wo.WorkOrd);

                var check = await _resourceCheck.RunForEntryAsync(order, entry, wo.WorkOrd, account, warnings, _currentReviewBangId);
                result.ResourceCheckCount++;
                result.ResourceCheckLineCount += check.LineCount;

                await _materialDetailSync.EnsureFromResourceCheckAsync(entry.TenantId, wo.WorkOrd, account);
                await _routingSync.EnsureFromRoutingAsync(entry.TenantId, wo.WorkOrd, account);
                await _workOrdMesPush.TryEnqueueSnapshotSafeAsync(entry.TenantId, wo.WorkOrd, warnings);

                // 当工单状态为下达/投产/暂停（R、W、S）时，更新对应领料单数据
                await UpdatePickingListForActiveWorkOrderAsync(entry.TenantId, wo.WorkOrd, account, warnings);

                // 计算齐套率并回写到领料单（NbrMaster.MFRate / NbrDetail.SatisfyQty）
                await _kittingCheckService.WriteBackMFRateAsync(entry.TenantId, wo.WorkOrd);

                // 根据资源检查结果更新明细行系统建议交期
                await UpdateEntrySysCapacityDateAsync(entry.Id, check.KittingTime, account);
            }

            // ── 第4步：更新明细行进度为3 ──
            await UpdateEntriesProgressAsync(entries.Select(e => e.Id).ToList(), "3", account);

            // ── 第4.5步：重新进行生产排程（在事务提交前） ──
            try
            {
                var scheduleResult = await _scheduleGen.GenerateAsync(order.TenantId, order.TenantId.ToString(), account);
                if (!string.IsNullOrWhiteSpace(scheduleResult.Message))
                    warnings.Add($"生产排程：{scheduleResult.Message}");
            }
            catch (Exception ex)
            {
                warnings.Add($"生产排程失败：{ex.Message}");
            }

            // ── 第4.5步：同步物料需求（MRP → PR → 采购闭环） ──
            try
            {
                var mrResult = await _pipeline.ExecuteCoreAsync(order.TenantId, account, createFromShortage: true);
                if (!string.IsNullOrWhiteSpace(mrResult.Message))
                    warnings.Add($"物料需求同步：{mrResult.Message}");
            }
            catch (Exception ex)
            {
                warnings.Add($"物料需求同步失败：{ex.Message}");
            }

            result.EntryCount = entries.Count;
            result.Message = string.IsNullOrWhiteSpace(reason)
                ? "3级计划重排完成"
                : $"3级计划重排完成：{reason.Trim()}";
        });

    private async Task<SeOrderReviewExecuteResult> ExecuteBatchAsync(
        string actionCode,
        IReadOnlyList<long> orderIds,
        Func<OrderWorkOrderGenerationService.OrderHeader, SeOrderReviewExecuteResult, List<string>, string, Task> handler)
    {
        if (orderIds is null || orderIds.Count == 0)
            throw Oops.Oh("至少选择一条订单");

        var tenantId = _userManager.TenantId > 0
            ? _userManager.TenantId
            : AidopTenantHelper.Resolve(App.HttpContext);
        var account = _userManager.Account ?? "system";
        var distinctIds = orderIds.Distinct().ToList();

        var aggregate = new SeOrderReviewExecuteResult
        {
            ActionCode = actionCode,
            OrderCount = distinctIds.Count,
            Message = "执行成功"
        };
        var allWarnings = new List<string>();
        long? firstLogId = null;

        // 评审/重排：分配新 run_id，跨本批次订单共享以正确递减库存
        if (actionCode == ActionReview || actionCode == ActionRefresh)
            EnsureReviewBangId();

        foreach (var orderId in distinctIds)
        {
            var order = await LoadOrderAsync(orderId, tenantId)
                ?? throw Oops.Oh($"订单 {orderId} 不存在或不属于当前租户");

            var runLogId = await _runLog.StartAsync(actionCode, tenantId, "crm_seorder", order.Id, order.BillNo);
            if (firstLogId is null)
                firstLogId = runLogId;

            var perOrder = new SeOrderReviewExecuteResult { ActionCode = actionCode };
            var warnings = new List<string>();

            try
            {
                await _db.Ado.BeginTranAsync();
                await handler(order, perOrder, warnings, account);
                await _db.Ado.CommitTranAsync();

                aggregate.EntryCount += perOrder.EntryCount;
                aggregate.WorkOrderCreatedCount += perOrder.WorkOrderCreatedCount;
                aggregate.WorkOrderUpdatedCount += perOrder.WorkOrderUpdatedCount;
                aggregate.WorkOrderClosedCount += perOrder.WorkOrderClosedCount;
                aggregate.ResourceCheckCount += perOrder.ResourceCheckCount;
                aggregate.ResourceCheckLineCount += perOrder.ResourceCheckLineCount;
                aggregate.WorkOrders.AddRange(perOrder.WorkOrders);
                allWarnings.AddRange(warnings);

                await _runLog.SuccessAsync(runLogId, perOrder.Message, new
                {
                    orderId = order.Id,
                    billNo = order.BillNo,
                    perOrder.EntryCount,
                    perOrder.WorkOrderCreatedCount,
                    perOrder.WorkOrderUpdatedCount,
                    workOrders = perOrder.WorkOrders,
                    warnings
                });
            }
            catch (Exception ex)
            {
                await _db.Ado.RollbackTranAsync();
                await _runLog.FailedAsync(runLogId, ex.Message, new { orderId = order.Id, billNo = order.BillNo });
                throw Oops.Oh(ex.Message);
            }
        }

        aggregate.RunLogId = firstLogId ?? 0;
        aggregate.Warnings = allWarnings.Distinct().ToList();
        aggregate.Message = BuildAggregateMessage(actionCode, aggregate);

        if ((actionCode == ActionReview || actionCode == ActionRefresh) && _currentReviewBangId > 0)
            await CleanupStaleReviewOccupyAsync(tenantId, _currentReviewBangId);

        if (actionCode == ActionReview && aggregate.ResourceCheckCount > 0)
            aggregate.Warnings.AddRange(TryTriggerMdpRefresh());

        return aggregate;
    }

    private async Task<SeOrderReviewExecuteResult> ExecuteSingleAsync(
        string actionCode,
        long orderId,
        Func<OrderWorkOrderGenerationService.OrderHeader, SeOrderReviewExecuteResult, List<string>, string, Task> handler)
    {
        var tenantId = _userManager.TenantId > 0
            ? _userManager.TenantId
            : AidopTenantHelper.Resolve(App.HttpContext);
        var account = _userManager.Account ?? "system";
        var order = await LoadOrderAsync(orderId, tenantId)
            ?? throw Oops.Oh("订单不存在或不属于当前租户");

        var result = new SeOrderReviewExecuteResult
        {
            ActionCode = actionCode,
            OrderCount = 1
        };
        var warnings = new List<string>();
        var runLogId = await _runLog.StartAsync(actionCode, tenantId, "crm_seorder", order.Id, order.BillNo);
        result.RunLogId = runLogId;

        try
        {
            await _db.Ado.BeginTranAsync();
            await handler(order, result, warnings, account);
            await _db.Ado.CommitTranAsync();

            result.Warnings = warnings;
            result.Message = BuildSingleMessage(result);
            await _runLog.SuccessAsync(runLogId, result.Message, new
            {
                orderId = order.Id,
                billNo = order.BillNo,
                result.EntryCount,
                result.WorkOrderCreatedCount,
                result.WorkOrderUpdatedCount,
                workOrders = result.WorkOrders,
                warnings
            });

            if (actionCode == ActionRefresh && _currentReviewBangId > 0)
                await CleanupStaleReviewOccupyAsync(tenantId, _currentReviewBangId);

            if (actionCode == ActionRefresh && result.ResourceCheckCount > 0)
                result.Warnings.AddRange(TryTriggerMdpRefresh());

            return result;
        }
        catch (Exception ex)
        {
            await _db.Ado.RollbackTranAsync();
            await _runLog.FailedAsync(runLogId, ex.Message, new { orderId = order.Id, billNo = order.BillNo });
            throw Oops.Oh(ex.Message);
        }
    }

    private void EnsureReviewBangId()
    {
        if (_currentReviewBangId <= 0)
            _currentReviewBangId = YitIdHelper.NextId();
    }

    /// <summary>
    /// 清理本租户非当前 S1 版本、且非当前 S2 版本的占用行（保留 legacy S2 bang_id=1 直至首次 S2 全量排程切换）。
    /// </summary>
    private async Task CleanupStaleReviewOccupyAsync(long tenantId, long currentS1BangId)
    {
        var s2BangId = await _db.Ado.GetLongAsync(
            """
            SELECT id FROM aidop_action_run_log
            WHERE tenant_id = @TenantId AND action_code = 'S2_SCHEDULE_GENERATE' AND status = 'SUCCESS'
            ORDER BY start_time DESC, id DESC
            LIMIT 1
            """,
            new List<SugarParameter> { new("@TenantId", tenantId) });

        const long legacyS2BangId = 1;
        await _db.Ado.ExecuteCommandAsync(
            """
            DELETE FROM ic_item_stockoccupy
            WHERE tenant_id = @TenantId
              AND bang_id <> @S1BangId
              AND bang_id <> @S2BangId
              AND bang_id <> @LegacyS2
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@S1BangId", currentS1BangId),
            new SugarParameter("@S2BangId", s2BangId > 0 ? s2BangId : -1L),
            new SugarParameter("@LegacyS2", legacyS2BangId));
        await _db.Ado.ExecuteCommandAsync(
            """
            DELETE FROM srm_po_occupy
            WHERE tenant_id = @TenantId
              AND bang_id <> @S1BangId
              AND bang_id <> @S2BangId
              AND bang_id <> @LegacyS2
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@S1BangId", currentS1BangId),
            new SugarParameter("@S2BangId", s2BangId > 0 ? s2BangId : -1L),
            new SugarParameter("@LegacyS2", legacyS2BangId));
    }

    /// <summary>
    /// 清理本订单所有明细行的旧 status=0 预占记录（b_stock_occupy / b_purchase_occupy / b_mo_occupy）。
    /// 每次评审前调用，确保幂等。
    /// </summary>
    private async Task CleanupReviewOccupyAsync(long tenantId, List<long> entryIds)
    {
        if (entryIds.Count == 0) return;

        var pars = new List<SugarParameter> { new("@TenantId", tenantId) };
        var placeholders = new List<string>();
        for (var i = 0; i < entryIds.Count; i++)
        {
            placeholders.Add($"@e{i}");
            pars.Add(new SugarParameter($"@e{i}", entryIds[i]));
        }
        var inClause = string.Join(",", placeholders);

        // b_stock_occupy（无 IsDeleted 字段）
        try { await _db.Ado.ExecuteCommandAsync(
            $"DELETE FROM b_stock_occupy WHERE tenant_id = @TenantId AND status = 0 AND entry_id IN ({inClause})",
            pars); } catch { /* 表可能缺少 status 列，跳过 */ }

        // b_purchase_occupy（有 IsDeleted 字段）
        try { await _db.Ado.ExecuteCommandAsync(
            $"DELETE FROM b_purchase_occupy WHERE tenant_id = @TenantId AND status = 0 AND IFNULL(IsDeleted, 0) = 0 AND entry_id IN ({inClause})",
            pars); } catch { /* 表可能缺少列，跳过 */ }

        // b_mo_occupy（有 IsDeleted 字段）
        try { await _db.Ado.ExecuteCommandAsync(
            $"DELETE FROM b_mo_occupy WHERE tenant_id = @TenantId AND status = 0 AND IFNULL(IsDeleted, 0) = 0 AND entry_id IN ({inClause})",
            pars); } catch { /* 表可能缺少列，跳过 */ }
    }

    /// <summary>
    /// 交期确认时将指定明细行的三张占用表 status 从 0 更新为 1。
    /// </summary>
    private async Task ConfirmReviewOccupyAsync(long tenantId, long entryId)
    {
        var now = DateTime.Now;
        var pars = new List<SugarParameter>
        {
            new("@TenantId", tenantId),
            new("@EntryId", entryId),
            new("@Now", now)
        };

        // b_stock_occupy（无 IsDeleted 字段）
        try { await _db.Ado.ExecuteCommandAsync(
            "UPDATE b_stock_occupy SET status = 1, update_time = @Now WHERE tenant_id = @TenantId AND entry_id = @EntryId AND status = 0",
            pars); } catch { /* 表可能缺少 status 列，跳过 */ }

        // b_purchase_occupy（有 IsDeleted 字段）
        try { await _db.Ado.ExecuteCommandAsync(
            "UPDATE b_purchase_occupy SET status = 1, update_time = @Now WHERE tenant_id = @TenantId AND entry_id = @EntryId AND status = 0 AND IFNULL(IsDeleted, 0) = 0",
            pars); } catch { /* 表可能缺少列，跳过 */ }

        // b_mo_occupy（有 IsDeleted 字段）
        try { await _db.Ado.ExecuteCommandAsync(
            "UPDATE b_mo_occupy SET status = 1, update_time = @Now WHERE tenant_id = @TenantId AND entry_id = @EntryId AND status = 0 AND IFNULL(IsDeleted, 0) = 0",
            pars); } catch { /* 表可能缺少列，跳过 */ }
    }

    private async Task ReviewOneOrderAsync(
        OrderWorkOrderGenerationService.OrderHeader order,
        SeOrderReviewExecuteResult result,
        List<string> warnings,
        string account)
    {
        var entries = await LoadReviewableEntriesAsync(order.Id, order.TenantId, ["1", "0", "2"]);
        if (entries.Count == 0)
            throw Oops.Oh($"订单 {order.BillNo} 没有可评审的明细行（须为新建,评审,再评审状态）");

        // 清理本订单所有明细行的旧 status=0 预占记录（三张表）
        await CleanupReviewOccupyAsync(order.TenantId, entries.Select(e => e.Id).ToList());

        foreach (var entry in entries)
        {
            ValidateEntryForResourceCheck(entry);
            if (entry.PlanDate is null)
                throw Oops.Oh($"订单行 {entry.EntrySeq} 缺少客户要求交期（plan_date）");

            // 1. 先做资源检查（纯 BOM 展开 + 库存计算，不依赖工单），记录库存占用
            EnsureReviewBangId();
            var (check, lines) = await _resourceCheck.CheckOnlyAsync(order, entry, warnings, _currentReviewBangId);
            result.ResourceCheckCount++;
            result.ResourceCheckLineCount += check.LineCount;

            long? examineId = null;

            // 2. 有缺料 → 才生成工单并写入资源检查结果；库存可满足则跳过工单
            if (check.HasShortage)
            {
                var wo = await _workOrderGen.CreateOrUpdateForEntryAsync(order, entry, account, warnings);
                if (wo.Created) result.WorkOrderCreatedCount++;
                else result.WorkOrderUpdatedCount++;
                result.WorkOrders.Add(wo.WorkOrd);

                var checkResult = await _resourceCheck.WriteResultAsync(order, entry, wo.WorkOrd, lines, account);
                examineId = checkResult.ExamineId;
                await _materialDetailSync.EnsureFromResourceCheckAsync(entry.TenantId, wo.WorkOrd, account);
                await _routingSync.EnsureFromRoutingAsync(entry.TenantId, wo.WorkOrd, account);
                await _workOrdMesPush.TryEnqueueSnapshotSafeAsync(entry.TenantId, wo.WorkOrd, warnings);
                warnings.Add($"订单行 {entry.EntrySeq} 存在缺料（工单 {wo.WorkOrd}）");
            }
            else
            {
                // 3. 不缺料 → 若之前已生成工单则关闭（Status='C', IsActive=0）
                var closedCount = await CloseExistingWorkOrdersAsync(entry.TenantId, entry.Id, account);
                if (closedCount > 0)
                {
                    result.WorkOrderClosedCount += closedCount;
                    warnings.Add($"订单行 {entry.EntrySeq} 库存可满足，已关闭 {closedCount} 个历史工单");
                }
            }

            // 写入订单评审三表占用追踪（status=0 预占）
            await _calculator.WriteReviewOccupyAsync(
                lines, entry.TenantId, entry.Id, order.BillNo ?? "", examineId);

            await UpdateEntryAfterReviewAsync(entry.Id, check.KittingTime, account);
        }

        result.EntryCount = entries.Count;
        result.Message = $"订单 {order.BillNo} 评审完成（资源检查 {result.ResourceCheckCount} 条）";
    }

    private async Task ConfirmOneOrderAsync(
        OrderWorkOrderGenerationService.OrderHeader order,
        SeOrderReviewExecuteResult result,
        List<string> warnings,
        string account)
    {
        var entries = await LoadReviewableEntriesAsync(order.Id, order.TenantId, ["2"]);
        if (entries.Count == 0)
            throw Oops.Oh($"订单 {order.BillNo} 没有处于评审状态的明细行，请先完成订单评审");

        foreach (var entry in entries)
        {
            var woCnt = await _db.Ado.GetIntAsync(
                """
                SELECT COUNT(*) FROM WorkOrdMaster
                WHERE tenant_id = @TenantId AND BusinessID = @EntryId
                  AND LOWER(TRIM(IFNULL(Status,''))) <> 'c'
                """,
                new SugarParameter("@TenantId", entry.TenantId),
                new SugarParameter("@EntryId", entry.Id));

            // 无活跃工单：可能从未生成（库存可满足）或已关闭 → 仍允许交期确认
            var confirmDate = entry.SysCapacityDate ?? entry.PlanDate;
            // 交期确认：三张占用表 status 0 -> 1
            await ConfirmReviewOccupyAsync(entry.TenantId, entry.Id);

            await UpdateEntryAfterConfirmAsync(entry.Id, confirmDate, account);
            result.EntryCount++;

            if (woCnt > 0)
            {
                var workOrd = await LoadWorkOrdForEntryAsync(entry.TenantId, entry.Id);
                result.WorkOrders.Add(workOrd);

                // 交期确认后将工单状态设为 p，同步 OrdDate 和 DueDate
                if (!string.IsNullOrWhiteSpace(workOrd))
                {
                    var confirmDateForWo = confirmDate ?? DateTime.Now;
                    await _db.Ado.ExecuteCommandAsync(
                        """
                        UPDATE WorkOrdMaster
                        SET Status = 'p',
                            OrdDate = COALESCE(OrdDate, @OrdDate),
                            DueDate = @DueDate,
                            UpdateUser = @User, UpdateTime = @Now
                        WHERE tenant_id = @TenantId AND WorkOrd = @WorkOrd
                          AND IFNULL(TRIM(Status), '') = ''
                        """,
                        new SugarParameter("@TenantId", entry.TenantId),
                        new SugarParameter("@WorkOrd", workOrd),
                        new SugarParameter("@OrdDate", DateTime.Now.Date),
                        new SugarParameter("@DueDate", confirmDateForWo.Date),
                        new SugarParameter("@User", account),
                        new SugarParameter("@Now", DateTime.Now));

                    // 交期确认后同步工单物料明细，使其与资源检查子物料一致
                    await _materialDetailSync.EnsureFromResourceCheckAsync(entry.TenantId, workOrd, account);
                    await _workOrdMesPush.TryEnqueueSnapshotSafeAsync(entry.TenantId, workOrd, warnings);
                }
            }
            else
                warnings.Add($"订单行 {entry.EntrySeq} 无活跃工单（库存可满足或已关闭），已直接确认交期");
        }

        result.WorkOrders = result.WorkOrders.Where(x => !string.IsNullOrWhiteSpace(x)).Distinct().ToList();
        result.Message = $"订单 {order.BillNo} 交期确认完成";
    }

    private async Task<string> LoadWorkOrdForEntryAsync(long tenantId, long entryId)
    {
        var rows = await _db.Ado.SqlQueryAsync<string>(
            """
            SELECT WorkOrd FROM WorkOrdMaster
            WHERE tenant_id = @TenantId AND BusinessID = @EntryId
            ORDER BY RecID DESC LIMIT 1
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@EntryId", entryId));
        return rows.FirstOrDefault() ?? string.Empty;
    }

    /// <summary>
    /// 关闭指定订单行已有的未关闭工单（WorkOrdMaster.Status='C', IsActive=0；mes_morder.morder_state='关闭'）。
    /// 返回被关闭的工单数量。
    /// </summary>
    private async Task<int> CloseExistingWorkOrdersAsync(long tenantId, long entryId, string account)
    {
        // 查找该订单行下所有未关闭的工单号
        var openWorkOrds = await _db.Ado.SqlQueryAsync<string>(
            """
            SELECT WorkOrd FROM WorkOrdMaster
            WHERE tenant_id = @TenantId AND BusinessID = @EntryId
              AND LOWER(TRIM(IFNULL(Status,''))) <> 'c'
              AND IFNULL(IsActive, 0) = 1
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@EntryId", entryId));

        if (openWorkOrds.Count == 0) return 0;

        var workOrdList = string.Join(",", openWorkOrds.Select(w => $"'{w}'"));
        var now = DateTime.Now;

        // 关闭 WorkOrdMaster
        await _db.Ado.ExecuteCommandAsync(
            $"""
            UPDATE WorkOrdMaster
            SET Status = 'C', IsActive = 0,
                UpdateUser = @User, UpdateTime = @Now
            WHERE tenant_id = @TenantId AND BusinessID = @EntryId
              AND LOWER(TRIM(IFNULL(Status,''))) <> 'c'
              AND IFNULL(IsActive, 0) = 1
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@EntryId", entryId),
            new SugarParameter("@User", account),
            new SugarParameter("@Now", now));

        // 同步关闭 mes_morder
        await _db.Ado.ExecuteCommandAsync(
            $"""
            UPDATE mes_morder
            SET morder_state = '关闭',
                update_by_name = @User, update_time = @Now
            WHERE tenant_id = @TenantId AND morder_no IN ({workOrdList})
              AND IFNULL(morder_state, '') <> '关闭'
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@User", account),
            new SugarParameter("@Now", now));

        return openWorkOrds.Count;
    }

    private static void ValidateEntryForResourceCheck(OrderWorkOrderGenerationService.OrderEntryLine entry)
    {
        if (string.IsNullOrWhiteSpace(entry.ItemNumber))
            throw Oops.Oh($"订单行 {entry.EntrySeq} 物料编码不能为空");
        if (entry.Qty is null or <= 0)
            throw Oops.Oh($"订单行 {entry.EntrySeq} 数量必须大于 0");
        if (entry.PlanDate is null && entry.SysCapacityDate is null)
            throw Oops.Oh($"订单行 {entry.EntrySeq} 缺少计划交期");
    }

    private async Task<OrderWorkOrderGenerationService.OrderHeader?> LoadOrderAsync(long orderId, long tenantId)
    {
        var rows = await _db.Ado.SqlQueryAsync<OrderWorkOrderGenerationService.OrderHeader>(
            """
            SELECT Id, bill_no AS BillNo, custom_no AS CustomNo, urgent AS Urgent,
                   factory_id AS FactoryId, tenant_id AS TenantId
            FROM crm_seorder
            WHERE Id = @Id AND tenant_id = @TenantId AND IsDeleted = 0
            LIMIT 1
            """,
            new SugarParameter("@Id", orderId),
            new SugarParameter("@TenantId", tenantId));
        return rows.FirstOrDefault();
    }

    private async Task<List<OrderWorkOrderGenerationService.OrderEntryLine>> LoadReviewableEntriesAsync(
        long orderId,
        long tenantId,
        IReadOnlyList<string> progressList)
    {
        if (progressList.Count == 0)
            return new List<OrderWorkOrderGenerationService.OrderEntryLine>();

        var inClause = string.Join(", ", progressList.Select((_, i) => $"@P{i}"));
        var pars = new List<SugarParameter>
        {
            new("@OrderId", orderId),
            new("@TenantId", tenantId)
        };
        for (var i = 0; i < progressList.Count; i++)
            pars.Add(new SugarParameter($"@P{i}", progressList[i]));

        return await _db.Ado.SqlQueryAsync<OrderWorkOrderGenerationService.OrderEntryLine>(
            $"""
            SELECT
                Id, seorder_id AS SeOrderId, bill_no AS BillNo, entry_seq AS EntrySeq,
                item_number AS ItemNumber, item_name AS ItemName, specification AS Specification,
                unit AS Unit, bom_number AS BomNumber, qty AS Qty,
                plan_date AS PlanDate, sys_capacity_date AS SysCapacityDate,
                progress AS Progress, urgent AS Urgent,
                factory_id AS FactoryId, company_id AS CompanyId, tenant_id AS TenantId
            FROM crm_seorderentry
            WHERE seorder_id = @OrderId AND tenant_id = @TenantId AND IsDeleted = 0
              AND COALESCE(NULLIF(progress, ''), '1') IN ({inClause})
            ORDER BY entry_seq, Id
            """,
            pars);
    }

    private async Task UpdateEntryAfterReviewAsync(long entryId, DateTime? capacityDate, string account)
    {
        await _db.Ado.ExecuteCommandAsync(
            """
            UPDATE crm_seorderentry
            SET progress = '2',
                sys_capacity_date = @CapacityDate,
                update_time = @Now
            WHERE Id = @Id AND IsDeleted = 0
            """,
            new SugarParameter("@CapacityDate", capacityDate ?? (object)DBNull.Value),
            new SugarParameter("@Now", DateTime.Now),
            new SugarParameter("@Id", entryId));
    }

    private async Task UpdateEntryAfterConfirmAsync(long entryId, DateTime? confirmDate, string account)
    {
        await _db.Ado.ExecuteCommandAsync(
            """
            UPDATE crm_seorderentry
            SET progress = '3',
                date = COALESCE(date, @ConfirmDate),
                update_time = @Now
            WHERE Id = @Id AND IsDeleted = 0
            """,
            new SugarParameter("@ConfirmDate", confirmDate ?? (object)DBNull.Value),
            new SugarParameter("@Now", DateTime.Now),
            new SugarParameter("@Id", entryId));
    }

    private async Task UpdateEntriesProgressAsync(IReadOnlyList<long> entryIds, string progress, string account)
    {
        if (entryIds.Count == 0) return;
        var idList = string.Join(",", entryIds);
        await _db.Ado.ExecuteCommandAsync(
            $"""
            UPDATE crm_seorderentry
            SET progress = @Progress, update_time = @Now
            WHERE Id IN ({idList}) AND IsDeleted = 0
            """,
            new SugarParameter("@Progress", progress),
            new SugarParameter("@Now", DateTime.Now));
    }

    private static string BuildAggregateMessage(string actionCode, SeOrderReviewExecuteResult r)
    {
        var woPart = r.WorkOrders.Count > 0
            ? $"，工单：{string.Join("、", r.WorkOrders.Distinct())}"
            : string.Empty;
        var closedPart = r.WorkOrderClosedCount > 0
            ? $"、关闭 {r.WorkOrderClosedCount}"
            : string.Empty;
        return actionCode switch
        {
            ActionReview => $"评审完成 {r.OrderCount} 单、{r.EntryCount} 行，新建工单 {r.WorkOrderCreatedCount}、更新 {r.WorkOrderUpdatedCount}{closedPart}、资源检查 {r.ResourceCheckCount} 条{woPart}",
            ActionConfirm => $"交期确认完成 {r.OrderCount} 单、{r.EntryCount} 行{woPart}",
            _ => r.Message
        };
    }

    private static string BuildSingleMessage(SeOrderReviewExecuteResult r)
    {
        if (r.WorkOrders.Count == 0)
            return r.Message;
        return $"{r.Message}，工单：{string.Join("、", r.WorkOrders.Distinct())}";
    }

    private List<string> TryTriggerMdpRefresh()
    {
        if (_userManager.TenantId <= 0)
            return new List<string>();
        var tenantId = _userManager.TenantId;
        var factoryId = 1L;
        _refreshDispatcher.Enqueue("S1", async (sp, ct) =>
        {
            await sp.GetRequiredService<Admin.NET.Plugin.AiDOP.DataPlatform.MdpRebuild.ModuleRebuildService>()
                .EnqueueAsync("S1", tenantId, factoryId, null, "ORDER_REVIEW", ct);
        });
        return new List<string>();
    }

    // ══════════════════════════════════════════════════════════════
    // 3级计划重排 — 辅助方法
    // ══════════════════════════════════════════════════════════════

    /// <summary>
    /// 校验订单明细行的物料编码是否与已生成工单的物料编码一致。
    /// 若不一致则说明物料信息已变更，不允许重排。
    /// </summary>
    private async Task ValidateMaterialNotChangedAsync(long orderId, long tenantId)
    {
        var mismatches = await _db.Ado.SqlQueryAsync<dynamic>(
            """
            SELECT e.entry_seq AS EntrySeq,
                   e.item_number AS EntryItemNum,
                   w.ItemNum AS WoItemNum
            FROM crm_seorderentry e
            INNER JOIN WorkOrdMaster w
                ON w.BusinessID = e.Id AND w.tenant_id = e.tenant_id
               AND IFNULL(w.IsActive, 0) = 1
               AND LOWER(TRIM(IFNULL(w.Status, ''))) <> 'c'
            WHERE e.seorder_id = @OrderId AND e.tenant_id = @TenantId AND e.IsDeleted = 0
              AND TRIM(IFNULL(e.item_number, '')) <> ''
              AND TRIM(IFNULL(w.ItemNum, '')) <> ''
              AND TRIM(e.item_number) <> TRIM(w.ItemNum)
            """,
            new SugarParameter("@OrderId", orderId),
            new SugarParameter("@TenantId", tenantId));

        if (mismatches.Count > 0)
            throw Oops.Oh("此订单行的物料信息有变更无法重排");
    }

    /// <summary>
    /// 当工单状态为下达(R)/投产(W)/暂停(S)时，更新对应领料单明细数据。
    /// 重新从 WorkOrdDetail 汇总物料需求，覆盖 NbrDetail 中的 QtyOrd。
    /// </summary>
    private async Task UpdatePickingListForActiveWorkOrderAsync(
        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"))
            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));
        }
    }

    /// <summary>
    /// 根据资源检查结果更新明细行系统建议交期（sys_capacity_date）。
    /// </summary>
    private async Task UpdateEntrySysCapacityDateAsync(long entryId, DateTime? kittingTime, string account)
    {
        await _db.Ado.ExecuteCommandAsync(
            """
            UPDATE crm_seorderentry
            SET sys_capacity_date = @CapacityDate,
                update_time = @Now
            WHERE Id = @Id AND IsDeleted = 0
            """,
            new SugarParameter("@CapacityDate", kittingTime ?? (object)DBNull.Value),
            new SugarParameter("@Now", DateTime.Now),
            new SugarParameter("@Id", entryId));
    }

    // ══════════════════════════════════════════════════════════════
    // 3级计划重排 — 内部 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; }
    }

}