AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/MaterialWarehouse/InventoryReconService.cs

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

namespace Admin.NET.Plugin.AiDOP.MaterialWarehouse;

/// <summary>165 LocationDetail ↔ mdp_std_inventory 日终对账 + 冷链滞后检测。</summary>
public sealed class InventoryReconService : ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly MdpSourceScopeFactory _scopeFactory;
    private readonly SourceDomainTenantResolver _domainTenant;
    private readonly AidopInventoryOptions _opt;
    private readonly ILogger _logger;

    public InventoryReconService(
        ISqlSugarClient db,
        MdpSourceScopeFactory scopeFactory,
        SourceDomainTenantResolver domainTenant,
        IOptions<AidopInventoryOptions> opt,
        ILoggerFactory loggerFactory)
    {
        _db = db;
        _scopeFactory = scopeFactory;
        _domainTenant = domainTenant;
        _opt = opt.Value;
        _logger = loggerFactory.CreateLogger(nameof(InventoryReconService));
    }

    public async Task<int> RunDailyReconAsync(CancellationToken cancellationToken = default)
    {
        var sourceCode = string.IsNullOrWhiteSpace(_opt.SourceCode) ? "DOPDEMORQ_SQLSERVER" : _opt.SourceCode.Trim();
        var domain = string.IsNullOrWhiteSpace(_opt.DefaultDomain) ? "8010" : _opt.DefaultDomain.Trim();
        var tenantId = await _domainTenant.ResolveTenantIdAsync(sourceCode, domain, cancellationToken);
        var asOf = DateTime.Now;
        var batchId = $"S5_INV_RECON_{asOf:yyyyMMddHHmmss}";

        await CheckSyncLagAsync(cancellationToken);

        var remote = await _scopeFactory.GetScopeAsync(sourceCode, cancellationToken);
        var live = await remote.Ado.SqlQueryAsync<QtyKeyRow>(
            """
            SELECT Domain, ItemNum, ISNULL(LotSerial,'') AS LotSerial, Location,
                   ISNULL(AvailStatusQty,0) AS Qty
            FROM LocationDetail
            WHERE Domain = @Domain
              AND (ISNULL(AvailStatusQty,0)+ISNULL(Assay,0)+ISNULL(FreezeQty,0)) <> 0
            """,
            new List<SugarParameter> { new("@Domain", domain) });

        var std = await _db.Ado.SqlQueryAsync<QtyKeyRow>(
            """
            SELECT domain AS Domain, item_num AS ItemNum, IFNULL(lot_serial,'') AS LotSerial,
                   location AS Location, IFNULL(qty_unrestricted,0) AS Qty
            FROM mdp_std_inventory
            WHERE tenant_id=@TenantId AND domain=@Domain AND source_system=@SourceSystem
            """,
            new List<SugarParameter>
            {
                new("@TenantId", tenantId),
                new("@Domain", domain),
                new("@SourceSystem", sourceCode)
            });

        var liveMap = live.ToDictionary(
            x => Key(x), x => x.Qty, StringComparer.OrdinalIgnoreCase);
        var stdMap = std.ToDictionary(
            x => Key(x), x => x.Qty, StringComparer.OrdinalIgnoreCase);

        var keys = liveMap.Keys.Union(stdMap.Keys, StringComparer.OrdinalIgnoreCase).ToList();
        var diffCount = 0;
        foreach (var key in keys)
        {
            cancellationToken.ThrowIfCancellationRequested();
            liveMap.TryGetValue(key, out var qLive);
            stdMap.TryGetValue(key, out var qStd);
            var diff = qLive - qStd;
            if (diff == 0) continue;
            diffCount++;
            var parts = key.Split('\u001f');
            await _db.Ado.ExecuteCommandAsync(
                """
                INSERT INTO ado_inventory_recon_diff
                  (tenant_id, domain, item_num, lot_serial, location,
                   qty_live, qty_std, qty_diff, recon_batch_id, as_of, remark)
                VALUES
                  (@TenantId, @Domain, @ItemNum, @Lot, @Loc,
                   @QtyLive, @QtyStd, @QtyDiff, @BatchId, @AsOf, NULL)
                """,
                new List<SugarParameter>
                {
                    new("@TenantId", tenantId),
                    new("@Domain", parts.ElementAtOrDefault(0) ?? domain),
                    new("@ItemNum", parts.ElementAtOrDefault(1) ?? ""),
                    new("@Lot", parts.ElementAtOrDefault(2) ?? ""),
                    new("@Loc", parts.ElementAtOrDefault(3) ?? ""),
                    new("@QtyLive", qLive),
                    new("@QtyStd", qStd),
                    new("@QtyDiff", diff),
                    new("@BatchId", batchId),
                    new("@AsOf", asOf)
                });
        }

        if (diffCount > 0)
            _logger.LogWarning("[InventoryRecon] batch={Batch} diffs={Diffs}", batchId, diffCount);
        else
            _logger.LogInformation("[InventoryRecon] batch={Batch} clean", batchId);

        return diffCount;
    }

    private async Task CheckSyncLagAsync(CancellationToken ct)
    {
        var maxAge = _opt.StdMaxAgeMinutes <= 0 ? 90 : _opt.StdMaxAgeMinutes;
        var entities = await _db.Queryable<MdpEntity>()
            .Where(x => x.EntityCode == "S5_LOCATION_DETAIL_SQLSERVER"
                        || x.EntityCode == "S5_INV_TRANS_HIST_SQLSERVER")
            .ToListAsync(ct);

        foreach (var e in entities)
        {
            if (!e.LastSyncTo.HasValue)
            {
                _logger.LogWarning("[InventoryRecon] 冷链无 last_sync_to entity={Code}", e.EntityCode);
                continue;
            }
            var age = (DateTime.Now - e.LastSyncTo.Value).TotalMinutes;
            if (age > maxAge)
                _logger.LogWarning(
                    "[InventoryRecon] 冷链滞后 entity={Code} lastSync={Last} ageMin={Age} threshold={Th}",
                    e.EntityCode, e.LastSyncTo, (int)age, maxAge);
        }
    }

    private static string Key(QtyKeyRow r) =>
        $"{r.Domain}\u001f{r.ItemNum}\u001f{r.LotSerial}\u001f{r.Location}";

    private sealed class QtyKeyRow
    {
        public string? Domain { get; set; }
        public string? ItemNum { get; set; }
        public string? LotSerial { get; set; }
        public string? Location { get; set; }
        public decimal Qty { get; set; }
    }
}