from fastapi import APIRouter, Request, Query
from fastapi.responses import HTMLResponse
from fastapi.templating import Jinja2Templates
from app.db import engine
from sqlalchemy import text

router = APIRouter()
templates = Jinja2Templates(directory="app/templates")

@router.get("/search", response_class=HTMLResponse)
def search(request: Request,
           from_city: str = Query(..., alias="from"),
           to_city: str = Query(..., alias="to")):
    """
    Recherche de lignes de bus entre deux villes.
    """
    query = text("""
        SELECT r.id AS route_id,
               r.name AS route_name,
               c.name AS company_name,
               s1.name AS departure_stop,
               s2.name AS arrival_stop
        FROM routes r
        JOIN companies c ON r.company_id = c.id
        JOIN route_stops rs1 ON rs1.route_id = r.id
        JOIN route_stops rs2 ON rs2.route_id = r.id
        JOIN stops s1 ON rs1.stop_id = s1.id
        JOIN stops s2 ON rs2.stop_id = s2.id
        WHERE s1.city_name = :from_city
          AND s2.city_name = :to_city
          AND rs1.sequence < rs2.sequence
        LIMIT 50;
    """)
    with engine.connect() as conn:
        results = conn.execute(query, {"from_city": from_city, "to_city": to_city}).mappings().all()
    return templates.TemplateResponse(
        "search_results.html",
        {"request": request, "results": results, "from_city": from_city, "to_city": to_city},
    )