import logging
import threading
import time
import traceback

from backend.utils.metrics import GLOBAL_REGISTRY
from flask import g, has_request_context, request
from sqlalchemy import event

log = logging.getLogger("dbpool.instrumentation")
_pool_inflight_lock = threading.Lock()
_pool_inflight = 0

_checked_out = {}

SQL_SLOW_STATEMENT_MIN_DURATION_S = 0.5
SQL_LONG_CHECKOUT_MIN_DURATION_S = 2.0
SQL_PERIODIC_REPORTER_INTERVAL_S = 5.0


def install_sql_trace(engine):
    @event.listens_for(engine, "before_cursor_execute")
    def before_cursor_execute(conn, cursor, statement, parameters, context, executemany):
        context._query_start_time = time.time()

    @event.listens_for(engine, "after_cursor_execute")
    def after_cursor_execute(conn, cursor, statement, parameters, context, executemany):
        duration = time.time() - context._query_start_time

        GLOBAL_REGISTRY.timer("db.sql.query.execute").update(duration)
        # GLOBAL_REGISTRY.timer(f"db.sql.query.__{statement}__.execute").update(duration)

        if duration > SQL_SLOW_STATEMENT_MIN_DURATION_S:
            req = "-"
            if has_request_context():
                req = f"{request.method} {request.path}"

            log.warning(
                "SQL SLOW thread=%s tid=%s req=%s duration=%.3fs sql=%s params=%s",
                threading.current_thread().name,
                threading.get_ident(),
                req,
                duration,
                statement,
                parameters,
            )


####################################################
# Reporting on DB pool status / checkout / checkin
####################################################


