# Helper module for comparing Oracle and PostgreSQL objects
# Compares CSV files directly without creating temporary tables
# Import the utilities module for logging and common functions

Function Invoke-ExtractOraclePostgresSchemaObjects {
    param(
        [Parameter(Mandatory=$true)]
        [hashtable]$Properties
    )
    
    # Extract PostgreSQL Schema Objects
    Show-Info -Message "Extracting PostgreSQL Schema Objects..." -LogPrefix "CompareDB"
    $postgresModule = Get-HelperScriptPath "Helper-Extract-PostgreSQL-Objects.psm1"
    if (Test-Path $postgresModule) {
        try {
            Import-Module $postgresModule -Force
            $result = Get-PostgreSQLObjects -Properties $Properties
            if (-not $result) {
                Show-Error -Message "PostgreSQL object extraction failed!" -LogPrefix "CompareDB"
                return $false
            }
        } catch {
            Show-Error -Message "PostgreSQL object extraction failed: $($_.Exception.Message)" -LogPrefix "CompareDB"
            return $false
        }
    } else {
        Show-Error -Message "Helper-Extract-PostgreSQL-Objects.psm1 not found at: $postgresModule" -LogPrefix "CompareDB"
        return $false
    }
    
    # Extract Oracle Customer Schema
    Show-Info -Message "Extracting Oracle Customer Schema..." -LogPrefix "CompareDB"
    $oracleModule = Get-HelperScriptPath "Helper-Extract-Oracle-Objects.psm1"
    if (Test-Path $oracleModule) {
        try {
            Import-Module $oracleModule -Force
            $result = Get-OracleSchemaObjects -Properties $Properties
            if (-not $result) {
                Show-Error -Message "Oracle object extraction failed!" -LogPrefix "CompareDB"
                return $false
            }
        } catch {
            Show-Error -Message "Oracle object extraction failed: $($_.Exception.Message)" -LogPrefix "CompareDB"
            return $false
        }
    } else {
        Show-Error -Message "Helper-Extract-Oracle-Objects.psm1 not found at: $oracleModule" -LogPrefix "CompareDB"
        return $false
    }
}

