#Requires -Modules Microsoft.Graph.Authentication, ImportExcel

<#
.SYNOPSIS
    Audits unique SharePoint permissions at library, folder, and file level across all sites.

.DESCRIPTION
    Reads site URLs from a CSV export (SharePoint Admin Center) and retrieves all
    permissions via the Microsoft Graph API. Results are streamed directly to CSV
    after each site to minimize memory usage. An Excel report is generated at the end.

    =========================================================================
    REQUIRED AZURE APP REGISTRATION PERMISSIONS
    =========================================================================
    Create an App Registration in Azure Portal (Entra ID) with the following
    APPLICATION permissions (not Delegated) and grant Admin Consent for all:

    Microsoft Graph:
      - Sites.Read.All       : Read all SharePoint site collections and their contents
      - Files.Read.All       : Read all files in all site document libraries
      - User.Read.All        : Resolve user IDs to display names / UPNs
      - Group.Read.All       : Resolve group IDs to display names

    How to set up:
      1. Go to Azure Portal > Microsoft Entra ID > App Registrations > New Registration
      2. Give it a name (e.g. "SharePoint Permissions Audit")
      3. Go to API Permissions > Add a Permission > Microsoft Graph > Application Permissions
      4. Add: Sites.Read.All, Files.Read.All, User.Read.All, Group.Read.All
      5. Click "Grant Admin Consent for <tenant>"
      6. Go to Certificates & Secrets > New Client Secret
      7. Copy the SECRET VALUE (not the Secret ID) immediately after creation
      8. Note down the Application (client) ID and the Directory (tenant) ID
    =========================================================================

.PARAMETER TenantId
    Azure AD / Entra ID Tenant ID (found on the App Registration overview page).

.PARAMETER ClientId
    Application (Client) ID of the App Registration.

.PARAMETER ClientSecret
    Client Secret VALUE (not the Secret ID) of the App Registration.

.PARAMETER SitesCsvPath
    Path to the CSV file exported from the SharePoint Admin Center.
    The CSV must contain a column named 'URL' with the site URLs.
    Export: SharePoint Admin Center > Active Sites > Export to CSV

.PARAMETER OutputPath
    Folder where output files (CSV, Excel, log) will be saved. Defaults to current directory.

.PARAMETER ExcludedSites
    Optional array of site URLs to skip during the scan.

.PARAMETER IncludeFileLevel
    Switch to enable file-level permission scanning in addition to libraries and folders.
    Warning: this significantly increases scan time on large tenants.

.EXAMPLE
    # Scan all sites using the exported CSV
    .\Get-SharePointPermissions.ps1 `
        -TenantId  "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx" `
        -ClientId  "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx" `
        -ClientSecret "your-secret-value-here" `
        -SitesCsvPath "C:\scripts\Sites.csv" `
        -OutputPath "C:\scripts\Reports"

.EXAMPLE
    # Include file-level permissions and skip a specific site
    .\Get-SharePointPermissions.ps1 `
        -TenantId  "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx" `
        -ClientId  "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx" `
        -ClientSecret "your-secret-value-here" `
        -SitesCsvPath "C:\scripts\Sites.csv" `
        -OutputPath "C:\scripts\Reports" `
        -IncludeFileLevel `
        -ExcludedSites @("https://contoso.sharepoint.com/sites/HR")

.NOTES
    Monitor progress: Get-Content "C:\scripts\Reports\SharePoint_Permissions_*.csv" -Wait -Tail 20
    Do NOT open the CSV in Excel while the script is running - this locks the file.
    Use Notepad or Notepad++ to view the CSV during the scan.
#>

param(
    [Parameter(Mandatory)]
    [string]$TenantId,

    [Parameter(Mandatory)]
    [string]$ClientId,

    [Parameter(Mandatory)]
    [string]$ClientSecret,

    [Parameter(Mandatory)]
    [string]$SitesCsvPath,

    [string]$OutputPath = (Get-Location).Path,

    [string[]]$ExcludedSites = @(),

    [switch]$IncludeFileLevel
)

$ErrorActionPreference = 'Stop'

