# DBAOps.PredictiveAnalytics.psm1 - Predictive Analytics for SQL Server
# Author: JB Gakumba - Sr SQL DBA / DBAOps Architect
# Version: 1.0.0 | January 2026
#
# SOURCE OF TRUTH: Central Management Server (CMS)
# - Server list from: config.ServerInventory (CMS-synced)
# - Only processes CMS-registered servers
# - Integrates with Help Desk Environment Intelligence Panel

$Script:Config = @{
    CentralServer = "AZDBAOPSSQL01"
    Database = "DBAOpsRepository"
    # CMS is the source of truth for server inventory
    CMSSourceTable = "config.ServerInventory"
    CMSActiveFilter = "IsActive = 1 AND IsSQLServer = 1"
}

function Get-CapacityForecast {
<#
.SYNOPSIS
    Predicts when disk drives will reach capacity
.PARAMETER ServerName
    Filter by server (optional)
.PARAMETER DaysAhead
    Days to forecast (default: 90)
.EXAMPLE
    Get-CapacityForecast
    Get-CapacityForecast -ServerName "SQLPROD01"
#>
    [CmdletBinding()]
    param(
        [string]$ServerName,
        [int]$DaysAhead = 90
    )
    
    Write-Host "Analyzing capacity forecasts..." -ForegroundColor Cyan
    
    $serverFilter = ""
    if ($ServerName) { $serverFilter = "AND d.ServerName = '$ServerName'" }
    
    # CMS is source of truth - only query CMS-registered servers
    $query = "
    SELECT 
        d.ServerName,
        d.DriveLetter,
        d.TotalSpaceGB,
        d.FreeSpaceGB,
        (100 - d.FreePercent),
        d.CaptureDate,
        si.CMSGroupName,
        si.Environment
    FROM fact.DiskSpaceMetrics d
    INNER JOIN config.ServerInventory si ON d.ServerName = si.ServerName  -- CMS filter
    WHERE si.IsActive = 1 AND si.IsSQLServer = 1  -- Only CMS-registered servers
    AND d.CaptureDate >= DATEADD(DAY, -30, GETDATE())
    $serverFilter
    ORDER BY d.ServerName, d.DriveLetter, d.CaptureDate
    "
    
    try {
        $diskData = Invoke-DbaQuery -SqlInstance $Script:Config.CentralServer -Database $Script:Config.Database -Query $query
        
        if (-not $diskData) {
            Write-Host "No disk data found. Using sample forecast." -ForegroundColor Yellow
            return Get-SampleCapacityForecast
        }
        
        $forecasts = @()
        $grouped = $diskData | Group-Object ServerName, DriveLetter
        
        foreach ($group in $grouped) {
            $sorted = $group.Group | Sort-Object CaptureDate
            $first = $sorted | Select-Object -First 1
            $last = $sorted | Select-Object -Last 1
            
            $daysDiff = (($last.CaptureDate) - ($first.CaptureDate)).TotalDays
            if ($daysDiff -lt 1) { $daysDiff = 1 }
            
            $usedFirst = $first.TotalSpaceGB - $first.FreeSpaceGB
            $usedLast = $last.TotalSpaceGB - $last.FreeSpaceGB
            $dailyGrowth = ($usedLast - $usedFirst) / $daysDiff
            
            $daysUntilFull = 9999
            if ($dailyGrowth -gt 0.01) {
                $daysUntilFull = [int]($last.FreeSpaceGB / $dailyGrowth)
            }
            
            $severity = "Healthy"
            if ($daysUntilFull -le 30) { $severity = "Critical" }
            elseif ($daysUntilFull -le 90) { $severity = "Warning" }
            elseif ($daysUntilFull -le 180) { $severity = "Caution" }
            
            $forecasts += [PSCustomObject]@{
                ServerName = $last.ServerName
                Drive = $last.DriveLetter
                TotalGB = [math]::Round($last.TotalSpaceGB, 1)
                FreeGB = [math]::Round($last.FreeSpaceGB, 1)
                FreePercent = [math]::Round($last.FreePercent, 1)
                DailyGrowthGB = [math]::Round($dailyGrowth, 3)
                DaysUntilFull = $daysUntilFull
                PredictedFullDate = if ($daysUntilFull -lt 9999) { (Get-Date).AddDays($daysUntilFull).ToString("yyyy-MM-dd") } else { "N/A" }
                Severity = $severity
            }
        }
        
        $critical = ($forecasts | Where-Object { $_.Severity -eq "Critical" }).Count
        $warning = ($forecasts | Where-Object { $_.Severity -eq "Warning" }).Count
        
        Write-Host "Forecast complete: $($forecasts.Count) drives analyzed" -ForegroundColor Green
        Write-Host "  Critical: $critical | Warning: $warning" -ForegroundColor $(if ($critical -gt 0) { "Red" } elseif ($warning -gt 0) { "Yellow" } else { "Green" })
        
        return $forecasts | Sort-Object DaysUntilFull
        
    } catch {
        Write-Warning "Capacity forecast failed: $_"
        return Get-SampleCapacityForecast
    }
}

