# Helper Utilities Module for PowerShell Scripts
# Provides centralized UI display functions for all helper modules

# Script-level variable to track logger availability warning
$script:LoggerUnavailableWarningShown = $false

# =============================================================================
# UI DISPLAY FUNCTIONS
# =============================================================================

function Show-Header {
    param([string]$Title)
    $separator = "=" * 60
    Write-Host ""
    Write-Host $separator -ForegroundColor Cyan
    Write-Host "  $Title" -ForegroundColor White
    Write-Host $separator -ForegroundColor Cyan
    Write-Host ""
}

function Show-Section {
    param([string]$Title)
    $separator = "-" * 50
    Write-Host ""
    Write-Host $separator -ForegroundColor Yellow
    Write-Host "  $Title" -ForegroundColor White
    Write-Host $separator -ForegroundColor Yellow
    Write-Host ""
}

function Show-Success {
    param(
        [string]$Message,
        [string]$LogPrefix = "Migration"
    )
    
    # Skip processing if message is empty or null
    if ([string]::IsNullOrEmpty($Message)) {
        return
    }
    
    if (Get-Command Write-HelperLog -ErrorAction SilentlyContinue) {
        Write-HelperLog -Message $Message -Level "Success" -LogPrefix $LogPrefix -MigrationDirectory $global:MigrationDirectory
    } else {
        if (-not $script:LoggerUnavailableWarningShown) {
            Write-Host "  [WARNING] Logger unavailable - messages will only be displayed in console" -ForegroundColor Yellow
            $script:LoggerUnavailableWarningShown = $true
        }
        Write-Host ""
        Write-Host "  [SUCCESS] $Message" -ForegroundColor Green
        Write-Host ""
    }
}

function Show-Info {
    param(
        [string]$Message,
        [string]$LogPrefix = "Migration"
    )
    
    # Skip processing if message is empty or null
    if ([string]::IsNullOrEmpty($Message)) {
        return
    }
    
    if (Get-Command Write-HelperLog -ErrorAction SilentlyContinue) {
        Write-HelperLog -Message $Message -Level "Info" -LogPrefix $LogPrefix -MigrationDirectory $global:MigrationDirectory
    } else {
        if (-not $script:LoggerUnavailableWarningShown) {
            Write-Host "  [WARNING] Logger unavailable - messages will only be displayed in console" -ForegroundColor Yellow
            $script:LoggerUnavailableWarningShown = $true
        }
        Write-Host "  [INFO] $Message" -ForegroundColor White
    }
}

function Show-Warning {
    param(
        [string]$Message,
        [string]$LogPrefix = "Migration"
    )
    
    # Skip processing if message is empty or null
    if ([string]::IsNullOrEmpty($Message)) {
        return
    }
    
    if (Get-Command Write-HelperLog -ErrorAction SilentlyContinue) {
        Write-HelperLog -Message $Message -Level "Warning" -LogPrefix $LogPrefix -MigrationDirectory $global:MigrationDirectory
    } else {
        if (-not $script:LoggerUnavailableWarningShown) {
            Write-Host "  [WARNING] Logger unavailable - messages will only be displayed in console" -ForegroundColor Yellow
            $script:LoggerUnavailableWarningShown = $true
        }
        Write-Host "  [WARNING] $Message" -ForegroundColor Yellow
    }
}

function Show-Error {
    param(
        [string]$Message,
        [string]$LogPrefix = "Migration"
    )
    
    # Skip processing if message is empty or null
    if ([string]::IsNullOrEmpty($Message)) {
        return
    }
    
    if (Get-Command Write-HelperLog -ErrorAction SilentlyContinue) {
        Write-HelperLog -Message $Message -Level "Error" -LogPrefix $LogPrefix -MigrationDirectory $global:MigrationDirectory
    } else {
        if (-not $script:LoggerUnavailableWarningShown) {
            Write-Host "  [WARNING] Logger unavailable - messages will only be displayed in console" -ForegroundColor Yellow
            $script:LoggerUnavailableWarningShown = $true
        }
        Write-Host "  [ERROR] $Message" -ForegroundColor Red
    }
}

# Function to handle psql output specifically
function Show-PsqlOutput {
    param(
        [string]$Message,
        [string]$LogPrefix = "Psql"
    )
    Show-Info -Message $Message -LogPrefix $LogPrefix
}

