from typing import List, Optional
from sqlalchemy.orm import Session
from app.crud.base import CRUDBase
from app.models.item import Item
from app.schemas.item import ItemCreate, ItemUpdate


class CRUDItem(CRUDBase[Item, ItemCreate, ItemUpdate]):
    def get_by_sku(self, db: Session, *, sku: str) -> Optional[Item]:
        return db.query(Item).filter(Item.sku == sku).first()

    def get_by_barcode(self, db: Session, *, barcode: str) -> Optional[Item]:
        return db.query(Item).filter(Item.barcode == barcode).first()

    def get_by_category(
        self, db: Session, *, category_id: int, skip: int = 0, limit: int = 100
    ) -> List[Item]:
        return (
            db.query(Item)
            .filter(Item.category_id == category_id)
            .offset(skip)
            .limit(limit)
            .all()
        )

    def get_by_supplier(
        self, db: Session, *, supplier_id: int, skip: int = 0, limit: int = 100
    ) -> List[Item]:
        return (
            db.query(Item)
            .filter(Item.supplier_id == supplier_id)
            .offset(skip)
            .limit(limit)
            .all()
        )

    def get_low_stock_items(
        self, db: Session, skip: int = 0, limit: int = 100
    ) -> List[Item]:
        return (
            db.query(Item)
            .filter(Item.quantity_in_stock <= Item.reorder_point)
            .offset(skip)
            .limit(limit)
            .all()
        )

    def get_active_items(
        self, db: Session, skip: int = 0, limit: int = 100
    ) -> List[Item]:
        return db.query(Item).filter(Item.is_active).offset(skip).limit(limit).all()

    def search_items(
        self, db: Session, *, query: str, skip: int = 0, limit: int = 100
    ) -> List[Item]:
        return (
            db.query(Item)
            .filter(
                Item.name.contains(query)
                | Item.description.contains(query)
                | Item.sku.contains(query)
            )
            .offset(skip)
            .limit(limit)
            .all()
        )


item = CRUDItem(Item)