function Get-SampleCapacityForecast {
    # Returns sample data when no actual data available
    return @(
        [PSCustomObject]@{ ServerName = "Sample"; Drive = "C"; TotalGB = 100; FreeGB = 25; FreePercent = 75; DailyGrowthGB = 0.5; DaysUntilFull = 50; PredictedFullDate = (Get-Date).AddDays(50).ToString("yyyy-MM-dd"); Severity = "Warning" }
    )
}

function Get-GrowthTrend {
<#
.SYNOPSIS
    Analyzes database growth trends
.PARAMETER ServerName
    Filter by server
.PARAMETER DatabaseName
    Filter by database
.PARAMETER Days
    Days of history to analyze (default: 30)
.EXAMPLE
    Get-GrowthTrend
#>
    [CmdletBinding()]
    param(
        [string]$ServerName,
        [string]$DatabaseName,
        [int]$Days = 30
    )
    
    Write-Host "Analyzing growth trends..." -ForegroundColor Cyan
    
    $filters = @()
    if ($ServerName) { $filters += "si.ServerName = '$ServerName'" }
    if ($DatabaseName) { $filters += "ds.DatabaseName = '$DatabaseName'" }
    $whereClause = if ($filters.Count -gt 0) { "AND " + ($filters -join " AND ") } else { "" }
    
    # CMS is source of truth - only query CMS-registered servers
    $query = "
    SELECT 
        ds.ServerName,
        ds.DatabaseName,
        MIN(ds.SizeMB) AS StartSizeMB,
        MAX(ds.SizeMB) AS EndSizeMB,
        MAX(ds.SizeMB) - MIN(ds.SizeMB) AS GrowthMB,
        COUNT(*) AS DataPoints
    FROM inventory.DatabaseSizes ds
    INNER JOIN config.ServerInventory si ON ds.ServerName = si.ServerName  -- CMS filter
    WHERE si.IsActive = 1 AND si.IsSQLServer = 1  -- Only CMS-registered servers
    AND ds.CollectedDate >= DATEADD(DAY, -$Days, GETDATE())
    $whereClause
    GROUP BY ds.ServerName, ds.DatabaseName
    HAVING COUNT(*) >= 2
    ORDER BY (MAX(ds.SizeMB) - MIN(ds.SizeMB)) DESC
    "
    
    try {
        $data = Invoke-DbaQuery -SqlInstance $Script:Config.CentralServer -Database $Script:Config.Database -Query $query
        
        if (-not $data) {
            Write-Host "No growth data found." -ForegroundColor Yellow
            return $null
        }
        
        $trends = @()
        foreach ($db in $data) {
            $growthPercent = 0
            if ($db.StartSizeMB -gt 0) {
                $growthPercent = ($db.GrowthMB / $db.StartSizeMB) * 100
            }
            
            $trendType = "Stable"
            if ($growthPercent -gt 50) { $trendType = "Rapid Growth" }
            elseif ($growthPercent -gt 10) { $trendType = "Moderate Growth" }
            elseif ($growthPercent -gt 0) { $trendType = "Slow Growth" }
            elseif ($growthPercent -lt -5) { $trendType = "Shrinking" }
            
            $trends += [PSCustomObject]@{
                ServerName = $db.ServerName
                DatabaseName = $db.DatabaseName
                StartSizeMB = [math]::Round($db.StartSizeMB, 0)
                EndSizeMB = [math]::Round($db.EndSizeMB, 0)
                GrowthMB = [math]::Round($db.GrowthMB, 0)
                GrowthPercent = [math]::Round($growthPercent, 1)
                TrendType = $trendType
                IsAnomaly = $growthPercent -gt 50
            }
        }
        
        Write-Host "Growth analysis complete: $($trends.Count) databases" -ForegroundColor Green
        return $trends
        
    } catch {
        Write-Warning "Growth trend analysis failed: $_"
        return $null
    }
}

