<#
=====================================================
SQL 2016 MIGRATION DATA COLLECTION
Add this section to Get-JBRhoylleDashboardData.ps1
Insert after the existing data collection sections (around line 2133)
=====================================================
#>

# =============================================
# SQL 2016 MIGRATION PLANNING DATA
# =============================================
Write-Host "  Collecting SQL 2016 Migration data..." -ForegroundColor Yellow

$sql2016Migration = @{
    summary = @{
        total = 0
        production = 0
        development = 0
        tier1 = 0
        tier2 = 0
        tier3 = 0
        tier4 = 0
        nonCriminalJustice = 0
        enterprise = 0
        standard = 0
    }
    productionServers = @()
    developmentServers = @()
    tierSummary = @()
    versionAnalysis = @()
    applicationMapping = @()
    deadline = "2026-07-14"
    daysRemaining = 0
}

# Calculate days remaining
$deadline = [DateTime]::Parse("2026-07-14")
$today = Get-Date
$sql2016Migration.daysRemaining = ($deadline - $today).Days

# Get all SQL 2016 servers from ServerInventory
$sql2016Servers = Invoke-SqlQuery -Query @"
SELECT 
    si.ServerName,
    si.InstanceName,
    si.SQLVersion,
    si.SQLVersionMajor,
    si.SQLBuild,
    si.SQLCumulativeUpdate,
    si.Edition,
    si.Environment,
    si.ComplianceTier,
    si.CMSGroupName,
    si.IsClustered,
    si.IsAGNode,
    si.AGRole,
    si.CpuCount,
    si.TotalMemoryGB,
    si.WindowsVersion,
    si.MachineType,
    si.CollectionTime,
    CASE 
        WHEN si.Environment = 'Production' THEN 'Critical'
        WHEN si.ComplianceTier LIKE 'Tier 1%' THEN 'Critical'
        WHEN si.ComplianceTier LIKE 'Tier 2%' THEN 'High'
        WHEN si.ComplianceTier LIKE 'Tier 3%' THEN 'Medium'
        ELSE 'Low'
    END AS MigrationPriority
FROM dbo.ServerInventory si
WHERE si.SQLVersionMajor = 13
   OR si.SQLVersion LIKE '13.%'
   OR si.ServerName LIKE '%SQL16%'
   OR si.ServerName LIKE '%2016%'
ORDER BY 
    CASE si.Environment WHEN 'Production' THEN 1 ELSE 2 END,
    CASE si.ComplianceTier 
        WHEN 'Tier 1 - CriminalJustice High Security' THEN 1
        WHEN 'Tier 2 - Criminal Justice Ops' THEN 2
        WHEN 'Tier 3 - Administrative Support' THEN 3
        WHEN 'Tier 4 - Development/Test' THEN 4
        ELSE 5
    END,
    si.ServerName;
"@

