# PowerShell script to migrate axiUm from Oracle to PostgreSQL
# This script will:
# 1. Create database and user schema with confirmation (includes credential setup)
# 2. Extract and compare schema objects, then migrate missing objects (Sequences, Types, Temp Tables, Tables only)
# 3. Update toolkit properties, run migration data, and handle sequences
# 4. Handle database upgrades
# 5. Migrate Views, Packages, Procedures, Functions, Triggers, Materialized Views
# 6. Execute missing SQL files for Postgres in SQLFiles directory
# 7. Run final comparison report
#
# Implementation Notes:
# - Oracle connectivity requires Oracle client tools (not fully implemented)
# - PostgreSQL connectivity requires Npgsql module
# - MTK tool path is hardcoded to C:\Program Files\edb\mtk\bin
# - Some object types may require manual intervention (types, global temp tables)

param(
    [string]$MigrationDirectory = "C:\AxiUmDatabaseMigration",
    [bool]$SkipOracleDbUpgrade = $true
)

# Import helper modules
$scriptDir = $null
if ($MyInvocation.MyCommand.Path) {
    $scriptDir = Split-Path -Parent $MyInvocation.MyCommand.Path
}
if (-not $scriptDir) {
    $scriptDir = Get-Location
}

$global:ScriptDirectory = $scriptDir

# Set the global migration directory
$global:MigrationDirectory = $MigrationDirectory

$global:Timestamp = Get-Date -Format "yyyyMMdd_HHmmss"

$global:ProcessedPrefix = "DONE_"

# Import utility module for shared helper functions
# hardcoded to avoid duplicatation
$utilityPath = Join-Path $global:ScriptDirectory "helper\Helper-Utilities.psm1"
if (Test-Path $utilityPath) {
    Import-Module $utilityPath -Force
    Write-Host "Imported shared utility module" -ForegroundColor Cyan
} else {
    Write-Host "Warning: Helper utility module not found at $utilityPath" -ForegroundColor Yellow
}


# Import logger module and initialize shared log file for the entire migration process
# All helper modules will use this shared log file instead of creating individual log files
$loggerPath = Get-HelperScriptPath "Helper-Logger.psm1"
if (Test-Path $loggerPath) {
    Import-Module $loggerPath -Force
    
    # Initialize shared log file for the entire migration process
    $sharedLogFile = New-SharedLogFile -MigrationDirectory $global:MigrationDirectory
    Write-Host "Using log file: $sharedLogFile" -ForegroundColor Cyan
    
    # Test the logger to ensure it's working
    Write-HelperLog -Message "Migration script started" -Level "Info" -LogPrefix "Migration" -MigrationDirectory $global:MigrationDirectory
} else {
    Write-Host "Warning: Helper logger module not found at $loggerPath" -ForegroundColor Yellow
}

$encryptionModule = Get-HelperScriptPath "Helper-Encryption.psm1"
if (Test-Path $encryptionModule) {
    Import-Module $encryptionModule -Force
} else {
    Write-Host "Fatal: Encryption module not found at $encryptionModule" -ForegroundColor Red
    exit 1
}

$migrationDataModule = Get-HelperScriptPath "Helper-Run-Migration-Data.psm1"
if (Test-Path $migrationDataModule) {
    Import-Module $migrationDataModule -Force
} else {
    Write-Host "Fatal: Migration data module not found at $migrationDataModule" -ForegroundColor Red
    exit 1
}

Show-Info "Migration Directory: $global:MigrationDirectory" -ForegroundColor Cyan
Show-Info "Script Directory: $global:ScriptDirectory" -ForegroundColor Cyan
Show-Info "Axium Upgrade Path: $(Get-AxiumUpgradePath)" -ForegroundColor Cyan
Show-Info "App Settings Path: $(Get-AppSettingsPath)" -ForegroundColor Cyan
Show-Info "Helper Script Path: $(Get-HelperScriptPath)" -ForegroundColor Cyan
Show-Info "Postgres Output Path: $(Get-PostgresOutputPath)" -ForegroundColor Cyan
Show-Info "Oracle Output Path: $(Get-OracleOutputPath)" -ForegroundColor Cyan
Show-Info "SQL Files Path: $(Get-SQLFilesPath)" -ForegroundColor Cyan
Show-Info "Output Path: $(Get-OutputPath)" -ForegroundColor Cyan
Show-Info "Properties File: $(Get-PropertiesFilePath)" -ForegroundColor Cyan
Show-Info "Skip Oracle DB Upgrade: $SkipOracleDbUpgrade" -ForegroundColor Cyan

# Global variables to store properties
$script:Properties = @{}

# Function to load properties from file
function Read-Properties {
    param([string]$PropertiesFile)

    Show-Info "Loading properties from: $PropertiesFile"

    if (-not (Test-Path $PropertiesFile)) {
        Show-Error "Properties file not found: $PropertiesFile"
        return $false
    }

    try {
        # Check for and convert any plaintext passwords
        $null = Convert-PlaintextPasswords -PropertiesFilePath $PropertiesFile
        $content = Get-Content $PropertiesFile -ErrorAction Stop
        $rawProperties = @{}
        foreach ($line in $content) {
            if ($line -match '^([^=]+)=(.*)$') {
                $key = $matches[1].Trim()
                $value = $matches[2].Trim()
                $rawProperties[$key] = $value
            }
        }
        # Decrypt password properties
        $script:Properties = Unprotect-Properties -Properties $rawProperties

        Show-Success "Successfully loaded $($script:Properties.Count) properties"
        return $true
    } catch {
        Show-Error "Failed to load properties: $($_.Exception.Message)"
        return $false
    }
}

