# PostgreSQL restore logic
>&2 echo "Testing PostgreSQL connectivity..."
local postgres_ready=false

for i in {1..10}; do
    if kubectl exec "$pod" -c "$restore_container" -n "$NAMESPACE" -- \
        timeout 10 pg_isready -U "$pg_user" -d "postgres" >/dev/null 2>&1; then
        postgres_ready=true
        break
    fi
    >&2 echo "PostgreSQL not ready yet, waiting... (attempt $i/10)"
    sleep 5
done

if [[ "$postgres_ready" != "true" ]]; then
    >&2 echo "❌ PostgreSQL is not responding after multiple attempts"
    return 1
fi

>&2 echo "PostgreSQL is ready! Proceeding with restore..."
>&2 echo "Preparing database: $db_name"

# First, try to connect as the configured user to postgres database
if kubectl exec "$pod" -c "$restore_container" -n "$NAMESPACE" -- \
    psql -U "$pg_user" -d "postgres" -c "SELECT 1;" >/dev/null 2>&1; then
    >&2 echo "✅ Successfully connected as user: $pg_user"

    # Check if user has superuser privileges
    if kubectl exec "$pod" -c "$restore_container" -n "$NAMESPACE" -- \
        psql -U "$pg_user" -d "postgres" -t -c "SELECT rolsuper FROM pg_roles WHERE rolname='$pg_user';" | grep -q "t"; then
        >&2 echo "✅ User $pg_user has superuser privileges"

        >&2 echo "Terminating connections to database: $db_name"
        kubectl exec "$pod" -c "$restore_container" -n "$NAMESPACE" -- \
            psql -U "$pg_user" -d "postgres" -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '$db_name';" 2>/dev/null || true

        >&2 echo "Dropping database: $db_name"
        kubectl exec "$pod" -c "$restore_container" -n "$NAMESPACE" -- \
            psql -U "$pg_user" -d "postgres" -c "DROP DATABASE IF EXISTS \"$db_name\";" || {
            >&2 echo "❌ Failed to drop database $db_name"
            return 1
        }

        >&2 echo "Creating database: $db_name using template0"
        kubectl exec "$pod" -c "$restore_container" -n "$NAMESPACE" -- \
            psql -U "$pg_user" -d "postgres" -c "CREATE DATABASE \"$db_name\" TEMPLATE template0;" || {
            >&2 echo "❌ Failed to create database $db_name"
            return 1
        }
    else
        >&2 echo "⚠ User $pg_user does not have superuser privileges, trying alternative approach"
        kubectl exec "$pod" -c "$restore_container" -n "$NAMESPACE" -- \
            psql -U "$pg_user" -d "postgres" -c "DROP DATABASE IF EXISTS \"$db_name\";" 2>/dev/null || true
        kubectl exec "$pod" -c "$restore_container" -n "$NAMESPACE" -- \
            psql -U "$pg_user" -d "postgres" -c "CREATE DATABASE \"$db_name\" TEMPLATE template0;" || {
            >&2 echo "❌ Failed to prepare PostgreSQL database $db_name"
            return 1
        }
    fi
else
    >&2 echo "❌ Cannot connect as user: $pg_user"
    return 1
fi

>&2 echo "Importing backup data..."

if gunzip -c "$backup_file" | \
   kubectl exec -i "$pod" -c "$restore_container" -n "$NAMESPACE" -- \
       psql -U "$pg_user" -d "$db_name"; then
    >&2 echo "✅ PostgreSQL restore completed successfully as user: $pg_user"
else
    >&2 echo "❌ Failed to import PostgreSQL backup as user: $pg_user"
    return 1
fi