<#
.SYNOPSIS
    DBAOps Dashboard Supplement - Adds ServerType and v2.0 data
.DESCRIPTION
    Reads existing data.json and adds new properties for ServerType,
    App Webservers, Servers Needing Attention, Alerts, and Jobs
.NOTES
    Version: 2.0
    Run AFTER Get-JBRhoylleDashboardData.ps1
#>

param(
    [string]$SqlServer = "YOURSERVER",
    [string]$Database = "JBRhoylleRepository",
    [string]$DataJsonPath = "E:\DBAOps\WebDashboard\data.json"
)

$ErrorActionPreference = "Continue"

Write-Host ""
Write-Host "  ============================================================" -ForegroundColor Cyan
Write-Host "  DBAOps Dashboard Supplement v2.0" -ForegroundColor Cyan
Write-Host "  ============================================================" -ForegroundColor Cyan
Write-Host ""

# SQL Query Function
function Invoke-SqlQueryLocal {
    param([string]$Query)
    try {
        $connection = New-Object System.Data.SqlClient.SqlConnection
        $connection.ConnectionString = "Server=$SqlServer;Database=$Database;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
    }
}

function ConvertTo-SafeInt {
    param($Value, $Default = 0)
    if ($null -eq $Value -or $Value -eq [DBNull]::Value -or $Value -eq '') { return $Default }
    try { return [int]$Value } catch { return $Default }
}

function ConvertTo-IDSystemtring {
    param($Value, $Default = $null)
    if ($null -eq $Value -or $Value -eq [DBNull]::Value) { return $Default }
    return [string]$Value
}

# Load existing data.json
Write-Host "  Loading existing data.json..." -ForegroundColor Gray

if (-not (Test-Path $DataJsonPath)) {
    Write-Host "    ERROR: data.json not found!" -ForegroundColor Red
    exit 1
}

try {
    $jsonContent = Get-Content -Path $DataJsonPath -Raw -Encoding UTF8
    $dashboardData = $jsonContent | ConvertFrom-Json
    Write-Host "    Loaded existing data.json" -ForegroundColor Green
} catch {
    Write-Host "    ERROR loading data.json: $_" -ForegroundColor Red
    exit 1
}

# Query Server Type Summary
Write-Host "  Querying server type summary..." -ForegroundColor Gray

$serverTypeSummary = Invoke-SqlQueryLocal -Query "SELECT * FROM dashboard.vw_ServerTypeSummary"

if ($serverTypeSummary) {
    $stSum = @{
        totalServers = ConvertTo-SafeInt $serverTypeSummary.TotalServers
        sqlServers = ConvertTo-SafeInt $serverTypeSummary.SQLServers
        appWebservers = ConvertTo-SafeInt $serverTypeSummary.AppWebservers
        sqlWithVersion = ConvertTo-SafeInt $serverTypeSummary.SQLWithVersion
        sqlMissingVersion = ConvertTo-SafeInt $serverTypeSummary.SQLMissingVersion
        prodSQLServers = ConvertTo-SafeInt $serverTypeSummary.ProdSQLServers
        nonProdSQLServers = ConvertTo-SafeInt $serverTypeSummary.NonProdSQLServers
    }
    $dashboardData | Add-Member -NotePropertyName "serverTypeSummary" -NotePropertyValue $stSum -Force
    Write-Host "    Total: $($stSum.totalServers) | SQL: $($stSum.sqlServers) | App: $($stSum.appWebservers)" -ForegroundColor Green
}

# Query App Webservers
Write-Host "  Querying App Webservers..." -ForegroundColor Gray

$appWebserverList = Invoke-SqlQueryLocal -Query "SELECT * FROM dashboard.vw_AppWebservers ORDER BY AppCategory, Environment"

$appWebserversData = @{
    total = 0
    servers = @()
}

