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

using Strata.SqlTools.Breakdowns.Snowflake;
using Strata.SqlTools.Markdown.Snowflake;

namespace Strata.SqlTools.Markdown.Tests.Snowflake;

[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 = "Snowflake Query Report";

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

        // Assert
        Assert.That(report, Does.Contain(title));
        Assert.That(report, Does.Contain("# Snowflake 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("Snowflake Features"));
        Assert.That(report, Does.Contain("Parameter Analysis"));
        Assert.That(report, Does.Contain("Query Composition Report"));
    }

    [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"));
    }

    [Test]
    public void GenerateSnowflakeFeaturesAnalysis_WithStageReferences_ReportsUsage()
    {
        // Arrange
        var stageQuery = new QueryBreakdown("$1, $2", "@mystage/data.csv");
        _collection.Add(stageQuery);

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

        // Assert
        Assert.That(analysis, Does.Contain("Snowflake Features"));
        Assert.That(analysis, Does.Contain("Stage References"));
        Assert.That(analysis, Does.Contain("✓ Yes"));
    }

    [Test]
    public void GenerateSnowflakeFeaturesAnalysis_WithSemiStructuredData_ReportsUsage()
    {
        // Arrange
        var jsonQuery = new QueryBreakdown("JSON_EXTRACT(data, '$.id')", "raw_data");
        _collection.Add(jsonQuery);

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

        // Assert
        Assert.That(analysis, Does.Contain("Semi-Structured Data"));
        Assert.That(analysis, Does.Contain("✓ Yes"));
    }

    [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_ShowsSnowflakeSyntax()
    {
        // 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("@UserId"));  // Snowflake supports both
        Assert.That(analysis, Does.Contain("Parameter Dependency Diagram"));
    }

    [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"));
    }

    [Test]
    public void GenerateQueryCompositionReport_WithSnowflakeFeatures_IncludesFeatureInfo()
    {
        // Arrange
        var jsonQuery = new QueryBreakdown("JSON_EXTRACT(data, '$.id')", "raw_data");
        var stageQuery = new QueryBreakdown("$1, $2", "@mystage/data.csv");
        _collection.Add(jsonQuery);
        _collection.Add(stageQuery);

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

        // Assert
        Assert.That(report, Does.Contain("Snowflake Features"));
        Assert.That(report, Does.Contain("Semi-Structured Data"));
        Assert.That(report, Does.Contain("Stage References"));
    }

    [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_WithSessionSetup_IncludesSessionSetup()
    {
        // Arrange
        _collection.Add(new QueryBreakdown("ID, Name", "Users"));

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

        // Assert
        Assert.That(diagram, Does.Contain("Session Setup"));
    }

    [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_WithSessionSetup_ValidatesProperConnections()
    {
        // Arrange
        _collection.Add(new QueryBreakdown("ID, Name", "Users"));
        _collection.Add(new QueryBreakdown("OrderID", "Orders"));

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

        // Assert
        // Verify proper session setup flow
        Assert.That(diagram, Does.Contain("Start([Batch Start]) --> node0"));  // Start connects to Session Setup
        Assert.That(diagram, Does.Contain("node0[\"Session Setup\"]"));        // Session Setup node
        Assert.That(diagram, Does.Contain("node0 --> node1"));                 // Session Setup 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\"] --> End"));      // Last query 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("Snowflake Features"));
        Assert.That(report, Does.Contain("Total Queries"));
        Assert.That(report, Does.Contain("0"));
    }

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

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

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