#region Configuration UPDATE THE CONFIG VALUES
$config = @{
    ApiEndpoint = "https://api.powerbi.com/v1.0/myorg/admin/groups?`$filter=endswith(name,'SPList')&`$expand=reports,datasets,users&`$top=5000"
    OutputFile = "PowerBI_SPList_Workspaces_WithDatasources_$(Get-Date -Format 'yyyyMMdd_HHmmss').csv"

    ClientId = "UPDATE THIS"
    Thumbprint = "UPDATE THIS"
    Tenant = "YourTenant" #contoso
}

$listDisplayNameCache = @{}
#endregion

#region Helper Functions
function Get-ListGuidFromEmail {
    param($emailAddress)
    if ($emailAddress -match "SPList-([a-fA-F0-9]{8}-[a-fA-F0-9]{4}-[a-fA-F0-9]{4}-[a-fA-F0-9]{4}-[a-fA-F0-9]{12})") {
        return $matches[1]
    }
    return $null
}

function Get-SharePointListDisplayName {
    param([string]$siteUrl, [string]$listGuid)
    
    try {
        Connect-PnPOnline -Url $siteUrl -ClientId $config.ClientId -Thumbprint $config.Thumbprint -Tenant "$($config.Tenant).onmicrosoft.com" -ErrorAction Stop
        $list = Get-PnPList -Identity $listGuid -ErrorAction Stop
        return $list.Title
    }
    catch {
        Write-Host "      ⚠️  Could not retrieve list name for GUID $listGuid : $($_.Exception.Message)" -ForegroundColor Yellow
        return $null
    }
}

function Get-ListInfoFromWorkspace {
    param($workspace, $siteUrl)
    
    $listGuid = $null
    $listDisplayName = $null
    
    foreach ($user in $workspace.users) {
        $extractedGuid = Get-ListGuidFromEmail -emailAddress $user.emailAddress
        if ($extractedGuid) {
            $listGuid = $extractedGuid
            Write-Host "      Found List GUID: $listGuid" -ForegroundColor Cyan
            
            $cacheKey = "$siteUrl|$listGuid"
            if ($listDisplayNameCache.ContainsKey($cacheKey)) {
                $listDisplayName = $listDisplayNameCache[$cacheKey]
                Write-Host "      Using cached: $listDisplayName" -ForegroundColor Gray
            }
            else {
                Write-Host "      Querying SharePoint..." -ForegroundColor Yellow
                $listDisplayName = Get-SharePointListDisplayName -siteUrl $siteUrl -listGuid $listGuid
                if ($listDisplayName) {
                    Write-Host "      ✅ Display Name: $listDisplayName" -ForegroundColor Green
                    $listDisplayNameCache[$cacheKey] = $listDisplayName
                }
            }
            break
        }
    }
    
    return @{
        Guid = $listGuid
        DisplayName = $listDisplayName
    }
}

function New-WorkspaceResult {
    param(
        $workspace,
        $report = $null,
        $datasetId = $null,
        $datasource = $null,
        $listInfo = $null,
        $errorStatus = $null
    )
    
    $usersList = ($workspace.users | ForEach-Object { "$($_.emailAddress) [$($_.groupUserAccessRight)]" }) -join "; "
    
    return [PSCustomObject]@{
        WorkspaceName = $workspace.name
        WorkspaceId = $workspace.id
        WorkspaceType = $workspace.type
        WorkspaceState = $workspace.state
        ReportName = $report.name ?? "No Reports"
        ReportId = $report.id
        ReportWebUrl = $report.webUrl
        DatasetId = $datasetId
        DatasourceType = $datasource.datasourceType ?? $errorStatus
        DatasourceId = $datasource.datasourceId
        SharePointSiteUrl = $datasource.connectionDetails.url
        SharePointListGuid = $listInfo.Guid
        SharePointListDisplayName = $listInfo.DisplayName
        ConnectionString = $datasource.connectionDetails.url
        GatewayId = $datasource.gatewayId
        UserCount = $workspace.users.Count
        Users = $usersList
        QueryDate = Get-Date
    }
}
#endregion

#region Authentication
Write-Host "=== POWER BI WORKSPACE & DATASOURCE QUERY ===" -ForegroundColor Yellow
Write-Host "API Endpoint: $($config.ApiEndpoint)" -ForegroundColor Cyan
Write-Host "Output File: $($config.OutputFile)" -ForegroundColor Cyan

