# Helper module for extracting PostgreSQL schema objects
# Uses psql command-line tool to connect and extract objects
# Import the utilities module for logging and common functions
# This module will use the shared logger instance from Helper-Utilities

function Get-PostgreSQLObjects {
    param(
        [Parameter(Mandatory=$true)]
        [hashtable]$Properties
    )
    
    try {
        # Validate input parameters
        if (-not $Properties) {
            Show-Error -Message "Properties parameter is null or empty" -LogPrefix "PostgreSQLExtract"
            return $false
        }
        
        # Create output directory
        $outputDir = Get-PostgresOutputPath
        if (-not (Test-Path $outputDir)) {
            New-Item -ItemType Directory -Path $outputDir -Force | Out-Null
        }
        
        $csvFile = Get-PostgresCsvFilePath -PostgresSchema $Properties['POSTGRES_SCHEMA']
        $objects = @()
        
        Show-Info -Message "Connecting to PostgreSQL and extracting schema objects..." -LogPrefix "PostgreSQLExtract"
        
        # Build psql connection parameters
        $pgHost = $Properties['POSTGRES_SERVER']
        $pgPort = $Properties['POSTGRES_SERVER_PORT']
        $pgDatabase = $Properties['POSTGRES_DATABASE']
        $pgUser = $Properties['POSTGRES_USER']
        $pgPassword = $Properties['POSTGRES_USER_PASSWORD']
        $pgSchema = $Properties['POSTGRES_SCHEMA']
        
        # Validate connection parameters
        if ([string]::IsNullOrWhiteSpace($pgHost)) {
            Show-Error -Message "ERROR: POSTGRES_SERVER is empty or null" -LogPrefix "PostgreSQLExtract"
            return $false
        }
        if ([string]::IsNullOrWhiteSpace($pgPort)) {
            Show-Error -Message "ERROR: POSTGRES_SERVER_PORT is empty or null" -LogPrefix "PostgreSQLExtract"
            return $false
        }
        if ([string]::IsNullOrWhiteSpace($pgDatabase)) {
            Show-Error -Message "ERROR: POSTGRES_DATABASE is empty or null" -LogPrefix "PostgreSQLExtract"
            return $false
        }
        if ([string]::IsNullOrWhiteSpace($pgUser)) {
            Show-Error -Message "ERROR: POSTGRES_USER is empty or null" -LogPrefix "PostgreSQLExtract"
            return $false
        }
        if ([string]::IsNullOrWhiteSpace($pgPassword)) {
            Show-Error -Message "ERROR: POSTGRES_USER_PASSWORD is empty or null" -LogPrefix "PostgreSQLExtract"
            return $false
        }
        if ([string]::IsNullOrWhiteSpace($pgSchema)) {
            Show-Error -Message "ERROR: POSTGRES_SCHEMA is empty or null" -LogPrefix "PostgreSQLExtract"
            return $false
        }
        
        Show-Info -Message "Connection parameters:" -LogPrefix "PostgreSQLExtract"
        Show-Info -Message "  Host: $pgHost" -LogPrefix "PostgreSQLExtract"
        Show-Info -Message "  Port: $pgPort" -LogPrefix "PostgreSQLExtract"
        Show-Info -Message "  Database: $pgDatabase" -LogPrefix "PostgreSQLExtract"
        Show-Info -Message "  User: $pgUser" -LogPrefix "PostgreSQLExtract"
        Show-Info -Message "  Schema: $pgSchema" -LogPrefix "PostgreSQLExtract"
        
        # Test psql connection first
        Show-Info -Message "Testing psql connection..." -LogPrefix "PostgreSQLExtract"
        $testQuery = "SELECT version();"
        $testQueryFile = Join-Path $outputDir "test_query.sql"
        $testQuery | Out-File -FilePath $testQueryFile -Encoding UTF8
        
        # Set PGPASSWORD environment variable for psql
        $env:PGPASSWORD = $pgPassword
        
        try {
            # Debug: Show the psql command being executed
            $psqlCommand = "psql -h `"$pgHost`" -p `"$pgPort`" -d `"$pgDatabase`" -U `"$pgUser`" -f `"$testQueryFile`" -t -A"
            Show-Info -Message "Executing psql command: $psqlCommand" -LogPrefix "PostgreSQLExtract"
            
            $testResult = & psql -h $pgHost -p $pgPort -d $pgDatabase -U $pgUser -f $testQueryFile -t -A 2>&1
            if ($LASTEXITCODE -eq 0) {
                Show-Success -Message "Connection test successful" -LogPrefix "PostgreSQLExtract"
            } else {
                Show-Error -Message "Connection test failed. Exit code: $LASTEXITCODE" -LogPrefix "PostgreSQLExtract"
                Show-Error -Message "Error output: $testResult" -LogPrefix "PostgreSQLExtract"
                
                Invoke-CleanUpAndExit -ExitCode $LASTEXITCODE
            }
        } catch {
            Show-Error -Message "Connection test failed: $($_.Exception.Message)" -LogPrefix "PostgreSQLExtract"
            return $false
        } finally {
            Remove-Item $testQueryFile -ErrorAction SilentlyContinue
        }
        
        # Query 1: Packages (EDB-specific)
        Show-Info -Message "Querying packages..." -LogPrefix "PostgreSQLExtract"
        
        $packageQueryPath = Get-HelperScriptPath "SQL-Extract-PostgreSQL-Packages.sql"
        
        # Read package query from external SQL file
        if (-not (Test-Path $packageQueryPath)) {
            Show-Warning -Message "Package SQL script not found: $packageQueryPath" -LogPrefix "PostgreSQLExtract"
            $packageQuery = "SELECT pkgname FROM edb_package WHERE pkgnamespace = to_regnamespace('$pgSchema')::oid ORDER BY pkgname;"
        } else {
            $packageQuery = Get-Content -Path $packageQueryPath -Raw
            # Replace placeholder with actual schema name
            $packageQuery = $packageQuery -replace '\$pgSchema', $pgSchema
        }
        
        try {
            $packageQueryFile = Join-Path $outputDir "package_query.sql"
            $packageQuery | Out-File -FilePath $packageQueryFile -Encoding UTF8
            
            $packages = & psql -h $pgHost -p $pgPort -d $pgDatabase -U $pgUser -f $packageQueryFile -t -A -F "," 2>&1
            $exitCode = $LASTEXITCODE
            
            if ($exitCode -eq 0 -and $packages) {
                $packageCount = 0
                foreach ($pkg in $packages) {
                    if ($pkg.Trim()) {
                        $objects += [PSCustomObject]@{
                            objecttype = "package"
                            objectname = $pkg.Trim()
                        }
                        $packageCount++
                    }
                }
                Show-Success -Message "Found $packageCount packages" -LogPrefix "PostgreSQLExtract"
            } else {
                Show-Warning -Message "No packages found or query failed (exit code: $exitCode)" -LogPrefix "PostgreSQLExtract"
                if ($packages) {
                    Show-Info -Message "Package query output: $packages" -LogPrefix "PostgreSQLExtract"
                }
            }
            
            Remove-Item $packageQueryFile -ErrorAction SilentlyContinue
        } catch {
            Show-Warning -Message "Failed to extract packages: $($_.Exception.Message)" -LogPrefix "PostgreSQLExtract"
        }
        
        # Query 2: Tables, Views, Materialized Views, Sequences
        Show-Info -Message "Querying tables, views, sequences..." -LogPrefix "PostgreSQLExtract"
        
        $objectsQueryPath = Get-HelperScriptPath "SQL-Extract-PostgreSQL-Objects.sql"
        
        # Read objects query from external SQL file
        if (-not (Test-Path $objectsQueryPath)) {
            Show-Warning -Message "Objects SQL script not found: $objectsQueryPath" -LogPrefix "PostgreSQLExtract"
            $objectsQuery = "SELECT relname AS object_name, relkind AS object_type FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE n.nspname = '$pgSchema' AND relkind IN ('r', 'v', 'm', 'S') ORDER BY relkind, relname;"
        } else {
            $objectsQuery = Get-Content -Path $objectsQueryPath -Raw
            # Replace placeholder with actual schema name
            $objectsQuery = $objectsQuery -replace '\$pgSchema', $pgSchema
        }
        
        try {
            $objectsQueryFile = Join-Path $outputDir "objects_query.sql"
            $objectsQuery | Out-File -FilePath $objectsQueryFile -Encoding UTF8
            
            $dbObjects = & psql -h $pgHost -p $pgPort -d $pgDatabase -U $pgUser -f $objectsQueryFile -t -A -F "," 2>&1
            $exitCode = $LASTEXITCODE
            
            if ($exitCode -eq 0 -and $dbObjects) {
                $objectCount = 0
                foreach ($obj in $dbObjects) {
                    if ($obj.Trim()) {
                        $parts = $obj.Split(',')
                        if ($parts.Length -eq 2) {
                            $objectName = $parts[0].Trim()
                            $objectType = $parts[1].Trim()
                            
                            $objectTypeName = switch ($objectType) {
                                'r' { 'table' }
                                'v' { 'view' }
                                'm' { 'materialized view' }
                                'S' { 'sequence' }
                                default { $objectType }
                            }
                            
                            $objects += [PSCustomObject]@{
                                objecttype = $objectTypeName
                                objectname = $objectName
                            }
                            $objectCount++
                        }
                    }
                }
                Show-Success -Message "Found $objectCount database objects" -LogPrefix "PostgreSQLExtract"
            } else {
                Show-Warning -Message "No database objects found or query failed (exit code: $exitCode)" -LogPrefix "PostgreSQLExtract"
                if ($dbObjects) {
                    Show-Info -Message "Objects query output: $dbObjects" -LogPrefix "PostgreSQLExtract"
                }
            }
            
            Remove-Item $objectsQueryFile -ErrorAction SilentlyContinue
        } catch {
            Show-Warning -Message "Failed to extract database objects: $($_.Exception.Message)" -LogPrefix "PostgreSQLExtract"
        }
        
        # Query 3: Global Temporary Tables
        Show-Info -Message "Querying global temporary tables..." -LogPrefix "PostgreSQLExtract"
        
        $tempTablesQueryPath = Get-HelperScriptPath "SQL-Extract-PostgreSQL-TempTables.sql"
        
        # Read temp tables query from external SQL file
        if (-not (Test-Path $tempTablesQueryPath)) {
            Show-Warning -Message "Temp tables SQL script not found: $tempTablesQueryPath" -LogPrefix "PostgreSQLExtract"
            $tempTablesQuery = "SELECT relname AS object_name FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE n.nspname = 'pgtt_schema' AND relkind = 'r' AND relname <> 'pg_global_temp_tables' ORDER BY relname;"
        } else {
            $tempTablesQuery = Get-Content -Path $tempTablesQueryPath -Raw
        }
        
        try {
            $tempTablesQueryFile = Join-Path $outputDir "temp_tables_query.sql"
            $tempTablesQuery | Out-File -FilePath $tempTablesQueryFile -Encoding UTF8
            
            $tempTables = & psql -h $pgHost -p $pgPort -d $pgDatabase -U $pgUser -f $tempTablesQueryFile -t -A -F "," 2>&1
            $exitCode = $LASTEXITCODE
            
            if ($exitCode -eq 0 -and $tempTables) {
                $tempTableCount = 0
                foreach ($obj in $tempTables) {
                    if ($obj.Trim()) {
                        $objects += [PSCustomObject]@{
                            objecttype = 'global temporary table'
                            objectname = $obj.Trim()
                        }
                        $tempTableCount++
                    }
                }
                Show-Success -Message "Found $tempTableCount global temporary tables" -LogPrefix "PostgreSQLExtract"
            } else {
                Show-Warning -Message "No global temporary tables found or query failed (exit code: $exitCode)" -LogPrefix "PostgreSQLExtract"
            }
            
            Remove-Item $tempTablesQueryFile -ErrorAction SilentlyContinue
        } catch {
            Show-Warning -Message "Failed to extract global temporary tables: $($_.Exception.Message)" -LogPrefix "PostgreSQLExtract"
        }
        
        # Query 4: Types
        Show-Info -Message "Querying types..." -LogPrefix "PostgreSQLExtract"
        
        $typesQueryPath = Get-HelperScriptPath "SQL-Extract-PostgreSQL-Types.sql"
        Show-Info -Message "Types query path: $typesQueryPath" -LogPrefix "PostgreSQLExtract"
        
        # Read types query from external SQL file
        if (-not (Test-Path $typesQueryPath)) {
            Show-Warning -Message "Types SQL script not found: $typesQueryPath" -LogPrefix "PostgreSQLExtract"
            $typesQuery = "SELECT DISTINCT typname FROM pg_catalog.pg_type t JOIN pg_catalog.pg_namespace n ON t.typnamespace = n.oid WHERE n.nspname = '$pgSchema' AND typname NOT LIKE '\_%' ESCAPE '\' ORDER BY typname;"
        } else {
            $typesQuery = Get-Content -Path $typesQueryPath -Raw
            # Replace placeholder with actual schema name
            $typesQuery = $typesQuery -replace '\$pgSchema', $pgSchema
        }
        
        try {
            $typesQueryFile = Join-Path $outputDir "types_query.sql"
            $typesQuery | Out-File -FilePath $typesQueryFile -Encoding UTF8
            
            $types = & psql -h $pgHost -p $pgPort -d $pgDatabase -U $pgUser -f $typesQueryFile -t -A -F "," 2>&1
            $exitCode = $LASTEXITCODE
            
            if ($exitCode -eq 0 -and $types) {
                $typeCount = 0
                foreach ($type in $types) {
                    if ($type.Trim()) {
                        $objects += [PSCustomObject]@{
                            objecttype = "type"
                            objectname = $type.Trim()
                        }
                        $typeCount++
                    }
                }
                Show-Success -Message "Found $typeCount types" -LogPrefix "PostgreSQLExtract"
            } else {
                Show-Info -Message "No types found or query failed (exit code: $exitCode)" -LogPrefix "PostgreSQLExtract"
            }
            
            Remove-Item $typesQueryFile -ErrorAction SilentlyContinue
        } catch {
            Show-Warning -Message "Failed to extract types: $($_.Exception.Message)" -LogPrefix "PostgreSQLExtract"
        }
        
        # Query 5: Triggers
        Show-Info -Message "Querying triggers..." -LogPrefix "PostgreSQLExtract"
        
        $triggersQueryPath = Get-HelperScriptPath "SQL-Extract-PostgreSQL-Triggers.sql"
        
        # Read triggers query from external SQL file
        if (-not (Test-Path $triggersQueryPath)) {
            Show-Warning -Message "Triggers SQL script not found: $triggersQueryPath" -LogPrefix "PostgreSQLExtract"
            $triggersQuery = "SELECT DISTINCT t.tgname FROM pg_catalog.pg_trigger t JOIN pg_catalog.pg_class c ON c.oid = t.tgrelid JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = '$pgSchema' AND t.tgisinternal = false ORDER BY t.tgname;"
        } else {
            $triggersQuery = Get-Content -Path $triggersQueryPath -Raw
            # Replace placeholder with actual schema name
            $triggersQuery = $triggersQuery -replace '\$pgSchema', $pgSchema
        }
        
        try {
            $triggersQueryFile = Join-Path $outputDir "triggers_query.sql"
            $triggersQuery | Out-File -FilePath $triggersQueryFile -Encoding UTF8
            
            $triggers = & psql -h $pgHost -p $pgPort -d $pgDatabase -U $pgUser -f $triggersQueryFile -t -A -F "," 2>&1
            $exitCode = $LASTEXITCODE
            
            if ($exitCode -eq 0 -and $triggers) {
                $triggerCount = 0
                foreach ($trigger in $triggers) {
                    if ($trigger.Trim()) {
                        $objects += [PSCustomObject]@{
                            objecttype = "trigger"
                            objectname = $trigger.Trim()
                        }
                        $triggerCount++
                    }
                }
                Show-Success -Message "Found $triggerCount triggers" -LogPrefix "PostgreSQLExtract"
            } else {
                Show-Warning -Message "No triggers found or query failed (exit code: $exitCode)" -LogPrefix "PostgreSQLExtract"
            }
            
            Remove-Item $triggersQueryFile -ErrorAction SilentlyContinue
        } catch {
            Show-Warning -Message "Failed to extract triggers: $($_.Exception.Message)" -LogPrefix "PostgreSQLExtract"
        }
        
        # Query 6: Functions, Procedures, Aggregates
        Show-Info -Message "Querying functions, procedures, aggregates..." -LogPrefix "PostgreSQLExtract"
        
        $functionsQueryPath = Get-HelperScriptPath "SQL-Extract-PostgreSQL-Functions.sql"
        
        # Read functions query from external SQL file
        if (-not (Test-Path $functionsQueryPath)) {
            Show-Warning -Message "Functions SQL script not found: $functionsQueryPath" -LogPrefix "PostgreSQLExtract"
            $functionsQuery = "SELECT p.proname as object_name, CASE p.prokind WHEN 'a' THEN 'aggregate' WHEN 'w' THEN 'window' WHEN 'p' THEN 'procedure' ELSE 'function' END as object_type FROM pg_catalog.pg_proc p JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace WHERE n.nspname = '$pgSchema' ORDER BY p.prokind, p.proname;"
        } else {
            $functionsQuery = Get-Content -Path $functionsQueryPath -Raw
            # Replace placeholder with actual schema name
            $functionsQuery = $functionsQuery -replace '\$pgSchema', $pgSchema
        }
        
        try {
            $functionsQueryFile = Join-Path $outputDir "functions_query.sql"
            $functionsQuery | Out-File -FilePath $functionsQueryFile -Encoding UTF8
            
            $functions = & psql -h $pgHost -p $pgPort -d $pgDatabase -U $pgUser -f $functionsQueryFile -t -A -F "," 2>&1
            $exitCode = $LASTEXITCODE
            
            if ($exitCode -eq 0 -and $functions) {
                $functionCount = 0
                foreach ($func in $functions) {
                    if ($func.Trim()) {
                        $parts = $func.Split(',')
                        if ($parts.Length -eq 2) {
                            $objectName = $parts[0].Trim()
                            $objectType = $parts[1].Trim()
                            
                            $objects += [PSCustomObject]@{
                                objecttype = $objectType
                                objectname = $objectName
                            }
                            $functionCount++
                        }
                    }
                }
                Show-Success -Message "Found $functionCount functions/procedures/aggregates" -LogPrefix "PostgreSQLExtract"
            } else {
                Show-Warning -Message "No functions/procedures/aggregates found or query failed (exit code: $exitCode)" -LogPrefix "PostgreSQLExtract"
            }
            
            Remove-Item $functionsQueryFile -ErrorAction SilentlyContinue
        } catch {
            Show-Warning -Message "Failed to extract functions/procedures/aggregates: $($_.Exception.Message)" -LogPrefix "PostgreSQLExtract"
        }
        
        # Export to CSV
        $objects | Export-Csv -Path $csvFile -NoTypeInformation
        
        # Clear PGPASSWORD environment variable
        Remove-Item Env:PGPASSWORD -ErrorAction SilentlyContinue
        
        Show-Success -Message "List of $($objects.Count) objects added to $csvFile" -LogPrefix "PostgreSQLExtract"
        
        if ($objects.Count -eq 0) {
            Show-Warning -Message "Warning: No objects were extracted. This could indicate:" -LogPrefix "PostgreSQLExtract"
            Show-Warning -Message "  1. The schema '$pgSchema' doesn't exist or is empty" -LogPrefix "PostgreSQLExtract"
            Show-Warning -Message "  2. The user doesn't have permissions to access the schema" -LogPrefix "PostgreSQLExtract"
            Show-Warning -Message "  3. The database connection parameters are incorrect" -LogPrefix "PostgreSQLExtract"
            Show-Warning -Message "  4. The database is not an EDB PostgreSQL instance (for packages)" -LogPrefix "PostgreSQLExtract"
        }
        
        return $true
        
    } catch {
        Show-Error -Message "Failed to extract PostgreSQL objects: $($_.Exception.Message)" -LogPrefix "PostgreSQLExtract"
        # Clear PGPASSWORD environment variable
        Remove-Item Env:PGPASSWORD -ErrorAction SilentlyContinue
        return $false
    }
}

Export-ModuleMember -Function Get-PostgreSQLObjects
