"""PostgreSQL access (psycopg 3, connection pool) and SQL migrations."""
from __future__ import annotations

import hashlib
from collections.abc import Iterator
from contextlib import contextmanager
from importlib import resources
from typing import Any

import psycopg
from psycopg.rows import dict_row
from psycopg_pool import ConnectionPool

from findata_core.config import get_settings

_pool: ConnectionPool | None = None


def get_pool() -> ConnectionPool:
    global _pool
    if _pool is None:
        s = get_settings()
        _pool = ConnectionPool(s.database_url.get_secret_value(), min_size=1, max_size=8, open=True,
                               kwargs={"row_factory": dict_row, "autocommit": False})
    return _pool


def close_pool() -> None:
    global _pool
    if _pool is not None:
        _pool.close()
        _pool = None


@contextmanager
def connection() -> Iterator[psycopg.Connection[Any]]:
    pool = get_pool()
    with pool.connection() as conn:
        yield conn


@contextmanager
def transaction() -> Iterator[psycopg.Connection[Any]]:
    with connection() as conn, conn.transaction():
        yield conn


def fetch_all(sql: str, params: Any = None) -> list[dict[str, Any]]:
    with connection() as conn:
        return conn.execute(sql, params).fetchall()


def fetch_one(sql: str, params: Any = None) -> dict[str, Any] | None:
    with connection() as conn:
        return conn.execute(sql, params).fetchone()


def execute(sql: str, params: Any = None) -> None:
    with transaction() as conn:
        conn.execute(sql, params)


def migrate(verbose: bool = True) -> list[str]:
    """Apply every `migrations/NNNN_*.sql` not yet recorded in `schema_migrations`. Idempotent."""
    applied: list[str] = []
    files = sorted(p for p in resources.files("findata_core.migrations").iterdir() if p.name.endswith(".sql"))
    with connection() as conn:
        conn.execute("CREATE TABLE IF NOT EXISTS schema_migrations (name text primary key, sha256 text not null, "
                     "applied_at timestamptz not null default now())")
        conn.commit()
        done = {r["name"]: r["sha256"] for r in conn.execute("SELECT name, sha256 FROM schema_migrations").fetchall()}
        for f in files:
            sql = f.read_text(encoding="utf-8")
            digest = hashlib.sha256(sql.encode()).hexdigest()
            if f.name in done:
                if done[f.name] != digest:
                    raise RuntimeError(f"migration {f.name} was modified after being applied")
                continue
            with conn.transaction():
                conn.execute(sql)
                conn.execute("INSERT INTO schema_migrations(name, sha256) VALUES (%s, %s)", (f.name, digest))
            applied.append(f.name)
            if verbose:
                print(f"applied {f.name}")
    return applied