try {
    Write-Host "`nGetting access token..." -ForegroundColor Yellow
    $token = az account get-access-token --resource https://analysis.windows.net/powerbi/api --query accessToken -o tsv
    if (!$token) { throw "Failed to get access token" }
    Write-Host "✅ Token retrieved" -ForegroundColor Green
}
catch {
    Write-Host "❌ Failed to authenticate: $_" -ForegroundColor Red
    Write-Host "Please run 'az login' first" -ForegroundColor Yellow
    exit 1
}
#endregion

#region Query Power BI Workspaces
try {
    Write-Host "`nQuerying Power BI Admin API..." -ForegroundColor Yellow
    $headers = @{ "Authorization" = "Bearer $token"; "Content-Type" = "application/json" }
    $response = Invoke-RestMethod -Uri $config.ApiEndpoint -Headers $headers -Method Get
    $workspaces = $response.value
    
    Write-Host "✅ Found $($workspaces.Count) workspace(s)" -ForegroundColor Green
    if ($workspaces.Count -eq 0) { exit 0 }
}
catch {
    Write-Host "❌ API call failed: $_" -ForegroundColor Red
    exit 1
}
#endregion

#region Process Workspaces
Write-Host "`n=== PROCESSING WORKSPACES & DATASOURCES ===" -ForegroundColor Yellow
$allResults = @()
$counter = 1

foreach ($workspace in $workspaces) {
    Write-Host "`n--- Workspace $counter of $($workspaces.Count) ---" -ForegroundColor Cyan
    Write-Host "Name: $($workspace.name)" -ForegroundColor White
    
    $datasetIds = $workspace.reports | Where-Object { $_.datasetId } | Select-Object -ExpandProperty datasetId -Unique
    
    if (-not $datasetIds) {
        Write-Host "No datasets found" -ForegroundColor Yellow
        $allResults += New-WorkspaceResult -workspace $workspace
        $counter++
        continue
    }
    
    Write-Host "Datasets: $($datasetIds.Count)" -ForegroundColor Green
    
    foreach ($datasetId in $datasetIds) {
        Write-Host "`n  Dataset: $datasetId" -ForegroundColor Yellow
        
        try {
            $datasourceUrl = "https://api.powerbi.com/v1.0/myorg/admin/datasets/$datasetId/datasources"
            $datasourcesResponse = Invoke-RestMethod -Uri $datasourceUrl -Headers $headers -Method Get
            $datasources = $datasourcesResponse.value
            
            if (-not $datasources -or $datasources.Count -eq 0) {
                Write-Host "  ⚠️  No datasources found" -ForegroundColor Yellow
                continue
            }
            
            Write-Host "  ✅ Found $($datasources.Count) datasource(s)" -ForegroundColor Green
            
            foreach ($datasource in $datasources) {
                Write-Host "    Type: $($datasource.datasourceType)" -ForegroundColor Gray
                
                $siteUrl = $datasource.connectionDetails.url
                $listInfo = Get-ListInfoFromWorkspace -workspace $workspace -siteUrl $siteUrl
                $reportsUsingDataset = $workspace.reports | Where-Object { $_.datasetId -eq $datasetId }
                
                foreach ($report in $reportsUsingDataset) {
                    $allResults += New-WorkspaceResult -workspace $workspace -report $report -datasetId $datasetId -datasource $datasource -listInfo $listInfo
                }
            }
        }
        catch {
            Write-Host "  ⚠️  Error: $($_.Exception.Message)" -ForegroundColor Yellow
            $reportsUsingDataset = $workspace.reports | Where-Object { $_.datasetId -eq $datasetId }
            foreach ($report in $reportsUsingDataset) {
                $allResults += New-WorkspaceResult -workspace $workspace -report $report -datasetId $datasetId -errorStatus "Error retrieving"
            }
        }
        
        Start-Sleep -Milliseconds 100
    }
    
    $counter++
}
#endregion

#region Export and Summary
if ($allResults.Count -gt 0) {
    Write-Host "`n=== EXPORTING RESULTS ===" -ForegroundColor Yellow
    $allResults | Export-Csv -Path $config.OutputFile -NoTypeInformation
    Write-Host "✅ Exported to: $($config.OutputFile)" -ForegroundColor Green
    Write-Host "   Total records: $($allResults.Count)" -ForegroundColor Cyan
}

