using Admin.NET.Plugin.AiDOP.DataPlatform;
using Admin.NET.Plugin.AiDOP.Infrastructure;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using SqlSugar;

namespace Admin.NET.Plugin.AiDOP.MaterialWarehouse;

public interface IInventoryBalanceReader
{
    Task<InventoryBalancePage> QueryPageAsync(
        InventoryBalanceQuery query,
        CancellationToken cancellationToken = default);

    /// <summary>
    /// 业务可用量：仅白名单库位、qty_available=AvailStatusQty（不加 Assay）。
    /// </summary>
    Task<IReadOnlyDictionary<string, decimal>> LoadAvailableQtyAsync(
        long tenantId,
        IReadOnlyCollection<string> itemNumbers,
        CancellationToken cancellationToken = default);
}

/// <summary>余额门面：LiveThenStd / StdOnly。</summary>
public sealed class InventoryBalanceReader : IInventoryBalanceReader, ITransient
{
    private readonly Live165InventorySource _live;
    private readonly StdInventorySource _std;
    private readonly SourceDomainTenantResolver _domainTenant;
    private readonly AidopInventoryOptions _opt;
    private readonly ILogger _logger;

    public InventoryBalanceReader(
        Live165InventorySource live,
        StdInventorySource std,
        SourceDomainTenantResolver domainTenant,
        IOptions<AidopInventoryOptions> opt,
        ILoggerFactory loggerFactory)
    {
        _live = live;
        _std = std;
        _domainTenant = domainTenant;
        _opt = opt.Value;
        _logger = loggerFactory.CreateLogger(nameof(InventoryBalanceReader));
    }

    public async Task<InventoryBalancePage> QueryPageAsync(
        InventoryBalanceQuery query,
        CancellationToken cancellationToken = default)
    {
        var maxPage = _opt.MaxPageSize <= 0 ? 200 : _opt.MaxPageSize;
        var pageSize = Math.Clamp(query.PageSize <= 0 ? 10 : query.PageSize, 1, maxPage);
        var page = query.Page <= 0 ? 1 : query.Page;
        var q = new InventoryBalanceQuery
        {
            TenantId = query.TenantId,
            Domain = query.Domain,
            Type = query.Type,
            Location = query.Location,
            MaterialCode = query.MaterialCode,
            BatchNo = query.BatchNo,
            StockGt = query.StockGt,
            UnrestrictedGt = query.UnrestrictedGt,
            QcStockGt = query.QcStockGt,
            FrozenGt = query.FrozenGt,
            Page = page,
            PageSize = pageSize
        };

        var selective = HasSelectiveFilter(q);
        var mode = (_opt.BalanceMode ?? "LiveThenStd").Trim();
        var stdOnly = string.Equals(mode, "StdOnly", StringComparison.OrdinalIgnoreCase);

        if (!stdOnly && selective)
        {
            try
            {
                using var cts = CancellationTokenSource.CreateLinkedTokenSource(cancellationToken);
                cts.CancelAfter(TimeSpan.FromSeconds(Math.Max(1, _opt.LiveTimeoutSeconds)));
                var live = await _live.QueryPageAsync(q, cts.Token);
                return live;
            }
            catch (OperationCanceledException) when (!cancellationToken.IsCancellationRequested)
            {
                _logger.LogWarning("[InventoryBalanceReader] LIVE_165 超时，回落 STD tenant={Tenant}", q.TenantId);
            }
            catch (Exception ex)
            {
                _logger.LogWarning(ex, "[InventoryBalanceReader] LIVE_165 失败，回落 STD tenant={Tenant}", q.TenantId);
            }
        }

        return await _std.QueryPageAsync(q, cancellationToken);
    }

