Analyze-AzSqlDatabases.ps1

#Requires -Version 5.1

<#
.SYNOPSIS
    Analyzes Azure SQL Database metrics and generates optimization recommendations.

.DESCRIPTION
    Processes metric CSVs to calculate statistics, classify workload patterns,
    and run through a comprehensive decision tree (Tier 1-5) to generate
    specific recommendations for each database.

.PARAMETER MetricsPath
    Path to directory containing metric CSV files

.PARAMETER OutputPath
    Optional path to export results CSV

.EXAMPLE
    $results = .\Analyze-AzSqlDatabases.ps1 -MetricsPath "C:\Metrics"
    $results | Export-Csv "recommendations.csv" -NoTypeInformation
    
.EXAMPLE
    $results = .\Analyze-AzSqlDatabases.ps1 -MetricsPath "C:\Metrics" | 
        Where-Object { $_.Status -eq 'OPTIMIZE' }
#>

[CmdletBinding()]
param(
    [Parameter(Mandatory = $true)]
    [string]$MetricsPath
)

#region Configuration

# DTU Tier Limits
$DTULimits = @{
    'Basic' = @{ Sessions = 30; Workers = 30; DTU = 5 }
    'S0'    = @{ Sessions = 60; Workers = 200; DTU = 10 }
    'S1'    = @{ Sessions = 90; Workers = 200; DTU = 20 }
    'S2'    = @{ Sessions = 120; Workers = 400; DTU = 50 }
    'S3'    = @{ Sessions = 200; Workers = 400; DTU = 100 }
    'S4'    = @{ Sessions = 400; Workers = 1600; DTU = 200 }
    'S6'    = @{ Sessions = 800; Workers = 1600; DTU = 400 }
    'S7'    = @{ Sessions = 1600; Workers = 3200; DTU = 800 }
    'S9'    = @{ Sessions = 3200; Workers = 6400; DTU = 1600 }
    'S12'   = @{ Sessions = 6400; Workers = 12800; DTU = 3000 }
    'P1'    = @{ Sessions = 200; Workers = 400; DTU = 125 }
    'P2'    = @{ Sessions = 400; Workers = 800; DTU = 250 }
    'P4'    = @{ Sessions = 800; Workers = 1600; DTU = 500 }
    'P6'    = @{ Sessions = 1600; Workers = 3200; DTU = 1000 }
    'P11'   = @{ Sessions = 3200; Workers = 6400; DTU = 1750 }
    'P15'   = @{ Sessions = 6400; Workers = 12800; DTU = 4000 }
}

# vCore per-core limits
$vCoreSessionsPerCore = 30000
$vCoreWorkersPerCore = 512

# Azure Pricing (West Europe, EUR, monthly, Feb 2025 estimates)
$Pricing = @{
    # DTU tiers
    'Basic'  = 4.45
    'S0'     = 13.39
    'S1'     = 26.77
    'S2'     = 66.93
    'S3'     = 133.86
    'S4'     = 267.72
    'S6'     = 535.44
    'S7'     = 1070.88
    'S9'     = 2141.76
    'S12'    = 4283.52
    'P1'     = 401.04
    'P2'     = 802.08
    'P4'     = 1604.16
    'P6'     = 2406.24
    'P11'    = 4011.12
    'P15'    = 6416.64
    
    # vCore GP (per vCore per month)
    'GP_Gen5_vCore' = 267.0
    'GP_S_Gen5_vCore' = 66.75  # Serverless avg (50% discount when paused)
    
    # vCore BC (per vCore per month)
    'BC_Gen5_vCore' = 534.0
    
    # Elastic Pool per eDTU
    'Pool_Standard_eDTU' = 1.34
    'Pool_Premium_eDTU' = 3.21
    
    # Storage pricing (per GB per month)
    'Storage_GB_Monthly' = 0.115  # Standard storage, West Europe
}

# Scheduled pause automation cost (EUR per month)
# Assumes single Azure Automation account managing multiple databases
$AutomationCostMonthly = 10

#endregion

#region Helper Functions

function Get-Percentile {
    param([double[]]$Data, [int]$Percentile)
    if ($Data.Count -eq 0) { return 0 }
    $sorted = $Data | Sort-Object
    $index = [Math]::Ceiling($Percentile / 100 * $sorted.Count) - 1
    if ($index -lt 0) { $index = 0 }
    return $sorted[$index]
}

function Get-CoefficientOfVariation {
    param([double[]]$Data)
    if ($Data.Count -eq 0) { return 0 }
    $mean = ($Data | Measure-Object -Average).Average
    if ($mean -eq 0) { return 0 }
    $variance = ($Data | ForEach-Object { [Math]::Pow($_ - $mean, 2) } | Measure-Object -Average).Average
    $stdDev = [Math]::Sqrt($variance)
    return ($stdDev / $mean) * 100
}

function Get-LinearTrend {
    param([object[]]$TimeSeries, [string]$ValueProperty)
    if ($TimeSeries.Count -lt 2) { return 0 }
    
    $n = $TimeSeries.Count
    $x = 0..($n - 1)
    $y = $TimeSeries | ForEach-Object { $_.$ValueProperty }
    
    $sumX = ($x | Measure-Object -Sum).Sum
    $sumY = ($y | Measure-Object -Sum).Sum
    $sumXY = 0..($n - 1) | ForEach-Object { $x[$_] * $y[$_] } | Measure-Object -Sum | Select-Object -ExpandProperty Sum
    $sumX2 = ($x | ForEach-Object { $_ * $_ } | Measure-Object -Sum).Sum
    
    $slope = ($n * $sumXY - $sumX * $sumY) / ($n * $sumX2 - $sumX * $sumX)
    $meanY = $sumY / $n
    
    if ($meanY -eq 0) { return 0 }
    
    # Return trend as % change per month (assuming 30 days of data)
    return ($slope * 30 / $meanY) * 100
}

function Get-Autocorrelation {
    param([double[]]$Data, [int]$Lag = 288)  # 24 hours in 5-min intervals
    if ($Data.Count -lt ($Lag * 2)) { return 0 }
    
    $mean = ($Data | Measure-Object -Average).Average
    $variance = ($Data | ForEach-Object { [Math]::Pow($_ - $mean, 2) } | Measure-Object -Average).Average
    
    if ($variance -eq 0) { return 0 }
    
    $autocovariance = 0
    for ($i = 0; $i -lt ($Data.Count - $Lag); $i++) {
        $autocovariance += ($Data[$i] - $mean) * ($Data[$i + $Lag] - $mean)
    }
    $autocovariance /= ($Data.Count - $Lag)
    
    return $autocovariance / $variance
}

function Test-BimodalDistribution {
    param([double[]]$Data)
    if ($Data.Count -lt 20) { return $false }
    
    # Simple bimodal test: check if there are two distinct clusters
    $sorted = $Data | Sort-Object
    $q1 = Get-Percentile -Data $sorted -Percentile 25
    $q3 = Get-Percentile -Data $sorted -Percentile 75
    $median = Get-Percentile -Data $sorted -Percentile 50
    
    $iqr = $q3 - $q1
    if ($iqr -eq 0) { return $false }
    
    # Check for gap around median (Hartigan's dip test approximation)
    $midRange = $Data | Where-Object { $_ -gt ($median - $iqr * 0.5) -and $_ -lt ($median + $iqr * 0.5) }
    $midCount = $midRange.Count
    $totalCount = $Data.Count
    
    # If middle range is sparse (<20% of data), likely bimodal
    return ($midCount / $totalCount) -lt 0.2
}

function Get-WeeklyVariance {
    param([object[]]$TimeSeries, [string]$ValueProperty)
    if ($TimeSeries.Count -lt 2016) { return 0 }  # Need at least 1 week (7 days × 24 hours × 12 intervals)
    
    $weekdays = $TimeSeries | Where-Object { 
        $dow = ([DateTime]$_.Timestamp).DayOfWeek
        $dow -ne 'Saturday' -and $dow -ne 'Sunday'
    } | ForEach-Object { $_.$ValueProperty }
    
    $weekends = $TimeSeries | Where-Object { 
        $dow = ([DateTime]$_.Timestamp).DayOfWeek
        $dow -eq 'Saturday' -or $dow -eq 'Sunday'
    } | ForEach-Object { $_.$ValueProperty }
    
    if ($weekdays.Count -eq 0 -or $weekends.Count -eq 0) { return 0 }
    
    $weekdayAvg = ($weekdays | Measure-Object -Average).Average
    $weekendAvg = ($weekends | Measure-Object -Average).Average
    
    if ($weekdayAvg -eq 0 -and $weekendAvg -eq 0) { return 0 }
    
    $maxAvg = [Math]::Max($weekdayAvg, $weekendAvg)
    $minAvg = [Math]::Min($weekdayAvg, $weekendAvg)
    
    return [Math]::Abs(($maxAvg - $minAvg) / $maxAvg * 100)
}