# Function to ensure properties are loaded for a step
function Test-PropertiesLoaded {
    if ($script:Properties.Count -eq 0) {
        $propertiesFile = Get-PropertiesFilePath
        if (Test-Path $propertiesFile) {
            Show-Info "Loading existing properties from: $propertiesFile"
            if (-not (Read-Properties $propertiesFile)) {
                Show-Error "Failed to load properties. Please run Step 1 first."
                return $false
            }
        } else {
            Show-Error "Properties file not found at: $propertiesFile"
            Show-Error "Please run Step 1 first to create the properties file."
            return $false
        }
    }
    return $true
}

# Function to get database credentials (formerly Step 1)
function Invoke-GetDatabaseCredentials {
    Show-Section "Getting Database Credentials"
    Show-Info "This step will prompt you for Oracle and PostgreSQL connection details."
    Show-Info "The credentials will be saved to axiummigration.properties file."
    
    $credentialsModule = Get-HelperScriptPath "Helper-Get-Credentials.psm1"
    if (-not (Test-Path $credentialsModule)) {
        Show-Error "Helper-Get-Credentials.psm1 not found at: $credentialsModule"
        return $false
    }

    # Import the credentials module and call the function
    Import-Module $credentialsModule -Force
    $result = Get-DatabaseCredentials
    if (-not $result) {
        Show-Error "Failed to get database credentials!"
        return $false
    }
    # this should fail in rare cases
    $null = Test-PropertiesLoaded
    # setting toolkitprops not required for all cases, shows a message if it fails
    $null = Set-ToolkitPropertiesHelper -Properties $script:Properties -MigrationDirectory $global:MigrationDirectory -StepName "Database Upgrade (Option 4)"
    return $true 
}




# Function to check for failed objects that require manual fixes
function Assert-FailedObjectsExist {
    param(
        [hashtable]$Properties,
        [string]$MigrationDirectory
    )
    
    try {
        # Look for failed objects files in the migration directory
        $failedObjectsDirs = Join-Path $(Get-OutputPath) "$($Properties['ORACLE_USER'])_$($global:Timestamp)_failed_objects"
        $failedObjectsCsv = Join-Path $failedObjectsDirs "failed_objects_manual_fixes_required.csv"

        if (-not (Test-Path $failedObjectsCsv)) {
            return $false
        }
        
        # Check if there are any failed objects
        $failedObjects = Import-Csv -Path $failedObjectsCsv -ErrorAction SilentlyContinue
        if ($failedObjects -and $failedObjects.Count -gt 0) {
            return $true
        }
        
        return $false
        
    } catch {
        Show-Warning "Error checking for failed objects: $($_.Exception.Message)"
        return $false
    }
}

# Function to update toolkit properties (refactored helper function)
function Set-ToolkitPropertiesHelper {
    param(
        [hashtable]$Properties,
        [string]$MigrationDirectory,
        [string]$StepName = "Unknown"
    )
    
    # Check if properties file exists and has been properly configured
    $propertiesFile = Get-PropertiesFilePath
    if (-not (Test-Path $propertiesFile)) {
        Show-Info "Skipping toolkit properties update - properties file not found. Run Step 1 first to create the properties file."
        return $false
    }
    
    # Check if we have the required properties loaded
    if ($Properties.Count -eq 0) {
        Show-Info "Skipping toolkit properties update - no properties loaded. Run Step 1 first to configure the properties."
        return $false
    }
    
    # Check for required properties
    $requiredProperties = @("ORACLE_TNS", "ORACLE_USER", "ORACLE_PASSWORD", "POSTGRES_SERVER", "POSTGRES_SERVER_PORT", "POSTGRES_DATABASE", "POSTGRES_USER", "POSTGRES_USER_PASSWORD")
    $missingProperties = @()
    foreach ($prop in $requiredProperties) {
        if (-not $Properties.ContainsKey($prop) -or [string]::IsNullOrWhiteSpace($Properties[$prop])) {
            $missingProperties += $prop
        }
    }
    
    if ($missingProperties.Count -gt 0) {
        Show-Info "Skipping toolkit properties update - missing required properties: $($missingProperties -join ', '). Run Step 1 first to configure all properties."
        return $false
    }
    
    Show-Info "Updating toolkit properties for $StepName"
    $toolkitModule = Get-HelperScriptPath "Helper-Update-Toolkit-Properties.psm1"
    if (Test-Path $toolkitModule) {
        try {
            Import-Module $toolkitModule -Force
            $result = Set-ToolkitProperties -Properties $Properties -MigrationDirectory $MigrationDirectory
            if (-not $result) {
                Show-Warning "Toolkit properties update failed for $StepName, but continuing..."
            }
            return $result
        } catch {
            Show-Warning "Toolkit properties update failed for $StepName - $($_.Exception.Message), but continuing..."
            return $false
        }
    } else {
        Show-Warning "Helper-Update-Toolkit-Properties.psm1 not found at: $toolkitModule, but continuing..."
        return $false
    }
}

# Function to create axiUm Database Structure in PostgreSQL (includes credential setup)
function Invoke-CreateAxiumDatabaseStructure {
    Show-Section "Create axiUm Database Structure in PostgreSQL"
    
    # Import the database creation module
    $databaseModule = Get-HelperScriptPath "Helper-Create-Database-Schema.psm1"
    if (Test-Path $databaseModule) {
        try {
            Import-Module $databaseModule -Force
            $result = New-DatabaseAndSchema -Properties $script:Properties -MigrationDirectory $global:MigrationDirectory
            if (-not $result) {
                Show-Error "Database and schema creation failed!"
                return $false
            }
        } catch {
            Show-Error "Database and schema creation failed: $($_.Exception.Message)"
            return $false
        }
    } else {
        Show-Error "Helper-Create-Database-Schema.psm1 not found at: $databaseModule"
        return $false
    }
    
    # Import the axiUm database structure creation module
    $axiumModule = Get-HelperScriptPath "Helper-Create-Axium-Database-Structure.psm1"
    if (Test-Path $axiumModule) {
        try {
            Import-Module $axiumModule -Force
            $result = New-AxiumDatabaseStructure -Properties $script:Properties -MigrationDirectory $global:MigrationDirectory
            if (-not $result) {
                Show-Error "axiUm database structure creation failed!"
                return $false
            }
        } catch {
            Show-Error "axiUm database structure creation failed: $($_.Exception.Message)"
            return $false
        }
    } else {
        Show-Error "Helper-Create-Axium-Database-Structure.psm1 not found at: $axiumModule"
        return $false
    }
    
    Show-Success "Step 1 completed successfully!"
    return $true
}

