#!/bin/bash
# Script to check database connection and diagnose issues

echo "=== Database Connection Diagnostic ==="
echo ""

# Check if .env file exists
if [ ! -f .env ]; then
    echo "❌ ERROR: .env file not found!"
    echo "Please create .env file from env.example"
    exit 1
fi

echo "✅ .env file found"
echo ""

# Load environment variables
source .env 2>/dev/null || export $(cat .env | xargs)

echo "Database Configuration:"
echo "  Host: ${DB_HOST:-not set}"
echo "  Port: ${DB_PORT:-not set}"
echo "  User: ${DB_USER:-not set}"
echo "  Database: ${DB_NAME:-not set}"
echo "  SSL Mode: ${DB_SSL_MODE:-not set}"
echo ""

# Check if password is set
if [ -z "$DB_PASSWORD" ]; then
    echo "⚠️  WARNING: DB_PASSWORD is not set or empty!"
    echo ""
fi

# Test PostgreSQL connection
echo "Testing PostgreSQL connection..."
PGPASSWORD="$DB_PASSWORD" psql -h "$DB_HOST" -p "${DB_PORT:-5432}" -U "$DB_USER" -d "$DB_NAME" -c "SELECT version();" 2>&1

if [ $? -eq 0 ]; then
    echo ""
    echo "✅ Database connection successful!"
else
    echo ""
    echo "❌ Database connection failed!"
    echo ""
    echo "Troubleshooting steps:"
    echo "1. Verify the password in .env file is correct"
    echo "2. Check if user '$DB_USER' exists in PostgreSQL"
    echo "3. Verify user has access to database '$DB_NAME'"
    echo "4. Check PostgreSQL is running and accessible from this host"
    echo "5. Verify firewall rules allow connection to $DB_HOST:$DB_PORT"
fi