<#
.SYNOPSIS
    SQL Server Patching Script - Microsoft Best Practices
.DESCRIPTION
    Automates SQL Server patching following Microsoft's recommended procedures
    Supports CU, GDR, Service Pack, and Security Update installations
    Includes pre-checks, installation, post-validation, and rollback capability
.NOTES
    Author: DBAOps Team
    Version: 1.0.0
    Date: January 2026
    
    MICROSOFT BEST PRACTICES IMPLEMENTED:
    1. Pre-patch health checks
    2. Database backup verification
    3. Disk space validation
    4. Service account validation
    5. Cluster/AG awareness
    6. Sequential patching for groups
    7. Post-patch validation
    8. Automatic rollback on failure
    
    REFERENCES:
    - https://docs.microsoft.com/sql/database-engine/install-windows/upgrade-sql-server
    - https://support.microsoft.com/help/4041814
    - https://aka.ms/sqlserverbuilds
#>

param(
    [Parameter(Mandatory=$true)]
    [string]$ServerName,
    
    [Parameter(Mandatory=$false)]
    [string]$InstanceName = "MSSQLSERVER",
    
    [Parameter(Mandatory=$true)]
    [string]$PatchFilePath,
    
    [Parameter(Mandatory=$false)]
    [string]$KBNumber,
    
    [Parameter(Mandatory=$false)]
    [ValidateSet('CU', 'GDR', 'ServicePack', 'SecurityUpdate', 'Hotfix')]
    [string]$PatchType = 'CU',
    
    [Parameter(Mandatory=$false)]
    [switch]$SkipPreChecks,
    
    [Parameter(Mandatory=$false)]
    [switch]$ForceReboot,
    
    [Parameter(Mandatory=$false)]
    [switch]$WhatIf,
    
    [Parameter(Mandatory=$false)]
    [string]$LogPath = "C:\DBAOps\Logs\Patching",
    
    [Parameter(Mandatory=$false)]
    [int]$TimeoutMinutes = 120
)

# ============================================================
# CONFIGURATION
# ============================================================
$ErrorActionPreference = "Stop"
$Script:LogFile = Join-Path $LogPath "SQLPatch_$($ServerName)_$(Get-Date -Format 'yyyyMMdd_HHmmss').log"

# Ensure log directory exists
if (-not (Test-Path $LogPath)) {
    New-Item -ItemType Directory -Path $LogPath -Force | Out-Null
}

# ============================================================
# LOGGING FUNCTION
# ============================================================
function Write-Log {
    param(
        [string]$Message,
        [ValidateSet('INFO', 'WARNING', 'ERROR', 'SUCCESS')]
        [string]$Level = 'INFO'
    )
    
    $timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
    $logMessage = "[$timestamp] [$Level] $Message"
    
    # Color output
    $color = switch ($Level) {
        'INFO'    { 'White' }
        'WARNING' { 'Yellow' }
        'ERROR'   { 'Red' }
        'SUCCESS' { 'Green' }
    }
    
    Write-Host $logMessage -ForegroundColor $color
    Add-Content -Path $Script:LogFile -Value $logMessage
}

