"""Synchronous SQLAlchemy session — cPanel Passenger is WSGI (sync)."""
import os
from contextlib import contextmanager
from typing import Iterator

from sqlalchemy import create_engine
from sqlalchemy.orm import Session, sessionmaker

from .models import Base

_engine = None
_SessionLocal: sessionmaker | None = None


def _build_url() -> str:
    """Reads DATABASE_URL if set, else assembles one from MYSQL_* env vars.

    Example DATABASE_URL = mysql+pymysql://user:pass@localhost:3306/dbname?charset=utf8mb4
    """
    url = os.environ.get("DATABASE_URL")
    if url:
        return url
    user = os.environ["MYSQL_USER"]
    pwd = os.environ["MYSQL_PASSWORD"]
    host = os.environ.get("MYSQL_HOST", "localhost")
    port = os.environ.get("MYSQL_PORT", "3306")
    name = os.environ["MYSQL_DATABASE"]
    # urlencode the password because it can contain special chars
    from urllib.parse import quote_plus
    return f"mysql+pymysql://{quote_plus(user)}:{quote_plus(pwd)}@{host}:{port}/{name}?charset=utf8mb4"


def get_engine():
    global _engine, _SessionLocal
    if _engine is None:
        _engine = create_engine(
            _build_url(),
            pool_pre_ping=True,
            pool_recycle=3600,       # re-connect every hour (shared hosts close idle)
            future=True,
        )
        _SessionLocal = sessionmaker(bind=_engine, autoflush=False, expire_on_commit=False)
    return _engine


def init_db() -> None:
    """Create all tables if they don't exist."""
    engine = get_engine()
    Base.metadata.create_all(engine)


@contextmanager
def session_scope() -> Iterator[Session]:
    if _SessionLocal is None:
        get_engine()
    assert _SessionLocal is not None
    s = _SessionLocal()
    try:
        yield s
        s.commit()
    except Exception:
        s.rollback()
        raise
    finally:
        s.close()


def get_session() -> Session:
    """FastAPI dependency style — must be closed by caller (used in Depends)."""
    if _SessionLocal is None:
        get_engine()
    assert _SessionLocal is not None
    s = _SessionLocal()
    try:
        yield s
    finally:
        s.close()
