<#
.SYNOPSIS
    DBAOps Schema Documentation Generator
    Alternative to SchemaSpy - Native PowerShell/SQL Server solution
    
.DESCRIPTION
    Generates HTML schema documentation for SQL Server databases including:
    - Table listings with row counts
    - Column details with data types
    - Primary keys and indexes
    - Foreign key relationships
    - Views, stored procedures, functions
    - Interactive navigation
    
.PARAMETER ServerName
    SQL Server instance name (default: uses CMS)
    
.PARAMETER DatabaseName
    Specific database or 'ALL' for all user databases
    
.PARAMETER OutputPath
    Path for generated documentation (default: E:\DBAOps\WebDashboard\SchemaDocs)
    
.EXAMPLE
    .\New-SchemaDocumentation.ps1 -ServerName "AZAPPDEVSQL19" -DatabaseName "ALL"
    
.EXAMPLE
    .\New-SchemaDocumentation.ps1 -DatabaseName "DBAOpsRepository"
    
.NOTES
    Author: DBAOps Team
    Date: January 19, 2026
    Version: 1.0
    
    Replaces SchemaSpy with native SQL Server solution
    No Java or external dependencies required
#>

[CmdletBinding()]
param(
    [Parameter()]
    [string]$ServerName,
    
    [Parameter()]
    [string]$DatabaseName = "ALL",
    
    [Parameter()]
    [string]$OutputPath = "E:\DBAOps\WebDashboard\SchemaDocs",
    
    [Parameter()]
    [string]$CMSServer = "YOURSERVER01",
    
    [Parameter()]
    [switch]$IncludeSystemDatabases,
    
    [Parameter()]
    [switch]$GenerateIndex
)

$ErrorActionPreference = "Stop"

# ============================================================================
# HTML Templates
# ============================================================================

