sql-utilities/tests/Strata.SqlTools.LinqToSql.Tests/LinqQueryBreakdownBuilderTests.cs

using NUnit.Framework;
using Strata.SqlTools.Builders.LinqToSql;

namespace Strata.SqlTools.LinqToSql.Tests;

[TestFixture]
public class LinqQueryBreakdownBuilderTests
{
    #region Basic Builder Tests

    [Test]
    public void Build_WithValidConfiguration_ReturnsBreakdown()
    {
        // Arrange
        var builder = LinqQueryBreakdownBuilder
            .Create()
            .SelectColumns("Id", "Name", "Email")
            .FromTable("Users")
            .Where("Age > 18");

        // Act
        var breakdown = builder.Build();

        // Assert
        Assert.That(breakdown, Is.Not.Null);
        Assert.Multiple(() =>
        {
            Assert.That(breakdown.SelectClause.Clause, Contains.Substring("Id"));
            Assert.That(breakdown.FromClause.Clause, Is.EqualTo("Users"));
            Assert.That(breakdown.WhereClause.Clause, Is.EqualTo("Age > 18"));
        });
    }

    [Test]
    public void Build_WithoutSelectColumns_ThrowsInvalidOperationException()
    {
        // Arrange
        var builder = LinqQueryBreakdownBuilder
            .Create()
            .FromTable("Users");

        // Act & Assert
        Assert.Throws<InvalidOperationException>(() => builder.Build());
    }

    [Test]
    public void Build_WithoutFromTable_ThrowsInvalidOperationException()
    {
        // Arrange
        var builder = LinqQueryBreakdownBuilder
            .Create()
            .SelectColumns("Id", "Name");

        // Act & Assert
        Assert.Throws<InvalidOperationException>(() => builder.Build());
    }

    [Test]
    public void SelectAll_SetsSelectAllColumns()
    {
        // Arrange & Act
        var breakdown = LinqQueryBreakdownBuilder
            .Create()
            .SelectAll()
            .FromTable("Users")
            .Build();

        // Assert
        Assert.That(breakdown.SelectClause.Clause, Is.EqualTo("*"));
    }

    [Test]
    public void SelectColumns_WithEmptyArray_SelectsAll()
    {
        // Arrange & Act
        var breakdown = LinqQueryBreakdownBuilder
            .Create()
            .SelectColumns()
            .FromTable("Users")
            .Build();

        // Assert
        Assert.That(breakdown.SelectClause.Clause, Is.EqualTo("*"));
    }

    [Test]
    public void SelectColumns_WithMultipleColumns_JoinsWithComma()
    {
        // Arrange & Act
        var breakdown = LinqQueryBreakdownBuilder
            .Create()
            .SelectColumns("Id", "Name", "Email", "Age")
            .FromTable("Users")
            .Build();

        // Assert
        Assert.That(breakdown.SelectClause.Clause, Contains.Substring("Id"));
        Assert.That(breakdown.SelectClause.Clause, Contains.Substring("Name"));
        Assert.That(breakdown.SelectClause.Clause, Contains.Substring("Email"));
        Assert.That(breakdown.SelectClause.Clause, Contains.Substring("Age"));
    }

    #endregion

    #region Table Builder Tests

    [Test]
    public void FromTable_WithValidTableName_SetsFromClause()
    {
        // Arrange & Act
        var breakdown = LinqQueryBreakdownBuilder
            .Create()
            .SelectAll()
            .FromTable("Customers")
            .Build();

        // Assert
        Assert.That(breakdown.FromClause.Clause, Is.EqualTo("Customers"));
    }

    [Test]
    public void FromTable_WithNullTableName_ThrowsArgumentException()
    {
        // Arrange
        var builder = LinqQueryBreakdownBuilder.Create();

        // Act & Assert
#pragma warning disable CS8625 // Cannot convert null literal to non-nullable reference type.
        Assert.Throws<ArgumentException>(() => builder.FromTable(null));
#pragma warning restore CS8625 // Cannot convert null literal to non-nullable reference type.
    }

    [Test]
    public void FromTable_WithEmptyTableName_ThrowsArgumentException()
    {
        // Arrange
        var builder = LinqQueryBreakdownBuilder.Create();

        // Act & Assert
        Assert.Throws<ArgumentException>(() => builder.FromTable(""));
    }

    #endregion

    #region WHERE Clause Tests

    [Test]
    public void Where_WithValidCondition_SetsWhereClause()
    {
        // Arrange & Act
        var breakdown = LinqQueryBreakdownBuilder
            .Create()
            .SelectAll()
            .FromTable("Users")
            .Where("IsActive = true AND Age >= 21")
            .Build();

        // Assert
        Assert.That(breakdown.WhereClause.Clause, Is.EqualTo("IsActive = true AND Age >= 21"));
    }

    [Test]
    public void Where_WithNullCondition_IgnoresAndUsesEmptyString()
    {
        // Arrange & Act
        var breakdown = LinqQueryBreakdownBuilder
            .Create()
            .SelectAll()
            .FromTable("Users")
            .Where(null!)
            .Build();

        // Assert
        Assert.That(breakdown.WhereClause.Clause, Is.Empty);
    }

