using Admin.NET.Plugin.AiDOP.MaterialWarehouse;
using Microsoft.Extensions.Logging;
using Yitter.IdGenerator;

namespace Admin.NET.Plugin.AiDOP.Order;

/// <summary>
/// 订单评审资源检查：多层 BOM 展开与库存/采购在途缺口计算。
/// </summary>
public class MaterialRequirementCalculator : ITransient
{
    private const int MaxBomDepth = 20;
    private readonly ISqlSugarClient _db;
    private readonly IInventoryBalanceReader _balanceReader;
    private readonly ILogger _logger;

    public MaterialRequirementCalculator(
        ISqlSugarClient db,
        IInventoryBalanceReader balanceReader,
        ILoggerFactory loggerFactory)
    {
        _db = db;
        _balanceReader = balanceReader;
        _logger = loggerFactory.CreateLogger(nameof(MaterialRequirementCalculator));
    }

    /// <param name="writeOccupy">
    /// 是否登记库存/在途占用。只有排产才写（占用有明确的版本与清理时机）；
    /// 手动齐套检查是查询语义，多点几次不应把库存占光。
    /// </param>
    /// <param name="excludeOccupyWorkOrd">
    /// 扣减占用时要排除的工单号（通常是本工单）。不排除会把自己上一次的占用当成别人的，
    /// 导致同一工单反复检查时缺料量不断翻倍。
    /// </param>
    public async Task<List<ResourceCheckBomLine>> BuildLinesAsync(
        OrderWorkOrderGenerationService.OrderHeader order,
        OrderWorkOrderGenerationService.OrderEntryLine entry,
        List<string> warnings,
        long bangId = 0,
        string? workOrd = null,
        long morderId = 0,
        bool writeOccupy = true,
        string? excludeOccupyWorkOrd = null)
    {
        var itemNum = entry.ItemNumber!.Trim();
        var orderQty = entry.Qty ?? 0;
        var needTime = entry.SysCapacityDate ?? entry.PlanDate ?? DateTime.Today;
        var lines = new List<ResourceCheckBomLine>();

        // 加载齐套检查排除规则
        var (excludeItemTypes, excludeOwnerApps) = await LoadExcludeRuleAsync(entry.TenantId);

        var rootFid = YitIdHelper.NextId();
        lines.Add(new ResourceCheckBomLine
        {
            Fid = rootFid,
            Num = "1",
            Level = 1,
            ItemNumber = itemNum,
            ItemName = entry.ItemName,
            Model = entry.Specification,
            Unit = entry.Unit,
            BomNumber = entry.BomNumber,
            ErpCls = 1,
            ErpClsName = "自制",
            NeedCount = orderQty,
            NeedCountNoLoss = orderQty,
            KittingTime = needTime,
            SatisfyTime = needTime,
            IsUse = 1
        });

        var bomId = await ResolveBomIdAsync(entry, itemNum);
        if (bomId is null)
        {
            warnings.Add($"订单行 {entry.EntrySeq}（{itemNum}）未匹配到 BOM，仅写入成品行资源检查结果");
            await ApplySupplyAsync(lines, entry.TenantId, bangId, entry.Id, workOrd, morderId, writeOccupy, excludeOccupyWorkOrd);
            return lines;
        }

        var children = await LoadBomChildrenAsync(bomId.Value, entry.TenantId);
        if (children.Count == 0)
        {
            warnings.Add($"订单行 {entry.EntrySeq} BOM {entry.BomNumber ?? bomId.ToString()} 无子件明细");
            await ApplySupplyAsync(lines, entry.TenantId, bangId, entry.Id, workOrd, morderId, writeOccupy, excludeOccupyWorkOrd);
            return lines;
        }

        var seq = 1;
        foreach (var child in children)
        {
            seq = await AppendBomChildAsync(
                lines, rootFid, "1", 1, orderQty, child, entry, needTime, warnings, seq, depth: 1,
                excludeItemTypes, excludeOwnerApps);
        }

        await ApplySupplyAsync(lines, entry.TenantId, bangId, entry.Id, workOrd, morderId, writeOccupy, excludeOccupyWorkOrd);
        return lines;
    }

