# Helper module for creating axiUm database structure
# This module handles Step 3 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 axiUm database structure
function New-AxiumDatabaseStructure {
    param(
        [hashtable]$Properties,
        [string]$MigrationDirectory
    )
    
    Show-Section "Step 1.3: Create axiUm Database Structure"
    
    # Log the start of Step 3
    try {
        if (Get-Command Write-HelperLog -ErrorAction SilentlyContinue) {
            Write-HelperLog -Message "Starting Step 1.3: Create axiUm Database Structure" -Level "Info" -LogPrefix "Migration"
        }
    } catch {
        # Silently continue if logging fails
    }
    
    # Check if properties are loaded
    if ($Properties.Count -eq 0) {
        Show-Error -Message "Properties not loaded. Please run Step 1 first." -LogPrefix "AxiumStructure"
        return $false
    }
    
    $sqlFile = Get-HelperScriptPath "SQL-Golden-Axium-DDL.sql"
    
    # Check if the SQL file exists
    if (-not (Test-Path $sqlFile)) {
        Show-Error -Message "SQL file not found: $sqlFile" -LogPrefix "AxiumStructure"
        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 "AxiumStructure"
    Show-Info -Message "" -LogPrefix "AxiumStructure"
    Show-Info -Message "  psql -U $($Properties['POSTGRES_USER']) -h $($Properties['POSTGRES_SERVER']) -p $($Properties['POSTGRES_SERVER_PORT']) -d $($Properties['POSTGRES_DATABASE']) -q -f `"$sqlFile`"" -LogPrefix "AxiumStructure"
    Show-Info -Message "" -LogPrefix "AxiumStructure"
    
    # Display file information
    $fileInfo = Get-Item $sqlFile
    Show-Info -Message "SQL File Details:" -LogPrefix "AxiumStructure"
    Show-Info -Message "  File: $($fileInfo.Name)" -LogPrefix "AxiumStructure"
    Show-Info -Message "  Size: $([math]::Round($fileInfo.Length / 1MB, 2)) MB" -LogPrefix "AxiumStructure"
    Show-Info -Message "  Last Modified: $($fileInfo.LastWriteTime)" -LogPrefix "AxiumStructure"
    Show-Info -Message "" -LogPrefix "AxiumStructure"
    
    # Proceed automatically without user confirmation
    Show-Info -Message "Proceeding with creating the axiUm database structure..." -LogPrefix "AxiumStructure"
    
    # Set the PGPASSWORD environment variable to avoid password prompt
    $env:PGPASSWORD = $Properties['POSTGRES_USER_PASSWORD']
    
    Show-Info -Message "Executing psql command to create axiUm database structure..." -LogPrefix "AxiumStructure"
        
    Show-Info "Executing psql command for axiUm database structure creation"
    Show-Info "Database: $($Properties['POSTGRES_DATABASE'])"
    Show-Info "User: $($Properties['POSTGRES_USER'])"
    Show-Info "SQL File: $($fileInfo.Name)"
    Show-Info "File Size: $([math]::Round($fileInfo.Length / 1MB, 2)) MB"
    
    # Log the actual psql command being executed
    $psqlCommand = "psql -U $($Properties['POSTGRES_USER']) -h $($Properties['POSTGRES_SERVER']) -p $($Properties['POSTGRES_SERVER_PORT']) -d $($Properties['POSTGRES_DATABASE']) -q -f `"$sqlFile`""
    Show-Info "PSQL Command: $psqlCommand"
    
    try {
        # Build the psql command arguments
        $psqlArgs = @(
            "-U", $Properties['POSTGRES_USER'],
            "-h", $Properties['POSTGRES_SERVER'],
            "-p", $Properties['POSTGRES_SERVER_PORT'],
            "-d", $Properties['POSTGRES_DATABASE'],
            "-q", "-f", $sqlFile
        )
        
        # Execute psql command and capture output to prevent double display
        Show-Info -Message "Starting axiUm database structure creation..." -LogPrefix "AxiumStructure"
        Show-Info -Message "  This may take several minutes depending on the size of the SQL file..." -LogPrefix "AxiumStructure"
        Show-Info -Message "" -LogPrefix "AxiumStructure"
        
        $startTime = Get-Date
        $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
            }
        }
        $endTime = Get-Date
        $duration = $endTime - $startTime
        
        Show-Info -Message "psql command completed with exit code: $exitCode"
        Show-Info -Message "Execution time: $($duration.TotalSeconds) seconds"
        
        # Display execution summary
        Show-Info -Message "axiUm Database Structure Creation Summary:" -LogPrefix "AxiumStructure"
        Show-Info -Message "" -LogPrefix "AxiumStructure"
        Show-Info -Message "  Execution Time: $($duration.TotalSeconds) seconds" -LogPrefix "AxiumStructure"
        Show-Info -Message "  Exit Code: $exitCode" -LogPrefix "AxiumStructure"
        Show-Info -Message "" -LogPrefix "AxiumStructure"
        
        # Display a formatted summary of the output
        $lines = $result -split "`n" | Where-Object { $_ -match '\S' }
        
        # Count different types of messages
        $noticeCount = 0
        $warningCount = 0
        $errorCount = 0
        $successCount = 0
        
        # Track processed messages to avoid duplicates
        $processedMessages = @{}
        
        foreach ($line in $lines) {
            $logMessage = ""
            $logLevel = "Info"
            
            # Process INFO, WARNING, ERROR, and SUCCESS messages
            if ($line -match "INFO:") {
                $infoMessage = $($line -replace 'INFO:', '').Trim()
                $infoKey = "INFO:$infoMessage"
                
                # Only process if we haven't seen this info before
                if (-not $processedMessages.ContainsKey($infoKey)) {
                    $logMessage = $infoMessage
                    $logLevel = "Info"
                    $processedMessages[$infoKey] = $true
                }
            } elseif ($line -match "WARNING:") {
                $warningMessage = $($line -replace 'WARNING:', '').Trim()
                $warningKey = "WARNING:$warningMessage"
                
                # Only process if we haven't seen this warning before
                if (-not $processedMessages.ContainsKey($warningKey)) {
                    $warningCount++
                    $logMessage = $warningMessage
                    $logLevel = "Warning"
                    $processedMessages[$warningKey] = $true
                }
            } elseif ($line -match "ERROR:") {
                $errorMessage = $($line -replace 'ERROR:', '').Trim()
                $errorKey = "ERROR:$errorMessage"
                
                # Only process if we haven't seen this error before
                if (-not $processedMessages.ContainsKey($errorKey)) {
                    $errorCount++
                    $logMessage = $errorMessage
                    $logLevel = "Error"
                    $processedMessages[$errorKey] = $true
                }
            } elseif ($line -match "CREATE TABLE" -or $line -match "CREATE INDEX" -or $line -match "CREATE SEQUENCE" -or $line -match "CREATE VIEW" -or $line -match "CREATE FUNCTION" -or $line -match "CREATE TRIGGER") {
                $successCount++
                $logMessage = "Created database object"
                $logLevel = "Success"
            } elseif ($line -match "ALTER TABLE" -or $line -match "ALTER SEQUENCE" -or $line -match "ALTER VIEW" -or $line -match "ALTER FUNCTION") {
                $successCount++
                $logMessage = "Modified database object"
                $logLevel = "Success"
            } elseif ($line -match "INSERT" -or $line -match "UPDATE") {
                $successCount++
                $logMessage = "Data operation completed"
                $logLevel = "Success"
            } elseif ($line -match "NOTICE:") {
                $noticeMessage = $($line -replace 'NOTICE:', '').Trim()
                $noticeKey = "NOTICE:$noticeMessage"
                
                # Only process if we haven't seen this notice before
                if (-not $processedMessages.ContainsKey($noticeKey)) {
                    $noticeCount++
                    $logMessage = $noticeMessage
                    $logLevel = "Warning"
                    $processedMessages[$noticeKey] = $true
                }
            }
            # Skip all other lines (query details, etc.)
            
            # Log the message if logging is available and we have a message to log
            if ($logMessage -ne "" -and $null -ne $logMessage) {
                switch ($logLevel) {
                    "Info" { Show-Info -Message $logMessage -LogPrefix "AxiumStructure" }
                    "Warning" { Show-Warning -Message $logMessage -LogPrefix "AxiumStructure" }
                    "Error" { Show-Error -Message $logMessage -LogPrefix "AxiumStructure" }
                    "Success" { Show-Success -Message $logMessage -LogPrefix "AxiumStructure" }
                }
            }
        }

        # Display summary statistics
        if ($successCount -gt 0) {
            Show-Success -Message "  Success Operations: $successCount" -LogPrefix "AxiumStructure"
        }
        if ($noticeCount -gt 0) {
            Show-Warning -Message "  Notices: $noticeCount" -LogPrefix "AxiumStructure"
        }
        if ($warningCount -gt 0) {
            Show-Warning -Message "  Warnings: $warningCount" -LogPrefix "AxiumStructure"
        }
        if ($errorCount -gt 0) {
            Show-Error -Message "  Errors: $errorCount" -LogPrefix "AxiumStructure"
        }
        Show-Info -Message "" -LogPrefix "AxiumStructure"
        
        # Check for SQL errors in the output
        $hasErrors = $false
        if ($result -match "ERROR:") {
            $hasErrors = $true
            Show-Error -Message "SQL errors detected in the output" -LogPrefix "AxiumStructure"
        }
        
        if ($exitCode -eq 0 -and -not $hasErrors) {
            Show-Success -Message "Step 1.3 completed successfully!" -LogPrefix "AxiumStructure"
            Show-Info -Message "" -LogPrefix "AxiumStructure"
            Show-Info -Message "  Database Structure Details:" -LogPrefix "AxiumStructure"
            Show-Info -Message "    Database: $($Properties['POSTGRES_DATABASE'])" -LogPrefix "AxiumStructure"
            Show-Info -Message "    User: $($Properties['POSTGRES_USER'])" -LogPrefix "AxiumStructure"
            Show-Info -Message "    Server: $($Properties['POSTGRES_SERVER']):$($Properties['POSTGRES_SERVER_PORT'])" -LogPrefix "AxiumStructure"
            Show-Info -Message "    SQL File: $($fileInfo.Name)" -LogPrefix "AxiumStructure"
            Show-Info -Message "    File Size: $([math]::Round($fileInfo.Length / 1MB, 2)) MB" -LogPrefix "AxiumStructure"
            Show-Info -Message "    Execution Time: $($duration.TotalSeconds) seconds" -LogPrefix "AxiumStructure"
            Show-Info -Message "" -LogPrefix "AxiumStructure"
            
            Show-Success "axiUm database structure creation completed successfully"
            Show-Info "Database: $($Properties['POSTGRES_DATABASE'])"
            Show-Info "User: $($Properties['POSTGRES_USER'])"
            Show-Info "Server: $($Properties['POSTGRES_SERVER']):$($Properties['POSTGRES_SERVER_PORT'])"
            Show-Info "SQL File: $($fileInfo.Name)"
            Show-Info "File Size: $([math]::Round($fileInfo.Length / 1MB, 2)) MB"
            Show-Info "Execution Time: $($duration.TotalSeconds) seconds"
            Show-Info "Success Operations: $successCount"
            Show-Info "Notices: $noticeCount"
            Show-Info "Warnings: $warningCount"
            Show-Info "Errors: $errorCount"

            return $true
        } else {
            if ($hasErrors) {
                Show-Error -Message "Step 1.3 failed due to SQL errors in the script" -LogPrefix "AxiumStructure"
            } else {
                Show-Error -Message "Step 1.3 failed with exit code: $exitCode" -LogPrefix "AxiumStructure"
            }
            return $false
        }
    } catch {
        Show-Error -Message "Step 1.3 failed: $($_.Exception.Message)" -LogPrefix "AxiumStructure"
        return $false
    }
}

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