# Helper module for running MTK tool for migration
# Reads differences CSV and runs MTK tool for different object types
# Import the utilities module for logging and common functions

# Script-level variables for deduplication
$script:LoggedFileOperations = @()

# Function to reset deduplication tracking
function Reset-MTKDeduplicationTracking {
    $script:LoggedFileOperations = @()
}

# Function to clean up MTK output files, keeping only the _ddl file
function Remove-MTKOutputFiles {
    param(
        [string]$FailedDbObjectsDirectory,
        [string]$SchemaName
    )
    
    try {
        if (-not (Test-Path $FailedDbObjectsDirectory)) {
            Show-Info -Message "FailedDbObjects directory not found: $FailedDbObjectsDirectory" -LogPrefix "MTKMissing" 
            return
        }
        
        # Get all SQL files in the directory
        $sqlFiles = Get-ChildItem -Path $FailedDbObjectsDirectory -Filter "*.sql" -Recurse
        
        if ($sqlFiles.Count -eq 0) {
            Show-Info -Message "No SQL files found in FailedDbObjects directory" -LogPrefix "MTKMissing" 
            return
        }
        
        $filesToDelete = @()
        $filesToKeep = @()
        
        foreach ($file in $sqlFiles) {
            $fileName = $file.Name
            if ($fileName -eq "mtk_$($SchemaName)_ddl.sql" -or
                $fileName -eq "mtk_$($SchemaName)_schema_ddl.sql") {
                 # Delete all other files
                 $filesToDelete += $file
                 # Only log if we haven't seen this file operation before in this session
                 if ($script:LoggedFileOperations -notcontains "delete:$fileName") {
                     Show-Info -Message "Marking for deletion: $fileName" -LogPrefix "MTKMissing" 
                     $script:LoggedFileOperations += "delete:$fileName"
                 }
            } else {

                $filesToKeep += $file
                # Only log if we haven't seen this file operation before in this session
                if ($script:LoggedFileOperations -notcontains "keep:$fileName") {
                    Show-Info -Message "Keeping file: $fileName" -LogPrefix "MTKMissing" 
                    $script:LoggedFileOperations += "keep:$fileName"
                }
            }
        }
        
        # Delete the unwanted files
        foreach ($file in $filesToDelete) {
            try {
                Remove-Item -Path $file.FullName -Force
            } catch {
                Show-Warning -Message "Failed to delete file $($file.Name): $($_.Exception.Message)" -LogPrefix "MTKMissing" 
            }
        }
        
        Show-Success -Message "MTK output cleanup completed. Deleted $($filesToDelete.Count) files, kept $($filesToKeep.Count) files." -LogPrefix "MTKMissing" 
        
        return $filesToKeep
    } catch {
        Show-Error -Message "Failed to cleanup MTK output files: $($_.Exception.Message)" -LogPrefix "MTKMissing" 
    }
}

