<#
.SYNOPSIS
  DBAOps Sovereign™ — Fleet Collector (telemetry + fleet coverage facts)
.DESCRIPTION
  Registers instances (optional, requires admin JWT), collects live telemetry, and/or gathers backup / HADR / patch / inventory / SQL Agent / volume stats for POST /api/ingest/fleet-facts.
  Supports both Windows Integrated Authentication (default) and SQL Server Authentication via -SqlCredential.
.EXAMPLE
  .\DBAOps-Sovereign-Collector.ps1 -ServerList "localhost" -SovereignApi "http://localhost:3041" -RegisterIfMissing -BearerToken $token -SubmitTelemetry
.EXAMPLE
  .\DBAOps-Sovereign-Collector.ps1 -ServerList "SQL01" -SovereignApi "http://localhost:3041" -SubmitFleetFacts -IngestKey $env:SOVEREIGN_INGEST_KEY -RegisterIfMissing -BearerToken $token
.EXAMPLE
  .\DBAOps-Sovereign-Collector.ps1 -ServerList "localhost" -SovereignApi "http://localhost:3041" -SubmitAll
.EXAMPLE
  # SQL auth (explicit opt-in — integrated Windows auth is the default)
  $cred = Get-Credential
  .\DBAOps-Sovereign-Collector.ps1 -ServerList "SQL01" -SqlCredential $cred -SubmitAll -IngestKey $env:SOVEREIGN_INGEST_KEY
#>

[CmdletBinding()]
param(
    [string[]]$ServerList = @("localhost"),
    [string]$SovereignApi = "http://localhost:3041",
    [switch]$SubmitTelemetry,
    [switch]$SubmitFleetFacts,
    [switch]$SubmitWaitStats,
    [switch]$SubmitDatabaseHealth,
    [switch]$SubmitQueryStats,
    [switch]$SubmitTechnologyDetection,
    [switch]$SubmitAgentStatus,
    [switch]$SubmitSecurity,
    [switch]$SubmitAll,
    [switch]$RegisterIfMissing,
    [string]$IngestKey = "",
    [string]$BearerToken = "",
    [int]$RpoHoursDefault = 24,
    [System.Management.Automation.PSCredential]$SqlCredential
)

$ErrorActionPreference = "Stop"
$script:ApiBase = $SovereignApi.TrimEnd('/')
$script:SqlCredential = $SqlCredential
$script:SqlEncrypt = if ($env:SOVEREIGN_SQL_ENCRYPT -eq 'true') { 'True' } else { 'False' }
$script:SqlTrustCert = if ($env:SOVEREIGN_SQL_TRUST_SERVER_CERT -eq 'false') { 'False' } else { 'True' }

function Get-SqlConnectionString {
    param([string]$ServerName, [string]$DatabaseName = 'master')
    if ($script:SqlCredential) {
        $netCred = $script:SqlCredential.GetNetworkCredential()
        return "Server=$ServerName;Database=$DatabaseName;User Id=$($netCred.UserName);Password=$($netCred.Password);Connection Timeout=12;Encrypt=$($script:SqlEncrypt);TrustServerCertificate=$($script:SqlTrustCert);"
    }
    return "Server=$ServerName;Database=$DatabaseName;Trusted_Connection=Yes;Connection Timeout=12;Encrypt=$($script:SqlEncrypt);TrustServerCertificate=$($script:SqlTrustCert);"
}

function Invoke-SqlDataTable {
    param([string]$ServerName, [string]$DatabaseName = "master", [string]$Query, [switch]$Quiet)
    try {
        $conn = New-Object System.Data.SqlClient.SqlConnection
        $conn.ConnectionString = Get-SqlConnectionString -ServerName $ServerName -DatabaseName $DatabaseName
        $conn.Open()
        $cmd = $conn.CreateCommand()
        $cmd.CommandText = $Query
        $adapter = New-Object System.Data.SqlClient.SqlDataAdapter $cmd
        $dt = New-Object System.Data.DataTable
        [void]$adapter.Fill($dt)
        $conn.Close()
        return ,$dt
    } catch {
        if (-not $Quiet) {
            Write-Warning "  SQL query failed on ${ServerName}: $($_.Exception.Message)"
        }
        return $null
    }
}

function Get-SqlServerInfo {
    param([string]$ServerName)
    $dt = Invoke-SqlDataTable -ServerName $ServerName -Query @"
SELECT 
    CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(256)) AS ServerName,
    CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(64)) AS Version,
    CAST(SERVERPROPERTY('Edition') AS NVARCHAR(128)) AS Edition,
    (SELECT COUNT(*) FROM sys.databases WHERE state = 0) AS DatabaseCount
"@
    if ($null -eq $dt -or $dt.Rows.Count -lt 1) { return $null }
    return $dt.Rows[0]
}

function Get-PerfCounterMap {
    param([string]$ServerName)
    $dt = Invoke-SqlDataTable -ServerName $ServerName -Query @"
SELECT counter_name AS CounterName, instance_name AS InstanceName, cntr_value AS CntrValue
FROM sys.dm_os_performance_counters
WHERE instance_name IN (N'', N'_Total')
  AND counter_name IN (
    N'Page reads/sec', N'Page writes/sec', N'Lazy writes/sec', N'Page splits/sec', N'Read-ahead pages/sec',
    N'Full scans/sec', N'Index searches/sec', N'SQL Compilations/sec', N'SQL Re-Compilations/sec',
    N'Buffer cache hit ratio', N'Buffer cache hit ratio base',
    N'Plan Cache Hit Ratio', N'Plan Cache Hit Ratio base',
    N'Checkpoint pages/sec', N'Log Flushes/sec', N'Log Flush Wait Time',
    N'Disk Read Bytes/sec', N'Disk Write Bytes/sec', N'Average Disk sec/Read', N'Average Disk sec/Write',
    N'Processes blocked', N'Logins/sec', N'Logouts/sec', N'Latch Waits/sec',
    N'Workfiles Created/sec', N'Worktables Created/sec', N'Forwarded Records/sec',
    N'Sort Overflows/sec', N'Hash Warnings/sec', N'Memory Grants Outstanding', N'Memory Grants Pending',
    N'Granted Workspace Memory (KB)', N'User Connections', N'Transactions/sec', N'Lock Requests/sec',
    N'Logins/sec', N'Logouts/sec'
  );
"@
    $map = @{}
    if ($null -eq $dt) { return $map }
    foreach ($r in $dt.Rows) {
        $key = [string]$r.CounterName
        if (-not $map.ContainsKey($key) -or [string]$r.InstanceName -eq '') {
            $map[$key] = [double]$r.CntrValue
        }
    }
    return $map
}

function Get-RatioPct {
    param($Map, [string]$NumKey, [string]$DenKey)
    if (-not $Map.ContainsKey($NumKey) -or -not $Map.ContainsKey($DenKey)) { return $null }
    $den = [double]$Map[$DenKey]
    if ($den -le 0) { return $null }
    return [math]::Round(100.0 * [double]$Map[$NumKey] / $den, 2)
}

function Get-WaitRateAggregates {
    param([string]$ServerName)
    $dt = Invoke-SqlDataTable -ServerName $ServerName -Query @"
DECLARE @uptimeSec BIGINT = DATEDIFF(SECOND, (SELECT sqlserver_start_time FROM sys.dm_os_sys_info), GETDATE());
SELECT @uptimeSec AS UptimeSec,
  SUM(CASE WHEN wait_type LIKE 'PAGEIOLATCH%' OR wait_type IN (
    N'ASYNC_IO_COMPLETION', N'IO_COMPLETION', N'WRITELOG', N'LOGMGR_QUEUE', N'LOGMGR_RESERVE_APPEND',
    N'BACKUPIO', N'BACKUPBUFFER', N'ASYNC_NETWORK_IO', N'NET_WAITFOR_PACKET'
  ) THEN wait_time_ms ELSE 0 END) AS WaitIoMs,
  SUM(CASE WHEN wait_type LIKE 'LCK_%' OR wait_type LIKE 'PAGELATCH_%' THEN wait_time_ms ELSE 0 END) AS WaitLockMs,
  SUM(CASE WHEN wait_type IN (
    N'RESOURCE_SEMAPHORE', N'CMEMTHREAD', N'RESULT_SET_SPOOL', N'RESOURCE_SEMAPHORE_QUERY_COMPILE'
  ) THEN wait_time_ms ELSE 0 END) AS WaitMemoryMs,
  SUM(CASE WHEN wait_type IN (N'CXPACKET', N'CXCONSUMER', N'CXSYNC_PORT', N'CXSYNC_CONSUMER') THEN wait_time_ms ELSE 0 END) AS WaitParallelMs
FROM sys.dm_os_wait_stats
WHERE @uptimeSec > 0;
"@
    if ($null -eq $dt -or $dt.Rows.Count -lt 1) { return @{} }
    $r = $dt.Rows[0]
    $sec = [double]$r.UptimeSec
    if ($sec -le 0) { return @{} }
    return @{
        WaitIoMsPerSec         = [math]::Round([double]$r.WaitIoMs / $sec, 2)
        WaitLockMsPerSec       = [math]::Round([double]$r.WaitLockMs / $sec, 2)
        WaitMemoryMsPerSec     = [math]::Round([double]$r.WaitMemoryMs / $sec, 2)
        WaitCpuParallelMsPerSec = [math]::Round([double]$r.WaitParallelMs / $sec, 2)
    }
}

