cacert-boardvoting/internal/models/users.go

/*
Copyright 2017-2022 CAcert Inc.
SPDX-License-Identifier: Apache-2.0

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

    http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
*/

package models

import (
	"context"
	"database/sql"
	"database/sql/driver"
	"errors"
	"fmt"
	"sort"
	"strings"
	"time"

	"github.com/jmoiron/sqlx"
	"golang.org/x/text/cases"
	"golang.org/x/text/language"
)

type Role struct {
	Name string `db:"role"`
}

func (r *Role) String() string {
	return cases.Title(language.BritishEnglish).String(strings.ToLower(r.Name))
}

func (r *Role) Scan(src any) error {
	value, ok := src.(string)
	if !ok {
		return fmt.Errorf("could not cast %v of %T to string", src, src)
	}

	*r = Role{Name: value}

	return nil
}

func (r *Role) Value() (driver.Value, error) {
	return r.Name, nil
}

type RoleName string

const (
	RoleAdmin     RoleName = "ADMIN"
	RoleSecretary RoleName = "SECRETARY"
	RoleVoter     RoleName = "VOTER"
)

var AllRoles = []*Role{
	{Name: string(RoleVoter)},
	{Name: string(RoleSecretary)},
	{Name: string(RoleAdmin)},
}

// The User type is used for mapping users from the voters table. The table
// has two columns that are obsolete:
//
// enabled -> replaced with user_roles where role is 'VOTER'
// reminder -> replaced with address from emails where reminder is true
//
// The columns cannot be dropped in SQLite without dropping and recreating
// the voters table and all foreign key indices pointing to that table.
type User struct {
	ID             int64          `db:"id"`
	Name           string         `db:"name"`
	Reminder       sql.NullString `db:"reminder"` // reminder email address
	roles          []*Role        `db:"-"`
	emailAddresses []string       `db:"-"`
	canDelete      bool           `db:"-"`
}

func (u *User) Roles() ([]*Role, error) {
	if u.roles != nil {
		return u.roles, nil
	}

	return nil, errors.New("WithRoles required")
}

func (u *User) EmailAddresses() ([]string, error) {
	if u.emailAddresses != nil {
		return u.emailAddresses, nil
	}

	return nil, errors.New("WithEmailAddressesOption required")
}

func (u *User) HasRole(roles ...RoleName) (bool, error) {
	userRoles, err := u.Roles()
	if err != nil {
		return false, err
	}

	roleMatched := false

outer:
	for _, role := range userRoles {
		for _, checkRole := range roles {
			if role.Name == string(checkRole) {
				roleMatched = true

				break outer
			}
		}
	}

	return roleMatched, nil
}

func (u *User) CanDelete() bool {
	return u.canDelete
}

func (u *User) rolesAndAddresses() ([]string, []string) {
	roleNames := make([]string, len(u.roles))

	for i := range u.roles {
		roleNames[i] = u.roles[i].Name
	}

	return roleNames, u.emailAddresses
}

func (u *User) rolesEqual(other *User) bool {
	roles1 := u.roles
	roles2 := other.roles

	if len(roles1) != len(roles2) {
		return false
	}

	sort.SliceStable(roles1, func(i, j int) bool { return roles1[i].Name < roles1[j].Name })
	sort.SliceStable(roles2, func(i, j int) bool { return roles2[i].Name < roles2[j].Name })

	for i, v := range roles1 {
		if v.Name != roles2[i].Name {
			return false
		}
	}

	return true
}

func (u *User) reminderEqual(other *User) bool {
	return u.Reminder.Valid == other.Reminder.Valid && u.Reminder.String == other.Reminder.String
}

type UserModel struct {
	DB *sqlx.DB
}

func (m *UserModel) ReminderVoters(ctx context.Context) ([]*User, error) {
	rows, err := m.DB.QueryxContext(
		ctx,
		`SELECT v.id, v.name, e.address AS reminder
FROM voters v
         JOIN emails e ON v.id = e.voter
         JOIN user_roles ur ON v.id = ur.voter_id
WHERE ur.role = ?
  AND e.reminder = TRUE`,
		RoleVoter,
	)
	if err != nil {
		return nil, fmt.Errorf("could not run query: %w", err)
	}

	defer func() { _ = rows.Close() }()

	result := make([]*User, 0)

	for rows.Next() {
		if err := rows.Err(); err != nil {
			return nil, fmt.Errorf("could not fetch row: %w", err)
		}

		var user User

		if err := rows.StructScan(&user); err != nil {
			return nil, fmt.Errorf("could not scan row: %w", err)
		}

		result = append(result, &user)
	}

	return result, nil
}

