from sqlalchemy.orm import Session, joinedload, subqueryload
from typing import Optional, List
from .models import (
    Category,
    Destination,
    Season,
    Amenity,
    Tourpackage,
    Tourpackagedestination,
    Tourpackageamenity,
    Itinerary,
)


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


def list_all(db: Session, limit: int = 20, offset: int = 0, **filters) -> List[Category]:
    query = db.query(Category)
    if filters.get("is_active") is not None:
        query = query.filter(Category.is_active == filters["is_active"])
    if filters.get("search"):
        search_term = f"%{filters['search']}%"
        query = query.filter(Category.name.ilike(search_term))
    return query.order_by(Category.display_order.asc(), Category.name.asc()).limit(limit).offset(offset).all()


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


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


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


def get_by_slug(db: Session, slug: str) -> Optional[Category]:
    return db.query(Category).filter(Category.slug == slug).first()


# Destination Repository
def get_destination_by_id(db: Session, entity_id: str) -> Optional[Destination]:
    return db.query(Destination).filter(Destination.id == entity_id).first()


def list_destinations(db: Session, limit: int = 20, offset: int = 0, **filters) -> List[Destination]:
    query = db.query(Destination)
    if filters.get("is_active") is not None:
        query = query.filter(Destination.is_active == filters["is_active"])
    if filters.get("country"):
        query = query.filter(Destination.country == filters["country"])
    if filters.get("search"):
        search_term = f"%{filters['search']}%"
        query = query.filter(Destination.name.ilike(search_term))
    return query.order_by(Destination.name.asc()).limit(limit).offset(offset).all()


def create_destination(db: Session, data: dict) -> Destination:
    destination = Destination(**data)
    db.add(destination)
    db.flush()
    return destination


def update_destination(db: Session, entity_id: str, data: dict) -> Optional[Destination]:
    destination = get_destination_by_id(db, entity_id)
    if not destination:
        return None
    for key, value in data.items():
        setattr(destination, key, value)
    db.flush()
    return destination


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


def get_destination_by_slug(db: Session, slug: str) -> Optional[Destination]:
    return db.query(Destination).filter(Destination.slug == slug).first()


# Season Repository
def get_season_by_id(db: Session, entity_id: str) -> Optional[Season]:
    return db.query(Season).filter(Season.id == entity_id).first()


def list_seasons(db: Session, limit: int = 20, offset: int = 0, **filters) -> List[Season]:
    query = db.query(Season)
    if filters.get("season_type"):
        query = query.filter(Season.season_type == filters["season_type"])
    return query.order_by(Season.start_date.asc()).limit(limit).offset(offset).all()


def create_season(db: Session, data: dict) -> Season:
    season = Season(**data)
    db.add(season)
    db.flush()
    return season


def update_season(db: Session, entity_id: str, data: dict) -> Optional[Season]:
    season = get_season_by_id(db, entity_id)
    if not season:
        return None
    for key, value in data.items():
        setattr(season, key, value)
    db.flush()
    return season


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


# Amenity Repository
def get_amenity_by_id(db: Session, entity_id: str) -> Optional[Amenity]:
    return db.query(Amenity).filter(Amenity.id == entity_id).first()


def list_amenities(db: Session, limit: int = 20, offset: int = 0, **filters) -> List[Amenity]:
    query = db.query(Amenity)
    if filters.get("amenity_type"):
        query = query.filter(Amenity.amenity_type == filters["amenity_type"])
    if filters.get("search"):
        search_term = f"%{filters['search']}%"
        query = query.filter(Amenity.name.ilike(search_term))
    return query.order_by(Amenity.name.asc()).limit(limit).offset(offset).all()


def create_amenity(db: Session, data: dict) -> Amenity:
    amenity = Amenity(**data)
    db.add(amenity)
    db.flush()
    return amenity


def update_amenity(db: Session, entity_id: str, data: dict) -> Optional[Amenity]:
    amenity = get_amenity_by_id(db, entity_id)
    if not amenity:
        return None
    for key, value in data.items():
        setattr(amenity, key, value)
    db.flush()
    return amenity


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


# Tourpackage Repository
def get_tourpackage_by_id(db: Session, entity_id: str) -> Optional[Tourpackage]:
    return db.query(Tourpackage).filter(Tourpackage.id == entity_id).first()


def list_tourpackages(db: Session, limit: int = 20, offset: int = 0, **filters) -> List[Tourpackage]:
    query = db.query(Tourpackage)
    if filters.get("is_active") is not None:
        query = query.filter(Tourpackage.is_active == filters["is_active"])
    if filters.get("is_featured") is not None:
        query = query.filter(Tourpackage.is_featured == filters["is_featured"])
    if filters.get("category_id"):
        query = query.filter(Tourpackage.category_id == filters["category_id"])
    if filters.get("difficulty_level"):
        query = query.filter(Tourpackage.difficulty_level == filters["difficulty_level"])
    if filters.get("search"):
        search_term = f"%{filters['search']}%"
        query = query.filter(Tourpackage.name.ilike(search_term))
    return query.order_by(Tourpackage.created_at.desc()).limit(limit).offset(offset).all()