    private async Task<int> AppendBomChildAsync(
        List<ResourceCheckBomLine> lines,
        long parentFid,
        string parentNum,
        int parentLevel,
        decimal parentNeedQty,
        BomChildRow child,
        OrderWorkOrderGenerationService.OrderEntryLine entry,
        DateTime needTime,
        List<string> warnings,
        int seq,
        int depth,
        HashSet<string>? excludeItemTypes = null,
        HashSet<string>? excludeOwnerApps = null)
    {
        var unitQty = child.Qty <= 0 ? 1m : child.Qty;
        var wastageFactor = 1m + (child.Wastage / 100m);
        var scrapFactor = 1m + (child.Scrap / 100m);
        var needNoLoss = parentNeedQty * unitQty;
        var needCount = RoundQty(needNoLoss * wastageFactor * scrapFactor);

        if (needCount > 10000 && parentNeedQty > 0 && unitQty > 100)
        {
            warnings.Add($"物料 {child.ItemNumber} BOM用量({unitQty}) × 父需求({parentNeedQty}) = 需求数量({needCount})，请核实 BOM 配置");
        }
        var isVirtual = child.ErpCls == 4;
        var isExcluded = !isVirtual && (
            (!string.IsNullOrEmpty(child.ItemType) && excludeItemTypes?.Contains(child.ItemType) == true) ||
            (!string.IsNullOrEmpty(child.OwnerApplication) && excludeOwnerApps?.Contains(child.OwnerApplication) == true));
        var num = $"{parentNum}.{seq}";
        var fid = YitIdHelper.NextId();
        var nextSeq = seq + 1;

        // 虚拟件(erp_cls=4)也写入资源检查记录，IsUse=0 标识为虚拟件，不参与缺料计算
        lines.Add(new ResourceCheckBomLine
        {
            Fid = fid,
            ParentFid = parentFid,
            BomChildId = child.Id,
            Num = num,
            Level = parentLevel + 1,
            Type = child.Type,
            ItemNumber = child.ItemNumber ?? string.Empty,
            ItemName = child.ItemName,
            Model = child.Model,
            Unit = child.Unit,
            BomNumber = child.BomNumber,
            ErpCls = child.ErpCls,
            ErpClsName = MapErpClsName(child.ErpCls),
            Backflush = child.Backflush,
            Qty = unitQty,
            Wastage = child.Wastage,
            Scrap = child.Scrap,
            NeedCount = needCount,
            NeedCountNoLoss = RoundQty(needNoLoss),
            KittingTime = needTime,
            SatisfyTime = needTime,
            IsBom = child.IsBom,
            HaveIcSubs = child.HaveIcSubs,
            SubstituteCode = child.SubstituteCode,
            IsUse = (isVirtual || isExcluded) ? 0 : 1
        });

        seq = nextSeq;
        var expandParentFid = isVirtual ? parentFid : fid;
        var expandParentNum = num;
        var expandNeed = needCount;

        // 仅虚拟件(erp_cls=4)继续向下展开子阶BOM，其他类型作为叶子节点不再下钻
        if (isVirtual && depth < MaxBomDepth && !string.IsNullOrWhiteSpace(child.ItemNumber))
        {
            var subBomId = await ResolveBomIdByItemAsync(child.ItemNumber!, entry.TenantId);
            if (subBomId is null)
                return seq;

            var grandchildren = await LoadBomChildrenAsync(subBomId.Value, entry.TenantId);
            if (grandchildren.Count == 0)
                return seq;

            var childSeq = 1;
            foreach (var gc in grandchildren)
            {
                childSeq = await AppendBomChildAsync(
                    lines,
                    expandParentFid,
                    expandParentNum,
                    parentLevel + 1,
                    expandNeed,
                    gc,
                    entry,
                    needTime,
                    warnings,
                    childSeq,
                    depth + 1,
                    excludeItemTypes,
                    excludeOwnerApps);
            }
        }
        else if (depth >= MaxBomDepth)
        {
            warnings.Add($"物料 {child.ItemNumber} 超过最大 BOM 展开层级 {MaxBomDepth}，停止下钻");
        }

        return seq;
    }

