<?php
/**
 * Where the statistics are stored: the WordPress database (default), ClickHouse (see class-visitrix-clickhouse.php)
 * or a separate MySQL/MariaDB-compatible database — a second database on the same hosting or a cloud one
 * (Yandex Cloud, VK Cloud, Selectel, Timeweb Cloud, AWS RDS/Aurora, Google Cloud SQL,
 * Azure Database for MySQL, DigitalOcean, PlanetScale, TiDB Cloud and others).
 *
 * @package Visitrix
 */

defined( 'ABSPATH' ) || exit;

// Custom tables in a database chosen by the site owner: direct queries are expected here.
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
// phpcs:disable WordPress.DB.RestrictedFunctions -- a second database is reached with mysqli, the extension WordPress itself uses.

/**
 * Storage settings and the database connection used for the statistics tables.
 */
class Visitrix_Storage {

	/** Settings option. */
	const OPTION = 'visitrix_storage';

	/** Last connection error (transient). */
	const ERROR = 'visitrix_storage_error';

	/** Rows copied per request when moving data. */
	const CHUNK = 2000;

	/**
	 * Connection used for this request.
	 *
	 * @var Visitrix_Remote_DB|wpdb|null
	 */
	private static $db = null;

	/**
	 * Effective settings of this request.
	 *
	 * @var array|null
	 */
	private static $settings = null;

	/**
	 * Default settings.
	 *
	 * @return array
	 */
	public static function defaults() {
		return array(
			'type'   => 'wordpress',
			'url'    => '',
			'host'   => '',
			'port'   => 3306,
			'name'   => '',
			'user'   => '',
			'pass'   => '',
			'prefix' => 'wp_',
			'ssl'    => 0,
			'ca'     => '',
		);
	}

	/**
	 * Whether the connection is set in wp-config.php (VISITRIX_DB_HOST and friends).
	 *
	 * @return bool
	 */
	public static function from_constants() {
		return defined( 'VISITRIX_DB_HOST' ) && '' !== (string) VISITRIX_DB_HOST;
	}

	/**
	 * Effective settings: wp-config.php constants win over the saved ones. The password is decrypted.
	 *
	 * @return array
	 */
	public static function get() {
		if ( null === self::$settings ) {
			self::$settings = self::read();
		}
		return self::$settings;
	}

	/**
	 * Reads the settings.
	 *
	 * @return array
	 */
	private static function read() {
		if ( self::from_constants() ) {
			$ca  = defined( 'VISITRIX_DB_SSL_CA' ) && is_readable( (string) VISITRIX_DB_SSL_CA ) ? (string) file_get_contents( (string) VISITRIX_DB_SSL_CA ) : '';
			$url = preg_match( '#^https?://#i', (string) VISITRIX_DB_HOST ) ? (string) VISITRIX_DB_HOST : '';
			return array(
				'type'   => '' !== $url ? 'clickhouse' : 'mysql',
				'url'    => $url,
				'host'   => '' !== $url ? '' : (string) VISITRIX_DB_HOST,
				'port'   => defined( 'VISITRIX_DB_PORT' ) ? (int) VISITRIX_DB_PORT : 3306,
				'name'   => defined( 'VISITRIX_DB_NAME' ) ? (string) VISITRIX_DB_NAME : '',
				'user'   => defined( 'VISITRIX_DB_USER' ) ? (string) VISITRIX_DB_USER : '',
				'pass'   => defined( 'VISITRIX_DB_PASSWORD' ) ? (string) VISITRIX_DB_PASSWORD : '',
				'prefix' => defined( 'VISITRIX_DB_PREFIX' ) ? (string) VISITRIX_DB_PREFIX : 'wp_',
				'ssl'    => ( defined( 'VISITRIX_DB_SSL' ) && VISITRIX_DB_SSL ) || '' !== $ca ? 1 : 0,
				'ca'     => $ca,
			);
		}
		$saved = get_option( self::OPTION, array() );
		$s     = wp_parse_args( is_array( $saved ) ? $saved : array(), self::defaults() );
		$s['pass'] = self::decrypt( (string) $s['pass'] );
		return $s;
	}

