<?php

/**
 * Restores a .shiftbox backup in short, resumable steps:
 * extract to a staging folder -> load database into temporary tables ->
 * update URLs and paths -> swap files and tables in one quick final step.
 *
 * The live site keeps working until the final swap.
 *
 * @package Shiftbox
 */

// A restore loads tables directly and cannot use the object cache, so direct queries are expected here.
// Every query built by this class goes through $wpdb->prepare() with %i (identifiers), %s and %d placeholders.
// phpcs:disable WordPress.WP.AlternativeFunctions, WordPress.PHP.NoSilencedErrors.Discouraged, WordPress.DB.DirectDatabaseQuery

defined('ABSPATH') || exit;

/**
 * Importer.
 */
class Shiftbox_Importer
{

	const TMP = 'sbtmp_';
	const OLD = 'sbold_';

	/**
	 * Content paths never restored onto another server (host-specific or cache drop-ins).
	 *
	 * @return string[]
	 */
	public static function skip_paths()
	{
		$own   = dirname(plugin_basename(SHIFTBOX_FILE));
		$paths = array(
			'plugins/' . $own,
			'uploads/shiftbox-backups',
			'shiftbox-backups',
			'ai1wm-backups',
			'updraft',
			'backups-dup-lite',
			'advanced-cache.php',
			'object-cache.php',
			'db.php',
			'cache',
			'mu-plugins/wpengine-common',
			'mu-plugins/mu-plugin.php',
			'mu-plugins/wpe-wp-sign-on-plugin',
			'mu-plugins/wpe-wp-sign-on-plugin.php',
			'mu-plugins/kinsta-mu-plugins',
			'mu-plugins/kinsta-mu-plugins.php',
			'mu-plugins/sso.php',
			'mu-plugins/endurance-page-cache.php',
			'mu-plugins/endurance-php-edge.php',
			'mu-plugins/endurance-browser-cache.php',
			'mu-plugins/wp-stack-cache.php',
			'mu-plugins/0-sso.php',
		);
		$storage = Shiftbox_Storage::relative_dir();
		if (null !== $storage) {
			$paths[] = $storage;
		}
		/**
		 * Filters content paths (relative to the content folder) skipped during a restore.
		 *
		 * @param string[] $paths Paths.
		 */
		return apply_filters('shiftbox_import_skip_paths', $paths);
	}

	/**
	 * Details of this site used to rewrite URLs and paths.
	 *
	 * @return array
	 */
	public static function this_site()
	{
		global $wpdb;
		$up = wp_upload_dir(null, false);
		return array(
			'home'        => get_option('home'),
			'siteurl'     => get_option('siteurl'),
			'abspath'     => wp_normalize_path(untrailingslashit(ABSPATH)),
			'content_dir' => Shiftbox_Storage::content_dir(),
			'uploads_dir' => wp_normalize_path(untrailingslashit($up['basedir'])),
			'uploads_url' => untrailingslashit($up['baseurl']),
			'prefix'      => $wpdb->prefix,
		);
	}

	/**
	 * Checks a backup before a restore starts.
	 *
	 * @param string $file Archive path.
	 * @return array|WP_Error Manifest.
	 */
	public static function inspect($file)
	{
		$wpress   = Shiftbox_Wpress::is_name($file);
		$manifest = $wpress ? Shiftbox_Wpress::read_manifest($file) : Shiftbox_Archive::read_manifest($file);
		if (is_wp_error($manifest)) {
			return $manifest;
		}
		$manifest['format'] = $wpress ? 'wpress' : 'shiftbox';
		if (is_multisite() || ! empty($manifest['multisite'])) {
			return new WP_Error('shiftbox_multisite', __('Multisite networks are not supported yet. Both sites must be single sites.', 'shiftbox'));
		}
		$size = (float) filesize($file);
		$fh   = fopen($file, 'rb');
		fseek($fh, -3, SEEK_END);
		$tail = fread($fh, 3);
		fclose($fh);
		if (! $wpress && Shiftbox_Archive::END !== $tail) {
			return new WP_Error('shiftbox_incomplete', __('This backup file is incomplete. It was probably cut off while downloading or uploading. Download it again from the old site.', 'shiftbox'));
		}
		$limit = (int) Shiftbox_Settings::get('max_import');
		if ($limit > 0 && $size > $limit) {
			/* translators: 1: file size, 2: limit. */
			return new WP_Error('shiftbox_limit', sprintf(__('This backup is %1$s, above the import limit of %2$s set in Settings.', 'shiftbox'), size_format($size), Shiftbox_Settings::human($limit)));
		}
		$free = Shiftbox_Storage::free_space();
		if ($free >= 0 && $free < $size * 1.05 + 10485760) {
			/* translators: 1: needed, 2: free. */
			return new WP_Error('shiftbox_space', sprintf(__('Not enough disk space to restore: about %1$s is needed and %2$s is free.', 'shiftbox'), size_format($size * 1.05), size_format($free)));
		}
		$manifest['archive_size'] = $size;
		return $manifest;
	}