func (m *UserModel) ByEmails(ctx context.Context, emails []string) (*User, error) {
	for i := range emails {
		emails[i] = strings.ToLower(emails[i])
	}

	query, args, err := sqlx.In(
		`WITH reminders AS (SELECT voter, address
                   FROM emails
                   WHERE reminder = TRUE)
SELECT DISTINCT v.id, v.name, reminders.address AS reminder
FROM voters v
         JOIN emails e ON e.voter = v.id
         LEFT JOIN reminders ON v.id = reminders.voter
WHERE e.address IN (?)`, emails)
	if err != nil {
		return nil, fmt.Errorf("could not build query: %w", err)
	}

	rows, err := m.DB.QueryxContext(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("could not run query: %w", err)
	}

	defer func() { _ = rows.Close() }()

	var (
		user  User
		count int
	)

	for rows.Next() {
		count++

		if count > 1 {
			return nil, fmt.Errorf(
				"multiple voters found for addresses in certificate %s",
				strings.Join(emails, ", "),
			)
		}

		if err := rows.Err(); err != nil {
			return nil, fmt.Errorf("could not fetch row: %w", err)
		}

		if err := rows.StructScan(&user); err != nil {
			return nil, fmt.Errorf("could not get user from row: %w", err)
		}
	}

	if user.roles, err = m.Roles(ctx, &user); err != nil {
		return nil, fmt.Errorf("could not retrieve roles for user %s: %w", user.Name, err)
	}

	return &user, nil
}

func (m *UserModel) Roles(ctx context.Context, user *User) ([]*Role, error) {
	rows, err := m.DB.QueryxContext(ctx, `SELECT role FROM user_roles WHERE voter_id=?`, user.ID)
	if err != nil {
		return nil, fmt.Errorf("could not query roles for %s: %w", user.Name, err)
	}

	defer func() { _ = rows.Close() }()

	result := make([]*Role, 0)

	for rows.Next() {
		if err := rows.Err(); err != nil {
			return nil, fmt.Errorf("could not retrieve row: %w", err)
		}

		var role Role

		if err := rows.StructScan(&role); err != nil {
			return nil, fmt.Errorf("could not get role from row: %w", err)
		}

		result = append(result, &role)
	}

	return result, nil
}

type CreateUserParams struct {
	Admin     *User
	Name      string
	Reminder  string
	Emails    []string
	Reasoning string
}

func (m *UserModel) Create(ctx context.Context, params *CreateUserParams) (int64, error) {
	tx, err := m.DB.BeginTxx(ctx, nil)
	if err != nil {
		return 0, fmt.Errorf("could not start transaction: %w", err)
	}

	defer func(tx *sqlx.Tx) {
		_ = tx.Rollback()
	}(tx)

	res, err := tx.ExecContext(
		ctx,
		`INSERT INTO voters (name, enabled) VALUES (?, 0)`,
		params.Name)
	if err != nil {
		return 0, fmt.Errorf("could not insert user: %w", err)
	}

	userID, err := res.LastInsertId()
	if err != nil {
		return 0, fmt.Errorf("could not get user id: %w", err)
	}

	for i := range params.Emails {
		_, err := tx.ExecContext(
			ctx,
			`INSERT INTO emails (voter, address, reminder)
VALUES (?, ?, ?)`,
			userID,
			params.Emails[i],
			params.Emails[i] == params.Reminder,
		)
		if err != nil {
			return 0, fmt.Errorf("could not insert email %s for voter %s: %w", params.Emails[i], params.Name, err)
		}
	}

	if err := AuditLog(ctx, tx, params.Admin, AuditCreateUser, params.Reasoning, userCreateInfo(&User{
		Name: params.Name,
		Reminder: sql.NullString{
			String: params.Reminder,
			Valid:  true,
		},
		roles:          []*Role{},
		emailAddresses: params.Emails,
	})); err != nil {
		return 0, err
	}

	if err := tx.Commit(); err != nil {
		return 0, fmt.Errorf("could not commit user transaction: %w", err)
	}

	return userID, nil
}

