<#
.SYNOPSIS
    DBAOps Infrastructure Assessment and Documentation Generator
.DESCRIPTION
    Generates an HTML User Manual documenting the DBAOps infrastructure
.PARAMETER OutputPath
    Path for the generated HTML report
.EXAMPLE
    .\Get-DBAOpsAssessment.ps1 -OutputPath "E:\DBAOps\Documentation\Manual.html"
#>

param(
    [string]$SqlServer = $env:COMPUTERNAME,
    [string]$Database = "DBAOpsRepository",
    [string]$OutputPath = $null
)
$scriptDir = Split-Path -Parent $MyInvocation.MyCommand.Path
$defaultDocPath = Join-Path (Join-Path (Split-Path -Parent $scriptDir) "..") "Documentation\DBAOps-UserManual-$(Get-Date -Format 'yyyyMMdd').html"
if (-not $OutputPath) { $OutputPath = $defaultDocPath }

$ErrorActionPreference = "Continue"
$scriptVersion = "1.0.0"
$generatedDate = Get-Date -Format "yyyy-MM-dd HH:mm:ss"

Write-Host ""
Write-Host "==================================================================" -ForegroundColor Cyan
Write-Host "  DBAOps Infrastructure Assessment - User Manual Generator" -ForegroundColor Cyan
Write-Host "==================================================================" -ForegroundColor Cyan
Write-Host "  Server: $SqlServer" -ForegroundColor White
Write-Host "  Database: $Database" -ForegroundColor White
Write-Host "  Output: $OutputPath" -ForegroundColor White
Write-Host ""

# SQL Query Function
function Invoke-SqlQuery {
    param([string]$Query, [string]$Server = $SqlServer, [string]$Db = $Database)
    try {
        $connection = New-Object System.Data.SqlClient.SqlConnection
        $connection.ConnectionString = "Server=$Server;Database=$Db;Integrated Security=True;TrustServerCertificate=True"
        $connection.Open()
        $command = $connection.CreateCommand()
        $command.CommandText = $Query
        $command.CommandTimeout = 120
        $adapter = New-Object System.Data.SqlClient.SqlDataAdapter $command
        $dataset = New-Object System.Data.DataSet
        $adapter.Fill($dataset) | Out-Null
        $connection.Close()
        return $dataset.Tables[0]
    }
    catch {
        Write-Warning "Query failed: $_"
        return $null
    }
}

# Initialize assessment data
$assessment = @{
    GeneratedAt = $generatedDate
    Version = $scriptVersion
    Server = $SqlServer
    Database = $Database
    DatabaseInfo = @{}
    Schemas = @()
    Tables = @()
    Views = @()
    StoredProcedures = @()
    ServerInventory = @{}
    ComplianceFrameworks = @()
    DocumentationLinks = @()
    AzureDevOpsProjects = @()
    Jobs = @()
    DashboardFeatures = @()
    Statistics = @{}
}

# Section 1: Database Information
Write-Host "[1/10] Gathering Database Information..." -ForegroundColor Yellow
$dbInfoQuery = "SELECT DB_NAME() AS DatabaseName, SERVERPROPERTY('ServerName') AS ServerName, SERVERPROPERTY('ProductVersion') AS SQLVersion, SERVERPROPERTY('Edition') AS Edition, (SELECT SUM(size * 8 / 1024) FROM sys.database_files WHERE type = 0) AS DataSizeMB, (SELECT COUNT(*) FROM sys.tables) AS TableCount, (SELECT COUNT(*) FROM sys.views) AS ViewCount, (SELECT COUNT(*) FROM sys.procedures) AS ProcedureCount"
$dbInfo = Invoke-SqlQuery -Query $dbInfoQuery
if ($dbInfo) {
    $assessment.DatabaseInfo = @{
        DatabaseName = $dbInfo.DatabaseName
        ServerName = $dbInfo.ServerName
        SQLVersion = $dbInfo.SQLVersion
        Edition = $dbInfo.Edition
        DataSizeMB = $dbInfo.DataSizeMB
        TableCount = $dbInfo.TableCount
        ViewCount = $dbInfo.ViewCount
        ProcedureCount = $dbInfo.ProcedureCount
    }
    Write-Host "       OK - $($dbInfo.DatabaseName) on $($dbInfo.ServerName)" -ForegroundColor Green
}

