"""
Setup MySQL database user for local development
"""
import pymysql
import os
from dotenv import load_dotenv

load_dotenv()

# Get credentials from .env
db_name = os.getenv('DB_NAME', 'xygbfpsg_graz')
db_user = os.getenv('DB_USER', 'xygbfpsg_graz')
db_password = os.getenv('DB_PASSWORD', ',qdN3O_!}oC67(]W')

print("Setting up MySQL database and user...")
print(f"Database: {db_name}")
print(f"User: {db_user}")

# Connect as root to create user and database
root_password = input("Enter MySQL root password (press Enter if no password): ").strip()

try:
    # Try connecting with the provided password
    connection = pymysql.connect(
        host='localhost',
        user='root',
        password=root_password if root_password else '',
        charset='utf8mb4'
    )
    
    cursor = connection.cursor()
    
    # Create database if it doesn't exist
    print(f"\n1. Creating database '{db_name}'...")
    cursor.execute(f"CREATE DATABASE IF NOT EXISTS `{db_name}` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")
    print("✓ Database created/verified")
    
    # Create user if doesn't exist
    print(f"\n2. Creating user '{db_user}'@'localhost'...")
    try:
        cursor.execute(f"CREATE USER IF NOT EXISTS '{db_user}'@'localhost' IDENTIFIED BY '{db_password}'")
        print("✓ User created")
    except pymysql.err.OperationalError as e:
        if "Operation CREATE USER failed" in str(e):
            print("User already exists, updating password...")
            cursor.execute(f"ALTER USER '{db_user}'@'localhost' IDENTIFIED BY '{db_password}'")
            print("✓ Password updated")
        else:
            raise
    
    # Grant all privileges
    print(f"\n3. Granting privileges on '{db_name}' to '{db_user}'@'localhost'...")
    cursor.execute(f"GRANT ALL PRIVILEGES ON `{db_name}`.* TO '{db_user}'@'localhost'")
    cursor.execute("FLUSH PRIVILEGES")
    print("✓ Privileges granted")
    
    # Test the connection
    print(f"\n4. Testing connection as '{db_user}'...")
    test_conn = pymysql.connect(
        host='localhost',
        user=db_user,
        password=db_password,
        database=db_name,
        charset='utf8mb4'
    )
    test_conn.close()
    print("✓ Connection successful!")
    
    cursor.close()
    connection.close()
    
    print("\n" + "="*50)
    print("✓ Database setup complete!")
    print("="*50)
    print("\nYou can now run: python manage.py runserver")
    
except pymysql.err.OperationalError as e:
    print(f"\n✗ Error: {e}")
    print("\nMake sure:")
    print("1. MySQL is running")
    print("2. You entered the correct root password")
    print("3. Root user has permission to create users and databases")
except Exception as e:
    print(f"\n✗ Unexpected error: {e}")