# Set Error Action Preference for the script
$ErrorActionPreference = 'Stop' # Stop on terminating errors

try {
    # Import admin credentials
    Write-Host "Loading configuration..." -ForegroundColor Gray
    $serverAddress = Get-Content -Path ".\DefaultServer.txt" -ErrorAction SilentlyContinue
    if (-not $serverAddress) { throw "Could not read server address from .\DefaultServer.txt" }
    $credential = Import-Clixml -Path .\AdminCredential.xml -ErrorAction SilentlyContinue
    if (-not $credential) { throw "Could not import credentials from .\AdminCredential.xml" }

    # Establish session with Exchange Server
    Write-Host "Connecting to Exchange server $serverAddress..." -ForegroundColor Yellow
    $session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri "http://$serverAddress/PowerShell/" -Authentication Kerberos -Credential $credential # ErrorAction Stop is implied by preference
    Import-PSSession $session -DisableNameChecking -WarningAction SilentlyContinue # ErrorAction Stop is implied by preference
    Write-Host "Successfully connected." -ForegroundColor Green

    # Define output file path in the user's Downloads folder
    $outputFile = Join-Path -Path $env:USERPROFILE -ChildPath "Downloads\ExchangeDatabaseMailboxListReport_$(Get-Date -Format 'yyyyMMdd_HHmm').html"
    Write-Host "Report will be saved to: $outputFile" -ForegroundColor Gray

    # --- Data Collection Variables ---
    $totalUserMailboxes = 0
    $totalSharedMailboxes = 0
    $databaseStats = @() # Array to hold stats for the column chart

    # --- HTML Report Setup ---
    Write-Host "Setting up HTML structure and styles..." -ForegroundColor Gray
    $head = @"
<style>
body { font-family: Arial, sans-serif; font-size: 10pt; }
h1 { color: #336699; font-size: 18pt; text-align: center; }
h2 { color: #336699; font-size: 14pt; margin-top: 20px; border-bottom: 1px solid #cccccc; text-align: center; padding-bottom: 5px; }
h3 { color: #5588bb; font-size: 12pt; margin-top: 15px; text-align: center; }
table { border-collapse: collapse; width: 95%; margin: 10px auto 15px auto; } /* Wider table */
th, td { border: 1px solid #cccccc; padding: 5px; text-align: left; font-size: 9pt; vertical-align: top; } /* Smaller font, top align */
th { background-color: #f2f2f2; font-weight: bold; }
/* Align specific columns using inline styles now, but keep CSS as fallback/complement */
table th:nth-child(3), table td:nth-child(3) { text-align: right; white-space: nowrap; } /* Size */
table th:nth-child(4), table td:nth-child(4) { text-align: right; white-space: nowrap; } /* Last Logon */
.dbinfo { margin-bottom: 10px; text-align: center; padding-left: 0; }
.note { font-style: italic; color: #666666; margin-bottom: 20px; text-align: center; }
.nodata { color: #888888; font-style: italic; text-align:center; }

.charts-wrapper { display: flex; flex-wrap: wrap; justify-content: space-around; align-items: stretch; width: 95%; margin: 20px auto; }
.chart-container { border: 1px solid #ddd; border-radius: 5px; padding: 15px; box-sizing: border-box; display: flex; flex-direction: column; align-items: center; }
.charts-wrapper .chart-container { width: 48%; margin-bottom: 20px; }
.chart-container canvas { max-width: 100%; height: auto !important; }

@media (max-width: 900px) { /* Adjusted breakpoint */
    .charts-wrapper { flex-direction: column; align-items: center; }
    .charts-wrapper .chart-container { width: 90%; }
    table { width: 98%; } /* Even wider on small screens */
    th, td { font-size: 8pt; } /* Even smaller font */
}
</style>
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
"@

    # Start building the HTML body content
    $scriptStartTime = Get-Date
    $bodyContent = "<h1>Exchange Database Mailbox Report</h1>"
    # Use provided context
    $bodyContent += "<p class='note'>Generated on: $($scriptStartTime.ToString('yyyy-MM-dd HH:mm:ss')) (AEST - Bexley, NSW)</p>"
    $bodyContent += "<p class='note'>Note: System mailboxes such as 'Discovery Search Mailbox' have been excluded. Size and Last Logon data retrieval can take significant time.</p>"

    # Get database size and mailboxes
    Write-Host "Retrieving database information..." -ForegroundColor Yellow
    $databases = Get-MailboxDatabase -Status | Select-Object Name, DatabaseSize
    Write-Host "Found $($databases.Count) databases." -ForegroundColor Green

    # --- Process Each Database ---
    foreach ($db in $databases) {
        $dbStartTime = Get-Date
        Write-Host "Processing database: $($db.Name)" -ForegroundColor Yellow
        $dbName = $db.Name
        $dbSize = $db.DatabaseSize -replace '\s*\(.*\)', '' # Clean up size string

        # Add database details to the HTML body
        $bodyContent += "<h2>Database: $dbName</h2>"
        $bodyContent += "<p class='dbinfo'>Total Size: $dbSize</p>"

        # Retrieve base mailboxes first
        Write-Host "  Retrieving base mailbox list for $dbName..." -ForegroundColor Gray
        $baseMailboxes = Get-Mailbox -Database $dbName -ResultSize Unlimited | Where-Object {$_.DisplayName -ne "Discovery Search Mailbox"} | Select-Object Identity, DisplayName, PrimarySmtpAddress, RecipientTypeDetails
        Write-Host "  Found $($baseMailboxes.Count) mailboxes to query for statistics." -ForegroundColor Gray

        # --- Get Detailed Stats (Slow Part) ---
        $detailedMailboxData = @() # Array to hold combined data for this DB
        Write-Host "  Retrieving statistics for $($baseMailboxes.Count) mailboxes (this may take considerable time)..." -ForegroundColor Cyan
        $progressCount = 0
        foreach ($mb in $baseMailboxes) {
            $progressCount++
            Write-Progress -Activity "Getting Mailbox Stats for $($db.Name)" -Status "Processing mailbox $progressCount of $($baseMailboxes.Count): $($mb.DisplayName)" -PercentComplete (($progressCount / $baseMailboxes.Count) * 100)

            $mbSize = "N/A"
            $mbLastLogon = $null

            try {
                $stats = Get-MailboxStatistics -Identity $mb.Identity -ErrorAction Stop | Select-Object TotalItemSize, LastLogonTime
                if ($stats.TotalItemSize -ne $null) {
                    $mbSize = "{0:N2}" -f $stats.TotalItemSize.ToMB()
                }
                $mbLastLogon = $stats.LastLogonTime
            } catch {
                Write-Warning "Could not retrieve statistics for mailbox $($mb.DisplayName) ($($mb.Identity)). Error: $($_.Exception.Message)"
                $mbSize = "Error"
                $mbLastLogon = $null
            }

            $detailedMailboxData += [PSCustomObject]@{
                DisplayName          = $mb.DisplayName
                PrimarySmtpAddress   = $mb.PrimarySmtpAddress
                RecipientTypeDetails = $mb.RecipientTypeDetails
                MailboxSizeMB        = $mbSize
                LastLogonTime        = $mbLastLogon
            }
        } # End foreach mailbox statistics
        Write-Progress -Activity "Getting Mailbox Stats for $($db.Name)" -Completed
        Write-Host "  Finished retrieving statistics for $dbName." -ForegroundColor Green

        # Filter and Sort the detailed data
        Write-Host "  Filtering and sorting mailbox data..." -ForegroundColor Gray
        $userMailboxes = $detailedMailboxData | Where-Object {$_.RecipientTypeDetails -eq "UserMailbox"} | Sort-Object DisplayName
        $sharedMailboxes = $detailedMailboxData | Where-Object {$_.RecipientTypeDetails -eq "SharedMailbox"} | Sort-Object DisplayName

        # Accumulate total counts for charts
        $currentUserCount = $userMailboxes.Count
        $currentSharedCount = $sharedMailboxes.Count
        $totalUserMailboxes += $currentUserCount
        $totalSharedMailboxes += $currentSharedCount
        $databaseStats += [PSCustomObject]@{
            DatabaseName = $dbName
            UserCount    = $currentUserCount
            SharedCount  = $currentSharedCount
        }


        # --- Add User Mailboxes Table (Manual HTML Generation) ---
        # START BLOCK TO REPLACE ConvertTo-Html
        Write-Host "  Generating User Mailbox table (Manual Method)..." -ForegroundColor Gray
        $bodyContent += "<h3>User Mailboxes</h3>"
        if ($userMailboxes) {
            # Select data (still useful to get consistent objects)
            $userTableData = $userMailboxes | Select-Object @{Name='DisplayName'; Expression={$_.DisplayName}}, `
                                                            @{Name='PrimarySmtpAddress'; Expression={$_.PrimarySmtpAddress}}, `
                                                            @{Name='Size (MB)'; Expression={ "$($_.MailboxSizeMB)" }}, ` # Get as string
                                                            @{Name='Last Logon'; Expression={ if ($_.LastLogonTime) { $_.LastLogonTime.ToString('yyyy-MM-dd HH:mm') } else { 'N/A' } }} # Get as string

            # Manual HTML Table Build
            $tableHtml = "<p style='text-align: center; margin-top:0;'>Count: $currentUserCount</p>"
            # Add alignment styling directly to TH elements
            $tableHtml += "<table><thead><tr><th>DisplayName</th><th>PrimarySmtpAddress</th><th style='text-align: right;'>Size (MB)</th><th style='text-align: right;'>Last Logon</th></tr></thead><tbody>"

            foreach ($row in $userTableData) {
                # Basic HTML Encoding for data fields
                $displayNameEsc = $row.DisplayName -replace '&', '&amp;' -replace '<', '&lt;' -replace '>', '&gt;'
                $smtpAddressEsc = $row.PrimarySmtpAddress -replace '&', '&amp;' -replace '<', '&lt;' -replace '>', '&gt;'
                $sizeEsc = $row.'Size (MB)' -replace '&', '&amp;' -replace '<', '&lt;' -replace '>', '&gt;'
                $logonEsc = $row.'Last Logon' -replace '&', '&amp;' -replace '<', '&lt;' -replace '>', '&gt;'

                $tableHtml += "<tr>"
                $tableHtml += "<td>$displayNameEsc</td>"
                $tableHtml += "<td>$smtpAddressEsc</td>"
                # Add alignment styling directly to TD elements
                $tableHtml += "<td style='text-align: right; white-space: nowrap;'>$sizeEsc</td>"
                $tableHtml += "<td style='text-align: right; white-space: nowrap;'>$logonEsc</td>"
                $tableHtml += "</tr>"
            }
            $tableHtml += "</tbody></table>"
            $bodyContent += $tableHtml # Append the manually built HTML

        } else {
             $bodyContent += "<p class='nodata'>No user mailboxes found in this database.</p>"
        }
        # END BLOCK TO REPLACE ConvertTo-Html


        # --- Add Shared Mailboxes Table (Manual HTML Generation) ---
        # START BLOCK TO REPLACE ConvertTo-Html
        Write-Host "  Generating Shared Mailbox table (Manual Method)..." -ForegroundColor Gray
        $bodyContent += "<h3>Shared Mailboxes</h3>"
        if ($sharedMailboxes) {
            # Select data
            $sharedTableData = $sharedMailboxes | Select-Object @{Name='DisplayName'; Expression={$_.DisplayName}}, `
                                                               @{Name='PrimarySmtpAddress'; Expression={$_.PrimarySmtpAddress}}, `
                                                               @{Name='Size (MB)'; Expression={ "$($_.MailboxSizeMB)" }}, ` # Get as string
                                                               @{Name='Last Logon'; Expression={ if ($_.LastLogonTime) { $_.LastLogonTime.ToString('yyyy-MM-dd HH:mm') } else { 'N/A' } }} # Get as string

            # Manual HTML Table Build
            $tableHtml = "<p style='text-align: center; margin-top:0;'>Count: $currentSharedCount</p>"
             # Add alignment styling directly to TH elements
            $tableHtml += "<table><thead><tr><th>DisplayName</th><th>PrimarySmtpAddress</th><th style='text-align: right;'>Size (MB)</th><th style='text-align: right;'>Last Logon</th></tr></thead><tbody>"

            foreach ($row in $sharedTableData) {
                # Basic HTML Encoding for data fields
                $displayNameEsc = $row.DisplayName -replace '&', '&amp;' -replace '<', '&lt;' -replace '>', '&gt;'
                $smtpAddressEsc = $row.PrimarySmtpAddress -replace '&', '&amp;' -replace '<', '&lt;' -replace '>', '&gt;'
                $sizeEsc = $row.'Size (MB)' -replace '&', '&amp;' -replace '<', '&lt;' -replace '>', '&gt;'
                $logonEsc = $row.'Last Logon' -replace '&', '&amp;' -replace '<', '&lt;' -replace '>', '&gt;'

                $tableHtml += "<tr>"
                $tableHtml += "<td>$displayNameEsc</td>"
                $tableHtml += "<td>$smtpAddressEsc</td>"
                 # Add alignment styling directly to TD elements
                $tableHtml += "<td style='text-align: right; white-space: nowrap;'>$sizeEsc</td>"
                $tableHtml += "<td style='text-align: right; white-space: nowrap;'>$logonEsc</td>"
                $tableHtml += "</tr>"
            }
            $tableHtml += "</tbody></table>"
            $bodyContent += $tableHtml # Append the manually built HTML
        } else {
            $bodyContent += "<p class='nodata'>No shared mailboxes found in this database.</p>"
        }
        # END BLOCK TO REPLACE ConvertTo-Html

         $dbEndTime = Get-Date
         Write-Host "  Finished processing $dbName in $(($dbEndTime - $dbStartTime).ToString('g'))." -ForegroundColor Green
    } # End foreach database

    # --- Add Chart Placeholders and Script ---
    Write-Host "Generating summary charts..." -ForegroundColor Yellow
    $bodyContent += "<hr style='margin-top: 40px;'>"
    $bodyContent += "<h2 style='text-align: center;'>Mailbox Count Summary Charts</h2>"
    $bodyContent += "<div class='charts-wrapper'>" # Start charts wrapper
    $bodyContent += "<div class='chart-container'><h3>Total Mailbox Distribution</h3><canvas id='mailboxPieChart'></canvas></div>" # Pie Chart
    $bodyContent += "<div class='chart-container'><h3>Mailbox Counts per Database</h3><canvas id='mailboxColumnChart'></canvas></div>" # Column Chart
    $bodyContent += "</div>" # End charts wrapper

    # --- Prepare Data for JavaScript ---
    $dbLabels = $databaseStats.DatabaseName | ForEach-Object { "'$($_ -replace "'", "\'")'" } # Quote names & escape internal quotes for JS
    $jsDbLabels = "[$($dbLabels -join ', ')]"
    $jsUserCounts = "[$($databaseStats.UserCount -join ', ')]"
    $jsSharedCounts = "[$($databaseStats.SharedCount -join ', ')]"

    # --- Embed JavaScript for Chart Generation ---
    # Using a self-executing anonymous function to scope variables
    $chartScript = @"
<script>
(function() {
  const totalUserData=$totalUserMailboxes, totalSharedData=$totalSharedMailboxes;
  const dbLabels=$jsDbLabels, dbUserData=$jsUserCounts, dbSharedData=$jsSharedCounts;
  const pieCtx=document.getElementById('mailboxPieChart').getContext('2d');
  new Chart(pieCtx,{type:'pie',data:{labels:['User ('+totalUserData+')','Shared ('+totalSharedData+')'],datasets:[{data:[totalUserData,totalSharedData],backgroundColor:['rgba(54,162,235,0.7)','rgba(255,159,64,0.7)'],borderColor:['rgba(54,162,235,1)','rgba(255,159,64,1)'],borderWidth:1}]},options:{responsive:!0,aspectRatio:1.5,plugins:{legend:{position:'bottom'},title:{display:!1}}}});
  const colCtx=document.getElementById('mailboxColumnChart').getContext('2d');
  new Chart(colCtx,{type:'bar',data:{labels:dbLabels,datasets:[{label:'User Mailboxes',data:dbUserData,backgroundColor:'rgba(54,162,235,0.7)',borderColor:'rgba(54,162,235,1)',borderWidth:1},{label:'Shared Mailboxes',data:dbSharedData,backgroundColor:'rgba(255,159,64,0.7)',borderColor:'rgba(255,159,64,1)',borderWidth:1}]},options:{responsive:!0,aspectRatio:1.5,plugins:{legend:{position:'top'},title:{display:!1}},scales:{y:{beginAtZero:!0,title:{display:!0,text:'Number of Mailboxes'}},x:{title:{display:!0,text:'Database Name'}}}}});
})();
</script>
"@
    $bodyContent += $chartScript

    # --- Finalize and Save HTML Report ---
    $scriptEndTime = Get-Date
    $bodyContent += "<p class='note' style='margin-top: 30px;'>Report generation took: $(($scriptEndTime - $scriptStartTime).ToString('g'))</p>" # Add duration
    Write-Host "Generating final HTML report file..." -ForegroundColor Yellow
    ConvertTo-Html -Head $head -Body $bodyContent -Title "Exchange Database Mailbox Report" | Out-File -FilePath $outputFile -Encoding UTF8 -Force

    Write-Host "Report saved to $outputFile" -ForegroundColor Green

    # --- Open the file ---
    Write-Host "Attempting to open the report..." -ForegroundColor Cyan
    Invoke-Item -Path $outputFile

    # Clean up session
    Write-Host "Removing PSSession..." -ForegroundColor Gray
    Remove-PSSession $session
    Write-Host "Script finished successfully in $(($scriptEndTime - $scriptStartTime).ToString('g'))." -ForegroundColor Green

} catch {
    # Log the error details
    Write-Host "An error occurred:" -ForegroundColor Red
    Write-Host "Error Message: $($_.Exception.Message)" -ForegroundColor Red
    # Display Line number if available
    if ($_.InvocationInfo) {
        Write-Host "Line Number: $($_.InvocationInfo.ScriptLineNumber)" -ForegroundColor Red
        # Write-Host "Line Content: $($_.InvocationInfo.Line)" -ForegroundColor Red # Uncomment to see the line content
    }
    # Write-Host "Stack Trace: $($_.ScriptStackTrace)" -ForegroundColor DarkGray # Uncomment for full stack trace if needed
    Write-Host "Script failed." -ForegroundColor Red

    # Attempt to remove session even if error occurred mid-script
    if ($PSBoundParameters.ContainsKey('session') -and $session -ne $null -and (Get-PSSession | Where-Object {$_.ID -eq $session.ID})) {
        Write-Host "Attempting to remove PSSession after error..." -ForegroundColor Yellow
        Remove-PSSession $session -ErrorAction SilentlyContinue
    }
    # Pause script window if running in console host before exit
    if ($Host.Name -eq 'ConsoleHost') {
        Read-Host -Prompt "Press Enter to exit"
    }
    # Exit with a non-zero code to indicate failure
    exit 1
} finally {
    # Any final cleanup that should always run (even after errors, if possible)
    Write-Progress -Activity "Cleanup" -Completed -ErrorAction SilentlyContinue
}
```