<#PSScriptInfo

.VERSION 1.9.0

.GUID 6c861af7-d12e-4ea2-b5dc-56fee16e0107

.AUTHOR Nicola Suter

.TAGS ConditionalAccess, AzureAD, Identity

.PROJECTURI https://git.cqre.net/vibecoding/CAExporter

.ICONURI https://raw.githubusercontent.com/microsoftgraph/g-raph/master/g-raph.png

.DESCRIPTION This script documents Azure AD Conditional Access Policies using the latest Microsoft.Graph PowerShell module.

.SYNOPSIS This script retrieves all Conditional Access Policies and translates Azure AD Object IDs to display names for users, groups, directory roles, locations...

.EXAMPLE
    Connect-MgGraph -Scopes "Application.Read.All", "Group.Read.All", "Policy.Read.All", "RoleManagement.Read.Directory", "User.Read.All"
    & .\Invoke-ConditionalAccessDocumentation.ps1
    Generates the documentation and exports the csv to the script directory.
.NOTES
    Author:           Nicola Suter
    Creation Date:    31.01.2022
    Updated:          15.09.2025
#>

param(
    [switch]$ExportExcel,
    [string]$ExcelPath
)
# NOTE: Module requirements are handled programmatically below to allow auto-install.
$RequiredGraphVersion = '2.30.0'
$RequiredGraphModules = @(
    'Microsoft.Graph.Authentication',
    'Microsoft.Graph.Applications',
    'Microsoft.Graph.Identity.SignIns',
    'Microsoft.Graph.Groups',
    'Microsoft.Graph.DirectoryObjects',
    'Microsoft.Graph.Identity.DirectoryManagement',
    'Microsoft.Graph.Identity.Governance'
)

function Ensure-NuGetProvider {
    try {
        if (-not (Get-PackageProvider -ListAvailable -Name 'NuGet' -ErrorAction SilentlyContinue)) {
            Install-PackageProvider -Name 'NuGet' -Force -Scope CurrentUser | Out-Null
        }
    } catch { Write-Warning "NuGet provider installation failed: $($_.Exception.Message)" }
}

function Ensure-PSGalleryTrusted {
    try {
        $repo = Get-PSRepository -Name 'PSGallery' -ErrorAction Stop
        if ($repo.InstallationPolicy -ne 'Trusted') {
            Set-PSRepository -Name 'PSGallery' -InstallationPolicy Trusted -ErrorAction Stop
        }
    } catch { Write-Warning "Failed to set PSGallery trusted: $($_.Exception.Message)" }
}

function Ensure-Module {
    param(
        [Parameter(Mandatory)] [string] $Name,
        [Parameter(Mandatory)] [string] $Version
    )
    $hasVersion = Get-Module -ListAvailable -Name $Name -ErrorAction SilentlyContinue |
                  Where-Object { $_.Version -eq [version]$Version }
    if (-not $hasVersion) {
        Write-Verbose "Installing $Name $Version for current user..."
        Ensure-NuGetProvider
        Ensure-PSGalleryTrusted
        try {
            Install-Module -Name $Name -RequiredVersion $Version -Scope CurrentUser -Force -ErrorAction Stop
        } catch {
            Write-Warning "Exact version $Version not available for $Name. Installing latest available version."
            Install-Module -Name $Name -Scope CurrentUser -Force -ErrorAction Stop
        }
    }
    Import-Module -Name $Name -ErrorAction Stop | Out-Null
}


foreach ($m in $RequiredGraphModules) { Ensure-Module -Name $m -Version $RequiredGraphVersion }

