WITH
trajet_aller AS ( -- ------------------------------------------- -- aller ou retour
  SELECT TRUE AS is_aller
),
gares_cibles AS ( -- ------------------------------------------- -- gares de départ et d'arrivée
  SELECT 'Muret' AS départ, 'Toulouse Matabiau' AS arrivée
),
gare_depart AS ( -- -------------------------------------------- -- ids de la gare de départ
  SELECT stop.id
  FROM tec_stops stop
  WHERE stop.name = (SELECT départ FROM gares_cibles)
),
gare_arrivee AS ( -- ------------------------------------------- -- ids de la gare de d'arrivée
  SELECT stop.id
  FROM tec_stops stop
  WHERE stop.name = (SELECT arrivée FROM gares_cibles)
),
trajet_passant_par_gare_depart AS ( -- ------------------------- -- tous les trains qui passeront par la gare de départ ajourd'hui.
  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 * FROM gare_depart)
  AND calendar_dates.cal_date = CURRENT_DATE
  AND stop_times.arrival_time > CURRENT_TIME
),
trajet_passant_par_gare_arrivee AS (-- ------------------------- -- tous les trains qui passeront par la gare de d'arrivée ajourd'hui.
  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 * FROM gare_arrivee)
  AND calendar_dates.cal_date = CURRENT_DATE
  AND stop_times.arrival_time > CURRENT_TIME
),
trajet_passant_par_gare_depart_et_gare_arrivee AS ( -- --------- -- tous les trains qui passeront par les gares de départ et d'arrivée et dans l'ordre demandé par trajet_aller.is_aller
  SELECT CASE WHEN (SELECT is_aller FROM trajet_aller) THEN trajet_passant_par_gare_depart.arrival_time ELSE trajet_passant_par_gare_arrivee.arrival_time END AS départ
       , CASE WHEN (SELECT is_aller FROM trajet_aller) THEN trajet_passant_par_gare_arrivee.arrival_time ELSE trajet_passant_par_gare_depart.arrival_time END AS arrivée
       , trajet_passant_par_gare_depart.headsign
       , tec_routes.long_name
  FROM trajet_passant_par_gare_depart
  INNER JOIN trajet_passant_par_gare_arrivee
     ON (trajet_passant_par_gare_arrivee.arrival_time > trajet_passant_par_gare_depart.arrival_time) = (SELECT is_aller FROM trajet_aller)
    AND trajet_passant_par_gare_arrivee.service_id   = trajet_passant_par_gare_depart.service_id
    AND trajet_passant_par_gare_arrivee.direction    = trajet_passant_par_gare_depart.direction
    AND trajet_passant_par_gare_arrivee.headsign     = trajet_passant_par_gare_depart.headsign
  INNER JOIN tec_trips  ON tec_trips.id  = trajet_passant_par_gare_depart.id
  INNER JOIN tec_routes ON tec_routes.id = tec_trips.route_id
)
SELECT *
FROM trajet_passant_par_gare_depart_et_gare_arrivee
WHERE 1=1
ORDER BY départ;

trajet_aller.is_aller == FALSE
+----------+----------+----------+-------------------------------------------------+
| départ   | arrivée  | headsign | long_name                                       |
+----------+----------+----------+-------------------------------------------------+
| 14:58:00 | 14:40:00 | 872801   | Toulouse Matabiau - Montrejeau Gourdan Polignan |
| 15:46:00 | 15:31:00 | 872735   | Toulouse Matabiau - Pau                         |
| 16:27:00 | 16:09:00 | 872643   | Toulouse Matabiau - Montrejeau Gourdan Polignan |
| 16:58:00 | 16:40:00 | 872803   | Toulouse Matabiau - Montrejeau Gourdan Polignan |
| 17:19:00 | 17:00:00 | 872607   | Toulouse Matabiau - Montrejeau Gourdan Polignan |
| 17:27:00 | 17:09:00 | 872645   | Toulouse Matabiau - Montrejeau Gourdan Polignan |
| 17:50:00 | 17:35:00 | 872737   | Toulouse Matabiau - Pau                         |
| 17:58:00 | 17:40:00 | 872805   | Toulouse Matabiau - Montrejeau Gourdan Polignan |
| 18:19:00 | 18:00:00 | 872609   | Toulouse Matabiau - Montrejeau Gourdan Polignan |
| 18:27:00 | 18:09:00 | 872649   | Toulouse Matabiau - Montrejeau Gourdan Polignan |
| 18:46:00 | 18:31:00 | 872711   | Toulouse Matabiau - Pau                         |
| 18:58:00 | 18:40:00 | 872807   | Toulouse Matabiau - Montrejeau Gourdan Polignan |
| 19:27:00 | 19:09:00 | 872651   | Toulouse Matabiau - Montrejeau Gourdan Polignan |
| 19:46:00 | 19:31:00 | 872739   | Toulouse Matabiau - Pau                         |
| 20:46:00 | 20:31:00 | 872713   | Toulouse Matabiau - Pau                         |
| 21:59:00 | 21:42:00 | 872655   | Toulouse Matabiau - Montrejeau Gourdan Polignan |
| 22:59:00 | 22:40:00 | 872611   | Toulouse Matabiau - Montrejeau Gourdan Polignan |
+----------+----------+----------+-------------------------------------------------+
17 rows in set (1.280 sec)

trajet_aller.is_aller == TRUE
+----------+----------+----------+-------------------------------------------------+
| départ   | arrivée  | headsign | long_name                                       |
+----------+----------+----------+-------------------------------------------------+
| 15:11:00 | 15:29:00 | 872736   | Toulouse Matabiau - Pau                         |
| 16:00:00 | 16:21:00 | 872640   | Toulouse Matabiau - Montrejeau Gourdan Polignan |
| 16:13:00 | 16:30:00 | 872710   | Toulouse Matabiau - Pau                         |
| 17:11:00 | 17:29:00 | 872712   | Toulouse Matabiau - Pau                         |
| 17:41:00 | 18:00:00 | 872608   | Toulouse Matabiau - Montrejeau Gourdan Polignan |
| 18:04:00 | 18:21:00 | 872804   | Toulouse Matabiau - Montrejeau Gourdan Polignan |
| 18:41:00 | 19:00:00 | 872610   | Toulouse Matabiau - Montrejeau Gourdan Polignan |
| 19:01:00 | 19:21:00 | 872644   | Toulouse Matabiau - Montrejeau Gourdan Polignan |
| 19:12:00 | 19:29:00 | 872738   | Toulouse Matabiau - Pau                         |
| 20:00:00 | 20:20:00 | 872646   | Toulouse Matabiau - Montrejeau Gourdan Polignan |
| 21:00:00 | 21:20:00 | 872648   | Toulouse Matabiau - Montrejeau Gourdan Polignan |
| 21:11:00 | 21:29:00 | 872740   | Toulouse Matabiau - Pau                         |
| 22:00:00 | 22:20:00 | 872806   | Toulouse Matabiau - Montrejeau Gourdan Polignan |
+----------+----------+----------+-------------------------------------------------+
13 rows in set (1.271 sec)
