from sqlalchemy.orm import Session, joinedload
from typing import Optional, List
from .models import Schedule, Seat
from bus_management.models import Bus
from route_management.models import Route


def get_schedule_by_id(db: Session, schedule_id: str) -> Optional[Schedule]:
    return db.query(Schedule).filter(Schedule.id == schedule_id).first()


def list_schedules(
    db: Session,
    limit: int = 20,
    offset: int = 0,
    route_id: Optional[str] = None,
    status: Optional[str] = None,
) -> List[Schedule]:
    query = db.query(Schedule)
    if route_id:
        query = query.filter(Schedule.route_id == route_id)
    if status:
        query = query.filter(Schedule.status == status)
    return query.order_by(Schedule.departure_datetime.desc()).limit(limit).offset(offset).all()


def create_schedule(db: Session, data: dict) -> Schedule:
    schedule = Schedule(**data)
    db.add(schedule)
    db.flush()
    return schedule


def update_schedule(db: Session, schedule_id: str, data: dict) -> Optional[Schedule]:
    schedule = get_schedule_by_id(db, schedule_id)
    if not schedule:
        return None
    for key, value in data.items():
        setattr(schedule, key, value)
    db.flush()
    return schedule


def delete_schedule(db: Session, schedule_id: str) -> bool:
    schedule = get_schedule_by_id(db, schedule_id)
    if not schedule:
        return False
    db.delete(schedule)
    db.flush()
    return True


def get_schedule_with_details(db: Session, schedule_id: str) -> Optional[Schedule]:
    return (
        db.query(Schedule)
        .options(
            joinedload(Schedule.bus),
            joinedload(Schedule.route),
            joinedload(Schedule.seats),
        )
        .filter(Schedule.id == schedule_id)
        .first()
    )


def get_bus_by_id(db: Session, bus_id: str) -> Optional[Bus]:
    return db.query(Bus).filter(Bus.id == bus_id).first()


def get_route_by_id(db: Session, route_id: str) -> Optional[Route]:
    return db.query(Route).filter(Route.id == route_id).first()


def get_seat_by_id(db: Session, seat_id: str) -> Optional[Seat]:
    return db.query(Seat).filter(Seat.id == seat_id).first()


def list_seats(
    db: Session,
    limit: int = 20,
    offset: int = 0,
    schedule_id: Optional[str] = None,
    status: Optional[str] = None,
) -> List[Seat]:
    query = db.query(Seat)
    if schedule_id:
        query = query.filter(Seat.schedule_id == schedule_id)
    if status:
        query = query.filter(Seat.status == status)
    return query.order_by(Seat.seat_number).limit(limit).offset(offset).all()


def create_seat(db: Session, data: dict) -> Seat:
    seat = Seat(**data)
    db.add(seat)
    db.flush()
    return seat


def update_seat(db: Session, seat_id: str, data: dict) -> Optional[Seat]:
    seat = get_seat_by_id(db, seat_id)
    if not seat:
        return None
    for key, value in data.items():
        setattr(seat, key, value)
    db.flush()
    return seat


def delete_seat(db: Session, seat_id: str) -> bool:
    seat = get_seat_by_id(db, seat_id)
    if not seat:
        return False
    db.delete(seat)
    db.flush()
    return True


def get_seats_by_schedule(db: Session, schedule_id: str) -> List[Seat]:
    return db.query(Seat).filter(Seat.schedule_id == schedule_id).order_by(Seat.seat_number).all()


def search_schedules(
    db: Session,
    origin_city: Optional[str] = None,
    destination_city: Optional[str] = None,
    departure_date: Optional[str] = None,
    limit: int = 20,
    offset: int = 0,
) -> List[Schedule]:
    query = db.query(Schedule).join(Route)
    if origin_city:
        query = query.filter(Route.origin_city == origin_city)
    if destination_city:
        query = query.filter(Route.destination_city == destination_city)
    if departure_date:
        from datetime import datetime
        try:
            date_obj = datetime.fromisoformat(departure_date).date()
            from sqlalchemy import func
            query = query.filter(func.date(Schedule.departure_datetime) == date_obj)
        except ValueError:
            pass
    query = query.filter(Schedule.status == "scheduled")
    return query.order_by(Schedule.departure_datetime).limit(limit).offset(offset).all()


# Canonical aliases so cross-module callers can use get_by_id / create / update / delete
get_by_id = get_schedule_by_id
create = create_schedule
update = update_schedule
delete = delete_schedule
list_all = list_schedules