function Get-BackupTranMetrics {
    param([string]$ServerName)
    $dt = Invoke-SqlDataTable -ServerName $ServerName -Query @"
SELECT
  (SELECT MAX(DATEDIFF(MINUTE, bs.backup_finish_date, GETUTCDATE()))
   FROM msdb.dbo.backupset bs
   INNER JOIN sys.databases d ON d.name = bs.database_name
   WHERE bs.type = 'D' AND d.database_id > 4 AND d.state = 0) AS BackupAgeWorstMinutes,
  (SELECT MAX(DATEDIFF(MINUTE, dt.database_transaction_begin_time, GETUTCDATE()))
   FROM sys.dm_tran_database_transactions dt
   WHERE dt.database_transaction_begin_time IS NOT NULL) AS OldestOpenTranMinutes;
"@
    if ($null -eq $dt -or $dt.Rows.Count -lt 1) { return @{} }
    $r = $dt.Rows[0]
    return @{
        BackupAgeWorstMinutes   = if ($r.BackupAgeWorstMinutes -is [DBNull]) { $null } else { [int]$r.BackupAgeWorstMinutes }
        OldestOpenTranMinutes   = if ($r.OldestOpenTranMinutes -is [DBNull]) { $null } else { [double]$r.OldestOpenTranMinutes }
    }
}

function Get-SqlTelemetry {
    param([string]$ServerName)
    $dt = Invoke-SqlDataTable -ServerName $ServerName -Query @"
;WITH RingBufferCPU AS (
    SELECT TOP (1)
        record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLCpuPercent
    FROM (
        SELECT CAST(record AS XML) AS record
        FROM sys.dm_os_ring_buffers
        WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
          AND record LIKE N'%<SystemHealth>%'
    ) AS rb
    ORDER BY record.value('(./Record/@id)[1]', 'int') DESC
),
Perf AS (
    SELECT
      (SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1) AS ActiveSessions,
      (SELECT COUNT(*) FROM sys.dm_exec_requests WHERE blocking_session_id <> 0) AS BlockingSessions,
      (SELECT CAST(physical_memory_in_use_kb / 1024.0 AS INT) FROM sys.dm_os_process_memory) AS SqlMemoryUsedMB,
      (SELECT AVG(CAST(cntr_value AS FLOAT)) FROM sys.dm_os_performance_counters
        WHERE counter_name = N'Batch Requests/sec' AND instance_name = N'') AS BatchRequestsPerSec,
      (SELECT AVG(wait_time_ms / 1000.0) FROM sys.dm_os_wait_stats
        WHERE wait_type = N'SOS_SCHEDULER_YIELD') AS AvgSignalWaitMs,
      (SELECT SQLCpuPercent FROM RingBufferCPU) AS CpuPercent,
      (SELECT TOP 1 cntr_value FROM sys.dm_os_performance_counters
        WHERE counter_name = N'Page life expectancy' AND object_name LIKE N'%Buffer Manager%') AS PageLifeExpectancySec,
      (SELECT TOP 1 cntr_value * 100.0 / NULLIF(
        (SELECT cntr_value FROM sys.dm_os_performance_counters b
         WHERE b.object_name = p.object_name AND b.counter_name = N'Buffer cache hit ratio base'
           AND b.instance_name = p.instance_name), 0)
       FROM sys.dm_os_performance_counters p
       WHERE p.counter_name = N'Buffer cache hit ratio' AND p.object_name LIKE N'%Buffer Manager%') AS BufferCacheHitRatioPct,
      (SELECT TOP 1 cntr_value FROM sys.dm_os_performance_counters
        WHERE counter_name = N'Deadlocks/sec' AND instance_name = N'') AS DeadlocksPerSec,
      (SELECT TOP 1 cntr_value FROM sys.dm_os_performance_counters
        WHERE counter_name = N'User Connections' AND instance_name = N'') AS UserConnections,
      (SELECT TOP 1 cntr_value FROM sys.dm_os_performance_counters
        WHERE counter_name = N'Processes blocked' AND instance_name = N'') AS ProcessesBlocked,
      (SELECT COUNT(*) FROM sys.dm_os_schedulers WHERE status = N'VISIBLE ONLINE') AS SchedulerCountOnline,
      (SELECT SUM(runnable_tasks_count) FROM sys.dm_os_schedulers WHERE status = N'VISIBLE ONLINE') AS RunnableTasksCount,
      (SELECT COUNT(*) FROM sys.dm_os_workers) AS ActiveWorkers,
      (SELECT max_workers_count FROM sys.dm_os_sys_info) AS MaxWorkers,
      (SELECT cpu_count FROM sys.dm_os_sys_info) AS CpuCountLogical,
      (SELECT CAST(total_physical_memory_kb / 1024 AS BIGINT) FROM sys.dm_os_sys_memory) AS HostTotalMemoryMB,
      (SELECT CAST(available_physical_memory_kb / 1024 AS BIGINT) FROM sys.dm_os_sys_memory) AS HostAvailableMemoryMB,
      (SELECT CAST(value_in_use AS BIGINT) FROM sys.configurations WHERE name = N'max server memory (MB)') AS MaxServerMemoryMB,
      (SELECT CAST(cntr_value AS BIGINT) FROM sys.dm_os_performance_counters
        WHERE counter_name = N'Target Server Memory (KB)' AND instance_name = N'') / 1024 AS TargetServerMemoryMB,
      (SELECT CAST(cntr_value AS BIGINT) FROM sys.dm_os_performance_counters
        WHERE counter_name = N'Total Server Memory (KB)' AND instance_name = N'') / 1024 AS TotalServerMemoryMB,
      (SELECT TOP 1 CAST(cntr_value AS INT) FROM sys.dm_os_performance_counters
        WHERE counter_name = N'Memory Grants Pending' AND instance_name = N'') AS PendingMemoryGrantsCount,
      (SELECT SUM(user_object_reserved_page_count) * 8.0 / 1024 FROM sys.dm_db_file_space_usage) AS TempdbUserObjectsMB,
      (SELECT SUM(internal_object_reserved_page_count) * 8.0 / 1024 FROM sys.dm_db_file_space_usage) AS TempdbInternalObjectsMB,
      (SELECT SUM(version_store_reserved_page_count) * 8.0 / 1024 FROM sys.dm_db_file_space_usage) AS TempdbVersionStoreMB,
      (SELECT CAST(value AS INT) FROM sys.configurations WHERE name = N'max degree of parallelism') AS MaxDopConfigured,
      (SELECT CAST(value AS INT) FROM sys.configurations WHERE name = N'cost threshold for parallelism') AS CostThresholdForParallelism,
      (SELECT COUNT(*) FROM sys.databases WHERE database_id > 4 AND state = 0) AS UserDatabaseCount
)
SELECT * FROM Perf;
"@
    if ($null -eq $dt -or $dt.Rows.Count -lt 1) { return $null }
    $row = $dt.Rows[0]
    $counters = Get-PerfCounterMap -ServerName $ServerName
    $waits = Get-WaitRateAggregates -ServerName $ServerName
    $backup = Get-BackupTranMetrics -ServerName $ServerName

    $extra = [ordered]@{
        PlanCacheHitRatioPct      = Get-RatioPct -Map $counters -NumKey 'Plan Cache Hit Ratio' -DenKey 'Plan Cache Hit Ratio base'
        SqlCompilationsPerSec     = if ($counters.ContainsKey('SQL Compilations/sec')) { $counters['SQL Compilations/sec'] } else { $null }
        SqlRecompilationsPerSec   = if ($counters.ContainsKey('SQL Re-Compilations/sec')) { $counters['SQL Re-Compilations/sec'] } else { $null }
        TransactionsPerSec        = if ($counters.ContainsKey('Transactions/sec')) { $counters['Transactions/sec'] } else { $null }
        LockRequestsPerSec        = if ($counters.ContainsKey('Lock Requests/sec')) { $counters['Lock Requests/sec'] } else { $null }
        FullScansPerSec           = if ($counters.ContainsKey('Full scans/sec')) { $counters['Full scans/sec'] } else { $null }
        IndexSearchesPerSec       = if ($counters.ContainsKey('Index searches/sec')) { $counters['Index searches/sec'] } else { $null }
        PageReadsPerSec           = if ($counters.ContainsKey('Page reads/sec')) { $counters['Page reads/sec'] } else { $null }
        PageWritesPerSec          = if ($counters.ContainsKey('Page writes/sec')) { $counters['Page writes/sec'] } else { $null }
        CheckpointPagesPerSec     = if ($counters.ContainsKey('Checkpoint pages/sec')) { $counters['Checkpoint pages/sec'] } else { $null }
        LazyWritesPerSec          = if ($counters.ContainsKey('Lazy writes/sec')) { $counters['Lazy writes/sec'] } else { $null }
        LogFlushesPerSec          = if ($counters.ContainsKey('Log Flushes/sec')) { $counters['Log Flushes/sec'] } else { $null }
        LogFlushWaitMs            = if ($counters.ContainsKey('Log Flush Wait Time')) { $counters['Log Flush Wait Time'] } else { $null }
        DiskReadMbps              = if ($counters.ContainsKey('Disk Read Bytes/sec')) { [math]::Round($counters['Disk Read Bytes/sec'] / 1048576.0, 2) } else { $null }
        DiskWriteMbps             = if ($counters.ContainsKey('Disk Write Bytes/sec')) { [math]::Round($counters['Disk Write Bytes/sec'] / 1048576.0, 2) } else { $null }
        DataIOResponseMsAvg       = if ($counters.ContainsKey('Average Disk sec/Read')) { [math]::Round($counters['Average Disk sec/Read'] * 1000.0, 2) } else { $null }
        LogIOResponseMsAvg        = if ($counters.ContainsKey('Average Disk sec/Write')) { [math]::Round($counters['Average Disk sec/Write'] * 1000.0, 2) } else { $null }
        PendingDiskIOCount        = $null
        LoginsPerSec              = if ($counters.ContainsKey('Logins/sec')) { $counters['Logins/sec'] } else { $null }
        LogoutsPerSec             = if ($counters.ContainsKey('Logouts/sec')) { $counters['Logouts/sec'] } else { $null }
        GrantedWorkspaceMemoryMB  = if ($counters.ContainsKey('Granted Workspace Memory (KB)')) { [math]::Round($counters['Granted Workspace Memory (KB)'] / 1024.0, 1) } else { $null }
        ReadAheadPagesPerSec      = if ($counters.ContainsKey('Read-ahead pages/sec')) { $counters['Read-ahead pages/sec'] } else { $null }
        WorkfilesCreatedPerSec    = if ($counters.ContainsKey('Workfiles Created/sec')) { $counters['Workfiles Created/sec'] } else { $null }
        WorktablesCreatedPerSec   = if ($counters.ContainsKey('Worktables Created/sec')) { $counters['Worktables Created/sec'] } else { $null }
        LatchWaitsPerSec          = if ($counters.ContainsKey('Latch Waits/sec')) { $counters['Latch Waits/sec'] } else { $null }
        SortSpillsPerSec          = if ($counters.ContainsKey('Sort Overflows/sec')) { $counters['Sort Overflows/sec'] } else { $null }
        HashSpillsPerSec          = if ($counters.ContainsKey('Hash Warnings/sec')) { $counters['Hash Warnings/sec'] } else { $null }
        ForwardedRecordsPerSec    = if ($counters.ContainsKey('Forwarded Records/sec')) { $counters['Forwarded Records/sec'] } else { $null }
        PageSplitsPerSec          = if ($counters.ContainsKey('Page splits/sec')) { $counters['Page splits/sec'] } else { $null }
        TempdbDataAllocatedMB     = $null
        TempdbDataUsedMB          = $null
    }
    foreach ($k in $waits.Keys) { $extra[$k] = $waits[$k] }
    foreach ($k in $backup.Keys) { $extra[$k] = $backup[$k] }

    $merged = New-Object psobject -Property @{}
    foreach ($col in $row.Table.Columns) {
        $merged | Add-Member -NotePropertyName $col.ColumnName -NotePropertyValue $row[$col.ColumnName]
    }
    foreach ($k in $extra.Keys) {
        if ($null -ne $extra[$k]) {
            $merged | Add-Member -NotePropertyName $k -NotePropertyValue $extra[$k] -Force
        }
    }
    return $merged
}