# If Excel export requested, ensure ImportExcel module is loaded/installed
if ($ExportExcel) {
    try {
        Import-Module ImportExcel -ErrorAction Stop | Out-Null
    } catch {
        Write-Host 'Installing ImportExcel module for Excel export...' -ForegroundColor Cyan
        Ensure-PSGalleryTrusted
        Install-Module -Name ImportExcel -Scope CurrentUser -Force -ErrorAction Stop
        Import-Module ImportExcel -ErrorAction Stop | Out-Null
    }

    # --- Helpers for older ImportExcel versions ---
    if (-not (Get-Command New-WorksheetName -ErrorAction SilentlyContinue)) {
        function New-WorksheetName {
            param([string]$Name)
            if (-not $Name) { return 'Sheet' }
            $san = ($Name -replace '[\\/:*?\[\]]','_')
            if ($san.Length -gt 31) { $san = $san.Substring(0,31) }
            if ($san -match '^\d+$') { $san = "_$san" }
            return $san
        }
    }

    if (-not (Get-Command Set-Cell -ErrorAction SilentlyContinue)) {
        function Set-Cell {
            param(
                [Parameter(Mandatory)][string]$Path,
                [Parameter(Mandatory)][string]$WorksheetName,
                [Parameter(Mandatory)][int]$Row,
                [Parameter(Mandatory)][int]$Column,
                [string]$Value,
                [string]$Hyperlink,
                [switch]$Formula
            )
            # Fallback using EPPlus via ImportExcel helper cmdlets
            $pkg = Open-ExcelPackage -Path $Path
            try {
                $ws = $pkg.Workbook.Worksheets[$WorksheetName]
                if (-not $ws) { $ws = Add-WorkSheet -ExcelPackage $pkg -WorksheetName $WorksheetName }
                $cell = $ws.Cells[$Row,$Column]
                if ($Formula) {
                    # Ensure the previous text value doesn't force the cell to stay as text
                    try { $cell.Clear() } catch { }
                    if ($Value -and $Value.StartsWith('=')) { $cell.Formula = $Value.Substring(1) } else { $cell.Formula = $Value }
                } else {
                    $cell.Value = $Value
                }
                if ($Hyperlink) { $cell.Hyperlink = $Hyperlink }
            }
            finally {
                try { $pkg.Workbook.CalcMode = [OfficeOpenXml.ExcelCalcMode]::Automatic } catch { }
                try { $ws.Calculate() } catch { }
                try { $pkg.Save() } catch { }
                try { $pkg.Dispose() } catch { }
            }
        }
    }

    function Try-AddConditionalFormatting {
        param(
            [string]$Path,
            [string]$WorksheetName,
            [string]$Range,
            [string]$RuleType,
            [string]$ConditionValue,
            [string]$ForegroundColor,
            [string]$BackgroundColor
        )
        try {
            Add-ConditionalFormatting -Path $Path -WorksheetName $WorksheetName -Range $Range -RuleType $RuleType -ConditionValue $ConditionValue -ForegroundColor $ForegroundColor -BackgroundColor $BackgroundColor
        } catch {
            try {
                $pkg = Open-ExcelPackage -Path $Path
                $ws  = $pkg.Workbook.Worksheets[$WorksheetName]
                if ($ws) {
                    $cf = $ws.ConditionalFormatting.AddContainsText($Range)
                    $cf.Text = $ConditionValue
                    if ($BackgroundColor) { $cf.Style.Fill.BackgroundColor.SetColor([System.Drawing.Color]::$BackgroundColor) }
                }
                $pkg.Save(); $pkg.Dispose()
            } catch { }
        }
    }

    function Try-SetColumnWidth {
        param(
            [string]$Path,
            [string]$WorksheetName,
            [int]$Column,
            [double]$Width
        )
        try {
            Set-Column -Path $Path -WorksheetName $WorksheetName -Column $Column -Width $Width
        } catch {
            try {
                $pkg = Open-ExcelPackage -Path $Path
                $ws  = $pkg.Workbook.Worksheets[$WorksheetName]
                if ($ws) { $ws.Column($Column).Width = $Width }
                $pkg.Save(); $pkg.Dispose()
            } catch { }
        }
    }

    function Try-SetFormat {
        param(
            [Parameter(Mandatory)][string]$Path,
            [Parameter(Mandatory)][string]$WorksheetName,
            [Parameter(Mandatory)][string]$Range,
            [switch]$Bold,
            [int]$FontSize,
            [string]$BackgroundColor
        )
        # Attempt ImportExcel first (varies across versions)
        $ok = $false
        try {
            Set-Format -Path $Path -WorksheetName $WorksheetName -Range $Range -Bold:$Bold -FontSize $FontSize -BackgroundColor $BackgroundColor
            $ok = $true
        } catch {}
        if (-not $ok) {
            try {
                # Alternate parameter names
                Set-Format -Address $Range -WorkSheetname $WorksheetName -Bold:$Bold -FontSize $FontSize -BackgroundColor $BackgroundColor -PassThru | Export-Excel -Path $Path -WorksheetName $WorksheetName -Append
                $ok = $true
            } catch {}
        }
        if (-not $ok) {
            # Fallback to EPPlus directly
            try {
                $pkg = Open-ExcelPackage -Path $Path
                $ws  = $pkg.Workbook.Worksheets[$WorksheetName]
                if ($ws) {
                    $addr = [OfficeOpenXml.ExcelAddress]::new($Range)
                    $cells = $ws.Cells[$addr.Address]
                    if ($Bold) { $cells.Style.Font.Bold = $true }
                    if ($FontSize -gt 0) { $cells.Style.Font.Size = $FontSize }
                    if ($BackgroundColor) { $cells.Style.Fill.PatternType = [OfficeOpenXml.Style.ExcelFillStyle]::Solid; $cells.Style.Fill.BackgroundColor.SetColor([System.Drawing.Color]::$BackgroundColor) }
                }
                $pkg.Save(); $pkg.Dispose()
            } catch { }
        }
    }

    function Add-PolicyNamesNamedRange {
        param([Parameter(Mandatory)][string]$Path)
        $pkg = Open-ExcelPackage -Path $Path
        try {
            $ws = $pkg.Workbook.Worksheets['Master']
            if (-not $ws) { return }
            # Find the column index of 'Name' in header row 1
            $lastCol = $ws.Dimension.End.Column
            $nameCol = $null
            for ($c=1; $c -le $lastCol; $c++) {
                if (($ws.Cells[1,$c].Text) -eq 'Name') { $nameCol = $c; break }
            }
            if ($null -eq $nameCol) { return }
            $lastRow = $ws.Dimension.End.Row
            if ($lastRow -lt 2) { return }
            $rangeAddress = [OfficeOpenXml.ExcelAddress]::new(2, $nameCol, $lastRow, $nameCol)
            # Create or update a workbook-level named range 'PolicyNames'
            $existing = $pkg.Workbook.Names['PolicyNames']
            if ($existing) { $pkg.Workbook.Names.Remove($existing) | Out-Null }
            [void]$pkg.Workbook.Names.Add('PolicyNames', $ws.Cells[$rangeAddress.Address])
            $pkg.Save()
        } finally { try { $pkg.Dispose() } catch { } }
    }

    function Add-PolicyNameValidation {
        param(
            [Parameter(Mandatory)][string]$Path,
            [Parameter(Mandatory)][string]$WorksheetName
        )
        $pkg = Open-ExcelPackage -Path $Path
        try {
            $ws = $pkg.Workbook.Worksheets[$WorksheetName]
            if (-not $ws) { return }
            # Add a dropdown in B1 pointing to the 'PolicyNames' named range
            $dv = $ws.DataValidations.AddListValidation('B1')
            $dv.Formula.ExcelFormula = 'PolicyNames'
            $dv.ShowErrorMessage = $true
            $dv.ErrorTitle = 'Invalid selection'
            $dv.Error = 'Please pick a policy name from the list.'
            $pkg.Save()
        } finally { try { $pkg.Dispose() } catch { } }
    }
}

