sql-utilities/testContainers/Strata.SqlTools.PostgreSql.TestContainers/PostgreSqlQueryBreakdownIntegrationTests.cs

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

namespace Strata.SqlTools.Tests.PostgreSql.TestContainers;

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

    private async Task InsertTestData()
    {
        if (DataSource == null)
            return;

        using var command = DataSource.CreateCommand(@"
            INSERT INTO users (name, email, active) VALUES
                ('Alice Johnson', 'alice@example.com', true),
                ('Bob Smith', 'bob@example.com', true),
                ('Charlie Brown', 'charlie@example.com', false),
                ('Diana Prince', 'diana@example.com', true);

            INSERT INTO orders (user_id, order_total) VALUES
                (1, 99.99),
                (1, 150.50),
                (2, 75.25),
                (3, 200.00),
                (4, 125.75);

            INSERT INTO products (name, price, in_stock) VALUES
                ('Laptop', 999.99, true),
                ('Mouse', 29.99, true),
                ('Keyboard', 79.99, false),
                ('Monitor', 299.99, true);
        ");

        await command.ExecuteNonQueryAsync();
    }

    #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 = true");

        // 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 = $1");
        query.AddParameter("user_id", 1);

        // Act
        var sql = query.GetSql();
        // In real scenario, would use parameterized query with the actual parameter value
        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 = $1");
        query.AddParameter("email", "bob@example.com");

        // Act
        // Execute with actual parameter substitution
        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((long)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.Null);
    }

    [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((long)row["order_count"], Is.GreaterThan(1));
        }
    }

    #endregion

    #region LIMIT Tests

    [Test]
    public async Task QueryBreakdown_SelectWithLimit_ReturnsLimitedResults()
    {
        // Arrange - LIMIT is appended to ORDER BY clause
        var query = new QueryBreakdown("id, name", "users", null, "id ASC LIMIT 2");

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

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

    [Test]
    public async Task QueryBreakdown_SelectWithLimitAndOffset_SkipsAndLimitsResults()
    {
        // Arrange - OFFSET and LIMIT in ORDER BY clause
        var query = new QueryBreakdown("id, name", "users", null, "id ASC LIMIT 2 OFFSET 2");

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

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

    #endregion

    #region Case Sensitivity Tests

    [Test]
    public async Task QueryBreakdown_DoubleQuotedIdentifiers_PreservesCaseSensitivity()
    {
        // Arrange - PostgreSQL preserves case in double-quoted identifiers
        var query = new QueryBreakdown("\"id\", \"name\"", "users");

        // Act
        var sql = query.GetSql();
        // Should not fail due to case mismatch
        var results = await ExecuteQuery(sql);

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

    #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 = true";
        query.GroupByClause.Clause = "u.id, u.name";
        query.OrderByClause.Clause = "revenue DESC NULLS LAST";

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

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

    [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 = true 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 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_HandlesBooleanDataTypes()
    {
        // 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_HandlesTimestampDataTypes()
    {
        // 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
}