using Admin.NET.Plugin.AiDOP.DataPlatform.Executors;
using SqlSugar;
using Xunit;

namespace Admin.NET.Plugin.AiDOP.Tests.DataPlatform;

/// <summary>
/// 入站抽数（MdpDbPullExecutor）的租户/工厂作用域契约。
///
/// 缺陷背景：旧谓词 <c>COALESCE(NULLIF(factory_id,0),1) = @scopeFactoryId</c> 把
/// factory_id==tenant_id 当成了一个真实工厂号。而 S1MdpScopeCatalog 给每个租户发的基线作用域是
/// factoryId=1，于是 b_examine_result(660/660 行) / b_bom_child_examine(17600/17600 行)
/// 这类"工厂号写成租户号"的源表永远匹配不上，长期 rows_read=0 却记 status=SUCCESS。
///
/// 修复口径：与写入侧 MdpStagingWriter.NormalizeFactoryId 同源——
/// NULL / &lt;=0 / ==tenant_id 一律归一为"无真实工厂"→默认工厂 1；真实工厂号保持逐值隔离。
/// </summary>
public class MdpDbPullScopeContractTests
{
    // 取自共享库 aidopdev 的真实作用域值
    private const long TenantA = 797403760988229; // b_examine_result 508 行 / b_bom_child_examine 17228 行
    private const long TenantB = 838257186181189; // b_examine_result 152 行 / b_bom_child_examine 372 行
    private const long TenantC = 824585161322565; // ic_item / srm_purchase 走真实工厂 2410
    private const long RealFactoryA = 8010;       // ic_item + srm_purchase 的真实工厂
    private const long RealFactoryB = 2410;
    private const long DefaultFactory = 1;

    // ---------- 归一语义：与写入侧严格同源 ----------

    [Fact]
    public void Resolve_IsExactlyWriterNormalizationWithDefaultFallback()
    {
        // 与 MdpStagingWriter 的 `factoryValue is > 0 ? factoryValue.Value : 1` 完全一致，
        // 包含 null / 0 / 负数 / 等于租户号 / 真实工厂 五种输入。
        long?[] samples = [null, 0, -1, -8010, TenantA, DefaultFactory, RealFactoryA, RealFactoryB];
        foreach (var sample in samples)
        {
            var expected = MdpStagingWriter.NormalizeFactoryId(TenantA, sample) ?? 1;
            Assert.Equal(expected, MdpFactoryScope.Resolve(TenantA, sample));
        }
    }

    [Theory]
    // TENANT_AS_FACTORY：factory_id 写成 tenant_id —— 必须归一到默认工厂 1
    [InlineData(TenantA, TenantA, DefaultFactory)]
    [InlineData(TenantB, TenantB, DefaultFactory)]
    // 无工厂的各种写法
    [InlineData(TenantA, null, DefaultFactory)]
    [InlineData(TenantA, 0L, DefaultFactory)]
    // FACTORY_SCOPED：真实工厂号原样保留
    [InlineData(TenantA, RealFactoryA, RealFactoryA)]
    [InlineData(TenantC, RealFactoryB, RealFactoryB)]
    public void Resolve_NormalizesTenantAsFactoryButKeepsRealFactories(long tenantId, long? rowFactoryId, long expected)
    {
        Assert.Equal(expected, MdpFactoryScope.Resolve(tenantId, rowFactoryId));
    }

    // ---------- TENANT_AS_FACTORY 源（b_examine_result / b_bom_child_examine，entity 47/48）----------

    [Fact]
    public void TenantAsFactorySource_IsPulledUnderTheDefaultFactoryScope()
    {
        // 修复前：COALESCE(NULLIF(tenantId,0),1) = 797403760988229 ≠ 1 → 0 行
        Assert.NotEqual(DefaultFactory, LegacyResolve(TenantA));
        // 修复后：命中默认工厂作用域 → 660/17600 行全部可拉
        Assert.True(MdpFactoryScope.Matches(TenantA, DefaultFactory, TenantA));
        Assert.True(MdpFactoryScope.Matches(TenantB, DefaultFactory, TenantB));
    }

    [Fact]
    public void TenantAsFactorySource_DoesNotLeakAcrossTenants()
    {
        // 租户 A 的行（factory_id=TenantA）在租户 B 的作用域里不是"无工厂"，
        // 会被解析成一个真实工厂号 TenantA，从而不落进 B 的默认工厂作用域。
        Assert.Equal(TenantA, MdpFactoryScope.Resolve(TenantB, TenantA));
        Assert.False(MdpFactoryScope.Matches(TenantB, DefaultFactory, TenantA));
        Assert.False(MdpFactoryScope.Matches(TenantA, DefaultFactory, TenantB));

        // 而租户维度本身另有一道硬隔离：谓词必然带 tenant 等值。
        var predicates = new List<string>();
        MdpDbPullExecutor.AppendScopePredicates(predicates, isSqlServer: false, "tenant_id", "factory_id");
        Assert.Contains("`tenant_id` = @scopeTenantId", predicates);
    }