# Function to analyze MTK log file and determine if migration actually failed
function Test-MTKLogForFailures {
    param(
        [string]$LogFilePath
    )
    
    try {
        if (-not (Test-Path $LogFilePath)) {
            Show-Warning -Message "Log file not found: $LogFilePath" -LogPrefix "MTKMissing" 
            return @{ HasFailures = $false; ErrorMessages = @(); FailedObjectNames = @() }
        }
        
        $logContent = Get-Content $LogFilePath
        $failedObjectNames = @()
        
        # Look for strong failure indicators FIRST (these override everything else)
        $strongFailureIndicators = $logContent | Where-Object {
            $_ -match "Schema.*imported with errors|Failed count: [1-9]|List of failed objects|One or more schema objects could not be imported|Created 0 tables"
        }
        
        # Extract failed object names from the log
        $inFailedObjectsSection = $false
        $currentObjectType = ""
        
        foreach ($line in $logContent) {
            # Check if we're entering the failed objects section
            if ($line -match "List of failed objects") {
                $inFailedObjectsSection = $true
                continue
            }
            
            # Check if we're exiting the failed objects section
            if ($inFailedObjectsSection -and $line -match "^$") {
                $inFailedObjectsSection = $false
                continue
            }
            
            # If we're in the failed objects section, look for object type headers
            if ($inFailedObjectsSection) {
                # Look for object type headers (e.g., "Tables", "Sequences", etc.)
                if ($line -match "^([A-Za-z]+)$" -and $line -notmatch "List of failed objects") {
                    $currentObjectType = $matches[1]
                    continue
                }
                
                # Look for object names (lines that start with numbers and dots)
                if ($line -match "^\s*\d+\.\s*(.+)$") {
                    $objectName = $matches[1].Trim()
                    if ($objectName -and $objectName -ne $currentObjectType) {
                        # Strip schema name if present (e.g., "AX7XDEV.WSTEST1" becomes "WSTEST1")
                        if ($objectName -match "^[A-Za-z0-9_]+\.") {
                            $objectName = $objectName.Split('.')[-1]
                        }
                        $failedObjectNames += $objectName
                    }
                }
                
                # Also look for object names without numbers (direct object names)
                if ($line -match "^\s*([A-Za-z0-9_\.]+)$" -and $line -notmatch "^[A-Za-z]+$" -and $line -notmatch "List of failed objects") {
                    $objectName = $line.Trim()
                    if ($objectName -and $objectName -ne $currentObjectType -and $objectName -notin $failedObjectNames) {
                        # Strip schema name if present (e.g., "AX7XDEV.WSTEST1" becomes "WSTEST1")
                        if ($objectName -match "^[A-Za-z0-9_]+\.") {
                            $objectName = $objectName.Split('.')[-1]
                        }
                        $failedObjectNames += $objectName
                    }
                }
            }
        }
        
        if ($strongFailureIndicators) {
            Show-Warning -Message "MTK log shows strong failure indicators: $($strongFailureIndicators -join '; ')" -LogPrefix "MTKMissing" 
            if ($failedObjectNames.Count -gt 0) {
                Show-Info -Message "Extracted failed object names: $($failedObjectNames -join ', ')" -LogPrefix "MTKMissing" 
            }
            return @{ HasFailures = $true; ErrorMessages = $strongFailureIndicators; FailedObjectNames = $failedObjectNames }
        }
        
        # Check for migration summary pattern showing success (Failed count: 0 and Successful count > 0)
        # Extract the counts from the migration summary section
        $failedCount = -1
        $successCount = -1
        
        $failedCountLine = $logContent | Where-Object { $_ -match "Failed count:\s*(\d+)" } | Select-Object -First 1
        $successCountLine = $logContent | Where-Object { $_ -match "Successful count:\s*(\d+)" } | Select-Object -First 1
        
        if ($failedCountLine -and $failedCountLine -match "Failed count:\s*(\d+)") {
            $failedCount = [int]$matches[1]
        }
        if ($successCountLine -and $successCountLine -match "Successful count:\s*(\d+)") {
            $successCount = [int]$matches[1]
        }
        
        # If failed count is 0 and successful count > 0, it's a success
        if ($failedCount -eq 0 -and $successCount -gt 0) {
            Show-Info -Message "MTK log shows migration summary: Failed count: $failedCount, Successful count: $successCount" -LogPrefix "MTKMissing" 
            return @{ HasFailures = $false; ErrorMessages = @(); FailedObjectNames = @() }
        }
        
        # Look for strong success indicators
        $strongSuccessIndicators = $logContent | Where-Object {
            $_ -match "Migration process completed successfully|Schema.*imported successfully|Created.*tables.*[1-9]|Migration completed successfully"
        }
        
        # Look for actual failure patterns, excluding informational messages
        $actualFailures = $logContent | Where-Object { 
            $_ -match "ERROR|FAILED|FAILURE" -and
            $_ -notmatch "No errors found|No failures detected|Migration completed successfully|SUCCESS|INFO" -and
            $_ -notmatch "Failed to connect.*reconnected|Failed to connect.*retrying" -and
            $_ -notmatch "WARNING.*non-critical|INFO.*informational" -and
            $_ -notmatch ".*INFO.*|.*WARNING.*" -and
            $_ -notmatch "Migration.*completed.*successfully|Successfully.*migrated|Created.*tables|Schema.*imported successfully"
        }
        
        # If we have strong success indicators and no strong failures, consider it successful
        if ($strongSuccessIndicators -and -not $strongFailureIndicators) {
            Show-Info -Message "MTK log shows strong success indicators: $($strongSuccessIndicators -join '; ')" -LogPrefix "MTKMissing" 
            return @{ HasFailures = $false; ErrorMessages = @(); FailedObjectNames = @() }
        }
        
        # Look for general success indicators
        $successIndicators = $logContent | Where-Object {
            $_ -match "Migration completed successfully|Successfully migrated|SUCCESS|Completed successfully"
        }
        
        # If we have success indicators and no actual failures, consider it successful
        if ($successIndicators -and -not $actualFailures -and -not $strongFailureIndicators) {
            Show-Info -Message "MTK log shows successful migration with success indicators" -LogPrefix "MTKMissing" 
            return @{ HasFailures = $false; ErrorMessages = @(); FailedObjectNames = @() }
        }
        
        # If we have actual failures, return them
        if ($actualFailures) {
            Show-Warning -Message "MTK log shows actual failures: $($actualFailures.Count) failure messages" -LogPrefix "MTKMissing" 
            return @{ HasFailures = $true; ErrorMessages = $actualFailures; FailedObjectNames = $failedObjectNames }
        }
        
        # If no clear success or failure indicators, check for completion
        $completionIndicators = $logContent | Where-Object {
            $_ -match "Migration.*completed|Process.*completed|Finished|Done"
        }
        
        if ($completionIndicators -and -not $strongFailureIndicators) {
            Show-Info -Message "MTK log shows completion without clear failures" -LogPrefix "MTKMissing" 
            return @{ HasFailures = $false; ErrorMessages = @(); FailedObjectNames = @() }
        }
        
        # Default to no failures if we can't determine
        Show-Info -Message "MTK log analysis inconclusive - assuming no failures" -LogPrefix "MTKMissing" 
        return @{ HasFailures = $false; ErrorMessages = @(); FailedObjectNames = @() }
        
    } catch {
        Show-Error -Message "Failed to analyze MTK log: $($_.Exception.Message)" -LogPrefix "MTKMissing" 
        return @{ HasFailures = $false; ErrorMessages = @(); FailedObjectNames = @() }
    }
}