if ($sql2016Servers) {
    foreach ($server in $sql2016Servers) {
        $serverObj = @{
            serverName = ConvertTo-IDSystemtring $server.ServerName
            instanceName = ConvertTo-IDSystemtring $server.InstanceName
            version = ConvertTo-IDSystemtring $server.SQLVersion
            versionMajor = ConvertTo-SafeInt $server.SQLVersionMajor
            build = ConvertTo-IDSystemtring $server.SQLBuild
            cumulativeUpdate = ConvertTo-IDSystemtring $server.SQLCumulativeUpdate
            edition = ConvertTo-IDSystemtring $server.Edition
            environment = ConvertTo-IDSystemtring $server.Environment "Unknown"
            tier = ConvertTo-IDSystemtring $server.ComplianceTier "Unclassified"
            cmsGroup = ConvertTo-IDSystemtring $server.CMSGroupName
            isClustered = [bool]$server.IsClustered
            isAGNode = [bool]$server.IsAGNode
            agRole = ConvertTo-IDSystemtring $server.AGRole
            cpuCount = ConvertTo-SafeInt $server.CpuCount
            totalMemoryGB = ConvertTo-SafeInt $server.TotalMemoryGB
            windowsVersion = ConvertTo-IDSystemtring $server.WindowsVersion
            machineType = ConvertTo-IDSystemtring $server.MachineType
            lastCollected = if ($server.CollectionTime -ne [DBNull]::Value) { $server.CollectionTime.ToString("yyyy-MM-dd HH:mm") } else { $null }
            priority = ConvertTo-IDSystemtring $server.MigrationPriority "Medium"
            notes = ""
        }
        
        # Add notes for test pairs
        if ($serverObj.serverName -match 'T$|T1$|TEST') {
            $prodName = $serverObj.serverName -replace 'T$|T1$|TEST', ''
            $serverObj.notes = "Test environment for $prodName"
        }
        
        # Categorize by environment
        $env = ConvertTo-IDSystemtring $server.Environment "Unknown"
        if ($env -eq 'Production') {
            $sql2016Migration.productionServers += $serverObj
            $sql2016Migration.summary.production++
        } else {
            $sql2016Migration.developmentServers += $serverObj
            $sql2016Migration.summary.development++
        }
        
        # Count by tier
        $tier = ConvertTo-IDSystemtring $server.ComplianceTier ""
        if ($tier -like '*Tier 1*') { $sql2016Migration.summary.tier1++ }
        elseif ($tier -like '*Tier 2*') { $sql2016Migration.summary.tier2++ }
        elseif ($tier -like '*Tier 3*') { $sql2016Migration.summary.tier3++ }
        elseif ($tier -like '*Tier 4*') { $sql2016Migration.summary.tier4++ }
        elseif ($tier -like '*Non-CriminalJustice*') { $sql2016Migration.summary.nonCriminalJustice++ }
        
        # Count by edition
        $edition = ConvertTo-IDSystemtring $server.Edition ""
        if ($edition -like '*Enterprise*') { $sql2016Migration.summary.enterprise++ }
        elseif ($edition -like '*Standard*') { $sql2016Migration.summary.standard++ }
        
        $sql2016Migration.summary.total++
    }
}

# Build tier summary
$sql2016Migration.tierSummary = @(
    @{
        tier = "Tier 1 - CriminalJustice High Security"
        production = ($sql2016Migration.productionServers | Where-Object { $_.tier -like '*Tier 1*' }).Count
        development = ($sql2016Migration.developmentServers | Where-Object { $_.tier -like '*Tier 1*' }).Count
        total = $sql2016Migration.summary.tier1
        complexity = "🔴 High - Extended testing & compliance review"
        recommendedPhase = "Phase 3 (May-Jun 2026)"
    },
    @{
        tier = "Tier 2 - Criminal Justice Ops"
        production = ($sql2016Migration.productionServers | Where-Object { $_.tier -like '*Tier 2*' }).Count
        development = ($sql2016Migration.developmentServers | Where-Object { $_.tier -like '*Tier 2*' }).Count
        total = $sql2016Migration.summary.tier2
        complexity = "🟠 Medium-High - Critical operations impact"
        recommendedPhase = "Phase 2-3 (Mar-Jun 2026)"
    },
    @{
        tier = "Tier 3 - Administrative Support"
        production = ($sql2016Migration.productionServers | Where-Object { $_.tier -like '*Tier 3*' }).Count
        development = ($sql2016Migration.developmentServers | Where-Object { $_.tier -like '*Tier 3*' }).Count
        total = $sql2016Migration.summary.tier3
        complexity = "🟡 Medium - Standard testing required"
        recommendedPhase = "Phase 2 (Mar-Apr 2026)"
    },
    @{
        tier = "Tier 4 - Development/Test"
        production = ($sql2016Migration.productionServers | Where-Object { $_.tier -like '*Tier 4*' }).Count
        development = ($sql2016Migration.developmentServers | Where-Object { $_.tier -like '*Tier 4*' }).Count
        total = $sql2016Migration.summary.tier4
        complexity = "🟢 Low - Non-production systems"
        recommendedPhase = "Phase 1 (Jan-Feb 2026)"
    },
    @{
        tier = "Non-CriminalJustice / Unclassified"
        production = ($sql2016Migration.productionServers | Where-Object { $_.tier -like '*Non-CriminalJustice*' -or $_.tier -eq 'Unclassified' }).Count
        development = ($sql2016Migration.developmentServers | Where-Object { $_.tier -like '*Non-CriminalJustice*' -or $_.tier -eq 'Unclassified' }).Count
        total = $sql2016Migration.summary.nonCriminalJustice
        complexity = "🟢 Low - Flexible scheduling"
        recommendedPhase = "Phase 1-2 (Jan-Apr 2026)"
    }
)