	/**
	 * Statistics go to a separate database.
	 *
	 * @return bool
	 */
	public static function is_external() {
		return in_array( self::get()['type'], array( 'mysql', 'clickhouse' ), true );
	}

	/**
	 * Statistics are stored in ClickHouse.
	 *
	 * @return bool
	 */
	public static function is_clickhouse() {
		return 'clickhouse' === self::get()['type'];
	}

	/**
	 * Database object for the statistics tables: $wpdb, the external database, or a stub
	 * that stores nothing while the external database is unreachable (the data never falls back
	 * into the WordPress database).
	 *
	 * @return Visitrix_Remote_DB|Visitrix_ClickHouse_DB|wpdb
	 */
	public static function db() {
		global $wpdb;
		if ( null === self::$db ) {
			$s = self::get();
			if ( 'clickhouse' === $s['type'] ) {
				self::$db = new Visitrix_ClickHouse_DB( $s );
			} elseif ( 'mysql' === $s['type'] ) {
				self::$db = new Visitrix_Remote_DB( $s );
			} else {
				self::$db = $wpdb;
			}
		}
		return self::$db;
	}

	/**
	 * Forgets the connection (after the settings change).
	 */
	public static function reset() {
		if ( self::$db instanceof Visitrix_Remote_DB ) {
			self::$db->close();
		}
		self::$db = null;
	}

	/* ---------- Settings ---------- */

	/**
	 * Cleans submitted storage settings. An empty password field keeps the saved password.
	 *
	 * @param array $in Raw input.
	 * @return array Settings with the plain password.
	 */
	public static function clean( $in ) {
		$in     = is_array( $in ) ? $in : array();
		$old    = self::get();
		$prefix = isset( $in['prefix'] ) ? preg_replace( '/[^A-Za-z0-9_]/', '', (string) $in['prefix'] ) : 'wp_';
		$pass   = isset( $in['pass'] ) ? (string) $in['pass'] : '';
		$ca     = isset( $in['ca'] ) ? trim( (string) $in['ca'] ) : '';
		return array(
			'type'   => isset( $in['type'] ) && in_array( $in['type'], array( 'mysql', 'clickhouse' ), true ) ? $in['type'] : 'wordpress',
			'url'    => isset( $in['url'] ) && preg_match( '#^https?://#i', trim( (string) $in['url'] ) ) ? untrailingslashit( esc_url_raw( trim( (string) $in['url'] ) ) ) : '',
			'host'   => isset( $in['host'] ) ? preg_replace( '/[^A-Za-z0-9._:\-\[\]]/', '', (string) $in['host'] ) : '',
			'port'   => isset( $in['port'] ) && absint( $in['port'] ) ? min( 65535, absint( $in['port'] ) ) : 3306,
			'name'   => isset( $in['name'] ) ? preg_replace( '/[^A-Za-z0-9_$\-]/', '', (string) $in['name'] ) : '',
			'user'   => isset( $in['user'] ) ? sanitize_text_field( (string) $in['user'] ) : '',
			'pass'   => '' !== $pass ? $pass : (string) $old['pass'],
			'prefix' => '' !== $prefix ? substr( $prefix, 0, 32 ) : 'wp_',
			'ssl'    => empty( $in['ssl'] ) ? 0 : 1,
			'ca'     => preg_match( '/^-----BEGIN CERTIFICATE-----[\sA-Za-z0-9+\/=\-]+-----END CERTIFICATE-----$/', $ca ) ? $ca : '',
		);
	}

	/**
	 * Saves settings (password encrypted) and resets the connection.
	 *
	 * @param array $s Clean settings with the plain password.
	 */
	public static function save( $s ) {
		$s['pass'] = self::encrypt( (string) $s['pass'] );
		update_option( self::OPTION, $s, true );
		delete_transient( self::ERROR );
		delete_transient( 'visitrix_storage_down' );
		self::$settings = null;
		self::reset();
		self::flush_reports();
	}

