#!/usr/bin/env python3
"""
Test MySQL connection with different credentials to diagnose the issue.
"""
import pymysql
from dotenv import load_dotenv
import os

load_dotenv()

DB_NAME = os.getenv('DB_NAME', 'xygbfpsg_loans')
DB_USER = os.getenv('DB_USER', 'xygbfpsg_graz')
DB_PASSWORD = os.getenv('DB_PASSWORD', '')
DB_HOST = os.getenv('DB_HOST', 'localhost')
DB_PORT = int(os.getenv('DB_PORT', 3306))

print(f"\n{'='*60}")
print(f"  MySQL Connection Test")
print(f"{'='*60}\n")
print(f"  Host: {DB_HOST}:{DB_PORT}")
print(f"  User: {DB_USER}")
print(f"  Database: {DB_NAME}")
print(f"  Password: {'*' * len(DB_PASSWORD)}")
print(f"\n{'─'*60}\n")

# Test 1: Try to connect without specifying database
print("Test 1: Connecting without database selection...")
try:
    conn = pymysql.connect(
        host=DB_HOST,
        port=DB_PORT,
        user=DB_USER,
        password=DB_PASSWORD,
        charset='utf8mb4'
    )
    print("  ✓ SUCCESS: User credentials are valid!")
    
    # Check if database exists
    cursor = conn.cursor()
    cursor.execute("SHOW DATABASES LIKE %s", (DB_NAME,))
    result = cursor.fetchone()
    
    if result:
        print(f"  ✓ Database '{DB_NAME}' exists")
    else:
        print(f"  ✗ Database '{DB_NAME}' does NOT exist")
        print(f"\n  Available databases for this user:")
        cursor.execute("SHOW DATABASES")
        for (db,) in cursor.fetchall():
            print(f"    - {db}")
    
    cursor.close()
    conn.close()
    
except pymysql.err.OperationalError as e:
    print(f"  ✗ FAILED: {e}")
    print(f"\n  Possible issues:")
    print(f"    1. Wrong username or password")
    print(f"    2. User doesn't exist in MySQL")
    print(f"    3. User doesn't have permission from 'localhost'")
    print(f"\n  Solutions:")
    print(f"    • Use MySQL Workbench to check/create the user")
    print(f"    • Reset MySQL root password if needed")
    print(f"    • Run: CREATE USER '{DB_USER}'@'localhost' IDENTIFIED BY 'your_password';")
    print(f"    • Run: GRANT ALL PRIVILEGES ON {DB_NAME}.* TO '{DB_USER}'@'localhost';")

print(f"\n{'='*60}\n")

# Test 2: Try with root (if user wants to try manually)
print("If you want to test with root user, enter root password")
print("(or press Enter to skip):")
root_password = input("Root password: ").strip()

if root_password:
    print("\nTest 2: Connecting as root...")
    try:
        conn = pymysql.connect(
            host=DB_HOST,
            port=DB_PORT,
            user='root',
            password=root_password,
            charset='utf8mb4'
        )
        print("  ✓ SUCCESS: Root connection works!")
        
        cursor = conn.cursor()
        
        # Create the user if it doesn't exist
        print(f"\n  Creating/updating user '{DB_USER}'...")
        try:
            cursor.execute(f"DROP USER IF EXISTS '{DB_USER}'@'localhost';")
            cursor.execute(f"CREATE USER '{DB_USER}'@'localhost' IDENTIFIED BY '{DB_PASSWORD}';")
            cursor.execute(f"CREATE DATABASE IF NOT EXISTS {DB_NAME} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;")
            cursor.execute(f"GRANT ALL PRIVILEGES ON {DB_NAME}.* TO '{DB_USER}'@'localhost';")
            cursor.execute("FLUSH PRIVILEGES;")
            conn.commit()
            print(f"  ✓ User '{DB_USER}' created successfully!")
            print(f"  ✓ Database '{DB_NAME}' ready!")
            print(f"\n  You can now run: python create_admin.py")
        except Exception as e:
            print(f"  ✗ Error creating user: {e}")
        
        cursor.close()
        conn.close()
        
    except pymysql.err.OperationalError as e:
        print(f"  ✗ FAILED: {e}")
        print(f"  The root password is incorrect.")

print(f"\n{'='*60}\n")