# Pagination offset/limit — simple, adaptée à la plupart des cas
from fastapi import Query
from typing import Annotated
from pydantic import BaseModel

class PageParams(BaseModel):
    page: int = 1
    page_size: int = 20

    @property
    def offset(self) -> int:
        return (self.page - 1) * self.page_size


def get_page_params(
    page: Annotated[int, Query(ge=1)] = 1,
    page_size: Annotated[int, Query(ge=1, le=100)] = 20,
) -> PageParams:
    return PageParams(page=page, page_size=page_size)


@router.get("/products")
def list_products(
    db: Annotated[Session, Depends(get_db)],
    pagination: Annotated[PageParams, Depends(get_page_params)],
):
    total = db.query(Product).count()
    items = (
        db.query(Product)
        .offset(pagination.offset)
        .limit(pagination.page_size)
        .all()
    )
    return {
        "items": items,
        "total": total,
        "page": pagination.page,
        "page_size": pagination.page_size,
        "total_pages": (total + pagination.page_size - 1) // pagination.page_size,
    }

# Filtres multiples combinés dynamiquement
from sqlalchemy import select


@router.get("/products/search")
def search_products(
    db: Annotated[Session, Depends(get_db)],
    name: str | None = None,
    min_price: float | None = None,
    max_price: float | None = None,
    category: str | None = None,
    in_stock: bool | None = None,
):
    stmt = select(Product)

    if name:
        stmt = stmt.where(Product.name.ilike(f"%{name}%"))
    if min_price is not None:
        stmt = stmt.where(Product.price >= min_price)
    if max_price is not None:
        stmt = stmt.where(Product.price <= max_price)
    if category:
        stmt = stmt.where(Product.category == category)
    if in_stock is not None:
        stmt = stmt.where(Product.stock > 0 if in_stock else Product.stock == 0)

    return db.scalars(stmt).all()

# Tri dynamique — whitelist stricte des colonnes triables (jamais une string brute dans order_by)
SORTABLE_FIELDS = {"name": Product.name, "price": Product.price, "created_at": Product.created_at}


@router.get("/products/sorted")
def list_sorted_products(
    db: Annotated[Session, Depends(get_db)],
    sort_by: str = "created_at",
    order: str = "desc",
):
    if sort_by not in SORTABLE_FIELDS:
        raise HTTPException(status_code=400, detail=f"Tri invalide : {sort_by}")

    column = SORTABLE_FIELDS[sort_by]
    column = column.desc() if order == "desc" else column.asc()

    return db.query(Product).order_by(column).all()

# Cursor-based pagination — plus stable que offset/limit sur des données qui changent souvent
@router.get("/products/cursor")
def list_products_cursor(
    db: Annotated[Session, Depends(get_db)],
    cursor: int | None = None,   # dernier ID vu par le client
    limit: int = 20,
):
    stmt = select(Product).order_by(Product.id).limit(limit + 1)  # +1 pour savoir s'il y a une page suivante
    if cursor is not None:
        stmt = stmt.where(Product.id > cursor)

    items = list(db.scalars(stmt))
    has_next = len(items) > limit
    items = items[:limit]

    return {
        "items": items,
        "next_cursor": items[-1].id if has_next else None,
    }