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

2 files changed, 307 insertions, 2 deletions

diff --git a/lib/Travelynx/Command/database.pm b/lib/Travelynx/Command/database.pm
index d0bc163..0e87b2a 100644
--- a/lib/Travelynx/Command/database.pm
+++ b/lib/Travelynx/Command/database.pm
@@ -1,6 +1,7 @@
 package Travelynx::Command::database;
 
 # Copyright (C) 2020-2023 Birte Kristina Friesel
+# Copyright (C) 2025 networkException <git@nwex.de>
 #
 # SPDX-License-Identifier: AGPL-3.0-or-later
 use Mojo::Base 'Mojolicious::Command';
@@ -11,6 +12,7 @@ use List::Util  qw();
 use JSON;
 use Travel::Status::DE::HAFAS;
 use Travel::Status::DE::IRIS::Stations;
+use Travel::Status::MOTIS;
 
 has description => 'Initialize or upgrade database layout';
 
@@ -2854,6 +2856,173 @@ qq{select distinct checkout_station_id from in_transit where backend_id = 0;}
 			}
 		);
 	},
+
+	# v61 -> v62
+	# Add MOTIS backend type, add RNV and transitous MOTIS backends
+	sub {
+		my ($db) = @_;
+		$db->query(
+			qq{
+				alter table backends add column motis bool default false;
+				alter table schema_version add column motis varchar(12);
+
+				create table stations_external_ids (
+					eva serial not null primary key,
+					backend_id smallint not null,
+					external_id text not null,
+
+					unique (backend_id, external_id),
+					foreign key (eva, backend_id) references stations (eva, source)
+				);
+
+				create view stations_with_external_ids as select
+					stations.*, stations_external_ids.external_id
+					from stations
+					left join stations_external_ids on
+						stations.eva = stations_external_ids.eva and
+						stations.source = stations_external_ids.backend_id
+					;
+
+				drop view in_transit_str;
+				drop view journeys_str;
+				drop view users_with_backend;
+				drop view follows_in_transit;
+
+				create view in_transit_str as select
+					user_id,
+					backend.iris as is_iris, backend.hafas as is_hafas,
+					backend.efa as is_efa, backend.dbris as is_dbris,
+					backend.motis as is_motis,
+					backend.name as backend_name, in_transit.backend_id as backend_id,
+					train_type, train_line, train_no, train_id,
+					extract(epoch from checkin_time) as checkin_ts,
+					extract(epoch from sched_departure) as sched_dep_ts,
+					extract(epoch from real_departure) as real_dep_ts,
+					checkin_station_id as dep_eva,
+					dep_station.ds100 as dep_ds100,
+					dep_station.name as dep_name,
+					dep_station.lat as dep_lat,
+					dep_station.lon as dep_lon,
+					dep_station_external_id.external_id as dep_external_id,
+					extract(epoch from checkout_time) as checkout_ts,
+					extract(epoch from sched_arrival) as sched_arr_ts,
+					extract(epoch from real_arrival) as real_arr_ts,
+					checkout_station_id as arr_eva,
+					arr_station.ds100 as arr_ds100,
+					arr_station.name as arr_name,
+					arr_station.lat as arr_lat,
+					arr_station.lon as arr_lon,
+					arr_station_external_id.external_id as arr_external_id,
+					polyline_id,
+					polylines.polyline as polyline,
+					visibility,
+					coalesce(visibility, users.public_level & 127) as effective_visibility,
+					cancelled, route, messages, user_data,
+					dep_platform, arr_platform, data
+					from in_transit
+					left join polylines on polylines.id = polyline_id
+					left join users on users.id = user_id
+					left join stations as dep_station on checkin_station_id = dep_station.eva and in_transit.backend_id = dep_station.source
+					left join stations as arr_station on checkout_station_id = arr_station.eva and in_transit.backend_id = arr_station.source
+					left join stations_external_ids as dep_station_external_id on checkin_station_id = dep_station_external_id.eva and in_transit.backend_id = dep_station_external_id.backend_id
+					left join stations_external_ids as arr_station_external_id on checkout_station_id = arr_station_external_id.eva and in_transit.backend_id = arr_station_external_id.backend_id
+					left join backends as backend on in_transit.backend_id = backend.id
+					;
+				create view journeys_str as select
+					journeys.id as journey_id, user_id,
+					backend.iris as is_iris, backend.hafas as is_hafas,
+					backend.efa as is_efa, backend.dbris as is_dbris,
+					backend.motis as is_motis,
+					backend.name as backend_name, journeys.backend_id as backend_id,
+					train_type, train_line, train_no, train_id,
+					extract(epoch from checkin_time) as checkin_ts,
+					extract(epoch from sched_departure) as sched_dep_ts,
+					extract(epoch from real_departure) as real_dep_ts,
+					checkin_station_id as dep_eva,
+					dep_station.ds100 as dep_ds100,
+					dep_station.name as dep_name,
+					dep_station.lat as dep_lat,
+					dep_station.lon as dep_lon,
+					dep_station_external_id.external_id as dep_external_id,
+					extract(epoch from checkout_time) as checkout_ts,
+					extract(epoch from sched_arrival) as sched_arr_ts,
+					extract(epoch from real_arrival) as real_arr_ts,
+					checkout_station_id as arr_eva,
+					arr_station.ds100 as arr_ds100,
+					arr_station.name as arr_name,
+					arr_station.lat as arr_lat,
+					arr_station.lon as arr_lon,
+					arr_station_external_id.external_id as arr_external_id,
+					polylines.polyline as polyline,
+					visibility,
+					coalesce(visibility, users.public_level & 127) as effective_visibility,
+					cancelled, edited, route, messages, user_data,
+					dep_platform, arr_platform
+					from journeys
+					left join polylines on polylines.id = polyline_id
+					left join users on users.id = user_id
+					left join stations as dep_station on checkin_station_id = dep_station.eva and journeys.backend_id = dep_station.source
+					left join stations as arr_station on checkout_station_id = arr_station.eva and journeys.backend_id = arr_station.source
+					left join stations_external_ids as dep_station_external_id on checkin_station_id = dep_station_external_id.eva and journeys.backend_id = dep_station_external_id.backend_id
+					left join stations_external_ids as arr_station_external_id on checkout_station_id = arr_station_external_id.eva and journeys.backend_id = arr_station_external_id.backend_id
+					left join backends as backend on journeys.backend_id = backend.id
+					;
+				create view users_with_backend as select
+					users.id as id, users.name as name, status, public_level,
+					email, password, registered_at, last_seen,
+					deletion_requested, deletion_notified, use_history,
+					accept_follows, notifications, profile, backend_id, iris,
+					hafas, efa, dbris, motis, backend.name as backend_name
+					from users
+					left join backends as backend on users.backend_id = backend.id
+					;
+				create view follows_in_transit as select
+					r1.subject_id as follower_id, user_id as followee_id,
+					users.name as followee_name,
+					train_type, train_line, train_no, train_id,
+					backend.iris as is_iris, backend.hafas as is_hafas,
+					backend.efa as is_efa, backend.dbris as is_dbris,
+					backend.motis as is_motis,
+					backend.name as backend_name, in_transit.backend_id as backend_id,
+					extract(epoch from checkin_time) as checkin_ts,
+					extract(epoch from sched_departure) as sched_dep_ts,
+					extract(epoch from real_departure) as real_dep_ts,
+					checkin_station_id as dep_eva,
+					dep_station.ds100 as dep_ds100,
+					dep_station.name as dep_name,
+					dep_station.lat as dep_lat,
+					dep_station.lon as dep_lon,
+					extract(epoch from checkout_time) as checkout_ts,
+					extract(epoch from sched_arrival) as sched_arr_ts,
+					extract(epoch from real_arrival) as real_arr_ts,
+					checkout_station_id as arr_eva,
+					arr_station.ds100 as arr_ds100,
+					arr_station.name as arr_name,
+					arr_station.lat as arr_lat,
+					arr_station.lon as arr_lon,
+					polyline_id,
+					polylines.polyline as polyline,
+					visibility,
+					coalesce(visibility, users.public_level & 127) as effective_visibility,
+					cancelled, route, messages, user_data,
+					dep_platform, arr_platform, data
+					from in_transit
+					left join polylines on polylines.id = polyline_id
+					left join users on users.id = user_id
+					left join relations as r1 on r1.predicate = 1 and r1.object_id = user_id
+					left join stations as dep_station on checkin_station_id = dep_station.eva and in_transit.backend_id = dep_station.source
+					left join stations as arr_station on checkout_station_id = arr_station.eva and in_transit.backend_id = arr_station.source
+					left join backends as backend on in_transit.backend_id = backend.id
+					order by checkin_time desc
+					;
+			}
+		);
+		$db->query(
+			qq{
+				update schema_version set version = 62;
+			}
+		);
+	},
 );
 
 sub sync_stations {
@@ -3044,7 +3213,7 @@ sub sync_stations {
 	}
 }
 
