namespace Admin.NET.Plugin.AiDOP.Production;

using System.Diagnostics;
using Admin.NET.Plugin.AiDOP.WorkOrder;

/// <summary>生产排程生成：为待排工单写入 PeriodSequenceDet（工作日历 + 工作中心冲突避让）。</summary>
public class ProductionScheduleGenerationService : ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly WorkOrderKittingCheckService _kittingCheck;

    public ProductionScheduleGenerationService(ISqlSugarClient db, WorkOrderKittingCheckService kittingCheck)
    {
        _db = db;
        _kittingCheck = kittingCheck;
    }

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

        return await ScheduleWorkOrdersAsync(tenantId, domain, account, workOrders, enableCapacityConstraint);
    }

    public async Task<ScheduleGenerationResult> RegenerateForWorkOrderAsync(
        long tenantId,
        string workOrd,
        string? domain,
        string account,
        bool enableCapacityConstraint = false)
    {
        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} 不存在" };

        return await ScheduleWorkOrdersAsync(tenantId, domain, account, new List<PendingWorkOrderRow> { wo }, enableCapacityConstraint);
    }

    private async Task<ScheduleGenerationResult> ScheduleWorkOrdersAsync(
        long tenantId,
        string? domain,
        string account,
        List<PendingWorkOrderRow> workOrders,
        bool enableCapacityConstraint = false)
    {
        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 skipped = new List<string>();
        // 按优先级顺序跟踪各物料已占用库存量（ItemNumber → 已占用数量）
        var consumedStock = new Dictionary<string, decimal>(StringComparer.OrdinalIgnoreCase);
        // 跟踪每日各工作中心已占用设备数（Key = "WorkCtr|yyyy-MM-dd"）
        var equipmentDailyOccupancy = new Dictionary<string, decimal>(StringComparer.OrdinalIgnoreCase);

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

        // 清理旧的占用记录，确保每次排程重新计算
        const long scheduleBangId = 1;
        await _db.Ado.ExecuteCommandAsync(
            "DELETE FROM ic_item_stockoccupy WHERE tenant_id = @TenantId AND bang_id = @BangId",
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@BangId", scheduleBangId));
        await _db.Ado.ExecuteCommandAsync(
            "DELETE FROM srm_po_occupy WHERE tenant_id = @TenantId AND bang_id = @BangId",
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@BangId", scheduleBangId));

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

            // 0. 先加载工艺路线，无路线的工单跳过齐套检查（性能优化）
            var routings = await LoadRoutingsAsync(tenantId, wo.WorkOrd);
            if (routings.Count == 0)
            {
                skipped.Add($"{wo.WorkOrd}(无工艺路线)");
                await InsertScheduleExceptionAsync(tenantId, wo, domain, "无工艺路线", "工单无工艺路线，无法生成排程");
                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);
            }
            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);
            await DeactivateExistingScheduleAsync(tenantId, wo.WorkOrd);

            var planStart = (wo.OrdDate ?? now).Date;
            var planEnd = (wo.DueDate ?? planStart.AddDays(Math.Max(routings.Count, 7))).Date;
            if (planEnd < planStart)
                planEnd = planStart;

            var cursor = planStart;
            var seq = 1;
            for (var i = 0; i < routings.Count; i++)
            {
                var routing = routings[i];
                DateTime planDate;
                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 > planEnd)
                    planEnd = planDate;

                var slotKey = BuildOccupancyKey(routing.WorkCtr, planDate);
                occupiedSlots.Add(slotKey);

                // 产能约束：非人工工序，根据设备和人员瓶颈限制排产数量
                decimal? effectiveQty = null;
                string? capacityMachineCode = null;
                string? capacitySkillNo = null;
                int? capacityDeviceAllocation = null;
                decimal? capacityAssignedPersonnel = null;
                if (enableCapacityConstraint
                    && !string.IsNullOrWhiteSpace(routing.WorkCode)
                    && routing.WorkCode.Trim().ToUpperInvariant() != "P")
                {
                    capacityMachineCode = await ResolveMachineCodeAsync(tenantId, routing.Machine, wo.ItemNum, routing.Op);
                    capacitySkillNo = await ResolveSkillNoAsync(tenantId, routing.Engineer, wo.ItemNum, routing.Op);

                    var equipCount = await LoadAvailableEquipmentCountAsync(tenantId, capacityMachineCode);
                    var personnelCount = await LoadAvailablePersonnelCountAsync(tenantId, capacitySkillNo);

                    var maxAvailable = Math.Min(equipCount, personnelCount);
                    if (maxAvailable > 0 && routing.MachBdnRate.HasValue && routing.MachBdnRate.Value > 0)
                    {
                        var workDays = Math.Max(1, (planEnd - planStart).Days);
                        var dailyQty = Math.Ceiling((wo.QtyOrded ?? 0) / workDays);
                        var standardNeed = Math.Ceiling(dailyQty / routing.MachBdnRate.Value);

                        var occupancyKey = $"{NormalizeWorkCtr(routing.WorkCtr)}|{planDate:yyyy-MM-dd}";
                        var alreadyOccupied = equipmentDailyOccupancy.TryGetValue(occupancyKey, out var occ) ? occ : 0m;
                        var remainingAvailable = (decimal)maxAvailable - alreadyOccupied;

                        if (remainingAvailable > 0)
                        {
                            var actualOccupied = Math.Min(standardNeed, remainingAvailable);
                            equipmentDailyOccupancy[occupancyKey] = alreadyOccupied + actualOccupied;
                            effectiveQty = Math.Min(wo.QtyOrded ?? 0, actualOccupied * routing.MachBdnRate.Value);
                            capacityDeviceAllocation = (int)actualOccupied;
                            capacityAssignedPersonnel = actualOccupied;
                        }
                    }
                }

                var recId = await NextPeriodRecIdAsync();
                await InsertScheduleRowAsync(
                    recId, woDomain, routing, wo, planDate, seq, account, now, tenantId,
                    capacityQty: effectiveQty);

                // 产能约束启用时，将设备/人员分配数据写入 ScheduleResultOpMaster
                if (capacityDeviceAllocation.HasValue || !string.IsNullOrWhiteSpace(capacityMachineCode) || !string.IsNullOrWhiteSpace(capacitySkillNo))
                {
                    var resultRecId = await NextScheduleResultOpRecIdAsync();
                    await InsertScheduleResultOpAsync(
                        resultRecId, woDomain, routing, wo, planDate, seq, now, tenantId,
                        capacityMachineCode, capacityDeviceAllocation, capacitySkillNo, capacityAssignedPersonnel);
                }
                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");
            }
        }

        totalSw.Stop();
        Console.WriteLine($"[排程诊断] 完成: {workOrders.Count}工单/{rowCount}条, " +
            $"齐套检查={kittingMs}ms 其他={otherMs}ms 总计={totalSw.ElapsedMilliseconds}ms");

        return new ScheduleGenerationResult
        {
            WorkOrderCount = scheduledCount,
            ScheduleRowCount = rowCount,
            SkippedWorkOrders = skipped,
            UsedWorkCenterCalendar = usedCalendar,
            Message = scheduledCount > 0
                ? $"已为 {scheduledCount} 个工单生成 {rowCount} 条工序排程"
                : "未生成排程，请确认工单已同步工艺路线"
        };
    }

    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,
                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,
                @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("@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);
                if (!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;

        var key = NormalizeWorkCtr(workCtr);
        if (!calendar.TryGetValue(key, out var days) || days.Count == 0)
            return date.DayOfWeek is not DayOfWeek.Saturday and not DayOfWeek.Sunday;

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

    private static string BuildOccupancyKey(string? workCtr, DateTime date) =>
        $"{NormalizeWorkCtr(workCtr)}|{date:yyyy-MM-dd}";

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

    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
            """,
            new SugarParameter("@TenantId", tenantId));

        return rows
            .Where(x => x.PlanDate.HasValue)
            .Select(x => BuildOccupancyKey(x.WorkCtr, x.PlanDate!.Value))
            .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)
    {
        return await _db.Ado.SqlQueryAsync<RoutingRow>(
            """
            SELECT OP AS Op, ProdLine, WorkCtr, WorkCtr AS Site,
                   IFNULL(StdOp, '') AS StdOp,
                   IFNULL(Machine, '') AS Machine,
                   IFNULL(Engineer, '') AS Engineer,
                   IFNULL(RunCrew, 0) AS RunCrew,
                   IFNULL(MachBdnRate, 0) AS MachBdnRate,
                   IFNULL(MachinesperOp, 0) AS MachinestPerOp,
                   IFNULL(RunTime, 0) AS RunTime,
                   IFNULL(WorkCode, '') AS WorkCode
            FROM WorkOrdRouting
            WHERE tenant_id = @TenantId AND WorkOrd = @WorkOrd AND IFNULL(IsActive, 0) = 1
            ORDER BY (OP + 0), OP
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrd", workOrd));
    }

    public async Task DeactivateExistingScheduleAsync(long tenantId, string workOrd)
    {
        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", DateTime.Now),
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrd", workOrd));
    }

    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 async Task<int> NextPeriodRecIdAsync()
    {
        var max = await _db.Ado.GetIntAsync("SELECT IFNULL(MAX(RecID), 0) FROM PeriodSequenceDet");
        return max + 1;
    }

    private async Task<long> NextScheduleExceptionRecIdAsync()
    {
        var max = await _db.Ado.GetIntAsync("SELECT IFNULL(MAX(RecID), 0) FROM ScheduleExceptionMaster");
        return max + 1;
    }

    private async Task<long> NextScheduleResultOpRecIdAsync()
    {
        var max = await _db.Ado.GetIntAsync("SELECT IFNULL(MAX(RecID), 0) FROM ScheduleResultOpMaster");
        return max + 1;
    }

    /// <summary>查询可用设备数量（仅排产设备）。</summary>
    private async Task<int> LoadAvailableEquipmentCountAsync(long tenantId, string? machineCode)
    {
        if (string.IsNullOrWhiteSpace(machineCode)) return 0;
        return await _db.Ado.GetIntAsync(
            """
            SELECT COUNT(*) FROM EquipmentList
            WHERE InternalEquipmentCode = @Code AND IFNULL(IsSchedulable, 0) = 1
            """,
            new SugarParameter("@Code", machineCode.Trim()));
    }

    /// <summary>查询可用人员数量（持有指定技能的人员）。</summary>
    private async Task<int> LoadAvailablePersonnelCountAsync(long tenantId, string? skillNo)
    {
        if (string.IsNullOrWhiteSpace(skillNo)) return 0;
        return await _db.Ado.GetIntAsync(
            "SELECT COUNT(*) FROM EmpSkills WHERE SkillNo = @SkillNo",
            new SugarParameter("@SkillNo", skillNo.Trim()));
    }

    /// <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 = await NextScheduleExceptionRecIdAsync();
        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)
    {
        await _db.Ado.ExecuteCommandAsync(
            """
            INSERT INTO ScheduleResultOpMaster (
                RecID, `Domain`, WorkOrd, WorkCtr, Line, ItemNum, Op,
                WorkDate, WorkQty, WorkSort,
                InternalEquipmentCode, DeviceAllocationCount, SkillNo, AssignedPersonnelCount,
                Remark, CreateTime, tenant_id
            ) VALUES (
                @RecId, @Domain, @WorkOrd, @WorkCtr, @Line, @ItemNum, @Op,
                @WorkDate, @WorkQty, @WorkSort,
                @InternalEquipmentCode, @DeviceAllocationCount, @SkillNo, @AssignedPersonnelCount,
                @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("@InternalEquipmentCode", string.IsNullOrWhiteSpace(machineCode) ? (object)DBNull.Value : machineCode.Trim()),
            new SugarParameter("@DeviceAllocationCount", deviceAllocationCount ?? (object)DBNull.Value),
            new SugarParameter("@SkillNo", string.IsNullOrWhiteSpace(skillNo) ? (object)DBNull.Value : skillNo.Trim()),
            new SugarParameter("@AssignedPersonnelCount", assignedPersonnelCount ?? (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 bool UsedWorkCenterCalendar { get; set; }
        public List<string> SkippedWorkOrders { get; set; } = new();
        public string Message { get; set; } = string.Empty;
    }

    /// <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 string? WorkCode { 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; }
    }
}