sql-utilities/tests/Strata.SqlTools.Markdown.Tests/SqlServer/QueryBreakdownCollectionGeneratorTests.cs

using Strata.SqlTools.Breakdowns.SqlServer;
using Strata.SqlTools.Markdown.SqlServer;

namespace Strata.SqlTools.Markdown.Tests.SqlServer;

[TestFixture]
public class QueryBreakdownCollectionGeneratorTests
{
    private QueryBreakdownCollection _collection = null!;

    [SetUp]
    public void Setup()
    {
        _collection = new QueryBreakdownCollection();
    }

    [Test]
    public void GenerateCollectionReport_WithTitle_IncludesTitle()
    {
        // Arrange
        _collection.Add(new QueryBreakdown("ID, Name", "Users"));
        var title = "User Query Report";

        // Act
        var report = QueryBreakdownCollectionGenerator.GenerateCollectionReport(_collection, title);

        // Assert
        Assert.That(report, Does.Contain(title));
        Assert.That(report, Does.Contain("# User Query Report"));
    }

    [Test]
    public void GenerateCollectionReport_WithMultipleQueries_GeneratesCompleteReport()
    {
        // Arrange
        _collection.Add(new QueryBreakdown("ID, Name, Email", "Users"));
        _collection.Add(new QueryBreakdown("OrderID, Amount", "Orders"));

        // Act
        var report = QueryBreakdownCollectionGenerator.GenerateCollectionReport(_collection);

        // Assert
        Assert.That(report, Does.Contain("Collection Summary"));
        Assert.That(report, Does.Contain("Parameter Analysis"));
        Assert.That(report, Does.Contain("Query Composition Report"));
        Assert.That(report, Does.Contain("Total Queries"));
    }

    [Test]
    public void GenerateCollectionSummary_WithQueries_IncludesSummaryMetrics()
    {
        // Arrange
        _collection.Add(new QueryBreakdown("ID, Name, Email", "Users"));
        _collection.Add(new QueryBreakdown("OrderID, Amount", "Orders"));

        // Act
        var summary = QueryBreakdownCollectionGenerator.GenerateCollectionSummary(_collection);

        // Assert
        Assert.That(summary, Does.Contain("Collection Summary"));
        Assert.That(summary, Does.Contain("Total Queries"));
        Assert.That(summary, Does.Contain("2"));
        Assert.That(summary, Does.Contain("Unique Tables"));
    }

    [Test]
    public void GenerateParameterAnalysis_NoParameters_IndicatesNoParameters()
    {
        // Arrange
        _collection.Add(new QueryBreakdown("ID, Name", "Users"));

        // Act
        var analysis = QueryBreakdownCollectionGenerator.GenerateParameterAnalysis(_collection);

        // Assert
        Assert.That(analysis, Does.Contain("Parameters"));
        Assert.That(analysis, Does.Contain("No parameters"));
    }

    [Test]
    public void GenerateParameterAnalysis_WithParameters_IncludesParameterTable()
    {
        // Arrange
        var query = new QueryBreakdown("ID, Name", "Users", "ID = @UserId");
        query.Parameters["UserId"] = 123;
        _collection.Add(query);

        // Act
        var analysis = QueryBreakdownCollectionGenerator.GenerateParameterAnalysis(_collection);

        // Assert
        Assert.That(analysis, Does.Contain("Parameter Analysis"));
        Assert.That(analysis, Does.Contain("@UserId"));
        Assert.That(analysis, Does.Contain("Parameter Dependency Diagram"));
        Assert.That(analysis, Does.Contain("```mermaid"));
    }

