smallbusinessinventorymanagementsystem-l10b6c/app/api/routes/inventory.py

from typing import List, Optional, Dict, Any
from fastapi import APIRouter, Depends, HTTPException, status, Query
from sqlalchemy.orm import Session
from sqlalchemy.exc import IntegrityError

from app.db.session import get_db
from app.models.inventory import (
    Location, 
    LocationType,
    InventoryItem, 
    InventoryTransaction, 
    InventoryTransactionItem,
    TransactionType
)
from app.models.product import Product
from app.schemas.inventory import (
    LocationCreate,
    LocationUpdate,
    LocationInDB,
    InventoryItemCreate,
    InventoryItemUpdate,
    InventoryItemInDB,
    InventoryItemWithDetails,
    InventoryTransactionCreate,
    InventoryTransactionWithItems,
    InventoryTransactionInDB,
    TransactionTypeEnum
)
from app.api.deps import get_product, get_location

router = APIRouter(prefix="/api/v1")

# Location endpoints
@router.post("/locations/", response_model=LocationInDB, status_code=status.HTTP_201_CREATED)
def create_location(
    *,
    db: Session = Depends(get_db),
    location_in: LocationCreate
):
    """Create a new location"""
    try:
        # Convert from schema enum to model enum
        type_value = LocationType(location_in.type.value)
        
        db_location = Location(
            name=location_in.name,
            type=type_value,
            address=location_in.address,
            description=location_in.description
        )
        db.add(db_location)
        db.commit()
        db.refresh(db_location)
        return db_location
    except Exception as e:
        db.rollback()
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail=f"Could not create location: {str(e)}"
        )

@router.get("/locations/", response_model=List[LocationInDB])
def read_locations(
    *,
    db: Session = Depends(get_db),
    skip: int = 0,
    limit: int = 100,
    search: Optional[str] = None,
    location_type: Optional[str] = None
):
    """Get all locations with optional filters"""
    query = db.query(Location)
    
    if search:
        query = query.filter(Location.name.ilike(f"%{search}%"))
    
    if location_type:
        try:
            type_value = LocationType(location_type)
            query = query.filter(Location.type == type_value)
        except ValueError:
            raise HTTPException(
                status_code=status.HTTP_400_BAD_REQUEST,
                detail=f"Invalid location type: {location_type}"
            )
    
    return query.offset(skip).limit(limit).all()

@router.get("/locations/{location_id}", response_model=LocationInDB)
def read_location(
    *,
    db: Session = Depends(get_db),
    location: Location = Depends(get_location)
):
    """Get a specific location by ID"""
    return location

@router.put("/locations/{location_id}", response_model=LocationInDB)
def update_location(
    *,
    db: Session = Depends(get_db),
    location: Location = Depends(get_location),
    location_in: LocationUpdate
):
    """Update a location"""
    update_data = location_in.model_dump(exclude_unset=True)
    
    # Convert type enum if present
    if "type" in update_data and update_data["type"]:
        update_data["type"] = LocationType(update_data["type"].value)
    
    for field, value in update_data.items():
        setattr(location, field, value)
    
    try:
        db.add(location)
        db.commit()
        db.refresh(location)
        return location
    except Exception as e:
        db.rollback()
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail=f"Could not update location: {str(e)}"
        )

@router.delete("/locations/{location_id}", status_code=status.HTTP_204_NO_CONTENT)
def delete_location(
    *,
    db: Session = Depends(get_db),
    location: Location = Depends(get_location)
):
    """Delete a location"""
    # Check if location has inventory items
    if location.inventory_items:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="Cannot delete location with existing inventory"
        )
    
    db.delete(location)
    db.commit()
    return None

