from sqlalchemy.orm import Session, joinedload
from typing import Optional, List
from .models import Review


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


def list_all(db: Session, limit: int = 20, offset: int = 0, **filters) -> List[Review]:
    query = db.query(Review)
    
    if filters.get("customer_id"):
        query = query.filter(Review.customer_id == filters["customer_id"])
    
    if filters.get("tour_package_id"):
        query = query.filter(Review.tour_package_id == filters["tour_package_id"])
    
    if filters.get("tour_guide_id"):
        query = query.filter(Review.tour_guide_id == filters["tour_guide_id"])
    
    if filters.get("is_published") is not None:
        query = query.filter(Review.is_published == filters["is_published"])
    
    if filters.get("is_verified") is not None:
        query = query.filter(Review.is_verified == filters["is_verified"])
    
    return query.order_by(Review.created_at.desc()).limit(limit).offset(offset).all()


def create(db: Session, data: dict) -> Review:
    review = Review(**data)
    db.add(review)
    db.flush()
    return review


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


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


def get_with_details(db: Session, review_id: str) -> Optional[Review]:
    return (
        db.query(Review)
        .options(
            joinedload(Review.customer).joinedload("user"),
            joinedload(Review.tour_package),
            joinedload(Review.tour_guide).joinedload("user"),
            joinedload(Review.booking)
        )
        .filter(Review.id == review_id)
        .first()
    )