from typing import Any

import pymysql
import pymysql.cursors

from .models import ExecuteResult
from .secrets import get_secret

_REQUIRED_KEYS = {'host', 'user', 'password', 'database'}


class MySQLConnection:
    def __init__(self, secret_name: str) -> None:
        """Connect to MySQL using credentials from Secrets Manager.

        The secret must be a JSON dict with keys: host, user, password, database.
        An optional port key is supported (defaults to 3306).
        """
        secret = get_secret(secret_name)
        if not isinstance(secret, dict):
            raise ValueError(
                f"Secret '{secret_name}' must be a JSON dict, got {type(secret).__name__}"
            )
        missing = _REQUIRED_KEYS - secret.keys()
        if missing:
            raise ValueError(
                f"Secret '{secret_name}' is missing required keys: {sorted(missing)}"
            )
        self._conn = pymysql.connect(
            host=secret['host'],
            port=int(secret.get('port', 3306)),
            user=secret['user'],
            password=secret['password'],
            database=secret['database'],
            cursorclass=pymysql.cursors.DictCursor,
            autocommit=True,
        )

    def __enter__(self) -> 'MySQLConnection':
        return self

    def __exit__(self, *_: Any) -> None:
        self.close()

    def fetchall(
        self, sql: str, params: tuple[Any, ...] | None = None
    ) -> list[dict[str, Any]]:
        with self._conn.cursor() as cursor:
            cursor.execute(sql, params)
            return cursor.fetchall()

    def fetchone(
        self, sql: str, params: tuple[Any, ...] | None = None
    ) -> dict[str, Any] | None:
        with self._conn.cursor() as cursor:
            cursor.execute(sql, params)
            return cursor.fetchone()

    def execute(self, sql: str, params: tuple[Any, ...] | None = None) -> ExecuteResult:
        with self._conn.cursor() as cursor:
            cursor.execute(sql, params)
            rows = cursor.fetchall() if cursor.description is not None else []
            return ExecuteResult(rowcount=cursor.rowcount or 0, rows=rows)

    def close(self) -> None:
        self._conn.close()