#Requires -Modules SqlServer

$Script:DBAOpsConfig = @{
    CMSServer        = "AZDBAOPSSQL01"
    RepositoryDB     = "DBAOpsRepository"
    ConnectionTimeout = 10
}

function Get-CMSRegisteredServers {
    param([string]$CMSServer = $Script:DBAOpsConfig.CMSServer)
    $Query = "SELECT DISTINCT server_name AS ServerName FROM msdb.dbo.sysmanagement_shared_registered_servers_internal WHERE server_name NOT LIKE '%\%' AND server_name <> @@SERVERNAME ORDER BY server_name;"
    try {
        $Servers = Invoke-Sqlcmd -ServerInstance $CMSServer -Query $Query -TrustServerCertificate -ErrorAction Stop
        return $Servers.ServerName
    } catch {
        Write-Host "Failed to get servers from CMS: $_" -ForegroundColor Red
        return $null
    }
}

function Test-ServerConnection {
    param([string]$ServerName)
    try {
        $null = Invoke-Sqlcmd -ServerInstance $ServerName -Query "SELECT 1" -ConnectionTimeout 10 -TrustServerCertificate -ErrorAction Stop
        return $true
    } catch { return $false }
}

function Invoke-DBAOpsJobComplianceScan {
    param([bool]$AutoFix = $false)
    
    Write-Host "Starting Job Compliance Scan (AutoFix: $AutoFix)" -ForegroundColor Cyan
    
    $Servers = Get-CMSRegisteredServers
    if ($null -eq $Servers) { Write-Host "No servers found" -ForegroundColor Red; return }
    
    Write-Host "Found $($Servers.Count) servers in CMS" -ForegroundColor Green
    
    $CheckQuery = @"
SELECT j.name AS JobName, js.step_id AS StepID, js.step_name AS StepName, js.command AS Command
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobsteps js ON j.job_id = js.job_id
WHERE j.name = 'DBAOps - Daily Statistics Update'
  AND js.command LIKE '%@OnlyModifiedStatistics%'
  AND js.command LIKE '%@StatisticsModificationLevel%';
"@
    
    $FixCommand = @"
EXECUTE [master].[dbo].[IndexOptimize]
    @Databases = 'USER_DATABASES',
    @FragmentationLow = NULL, @FragmentationMedium = NULL, @FragmentationHigh = NULL,
    @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @MinNumberOfPages = 1000, @MaxNumberOfPages = NULL,
    @SortInTempdb = 'N', @MaxDOP = NULL, @FillFactor = NULL, @PadIndex = NULL, @LOBCompaction = 'Y',
    @UpdateStatistics = 'ALL', @StatisticsModificationLevel = 15, @StatisticsSample = NULL, @StatisticsResample = 'N',
    @PartitionLevel = 'Y', @MSShippedObjects = 'N',
    @Indexes = 'ALL_INDEXES, -%.%.action_trigger_email.%, -%.%.grs_import_citibank_paid.%, -%.%.grs_seje_work.%, -%.%.invoice_work_transaction.%, -%.%.MSnapshotdeliveryprogress.%, -%.%.security_current_users.%',
    @TimeLimit = 7200, @Delay = NULL, @WaitAtLowPriorityMaxDuration = NULL, @WaitAtLowPriorityAbortAfterWait = NULL,
    @Resumable = 'N', @AvailabilityGroups = NULL, @LockTimeout = NULL, @LockMessageSeverity = 16,
    @StringDelimiter = ',', @DatabaseOrder = NULL, @DatabasesInParallel = 'N', @ExecuteAsUser = NULL,
    @LogToTable = 'Y', @Execute = 'Y'
"@
    
    $Stats = @{ Scanned = 0; Affected = 0; Fixed = 0; Failed = 0; Unreachable = 0 }
    $Results = @()
    
    foreach ($Server in $Servers) {
        $Stats.Scanned++
        Write-Host "  [$($Stats.Scanned)/$($Servers.Count)] $Server... " -NoNewline
        
        if (!(Test-ServerConnection -ServerName $Server)) {
            Write-Host "UNREACHABLE" -ForegroundColor Red
            $Stats.Unreachable++
            continue
        }
        
        try {
            $Result = Invoke-Sqlcmd -ServerInstance $Server -Query $CheckQuery -TrustServerCertificate -ErrorAction Stop
            
            if ($null -ne $Result) {
                $Stats.Affected++
                Write-Host "NON-COMPLIANT" -ForegroundColor Red
                
                if ($AutoFix) {
                    $FixQuery = "USE msdb; EXEC dbo.sp_update_jobstep @job_name = N'DBAOps - Daily Statistics Update', @step_id = $($Result.StepID), @command = N'$($FixCommand.Replace("'", "''"))';"
                    try {
                        Invoke-Sqlcmd -ServerInstance $Server -Query $FixQuery -TrustServerCertificate -ErrorAction Stop
                        Write-Host "    -> FIXED" -ForegroundColor Green
                        $Stats.Fixed++
                    } catch {
                        Write-Host "    -> FIX FAILED: $_" -ForegroundColor Red
                        $Stats.Failed++
                    }
                }
                $Results += [PSCustomObject]@{ Server = $Server; Status = "NonCompliant"; StepID = $Result.StepID }
            } else {
                Write-Host "OK" -ForegroundColor Green
                $Results += [PSCustomObject]@{ Server = $Server; Status = "Compliant"; StepID = $null }
            }
        } catch {
            Write-Host "ERROR: $_" -ForegroundColor Yellow
        }
    }
    
    Write-Host ""
    Write-Host "========================================" -ForegroundColor Cyan
    Write-Host "  SCAN COMPLETE" -ForegroundColor Cyan
    Write-Host "========================================" -ForegroundColor Cyan
    Write-Host "  Scanned:     $($Stats.Scanned)"
    Write-Host "  Affected:    $($Stats.Affected)" -ForegroundColor $(if ($Stats.Affected -gt 0) { 'Yellow' } else { 'Green' })
    Write-Host "  Fixed:       $($Stats.Fixed)" -ForegroundColor Green
    Write-Host "  Failed:      $($Stats.Failed)" -ForegroundColor $(if ($Stats.Failed -gt 0) { 'Red' } else { 'Green' })
    Write-Host "  Unreachable: $($Stats.Unreachable)" -ForegroundColor $(if ($Stats.Unreachable -gt 0) { 'Yellow' } else { 'Green' })
    Write-Host "========================================" -ForegroundColor Cyan
    
    return [PSCustomObject]@{ Statistics = $Stats; Results = $Results }
}

Export-ModuleMember -Function 'Invoke-DBAOpsJobComplianceScan'
