"""
database.py

PostgreSQL database configuration for LeafDoctor AI.

Responsibilities
----------------
- Create SQLAlchemy engine
- Configure connection pooling
- Provide SessionLocal
- Provide Base declarative class
- Initialize database tables

No business logic should exist here.
"""

from sqlalchemy import create_engine
from sqlalchemy.orm import declarative_base, sessionmaker
from config import settings
from urllib.parse import quote_plus


DATABASE_URL = (
    "postgresql+psycopg2://"
    f"{quote_plus(settings.DB_USER)}:"
    f"{quote_plus(settings.DB_PASSWORD)}@"
    f"{settings.DB_HOST}:"
    f"{settings.DB_PORT}/"
    f"{settings.DB_NAME}"
)


engine = create_engine(
    DATABASE_URL,
    pool_size=10,
    max_overflow=20,
    pool_pre_ping=True,
    pool_recycle=3600,
    echo=False,
    future=True,
)


SessionLocal = sessionmaker(
    bind=engine,
    autoflush=False,
    autocommit=False,
    expire_on_commit=False,
)


Base = declarative_base()


def get_db():
    """
    FastAPI dependency.

    Usage:
        db: Session = Depends(get_db)
    """
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()


def create_tables():
    """
    Create all tables defined in models.py.
    """
    import models  # noqa: F401

    Base.metadata.create_all(bind=engine)


def drop_tables():
    """
    Drop all tables.
    Useful only during development.
    """
    import models  # noqa: F401

    Base.metadata.drop_all(bind=engine)


def test_connection() -> bool:
    """
    Test PostgreSQL connectivity.

    Returns
    -------
    bool
        True if database connection succeeds.
    """
    from sqlalchemy import text

    try:
        with engine.connect() as connection:
            connection.execute(text("SELECT 1"))
        return True
    except Exception as exc:
        print(f"Database connection failed: {exc}")
        return False