	/**
	 * Initial state for a restore job.
	 *
	 * @param string $file         Archive path.
	 * @param bool   $delete_after Delete the archive when done.
	 * @param array  $manifest     Manifest.
	 * @return array
	 */
	public static function initial_state($file, $delete_after, array $manifest)
	{
		return array(
			'step'     => 'extract',
			'message'  => __('Unpacking the backup…', 'shiftbox'),
			'file'     => $file,
			'delete'   => (bool) $delete_after,
			'size'     => (float) filesize($file),
			'manifest' => $manifest,
			'format'   => $manifest['format'],
			'site'     => self::this_site(),
			'extract'  => array(
				'offset' => 'wpress' === $manifest['format'] ? 0 : strlen(Shiftbox_Archive::MAGIC),
				'cur'    => null,
				'has_db' => false,
			),
		);
	}

	/**
	 * Runs work until the deadline.
	 *
	 * @param Shiftbox_Job $job      Job.
	 * @param float        $deadline Deadline.
	 * @return void
	 * @throws RuntimeException On failure.
	 */
	public static function tick(Shiftbox_Job $job, $deadline)
	{
		$d = &$job->data;
		while (microtime(true) < $deadline) {
			switch ($d['step']) {
				case 'extract':
					self::extract($job, $deadline);
					break;
				case 'database':
					self::database($job, $deadline);
					break;
				case 'replace':
					self::replace($job, $deadline);
					break;
				case 'finalize':
					self::finalize($job);
					return;
				default:
					return;
			}
		}
	}

	/**
	 * Extracts entries to the staging folder.
	 *
	 * @param Shiftbox_Job $job      Job.
	 * @param float        $deadline Deadline.
	 * @return void
	 * @throws RuntimeException On failure.
	 */
	private static function extract(Shiftbox_Job $job, $deadline)
	{
		$d     = &$job->data;
		$e     = &$d['extract'];
		$chunk = (int) Shiftbox_Settings::profile()['chunk'];
		$stage  = $job->path('stage');
		$skip   = self::skip_paths();
		$wpress = 'wpress' === $d['format'];
		$packed = $wpress && ! empty($d['manifest']['compression']) && 'none' !== $d['manifest']['compression'] ? $d['manifest']['compression'] : '';

		$in = @fopen($d['file'], 'rb');
		if (! $in) {
			throw new RuntimeException(esc_html(__('The backup file could not be opened.', 'shiftbox')));
		}

		while (microtime(true) < $deadline) {
			if (null === $e['cur']) {
				fseek($in, (int) $e['offset']);
				$entry = $wpress ? Shiftbox_Wpress::read_header($in) : Shiftbox_Archive::read_header($in);
				if ($entry && $wpress) {
					$entry['path'] = in_array($entry['path'], array('package.json', 'database.sql', 'multisite.json'), true) ? $entry['path'] : 'content/' . $entry['path'];
				}
				if (null === $entry) {
					$d['step'] = $e['has_db'] ? 'database' : 'finalize';
					if ($e['has_db']) {
						self::init_database($job);
					}
					break;
				}
				$target = self::target_for($entry['path'], $job, $stage, $skip);
				if ('database.sql' === $entry['path']) {
					$e['has_db'] = true;
				}
				$e['cur'] = array(
					'start'  => $entry['data_offset'],
					'size'   => $entry['size'],
					'mtime'  => $entry['mtime'],
					'done'   => 0,
					'out'    => 0,
					'packed' => ($packed && $entry['size'] > 0) ? $packed : '',
					'target' => $target,
				);
				if (null === $target) {
					$e['offset'] = $entry['data_offset'] + $entry['size'];
					$e['cur']    = null;
					continue;
				}
				wp_mkdir_p(dirname($target));
			}

			$cur = &$e['cur'];
			$out = @fopen($cur['target'] . '.sbpart', 'c+b');
			if (! $out) {
				fclose($in);
				/* translators: %s: file path. */
				throw new RuntimeException(esc_html(sprintf(__('Could not write %s. Check folder permissions.', 'shiftbox'), $cur['target'])));
			}
			ftruncate($out, (int) ($cur['packed'] ? $cur['out'] : $cur['done']));
			fseek($out, 0, SEEK_END);
			fseek($in, (int) ($cur['start'] + $cur['done']));
			while ($cur['packed'] && $cur['done'] < $cur['size'] && microtime(true) < $deadline) {
				$len = fread($in, 4);
				if (false === $len || strlen($len) !== 4) {
					fclose($out);
					fclose($in);
					throw new RuntimeException(esc_html__('The backup file ends early. It was probably not fully uploaded.', 'shiftbox'));
				}
				$len = unpack('N', $len)[1];
				if ($len <= 0 || $cur['done'] + 4 + $len > $cur['size']) {
					fclose($out);
					fclose($in);
					throw new RuntimeException(esc_html__('A compressed file inside the backup is damaged.', 'shiftbox'));
				}
				$plain = Shiftbox_Wpress::inflate((string) fread($in, $len), $cur['packed']);
				if (fwrite($out, $plain) !== strlen($plain)) {
					fclose($out);
					fclose($in);
					throw new RuntimeException(esc_html__('Writing files failed. The disk may be full.', 'shiftbox'));
				}
				$cur['done'] += 4 + $len;
				$cur['out']  += strlen($plain);
			}
			while (! $cur['packed'] && $cur['done'] < $cur['size'] && microtime(true) < $deadline) {
				$data = fread($in, (int) min($chunk, $cur['size'] - $cur['done']));
				if (false === $data || '' === $data) {
					fclose($out);
					fclose($in);
					throw new RuntimeException(esc_html(__('The backup file ends early. It was probably not fully uploaded.', 'shiftbox')));
				}
				if (fwrite($out, $data) !== strlen($data)) {
					fclose($out);
					fclose($in);
					throw new RuntimeException(esc_html(__('Writing files failed. The disk may be full.', 'shiftbox')));
				}
				$cur['done'] += strlen($data);
			}
			fclose($out);
			if ($cur['done'] >= $cur['size']) {
				@rename($cur['target'] . '.sbpart', $cur['target']);
				if ($cur['mtime']) {
					@touch($cur['target'], (int) $cur['mtime']);
				}
				$e['offset'] = $cur['start'] + $cur['size'];
				unset($cur);
				$e['cur'] = null;
			} else {
				unset($cur);
			}
		}
		fclose($in);

		$pos = null === $e['cur'] ? $e['offset'] : $e['cur']['start'] + $e['cur']['done'];
		/* translators: 1: done, 2: total. */
		$d['message']  = sprintf(__('Unpacking: %1$s of %2$s', 'shiftbox'), size_format($pos), size_format($d['size']));
		$d['progress'] = (int) (45 * ($pos / max(1, $d['size'])));
	}

