from sqlalchemy.orm import Session, joinedload, subqueryload
from typing import Optional, List
from .models import Booking, Traveler, Tourschedule, Tourschedulehotel, Tourscheduletransportation


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


def list_all(db: Session, limit: int = 20, offset: int = 0, **filters) -> List[Booking]:
    query = db.query(Booking)
    if "customer_id" in filters and filters["customer_id"]:
        query = query.filter(Booking.customer_id == filters["customer_id"])
    if "tour_schedule_id" in filters and filters["tour_schedule_id"]:
        query = query.filter(Booking.tour_schedule_id == filters["tour_schedule_id"])
    if "booking_status" in filters and filters["booking_status"]:
        query = query.filter(Booking.booking_status == filters["booking_status"])
    if "payment_status" in filters and filters["payment_status"]:
        query = query.filter(Booking.payment_status == filters["payment_status"])
    return query.order_by(Booking.created_at.desc()).limit(limit).offset(offset).all()


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


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


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


def get_with_details(db: Session, booking_id: str) -> Optional[Booking]:
    return (
        db.query(Booking)
        .options(
            joinedload(Booking.customer).joinedload("user"),
            joinedload(Booking.tour_schedule).joinedload(Tourschedule.tour_package),
            joinedload(Booking.tour_schedule).joinedload(Tourschedule.tour_guide).joinedload("user"),
            joinedload(Booking.booking_agent),
            joinedload(Booking.discount),
            joinedload(Booking.travelers),
            joinedload(Booking.payments),
            joinedload(Booking.invoice).joinedload("invoice_line_items"),
        )
        .filter(Booking.id == booking_id)
        .first()
    )


def get_by_booking_number(db: Session, booking_number: str) -> Optional[Booking]:
    return db.query(Booking).filter(Booking.booking_number == booking_number).first()


# Traveler repository functions
def get_traveler_by_id(db: Session, entity_id: str) -> Optional[Traveler]:
    return db.query(Traveler).filter(Traveler.id == entity_id).first()


def list_travelers(db: Session, limit: int = 20, offset: int = 0, **filters) -> List[Traveler]:
    query = db.query(Traveler)
    if "booking_id" in filters and filters["booking_id"]:
        query = query.filter(Traveler.booking_id == filters["booking_id"])
    if "traveler_type" in filters and filters["traveler_type"]:
        query = query.filter(Traveler.traveler_type == filters["traveler_type"])
    return query.order_by(Traveler.created_at.desc()).limit(limit).offset(offset).all()


def create_traveler(db: Session, data: dict) -> Traveler:
    traveler = Traveler(**data)
    db.add(traveler)
    db.flush()
    return traveler


def update_traveler(db: Session, entity_id: str, data: dict) -> Optional[Traveler]:
    traveler = get_traveler_by_id(db, entity_id)
    if not traveler:
        return None
    for key, value in data.items():
        setattr(traveler, key, value)
    db.flush()
    return traveler


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


# Tourschedule repository functions
def get_tourschedule_by_id(db: Session, entity_id: str) -> Optional[Tourschedule]:
    return db.query(Tourschedule).filter(Tourschedule.id == entity_id).first()


def list_tourschedules(db: Session, limit: int = 20, offset: int = 0, **filters) -> List[Tourschedule]:
    query = db.query(Tourschedule)
    if "tour_package_id" in filters and filters["tour_package_id"]:
        query = query.filter(Tourschedule.tour_package_id == filters["tour_package_id"])
    if "tour_guide_id" in filters and filters["tour_guide_id"]:
        query = query.filter(Tourschedule.tour_guide_id == filters["tour_guide_id"])
    if "status" in filters and filters["status"]:
        query = query.filter(Tourschedule.status == filters["status"])
    return query.order_by(Tourschedule.departure_date.desc()).limit(limit).offset(offset).all()


def create_tourschedule(db: Session, data: dict) -> Tourschedule:
    tourschedule = Tourschedule(**data)
    db.add(tourschedule)
    db.flush()
    return tourschedule