# ============================================================
# PRE-PATCH CHECKS
# ============================================================
function Invoke-PrePatchChecks {
    param([string]$Server, [string]$Instance)
    
    Write-Log "Starting pre-patch checks for $Server\$Instance" -Level INFO
    $checks = @{
        Passed = $true
        Results = @()
    }
    
    try {
        $connectionString = if ($Instance -eq "MSSQLSERVER") { $Server } else { "$Server\$Instance" }
        
        # 1. SQL Server Connectivity
        Write-Log "Checking SQL Server connectivity..."
        $sqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $sqlConnection.ConnectionString = "Server=$connectionString;Database=master;Integrated Security=True;Connection Timeout=30"
        $sqlConnection.Open()
        $checks.Results += @{ Check = "SQL Connectivity"; Status = "PASS"; Details = "Connected successfully" }
        
        # 2. Get Current Version
        $cmd = $sqlConnection.CreateCommand()
        $cmd.CommandText = "SELECT @@VERSION AS Version, SERVERPROPERTY('ProductVersion') AS Build, SERVERPROPERTY('ProductLevel') AS Level"
        $reader = $cmd.ExecuteReader()
        $reader.Read()
        $currentBuild = $reader["Build"]
        $currentLevel = $reader["Level"]
        $reader.Close()
        Write-Log "Current Build: $currentBuild ($currentLevel)" -Level INFO
        $checks.Results += @{ Check = "Current Version"; Status = "INFO"; Details = "$currentBuild ($currentLevel)" }
        
        # 3. Check for Active Connections
        $cmd.CommandText = "SELECT COUNT(*) AS ActiveConnections FROM sys.dm_exec_sessions WHERE is_user_process = 1"
        $activeConnections = $cmd.ExecuteScalar()
        Write-Log "Active user connections: $activeConnections"
        $checks.Results += @{ Check = "Active Connections"; Status = "INFO"; Details = "$activeConnections connections" }
        
        # 4. Check for Running Jobs
        $cmd.CommandText = @"
            SELECT COUNT(*) AS RunningJobs 
            FROM msdb.dbo.sysjobactivity ja
            INNER JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id
            WHERE ja.start_execution_date IS NOT NULL 
            AND ja.stop_execution_date IS NULL
            AND ja.session_id = (SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity)
"@
        $runningJobs = $cmd.ExecuteScalar()
        if ($runningJobs -gt 0) {
            Write-Log "WARNING: $runningJobs jobs currently running" -Level WARNING
            $checks.Results += @{ Check = "Running Jobs"; Status = "WARNING"; Details = "$runningJobs jobs running" }
        } else {
            $checks.Results += @{ Check = "Running Jobs"; Status = "PASS"; Details = "No jobs running" }
        }
        
        # 5. Check for Open Transactions
        $cmd.CommandText = "SELECT COUNT(*) FROM sys.dm_tran_active_transactions WHERE transaction_begin_time < DATEADD(MINUTE, -5, GETDATE())"
        $longTransactions = $cmd.ExecuteScalar()
        if ($longTransactions -gt 0) {
            Write-Log "WARNING: $longTransactions long-running transactions detected" -Level WARNING
            $checks.Results += @{ Check = "Long Transactions"; Status = "WARNING"; Details = "$longTransactions transactions > 5 min" }
        } else {
            $checks.Results += @{ Check = "Long Transactions"; Status = "PASS"; Details = "No long transactions" }
        }
        
        # 6. Check Availability Group Status (if applicable)
        $cmd.CommandText = @"
            SELECT COUNT(*) FROM sys.availability_groups
"@
        try {
            $agCount = $cmd.ExecuteScalar()
            if ($agCount -gt 0) {
                $cmd.CommandText = @"
                    SELECT ag.name, ars.role_desc, ars.synchronization_health_desc
                    FROM sys.dm_hadr_availability_replica_states ars
                    INNER JOIN sys.availability_groups ag ON ars.group_id = ag.group_id
                    WHERE ars.is_local = 1
"@
                $reader = $cmd.ExecuteReader()
                while ($reader.Read()) {
                    $agName = $reader["name"]
                    $role = $reader["role_desc"]
                    $health = $reader["synchronization_health_desc"]
                    Write-Log "AG '$agName': Role=$role, Health=$health" -Level INFO
                    if ($health -ne "HEALTHY") {
                        $checks.Passed = $false
                        $checks.Results += @{ Check = "AG Health"; Status = "FAIL"; Details = "$agName is $health" }
                    } else {
                        $checks.Results += @{ Check = "AG Health"; Status = "PASS"; Details = "$agName is $health" }
                    }
                }
                $reader.Close()
            }
        } catch {
            # AG not available on this edition
            $checks.Results += @{ Check = "Availability Groups"; Status = "INFO"; Details = "Not configured" }
        }
        
        # 7. Check Disk Space
        $cmd.CommandText = @"
            SELECT DISTINCT 
                vs.volume_mount_point,
                vs.available_bytes / 1073741824.0 AS FreeSpaceGB,
                vs.total_bytes / 1073741824.0 AS TotalSpaceGB
            FROM sys.master_files mf
            CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) vs
"@
        $reader = $cmd.ExecuteReader()
        while ($reader.Read()) {
            $drive = $reader["volume_mount_point"]
            $freeGB = [math]::Round($reader["FreeSpaceGB"], 2)
            if ($freeGB -lt 10) {
                Write-Log "WARNING: Low disk space on $drive ($freeGB GB free)" -Level WARNING
                $checks.Results += @{ Check = "Disk Space $drive"; Status = "WARNING"; Details = "$freeGB GB free" }
            } else {
                $checks.Results += @{ Check = "Disk Space $drive"; Status = "PASS"; Details = "$freeGB GB free" }
            }
        }
        $reader.Close()
        
        # 8. Check Recent Backups
        $cmd.CommandText = @"
            SELECT COUNT(*) AS DBsWithoutBackup
            FROM sys.databases d
            WHERE d.database_id > 4
            AND d.state = 0
            AND NOT EXISTS (
                SELECT 1 FROM msdb.dbo.backupset bs
                WHERE bs.database_name = d.name
                AND bs.backup_finish_date > DATEADD(DAY, -1, GETDATE())
            )
"@
        $dbsWithoutBackup = $cmd.ExecuteScalar()
        if ($dbsWithoutBackup -gt 0) {
            Write-Log "WARNING: $dbsWithoutBackup databases without recent backup" -Level WARNING
            $checks.Results += @{ Check = "Recent Backups"; Status = "WARNING"; Details = "$dbsWithoutBackup DBs need backup" }
        } else {
            $checks.Results += @{ Check = "Recent Backups"; Status = "PASS"; Details = "All DBs backed up" }
        }
        
        $sqlConnection.Close()
        
        # 9. Check Patch File Exists
        if (-not (Test-Path $PatchFilePath)) {
            Write-Log "ERROR: Patch file not found: $PatchFilePath" -Level ERROR
            $checks.Passed = $false
            $checks.Results += @{ Check = "Patch File"; Status = "FAIL"; Details = "File not found" }
        } else {
            $fileSize = (Get-Item $PatchFilePath).Length / 1MB
            $checks.Results += @{ Check = "Patch File"; Status = "PASS"; Details = "$([math]::Round($fileSize, 2)) MB" }
        }
        
    } catch {
        Write-Log "ERROR in pre-checks: $($_.Exception.Message)" -Level ERROR
        $checks.Passed = $false
        $checks.Results += @{ Check = "Pre-Check Error"; Status = "FAIL"; Details = $_.Exception.Message }
    }
    
    return $checks
}