	/**
	 * Where an archive entry goes, or null to skip it.
	 *
	 * @param string       $path  Path in archive.
	 * @param Shiftbox_Job $job   Job.
	 * @param string       $stage Staging folder.
	 * @param string[]     $skip  Skipped content paths.
	 * @return string|null
	 * @throws RuntimeException On unsafe paths.
	 */
	private static function target_for($path, Shiftbox_Job $job, $stage, array $skip)
	{
		if ('package.json' === $path || 'multisite.json' === $path) {
			return null;
		}
		if ('database.sql' === $path) {
			return $job->path('database.sql');
		}
		if (0 !== strpos($path, 'content/')) {
			return null;
		}
		$rel = Shiftbox_Archive::clean_path(substr($path, 8));
		if (false === $rel) {
			throw new RuntimeException(esc_html(__('The backup contains an unsafe file path and was rejected.', 'shiftbox')));
		}
		foreach ($skip as $s) {
			if ($rel === $s || 0 === strpos($rel, $s . '/')) {
				return null;
			}
		}
		if ('.sbpart' === substr($rel, -7)) {
			return null;
		}
		return $stage . '/' . $rel;
	}

	/**
	 * Prepares database loading.
	 *
	 * @param Shiftbox_Job $job Job.
	 * @return void
	 */
	private static function init_database(Shiftbox_Job $job)
	{
		self::drop_prefixed(self::TMP);
		$job->data['db'] = array(
			'offset' => 0,
			'size'   => (int) filesize($job->path('database.sql')),
			'count'  => 0,
		);
		$job->data['message'] = __('Loading the database…', 'shiftbox');
	}

	/**
	 * Drops every table starting with a prefix.
	 *
	 * @param string $prefix Prefix.
	 * @return void
	 */
	public static function drop_prefixed($prefix)
	{
		global $wpdb;
		foreach (self::tables_like($prefix) as $t) {
			$wpdb->query($wpdb->prepare('DROP TABLE IF EXISTS %i', $t));
		}
	}

	/**
	 * Tables whose names start with a prefix.
	 *
	 * @param string $prefix Prefix.
	 * @return string[]
	 */
	private static function tables_like($prefix)
	{
		global $wpdb;
		return (array) $wpdb->get_col($wpdb->prepare('SHOW TABLES LIKE %s', $wpdb->esc_like($prefix) . '%'));
	}

	/**
	 * Session settings for loading SQL.
	 *
	 * @return void
	 */
	private static function session()
	{
		global $wpdb;
		$wpdb->query('SET FOREIGN_KEY_CHECKS = 0');
		$wpdb->query("SET SESSION sql_mode = 'NO_AUTO_VALUE_ON_ZERO'");
		$wpdb->query('SET SESSION unique_checks = 0');
	}

	/**
	 * Runs SQL statements into temporary tables.
	 *
	 * @param Shiftbox_Job $job      Job.
	 * @param float        $deadline Deadline.
	 * @return void
	 * @throws RuntimeException On failure.
	 */
	private static function database(Shiftbox_Job $job, $deadline)
	{
		global $wpdb;
		$d  = &$job->data;
		$db = &$d['db'];
		self::session();

		$placeholder = 'wpress' === $d['format'] ? '`' . Shiftbox_Wpress::PLACEHOLDER : '`{{SBPFX}}';

		$fh = fopen($job->path('database.sql'), 'rb');
		fseek($fh, (int) $db['offset']);
		$buffer = '';
		while (microtime(true) < $deadline || '' !== $buffer) {
			$line = fgets($fh);
			if (false === $line) {
				if ('' !== trim($buffer)) {
					$line = ';';
				} else {
					$d['step'] = 'replace';
					self::init_replace($job);
					break;
				}
			}
			$trim = trim($line);
			if ('' === $buffer && ('' === $trim || 0 === strpos($trim, '--') || 0 === strpos($trim, '#'))) {
				$db['offset'] = ftell($fh);
				continue;
			}
			$buffer .= $line;
			if (';' !== substr($trim, -1)) {
				continue;
			}
			$sql    = rtrim(trim($buffer), ';');
			$buffer = '';
			$sql    = str_replace($placeholder, '`' . self::TMP, $sql);
			if (! self::is_allowed($sql)) {
				$job->log('Skipped statement: ' . substr($sql, 0, 80));
				$db['offset'] = ftell($fh);
				continue;
			}
			if (false === self::run_sql($sql)) {
				fclose($fh);
				/* translators: 1: database error, 2: start of the statement. */
				throw new RuntimeException(esc_html(sprintf(__('The database could not be loaded: %1$s (statement: %2$s…). Your live site was not changed.', 'shiftbox'), $wpdb->last_error, substr($sql, 0, 120))));
			}
			$db['offset'] = ftell($fh);
			++$db['count'];
		}
		fclose($fh);

		/* translators: 1: done, 2: total. */
		$d['message']  = sprintf(__('Loading the database: %1$s of %2$s', 'shiftbox'), size_format($db['offset']), size_format($db['size']));
		$d['progress'] = 45 + (int) (35 * ($db['offset'] / max(1, $db['size'])));
	}

