using System.Text.RegularExpressions;
using Admin.NET.Plugin.AiDOP.DataPlatform;
using Admin.NET.Plugin.AiDOP.MaterialWarehouse;
using SqlSugar;
using Xunit;

namespace Admin.NET.Plugin.AiDOP.Tests.S5.MaterialWarehouse;

/// <summary>
/// S5 库存日终对账（<see cref="InventoryReconService"/>）口径实库测试。
/// <para>
/// 验证的不变量：LIVE 与 STD 必须是**同一集合口径**——同一租户、同一 Domain、
/// 同一份 <see cref="TenantLocationScopeLoader"/> 库位白名单；否则差集里全是
/// 「本租户根本不该有的库位」，产生系统性假差异。
/// </para>
/// <para>
/// **门禁**：仅当 AIDOP_IT=1 时执行；触达 165 源库的用例额外要求 AIDOP_IT_165=1。
/// 全程**只读**：不写 aidopdev、不写 165、不写 ado_inventory_recon_diff、不调用
/// <c>RunDailyReconAsync</c>（它会写差异表）。
/// </para>
/// </summary>
[Trait("Category", "Integration")]
public class InventoryReconScopeTests
{
    private const long UatTenant = 838257186181189L;
    private const long AidopTenant = 797403760988229L;
    private const long DefaultTenant = 1300000000001L;
    private const string SourceCode = "DOPDEMORQ_SQLSERVER";
    private const string Domain = "8010";

    private static bool Enabled => Environment.GetEnvironmentVariable("AIDOP_IT") == "1";
    private static bool LiveEnabled => Enabled && Environment.GetEnvironmentVariable("AIDOP_IT_165") == "1";

    private readonly Xunit.Abstractions.ITestOutputHelper _out;
    public InventoryReconScopeTests(Xunit.Abstractions.ITestOutputHelper output) => _out = output;

    private static ISqlSugarClient BuildAidopdev()
    {
        const string path = "/home/yy968/work/New9S/AiDOPWarehouse/server/Admin.NET.Application/Configuration/Database.json";
        if (!File.Exists(path)) return null;
        var cs = File.ReadLines(path)
            .Select(l => l.Trim())
            .Where(l => !l.StartsWith("//") && l.Contains("\"ConnectionString\"") && l.Contains("Database=aidopdev") && l.Contains("123.60.180.165"))
            .Select(l => Regex.Match(l, "\"ConnectionString\"\\s*:\\s*\"([^\"]+)\"").Groups[1].Value)
            .FirstOrDefault(v => !string.IsNullOrEmpty(v));
        if (string.IsNullOrEmpty(cs)) return null;
        return new SqlSugarScope(new ConnectionConfig
        {
            ConfigId = "aidopdev-inv-recon-it", DbType = DbType.MySql, ConnectionString = cs, IsAutoCloseConnection = true,
        });
    }

    /// <summary>对账的租户枚举口径（必须与标准层物化侧一致）。</summary>
    private static Task<List<long>> ScopedTenantsAsync(ISqlSugarClient db, string domain) =>
        db.Ado.SqlQueryAsync<long>(
            """
            SELECT DISTINCT lm.tenant_id
            FROM LocationMaster lm
            JOIN SysTenant t ON t.Id = lm.tenant_id AND t.Status = 1
            WHERE lm.Domain = @Domain
              AND IFNULL(lm.typed,'') <> 'Supp'
              AND TRIM(lm.location) <> ''
            ORDER BY lm.tenant_id
            """,
            new List<SugarParameter> { new("@Domain", domain) });

    private static async Task<TenantLocationScope> ScopeAsync(ISqlSugarClient db, long tenantId, string domain) =>
        TenantLocationScope.FromWhitelist(await TenantLocationScopeLoader.LoadAsync(db, tenantId, domain));

    private static async Task<HashSet<string>> SuppLocationsAsync(ISqlSugarClient db, long tenantId, string domain) =>
        (await db.Ado.SqlQueryAsync<string>(
            "SELECT DISTINCT Location FROM LocationMaster WHERE tenant_id=@T AND Domain=@D AND Typed='Supp' AND TRIM(Location)<>''",
            new List<SugarParameter> { new("@T", tenantId), new("@D", domain) }))
        .Select(x => x.Trim()).ToHashSet(StringComparer.OrdinalIgnoreCase);

    // ===== Case 1：对账覆盖面 = 标准层物化覆盖面 =====