# =============================================================================
# UTILITY FUNCTIONS
# =============================================================================

function Test-Administrator {
    return $true
    # AHMAD: This isn't necessary I think
    # Check if the current process is running with elevated privileges
    $identity = [Security.Principal.WindowsIdentity]::GetCurrent()
    $principal = New-Object Security.Principal.WindowsPrincipal($identity)
    return $principal.IsInRole([Security.Principal.WindowsBuiltInRole]::Administrator)
}

function Get-HelperScriptPath {
    param(
        [string]$ScriptFileName = $null
    )

    $finalPath = "";
    $scriptHelperPath = Join-Path $global:ScriptDirectory "helper"
    
    if (Test-Path $scriptHelperPath) {
        $finalPath = $scriptHelperPath
    } else {
        throw "Helper directory not found at: $scriptHelperPath"
    }

    # Not checking if the script file exists, as the error is handled by the caller
    if ($ScriptFileName) {
        $finalPath = Join-Path $finalPath $ScriptFileName
    }

    return $finalPath
}

function Get-PostgresOutputPath {
    return Get-OutputPath
}   

function Get-OracleOutputPath {
    return Get-OutputPath
}

function Get-OracleCsvFilePath {
    param(
        [Parameter(Mandatory=$true)]
        [string]$OracleUser
    )
    return Join-Path $(Get-OracleOutputPath) "$($OracleUser)_$($global:Timestamp)_Oracle.csv"
}

function Get-PostgresCsvFilePath {
    param(
        [Parameter(Mandatory=$true)]
        [string]$PostgresSchema
    )
    return Join-Path $(Get-PostgresOutputPath) "$($PostgresSchema)_$($global:Timestamp)_Postgres.csv"
}

function Get-OutputPath {
    return Join-Path $global:MigrationDirectory "output"
}

function Get-FailedObjectsPath {
    param(
        [string]$SchemaName = "[schema_name]"
    )
    return "$(Get-OutputPath)\$($global:Timestamp)_$($SchemaName)_failed_objects"
}

function Get-SQLFilesPath {
    return Join-Path $global:MigrationDirectory "SQLFiles"
}

function Get-AxiumUpgradePath {
    return Get-HelperScriptPath "AxiumDatabaseUpgrade\AxiumDatabaseUpgrade.exe"
}

function Get-AppSettingsPath {
    return Get-HelperScriptPath "AxiumDatabaseUpgrade"
}

# Function to get the properties file path
function Get-PropertiesFilePath {
    $propertiesPath = Join-Path $global:MigrationDirectory "axiummigration.properties"
    return $propertiesPath
}


# Function to validate PostgreSQL properties
function Test-PostgreSQLProperties {
    param(
        [hashtable]$Properties
    )
    
    # Check if required properties exist
    $requiredProperties = @(
        'POSTGRES_SERVER',
        'POSTGRES_SERVER_PORT', 
        'POSTGRES_DATABASE',
        'POSTGRES_USER',
        'POSTGRES_USER_PASSWORD'
    )
    
    $missingProperties = @()
    foreach ($prop in $requiredProperties) {
        if (-not $Properties.ContainsKey($prop) -or [string]::IsNullOrEmpty($Properties[$prop])) {
            $missingProperties += $prop
        }
    }
    
    if ($missingProperties.Count -gt 0) {
        Show-Error -Message "Missing or empty PostgreSQL properties: $($missingProperties -join ', ')" -LogPrefix "SQLFiles"
        Show-Error -Message "Please ensure all required PostgreSQL connection properties are provided." -LogPrefix "SQLFiles"
        return $false
    }
    
    Show-Info -Message "PostgreSQL properties validation passed." -LogPrefix "SQLFiles"
    return $true
}

