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>
    /// 数据模型债的实证守卫：一旦 MissedPrint 开始有非 NULL 的 tenant_id，
    /// 说明可以建立真正的租户边界，此用例会失败以提醒复核 008 的 Scope 决策。
    /// </summary>
    [Fact]
    public async Task F008_MissedPrintTenantColumn_StillUnusable_DataModelDebtGuard()
    {
        if (!Enabled) return;
        var db = BuildAidopdev();
        Assert.NotNull(db);

        var total = await db.Ado.GetIntAsync("SELECT COUNT(1) FROM MissedPrint");
        var withTenant = await db.Ado.GetIntAsync("SELECT COUNT(1) FROM MissedPrint WHERE tenant_id IS NOT NULL");
        var withLocation = await db.Ado.GetIntAsync("SELECT COUNT(1) FROM MissedPrint WHERE IFNULL(Location,'')<>''");

        _out.WriteLine($"MissedPrint rows={total} tenant_id 非空={withTenant} Location 非空={withLocation}");
        Assert.Equal(0, withTenant);   // 仍全为 NULL → 无法建立租户边界，008 的 DATA-MODEL RISK 依然成立
    }

    // ===================== 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
    }
}