function ConvertTo-IsoUtc {
    param($Value)
    if ($null -eq $Value -or $Value -is [DBNull]) { return $null }
    try {
        $d = [datetime]$Value
        if ($d.Kind -eq [DateTimeKind]::Unspecified) { $d = [DateTime]::SpecifyKind($d, [DateTimeKind]::Utc) }
        return $d.ToUniversalTime().ToString("o")
    } catch {
        return $null
    }
}

function Test-BackupRowCompliant {
    param($RecoveryModel, $LastFull, $LastLog, [int]$RpoHours)
    $now = [datetime]::UtcNow
    if ($null -eq $LastFull -or $LastFull -is [DBNull]) { return $false }
    $rec = [string]$RecoveryModel
    if ($rec -match 'FULL' -or $rec -match 'BULK_LOGGED') {
        if ($null -eq $LastLog -or $LastLog -is [DBNull]) { return $false }
        return (($now - [datetime]$LastLog).TotalHours -le $RpoHours)
    }
    return (($now - [datetime]$LastFull).TotalHours -le [Math]::Max($RpoHours * 7, 168))
}

function Get-FleetFactsForServer {
    param([string]$ConnectName, [string]$CanonicalName, [int]$RpoHours)
    $backup = [System.Collections.Generic.List[object]]::new()
    $hadr = [System.Collections.Generic.List[object]]::new()
    $patch = [System.Collections.Generic.List[object]]::new()
    $inventory = [System.Collections.Generic.List[object]]::new()
    $jobs = [System.Collections.Generic.List[object]]::new()
    $volumes = [System.Collections.Generic.List[object]]::new()

    $dtBak = Invoke-SqlDataTable -ServerName $ConnectName -Query @"
SELECT
  d.name AS DatabaseName,
  d.recovery_model_desc AS RecoveryModel,
  (SELECT MAX(bs.backup_finish_date) FROM msdb.dbo.backupset bs WHERE bs.database_name = d.name AND bs.type = 'D') AS LastFullBackup,
  (SELECT MAX(bs.backup_finish_date) FROM msdb.dbo.backupset bs WHERE bs.database_name = d.name AND bs.type = 'I') AS LastDiffBackup,
  (SELECT MAX(bs.backup_finish_date) FROM msdb.dbo.backupset bs WHERE bs.database_name = d.name AND bs.type = 'L') AS LastLogBackup
FROM sys.databases d
WHERE d.state = 0 AND d.name <> N'tempdb';
"@
    if ($dtBak) {
        foreach ($r in $dtBak.Rows) {
            $ok = Test-BackupRowCompliant -RecoveryModel $r.RecoveryModel -LastFull $r.LastFullBackup -LastLog $r.LastLogBackup -RpoHours $RpoHours
            $backup.Add(@{
                serverName   = $CanonicalName
                databaseName = [string]$r.DatabaseName
                recoveryModel = if ($r.RecoveryModel -is [DBNull]) { $null } else { [string]$r.RecoveryModel }
                lastFullBackup = ConvertTo-IsoUtc $r.LastFullBackup
                lastDiffBackup = ConvertTo-IsoUtc $r.LastDiffBackup
                lastLogBackup  = ConvertTo-IsoUtc $r.LastLogBackup
                rpoHoursTarget = $RpoHours
                isCompliant    = [bool]$ok
            })
        }
    }

    $dtHadr = Invoke-SqlDataTable -ServerName $ConnectName -Query @"
SELECT
  ag.name AS AGName,
  UPPER(LEFT(ars.role_desc, 32)) AS ReplicaRole,
  ars.synchronization_health_desc AS SynchronizationState
FROM sys.dm_hadr_availability_replica_states ars
INNER JOIN sys.availability_replicas ar ON ars.replica_id = ar.replica_id
INNER JOIN sys.availability_groups ag ON ag.group_id = ar.group_id
WHERE ars.is_local = 1;
"@
    if ($dtHadr) {
        foreach ($r in $dtHadr.Rows) {
            $hadr.Add(@{
                serverName           = $CanonicalName
                agName               = [string]$r.AGName
                replicaRole          = [string]$r.ReplicaRole
                synchronizationState = if ($r.SynchronizationState -is [DBNull]) { $null } else { [string]$r.SynchronizationState }
            })
        }
    }

    $verRow = Invoke-SqlDataTable -ServerName $ConnectName -Query @"
SELECT
  CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(64)) AS ProductVersion,
  CAST(SERVERPROPERTY('ProductLevel') AS NVARCHAR(32)) AS ProductLevel;
"@
    if ($verRow -and $verRow.Rows.Count -gt 0) {
        $pv = [string]$verRow.Rows[0].ProductVersion
        $buildPart = 0
        if ($pv -match '^(\d+)\.(\d+)\.(\d+)\.(\d+)') {
            try { $buildPart = [int]$Matches[4] } catch { $buildPart = 0 }
        }
        $patch.Add(@{
            serverName     = $CanonicalName
            productVersion = $pv
            productLevel   = if ($verRow.Rows[0].ProductLevel -is [DBNull]) { $null } else { [string]$verRow.Rows[0].ProductLevel }
            sqlBuildNumber = $buildPart
            cuBehindCount  = 0
        })
    }

    $dtInv = Invoke-SqlDataTable -ServerName $ConnectName -Query @"
SELECT
  d.name AS DatabaseName,
  CAST(SUM(CASE WHEN mf.type = 0 THEN mf.size ELSE 0 END) * 8 / 1024 AS BIGINT) AS SizeMB,
  d.state_desc AS State,
  d.recovery_model_desc AS RecoveryModel