# Function to test PostgreSQL connection (admin or regular user)
function Test-PostgreSQLConnection {
    param(
        [hashtable]$Properties,
        [bool]$TestAdmin = $false,
        [bool]$TestDatabase = $false
    )
    
    # Determine which user type to test and set credentials accordingly
    if ($TestAdmin) {
        $user = $Properties['POSTGRES_ADMIN_USER']
        $password = $Properties['POSTGRES_ADMIN_PASSWORD']
        $database = "postgres"
        $userType = "admin"
        $credentialsRequired = "POSTGRES_ADMIN_USER, POSTGRES_ADMIN_PASSWORD"
        $connectionType = "admin user"
    } else {
        $user = $Properties['POSTGRES_USER']
        $password = $Properties['POSTGRES_USER_PASSWORD']
        $database = "postgres"
        $userType = "regular"
        $credentialsRequired = "POSTGRES_USER, POSTGRES_USER_PASSWORD"
        $connectionType = "regular user"
    }
    
    # Override database if TestDatabase parameter is true
    if ($TestDatabase) {
        $database = $Properties['POSTGRES_DATABASE']
    }
    
    # Validate credentials
    if ([string]::IsNullOrEmpty($user) -or [string]::IsNullOrEmpty($password)) {
        Show-Error -Message "Missing PostgreSQL $userType credentials. Required: $credentialsRequired" -LogPrefix "SQLFiles"
        return $false
    }
    
    # Validate database name for regular user
    if (-not $TestAdmin -and [string]::IsNullOrEmpty($database)) {
        Show-Error -Message "Missing PostgreSQL database name. Required: POSTGRES_DATABASE" -LogPrefix "SQLFiles"
        return $false
    }
    
    Show-Info -Message "Testing PostgreSQL $connectionType connection..." -LogPrefix "SQLFiles"
    
    try {
        # Set PGPASSWORD environment variable for psql
        $env:PGPASSWORD = $password
        
        # Build psql arguments
        $psqlArgs = @(
            "-U", $user,
            "-h", $Properties['POSTGRES_SERVER'],
            "-p", $Properties['POSTGRES_SERVER_PORT'],
            "-d", $database,
            "-c", "SELECT version();"
        )
        
        # Test connection using psql
        $result = & psql @psqlArgs 2>&1
        $exitCode = $LASTEXITCODE
        
        # Clear the password from environment
        $env:PGPASSWORD = $null
        
        if ($exitCode -ne 0) {
            if($TestDatabase) {
                Show-Error -Message "PostgreSQL database test failed." -LogPrefix "SQLFiles"
                Show-Error -Message "Note: The target database '$database' does not exist yet. It will be created during migration." -LogPrefix "SQLFiles"
            } else {
            Show-Error -Message "PostgreSQL $connectionType connection test failed." -LogPrefix "SQLFiles"
            Show-Error -Message "Exit code: $exitCode" -LogPrefix "SQLFiles"
            Show-Error -Message "Error output: $result" -LogPrefix "SQLFiles"
            Show-Error -Message "PostgreSQL $connectionType connection test failed. Please verify your PostgreSQL $userType connection settings." -LogPrefix "SQLFiles"
            }
            return $false
        }
        
        Show-Success -Message "PostgreSQL $connectionType connection test successful." -LogPrefix "SQLFiles"
    } catch {
        # Clear the password from environment in case of exception
        $env:PGPASSWORD = $null
        Show-Error -Message "Exception while testing PostgreSQL $connectionType connection: $($_.Exception.Message)" -LogPrefix "SQLFiles"
        Show-Error -Message "PostgreSQL $connectionType connection test failed. Please verify your PostgreSQL $userType connection settings." -LogPrefix "SQLFiles"
        
        # Add note for regular user if database might not exist
        if ($TestDatabase) {      
            Show-Error -Message "Note: The target database '$database' may not exist yet. It will be created during migration if needed." -LogPrefix "SQLFiles"
        }
        
        return $false
    }
    
    return $true
}

