import os
import logging
from sqlalchemy import create_engine, text, MetaData
from typing import Optional, Any

logger = logging.getLogger(__name__)

_engine = None


def get_engine():
    global _engine
    if _engine is None:
        db_url = os.environ.get("DATABASE_URL")
        if not db_url:
            raise RuntimeError("DATABASE_URL not set")
        _engine = create_engine(db_url, pool_pre_ping=True, pool_size=3)
    return _engine


def read_query(sql: str, params: Optional[dict] = None) -> list[dict[str, Any]]:
    engine = get_engine()
    with engine.connect() as conn:
        result = conn.execute(text(sql), params or {})
        columns = result.keys()
        return [dict(zip(columns, row)) for row in result.fetchall()]


def read_one(sql: str, params: Optional[dict] = None) -> Optional[dict[str, Any]]:
    rows = read_query(sql, params)
    return rows[0] if rows else None


def read_scalar(sql: str, params: Optional[dict] = None) -> Any:
    engine = get_engine()
    with engine.connect() as conn:
        result = conn.execute(text(sql), params or {})
        row = result.fetchone()
        return row[0] if row else None


def table_exists(table_name: str) -> bool:
    try:
        count = read_scalar("SELECT COUNT(*) FROM information_schema.tables WHERE table_name = :t", {"t": table_name})
        return count > 0
    except Exception:
        return False


def reset_engine():
    global _engine
    if _engine:
        _engine.dispose()
    _engine = None