namespace Admin.NET.Plugin.AiDOP.Production;

using System.Diagnostics;
using System.Text.Json;
using Admin.NET.Plugin.AiDOP.DataPlatform;
using Admin.NET.Plugin.AiDOP.DataPlatform.Executors;
using Admin.NET.Plugin.AiDOP.DataPlatform.Wms;
using Admin.NET.Plugin.AiDOP.Entity.DataPlatform;
using Admin.NET.Plugin.AiDOP.WorkOrder;
using Yitter.IdGenerator;

/// <summary>生产排程生成：为待排工单写入 PeriodSequenceDet（工作日历 + 工作中心冲突避让），并经 Outbox 回写 165。</summary>
public class ProductionScheduleGenerationService : ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly WorkOrderKittingCheckService _kittingCheck;
    private readonly MdpOutboxEnqueueService _enqueue;
    private readonly MdpSourceScopeFactory _scopeFactory;
    private readonly WorkOrdMesPushService _workOrdMesPush;
    private readonly short _defaultPeriod;

    public ProductionScheduleGenerationService(
        ISqlSugarClient db,
        WorkOrderKittingCheckService kittingCheck,
        MdpOutboxEnqueueService enqueue,
        MdpSourceScopeFactory scopeFactory,
        WorkOrdMesPushService workOrdMesPush)
    {
        _db = db;
        _kittingCheck = kittingCheck;
        _enqueue = enqueue;
        _scopeFactory = scopeFactory;
        _workOrdMesPush = workOrdMesPush;
        _defaultPeriod = ResolveDefaultPeriod();
    }

    private static short ResolveDefaultPeriod()
    {
        try
        {
            var v = App.GetConfig<int?>("AiDOP:Schedule:DefaultPeriod", true);
            if (v is > 0 and <= short.MaxValue) return (short)v.Value;
        }
        catch
        {
            // 配置缺失时回落默认班次 1
        }

        return 1;
    }

    public const string ActionScheduleGenerate = "S2_SCHEDULE_GENERATE";

    /// <summary>产线产能排期视界（自然日）。超期仍未排完则写异常，不再静默丢量。</summary>
    private const int ScheduleHorizonDays = 1095;

    public async Task<ScheduleGenerationResult> GenerateAsync(
        long tenantId,
        string? domain,
        string account,
        bool enableCapacityConstraint = true,
        long? runId = null)
    {
        var workOrders = await LoadPendingWorkOrdersAsync(tenantId);
        if (workOrders.Count == 0)
            return new ScheduleGenerationResult { Message = "没有待排产的工单（状态 p/r）" };

        var bangId = runId is > 0 ? runId.Value : YitIdHelper.NextId();
        return await ScheduleWorkOrdersAsync(
            tenantId, domain, account, workOrders, enableCapacityConstraint,
            bangId, isFullRun: true);
    }

    public async Task<ScheduleGenerationResult> RegenerateForWorkOrderAsync(
        long tenantId,
        string workOrd,
        string? domain,
        string account,
        bool enableCapacityConstraint = true)
    {
        var rows = await _db.Ado.SqlQueryAsync<PendingWorkOrderRow>(
            """
            SELECT RecID AS RecId, WorkOrd, ItemNum, `Domain`, QtyOrded, OrdDate, DueDate, Priority, Urgent
            FROM WorkOrdMaster
            WHERE tenant_id = @TenantId AND WorkOrd = @WorkOrd
            LIMIT 1
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrd", workOrd.Trim()));

        var wo = rows.FirstOrDefault();
        if (wo is null)
            return new ScheduleGenerationResult { Message = $"工单 {workOrd} 不存在" };

        // 单工单重排：复用当前有效 S2 版本，只清本工单占用，避免洗掉全租户预留
        var bangId = await ResolveCurrentScheduleBangIdAsync(tenantId) ?? YitIdHelper.NextId();
        return await ScheduleWorkOrdersAsync(
            tenantId, domain, account, new List<PendingWorkOrderRow> { wo }, enableCapacityConstraint,
            bangId, isFullRun: false);
    }

    private async Task<long?> ResolveCurrentScheduleBangIdAsync(long tenantId)
    {
        var id = await _db.Ado.GetLongAsync(
            """
            SELECT id FROM aidop_action_run_log
            WHERE tenant_id = @TenantId AND action_code = @ActionCode AND status = 'SUCCESS'
            ORDER BY start_time DESC, id DESC
            LIMIT 1
            """,
            new List<SugarParameter>
            {
                new("@TenantId", tenantId),
                new("@ActionCode", ActionScheduleGenerate)
            });
        return id > 0 ? id : null;
    }

    private async Task<ScheduleGenerationResult> ScheduleWorkOrdersAsync(
        long tenantId,
        string? domain,
        string account,
        List<PendingWorkOrderRow> workOrders,
        bool enableCapacityConstraint,
        long scheduleBangId,
        bool isFullRun)
    {
        var totalSw = Stopwatch.StartNew();
        var now = DateTime.Now;
        var calendar = await LoadWorkCenterCalendarAsync(tenantId);
        var occupiedSlots = await LoadOccupiedSlotsAsync(tenantId);
        var usedCalendar = calendar.Count > 0;

        var scheduledCount = 0;
        var rowCount = 0;
        var frozenWorkOrderCount = 0;
        var frozenOpCount = 0;
        var skipped = new List<string>();
        var pendingPush = new List<PendingPsdPushRow>();
        // 按优先级顺序跟踪各物料已占用库存量（ItemNumber → 已占用数量）
        var consumedStock = new Dictionary<string, decimal>(StringComparer.OrdinalIgnoreCase);

        // 诊断计时
        var kittingMs = 0L;
        var otherMs = 0L;

        // 冻结期：已全发料工单作为既定约束载入，不参与重排
        var frozenWorkOrds = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
        foreach (var wo in workOrders)
        {
            if (await IsWorkOrderFullyFrozenAsync(tenantId, wo.WorkOrd))
                frozenWorkOrds.Add(wo.WorkOrd);
        }
        if (frozenWorkOrds.Count > 0)
        {
            await SeedConsumedStockFromFrozenAsync(tenantId, frozenWorkOrds, consumedStock);
            frozenWorkOrderCount = frozenWorkOrds.Count;
        }

        // H1/H3：产线产能占用从独立表加载。排除本批即将重算的非冻结工单（全量与单工单重排均排除，避免跟自己抢产能）
        var excludeFromCapacity = workOrders.Select(x => x.WorkOrd)
            .Where(x => !string.IsNullOrWhiteSpace(x) && !frozenWorkOrds.Contains(x))
            .ToHashSet(StringComparer.OrdinalIgnoreCase);
        var baseRunId = await ResolveCurrentScheduleBangIdAsync(tenantId);
        var lineCapacity = await LoadProdLineCapacityContextAsync(tenantId, baseRunId, excludeFromCapacity);
        var useLineCapacity = lineCapacity.Enabled;

        // H1：冻结工单占用结转到新 run_id（否则清理旧版本时保护消失）
        if (isFullRun && frozenWorkOrds.Count > 0 && baseRunId is > 0)
            await CarryOverFrozenCapacityOccupyAsync(tenantId, baseRunId.Value, scheduleBangId, frozenWorkOrds);

        // 占用记账：全量重排写新版本；单工单重排只清本批工单在当前版本下的占用
        if (!isFullRun)
        {
            foreach (var woNo in workOrders.Select(x => x.WorkOrd).Where(x => !string.IsNullOrWhiteSpace(x)).Distinct())
            {
                await _db.Ado.ExecuteCommandAsync(
                    """
                    DELETE FROM ic_item_stockoccupy
                    WHERE tenant_id = @TenantId AND bang_id = @BangId AND morder_mo = @WorkOrd
                    """,
                    new SugarParameter("@TenantId", tenantId),
                    new SugarParameter("@BangId", scheduleBangId),
                    new SugarParameter("@WorkOrd", woNo));
                await _db.Ado.ExecuteCommandAsync(
                    """
                    DELETE FROM srm_po_occupy
                    WHERE tenant_id = @TenantId AND bang_id = @BangId AND morder_mo = @WorkOrd
                    """,
                    new SugarParameter("@TenantId", tenantId),
                    new SugarParameter("@BangId", scheduleBangId),
                    new SugarParameter("@WorkOrd", woNo));
                await _db.Ado.ExecuteCommandAsync(
                    """
                    DELETE FROM aidop_s2_line_capacity_occupy
                    WHERE tenant_id = @TenantId AND run_id = @BangId AND work_ord = @WorkOrd
                    """,
                    new SugarParameter("@TenantId", tenantId),
                    new SugarParameter("@BangId", scheduleBangId),
                    new SugarParameter("@WorkOrd", woNo));
            }
        }

        // 号段：进入循环前一次性取 MAX，内存自增（避免逐行 MAX+1 并发冲突）
        var tidParams = new List<SugarParameter> { new("@TenantId", tenantId) };
        _periodRecIdSeq = await _db.Ado.GetLongAsync(
            "SELECT IFNULL(MAX(RecID), 0) FROM PeriodSequenceDet WHERE tenant_id = @TenantId", tidParams);
        _exceptionRecIdSeq = await _db.Ado.GetLongAsync(
            "SELECT IFNULL(MAX(RecID), 0) FROM ScheduleExceptionMaster WHERE tenant_id = @TenantId", tidParams);
        _resultOpRecIdSeq = await _db.Ado.GetLongAsync(
            "SELECT IFNULL(MAX(RecID), 0) FROM ScheduleResultOpMaster WHERE tenant_id = @TenantId", tidParams);

        var woIndex = 0;
        foreach (var wo in workOrders)
        {
            woIndex++;
            var woSw = Stopwatch.StartNew();

            if (frozenWorkOrds.Contains(wo.WorkOrd))
            {
                var keptOps = await CountActiveOpsAsync(tenantId, wo.WorkOrd);
                frozenOpCount += keptOps;
                skipped.Add($"{wo.WorkOrd}(冻结：已开工或已全发料，保留原计划)");
                woSw.Stop();
                otherMs += woSw.ElapsedMilliseconds;
                continue;
            }

            // 0. 先加载工艺路线，无路线的工单跳过齐套检查（性能优化）
            var routings = await LoadRoutingsAsync(tenantId, wo.WorkOrd, wo.ItemNum);
            if (routings.Count == 0)
            {
                skipped.Add($"{wo.WorkOrd}(无关键工序)");
                await InsertScheduleExceptionAsync(tenantId, wo, domain, "无关键工序", "工单无关键工序（MilestoneOp=1），无法生成排程");
                woSw.Stop();
                otherMs += woSw.ElapsedMilliseconds;
                if (woIndex % 5 == 0 || woIndex == workOrders.Count)
                {
                    Console.WriteLine($"[排程诊断] {woIndex}/{workOrders.Count} 工单, " +
                        $"累计: 齐套检查={kittingMs}ms 其他={otherMs}ms 总耗时={totalSw.ElapsedMilliseconds}ms, " +
                        $"本工单[{wo.WorkOrd}]=跳过(无路线)");
                }
                continue;
            }

            // 1. 执行齐套检查，刷新资源检查记录（写入 b_examine_result / b_bom_child_examine）
            var kitSw = Stopwatch.StartNew();
            try
            {
                await _kittingCheck.CheckSingleAsync(tenantId, wo.WorkOrd, account, scheduleBangId, checkScope: "SCHEDULE");
            }
            catch (Exception ex)
            {
                skipped.Add($"齐套检查失败[{wo.WorkOrd}]: {ex.Message}");
            }
            kitSw.Stop();
            kittingMs += kitSw.ElapsedMilliseconds;

            // 2. 刷新齐套数量（LocationStock），扣减已被高优先级工单占用的库存
            var locationStock = await CalcLocationStockAsync(tenantId, wo.WorkOrd, wo.QtyOrded ?? 0, consumedStock);
            if (locationStock.HasValue)
            {
                await _db.Ado.ExecuteCommandAsync(
                    """
                    UPDATE WorkOrdMaster
                    SET LocationStock = @Stock, UpdateTime = @Now
                    WHERE tenant_id = @TenantId AND WorkOrd = @WorkOrd
                    """,
                    new SugarParameter("@Stock", locationStock.Value),
                    new SugarParameter("@Now", now),
                    new SugarParameter("@TenantId", tenantId),
                    new SugarParameter("@WorkOrd", wo.WorkOrd));
            }

            // 检查各道工序的数据完整性（标准工艺路线、设备、技能）
            var missingStdOp = routings.Where(r => string.IsNullOrWhiteSpace(r.StdOp)).Select(r => r.Op).ToList();
            var missingWorkCtr = routings.Where(r => string.IsNullOrWhiteSpace(r.WorkCtr)).Select(r => r.Op).ToList();
            var missingMachine = routings.Where(r => string.IsNullOrWhiteSpace(r.Machine)).Select(r => r.Op).ToList();
            var missingEngineer = routings.Where(r => string.IsNullOrWhiteSpace(r.Engineer)).Select(r => r.Op).ToList();

            if (missingStdOp.Count > 0)
            {
                var ops = string.Join("、", missingStdOp);
                skipped.Add($"{wo.WorkOrd}(工序{ops}未查到标准工艺路线)");
                await InsertScheduleExceptionAsync(tenantId, wo, domain, "未查到标准工艺路线",
                    $"工序 {ops} 未查到标准工艺路线，排程可能不准确");
            }

            var missingEquip = missingWorkCtr.Concat(missingMachine).Distinct().ToList();
            if (missingEquip.Count > 0)
            {
                var ops = string.Join("、", missingEquip);
                skipped.Add($"{wo.WorkOrd}(工序{ops}未查到设备)");
                await InsertScheduleExceptionAsync(tenantId, wo, domain, "未查到设备",
                    $"工序 {ops} 未查到设备（WorkCtr或设备编码为空），排程可能不准确");
            }

            if (missingEngineer.Count > 0)
            {
                var ops = string.Join("、", missingEngineer);
                skipped.Add($"{wo.WorkOrd}(工序{ops}未查到技能)");
                await InsertScheduleExceptionAsync(tenantId, wo, domain, "未查到技能",
                    $"工序 {ops} 未查到技能（SkillNo为空），排程可能不准确");
            }

            var woDomain = ResolveDomain(wo.Domain, domain, tenantId);
            var startedOpKeys = await LoadStartedPsdKeysFrom165Async(wo.WorkOrd);
            await DeactivateExistingScheduleAsync(tenantId, wo.WorkOrd, account, domain, occupiedSlots, startedOpKeys);
            // 冻结工序的既有计划日（作废后仍保留 IsActive=1），用于推进游标
            var activePlanByOp = await LoadActivePlanDatesByOpAsync(tenantId, wo.WorkOrd);

            // 计划日期下界：不早于 max(今天, OrdDate)
            var planStart = MaxDate(DateTime.Today, (wo.OrdDate ?? now).Date);
            var planEnd = (wo.DueDate ?? planStart.AddDays(Math.Max(routings.Count, 7))).Date;
            if (planEnd < planStart)
                planEnd = planStart;
            var dueDate = wo.DueDate?.Date;

            var cursor = planStart;
            var seq = 1;
            for (var i = 0; i < routings.Count; i++)
            {
                var routing = routings[i];

                // 部分冻结：165 已开工工序保留原计划，不重排；游标推进到其计划日之后
                if (startedOpKeys.Count > 0 && IsOpStarted(startedOpKeys, woDomain, routing, wo))
                {
                    frozenOpCount++;
                    if (activePlanByOp.TryGetValue(routing.Op, out var frozenPlan))
                        cursor = MaxDate(cursor, frozenPlan.AddDays(1));
                    seq++;
                    continue;
                }

                var prodLine = routing.ProdLine?.Trim() ?? "";
                var rate = await ResolveProdLineRateAsync(tenantId, wo.ItemNum, routing.Op, routing);
                DateTime planDate;
                decimal? effectiveQty = null;
                CapacityAllocation? capacityAlloc = null;

                if (useLineCapacity && string.IsNullOrWhiteSpace(prodLine))
                {
                    await InsertScheduleExceptionAsync(tenantId, wo, domain, "产线为空",
                        $"工单{wo.WorkOrd}工序{routing.Op}产线(Line/ProdLine)为空，不参与产能占用");
                }

                if (useLineCapacity && !string.IsNullOrWhiteSpace(prodLine) && rate > 0)
                {
                    var qtyNeed = wo.QtyOrded ?? 0;
                    capacityAlloc = AllocateByProdLineCapacity(
                        cursor, prodLine, qtyNeed, rate, routing.SetupTime ?? 0, lineCapacity);

                    if (capacityAlloc.NoCapacityCalendar)
                    {
                        await InsertScheduleExceptionAsync(tenantId, wo, domain, "产线无可用工作日历",
                            $"产线{prodLine}无可用工作日历，工序{routing.Op}未参与产能占用");
                        capacityAlloc = null;
                    }
                }

                if (capacityAlloc != null)
                {
                    var qtyNeed = wo.QtyOrded ?? 0;
                    planDate = capacityAlloc.StartDate;
                    effectiveQty = qtyNeed;
                    if (capacityAlloc.Daily.Count > 0)
                    {
                        await InsertCapacityOccupyBatchAsync(
                            tenantId, scheduleBangId, prodLine, wo.WorkOrd, routing.Op, capacityAlloc.Daily);
                    }

                    if (capacityAlloc.Exhausted)
                    {
                        await InsertScheduleExceptionAsync(tenantId, wo, domain, "排程视界耗尽",
                            $"排程视界耗尽：工单{wo.WorkOrd}工序{routing.Op}在产线{prodLine}上 {ScheduleHorizonDays} 天内仍有 {capacityAlloc.RemainingUnallocated:0.####} 件未排入，请检查产能或数量");
                    }

                    if (dueDate.HasValue && capacityAlloc.EndDate.Date > dueDate.Value)
                    {
                        var lateDays = (capacityAlloc.EndDate.Date - dueDate.Value).Days;
                        await InsertScheduleExceptionAsync(tenantId, wo, domain, "产能不足/交期不可达",
                            $"产能不足 / 交期不可达：工单{wo.WorkOrd}工序{routing.Op}预计 {capacityAlloc.EndDate:yyyy-MM-dd} 完工，晚于交期 {dueDate:yyyy-MM-dd} 共 {lateDays} 天");
                    }

                    cursor = capacityAlloc.EndDate.AddDays(1);
                }
                else if (usedCalendar)
                {
                    planDate = ResolveNextAvailablePlanDate(cursor, routing.WorkCtr, calendar, occupiedSlots);
                    cursor = planDate.AddDays(1);
                }
                else
                {
                    var spanDays = Math.Max((planEnd - planStart).Days, 0);
                    planDate = routings.Count <= 1
                        ? planStart
                        : planStart.AddDays(spanDays * i / Math.Max(routings.Count - 1, 1));
                }

                if (planDate < DateTime.Today)
                    planDate = DateTime.Today;

                if (planDate > planEnd)
                    planEnd = planDate;

                // 产线产能占用已写入独立表；非产能路径仍用 WorkCtr 日槽避让
                if (capacityAlloc is null)
                {
                    var slotKey = BuildOccupancyKey(routing.WorkCtr, planDate);
                    if (slotKey != null)
                        occupiedSlots.Add(slotKey);
                }

                // 始终解析设备编码和技能编号（供 ScheduleResultOpMaster 写入）
                var capacityMachineCode = await ResolveMachineCodeAsync(tenantId, routing.Machine, wo.ItemNum, routing.Op);
                var capacitySkillNo = await ResolveSkillNoAsync(tenantId, routing.Engineer, wo.ItemNum, routing.Op);

                var isNonManual = !string.IsNullOrWhiteSpace(routing.WorkCode)
                    ? routing.WorkCode.Trim().ToUpperInvariant() != "P"
                    : (!string.IsNullOrWhiteSpace(routing.OpType)
                        && routing.OpType.Trim().ToUpperInvariant() != "P");

                // 非人工工序：无设备可用时写入排产异常（信息性，不阻断产线产能排期）
                if (isNonManual && string.IsNullOrWhiteSpace(capacityMachineCode))
                {
                    await InsertScheduleExceptionAsync(tenantId, wo, domain, "无设备可用",
                        $"工单{wo.WorkOrd}工序{routing.Op}无设备可用");
                }

                var equipCount = await LoadAvailableEquipmentCountAsync(tenantId, capacityMachineCode);
                int? capacityDeviceAllocation = null;
                decimal? capacityAssignedPersonnel = null;

                // 设备占用数兜底（不再用 MachBdnRate 做台数公式——量纲错误已废弃）
                {
                    var desired = routing.MachinestPerOp > 0 ? routing.MachinestPerOp.Value : 1;
                    capacityDeviceAllocation = equipCount > 0 ? Math.Min(desired, equipCount) : desired;
                }

                // enableCapacityConstraint：产线产能模式下以当日剩余产能为准（已在 Allocate 中体现）
                _ = enableCapacityConstraint;

                var recId = (int)NextPeriodRecId();
                var ordQty = effectiveQty ?? wo.QtyOrded ?? 0;
                await InsertScheduleRowAsync(
                    recId, woDomain, routing, wo, planDate, seq, account, now, tenantId,
                    capacityQty: effectiveQty);

                // 始终写入 ScheduleResultOpMaster（基础排产数据 + 可选产能分配数据）
                var resultRecId = NextScheduleResultOpRecId();
                await InsertScheduleResultOpAsync(
                    resultRecId, woDomain, routing, wo, planDate, seq, now, tenantId,
                    capacityMachineCode, capacityDeviceAllocation, capacitySkillNo, capacityAssignedPersonnel,
                    periodDetRecId: recId);

                var line = routing.ProdLine?.Trim() ?? string.Empty;
                if (string.IsNullOrWhiteSpace(line))
                    skipped.Add($"{wo.WorkOrd}(工序{routing.Op}无产线，跳过日计划回写)");
                else if (ordQty <= 0)
                    skipped.Add($"{wo.WorkOrd}(工序{routing.Op}计划数为0，跳过日计划回写)");
                else
                {
                    pendingPush.Add(new PendingPsdPushRow
                    {
                        Domain = woDomain.Length > 8 ? woDomain[..8] : woDomain,
                        Line = line,
                        ItemNum = wo.ItemNum ?? string.Empty,
                        PlanDate = planDate.Date,
                        Period = _defaultPeriod,
                        Op = routing.Op,
                        WorkOrds = wo.WorkOrd,
                        Sequence = seq,
                        OrdQty = ordQty
                    });
                }

                seq++;
                rowCount++;
            }

            scheduledCount++;

            woSw.Stop();
            otherMs += woSw.ElapsedMilliseconds - kitSw.ElapsedMilliseconds;
            // 每5个工单输出一次诊断日志
            if (woIndex % 5 == 0 || woIndex == workOrders.Count)
            {
                Console.WriteLine($"[排程诊断] {woIndex}/{workOrders.Count} 工单, " +
                    $"累计: 齐套检查={kittingMs}ms 其他={otherMs}ms 总耗时={totalSw.ElapsedMilliseconds}ms, " +
                    $"本工单[{wo.WorkOrd}]={woSw.ElapsedMilliseconds}ms");
            }
        }

        // P-031 K4：推日计划前先入队工单三人组（WOM/WOD/WOR），保证 MES APP 取数有 MilestoneOp
        var mesPushWorkOrds = pendingPush
            .Select(x => x.WorkOrds)
            .Where(x => !string.IsNullOrWhiteSpace(x))
            .Distinct(StringComparer.OrdinalIgnoreCase)
            .ToList();
        foreach (var wo in mesPushWorkOrds)
            await _workOrdMesPush.TryEnqueueSnapshotSafeAsync(tenantId, wo, skipped);

        var pushEnqueued = 0;
        try
        {
            pushEnqueued = await EnqueuePsdUpsertsAsync(tenantId, account, now, pendingPush);
        }
        catch (Exception ex)
        {
            skipped.Add($"日计划回写入队失败: {ex.Message}");
        }

        // 全量重排成功后清理本租户旧版本占用（保留本 run 与 S1 当前有效版本）
        // 含「全部冻结仅结转产能占用」的情况，否则旧 run 的占用会堆积
        if (isFullRun && (scheduledCount > 0 || frozenWorkOrderCount > 0))
            await CleanupStaleOccupyAsync(tenantId, scheduleBangId);

        // P-029 I3/I4：与 165 对账，补发孤儿生效行的作废；并汇总告警指标
        var workOrdKeys = workOrders
            .Select(x => x.WorkOrd)
            .Where(x => !string.IsNullOrWhiteSpace(x))
            .Distinct(StringComparer.OrdinalIgnoreCase)
            .ToList();
        var reconcile = await ReconcileStale165PsdAsync(
            tenantId, account, now, scheduleBangId, workOrdKeys);
        if (reconcile.Enqueued > 0)
            pushEnqueued += reconcile.Enqueued;

        try
        {
            await CleanupStalePsdDeactOutboxAsync();
        }
        catch
        {
            // 清理失败不影响排程结果
        }

        var outboxDeadCount = await CountOutboxDeadAsync(tenantId);

        totalSw.Stop();
        Console.WriteLine($"[排程诊断] 完成: {workOrders.Count}工单/{rowCount}条, " +
            $"齐套检查={kittingMs}ms 其他={otherMs}ms 总计={totalSw.ElapsedMilliseconds}ms, 日计划入队={pushEnqueued}, " +
            $"165对账差={reconcile.MismatchCount} 重复组={reconcile.DuplicateActiveCount} 兜底作废={reconcile.Enqueued}" +
            $" 缺产线={reconcile.MesLineMissingCount} 缺工序={reconcile.MesRoutingMissingCount}");

        if (reconcile.MesLineMissingCount > 0)
            skipped.Add($"165 产线主数据缺失 {reconcile.MesLineMissingCount} 行，MES APP 无法显示这些产线的计划");
        if (reconcile.MesRoutingMissingCount > 0)
            skipped.Add($"165 工单工序缺失 {reconcile.MesRoutingMissingCount} 行，MES APP「投产」列表不会出现这些工单，请检查工单三人组推送");

        var message = scheduledCount > 0
            ? $"已为 {scheduledCount} 个工单生成 {rowCount} 条工序排程，已入队 {pushEnqueued} 条日计划回写（runId={scheduleBangId}，冻结工单 {frozenWorkOrderCount}/工序 {frozenOpCount}）"
            : frozenWorkOrderCount > 0
                ? $"未生成新排程：{frozenWorkOrderCount} 个工单处于冻结期"
                : "未生成排程，请确认工单已同步工艺路线";

        return new ScheduleGenerationResult
        {
            WorkOrderCount = scheduledCount,
            ScheduleRowCount = rowCount,
            PushEnqueuedCount = pushEnqueued,
            FrozenWorkOrderCount = frozenWorkOrderCount,
            FrozenOpCount = frozenOpCount,
            SkippedWorkOrders = skipped,
            UsedWorkCenterCalendar = usedCalendar || useLineCapacity,
            RunId = scheduleBangId,
            PsdMismatchCount = reconcile.MismatchCount,
            PsdDuplicateActiveCount = reconcile.DuplicateActiveCount,
            OutboxDeadCount = outboxDeadCount,
            MesLineMissingCount = reconcile.MesLineMissingCount,
            MesRoutingMissingCount = reconcile.MesRoutingMissingCount,
            Message = message
        };
    }

    private async Task InsertScheduleRowAsync(
        int recId,
        string woDomain,
        RoutingRow routing,
        PendingWorkOrderRow wo,
        DateTime planDate,
        int seq,
        string account,
        DateTime now,
        long tenantId,
        decimal? capacityQty = null)
    {
        var ordQty = capacityQty ?? wo.QtyOrded ?? 0;
        await _db.Ado.ExecuteCommandAsync(
            """
            INSERT INTO PeriodSequenceDet (
                RecID, `Domain`, Site, ItemNum, Line, Op, WorkCtr, ProdDate, PlanDate,
                Period, Sequence, OrdQty, CompQty, WorkOrds, Status, Employee,
                CreateUser, CreateTime, UpdateUser, UpdateTime,
                IsActive, IsConfirm, BusinessID, tenant_id
            ) VALUES (
                @RecId, @Domain, @Site, @ItemNum, @Line, @Op, @WorkCtr, @ProdDate, @PlanDate,
                @Period, @Sequence, @OrdQty, 0, @WorkOrd, '', '',
                @User, @Now, @User, @Now,
                1, 0, @BusinessId, @TenantId
            )
            """,
            new SugarParameter("@RecId", recId),
            new SugarParameter("@Domain", woDomain.Length > 8 ? woDomain[..8] : woDomain),
            new SugarParameter("@Site", routing.Site ?? (object)DBNull.Value),
            new SugarParameter("@ItemNum", wo.ItemNum ?? string.Empty),
            new SugarParameter("@Line", routing.ProdLine ?? (object)DBNull.Value),
            new SugarParameter("@Op", routing.Op),
            new SugarParameter("@WorkCtr", routing.WorkCtr ?? (object)DBNull.Value),
            new SugarParameter("@ProdDate", planDate),
            new SugarParameter("@PlanDate", planDate),
            new SugarParameter("@Period", _defaultPeriod),
            new SugarParameter("@Sequence", seq),
            new SugarParameter("@OrdQty", ordQty),
            new SugarParameter("@WorkOrd", wo.WorkOrd),
            new SugarParameter("@BusinessId", wo.RecId),
            new SugarParameter("@User", account.Length > 24 ? account[..24] : account),
            new SugarParameter("@Now", now),
            new SugarParameter("@TenantId", tenantId));
    }

    private static DateTime ResolveNextAvailablePlanDate(
        DateTime start,
        string? workCtr,
        Dictionary<string, HashSet<int>> calendar,
        HashSet<string> occupiedSlots)
    {
        var date = start.Date;
        for (var guard = 0; guard < 366; guard++)
        {
            if (IsWorkDay(date, workCtr, calendar))
            {
                var key = BuildOccupancyKey(workCtr, date);
                // WorkCtr 为空时不参与占用判定，只看工作日
                if (key is null || !occupiedSlots.Contains(key))
                    return date;
            }
            date = date.AddDays(1);
        }
        return start.Date;
    }

    private static bool IsWorkDay(DateTime date, string? workCtr, Dictionary<string, HashSet<int>> calendar)
    {
        if (calendar.Count == 0)
            return true;

        // 优先按具体工作中心；无工作中心时回退到通配日历（ProdLine 维度日历播种后 WorkCtr 为空）
        var key = string.IsNullOrWhiteSpace(workCtr) ? "*" : workCtr.Trim();
        if (!calendar.TryGetValue(key, out var days) || days.Count == 0)
        {
            if (key != "*" && calendar.TryGetValue("*", out var wildcard) && wildcard.Count > 0)
                days = wildcard;
            else
                return date.DayOfWeek is not DayOfWeek.Saturday and not DayOfWeek.Sunday;
        }

        var weekDay = (int)date.DayOfWeek;
        return days.Contains(weekDay);
    }

    /// <summary>
    /// 构建产能占用键。WorkCtr 为空时返回 null（不参与占用），避免归一化为 "*" 退化为全厂单槽。
    /// </summary>
    private static string? BuildOccupancyKey(string? workCtr, DateTime date)
    {
        if (string.IsNullOrWhiteSpace(workCtr))
            return null;
        return $"{workCtr.Trim()}|{date:yyyy-MM-dd}";
    }

    private static string NormalizeWorkCtr(string? workCtr) =>
        string.IsNullOrWhiteSpace(workCtr) ? "*" : workCtr.Trim();

    /// <summary>
    /// 加载工作中心日历。
    /// 注意：ShopCalendarWorkCtr 同时被 S1（按 ProdLine + ShiftsHours1）与 S2（按 WorkCtr + IsWorkDay）读取；
    /// 改动该表前须同时评估两侧。WorkCtr 为空的行记入通配键 "*"，仅供工作日判定，不参与占用键。
    /// </summary>
    private async Task<Dictionary<string, HashSet<int>>> LoadWorkCenterCalendarAsync(long tenantId)
    {
        var rows = await _db.Ado.SqlQueryAsync<CalendarRow>(
            """
            SELECT TRIM(IFNULL(WorkCtr,'')) AS WorkCtr, WeekDay
            FROM ShopCalendarWorkCtr
            WHERE tenant_id = @TenantId
              AND IFNULL(IsActive, 0) = 1
              AND IFNULL(IsWorkDay, 0) = 1
              AND WeekDay IS NOT NULL
            """,
            new SugarParameter("@TenantId", tenantId));

        var map = new Dictionary<string, HashSet<int>>(StringComparer.OrdinalIgnoreCase);
        foreach (var row in rows)
        {
            if (row.WeekDay is null) continue;
            var key = NormalizeWorkCtr(row.WorkCtr);
            if (!map.TryGetValue(key, out var set))
            {
                set = new HashSet<int>();
                map[key] = set;
            }
            set.Add(row.WeekDay.Value);
        }
        return map;
    }

    private async Task<HashSet<string>> LoadOccupiedSlotsAsync(long tenantId)
    {
        var rows = await _db.Ado.SqlQueryAsync<OccupiedSlotRow>(
            """
            SELECT TRIM(IFNULL(WorkCtr,'')) AS WorkCtr, DATE(PlanDate) AS PlanDate
            FROM PeriodSequenceDet
            WHERE tenant_id = @TenantId AND IFNULL(IsActive, 0) = 1 AND PlanDate IS NOT NULL
              AND TRIM(IFNULL(WorkCtr,'')) <> ''
            """,
            new SugarParameter("@TenantId", tenantId));

        return rows
            .Where(x => x.PlanDate.HasValue)
            .Select(x => BuildOccupancyKey(x.WorkCtr, x.PlanDate!.Value))
            .Where(x => x != null)
            .Cast<string>()
            .ToHashSet(StringComparer.OrdinalIgnoreCase);
    }

    private async Task<List<PendingWorkOrderRow>> LoadPendingWorkOrdersAsync(long tenantId)
    {
        return await _db.Ado.SqlQueryAsync<PendingWorkOrderRow>(
            """
            SELECT RecID AS RecId, WorkOrd, ItemNum, `Domain`, QtyOrded, OrdDate, DueDate, Priority, Urgent,
                   IFNULL(Status, '') AS Status
            FROM WorkOrdMaster
            WHERE tenant_id = @TenantId
              AND LOWER(TRIM(IFNULL(Status,''))) IN ('w', 's', 'r', 'p')
            ORDER BY
              CASE LOWER(TRIM(IFNULL(Status,'')))
                WHEN 'w' THEN 1
                WHEN 's' THEN 1
                WHEN 'r' THEN 2
                WHEN 'p' THEN 3
                ELSE 4
              END,
              IFNULL(Urgent, 0) DESC,
              IFNULL(Priority, 0) ASC,
              DueDate, WorkOrd
            """,
            new SugarParameter("@TenantId", tenantId));
    }

    private async Task<List<RoutingRow>> LoadRoutingsAsync(long tenantId, string workOrd, string? itemNum = null)
    {
        var routings = await _db.Ado.SqlQueryAsync<RoutingRow>(
            """
            SELECT r.OP AS Op,
                   TRIM(CASE WHEN IFNULL(r.ProdLine, '') = '' THEN IFNULL(pd.Line, '') ELSE r.ProdLine END) AS ProdLine,
                   r.WorkCtr,
                   CASE WHEN IFNULL(r.WorkCtr, '') = '' THEN IFNULL(pd.Site, '') ELSE r.WorkCtr END AS Site,
                   IFNULL(r.StdOp, '') AS StdOp,
                   IFNULL(r.Machine, '') AS Machine,
                   IFNULL(r.Engineer, '') AS Engineer,
                   IFNULL(r.RunCrew, 0) AS RunCrew,
                   IFNULL(r.MachBdnRate, 0) AS MachBdnRate,
                   IFNULL(r.MachinesperOp, 0) AS MachinestPerOp,
                   IFNULL(r.RunTime, 0) AS RunTime,
                   IFNULL(r.StdRunTime, 0) AS StdRunTime,
                   IFNULL(pd.Rate, 0) AS ProdLineRate,
                   IFNULL(r.WorkCode, '') AS WorkCode,
                   IFNULL(pd.OpType, '') AS OpType,
                   IFNULL(r.Descr, '') AS OpDescr,
                   IFNULL(r.StdSetupTime, 0) AS SetupTime
            FROM WorkOrdRouting r
            LEFT JOIN ProdLineDetail pd ON pd.Part = r.ItemNum AND pd.Op = r.OP AND pd.tenant_id = r.tenant_id
            WHERE r.tenant_id = @TenantId AND r.WorkOrd = @WorkOrd AND IFNULL(r.IsActive, 0) = 1 AND IFNULL(r.MilestoneOp, 0) = 1
            ORDER BY (r.OP + 0), r.OP
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrd", workOrd));

        // ── C# 层补充回退：若 SQL JOIN 未命中，用 WorkOrdMaster.ItemNum 直接查 ProdLineDetail ──
        var needFill = routings.Any(r => string.IsNullOrWhiteSpace(r.ProdLine)
            || string.IsNullOrWhiteSpace(r.Site) || string.IsNullOrWhiteSpace(r.OpType));
        if (needFill && !string.IsNullOrWhiteSpace(itemNum))
        {
            var fallback = await _db.Ado.SqlQueryAsync<ProdLineFallbackRow>(
                """
                SELECT Op, Line, Site, OpType
                FROM ProdLineDetail
                WHERE Part = @Part
                """,
                new SugarParameter("@Part", itemNum));
            var fbMap = fallback.ToDictionary(f => f.Op, f => f);
            foreach (var r in routings)
            {
                if (fbMap.TryGetValue(r.Op, out var fb))
                {
                    if (string.IsNullOrWhiteSpace(r.ProdLine) && !string.IsNullOrWhiteSpace(fb.Line))
                        r.ProdLine = fb.Line;
                    if (string.IsNullOrWhiteSpace(r.Site) && !string.IsNullOrWhiteSpace(fb.Site))
                        r.Site = fb.Site;
                    if (string.IsNullOrWhiteSpace(r.OpType) && !string.IsNullOrWhiteSpace(fb.OpType))
                        r.OpType = fb.OpType;
                }
            }
        }

        return routings;
    }

    private sealed class ProdLineFallbackRow
    {
        public int Op { get; set; }
        public string? Line { get; set; }
        public string? Site { get; set; }
        public string? OpType { get; set; }
    }

    public async Task DeactivateExistingScheduleAsync(
        long tenantId,
        string workOrd,
        string? account = null,
        string? domain = null,
        HashSet<string>? occupiedSlots = null,
        HashSet<string>? startedOpKeys = null)
    {
        var now = DateTime.Now;
        var user = TruncUser(account);

        // 先取出将要作废的槽位（含 WorkCtr），以便从内存占用集合中释放，避免计划逐次后漂
        var activeSlots = await _db.Ado.SqlQueryAsync<OccupiedSlotRow>(
            """
            SELECT TRIM(IFNULL(WorkCtr,'')) AS WorkCtr, DATE(PlanDate) AS PlanDate
            FROM PeriodSequenceDet
            WHERE tenant_id = @TenantId AND WorkOrds = @WorkOrd
              AND IFNULL(IsActive, 0) = 1 AND PlanDate IS NOT NULL
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrd", workOrd));

        var existing = await _db.Ado.SqlQueryAsync<PendingPsdPushRow>(
            """
            SELECT TRIM(IFNULL(`Domain`,'')) AS `Domain`,
                   TRIM(IFNULL(Line,'')) AS Line,
                   TRIM(IFNULL(ItemNum,'')) AS ItemNum,
                   DATE(PlanDate) AS PlanDate,
                   IFNULL(Period, 0) AS Period,
                   Op,
                   TRIM(IFNULL(WorkOrds,'')) AS WorkOrds,
                   IFNULL(Sequence, 0) AS Sequence,
                   IFNULL(OrdQty, 0) AS OrdQty
            FROM PeriodSequenceDet
            WHERE tenant_id = @TenantId AND WorkOrds = @WorkOrd AND IFNULL(IsActive, 0) = 1
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrd", workOrd));

        var startedKeys = startedOpKeys ?? await LoadStartedPsdKeysFrom165Async(workOrd);
        var deactivateCandidates = new List<PendingPsdPushRow>();
        var frozenRecKeys = new HashSet<string>(StringComparer.OrdinalIgnoreCase);

        foreach (var row in existing)
        {
            if (string.IsNullOrWhiteSpace(row.Line) || string.IsNullOrWhiteSpace(row.WorkOrds))
                continue;

            var key = BuildPsdBizKey(row);
            if (startedKeys.Contains(key))
            {
                frozenRecKeys.Add($"{row.Op}|{row.PlanDate:yyyy-MM-dd}|{row.Period}");
                try
                {
                    await InsertScheduleExceptionAsync(
                        tenantId,
                        new PendingWorkOrderRow
                        {
                            WorkOrd = row.WorkOrds,
                            ItemNum = row.ItemNum,
                            Domain = row.Domain
                        },
                        domain ?? row.Domain,
                        "现场已投产",
                        $"工单{row.WorkOrds}工序{row.Op}产线{row.Line}在165已投产，跳过日计划作废回写");
                }
                catch
                {
                    // 异常表写入失败不影响作废本库排程
                }

                continue;
            }

            deactivateCandidates.Add(row);
        }

        for (var i = 0; i < deactivateCandidates.Count; i++)
        {
            try
            {
                await EnqueuePsdDeactivateAsync(
                    tenantId,
                    user,
                    now,
                    deactivateCandidates[i],
                    pulse: i == deactivateCandidates.Count - 1);
            }
            catch
            {
                // Outbox 失败不影响本库作废
            }
        }

        // 仅作废非冻结工序；已开工工序保持 IsActive=1
        if (frozenRecKeys.Count == 0)
        {
            await _db.Ado.ExecuteCommandAsync(
                """
                UPDATE PeriodSequenceDet
                SET IsActive = 0, UpdateTime = @Now
                WHERE tenant_id = @TenantId AND WorkOrds = @WorkOrd AND IFNULL(IsActive, 0) = 1
                """,
                new SugarParameter("@Now", now),
                new SugarParameter("@TenantId", tenantId),
                new SugarParameter("@WorkOrd", workOrd));
        }
        else
        {
            foreach (var row in deactivateCandidates)
            {
                await _db.Ado.ExecuteCommandAsync(
                    """
                    UPDATE PeriodSequenceDet
                    SET IsActive = 0, UpdateTime = @Now
                    WHERE tenant_id = @TenantId AND WorkOrds = @WorkOrd AND Op = @Op
                      AND DATE(PlanDate) = @PlanDate AND Period = @Period AND IFNULL(IsActive, 0) = 1
                    """,
                    new SugarParameter("@Now", now),
                    new SugarParameter("@TenantId", tenantId),
                    new SugarParameter("@WorkOrd", workOrd),
                    new SugarParameter("@Op", row.Op),
                    new SugarParameter("@PlanDate", row.PlanDate.Date),
                    new SugarParameter("@Period", row.Period));
            }
        }

        // 从内存占用集合释放本工单「非冻结」旧槽位
        if (occupiedSlots != null)
        {
            foreach (var slot in activeSlots)
            {
                if (!slot.PlanDate.HasValue) continue;
                var key = BuildOccupancyKey(slot.WorkCtr, slot.PlanDate.Value);
                if (key != null)
                    occupiedSlots.Remove(key);
            }
        }

        // 清理旧的排产结果数据（冻结工单整体跳过本方法调用）
        await _db.Ado.ExecuteCommandAsync(
            """
            DELETE FROM ScheduleResultOpMaster
            WHERE tenant_id = @TenantId AND WorkOrd = @WorkOrd
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrd", workOrd));
    }

    private async Task<bool> IsWorkOrderFullyFrozenAsync(long tenantId, string workOrd)
    {
        // 条件1：领料单已全部发完
        var allIssued = await _db.Ado.GetIntAsync(
            """
            SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END
            FROM NbrMaster nm
            WHERE nm.tenant_id = @TenantId AND nm.WorkOrd = @WorkOrd
              AND nm.Type = 'SM' AND IFNULL(nm.IsActive, 0) = 1
              AND NOT EXISTS (
                  SELECT 1 FROM NbrDetail nd
                  WHERE nd.tenant_id = nm.tenant_id AND nd.Nbr = nm.Nbr
                    AND nd.Type = 'SM' AND IFNULL(nd.IsActive, 0) = 1
                    AND IFNULL(nd.QtyRec, 0) < IFNULL(nd.QtyOrd, 0)
              )
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrd", workOrd));
        // 全发料 → 整单冻结。165 已开工仅冻结对应工序（见循环内 IsOpStarted），不整单跳过。
        return allIssued > 0;
    }

    private async Task SeedConsumedStockFromFrozenAsync(
        long tenantId,
        HashSet<string> frozenWorkOrds,
        Dictionary<string, decimal> consumedStock)
    {
        foreach (var wo in frozenWorkOrds)
        {
            var rows = await _db.Ado.SqlQueryAsync<OccupyRow>(
                """
                SELECT icitem_number AS ItemNumber, IFNULL(SUM(quantity), 0) AS TotalOccupied
                FROM ic_item_stockoccupy
                WHERE tenant_id = @TenantId AND morder_mo = @WorkOrd AND IsDeleted = 0
                GROUP BY icitem_number
                """,
                new SugarParameter("@TenantId", tenantId),
                new SugarParameter("@WorkOrd", wo));
            foreach (var r in rows)
            {
                if (string.IsNullOrWhiteSpace(r.ItemNumber)) continue;
                if (!consumedStock.ContainsKey(r.ItemNumber))
                    consumedStock[r.ItemNumber] = 0m;
                consumedStock[r.ItemNumber] += r.TotalOccupied;
            }
        }
    }

    private async Task<int> CountActiveOpsAsync(long tenantId, string workOrd)
    {
        return await _db.Ado.GetIntAsync(
            """
            SELECT COUNT(*) FROM PeriodSequenceDet
            WHERE tenant_id = @TenantId AND WorkOrds = @WorkOrd AND IFNULL(IsActive, 0) = 1
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrd", workOrd));
    }

    private static bool IsOpStarted(
        HashSet<string> startedKeys,
        string woDomain,
        RoutingRow routing,
        PendingWorkOrderRow wo)
    {
        // startedKeys 的业务键含 PlanDate/Period，此处按「同工单同工序」宽松匹配（Domain|Line|Item|...|Op|WorkOrds）
        var line = routing.ProdLine?.Trim() ?? "";
        var item = wo.ItemNum ?? "";
        var domain = woDomain.Length > 8 ? woDomain[..8] : woDomain;
        var suffix = $"|{routing.Op}|{wo.WorkOrd}";
        foreach (var key in startedKeys)
        {
            if (key.EndsWith(suffix, StringComparison.OrdinalIgnoreCase)
                && key.StartsWith($"{domain}|{line}|{item}|", StringComparison.OrdinalIgnoreCase))
                return true;
        }
        return false;
    }

    private static DateTime MaxDate(DateTime a, DateTime b) => a >= b ? a : b;

    private sealed class OccupyRow
    {
        public string ItemNumber { get; set; } = string.Empty;
        public decimal TotalOccupied { get; set; }
    }

    /// <summary>产线产能上下文：日历日产能 + 当日剩余可排数量。</summary>
    private sealed class ProdLineCapacityContext
    {
        public bool Enabled { get; set; }
        /// <summary>ProdLine → WeekDay → (IsWorkDay, ShiftsHours1)</summary>
        public Dictionary<string, Dictionary<int, (bool IsWorkDay, decimal Hours)>> Calendars { get; set; } = new(StringComparer.OrdinalIgnoreCase);
        /// <summary>ProdLine → 休息分钟合计</summary>
        public Dictionary<string, decimal> RestMinutes { get; set; } = new(StringComparer.OrdinalIgnoreCase);
        /// <summary>ProdLine|yyyy-MM-dd → 已占用件数（含 setup 折合件数，仅内存）</summary>
        public Dictionary<string, decimal> UsedQty { get; set; } = new(StringComparer.OrdinalIgnoreCase);
    }

    /// <summary>产线产能分配结果（开工首日 + 完工日 + 逐日明细）。</summary>
    private sealed class CapacityAllocation
    {
        public DateTime StartDate { get; init; }
        public DateTime EndDate { get; init; }
        public List<(DateTime Day, decimal Qty)> Daily { get; init; } = new();
        public bool Exhausted { get; init; }
        public bool NoCapacityCalendar { get; init; }
        public decimal RemainingUnallocated { get; init; }
    }

    private async Task<ProdLineCapacityContext> LoadProdLineCapacityContextAsync(
        long tenantId,
        long? baseRunId,
        HashSet<string> excludeWorkOrds)
    {
        var ctx = new ProdLineCapacityContext();
        var calRows = await _db.Ado.SqlQueryAsync<ProdLineCalendarRow>(
            """
            SELECT TRIM(IFNULL(ProdLine,'')) AS ProdLine, WeekDay,
                   IFNULL(IsWorkDay, 0) AS IsWorkDay, IFNULL(ShiftsHours1, 0) AS ShiftsHours1
            FROM ShopCalendarWorkCtr
            WHERE tenant_id = @TenantId AND IFNULL(IsActive, 0) = 1
              AND TRIM(IFNULL(ProdLine,'')) <> '' AND WeekDay IS NOT NULL
            """,
            new SugarParameter("@TenantId", tenantId));

        foreach (var row in calRows)
        {
            if (string.IsNullOrWhiteSpace(row.ProdLine) || row.WeekDay is null) continue;
            if (!ctx.Calendars.TryGetValue(row.ProdLine, out var days))
            {
                days = new Dictionary<int, (bool, decimal)>();
                ctx.Calendars[row.ProdLine] = days;
            }
            days[row.WeekDay.Value] = (row.IsWorkDay == 1, row.ShiftsHours1);
        }

        ctx.Enabled = ctx.Calendars.Values.Any(d => d.Count == 7);
        if (!ctx.Enabled)
            return ctx;

        var restRows = await _db.Ado.SqlQueryAsync<ProdLineRestRow>(
            """
            SELECT TRIM(IFNULL(ProdLine,'')) AS ProdLine, IFNULL(SUM(RestTime), 0) AS RestTime
            FROM QualityLineWorkDetail
            WHERE tenant_id = @TenantId AND IFNULL(IsActive, 0) = 1
              AND TRIM(IFNULL(ProdLine,'')) <> ''
            GROUP BY TRIM(IFNULL(ProdLine,''))
            """,
            new SugarParameter("@TenantId", tenantId));
        foreach (var r in restRows)
            ctx.RestMinutes[r.ProdLine] = r.RestTime;

        // H1：占用从独立表按 run_id 加载（首轮空表视为全空闲，跑完一轮即自愈）
        if (baseRunId is > 0)
        {
            var pars = new List<SugarParameter>
            {
                new("@TenantId", tenantId),
                new("@BaseRunId", baseRunId.Value)
            };
            var sql = """
                SELECT prod_line AS ProdLine, plan_date AS PlanDate,
                       IFNULL(SUM(qty), 0) AS UsedQty
                FROM aidop_s2_line_capacity_occupy
                WHERE tenant_id = @TenantId AND run_id = @BaseRunId
                """;
            if (excludeWorkOrds.Count > 0)
            {
                var names = new List<string>();
                var idx = 0;
                foreach (var wo in excludeWorkOrds)
                {
                    var p = $"@Ex{idx++}";
                    names.Add(p);
                    pars.Add(new SugarParameter(p, wo));
                }
                sql += $" AND work_ord NOT IN ({string.Join(",", names)})";
            }
            sql += " GROUP BY prod_line, plan_date";

            var usedRows = await _db.Ado.SqlQueryAsync<ProdLineUsedRow>(sql, pars);
            foreach (var u in usedRows)
            {
                if (!u.PlanDate.HasValue || string.IsNullOrWhiteSpace(u.ProdLine)) continue;
                var key = $"{u.ProdLine}|{u.PlanDate:yyyy-MM-dd}";
                if (!ctx.UsedQty.ContainsKey(key))
                    ctx.UsedQty[key] = 0m;
                ctx.UsedQty[key] += u.UsedQty;
            }
        }

        return ctx;
    }

    private async Task CarryOverFrozenCapacityOccupyAsync(
        long tenantId,
        long baseRunId,
        long newRunId,
        HashSet<string> frozenWorkOrds)
    {
        if (frozenWorkOrds.Count == 0 || baseRunId == newRunId)
            return;

        var pars = new List<SugarParameter>
        {
            new("@TenantId", tenantId),
            new("@BaseRunId", baseRunId),
            new("@NewRunId", newRunId)
        };
        var names = new List<string>();
        var idx = 0;
        foreach (var wo in frozenWorkOrds)
        {
            var p = $"@Fw{idx++}";
            names.Add(p);
            pars.Add(new SugarParameter(p, wo));
        }

        await _db.Ado.ExecuteCommandAsync(
            $"""
            INSERT INTO aidop_s2_line_capacity_occupy
                (tenant_id, run_id, prod_line, plan_date, work_ord, op, qty)
            SELECT tenant_id, @NewRunId, prod_line, plan_date, work_ord, op, qty
            FROM aidop_s2_line_capacity_occupy
            WHERE tenant_id = @TenantId AND run_id = @BaseRunId
              AND work_ord IN ({string.Join(",", names)})
            """,
            pars);
    }

    private async Task InsertCapacityOccupyBatchAsync(
        long tenantId,
        long runId,
        string prodLine,
        string workOrd,
        int op,
        List<(DateTime Day, decimal Qty)> daily)
    {
        if (daily.Count == 0) return;

        const int chunkSize = 100;
        for (var offset = 0; offset < daily.Count; offset += chunkSize)
        {
            var chunk = daily.Skip(offset).Take(chunkSize).ToList();
            var pars = new List<SugarParameter>
            {
                new("@TenantId", tenantId),
                new("@RunId", runId),
                new("@ProdLine", prodLine),
                new("@WorkOrd", workOrd),
                new("@Op", op)
            };
            var values = new List<string>();
            for (var j = 0; j < chunk.Count; j++)
            {
                values.Add($"(@TenantId, @RunId, @ProdLine, @PlanDate{j}, @WorkOrd, @Op, @Qty{j})");
                pars.Add(new SugarParameter($"@PlanDate{j}", chunk[j].Day.Date));
                pars.Add(new SugarParameter($"@Qty{j}", chunk[j].Qty));
            }

            await _db.Ado.ExecuteCommandAsync(
                $"""
                INSERT INTO aidop_s2_line_capacity_occupy
                    (tenant_id, run_id, prod_line, plan_date, work_ord, op, qty)
                VALUES {string.Join(",\n", values)}
                """,
                pars);
        }
    }

    private async Task<Dictionary<int, DateTime>> LoadActivePlanDatesByOpAsync(long tenantId, string workOrd)
    {
        var rows = await _db.Ado.SqlQueryAsync<ActivePlanDateRow>(
            """
            SELECT Op, DATE(PlanDate) AS PlanDate
            FROM PeriodSequenceDet
            WHERE tenant_id = @TenantId AND WorkOrds = @WorkOrd
              AND IFNULL(IsActive, 0) = 1 AND PlanDate IS NOT NULL
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrd", workOrd));

        var map = new Dictionary<int, DateTime>();
        foreach (var r in rows)
        {
            if (!r.PlanDate.HasValue) continue;
            // 同工序多行时取最晚计划日作为完工约束
            if (!map.TryGetValue(r.Op, out var existing) || r.PlanDate.Value > existing)
                map[r.Op] = r.PlanDate.Value.Date;
        }
        return map;
    }

    private static decimal CalcDailyCapacityPieces(ProdLineCapacityContext ctx, string prodLine, DateTime date, decimal rate)
    {
        if (rate <= 0) return 0;
        if (!ctx.Calendars.TryGetValue(prodLine, out var days))
            return 0;
        var weekDay = (int)date.DayOfWeek;
        if (!days.TryGetValue(weekDay, out var cal) || !cal.IsWorkDay)
            return 0;
        var rest = ctx.RestMinutes.TryGetValue(prodLine, out var rm) ? rm : 0m;
        var workMinutes = cal.Hours * 60m - rest;
        if (workMinutes <= 0) return 0;
        // H6：小数累计，跨日自然结转（不再 Math.Ceiling）
        return workMinutes / 60m * rate;
    }

    private static CapacityAllocation AllocateByProdLineCapacity(
        DateTime start,
        string prodLine,
        decimal qtyNeed,
        decimal rate,
        decimal setupHours,
        ProdLineCapacityContext ctx)
    {
        var startDate = start.Date < DateTime.Today ? DateTime.Today : start.Date;

        if (!ctx.Calendars.TryGetValue(prodLine, out var days) || !days.Values.Any(d => d.IsWorkDay))
        {
            return new CapacityAllocation
            {
                StartDate = startDate,
                EndDate = startDate,
                NoCapacityCalendar = true,
                RemainingUnallocated = qtyNeed
            };
        }

        if (qtyNeed <= 0)
        {
            return new CapacityAllocation
            {
                StartDate = startDate,
                EndDate = startDate
            };
        }

        var date = startDate;
        var remaining = qtyNeed;
        DateTime? firstDay = null;
        DateTime? lastDay = null;
        var daily = new List<(DateTime Day, decimal Qty)>();
        var setupApplied = false;

        for (var guard = 0; guard < ScheduleHorizonDays && remaining > 0; guard++)
        {
            var dayCap = CalcDailyCapacityPieces(ctx, prodLine, date, rate);
            if (dayCap <= 0)
            {
                date = date.AddDays(1);
                continue;
            }

            var key = $"{prodLine}|{date:yyyy-MM-dd}";
            var alreadyUsed = ctx.UsedQty.TryGetValue(key, out var used) ? used : 0m;
            var setupCost = (!setupApplied && setupHours > 0) ? setupHours * rate : 0m;
            var remainingCap = dayCap - alreadyUsed - setupCost;
            if (remainingCap <= 0)
            {
                date = date.AddDays(1);
                continue;
            }

            var take = Math.Min(remaining, remainingCap);
            // 内存占用含 setup 折合，避免后续工单误抢；落库只记业务件数
            ctx.UsedQty[key] = alreadyUsed + take + setupCost;
            if (!setupApplied && setupCost > 0)
                setupApplied = true;

            remaining -= take;
            firstDay ??= date;
            lastDay = date;
            daily.Add((date, take));

            if (remaining > 0)
                date = date.AddDays(1);
        }

        return new CapacityAllocation
        {
            StartDate = firstDay ?? startDate,
            EndDate = lastDay ?? (firstDay ?? startDate),
            Daily = daily,
            Exhausted = remaining > 0,
            RemainingUnallocated = remaining
        };
    }

    private async Task<decimal> ResolveProdLineRateAsync(long tenantId, string? itemNum, int op, RoutingRow routing)
    {
        if (routing.ProdLineRate > 0)
            return routing.ProdLineRate;
        if (routing.StdRunTime is > 0)
            return 1m / routing.StdRunTime.Value;
        if (routing.MachBdnRate is > 0)
            return routing.MachBdnRate.Value;

        if (string.IsNullOrWhiteSpace(itemNum))
            return 0;

        return await _db.Ado.GetDecimalAsync(
            """
            SELECT IFNULL(Rate, 0) FROM ProdLineDetail
            WHERE tenant_id = @TenantId AND Part = @Part AND Op = @Op
            LIMIT 1
            """,
            new List<SugarParameter>
            {
                new("@TenantId", tenantId),
                new("@Part", itemNum),
                new("@Op", op)
            });
    }

    private sealed class ProdLineCalendarRow
    {
        public string ProdLine { get; set; } = string.Empty;
        public int? WeekDay { get; set; }
        public int IsWorkDay { get; set; }
        public decimal ShiftsHours1 { get; set; }
    }

    private sealed class ProdLineRestRow
    {
        public string ProdLine { get; set; } = string.Empty;
        public decimal RestTime { get; set; }
    }

    private sealed class ProdLineUsedRow
    {
        public string ProdLine { get; set; } = string.Empty;
        public DateTime? PlanDate { get; set; }
        public decimal UsedQty { get; set; }
    }

    private sealed class ActivePlanDateRow
    {
        public int Op { get; set; }
        public DateTime? PlanDate { get; set; }
    }

    private static string ResolveDomain(string? woDomain, string? requestDomain, long tenantId)
    {
        if (!string.IsNullOrWhiteSpace(woDomain))
            return woDomain.Trim();
        if (!string.IsNullOrWhiteSpace(requestDomain))
            return requestDomain.Trim();
        return tenantId.ToString();
    }

    private long _periodRecIdSeq;
    private long _exceptionRecIdSeq;
    private long _resultOpRecIdSeq;

    private long NextPeriodRecId() => ++_periodRecIdSeq;
    private long NextScheduleExceptionRecId() => ++_exceptionRecIdSeq;
    private long NextScheduleResultOpRecId() => ++_resultOpRecIdSeq;

    /// <summary>
    /// 清理本租户非当前 S2 版本、且非当前 S1 版本的占用行。崩溃时旧版本仍在，切换后才清。
    /// </summary>
    private async Task CleanupStaleOccupyAsync(long tenantId, long currentS2BangId)
    {
        var s1BangId = await _db.Ado.GetLongAsync(
            """
            SELECT id FROM aidop_action_run_log
            WHERE tenant_id = @TenantId
              AND action_code IN ('S1_ORDER_REVIEW', 'S1_ORDER_REFRESH_PLAN', 'S1_DELIVERY_CONFIRM')
              AND status = 'SUCCESS'
            ORDER BY start_time DESC, id DESC
            LIMIT 1
            """,
            new List<SugarParameter> { new("@TenantId", tenantId) });

        // 兼容尚未切换到 run_id 的旧常量批次
        const long legacyS1BangId = 2;

        await _db.Ado.ExecuteCommandAsync(
            """
            DELETE FROM ic_item_stockoccupy
            WHERE tenant_id = @TenantId
              AND bang_id <> @S2BangId
              AND bang_id <> @S1BangId
              AND bang_id <> @LegacyS1
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@S2BangId", currentS2BangId),
            new SugarParameter("@S1BangId", s1BangId > 0 ? s1BangId : -1L),
            new SugarParameter("@LegacyS1", legacyS1BangId));
        await _db.Ado.ExecuteCommandAsync(
            """
            DELETE FROM srm_po_occupy
            WHERE tenant_id = @TenantId
              AND bang_id <> @S2BangId
              AND bang_id <> @S1BangId
              AND bang_id <> @LegacyS1
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@S2BangId", currentS2BangId),
            new SugarParameter("@S1BangId", s1BangId > 0 ? s1BangId : -1L),
            new SugarParameter("@LegacyS1", legacyS1BangId));

        // H1：产能占用表仅有 S2 版本，切换成功后清理非当前 run
        await _db.Ado.ExecuteCommandAsync(
            """
            DELETE FROM aidop_s2_line_capacity_occupy
            WHERE tenant_id = @TenantId AND run_id <> @S2BangId
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@S2BangId", currentS2BangId));
    }

    /// <summary>
    /// 查询可用设备数量（仅排产设备）。
    /// 支持复合编码："/" 分隔表示任一可用（SUM），"+" 分隔表示需同时使用（MIN）。
    /// </summary>
    private async Task<int> LoadAvailableEquipmentCountAsync(long tenantId, string? machineCode)
    {
        if (string.IsNullOrWhiteSpace(machineCode)) return 0;

        var code = machineCode.Trim();

        // "+" 分隔：需要所有设备同时使用 → 取 MIN
        if (code.Contains('+'))
        {
            var parts = code.Split('+', StringSplitOptions.RemoveEmptyEntries)
                            .Select(s => s.Trim()).Where(s => s.Length > 0).ToArray();
            if (parts.Length == 0) return 0;
            var counts = new List<int>();
            foreach (var part in parts)
            {
                var c = await CountSingleEquipmentAsync(part);
                counts.Add(c);
            }
            return counts.Count > 0 ? counts.Min() : 0;
        }

        // "/" 分隔：任一设备可用 → 取 SUM
        if (code.Contains('/'))
        {
            var parts = code.Split('/', StringSplitOptions.RemoveEmptyEntries)
                            .Select(s => s.Trim()).Where(s => s.Length > 0).ToArray();
            if (parts.Length == 0) return 0;
            var total = 0;
            foreach (var part in parts)
            {
                total += await CountSingleEquipmentAsync(part);
            }
            return total;
        }

        // 单设备编码
        return await CountSingleEquipmentAsync(code);
    }

    /// <summary>查询单个设备编码的可用设备数量。</summary>
    private async Task<int> CountSingleEquipmentAsync(string code)
    {
        return await _db.Ado.GetIntAsync(
            """
            SELECT COUNT(*) FROM EquipmentList
            WHERE InternalEquipmentCode = @Code AND IFNULL(IsSchedulable, 0) = 1
            """,
            new SugarParameter("@Code", code));
    }

    /// <summary>
    /// 查询可用人员数量（持有指定技能的人员）。
    /// 支持复合编码："/" 分隔表示任一技能可用（SUM），"+" 分隔表示需同时持有（MIN）。
    /// </summary>
    private async Task<int> LoadAvailablePersonnelCountAsync(long tenantId, string? skillNo)
    {
        if (string.IsNullOrWhiteSpace(skillNo)) return 0;

        var code = skillNo.Trim();

        // "+" 分隔：需要同时持有所有技能 → 取 MIN
        if (code.Contains('+'))
        {
            var parts = code.Split('+', StringSplitOptions.RemoveEmptyEntries)
                            .Select(s => s.Trim()).Where(s => s.Length > 0).ToArray();
            if (parts.Length == 0) return 0;
            var counts = new List<int>();
            foreach (var part in parts)
            {
                var c = await CountSingleSkillAsync(tenantId, part);
                counts.Add(c);
            }
            return counts.Count > 0 ? counts.Min() : 0;
        }

        // "/" 分隔：任一技能可用 → 取 SUM
        if (code.Contains('/'))
        {
            var parts = code.Split('/', StringSplitOptions.RemoveEmptyEntries)
                            .Select(s => s.Trim()).Where(s => s.Length > 0).ToArray();
            if (parts.Length == 0) return 0;
            var total = 0;
            foreach (var part in parts)
            {
                total += await CountSingleSkillAsync(tenantId, part);
            }
            return total;
        }

        // 单技能编码
        return await CountSingleSkillAsync(tenantId, code);
    }

    /// <summary>查询持有单个技能编码的人员数量。</summary>
    private async Task<int> CountSingleSkillAsync(long tenantId, string skillNo)
    {
        return await _db.Ado.GetIntAsync(
            "SELECT COUNT(*) FROM EmpSkills WHERE SkillNo = @SkillNo AND tenant_id = @TenantId",
            new SugarParameter("@SkillNo", skillNo),
            new SugarParameter("@TenantId", tenantId));
    }

    /// <summary>解析设备编码：WorkOrdRouting.Machine 为空时回退查 ProdLineDetail。</summary>
    private async Task<string?> ResolveMachineCodeAsync(long tenantId, string? machine, string? itemNum, int op)
    {
        if (!string.IsNullOrWhiteSpace(machine)) return machine.Trim();
        if (string.IsNullOrWhiteSpace(itemNum)) return null;
        return await _db.Ado.GetStringAsync(
            """
            SELECT InternalEquipmentCode FROM ProdLineDetail
            WHERE `Part` = @ItemNum AND `Op` = @Op AND tenant_id = @TenantId LIMIT 1
            """,
            new SugarParameter("@ItemNum", itemNum),
            new SugarParameter("@Op", op),
            new SugarParameter("@TenantId", tenantId));
    }

    /// <summary>解析技能编码：WorkOrdRouting.Engineer 为空时回退查 ProdLineDetail。</summary>
    private async Task<string?> ResolveSkillNoAsync(long tenantId, string? engineer, string? itemNum, int op)
    {
        if (!string.IsNullOrWhiteSpace(engineer)) return engineer.Trim();
        if (string.IsNullOrWhiteSpace(itemNum)) return null;
        return await _db.Ado.GetStringAsync(
            """
            SELECT SkillNo FROM ProdLineDetail
            WHERE `Part` = @ItemNum AND `Op` = @Op AND tenant_id = @TenantId LIMIT 1
            """,
            new SugarParameter("@ItemNum", itemNum),
            new SugarParameter("@Op", op),
            new SugarParameter("@TenantId", tenantId));
    }

    private async Task InsertScheduleExceptionAsync(
        long tenantId,
        PendingWorkOrderRow wo,
        string? domain,
        string type,
        string remark)
    {
        var recId = NextScheduleExceptionRecId();
        var woDomain = ResolveDomain(wo.Domain, domain, tenantId);
        await _db.Ado.ExecuteCommandAsync(
            """
            INSERT INTO ScheduleExceptionMaster (
                RecID, `Domain`, WorkOrd, ItemNum, CreateTime, Remark, Type, OptTime, tenant_id
            ) VALUES (
                @RecId, @Domain, @WorkOrd, @ItemNum, @CreateTime, @Remark, @Type, @OptTime, @TenantId
            )
            """,
            new SugarParameter("@RecId", recId),
            new SugarParameter("@Domain", woDomain.Length > 8 ? woDomain[..8] : woDomain),
            new SugarParameter("@WorkOrd", wo.WorkOrd),
            new SugarParameter("@ItemNum", wo.ItemNum ?? string.Empty),
            new SugarParameter("@CreateTime", DateTime.Now),
            new SugarParameter("@Remark", remark),
            new SugarParameter("@Type", type),
            new SugarParameter("@OptTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm")),
            new SugarParameter("@TenantId", tenantId));
    }

    /// <summary>写入排产结果（ScheduleResultOpMaster），包含设备/人员产能分配数据。</summary>
    private async Task InsertScheduleResultOpAsync(
        long recId,
        string woDomain,
        RoutingRow routing,
        PendingWorkOrderRow wo,
        DateTime planDate,
        int seq,
        DateTime now,
        long tenantId,
        string? machineCode,
        int? deviceAllocationCount,
        string? skillNo,
        decimal? assignedPersonnelCount,
        long? periodDetRecId)
    {
        await _db.Ado.ExecuteCommandAsync(
            """
            INSERT INTO ScheduleResultOpMaster (
                RecID, `Domain`, WorkOrd, WorkCtr, Line, ItemNum, Op,
                WorkDate, WorkQty, WorkSort, OpDescr, SetupTime, WorkActivateTime,
                InternalEquipmentCode, DeviceAllocationCount, MoldAllocationCount,
                SkillNo, AssignedPersonnelCount,
                PeriodDetRecId,
                Remark, CreateTime, tenant_id
            ) VALUES (
                @RecId, @Domain, @WorkOrd, @WorkCtr, @Line, @ItemNum, @Op,
                @WorkDate, @WorkQty, @WorkSort, @OpDescr, @SetupTime, @WorkActivateTime,
                @InternalEquipmentCode, @DeviceAllocationCount, @MoldAllocationCount,
                @SkillNo, @AssignedPersonnelCount,
                @PeriodDetRecId,
                @Remark, @CreateTime, @TenantId
            )
            """,
            new SugarParameter("@RecId", recId),
            new SugarParameter("@Domain", woDomain.Length > 8 ? woDomain[..8] : woDomain),
            new SugarParameter("@WorkOrd", wo.WorkOrd),
            new SugarParameter("@WorkCtr", routing.WorkCtr ?? (object)DBNull.Value),
            new SugarParameter("@Line", routing.ProdLine ?? (object)DBNull.Value),
            new SugarParameter("@ItemNum", wo.ItemNum ?? string.Empty),
            new SugarParameter("@Op", routing.Op),
            new SugarParameter("@WorkDate", planDate),
            new SugarParameter("@WorkQty", wo.QtyOrded ?? 0),
            new SugarParameter("@WorkSort", seq),
            new SugarParameter("@OpDescr", string.IsNullOrWhiteSpace(routing.OpDescr) ? (object)DBNull.Value : routing.OpDescr.Trim()),
            new SugarParameter("@SetupTime", routing.SetupTime ?? (object)DBNull.Value),
            new SugarParameter("@WorkActivateTime", now),
            new SugarParameter("@InternalEquipmentCode", string.IsNullOrWhiteSpace(machineCode) ? (object)DBNull.Value : machineCode.Trim()),
            new SugarParameter("@DeviceAllocationCount", deviceAllocationCount ?? (object)DBNull.Value),
            new SugarParameter("@MoldAllocationCount", deviceAllocationCount ?? (object)DBNull.Value),
            new SugarParameter("@SkillNo", string.IsNullOrWhiteSpace(skillNo) ? (object)DBNull.Value : skillNo.Trim()),
            new SugarParameter("@AssignedPersonnelCount", assignedPersonnelCount ?? (object)DBNull.Value),
            new SugarParameter("@PeriodDetRecId", periodDetRecId ?? (object)DBNull.Value),
            new SugarParameter("@Remark", string.Empty),
            new SugarParameter("@CreateTime", now),
            new SugarParameter("@TenantId", tenantId));
    }

    public sealed class ScheduleGenerationResult
    {
        public int WorkOrderCount { get; set; }
        public int ScheduleRowCount { get; set; }
        public int PushEnqueuedCount { get; set; }
        public int FrozenWorkOrderCount { get; set; }
        public int FrozenOpCount { get; set; }
        public bool UsedWorkCenterCalendar { get; set; }
        public long RunId { get; set; }
        /// <summary>165 生效行数 − 本库生效行数（本批工单范围）。</summary>
        public int PsdMismatchCount { get; set; }
        /// <summary>165 上同 (工单,工序) 生效行 &gt; 1 的组数。</summary>
        public int PsdDuplicateActiveCount { get; set; }
        /// <summary>本轮结束时该租户 outbox status=2 条数。</summary>
        public int OutboxDeadCount { get; set; }
        /// <summary>P-031：本批日计划中 165 产线缺失或未激活的行数。</summary>
        public int MesLineMissingCount { get; set; }
        /// <summary>P-031：本批日计划中 165 缺 MilestoneOp=1 工序的行数。</summary>
        public int MesRoutingMissingCount { get; set; }
        public List<string> SkippedWorkOrders { get; set; } = new();
        public string Message { get; set; } = string.Empty;
    }

    private sealed class PendingPsdPushRow
    {
        public string Domain { get; set; } = string.Empty;
        public string Line { get; set; } = string.Empty;
        public string ItemNum { get; set; } = string.Empty;
        public DateTime PlanDate { get; set; }
        public short Period { get; set; }
        public int Op { get; set; }
        public string WorkOrds { get; set; } = string.Empty;
        public int Sequence { get; set; }
        public decimal OrdQty { get; set; }
    }

    private async Task<int> EnqueuePsdUpsertsAsync(
        long tenantId,
        string account,
        DateTime now,
        List<PendingPsdPushRow> rows)
    {
        if (rows.Count == 0) return 0;

        var user = TruncUser(account);
        var nowText = now.ToString("yyyy-MM-dd HH:mm:ss");
        var enqueued = 0;

        for (var i = 0; i < rows.Count; i++)
        {
            var row = rows[i];
            var planDateText = row.PlanDate.ToString("yyyy-MM-dd");
            var domain = Trunc(row.Domain, 8);
            var keys = new Dictionary<string, object?>
            {
                ["Domain"] = domain,
                ["Line"] = row.Line,
                ["ItemNum"] = row.ItemNum,
                ["PlanDate"] = planDateText,
                ["Period"] = row.Period,
                ["Op"] = row.Op,
                ["WorkOrds"] = row.WorkOrds
            };
            var insert = new Dictionary<string, object?>
            {
                ["Site"] = domain,
                ["Domain"] = domain,
                ["Line"] = row.Line,
                ["ItemNum"] = row.ItemNum,
                ["Op"] = row.Op,
                ["WorkOrds"] = row.WorkOrds,
                ["PlanDate"] = planDateText,
                ["ProdDate"] = planDateText,
                ["Period"] = row.Period,
                ["Sequence"] = row.Sequence,
                ["OrdQty"] = row.OrdQty,
                ["ProdQty"] = row.OrdQty,
                ["CompQty"] = 0,
                ["RejectQty"] = 0,
                ["ScrapQty"] = 0,
                ["BusinessID"] = 0,
                ["Status"] = "",
                ["Employee"] = "",
                ["IsActive"] = 1,
                ["IsConfirm"] = 0,
                ["CreateUser"] = user,
                ["UpdateUser"] = user,
                ["CreateTime"] = nowText,
                ["UpdateTime"] = nowText,
                ["EffTime"] = nowText
            };
            var update = new Dictionary<string, object?>
            {
                ["OrdQty"] = row.OrdQty,
                ["ProdQty"] = row.OrdQty,
                ["Sequence"] = row.Sequence,
                ["PlanDate"] = planDateText,
                ["ProdDate"] = planDateText,
                ["IsActive"] = 1,
                ["Status"] = "", // P-031 K3：复活时复位作废态 'X'
                ["UpdateUser"] = user,
                ["UpdateTime"] = nowText
            };

            var idem = Trunc(
                $"psd|{domain}|{row.WorkOrds}|{row.Op}|{row.PlanDate:yyyyMMdd}|{row.Period}",
                200);
            var pulse = i == rows.Count - 1;
            if (await EnqueueOutboxAsync(
                    tenantId,
                    idem,
                    "S2_PSD_UPSERT",
                    new
                    {
                        op = "UPSERT",
                        table = "PeriodSequenceDet",
                        keys,
                        insert,
                        update,
                        // P-031 K3：允许覆盖空状态或已作废 'X'（依赖 K1 expect IN）
                        expect = new Dictionary<string, object?> { ["Status"] = new[] { "", "X" } }
                    },
                    pulse))
                enqueued++;
        }

        return enqueued;
    }

    private async Task EnqueuePsdDeactivateAsync(
        long tenantId,
        string user,
        DateTime now,
        PendingPsdPushRow row,
        bool pulse,
        long? runId = null)
    {
        var planDateText = row.PlanDate.ToString("yyyy-MM-dd");
        var domain = Trunc(row.Domain, 8);
        var period = row.Period > 0 ? row.Period : _defaultPeriod;
        // 对账兜底带 runId 后缀，避开历史死信 idem_key；常规作废不加，以便 Refresh 复活同键死信
        var idem = runId is > 0
            ? Trunc($"psd-deact|{domain}|{row.WorkOrds}|{row.Op}|{row.PlanDate:yyyyMMdd}|{period}|r{runId}", 200)
            : Trunc($"psd-deact|{domain}|{row.WorkOrds}|{row.Op}|{row.PlanDate:yyyyMMdd}|{period}", 200);

        await EnqueueOutboxAsync(
            tenantId,
            idem,
            "S2_PSD_DEACTIVATE",
            new
            {
                op = "UPDATE",
                table = "PeriodSequenceDet",
                keys = new Dictionary<string, object?>
                {
                    ["Domain"] = domain,
                    ["Line"] = row.Line,
                    ["ItemNum"] = row.ItemNum,
                    ["PlanDate"] = planDateText,
                    ["Period"] = period,
                    ["Op"] = row.Op,
                    ["WorkOrds"] = row.WorkOrds
                },
                update = new Dictionary<string, object?>
                {
                    ["IsActive"] = 0,
                    ["Status"] = "X", // P-031 K2：MES 取数只认 Status，不认 IsActive
                    ["UpdateUser"] = user,
                    ["UpdateTime"] = now.ToString("yyyy-MM-dd HH:mm:ss")
                },
                expect = new Dictionary<string, object?> { ["Status"] = "" }
            },
            pulse);
    }

    /// <summary>
    /// P-029 I3：本库已无、165 仍生效且未投产的日计划行 → 补发作废（idem 带 runId）。
    /// </summary>
    private async Task<PsdReconcileResult> ReconcileStale165PsdAsync(
        long tenantId,
        string account,
        DateTime now,
        long runId,
        IReadOnlyList<string> workOrds)
    {
        var result = new PsdReconcileResult();
        if (workOrds.Count == 0) return result;

        var inNames = new List<string>();
        var inPars = new List<SugarParameter> { new("@TenantId", tenantId) };
        for (var i = 0; i < workOrds.Count; i++)
        {
            var name = $"@w{i}";
            inNames.Add(name);
            inPars.Add(new SugarParameter(name, workOrds[i]));
        }

        var inClause = string.Join(",", inNames);
        var localRows = await _db.Ado.SqlQueryAsync<PendingPsdPushRow>(
            $"""
            SELECT TRIM(IFNULL(`Domain`,'')) AS `Domain`,
                   TRIM(IFNULL(Line,'')) AS Line,
                   TRIM(IFNULL(ItemNum,'')) AS ItemNum,
                   DATE(PlanDate) AS PlanDate,
                   IFNULL(Period, 0) AS Period,
                   Op,
                   TRIM(IFNULL(WorkOrds,'')) AS WorkOrds,
                   IFNULL(Sequence, 0) AS Sequence,
                   IFNULL(OrdQty, 0) AS OrdQty
            FROM PeriodSequenceDet
            WHERE tenant_id = @TenantId
              AND IFNULL(IsActive, 0) = 1
              AND WorkOrds IN ({inClause})
            """,
            inPars);

        var localKeys = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
        foreach (var r in localRows)
        {
            if (string.IsNullOrWhiteSpace(r.WorkOrds)) continue;
            localKeys.Add(BuildPsdBizKey(r));
        }

        List<StartedPsdRow> remoteRows;
        try
        {
            var ss = await _scopeFactory.GetScopeAsync(CreatePickBillService.TargetSource);
            var remotePars = inPars.Where(p => p.ParameterName != "@TenantId").ToList();
            remoteRows = await ss.Ado.SqlQueryAsync<StartedPsdRow>(
                $"""
                SELECT Domain, Line, ItemNum, PlanDate, Period, Op, WorkOrds, Status
                FROM PeriodSequenceDet
                WHERE ISNULL(IsActive, 0) = 1
                  AND WorkOrds IN ({inClause})
                """,
                remotePars);

            try
            {
                // P-031 K5：体检本库生效日计划在 165 的产线 / MilestoneOp 完备性
                await FillMesReadinessCountsAsync(ss, localRows, result);
            }
            catch
            {
                // 完备性体检失败不影响孤儿作废对账
            }
        }
        catch
        {
            // 165 探测失败时跳过对账，不中断排程
            return result;
        }

        var dupGroups = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
        var orphans = new List<PendingPsdPushRow>();
        var user = TruncUser(account);
        var remoteSchedulable = 0; // Status='' 的生效行（不含已投产）

        foreach (var r in remoteRows)
        {
            var wo = (r.WorkOrds ?? string.Empty).Trim();
            if (string.IsNullOrWhiteSpace(wo)) continue;

            var status = (r.Status ?? string.Empty).Trim();
            if (status.Length > 0)
                continue; // 已投产，保护；也不计入重复派工

            remoteSchedulable++;
            var dupKey = $"{wo}|{r.Op}";
            dupGroups[dupKey] = dupGroups.TryGetValue(dupKey, out var c) ? c + 1 : 1;

            var row = new PendingPsdPushRow
            {
                Domain = r.Domain ?? string.Empty,
                Line = r.Line ?? string.Empty,
                ItemNum = r.ItemNum ?? string.Empty,
                PlanDate = r.PlanDate?.Date ?? DateTime.MinValue,
                Period = r.Period,
                Op = r.Op,
                WorkOrds = wo
            };
            if (row.PlanDate == DateTime.MinValue || string.IsNullOrWhiteSpace(row.Line))
                continue;

            if (!localKeys.Contains(BuildPsdBizKey(row)))
                orphans.Add(row);
        }

        result.DuplicateActiveCount = dupGroups.Count(kv => kv.Value > 1);
        // 对账口径：仅 Status='' 的可派工行 vs 本库生效行
        result.MismatchCount = remoteSchedulable - localRows.Count;
        if (result.MismatchCount < 0) result.MismatchCount = 0;

        for (var i = 0; i < orphans.Count; i++)
        {
            try
            {
                await EnqueuePsdDeactivateAsync(
                    tenantId, user, now, orphans[i],
                    pulse: i == orphans.Count - 1,
                    runId: runId);
                result.Enqueued++;
            }
            catch
            {
                // 单条失败不阻断
            }
        }

        return result;
    }

    /// <summary>清理成功且过期的 S2_PSD_DEACTIVATE，防止带 runId 的 idem 无限增长。</summary>
    private async Task CleanupStalePsdDeactOutboxAsync()
    {
        var cutoff = DateTime.Now.AddDays(-7);
        await _db.Ado.ExecuteCommandAsync(
            """
            DELETE FROM mdp_outbox
            WHERE action_code = 'S2_PSD_DEACTIVATE'
              AND status = 1
              AND update_time < @Cutoff
            """,
            new SugarParameter("@Cutoff", cutoff));
    }

    private async Task<int> CountOutboxDeadAsync(long tenantId)
    {
        return await _db.Ado.GetIntAsync(
            """
            SELECT COUNT(*) FROM mdp_outbox
            WHERE tenant_id = @TenantId AND status = 2
            """,
            new SugarParameter("@TenantId", tenantId));
    }

    private sealed class PsdReconcileResult
    {
        public int MismatchCount { get; set; }
        public int DuplicateActiveCount { get; set; }
        public int Enqueued { get; set; }
        public int MesLineMissingCount { get; set; }
        public int MesRoutingMissingCount { get; set; }
    }

    private sealed class MesLineRow
    {
        public string? Domain { get; set; }
        public string? Line { get; set; }
        public bool IsActive { get; set; }
    }

    private sealed class MesRoutingKeyRow
    {
        public string? Domain { get; set; }
        public string? WorkOrd { get; set; }
        public int Op { get; set; }
        public string? ItemNum { get; set; }
    }

    /// <summary>
    /// P-031 K5：按 MES 取数口径统计本库日计划中 165 缺产线 / 缺 MilestoneOp 工序的行数。
    /// </summary>
    private static async Task FillMesReadinessCountsAsync(
        ISqlSugarClient ss,
        List<PendingPsdPushRow> localRows,
        PsdReconcileResult result)
    {
        if (localRows.Count == 0) return;

        var domains = localRows
            .Select(r => (r.Domain ?? string.Empty).Trim())
            .Where(d => d.Length > 0)
            .Distinct(StringComparer.OrdinalIgnoreCase)
            .ToList();
        if (domains.Count == 0) return;

        var domainNames = new List<string>();
        var domainPars = new List<SugarParameter>();
        for (var i = 0; i < domains.Count; i++)
        {
            var name = $"@d{i}";
            domainNames.Add(name);
            domainPars.Add(new SugarParameter(name, domains[i]));
        }
        var domainIn = string.Join(",", domainNames);

        var lineRows = await ss.Ado.SqlQueryAsync<MesLineRow>(
            $"""
            SELECT Domain, RTRIM(Line) AS Line, CAST(ISNULL(IsActive, 0) AS bit) AS IsActive
            FROM LineMaster
            WHERE Domain IN ({domainIn})
            """,
            domainPars);
        var activeLines = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
        foreach (var lm in lineRows)
        {
            if (!lm.IsActive) continue;
            var d = (lm.Domain ?? string.Empty).Trim();
            var line = (lm.Line ?? string.Empty).Trim();
            if (d.Length == 0 || line.Length == 0) continue;
            activeLines.Add($"{d}|{line}");
        }

        var woNames = new List<string>();
        var woPars = new List<SugarParameter>();
        var workOrds = localRows
            .Select(r => (r.WorkOrds ?? string.Empty).Trim())
            .Where(w => w.Length > 0)
            .Distinct(StringComparer.OrdinalIgnoreCase)
            .ToList();
        for (var i = 0; i < workOrds.Count; i++)
        {
            var name = $"@w{i}";
            woNames.Add(name);
            woPars.Add(new SugarParameter(name, workOrds[i]));
        }

        var milestoneKeys = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
        if (woNames.Count > 0)
        {
            var woIn = string.Join(",", woNames);
            var rtgRows = await ss.Ado.SqlQueryAsync<MesRoutingKeyRow>(
                $"""
                SELECT Domain, WorkOrd, Op, ItemNum
                FROM WorkOrdRouting
                WHERE WorkOrd IN ({woIn})
                  AND ISNULL(MilestoneOp, 0) = 1
                """,
                woPars);
            foreach (var r in rtgRows)
            {
                var d = (r.Domain ?? string.Empty).Trim();
                var wo = (r.WorkOrd ?? string.Empty).Trim();
                var item = (r.ItemNum ?? string.Empty).Trim();
                if (d.Length == 0 || wo.Length == 0) continue;
                milestoneKeys.Add($"{d}|{wo}|{r.Op}|{item}");
            }
        }

        var lineMissing = 0;
        var routingMissing = 0;
        foreach (var row in localRows)
        {
            var d = (row.Domain ?? string.Empty).Trim();
            var line = (row.Line ?? string.Empty).Trim();
            var wo = (row.WorkOrds ?? string.Empty).Trim();
            var item = (row.ItemNum ?? string.Empty).Trim();
            if (d.Length == 0 || wo.Length == 0) continue;

            if (!activeLines.Contains($"{d}|{line}"))
                lineMissing++;
            if (!milestoneKeys.Contains($"{d}|{wo}|{row.Op}|{item}"))
                routingMissing++;
        }

        result.MesLineMissingCount = lineMissing;
        result.MesRoutingMissingCount = routingMissing;
    }

    private async Task<bool> EnqueueOutboxAsync(
        long tenantId,
        string idem,
        string action,
        object payload,
        bool pulse)
    {
        var item = new MdpOutbox
        {
            TenantId = tenantId,
            TargetSourceCode = CreatePickBillService.TargetSource,
            ActionCode = action,
            IdemKey = idem,
            PayloadJson = JsonSerializer.Serialize(payload)
        };
        // 重排时同业务键可能已成功推过，须 Refresh 覆盖 payload
        return await _enqueue.TryEnqueueOrRefreshAsync(item, CancellationToken.None, pulse);
    }

    private async Task<HashSet<string>> LoadStartedPsdKeysFrom165Async(string workOrd)
    {
        var started = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
        try
        {
            var ss = await _scopeFactory.GetScopeAsync(CreatePickBillService.TargetSource);
            var rows = await ss.Ado.SqlQueryAsync<StartedPsdRow>(
                """
                SELECT Domain, Line, ItemNum, PlanDate, Period, Op, WorkOrds, Status
                FROM PeriodSequenceDet
                WHERE WorkOrds = @w AND ISNULL(IsActive, 0) = 1
                  AND ISNULL(Status, '') <> ''
                """,
                new SugarParameter("@w", workOrd));

            foreach (var r in rows)
            {
                started.Add(BuildPsdBizKey(new PendingPsdPushRow
                {
                    Domain = r.Domain ?? string.Empty,
                    Line = r.Line ?? string.Empty,
                    ItemNum = r.ItemNum ?? string.Empty,
                    PlanDate = r.PlanDate?.Date ?? DateTime.MinValue,
                    Period = r.Period,
                    Op = r.Op,
                    WorkOrds = r.WorkOrds ?? string.Empty
                }));
            }
        }
        catch
        {
            // 165 探测失败降级放行，由 Outbox expect 保护现场状态
        }

        return started;
    }

    private static string BuildPsdBizKey(PendingPsdPushRow row) =>
        $"{Trunc(row.Domain, 8)}|{row.Line}|{row.ItemNum}|{row.PlanDate:yyyy-MM-dd}|{row.Period}|{row.Op}|{row.WorkOrds}";

    private static string TruncUser(string? account)
    {
        var user = string.IsNullOrWhiteSpace(account) ? "system" : account.Trim();
        return Trunc(user, 24);
    }

    private static string Trunc(string? value, int max)
    {
        var s = value ?? string.Empty;
        return s.Length > max ? s[..max] : s;
    }

    private sealed class StartedPsdRow
    {
        public string? Domain { get; set; }
        public string? Line { get; set; }
        public string? ItemNum { get; set; }
        public DateTime? PlanDate { get; set; }
        public short Period { get; set; }
        public int Op { get; set; }
        public string? WorkOrds { get; set; }
        public string? Status { get; set; }
    }

    /// <summary>
    /// 计算工单齐套数量：MIN(可用库存 / qty) 得可生产成品数，超过工单需求量则取工单需求量。
    /// 若工单领料单已全部发完料，则齐套数量 = 工单需求量。
    /// consumedStock 跟踪已被高优先级工单占用的库存量，避免重复占用。
    /// </summary>
    private async Task<decimal?> CalcLocationStockAsync(
        long tenantId, string workOrd, decimal qtyOrded,
        Dictionary<string, decimal> consumedStock)
    {
        // 检查领料单是否已全部发完料
        var allIssued = await _db.Ado.GetIntAsync(
            """
            SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END
            FROM NbrMaster nm
            WHERE nm.tenant_id = @TenantId AND nm.WorkOrd = @WorkOrd
              AND nm.Type = 'SM' AND IFNULL(nm.IsActive, 0) = 1
              AND NOT EXISTS (
                  SELECT 1 FROM NbrDetail nd
                  WHERE nd.tenant_id = nm.tenant_id AND nd.Nbr = nm.Nbr
                    AND nd.Type = 'SM' AND IFNULL(nd.IsActive, 0) = 1
                    AND IFNULL(nd.QtyRec, 0) < IFNULL(nd.QtyOrd, 0)
              )
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrd", workOrd));

        if (allIssued > 0 && qtyOrded > 0)
            return qtyOrded;

        // 从最新资源检查获取各物料的 use_qty 和 qty
        var materials = await _db.Ado.SqlQueryAsync<LocationStockMaterialRow>(
            """
            SELECT
                bce.item_number AS ItemNumber,
                IFNULL(bce.use_qty, 0) AS UseQty,
                IFNULL(bce.qty, 0) AS Qty
            FROM b_examine_result ber
            INNER JOIN b_bom_child_examine bce ON ber.Id = bce.examine_id AND bce.is_use = 1
            WHERE ber.tenant_id = @TenantId
              AND ber.IsDeleted = 0
              AND ber.morder_no = @WorkOrd
              AND ber.Id = (
                  SELECT br.Id FROM b_examine_result br
                  WHERE br.tenant_id = @TenantId AND br.morder_no = @WorkOrd AND br.IsDeleted = 0
                  ORDER BY br.create_time DESC LIMIT 1
              )
              AND IFNULL(bce.qty, 0) > 0
              AND IFNULL(bce.erp_cls, 3) = 3
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrd", workOrd));

        if (materials.Count == 0)
            return null;

        // MIN(可用库存 / qty) = 扣减已占用后的瓶颈物料可生产成品数
        decimal? minProducible = null;
        foreach (var mat in materials)
        {
            if (mat.Qty <= 0) continue;
            var alreadyConsumed = consumedStock.TryGetValue(mat.ItemNumber, out var c) ? c : 0m;
            var availableUseQty = Math.Max(0m, mat.UseQty - alreadyConsumed);
            var producible = availableUseQty / mat.Qty;
            if (minProducible is null || producible < minProducible)
                minProducible = producible;
        }

        if (minProducible is null)
            return null;

        // 可生产数 > 工单需求量则取工单需求量
        var result = Math.Ceiling(minProducible.Value);
        if (qtyOrded > 0 && result > qtyOrded)
            result = qtyOrded;

        // 记录本工单占用的库存量
        foreach (var mat in materials)
        {
            if (mat.Qty <= 0) continue;
            var alreadyConsumed = consumedStock.TryGetValue(mat.ItemNumber, out var c) ? c : 0m;
            var availableUseQty = Math.Max(0m, mat.UseQty - alreadyConsumed);
            var consume = Math.Min(availableUseQty, result * mat.Qty);
            if (!consumedStock.ContainsKey(mat.ItemNumber))
                consumedStock[mat.ItemNumber] = 0m;
            consumedStock[mat.ItemNumber] += consume;
        }

        return result;
    }

    private sealed class LocationStockMaterialRow
    {
        public string ItemNumber { get; set; } = string.Empty;
        public decimal UseQty { get; set; }
        public decimal Qty { get; set; }
    }

    private sealed class PendingWorkOrderRow
    {
        public long RecId { get; set; }
        public string WorkOrd { get; set; } = string.Empty;
        public string? ItemNum { get; set; }
        public string? Domain { get; set; }
        public decimal? QtyOrded { get; set; }
        public DateTime? OrdDate { get; set; }
        public DateTime? DueDate { get; set; }
        public decimal? Priority { get; set; }
        public int? Urgent { get; set; }
        public string Status { get; set; } = string.Empty;
    }

    private sealed class RoutingRow
    {
        public int Op { get; set; }
        public string? ProdLine { get; set; }
        public string? WorkCtr { get; set; }
        public string? Site { get; set; }
        public string? StdOp { get; set; }
        public string? Machine { get; set; }
        public string? Engineer { get; set; }
        public decimal RunCrew { get; set; }
        public decimal? MachBdnRate { get; set; }
        public int? MachinestPerOp { get; set; }
        public decimal? RunTime { get; set; }
        public decimal? StdRunTime { get; set; }
        public decimal ProdLineRate { get; set; }
        public string? WorkCode { get; set; }
        public string? OpType { get; set; }
        public string? OpDescr { get; set; }
        public decimal? SetupTime { get; set; }
    }

    private sealed class CalendarRow
    {
        public string? WorkCtr { get; set; }
        public int? WeekDay { get; set; }
    }

    private sealed class OccupiedSlotRow
    {
        public string? WorkCtr { get; set; }
        public DateTime? PlanDate { get; set; }
    }
}