from sqlalchemy.orm import Session, joinedload, subqueryload
from typing import Optional, List
from .models import Passenger, Booking


def get_by_id(db: Session, entity_id: str, model_class) -> Optional[Passenger | Booking]:
    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[Passenger | Booking]:
    query = db.query(model_class)
    
    if model_class == Passenger:
        if filters.get("search"):
            search_term = f"%{filters['search']}%"
            query = query.filter(
                (Passenger.first_name.ilike(search_term)) |
                (Passenger.last_name.ilike(search_term)) |
                (Passenger.email.ilike(search_term)) |
                (Passenger.passport_number.ilike(search_term))
            )
    
    if model_class == Booking:
        if filters.get("search"):
            search_term = f"%{filters['search']}%"
            query = query.filter(Booking.booking_reference.ilike(search_term))
        if filters.get("booking_status"):
            query = query.filter(Booking.booking_status == filters["booking_status"])
        if filters.get("payment_status"):
            query = query.filter(Booking.payment_status == filters["payment_status"])
        if filters.get("passenger_id"):
            query = query.filter(Booking.passenger_id == filters["passenger_id"])
        if filters.get("flight_id"):
            query = query.filter(Booking.flight_id == filters["flight_id"])
    
    return query.order_by(model_class.created_at.desc()).limit(limit).offset(offset).all()


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


def update(db: Session, entity_id: str, data: dict, model_class) -> Optional[Passenger | Booking]:
    instance = get_by_id(db, entity_id, model_class)
    if not instance:
        return None
    for key, value in data.items():
        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_passenger_by_email(db: Session, email: str) -> Optional[Passenger]:
    return db.query(Passenger).filter(Passenger.email == email).first()


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


def get_booking_with_details(db: Session, booking_id: str) -> Optional[Booking]:
    return (
        db.query(Booking)
        .options(
            joinedload(Booking.passenger),
            joinedload(Booking.flight)
                .joinedload("route")
                .joinedload("origin_airport"),
            joinedload(Booking.flight)
                .joinedload("route")
                .joinedload("destination_airport"),
            joinedload(Booking.flight)
                .joinedload("aircraft"),
            joinedload(Booking.seat),
            joinedload(Booking.fare_class),
        )
        .filter(Booking.id == booking_id)
        .first()
    )


def get_passenger_with_details(db: Session, passenger_id: str) -> Optional[Passenger]:
    return (
        db.query(Passenger)
        .options(
            joinedload(Passenger.bookings)
                .joinedload(Booking.flight)
                .joinedload("route")
                .joinedload("origin_airport"),
            joinedload(Passenger.bookings)
                .joinedload(Booking.flight)
                .joinedload("route")
                .joinedload("destination_airport"),
        )
        .filter(Passenger.id == passenger_id)
        .first()
    )


def get_bookings_by_flight_id(db: Session, flight_id: str) -> List[Booking]:
    return db.query(Booking).filter(Booking.flight_id == flight_id).all()


def get_booking_by_seat_and_flight(db: Session, seat_id: str, flight_id: str) -> Optional[Booking]:
    return (
        db.query(Booking)
        .filter(
            Booking.seat_id == seat_id,
            Booking.flight_id == flight_id,
            Booking.booking_status.in_(["Confirmed", "Pending", "Checked In", "Boarded"])
        )
        .first()
    )