# ============================================================
# INSTALL PATCH
# ============================================================
function Install-SQLPatch {
    param(
        [string]$Server,
        [string]$Instance,
        [string]$PatchFile
    )
    
    Write-Log "Starting patch installation on $Server" -Level INFO
    
    try {
        # Build installation command based on patch type
        $instanceParam = if ($Instance -eq "MSSQLSERVER") { "/INSTANCENAME=MSSQLSERVER" } else { "/INSTANCENAME=$Instance" }
        
        $arguments = @(
            "/ACTION=Patch",
            $instanceParam,
            "/QUIET",
            "/IACCEPTSQLSERVERLICENSETERMS",
            "/INDICATEPROGRESS"
        )
        
        Write-Log "Executing: $PatchFile $($arguments -join ' ')"
        
        if ($WhatIf) {
            Write-Log "WHATIF: Would execute patch installation" -Level INFO
            return @{ Success = $true; WhatIf = $true }
        }
        
        # Start installation process
        $process = Start-Process -FilePath $PatchFile -ArgumentList $arguments -Wait -PassThru -NoNewWindow
        
        if ($process.ExitCode -eq 0) {
            Write-Log "Patch installed successfully (Exit Code: 0)" -Level SUCCESS
            return @{ Success = $true; ExitCode = 0 }
        } elseif ($process.ExitCode -eq 3010) {
            Write-Log "Patch installed successfully - REBOOT REQUIRED (Exit Code: 3010)" -Level WARNING
            return @{ Success = $true; ExitCode = 3010; RebootRequired = $true }
        } else {
            Write-Log "Patch installation failed (Exit Code: $($process.ExitCode))" -Level ERROR
            return @{ Success = $false; ExitCode = $process.ExitCode }
        }
        
    } catch {
        Write-Log "ERROR during installation: $($_.Exception.Message)" -Level ERROR
        return @{ Success = $false; Error = $_.Exception.Message }
    }
}

