Advisor_Locally_Run.ps1

# ----------------------------------------------
# Azure Advisor Recommendations
# ----------------------------------------------
# Notes:
# Groups by: Security, Reliability, Performance, Operational Excellence, Cost
# Priority shown: Critical/High (RED), Medium (AMBER). Low findings are hidden in detail tables.
# Added URL Checker to direct Security to DfC, and everything else to Advisor
# Fixed intermittent issue with 'Summary by Category' showing blank returns.
#
# Future Work:
# Include MS documentation on what the issue is, and how to resolve it, as well as
# the current DfC resolution.
#
# ----------------------------------------------

# -------------------------
# Variables
# -------------------------
[int]$global:RetryCnt       = 0
[string]$global:Failure     = ""

# SendGrid (SMTP)
[string]$Global:SendGrid    = "" # Insert your Sendgrid API

[string]$global:TenantID       = "" # Change to your Tenant ID
[string]$global:ClientID       = "" # Change to your Service Principal ID
[string]$global:ClientSecret   = "" # Change to your Client Secret Value
[string]$global:SubscriptionId = "" # Change to your Sub ID

# Report settings
[string]$FromAddress       = "sendername@domain.com"
[string]$ToAddressesCsv    = "whoisgettingit@domain.com" # Who is receiving it
[string]$MailSubject       = "Advisor Recommendations - $(Get-Date -Format 'dd/MM/yyyy HH:mm')"
[string]$OptionalHtmlPath  = "$env:TEMP\AdvisorRecommendations.html" # Can be commented out if not testing or you don't need a local copy saving as a HTML

# ---------------------------------
# 1) Modules
# ---------------------------------
Import-Module Az.Accounts  -ErrorAction SilentlyContinue
Import-Module Az.Resources -ErrorAction SilentlyContinue

# ---------------------------------
# 2) Sign in with Service Principal
# ---------------------------------
if ([string]::IsNullOrWhiteSpace($global:TenantID))       { Write-Error "global:TenantID is empty."; exit 1 }
if ([string]::IsNullOrWhiteSpace($global:ClientID))       { Write-Error "global:ClientID is empty."; exit 1 }
if ([string]::IsNullOrWhiteSpace($global:ClientSecret))   { Write-Error "global:ClientSecret is empty."; exit 1 }
if ([string]::IsNullOrWhiteSpace($global:SubscriptionId)) { Write-Error "global:SubscriptionId is empty."; exit 1 }

$sec  = ConvertTo-SecureString $global:ClientSecret -AsPlainText -Force
$cred = New-Object System.Management.Automation.PSCredential ($global:ClientID, $sec)

Connect-AzAccount -ServicePrincipal -TenantId $global:TenantID -Credential $cred | Out-Null
Set-AzContext -SubscriptionId $global:SubscriptionId | Out-Null
$Subscriptions = @(Get-AzSubscription -SubscriptionId $global:SubscriptionId)

# ---------------------------------
# 3) Helpers
# ---------------------------------
function Encode-Html([string]$s) { if ($null -eq $s) { return "" } [System.Net.WebUtility]::HtmlEncode($s) }
function HtmlAttr([string]$s)   { if ($null -eq $s) { return "" } [System.Web.HttpUtility]::HtmlAttributeEncode($s) }
function UrlEncode([string]$s)  { if ($null -eq $s) { return "" } [System.Web.HttpUtility]::UrlEncode($s) }

function Get-ArmBearerToken {
  try {
    $ctx = Get-AzContext
    if ($ctx) {
      $profile = [Microsoft.Azure.Commands.Common.Authentication.Abstractions.AzureRmProfileProvider]::Instance.Profile
      $client  = [Microsoft.Azure.Commands.ResourceManager.Common.RMProfileClient]::new($profile)
      $token   = $client.AcquireAccessToken($ctx.Subscription.TenantId)
      if ($token.AccessToken) { return $token.AccessToken }
    }
  } catch {}
  # Fallback to client credentials
  $body = @{
    grant_type    = "client_credentials"
    client_id     = $global:ClientID
    client_secret = $global:ClientSecret
    scope         = "https://management.azure.com/.default"
  }
  $resp = Invoke-RestMethod -Method POST -Uri "https://login.microsoftonline.com/$($global:TenantID)/oauth2/v2.0/token" -Body $body
  return $resp.access_token
}

