AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP.Tests/DataPlatform/MdpSchemaAlignerTests.cs

using Admin.NET.Plugin.AiDOP.DataPlatform.Schema;
using Xunit;

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

public class MdpSchemaAlignerTests
{
    private const string Create = """
        CREATE TABLE IF NOT EXISTS mdp_std_demo (
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            tenant_id BIGINT NOT NULL,
            approved_flag TINYINT NOT NULL DEFAULT 1,
            UNIQUE KEY uk_demo (tenant_id),
            KEY idx_demo (approved_flag)
        ) ENGINE=InnoDB
        """;

    [Fact]
    public void MissingTable_PlansCreateOnly()
    {
        var planned = MdpSchemaAligner.Plan(Create, SchemaSnapshot.Missing);
        Assert.Single(planned);
        Assert.StartsWith("CREATE TABLE", planned[0], StringComparison.OrdinalIgnoreCase);
        Assert.DoesNotContain("DROP", planned[0], StringComparison.OrdinalIgnoreCase);
    }

    [Fact]
    public void MissingColumnAndIndex_PlansAdditiveAlterOnly()
    {
        var snapshot = new SchemaSnapshot(true, ["id", "tenant_id"], ["PRIMARY"]);
        var planned = MdpSchemaAligner.Plan(Create, snapshot);
        Assert.Equal(3, planned.Count);
        Assert.Contains(planned, x => x.Contains("ADD COLUMN approved_flag", StringComparison.OrdinalIgnoreCase));
        Assert.Contains(planned, x => x.Contains("ADD UNIQUE KEY `uk_demo`", StringComparison.OrdinalIgnoreCase));
        Assert.Contains(planned, x => x.Contains("ADD KEY `idx_demo`", StringComparison.OrdinalIgnoreCase));
        Assert.DoesNotContain(planned, x => x.Contains("DROP", StringComparison.OrdinalIgnoreCase));
    }

    [Fact]
    public void ReadyTable_PlansNothing()
    {
        var snapshot = new SchemaSnapshot(
            true,
            ["id", "tenant_id", "approved_flag"],
            ["PRIMARY", "uk_demo", "idx_demo"]);
        Assert.Empty(MdpSchemaAligner.Plan(Create, snapshot));
    }

    [Fact]
    public void LikeTable_SkippedWhenPresent()
    {
        const string like = "CREATE TABLE IF NOT EXISTS mdp_stg_s4_shipment LIKE mdp_stg_s4_iqc";
        Assert.Empty(MdpSchemaAligner.Plan(like, new SchemaSnapshot(true, [], [])));
        var created = MdpSchemaAligner.Plan(like, SchemaSnapshot.Missing);
        Assert.Single(created);
        Assert.Contains("LIKE", created[0], StringComparison.OrdinalIgnoreCase);
    }

    /// <summary>
    /// dwd_requirement_examine_detail 的 need_count 前面有一段说明注释。
    /// Plan 过去不剥行注释，ColumnName 取到的是「--」而不是 need_count，于是这一列
    /// 每次都被当成缺列再 ADD 一次；MySQL 执行 ALTER 时自己吃掉注释，抛
    /// 「Duplicate column name 'need_count'」，S1 重算因此每次都在对齐阶段失败。
    /// </summary>
    [Fact]
    public void CommentedColumns_AreNotReAdded()
    {
        const string create = """
            CREATE TABLE IF NOT EXISTS mdp_std_demo (
                id BIGINT AUTO_INCREMENT PRIMARY KEY,
                tenant_id BIGINT NOT NULL,
                -- 说明：这一列前面有注释，注释里还有逗号、括号 (a,b) 和 don't 这种单引号
                -- 第二行注释
                need_count DECIMAL(18,4) NULL COMMENT '标准列·需求量',
                approved_flag TINYINT NOT NULL DEFAULT 1
            ) ENGINE=InnoDB
            """;

        var ready = new SchemaSnapshot(
            true, ["id", "tenant_id", "need_count", "approved_flag"], ["PRIMARY"]);
        Assert.Empty(MdpSchemaAligner.Plan(create, ready));

        var missingOne = new SchemaSnapshot(true, ["id", "tenant_id", "approved_flag"], ["PRIMARY"]);
        var planned = MdpSchemaAligner.Plan(create, missingOne);
        Assert.Single(planned);
        Assert.Contains("ADD COLUMN need_count", planned[0], StringComparison.OrdinalIgnoreCase);
        Assert.DoesNotContain("--", planned[0], StringComparison.Ordinal);
    }

    /// <summary>COMMENT 字符串里的 -- 不是注释，不能被剥掉。</summary>
    [Fact]
    public void StripLineComments_KeepsDashesInsideQuotedText()
    {
        var stripped = MdpSchemaAligner.StripLineComments(
            "a VARCHAR(10) COMMENT 'x -- y', -- 真注释\nb INT");

        Assert.Contains("'x -- y'", stripped, StringComparison.Ordinal);
        Assert.DoesNotContain("真注释", stripped, StringComparison.Ordinal);
        Assert.Contains("b INT", stripped, StringComparison.Ordinal);
    }

    [Fact]
    public void NonNeutralTable_IsLeftUntouched()
    {
        const string sql = "CREATE TABLE IF NOT EXISTS qms_demo (id BIGINT)";
        var planned = MdpSchemaAligner.Plan(sql, SchemaSnapshot.Missing);
        Assert.Equal([sql], planned);
    }
}