<#
.SYNOPSIS
    Download SQL Server patches from Microsoft
.DESCRIPTION
    Downloads CU patches based on KB number from patching.PatchDownloadURLs table
#>
param(
    [Parameter(Mandatory=$true)]
    [string]$KBNumber,
    [string]$DestinationPath = "E:\Patches"
)

$SqlServer = "YOURSERVER"
$Database = "JBRhoylleRepository"

# Get download info from table
$query = "SELECT SQLVersion, DownloadURL, FileName FROM patching.PatchDownloadURLs WHERE KBNumber = '$KBNumber' AND IsActive = 1"
$patchInfo = Invoke-Sqlcmd -ServerInstance $SqlServer -Database $Database -Query $query -TrustServerCertificate

if (-not $patchInfo) {
    Write-Error "No download URL found for $KBNumber"
    exit 1
}

$sqlVersion = "SQL$($patchInfo.SQLVersion)"
$downloadUrl = $patchInfo.DownloadURL
$fileName = $patchInfo.FileName
$targetFolder = Join-Path $DestinationPath $sqlVersion
$targetFile = Join-Path $targetFolder $fileName

# Create folder if not exists
if (-not (Test-Path $targetFolder)) {
    New-Item -ItemType Directory -Path $targetFolder -Force | Out-Null
    Write-Host "Created folder: $targetFolder"
}

# Check if already downloaded
if (Test-Path $targetFile) {
    Write-Host "Patch already exists: $targetFile"
    return @{ Success = $true; FilePath = $targetFile; Downloaded = $false }
}

# Download patch
Write-Host "Downloading $KBNumber from Microsoft..."
Write-Host "URL: $downloadUrl"
Write-Host "Destination: $targetFile"

try {
    $ProgressPreference = 'SilentlyContinue'  # Speed up download
    Invoke-WebRequest -Uri $downloadUrl -OutFile $targetFile -UseBasicParsing
    $ProgressPreference = 'Continue'
    
    if (Test-Path $targetFile) {
        $size = [math]::Round((Get-Item $targetFile).Length / 1MB, 2)
        Write-Host "Download complete! Size: $size MB"
        return @{ Success = $true; FilePath = $targetFile; Downloaded = $true }
    } else {
        Write-Error "Download failed - file not found"
        return @{ Success = $false; FilePath = $null; Downloaded = $false }
    }
} catch {
    Write-Error "Download failed: $_"
    return @{ Success = $false; FilePath = $null; Downloaded = $false; Error = $_.Exception.Message }
}
