from sqlalchemy.orm import Session
from typing import Optional, List
from .models import Route


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


def list_all(
    db: Session,
    limit: int = 20,
    offset: int = 0,
    origin_city: Optional[str] = None,
    destination_city: Optional[str] = None,
    is_active: Optional[bool] = None,
) -> List[Route]:
    query = db.query(Route)
    
    if origin_city is not None:
        query = query.filter(Route.origin_city.ilike(f"%{origin_city}%"))
    
    if destination_city is not None:
        query = query.filter(Route.destination_city.ilike(f"%{destination_city}%"))
    
    if is_active is not None:
        query = query.filter(Route.is_active == is_active)
    
    return query.order_by(Route.created_at.desc()).limit(limit).offset(offset).all()


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


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


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


def get_by_cities(db: Session, origin_city: str, destination_city: str) -> Optional[Route]:
    return (
        db.query(Route)
        .filter(Route.origin_city == origin_city)
        .filter(Route.destination_city == destination_city)
        .first()
    )