<#
================================================================================
    DBAOps Dashboard Inventory Collector
    Author: John Bosco RHOYLLE INNOVATION EMPIRE™ (JB), Sr SQL DBA/DBAOps Engineer
    Created: 2026-01-13
    
    Purpose: Collects SQL Server inventory from CMS and populates dashboard tables
    Tables: dbo.dim_Servers, inventory.dim_ServerBuilds
    Runtime: ~60 seconds for 8 servers
    
    Usage: .\Update-DashboardInventory.ps1 [-Verbose]
================================================================================
#>

[CmdletBinding()]
param()

#region Configuration
$ErrorActionPreference = "Continue"
$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 }
$CentralServer = $cfg.CentralServer
$CentralDB = $cfg.Database
$LogFile = Join-Path $cfg.InstallPath "Logs\DashboardInventory_$(Get-Date -Format 'yyyyMMdd_HHmmss').log"

# Ensure log directory exists
$LogDir = Split-Path $LogFile -Parent
if (-not (Test-Path $LogDir)) {
    New-Item -ItemType Directory -Path $LogDir -Force | Out-Null
}

function Write-Log {
    param([string]$Message, [string]$Level = "INFO")
    $Timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
    $LogMessage = "[$Timestamp] [$Level] $Message"
    Write-Output $LogMessage
    Add-Content -Path $LogFile -Value $LogMessage
}
#endregion

#region SQL Reference Data
$BuildReference = @{
    17 = @{ LatestCU = "RTM"; LatestBuild = "17.0.1000.0" }  # SQL 2025
    16 = @{ LatestCU = "CU16"; LatestBuild = "16.0.4165.4" }  # SQL 2022
    15 = @{ LatestCU = "CU29"; LatestBuild = "15.0.4395.2" }  # SQL 2019
    14 = @{ LatestCU = "CU31"; LatestBuild = "14.0.3485.1" }  # SQL 2017
    13 = @{ LatestCU = "SP3";  LatestBuild = "13.0.6441.1" }  # SQL 2016
}

function Get-BuildsToLatest {
    param(
        [int]$MajorVersion,
        [string]$CurrentCU
    )
    
    if (-not $BuildReference.ContainsKey($MajorVersion)) { return 0 }
    
    $LatestCU = $BuildReference[$MajorVersion].LatestCU
    
    # Extract CU numbers
    if ($CurrentCU -match 'CU(\d+)') {
        $CurrentNum = [int]$matches[1]
    } else {
        return 999  # Unknown/RTM
    }
    
    if ($LatestCU -match 'CU(\d+)') {
        $LatestNum = [int]$matches[1]
        return [Math]::Max(0, $LatestNum - $CurrentNum)
    }
    
    return 0
}
#endregion

#region Main Collection
Write-Log "========================================"
Write-Log "Starting Dashboard Inventory Collection"
Write-Log "========================================"

