tests/test_database_connection.py

"""
Database Connection Test Script

This script helps diagnose database connectivity issues.
Run this to test if your Supabase database is accessible.
"""

import asyncio
import asyncpg
import socket
import os
import sys
from dotenv import load_dotenv

# Load environment variables
load_dotenv()

async def test_database_connection():
    """Test database connection with detailed error reporting."""
    print("🔍 Testing Database Connectivity...")
    print("=" * 50)
    
    # Get database credentials
    db_user = os.getenv('DB_USER')
    db_password = os.getenv('DB_PASSWORD')
    db_host = os.getenv('DB_HOST')
    db_port = os.getenv('DB_PORT', '6543')
    db_name = os.getenv('DB_NAME', 'postgres')
    
    print(f"📋 Database Configuration:")
    print(f"   Host: {db_host}")
    print(f"   Port: {db_port}")
    print(f"   User: {db_user}")
    print(f"   Database: {db_name}")
    print(f"   Password: {'*' * len(db_password) if db_password else 'NOT SET'}")
    print()
    
    # Test 1: DNS Resolution
    print("🌐 Test 1: DNS Resolution...")
    try:
        socket.gethostbyname(db_host)
        print(f"✅ DNS Resolution successful for {db_host}")
    except socket.gaierror as e:
        print(f"❌ DNS Resolution failed: {e}")
        print("💡 Solutions:")
        print("   - Check your internet connection")
        print("   - Try using a different DNS server (8.8.8.8)")
        print("   - Check if your firewall is blocking DNS requests")
        return False
    
    # Test 2: Port Connection
    print("🔌 Test 2: Port Connectivity...")
    try:
        sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
        sock.settimeout(10)
        result = sock.connect_ex((db_host, int(db_port)))
        sock.close()
        
        if result == 0:
            print(f"✅ Port {db_port} is accessible on {db_host}")
        else:
            print(f"❌ Cannot connect to port {db_port} on {db_host}")
            print("💡 Solutions:")
            print("   - Check if your firewall blocks port 6543")
            print("   - Supabase database might be paused (visit your dashboard)")
            print("   - Try using the direct connection (port 5432)")
            return False
    except Exception as e:
        print(f"❌ Port connectivity test failed: {e}")
        return False
    
    # Test 3: Database Authentication
    print("🔐 Test 3: Database Connection...")
    try:
        # Try to connect to the database
        conn = await asyncpg.connect(
            user=db_user,
            password=db_password,
            database=db_name,
            host=db_host,
            port=int(db_port),
            ssl='require'
        )
        
        # Test a simple query
        result = await conn.fetchval('SELECT 1')
        await conn.close()
        
        if result == 1:
            print("✅ Database connection successful!")
            print("✅ Authentication successful!")
            return True
        else:
            print("❌ Database connection failed - unexpected result")
            return False
            
    except Exception as e:
        print(f"❌ Database connection failed: {e}")
        
        error_str = str(e).lower()
        if 'authentication' in error_str or 'password' in error_str:
            print("💡 Authentication issue - check your username/password")
        elif 'database' in error_str and 'does not exist' in error_str:
            print("💡 Database name issue - check your database name")
        elif 'timeout' in error_str:
            print("💡 Connection timeout - database might be paused")
        else:
            print("💡 General connection issue")
            
        print("\n🛠️ Common Solutions:")
        print("1. Visit Supabase Dashboard and check if database is paused")
        print("2. Verify password in Supabase Settings → Database")
        print("3. Try the alternative connection settings below")
        
        return False

def print_alternative_configs():
    """Print alternative database configurations to try."""
    print("\n🔄 Alternative Database Configurations to Try:")
    print("=" * 50)
    
    db_host_original = os.getenv('DB_HOST')
    project_id = db_host_original.split('.')[0] if db_host_original else "your-project"
    
    print("Option 1 - Direct Connection (try if pooler fails):")
    print(f"DB_HOST=db.{project_id.replace('aws-0-ap-south-1', 'hsmtojoigweyexzczjap')}.supabase.co")
    print("DB_PORT=5432")
    print("DB_USER=postgres")
    print()
    
    print("Option 2 - Session Mode Pooler:")
    print(f"DB_HOST=aws-0-ap-south-1.pooler.supabase.com")
    print("DB_PORT=6543") 
    print(f"DB_USER=postgres.{project_id.replace('aws-0-ap-south-1.', '')}")
    print()
    
    print("Option 3 - Transaction Mode Pooler:")
    print(f"DB_HOST=aws-0-ap-south-1.pooler.supabase.com")
    print("DB_PORT=6543")
    print(f"DB_USER=postgres.{project_id.replace('aws-0-ap-south-1.', '')}")
    
def check_supabase_status():
    """Check if Supabase services are running."""
    print("\n🏥 Supabase Service Check:")
    print("=" * 50)
    print("1. Visit: https://status.supabase.com/ to check service status")
    print("2. Visit your Supabase dashboard: https://supabase.com/dashboard")
    print("3. Go to Settings → Database and check if database is active")
    print("4. Look for any 'Database Paused' messages")

async def main():
    """Main function to run all tests."""
    print("🧪 Database Connectivity Diagnostic Tool")
    print("This will help identify why your database connection is failing.\n")
    
    # Check if we have the required environment variables
    required_vars = ['DB_USER', 'DB_PASSWORD', 'DB_HOST']
    missing_vars = []
    
    for var in required_vars:
        if not os.getenv(var):
            missing_vars.append(var)
    
    if missing_vars:
        print(f"❌ Missing environment variables: {missing_vars}")
        print("Please check your .env file")
        return
    
    # Run connectivity tests
    connection_successful = await test_database_connection()
    
    if not connection_successful:
        print_alternative_configs()
        check_supabase_status()
        
        print("\n🚨 IMMEDIATE ACTIONS TO TRY:")
        print("1. Check Supabase Dashboard - database might be paused")
        print("2. Restart your internet connection") 
        print("3. Try different database connection settings above")
        print("4. Contact Supabase support if issue persists")
    else:
        print("\n🎉 SUCCESS! Database connection is working!")
        print("Your FastAPI app should be able to connect to the database.")

if __name__ == "__main__":
    asyncio.run(main())