"""
utils/database.py — PostgreSQL connection pool + helpers
========================================================
Centralized, thread-safe Postgres access using a psycopg2
ThreadedConnectionPool. All routes / services should go
through `fetch_one`, `fetch_all`, or `execute` — never open
their own connections.

Lifecycle:
    init_pool()    – called once at FastAPI startup (fail-fast).
    close_pool()   – called once at FastAPI shutdown.
    get_conn()     – context manager that checks out a connection
                     from the pool and returns it on exit.

Credentials are loaded from `.env` via `config.settings`.
Nothing is hardcoded.
"""
from __future__ import annotations

import threading
from contextlib import contextmanager
from typing import Any, Iterator

import psycopg2
from psycopg2 import pool as pg_pool
from psycopg2.extras import RealDictCursor

from config import settings
from utils.logger import get_logger

log = get_logger(__name__)

# Module-level pool — initialized at startup.
_pool: pg_pool.ThreadedConnectionPool | None = None
_pool_lock = threading.Lock()


# ── Lifecycle ────────────────────────────────────────────────────────────────

def init_pool() -> None:
    """
    Create the connection pool. Call this exactly once at app startup.
    Fail-fast: if DB_FAIL_FAST is true and the pool can't be created,
    we re-raise so the app refuses to start.
    """
    global _pool
    with _pool_lock:
        if _pool is not None:
            log.debug("DB pool already initialized — skipping")
            return

        if not settings.DB_HOST or not settings.DB_NAME:
            msg = "Database is not configured. Check DB_HOST / DB_NAME in .env."
            log.error(msg)
            if settings.DB_FAIL_FAST:
                raise RuntimeError(msg)
            return

        log.info(
            "Initializing PostgreSQL pool → host=%s port=%s db=%s user=%s "
            "min=%d max=%d sslmode=%s",
            settings.DB_HOST, settings.DB_PORT, settings.DB_NAME, settings.DB_USER,
            settings.DB_POOL_MIN, settings.DB_POOL_MAX, settings.DB_SSLMODE,
        )

        try:
            _pool = pg_pool.ThreadedConnectionPool(
                minconn=settings.DB_POOL_MIN,
                maxconn=settings.DB_POOL_MAX,
                host=settings.DB_HOST,
                port=settings.DB_PORT,
                dbname=settings.DB_NAME,
                user=settings.DB_USER,
                password=settings.DB_PASSWORD,
                sslmode=settings.DB_SSLMODE,
                connect_timeout=settings.DB_CONNECT_TIMEOUT,
                application_name="people_hub_candidate",
            )
        except psycopg2.Error as exc:
            log.error("Failed to create PostgreSQL pool: %s", exc)
            if settings.DB_FAIL_FAST:
                raise
            _pool = None
            return

        # Smoke-test the pool with a SELECT 1 — ensures real reachability,
        # not just that the pool object was constructed.
        try:
            with get_conn() as conn:
                with conn.cursor() as cur:
                    cur.execute("SELECT 1;")
                    cur.fetchone()
            log.info("PostgreSQL pool ready ✓ (connection verified)")
        except Exception as exc:
            log.error("PostgreSQL pool created but probe query failed: %s", exc)
            close_pool()
            if settings.DB_FAIL_FAST:
                raise


def close_pool() -> None:
    """Close all connections in the pool. Called at shutdown."""
    global _pool
    with _pool_lock:
        if _pool is not None:
            try:
                _pool.closeall()
                log.info("PostgreSQL pool closed")
            except Exception as exc:  # noqa: BLE001
                log.warning("Error closing PostgreSQL pool: %s", exc)
            finally:
                _pool = None


def is_ready() -> bool:
    return _pool is not None


# ── Connection / cursor helpers ──────────────────────────────────────────────

@contextmanager
def get_conn() -> Iterator[psycopg2.extensions.connection]:
    """
    Check out a connection from the pool, return it on exit.
    Auto-commits on success and rolls back on exception.
    """
    if _pool is None:
        raise RuntimeError("DB pool not initialized — call init_pool() at startup")

    conn = _pool.getconn()
    try:
        yield conn
        if not conn.closed:
            conn.commit()
    except Exception:
        try:
            conn.rollback()
        except Exception:  # noqa: BLE001
            pass
        raise
    finally:
        _pool.putconn(conn)


@contextmanager
def get_cursor(dict_rows: bool = True) -> Iterator[psycopg2.extensions.cursor]:
    """
    Convenience: get_conn() + cursor() in one go.
    Returns dict-like rows by default (RealDictCursor).
    """
    with get_conn() as conn:
        cursor_factory = RealDictCursor if dict_rows else None
        with conn.cursor(cursor_factory=cursor_factory) as cur:
            yield cur


# ── Query helpers ────────────────────────────────────────────────────────────

def fetch_one(sql: str, params: tuple | dict | None = None) -> dict | None:
    """Run SELECT and return the first row as a dict, or None."""
    with get_cursor() as cur:
        cur.execute(sql, params or ())
        row = cur.fetchone()
        return dict(row) if row else None


def fetch_all(sql: str, params: tuple | dict | None = None) -> list[dict]:
    """Run SELECT and return all rows as a list of dicts."""
    with get_cursor() as cur:
        cur.execute(sql, params or ())
        rows = cur.fetchall()
        return [dict(r) for r in rows]


def execute(sql: str, params: tuple | dict | None = None) -> int:
    """Run INSERT / UPDATE / DELETE and return the rowcount."""
    with get_cursor(dict_rows=False) as cur:
        cur.execute(sql, params or ())
        return cur.rowcount