FROM sys.databases d
LEFT JOIN sys.master_files mf ON mf.database_id = d.database_id
WHERE d.state = 0
GROUP BY d.name, d.state_desc, d.recovery_model_desc;
"@
    if ($dtInv) {
        foreach ($r in $dtInv.Rows) {
            $inventory.Add(@{
                serverName     = $CanonicalName
                databaseName   = [string]$r.DatabaseName
                sizeMB         = if ($r.SizeMB -is [DBNull]) { $null } else { [int64]$r.SizeMB }
                state          = if ($r.State -is [DBNull]) { $null } else { [string]$r.State }
                recoveryModel  = if ($r.RecoveryModel -is [DBNull]) { $null } else { [string]$r.RecoveryModel }
            })
        }
    }

    $dtJobs = Invoke-SqlDataTable -ServerName $ConnectName -DatabaseName "msdb" -Query @"
SELECT
  j.name AS JobName,
  CASE js.last_run_outcome
    WHEN 0 THEN 'Failed'
    WHEN 1 THEN 'Succeeded'
    WHEN 2 THEN 'Retry'
    WHEN 3 THEN 'Canceled'
    ELSE 'Unknown'
  END AS LastRunOutcome,
  js.last_run_date,
  js.last_run_time,
  js.last_run_duration,
  j.enabled
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobservers js ON j.job_id = js.job_id;
"@
    if ($dtJobs) {
        foreach ($r in $dtJobs.Rows) {
            $runDt = $null
            if ($r.last_run_date -isnot [DBNull] -and [int]$r.last_run_date -gt 0) {
                try {
                    $d = [string][int]$r.last_run_date
                    $t = [string][int]$r.last_run_time
                    while ($t.Length -lt 6) { $t = "0$t" }
                    $runDt = [datetime]::ParseExact("$d$t", "yyyyMMddHHmmss", [System.Globalization.CultureInfo]::InvariantCulture)
                } catch { $runDt = $null }
            }
            $jobs.Add(@{
                serverName         = $CanonicalName
                jobName            = [string]$r.JobName
                lastRunOutcome     = [string]$r.LastRunOutcome
                lastRunDate        = if ($runDt) { $runDt.ToUniversalTime().ToString("o") } else { $null }
                lastRunDurationSec = if ($r.last_run_duration -is [DBNull]) { $null } else {
                    $raw = [int]$r.last_run_duration
                    $ds = "{0:D6}" -f $raw
                    try {
                        $hh = [int]$ds.Substring(0, 2)
                        $mm = [int]$ds.Substring(2, 2)
                        $ss = [int]$ds.Substring(4, 2)
                        $hh * 3600 + $mm * 60 + $ss
                    } catch { $raw }
                }
                isDisabled         = ([int]$r.enabled -eq 0)
            })
        }
    }

    $dtVol = Invoke-SqlDataTable -ServerName $ConnectName -Query @"
SELECT
  vs.volume_mount_point AS MountPoint,
  CAST(MAX(vs.total_bytes) / 1024.0 / 1024 / 1024 AS DECIMAL(18,2)) AS TotalGB,
  CAST(MAX(vs.available_bytes) / 1024.0 / 1024 / 1024 AS DECIMAL(18,2)) AS FreeGB
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) vs
GROUP BY vs.volume_mount_point;
"@
    if ($dtVol) {
        foreach ($r in $dtVol.Rows) {
            $volumes.Add(@{
                serverName  = $CanonicalName
                mountPoint  = [string]$r.MountPoint
                totalGB     = if ($r.TotalGB -is [DBNull]) { $null } else { [double]$r.TotalGB }
                freeGB      = if ($r.FreeGB -is [DBNull]) { $null } else { [double]$r.FreeGB }
            })
        }
    }

    return @{
        backup    = $backup
        hadr      = $hadr
        patch     = $patch
        inventory = $inventory
        jobs      = $jobs
        volumes   = $volumes
    }
}

$script:WaitCategories = @{
    'CXPACKET'='Parallelism'; 'CXCONSUMER'='Parallelism'; 'CXSYNC_PORT'='Parallelism'
    'SOS_SCHEDULER_YIELD'='CPU'; 'THREADPOOL'='CPU'; 'CMEMTHREAD'='CPU'
    'PAGEIOLATCH_SH'='IO'; 'PAGEIOLATCH_EX'='IO'; 'PAGEIOLATCH_UP'='IO'
    'WRITELOG'='IO'; 'IO_COMPLETION'='IO'; 'ASYNC_IO_COMPLETION'='IO'
    'PAGELATCH_SH'='Memory'; 'PAGELATCH_EX'='Memory'; 'PAGELATCH_UP'='Memory'
    'RESOURCE_SEMAPHORE'='Memory'; 'RESOURCE_SEMAPHORE_QUERY_COMPILE'='Memory'
    'LCK_M_S'='Locking'; 'LCK_M_X'='Locking'; 'LCK_M_U'='Locking'
    'LCK_M_SCH_M'='Locking'; 'LCK_M_SCH_S'='Locking'; 'LCK_M_IX'='Locking'
    'ASYNC_NETWORK_IO'='Network'; 'NET_WAITFOR_PACKET'='Network'
    'HADR_SYNC_COMMIT'='AG'; 'HADR_LOGCAPTURE_WAIT'='AG'; 'HADR_NOTIFICATION_DEQUEUE'='AG'
    'BACKUPBUFFER'='Backup'; 'BACKUPIO'='Backup'; 'BACKUPTHREAD'='Backup'
}

function Get-WaitStatsSnapshot {
    param([string]$ServerName)
    $dt = Invoke-SqlDataTable -ServerName $ServerName -Query @"
SELECT
  wait_type AS WaitType,
  waiting_tasks_count AS WaitingTasksCount,
  wait_time_ms AS WaitTimeMs,
  signal_wait_time_ms AS SignalWaitTimeMs
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
  'CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SQLTRACE_BUFFER_FLUSH',
  'SLEEP_TASK','SLEEP_SYSTEMTASK','WAITFOR','HADR_FILESTREAM_IOMGR_IOCOMPLETION',
  'CHECKPOINT_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','XE_DISPATCH_WAIT',
  'BROKER_TO_FLUSH','BROKER_TASK_STOP','FT_IFTS_SCHEDULER_IDLE_WAIT',
  'SQLTRACE_INCREMENTAL_FLUSH_SLEEP','ONDEMAND_TASK_QUEUE','DBMIRROR_EVENTS_QUEUE',
  'SP_SERVER_DIAGNOSTICS_SLEEP','DIRTY_PAGE_POLL','QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
  'QDS_ASYNC_QUEUE','QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
  'LOGMGR_QUEUE','FSAGENT','BROKER_EVENTHANDLER'
)
AND waiting_tasks_count > 0
ORDER BY wait_time_ms DESC;
"@
    if ($null -eq $dt) { return $null }
    $rows = [System.Collections.Generic.List[object]]::new()
    foreach ($r in $dt.Rows) {
        $wt = [string]$r.WaitType
        $cat = if ($script:WaitCategories.ContainsKey($wt)) { $script:WaitCategories[$wt] } else { 'Internal' }
        $rows.Add(@{
            waitType          = $wt
            category          = $cat
            waitingTasksCount = [int64]$r.WaitingTasksCount
            waitTimeMs        = [int64]$r.WaitTimeMs
            signalWaitTimeMs  = [int64]$r.SignalWaitTimeMs
        })
    }
    return $rows
}