if ($appWebserverList) {
    foreach ($srv in $appWebserverList) {
        $lastSeenStr = "N/A"
        if ($srv.LastSeen -ne $null -and $srv.LastSeen -ne [DBNull]::Value) {
            try { $lastSeenStr = $srv.LastSeen.ToString("yyyy-MM-dd HH:mm:ss") } catch { $lastSeenStr = "N/A" }
        }
        $appWebserversData.servers += @{
            serverName = ConvertTo-IDSystemtring $srv.ServerName
            environment = ConvertTo-IDSystemtring $srv.Environment
            appCategory = ConvertTo-IDSystemtring $srv.AppCategory
            status = ConvertTo-IDSystemtring $srv.Status
            lastSeen = $lastSeenStr
            cmsGroup = ConvertTo-IDSystemtring $srv.CMSGroupName
            notes = ConvertTo-IDSystemtring $srv.Notes
        }
    }
    $appWebserversData.total = $appWebserversData.servers.Count
    Write-Host "    Found $($appWebserversData.total) App Webservers (WebFOCUS)" -ForegroundColor Cyan
}

$dashboardData | Add-Member -NotePropertyName "appWebservers" -NotePropertyValue $appWebserversData -Force

# Query Servers Needing Attention
Write-Host "  Querying servers needing attention..." -ForegroundColor Gray

$attentionServers = Invoke-SqlQueryLocal -Query "SELECT * FROM dashboard.vw_ServersNeedingAttention ORDER BY Priority, ServerName"

$serversNeedingAttention = @()

if ($attentionServers) {
    foreach ($srv in $attentionServers) {
        $lastSeenStr = "N/A"
        if ($srv.LastSeen -ne $null -and $srv.LastSeen -ne [DBNull]::Value) {
            try { $lastSeenStr = $srv.LastSeen.ToString("yyyy-MM-dd HH:mm:ss") } catch { $lastSeenStr = "N/A" }
        }
        $sqlVer = ConvertTo-IDSystemtring $srv.SQLVersion
        if ([string]::IsNullOrEmpty($sqlVer)) { $sqlVer = "Missing" }
        
        $serversNeedingAttention += @{
            serverName = ConvertTo-IDSystemtring $srv.ServerName
            environment = ConvertTo-IDSystemtring $srv.Environment
            appCategory = ConvertTo-IDSystemtring $srv.AppCategory
            sqlVersion = $sqlVer
            issue = ConvertTo-IDSystemtring $srv.Issue
            priority = ConvertTo-SafeInt $srv.Priority
            lastSeen = $lastSeenStr
            notes = ConvertTo-IDSystemtring $srv.Notes
        }
    }
    $count = $serversNeedingAttention.Count
    $color = if ($count -gt 0) { "Yellow" } else { "Green" }
    Write-Host "    Found $count servers needing attention" -ForegroundColor $color
}

$dashboardData | Add-Member -NotePropertyName "serversNeedingAttention" -NotePropertyValue $serversNeedingAttention -Force

# Query Alert Summary
Write-Host "  Querying alerts summary..." -ForegroundColor Gray

$alertSummary = Invoke-SqlQueryLocal -Query "SELECT * FROM dashboard.vw_AlertSummary"

$databaseAlerts = @{
    summary = @{
        total = 0
        critical = 0
        high = 0
        medium = 0
        low = 0
        unresolved = 0
    }
    recent = @()
}

if ($alertSummary) {
    $databaseAlerts.summary = @{
        total = ConvertTo-SafeInt $alertSummary.TotalAlerts
        critical = ConvertTo-SafeInt $alertSummary.CriticalCount
        high = ConvertTo-SafeInt $alertSummary.HighCount
        medium = ConvertTo-SafeInt $alertSummary.MediumCount
        low = ConvertTo-SafeInt $alertSummary.LowCount
        unresolved = ConvertTo-SafeInt $alertSummary.UnresolvedCount
    }
    $critical = $databaseAlerts.summary.critical
    $color = if ($critical -gt 0) { "Red" } else { "Green" }
    Write-Host "    Alerts (24h): $($databaseAlerts.summary.total) total, $critical critical" -ForegroundColor $color
}