func (m *UserModel) InRole(ctx context.Context, role RoleName) ([]*User, error) {
	rows, err := m.DB.QueryxContext(
		ctx,
		`SELECT voters.id, voters.name
FROM voters
         JOIN user_roles ur ON voters.id = ur.voter_id
WHERE ur.role = ?
ORDER BY voters.name`,
		role,
	)

	if err != nil {
		return nil, fmt.Errorf("could not execute query: %w", err)
	}

	defer func() { _ = rows.Close() }()

	result := make([]*User, 0)

	for rows.Next() {
		if err := rows.Err(); err != nil {
			return nil, fmt.Errorf("could not get row: %w", err)
		}

		var user User

		if err := rows.StructScan(&user); err != nil {
			return nil, fmt.Errorf("could not scan row: %w", err)
		}

		result = append(result, &user)
	}

	return result, nil
}

func (m *UserModel) Voters(ctx context.Context) ([]*User, error) {
	return m.InRole(ctx, RoleVoter)
}

func (m *UserModel) ByID(ctx context.Context, voterID int64, options ...UserListOption) (*User, error) {
	row := m.DB.QueryRowxContext(
		ctx,
		`SELECT DISTINCT v.id, v.name, e.address AS reminder
FROM voters v
         LEFT OUTER JOIN emails e ON e.voter = v.id AND e.reminder = TRUE
WHERE v.id = ?
`,
		voterID,
	)

	if err := row.Err(); err != nil {
		return nil, fmt.Errorf("could not execute query: %w", err)
	}

	var user User

	if err := row.StructScan(&user); err != nil {
		return nil, fmt.Errorf("could not scan row: %w", err)
	}

	for _, option := range options {
		if err := option(ctx, []*User{&user}); err != nil {
			return nil, err
		}
	}

	return &user, nil
}

type UserListOption func(context.Context, []*User) error

func (m *UserModel) List(ctx context.Context, options ...UserListOption) ([]*User, error) {
	rows, err := m.DB.QueryxContext(ctx, `SELECT id, name FROM voters ORDER BY name`)
	if err != nil {
		return nil, errCouldNotExecuteQuery(err)
	}

	defer func() { _ = rows.Close() }()

	users := make([]*User, 0)

	for rows.Next() {
		if err = rows.Err(); err != nil {
			return nil, errCouldNotFetchRow(err)
		}

		var user User
		if err = rows.StructScan(&user); err != nil {
			return nil, errCouldNotScanResult(err)
		}

		users = append(users, &user)
	}

	for _, option := range options {
		if err = option(ctx, users); err != nil {
			return nil, err
		}
	}

	return users, nil
}

func (m *UserModel) WithEmailAddresses() UserListOption {
	return func(ctx context.Context, users []*User) error {
		if len(users) == 0 {
			return nil
		}

		userIDs := userIDsFromUsers(users)

		query, args, err := sqlx.In(
			`SELECT voter, address FROM emails WHERE voter IN (?) ORDER BY address`,
			userIDs,
		)
		if err != nil {
			return errCouldNotCreateInQuery(err)
		}

		rows, err := m.DB.QueryxContext(ctx, query, args...)
		if err != nil {
			return errCouldNotExecuteQuery(err)
		}

		defer func(rows *sqlx.Rows) {
			_ = rows.Close()
		}(rows)

		addressMap, err := buildAddressMap(rows, len(users))
		if err != nil {
			return err
		}

		for idx := range users {
			if addresses, ok := addressMap[users[idx].ID]; ok {
				users[idx].emailAddresses = addresses

				continue
			}

			users[idx].emailAddresses = make([]string, 0)
		}

		return nil
	}
}

func buildAddressMap(rows *sqlx.Rows, userCount int) (map[int64][]string, error) {
	addressMap := make(map[int64][]string, userCount)

	for rows.Next() {
		if err := rows.Err(); err != nil {
			return nil, errCouldNotFetchRow(err)
		}

		var (
			userID  int64
			address string
		)

		if err := rows.Scan(&userID, &address); err != nil {
			return nil, errCouldNotScanResult(err)
		}

		if _, ok := addressMap[userID]; !ok {
			addressMap[userID] = []string{address}
		} else {
			addressMap[userID] = append(addressMap[userID], address)
		}
	}

	return addressMap, nil
}

