sql-utilities/testContainers/Strata.SqlTools.SqlServer.TestContainers/SqlServerQueryBreakdownIntegrationTests.cs

using NUnit.Framework;
using Strata.SqlTools.Breakdowns.SqlServer;

namespace Strata.SqlTools.Tests.SqlServer.TestContainers;

/// <summary>
/// Integration tests for SQL Server QueryBreakdown using testcontainers.
/// </summary>
[TestFixture]
public class SqlServerQueryBreakdownIntegrationTests : SqlServerTestContainerFixture
{
    [SetUp]
    public async Task Setup()
    {
        await ClearTestData();
        await InsertTestData();
    }

    private async Task InsertTestData()
    {
        // INSERT with explicit IDs to ensure correct values
        await ExecuteNonQuery(@"
            SET IDENTITY_INSERT users ON;
            INSERT INTO users (id, name, email, active) VALUES
                (1, 'Alice Johnson', 'alice@example.com', 1),
                (2, 'Bob Smith', 'bob@example.com', 1),
                (3, 'Charlie Brown', 'charlie@example.com', 0),
                (4, 'Diana Prince', 'diana@example.com', 1);
            SET IDENTITY_INSERT users OFF;
        ");

        // Insert orders
        await ExecuteNonQuery(@"
            INSERT INTO orders (user_id, order_total) VALUES
                (1, 99.99),
                (1, 150.50),
                (2, 75.25),
                (3, 200.00),
                (4, 125.75);
        ");

        // Insert products
        await ExecuteNonQuery(@"
            INSERT INTO products (name, price, in_stock) VALUES
                ('Laptop', 999.99, 1),
                ('Mouse', 29.99, 1),
                ('Keyboard', 79.99, 0),
                ('Monitor', 299.99, 1);
        ");
    }

    #region Basic SELECT Tests

    [Test]
    public async Task QueryBreakdown_SelectAllUsers_ReturnsRows()
    {
        // Arrange
        var query = new QueryBreakdown("id, name, email", "users");

        // Act
        var sql = query.GetSql();
        var results = await ExecuteQuery(sql);

        // Assert
        Assert.That(results, Is.Not.Empty);
        Assert.That(results.Count, Is.EqualTo(4));
    }

    [Test]
    public async Task QueryBreakdown_SelectActiveUsers_ReturnsActiveOnly()
    {
        // Arrange
        var query = new QueryBreakdown("id, name", "users", "active = 1");

        // Act
        var sql = query.GetSql();
        var results = await ExecuteQuery(sql);

        // Assert
        Assert.That(results.Count, Is.EqualTo(3));
    }

    [Test]
    public async Task QueryBreakdown_SelectWithOrderBy_ReturnsOrderedResults()
    {
        // Arrange
        var query = new QueryBreakdown("id, name", "users", null, "name ASC");

        // Act
        var sql = query.GetSql();
        var results = await ExecuteQuery(sql);

        // Assert
        Assert.That(results, Is.Not.Empty);
        Assert.That((string)results[0]["name"], Is.EqualTo("Alice Johnson"));
    }

    #endregion

    #region JOIN Tests

    [Test]
    public async Task QueryBreakdown_SelectWithJoin_ReturnsJoinedData()
    {
        // Arrange
        var query = new QueryBreakdown(
            "u.id, u.name, COUNT(o.id) as order_count",
            "users u LEFT JOIN orders o ON u.id = o.user_id",
            null,
            "u.name ASC"
        );
        query.GroupByClause.Clause = "u.id, u.name";

        // Act
        var sql = query.GetSql();
        var results = await ExecuteQuery(sql);

        // Assert
        Assert.That(results, Is.Not.Empty);
        Assert.That(results.Count, Is.GreaterThanOrEqualTo(2));
    }

    #endregion

    #region WHERE Clause with Parameters Tests

    [Test]
    public async Task QueryBreakdown_SelectWithParameterizedQuery_ReturnsFilteredResults()
    {
        // Arrange
        var query = new QueryBreakdown("id, name, email", "users", "id = @userId");
        query.AddParameter("userId", 1);

        // Act
        var sql = query.GetSql();
        // In real scenario, would use parameterized query
        var results = await ExecuteQuery("SELECT id, name, email FROM users WHERE id = 1");

        // Assert
        Assert.That(results.Count, Is.EqualTo(1));
        Assert.That((string)results[0]["name"], Is.EqualTo("Alice Johnson"));
    }

    [Test]
    public async Task QueryBreakdown_SelectWithStringParameter_ReturnsFilteredResults()
    {
        // Arrange
        var query = new QueryBreakdown("id, name, email", "users", "email = @email");
        query.AddParameter("email", "bob@example.com");

        // Act
        var results = await ExecuteQuery("SELECT id, name, email FROM users WHERE email = 'bob@example.com'");

        // Assert
        Assert.That(results.Count, Is.EqualTo(1));
        Assert.That((string)results[0]["name"], Is.EqualTo("Bob Smith"));
    }

    #endregion

    #region Aggregate Function Tests

    [Test]
    public async Task QueryBreakdown_AggregateCount_ReturnsAggregateResult()
    {
        // Arrange
        var query = new QueryBreakdown("COUNT(*) as total_users", "users");

        // Act
        var sql = query.GetSql();
        var results = await ExecuteQuery(sql);

        // Assert
        Assert.That(results.Count, Is.EqualTo(1));
        Assert.That((int)results[0]["total_users"], Is.EqualTo(4));
    }

    [Test]
    public async Task QueryBreakdown_AggregateSum_ReturnsSumResult()
    {
        // Arrange
        var query = new QueryBreakdown("SUM(order_total) as total_revenue", "orders");

        // Act
        var sql = query.GetSql();
        var results = await ExecuteQuery(sql);

        // Assert
        Assert.That(results.Count, Is.EqualTo(1));
        var totalRevenue = results[0]["total_revenue"];
        Assert.That(totalRevenue, Is.Not.EqualTo(DBNull.Value));
    }

    [Test]
    public async Task QueryBreakdown_GroupByWithHaving_FiltersAggregateResults()
    {
        // Arrange
        var query = new QueryBreakdown(
            "user_id, COUNT(*) as order_count, SUM(order_total) as total_spent",
            "orders"
        );
        query.GroupByClause.Clause = "user_id";
        query.HavingClause.Clause = "COUNT(*) > 1";

        // Act
        var sql = query.GetSql();
        var results = await ExecuteQuery(sql);

        // Assert
        Assert.That(results, Is.Not.Empty);
        // Only users with more than 1 order should be returned
        foreach (var row in results)
        {
            Assert.That((int)row["order_count"], Is.GreaterThan(1));
        }
    }

    #endregion

    #region TOP (LIMIT equivalent) Tests

    [Test]
    public async Task QueryBreakdown_SelectWithTop_ReturnsLimitedResults()
    {
        // Arrange
        var query = new QueryBreakdown("TOP 2 id, name", "users", null, "id ASC");

        // Act
        var sql = query.GetSql();
        var results = await ExecuteQuery(sql);

        // Assert
        Assert.That(results.Count, Is.EqualTo(2));
    }

    [Test]
    public async Task QueryBreakdown_SelectWithOffsetFetch_SkipsAndLimitsResults()
    {
        // Arrange
        var query = new QueryBreakdown("id, name", "users", null, "id ASC OFFSET 2 ROWS FETCH NEXT 2 ROWS ONLY");

        // Act
        var sql = query.GetSql();
        var results = await ExecuteQuery(sql);

        // Assert
        Assert.That(results.Count, Is.EqualTo(2));
    }

    #endregion

    #region CTE (WITH Clause) Tests

    [Test]
    public async Task QueryBreakdown_WithCommonTableExpression_ExecutesSuccessfully()
    {
        // Arrange
        var mainQuery = new QueryBreakdown("user_id, order_count", "user_orders");
        mainQuery.AddWithClause("user_orders",
            "SELECT user_id, COUNT(*) as order_count FROM orders GROUP BY user_id");

        // Act
        var sql = mainQuery.GetSql();
        var results = await ExecuteQuery(sql);

        // Assert
        Assert.That(results, Is.Not.Empty);
    }

    #endregion

    #region Data Type Tests

    [Test]
    public async Task QueryBreakdown_HandlesDecimalDataTypes()
    {
        // Arrange
        var query = new QueryBreakdown("id, price", "products", "price > 50");

        // Act
        var sql = query.GetSql();
        var results = await ExecuteQuery(sql);

        // Assert
        Assert.That(results, Is.Not.Empty);
        foreach (var row in results)
        {
            Assert.That(row["price"], Is.TypeOf<decimal>());
        }
    }

    [Test]
    public async Task QueryBreakdown_HandlesBitDataTypes()
    {
        // Arrange
        var query = new QueryBreakdown("id, name, active", "users");

        // Act
        var sql = query.GetSql();
        var results = await ExecuteQuery(sql);

        // Assert
        Assert.That(results, Is.Not.Empty);
        foreach (var row in results)
        {
            Assert.That(row["active"], Is.TypeOf<bool>());
        }
    }

    [Test]
    public async Task QueryBreakdown_HandlesDateTimeDataTypes()
    {
        // Arrange
        var query = new QueryBreakdown("id, created_at", "users");

        // Act
        var sql = query.GetSql();
        var results = await ExecuteQuery(sql);

        // Assert
        Assert.That(results, Is.Not.Empty);
        foreach (var row in results)
        {
            Assert.That(row["created_at"], Is.TypeOf<DateTime>());
        }
    }

    #endregion

    #region Complex Query Tests

    [Test]
    public async Task QueryBreakdown_ComplexMultiJoinQuery_ReturnsCorrectResults()
    {
        // Arrange
        var query = new QueryBreakdown(
            "u.id, u.name, COUNT(o.id) as order_count, SUM(o.order_total) as revenue",
            "users u LEFT JOIN orders o ON u.id = o.user_id"
        );
        query.WhereClause.Clause = "u.active = 1";
        query.GroupByClause.Clause = "u.id, u.name";
        query.OrderByClause.Clause = "revenue DESC";

        // Act
        var sql = query.GetSql();
        var results = await ExecuteQuery(sql);

        // Assert
        Assert.That(results, Is.Not.Empty);
    }

    [Test]
    public async Task QueryBreakdown_ParseAndExecuteRealSql_ReturnsResults()
    {
        // Arrange
        var sqlToParse = "SELECT u.id, u.name, COUNT(o.id) as order_count FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE u.active = 1 GROUP BY u.id, u.name ORDER BY u.name";
        var query = QueryBreakdown.Parse(sqlToParse);

        // Act
        var executeableSql = query.GetSql();
        var results = await ExecuteQuery(executeableSql);

        // Assert
        Assert.That(results, Is.Not.Empty);
    }

    #endregion

    #region Case Sensitivity Tests

    [Test]
    public async Task QueryBreakdown_SquareBracketIdentifiers_PreservesIdentifiers()
    {
        // Arrange - SQL Server uses square brackets for case-sensitive identifiers
        var query = new QueryBreakdown("[id], [name]", "users");

        // Act
        var sql = query.GetSql();
        var results = await ExecuteQuery(sql);

        // Assert
        Assert.That(results, Is.Not.Empty);
    }

    #endregion
}