# Invoke-FuturisticDiscovery.ps1
# DBAOps Futuristic Enterprise™ - Runs 14 Hidden SQL Server Risks discovery scripts
# SOURCE OF TRUTH: config.ServerInventory (synced from CMS)
# Requires: dbatools, FuturisticREPO database
#
# Usage: .\Invoke-FuturisticDiscovery.ps1 -CentralServer $env:COMPUTERNAME -RepositoryDB "FuturisticREPO"

param(
    [Parameter(Mandatory)][string]$CentralServer,
    [string]$RepositoryDB = "FuturisticREPO",
    [string[]]$ServerFilter = @()
)

$ErrorActionPreference = "Continue"

# Get servers from config.ServerInventory (CMS source of truth)
$serverQuery = @"
SELECT ServerName FROM config.ServerInventory
WHERE IsActive = 1 AND IsSQLServer = 1
"@

try {
    $servers = Invoke-DbaQuery -SqlInstance $CentralServer -Database $RepositoryDB -Query $serverQuery
} catch {
    Write-Error "Failed to get server list: $_"
    exit 1
}

if (-not $servers -or $servers.Count -eq 0) {
    Write-Warning "No servers found in config.ServerInventory. Sync from CMS first."
    exit 0
}

$serverList = $servers.ServerName
if ($ServerFilter.Count -gt 0) {
    $serverList = $serverList | Where-Object { $_ -in $ServerFilter }
}

# Get discovery definitions from FuturisticREPO
$defQuery = "SELECT TypeCode, DisplayName, QueryText, RiskNumber FROM discovery.DiscoveryDefinitions ORDER BY RiskNumber"
$definitions = Invoke-DbaQuery -SqlInstance $CentralServer -Database $RepositoryDB -Query $defQuery

$totalRuns = 0
$totalFindings = 0

foreach ($server in $serverList) {
    foreach ($def in $definitions) {
        try {
            $results = Invoke-DbaQuery -SqlInstance $server -Query $def.QueryText -EnableException
            if ($results) {
                foreach ($row in $results) {
                    $finding = ($row.PSObject.Properties | ForEach-Object { "$($_.Name)=$($_.Value)" }) -join "; "
                    $dbName = if ($row.PSObject.Properties.Name -contains 'DatabaseName') { $row.DatabaseName } else { $null }
                    $params = @{ DiscoveryType = $def.TypeCode; ServerName = $server; DatabaseName = $dbName; Finding = $finding; Severity = 'Warning' }
                    Invoke-DbaQuery -SqlInstance $CentralServer -Database $RepositoryDB -CommandType StoredProcedure -CommandText 'discovery.usp_InsertDiscoveryResult' -SqlParameters $params -EnableException
                    $totalFindings++
                }
            }
        } catch {
            $params = @{ DiscoveryType = $def.TypeCode; ServerName = $server; DatabaseName = [DBNull]::Value; Finding = "Error: $($_.Exception.Message)"; Severity = 'Critical' }
            Invoke-DbaQuery -SqlInstance $CentralServer -Database $RepositoryDB -CommandType StoredProcedure -CommandText 'discovery.usp_InsertDiscoveryResult' -SqlParameters $params -EnableException
        }
        $totalRuns++
    }
}

Write-Host "Discovery complete: $totalRuns runs, $totalFindings findings" -ForegroundColor Cyan
