# Helper module for dropping sequences and reimporting
# This module handles Step 6 of the migration process
# Import the utilities module for logging and common functions
# This module will use the shared logger instance from Helper-Utilities

# Function to drop sequences and reimport
function Invoke-CleanSchema {
    param(
        [hashtable]$Properties,
        [string]$MigrationDirectory
    )
    
    Show-Section "Step 6: Drop Sequences and Reimport"
    Show-Info "Starting Step 6: Drop Sequences and Reimport" -LogPrefix "Sequences"
    
    # Check if properties are loaded
    if ($Properties.Count -eq 0) {
        Show-Error -Message "Properties not loaded. Please run Step 1 first." -LogPrefix "Sequences"
        return $false
    }
    
    # Proceed automatically without user confirmation
    Show-Info -Message "This step will drop all sequences and reimport sequences for the database: $($Properties['POSTGRES_DATABASE'])" -LogPrefix "Sequences"
    Show-Info -Message "Proceeding with sequence drop and reimport..." -LogPrefix "Sequences"
    
    # Step 1: Drop all sequences using psql
    Show-Info -Message "Step 6.1: Dropping all sequences from schema '$($Properties['POSTGRES_SCHEMA'])'..." -LogPrefix "Sequences"
    
    # First, check if sequences exist in the target schema
    $checkSequencesQuery = @"
SELECT COUNT(*) as sequence_count
FROM pg_sequences
WHERE schemaname = '$($Properties['POSTGRES_SCHEMA'])'
"@
    
    Show-Info -Message "Checking for existing sequences in schema '$($Properties['POSTGRES_SCHEMA'])'..." -LogPrefix "Sequences"
    
    try {
        $checkArgs = @(
            "-U", $Properties['POSTGRES_USER'],
            "-h", $Properties['POSTGRES_SERVER'],
            "-p", $Properties['POSTGRES_SERVER_PORT'],
            "-d", $Properties['POSTGRES_DATABASE'],
            "-q", "-t", "-c", $checkSequencesQuery
        )
        
        $initialSequenceCount = & psql @checkArgs 2>$null | Where-Object { $_ -match '\d+' } | ForEach-Object { $_.Trim() }
        
        if ($initialSequenceCount -and $initialSequenceCount -gt 0) {
            Show-Info -Message "Found $initialSequenceCount sequence(s) in schema '$($Properties['POSTGRES_SCHEMA'])'" -LogPrefix "Sequences"
        } else {
            Show-Warning -Message "No sequences found in schema '$($Properties['POSTGRES_SCHEMA'])'" -LogPrefix "Sequences"
            Show-Info -Message "The drop command will still execute but will not drop any sequences since none exist." -LogPrefix "Sequences"
        }
    } catch {
        Show-Warning -Message "Could not check for existing sequences: $($_.Exception.Message)" -LogPrefix "Sequences"
        $initialSequenceCount = 0
    }
    
    # Get the path to the SQL file
    $sqlFilePath = Get-HelperScriptPath "SQL-Drop-Sequences.sql"
    
    # Display the command that will be executed
    Show-Info -Message "The following psql command will be executed (password will be provided via PGPASSWORD environment variable):" -LogPrefix "Sequences"
    Show-Info -Message "" -LogPrefix "Sequences"
    Show-Info -Message "  psql -U $($Properties['POSTGRES_USER']) -h $($Properties['POSTGRES_SERVER']) -p $($Properties['POSTGRES_SERVER_PORT']) -d $($Properties['POSTGRES_DATABASE']) -v schema_name=`"$($Properties['POSTGRES_SCHEMA'])`" -f `"$sqlFilePath`"" -LogPrefix "Sequences"
    Show-Info -Message "" -LogPrefix "Sequences"
    
    # Set the PGPASSWORD environment variable to avoid password prompt
    $env:PGPASSWORD = $Properties['POSTGRES_USER_PASSWORD']
    
    Show-Info "Executing psql command to drop sequences" -LogPrefix "Sequences"
    Show-Info "Database: $($Properties['POSTGRES_DATABASE'])" -LogPrefix "Sequences"
    Show-Info "User: $($Properties['POSTGRES_USER'])" -LogPrefix "Sequences"
    Show-Info "SQL File: $sqlFilePath" -LogPrefix "Sequences"
    
    try {
        # Execute psql command to drop sequences using SQL file
        $psqlArgs = @(
            "-U", $Properties['POSTGRES_USER'],
            "-h", $Properties['POSTGRES_SERVER'],
            "-p", $Properties['POSTGRES_SERVER_PORT'],
            "-d", $Properties['POSTGRES_DATABASE'],
            "-v", "schema_name=$($Properties['POSTGRES_SCHEMA'])",
            "-f", $sqlFilePath
        )
        
        # Execute psql command and capture output
        $tempOutputFile = [System.IO.Path]::GetTempFileName()
        try {
            & psql @psqlArgs 2>&1 | Out-File -FilePath $tempOutputFile -Encoding UTF8
            $exitCode = $LASTEXITCODE
            $result = Get-Content -Path $tempOutputFile -Encoding UTF8
        } finally {
            if (Test-Path $tempOutputFile) {
                Remove-Item -Path $tempOutputFile -Force
            }
        }
        
        Show-Info "psql command for dropping sequences completed with exit code: $exitCode" -LogPrefix "Sequences"
        
        if ($exitCode -eq 0) {
            # Verify that sequences were actually dropped
            Show-Info -Message "Verifying sequences were dropped..." -LogPrefix "Sequences"
            try {
                $verifyArgs = @(
                    "-U", $Properties['POSTGRES_USER'],
                    "-h", $Properties['POSTGRES_SERVER'],
                    "-p", $Properties['POSTGRES_SERVER_PORT'],
                    "-d", $Properties['POSTGRES_DATABASE'],
                    "-q", "-t", "-c", $checkSequencesQuery
                )
                
                $remainingSequences = & psql @verifyArgs 2>$null | Where-Object { $_ -match '\d+' } | ForEach-Object { $_.Trim() }
                
                if ($remainingSequences -and $remainingSequences -gt 0) {
                    Show-Warning -Message "Warning: $remainingSequences sequence(s) still exist in schema '$($Properties['POSTGRES_SCHEMA'])'. They may not have been dropped properly." -LogPrefix "Sequences"
                    Show-Info -Message "The drop command completed successfully, but some sequences may not have been dropped." -LogPrefix "Sequences"
                } else {
                    if ($initialSequenceCount -and $initialSequenceCount -gt 0) {
                        Show-Success -Message "Sequences dropped successfully!" -LogPrefix "Sequences"
                        Show-Success -Message "Verification complete: All sequences successfully dropped from schema '$($Properties['POSTGRES_SCHEMA'])'" -LogPrefix "Sequences"
                    } else {
                        Show-Info -Message "Drop command completed successfully. No sequences were dropped because none existed in schema '$($Properties['POSTGRES_SCHEMA'])'." -LogPrefix "Sequences"
                    }
                }
            } catch {
                Show-Warning -Message "Could not verify sequence drop: $($_.Exception.Message)" -LogPrefix "Sequences"
                Show-Info -Message "The drop command completed with exit code 0, but verification failed." -LogPrefix "Sequences"
            }
        } else {
            Show-Warning -Message "psql command for dropping sequences completed with exit code: $exitCode" -LogPrefix "Sequences"
            Show-Info -Message "This might be normal if no sequences existed to drop." -LogPrefix "Sequences"
        }
        
    } catch {
        Show-Error -Message "Failed to drop sequences: $($_.Exception.Message)" -LogPrefix "Sequences"
        return $false
    }
    
    # Step 2: Check if runMTK.bat exists
    Show-Info -Message "Step 6.2: Checking for EDB Migration Toolkit..." -LogPrefix "Sequences"
    
    $runMTKPath = Get-MtkInstallationPath -LogPrefix "Sequences"
    Show-Success -Message "EDB Migration Toolkit found at: $runMTKPath" -LogPrefix "Sequences"
    
    # Step 3: Run runMTK.bat command
    Show-Info -Message "Step 6.3: Running EDB Migration Toolkit to reimport sequences..." -LogPrefix "Sequences"
    
    # Build the runMTK command
    $runMTKArgs = @(
        "-schemaOnly",
        "-allSequences",
        "-targetSchema",
        $Properties['POSTGRES_SCHEMA'],
        $Properties['ORACLE_USER']
    )
    
    # Display the command that will be executed
    Show-Info -Message "The following runMTK command will be executed:" -LogPrefix "Sequences"
    Show-Info -Message "" -LogPrefix "Sequences"
    Show-Info -Message "  `"$runMTKPath`" -schemaOnly -allSequences -targetSchema $($Properties['POSTGRES_SCHEMA']) $($Properties['ORACLE_USER'])" -LogPrefix "Sequences"
    Show-Info -Message "" -LogPrefix "Sequences"
    
    Show-Info "Executing runMTK command to reimport sequences" -LogPrefix "Sequences"
    Show-Info "Target Schema: $($Properties['POSTGRES_SCHEMA'])" -LogPrefix "Sequences"
    Show-Info "Oracle User: $($Properties['ORACLE_USER'])" -LogPrefix "Sequences"
    
    try {
        # Change to the directory containing runMTK.bat
        $mtkDir = Split-Path -Parent $runMTKPath
        Push-Location $mtkDir
        
        # Execute runMTK command and capture output
        $tempOutputFile = [System.IO.Path]::GetTempFileName()
        try {
            & $runMTKPath @runMTKArgs 2>&1 | Out-File -FilePath $tempOutputFile -Encoding UTF8
            $exitCode = $LASTEXITCODE
            $result = Get-Content -Path $tempOutputFile -Encoding UTF8
        } finally {
            if (Test-Path $tempOutputFile) {
                Remove-Item -Path $tempOutputFile -Force
            }
            Pop-Location
        }
        
        Show-Info "runMTK command completed with exit code: $exitCode" -LogPrefix "Sequences"
        
        Show-Info -Message "runMTK Command Output:" -LogPrefix "Sequences" 
        Show-Info -Message "" -LogPrefix "Sequences" 
        
        # Parse and display results in a prettier format
        $lines = $result -split "`n" | Where-Object { $_ -match '\S' }
        
        foreach ($line in $lines) {
            $logMessage = ""
            $logLevel = "Info"
            
            if ($line -match "ERROR" -or $line -match "FATAL") {
                $logMessage = "$line"
                $logLevel = "Error"
            } elseif ($line -match "WARNING") {
                $logMessage = "$line"
                $logLevel = "Warning"
            } elseif ($line -match "SUCCESS" -or $line -match "COMPLETED") {
                $logMessage = "$line"
                $logLevel = "Success"
            } elseif ($line -match "INFO" -or $line -match "Processing") {
                $logMessage = "$line"
                $logLevel = "Info"
            } else {
                $logMessage = $line
                $logLevel = "Info"
            }
            
            # Log the message if logging is available (this will also display to console)
            if ($logMessage -ne "") {
                switch ($logLevel) {
                    "Info" { Show-Info -Message $logMessage -LogPrefix "Sequences" }
                    "Warning" { Show-Warning -Message $logMessage -LogPrefix "Sequences" }
                    "Error" { Show-Error -Message $logMessage -LogPrefix "Sequences" }
                    "Success" { Show-Success -Message $logMessage -LogPrefix "Sequences" }
                }
            }
        }
        
        Show-Info -Message "" -LogPrefix "Sequences"
        
        # Check for migration summary to determine success
        $migrationSuccessful = $false
        $failedCount = 0
        $invalidCount = 0
        
        # Look for migration summary pattern
        if ($result -match "Failed count:\s*(\d+)") {
            $failedCount = [int]$matches[1]
        }
        if ($result -match "Invalid count:\s*(\d+)") {
            $invalidCount = [int]$matches[1]
        }
        
        # If we found a migration summary and both failed and invalid counts are 0, consider it successful
        if ($failedCount -eq 0 -and $invalidCount -eq 0 -and ($result -match "Failed count:" -or $result -match "Invalid count:")) {
            $migrationSuccessful = $true
        }
        
        # Check for actual errors (but exclude migration summary lines and successful cases)
        $hasErrors = $false
        if ($result -match "ERROR" -or $result -match "FATAL") {
            # Check if this is just part of a successful migration summary
            $lines = $result -split "`n"
            $actualErrors = @()
            
            foreach ($line in $lines) {
                if ($line -match "ERROR" -or $line -match "FATAL") {
                    # Skip if this is part of a successful migration summary
                    if (-not ($line -match "Failed count:\s*0" -or $line -match "Invalid count:\s*0" -or $line -match "Successful count:")) {
                        $actualErrors += $line
                    }
                }
            }
            
            # Only show errors if we have actual errors AND the migration was not successful
            if ($actualErrors.Count -gt 0 -and -not $migrationSuccessful) {
                $hasErrors = $true
                Show-Error -Message "Errors detected in runMTK output" -LogPrefix "Sequences"
            }
        }
        
        # Determine final success based on migration summary and exit code
        if ($migrationSuccessful -or ($exitCode -eq 0 -and -not $hasErrors)) {
            Show-Success -Message "Step 6 completed successfully!" -LogPrefix "Sequences"
            Show-Info -Message "" -LogPrefix "Sequences"
            Show-Info -Message "  Schema Clean Process Summary:" -LogPrefix "Sequences"
            Show-Info -Message "    Database: $($Properties['POSTGRES_DATABASE'])" -LogPrefix "Sequences"
            Show-Info -Message "    Target Schema: $($Properties['POSTGRES_SCHEMA'])" -LogPrefix "Sequences"
            Show-Info -Message "    Oracle User: $($Properties['ORACLE_USER'])" -LogPrefix "Sequences"
            Show-Info -Message "    Sequences: Dropped and reimported" -LogPrefix "Sequences"
            if ($migrationSuccessful) {
                Show-Info -Message "    Migration Summary: $failedCount failed, $invalidCount invalid" -LogPrefix "Sequences"
            }
            Show-Info -Message "" -LogPrefix "Sequences"
            
            Show-Info "Step 6 completed successfully" -LogPrefix "Sequences"
            Show-Info "Database: $($Properties['POSTGRES_DATABASE'])" -LogPrefix "Sequences"
            Show-Info "Target Schema: $($Properties['POSTGRES_SCHEMA'])" -LogPrefix "Sequences"
            Show-Info "Oracle User: $($Properties['ORACLE_USER'])" -LogPrefix "Sequences"
            Show-Info "Sequences: Dropped and reimported" -LogPrefix "Sequences"
            if ($migrationSuccessful) {
                Show-Info "Migration Summary: $failedCount failed, $invalidCount invalid" -LogPrefix "Sequences"
            }
            return $true
        } else {
            if ($hasErrors) {
                Show-Error -Message "Step 6 failed due to errors in runMTK execution" -LogPrefix "Sequences" 
            } else {
                Show-Error -Message "Step 6 failed with exit code: $exitCode" -LogPrefix "Sequences" 
            }
            return $false
        }
    } catch {
        Show-Error -Message "Step 6 failed: $($_.Exception.Message)" -LogPrefix "Sequences" 
        return $false
    }
}

# Export the main function
Export-ModuleMember -Function Invoke-CleanSchema 