From f08bdaca5cafc6840cbf8489d7790656bf38f9e4 Mon Sep 17 00:00:00 2001
From: Daniel Friesel <derf@finalrewind.org>
Date: Mon, 27 Jul 2020 18:53:22 +0200
Subject: Move user model to a separate module

---
 lib/Travelynx.pm                      | 413 +-------------------------------
 lib/Travelynx/Controller/Account.pm   | 112 ++++++---
 lib/Travelynx/Controller/Traveling.pm |   8 +-
 lib/Travelynx/Model/Users.pm          | 434 ++++++++++++++++++++++++++++++++++
 4 files changed, 533 insertions(+), 434 deletions(-)
 create mode 100644 lib/Travelynx/Model/Users.pm

(limited to 'lib')

diff --git a/lib/Travelynx.pm b/lib/Travelynx.pm
index 285cfef..378a2ca 100755
--- a/lib/Travelynx.pm
+++ b/lib/Travelynx.pm
@@ -19,6 +19,7 @@ use Travel::Status::DE::DBWagenreihung;
 use Travel::Status::DE::IRIS;
 use Travel::Status::DE::IRIS::Stations;
 use Travelynx::Helper::Sendmail;
+use Travelynx::Model::Users;
 use XML::LibXML;
 
 sub check_password {
@@ -93,7 +94,8 @@ sub startup {
 			},
 			validate_user => sub {
 				my ( $self, $username, $password, $extradata ) = @_;
-				my $user_info = $self->get_user_password($username);
+				my $user_info
+				  = $self->users->get_login_data( name => $username );
 				if ( not $user_info ) {
 					return undef;
 				}
@@ -270,6 +272,13 @@ sub startup {
 		}
 	);
 
+	$self->helper(
+		users => sub {
+			my ($self) = @_;
+			state $users = Travelynx::Model::Users->new( pg => $self->pg );
+		}
+	);
+
 	$self->helper(
 		pg => sub {
 			my ($self) = @_;
@@ -918,18 +927,6 @@ sub startup {
 		}
 	);
 
-	$self->helper(
-		'mark_seen' => sub {
-			my ( $self, $uid ) = @_;
-
-			$self->pg->db->update(
-				'users',
-				{ last_seen => DateTime->now( time_zone => 'Europe/Berlin' ) },
-				{ id        => $uid }
-			);
-		}
-	);
-
 	$self->helper(
 		'update_in_transit_comment' => sub {
 			my ( $self, $comment, $uid ) = @_;
@@ -1151,197 +1148,6 @@ sub startup {
 		}
 	);
 
-	$self->helper(
-		'verify_registration_token' => sub {
-			my ( $self, $uid, $token ) = @_;
-
-			my $db = $self->pg->db;
-			my $tx = $db->begin;
-
-			my $res = $db->select(
-				'pending_registrations',
-				'count(*) as count',
-				{
-					user_id => $uid,
-					token   => $token
-				}
-			);
-
-			if ( $res->hash->{count} ) {
-				$db->update( 'users', { status => 1 }, { id => $uid } );
-				$db->delete( 'pending_registrations', { user_id => $uid } );
-				$tx->commit;
-				return 1;
-			}
-			return;
-		}
-	);
-
-	$self->helper(
-		'get_uid_by_name_and_mail' => sub {
-			my ( $self, $name, $email ) = @_;
-
-			my $res = $self->pg->db->select(
-				'users',
-				['id'],
-				{
-					name   => $name,
-					email  => $email,
-					status => 1
-				}
-			);
-
-			if ( my $user = $res->hash ) {
-				return $user->{id};
-			}
-			return;
-		}
-	);
-
-	$self->helper(
-		'get_privacy_by_name' => sub {
-			my ( $self, $name ) = @_;
-
-			my $res = $self->pg->db->select(
-				'users',
-				[ 'id', 'public_level' ],
-				{
-					name   => $name,
-					status => 1
-				}
-			);
-
-			if ( my $user = $res->hash ) {
-				return $user;
-			}
-			return;
-		}
-	);
-
-	$self->helper(
-		'set_privacy' => sub {
-			my ( $self, $uid, $public_level ) = @_;
-
-			$self->pg->db->update(
-				'users',
-				{ public_level => $public_level },
-				{ id           => $uid }
-			);
-		}
-	);
-
-	$self->helper(
-		'mark_for_password_reset' => sub {
-			my ( $self, $db, $uid, $token ) = @_;
-
-			my $res = $db->select(
-				'pending_passwords',
-				'count(*) as count',
-				{ user_id => $uid }
-			);
-			if ( $res->hash->{count} ) {
-				return 'in progress';
-			}
-
-			$db->insert(
-				'pending_passwords',
-				{
-					user_id => $uid,
-					token   => $token,
-					requested_at =>
-					  DateTime->now( time_zone => 'Europe/Berlin' )
-				}
-			);
-
-			return undef;
-		}
-	);
-
-	$self->helper(
-		'verify_password_token' => sub {
-			my ( $self, $uid, $token ) = @_;
-
-			my $res = $self->pg->db->select(
-				'pending_passwords',
-				'count(*) as count',
-				{
-					user_id => $uid,
-					token   => $token
-				}
-			);
-
-			if ( $res->hash->{count} ) {
-				return 1;
-			}
-			return;
-		}
-	);
-
-	$self->helper(
-		'mark_for_mail_change' => sub {
-			my ( $self, $db, $uid, $email, $token ) = @_;
-
-			$db->insert(
-				'pending_mails',
-				{
-					user_id => $uid,
-					email   => $email,
-					token   => $token,
-					requested_at =>
-					  DateTime->now( time_zone => 'Europe/Berlin' )
-				},
-				{
-					on_conflict => \
-'(user_id) do update set email = EXCLUDED.email, token = EXCLUDED.token, requested_at = EXCLUDED.requested_at'
-				},
-			);
-		}
-	);
-
-	$self->helper(
-		'change_mail_with_token' => sub {
-			my ( $self, $uid, $token ) = @_;
-
-			my $db = $self->pg->db;
-			my $tx = $db->begin;
-
-			my $res_h = $db->select(
-				'pending_mails',
-				['email'],
-				{
-					user_id => $uid,
-					token   => $token
-				}
-			)->hash;
-
-			if ($res_h) {
-				$db->update(
-					'users',
-					{ email => $res_h->{email} },
-					{ id    => $uid }
-				);
-				$db->delete( 'pending_mails', { user_id => $uid } );
-				$tx->commit;
-				return 1;
-			}
-			return;
-		}
-	);
-
-	$self->helper(
-		'remove_password_token' => sub {
-			my ( $self, $uid, $token ) = @_;
-
-			$self->pg->db->delete(
-				'pending_passwords',
-				{
-					user_id => $uid,
-					token   => $token
-				}
-			);
-		}
-	);
-
 	# This helper should only be called directly when also providing a user ID.
 	# If you don't have one, use current_user() instead (get_user_data will
 	# delegate to it anyways).
@@ -1351,39 +1157,7 @@ sub startup {
 
 			$uid //= $self->current_user->{id};
 
-			my $user_data = $self->pg->db->select(
-				'users',
-				'id, name, status, public_level, email, '
-				  . 'extract(epoch from registered_at) as registered_at_ts, '
-				  . 'extract(epoch from last_seen) as last_seen_ts, '
-				  . 'extract(epoch from deletion_requested) as deletion_requested_ts',
-				{ id => $uid }
-			)->hash;
-
-			if ($user_data) {
-				return {
-					id            => $user_data->{id},
-					name          => $user_data->{name},
-					status        => $user_data->{status},
-					is_public     => $user_data->{public_level},
-					email         => $user_data->{email},
-					registered_at => DateTime->from_epoch(
-						epoch     => $user_data->{registered_at_ts},
-						time_zone => 'Europe/Berlin'
-					),
-					last_seen => DateTime->from_epoch(
-						epoch     => $user_data->{last_seen_ts},
-						time_zone => 'Europe/Berlin'
-					),
-					deletion_requested => $user_data->{deletion_requested_ts}
-					? DateTime->from_epoch(
-						epoch     => $user_data->{deletion_requested_ts},
-						time_zone => 'Europe/Berlin'
-					  )
-					: undef,
-				};
-			}
-			return undef;
+			return $self->users->get_data( uid => $uid );
 		}
 	);
 
@@ -1534,153 +1308,6 @@ sub startup {
 		}
 	);
 
-	$self->helper(
-		'get_user_password' => sub {
-			my ( $self, $name ) = @_;
-
-			my $res_h = $self->pg->db->select(
-				'users',
-				'id, name, status, password as password_hash',
-				{ name => $name }
-			)->hash;
-
-			return $res_h;
-		}
-	);
-
-	$self->helper(
-		'add_user' => sub {
-			my ( $self, $db, $user_name, $email, $token, $password ) = @_;
-
-          # This helper must be called during a transaction, as user creation
-          # may fail even after the database entry has been generated, e.g.  if
-          # the registration mail cannot be sent. We therefore use $db (the
-          # database handle performing the transaction) instead of $self->pg->db
-          # (which may be a new handle not belonging to the transaction).
-
-			my $now = DateTime->now( time_zone => 'Europe/Berlin' );
-
-			my $res = $db->insert(
-				'users',
-				{
-					name          => $user_name,
-					status        => 0,
-					public_level  => 0,
-					email         => $email,
-					password      => $password,
-					registered_at => $now,
-					last_seen     => $now,
-				},
-				{ returning => 'id' }
-			);
-			my $uid = $res->hash->{id};
-
-			$db->insert(
-				'pending_registrations',
-				{
-					user_id => $uid,
-					token   => $token
-				}
-			);
-
-			return $uid;
-		}
-	);
-
-	$self->helper(
-		'flag_user_deletion' => sub {
-			my ( $self, $uid ) = @_;
-
-			my $now = DateTime->now( time_zone => 'Europe/Berlin' );
-
-			$self->pg->db->update(
-				'users',
-				{ deletion_requested => $now },
-				{
-					id => $uid,
-				}
-			);
-		}
-	);
-
-	$self->helper(
-		'unflag_user_deletion' => sub {
-			my ( $self, $uid ) = @_;
-
-			$self->pg->db->update(
-				'users',
-				{
-					deletion_requested => undef,
-				},
-				{
-					id => $uid,
-				}
-			);
-		}
-	);
-
-	$self->helper(
-		'set_user_password' => sub {
-			my ( $self, $uid, $password ) = @_;
-
-			$self->pg->db->update(
-				'users',
-				{ password => $password },
-				{ id       => $uid }
-			);
-		}
-	);
-
-	$self->helper(
-		'check_if_user_name_exists' => sub {
-			my ( $self, $user_name ) = @_;
-
-			my $count = $self->pg->db->select(
-				'users',
-				'count(*) as count',
-				{ name => $user_name }
-			)->hash->{count};
-
-			if ($count) {
-				return 1;
-			}
-			return 0;
-		}
-	);
-
-	$self->helper(
-		'check_if_mail_is_blacklisted' => sub {
-			my ( $self, $mail ) = @_;
-
-			my $count = $self->pg->db->select(
-				'users',
-				'count(*) as count',
-				{
-					email  => $mail,
-					status => 0,
-				}
-			)->hash->{count};
-
-			if ($count) {
-				return 1;
-			}
-
-			$count = $self->pg->db->select(
-				'mail_blacklist',
-				'count(*) as count',
-				{
-					email     => $mail,
-					num_tries => { '>', 1 },
-				}
-			)->hash->{count};
-
-			if ($count) {
-				return 1;
-			}
-			return 0;
-		}
-	);
-
 	$self->helper(
 		'delete_journey' => sub {
 			my ( $self, $journey_id, $checkin_epoch, $checkout_epoch ) = @_;
@@ -2910,24 +2537,6 @@ sub startup {
 		}
 	);
 
-	$self->helper(
-		'account_use_history' => sub {
-			my ( $self, $uid, $value ) = @_;
-
-			if ($value) {
-				$self->pg->db->update(
-					'users',
-					{ use_history => $value },
-					{ id          => $uid }
-				);
-			}
-			else {
-				return $self->pg->db->select( 'users', ['use_history'],
-					{ id => $uid } )->hash->{use_history};
-			}
-		}
-	);
-
 	$self->helper(
 		'get_user_travels' => sub {
 			my ( $self, %opt ) = @_;
diff --git a/lib/Travelynx/Controller/Account.pm b/lib/Travelynx/Controller/Account.pm
index 135c52b..10200a6 100644
--- a/lib/Travelynx/Controller/Account.pm
+++ b/lib/Travelynx/Controller/Account.pm
@@ -7,7 +7,7 @@ use UUID::Tiny qw(:std);
 sub hash_password {
 	my ($password) = @_;
 	my @salt_bytes = map { int( rand(255) ) + 1 } ( 1 .. 16 );
-	my $salt = en_base64( pack( 'C[16]', @salt_bytes ) );
+	my $salt       = en_base64( pack( 'C[16]', @salt_bytes ) );
 
 	return bcrypt( $password, '$2a$12$' . $salt );
 }
@@ -38,10 +38,10 @@ sub do_login {
 	else {
 		if ( $self->authenticate( $user, $password ) ) {
 			$self->redirect_to( $self->req->param('redirect_to') // '/' );
-			$self->mark_seen( $self->current_user->{id} );
+			$self->users->mark_seen( uid => $self->current_user->{id} );
 		}
 		else {
-			my $data = $self->get_user_password($user);
+			my $data = $self->users->get_login_data( name => $user );
 			if ( $data and $data->{status} == 0 ) {
 				$self->render( 'login', invalid => 'confirmation' );
 			}
@@ -95,12 +95,12 @@ sub register {
 		return;
 	}
 
-	if ( $self->check_if_user_name_exists($user) ) {
+	if ( $self->users->check_if_user_name_exists( name => $user ) ) {
 		$self->render( 'register', invalid => 'user_collision' );
 		return;
 	}
 
-	if ( $self->check_if_mail_is_blacklisted($email) ) {
+	if ( $self->users->check_if_mail_is_blacklisted( email => $email ) ) {
 		$self->render( 'register', invalid => 'mail_blacklisted' );
 		return;
 	}
@@ -115,11 +115,17 @@ sub register {
 		return;
 	}
 
-	my $token       = make_token();
-	my $pw_hash     = hash_password($password);
-	my $db          = $self->pg->db;
-	my $tx          = $db->begin;
-	my $user_id     = $self->add_user( $db, $user, $email, $token, $pw_hash );
+	my $token   = make_token();
+	my $pw_hash = hash_password($password);
+	my $db      = $self->pg->db;
+	my $tx      = $db->begin;
+	my $user_id = $self->users->add_user(
+		db            => $db,
+		name          => $user,
+		email         => $email,
+		token         => $token,
+		password_hash => $pw_hash
+	);
 	my $reg_url     = $self->url_for('reg')->to_abs->scheme('https');
 	my $imprint_url = $self->url_for('impressum')->to_abs->scheme('https');
 
@@ -164,7 +170,13 @@ sub verify {
 		return;
 	}
 
-	if ( not $self->verify_registration_token( $id, $token ) ) {
+	if (
+		not $self->users->verify_registration_token(
+			uid   => $id,
+			token => $token
+		)
+	  )
+	{
 		$self->render( 'register', invalid => 'token' );
 		return;
 	}
@@ -190,10 +202,10 @@ sub delete {
 			$self->render( 'account', invalid => 'deletion password' );
 			return;
 		}
-		$self->flag_user_deletion( $self->current_user->{id} );
+		$self->users->flag_deletion( uid => $self->current_user->{id} );
 	}
 	else {
-		$self->unflag_user_deletion( $self->current_user->{id} );
+		$self->users->unflag_deletion( uid => $self->current_user->{id} );
 	}
 	$self->redirect_to('account');
 }
@@ -249,7 +261,10 @@ sub privacy {
 			$public_level &= ~0x30;
 		}
 
-		$self->set_privacy( $user->{id}, $public_level );
+		$self->users->set_privacy(
+			uid   => $user->{id},
+			level => $public_level
+		);
 
 		$self->flash( success => 'privacy' );
 		$self->redirect_to('account');
@@ -274,7 +289,7 @@ sub insight {
 	my ($self) = @_;
 
 	my $user        = $self->current_user;
-	my $use_history = $self->account_use_history( $user->{id} );
+	my $use_history = $self->users->use_history( uid => $user->{id} );
 
 	if ( $self->param('action') and $self->param('action') eq 'save' ) {
 		if ( $self->param('on_departure') ) {
@@ -291,7 +306,10 @@ sub insight {
 			$use_history &= ~0x02;
 		}
 
-		$self->account_use_history( $user->{id}, $use_history );
+		$self->users->use_history(
+			uid => $user->{id},
+			set => $use_history
+		);
 		$self->flash( success => 'use_history' );
 		$self->redirect_to('account');
 	}
@@ -375,8 +393,12 @@ sub change_mail {
 		my $db    = $self->pg->db;
 		my $tx    = $db->begin;
 
-		$self->mark_for_mail_change( $db, $self->current_user->{id},
-			$email, $token );
+		$self->users->mark_for_mail_change(
+			db    => $db,
+			uid   => $self->current_user->{id},
+			email => $email,
+			token => $token
+		);
 
 		my $ip   = $self->req->headers->header('X-Forwarded-For');
 		my $ua   = $self->req->headers->user_agent;
@@ -459,7 +481,10 @@ sub change_password {
 	}
 
 	my $pw_hash = hash_password($password);
-	$self->set_user_password( $self->current_user->{id}, $pw_hash );
+	$self->users->set_password_hash(
+		uid           => $self->current_user->{id},
+		password_hash => $pw_hash
+	);
 
 	$self->flash( success => 'password' );
 	$self->redirect_to('account');
@@ -500,7 +525,10 @@ sub request_password_reset {
 		my $name  = $self->param('user');
 		my $email = $self->param('email');
 
-		my $uid = $self->get_uid_by_name_and_mail( $name, $email );
+		my $uid = $self->users->get_uid_by_name_and_mail(
+			name  => $name,
+			email => $email
+		);
 
 		if ( not $uid ) {
 			$self->render( 'recover_password',
@@ -512,7 +540,11 @@ sub request_password_reset {
 		my $db    = $self->pg->db;
 		my $tx    = $db->begin;
 
-		my $error = $self->mark_for_password_reset( $db, $uid, $token );
+		my $error = $self->users->mark_for_password_reset(
+			db    => $db,
+			uid   => $uid,
+			token => $token
+		);
 
 		if ($error) {
 			$self->render( 'recover_password', invalid => $error );
@@ -570,7 +602,13 @@ sub request_password_reset {
 			$self->render( 'set_password', invalid => 'csrf' );
 			return;
 		}
-		if ( not $self->verify_password_token( $id, $token ) ) {
+		if (
+			not $self->users->verify_password_token(
+				uid   => $id,
+				token => $token
+			)
+		  )
+		{
 			$self->render( 'recover_password', invalid => 'change token' );
 			return;
 		}
@@ -585,7 +623,10 @@ sub request_password_reset {
 		}
 
 		my $pw_hash = hash_password($password);
-		$self->set_user_password( $id, $pw_hash );
+		$self->users->set_password_hash(
+			uid           => $id,
+			password_hash => $pw_hash
+		);
 
 		my $account = $self->get_user_data($id);
 
@@ -597,7 +638,10 @@ sub request_password_reset {
 		$self->flash( success => 'password' );
 		$self->redirect_to('account');
 
-		$self->remove_password_token( $id, $token );
+		$self->users->remove_password_token(
+			uid   => $id,
+			token => $token
+		);
 
 		my $user  = $account->{name};
 		my $email = $account->{email};
@@ -641,7 +685,13 @@ sub recover_password {
 		return;
 	}
 
-	if ( $self->verify_password_token( $id, $token ) ) {
+	if (
+		$self->users->verify_password_token(
+			uid   => $id,
+			token => $token
+		)
+	  )
+	{
 		$self->render('set_password');
 	}
 	else {
@@ -654,7 +704,13 @@ sub confirm_mail {
 	my $id     = $self->current_user->{id};
 	my $token  = $self->stash('token');
 
-	if ( $self->change_mail_with_token( $id, $token ) ) {
+	if (
+		$self->users->change_mail_with_token(
+			uid   => $id,
+			token => $token
+		)
+	  )
+	{
 		$self->flash( success => 'mail' );
 		$self->redirect_to('account');
 	}
@@ -667,7 +723,7 @@ sub account {
 	my ($self) = @_;
 
 	$self->render('account');
-	$self->mark_seen( $self->current_user->{id} );
+	$self->users->mark_seen( uid => $self->current_user->{id} );
 }
 
 sub json_export {
@@ -678,7 +734,7 @@ sub json_export {
 
 	$self->render(
 		json => {
-			account => $db->select( 'users', '*', { id => $uid } )->hash,
+			account    => $db->select( 'users', '*', { id => $uid } )->hash,
 			in_transit => [
 				$db->select( 'in_transit_str', '*', { user_id => $uid } )
 				  ->hashes->each
diff --git a/lib/Travelynx/Controller/Traveling.pm b/lib/Travelynx/Controller/Traveling.pm
index 702a89e..05bbccd 100755
--- a/lib/Travelynx/Controller/Traveling.pm
+++ b/lib/Travelynx/Controller/Traveling.pm
@@ -19,7 +19,7 @@ sub homepage {
 			with_autocomplete => 1,
 			with_geolocation  => 1
 		);
-		$self->mark_seen( $self->current_user->{id} );
+		$self->users->mark_seen( uid => $self->current_user->{id} );
 	}
 	else {
 		$self->render(
@@ -35,7 +35,7 @@ sub user_status {
 
 	my $name = $self->stash('name');
 	my $ts   = $self->stash('ts') // 0;
-	my $user = $self->get_privacy_by_name($name);
+	my $user = $self->users->get_privacy_by_name( name => $name );
 
 	if ( not $user or not $user->{public_level} & 0x03 ) {
 		$self->render('not_found');
@@ -150,7 +150,7 @@ sub public_status_card {
 	my ($self) = @_;
 
 	my $name = $self->stash('name');
-	my $user = $self->get_privacy_by_name($name);
+	my $user = $self->users->get_privacy_by_name( name => $name );
 
 	delete $self->stash->{layout};
 
@@ -457,7 +457,7 @@ sub station {
 			title            => "travelynx: $status->{station_name}",
 		);
 	}
-	$self->mark_seen( $self->current_user->{id} );
+	$self->users->mark_seen( uid => $self->current_user->{id} );
 }
 
 sub redirect_to_station {
diff --git a/lib/Travelynx/Model/Users.pm b/lib/Travelynx/Model/Users.pm
new file mode 100644
index 0000000..830ec63
--- /dev/null
+++ b/lib/Travelynx/Model/Users.pm
@@ -0,0 +1,434 @@
+package Travelynx::Model::Users;
+
+use strict;
+use warnings;
+use 5.020;
+
+use DateTime;
+
+sub new {
+	my ( $class, %opt ) = @_;
+
+	return bless( \%opt, $class );
+}
+
+sub mark_seen {
+	my ($self, %opt) = @_;
+	my $uid = $opt{uid};
+	my $db = $opt{db} // $self->{pg}->db;
+
+	$db->update(
+		'users',
+		{ last_seen => DateTime->now( time_zone => 'Europe/Berlin' ) },
+		{ id        => $uid }
+	);
+}
+
+sub verify_registration_token {
+	my ( $self, %opt ) = @_;
+	my $uid = $opt{uid};
+	my $token = $opt{token};
+	my $db = $opt{db} // $self->{pg}->db;
+
+	my $tx = $db->begin;
+
+	my $res = $db->select(
+		'pending_registrations',
+		'count(*) as count',
+		{
+			user_id => $uid,
+			token   => $token
+		}
+	);
+
+	if ( $res->hash->{count} ) {
+		$db->update( 'users', { status => 1 }, { id => $uid } );
+		$db->delete( 'pending_registrations', { user_id => $uid } );
+		$tx->commit;
+		return 1;
+	}
+	return;
+}
+
+sub get_uid_by_name_and_mail {
+	my ( $self, %opt ) = @_;
+	my $db = $opt{db} // $self->{pg}->db;
+	my $name = $opt{name};
+	my $email = $opt{email};
+
+	my $res = $db->select(
+		'users',
+		['id'],
+		{
+			name   => $name,
+			email  => $email,
+			status => 1
+		}
+	);
+
+	if ( my $user = $res->hash ) {
+		return $user->{id};
+	}
+	return;
+}
+
+sub get_privacy_by_name {
+	my ( $self, %opt ) = @_;
+	my $db = $opt{db} // $self->{pg}->db;
+	my $name = $opt{name};
+
+	my $res = $db->select(
+		'users',
+		[ 'id', 'public_level' ],
+		{
+			name   => $name,
+			status => 1
+		}
+	);
+
+	if ( my $user = $res->hash ) {
+		return $user;
+	}
+	return;
+}
+
+sub set_privacy {
+	my ( $self, %opt ) = @_;
+	my $db = $opt{db} // $self->{pg}->db;
+	my $uid = $opt{uid};
+	my $public_level = $opt{level};
+
+	$db->update(
+		'users',
+		{ public_level => $public_level },
+		{ id           => $uid }
+	);
+}
+
+sub mark_for_password_reset {
+	my ( $self, %opt ) = @_;
+	my $db = $opt{db} // $self->{pg}->db;
+	my $uid = $opt{uid};
+	my $token = $opt{token};
+
+	my $res = $db->select(
+		'pending_passwords',
+		'count(*) as count',
+		{ user_id => $uid }
+	);
+	if ( $res->hash->{count} ) {
+		return 'in progress';
+	}
+
+	$db->insert(
+		'pending_passwords',
+		{
+			user_id => $uid,
+			token   => $token,
+			requested_at =>
+				DateTime->now( time_zone => 'Europe/Berlin' )
+		}
+	);
+
+	return undef;
+}
+
+sub verify_password_token {
+	my ( $self, %opt ) = @_;
+	my $db = $opt{db} // $self->{pg}->db;
+	my $uid = $opt{uid};
+	my $token = $opt{token};
+
+	my $res = $db->select(
+		'pending_passwords',
+		'count(*) as count',
+		{
+			user_id => $uid,
+			token   => $token
+		}
+	);
+
+	if ( $res->hash->{count} ) {
+		return 1;
+	}
+	return;
+}
+
+sub mark_for_mail_change {
+	my ( $self, %opt ) = @_;
+	my $db = $opt{db} // $self->{pg}->db;
+	my $uid = $opt{uid};
+	my $email = $opt{email};
+	my $token = $opt{token};
+
+	$db->insert(
+		'pending_mails',
+		{
+			user_id => $uid,
+			email   => $email,
+			token   => $token,
+			requested_at =>
+				DateTime->now( time_zone => 'Europe/Berlin' )
+		},
+		{
+			on_conflict => \
+'(user_id) do update set email = EXCLUDED.email, token = EXCLUDED.token, requested_at = EXCLUDED.requested_at'
+		},
+	);
+}
+
+sub change_mail_with_token {
+	my ( $self, %opt ) = @_;
+	my $db = $opt{db} // $self->{pg}->db;
+	my $uid = $opt{uid};
+	my $token = $opt{token};
+
+	my $tx = $db->begin;
+
+	my $res_h = $db->select(
+		'pending_mails',
+		['email'],
+		{
+			user_id => $uid,
+			token   => $token
+		}
+	)->hash;
+
+	if ($res_h) {
+		$db->update(
+			'users',
+			{ email => $res_h->{email} },
+			{ id    => $uid }
+		);
+		$db->delete( 'pending_mails', { user_id => $uid } );
+		$tx->commit;
+		return 1;
+	}
+	return;
+}
+
+sub remove_password_token {
+	my ( $self, %opt ) = @_;
+	my $db = $opt{db} // $self->{pg}->db;
+	my $uid = $opt{uid};
+	my $token = $opt{token};
+
+	$db->delete(
+		'pending_passwords',
+		{
+			user_id => $uid,
+			token   => $token
+		}
+	);
+}
+
+sub get_data {
+	my ($self, %opt) = @_;
+	my $db = $opt{db} // $self->{pg}->db;
+	my $uid = $opt{uid};
+
+	my $user = $db->select(
+		'users',
+		'id, name, status, public_level, email, '
+			. 'extract(epoch from registered_at) as registered_at_ts, '
+			. 'extract(epoch from last_seen) as last_seen_ts, '
+			. 'extract(epoch from deletion_requested) as deletion_requested_ts',
+		{ id => $uid }
+	)->hash;
+	if ($user) {
+		return {
+			id            => $user->{id},
+			name          => $user->{name},
+			status        => $user->{status},
+			is_public     => $user->{public_level},
+			email         => $user->{email},
+			registered_at => DateTime->from_epoch(
+				epoch     => $user->{registered_at_ts},
+				time_zone => 'Europe/Berlin'
+			),
+			last_seen => DateTime->from_epoch(
+				epoch     => $user->{last_seen_ts},
+				time_zone => 'Europe/Berlin'
+			),
+			deletion_requested => $user->{deletion_requested_ts}
+			? DateTime->from_epoch(
+				epoch     => $user->{deletion_requested_ts},
+				time_zone => 'Europe/Berlin'
+				)
+			: undef,
+		};
+	}
+	return undef;
+}
+
+sub get_login_data {
+	my ( $self, %opt ) = @_;
+	my $db = $opt{db} // $self->{pg}->db;
+	my $name = $opt{name};
+
+	my $res_h = $db->select(
+		'users',
+		'id, name, status, password as password_hash',
+		{ name => $name }
+	)->hash;
+
+	return $res_h;
+}
+
+sub add_user {
+	my ( $self, %opt ) = @_;
+	my $db = $opt{db} // $self->{pg}->db;
+	my $user_name = $opt{name};
+	my $email = $opt{email};
+	my $token = $opt{token};
+	my $password = $opt{password_hash};
+
+	# This helper must be called during a transaction, as user creation
+	# may fail even after the database entry has been generated, e.g.  if
+	# the registration mail cannot be sent. We therefore use $db (the
+	# database handle performing the transaction) instead of $self->pg->db
+	# (which may be a new handle not belonging to the transaction).
+
+	my $now = DateTime->now( time_zone => 'Europe/Berlin' );
+
+	my $res = $db->insert(
+		'users',
+		{
+			name          => $user_name,
+			status        => 0,
+			public_level  => 0,
+			email         => $email,
+			password      => $password,
+			registered_at => $now,
+			last_seen     => $now,
+		},
+		{ returning => 'id' }
+	);
+	my $uid = $res->hash->{id};
+
+	$db->insert(
+		'pending_registrations',
+		{
+			user_id => $uid,
+			token   => $token
+		}
+	);
+
+	return $uid;
+}
+
+sub flag_deletion {
+	my ( $self, %opt ) = @_;
+	my $db = $opt{db} // $self->{pg}->db;
+	my $uid = $opt{uid};
+
+	my $now = DateTime->now( time_zone => 'Europe/Berlin' );
+
+	$db->update(
+		'users',
+		{ deletion_requested => $now },
+		{
+			id => $uid,
+		}
+	);
+}
+
+sub unflag_deletion {
+	my ( $self, %opt ) = @_;
+	my $db = $opt{db} // $self->{pg}->db;
+	my $uid = $opt{uid};
+
+	$db->update(
+		'users',
+		{
+			deletion_requested => undef,
+		},
+		{
+			id => $uid,
+		}
+	);
+}
+
+sub set_password_hash {
+	my ( $self, %opt ) = @_;
+	my $db = $opt{db} // $self->{pg}->db;
+	my $uid = $opt{uid};
+	my $password = $opt{password_hash};
+
+	$db->update(
+		'users',
+		{ password => $password },
+		{ id       => $uid }
+	);
+}
+
+sub check_if_user_name_exists {
+	my ( $self, %opt ) = @_;
+	my $db = $opt{db} // $self->{pg}->db;
+	my $user_name = $opt{name};
+
+	my $count = $db->select(
+		'users',
+		'count(*) as count',
+		{ name => $user_name }
+	)->hash->{count};
+
+	if ($count) {
+		return 1;
+	}
+	return 0;
+}
+
+sub check_if_mail_is_blacklisted {
+	my ( $self, %opt ) = @_;
+	my $db = $opt{db} // $self->{pg}->db;
+	my $mail = $opt{email};
+
+	my $count = $db->select(
+		'users',
+		'count(*) as count',
+		{
+			email  => $mail,
+			status => 0,
+		}
+	)->hash->{count};
+
+	if ($count) {
+		return 1;
+	}
+
+	$count = $db->select(
+		'mail_blacklist',
+		'count(*) as count',
+		{
+			email     => $mail,
+			num_tries => { '>', 1 },
+		}
+	)->hash->{count};
+
+	if ($count) {
+		return 1;
+	}
+	return 0;
+}
+
+sub use_history {
+	my ($self, %opt) = @_;
+	my $db = $opt{db} // $self->{pg}->db;
+	my $uid = $opt{uid};
+	my $value = $opt{set};
+
+	if ($value) {
+		$db->update(
+			'users',
+			{ use_history => $value },
+			{ id          => $uid }
+		);
+	}
+	else {
+		return $db->select( 'users', ['use_history'],
+			{ id => $uid } )->hash->{use_history};
+	}
+}
+
+1;
-- 
cgit v1.2.3