# Function to run Oracle Database Upgrade
function Invoke-OracleDatabaseUpgrade {
    Show-Section "Run axiUm Database Upgrade in Oracle"
    
    # Import the database upgrade module
    $databaseUpgradeModule = Get-HelperScriptPath "Helper-Run-Database-Upgrade.psm1"
    if (Test-Path $databaseUpgradeModule) {
        try {
            Import-Module $databaseUpgradeModule -Force
            $result = Start-OracleDatabaseUpgrade -Properties $script:Properties -MigrationDirectory $global:MigrationDirectory
            return $result
        } catch {
            Show-Error "Oracle Database Upgrade failed: $($_.Exception.Message)"
            return $false
        }
    } else {
        Show-Error "Oracle Database Upgrade failed: Helper-Run-Database-Upgrade.psm1 not found at: $databaseUpgradeModule"
        return $false
    }
}
function Import-MissingSchemaObjects {
    param(
        [hashtable]$Properties,
        [string[]]$ObjectTypes = $null
    )
    # Show changes summary and ask for confirmation
    # Use the helper function to show changes summary and get user confirmation
    $compareDbModule = Get-HelperScriptPath "Helper-Compare-Oracle-Postgres-Database.psm1"
    $mtkModule = Get-HelperScriptPath "Helper-Run-MTK-Missing-Oracle-Objects.psm1"
    
    try {
        Import-Module $compareDbModule -Force 
    } catch {
        Show-Error "Error loading $compareDbModule : $($_.Exception.Message)"
        return @{ Success = $false; MigrationPerformed = $true }
    }

    try {
        Import-Module $mtkModule -Force 
    } catch {
        Show-Error "Error loading $mtkModule : $($_.Exception.Message)"
        return @{ Success = $false; MigrationPerformed = $true }
    }

    return Invoke-CompareAndImportObjects -Properties $Properties -ObjectTypes $ObjectTypes
}

function Invoke-CompareAndImportObjects {
    param(
        [hashtable]$Properties,
        [string[]]$ObjectTypes = $null,
        [bool]$NeedsAutoFix = $true
    )

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

    # Generate comparison summary
    $shouldMigrate = Show-ChangesSummaryAndMigrateObjects -Properties $script:Properties -ObjectTypes $ObjectTypes -AutoFixAttempted (-not $NeedsAutoFix)

    if ($shouldMigrate.ProceedWithStructureMigration) {
        $result = Import-OracleObjectsToPostgreSQL -Properties $script:Properties -ObjectTypes $ObjectTypes -FailedObjectsDirectory $failedObjectsDirectory
        Write-Output $result

        if (-not $result) {            
            if ($NeedsAutoFix) {
                Show-Warning "Importing objects from Oracle to PostgreSQL failed. There is a possibility to auto fix the failed objects."
                $confirm = Read-Host "Would you like to attempt auto fixing the failed objects? (y/n) (default: y)"
                if ($confirm -match "^(no|n)$") {
                    return @{ Success = $false; MigrationPerformed = $true }
                }

                $autoFixResult = Invoke-AutoProcessFailedFilesInFolder $failedObjectsDirectory

                if ($autoFixResult.FilesUpdated) {
                    # re-run compare any files were executed
                    return Invoke-CompareAndImportObjects -Properties $Properties -ObjectTypes $ObjectTypes -NeedsAutoFix $false
                }
                return @{ Success = $true; MigrationPerformed = $true }
            } 

            Show-Error "MTK migration failed due to critical migration errors!"
            Show-Error "MIGRATION PROCESS STOPPED!"
            # Message not appropriate for all cases
            #Show-Warning "Cannot proceed with data migration!"
            Show-Warning "Failed objects detected that require manual fixes before proceeding."
            Show-Info "Please follow these steps to resolve the issues:"
            Show-Info "1. Review the failed objects in the generated report"
            Show-Info "2. Fix the failed objects and create updated SQL files"
            $sqlFilesPath = Get-SQLFilesPath
            Show-Info "3. Place the updated SQL files in: $sqlFilesPath"
            Show-Info "4. Run Execute Missing SQL Files to Postgres in $sqlFilesPath"
            Show-Info "5. After fixing the issues, you can continue with data migration"
            return @{ Success = $false; MigrationPerformed = $false }
        
        } else {
            Show-Success "Migrate Core Database Objects completed successfully!"
            return @{ Success = $true; MigrationPerformed = $true }
        }
    } else {
        if ($shouldMigrate.CriticalError) {
            return @{ Success = $false; MigrationPerformed = $false }
        } else {
            Show-Info "No migration of missing objects requested"
            Show-Success "Structure migration completed successfully"
            return @{ Success = $true; MigrationPerformed = $false }
        }
    }

    Show-Error "Cannot proceed due to to user selection"
    return @{ Success = $false; MigrationPerformed = $false }    
}