function Invoke-FailedObjects {
    param(
        [hashtable]$Properties,
        [string]$FailedDbObjectsDirectory,
        [string]$FailedObjectType,
        [string]$FailedList,
        [int]$BatchNumber = 0
    )
    
    try {
        Show-Info -Message "Handling failed objects of type: $FailedObjectType" -LogPrefix "MTKMissing" 
        
        # Include batch number in directory path if provided
        if ($BatchNumber -gt 0) {
            $specificFailedObjectDir = Join-Path $FailedDbObjectsDirectory "$FailedObjectType\Batch$BatchNumber"
        } else {
            $specificFailedObjectDir = Join-Path $FailedDbObjectsDirectory $FailedObjectType
        }
        
        # Check if folder exists and create if it does not
        if (-not (Test-Path $FailedDbObjectsDirectory)) {
            Show-Info -Message "Creating FailedDbObjects directory: $FailedDbObjectsDirectory" -LogPrefix "MTKMissing" 
            New-Item -ItemType Directory -Path $FailedDbObjectsDirectory -Force | Out-Null
        }
        
        if (-not (Test-Path $specificFailedObjectDir)) {
            Show-Info -Message "Creating failed object directory: $specificFailedObjectDir" -LogPrefix "MTKMissing" 
            New-Item -ItemType Directory -Path $specificFailedObjectDir -Force | Out-Null
        }
        
        # Determine the flag based on object type
        $objectFlag = switch ($FailedObjectType.ToLower()) {
            { $_ -like "table*" } { "indexes -constraints -triggers -tables" }  # Handles TABLE, TABLE_LOB, etc.
            "sequence" { "sequences" }
            "type" { "types" }
            "view" { "views" }
            "procedure" { "procs" }
            "function" { "funcs" }
            "package" { "packages" }
            "trigger" { "indexes -constraints -triggers -tables" }
            "materialized view" { "views" }
            
            default { "objects" }
        }

        $failedListString = $FailedList

        # Build the MTK command
        $mtkCommand = ".\runMTK.bat -schemaOnly -offlineMigration `"$specificFailedObjectDir`" -$objectFlag '$failedListString' -targetSchema $($Properties['POSTGRES_SCHEMA']) $($Properties['ORACLE_USER'])"
        
        Show-Info -Message "Executing MTK command for failed object: $mtkCommand" -LogPrefix "MTKMissing" 
        
        # Change to MTK bin directory and execute command
        $mtkBinPath = "C:\Program Files\edb\mtk\bin"
        Push-Location $mtkBinPath
        try {
            Invoke-Expression $mtkCommand 2>&1 | Out-Null
            Show-Success -Message "MTK command completed for failed type: $FailedObjectType" -LogPrefix "MTKMissing" 
            
            # Clean up MTK output files, keeping only the _ddl file
            $filesToKeep = Remove-MTKOutputFiles -FailedDbObjectsDirectory $FailedDbObjectsDirectory -SchemaName $Properties['POSTGRES_SCHEMA']
 
            # If it is a trigger, parse the produced files and remove create table and create index statements
            if ($FailedObjectType.ToLower() -eq "trigger") {
                Remove-NonTriggerDDLInFiles -Files $filesToKeep
            }
        } catch {
            Show-Error -Message "MTK command failed for type $FailedObjectType : $($_.Exception.Message)" -LogPrefix "MTKMissing" 
        } finally {
            Pop-Location
        }
    } catch {
        Show-Error -Message "Failed to handle failed type $FailedObjectType : $($_.Exception.Message)" -LogPrefix "MTKMissing" 
    }
}

# Function to process a single batch of objects with MTK
function Invoke-MTKObjectBatch {
    param(
        [string]$ObjectType,
        [string[]]$ObjectNames,
        [hashtable]$Properties,
        [string]$MtkFlag,
        [string]$OracleUser,
        [string]$MtkBinPath,
        [string]$FailedDbObjectsDir,
        [string]$ObjectList = $null
    )
    
    # If ObjectList is not provided or is empty, build it from ObjectNames
    if ([string]::IsNullOrEmpty($ObjectList)) {
        if ($ObjectType.ToLower() -eq "trigger") {
            $ObjectList = Get-OracleTriggerTables -Properties $Properties -Triggers $ObjectNames -LogPrefix "MTKMissing" 
            if ($ObjectList -eq $false) {
                return @{
                    HasFailures = $true
                    FailedObjectNames = $ObjectNames
                    FailedObjectText = @("=== $ObjectType ===", "Failed to get trigger tables", "")
                    FailedObjectCsv = @([PSCustomObject]@{
                        Timestamp = $global:Timestamp
                        ObjectType = $ObjectType
                        ObjectName = ($ObjectNames -join ", ")
                        Count = $ObjectNames.Count
                        Status = "MTK_FAILED"
                        Reason = "Failed to get trigger tables"
                        Action = "Review and fix manually"
                        MigrationTool = "MTK"
                        ErrorDetails = "Failed to get associated tables for triggers"
                    })
                    LogFileName = ""
                }
            }
        } else {
            $ObjectList = $ObjectNames -join ","
        }
    }
    
    # Build MTK command
    $template = ".\runMTK.bat -schemaOnly -{0} '$ObjectList' -verbose off -logBadSQL -targetSchema $($Properties['POSTGRES_SCHEMA']) $OracleUser"
    $mtkCommand = $template -f $MtkFlag
    
    # Ensure mtkCommand is a string, not an array
    $mtkCommandString = if ($mtkCommand -is [array]) { $mtkCommand -join ' ' } else { $mtkCommand.ToString() }
    
    Show-Info -Message "Executing: $mtkCommandString" -LogPrefix "MTKMissing" 
    
    # Get current timestamp before command execution to identify the specific log file
    $commandStartTime = Get-Date
    
    # Change to MTK bin directory and execute command
    Push-Location $MtkBinPath
    try {
        Invoke-Expression $mtkCommandString 2>&1 | Out-Null
        
        # Get MTK log file created for this specific command execution
        $userName = $env:USERNAME
        $logPath = "C:\Users\$userName\.enterprisedb\migration-toolkit\logs"
        
        if (Test-Path $logPath) {
            # Find log files created after the command started
            $relevantLogs = Get-ChildItem -Path $logPath -Filter "*.log" | Where-Object { $_.LastWriteTime -ge $commandStartTime }
            
            if ($relevantLogs) {
                # Get the most recent log file from the relevant ones
                $commandLog = $relevantLogs | Sort-Object LastWriteTime -Descending | Select-Object -First 1
                
                Show-Info -Message "Analyzing log file for $($ObjectType): $($commandLog.FullName)" -LogPrefix "MTKMissing" 
                
                # Use the new function to analyze the log file
                $logAnalysis = Test-MTKLogForFailures -LogFilePath $commandLog.FullName
                
                if ($logAnalysis.HasFailures) {
                    $failedObjectText = @()
                    $failedObjectText += "=== $ObjectType ==="
                    $failedObjectText += "Log File: $($commandLog.Name)"
                    $failedObjectText += "Failed count: $($logAnalysis.FailedObjectNames.Count)"
                    
                    # Add failed object names to text report
                    if ($logAnalysis.FailedObjectNames.Count -gt 0) {
                        $failedObjectText += "Failed objects: $($logAnalysis.FailedObjectNames -join ', ')"
                    } else {
                        $failedObjectText += "Failed objects: $($ObjectNames -join ', ')"
                    }
                    
                    $failedObjectText += $logAnalysis.ErrorMessages
                    $failedObjectText += ""
                    
                    # Get unique error messages to avoid duplication
                    $uniqueErrors = $logAnalysis.ErrorMessages | Sort-Object | Get-Unique
                    
                    # Parse failed objects for CSV - use specific failed object names if available
                    $failedObjectNamesForCsv = if ($logAnalysis.FailedObjectNames.Count -gt 0) {
                        $logAnalysis.FailedObjectNames -join ", "
                    } else {
                        ($ObjectNames -join ", ")
                    }          
                    $failedObjectCountForCsv = if ($logAnalysis.FailedObjectNames.Count -gt 0) {
                        $logAnalysis.FailedObjectNames.Count
                    } else {
                        $ObjectNames.Count
                    }

                    # Parse failed objects for CSV - only add one entry per object type with combined errors
                    $failedObjectCsv = [PSCustomObject]@{
                        Timestamp = $global:Timestamp
                        ObjectType = $ObjectType
                        ObjectName = $failedObjectNamesForCsv
                        Count = $failedObjectCountForCsv
                        Status = "MTK_FAILED"
                        Reason = "MTK migration failed"
                        Action = "Review MTK logs and fix manually"
                        MigrationTool = "MTK"
                        ErrorDetails = "Log: $($commandLog.Name); Errors: " + ($uniqueErrors -join "; ")
                    }
                    
                    return @{
                        HasFailures = $true
                        FailedObjectNames = if ($logAnalysis.FailedObjectNames.Count -gt 0) { $logAnalysis.FailedObjectNames } else { $ObjectNames }
                        FailedObjectText = $failedObjectText
                        FailedObjectCsv = @($failedObjectCsv)
                        LogFileName = $commandLog.Name
                    }
                } else {
                    return @{
                        HasFailures = $false
                        FailedObjectNames = @()
                        FailedObjectText = @()
                        FailedObjectCsv = @()
                        LogFileName = $commandLog.Name
                    }
                }
            } else {
                Show-Warning -Message "No log file found for this command execution" -LogPrefix "MTKMissing" 
                return @{
                    HasFailures = $false
                    FailedObjectNames = @()
                    FailedObjectText = @()
                    FailedObjectCsv = @()
                    LogFileName = ""
                }
            }
        } else {
            Show-Warning -Message "Log path not found: $logPath" -LogPrefix "MTKMissing" 
            return @{
                HasFailures = $false
                FailedObjectNames = @()
                FailedObjectText = @()
                FailedObjectCsv = @()
                LogFileName = ""
            }
        }
    } finally {
        Pop-Location
    }
}

function Import-OracleObjectsToPostgreSQL {
    param(
        [hashtable]$Properties,
        [string]$FailedObjectsDirectory,
        [string[]]$ObjectTypes = $null
    )
    $null = Get-MtkInstallationPath

    # Reset deduplication tracking for this session
    Reset-MTKDeduplicationTracking
    
    # Global variable to track failed objects from logs
    $script:FailedObjectsFromLogs = @()
    $failedDbObjectsDir = Join-Path $FailedObjectsDirectory "FailedDbObjects"
    
    try {
        Show-Success -Message "Starting MTK migration..." -LogPrefix "MTKMissing" 
        
        # Show which object types will be migrated
        if ($ObjectTypes -and $ObjectTypes.Count -gt 0) {
            Show-Info -Message "Migration will be limited to the following object types: $($ObjectTypes -join ', ')" -LogPrefix "MTKMissing" 
        } else {
            Show-Info -Message "Migration will process all missing object types" -LogPrefix "MTKMissing" 
        }
        
        # Validate Properties parameter
        if ($null -eq $Properties) {
            Show-Error -Message "Properties parameter is null" -LogPrefix "MTKMissing" 
            return $false
        }
        
        if (-not $Properties.ContainsKey('ORACLE_USER')) {
            Show-Error -Message "ORACLE_USER not found in Properties" -LogPrefix "MTKMissing" 
            return $false
        }
        $oracleUser = $Properties['ORACLE_USER']
        
        Show-Info -Message "Oracle User: $oracleUser" -LogPrefix "MTKMissing" 

        $differencesCsv = Join-Path $(Get-OutputPath) "$($oracleUser)_$($global:Timestamp)_differences.csv"
        if (-not (Test-Path $differencesCsv)) {
            Show-Error -Message "No differences file found: $differencesCsv" -LogPrefix "MTKMissing" 
            return $false
        }
        
        $mtkBinPath = "C:\Program Files\edb\mtk\bin"

        if (-not (Test-Path $FailedObjectsDirectory)) {
            New-Item -ItemType Directory -Path $FailedObjectsDirectory -Force | Out-Null
        }
        
        $failedObjectsFile = Join-Path $FailedObjectsDirectory "failed_objects_manual_fixes_required.txt"
        $failedObjectsCsv = Join-Path $FailedObjectsDirectory "failed_objects_manual_fixes_required.csv"
        
        Show-Info -Message "Loading differences from CSV file..." -LogPrefix "MTKMissing" 
        
        # Load differences and group by object type
        try {
            $differences = Import-Csv -Path $differencesCsv
            Show-Success -Message "Successfully loaded $($differences.Count) differences from CSV" -LogPrefix "MTKMissing" 
        } catch {
            Show-Error -Message "Failed to import CSV file: $($_.Exception.Message)" -LogPrefix "MTKMissing" 
            return $false
        }
        
        try {
            $objectGroups = $differences | Group-Object -Property objecttype
            Show-Success -Message "Successfully grouped objects by type. Found $($objectGroups.Count) object types" -LogPrefix "MTKMissing" 
        } catch {
            Show-Error -Message "Failed to group objects by type: $($_.Exception.Message)" -LogPrefix "MTKMissing" 
            return $false
        }
        
        # Filter object groups if ObjectTypes parameter is provided
        if ($ObjectTypes -and $ObjectTypes.Count -gt 0) {
            Show-Info -Message "Filtering objects to only include: $($ObjectTypes -join ', ')" -LogPrefix "MTKMissing" 
            $filteredGroups = @()
            foreach ($o in $ObjectTypes) {
                # Using StartsWith to handle cases like TABLE_LOB and TABLE
                $group = $objectGroups | Where-Object { $_.Name.StartsWith($o, [System.StringComparison]::OrdinalIgnoreCase) }
                if ($group) {
                    $filteredGroups += $group
                }
            }
            $objectGroups = $filteredGroups
            Show-Success -Message "After filtering, found $($objectGroups.Count) object types" -LogPrefix "MTKMissing" 
            Show-Info -Message "Only the following object types will be migrated: $(($objectGroups | Select-Object -ExpandProperty Name) -join ', ')" -LogPrefix "MTKMissing" 
        }
        
        $allFailedObjects = @()
        $failedObjectsForCsv = @()
        $criticalFailures = @()  # Track critical failures that should stop migration
        $successfulObjects = @()  # Track successful migrations for reporting
        
        # Check MAX_TABLE_IMPORT property
        $maxTableImport = 0
        if ($Properties.ContainsKey('MAX_TABLE_IMPORT')) {
            $maxTableImport = [int]$Properties['MAX_TABLE_IMPORT']
        }
        
        # Process other object types (excluding tables and triggers since they were handled above)
        foreach ($group in $objectGroups) {
            Show-Info -Message "Processing group: $($group.Name)" -LogPrefix "MTKMissing" 
            
            $objectType = $group.Name
            
            try {
                $objectNames = ($group.Group | Where-Object { 
                    $_.source -eq "Oracle" -and 
                    $_.status -eq "Missing in PostgreSQL" -and 
                    $null -ne $_.objectname -and 
                    $_.objectname.Trim() -ne "" 
                }).objectname | ForEach-Object { 
                    if ($_) { $_ } 
                }
            } catch {
                Show-Error -Message "Failed to process object names for type $objectType : $($_.Exception.Message)" -LogPrefix "MTKMissing" 
                continue
            }
            
            if ($objectNames.Count -eq 0) {
                Show-Warning -Message "No objects found for type: $objectType" -LogPrefix "MTKMissing" 
                continue
            }
            
            Show-Info -Message "Found $($objectNames.Count) objects of type: $objectType" -LogPrefix "MTKMissing" 
            
            # Handle manual processing types (type, global temporary table)
            if ($objectType.ToLower() -eq "type") {
                Invoke-ManualObjectProcessing -ObjectType "TYPE" -ObjectNames $objectNames -Properties $Properties -FailedDbObjectsDir $failedDbObjectsDir -SqlScriptName "SQL-Extract-Oracle-Types.sql" -DirectoryName "TYPES" -DisplayName "Types" -Reason "MTK does not support type migration" -Action "Create equivalent PostgreSQL type manually" -ErrorDetails "Types require manual conversion" -AllFailedObjects ([ref]$allFailedObjects) -FailedObjectsForCsv ([ref]$failedObjectsForCsv)
                continue
            }
            
            if ($objectType.ToLower() -eq "global temporary table") {
                Invoke-ManualObjectProcessing -ObjectType "GLOBAL TEMPORARY TABLE" -ObjectNames $objectNames -Properties $Properties -FailedDbObjectsDir $failedDbObjectsDir -SqlScriptName "SQL-Extract-Global-Temp-Table-Columns.sql" -DirectoryName "GLOBALTEMPORARYTABLES" -DisplayName "Global temporary tables" -Reason "MTK does not support global temporary table migration" -Action "Create equivalent PostgreSQL temporary table manually" -ErrorDetails "Global temporary tables require manual conversion" -AllFailedObjects ([ref]$allFailedObjects) -FailedObjectsForCsv ([ref]$failedObjectsForCsv)
                continue
            }
            
            # Determine MTK command flag based on object type
            $mtkFlag = switch ($objectType.ToLower()) {
                { $_ -like "table*" -or $_ -eq "trigger" } { 'indexes -constraints -triggers -tables' }
                "sequence" { 'sequences' }
                { $_ -like "*view" } { 'views' }
                "procedure" { 'procs' }
                "package" { 'packages' }
                "function" { 'funcs' }
                default { 
                    Show-Warning -Message "No MTK command defined for object type: $objectType" -LogPrefix "MTKMissing" 
                    # Add to failed objects list for manual handling
                    foreach ($obj in $objectNames) {
                        $allFailedObjects += "=== $objectType ==="
                        $allFailedObjects += "Object: $obj"
                        $allFailedObjects += "Status: MANUAL FIX REQUIRED - No MTK command defined"
                        $allFailedObjects += "Reason: Object type not supported by MTK"
                        $allFailedObjects += "Action: Manual migration required"
                        $allFailedObjects += ""
                        
                        $failedObjectsForCsv += [PSCustomObject]@{
                            Timestamp = $global:Timestamp
                            ObjectType = $objectType
                            ObjectName = $obj
                            Count = 1
                            Status = "MANUAL_FIX_REQUIRED"
                            Reason = "Object type not supported by MTK"
                            Action = "Manual migration required"
                            MigrationTool = "N/A"
                            ErrorDetails = "No MTK command defined for this object type"
                        }
                    }
                    continue
                }
            }
            
            if (-not $mtkFlag) {
                continue
            }
            
            # Process objects in batches if MAX_TABLE_IMPORT is set
            if ($maxTableImport -gt 0) {
                Show-Info -Message "MAX_TABLE_IMPORT is $maxTableImport, processing $objectType objects in batches" -LogPrefix "MTKMissing" 
                
                $totalObjects = $objectNames.Count
                $allBatchFailedObjects = @()
                $allBatchSuccessfulObjects = @()
                $batchFailures = @{}  # Track failures per batch: @{BatchNumber = @(FailedObjectNames)}
                
                for ($i = 0; $i -lt $totalObjects; $i += $maxTableImport) {
                    $batchObjects = $objectNames[$i..([Math]::Min($i + $maxTableImport - 1, $totalObjects - 1))]
                    $batchNumber = [Math]::Floor($i / $maxTableImport) + 1
                    $totalBatches = [Math]::Ceiling($totalObjects / $maxTableImport)
                    
                    Show-Section -Message "Processing Batch $batchNumber of $totalBatches for $objectType" -LogPrefix "MTKMissing" 
                    Show-Info -Message "Processing objects: $($batchObjects -join ', ')" -LogPrefix "MTKMissing" 
                    
                    # Process this batch
                    $batchResult = Invoke-MTKObjectBatch -ObjectType $objectType -ObjectNames $batchObjects -Properties $Properties -MtkFlag $mtkFlag -OracleUser $oracleUser -MtkBinPath $mtkBinPath -FailedDbObjectsDir $failedDbObjectsDir
                    
                    if ($batchResult.HasFailures) {
                        $allBatchFailedObjects += $batchResult.FailedObjectNames
                        $allFailedObjects += $batchResult.FailedObjectText
                        $failedObjectsForCsv += $batchResult.FailedObjectCsv
                        $batchFailures[$batchNumber] = $batchResult.FailedObjectNames
                        
                        if ($objectType.ToLower() -in @("table", "table_lob", "sequence", "type")) {
                            $criticalFailures += $objectType
                        }
                    } else {
                        $allBatchSuccessfulObjects += $batchObjects
                    }
                    
                    # Wait before starting the next batch to ensure proper completion
                    if ($batchNumber -lt $totalBatches) {
                        Show-Info -Message "Waiting 5 seconds before starting next batch..." -LogPrefix "MTKMissing" 
                        Start-Sleep -Seconds 5
                    }
                }
                
                # Aggregate results from all batches
                if ($allBatchSuccessfulObjects.Count -gt 0) {
                    $successfulObjects += [PSCustomObject]@{
                        ObjectType = $objectType
                        ObjectName = ($allBatchSuccessfulObjects -join ", ")
                        Count = $allBatchSuccessfulObjects.Count
                        Status = "SUCCESS"
                        LogFile = "Multiple batches"
                    }
                    Show-Success -Message "$objectType migration completed successfully for $($allBatchSuccessfulObjects.Count) objects" -LogPrefix "MTKMissing" 
                }
                
                # Process failed objects per batch if any
                if ($allBatchFailedObjects.Count -gt 0) {
                    $script:FailedObjectsFromLogs += $allBatchFailedObjects
                    # Process each batch's failures separately with batch number
                    foreach ($batchNum in $batchFailures.Keys | Sort-Object) {
                        $batchFailedList = $batchFailures[$batchNum] -join ","
                        Show-Info -Message "Processing failed objects from Batch $batchNum : $($batchFailures[$batchNum] -join ', ')" -LogPrefix "MTKMissing" 
                        Invoke-FailedObjects -Properties $Properties -FailedObjectType $objectType -FailedList $batchFailedList -FailedDbObjectsDirectory $failedDbObjectsDir -BatchNumber $batchNum
                    }
                }
            } else {
                # Process all objects at once (original behavior)
                Show-Info -Message "Processing $objectType objects: $($objectNames.Count) objects" -LogPrefix "MTKMissing" 
                
                # Handle trigger special case - build objectList for triggers
                $objectList = $null
                if ($objectType.ToLower() -eq "trigger") {
                    $objectList = Get-OracleTriggerTables -Properties $Properties -Triggers $objectNames -LogPrefix "MTKMissing" 
                    if ($objectList -eq $false) {
                        continue
                    }
                }
                
                # Execute MTK command (Invoke-MTKObjectBatch will build objectList if not provided)
                $batchResult = Invoke-MTKObjectBatch -ObjectType $objectType -ObjectNames $objectNames -Properties $Properties -MtkFlag $mtkFlag -OracleUser $oracleUser -MtkBinPath $mtkBinPath -FailedDbObjectsDir $failedDbObjectsDir -ObjectList $objectList
                
                if ($batchResult.HasFailures) {
                    $allFailedObjects += $batchResult.FailedObjectText
                    $failedObjectsForCsv += $batchResult.FailedObjectCsv
                    
                    if ($objectType.ToLower() -in @("table", "table_lob", "sequence", "type")) {
                        $criticalFailures += $objectType
                    }
                    
                    if ($batchResult.FailedObjectNames.Count -gt 0) {
                        $script:FailedObjectsFromLogs += $batchResult.FailedObjectNames
                        $failedList = $batchResult.FailedObjectNames -join ","
                        Show-Info -Message "Processing only failed objects: $($batchResult.FailedObjectNames -join ', ')" -LogPrefix "MTKMissing" 
                        Invoke-FailedObjects -Properties $Properties -FailedObjectType $objectType -FailedList $failedList -FailedDbObjectsDirectory $failedDbObjectsDir
                    }
                } else {
                    $successfulObjects += [PSCustomObject]@{
                        ObjectType = $objectType
                        ObjectName = ($objectNames -join ", ")
                        Count = $objectNames.Count
                        Status = "SUCCESS"
                        LogFile = $batchResult.LogFileName
                    }
                    Show-Success -Message "$objectType migration completed successfully" -LogPrefix "MTKMissing" 
                }
            }
        }
        
        # Check for critical failures and stop migration if needed
        if ($criticalFailures.Count -gt 0) {
            $uniqueCriticalFailures = $criticalFailures | Sort-Object | Get-Unique
            Show-Error -Message "CRITICAL FAILURES DETECTED!" -LogPrefix "MTKMissing" 
            Show-Warning -Message "The following critical object types failed migration:" -LogPrefix "MTKMissing" 
            foreach ($failure in $uniqueCriticalFailures) {
                Show-Warning -Message "  - $failure" -LogPrefix "MTKMissing" 
            }
            Show-Warning -Message "MIGRATION PROCESS STOPPED!" -LogPrefix "MTKMissing" 
            Show-Info -Message "Failed objects have been processed and placed in: $(Get-FailedObjectsPath -SchemaName $Properties['ORACLE_USER'])" -LogPrefix "MTKMissing" 
            
            # Write failed objects to files before returning
            if ($allFailedObjects.Count -gt 0) {
                # Write detailed text report
                $allFailedObjects | Out-File -FilePath $failedObjectsFile -Encoding UTF8
                
                # Write CSV report for easier processing
                $failedObjectsForCsv | Export-Csv -Path $failedObjectsCsv -NoTypeInformation
                
                Show-Warning -Message "Found failed objects requiring manual fixes." -LogPrefix "MTKMissing" 
                Show-Info -Message "Text report: $failedObjectsFile" -LogPrefix "MTKMissing" 
                Show-Info -Message "CSV report: $failedObjectsCsv" -LogPrefix "MTKMissing" 
                Show-Info -Message "Total failed objects: $($failedObjectsForCsv.Count)" -LogPrefix "MTKMissing" 
                
                # Display summary
                Show-Warning -Message "Failed Objects Summary:" -LogPrefix "MTKMissing" 
                $failedObjectsForCsv | Group-Object ObjectType | ForEach-Object {
                    $totalCount = ($_.Group | Measure-Object -Property Count -Sum).Sum
                    Show-Warning -Message "  $($_.Name): $totalCount objects" -LogPrefix "MTKMissing" 
                }
            }
            return $false
        }

        # Write failed objects to files (only for non-critical failures)
        if ($allFailedObjects.Count -gt 0) {
            # Write detailed text report
            $allFailedObjects | Out-File -FilePath $failedObjectsFile -Encoding UTF8
            
            # Write CSV report for easier processing
            $failedObjectsForCsv | Export-Csv -Path $failedObjectsCsv -NoTypeInformation
            
            Show-Warning -Message "Found failed objects requiring manual fixes." -LogPrefix "MTKMissing" 
            Show-Info -Message "Text report: $failedObjectsFile" -LogPrefix "MTKMissing" 
            Show-Info -Message "CSV report: $failedObjectsCsv" -LogPrefix "MTKMissing" 
            Show-Info -Message "Total failed objects: $($failedObjectsForCsv.Count)" -LogPrefix "MTKMissing" 
            
            # Display summary
            Show-Error -Message "Failed Objects Summary:" -LogPrefix "MTKMissing" 
            $failedObjectsForCsv | Group-Object ObjectType | ForEach-Object {
                $totalCount = ($_.Group | Measure-Object -Property Count -Sum).Sum
                Show-Error -Message "  $($_.Name): $totalCount objects" -LogPrefix "MTKMissing" 
            }
        }

        # Display successful migrations summary
        if ($successfulObjects.Count -gt 0) {
            Show-Success -Message "Successful Migrations Summary:" -LogPrefix "MTKMissing" 
            $successfulObjects | ForEach-Object {
                Show-Success -Message "  $($_.ObjectType): $($_.Count) objects" -LogPrefix "MTKMissing" 
            }
            Show-Info -Message "Total successful object types: $($successfulObjects.Count)" -LogPrefix "MTKMissing" 
        }
        
        # Final success message
        if ($allFailedObjects.Count -eq 0) {
            Show-Success -Message "No failed objects found." -LogPrefix "MTKMissing" 
            Show-Success -Message "MTK migration completed successfully!" -LogPrefix "MTKMissing" 
            return $true
        } else {
            Show-Warning -Message "MTK migration completed with failed objects." -LogPrefix "MTKMissing" 
            Show-Info -Message "Total failed objects: $($failedObjectsForCsv.Count)" -LogPrefix "MTKMissing" 
            Show-Info -Message "Failed objects have been processed and placed in: $(Get-FailedObjectsPath -SchemaName $Properties['ORACLE_USER'])" -LogPrefix "MTKMissing" 
            return $false
        }
        
    } catch {
        Show-Error -Message "Failed to run MTK migration: $($_.Exception.Message)" -LogPrefix "MTKMissing" 
        Show-Error -Message "Stack trace: $($_.ScriptStackTrace)" -LogPrefix "MTKMissing" 
        return $false
    }
}

# Function to remove non-trigger DDL statements from files
function Remove-NonTriggerDDLInFiles {
    param(
        [Parameter(Mandatory = $true)]
        [System.IO.FileInfo[]]$Files
    )

    try {
        Show-Info -Message "Processing $($Files.Count) files to remove non-trigger DDL statements" -LogPrefix "MTKMissing"

        foreach ($file in $Files) {
            if ($file.FullName -notmatch "trigger") {
                continue
            }
            if (-not (Test-Path $file.FullName)) {
                Show-Warning -Message "File not found: $($file.FullName)" -LogPrefix "MTKMissing"
                continue
            }

            Show-Info -Message "Processing file: $($file.Name)" -LogPrefix "MTKMissing"

            # Read the file content as lines for better processing
            $lines = Get-Content -Path $file.FullName

            if ($lines.Count -eq 0) {
                Show-Warning -Message "File is empty: $($file.Name)" -LogPrefix "MTKMissing"
                continue
            }

            $triggerStatements = @()
            $currentStatement = @()
            $inTrigger = $false
            
            for ($i = 0; $i -lt $lines.Count; $i++) {
                $line = $lines[$i]
                $trimmedLine = $line.Trim()
            
                # Detect start of a trigger
                if ($trimmedLine -match '^\s*CREATE\s+(?:OR\s+REPLACE\s+)?TRIGGER\s+' -or
                    $trimmedLine -match '^\s*ALTER\s+TRIGGER\s+') {
            
                    # If we were already inside a trigger, flush the previous one
                    if ($inTrigger -and $currentStatement.Count -gt 0) {
                        $triggerStatements += ($currentStatement -join "`n")
                        $currentStatement = @()
                    }
            
                    $inTrigger = $true
                }
            
                if ($inTrigger) {
                    $currentStatement += $line
            
                    # End of trigger if:
                    # - We see a "/" line, OR
                    # - The current line ends with ";" AND the next line starts a new trigger or file ends
                    if ($trimmedLine -eq "/") {
                        $triggerStatements += ($currentStatement -join "`n")
                        $inTrigger = $false
                        $currentStatement = @()
                    }
                    elseif ($trimmedLine -match ';\s*$' -and (
                        $i -eq $lines.Count - 1 -or
                        $lines[$i+1].Trim() -match '^\s*CREATE\s+(?:OR\s+REPLACE\s+)?TRIGGER\s+' -or
                        $lines[$i+1].Trim() -match '^\s*ALTER\s+TRIGGER\s+'
                    )) {
                        $triggerStatements += ($currentStatement -join "`n")
                        $inTrigger = $false
                        $currentStatement = @()
                    }
                }
            }
            
            # Flush last trigger if file ended while still inside one
            if ($inTrigger -and $currentStatement.Count -gt 0) {
                $triggerStatements += ($currentStatement -join "`n")
            }

            if ($triggerStatements.Count -eq 0) {
                Show-Warning -Message "No trigger statements found in file: $($file.Name)" -LogPrefix "MTKMissing"
                continue
            }

            # Join the trigger statements back together with proper spacing
            $cleanedContent = $triggerStatements -join "`n`n"

            # Write the cleaned content back to the file
            $cleanedContent | Out-File -FilePath $file.FullName -Encoding UTF8 -Force

            Show-Success -Message "Successfully cleaned file $($file.Name): kept $($triggerStatements.Count) trigger statements" -LogPrefix "MTKMissing"
        }

        Show-Success -Message "Finished processing all trigger DDL files" -LogPrefix "MTKMissing"

    } catch {
        Show-Error -Message "Failed to remove non-trigger DDL statements: $($_.Exception.Message)" -LogPrefix "MTKMissing"
    }
}

