<#
.SYNOPSIS
    Tests connectivity to servers that are missing inventory data
.DESCRIPTION
    Identifies servers in config.ServerInventory that have missing SQL version
    and tests DNS, Ping, Port 1433, and SQL connectivity
.NOTES
    Version: 1.0
    Run this to diagnose servers needing attention
#>

param(
    [string]$SqlServer = "YOURSERVER",
    [string]$Database = "JBRhoylleRepository",
    [switch]$UpdateInventory
)

$ErrorActionPreference = "Continue"

Write-Host ""
Write-Host "  ============================================================" -ForegroundColor Cyan
Write-Host "  Server Connectivity Test" -ForegroundColor Cyan
Write-Host "  Testing servers with missing inventory data" -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 = 30
        $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
    }
}

# Get servers needing attention
Write-Host "  Getting servers needing attention..." -ForegroundColor Gray

$serversToTest = Invoke-SqlQueryLocal -Query @"
SELECT 
    ServerName,
    Environment,
    AppCategory,
    SQLVersion,
    CMSGroupName,
    CMSLastSyncDate
FROM config.ServerInventory
WHERE IsActive = 1
  AND (ServerType = 'SQL Server' OR ServerType IS NULL)
  AND (SQLVersion IS NULL OR LTRIM(RTRIM(SQLVersion)) = '')
ORDER BY ServerName
"@

if (-not $serversToTest -or $serversToTest.Rows.Count -eq 0) {
    Write-Host "  No servers found with missing inventory data!" -ForegroundColor Green
    Write-Host ""
    exit 0
}

$serverCount = $serversToTest.Rows.Count
Write-Host "  Found $serverCount servers to test" -ForegroundColor Yellow
Write-Host ""

# Results collection
$results = @()

foreach ($row in $serversToTest) {
    $serverName = $row.ServerName
    $environment = if ($row.Environment -ne [DBNull]::Value) { $row.Environment } else { "Unknown" }
    $appCategory = if ($row.AppCategory -ne [DBNull]::Value) { $row.AppCategory } else { "Unknown" }
    
    Write-Host "  Testing: $serverName" -ForegroundColor White
    
    $result = @{
        ServerName = $serverName
        Environment = $environment
        AppCategory = $appCategory
        DNSResolved = $false
        IPAddress = "N/A"
        PingSuccess = $false
        PingLatency = "N/A"
        Port1433Open = $false
        SQLConnected = $false
        SQLVersion = "N/A"
        SQLEdition = "N/A"
        ErrorMessage = ""
    }
    
    # Test 1: DNS Resolution
    try {
        $dns = [System.Net.Dns]::GetHostAddresses($serverName)
        if ($dns.Count -gt 0) {
            $result.DNSResolved = $true
            $result.IPAddress = $dns[0].IPAddressToString
            Write-Host "    [OK] DNS: $($result.IPAddress)" -ForegroundColor Green
        }
    }
    catch {
        Write-Host "    [FAIL] DNS: Cannot resolve hostname" -ForegroundColor Red
        $result.ErrorMessage = "DNS resolution failed"
    }
    
    # Test 2: Ping
    if ($result.DNSResolved) {
        try {
            $ping = Test-Connection -ComputerName $serverName -Count 1 -Quiet -ErrorAction SilentlyContinue
            if ($ping) {
                $result.PingSuccess = $true
                $pingResult = Test-Connection -ComputerName $serverName -Count 1 -ErrorAction SilentlyContinue
                if ($pingResult) {
                    $result.PingLatency = "$($pingResult.ResponseTime)ms"
                }
                Write-Host "    [OK] Ping: $($result.PingLatency)" -ForegroundColor Green
            }
            else {
                Write-Host "    [FAIL] Ping: No response (may be blocked by firewall)" -ForegroundColor Yellow
            }
        }
        catch {
            Write-Host "    [FAIL] Ping: $_" -ForegroundColor Yellow
        }
    }
    
    # Test 3: Port 1433
    if ($result.DNSResolved) {
        try {
            $tcpClient = New-Object System.Net.Sockets.TcpClient
            $asyncResult = $tcpClient.BeginConnect($serverName, 1433, $null, $null)
            $waitHandle = $asyncResult.AsyncWaitHandle
            $connected = $waitHandle.WaitOne(3000, $false)
            
            if ($connected) {
                $result.Port1433Open = $true
                Write-Host "    [OK] Port 1433: Open" -ForegroundColor Green
                $tcpClient.EndConnect($asyncResult)
            }
            else {
                Write-Host "    [FAIL] Port 1433: Connection timeout" -ForegroundColor Red
                $result.ErrorMessage = "Port 1433 blocked or SQL not running"
            }
            $tcpClient.Close()
        }
        catch {
            Write-Host "    [FAIL] Port 1433: $_" -ForegroundColor Red
            $result.ErrorMessage = "Port 1433 connection failed"
        }
    }
    
    # Test 4: SQL Connection
    if ($result.Port1433Open) {
        try {
            $sqlConn = New-Object System.Data.SqlClient.SqlConnection
            $sqlConn.ConnectionString = "Server=$serverName;Database=master;Integrated Security=True;TrustServerCertificate=True;Connection Timeout=10"
            $sqlConn.Open()
            
            $cmd = $sqlConn.CreateCommand()
            $cmd.CommandText = "SELECT SERVERPROPERTY('ProductVersion') AS Version, SERVERPROPERTY('Edition') AS Edition"
            $reader = $cmd.ExecuteReader()
            
            if ($reader.Read()) {
                $result.SQLConnected = $true
                $result.SQLVersion = $reader["Version"].ToString()
                $result.SQLEdition = $reader["Edition"].ToString()
                Write-Host "    [OK] SQL: Connected - $($result.SQLVersion)" -ForegroundColor Green
            }
            $reader.Close()
            $sqlConn.Close()
        }
        catch {
            Write-Host "    [FAIL] SQL: $_" -ForegroundColor Red
            $result.ErrorMessage = "SQL connection failed: $_"
        }
    }
    
    $results += $result
    Write-Host ""
}

