<#
.SYNOPSIS
    Process Patch Queue - Executes queued SQL Server patches
.DESCRIPTION
    Retrieves and processes the next patch from the DBAOps patching queue
.AUTHOR
    RHOYLLE INNOVATION EMPIRE™, Sr SQL DBA\DBAOps Eng.
.CREATED
    2026-01-27
.MODIFIED
    2026-01-27
#>

# Process Patch Queue
$SqlServer = "YOURSERVER01"
$Database = "DBAOpsRepository"
$connString = "Server=$SqlServer;Database=$Database;Integrated Security=True;TrustServerCertificate=True;"

# Get next patch from queue
$query = "EXEC Patching.usp_GetNextPatchToExecute"
$patch = Invoke-Sqlcmd -ConnectionString $connString -Query $query

$queueId = $patch.QueueID
if ($patch -and $queueId -and ![string]::IsNullOrEmpty($queueId) -and $queueId -isnot [DBNull]) {
    Write-Output "Processing QueueID: $queueId, Server: $($patch.ServerName)"
    
    # Execute the patch script
    $scriptPath = "E:\DBAOps\PowerShell\Invoke-SQLServerPatch.ps1"
    
    try {
        $result = & $scriptPath `
            -ServerName $patch.ServerName `
            -InstanceName $patch.InstanceName `
            -PatchFilePath $patch.PatchFilePath `
            -KBNumber $patch.KBNumber `
            -PatchType $patch.PatchType
        
        $exitCode = if ($result.Success) { 0 } else { 1 }
        $message = "Patch completed. Duration: $($result.Duration)"
        $logFile = $result.LogFile
    }
    catch {
        $exitCode = 99
        $message = $_.Exception.Message
        $logFile = $null
    }
    
    # Update result
    $updateQuery = "EXEC Patching.usp_UpdatePatchResult @QueueID=$queueId, @ExitCode=$exitCode, @ResultMessage='$message', @LogFilePath='$logFile'"
    Invoke-Sqlcmd -ConnectionString $connString -Query $updateQuery
    
    Write-Output "Result: ExitCode=$exitCode"
}
else {
    Write-Output "No patches queued for execution"
}