Add (possibly still somewhat experimental) MOTIS support

Squashed commit of the following:

commit c7c8b2ec5d8254eefb548bfe7763a7d8c9558be4
Author: Birte Kristina Friesel <derf@finalrewind.org>
Date:   Mon Jun 9 13:08:57 2025 +0200

    fix another merge issue

commit d2ae55c901ab59284263ad3070ba425e03cee833
Author: Birte Kristina Friesel <derf@finalrewind.org>
Date:   Mon Jun 9 13:08:39 2025 +0200

    Stations: get_by_external_id is a slow function

commit 725174413300e71c350d2f1dcfbeacd751def977
Author: Birte Kristina Friesel <derf@finalrewind.org>
Date:   Mon Jun 9 13:05:48 2025 +0200

    ... I accidentally commited a merge conflict

commit c695494dbd6aaf252199da42ad763bdffa1d64b9
Merge: e5da62b 3322ca2
Author: Birte Kristina Friesel <derf@finalrewind.org>
Date:   Mon Jun 9 12:46:08 2025 +0200

    Merge branch 'main' into motis

commit e5da62bcfc7953d5109ba53ae1fcc34f509f251b
Author: Birte Kristina Friesel <derf@finalrewind.org>
Date:   Wed Apr 30 18:15:39 2025 +0200

    cpanfile: add Travel::Status::MOTIS dependency

commit 180723a9e0e2f0aede0bc6352d5eee601183ccef
Merge: 479373b c90ae4c
Author: Birte Kristina Friesel <derf@finalrewind.org>
Date:   Wed Apr 30 18:13:45 2025 +0200

    Merge branch 'main' into motis

commit 479373b14eaadbc022199df246c9fb523a87188c
Author: Birte Kristina Friesel <derf@finalrewind.org>
Date:   Wed Apr 30 18:06:41 2025 +0200

    database: remove duplicate users_with_backend migration

commit 94c8b5a7d1e2cb7f73b0eca7e33d916775504cd4
Author: Birte Kristina Friesel <derf@finalrewind.org>
Date:   Wed Apr 30 18:06:04 2025 +0200

    Do not store train colours in database. They're only supported by MOTIS.

commit d58f23c3c7b06cc0243c1945dacd8673d2d2e428
Author: networkException <git@nwex.de>
Date:   Fri Apr 18 11:47:02 2025 +0200

    Initial MOTIS backend support

    This patch adds support for checkins using MOTIS backends
    using the Travel::Status::MOTIS module.

    With this travelynx supports the two services currently
    exposed by the module, RNV for local transit in Mannheim,
    Germany and surrounding cities and transitous for worldwide
    crowdsourced tranit feeds.

    This implementation supports realtime predictions,
    cancellations and polylines as well as custom route colors
    if available.

    As MOTIS doesn't expose names of indivial trips currently,
    displaying transports is mostly limited to route names.

    MOTIS uses strings for stop ids, based on the used GTFS
    source feeds. As travelynx's data model currently assumes
    interger station ids, this patch adds a mapping table
    to the database.

    This patch assumes support for MOTIS in db-fakedisplay.

    Note that while träwelling has migrated to tranitous fully
    sync remains unsupported for now.

    See https://github.com/Traewelling/traewelling/issues/3345

4 files changed, 246 insertions, 18 deletions

diff --git a/lib/Travelynx/Model/InTransit.pm b/lib/Travelynx/Model/InTransit.pm
index eeb1d87..0e3fdc6 100644
--- a/lib/Travelynx/Model/InTransit.pm
+++ b/lib/Travelynx/Model/InTransit.pm
@@ -1,6 +1,7 @@
 package Travelynx::Model::InTransit;
 
 # Copyright (C) 2020-2025 Birte Kristina Friesel
+# Copyright (C) 2025 networkException <git@nwex.de>
 #
 # SPDX-License-Identifier: AGPL-3.0-or-later
 
@@ -99,6 +100,7 @@ sub add {
 	my $train_suffix       = $opt{train_suffix};
 	my $journey            = $opt{journey};
 	my $stop               = $opt{stop};
+	my $stopover           = $opt{stopover};
 	my $checkin_station_id = $opt{departure_eva};
 	my $route              = $opt{route};
 	my $data               = $opt{data};
@@ -282,6 +284,57 @@ sub add {
 			}
 		);
 	}
