<?php
/**
 * Database abstraction layer — all prepared queries centralised here.
 *
 * @package AdsdReferralCouponcoreEcosystem
 */

defined( 'ABSPATH' ) || exit;

/**
 * Class ADSD_RCC_DB
 *
 * All table names are whitelisted via $this->tables[] (built from $wpdb->prefix +
 * the ADSD_RCC_TABLE_PREFIX constant) and sanitised with esc_sql() before being
 * interpolated into prepared SQL strings.  No user-controlled value ever reaches
 * a table-name position, so esc_sql() is defence-in-depth rather than the primary
 * protection mechanism.
 *
 * The WordPress.DB.PreparedSQL.InterpolatedNotPrepared sniff is suppressed only for
 * table-name positions; all data placeholders use %s / %d / %f as required.
 */
class ADSD_RCC_DB {

	private static ?ADSD_RCC_DB $instance = null;

	/** @var wpdb */
	private $wpdb;

	/** @var array<string,string> Table name map */
	private array $tables = array();

	/** Object-cache group used for all caching in this class. */
	private const CACHE_GROUP = 'adsd_rcc';

	public static function instance(): self {
		if ( null === self::$instance ) {
			self::$instance = new self();
		}
		return self::$instance;
	}

	private function __construct() {
		global $wpdb;
		$this->wpdb = $wpdb;
		$p          = $wpdb->prefix . ADSD_RCC_TABLE_PREFIX;

		$this->tables = array(
			'links'           => $p . 'referral_links',
			'referrals'       => $p . 'referrals',
			'rewards'         => $p . 'rewards',
			'verifications'   => $p . 'pending_verifications',
			'settings'        => $p . 'settings',
			'email_templates' => $p . 'email_templates',
			'logs'            => $p . 'logs',
			'passkeys'        => $p . 'passkeys',
		);
	}

	/** Return a safe, esc_sql()-sanitised table name from the internal whitelist. */
	public function t( string $name ): string {
		return $this->tables[ $name ] ?? '';
	}

	/**
	 * Return the esc_sql()-sanitised table name for use inside prepared SQL strings.
	 * All names originate from $wpdb->prefix + a plugin constant — never user input.
	 */
	private function safe_table( string $name ): string {
		return esc_sql( $this->tables[ $name ] ?? '' );
	}

	// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter -- All table names come from $wpdb->prefix + plugin constant (never user input) and are sanitised with esc_sql(). All data values use %s/%d placeholders via $wpdb->prepare().

	// ─── Settings ────────────────────────────────────────────────────────────

	public function get_setting( string $key, $default = null ) {
		if ( ! $this->table_exists( 'settings' ) ) {
			return $default;
		}

		$cache_key = 'setting_' . md5( $key );
		$cached    = wp_cache_get( $cache_key, self::CACHE_GROUP );
		if ( false !== $cached ) {
			return $cached;
		}

		$t   = $this->safe_table( 'settings' );
		$row = $this->wpdb->get_row(
			$this->wpdb->prepare(
				"SELECT setting_value FROM `{$t}` WHERE setting_key = %s",
				$key
			)
		);
		$val = $row ? maybe_unserialize( $row->setting_value ) : $default;
		wp_cache_set( $cache_key, $val, self::CACHE_GROUP, HOUR_IN_SECONDS );
		return $val;
	}

	public function set_setting( string $key, $value, bool $overwrite = true ): bool {
		if ( ! $this->table_exists( 'settings' ) ) {
			return false;
		}
		$table = $this->tables['settings'];
		$t     = esc_sql( $table );
		$existing = $this->wpdb->get_var(
			$this->wpdb->prepare( "SELECT id FROM `{$t}` WHERE setting_key = %s", $key )
		);

		$serialized = maybe_serialize( $value );
		$result     = false;

		if ( $existing ) {
			if ( ! $overwrite ) {
				return false;
			}
			$result = (bool) $this->wpdb->update(
				$table,
				array( 'setting_value' => $serialized ),
				array( 'setting_key'   => $key ),
				array( '%s' ),
				array( '%s' )
			);
		} else {
			$result = (bool) $this->wpdb->insert(
				$table,
				array( 'setting_key' => $key, 'setting_value' => $serialized ),
				array( '%s', '%s' )
			);
		}

		if ( $result ) {
			wp_cache_delete( 'setting_' . md5( $key ), self::CACHE_GROUP );
			wp_cache_delete( 'all_settings', self::CACHE_GROUP );
		}
		return $result;
	}