    private async Task<long?> ResolveBomIdAsync(
        OrderWorkOrderGenerationService.OrderEntryLine entry,
        string itemNum)
    {
        var bomNumber = entry.BomNumber?.Trim();
        var rows = await _db.Ado.SqlQueryAsync<BomHeaderRow>(
            """
            SELECT Id, bom_number AS BomNumber, item_number AS ItemNumber
            FROM ic_bom
            WHERE tenant_id = @TenantId AND IsDeleted = 0
              AND (
                    (@BomNumber <> '' AND bom_number = @BomNumber)
                 OR (@BomNumber = '' AND item_number = @ItemNum)
                 OR item_number = @ItemNum
              )
            ORDER BY
                CASE WHEN @BomNumber <> '' AND bom_number = @BomNumber THEN 0 ELSE 1 END,
                Id DESC
            LIMIT 1
            """,
            new SugarParameter("@TenantId", entry.TenantId),
            new SugarParameter("@BomNumber", bomNumber ?? string.Empty),
            new SugarParameter("@ItemNum", itemNum));
        return rows.FirstOrDefault()?.Id;
    }

    private async Task<long?> ResolveBomIdByItemAsync(string itemNum, long tenantId)
    {
        var rows = await _db.Ado.SqlQueryAsync<BomHeaderRow>(
            """
            SELECT Id
            FROM ic_bom
            WHERE tenant_id = @TenantId AND IsDeleted = 0 AND item_number = @ItemNum
            ORDER BY Id DESC
            LIMIT 1
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@ItemNum", itemNum.Trim()));
        return rows.FirstOrDefault()?.Id;
    }

    private async Task<List<BomChildRow>> LoadBomChildrenAsync(long bomId, long tenantId)
    {
        return await _db.Ado.SqlQueryAsync<BomChildRow>(
            """
            SELECT
                c.Id,
                c.bom_number AS BomNumber,
                c.item_number AS ItemNumber,
                c.item_name AS ItemName,
                IFNULL(im.Descr1, '') AS Model,
                c.unit AS Unit,
                IFNULL(c.qty, 1) AS Qty,
                IFNULL(c.wastage, 0) AS Wastage,
                IFNULL(c.scrap, 0) AS Scrap,
                IFNULL(c.backflush, 0) AS Backflush,
                IFNULL(c.type, 0) AS Type,
                IFNULL(c.erp_cls, 3) AS ErpCls,
                IFNULL(c.is_bom, 0) AS IsBom,
                IFNULL(c.haveicsubs, 0) AS HaveIcSubs,
                c.substitute_code AS SubstituteCode,
                IFNULL(im.ItemType, '') AS ItemType,
                IFNULL(im.OwnerApplication, '') AS OwnerApplication
            FROM ic_bom_child c
            LEFT JOIN ItemMaster im ON c.item_number = im.ItemNum AND (c.tenant_id = im.tenant_id OR c.tenant_id IS NULL)
            WHERE c.bom_id = @BomId
              AND c.tenant_id = @TenantId
              AND c.IsDeleted = 0
            ORDER BY IFNULL(c.child_num, 0), IFNULL(c.entryid, 0), c.Id
            """,
            new SugarParameter("@BomId", bomId),
            new SugarParameter("@TenantId", tenantId));
    }

    /// <summary>
    /// 从 ado_kitting_exclude_rule 加载当前租户的齐套检查排除配置。
    /// 返回两个 HashSet（物料类型 / 物料属性），为空时表示不排除任何物料。
    /// </summary>
    private async Task<(HashSet<string> ItemTypes, HashSet<string> OwnerApps)> LoadExcludeRuleAsync(long tenantId)
    {
        var empty = (new HashSet<string>(StringComparer.OrdinalIgnoreCase),
                     new HashSet<string>(StringComparer.OrdinalIgnoreCase));
        if (tenantId <= 0) return empty;

        try
        {
            var rows = await _db.Ado.SqlQueryAsync<ExcludeRuleRow>(
                """
                SELECT excluded_item_types AS ExcludedItemTypes, excluded_owner_apps AS ExcludedOwnerApps, is_active AS IsActive
                FROM ado_kitting_exclude_rule
                WHERE tenant_id = @TenantId AND IFNULL(is_active, 1) = 1
                LIMIT 1
                """,
                new SugarParameter("@TenantId", tenantId));

            var row = rows.FirstOrDefault();
            if (row == null || !row.IsActive) return empty;

            var itemTypes = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
            var ownerApps = new HashSet<string>(StringComparer.OrdinalIgnoreCase);

            if (!string.IsNullOrWhiteSpace(row.ExcludedItemTypes))
            {
                try
                {
                    var arr = System.Text.Json.JsonSerializer.Deserialize<string[]>(row.ExcludedItemTypes);
                    if (arr != null) foreach (var v in arr) if (!string.IsNullOrEmpty(v)) itemTypes.Add(v);
                }
                catch { /* JSON 解析失败忽略 */ }
            }

            if (!string.IsNullOrWhiteSpace(row.ExcludedOwnerApps))
            {
                try
                {
                    var arr = System.Text.Json.JsonSerializer.Deserialize<string[]>(row.ExcludedOwnerApps);
                    if (arr != null) foreach (var v in arr) if (!string.IsNullOrEmpty(v)) ownerApps.Add(v);
                }
                catch { /* JSON 解析失败忽略 */ }
            }

            return (itemTypes, ownerApps);
        }
        catch (Exception ex)
        {
            _logger.LogWarning(ex, "加载齐套检查排除规则失败，tenantId={TenantId}，按不排除处理", tenantId);
            return empty;
        }
    }

    private async Task ApplySupplyAsync(
        List<ResourceCheckBomLine> lines,
        long tenantId,
        long bangId = 0,
        long entryId = 0,
        string? workOrd = null,
        long morderId = 0,
        bool writeOccupy = true,
        string? excludeOccupyWorkOrd = null)
    {
        var workOrdValue = string.IsNullOrWhiteSpace(workOrd) ? "" : workOrd.Trim();
        var stockMap = new Dictionary<string, decimal>(StringComparer.OrdinalIgnoreCase);
        var transitMap = new Dictionary<string, decimal>(StringComparer.OrdinalIgnoreCase);

        // 查询本次计算批次(bangId)已记录的库存占用和采购在途占用
        var occupiedStockMap = new Dictionary<string, decimal>(StringComparer.OrdinalIgnoreCase);
        var occupiedTransitMap = new Dictionary<string, decimal>(StringComparer.OrdinalIgnoreCase);
        if (bangId > 0)
        {
            // 排除指定工单（通常是本工单）自己的占用，否则同一工单重复检查会把自己的占用当别人的
            var excludeWo = (excludeOccupyWorkOrd ?? "").Trim();
            var excludeClause = excludeWo.Length == 0 ? "" : " AND IFNULL(morder_mo, '') <> @ExcludeWo";

            var occupyPars = new List<SugarParameter>
            {
                new("@TenantId", tenantId),
                new("@BangId", bangId)
            };
            if (excludeWo.Length > 0)
                occupyPars.Add(new SugarParameter("@ExcludeWo", excludeWo));

            var occupiedRows = 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 bang_id = @BangId AND IsDeleted = 0{excludeClause}
                GROUP BY icitem_number
                """,
                occupyPars);
            foreach (var r in occupiedRows)
                occupiedStockMap[r.ItemNumber] = r.TotalOccupied;

            var transitPars = new List<SugarParameter>
            {
                new("@TenantId", tenantId),
                new("@BangId", bangId)
            };
            if (excludeWo.Length > 0)
                transitPars.Add(new SugarParameter("@ExcludeWo", excludeWo));

            var occupiedTransitRows = await _db.Ado.SqlQueryAsync<OccupyRow>(
                $"""
                SELECT ItemNumber, IFNULL(SUM(OccupyQty), 0) AS TotalOccupied
                FROM srm_po_occupy
                WHERE tenant_id = @TenantId AND bang_id = @BangId AND IsDeleted = 0{excludeClause}
                GROUP BY ItemNumber
                """,
                transitPars);
            foreach (var r in occupiedTransitRows)
                occupiedTransitMap[r.ItemNumber] = r.TotalOccupied;
        }