	/**
	 * Only statements on the temporary tables (plus SET) may run, so a backup can
	 * never touch live tables, views, triggers or other databases. Table names
	 * must be plain (letters, digits, underscore, dollar sign, hyphen).
	 *
	 * @param string $sql Statement.
	 * @return bool
	 */
	private static function is_allowed($sql)
	{
		if (preg_match('/^SET\s+(NAMES|FOREIGN_KEY_CHECKS|UNIQUE_CHECKS|SQL_MODE|TIME_ZONE|CHARACTER_SET_CLIENT|@)/i', $sql)) {
			return true;
		}
		if (! preg_match('/^(DROP\s+TABLE(?:\s+IF\s+EXISTS)?|CREATE\s+TABLE(?:\s+IF\s+NOT\s+EXISTS)?|INSERT(?:\s+IGNORE)?\s+INTO|REPLACE\s+INTO|ALTER\s+TABLE|TRUNCATE\s+TABLE)\s+`([^`]+)`(?!`)/i', $sql, $m)) {
			return false;
		}
		if (0 !== strpos($m[2], self::TMP) || ! self::is_plain_identifier($m[2])) {
			return false;
		}
		// Outside quoted values and names, a statement may not read from or rename
		// other tables (INSERT ... SELECT, CREATE ... SELECT, ALTER ... RENAME).
		$bare = preg_replace(array("/'(?:[^'\\\\]++|\\\\.|'')*+'/s", '/`(?:[^`]++|``)*+`/'), '?', $sql);
		return is_string($bare) && ! preg_match('/\b(SELECT|RENAME)\b/i', $bare);
	}

	/**
	 * Whether a table name is plain enough to be trusted in a restore.
	 *
	 * @param string $name Identifier.
	 * @return bool
	 */
	private static function is_plain_identifier($name)
	{
		return is_string($name) && strlen($name) <= 64 && (bool) preg_match('/^[A-Za-z0-9_$-]+$/', $name);
	}

	/**
	 * Runs one statement from the backup's SQL file, retrying with compatible
	 * collations when needed.
	 *
	 * These statements are the backup's own table definitions and data. They cannot
	 * be rebuilt with placeholders, so they only run after is_allowed() has checked
	 * that each one is a single CREATE/INSERT/DROP/ALTER/TRUNCATE (or SET) on a
	 * temporary sbtmp_ table with a plain name. Live tables are never touched here.
	 *
	 * @param string $sql SQL.
	 * @return bool
	 */
	private static function run_sql($sql)
	{
		global $wpdb;
		if (strlen($sql) > self::packet_limit() && preg_match('/^(INSERT|REPLACE)\b/i', $sql)) {
			return self::run_split($sql);
		}
		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Whitelisted backup statement, see above.
		if (false !== $wpdb->query($sql)) {
			return true;
		}
		$err = $wpdb->last_error;
		if (preg_match('/^CREATE\s+TABLE/i', $sql) && preg_match('/collation|character set|charset/i', $err)) {
			foreach (self::collation_fallbacks($sql) as $fixed) {
				if (false !== $wpdb->query($fixed)) {
					return true;
				}
			}
		}
		// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return false;
	}


	/**
	 * Versions of a CREATE TABLE statement that older database servers accept,
	 * tried in order when the backup uses a collation this server does not know
	 * (MySQL 8 "_0900_" or MariaDB 11 "_uca1400_" collations, for example).
	 *
	 * @param string $sql CREATE TABLE statement.
	 * @return string[]
	 */
	private static function collation_fallbacks($sql)
	{
		global $wpdb;
		$tries = array();

		// 1. Map newer collations to the closest ones older servers know, keeping each column's charset.
		$u4    = $wpdb->has_cap('utf8mb4_520') ? 'utf8mb4_unicode_520_ci' : 'utf8mb4_unicode_ci';
		$fixed = preg_replace(
			array(
				'/\butf8mb4_(?:0900|uca1400)_[a-z0-9_]+/i',
				'/\b(?:utf8mb3|utf8)_(?:0900|uca1400)_[a-z0-9_]+/i',
				'/\butf8mb3_/i',
			),
			array($u4, 'utf8_unicode_ci', 'utf8_'),
			$sql
		);
		if (is_string($fixed) && $fixed !== $sql) {
			$tries[] = $fixed;
		} else {
			$fixed = $sql;
		}

		// 2. Servers without utf8mb4 at all.
		if (! $wpdb->has_cap('utf8mb4')) {
			$tries[] = str_replace('utf8mb4', 'utf8', $fixed);
		}

		// 3. Last resort: drop every COLLATE clause so the server uses the charset's default collation.
		$bare = preg_replace('/\s+COLLATE\s*=?\s*[A-Za-z0-9_]+/i', '', end($tries) ? end($tries) : $sql);
		if (is_string($bare)) {
			$tries[] = $bare;
		}

		return array_values(array_unique($tries));
	}