    public async Task<IReadOnlyDictionary<string, decimal>> LoadAvailableQtyAsync(
        long tenantId,
        IReadOnlyCollection<string> itemNumbers,
        CancellationToken cancellationToken = default)
    {
        var items = itemNumbers
            .Where(x => !string.IsNullOrWhiteSpace(x))
            .Select(x => x.Trim())
            .Distinct(StringComparer.OrdinalIgnoreCase)
            .ToList();
        if (items.Count == 0)
            return new Dictionary<string, decimal>(StringComparer.OrdinalIgnoreCase);

        var sourceCode = string.IsNullOrWhiteSpace(_opt.SourceCode) ? "DOPDEMORQ_SQLSERVER" : _opt.SourceCode.Trim();
        var domain = await _domainTenant.ResolveDomainAsync(sourceCode, tenantId, cancellationToken);
        var mode = (_opt.BalanceMode ?? "LiveThenStd").Trim();
        var stdOnly = string.Equals(mode, "StdOnly", StringComparison.OrdinalIgnoreCase);

        if (!stdOnly)
        {
            try
            {
                using var cts = CancellationTokenSource.CreateLinkedTokenSource(cancellationToken);
                cts.CancelAfter(TimeSpan.FromSeconds(Math.Max(1, _opt.LiveTimeoutSeconds)));
                return await _live.LoadAvailableQtyAsync(tenantId, domain, items, cts.Token);
            }
            catch (OperationCanceledException) when (!cancellationToken.IsCancellationRequested)
            {
                _logger.LogWarning("[InventoryBalanceReader] LoadAvailableQty LIVE 超时，回落 STD");
            }
            catch (Exception ex)
            {
                _logger.LogWarning(ex, "[InventoryBalanceReader] LoadAvailableQty LIVE 失败，回落 STD");
            }
        }

        var std = await _std.LoadAvailableQtyAsync(tenantId, domain, items, cancellationToken);
        if (std.Count == 0 && items.Count > 0)
        {
            // STD 无快照时不得静默 0：仍返回空字典由调用方当 0，但打 Error 便于运维
            _logger.LogError(
                "[InventoryBalanceReader] STD 无可用库存快照 tenant={Tenant} domain={Domain} items={Count}",
                tenantId, domain, items.Count);
        }
        return std;
    }

    private static bool HasSelectiveFilter(InventoryBalanceQuery q) =>
        !string.IsNullOrWhiteSpace(q.MaterialCode)
        || !string.IsNullOrWhiteSpace(q.BatchNo)
        || !string.IsNullOrWhiteSpace(q.Location);
}

/// <summary>165 LocationDetail 实时余额（查询页用，展示全部库位）。</summary>
public sealed class Live165InventorySource : ITransient
{
    private readonly MdpSourceScopeFactory _scopeFactory;
    private readonly ISqlSugarClient _db;
    private readonly AidopInventoryOptions _opt;

    public Live165InventorySource(
        MdpSourceScopeFactory scopeFactory,
        ISqlSugarClient db,
        IOptions<AidopInventoryOptions> opt)
    {
        _scopeFactory = scopeFactory;
        _db = db;
        _opt = opt.Value;
    }