function Get-OracleTriggerTables{
    param(
        [hashtable]$Properties,
        [string[]]$Triggers,
        [string]$LogPrefix
    )

    $temporaryOutputPath = Join-Path $(Get-OutputPath) "$($Properties['ORACLE_USER'])_$($global:Timestamp)_failed_objects"

    # Create a temporary SQL file with embedded trigger names to avoid command-line length limits
    $tempSqlFile = Join-Path $temporaryOutputPath "temp_trigger_query_$($global:Timestamp).sql"
    # Temp output file to store the tables
    $outputFile = Join-Path $temporaryOutputPath "temp_triggers_tables_$($global:Timestamp).txt"

    # Build the IN clause with all trigger names - handle the case where Triggers might be null or empty
    if ($null -eq $Triggers -or $Triggers.Count -eq 0) {
        Show-Warning -Message "No triggers provided to Get-OracleTriggerTables" -LogPrefix $LogPrefix
        return $false
    }

    $triggerInClause = "'$($Triggers -join "','")'"

    # Create the SQL content with embedded trigger names
    $sqlContent = @"
SET LONG 2000000
SET PAGESIZE 0
SET LINES 32767
SET TRIMSPOOL ON
SET VERIFY OFF
SET ECHO OFF
SET FEEDBACK OFF

spool "&1"
SELECT UNIQUE
table_name
FROM
all_triggers
WHERE
    owner = '&2'
AND trigger_name IN ($triggerInClause);
spool off

quit;
/
"@

    # Write the SQL content to the temporary file
    $sqlContent | Out-File -FilePath $tempSqlFile -Encoding ASCII

    # Execute the temporary SQL file instead of using command-line parameters
    $sqlplusCmd = Get-OracleSqlPlusCommand -Properties $Properties -SqlPlusFile $tempSqlFile -AdditionalArgs "$outputFile $($Properties['ORACLE_USER'])"

    Show-Info -Message "Executing SQL*Plus command to get the associated tables for $($Triggers.Count) triggers" -LogPrefix $LogPrefix

    try {
        & cmd /c $sqlplusCmd 2>&1 | Out-Null

        # Clean up the temporary SQL file
        if (Test-Path $tempSqlFile) {
            Remove-Item $tempSqlFile -Force
        }

        if (Test-Path $outputFile) {
            # read output file and get the tables
            $tables = Get-Content $outputFile | Where-Object { $_.Trim() -ne "" }
            
            Remove-Item $outputFile -Force

            Show-Success -Message "Successfully retrieved the associated tables for the failed triggers" -LogPrefix $LogPrefix

            if ($tables.Count -eq 0) {
                Show-Warning -Message "No tables found for the specified triggers" -LogPrefix $LogPrefix
                return $false
            }
            $result = $tables -join ","
            Show-Info -Message "Found $($tables.Count) associated tables: $result" -LogPrefix $LogPrefix
            return $result
        } else {
            Show-Error -Message "Failed to find the tables for the failed triggers - output file not created" -LogPrefix $LogPrefix
            return $false
        }
    } catch {
        Show-Error -Message "Error in Get-OracleTriggerTables: $($_.Exception.Message)" -LogPrefix $LogPrefix
        # Clean up temporary file in case of error
        if (Test-Path $tempSqlFile) {
            Remove-Item $tempSqlFile -Force
        }
        return $false
    }
}