function Get-DatabaseCost {
    param(
        [string]$Edition,
        [string]$SkuName,
        [int]$Capacity
    )
    
    # First check if SkuName is already in the pricing table (e.g., "S3", "P2")
    if ($Pricing.ContainsKey($SkuName)) {
        return $Pricing[$SkuName]
    }
    
    # Map Edition + Capacity to SKU code for DTU tiers
    $skuCode = $null
    
    if ($Edition -eq 'Basic') {
        $skuCode = 'Basic'
    }
    elseif ($Edition -eq 'Standard') {
        # Map DTU capacity to Standard tier SKU
        $skuCode = switch ($Capacity) {
            10    { 'S0' }
            20    { 'S1' }
            50    { 'S2' }
            100   { 'S3' }
            200   { 'S4' }
            400   { 'S6' }
            800   { 'S7' }
            1600  { 'S9' }
            3000  { 'S12' }
            default { $null }
        }
    }
    elseif ($Edition -eq 'Premium') {
        # Map DTU capacity to Premium tier SKU
        $skuCode = switch ($Capacity) {
            125   { 'P1' }
            250   { 'P2' }
            500   { 'P4' }
            1000  { 'P6' }
            1750  { 'P11' }
            4000  { 'P15' }
            default { $null }
        }
    }
    
    # If we found a SKU code, return its price
    if ($skuCode -and $Pricing.ContainsKey($skuCode)) {
        return $Pricing[$skuCode]
    }
    
    # vCore pricing
    if ($Edition -eq 'GeneralPurpose') {
        if ($SkuName -match 'Serverless' -or $SkuName -match '_S_') {
            return $Capacity * $Pricing['GP_S_Gen5_vCore']
        }
        return $Capacity * $Pricing['GP_Gen5_vCore']
    }
    
    if ($Edition -eq 'BusinessCritical') {
        return $Capacity * $Pricing['BC_Gen5_vCore']
    }
    
    if ($Edition -eq 'Hyperscale') {
        return $Capacity * $Pricing['GP_Gen5_vCore']  # Hyperscale pricing similar to GP
    }
    
    return 0
}

function Get-SkuCode {
    param(
        [string]$Edition,
        [string]$SkuName,
        [int]$Capacity
    )
    
    # If SkuName already looks like a SKU code (S3, P2, etc.), return it
    if ($SkuName -match '^(S\d+|P\d+|Basic)$') {
        return $SkuName
    }
    
    # Map Edition + Capacity to SKU code
    if ($Edition -eq 'Basic') {
        return 'Basic'
    }
    elseif ($Edition -eq 'Standard') {
        return $(switch ($Capacity) {
            10    { 'S0' }
            20    { 'S1' }
            50    { 'S2' }
            100   { 'S3' }
            200   { 'S4' }
            400   { 'S6' }
            800   { 'S7' }
            1600  { 'S9' }
            3000  { 'S12' }
            default { $null }
        })
    }
    elseif ($Edition -eq 'Premium') {
        return $(switch ($Capacity) {
            125   { 'P1' }
            250   { 'P2' }
            500   { 'P4' }
            1000  { 'P6' }
            1750  { 'P11' }
            4000  { 'P15' }
            default { $null }
        })
    }
    
    return $null
}

function Get-EstimatedServerlessCost {
    param(
        [double]$VCores,
        [object[]]$TimeSeries,
        [string]$ValueProperty,
        [double]$StorageGB
    )
    
    if ($TimeSeries.Count -eq 0) {
        return [PSCustomObject]@{
            ComputeCost = 0
            StorageCost = 0
            TotalCost = 0
            ActiveHoursMonthly = 0
        }
    }
    
    # Calculate active hours (slots with >5% usage)
    $activeSlots = $TimeSeries | Where-Object { $_.$ValueProperty -gt 5 }
    $activeHoursPerDay = ($activeSlots.Count / 288)  # 288 five-minute intervals per day
    $activeHoursPerMonth = $activeHoursPerDay * 30
    
    # Serverless compute cost (billed per second when active)
    # Price is per month, so divide by 730 hours to get hourly rate
    $computeCostPerHour = ($VCores * $Pricing['GP_S_Gen5_vCore']) / 730
    $computeCost = $activeHoursPerMonth * $computeCostPerHour
    
    # Storage cost (always billed, even when paused)
    $storageCost = $StorageGB * $Pricing['Storage_GB_Monthly']
    
    return [PSCustomObject]@{
        ComputeCost = [Math]::Round($computeCost, 2)
        StorageCost = [Math]::Round($storageCost, 2)
        TotalCost = [Math]::Round($computeCost + $storageCost, 2)
        ActiveHoursMonthly = [Math]::Round($activeHoursPerMonth, 1)
    }
}

function Get-WeeklyBusyWindows {
    param(
        [object[]]$TimeSeries,
        [string]$ValueProperty,
        [double]$BusyThreshold
    )
    
    if ($TimeSeries.Count -eq 0) { return @() }
    
    # Group by day-of-week + time slot
    $weeklySlots = $TimeSeries | Group-Object {
        $dt = [DateTime]$_.Timestamp
        "$($dt.DayOfWeek) $($dt.ToString('HH:mm'))"
    }
    
    # Calculate average for each weekly slot
    $slotAverages = $weeklySlots | ForEach-Object {
        $parts = $_.Name -split ' '
        [PSCustomObject]@{
            DayOfWeek = $parts[0]
            Time = $parts[1]
            Avg = ($_.Group.$ValueProperty | Measure-Object -Average).Average
        }
    } | Where-Object { $_.Avg -gt $BusyThreshold }
    
    if ($slotAverages.Count -eq 0) { return @() }
    
    # Sort by day order then time
    $dayOrder = @{Monday=1; Tuesday=2; Wednesday=3; Thursday=4; Friday=5; Saturday=6; Sunday=7}
    $slotAverages = $slotAverages | Sort-Object { 
        $dayOrder[$_.DayOfWeek] * 10000 + [int]$_.Time.Replace(':','')
    }
    
    # Group contiguous slots into windows
    $windows = @()
    $currentWindow = $null
    
    foreach ($slot in $slotAverages) {
        if (-not $currentWindow) {
            # Start new window
            $currentWindow = @{
                Day = $slot.DayOfWeek
                Start = $slot.Time
                End = $slot.Time
                AvgUsage = @($slot.Avg)
            }
        }
        elseif ($currentWindow.Day -eq $slot.DayOfWeek) {
            # Same day - check if contiguous (5 minutes apart)
            $currentEndTime = [DateTime]::ParseExact($currentWindow.End, 'HH:mm', $null)
            $slotTime = [DateTime]::ParseExact($slot.Time, 'HH:mm', $null)
            $diffMinutes = ($slotTime - $currentEndTime).TotalMinutes
            
            if ($diffMinutes -eq 5) {
                # Extend current window
                $currentWindow.End = $slot.Time
                $currentWindow.AvgUsage += $slot.Avg
            }
            else {
                # Gap detected - save current window and start new one
                $startTime = [DateTime]::ParseExact($currentWindow.Start, 'HH:mm', $null)
                $endTime = [DateTime]::ParseExact($currentWindow.End, 'HH:mm', $null)
                $durationHours = ($endTime - $startTime).TotalHours + (5.0/60)  # Add final 5-min slot
                
                $windows += [PSCustomObject]@{
                    Period = "$($currentWindow.Day) $($currentWindow.Start)-$($currentWindow.End)"
                    AvgUsage = [Math]::Round(($currentWindow.AvgUsage | Measure-Object -Average).Average, 2)
                    DurationHours = [Math]::Round($durationHours, 2)
                }
                
                $currentWindow = @{
                    Day = $slot.DayOfWeek
                    Start = $slot.Time
                    End = $slot.Time
                    AvgUsage = @($slot.Avg)
                }
            }
        }
        else {
            # Different day - save current window and start new one
            $startTime = [DateTime]::ParseExact($currentWindow.Start, 'HH:mm', $null)
            $endTime = [DateTime]::ParseExact($currentWindow.End, 'HH:mm', $null)
            $durationHours = ($endTime - $startTime).TotalHours + (5.0/60)
            
            $windows += [PSCustomObject]@{
                Period = "$($currentWindow.Day) $($currentWindow.Start)-$($currentWindow.End)"
                AvgUsage = [Math]::Round(($currentWindow.AvgUsage | Measure-Object -Average).Average, 2)
                DurationHours = [Math]::Round($durationHours, 2)
            }
            
            $currentWindow = @{
                Day = $slot.DayOfWeek
                Start = $slot.Time
                End = $slot.Time
                AvgUsage = @($slot.Avg)
            }
        }
    }
    
    # Add last window
    if ($currentWindow) {
        $startTime = [DateTime]::ParseExact($currentWindow.Start, 'HH:mm', $null)
        $endTime = [DateTime]::ParseExact($currentWindow.End, 'HH:mm', $null)
        $durationHours = ($endTime - $startTime).TotalHours + (5.0/60)
        
        $windows += [PSCustomObject]@{
            Period = "$($currentWindow.Day) $($currentWindow.Start)-$($currentWindow.End)"
            AvgUsage = [Math]::Round(($currentWindow.AvgUsage | Measure-Object -Average).Average, 2)
            DurationHours = [Math]::Round($durationHours, 2)
        }
    }
    
    return $windows
}

