# Get-ServerHealth.ps1
# Uses CMS as source of truth
# Version 1.0

param(
    [string]$SqlInstance = "YOURSERVER01",
    [string]$Database = "DBAOpsRepository"
)

Write-Host "=== DBAOps Server Health Collector ===" -ForegroundColor Cyan
Write-Host "Started: $(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')"

# Get servers from CMS - SOURCE OF TRUTH
$cmsQuery = @"
SELECT server_name AS ServerName
FROM msdb.dbo.sysmanagement_shared_registered_servers_internal
WHERE server_name NOT LIKE '%\%'
  AND server_name <> @@SERVERNAME
ORDER BY server_name
"@

try {
    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = "Server=$SqlInstance;Database=msdb;Integrated Security=True;TrustServerCertificate=True"
    $connection.Open()
    
    $command = $connection.CreateCommand()
    $command.CommandText = $cmsQuery
    $adapter = New-Object System.Data.SqlClient.SqlDataAdapter $command
    $dataset = New-Object System.Data.DataSet
    $adapter.Fill($dataset) | Out-Null
    $connection.Close()
    
    $Servers = $dataset.Tables[0]
    Write-Host "Found $($Servers.Rows.Count) servers from CMS" -ForegroundColor Green
}
catch {
    Write-Host "ERROR getting servers from CMS: $_" -ForegroundColor Red
    exit 1
}

$collected = 0
$errors = 0

foreach ($row in $Servers.Rows) {
    $ServerName = $row.ServerName
    Write-Host "  Checking: $ServerName" -ForegroundColor Gray -NoNewline
    
    try {
        $healthQuery = @"
SELECT 
    cpu_count AS CPUCount,
    (SELECT COUNT(*) FROM sys.databases WHERE state = 0) AS OnlineDBs,
    (SELECT COUNT(*) FROM sys.databases WHERE state <> 0) AS OfflineDBs,
    (SELECT SUM(size * 8 / 1024) FROM sys.master_files WHERE type = 0) AS DataSizeMB,
    (SELECT SUM(size * 8 / 1024) FROM sys.master_files WHERE type = 1) AS LogSizeMB,
    (SELECT sqlserver_start_time FROM sys.dm_os_sys_info) AS StartTime,
    @@VERSION AS SQLVersion
FROM sys.dm_os_sys_info
"@
        
        $conn = New-Object System.Data.SqlClient.SqlConnection
        $conn.ConnectionString = "Server=$ServerName;Database=master;Integrated Security=True;TrustServerCertificate=True;Connect Timeout=10"
        $conn.Open()
        
        $cmd = $conn.CreateCommand()
        $cmd.CommandText = $healthQuery
        $cmd.CommandTimeout = 30
        
        $adapt = New-Object System.Data.SqlClient.SqlDataAdapter $cmd
        $ds = New-Object System.Data.DataSet
        $adapt.Fill($ds) | Out-Null
        $conn.Close()
        
        $result = $ds.Tables[0].Rows[0]
        
        # Insert into repository
        $repoConn = New-Object System.Data.SqlClient.SqlConnection
        $repoConn.ConnectionString = "Server=$SqlInstance;Database=$Database;Integrated Security=True;TrustServerCertificate=True"
        $repoConn.Open()
        
        $insertSQL = @"
INSERT INTO health.ServerHealth (ServerName, CPUCount, OnlineDatabases, OfflineDatabases, DataSizeMB, LogSizeMB, SQLStartTime, CollectedAt)
VALUES ('$ServerName', $($result.CPUCount), $($result.OnlineDBs), $($result.OfflineDBs), $($result.DataSizeMB), $($result.LogSizeMB), '$($result.StartTime)', GETDATE())
"@
        
        $insertCmd = $repoConn.CreateCommand()
        $insertCmd.CommandText = $insertSQL
        $insertCmd.ExecuteNonQuery() | Out-Null
        $repoConn.Close()
        
        $collected++
        Write-Host " - OK" -ForegroundColor Green
    }
    catch {
        Write-Host " - ERROR" -ForegroundColor Yellow
        $errors++
    }
}

# Update LastSeen in ServerInventory
try {
    $updateConn = New-Object System.Data.SqlClient.SqlConnection
    $updateConn.ConnectionString = "Server=$SqlInstance;Database=$Database;Integrated Security=True;TrustServerCertificate=True"
    $updateConn.Open()
    
    $updateSQL = @"
UPDATE si SET LastSeen = GETDATE()
FROM config.ServerInventory si
WHERE EXISTS (SELECT 1 FROM health.ServerHealth h WHERE h.ServerName = si.ServerName AND h.CollectedAt > DATEADD(MINUTE, -5, GETDATE()))
"@
    
    $updateCmd = $updateConn.CreateCommand()
    $updateCmd.CommandText = $updateSQL
    $updateCmd.ExecuteNonQuery() | Out-Null
    $updateConn.Close()
}
catch { }

Write-Host ""
Write-Host "=== Collection Complete ===" -ForegroundColor Cyan
Write-Host "  Servers collected: $collected"
Write-Host "  Servers with errors: $errors"
