"""MySQL connection management using SQLAlchemy."""

import logging
import os

from dotenv import load_dotenv
from sqlalchemy import create_engine, text
from sqlalchemy.engine import Engine

logger = logging.getLogger(__name__)


def create_mysql_engine() -> Engine:
    """Create a SQLAlchemy engine from environment variables.

    Required env vars: MYSQL_HOST, MYSQL_USERNAME, MYSQL_PASSWORD, MYSQL_DATABASE
    Optional: MYSQL_PORT (default: 3306)
    """
    load_dotenv()

    host = os.getenv('MYSQL_HOST')
    username = os.getenv('MYSQL_USERNAME')
    password = os.getenv('MYSQL_PASSWORD')
    database = os.getenv('MYSQL_DATABASE')
    port = int(os.getenv('MYSQL_PORT', '3306'))

    if not all([host, username, password, database]):
        raise ValueError('Missing required MySQL environment variables (MYSQL_HOST, MYSQL_USERNAME, MYSQL_PASSWORD, MYSQL_DATABASE).')

    url = f'mysql+pymysql://{username}:{password}@{host}:{port}/{database}'
    engine = create_engine(url, pool_pre_ping=True)

    with engine.connect() as conn:
        conn.execute(text('SELECT 1'))
    logger.info(f'Connected to MySQL: {host}:{port}/{database}')

    return engine