	/**
	 * The server's max_allowed_packet in bytes (read once per request).
	 *
	 * @return int
	 */
	private static function packet_max()
	{
		static $max = null;
		if (null === $max) {
			global $wpdb;
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads a server setting, nothing to cache.
			$max = (int) $wpdb->get_var('SELECT @@SESSION.max_allowed_packet');
			if ($max <= 0) {
				$max = 1048576;
			}
		}
		return $max;
	}

	/**
	 * Largest statement this server will accept, with headroom for protocol overhead.
	 *
	 * @return int
	 */
	private static function packet_limit()
	{
		return max(16384, (int) (self::packet_max() * 0.9) - 1024);
	}

	/**
	 * Runs a multi-row INSERT that is bigger than max_allowed_packet by splitting
	 * it into smaller INSERTs with the same table and column list. The parts are
	 * pieces of a statement that already passed is_allowed(), so they only ever
	 * touch the same temporary sbtmp_ table.
	 *
	 * @param string $sql Statement.
	 * @return bool
	 * @throws RuntimeException When a single row is larger than the server allows.
	 */
	private static function run_split($sql)
	{
		global $wpdb;
		$limit = self::packet_limit();
		$parts = self::split_insert($sql);
		if (false === $parts) {
			// Not a plain VALUES list; let the server report its own error.
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Whitelisted backup statement, see run_sql().
			return false !== $wpdb->query($sql);
		}
		$head  = $parts['head'] . ' ';
		$batch = '';
		foreach ($parts['rows'] as $row) {
			if (strlen($head) + strlen($row) > $limit) {
				throw new RuntimeException(
					esc_html(
						sprintf(
							/* translators: 1: row size, 2: current max_allowed_packet, 3: suggested value. */
							__('One database row in this backup is %1$s, but this server accepts at most %2$s per query (MySQL max_allowed_packet). Ask your host to raise max_allowed_packet to at least %3$s (on XAMPP or WAMP, set max_allowed_packet=256M in my.ini and restart MySQL), then import again. Your live site was not changed.', 'shiftbox'),
							size_format(strlen($row)),
							size_format(self::packet_max()),
							size_format(max(16777216, 2 * strlen($row)))
						)
					)
				);
			}
			if ('' !== $batch && strlen($head) + strlen($batch) + 1 + strlen($row) > $limit) {
				if (! self::run_sql($head . $batch)) {
					return false;
				}
				$batch = '';
			}
			$batch .= ('' === $batch ? '' : ',') . $row;
		}
		return '' === $batch || self::run_sql($head . $batch);
	}

	/**
	 * Splits "INSERT INTO `t` (cols) VALUES (...),(...)" into its head and row
	 * tuples. Quotes, escaped characters and backtick names are respected.
	 *
	 * @param string $sql Statement.
	 * @return array|false Array with 'head' and 'rows', or false if it cannot be split.
	 */
	private static function split_insert($sql)
	{
		$len   = strlen($sql);
		$quote = '';
		$depth = 0;
		$head  = -1;
		$start = -1;
		$rows  = array();
		for ($i = 0; $i < $len; $i++) {
			$c = $sql[$i];
			if ('' !== $quote) {
				if ('\\' === $c && '`' !== $quote) {
					++$i;
					continue;
				}
				if ($c === $quote) {
					if ($i + 1 < $len && $sql[$i + 1] === $quote) {
						++$i;
						continue;
					}
					$quote = '';
				}
				continue;
			}
			if ("'" === $c || '"' === $c || '`' === $c) {
				$quote = $c;
				continue;
			}
			if (-1 === $head) {
				if ('(' === $c) {
					++$depth;
				} elseif (')' === $c) {
					--$depth;
				} elseif (0 === $depth && ('V' === $c || 'v' === $c) && 0 === strncasecmp(substr($sql, $i, 6), 'VALUES', 6)) {
					$before = $i > 0 ? $sql[$i - 1] : ' ';
					$after  = $i + 6 < $len ? $sql[$i + 6] : ' ';
					if ((ctype_space($before) || ')' === $before || '`' === $before) && (ctype_space($after) || '(' === $after)) {
						$head = $i + 6;
						$i   += 5;
					}
				}
				continue;
			}
			if ('(' === $c) {
				if (0 === $depth) {
					$start = $i;
				}
				++$depth;
			} elseif (')' === $c) {
				--$depth;
				if (0 === $depth) {
					$rows[] = substr($sql, $start, $i - $start + 1);
				}
			} elseif (0 === $depth && ',' !== $c && ! ctype_space($c)) {
				return false;
			}
		}
		if (-1 === $head || '' !== $quote || 0 !== $depth || empty($rows)) {
			return false;
		}
		return array(
			'head' => rtrim(substr($sql, 0, $head)),
			'rows' => $rows,
		);
	}

