using Xunit;

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

/// <summary>
/// dwd_requirement_examine_detail 作为「工单当前齐套/缺料」中台权威表的契约。
///
/// 为什么是源文本契约测试而不是跑库集成测试：
///   本表的 schema 与全部投影逻辑都以 SQL 字符串形式内联在 S1MdpSyncTransformService.cs 里
///   （没有 SqlSugar 实体、没有 UpdateScripts 的 CREATE），
///   本仓库 DataPlatform/ 下既有测试（MdpStandardUpsertContractTests / T8KpiTenantIsolationContractTests）
///   也一律用这种方式守护数据中台 SQL。这里沿用同一范式，逐条钉死会被悄悄改坏的不变量。
/// </summary>
public class S1RequirementExamineDwdContractTests
{
    private static string Service() => File.ReadAllText(FindFile(
        "server", "Plugins", "Admin.NET.Plugin.AiDOP", "Order", "S1MdpSyncTransformService.cs"));

    /// <summary>
    /// 按<b>内容</b>而不是按文件名定位本批迁移。
    ///
    /// <para>本仓的版本号是「提交那一刻才取号」的（多实例并行，谁先 push 谁定号），
    /// 所以迁移文件名在合并前后都可能顺延。写死 <c>1.0.NNN.sql</c> 的测试
    /// 会在每一次顺延时集体变红，而红的原因与被测契约毫无关系 —— 实测发生过一次。</para>
    /// </summary>
    private static string MigrationPath(string suffix)
    {
        var dir = new DirectoryInfo(AppContext.BaseDirectory);
        while (dir != null)
        {
            var scripts = Path.Combine(dir.FullName, "server", "Admin.NET.Web.Entry", "UpdateScripts");
            if (Directory.Exists(scripts))
            {
                // 注意：通配 "1.0.*.sql" 也会命中 "1.0.NNN.verify.sql"（它同样以 .sql 结尾）。
                // 必须按文件名精确收口，否则 Migration() 会拿到 verify 脚本 —— 实测踩过。
                var hit = Directory.GetFiles(scripts, "1.0.*.sql")
                    .Where(f => Path.GetFileName(f).EndsWith(suffix, StringComparison.Ordinal)
                             && (suffix != ".sql" || !Path.GetFileName(f).EndsWith(".verify.sql", StringComparison.Ordinal)))
                    .Where(f => File.ReadAllText(f).Contains("dwd_requirement_examine_detail", StringComparison.Ordinal)
                             && File.ReadAllText(f).Contains("material_role", StringComparison.Ordinal))
                    .OrderBy(f => f, StringComparer.Ordinal)
                    .LastOrDefault();
                if (hit != null) return hit;
            }
            dir = dir.Parent;
        }
        throw new FileNotFoundException($"找不到含 material_role 的 dwd_requirement_examine_detail 迁移脚本 (*{suffix})");
    }

    private static string Migration() => File.ReadAllText(MigrationPath(".sql"));

    // ── B1 · 新列必须「内联 DDL」与「迁移脚本」两处同时到位 ──────────────────────────
    // 这是本表最容易出事的地方：新库只走内联 CREATE TABLE IF NOT EXISTS，旧库只走 ALTER，
    // 任一处漏改都会让两种环境的 schema 分叉，且分叉在运行期才暴露。
    [Fact]
    public void NewColumns_ExistInBothInlineDdlAndMigration()
    {
        var service = Service();
        var migration = Migration();

        Assert.Contains("bom_level INT NULL", service);
        Assert.Contains("material_role VARCHAR(32) NULL", service);
        Assert.Contains("is_current_flag TINYINT NOT NULL DEFAULT 0", service);

        Assert.Contains("ADD COLUMN `bom_level` INT NULL", migration);
        Assert.Contains("ADD COLUMN `material_role` VARCHAR(32) NULL", migration);
        Assert.Contains("ADD COLUMN `is_current_flag` TINYINT NOT NULL DEFAULT 0", migration);
    }

    [Fact]
    public void CurrentSnapshotIndex_ExistsInBothInlineDdlAndMigration()
    {
        Assert.Contains("KEY idx_req_exam_current (tenant_id, is_current_flag, morder_no)", Service());
        Assert.Contains("ADD KEY `idx_req_exam_current` (`tenant_id`,`is_current_flag`,`morder_no`)", Migration());
    }

    // 仓库约定：布尔一律 <noun>_flag TINYINT，不用 bit、不用 is_* 裸名（is_current_flag 本身是既定命名例外）。
    [Fact]
    public void BooleanColumn_FollowsRepoFlagConvention_NotBit()
    {
        var service = Service();
        var ddl = Slice(service, "CREATE TABLE IF NOT EXISTS dwd_requirement_examine_detail", ") ENGINE=InnoDB");

        Assert.Contains("is_current_flag TINYINT", ddl);
        Assert.DoesNotContain("is_current_flag BIT", ddl);
        Assert.DoesNotContain("bom_level BIT", ddl);
    }