	/**
	 * Checks a connection and the rights the statistics need (create table, insert, select, delete).
	 *
	 * @param array $s Settings with the plain password.
	 * @return true|WP_Error
	 */
	public static function test( $s ) {
		if ( 'clickhouse' === $s['type'] ) {
			return Visitrix_ClickHouse::test( $s );
		}
		if ( '' === $s['host'] || '' === $s['name'] || '' === $s['user'] ) {
			return new WP_Error( 'visitrix_storage', __( 'Fill in the host, database name and user.', 'visitrix' ) );
		}
		$db = new Visitrix_Remote_DB( $s, true );
		if ( ! $db->connect() ) {
			return new WP_Error( 'visitrix_storage', $db->last_error );
		}
		$table = $s['prefix'] . 'visitrix_check';
		$ok    = false !== $db->query( 'CREATE TABLE IF NOT EXISTS `' . $table . '` (id int unsigned NOT NULL AUTO_INCREMENT, v varchar(10) NOT NULL, PRIMARY KEY (id))' )
			&& false !== $db->query( "INSERT INTO `{$table}` (v) VALUES ('ok')" )
			&& 'ok' === $db->get_var( "SELECT v FROM `{$table}` ORDER BY id DESC LIMIT 1" )
			&& false !== $db->query( 'DROP TABLE `' . $table . '`' );
		$error = $db->last_error;
		$db->close();
		return $ok ? true : new WP_Error(
			'visitrix_storage',
			/* translators: %s: database error. */
			sprintf( __( 'Connected, but the user cannot create or write tables: %s', 'visitrix' ), $error )
		);
	}

	/* ---------- Tables in the external database ---------- */

	/**
	 * Creates the statistics tables in the external database. New ids start above the ids
	 * of the WordPress database, so copied rows never collide with the rows recorded meanwhile.
	 *
	 * @return bool
	 */
	public static function install_remote() {
		global $wpdb;
		$db = self::db();
		if ( $db instanceof Visitrix_ClickHouse_DB ) {
			if ( ! Visitrix_ClickHouse::install( $db ) ) {
				return false;
			}
			update_option( 'visitrix_remote_ready', self::ready_key(), true );
			return true;
		}
		if ( ! $db instanceof Visitrix_Remote_DB ) {
			return false;
		}
		$ok = true;
		foreach ( explode( ";\n", Visitrix_DB::schema( $db->get_charset_collate() ) ) as $sql ) {
			$sql = trim( $sql );
			if ( '' !== $sql ) {
				$ok = $ok && false !== $db->query( (string) preg_replace( '/^CREATE TABLE /', 'CREATE TABLE IF NOT EXISTS ', $sql ) );
			}
		}
		if ( ! $ok ) {
			return false;
		}
		foreach ( array( 'visitrix_visits', 'visitrix_hits', 'visitrix_goals' ) as $name ) {
			$local = $wpdb->prefix . $name;
			$max   = self::local_exists( $local ) ? (int) $wpdb->get_var( $wpdb->prepare( 'SELECT MAX(id) FROM %i', $local ) ) : 0;
			if ( $max > 0 && ! (int) $db->get_var( $db->prepare( 'SELECT COUNT(*) FROM %i', $db->prefix . $name ) ) ) {
				$db->query( $db->prepare( 'ALTER TABLE %i AUTO_INCREMENT = %d', $db->prefix . $name, $max + 1000 ) );
			}
		}
		update_option( 'visitrix_remote_ready', self::ready_key(), true );
		return true;
	}

	/**
	 * Identifies the external database and schema version the tables were created for.
	 *
	 * @return string
	 */
	private static function ready_key() {
		$s = self::get();
		return md5( wp_json_encode( array( $s['type'], $s['url'], $s['host'], $s['name'], self::db()->prefix, VISITRIX_DB_VERSION ) ) );
	}

	/**
	 * Tables of the external database exist for the current settings and schema version.
	 *
	 * @return bool
	 */
	public static function remote_ready() {
		return get_option( 'visitrix_remote_ready' ) === self::ready_key();
	}