# Extract a friendly resource name when Advisor provides it
function Get-ResourceFriendlyName {
  param($ResourceMetadata, [string]$ResourceId)
  if (-not $ResourceMetadata -and -not $ResourceId) { return $null }
  foreach ($propName in @('displayName','resourceName','name','friendlyName')) {
    $prop = $ResourceMetadata.PSObject.Properties[$propName]
    if ($prop -and $prop.Value) { return [string]$prop.Value }
  }
  if ($ResourceMetadata -and $ResourceMetadata.PSObject.Properties['additionalInfo']) {
    $ai = $ResourceMetadata.additionalInfo
    foreach ($propName in @('resourceName','name','displayName')) {
      $p = $ai.PSObject.Properties[$propName]
      if ($p -and $p.Value) { return [string]$p.Value }
    }
  }
  if ($ResourceId) {
    $parts = $ResourceId.Trim('/') -split '/'
    if ($parts.Length -gt 0) { return $parts[-1] }
  }
  return $null
}

# Pull Advisor recommendations for a subscription (REST)
function Get-AdvisorRecommendationsForSubscription {
  param(
    [Parameter(Mandatory)][string]$SubscriptionId,
    [Parameter(Mandatory)][string]$SubscriptionName
  )

  $token   = Get-ArmBearerToken
  $headers = @{ Authorization = "Bearer $token" }
  $api     = "2023-01-01"
  $url     = "https://management.azure.com/subscriptions/$SubscriptionId/providers/Microsoft.Advisor/recommendations?api-version=$api&`$top=1000"

  $out = @()
  do {
    Write-Host "Fetching Advisor page for $SubscriptionId..."
    $resp = Invoke-RestMethod -Method GET -Uri $url -Headers $headers
    if ($resp.value) { $out += $resp.value }
    $url = $resp.nextLink
  } while ($url)

  return ,@($SubscriptionName, $out)
}

# Return first URL, preferring Azure Portal blades first, then official docs
function Select-PreferredUrl {
  param([string[]]$Urls)
  if (-not $Urls) { return $null }
  $dedup = $Urls | Where-Object { $_ } | Select-Object -Unique
  if (-not $dedup) { return $null }

  # 1) Azure Portal blades (Advisor/Defender/etc.)
  $portal = $dedup | Where-Object { $_ -match '^https?://[^/]*portal\.azure\.com/#' }
  if ($portal) { return $portal[0] }

  # 2) Official Microsoft docs
  $docs = $dedup | Where-Object {
    $_ -match '^https?://(learn\.microsoft\.com|docs\.microsoft\.com|azure\.microsoft\.com|aka\.ms/)'
  }
  if ($docs) { return $docs[0] }

  # 3) Anything else
  return $dedup[0]
}

# Deep-scan any PSObject/array for URLs in *any* string field
function Find-UrlsDeep {
  param($Obj)
  $urls = New-Object System.Collections.Generic.List[string]
  function _scan($o) {
    if ($null -eq $o) { return }
    if ($o -is [string]) {
      foreach ($m in [regex]::Matches($o, '(https?://[^\s"\\<>\]]+)', 'IgnoreCase')) {
        if ($m.Value) { $urls.Add($m.Value) | Out-Null }
      }
      return
    }
    if ($o -is [System.Collections.IEnumerable] -and -not ($o -is [string])) {
      foreach ($item in $o) { _scan $item }
      return
    }
    if ($o.PSObject -and $o.PSObject.Properties) {
      foreach ($p in $o.PSObject.Properties) { _scan $p.Value }
    }
  }
  _scan $Obj
  return ($urls | Where-Object { $_ } | Select-Object -Unique)
}

function New-SecurityRecommendationUrl {
  param(
    [Parameter(Mandatory)][string]$SubscriptionId,       # kept for compatibility, not used in URL
    [Parameter(Mandatory)][string]$RecommendationTypeId, # assessment key
    [string]$ImpactedResourceId                          # kept for compatibility, not used in URL
  )
  # Defender for Cloud / Advisor recommendation details blade.
  # Pattern taken from the 'links.azurePortalUri' examples in the Defender for Cloud REST docs:
  #   https://portal.azure.com/#blade/Microsoft_Azure_Security/RecommendationsBlade/assessmentKey/{key}

  $base = "https://portal.azure.com/#blade/Microsoft_Azure_Security/RecommendationsBlade/assessmentKey/$RecommendationTypeId"

  return $base
}