function Find-PerformanceAnomaly {
<#
.SYNOPSIS
    Detects performance anomalies
.PARAMETER ServerName
    Filter by server
.PARAMETER Hours
    Hours of recent data to check (default: 24)
.EXAMPLE
    Find-PerformanceAnomaly -Hours 48
#>
    [CmdletBinding()]
    param(
        [string]$ServerName,
        [int]$Hours = 24
    )
    
    Write-Host "Scanning for performance anomalies..." -ForegroundColor Cyan
    
    $serverFilter = ""
    if ($ServerName) { $serverFilter = "AND pm.ServerName = '$ServerName'" }
    
    # CMS is source of truth - only query CMS-registered servers
    # Check for high CPU servers
    $query = "
    SELECT 
        pm.ServerName,
        'CPU' AS MetricName,
        AVG(pm.CPUPercent) AS AvgValue,
        MAX(pm.CPUPercent) AS MaxValue,
        si.Environment,
        si.CMSGroupName
    FROM performance.ServerMetrics pm
    INNER JOIN config.ServerInventory si ON pm.ServerName = si.ServerName  -- CMS filter
    WHERE si.IsActive = 1 AND si.IsSQLServer = 1  -- Only CMS-registered servers
    AND pm.CaptureDate >= DATEADD(HOUR, -$Hours, GETDATE())
    $serverFilter
    GROUP BY pm.ServerName, si.Environment, si.CMSGroupName
    HAVING AVG(pm.CPUPercent) > 80 OR MAX(pm.CPUPercent) > 95
    "
    
    try {
        $anomalies = @()
        $data = Invoke-DbaQuery -SqlInstance $Script:Config.CentralServer -Database $Script:Config.Database -Query $query -ErrorAction SilentlyContinue
        
        if ($data) {
            foreach ($item in $data) {
                $severity = "Warning"
                if ($item.AvgValue -gt 90 -or $item.MaxValue -gt 98) { $severity = "Critical" }
                
                $anomalies += [PSCustomObject]@{
                    ServerName = $item.ServerName
                    MetricName = $item.MetricName
                    AvgValue = [math]::Round($item.AvgValue, 1)
                    MaxValue = [math]::Round($item.MaxValue, 1)
                    Severity = $severity
                    DetectedAt = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
                    Recommendation = "Investigate high CPU. Check for runaway queries or increased workload."
                }
            }
        }
        
        if ($anomalies.Count -eq 0) {
            Write-Host "No anomalies detected" -ForegroundColor Green
        } else {
            $critical = ($anomalies | Where-Object { $_.Severity -eq "Critical" }).Count
            Write-Host "Found $($anomalies.Count) anomalies ($critical critical)" -ForegroundColor $(if ($critical -gt 0) { "Red" } else { "Yellow" })
        }
        
        return $anomalies
        
    } catch {
        Write-Host "No performance data available for anomaly detection" -ForegroundColor Yellow
        return @()
    }
}

function Get-FailurePrediction {
<#
.SYNOPSIS
    Predicts likely failures based on patterns
.PARAMETER ServerName
    Filter by server
.EXAMPLE
    Get-FailurePrediction
#>
    [CmdletBinding()]
    param(
        [string]$ServerName
    )
    
    Write-Host "Analyzing failure patterns..." -ForegroundColor Cyan
    
    $serverFilter = ""
    if ($ServerName) { $serverFilter = "AND j.ServerName = '$ServerName'" }
    
    # CMS is source of truth - only query CMS-registered servers
    # Analyze job failure rates
    $jobQuery = "
    SELECT 
        j.ServerName,
        j.JobName,
        COUNT(*) AS TotalRuns,
        SUM(CASE WHEN j.LastRunOutcome = 'Failed' THEN 1 ELSE 0 END) AS FailedRuns,
        si.Environment,
        si.CMSGroupName
    FROM jobs.SQLAgentJobs j
    INNER JOIN config.ServerInventory si ON j.ServerName = si.ServerName  -- CMS filter
    WHERE si.IsActive = 1 AND si.IsSQLServer = 1  -- Only CMS-registered servers
    AND j.LastRunTime >= DATEADD(DAY, -30, GETDATE())
    $serverFilter
    GROUP BY j.ServerName, j.JobName, si.Environment, si.CMSGroupName
    HAVING SUM(CASE WHEN j.LastRunOutcome = 'Failed' THEN 1 ELSE 0 END) > 0
    "
    
    try {
        $predictions = @()
        $jobData = Invoke-DbaQuery -SqlInstance $Script:Config.CentralServer -Database $Script:Config.Database -Query $jobQuery -ErrorAction SilentlyContinue
        
        if ($jobData) {
            foreach ($job in $jobData) {
                $failureRate = ($job.FailedRuns / $job.TotalRuns) * 100
                $riskLevel = "Low"
                if ($failureRate -gt 50) { $riskLevel = "High" }
                elseif ($failureRate -gt 25) { $riskLevel = "Medium" }
                
                if ($failureRate -gt 10) {
                    $predictions += [PSCustomObject]@{
                        ServerName = $job.ServerName
                        PredictionType = "JobFailure"
                        ObjectName = $job.JobName
                        FailureRate = [math]::Round($failureRate, 1)
                        TotalRuns = $job.TotalRuns
                        FailedRuns = $job.FailedRuns
                        RiskLevel = $riskLevel
                        Probability = [math]::Min(95, [math]::Round($failureRate * 1.2, 0))
                        RecommendedAction = "Review job $($job.JobName) - $([math]::Round($failureRate, 0))% failure rate"
                    }
                }
            }
        }
        
        $highRisk = ($predictions | Where-Object { $_.RiskLevel -eq "High" }).Count
        Write-Host "Predictions complete: $($predictions.Count) items ($highRisk high risk)" -ForegroundColor $(if ($highRisk -gt 0) { "Red" } else { "Green" })
        
        return $predictions | Sort-Object FailureRate -Descending
        
    } catch {
        Write-Host "No job data available for failure prediction" -ForegroundColor Yellow
        return @()
    }
}

