WITH
  parameters AS (
  SELECT DATE('2026-06-27') AS jour
       , TIME('00:00:00') AS heure
       , (SELECT id FROM tec_stops WHERE name = 'Perpignan' AND parent_station IS NULL) AS départ_id
       , (SELECT id FROM tec_stops WHERE name = 'Strasbourg' AND parent_station IS NULL) AS arrivée_id
  ),
  trajet_passant_par_gare_depart AS (
  SELECT trips.id
       , stop_times.departure_time
       , trips.service_id
       , trips.direction
       , trips.headsign
  FROM tec_stops stops
  INNER JOIN tec_stop_times     stop_times
    ON stop_times.stop_id        = stops.id
  INNER JOIN tec_trips          trips
    ON trips.id                  = stop_times.trip_id
  INNER JOIN tec_routes         routes
    ON routes.id                 = trips.route_id
  INNER JOIN tec_calendar_dates calendar_dates
    ON calendar_dates.service_id = trips.service_id
  WHERE 1=1
  AND stops.id IN (
    SELECT id
      FROM tec_stops
      WHERE parent_station = (SELECT départ_id FROM parameters)
    UNION
      SELECT (SELECT départ_id FROM parameters)
  )
  AND calendar_dates.cal_date = (SELECT jour FROM parameters)
  AND stop_times.arrival_time > (SELECT heure FROM parameters)
),
trajet_passant_par_gare_arrivee AS (
  SELECT trips.id
       , stop_times.arrival_time
       , trips.service_id
       , trips.direction
       , trips.headsign
  FROM tec_stops stops
  INNER JOIN tec_stop_times     stop_times
    ON stop_times.stop_id        = stops.id
  INNER JOIN tec_trips          trips
    ON trips.id                  = stop_times.trip_id
  INNER JOIN tec_routes         routes
    ON routes.id                 = trips.route_id
  INNER JOIN tec_calendar_dates calendar_dates
    ON calendar_dates.service_id = trips.service_id
  WHERE 1=1
  AND stops.id IN (
    SELECT id
      FROM tec_stops
      WHERE parent_station = (SELECT arrivée_id FROM parameters)
    UNION
      SELECT (SELECT arrivée_id FROM parameters)
  )
  AND calendar_dates.cal_date = (SELECT jour FROM parameters)
  AND stop_times.arrival_time > (SELECT heure FROM parameters)
),
trajet_passant_par_gare_depart_et_gare_arrivee AS (
  SELECT tppgd.departure_time AS départ
       , tppga.arrival_time AS arrivée
       , tppgd.headsign
       , tec_routes.long_name
  FROM trajet_passant_par_gare_depart tppgd
  INNER JOIN trajet_passant_par_gare_arrivee tppga
     ON tppga.arrival_time > tppgd.departure_time
    AND tppga.service_id   = tppgd.service_id
    AND tppga.direction    = tppgd.direction
    AND tppga.headsign     = tppgd.headsign
  INNER JOIN tec_trips
    ON tec_trips.id  = tppgd.id
  INNER JOIN tec_routes ON tec_routes.id = tec_trips.route_id
)
SELECT TIMESTAMP((SELECT jour FROM parameters), tppgdega.départ)
	, TIMESTAMP((SELECT jour FROM parameters), tppgdega.arrivée)
	, tppgdega.headsign
	, tppgdega.long_name
FROM trajet_passant_par_gare_depart_et_gare_arrivee tppgdega
WHERE 1=1
ORDER BY départ