# Define the path to track processed files
$processedFilesPath = "C:\Audits\processed_files.txt"

# Create or initialize the processed files list
if (-Not (Test-Path $processedFilesPath)) {
    New-Item -Path $processedFilesPath -ItemType File -Force
}

# Load assemblies needed for QueryableXEventData
try {
    $sharedPath = "C:\Program Files\Microsoft SQL Server\150\Shared" # Path varies by SQL tool version
    $xeCore = [System.IO.Path]::Combine($sharedPath, "Microsoft.SqlServer.XE.Core.dll")
    $xeLinq = [System.IO.Path]::Combine($sharedPath, "Microsoft.SqlServer.XEvent.Linq.dll")
    
    # Check if assemblies exist
    if (-Not (Test-Path $xeCore) -or -Not (Test-Path $xeLinq)) {
        Write-Host "One or more assemblies are missing."
        exit
    }
    Add-Type -Path $xeCore
    Add-Type -Path $xeLinq

    # Define the directory path
    $directoryPath = "C:\Audits\SQLAudits"

    # Get all .sqlaudit files from the directory
    $files = Get-ChildItem -Path $directoryPath -Filter *.sqlaudit

    if ($files.Count -eq 0) {
        Write-Host "No session files found in the directory."
        exit
    }

    # Read the list of processed files
    $processedFiles = Get-Content -Path $processedFilesPath

    # Loop through each session file and create a QueryableXEventData object
    foreach ($file in $files) 
    {
        $filePath = $file.FullName
        
        # Skip files that have already been processed
        if ($processedFiles -contains $filePath) {
            Write-Host "Skipping already processed file: $filePath"
            continue
        }

        Write-Host "Processing file: $filePath"
        
        try {
            # Use a 'using' block for proper disposal
            $events = New-Object Microsoft.SqlServer.XEvent.Linq.QueryableXEventData($filePath)
            if ($events -eq $null) {
                Write-Host "Failed to create QueryableXEventData object for file: $filePath"
                continue
            }

            # Perform operations with $events
            $count = 1
            foreach ($event in $events) {
                if ($event.Fields["statement"]) {
                    Write-Host $count " - " $event.Fields["statement"].Value
                } else {
                    Write-Host "$count - No 'statement' field found"
                }
                $count++
            }
            
            # Log the file path to the processed files list
            Add-Content -Path $processedFilesPath -Value $filePath
        }
        catch {
            Write-Host "An error occurred while processing file: $filePath"
            Write-Host "Error details: $_"
        }
        finally {
            # Dispose of the events object
            if ($events) { $events.Dispose() }
        }
    }
}
catch {
    Write-Host "An error occurred: $_"
}