function Invoke-AutoRemediation {
<#
.SYNOPSIS
    Executes auto-remediation for common issues
.PARAMETER IssueType
    Type: DiskSpace, HighCPU, StaleStats, BlockedProcess
.PARAMETER ServerName
    Target server
.PARAMETER WhatIf
    Preview only
.EXAMPLE
    Invoke-AutoRemediation -IssueType StaleStats -ServerName SQLPROD01 -WhatIf
#>
    [CmdletBinding(SupportsShouldProcess)]
    param(
        [Parameter(Mandatory)]
        [ValidateSet('DiskSpace', 'HighCPU', 'StaleStats', 'BlockedProcess')]
        [string]$IssueType,
        
        [Parameter(Mandatory)]
        [string]$ServerName
    )
    
    Write-Host "Auto-remediation: $IssueType on $ServerName" -ForegroundColor Cyan
    
    $action = switch ($IssueType) {
        'DiskSpace'     { "Review and clean old backup files, transaction logs" }
        'HighCPU'       { "Identify and optimize top CPU-consuming queries" }
        'StaleStats'    { "Run UPDATE STATISTICS on tables with stale stats" }
        'BlockedProcess' { "Identify and resolve blocking chains" }
    }
    
    $result = [PSCustomObject]@{
        ServerName = $ServerName
        IssueType = $IssueType
        Action = $action
        Status = "Recommended"
        ExecutedAt = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
    }
    
    if ($PSCmdlet.ShouldProcess($ServerName, $action)) {
        Write-Host "  Action: $action" -ForegroundColor Yellow
        Write-Host "  Status: Manual review recommended" -ForegroundColor Gray
        
        # Log to database
        $logQuery = "INSERT INTO analytics.RemediationLog (ServerName, IssueType, ActionTaken, Status, ExecutedBy) VALUES ('$ServerName', '$IssueType', '$action', 'Logged', '$env:USERNAME')"
        try {
            Invoke-DbaQuery -SqlInstance $Script:Config.CentralServer -Database $Script:Config.Database -Query $logQuery -ErrorAction SilentlyContinue
        } catch { }
    }
    
    return $result
}

