# Strata.SqlTools.Markdown

**SQL Query Visualization with Mermaid Diagrams**

---

## Overview

The `Strata.SqlTools.Markdown` package provides comprehensive visualization tools for SQL queries using Mermaid diagrams. It generates flowcharts, sequence diagrams, entity-relationship diagrams, and specialized visualizations for different SQL dialects.

### Supported Dialects

- ✅ **SQL Server** - T-SQL query visualization
- ✅ **PostgreSQL** - PostgreSQL query visualization with parameter analysis
- ✅ **Snowflake** - Snowflake query visualization
- ✅ **LINQ to SQL** - LINQ expression tree and execution pipeline visualization

---

## Installation

```bash
dotnet add package Strata.SqlTools.Markdown
```

**Dependencies:**
- `Strata.SqlTools` (core)
- `Strata.SqlTools.SqlServer` (for SQL Server visualizations)
- `Strata.SqlTools.PostgreSql` (for PostgreSQL visualizations)
- `Strata.SqlTools.Snowflake` (for Snowflake visualizations)
- `Strata.SqlTools.LinqToSql` (for LINQ visualizations)

---

## Quick Start

### Basic Query Diagram

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

var breakdown = new QueryBreakdown(@"
    SELECT id, name, email
    FROM users
    WHERE age > @minAge
    ORDER BY name ASC
");

var generator = new QueryBreakdownGenerator();
string diagram = generator.GenerateMermaidDiagram(breakdown, "User Query");

Console.WriteLine(diagram);
```

**Output:**

````markdown
### User Query

```mermaid
flowchart TD
    Start([Start]) --> Select[SELECT id, name, email]
    Select --> From[FROM users]
    From --> Where[WHERE age > @minAge]
    Where --> OrderBy[ORDER BY name ASC]
    OrderBy --> End([End])
```
````

---

## SQL Server Visualizations

### QueryBreakdownGenerator

Generate flowchart diagrams showing query structure:

```csharp
using Strata.SqlTools.Markdown.SqlServer;

var generator = new QueryBreakdownGenerator();

// Generate basic flowchart
string diagram = generator.GenerateMermaidDiagram(breakdown, "Query Structure");

// Generate with CTE
var breakdown = new QueryBreakdown("*", "cte_result");
breakdown.AddWithClause("cte_result", subquery);
string cteDiagram = generator.GenerateMermaidDiagram(breakdown, "CTE Query");
```

### SqlStatementGenerator

Generate sequence and ER diagrams:

```csharp
var stmtGenerator = new SqlStatementGenerator();

// Sequence diagram showing execution flow
string sequence = stmtGenerator.GenerateSequenceDiagram(breakdown, "Execution");

// Entity-relationship diagram
var tables = new[] { "users", "orders", "products" };
string erDiagram = stmtGenerator.GenerateEntityRelationshipDiagram(tables, "Schema");
```

---

## PostgreSQL Visualizations

### QueryBreakdownGenerator

PostgreSQL-specific visualization with parameter tracking:

```csharp
using Strata.SqlTools.Breakdowns.PostgreSql;
using Strata.SqlTools.Markdown.PostgreSql;

var breakdown = new QueryBreakdown(@"
    SELECT u.id, u.name, o.total
    FROM users u
    JOIN orders o ON u.id = o.user_id
    WHERE u.age > $1
    ORDER BY o.total DESC
");

var generator = new QueryBreakdownGenerator();
string diagram = generator.GenerateMermaidDiagram(breakdown, "User Orders");
```

### QueryBreakdownCollectionGenerator

Visualize collections of queries:

```csharp
using Strata.SqlTools.Markdown.PostgreSql;

var collection = new QueryBreakdownCollection();
collection.Add(new QueryBreakdown("SELECT * FROM users WHERE age > $1"));
collection.Add(new QueryBreakdown("SELECT * FROM orders WHERE status = $1"));

var collectionGen = new QueryBreakdownCollectionGenerator();

// Generate summary with all diagrams
string summary = collectionGen.GenerateCollectionSummary(collection, "All Queries");

// Generate parameter usage diagram
string paramDiagram = collectionGen.GenerateParameterUsageDiagram(collection);

// Generate table reference diagram
string tableDiagram = collectionGen.GenerateTableReferenceDiagram(collection);
```

**Example Parameter Usage Diagram:**

```mermaid
graph LR
    Q1[Query 1] --> P1[$1]
    Q2[Query 2] --> P1
    Q1 --> P2[$2]

    style P1 fill:#e1f5ff
    style P2 fill:#e1f5ff
```

---

## Snowflake Visualizations

### QueryBreakdownGenerator

Snowflake-specific query visualization:

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

var breakdown = new QueryBreakdown(@"
    SELECT *
    FROM database.schema.table
    WHERE created_at > :start_date
    LIMIT 100
");

var generator = new QueryBreakdownGenerator();
string diagram = generator.GenerateMermaidDiagram(breakdown, "Snowflake Query");
```

---

## LINQ to SQL Visualizations

### LINQ Method Chain Diagrams

Visualize LINQ query method chains:

```csharp
using Strata.SqlTools.Breakdowns.LinqToSql;
using Strata.SqlTools.Markdown.LinqToSql;

var query = context.Users
    .Where(u => u.Age > 21)
    .OrderBy(u => u.Name)
    .Select(u => new { u.Id, u.Name });

var breakdown = LinqQueryBreakdown.Analyze(query);
var generator = new QueryBreakdownGenerator();

// Generate method chain diagram
string methodChain = generator.GenerateMethodChainDiagram(breakdown, "LINQ Flow");
```

**Output:**

```mermaid
flowchart LR
    Start[IQueryable] --> Where[Where]
    Where --> OrderBy[OrderBy]
    OrderBy --> Select[Select]
    Select --> Result[Result]
```

### LINQ Execution Pipeline

Visualize how LINQ translates to SQL:

```csharp
var sqlGenerator = new SqlStatementGenerator();
string pipeline = sqlGenerator.GenerateLinqPipelineDiagram(breakdown, "Execution Pipeline");
```

**Output:**

```mermaid
sequenceDiagram
    participant Client as Client Application
    participant LINQ as LINQ Provider
    participant ET as Expression Tree
    participant SQL as SQL Generator
    participant DB as Database

    Client->>LINQ: LINQ Query
    activate LINQ
    LINQ->>ET: Where Predicate
    activate ET
    LINQ->>ET: Select Projection
    ET->>SQL: Expression Tree
    deactivate ET
    SQL->>DB: Generate SQL
    activate DB
    DB-->>SQL: Result Set
    deactivate DB
    SQL-->>LINQ: Mapped Objects
    LINQ-->>Client: IEnumerable Result
    deactivate LINQ
```

### Combined Diagrams

Show both method chain and SQL structure:

```csharp
string combined = generator.GenerateCombinedDiagram(breakdown, "Full Analysis");
```

---

## Advanced Features

### Custom Diagram Titles

```csharp
// With title
string diagram = generator.GenerateMermaidDiagram(breakdown, "My Custom Title");

// Without title
string diagram = generator.GenerateMermaidDiagram(breakdown, null);
```

### Nested CTEs Visualization

```csharp
var mainQuery = new QueryBreakdown("*", "cte2");
var cte1 = new QueryBreakdown("id, name", "users");
var cte2 = new QueryBreakdown("*", "cte1");

mainQuery.AddWithClause("cte1", cte1);
mainQuery.AddWithClause("cte2", cte2);

var generator = new QueryBreakdownGenerator();
string diagram = generator.GenerateMermaidDiagram(mainQuery, "Nested CTEs");
```

### Collection Statistics

```csharp
var collectionGen = new QueryBreakdownCollectionGenerator();
var collection = new QueryBreakdownCollection();
// ... add queries ...

// Generate statistics table
string stats = $@"
## Query Statistics

- Total Queries: {collection.Count}
- Total Selected Columns: {collection.GetTotalSelectedColumns()}
- Unique Tables: {string.Join(", ", collection.GetUniqueTableReferences())}

{collectionGen.GenerateCollectionSummary(collection, "Query Details")}
";
```

---

## Integration with Documentation Tools

### Markdown File Generation

```csharp
public class QueryDocumentationGenerator
{
    public void GenerateDocumentation(string outputPath)
    {
        var sb = new StringBuilder();
        sb.AppendLine("# Database Queries Documentation");
        sb.AppendLine();

        var queries = GetAllQueries(); // Your query collection
        var generator = new QueryBreakdownGenerator();

        foreach (var (name, breakdown) in queries)
        {
            sb.AppendLine($"## {name}");
            sb.AppendLine();
            sb.AppendLine($"**SQL:**");
            sb.AppendLine("```sql");
            sb.AppendLine(breakdown.GetSql());
            sb.AppendLine("```");
            sb.AppendLine();
            sb.AppendLine(generator.GenerateMermaidDiagram(breakdown, $"{name} Flow"));
            sb.AppendLine();
        }

        File.WriteAllText(outputPath, sb.ToString());
    }
}
```

### GitHub Pages / Wikis

The generated Mermaid diagrams work seamlessly with:
- **GitHub** - Renders Mermaid in README.md and wiki pages
- **GitLab** - Full Mermaid support in markdown
- **Azure DevOps** - Mermaid support in wiki
- **Docusaurus** - With mermaid plugin
- **MkDocs** - With mermaid2 plugin

---

## Diagram Customization

### Flowchart Styles

The generators use standard Mermaid syntax. You can customize by modifying the output:

```csharp
string diagram = generator.GenerateMermaidDiagram(breakdown, "Styled Query");