func (m *UserModel) WithRoles() UserListOption {
	return func(ctx context.Context, users []*User) error {
		if len(users) == 0 {
			return nil
		}

		userIDs := userIDsFromUsers(users)

		query, args, err := sqlx.In(
			`SELECT voter_id, role FROM user_roles WHERE voter_id IN (?)`,
			userIDs,
		)
		if err != nil {
			return errCouldNotCreateInQuery(err)
		}

		rows, err := m.DB.QueryxContext(ctx, query, args...)
		if err != nil {
			return errCouldNotExecuteQuery(err)
		}

		defer func(rows *sqlx.Rows) {
			_ = rows.Close()
		}(rows)

		roleMap, err := buildRoleMap(rows, len(users))
		if err != nil {
			return err
		}

		for idx := range users {
			if roles, ok := roleMap[users[idx].ID]; ok {
				users[idx].roles = roles

				continue
			}

			users[idx].roles = make([]*Role, 0)
		}

		return nil
	}
}

func userIDsFromUsers(users []*User) []int64 {
	userIDs := make([]int64, len(users))
	for idx := range users {
		userIDs[idx] = users[idx].ID
	}

	return userIDs
}

func buildRoleMap(rows *sqlx.Rows, userCount int) (map[int64][]*Role, error) {
	roleMap := make(map[int64][]*Role, userCount)

	for rows.Next() {
		if err := rows.Err(); err != nil {
			return nil, errCouldNotFetchRow(err)
		}

		var (
			userID int64
			role   Role
		)

		if err := rows.Scan(&userID, &role); err != nil {
			return nil, errCouldNotScanResult(err)
		}

		if _, ok := roleMap[userID]; !ok {
			roleMap[userID] = []*Role{&role}
		} else {
			roleMap[userID] = append(roleMap[userID], &role)
		}
	}

	return roleMap, nil
}

func (m *UserModel) CanDelete() UserListOption {
	return func(ctx context.Context, users []*User) error {
		if len(users) == 0 {
			return nil
		}

		userIDs := make([]int64, len(users))
		for idx := range users {
			userIDs[idx] = users[idx].ID
		}

		query, args, err := sqlx.In(
			`SELECT id
FROM voters
WHERE id IN (?)
  AND NOT EXISTS(SELECT * FROM decisions WHERE proponent = voters.id)
  AND NOT EXISTS(SELECT * FROM votes WHERE voter = voters.id)
  AND NOT EXISTS(SELECT * FROM user_roles WHERE voter_id = voters.id)`,
			userIDs,
		)
		if err != nil {
			return errCouldNotCreateInQuery(err)
		}

		rows, err := m.DB.QueryxContext(ctx, query, args...)
		if err != nil {
			return errCouldNotExecuteQuery(err)
		}

		defer func() { _ = rows.Close() }()

		for rows.Next() {
			if err := rows.Err(); err != nil {
				return errCouldNotFetchRow(err)
			}

			var userID int64

			if err := rows.Scan(&userID); err != nil {
				return errCouldNotScanResult(err)
			}

			for idx := range users {
				if userID == users[idx].ID {
					users[idx].canDelete = true
				}
			}
		}

		return nil
	}
}

func (m *UserModel) DeleteUser(ctx context.Context, userToDelete, admin *User, reasoning string) error {
	userInfo := struct {
		Name    string `json:"user"`
		Address string `json:"address"`
	}{Name: userToDelete.Name, Address: userToDelete.Reminder.String}
	details := struct {
		User any `json:"user"`
	}{User: userInfo}

	tx, err := m.DB.BeginTxx(ctx, nil)
	if err != nil {
		return errCouldNotStartTransaction(err)
	}

	defer func(tx *sqlx.Tx) {
		_ = tx.Rollback()
	}(tx)

	if _, err = tx.ExecContext(ctx, `DELETE FROM emails WHERE voter=?`, userToDelete.ID); err != nil {
		return errCouldNotExecuteQuery(err)
	}

	if _, err = tx.ExecContext(ctx, `DELETE FROM voters WHERE id=?`, userToDelete.ID); err != nil {
		return errCouldNotExecuteQuery(err)
	}

	if err = AuditLog(ctx, tx, admin, AuditDeleteUser, reasoning, details); err != nil {
		return err
	}

	if err = tx.Commit(); err != nil {
		return errCouldNotCommitTransaction(err)
	}

	return nil
}

