# app/core/database.py
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, DeclarativeBase

DATABASE_URL = "postgresql://user:password@localhost:5432/mydb"

engine = create_engine(
    DATABASE_URL,
    pool_size=10,          # connexions maintenues ouvertes en permanence
    max_overflow=20,        # connexions supplémentaires temporaires autorisées sous charge
    pool_pre_ping=True,      # vérifie la connexion avant usage (évite les erreurs sur connexion morte)
)

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


class Base(DeclarativeBase):
    pass

# app/models/product.py — modèle ORM SQLAlchemy 2.0 (Mapped / mapped_column)
from sqlalchemy import String, ForeignKey
from sqlalchemy.orm import Mapped, mapped_column, relationship
from app.core.database import Base


class Product(Base):
    __tablename__ = "products"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(200))
    price: Mapped[float]
    owner_id: Mapped[int] = mapped_column(ForeignKey("users.id"))

    owner: Mapped["User"] = relationship(back_populates="products")

# app/dependencies.py — dépendance de session par requête
from app.core.database import SessionLocal

def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

# Requêtes courantes avec l'API SQLAlchemy 2.0 (select explicite)
from sqlalchemy import select
from sqlalchemy.orm import Session


def get_product(db: Session, product_id: int) -> Product | None:
    return db.get(Product, product_id)  # lookup direct par clé primaire


def list_expensive_products(db: Session, min_price: float) -> list[Product]:
    stmt = select(Product).where(Product.price >= min_price).order_by(Product.price.desc())
    return list(db.scalars(stmt))


def get_products_with_owner(db: Session):
    # eager loading pour éviter le problème N+1 (une requête au lieu de N)
    from sqlalchemy.orm import joinedload
    stmt = select(Product).options(joinedload(Product.owner))
    return list(db.scalars(stmt))

# Création, mise à jour, suppression
def create_product(db: Session, name: str, price: float, owner_id: int) -> Product:
    product = Product(name=name, price=price, owner_id=owner_id)
    db.add(product)
    db.commit()
    db.refresh(product)  # recharge les champs générés par la DB (id, defaults)
    return product


def update_product(db: Session, product: Product, **fields) -> Product:
    for key, value in fields.items():
        setattr(product, key, value)
    db.commit()
    db.refresh(product)
    return product


def delete_product(db: Session, product: Product) -> None:
    db.delete(product)
    db.commit()

# Alembic pour les migrations de schéma — jamais modifier la DB à la main en production
# alembic init alembic
# alembic revision --autogenerate -m "Ajout de la table products"
# alembic upgrade head

# alembic.ini (extrait) — pointer vers la même base que l'application
sqlalchemy.url = postgresql://user:password@localhost:5432/mydb