# Function to test Oracle connection (both regular and admin users)
function Test-OracleConnection {
    param(
        [hashtable]$Properties,
        [bool]$TestAdmin = $false
    )
    
    try {
        # Check if SQL*Plus is available
        $sqlplusPath = Get-Command sqlplus -ErrorAction SilentlyContinue
        if (-not $sqlplusPath) {
            Show-Error -Message "SQL*Plus is not available in PATH. Please ensure Oracle client is installed and sqlplus is in PATH." -LogPrefix "SQLFiles"
            return $false
        }

        $oracleTns = $Properties['ORACLE_TNS']
        
        # Determine which user type to test and set credentials accordingly
        if ($TestAdmin) {
            $user = $Properties['ORACLE_ADMIN_USER']
            $password = $Properties['ORACLE_ADMIN_PASSWORD']
            $userType = "admin"
            $connectionType = "admin user"
        } else {
            $user = $Properties['ORACLE_USER']
            $password = $Properties['ORACLE_PASSWORD']
            $userType = "regular"
            $connectionType = "regular user"
        }
        
        # Validate credentials
        if ([string]::IsNullOrEmpty($user) -or [string]::IsNullOrEmpty($password)) {
            Show-Error -Message "Missing Oracle $userType user credentials." -LogPrefix "SQLFiles"
            return $false
        }
        
        Show-Info -Message "Testing Oracle $connectionType connection..." -LogPrefix "SQLFiles"
        $result = Write-Output "SELECT 1 FROM DUAL;" | sqlplus -S "$user/$password@$oracleTns" 2>&1
        $exitCode = $LASTEXITCODE
        
        # Check for authentication and connection errors in output
        $resultString = $result -join "`n"
        
        # Determine if there's an error: check exit code, error patterns, or missing query result
        $hasError = ($exitCode -ne 0) -or 
                    ($resultString -match "ORA-\d+:|ERROR|SP2-\d+:|invalid username/password|logon denied|could not resolve") -or
                    (-not ($resultString -match "\s*1\s*"))
        
        if ($hasError) {
            Show-Error -Message "Oracle $connectionType connection test failed." -LogPrefix "SQLFiles"
            Show-Error -Message "Exit code: $exitCode" -LogPrefix "SQLFiles"
            Show-Error -Message "Error output: $resultString" -LogPrefix "SQLFiles"
            return $false
        }
        
        Show-Success -Message "Oracle $connectionType connection test successful." -LogPrefix "SQLFiles"
        return $true
    } catch {
        $connectionType = if ($TestAdmin) { "admin user" } else { "regular user" }
        Show-Error -Message "Exception while testing Oracle $connectionType connection: $($_.Exception.Message)" -LogPrefix "SQLFiles"
        return $false
    }
}

function Get-OracleSqlPlusCommand {
    param(
        [hashtable]$Properties,
        [string]$SqlPlusFile,
        [string]$AdditionalArgs = ""
    )
                                
    # Check if SQL*Plus is available
    $sqlplusPath = Get-Command sqlplus -ErrorAction SilentlyContinue
    if (-not $sqlplusPath) {
        throw "SQL*Plus is not available in PATH. Please ensure Oracle client is installed and sqlplus is in PATH."
    }

    $oracleUser = $Properties['ORACLE_USER']
    $oraclePassword = $Properties['ORACLE_PASSWORD']
    $oracleTns = $Properties['ORACLE_TNS']
    if ($oracleTns -notmatch '^([^:]+):(\d+)/(.+)$') {
        throw "Invalid ORACLE_TNS format. Expected format: host:port/service"
    }

    $connectString = "$oracleUser/{0}@$oracleTns"
    $command = "sqlplus -S $connectString"
    if ($SqlPlusFile) {
        # Quote the script file path if it contains spaces
        if ($SqlPlusFile -match '\s') {
            $command += " `"@$SqlPlusFile`""
        } else {
            $command += " @$SqlPlusFile"
        }
    }
    if ($AdditionalArgs) {
        $command += " $AdditionalArgs"
    }
    
    Show-Info -Message "SQLPlus command: $($command -f "[REDACTED]")" -LogPrefix "SQLFiles"
    
    $command = $command -f $oraclePassword
    return $command
}

# Export the functions
Export-ModuleMember -Function Show-Header, Show-Section, Show-Success, Show-Info, Show-Warning, Show-Error, Show-PsqlOutput, Test-Administrator, Get-HelperScriptPath, Get-OutputPath, Get-PostgresOutputPath, Get-OracleOutputPath, Get-SQLFilesPath, Get-AxiumUpgradePath, Get-AppSettingsPath, Test-PostgreSQLProperties, Test-PostgreSQLConnection, Test-OracleConnection, Get-PropertiesFilePath, Get-FailedObjectsPath, Get-OracleCsvFilePath, Get-PostgresCsvFilePath, Get-OracleSqlPlusCommand