Write-Host "`n=== SUMMARY ===" -ForegroundColor Yellow
Write-Host "Total Workspaces: $($workspaces.Count)" -ForegroundColor Cyan
Write-Host "Total Records: $($allResults.Count)" -ForegroundColor Cyan

$uniqueSites = $allResults | Where-Object { $_.SharePointSiteUrl } | Select-Object -ExpandProperty SharePointSiteUrl -Unique
if ($uniqueSites) {
    Write-Host "`nUnique SharePoint Sites: $($uniqueSites.Count)" -ForegroundColor Cyan
    $uniqueSites | ForEach-Object { Write-Host "  - $_" -ForegroundColor Gray }
}

Write-Host "`n✅ Query completed successfully!" -ForegroundColor Green
#endregion

#region Configuration
$config = @{
    ApiEndpoint = "https://api.powerbi.com/v1.0/myorg/admin/groups?`$filter=endswith(name,'SPList')&`$expand=reports,datasets,users&`$top=5000"
    OutputFile = "PowerBI_SPList_Workspaces_$(Get-Date -Format 'yyyyMMdd_HHmmss').csv"
}
#endregion

#region Helper Functions
function Get-ListGuidFromEmail {
    param($emailAddress)
    if ($emailAddress -match "SPList-([a-fA-F0-9]{8}-[a-fA-F0-9]{4}-[a-fA-F0-9]{4}-[a-fA-F0-9]{4}-[a-fA-F0-9]{12})") {
        return $matches[1]
    }
    return $null
}

function New-WorkspaceResult {
    param(
        $workspace,
        $report = $null,
        $datasetId = $null,
        $datasource = $null,
        $listGuid = $null
    )
    
    $usersList = ($workspace.users | ForEach-Object { "$($_.emailAddress) [$($_.groupUserAccessRight)]" }) -join "; "
    
    return [PSCustomObject]@{
        WorkspaceName = $workspace.name
        WorkspaceId = $workspace.id
        WorkspaceType = $workspace.type
        WorkspaceState = $workspace.state
        ReportName = $report.name ?? "No Reports"
        ReportId = $report.id
        ReportWebUrl = $report.webUrl
        DatasetId = $datasetId
        DatasourceType = $datasource.datasourceType
        DatasourceId = $datasource.datasourceId
        SharePointSiteUrl = $datasource.connectionDetails.url
        SharePointListGuid = $listGuid
        ConnectionString = $datasource.connectionDetails.url
        GatewayId = $datasource.gatewayId
        UserCount = $workspace.users.Count
        Users = $usersList
        QueryDate = Get-Date
    }
}
#endregion

#region Authentication
Write-Host "=== POWER BI WORKSPACE QUERY (Simple Version) ===" -ForegroundColor Yellow
Write-Host "API Endpoint: $($config.ApiEndpoint)" -ForegroundColor Cyan
Write-Host "Output File: $($config.OutputFile)" -ForegroundColor Cyan

try {
    Write-Host "`nGetting access token..." -ForegroundColor Yellow
    $token = az account get-access-token --resource https://analysis.windows.net/powerbi/api --query accessToken -o tsv
    if (!$token) { throw "Failed to get access token" }
    Write-Host "✅ Token retrieved" -ForegroundColor Green
}
catch {
    Write-Host "❌ Failed to authenticate: $_" -ForegroundColor Red
    Write-Host "Please run 'az login' first" -ForegroundColor Yellow
    exit 1
}
#endregion

#region Query Power BI Workspaces
try {
    Write-Host "`nQuerying Power BI Admin API..." -ForegroundColor Yellow
    $headers = @{ "Authorization" = "Bearer $token"; "Content-Type" = "application/json" }
    $response = Invoke-RestMethod -Uri $config.ApiEndpoint -Headers $headers -Method Get
    $workspaces = $response.value
    
    Write-Host "✅ Found $($workspaces.Count) workspace(s)" -ForegroundColor Green
    if ($workspaces.Count -eq 0) { exit 0 }
}
catch {
    Write-Host "❌ API call failed: $_" -ForegroundColor Red
    exit 1
}
#endregion

#region Process Workspaces
Write-Host "`n=== PROCESSING WORKSPACES ===" -ForegroundColor Yellow
$allResults = @()
$counter = 1

