<?php
/**
 * Points, badges, levels and the leaderboard.
 *
 * @package SpaceBay
 */

namespace SpaceBay\Gamification;

use SpaceBay\Core\Settings;
use SpaceBay\Database\Tables;
use SpaceBay\Feed\Comments;
use SpaceBay\Feed\Posts;
use SpaceBay\Members\MemberDirectory;
use SpaceBay\Members\Profiles;
use SpaceBay\Notifications\Notifications;
use SpaceBay\Spaces\SpaceAccess;
use SpaceBay\Support\Cache;

defined( 'ABSPATH' ) || exit;

/**
 * Listens to community activity and turns it into points and badges.
 */
final class Gamification {

	/**
	 * Option set once past activity has been turned into points.
	 *
	 * @var string
	 */
	const BACKFILL_OPTION = 'spacebay_points_backfilled';

	/**
	 * User option holding the last day a member got points for a visit.
	 *
	 * @var string
	 */
	const VISIT_OPTION = 'spacebay_visit_day';

	/**
	 * Hooks community activity.
	 *
	 * @return void
	 */
	public static function init() {
		add_action( 'spacebay_post_created', array( __CLASS__, 'on_post_created' ), 20 );
		add_action( 'spacebay_comment_created', array( __CLASS__, 'on_comment_created' ), 20 );
		add_action( 'spacebay_reaction_added', array( __CLASS__, 'on_reaction_added' ), 20, 4 );
		add_action( 'spacebay_reaction_removed', array( __CLASS__, 'on_reaction_removed' ), 20, 4 );
		add_action( 'spacebay_post_deleted', array( __CLASS__, 'on_post_deleted' ) );
		add_action( 'spacebay_comment_deleted', array( __CLASS__, 'on_comment_deleted' ), 10, 2 );
		add_action( 'spacebay_points_added', array( __CLASS__, 'on_points_added' ), 10, 5 );
		add_action( 'spacebay_badge_awarded', array( __CLASS__, 'on_badge_awarded' ), 10, 5 );
		add_action( 'spacebay_installed', array( __CLASS__, 'backfill' ) );
		// After posts and comments are removed (priority 10), which also takes their points away.
		add_action( 'deleted_user', array( __CLASS__, 'delete_user_data' ), 20 );
		add_action( 'remove_user_from_blog', array( __CLASS__, 'delete_user_data' ), 20 );
		add_filter( 'rest_pre_dispatch', array( __CLASS__, 'track_visit' ), 20, 3 );
	}

	/**
	 * Whether points and badges are switched on.
	 *
	 * @return bool
	 */
	public static function enabled() {
		return (bool) Settings::get( 'gamification', true );
	}

	/**
	 * Whether members can see the leaderboard.
	 *
	 * @return bool
	 */
	public static function leaderboard_enabled() {
		return self::enabled() && (bool) Settings::get( 'leaderboard', true );
	}

	/**
	 * Points for one kind of action, from the settings.
	 *
	 * @param string $reason "post", "comment", "like", "daily" or "event".
	 * @return int
	 */
	public static function value( $reason ) {
		return (int) Settings::get( 'points_' . $reason, 0 );
	}

	/**
	 * Gives points for an action when points are on.
	 *
	 * @param int    $user_id     Member.
	 * @param string $reason      Reason.
	 * @param string $ref         Reference.
	 * @param string $object_type Object type.
	 * @param int    $object_id   Object ID.
	 * @return bool
	 */
	public static function earn( $user_id, $reason, $ref, $object_type = '', $object_id = 0 ) {
		if ( ! self::enabled() || ! $user_id || SpaceAccess::is_restricted( $user_id ) ) {
			return false;
		}

		return Points::add( $user_id, self::value( $reason ), $reason, $ref, $object_type, $object_id );
	}

