# Helper module for creating PostgreSQL database and user schema
# This module handles Step 2 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 create database and user schema
function New-DatabaseAndSchema {
    param(
        [hashtable]$Properties,
        [string]$MigrationDirectory
    )
    
    Show-Section "Step 1.2: Create Database and User Schema"
    
    Show-Info "Starting Step 1.2: Create Database and User Schema"
    
    # Check if properties are loaded
    if ($Properties.Count -eq 0) {
        Show-Error -Message "Properties not loaded. Please run Step 1 first." -LogPrefix "DatabaseSchema"
        return $false
    }
    
    # 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 "DatabaseSchema"
    Show-Info -Message "" -LogPrefix "DatabaseSchema"
    Show-Info -Message "  psql -U $($Properties['POSTGRES_ADMIN_USER']) -h $($Properties['POSTGRES_SERVER']) -p $($Properties['POSTGRES_SERVER_PORT']) -d postgres" -LogPrefix "DatabaseSchema"
    Show-Info -Message "  -v POSTGRES_DATABASE=$($Properties['POSTGRES_DATABASE'])" -LogPrefix "DatabaseSchema"
    Show-Info -Message "  -v POSTGRES_USER=$($Properties['POSTGRES_USER'])" -LogPrefix "DatabaseSchema"
    Show-Info -Message "  -v POSTGRES_USER_PASSWORD=[REDACTED]" -LogPrefix "DatabaseSchema"
    Show-Info -Message "  -v POSTGRES_SCHEMA=$($Properties['POSTGRES_SCHEMA'])" -LogPrefix "DatabaseSchema"
    Show-Info -Message "  -f `"$(Get-HelperScriptPath "SQL-Create-Db-User-Schema.sql")`"" -LogPrefix "DatabaseSchema"
    Show-Info -Message "" -LogPrefix "DatabaseSchema"
    
    # Proceed automatically without user confirmation
    Show-Info -Message "Proceeding with database and schema creation..." -LogPrefix "DatabaseSchema"
    
    # Set the PGPASSWORD environment variable to avoid password prompt
    $env:PGPASSWORD = $Properties['POSTGRES_ADMIN_PASSWORD']
    
    Show-Info -Message "Executing psql command..." -LogPrefix "DatabaseSchema"
    
    Show-Info "Executing psql command for database creation"
    Show-Info "Database: $($Properties['POSTGRES_DATABASE'])"
    Show-Info "User: $($Properties['POSTGRES_USER'])"
    Show-Info "Schema: $($Properties['POSTGRES_SCHEMA'])"
    
    # Log the actual psql command being executed (without password)
    $psqlCommand = "psql -U $($Properties['POSTGRES_ADMIN_USER']) -h $($Properties['POSTGRES_SERVER']) -p $($Properties['POSTGRES_SERVER_PORT']) -d postgres -v POSTGRES_DATABASE=$($Properties['POSTGRES_DATABASE']) -v POSTGRES_USER=$($Properties['POSTGRES_USER']) -v POSTGRES_USER_PASSWORD=[REDACTED] -v POSTGRES_SCHEMA=$($Properties['POSTGRES_SCHEMA']) -v ON_ERROR_STOP=1 -q -t -A -f `"$(Get-HelperScriptPath "SQL-Create-Db-User-Schema.sql")`""
    Show-Info "PSQL Command: $psqlCommand"
    
    try {
        # Execute psql command with verbose output
        $psqlArgsVerbose = @(
            "-U", $Properties['POSTGRES_ADMIN_USER'],
            "-h", $Properties['POSTGRES_SERVER'],
            "-p", $Properties['POSTGRES_SERVER_PORT'],
            "-d", "postgres",
            "-v", "POSTGRES_DATABASE=$($Properties['POSTGRES_DATABASE'])",
            "-v", "POSTGRES_USER=$($Properties['POSTGRES_USER'])",
            "-v", "POSTGRES_USER_PASSWORD=$($Properties['POSTGRES_USER_PASSWORD'])",
            "-v", "POSTGRES_SCHEMA=$($Properties['POSTGRES_SCHEMA'])",
            "-v", "ON_ERROR_STOP=1",
            "-q", "-t", "-A", "-f", (Get-HelperScriptPath "SQL-Create-Db-User-Schema.sql")
        )
        
        # Execute psql command and capture output to prevent double display
        $tempOutputFile = [System.IO.Path]::GetTempFileName()
        try {
            & psql @psqlArgsVerbose 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 completed with exit code: $exitCode"
        
        # Display a formatted summary
        Show-Info -Message "Database and Schema Creation Summary:" -LogPrefix "DatabaseSchema"
        Show-Info -Message "" -LogPrefix "DatabaseSchema"
        
        # Parse and display results in a prettier format
        $lines = $result -split "`n" | Where-Object { $_ -match '\S' }
        
        foreach ($line in $lines) {
            $logMessage = $line
            $logLevel = "Info"
            
            if ($line -match "NOTICE:") {
                $logLevel = "Warning"
            } elseif ($line -match "WARNING:") {
                $logLevel = "Warning"
            } elseif ($line -match "ERROR:") {
                $logLevel = "Error"
            } 
            
            # Log the message if logging is available (this will also display to console)
            if ($logMessage -ne "") {
                switch ($logLevel) {
                    "Info" { Show-Info -Message $logMessage -LogPrefix "DatabaseSchema" }
                    "Warning" { Show-Warning -Message $logMessage -LogPrefix "DatabaseSchema" }
                    "Error" { Show-Error -Message $logMessage -LogPrefix "DatabaseSchema" }
                    "Success" { Show-Success -Message $logMessage -LogPrefix "DatabaseSchema" }
                }
            }
        }
        
        # Check for errors in the output
        if ($result -match "ERROR:") {
            Show-Error -Message "SQL errors detected in the output" -LogPrefix "DatabaseSchema"
            Show-Error -Message "Step 1.2 failed with exit code: $exitCode" -LogPrefix "DatabaseSchema"
            Show-Error -Message "SQL errors detected in the output" -LogPrefix "DatabaseSchema"
            Show-Error -Message "Step 1.2 failed due to SQL errors in the script" -LogPrefix "DatabaseSchema"
            Show-Error -Message "Database and schema creation failed!" -LogPrefix "DatabaseSchema"
            Show-Error -Message "Step 1 failed" -LogPrefix "DatabaseSchema"
            return $false
        }
        
        # Check exit code
        if ($exitCode -ne 0) {
            Show-Error -Message "Database and schema creation failed with exit code: $exitCode" -LogPrefix "DatabaseSchema"
            Show-Error -Message "Step 1.2 failed" -LogPrefix "DatabaseSchema"
            Show-Error -Message "Step 1 failed" -LogPrefix "DatabaseSchema"
            return $false
        }
        
        Show-Success -Message "Database and schema created successfully!" -LogPrefix "DatabaseSchema"
        Show-Success -Message "Step 1.2 completed successfully!" -LogPrefix "DatabaseSchema"
        return $true
        
    } catch {
        Show-Error -Message "Database and schema creation failed: $($_.Exception.Message)" -LogPrefix "DatabaseSchema"
        Show-Error -Message "Step 1.2 failed" -LogPrefix "DatabaseSchema"
        Show-Error -Message "Step 1 failed" -LogPrefix "DatabaseSchema"
        return $false
    }
}

# Export the main function
Export-ModuleMember -Function New-DatabaseAndSchema 