Store journey polylines in DB

Squashed commit of the following:

commit d60c7d3c98d88a8f5b0e3ced6c11b56053e1e44b
Author: Daniel Friesel <derf@finalrewind.org>
Date:   Mon Jan 27 20:22:46 2020 +0100

    fix bugs related to users without past journeys

commit 707fcc937ac7f6bc3dc29024273f5e74963f7f15
Author: Daniel Friesel <derf@finalrewind.org>
Date:   Mon Jan 27 20:19:14 2020 +0100

    work around Cache::file turning floats into strings

commit 55831121eb30bc30ed20134bbb48e4bee9772feb
Author: Daniel Friesel <derf@finalrewind.org>
Date:   Mon Jan 27 19:43:29 2020 +0100

    store journey polylines for later use

commit 1971d511037ff2b8fbc9699cb98e4f8fd51261e5
Author: Daniel Friesel <derf@finalrewind.org>
Date:   Sat Jan 25 16:49:48 2020 +0100

    set preliminary database schema for polyline storage.

    deduplication will follow at a later stage

2 files changed, 176 insertions, 0 deletions

diff --git a/lib/Travelynx.pm b/lib/Travelynx.pm
index b7d9368..a8078e8 100755
--- a/lib/Travelynx.pm
+++ b/lib/Travelynx.pm
@@ -1945,6 +1945,66 @@ sub startup {
 	);
 
 	$self->helper(
+		'get_hafas_polyline_p' => sub {
+			my ( $self, $train, $trip_id ) = @_;
+
+			my $line = $train->line // 0;
+			my $url
+			  = "https://2.db.transport.rest/trips/${trip_id}?lineName=${line}&polyline=true";
+			my $cache   = $self->app->cache_iris_main;
+			my $promise = Mojo::Promise->new;
+			my $version = $self->app->config->{version};
+
+			if ( my $content = $cache->thaw($url) ) {
+				$promise->resolve($content);
+				return $promise;
+			}
+
+			$self->ua->request_timeout(5)->get_p(
+				$url => {
+					'User-Agent' =>
+"travelynx/${version} +https://finalrewind.org/projects/travelynx"
+				}
+			)->then(
+				sub {
+					my ($tx) = @_;
+					my $body = decode( 'utf-8', $tx->res->body );
+					my $json = JSON->new->decode($body);
+					my @coordinate_list;
+
+					for my $feature ( @{ $json->{polyline}{features} } ) {
+						if ( exists $feature->{geometry}{coordinates} ) {
+							my $coord = $feature->{geometry}{coordinates};
+							if ( exists $feature->{properties}{type}
+								and $feature->{properties}{type} eq 'stop' )
+							{
+								push( @{$coord}, $feature->{properties}{id} );
+							}
+							push( @coordinate_list, $coord );
+						}
+					}
+
+					my $ret = {
+						name     => $json->{line}{name} // '?',
+						polyline => [@coordinate_list],
+						raw      => $json,
+					};
+
+					$cache->freeze( $url, $ret );
+					$promise->resolve($ret);
+				}
+			)->catch(
+				sub {
+					my ($err) = @_;
+					$promise->reject($err);
+				}
+			)->wait;
+
+			return $promise;
+		}
+	);
+
+	$self->helper(
 		'get_hafas_tripid_p' => sub {
 			my ( $self, $train ) = @_;
 
@@ -2133,6 +2193,7 @@ sub startup {
 			}
 
 			if ( not $journey->{data}{trip_id} ) {
+				my ( $origin_eva, $destination_eva, $polyline_str );
 				$self->get_hafas_tripid_p($train)->then(
 					sub {
 						my ($trip_id) = @_;
@@ -2149,6 +2210,65 @@ sub startup {
 							{ data    => JSON->new->encode($data) },
 							{ user_id => $uid }
 						);
+						return $self->get_hafas_polyline_p( $train, $trip_id );
+					}
+				)->then(
+					sub {
+						my ($ret) = @_;
+						my $polyline = $ret->{polyline};
+						$origin_eva      = 0 + $ret->{raw}{origin}{id};
+						$destination_eva = 0 + $ret->{raw}{destination}{id};
+
+						# work around Cache::File turning floats into strings
+						for my $coord ( @{$polyline} ) {
+							@{$coord} = map { 0 + $_ } @{$coord};
+						}
+
+						$polyline_str = JSON->new->encode($polyline);
+
+						return $db->select_p(
+							'polylines',
+							['id'],
+							{
+								origin_eva      => $origin_eva,
+								destination_eva => $destination_eva,
+								polyline        => $polyline_str
+							},
+							{ limit => 1 }
+						);
+					}
+				)->then(
+					sub {
+						my ($pl_res) = @_;
+						my $polyline_id;
+						if ( my $h = $pl_res->hash ) {
+							$polyline_id = $h->{id};
+						}
+						else {
+							eval {
+								$polyline_id = $db->insert(
+									'polylines',
+									{
+										origin_eva      => $origin_eva,
+										destination_eva => $destination_eva,
+										polyline        => $polyline_str
+									},
+									{ returning => 'id' }
+								)->hash->{id};
+							};
+							if ($@) {
+								$self->app->log->warn(
+									"add_route_timestamps: insert polyline: $@"
+								);
+							}
+						}
+						if ($polyline_id) {
+							$db->update(
+								'in_transit',
+								{ polyline_id => $polyline_id },
+								{ user_id     => $uid }
+							);
+						}
 					}
 				)->wait;
 			}
diff --git a/lib/Travelynx/Command/database.pm b/lib/Travelynx/Command/database.pm
index cb5ffec..22d8a93 100644
--- a/lib/Travelynx/Command/database.pm
+++ b/lib/Travelynx/Command/database.pm
@@ -941,6 +941,62 @@ my @migrations = (
 			}
 		);
 	},
+
+	# v19 -> v20
+	sub {
+		my ($db) = @_;
+		$db->query(
+			qq{
+				create table polylines (
+					id serial not null primary key,
+					origin_eva integer not null,
+					destination_eva integer not null,
+					polyline jsonb not null
+				);
+				alter table journeys
+					add column polyline_id integer references polylines (id);
+				alter table in_transit
+					add column polyline_id integer references polylines (id);
+				drop view journeys_str;
+				drop view in_transit_str;
+				create view journeys_str as select
+					journeys.id as journey_id, user_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,
+					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,
+					polylines.polyline as polyline,
+					cancelled, edited, route, messages, user_data,
+					dep_platform, arr_platform
+					from journeys
+					left join polylines on polylines.id = polyline_id
+					;
+				create or replace view in_transit_str as select
+					user_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,
+					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,
+					polylines.polyline as polyline,
+					cancelled, route, messages, user_data,
+					dep_platform, arr_platform, data
+					from in_transit
+					left join polylines on polylines.id = polyline_id
+					;
+				update schema_version set version = 20;
+			}
+		);
+	},
 );
 
 sub setup_db {