from typing import Any, Dict, Iterable, List, Optional, Tuple
import MySQLdb
import MySQLdb.cursors
import logging
import os

logger = logging.getLogger(__name__)
logging.basicConfig(level=logging.INFO)


class DBHelper:
    """
    Simple helper around mysqlclient (MySQLdb).
    Usage:
        with DBHelper(host='localhost', user='root', password='pw', database='otherdb') as db:
            rows = db.query("SELECT id, name FROM users WHERE active=%s", (1,))
    """

    def __init__(
        self,
        host: str = "localhost",
        user: str = "root",
        password: str = "",
        database: Optional[str] = None,
        port: int = 3306,
        charset: str = "utf8mb4",
        use_unicode: bool = True,
        connect_timeout: int = 10,
    ):
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.port = port
        self.charset = charset
        self.use_unicode = use_unicode
        self.connect_timeout = connect_timeout
        self.conn: Optional[MySQLdb.connections.Connection] = None

    def connect(self) -> None:
        if self.conn:
            return
        try:
            self.conn = MySQLdb.connect(
                host=self.host,
                user=self.user,
                passwd=self.password,
                db=self.database,
                port=self.port,
                charset=self.charset,
                use_unicode=self.use_unicode,
                connect_timeout=self.connect_timeout,
            )
            # Optional: enable autocommit for simple reads/writes
            self.conn.autocommit(True)
            logger.info("Connected to MySQL db=%s@%s:%s", self.database, self.host, self.port)
        except MySQLdb.Error as e:
            logger.exception("Error connecting to database: %s", e)
            raise

    def close(self) -> None:
        if self.conn:
            try:
                self.conn.close()
                logger.info("Closed MySQL connection")
            except Exception:
                logger.exception("Error closing connection")
            finally:
                self.conn = None

    # Context manager support
    def __enter__(self):
        self.connect()
        return self

    def __exit__(self, exc_type, exc, tb):
        self.close()
        # Do not suppress exceptions
        return False

    def query(self, sql: str, params: Optional[Iterable[Any]] = None) -> List[Dict[str, Any]]:
        """
        Execute a SELECT-style query and return list of dict rows.
        """
        if not self.conn:
            self.connect()

        cur = self.conn.cursor(MySQLdb.cursors.DictCursor)
        try:
            cur.execute(sql, params or ())
            rows = cur.fetchall()  # tuple of dicts
            # Convert to plain list
            return [dict(row) for row in rows]
        except MySQLdb.Error:
            logger.exception("Query failed: %s -- params=%s", sql, params)
            raise
        finally:
            cur.close()

    def execute(self, sql: str, params: Optional[Iterable[Any]] = None) -> int:
        """
        Execute INSERT/UPDATE/DELETE. Returns affected row count.
        """
        if not self.conn:
            self.connect()

        cur = self.conn.cursor()
        try:
            cur.execute(sql, params or ())
            # If autocommit is False, call self.conn.commit() here
            affected = cur.rowcount
            return affected
        except MySQLdb.Error:
            logger.exception("Execute failed: %s -- params=%s", sql, params)
            # If using transactions, you might want self.conn.rollback()
            raise
        finally:
            cur.close()

    def executemany(self, sql: str, seq_of_params: Iterable[Iterable[Any]]) -> int:
        """
        Execute many (bulk) statements. Returns total affected rows.
        """
        if not self.conn:
            self.connect()

        cur = self.conn.cursor()
        try:
            cur.executemany(sql, seq_of_params)
            return cur.rowcount
        except MySQLdb.Error:
            logger.exception("Executemany failed: %s", sql)
            raise
        finally:
            cur.close()