#region ── Module check ──────────────────────────────────────────────────────
# Ensure required PowerShell modules are installed and loaded.
# Microsoft.Graph.Authentication : Connect to Graph API and make REST calls
# ImportExcel                    : Generate formatted Excel reports without Excel installed

foreach ($mod in @('Microsoft.Graph.Authentication', 'ImportExcel')) {
    if (-not (Get-Module -ListAvailable -Name $mod)) {
        Write-Host "[SETUP] Installing module '$mod'..." -ForegroundColor Yellow
        Install-Module $mod -Scope CurrentUser -Force -AllowClobber
    }
    Import-Module $mod -ErrorAction Stop
}

#endregion

#region ── Output file paths ─────────────────────────────────────────────────

$timestamp = Get-Date -Format 'yyyyMMdd_HHmmss'
$csvOut    = Join-Path $OutputPath "SharePoint_Permissions_$timestamp.csv"
$xlsxOut   = Join-Path $OutputPath "SharePoint_Permissions_$timestamp.xlsx"
$logOut    = Join-Path $OutputPath "SharePoint_Permissions_$timestamp.log"

if (-not (Test-Path $OutputPath)) {
    New-Item -ItemType Directory -Path $OutputPath | Out-Null
}

#endregion

#region ── Logging ───────────────────────────────────────────────────────────

function Write-Log {
    param([string]$Message, [string]$Level = 'INFO')
    $entry = "[{0}] [{1}] {2}" -f (Get-Date -Format 'yyyy-MM-dd HH:mm:ss'), $Level, $Message
    Add-Content -Path $logOut -Value $entry
    switch ($Level) {
        'ERROR'   { Write-Host $entry -ForegroundColor Red }
        'WARNING' { Write-Host $entry -ForegroundColor Yellow }
        'SUCCESS' { Write-Host $entry -ForegroundColor Green }
        default   { Write-Host $entry }
    }
}

#endregion

#region ── Authentication ────────────────────────────────────────────────────
# Uses the OAuth2 client credentials flow directly via Invoke-RestMethod.
# This avoids MSAL.PS compatibility issues with PowerShell strict mode.

Write-Log "Script started."

try {
    $tokenBody = @{
        grant_type    = 'client_credentials'
        client_id     = $ClientId
        client_secret = $ClientSecret
        scope         = 'https://graph.microsoft.com/.default'
    }
    $tokenResponse = Invoke-RestMethod `
        -Uri "https://login.microsoftonline.com/$TenantId/oauth2/v2.0/token" `
        -Method POST -Body $tokenBody -ContentType 'application/x-www-form-urlencoded'

    Connect-MgGraph `
        -AccessToken ($tokenResponse.access_token | ConvertTo-SecureString -AsPlainText -Force) `
        -NoWelcome

    Write-Log "Authentication successful." -Level 'SUCCESS'
} catch {
    Write-Log "Authentication failed: $_" -Level 'ERROR'
    exit 1
}

#endregion

#region ── Graph REST helper with automatic pagination ───────────────────────
# Graph API returns results in pages (default 200 items per page).
# This function follows @odata.nextLink until all results are retrieved.

function Invoke-GraphGet {
    param([string]$Uri)
    $results = [System.Collections.Generic.List[object]]::new()
    $nextUri = $Uri
    while ($nextUri) {
        $resp    = Invoke-MgGraphRequest -Uri $nextUri -Method GET -OutputType PSObject
        $nextUri = $null
        if ($resp.PSObject.Properties.Name -contains '@odata.nextLink') {
            $nextUri = $resp.'@odata.nextLink'
        }
        if ($resp.PSObject.Properties.Name -contains 'value') {
            foreach ($item in $resp.value) { $results.Add($item) }
        } elseif ($null -ne $resp) {
            $results.Add($resp)
        }
    }
    return $results
}

#endregion

#region ── Recursive drive item retrieval ────────────────────────────────────
# Recursively fetches all folders (and optionally files) within a drive.
# Recurses into subfolders to build a complete tree.

