<?php
/**
 * Custom tables and queries.
 *
 * @package NovoVerify
 */

defined( 'ABSPATH' ) || exit;

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Custom tables, see class comment.

/**
 * Creates the custom tables and runs every query against them.
 *
 * Direct queries are intentional: the data lives in custom tables, and check
 * counters change on every request, so object caching would only return stale data.
 */
final class NovoVerify_DB {

	/**
	 * Schema version. Increase it whenever the table definitions change.
	 */
	const VERSION = '1.0.1';

	/**
	 * Option that stores the installed schema version.
	 */
	const VERSION_OPTION = 'novoverify_db_version';

	/**
	 * Code statuses.
	 */
	const STATUS_ACTIVE  = 'active';
	const STATUS_REVOKED = 'revoked';

	/**
	 * Returns the codes table name.
	 *
	 * @return string
	 */
	public static function codes_table() {
		global $wpdb;
		return $wpdb->prefix . 'novoverify_codes';
	}

	/**
	 * Returns the checks (log) table name.
	 *
	 * @return string
	 */
	public static function checks_table() {
		global $wpdb;
		return $wpdb->prefix . 'novoverify_checks';
	}

	/**
	 * Creates or updates the tables for the current site.
	 */
	public static function install() {
		global $wpdb;

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$charset_collate = $wpdb->get_charset_collate();
		$codes           = self::codes_table();
		$checks          = self::checks_table();

		// dbDelta() is strict about formatting: one field per line, two spaces after PRIMARY KEY.
		$sql = "CREATE TABLE {$codes} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			code varchar(64) NOT NULL,
			display_code varchar(100) NOT NULL DEFAULT '',
			product_name varchar(191) NOT NULL DEFAULT '',
			product_id bigint(20) unsigned NOT NULL DEFAULT 0,
			batch varchar(100) NOT NULL DEFAULT '',
			meta longtext NULL,
			status varchar(20) NOT NULL DEFAULT 'active',
			check_count int(10) unsigned NOT NULL DEFAULT 0,
			first_checked_at datetime NULL DEFAULT NULL,
			last_checked_at datetime NULL DEFAULT NULL,
			created_at datetime NOT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY code (code),
			KEY status (status),
			KEY batch (batch),
			KEY product_id (product_id),
			KEY check_count (check_count),
			KEY created_at (created_at)
		) {$charset_collate};
		CREATE TABLE {$checks} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			code_id bigint(20) unsigned NULL DEFAULT NULL,
			code_input varchar(64) NOT NULL DEFAULT '',
			result varchar(20) NOT NULL,
			ip_hash char(64) NOT NULL DEFAULT '',
			country char(2) NOT NULL DEFAULT '',
			user_agent_hash char(64) NOT NULL DEFAULT '',
			created_at datetime NOT NULL,
			PRIMARY KEY  (id),
			KEY code_id (code_id,created_at),
			KEY result (result),
			KEY ip_created (ip_hash,created_at),
			KEY created_at (created_at)
		) {$charset_collate};";

		dbDelta( $sql );

		update_option( self::VERSION_OPTION, self::VERSION );
	}

	/**
	 * Installs or upgrades the schema when the stored version differs.
	 */
	public static function maybe_upgrade() {
		if ( get_option( self::VERSION_OPTION ) !== self::VERSION ) {
			NovoVerify_Activator::install_site();
		}
	}

	/**
	 * Drops the tables of the current site.
	 */
	public static function drop_tables() {
		global $wpdb;
		$wpdb->query( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', self::checks_table() ) );
		$wpdb->query( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', self::codes_table() ) );
		delete_option( self::VERSION_OPTION );
	}

	/**
	 * Adds the plugin tables to the list dropped when a multisite site is deleted.
	 *
	 * @param string[] $tables Table names.
	 * @return string[]
	 */
	public static function filter_drop_tables( $tables ) {
		$tables[] = self::codes_table();
		$tables[] = self::checks_table();
		return $tables;
	}

	/**
	 * Current UTC time in MySQL format.
	 *
	 * @return string
	 */
	public static function now() {
		return current_time( 'mysql', true );
	}

	/**
	 * Finds a code row by its normalized value.
	 *
	 * @param string $code Normalized code.
	 * @return object|null
	 */
	public static function get_code_by_value( $code ) {
		global $wpdb;
		return $wpdb->get_row(
			$wpdb->prepare( 'SELECT * FROM %i WHERE code = %s LIMIT 1', self::codes_table(), $code )
		);
	}

	/**
	 * Finds a code row by ID.
	 *
	 * @param int $id Code ID.
	 * @return object|null
	 */
	public static function get_code( $id ) {
		global $wpdb;
		return $wpdb->get_row(
			$wpdb->prepare( 'SELECT * FROM %i WHERE id = %d LIMIT 1', self::codes_table(), $id )
		);
	}

	/**
	 * Inserts a code row. The caller must pass an already normalized code.
	 *
	 * @param array $data Row data (code, display_code, product_name, product_id, batch, meta, status).
	 * @return int|false New row ID, or false on failure (e.g. duplicate code).
	 */
	public static function insert_code( array $data ) {
		global $wpdb;

		$row = array(
			'code'         => (string) $data['code'],
			'display_code' => isset( $data['display_code'] ) ? (string) $data['display_code'] : (string) $data['code'],
			'product_name' => isset( $data['product_name'] ) ? (string) $data['product_name'] : '',
			'product_id'   => isset( $data['product_id'] ) ? absint( $data['product_id'] ) : 0,
			'batch'        => isset( $data['batch'] ) ? (string) $data['batch'] : '',
			'meta'         => ! empty( $data['meta'] ) ? wp_json_encode( $data['meta'] ) : null,
			'status'       => isset( $data['status'] ) && self::STATUS_REVOKED === $data['status'] ? self::STATUS_REVOKED : self::STATUS_ACTIVE,
			'created_at'   => self::now(),
		);

		$inserted = $wpdb->insert(
			self::codes_table(),
			$row,
			array( '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s' )
		);

		return $inserted ? (int) $wpdb->insert_id : false;
	}

	/**
	 * Updates editable fields of a code row.
	 *
	 * @param int   $id   Code ID.
	 * @param array $data Fields to update (code, display_code, product_name, product_id, batch, meta, status).
	 * @return bool
	 */
	public static function update_code( $id, array $data ) {
		global $wpdb;

		$formats = array(
			'code'         => '%s',
			'display_code' => '%s',
			'product_name' => '%s',
			'product_id'   => '%d',
			'batch'        => '%s',
			'meta'         => '%s',
			'status'       => '%s',
		);

		$row    = array();
		$format = array();
		foreach ( $formats as $field => $type ) {
			if ( ! array_key_exists( $field, $data ) ) {
				continue;
			}
			$value = $data[ $field ];
			if ( 'meta' === $field ) {
				$value = ! empty( $value ) ? wp_json_encode( $value ) : null;
			} elseif ( 'status' === $field ) {
				$value = self::STATUS_REVOKED === $value ? self::STATUS_REVOKED : self::STATUS_ACTIVE;
			}
			$row[ $field ] = $value;
			$format[]      = $type;
		}

		if ( empty( $row ) ) {
			return false;
		}

		return false !== $wpdb->update( self::codes_table(), $row, array( 'id' => absint( $id ) ), $format, array( '%d' ) );
	}

	/**
	 * Sets the status of one or more codes.
	 *
	 * @param int[]  $ids    Code IDs.
	 * @param string $status New status.
	 * @return int Number of updated rows.
	 */
	public static function set_status( array $ids, $status ) {
		global $wpdb;

		$ids = array_filter( array_map( 'absint', $ids ) );
		if ( empty( $ids ) ) {
			return 0;
		}
		$status       = self::STATUS_REVOKED === $status ? self::STATUS_REVOKED : self::STATUS_ACTIVE;
		$placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );

		// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Placeholders are generated above.
		$sql = $wpdb->prepare( "UPDATE %i SET status = %s WHERE id IN ({$placeholders})", array_merge( array( self::codes_table(), $status ), $ids ) );

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Prepared above.
		return (int) $wpdb->query( $sql );
	}

	/**
	 * Deletes one or more codes. Their log rows are kept and detached.
	 *
	 * @param int[] $ids Code IDs.
	 * @return int Number of deleted rows.
	 */
	public static function delete_codes( array $ids ) {
		global $wpdb;

		$ids = array_filter( array_map( 'absint', $ids ) );
		if ( empty( $ids ) ) {
			return 0;
		}
		$placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );

		// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Placeholders are generated above.
		$detach = $wpdb->prepare( "UPDATE %i SET code_id = NULL WHERE code_id IN ({$placeholders})", array_merge( array( self::checks_table() ), $ids ) );
		// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Placeholders are generated above.
		$delete = $wpdb->prepare( "DELETE FROM %i WHERE id IN ({$placeholders})", array_merge( array( self::codes_table() ), $ids ) );

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Prepared above.
		$wpdb->query( $detach );
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Prepared above.
		$deleted = $wpdb->query( $delete );

		return (int) $deleted;
	}

	/**
	 * Atomically increases the check counter of a code and stamps the check dates.
	 *
	 * @param int $id Code ID.
	 * @return bool
	 */
	public static function increment_check_count( $id ) {
		global $wpdb;
		$now = self::now();
		return false !== $wpdb->query(
			$wpdb->prepare(
				'UPDATE %i SET check_count = check_count + 1, first_checked_at = COALESCE(first_checked_at, %s), last_checked_at = %s WHERE id = %d',
				self::codes_table(),
				$now,
				$now,
				$id
			)
		);
	}

	/**
	 * Writes a row to the check log.
	 *
	 * @param array $data Log data (code_id, code_input, result, ip_hash, country, user_agent_hash).
	 * @return int|false New row ID or false.
	 */
	public static function insert_check( array $data ) {
		global $wpdb;

		$row    = array(
			'code_input'      => isset( $data['code_input'] ) ? mb_substr( (string) $data['code_input'], 0, 64 ) : '',
			'result'          => (string) $data['result'],
			'ip_hash'         => isset( $data['ip_hash'] ) ? (string) $data['ip_hash'] : '',
			'country'         => isset( $data['country'] ) ? substr( (string) $data['country'], 0, 2 ) : '',
			'user_agent_hash' => isset( $data['user_agent_hash'] ) ? (string) $data['user_agent_hash'] : '',
			'created_at'      => self::now(),
		);
		$format = array( '%s', '%s', '%s', '%s', '%s', '%s' );

		// NULL cannot be passed through a %d format, so code_id is only added when known.
		if ( ! empty( $data['code_id'] ) ) {
			$row['code_id'] = absint( $data['code_id'] );
			$format[]       = '%d';
		}

		$inserted = $wpdb->insert( self::checks_table(), $row, $format );

		return $inserted ? (int) $wpdb->insert_id : false;
	}

	/**
	 * Tells whether the same visitor checked this code recently.
	 *
	 * @param int    $code_id         Code ID.
	 * @param string $ip_hash         Hashed IP address.
	 * @param string $user_agent_hash Hashed user agent.
	 * @param int    $seconds         Time window in seconds.
	 * @return bool
	 */
	public static function visitor_checked_recently( $code_id, $ip_hash, $user_agent_hash, $seconds ) {
		global $wpdb;

		if ( '' === $ip_hash || $seconds <= 0 ) {
			return false;
		}

		$since = gmdate( 'Y-m-d H:i:s', time() - (int) $seconds );

		return (bool) $wpdb->get_var(
			$wpdb->prepare(
				'SELECT id FROM %i WHERE code_id = %d AND created_at >= %s AND ip_hash = %s AND user_agent_hash = %s LIMIT 1',
				self::checks_table(),
				$code_id,
				$since,
				$ip_hash,
				$user_agent_hash
			)
		);
	}

	/**
	 * Counts logged checks from one visitor since a time (used by the rate limiter).
	 *
	 * @param string $ip_hash Hashed IP address.
	 * @param int    $seconds Window length in seconds.
	 * @return int
	 */
	public static function count_checks_by_ip( $ip_hash, $seconds ) {
		global $wpdb;

		$since = gmdate( 'Y-m-d H:i:s', time() - (int) $seconds );

		return (int) $wpdb->get_var(
			$wpdb->prepare(
				'SELECT COUNT(*) FROM %i WHERE ip_hash = %s AND created_at >= %s',
				self::checks_table(),
				(string) $ip_hash,
				$since
			)
		);
	}

	/**
	 * Deletes one batch of log rows older than the given number of days.
	 *
	 * IDs grow with time, so the batch is selected by ID range. This works on every
	 * database engine (DELETE ... LIMIT is not portable).
	 *
	 * @param int $days       Retention in days.
	 * @param int $batch_size Maximum rows to delete.
	 * @return int Number of deleted rows.
	 */
	public static function delete_checks_older_than( $days, $batch_size = 10000 ) {
		global $wpdb;

		$days = absint( $days );
		if ( $days < 1 ) {
			return 0;
		}

		$before = gmdate( 'Y-m-d H:i:s', time() - $days * DAY_IN_SECONDS );
		$table  = self::checks_table();

		$max_id = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT MAX(id) FROM %i WHERE created_at < %s', $table, $before ) );
		if ( ! $max_id ) {
			return 0;
		}
		$min_id = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT MIN(id) FROM %i', $table ) );
		$upto   = min( $max_id, $min_id + max( 1, (int) $batch_size ) - 1 );

		return (int) $wpdb->query(
			$wpdb->prepare( 'DELETE FROM %i WHERE id <= %d AND created_at < %s', $table, $upto, $before )
		);
	}

	/**
	 * Counts logged checks in the last given seconds.
	 *
	 * @param int $seconds Window length.
	 * @return int
	 */
	public static function count_checks_since( $seconds ) {
		global $wpdb;
		$since = gmdate( 'Y-m-d H:i:s', time() - (int) $seconds );
		return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE created_at >= %s', self::checks_table(), $since ) );
	}

	/**
	 * Lists the newest checks with the matching code (if it still exists).
	 *
	 * @param int $per_page Rows per page.
	 * @param int $page     Page number, 1-based.
	 * @return object[]
	 */
	public static function get_recent_checks( $per_page = 20, $page = 1 ) {
		global $wpdb;

		$per_page = max( 1, min( 999, absint( $per_page ) ) );
		$page     = max( 1, absint( $page ) );

		return (array) $wpdb->get_results(
			$wpdb->prepare(
				'SELECT l.id, l.code_id, l.code_input, l.result, l.country, l.created_at, c.display_code, c.code
				FROM %i AS l
				LEFT JOIN %i AS c ON c.id = l.code_id
				ORDER BY l.id DESC
				LIMIT %d OFFSET %d',
				self::checks_table(),
				self::codes_table(),
				$per_page,
				( $page - 1 ) * $per_page
			)
		);
	}

	/**
	 * Values for the fixed WHERE conditions of check log queries. Every filter is always part of
	 * the SQL; an empty flag value turns it off, so the SQL text itself never changes.
	 *
	 * @param array $args Filters: result, search (entered code prefix), country (or "unknown"), from, to (UTC Y-m-d H:i:s), code_id, before_id.
	 * @return array
	 */
	private static function checks_where_values( array $args ) {
		global $wpdb;

		$result  = ! empty( $args['result'] ) && in_array( $args['result'], NovoVerify_Codes::results(), true ) ? (string) $args['result'] : '';
		$search  = isset( $args['search'] ) ? (string) $args['search'] : '';
		$country = isset( $args['country'] ) ? (string) $args['country'] : '';
		$from    = ! empty( $args['from'] ) ? (string) $args['from'] : '';
		$to      = ! empty( $args['to'] ) ? (string) $args['to'] : '';
		$code_id = ! empty( $args['code_id'] ) ? (int) $args['code_id'] : 0;
		$before  = ! empty( $args['before_id'] ) ? (int) $args['before_id'] : 0;

		return array(
			$result,
			$result,
			$search,
			$wpdb->esc_like( $search ) . '%',
			$country,
			'unknown' === $country ? '' : $country,
			$from,
			$from,
			$to,
			$to,
			$code_id,
			$code_id,
			$before,
			$before,
		);
	}

	/**
	 * Lists checks (newest first) with filters and paging.
	 *
	 * @param array $args     Filters, see checks_where_values().
	 * @param int   $per_page Rows per page.
	 * @param int   $page     Page number, 1-based.
	 * @return array{items: object[], total: int}
	 */
	public static function query_checks( array $args, $per_page = 50, $page = 1 ) {
		global $wpdb;

		$per_page = max( 1, min( 999, absint( $per_page ) ) );
		$page     = max( 1, absint( $page ) );
		$values   = self::checks_where_values( $args );

		$items = $wpdb->get_results(
			// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- The SQL is a fixed string; its values are passed as one array.
			$wpdb->prepare(
				"SELECT l.id, l.code_id, l.code_input, l.result, l.country, l.created_at, c.display_code, c.code FROM %i AS l LEFT JOIN %i AS c ON c.id = l.code_id WHERE ( %s = '' OR l.result = %s ) AND ( %s = '' OR l.code_input LIKE %s ) AND ( %s = '' OR l.country = %s ) AND ( %s = '' OR l.created_at >= %s ) AND ( %s = '' OR l.created_at <= %s ) AND ( %d = 0 OR l.code_id = %d ) AND ( %d = 0 OR l.id < %d ) ORDER BY l.id DESC LIMIT %d OFFSET %d",
				array_merge( array( self::checks_table(), self::codes_table() ), $values, array( $per_page, ( $page - 1 ) * $per_page ) )
			)
		);
		$total = $wpdb->get_var(
			// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- The SQL is a fixed string; its values are passed as one array.
			$wpdb->prepare(
				"SELECT COUNT(*) FROM %i AS l WHERE ( %s = '' OR l.result = %s ) AND ( %s = '' OR l.code_input LIKE %s ) AND ( %s = '' OR l.country = %s ) AND ( %s = '' OR l.created_at >= %s ) AND ( %s = '' OR l.created_at <= %s ) AND ( %d = 0 OR l.code_id = %d ) AND ( %d = 0 OR l.id < %d )",
				array_merge( array( self::checks_table() ), $values )
			)
		);

		return array(
			'items' => (array) $items,
			'total' => (int) $total,
		);
	}

	/**
	 * Returns the next chunk of checks before an ID (for streaming exports, newest first).
	 *
	 * @param int   $before_id Only rows with a smaller ID; 0 starts at the newest row.
	 * @param int   $limit     Chunk size.
	 * @param array $args      Filters, see checks_where_values().
	 * @return object[]
	 */
	public static function get_checks_before( $before_id, $limit, array $args = array() ) {
		global $wpdb;

		$values = self::checks_where_values( array_merge( $args, array( 'before_id' => (int) $before_id ) ) );

		return (array) $wpdb->get_results(
			// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- The SQL is a fixed string; its values are passed as one array.
			$wpdb->prepare(
				"SELECT l.id, l.code_id, l.code_input, l.result, l.country, l.created_at, c.display_code, c.code, c.product_name, c.batch FROM %i AS l LEFT JOIN %i AS c ON c.id = l.code_id WHERE ( %s = '' OR l.result = %s ) AND ( %s = '' OR l.code_input LIKE %s ) AND ( %s = '' OR l.country = %s ) AND ( %s = '' OR l.created_at >= %s ) AND ( %s = '' OR l.created_at <= %s ) AND ( %d = 0 OR l.code_id = %d ) AND ( %d = 0 OR l.id < %d ) ORDER BY l.id DESC LIMIT %d",
				array_merge( array( self::checks_table(), self::codes_table() ), $values, array( (int) $limit ) )
			)
		);
	}

	/**
	 * Returns code rows after an ID, for batch jobs.
	 *
	 * @param int $after_id Last processed ID.
	 * @param int $limit    Batch size.
	 * @return object[] Rows with id, code, display_code.
	 */
	public static function get_code_batch( $after_id, $limit ) {
		global $wpdb;
		return (array) $wpdb->get_results(
			$wpdb->prepare( 'SELECT id, code, display_code FROM %i WHERE id > %d ORDER BY id ASC LIMIT %d', self::codes_table(), $after_id, $limit )
		);
	}

	/**
	 * Changes the normalized value of one code. Fails when the new value already exists.
	 *
	 * @param int    $id   Code ID.
	 * @param string $code New normalized code.
	 * @return bool
	 */
	public static function set_code_value( $id, $code ) {
		global $wpdb;
		$suppress = $wpdb->suppress_errors( true );
		$updated  = $wpdb->update( self::codes_table(), array( 'code' => $code ), array( 'id' => (int) $id ), array( '%s' ), array( '%d' ) );
		$wpdb->suppress_errors( $suppress );
		return false !== $updated;
	}

	/**
	 * Counts all codes.
	 *
	 * @return int
	 */
	public static function count_codes() {
		global $wpdb;
		return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i', self::codes_table() ) );
	}

	/**
	 * Counts all logged checks.
	 *
	 * @return int
	 */
	public static function count_checks() {
		global $wpdb;
		return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i', self::checks_table() ) );
	}

	/**
	 * Counts codes per status.
	 *
	 * @return array{all: int, active: int, revoked: int}
	 */
	public static function count_codes_by_status() {
		global $wpdb;

		$rows   = $wpdb->get_results(
			$wpdb->prepare( 'SELECT status, COUNT(*) AS total FROM %i GROUP BY status', self::codes_table() )
		);
		$counts = array(
			'all'                => 0,
			self::STATUS_ACTIVE  => 0,
			self::STATUS_REVOKED => 0,
		);
		foreach ( (array) $rows as $row ) {
			if ( isset( $counts[ $row->status ] ) ) {
				$counts[ $row->status ] = (int) $row->total;
			}
			$counts['all'] += (int) $row->total;
		}
		return $counts;
	}

	/**
	 * Returns the distinct batch names (for filters).
	 *
	 * @param int $limit Maximum number of batches.
	 * @return string[]
	 */
	public static function get_batches( $limit = 500 ) {
		global $wpdb;
		return array_map(
			'strval',
			(array) $wpdb->get_col(
				$wpdb->prepare( "SELECT DISTINCT batch FROM %i WHERE batch <> '' ORDER BY batch ASC LIMIT %d", self::codes_table(), $limit )
			)
		);
	}

	/**
	 * Values for the fixed WHERE conditions of code queries. Every filter is always part of
	 * the SQL; an empty flag value turns it off, so the SQL text itself never changes.
	 *
	 * @param array $args Filters: search, status, batch, after_id.
	 * @return array
	 */
	private static function codes_where_values( array $args ) {
		global $wpdb;

		$status = ! empty( $args['status'] ) && in_array( $args['status'], array( self::STATUS_ACTIVE, self::STATUS_REVOKED ), true ) ? (string) $args['status'] : '';
		$batch  = isset( $args['batch'] ) ? (string) $args['batch'] : '';
		$search = isset( $args['search'] ) ? (string) $args['search'] : '';
		$code   = '' !== $search ? NovoVerify_Codes::normalize_with_settings( $search ) : '';

		return array(
			$status,
			$status,
			$batch,
			$batch,
			$search,
			$wpdb->esc_like( '' !== $code ? $code : $search ) . '%',
			'%' . $wpdb->esc_like( $search ) . '%',
			'%' . $wpdb->esc_like( $search ) . '%',
			! empty( $args['after_id'] ) ? (int) $args['after_id'] : 0,
		);
	}

	/**
	 * Lists codes for the admin table.
	 *
	 * @param array $args search, status, batch, orderby, order, per_page, page.
	 * @return array{items: object[], total: int}
	 */
	public static function query_codes( array $args ) {
		global $wpdb;

		$orderby_allowed = array( 'id', 'code', 'product_name', 'batch', 'status', 'check_count', 'last_checked_at', 'created_at' );
		$orderby         = isset( $args['orderby'] ) && in_array( $args['orderby'], $orderby_allowed, true ) ? $args['orderby'] : 'id';
		$ascending       = isset( $args['order'] ) && 'asc' === strtolower( (string) $args['order'] );
		$per_page        = isset( $args['per_page'] ) ? max( 1, min( 999, absint( $args['per_page'] ) ) ) : 20;
		$page            = isset( $args['page'] ) ? max( 1, absint( $args['page'] ) ) : 1;
		$values          = self::codes_where_values( array_diff_key( $args, array( 'after_id' => 0 ) ) );
		$query_values    = array_merge( array( self::codes_table() ), $values, array( $orderby, $per_page, ( $page - 1 ) * $per_page ) );

		// The sort column is a whitelisted identifier (%i); the direction picks one of two fixed queries.
		if ( $ascending ) {
			// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- The SQL is a fixed string; its values are passed as one array.
			$items = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM %i WHERE ( %s = '' OR status = %s ) AND ( %s = '' OR batch = %s ) AND ( %s = '' OR code LIKE %s OR product_name LIKE %s OR batch LIKE %s ) AND id > %d ORDER BY %i ASC, id ASC LIMIT %d OFFSET %d", $query_values ) );
		} else {
			// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- The SQL is a fixed string; its values are passed as one array.
			$items = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM %i WHERE ( %s = '' OR status = %s ) AND ( %s = '' OR batch = %s ) AND ( %s = '' OR code LIKE %s OR product_name LIKE %s OR batch LIKE %s ) AND id > %d ORDER BY %i DESC, id DESC LIMIT %d OFFSET %d", $query_values ) );
		}
		$total = $wpdb->get_var(
			// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- The SQL is a fixed string; its values are passed as one array.
			$wpdb->prepare(
				"SELECT COUNT(*) FROM %i WHERE ( %s = '' OR status = %s ) AND ( %s = '' OR batch = %s ) AND ( %s = '' OR code LIKE %s OR product_name LIKE %s OR batch LIKE %s ) AND id > %d",
				array_merge( array( self::codes_table() ), $values )
			)
		);

		return array(
			'items' => (array) $items,
			'total' => (int) $total,
		);
	}

	/**
	 * Returns the next chunk of codes after an ID (for streaming exports).
	 *
	 * @param int   $after_id Last ID of the previous chunk.
	 * @param int   $limit    Chunk size.
	 * @param array $args     Filters: search, status, batch.
	 * @return object[]
	 */
	public static function get_codes_after( $after_id, $limit, array $args = array() ) {
		global $wpdb;

		$values = self::codes_where_values( array_merge( $args, array( 'after_id' => (int) $after_id ) ) );

		return (array) $wpdb->get_results(
			// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- The SQL is a fixed string; its values are passed as one array.
			$wpdb->prepare(
				"SELECT * FROM %i WHERE ( %s = '' OR status = %s ) AND ( %s = '' OR batch = %s ) AND ( %s = '' OR code LIKE %s OR product_name LIKE %s OR batch LIKE %s ) AND id > %d ORDER BY id ASC LIMIT %d",
				array_merge( array( self::codes_table() ), $values, array( (int) $limit ) )
			)
		);
	}

	/**
	 * Returns which of the given normalized codes already exist.
	 *
	 * @param string[] $codes Normalized codes.
	 * @return string[] Existing codes.
	 */
	public static function get_existing_codes( array $codes ) {
		global $wpdb;

		$codes = array_values( array_unique( array_map( 'strval', $codes ) ) );
		if ( empty( $codes ) ) {
			return array();
		}
		$placeholders = implode( ',', array_fill( 0, count( $codes ), '%s' ) );

		// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Placeholders are generated above.
		$sql = $wpdb->prepare( "SELECT code FROM %i WHERE code IN ({$placeholders})", array_merge( array( self::codes_table() ), $codes ) );

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Prepared above.
		return array_map( 'strval', (array) $wpdb->get_col( $sql ) );
	}

	/**
	 * Inserts many new codes with one query. Codes must be normalized, valid and not existing.
	 *
	 * Falls back to row-by-row inserts when the multi-row insert fails
	 * (for example because another request inserted one of the codes meanwhile).
	 *
	 * @param array[] $rows Rows with code, display_code, product_name, batch.
	 * @return int Number of inserted rows.
	 */
	public static function insert_codes_bulk( array $rows ) {
		global $wpdb;

		if ( empty( $rows ) ) {
			return 0;
		}

		$now    = self::now();
		$values = array();
		foreach ( $rows as $row ) {
			$values[] = $wpdb->prepare(
				'(%s,%s,%s,%s,%s,%s)',
				$row['code'],
				isset( $row['display_code'] ) ? $row['display_code'] : $row['code'],
				isset( $row['product_name'] ) ? $row['product_name'] : '',
				isset( $row['batch'] ) ? $row['batch'] : '',
				self::STATUS_ACTIVE,
				$now
			);
		}

		$sql = $wpdb->prepare( 'INSERT INTO %i (code, display_code, product_name, batch, status, created_at) VALUES ', self::codes_table() ) . implode( ',', $values );

		$suppress = $wpdb->suppress_errors( true );
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Every value fragment is prepared above.
		$inserted = $wpdb->query( $sql );

		if ( false === $inserted ) {
			$inserted = 0;
			foreach ( $rows as $row ) {
				if ( self::insert_code( $row ) ) {
					++$inserted;
				}
			}
		}

		$wpdb->suppress_errors( $suppress );

		return (int) $inserted;
	}
}