def install_db_pool_instrumentation(db, capture_stack=True, stack_limit=120):
    """
    Installs SQLAlchemy pool instrumentation on a Flask-SQLAlchemy db object.
    Logs: CONNECT/CLOSE/CHECKOUT/CHECKIN events with connection and thread metadata.

    Also starts a background reporter thread that periodically logs any connections
    currently checked out along with how long they've been held, to help identify connection leaks or long-running transactions.
    """

    def _periodic_reporter():
        while True:
            time.sleep(SQL_PERIODIC_REPORTER_INTERVAL_S)
            now = time.time()
            with _pool_inflight_lock:
                items = list(_checked_out.items())

            if not items:
                log.warning("DB HOLDS inflight=0 holders=[]")
                continue

            holders = []
            for record_id, meta in items:
                held_for = now - meta["checkout_ts"]
                holders.append(
                    {
                        "record_id": record_id,
                        "conn_id": meta["conn_id"],
                        "thread_name": meta["thread_name"],
                        "thread_id": meta["thread_id"],
                        "request_id": meta["request_id"],
                        "method": meta["method"],
                        "path": meta["path"],
                        "held_for": held_for,
                    }
                )

            holders.sort(key=lambda h: h["held_for"], reverse=True)

            if len(holders) == 0:
                continue

            summary = " | ".join(
                (
                    f"record={h['record_id']} conn={h['conn_id']} "
                    f"held_for={h['held_for']:.3f}s "
                    f"thread={h['thread_name']}/{h['thread_id']} "
                    f"req={h['request_id']} {h['method']} {h['path']}"
                )
                for h in holders
            )
            log.info("DB HOLDS inflight=%s holders=%s", len(items), summary)

    # Start periodic reporter
    threading.Thread(target=_periodic_reporter, name="dbpool-reporter", daemon=True).start()

    engine = db.engine

    def request_info():
        if has_request_context():
            req_id = getattr(g, "_dbpool_request_id", None)
            if req_id is None:
                req_id = f"req-{int(time.time() * 1000)}-{threading.get_ident()}"
                g._dbpool_request_id = req_id
            return {
                "request_id": req_id,
                "method": request.method,
                "path": request.path,
            }
        return {
            "request_id": "no-request",
            "method": "-",
            "path": "-",
        }

    @event.listens_for(engine, "connect")
    def on_connect(dbapi_connection, connection_record):
        log.warning(
            "DB CONNECT conn=%s record=%s",
            hex(id(dbapi_connection)),
            hex(id(connection_record)),
        )

    @event.listens_for(engine, "close")
    def on_close(dbapi_connection, connection_record):
        log.warning(
            "DB CLOSE conn=%s record=%s",
            hex(id(dbapi_connection)),
            hex(id(connection_record)),
        )

    @event.listens_for(engine, "checkout")
    def on_checkout(dbapi_connection, connection_record, connection_proxy):
        now = time.time()
        info = request_info()
        global _pool_inflight

        connection_record.info["checkout_ts"] = now
        connection_record.info["checkout_thread_id"] = threading.get_ident()
        connection_record.info["checkout_thread_name"] = threading.current_thread().name
        connection_record.info["checkout_request_id"] = info["request_id"]
        connection_record.info["checkout_method"] = info["method"]
        connection_record.info["checkout_path"] = info["path"]

        if capture_stack:
            stack = "".join(traceback.format_stack(limit=stack_limit))
            connection_record.info["checkout_stack"] = stack
        else:
            connection_record.info["checkout_stack"] = None

        with _pool_inflight_lock:
            _pool_inflight += 1
            inflight = _pool_inflight
            _checked_out[hex(id(connection_record))] = {
                "conn_id": hex(id(dbapi_connection)),
                "record_id": hex(id(connection_record)),
                "checkout_ts": now,
                "thread_id": threading.get_ident(),
                "thread_name": threading.current_thread().name,
                "request_id": info["request_id"],
                "method": info["method"],
                "path": info["path"],
            }
        log.debug(
            "DB CHECKOUT conn=%s record=%s inflight=%s thread=%s tid=%s req=%s %s %s",
            hex(id(dbapi_connection)),
            hex(id(connection_record)),
            inflight,
            threading.current_thread().name,
            threading.get_ident(),
            info["request_id"],
            info["method"],
            info["path"],
        )

    @event.listens_for(engine, "checkin")
    def on_checkin(dbapi_connection, connection_record):
        now = time.time()
        global _pool_inflight

        checkin_stack = None
        if capture_stack:
            checkin_stack = "".join(traceback.format_stack(limit=stack_limit))

        checkout_ts = connection_record.info.pop("checkout_ts", None)
        checkout_thread_id = connection_record.info.pop("checkout_thread_id", None)
        checkout_thread_name = connection_record.info.pop("checkout_thread_name", None)
        checkout_request_id = connection_record.info.pop("checkout_request_id", None)
        checkout_method = connection_record.info.pop("checkout_method", None)
        checkout_path = connection_record.info.pop("checkout_path", None)
        checkout_stack = connection_record.info.pop("checkout_stack", None)

        held_for = None if checkout_ts is None else now - checkout_ts

        with _pool_inflight_lock:
            _pool_inflight -= 1
            if _pool_inflight < 0:
                log.warning("DB INFLIGHT COUNTER UNDERFLOW; resetting to 0")
                _pool_inflight = 0
            inflight = _pool_inflight
            _checked_out.pop(hex(id(connection_record)), None)
        log.debug(
            "DB CHECKIN conn=%s record=%s inflight=%s thread=%s tid=%s req=%s held_for=%.3fs checkout_by=%s/%s checkout_req=%s %s %s",
            hex(id(dbapi_connection)) if dbapi_connection is not None else "None",
            hex(id(connection_record)),
            inflight,
            threading.current_thread().name,
            threading.get_ident(),
            request_info()["request_id"],
            held_for if held_for is not None else -1.0,
            checkout_thread_name,
            checkout_thread_id,
            checkout_request_id,
            checkout_method,
            checkout_path,
        )

        if held_for is not None and held_for > SQL_LONG_CHECKOUT_MIN_DURATION_S:
            log.warning(
                "DB LONG HOLD conn=%s record=%s inflight=%s held_for=%.3fs checkout_by=%s/%s checkout_req=%s %s %s\nCHECKOUT STACK:\n%s\nCHECKING STACK:\n%s",
                hex(id(dbapi_connection)) if dbapi_connection is not None else "None",
                hex(id(connection_record)),
                inflight,
                held_for,
                checkout_thread_name,
                checkout_thread_id,
                checkout_request_id,
                checkout_method,
                checkout_path,
                checkout_stack or "<stack capture disabled>",
                checkin_stack or "<stack capture disabled>",
            )