# Section 2: Schema Documentation
Write-Host "[2/10] Documenting Schemas..." -ForegroundColor Yellow
$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.schema_id < 16384 ORDER BY s.name"
$schemas = Invoke-SqlQuery -Query $schemaQuery
if ($schemas) {
    foreach ($schema in $schemas) {
        $desc = switch ($schema.SchemaName) {
            "dbo" { "Default schema for general objects" }
            "config" { "Configuration tables and settings" }
            "dashboard" { "Dashboard views and reporting objects" }
            "inventory" { "Server and database inventory" }
            "compliance" { "Compliance framework assessments" }
            "monitoring" { "Monitoring and performance data" }
            "alerts" { "Alert management and notifications" }
            "jobs" { "SQL Agent job tracking" }
            "backup" { "Backup monitoring and history" }
            "security" { "Security assessments and auditing" }
            "patch" { "Patch management and tracking" }
            default { "Custom schema" }
        }
        $assessment.Schemas += @{ Name = $schema.SchemaName; TableCount = $schema.TableCount; ViewCount = $schema.ViewCount; ProcCount = $schema.ProcCount; Description = $desc }
    }
    Write-Host "       OK - Found $($assessment.Schemas.Count) schemas" -ForegroundColor Green
}

# Section 3: Table Documentation
Write-Host "[3/10] Documenting Tables..." -ForegroundColor Yellow
$tableQuery = "SELECT s.name AS SchemaName, t.name AS TableName, p.rows AS RowCount, (SELECT COUNT(*) FROM sys.columns c WHERE c.object_id = t.object_id) AS ColumnCount FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id INNER JOIN sys.partitions p ON t.object_id = p.object_id AND p.index_id IN (0,1) WHERE s.schema_id > 4 AND s.schema_id < 16384 ORDER BY s.name, t.name"
$tables = Invoke-SqlQuery -Query $tableQuery
if ($tables) {
    foreach ($table in $tables) {
        $assessment.Tables += @{ Schema = $table.SchemaName; Name = $table.TableName; FullName = "$($table.SchemaName).$($table.TableName)"; RowCount = $table.RowCount; ColumnCount = $table.ColumnCount }
    }
    Write-Host "       OK - Found $($assessment.Tables.Count) tables" -ForegroundColor Green
}

# Section 4: View Documentation
Write-Host "[4/10] Documenting Views..." -ForegroundColor Yellow
$viewQuery = "SELECT s.name AS SchemaName, v.name AS ViewName, (SELECT COUNT(*) FROM sys.columns c WHERE c.object_id = v.object_id) AS ColumnCount FROM sys.views v INNER JOIN sys.schemas s ON v.schema_id = s.schema_id WHERE s.schema_id > 4 AND s.schema_id < 16384 ORDER BY s.name, v.name"
$views = Invoke-SqlQuery -Query $viewQuery
if ($views) {
    foreach ($view in $views) {
        $assessment.Views += @{ Schema = $view.SchemaName; Name = $view.ViewName; FullName = "$($view.SchemaName).$($view.ViewName)"; ColumnCount = $view.ColumnCount }
    }
    Write-Host "       OK - Found $($assessment.Views.Count) views" -ForegroundColor Green
}