function Get-DatabaseHealth {
    param([string]$ServerName)
    $missingIndexes = [System.Collections.Generic.List[object]]::new()
    $dtMi = Invoke-SqlDataTable -ServerName $ServerName -Query @"
SELECT TOP (50)
  DB_NAME(mid.database_id) AS DatabaseName,
  OBJECT_SCHEMA_NAME(mid.object_id, mid.database_id) AS SchemaName,
  OBJECT_NAME(mid.object_id, mid.database_id) AS TableName,
  mid.equality_columns AS EqualityColumns,
  mid.inequality_columns AS InequalityColumns,
  mid.included_columns AS IncludeColumns,
  CAST(migs.avg_user_impact * migs.avg_total_user_cost * (migs.user_seeks + migs.user_scans) AS DECIMAL(18,2)) AS ImpactScore,
  migs.user_seeks AS UserSeeks,
  migs.user_scans AS UserScans,
  migs.avg_total_user_cost AS AvgTotalUserCost,
  migs.avg_user_impact AS AvgUserImpact
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE mid.database_id > 4
ORDER BY ImpactScore DESC;
"@
    if ($dtMi) {
        foreach ($r in $dtMi.Rows) {
            $missingIndexes.Add(@{
                databaseName      = if ($r.DatabaseName -is [DBNull]) { $null } else { [string]$r.DatabaseName }
                schemaName        = if ($r.SchemaName -is [DBNull]) { $null } else { [string]$r.SchemaName }
                tableName         = if ($r.TableName -is [DBNull]) { $null } else { [string]$r.TableName }
                equalityColumns   = if ($r.EqualityColumns -is [DBNull]) { $null } else { [string]$r.EqualityColumns }
                inequalityColumns = if ($r.InequalityColumns -is [DBNull]) { $null } else { [string]$r.InequalityColumns }
                includeColumns    = if ($r.IncludeColumns -is [DBNull]) { $null } else { [string]$r.IncludeColumns }
                impactScore       = if ($r.ImpactScore -is [DBNull]) { $null } else { [double]$r.ImpactScore }
                userSeeks         = if ($r.UserSeeks -is [DBNull]) { 0 } else { [int64]$r.UserSeeks }
                userScans         = if ($r.UserScans -is [DBNull]) { 0 } else { [int64]$r.UserScans }
                avgTotalUserCost  = if ($r.AvgTotalUserCost -is [DBNull]) { $null } else { [double]$r.AvgTotalUserCost }
                avgUserImpact     = if ($r.AvgUserImpact -is [DBNull]) { $null } else { [double]$r.AvgUserImpact }
            })
        }
    }

    $indexes = [System.Collections.Generic.List[object]]::new()
    $dtDbs = Invoke-SqlDataTable -ServerName $ServerName -Query @"
SELECT name AS DatabaseName FROM sys.databases
WHERE state = 0 AND database_id > 4 AND user_access = 0;
"@
    if ($dtDbs) {
        foreach ($dbRow in $dtDbs.Rows) {
            $dbName = [string]$dbRow.DatabaseName
            $dtIdx = Invoke-SqlDataTable -ServerName $ServerName -DatabaseName $dbName -Query @"
SELECT TOP (30)
  DB_NAME() AS DatabaseName,
  OBJECT_SCHEMA_NAME(ips.object_id) AS SchemaName,
  OBJECT_NAME(ips.object_id) AS TableName,
  i.name AS IndexName,
  i.type_desc AS IndexType,
  CAST(ips.avg_fragmentation_in_percent AS DECIMAL(5,2)) AS FragmentationPct,
  ips.page_count AS PageCount,
  ius.user_seeks AS UserSeeks,
  ius.user_scans AS UserScans,
  ius.user_lookups AS UserLookups,
  ius.user_updates AS UserUpdates
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
INNER JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
LEFT JOIN sys.dm_db_index_usage_stats ius ON ips.object_id = ius.object_id AND ips.index_id = ius.index_id AND ius.database_id = DB_ID()
WHERE ips.page_count > 100 AND ips.index_id > 0
ORDER BY ips.avg_fragmentation_in_percent DESC;
"@
            if ($dtIdx) {
                foreach ($r in $dtIdx.Rows) {
                    $indexes.Add(@{
                        databaseName     = if ($r.DatabaseName -is [DBNull]) { $null } else { [string]$r.DatabaseName }
                        schemaName       = if ($r.SchemaName -is [DBNull]) { $null } else { [string]$r.SchemaName }
                        tableName        = if ($r.TableName -is [DBNull]) { $null } else { [string]$r.TableName }
                        indexName        = if ($r.IndexName -is [DBNull]) { $null } else { [string]$r.IndexName }
                        indexType        = if ($r.IndexType -is [DBNull]) { $null } else { [string]$r.IndexType }
                        fragmentationPct = if ($r.FragmentationPct -is [DBNull]) { $null } else { [double]$r.FragmentationPct }
                        pageCount        = if ($r.PageCount -is [DBNull]) { $null } else { [int64]$r.PageCount }
                        userSeeks        = if ($r.UserSeeks -is [DBNull]) { $null } else { [int64]$r.UserSeeks }
                        userScans        = if ($r.UserScans -is [DBNull]) { $null } else { [int64]$r.UserScans }
                        userLookups      = if ($r.UserLookups -is [DBNull]) { $null } else { [int64]$r.UserLookups }
                        userUpdates      = if ($r.UserUpdates -is [DBNull]) { $null } else { [int64]$r.UserUpdates }
                    })
                }
            }
        }
    }

    $statistics = [System.Collections.Generic.List[object]]::new()
    $dtDbsStats = Invoke-SqlDataTable -ServerName $ServerName -Query @"
SELECT name AS DatabaseName FROM sys.databases
WHERE state = 0 AND database_id > 4 AND user_access = 0;
"@
    if ($dtDbsStats) {
        foreach ($dbRow in $dtDbsStats.Rows) {
            $dbName = [string]$dbRow.DatabaseName
            $dtSt = Invoke-SqlDataTable -ServerName $ServerName -DatabaseName $dbName -Query @"
SELECT TOP (50)
  DB_NAME() AS DatabaseName,
  SCHEMA_NAME(o.schema_id) AS SchemaName,
  o.name AS TableName,
  s.name AS StatName,
  STATS_DATE(s.object_id, s.stats_id) AS LastUpdated,
  sp.[rows] AS [RowCount],
  sp.modification_counter AS RowsModified
FROM sys.stats s
INNER JOIN sys.objects o ON s.object_id = o.object_id
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE o.type = 'U' AND sp.modification_counter > 1000
ORDER BY sp.modification_counter DESC;
"@
            if ($dtSt) {
                foreach ($r in $dtSt.Rows) {
                    $statistics.Add(@{
                        databaseName  = if ($r.DatabaseName -is [DBNull]) { $null } else { [string]$r.DatabaseName }
                        schemaName    = if ($r.SchemaName -is [DBNull]) { $null } else { [string]$r.SchemaName }
                        tableName     = if ($r.TableName -is [DBNull]) { $null } else { [string]$r.TableName }
                        statName      = if ($r.StatName -is [DBNull]) { $null } else { [string]$r.StatName }
                        lastUpdated   = if ($r.LastUpdated -is [DBNull]) { $null } else { ([datetime]$r.LastUpdated).ToUniversalTime().ToString('o') }
                        rowCount      = if ($r.RowCount -is [DBNull]) { $null } else { [int64]$r.RowCount }
                        rowsModified  = if ($r.RowsModified -is [DBNull]) { $null } else { [int64]$r.RowsModified }
                    })
                }
            }
        }
    }

    return @{
        missingIndexes = $missingIndexes
        indexes        = $indexes
        statistics     = $statistics
    }
}

function Get-QueryStoreStats {
    param([string]$ServerName)
    $rows = [System.Collections.Generic.List[object]]::new()
    $dtDbs = Invoke-SqlDataTable -ServerName $ServerName -Query @"
SELECT name AS DatabaseName FROM sys.databases
WHERE state = 0 AND is_query_store_on = 1 AND database_id > 4;
"@
    if (-not $dtDbs -or $dtDbs.Rows.Count -eq 0) { return $rows }

    foreach ($dbRow in $dtDbs.Rows) {
        $dbName = [string]$dbRow.DatabaseName
        $dtQs = Invoke-SqlDataTable -ServerName $ServerName -DatabaseName $dbName -Query @"
SELECT TOP (20)
  qt.query_sql_text AS QueryText,
  CAST(qsp.query_plan_hash AS VARBINARY(32)) AS PlanHash,
  rs.count_executions AS ExecutionCount,
  CAST(rs.avg_cpu_time / 1000.0 AS BIGINT) AS TotalCpuTimeMs,
  CAST(rs.avg_duration / 1000.0 AS BIGINT) AS TotalDurationMs,
  CAST(rs.avg_logical_io_reads AS BIGINT) AS TotalLogicalReads,
  CAST(rs.avg_physical_io_reads AS BIGINT) AS TotalPhysicalReads,
  CAST(rs.avg_logical_io_writes AS BIGINT) AS TotalLogicalWrites
FROM sys.query_store_runtime_stats rs
INNER JOIN sys.query_store_plan qsp ON rs.plan_id = qsp.plan_id
INNER JOIN sys.query_store_query q ON qsp.query_id = q.query_id
INNER JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE rs.last_execution_time > DATEADD(HOUR, -24, GETUTCDATE())
ORDER BY rs.avg_cpu_time * rs.count_executions DESC;
"@
        if ($dtQs) {
            foreach ($r in $dtQs.Rows) {
                $rows.Add(@{
                    databaseName      = $dbName
                    queryText         = if ($r.QueryText -is [DBNull]) { $null } else { [string]$r.QueryText }
                    executionCount    = if ($r.ExecutionCount -is [DBNull]) { 0 } else { [int64]$r.ExecutionCount }
                    totalCpuTimeMs    = if ($r.TotalCpuTimeMs -is [DBNull]) { $null } else { [int64]$r.TotalCpuTimeMs }
                    totalDurationMs   = if ($r.TotalDurationMs -is [DBNull]) { $null } else { [int64]$r.TotalDurationMs }
                    totalLogicalReads = if ($r.TotalLogicalReads -is [DBNull]) { $null } else { [int64]$r.TotalLogicalReads }
                    totalPhysicalReads = if ($r.TotalPhysicalReads -is [DBNull]) { $null } else { [int64]$r.TotalPhysicalReads }
                    totalLogicalWrites = if ($r.TotalLogicalWrites -is [DBNull]) { $null } else { [int64]$r.TotalLogicalWrites }
                })
            }
        }
    }
    return $rows
}

