sql-utilities/tests/Strata.SqlTools.Snowflake.Tests/Snowflake/ProcedureBreakdownTests.cs

using Strata.SqlTools.Breakdowns.Snowflake;

namespace Strata.SqlTools.SqlBreakdown.Tests.Snowflake;

[TestFixture]
public class ProcedureBreakdownTests
{
    #region Constructor Tests

    [Test]
    public void Constructor_WithName_SetsProcedureName()
    {
        // Arrange & Act
        var breakdown = new ProcedureBreakdown("MY_PROC");

        Assert.Multiple(() =>
        {
            // Assert
            Assert.That(breakdown.ProcedureName.Clause, Is.EqualTo("MY_PROC"));
            Assert.That(breakdown.IsUsingParameters, Is.False);
        });
    }

    [Test]
    public void Constructor_WithParameters_SetsParameters()
    {
        // Arrange
        var parameters = new Dictionary<string, string> { ["@ID"] = "1" };

        // Act
        var breakdown = new ProcedureBreakdown("MY_PROC", parameters);

        Assert.Multiple(() =>
        {
            // Assert
            Assert.That(breakdown.IsUsingParameters, Is.True);
            Assert.That(breakdown.Parameters["@ID"], Is.EqualTo("1"));
        });
    }

    #endregion

    #region GetSql Tests - Snowflake CALL Syntax

    [Test]
    public void GetSql_WithoutParameters_RendersCallWithEmptyParens()
    {
        // Arrange - Snowflake uses CALL proc() rather than EXEC proc
        var breakdown = new ProcedureBreakdown("MY_PROC");

        // Act
        var sql = breakdown.GetSql();

        Assert.Multiple(() =>
        {
            // Assert
            Assert.That(sql, Does.Contain("CALL"));
            Assert.That(sql, Does.Contain("MY_PROC"));
            Assert.That(sql, Does.Contain("()"));
            Assert.That(sql, Does.Not.Contain("EXEC"));
        });
    }

    [Test]
    public void GetSql_WithParameters_RendersNamedArrowSyntax()
    {
        // Arrange
        var breakdown = new ProcedureBreakdown("MY_PROC");
        breakdown.AddParameter("@ID", "1");

        // Act - Snowflake named-parameter syntax strips @ and uses =>
        var sql = breakdown.GetSql();

        Assert.Multiple(() =>
        {
            // Assert
            Assert.That(sql, Does.Contain("CALL"));
            Assert.That(sql, Does.Contain("ID => 1"));
        });
    }

    #endregion

    #region Parse Tests - Snowflake CALL

    [Test]
    public void Parse_CallWithoutParameters_PopulatesName()
    {
        // Act
        var breakdown = ProcedureBreakdown.Parse("CALL MY_PROC()");

        Assert.Multiple(() =>
        {
            // Assert
            Assert.That(breakdown.ProcedureName.Clause, Is.EqualTo("MY_PROC"));
            Assert.That(breakdown.IsUsingParameters, Is.False);
        });
    }

    [Test]
    public void Parse_CallWithNamedParameters_PopulatesParametersWithAtPrefix()
    {
        // Arrange
        var input = "CALL MY_PROC(ID => 1, NAME => 'BOB')";

        // Act
        var breakdown = ProcedureBreakdown.Parse(input);

        Assert.Multiple(() =>
        {
            // Assert - parsed named params are stored with @ prefix for consistency
            Assert.That(breakdown.ProcedureName.Clause, Is.EqualTo("MY_PROC"));
            Assert.That(breakdown.Parameters["@ID"], Is.EqualTo("1"));
            Assert.That(breakdown.Parameters["@NAME"], Is.EqualTo("'BOB'"));
        });
    }

    [Test]
    public void Parse_CallWithPositionalParameters_GeneratesParamNames()
    {
        // Arrange - no => present, so values are treated positionally
        var input = "CALL MY_PROC(1, 'BOB')";

        // Act
        var breakdown = ProcedureBreakdown.Parse(input);

        Assert.Multiple(() =>
        {
            // Assert
            Assert.That(breakdown.Parameters["@param1"], Is.EqualTo("1"));
            Assert.That(breakdown.Parameters["@param2"], Is.EqualTo("'BOB'"));
        });
    }

    [Test]
    public void Parse_MicrosoftSqlMode_AcceptsExecSyntax()
    {
        // Act - T-SQL EXEC is parsed via base-class delegation
        var breakdown = ProcedureBreakdown.Parse("EXEC dbo.GetUser @Id = 1", isMicrosoftSql: true);

        Assert.Multiple(() =>
        {
            // Assert
            Assert.That(breakdown, Is.InstanceOf<ProcedureBreakdown>());
            Assert.That(breakdown.ProcedureName.Clause, Is.EqualTo("dbo.GetUser"));
            Assert.That(breakdown.Parameters["@Id"], Is.EqualTo("1"));
        });
    }

    [Test]
    public void Parse_NullOrWhitespace_ThrowsArgumentNullException()
    {
        // Act & Assert
        Assert.Throws<ArgumentNullException>(() => ProcedureBreakdown.Parse("  "));
    }

    [Test]
    public void Parse_NotACallStatement_ThrowsFormatException()
    {
        // Act & Assert
        Assert.Throws<FormatException>(() => ProcedureBreakdown.Parse("SELECT * FROM USERS"));
    }

    [Test]
    public void TryParse_ValidCall_ReturnsTrue()
    {
        // Act
        var success = ProcedureBreakdown.TryParse("CALL MY_PROC()", out var breakdown);

        Assert.Multiple(() =>
        {
            // Assert
            Assert.That(success, Is.True);
            Assert.That(breakdown.ProcedureName.Clause, Is.EqualTo("MY_PROC"));
        });
    }

    [Test]
    public void TryParse_WrongPrefix_ReturnsFalseWithMessage()
    {
        // Act
        var success = ProcedureBreakdown.TryParse("SELECT 1", out _, out var error);

        Assert.Multiple(() =>
        {
            // Assert
            Assert.That(success, Is.False);
            Assert.That(error, Does.Contain("CALL"));
        });
    }

    #endregion
}