    [Test]
    public void GenerateParameterDependencyDiagram_WithParameters_GeneratesMermaidDiagram()
    {
        // Arrange
        var query1 = new QueryBreakdown("ID, Name", "Users", "ID = @UserId");
        var query2 = new QueryBreakdown("OrderID", "Orders", "UserID = @UserId");
        query1.Parameters["UserId"] = 123;
        query2.Parameters["UserId"] = 123;
        _collection.Add(query1);
        _collection.Add(query2);

        // Act
        var diagram = QueryBreakdownCollectionGenerator.GenerateParameterDependencyDiagram(_collection);

        // Assert
        Assert.That(diagram, Does.Contain("```mermaid"));
        Assert.That(diagram, Does.Contain("graph TD"));
        Assert.That(diagram, Does.Contain("@UserId"));
        Assert.That(diagram, Does.Contain("Query #0"));
        Assert.That(diagram, Does.Contain("Query #1"));
        Assert.That(diagram, Does.Contain("```"));
    }

    [Test]
    public void GenerateQueryCompositionReport_WithQueries_IncludesAllQueryDetails()
    {
        // Arrange
        var query = new QueryBreakdown("ID, Name", "Users", "ID > 10");
        query.GroupByClause.Clause = "Region";
        query.OrderByClause.Clause = "Name ASC";
        _collection.Add(query);

        // Act
        var report = QueryBreakdownCollectionGenerator.GenerateQueryCompositionReport(_collection);

        // Assert
        Assert.That(report, Does.Contain("Query Composition Report"));
        Assert.That(report, Does.Contain("Query #0"));
        Assert.That(report, Does.Contain("SELECT Clause"));
        Assert.That(report, Does.Contain("✓ Yes"));
        Assert.That(report, Does.Contain("WHERE Clause"));
        Assert.That(report, Does.Contain("GROUP BY"));
        Assert.That(report, Does.Contain("ORDER BY"));
    }

    [Test]
    public void GenerateQueryCompositionReport_WithCTEs_IncludesCTEList()
    {
        // Arrange
        var query = new QueryBreakdown("ID, Name", "Users");
        var cteQuery = new QueryBreakdown("ID, Name", "ArchiveUsers");
        query.AddWithClause("ActiveUsers", cteQuery);
        _collection.Add(query);

        // Act
        var report = QueryBreakdownCollectionGenerator.GenerateQueryCompositionReport(_collection);

        // Assert
        Assert.That(report, Does.Contain("CTEs Defined"));
        Assert.That(report, Does.Contain("ActiveUsers"));
    }

    [Test]
    public void GenerateQueryCompositionReport_WithParameters_IncludesParameterValues()
    {
        // Arrange
        var query = new QueryBreakdown("ID, Name", "Users", "ID = @UserId AND Status = @Status");
        query.Parameters["UserId"] = 123;
        query.Parameters["Status"] = "Active";
        _collection.Add(query);

        // Act
        var report = QueryBreakdownCollectionGenerator.GenerateQueryCompositionReport(_collection);

        // Assert
        Assert.That(report, Does.Contain("Parameters Used"));
        Assert.That(report, Does.Contain("@UserId"));
        Assert.That(report, Does.Contain("@Status"));
        Assert.That(report, Does.Contain("123"));
        Assert.That(report, Does.Contain("Active"));
    }

    [Test]
    public void GenerateBatchFlowDiagram_WithQueries_GeneratesFlowDiagram()
    {
        // Arrange
        _collection.Add(new QueryBreakdown("ID, Name", "Users"));
        _collection.Add(new QueryBreakdown("OrderID", "Orders"));

        // Act
        var diagram = QueryBreakdownCollectionGenerator.GenerateBatchFlowDiagram(_collection);

        // Assert
        Assert.That(diagram, Does.Contain("```mermaid"));
        Assert.That(diagram, Does.Contain("flowchart TD"));
        Assert.That(diagram, Does.Contain("Batch Start"));
        Assert.That(diagram, Does.Contain("Query 0"));
        Assert.That(diagram, Does.Contain("Query 1"));
        Assert.That(diagram, Does.Contain("Batch Complete"));
        Assert.That(diagram, Does.Contain("```"));
    }