function Get-TelemetryPayload {
    param($TelRow, [string]$CanonicalName)
    function NVal($col, [switch]$AsInt) {
        if ($null -eq $TelRow -or $TelRow.$col -is [DBNull]) { return $null }
        if ($AsInt) { return [int]$TelRow.$col }
        return [double]$TelRow.$col
    }
    return @{
        serverName                = $CanonicalName
        collectedAt               = (Get-Date).ToUniversalTime().ToString("o")
        activeSessions            = (NVal -col 'ActiveSessions' -AsInt)
        blockingSessions          = (NVal -col 'BlockingSessions' -AsInt)
        sqlMemoryUsedMB           = (NVal -col 'SqlMemoryUsedMB' -AsInt)
        batchRequestsPerSec       = (NVal -col 'BatchRequestsPerSec')
        avgSignalWaitMs           = (NVal -col 'AvgSignalWaitMs')
        cpuPercent                = (NVal -col 'CpuPercent')
        pageLifeExpectancySec     = (NVal -col 'PageLifeExpectancySec' -AsInt)
        bufferCacheHitRatioPct    = (NVal -col 'BufferCacheHitRatioPct')
        deadlocksPerSec           = (NVal -col 'DeadlocksPerSec')
        userConnections           = (NVal -col 'UserConnections' -AsInt)
        processesBlocked          = (NVal -col 'ProcessesBlocked' -AsInt)
        schedulerCountOnline      = (NVal -col 'SchedulerCountOnline' -AsInt)
        runnableTasksCount        = (NVal -col 'RunnableTasksCount' -AsInt)
        activeWorkers             = (NVal -col 'ActiveWorkers' -AsInt)
        maxWorkers                = (NVal -col 'MaxWorkers' -AsInt)
        cpuCountLogical           = (NVal -col 'CpuCountLogical' -AsInt)
        hostTotalMemoryMB         = (NVal -col 'HostTotalMemoryMB' -AsInt)
        hostAvailableMemoryMB     = (NVal -col 'HostAvailableMemoryMB' -AsInt)
        maxServerMemoryMB         = (NVal -col 'MaxServerMemoryMB' -AsInt)
        targetServerMemoryMB      = (NVal -col 'TargetServerMemoryMB' -AsInt)
        totalServerMemoryMB       = (NVal -col 'TotalServerMemoryMB' -AsInt)
        pendingMemoryGrantsCount  = (NVal -col 'PendingMemoryGrantsCount' -AsInt)
        tempdbUserObjectsMB       = (NVal -col 'TempdbUserObjectsMB')
        tempdbInternalObjectsMB   = (NVal -col 'TempdbInternalObjectsMB')
        tempdbVersionStoreMB      = (NVal -col 'TempdbVersionStoreMB')
        maxDopConfigured          = (NVal -col 'MaxDopConfigured' -AsInt)
        costThresholdForParallelism = (NVal -col 'CostThresholdForParallelism' -AsInt)
        userDatabaseCount         = (NVal -col 'UserDatabaseCount' -AsInt)
        planCacheHitRatioPct      = (NVal -col 'PlanCacheHitRatioPct')
        sqlCompilationsPerSec     = (NVal -col 'SqlCompilationsPerSec')
        sqlRecompilationsPerSec   = (NVal -col 'SqlRecompilationsPerSec')
        transactionsPerSec        = (NVal -col 'TransactionsPerSec')
        lockRequestsPerSec        = (NVal -col 'LockRequestsPerSec')
        fullScansPerSec           = (NVal -col 'FullScansPerSec')
        indexSearchesPerSec       = (NVal -col 'IndexSearchesPerSec')
        pageReadsPerSec           = (NVal -col 'PageReadsPerSec')
        pageWritesPerSec          = (NVal -col 'PageWritesPerSec')
        checkpointPagesPerSec     = (NVal -col 'CheckpointPagesPerSec')
        lazyWritesPerSec          = (NVal -col 'LazyWritesPerSec')
        logFlushesPerSec          = (NVal -col 'LogFlushesPerSec')
        logFlushWaitMs            = (NVal -col 'LogFlushWaitMs')
        diskReadMbps              = (NVal -col 'DiskReadMbps')
        diskWriteMbps             = (NVal -col 'DiskWriteMbps')
        dataIOResponseMsAvg       = (NVal -col 'DataIOResponseMsAvg')
        logIOResponseMsAvg        = (NVal -col 'LogIOResponseMsAvg')
        pendingDiskIOCount        = (NVal -col 'PendingDiskIOCount' -AsInt)
        loginsPerSec              = (NVal -col 'LoginsPerSec')
        logoutsPerSec             = (NVal -col 'LogoutsPerSec')
        grantedWorkspaceMemoryMB  = (NVal -col 'GrantedWorkspaceMemoryMB')
        readAheadPagesPerSec      = (NVal -col 'ReadAheadPagesPerSec')
        workfilesCreatedPerSec    = (NVal -col 'WorkfilesCreatedPerSec')
        worktablesCreatedPerSec   = (NVal -col 'WorktablesCreatedPerSec')
        latchWaitsPerSec          = (NVal -col 'LatchWaitsPerSec')
        waitIoMsPerSec            = (NVal -col 'WaitIoMsPerSec')
        waitLockMsPerSec          = (NVal -col 'WaitLockMsPerSec')
        waitMemoryMsPerSec        = (NVal -col 'WaitMemoryMsPerSec')
        waitCpuParallelMsPerSec   = (NVal -col 'WaitCpuParallelMsPerSec')
        sortSpillsPerSec          = (NVal -col 'SortSpillsPerSec')
        hashSpillsPerSec          = (NVal -col 'HashSpillsPerSec')
        forwardedRecordsPerSec    = (NVal -col 'ForwardedRecordsPerSec')
        pageSplitsPerSec          = (NVal -col 'PageSplitsPerSec')
        tempdbDataAllocatedMB     = (NVal -col 'TempdbDataAllocatedMB')
        tempdbDataUsedMB          = (NVal -col 'TempdbDataUsedMB')
        oldestOpenTranMinutes     = (NVal -col 'OldestOpenTranMinutes')
        backupAgeWorstMinutes     = (NVal -col 'BackupAgeWorstMinutes' -AsInt)
    }
}