# Get recent alerts
$recentAlertsQuery = @"
SELECT TOP 50
    AlertID, ServerName, AlertTime, Severity, AlertType, AlertSource,
    LEFT(AlertMessage, 200) AS AlertMessage, IsResolved
FROM alerts.RecentAlerts
WHERE AlertTime >= DATEADD(DAY, -7, GETDATE())
ORDER BY AlertTime DESC
"@

$recentAlerts = Invoke-SqlQueryLocal -Query $recentAlertsQuery

if ($recentAlerts) {
    foreach ($alert in $recentAlerts) {
        $alertTimeStr = "N/A"
        if ($alert.AlertTime -ne $null -and $alert.AlertTime -ne [DBNull]::Value) {
            try { $alertTimeStr = $alert.AlertTime.ToString("yyyy-MM-dd HH:mm:ss") } catch { $alertTimeStr = "N/A" }
        }
        $isResolved = $false
        if ($alert.IsResolved -ne $null -and $alert.IsResolved -ne [DBNull]::Value) {
            $isResolved = [bool]$alert.IsResolved
        }
        $databaseAlerts.recent += @{
            alertId = ConvertTo-SafeInt $alert.AlertID
            serverName = ConvertTo-IDSystemtring $alert.ServerName
            alertTime = $alertTimeStr
            severity = ConvertTo-IDSystemtring $alert.Severity
            alertType = ConvertTo-IDSystemtring $alert.AlertType
            alertSource = ConvertTo-IDSystemtring $alert.AlertSource
            message = ConvertTo-IDSystemtring $alert.AlertMessage
            isResolved = $isResolved
        }
    }
}

$dashboardData | Add-Member -NotePropertyName "databaseAlerts" -NotePropertyValue $databaseAlerts -Force

# Query Jobs Summary
Write-Host "  Querying jobs summary..." -ForegroundColor Gray

$jobsSummary = Invoke-SqlQueryLocal -Query "SELECT * FROM dashboard.vw_JobsSummary"

$jobsMonitoring = @{
    summary = @{
        totalJobs = 0
        succeeded = 0
        failed = 0
        cancelled = 0
        unknown = 0
    }
    failedJobs = @()
}

if ($jobsSummary) {
    $jobsMonitoring.summary = @{
        totalJobs = ConvertTo-SafeInt $jobsSummary.TotalJobs
        succeeded = ConvertTo-SafeInt $jobsSummary.SucceededCount
        failed = ConvertTo-SafeInt $jobsSummary.FailedCount
        cancelled = ConvertTo-SafeInt $jobsSummary.CancelledCount
        unknown = ConvertTo-SafeInt $jobsSummary.UnknownCount
    }
    $failed = $jobsMonitoring.summary.failed
    $color = if ($failed -gt 0) { "Yellow" } else { "Green" }
    Write-Host "    Jobs (24h): $($jobsMonitoring.summary.totalJobs) total, $failed failed" -ForegroundColor $color
}

# Get failed jobs
$failedJobsQuery = @"
SELECT TOP 50
    ServerName, JobName, JobCategory, LastRunDate, LastRunOutcome,
    LastRunDuration_Seconds, LEFT(LastRunMessage, 200) AS LastRunMessage
FROM jobs.SQLAgentJobs
WHERE LastRunOutcome = 'Failed' AND LastRunDate >= DATEADD(DAY, -1, GETDATE())
ORDER BY LastRunDate DESC
"@

$failedJobs = Invoke-SqlQueryLocal -Query $failedJobsQuery

if ($failedJobs) {
    foreach ($job in $failedJobs) {
        $lastRunStr = "N/A"
        if ($job.LastRunDate -ne $null -and $job.LastRunDate -ne [DBNull]::Value) {
            try { $lastRunStr = $job.LastRunDate.ToString("yyyy-MM-dd HH:mm:ss") } catch { $lastRunStr = "N/A" }
        }
        $jobsMonitoring.failedJobs += @{
            serverName = ConvertTo-IDSystemtring $job.ServerName
            jobName = ConvertTo-IDSystemtring $job.JobName
            category = ConvertTo-IDSystemtring $job.JobCategory
            lastRunDate = $lastRunStr
            durationSeconds = ConvertTo-SafeInt $job.LastRunDuration_Seconds
            message = ConvertTo-IDSystemtring $job.LastRunMessage
        }
    }
}