# Function to migrate data from Oracle to Postgres
function Invoke-MigrateDataFromOracleToPostgres {
    Show-Section "Migrate Data from Oracle to Postgres"

    # Run Migration Data Execution
    Show-Info "Running Migration Data Execution..."

    $result = Start-MigrationData -Properties $script:Properties
    if (-not $result) {
        Show-Error "Migration data execution failed!"
        return $false
    }
    return $true
}

function Invoke-ReimportSequences {
    Show-Section "Reimport Sequences"
    # Process Clean Schema and Reimport Sequences
    Show-Info "Processing Clean Schema and Reimporting Sequences..."
    $cleanSchemaModule = Get-HelperScriptPath "Helper-Reimport-Sequences.psm1"
    if (Test-Path $cleanSchemaModule) {
        try {
            Import-Module $cleanSchemaModule -Force
            $result = Invoke-CleanSchema -Properties $script:Properties -MigrationDirectory $global:MigrationDirectory
            if (-not $result) {
                Show-Error "Reimporting sequences failed!"
                return $false
            }
            Show-Success "Migrate Data from Oracle to Postgres completed successfully!"
            return $true
        } catch {
            Show-Error "Reimporting sequences failed: $($_.Exception.Message)"
            return $false
        }
    } else {
        Show-Error "Helper-Reimport-Sequences.psm1 not found at: $cleanSchemaModule"
        return $false
    }
}

# Function to run PostgreSQL Database Upgrade
function Invoke-PostgresDatabaseUpgradeWithPracticeDowngrade {
    Show-Section "Run axiUm Database Upgrade in PostgreSQL"
    
    # Import the database upgrade module
    $databaseUpgradeModule = Get-HelperScriptPath "Helper-Run-Database-Upgrade.psm1"
    if (Test-Path $databaseUpgradeModule) {
        try {
            Import-Module $databaseUpgradeModule -Force
            $result = Start-PostgresDatabaseUpgradewithPracticeDowngrade -Properties $script:Properties -MigrationDirectory $global:MigrationDirectory
            return $result
        } catch {
            Show-Error "PostgreSQL Database Upgrade failed: $($_.Exception.Message)"
            return $false
        }
    } else {
        Show-Error "PostgreSQL Database Upgrade failed: Helper-Run-Database-Upgrade.psm1 not found at: $databaseUpgradeModule"
        return $false
    }
}

# Function to run PostgreSQL Database Upgrade
function Invoke-PostgresDatabaseUpgrade {
    Show-Section "Run axiUm Database Upgrade in PostgreSQL"
    
    # Import the database upgrade module
    $databaseUpgradeModule = Get-HelperScriptPath "Helper-Run-Database-Upgrade.psm1"
    if (Test-Path $databaseUpgradeModule) {
        try {
            Import-Module $databaseUpgradeModule -Force
            $result = Invoke-AxiumDatabasePostgresUpgrade -Properties $script:Properties -MigrationDirectory $global:MigrationDirectory
            return $result
        } catch {
            Show-Error "PostgreSQL Database Upgrade failed: $($_.Exception.Message)"
            return $false
        }
    } else {
        Show-Error "PostgreSQL Database Upgrade failed: Helper-Run-Database-Upgrade.psm1 not found at: $databaseUpgradeModule"
        return $false
    }
}

# Function to migrate missing database objects from Oracle to Postgres (Sequences, Types, Temp Tables, Tables only)
function Invoke-MigrateCoreDatabaseObjects {
    Show-Section "Migrate Core Database Objects (Sequences, Types, Tables)"
    return Import-MissingSchemaObjects -Properties $script:Properties -ObjectTypes @("sequence", "type", "table")
  
}

# Function to migrate advanced database objects (Views, Packages, Procedures, Functions, Triggers, Materialized Views)
function Invoke-MigrateAdvancedDatabaseObjects {
    Show-Section "Migrate non-essential Database Objects (Temp Tables, Views, Packages, Procedures, Functions, Triggers, Materialized Views)"
    return Import-MissingSchemaObjects -Properties $script:Properties -ObjectTypes @("view", "package", "procedure", "function", "trigger", "global temporary table", "materialized view")
}

function Invoke-MigrateAllDatabaseObjects {
    Show-Section "Migrating all Database Objects (Sequences, Types, Tables, Temp Tables, Views, Packages, Procedures, Functions, Triggers, Materialized Views)"
    return Import-MissingSchemaObjects -Properties $script:Properties -ObjectTypes @("sequence", "type", "table","view", "package", "procedure", "function", "trigger", "global temporary table", "materialized view")
}

# Function to execute missing SQL files for Postgres in SQLFiles directory
function Invoke-ExecuteMissingSQLFiles {
    $sqlFilesPath = Get-SQLFilesPath
    Show-Section "Execute Missing SQL Files for Postgres in $sqlFilesPath"
    
    # Execute SQL Files
    Show-Info "Executing SQL Files..."
    $sqlFilesModule = Get-HelperScriptPath "Helper-Run-SQLFiles.psm1"
    if (Test-Path $sqlFilesModule) {
        try {
            Import-Module $sqlFilesModule -Force
            $result = Invoke-SQLFilesExecution -Properties $script:Properties
            if (-not $result) {
                return $false
            }
        } catch {
            Show-Error "SQL files execution failed: $($_.Exception.Message)"
            return $false
        }
    } else {
        Show-Error "Helper-Run-SQLFiles.psm1 not found at: $sqlFilesModule"
        return $false
    }

    Show-Success "Execute Missing SQL Files completed successfully! SQL files have been executed."
    return $true
}

