# SQL Files Execution Helper Module
# This module provides functions to detect and execute SQL files in the SQLFiles directory
# Usage: Import-Module Helper-Run-SQLFiles.psm1; Invoke-SQLFilesExecution [-Properties]

# Import the utilities module for logging and common functions
# This module will use the shared logger instance from Helper-Utilities

# Function to detect SQL files in SQLFiles directory
function Get-SQLFiles {
    $sqlFilesPath = Get-SQLFilesPath

    if (-not (Test-Path $sqlFilesPath)) {
        Show-Warning -Message "SQLFiles directory not found: $sqlFilesPath" -LogPrefix "SQLFiles"
        return @()
    }

    $sqlFiles = Get-ChildItem -Path $sqlFilesPath -Filter "*.sql" -Recurse | Sort-Object Name

    # Filter out files that have already been processed
    $unprocessedFiles = $sqlFiles | Where-Object { $_.Name -notlike "$($global:ProcessedPrefix)*" }

    if ($unprocessedFiles.Count -eq 0) {
        Show-Info -Message "No unprocessed SQL files found in: $sqlFilesPath" -LogPrefix "SQLFiles"
        return @()
    }

    Show-Info -Message "Found $($unprocessedFiles.Count) unprocessed SQL file(s) in: $sqlFilesPath" -LogPrefix "SQLFiles"
    foreach ($file in $unprocessedFiles) {
        Show-Info -Message "  - $($file.Name)" -LogPrefix "SQLFiles"
    }

    return $unprocessedFiles
}

# Function to execute a single SQL file
function Invoke-SQLFile {
    param(
        [System.IO.FileInfo]$SQLFile,
        [hashtable]$Properties
    )
    
    Show-Info -Message "Executing SQL file: $($SQLFile.Name)" -LogPrefix "SQLFiles" 
    
    try {
        # Build connection string using regular user credentials
        $connectionString = "host=$($Properties['POSTGRES_SERVER']) port=$($Properties['POSTGRES_SERVER_PORT']) dbname=$($Properties['POSTGRES_DATABASE']) user=$($Properties['POSTGRES_USER']) password=$($Properties['POSTGRES_USER_PASSWORD'])"
        
        # Execute the SQL file with verbose output and error checking
        $result = & psql -v ON_ERROR_STOP=0 -f $SQLFile.FullName $connectionString 2>&1
        
        # Check if script was executed
        if ($LASTEXITCODE -eq 0) {
                    
            # Rename the file to mark it as processed
            $processedFileName = Join-Path $SQLFile.Directory.FullName "$($global:ProcessedPrefix)$($SQLFile.Name)"
            try {
                Rename-Item -Path $SQLFile.FullName -NewName $processedFileName -Force
                Show-Success -Message "Successfully executed and marked as processed: $($SQLFile.Name)" -LogPrefix "SQLFiles"
            } catch {
                Show-Warning -Message "Successfully executed but failed to rename file: $($SQLFile.Name) - $($_.Exception.Message)" -LogPrefix "SQLFiles"
            }

            # Check for errors in the output
            if ($result -match "ERROR:" -or $result -match "syntax error" -or $result -match "failed" -or $result -match "invalid" -or $result -match "unexpected") {
                Show-Warning -Message "Failed to execute: $($SQLFile.Name)" -LogPrefix "SQLFiles" 
                Show-Warning -Message $($result | Out-String) -LogPrefix "SQLFiles" 
                return $false
            } else {
                return $true
            }
        } else {
            Show-Error -Message "Error executing: $($SQLFile.Name)" -LogPrefix "SQLFiles" 
            Show-Error -Message "Exit code: $LASTEXITCODE" -LogPrefix "SQLFiles" 
            Show-Error -Message "Error output: $($result | Out-String)" -LogPrefix "SQLFiles"
            return $false

        }
    } catch {
        Show-Error -Message "Exception while executing $($SQLFile.Name): $($_.Exception.Message)" -LogPrefix "SQLFiles" 
        return $false
    }
}