# Section 5: Stored Procedures
Write-Host "[5/10] Documenting Stored Procedures..." -ForegroundColor Yellow
$procQuery = "SELECT s.name AS SchemaName, p.name AS ProcName, (SELECT COUNT(*) FROM sys.parameters pm WHERE pm.object_id = p.object_id) AS ParameterCount FROM sys.procedures p INNER JOIN sys.schemas s ON p.schema_id = s.schema_id WHERE s.schema_id > 4 AND s.schema_id < 16384 ORDER BY s.name, p.name"
$procs = Invoke-SqlQuery -Query $procQuery
if ($procs) {
    foreach ($proc in $procs) {
        $assessment.StoredProcedures += @{ Schema = $proc.SchemaName; Name = $proc.ProcName; FullName = "$($proc.SchemaName).$($proc.ProcName)"; ParameterCount = $proc.ParameterCount }
    }
    Write-Host "       OK - Found $($assessment.StoredProcedures.Count) procedures" -ForegroundColor Green
}

# Section 6: Server Inventory
Write-Host "[6/10] Gathering Server Inventory..." -ForegroundColor Yellow
$serverQuery = "SELECT COUNT(*) AS TotalServers, SUM(CASE WHEN Environment = 'Production' THEN 1 ELSE 0 END) AS ProductionServers, SUM(CASE WHEN Environment = 'Development' THEN 1 ELSE 0 END) AS DevelopmentServers, SUM(CASE WHEN ComplianceTier = 'Tier 1' THEN 1 ELSE 0 END) AS Tier1Count, SUM(CASE WHEN ComplianceTier = 'Tier 2' THEN 1 ELSE 0 END) AS Tier2Count, SUM(CASE WHEN ComplianceTier = 'Tier 3' THEN 1 ELSE 0 END) AS Tier3Count, SUM(CASE WHEN ComplianceTier = 'Tier 4' THEN 1 ELSE 0 END) AS Tier4Count FROM config.ServerInventory WHERE IsActive = 1"
$serverStats = Invoke-SqlQuery -Query $serverQuery
if ($serverStats) {
    $assessment.ServerInventory = @{
        TotalServers = $serverStats.TotalServers
        ProductionServers = $serverStats.ProductionServers
        DevelopmentServers = $serverStats.DevelopmentServers
        Tier1Count = $serverStats.Tier1Count
        Tier2Count = $serverStats.Tier2Count
        Tier3Count = $serverStats.Tier3Count
        Tier4Count = $serverStats.Tier4Count
    }
    Write-Host "       OK - $($serverStats.TotalServers) servers" -ForegroundColor Green
}

# Section 7: Documentation Links
Write-Host "[7/10] Gathering Documentation Links..." -ForegroundColor Yellow
$docQuery = "SELECT LinkID, ISNULL(ApplicationCode, 'General') AS ApplicationCode, DocumentType, DocumentTitle, DocumentURL, Category, Icon FROM config.DocumentationLinks WHERE IsActive = 1 ORDER BY DisplayOrder"
$docs = Invoke-SqlQuery -Query $docQuery
if ($docs) {
    foreach ($doc in $docs) {
        $assessment.DocumentationLinks += @{ LinkID = $doc.LinkID; ApplicationCode = $doc.ApplicationCode; DocumentType = $doc.DocumentType; Title = $doc.DocumentTitle; URL = $doc.DocumentURL; Category = $doc.Category; Icon = $doc.Icon }
    }
    Write-Host "       OK - Found $($assessment.DocumentationLinks.Count) documentation links" -ForegroundColor Green
}

# Section 8: Azure DevOps Projects
Write-Host "[8/10] Gathering Azure DevOps Projects..." -ForegroundColor Yellow
$devOpsQuery = "SELECT ApplicationCode AS ProjectCode, DocumentTitle AS ProjectName, DocumentURL AS ProjectURL, Icon FROM config.DocumentationLinks WHERE DocumentType = 'Project' AND IsActive = 1 ORDER BY DocumentTitle"
$devOps = Invoke-SqlQuery -Query $devOpsQuery
if ($devOps) {
    foreach ($proj in $devOps) {
        $assessment.AzureDevOpsProjects += @{ Code = $proj.ProjectCode; Name = $proj.ProjectName; URL = $proj.ProjectURL; Icon = $proj.Icon }
    }
    Write-Host "       OK - Found $($assessment.AzureDevOpsProjects.Count) Azure DevOps projects" -ForegroundColor Green
}

