<#
.SYNOPSIS
    Process Patch Queue - Executes queued SQL Server patches
.DESCRIPTION
    Retrieves and processes the next patch from the DBAOps patching queue
.AUTHOR
    JB Gakumba, Sr SQL DBA\DBAOps Eng.
#>

$SqlServer = "YOURSERVER"
$Database = "JBRhoylleRepository"
$connString = "Server=$SqlServer;Database=$Database;Integrated Security=True;TrustServerCertificate=True;"

# Get next patch from queue
$query = "SELECT TOP 1 QueueID, ServerName, InstanceName, KBNumber, PatchType, PatchFilePath FROM Patching.PatchExecutionQueue WHERE QueueStatus = 'Queued' ORDER BY Priority, QueuedDate"
$patch = Invoke-Sqlcmd -ConnectionString $connString -Query $query

if ($patch -and $patch.QueueID) {
    $queueId = $patch.QueueID
    $serverName = $patch.ServerName
    $kbNumber = $patch.KBNumber
    $patchFilePath = $patch.PatchFilePath
    
    Write-Output "Processing QueueID: $queueId, Server: $serverName, KB: $kbNumber"
    Write-Output "Patch File: $patchFilePath"
    
    # Update status to Running
    $updateQuery = "UPDATE Patching.PatchExecutionQueue SET QueueStatus = 'Running', StartedDate = GETDATE() WHERE QueueID = $queueId"
    Invoke-Sqlcmd -ConnectionString $connString -Query $updateQuery
    
    try {
        # Verify patch file exists
        if (-not (Test-Path $patchFilePath)) {
            throw "Patch file not found: $patchFilePath"
        }
        
        Write-Output "[$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')] [INFO] Patch file verified: $patchFilePath"
        
        # Execute patch
        $patchScript = "E:\DBAOps\PowerShell\Invoke-SQLServerPatch.ps1"
        Write-Output "[$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')] [INFO] Starting patch installation on $serverName..."
        
        $result = & $patchScript -ServerName $serverName -PatchFilePath $patchFilePath -KBNumber $kbNumber -PatchType $patch.PatchType
        
        $exitCode = if ($result.Success) { 0 } else { 1 }
        $message = "Patch completed. Duration: $($result.Duration)"
        
    } catch {
        $exitCode = 99
        $message = $_.Exception.Message
        Write-Output "[$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')] [ERROR] $message"
    }
    
    # Update result
    $status = if ($exitCode -eq 0) { 'Completed' } else { 'Failed' }
    $updateResultQuery = @"
UPDATE Patching.PatchExecutionQueue 
SET QueueStatus = '$status', 
    CompletedDate = GETDATE(), 
    ExitCode = $exitCode, 
    ResultMessage = '$($message -replace "'", "''")' 
WHERE QueueID = $queueId
"@
    Invoke-Sqlcmd -ConnectionString $connString -Query $updateResultQuery
    
    Write-Output "[$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')] [INFO] Result: $status (ExitCode=$exitCode)"
    
} else {
    Write-Output "No patches queued for execution"
}