function Get-AllDriveItems {
    param([string]$DriveId, [string]$ItemId = 'root')
    $all = [System.Collections.Generic.List[object]]::new()
    try {
        $children = Invoke-GraphGet -Uri "https://graph.microsoft.com/v1.0/drives/$DriveId/items/$ItemId/children"
        foreach ($child in $children) {
            $all.Add($child)
            if ($child.PSObject.Properties.Name -contains 'folder') {
                $sub = Get-AllDriveItems -DriveId $DriveId -ItemId $child.id
                foreach ($s in $sub) { $all.Add($s) }
            }
        }
    } catch {
        Write-Log "    Error retrieving children of '$ItemId': $_" -Level 'WARNING'
    }
    return $all
}

#endregion

#region ── Principal name lookup with caching ────────────────────────────────
# Resolves Azure AD user/group IDs to readable display names.
# Results are cached to avoid duplicate API calls for the same principal.

$principalCache = @{}

function Get-PrincipalName {
    param([string]$Id, [string]$Type)
    if (-not $Id) { return 'Unknown' }
    $key = "$Type|$Id"
    if ($principalCache.ContainsKey($key)) { return $principalCache[$key] }
    $name = "$Type`: $Id"
    try {
        if ($Type -eq 'user') {
            $u    = Invoke-MgGraphRequest -Uri "https://graph.microsoft.com/v1.0/users/${Id}?`$select=displayName,userPrincipalName" -Method GET -OutputType PSObject
            $name = "$($u.displayName) ($($u.userPrincipalName))"
        } elseif ($Type -eq 'group') {
            $g    = Invoke-MgGraphRequest -Uri "https://graph.microsoft.com/v1.0/groups/${Id}?`$select=displayName" -Method GET -OutputType PSObject
            $name = $g.displayName
        }
    } catch { }
    $principalCache[$key] = $name
    return $name
}

#endregion

#region ── Permission record builder ─────────────────────────────────────────
# Retrieves permissions for a single drive item and returns structured records.
# ForceInclude: always return records even if only inherited permissions exist (used for library root).
# Without ForceInclude: only returns records if explicit (non-inherited) grants are found.