# Section 9: SQL Agent Jobs
Write-Host "[9/10] Documenting SQL Agent Jobs..." -ForegroundColor Yellow
$jobQuery = "SELECT j.name AS JobName, j.description AS JobDescription, CASE j.enabled WHEN 1 THEN 'Enabled' ELSE 'Disabled' END AS Status, c.name AS Category FROM msdb.dbo.sysjobs j LEFT JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id WHERE j.name LIKE '%DBAOps%' OR j.name LIKE '%Dashboard%' OR j.name LIKE '%Collect%' OR c.name LIKE '%DBA%' ORDER BY j.name"
$jobs = Invoke-SqlQuery -Query $jobQuery -Db "msdb"
if ($jobs) {
    foreach ($job in $jobs) {
        $assessment.Jobs += @{ Name = $job.JobName; Description = $job.JobDescription; Status = $job.Status; Category = $job.Category }
    }
    Write-Host "       OK - Found $($assessment.Jobs.Count) related jobs" -ForegroundColor Green
}

# Section 10: Dashboard Features
Write-Host "[10/10] Documenting Dashboard Features..." -ForegroundColor Yellow
$assessment.DashboardFeatures = @(
    @{ Name = "Server Inventory"; Description = "Real-time view of all SQL Server instances with health status"; Location = "Servers Tab" },
    @{ Name = "Compliance Scorecard"; Description = "CJIS, NIST, STIG, DISA compliance scores"; Location = "Compliance Tab" },
    @{ Name = "Patch Management"; Description = "SQL Server patch status and CU tracking"; Location = "Patching Tab" },
    @{ Name = "Backup Monitoring"; Description = "Backup status and Veeam repository monitoring"; Location = "Backup Tab" },
    @{ Name = "SQL Intelligence"; Description = "Performance insights and recommendations"; Location = "SQL Intelligence Tab" },
    @{ Name = "Job Monitoring"; Description = "SQL Agent job status and failure tracking"; Location = "Jobs Tab" },
    @{ Name = "Alert Management"; Description = "Real-time alerts with severity levels"; Location = "Alerts Tab" },
    @{ Name = "Help Desk Center"; Description = "Application mapping, contacts, documentation"; Location = "Help Desk Tab" },
    @{ Name = "Disk Space Monitoring"; Description = "Drive space tracking with thresholds"; Location = "Disk Space Tab" },
    @{ Name = "Remediation Search"; Description = "Filter servers needing attention"; Location = "Remediation Tab" },
    @{ Name = "SQL 2016 Migration"; Description = "Track SQL 2016 instances for upgrade"; Location = "Dashboard Overview" },
    @{ Name = "Global Search"; Description = "Search servers, databases, jobs, Azure DevOps"; Location = "Header Search Bar" },
    @{ Name = "Azure DevOps Integration"; Description = "Direct links to projects and documentation"; Location = "Help Desk Tab" }
)
Write-Host "       OK - Documented $($assessment.DashboardFeatures.Count) features" -ForegroundColor Green

# Calculate Statistics
$assessment.Statistics = @{
    TotalSchemas = $assessment.Schemas.Count
    TotalTables = $assessment.Tables.Count
    TotalViews = $assessment.Views.Count
    TotalProcedures = $assessment.StoredProcedures.Count
    TotalDocLinks = $assessment.DocumentationLinks.Count
    TotalDevOpsProjects = $assessment.AzureDevOpsProjects.Count
    TotalJobs = $assessment.Jobs.Count
    TotalFeatures = $assessment.DashboardFeatures.Count
}

# Generate HTML Report
Write-Host ""
Write-Host "Generating HTML Report..." -ForegroundColor Yellow

# Build schema table rows
$schemaRows = ""
foreach ($s in $assessment.Schemas) {
    $schemaRows += "<tr><td><code>$($s.Name)</code></td><td>$($s.Description)</td><td>$($s.TableCount)</td><td>$($s.ViewCount)</td><td>$($s.ProcCount)</td></tr>`n"
}