function Compare-Objects {
    param(
        [Parameter(Mandatory=$true)]
        [hashtable]$Properties,
        [bool]$ShowDifferences = $true
    )
    Show-Info -Message "Comparing Oracle and PostgreSQL objects..." -LogPrefix "CompareDB"

    try {
        # Create output directory and generate filename with timestamp
        $outputDir = Get-OutputPath
        if (-not (Test-Path $outputDir)) {
            New-Item -ItemType Directory -Path $outputDir -Force | Out-Null
        }
        
        $differencesFile = Join-Path $outputDir "$($Properties['ORACLE_USER'])_$($global:Timestamp)_differences.csv"
        
        $postgresCsv = Get-PostgresCsvFilePath -PostgresSchema $Properties['POSTGRES_SCHEMA']
        if (-not (Test-Path $postgresCsv)) {
            Show-Error -Message "PostgreSQL objects CSV not found: $postgresCsv" -LogPrefix "CompareDB"
            return $false
        }
        
        $oracleCsv = Get-OracleCsvFilePath -OracleUser $Properties['ORACLE_USER']
        if (-not (Test-Path $oracleCsv)) {
            Show-Error -Message "Oracle objects CSV not found: $oracleCsv" -LogPrefix "CompareDB"
            return $false
        }
        
        Show-Info -Message "Loading object lists from CSV files..." -LogPrefix "CompareDB"
        Show-Info -Message "PostgreSQL file: $postgresCsv" -LogPrefix "CompareDB"
        Show-Info -Message "Oracle file: $oracleCsv" -LogPrefix "CompareDB"
        
        $postgresObjects = Import-Csv -Path $postgresCsv
        $oracleObjects = Import-Csv -Path $oracleCsv
        
        Show-Info -Message "PostgreSQL objects loaded: $($postgresObjects.Count)" -LogPrefix "CompareDB"
        Show-Info -Message "Oracle objects loaded: $($oracleObjects.Count)" -LogPrefix "CompareDB"
        
        Show-Info -Message "Comparing objects to find differences..." -LogPrefix "CompareDB"
        
        $differences = @()
        
        # Find objects in Oracle but not in PostgreSQL
        foreach ($oracleObj in $oracleObjects) {
            $found = $false
            foreach ($pgObj in $postgresObjects) {
                # Check if the Oracle object name starts with the PostgreSQL object name, relevant for TABLES
                if ($oracleObj.objecttype.StartsWith($pgObj.objecttype, [System.StringComparison]::OrdinalIgnoreCase) -and $oracleObj.objectname -ieq $pgObj.objectname) {
                    $found = $true
                    break
                }
            }
            if (-not $found) {
                $differences += [PSCustomObject]@{
                    source = "Oracle"
                    objecttype = $oracleObj.objecttype
                    objectname = $oracleObj.objectname
                    status = "Missing in PostgreSQL"
                }
            }
        }
        
        # Export differences to CSV
        $differences | Export-Csv -Path $differencesFile -NoTypeInformation
        
        Show-Success -Message "Found $($differences.Count) differences exported to $differencesFile" -LogPrefix "CompareDB"
        Show-Info -Message "Output saved to: $differencesFile" -LogPrefix "CompareDB"
        
        if ($differences.Count -eq 0) {
            Show-Success -Message "No differences found between Oracle and PostgreSQL objects." -LogPrefix "CompareDB"
        } else {
            if ($ShowDifferences) {
                Show-Warning -Message "Differences found:" -LogPrefix "CompareDB"
                foreach ($diff in $differences) {
                    Show-Warning -Message "  $($diff.source): $($diff.objecttype) - $($diff.objectname) ($($diff.status))" -LogPrefix "CompareDB"
                }
            }
        }
    } catch {
        Show-Error -Message "Failed to compare objects: $($_.Exception.Message)" -LogPrefix "CompareDB"
    }
}

function Find-Differences{
    param(
        [Parameter(Mandatory=$true)]
        [hashtable]$Properties,
        [Parameter(Mandatory=$false)]
        [string[]]$ObjectTypes = $null
    )
    try {
        Show-Info -Message "Changes Summary:" -LogPrefix "CompareDB"

        # Find the latest differences file
        $differencesCsv = Join-Path $(Get-OutputPath) "$($Properties['ORACLE_USER'])_$($global:Timestamp)_differences.csv"
        if (-not (Test-Path $differencesCsv)) {
            Show-Warning -Message "No differences file found. Proceeding with migration..." -LogPrefix "CompareDB"
            return $false
        }
        
        $differences = Import-Csv -Path $differencesCsv
        
        if ($differences.Count -eq 0) {
            Show-Success -Message "No differences found - all objects are already synchronized." -LogPrefix "CompareDB"
            Show-Info -Message "No migration of missing objects requested" -LogPrefix "CompareDB"
            return $false
        }
        
        # Filter differences if ObjectTypes parameter is provided
        if ($ObjectTypes -and $ObjectTypes.Count -gt 0) {
            Show-Info -Message "Migration will be limited to the following object types: $($ObjectTypes -join ', ')" -LogPrefix "CompareDB"
            $filteredDifferences = $differences | Where-Object { $_.objecttype.ToLower() -in ($ObjectTypes | ForEach-Object { $_.ToLower() }) }
            Show-Info -Message "After filtering, found $($filteredDifferences.Count) objects that will be migrated:" -LogPrefix "CompareDB"
        } else {
            Show-Warning -Message "Found $($differences.Count) objects that need to be migrated:" -LogPrefix "CompareDB"
            $filteredDifferences = $differences
        }
        
        Show-Info -Message "" -LogPrefix "CompareDB"
        
        # Group by object type for better readability
        $groupedDifferences = $filteredDifferences | Group-Object -Property objecttype
        foreach ($group in $groupedDifferences) {
            Show-Info -Message "  $($group.Name) ($($group.Count) objects):" -LogPrefix "CompareDB"
            foreach ($diff in $group.Group) {
                Show-Info -Message "    - $($diff.objectname)" -LogPrefix "CompareDB"
            }
            Show-Info -Message "" -LogPrefix "CompareDB"
        }
        
           # Check if there are any filtered objects to migrate
           if ($groupedDifferences.Count -eq 0 -or $filteredDifferences.Count -eq 0) {
            Show-Success -Message "No objects to migrate after filtering - all objects are already synchronized." -LogPrefix "CompareDB"
            Show-Info -Message "No migration of missing objects requested" -LogPrefix "CompareDB"
            return $false
        }

        # Return true if there are differences to migrate
        return $true

    }
    catch {
        Show-Error -Message "Error reading changes summary: $($_.Exception.Message)" -LogPrefix "CompareDB"
        Show-Warning -Message "Proceeding with migration..." -LogPrefix "CompareDB"
        return $true
    }
}

