from sqlalchemy.orm import Session
from typing import Optional, List
from .models import Expense, Commission


def get_by_id(db: Session, entity_id: str, model_class) -> Optional[object]:
    return db.query(model_class).filter(model_class.id == entity_id).first()


def list_all(db: Session, model_class, limit: int = 20, offset: int = 0, **filters) -> List[object]:
    query = db.query(model_class)
    
    for key, value in filters.items():
        if value is not None and hasattr(model_class, key):
            query = query.filter(getattr(model_class, key) == value)
    
    return query.limit(limit).offset(offset).all()


def create(db: Session, model_class, data: dict) -> object:
    instance = model_class(**data)
    db.add(instance)
    db.flush()
    return instance


def update(db: Session, entity_id: str, model_class, data: dict) -> Optional[object]:
    instance = get_by_id(db, entity_id, model_class)
    if not instance:
        return None
    
    for key, value in data.items():
        if hasattr(instance, key):
            setattr(instance, key, value)
    
    db.flush()
    return instance


def delete(db: Session, entity_id: str, model_class) -> bool:
    instance = get_by_id(db, entity_id, model_class)
    if not instance:
        return False
    
    db.delete(instance)
    db.flush()
    return True


def get_expense_by_id(db: Session, expense_id: str) -> Optional[Expense]:
    return get_by_id(db, expense_id, Expense)


def list_expenses(db: Session, limit: int = 20, offset: int = 0, **filters) -> List[Expense]:
    return list_all(db, Expense, limit, offset, **filters)


def create_expense(db: Session, data: dict) -> Expense:
    return create(db, Expense, data)


def update_expense(db: Session, expense_id: str, data: dict) -> Optional[Expense]:
    return update(db, expense_id, Expense, data)


def delete_expense(db: Session, expense_id: str) -> bool:
    return delete(db, expense_id, Expense)


def get_commission_by_id(db: Session, commission_id: str) -> Optional[Commission]:
    return get_by_id(db, commission_id, Commission)


def list_commissions(db: Session, limit: int = 20, offset: int = 0, **filters) -> List[Commission]:
    return list_all(db, Commission, limit, offset, **filters)


def create_commission(db: Session, data: dict) -> Commission:
    return create(db, Commission, data)


def update_commission(db: Session, commission_id: str, data: dict) -> Optional[Commission]:
    return update(db, commission_id, Commission, data)


def delete_commission(db: Session, commission_id: str) -> bool:
    return delete(db, commission_id, Commission)


def list_expenses_by_tour_schedule(db: Session, tour_schedule_id: str, limit: int = 20, offset: int = 0) -> List[Expense]:
    return (
        db.query(Expense)
        .filter(Expense.tour_schedule_id == tour_schedule_id)
        .limit(limit)
        .offset(offset)
        .all()
    )


def list_expenses_by_submitter(db: Session, user_id: str, limit: int = 20, offset: int = 0) -> List[Expense]:
    return (
        db.query(Expense)
        .filter(Expense.submitted_by == user_id)
        .limit(limit)
        .offset(offset)
        .all()
    )


def list_commissions_by_agent(db: Session, agent_id: str, limit: int = 20, offset: int = 0) -> List[Commission]:
    return (
        db.query(Commission)
        .filter(Commission.booking_agent_id == agent_id)
        .limit(limit)
        .offset(offset)
        .all()
    )


def list_commissions_by_booking(db: Session, booking_id: str) -> List[Commission]:
    return db.query(Commission).filter(Commission.booking_id == booking_id).all()