	/**
	 * Points for a published post.
	 *
	 * @param array $post Post row.
	 * @return void
	 */
	public static function on_post_created( $post ) {
		if ( 'published' === ( $post['status'] ?? 'published' ) ) {
			self::earn( (int) $post['user_id'], 'post', 'post:' . (int) $post['id'], 'post', (int) $post['id'] );
		}
	}

	/**
	 * Points for a published comment.
	 *
	 * @param array $comment Comment row.
	 * @return void
	 */
	public static function on_comment_created( $comment ) {
		if ( 'published' === ( $comment['status'] ?? 'published' ) ) {
			self::earn( (int) $comment['user_id'], 'comment', 'comment:' . (int) $comment['id'], 'comment', (int) $comment['id'] );
		}
	}

	/**
	 * Points for the author when someone else likes their post or comment.
	 *
	 * @param string $object_type "post" or "comment".
	 * @param int    $object_id   Object ID.
	 * @param int    $user_id     Member who liked.
	 * @param string $type        Reaction type.
	 * @return void
	 */
	public static function on_reaction_added( $object_type, $object_id, $user_id, $type ) {
		if ( 'like' !== $type ) {
			return;
		}

		$author = self::author_of( $object_type, $object_id );

		if ( $author && $author !== (int) $user_id ) {
			self::earn( $author, 'like', self::like_ref( $object_type, $object_id, $user_id ), $object_type, $object_id );
		}
	}

	/**
	 * Takes back the points of a like that was undone.
	 *
	 * @param string $object_type "post" or "comment".
	 * @param int    $object_id   Object ID.
	 * @param int    $user_id     Member who liked.
	 * @param string $type        Reaction type.
	 * @return void
	 */
	public static function on_reaction_removed( $object_type, $object_id, $user_id, $type ) {
		if ( 'like' !== $type ) {
			return;
		}

		$author = self::author_of( $object_type, $object_id );

		if ( $author ) {
			Points::remove( $author, self::like_ref( $object_type, $object_id, $user_id ) );
		}
	}

	/**
	 * Takes back the points of a deleted post and of the likes it got.
	 *
	 * @param array $post Deleted post row.
	 * @return void
	 */
	public static function on_post_deleted( $post ) {
		Points::remove( (int) $post['user_id'], 'post:' . (int) $post['id'] );
		Points::remove_prefix( 'like:post:' . (int) $post['id'] . ':' );
	}

	/**
	 * Takes back the points of deleted comments and of the likes they got.
	 *
	 * @param array $comment Deleted comment row.
	 * @param int[] $ids     Every deleted comment ID.
	 * @return void
	 */
	public static function on_comment_deleted( $comment, $ids = array() ) {
		$ids = $ids ? (array) $ids : array( (int) $comment['id'] );

		foreach ( $ids as $id ) {
			Points::remove_ref( 'comment:' . (int) $id );
			Points::remove_prefix( 'like:comment:' . (int) $id . ':' );
		}
	}

	/**
	 * One point a day for opening the community.
	 *
	 * @param mixed            $result  Response to replace the request with, or null.
	 * @param \WP_REST_Server  $server  Server.
	 * @param \WP_REST_Request $request Request.
	 * @return mixed Unchanged $result.
	 */
	public static function track_visit( $result, $server, $request ) {
		unset( $server );

		$user_id = get_current_user_id();

		if ( ! $user_id || ! self::enabled() || 0 !== strpos( $request->get_route(), '/spacebay/' ) ) {
			return $result;
		}

		$today = wp_date( 'Y-m-d' );

		if ( get_user_option( self::VISIT_OPTION, $user_id ) === $today || ( is_multisite() && ! is_user_member_of_blog( $user_id ) ) ) {
			return $result;
		}

		update_user_option( $user_id, self::VISIT_OPTION, $today );
		self::earn( $user_id, 'daily', 'daily:' . $today );

		return $result;
	}