function Get-SecuritySnapshot {
    param([string]$ServerName)
    $tde = [System.Collections.Generic.List[object]]::new()
    $keyVault = [System.Collections.Generic.List[object]]::new()
    $dtTde = Invoke-SqlDataTable -ServerName $ServerName -Query @"
SELECT d.name AS DatabaseName, dek.encryption_state AS EncryptionState, dek.encryptor_type AS EncryptorType,
       dek.key_algorithm AS KeyAlgorithm, dek.key_length AS KeyLength
FROM sys.databases d
LEFT JOIN sys.dm_database_encryption_keys dek ON d.database_id = dek.database_id
WHERE d.database_id > 4 AND d.state = 0;
"@
    if ($dtTde) {
        foreach ($r in $dtTde.Rows) {
            $encType = if ($r.EncryptorType -is [DBNull]) { $null } else { [string]$r.EncryptorType }
            $tde.Add(@{
                dbName          = [string]$r.DatabaseName
                encryptionState = if ($r.EncryptionState -is [DBNull]) { $null } else { [int]$r.EncryptionState }
                encryptorType   = $encType
                keyAlgorithm    = if ($r.KeyAlgorithm -is [DBNull]) { $null } else { [string]$r.KeyAlgorithm }
                keyLength       = if ($r.KeyLength -is [DBNull]) { $null } else { [int]$r.KeyLength }
            })
            if ($encType -and ($encType -match 'Azure|KEY_STORE|EKM|HSM')) {
                $keyVault.Add(@{
                    databaseName  = [string]$r.DatabaseName
                    vaultName     = if ($encType -match 'Azure') { 'Azure Key Vault (EKM)' } else { 'External Key Store' }
                    keyName       = $encType
                    encryptorType = $encType
                    keyType       = if ($r.KeyAlgorithm -is [DBNull]) { 'Unknown' } else { [string]$r.KeyAlgorithm }
                })
            }
        }
    }

    $forceEncryption = $false
    $certSubject = $null
    $certExpiry = $null
    $tlsVersion = 'TLS 1.2+'
    $hostPart = ($ServerName -split '\\')[0]
    if ($hostPart -eq $env:COMPUTERNAME -or $hostPart -eq 'localhost' -or $hostPart -eq '.') {
        try {
            $instKey = 'MSSQLSERVER'
            if ($ServerName -match '\\(.+)$') { $instKey = $matches[1] }
            $regBase = 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server'
            $instances = Get-ChildItem "$regBase\Instance Names\SQL" -ErrorAction SilentlyContinue
            $instId = $null
            if ($instances) {
                $prop = $instances.GetValue($instKey)
                if ($prop) { $instId = $prop }
            }
            if ($instId) {
                $encPath = "$regBase\$instId\MSSQLServer\SuperSocketNetLib\Encrypt"
                $encVal = Get-ItemProperty -Path $encPath -Name Enabled -ErrorAction SilentlyContinue
                if ($encVal -and $encVal.Enabled -eq 1) { $forceEncryption = $true }
            }
        } catch { }
    }

    $dtCert = Invoke-SqlDataTable -ServerName $ServerName -Query @"
SELECT TOP 1 name AS CertName, pvt_key_last_backup_date AS LastBackup,
       CAST(serverproperty('InstanceDefaultBackupPath') AS NVARCHAR(512)) AS BackupPath
FROM sys.certificates
WHERE pvt_key_last_backup_date IS NOT NULL OR name LIKE '%Server%' OR name LIKE '%TDE%'
ORDER BY pvt_key_last_backup_date DESC;
"@
    if ($dtCert -and $dtCert.Rows.Count -gt 0) {
        $certSubject = [string]$dtCert.Rows[0].CertName
    }

    $dtConn = Invoke-SqlDataTable -ServerName $ServerName -Query @"
SELECT TOP 1 encrypt_option AS EncryptOption FROM sys.dm_exec_connections
WHERE session_id = @@SPID AND encrypt_option IS NOT NULL;
"@
    $connEncrypted = $false
    if ($dtConn -and $dtConn.Rows.Count -gt 0) {
        $opt = [string]$dtConn.Rows[0].EncryptOption
        if ($opt -eq 'TRUE') { $connEncrypted = $true }
    }

    $sqlAuditEnabled = $false
    $xpCmdshell = $false
    $adHoc = $false
    $clrStrict = $false
    $crossDb = $false
    $remoteAdmin = $false
    $saDisabled = $false

    $dtAudit = Invoke-SqlDataTable -ServerName $ServerName -Query @"
SELECT COUNT(*) AS Cnt FROM sys.server_audits WHERE is_state_enabled = 1;
"@
    if ($dtAudit -and $dtAudit.Rows.Count -gt 0) {
        $sqlAuditEnabled = [int]$dtAudit.Rows[0].Cnt -gt 0
    }

    $dtCfg = Invoke-SqlDataTable -ServerName $ServerName -Query @"
SELECT name, CAST(value_in_use AS INT) AS Val FROM sys.configurations
WHERE name IN ('xp_cmdshell','Ad Hoc Distributed Queries','clr strict security',
  'cross db ownership chaining','remote admin connections');
"@
    if ($dtCfg) {
        foreach ($r in $dtCfg.Rows) {
            $n = [string]$r.name
            $v = [int]$r.Val
            switch -Regex ($n) {
                'xp_cmdshell' { $xpCmdshell = ($v -eq 1) }
                'Ad Hoc' { $adHoc = ($v -eq 1) }
                'clr strict' { $clrStrict = ($v -eq 1) }
                'cross db' { $crossDb = ($v -eq 1) }
                'remote admin' { $remoteAdmin = ($v -eq 1) }
            }
        }
    }

    $dtSa = Invoke-SqlDataTable -ServerName $ServerName -Query @"
SELECT is_disabled FROM sys.sql_logins WHERE name = 'sa';
"@
    if ($dtSa -and $dtSa.Rows.Count -gt 0) {
        $saDisabled = [bool]$dtSa.Rows[0].is_disabled
    }

    $isCompliant = ($forceEncryption -or $connEncrypted) -and ($certSubject -ne $null -or $connEncrypted)

    return @{
        tde       = $tde
        keyVault  = $keyVault
        tls       = @{
            forceEncryption = $forceEncryption
            certSubject     = $certSubject
            certExpiry      = $certExpiry
            tlsVersion      = $tlsVersion
            connEncrypted   = $connEncrypted
            isCompliant     = [bool]$isCompliant
        }
        serverSecurity = @{
            sqlAuditEnabled          = $sqlAuditEnabled
            xpCmdshellEnabled        = $xpCmdshell
            adHocDistributedQueries  = $adHoc
            clrStrictSecurity        = $clrStrict
            crossDbOwnershipChaining = $crossDb
            remoteAdminConnections   = $remoteAdmin
            saLoginDisabled          = $saDisabled
        }
    }
}

if ($SubmitAll) {
    $SubmitTelemetry = $true
    $SubmitFleetFacts = $true
    $SubmitWaitStats = $true
    $SubmitDatabaseHealth = $true
    $SubmitTechnologyDetection = $true
    $SubmitAgentStatus = $true
    $SubmitQueryStats = $true
    $SubmitSecurity = $true
}

function Invoke-SovereignApi {
    param([string]$Method, [string]$Path, $Body, [hashtable]$Headers = @{})
    $uri = "$script:ApiBase$Path"
    $params = @{
        Method      = $Method
        Uri         = $uri
        ContentType = "application/json"
    }
    if ($null -ne $Body) { $params.Body = ($Body | ConvertTo-Json -Depth 12 -Compress) }
    if ($Headers.Count -gt 0) { $params.Headers = $Headers }
    try {
        Invoke-RestMethod @params
    } catch {
        Write-Warning "API call failed: $uri - $($_.Exception.Message)"
        $null
    }
}