# Function to show changes summary and get user confirmation
function Show-ChangesSummaryAndMigrateObjects {
    param(
        [Parameter(Mandatory=$true)]
        [hashtable]$Properties,
        [string[]]$ObjectTypes = $null,
        [bool]$AutoFixAttempted = $false
    )
    Invoke-ExtractOraclePostgresSchemaObjects -Properties $Properties
    Compare-Objects -Properties $Properties -ShowDifferences $false
    $differences = Find-Differences -Properties $Properties -ObjectTypes $ObjectTypes

    if ($differences) {
        if ($AutoFixAttempted) {
            Show-Warning -Message "Differences remain after attempted auto fix. Please review the differences and fix them manually." -LogPrefix "CompareDB"
            return @{ ProceedWithStructureMigration = $false; CriticalError = $true }
        } else {
            Show-Info -Message "These changes will:" -LogPrefix "CompareDB"
            Show-Info -Message "  - Add missing Oracle objects to PostgreSQL using MTK" -LogPrefix "CompareDB"
            Show-Info -Message "  - May take some time depending on the number of objects" -LogPrefix "CompareDB"
            Show-Info -Message "  - Create the necessary database structures" -LogPrefix "CompareDB"
            Show-Info -Message "" -LogPrefix "CompareDB"

            $migrateObjects = Read-Host "Do you want to proceed with migrating these objects? Choose reload to re-do the comparison and check the ignore file. ([y]es/[n]o/[r]eload, default: y)"
            $migrateObjects = $migrateObjects.ToLower()
            if ($migrateObjects -match "^(no|n)$") {
                Show-Info -Message "User did not want to proceed with migrating these objects" -LogPrefix "CompareDB"
                return @{ ProceedWithStructureMigration = $false; CriticalError = $true }
            } elseif ($migrateObjects -match "^(reload|r)$") {
                Show-Info -Message "User wants to reload the comparison" -LogPrefix "CompareDB"
                return Show-ChangesSummaryAndMigrateObjects -Properties $Properties -ObjectTypes $ObjectTypes -AutoFixAttempted $AutoFixAttempted
            } elseif ($migrateObjects -eq '' -or $migrateObjects -match "^(yes|y)$") {
                return @{ ProceedWithStructureMigration = $true; CriticalError = $false }
            } else {
                # TODO: this should be a loop
                Show-Error -Message "Invalid input. Exiting" -LogPrefix "CompareDB"
                Invoke-CleanUpAndExit -ExitCode 1
            }
        }
    }
    
    return @{ ProceedWithStructureMigration = $true; CriticalError = $false }
}

Export-ModuleMember -Function Invoke-ExtractOraclePostgresSchemaObjects, Compare-Objects, Find-Differences, Show-ChangesSummaryAndMigrateObjects