-sub sync_backends {
+sub sync_backends_hafas {
 	my ($db) = @_;
 	for my $service ( Travel::Status::DE::HAFAS::get_services() ) {
 		my $present = $db->select(
@@ -3074,6 +3243,36 @@ sub sync_backends {
 		{ hafas => $Travel::Status::DE::HAFAS::VERSION } );
 }
 
+sub sync_backends_motis {
+	my ($db) = @_;
+	for my $service ( Travel::Status::MOTIS::get_services() ) {
+		my $present = $db->select(
+			'backends',
+			'count(*) as count',
+			{
+				motis => 1,
+				name  => $service->{shortname}
+			}
+		)->hash->{count};
+		if ( not $present ) {
+			$db->insert(
+				'backends',
+				{
+					iris  => 0,
+					hafas => 0,
+					efa   => 0,
+					dbris => 0,
+					motis => 1,
+					name  => $service->{shortname},
+				},
+				{ on_conflict => undef }
+			);
+		}
+	}
+
+	$db->update( 'schema_version', { motis => $Travel::Status::MOTIS::VERSION } );
+}
+
 sub setup_db {
 	my ($db) = @_;
 	my $tx = $db->begin;
@@ -3169,7 +3368,18 @@ sub migrate_db {
 	else {
 		say
 "Synchronizing with Travel::Status::DE::HAFAS $Travel::Status::DE::HAFAS::VERSION";
-		sync_backends($db);
+		sync_backends_hafas($db);
+	}
+
+	my $motis_version = get_schema_version( $db, 'motis' ) // '0';
+	say "Found backend table for Motis v${motis_version}";
+	if ( $motis_version eq $Travel::Status::MOTIS::VERSION ) {
+		say 'Backend table is up-to-date';
+	}
+	else {
+		say
+"Synchronizing with Travel::Status::MOTIS $Travel::Status::MOTIS::VERSION";
+		sync_backends_motis($db);
 	}
 
 	$db->update( 'schema_version',
diff --git a/lib/Travelynx/Command/work.pm b/lib/Travelynx/Command/work.pm
index 4876e98..4ff5c9e 100644
--- a/lib/Travelynx/Command/work.pm
+++ b/lib/Travelynx/Command/work.pm
@@ -1,6 +1,7 @@
 package Travelynx::Command::work;
 
 # Copyright (C) 2020-2023 Birte Kristina Friesel
+# Copyright (C) 2025 networkException <git@nwex.de>
 #
 # SPDX-License-Identifier: AGPL-3.0-or-later
 use Mojo::Base 'Mojolicious::Command';
@@ -184,6 +185,100 @@ sub run {
 			next;
 		}
 
+		if ( $entry->{is_motis} ) {
+
+			eval {
+				$self->app->motis->trip_id(
+					service => $entry->{backend_name},
+					trip_id => $train_id,
+				)->then(
+					sub {
+						my ($journey) = @_;
+
+						for my $stopover ( $journey->stopovers ) {
+							if ( not defined $stopover->stop->{eva} ) {
+								my $stop = $self->app->stations->get_by_external_id(
+									external_id => $stopover->stop->id,
+									motis       => $entry->{backend_name},
+								);
+
+								$stopover->stop->{eva} = $stop->{eva};
+							}
+						}
+
+						my $found_departure;
+						my $found_arrival;
+						for my $stopover ( $journey->stopovers ) {
+							if ( $stopover->stop->{eva} == $dep ) {
+								$found_departure = $stopover;
+							}
+
+							if ( $arr and $stopover->stop->{eva} == $arr ) {
+								$found_arrival = $stopover;
+								last;
+							}
+						}
+
+						if ( not $found_departure ) {
+							$self->app->log->debug("Did not find $dep within trip $train_id");
+							return;
+						}
+
+						if ( $found_departure->realtime_departure ) {
+							$self->app->in_transit->update_departure_motis(
+								uid      => $uid,
+								journey  => $journey,
+								stopover => $found_departure,
+								dep_eva  => $dep,
+								arr_eva  => $arr,
+								train_id => $train_id,
+							);
+						}
+
+						if ( $found_arrival and $found_arrival->realtime_arrival ) {
+							$self->app->in_transit->update_arrival_motis(
+								uid      => $uid,
+								journey  => $journey,
+								train_id => $train_id,
+								stopover => $found_arrival,
+								dep_eva  => $dep,
+								arr_eva  => $arr
+							);
+						}
+					}
+				)->catch(
+					sub {
+						my ($err) = @_;
+						$self->app->log->error(
+"work($uid) @ MOTIS $entry->{backend_name}: journey: $err"
+						);
+						if ( $err =~ m{HTTP 429} ) {
+							$dbris_rate_limited = 1;
+						}
+					}
+				)->wait;
+
+				if (    $arr
+					and $entry->{real_arr_ts}
+					and $now->epoch - $entry->{real_arr_ts} > 600 )
+				{
+					$self->app->checkout_p(
+						station => $arr,
+						force   => 2,
+						dep_eva => $dep,
+						arr_eva => $arr,
+						uid     => $uid
+					)->wait;
+				}
+			};
+			if ($@) {
+				$errors += 1;
+				$self->app->log->error(
+					"work($uid) @ MOTIS $entry->{backend_name}: $@");
+			}
+			next;
+		}
+
 		if ( $entry->{is_hafas} ) {
 
 			eval {