foreach ($srv in $ServerList) {
    Write-Host "Collecting from $srv..."
    $row = Get-SqlServerInfo -ServerName $srv
    if (-not $row) {
        Write-Warning "  Failed to connect to $srv"
        continue
    }

    $canonicalName = [string]$row.ServerName
    Write-Host "  $canonicalName - $($row.Version) $($row.Edition)"

    if ($RegisterIfMissing) {
        if (-not $BearerToken) {
            Write-Warning "  RegisterIfMissing skipped: provide -BearerToken (Administrator JWT) for POST /api/fleet/register."
        } else {
            $regHeaders = @{ Authorization = "Bearer $BearerToken" }
            Invoke-SovereignApi -Method POST -Path "/api/fleet/register" -Body @{
                serverName = $canonicalName
                engine     = "SQL Server"
                version    = [string]$row.Version
                edition    = [string]$row.Edition
            } -Headers $regHeaders | Out-Null
        }
    }

    if ($SubmitTelemetry) {
        $tel = Get-SqlTelemetry -ServerName $srv
        if (-not $tel) {
            Write-Warning "  Telemetry query failed for $srv"
        } else {
            $headers = @{}
            if ($IngestKey) { $headers["X-Sovereign-Ingest-Key"] = $IngestKey }

            $payload = Get-TelemetryPayload -TelRow $tel -CanonicalName $canonicalName

            Invoke-SovereignApi -Method POST -Path "/api/telemetry/snapshot" -Body $payload -Headers $headers | Out-Null
            Write-Host "  Telemetry ingested."
        }
    }

    if ($SubmitFleetFacts) {
        $facts = Get-FleetFactsForServer -ConnectName $srv -CanonicalName $canonicalName -RpoHours $RpoHoursDefault
        $headers = @{}
        if ($IngestKey) { $headers["X-Sovereign-Ingest-Key"] = $IngestKey }
        $body = @{
            backup    = @($facts.backup)
            hadr      = @($facts.hadr)
            patch     = @($facts.patch)
            inventory = @($facts.inventory)
            jobs      = @($facts.jobs)
            volumes   = @($facts.volumes)
        }
        Invoke-SovereignApi -Method POST -Path "/api/ingest/fleet-facts" -Body $body -Headers $headers | Out-Null
        Write-Host "  Fleet facts ingested (backup/hadr/patch/inventory/jobs/volumes)."
    }

    if ($SubmitWaitStats) {
        $waits = Get-WaitStatsSnapshot -ServerName $srv
        if ($waits -and $waits.Count -gt 0) {
            $headers = @{}
            if ($IngestKey) { $headers["X-Sovereign-Ingest-Key"] = $IngestKey }
            $body = @{
                serverName = $canonicalName
                waits      = @($waits)
            }
            Invoke-SovereignApi -Method POST -Path "/api/ingest/wait-stats" -Body $body -Headers $headers | Out-Null
            Write-Host "  Wait stats ingested ($($waits.Count) wait types)."
        } else {
            Write-Warning "  No wait stats collected for $srv"
        }
    }

    if ($SubmitDatabaseHealth) {
        $health = Get-DatabaseHealth -ServerName $srv
        if ($health) {
            $headers = @{}
            if ($IngestKey) { $headers["X-Sovereign-Ingest-Key"] = $IngestKey }
            $body = @{
                serverName     = $canonicalName
                missingIndexes = @($health.missingIndexes)
                indexes        = @($health.indexes)
                statistics     = @($health.statistics)
            }
            Invoke-SovereignApi -Method POST -Path "/api/ingest/database-health" -Body $body -Headers $headers | Out-Null
            Write-Host "  Database health ingested ($($health.missingIndexes.Count) missing indexes, $($health.indexes.Count) indexes, $($health.statistics.Count) statistics)."
        } else {
            Write-Warning "  No database health data for $srv"
        }
    }

    if ($SubmitTechnologyDetection) {
        $techs = @()
        # Always On Availability Groups
        $agDt = Invoke-SqlDataTable -ServerName $srv -Query @"
SELECT ag.name AS AGName, ar.replica_server_name, ars.role_desc,
       ars.synchronization_health_desc AS SyncHealth,
       ars.connected_state_desc AS ConnState
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
LEFT JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id
"@
        if ($agDt -and $agDt.Rows.Count -gt 0) {
            foreach ($row in $agDt.Rows) {
                $techs += @{
                    technology = "Always On Availability Group"
                    techType   = "AlwaysOn_AG"
                    name       = $row.AGName
                    status     = $row.SyncHealth
                    role       = $row.role_desc
                    partnerServer = $row.replica_server_name
                }
            }
        }

        # Database Mirroring
        $mirrorDt = Invoke-SqlDataTable -ServerName $srv -Query @"
SELECT d.name AS DatabaseName,
       m.mirroring_role_desc AS Role,
       m.mirroring_state_desc AS MirrorState,
       m.mirroring_partner_name AS Partner
FROM sys.database_mirroring m
JOIN sys.databases d ON m.database_id = d.database_id
WHERE m.mirroring_guid IS NOT NULL
"@
        if ($mirrorDt -and $mirrorDt.Rows.Count -gt 0) {
            foreach ($row in $mirrorDt.Rows) {
                $techs += @{
                    technology   = "Database Mirroring"
                    techType     = "Mirroring"
                    name         = "$($row.DatabaseName) Mirror"
                    databaseName = $row.DatabaseName
                    status       = $row.MirrorState
                    role         = $row.Role
                    partnerServer = $row.Partner
                }
            }
        }

        # Log Shipping (msdb column names vary by SQL Server version)
        $lsDt = $null
        $lsQueries = @(
            @"
SELECT primary_database AS DatabaseName, monitor_server AS PartnerServer, 'Primary' AS Role
FROM msdb.dbo.log_shipping_monitor_primary
UNION ALL
SELECT secondary_database, monitor_server, 'Secondary'
FROM msdb.dbo.log_shipping_monitor_secondary;
"@,
            @"
SELECT primary_database AS DatabaseName, primary_server AS PartnerServer, 'Primary' AS Role
FROM msdb.dbo.log_shipping_monitor_primary
UNION ALL
SELECT secondary_database, secondary_server, 'Secondary'
FROM msdb.dbo.log_shipping_monitor_secondary;
"@,
            @"
SELECT primary_database AS DatabaseName, originating_server AS PartnerServer, 'Primary' AS Role
FROM msdb.dbo.log_shipping_monitor_primary
UNION ALL
SELECT secondary_database, secondary_server, 'Secondary'
FROM msdb.dbo.log_shipping_monitor_secondary;
"@
        )
        foreach ($lsQ in $lsQueries) {
            if ($lsDt) { break }
            $lsDt = Invoke-SqlDataTable -ServerName $srv -DatabaseName "msdb" -Query $lsQ -Quiet
        }
        if ($lsDt -and $lsDt.Rows.Count -gt 0) {
            foreach ($row in $lsDt.Rows) {
                $partner = if ($row.Role -eq 'Primary') { $row.PartnerServer } else { $row.PartnerServer }
                $techs += @{
                    technology   = "Log Shipping"
                    techType     = "LogShipping"
                    name         = "$($row.DatabaseName) LS"
                    databaseName = $row.DatabaseName
                    status       = "Active"
                    role         = $row.Role
                    partnerServer = $partner
                }
            }
        }

        # Replication (requires distribution database access — skip quietly when unavailable)
        $replDt = $null
        try {
            $replDt = Invoke-SqlDataTable -ServerName $srv -DatabaseName "distribution" -Query @"
SELECT TOP (20) p.publication AS PubName, p.publication_type,
       s.subscriber_server, a.publisher_db
FROM distribution.dbo.MSpublications p
JOIN distribution.dbo.MSsubscriptions s ON p.publication_id = s.publication_id
JOIN distribution.dbo.MSarticles a ON p.publication_id = a.publication_id;
"@ -Quiet
        } catch { }
        if (-not $replDt) {
            try {
                $replDt = Invoke-SqlDataTable -ServerName $srv -DatabaseName "distribution" -Query @"
SELECT TOP (20) p.publication AS PubName, p.publication_type,
       s.srvname AS subscriber_server, a.publisher_db
FROM distribution.dbo.MSpublications p
JOIN distribution.dbo.MSsubscriptions s ON p.publication_id = s.publication_id
JOIN distribution.dbo.MSarticles a ON p.publication_id = a.publication_id;
"@ -Quiet
            } catch { }
        }
        if ($replDt -and $replDt.Rows.Count -gt 0) {
            foreach ($row in $replDt.Rows) {
                $replType = switch ($row.publication_type) {
                    0 { "Replication_Transactional" }
                    1 { "Replication_Snapshot" }
                    2 { "Replication_Merge" }
                    default { "Other" }
                }
                $techs += @{
                    technology    = "Replication ($replType)"
                    techType      = $replType
                    name          = $row.PubName
                    databaseName  = $row.publisher_db
                    status        = "Active"
                    partnerServer = $row.subscriber_server
                }
            }
        }

        # Failover Cluster
        $fciDt = Invoke-SqlDataTable -ServerName $srv -Query @"
SELECT NodeName, status_description AS NodeStatus,
       CASE WHEN is_current_owner = 1 THEN 'Owner' ELSE 'Node' END AS Role
FROM sys.dm_os_cluster_nodes
"@
        if ($fciDt -and $fciDt.Rows.Count -gt 0) {
            foreach ($row in $fciDt.Rows) {
                $techs += @{
                    technology = "Windows Failover Cluster"
                    techType   = "Clustering_WSFC"
                    name       = $row.NodeName
                    status     = $row.NodeStatus
                    role       = $row.Role
                }
            }
        }

        if ($techs.Count -gt 0) {
            $headers = @{}
            if ($IngestKey) { $headers["X-Sovereign-Ingest-Key"] = $IngestKey }
            $body = @{
                serverName   = $canonicalName
                technologies = @($techs)
            }
            Invoke-SovereignApi -Method POST -Path "/api/ingest/technologies" -Body $body -Headers $headers | Out-Null
            Write-Host "  Technology detection ingested ($($techs.Count) technologies)."
        } else {
            Write-Host "  No HA/DR technologies detected for $srv."
        }
    }

    if ($SubmitAgentStatus) {
        $agentDt = Invoke-SqlDataTable -ServerName $srv -Query @"
SELECT servicename, status_desc, startup_type_desc, last_startup_time
FROM sys.dm_server_services
WHERE servicename LIKE '%Agent%' OR servicename LIKE '%SQL Server%'
"@
        if ($agentDt -and $agentDt.Rows.Count -gt 0) {
            $services = @()
            foreach ($row in $agentDt.Rows) {
                $isRunning = $row.status_desc -eq 'Running'
                $services += @{
                    serviceName = $row.servicename
                    isRunning   = $isRunning
                    startMode   = $row.startup_type_desc
                    stopReason  = if (-not $isRunning) { "Service not running at collection time" } else { $null }
                }
            }
            $headers = @{}
            if ($IngestKey) { $headers["X-Sovereign-Ingest-Key"] = $IngestKey }
            $body = @{
                serverName = $canonicalName
                services   = @($services)
            }
            Invoke-SovereignApi -Method POST -Path "/api/ingest/agent-status" -Body $body -Headers $headers | Out-Null
            Write-Host "  Agent status ingested ($($services.Count) services)."
        } else {
            Write-Host "  No agent service data for $srv."
        }
    }

    if ($SubmitQueryStats) {
        $qs = Get-QueryStoreStats -ServerName $srv
        if ($qs -and $qs.Count -gt 0) {
            $headers = @{}
            if ($IngestKey) { $headers["X-Sovereign-Ingest-Key"] = $IngestKey }
            $body = @{
                serverName = $canonicalName
                queries    = @($qs)
            }
            Invoke-SovereignApi -Method POST -Path "/api/ingest/query-stats" -Body $body -Headers $headers | Out-Null
            Write-Host "  Query store stats ingested ($($qs.Count) queries)."
        } else {
            Write-Host "  No Query Store data for $srv (QS may not be enabled)."
        }
    }

    if ($SubmitSecurity) {
        $sec = Get-SecuritySnapshot -ServerName $srv
        $headers = @{}
        if ($IngestKey) { $headers["X-Sovereign-Ingest-Key"] = $IngestKey }
        $body = @{
            serverName     = $canonicalName
            tde            = @($sec.tde)
            tls            = $sec.tls
            keyVault       = @($sec.keyVault)
            serverSecurity = $sec.serverSecurity
        }
        Invoke-SovereignApi -Method POST -Path "/api/ingest/security" -Body $body -Headers $headers | Out-Null
        $kvCount = @($sec.keyVault).Count
        Write-Host "  Security ingested: TDE=$($sec.tde.Count) TLS compliant=$($sec.tls.isCompliant) KeyVault/EKM=$kvCount STIG probes=$($sec.serverSecurity.sqlAuditEnabled)"
    }
}

Write-Host "DBAOps Sovereign™ Collector — Run complete."
Write-Host "Powered by RHOYLLE INNOVATION EMPIRE LLC"