    // ---------- FACTORY_SCOPED 源（ic_item / srm_purchase）----------

    [Fact]
    public void RealFactoryScopedSource_StillIsolatesFactoryAFromFactoryB()
    {
        Assert.True(MdpFactoryScope.Matches(TenantA, RealFactoryA, RealFactoryA));
        Assert.False(MdpFactoryScope.Matches(TenantA, RealFactoryB, RealFactoryA));
        Assert.True(MdpFactoryScope.Matches(TenantC, RealFactoryB, RealFactoryB));
        Assert.False(MdpFactoryScope.Matches(TenantC, RealFactoryA, RealFactoryB));
        // 真实工厂行不会被默认工厂作用域误捞
        Assert.False(MdpFactoryScope.Matches(TenantA, DefaultFactory, RealFactoryA));
    }

    [Fact]
    public void RealFactoryScopedSource_BehaviourIsUnchangedByTheFix()
    {
        // 真实工厂（!=0、!=tenant）在新旧口径下解析结果必须完全一致 —— 修复不得改动 FACTORY_SCOPED 源。
        foreach (var tenantId in new[] { TenantA, TenantB, TenantC })
            foreach (var factoryId in new long?[] { RealFactoryA, RealFactoryB, DefaultFactory, null, 0 })
                Assert.Equal(LegacyResolve(factoryId), MdpFactoryScope.Resolve(tenantId, factoryId));
    }

    [Fact]
    public void SharedRealFactoryAcrossTenants_IsSeparatedByTheTenantPredicate()
    {
        // ic_item 里租户 797…229 与 838…189 共用工厂 8010，工厂谓词本身分不开，靠租户谓词隔离。
        Assert.True(MdpFactoryScope.Matches(TenantA, RealFactoryA, RealFactoryA));
        Assert.True(MdpFactoryScope.Matches(TenantB, RealFactoryA, RealFactoryA));

        var predicates = new List<string>();
        MdpDbPullExecutor.AppendScopePredicates(predicates, isSqlServer: false, "tenant_id", "factory_id");
        Assert.Equal(2, predicates.Count);
        Assert.Contains(predicates, p => p.Contains("@scopeTenantId") && p.Contains("`tenant_id`"));
    }

    // ---------- TENANT_ONLY 源（无 factory_id 列，占本库 68 张源表中的 58 张）----------

    [Fact]
    public void TenantOnlySource_EmitsTenantPredicateOnly()
    {
        var predicates = new List<string>();
        MdpDbPullExecutor.AppendScopePredicates(predicates, isSqlServer: false, "tenant_id", null);

        Assert.Single(predicates);
        Assert.DoesNotContain("@scopeFactoryId", predicates[0]);
        Assert.True(MdpDbPullExecutor.NeedsTenantParameter("tenant_id", null));
    }

    [Fact]
    public void NoScopeColumns_EmitsNoPredicateAndBindsNoParameter()
    {
        var predicates = new List<string>();
        MdpDbPullExecutor.AppendScopePredicates(predicates, isSqlServer: false, null, null);

        Assert.Empty(predicates);
        Assert.False(MdpDbPullExecutor.NeedsTenantParameter(null, null));
    }

    // ---------- 谓词形状与参数绑定 ----------

    [Fact]
    public void FactoryPredicate_NormalizesZeroNullAndTenantAsFactory()
    {
        var predicates = new List<string>();
        MdpDbPullExecutor.AppendScopePredicates(predicates, isSqlServer: false, "tenant_id", "factory_id");
        var factoryPredicate = Assert.Single(predicates, p => p.Contains("@scopeFactoryId"));

        // 归一三要素：>0 才算工厂（覆盖 NULL/0/负数）、等于租户号置空、最后回落 1
        Assert.Contains("CASE WHEN `factory_id` > 0 THEN `factory_id` END", factoryPredicate);
        Assert.Contains("NULLIF(", factoryPredicate);
        Assert.Contains("@scopeTenantId", factoryPredicate);
        Assert.Contains(", 1)", factoryPredicate);
        // 旧的错误写法必须消失
        Assert.DoesNotContain("COALESCE(NULLIF(`factory_id`, 0), 1)", factoryPredicate);
    }

    [Fact]
    public void FactoryPredicate_QuotesIdentifierPerDialect()
    {
        var mysql = new List<string>();
        MdpDbPullExecutor.AppendScopePredicates(mysql, isSqlServer: false, "tenant_id", "factory_id");
        Assert.All(mysql, p => Assert.Contains("`", p));

        var sqlServer = new List<string>();
        MdpDbPullExecutor.AppendScopePredicates(sqlServer, isSqlServer: true, "tenant_id", "factory_id");
        Assert.All(sqlServer, p => Assert.DoesNotContain("`", p));
        Assert.Contains(sqlServer, p => p.Contains("CASE WHEN factory_id > 0 THEN factory_id END"));
    }

