import os
import sys
from sqlalchemy import create_engine, text
from dotenv import load_dotenv

# Add parent directory to path to import db_connection
sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from db_connection import get_database_url

def test_database_connection():
    """Test database connection and basic operations"""
    
    # Load environment variables
    load_dotenv()
    
    try:
        # Get database URL
        database_url = get_database_url()
        print(f"🔗 Connecting to database...")
        print(f"   Host: {os.getenv('DB_HOST', 'localhost')}")
        print(f"   Port: {os.getenv('DB_PORT', '5432')}")
        print(f"   Database: {os.getenv('DB_NAME', 'kitamove')}")
        print(f"   User: {os.getenv('DB_USERNAME', 'kitamove_user')}")
        
        # Create engine
        engine = create_engine(database_url)
        
        # Test connection
        with engine.connect() as connection:
            print("✅ Database connection successful!")
            
            # Test basic query
            result = connection.execute(text("SELECT version();"))
            version = result.fetchone()[0]
            print(f"📊 PostgreSQL version: {version}")
            
            # Test if tables exist
            result = connection.execute(text("""
                SELECT table_name 
                FROM information_schema.tables 
                WHERE table_schema = 'public'
                ORDER BY table_name;
            """))
            
            tables = [row[0] for row in result.fetchall()]
            if tables:
                print(f"📋 Found tables: {', '.join(tables)}")
            else:
                print("⚠️  No tables found in database")
                
    except Exception as e:
        print(f"❌ Database connection failed: {e}")
        print("\n🔧 Troubleshooting tips:")
        print("1. Check if PostgreSQL is running")
        print("2. Verify .env file exists and has correct credentials")
        print("3. Ensure database 'kitamove' exists")
        print("4. Check firewall settings for port 5432")
        return False
    
    return True

if __name__ == "__main__":
    print("🧪 Testing Database Connection...")
    print("=" * 50)
    
    success = test_database_connection()
    
    print("=" * 50)
    if success:
        print("🎉 Database connection test completed successfully!")
    else:
        print("💥 Database connection test failed!")
        sys.exit(1)