	public function get_all_settings(): array {
		if ( ! $this->table_exists( 'settings' ) ) {
			return array();
		}

		$cached = wp_cache_get( 'all_settings', self::CACHE_GROUP );
		if ( false !== $cached ) {
			return $cached;
		}

		$t = $this->safe_table( 'settings' );
		$rows = $this->wpdb->get_results( "SELECT setting_key, setting_value FROM `{$t}`" );
		if ( ! is_array( $rows ) ) {
			return array();
		}
		$out = array();
		foreach ( $rows as $row ) {
			$out[ $row->setting_key ] = maybe_unserialize( $row->setting_value );
		}
		wp_cache_set( 'all_settings', $out, self::CACHE_GROUP, HOUR_IN_SECONDS );
		return $out;
	}

	/**
	 * Check whether one of our custom tables actually exists in the DB.
	 * Cached per request via a static variable to avoid repeated SHOW TABLES calls.
	 */
	private function table_exists( string $name ): bool {
		$table = $this->tables[ $name ] ?? '';
		if ( ! $table ) {
			return false;
		}
		static $cache = array();
		if ( isset( $cache[ $name ] ) ) {
			return $cache[ $name ];
		}
		$result         = $this->wpdb->get_var(
			$this->wpdb->prepare( 'SHOW TABLES LIKE %s', $table )
		);
		$cache[ $name ] = ( $result === $table );
		return $cache[ $name ];
	}

	// ─── Referral Links ──────────────────────────────────────────────────────

	public function insert_referral_link( array $data ) {
		$result = $this->wpdb->insert( $this->tables['links'], $data );
		if ( $result ) {
			$this->bust_cache( 'links' );
		}
		return $result ? (int) $this->wpdb->insert_id : false;
	}

	public function get_link_by_token( string $token ): ?object {
		$cache_key = 'link_token_' . md5( $token );
		$cached    = wp_cache_get( $cache_key, self::CACHE_GROUP );
		if ( false !== $cached ) {
			return $cached ?: null;
		}
		$t = $this->safe_table( 'links' );
		$row = $this->wpdb->get_row(
			$this->wpdb->prepare(
				"SELECT * FROM `{$t}` WHERE token = %s AND status = 'active'",
				$token
			)
		);
		wp_cache_set( $cache_key, $row ?: false, self::CACHE_GROUP, 5 * MINUTE_IN_SECONDS );
		return $row ?: null;
	}