function Invoke-PartialMigration{

    # Validate both database connections before continuing
    Show-Section "Validating Database Connections"
    Show-Info "Testing Oracle and PostgreSQL connections..."

    # Test Oracle connections (admin and regular user)
    $oracleAdminConnectionValid = Test-OracleConnection -Properties $script:Properties -TestAdmin $true
    if (-not $oracleAdminConnectionValid) {
        Show-Error "Oracle admin connection test failed. Please verify your Oracle admin connection settings."
        Invoke-CleanUpAndExit -ExitCode 1
    }
    
    $oracleUserConnectionValid = Test-OracleConnection -Properties $script:Properties -TestAdmin $false
    if (-not $oracleUserConnectionValid) {
        Show-Error "Oracle user connection test failed. Please verify your Oracle user connection settings."
        Invoke-CleanUpAndExit -ExitCode 1
    }

    # Test PostgreSQL connections (admin and regular user)
    $postgresAdminConnectionValid = Test-PostgreSQLConnection -Properties $script:Properties -TestAdmin $true -TestDatabase $false
    if (-not $postgresAdminConnectionValid) {
        Show-Error "PostgreSQL admin connection test failed. Please verify your PostgreSQL admin connection settings."
        Invoke-CleanUpAndExit -ExitCode 1
    }
    
    $postgresUserConnectionValid = Test-PostgreSQLConnection -Properties $script:Properties -TestAdmin $false -TestDatabase $false
    if (-not $postgresUserConnectionValid) {
        Show-Error "PostgreSQL user connection test failed. Please verify your PostgreSQL user connection settings."
        Invoke-CleanUpAndExit -ExitCode 1
    }

    Show-Success "Both database connections validated successfully."

    if (-not $SkipOracleDbUpgrade) {
        Show-Info "Updating Oracle Database..."
        $success = Invoke-OracleDatabaseUpgrade
        if (-not $success) {
            Show-Error "axiUm Database Upgrade failed! Please resolve before continuing"
            Invoke-CleanUpAndExit -ExitCode 1
        }
    }
    Show-Section "Axium Data and Schema Migration"
    Show-Info "This step will run the complete data and schema migration process including:"
    Show-Info "  - Migrate Core Database Objects (Sequences, Types, Tables only)"
    Show-Info "  - Migrate Data from Oracle to Postgres"
    Show-Info "  - Run axiUm Database Upgrade in PostgreSQL"
    Show-Info "  - Migrate Advanced Database Objects (Temp Tables, Views, Packages, Procedures, Functions, Triggers, Materialized Views)"

    # Run Migrate Core Database Objects
    Show-Info "Running Migrate Core Database Objects..."
    $step2Result = Invoke-MigrateCoreDatabaseObjects
    if (-not $step2Result.Success -or -not $step2Result.MigrationPerformed ) {
        Show-Error "Axium migration stopped."
        return $false
    }
    
    # Run Migrate Data from Oracle to Postgres
    Show-Info "Running Migrate Data from Oracle to Postgres..."
    $success = Invoke-MigrateDataFromOracleToPostgres
    if (-not $success) {
        Show-Error "Migrate Data from Oracle to Postgres failed!"
        Invoke-CleanUpAndExit -ExitCode 1
    }

     # Run Migrate Data from Oracle to Postgres
     Show-Info "Reimporting sequences ..."
     $success = Invoke-ReimportSequences
     if (-not $success) {
        Show-Error "Reimporting sequences failed!"
        Invoke-CleanUpAndExit -ExitCode 1
     }
         
    # Run PostgreSQL Database Upgrade
    Show-Info "Running PostgreSQL Database Upgrade..."
    $success = Invoke-PostgresDatabaseUpgradeWithPracticeDowngrade
    if (-not $success) {
        Show-Error "PostgreSQL Database Upgrade failed!"
        Invoke-CleanUpAndExit -ExitCode 1
    }
    
    # Run Migrate Advanced Database Objects
    Show-Info "Running Migrate Non-essential Database Objects..."
    $success = Invoke-MigrateAdvancedDatabaseObjects
    if (-not $success) {
        Show-Error "Migrate Non-essential Database Objects failed!"
        Show-Error "MIGRATION FAILURES DETECTED!"
        Show-Error "The migration process has been stopped due to object failures."
        Show-Info "Please follow these steps to resolve the issues:"
        Show-Info "1. Review the failed objects in: $(Get-FailedObjectsPath $script:Properties['ORACLE_USER'])"
        Show-Info "2. Fix the failed objects and create updated SQL files"
        $sqlFilesPath = Get-SQLFilesPath
        Show-Info "3. Place the updated SQL files in: $sqlFilesPath"
        Show-Info "4. Run Execute Missing SQL Files to Postgres"
        Show-Info "5. After fixing the issues, you can continue with the next step"
        return $false
    }

    return $true
}

# Add new function before the main execution
function Invoke-AutoProcessFailedFiles {
    Show-Header "Attempt Auto Processing of Failed Files"
    
    $schema = $script:Properties['ORACLE_USER'].ToUpper()
    $outputPath = Get-OutputPath
        
    # Find all matching failed objects folders
    $failedFolders = Get-ChildItem -Path $outputPath -Directory | 
        Where-Object { $_.Name -match "^$([regex]::Escape($schema))_\d{8}_\d{6}_failed_objects$" } |
        ForEach-Object {
            $name = $_.Name
            $timestampStr = $name -replace "^${schema}_" -replace "_failed_objects$"
            try {
                $date = [datetime]::ParseExact($timestampStr, 'yyyyMMdd_HHmmss', $null)
            } catch {
                $date = [datetime]::MinValue
            }
            [PSCustomObject]@{
                Folder = $_
                Date = $date
                Name = $name
            }
        } | Sort-Object Date -Descending
    
    if ($failedFolders.Count -eq 0) {
        Show-Warning "No failed objects folders found for schema $schema"
        return $true
    }
    
    Show-Section " Available failed objects folders:"
    #$options = @()
    for ($i = 0; $i -lt $failedFolders.Count; $i++) {
        $folder = $failedFolders[$i].Folder
        $dateStr = $failedFolders[$i].Date.ToString("yyyy-MM-dd HH:mm:ss")
        $csvPath = Join-Path $folder.FullName "failed_objects_manual_fixes_required.csv"
        
        $summary = "No summary available"
        if (Test-Path $csvPath) {
            $data = Import-Csv $csvPath
            $totalObjects = $data.Count
            $types = ($data | Group-Object ObjectType | Select-Object -ExpandProperty Name) -join ', '
            $summary = "Total object types: $totalObjects, Types: $types"
        }
        
        Write-Host "    $($i+1). $dateStr - $($folder.Name) " -ForegroundColor White
        Write-Host "        $summary" -ForegroundColor Gray
        Write-Host ""
    }
    
    $choice = Read-Host "Select folder to process (1-$($failedFolders.Count))"
    if (-not [int]::TryParse($choice, [ref]$null) -or $choice -lt 1 -or $choice -gt $failedFolders.Count) {
        Show-Error "Invalid selection"
        return $false
    }

    $autoProcessResult = Invoke-AutoProcessFailedFilesInFolder $failedFolders[$choice-1].Folder.FullName
    return $autoProcessResult.Success
}