    [Test]
    public void GenerateBatchFlowDiagram_WithTransaction_IncludesTransactionStatements()
    {
        // Arrange
        _collection.Add(new QueryBreakdown("ID, Name", "Users"));

        // Act
        var diagram = QueryBreakdownCollectionGenerator.GenerateBatchFlowDiagram(_collection, includeTransaction: true);

        // Assert
        Assert.That(diagram, Does.Contain("BEGIN TRANSACTION"));
        Assert.That(diagram, Does.Contain("COMMIT TRANSACTION"));
    }

    [Test]
    public void GenerateBatchFlowDiagram_WithQueries_ValidatesProperConnections()
    {
        // Arrange
        _collection.Add(new QueryBreakdown("ID, Name", "Users"));
        _collection.Add(new QueryBreakdown("OrderID", "Orders"));
        _collection.Add(new QueryBreakdown("ProductID", "Products"));

        // Act
        var diagram = QueryBreakdownCollectionGenerator.GenerateBatchFlowDiagram(_collection);

        // Assert
        // Verify proper connections between nodes
        Assert.That(diagram, Does.Contain("Start([Batch Start]) --> node0"));  // Start connects to first node
        Assert.That(diagram, Does.Contain("node0[\"Query 0\"] --> node1"));     // Query 0 to Query 1
        Assert.That(diagram, Does.Contain("node1[\"Query 1\"] --> node2"));     // Query 1 to Query 2
        Assert.That(diagram, Does.Contain("node2[\"Query 2\"] --> End"));       // Last query connects to End
        // Verify no dangling nodes
        Assert.That(diagram, Does.Not.Contain("node3"));                        // No extra unconnected node
    }

    [Test]
    public void GenerateBatchFlowDiagram_WithTransaction_ValidatesProperConnections()
    {
        // Arrange
        _collection.Add(new QueryBreakdown("ID, Name", "Users"));
        _collection.Add(new QueryBreakdown("OrderID", "Orders"));

        // Act
        var diagram = QueryBreakdownCollectionGenerator.GenerateBatchFlowDiagram(_collection, includeTransaction: true);

        // Assert
        // Verify proper transaction flow
        Assert.That(diagram, Does.Contain("Start([Batch Start]) --> node0"));  // Start connects to BEGIN TRANSACTION
        Assert.That(diagram, Does.Contain("node0[\"BEGIN TRANSACTION\"]"));    // BEGIN TRANSACTION node
        Assert.That(diagram, Does.Contain("node0 --> node1"));                 // BEGIN to first query
        Assert.That(diagram, Does.Contain("node1[\"Query 0\"] --> node2"));    // Query 0 to Query 1
        Assert.That(diagram, Does.Contain("node2[\"Query 1\"] --> node3"));    // Query 1 to COMMIT
        Assert.That(diagram, Does.Contain("node3[\"COMMIT TRANSACTION\"]"));   // COMMIT node
        Assert.That(diagram, Does.Contain("node3 --> End"));                  // COMMIT to End
    }

    [Test]
    public void GenerateCollectionReport_EmptyCollection_GeneratesValidMarkdown()
    {
        // Act
        var report = QueryBreakdownCollectionGenerator.GenerateCollectionReport(_collection);

        // Assert
        Assert.That(report, Does.Contain("Collection Summary"));
        Assert.That(report, Does.Contain("Total Queries"));
        Assert.That(report, Does.Contain("0"));
    }

    [Test]
    public void GenerateCollectionReport_MultipleQueries_ContainsAllSections()
    {
        // Arrange
        for (int i = 0; i < 3; i++)
        {
            _collection.Add(new QueryBreakdown($"Col{i}", $"Table{i}"));
        }

        // Act
        var report = QueryBreakdownCollectionGenerator.GenerateCollectionReport(_collection);

        // Assert
        Assert.That(report, Does.Contain("## Collection Summary"));
        Assert.That(report, Does.Contain("## Parameter Analysis"));
        Assert.That(report, Does.Contain("## Query Composition Report"));
    }
}