    /// <summary>
    /// 对账必须逐「业务租户」做，而不是拿 <c>SourceDomainTenantResolver</c> 解析出的
    /// 「源归属租户」当唯一口径——后者只决定 stg 落地区，不代表业务归属。
    /// </summary>
    [Fact]
    public async Task ReconTenantScope_CoversEveryMaterializedTenant_NotOnlySourceOwner()
    {
        if (!Enabled) return;
        var db = BuildAidopdev();
        Assert.NotNull(db);

        var tenants = await ScopedTenantsAsync(db, Domain);
        _out.WriteLine($"domain={Domain} 参与对账的租户={string.Join(",", tenants)}");

        Assert.Contains(AidopTenant, tenants);
        Assert.Contains(UatTenant, tenants);          // 源归属租户之外的租户也必须被对账
        Assert.DoesNotContain(DefaultTenant, tenants); // 无任何合法库位 → 不得进入对账

        foreach (var t in tenants)
            Assert.False((await ScopeAsync(db, t, Domain)).IsEmpty, $"tenant={t} 白名单为空却被枚举");
    }

    // ===== Case 2：旧口径（全 Domain LIVE vs 单租户 STD）的假差异量化 =====

    /// <summary>
    /// 用 <c>mdp_stg_inventory</c>（贴源层，按 Domain 全量落地，未按租户收窄）作为 LIVE 的等价代理，
    /// 量化「LIVE 不收窄库位」会给每个租户造成多少条 <c>qty_std=0</c> 的假「STD 缺失」差异。
    /// 断言 > 0，即缺陷客观存在；修复后这些键在两侧都被同一白名单排除，恒不入差异表。
    /// </summary>
    [Theory]
    [InlineData(AidopTenant)]
    [InlineData(UatTenant)]
    public async Task OldScope_UnscopedLive_ProducesFalseMissingDiffs_NewScopeProducesNone(long tenantId)
    {
        if (!Enabled) return;
        var db = BuildAidopdev();
        Assert.NotNull(db);

        var scope = await ScopeAsync(db, tenantId, Domain);
        Assert.False(scope.IsEmpty);

        var liveProxyKeys = await LiveProxyKeysAsync(db);
        Assert.NotEmpty(liveProxyKeys);

        var outOfScope = liveProxyKeys.Where(k => !scope.Contains(k.Location)).ToList();
        var inScope = liveProxyKeys.Count - outOfScope.Count;

        _out.WriteLine(
            $"tenant={tenantId} allowedLoc={scope.Count} LIVE代理键={liveProxyKeys.Count} " +
            $"在范围内={inScope} 旧口径假差异={outOfScope.Count} " +
            $"越界库位={string.Join(",", outOfScope.Select(x => x.Location).Distinct().OrderBy(x => x).Take(20))}");

        // 旧口径：这些键 LIVE 有、STD 按租户白名单物化后永远没有 → 落成 qty_std=0 的假差异
        Assert.True(outOfScope.Count > 0,
            "未发现越界键：本用例的前提（LIVE 全 Domain 大于租户库位范围）不再成立，需重新评估对账口径");

        // 新口径：两侧同白名单收窄后仍有可比较的键，说明收窄没有把对账做空
        Assert.True(inScope > 0, "收窄后无任何可对账的键，白名单或 Domain 口径有误");

        // 越界库位一律不属于该租户：其在 LocationMaster 里要么是 Supp，要么根本不存在
        var owned = await db.Ado.SqlQueryAsync<string>(
            "SELECT DISTINCT Location FROM LocationMaster WHERE tenant_id=@T AND Domain=@D AND TRIM(Location)<>''",
            new List<SugarParameter> { new("@T", tenantId), new("@D", Domain) });
        var ownedSet = owned.Select(x => x.Trim()).ToHashSet(StringComparer.OrdinalIgnoreCase);
        var suppSet = await SuppLocationsAsync(db, tenantId, Domain);
        Assert.All(outOfScope.Select(x => x.Location).Distinct(StringComparer.OrdinalIgnoreCase),
            loc => Assert.True(!ownedSet.Contains(loc?.Trim() ?? "") || suppSet.Contains(loc?.Trim() ?? ""),
                $"越界库位 {loc} 既非 Supp 又属于本租户，说明白名单口径可疑"));
    }