	/**
	 * Prepares URL and path rewriting.
	 *
	 * @param Shiftbox_Job $job Job.
	 * @return void
	 */
	private static function init_replace(Shiftbox_Job $job)
	{
		global $wpdb;
		$pairs = Shiftbox_Replacer::pairs_for_move($job->data['manifest'], $job->data['site']);
		if ('wpress' === $job->data['format']) {
			$pairs[Shiftbox_Wpress::PLACEHOLDER] = $wpdb->prefix;
		}
		$tables = self::tables_like(self::TMP);

		$needles = array();
		foreach (array_keys($pairs) as $from) {
			$needles[] = (string) $from;
		}
		usort(
			$needles,
			static function ($a, $b) {
				return strlen($a) - strlen($b);
			}
		);
		$short = array();
		foreach ($needles as $n) {
			$covered = false;
			foreach ($short as $s) {
				if (false !== strpos($n, $s)) {
					$covered = true;
					break;
				}
			}
			if (! $covered && '' !== $n) {
				$short[] = $n;
			}
		}

		$job->data['replace'] = array(
			'pairs'   => $pairs,
			'needles' => $short,
			'tables'  => empty($pairs) ? array() : $tables,
			'index'   => 0,
			'table'   => null,
			'changed' => 0,
		);
		$job->data['message'] = __('Updating links and paths…', 'shiftbox');
	}

	/**
	 * Serialization-safe search and replace across the temporary tables.
	 *
	 * @param Shiftbox_Job $job      Job.
	 * @param float        $deadline Deadline.
	 * @return void
	 */
	private static function replace(Shiftbox_Job $job, $deadline)
	{
		global $wpdb;
		$d        = &$job->data;
		$r        = &$d['replace'];
		$rows     = (int) Shiftbox_Settings::profile()['rows'];
		$replacer = new Shiftbox_Replacer($r['pairs']);

		while (microtime(true) < $deadline && $r['index'] < count($r['tables'])) {
			$table = $r['tables'][$r['index']];
			if (null === $r['table']) {
				$text = array();
				foreach ((array) $wpdb->get_results($wpdb->prepare('SHOW COLUMNS FROM %i', $table), ARRAY_A) as $col) {
					if (preg_match('/char|text|json|enum|set/i', $col['Type']) && ! preg_match('/^(enum|set)/i', $col['Type'])) {
						$text[] = $col['Field'];
					}
				}
				$keys       = (array) $wpdb->get_results($wpdb->prepare('SHOW KEYS FROM %i WHERE Key_name = %s', $table, 'PRIMARY'), ARRAY_A);
				$r['table'] = array(
					'text' => $text,
					'pk'   => 1 === count($keys) ? $keys[0]['Column_name'] : null,
					'last' => null,
				);
				if (empty($text)) {
					$r['table'] = null;
					++$r['index'];
					continue;
				}
				if (null === $r['table']['pk']) {
					self::replace_without_key($table, $text, $r['pairs']);
					$r['table'] = null;
					++$r['index'];
					continue;
				}
			}

			$t      = &$r['table'];
			$pk     = $t['pk'];
			$cols   = array_values(array_unique(array_merge(array($pk), $t['text'])));
			$result = self::rows_to_replace($table, $cols, $t['text'], $r['needles'], $pk, $t['last'], $rows);

			foreach ($result as $row) {
				$set = array();
				foreach ($t['text'] as $col) {
					if (null === $row[$col]) {
						continue;
					}
					$new = $replacer->run($row[$col]);
					if ($new !== $row[$col]) {
						$set[$col] = $new;
					}
				}
				if ($set) {
					self::update_row($table, $set, $pk, $row[$pk]);
					++$r['changed'];
				}
				$t['last'] = $row[$pk];
			}
			if (count($result) < $rows) {
				unset($t);
				$r['table'] = null;
				++$r['index'];
			} else {
				unset($t);
			}
		}

		/* translators: 1: table number, 2: total. */
		$d['message']  = sprintf(__('Updating links and paths (table %1$d of %2$d)…', 'shiftbox'), min($r['index'] + 1, max(1, count($r['tables']))), max(1, count($r['tables'])));
		$d['progress'] = 80 + (int) (15 * ($r['index'] / max(1, count($r['tables']))));

		if ($r['index'] >= count($r['tables'])) {
			$d['step'] = 'finalize';
		}
	}

	/**
	 * Next batch of rows that may contain an old URL or path.
	 *
	 * Table and column names are bound with %i and values with %s / %d. The only
	 * text added to the query is a fixed list of placeholders.
	 *
	 * @param string   $table   Table.
	 * @param string[] $cols    Columns to read (primary key first).
	 * @param string[] $text    Text columns to search.
	 * @param string[] $needles Strings to look for.
	 * @param string   $pk      Primary key column.
	 * @param mixed    $last    Last primary key value seen, or null.
	 * @param int      $rows    Batch size.
	 * @return array
	 */
	private static function rows_to_replace($table, array $cols, array $text, array $needles, $pk, $last, $rows)
	{
		global $wpdb;
		$args   = $cols;
		$args[] = $table;
		foreach ($text as $col) {
			foreach ($needles as $needle) {
				$args[] = $col;
				$args[] = '%' . $wpdb->esc_like($needle) . '%';
			}
		}
		$select = implode(', ', array_fill(0, count($cols), '%i'));
		$likes  = implode(' OR ', array_fill(0, count($text) * count($needles), '%i LIKE %s'));
		if (null === $last) {
			$args[] = $pk;
			$args[] = (int) $rows;
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- $select and $likes contain only placeholders.
			return (array) $wpdb->get_results($wpdb->prepare("SELECT {$select} FROM %i WHERE ({$likes}) ORDER BY %i ASC LIMIT %d", $args), ARRAY_A);
		}
		$args[] = $pk;
		$args[] = (string) $last;
		$args[] = $pk;
		$args[] = (int) $rows;
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- $select and $likes contain only placeholders.
		return (array) $wpdb->get_results($wpdb->prepare("SELECT {$select} FROM %i WHERE ({$likes}) AND %i > %s ORDER BY %i ASC LIMIT %d", $args), ARRAY_A);
	}

