import os
import mysql.connector
from mysql.connector import Error


class DatabaseConnectionError(Exception):
    """Raised when the application cannot connect to MySQL."""


class DatabaseConfig:
    """Database configuration for AI Event Photo System."""

    DB_HOST     = os.getenv("DB_HOST", "127.0.0.1")
    DB_PORT     = int(os.getenv("DB_PORT", 3306))
    DB_USER     = os.getenv("DB_USER", "root")
    DB_PASSWORD = os.getenv("DB_PASSWORD", "")
    DB_NAME     = os.getenv("DB_NAME", "event_ai")


def get_db_connection():
    """Create and return a live MySQL connection or raise a clear error."""
    try:
        connection = mysql.connector.connect(
            host=DatabaseConfig.DB_HOST,
            port=DatabaseConfig.DB_PORT,
            user=DatabaseConfig.DB_USER,
            password=DatabaseConfig.DB_PASSWORD,
            database=DatabaseConfig.DB_NAME,
        )
    except Error as exc:
        raise DatabaseConnectionError(
            "Failed to connect to MySQL. "
            f"Host={DatabaseConfig.DB_HOST}, Port={DatabaseConfig.DB_PORT}, "
            f"User={DatabaseConfig.DB_USER}, Database={DatabaseConfig.DB_NAME}. "
            f"MySQL error: {exc}"
        ) from exc

    if connection is None or not connection.is_connected():
        raise DatabaseConnectionError(
            "Failed to connect to MySQL. mysql.connector did not return a live connection."
        )

    return connection


def test_db_connection():
    """Test DB connection."""
    connection = None
    try:
        connection = get_db_connection()
        print("Database connected successfully")
        return True
    except DatabaseConnectionError as exc:
        print(f"Database connection failed: {exc}")
        return False
    finally:
        if connection and connection.is_connected():
            connection.close()