import logging

import aiomysql
import mysql.connector
from mysql.connector import Error

logger = logging.getLogger(__name__)


def connetti_db(cfg: object) -> object:
    """
    Establishes a synchronous connection to a MySQL database.

    DEPRECATED: Use connetti_db_async() for async code.
    This function is kept for backward compatibility with synchronous code
    (e.g., ftp_csv_receiver.py which uses pyftpdlib).

    Args:
        cfg: A configuration object containing database connection parameters.
             It should have the following attributes:
                 - dbuser: The database username.
                 - dbpass: The database password.
                 - dbhost: The database host address.
                 - dbport: The database port number.
                 - dbname: The name of the database to connect to.

    Returns:
        A MySQL connection object if the connection is successful, otherwise None.
    """
    try:
        conn = mysql.connector.connect(user=cfg.dbuser, password=cfg.dbpass, host=cfg.dbhost, port=cfg.dbport, database=cfg.dbname)
        conn.autocommit = True
        logger.info("Connected")
        return conn
    except Error as e:
        logger.error(f"Database connection error: {e}")
        raise  # Re-raise the exception to be handled by the caller


async def connetti_db_async(cfg: object) -> aiomysql.Connection:
    """
    Establishes an asynchronous connection to a MySQL database.

    This is the preferred method for async code. Use this instead of connetti_db()
    in all async contexts to avoid blocking the event loop.

    Args:
        cfg: A configuration object containing database connection parameters.
             It should have the following attributes:
                 - dbuser: The database username.
                 - dbpass: The database password.
                 - dbhost: The database host address.
                 - dbport: The database port number.
                 - dbname: The name of the database to connect to.

    Returns:
        An aiomysql Connection object if the connection is successful.

    Raises:
        Exception: If the connection fails.

    Example:
        async with await connetti_db_async(cfg) as conn:
            async with conn.cursor() as cur:
                await cur.execute("SELECT * FROM table")
    """
    try:
        conn = await aiomysql.connect(
            user=cfg.dbuser,
            password=cfg.dbpass,
            host=cfg.dbhost,
            port=cfg.dbport,
            db=cfg.dbname,
            autocommit=True,
        )
        logger.info("Connected (async)")
        return conn
    except Exception as e:
        logger.error(f"Database connection error (async): {e}")
        raise