function Invoke-AutoProcessFailedFilesInFolder {
    param(
        [string]$FailedPath,
        [bool]$SkipConfirmation = $false

    )
    if (-not (Test-Path $FailedPath)) {
        Show-Error "Selected folder not found: $FailedPath"
        return @{ Success = $false; FilesUpdated = $false }
    }

    $schema = $script:Properties['ORACLE_USER'].ToUpper()
    $sqlFilesPath = Get-SQLFilesPath

    # preserve existing SQL Files
    if (Test-Path $sqlFilesPath) {
        #check folder if empty
        if ((Get-ChildItem -Path $sqlFilesPath).Count -gt 0) {
            Show-Info "Renaming SQLFiles path to _$($global:Timestamp)_SQLFiles"
            Rename-Item -Path $sqlFilesPath -NewName "_$($global:Timestamp)_SQLFiles" -Force
        }
    }

    # Copy to SQLFiles path
    if (-not (Test-Path $sqlFilesPath)) {
        New-Item -ItemType Directory -Path $sqlFilesPath -Force | Out-Null
    }
    
    # Define replacement rules
    $charRegex = [regex]'(?i)\((\d+) CHAR\)'
    $simpleIntRegex = [regex]'(?i)\bsimple_integer\b'
    $plsqlRegex = [regex]'\$\$PLSQL_UNIT'
    $triggerReturnRegex = [regex]'(?mi)^(\s*)RETURN;'
    $schemaReferenceRegex = [regex]"\b$schema\."
    $numericTypeRegex = [regex]"(?i)NUMBER\(\*,0\)"
    $oracleAnyRegEx = [regex]"\s*=\s*any\s*\("
    $ediColumnRegex = [regex]'\b(?i)EDI\b(?=[,)])'
    
    $replacements = @(
        @{ Pattern = $charRegex; Replacement = '(${1})'; Filter = $null },
        @{ Pattern = $simpleIntRegex; Replacement = 'INTEGER'; Filter = $null },
        @{ Pattern = $plsqlRegex; Replacement = '''$$PLSQL_UNIT'''; Filter = $null },
        @{ Pattern = $numericTypeRegex; Replacement = 'NUMERIC'; Filter = $null },
        @{ Pattern = $oracleAnyRegEx; Replacement = ' IN ('; Filter = $null },
        @{ Pattern = $schemaReferenceRegex; Replacement = ''; Filter = $null },
        @{ Pattern = $triggerReturnRegex; Replacement = '$1RETURN NEW;'; Filter = 'TRIGGER' },
        @{ Pattern = $ediColumnRegex; Replacement = '"EDI"'; Filter = 'VIEW' }
    )
    
    # Check if FailedDbObjects subdirectory exists (where MTK places SQL files)
    $failedDbObjectsPath = Join-Path $FailedPath "FailedDbObjects"
    $searchPath = if (Test-Path $failedDbObjectsPath) { $failedDbObjectsPath } else { $FailedPath }
    
    Show-Info "Searching for SQL files in: $searchPath"
    
    # grabbing all SQL files for now, skipping already processed files
    $pattern = ".*\.sql"
    $files = Get-ChildItem -Path $searchPath -Recurse -File | Where-Object { $_.Name -match $pattern -and $_.Name -notmatch "^$($global:ProcessedPrefix)" }
    
    if ($files.Count -eq 0) {
        Show-Info "No matching failed SQL files found in $searchPath"
        return @{ Success = $true; FilesUpdated = $false }
    }
    
    Show-Info "Found $($files.Count) matching files"
    
    $fixedFiles = @()
    $changeSummary = @()
    
    foreach ($file in $files) {
        # get parent folder name
        $parentFolder = $file.Directory.Name

        Show-Info "Processing file: $($file.FullName)"
        Write-Host ""

        $content = Get-Content $file.FullName -Raw
        $modified = $content
        $changes = 0
        
        foreach ($rep in $replacements) {
            if ($rep.Filter -and $parentFolder -notmatch $rep.Filter) {
                continue
            }
            $mp = $rep.Pattern.Matches($modified)
            $changes += $mp.Count
            $modified = $rep.Pattern.Replace($modified, $rep.Replacement)
        }
        
        if ($modified -ne $content -and $changes -gt 0) {
            $modified = " -- AUTOFIX: $($file.Name)" + "`n" +
                " -- AUTOGENERATED: $(Get-Date)" + "`n" +
                " -- Any changes to this file may be overwritten" + "`n" +
                $modified
            $autofixName = $file.Name -replace '\.sql$', "_AUTOFIX.sql"
            $autofixPath = Join-Path $sqlFilesPath $autofixName
            Set-Content -Path $autofixPath -Value $modified

            # Rename original file to mark it as processed
            $processedName = "$($global:ProcessedPrefix)$($file.Name)"
            $processedPath = Join-Path $file.Directory.FullName $processedName
            Rename-Item -Path $file.FullName -NewName $processedPath -Force

            $fixedFiles += $autofixPath
            $changeSummary += "$($file.Name): $changes changes applied"
        }
    }
    
    $filesUpdated = $false;

    if ($fixedFiles.Count -eq 0) {
        Show-Info "No files needed fixes"
        return @{ Success = $true; FilesUpdated = $false }
    } else {
        $filesUpdated = $true
    }

    # Present summary
    Show-Section "Auto-Fix Summary"
    
    Show-Success "Auto-fix files have been created in $sqlFilesPath"

    Show-Info "Created $($fixedFiles.Count) auto-fix files:"
    foreach ($sum in $changeSummary) {
        Show-Info "  $sum"
    }
    
    Write-Host ""
    if (-not $SkipConfirmation) {  
        $confirm = Read-Host "Apply these fixes to PostgreSQL? (y/n) (default: y)"
        if ($confirm -match "^(no|n)$") {
            Show-Info "Application cancelled"
            return @{ Success = $false; FilesUpdated = $filesUpdated }
        }
    }

    Show-Info "Loading SQL runner module..."
    # Import SQL runner module
    $sqlModule = Get-HelperScriptPath "Helper-Run-SQLFiles.psm1"
    if (Test-Path $sqlModule) {
        Import-Module $sqlModule -Force
        Show-Info "SQL runner module loaded"
    } else {
        Show-Error "SQL runner module not found"
        return @{ Success = $false; FilesUpdated = $filesUpdated }
    }
    
    $applyScripts = Invoke-SQLFilesExecution -Properties $script:Properties -SkipConfirmation $true
    
    return @{ Success = $applyScripts; FilesUpdated = $filesUpdated }
}