	/**
	 * Whether a table exists in the WordPress database.
	 *
	 * @param string $table Table.
	 * @return bool
	 */
	private static function local_exists( $table ) {
		global $wpdb;
		return $table === $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table ) ) );
	}

	/**
	 * Rows of the statistics tables in the WordPress database.
	 *
	 * @return int
	 */
	public static function local_rows() {
		global $wpdb;
		$n = 0;
		foreach ( array( 'visitrix_visits', 'visitrix_hits', 'visitrix_goals', 'visitrix_visitors' ) as $name ) {
			if ( self::local_exists( $wpdb->prefix . $name ) ) {
				$n += (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i', $wpdb->prefix . $name ) );
			}
		}
		return $n;
	}

	/**
	 * Copies one chunk of the WordPress-database statistics into the external database.
	 *
	 * @param string $table  visits|hits|goals|visitors.
	 * @param string $after  Last copied key.
	 * @return array|WP_Error {copied, next (key or ''), done}
	 */
	public static function copy_chunk( $table, $after ) {
		global $wpdb;
		$db = self::db();
		if ( $db instanceof Visitrix_ClickHouse_DB && in_array( $table, array( 'visits', 'hits', 'goals', 'visitors' ), true ) ) {
			$res = Visitrix_ClickHouse::copy_chunk( $db, $table, $after );
			if ( is_array( $res ) && $res['done'] && 'visitors' === $table ) {
				self::flush_reports();
			}
			return $res;
		}
		if ( ! $db instanceof Visitrix_Remote_DB || ! in_array( $table, array( 'visits', 'hits', 'goals', 'visitors' ), true ) ) {
			return new WP_Error( 'visitrix_storage', __( 'The external database is not enabled.', 'visitrix' ) );
		}
		$local  = $wpdb->prefix . 'visitrix_' . $table;
		$remote = $db->prefix . 'visitrix_' . $table;
		if ( ! self::local_exists( $local ) ) {
			return array( 'copied' => 0, 'next' => '', 'done' => true );
		}
		$key  = 'visitors' === $table ? 'visitor_key' : 'id';
		if ( 'visitors' === $table ) {
			$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE visitor_key > %s ORDER BY visitor_key LIMIT %d', $local, (string) $after, self::CHUNK ), ARRAY_A );
		} else {
			$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE id > %d ORDER BY id LIMIT %d', $local, (int) $after, self::CHUNK ), ARRAY_A );
		}
		if ( ! $rows ) {
			return array( 'copied' => 0, 'next' => '', 'done' => true );
		}
		$cols   = array_keys( $rows[0] );
		$values = array();
		foreach ( $rows as $row ) {
			$values[] = $db->prepare( '(' . implode( ',', array_fill( 0, count( $cols ), '%s' ) ) . ')', array_values( $row ) );
		}
		$names = '`' . implode( '`,`', $cols ) . '`';
		$tail  = 'visitors' === $table
			? ' ON DUPLICATE KEY UPDATE first_seen = LEAST(first_seen, VALUES(first_seen)), last_seen = GREATEST(last_seen, VALUES(last_seen)), visits = visits + VALUES(visits)'
			: '';
		$verb  = 'visitors' === $table ? 'INSERT' : 'INSERT IGNORE';
		if ( false === $db->query( $verb . ' INTO `' . $remote . '` (' . $names . ') VALUES ' . implode( ',', $values ) . $tail ) ) {
			return new WP_Error( 'visitrix_storage', $db->last_error );
		}
		$last = end( $rows );
		if ( count( $rows ) < self::CHUNK ) {
			self::flush_reports();
		}
		return array(
			'copied' => count( $rows ),
			'next'   => (string) $last[ $key ],
			'done'   => count( $rows ) < self::CHUNK,
		);
	}

	/**
	 * Empties the statistics tables of the WordPress database (after the data was copied).
	 */
	public static function purge_local() {
		global $wpdb;
		foreach ( array( 'visitrix_hits', 'visitrix_goals', 'visitrix_visits', 'visitrix_visitors' ) as $name ) {
			if ( self::local_exists( $wpdb->prefix . $name ) ) {
				$wpdb->query( $wpdb->prepare( 'TRUNCATE TABLE %i', $wpdb->prefix . $name ) );
			}
		}
		self::flush_reports();
	}

	/**
	 * Makes cached reports stale (after the storage or the data changed).
	 */
	public static function flush_reports() {
		update_option( 'visitrix_cache_gen', (int) get_option( 'visitrix_cache_gen', 0 ) + 1, true );
	}

	/* ---------- Errors ---------- */

	/**
	 * Remembers a connection problem for the admin notice.
	 *
	 * @param string $message Error.
	 */
	public static function fail( $message ) {
		if ( ! get_transient( self::ERROR ) ) {
			set_transient( self::ERROR, array( 'message' => (string) $message, 'time' => time() ), 10 * MINUTE_IN_SECONDS );
		}
	}

	/**
	 * Last connection problem.
	 *
	 * @return array|false {message, time}
	 */
	public static function error() {
		return self::is_external() ? get_transient( self::ERROR ) : false;
	}

	/* ---------- Password at rest ---------- */

	/**
	 * Encrypts the password with the site salts (stored in the options table).
	 *
	 * @param string $plain Password.
	 * @return string
	 */
	private static function encrypt( $plain ) {
		if ( '' === $plain ) {
			return '';
		}
		if ( function_exists( 'openssl_encrypt' ) ) {
			$iv  = random_bytes( 16 );
			$enc = openssl_encrypt( $plain, 'aes-256-cbc', hash( 'sha256', wp_salt( 'secure_auth' ), true ), OPENSSL_RAW_DATA, $iv );
			if ( false !== $enc ) {
				return 'enc:' . base64_encode( $iv . $enc ); // phpcs:ignore WordPress.PHP.DiscouragedPHPFunctions.obfuscation_base64_encode -- binary ciphertext in an option.
			}
		}
		return 'b64:' . base64_encode( $plain ); // phpcs:ignore WordPress.PHP.DiscouragedPHPFunctions.obfuscation_base64_encode -- no OpenSSL: kept as is.
	}

	/**
	 * Decrypts a stored password.
	 *
	 * @param string $stored Stored value.
	 * @return string
	 */
	private static function decrypt( $stored ) {
		if ( 0 === strpos( $stored, 'enc:' ) && function_exists( 'openssl_decrypt' ) ) {
			$raw = base64_decode( substr( $stored, 4 ), true ); // phpcs:ignore WordPress.PHP.DiscouragedPHPFunctions.obfuscation_base64_decode -- see encrypt().
			if ( false !== $raw && strlen( $raw ) > 16 ) {
				$plain = openssl_decrypt( substr( $raw, 16 ), 'aes-256-cbc', hash( 'sha256', wp_salt( 'secure_auth' ), true ), OPENSSL_RAW_DATA, substr( $raw, 0, 16 ) );
				return false === $plain ? '' : $plain;
			}
			return '';
		}
		if ( 0 === strpos( $stored, 'b64:' ) ) {
			return (string) base64_decode( substr( $stored, 4 ), true ); // phpcs:ignore WordPress.PHP.DiscouragedPHPFunctions.obfuscation_base64_decode -- see encrypt().
		}
		return $stored;
	}

	/**
	 * CA certificate as a file for mysqli (written to the uploads folder, public data).
	 *
	 * @param string $pem Certificate.
	 * @return string|null Path.
	 */
	public static function ca_file( $pem ) {
		if ( '' === $pem ) {
			return null;
		}
		$dir = trailingslashit( wp_upload_dir( null, false )['basedir'] ) . 'visitrix';
		$file = $dir . '/ca-' . md5( $pem ) . '.pem';
		if ( ! file_exists( $file ) ) {
			wp_mkdir_p( $dir );
			if ( ! file_exists( $dir . '/index.php' ) ) {
				file_put_contents( $dir . '/index.php', "<?php\n// Silence is golden.\n" ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_file_put_contents -- tiny file, no WP_Filesystem on the tracker path.
			}
			file_put_contents( $file, $pem ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_file_put_contents -- see above.
		}
		return is_readable( $file ) ? $file : null;
	}
}

/**
 * A separate MySQL/MariaDB database with the part of the wpdb interface Visitrix uses.
 * Queries are prepared with $wpdb->prepare(); the connection opens on the first query.
 */
class Visitrix_Remote_DB {

	/**
	 * Table prefix.
	 *
	 * @var string
	 */
	public $prefix = 'wp_';

	/**
	 * Last insert id.
	 *
	 * @var int
	 */
	public $insert_id = 0;

	/**
	 * Rows changed by the last query.
	 *
	 * @var int
	 */
	public $rows_affected = 0;

	/**
	 * Last error.
	 *
	 * @var string
	 */
	public $last_error = '';

	/**
	 * Connection settings.
	 *
	 * @var array
	 */
	private $s;

	/**
	 * Connection.
	 *
	 * @var mysqli|null|false False after a failed attempt.
	 */
	private $link = null;

	/**
	 * Connection check from the settings page: ignores the back-off after a failure.
	 *
	 * @var bool
	 */
	private $probe = false;

	/**
	 * Constructor.
	 *
	 * @param array $s Settings with the plain password.
	 */
	public function __construct( $s, $probe = false ) {
		$this->s      = $s;
		$this->prefix = (string) $s['prefix'];
		$this->probe  = (bool) $probe;
	}

	/**
	 * Opens the connection (once per request).
	 *
	 * @return bool
	 */
	public function connect() {
		if ( null !== $this->link ) {
			return false !== $this->link;
		}
		$this->link = false;
		// After a failure the site does not wait for the timeout on every page view: next try in a minute.
		if ( ! $this->probe && get_transient( 'visitrix_storage_down' ) ) {
			$this->last_error = 'connection paused after an error';
			return false;
		}
		if ( ! function_exists( 'mysqli_init' ) ) {
			$this->last_error = 'mysqli extension is missing';
			return false;
		}
		$s = $this->s;
		try {
			$link = mysqli_init();
			mysqli_options( $link, MYSQLI_OPT_CONNECT_TIMEOUT, 3 );
			$flags = 0;
			if ( ! empty( $s['ssl'] ) ) {
				$ca = Visitrix_Storage::ca_file( (string) $s['ca'] );
				mysqli_ssl_set( $link, null, null, $ca, null, null );
				$flags = MYSQLI_CLIENT_SSL | ( $ca ? 0 : MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT );
			}
			// A host may come as "host:port".
			$host = (string) $s['host'];
			$port = (int) $s['port'];
			if ( preg_match( '/^([^:\[\]]+):(\d+)$/', $host, $m ) ) {
				$host = $m[1];
				$port = (int) $m[2];
			}
			$ok = @mysqli_real_connect( $link, $host, (string) $s['user'], (string) $s['pass'], (string) $s['name'], $port ?: 3306, null, $flags ); // phpcs:ignore WordPress.PHP.NoSilencedErrors.Discouraged -- the error is read below.
			if ( ! $ok ) {
				$this->last_error = (string) mysqli_connect_error();
				$this->down();
				return false;
			}
			mysqli_set_charset( $link, 'utf8mb4' );
			$this->link = $link;
			return true;
		} catch ( Throwable $e ) {
			$this->last_error = $e->getMessage();
			$this->down();
			return false;
		}
	}

	/**
	 * Records a failed connection and pauses new attempts for a minute.
	 */
	private function down() {
		if ( ! $this->probe ) {
			set_transient( 'visitrix_storage_down', 1, MINUTE_IN_SECONDS );
			Visitrix_Storage::fail( $this->last_error );
		}
	}

	/**
	 * Closes the connection.
	 */
	public function close() {
		if ( $this->link ) {
			mysqli_close( $this->link );
		}
		$this->link = null;
	}

	/**
	 * Prepares a query exactly like $wpdb->prepare().
	 *
	 * @param string $query Query.
	 * @param mixed  ...$args Values.
	 * @return string
	 */
	public function prepare( $query, ...$args ) {
		global $wpdb;
		return $wpdb->prepare( $query, ...$args ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- passes the caller's query and values through.
	}

	/**
	 * Charset clause for CREATE TABLE.
	 *
	 * @return string
	 */
	public function get_charset_collate() {
		return 'DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci';
	}

	/**
	 * Runs a query.
	 *
	 * @param string $sql Query.
	 * @param bool   $rows Return the result rows.
	 * @return int|bool|array Affected/selected rows count, or rows when $rows; false on error.
	 */
	public function query( $sql, $rows = false ) {
		global $wpdb;
		if ( ! $this->connect() ) {
			return false;
		}
		$sql = $wpdb->remove_placeholder_escape( (string) $sql );
		try {
			$res = mysqli_query( $this->link, $sql );
		} catch ( Throwable $e ) {
			$res = false;
			$this->last_error = $e->getMessage();
		}
		if ( false === $res ) {
			$this->last_error = $this->last_error ? $this->last_error : (string) mysqli_error( $this->link );
			// Tables were removed in the external database: create them again on the next request.
			if ( 1146 === (int) mysqli_errno( $this->link ) ) {
				delete_option( 'visitrix_remote_ready' );
			}
			return false;
		}
		$this->last_error = '';
		if ( $res instanceof mysqli_result ) {
			$out = array();
			while ( $row = mysqli_fetch_assoc( $res ) ) { // phpcs:ignore Generic.CodeAnalysis.AssignmentInCondition.FoundInWhileCondition -- standard fetch loop.
				$out[] = $row;
			}
			mysqli_free_result( $res );
			return $rows ? $out : count( $out );
		}
		$this->rows_affected = (int) mysqli_affected_rows( $this->link );
		$this->insert_id     = (int) mysqli_insert_id( $this->link );
		return $rows ? array() : $this->rows_affected;
	}

	/**
	 * Result rows.
	 *
	 * @param string $sql    Query.
	 * @param string $output OBJECT, OBJECT_K, ARRAY_A or ARRAY_N.
	 * @return array|null
	 */
	public function get_results( $sql, $output = OBJECT ) {
		$rows = $this->query( $sql, true );
		if ( false === $rows ) {
			return null;
		}
		if ( ARRAY_A === $output ) {
			return $rows;
		}
		if ( ARRAY_N === $output ) {
			return array_map( 'array_values', $rows );
		}
		if ( OBJECT_K === $output ) {
			// Keyed by the first column; the first row of a duplicate key wins, as in wpdb.
			$out = array();
			foreach ( $rows as $r ) {
				$key = reset( $r );
				if ( ! isset( $out[ $key ] ) ) {
					$out[ $key ] = (object) $r;
				}
			}
			return $out;
		}
		return array_map(
			static function ( $r ) {
				return (object) $r;
			},
			$rows
		);
	}

	/**
	 * One row.
	 *
	 * @param string $sql    Query.
	 * @param string $output OBJECT, ARRAY_A or ARRAY_N.
	 * @return mixed
	 */
	public function get_row( $sql, $output = OBJECT ) {
		$rows = $this->get_results( $sql, $output );
		return $rows ? $rows[0] : null;
	}

	/**
	 * One value.
	 *
	 * @param string $sql Query.
	 * @return string|null
	 */
	public function get_var( $sql ) {
		$row = $this->get_row( $sql, ARRAY_N );
		return $row ? $row[0] : null;
	}

	/**
	 * First column.
	 *
	 * @param string $sql Query.
	 * @return array
	 */
	public function get_col( $sql ) {
		return array_map(
			static function ( $r ) {
				return $r[0];
			},
			(array) $this->get_results( $sql, ARRAY_N )
		);
	}

	/**
	 * Inserts a row.
	 *
	 * @param string     $table  Table.
	 * @param array      $data   Column => value.
	 * @param array|null $format Formats.
	 * @return int|false
	 */
	public function insert( $table, $data, $format = null ) {
		$format = $format ? array_values( (array) $format ) : array_fill( 0, count( $data ), '%s' );
		$sql    = 'INSERT INTO `' . str_replace( '`', '', $table ) . '` (`' . implode( '`,`', array_keys( $data ) ) . '`) VALUES (' . implode( ',', $format ) . ')';
		return $this->query( $this->prepare( $sql, array_values( $data ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- built from column names and format placeholders.
	}
}