# Helper module for extracting Oracle schema objects
# Uses SQL*Plus or other Oracle command-line tools to connect and extract objects
# Import the utilities module for logging and common functions

function Get-OracleSchemaObjects {
    param(
        [hashtable]$Properties
    )
    
    try {
        # Validate input parameters
        if (-not $Properties) {
            Show-Error -Message "Properties parameter is null or empty" -LogPrefix "OracleExtract"
            return $false
        }
        
        # Create output directory
        $outputDir = Get-OracleOutputPath
        if (-not (Test-Path $outputDir)) {
            New-Item -ItemType Directory -Path $outputDir -Force | Out-Null
        }
        
        $oracleUser = $Properties['ORACLE_USER']
        $csvFile = Get-OracleCsvFilePath -OracleUser $Properties['ORACLE_USER']
        
        Show-Info -Message "Connecting to Oracle and extracting schema objects..." -LogPrefix "OracleExtract"
        
        # Get the SQL script path using utility function
        $sqlScriptPath = Get-HelperScriptPath "SQL-Extract-Oracle-Objects.sql"
        
        # Read Oracle query from external SQL file
        if (-not (Test-Path $sqlScriptPath)) {
            throw "SQL script not found: $sqlScriptPath"
        }
        $oracleQuery = Get-Content -Path $sqlScriptPath -Raw
        
        # Create SQL file for SQL*Plus
        $sqlFile = Join-Path $outputDir "oracle_extract.sql"
        $oracleQuery | Out-File -FilePath $sqlFile -Encoding ASCII
        
        # Build SQL*Plus command with OWNER parameter
        $sqlplusCmd = Get-OracleSqlPlusCommand -Properties $Properties -SqlPlusFile $sqlFile -AdditionalArgs $oracleUser
        
        Show-Info -Message "Executing SQL*Plus command..." -LogPrefix "OracleExtract"
        
        # Execute SQL*Plus and capture output
        $output = & cmd /c $sqlplusCmd 2>&1

        if ($LASTEXITCODE -ne 0) {
            Show-Error -Message "Connection test failed. Exit code: $LASTEXITCODE" -LogPrefix "OracleExtract"
            Show-Error -Message "Error output: $output" -LogPrefix "OracleExtract"
            
            Invoke-CleanUpAndExit -ExitCode $LASTEXITCODE
        }

        # Check for ignore objects file
        $ignoreTablesPath = Join-Path $global:MigrationDirectory "ignoreoracleobjects.txt"
        $ignoreTables = @()
        
        if (Test-Path $ignoreTablesPath) {
            Show-Info -Message "Found ignoreoracleobjects.txt file, reading ignored object names..." -LogPrefix "OracleExtract"
            $ignoreTables = Get-Content -Path $ignoreTablesPath | ForEach-Object { $_.Trim() } | Where-Object { $_ -ne "" }
            Show-Info -Message "Found $($ignoreTables.Count) objects to ignore" -LogPrefix "OracleExtract"
        } else {
            Show-Info -Message "No ignoreoracleobjects.txt file found at $ignoreTablesPath" -LogPrefix "OracleExtract"
        }
        
        # Parse output and create CSV
        $objects = @()
        foreach ($line in $output) {
            if ($line -match '^([^,]+),(.+)$') {
                $objectType = $matches[1].Trim()
                $objectName = $matches[2].Trim()
                
                if ($objectName -and $objectType) {
                    # Check if this table should be ignored
                    if ($ignoreTables -contains $objectName) {
                        Show-Info -Message "Ignoring object: $objectName" -LogPrefix "OracleExtract"
                        continue
                    }
                    
                    $objects += [PSCustomObject]@{
                        objecttype = $objectType
                        objectname = $objectName
                    }
                }
            }
        }
        
        # Export to CSV
        $objects | Export-Csv -Path $csvFile -NoTypeInformation
        
        # Clean up SQL file
        Remove-Item $sqlFile -ErrorAction SilentlyContinue
        
        Show-Success -Message "List of $($objects.Count) objects added to $csvFile" -LogPrefix "OracleExtract"
        return $true
        
    } catch {
        Show-Error -Message "Failed to extract Oracle objects: $($_.Exception.Message)" -LogPrefix "OracleExtract"
        
        # Create a sample CSV structure for demonstration if Oracle connectivity fails
        Show-Warning -Message "Creating sample CSV for demonstration..." -LogPrefix "OracleExtract"
        $sampleObjects = @(
            [PSCustomObject]@{ objecttype = "TABLE"; objectname = "SAMPLE_TABLE" },
            [PSCustomObject]@{ objecttype = "VIEW"; objectname = "SAMPLE_VIEW" },
            [PSCustomObject]@{ objecttype = "SEQUENCE"; objectname = "SAMPLE_SEQ" }
        )
        $sampleObjects | Export-Csv -Path $csvFile -NoTypeInformation

        Show-Warning -Message "Note: This is a sample implementation. Oracle connectivity needs to be implemented." -LogPrefix "OracleExtract"
        return $true
    }
}

Export-ModuleMember -Function Get-OracleSchemaObjects
