"""
DSE (Dhaka Stock Exchange) remote MySQL connection factory.

Provides an async context-manager that opens an aiomysql connection to the
DSE data source and guarantees the connection is closed on exit.

Credentials are loaded from settings (dse_host / dse_user / dse_password /
dse_database / dse_port), which map to the .env keys:
  dse_host, dse_port, dse_user, dse_password, dse_database

Usage:
    from app.core.dse_client import dse_connection

    async with dse_connection() as conn:
        async with conn.cursor(aiomysql.DictCursor) as cur:
            await cur.execute("SELECT * FROM TRD")
            rows = await cur.fetchall()
"""

from __future__ import annotations

from contextlib import asynccontextmanager
from typing import AsyncGenerator

import aiomysql
import structlog

from app.config import get_settings

log = structlog.get_logger(__name__)


@asynccontextmanager
async def dse_connection() -> AsyncGenerator[aiomysql.Connection, None]:
    """
    Async context manager — yields an open aiomysql Connection.

    Opens a fresh connection every time; DSE queries are infrequent
    (scheduler interval) so connection pooling is not needed here.
    Raises ConnectionError if the connection cannot be established.
    """
    settings = get_settings()

    if not settings.dse_host:
        raise ConnectionError(
            "DSE credentials are not configured. "
            "Set dse_host, dse_user, dse_password, dse_database in .env"
        )

    conn: aiomysql.Connection | None = None
    try:
        conn = await aiomysql.connect(
            host=settings.dse_host,
            port=settings.dse_port,
            user=settings.dse_user,
            password=settings.dse_password,
            db=settings.dse_database,
            autocommit=True,
            connect_timeout=10,
            charset="utf8mb4",
        )
        log.debug("dse_connected", host=settings.dse_host, db=settings.dse_database)
        yield conn
    except aiomysql.Error as exc:
        log.error(
            "dse_connection_failed",
            host=settings.dse_host,
            db=settings.dse_database,
            error=str(exc),
        )
        raise ConnectionError(
            f"DSE database connection failed: {exc}"
        ) from exc
    finally:
        if conn is not None:
            conn.close()
            log.debug("dse_disconnected")