from typing import List, Optional

from sqlalchemy.orm import Session

from app.crud.base import CRUDBase
from app.models.product import Category, Product
from app.schemas.product import ProductCreate, ProductFilterParams, ProductUpdate


class CRUDProduct(CRUDBase[Product, ProductCreate, ProductUpdate]):
    def search_products(
        self, db: Session, *, filter_params: ProductFilterParams, skip: int = 0, limit: int = 100
    ) -> List[Product]:
        query = db.query(Product)

        # Apply filters
        if filter_params.name:
            query = query.filter(Product.name.ilike(f"%{filter_params.name}%"))

        if filter_params.category_id:
            query = query.filter(Product.category_id == filter_params.category_id)

        if filter_params.min_price is not None:
            query = query.filter(Product.price >= filter_params.min_price)

        if filter_params.max_price is not None:
            query = query.filter(Product.price <= filter_params.max_price)

        if filter_params.in_stock is not None:
            if filter_params.in_stock:
                query = query.filter(Product.stock > 0)
            else:
                query = query.filter(Product.stock == 0)

        # Only return active products by default
        query = query.filter(Product.is_active)

        # Order by newest first
        query = query.order_by(Product.created_at.desc())

        return query.offset(skip).limit(limit).all()

    def get_product_with_category(self, db: Session, product_id: int) -> Optional[Product]:
        return db.query(Product).filter(Product.id == product_id).first()


class CRUDCategory(CRUDBase[Category, ProductCreate, ProductUpdate]):
    def get_by_name(self, db: Session, *, name: str) -> Optional[Category]:
        return db.query(Category).filter(Category.name == name).first()


product = CRUDProduct(Product)
category = CRUDCategory(Category)