    [Fact]
    public void TenantParameter_IsBoundEvenWhenOnlyTheFactoryColumnExists()
    {
        // 回归：工厂谓词引用 @scopeTenantId；源表只有 factory_id 而无 tenant_id 时，
        // 旧的 `if (tenantCol != null)` 绑定条件会漏绑参数，导致 SQL 执行失败。
        Assert.True(MdpDbPullExecutor.NeedsTenantParameter(null, "factory_id"));

        var predicates = new List<string>();
        MdpDbPullExecutor.AppendScopePredicates(predicates, isSqlServer: false, null, "factory_id");
        Assert.Single(predicates);
        Assert.Contains("@scopeTenantId", predicates[0]);
    }

    private static long LegacyResolve(long? factoryId)
        => factoryId is null or 0 ? 1 : factoryId.Value;
}

/// <summary>零行假成功守卫：读到 0 行时必须能区分"源侧本就没有"与"全被作用域过滤掉"。</summary>
public class MdpDbPullZeroRowGuardTests
{
    private static MdpPullContext Ctx(int offset = 0) => new()
    {
        TenantId = 797403760988229,
        FactoryId = 1,
        FullRefresh = true,
        Offset = offset
    };

    [Fact]
    public void LegitimatelyEmptySource_IsPlainSuccess()
    {
        // dwd_material_shortage：共享库实测 0 行。探针同样取不到行 → 不告警。
        Assert.False(MdpDbPullExecutor.IsScopeMismatch(
            rowsRead: 0, Ctx(), "tenant_id", "factory_id", sourceHasRowsOutsideScope: false));
    }

    [Fact]
    public void AllFilteredByScope_IsFlaggedAsPartial()
    {
        // b_examine_result：源侧 660 行，作用域过滤后 0 行 → 必须告警，不得记无保留 SUCCESS。
        Assert.True(MdpDbPullExecutor.IsScopeMismatch(
            rowsRead: 0, Ctx(), "tenant_id", "factory_id", sourceHasRowsOutsideScope: true));
    }

    [Fact]
    public void NonEmptyRead_IsNeverFlagged()
    {
        Assert.False(MdpDbPullExecutor.IsScopeMismatch(
            rowsRead: 508, Ctx(), "tenant_id", "factory_id", sourceHasRowsOutsideScope: true));
    }

    [Fact]
    public void SourceWithoutScopeColumns_IsNeverFlagged()
    {
        // 没有发射作用域谓词就没有"被作用域过滤"这回事，0 行只能是源侧真没有。
        Assert.False(MdpDbPullExecutor.IsScopeMismatch(
            rowsRead: 0, Ctx(), null, null, sourceHasRowsOutsideScope: true));
        Assert.False(MdpDbPullExecutor.ShouldProbeScope(0, Ctx(), null, null));
    }

    [Fact]
    public void ExhaustedOffsetPage_IsNeverFlagged()
    {
        // 前一页已拉满作用域内数据、翻页到底的 0 行是正常的，探针（不带 OFFSET）会误报，故直接不探。
        Assert.False(MdpDbPullExecutor.ShouldProbeScope(0, Ctx(offset: 5000), "tenant_id", "factory_id"));
        Assert.False(MdpDbPullExecutor.IsScopeMismatch(
            rowsRead: 0, Ctx(offset: 5000), "tenant_id", "factory_id", sourceHasRowsOutsideScope: true));
    }

    [Fact]
    public void ScopeMismatch_UsesExistingPartialEnumMember_NotFailed()
    {
        // mdp_sync_log.status 既有 enum('RUNNING','SUCCESS','PARTIAL','FAILED')，无需改表。
        Assert.Equal("PARTIAL", MdpDbPullExecutor.SyncStatusPartial);
        Assert.Equal("PARTIAL", MdpDbPullExecutor.ResolveSyncStatus(
            MdpDbPullExecutor.SyncStatusPartial, "SCOPE_MISMATCH: ..."));
        // 显式 status 缺省时仍沿用旧的 "有 error 即 FAILED"
        Assert.Equal("SUCCESS", MdpDbPullExecutor.ResolveSyncStatus(null, null));
        Assert.Equal("FAILED", MdpDbPullExecutor.ResolveSyncStatus(null, "boom"));
    }

