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

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

/// <summary>
/// S5 供应商链 P0 租户安全 · 谓词语义验证（§14 C / D / E / F 的数据面部分）。
/// <para>
/// <b>门禁</b>：仅当环境变量 AIDOP_IT=1 时执行。
/// <b>全程零写入</b>：不 INSERT / UPDATE / DELETE 任何业务表，也不执行 Refresh。
/// 双租户样本用内联派生表（<c>SELECT ... UNION ALL</c>）构造，把**修复后的真实谓词**
/// 放到真实 MySQL 引擎上跑，验证「本租户算式只吃本租户行」。
/// </para>
/// <para>
/// 边界声明：本组验证的是 <b>SQL 谓词语义</b>，不是端到端接线。
/// 「谁调用、传的哪个 tenantId、是否匿名」由 <see cref="SupplierTenantSecurityGuardTests"/> 的
/// 反射 / 源码断言覆盖。Refresh 属破坏性写路径，按批次纪律
/// <b>RUNTIME DESTRUCTIVE PATH NOT EXECUTED</b>。
/// </para>
/// </summary>
[Trait("Category", "Integration")]
public class SupplierTenantSecurityDataTests
{
    private const long TenantA = 838257186181189L;   // UAT
    private const long TenantB = 797403760988229L;   // AIDOP

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

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

    /// <summary>双租户 MissedPrint 样本：同一 (PurOrd, PurLine)，A=10 / B=99。</summary>
    private const string MissedPrintFixture = """
        SELECT @A AS tenant_id, 'PO-DUP-1' AS PurOrd, 1 AS PurLine, 'I' AS Status, 10 AS Qty, 'SHD-DUP-1' AS ShipperNbr
        UNION ALL SELECT @B, 'PO-DUP-1', 1, 'I', 99, 'SHD-DUP-1'
        UNION ALL SELECT @B, 'PO-DUP-1', 1, 'I',  1, 'SHD-DUP-1'
        """;

    /// <summary>双租户欠料中间表样本：同一 ItemNum，各 1 行。</summary>
    private const string TotalFixture = """
        SELECT @A AS tenant_id, 'ITEM-DUP' AS ItemNum, 0 AS zjsl, 0 AS locStock
        UNION ALL SELECT @B, 'ITEM-DUP', 0, 0
        """;

    private static List<SugarParameter> Pars() => new()
    {
        new("@A", TenantA), new("@B", TenantB), new("@TenantId", TenantA),
    };

    // ===================== E：MissedPrint 在检数量聚合 =====================

    /// <summary>
    /// §14 E：Tenant A 的 zjsl 必须只得到 10，不得得到 110（A 的 10 + B 的 99+1）。
    /// 修复后的谓词是 <c>m.tenant_id = p0.tenant_id</c>。
    /// </summary>
    [Fact]
    public async Task E_MissedPrintAggregate_OnlySumsOwnTenant()
    {
        if (!Enabled) return;
        var db = BuildAidopdev();
        Assert.NotNull(db);

        var scoped = await db!.Ado.SqlQuerySingleAsync<decimal?>(
            $"""
            SELECT (SELECT SUM(m.Qty) FROM ({MissedPrintFixture}) m
                    WHERE m.Status='I' AND m.PurOrd=p0.PurOrd AND m.PurLine=p0.Line
                      AND m.tenant_id = p0.tenant_id)
            FROM (SELECT @TenantId AS tenant_id, 'PO-DUP-1' AS PurOrd, 1 AS Line) p0
            """, Pars());

        // 负向对照：旧写法（无 tenant 条件）会把 B 的 100 一起算进来
        var unscoped = await db.Ado.SqlQuerySingleAsync<decimal?>(
            $"""
            SELECT (SELECT SUM(m.Qty) FROM ({MissedPrintFixture}) m
                    WHERE m.Status='I' AND m.PurOrd=p0.PurOrd AND m.PurLine=p0.Line)
            FROM (SELECT @TenantId AS tenant_id, 'PO-DUP-1' AS PurOrd, 1 AS Line) p0
            """, Pars());

        _out.WriteLine($"修复后 zjsl={scoped}；旧写法 zjsl={unscoped}");
        Assert.Equal(110m, unscoped);   // 负向对照成立，用例才有意义
        Assert.Equal(10m, scoped);
    }

