using System.Text.RegularExpressions;
using SqlSugar;
using Xunit;

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

/// <summary>
/// FUNC-S5-011 进出存流水 · 逐租户物化守卫。
/// <para>
/// 源码断言无门禁；数据面断言需 AIDOP_IT=1 且**全程只读**（不写任何表、不触发同步）。
/// </para>
/// <para>
/// 背景：<c>ado_source_domain_tenant_map</c> 把 DOPDEMORQ_SQLSERVER/8010 登记在 797 名下，
/// 修复前 <c>UpsertInvTransStdAsync</c> 只按这个「源归属租户」写一次标准层，
/// 于是 UAT(838257186181189) 名下恒为 0 —— 而正式贴源层里落在 UAT 18 个合法库位上的流水
/// 实测有 188,419 行。余额腿早已是「逐租户 + 库位投影」，流水腿此前漏了这一步。
/// </para>
/// </summary>
public class InvTransTenantMaterializationTests
{
    private const long TenantUat = 838257186181189L;
    private const long TenantAidop = 797403760988229L;   // 同时也是 DOPDEMORQ_SQLSERVER/8010 的源归属租户
    private const string FormalSource = "DOPDEMORQ_SQLSERVER";
    private const string FixtureSource = "UAT_GENERATOR";

    private static bool ItEnabled => Environment.GetEnvironmentVariable("AIDOP_IT") == "1";

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

    private static readonly string PluginRoot =
        Path.GetFullPath(Path.Combine(AppContext.BaseDirectory, "../../../../Admin.NET.Plugin.AiDOP"));

    private const string SyncSrc = "MaterialWarehouse/InventoryMdpSyncService.cs";
    private const string ReaderSrc = "MaterialWarehouse/StdInventoryTransactionReader.cs";

    private static string ReadSource(string relativePath)
    {
        var full = Path.Combine(PluginRoot, relativePath);
        Assert.True(File.Exists(full), $"源码文件不存在，路径需同步更新：{full}");
        return File.ReadAllText(full);
    }

