"""
Test PostgreSQL database connection
"""

import os
from dotenv import load_dotenv
from sqlalchemy import create_engine, text
from sqlalchemy.exc import OperationalError

def test_postgresql_connection():
    """Test PostgreSQL database connection"""
    try:
        print("🔍 Testing PostgreSQL connection...")
        
        # Load environment variables
        load_dotenv()
        
        # Get database URL
        database_url = os.getenv("DATABASE_URL", "")
        print(f"📊 Database URL: {database_url}")
        
        # Create engine
        engine = create_engine(
            database_url,
            pool_pre_ping=True,
            pool_size=5,
            max_overflow=10,
            pool_recycle=300,
            echo=False
        )
        
        # Test connection
        with engine.connect() as connection:
            result = connection.execute(text("SELECT 1"))
            print("✅ PostgreSQL connection successful!")
            
            # Test if we can create tables
            from src.database import create_tables
            create_tables()
            print("✅ Database tables created successfully!")
            
        return True
        
    except OperationalError as e:
        print(f"❌ PostgreSQL connection failed: {e}")
        return False
    except Exception as e:
        print(f"❌ Unexpected error: {e}")
        return False

if __name__ == "__main__":
    print("🚀 Testing PostgreSQL Database Connection...")
    print("=" * 50)
    
    success = test_postgresql_connection()
    
    print("\n" + "=" * 50)
    if success:
        print("✅ PostgreSQL connection test passed!")
    else:
        print("❌ PostgreSQL connection test failed!")