function Get-PredictabilityScore {
    param(
        [object[]]$TimeSeries,
        [string]$ValueProperty,
        [double]$BusyThreshold
    )
    
    if ($TimeSeries.Count -lt 288) { return 0 }  # Need at least 1 day
    
    # Calculate busy hours per day
    $dailyBusyHours = $TimeSeries | 
        Group-Object { ([DateTime]$_.Timestamp).Date } | 
        ForEach-Object {
            $busySlots = $_.Group | Where-Object { $_.$ValueProperty -gt $BusyThreshold }
            $busySlots.Count / 12  # 12 five-minute intervals per hour
        }
    
    if ($dailyBusyHours.Count -eq 0) { return 0 }
    
    # Calculate coefficient of variation
    $stats = $dailyBusyHours | Measure-Object -Average -StandardDeviation
    
    if ($stats.Average -eq 0) { return 0 }
    
    $cv = ($stats.StandardDeviation / $stats.Average) * 100
    
    # Score: 100 = perfectly predictable (CV=0), 0 = chaotic (CV>=100)
    # CV < 20 = excellent (score 80-100)
    # CV 20-50 = good (score 50-80)
    # CV 50-100 = fair (score 0-50)
    # CV > 100 = poor (score 0)
    
    $score = [Math]::Max(0, [Math]::Min(100, 100 - $cv))
    
    return [Math]::Round($score, 0)
}

function Get-ScheduledPauseCost {
    param(
        [double]$CurrentCost,
        [object[]]$BusyWindows,
        [double]$BufferHours = 0.5
    )
    
    if ($BusyWindows.Count -eq 0) {
        return [PSCustomObject]@{
            RuntimeCost = 0
            AutomationCost = 0
            TotalCost = 0
            ActiveHoursWeekly = 0
            ActiveFraction = 0
        }
    }
    
    # Total busy hours per week
    $busyHoursPerWeek = ($BusyWindows.DurationHours | Measure-Object -Sum).Sum
    
    # Add buffer for startup/shutdown (30 min each side per window)
    $totalActiveHoursPerWeek = $busyHoursPerWeek + ($BusyWindows.Count * $BufferHours * 2)
    
    # Calculate runtime cost
    $activeFraction = $totalActiveHoursPerWeek / 168  # 168 hours per week
    $runtimeCost = $CurrentCost * $activeFraction
    
    # Azure Automation cost
    $automationCost = $AutomationCostMonthly
    
    return [PSCustomObject]@{
        RuntimeCost = [Math]::Round($runtimeCost, 2)
        AutomationCost = $automationCost
        TotalCost = [Math]::Round($runtimeCost + $automationCost, 2)
        ActiveHoursWeekly = [Math]::Round($totalActiveHoursPerWeek, 1)
        ActiveFraction = [Math]::Round($activeFraction * 100, 1)
    }
}

#endregion

#region Data Import

Write-Verbose "Importing metric files from $MetricsPath"

# Import SKU data
$skuFile = Join-Path $MetricsPath "AzSQLDb_SKU.csv"
if (-not (Test-Path $skuFile)) {
    throw "SKU file not found: $skuFile"
}
$databases = Import-Csv $skuFile -Delimiter "`t"

# Import metrics
$metricFiles = @{
    DTU         = "azsqldb_metric_dtu_consumption.csv"
    CPU         = "azsqldb_metric_cpu.csv"
    Workers     = "azsqldb_metric_workers.csv"
    Sessions    = "azsqldb_metric_sessions.csv"
    Storage     = "azsqldb_metric_storage.csv"
    Connections = "azsqldb_metric_connection_successful.csv"
    LogWrite    = "azsqldb_metric_log_write.csv"
}

$metrics = @{}
foreach ($metricName in $metricFiles.Keys) {
    $file = Join-Path $MetricsPath $metricFiles[$metricName]
    if (Test-Path $file) {
        Write-Verbose "Importing $metricName metrics"
        $metrics[$metricName] = Import-Csv $file -Delimiter "`t"
    } else {
        Write-Warning "Metric file not found: $file"
        $metrics[$metricName] = @()
    }
}

#endregion

#region Main Analysis Loop