        // 库存基数一次性批量取：MES 权威时读 165 实时值，避免逐行跨库往返
        var itemNumbers = lines
            .Where(x => !string.IsNullOrWhiteSpace(x.ItemNumber))
            .Select(x => x.ItemNumber!.Trim())
            .Distinct(StringComparer.OrdinalIgnoreCase)
            .ToList();
        var rawStockMap = await LoadRawStockMapAsync(itemNumbers, tenantId);

        // 扣减已确认交期(status=1)的跨评审持久占用（三张新表）
        var confirmedStock = await LoadConfirmedOccupyMapAsync(itemNumbers, tenantId, "b_stock_occupy", "qty", hasIsDeleted: false);
        var confirmedPurchase = await LoadConfirmedOccupyMapAsync(itemNumbers, tenantId, "b_purchase_occupy", "qty");
        var confirmedMo = await LoadConfirmedOccupyMapAsync(itemNumbers, tenantId, "b_mo_occupy", "moo_qty");

        foreach (var line in lines)
        {
            if (string.IsNullOrWhiteSpace(line.ItemNumber))
                continue;

            if (!stockMap.TryGetValue(line.ItemNumber, out var stock))
            {
                var rawStock = rawStockMap.TryGetValue(line.ItemNumber, out var rs) ? rs : 0m;
                // 扣减已确认交期(status=1)的跨评审持久占用
                var cfStock = confirmedStock.TryGetValue(line.ItemNumber, out var cfs) ? cfs : 0m;
                rawStock = Math.Max(0m, rawStock - cfStock);
                // 扣减已被同批次其他工单占用的库存
                var occupied = occupiedStockMap.TryGetValue(line.ItemNumber, out var occ) ? occ : 0m;
                stock = Math.Max(0m, rawStock - occupied);
                stockMap[line.ItemNumber] = stock;
            }

            if (!transitMap.TryGetValue(line.ItemNumber, out var inTransit))
            {
                var rawTransit = await QueryOpenPurchaseQtyAsync(line.ItemNumber, tenantId);
                // 扣减已确认交期(status=1)的跨评审持久占用
                var cfTransit = confirmedPurchase.TryGetValue(line.ItemNumber, out var cft) ? cft : 0m;
                rawTransit = Math.Max(0m, rawTransit - cfTransit);
                // 扣减已被同批次其他工单占用的在途
                var occupied = occupiedTransitMap.TryGetValue(line.ItemNumber, out var occ) ? occ : 0m;
                inTransit = Math.Max(0m, rawTransit - occupied);
                transitMap[line.ItemNumber] = inTransit;
            }

            line.StockQty = stock;
            line.PurchaseOccupyQty = inTransit;
            // sqty = 可用库存数量（不截断）
            line.Sqty = RoundQty(stock);
            var available = RoundQty(stock + inTransit);
            // use_qty = 实际占用数量 = MIN(需求量, 可用总量)
            line.UseQty = RoundQty(Math.Min(line.NeedCount, available));
            line.LackQty = RoundQty(Math.Max(0m, line.NeedCount - line.UseQty));
            line.SelfLackQty = line.LackQty;
            line.MoQty = 0;
            line.MakeQty = line.ErpCls == 1 ? line.LackQty : 0;
            line.PurchaseQty = line.ErpCls == 3 ? line.LackQty : 0;

            // 虚拟件(erp_cls=4)及排除规则命中项(IsUse=0)仅展示BOM结构，不参与库存消耗与缺料计算
            if (line.ErpCls == 4 || line.IsUse != 1)
            {
                line.Sqty = 0;
                line.LackQty = 0;
                line.SelfLackQty = 0;
                line.UseQty = 0;
                line.MakeQty = 0;
                line.PurchaseQty = 0;
                continue;
            }

            var consumedFromStock = RoundQty(Math.Min(stock, line.UseQty));
            stockMap[line.ItemNumber] = RoundQty(Math.Max(0m, stock - consumedFromStock));
            var remainingUseQty = RoundQty(Math.Max(0m, line.UseQty - consumedFromStock));
            transitMap[line.ItemNumber] = RoundQty(Math.Max(0m, inTransit - remainingUseQty));
        }