    // ── B2 · 角色投影：level=1 → 根，其余（含脏值/NULL）→ 投入料 ────────────────────
    [Fact]
    public void MaterialRole_ProjectsLevelOneAsFinishedGoodRoot()
    {
        var service = Service();

        Assert.Contains("CASE WHEN JSON_UNQUOTE(JSON_EXTRACT(d.raw_data,'$.level')) = '1'", service);
        Assert.Contains("THEN 'FINISHED_GOOD_ROOT' ELSE 'INPUT_MATERIAL' END", service);
    }

    /// <summary>
    /// 失败方向必须保守：脏值 / NULL / 空串一律落 INPUT_MATERIAL，绝不能落成根、更不能整行丢掉。
    /// 反向断言（ELSE 不得是 FINISHED_GOOD_ROOT）才是真正防回归的那一条 ——
    /// 如果哪天有人把三元反过来写成「非 1 就是根」，正向断言仍会通过，只有这条会挂。
    /// </summary>
    [Fact]
    public void MaterialRole_MalformedOrNullLevel_FallsBackToInputMaterial()
    {
        var service = Service();

        Assert.DoesNotContain("ELSE 'FINISHED_GOOD_ROOT'", service);
        // 角色列不得出现 NULL 分支：CASE 必须有 ELSE，否则脏值会变成 NULL
        Assert.DoesNotContain("THEN 'FINISHED_GOOD_ROOT' END", service);
        // level 不得被提升为过滤条件——那会把脏值行直接丢掉（漏报缺料）
        Assert.DoesNotContain("AND JSON_UNQUOTE(JSON_EXTRACT(d.raw_data,'$.level'))", service);
    }

    /// <summary>bom_level 只在纯数字时物化，不猜层级；下游（含 S8）只需读 material_role。</summary>
    [Fact]
    public void BomLevel_OnlyMaterializedWhenNumeric()
    {
        Assert.Contains(
            "CASE WHEN JSON_UNQUOTE(JSON_EXTRACT(d.raw_data,'$.level')) REGEXP '^[0-9]+$'",
            Service());
    }

    // ── B4 · 当前快照发布必须是「一条」原子语句 ──────────────────────────────────────
    [Fact]
    public void CurrentSnapshotPublication_IsSingleAtomicStatement()
    {
        var service = Service();
        var publish = Slice(service, "UPDATE dwd_requirement_examine_detail", "\"\"\",");

        Assert.Contains("SET is_current_flag = CASE WHEN calc_batch_id=@BatchId THEN 1 ELSE 0 END", publish);
        Assert.Contains("AND (calc_batch_id=@BatchId OR is_current_flag=1)", publish);

        // 单条语句：整段里不得出现第二个分号分隔的语句
        Assert.DoesNotContain(";", publish);
    }

    /// <summary>
    /// 禁止「先清后置」/「先置后清」两段式：BuildDwdAsync 没有事务，
    /// 两段式必然留下「零个当前批次」（漏报）或「两个当前批次」（重复计数）的可见窗口。
    /// </summary>
    [Fact]
    public void CurrentSnapshotPublication_ForbidsTwoStatementClearThenSet()
    {
        var service = Service();
        var sqlOnly = StripComments(service);

        Assert.DoesNotContain("SET is_current_flag = 0", sqlOnly);
        Assert.DoesNotContain("SET is_current_flag = 1", sqlOnly);
        Assert.DoesNotContain("SET is_current_flag=0", sqlOnly);
        Assert.DoesNotContain("SET is_current_flag=1", sqlOnly);
        // 只能有一处写 is_current_flag 的 UPDATE
        Assert.Equal(1, Count(sqlOnly, "SET is_current_flag"));
    }

    /// <summary>
    /// factory_id 必须归一后再圈作用域。实测存量 296189/1019060 行 factory_id IS NULL，
    /// 且 594/3439 个批次同时含 factory_id=1 与 NULL；
    /// 若写成 factory_id=@FactoryId，NULL 行永不被 = 匹配，旧批次会永久残留 is_current_flag=1 → 双 current。
    /// </summary>
    [Fact]
    public void CurrentSnapshotPublication_NormalizesNullFactoryScope()
    {
        var publish = Slice(Service(), "UPDATE dwd_requirement_examine_detail", "\"\"\",");

        Assert.Contains("AND COALESCE(NULLIF(factory_id, 0), 1)=@FactoryId", publish);
        Assert.DoesNotContain("AND factory_id=@FactoryId", publish);
    }