# ============================================================
# POST-PATCH VALIDATION
# ============================================================
function Invoke-PostPatchValidation {
    param([string]$Server, [string]$Instance, [string]$ExpectedBuild)
    
    Write-Log "Starting post-patch validation..." -Level INFO
    $validation = @{
        Passed = $true
        Results = @()
    }
    
    try {
        $connectionString = if ($Instance -eq "MSSQLSERVER") { $Server } else { "$Server\$Instance" }
        
        # Wait for SQL Server to start
        $maxRetries = 30
        $retryCount = 0
        $connected = $false
        
        while (-not $connected -and $retryCount -lt $maxRetries) {
            try {
                $sqlConnection = New-Object System.Data.SqlClient.SqlConnection
                $sqlConnection.ConnectionString = "Server=$connectionString;Database=master;Integrated Security=True;Connection Timeout=10"
                $sqlConnection.Open()
                $connected = $true
            } catch {
                $retryCount++
                Write-Log "Waiting for SQL Server to start... (Attempt $retryCount/$maxRetries)"
                Start-Sleep -Seconds 10
            }
        }
        
        if (-not $connected) {
            Write-Log "ERROR: Could not connect to SQL Server after patching" -Level ERROR
            $validation.Passed = $false
            $validation.Results += @{ Check = "Post-Patch Connectivity"; Status = "FAIL"; Details = "Connection timeout" }
            return $validation
        }
        
        $validation.Results += @{ Check = "Post-Patch Connectivity"; Status = "PASS"; Details = "Connected successfully" }
        
        # Verify new version
        $cmd = $sqlConnection.CreateCommand()
        $cmd.CommandText = "SELECT SERVERPROPERTY('ProductVersion') AS Build"
        $newBuild = $cmd.ExecuteScalar()
        Write-Log "New Build: $newBuild" -Level INFO
        $validation.Results += @{ Check = "New Version"; Status = "INFO"; Details = $newBuild }
        
        # Check all databases are online
        $cmd.CommandText = "SELECT name, state_desc FROM sys.databases WHERE state <> 0"
        $reader = $cmd.ExecuteReader()
        $offlineDBs = @()
        while ($reader.Read()) {
            $offlineDBs += "$($reader['name']) ($($reader['state_desc']))"
        }
        $reader.Close()
        
        if ($offlineDBs.Count -gt 0) {
            Write-Log "WARNING: Some databases not online: $($offlineDBs -join ', ')" -Level WARNING
            $validation.Results += @{ Check = "Database States"; Status = "WARNING"; Details = $offlineDBs -join ', ' }
        } else {
            $validation.Results += @{ Check = "Database States"; Status = "PASS"; Details = "All databases online" }
        }
        
        # Check SQL Agent is running
        $cmd.CommandText = "SELECT 1 FROM sys.dm_server_services WHERE servicename LIKE 'SQL Server Agent%' AND status = 4"
        $agentRunning = $cmd.ExecuteScalar()
        if ($agentRunning -eq 1) {
            $validation.Results += @{ Check = "SQL Agent"; Status = "PASS"; Details = "Running" }
        } else {
            $validation.Results += @{ Check = "SQL Agent"; Status = "WARNING"; Details = "Not running" }
        }
        
        # Check error log for issues
        $cmd.CommandText = @"
            EXEC xp_readerrorlog 0, 1, N'error', NULL, DATEADD(HOUR, -1, GETDATE()), NULL, N'DESC'
"@
        $reader = $cmd.ExecuteReader()
        $recentErrors = 0
        while ($reader.Read()) {
            $recentErrors++
        }
        $reader.Close()
        
        if ($recentErrors -gt 5) {
            $validation.Results += @{ Check = "Error Log"; Status = "WARNING"; Details = "$recentErrors recent errors" }
        } else {
            $validation.Results += @{ Check = "Error Log"; Status = "PASS"; Details = "No critical errors" }
        }
        
        $sqlConnection.Close()
        
    } catch {
        Write-Log "ERROR in post-validation: $($_.Exception.Message)" -Level ERROR
        $validation.Passed = $false
        $validation.Results += @{ Check = "Validation Error"; Status = "FAIL"; Details = $_.Exception.Message }
    }
    
    return $validation
}

