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


def get_booking_by_id(db: Session, booking_id: str) -> Optional[Booking]:
    return db.query(Booking).filter(Booking.id == booking_id).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 list_bookings(
    db: Session,
    limit: int = 20,
    offset: int = 0,
    user_id: Optional[str] = None,
    schedule_id: Optional[str] = None,
    status: Optional[str] = None,
) -> List[Booking]:
    query = db.query(Booking)
    if user_id:
        query = query.filter(Booking.user_id == user_id)
    if schedule_id:
        query = query.filter(Booking.schedule_id == schedule_id)
    if status:
        query = query.filter(Booking.status == status)
    return query.order_by(Booking.created_at.desc()).limit(limit).offset(offset).all()


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


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


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


def get_booking_with_details(db: Session, booking_id: str) -> Optional[Booking]:
    return (
        db.query(Booking)
        .options(
            joinedload(Booking.user),
            joinedload(Booking.schedule).joinedload("bus"),
            joinedload(Booking.schedule).joinedload("route"),
            joinedload(Booking.booking_seats).joinedload(Bookingseat.seat),
            joinedload(Booking.ticket),
            subqueryload(Booking.payments),
        )
        .filter(Booking.id == booking_id)
        .first()
    )


def get_bookingseat_by_id(db: Session, bookingseat_id: str) -> Optional[Bookingseat]:
    return db.query(Bookingseat).filter(Bookingseat.id == bookingseat_id).first()


def list_bookingseats(
    db: Session,
    limit: int = 20,
    offset: int = 0,
    booking_id: Optional[str] = None,
) -> List[Bookingseat]:
    query = db.query(Bookingseat)
    if booking_id:
        query = query.filter(Bookingseat.booking_id == booking_id)
    return query.order_by(Bookingseat.created_at.desc()).limit(limit).offset(offset).all()


def create_bookingseat(db: Session, data: dict) -> Bookingseat:
    bookingseat = Bookingseat(**data)
    db.add(bookingseat)
    db.flush()
    return bookingseat


def update_bookingseat(db: Session, bookingseat_id: str, data: dict) -> Optional[Bookingseat]:
    bookingseat = get_bookingseat_by_id(db, bookingseat_id)
    if not bookingseat:
        return None
    for key, value in data.items():
        setattr(bookingseat, key, value)
    db.flush()
    return bookingseat


def delete_bookingseat(db: Session, bookingseat_id: str) -> bool:
    bookingseat = get_bookingseat_by_id(db, bookingseat_id)
    if not bookingseat:
        return False
    db.delete(bookingseat)
    db.flush()
    return True


def get_ticket_by_id(db: Session, ticket_id: str) -> Optional[Ticket]:
    return db.query(Ticket).filter(Ticket.id == ticket_id).first()


def get_ticket_by_number(db: Session, ticket_number: str) -> Optional[Ticket]:
    return db.query(Ticket).filter(Ticket.ticket_number == ticket_number).first()


def get_ticket_by_booking_id(db: Session, booking_id: str) -> Optional[Ticket]:
    return db.query(Ticket).filter(Ticket.booking_id == booking_id).first()


def list_tickets(
    db: Session,
    limit: int = 20,
    offset: int = 0,
    validity_status: Optional[str] = None,
) -> List[Ticket]:
    query = db.query(Ticket)
    if validity_status:
        query = query.filter(Ticket.validity_status == validity_status)
    return query.order_by(Ticket.created_at.desc()).limit(limit).offset(offset).all()


def create_ticket(db: Session, data: dict) -> Ticket:
    ticket = Ticket(**data)
    db.add(ticket)
    db.flush()
    return ticket


def update_ticket(db: Session, ticket_id: str, data: dict) -> Optional[Ticket]:
    ticket = get_ticket_by_id(db, ticket_id)
    if not ticket:
        return None
    for key, value in data.items():
        setattr(ticket, key, value)
    db.flush()
    return ticket


def delete_ticket(db: Session, ticket_id: str) -> bool:
    ticket = get_ticket_by_id(db, ticket_id)
    if not ticket:
        return False
    db.delete(ticket)
    db.flush()
    return True


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


# Canonical aliases so cross-module callers can use get_by_id / create / update / delete
get_by_id = get_booking_by_id
create = create_booking
update = update_booking
delete = delete_booking
list_all = list_bookings