import datetime
import json
from typing import Any, Sequence

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

from app.config import settings


def _url() -> str:
    return (
        f"mysql+pymysql://{settings.db_user}:{settings.db_password}"
        f"@{settings.db_host}:{settings.db_port}/{settings.db_name}"
        "?charset=utf8mb4"
    )


engine: Engine = create_engine(
    _url(),
    pool_size=settings.db_pool_size,
    max_overflow=settings.db_max_overflow,
    pool_recycle=settings.db_pool_recycle,
    pool_pre_ping=True,
    future=True,
)

JSON_COLUMNS = {
    "highlights", "tags", "social", "statesServed", "isFeaturedOnHome",
}


def _coerce(row: dict[str, Any]) -> dict[str, Any]:
    """MySQL returns JSON columns as strings via PyMySQL. Parse them so the
    response matches the frontend's expected types exactly."""
    out = dict(row)
    for key in JSON_COLUMNS & out.keys():
        val = out[key]
        if isinstance(val, (str, bytes)):
            out[key] = json.loads(val)
    if "isFeaturedOnHome" in out:
        out["isFeaturedOnHome"] = bool(out["isFeaturedOnHome"])
    # DATE/DATETIME columns (e.g. publishedAt) come back as date/datetime
    # objects — serialise to ISO strings to match the frontend's contract.
    for key, val in out.items():
        if isinstance(val, (datetime.date, datetime.datetime)):
            out[key] = val.isoformat()
    return out


def fetch_view(view: str, where: str = "", params: dict | None = None) -> list[dict]:
    """Read from a view. `view` is never user-controlled — callers pass a
    literal constant. `where` uses bound parameters only."""
    sql = f"SELECT * FROM {view}"  # noqa: S608 - view is a literal
    if where:
        sql += f" WHERE {where}"
    with engine.connect() as conn:
        rows = conn.execute(text(sql), params or {}).mappings().all()
    return [_coerce(dict(r)) for r in rows]


def call_proc(name: str, args: Sequence[Any], out_count: int = 0) -> list[Any]:
    """Execute a stored procedure. Returns OUT parameter values.

    PyMySQL's callproc handles OUT params via session variables, so we use
    the raw DBAPI cursor rather than SQLAlchemy text()."""
    raw = engine.raw_connection()
    try:
        cursor = raw.cursor()
        in_args = list(args) + [None] * out_count
        cursor.callproc(name, in_args)
        outs: list[Any] = []
        if out_count:
            placeholders = ", ".join(
                f"@_{name}_{i}" for i in range(len(args), len(args) + out_count)
            )
            cursor.execute(f"SELECT {placeholders}")
            fetched = cursor.fetchone() or ()
            outs = list(fetched)
        raw.commit()
        return outs
    except Exception:
        raw.rollback()
        raise
    finally:
        raw.close()