from typing import List, Optional

from sqlalchemy.orm import Session

from app.db.crud_base import CRUDBase
from app.models.item import Item, InventoryTransaction
from app.schemas.item import ItemCreate, ItemUpdate, InventoryTransactionCreate


class CRUDItem(CRUDBase[Item, ItemCreate, ItemUpdate]):
    """
    CRUD operations for Item
    """
    def get_by_sku(self, db: Session, *, sku: str) -> Optional[Item]:
        """
        Get an item by SKU
        """
        return db.query(self.model).filter(self.model.sku == sku).first()
    
    def get_by_category(
        self, db: Session, *, category_id: int, skip: int = 0, limit: int = 100
    ) -> List[Item]:
        """
        Get items by category ID
        """
        return (
            db.query(self.model)
            .filter(self.model.category_id == category_id)
            .offset(skip)
            .limit(limit)
            .all()
        )
    
    def search_items(
        self, db: Session, *, query: str, skip: int = 0, limit: int = 100
    ) -> List[Item]:
        """
        Search items by name or description
        """
        search_pattern = f"%{query}%"
        return (
            db.query(self.model)
            .filter(
                (self.model.name.ilike(search_pattern)) | 
                (self.model.description.ilike(search_pattern))
            )
            .offset(skip)
            .limit(limit)
            .all()
        )
    
    def create_with_transaction(
        self, db: Session, *, obj_in: ItemCreate
    ) -> Item:
        """
        Create an item and record it as an inventory transaction if quantity > 0
        """
        # Create the item
        db_obj = self.create(db, obj_in=obj_in)
        
        # If initial quantity > 0, create a transaction record
        if db_obj.quantity > 0:
            transaction = InventoryTransaction(
                item_id=db_obj.id,
                quantity_change=db_obj.quantity,
                transaction_type="initial",
                notes="Initial inventory"
            )
            db.add(transaction)
            db.commit()
        
        return db_obj
    
    def update_quantity(
        self, db: Session, *, db_obj: Item, quantity_change: int, transaction_type: str, notes: Optional[str] = None
    ) -> Item:
        """
        Update item quantity and record the transaction
        """
        # Update item quantity
        new_quantity = db_obj.quantity + quantity_change
        if new_quantity < 0:
            raise ValueError("Cannot reduce quantity below zero")
        
        db_obj.quantity = new_quantity
        db.add(db_obj)
        
        # Create transaction record
        transaction = InventoryTransaction(
            item_id=db_obj.id,
            quantity_change=quantity_change,
            transaction_type=transaction_type,
            notes=notes
        )
        db.add(transaction)
        db.commit()
        db.refresh(db_obj)
        
        return db_obj
    
    def get_low_stock_items(
        self, db: Session, *, threshold: int = 10, skip: int = 0, limit: int = 100
    ) -> List[Item]:
        """
        Get items with quantity below the specified threshold
        """
        return (
            db.query(self.model)
            .filter(self.model.quantity < threshold)
            .offset(skip)
            .limit(limit)
            .all()
        )


class CRUDInventoryTransaction(CRUDBase[InventoryTransaction, InventoryTransactionCreate, InventoryTransactionCreate]):
    """
    CRUD operations for InventoryTransaction
    """
    def get_by_item(
        self, db: Session, *, item_id: int, skip: int = 0, limit: int = 100
    ) -> List[InventoryTransaction]:
        """
        Get transactions for a specific item
        """
        return (
            db.query(self.model)
            .filter(self.model.item_id == item_id)
            .order_by(self.model.timestamp.desc())
            .offset(skip)
            .limit(limit)
            .all()
        )


item = CRUDItem(Item)
inventory_transaction = CRUDInventoryTransaction(InventoryTransaction)