function Get-ItemPermissionRecords {
    param(
        [string]$DriveId,
        [string]$ItemId,
        [string]$ItemName,
        [string]$ItemPath,
        [string]$ItemType,    # 'Library', 'Folder', or 'File'
        [string]$SiteName,
        [string]$SiteUrl,
        [string]$LibraryName,
        [switch]$ForceInclude
    )

    $records = [System.Collections.Generic.List[object]]::new()

    try {
        $perms = Invoke-GraphGet -Uri "https://graph.microsoft.com/v1.0/drives/$DriveId/items/$ItemId/permissions"
    } catch {
        Write-Log "    Error retrieving permissions for '$ItemPath': $_" -Level 'WARNING'
        return $records
    }

    if (-not $perms -or $perms.Count -eq 0) { return $records }

    # Only include items with explicit direct grants (not just sharing links), unless ForceInclude
    $hasExplicitGrants = $perms | Where-Object {
        ($_.PSObject.Properties.Name -contains 'grantedToV2' -and $_.grantedToV2) -or
        ($_.PSObject.Properties.Name -contains 'grantedTo'   -and $_.grantedTo)
    }
    if (-not $ForceInclude -and -not $hasExplicitGrants) { return $records }

    foreach ($perm in $perms) {
        $roles     = if ($perm.PSObject.Properties.Name -contains 'roles' -and $perm.roles) { $perm.roles -join ', ' } else { 'read' }
        $grantedTo = 'Unknown'
        $linkType  = ''

        # Resolve who the permission is granted to (Graph API v2 properties take priority)
        if ($perm.PSObject.Properties.Name -contains 'grantedToV2' -and $perm.grantedToV2) {
            $g = $perm.grantedToV2
            if     ($g.PSObject.Properties.Name -contains 'user'      -and $g.user)      { $grantedTo = Get-PrincipalName -Id $g.user.id      -Type 'user' }
            elseif ($g.PSObject.Properties.Name -contains 'group'     -and $g.group)     { $grantedTo = Get-PrincipalName -Id $g.group.id     -Type 'group' }
            elseif ($g.PSObject.Properties.Name -contains 'siteUser'  -and $g.siteUser)  { $grantedTo = $g.siteUser.displayName }
            elseif ($g.PSObject.Properties.Name -contains 'siteGroup' -and $g.siteGroup) { $grantedTo = $g.siteGroup.displayName }
        } elseif ($perm.PSObject.Properties.Name -contains 'grantedToIdentitiesV2' -and $perm.grantedToIdentitiesV2) {
            # Multiple recipients (e.g. sharing link used by multiple people)
            $names = foreach ($identity in $perm.grantedToIdentitiesV2) {
                if     ($identity.PSObject.Properties.Name -contains 'user'      -and $identity.user)      { Get-PrincipalName -Id $identity.user.id  -Type 'user' }
                elseif ($identity.PSObject.Properties.Name -contains 'group'     -and $identity.group)     { Get-PrincipalName -Id $identity.group.id -Type 'group' }
                elseif ($identity.PSObject.Properties.Name -contains 'siteUser'  -and $identity.siteUser)  { $identity.siteUser.displayName }
                else   { 'Unknown' }
            }
            $grantedTo = ($names | Where-Object { $_ }) -join '; '
        } elseif ($perm.PSObject.Properties.Name -contains 'grantedTo' -and $perm.grantedTo) {
            # Fallback: older grantedTo property
            $g = $perm.grantedTo
            if     ($g.PSObject.Properties.Name -contains 'user'  -and $g.user)  { $grantedTo = "$($g.user.displayName) ($($g.user.email))" }
            elseif ($g.PSObject.Properties.Name -contains 'group' -and $g.group) { $grantedTo = $g.group.displayName }
        }

        # Detect sharing links (anonymous, organization-wide, or specific people links)
        if ($perm.PSObject.Properties.Name -contains 'link' -and $perm.link) {
            $linkType  = "SharingLink ($($perm.link.type), $($perm.link.scope))"
            if ($grantedTo -eq 'Unknown') { $grantedTo = $linkType }
        }

        $records.Add([PSCustomObject]@{
            SiteName    = $SiteName
            SiteUrl     = $SiteUrl
            Library     = $LibraryName
            ItemType    = $ItemType
            ItemName    = $ItemName
            ItemPath    = $ItemPath
            GrantedTo   = $grantedTo
            Permissions = $roles
            LinkType    = $linkType
            ScannedAt   = (Get-Date -Format 'yyyy-MM-dd HH:mm')
        })
    }

    return $records
}

#endregion

#region ── Load site list from CSV ───────────────────────────────────────────
# Reads site URLs from the SharePoint Admin Center export CSV.
# Export location: SharePoint Admin Center > Active Sites > Export

Write-Log "Loading sites from CSV: $SitesCsvPath"

if (-not (Test-Path $SitesCsvPath)) {
    Write-Log "CSV file not found: $SitesCsvPath" -Level 'ERROR'
    exit 1
}

$csvSites  = Import-Csv -Path $SitesCsvPath
$urlColumn = $csvSites[0].PSObject.Properties.Name | Where-Object { $_ -match '^url$' } | Select-Object -First 1

if (-not $urlColumn) {
    Write-Log "No 'URL' column found in CSV. Available columns: $($csvSites[0].PSObject.Properties.Name -join ', ')" -Level 'ERROR'
    exit 1
}

$siteUrls = $csvSites.$urlColumn | Where-Object {
    $_ -and $_ -notmatch '/personal/' -and $_ -notin $ExcludedSites
}

Write-Log "Sites loaded from CSV: $($siteUrls.Count)" -Level 'SUCCESS'

#endregion

#region ── Main scan loop ────────────────────────────────────────────────────
# Streams permission records directly to CSV after each site to keep memory usage low.
# The CSV file is written incrementally - do NOT open it in Excel during the scan.

$csvHeaders = 'SiteName;SiteUrl;Library;ItemType;ItemName;ItemPath;GrantedTo;Permissions;LinkType;ScannedAt'
Set-Content -Path $csvOut -Value $csvHeaders -Encoding UTF8
$recordCount = 0

