""" API SNCF - Horaires des prochains trains Basée sur les données GTFS SNCF """ from fastapi import FastAPI, Query, HTTPException from fastapi.responses import JSONResponse import sqlite3 from datetime import datetime, date from typing import Optional import os import traceback from sqlalchemy import create_engine import psycopg2 from psycopg2.extras import RealDictCursor app = FastAPI( title="HappyTrain API - Horaires SNCF", description="API de consultation des horaires de trains à partir des données GTFS SNCF", version="1.0.0", ) def get_conn(type="sqlite"): if type == "sqlite": DB_PATH = os.environ.get("DB_PATH", "./gtfs_sncf.db") conn = sqlite3.connect(DB_PATH) conn.row_factory = sqlite3.Row return conn elif type == "postgres": url = os.environ.get("NEONDB_URL") print(f"--------→ Connecting to database with URL: {url}") if url: engine = create_engine(url) return engine.connect() else: conn = psycopg2.connect( host=os.environ.get("NEONDB_HOST", "localhost"), port=int(os.environ.get("NEONDB_PORT", 5432)), dbname=os.environ.get("NEONDB_DB", "gtfs_sncf"), user=os.environ.get("NEONDB_USER", "postgres"), password=os.environ.get("NEONDB_PASSWORD", "password"), ) return conn.cursor(cursor_factory=RealDictCursor) # ───────────────────────────────────────────── # UTILITAIRES # ───────────────────────────────────────────── def current_gtfs_date() -> str: """Date du jour au format GTFS : YYYYMMDD""" return datetime.now().strftime("%Y%m%d") def current_time_str() -> str: """Heure actuelle HH:MM:SS""" return datetime.now().strftime("%H:%M:%S") # ───────────────────────────────────────────── # ENDPOINTS # ───────────────────────────────────────────── @app.get("/", summary="Bienvenue") def root(): return { "message": "API Horaires SNCF - GTFS", "endpoints": { "recherche_gare": "/stops/search?q=Paris", "prochains_trains": "/next-trains?stop_id=StopPoint:OCETGV INOUI-71043075", "trains_entre_gares": "/trains?from_stop_id=...&to_stop_id=...&date=20260218", "details_gare": "/stops/{stop_id}", "lignes": "/routes?q=TGV", }, } @app.get("/stops/search", summary="Rechercher une gare par nom") def search_stops( q: str = Query(..., description="Nom ou partie du nom de la gare", min_length=2), limit: int = Query(20, le=100), ): """Recherche une gare par son nom (recherche partielle insensible à la casse).""" conn = get_conn() rows = conn.execute( """ SELECT stop_id, stop_name, stop_lat, stop_lon, location_type, parent_station FROM stops WHERE stop_name LIKE ? ORDER BY location_type, stop_name LIMIT ? """, (f"%{q}%", limit), ).fetchall() conn.close() return [dict(r) for r in rows] @app.get("/stops/{stop_id}", summary="Détails d'une gare") def get_stop(stop_id: str): """Retourne les informations complètes d'un arrêt.""" conn = get_conn() row = conn.execute( "SELECT * FROM stops WHERE stop_id = ?", (stop_id,) ).fetchone() conn.close() if not row: raise HTTPException(404, f"Arrêt '{stop_id}' introuvable") return dict(row) @app.get("/next-trains", summary="Prochains trains depuis une gare") def next_trains( stop_id: str = Query(..., description="Identifiant de l'arrêt (stop_id)"), date: Optional[str] = Query(None, description="Date YYYYMMDD (défaut: aujourd'hui)"), time: Optional[str] = Query(None, description="Heure de départ HH:MM:SS (défaut: maintenant)"), limit: int = Query(10, le=50, description="Nombre de trains à retourner"), ): """ Retourne les prochains trains au départ d'un arrêt donné. Les trains sont filtrés sur la date (via calendar_dates) et triés par heure de départ. Attention : le GTFS SNCF peut avoir des heures > 24:00 (service nocturne). """ gtfs_date = date or current_gtfs_date() from_time = time or current_time_str() conn = get_conn() # Vérifier que l'arrêt existe stop = conn.execute("SELECT stop_name FROM stops WHERE stop_id = ?", (stop_id,)).fetchone() if not stop: conn.close() raise HTTPException(404, f"Arrêt '{stop_id}' introuvable") rows = conn.execute( """ SELECT st.departure_time, st.arrival_time, st.stop_sequence, t.trip_headsign AS destination, t.direction_id, t.trip_id, r.route_short_name AS ligne, r.route_long_name, r.route_type, ag.agency_name FROM stop_times st JOIN trips t ON st.trip_id = t.trip_id JOIN routes r ON t.route_id = r.route_id JOIN agency ag ON r.agency_id = ag.agency_id JOIN calendar_dates cd ON t.service_id = cd.service_id WHERE st.stop_id = ? AND cd.date = ? AND cd.exception_type = 1 AND st.departure_time >= ? ORDER BY st.departure_time LIMIT ? """, (stop_id, gtfs_date, from_time, limit), ).fetchall() conn.close() return { "stop_id": stop_id, "stop_name": stop["stop_name"], "date": gtfs_date, "from_time": from_time, "count": len(rows), "trains": [dict(r) for r in rows], } @app.get("/trains", summary="Trains entre deux gares") def trains_between( from_stop_id: str = Query(..., description="Arrêt de départ (stop_id)"), to_stop_id: str = Query(..., description="Arrêt d'arrivée (stop_id)"), date: Optional[str] = Query(None, description="Date YYYYMMDD (défaut: aujourd'hui)"), time: Optional[str] = Query(None, description="Heure mini HH:MM:SS (défaut: maintenant)"), limit: int = Query(10, le=50), ): """ Trains directs entre deux gares à une date donnée. Seuls les trains directs (sans correspondance) sont retournés. """ gtfs_date = date or current_gtfs_date() from_time = time or current_time_str() conn = get_conn() # Vérifier les arrêts for sid, label in [(from_stop_id, "départ"), (to_stop_id, "arrivée")]: if not conn.execute("SELECT 1 FROM stops WHERE stop_id=?", (sid,)).fetchone(): conn.close() raise HTTPException(404, f"Arrêt de {label} '{sid}' introuvable") rows = conn.execute( """ SELECT st_from.departure_time AS depart, st_to.arrival_time AS arrivee, t.trip_headsign AS destination, t.trip_id, r.route_short_name AS ligne, r.route_long_name, ag.agency_name FROM stop_times st_from JOIN stop_times st_to ON st_from.trip_id = st_to.trip_id AND st_to.stop_sequence > st_from.stop_sequence JOIN trips t ON st_from.trip_id = t.trip_id JOIN routes r ON t.route_id = r.route_id JOIN agency ag ON r.agency_id = ag.agency_id JOIN calendar_dates cd ON t.service_id = cd.service_id WHERE st_from.stop_id = ? AND st_to.stop_id = ? AND cd.date = ? AND cd.exception_type = 1 AND st_from.departure_time >= ? ORDER BY st_from.departure_time LIMIT ? """, (from_stop_id, to_stop_id, gtfs_date, from_time, limit), ).fetchall() conn.close() return { "from_stop_id": from_stop_id, "to_stop_id": to_stop_id, "date": gtfs_date, "from_time": from_time, "count": len(rows), "trains": [dict(r) for r in rows], } @app.get("/routes", summary="Rechercher une ligne") def search_routes( q: Optional[str] = Query(None, description="Nom ou numéro de ligne"), route_type: Optional[int] = Query(None, description="Type GTFS (2=rail, 3=bus…)"), limit: int = Query(20, le=100), ): """Liste des lignes avec filtre optionnel par nom ou type.""" conn = get_conn() query = "SELECT route_id, route_short_name, route_long_name, route_type, agency_id FROM routes WHERE 1=1" params = [] if q: query += " AND (route_short_name LIKE ? OR route_long_name LIKE ?)" params += [f"%{q}%", f"%{q}%"] if route_type is not None: query += " AND route_type = ?" params.append(route_type) query += f" ORDER BY route_short_name LIMIT {limit}" rows = conn.execute(query, params).fetchall() conn.close() return [dict(r) for r in rows] @app.get("/routes/{route_id}/stops", summary="Arrêts d'une ligne") def route_stops( route_id: str, date: Optional[str] = Query(None, description="Date YYYYMMDD (défaut: aujourd'hui)"), ): """Retourne tous les arrêts desservis par une ligne à une date donnée.""" gtfs_date = date or current_gtfs_date() conn = get_conn() rows = conn.execute( """ SELECT DISTINCT s.stop_id, s.stop_name, s.stop_lat, s.stop_lon FROM stop_times st JOIN trips t ON st.trip_id = t.trip_id JOIN stops s ON st.stop_id = s.stop_id JOIN calendar_dates cd ON t.service_id = cd.service_id WHERE t.route_id = ? AND cd.date = ? AND cd.exception_type = 1 ORDER BY s.stop_name """, (route_id, gtfs_date), ).fetchall() conn.close() return [dict(r) for r in rows] @app.get("/health", summary="Santé de l'API") def health_check(): conn = get_conn("postgres") stats = {} for table in ["stops", "routes", "trips", "stop_times", "calendar_dates"]: stats[table] = conn.execute(f"SELECT COUNT(*) FROM {table}").fetchone()[0] conn.close() return { "status": "healthy", "model_loaded": "model is not None", "mlflow_uri": os.getenv("MLFLOW_TRACKING_URI", "https://davidrambeau-bloc3-mlflow.hf.space"), "database_stats": stats, } def headth(): try: conn = get_conn("postgres") stats = {} for table in ["stops", "routes", "trips", "stop_times", "calendar_dates"]: stats[table] = conn.execute(f"SELECT COUNT(*) FROM {table}").fetchone()[0] conn.close() return {"status": "ok", "db": DB_PATH, "tables": stats} except Exception as e: traceback.print_exc() return JSONResponse(status_code=500, content={"status": "error", "message": str(e)})