    public async Task<InventoryBalancePage> QueryPageAsync(
        InventoryBalanceQuery query,
        CancellationToken cancellationToken = default)
    {
        var sourceCode = string.IsNullOrWhiteSpace(_opt.SourceCode) ? "DOPDEMORQ_SQLSERVER" : _opt.SourceCode.Trim();
        var remote = await _scopeFactory.GetScopeAsync(sourceCode, cancellationToken);

        const string locTypeExpr = "CASE WHEN t.Typed='VMI' THEN 'VMI' WHEN t.Typed='Supp' THEN N'O库' ELSE '' END";
        var where = new List<string>
        {
            "t.Domain = @Domain",
            "(ISNULL(d.AvailStatusQty,0)+ISNULL(d.Assay,0)+ISNULL(d.FreezeQty,0)) > 0"
        };
        var pars = new List<SugarParameter> { new("@Domain", query.Domain) };
        if (!string.IsNullOrWhiteSpace(query.Type))
        {
            where.Add($"({locTypeExpr}) = @Type");
            pars.Add(new SugarParameter("@Type", query.Type.Trim()));
        }
        if (!string.IsNullOrWhiteSpace(query.Location))
        {
            where.Add("d.Location = @Location");
            pars.Add(new SugarParameter("@Location", query.Location.Trim()));
        }
        if (!string.IsNullOrWhiteSpace(query.MaterialCode))
        {
            where.Add("d.ItemNum LIKE @MaterialCode");
            pars.Add(new SugarParameter("@MaterialCode", $"%{query.MaterialCode.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(query.BatchNo))
        {
            where.Add("ISNULL(d.LotSerial,'') LIKE @BatchNo");
            pars.Add(new SugarParameter("@BatchNo", $"%{query.BatchNo.Trim()}%"));
        }
        if (query.StockGt.HasValue)
        {
            where.Add("d.QtyOnHand > @StockGt");
            pars.Add(new SugarParameter("@StockGt", query.StockGt.Value));
        }
        if (query.UnrestrictedGt.HasValue)
        {
            where.Add("d.AvailStatusQty > @UnrestrictedGt");
            pars.Add(new SugarParameter("@UnrestrictedGt", query.UnrestrictedGt.Value));
        }
        if (query.QcStockGt.HasValue)
        {
            where.Add("d.Assay > @QcStockGt");
            pars.Add(new SugarParameter("@QcStockGt", query.QcStockGt.Value));
        }
        if (query.FrozenGt.HasValue)
        {
            where.Add("d.FreezeQty > @FrozenGt");
            pars.Add(new SugarParameter("@FrozenGt", query.FrozenGt.Value));
        }

        var whereSql = string.Join(" AND ", where);
        var baseQuery =
            $"""
            SELECT d.Location, d.ItemNum, ISNULL(d.LotSerial,'') AS LotSerial,
                   d.QtyOnHand, d.AvailStatusQty, d.Assay, d.FreezeQty,
                   {locTypeExpr} AS LocType, t.Descr AS LocationName
            FROM LocationMaster t
            INNER JOIN LocationDetail d ON t.Domain = d.Domain AND t.Location = d.Location
            WHERE {whereSql}
            """;

        var total = await remote.Ado.GetIntAsync($"SELECT COUNT(1) FROM ({baseQuery}) n", pars);
        var grand = (await remote.Ado.SqlQueryAsync<GrandRow>(
            $"""
             SELECT ISNULL(SUM(n.QtyOnHand),0) AS QtyOnHand,
                    ISNULL(SUM(n.AvailStatusQty),0) AS Unrestricted,
                    ISNULL(SUM(n.Assay),0) AS Qc,
                    ISNULL(SUM(n.FreezeQty),0) AS Frozen
             FROM ({baseQuery}) n
             """, pars)).FirstOrDefault() ?? new GrandRow();

        var offset = (query.Page - 1) * query.PageSize;
        var raw = await remote.Ado.SqlQueryAsync<RawBalanceRow>(
            $"""
             SELECT n.LocType, n.Location, n.LocationName, n.ItemNum, n.LotSerial,
                    n.AvailStatusQty, n.Assay, n.FreezeQty, n.QtyOnHand
             FROM ({baseQuery}) n
             ORDER BY n.Location, n.ItemNum, n.LotSerial
             OFFSET {offset} ROWS FETCH NEXT {query.PageSize} ROWS ONLY
             """, pars);

        var list = await EnrichLocalAsync(query.TenantId, query.Domain, raw, cancellationToken);
        return new InventoryBalancePage
        {
            Total = total,
            Page = query.Page,
            PageSize = query.PageSize,
            List = list,
            GrandTotalQtyOnHand = grand.QtyOnHand,
            GrandUnrestricted = grand.Unrestricted,
            GrandQc = grand.Qc,
            GrandFrozen = grand.Frozen,
            DataSource = InventoryDataSources.Live165,
            AsOf = DateTime.Now,
            IsRealtime = true
        };
    }

    public async Task<IReadOnlyDictionary<string, decimal>> LoadAvailableQtyAsync(
        long tenantId,
        string domain,
        IReadOnlyList<string> itemNumbers,
        CancellationToken cancellationToken = default)
    {
        var scopes = await LoadWhitelistLocationsAsync(tenantId, domain, cancellationToken);
        if (scopes.Count == 0)
            throw new InvalidOperationException($"库存库位白名单为空：tenant={tenantId}, domain={domain}");

        var sourceCode = string.IsNullOrWhiteSpace(_opt.SourceCode) ? "DOPDEMORQ_SQLSERVER" : _opt.SourceCode.Trim();
        var remote = await _scopeFactory.GetScopeAsync(sourceCode, cancellationToken);
        var result = new Dictionary<string, decimal>(StringComparer.OrdinalIgnoreCase);
        var batchSize = _opt.BatchSize <= 0 ? 500 : _opt.BatchSize;

        for (var offset = 0; offset < itemNumbers.Count; offset += batchSize)
        {
            cancellationToken.ThrowIfCancellationRequested();
            var chunk = itemNumbers.Skip(offset).Take(batchSize).ToList();
            var names = new List<string>(chunk.Count);
            var locNames = new List<string>(scopes.Count);
            var pars = new List<SugarParameter> { new("@Domain", domain) };
            for (var i = 0; i < chunk.Count; i++)
            {
                names.Add($"@i{i}");
                pars.Add(new SugarParameter($"@i{i}", chunk[i]));
            }
            for (var i = 0; i < scopes.Count; i++)
            {
                locNames.Add($"@l{i}");
                pars.Add(new SugarParameter($"@l{i}", scopes[i]));
            }

            var sql =
                $"""
                SELECT ItemNum AS ItemNum, SUM(ISNULL(AvailStatusQty, 0)) AS Qty
                FROM LocationDetail
                WHERE Domain = @Domain
                  AND ItemNum IN ({string.Join(",", names)})
                  AND Location IN ({string.Join(",", locNames)})
                GROUP BY ItemNum
                """;
            var rows = await remote.Ado.SqlQueryAsync<QtyRow>(sql, pars);
            foreach (var r in rows)
            {
                if (string.IsNullOrWhiteSpace(r.ItemNum)) continue;
                result[r.ItemNum.Trim()] = r.Qty;
            }
        }
        return result;
    }

    private async Task<List<string>> LoadWhitelistLocationsAsync(
        long tenantId, string domain, CancellationToken ct)
    {
        return await _db.Ado.SqlQueryAsync<string>(
            """
            SELECT location
            FROM ado_inventory_location_scope
            WHERE tenant_id=@TenantId AND domain=@Domain
              AND scope_type='AVAILABLE' AND status=1
            """,
            new List<SugarParameter>
            {
                new("@TenantId", tenantId),
                new("@Domain", domain)
            });
    }

    private async Task<List<InventoryBalanceRow>> EnrichLocalAsync(
        long tenantId, string domain, List<RawBalanceRow> raw, CancellationToken ct)
    {
        if (raw.Count == 0) return new List<InventoryBalanceRow>();

        var itemNums = raw.Select(x => x.ItemNum ?? "").Where(x => x.Length > 0).Distinct().ToList();
        var itemMap = new Dictionary<string, (string? Descr, string? Um)>(StringComparer.OrdinalIgnoreCase);
        if (itemNums.Count > 0)
        {
            var names = itemNums.Select((_, i) => $"@i{i}").ToList();
            var pars = new List<SugarParameter>
            {
                new("@Domain", domain),
                new("@TenantId", tenantId)
            };
            for (var i = 0; i < itemNums.Count; i++)
                pars.Add(new SugarParameter($"@i{i}", itemNums[i]));

            // ItemMaster 可能无 tenant 列：优先带 tenant，失败则仅 Domain
            List<ItemRow> items;
            try
            {
                items = await _db.Ado.SqlQueryAsync<ItemRow>(
                    $"""
                     SELECT ItemNum, Descr, UM
                     FROM ItemMaster
                     WHERE Domain=@Domain AND tenant_id=@TenantId
                       AND ItemNum IN ({string.Join(",", names)})
                     """, pars);
            }
            catch
            {
                items = await _db.Ado.SqlQueryAsync<ItemRow>(
                    $"""
                     SELECT ItemNum, Descr, UM
                     FROM ItemMaster
                     WHERE Domain=@Domain AND ItemNum IN ({string.Join(",", names)})
                     """, pars.Where(p => p.ParameterName != "@TenantId").ToList());
            }
            foreach (var it in items)
                if (!string.IsNullOrWhiteSpace(it.ItemNum))
                    itemMap[it.ItemNum] = (it.Descr, it.UM);
        }

        var mp = await _db.Ado.SqlQueryAsync<MpRow>(
            """
            SELECT Location, ItemNum, IFNULL(LotSerial,'') AS LotSerial,
                   MAX(Shelf) AS Shelf, MAX(LevelChar) AS LevelChar, MAX(Supply) AS Supply
            FROM MissedPrint
            WHERE Domain=@Domain AND IFNULL(Status,'') NOT IN ('U','P','C')
              AND IFNULL(Location,'') <> '' AND Qty > 0
            GROUP BY Location, ItemNum, IFNULL(LotSerial,'')
            """,
            new List<SugarParameter> { new("@Domain", domain) });
        var mpMap = mp.ToDictionary(
            x => $"{x.Location}|{x.ItemNum}|{x.LotSerial}",
            x => x,
            StringComparer.OrdinalIgnoreCase);

        var list = new List<InventoryBalanceRow>(raw.Count);
        foreach (var r in raw)
        {
            itemMap.TryGetValue(r.ItemNum ?? "", out var im);
            mpMap.TryGetValue($"{r.Location}|{r.ItemNum}|{r.LotSerial}", out var m);
            list.Add(new InventoryBalanceRow
            {
                Type = r.LocType,
                Location = r.Location,
                LocationName = r.LocationName,
                MaterialCode = r.ItemNum,
                MaterialName = im.Descr,
                BatchNo = r.LotSerial,
                Shelf = m?.Shelf ?? "",
                Version = m?.LevelChar ?? "",
                UnrestrictedStock = r.AvailStatusQty,
                QcStock = r.Assay,
                FrozenStock = r.FreezeQty,
                QtyOnHand = r.QtyOnHand,
                Unit = im.Um,
                Supplier = m?.Supply ?? ""
            });
        }
        return list;
    }

    private sealed class GrandRow
    {
        public decimal QtyOnHand { get; set; }
        public decimal Unrestricted { get; set; }
        public decimal Qc { get; set; }
        public decimal Frozen { get; set; }
    }

    private sealed class RawBalanceRow
    {
        public string? LocType { get; set; }
        public string? Location { get; set; }
        public string? LocationName { get; set; }
        public string? ItemNum { get; set; }
        public string? LotSerial { get; set; }
        public decimal AvailStatusQty { get; set; }
        public decimal Assay { get; set; }
        public decimal FreezeQty { get; set; }
        public decimal QtyOnHand { get; set; }
    }

    private sealed class ItemRow
    {
        public string? ItemNum { get; set; }
        public string? Descr { get; set; }
        public string? UM { get; set; }
    }

    private sealed class MpRow
    {
        public string? Location { get; set; }
        public string? ItemNum { get; set; }
        public string? LotSerial { get; set; }
        public string? Shelf { get; set; }
        public string? LevelChar { get; set; }
        public string? Supply { get; set; }
    }

    private sealed class QtyRow
    {
        public string? ItemNum { get; set; }
        public decimal Qty { get; set; }
    }
}

/// <summary>标准层余额（mdp_std_inventory），查询页展示全部库位。</summary>
public sealed class StdInventorySource : ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly AidopInventoryOptions _opt;

    public StdInventorySource(ISqlSugarClient db, IOptions<AidopInventoryOptions> opt)
    {
        _db = db;
        _opt = opt.Value;
    }

    public async Task<InventoryBalancePage> QueryPageAsync(
        InventoryBalanceQuery query,
        CancellationToken cancellationToken = default)
    {
        const string locTypeExpr = "CASE WHEN t.typed='VMI' THEN 'VMI' WHEN t.typed='Supp' THEN 'O库' ELSE '' END";
        var where = new List<string>
        {
            "s.tenant_id = @TenantId",
            "s.domain = @Domain",
            "s.source_system = @SourceSystem",
            "(IFNULL(s.qty_unrestricted,0)+IFNULL(s.qty_inspection,0)+IFNULL(s.qty_frozen,0)) > 0",
            "t.tenant_id = @TenantId"
        };
        var sourceSystem = string.IsNullOrWhiteSpace(_opt.SourceCode) ? "DOPDEMORQ_SQLSERVER" : _opt.SourceCode.Trim();
        var pars = new List<SugarParameter>
        {
            new("@TenantId", query.TenantId),
            new("@Domain", query.Domain),
            new("@SourceSystem", sourceSystem)
        };

        if (!string.IsNullOrWhiteSpace(query.Type))
        {
            where.Add($"({locTypeExpr}) = @Type");
            pars.Add(new SugarParameter("@Type", query.Type.Trim()));
        }
        if (!string.IsNullOrWhiteSpace(query.Location))
        {
            where.Add("s.location = @Location");
            pars.Add(new SugarParameter("@Location", query.Location.Trim()));
        }
        if (!string.IsNullOrWhiteSpace(query.MaterialCode))
        {
            where.Add("s.item_num LIKE @MaterialCode");
            pars.Add(new SugarParameter("@MaterialCode", $"%{query.MaterialCode.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(query.BatchNo))
        {
            where.Add("IFNULL(s.lot_serial,'') LIKE @BatchNo");
            pars.Add(new SugarParameter("@BatchNo", $"%{query.BatchNo.Trim()}%"));
        }
        if (query.StockGt.HasValue)
        {
            where.Add("s.qty_on_hand > @StockGt");
            pars.Add(new SugarParameter("@StockGt", query.StockGt.Value));
        }
        if (query.UnrestrictedGt.HasValue)
        {
            where.Add("s.qty_unrestricted > @UnrestrictedGt");
            pars.Add(new SugarParameter("@UnrestrictedGt", query.UnrestrictedGt.Value));
        }
        if (query.QcStockGt.HasValue)
        {
            where.Add("s.qty_inspection > @QcStockGt");
            pars.Add(new SugarParameter("@QcStockGt", query.QcStockGt.Value));
        }
        if (query.FrozenGt.HasValue)
        {
            where.Add("s.qty_frozen > @FrozenGt");
            pars.Add(new SugarParameter("@FrozenGt", query.FrozenGt.Value));
        }

        var whereSql = string.Join(" AND ", where);
        var baseQuery =
            $"""
            SELECT s.location AS Location, s.item_num AS ItemNum, IFNULL(s.lot_serial,'') AS LotSerial,
                   s.qty_on_hand AS QtyOnHand, s.qty_unrestricted AS AvailStatusQty,
                   s.qty_inspection AS Assay, s.qty_frozen AS FreezeQty,
                   {locTypeExpr} AS LocType, t.descr AS LocationName, s.as_of AS AsOf
            FROM mdp_std_inventory s
            INNER JOIN LocationMaster t
              ON t.Domain = s.domain AND t.location = s.location AND t.tenant_id = s.tenant_id
            WHERE {whereSql}
            """;

        var total = await _db.Ado.GetIntAsync($"SELECT COUNT(1) FROM ({baseQuery}) n", pars);
        var grand = (await _db.Ado.SqlQueryAsync<GrandRow>(
            $"""
             SELECT IFNULL(SUM(n.QtyOnHand),0) AS QtyOnHand,
                    IFNULL(SUM(n.AvailStatusQty),0) AS Unrestricted,
                    IFNULL(SUM(n.Assay),0) AS Qc,
                    IFNULL(SUM(n.FreezeQty),0) AS Frozen,
                    MAX(n.AsOf) AS AsOf
             FROM ({baseQuery}) n
             """, pars)).FirstOrDefault() ?? new GrandRow();

        var offset = (query.Page - 1) * query.PageSize;
        var list = await _db.Ado.SqlQueryAsync<InventoryBalanceRow>(
            $"""
             SELECT
                 n.LocType AS Type,
                 n.Location AS Location,
                 n.LocationName AS LocationName,
                 n.ItemNum AS MaterialCode,
                 i.Descr AS MaterialName,
                 n.LotSerial AS BatchNo,
                 IFNULL(mp.Shelf,'') AS Shelf,
                 IFNULL(mp.LevelChar,'') AS Version,
                 n.AvailStatusQty AS UnrestrictedStock,
                 n.Assay AS QcStock,
                 n.FreezeQty AS FrozenStock,
                 n.QtyOnHand AS QtyOnHand,
                 i.UM AS Unit,
                 IFNULL(mp.Supply,'') AS Supplier
             FROM ({baseQuery}) n
             LEFT JOIN ItemMaster i ON i.Domain=@Domain AND n.ItemNum=i.ItemNum
             LEFT JOIN (
                 SELECT Location, ItemNum, IFNULL(LotSerial,'') AS LotSerial,
                        MAX(Shelf) AS Shelf, MAX(LevelChar) AS LevelChar, MAX(Supply) AS Supply
                 FROM MissedPrint
                 WHERE Domain=@Domain AND IFNULL(Status,'') NOT IN ('U','P','C')
                   AND IFNULL(Location,'') <> '' AND Qty > 0
                 GROUP BY Location, ItemNum, IFNULL(LotSerial,'')
             ) mp ON mp.Location=n.Location AND mp.ItemNum=n.ItemNum AND mp.LotSerial=n.LotSerial
             ORDER BY n.Location, n.ItemNum, n.LotSerial
             LIMIT {query.PageSize} OFFSET {offset}
             """, pars);

        return new InventoryBalancePage
        {
            Total = total,
            Page = query.Page,
            PageSize = query.PageSize,
            List = list,
            GrandTotalQtyOnHand = grand.QtyOnHand,
            GrandUnrestricted = grand.Unrestricted,
            GrandQc = grand.Qc,
            GrandFrozen = grand.Frozen,
            DataSource = InventoryDataSources.Std,
            AsOf = grand.AsOf ?? DateTime.Now,
            IsRealtime = false
        };
    }

    public async Task<IReadOnlyDictionary<string, decimal>> LoadAvailableQtyAsync(
        long tenantId,
        string domain,
        IReadOnlyList<string> itemNumbers,
        CancellationToken cancellationToken = default)
    {
        var scopes = await _db.Ado.SqlQueryAsync<string>(
            """
            SELECT location
            FROM ado_inventory_location_scope
            WHERE tenant_id=@TenantId AND domain=@Domain
              AND scope_type='AVAILABLE' AND status=1
            """,
            new List<SugarParameter>
            {
                new("@TenantId", tenantId),
                new("@Domain", domain)
            });
        if (scopes.Count == 0)
            throw new InvalidOperationException($"库存库位白名单为空：tenant={tenantId}, domain={domain}");

        var sourceSystem = string.IsNullOrWhiteSpace(_opt.SourceCode) ? "DOPDEMORQ_SQLSERVER" : _opt.SourceCode.Trim();
        var result = new Dictionary<string, decimal>(StringComparer.OrdinalIgnoreCase);
        var batchSize = _opt.BatchSize <= 0 ? 500 : _opt.BatchSize;

        for (var offset = 0; offset < itemNumbers.Count; offset += batchSize)
        {
            cancellationToken.ThrowIfCancellationRequested();
            var chunk = itemNumbers.Skip(offset).Take(batchSize).ToList();
            var names = chunk.Select((_, i) => $"@i{i}").ToList();
            var locNames = scopes.Select((_, i) => $"@l{i}").ToList();
            var pars = new List<SugarParameter>
            {
                new("@TenantId", tenantId),
                new("@Domain", domain),
                new("@SourceSystem", sourceSystem)
            };
            for (var i = 0; i < chunk.Count; i++)
                pars.Add(new SugarParameter($"@i{i}", chunk[i]));
            for (var i = 0; i < scopes.Count; i++)
                pars.Add(new SugarParameter($"@l{i}", scopes[i]));

            var rows = await _db.Ado.SqlQueryAsync<QtyRow>(
                $"""
                 SELECT item_num AS ItemNum, SUM(IFNULL(qty_available,0)) AS Qty
                 FROM mdp_std_inventory
                 WHERE tenant_id=@TenantId AND domain=@Domain AND source_system=@SourceSystem
                   AND item_num IN ({string.Join(",", names)})
                   AND location IN ({string.Join(",", locNames)})
                 GROUP BY item_num
                 """, pars);
            foreach (var r in rows)
            {
                if (string.IsNullOrWhiteSpace(r.ItemNum)) continue;
                result[r.ItemNum.Trim()] = r.Qty;
            }
        }
        return result;
    }

    private sealed class GrandRow
    {
        public decimal QtyOnHand { get; set; }
        public decimal Unrestricted { get; set; }
        public decimal Qc { get; set; }
        public decimal Frozen { get; set; }
        public DateTime? AsOf { get; set; }
    }

    private sealed class QtyRow
    {
        public string? ItemNum { get; set; }
        public decimal Qty { get; set; }
    }
}