    [Test]
    public void Where_WithEmptyCondition_IgnoresAndUsesEmptyString()
    {
        // Arrange & Act
        var breakdown = LinqQueryBreakdownBuilder
            .Create()
            .SelectAll()
            .FromTable("Users")
            .Where("")
            .Build();

        // Assert
        Assert.That(breakdown.WhereClause.Clause, Is.Empty);
    }

    [Test]
    public void Where_CalledMultipleTimes_LastValueWins()
    {
        // Arrange & Act
        var breakdown = LinqQueryBreakdownBuilder
            .Create()
            .SelectAll()
            .FromTable("Users")
            .Where("Age > 18")
            .Where("Status = 'Active'")
            .Build();

        // Assert
        Assert.That(breakdown.WhereClause.Clause, Is.EqualTo("Status = 'Active'"));
    }

    #endregion

    #region GROUP BY Tests

    [Test]
    public void GroupBy_WithSingleColumn_SetsGroupByClause()
    {
        // Arrange & Act
        var breakdown = LinqQueryBreakdownBuilder
            .Create()
            .SelectColumns("Department", "COUNT(*) as EmployeeCount")
            .FromTable("Employees")
            .GroupBy("Department")
            .Build();

        // Assert
        Assert.That(breakdown.GroupByClause.Clause, Is.EqualTo("Department"));
    }

    [Test]
    public void GroupBy_WithMultipleColumns_JoinsWithComma()
    {
        // Arrange & Act
        var breakdown = LinqQueryBreakdownBuilder
            .Create()
            .SelectColumns("Department", "Level", "COUNT(*) as Count")
            .FromTable("Employees")
            .GroupBy("Department", "Level")
            .Build();

        // Assert
        Assert.That(breakdown.GroupByClause.Clause, Is.EqualTo("Department, Level"));
    }

    [Test]
    public void GroupBy_WithNoColumns_IgnoresGroupBy()
    {
        // Arrange & Act
        var breakdown = LinqQueryBreakdownBuilder
            .Create()
            .SelectAll()
            .FromTable("Users")
            .GroupBy()
            .Build();

        // Assert
        Assert.That(breakdown.GroupByClause.Clause, Is.Empty);
    }

    #endregion

    #region HAVING Clause Tests

    [Test]
    public void Having_WithValidCondition_SetsHavingClause()
    {
        // Arrange & Act
        var breakdown = LinqQueryBreakdownBuilder
            .Create()
            .SelectColumns("Department", "COUNT(*) as Count")
            .FromTable("Employees")
            .GroupBy("Department")
            .Having("COUNT(*) > 5")
            .Build();

        // Assert
        Assert.That(breakdown.HavingClause.Clause, Is.EqualTo("COUNT(*) > 5"));
    }

    [Test]
    public void Having_WithoutGroupBy_StillSetsHavingClause()
    {
        // Arrange & Act
        var breakdown = LinqQueryBreakdownBuilder
            .Create()
            .SelectAll()
            .FromTable("Users")
            .Having("COUNT(*) > 1")
            .Build();

        // Assert
        Assert.That(breakdown.HavingClause.Clause, Is.EqualTo("COUNT(*) > 1"));
    }

    #endregion

    #region ORDER BY Tests

    [Test]
    public void OrderBy_WithCustomSpecification_SetsOrderByClause()
    {
        // Arrange & Act
        var breakdown = LinqQueryBreakdownBuilder
            .Create()
            .SelectAll()
            .FromTable("Users")
            .OrderBy("LastName ASC, FirstName ASC")
            .Build();

        // Assert
        Assert.That(breakdown.OrderByClause.Clause, Is.EqualTo("LastName ASC, FirstName ASC"));
    }

    [Test]
    public void OrderByAscending_WithColumn_SetsAscendingOrder()
    {
        // Arrange & Act
        var breakdown = LinqQueryBreakdownBuilder
            .Create()
            .SelectAll()
            .FromTable("Users")
            .OrderByAscending("Name")
            .Build();

        // Assert
        Assert.That(breakdown.OrderByClause.Clause, Is.EqualTo("Name ASC"));
    }

    [Test]
    public void OrderByDescending_WithColumn_SetsDescendingOrder()
    {
        // Arrange & Act
        var breakdown = LinqQueryBreakdownBuilder
            .Create()
            .SelectAll()
            .FromTable("Users")
            .OrderByDescending("CreatedDate")
            .Build();

        // Assert
        Assert.That(breakdown.OrderByClause.Clause, Is.EqualTo("CreatedDate DESC"));
    }

    [Test]
    public void OrderByAscending_WithNullColumn_ThrowsArgumentException()
    {
        // Arrange
        var builder = LinqQueryBreakdownBuilder.Create().SelectAll().FromTable("Users");

        // Act & Assert
#pragma warning disable CS8625 // Cannot convert null literal to non-nullable reference type.
        Assert.Throws<ArgumentException>(() => builder.OrderByAscending(null));
#pragma warning restore CS8625 // Cannot convert null literal to non-nullable reference type.
    }