    /// <summary>
    /// AIDOP 租户越界的那部分键，必须全部落在它自己 LocationMaster 的 <c>Typed='Supp'</c> 库位上——
    /// 即「供应商/寄存库存」，本就不属于 S5 自有库存，两侧同时排除才是正确口径。
    /// </summary>
    [Fact]
    public async Task AidopTenant_OutOfScopeKeys_AreSupplierOwnedLocations_NotOwnStock()
    {
        if (!Enabled) return;
        var db = BuildAidopdev();
        Assert.NotNull(db);

        var scope = await ScopeAsync(db, AidopTenant, Domain);
        var supp = await SuppLocationsAsync(db, AidopTenant, Domain);
        var liveProxyKeys = await LiveProxyKeysAsync(db);

        var outOfScope = liveProxyKeys.Where(k => !scope.Contains(k.Location)).ToList();
        var explainedBySupp = outOfScope.Count(k => supp.Contains(k.Location?.Trim() ?? ""));
        var notExplainedBySupp = outOfScope.Where(k => !supp.Contains(k.Location?.Trim() ?? ""))
            .Select(k => k.Location).Distinct(StringComparer.OrdinalIgnoreCase).OrderBy(x => x).ToList();

        _out.WriteLine(
            $"AIDOP 越界键={outOfScope.Count} 其中 Supp 库位可解释={explainedBySupp} " +
            $"无法解释的库位={string.Join(",", notExplainedBySupp)}");

        Assert.Empty(notExplainedBySupp);
    }

    // ===== Case 3：新口径下 STD 侧查询恒在白名单内（并挡住历史脏快照）=====