func (m *UserModel) EditUser(ctx context.Context, edit *User, admin *User, roles []string, reasoning string) error {
	newRoles := make([]*Role, len(roles))
	for i := range roles {
		newRoles[i] = &Role{Name: roles[i]}
	}

	edit.roles = newRoles

	userBefore, err := m.ByID(ctx, edit.ID, m.WithRoles(), m.WithEmailAddresses())
	if err != nil {
		return err
	}

	tx, err := m.DB.BeginTxx(ctx, nil)
	if err != nil {
		return errCouldNotStartTransaction(err)
	}

	defer func(tx *sqlx.Tx) {
		_ = tx.Rollback()
	}(tx)

	err = m.updateChangedUser(ctx, edit, userBefore, tx)
	if err != nil {
		return err
	}

	if err = AuditLog(ctx, tx, admin, AuditEditUser, reasoning, userChangeInfo(userBefore, edit)); err != nil {
		return err
	}

	if err = tx.Commit(); err != nil {
		return errCouldNotCommitTransaction(err)
	}

	return nil
}

func (m *UserModel) updateChangedUser(ctx context.Context, edit, userBefore *User, tx *sqlx.Tx) error {
	if edit.Name != userBefore.Name {
		if _, err := tx.NamedExecContext(ctx, `UPDATE voters SET name=:name WHERE id=:id`, edit); err != nil {
			return errCouldNotExecuteQuery(err)
		}
	}

	if !userBefore.rolesEqual(edit) {
		if err := updateRoles(ctx, tx, edit); err != nil {
			return err
		}
	}

	if !userBefore.reminderEqual(edit) {
		if err := updateReminder(ctx, tx, edit); err != nil {
			return err
		}
	}

	return nil
}

func (m *UserModel) EmailExists(ctx context.Context, address string) (bool, error) {
	row := m.DB.QueryRowxContext(ctx, `SELECT EXISTS(SELECT * FROM emails WHERE address=?)`, address)

	if err := row.Err(); err != nil {
		return false, errCouldNotExecuteQuery(err)
	}

	var exists bool

	if err := row.Scan(&exists); err != nil {
		return false, errCouldNotScanResult(err)
	}

	return exists, nil
}

func (m *UserModel) AddEmail(ctx context.Context, user, admin *User, emailAddress, reasoning string) error {
	userBefore, err := m.ByID(ctx, user.ID, m.WithEmailAddresses())
	if err != nil {
		return err
	}

	tx, err := m.DB.BeginTxx(ctx, nil)
	if err != nil {
		return errCouldNotStartTransaction(err)
	}

	defer func(tx *sqlx.Tx) {
		_ = tx.Rollback()
	}(tx)

	if _, err := tx.ExecContext(ctx, `INSERT INTO emails (voter, address) VALUES (?, ?)`,
		user.ID, emailAddress); err != nil {
		return errCouldNotExecuteQuery(err)
	}

	if err = AuditLog(
		ctx, tx, admin, AuditAddEmail, reasoning,
		emailChangeInfo(userBefore.emailAddresses, append(userBefore.emailAddresses, emailAddress)),
	); err != nil {
		return err
	}

	if err = tx.Commit(); err != nil {
		return errCouldNotCommitTransaction(err)
	}

	return nil
}

func (m *UserModel) DeleteEmail(ctx context.Context, user, admin *User, emailAddress, reasoning string) error {
	userBefore, err := m.ByID(ctx, user.ID, m.WithEmailAddresses())
	if err != nil {
		return err
	}

	tx, err := m.DB.BeginTxx(ctx, nil)
	if err != nil {
		return errCouldNotStartTransaction(err)
	}

	defer func(tx *sqlx.Tx) {
		_ = tx.Rollback()
	}(tx)

	if _, err := tx.ExecContext(
		ctx,
		`DELETE FROM emails WHERE reminder=FALSE AND address=? AND voter=?`,
		emailAddress,
		user.ID,
	); err != nil {
		return errCouldNotExecuteQuery(err)
	}

	rows, err := tx.QueryxContext(ctx, `SELECT address FROM emails WHERE voter=? ORDER BY address`, user.ID)
	if err != nil {
		return errCouldNotExecuteQuery(err)
	}

	defer func() { _ = rows.Close() }()

	emailAddresses := make([]string, 0, len(userBefore.emailAddresses)-1)

	for rows.Next() {
		if err := rows.Err(); err != nil {
			return errCouldNotFetchRow(err)
		}

		var address string

		if err := rows.Scan(&address); err != nil {
			return errCouldNotScanResult(err)
		}

		emailAddresses = append(emailAddresses, address)
	}

	if err = AuditLog(
		ctx, tx, admin, AuditDeleteEmail, reasoning,
		emailChangeInfo(userBefore.emailAddresses, emailAddresses),
	); err != nil {
		return err
	}

	if err = tx.Commit(); err != nil {
		return errCouldNotCommitTransaction(err)
	}

	return nil
}