	/**
	 * After points change: check badges and tell the member about a new level.
	 *
	 * @param int    $user_id Member.
	 * @param int    $points  Points of this action.
	 * @param string $reason  Reason.
	 * @param int    $before  Total before.
	 * @param int    $after   Total after.
	 * @return void
	 */
	public static function on_points_added( $user_id, $points, $reason, $before, $after ) {
		unset( $points, $reason );

		if ( ! self::enabled() ) {
			return;
		}

		Badges::check( $user_id );

		$old = Levels::for_points( $before );
		$new = Levels::for_points( $after );

		if ( $new['number'] > $old['number'] ) {
			Notifications::add(
				$user_id,
				0,
				'level_up',
				'member',
				$new['number'],
				array( 'level' => $new['name'] ),
				'members/' . (int) $user_id
			);
		}
	}

	/**
	 * Tells a member about a new badge.
	 *
	 * @param int    $user_id    Member.
	 * @param string $key        Badge key.
	 * @param int    $awarded_by Team member who gave it.
	 * @param int    $row_id     Award ID.
	 * @param bool   $notify     Whether to notify.
	 * @return void
	 */
	public static function on_badge_awarded( $user_id, $key, $awarded_by, $row_id, $notify ) {
		$badges = Badges::all();

		if ( ! $notify || ! isset( $badges[ $key ] ) ) {
			return;
		}

		Notifications::add(
			$user_id,
			(int) $awarded_by,
			'badge_earned',
			'badge',
			$row_id,
			array(
				'badge' => $badges[ $key ]['name'],
				'given' => $awarded_by > 0,
			),
			'members/' . (int) $user_id
		);
	}

	/**
	 * Points, level and badges of a member, as shown on their profile.
	 *
	 * @param int $user_id   Member.
	 * @param int $viewer_id Viewer.
	 * @return array
	 */
	public static function summary( $user_id, $viewer_id ) {
		$points  = Points::total( $user_id );
		$is_self = (int) $user_id === (int) $viewer_id;

		// Earlier activity may have reached a badge before badges existed.
		if ( $is_self ) {
			Badges::check( $user_id, false );
		}

		$summary = array(
			'points'      => $points,
			'level'       => Levels::for_points( $points ),
			'levels'      => Levels::all(),
			'badges'      => $is_self ? Badges::progress( $user_id ) : Badges::of_user( $user_id ),
			'rank'        => self::leaderboard_enabled() ? self::rank( $user_id, 'all' ) : null,
			'is_self'     => $is_self,
			'leaderboard' => self::leaderboard_enabled(),
		);

		/**
		 * Filters the points, level and badges shown on a profile. SpaceBay
		 * Pro adds what the team needs to give badges and points.
		 *
		 * @since 1.0.0
		 *
		 * @param array $summary   Summary.
		 * @param int   $user_id   Member.
		 * @param int   $viewer_id Viewer.
		 */
		return apply_filters( 'spacebay_achievements', $summary, $user_id, $viewer_id );
	}

	/**
	 * First moment of a leaderboard period, as a UTC datetime.
	 *
	 * @param string $period "week", "month" or "all".
	 * @return string Empty for "all".
	 */
	public static function period_start( $period ) {
		if ( 'all' === $period ) {
			return '';
		}

		$now = new \DateTimeImmutable( 'now', wp_timezone() );

		if ( 'month' === $period ) {
			$start = $now->setDate( (int) $now->format( 'Y' ), (int) $now->format( 'n' ), 1 )->setTime( 0, 0 );
		} else {
			$first = (int) get_option( 'start_of_week', 1 );
			$back  = ( (int) $now->format( 'w' ) - $first + 7 ) % 7;
			$start = $now->setTime( 0, 0 )->modify( '-' . $back . ' days' );
		}

		return $start->setTimezone( new \DateTimeZone( 'UTC' ) )->format( 'Y-m-d H:i:s' );
	}

