"""Read-only SQL execution for Polleo AI.

This is the hard safety boundary for Claude-generated SQL. Defence in depth:

  0. PRIVILEGE FLOOR (strongest): when settings.POLLEO_AI_DATABASE_URL is set,
     queries run as a dedicated SELECT-only Postgres role (see db/migrations/
     polleo_ai_readonly_role.sql). The database itself rejects any write — even
     a query that defeats every check below physically cannot mutate data.
  1. The caller (polleo_ai_service.validate_sql) only lets SELECT / WITH...SELECT
     single statements through.
  2. Here, every query runs inside an explicit transaction that is:
       * SET TRANSACTION READ ONLY  — Postgres rejects any write at the engine
         level, regardless of what slipped past the keyword check.
       * SET LOCAL statement_timeout = 5000  — 5s hard cap so a runaway query
         can't pin a connection.
       * ALWAYS rolled back — nothing the query did can persist.
  3. Results are capped at 500 rows.

We open a dedicated connection off a read-only engine rather than reusing the
request Session, so this isolation can never leak into normal app transactions.
"""
from __future__ import annotations

from functools import lru_cache
from typing import Any

from sqlalchemy import create_engine, text
from sqlalchemy.engine import Engine
from sqlalchemy.exc import DBAPIError, OperationalError, ProgrammingError

from backend.config import settings
from backend.models.database import engine as _main_engine

MAX_ROWS = 500
STATEMENT_TIMEOUT_MS = 5000


@lru_cache(maxsize=1)
def _readonly_engine() -> Engine:
    """Engine used for all Polleo AI queries.

    Prefers POLLEO_AI_DATABASE_URL (a least-privilege SELECT-only role). Falls
    back to the app's main engine when unset — still safe via the read-only
    transaction + timeout below, just without the DB-level privilege floor.
    Cached so we build the pool once.
    """
    url = settings.POLLEO_AI_DATABASE_URL.strip()
    if url:
        return create_engine(
            url,
            pool_size=2,
            max_overflow=3,
            pool_pre_ping=True,
            future=True,
        )
    return _main_engine


class SqlTimeoutError(Exception):
    """Query exceeded statement_timeout."""


class SqlExecutionError(Exception):
    """Query failed (bad column/table, syntax, type error, ...)."""


def _is_timeout(exc: Exception) -> bool:
    # psycopg2 raises QueryCanceled (subclass of OperationalError) on
    # statement_timeout; its message carries the canonical phrase.
    msg = str(getattr(exc, "orig", exc)).lower()
    return "statement timeout" in msg or "canceling statement" in msg


def run_readonly(sql: str) -> list[dict[str, Any]]:
    """Execute a single read-only SELECT and return up to MAX_ROWS row dicts.

    Raises SqlTimeoutError on timeout, SqlExecutionError on any other DB error.
    """
    sql = sql.strip().rstrip(";").strip()
    conn = _readonly_engine().connect()
    trans = conn.begin()
    try:
        # Order matters: READ ONLY + timeout must be set before the user query.
        conn.execute(text("SET TRANSACTION READ ONLY"))
        conn.execute(text(f"SET LOCAL statement_timeout = {STATEMENT_TIMEOUT_MS}"))
        result = conn.execute(text(sql))
        rows = [dict(r) for r in result.mappings().fetchmany(MAX_ROWS)]
        return rows
    except OperationalError as exc:
        if _is_timeout(exc):
            raise SqlTimeoutError(str(exc)) from exc
        raise SqlExecutionError(str(getattr(exc, "orig", exc))) from exc
    except (ProgrammingError, DBAPIError) as exc:
        if _is_timeout(exc):
            raise SqlTimeoutError(str(exc)) from exc
        raise SqlExecutionError(str(getattr(exc, "orig", exc))) from exc
    finally:
        # Never commit. Roll the (read-only) transaction back unconditionally.
        try:
            trans.rollback()
        finally:
            conn.close()