# ============================================
# DBAOps Dashboard Data with Predictive Analytics
# Wrapper script - runs main script then adds analytics
# ============================================

# Load config
$scriptDir = Split-Path -Parent $MyInvocation.MyCommand.Path
$cfgPath = Join-Path (Split-Path -Parent $scriptDir) "Config\DBAOps.config.json"
$cfg = @{ InstallPath = "M:\RHOYLLE INNOVATION EMPIRE LLC\DBAOps Futuristic Enterprise"; CentralServer = $env:COMPUTERNAME; Database = "DBAOpsRepository" }
if (Test-Path $cfgPath) {
    $cfg = Get-Content $cfgPath -Raw | ConvertFrom-Json
}

# Step 1: Run the main dashboard script
Write-Host "Running main dashboard script..." -ForegroundColor Cyan
$genScript = Join-Path $cfg.InstallPath "PowerShell\Get-DBAOpsDashboardData.ps1"
if (Test-Path $genScript) {
    & $genScript
} else {
    & (Join-Path $scriptDir "Get-DBAOpsDashboardData.ps1")
}

# Step 2: Add predictive analytics to the JSON
Write-Host "`nAdding Predictive Analytics data..." -ForegroundColor Cyan

$jsonPath = Join-Path $cfg.InstallPath "WebDashboard\data.json"
$dashboardData = Get-Content $jsonPath -Raw | ConvertFrom-Json

# Get analytics data from config
$centralServer = $cfg.CentralServer
$database = $cfg.Database

# Capacity Alerts
$capacityQuery = @"
SELECT TOP 10
    d.ServerName,
    d.DriveLetter AS Drive,
    d.TotalSpaceGB AS TotalGB,
    d.FreeSpaceGB AS FreeGB,
    CAST(100 - d.FreePercent AS DECIMAL(5,1)) AS UsedPercent,
    CASE 
        WHEN d.FreePercent <= 10 THEN 'Critical'
        WHEN d.FreePercent <= 20 THEN 'Warning'
        ELSE 'Healthy'
    END AS Severity
FROM fact.DiskSpaceMetrics d
INNER JOIN config.ServerInventory si ON d.ServerName = si.ServerName
WHERE si.IsActive = 1 AND d.CaptureDate >= DATEADD(HOUR, -4, GETDATE())
AND d.FreePercent <= 25
ORDER BY d.FreePercent ASC
"@

$capacityData = Invoke-DbaQuery -SqlInstance $centralServer -Database $database -Query $capacityQuery -ErrorAction SilentlyContinue

# On-Call DBA
$onCallQuery = @"
SELECT TOP 1 PrimaryDBA, PrimaryPhone, PrimaryEmail, BackupDBA, BackupPhone, BackupEmail,
       DATEDIFF(DAY, GETDATE(), RotationEndDate) AS DaysRemaining
FROM config.OnCallRotation
WHERE IsActive = 1 AND GETDATE() BETWEEN RotationStartDate AND RotationEndDate
"@

$onCallData = Invoke-DbaQuery -SqlInstance $centralServer -Database $database -Query $onCallQuery -ErrorAction SilentlyContinue

# Environment Summary  
$envQuery = @"
SELECT 
    COUNT(*) AS TotalServers,
    SUM(CASE WHEN rs.IsOnline = 1 THEN 1 ELSE 0 END) AS OnlineServers,
    SUM(CASE WHEN rs.IsOnline = 0 THEN 1 ELSE 0 END) AS OfflineServers,
    CAST(SUM(CASE WHEN rs.IsOnline = 1 THEN 1.0 ELSE 0 END) / NULLIF(COUNT(*),0) * 100 AS DECIMAL(5,2)) AS UptimePercent
FROM config.ServerInventory si
LEFT JOIN config.ReachabilityStatus rs ON si.ServerName = rs.ServerName
WHERE si.IsActive = 1 AND si.IsSQLServer = 1
"@

$envData = Invoke-DbaQuery -SqlInstance $centralServer -Database $database -Query $envQuery -ErrorAction SilentlyContinue

# Add to dashboard data
$dashboardData | Add-Member -NotePropertyName "predictiveAnalytics" -NotePropertyValue @{
    capacityAlerts = @($capacityData | ForEach-Object {
        @{
            serverName = $_.ServerName
            drive = $_.Drive
            totalGB = $_.TotalGB
            freeGB = $_.FreeGB
            usedPercent = $_.UsedPercent
            severity = $_.Severity
        }
    })
    environmentSummary = @{
        totalServers = if ($envData -and $envData.TotalServers) { [int]$envData.TotalServers } else { 0 }
        onlineServers = if ($envData -and $envData.OnlineServers) { [int]$envData.OnlineServers } else { 0 }
        offlineServers = if ($envData -and $envData.OfflineServers) { [int]$envData.OfflineServers } else { 0 }
        uptimePercent = if ($envData -and $envData.UptimePercent) { [decimal]$envData.UptimePercent } else { 0 }
    }
    onCall = @{
        primaryDBA = $onCallData.PrimaryDBA
        primaryPhone = $onCallData.PrimaryPhone
        primaryEmail = $onCallData.PrimaryEmail
        backupDBA = $onCallData.BackupDBA
        daysRemaining = if ($onCallData -and $onCallData.DaysRemaining) { [int]$onCallData.DaysRemaining } else { 0 }
    }
    lastUpdated = (Get-Date -Format "yyyy-MM-dd HH:mm:ss")
} -Force

# Save updated JSON
$dashboardData | ConvertTo-Json -Depth 20 | Out-File $jsonPath -Encoding UTF8 -Force

Write-Host "  OK Predictive Analytics added to data.json" -ForegroundColor Green
Write-Host "`nDashboard ready! Refresh https://dbaopsdash/" -ForegroundColor Green
