sql-utilities/tests/Strata.SqlTools.PostgreSql.Tests/PostgreSql/StatementExpressionParserTests.cs

using Strata.SqlTools.Statements.PostgreSql;

namespace Strata.SqlTools.SqlBreakdown.Tests.PostgreSql;

[TestFixture]
public class StatementExpressionParserTests
{
    [Test]
    public void Parse_NumberExpression_ReturnNum()
    {
        // Arrange
        var parser = new StatementExpressionParser();
        var sql = "42";

        // Act
        var result = parser.Parse(sql);

        // Assert
        Assert.That(result, Is.Not.Null);
    }

    [Test]
    public void Parse_NegativeNumber_Succeeds()
    {
        // Arrange
        var parser = new StatementExpressionParser();
        var sql = "-42";

        // Act
        var result = parser.Parse(sql);

        // Assert
        Assert.That(result, Is.Not.Null);
    }

    [Test]
    public void Parse_DecimalNumber_Succeeds()
    {
        // Arrange
        var parser = new StatementExpressionParser();
        var sql = "3.14159";

        // Act
        var result = parser.Parse(sql);

        // Assert
        Assert.That(result, Is.Not.Null);
    }

    [Test]
    public void Parse_StringExpression_ReturnsString()
    {
        // Arrange
        var parser = new StatementExpressionParser();
        var sql = "'hello world'";

        // Act
        var result = parser.Parse(sql);

        // Assert
        Assert.That(result, Is.Not.Null);
    }

    [Test]
    public void Parse_StringWithEscapedQuotes_Succeeds()
    {
        // Arrange
        var parser = new StatementExpressionParser();
        var sql = "'it''s working'"; // PostgreSQL escape style

        // Act
        var result = parser.Parse(sql);

        // Assert
        Assert.That(result, Is.Not.Null);
    }

    [Test]
    public void Parse_ColumnIdentifier_Succeeds()
    {
        // Arrange
        var parser = new StatementExpressionParser();
        var sql = "user_id";

        // Act
        var result = parser.Parse(sql);

        // Assert
        Assert.That(result, Is.Not.Null);
    }

    [Test]
    public void Parse_QuotedIdentifier_Succeeds()
    {
        // Arrange
        var parser = new StatementExpressionParser();
        var sql = "\"UserId\"";

        // Act
        var result = parser.Parse(sql);

        // Assert
        Assert.That(result, Is.Not.Null);
    }

    [Test]
    public void Parse_ColumnWithTablePrefix_Succeeds()
    {
        // Arrange
        var parser = new StatementExpressionParser();
        var sql = "users.id";

        // Act
        var result = parser.Parse(sql);

        // Assert
        Assert.That(result, Is.Not.Null);
    }

    [Test]
    public void Parse_QuotedColumnWithTablePrefix_Succeeds()
    {
        // Arrange
        var parser = new StatementExpressionParser();
        var sql = "\"Users\".\"Id\"";

        // Act
        var result = parser.Parse(sql);

        // Assert
        Assert.That(result, Is.Not.Null);
    }

    [Test]
    public void Parse_SimpleAddition_Succeeds()
    {
        // Arrange
        var parser = new StatementExpressionParser();
        var sql = "42 + 8";

        // Act
        var result = parser.Parse(sql);

        // Assert
        Assert.That(result, Is.Not.Null);
    }

    [Test]
    public void Parse_SimpleSubtraction_Succeeds()
    {
        // Arrange
        var parser = new StatementExpressionParser();
        var sql = "42 - 8";

        // Act
        var result = parser.Parse(sql);

        // Assert
        Assert.That(result, Is.Not.Null);
    }

    [Test]
    public void Parse_SimpleMultiplication_Succeeds()
    {
        // Arrange
        var parser = new StatementExpressionParser();
        var sql = "42 * 8";

        // Act
        var result = parser.Parse(sql);

        // Assert
        Assert.That(result, Is.Not.Null);
    }

