# Generate-SchemaDocumentation.ps1
# Uses CMS as source of truth
# Version 1.0

param(
    [string]$SqlInstance = "YOURSERVER01",
    [string]$Database = "DBAOpsRepository",
    [string]$OutputPath = "E:\DBAOps\SchemaDoc"
)

$ErrorActionPreference = "Continue"
$LogFile = "E:\DBAOps\Logs\SchemaDocGeneration_$(Get-Date -Format 'yyyyMMdd_HHmmss').log"

Start-Transcript -Path $LogFile -Append

Write-Host "=== DBAOps Schema Documentation Generator ===" -ForegroundColor Cyan
Write-Host "Started: $(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')"

# Ensure output directory exists
if (-not (Test-Path $OutputPath)) {
    New-Item -Path $OutputPath -ItemType Directory -Force | Out-Null
}

try {
    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = "Server=$SqlInstance;Database=$Database;Integrated Security=True;TrustServerCertificate=True"
    $connection.Open()

    # Get all schemas
    $schemaQuery = @"
SELECT s.name AS SchemaName,
    (SELECT COUNT(*) FROM sys.tables t WHERE t.schema_id = s.schema_id) AS TableCount,
    (SELECT COUNT(*) FROM sys.views v WHERE v.schema_id = s.schema_id) AS ViewCount,
    (SELECT COUNT(*) FROM sys.procedures p WHERE p.schema_id = s.schema_id) AS ProcCount
FROM sys.schemas s
WHERE s.schema_id > 4 
  AND s.name NOT IN ('guest','INFORMATION_SCHEMA','sys','db_owner','db_accessadmin',
      'db_securityadmin','db_ddladmin','db_backupoperator','db_datareader',
      'db_datawriter','db_denydatareader','db_denydatawriter')
ORDER BY s.name
"@

    $cmd = $connection.CreateCommand()
    $cmd.CommandText = $schemaQuery
    $adapter = New-Object System.Data.SqlClient.SqlDataAdapter $cmd
    $schemaDS = New-Object System.Data.DataSet
    $adapter.Fill($schemaDS) | Out-Null

    $schemas = $schemaDS.Tables[0]
    Write-Host "Found $($schemas.Rows.Count) schemas" -ForegroundColor Green

    # Build HTML documentation
    $html = @"
<!DOCTYPE html>
<html>
<head>
    <title>DBAOps Schema Documentation</title>
    <style>
        body { font-family: Segoe UI, Arial; margin: 20px; background: #f5f5f5; }
        .container { max-width: 1200px; margin: 0 auto; background: white; padding: 20px; border-radius: 8px; }
        h1 { color: #1e3a5f; border-bottom: 3px solid #2d5a87; padding-bottom: 10px; }
        h2 { color: #2d5a87; margin-top: 30px; }
        table { width: 100%; border-collapse: collapse; margin: 15px 0; }
        th { background: #1e3a5f; color: white; padding: 10px; text-align: left; }
        td { padding: 8px 10px; border-bottom: 1px solid #ddd; }
        tr:hover { background: #f8f9fa; }
        .summary { display: flex; gap: 20px; margin: 20px 0; }
        .card { background: #f8f9fa; padding: 15px; border-radius: 8px; text-align: center; flex: 1; }
        .card .number { font-size: 32px; font-weight: bold; color: #1e3a5f; }
        .footer { text-align: center; color: #666; margin-top: 30px; padding-top: 20px; border-top: 1px solid #ddd; }
    </style>
</head>
<body>
<div class="container">
    <h1>DBAOps Repository Schema Documentation</h1>
    <p>Generated: $(Get-Date -Format 'yyyy-MM-dd HH:mm:ss') | Server: $SqlInstance | Database: $Database</p>
    
    <div class="summary">
        <div class="card"><div class="number">$($schemas.Rows.Count)</div><div>Schemas</div></div>
        <div class="card"><div class="number">$(($schemas.Rows | Measure-Object -Property TableCount -Sum).Sum)</div><div>Tables</div></div>
        <div class="card"><div class="number">$(($schemas.Rows | Measure-Object -Property ViewCount -Sum).Sum)</div><div>Views</div></div>
        <div class="card"><div class="number">$(($schemas.Rows | Measure-Object -Property ProcCount -Sum).Sum)</div><div>Procedures</div></div>
    </div>
    
    <h2>Schema Summary</h2>
    <table>
        <tr><th>Schema</th><th>Tables</th><th>Views</th><th>Procedures</th><th>Total</th></tr>
"@

    foreach ($s in $schemas.Rows) {
        $total = [int]$s.TableCount + [int]$s.ViewCount + [int]$s.ProcCount
        $html += "<tr><td><b>$($s.SchemaName)</b></td><td>$($s.TableCount)</td><td>$($s.ViewCount)</td><td>$($s.ProcCount)</td><td>$total</td></tr>`n"
    }

    $html += @"
    </table>
    <div class="footer">
        <p>DBAOps™ Enterprise Platform | Auto-generated documentation</p>
    </div>
</div>
</body>
</html>
"@

    # Save HTML
    $htmlPath = "$OutputPath\SchemaDocumentation.html"
    $html | Out-File -FilePath $htmlPath -Encoding UTF8
    Write-Host "Documentation saved to: $htmlPath" -ForegroundColor Green

    # Update schemaDoc table if exists
    $updateQuery = @"
IF OBJECT_ID('schemaDoc.GenerationLog') IS NOT NULL
BEGIN
    INSERT INTO schemaDoc.GenerationLog (GeneratedAt, OutputPath, SchemaCount, Status)
    VALUES (GETDATE(), '$htmlPath', $($schemas.Rows.Count), 'Success')
END
"@
    $cmd.CommandText = $updateQuery
    $cmd.ExecuteNonQuery() | Out-Null

    $connection.Close()

    Write-Host ""
    Write-Host "=== Schema Documentation Complete ===" -ForegroundColor Cyan
}
catch {
    Write-Host "ERROR: $_" -ForegroundColor Red
}
finally {
    Stop-Transcript
}