# Build table rows
$tableRows = ""
foreach ($t in $assessment.Tables | Select-Object -First 50) {
    $tableRows += "<tr><td><code>$($t.FullName)</code></td><td>$($t.RowCount)</td><td>$($t.ColumnCount)</td></tr>`n"
}

# Build view rows
$viewRows = ""
foreach ($v in $assessment.Views | Select-Object -First 50) {
    $viewRows += "<tr><td><code>$($v.FullName)</code></td><td>$($v.ColumnCount)</td></tr>`n"
}

# Build procedure rows
$procRows = ""
foreach ($p in $assessment.StoredProcedures | Select-Object -First 50) {
    $procRows += "<tr><td><code>$($p.FullName)</code></td><td>$($p.ParameterCount)</td></tr>`n"
}

# Build documentation rows
$docRows = ""
foreach ($d in $assessment.DocumentationLinks | Where-Object { $_.DocumentType -ne 'Project' } | Select-Object -First 30) {
    $docRows += "<tr><td>$($d.Icon) $($d.Title)</td><td>$($d.DocumentType)</td><td>$($d.Category)</td></tr>`n"
}

# Build DevOps project rows
$devOpsRows = ""
foreach ($p in $assessment.AzureDevOpsProjects | Select-Object -First 40) {
    $devOpsRows += "<tr><td>$($p.Icon) <a href='$($p.URL)' target='_blank'>$($p.Name)</a></td><td>$($p.Code)</td></tr>`n"
}

# Build job rows
$jobRows = ""
foreach ($j in $assessment.Jobs) {
    $statusClass = if ($j.Status -eq 'Enabled') { 'badge-success' } else { 'badge-warning' }
    $jobRows += "<tr><td><strong>$($j.Name)</strong></td><td>$($j.Category)</td><td><span class='badge $statusClass'>$($j.Status)</span></td></tr>`n"
}

# Build feature cards
$featureCards = ""
foreach ($f in $assessment.DashboardFeatures) {
    $featureCards += "<div class='feature-card'><h4>$($f.Name)</h4><p>$($f.Description)</p><div class='meta'>Location: $($f.Location)</div></div>`n"
}