# --- Ensure connection to Microsoft Graph with required scopes ---
$RequiredMgScopes = @(
    'Application.Read.All',
    'Group.Read.All',
    'Policy.Read.All',
    'RoleManagement.Read.Directory',
    'User.Read.All',
    'NetworkAccessPolicy.Read.All',   # optional: for Global Secure Access profile names
    'Agreement.Read.All'              # optional: for Terms of Use display names
)

function Ensure-MgConnection {
    try { $ctx = Get-MgContext -ErrorAction Stop } catch { $ctx = $null }

    $needConnect = $true
    if ($ctx) {
        $currentScopes = @($ctx.Scopes)
        if ($currentScopes -and ($RequiredMgScopes | Where-Object { $currentScopes -contains $_ }).Count -eq $RequiredMgScopes.Count) {
            $needConnect = $false
        }
    }

    if ($needConnect) {
        Write-Host 'Connecting to Microsoft Graph...' -ForegroundColor Cyan
        Connect-MgGraph -Scopes $RequiredMgScopes -NoWelcome
    }
}

Ensure-MgConnection

function Test-Guid {
    <#
    .SYNOPSIS
    Validates a given input string and checks string is a valid GUID
    .DESCRIPTION
    Validates a given input string and checks string is a valid GUID by using the .NET method Guid.TryParse
    .EXAMPLE
    Test-Guid -InputObject "3363e9e1-00d8-45a1-9c0c-b93ee03f8c13"
    .NOTES
    Uses .NET method [guid]::TryParse()
    #>
    [Cmdletbinding()]
    [OutputType([bool])]
    param
    (
        [Parameter(Mandatory = $true, Position = 0, ValueFromPipelineByPropertyName = $true)]
        [AllowEmptyString()]
        [string]$InputObject
    )
    process {
        return [guid]::TryParse($InputObject, $([ref][guid]::Empty))
    }
}