	public function get_links_by_user( int $user_id ): array {
		$cache_key = 'links_user_' . $user_id;
		$cached    = wp_cache_get( $cache_key, self::CACHE_GROUP );
		if ( false !== $cached ) {
			return $cached;
		}
		$t = $this->safe_table( 'links' );
		$rows = $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT * FROM `{$t}` WHERE user_id = %d ORDER BY created_at DESC",
				$user_id
			)
		);
		$rows = $rows ?: array();
		wp_cache_set( $cache_key, $rows, self::CACHE_GROUP, 5 * MINUTE_IN_SECONDS );
		return $rows;
	}

	public function count_links_today( int $user_id ): int {
		$cache_key = 'links_today_' . $user_id;
		$cached    = wp_cache_get( $cache_key, self::CACHE_GROUP );
		if ( false !== $cached ) {
			return (int) $cached;
		}
		$t = $this->safe_table( 'links' );
		$count = (int) $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT COUNT(*) FROM `{$t}` WHERE user_id = %d AND DATE(created_at) = CURDATE()",
				$user_id
			)
		);
		wp_cache_set( $cache_key, $count, self::CACHE_GROUP, MINUTE_IN_SECONDS );
		return $count;
	}

	public function increment_link_click( int $link_id ): void {
		$t = $this->safe_table( 'links' );
		$this->wpdb->query(
			$this->wpdb->prepare(
				"UPDATE `{$t}` SET click_count = click_count + 1 WHERE id = %d",
				$link_id
			)
		);
		$this->bust_cache( 'links' );
	}

	// ─── Referrals ───────────────────────────────────────────────────────────

	public function insert_referral( array $data ) {
		$result = $this->wpdb->insert( $this->tables['referrals'], $data );
		if ( $result ) {
			$this->bust_cache( 'referrals' );
		}
		return $result ? (int) $this->wpdb->insert_id : false;
	}

	public function get_referral( int $id ): ?object {
		$cache_key = 'referral_' . $id;
		$cached    = wp_cache_get( $cache_key, self::CACHE_GROUP );
		if ( false !== $cached ) {
			return $cached ?: null;
		}
		$t = $this->safe_table( 'referrals' );
		$row = $this->wpdb->get_row(
			$this->wpdb->prepare( "SELECT * FROM `{$t}` WHERE id = %d", $id )
		);
		wp_cache_set( $cache_key, $row ?: false, self::CACHE_GROUP, 5 * MINUTE_IN_SECONDS );
		return $row ?: null;
	}

	public function get_referral_by_email( string $email ): ?object {
		$cache_key = 'referral_email_' . md5( $email );
		$cached    = wp_cache_get( $cache_key, self::CACHE_GROUP );
		if ( false !== $cached ) {
			return $cached ?: null;
		}
		$t = $this->safe_table( 'referrals' );
		$row = $this->wpdb->get_row(
			$this->wpdb->prepare( "SELECT * FROM `{$t}` WHERE referee_email = %s", $email )
		);
		wp_cache_set( $cache_key, $row ?: false, self::CACHE_GROUP, 5 * MINUTE_IN_SECONDS );
		return $row ?: null;
	}

	public function update_referral_status( int $id, string $status, array $extra = array() ): bool {
		$data = array_merge( array( 'status' => $status ), $extra );
		$result = (bool) $this->wpdb->update( $this->tables['referrals'], $data, array( 'id' => $id ) );
		if ( $result ) {
			$this->bust_cache( 'referrals' );
			wp_cache_delete( 'referral_' . $id, self::CACHE_GROUP );
		}
		return $result;
	}

	public function get_referrals_by_user( int $user_id ): array {
		$cache_key = 'referrals_user_' . $user_id;
		$cached    = wp_cache_get( $cache_key, self::CACHE_GROUP );
		if ( false !== $cached ) {
			return $cached;
		}
		$t = $this->safe_table( 'referrals' );
		$rows = $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT * FROM `{$t}` WHERE referrer_id = %d ORDER BY created_at DESC",
				$user_id
			)
		);
		$rows = $rows ?: array();
		wp_cache_set( $cache_key, $rows, self::CACHE_GROUP, 5 * MINUTE_IN_SECONDS );
		return $rows;
	}

	// ─── Rewards ─────────────────────────────────────────────────────────────

	public function insert_reward( array $data ) {
		$result = $this->wpdb->insert( $this->tables['rewards'], $data );
		if ( $result ) {
			$this->bust_cache( 'rewards' );
		}
		return $result ? (int) $this->wpdb->insert_id : false;
	}

	public function get_reward( int $id ): ?object {
		$cache_key = 'reward_' . $id;
		$cached    = wp_cache_get( $cache_key, self::CACHE_GROUP );
		if ( false !== $cached ) {
			return $cached ?: null;
		}
		$t = $this->safe_table( 'rewards' );
		$row = $this->wpdb->get_row(
			$this->wpdb->prepare( "SELECT * FROM `{$t}` WHERE id = %d", $id )
		);
		wp_cache_set( $cache_key, $row ?: false, self::CACHE_GROUP, 5 * MINUTE_IN_SECONDS );
		return $row ?: null;
	}

	public function get_reward_by_code( string $code ): ?object {
		$cache_key = 'reward_code_' . md5( $code );
		$cached    = wp_cache_get( $cache_key, self::CACHE_GROUP );
		if ( false !== $cached ) {
			return $cached ?: null;
		}
		$t = $this->safe_table( 'rewards' );
		$row = $this->wpdb->get_row(
			$this->wpdb->prepare( "SELECT * FROM `{$t}` WHERE reward_code = %s", $code )
		);
		wp_cache_set( $cache_key, $row ?: false, self::CACHE_GROUP, 5 * MINUTE_IN_SECONDS );
		return $row ?: null;
	}

	public function get_active_rewards_for_user( int $user_id ): array {
		$cache_key = 'rewards_active_' . $user_id;
		$cached    = wp_cache_get( $cache_key, self::CACHE_GROUP );
		if ( false !== $cached ) {
			return $cached;
		}
		$t = $this->safe_table( 'rewards' );
		$rows = $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT * FROM `{$t}` WHERE user_id = %d AND status = 'active' AND (expires_at IS NULL OR expires_at > NOW()) ORDER BY created_at DESC",
				$user_id
			)
		);
		$rows = $rows ?: array();
		wp_cache_set( $cache_key, $rows, self::CACHE_GROUP, 2 * MINUTE_IN_SECONDS );
		return $rows;
	}

	public function get_rewards_for_user( int $user_id ): array {
		$cache_key = 'rewards_user_' . $user_id;
		$cached    = wp_cache_get( $cache_key, self::CACHE_GROUP );
		if ( false !== $cached ) {
			return $cached;
		}
		$t = $this->safe_table( 'rewards' );
		$rows = $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT * FROM `{$t}` WHERE user_id = %d ORDER BY created_at DESC",
				$user_id
			)
		);
		$rows = $rows ?: array();
		wp_cache_set( $cache_key, $rows, self::CACHE_GROUP, 5 * MINUTE_IN_SECONDS );
		return $rows;
	}

	public function update_reward( int $id, array $data ): bool {
		$result = (bool) $this->wpdb->update( $this->tables['rewards'], $data, array( 'id' => $id ) );
		if ( $result ) {
			$this->bust_cache( 'rewards' );
			wp_cache_delete( 'reward_' . $id, self::CACHE_GROUP );
		}
		return $result;
	}

	public function expire_overdue_rewards(): int {
		$t = $this->safe_table( 'rewards' );
		// Fully static SQL — no user input. phpcs:ignore covers the direct query.
		$result = (int) $this->wpdb->query( "UPDATE `{$t}` SET status = 'expired' WHERE status = 'active' AND expires_at IS NOT NULL AND expires_at < NOW()" );
		if ( $result > 0 ) {
			$this->bust_cache( 'rewards' );
		}
		return $result;
	}

	// ─── Pending Verifications ───────────────────────────────────────────────

	public function insert_verification( array $data ) {
		$this->wpdb->delete( $this->tables['verifications'], array( 'email' => $data['email'] ) );
		$result = $this->wpdb->insert( $this->tables['verifications'], $data );
		if ( $result ) {
			$this->bust_cache( 'verifications' );
		}
		return $result ? (int) $this->wpdb->insert_id : false;
	}

	public function get_verification_by_token( string $token ): ?object {
		$cache_key = 'verif_token_' . md5( $token );
		$cached    = wp_cache_get( $cache_key, self::CACHE_GROUP );
		if ( false !== $cached ) {
			return $cached ?: null;
		}
		$t = $this->safe_table( 'verifications' );
		$row = $this->wpdb->get_row(
			$this->wpdb->prepare(
				"SELECT * FROM `{$t}` WHERE token = %s AND verified = 0 AND expires_at > NOW()",
				$token
			)
		);
		wp_cache_set( $cache_key, $row ?: false, self::CACHE_GROUP, 5 * MINUTE_IN_SECONDS );
		return $row ?: null;
	}

	public function mark_verification_complete( int $id ): bool {
		$result = (bool) $this->wpdb->update(
			$this->tables['verifications'],
			array( 'verified' => 1 ),
			array( 'id' => $id )
		);
		if ( $result ) {
			$this->bust_cache( 'verifications' );
		}
		return $result;
	}

	public function get_pending_by_email( string $email ): ?object {
		$cache_key = 'verif_email_' . md5( $email );
		$cached    = wp_cache_get( $cache_key, self::CACHE_GROUP );
		if ( false !== $cached ) {
			return $cached ?: null;
		}
		$t = $this->safe_table( 'verifications' );
		$row = $this->wpdb->get_row(
			$this->wpdb->prepare( "SELECT * FROM `{$t}` WHERE email = %s AND verified = 0", $email )
		);
		wp_cache_set( $cache_key, $row ?: false, self::CACHE_GROUP, 5 * MINUTE_IN_SECONDS );
		return $row ?: null;
	}

	// ─── Email Templates ─────────────────────────────────────────────────────

	public function get_email_template( string $key ): ?object {
		$cache_key = 'email_tpl_' . md5( $key );
		$cached    = wp_cache_get( $cache_key, self::CACHE_GROUP );
		if ( false !== $cached ) {
			return $cached ?: null;
		}
		$t = $this->safe_table( 'email_templates' );
		$row = $this->wpdb->get_row(
			$this->wpdb->prepare( "SELECT * FROM `{$t}` WHERE template_key = %s", $key )
		);
		wp_cache_set( $cache_key, $row ?: false, self::CACHE_GROUP, HOUR_IN_SECONDS );
		return $row ?: null;
	}

	public function save_email_template( string $key, array $data ): bool {
		$table = $this->tables['email_templates'];
		$t     = esc_sql( $table );
		$existing = $this->wpdb->get_var(
			$this->wpdb->prepare( "SELECT id FROM `{$t}` WHERE template_key = %s", $key )
		);
		if ( $existing ) {
			$result = (bool) $this->wpdb->update( $table, $data, array( 'template_key' => $key ) );
		} else {
			$data['template_key'] = $key;
			$result = (bool) $this->wpdb->insert( $table, $data );
		}
		if ( $result ) {
			wp_cache_delete( 'email_tpl_' . md5( $key ), self::CACHE_GROUP );
			wp_cache_delete( 'all_email_tpl', self::CACHE_GROUP );
		}
		return $result;
	}

	public function get_all_email_templates(): array {
		$cached = wp_cache_get( 'all_email_tpl', self::CACHE_GROUP );
		if ( false !== $cached ) {
			return $cached;
		}
		$t = $this->safe_table( 'email_templates' );
		$rows = $this->wpdb->get_results( "SELECT * FROM `{$t}` ORDER BY template_key" );
		$rows = $rows ?: array();
		wp_cache_set( 'all_email_tpl', $rows, self::CACHE_GROUP, HOUR_IN_SECONDS );
		return $rows;
	}

	// ─── Passkeys ────────────────────────────────────────────────────────────

	public function insert_passkey( string $hashed_key, int $user_id ) {
		$this->wpdb->update(
			$this->tables['passkeys'],
			array( 'status' => 'revoked' ),
			array( 'status' => 'active' )
		);
		$result = $this->wpdb->insert(
			$this->tables['passkeys'],
			array( 'passkey_hash' => $hashed_key, 'created_by' => $user_id ),
			array( '%s', '%d' )
		);
		if ( $result ) {
			wp_cache_delete( 'active_passkey', self::CACHE_GROUP );
		}
		return $result ? (int) $this->wpdb->insert_id : false;
	}

	public function get_active_passkey(): ?object {
		$cached = wp_cache_get( 'active_passkey', self::CACHE_GROUP );
		if ( false !== $cached ) {
			return $cached ?: null;
		}
		$t = $this->safe_table( 'passkeys' );
		$row = $this->wpdb->get_row(
			"SELECT * FROM `{$t}` WHERE status = 'active' ORDER BY id DESC LIMIT 1"
		);
		wp_cache_set( 'active_passkey', $row ?: false, self::CACHE_GROUP, HOUR_IN_SECONDS );
		return $row ?: null;
	}

	public function validate_passkey( string $plain_passkey ): bool {
		$row = $this->get_active_passkey();
		if ( ! $row ) {
			return false;
		}
		return wp_check_password( $plain_passkey, $row->passkey_hash );
	}

	// ─── Logs ────────────────────────────────────────────────────────────────

	public function log( string $event, ?int $user_id = null, ?int $object_id = null, string $message = '', array $context = array() ): void {
		$data   = array(
			'event_type' => $event,
			'message'    => $message,
			'context'    => wp_json_encode( $context ),
			'ip_address' => ADSD_RCC_Crypto::get_client_ip(),
		);
		$format = array( '%s', '%s', '%s', '%s' );

		if ( null !== $user_id ) {
			$data['user_id'] = $user_id;
			$format[]        = '%d';
		}
		if ( null !== $object_id ) {
			$data['object_id'] = $object_id;
			$format[]          = '%d';
		}
		$this->wpdb->insert( $this->tables['logs'], $data, $format );
		$this->bust_cache( 'logs' );
	}

	public function get_logs( array $args = array() ): array {
		$et  = ! empty( $args['event_type'] ) ? (string) $args['event_type'] : '';
		$uid = ! empty( $args['user_id'] )    ? (int) $args['user_id']       : 0;
		$lim = isset( $args['limit'] )  ? (int) $args['limit']  : 100;
		$off = isset( $args['offset'] ) ? (int) $args['offset'] : 0;

		$cache_key = 'logs_' . md5( $et . '_' . $uid . '_' . $lim . '_' . $off );
		$cached    = wp_cache_get( $cache_key, self::CACHE_GROUP );
		if ( false !== $cached ) {
			return $cached;
		}

		$t    = $this->safe_table( 'logs' );
		$rows = null;

		// Four explicit branches — one SQL literal per filter combination.
		if ( $et && $uid ) {
			$rows = $this->wpdb->get_results(
				$this->wpdb->prepare(
					"SELECT * FROM `{$t}` WHERE event_type = %s AND user_id = %d ORDER BY created_at DESC LIMIT %d OFFSET %d",
					$et, $uid, $lim, $off
				)
			);
		} elseif ( $et ) {
			$rows = $this->wpdb->get_results(
				$this->wpdb->prepare(
					"SELECT * FROM `{$t}` WHERE event_type = %s ORDER BY created_at DESC LIMIT %d OFFSET %d",
					$et, $lim, $off
				)
			);
		} elseif ( $uid ) {
			$rows = $this->wpdb->get_results(
				$this->wpdb->prepare(
					"SELECT * FROM `{$t}` WHERE user_id = %d ORDER BY created_at DESC LIMIT %d OFFSET %d",
					$uid, $lim, $off
				)
			);
		} else {
			$rows = $this->wpdb->get_results(
				$this->wpdb->prepare(
					"SELECT * FROM `{$t}` ORDER BY created_at DESC LIMIT %d OFFSET %d",
					$lim, $off
				)
			);
		}

		$rows = $rows ?: array();
		wp_cache_set( $cache_key, $rows, self::CACHE_GROUP, MINUTE_IN_SECONDS );
		return $rows;
	}

	public function count_logs( array $args = array() ): int {
		$et  = ! empty( $args['event_type'] ) ? (string) $args['event_type'] : '';
		$uid = ! empty( $args['user_id'] )    ? (int) $args['user_id']       : 0;

		$cache_key = 'logs_count_' . md5( $et . '_' . $uid );
		$cached    = wp_cache_get( $cache_key, self::CACHE_GROUP );
		if ( false !== $cached ) {
			return (int) $cached;
		}

		$t     = $this->safe_table( 'logs' );
		$count = 0;

		if ( $et && $uid ) {
			$count = (int) $this->wpdb->get_var(
				$this->wpdb->prepare( "SELECT COUNT(*) FROM `{$t}` WHERE event_type = %s AND user_id = %d", $et, $uid )
			);
		} elseif ( $et ) {
			$count = (int) $this->wpdb->get_var(
				$this->wpdb->prepare( "SELECT COUNT(*) FROM `{$t}` WHERE event_type = %s", $et )
			);
		} elseif ( $uid ) {
			$count = (int) $this->wpdb->get_var(
				$this->wpdb->prepare( "SELECT COUNT(*) FROM `{$t}` WHERE user_id = %d", $uid )
			);
		} else {
			$count = (int) $this->wpdb->get_var( "SELECT COUNT(*) FROM `{$t}`" );
		}

		wp_cache_set( $cache_key, $count, self::CACHE_GROUP, MINUTE_IN_SECONDS );
		return $count;
	}

	/**
	 * Bust all object-cache keys associated with a given table group.
	 * Called after any INSERT/UPDATE/DELETE to keep caches consistent.
	 *
	 * @param string $group One of: links | referrals | rewards | verifications | logs.
	 */
	// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter

	private function bust_cache( string $group ): void {
		// wp_cache_flush_group() is only available when the object cache supports groups.
		// For broad compatibility we use wp_cache_flush_runtime() when available, or
		// simply delete the known composite-key patterns we use for this group.
		// Since most installs use the default transient-free cache, and our keys are
		// predictable, deleting the sentinel key is sufficient to invalidate list caches.
		// Individual record caches (e.g. reward_42) expire within their TTL.
		switch ( $group ) {
			case 'links':
				// Bust the daily-count and list caches; token caches expire naturally.
				wp_cache_flush_runtime();
				break;
			case 'referrals':
			case 'rewards':
			case 'verifications':
			case 'logs':
			default:
				wp_cache_flush_runtime();
				break;
		}
	}
}