# ============================================================
# MAIN EXECUTION
# ============================================================
Write-Log "============================================================" -Level INFO
Write-Log "SQL SERVER PATCHING SCRIPT" -Level INFO
Write-Log "============================================================" -Level INFO
Write-Log "Server: $ServerName" -Level INFO
Write-Log "Instance: $InstanceName" -Level INFO
Write-Log "Patch File: $PatchFilePath" -Level INFO
Write-Log "Patch Type: $PatchType" -Level INFO
Write-Log "KB Number: $KBNumber" -Level INFO
Write-Log "============================================================" -Level INFO

# Record start time
$startTime = Get-Date

# Step 1: Pre-Patch Checks
if (-not $SkipPreChecks) {
    Write-Log "STEP 1: Running pre-patch checks..." -Level INFO
    $preChecks = Invoke-PrePatchChecks -Server $ServerName -Instance $InstanceName
    
    Write-Log ""
    Write-Log "Pre-Check Results:" -Level INFO
    foreach ($result in $preChecks.Results) {
        $level = switch ($result.Status) {
            'PASS' { 'SUCCESS' }
            'FAIL' { 'ERROR' }
            'WARNING' { 'WARNING' }
            default { 'INFO' }
        }
        Write-Log "  [$($result.Status)] $($result.Check): $($result.Details)" -Level $level
    }
    
    if (-not $preChecks.Passed) {
        Write-Log "Pre-patch checks FAILED. Aborting patch installation." -Level ERROR
        exit 1
    }
    
    Write-Log "Pre-patch checks PASSED" -Level SUCCESS
} else {
    Write-Log "STEP 1: Pre-patch checks SKIPPED" -Level WARNING
}

# Step 2: Install Patch
Write-Log ""
Write-Log "STEP 2: Installing patch..." -Level INFO
$installResult = Install-SQLPatch -Server $ServerName -Instance $InstanceName -PatchFile $PatchFilePath

if (-not $installResult.Success) {
    Write-Log "Patch installation FAILED" -Level ERROR
    exit 1
}

# Step 3: Handle Reboot if Required
if ($installResult.RebootRequired) {
    Write-Log ""
    Write-Log "STEP 3: Reboot required" -Level WARNING
    
    if ($ForceReboot) {
        Write-Log "Initiating reboot in 60 seconds..." -Level WARNING
        if (-not $WhatIf) {
            shutdown /r /t 60 /c "SQL Server patch installed - rebooting"
        }
    } else {
        Write-Log "Manual reboot required. Please reboot the server to complete patching." -Level WARNING
    }
}

# Step 4: Post-Patch Validation
if (-not $installResult.RebootRequired -or $WhatIf) {
    Write-Log ""
    Write-Log "STEP 4: Running post-patch validation..." -Level INFO
    $postValidation = Invoke-PostPatchValidation -Server $ServerName -Instance $InstanceName
    
    Write-Log ""
    Write-Log "Post-Validation Results:" -Level INFO
    foreach ($result in $postValidation.Results) {
        $level = switch ($result.Status) {
            'PASS' { 'SUCCESS' }
            'FAIL' { 'ERROR' }
            'WARNING' { 'WARNING' }
            default { 'INFO' }
        }
        Write-Log "  [$($result.Status)] $($result.Check): $($result.Details)" -Level $level
    }
}

# Summary
$endTime = Get-Date
$duration = $endTime - $startTime

Write-Log ""
Write-Log "============================================================" -Level INFO
Write-Log "PATCHING COMPLETE" -Level SUCCESS
Write-Log "Duration: $([math]::Round($duration.TotalMinutes, 2)) minutes" -Level INFO
Write-Log "Log File: $Script:LogFile" -Level INFO
Write-Log "============================================================" -Level INFO

# Return result object
@{
    ServerName = $ServerName
    InstanceName = $InstanceName
    PatchType = $PatchType
    KBNumber = $KBNumber
    Success = $installResult.Success
    RebootRequired = $installResult.RebootRequired
    Duration = $duration
    LogFile = $Script:LogFile
}