# Main execution
Clear-Host
Show-Header "axiUm Database Migration from Oracle to PostgreSQL"

# Set up error handling to ensure errors are visible
$ErrorActionPreference = "Continue"
$PSDefaultParameterValues['*:ErrorAction'] = 'Continue'

# Check for administrator privileges at the beginning
Show-Section "Checking Administrator Privileges"
if (-not (Test-Administrator)) {
    Show-Error "This script requires administrator privileges!"
    Show-Info "Please run PowerShell as Administrator and try again."
    Show-Info "To run as Administrator:"
    Show-Info "  1. Right-click on PowerShell in the Start menu"
    Show-Info "  2. Select 'Run as Administrator'"
    Show-Info "  3. Navigate to this directory and run the script again"
    Write-Host ""
    Show-Info "Press any key to exit..."
    $null = $Host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown")
    exit 1
}
Show-Success "Administrator privileges confirmed!"


$sqlFilesPath = Get-SQLFilesPath

# Display available steps
Show-Section "Available Steps"
Write-Host "  1. Full Migration" -ForegroundColor White
Write-Host "     Creates or recreates the PostgreSQL database (with confirmation if exists), migrates all customizations & data" -ForegroundColor Gray
Write-Host ""
Write-Host "  2. Schema Comparison Report + Migrate Differences" -ForegroundColor White
Write-Host "     Compare Oracle and PostgreSQL schema objects and migrate differences (optional)" -ForegroundColor Gray
Write-Host ""
Write-Host "  3. axiUm Database Upgrade" -ForegroundColor White
Write-Host "     Check database version and run AxiumDatabaseUpgrade (PostgreSQL or Oracle)" -ForegroundColor Gray
Write-Host ""
Write-Host "  4. Apply SQL Files to PostgreSQL" -ForegroundColor White
Write-Host "     For any customizations that failed previously" -ForegroundColor Gray
Write-Host "     Files in $sqlFilesPath are applied" -ForegroundColor Gray
Write-Host ""
Write-Host "  5. Migrate Data + Reimport sequences" -ForegroundColor White
Write-Host "     Does not make any changes to database structure" -ForegroundColor Gray
Write-Host ""
Write-Host "  6. Attempt auto processing of failed files" -ForegroundColor White
Write-Host "     Automatically fix common issues in failed objects and apply them to PostgreSQL" -ForegroundColor Gray
Write-Host ""

# Ask user for step selection
Write-Host "What would you like to do?" -ForegroundColor Cyan

Write-Host "  1. Full Migration" -ForegroundColor White
Write-Host "  2. Schema Comparison Report + Migrate Differences" -ForegroundColor White
Write-Host "  3. axiUm Database Upgrade" -ForegroundColor White
Write-Host "  4. Apply SQL Files to PostgreSQL" -ForegroundColor White
Write-Host "  5. Migrate Data + Reimport sequences" -ForegroundColor White
Write-Host "  6. Attempt auto processing of failed files" -ForegroundColor White
Write-Host "  7. Exit" -ForegroundColor White
Write-Host ""

$choice = Read-Host "Enter your choice (default: 1)"

# Set default choice if user just presses Enter
if ([string]::IsNullOrWhiteSpace($choice)) {
    $choice = "1"
}

$exitCode = 0

if (-not (Invoke-GetDatabaseCredentials)) {
    Invoke-CleanUpAndExit -ExitCode 1
}