def create_tourpackage(db: Session, data: dict) -> Tourpackage:
    tourpackage = Tourpackage(**data)
    db.add(tourpackage)
    db.flush()
    return tourpackage


def update_tourpackage(db: Session, entity_id: str, data: dict) -> Optional[Tourpackage]:
    tourpackage = get_tourpackage_by_id(db, entity_id)
    if not tourpackage:
        return None
    for key, value in data.items():
        setattr(tourpackage, key, value)
    db.flush()
    return tourpackage


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


def get_tourpackage_by_slug(db: Session, slug: str) -> Optional[Tourpackage]:
    return db.query(Tourpackage).filter(Tourpackage.slug == slug).first()


def get_tourpackage_with_details(db: Session, entity_id: str) -> Optional[Tourpackage]:
    return (
        db.query(Tourpackage)
        .options(
            joinedload(Tourpackage.category),
            joinedload(Tourpackage.itineraries),
            joinedload(Tourpackage.tour_package_destinations).joinedload(Tourpackagedestination.destination),
            joinedload(Tourpackage.tour_package_amenities).joinedload(Tourpackageamenity.amenity),
        )
        .filter(Tourpackage.id == entity_id)
        .first()
    )


# Tourpackagedestination Repository
def get_tourpackagedestination_by_id(db: Session, entity_id: str) -> Optional[Tourpackagedestination]:
    return db.query(Tourpackagedestination).filter(Tourpackagedestination.id == entity_id).first()


def list_tourpackagedestinations(db: Session, limit: int = 20, offset: int = 0, **filters) -> List[Tourpackagedestination]:
    query = db.query(Tourpackagedestination)
    if filters.get("tour_package_id"):
        query = query.filter(Tourpackagedestination.tour_package_id == filters["tour_package_id"])
    if filters.get("destination_id"):
        query = query.filter(Tourpackagedestination.destination_id == filters["destination_id"])
    return query.order_by(Tourpackagedestination.visit_order.asc()).limit(limit).offset(offset).all()


def create_tourpackagedestination(db: Session, data: dict) -> Tourpackagedestination:
    tourpackagedestination = Tourpackagedestination(**data)
    db.add(tourpackagedestination)
    db.flush()
    return tourpackagedestination


def update_tourpackagedestination(db: Session, entity_id: str, data: dict) -> Optional[Tourpackagedestination]:
    tourpackagedestination = get_tourpackagedestination_by_id(db, entity_id)
    if not tourpackagedestination:
        return None
    for key, value in data.items():
        setattr(tourpackagedestination, key, value)
    db.flush()
    return tourpackagedestination


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


# Tourpackageamenity Repository
def get_tourpackageamenity_by_id(db: Session, entity_id: str) -> Optional[Tourpackageamenity]:
    return db.query(Tourpackageamenity).filter(Tourpackageamenity.id == entity_id).first()


def list_tourpackageamenities(db: Session, limit: int = 20, offset: int = 0, **filters) -> List[Tourpackageamenity]:
    query = db.query(Tourpackageamenity)
    if filters.get("tour_package_id"):
        query = query.filter(Tourpackageamenity.tour_package_id == filters["tour_package_id"])
    if filters.get("amenity_id"):
        query = query.filter(Tourpackageamenity.amenity_id == filters["amenity_id"])
    return query.limit(limit).offset(offset).all()


def create_tourpackageamenity(db: Session, data: dict) -> Tourpackageamenity:
    tourpackageamenity = Tourpackageamenity(**data)
    db.add(tourpackageamenity)
    db.flush()
    return tourpackageamenity


def update_tourpackageamenity(db: Session, entity_id: str, data: dict) -> Optional[Tourpackageamenity]:
    tourpackageamenity = get_tourpackageamenity_by_id(db, entity_id)
    if not tourpackageamenity:
        return None
    for key, value in data.items():
        setattr(tourpackageamenity, key, value)
    db.flush()
    return tourpackageamenity


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


# Itinerary Repository
def get_itinerary_by_id(db: Session, entity_id: str) -> Optional[Itinerary]:
    return db.query(Itinerary).filter(Itinerary.id == entity_id).first()


def list_itineraries(db: Session, limit: int = 20, offset: int = 0, **filters) -> List[Itinerary]:
    query = db.query(Itinerary)
    if filters.get("tour_package_id"):
        query = query.filter(Itinerary.tour_package_id == filters["tour_package_id"])
    return query.order_by(Itinerary.day_number.asc()).limit(limit).offset(offset).all()


def create_itinerary(db: Session, data: dict) -> Itinerary:
    itinerary = Itinerary(**data)
    db.add(itinerary)
    db.flush()
    return itinerary


def update_itinerary(db: Session, entity_id: str, data: dict) -> Optional[Itinerary]:
    itinerary = get_itinerary_by_id(db, entity_id)
    if not itinerary:
        return None
    for key, value in data.items():
        setattr(itinerary, key, value)
    db.flush()
    return itinerary


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