# Inventory Item endpoints
@router.post("/inventory/", response_model=InventoryItemInDB, status_code=status.HTTP_201_CREATED)
def create_inventory_item(
    *,
    db: Session = Depends(get_db),
    item_in: InventoryItemCreate
):
    """Create a new inventory item"""
    # Check if product exists
    product = db.query(Product).filter(Product.id == item_in.product_id).first()
    if not product:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail=f"Product with ID {item_in.product_id} not found"
        )
    
    # Check if location exists
    location = db.query(Location).filter(Location.id == item_in.location_id).first()
    if not location:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail=f"Location with ID {item_in.location_id} not found"
        )
    
    # Check if inventory item already exists for this product and location
    existing_item = db.query(InventoryItem).filter(
        InventoryItem.product_id == item_in.product_id,
        InventoryItem.location_id == item_in.location_id
    ).first()
    
    if existing_item:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail=f"Inventory item already exists for this product and location"
        )
    
    try:
        db_item = InventoryItem(**item_in.model_dump())
        db.add(db_item)
        db.commit()
        db.refresh(db_item)
        return db_item
    except Exception as e:
        db.rollback()
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail=f"Could not create inventory item: {str(e)}"
        )

@router.get("/inventory/", response_model=List[InventoryItemWithDetails])
def read_inventory_items(
    *,
    db: Session = Depends(get_db),
    skip: int = 0,
    limit: int = 100,
    product_id: Optional[int] = None,
    location_id: Optional[int] = None,
    low_stock: bool = False
):
    """Get inventory items with optional filters"""
    query = db.query(InventoryItem)
    
    if product_id:
        query = query.filter(InventoryItem.product_id == product_id)
    
    if location_id:
        query = query.filter(InventoryItem.location_id == location_id)
    
    if low_stock:
        query = query.filter(InventoryItem.quantity <= InventoryItem.minimum_stock)
    
    items = query.offset(skip).limit(limit).all()
    
    # Enhance with product and location details
    result = []
    for item in items:
        item_dict = {
            "id": item.id,
            "product_id": item.product_id,
            "location_id": item.location_id,
            "quantity": item.quantity,
            "minimum_stock": item.minimum_stock,
            "maximum_stock": item.maximum_stock,
            "created_at": item.created_at,
            "updated_at": item.updated_at,
            "product": {
                "id": item.product.id,
                "name": item.product.name,
                "sku": item.product.sku,
            },
            "location": {
                "id": item.location.id,
                "name": item.location.name,
                "type": item.location.type.value,
            }
        }
        result.append(item_dict)
    
    return result

@router.get("/inventory/{item_id}", response_model=InventoryItemWithDetails)
def read_inventory_item(
    *,
    db: Session = Depends(get_db),
    item_id: int
):
    """Get a specific inventory item by ID"""
    item = db.query(InventoryItem).filter(InventoryItem.id == item_id).first()
    if not item:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail=f"Inventory item with ID {item_id} not found"
        )
    
    # Enhance with product and location details
    item_dict = {
        "id": item.id,
        "product_id": item.product_id,
        "location_id": item.location_id,
        "quantity": item.quantity,
        "minimum_stock": item.minimum_stock,
        "maximum_stock": item.maximum_stock,
        "created_at": item.created_at,
        "updated_at": item.updated_at,
        "product": {
            "id": item.product.id,
            "name": item.product.name,
            "sku": item.product.sku,
        },
        "location": {
            "id": item.location.id,
            "name": item.location.name,
            "type": item.location.type.value,
        }
    }
    
    return item_dict

@router.put("/inventory/{item_id}", response_model=InventoryItemInDB)
def update_inventory_item(
    *,
    db: Session = Depends(get_db),
    item_id: int,
    item_in: InventoryItemUpdate
):
    """Update an inventory item"""
    item = db.query(InventoryItem).filter(InventoryItem.id == item_id).first()
    if not item:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail=f"Inventory item with ID {item_id} not found"
        )
    
    update_data = item_in.model_dump(exclude_unset=True)
    for field, value in update_data.items():
        setattr(item, field, value)
    
    try:
        db.add(item)
        db.commit()
        db.refresh(item)
        return item
    except Exception as e:
        db.rollback()
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail=f"Could not update inventory item: {str(e)}"
        )

@router.delete("/inventory/{item_id}", status_code=status.HTTP_204_NO_CONTENT)
def delete_inventory_item(
    *,
    db: Session = Depends(get_db),
    item_id: int
):
    """Delete an inventory item"""
    item = db.query(InventoryItem).filter(InventoryItem.id == item_id).first()
    if not item:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail=f"Inventory item with ID {item_id} not found"
        )
    
    db.delete(item)
    db.commit()
    return None