# Function to confirm PostgreSQL properties
function Confirm-PostgreSQLProperties {
    param(
        [hashtable]$Properties
    )
    
    Show-Section "PostgreSQL Connection Properties"
    Show-Info -Message "Current PostgreSQL settings:" -LogPrefix "SQLFiles" 
    Show-Info -Message "  Server: $($Properties['POSTGRES_SERVER'])" -LogPrefix "SQLFiles" 
    Show-Info -Message "  Port: $($Properties['POSTGRES_SERVER_PORT'])" -LogPrefix "SQLFiles" 
    Show-Info -Message "  Database: $($Properties['POSTGRES_DATABASE'])" -LogPrefix "SQLFiles" 
    Show-Info -Message "  User: $($Properties['POSTGRES_USER'])" -LogPrefix "SQLFiles" 
    Show-Info -Message "  Password: [REDACTED]" -LogPrefix "SQLFiles" 
    
    Write-Host ""
    $confirm = Read-Host -Prompt "Do you want to proceed with these PostgreSQL settings? (y/n) (default: y)"
    
    if ($confirm -match "^(no|n)$") {
        Show-Warning -Message "Operation cancelled by user." -LogPrefix "SQLFiles" 
        return $false
    }
    
    return $true
}

# Main function to execute SQL files
function Invoke-SQLFilesExecution {
    param(
        [Parameter(Mandatory = $true)]
        [hashtable]$Properties,
        [Parameter(Mandatory = $false)]
        [bool]$SkipConfirmation = $false
    )
    
    if (-not $SkipConfirmation) {
        Show-Header "SQL Files Execution Tool"
        Show-Info -Message "This tool will detect and execute SQL files in the SQLFiles directory." -LogPrefix "SQLFiles" 
    }

    # Validate properties
    if ($Properties.Count -eq 0) {
        Show-Error -Message "No properties provided. Please provide PostgreSQL connection properties." -LogPrefix "SQLFiles" 
        return $false
    }
    
    # Validate PostgreSQL properties
    if (-not (Test-PostgreSQLProperties -Properties $Properties)) {
        return $false
    }
    
    # Confirm PostgreSQL properties
    if (-not (Confirm-PostgreSQLProperties -Properties $Properties)) {
        return $false
    }

    # Get SQL files
    $sqlFiles = Get-SQLFiles
    if ($sqlFiles.Count -eq 0) {
        Show-Info -Message "No SQL files to execute." -LogPrefix "SQLFiles" 
        return $true
    }
    
    if (-not $SkipConfirmation) {
        # Confirm execution
        Write-Host ""
        $confirm = Read-Host -Prompt "Do you want to execute $($sqlFiles.Count) unprocessed SQL file(s)? (y/n) (default: y)"
        
        if ($confirm -match "^(no|n)$") {
            Show-Warning -Message "SQL file execution cancelled by user." -LogPrefix "SQLFiles" 
            return $false
        }
    }

    # Execute SQL files
    Show-Section "Executing SQL Files"
    
    $successCount = 0
    $failureCount = 0
    
    foreach ($sqlFile in $sqlFiles) {
        if (Invoke-SQLFile -SQLFile $sqlFile -Properties $Properties) {
            $successCount++
        } else {
            $failureCount++
        }
        
        Write-Host ""
    }
    
    # Summary
    Show-Section "Execution Summary"
    Show-Info -Message "Total SQL files: $($sqlFiles.Count)" -LogPrefix "SQLFiles" 
    Show-Info -Message "Successfully executed: $successCount" -LogPrefix "SQLFiles" 
    Show-Info -Message "Failed: $failureCount" -LogPrefix "SQLFiles" 
    
    if ($failureCount -eq 0) {
        Show-Success -Message "All SQL files executed successfully!" -LogPrefix "SQLFiles" 
        return $true
    } else {
        Show-Warning -Message "Some SQL files failed to execute. Please check the error messages above." -LogPrefix "SQLFiles" 
        return $false
    }
}

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