    /// <summary>发布必须是 DWD 阶段的最后一步：在所有 DWD 写入命令跑完之后。</summary>
    [Fact]
    public void CurrentSnapshotPublication_RunsAfterAllDwdCommands()
    {
        var service = Service();
        var loop = service.IndexOf("foreach (var command in BuildDwdCommands(", StringComparison.Ordinal);
        var publish = service.IndexOf("await PublishCurrentSnapshotAsync(db, scope, batchId);", StringComparison.Ordinal);

        Assert.True(loop > 0 && publish > loop, "发布语句必须位于 BuildDwdCommands 循环之后");
    }

    /// <summary>ON DUPLICATE KEY UPDATE 不得回写 is_current_flag，否则同批次重跑会把已发布状态打回 0。</summary>
    [Fact]
    public void UpsertClause_DoesNotResetCurrentFlag()
    {
        Assert.DoesNotContain("is_current_flag=VALUES(is_current_flag)", Service());
        Assert.Contains("bom_level=VALUES(bom_level), material_role=VALUES(material_role)", Service());
    }

    // ── B5 · 表头软删必须被排除 ──────────────────────────────────────────────────────
    [Fact]
    public void DwdBuild_ExcludesDeletedExamineHeaders()
    {
        Assert.Contains(
            "AND JSON_UNQUOTE(JSON_EXTRACT(r.raw_data,'$.IsDeleted')) IN ('0', 'false', 'False', 'base64:type16:AA==')",
            Service());
    }

    /// <summary>
    /// bit(1) 源列经贴源后两种编码并存（'1'/'0' 与 base64:type16:AQ==/AA==），
    /// 任何布尔过滤都必须同时认这两种，只认一种会静默漏掉整批数据。
    /// </summary>
    [Fact]
    public void BooleanFilters_HandleBothBitEncodings()
    {
        var service = Service();

        // 存活表头：明文 '0' 与 base64 AA== 都要认
        Assert.Contains("'0', 'false', 'False', 'base64:type16:AA=='", service);
        // 启用明细：明文 '1' 与 base64 AQ== 都要认
        Assert.Contains("'1', 'true', 'True', 'base64:type16:AQ=='", service);

        // base64 编码不得只出现在其中一侧
        Assert.True(Count(service, "base64:type16:AA==") >= 1);
        Assert.True(Count(service, "base64:type16:AQ==") >= 2);
    }

    // ── B6 · FULL = 镜像（DWD 侧权宜实现）───────────────────────────────────────────
    /// <summary>
    /// 只取每个 (tenant_id, source_table) 的最新 sync_batch_id。
    /// 明细与表头两侧都必须过滤，且父行（成品根）查找子查询同样要过滤，
    /// 否则会拿一个旧批次的根去挂今天的子件。
    /// </summary>
    [Fact]
    public void DwdBuild_UsesOnlyLatestSyncBatchPerSourceTable()
    {
        var service = Service();

        Assert.Contains(
            "AND d.sync_batch_id = (SELECT lb.sync_batch_id FROM mdp_stg_so lb",
            service);
        Assert.Contains(
            "AND r.sync_batch_id = (SELECT lb.sync_batch_id FROM mdp_stg_so lb",
            service);
        Assert.Contains("ORDER BY lb.sync_time DESC, lb.id DESC LIMIT 1)", service);

        // 明细侧 + 表头侧 + 父行子查询 = 至少 3 处
        Assert.True(Count(service, "ORDER BY lb.sync_time DESC, lb.id DESC LIMIT 1)") >= 3);
    }

    /// <summary>权宜实现必须写明是 STOPGAP 及其失效前提（entity 改成 INCREMENTAL 就会错杀存量行）。</summary>
    [Fact]
    public void LatestBatchStopgap_IsDocumentedAsStopgap()
    {
        var service = Service();

        Assert.Contains("STOPGAP", service);
        Assert.Contains("INCREMENTAL", service);
    }

    // ── B3 · 齐套三量 ────────────────────────────────────────────────────────────────
    /// <summary>不得新增别名列，也不得重算出第二套数字。</summary>
    [Fact]
    public void QuantityColumns_NoAliasAndNoRecomputation()
    {
        // 别名词本身会合法出现在「不要新增 xxx 别名」的列注释与说明文字里，
        // 所以不能整文件盲搜，必须针对「列声明 / 列清单 / 回写」三个真正会引入别名列的位置断言。
        var service = Service();
        var migration = Migration();
        var insertColumns = Slice(service, "INSERT INTO dwd_requirement_examine_detail", "SELECT");

        foreach (var alias in new[] { "required_qty", "satisfied_qty", "shortage_qty" })
        {
            Assert.DoesNotContain(alias + " DECIMAL", service);       // 内联 DDL 里的列声明
            Assert.DoesNotContain(alias + "=VALUES(", service);       // ON DUPLICATE KEY UPDATE 回写
            Assert.DoesNotContain(alias, insertColumns);              // INSERT 列清单
            Assert.DoesNotContain("ADD COLUMN `" + alias + "`", migration);
        }

        // 三个标准列必须是直取源值，不得由其它列相减/相加得出
        Assert.DoesNotContain("need_count - use_qty", service);
        Assert.DoesNotContain("need_count-use_qty", service);
    }