# Function to handle manual object processing (types and global temporary tables)
function Invoke-ManualObjectProcessing {
    param(
        [string]$ObjectType,
        [string[]]$ObjectNames,
        [hashtable]$Properties,
        [string]$FailedDbObjectsDir,
        [string]$SqlScriptName,
        [string]$DirectoryName,
        [string]$DisplayName,
        [string]$Reason,
        [string]$Action,
        [string]$ErrorDetails,
        [ref]$AllFailedObjects,
        [ref]$FailedObjectsForCsv
    )

    Show-Warning -Message "$DisplayName requires manual handling - adding to failed objects list" -LogPrefix "MTKMissing" 
    
    # Test Oracle connection first
    Show-Info -Message "Testing Oracle connection before processing $($ObjectType.ToLower())..." -LogPrefix "MTKMissing" 
    
    # Create the folder structure first
    $specificFailedObjectDir = Join-Path $FailedDbObjectsDir $DirectoryName
    
    # Check if folder exists and create if it does not
    if (-not (Test-Path $FailedDbObjectsDir)) {
        Show-Info -Message "Creating FailedDbObjects directory: $FailedDbObjectsDir" -LogPrefix "MTKMissing" 
        New-Item -ItemType Directory -Path $FailedDbObjectsDir -Force | Out-Null
    }
    
    if (-not (Test-Path $specificFailedObjectDir)) {
        Show-Info -Message "Creating $($ObjectType.ToLower()) directory: $specificFailedObjectDir" -LogPrefix "MTKMissing" 
        New-Item -ItemType Directory -Path $specificFailedObjectDir -Force | Out-Null
    }
    
    $specificFailedObjectDir = Join-Path $FailedDbObjectsDir $DirectoryName
            
    if (-not (Test-Path $specificFailedObjectDir)) {
        Show-Info -Message "Creating $($ObjectType.ToLower()) directory: $specificFailedObjectDir" -LogPrefix "MTKMissing" 
        New-Item -ItemType Directory -Path $specificFailedObjectDir -Force | Out-Null
    }
    
    # Create DDL export for each object
    foreach ($objectName in $ObjectNames) {
        try {
            Show-Info -Message "Creating DDL export for $($ObjectType.ToLower()): $objectName" -LogPrefix "MTKMissing" 
            

            # Get the SQL script path using utility function
            $sqlScriptPath = Get-HelperScriptPath $SqlScriptName
            
            # Check if SQL script exists
            if (-not (Test-Path $sqlScriptPath)) {
                Show-Error -Message "SQL script not found: $sqlScriptPath" -LogPrefix "MTKMissing" 
                continue
            }
            
            Show-Info -Message "Using SQL script: $sqlScriptPath" -LogPrefix "MTKMissing" 

            # Define output file path
            $safeObjectName = $objectName -replace '[^\w]', '_'
            $outputFile = Join-Path $specificFailedObjectDir "$safeObjectName`_ddl.sql"
            
            # Build SQL*Plus command using the SQL script directly
            $sqlplusCmd = Get-OracleSqlPlusCommand -Properties $Properties -SqlPlusFile $sqlScriptPath -AdditionalArgs "$objectName $outputFile"
                                            
            Show-Info -Message "Executing SQL*Plus command for $($ObjectType.ToLower()): $objectName" -LogPrefix "MTKMissing" 
            
             # Execute SQL*Plus with timeout and error handling
             & cmd /c $sqlplusCmd 2>&1 | Out-Null
            
            # No cleanup needed since we're using the SQL script directly
            
            if (Test-Path $outputFile) {
                Show-Success -Message "Successfully created DDL export for $($ObjectType.ToLower()): $objectName" -LogPrefix "MTKMissing" 
                Show-Info -Message "DDL file: $outputFile" -LogPrefix "MTKMissing" 
            } else {
                Show-Warning -Message "Failed to create DDL file for $($ObjectType.ToLower()): $objectName" -LogPrefix "MTKMissing" 
            }
        } catch {
            Show-Error -Message "Failed to create DDL export for $($ObjectType.ToLower()) $objectName : $($_.Exception.Message)" -LogPrefix "MTKMissing" 
        }
    }
    

    if ($ObjectNames.Count -gt 0) {
        # Add combined text report
        $AllFailedObjects.Value += "=== $ObjectType ==="
        $AllFailedObjects.Value += "Object Count: $($ObjectNames.Count)"
        $AllFailedObjects.Value += "Objects: $($ObjectNames -join ', ')"
        $AllFailedObjects.Value += "Status: MANUAL FIX REQUIRED - $DisplayName need special handling"
        $AllFailedObjects.Value += "Reason: $Reason"
        $AllFailedObjects.Value += "Action: $Action"
        $AllFailedObjects.Value += ""

        # Create single CSV entry for all objects with total count
        $FailedObjectsForCsv.Value += [PSCustomObject]@{
            Timestamp = $global:Timestamp
            ObjectType = $ObjectType
            ObjectName = ($ObjectNames -join ", ")
            Count = $ObjectNames.Count
            Status = "MANUAL_FIX_REQUIRED"
            Reason = $Reason
            Action = $Action
            MigrationTool = "N/A"
            ErrorDetails = $ErrorDetails
        }
    }
    # Note: DDL files are preserved and not deleted
    # They are stored in: $specificFailedObjectDir
}

Export-ModuleMember -Function Import-OracleObjectsToPostgreSQL, Invoke-FailedObjects, Remove-MTKOutputFiles, Reset-MTKDeduplicationTracking, Remove-NonTriggerDDLInFiles, Get-OracleTriggerTables, Invoke-ManualObjectProcessing