def update_tourschedule(db: Session, entity_id: str, data: dict) -> Optional[Tourschedule]:
    tourschedule = get_tourschedule_by_id(db, entity_id)
    if not tourschedule:
        return None
    for key, value in data.items():
        setattr(tourschedule, key, value)
    db.flush()
    return tourschedule


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


def get_tourschedule_with_details(db: Session, schedule_id: str) -> Optional[Tourschedule]:
    return (
        db.query(Tourschedule)
        .options(
            joinedload(Tourschedule.tour_package).joinedload("category"),
            joinedload(Tourschedule.tour_guide).joinedload("user"),
            joinedload(Tourschedule.tour_schedule_hotels).joinedload(Tourschedulehotel.hotel),
            joinedload(Tourschedule.tour_schedule_hotels).joinedload(Tourschedulehotel.room_type),
            joinedload(Tourschedule.tour_schedule_transportations).joinedload(Tourscheduletransportation.transportation),
        )
        .filter(Tourschedule.id == schedule_id)
        .first()
    )


# Tourschedulehotel repository functions
def get_tourschedulehotel_by_id(db: Session, entity_id: str) -> Optional[Tourschedulehotel]:
    return db.query(Tourschedulehotel).filter(Tourschedulehotel.id == entity_id).first()


def list_tourschedulehotels(db: Session, limit: int = 20, offset: int = 0, **filters) -> List[Tourschedulehotel]:
    query = db.query(Tourschedulehotel)
    if "tour_schedule_id" in filters and filters["tour_schedule_id"]:
        query = query.filter(Tourschedulehotel.tour_schedule_id == filters["tour_schedule_id"])
    if "hotel_id" in filters and filters["hotel_id"]:
        query = query.filter(Tourschedulehotel.hotel_id == filters["hotel_id"])
    if "booking_status" in filters and filters["booking_status"]:
        query = query.filter(Tourschedulehotel.booking_status == filters["booking_status"])
    return query.order_by(Tourschedulehotel.created_at.desc()).limit(limit).offset(offset).all()


def create_tourschedulehotel(db: Session, data: dict) -> Tourschedulehotel:
    tourschedulehotel = Tourschedulehotel(**data)
    db.add(tourschedulehotel)
    db.flush()
    return tourschedulehotel


def update_tourschedulehotel(db: Session, entity_id: str, data: dict) -> Optional[Tourschedulehotel]:
    tourschedulehotel = get_tourschedulehotel_by_id(db, entity_id)
    if not tourschedulehotel:
        return None
    for key, value in data.items():
        setattr(tourschedulehotel, key, value)
    db.flush()
    return tourschedulehotel


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


# Tourscheduletransportation repository functions
def get_tourscheduletransportation_by_id(db: Session, entity_id: str) -> Optional[Tourscheduletransportation]:
    return db.query(Tourscheduletransportation).filter(Tourscheduletransportation.id == entity_id).first()


def list_tourscheduletransportations(db: Session, limit: int = 20, offset: int = 0, **filters) -> List[Tourscheduletransportation]:
    query = db.query(Tourscheduletransportation)
    if "tour_schedule_id" in filters and filters["tour_schedule_id"]:
        query = query.filter(Tourscheduletransportation.tour_schedule_id == filters["tour_schedule_id"])
    if "transportation_id" in filters and filters["transportation_id"]:
        query = query.filter(Tourscheduletransportation.transportation_id == filters["transportation_id"])
    if "booking_status" in filters and filters["booking_status"]:
        query = query.filter(Tourscheduletransportation.booking_status == filters["booking_status"])
    return query.order_by(Tourscheduletransportation.created_at.desc()).limit(limit).offset(offset).all()


def create_tourscheduletransportation(db: Session, data: dict) -> Tourscheduletransportation:
    tourscheduletransportation = Tourscheduletransportation(**data)
    db.add(tourscheduletransportation)
    db.flush()
    return tourscheduletransportation


def update_tourscheduletransportation(db: Session, entity_id: str, data: dict) -> Optional[Tourscheduletransportation]:
    tourscheduletransportation = get_tourscheduletransportation_by_id(db, entity_id)
    if not tourscheduletransportation:
        return None
    for key, value in data.items():
        setattr(tourscheduletransportation, key, value)
    db.flush()
    return tourscheduletransportation


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