function Get-PredictiveAnalyticsSummary {
<#
.SYNOPSIS
    Gets comprehensive predictive analytics summary
.EXAMPLE
    Get-PredictiveAnalyticsSummary
#>
    [CmdletBinding()]
    param()
    
    Write-Host ""
    Write-Host "============================================" -ForegroundColor Cyan
    Write-Host "  DBAOps PREDICTIVE ANALYTICS SUMMARY" -ForegroundColor Cyan
    Write-Host "  Source: CMS (Central Management Server)" -ForegroundColor Gray
    Write-Host "============================================" -ForegroundColor Cyan
    Write-Host ""
    
    $summary = @{
        GeneratedAt = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
        HealthScore = 100
        CapacityAlerts = @{ Critical = 0; Warning = 0 }
        Anomalies = @{ Critical = 0; Warning = 0 }
        FailureRisks = @{ High = 0; Medium = 0 }
        Recommendations = @()
    }
    
    # Get capacity forecasts
    Write-Host "1. Capacity Forecasting" -ForegroundColor White
    $capacity = Get-CapacityForecast -ErrorAction SilentlyContinue
    if ($capacity) {
        $summary.CapacityAlerts.Critical = ($capacity | Where-Object { $_.Severity -eq "Critical" }).Count
        $summary.CapacityAlerts.Warning = ($capacity | Where-Object { $_.Severity -eq "Warning" }).Count
    }
    Write-Host ""
    
    # Get anomalies
    Write-Host "2. Anomaly Detection" -ForegroundColor White
    $anomalies = Find-PerformanceAnomaly -ErrorAction SilentlyContinue
    if ($anomalies) {
        $summary.Anomalies.Critical = ($anomalies | Where-Object { $_.Severity -eq "Critical" }).Count
        $summary.Anomalies.Warning = ($anomalies | Where-Object { $_.Severity -eq "Warning" }).Count
    }
    Write-Host ""
    
    # Get failure predictions
    Write-Host "3. Failure Prediction" -ForegroundColor White
    $failures = Get-FailurePrediction -ErrorAction SilentlyContinue
    if ($failures) {
        $summary.FailureRisks.High = ($failures | Where-Object { $_.RiskLevel -eq "High" }).Count
        $summary.FailureRisks.Medium = ($failures | Where-Object { $_.RiskLevel -eq "Medium" }).Count
    }
    Write-Host ""
    
    # Calculate health score
    $deductions = 0
    $deductions += $summary.CapacityAlerts.Critical * 15
    $deductions += $summary.CapacityAlerts.Warning * 5
    $deductions += $summary.Anomalies.Critical * 10
    $deductions += $summary.Anomalies.Warning * 3
    $deductions += $summary.FailureRisks.High * 8
    $deductions += $summary.FailureRisks.Medium * 3
    
    $summary.HealthScore = [math]::Max(0, 100 - $deductions)
    
    # Generate recommendations
    if ($summary.CapacityAlerts.Critical -gt 0) {
        $summary.Recommendations += "URGENT: $($summary.CapacityAlerts.Critical) drive(s) predicted to fill within 30 days"
    }
    if ($summary.FailureRisks.High -gt 0) {
        $summary.Recommendations += "REVIEW: $($summary.FailureRisks.High) high-risk failure prediction(s)"
    }
    if ($summary.Anomalies.Critical -gt 0) {
        $summary.Recommendations += "INVESTIGATE: $($summary.Anomalies.Critical) critical performance anomaly(ies)"
    }
    
    # Display summary
    Write-Host "============================================" -ForegroundColor Cyan
    Write-Host "  SUMMARY" -ForegroundColor Cyan
    Write-Host "============================================" -ForegroundColor Cyan
    Write-Host ""
    
    $scoreColor = if ($summary.HealthScore -ge 80) { "Green" } elseif ($summary.HealthScore -ge 60) { "Yellow" } else { "Red" }
    Write-Host "  Health Score: $($summary.HealthScore)/100" -ForegroundColor $scoreColor
    Write-Host ""
    Write-Host "  Capacity: $($summary.CapacityAlerts.Critical) critical, $($summary.CapacityAlerts.Warning) warning" -ForegroundColor $(if ($summary.CapacityAlerts.Critical -gt 0) { "Red" } else { "Green" })
    Write-Host "  Anomalies: $($summary.Anomalies.Critical) critical, $($summary.Anomalies.Warning) warning" -ForegroundColor $(if ($summary.Anomalies.Critical -gt 0) { "Red" } else { "Green" })
    Write-Host "  Failures: $($summary.FailureRisks.High) high risk, $($summary.FailureRisks.Medium) medium" -ForegroundColor $(if ($summary.FailureRisks.High -gt 0) { "Red" } else { "Green" })
    Write-Host ""
    
    if ($summary.Recommendations.Count -gt 0) {
        Write-Host "  Recommendations:" -ForegroundColor Yellow
        foreach ($rec in $summary.Recommendations) {
            Write-Host "    - $rec" -ForegroundColor White
        }
    } else {
        Write-Host "  All systems healthy!" -ForegroundColor Green
    }
    
    Write-Host ""
    Write-Host "============================================" -ForegroundColor Cyan
    
    return $summary
}

Export-ModuleMember -Function @(
    'Get-CapacityForecast',
    'Get-GrowthTrend',
    'Find-PerformanceAnomaly',
    'Get-FailurePrediction',
    'Invoke-AutoRemediation',
    'Get-PredictiveAnalyticsSummary'
)