    [Test]
    public void Parse_SimpleDivision_Succeeds()
    {
        // Arrange
        var parser = new StatementExpressionParser();
        var sql = "42 / 8";

        // Act
        var result = parser.Parse(sql);

        // Assert
        Assert.That(result, Is.Not.Null);
    }

    [Test]
    public void Parse_ComplexArithmeticExpression_Succeeds()
    {
        // Arrange
        var parser = new StatementExpressionParser();
        var sql = "(42 + 8) * (10 - 2) / 4";

        // Act
        var result = parser.Parse(sql);

        // Assert
        Assert.That(result, Is.Not.Null);
    }

    [Test]
    public void Parse_FunctionCall_Succeeds()
    {
        // Arrange
        var parser = new StatementExpressionParser();
        var sql = "COUNT(id)";

        // Act
        var result = parser.Parse(sql);

        // Assert
        Assert.That(result, Is.Not.Null);
    }

    [Test]
    public void Parse_CountExpression_Succeeds()
    {
        // Arrange
        var parser = new StatementExpressionParser();
        var sql = "COUNT(*)";

        // Act
        var result = parser.Parse(sql);

        // Assert
        Assert.That(result, Is.Not.Null);
    }

    [Test]
    public void Parse_SumExpression_Succeeds()
    {
        // Arrange
        var parser = new StatementExpressionParser();
        var sql = "SUM(amount)";

        // Act
        var result = parser.Parse(sql);

        // Assert
        Assert.That(result, Is.Not.Null);
    }

    [Test]
    public void Parse_CaseInsensitiveFunctionName_Succeeds()
    {
        // Arrange
        var parser = new StatementExpressionParser();
        var sql = "sum(amount)";

        // Act
        var result = parser.Parse(sql);

        // Assert
        Assert.That(result, Is.Not.Null);
    }

    [Test]
    public void Parse_FunctionWithMultipleArguments_Succeeds()
    {
        // Arrange
        var parser = new StatementExpressionParser();
        var sql = "SUBSTRING(name, 1, 10)";

        // Act
        var result = parser.Parse(sql);

        // Assert
        Assert.That(result, Is.Not.Null);
    }

    [Test]
    public void Parse_NestedFunctionCalls_Succeeds()
    {
        // Arrange
        var parser = new StatementExpressionParser();
        var sql = "UPPER(SUBSTRING(name, 1, 10))";

        // Act
        var result = parser.Parse(sql);

        // Assert
        Assert.That(result, Is.Not.Null);
    }

    [Test]
    public void Parse_ColumnWithAlias_Succeeds()
    {
        // Arrange
        var parser = new StatementExpressionParser();
        var sql = "COUNT(id) AS total_count";

        // Act
        var result = parser.Parse(sql);

        // Assert
        Assert.That(result, Is.Not.Null);
    }

    [Test]
    public void Parse_CaseExpression_Succeeds()
    {
        // Arrange
        var parser = new StatementExpressionParser();
        var sql = "CASE WHEN status = 'active' THEN 1 ELSE 0 END";

        // Act
        var result = parser.Parse(sql);

        // Assert
        Assert.That(result, Is.Not.Null);
    }

    [Test]
    public void Parse_PostgreSqlPositionalParameter_Succeeds()
    {
        // Arrange
        var parser = new StatementExpressionParser();
        var sql = "$1";

        // Act
        var result = parser.Parse(sql);

        // Assert
        Assert.That(result, Is.Not.Null);
    }

    [Test]
    public void Parse_NamedParameter_Succeeds()
    {
        // Arrange
        var parser = new StatementExpressionParser();
        var sql = "@userId";

        // Act
        var result = parser.Parse(sql);

        // Assert
        Assert.That(result, Is.Not.Null);
    }

    [Test]
    public void Parse_ColonNamedParameter_Succeeds()
    {
        // Arrange
        var parser = new StatementExpressionParser();
        var sql = ":userId";

        // Act
        var result = parser.Parse(sql);

        // Assert
        Assert.That(result, Is.Not.Null);
    }
}