        // 写入占用记录（库存占用和在途占用）。只有排产写；手动齐套检查是查询语义，只读不写
        if (bangId > 0 && writeOccupy)
        {
            foreach (var line in lines)
            {
                if (string.IsNullOrWhiteSpace(line.ItemNumber) || line.IsUse != 1)
                    continue;
                if (line.UseQty > 0)
                {
                    var stockUsed = RoundQty(Math.Min(line.StockQty, line.UseQty));
                    if (stockUsed > 0)
                    {
                        await _db.Ado.ExecuteCommandAsync(
                            """
                            INSERT INTO ic_item_stockoccupy (
                                Id, icitem_number, quantity, morder_mo, morder_id, bang_id,
                                orderentry_id, tenant_id, occupy_time, create_time, update_time
                            ) VALUES (
                                @Id, @ItemNumber, @Qty, @WorkOrd, @MorderId, @BangId,
                                @EntryId, @TenantId, @Now, @Now, @Now
                            )
                            """,
                            new SugarParameter("@Id", YitIdHelper.NextId()),
                            new SugarParameter("@ItemNumber", line.ItemNumber),
                            new SugarParameter("@Qty", stockUsed),
                            new SugarParameter("@WorkOrd", workOrdValue),
                            new SugarParameter("@MorderId", morderId),
                            new SugarParameter("@BangId", bangId),
                            new SugarParameter("@EntryId", entryId),
                            new SugarParameter("@TenantId", tenantId),
                            new SugarParameter("@Now", DateTime.Now));
                    }

                    var transitUsed = RoundQty(Math.Max(0m, line.UseQty - stockUsed));
                    if (transitUsed > 0)
                    {
                        await _db.Ado.ExecuteCommandAsync(
                            """
                            INSERT INTO srm_po_occupy (
                                Id, ItemNumber, OccupyQty, morder_mo, morder_id, bang_id,
                                polist_id, entry_id, tenant_id, create_time, update_time
                            ) VALUES (
                                @Id, @ItemNumber, @Qty, @WorkOrd, @MorderId, @BangId,
                                @PolistId, @EntryId, @TenantId, @Now, @Now
                            )
                            """,
                            new SugarParameter("@Id", YitIdHelper.NextId()),
                            new SugarParameter("@ItemNumber", line.ItemNumber),
                            new SugarParameter("@Qty", transitUsed),
                            new SugarParameter("@WorkOrd", workOrdValue),
                            new SugarParameter("@MorderId", morderId),
                            new SugarParameter("@BangId", bangId),
                            new SugarParameter("@PolistId", 0),
                            new SugarParameter("@EntryId", entryId),
                            new SugarParameter("@TenantId", tenantId),
                            new SugarParameter("@Now", DateTime.Now));
                    }
                }
            }
        }

    }

    /// <summary>
    /// 将资源检查结果写入订单评审三表占用追踪（b_stock_occupy / b_purchase_occupy / b_mo_occupy），status=0。
    /// 由 OrderReviewOrchestrationService 在获取 examineId 后调用。
    /// </summary>
    public async Task WriteReviewOccupyAsync(
        IReadOnlyList<ResourceCheckBomLine> lines,
        long tenantId, long entryId, string billNo,
        long? examineId)
    {
        if (lines.Count == 0 || string.IsNullOrWhiteSpace(billNo))
            return;

        try
        {
        var now = DateTime.Now;

        // 收集消耗了库存的物料
        var stockConsumedItems = lines
            .Where(x => !string.IsNullOrWhiteSpace(x.ItemNumber) && x.IsUse == 1 && x.UseQty > 0)
            .ToDictionary(
                x => x.ItemNumber!.Trim(),
                x => RoundQty(Math.Min(x.StockQty, x.UseQty)),
                StringComparer.OrdinalIgnoreCase);

        // 按批次写入 b_stock_occupy
        if (stockConsumedItems.Count > 0)
        {
            var batchRows = await LoadInvMasterBatchAsync(
                stockConsumedItems.Keys.ToList(), tenantId);
            var consumedTracker = new Dictionary<string, decimal>(
                stockConsumedItems, StringComparer.OrdinalIgnoreCase);

            foreach (var batch in batchRows)
            {
                var remaining = consumedTracker.TryGetValue(batch.ItemNum, out var r) ? r : 0m;
                if (remaining <= 0) continue;
                var batchQty = Math.Min(remaining, batch.Qty);
                if (batchQty <= 0) continue;

                await _db.Ado.ExecuteCommandAsync(
                    """
                    INSERT INTO b_stock_occupy (
                        Id, bill_no, entry_id, lot_serial, item_number, qty,
                        examine_id, tenant_id, status, create_time, update_time
                    ) VALUES (
                        @Id, @BillNo, @EntryId, @LotSerial, @ItemNumber, @Qty,
                        @ExamineId, @TenantId, 0, @Now, @Now
                    )
                    """,
                    new SugarParameter("@Id", YitIdHelper.NextId()),
                    new SugarParameter("@BillNo", billNo),
                    new SugarParameter("@EntryId", entryId),
                    new SugarParameter("@LotSerial", batch.LotSerial ?? ""),
                    new SugarParameter("@ItemNumber", batch.ItemNum),
                    new SugarParameter("@Qty", batchQty),
                    new SugarParameter("@ExamineId", examineId ?? (object)DBNull.Value),
                    new SugarParameter("@TenantId", tenantId),
                    new SugarParameter("@Now", now));

                consumedTracker[batch.ItemNum] = RoundQty(remaining - batchQty);
            }
        }

        foreach (var line in lines)
        {
            if (string.IsNullOrWhiteSpace(line.ItemNumber) || line.IsUse != 1)
                continue;

            // b_purchase_occupy（在途消耗）
            var transitUsed = RoundQty(Math.Max(0m, line.UseQty - Math.Min(line.StockQty, line.UseQty)));
            if (transitUsed > 0)
            {
                await _db.Ado.ExecuteCommandAsync(
                    """
                    INSERT INTO b_purchase_occupy (
                        Id, bill_no, entry_id, item_number, qty,
                        examine_id, tenant_id, status, IsDeleted, create_time, update_time
                    ) VALUES (
                        @Id, @BillNo, @EntryId, @ItemNumber, @Qty,
                        @ExamineId, @TenantId, 0, 0, @Now, @Now
                    )
                    """,
                    new SugarParameter("@Id", YitIdHelper.NextId()),
                    new SugarParameter("@BillNo", billNo),
                    new SugarParameter("@EntryId", entryId),
                    new SugarParameter("@ItemNumber", line.ItemNumber),
                    new SugarParameter("@Qty", transitUsed),
                    new SugarParameter("@ExamineId", examineId ?? (object)DBNull.Value),
                    new SugarParameter("@TenantId", tenantId),
                    new SugarParameter("@Now", now));
            }

            // b_mo_occupy（制造缺口，erp_cls=1 自制类）
            if (line.MakeQty > 0)
            {
                await _db.Ado.ExecuteCommandAsync(
                    """
                    INSERT INTO b_mo_occupy (
                        Id, bill_no, entry_id, item_number, moo_qty,
                        examine_id, tenant_id, status, IsDeleted, create_time, update_time
                    ) VALUES (
                        @Id, @BillNo, @EntryId, @ItemNumber, @Qty,
                        @ExamineId, @TenantId, 0, 0, @Now, @Now
                    )
                    """,
                    new SugarParameter("@Id", YitIdHelper.NextId()),
                    new SugarParameter("@BillNo", billNo),
                    new SugarParameter("@EntryId", entryId),
                    new SugarParameter("@ItemNumber", line.ItemNumber),
                    new SugarParameter("@Qty", line.MakeQty),
                    new SugarParameter("@ExamineId", examineId ?? (object)DBNull.Value),
                    new SugarParameter("@TenantId", tenantId),
                    new SugarParameter("@Now", now));
            }
        }
        } // end try
        catch (Exception ex)
        {
            _logger.LogWarning(ex, "写入占用表(b_stock_occupy/b_purchase_occupy/b_mo_occupy)失败，可能缺少列，跳过");
        }
    }

    /// <summary>
    /// 查询订单评审占用表中已确认交期(status=1)的物料占用量汇总。
    /// </summary>
    private async Task<Dictionary<string, decimal>> LoadConfirmedOccupyMapAsync(
        List<string> itemNumbers, long tenantId, string tableName, string qtyColumn, bool hasIsDeleted = true)
    {
        if (itemNumbers.Count == 0)
            return new Dictionary<string, decimal>(StringComparer.OrdinalIgnoreCase);

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

        var deletedFilter = hasIsDeleted ? "AND IFNULL(IsDeleted, 0) = 0" : "";
        try
        {
            var rows = await _db.Ado.SqlQueryAsync<OccupyRow>(
                $"""
                SELECT item_number AS ItemNumber, IFNULL(SUM({qtyColumn}), 0) AS TotalOccupied
                FROM {tableName}
                WHERE tenant_id = @TenantId AND status = 1 {deletedFilter}
                  AND item_number IN ({string.Join(",", names)})
                GROUP BY item_number
                """,
                pars);

            var map = new Dictionary<string, decimal>(StringComparer.OrdinalIgnoreCase);
            foreach (var r in rows)
            {
                if (string.IsNullOrWhiteSpace(r.ItemNumber)) continue;
                map[r.ItemNumber.Trim()] = RoundQty(r.TotalOccupied);
            }
            return map;
        }
        catch (Exception ex)
        {
            _logger.LogWarning(ex, "查询占用表 {Table} 失败（可能缺少列），跳过扣减", tableName);
            return new Dictionary<string, decimal>(StringComparer.OrdinalIgnoreCase);
        }
    }

    /// <summary>
    /// 按物料号批量查询 InvMaster 批次明细（ItemNum + LotSerial + Qty）。
    /// </summary>
    private async Task<List<InvMasterBatchRow>> LoadInvMasterBatchAsync(
        List<string> itemNumbers, long tenantId)
    {
        if (itemNumbers.Count == 0)
            return new List<InvMasterBatchRow>();

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

        return await _db.Ado.SqlQueryAsync<InvMasterBatchRow>(
            $"""
            SELECT ItemNum, IFNULL(LotSerial, '') AS LotSerial,
                   COALESCE(AvailStatusQty, QtyOnHand, 0) AS Qty
            FROM InvMaster
            WHERE tenant_id = @TenantId
              AND ItemNum IN ({string.Join(",", names)})
            ORDER BY ItemNum, LotSerial
            """,
            pars);
    }

    /// <summary>
    /// 批量取业务可用量：经 IInventoryBalanceReader（白名单库位 + AvailStatusQty，LIVE→STD）。
    /// </summary>
    private async Task<Dictionary<string, decimal>> LoadRawStockMapAsync(
        List<string> itemNumbers, long tenantId)
    {
        if (tenantId <= 0)
            throw Oops.Oh("数据异常：无法确定租户，无法查询库存");
        if (itemNumbers.Count == 0)
            return new Dictionary<string, decimal>(StringComparer.OrdinalIgnoreCase);

        var map = await _balanceReader.LoadAvailableQtyAsync(tenantId, itemNumbers);
        return new Dictionary<string, decimal>(
            map.ToDictionary(kv => kv.Key, kv => RoundQty(kv.Value)),
            StringComparer.OrdinalIgnoreCase);
    }

    private async Task<decimal> QueryStockQtyAsync(string itemNum, long tenantId)
    {
        if (tenantId <= 0)
            throw Oops.Oh("数据异常：无法确定租户，无法查询库存");
        var map = await _balanceReader.LoadAvailableQtyAsync(tenantId, new[] { itemNum });
        return map.TryGetValue(itemNum, out var q) ? RoundQty(q) : 0m;
    }

    private async Task<decimal> QueryOpenPurchaseQtyAsync(string itemNum, long tenantId)
    {
        var qty = await _db.Ado.GetDecimalAsync(
            """
            SELECT COALESCE(SUM(
                GREATEST(IFNULL(d.QtyOrded, 0) - IFNULL(d.QtyReceived, 0), 0)
            ), 0)
            FROM PurOrdDetail d
            INNER JOIN PurOrdMaster m ON m.RecID = d.PurOrdRecID
            WHERE d.ItemNum = @ItemNum
              AND m.tenant_id = @TenantId
              AND IFNULL(d.IsActive, 1) = 1
              AND IFNULL(m.IsActive, 1) = 1
            """,
            new SugarParameter("@ItemNum", itemNum),
            new SugarParameter("@TenantId", tenantId));
        return RoundQty(qty);
    }

    private static decimal RoundQty(decimal value) =>
        Math.Round(value, 4, MidpointRounding.AwayFromZero);

    private static string MapErpClsName(int erpCls) => erpCls switch
    {
        0 => "配置类",
        1 => "自制",
        2 => "委外加工",
        3 => "外购",
        4 => "虚拟件",
        _ => string.Empty
    };

    private sealed class BomHeaderRow
    {
        public long Id { get; set; }
        public string? BomNumber { get; set; }
        public string? ItemNumber { get; set; }
    }

    private sealed class BomChildRow
    {
        public long Id { get; set; }
        public string? BomNumber { get; set; }
        public string? ItemNumber { get; set; }
        public string? ItemName { get; set; }
        public string? Model { get; set; }
        public string? Unit { get; set; }
        public decimal Qty { get; set; }
        public decimal Wastage { get; set; }
        public decimal Scrap { get; set; }
        public int Backflush { get; set; }
        public int Type { get; set; }
        public int ErpCls { get; set; }
        public int IsBom { get; set; }
        public int HaveIcSubs { get; set; }
        public string? SubstituteCode { get; set; }
        public string? ItemType { get; set; }
        public string? OwnerApplication { get; set; }
    }

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

    private sealed class InvMasterBatchRow
    {
        public string ItemNum { get; set; } = string.Empty;
        public string? LotSerial { get; set; }
        public decimal Qty { get; set; }
    }

    private sealed class ExcludeRuleRow
    {
        public string? ExcludedItemTypes { get; set; }
        public string? ExcludedOwnerApps { get; set; }
        public bool IsActive { get; set; }
    }
}