function Write-RecordToCsv {
    # Appends one or more permission records to the CSV file immediately.
    param([object[]]$Records)
    foreach ($rec in $Records) {
        $line = '"{0}";"{1}";"{2}";"{3}";"{4}";"{5}";"{6}";"{7}";"{8}";"{9}"' -f `
            $rec.SiteName, $rec.SiteUrl, $rec.Library, $rec.ItemType, `
            $rec.ItemName, $rec.ItemPath, $rec.GrantedTo, $rec.Permissions, `
            $rec.LinkType, $rec.ScannedAt
        Add-Content -Path $csvOut -Value $line -Encoding UTF8
        $script:recordCount++
    }
}

$siteIndex = 0
$total     = $siteUrls.Count

foreach ($siteUrl in $siteUrls) {
    $siteIndex++
    $pct = [math]::Round(($siteIndex / $total) * 100)
    Write-Progress -Activity "SharePoint Permissions Audit" `
        -Status "[$siteIndex/$total] $siteUrl  |  Total records: $recordCount" `
        -PercentComplete $pct

    # Resolve site URL to Graph site ID using the hostname:/path format
    try {
        $uri      = [Uri]$siteUrl
        $hostPart = $uri.Host
        $pathPart = $uri.AbsolutePath.TrimStart('/')
        if ($pathPart) {
            $siteObj = Invoke-MgGraphRequest -Uri "https://graph.microsoft.com/v1.0/sites/${hostPart}:/${pathPart}" -Method GET -OutputType PSObject
        } else {
            $siteObj = Invoke-MgGraphRequest -Uri "https://graph.microsoft.com/v1.0/sites/${hostPart}" -Method GET -OutputType PSObject
        }
        $siteId   = $siteObj.id
        $siteName = if ($siteObj.PSObject.Properties.Name -contains 'displayName' -and $siteObj.displayName) { $siteObj.displayName } else { $siteObj.name }
    } catch {
        Write-Log "[$siteIndex/$total] Failed to resolve site ID for '$siteUrl': $_" -Level 'WARNING'
        continue
    }

    Write-Log "[$siteIndex/$total] $siteName"

    # Get all document libraries for this site (excludes system lists)
    try {
        $drives = Invoke-GraphGet -Uri "https://graph.microsoft.com/v1.0/sites/$siteId/drives"
        $drives = $drives | Where-Object { $_.driveType -eq 'documentLibrary' }
    } catch {
        Write-Log "  Error retrieving document libraries: $_" -Level 'WARNING'
        continue
    }

    $recordsBefore = $recordCount

    foreach ($drive in $drives) {
        $libName = $drive.name
        $driveId = $drive.id
        Write-Log "  Library: $libName"

        # Scan library root permissions (always included)
        try {
            $root    = Invoke-MgGraphRequest -Uri "https://graph.microsoft.com/v1.0/drives/$driveId/root" -Method GET -OutputType PSObject
            $records = Get-ItemPermissionRecords -DriveId $driveId -ItemId $root.id `
                -ItemName $libName -ItemPath "/" -ItemType 'Library' `
                -SiteName $siteName -SiteUrl $siteUrl -LibraryName $libName -ForceInclude
            Write-RecordToCsv -Records $records
        } catch {
            Write-Log "  Error scanning library root '$libName': $_" -Level 'WARNING'
        }

        # Scan all folders (and files if -IncludeFileLevel is set)
        # Only items with unique (broken) permissions are included
        try {
            $items = Get-AllDriveItems -DriveId $driveId
            foreach ($item in $items) {
                $isFolder = $item.PSObject.Properties.Name -contains 'folder'
                $isFile   = $item.PSObject.Properties.Name -contains 'file'
                if (-not $isFolder -and -not ($IncludeFileLevel -and $isFile)) { continue }

                $itemType   = if ($isFolder) { 'Folder' } else { 'File' }
                $parentPath = ''
                if ($item.PSObject.Properties.Name -contains 'parentReference' -and
                    $item.parentReference.PSObject.Properties.Name -contains 'path') {
                    $parentPath = $item.parentReference.path -replace '.*?/root:', ''
                }
                $cleanPath = "$parentPath/$($item.name)"

                $records = Get-ItemPermissionRecords -DriveId $driveId -ItemId $item.id `
                    -ItemName $item.name -ItemPath $cleanPath -ItemType $itemType `
                    -SiteName $siteName -SiteUrl $siteUrl -LibraryName $libName
                Write-RecordToCsv -Records $records
            }
        } catch {
            Write-Log "  Error scanning items in '$libName': $_" -Level 'WARNING'
        }
    }

    $added = $recordCount - $recordsBefore
    if ($added -gt 0) {
        Write-Log "  -> $added new records written (total: $recordCount)" -Level 'SUCCESS'
    }
}

Write-Progress -Activity "SharePoint Permissions Audit" -Completed
Write-Log "Scan complete. $recordCount permission records found." -Level 'SUCCESS'

#endregion

#region ── Build Excel report from CSV ───────────────────────────────────────
# Reads the completed CSV and generates a multi-sheet Excel report.
# Sheet 1 - All Permissions  : full dataset
# Sheet 2 - Summary by Site  : record counts grouped per site
# Sheet 3 - Top 20 Risk Items: items with the most unique permission grants
# Sheet 4 - Sharing Links    : all items shared via external/anonymous links

Write-Log "Building Excel report from CSV..."

if ($recordCount -gt 0) {
    $allRecords = Import-Csv -Path $csvOut -Delimiter ';' -Encoding UTF8

    # Sheet 1: Full permission list
    $allRecords | Export-Excel -Path $xlsxOut -WorksheetName 'All Permissions' `
        -TableName 'TblAllPermissions' -TableStyle Medium9 -AutoSize -FreezeTopRow -BoldTopRow

    # Sheet 2: Summary grouped by site
    $allRecords | Group-Object SiteName | Select-Object `
        @{N='Site';      E={$_.Name}},
        @{N='Total';     E={$_.Count}},
        @{N='Libraries'; E={($_.Group.Library | Sort-Object -Unique).Count}},
        @{N='Folders';   E={($_.Group | Where-Object ItemType -eq 'Folder').Count}},
        @{N='Files';     E={($_.Group | Where-Object ItemType -eq 'File').Count}} |
    Sort-Object Total -Descending |
    Export-Excel -Path $xlsxOut -WorksheetName 'Summary by Site' `
        -TableName 'TblSummary' -TableStyle Medium2 -AutoSize -FreezeTopRow -BoldTopRow -Append

    # Sheet 3: Top 20 items with most unique grants (highest permission fragmentation risk)
    $allRecords | Group-Object ItemPath | Select-Object `
        @{N='Site';           E={($_.Group | Select-Object -First 1).SiteName}},
        @{N='Library';        E={($_.Group | Select-Object -First 1).Library}},
        @{N='Type';           E={($_.Group | Select-Object -First 1).ItemType}},
        @{N='Item';           E={($_.Group | Select-Object -First 1).ItemName}},
        @{N='Path';           E={($_.Group | Select-Object -First 1).ItemPath}},
        @{N='Unique Grants';  E={$_.Count}} |
    Sort-Object 'Unique Grants' -Descending | Select-Object -First 20 |
    Export-Excel -Path $xlsxOut -WorksheetName 'Top 20 Risk Items' `
        -TableName 'TblTopRisk' -TableStyle Medium6 -AutoSize -FreezeTopRow -BoldTopRow -Append

    # Sheet 4: Sharing links (potential external exposure)
    $links = @($allRecords | Where-Object { $_.LinkType -ne '' })
    if ($links.Count -gt 0) {
        $links | Export-Excel -Path $xlsxOut -WorksheetName 'Sharing Links' `
            -TableName 'TblSharingLinks' -TableStyle Medium3 -AutoSize -FreezeTopRow -BoldTopRow -Append
    }

    Write-Log "Excel report saved: $xlsxOut" -Level 'SUCCESS'
} else {
    Write-Log "No records found - Excel report not created." -Level 'WARNING'
}

#endregion

Disconnect-MgGraph | Out-Null

Write-Host "`n========================================" -ForegroundColor Cyan
Write-Host " Scan complete!" -ForegroundColor Cyan
Write-Host "========================================" -ForegroundColor Cyan
Write-Host " Records : $recordCount"
Write-Host " CSV     : $csvOut"
Write-Host " Excel   : $xlsxOut"
Write-Host " Log     : $logOut"
Write-Host "========================================`n" -ForegroundColor Cyan