from __future__ import annotations

import os
from pathlib import Path
from typing import Any, Iterable

import psycopg
from psycopg.rows import dict_row
from dotenv import load_dotenv

for candidate in [Path('/srv/goods-ledger/.env'), Path(__file__).resolve().parents[2] / '.env']:
    if candidate.exists():
        load_dotenv(candidate, override=False)


def connect():
    # Use discrete PG* variables instead of DATABASE_URL so credentials are never assembled into logs.
    return psycopg.connect(
        host=os.environ.get('PGHOST', '127.0.0.1'),
        port=os.environ.get('PGPORT', '5432'),
        dbname=os.environ.get('PGDATABASE', 'goods_ledger'),
        user=os.environ.get('PGUSER', 'ledger_app'),
        password=os.environ.get('PGPASSWORD', ''),
        row_factory=dict_row,
        autocommit=False,
    )


def fetch_all(sql: str, params: Iterable[Any] | dict[str, Any] = ()) -> list[dict[str, Any]]:
    with connect() as conn:
        with conn.cursor() as cur:
            cur.execute(sql, params)
            return list(cur.fetchall())


def fetch_one(sql: str, params: Iterable[Any] | dict[str, Any] = ()) -> dict[str, Any] | None:
    with connect() as conn:
        with conn.cursor() as cur:
            cur.execute(sql, params)
            row = cur.fetchone()
            return dict(row) if row else None