"""SQLAlchemy engine + session factory.

Used by every web endpoint that touches the database, and by the
init / migration scripts under scripts/.

Default DSN is a local SQLite file at the project root; production
overrides via the DATABASE_URL environment variable (typically
``sqlite:////var/lib/courseware/courseware.db`` on the server).
"""
from __future__ import annotations

import os
from pathlib import Path

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

_DEFAULT_DB_PATH = Path(__file__).parent.parent / "courseware.db"
DATABASE_URL = os.environ.get("DATABASE_URL", f"sqlite:///{_DEFAULT_DB_PATH}")

# check_same_thread=False lets Flask's threaded request handlers reuse
# connections across threads. Safe because each request opens/closes its
# own Session.
engine = create_engine(
    DATABASE_URL,
    connect_args={"check_same_thread": False} if DATABASE_URL.startswith("sqlite") else {},
    future=True,
)

SessionLocal = sessionmaker(bind=engine, autocommit=False, autoflush=False, future=True)

Base = declarative_base()


def get_session():
    """Yield a session, ensure it's closed. Use as a Flask request-scoped helper."""
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()