5 files changed, 278 insertions, 97 deletions

diff --git a/lib/Travelynx/Command/database.pm b/lib/Travelynx/Command/database.pm
index b270262..b5e8cf5 100644
--- a/lib/Travelynx/Command/database.pm
+++ b/lib/Travelynx/Command/database.pm
@@ -12,9 +12,11 @@ sub get_schema_version {
 	my $version;
 
 	eval {
-		$version = $db->select( 'schema_version', ['version'] )->hash->{version};
+		$version
+		  = $db->select( 'schema_version', ['version'] )->hash->{version};
 	};
 	if ($@) {
+
 		# If it failed, the version table does not exist -> run setup first.
 		return undef;
 	}
@@ -124,6 +126,210 @@ my @migrations = (
 			}
 		);
 	},
+
+	# v3 -> v4
+	# Introduces "journeys", containing one row for each complete
+	# journey, and "in_transit", containing the journey which is currently
+	# in progress (if any). "user_actions" is no longer used, but still kept
+	# as a backup for now.
+	sub {
+		my ($db) = @_;
+
+		$db->query(
+			qq{
+				create table journeys (
+					id serial not null primary key,
+					user_id integer not null references users (id),
+					train_type varchar(16) not null,
+					train_line varchar(16),
+					train_no varchar(16) not null,
+					train_id varchar(128) not null,
+					checkin_station_id integer not null references stations (id),
+					checkin_time timestamptz not null,
+					sched_departure timestamptz not null,
+					real_departure timestamptz not null,
+					checkout_station_id integer not null references stations (id),
+					checkout_time timestamptz not null,
+					sched_arrival timestamptz,
+					real_arrival timestamptz,
+					cancelled boolean not null,
+					edited smallint not null,
+					route text,
+					messages text
+				);
+				create table in_transit (
+					user_id integer not null references users (id) primary key,
+					train_type varchar(16) not null,
+					train_line varchar(16),
+					train_no varchar(16) not null,
+					train_id varchar(128) not null,
+					checkin_station_id integer not null references stations (id),
+					checkin_time timestamptz not null,
+					sched_departure timestamptz not null,
+					real_departure timestamptz not null,
+					checkout_station_id int references stations (id),
+					checkout_time timestamptz,
+					sched_arrival timestamptz,
+					real_arrival timestamptz,
+					cancelled boolean not null,
+					route text,
+					messages text
+				);
+				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,
+					dep_stations.ds100 as dep_ds100,
+					dep_stations.name as dep_name,
+					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,
+					arr_stations.ds100 as arr_ds100,
+					arr_stations.name as arr_name,
+					cancelled, edited, route, messages
+					from journeys
+					join stations as dep_stations on dep_stations.id = checkin_station_id
+					join stations as arr_stations on arr_stations.id = checkout_station_id
+					;
+				create 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,
+					dep_stations.ds100 as dep_ds100,
+					dep_stations.name as dep_name,
+					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,
+					arr_stations.ds100 as arr_ds100,
+					arr_stations.name as arr_name,
+					cancelled, route, messages
+					from in_transit
+					join stations as dep_stations on dep_stations.id = checkin_station_id
+					left join stations as arr_stations on arr_stations.id = checkout_station_id
+					;
+			}
+		);
+
+		my @uids
+		  = $db->select( 'users', ['id'] )->hashes->map( sub { shift->{id} } )
+		  ->each;
+		my $count = 0;
+
+		for my $uid (@uids) {
+			my %cache;
+			my $prev_action_type = 0;
+			my $actions          = $db->select(
+				'user_actions', '*',
+				{ user_id  => $uid },
+				{ order_by => { -asc => 'id' } }
+			);
+			for my $action ( $actions->hashes->each ) {
+				my $action_type = $action->{action_id};
+				my $id          = $action->{id};
+
+				if ( $action_type == 2 and $prev_action_type != 1 ) {
+					die(
+"Inconsistent data at uid ${uid} action ${id}: Illegal transition $prev_action_type -> $action_type.\n"
+					);
+				}
+
+				if ( $action_type == 5 and $prev_action_type != 4 ) {
+					die(
+"Inconsistent data at uid ${uid} action ${id}: Illegal transition $prev_action_type -> $action_type.\n"
+					);
+				}
+
+				if ( $action_type == 1 or $action_type == 4 ) {
+					%cache = (
+						train_type         => $action->{train_type},
+						train_line         => $action->{train_line},
+						train_no           => $action->{train_no},
+						train_id           => $action->{train_id},
+						checkin_station_id => $action->{station_id},
+						checkin_time       => $action->{action_time},
+						sched_departure    => $action->{sched_time},
+						real_departure     => $action->{real_time},
+						route              => $action->{route},
+						messages           => $action->{messages},
+						cancelled          => $action->{action_id} == 4 ? 1 : 0,
+						edited             => $action->{edited},
+					);
+				}
+				elsif ( $action_type == 2 or $action_type == 5 ) {
+					$cache{checkout_station_id} = $action->{station_id};
+					$cache{checkout_time}       = $action->{action_time};
+					$cache{sched_arrival}       = $action->{sched_time};
+					$cache{real_arrival}        = $action->{real_time};
+					$cache{edited} |= $action->{edited} << 8;
+					if ( $action->{route} ) {
+						$cache{route} = $action->{route};
+					}
+					if ( $action->{messages} ) {
+						$cache{messages} = $action->{messages};
+					}
+
+					$db->insert(
+						'journeys',
+						{
+							user_id             => $uid,
+							train_type          => $cache{train_type},
+							train_line          => $cache{train_line},
+							train_no            => $cache{train_no},
+							train_id            => $cache{train_id},
+							checkin_station_id  => $cache{checkin_station_id},
+							checkin_time        => $cache{checkin_time},
+							sched_departure     => $cache{sched_departure},
+							real_departure      => $cache{real_departure},
+							checkout_station_id => $cache{checkout_station_id},
+							checkout_time       => $cache{checkout_time},
+							sched_arrival       => $cache{sched_arrival},
+							real_arrival        => $cache{real_arrival},
+							cancelled           => $cache{cancelled},
+							edited              => $cache{edited},
+							route               => $cache{route},
+							messages            => $cache{messages}
+						}
+					);
+
+					%cache = ();
+
+				}
+
+				$prev_action_type = $action_type;
+			}
+
+			if (%cache) {
+
+				# user is currently in transit
+				$db->insert(
+					'in_transit',
+					{
+						user_id            => $uid,
+						train_type         => $cache{train_type},
+						train_line         => $cache{train_line},
+						train_no           => $cache{train_no},
+						train_id           => $cache{train_id},
+						checkin_station_id => $cache{checkin_station_id},
+						checkin_time       => $cache{checkin_time},
+						sched_departure    => $cache{sched_departure},
+						real_departure     => $cache{real_departure},
+						cancelled          => $cache{cancelled},
+						route              => $cache{route},
+						messages           => $cache{messages}
+					}
+				);
+			}
+
+			$count++;
+			printf( "    journey storage migration: %3.0f%% complete\n",
+				$count * 100 / @uids );
+		}
+		$db->update( 'schema_version', { version => 4 } );
+	},
 );
 
 sub setup_db {
diff --git a/lib/Travelynx/Controller/Account.pm b/lib/Travelynx/Controller/Account.pm
index 0037e16..e60f1d3 100644
--- a/lib/Travelynx/Controller/Account.pm
+++ b/lib/Travelynx/Controller/Account.pm
@@ -286,45 +286,18 @@ sub account {
 
 sub json_export {
 	my ($self) = @_;
-	my $uid    = $self->current_user->{id};
-	my $query  = $self->app->get_all_actions_query;
-
-	$query->execute($uid);
-
-	my @entries;
-
-	while ( my @row = $query->fetchrow_array ) {
-		my (
-			$action_id, $action,       $raw_ts,      $ds100,
-			$name,      $train_type,   $train_line,  $train_no,
-			$train_id,  $raw_sched_ts, $raw_real_ts, $raw_route,
-			$raw_messages
-		) = @row;
-
-		push(
-			@entries,
-			{
-				action        => $self->app->action_types->[ $action - 1 ],
-				action_ts     => $raw_ts,
-				station_ds100 => $ds100,
-				station_name  => $name,
-				train_type    => $train_type,
-				train_line    => $train_line,
-				train_no      => $train_no,
-				train_id      => $train_id,
-				scheduled_ts  => $raw_sched_ts,
-				realtime_ts   => $raw_real_ts,
-				messages      => $raw_messages
-				? [ map { [ split(qr{:}) ] } split( qr{[|]}, $raw_messages ) ]
-				: undef,
-				route => $raw_route ? [ split( qr{[|]}, $raw_route ) ]
-				: undef,
-			}
-		);
-	}
+	my $uid = $self->current_user->{id};
+
+	my $db = $self->pg->db;
 
 	$self->render(
-		json => [@entries],
+		json => {
+			account  => $db->select( 'users', '*', { id => $uid } )->hash,
+			journeys => [
+				$db->select( 'journeys', '*', { user_id => $uid } )
+				  ->hashes->each
+			],
+		}
 	);
 }
 
diff --git a/lib/Travelynx/Controller/Api.pm b/lib/Travelynx/Controller/Api.pm
index a9500f1..8e72374 100755
--- a/lib/Travelynx/Controller/Api.pm
+++ b/lib/Travelynx/Controller/Api.pm
@@ -48,10 +48,10 @@ sub get_v0 {
 		my $station_lon = undef;
 		my $station_lat = undef;
 
-		if ( $status->{station_ds100} ) {
+		if ( $status->{arr_ds100} // $status->{dep_ds100} ) {
 			@station_descriptions
 			  = Travel::Status::DE::IRIS::Stations::get_station(
-				$status->{station_ds100} );
+				$status->{arr_ds100} // $status->{dep_ds100} );
 		}
 		if ( @station_descriptions == 1 ) {
 			( undef, undef, $station_eva, $station_lon, $station_lat )
@@ -59,14 +59,14 @@ sub get_v0 {
 		}
 		$self->render(
 			json => {
-				deprecated => \0,
+				deprecated => \1,
 				checked_in => (
 					     $status->{checked_in}
 					  or $status->{cancelled}
 				) ? \1 : \0,
 				station => {
-					ds100     => $status->{station_ds100},
-					name      => $status->{station_name},
+					ds100     => $status->{arr_ds100} // $status->{dep_ds100},
+					name      => $status->{arr_ds100} // $status->{dep_ds100},
 					uic       => $station_eva,
 					longitude => $station_lon,
 					latitude  => $station_lat,
@@ -77,8 +77,10 @@ sub get_v0 {
 					no   => $status->{train_no},
 				},
 				actionTime    => $status->{timestamp}->epoch,
-				scheduledTime => $status->{sched_ts}->epoch,
-				realTime      => $status->{real_ts}->epoch,
+				scheduledTime => $status->{sched_arrival}->epoch
+				  || $status->{sched_departure}->epoch,
+				realTime => $status->{real_arrival}->epoch
+				  || $status->{real_departure}->epoch,
 			},
 		);
 	}
diff --git a/lib/Travelynx/Controller/Static.pm b/lib/Travelynx/Controller/Static.pm
index 0144d83..09d7f51 100644
--- a/lib/Travelynx/Controller/Static.pm
+++ b/lib/Travelynx/Controller/Static.pm
@@ -6,7 +6,8 @@ my $travelynx_version = qx{git describe --dirty} || 'experimental';
 sub about {
 	my ($self) = @_;
 
-	$self->render( 'about', version => $self->app->config->{version} // 'UNKNOWN' );
+	$self->render( 'about',
+		version => $self->app->config->{version} // 'UNKNOWN' );
 }
 
 sub imprint {
diff --git a/lib/Travelynx/Controller/Traveling.pm b/lib/Travelynx/Controller/Traveling.pm
index b43c891..d8e5e03 100755
--- a/lib/Travelynx/Controller/Traveling.pm
+++ b/lib/Travelynx/Controller/Traveling.pm
@@ -97,13 +97,16 @@ sub log_action {
 		else {
 			$self->render(
 				json => {
-					success => 1,
+					success     => 1,
+					redirect_to => '/',
 				},
 			);
 		}
 	}
 	elsif ( $params->{action} eq 'checkout' ) {
-		my $error = $self->checkout( $params->{station}, $params->{force} );
+		my ( $still_checked_in, $error )
+		  = $self->checkout( $params->{station}, $params->{force} );
+		my $station_link = '/s/' . $params->{station};
 
 		if ($error) {
 			$self->render(
@@ -116,7 +119,8 @@ sub log_action {
 		else {
 			$self->render(
 				json => {
-					success => 1,
+					success     => 1,
+					redirect_to => $still_checked_in ? '/' : $station_link,
 				},
 			);
 		}
@@ -134,7 +138,8 @@ sub log_action {
 		else {
 			$self->render(
 				json => {
-					success => 1,
+					success     => 1,
+					redirect_to => '/',
 				},
 			);
 		}
@@ -155,13 +160,15 @@ sub log_action {
 		else {
 			$self->render(
 				json => {
-					success => 1,
+					success     => 1,
+					redirect_to => '/',
 				},
 			);
 		}
 	}
 	elsif ( $params->{action} eq 'cancelled_to' ) {
-		my $error = $self->checkout( $params->{station}, 1,
+		my ( undef, $error )
+		  = $self->checkout( $params->{station}, 1,
 			$self->app->action_type->{cancelled_to} );
 
 		if ($error) {
@@ -175,14 +182,14 @@ sub log_action {
 		else {
 			$self->render(
 				json => {
-					success => 1,
+					success     => 1,
+					redirect_to => '/',
 				},
 			);
 		}
 	}
 	elsif ( $params->{action} eq 'delete' ) {
-		my ( $from, $to ) = split( qr{,}, $params->{ids} );
-		my $error = $self->delete_journey( $from, $to, $params->{checkin},
+		my $error = $self->delete_journey( $params->{id}, $params->{checkin},
 			$params->{checkout} );
 		if ($error) {
 			$self->render(
@@ -195,7 +202,8 @@ sub log_action {
 		else {
 			$self->render(
 				json => {
-					success => 1,
+					success     => 1,
+					redirect_to => '/history',
 				},
 			);
 		}
@@ -215,7 +223,7 @@ sub station {
 	my $station = $self->stash('station');
 	my $train   = $self->param('train');
 
-	my $status = $self->get_departures($station);
+	my $status = $self->get_departures( $station, 120, 30 );
 
 	if ( $status->{errstr} ) {
 		$self->render(
@@ -382,11 +390,13 @@ sub monthly_history {
 
 sub journey_details {
 	my ($self) = @_;
-	my ( $uid, $checkout_id ) = split( qr{-}, $self->stash('id') );
+	my $journey_id = $self->stash('id');
+
+	my $uid = $self->current_user->{id};
 
-	$self->param( journey_id => $checkout_id );
+	$self->param( journey_id => $journey_id );
 
-	if ( not( $uid == $self->current_user->{id} and $checkout_id ) ) {
+	if ( not($journey_id) ) {
 		$self->render(
 			'journey',
 			error   => 'notfound',
@@ -395,36 +405,35 @@ sub journey_details {
 		return;
 	}
 
-	my @journeys = $self->get_user_travels(
-		uid         => $uid,
-		checkout_id => $checkout_id,
-		verbose     => 1,
+	my $journey = $self->get_journey(
+		uid        => $uid,
+		journey_id => $journey_id,
+		verbose    => 1,
 	);
-	if (   @journeys == 0
-		or not $journeys[0]{completed}
-		or $journeys[0]{ids}[1] != $checkout_id )
-	{
+
+	if ($journey) {
+		$self->render(
+			'journey',
+			error   => undef,
+			journey => $journey,
+		);
+	}
+	else {
 		$self->render(
 			'journey',
 			error   => 'notfound',
 			journey => {}
 		);
-		return;
 	}
 
-	$self->render(
-		'journey',
-		error   => undef,
-		journey => $journeys[0]
-	);
 }
 
 sub edit_journey {
-	my ($self)      = @_;
-	my $checkout_id = $self->param('journey_id');
-	my $uid         = $self->current_user->{id};
+	my ($self)     = @_;
+	my $journey_id = $self->param('journey_id');
+	my $uid        = $self->current_user->{id};
 
-	if ( not( $uid == $self->current_user->{id} and $checkout_id ) ) {
+	if ( not( $journey_id =~ m{ ^ \d+ $ }x ) ) {
 		$self->render(
 			'edit_journey',
 			error   => 'notfound',
@@ -434,8 +443,8 @@ sub edit_journey {
 	}
 
 	my $journey = $self->get_journey(
-		uid         => $uid,
-		checkout_id => $checkout_id
+		uid        => $uid,
+		journey_id => $journey_id
 	);
 
 	if ( not $journey ) {
@@ -449,11 +458,6 @@ sub edit_journey {
 
 	my $error = undef;
 
-	if ( $self->param('action') and $self->param('action') eq 'cancel' ) {
-		$self->redirect_to("/journey/${uid}-${checkout_id}");
-		return;
-	}
-
 	if ( $self->param('action') and $self->param('action') eq 'save' ) {
 		my $parser = DateTime::Format::Strptime->new(
 			pattern   => '%d.%m.%Y %H:%M',
@@ -468,12 +472,8 @@ sub edit_journey {
 		{
 			my $datetime = $parser->parse_datetime( $self->param($key) );
 			if ( $datetime and $datetime->epoch ne $journey->{$key}->epoch ) {
-				$error = $self->update_journey_part(
-					$db,
-					$journey->{ids}[0],
-					$journey->{ids}[1],
-					$key, $datetime
-				);
+				$error = $self->update_journey_part( $db, $journey->{id},
+					$key, $datetime );
 				if ($error) {
 					last;
 				}
@@ -482,17 +482,16 @@ sub edit_journey {
 
 		if ( not $error ) {
 			$journey = $self->get_journey(
-				uid         => $uid,
-				db          => $db,
-				checkout_id => $checkout_id,
-				verbose     => 1
+				uid        => $uid,
+				db         => $db,
+				journey_id => $journey_id,
+				verbose    => 1
 			);
 			$error = $self->journey_sanity_check($journey);
 		}
 		if ( not $error ) {
 			$tx->commit;
-			$self->redirect_to("/journey/${uid}-${checkout_id}");
-			$self->invalidate_stats_cache( $journey->{checkout} );
+			$self->redirect_to("/journey/${journey_id}");
 			return;
 		}
 	}