# PostgreSQL Connection Check Script
# This script checks if PostgreSQL is accessible and ready for HR Portal setup

$ErrorActionPreference = "Continue"

Write-Host "========================================" -ForegroundColor Cyan
Write-Host "  PostgreSQL Connection Check" -ForegroundColor Cyan
Write-Host "========================================" -ForegroundColor Cyan
Write-Host ""

# Configuration
$POSTGRES_HOST = "10.1.0.254"
$POSTGRES_PORT = 5432

# Step 1: Test Network Connectivity
Write-Host "[1/4] Testing network connectivity..." -ForegroundColor Yellow
try {
    $tcpResult = Test-NetConnection -ComputerName $POSTGRES_HOST -Port $POSTGRES_PORT -WarningAction SilentlyContinue

    if ($tcpResult.TcpTestSucceeded) {
        Write-Host "  [OK] Port $POSTGRES_PORT is accessible on $POSTGRES_HOST" -ForegroundColor Green
    } else {
        Write-Host "  [FAIL] Cannot reach port $POSTGRES_PORT on $POSTGRES_HOST" -ForegroundColor Red
        Write-Host "  Please check if PostgreSQL container is running" -ForegroundColor Yellow
        exit 1
    }
} catch {
    Write-Host "  [ERROR] Network test failed: $($_.Exception.Message)" -ForegroundColor Red
    exit 1
}

Write-Host ""

# Step 2: Check Docker Containers
Write-Host "[2/4] Checking Docker containers on remote host..." -ForegroundColor Yellow
try {
    $containers = ssh ubuntu@$POSTGRES_HOST "docker ps --format 'table {{.Names}}\t{{.Image}}\t{{.Status}}' | grep postgres"

    if ($LASTEXITCODE -eq 0) {
        Write-Host "  [OK] PostgreSQL containers found:" -ForegroundColor Green
        Write-Host "  $containers" -ForegroundColor White
    } else {
        Write-Host "  [WARNING] No PostgreSQL containers found or SSH failed" -ForegroundColor Yellow
        Write-Host "  This is not critical if you can connect to database" -ForegroundColor Gray
    }
} catch {
    Write-Host "  [WARNING] Could not check containers: $($_.Exception.Message)" -ForegroundColor Yellow
}

Write-Host ""

# Step 3: Test Database Connection
Write-Host "[3/4] Testing database connection..." -ForegroundColor Yellow
Write-Host "  Please enter postgres password when prompted" -ForegroundColor Gray

try {
    # Test connection to postgres default database
    $testQuery = "SELECT version();"
    $result = ssh ubuntu@$POSTGRES_HOST "docker exec -i postgres psql -U postgres -c `"$testQuery`"" 2>&1

    if ($LASTEXITCODE -eq 0) {
        Write-Host "  [OK] Successfully connected to PostgreSQL" -ForegroundColor Green
        Write-Host "  Version info:" -ForegroundColor Gray
        Write-Host "  $result" -ForegroundColor White
    } else {
        Write-Host "  [FAIL] Could not connect to PostgreSQL" -ForegroundColor Red
        Write-Host "  Error: $result" -ForegroundColor Red
        exit 1
    }
} catch {
    Write-Host "  [ERROR] Connection test failed: $($_.Exception.Message)" -ForegroundColor Red
    exit 1
}

Write-Host ""

# Step 4: Check if hr_portal database exists
Write-Host "[4/4] Checking for existing hr_portal database..." -ForegroundColor Yellow

try {
    $dbCheck = ssh ubuntu@$POSTGRES_HOST "docker exec -i postgres psql -U postgres -lqt | cut -d '|' -f 1 | grep -w hr_portal" 2>&1

    if ($LASTEXITCODE -eq 0) {
        Write-Host "  [INFO] hr_portal database already exists" -ForegroundColor Yellow
        Write-Host "  You may want to drop it before running setup-db-simple.ps1" -ForegroundColor Gray
        Write-Host "  Command: docker exec postgres psql -U postgres -c 'DROP DATABASE hr_portal;'" -ForegroundColor Gray
    } else {
        Write-Host "  [OK] hr_portal database does not exist (ready for setup)" -ForegroundColor Green
    }
} catch {
    Write-Host "  [INFO] Could not check database: $($_.Exception.Message)" -ForegroundColor Gray
}

Write-Host ""
Write-Host "========================================" -ForegroundColor Cyan
Write-Host "  Check completed!" -ForegroundColor Cyan
Write-Host "========================================" -ForegroundColor Cyan
Write-Host ""
Write-Host "Next steps:" -ForegroundColor Yellow
Write-Host "  1. Run setup-db-simple.ps1 to create hr_portal database" -ForegroundColor White
Write-Host "  2. Execute test data insertion if needed" -ForegroundColor White
Write-Host ""