# Version analysis
$versionGroups = ($sql2016Migration.productionServers + $sql2016Migration.developmentServers) | Group-Object -Property version

foreach ($group in $versionGroups) {
    $sql2016Migration.versionAnalysis += @{
        version = $group.Name
        count = $group.Count
        servers = ($group.Group | Select-Object -ExpandProperty serverName) -join ", "
        status = if ($group.Name -like '13.0.7*') { "Most current for SQL 2016" } 
                 elseif ($group.Name -like '13.0.6*') { "Supported baseline" }
                 else { "Verify version" }
    }
}

# Add to main dashboard data
$dashboardData.sql2016Migration = $sql2016Migration

Write-Host "    SQL 2016 Migration: $($sql2016Migration.summary.total) servers ($($sql2016Migration.summary.production) prod, $($sql2016Migration.summary.development) dev)" -ForegroundColor $(if ($sql2016Migration.summary.total -gt 0) { "Yellow" } else { "Green" })
Write-Host "      Tier 1 (CriminalJustice High): $($sql2016Migration.summary.tier1)" -ForegroundColor $(if ($sql2016Migration.summary.tier1 -gt 0) { "Red" } else { "Green" })
Write-Host "      Tier 2 (CJ Ops): $($sql2016Migration.summary.tier2)" -ForegroundColor $(if ($sql2016Migration.summary.tier2 -gt 0) { "Yellow" } else { "Green" })
Write-Host "      Enterprise Edition: $($sql2016Migration.summary.enterprise)" -ForegroundColor Cyan
Write-Host "      Days until deadline: $($sql2016Migration.daysRemaining)" -ForegroundColor $(if ($sql2016Migration.daysRemaining -lt 180) { "Red" } elseif ($sql2016Migration.daysRemaining -lt 365) { "Yellow" } else { "Green" })

# Add SQL 2016 alert if servers exist
if ($sql2016Migration.summary.total -gt 0) {
    $dashboardData.recentAlerts += @{
        type = "warning"
        message = "$($sql2016Migration.summary.total) SQL Server 2016 instances require migration before July 2026"
        time = (Get-Date).ToString("HH:mm:ss")
        source = "Migration Planning"
    }
}

if ($sql2016Migration.summary.tier1 -gt 0) {
    $dashboardData.recentAlerts += @{
        type = "critical"
        message = "$($sql2016Migration.summary.tier1) Tier 1 CriminalJustice SQL 2016 servers need migration planning"
        time = (Get-Date).ToString("HH:mm:ss")
        source = "Migration Planning"
    }
}


# =============================================
# Alternative: Query from CMS if ServerInventory doesn't have version data
# =============================================
<#
# If ServerInventory doesn't have SQL 2016 data, query CMS directly for servers by name pattern
$cmsQuery = @"
SELECT 
    g.name AS ServerGroup,
    s.server_name AS ServerName,
    s.description AS ServerDescription,
    CASE 
        WHEN g.name LIKE '%HIGH%' OR g.name = 'PUBLISHERS' THEN 'Production'
        WHEN g.name LIKE '%NONPROD%' THEN 'Development'
        WHEN g.name LIKE '%MEDIUM%' THEN 'Production'
        ELSE 'Unknown'
    END AS Environment,
    CASE 
        WHEN g.name LIKE '%HIGH%' THEN 'Tier 1 - CriminalJustice High Security'
        WHEN g.name LIKE '%MEDIUM%' THEN 'Tier 2 - Criminal Justice Ops'
        WHEN g.name LIKE '%LOW%' THEN 'Tier 3 - Administrative Support'
        WHEN g.name LIKE '%NONPROD%' THEN 'Tier 4 - Development/Test'
        ELSE 'Unclassified'
    END AS ComplianceTier
FROM msdb.dbo.sysmanagement_shared_registered_servers s
JOIN msdb.dbo.sysmanagement_shared_server_groups g 
    ON s.server_group_id = g.server_group_id
WHERE s.server_name LIKE '%SQL16%' 
   OR s.server_name LIKE '%2016%'
ORDER BY g.name, s.server_name;
"@

$cmsServers = Invoke-SqlQuery -Query $cmsQuery
# Process CMS servers similarly...
#>