	/**
	 * Writes changed values back to one row.
	 *
	 * @param string $table Table.
	 * @param array  $set   Column => new value.
	 * @param string $pk    Primary key column.
	 * @param mixed  $id    Primary key value.
	 * @return void
	 */
	private static function update_row($table, array $set, $pk, $id)
	{
		global $wpdb;
		$args = array($table);
		foreach ($set as $col => $value) {
			$args[] = $col;
			$args[] = $value;
		}
		$args[]  = $pk;
		$args[]  = (string) $id;
		$columns = implode(', ', array_fill(0, count($set), '%i = %s'));
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- $columns contains only placeholders.
		$wpdb->query($wpdb->prepare("UPDATE %i SET {$columns} WHERE %i = %s", $args));
	}

	/**
	 * Fallback for tables without a single-column primary key.
	 *
	 * @param string $table Table.
	 * @param array  $cols  Text columns.
	 * @param array  $pairs Pairs.
	 * @return void
	 */
	private static function replace_without_key($table, array $cols, array $pairs)
	{
		global $wpdb;
		foreach ($cols as $col) {
			foreach ($pairs as $from => $to) {
				$wpdb->query(
					$wpdb->prepare(
						'UPDATE %i SET %i = REPLACE(%i, %s, %s) WHERE %i LIKE %s',
						$table,
						$col,
						$col,
						(string) $from,
						(string) $to,
						$col,
						'%' . $wpdb->esc_like((string) $from) . '%'
					)
				);
			}
		}
	}

	/**
	 * Swaps files and tables into place.
	 *
	 * @param Shiftbox_Job $job Job.
	 * @return void
	 * @throws RuntimeException On failure.
	 */
	private static function finalize(Shiftbox_Job $job)
	{
		global $wpdb;
		$d = &$job->data;
		$d['message'] = __('Switching to the restored site…', 'shiftbox');

		$has_db = ! empty($d['extract']['has_db']);
		if ($has_db) {
			self::prepare_tables($job);
		}

		self::swap_files($job);

		if ($has_db) {
			self::swap_tables($job);
		}

		wp_cache_flush();
		if (function_exists('opcache_reset')) {
			@opcache_reset();
		}
		if ($d['delete'] && is_file($d['file'])) {
			@unlink($d['file']);
			@unlink(preg_replace('/\.(shiftbox|wpress)$/', '.json', $d['file']));
		}

		$d['step']     = 'done';
		$d['progress'] = 100;
		$d['message']  = $has_db
			? __('Restore complete. Log in with a user from the restored site.', 'shiftbox')
			: __('Files restored.', 'shiftbox');
		$d['result']   = array(
			'relogin' => $has_db,
			'login'   => wp_login_url(admin_url('admin.php?page=shiftbox')),
		);
		/**
		 * Fires after a restore completes.
		 *
		 * @param array $data Job state.
		 */
		do_action('shiftbox_restore_completed', $d);
	}

