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

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

/// <summary>
/// S5「库存数据」菜单 008 / 009 / 013 收口测试。
/// **门禁**：仅当环境变量 AIDOP_IT=1 时执行。全程只读，不写任何表。
/// </summary>
[Trait("Category", "Integration")]
public class InventoryDataPagesScopeTests
{
    private const long UatTenant = 838257186181189L;
    private const long AidopTenant = 797403760988229L;
    private const long SecondDomainTenant = 824585161322565L;
    private const string SourceCode = "DOPDEMORQ_SQLSERVER";

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

    private readonly Xunit.Abstractions.ITestOutputHelper _out;
    public InventoryDataPagesScopeTests(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-invpages-it", DbType = DbType.MySql, ConnectionString = cs, IsAutoCloseConnection = true,
        });
    }

    // ===================== FUNC-S5-008 标签查询 =====================

    /// <summary>
    /// 缺陷复现 + 修复验证：码表 JOIN 若不按 Domain+租户限定，会把行数放大。
    /// 这是本批 008 的核心断言。
    /// </summary>
    [Fact]
    public async Task F008_StatusDictionaryJoin_MustNotAmplifyRowCount()
    {
        if (!Enabled) return;
        var db = BuildAidopdev();
        Assert.NotNull(db);

        var baseRows = await db.Ado.GetIntAsync("SELECT COUNT(1) FROM MissedPrint m");

        // 旧写法：只按 (FldName, Val) 关联 —— 负向对照，证明缺陷真实存在
        var oldJoin = await db.Ado.GetIntAsync(
            """
            SELECT COUNT(1) FROM MissedPrint m
            LEFT JOIN GeneralizedCodeMaster g ON g.FldName='BarcodeStatus' AND g.Val=m.Status
            """);

        // 新写法：Domain + 租户双限定
        var newJoin = await db.Ado.GetIntAsync(
            """
            SELECT COUNT(1) FROM MissedPrint m
            LEFT JOIN GeneralizedCodeMaster g
                   ON g.FldName='BarcodeStatus' AND g.Val=m.Status
                  AND g.Domain=m.Domain AND g.tenant_id=@TenantId
            """,
            new List<SugarParameter> { new("@TenantId", UatTenant) });

        _out.WriteLine($"base={baseRows} oldJoin={oldJoin} newJoin={newJoin}");
        Assert.True(oldJoin > baseRows, "旧写法应当放大行数，否则本用例失去意义");
        Assert.Equal(baseRows, newJoin);   // 修复后必须与基表行数一致
    }

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

        // 任一标签行经码表 JOIN 后不得命中多于 1 条字典
        var worst = await db.Ado.GetIntAsync(
            """
            SELECT IFNULL(MAX(c),0) FROM (
              SELECT m.RecID, COUNT(g.RecID) c
              FROM MissedPrint m
              LEFT JOIN GeneralizedCodeMaster g
                     ON g.FldName='BarcodeStatus' AND g.Val=m.Status
                    AND g.Domain=m.Domain AND g.tenant_id=@TenantId
              GROUP BY m.RecID
            ) x
            """,
            new List<SugarParameter> { new("@TenantId", tenantId) });

        _out.WriteLine($"tenant={tenantId} 单行最多命中字典条数={worst}");
        Assert.True(worst <= 1, $"tenant={tenantId} 存在一行命中 {worst} 条字典，会造成分页放大");
    }

    /// <summary>状态文案不得来自其它租户：当前租户无字典时应为空（由服务层 COALESCE 回落原始码）。</summary>
    [Fact]
    public async Task F008_StatusText_NeverBorrowedFromAnotherTenant()
    {
        if (!Enabled) return;
        var db = BuildAidopdev();
        Assert.NotNull(db);

        var uatOwnDict = await db.Ado.GetIntAsync(
            "SELECT COUNT(1) FROM GeneralizedCodeMaster WHERE FldName='BarcodeStatus' AND tenant_id=@T",
            new List<SugarParameter> { new("@T", UatTenant) });

        var borrowed = await db.Ado.GetIntAsync(
            """
            SELECT COUNT(1) FROM MissedPrint m
            JOIN GeneralizedCodeMaster g
              ON g.FldName='BarcodeStatus' AND g.Val=m.Status
             AND g.Domain=m.Domain AND g.tenant_id=@TenantId
            WHERE g.tenant_id <> @TenantId
            """,
            new List<SugarParameter> { new("@TenantId", UatTenant) });

        _out.WriteLine($"UAT 自有 BarcodeStatus 字典条数={uatOwnDict}；借用他租户文案的行数={borrowed}");
        Assert.Equal(0, borrowed);
    }

    /// <summary>ORDER BY 的 tiebreaker 必须唯一：MissedPrint 主键为 RecID。</summary>
    [Fact]
    public async Task F008_RecIdIsUniqueTiebreaker()
    {
        if (!Enabled) return;
        var db = BuildAidopdev();
        Assert.NotNull(db);

        var total = await db.Ado.GetIntAsync("SELECT COUNT(1) FROM MissedPrint");
        var distinct = await db.Ado.GetIntAsync("SELECT COUNT(DISTINCT RecID) FROM MissedPrint");
        _out.WriteLine($"MissedPrint rows={total} distinct RecID={distinct}");
        Assert.Equal(total, distinct);
    }

    /// <summary>
    /// 原「数据模型债守卫」的反转版：1.0.442 回填后 MissedPrint.tenant_id 必须全部有值。
    /// 一旦又出现 NULL，说明有写路径漏写 tenant_id（008/013 的租户过滤会把这些行整体查空）。
    /// </summary>
    [Fact]
    public async Task F008_MissedPrintTenantColumn_MustBeFullyPopulated()
    {
        if (!Enabled) return;
        var db = BuildAidopdev();
        Assert.NotNull(db);

        var total = await db.Ado.GetIntAsync("SELECT COUNT(1) FROM MissedPrint");
        var nullTenant = await db.Ado.GetIntAsync("SELECT COUNT(1) FROM MissedPrint WHERE tenant_id IS NULL");

        _out.WriteLine($"MissedPrint rows={total} tenant_id 为空={nullTenant}");
        Assert.Equal(0, nullTenant);
    }

    /// <summary>
    /// 回填正确性：归属只能来自 scm_shd.tenant_id（ShipperNbr → shddh），
    /// 且必须与 CreateUser → SysUser.TenantId 的独立推导完全一致。
    /// </summary>
    [Fact]
    public async Task F008_MissedPrintTenant_MatchesShipperAndCreateUser()
    {
        if (!Enabled) return;
        var db = BuildAidopdev();
        Assert.NotNull(db);

        var vsShipper = await db.Ado.GetIntAsync(
            """
            SELECT COUNT(1) FROM MissedPrint m
            JOIN (SELECT shddh, MIN(tenant_id) tenant_id FROM scm_shd
                  GROUP BY shddh HAVING COUNT(DISTINCT tenant_id)=1) d ON d.shddh = m.ShipperNbr
            WHERE IFNULL(m.tenant_id,0) <> d.tenant_id
            """);

        var vsCreateUser = await db.Ado.GetIntAsync(
            """
            SELECT COUNT(1) FROM MissedPrint m
            JOIN (SELECT Account, MIN(TenantId) TenantId FROM SysUser
                  GROUP BY Account HAVING COUNT(DISTINCT TenantId)=1) u ON u.Account = m.CreateUser
            WHERE IFNULL(m.tenant_id,0) <> u.TenantId
            """);

        _out.WriteLine($"与 scm_shd 不一致={vsShipper}；与 CreateUser→SysUser 不一致={vsCreateUser}");
        Assert.Equal(0, vsShipper);
        Assert.Equal(0, vsCreateUser);
    }

    /// <summary>
    /// 双向不可见：任一租户按 tenant_id 过滤后，不得看到其它租户的标签；
    /// 且各租户可见行数之和必须等于全表行数（不重不漏）。
    /// </summary>
    [Fact]
    public async Task F008_F013_TenantScopedLabels_AreMutuallyInvisible()
    {
        if (!Enabled) return;
        var db = BuildAidopdev();
        Assert.NotNull(db);

        var total = await db.Ado.GetIntAsync("SELECT COUNT(1) FROM MissedPrint");
        var uat = await db.Ado.GetIntAsync(
            "SELECT COUNT(1) FROM MissedPrint WHERE tenant_id=@T", new List<SugarParameter> { new("@T", UatTenant) });
        var aidop = await db.Ado.GetIntAsync(
            "SELECT COUNT(1) FROM MissedPrint WHERE tenant_id=@T", new List<SugarParameter> { new("@T", AidopTenant) });
        var leaked = await db.Ado.GetIntAsync(
            "SELECT COUNT(1) FROM MissedPrint WHERE tenant_id=@T AND tenant_id<>@T2",
            new List<SugarParameter> { new("@T", UatTenant), new("@T2", UatTenant) });

        _out.WriteLine($"total={total} UAT可见={uat} 797可见={aidop} 越界={leaked}");
        Assert.Equal(0, leaked);
        Assert.True(uat > 0 && aidop > 0, "两个租户都应各有标签，否则本用例失去意义");
        Assert.Equal(total, uat + aidop);
    }

    /// <summary>
    /// 生成标签 INSERT ... SELECT 的 ItemMaster 关联必须限定租户：
    /// ItemNum 单独并不唯一（跨租户重名），不限定会把一行箱码放大成多行 MissedPrint，
    /// 并可能取到别的租户的保质期主数据。
    /// </summary>
    [Fact]
    public async Task LabelInsert_ItemMasterJoin_MustBeTenantScoped()
    {
        if (!Enabled) return;
        var db = BuildAidopdev();
        Assert.NotNull(db);

        // (tenant_id, ItemNum) 唯一 —— 这是加租户条件之后不再放大的前提
        var dupWithinTenant = await db.Ado.GetIntAsync(
            "SELECT COUNT(1) FROM (SELECT tenant_id,ItemNum FROM ItemMaster GROUP BY tenant_id,ItemNum HAVING COUNT(*)>1) x");
        // 单独 ItemNum 不唯一 —— 这是缺陷成立的前提（负向对照）
        var dupAcrossTenant = await db.Ado.GetIntAsync(
            "SELECT COUNT(1) FROM (SELECT ItemNum FROM ItemMaster GROUP BY ItemNum HAVING COUNT(DISTINCT tenant_id)>1) x");

        var amplified = await db.Ado.GetIntAsync(
            """
            SELECT IFNULL(SUM((SELECT COUNT(*) FROM ItemMaster i WHERE i.ItemNum=s.sh_material_code)),0)
            FROM scm_shbq s
            """);
        var scoped = await db.Ado.GetIntAsync(
            """
            SELECT IFNULL(SUM((SELECT COUNT(*) FROM ItemMaster i
                               WHERE i.ItemNum=s.sh_material_code AND i.tenant_id=s.tenant_id)),0)
            FROM scm_shbq s
            """);
        var shbq = await db.Ado.GetIntAsync("SELECT COUNT(1) FROM scm_shbq");

        _out.WriteLine($"shbq={shbq} 不限租户JOIN={amplified} 限租户JOIN={scoped} " +
                       $"(tenant,ItemNum)重复={dupWithinTenant} ItemNum跨租户重名={dupAcrossTenant}");
        Assert.Equal(0, dupWithinTenant);
        Assert.True(dupAcrossTenant > 0, "ItemNum 若已全局唯一，本缺陷不成立，用例失去意义");
        Assert.True(amplified > shbq, "旧写法应当放大，否则本用例失去意义");
        Assert.Equal(shbq, scoped);
    }

    // ===================== FUNC-S5-009 / 013 Domain 解析 =====================

    [Theory]
    [InlineData(UatTenant, "8010")]
    [InlineData(AidopTenant, "8010")]
    [InlineData(SecondDomainTenant, "2410")]
    public async Task F009_F013_DomainResolvedPerTenant(long tenantId, string expected)
    {
        if (!Enabled) return;
        var db = BuildAidopdev();
        Assert.NotNull(db);

        var domain = await new SourceDomainTenantResolver(db).ResolveDomainAsync(SourceCode, tenantId);
        _out.WriteLine($"tenant={tenantId} → domain={domain}");
        Assert.Equal(expected, domain);
    }

    /// <summary>
    /// 负向对照：旧的「全局取第一条 CompanyCode」写法对所有租户返回同一个 Domain，
    /// 与按租户解析的结果不一致 —— 证明 009/013 换用统一 resolver 是必要的。
    /// </summary>
    [Fact]
    public async Task F009_F013_LegacyGlobalFirstDomain_DiffersFromPerTenantResolution()
    {
        if (!Enabled) return;
        var db = BuildAidopdev();
        Assert.NotNull(db);

        var legacy = (await db.Ado.SqlQueryAsync<string>(
            "SELECT Domain FROM GeneralizedCodeMaster WHERE FldName='SystemConfig' AND Val='CompanyCode' ORDER BY RecID LIMIT 1"))
            .FirstOrDefault();

        var resolver = new SourceDomainTenantResolver(db);
        var second = await resolver.ResolveDomainAsync(SourceCode, SecondDomainTenant);

        _out.WriteLine($"旧写法(全局第一条)={legacy}；按租户解析 {SecondDomainTenant} → {second}");
        Assert.NotEqual(legacy, second);   // 旧写法会把 824 错解析成 8010
    }

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

        var orphan = (await db.Ado.SqlQueryAsync<long>(
            """
            SELECT t.Id FROM SysTenant t
            WHERE t.Status = 1
              AND NOT EXISTS (SELECT 1 FROM GeneralizedCodeMaster g
                              WHERE g.tenant_id=t.Id AND g.FldName='SystemConfig' AND g.Val='CompanyCode'
                                AND IFNULL(g.Domain,'')<>'')
              AND NOT EXISTS (SELECT 1 FROM LocationMaster l
                              WHERE l.tenant_id=t.Id AND IFNULL(l.Domain,'')<>'')
            ORDER BY t.Id LIMIT 1
            """)).FirstOrDefault();
        Assert.True(orphan > 0, "需要一个无 Domain 映射的启用租户来验证 fail closed");

        var ex = await Assert.ThrowsAsync<InvalidOperationException>(
            () => new SourceDomainTenantResolver(db).ResolveDomainAsync(SourceCode, orphan));
        Assert.DoesNotContain("8010", ex.Message);   // 不得悄悄回落固定 Domain
    }
}