	/**
	 * Members who are left out of the leaderboard: hidden profiles and
	 * accounts that wait for approval or are suspended.
	 *
	 * @return int[]
	 */
	private static function left_out() {
		return array_values( array_unique( array_merge( Profiles::hidden_ids(), Profiles::inactive_ids() ) ) );
	}

	/**
	 * How many members a stored weekly or monthly board keeps.
	 *
	 * @var int
	 */
	const BOARD_ROWS = 1000;

	/**
	 * Points per member since the start of a week or month, highest first.
	 *
	 * Adding up the ledger for a period is the slowest query of the plugin on
	 * a busy site, and the Home page asks for it on every visit. So the result
	 * is kept for a few minutes: boards show points earned up to that long ago.
	 *
	 * @param string $period "week" or "month".
	 * @return array{rows: array<int, int>, complete: bool} Member ID => points, and whether every member with points is in the list.
	 */
	private static function board( $period ) {
		global $wpdb;

		$period = 'month' === $period ? 'month' : 'week';
		$since  = self::period_start( $period );

		/**
		 * Filters how long weekly and monthly leaderboards are kept before they
		 * are added up again. Return 0 to add them up on every request.
		 *
		 * @since 1.0.0
		 *
		 * @param int    $seconds Seconds, 300 by default.
		 * @param string $period  "week" or "month".
		 */
		$ttl    = (int) apply_filters( 'spacebay_leaderboard_cache_seconds', 5 * MINUTE_IN_SECONDS, $period );
		$name   = 'spacebay_board_' . $period;
		$stored = $ttl > 0 ? get_transient( $name ) : false;

		// A board saved before the week or month changed belongs to the old period.
		if ( is_array( $stored ) && isset( $stored['since'], $stored['rows'], $stored['complete'] ) && $stored['since'] === $since ) {
			return array(
				'rows'     => $stored['rows'],
				'complete' => (bool) $stored['complete'],
			);
		}

		$found = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Indexed range (created_user_points); kept in a transient above.
			$wpdb->prepare( 'SELECT user_id, SUM(points) AS points FROM %i WHERE created_at >= %s GROUP BY user_id HAVING points > 0 ORDER BY points DESC, user_id ASC LIMIT %d', Tables::name( 'points' ), $since, self::BOARD_ROWS + 1 ),
			ARRAY_A
		);

		$rows = array();

		foreach ( array_slice( (array) $found, 0, self::BOARD_ROWS ) as $row ) {
			$rows[ (int) $row['user_id'] ] = (int) $row['points'];
		}

		$board = array(
			'rows'     => $rows,
			'complete' => count( (array) $found ) <= self::BOARD_ROWS,
		);

		if ( $ttl > 0 ) {
			set_transient(
				$name,
				array(
					'since'    => $since,
					'rows'     => $board['rows'],
					'complete' => $board['complete'],
				),
				$ttl
			);
		}

