param(
    [Parameter(Mandatory=$true)]
    [string]$Username,
    
    [Parameter(Mandatory=$true)]
    [string]$Password,
    
    [Parameter(Mandatory=$true)]
    [string]$Database,
    
    [Parameter(Mandatory=$true)]
    [string]$OldSchema,
    
    [Parameter(Mandatory=$true)]
    [string]$NewSchema,
    
    [Parameter(Mandatory=$true)]
    [string]$AdminUsername,
    
    [Parameter(Mandatory=$true)]
    [string]$AdminPassword,
    
    [Parameter(Mandatory=$false)]
    [string]$ServerHost = "localhost",
    
    [Parameter(Mandatory=$false)]
    [int]$Port = 15444,
    
    [Parameter(Mandatory=$false)]
    [switch]$SkipExportImport,
    
    [Parameter(Mandatory=$false)]
    [string]$LogPath = ".\logs"
)

# Create log directory if it doesn't exist
if (-not (Test-Path $LogPath)) {
    New-Item -ItemType Directory -Path $LogPath | Out-Null
}

# Generate log file name with timestamp
$timestamp = Get-Date -Format "yyyyMMdd_HHmmss"
$logFile = Join-Path $LogPath "pg_copy_schema_$timestamp.log"

# Function to write to both console and log file
function Write-Log {
    param($Message)
    $timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
    $logMessage = "[$timestamp] $Message"
    Write-Host $logMessage
    Add-Content -Path $logFile -Value $logMessage
}

# Function to execute psql command
function Invoke-PostgresCommand {
    param(
        $Command,
        [string]$CommandUsername = $Username,
        [string]$CommandPassword = $Password
    )
    $cmd = "psql -h $ServerHost -p $Port -U $CommandUsername -d $Database -q -c `"$Command`""
    Write-Log "Executing command: $cmd"
    $env:PGPASSWORD = $CommandPassword
    $output = Invoke-Expression $cmd 2>&1
    if ($LASTEXITCODE -ne 0) {
        Write-Log "Error executing command: $Command"
        Write-Log "Error output: $output"
        throw "Command failed with exit code $LASTEXITCODE"
    }
    return $output
}

# Function to execute psql file
function Invoke-PostgresFile {
    param(
        [string]$File,
        [string]$FileUsername = $Username,
        [string]$FilePassword = $Password,
        [hashtable]$Variables = @{}
    )
    $varParams = $Variables.GetEnumerator() | ForEach-Object { "-v $($_.Key)=$($_.Value)" }
    $cmd = "psql -h $ServerHost -p $Port -U $FileUsername -q -d $Database $varParams -f `"$File`""
    Write-Log "Executing command: $cmd"
    $env:PGPASSWORD = $FilePassword
    Invoke-Expression $cmd
    if ($LASTEXITCODE -ne 0) {
        throw "Failed to execute file: $File"
    }
}

try {
    Write-Log "Starting schema copy from $OldSchema to $NewSchema"
    
    # Switch back to regular user credentials for remaining operations
    $env:PGPASSWORD = $Password

    if (-not $SkipExportImport) {
        # Rename old schema to new schema
        Write-Log "Renaming schema $OldSchema to $NewSchema"
        try {
            Invoke-PostgresCommand "ALTER SCHEMA $OldSchema RENAME TO $NewSchema"
        }
        catch {
            Write-Log "Rename to schema already exists, assuming this was a failed run.. (DANGEROUS)"
        }

        # Dump the new schema
        Write-Log "Dumping schema $NewSchema to file"
        $dumpFile = "new_schema.sql"
        $env:PGPASSWORD = $Password
        # -O option excludes ownership information
        $dumpCmd = "pg_dump -h $ServerHost -p $Port -U $Username -n $NewSchema -O -f $dumpFile $Database"
        Write-Log "Executing command: $dumpCmd"
        Invoke-Expression $dumpCmd
        if ($LASTEXITCODE -ne 0) {
            throw "Failed to dump schema"
        }
        
        # Rename schema back to original
        Invoke-PostgresCommand "ALTER SCHEMA $NewSchema RENAME TO $OldSchema"

        # Run create_db_user_schema_usedb.sql with admin credentials
        Write-Log "Running create_db_user_schema_usedb.sql with admin credentials"
        Invoke-PostgresFile -File "create_db_user_schema_usedb.sql" -FileUsername $AdminUsername -FilePassword $AdminPassword -Variables @{v1=$NewSchema}
        
        # Import the dumped schema
        Write-Log "Importing schema"
        $NewUsername = $NewSchema + "_user"
        Invoke-PostgresFile -File $dumpFile -FileUsername $NewUsername -FilePassword $NewSchema
        
        # Clean up dump file
        Remove-Item $dumpFile -Force
    } else {
        # Run create_db_user_schema_usedb.sql with admin credentials
        Write-Log "Running create_db_user_schema_usedb.sql with admin credentials"
        Invoke-PostgresFile -File "create_db_user_schema_usedb.sql" -FileUsername $AdminUsername -FilePassword $AdminPassword -Variables @{v1=$NewSchema}

        # Create test table and insert data
        Write-Log "Creating test table and inserting data"
        $NewUsername = $NewSchema + "_user"
        Invoke-PostgresCommand -Command "CREATE TABLE import_test(col integer); INSERT INTO import_test VALUES(1);" -CommandUsername $NewUsername -CommandPassword $NewSchema
    }
            
    # Run vacuum after import
    Write-Log "Running vacuum on database"
    Invoke-PostgresFile -File "vacuum_schema.sql" -FileUsername $NewUsername -FilePassword $NewSchema -Variables @{v1=$NewSchema}
    
    Write-Log "Schema copy completed successfully"
    Write-Log "Log file created at: $logFile"
}
catch {
    Write-Log "Error occurred during copy: $_"
    throw
}
finally {
    # Clear password from environment
    $env:PGPASSWORD = $null
}