# Process user choice
switch ($choice) {
    "1" {
        Show-Info "Running full axiUm migration"

    # Test PostgreSQL connections (admin)
    $postgresAdminConnectionValid = Test-PostgreSQLConnection -Properties $script:Properties -TestAdmin $true -TestDatabase $false
    if (-not $postgresAdminConnectionValid) {
        Show-Error "PostgreSQL admin connection test failed. Please verify your PostgreSQL admin connection settings."
        Invoke-CleanUpAndExit -ExitCode 1
    }
        # Check if database exists
        $dbExists = Test-PostgreSQLConnection -Properties $script:Properties -TestAdmin $true -TestDatabase $true

        if ($dbExists) {
            Show-Info "Database '$($script:Properties['POSTGRES_DATABASE'])' already exists."
            Write-Host ""
            $confirm = Read-Host "Do you want to recreate the database? This will DROP and recreate it. (y/n) (default: n)"
            if ($confirm -match "^(yes|y)$") {
                Show-Info "Recreating database..."
                $success = Invoke-CreateAxiumDatabaseStructure
                if (-not $success) {
                    Show-Error "Create Axium Database Structure failed!"
                    $exitCode = 1
                    break
                }
            } else {
                Show-Info "Skipping database creation. Using existing database."
                Write-Host ""
                $applySql = Read-Host "Do you want to apply any SQL files prior to DDL comparison? (y/n) (default: n)"
                if ($applySql -match "^(yes|y)$") {
                    Show-Info "Applying SQL Files to Postgres..."
                    $success = Invoke-ExecuteMissingSQLFiles
                    if (-not $success) {
                        Show-Error "Applying SQL Files to Postgres failed!"
                        $exitCode = 1
                        break
                    }
                } else {
                    Show-Info "Skipping SQL file application."
                }
            }
        } else {
            Show-Info "Database does not exist. Creating database structure..."
            $success = Invoke-CreateAxiumDatabaseStructure
            if (-not $success) {
                Show-Error "Create Axium Database Structure failed!"
                $exitCode = 1
                break
            }
        }
        
        Show-Info "Continuing with migration..."
        $success = Invoke-PartialMigration
        if (-not $success) {
            Show-Error "Migration failed!"
            $exitCode = 1
            break
        }
        Show-Success "axiUm migration completed successfully!"
    }
    "2" {
        # Run Comparison Report
        Show-Info "Running Comparison Report + Migrate Differences..."
        $success = Invoke-MigrateAllDatabaseObjects
        if (-not $success) {
            Show-Error "Comparison Report + Migrate Differences failed!"
            $exitCode = 1
            break
        }
    }

    "3" {
        Show-Info "Running axiUm Database Upgrade..."
        
        # Ask user which database to run upgrade on
        Write-Host ""
        Write-Host "Which database would you like to run the upgrade on?" -ForegroundColor Cyan
        Write-Host "  1. Oracle" -ForegroundColor White
        Write-Host "  2. PostgreSQL" -ForegroundColor White
        Write-Host "  3. Postgres with practice downgrade to 2025.01.00" -ForegroundColor White
        Write-Host ""
        
        $dbChoice = Read-Host "Enter your choice: "
        
        # Set default choice if user just presses Enter
        if ([string]::IsNullOrWhiteSpace($dbChoice)) {
            $dbChoice = "1"
        }
        
        switch ($dbChoice) {
            "1" {
                Show-Info "Running axiUm Database Upgrade in Oracle..."
                $success = Invoke-OracleDatabaseUpgrade
                if (-not $success) {
                    Show-Error "axiUm Database Upgrade failed!"
                    $exitCode = 1
                    break
                }
                Show-Success "axiUm Database Upgrade in Oracle completed successfully!"
            }
            "2" {
                Show-Info "Running axiUm Database Upgrade in PostgreSQL..."
                $success = Invoke-PostgresDatabaseUpgrade
                if (-not $success) {
                    Show-Error "axiUm Database Upgrade failed!"
                    $exitCode = 1
                    break
                }
                Show-Success "axiUm Database Upgrade in PostgreSQL completed successfully!"
            }
            "3" {
                Show-Info "Running axiUm Database Upgrade in PostgreSQL..."
                $success = Invoke-PostgresDatabaseUpgradeWithPracticeDowngrade
                if (-not $success) {
                    Show-Error "axiUm Database Upgrade failed!"
                    $exitCode = 1
                    break
                }
                Show-Success "axiUm Database Upgrade in PostgreSQL completed successfully!"
            }
            default {
                Show-Error "Invalid choice. Please select 1 for PostgreSQL or 2 for Oracle."
                $exitCode = 1
                break
            }
        }
    }
    "4" {
        Show-Info "Running Execute SQL Files to Postgres..."
        $success = Invoke-ExecuteMissingSQLFiles
        if (-not $success) {
            $exitCode = 1
            break
        }
    }
    "5" {
                # Run Migrate Data from Oracle to Postgres
        Show-Info "Running Migrate Data from Oracle to Postgres + Reimporting sequences..."
        $global:Timestamp = Read-Host "Enter the timestamp for the comparison report (ie: $global:Timestamp)"
        $success = Invoke-MigrateDataFromOracleToPostgres
        if (-not $success) {
            Show-Error "Migrate Data from Oracle to Postgres failed!"
            $exitCode = 1
            break
        }
            # Run Migrate Data from Oracle to Postgres
        Show-Info "Reimporting sequences ..."
        $success = Invoke-ReimportSequences
        if (-not $success) {
            Show-Error "Reimporting sequences failed!"  
            $exitCode = 1
            break
        }
    }
    "6" {
        Show-Info "Running Attempt auto processing of failed files..."
        $success = Invoke-AutoProcessFailedFiles
        if (-not $success) {
            Show-Warning "Review above messages for warnings and errors"
            $exitCode = 1
            break
        }
        Show-Success "Auto processing of failed files completed successfully!"
    }
    "7" {
        Show-Info "Exiting..."
        break
    }
}

Invoke-CleanUpAndExit -ExitCode $exitCode

Write-Host ""
Show-Info "Press any key to exit..."
$null = $Host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown") 