    [Fact]
    public void QuantityColumns_DocumentedAsStandardNames()
    {
        Assert.Contains("标准列·需求量", Service());
        Assert.Contains("标准列·已满足量", Service());
        Assert.Contains("标准列·缺口量", Service());
    }

    /// <summary>恒等式 lack + use = need 必须被 verify 脚本守住（实测 1019060/1019060 行成立）。</summary>
    [Fact]
    public void QuantityIdentity_IsAssertedByVerifyScript()
    {
        var verify = File.ReadAllText(MigrationPath(".verify.sql"));

        Assert.Contains("ABS(IFNULL(`lack_qty`,0) + IFNULL(`use_qty`,0) - IFNULL(`need_count`,0)) > 0.0001", verify);
    }

    // ── B7 · 前修数据清理 ────────────────────────────────────────────────────────────
    /// <summary>
    /// 前修行以 material_role IS NULL 精确识别（修复后的投影恒不产生 NULL）。
    /// 该谓词幂等、可重跑，且不会误删迁移期间新管线产生的正确行。
    /// </summary>
    [Fact]
    public void LegacyRows_PurgedByNullRolePredicate()
    {
        var migration = Migration();

        Assert.Contains("DELETE FROM `dwd_requirement_examine_detail` WHERE `material_role` IS NULL", migration);
        // 不得无条件清表：那样会连迁移期间新产生的正确行一起删掉
        Assert.DoesNotContain("TRUNCATE", migration);
    }

    /// <summary>迁移必须是幂等的：所有 DDL 都要有存在性守卫。</summary>
    [Fact]
    public void Migration_IsIdempotent()
    {
        var migration = Migration();

        Assert.True(Count(migration, "PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;") >= 6);
        // 表不存在时（全新库尚未跑过 S1）必须整体跳过，不能 ALTER 一张不存在的表
        Assert.Contains("information_schema.TABLES", migration);
        Assert.True(Count(migration, "@tbl = 1") >= 6);
    }

    // ── 通用护栏 ─────────────────────────────────────────────────────────────────────
    /// <summary>本表是通用中台权威表，不得为单一消费方（S8）开专属列。</summary>
    [Fact]
    public void Table_HasNoS8SpecificColumns()
    {
        var ddl = Slice(Service(),
            "CREATE TABLE IF NOT EXISTS dwd_requirement_examine_detail", ") ENGINE=InnoDB");

        Assert.DoesNotContain("s8_", ddl);
        Assert.DoesNotContain("exception_id", ddl);
        Assert.DoesNotContain("rule_code", ddl);
    }

    private static string Slice(string source, string start, string end)
    {
        var from = source.IndexOf(start, StringComparison.Ordinal);
        Assert.True(from >= 0, $"未找到起始锚点：{start}");
        var to = source.IndexOf(end, from, StringComparison.Ordinal);
        Assert.True(to > from, $"未找到结束锚点：{end}");
        return source.Substring(from, to - from);
    }

    /// <summary>去掉 C# 行注释与 SQL 行注释，避免注释里的示例文本污染「禁止出现」类断言。</summary>
    private static string StripComments(string source)
    {
        var lines = source.Split('\n');
        var kept = lines.Where(l =>
        {
            var t = l.TrimStart();
            return !t.StartsWith("//", StringComparison.Ordinal)
                   && !t.StartsWith("--", StringComparison.Ordinal)
                   && !t.StartsWith("///", StringComparison.Ordinal)
                   && !t.StartsWith("*", StringComparison.Ordinal);
        });
        return string.Join("\n", kept);
    }

    private static int Count(string source, string token)
    {
        var count = 0;
        for (var index = 0; (index = source.IndexOf(token, index, StringComparison.Ordinal)) >= 0; index += token.Length)
            count++;
        return count;
    }

    private static string FindFile(params string[] parts)
    {
        var dir = new DirectoryInfo(AppContext.BaseDirectory);
        while (dir != null)
        {
            var candidate = Path.Combine(new[] { dir.FullName }.Concat(parts).ToArray());
            if (File.Exists(candidate)) return candidate;
            dir = dir.Parent;
        }
        throw new FileNotFoundException(string.Join("/", parts));
    }
}