	/**
	 * Fixes prefix-based keys and keeps this site's URL and Shiftbox active.
	 *
	 * @param Shiftbox_Job $job Job.
	 * @return void
	 */
	private static function prepare_tables(Shiftbox_Job $job)
	{
		global $wpdb;
		$old_prefix = (string) $job->data['manifest']['prefix'];
		$new_prefix = $wpdb->prefix;
		$options    = self::TMP . 'options';
		$usermeta   = self::TMP . 'usermeta';
		$has_opts   = (bool) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $wpdb->esc_like($options)));
		$has_meta   = (bool) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $wpdb->esc_like($usermeta)));

		if ('' !== $old_prefix && $old_prefix !== $new_prefix) {
			if ($has_opts) {
				$wpdb->query($wpdb->prepare('UPDATE %i SET option_name = %s WHERE option_name = %s', $options, $new_prefix . 'user_roles', $old_prefix . 'user_roles'));
			}
			if ($has_meta) {
				$wpdb->query($wpdb->prepare('UPDATE %i SET meta_key = CONCAT(%s, SUBSTRING(meta_key, %d)) WHERE meta_key LIKE %s', $usermeta, $new_prefix, strlen($old_prefix) + 1, $wpdb->esc_like($old_prefix) . '%'));
			}
		}

		if ($has_opts) {
			$site = $job->data['site'];
			$set  = array(
				'siteurl'           => $site['siteurl'],
				'home'              => $site['home'],
				'shiftbox_settings' => maybe_serialize(Shiftbox_Settings::all()),
			);
			foreach ($set as $name => $value) {
				self::put_option($options, $name, $value);
			}
			$wpdb->query($wpdb->prepare('DELETE FROM %i WHERE option_name = %s OR option_name LIKE %s OR option_name LIKE %s', $options, 'rewrite_rules', $wpdb->esc_like('_transient_') . '%', $wpdb->esc_like('_site_transient_') . '%'));

			$active = $wpdb->get_var($wpdb->prepare('SELECT option_value FROM %i WHERE option_name = %s', $options, 'active_plugins'));
			$list   = maybe_unserialize($active);
			$list   = is_array($list) ? $list : array();
			$own    = plugin_basename(SHIFTBOX_FILE);
			if (! in_array($own, $list, true)) {
				$list[] = $own;
				self::put_option($options, 'active_plugins', maybe_serialize(array_values($list)));
			}
		}
	}

	/**
	 * Sets an option in the restored (temporary) options table.
	 *
	 * @param string $options Options table.
	 * @param string $name    Option name.
	 * @param string $value   Stored value.
	 * @return void
	 */
	private static function put_option($options, $name, $value)
	{
		global $wpdb;
		$exists = (int) $wpdb->get_var($wpdb->prepare('SELECT COUNT(*) FROM %i WHERE option_name = %s', $options, $name));
		if ($exists) {
			$wpdb->query($wpdb->prepare('UPDATE %i SET option_value = %s WHERE option_name = %s', $options, $value, $name));
		} else {
			$wpdb->query($wpdb->prepare('INSERT INTO %i (option_name, option_value, autoload) VALUES (%s, %s, %s)', $options, $name, $value, 'yes'));
		}
	}

	/**
	 * Moves staged files into the content folder, keeping replaced items in the job trash.
	 *
	 * @param Shiftbox_Job $job Job.
	 * @return void
	 * @throws RuntimeException On failure.
	 */
	private static function swap_files(Shiftbox_Job $job)
	{
		$stage = $job->path('stage');
		if (! is_dir($stage)) {
			return;
		}
		$content = Shiftbox_Storage::content_dir();
		$trash   = $job->path('trash');
		$deep    = array('plugins', 'themes', 'uploads', 'mu-plugins', 'languages');

		foreach ((array) scandir($stage) as $top) {
			if ('.' === $top || '..' === $top) {
				continue;
			}
			if (is_dir($stage . '/' . $top) && (in_array($top, $deep, true) || is_dir($content . '/' . $top))) {
				wp_mkdir_p($content . '/' . $top);
				foreach ((array) scandir($stage . '/' . $top) as $child) {
					if ('.' === $child || '..' === $child) {
						continue;
					}
					self::swap_one($stage . '/' . $top . '/' . $child, $content . '/' . $top . '/' . $child, $trash . '/' . $top . '/' . $child);
				}
			} else {
				self::swap_one($stage . '/' . $top, $content . '/' . $top, $trash . '/' . $top);
			}
		}
	}

	/**
	 * Swaps one file or folder.
	 *
	 * @param string $from  Staged item.
	 * @param string $to    Live location.
	 * @param string $trash Where the old item goes.
	 * @return void
	 * @throws RuntimeException On failure.
	 */
	private static function swap_one($from, $to, $trash)
	{
		if (file_exists($to) || is_link($to)) {
			wp_mkdir_p(dirname($trash));
			if (! Shiftbox_Storage::move($to, $trash)) {
				/* translators: %s: path. */
				throw new RuntimeException(esc_html(sprintf(__('Could not replace %s. Check file permissions.', 'shiftbox'), $to)));
			}
		}
		if (! Shiftbox_Storage::move($from, $to)) {
			/* translators: %s: path. */
			throw new RuntimeException(esc_html(sprintf(__('Could not move files into %s. Check file permissions.', 'shiftbox'), $to)));
		}
	}

	/**
	 * Replaces live tables with the restored ones in a single RENAME.
	 *
	 * @param Shiftbox_Job $job Job.
	 * @return void
	 * @throws RuntimeException On failure.
	 */
	private static function swap_tables(Shiftbox_Job $job)
	{
		global $wpdb;
		self::drop_prefixed(self::OLD);
		$tmp   = self::tables_like(self::TMP);
		$live  = array_flip((array) $wpdb->get_col('SHOW TABLES'));
		$names = array();
		foreach ($tmp as $t) {
			$suffix = substr($t, strlen(self::TMP));
			if ('' === $suffix || ! self::is_plain_identifier($t)) {
				continue;
			}
			$real = $wpdb->prefix . $suffix;
			if (isset($live[$real])) {
				$names[] = $real;
				$names[] = self::OLD . $suffix;
			}
			$names[] = $t;
			$names[] = $real;
		}
		if (! $names) {
			return;
		}
		// One RENAME for every pair, so the switch is atomic. Each name is bound with %i.
		$more = implode('', array_fill(0, (int) (count($names) / 2) - 1, ', %i TO %i'));
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- $more contains only placeholders.
		if (false === $wpdb->query($wpdb->prepare("RENAME TABLE %i TO %i{$more}", $names))) {
			/* translators: %s: database error. */
			throw new RuntimeException(esc_html(sprintf(__('Switching database tables failed: %s. Your previous tables were kept.', 'shiftbox'), $wpdb->last_error)));
		}
		self::drop_prefixed(self::OLD);
	}
}