from datetime import date, datetime
from typing import List

from sqlalchemy import func
from sqlalchemy.orm import Session

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


class CRUDTransaction(CRUDBase[Transaction, TransactionCreate, TransactionUpdate]):
    """
    CRUD operations for Transaction model.
    """
    def create_with_item_and_user(
        self, db: Session, *, obj_in: TransactionCreate, recorded_by_id: int
    ) -> Transaction:
        """
        Create a new transaction record with item and user.
        """
        obj_in_data = obj_in.model_dump()
        db_obj = Transaction(**obj_in_data, recorded_by_id=recorded_by_id)
        db.add(db_obj)
        db.commit()
        db.refresh(db_obj)
        return db_obj
    
    def get_by_item_id(
        self, db: Session, *, item_id: int, skip: int = 0, limit: int = 100
    ) -> List[Transaction]:
        """
        Get transactions by item ID.
        """
        return (
            db.query(Transaction)
            .filter(Transaction.item_id == item_id)
            .order_by(Transaction.transaction_date.desc())
            .offset(skip)
            .limit(limit)
            .all()
        )
    
    def get_by_date_range(
        self, 
        db: Session, 
        *, 
        start_date: date, 
        end_date: date = None,
        skip: int = 0, 
        limit: int = 100
    ) -> List[Transaction]:
        """
        Get transactions within a date range.
        """
        if end_date is None:
            end_date = date.today()
        
        # Convert dates to datetime for proper comparison
        start_datetime = datetime.combine(start_date, datetime.min.time())
        end_datetime = datetime.combine(end_date, datetime.max.time())
        
        return (
            db.query(Transaction)
            .filter(Transaction.transaction_date >= start_datetime)
            .filter(Transaction.transaction_date <= end_datetime)
            .order_by(Transaction.transaction_date.desc())
            .offset(skip)
            .limit(limit)
            .all()
        )
    
    def get_summary_by_type(
        self, db: Session, *, start_date: date, end_date: date = None
    ) -> List[dict]:
        """
        Get transaction summary aggregated by transaction type.
        """
        if end_date is None:
            end_date = date.today()
        
        # Convert dates to datetime for proper comparison
        start_datetime = datetime.combine(start_date, datetime.min.time())
        end_datetime = datetime.combine(end_date, datetime.max.time())
        
        result = (
            db.query(
                Transaction.transaction_type,
                func.count().label("count"),
                func.sum(Transaction.quantity).label("total_quantity"),
                func.sum(Transaction.unit_price * Transaction.quantity).label("total_value")
            )
            .filter(Transaction.transaction_date >= start_datetime)
            .filter(Transaction.transaction_date <= end_datetime)
            .group_by(Transaction.transaction_type)
            .all()
        )
        
        return [
            {
                "transaction_type": r[0],
                "count": r[1],
                "total_quantity": r[2],
                "total_value": r[3]
            }
            for r in result
        ]


# Create a singleton instance
transaction = CRUDTransaction(Transaction)