# Collect-SQLAgentJobs.ps1
# Uses CMS as source of truth
# Version 1.0

param(
    [string]$SqlInstance = "YOURSERVER01",
    [string]$Database = "DBAOpsRepository"
)

Write-Host "=== DBAOps SQL Agent Jobs Collector ===" -ForegroundColor Cyan
Write-Host "Started: $(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')"

# Get servers from CMS - SOURCE OF TRUTH
$cmsQuery = @"
SELECT 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 {
    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = "Server=$SqlInstance;Database=msdb;Integrated Security=True;TrustServerCertificate=True"
    $connection.Open()
    
    $command = $connection.CreateCommand()
    $command.CommandText = $cmsQuery
    $adapter = New-Object System.Data.SqlClient.SqlDataAdapter $command
    $dataset = New-Object System.Data.DataSet
    $adapter.Fill($dataset) | Out-Null
    $connection.Close()
    
    $Servers = $dataset.Tables[0]
    Write-Host "Found $($Servers.Rows.Count) servers from CMS" -ForegroundColor Green
}
catch {
    Write-Host "ERROR getting servers from CMS: $_" -ForegroundColor Red
    exit 1
}

$collected = 0
$errors = 0

foreach ($row in $Servers.Rows) {
    $ServerName = $row.ServerName
    Write-Host "  Collecting from: $ServerName" -ForegroundColor Gray -NoNewline
    
    try {
        $jobQuery = @"
SELECT 
    j.name AS JobName,
    j.enabled AS IsEnabled,
    c.name AS Category,
    j.description AS Description,
    SUSER_SNAME(j.owner_sid) AS Owner,
    j.date_created AS DateCreated,
    j.date_modified AS DateModified,
    (SELECT COUNT(*) FROM msdb.dbo.sysjobsteps s WHERE s.job_id = j.job_id) AS StepCount,
    (SELECT TOP 1 CASE h.run_status WHEN 1 THEN 'Success' WHEN 0 THEN 'Failed' ELSE 'Unknown' END
     FROM msdb.dbo.sysjobhistory h WHERE h.job_id = j.job_id AND h.step_id = 0
     ORDER BY h.run_date DESC, h.run_time DESC) AS LastRunStatus,
    (SELECT TOP 1 msdb.dbo.agent_datetime(h.run_date, h.run_time)
     FROM msdb.dbo.sysjobhistory h WHERE h.job_id = j.job_id AND h.step_id = 0
     ORDER BY h.run_date DESC, h.run_time DESC) AS LastRunTime
FROM msdb.dbo.sysjobs j
LEFT JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
ORDER BY j.name
"@
        
        $conn = New-Object System.Data.SqlClient.SqlConnection
        $conn.ConnectionString = "Server=$ServerName;Database=msdb;Integrated Security=True;TrustServerCertificate=True;Connect Timeout=10"
        $conn.Open()
        
        $cmd = $conn.CreateCommand()
        $cmd.CommandText = $jobQuery
        $cmd.CommandTimeout = 60
        
        $adapt = New-Object System.Data.SqlClient.SqlDataAdapter $cmd
        $ds = New-Object System.Data.DataSet
        $adapt.Fill($ds) | Out-Null
        $conn.Close()
        
        $results = $ds.Tables[0]
        
        if ($results.Rows.Count -gt 0) {
            $repoConn = New-Object System.Data.SqlClient.SqlConnection
            $repoConn.ConnectionString = "Server=$SqlInstance;Database=$Database;Integrated Security=True;TrustServerCertificate=True"
            $repoConn.Open()
            
            # Clear old data for this server
            $deleteCmd = $repoConn.CreateCommand()
            $deleteCmd.CommandText = "DELETE FROM jobs.SQLAgentJobs WHERE ServerName = '$ServerName'"
            $deleteCmd.ExecuteNonQuery() | Out-Null
            
            foreach ($r in $results.Rows) {
                $jobName = ($r.JobName -replace "'", "''")
                $description = if ($r.Description) { ($r.Description -replace "'", "''").Substring(0, [Math]::Min(500, $r.Description.Length)) } else { "" }
                $category = if ($r.Category) { $r.Category } else { "Uncategorized" }
                $owner = if ($r.Owner) { $r.Owner } else { "Unknown" }
                
                $insertSQL = @"
INSERT INTO jobs.SQLAgentJobs (ServerName, JobName, IsEnabled, Category, Description, Owner, StepCount, LastRunStatus, LastRunTime, CollectedAt)
VALUES ('$ServerName', '$jobName', $([int]$r.IsEnabled), '$category', '$description', '$owner', $($r.StepCount), '$($r.LastRunStatus)', $(if ($r.LastRunTime) { "'$($r.LastRunTime)'" } else { "NULL" }), GETDATE())
"@
                
                try {
                    $insertCmd = $repoConn.CreateCommand()
                    $insertCmd.CommandText = $insertSQL
                    $insertCmd.ExecuteNonQuery() | Out-Null
                    $collected++
                } catch { }
            }
            $repoConn.Close()
            Write-Host " - $($results.Rows.Count) jobs" -ForegroundColor Green
        } else {
            Write-Host " - 0 jobs" -ForegroundColor Gray
        }
    }
    catch {
        Write-Host " - ERROR" -ForegroundColor Yellow
        $errors++
    }
}

Write-Host ""
Write-Host "=== Collection Complete ===" -ForegroundColor Cyan
Write-Host "  Jobs collected: $collected"
Write-Host "  Servers with errors: $errors"