		return $board;
	}

	/**
	 * Top members for a period.
	 *
	 * @param string $period "week", "month" or "all".
	 * @param int    $limit  Number of members.
	 * @return array[] Rows of user_id and points.
	 */
	public static function top( $period, $limit = 50 ) {
		global $wpdb;

		$limit   = max( 1, min( 100, absint( $limit ) ) );
		$exclude = self::left_out();
		$extra   = count( $exclude );

		if ( 'all' === $period ) {
			$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Indexed column, one query per page view.
				$wpdb->prepare( 'SELECT user_id, points FROM %i WHERE points > 0 ORDER BY points DESC, user_id ASC LIMIT %d', Tables::name( 'profiles' ), $limit + $extra ),
				ARRAY_A
			);
		} else {
			$board = self::board( $period );
			$rows  = array();

			foreach ( $board['rows'] as $member_id => $points ) {
				$rows[] = array(
					'user_id' => $member_id,
					'points'  => $points,
				);
			}

			// Rare: so many hidden or suspended members sit at the top that the stored list could run short.
			if ( ! $board['complete'] && $limit + $extra > count( $rows ) ) {
				$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Indexed range, fallback only.
					$wpdb->prepare( 'SELECT user_id, SUM(points) AS points FROM %i WHERE created_at >= %s GROUP BY user_id HAVING points > 0 ORDER BY points DESC, user_id ASC LIMIT %d', Tables::name( 'points' ), self::period_start( $period ), $limit + $extra ),
					ARRAY_A
				);
			}
		}

		$top = array();

		foreach ( (array) $rows as $row ) {
			if ( ! in_array( (int) $row['user_id'], $exclude, true ) && count( $top ) < $limit ) {
				$top[] = array(
					'user_id' => (int) $row['user_id'],
					'points'  => (int) $row['points'],
				);
			}
		}

		return $top;
	}

	/**
	 * A member's points and place for a period.
	 *
	 * @param int    $user_id Member.
	 * @param string $period  "week", "month" or "all".
	 * @return array{points: int, rank: int|null}
	 */
	public static function rank( $user_id, $period ) {
		global $wpdb;

		$user_id = absint( $user_id );
		$exclude = self::left_out();
		$board   = 'all' === $period ? array() : self::board( $period )['rows'];

		if ( 'all' === $period ) {
			$points = Points::total( $user_id );
			$above  = $points > 0 ? $wpdb->get_col( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Indexed column.
				$wpdb->prepare( 'SELECT user_id FROM %i WHERE points > %d OR ( points = %d AND user_id < %d )', Tables::name( 'profiles' ), $points, $points, $user_id )
			) : array();
		} elseif ( isset( $board[ $user_id ] ) ) {
			// The member is on the stored board: their place is their position in it.
			$points = $board[ $user_id ];
			$above  = array_slice( array_keys( $board ), 0, (int) array_search( $user_id, array_keys( $board ), true ) );
		} else {
			$start  = self::period_start( $period );
			$points = (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Indexed range.
				$wpdb->prepare( 'SELECT COALESCE(SUM(points), 0) FROM %i WHERE user_id = %d AND created_at >= %s', Tables::name( 'points' ), $user_id, $start )
			);
			$above  = $points > 0 ? $wpdb->get_col( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Indexed range.
				$wpdb->prepare( 'SELECT user_id FROM %i WHERE created_at >= %s GROUP BY user_id HAVING SUM(points) > %d OR ( SUM(points) = %d AND user_id < %d )', Tables::name( 'points' ), $start, $points, $points, $user_id )
			) : array();
		}

		$above = array_diff( array_map( 'intval', (array) $above ), $exclude );

		return array(
			'points' => $points,
			'rank'   => $points > 0 && ! in_array( $user_id, $exclude, true ) ? count( $above ) + 1 : null,
		);
	}

	/**
	 * The leaderboard for a period, formatted for the portal.
	 *
	 * @param string $period    "week", "month" or "all".
	 * @param int    $viewer_id Viewer.
	 * @param int    $limit     Number of members.
	 * @return array
	 */
	public static function leaderboard( $period, $viewer_id, $limit = 50 ) {
		$top   = self::top( $period, $limit );
		$ids   = wp_list_pluck( $top, 'user_id' );
		$users = array();

		cache_users( $ids );

		foreach ( $ids as $id ) {
			$user = get_userdata( $id );

			if ( $user ) {
				$users[ $id ] = $user;
			}
		}

		$members = array();

		foreach ( MemberDirectory::format_many( array_values( $users ) ) as $member ) {
			$members[ $member['id'] ] = $member;
		}

		$badges = Badges::count_many( $ids );
		$items  = array();
		$rank   = 0;

		foreach ( $top as $row ) {
			if ( ! isset( $members[ $row['user_id'] ] ) ) {
				continue;
			}

			++$rank;
			$items[] = array(
				'rank'    => $rank,
				'points'  => $row['points'],
				'badges'  => $badges[ $row['user_id'] ] ?? 0,
				'member'  => $members[ $row['user_id'] ],
				'is_self' => (int) $viewer_id === $row['user_id'],
			);
		}

		return array(
			'period' => $period,
			'since'  => \SpaceBay\Support\Time::iso( self::period_start( $period ) ),
			'items'  => $items,
			'me'     => $viewer_id && ! SpaceAccess::is_restricted( $viewer_id ) ? self::rank( $viewer_id, $period ) : null,
		);
	}

	/**
	 * Turns past posts, comments and likes into points, once. Runs when the
	 * plugin is installed or updated. Badges reached this way are
	 * given without notifications, and the points keep their original dates
	 * so weekly and monthly leaderboards are not flooded.
	 *
	 * @param bool $force Run again even if it ran before.
	 * @return int Number of members with points.
	 */
	public static function backfill( $force = false ) {
		global $wpdb;

		if ( ( ! $force && get_option( self::BACKFILL_OPTION ) ) || ! self::enabled() ) {
			return 0;
		}

		update_option( self::BACKFILL_OPTION, time(), false );

		$points   = Tables::name( 'points' );
		$posts    = Tables::name( 'posts' );
		$comments = Tables::name( 'comments' );
		$likes    = Tables::name( 'reactions' );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- One-time set-based import into a custom table.
		$wpdb->query(
			$wpdb->prepare(
				"INSERT IGNORE INTO %i (user_id, points, reason, ref, object_type, object_id, created_at) SELECT user_id, %d, 'post', CONCAT('post:', id), 'post', id, created_at FROM %i WHERE status = 'published' AND user_id > 0",
				$points,
				self::value( 'post' ),
				$posts
			)
		);
		$wpdb->query(
			$wpdb->prepare(
				"INSERT IGNORE INTO %i (user_id, points, reason, ref, object_type, object_id, created_at) SELECT user_id, %d, 'comment', CONCAT('comment:', id), 'comment', id, created_at FROM %i WHERE status = 'published' AND user_id > 0",
				$points,
				self::value( 'comment' ),
				$comments
			)
		);
		$wpdb->query(
			$wpdb->prepare(
				"INSERT IGNORE INTO %i (user_id, points, reason, ref, object_type, object_id, created_at) SELECT p.user_id, %d, 'like', CONCAT('like:post:', r.object_id, ':', r.user_id), 'post', r.object_id, r.created_at FROM %i r INNER JOIN %i p ON p.id = r.object_id WHERE r.object_type = 'post' AND r.type = 'like' AND p.status = 'published' AND p.user_id > 0 AND r.user_id <> p.user_id",
				$points,
				self::value( 'like' ),
				$likes,
				$posts
			)
		);
		$wpdb->query(
			$wpdb->prepare(
				"INSERT IGNORE INTO %i (user_id, points, reason, ref, object_type, object_id, created_at) SELECT c.user_id, %d, 'like', CONCAT('like:comment:', r.object_id, ':', r.user_id), 'comment', r.object_id, r.created_at FROM %i r INNER JOIN %i c ON c.id = r.object_id WHERE r.object_type = 'comment' AND r.type = 'like' AND c.status = 'published' AND c.user_id > 0 AND r.user_id <> c.user_id",
				$points,
				self::value( 'like' ),
				$likes,
				$comments
			)
		);
		// Every member with points needs a profile row to hold the total.
		$now = \SpaceBay\Support\Time::now();
		$wpdb->query(
			$wpdb->prepare(
				"INSERT IGNORE INTO %i (user_id, status, last_activity_at, created_at, updated_at) SELECT DISTINCT t.user_id, 'active', %s, %s, %s FROM %i t INNER JOIN %i u ON u.ID = t.user_id",
				Tables::name( 'profiles' ),
				$now,
				$now,
				$now,
				$points,
				$wpdb->users
			)
		);
		$wpdb->query(
			$wpdb->prepare(
				'UPDATE %i p INNER JOIN ( SELECT user_id, SUM(points) AS total FROM %i GROUP BY user_id ) t ON t.user_id = p.user_id SET p.points = t.total',
				Tables::name( 'profiles' ),
				$points
			)
		);
		$rows = (array) $wpdb->get_results(
			$wpdb->prepare( 'SELECT user_id, reason, COUNT(id) AS total, SUM(points) AS points FROM %i GROUP BY user_id, reason', $points ),
			ARRAY_A
		);
		// phpcs:enable

		$metrics = array();
		$map     = array(
			'post'    => 'posts',
			'comment' => 'comments',
			'like'    => 'likes',
			'daily'   => 'visits',
			'event'   => 'events',
		);

		foreach ( $rows as $row ) {
			$user_id = (int) $row['user_id'];

			if ( ! isset( $metrics[ $user_id ] ) ) {
				$metrics[ $user_id ] = array_fill_keys( array( 'posts', 'comments', 'likes', 'visits', 'events', 'points' ), 0 );
			}

			if ( isset( $map[ $row['reason'] ] ) ) {
				$metrics[ $user_id ][ $map[ $row['reason'] ] ] = (int) $row['total'];
			}

			$metrics[ $user_id ]['points'] += (int) $row['points'];
		}

		self::award_reached( $metrics );
		Cache::flush( 'profiles' );

		return count( $metrics );
	}

	/**
	 * Gives automatic badges to many members at once, without notifications.
	 *
	 * @param array<int, array<string, int>> $metrics Numbers per member.
	 * @return void
	 */
	private static function award_reached( array $metrics ) {
		global $wpdb;

		$badges = array_filter(
			Badges::all( true ),
			static function ( $badge ) {
				return 'manual' !== $badge['metric'];
			}
		);
		$values = array();
		$now    = \SpaceBay\Support\Time::now();

		foreach ( $metrics as $user_id => $numbers ) {
			foreach ( $badges as $key => $badge ) {
				if ( ( $numbers[ $badge['metric'] ] ?? 0 ) >= (int) $badge['threshold'] ) {
					array_push( $values, (int) $user_id, (string) $key, $now );
				}
			}
		}

		foreach ( array_chunk( $values, 300 ) as $chunk ) {
			$placeholders = implode( ',', array_fill( 0, count( $chunk ) / 3, '(%d, %s, 0, %s)' ) );

			$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- One-time import; the unique key skips badges members already have.
				$wpdb->prepare( // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- One (%d, %s, 0, %s) group per award.
					"INSERT IGNORE INTO %i (user_id, badge, awarded_by, created_at) VALUES $placeholders", // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Placeholders are built from fixed groups only.
					array_merge( array( Tables::name( 'user_badges' ) ), $chunk )
				)
			);
		}
	}

	/**
	 * Removes points and badges of a deleted member.
	 *
	 * @param int $user_id Member.
	 * @return void
	 */
	public static function delete_user_data( $user_id ) {
		Points::delete_for_user( $user_id );
		Badges::delete_for_user( $user_id );

		// Removing their posts recounts points, which can bring the profile row back.
		Profiles::delete_user_data( $user_id );
	}

	/**
	 * The author of a post or comment.
	 *
	 * @param string $object_type "post" or "comment".
	 * @param int    $object_id   Object ID.
	 * @return int
	 */
	private static function author_of( $object_type, $object_id ) {
		$row = 'post' === $object_type ? Posts::find( $object_id ) : Comments::find( $object_id );

		return $row ? (int) $row['user_id'] : 0;
	}

	/**
	 * Reference of one like.
	 *
	 * @param string $object_type Object type.
	 * @param int    $object_id   Object ID.
	 * @param int    $user_id     Member who liked.
	 * @return string
	 */
	private static function like_ref( $object_type, $object_id, $user_id ) {
		return 'like:' . ( 'post' === $object_type ? 'post' : 'comment' ) . ':' . (int) $object_id . ':' . (int) $user_id;
	}
}