$CSSStyles = @"
<style>
    :root {
        --primary: #1F4E79;
        --secondary: #2E75B6;
        --success: #28a745;
        --warning: #ffc107;
        --danger: #dc3545;
        --light: #f8f9fa;
        --dark: #343a40;
    }
    
    * { box-sizing: border-box; margin: 0; padding: 0; }
    
    body {
        font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
        line-height: 1.6;
        color: #333;
        background: #f5f5f5;
    }
    
    .container {
        max-width: 1400px;
        margin: 0 auto;
        padding: 20px;
    }
    
    header {
        background: linear-gradient(135deg, var(--primary), var(--secondary));
        color: white;
        padding: 20px;
        margin-bottom: 20px;
        border-radius: 8px;
        box-shadow: 0 2px 10px rgba(0,0,0,0.1);
    }
    
    header h1 { font-size: 24px; margin-bottom: 5px; }
    header .subtitle { opacity: 0.9; font-size: 14px; }
    
    .stats-grid {
        display: grid;
        grid-template-columns: repeat(auto-fit, minmax(150px, 1fr));
        gap: 15px;
        margin-bottom: 20px;
    }
    
    .stat-card {
        background: white;
        padding: 15px;
        border-radius: 8px;
        text-align: center;
        box-shadow: 0 2px 5px rgba(0,0,0,0.05);
    }
    
    .stat-card .value {
        font-size: 28px;
        font-weight: bold;
        color: var(--primary);
    }
    
    .stat-card .label {
        font-size: 12px;
        color: #666;
        text-transform: uppercase;
    }
    
    .card {
        background: white;
        border-radius: 8px;
        margin-bottom: 20px;
        box-shadow: 0 2px 5px rgba(0,0,0,0.05);
        overflow: hidden;
    }
    
    .card-header {
        background: var(--light);
        padding: 12px 15px;
        border-bottom: 1px solid #e0e0e0;
        font-weight: 600;
        display: flex;
        justify-content: space-between;
        align-items: center;
    }
    
    .card-body { padding: 15px; }
    
    table {
        width: 100%;
        border-collapse: collapse;
        font-size: 13px;
    }
    
    th, td {
        padding: 10px 12px;
        text-align: left;
        border-bottom: 1px solid #e0e0e0;
    }
    
    th {
        background: var(--light);
        font-weight: 600;
        color: var(--dark);
        position: sticky;
        top: 0;
    }
    
    tr:hover { background: #f8f9fa; }
    
    .badge {
        display: inline-block;
        padding: 3px 8px;
        border-radius: 12px;
        font-size: 11px;
        font-weight: 500;
    }
    
    .badge-pk { background: #fff3cd; color: #856404; }
    .badge-fk { background: #cce5ff; color: #004085; }
    .badge-idx { background: #d4edda; color: #155724; }
    .badge-null { background: #f8d7da; color: #721c24; }
    
    .search-box {
        padding: 10px 15px;
        border: 1px solid #ddd;
        border-radius: 5px;
        width: 300px;
        font-size: 14px;
    }
    
    .nav-links {
        display: flex;
        gap: 10px;
        flex-wrap: wrap;
        margin-bottom: 20px;
    }
    
    .nav-links a {
        padding: 8px 15px;
        background: white;
        border-radius: 5px;
        text-decoration: none;
        color: var(--primary);
        font-size: 13px;
        box-shadow: 0 1px 3px rgba(0,0,0,0.1);
    }
    
    .nav-links a:hover {
        background: var(--primary);
        color: white;
    }
    
    .relationship {
        display: flex;
        align-items: center;
        gap: 10px;
        padding: 8px;
        background: #f8f9fa;
        border-radius: 5px;
        margin-bottom: 5px;
        font-size: 13px;
    }
    
    .relationship .arrow { color: var(--secondary); }
    
    .timestamp {
        font-size: 12px;
        color: #888;
        margin-top: 20px;
        text-align: center;
    }
    
    code {
        background: #e9ecef;
        padding: 2px 6px;
        border-radius: 3px;
        font-family: 'Consolas', monospace;
        font-size: 12px;
    }
    
    .collapsible {
        cursor: pointer;
        user-select: none;
    }
    
    .collapsible::before {
        content: 'â–¶ ';
        font-size: 10px;
    }
    
    .collapsible.active::before {
        content: 'â–¼ ';
    }
    
    .content {
        display: none;
        overflow: hidden;
    }
    
    .content.show { display: block; }
</style>
"@

$JSScripts = @"
<script>
    // Search functionality
    function searchTable(inputId, tableId) {
        const input = document.getElementById(inputId);
        const filter = input.value.toLowerCase();
        const table = document.getElementById(tableId);
        const rows = table.getElementsByTagName('tr');
        
        for (let i = 1; i < rows.length; i++) {
            const cells = rows[i].getElementsByTagName('td');
            let found = false;
            for (let j = 0; j < cells.length; j++) {
                if (cells[j].textContent.toLowerCase().includes(filter)) {
                    found = true;
                    break;
                }
            }
            rows[i].style.display = found ? '' : 'none';
        }
    }
    
    // Collapsible sections
    document.addEventListener('DOMContentLoaded', function() {
        const collapsibles = document.querySelectorAll('.collapsible');
        collapsibles.forEach(item => {
            item.addEventListener('click', function() {
                this.classList.toggle('active');
                const content = this.nextElementSibling;
                content.classList.toggle('show');
            });
        });
    });
</script>
"@

# ============================================================================
# SQL Queries for Schema Information
# ============================================================================

$SQLGetDatabases = @"
SELECT 
    name AS DatabaseName,
    database_id,
    create_date,
    compatibility_level,
    collation_name,
    state_desc,
    recovery_model_desc
FROM sys.databases
WHERE database_id > 4  -- Exclude system databases
  AND state = 0        -- Online only
ORDER BY name
"@

$SQLGetTables = @"
SELECT 
    s.name AS SchemaName,
    t.name AS TableName,
    t.object_id,
    t.create_date,
    t.modify_date,
    SUM(p.rows) AS [RowCount],
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS DECIMAL(18,2)) AS SizeMB
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.indexes i ON t.object_id = i.object_id AND i.index_id IN (0, 1)
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.is_ms_shipped = 0
GROUP BY s.name, t.name, t.object_id, t.create_date, t.modify_date
ORDER BY s.name, t.name
"@

$SQLGetColumns = @"
SELECT 
    s.name AS SchemaName,
    t.name AS TableName,
    c.name AS ColumnName,
    c.column_id AS OrdinalPosition,
    ty.name AS DataType,
    c.max_length AS MaxLength,
    c.precision,
    c.scale,
    c.is_nullable AS IsNullable,
    c.is_identity AS IsIdentity,
    dc.definition AS DefaultValue,
    CASE WHEN pk.column_id IS NOT NULL THEN 1 ELSE 0 END AS IsPrimaryKey,
    CASE WHEN fk.parent_column_id IS NOT NULL THEN 1 ELSE 0 END AS IsForeignKey,
    ep.value AS Description
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.columns c ON t.object_id = c.object_id
INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
LEFT JOIN sys.default_constraints dc ON c.default_object_id = dc.object_id
LEFT JOIN (
    SELECT ic.object_id, ic.column_id
    FROM sys.index_columns ic
    INNER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
    WHERE i.is_primary_key = 1
) pk ON t.object_id = pk.object_id AND c.column_id = pk.column_id
LEFT JOIN sys.foreign_key_columns fk ON t.object_id = fk.parent_object_id AND c.column_id = fk.parent_column_id
LEFT JOIN sys.extended_properties ep ON t.object_id = ep.major_id AND c.column_id = ep.minor_id AND ep.name = 'MS_Description'
WHERE t.is_ms_shipped = 0
ORDER BY s.name, t.name, c.column_id
"@

$SQLGetForeignKeys = @"
SELECT 
    fk.name AS FKName,
    ps.name AS ParentSchema,
    pt.name AS ParentTable,
    pc.name AS ParentColumn,
    rs.name AS ReferencedSchema,
    rt.name AS ReferencedTable,
    rc.name AS ReferencedColumn,
    fk.delete_referential_action_desc AS OnDelete,
    fk.update_referential_action_desc AS OnUpdate
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.tables pt ON fk.parent_object_id = pt.object_id
INNER JOIN sys.schemas ps ON pt.schema_id = ps.schema_id
INNER JOIN sys.columns pc ON fkc.parent_object_id = pc.object_id AND fkc.parent_column_id = pc.column_id
INNER JOIN sys.tables rt ON fk.referenced_object_id = rt.object_id
INNER JOIN sys.schemas rs ON rt.schema_id = rs.schema_id
INNER JOIN sys.columns rc ON fkc.referenced_object_id = rc.object_id AND fkc.referenced_column_id = rc.column_id
ORDER BY ps.name, pt.name, fk.name
"@

$SQLGetIndexes = @"
SELECT 
    s.name AS SchemaName,
    t.name AS TableName,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    i.is_unique AS IsUnique,
    i.is_primary_key AS IsPrimaryKey,
    STUFF((
        SELECT ', ' + c.name
        FROM sys.index_columns ic
        INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
        WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0
        ORDER BY ic.key_ordinal
        FOR XML PATH('')
    ), 1, 2, '') AS KeyColumns,
    STUFF((
        SELECT ', ' + c.name
        FROM sys.index_columns ic
        INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
        WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1
        ORDER BY ic.key_ordinal
        FOR XML PATH('')
    ), 1, 2, '') AS IncludedColumns
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.is_ms_shipped = 0 AND i.type > 0
ORDER BY s.name, t.name, i.name
"@

$SQLGetViews = @"
SELECT 
    s.name AS SchemaName,
    v.name AS ViewName,
    v.create_date,
    v.modify_date,
    m.definition AS ViewDefinition
FROM sys.views v
INNER JOIN sys.schemas s ON v.schema_id = s.schema_id
LEFT JOIN sys.sql_modules m ON v.object_id = m.object_id
WHERE v.is_ms_shipped = 0
ORDER BY s.name, v.name
"@

$SQLGetProcedures = @"
SELECT 
    s.name AS SchemaName,
    p.name AS ProcedureName,
    p.create_date,
    p.modify_date,
    LEN(m.definition) AS DefinitionLength
FROM sys.procedures p
INNER JOIN sys.schemas s ON p.schema_id = s.schema_id
LEFT JOIN sys.sql_modules m ON p.object_id = m.object_id
WHERE p.is_ms_shipped = 0
ORDER BY s.name, p.name
"@

# ============================================================================
# Functions
# ============================================================================

function Get-SchemaData {
    param(
        [string]$Server,
        [string]$Database
    )
    
    $connectionString = "Server=$Server;Database=$Database;Integrated Security=True;TrustServerCertificate=True"
    $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
    
    try {
        $connection.Open()
        
        $data = @{
            Tables = @()
            Columns = @()
            ForeignKeys = @()
            Indexes = @()
            Views = @()
            Procedures = @()
        }
        
        # Get Tables
        $cmd = $connection.CreateCommand()
        $cmd.CommandText = $SQLGetTables
        $cmd.CommandTimeout = 120
        $reader = $cmd.ExecuteReader()
        $dt = New-Object System.Data.DataTable
        $dt.Load($reader)
        $data.Tables = $dt
        
        # Get Columns
        $cmd.CommandText = $SQLGetColumns
        $reader = $cmd.ExecuteReader()
        $dt = New-Object System.Data.DataTable
        $dt.Load($reader)
        $data.Columns = $dt
        
        # Get Foreign Keys
        $cmd.CommandText = $SQLGetForeignKeys
        $reader = $cmd.ExecuteReader()
        $dt = New-Object System.Data.DataTable
        $dt.Load($reader)
        $data.ForeignKeys = $dt
        
        # Get Indexes
        $cmd.CommandText = $SQLGetIndexes
        $reader = $cmd.ExecuteReader()
        $dt = New-Object System.Data.DataTable
        $dt.Load($reader)
        $data.Indexes = $dt
        
        # Get Views
        $cmd.CommandText = $SQLGetViews
        $reader = $cmd.ExecuteReader()
        $dt = New-Object System.Data.DataTable
        $dt.Load($reader)
        $data.Views = $dt
        
        # Get Procedures
        $cmd.CommandText = $SQLGetProcedures
        $reader = $cmd.ExecuteReader()
        $dt = New-Object System.Data.DataTable
        $dt.Load($reader)
        $data.Procedures = $dt
        
        return $data
    }
    finally {
        $connection.Close()
    }
}

function New-DatabaseDocumentation {
    param(
        [string]$Server,
        [string]$Database,
        [hashtable]$Data,
        [string]$OutputFile
    )
    
    $tableCount = if ($Data.Tables) { $Data.Tables.Rows.Count } else { 0 }
    $columnCount = if ($Data.Columns) { $Data.Columns.Rows.Count } else { 0 }
    $fkCount = if ($Data.ForeignKeys) { $Data.ForeignKeys.Rows.Count } else { 0 }
    $indexCount = if ($Data.Indexes) { $Data.Indexes.Rows.Count } else { 0 }
    $viewCount = if ($Data.Views) { $Data.Views.Rows.Count } else { 0 }
    $procCount = if ($Data.Procedures) { $Data.Procedures.Rows.Count } else { 0 }
    $totalRows = if ($tableCount -gt 0) { ($Data.Tables | Measure-Object -Property RowCount -Sum -ErrorAction SilentlyContinue).Sum } else { 0 }
    $totalSizeMB = if ($tableCount -gt 0) { ($Data.Tables | Measure-Object -Property SizeMB -Sum -ErrorAction SilentlyContinue).Sum } else { 0 }
    if ($null -eq $totalRows) { $totalRows = 0 }
    if ($null -eq $totalSizeMB) { $totalSizeMB = 0 }
    
    $html = @"
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>$Database - Schema Documentation</title>
    $CSSStyles
</head>
<body>
    <div class="container">
        <header>
            <h1>ðŸ“Š $Database</h1>
            <div class="subtitle">Schema Documentation | $Server | Generated: $(Get-Date -Format 'yyyy-MM-dd HH:mm')</div>
        </header>
        
        <div class="stats-grid">
            <div class="stat-card">
                <div class="value">$tableCount</div>
                <div class="label">Tables</div>
            </div>
            <div class="stat-card">
                <div class="value">$columnCount</div>
                <div class="label">Columns</div>
            </div>
            <div class="stat-card">
                <div class="value">$fkCount</div>
                <div class="label">Foreign Keys</div>
            </div>
            <div class="stat-card">
                <div class="value">$indexCount</div>
                <div class="label">Indexes</div>
            </div>
            <div class="stat-card">
                <div class="value">$viewCount</div>
                <div class="label">Views</div>
            </div>
            <div class="stat-card">
                <div class="value">$procCount</div>
                <div class="label">Procedures</div>
            </div>
            <div class="stat-card">
                <div class="value">$([string]::Format('{0:N0}', [long]$totalRows))</div>
                <div class="label">Total Rows</div>
            </div>
            <div class="stat-card">
                <div class="value">$([math]::Round([double]$totalSizeMB, 1)) MB</div>
                <div class="label">Total Size</div>
            </div>
        </div>
        
        <div class="nav-links">
            <a href="#tables">ðŸ“‹ Tables</a>
            <a href="#relationships">ðŸ”— Relationships</a>
            <a href="#indexes">ðŸ“‘ Indexes</a>
            <a href="#views">ðŸ‘ï¸ Views</a>
            <a href="#procedures">âš™ï¸ Procedures</a>
        </div>
        
        <!-- Tables Section -->
        <div class="card" id="tables">
            <div class="card-header">
                <span>ðŸ“‹ Tables ($tableCount)</span>
                <input type="text" class="search-box" id="tableSearch" placeholder="Search tables..." onkeyup="searchTable('tableSearch', 'tableList')">
            </div>
            <div class="card-body">
                <table id="tableList">
                    <thead>
                        <tr>
                            <th>Schema</th>
                            <th>Table Name</th>
                            <th>Rows</th>
                            <th>Size (MB)</th>
                            <th>Created</th>
                            <th>Modified</th>
                        </tr>
                    </thead>
                    <tbody>
"@

    # Add table rows
    foreach ($row in $Data.Tables.Rows) {
        $rowCount = if ($null -ne $row.RowCount) { [string]::Format('{0:N0}', $row.RowCount) } else { '0' }
        $sizeMB = if ($null -ne $row.SizeMB) { $row.SizeMB } else { '0' }
        $createDate = if ($null -ne $row.create_date) { $row.create_date.ToString('yyyy-MM-dd') } else { '-' }
        $modifyDate = if ($null -ne $row.modify_date) { $row.modify_date.ToString('yyyy-MM-dd') } else { '-' }
        $html += @"
                        <tr>
                            <td><code>$($row.SchemaName)</code></td>
                            <td><a href="#table_$($row.SchemaName)_$($row.TableName)">$($row.TableName)</a></td>
                            <td>$rowCount</td>
                            <td>$sizeMB</td>
                            <td>$createDate</td>
                            <td>$modifyDate</td>
                        </tr>
"@
    }

    $html += @"
                    </tbody>
                </table>
            </div>
        </div>
        
        <!-- Table Details -->
"@

    # Add detailed table information
    foreach ($table in $Data.Tables.Rows) {
        $tableColumns = $Data.Columns.Select("SchemaName = '$($table.SchemaName)' AND TableName = '$($table.TableName)'")
        $tableIndexes = $Data.Indexes.Select("SchemaName = '$($table.SchemaName)' AND TableName = '$($table.TableName)'")
        $tableFKs = $Data.ForeignKeys.Select("ParentSchema = '$($table.SchemaName)' AND ParentTable = '$($table.TableName)'")
        
        $html += @"
        <div class="card" id="table_$($table.SchemaName)_$($table.TableName)">
            <div class="card-header collapsible">
                <span>ðŸ“‹ [$($table.SchemaName)].[$($table.TableName)] - $($tableColumns.Count) columns, $([string]::Format('{0:N0}', $table.RowCount)) rows</span>
            </div>
            <div class="content">
                <div class="card-body">
                    <table>
                        <thead>
                            <tr>
                                <th>#</th>
                                <th>Column</th>
                                <th>Data Type</th>
                                <th>Nullable</th>
                                <th>Default</th>
                                <th>Keys</th>
                            </tr>
                        </thead>
                        <tbody>
"@
        
        foreach ($col in $tableColumns) {
            $dataType = $col.DataType
            if ($col.DataType -in @('varchar', 'nvarchar', 'char', 'nchar', 'binary', 'varbinary')) {
                $len = if ($col.MaxLength -eq -1) { 'MAX' } else { $col.MaxLength }
                $dataType = "$($col.DataType)($len)"
            }
            elseif ($col.DataType -in @('decimal', 'numeric')) {
                $dataType = "$($col.DataType)($($col.precision),$($col.scale))"
            }
            
            $badges = @()
            if ($col.IsPrimaryKey) { $badges += '<span class="badge badge-pk">PK</span>' }
            if ($col.IsForeignKey) { $badges += '<span class="badge badge-fk">FK</span>' }
            if ($col.IsIdentity) { $badges += '<span class="badge badge-idx">ID</span>' }
            
            $html += @"
                            <tr>
                                <td>$($col.OrdinalPosition)</td>
                                <td><code>$($col.ColumnName)</code></td>
                                <td>$dataType</td>
                                <td>$(if($col.IsNullable){'Yes'}else{'No'})</td>
                                <td>$(if($col.DefaultValue){$col.DefaultValue}else{'-'})</td>
                                <td>$($badges -join ' ')</td>
                            </tr>
"@
        }
        
        $html += @"
                        </tbody>
                    </table>
"@
        
        # Add foreign keys for this table
        if ($tableFKs.Count -gt 0) {
            $html += @"
                    <h4 style="margin-top: 15px;">ðŸ”— Foreign Keys</h4>
"@
            foreach ($fk in $tableFKs) {
                $html += @"
                    <div class="relationship">
                        <code>$($fk.ParentColumn)</code>
                        <span class="arrow">â†’</span>
                        <code>[$($fk.ReferencedSchema)].[$($fk.ReferencedTable)].$($fk.ReferencedColumn)</code>
                        <span style="color: #888; font-size: 11px;">($($fk.FKName))</span>
                    </div>
"@
            }
        }
        
        $html += @"
                </div>
            </div>
        </div>
"@
    }

    # Relationships section
    $html += @"
        
        <!-- Relationships Section -->
        <div class="card" id="relationships">
            <div class="card-header">
                <span>ðŸ”— Foreign Key Relationships ($fkCount)</span>
            </div>
            <div class="card-body">
                <table>
                    <thead>
                        <tr>
                            <th>FK Name</th>
                            <th>Parent Table</th>
                            <th>Column</th>
                            <th></th>
                            <th>Referenced Table</th>
                            <th>Column</th>
                            <th>On Delete</th>
                        </tr>
                    </thead>
                    <tbody>
"@
    
    foreach ($fk in $Data.ForeignKeys.Rows) {
        $html += @"
                        <tr>
                            <td><code>$($fk.FKName)</code></td>
                            <td>[$($fk.ParentSchema)].[$($fk.ParentTable)]</td>
                            <td>$($fk.ParentColumn)</td>
                            <td>â†’</td>
                            <td>[$($fk.ReferencedSchema)].[$($fk.ReferencedTable)]</td>
                            <td>$($fk.ReferencedColumn)</td>
                            <td>$($fk.OnDelete)</td>
                        </tr>
"@
    }

    $html += @"
                    </tbody>
                </table>
            </div>
        </div>
        
        <!-- Indexes Section -->
        <div class="card" id="indexes">
            <div class="card-header">
                <span>ðŸ“‘ Indexes ($indexCount)</span>
            </div>
            <div class="card-body">
                <table>
                    <thead>
                        <tr>
                            <th>Table</th>
                            <th>Index Name</th>
                            <th>Type</th>
                            <th>Key Columns</th>
                            <th>Included</th>
                        </tr>
                    </thead>
                    <tbody>
"@
    
    foreach ($idx in $Data.Indexes.Rows) {
        $uniqueBadge = if ($idx.IsUnique -and -not $idx.IsPrimaryKey) { '<span class="badge badge-idx">UQ</span>' } else { '' }
        $pkBadge = if ($idx.IsPrimaryKey) { '<span class="badge badge-pk">PK</span>' } else { '' }
        
        $html += @"
                        <tr>
                            <td>[$($idx.SchemaName)].[$($idx.TableName)]</td>
                            <td><code>$($idx.IndexName)</code> $pkBadge $uniqueBadge</td>
                            <td>$($idx.IndexType)</td>
                            <td>$($idx.KeyColumns)</td>
                            <td>$(if($idx.IncludedColumns){$idx.IncludedColumns}else{'-'})</td>
                        </tr>
"@
    }

    $html += @"
                    </tbody>
                </table>
            </div>
        </div>
        
        <!-- Views Section -->
        <div class="card" id="views">
            <div class="card-header">
                <span>ðŸ‘ï¸ Views ($viewCount)</span>
            </div>
            <div class="card-body">
                <table>
                    <thead>
                        <tr>
                            <th>Schema</th>
                            <th>View Name</th>
                            <th>Created</th>
                            <th>Modified</th>
                        </tr>
                    </thead>
                    <tbody>
"@
    
    foreach ($view in $Data.Views.Rows) {
        $html += @"
                        <tr>
                            <td><code>$($view.SchemaName)</code></td>
                            <td>$($view.ViewName)</td>
                            <td>$($view.create_date.ToString('yyyy-MM-dd'))</td>
                            <td>$($view.modify_date.ToString('yyyy-MM-dd'))</td>
                        </tr>
"@
    }

    $html += @"
                    </tbody>
                </table>
            </div>
        </div>
        
        <!-- Procedures Section -->
        <div class="card" id="procedures">
            <div class="card-header">
                <span>âš™ï¸ Stored Procedures ($procCount)</span>
            </div>
            <div class="card-body">
                <table>
                    <thead>
                        <tr>
                            <th>Schema</th>
                            <th>Procedure Name</th>
                            <th>Created</th>
                            <th>Modified</th>
                            <th>Size</th>
                        </tr>
                    </thead>
                    <tbody>
"@
    
    foreach ($proc in $Data.Procedures.Rows) {
        $html += @"
                        <tr>
                            <td><code>$($proc.SchemaName)</code></td>
                            <td>$($proc.ProcedureName)</td>
                            <td>$($proc.create_date.ToString('yyyy-MM-dd'))</td>
                            <td>$($proc.modify_date.ToString('yyyy-MM-dd'))</td>
                            <td>$([math]::Round($proc.DefinitionLength/1024, 1)) KB</td>
                        </tr>
"@
    }

    $html += @"
                    </tbody>
                </table>
            </div>
        </div>
        
        <div class="timestamp">
            Generated by DBAOps Schema Documentation Generator | $(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')
        </div>
    </div>
    
    $JSScripts
</body>
</html>
"@

    $html | Out-File -FilePath $OutputFile -Encoding UTF8
    Write-Host "  Generated: $OutputFile" -ForegroundColor Green
}

# ============================================================================
# Main Execution
# ============================================================================

Write-Host "=" * 80 -ForegroundColor Cyan
Write-Host "DBAOps Schema Documentation Generator" -ForegroundColor Cyan
Write-Host "=" * 80 -ForegroundColor Cyan
Write-Host ""

# Ensure output directory exists
if (-not (Test-Path $OutputPath)) {
    New-Item -Path $OutputPath -ItemType Directory -Force | Out-Null
    Write-Host "Created output directory: $OutputPath" -ForegroundColor Yellow
}

# Get servers from CMS if not specified
$servers = @()
if ([string]::IsNullOrEmpty($ServerName)) {
    Write-Host "Getting servers from CMS: $CMSServer" -ForegroundColor Yellow
    $cmsQuery = "SELECT DISTINCT server_name FROM msdb.dbo.sysmanagement_shared_registered_servers_internal ORDER BY server_name"
    $servers = Invoke-Sqlcmd -ServerInstance $CMSServer -Query $cmsQuery -TrustServerCertificate | Select-Object -ExpandProperty server_name
}
else {
    $servers = @($ServerName)
}

Write-Host "Servers to process: $($servers.Count)" -ForegroundColor Cyan
Write-Host ""

foreach ($server in $servers) {
    Write-Host "Processing server: $server" -ForegroundColor Yellow
    
    try {
        # Get databases
        if ($DatabaseName -eq "ALL") {
            $databases = Invoke-Sqlcmd -ServerInstance $server -Query $SQLGetDatabases -TrustServerCertificate -ErrorAction Stop |
                         Select-Object -ExpandProperty DatabaseName
        }
        else {
            $databases = @($DatabaseName)
        }
        
        # Create server folder
        $serverFolder = Join-Path $OutputPath $server
        if (-not (Test-Path $serverFolder)) {
            New-Item -Path $serverFolder -ItemType Directory -Force | Out-Null
        }
        
        foreach ($db in $databases) {
            Write-Host "  Database: $db" -ForegroundColor Gray
            
            try {
                # Get schema data
                $data = Get-SchemaData -Server $server -Database $db
                
                # Generate documentation
                $outputFile = Join-Path $serverFolder "$db.html"
                New-DatabaseDocumentation -Server $server -Database $db -Data $data -OutputFile $outputFile
            }
            catch {
                Write-Host "    Error: $($_.Exception.Message)" -ForegroundColor Red
            }
        }
    }
    catch {
        Write-Host "  Error connecting to $server : $($_.Exception.Message)" -ForegroundColor Red
    }
}

# Generate index page if requested
if ($GenerateIndex) {
    Write-Host ""
    Write-Host "Generating index page..." -ForegroundColor Yellow
    
    $indexHtml = @"
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>DBAOps Schema Documentation</title>
    $CSSStyles
</head>
<body>
    <div class="container">
        <header>
            <h1>ðŸ“š DBAOps Schema Documentation</h1>
            <div class="subtitle">Database Schema Reference | Generated: $(Get-Date -Format 'yyyy-MM-dd HH:mm')</div>
        </header>
        
        <div class="card">
            <div class="card-header">Servers & Databases</div>
            <div class="card-body">
"@

    $serverFolders = Get-ChildItem -Path $OutputPath -Directory
    foreach ($sf in $serverFolders) {
        $indexHtml += "<h3>ðŸ–¥ï¸ $($sf.Name)</h3><ul>"
        $dbFiles = Get-ChildItem -Path $sf.FullName -Filter "*.html"
        foreach ($dbf in $dbFiles) {
            $dbName = [System.IO.Path]::GetFileNameWithoutExtension($dbf.Name)
            $indexHtml += "<li><a href='$($sf.Name)/$($dbf.Name)'>$dbName</a></li>"
        }
        $indexHtml += "</ul>"
    }

    $indexHtml += @"
            </div>
        </div>
    </div>
</body>
</html>
"@

    $indexHtml | Out-File -FilePath (Join-Path $OutputPath "index.html") -Encoding UTF8
    Write-Host "Generated: $OutputPath\index.html" -ForegroundColor Green
}

Write-Host ""
Write-Host "=" * 80 -ForegroundColor Cyan
Write-Host "Documentation generation complete!" -ForegroundColor Green
Write-Host "Output: $OutputPath" -ForegroundColor Cyan
Write-Host "=" * 80 -ForegroundColor Cyan