    // ===================== F：PurOrdWmsPush 箱码取数 =====================

    /// <summary>
    /// §14 F：同一 ShipperNbr 双租户时，Tenant A 入队只能读到 A 自己的箱码。
    /// </summary>
    [Fact]
    public async Task F_BarcodeSelect_OnlyReadsOwnTenant()
    {
        if (!Enabled) return;
        var db = BuildAidopdev();
        Assert.NotNull(db);

        var scoped = await db!.Ado.GetIntAsync(
            $"""
            SELECT COUNT(1) FROM ({MissedPrintFixture}) m
            WHERE m.ShipperNbr='SHD-DUP-1' AND m.tenant_id = @TenantId
            """, Pars());

        var unscoped = await db.Ado.GetIntAsync(
            $"""
            SELECT COUNT(1) FROM ({MissedPrintFixture}) m
            WHERE m.ShipperNbr='SHD-DUP-1'
            """, Pars());

        _out.WriteLine($"修复后箱码数={scoped}；旧写法={unscoped}");
        Assert.Equal(3, unscoped);
        Assert.Equal(1, scoped);
    }

    // ===================== C / D：DELETE / UPDATE 影响面 =====================

    /// <summary>
    /// §14 C：Tenant A 的清表只能命中 A。用 SELECT 复刻 DELETE 的 WHERE 求影响面，不执行 DELETE。
    /// </summary>
    [Fact]
    public async Task C_DeleteScope_TouchesOnlyOwnTenant()
    {
        if (!Enabled) return;
        var db = BuildAidopdev();
        Assert.NotNull(db);

        var affected = await db!.Ado.SqlQueryAsync<long>(
            $"SELECT tenant_id FROM ({TotalFixture}) a WHERE a.tenant_id = @TenantId", Pars());
        var affectedAll = await db.Ado.GetIntAsync(
            $"SELECT COUNT(1) FROM ({TotalFixture}) a", Pars());

        _out.WriteLine($"本租户命中={affected.Count}，样本总行={affectedAll}");
        Assert.Equal(2, affectedAll);                 // 样本确实含两个租户，用例才有意义
        Assert.Single(affected);
        Assert.Equal(TenantA, affected[0]);
        Assert.DoesNotContain(TenantB, affected);
    }

    /// <summary>
    /// §14 D：同 ItemNum 的双租户数据，Tenant A 的 UPDATE 不得命中 B。
    /// 复刻 Step 3 的 `JOIN ... ON a.ItemNum=b.ItemNum WHERE a.tenant_id=@TenantId` 影响面。
    /// </summary>
    [Fact]
    public async Task D_UpdateScope_TouchesOnlyOwnTenant()
    {
        if (!Enabled) return;
        var db = BuildAidopdev();
        Assert.NotNull(db);

        const string joinSrc = "SELECT 'ITEM-DUP' AS ItemNum, 7 AS zjsl";

        var scoped = await db!.Ado.SqlQueryAsync<long>(
            $"""
            SELECT a.tenant_id FROM ({TotalFixture}) a
            JOIN ({joinSrc}) b ON a.ItemNum = b.ItemNum
            WHERE a.tenant_id = @TenantId
            """, Pars());

        // 负向对照：旧写法只按 ItemNum 关联，会同时改到两个租户
        var unscoped = await db.Ado.SqlQueryAsync<long>(
            $"""
            SELECT a.tenant_id FROM ({TotalFixture}) a
            JOIN ({joinSrc}) b ON a.ItemNum = b.ItemNum
            """, Pars());

        _out.WriteLine($"修复后命中租户={string.Join(",", scoped)}；旧写法命中={string.Join(",", unscoped)}");
        Assert.Equal(2, unscoped.Count);
        Assert.Single(scoped);
        Assert.Equal(TenantA, scoped[0]);
    }

    // ===================== 现网键碰撞现状（§12） =====================

