From 76f6922e825ea4f4f2ca0054bdd29ccd8e9edeca Mon Sep 17 00:00:00 2001
From: Daniel Friesel <derf@finalrewind.org>
Date: Sun, 31 Mar 2019 17:58:05 +0200
Subject: maintenance: delete accounts which requested deletion

---
 lib/Travelynx/Command/maintenance.pm | 36 ++++++++++++++++++++++++++++++++++++
 1 file changed, 36 insertions(+)

diff --git a/lib/Travelynx/Command/maintenance.pm b/lib/Travelynx/Command/maintenance.pm
index b9f5947..329855b 100644
--- a/lib/Travelynx/Command/maintenance.pm
+++ b/lib/Travelynx/Command/maintenance.pm
@@ -14,6 +14,7 @@ sub run {
 
 	my $now = DateTime->now( time_zone => 'Europe/Berlin' );
 	my $verification_deadline = $now->subtract( hours => 48 )->epoch;
+	my $deletion_deadline     = $now->subtract( hours => 72 )->epoch;
 
 	my $get_unverified_query
 	  = $dbh->prepare(
@@ -21,6 +22,8 @@ qq{select email, extract(epoch from registered_at) from users where status = 0 a
 	  );
 	my $get_pending_query
 	  = $dbh->prepare(qq{select num_tries from pending_mails where email = ?;});
+	my $get_deleted_query = $dbh->prepare(
+		qq{select id from users where deletion_requested < to_timestamp(?);});
 	my $set_pending_query
 	  = $dbh->prepare(
 qq{update pending_mails set num_tries = ?, last_try = to_timestamp(?) where email = ?;}
@@ -33,6 +36,13 @@ qq{insert into pending_mails (email, num_tries, last_try) values (?, ?, to_times
 	  = $dbh->prepare(
 qq{delete from users where status = 0 and registered_at < to_timestamp(?);}
 	  );
+	my $drop_tokens_query
+	  = $dbh->prepare(qq{delete from tokens where user_id = ?;});
+	my $drop_stats_query
+	  = $dbh->prepare(qq{delete from monthly_stats where user_id = ?;});
+	my $drop_actions_query
+	  = $dbh->prepare(qq{delete from user_actions where user_id = ?;});
+	my $drop_user_query = $dbh->prepare(qq{delete from users where id = ?;});
 
 	$dbh->begin_work;
 	$get_unverified_query->execute($verification_deadline);
@@ -57,6 +67,32 @@ qq{delete from users where status = 0 and registered_at < to_timestamp(?);}
 		$drop_unverified_query->rows );
 	$dbh->commit;
 
+	$dbh->begin_work;
+	$get_deleted_query->execute($deletion_deadline);
+	my @uids_to_delete
+	  = map { $_->[0] } @{ $get_deleted_query->fetchall_arrayref };
+
+	if ( @uids_to_delete < 10 ) {
+		for my $uid (@uids_to_delete) {
+			say "Deleting uid ${uid}...";
+			$drop_tokens_query->execute($uid);
+			$drop_stats_query->execute($uid);
+			$drop_actions_query->execute($uid);
+			$drop_user_query->execute($uid);
+			printf( "    %d tokens, %d monthly stats, %d actions\n",
+				$drop_tokens_query->rows, $drop_stats_query->rows,
+				$drop_actions_query->rows );
+		}
+	}
+	else {
+		printf(
+			"Unusually high number of deletion requests (%d accounts)"
+			  . " -- skipping automatic deletion, please investigate\n",
+			scalar @uids_to_delete
+		);
+	}
+	$dbh->commit;
+
 	$dbh->disconnect;
 }
 
-- 
cgit v1.2.3