# Inventory Transaction endpoints
@router.post("/inventory-transactions/", response_model=InventoryTransactionInDB, status_code=status.HTTP_201_CREATED)
def create_inventory_transaction(
    *,
    db: Session = Depends(get_db),
    transaction_in: InventoryTransactionWithItems
):
    """Create a new inventory transaction with items"""
    # Convert from schema enum to model enum
    try:
        transaction_type = TransactionType(transaction_in.type.value)
        
        # Create transaction
        db_transaction = InventoryTransaction(
            type=transaction_type,
            reference_id=transaction_in.reference_id,
            notes=transaction_in.notes
        )
        db.add(db_transaction)
        db.flush()  # Get ID without committing
        
        # Process transaction items
        transaction_items = []
        for item in transaction_in.items:
            # Validate product exists
            product = db.query(Product).filter(Product.id == item.product_id).first()
            if not product:
                db.rollback()
                raise HTTPException(
                    status_code=status.HTTP_404_NOT_FOUND,
                    detail=f"Product with ID {item.product_id} not found"
                )
            
            # Process based on transaction type
            if transaction_type == TransactionType.TRANSFER:
                # For transfers, need both locations
                if not item.from_location_id or not item.to_location_id:
                    db.rollback()
                    raise HTTPException(
                        status_code=status.HTTP_400_BAD_REQUEST,
                        detail="Transfer transactions require both from_location_id and to_location_id"
                    )
                
                # Validate from_location exists
                from_location = db.query(Location).filter(Location.id == item.from_location_id).first()
                if not from_location:
                    db.rollback()
                    raise HTTPException(
                        status_code=status.HTTP_404_NOT_FOUND,
                        detail=f"From location with ID {item.from_location_id} not found"
                    )
                
                # Validate to_location exists
                to_location = db.query(Location).filter(Location.id == item.to_location_id).first()
                if not to_location:
                    db.rollback()
                    raise HTTPException(
                        status_code=status.HTTP_404_NOT_FOUND,
                        detail=f"To location with ID {item.to_location_id} not found"
                    )
                
                # Update inventory at from_location
                from_inventory = db.query(InventoryItem).filter(
                    InventoryItem.product_id == item.product_id,
                    InventoryItem.location_id == item.from_location_id
                ).first()
                
                if not from_inventory:
                    db.rollback()
                    raise HTTPException(
                        status_code=status.HTTP_404_NOT_FOUND,
                        detail=f"Product {item.product_id} not found at location {item.from_location_id}"
                    )
                
                if from_inventory.quantity < item.quantity:
                    db.rollback()
                    raise HTTPException(
                        status_code=status.HTTP_400_BAD_REQUEST,
                        detail=f"Insufficient stock for product {item.product_id} at location {item.from_location_id}"
                    )
                
                from_inventory.quantity -= item.quantity
                
                # Update inventory at to_location
                to_inventory = db.query(InventoryItem).filter(
                    InventoryItem.product_id == item.product_id,
                    InventoryItem.location_id == item.to_location_id
                ).first()
                
                if to_inventory:
                    to_inventory.quantity += item.quantity
                else:
                    # Create new inventory item if it doesn't exist
                    to_inventory = InventoryItem(
                        product_id=item.product_id,
                        location_id=item.to_location_id,
                        quantity=item.quantity,
                        minimum_stock=0
                    )
                    db.add(to_inventory)
            
            elif transaction_type in [TransactionType.PURCHASE, TransactionType.RETURN]:
                # For purchases, need to_location (where items are added)
                if not item.to_location_id:
                    db.rollback()
                    raise HTTPException(
                        status_code=status.HTTP_400_BAD_REQUEST,
                        detail=f"{transaction_type.value} transactions require to_location_id"
                    )
                
                # Validate to_location exists
                to_location = db.query(Location).filter(Location.id == item.to_location_id).first()
                if not to_location:
                    db.rollback()
                    raise HTTPException(
                        status_code=status.HTTP_404_NOT_FOUND,
                        detail=f"Location with ID {item.to_location_id} not found"
                    )
                
                # Update inventory at to_location
                inventory = db.query(InventoryItem).filter(
                    InventoryItem.product_id == item.product_id,
                    InventoryItem.location_id == item.to_location_id
                ).first()
                
                if inventory:
                    inventory.quantity += item.quantity
                else:
                    # Create new inventory item if it doesn't exist
                    inventory = InventoryItem(
                        product_id=item.product_id,
                        location_id=item.to_location_id,
                        quantity=item.quantity,
                        minimum_stock=0
                    )
                    db.add(inventory)
            
            elif transaction_type in [TransactionType.SALE, TransactionType.ADJUSTMENT]:
                # For sales, need from_location (where items are removed)
                if not item.from_location_id:
                    db.rollback()
                    raise HTTPException(
                        status_code=status.HTTP_400_BAD_REQUEST,
                        detail=f"{transaction_type.value} transactions require from_location_id"
                    )
                
                # Validate from_location exists
                from_location = db.query(Location).filter(Location.id == item.from_location_id).first()
                if not from_location:
                    db.rollback()
                    raise HTTPException(
                        status_code=status.HTTP_404_NOT_FOUND,
                        detail=f"Location with ID {item.from_location_id} not found"
                    )
                
                # Update inventory at from_location
                inventory = db.query(InventoryItem).filter(
                    InventoryItem.product_id == item.product_id,
                    InventoryItem.location_id == item.from_location_id
                ).first()
                
                if not inventory:
                    db.rollback()
                    raise HTTPException(
                        status_code=status.HTTP_404_NOT_FOUND,
                        detail=f"Product {item.product_id} not found at location {item.from_location_id}"
                    )
                
                if inventory.quantity < item.quantity and transaction_type == TransactionType.SALE:
                    db.rollback()
                    raise HTTPException(
                        status_code=status.HTTP_400_BAD_REQUEST,
                        detail=f"Insufficient stock for product {item.product_id} at location {item.from_location_id}"
                    )
                
                inventory.quantity -= item.quantity
            
            # Create transaction item
            transaction_item = InventoryTransactionItem(
                transaction_id=db_transaction.id,
                product_id=item.product_id,
                quantity=item.quantity,
                from_location_id=item.from_location_id,
                to_location_id=item.to_location_id
            )
            db.add(transaction_item)
            transaction_items.append(transaction_item)
        
        db.commit()
        db.refresh(db_transaction)
        
        # Refresh transaction items
        for item in transaction_items:
            db.refresh(item)
        
        return db_transaction
    
    except IntegrityError as e:
        db.rollback()
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail=f"Database integrity error: {str(e)}"
        )
    except Exception as e:
        db.rollback()
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail=f"Could not create transaction: {str(e)}"
        )