    /// <summary>
    /// §12：即使当前无跨租户重复也不能省 tenant 条件（业务键无数据库级唯一约束）。
    /// 本用例只记录现状，不因为「今天不重复」而放松要求；若将来出现重复，日志里能直接看到。
    /// </summary>
    [Fact]
    public async Task BusinessKeys_HaveNoGlobalUniqueConstraint()
    {
        if (!Enabled) return;
        var db = BuildAidopdev();
        Assert.NotNull(db);

        var dupMp = await db!.Ado.GetIntAsync(
            "SELECT COUNT(1) FROM (SELECT PurOrd,PurLine FROM MissedPrint WHERE IFNULL(PurOrd,'')<>'' GROUP BY PurOrd,PurLine HAVING COUNT(DISTINCT tenant_id)>1) x");
        var dupShipper = await db.Ado.GetIntAsync(
            "SELECT COUNT(1) FROM (SELECT ShipperNbr FROM MissedPrint WHERE IFNULL(ShipperNbr,'')<>'' GROUP BY ShipperNbr HAVING COUNT(DISTINCT tenant_id)>1) x");
        var dupItem = await db.Ado.GetIntAsync(
            "SELECT COUNT(1) FROM (SELECT ItemNum FROM ItemMaster GROUP BY ItemNum HAVING COUNT(DISTINCT tenant_id)>1) x");

        _out.WriteLine($"跨租户重复：MissedPrint(PurOrd,PurLine)={dupMp} ShipperNbr={dupShipper} ItemMaster.ItemNum={dupItem}");

        // 这些业务键没有任何**不含 tenant_id 的**唯一索引 —— 即它们全局不唯一，
        // 所以只按业务键关联必然可能跨租户串数，必须显式带 tenant 条件。
        // （ItemMaster 有 uk_ItemMaster_factory_item = (tenant_id, factory_ref_id, ItemNum)，
        //   恰恰说明 ItemNum 只在租户内唯一。）
        var globallyUniqueIdx = await db.Ado.SqlQueryAsync<string>(
            """
            SELECT CONCAT(s.TABLE_NAME, '.', s.INDEX_NAME)
            FROM information_schema.STATISTICS s
            WHERE s.TABLE_SCHEMA = DATABASE() AND s.NON_UNIQUE = 0
              AND ((s.TABLE_NAME='MissedPrint'   AND s.COLUMN_NAME IN ('ShipperNbr','PurOrd'))
                OR (s.TABLE_NAME='ItemMaster'    AND s.COLUMN_NAME = 'ItemNum')
                OR (s.TABLE_NAME='WorkOrdMaster' AND s.COLUMN_NAME = 'WorkOrd'))
              AND NOT EXISTS (
                    SELECT 1 FROM information_schema.STATISTICS t
                    WHERE t.TABLE_SCHEMA = s.TABLE_SCHEMA AND t.TABLE_NAME = s.TABLE_NAME
                      AND t.INDEX_NAME = s.INDEX_NAME AND t.COLUMN_NAME = 'tenant_id')
            GROUP BY s.TABLE_NAME, s.INDEX_NAME
            """);
        _out.WriteLine($"不含 tenant_id 的唯一索引：{(globallyUniqueIdx.Count == 0 ? "无" : string.Join(",", globallyUniqueIdx))}");
        Assert.Empty(globallyUniqueIdx);

        // ItemNum 已经跨租户重名 —— 这条是「不能只按业务键关联」的现网证据
        Assert.True(dupItem > 0, "ItemMaster.ItemNum 若已全局唯一，需重新评估本组结论");
    }

    // ===================== §17 回归：008 主链未被本批破坏 =====================

    [Fact]
    public async Task Regression_LabelQueryTenantSplit_Unchanged()
    {
        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");
        var uat = await db.Ado.GetIntAsync("SELECT COUNT(1) FROM MissedPrint WHERE tenant_id=@T",
            new List<SugarParameter> { new("@T", TenantA) });
        var aidop = await db.Ado.GetIntAsync("SELECT COUNT(1) FROM MissedPrint WHERE tenant_id=@T",
            new List<SugarParameter> { new("@T", TenantB) });

        _out.WriteLine($"MissedPrint total={total} null={nullTenant} UAT={uat} 797={aidop}");
        Assert.Equal(0, nullTenant);
        Assert.Equal(total, uat + aidop);
    }
}