foreach ($workspace in $workspaces) {
    Write-Host "`n--- Workspace $counter of $($workspaces.Count) ---" -ForegroundColor Cyan
    Write-Host "Name: $($workspace.name)" -ForegroundColor White
    
    # Extract list GUID from workspace users (no SharePoint connection needed)
    $listGuid = $null
    foreach ($user in $workspace.users) {
        $extractedGuid = Get-ListGuidFromEmail -emailAddress $user.emailAddress
        if ($extractedGuid) {
            $listGuid = $extractedGuid
            Write-Host "List GUID: $listGuid" -ForegroundColor Cyan
            break
        }
    }
    
    $datasetIds = $workspace.reports | Where-Object { $_.datasetId } | Select-Object -ExpandProperty datasetId -Unique
    
    if (-not $datasetIds) {
        Write-Host "No datasets found" -ForegroundColor Yellow
        $allResults += New-WorkspaceResult -workspace $workspace -listGuid $listGuid
        $counter++
        continue
    }
    
    Write-Host "Datasets: $($datasetIds.Count)" -ForegroundColor Green
    
    foreach ($datasetId in $datasetIds) {
        Write-Host "  Dataset: $datasetId" -ForegroundColor Yellow
        
        try {
            $datasourceUrl = "https://api.powerbi.com/v1.0/myorg/admin/datasets/$datasetId/datasources"
            $datasourcesResponse = Invoke-RestMethod -Uri $datasourceUrl -Headers $headers -Method Get
            $datasources = $datasourcesResponse.value
            
            if (-not $datasources -or $datasources.Count -eq 0) {
                Write-Host "  No datasources found" -ForegroundColor Yellow
                continue
            }
            
            Write-Host "  ✅ Found $($datasources.Count) datasource(s)" -ForegroundColor Green
            
            foreach ($datasource in $datasources) {
                $siteUrl = $datasource.connectionDetails.url
                Write-Host "    Site: $siteUrl" -ForegroundColor Gray
                
                $reportsUsingDataset = $workspace.reports | Where-Object { $_.datasetId -eq $datasetId }
                
                foreach ($report in $reportsUsingDataset) {
                    $allResults += New-WorkspaceResult -workspace $workspace -report $report -datasetId $datasetId -datasource $datasource -listGuid $listGuid
                }
            }
        }
        catch {
            Write-Host "  ⚠️  Error: $($_.Exception.Message)" -ForegroundColor Yellow
            $reportsUsingDataset = $workspace.reports | Where-Object { $_.datasetId -eq $datasetId }
            foreach ($report in $reportsUsingDataset) {
                $allResults += New-WorkspaceResult -workspace $workspace -report $report -datasetId $datasetId -listGuid $listGuid
            }
        }
        
        Start-Sleep -Milliseconds 100
    }
    
    $counter++
}
#endregion

#region Export and Summary
if ($allResults.Count -gt 0) {
    Write-Host "`n=== EXPORTING RESULTS ===" -ForegroundColor Yellow
    $allResults | Export-Csv -Path $config.OutputFile -NoTypeInformation
    Write-Host "✅ Exported to: $($config.OutputFile)" -ForegroundColor Green
    Write-Host "   Location: $(Get-Location)$($config.OutputFile)" -ForegroundColor Green
    Write-Host "   Total records: $($allResults.Count)" -ForegroundColor Cyan
}

Write-Host "`n=== SUMMARY ===" -ForegroundColor Yellow
Write-Host "Total Workspaces: $($workspaces.Count)" -ForegroundColor Cyan
Write-Host "Total Records: $($allResults.Count)" -ForegroundColor Cyan

# Show unique SharePoint sites found
$uniqueSites = $allResults | Where-Object { $_.SharePointSiteUrl } | Select-Object -ExpandProperty SharePointSiteUrl -Unique
if ($uniqueSites) {
    Write-Host "`nUnique SharePoint Sites: $($uniqueSites.Count)" -ForegroundColor Cyan
    $uniqueSites | ForEach-Object { Write-Host "  - $_" -ForegroundColor Gray }
}

# Show unique list GUIDs found
$uniqueListGuids = $allResults | Where-Object { $_.SharePointListGuid } | Select-Object -ExpandProperty SharePointListGuid -Unique
if ($uniqueListGuids) {
    Write-Host "`nUnique SharePoint List GUIDs: $($uniqueListGuids.Count)" -ForegroundColor Cyan
    $uniqueListGuids | ForEach-Object { Write-Host "  - $_" -ForegroundColor Gray }
}

Write-Host "`n✅ Query completed successfully!" -ForegroundColor Green
#endregion