+	elsif ( $journey and $stopover ) {
+
+		# MOTIS
+		my @route;
+		for my $journey_stopover ( $journey->stopovers ) {
+			push(
+				@route,
+				[
+					$journey_stopover->stop->name,
+					$journey_stopover->stop->{eva} // die('eva not set for stopover'),
+					{
+						sched_arr => _epoch( $journey_stopover->scheduled_arrival ),
+						sched_dep => _epoch( $journey_stopover->scheduled_departure ),
+						rt_arr    => _epoch( $journey_stopover->realtime_arrival ),
+						rt_dep    => _epoch( $journey_stopover->realtime_departure ),
+						arr_delay => $journey_stopover->arrival_delay,
+						dep_delay => $journey_stopover->departure_delay,
+						lat => $journey_stopover->stop->lat,
+						lon => $journey_stopover->stop->lon,
+					}
+				]
+			);
+		}
+
+		$db->insert(
+			'in_transit',
+			{
+				user_id   => $uid,
+				cancelled => $stopover->{is_cancelled}
+				? 1
+				: 0,
+				checkin_station_id => $stopover->stop->{eva},
+				checkin_time => DateTime->now( time_zone => 'Europe/Berlin' ),
+				dep_platform => $stopover->track,
+				train_type   => $journey->mode,
+				train_no     => q{},
+				train_id     => $journey->id,
+				train_line   => $journey->route_name,
+				sched_departure => $stopover->scheduled_departure,
+				real_departure  => $stopover->departure,
+				route           => $json->encode( \@route ),
+				data            => JSON->new->encode(
+					{
+						rt => $stopover->{is_realtime} ? 1 : 0,
+						%{ $data // {} }
+					}
+				),
+				backend_id => $backend_id,
+			}
+		);
+	}
 	else {
 		die('neither train nor journey specified');
 	}
@@ -331,7 +384,7 @@ sub postprocess {
 		# Note that the departure stop may be present more than once in @route,
 		# e.g. when traveling along ring lines such as S41 / S42 in Berlin.
 		if (
-			    $ret->{dep_name}
+				$ret->{dep_name}
 			and $station->[0] eq $ret->{dep_name}
 			and not($station->[2]{sched_dep}
 				and $station->[2]{sched_dep} < $ret->{sched_dep_ts} )
@@ -887,6 +940,33 @@ sub update_departure_dbris {
 	);
 }
 
+sub update_departure_motis {
+	my ( $self, %opt ) = @_;
+	my $uid      = $opt{uid};
+	my $db       = $opt{db} // $self->{pg}->db;
+	my $dep_eva  = $opt{dep_eva};
+	my $arr_eva  = $opt{arr_eva};
+	my $journey  = $opt{journey};
+	my $stopover = $opt{stopover};
+	my $json     = JSON->new;
+
+	# selecting on user_id and train_no avoids a race condition if a user checks
+	# into a new train while we are fetching data for their previous journey. In
+	# this case, the new train would receive data from the previous journey.
+	$db->update(
+		'in_transit',
+		{
+			real_departure => $stopover->{realtime_departure},
+		},
+		{
+			user_id             => $uid,
+			train_id            => $opt{train_id},
+			checkin_station_id  => $dep_eva,
+			checkout_station_id => $arr_eva,
+		}
+	);
+}
+
 sub update_departure_hafas {
 	my ( $self, %opt ) = @_;
 	my $uid     = $opt{uid};
@@ -1053,6 +1133,55 @@ sub update_arrival_dbris {
 	);
 }
 
+sub update_arrival_motis {
+	my ( $self, %opt ) = @_;
+	my $uid      = $opt{uid};
+	my $db       = $opt{db} // $self->{pg}->db;
+	my $dep_eva  = $opt{dep_eva};
+	my $arr_eva  = $opt{arr_eva};
+	my $journey  = $opt{journey};
+	my $stopover = $opt{stopover};
+	my $json     = JSON->new;
+
+	my @route;
+	for my $journey_stopover ( $journey->stopovers ) {
+		push(
+			@route,
+			[
+				$journey_stopover->stop->name,
+				$journey_stopover->stop->{eva} // die('eva not set for stopover'),
+				{
+					sched_arr => _epoch( $journey_stopover->scheduled_arrival ),
+					sched_dep => _epoch( $journey_stopover->scheduled_departure ),
+					rt_arr    => _epoch( $journey_stopover->realtime_arrival ),
+					rt_dep    => _epoch( $journey_stopover->realtime_departure ),
+					arr_delay => $journey_stopover->arrival_delay,
+					dep_delay => $journey_stopover->departure_delay,
+					lat => $journey_stopover->stop->lat,
+					lon => $journey_stopover->stop->lon,
+				}
+			]
+		);
+	}
+
+	# selecting on user_id and train_no avoids a race condition if a user checks
+	# into a new train while we are fetching data for their previous journey. In
+	# this case, the new train would receive data from the previous journey.
+	$db->update(
+		'in_transit',
+		{
+			real_arrival => $stopover->{realtime_arrival},
+			route        => $json->encode( [@route] ),
+		},
+		{
+			user_id             => $uid,
+			train_id            => $opt{train_id},
+			checkin_station_id  => $dep_eva,
+			checkout_station_id => $arr_eva,
+		}
+	);
+}
+
 sub update_arrival_hafas {
 	my ( $self, %opt ) = @_;
 	my $uid     = $opt{uid};
diff --git a/lib/Travelynx/Model/Journeys.pm b/lib/Travelynx/Model/Journeys.pm
index f5bc9f1..fff59f9 100755
--- a/lib/Travelynx/Model/Journeys.pm
+++ b/lib/Travelynx/Model/Journeys.pm
@@ -549,7 +549,7 @@ sub get {
 
 	my @select
 	  = (
-		qw(journey_id is_dbris is_iris is_hafas backend_name backend_id train_type train_line train_no checkin_ts sched_dep_ts real_dep_ts dep_eva dep_ds100 dep_name dep_lat dep_lon checkout_ts sched_arr_ts real_arr_ts arr_eva arr_ds100 arr_name arr_lat arr_lon cancelled edited route messages user_data visibility effective_visibility)
+		qw(journey_id is_dbris is_iris is_hafas is_motis backend_name backend_id train_type train_line train_no checkin_ts sched_dep_ts real_dep_ts dep_eva dep_ds100 dep_name dep_lat dep_lon checkout_ts sched_arr_ts real_arr_ts arr_eva arr_ds100 arr_name arr_lat arr_lon cancelled edited route messages user_data visibility effective_visibility)
 	  );
 	my %where = (
 		user_id   => $uid,
@@ -610,6 +610,7 @@ sub get {
 			is_dbris             => $entry->{is_dbris},
 			is_iris              => $entry->{is_iris},
 			is_hafas             => $entry->{is_hafas},
+			is_motis             => $entry->{is_motis},
 			backend_name         => $entry->{backend_name},
 			backend_id           => $entry->{backend_id},
 			type                 => $entry->{train_type},
@@ -871,8 +872,9 @@ sub get_latest_checkout_stations {
 	my $res = $db->select(
 		'journeys_str',
 		[
-			'arr_name',     'arr_eva',  'train_id', 'backend_id',
-			'backend_name', 'is_dbris', 'is_hafas'
+			'arr_name',     'arr_eva',      'arr_external_id', 'train_id',
+			'backend_id',   'backend_name', 'is_dbris',        'is_hafas',
+			'is_motis'
 		],
 		{
 			user_id   => $uid,
@@ -894,11 +896,13 @@ sub get_latest_checkout_stations {
 		push(
 			@ret,
 			{
-				name       => $row->{arr_name},
-				eva        => $row->{arr_eva},
-				dbris      => $row->{is_dbris} ? $row->{backend_name} : 0,
-				hafas      => $row->{is_hafas} ? $row->{backend_name} : 0,
-				backend_id => $row->{backend_id},
+				name               => $row->{arr_name},
+				eva                => $row->{arr_eva},
+				external_id_or_eva => $row->{arr_external_id} // $row->{arr_eva},
+				dbris              => $row->{is_dbris} ? $row->{backend_name} : 0,
+				hafas              => $row->{is_hafas} ? $row->{backend_name} : 0,
+				motis              => $row->{is_motis} ? $row->{backend_name} : 0,
+				backend_id         => $row->{backend_id},
 			}
 		);
 	}
@@ -1392,7 +1396,7 @@ sub compute_review {
 			if (
 				not $most_undelay
 				or $speedup > (
-					    $most_undelay->{sched_duration}
+						$most_undelay->{sched_duration}
 					  - $most_undelay->{rt_duration}
 				)
 			  )
@@ -1665,7 +1669,7 @@ sub compute_stats {
 					@inconsistencies,
 					{
 						conflict => {
-							train => $journey->{type} . ' '
+							train => ( $journey->{is_motis} ? '' : $journey->{type} ) . ' '
 							  . ( $journey->{line} // $journey->{no} ),
 							arr => epoch_to_dt( $journey->{rt_arr_ts} )
 							  ->strftime('%d.%m.%Y %H:%M'),
@@ -1691,7 +1695,7 @@ sub compute_stats {
 		$next_departure = $journey->{rt_dep_ts};
 		$next_id        = $journey->{id};
 		$next_train
-		  = $journey->{type} . ' ' . ( $journey->{line} // $journey->{no} ),;
+		  = ( $journey->{is_motis} ? '' : $journey->{type} ) . ' ' . ( $journey->{line} // $journey->{no} ),;
 	}
 	my $ret = {
 		km_route             => $km_route,
@@ -1740,7 +1744,7 @@ sub get_stats {
 	# checks out of a train or manually edits/adds a journey.
 
 	if (
-		    not $opt{write_only}
+			not $opt{write_only}
 		and not $opt{review}
 		and my $stats = $self->stats_cache->get(
 			uid   => $uid,
diff --git a/lib/Travelynx/Model/Stations.pm b/lib/Travelynx/Model/Stations.pm
index 3d6549f..761c5de 100644
--- a/lib/Travelynx/Model/Stations.pm
+++ b/lib/Travelynx/Model/Stations.pm
@@ -1,6 +1,7 @@
 package Travelynx::Model::Stations;
 
 # Copyright (C) 2022 Birte Kristina Friesel
+# Copyright (C) 2025 networkException <git@nwex.de>
 #
 # SPDX-License-Identifier: AGPL-3.0-or-later
 
@@ -28,6 +29,9 @@ sub get_backend_id {
 	if ( $opt{dbris} and $self->{backend_id}{dbris}{ $opt{dbris} } ) {
 		return $self->{backend_id}{dbris}{ $opt{dbris} };
 	}
+	if ( $opt{motis} and $self->{backend_id}{motis}{ $opt{motis} } ) {
+		return $self->{backend_id}{motis}{ $opt{motis} };
+	}
 
 	my $db         = $opt{db} // $self->{pg}->db;
 	my $backend_id = 0;
@@ -54,6 +58,17 @@ sub get_backend_id {
 		)->hash->{id};
 		$self->{backend_id}{hafas}{ $opt{hafas} } = $backend_id;
 	}
+	elsif ( $opt{motis} ) {
+		$backend_id = $db->select(
+			'backends',
+			['id'],
+			{
+				motis => 1,
+				name  => $opt{motis}
+			}
+		)->hash->{id};
+		$self->{backend_id}{motis}{ $opt{motis} } = $backend_id;
+	}
 
 	return $backend_id;
 }
@@ -85,7 +100,7 @@ sub get_backends {
 	$opt{db} //= $self->{pg}->db;
 
 	my $res = $opt{db}
-	  ->select( 'backends', [ 'id', 'name', 'iris', 'hafas', 'dbris' ] );
+	  ->select( 'backends', [ 'id', 'name', 'iris', 'hafas', 'dbris', 'motis' ] );
 	my @ret;
 
 	while ( my $row = $res->hash ) {
@@ -97,6 +112,7 @@ sub get_backends {
 				iris  => $row->{iris},
 				dbris => $row->{dbris},
 				hafas => $row->{hafas},
+				motis => $row->{motis},
 			}
 		);
 	}
@@ -149,6 +165,61 @@ sub add_or_update {
 		return;
 	}
 
+	if ( $opt{motis} ) {
+		if (
+			my $s = $self->get_by_external_id(
+				external_id => $stop->id,
+				db          => $opt{db},
+				backend_id  => $opt{backend_id}
+			)
+		  )
+		{
+			$opt{db}->update(
+				'stations',
+				{
+					name     => $stop->name,
+					lat      => $stop->lat,
+					lon      => $stop->lon,
+					archived => 0
+				},
+				{
+					eva    => $s->{eva},
+					source => $opt{backend_id}
+				}
+			);
+
+			$stop->{eva} = $s->{eva};
+
+			return;
+		}
+
+		my $s = $opt{db}->query(
+			qq {
+				with new_station as (
+					insert into stations_external_ids (backend_id, external_id)
+					values (?, ?)
+					returning eva, backend_id
+				)
+
+				insert into stations (eva, name, lat, lon, source, archived)
+				values ((select eva from new_station), ?, ?, ?, (select backend_id from new_station), ?)
+				returning *
+			},
+			(
+				$opt{backend_id},
+				$stop->id,
+				$stop->name,
+				$stop->lat,
+				$stop->lon,
+				0,
+			)
+		);
+
+		$stop->{eva} = $s->hash->{eva};
+
+		return;
+	}
+
 	my $loc = $stop->loc;
 	if (
 		my $s = $self->get_by_eva(
@@ -184,6 +255,8 @@ sub add_or_update {
 			archived => 0
 		}
 	);
+
+	return;
 }
 
 sub add_meta {
@@ -276,6 +349,27 @@ sub get_by_eva {
 	)->hash;
 }
 
+# Slow
+sub get_by_external_id {
+	my ( $self, %opt ) = @_;
+
+	if ( not $opt{external_id} ) {
+		return;
+	}
+
+	$opt{db}         //= $self->{pg}->db;
+	$opt{backend_id} //= $self->get_backend_id(%opt);
+
+	return $opt{db}->select(
+		'stations_with_external_ids',
+		'*',
+		{
+			external_id => $opt{external_id},
+			source      => $opt{backend_id},
+		}
+	)->hash;
+}
+
 # Fast
 sub get_by_evas {
 	my ( $self, %opt ) = @_;
diff --git a/lib/Travelynx/Model/Users.pm b/lib/Travelynx/Model/Users.pm
index 750e889..10ab17e 100644
--- a/lib/Travelynx/Model/Users.pm
+++ b/lib/Travelynx/Model/Users.pm
@@ -418,7 +418,7 @@ sub get {
 		  . 'extract(epoch from registered_at) as registered_at_ts, '
 		  . 'extract(epoch from last_seen) as last_seen_ts, '
 		  . 'extract(epoch from deletion_requested) as deletion_requested_ts, '
-		  . 'backend_id, backend_name, hafas, dbris',
+		  . 'backend_id, backend_name, hafas, dbris, motis',
 		{ id => $uid }
 	)->hash;
 	if ($user) {
@@ -459,6 +459,7 @@ sub get {
 			backend_name  => $user->{backend_name},
 			backend_dbris => $user->{dbris},
 			backend_hafas => $user->{hafas},
+			backend_motis => $user->{motis},
 		};
 	}
 	return undef;
@@ -1026,11 +1027,11 @@ sub get_followers {
 				id             => $row->{id},
 				name           => $row->{name},
 				following_back => (
-					      $row->{inverse_predicate}
+						  $row->{inverse_predicate}
 					  and $row->{inverse_predicate} == $predicate_atoi{follows}
 				) ? 1 : 0,
 				followback_requested => (
-					      $row->{inverse_predicate}
+						  $row->{inverse_predicate}
 					  and $row->{inverse_predicate}
 					  == $predicate_atoi{requests_follow}
 				) ? 1 : 0,
@@ -1102,7 +1103,7 @@ sub get_followees {
 				id             => $row->{id},
 				name           => $row->{name},
 				following_back => (
-					      $row->{inverse_predicate}
+						  $row->{inverse_predicate}
 					  and $row->{inverse_predicate} == $predicate_atoi{follows}
 				) ? 1 : 0,
 			}