from sqlalchemy.orm import Session, joinedload, subqueryload
from typing import Optional, List
from .models import Reservation, Reservationseat
from datetime import datetime


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


def list_reservations(
    db: Session,
    limit: int = 20,
    offset: int = 0,
    user_id: Optional[str] = None,
    schedule_id: Optional[str] = None,
    status: Optional[str] = None,
) -> List[Reservation]:
    query = db.query(Reservation)
    if user_id:
        query = query.filter(Reservation.user_id == user_id)
    if schedule_id:
        query = query.filter(Reservation.schedule_id == schedule_id)
    if status:
        query = query.filter(Reservation.status == status)
    return query.order_by(Reservation.created_at.desc()).limit(limit).offset(offset).all()


def create_reservation(db: Session, data: dict) -> Reservation:
    reservation = Reservation(**data)
    db.add(reservation)
    db.flush()
    return reservation


def update_reservation(db: Session, reservation_id: str, data: dict) -> Optional[Reservation]:
    reservation = get_reservation_by_id(db, reservation_id)
    if not reservation:
        return None
    for key, value in data.items():
        setattr(reservation, key, value)
    db.flush()
    return reservation


def delete_reservation(db: Session, reservation_id: str) -> bool:
    reservation = get_reservation_by_id(db, reservation_id)
    if not reservation:
        return False
    db.delete(reservation)
    db.flush()
    return True


def get_reservation_with_details(db: Session, reservation_id: str) -> Optional[Reservation]:
    return (
        db.query(Reservation)
        .options(
            joinedload(Reservation.user),
            joinedload(Reservation.schedule).joinedload("bus"),
            joinedload(Reservation.schedule).joinedload("route"),
            joinedload(Reservation.reservation_seats).joinedload(Reservationseat.seat),
        )
        .filter(Reservation.id == reservation_id)
        .first()
    )


def get_active_reservation_by_user_schedule(
    db: Session, user_id: str, schedule_id: str
) -> Optional[Reservation]:
    return (
        db.query(Reservation)
        .filter(
            Reservation.user_id == user_id,
            Reservation.schedule_id == schedule_id,
            Reservation.status == "active",
        )
        .first()
    )


def get_expired_reservations(db: Session, current_time: datetime) -> List[Reservation]:
    return (
        db.query(Reservation)
        .filter(
            Reservation.status == "active",
            Reservation.expiry_datetime <= current_time,
        )
        .all()
    )


def create_reservation_seat(db: Session, data: dict) -> Reservationseat:
    reservation_seat = Reservationseat(**data)
    db.add(reservation_seat)
    db.flush()
    return reservation_seat


def get_reservation_seats_by_reservation_id(db: Session, reservation_id: str) -> List[Reservationseat]:
    return (
        db.query(Reservationseat)
        .filter(Reservationseat.reservation_id == reservation_id)
        .all()
    )


def delete_reservation_seats_by_reservation_id(db: Session, reservation_id: str) -> int:
    count = (
        db.query(Reservationseat)
        .filter(Reservationseat.reservation_id == reservation_id)
        .delete()
    )
    db.flush()
    return count


# Canonical aliases so cross-module callers can use get_by_id / create / update / delete
get_by_id = get_reservation_by_id
create = create_reservation
update = update_reservation
delete = delete_reservation
list_all = list_reservations