$htmlContent = @"
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>DBAOps User Manual</title>
    <style>
        :root { --bg-primary: #0a0e27; --bg-secondary: #1a1f3a; --bg-tertiary: #252d4a; --primary: #06b6d4; --success: #10b981; --warning: #f59e0b; --danger: #ef4444; --text-primary: #e2e8f0; --text-secondary: #94a3b8; --border: rgba(51,65,85,0.5); }
        * { margin: 0; padding: 0; box-sizing: border-box; }
        body { font-family: 'Segoe UI', sans-serif; background: linear-gradient(135deg, var(--bg-primary), var(--bg-secondary)); color: var(--text-primary); line-height: 1.6; min-height: 100vh; }
        .container { max-width: 1400px; margin: 0 auto; padding: 20px; }
        .header { background: var(--bg-secondary); border: 1px solid var(--border); border-radius: 16px; padding: 40px; margin-bottom: 30px; text-align: center; }
        .header h1 { font-size: 2.5rem; color: var(--primary); margin-bottom: 10px; }
        .header .subtitle { color: var(--text-secondary); }
        .header .meta { margin-top: 20px; display: flex; justify-content: center; gap: 30px; flex-wrap: wrap; }
        .header .meta-item { background: rgba(6,182,212,0.1); padding: 10px 20px; border-radius: 8px; }
        .header .meta-item label { display: block; font-size: 0.75rem; color: var(--text-secondary); }
        .header .meta-item value { display: block; font-weight: 600; color: var(--primary); }
        .nav-toc { background: var(--bg-secondary); border: 1px solid var(--border); border-radius: 12px; padding: 20px; margin-bottom: 30px; }
        .nav-toc h3 { color: var(--primary); margin-bottom: 15px; }
        .nav-toc ul { list-style: none; display: flex; flex-wrap: wrap; gap: 10px; }
        .nav-toc a { display: inline-block; padding: 8px 16px; background: var(--bg-tertiary); color: var(--text-secondary); text-decoration: none; border-radius: 6px; }
        .nav-toc a:hover { background: var(--primary); color: white; }
        .section { background: var(--bg-secondary); border: 1px solid var(--border); border-radius: 12px; padding: 30px; margin-bottom: 30px; }
        .section h2 { color: var(--primary); font-size: 1.5rem; margin-bottom: 20px; padding-bottom: 10px; border-bottom: 2px solid var(--border); }
        .section h3 { color: var(--text-primary); margin: 20px 0 10px 0; }
        .section p { color: var(--text-secondary); margin-bottom: 15px; }
        table { width: 100%; border-collapse: collapse; margin: 15px 0; font-size: 0.875rem; }
        th, td { padding: 12px 15px; text-align: left; border-bottom: 1px solid var(--border); }
        th { background: var(--bg-tertiary); color: var(--primary); font-weight: 600; }
        tr:hover { background: rgba(6,182,212,0.05); }
        .stats-grid { display: grid; grid-template-columns: repeat(auto-fit, minmax(180px, 1fr)); gap: 20px; margin: 20px 0; }
        .stat-card { background: var(--bg-tertiary); border-radius: 10px; padding: 20px; text-align: center; }
        .stat-card .value { font-size: 2rem; font-weight: 700; color: var(--primary); }
        .stat-card .label { color: var(--text-secondary); font-size: 0.875rem; }
        .feature-grid { display: grid; grid-template-columns: repeat(auto-fit, minmax(300px, 1fr)); gap: 20px; margin: 20px 0; }
        .feature-card { background: var(--bg-tertiary); border-radius: 10px; padding: 20px; border: 1px solid var(--border); }
        .feature-card h4 { color: var(--primary); margin-bottom: 10px; }
        .feature-card p { color: var(--text-secondary); font-size: 0.875rem; }
        .feature-card .meta { font-size: 0.75rem; color: var(--text-secondary); margin-top: 10px; }
        .badge { display: inline-block; padding: 4px 10px; border-radius: 4px; font-size: 0.75rem; font-weight: 600; }
        .badge-success { background: rgba(16,185,129,0.2); color: var(--success); }
        .badge-warning { background: rgba(245,158,11,0.2); color: var(--warning); }
        .badge-danger { background: rgba(239,68,68,0.2); color: var(--danger); }
        code { background: var(--bg-primary); padding: 2px 6px; border-radius: 4px; font-family: Consolas, monospace; color: var(--success); }
        a { color: var(--primary); text-decoration: none; }
        a:hover { text-decoration: underline; }
        details { background: var(--bg-tertiary); border-radius: 8px; margin: 10px 0; border: 1px solid var(--border); }
        summary { padding: 15px; cursor: pointer; font-weight: 600; }
        details > div { padding: 15px; }
        .tier-1 { color: #ef4444; } .tier-2 { color: #f59e0b; } .tier-3 { color: #3b82f6; } .tier-4 { color: #8b5cf6; }
        @media print { body { background: white; color: black; } .nav-toc { display: none; } }
    </style>
</head>
<body>
<div class="container">
    <header class="header">
        <h1>DBAOps User Manual</h1>
        <p class="subtitle">SQL Server Infrastructure Management Platform - Complete Documentation Guide</p>
        <div class="meta">
            <div class="meta-item"><label>Server</label><value>$($assessment.DatabaseInfo.ServerName)</value></div>
            <div class="meta-item"><label>Database</label><value>$($assessment.DatabaseInfo.DatabaseName)</value></div>
            <div class="meta-item"><label>SQL Version</label><value>$($assessment.DatabaseInfo.SQLVersion)</value></div>
            <div class="meta-item"><label>Generated</label><value>$generatedDate</value></div>
        </div>
    </header>

    <nav class="nav-toc">
        <h3>Table of Contents</h3>
        <ul>
            <li><a href="#overview">Overview</a></li>
            <li><a href="#servers">Server Inventory</a></li>
            <li><a href="#features">Dashboard Features</a></li>
            <li><a href="#schemas">Database Schemas</a></li>
            <li><a href="#tables">Tables</a></li>
            <li><a href="#views">Views</a></li>
            <li><a href="#procedures">Procedures</a></li>
            <li><a href="#documentation">Documentation</a></li>
            <li><a href="#devops">Azure DevOps</a></li>
            <li><a href="#jobs">SQL Jobs</a></li>
            <li><a href="#userguide">User Guide</a></li>
        </ul>
    </nav>

    <section class="section" id="overview">
        <h2>Overview</h2>
        <p>DBAOps is a comprehensive SQL Server infrastructure management platform designed for enterprise environments with strict compliance requirements including CJIS, FedRAMP, and DoD IL4/IL5/IL6.</p>
        <div class="stats-grid">
            <div class="stat-card"><div class="value">$($assessment.ServerInventory.TotalServers)</div><div class="label">Total Servers</div></div>
            <div class="stat-card"><div class="value">$($assessment.Statistics.TotalTables)</div><div class="label">Database Tables</div></div>
            <div class="stat-card"><div class="value">$($assessment.Statistics.TotalViews)</div><div class="label">Views</div></div>
            <div class="stat-card"><div class="value">$($assessment.Statistics.TotalProcedures)</div><div class="label">Stored Procedures</div></div>
            <div class="stat-card"><div class="value">$($assessment.Statistics.TotalDocLinks)</div><div class="label">Documentation Links</div></div>
            <div class="stat-card"><div class="value">$($assessment.Statistics.TotalDevOpsProjects)</div><div class="label">DevOps Projects</div></div>
        </div>
    </section>

    <section class="section" id="servers">
        <h2>Server Inventory</h2>
        <p>Server inventory managed through Central Management Server (CMS).</p>
        <div class="stats-grid">
            <div class="stat-card"><div class="value">$($assessment.ServerInventory.ProductionServers)</div><div class="label">Production</div></div>
            <div class="stat-card"><div class="value">$($assessment.ServerInventory.DevelopmentServers)</div><div class="label">Development</div></div>
        </div>
        <h3>Compliance Tier Distribution</h3>
        <table>
            <tr><th>Tier</th><th>Description</th><th>Count</th></tr>
            <tr><td class="tier-1">Tier 1</td><td>CJIS Critical - Highest security</td><td>$($assessment.ServerInventory.Tier1Count)</td></tr>
            <tr><td class="tier-2">Tier 2</td><td>Criminal Justice Operations</td><td>$($assessment.ServerInventory.Tier2Count)</td></tr>
            <tr><td class="tier-3">Tier 3</td><td>Administrative Support</td><td>$($assessment.ServerInventory.Tier3Count)</td></tr>
            <tr><td class="tier-4">Tier 4</td><td>Development/Test</td><td>$($assessment.ServerInventory.Tier4Count)</td></tr>
        </table>
    </section>

    <section class="section" id="features">
        <h2>Dashboard Features</h2>
        <p>The DBAOps dashboard provides comprehensive monitoring and management capabilities:</p>
        <div class="feature-grid">
$featureCards
        </div>
    </section>

    <section class="section" id="schemas">
        <h2>Database Schemas</h2>
        <p>The DBAOps repository uses logical schemas to organize objects by function.</p>
        <table>
            <tr><th>Schema</th><th>Description</th><th>Tables</th><th>Views</th><th>Procs</th></tr>
$schemaRows
        </table>
    </section>

    <section class="section" id="tables">
        <h2>Database Tables</h2>
        <details>
            <summary>All Tables ($($assessment.Tables.Count) total)</summary>
            <div>
                <table>
                    <tr><th>Table Name</th><th>Rows</th><th>Columns</th></tr>
$tableRows
                </table>
            </div>
        </details>
    </section>

    <section class="section" id="views">
        <h2>Database Views</h2>
        <details>
            <summary>All Views ($($assessment.Views.Count) total)</summary>
            <div>
                <table>
                    <tr><th>View Name</th><th>Columns</th></tr>
$viewRows
                </table>
            </div>
        </details>
    </section>

    <section class="section" id="procedures">
        <h2>Stored Procedures</h2>
        <details>
            <summary>All Procedures ($($assessment.StoredProcedures.Count) total)</summary>
            <div>
                <table>
                    <tr><th>Procedure Name</th><th>Parameters</th></tr>
$procRows
                </table>
            </div>
        </details>
    </section>

    <section class="section" id="documentation">
        <h2>Documentation Links</h2>
        <table>
            <tr><th>Title</th><th>Type</th><th>Category</th></tr>
$docRows
        </table>
    </section>

    <section class="section" id="devops">
        <h2>Azure DevOps Integration</h2>
        <p>Base URL: <code>https://dev.azure.com/yourorg</code></p>
        <table>
            <tr><th>Project</th><th>Code</th></tr>
$devOpsRows
        </table>
    </section>

    <section class="section" id="jobs">
        <h2>SQL Agent Jobs</h2>
        <table>
            <tr><th>Job Name</th><th>Category</th><th>Status</th></tr>
$jobRows
        </table>
    </section>

    <section class="section" id="userguide">
        <h2>User Guide</h2>
        <h3>Accessing the Dashboard</h3>
        <p>Open a web browser and navigate to the DBAOps dashboard URL. The dashboard loads with live data refreshed every 5 minutes.</p>
        <h3>Using Global Search</h3>
        <p>The search bar allows you to search: Server names, Database names, SQL Agent jobs, Azure DevOps projects, Documentation and SOPs</p>
        <h3>Escalation Procedures</h3>
        <table>
            <tr><th>Severity</th><th>Response Time</th><th>Action</th></tr>
            <tr><td><span class="badge badge-danger">Critical</span></td><td>Immediate</td><td>Page on-call DBA</td></tr>
            <tr><td><span class="badge badge-warning">Warning</span></td><td>15-60 min</td><td>Notify DBA team</td></tr>
            <tr><td><span class="badge badge-success">Caution</span></td><td>Next business day</td><td>Review and plan</td></tr>
        </table>
    </section>

    <footer style="text-align: center; padding: 40px; color: var(--text-secondary);">
        <p>DBAOps User Manual - Generated $generatedDate</p>
        <p>Version $scriptVersion</p>
    </footer>
</div>
</body>
</html>
"@

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

# Write file
$utf8NoBom = New-Object System.Text.UTF8Encoding $false
[System.IO.File]::WriteAllText($OutputPath, $htmlContent, $utf8NoBom)

Write-Host ""
Write-Host "==================================================================" -ForegroundColor Cyan
Write-Host "  ASSESSMENT COMPLETE" -ForegroundColor Cyan
Write-Host "==================================================================" -ForegroundColor Cyan
Write-Host "  Report: $OutputPath" -ForegroundColor Green
Write-Host "  Size: $([math]::Round((Get-Item $OutputPath).Length / 1KB, 2)) KB" -ForegroundColor White
Write-Host ""
Write-Host "  Summary:" -ForegroundColor Yellow
Write-Host "    Schemas: $($assessment.Statistics.TotalSchemas)" -ForegroundColor White
Write-Host "    Tables: $($assessment.Statistics.TotalTables)" -ForegroundColor White
Write-Host "    Views: $($assessment.Statistics.TotalViews)" -ForegroundColor White
Write-Host "    Procedures: $($assessment.Statistics.TotalProcedures)" -ForegroundColor White
Write-Host "    Servers: $($assessment.ServerInventory.TotalServers)" -ForegroundColor White
Write-Host "    Documentation Links: $($assessment.Statistics.TotalDocLinks)" -ForegroundColor White
Write-Host "    Azure DevOps Projects: $($assessment.Statistics.TotalDevOpsProjects)" -ForegroundColor White
Write-Host ""