    private static List<string> CodeLines(string relativePath) =>
        ReadSource(relativePath)
            .Split('\n')
            .Select(l => l.Trim())
            .Where(l => !l.StartsWith("///", StringComparison.Ordinal)
                        && !l.StartsWith("//", StringComparison.Ordinal)
                        && !l.StartsWith("--", StringComparison.Ordinal))
            .ToList();

    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-invtrans-it", DbType = DbType.MySql, ConnectionString = cs, IsAutoCloseConnection = true,
        });
    }

    // ===================== 1/2：逐租户物化 + source≠target =====================

    /// <summary>两条腿都必须逐目标租户循环，流水腿不得再只调一次。</summary>
    [Fact]
    public void InvTrans_MustLoopTargetTenants()
    {
        var src = ReadSource(SyncSrc);
        // 旧方法名必须彻底消失（它的签名把 source 当 business tenant）
        Assert.DoesNotContain("UpsertInvTransStdAsync", src, StringComparison.Ordinal);

        // 1 处定义 + 2 处调用（RunAsync 的 bootstrap/reconcile 与增量分支、transform-std）
        var calls = Regex.Matches(src, @"MaterializeInvTransStdAsync\(").Count;
        Assert.True(calls >= 3, $"应有 1 处定义 + 至少 2 处调用，实际 {calls}");

        // 3 处逐租户循环：余额腿 1 + 流水腿 2（RunAsync、transform-std）
        var loops = Regex.Matches(src, @"foreach \(var targetTenantId in targetTenants\)").Count;
        Assert.True(loops >= 3, $"逐租户循环应至少 3 处（余额 1 + 流水 2），实际 {loops}");

        // 每一处 MaterializeInvTransStdAsync 的调用点之前，最近的循环必须是 targetTenants 循环
        foreach (Match m in Regex.Matches(src, @"await MaterializeInvTransStdAsync\("))
        {
            var before = src[..m.Index];
            var lastLoop = before.LastIndexOf("foreach (var targetTenantId in targetTenants)", StringComparison.Ordinal);
            Assert.True(lastLoop >= 0, "存在不在逐租户循环内的流水物化调用");
        }
    }

    /// <summary>方法签名必须显式区分源归属租户与业务租户，不能再合并成一个 tenantId。</summary>
    [Fact]
    public void InvTrans_MustDistinguishSourceAndTargetTenant()
    {
        var src = ReadSource(SyncSrc);
        Assert.Contains("long sourceTenantId, long targetTenantId", src, StringComparison.Ordinal);
        Assert.Contains("new(\"@SourceTenantId\", sourceTenantId)", src, StringComparison.Ordinal);
        Assert.Contains("new(\"@TargetTenantId\", targetTenantId)", src, StringComparison.Ordinal);
        // std.tenant_id 必须写目标租户，不得再从 stg 反推
        Assert.Contains("var sTenant = \"@TargetTenantId\";", src, StringComparison.Ordinal);
    }

    /// <summary>目标租户集合必须复用余额腿同一个枚举，不得另写一套。</summary>
    [Fact]
    public void InvTrans_MustReuseScopedTenantEnumeration()
    {
        var lines = CodeLines(SyncSrc);
        var enumerators = lines.Count(l => l.Contains("ListInventoryScopedTenantsAsync(domain", StringComparison.Ordinal));
        Assert.True(enumerators >= 2, $"RunAsync 与 transform-std 都应复用该枚举，实际 {enumerators} 处");
        Assert.Equal(1, Regex.Matches(ReadSource(SyncSrc), @"private async Task<List<long>> ListInventoryScopedTenantsAsync").Count);
    }

    // ===================== 3/4：库位投影 + Supp 排除 =====================

    /// <summary>
    /// 库位投影必须只有一个实现口径（余额腿与流水腿共用），只有贴源 JSON 字段名不同。
    /// </summary>
    [Fact]
    public void LocationScopeJoin_HasSingleImplementation()
    {
        var src = ReadSource(SyncSrc);
        Assert.Equal(1, Regex.Matches(src, @"private static string TenantScopeJoinSql\(").Count);
        Assert.Contains("TenantScopeJoinSql(\"Location\")", src, StringComparison.Ordinal);
        Assert.Contains("TenantScopeJoinSql(\"Loc\")", src, StringComparison.Ordinal);
        // 全文只允许这一处 LocationMaster 投影 JOIN
        Assert.Equal(1, Regex.Matches(src, @"INNER JOIN LocationMaster lm").Count);
    }

    [Fact]
    public void LocationScopeJoin_ExcludesSuppAndEmptyLocation()
    {
        var src = ReadSource(SyncSrc);
        var stmt = src[src.IndexOf("INNER JOIN LocationMaster lm", StringComparison.Ordinal)..];
        stmt = stmt[..stmt.IndexOf("\"\"\"", StringComparison.Ordinal)];
        Assert.Contains("lm.tenant_id = @TargetTenantId", stmt, StringComparison.Ordinal);
        Assert.Contains("IFNULL(lm.typed, '') <> 'Supp'", stmt, StringComparison.Ordinal);
        Assert.Contains("TRIM(lm.location) <> ''", stmt, StringComparison.Ordinal);
    }

    /// <summary>流水物化必须用上该投影，且来源侧仍限定源归属租户与正式贴源表。</summary>
    [Fact]
    public void InvTrans_Materialization_AppliesScopeProjection()
    {
        var src = ReadSource(SyncSrc);
        var i = src.IndexOf("FROM mdp_stg_inv_trans s", StringComparison.Ordinal);
        Assert.True(i >= 0);
        var stmt = src[i..(i + 400)];
        Assert.Contains("{TransTenantScopeJoin}", stmt, StringComparison.Ordinal);
        Assert.Contains("s.tenant_id=@SourceTenantId", stmt, StringComparison.Ordinal);
        Assert.Contains("s.source_table='InvTransHist'", stmt, StringComparison.Ordinal);
    }

    // ===================== 5：空 scope fail closed =====================

    [Fact]
    public void EmptyTenantScope_FailsClosed_NoFallbackToSourceOwner()
    {
        var src = ReadSource(SyncSrc);
        Assert.Contains("no scoped tenant", src, StringComparison.Ordinal);
        // 绝不能存在「没有目标租户就按源归属租户写一份」的回落分支
        Assert.DoesNotContain(CodeLines(SyncSrc),
            l => l.Contains("MaterializeInvTransStdAsync(tenantId, tenantId", StringComparison.Ordinal));
    }

    // ===================== 6/7：FullReplace 只删正式切片，保留 fixture =====================

    /// <summary>
    /// FULL Replace 的删除范围必须带 source_system + domain：
    /// 只按 tenant_id 删会连带删掉 UAT_GENERATOR fixture。
    /// </summary>
    [Fact]
    public void FullReplace_OnlyDeletesFormalSlice()
    {
        var src = ReadSource(SyncSrc);
        Assert.Contains("FormalSliceWhere(sourceCode, domain)", src, StringComparison.Ordinal);
        Assert.Contains("source_system='{sourceSystem}' AND domain='{domain}'", src, StringComparison.Ordinal);

        // 两处 mdp_std_inv_trans 的 FullReplace 都必须传 extraWhere，不得传 null
        foreach (Match m in Regex.Matches(src, @"ReplaceAsync\(\s*_db,\s*""mdp_std_inv_trans"",\s*targetTenantId,\s*([^,]+),"))
            Assert.Contains("FormalSliceWhere", m.Groups[1].Value, StringComparison.Ordinal);
    }

    /// <summary>白名单校验：source_system / domain 虽来自配置，仍不得被拼接注入。</summary>
    [Fact]
    public void FormalSliceWhere_ValidatesIdentifiers()
    {
        var src = ReadSource(SyncSrc);
        Assert.Contains("非法 source_system", src, StringComparison.Ordinal);
        Assert.Contains("非法 domain", src, StringComparison.Ordinal);
    }

    // ===================== 9：ItemMaster tenant-aware =====================

    [Fact]
    public void Reader_ItemMasterJoin_MustBeTenantAware()
    {
        var src = ReadSource(ReaderSrc);
        Assert.Contains("ON i.tenant_id = a.tenant_id AND i.ItemNum = a.item_num", src, StringComparison.Ordinal);
        Assert.DoesNotContain(CodeLines(ReaderSrc),
            l => l.Contains("ON a.domain = i.Domain AND a.item_num = i.ItemNum", StringComparison.Ordinal));
    }

    /// <summary>ORDER BY 必须有唯一 tiebreaker，否则 OFFSET 分页会跨页重复/漏行。</summary>
    [Fact]
    public void Reader_OrderBy_HasUniqueTiebreaker()
    {
        var src = ReadSource(ReaderSrc);
        Assert.Contains("ORDER BY a.trans_time DESC, CAST(a.src_rec_id AS UNSIGNED) DESC",
            src, StringComparison.Ordinal);
    }

    // ===================== 数据面（AIDOP_IT=1，只读） =====================

    /// <summary>8：同一源流水必须能分别物化给两个业务租户 —— 唯一键须以 tenant_id 打头。</summary>
    [Fact]
    public async Task Schema_UniqueKey_AllowsPerTenantMaterialization()
    {
        if (!ItEnabled) return;
        var db = BuildAidopdev();
        Assert.NotNull(db);

        var cols = await db!.Ado.SqlQueryAsync<string>(
            """
            SELECT COLUMN_NAME FROM information_schema.STATISTICS
            WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_inv_trans'
              AND INDEX_NAME='uk_std_inv_trans_src' ORDER BY SEQ_IN_INDEX
            """);
        _out.WriteLine($"uk_std_inv_trans_src = ({string.Join(", ", cols)})");
        Assert.Equal(new[] { "tenant_id", "source_system", "src_rec_id" }, cols);
    }

    /// <summary>10：COUNT 基集与 JOIN 后集合必须同基数（ItemMaster 不放大）。</summary>
    [Fact]
    public async Task Reader_CountAndJoin_SameCardinality()
    {
        if (!ItEnabled) return;
        var db = BuildAidopdev();
        Assert.NotNull(db);

        var pars = new List<SugarParameter> { new("@T", TenantAidop), new("@S", FormalSource) };
        var baseRows = await db!.Ado.GetIntAsync(
            "SELECT COUNT(1) FROM mdp_std_inv_trans a WHERE a.tenant_id=@T AND a.source_system=@S", pars);
        if (baseRows == 0) return;   // 该租户尚无正式数据时本用例无意义

        var oldJoin = await db.Ado.GetIntAsync(
            """
            SELECT COUNT(1) FROM mdp_std_inv_trans a
            LEFT JOIN ItemMaster i ON a.domain=i.Domain AND a.item_num=i.ItemNum
            WHERE a.tenant_id=@T AND a.source_system=@S
            """, pars);
        var newJoin = await db.Ado.GetIntAsync(
            """
            SELECT COUNT(1) FROM mdp_std_inv_trans a
            LEFT JOIN ItemMaster i ON i.tenant_id=a.tenant_id AND i.ItemNum=a.item_num AND i.Domain=a.domain
            WHERE a.tenant_id=@T AND a.source_system=@S
            """, pars);

        _out.WriteLine($"base={baseRows} oldJoin={oldJoin} newJoin={newJoin}");
        Assert.True(oldJoin > baseRows, "旧写法应当放大，否则本用例失去意义");
        Assert.Equal(baseRows, newJoin);
    }

    /// <summary>ItemMaster 租户内必须唯一，否则加了 tenant_id 也不能断言 1:1。</summary>
    [Fact]
    public async Task ItemMaster_UniqueWithinTenant()
    {
        if (!ItEnabled) return;
        var db = BuildAidopdev();
        Assert.NotNull(db);

        var dup = await db!.Ado.GetIntAsync(
            "SELECT COUNT(1) FROM (SELECT tenant_id,ItemNum FROM ItemMaster GROUP BY tenant_id,ItemNum HAVING COUNT(*)>1) x");
        _out.WriteLine($"(tenant_id, ItemNum) 重复组={dup}");
        Assert.Equal(0, dup);
    }

    /// <summary>11：UAT 正式切片必须全部落在其合法库位内、无 Supp、domain 一致。</summary>
    [Fact]
    public async Task Uat_FormalSlice_WithinLocationScope()
    {
        if (!ItEnabled) return;
        var db = BuildAidopdev();
        Assert.NotNull(db);

        var pars = new List<SugarParameter> { new("@T", TenantUat), new("@S", FormalSource) };
        var formal = await db!.Ado.GetIntAsync(
            "SELECT COUNT(1) FROM mdp_std_inv_trans WHERE tenant_id=@T AND source_system=@S", pars);
        _out.WriteLine($"UAT 正式行数={formal}");
        if (formal == 0) return;   // 尚未物化时跳过（物化后本用例即生效）

        var outside = await db.Ado.GetIntAsync(
            """
            SELECT COUNT(1) FROM mdp_std_inv_trans a
            LEFT JOIN LocationMaster lm
              ON lm.tenant_id=a.tenant_id AND lm.Domain=a.domain AND lm.location=a.location
             AND IFNULL(lm.typed,'')<>'Supp'
            WHERE a.tenant_id=@T AND a.source_system=@S AND lm.location IS NULL
            """, pars);
        var badDomain = await db.Ado.GetIntAsync(
            "SELECT COUNT(1) FROM mdp_std_inv_trans WHERE tenant_id=@T AND source_system=@S AND domain<>'8010'", pars);

        _out.WriteLine($"越界库位行={outside} 异常domain行={badDomain}");
        Assert.Equal(0, outside);
        Assert.Equal(0, badDomain);
    }

    /// <summary>12：fixture 切片必须原样保留，且与正式切片互不混淆。</summary>
    [Fact]
    public async Task Fixture_Slice_Preserved_AndIsolated()
    {
        if (!ItEnabled) return;
        var db = BuildAidopdev();
        Assert.NotNull(db);

        var fixture = await db!.Ado.GetIntAsync(
            "SELECT COUNT(1) FROM mdp_std_inv_trans WHERE tenant_id=@T AND source_system=@S",
            new List<SugarParameter> { new("@T", TenantUat), new("@S", FixtureSource) });
        // fixture 的库位全部在 UAT 合法范围之外 —— 这是它「不是正式数据」的实证
        var fixtureOutside = await db.Ado.GetIntAsync(
            """
            SELECT COUNT(1) FROM mdp_std_inv_trans a
            WHERE a.tenant_id=@T AND a.source_system=@S
              AND a.location NOT IN (SELECT location FROM LocationMaster
                                     WHERE tenant_id=@T AND Domain='8010' AND IFNULL(typed,'')<>'Supp')
            """,
            new List<SugarParameter> { new("@T", TenantUat), new("@S", FixtureSource) });

        _out.WriteLine($"UAT_GENERATOR 行数={fixture}，其中越界库位={fixtureOutside}");
        Assert.Equal(168, fixture);            // 本批不做 fixture 治理，必须原样保留
        Assert.Equal(fixture, fixtureOutside); // 全部越界 → 确为 fixture，不可混入正式口径
    }
}