$dashboardData | Add-Member -NotePropertyName "jobsMonitoring" -NotePropertyValue $jobsMonitoring -Force

# Query SQL Version Distribution
Write-Host "  Querying SQL version distribution..." -ForegroundColor Gray

$versionDist = Invoke-SqlQueryLocal -Query "SELECT * FROM dashboard.vw_SQLVersionDistribution ORDER BY ServerCount DESC"

$sqlVersionDistribution = @()

if ($versionDist) {
    foreach ($ver in $versionDist) {
        $sqlVersionDistribution += @{
            versionName = ConvertTo-IDSystemtring $ver.VersionName
            serverCount = ConvertTo-SafeInt $ver.ServerCount
            percentage = [math]::Round((ConvertTo-SafeInt $ver.Percentage), 1)
        }
    }
    Write-Host "    SQL Version groups: $($sqlVersionDistribution.Count)" -ForegroundColor Green
}

$dashboardData | Add-Member -NotePropertyName "sqlVersionDistribution" -NotePropertyValue $sqlVersionDistribution -Force

# Update metadata
$dashboardData | Add-Member -NotePropertyName "supplementVersion" -NotePropertyValue "2.0" -Force
$dashboardData | Add-Member -NotePropertyName "supplementUpdated" -NotePropertyValue (Get-Date -Format "yyyy-MM-dd HH:mm:ss") -Force

# Save updated data.json
Write-Host ""
Write-Host "  Saving updated data.json..." -ForegroundColor Gray

try {
    $jsonContent = $dashboardData | ConvertTo-Json -Depth 10
    $utf8NoBom = New-Object System.Text.UTF8Encoding $false
    [System.IO.File]::WriteAllText($DataJsonPath, $jsonContent, $utf8NoBom)
    $fileSize = (Get-Item $DataJsonPath).Length
    $fileSizeKB = [math]::Round($fileSize / 1KB, 2)
    Write-Host "    Saved to $DataJsonPath ($fileSizeKB KB)" -ForegroundColor Green
} catch {
    Write-Host "    ERROR saving: $_" -ForegroundColor Red
}

# Summary
Write-Host ""
Write-Host "  ============================================================" -ForegroundColor Green
Write-Host "  Dashboard Supplement Complete!" -ForegroundColor Green
Write-Host "  ============================================================" -ForegroundColor Green
Write-Host ""
Write-Host "  NEW DATA ADDED:" -ForegroundColor Yellow

if ($dashboardData.serverTypeSummary) {
    Write-Host "    serverTypeSummary: $($dashboardData.serverTypeSummary.totalServers) total ($($dashboardData.serverTypeSummary.sqlServers) SQL, $($dashboardData.serverTypeSummary.appWebservers) App)" -ForegroundColor White
}
Write-Host "    appWebservers: $($dashboardData.appWebservers.total) WebFOCUS servers" -ForegroundColor White

$attCount = $dashboardData.serversNeedingAttention.Count
$attColor = if ($attCount -gt 0) { "Yellow" } else { "White" }
Write-Host "    serversNeedingAttention: $attCount servers" -ForegroundColor $attColor

Write-Host "    databaseAlerts: $($dashboardData.databaseAlerts.summary.total) alerts" -ForegroundColor White
Write-Host "    jobsMonitoring: $($dashboardData.jobsMonitoring.summary.totalJobs) jobs" -ForegroundColor White
Write-Host "    sqlVersionDistribution: $($dashboardData.sqlVersionDistribution.Count) versions" -ForegroundColor White
Write-Host ""
Write-Host "  ============================================================" -ForegroundColor Green
Write-Host ""