function New-AdvisorRecommendationUrl {
  param(
    [Parameter(Mandatory)][string]$SubscriptionId,
    [Parameter(Mandatory)][string]$RecommendationTypeId,
    [Parameter(Mandatory)][string]$CategoryRaw,   # HighAvailability, Cost, Performance, OperationalExcellence, etc.
    [string]$ResourceType = "microsoft.subscriptions%2Fsubscriptions"
  )

  # Example working pattern:
  # https://portal.azure.com/#view/Microsoft_Azure_Expert/RecommendationList.ReactView/
  #   recommendationTypeId/{id}/recommendationStatus~/0
  #   /subscriptionIds~/["{sub}"]/resourceType/microsoft.subscriptions%2Fsubscriptions
  #   /parentBlade/AdvisorDashboard.ReactView/category/{CategoryRaw}

  $subEsc = [System.Web.HttpUtility]::UrlEncode("[`"$SubscriptionId`"]")

  $base = "https://portal.azure.com/#view/Microsoft_Azure_Expert/RecommendationList.ReactView" +
          "/recommendationTypeId/$RecommendationTypeId" +
          "/recommendationStatus~/0" +
          "/subscriptionIds~/$subEsc" +
          "/resourceType/$ResourceType" +
          "/parentBlade/AdvisorDashboard.ReactView" +
          "/category/$CategoryRaw"

  return $base
}

# Pull a remediation URL/text/policy link from the full recommendation (deep scan)
function Get-RemediationData {
  param([Parameter(Mandatory)]$Recommendation)

  if (-not $Recommendation) {
    return [pscustomobject]@{ Url=$null; Text=$null; PolicyLink=$null; RecPortalUrl=$null; RecDetailsUrl=$null }
  }

  $p = $Recommendation.properties
  $urlCandidates = New-Object System.Collections.Generic.List[string]
  $textPieces    = New-Object System.Collections.Generic.List[string]
  $policyDefId   = $null

  # Always keep the ARM resource URL as a safe fallback
  $recPortalUrl = $null
  if ($Recommendation.id) { $recPortalUrl = "https://portal.azure.com/#resource$($Recommendation.id)" }

  # Try to build the recommendation details link, but use different blades for Security vs other Advisor categories.
  $recDetailsUrl = $null
  try {
    $subId     = ($Recommendation.id -split '/')[2]    # /subscriptions/{subId}/...
    $recTypeId = $p.recommendationTypeId
    $impacted  = $p.impactedValue
    $catRaw    = if ($p.PSObject.Properties['category']) { [string]$p.category } else { $null }

    if ($subId -and $recTypeId -and $catRaw) {
      if ($catRaw -eq 'Security') {
        # Security > Defender for Cloud blade
        $recDetailsUrl = New-SecurityRecommendationUrl -SubscriptionId $subId -RecommendationTypeId $recTypeId -ImpactedResourceId $impacted
      }
      else {
        # Reliability / Cost / Performance / OperationalExcellence > Advisor React view
        $recDetailsUrl = New-AdvisorRecommendationUrl -SubscriptionId $subId -RecommendationTypeId $recTypeId -CategoryRaw $catRaw
      }
    }
  } catch {}


  # harvest likely remediation/document links
  if ($p -and $p.PSObject.Properties['remediationDescription']) {
    $d = [string]$p.remediationDescription
    if ($d) {
      $textPieces.Add($d) | Out-Null
      $m = [regex]::Match($d,'https?://[^\s"<>]+'); if ($m.Success){$urlCandidates.Add($m.Value)|Out-Null}
    }
  }
  if ($p -and $p.PSObject.Properties['remediation']) {
    $rem = $p.remediation
    if ($rem -is [string]) {
      $textPieces.Add($rem) | Out-Null
      $m = [regex]::Match($rem,'https?://[^\s"<>]+'); if ($m.Success){$urlCandidates.Add($m.Value)|Out-Null}
    } elseif ($rem) {
      foreach ($node in @($rem) ) {
        if ($node -and $node.PSObject) {
          foreach ($k in @('text','description','details')) { if ($node.PSObject.Properties[$k] -and $node.$k) { $textPieces.Add([string]$node.$k)|Out-Null } }
          foreach ($k in @('url','link','href','portalLink','portalUrl','learnMoreLink')) { if ($node.PSObject.Properties[$k] -and $node.$k) { $urlCandidates.Add([string]$node.$k)|Out-Null } }
          if ($node.PSObject.Properties['steps']) {
            foreach ($step in $node.steps) {
              if ($step -and $step.PSObject) {
                foreach ($k in @('text','description','details')) { if ($step.PSObject.Properties[$k] -and $step.$k) { $textPieces.Add([string]$step.$k)|Out-Null } }
                foreach ($k in @('url','link','href','portalLink','portalUrl','learnMoreLink')) { if ($step.PSObject.Properties[$k] -and $step.$k) { $urlCandidates.Add([string]$step.$k)|Out-Null } }
              }
            }
          }
        }
      }
    }
  }
  if ($p) {
    foreach ($k in @('portalLink','portalUrl','learnMoreLink','remediationUrl','helpLink','documentationUrl','docUrl','link','url')) {
      if ($p.PSObject.Properties[$k] -and $p.$k) { $urlCandidates.Add([string]$p.$k)|Out-Null }
    }
    if ($p.PSObject.Properties['links'] -and $p.links) {
      foreach ($l in $p.links) {
        if ($l -and $l.PSObject) {
          foreach ($k in @('url','uri','href','portalLink','portalUrl','learnMoreLink')) {
            if ($l.PSObject.Properties[$k] -and $l.$k) { $urlCandidates.Add([string]$l.$k)|Out-Null }
          }
        }
      }
    }
  }
  if ($p -and $p.PSObject.Properties['extendedProperties'] -and $p.extendedProperties -and $p.extendedProperties.PSObject) {
    $ep = $p.extendedProperties
    foreach ($k in @('portalLink','portalUrl','learnMoreLink','documentationUrl','remediationUrl','docUrl','link','url')) {
      if ($ep.PSObject.Properties[$k] -and $ep.$k) { $urlCandidates.Add([string]$ep.$k)|Out-Null }
    }
    foreach ($k in @('remediationDescription','remediationText','solution','fix')) {
      if ($ep.PSObject.Properties[$k] -and $ep.$k) { $textPieces.Add([string]$ep.$k)|Out-Null }
    }
    foreach ($k in @('policyDefinitionId','policyId','policyDefId')) {
      if ($ep.PSObject.Properties[$k] -and $ep.$k) { $policyDefId = [string]$ep.$k }
    }
  }

  # choose a doc link if available (prefer Microsoft docs - not in place yet, but future work following some testing and a happy surprise that this worked by accident)
  $docUrl = $null
  if ($urlCandidates.Count) {
    $dedup = $urlCandidates | Select-Object -Unique
    $docUrl = ($dedup | Where-Object { $_ -match '^https?://(learn\.microsoft\.com|docs\.microsoft\.com|azure\.microsoft\.com|aka\.ms/)'} | Select-Object -First 1)
    if (-not $docUrl) { $docUrl = $dedup[0] }
  }

  $text = ($textPieces | Where-Object { $_ } | Select-Object -Unique) -join ' '
  if ($text) { $text = ($text -replace '<[^>]+>', ''); if ($text.Length -gt 240) { $text = $text.Substring(0,240) + '...' } }

  $policyLink = $null
  if (-not $docUrl -and $policyDefId) {
    $policyLink = "https://portal.azure.com/#blade/Microsoft_Azure_Policy/PolicyMenuBlade/DefinitionDetailsBlade/id/" + [System.Web.HttpUtility]::UrlEncode($policyDefId)
  }

  return [pscustomobject]@{
    Url          = $docUrl        # documentation/remediation article (if any - for future use)
    Text         = $text
    PolicyLink   = $policyLink
    RecPortalUrl = $recPortalUrl  # ARM recommendation page (fallback)
    RecDetailsUrl= $recDetailsUrl # Advisor/Defender details blade
  }
}

# ---------------------------------
# 4) Advisor Recommendations
# ---------------------------------
# Category Mapping
$CategoryMap = @{
  "Security"              = "Security"
  "HighAvailability"      = "Reliability"
  "Performance"           = "Performance"
  "OperationalExcellence" = "Operational Excellence"
  "Cost"                  = "Cost"
}

# Priority Ordering
$PriorityOrder = @{
  "Critical" = 1
  "High"     = 2
  "Medium"   = 3
  "Low"      = 4
  default    = 5
}

# Sorting Priority Rank
function Get-PriorityRank { param([string]$Risk,[string]$Impact)
  if ($Risk   -and $PriorityOrder.ContainsKey($Risk))   { return $PriorityOrder[$Risk] }
  if ($Impact -and $PriorityOrder.ContainsKey($Impact)) { return $PriorityOrder[$Impact] }
  return $PriorityOrder['default']
}

# Format potential savings nicely when we have currency/period
function Format-Savings {
  param($Properties)
  if (-not $Properties -or -not $Properties.extendedProperties) { return $null }
  $ep = $Properties.extendedProperties
  $names = $ep.PSObject.Properties.Name

  $amount = $null
  $period = $null
  $currency = $null

  if ($names -contains 'annualSavingsAmount' -and $ep.annualSavingsAmount) { $amount = $ep.annualSavingsAmount; $period = 'year' }
  elseif ($names -contains 'monthlySavingsAmount' -and $ep.monthlySavingsAmount) { $amount = $ep.monthlySavingsAmount; $period = 'month' }
  elseif ($names -contains 'savingsAmount' -and $ep.savingsAmount) { $amount = $ep.savingsAmount }

  foreach ($k in @('savingsCurrency','currency','annualSavingsCurrency','monthlySavingsCurrency')) {
    if ($names -contains $k -and $ep.$k) { $currency = [string]$ep.$k; break }
  }

  if ($amount -eq $null) { return $null }

  $amtStr = [string]$amount
  try {
    $n = [decimal]$amount
    $amtStr = $n.ToString('0.##')
  } catch {}

  $prefix = if ($currency) { "$currency" } else { "" }
  $per = if ($period) { " per $period" } else { "" }

  if ($prefix) { return "$prefix $amtStr$per" }
  else         { return "$amtStr$per" }
}

# Two collections:
#   - $rowsAll:   ALL findings (incl. Low) for summary counts
#   - $rowsTable: filtered (hide Low) for the detailed tables
$rowsAll   = New-Object System.Collections.Generic.List[object]
$rowsTable = New-Object System.Collections.Generic.List[object]

foreach ($s in $Subscriptions) {
  $subName = $s.Name
  $subId   = $s.Id
  Set-AzContext -SubscriptionId $subId | Out-Null

  $tuple = Get-AdvisorRecommendationsForSubscription -SubscriptionId $subId -SubscriptionName $subName
  $subscriptionNameFromCall = $tuple[0]
  $recs = $tuple[1]

  foreach ($r in $recs) {
    $p = $r.properties

    $recId = if ($r.PSObject.Properties['id']) { [string]$r.id } else { $null }
    $advisorPortalUrl = if ($recId) { "https://portal.azure.com/#resource$recId" } else { $null }

    $catRaw = [string]$p.category
    $cat    = if ($CategoryMap.ContainsKey($catRaw)) { $CategoryMap[$catRaw] } else { $catRaw }

    $risk   = if ($p.psobject.Properties['risk'])   { [string]$p.risk }   else { $null }
    $impact = if ($p.psobject.Properties['impact']) { [string]$p.impact } else { $null }
    $rank   = Get-PriorityRank -Risk $risk -Impact $impact
    $label  = $(if ($risk) { $risk } elseif ($impact) { $impact } else { 'Unknown' })

    $rid = [string]$p.resourceMetadata.resourceId

    # Parse type/name and build proper portal URL, handling subscription-scope IDs
    $rtype=$null;$ridName=$null;$resourcePortalUrl=$null
    if ($rid) {
      $trim = $rid.Trim('/')
      $parts = $trim -split '/'
      # Subscription-only scope: /subscriptions/{subId}
      if ($parts.Length -eq 2 -and $parts[0] -eq 'subscriptions') {
        $rtype     = 'Subscription'
        $ridName   = $subscriptionNameFromCall   # shows friendly subscription name
        $subOnlyId = $parts[1]
        $resourcePortalUrl = "https://portal.azure.com/#view/Microsoft_Azure_Billing/SubscriptionsBlade/~/overview?subscriptionId=$subOnlyId"
      }
      else {
        # Normal resource scope
        $provIdx= [Array]::IndexOf($parts,'providers')
        if ($provIdx -ge 0 -and $parts.Length -gt ($provIdx+1)) {
          $provider = $parts[$provIdx+1]
          if ($parts.Length -gt ($provIdx+2)) {
            $typeSegments = $parts[($provIdx+2)..($parts.Length-2)]
            $rtype = ("$provider/" + ($typeSegments -join '/'))
            $ridName = $parts[-1]
          } else {
            $rtype = $provider; $ridName = $parts[-1]
          }
        } else {
          $ridName = $parts[-1]
        }
        $resourcePortalUrl = "https://portal.azure.com/#resource$rid"
      }
    }

    $friendlyName = Get-ResourceFriendlyName -ResourceMetadata $p.resourceMetadata -ResourceId $rid
    if (-not $friendlyName) { $friendlyName = $ridName }
    if (-not $resourcePortalUrl -and $rid) { $resourcePortalUrl = "https://portal.azure.com/#resource$rid" }

    # Problem short text
    $shortText = ""
    if ($p.shortDescription) {
      if     ($p.shortDescription -is [string])                       { $shortText = "$($p.shortDescription)" }
      elseif ($p.shortDescription.psobject.Properties['Problem'])     { $shortText = "$($p.shortDescription.Problem)" }
      elseif ($p.shortDescription.psobject.Properties['Description']) { $shortText = "$($p.shortDescription.Description)" }
    }

    # Potential savings
    $savings = Format-Savings -Properties $p
    if (-not $savings) { $savings = 'N/A' }

    # Remediation URL/snippet/policy link
    $remed = Get-RemediationData -Recommendation $r

    # RAG colour
    $ragColor = if ($label -in @('Critical','High')) { '#b10035' } elseif ($label -eq 'Medium') { 'Orange' } else { 'transparent' }
    $priorityTextColor = if ($label -eq 'Medium') { '#000' } else { '#fff' }

    # LastUpdated: "dd-MM-yyyy"
    $lastFmt = ""
    if ($p.psobject.Properties['lastUpdated'] -and $p.lastUpdated) {
      try {
        $dt = [datetime]::Parse($p.lastUpdated).ToLocalTime()
        $lastFmt = $dt.ToString('dd-MM-yyyy')    # or 'HH:mm dd-MM-yyyy'
      } catch { $lastFmt = [string]$p.lastUpdated }
    }

    # Build base object (used for both summary + table)
    $obj = [pscustomobject]@{
      SubscriptionName   = $subscriptionNameFromCall
      Category           = $cat
      PriorityRank       = $rank
      PriorityLabel      = $label
      RAGColor           = $ragColor
      PriorityTextColor  = $priorityTextColor
      ResourceType       = $rtype
      ResourceName       = $friendlyName
      ResourceId         = $rid
      ResourcePortalUrl  = $resourcePortalUrl
      Problem            = $shortText
      RemediationUrl     = $remed.Url
      RemediationText    = $remed.Text
      PolicyLink         = $remed.PolicyLink
      RecPortalUrl       = $remed.RecPortalUrl
      PotentialSavings   = $savings
      LastUpdated        = $lastFmt
      AdvisorRecId       = $recId
      AdvisorPortalUrl   = $advisorPortalUrl
      RecDetailsUrl      = $remed.RecDetailsUrl
    }

    # Add to "all" (for summary counts)
    $rowsAll.Add($obj) | Out-Null

    # Add to "table" only if not Low
    if ($label -ne 'Low') { $rowsTable.Add($obj) | Out-Null }
  }
}

$rowsAll   = $rowsAll.ToArray()
$rowsTable = $rowsTable.ToArray() | Sort-Object Category, PriorityRank, SubscriptionName, ResourceName

# ---------------------------------
# 5) Build Summary by Category (auto-sized columns via <colgroup>)
# ---------------------------------
$allCategories = @('Security','Reliability','Performance','Operational Excellence','Cost')

$summary = foreach ($cat in $allCategories) {
    $catRows = $rowsAll | Where-Object { $_.Category -eq $cat }

    # High = Critical or High by label OR rank 1/2
    $highCount = ($catRows | Where-Object {
        $_.PriorityLabel -in @('Critical','High') -or $_.PriorityRank -in 1,2
    } | Measure-Object).Count

    # Medium = Medium by label OR rank 3
    $mediumCount = ($catRows | Where-Object {
        $_.PriorityLabel -eq 'Medium' -or $_.PriorityRank -eq 3
    } | Measure-Object).Count

    # Total = all recommendations in that category
    $totalCount = ($catRows | Measure-Object).Count

    # Low = everything that isn't High or Medium
    $lowCount = $totalCount - $highCount - $mediumCount
    if ($lowCount -lt 0) { $lowCount = 0 }   # safety guard

    [pscustomobject]@{
        Category = $cat
        High     = [int]$highCount
        Medium   = [int]$mediumCount
        Low      = [int]$lowCount
        Total    = [int]$totalCount
    }
}

# helper to get max string length over a property
function Get-MaxLen {
  param($items, [string]$prop)
  ($items | ForEach-Object { ([string]($_.$prop)).Length } | Measure-Object -Maximum).Maximum
}

$lenCat   = [Math]::Max($headers.Category.Length, (Get-MaxLen -items $summary -prop 'Category'))
$lenHigh  = [Math]::Max($headers.High.Length,    (Get-MaxLen -items $summary -prop 'High'))
$lenMed   = [Math]::Max($headers.Medium.Length,  (Get-MaxLen -items $summary -prop 'Medium'))
$lenLow   = [Math]::Max($headers.Low.Length,     (Get-MaxLen -items $summary -prop 'Low'))
$lenTotal = [Math]::Max($headers.Total.Length,   (Get-MaxLen -items $summary -prop 'Total'))

# convert char lengths to pixel widths (roughly ~8px/char + padding)
$pxPerChar = 8
$padPx     = 24
function To-Px([int]$chars, [int]$minPx=0, [int]$maxPx=9999) {
  $w = ($chars * $pxPerChar) + $padPx
  return [Math]::Min([Math]::Max($w, $minPx), $maxPx)
}

$wCat   = To-Px $lenCat   140 600   # Category can be long
$wHigh  = To-Px $lenHigh   60 160
$wMed   = To-Px $lenMed    70 160
$wLow   = To-Px $lenLow    60 140
$wTotal = To-Px $lenTotal  60 140

# Build Summary HTML (explicit col widths; inline styles for mail clients)
$SummaryHTML = @"
<h2>Summary by Category</h2>
<table class="summary" role="presentation" style="border-collapse:collapse; table-layout:fixed; width:auto; display:inline-table;">
  <colgroup>
    <col style="width:${wCat}px" />
    <col style="width:${wHigh}px" />
    <col style="width:${wMed}px" />
    <col style="width:${wLow}px" />
    <col style="width:${wTotal}px" />
  </colgroup>
  <tr>
    <th style="background:#0f2147;color:#fff; text-align:left; padding:8px; border:1px solid #ddd;">$($headers.Category)</th>
    <th style="background:#0f2147;color:#fff; text-align:left; padding:8px; border:1px solid #ddd;">$($headers.High)</th>
    <th style="background:#0f2147;color:#fff; text-align:left; padding:8px; border:1px solid #ddd;">$($headers.Medium)</th>
    <th style="background:#0f2147;color:#fff; text-align:left; padding:8px; border:1px solid #ddd;">$($headers.Low)</th>
    <th style="background:#0f2147;color:#fff; text-align:left; padding:8px; border:1px solid #ddd;">$($headers.Total)</th>
  </tr>
"@

foreach ($s in $summary) {
  $SummaryHTML += "<tr>"
  $SummaryHTML += "<td style='padding:8px;border:1px solid #ddd;white-space:nowrap;'>$(Encode-Html $($s.Category))</td>"
  $SummaryHTML += "<td style='padding:8px;border:1px solid #ddd;white-space:nowrap;color:#b10035;font-weight:bold;'>$($s.High)</td>"
  $SummaryHTML += "<td style='padding:8px;border:1px solid #ddd;white-space:nowrap;color:orange;font-weight:bold;'>$($s.Medium)</td>"
  $SummaryHTML += "<td style='padding:8px;border:1px solid #ddd;white-space:nowrap;color:#777;'>$($s.Low)</td>"
  $SummaryHTML += "<td style='padding:8px;border:1px solid #ddd;white-space:nowrap;'><strong>$($s.Total)</strong></td>"
  $SummaryHTML += "</tr>"
}
$SummaryHTML += "</table><br/>"

# ---------------------------------
# 6) Build HTML
# ---------------------------------
$HTMLContent = @'
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<title>Azure Advisor Recommendations</title>
<style>
  body { font-family: Segoe UI, Arial, sans-serif; font-size: 13px; color: #222; }
  h1 { color: #0f2147; }
  h2 { margin-top: 28px; color: #0f2147; }
  h3 { margin-top: 18px; }
  table { border-collapse: collapse; width: 100%; table-layout: fixed; }
  th, td { border: 1px solid #ddd; padding: 8px; vertical-align: top; word-wrap: break-word; }
  tr:nth-child(even) { background:#fafafa; }
  th { background: #0f2147; color: white; }
  .meta { color: #666; font-size: 12px; margin-bottom: 10px; }
  .legend td { padding: 4px 8px; }
  .problem { width: 44%; }         /* wide Problem column */
  .narrow  { width: 12%; }
  .priorityCell { text-align:center; font-weight:600; }
  a { color: #0f62fe; text-decoration: none; }
  a:hover { text-decoration: underline; }

  /* --- Summary table overrides --- */
  table.summary {
    table-layout: auto !important;
    width: auto !important;
    border-collapse: collapse;
    display: inline-table; /* helps some email clients */
  }
  table.summary th, table.summary td {
    white-space: nowrap;
    padding: 8px;
    border: 1px solid #ddd;
  }
</style>
</head>
<body>
'@

$HTMLContent += "<h1>Azure Advisor Recommendations</h1>"
$HTMLContent += "<div class='meta'>Generated: $((Get-Date).ToUniversalTime().ToString('yyyy-MM-dd HH:mm:ss')) UTC</div>"

# Inject summary table here
$HTMLContent += $SummaryHTML

$catOrder = @('Security','Reliability','Performance','Operational Excellence','Cost')
foreach ($cat in $catOrder) {
  $subset = $rowsTable | Where-Object { $_.Category -eq $cat }
  if (-not $subset) { continue }
  $HTMLContent += "<h3>$cat</h3>"

  $HTMLContent += @"
<table data-sort-dir='asc'>
  <colgroup>
    <col class="narrow" />            <!-- Priority (coloured) -->
    <col class="narrow" />            <!-- Subscription -->
    <col class="narrow" />            <!-- Resource Name -->
    <col class="narrow" />            <!-- Resource Type -->
    <col class="problem" />           <!-- Problem (expanded, link) -->
    <col class="narrow" />            <!-- Potential Savings -->
    <col class="narrow" />            <!-- Last Updated -->
  </colgroup>
  <tr>
    <th>Priority</th>
    <th>Subscription</th>
    <th>Resource Name</th>
    <th>Resource Type</th>
    <th>Problem</th>
    <th>Potential Savings</th>
    <th>Last Updated</th>
  </tr>
"@

  foreach ($r in $subset) {
    # Priority cell: colour only; tooltip with text label
    $priorityCell = "<td class='priorityCell' style='background-color:$($r.RAGColor);color:$($r.PriorityTextColor);' title='$(HtmlAttr $r.PriorityLabel)'>&nbsp;</td>"

    # Resource portal link (already computed, includes special-case for subscription scope)
    $hrefRes = $r.ResourcePortalUrl
    $resourceNameCell = if ($hrefRes) {
      "<td><a href=""$(HtmlAttr $hrefRes)"" target=""_blank"">$(Encode-Html $($r.ResourceName))</a></td>"
    } else {
      "<td>$(Encode-Html $($r.ResourceName))</td>"
    }

    $problemHref = $null

    if     ($r.RecDetailsUrl)    { $problemHref = $r.RecDetailsUrl }
    elseif ($r.AdvisorPortalUrl) { $problemHref = $r.AdvisorPortalUrl }
    elseif ($r.RecPortalUrl)     { $problemHref = $r.RecPortalUrl }

    if ($problemHref) {
      $problemCell = "<td><a href=""$(HtmlAttr $problemHref)"" target=""_blank"">$(Encode-Html $($r.Problem))</a></td>"
    }
    else {
      # No usable URL – show plain text instead of bouncing to something random
      $problemCell = "<td>$(Encode-Html $($r.Problem))</td>"
    }



    $HTMLContent += "<tr>"
    $HTMLContent += $priorityCell
    $HTMLContent += "<td>$(Encode-Html $($r.SubscriptionName))</td>"
    $HTMLContent += $resourceNameCell
    $HTMLContent += "<td>$(Encode-Html $($r.ResourceType))</td>"
    $HTMLContent += $problemCell
    $HTMLContent += "<td>$(Encode-Html $([string]$r.PotentialSavings))</td>"
    $HTMLContent += "<td>$(Encode-Html $([string]$r.LastUpdated))</td>"
    $HTMLContent += "</tr>"
  }
  $HTMLContent += "</table>"
}

# Key
$HTMLContent += @'
<h2>Key</h2>
<table style="width:auto;">
  <tr><td>High Impact - requires urgent investigation</td><td style="background-color:#b10035;width:60px;">&nbsp;</td></tr>
  <tr><td>Medium Impact - requires investigation</td><td style="background-color:Orange;width:60px;">&nbsp;</td></tr>
</table>
<p>Regards,</p>
<p>TheAngryTech</p>
<p>AngryTech Inc</p>
</body></html>
'@

$HTMLContent += "</body></html>"

# Save HTML (optional)
if ($OptionalHtmlPath) {
  $dir = Split-Path -Parent $OptionalHtmlPath
  if ($dir -and -not (Test-Path $dir)) { New-Item -ItemType Directory -Path $dir -Force | Out-Null }
  Set-Content -Path $OptionalHtmlPath -Value $HTMLContent -Encoding UTF8
  Write-Host "Saved HTML preview to: $OptionalHtmlPath"
}

# ---------------------------------
# 7) Send Email via SendGrid (SMTP)
# ---------------------------------
$Mail = New-Object System.Net.Mail.MailMessage
$Mail.From = $FromAddress
foreach ($addr in ($ToAddressesCsv -split ',')) { if ($addr.Trim()) { $null = $Mail.To.Add($addr.Trim()) } }
$Mail.Subject = $MailSubject
$Mail.Body = $HTMLContent
$Mail.IsBodyHtml = $true

$Smtp = New-Object Net.Mail.SmtpClient("smtp.sendgrid.net", 587)
$Smtp.Credentials = New-Object System.Net.NetworkCredential("apikey", $Global:SendGrid)
$Smtp.EnableSsl = $true

$Smtp.Send($Mail)
Write-Host "Email sent."