@router.get("/inventory-transactions/", response_model=List[InventoryTransactionInDB])
def read_inventory_transactions(
    *,
    db: Session = Depends(get_db),
    skip: int = 0,
    limit: int = 100,
    transaction_type: Optional[str] = None,
    reference_id: Optional[str] = None
):
    """Get inventory transactions with optional filters"""
    query = db.query(InventoryTransaction)
    
    if transaction_type:
        try:
            type_value = TransactionType(transaction_type)
            query = query.filter(InventoryTransaction.type == type_value)
        except ValueError:
            raise HTTPException(
                status_code=status.HTTP_400_BAD_REQUEST,
                detail=f"Invalid transaction type: {transaction_type}"
            )
    
    if reference_id:
        query = query.filter(InventoryTransaction.reference_id == reference_id)
    
    return query.order_by(InventoryTransaction.created_at.desc()).offset(skip).limit(limit).all()

@router.get("/inventory-transactions/{transaction_id}", response_model=InventoryTransactionInDB)
def read_inventory_transaction(
    *,
    db: Session = Depends(get_db),
    transaction_id: int
):
    """Get a specific inventory transaction by ID"""
    transaction = db.query(InventoryTransaction).filter(InventoryTransaction.id == transaction_id).first()
    if not transaction:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail=f"Inventory transaction with ID {transaction_id} not found"
        )
    
    return transaction