From e42b7030d0513c5a7ccb02a1cd61825ca7067ac6 Mon Sep 17 00:00:00 2001
From: Daniel Friesel <derf@finalrewind.org>
Date: Sun, 15 Jan 2023 06:47:09 +0100
Subject: update integritycheck to use internal station database

---
 lib/Travelynx/Command/integritycheck.pm | 90 ++++++++++++++++++++-------------
 1 file changed, 55 insertions(+), 35 deletions(-)

diff --git a/lib/Travelynx/Command/integritycheck.pm b/lib/Travelynx/Command/integritycheck.pm
index 4cfc48d..96b46c1 100644
--- a/lib/Travelynx/Command/integritycheck.pm
+++ b/lib/Travelynx/Command/integritycheck.pm
@@ -10,64 +10,84 @@ use Travel::Status::DE::IRIS::Stations;
 
 sub run {
 	my ($self) = @_;
+	my $found  = 0;
+	my $db     = $self->app->pg->db;
 
-	my %station
-	  = map { $_->[2] => 1 } Travel::Status::DE::IRIS::Stations::get_stations();
-
-	my @journey_stations;
+	my $res1 = $db->query(
+		qq{
+			select checkin_station_id
+			from journeys
+			left join stations on journeys.checkin_station_id = stations.eva
+			where stations.eva is null;
+		}
+	);
 
-	my $res
-	  = $self->app->pg->db->select( 'journeys', ['checkin_station_id'], {},
-		{ group_by => ['checkin_station_id'] } );
-	for my $j ( $res->hashes->each ) {
-		push( @journey_stations, $j->{checkin_station_id} );
-	}
+	my $res2 = $db->query(
+		qq{
+			select checkout_station_id
+			from journeys
+			left join stations on journeys.checkout_station_id = stations.eva
+			where stations.eva is null;
+		}
+	);
 
-	$res = $self->app->pg->db->select( 'journeys', ['checkout_station_id'], {},
-		{ group_by => ['checkout_station_id'] } );
-	for my $j ( $res->hashes->each ) {
-		push( @journey_stations, $j->{checkout_station_id} );
+	my %notified;
+	while ( my $row = $res1->hash ) {
+		my $eva = $row->{checkin_station_id};
+		if ( not $found ) {
+			$found = 1;
+			say
+'Journeys in the travelynx database contain the following unknown EVA IDs.';
+			say '------------8<----------';
+			say 'Travel::Status::DE::IRIS v'
+			  . $Travel::Status::DE::IRIS::Stations::VERSION;
+		}
+		if ( not $notified{$eva} ) {
+			say $eva;
+			$notified{$eva} = 1;
+		}
 	}
 
-	@journey_stations = List::Util::uniq @journey_stations;
-	my $found = 0;
-
-	for my $eva (@journey_stations) {
-		if ( not $station{$eva} ) {
-			if ( not $found ) {
-				say
+	while ( my $row = $res2->hash ) {
+		my $eva = $row->{checkout_station_id};
+		if ( not $found ) {
+			$found = 1;
+			say
 'Journeys in the travelynx database contain the following unknown EVA IDs.';
-				say '------------8<----------';
-				say 'Travel::Status::DE::IRIS v'
-				  . $Travel::Status::DE::IRIS::Stations::VERSION;
-				$found = 1;
-			}
+			say '------------8<----------';
+			say 'Travel::Status::DE::IRIS v'
+			  . $Travel::Status::DE::IRIS::Stations::VERSION;
+		}
+		if ( not $notified{$eva} ) {
 			say $eva;
+			$notified{$eva} = 1;
 		}
 	}
+
 	if ($found) {
 		say '------------8<----------';
 		say '';
 		$found = 0;
 	}
 
-	%station
-	  = map { $_->[1] => 1 } Travel::Status::DE::IRIS::Stations::get_stations();
-	my %notified;
 	my $rename = $self->app->renamed_station;
 
-	$res
-	  = $self->app->pg->db->select( 'journeys', [ 'route', 'edited' ] )->expand;
+	my $res = $db->select( 'journeys', [ 'route', 'edited' ] )->expand;
 	while ( my $j = $res->hash ) {
 		if ( $j->{edited} & 0x0010 ) {
 			next;
 		}
-		for my $stop ( @{ $j->{route} // [] } ) {
+		my @stops = @{ $j->{route} // [] };
+		for my $stop (@stops) {
 			my $stop_name = $stop->[0];
-			if ( $rename->{$stop_name} ) {
-				$stop_name = $rename->{$stop_name};
+			if ( $rename->{ $stop->[0] } ) {
+				$stop->[0] = $rename->{ $stop->[0] };
 			}
-			if ( not $station{$stop_name} and not $notified{$stop_name} ) {
+		}
+		my @unknown
+		  = $self->app->stations->grep_unknown( map { $_->[0] } @stops );
+		for my $stop_name (@unknown) {
+			if ( not $notified{$stop_name} ) {
 				if ( not $found ) {
 					say
 'Journeys in the travelynx database contain the following unknown route entries.';
-- 
cgit v1.2.3