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);

        // 是否真的尝试过实时源。未尝试（无选择性筛选 / StdOnly）时回落标准层属正常策略，
        // 不能对用户提示「实时库不可用」——只有尝试过且失败才算降级。
        var liveFailed = false;

        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)
            {
                liveFailed = true;
                _logger.LogWarning("[InventoryBalanceReader] LIVE_165 超时，回落 STD tenant={Tenant}", q.TenantId);
            }
            catch (Exception ex)
            {
                liveFailed = true;
                _logger.LogWarning(ex, "[InventoryBalanceReader] LIVE_165 失败，回落 STD tenant={Tenant}", q.TenantId);
            }
        }

        var std = await _std.QueryPageAsync(q, cancellationToken);
        std.FallbackFromLive = liveFailed;
        return std;
    }

    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 实时余额（查询页用）。
/// **只返回当前租户 LocationMaster 白名单（Typed &lt;&gt; 'Supp'）内的库位**，
/// 不再按 Domain 全量返回；白名单为空则 fail closed 返回 0 行。
/// </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)
    {
        // —— 安全第一层：当前租户的合法库位边界（Typed<>'Supp'），先于任何远端访问求得。
        // 用户传入的 Location 只能在其内部收窄（Intersect），不得替代、不得绕过。
        var scope = TenantLocationScope
            .FromWhitelist(await LoadWhitelistLocationsAsync(query.TenantId, query.Domain, cancellationToken))
            .Intersect(query.Location);

        // fail closed：空边界绝不等于全 Domain，直接返回空页且不触碰源库。
        if (scope.IsEmpty) return EmptyLivePage(query);

        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 (locationClause, locationPars) = scope.BuildInClause("d.Location", "loc");
        var where = new List<string>
        {
            "t.Domain = @Domain",
            locationClause,
            "(ISNULL(d.AvailStatusQty,0)+ISNULL(d.Assay,0)+ISNULL(d.FreezeQty,0)) > 0"
        };
        var pars = new List<SugarParameter> { new("@Domain", query.Domain) };
        pars.AddRange(locationPars);
        if (!string.IsNullOrWhiteSpace(query.Type))
        {
            where.Add($"({locTypeExpr}) = @Type");
            pars.Add(new SugarParameter("@Type", query.Type.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
        };
    }

    /// <summary>租户库位边界为空时的 fail-closed 结果：0 行，且不发起任何源库查询。</summary>
    private static InventoryBalancePage EmptyLivePage(InventoryBalanceQuery query) => new()
    {
        Total = 0,
        Page = query.Page,
        PageSize = query.PageSize,
        List = Array.Empty<InventoryBalanceRow>(),
        GrandTotalQtyOnHand = 0m,
        GrandUnrestricted = 0m,
        GrandQc = 0m,
        GrandFrozen = 0m,
        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;
    }

    /// <summary>与 STD 共用同一口径（<see cref="TenantLocationScopeLoader"/>），禁止两处各写一份 SQL。</summary>
    private Task<List<string>> LoadWhitelistLocationsAsync(
        long tenantId, string domain, CancellationToken ct)
        => TenantLocationScopeLoader.LoadAsync(_db, tenantId, domain, ct);

    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）。
/// **只返回当前租户 LocationMaster 白名单（Typed &lt;&gt; 'Supp'）内的库位**，口径与 LIVE 一致；
/// 白名单为空则 fail closed 返回 0 行。历史错误归属的脏快照会被这一层挡住。
/// </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;
    }

    /// <summary>与 LIVE 共用同一口径（<see cref="TenantLocationScopeLoader"/>）。</summary>
    private Task<List<string>> LoadWhitelistLocationsAsync(
        long tenantId, string domain, CancellationToken ct)
        => TenantLocationScopeLoader.LoadAsync(_db, tenantId, domain, ct);

    /// <summary>租户库位边界为空时的 fail-closed 结果：0 行。</summary>
    private static InventoryBalancePage EmptyStdPage(InventoryBalanceQuery query) => new()
    {
        Total = 0,
        Page = query.Page,
        PageSize = query.PageSize,
        List = Array.Empty<InventoryBalanceRow>(),
        GrandTotalQtyOnHand = 0m,
        GrandUnrestricted = 0m,
        GrandQc = 0m,
        GrandFrozen = 0m,
        DataSource = InventoryDataSources.Std,
        AsOf = DateTime.Now,
        IsRealtime = false
    };

    public async Task<InventoryBalancePage> QueryPageAsync(
        InventoryBalanceQuery query,
        CancellationToken cancellationToken = default)
    {
        // —— 安全第一层：与 LIVE 同一口径的租户库位边界（Typed<>'Supp'），
        // 用户传入的 Location 只能在其内部收窄。历史脏快照即使还留在表里也会被这一层挡住。
        var scope = TenantLocationScope
            .FromWhitelist(await LoadWhitelistLocationsAsync(query.TenantId, query.Domain, cancellationToken))
            .Intersect(query.Location);

        // fail closed：空边界绝不等于整个 domain
        if (scope.IsEmpty) return EmptyStdPage(query);

        const string locTypeExpr = "CASE WHEN t.typed='VMI' THEN 'VMI' WHEN t.typed='Supp' THEN 'O库' ELSE '' END";
        var (locationClause, locationPars) = scope.BuildInClause("s.location", "loc");
        var where = new List<string>
        {
            "s.tenant_id = @TenantId",
            "s.domain = @Domain",
            "s.source_system = @SourceSystem",
            locationClause,
            "(IFNULL(s.qty_unrestricted,0)+IFNULL(s.qty_inspection,0)+IFNULL(s.qty_frozen,0)) > 0",
            "t.tenant_id = @TenantId",
            // 第二层：JOIN 到的库位主数据本身也不得是供应商库存
            "IFNULL(t.typed,'') <> 'Supp'"
        };
        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)
        };
        pars.AddRange(locationPars);

        if (!string.IsNullOrWhiteSpace(query.Type))
        {
            where.Add($"({locTypeExpr}) = @Type");
            pars.Add(new SugarParameter("@Type", query.Type.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.id AS Id,
                   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
             -- ItemMaster 同 Domain 下存在重复 ItemNum（实测 8010 有 151 个），
             -- 直接 LEFT JOIN 会放大行数，使分页取回行数大于 COUNT 出来的 total。
             -- 与下方 MissedPrint 同款处理：先按业务键收敛成一行再关联。
             LEFT JOIN (
                 SELECT Domain, ItemNum, MAX(Descr) AS Descr, MAX(UM) AS UM
                 FROM ItemMaster
                 WHERE Domain=@Domain
                 GROUP BY Domain, ItemNum
             ) 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
             -- 业务排序保持 库位/物料/批次 不变，末尾补主键作唯一 tiebreaker：
             -- 三者组合非唯一，OFFSET 分页在并列行上会跨页重复/漏行。
             ORDER BY n.Location, n.ItemNum, n.LotSerial, n.Id
             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 LoadWhitelistLocationsAsync(tenantId, domain, cancellationToken);
        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; }
    }
}