try {
    # Import dbatools
    Write-Log "Loading dbatools module..."
    if (-not (Get-Module -ListAvailable -Name dbatools)) {
        Write-Log "ERROR: dbatools module not installed. Install with: Install-Module dbatools -Force" -Level "ERROR"
        exit 1
    }
    
    # Set dbatools config to avoid network path issues
    Import-Module dbatools -ErrorAction Stop -WarningAction SilentlyContinue
    Set-DbatoolsConfig -FullName path.dbatoolsdata -Value "$env:LOCALAPPDATA\dbatools" -PassThru | Out-Null
    Set-DbatoolsConfig -FullName path.dbatoolslogpath -Value "E:\DBAOps\Logs" -PassThru | Out-Null
    
    Write-Log "dbatools loaded successfully"

    # Get servers from CMS (Central Management Servers)
    Write-Log "Querying CMS for registered servers..."
    $Servers = Invoke-DbaQuery -SqlInstance $CentralServer -Database "msdb" -Query @"
SELECT DISTINCT server_name AS ServerName
FROM msdb.dbo.sysmanagement_shared_registered_servers_internal
WHERE server_name IS NOT NULL
  AND server_name <> ''
  AND server_name NOT IN ('ACTICIPOSQL')  -- Exclude decommissioned servers
  AND server_name NOT LIKE 'MAZ%'  -- Exclude Mesa/Azure servers
ORDER BY server_name
"@

    if (-not $Servers) {
        Write-Log "WARNING: No servers found in CMS" -Level "WARN"
        exit 0
    }

    Write-Log "Found $($Servers.Count) servers in CMS to collect from"
    Write-Log ""

    # Collection arrays
    $ServerResults = @()
    $BuildResults = @()
    $SuccessCount = 0
    $FailCount = 0

    # Collect from each server
    foreach ($ServerRow in $Servers) {
        $ServerName = $ServerRow.ServerName
        Write-Log "Processing: $ServerName"
        
        try {
            # Test connection first
            $TestResult = Test-DbaConnection -SqlInstance $ServerName -EnableException
            
            if (-not $TestResult.IsPingable) {
                Write-Log "  SKIP: Server not reachable" -Level "WARN"
                $FailCount++
                continue
            }

            # Collect server properties using direct SQL query (more reliable)
            $VersionQuery = @"
SELECT 
    SERVERPROPERTY('ProductVersion') AS ProductVersion,
    SERVERPROPERTY('Edition') AS Edition,
    SERVERPROPERTY('ProductLevel') AS ProductLevel,
    SERVERPROPERTY('ProductUpdateLevel') AS ProductUpdateLevel
"@
            $VersionInfo = Invoke-DbaQuery -SqlInstance $ServerName -Query $VersionQuery -ErrorAction Stop
            
            # Extract key properties
            $ProductVersion = $VersionInfo.ProductVersion
            $Edition = $VersionInfo.Edition
            $ProductLevel = $VersionInfo.ProductLevel
            $ProductUpdateLevel = $VersionInfo.ProductUpdateLevel
            
            # Parse version
            $VersionParts = $ProductVersion -split '\.'
            $MajorVersion = [int]$VersionParts[0]
            
            # Determine CU level
            $CULevel = if ($ProductUpdateLevel) { $ProductUpdateLevel } else { $ProductLevel }
            
            # Get latest build info
            $LatestBuild = $null
            $BuildsToLatest = 0
            if ($BuildReference.ContainsKey($MajorVersion)) {
                $LatestBuild = $BuildReference[$MajorVersion].LatestBuild
                $BuildsToLatest = Get-BuildsToLatest -MajorVersion $MajorVersion -CurrentCU $CULevel
            }

            # Add to results
            $ServerResults += [PSCustomObject]@{
                ServerName = $ServerName
                Edition = $Edition
                SQLVersion = $ProductVersion
            }

            $BuildResults += [PSCustomObject]@{
                ServerName = $ServerName
                ProductVersion = $ProductVersion
                ProductLevel = $ProductLevel
                ProductUpdateLevel = $ProductUpdateLevel
                MajorVersion = $MajorVersion
                CurrentBuild = $ProductVersion
                CurrentCULevel = if ($CULevel -match 'CU(\d+)') { [int]$matches[1] } else { 0 }
                BuildsToLatest = $BuildsToLatest
                LatestBuild = $LatestBuild
            }

            Write-Log "  SUCCESS: $Edition, $ProductVersion, $CULevel, $BuildsToLatest builds behind"
            $SuccessCount++

        } catch {
            Write-Log "  ERROR: $($_.Exception.Message)" -Level "ERROR"
            $FailCount++
        }
    }

    Write-Log ""
    Write-Log "========================================"
    Write-Log "Collection Summary"
    Write-Log "  Total Servers: $($Servers.Count)"
    Write-Log "  Successful: $SuccessCount"
    Write-Log "  Failed: $FailCount"
    Write-Log "========================================"
    Write-Log ""

    # Update/Insert dim_Servers (MERGE pattern)
    if ($ServerResults.Count -gt 0) {
        Write-Log "Updating dbo.dim_Servers..."
        
        foreach ($Server in $ServerResults) {
            $MergeQuery = @"
MERGE dbo.dim_Servers AS Target
USING (SELECT '$($Server.ServerName)' AS ServerName) AS Source
ON Target.ServerName = Source.ServerName
WHEN MATCHED THEN
    UPDATE SET 
        Edition = '$($Server.Edition)',
        SQLVersion = '$($Server.SQLVersion)',
        ModifiedDate = GETDATE()
WHEN NOT MATCHED THEN
    INSERT (ServerName, Edition, SQLVersion, IsActive, CreatedDate, ModifiedDate)
    VALUES ('$($Server.ServerName)', '$($Server.Edition)', '$($Server.SQLVersion)', 1, GETDATE(), GETDATE());
"@
            try {
                Invoke-DbaQuery -SqlInstance $CentralServer -Database $CentralDB -Query $MergeQuery -ErrorAction Stop
                Write-Log "  Updated: $($Server.ServerName)"
            } catch {
                Write-Log "  ERROR updating $($Server.ServerName): $($_.Exception.Message)" -Level "ERROR"
            }
        }
    }

    # Update/Insert dim_ServerBuilds (MERGE pattern)
    if ($BuildResults.Count -gt 0) {
        Write-Log ""
        Write-Log "Updating inventory.dim_ServerBuilds..."
        
        foreach ($Build in $BuildResults) {
            $MergeQuery = @"
MERGE inventory.dim_ServerBuilds AS Target
USING (SELECT '$($Build.ServerName)' AS ServerName) AS Source
ON Target.ServerName = Source.ServerName
WHEN MATCHED THEN
    UPDATE SET 
        ProductVersion = '$($Build.ProductVersion)',
        ProductLevel = '$($Build.ProductLevel)',
        ProductUpdateLevel = '$($Build.ProductUpdateLevel)',
        CurrentBuild = '$($Build.CurrentBuild)',
        CurrentCULevel = $($Build.CurrentCULevel),
        LatestAvailableBuild = '$($Build.LatestBuild)',
        BuildsBehind = $($Build.BuildsToLatest),
        LastChecked = GETDATE(),
        LastUpdated = GETDATE(),
        IsSupported = 1
WHEN NOT MATCHED THEN
    INSERT (ServerName, ProductVersion, ProductLevel, ProductUpdateLevel, CurrentVersion, CurrentBuild, 
            CurrentCULevel, LatestAvailableBuild, BuildsBehind, LastChecked, LastUpdated, IsSupported)
    VALUES ('$($Build.ServerName)', '$($Build.ProductVersion)', '$($Build.ProductLevel)', 
            '$($Build.ProductUpdateLevel)', '$($Build.CurrentBuild)', '$($Build.CurrentBuild)', 
            $($Build.CurrentCULevel), '$($Build.LatestBuild)', $($Build.BuildsToLatest), 
            GETDATE(), GETDATE(), 1);
"@
            try {
                Invoke-DbaQuery -SqlInstance $CentralServer -Database $CentralDB -Query $MergeQuery -ErrorAction Stop
                Write-Log "  Updated: $($Build.ServerName)"
            } catch {
                Write-Log "  ERROR updating $($Build.ServerName): $($_.Exception.Message)" -Level "ERROR"
            }
        }
    }

    Write-Log ""
    Write-Log "========================================"
    Write-Log "Dashboard Inventory Update COMPLETE"
    Write-Log "========================================"
    Write-Log "Log file: $LogFile"

} catch {
    Write-Log "FATAL ERROR: $($_.Exception.Message)" -Level "ERROR"
    Write-Log "Stack Trace: $($_.ScriptStackTrace)" -Level "ERROR"
    exit 1
}
#endregion