// Add custom styling
diagram = diagram.Replace("```mermaid", @"```mermaid
%%{init: {'theme':'forest'}}%%");

// Or add classDefs
diagram = diagram.Replace("```", @"
    classDef selectClass fill:#bbf,stroke:#333,stroke-width:2px
    classDef whereClass fill:#fbf,stroke:#333,stroke-width:2px
```");
```

### Sequence Diagram Themes

```csharp
string sequence = stmtGenerator.GenerateSequenceDiagram(breakdown, "Execution");

// Add theme
sequence = sequence.Replace("sequenceDiagram", @"%%{init: {'theme':'dark'}}%%
sequenceDiagram");
```

---

## Common Use Cases

### 1. API Documentation

```csharp
/// <summary>
/// Gets active users ordered by name.
/// </summary>
/// <remarks>
/// Query Details:
/// <code>
/// var generator = new QueryBreakdownGenerator();
/// var breakdown = new QueryBreakdown("SELECT * FROM users WHERE is_active = 1");
/// Console.WriteLine(generator.GenerateMermaidDiagram(breakdown, "Active Users"));
/// </code>
/// </remarks>
public async Task<List<User>> GetActiveUsers()
{
    // Implementation
}
```

### 2. Code Review Documentation

```csharp
// Generate before/after diagrams for query optimization
var beforeBreakdown = new QueryBreakdown(originalQuery);
var afterBreakdown = new QueryBreakdown(optimizedQuery);

var generator = new QueryBreakdownGenerator();

File.WriteAllText("query-comparison.md", $@"
# Query Optimization Results

## Before
{generator.GenerateMermaidDiagram(beforeBreakdown, "Original Query")}

## After
{generator.GenerateMermaidDiagram(afterBreakdown, "Optimized Query")}

## Improvements
- Reduced number of JOINs
- Added index on filtered column
- Removed SELECT *
");
```

### 3. Testing Documentation

```csharp
[Test]
public void ComplexQuery_GeneratesDiagram()
{
    var breakdown = BuildComplexQuery();
    var generator = new QueryBreakdownGenerator();
    string diagram = generator.GenerateMermaidDiagram(breakdown);

    // Save diagram for test documentation
    TestContext.WriteLine(diagram);

    // Assert query properties
    Assert.That(breakdown.WhereClause, Is.Not.Null);
}
```

---

## Best Practices

### 1. Use Descriptive Titles

```csharp
// Good: Descriptive title
generator.GenerateMermaidDiagram(breakdown, "Active Users by Department");

// Avoid: Generic title
generator.GenerateMermaidDiagram(breakdown, "Query 1");
```

### 2. Generate Diagrams for Complex Queries Only

```csharp
// Generate diagrams for queries with multiple clauses
if (breakdown.GetClauses().Count() > 3)
{
    string diagram = generator.GenerateMermaidDiagram(breakdown, queryName);
    SaveDiagram(diagram);
}
```

### 3. Include SQL Alongside Diagrams

```markdown
## User Query

**SQL:**
```sql
SELECT id, name, email
FROM users
WHERE age > 21
ORDER BY name
```

**Flow:**
[Mermaid diagram here]
```

---

## Troubleshooting

### Diagram Not Rendering

**Problem**: Mermaid diagram shows as plain text

**Solution**: Ensure your markdown viewer supports Mermaid:
- GitHub: Native support ✅
- VS Code: Install "Markdown Preview Mermaid Support" extension
- Local rendering: Use `mermaid-cli` or online editors

### Diagram Too Complex

**Problem**: Large queries create cluttered diagrams

**Solution**: Break into smaller sections or use collection generator:

```csharp
// Instead of one large diagram, generate multiple focused diagrams
var generator = new QueryBreakdownGenerator();

// Main query flow
string mainFlow = generator.GenerateMermaidDiagram(mainQuery, "Main Query");

// CTE flows separately
foreach (var cte in mainQuery.WithClauses)
{
    string cteFlow = generator.GenerateMermaidDiagram(cte.Value, $"CTE: {cte.Key}");
}
```

---

## API Reference

### Generator Classes by Dialect

| Namespace | Generator Classes |
|-----------|------------------|
| `Strata.SqlTools.Markdown.SqlServer` | `QueryBreakdownGenerator`, `SqlStatementGenerator` |
| `Strata.SqlTools.Markdown.PostgreSql` | `QueryBreakdownGenerator`, `SqlStatementGenerator`, `QueryBreakdownCollectionGenerator` |
| `Strata.SqlTools.Markdown.Snowflake` | `QueryBreakdownGenerator`, `SqlStatementGenerator` |
| `Strata.SqlTools.Markdown.LinqToSql` | `QueryBreakdownGenerator`, `SqlStatementGenerator` |

### Common Methods

All `QueryBreakdownGenerator` classes provide:

```csharp
string GenerateMermaidDiagram(breakdown, title?)     // Main flowchart diagram
```

All `SqlStatementGenerator` classes provide:

```csharp
string GenerateSequenceDiagram(breakdown, title?)                    // Execution sequence
string GenerateEntityRelationshipDiagram(tables/breakdown, title?)  // ER diagram
```

---

## Related Documentation

- [SqlUtilities.SqlServer.md](SqlUtilities.SqlServer.md) - SQL Server query breakdown
- [SqlUtilities.PostgreSql.md](SqlUtilities.PostgreSql.md) - PostgreSQL query breakdown
- [SqlUtilities.Snowflake.md](SqlUtilities.Snowflake.md) - Snowflake query breakdown
- [SqlUtilities.LinqToSql.md](SqlUtilities.LinqToSql.md) - LINQ query analysis

---

**Version**: 1.0.0
**Last Updated**: February 2026
**Package**: Strata.SqlTools.Markdown