from datetime import datetime
from typing import List

from sqlalchemy import and_
from sqlalchemy.orm import Session

from app.crud.base import CRUDBase
from app.models.item import Item
from app.models.transaction import Transaction, TransactionType
from app.schemas.transaction import TransactionCreate, TransactionUpdate


class CRUDTransaction(CRUDBase[Transaction, TransactionCreate, TransactionUpdate]):
    """
    CRUD operations for Transaction model.
    """
    def create_with_item_update(
        self, db: Session, *, obj_in: TransactionCreate, user_id: int
    ) -> Transaction:
        """
        Create a transaction and update the associated item's quantity.
        """
        # Get the item to update its quantity
        item = db.query(Item).filter(Item.id == obj_in.item_id).first()
        if not item:
            raise ValueError(f"Item with ID {obj_in.item_id} not found")
        
        # Calculate the total price
        total_price = obj_in.quantity * obj_in.unit_price
        
        # Create transaction object
        db_obj = Transaction(
            item_id=obj_in.item_id,
            user_id=user_id,
            quantity=obj_in.quantity,
            transaction_type=obj_in.transaction_type,
            unit_price=obj_in.unit_price,
            total_price=total_price,
            reference=obj_in.reference,
            notes=obj_in.notes,
            timestamp=datetime.utcnow()
        )
        
        # Update the item quantity based on transaction type
        if obj_in.transaction_type == TransactionType.STOCK_IN:
            item.quantity += obj_in.quantity
        elif obj_in.transaction_type == TransactionType.STOCK_OUT:
            if item.quantity < obj_in.quantity:
                raise ValueError(f"Insufficient stock. Available: {item.quantity}, Requested: {obj_in.quantity}")
            item.quantity -= obj_in.quantity
        
        # Save to database
        db.add(db_obj)
        db.add(item)
        db.commit()
        db.refresh(db_obj)
        return db_obj
    
    def get_by_item(
        self, db: Session, *, item_id: int, skip: int = 0, limit: int = 100
    ) -> List[Transaction]:
        """
        Get transactions for a specific item.
        """
        return (
            db.query(Transaction)
            .filter(Transaction.item_id == item_id)
            .order_by(Transaction.timestamp.desc())
            .offset(skip)
            .limit(limit)
            .all()
        )
    
    def get_by_user(
        self, db: Session, *, user_id: int, skip: int = 0, limit: int = 100
    ) -> List[Transaction]:
        """
        Get transactions performed by a specific user.
        """
        return (
            db.query(Transaction)
            .filter(Transaction.user_id == user_id)
            .order_by(Transaction.timestamp.desc())
            .offset(skip)
            .limit(limit)
            .all()
        )
    
    def get_by_date_range(
        self, db: Session, *, start_date: datetime, end_date: datetime,
        skip: int = 0, limit: int = 100
    ) -> List[Transaction]:
        """
        Get transactions within a date range.
        """
        return (
            db.query(Transaction)
            .filter(and_(
                Transaction.timestamp >= start_date,
                Transaction.timestamp <= end_date
            ))
            .order_by(Transaction.timestamp.desc())
            .offset(skip)
            .limit(limit)
            .all()
        )
    
    def get_by_type(
        self, db: Session, *, transaction_type: TransactionType,
        skip: int = 0, limit: int = 100
    ) -> List[Transaction]:
        """
        Get transactions by type (stock in or stock out).
        """
        return (
            db.query(Transaction)
            .filter(Transaction.transaction_type == transaction_type)
            .order_by(Transaction.timestamp.desc())
            .offset(skip)
            .limit(limit)
            .all()
        )


transaction = CRUDTransaction(Transaction)