# Summary
Write-Host "  ============================================================" -ForegroundColor Cyan
Write-Host "  RESULTS SUMMARY" -ForegroundColor Cyan
Write-Host "  ============================================================" -ForegroundColor Cyan
Write-Host ""

$dnsOK = ($results | Where-Object { $_.DNSResolved }).Count
$pingOK = ($results | Where-Object { $_.PingSuccess }).Count
$portOK = ($results | Where-Object { $_.Port1433Open }).Count
$sqlOK = ($results | Where-Object { $_.SQLConnected }).Count

Write-Host "  Total Servers Tested: $serverCount" -ForegroundColor White
Write-Host "  DNS Resolution:       $dnsOK / $serverCount" -ForegroundColor $(if ($dnsOK -eq $serverCount) { "Green" } else { "Yellow" })
Write-Host "  Ping Response:        $pingOK / $serverCount" -ForegroundColor $(if ($pingOK -eq $serverCount) { "Green" } else { "Yellow" })
Write-Host "  Port 1433 Open:       $portOK / $serverCount" -ForegroundColor $(if ($portOK -eq $serverCount) { "Green" } else { "Yellow" })
Write-Host "  SQL Connected:        $sqlOK / $serverCount" -ForegroundColor $(if ($sqlOK -eq $serverCount) { "Green" } else { "Yellow" })
Write-Host ""

# Show problem servers
$problemServers = $results | Where-Object { -not $_.SQLConnected }
if ($problemServers.Count -gt 0) {
    Write-Host "  SERVERS WITH ISSUES:" -ForegroundColor Red
    Write-Host "  --------------------" -ForegroundColor Red
    foreach ($srv in $problemServers) {
        $issue = if (-not $srv.DNSResolved) { "DNS Failed" }
                 elseif (-not $srv.Port1433Open) { "Port 1433 Blocked" }
                 else { "SQL Auth Failed" }
        Write-Host "    $($srv.ServerName): $issue" -ForegroundColor Yellow
    }
    Write-Host ""
}

# Show successful servers that can be updated
$successServers = $results | Where-Object { $_.SQLConnected }
if ($successServers.Count -gt 0) {
    Write-Host "  SERVERS READY FOR UPDATE:" -ForegroundColor Green
    Write-Host "  -------------------------" -ForegroundColor Green
    foreach ($srv in $successServers) {
        Write-Host "    $($srv.ServerName): SQL $($srv.SQLVersion)" -ForegroundColor Cyan
    }
    Write-Host ""
}

# Update inventory if requested
if ($UpdateInventory -and $successServers.Count -gt 0) {
    Write-Host "  UPDATING INVENTORY..." -ForegroundColor Yellow
    Write-Host ""
    
    foreach ($server in $successServers) {
        $versionEscaped = $server.SQLVersion -replace "'", "''"
        $editionEscaped = $server.SQLEdition -replace "'", "''"
        $serverEscaped = $server.ServerName -replace "'", "''"
        
        $updateQuery = "UPDATE config.ServerInventory SET SQLVersion = '$versionEscaped', SQLEdition = '$editionEscaped', LastInventoryUpdate = GETDATE(), Notes = ISNULL(Notes, '') + ' [Auto-updated ' + CONVERT(VARCHAR, GETDATE(), 120) + ']' WHERE ServerName = '$serverEscaped'"
        
        try {
            Invoke-SqlQueryLocal -Query $updateQuery
            Write-Host "    [OK] Updated: $($server.ServerName)" -ForegroundColor Green
        }
        catch {
            Write-Host "    [FAIL] Failed to update: $($server.ServerName) - $_" -ForegroundColor Red
        }
    }
    Write-Host ""
}
elseif ($successServers.Count -gt 0 -and -not $UpdateInventory) {
    Write-Host "  TIP: Run with -UpdateInventory to update the inventory for successful servers:" -ForegroundColor Cyan
    Write-Host "       .\Test-ServerConnectivity.ps1 -UpdateInventory" -ForegroundColor White
    Write-Host ""
}

# Export results to CSV
$csvPath = "E:\DBAOps\Logs\ServerConnectivity_$(Get-Date -Format 'yyyyMMdd_HHmmss').csv"
try {
    # Ensure directory exists
    $logDir = Split-Path $csvPath -Parent
    if (-not (Test-Path $logDir)) {
        New-Item -ItemType Directory -Path $logDir -Force | Out-Null
    }
    $results | Export-Csv -Path $csvPath -NoTypeInformation
    Write-Host "  Results exported to: $csvPath" -ForegroundColor Gray
}
catch {
    Write-Host "  Could not export to CSV: $_" -ForegroundColor Yellow
}

Write-Host ""
Write-Host "  ============================================================" -ForegroundColor Cyan
Write-Host "  Test Complete!" -ForegroundColor Cyan
Write-Host "  ============================================================" -ForegroundColor Cyan
Write-Host ""