    /// <summary>
    /// 探针必须<b>保留租户谓词、只去掉工厂谓词</b>。
    ///
    /// <para>此前两个作用域谓词一起剥掉，探针退化成 <c>SELECT 1 FROM 源表 LIMIT 1</c>。
    /// 在多租户共享同一张源表的模型下，它对任何租户都会命中别的租户的行 ——
    /// 于是每个<b>合法空租户</b>都被误报成 SCOPE_MISMATCH（共享库上累计 291 条误报），
    /// 而真正被工厂谓词滤空的租户反而一条都没报出来，信号方向是反的。</para>
    ///
    /// <para>探针要回答的是「同一租户内，是否因工厂口径而取空」，
    /// 不是「整张表有没有数据」。</para>
    /// </summary>
    [Fact]
    public void ProbeSql_KeepsTenantPredicate_DropsOnlyFactoryPredicate()
    {
        var entity = new Admin.NET.Plugin.AiDOP.Entity.DataPlatform.MdpEntity
        {
            SourceTableName = "b_examine_result",
            IncrColumn = "update_time",
            LastCursor = "2026-08-01 00:00:00.000"
        };
        var ctx = new MdpPullContext { TenantId = 797403760988229, FactoryId = 1, FullRefresh = false };

        var probe = MdpDbPullExecutor.BuildScopeProbeSql(
            entity, isSqlServer: false, ctx, useKeyset: false, tenantCol: "tenant_id");

        Assert.Contains("`tenant_id` = @scopeTenantId", probe);   // 租户维度必须保留
        Assert.DoesNotContain("@scopeFactoryId", probe);          // 工厂维度正是要去掉的那个
        Assert.Contains("`update_time` > @cursor", probe);        // 增量窗口保留，避免把"增量无新数据"误判为作用域异常
        Assert.Contains("FROM b_examine_result", probe);
        Assert.Contains("LIMIT 1", probe);                        // 只做存在性判断
    }

    /// <summary>源表没有租户列时，探针自然不发射租户谓词——不得凭空造一个。</summary>
    [Fact]
    public void ProbeSql_OmitsTenantPredicate_WhenSourceHasNoTenantColumn()
    {
        var entity = new Admin.NET.Plugin.AiDOP.Entity.DataPlatform.MdpEntity
        {
            SourceTableName = "b_examine_result"
        };
        var ctx = new MdpPullContext { TenantId = 797403760988229, FactoryId = 1 };

        var probe = MdpDbPullExecutor.BuildScopeProbeSql(
            entity, isSqlServer: false, ctx, useKeyset: false, tenantCol: null);

        Assert.DoesNotContain("@scopeTenantId", probe);
        Assert.DoesNotContain("@scopeFactoryId", probe);
    }

    [Fact]
    public void ProbeSql_RejectsIllegalSourceTableName()
    {
        var entity = new Admin.NET.Plugin.AiDOP.Entity.DataPlatform.MdpEntity
        {
            SourceTableName = "b_examine_result; DROP TABLE x"
        };
        Assert.Throws<InvalidOperationException>(() =>
            MdpDbPullExecutor.BuildScopeProbeSql(entity, isSqlServer: false, new MdpPullContext(), useKeyset: false));
    }

    /// <summary>
    /// 参数剥离必须与探针 SQL 是否发射租户谓词严格一致：
    /// 带租户谓词就留 <c>@scopeTenantId</c>（否则参数缺失），不带就剔除（否则多绑一个 SQL 里没有的参数）。
    /// <c>@scopeFactoryId</c> 两种情况下都剔除——探针的全部意义就是"去掉工厂口径再看一眼"。
    /// </summary>
    [Fact]
    public void ProbeParameters_KeepTenantWhenProbeEmitsTenantPredicate()
    {
        var parameters = new List<SugarParameter>
        {
            new("@scopeTenantId", 797403760988229),
            new("@scopeFactoryId", 1),
            new("@cursor", "2026-08-01 00:00:00.000"),
            new("@windowFrom", DateTime.Parse("2026-08-01"))
        };

        var kept = MdpDbPullExecutor.StripScopeParameters(parameters, keepTenantParameter: true);
        Assert.Equal(3, kept.Count);
        Assert.Contains(kept, p => p.ParameterName.Contains("scopeTenantId", StringComparison.OrdinalIgnoreCase));
        Assert.DoesNotContain(kept, p => p.ParameterName.Contains("scopeFactoryId", StringComparison.OrdinalIgnoreCase));

        var stripped = MdpDbPullExecutor.StripScopeParameters(parameters, keepTenantParameter: false);

        Assert.Equal(2, stripped.Count);
        Assert.DoesNotContain(stripped, p => p.ParameterName.Contains("scope", StringComparison.OrdinalIgnoreCase));
        Assert.Contains(stripped, p => p.ParameterName.Contains("cursor", StringComparison.OrdinalIgnoreCase));
        Assert.Contains(stripped, p => p.ParameterName.Contains("windowFrom", StringComparison.OrdinalIgnoreCase));
    }
}