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

# Step 1: Run the main dashboard script
Write-Host "Running main dashboard script..." -ForegroundColor Cyan
& "E:\DBAOps\PowerShell\Get-JBRhoylleDashboardData.ps1"

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

$jsonPath = "E:\DBAOps\WebDashboard\data.json"
$dashboardData = Get-Content $jsonPath -Raw | ConvertFrom-Json

# Get analytics data
$centralServer = "YOURSERVER"
$database = "JBRhoylleRepository"

# 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 = [int]$envData.TotalServers
        onlineServers = [int]$envData.OnlineServers
        offlineServers = [int]$envData.OfflineServers
        uptimePercent = [decimal]$envData.UptimePercent
    }
    onCall = @{
        primaryDBA = $onCallData.PrimaryDBA
        primaryPhone = $onCallData.PrimaryPhone
        primaryEmail = $onCallData.PrimaryEmail
        backupDBA = $onCallData.BackupDBA
        daysRemaining = [int]$onCallData.DaysRemaining
    }
    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