function Resolve-MgObject {
    <#
    .SYNOPSIS
    Resolve a Microsoft Graph item to display name
    .DESCRIPTION
    Resolves a Microsoft Graph Directory Object to a Display Name when possible
    .EXAMPLE

    .NOTES

    #>
    [Cmdletbinding()]
    [OutputType([string])]
    param
    (
        [Parameter(Mandatory = $true, Position = 0, ValueFromPipelineByPropertyName = $true)]
        [AllowEmptyString()]
        [string]$InputObject
    )
    process {
        if (Test-Guid -InputObject $InputObject) {
            try {
                # use hashtable as cache to limit API calls
                if ($displayNameCache.ContainsKey($InputObject)) {
                    Write-Debug "Cached display name for `"$InputObject`""
                    return $displayNameCache[$InputObject]
                } else {
                    $directoryObject = Get-MgDirectoryObject -DirectoryObjectId $InputObject -ErrorAction Stop
                    $displayName = $directoryObject.AdditionalProperties['displayName']
                    $displayNameCache[$InputObject] = $displayName
                    return $displayName
                }
            } catch {
                Write-Warning "Unable to resolve directory object with ID $InputObject, might have been deleted!"
            }
        }
        return $InputObject
    }
}

# Add GetOrDefault to hashtables
$etd = @{
    TypeName   = 'System.Collections.Hashtable'
    MemberType = 'Scriptmethod'
    MemberName = 'GetOrDefault'
    Value      = {
        param(
            $key,
            $defaultValue
        )

        if (-not [string]::IsNullOrEmpty($key)) {
            if ($this.ContainsKey($key)) {
                if ($this[$key].DisplayName) {
                    return $this[$key].DisplayName
                } else {
                    return $this[$key]
                }
            } else {
                return $defaultValue
            } 
        }
    }
}
Update-TypeData @etd -Force

Write-Progress -PercentComplete -1 -Activity 'Fetching conditional access policies and related data from Graph API'

try {
    if (-not (Get-MgContext)) { Write-Warning "Not connected to Microsoft Graph. Run: Connect-MgGraph -Scopes \"Application.Read.All\",\"Group.Read.All\",\"Policy.Read.All\",\"RoleManagement.Read.Directory\",\"User.Read.All\",\"NetworkAccessPolicy.Read.All\",\"Agreement.Read.All\"" }
} catch { }

# Get Conditional Access Policies
$conditionalAccessPolicies = Get-MgIdentityConditionalAccessPolicy -ExpandProperty '*' -All -ErrorAction Stop

# Get Conditional Access Named / Trusted Locations
$namedLocations = Get-MgIdentityConditionalAccessNamedLocation -All -ErrorAction Stop | Group-Object -Property Id -AsHashTable
if (-not $namedLocations) { $namedLocations = @{} }

# Get Azure AD Directory Role Templates (in latest module, use Get-MgDirectoryRoleTemplate)
try {
    $directoryRoleTemplates = Get-MgDirectoryRoleTemplate -All -ErrorAction Stop | Group-Object -Property Id -AsHashTable
} catch {
    Write-Warning "Directory role templates could not be retrieved (module missing or insufficient permissions). Role names will not be resolved."
    $directoryRoleTemplates = @{}
}

 # Service Principals
$servicePrincipals = Get-MgServicePrincipal -All -ErrorAction Stop | Group-Object -Property AppId -AsHashTable

# Terms of Use Agreements (for resolving TermsOfUse IDs)
try {
    $termsOfUseAgreements = Get-MgIdentityGovernanceTermsOfUseAgreement -All -ErrorAction Stop | Group-Object -Property Id -AsHashTable
} catch {
    Write-Warning "Terms of Use agreements could not be retrieved or permission missing (Agreement.Read.All)."
    $termsOfUseAgreements = @{}
}

# GSA network filtering (no direct beta endpoint in new modules, use Invoke-MgGraphRequest as fallback)
try {
    $networkFilteringProfiles = Invoke-MgGraphRequest -Uri 'https://graph.microsoft.com/beta/networkAccess/filteringProfiles' -Method GET -OutputType PSObject -ErrorAction Stop |
        Select-Object -ExpandProperty value |
        Group-Object -Property id -AsHashTable
} catch {
    Write-Warning "Global Secure Access filtering profiles not available or insufficient permission. Skipping."
    $networkFilteringProfiles = @{}
}

# Init report 
$documentation = [System.Collections.Generic.List[Object]]::new()
# Cache for resolved display names
$displayNameCache = @{}

# Process all Conditional Access Policies
foreach ($policy in $conditionalAccessPolicies) {

    # Display some progress (based on policy count)
    $currentIndex = $conditionalAccessPolicies.indexOf($policy) + 1

    $progress = @{
        Activity         = 'Generating Conditional Access Documentation...'
        PercentComplete  = [Decimal]::Divide($currentIndex, $conditionalAccessPolicies.Count) * 100
        CurrentOperation = "Processing Policy `"$($policy.DisplayName)`""
    }
    if ($currentIndex -eq $conditionalAccessPolicies.Count) { $progress.Add('Completed', $true) }

    Write-Progress @progress

    Write-Output "Processing policy `"$($policy.DisplayName)`""

    try {
        # Resolve object IDs of included users
        $includeUsers = @($policy.Conditions?.Users?.IncludeUsers) | ForEach-Object { Resolve-MgObject -InputObject $_ }
        # Resolve object IDs of excluded users
        $excludeUsers = @($policy.Conditions?.Users?.ExcludeUsers) | ForEach-Object { Resolve-MgObject -InputObject $_ }
        # Resolve object IDs of included groups
        $includeGroups = @($policy.Conditions?.Users?.IncludeGroups) | ForEach-Object { Resolve-MgObject -InputObject $_ }
        # Resolve object IDs of excluded groups
        $excludeGroups = @($policy.Conditions?.Users?.ExcludeGroups) | ForEach-Object { Resolve-MgObject -InputObject $_ }
        # Resolve object IDs of included roles
        $includeRoles  = @($policy.Conditions?.Users?.IncludeRoles) | ForEach-Object { $directoryRoleTemplates.GetOrDefault($_, $_) }
        # Resolve object IDs of excluded roles
        $excludeRoles  = @($policy.Conditions?.Users?.ExcludeRoles) | ForEach-Object { $directoryRoleTemplates.GetOrDefault($_, $_) }

        # Resolve object IDs of included apps
        $includeApps = @($policy.Conditions?.Applications?.IncludeApplications) | ForEach-Object { $servicePrincipals.GetOrDefault($_, $_) }
        # Resolve object IDs of excluded apps
        $excludeApps = @($policy.Conditions?.Applications?.ExcludeApplications) | ForEach-Object { $servicePrincipals.GetOrDefault($_, $_) }

        $includeServicePrincipals = [System.Collections.Generic.List[Object]]::new()
        $excludeServicePrincipals = [System.Collections.Generic.List[Object]]::new()

        @($policy.Conditions?.ClientApplications?.IncludeServicePrincipals) | ForEach-Object { $includeServicePrincipals.Add($servicePrincipals.GetOrDefault($_, $_)) }
        @($policy.Conditions?.ClientApplications?.ExcludeServicePrincipals) | ForEach-Object { $excludeServicePrincipals.Add($servicePrincipals.GetOrDefault($_, $_)) }

        $includeAuthenticationContext = [System.Collections.Generic.List[Object]]::new()
        @($policy.Conditions?.Applications?.IncludeAuthenticationContextClassReferences) | ForEach-Object {
            try {
                $context = Get-MgIdentityConditionalAccessAuthenticationContextClassReference -Filter "Id eq '$PSItem'" -ErrorAction Stop
                if ($context.DisplayName) { $includeAuthenticationContext.Add($context.DisplayName) }
            } catch {
                $includeAuthenticationContext.Add($PSItem)
            }
        }

        # Resolve object IDs of included/excluded locations
        $includeLocations = @($policy.Conditions?.Locations?.IncludeLocations) | ForEach-Object { $namedLocations.GetOrDefault($_, $_) }
        $excludeLocations = @($policy.Conditions?.Locations?.ExcludeLocations) | ForEach-Object { $namedLocations.GetOrDefault($_, $_) }

        # GSA web filtering profile (if available)
        $webFilteringProfile = $null
        try {
            $gsaProp = $policy.SessionControls?.AdditionalProperties?['globalSecureAccessFilteringProfile']
            if ($gsaProp) {
                $profileId = $gsaProp['profileId']
                if ($profileId -and $networkFilteringProfiles.ContainsKey($profileId)) {
                    $webFilteringProfile = $networkFilteringProfiles[$profileId].name
                }
            }
        } catch { }

        # delimiter for arrays in csv report
        $separator = "; "

        # Grant controls
        $grantBuiltIn = @()
        if ($policy.GrantControls?.BuiltInControls) { $grantBuiltIn += $policy.GrantControls.BuiltInControls }
        if ($policy.GrantControls?.TermsOfUse)       { $grantBuiltIn += 'termsOfUse' }
        if ($policy.GrantControls?.AuthenticationStrength) { $grantBuiltIn += 'authenticationStrength' }

        $grantControls = $grantBuiltIn | Where-Object { $_ -ne 'authenticationStrength' }
        $authStrengthName = $policy.GrantControls?.AuthenticationStrength?.DisplayName
        if ($authStrengthName) { $grantControls += 'authenticationStrength' }

        $authStrengthAllowed = @($policy.GrantControls?.AuthenticationStrength?.AllowedCombinations) -join $separator

        # Session controls / misc
        $signInFrequency = $null
        if ($policy.SessionControls?.SignInFrequency?.Value) {
            $signInFrequency = "{0} {1}" -f $policy.SessionControls.SignInFrequency.Value, $policy.SessionControls.SignInFrequency.Type
        }

        $secureSignInSession = $null
        $ss = $policy.SessionControls?.AdditionalProperties?['secureSignInSession']
        if ($ss) { $secureSignInSession = $ss.isEnabled }

        # Device states include/exclude (if present)
        $includeDeviceStates = @($policy.Conditions?.DeviceStates?.IncludeStates)
        $excludeDeviceStates = @($policy.Conditions?.DeviceStates?.ExcludeStates)

        # Include guests/external users (if present)
        $includeGuestsOrExternalUserTypes   = $policy.Conditions?.Users?.IncludeGuestsOrExternalUsers?.guestOrExternalUserTypes
        $includeGuestsOrExternalUserTenants = @($policy.Conditions?.Users?.IncludeGuestsOrExternalUsers?.externalTenants?.AdditionalProperties?['members'])

        # Authentication flows (future-proof; may be empty)
        $authenticationFlows = @($policy.Conditions?.AuthenticationFlows)

        # Applications additional properties (future-proof)
        $applicationsAdditional = $null
        if ($policy.Conditions?.Applications?.AdditionalProperties) {
            $applicationsAdditional = ($policy.Conditions.Applications.AdditionalProperties | ConvertTo-Json -Depth 6 -Compress)
        }

        # Conditions.additionalProperties (future-proof)
        $conditionsAdditional = $null
        if ($policy.Conditions?.AdditionalProperties) {
            $conditionsAdditional = ($policy.Conditions.AdditionalProperties | ConvertTo-Json -Depth 6 -Compress)
        }

        # GrantControls Terms of Use display names
        $termsOfUseNames = $null
        if ($policy.GrantControls?.TermsOfUse) {
            $termsOfUseNames = ($policy.GrantControls.TermsOfUse | ForEach-Object { $termsOfUseAgreements.GetOrDefault($_, $_) }) -join $separator
        }

        # GrantControls additional properties
        $grantControlsAdditional = $null
        if ($policy.GrantControls?.AdditionalProperties) {
            $grantControlsAdditional = ($policy.GrantControls.AdditionalProperties | ConvertTo-Json -Depth 6 -Compress)
        }

        # Session controls additional details
        $cloudAppSecurityMode = $policy.SessionControls?.CloudAppSecurity?.Mode
        $sessionAdditional = $null
        if ($policy.SessionControls?.AdditionalProperties) {
            $sessionAdditional = ($policy.SessionControls.AdditionalProperties | ConvertTo-Json -Depth 6 -Compress)
        }

        # construct entry for report
        $documentation.Add(
            [PSCustomObject]@{
                Name                                      = $policy.DisplayName
                # Conditions
                IncludeUsers                              = ($includeUsers -join $separator)
                IncludeGroups                             = ($includeGroups -join $separator)
                IncludeRoles                              = ($includeRoles  -join $separator)

                ExcludeUsers                              = ($excludeUsers -join $separator)
                ExcludeGuestOrExternalUserTypes           = $policy.Conditions?.Users?.ExcludeGuestsOrExternalUsers?.guestOrExternalUserTypes
                ExcludeGuestOrExternalUserTenants         = (@($policy.Conditions?.Users?.ExcludeGuestsOrExternalUsers?.externalTenants?.AdditionalProperties?['members']) -join $separator)

                ExcludeGroups                             = ($excludeGroups -join $separator)
                ExcludeRoles                              = ($excludeRoles  -join $separator)

                IncludeApps                               = ($includeApps -join $separator)
                ExcludeApps                               = ($excludeApps -join $separator)

                ApplicationFilterMode                     = $policy.Conditions?.Applications?.ApplicationFilter?.mode
                ApplicationFilterRule                     = $policy.Conditions?.Applications?.ApplicationFilter?.rule

                IncludeAuthenticationContext              = ($includeAuthenticationContext -join $separator)
                IncludeUserActions                        = (@($policy.Conditions?.Applications?.IncludeUserActions) -join $separator)
                ClientAppTypes                            = (@($policy.Conditions?.ClientAppTypes) -join $separator)

                IncludePlatforms                          = (@($policy.Conditions?.Platforms?.IncludePlatforms) -join $separator)
                ExcludePlatforms                          = (@($policy.Conditions?.Platforms?.ExcludePlatforms) -join $separator)

                IncludeLocations                          = ($includeLocations -join $separator)
                ExcludeLocations                          = ($excludeLocations -join $separator)

                DeviceFilterMode                          = $policy.Conditions?.Devices?.DeviceFilter?.Mode
                DeviceFilterRule                          = $policy.Conditions?.Devices?.DeviceFilter?.Rule

                SignInRiskLevels                          = (@($policy.Conditions?.SignInRiskLevels) -join $separator)
                UserRiskLevels                            = (@($policy.Conditions?.UserRiskLevels) -join $separator)
                ServicePrincipalRiskLevels                = (@($policy.Conditions?.servicePrincipalRiskLevels) -join $separator)

                # Additional/expanded condition fields
                IncludeDeviceStates                        = (@($includeDeviceStates) -join $separator)
                ExcludeDeviceStates                        = (@($excludeDeviceStates) -join $separator)
                IncludeGuestsOrExternalUserTypes           = $includeGuestsOrExternalUserTypes
                IncludeGuestOrExternalUserTenants          = (@($includeGuestsOrExternalUserTenants) -join $separator)
                AuthenticationFlows                        = (@($authenticationFlows) -join $separator)
                ApplicationsAdditional                     = $applicationsAdditional
                ConditionsAdditional                       = $conditionsAdditional

                # Workload Identity Protection
                IncludeServicePrincipals                  = ($includeServicePrincipals -join $separator)
                ExcludeServicePrincipals                  = ($excludeServicePrincipals -join $separator)
                ServicePrincipalFilterMode                = $policy.Conditions?.ClientApplications?.ServicePrincipalFilter?.mode
                ServicePrincipalFilter                    = $policy.Conditions?.ClientApplications?.ServicePrincipalFilter?.rule

                # Grantcontrols
                GrantControls                             = ($grantControls -join $separator)
                GrantControlsOperator                     = $policy.GrantControls?.Operator
                AuthenticationStrength                    = $authStrengthName
                AuthenticationStrengthAllowedCombinations = $authStrengthAllowed
                TermsOfUseNames                            = $termsOfUseNames
                GrantControlsAdditional                    = $grantControlsAdditional

                # Session controls
                ApplicationEnforcedRestrictions           = $policy.SessionControls?.ApplicationEnforcedRestrictions?.IsEnabled
                CloudAppSecurity                          = $policy.SessionControls?.CloudAppSecurity?.IsEnabled
                CloudAppSecurityMode                       = $cloudAppSecurityMode
                DisableResilienceDefaults                 = $policy.SessionControls?.DisableResilienceDefaults
                PersistentBrowser                         = $policy.SessionControls?.PersistentBrowser?.Mode
                SignInFrequency                           = $signInFrequency
                SecureSignInSession                       = $secureSignInSession # Require Token Protection
                GlobalSecureAccessFilteringProfile        = $webFilteringProfile
                SessionControlsAdditional                  = $sessionAdditional

                # State
                State                                     = $policy.State
            }
        )
    } catch {
        Write-Error $PSItem
    }
}


# Build export path (script directory)
$exportPath = Join-Path $PSScriptRoot 'ConditionalAccessDocumentation.csv'


# Export report as csv (use semicolon delimiter to play nice with Excel in many locales)
$CsvDelimiter = ';'
$exportParams = @{ Path = $exportPath; NoTypeInformation = $true; Delimiter = $CsvDelimiter; Encoding = 'utf8BOM' }
try {
    # UseQuotes is available in PowerShell 7.3+
    $exportParams['UseQuotes'] = 'AsNeeded'
} catch { }

try {
    $documentation | Export-Csv @exportParams
} catch {
    Write-Warning "Export-Csv with UTF-8 BOM failed on this PowerShell version. Retrying with UTF-8 (no BOM)."
    $exportParams['Encoding'] = 'utf8'
    $documentation | Export-Csv @exportParams
}

Write-Output "Exported Documentation to '$($exportPath)'"

if ($ExportExcel) {
    Write-Host 'Building Excel workbook...' -ForegroundColor Cyan
    if (-not $ExcelPath) {
        $ExcelPath = Join-Path $PSScriptRoot 'ConditionalAccessDocumentation.xlsx'
    }

    if (Test-Path $ExcelPath) { Remove-Item $ExcelPath -Force }

    # 0) MASTER: full matrix of all fields (like the CSV)
    $documentation | Export-Excel -Path $ExcelPath -WorksheetName 'Master' -TableName 'Master' -TableStyle 'Medium9' -ClearSheet -FreezeTopRow -AutoFilter

    # 1) SUMMARY: compact view (Name + State)
    $summary = $documentation | Select-Object Name, State
    $summary | Export-Excel -Path $ExcelPath -WorksheetName 'Summary' -TableName 'Policies' -TableStyle 'Medium6' -ClearSheet -FreezeTopRow -AutoFilter

    Try-AddConditionalFormatting -Path $ExcelPath -WorksheetName 'Summary' -Range 'B2:B1048576' -RuleType ContainsText -ConditionValue 'enabled'    -ForegroundColor 'Black' -BackgroundColor 'LightGreen'
    Try-AddConditionalFormatting -Path $ExcelPath -WorksheetName 'Summary' -Range 'B2:B1048576' -RuleType ContainsText -ConditionValue 'disabled'   -ForegroundColor 'Black' -BackgroundColor 'LightGray'
    Try-AddConditionalFormatting -Path $ExcelPath -WorksheetName 'Summary' -Range 'B2:B1048576' -RuleType ContainsText -ConditionValue 'reportOnly' -ForegroundColor 'Black' -BackgroundColor 'Khaki'

    # Make a named range of policy names for dropdowns on detail sheets
    Add-PolicyNamesNamedRange -Path $ExcelPath

    # 2) DETAIL SHEETS: readable, two-column layout with formulas referencing Master
    # Define the fields to show and their labels (grouped by section)
    $sections = @(
        @{ Title = 'General';   Fields = @(
            @{ Label = 'Policy name';     Col = 'Name' },
            @{ Label = 'State';           Col = 'State' }
        )},
        @{ Title = 'Users and groups'; Fields = @(
            @{ Label = 'Include users';   Col = 'IncludeUsers' },
            @{ Label = 'Include groups';  Col = 'IncludeGroups' },
            @{ Label = 'Include roles';   Col = 'IncludeRoles' },
            @{ Label = 'Exclude users';   Col = 'ExcludeUsers' },
            @{ Label = 'Exclude groups';  Col = 'ExcludeGroups' },
            @{ Label = 'Exclude roles';   Col = 'ExcludeRoles' }
        )},
        @{ Title = 'Applications'; Fields = @(
            @{ Label = 'Include apps';    Col = 'IncludeApps' },
            @{ Label = 'Exclude apps';    Col = 'ExcludeApps' },
            @{ Label = 'Client app types';Col = 'ClientAppTypes' },
            @{ Label = 'AuthN context';   Col = 'IncludeAuthenticationContext' }
        )},
        @{ Title = 'Conditions'; Fields = @(
            @{ Label = 'Platforms include'; Col = 'IncludePlatforms' },
            @{ Label = 'Platforms exclude'; Col = 'ExcludePlatforms' },
            @{ Label = 'Locations include'; Col = 'IncludeLocations' },
            @{ Label = 'Locations exclude'; Col = 'ExcludeLocations' },
            @{ Label = 'Device filter mode'; Col = 'DeviceFilterMode' },
            @{ Label = 'Device filter rule'; Col = 'DeviceFilterRule' },
            @{ Label = 'Sign-in risk';   Col = 'SignInRiskLevels' },
            @{ Label = 'User risk';      Col = 'UserRiskLevels' }
        )},
        @{ Title = 'Grant'; Fields = @(
            @{ Label = 'Operator';        Col = 'GrantControlsOperator' },
            @{ Label = 'Controls';        Col = 'GrantControls' },
            @{ Label = 'Auth strength';   Col = 'AuthenticationStrength' },
            @{ Label = 'Allowed combos';  Col = 'AuthenticationStrengthAllowedCombinations' },
            @{ Label = 'Terms of Use';    Col = 'TermsOfUseNames' }
        )},
        @{ Title = 'Session'; Fields = @(
            @{ Label = 'App enforced restrictions'; Col = 'ApplicationEnforcedRestrictions' },
            @{ Label = 'Defender for Cloud Apps';   Col = 'CloudAppSecurity' },
            @{ Label = 'CAS mode';                  Col = 'CloudAppSecurityMode' },
            @{ Label = 'Persistent browser';        Col = 'PersistentBrowser' },
            @{ Label = 'Sign-in frequency';         Col = 'SignInFrequency' },
            @{ Label = 'Secure sign-in session';    Col = 'SecureSignInSession' },
            @{ Label = 'GSA filtering profile';     Col = 'GlobalSecureAccessFilteringProfile' }
        )}
    )

    # Build a column index for Master to generate formulas (from in-memory objects)
    $first = $documentation | Select-Object -First 1
    $masterHeaders = @()
    if ($first) { $masterHeaders = @($first.PSObject.Properties.Name) }
    $headerIndex = @{}
    for ($i=0; $i -lt $masterHeaders.Count; $i++) { $headerIndex[$masterHeaders[$i]] = $i+1 }

    foreach ($item in $documentation) {
        $sheetName = New-WorksheetName -Name $item.Name

        # Create a new blank sheet
        Export-Excel -Path $ExcelPath -WorksheetName $sheetName -ClearSheet | Out-Null

        # Title and key identity cell (Policy name in B1) — set to this policy's name
        Set-Cell -Path $ExcelPath -WorksheetName $sheetName -Row 1 -Column 1 -Value 'Policy'
        Set-Cell -Path $ExcelPath -WorksheetName $sheetName -Row 1 -Column 2 -Value $item.Name
        Try-SetFormat -Path $ExcelPath -WorksheetName $sheetName -Range 'A1:B1' -Bold -FontSize 14

        # Write detail rows explicitly into A/B without creating a table
        $rowPtr = 3
        foreach ($section in $sections) {
            # Section header row
            Set-Cell -Path $ExcelPath -WorksheetName $sheetName -Row $rowPtr -Column 1 -Value $section.Title
            Try-SetFormat -Path $ExcelPath -WorksheetName $sheetName -Range ("A$rowPtr:B$rowPtr") -Bold -BackgroundColor 'LightGray'
            $rowPtr++

            foreach ($f in $section.Fields) {
                $label   = $f.Label
                $colName = $f.Col
                Set-Cell -Path $ExcelPath -WorksheetName $sheetName -Row $rowPtr -Column 1 -Value $label
                if ($headerIndex.ContainsKey($colName)) {
                    $formula = "=INDEX(Master[$colName], MATCH(`$B`$1, Master[Name], 0))"
                    Set-Cell -Path $ExcelPath -WorksheetName $sheetName -Row $rowPtr -Column 2 -Value $formula -Formula
                } else {
                    Set-Cell -Path $ExcelPath -WorksheetName $sheetName -Row $rowPtr -Column 2 -Value ''
                }
                $rowPtr++
            }

            # blank line between sections
            $rowPtr++
        }

        # Tidy up widths
        Try-SetColumnWidth -Path $ExcelPath -WorksheetName $sheetName -Column 1 -Width 34
        Try-SetColumnWidth -Path $ExcelPath -WorksheetName $sheetName -Column 2 -Width 80
        Try-AddConditionalFormatting -Path $ExcelPath -WorksheetName $sheetName -Range 'B1:B200' -RuleType ContainsText -ConditionValue 'enabled'    -ForegroundColor 'Black' -BackgroundColor 'LightGreen'
        Try-AddConditionalFormatting -Path $ExcelPath -WorksheetName $sheetName -Range 'B1:B200' -RuleType ContainsText -ConditionValue 'disabled'   -ForegroundColor 'Black' -BackgroundColor 'LightGray'
        Try-AddConditionalFormatting -Path $ExcelPath -WorksheetName $sheetName -Range 'B1:B200' -RuleType ContainsText -ConditionValue 'reportOnly' -ForegroundColor 'Black' -BackgroundColor 'Khaki'
    }

    # Hyperlinks from Summary -> policy sheets
    $r = 2
    foreach ($name in $documentation | Select-Object -ExpandProperty Name) {
        $ws = New-WorksheetName -Name $name
        Set-Cell -Path $ExcelPath -WorksheetName 'Summary' -Row $r -Column 1 -Value $name -Hyperlink ("#'" + $ws + "'!A1")
        $r++
    }

    Write-Output "Exported Excel workbook to '$ExcelPath'"
}