#!/usr/bin/env python3
"""
Get exact server connection details for pgAdmin
"""

import os
from sqlalchemy import create_engine, text
from urllib.parse import quote_plus

def get_server_connection_details():
    """Show exact connection details for pgAdmin"""
    
    print("🔍 POSTGRESQL SERVER CONNECTION DETAILS")
    print("=" * 50)
    
    # Test all possible databases and show connection info
    passwords = ["password", "f@k0s!!", "postgres", "admin", ""]
    databases = ["bookbeach", "postgres"]
    
    successful_connections = []
    
    for db_name in databases:
        print(f"\n📂 Testing database: {db_name}")
        
        for pwd in passwords:
            try:
                pwd_encoded = quote_plus(pwd) if pwd else ""
                DATABASE_URL = f"postgresql://postgres:{pwd_encoded}@localhost:5432/{db_name}"
                
                engine = create_engine(DATABASE_URL)
                with engine.connect() as conn:
                    # Test connection
                    result = conn.execute(text("SELECT current_database(), current_user, version()"))
                    db_info = result.fetchone()
                
                connection_info = {
                    'database': db_name,
                    'host': 'localhost',
                    'port': '5432',
                    'username': 'postgres',
                    'password': pwd,
                    'current_db': db_info[0],
                    'current_user': db_info[1],
                    'version': db_info[2][:50] + '...'
                }
                
                successful_connections.append(connection_info)
                print(f"   ✅ SUCCESS with password: '{pwd}'")
                
                # Check tables in this database
                with engine.connect() as conn:
                    tables = conn.execute(text("""
                        SELECT table_name FROM information_schema.tables 
                        WHERE table_schema = 'public'
                        ORDER BY table_name
                    """)).fetchall()
                    
                    if tables:
                        print(f"      Tables: {[t.table_name for t in tables]}")
                        
                        # Check users table specifically
                        if any(t.table_name == 'users' for t in tables):
                            users = conn.execute(text("SELECT COUNT(*) FROM users")).fetchone()
                            print(f"      Users table: {users[0]} records")
                            
                            if users[0] > 0:
                                user_list = conn.execute(text("""
                                    SELECT user_id, email, first_name 
                                    FROM users ORDER BY user_id LIMIT 5
                                """)).fetchall()
                                
                                print(f"      Sample users:")
                                for user in user_list:
                                    print(f"         • {user.first_name} ({user.email})")
                    else:
                        print(f"      No tables found")
                
                break  # Found working password for this database
                
            except Exception as e:
                continue
        else:
            print(f"   ❌ No working password found for {db_name}")
    
    print(f"\n🎯 PGADMIN CONNECTION DETAILS:")
    print("=" * 40)
    
    if successful_connections:
        for i, conn in enumerate(successful_connections, 1):
            print(f"\n{i}. Database: {conn['database']}")
            print(f"   Host: {conn['host']}")
            print(f"   Port: {conn['port']}")
            print(f"   Username: {conn['username']}")
            print(f"   Password: {conn['password']}")
            print(f"   Current DB: {conn['current_db']}")
            print(f"   Current User: {conn['current_user']}")
            
        print(f"\n📋 FOR PGADMIN SETUP:")
        print(f"   Server Name: BookBeach PostgreSQL")
        print(f"   Host: localhost")
        print(f"   Port: 5432")
        print(f"   Username: postgres")
        print(f"   Password: password")  # Most likely working password
        print(f"   Database: bookbeach")
        
        print(f"\n⚠️  IMPORTANT:")
        print(f"   • Make sure you're looking at the 'bookbeach' database")
        print(f"   • Not the 'postgres' system database")
        print(f"   • The users should be in: bookbeach > Schemas > public > Tables > users")
        
    else:
        print("❌ No successful database connections found!")
        print("PostgreSQL server might not be running")

if __name__ == "__main__":
    get_server_connection_details()