func updateReminder(ctx context.Context, tx *sqlx.Tx, edit *User) error {
	if _, err := tx.ExecContext(
		ctx,
		`UPDATE emails SET reminder=TRUE WHERE address=? AND voter=?`,
		edit.Reminder.String,
		edit.ID,
	); err != nil {
		return errCouldNotExecuteQuery(err)
	}

	if _, err := tx.ExecContext(
		ctx,
		`UPDATE emails SET reminder=FALSE WHERE address<>? AND voter=?`,
		edit.Reminder.String,
		edit.ID,
	); err != nil {
		return errCouldNotExecuteQuery(err)
	}

	return nil
}

func updateRoles(ctx context.Context, tx *sqlx.Tx, user *User) error {
	if len(user.roles) == 0 {
		if _, err := tx.ExecContext(ctx, `DELETE FROM user_roles WHERE voter_id=?`, user.ID); err != nil {
			return errCouldNotExecuteQuery(err)
		}

		return nil
	}

	query, args, err := sqlx.In(
		`DELETE FROM user_roles WHERE role NOT IN (?) AND voter_id=?`,
		user.roles,
		user.ID,
	)
	if err != nil {
		return errCouldNotCreateInQuery(err)
	}

	if _, err = tx.ExecContext(ctx, query, args...); err != nil {
		return errCouldNotExecuteQuery(err)
	}

	for idx := range user.roles {
		if _, err = tx.ExecContext(
			ctx,
			`INSERT INTO user_roles (voter_id, role, created)
SELECT ?, ?, ?
WHERE NOT EXISTS(SELECT * FROM user_roles WHERE voter_id = ? AND role = ?)`,
			user.ID,
			user.roles[idx],
			time.Now().UTC(),
			user.ID,
			user.roles[idx],
		); err != nil {
			return errCouldNotExecuteQuery(err)
		}
	}

	return nil
}

func (m *UserModel) ChooseVoters(ctx context.Context, admin *User, voterIDs []int64, reasoning string) error {
	tx, err := m.DB.BeginTxx(ctx, nil)
	if err != nil {
		return errCouldNotStartTransaction(err)
	}

	defer func(tx *sqlx.Tx) {
		_ = tx.Rollback()
	}(tx)

	votersBefore, err := allVoterNames(ctx, tx)
	if err != nil {
		return err
	}

	query, args, err := sqlx.In(
		`DELETE FROM user_roles WHERE voter_id NOT IN (?) AND role=?`,
		voterIDs, RoleVoter,
	)
	if err != nil {
		return errCouldNotCreateInQuery(err)
	}

	if _, err := tx.ExecContext(ctx, query, args...); err != nil {
		return errCouldNotExecuteQuery(err)
	}

	query, args, err = sqlx.In(`INSERT INTO user_roles (voter_id, role, created)
SELECT v.id, ?, ?
FROM voters v
WHERE id IN (?) AND NOT EXISTS(SELECT * FROM user_roles WHERE role = ? AND voter_id = v.id)`,
		RoleVoter, time.Now().UTC(), voterIDs, RoleVoter)
	if err != nil {
		return errCouldNotCreateInQuery(err)
	}

	if _, err := tx.ExecContext(ctx, query, args...); err != nil {
		return errCouldNotExecuteQuery(err)
	}

	votersAfter, err := allVoterNames(ctx, tx)
	if err != nil {
		return err
	}

	if err := AuditLog(
		ctx,
		tx,
		admin,
		AuditChangeVoters,
		reasoning,
		voterChangeInfo(votersBefore, votersAfter),
	); err != nil {
		return err
	}

	if err := tx.Commit(); err != nil {
		return errCouldNotCommitTransaction(err)
	}

	return nil
}

func allVoterNames(ctx context.Context, tx *sqlx.Tx) ([]string, error) {
	rows, err := tx.QueryxContext(ctx, `SELECT name
FROM voters
         JOIN user_roles ur ON voters.id = ur.voter_id
WHERE role = ?
ORDER BY name`, RoleVoter)
	if err != nil {
		return nil, errCouldNotExecuteQuery(err)
	}

	defer func() { _ = rows.Close() }()

	names := make([]string, 0)

	for rows.Next() {
		if err := rows.Err(); err != nil {
			return nil, errCouldNotFetchRow(err)
		}

		var name string

		if err := rows.Scan(&name); err != nil {
			return nil, errCouldNotScanResult(err)
		}

		names = append(names, name)
	}

	return names, nil
}