import psycopg2
import os
from config import ADMIN_CONFIG

# Connect to the PostgreSQL database
try:
    # Parse the DATABASE_URL to extract connection parameters
    # Format: postgresql://user:password@host:port/database
    import urllib.parse
    
    parsed = urllib.parse.urlparse(ADMIN_CONFIG['DATABASE_URL'])
    conn = psycopg2.connect(
        host=parsed.hostname,
        port=parsed.port,
        database=parsed.path[1:],  # Remove the leading '/'
        user=parsed.username,
        password=urllib.parse.unquote(parsed.password) if parsed.password else ''  # Decode URL-encoded password
    )
    
    cursor = conn.cursor()
    
    # Check user roles
    print("=== USER ROLES ===")
    cursor.execute("SELECT role_id, role_name, description FROM user_roles")
    roles = cursor.fetchall()
    for role in roles:
        print(f"Role ID: {role[0]}, Role Name: {role[1]}, Description: {role[2]}")
    
    print("\n=== USERS AND THEIR ROLES ===")
    cursor.execute("""
        SELECT u.user_id, u.email, u.first_name, u.last_name, ur.role_name
        FROM users u
        JOIN user_roles ur ON u.role_id = ur.role_id
        WHERE u.is_active = true
    """)
    users = cursor.fetchall()
    for user in users:
        print(f"User ID: {user[0]}, Email: {user[1]}, Name: {user[2]} {user[3]}, Role: {user[4]}")
    
    conn.close()
    
except Exception as e:
    print(f"Error connecting to database: {e}")