$results = foreach ($db in $databases) {
    Write-Verbose "Analyzing $($db.ServerName)/$($db.DatabaseName)"
    
    # Detect database type early
    $isDTU = $db.Edition -in @('Basic', 'Standard', 'Premium')
    $isInPool = -not [string]::IsNullOrEmpty($db.ElasticPoolName)
    $isServerless = $db.SkuName -match '_S_' -or $db.Edition -match 'Serverless'
    $isHyperscale = $db.Edition -eq 'Hyperscale'
    
    # Get actual SKU code for DTU databases (e.g., "S3" from Edition="Standard", Capacity=100)
    $skuCode = if ($isDTU) { 
        Get-SkuCode -Edition $db.Edition -SkuName $db.SkuName -Capacity $db.Capacity 
    } else { 
        $db.SkuName 
    }
    
    # Initialize result object
    $result = [PSCustomObject]@{
        # Identity
        ServerName              = $db.ServerName
        DatabaseName            = $db.DatabaseName
        
        # Current State
        CurrentEdition          = $db.Edition
        CurrentSkuName          = if ($isDTU) { $skuCode } else { $db.SkuName }
        CurrentCapacity         = $db.Capacity
        CurrentModel            = if ($isDTU) { 'DTU' } else { 'vCore' }
        CurrentMaxSizeGB        = if ($db.MaxSizeBytes) { [Math]::Round($db.MaxSizeBytes / 1GB, 2) } else { $db.MaxSizeGB }
        ElasticPoolName         = $db.ElasticPoolName
        IsInPool                = $isInPool
        IsServerless            = $isServerless
        IsHyperscale            = $isHyperscale
        
        # Recommendation
        Status                  = 'ANALYZING'
        Classification          = 'UNKNOWN'
        Recommendation          = 'Analyzing...'
        RecommendedTier         = ''
        RecommendedCapacity     = 0
        Confidence              = 'Medium'
        Priority                = 'Medium'
        Impact                  = 'Medium'
        NextAction              = ''
        Flags                   = ''
        
        # Statistics - Usage
        DTU_Avg                 = $null
        DTU_P95                 = $null
        DTU_Max                 = $null
        CPU_Avg                 = $null
        CPU_P95                 = $null
        CPU_Max                 = $null
        CV_DTU                  = $null
        CV_CPU                  = $null
        Idle_Percent            = $null
        
        # Statistics - Resources
        Sessions_Peak_Actual    = $null
        Sessions_Peak_Percent   = $null
        Workers_Peak_Actual     = $null
        Workers_Peak_Percent    = $null
        Storage_Used_GB         = $null
        Storage_Percent         = $null
        LogWrite_P95            = $null
        
        # Statistics - Patterns
        Connections_Per_Hour_Avg = $null
        Pattern_Autocorrelation = $null
        Weekly_Variance_Percent = $null
        Growth_DTU_Trend        = $null
        Growth_CPU_Trend        = $null
        Growth_Storage_MB_Per_Month = $null
        Days_Of_Data            = 0
        
        # Busy Period Detection
        Busy_Windows            = $null
        Busy_Hours_Per_Week     = $null
        Active_Hours_Per_Day_Avg = $null
        Pattern_Predictability_Score = $null
        Scheduled_Pause_Feasibility = $null
        
        # Decisions
        Serverless_Viable       = $false
        ElasticPool_Candidate   = $false
        
        # Costs
        Current_Cost_EUR_Monthly = 0
        Recommended_Cost_EUR_Monthly = 0
        Savings_EUR_Monthly     = 0
        Savings_Percent         = 0
        Alternative_Recommendation = $null
        Alternative_Cost_EUR_Monthly = $null
        
        # Serverless Cost Breakdown
        Serverless_Active_Hours_Monthly = $null
        Serverless_Compute_Cost = $null
        Serverless_Storage_Cost = $null
        Serverless_Total_Cost   = $null
        
        # Scheduled Pause Cost Breakdown
        Scheduled_Pause_Runtime_Cost = $null
        Scheduled_Pause_Automation_Cost = $null
        Scheduled_Pause_Active_Hours_Weekly = $null
        Scheduled_Pause_Total_Cost = $null
    }
    
    # Get metrics for this database
    $dbKey = "$($db.ServerName)|$($db.DatabaseName)"
    
    # Special handling for databases in elastic pools
    if ($isInPool) {
        # Pooled databases don't have individual DTU limits
        # They share the pool's eDTU and should be analyzed differently
        
        # We can still get CPU, sessions, workers, storage metrics
        $computeData = $metrics.CPU | Where-Object { 
            "$($_.ServerName)|$($_.DBName)" -eq $dbKey 
        }
        
        $sessionsData = $metrics.Sessions | Where-Object { 
            "$($_.ServerName)|$($_.DBName)" -eq $dbKey 
        }
        
        $workersData = $metrics.Workers | Where-Object { 
            "$($_.ServerName)|$($_.DBName)" -eq $dbKey 
        }
        
        $storageData = $metrics.Storage | Where-Object { 
            "$($_.ServerName)|$($_.DBName)" -eq $dbKey 
        }
        
        $connectionsData = $metrics.Connections | Where-Object { 
            "$($_.ServerName)|$($_.DBName)" -eq $dbKey 
        }
        
        $logWriteData = $metrics.LogWrite | Where-Object { 
            "$($_.ServerName)|$($_.DBName)" -eq $dbKey 
        }
        
        # Check data sufficiency
        if ($computeData.Count -eq 0) {
            # No data at all - likely permission issue
            $result.Status = 'REVIEW'
            $result.Classification = 'NO_DATA'
            $result.Recommendation = "No metric data available (check permissions)"
            $result.NextAction = 'Verify metric collection permissions'
            $result.Confidence = 'Low'
            $result.Priority = 'Low'
            $result.Days_Of_Data = 0
            $result.Flags = 'No metric data collected - permission issue or collection failure'
            # Leave all metric values as 0 (default) - they're unknown, not actually zero
            $result
            continue
        }
        
        if ($computeData.Count -lt 2016) {  # Less than 7 days (5-min intervals)
            $result.Status = 'OK'
            $result.Classification = 'IN_POOL'
            $result.Recommendation = "OK (in elastic pool '$($db.ElasticPoolName)', insufficient data)"
            $result.NextAction = 'Monitor pool performance'
            $result.Confidence = 'Low'
            $result.Priority = 'Low'
            $result.Days_Of_Data = [Math]::Round($computeData.Count / 288, 1)  # 288 intervals per day
            $result.Flags = 'Part of elastic pool - individual optimization not applicable'
            $result
            continue
        }
        
        $result.Days_Of_Data = [Math]::Round($computeData.Count / 288, 1)  # 288 intervals per day
        
        # Calculate basic statistics for pooled database
        $cpuValues = $computeData.Nominal_Value
        $result.CPU_Avg = [Math]::Round(($cpuValues | Measure-Object -Average).Average, 2)
        $result.CPU_P95 = [Math]::Round((Get-Percentile -Data $cpuValues -Percentile 95), 2)
        $result.CPU_Max = [Math]::Round(($cpuValues | Measure-Object -Maximum).Maximum, 2)
        
        if ($sessionsData.Count -gt 0) {
            $result.Sessions_Peak_Percent = [Math]::Round(($sessionsData.Metric_Value | Measure-Object -Maximum).Maximum, 2)
            $result.Sessions_Peak_Actual = [Math]::Round(($sessionsData.Nominal_Value | Measure-Object -Maximum).Maximum, 0)
        }
        
        if ($workersData.Count -gt 0) {
            $result.Workers_Peak_Percent = [Math]::Round(($workersData.Metric_Value | Measure-Object -Maximum).Maximum, 2)
            $result.Workers_Peak_Actual = [Math]::Round(($workersData.Nominal_Value | Measure-Object -Maximum).Maximum, 0)
        }
        
        if ($storageData.Count -gt 0) {
            $result.Storage_Percent = [Math]::Round(($storageData.Metric_Value | Measure-Object -Average).Average, 2)
            $result.Storage_Used_GB = [Math]::Round(($result.Storage_Percent / 100) * $result.CurrentMaxSizeGB, 2)
        }
        
        # Check if hitting pool-level constraints
        if ($result.Sessions_Peak_Percent -gt 80 -or $result.Workers_Peak_Percent -gt 80) {
            $result.Status = 'REVIEW'
            $result.Classification = 'IN_POOL_CONSTRAINED'
            $result.Recommendation = "Review pool capacity (database hitting pool limits)"
            $result.Priority = 'High'
            $result.NextAction = "Check elastic pool '$($db.ElasticPoolName)' sizing"
            $result.Flags = if ($result.Sessions_Peak_Percent -gt 80) { 'Sessions constrained' } else { 'Workers constrained' }
        } else {
            $result.Status = 'OK'
            $result.Classification = 'IN_POOL'
            $result.Recommendation = "OK (in elastic pool '$($db.ElasticPoolName)')"
            $result.NextAction = 'Monitor pool performance'
            $result.Flags = 'Part of elastic pool - individual optimization not applicable'
        }
        
        $result
        continue
    }
    
    # For non-pooled databases, determine if DTU or vCore
    
    # Get time-series data
    if ($isDTU) {
        $computeData = $metrics.DTU | Where-Object { 
            "$($_.ServerName)|$($_.DBName)" -eq $dbKey 
        }
        
        # Fallback to CPU if DTU metrics missing
        if ($computeData.Count -eq 0) {
            Write-Warning "DTU metrics missing for $dbKey, using CPU metrics as fallback"
            $computeData = $metrics.CPU | Where-Object { 
                "$($_.ServerName)|$($_.DBName)" -eq $dbKey 
            }
            $isDTU = $false  # Treat as vCore for analysis
        }
    } else {
        $computeData = $metrics.CPU | Where-Object { 
            "$($_.ServerName)|$($_.DBName)" -eq $dbKey 
        }
    }
    
    $sessionsData = $metrics.Sessions | Where-Object { 
        "$($_.ServerName)|$($_.DBName)" -eq $dbKey 
    }
    
    $workersData = $metrics.Workers | Where-Object { 
        "$($_.ServerName)|$($_.DBName)" -eq $dbKey 
    }
    
    $storageData = $metrics.Storage | Where-Object { 
        "$($_.ServerName)|$($_.DBName)" -eq $dbKey 
    }
    
    $connectionsData = $metrics.Connections | Where-Object { 
        "$($_.ServerName)|$($_.DBName)" -eq $dbKey 
    }
    
    $logWriteData = $metrics.LogWrite | Where-Object { 
        "$($_.ServerName)|$($_.DBName)" -eq $dbKey 
    }
    
    # Check data sufficiency (Tier 2)
    if ($computeData.Count -eq 0) {
        # No data at all - likely permission issue
        $result.Status = 'REVIEW'
        $result.Classification = 'NO_DATA'
        $result.Recommendation = 'No metric data available (check permissions)'
        $result.NextAction = 'Verify metric collection permissions'
        $result.Confidence = 'Low'
        $result.Priority = 'Low'
        $result.Days_Of_Data = 0
        $result.Flags = 'No metric data collected - permission issue or collection failure'
        $result.Current_Cost_EUR_Monthly = Get-DatabaseCost -Edition $db.Edition -SkuName $db.SkuName -Capacity $db.Capacity
        # Leave all metrics as 0 - they're unknown, not actually zero
        $result
        continue
    }
    
    if ($computeData.Count -lt 2016) {  # Less than 7 days (5-min intervals: 7×24×12)
        $result.Status = 'REVIEW'
        $result.Classification = 'INSUFFICIENT_DATA'
        $result.Recommendation = 'OK (insufficient data for analysis)'
        $result.NextAction = 'Re-analyze after day 30'
        $result.Confidence = 'Low'
        $result.Priority = 'Low'
        $result.Days_Of_Data = [Math]::Round($computeData.Count / 288, 1)  # 288 intervals per day
        $result.Current_Cost_EUR_Monthly = Get-DatabaseCost -Edition $db.Edition -SkuName $db.SkuName -Capacity $db.Capacity
        $result
        continue
    }
    
    $result.Days_Of_Data = [Math]::Round($computeData.Count / 288, 1)  # 288 intervals per day
    
    # Calculate statistics
    if ($isDTU) {
        $dtuValues = $computeData.Nominal_Value
        $result.DTU_Avg = [Math]::Round(($dtuValues | Measure-Object -Average).Average, 2)
        $result.DTU_P95 = [Math]::Round((Get-Percentile -Data $dtuValues -Percentile 95), 2)
        $result.DTU_Max = [Math]::Round(($dtuValues | Measure-Object -Maximum).Maximum, 2)
        $result.CV_DTU = [Math]::Round((Get-CoefficientOfVariation -Data $dtuValues), 2)
        
        $idleCount = ($computeData | Where-Object { $_.Metric_Value -lt 5 }).Count
        $result.Idle_Percent = [Math]::Round(($idleCount / $computeData.Count * 100), 2)
        
        $result.Growth_DTU_Trend = [Math]::Round((Get-LinearTrend -TimeSeries $computeData -ValueProperty 'Nominal_Value'), 2)
        $result.Pattern_Autocorrelation = [Math]::Round((Get-Autocorrelation -Data $dtuValues -Lag 288), 2)  # 24 hours in 5-min intervals
        $result.Weekly_Variance_Percent = [Math]::Round((Get-WeeklyVariance -TimeSeries $computeData -ValueProperty 'Nominal_Value'), 2)
        
        $computeMetricName = 'DTU'
        $computeP95 = $result.DTU_P95
    } else {
        $cpuValues = $computeData.Nominal_Value
        $result.CPU_Avg = [Math]::Round(($cpuValues | Measure-Object -Average).Average, 2)
        $result.CPU_P95 = [Math]::Round((Get-Percentile -Data $cpuValues -Percentile 95), 2)
        $result.CPU_Max = [Math]::Round(($cpuValues | Measure-Object -Maximum).Maximum, 2)
        $result.CV_CPU = [Math]::Round((Get-CoefficientOfVariation -Data $cpuValues), 2)
        
        $idleCount = ($computeData | Where-Object { $_.Metric_Value -lt 5 }).Count
        $result.Idle_Percent = [Math]::Round(($idleCount / $computeData.Count * 100), 2)
        
        $result.Growth_CPU_Trend = [Math]::Round((Get-LinearTrend -TimeSeries $computeData -ValueProperty 'Nominal_Value'), 2)
        $result.Pattern_Autocorrelation = [Math]::Round((Get-Autocorrelation -Data $cpuValues -Lag 288), 2)  # 24 hours in 5-min intervals
        $result.Weekly_Variance_Percent = [Math]::Round((Get-WeeklyVariance -TimeSeries $computeData -ValueProperty 'Nominal_Value'), 2)
        
        $computeMetricName = 'CPU'
        $computeP95 = $result.CPU_P95
    }
    
    # Sessions/Workers statistics
    if ($sessionsData.Count -gt 0) {
        $result.Sessions_Peak_Percent = [Math]::Round(($sessionsData.Metric_Value | Measure-Object -Maximum).Maximum, 2)
        $result.Sessions_Peak_Actual = [Math]::Round(($sessionsData.Nominal_Value | Measure-Object -Maximum).Maximum, 0)
    }
    
    if ($workersData.Count -gt 0) {
        $result.Workers_Peak_Percent = [Math]::Round(($workersData.Metric_Value | Measure-Object -Maximum).Maximum, 2)
        $result.Workers_Peak_Actual = [Math]::Round(($workersData.Nominal_Value | Measure-Object -Maximum).Maximum, 0)
    }
    
    # Storage statistics
    if ($storageData.Count -gt 0) {
        $result.Storage_Percent = [Math]::Round(($storageData.Metric_Value | Measure-Object -Average).Average, 2)
        $result.Storage_Used_GB = [Math]::Round(($result.Storage_Percent / 100) * $result.CurrentMaxSizeGB, 2)
        
        # Storage growth
        $storageGrowth = Get-LinearTrend -TimeSeries $storageData -ValueProperty 'Metric_Value'
        $result.Growth_Storage_MB_Per_Month = [Math]::Round(($storageGrowth / 100 * $result.CurrentMaxSizeGB * 1024), 0)
    }
    
    # Connections statistics
    if ($connectionsData.Count -gt 0) {
        $totalConnections = ($connectionsData.Metric_Value | Measure-Object -Sum).Sum
        $totalHours = $connectionsData.Count
        $result.Connections_Per_Hour_Avg = [Math]::Round(($totalConnections / $totalHours), 2)
    }
    
    # Log write statistics
    if ($logWriteData.Count -gt 0) {
        $result.LogWrite_P95 = [Math]::Round((Get-Percentile -Data $logWriteData.Metric_Value -Percentile 95), 2)
    }
    
    # Current cost
    if ($isServerless) {
        # For serverless, calculate estimated cost with active hours
        $serverlessCostCalc = Get-EstimatedServerlessCost -VCores $db.Capacity -TimeSeries $computeData -ValueProperty 'Metric_Value' -StorageGB $result.Storage_Used_GB
        $result.Current_Cost_EUR_Monthly = $serverlessCostCalc.TotalCost
    } else {
        $result.Current_Cost_EUR_Monthly = Get-DatabaseCost -Edition $db.Edition -SkuName $db.SkuName -Capacity $db.Capacity
    }
    
    # Detect busy windows and calculate predictability
    $busyThreshold = $computeP95 * 0.10
    $busyWindows = Get-WeeklyBusyWindows -TimeSeries $computeData -ValueProperty 'Nominal_Value' -BusyThreshold $busyThreshold
    
    if ($busyWindows.Count -gt 0) {
        # Format busy windows
        $result.Busy_Windows = ($busyWindows.Period -join ', ')
        $result.Busy_Hours_Per_Week = [Math]::Round(($busyWindows.DurationHours | Measure-Object -Sum).Sum, 1)
        $result.Active_Hours_Per_Day_Avg = [Math]::Round($result.Busy_Hours_Per_Week / 7, 1)
        
        # Calculate predictability score
        $result.Pattern_Predictability_Score = Get-PredictabilityScore -TimeSeries $computeData -ValueProperty 'Nominal_Value' -BusyThreshold $busyThreshold
        $result.Scheduled_Pause_Feasibility = $result.Pattern_Predictability_Score
    }
    
    #region Decision Tree
    
    # TIER 1: Constraint Triage
    $constraintViolation = $false
    $constraintReason = ''
    
    if ($result.Sessions_Peak_Percent -gt 80) {
        $constraintViolation = $true
        $constraintReason = 'Sessions constraint'
    }
    
    if ($result.Workers_Peak_Percent -gt 80) {
        $constraintViolation = $true
        $constraintReason = if ($constraintReason) { "$constraintReason + Workers constraint" } else { 'Workers constraint' }
    }
    
    if ($result.Storage_Percent -gt 90) {
        $constraintViolation = $true
        $constraintReason = if ($constraintReason) { "$constraintReason + Storage constraint" } else { 'Storage constraint' }
    }
    
    if ($constraintViolation) {
        $result.Status = 'UPGRADE'
        $result.Classification = 'CONSTRAINED'
        $result.Recommendation = "Upgrade required: $constraintReason"
        $result.Priority = 'High'
        $result.Confidence = 'High'
        $result.Flags = $constraintReason
        
        # Suggest next tier
        if ($isDTU) {
            $tiers = @('S0', 'S1', 'S2', 'S3', 'S4', 'S6', 'S7', 'S9', 'S12', 'P1', 'P2', 'P4', 'P6', 'P11', 'P15')
            $currentIndex = $tiers.IndexOf($db.SkuName)
            if ($currentIndex -ge 0 -and $currentIndex -lt ($tiers.Count - 1)) {
                $result.RecommendedTier = $tiers[$currentIndex + 1]
                $result.NextAction = "Upgrade to $($result.RecommendedTier)"
            } else {
                $result.NextAction = "Review tier upgrade options"
            }
        } else {
            $result.RecommendedTier = "Add vCores or switch to BusinessCritical"
            $result.NextAction = "Review vCore scaling options"
        }
        
        $result
        continue
    }
    
    # TIER 2: Growth/Decline Detection
    $growthTrend = if ($isDTU) { $result.Growth_DTU_Trend } else { $result.Growth_CPU_Trend }
    
    # Check storage growth (skip for Hyperscale - it auto-scales)
    if (-not $isHyperscale -and $result.Growth_Storage_MB_Per_Month -gt 0) {
        $monthsUntilFull = ($result.CurrentMaxSizeGB * 1024 - $result.Storage_Used_GB * 1024) / $result.Growth_Storage_MB_Per_Month
        if ($monthsUntilFull -lt 6 -and $monthsUntilFull -gt 0) {
            $result.Status = 'UPGRADE'
            $result.Classification = 'GROWING'
            $result.Recommendation = "Storage will max out in $([Math]::Round($monthsUntilFull, 1)) months"
            $result.Priority = if ($monthsUntilFull -lt 1) { 'Immediate' } else { 'High' }
            $result.RecommendedTier = if ($result.CurrentMaxSizeGB -gt 1000) { 'Hyperscale' } else { 'Increase max size or upgrade tier' }
            $result.NextAction = "Plan storage expansion"
            $result.Confidence = 'High'
            $result
            continue
        }
    }
    
    # Check compute growth
    if ($growthTrend -gt 20) {
        $result.Status = 'UPGRADE'
        $result.Classification = 'GROWING'
        $result.Recommendation = "$computeMetricName growing at $([Math]::Round($growthTrend, 1))% per month"
        $result.Priority = 'High'
        $result.NextAction = "Monitor and plan capacity increase"
        $result.Confidence = 'Medium'
        $result
        continue
    }
    
    # Check decline
    if ($growthTrend -lt -20) {
        $result.Status = 'REVIEW'
        $result.Classification = 'DECLINING'
        $result.Flags = "Usage declining at $([Math]::Round([Math]::Abs($growthTrend), 1))% per month"
        
        if ($growthTrend -lt -50) {
            $result.Recommendation = "URGENT: Decommission candidate"
            $result.Priority = 'Immediate'
            $result.NextAction = "Business review for decommissioning"
        } else {
            $result.Recommendation = "FLAG: Decommission candidate or aggressive downgrade"
            $result.Priority = 'Medium'
            $result.NextAction = "Business review or downgrade with monthly monitoring"
        }
        
        $result.Confidence = 'High'
        $result
        continue
    }
    
    # TIER 3: Workload Classification
    $cv = if ($isDTU) { $result.CV_DTU } else { $result.CV_CPU }
    $idle = $result.Idle_Percent
    $autocorr = $result.Pattern_Autocorrelation
    $weeklyVar = $result.Weekly_Variance_Percent
    
    # Check bimodal (batch-heavy)
    $computeValues = if ($isDTU) { $computeData.Nominal_Value } else { $computeData.Nominal_Value }
    $isBimodal = Test-BimodalDistribution -Data $computeValues
    
    if ($isBimodal) {
        $result.Classification = 'BATCH_HEAVY'
    } elseif ($weeklyVar -gt 50) {
        $result.Classification = 'WEEKEND_WEEKDAY'
    } elseif ($cv -lt 50 -and $idle -gt 70) {
        $result.Classification = 'SPARSE'
    } elseif ($cv -lt 50 -and $idle -lt 70) {
        $result.Classification = 'STEADY'
    } elseif ($cv -ge 50 -and $cv -lt 100 -and $autocorr -gt 0.7) {
        $result.Classification = 'PERIODIC'
    } elseif ($cv -ge 100 -and $idle -gt 50) {
        $result.Classification = 'BURSTY'
    } elseif ($cv -ge 50 -and $idle -lt 30) {
        $result.Classification = 'CHAOTIC'
    } elseif ($cv -ge 50 -and $idle -ge 30 -and $idle -le 50) {
        if ($autocorr -gt 0.4) {
            $result.Classification = 'PERIODIC'
        } else {
            $result.Classification = 'CHAOTIC'
        }
    } else {
        $result.Classification = 'UNCLASSIFIED'
    }
    
    # TIER 4: Optimization Paths
    
    # Special check: Should serverless move to provisioned?
    $serverlessShouldMoveToProvisioned = $false
    $serverlessMoveReason = ''
    $serverlessMoveImpact = 'Medium'
    
    if ($isServerless) {
        # Cost comparison: serverless vs provisioned
        $estimatedServerlessCost = Get-EstimatedServerlessCost -VCores $db.Capacity -IdlePercent $result.Idle_Percent
        $provisionedCost = $db.Capacity * $Pricing['GP_Gen5_vCore']
        
        # Reasons to move to provisioned:
        
        # 1. STEADY workload - never pauses
        if ($result.Classification -eq 'STEADY') {
            $serverlessShouldMoveToProvisioned = $true
            $serverlessMoveReason = 'Steady workload - auto-pause not utilized'
            $costSavings = $estimatedServerlessCost - $provisionedCost
            
            if ($costSavings -gt 50) {
                $serverlessMoveImpact = 'High'
            } elseif ($costSavings -gt 20) {
                $serverlessMoveImpact = 'Medium'
            } else {
                $serverlessMoveImpact = 'Low'
            }
        }
        
        # 2. High connection frequency prevents auto-pause
        if ($result.Connections_Per_Hour_Avg -gt 12 -and $result.Idle_Percent -lt 30) {
            $serverlessShouldMoveToProvisioned = $true
            $serverlessMoveReason = if ($serverlessMoveReason) { 
                "$serverlessMoveReason + Constant connections prevent auto-pause" 
            } else { 
                'Constant connections prevent auto-pause' 
            }
            $costSavings = $estimatedServerlessCost - $provisionedCost
            
            if ($costSavings -gt 50) {
                $serverlessMoveImpact = 'High'
            } elseif ($costSavings -gt 20) {
                $serverlessMoveImpact = 'Medium'
            } else {
                $serverlessMoveImpact = 'Low'
            }
        }
        
        # 3. Write-heavy workload (serverless has lower log limits)
        if ($result.LogWrite_P95 -gt 60) {
            $serverlessShouldMoveToProvisioned = $true
            $serverlessMoveReason = if ($serverlessMoveReason) {
                "$serverlessMoveReason + Write-heavy (serverless log limits)"
            } else {
                'Write-heavy workload (serverless has lower log throughput)'
            }
            # Performance impact rather than cost
            $serverlessMoveImpact = 'High'
        }
        
        # 4. Cost analysis - if auto-pause is ineffective
        if (-not $serverlessShouldMoveToProvisioned -and $estimatedServerlessCost -gt $provisionedCost * 1.1) {
            $serverlessShouldMoveToProvisioned = $true
            $serverlessMoveReason = 'Serverless cost higher than provisioned (low auto-pause effectiveness)'
            $costSavings = $estimatedServerlessCost - $provisionedCost
            
            if ($costSavings -gt 50) {
                $serverlessMoveImpact = 'High'
            } elseif ($costSavings -gt 20) {
                $serverlessMoveImpact = 'Medium'
            } else {
                $serverlessMoveImpact = 'Low'
            }
        }
    }
    
    # If serverless should move to provisioned, handle it before normal classification logic
    if ($serverlessShouldMoveToProvisioned) {
        $result.Status = 'OPTIMIZE'
        $result.Serverless_Viable = $false
        $result.Recommendation = "Migrate from Serverless to provisioned GP"
        $result.RecommendedTier = "GP_Gen5_$($db.Capacity)"
        $result.RecommendedCapacity = $db.Capacity
        $result.Recommended_Cost_EUR_Monthly = $db.Capacity * $Pricing['GP_Gen5_vCore']
        $result.Savings_EUR_Monthly = [Math]::Round($result.Current_Cost_EUR_Monthly - $result.Recommended_Cost_EUR_Monthly, 2)
        
        if ($result.Current_Cost_EUR_Monthly -gt 0) {
            $result.Savings_Percent = [Math]::Round(($result.Savings_EUR_Monthly / $result.Current_Cost_EUR_Monthly * 100), 2)
        }
        
        $result.Impact = $serverlessMoveImpact
        $result.Flags = $serverlessMoveReason
        
        if ($serverlessMoveImpact -eq 'High') {
            $result.Priority = 'High'
            $result.Confidence = 'High'
        } elseif ($serverlessMoveImpact -eq 'Medium') {
            $result.Priority = 'Medium'
            $result.Confidence = 'High'
        } else {
            $result.Priority = 'Low'
            $result.Confidence = 'Medium'
        }
        
        $result.NextAction = "Migrate to provisioned vCore"
        
        # Continue to Tier 5 for final validation
    } else {
        # Normal classification-based optimization paths
    
    switch ($result.Classification) {
        'BURSTY' {
            # Tier 4A: Serverless viability
            
            # If already serverless, optimize min/max vCore
            if ($isServerless) {
                $result.Serverless_Viable = $true
                $result.Status = 'OPTIMIZE'
                
                # Parse current capacity
                $currentVCores = $db.Capacity
                
                # Calculate optimal vCore range
                $optimalMinVCore = [Math]::Max(0.5, [Math]::Ceiling($result.CPU_P95 / 100 * 0.8))
                $optimalMaxVCore = [Math]::Max($optimalMinVCore, [Math]::Ceiling($result.CPU_Max / 100 * 1.2))
                
                if ($optimalMaxVCore -lt $currentVCores) {
                    $result.Recommendation = "Optimize serverless vCore range"
                    $result.RecommendedTier = "GP_S_Gen5_$optimalMinVCore-$optimalMaxVCore"
                    $result.RecommendedCapacity = $optimalMinVCore
                    
                    $serverlessCostCalc = Get-EstimatedServerlessCost -VCores $optimalMinVCore -TimeSeries $computeData -ValueProperty 'Metric_Value' -StorageGB $result.Storage_Used_GB
                    $result.Recommended_Cost_EUR_Monthly = $serverlessCostCalc.TotalCost
                    $result.Serverless_Active_Hours_Monthly = $serverlessCostCalc.ActiveHoursMonthly
                    $result.Serverless_Compute_Cost = $serverlessCostCalc.ComputeCost
                    $result.Serverless_Storage_Cost = $serverlessCostCalc.StorageCost
                    $result.Serverless_Total_Cost = $serverlessCostCalc.TotalCost
                    
                    $result.Priority = 'Medium'
                    $result.NextAction = "Adjust serverless min/max vCore settings"
                } else {
                    $result.Status = 'OK'
                    $result.Recommendation = "OK (serverless settings appropriate)"
                    $result.Priority = 'Low'
                }
            } else {
                # Not serverless yet - check if viable
                $serverlessDisqualifiers = @()
                
                if ($result.Connections_Per_Hour_Avg -gt 12) {  # >1 per 5 min
                    $serverlessDisqualifiers += 'High connection frequency'
                }
                
                if ($result.LogWrite_P95 -gt 40) {
                    $serverlessDisqualifiers += 'Write-heavy workload'
                }
                
                if ($serverlessDisqualifiers.Count -eq 0) {
                    $result.Serverless_Viable = $true
                    $result.Status = 'OPTIMIZE'
                    
                    # Calculate vCore size
                    $minVCore = [Math]::Max(0.5, [Math]::Ceiling($result.CPU_P95 / 100))
                    $maxVCore = [Math]::Max($minVCore, [Math]::Ceiling($result.CPU_Max / 100 * 1.2))
                    
                    # Calculate serverless cost breakdown
                    $serverlessCostCalc = Get-EstimatedServerlessCost -VCores $minVCore -TimeSeries $computeData -ValueProperty 'Metric_Value' -StorageGB $result.Storage_Used_GB
                    $result.Serverless_Active_Hours_Monthly = $serverlessCostCalc.ActiveHoursMonthly
                    $result.Serverless_Compute_Cost = $serverlessCostCalc.ComputeCost
                    $result.Serverless_Storage_Cost = $serverlessCostCalc.StorageCost
                    $result.Serverless_Total_Cost = $serverlessCostCalc.TotalCost
                    
                    # Calculate scheduled pause cost if pattern is predictable
                    if ($busyWindows.Count -gt 0) {
                        $scheduledCostCalc = Get-ScheduledPauseCost -CurrentCost $result.Current_Cost_EUR_Monthly -BusyWindows $busyWindows
                        $result.Scheduled_Pause_Runtime_Cost = $scheduledCostCalc.RuntimeCost
                        $result.Scheduled_Pause_Automation_Cost = $scheduledCostCalc.AutomationCost
                        $result.Scheduled_Pause_Active_Hours_Weekly = $scheduledCostCalc.ActiveHoursWeekly
                        $result.Scheduled_Pause_Total_Cost = $scheduledCostCalc.TotalCost
                    }
                    
                    # Compare options and pick best
                    $options = @(
                        [PSCustomObject]@{ Name = "Serverless"; Cost = $result.Serverless_Total_Cost }
                    )
                    
                    if ($result.Scheduled_Pause_Total_Cost -and $result.Pattern_Predictability_Score -ge 40) {
                        $options += [PSCustomObject]@{ Name = "Scheduled pause"; Cost = $result.Scheduled_Pause_Total_Cost }
                    }
                    
                    $best = $options | Sort-Object Cost | Select-Object -First 1
                    $alternative = $options | Where-Object { $_.Name -ne $best.Name } | Sort-Object Cost | Select-Object -First 1
                    
                    $result.Recommendation = "Migrate to $($best.Name)"
                    $result.RecommendedTier = "GP_S_Gen5_$minVCore-$maxVCore"
                    $result.RecommendedCapacity = $minVCore
                    $result.Recommended_Cost_EUR_Monthly = $best.Cost
                    
                    if ($alternative) {
                        $result.Alternative_Recommendation = "$($alternative.Name) (€$($alternative.Cost))"
                        $result.Alternative_Cost_EUR_Monthly = $alternative.Cost
                    }
                    
                    $result.Priority = 'High'
                    $result.NextAction = "Test serverless migration"
                    
                    if ($result.Scheduled_Pause_Feasibility) {
                        $result.Flags = "Scheduled pause feasibility: $($result.Scheduled_Pause_Feasibility)"
                    }
                } else {
                    $result.Serverless_Viable = $false
                    $result.Flags = "Serverless blocked: $($serverlessDisqualifiers -join ', ')"
                    # Fallback to Tier 4C
                    $result.Classification = 'BURSTY_PROVISIONED'
                }
            }
        }
        
        'SPARSE' {
            # Similar to BURSTY for serverless
            if ($isServerless) {
                # Already serverless - optimize
                $result.Serverless_Viable = $true
                $result.Status = 'OK'
                $result.Recommendation = "OK (serverless appropriate for sparse usage)"
                $result.Priority = 'Low'
                
                # Check if we can optimize
                $optimalMaxVCore = [Math]::Max(0.5, [Math]::Ceiling($result.CPU_Max / 100 * 1.2))
                
                if ($optimalMaxVCore -lt $db.Capacity) {
                    $optimalMinVCore = 0.5
                    $result.Status = 'OPTIMIZE'
                    $result.Recommendation = "Reduce serverless max vCore"
                    $result.RecommendedTier = "GP_S_Gen5_$optimalMinVCore-$optimalMaxVCore"
                    $result.RecommendedCapacity = $optimalMinVCore
                    
                    $serverlessCostCalc = Get-EstimatedServerlessCost -VCores $optimalMinVCore -TimeSeries $computeData -ValueProperty 'Metric_Value' -StorageGB $result.Storage_Used_GB
                    $result.Recommended_Cost_EUR_Monthly = $serverlessCostCalc.TotalCost
                    $result.Serverless_Active_Hours_Monthly = $serverlessCostCalc.ActiveHoursMonthly
                    $result.Serverless_Compute_Cost = $serverlessCostCalc.ComputeCost
                    $result.Serverless_Storage_Cost = $serverlessCostCalc.StorageCost
                    $result.Serverless_Total_Cost = $serverlessCostCalc.TotalCost
                    
                    $result.Priority = 'Medium'
                }
            } elseif ($result.Connections_Per_Hour_Avg -lt 2) {
                $result.Serverless_Viable = $true
                $result.Status = 'OPTIMIZE'
                
                # Calculate serverless cost breakdown
                $serverlessCostCalc = Get-EstimatedServerlessCost -VCores 0.5 -TimeSeries $computeData -ValueProperty 'Metric_Value' -StorageGB $result.Storage_Used_GB
                $result.Serverless_Active_Hours_Monthly = $serverlessCostCalc.ActiveHoursMonthly
                $result.Serverless_Compute_Cost = $serverlessCostCalc.ComputeCost
                $result.Serverless_Storage_Cost = $serverlessCostCalc.StorageCost
                $result.Serverless_Total_Cost = $serverlessCostCalc.TotalCost
                
                # Calculate scheduled pause cost if pattern is predictable
                if ($busyWindows.Count -gt 0) {
                    $scheduledCostCalc = Get-ScheduledPauseCost -CurrentCost $result.Current_Cost_EUR_Monthly -BusyWindows $busyWindows
                    $result.Scheduled_Pause_Runtime_Cost = $scheduledCostCalc.RuntimeCost
                    $result.Scheduled_Pause_Automation_Cost = $scheduledCostCalc.AutomationCost
                    $result.Scheduled_Pause_Active_Hours_Weekly = $scheduledCostCalc.ActiveHoursWeekly
                    $result.Scheduled_Pause_Total_Cost = $scheduledCostCalc.TotalCost
                }
                
                # Compare options
                $options = @(
                    [PSCustomObject]@{ Name = "Serverless"; Cost = $result.Serverless_Total_Cost }
                )
                
                if ($result.Scheduled_Pause_Total_Cost -and $result.Pattern_Predictability_Score -ge 40) {
                    $options += [PSCustomObject]@{ Name = "Scheduled pause"; Cost = $result.Scheduled_Pause_Total_Cost }
                }
                
                $best = $options | Sort-Object Cost | Select-Object -First 1
                $alternative = $options | Where-Object { $_.Name -ne $best.Name } | Sort-Object Cost | Select-Object -First 1
                
                $result.Recommendation = "Migrate to $($best.Name) (sparse usage)"
                $result.RecommendedTier = "GP_S_Gen5_0.5-1"
                $result.RecommendedCapacity = 0.5
                $result.Recommended_Cost_EUR_Monthly = $best.Cost
                
                if ($alternative) {
                    $result.Alternative_Recommendation = "$($alternative.Name) (€$($alternative.Cost))"
                    $result.Alternative_Cost_EUR_Monthly = $alternative.Cost
                }
                
                $result.Priority = 'High'
                
                if ($result.Scheduled_Pause_Feasibility) {
                    $result.Flags = "Scheduled pause feasibility: $($result.Scheduled_Pause_Feasibility)"
                }
            } else {
                $result.Status = 'REVIEW'
                $result.Recommendation = "FLAG: Decommission review (sparse usage but connections prevent serverless)"
                $result.Priority = 'Medium'
            }
        }
        
        'PERIODIC' {
            # Tier 4B: Elastic pool candidate
            $result.ElasticPool_Candidate = $true
            $result.Status = 'OPTIMIZE'
            $result.Recommendation = "Elastic pool candidate (predictable pattern)"
            $result.Flags = "Requires multi-DB analysis for pool sizing"
            $result.Priority = 'Medium'
        }
        
        'WEEKEND_WEEKDAY' {
            # Tier 4B: Scheduled scaling or pool
            $result.ElasticPool_Candidate = $true
            $result.Status = 'OPTIMIZE'
            $result.Recommendation = "Scheduled scaling OR elastic pool"
            $result.Flags = "Weekly pattern: weekday/weekend variance $([Math]::Round($weeklyVar, 0))%"
            $result.Priority = 'Medium'
        }
        
        'BATCH_HEAVY' {
            # Tier 4D: Workload split analysis
            $result.Status = 'OPTIMIZE'
            $result.Recommendation = "Consider workload split (OLTP + batch)"
            $result.Flags = "Bimodal usage detected"
            $result.Priority = 'Medium'
            $result.NextAction = "Assess if batch can be separated"
        }
        
        'CHAOTIC' {
            # Tier 4F: Conservative provisioning
            $result.Status = 'OK'
            $targetCapacity = $computeP95 * 1.5  # 1.5x safety margin
            $currentCapacity = if ($isDTU) { $DTULimits[$skuCode].DTU } else { $db.Capacity * 100 }
            
            # Hyperscale cannot downgrade
            if ($isHyperscale) {
                $result.Recommendation = "OK (Hyperscale - high variance, query optimization advised)"
                $result.Flags = "Chaotic pattern - Hyperscale tier cannot downgrade"
            } elseif ($targetCapacity -lt $currentCapacity * 0.7) {
                $result.Recommendation = "Conservative downgrade possible (high variance workload)"
                $result.Status = 'OPTIMIZE'
                $result.Priority = 'Low'
            } else {
                $result.Recommendation = "OK (high variance, query optimization advised)"
            }
            
            if (-not $result.Flags) {
                $result.Flags = "Chaotic pattern - unpredictable peaks"
            }
            $result.Confidence = 'Low'
        }
        
        'UNCLASSIFIED' {
            # Tier 4F: Conservative
            $result.Status = 'OK'
            $result.Recommendation = "OK (workload pattern unclear)"
            $result.Flags = "Unclassified pattern"
            $result.Confidence = 'Low'
        }
        
        default {
            # STEADY, BURSTY_PROVISIONED, or fallback
            # Tier 4C: Steady state optimization
            
            # Calculate optimal tier (60-75% utilization target)
            $optimalCapacity = $computeP95 * 1.2  # 20% headroom
            
            if ($isDTU) {
                # Find optimal DTU tier
                $tiers = @('S0', 'S1', 'S2', 'S3', 'S4', 'S6', 'S7', 'S9', 'S12')
                $optimalTier = $null
                
                foreach ($tier in $tiers) {
                    $tierCapacity = $DTULimits[$tier].DTU
                    if ($optimalCapacity -le ($tierCapacity * 0.75)) {
                        $optimalTier = $tier
                        break
                    }
                }
                
                if (-not $optimalTier) {
                    # Check Premium
                    $premiumTiers = @('P1', 'P2', 'P4', 'P6', 'P11', 'P15')
                    foreach ($tier in $premiumTiers) {
                        $tierCapacity = $DTULimits[$tier].DTU
                        if ($optimalCapacity -le ($tierCapacity * 0.75)) {
                            $optimalTier = $tier
                            break
                        }
                    }
                }
                
                if ($optimalTier) {
                    $currentCapacityValue = $DTULimits[$skuCode].DTU
                    $optimalCapacityValue = $DTULimits[$optimalTier].DTU
                    
                    if ($computeP95 -ge ($currentCapacityValue * 0.6) -and $computeP95 -le ($currentCapacityValue * 0.8)) {
                        # Current tier is optimal
                        $result.Status = 'OK'
                        $result.Recommendation = "OK (optimal utilization)"
                        $result.Confidence = 'High'
                    } elseif ($optimalCapacityValue -lt $currentCapacityValue) {
                        # Can downgrade
                        $result.Status = 'OPTIMIZE'
                        $result.Recommendation = "Downgrade to $optimalTier"
                        $result.RecommendedTier = $optimalTier
                        $result.Recommended_Cost_EUR_Monthly = Get-DatabaseCost -Edition 'Standard' -SkuName $optimalTier -Capacity 0
                        $result.Priority = 'Medium'
                        $result.Confidence = 'High'
                    } else {
                        $result.Status = 'OK'
                        $result.Recommendation = "OK (tier appropriate)"
                    }
                } else {
                    # Very high usage, consider vCore
                    $result.Status = 'OPTIMIZE'
                    $result.Recommendation = "Consider migration to vCore (high DTU usage)"
                    $result.Priority = 'Medium'
                }
            } else {
                # vCore optimization
                
                # Hyperscale special handling
                if ($isHyperscale) {
                    # Hyperscale cannot easily downgrade, only optimize vCores
                    $optimalVCores = [Math]::Ceiling($result.CPU_P95 / 100 * 1.2)
                    
                    if ($result.CPU_P95 -ge 60 -and $result.CPU_P95 -le 80) {
                        $result.Status = 'OK'
                        $result.Recommendation = "OK (Hyperscale utilization optimal)"
                        $result.Flags = 'Hyperscale tier - storage auto-scales'
                    } elseif ($optimalVCores -lt $db.Capacity) {
                        $result.Status = 'OPTIMIZE'
                        $result.Recommendation = "Reduce Hyperscale vCores to $optimalVCores"
                        $result.RecommendedCapacity = $optimalVCores
                        $result.Recommended_Cost_EUR_Monthly = $optimalVCores * $Pricing['GP_Gen5_vCore']
                        $result.Priority = 'Medium'
                        $result.Flags = 'Hyperscale tier - cannot downgrade to non-Hyperscale'
                    } else {
                        $result.Status = 'OK'
                        $result.Recommendation = "OK (Hyperscale vCores appropriate)"
                        $result.Flags = 'Hyperscale tier - storage auto-scales'
                    }
                } else {
                    # Regular vCore optimization
                    $optimalVCores = [Math]::Ceiling($result.CPU_P95 / 100 * 1.2)
                    
                    if ($result.CPU_P95 -ge 60 -and $result.CPU_P95 -le 80) {
                        $result.Status = 'OK'
                        $result.Recommendation = "OK (optimal utilization)"
                    } elseif ($optimalVCores -lt $db.Capacity) {
                        $result.Status = 'OPTIMIZE'
                        $result.Recommendation = "Reduce vCores to $optimalVCores"
                        $result.RecommendedCapacity = $optimalVCores
                        $result.Recommended_Cost_EUR_Monthly = $optimalVCores * $Pricing['GP_Gen5_vCore']
                        $result.Priority = 'Medium'
                    } else {
                        $result.Status = 'OK'
                        $result.Recommendation = "OK (vCore appropriate)"
                    }
                }
            }
        }
    }
    }  # End of else block for normal classification-based optimization
    
    # TIER 5: Cost validation and Impact assessment
    if ($result.Status -eq 'OPTIMIZE' -and $result.Recommended_Cost_EUR_Monthly -gt 0) {
        # Calculate savings if not already set (serverless-to-provisioned already calculated)
        if ($result.Savings_EUR_Monthly -eq 0) {
            $result.Savings_EUR_Monthly = [Math]::Round($result.Current_Cost_EUR_Monthly - $result.Recommended_Cost_EUR_Monthly, 2)
            
            if ($result.Current_Cost_EUR_Monthly -gt 0) {
                $result.Savings_Percent = [Math]::Round(($result.Savings_EUR_Monthly / $result.Current_Cost_EUR_Monthly * 100), 2)
            }
        }
        
        # Set Impact level based on savings (if not already set by serverless-to-provisioned logic)
        if ($result.Impact -eq 'Medium' -and -not $serverlessShouldMoveToProvisioned) {
            if ($result.Savings_EUR_Monthly -gt 100 -or $result.Savings_Percent -gt 50) {
                $result.Impact = 'High'
            } elseif ($result.Savings_EUR_Monthly -gt 50 -or $result.Savings_Percent -gt 30) {
                $result.Impact = 'Medium'
            } elseif ($result.Savings_EUR_Monthly -gt 0 -or $result.Savings_Percent -gt 0) {
                $result.Impact = 'Low'
            } else {
                $result.Impact = 'None'
            }
        }
        
        # Keep recommendation but adjust priority/flags for low-impact changes
        if ($result.Savings_EUR_Monthly -lt 20 -and $result.Savings_Percent -lt 15) {
            # Very low savings - mark as low impact but keep recommendation
            $result.Impact = 'Low'
            $result.Priority = 'Low'
            $result.Flags = if ($result.Flags) {
                "$($result.Flags) | Low cost impact: €$($result.Savings_EUR_Monthly)/mo ($($result.Savings_Percent)%)"
            } else {
                "Low cost impact: €$($result.Savings_EUR_Monthly)/mo ($($result.Savings_Percent)%)"
            }
        } elseif ($result.Savings_EUR_Monthly -lt 50 -and $result.Savings_Percent -lt 30) {
            # Moderate savings
            if ($result.Impact -eq 'Medium') {
                $result.Priority = 'Medium'
            }
        } else {
            # Good savings
            if ($result.Impact -eq 'High' -and $result.Priority -ne 'High') {
                $result.Priority = 'High'
            }
        }
    }
    
    # Set Impact for non-optimization recommendations
    if ($result.Status -ne 'OPTIMIZE' -and $result.Impact -eq 'Medium') {
        if ($result.Status -eq 'UPGRADE') {
            $result.Impact = 'High'  # Upgrades are high impact (avoid constraints)
        } elseif ($result.Status -eq 'REVIEW') {
            $result.Impact = 'Medium'  # Reviews need attention
        } else {
            $result.Impact = 'None'  # OK status = no change needed
        }
    }
    
    #endregion
    
    $result
}

#endregion

# Output results
$results