    [Theory]
    [InlineData(AidopTenant)]
    [InlineData(UatTenant)]
    public async Task NewScope_StdSideQuery_NeverReturnsOutOfScopeOrSuppLocation(long tenantId)
    {
        if (!Enabled) return;
        var db = BuildAidopdev();
        Assert.NotNull(db);

        var scope = await ScopeAsync(db, tenantId, Domain);
        var supp = await SuppLocationsAsync(db, tenantId, Domain);

        var (clause, locPars) = scope.BuildInClause("location", "loc");
        var pars = new List<SugarParameter>
        {
            new("@TenantId", tenantId), new("@Domain", Domain), new("@SourceSystem", SourceCode)
        };
        pars.AddRange(locPars);

        var scoped = await db.Ado.SqlQueryAsync<string>(
            $"""
             SELECT DISTINCT location FROM mdp_std_inventory
             WHERE tenant_id=@TenantId AND domain=@Domain AND source_system=@SourceSystem
               AND {clause}
             """, pars);

        var unscoped = await db.Ado.SqlQueryAsync<string>(
            """
            SELECT DISTINCT location 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)
            });

        _out.WriteLine($"tenant={tenantId} STD 库位（收窄后）={scoped.Count} STD 库位（不收窄）={unscoped.Count} " +
                       $"被挡住的历史脏库位={string.Join(",", unscoped.Except(scoped, StringComparer.OrdinalIgnoreCase).OrderBy(x => x).Take(20))}");

        Assert.All(scoped, loc => Assert.True(scope.Contains(loc), $"STD 侧越界库位：{loc}"));
        Assert.All(scoped, loc => Assert.False(supp.Contains(loc?.Trim() ?? ""), $"STD 侧出现 Supp 库位：{loc}"));
        Assert.True(scoped.Count <= unscoped.Count);
    }

    // ===== Case 4：历史差异表证据——旧口径写出的差异确为假差异 =====

    [Fact]
    public async Task HistoricalDiffRows_WereWrittenAgainstOutOfScopeLocations()
    {
        if (!Enabled) return;
        var db = BuildAidopdev();
        Assert.NotNull(db);

        var rows = await db.Ado.SqlQueryAsync<DiffStatRow>(
            """
            SELECT d.tenant_id AS TenantId, COUNT(*) AS Total,
                   SUM(CASE WHEN d.qty_std=0 AND d.qty_live<>0 THEN 1 ELSE 0 END) AS StdMissing,
                   SUM(CASE WHEN EXISTS(
                         SELECT 1 FROM LocationMaster l
                          WHERE l.tenant_id=d.tenant_id AND l.Domain=d.domain
                            AND IFNULL(l.Typed,'')<>'Supp' AND l.Location=d.location)
                       THEN 0 ELSE 1 END) AS OutOfScope
            FROM ado_inventory_recon_diff d
            GROUP BY d.tenant_id
            """);

        if (rows.Count == 0)
        {
            _out.WriteLine("ado_inventory_recon_diff 为空，跳过历史证据断言（本用例只读，不制造数据）");
            return;
        }

        foreach (var r in rows)
            _out.WriteLine($"tenant={r.TenantId} 差异行={r.Total} 其中 STD缺失型={r.StdMissing} 越界库位={r.OutOfScope}");

        // 历史差异若几乎全部落在「该租户根本没有的库位」上，即为口径错误产生的假差异
        var total = rows.Sum(x => x.Total);
        var outOfScope = rows.Sum(x => x.OutOfScope);
        _out.WriteLine($"合计 差异行={total} 越界占比={(total == 0m ? 0m : outOfScope * 100m / total):F1}%");
        Assert.True(outOfScope <= total);
    }

    // ===== Case 5：165 侧实测（需 AIDOP_IT_165=1）=====

    /// <summary>
    /// 直接对 165 跑「收窄 / 不收窄」两版 LIVE 查询，实测假差异规模。
    /// 只发 SELECT，且库位值全部参数化（复用 <see cref="TenantLocationScope.BuildInClause"/>）。
    /// </summary>
    [Theory]
    [InlineData(AidopTenant)]
    [InlineData(UatTenant)]
    public async Task Live165_ScopedQuery_ReturnsOnlyWhitelistedLocations(long tenantId)
    {
        if (!LiveEnabled)
        {
            _out.WriteLine("AIDOP_IT_165 != 1，跳过 165 实测用例");
            return;
        }
        var db = BuildAidopdev();
        Assert.NotNull(db);

        var scope = await ScopeAsync(db, tenantId, Domain);
        Assert.False(scope.IsEmpty);

        var remote = await new MdpSourceScopeFactory(db).GetScopeAsync(SourceCode);

        var (clause, locPars) = scope.BuildInClause("Location", "loc");
        var pars = new List<SugarParameter> { new("@Domain", Domain) };
        pars.AddRange(locPars);

        var scopedLocs = await remote.Ado.SqlQueryAsync<string>(
            $"""
             SELECT DISTINCT Location FROM LocationDetail
             WHERE Domain=@Domain AND {clause}
               AND (ISNULL(AvailStatusQty,0)+ISNULL(Assay,0)+ISNULL(FreezeQty,0)) <> 0
             """, pars);

        var allLocs = await remote.Ado.SqlQueryAsync<string>(
            """
            SELECT DISTINCT Location FROM LocationDetail
            WHERE Domain=@Domain
              AND (ISNULL(AvailStatusQty,0)+ISNULL(Assay,0)+ISNULL(FreezeQty,0)) <> 0
            """,
            new List<SugarParameter> { new("@Domain", Domain) });

        var falseDiffLocs = allLocs.Except(scopedLocs, StringComparer.OrdinalIgnoreCase).OrderBy(x => x).ToList();
        _out.WriteLine($"tenant={tenantId} 165 全 Domain 库位={allLocs.Count} 收窄后={scopedLocs.Count} " +
                       $"旧口径会产生假差异的库位={falseDiffLocs.Count}：{string.Join(",", falseDiffLocs.Take(30))}");

        Assert.All(scopedLocs, loc => Assert.True(scope.Contains(loc), $"LIVE 侧越界库位：{loc}"));
        Assert.True(scopedLocs.Count <= allLocs.Count);
    }

    // ===== 辅助：LIVE 等价代理（贴源层按 Domain 全量落地，未按租户收窄）=====

    private static async Task<List<LiveKeyRow>> LiveProxyKeysAsync(ISqlSugarClient db) =>
        await db.Ado.SqlQueryAsync<LiveKeyRow>(
            """
            SELECT DISTINCT
              JSON_UNQUOTE(JSON_EXTRACT(s.raw_data,'$.Location')) AS Location,
              JSON_UNQUOTE(JSON_EXTRACT(s.raw_data,'$.ItemNum'))  AS ItemNum,
              IFNULL(JSON_UNQUOTE(JSON_EXTRACT(s.raw_data,'$.LotSerial')),'') AS LotSerial
            FROM mdp_stg_inventory s
            WHERE s.source_table='LocationDetail'
              AND s.source_system=@SourceSystem
              AND JSON_UNQUOTE(JSON_EXTRACT(s.raw_data,'$.Domain'))=@Domain
              AND (IFNULL(CAST(NULLIF(NULLIF(JSON_UNQUOTE(JSON_EXTRACT(s.raw_data,'$.AvailStatusQty')),'null'),'') AS DECIMAL(18,5)),0)
                  +IFNULL(CAST(NULLIF(NULLIF(JSON_UNQUOTE(JSON_EXTRACT(s.raw_data,'$.Assay')),'null'),'') AS DECIMAL(18,5)),0)
                  +IFNULL(CAST(NULLIF(NULLIF(JSON_UNQUOTE(JSON_EXTRACT(s.raw_data,'$.FreezeQty')),'null'),'') AS DECIMAL(18,5)),0)) <> 0
            """,
            new List<SugarParameter> { new("@SourceSystem", SourceCode), new("@Domain", Domain) });

    private sealed class LiveKeyRow
    {
        public string Location { get; set; }
        public string ItemNum { get; set; }
        public string LotSerial { get; set; }
    }

    private sealed class DiffStatRow
    {
        public long TenantId { get; set; }
        // COUNT/SUM 在 MySQL 侧分别是 BIGINT / DECIMAL，统一用 decimal 承接避免映射转换异常
        public decimal Total { get; set; }
        public decimal StdMissing { get; set; }
        public decimal OutOfScope { get; set; }
    }
}