    [Test]
    public void OrderByDescending_WithEmptyColumn_ThrowsArgumentException()
    {
        // Arrange
        var builder = LinqQueryBreakdownBuilder.Create().SelectAll().FromTable("Users");

        // Act & Assert
        Assert.Throws<ArgumentException>(() => builder.OrderByDescending(""));
    }

    #endregion

    #region Complex Query Tests

    [Test]
    public void CompleteQuery_WithAllClauses_BuildsSuccessfully()
    {
        // Arrange & Act
        var breakdown = LinqQueryBreakdownBuilder
            .Create()
            .SelectColumns("Department", "Position", "COUNT(*) as EmployeeCount", "AVG(Salary) as AvgSalary")
            .FromTable("Employees")
            .Where("IsActive = true")
            .GroupBy("Department", "Position")
            .Having("COUNT(*) >= 3")
            .OrderByDescending("AvgSalary")
            .Build();

        Assert.Multiple(() =>
        {
            // Assert
            Assert.That(breakdown.SelectClause.Clause, Contains.Substring("Department"));
            Assert.That(breakdown.FromClause.Clause, Is.EqualTo("Employees"));
            Assert.That(breakdown.WhereClause.Clause, Is.EqualTo("IsActive = true"));
            Assert.That(breakdown.GroupByClause.Clause, Is.EqualTo("Department, Position"));
            Assert.That(breakdown.HavingClause.Clause, Is.EqualTo("COUNT(*) >= 3"));
            Assert.That(breakdown.OrderByClause.Clause, Is.EqualTo("AvgSalary DESC"));
        });
    }

    [Test]
    public void SimplePaginationQuery_BuildsSuccessfully()
    {
        // Arrange & Act
        var breakdown = LinqQueryBreakdownBuilder
            .Create()
            .SelectColumns("Id", "Name", "Email")
            .FromTable("Users")
            .Where("IsActive = true")
            .OrderByAscending("Id")
            .Build();

        Assert.Multiple(() =>
        {
            // Assert
            Assert.That(breakdown.SelectClause.Clause, Contains.Substring("Id"));
            Assert.That(breakdown.WhereClause.Clause, Is.EqualTo("IsActive = true"));
            Assert.That(breakdown.OrderByClause.Clause, Is.EqualTo("Id ASC"));
        });
    }

    #endregion

    #region Static Factory Tests

    [Test]
    public void Create_ReturnsNewBuilder()
    {
        // Act
        var builder = LinqQueryBreakdownBuilder.Create();

        // Assert
        Assert.That(builder, Is.Not.Null);
        Assert.That(builder, Is.TypeOf<LinqQueryBreakdownBuilder>());
    }

    [Test]
    public void CreateForTable_SetsTableAndReturnsBuilder()
    {
        // Act & Assert
        var breakdown = LinqQueryBreakdownBuilder
            .CreateForTable("Orders")
            .SelectAll()
            .Build();

        Assert.That(breakdown.FromClause.Clause, Is.EqualTo("Orders"));
    }

    #endregion

    #region Preview SQL Tests

    [Test]
    public void PreviewSql_WithCompleteQuery_ReturnsSqlString()
    {
        // Arrange
        var builder = LinqQueryBreakdownBuilder
            .Create()
            .SelectAll()
            .FromTable("Users")
            .Where("Age > 18");

        // Act
        var sql = builder.PreviewSql();

        // Assert
        Assert.That(sql, Is.Not.Null);
        Assert.That(sql, Contains.Substring("Users").IgnoreCase);
    }

    [Test]
    public void PreviewSql_WithIncompleteQuery_ReturnsPlaceholder()
    {
        // Arrange
        var builder = LinqQueryBreakdownBuilder.Create();

        // Act
        var sql = builder.PreviewSql();

        // Assert
        Assert.That(sql, Contains.Substring("Incomplete"));
    }

    #endregion

    #region Method Chaining Tests

    [Test]
    public void MethodChaining_AllMethodsReturnBuilder()
    {
        // Arrange & Act
        var breakdown = LinqQueryBreakdownBuilder
            .Create()
            .SelectColumns("Id")
            .FromTable("Users")
            .Where("Active = 1")
            .GroupBy("Department")
            .Having("COUNT(*) > 1")
            .OrderByAscending("Department")
            .Build();

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

    [Test]
    public void MethodChaining_CanBeInterrupted()
    {
        // Arrange
        var builder = LinqQueryBreakdownBuilder
            .Create()
            .SelectAll()
            .FromTable("Users");

        // Act
        var breakdown1 = builder.Build();
        var builder2 = LinqQueryBreakdownBuilder
            .Create()
            .SelectColumns("Id")
            .FromTable("Orders");
        var breakdown2 = builder2.Build();

        Assert.Multiple(() =>
        {
            // Assert
            Assert.That(breakdown1.FromClause.Clause, Is.EqualTo("Users"));
            Assert.That(breakdown2.FromClause.Clause, Is.EqualTo("Orders"));
        });
    }

    #endregion
}