<?php
/**
 * Database export helpers for BackupService.
 *
 * @package BackupRestore
 */

namespace BackupRestore\Includes;

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

trait BackupDatabaseExportTrait {
	private function find_mysqldump_binary(): ?string {
		// Prefer mariadb-dump on MariaDB hosts (mysqldump name is deprecated there).
		$paths = [
			'/usr/bin/mariadb-dump',
			'/bin/mariadb-dump',
			'/usr/local/bin/mariadb-dump',
			'/usr/bin/mysqldump',
			'/bin/mysqldump',
			'/usr/local/bin/mysqldump',
			'/opt/homebrew/bin/mariadb-dump',
			'/opt/homebrew/bin/mysqldump',
		];
		if ( strtoupper( substr( PHP_OS, 0, 3 ) ) === 'WIN' ) {
			$candidates = [];
			foreach ( [ 'C', 'D', 'E' ] as $drive ) {
				foreach ( [ 'MariaDB*', 'MySQL*' ] as $vendor ) {
					$pattern = $drive . ':\\Program Files\\' . $vendor . '\\*\\bin\\*dump.exe';
					$found   = glob( $pattern );
					if ( is_array( $found ) ) {
						$candidates = array_merge( $candidates, $found );
					}
				}
			}
			foreach ( $candidates as $p ) {
				if ( is_string( $p ) && file_exists( $p ) ) {
					return $p;
				}
			}
			return null;
		}
		foreach ( $paths as $p ) {
			if ( $p && @is_executable( $p ) ) { // phpcs:ignore WordPress.PHP.NoSilencedErrors.Discouraged
				return $p;
			}
		}
		return null;
	}

	/**
	 * Whether this dump client supports MySQL's --set-gtid-purged (MariaDB does not).
	 */
	private function mysqldump_supports_set_gtid_purged( string $bin ): bool {
		$disabled = array_filter( array_map( 'trim', explode( ',', (string) ini_get( 'disable_functions' ) ) ) );
		if ( ! function_exists( 'exec' ) || in_array( 'exec', $disabled, true ) ) {
			// Fail open only for known MariaDB binary names.
			$base = strtolower( basename( $bin ) );
			return ! str_contains( $base, 'mariadb' );
		}
		$output = [];
		// phpcs:ignore WordPress.PHP.DiscouragedPHPFunctions.system_call_exec,WordPress.PHP.NoSilencedErrors.Discouraged
		@exec( escapeshellarg( $bin ) . ' --version 2>&1', $output );
		$ver = strtolower( implode( ' ', $output ) );
		if ( $ver === '' ) {
			$base = strtolower( basename( $bin ) );
			return ! str_contains( $base, 'mariadb' );
		}
		if ( str_contains( $ver, 'mariadb' ) ) {
			return false;
		}

		return true;
	}

	/**
	 * Open per-backup log file.
	 */
	private function mysqldump_cnf_escape_value( string $value ): string {
		$escaped = str_replace( [ '\\', '"' ], [ '\\\\', '\\"' ], $value );
		$escaped = str_replace( [ "\r\n", "\n", "\r" ], [ '\n', '\n', '\n' ], $escaped );
		return '"' . $escaped . '"';
	}

	private function mysqldump_build_argv_unix(): ?array {
		if ( strtoupper( substr( PHP_OS, 0, 3 ) ) === 'WIN' ) {
			return null;
		}
		$bin = $this->find_mysqldump_binary();
		if ( ! $bin ) {
			return null;
		}

		$user = DB_USER;
		$pass = DB_PASSWORD;
		$name = DB_NAME;
		$host = DB_HOST;

		$cnf_tmp  = null;
		$cnf_args = [];
		$cnf_path = tempnam( sys_get_temp_dir(), 'wpps_db_' );
		$u_q      = $this->mysqldump_cnf_escape_value( $user );
		$p_q      = $this->mysqldump_cnf_escape_value( $pass );
		// Quoted values + [mysqldump]: unquoted passwords break on # etc.; mysqli often still connects while CLI didn't.
		$cnf_body = "[client]\nuser={$u_q}\npassword={$p_q}\n[mysqldump]\nuser={$u_q}\npassword={$p_q}\n";
		if ( $cnf_path && false !== file_put_contents( $cnf_path, $cnf_body ) ) {
			// phpcs:ignore WordPress.PHP.NoSilencedErrors.Discouraged
			@chmod( $cnf_path, 0600 );
			$cnf_tmp  = $cnf_path;
			$cnf_args = [ '--defaults-extra-file=' . escapeshellarg( $cnf_path ) ];
		}

		$flags = [
			'--single-transaction',
			'--quick',
			'--skip-lock-tables',
			'--routines',
			'--triggers',
			'--events',
			'--hex-blob',
			'--default-character-set=utf8mb4',
		];
		// MySQL only — MariaDB rejects this and aborts the whole dump.
		if ( $this->mysqldump_supports_set_gtid_purged( $bin ) ) {
			$flags[] = '--set-gtid-purged=OFF';
		}
		$args = array_merge(
			[ escapeshellarg( $bin ) ],
			$cnf_args,
			$flags
		);
		if ( ! $cnf_tmp ) {
			// Never put the DB password on argv (process-list leak). Fall back to PHP export.
			return null;
		}

		$socket_path = null;
		if ( str_starts_with( $host, 'localhost:' ) && str_contains( $host, '/' ) ) {
			$socket_path = substr( $host, strlen( 'localhost:' ) );
		}
		if ( $socket_path ) {
			$args[] = '--socket=' . escapeshellarg( $socket_path );
		} else {
			$h    = $host;
			$port = null;
			if ( preg_match( '/^([^:]+):(\d+)$/', $host, $hm ) ) {
				$h    = $hm[1];
				$port = (int) $hm[2];
			}
			$args[] = '-h' . escapeshellarg( $h );
			if ( $port ) {
				$args[] = '-P' . (string) $port;
			}
			// Align with typical mysqli TCP behaviour; avoids user@localhost (socket) vs user@127.0.0.1 (TCP) grant mismatches.
			$args[] = '--protocol=TCP';
		}

		$args[] = escapeshellarg( $name );

		// Multisite / filtered table list: append explicit tables (still dumps DB routines/events globally).
		$tables = $this->get_backup_table_list();
		if ( is_multisite() && $tables !== [] ) {
			foreach ( $tables as $table ) {
				if ( is_string( $table ) && $table !== '' && ! str_contains( $table, '`' ) ) {
					$args[] = escapeshellarg( $table );
				}
			}
		}

		return [ 'argv' => $args, 'cnf_tmp' => $cnf_tmp ];
	}

	private function mysqldump_shell_exec_available(): bool {
		if ( strtoupper( substr( PHP_OS, 0, 3 ) ) === 'WIN' ) {
			return false;
		}
		if ( ! function_exists( 'shell_exec' ) ) {
			return false;
		}
		$dis = ini_get( 'disable_functions' );
		if ( is_string( $dis ) && $dis !== '' && str_contains( strtolower( $dis ), 'shell_exec' ) ) {
			return false;
		}
		/**
		 * Set false to force synchronous mysqldump only (one HTTP request, may hit max_execution_time).
		 *
		 * @param bool $allow Default true on Unix when shell_exec is available.
		 */
		return (bool) apply_filters( 'wpps_br_allow_async_mysqldump', true );
	}

	/**
	 * @param list<string> $paths
	 */
	private function mysqldump_unlink_if_present( array $paths ): void {
		foreach ( $paths as $p ) {
			if ( is_string( $p ) && $p !== '' && is_file( $p ) ) {
				// phpcs:ignore WordPress.PHP.NoSilencedErrors.Discouraged
				@unlink( $p );
			}
		}
	}

	/**
	 * Send SIGTERM to a background mysqldump if pid file exists (backup abort / stall cleanup).
	 */
	private function mysqldump_try_terminate_async_dump( string $dest_sql_path ): void {
		if ( strtoupper( substr( PHP_OS, 0, 3 ) ) === 'WIN' ) {
			return;
		}
		$pidf = $dest_sql_path . '.mysqldump.pid';
		if ( ! is_readable( $pidf ) || filesize( $pidf ) < 1 ) {
			return;
		}
		// phpcs:ignore WordPress.WP.AlternativeFunctions.file_get_contents_file_get_contents
		$pid = (int) trim( (string) file_get_contents( $pidf ) );
		if ( $pid < 2 ) {
			return;
		}
		if ( function_exists( 'posix_kill' ) ) {
			// phpcs:ignore WordPress.PHP.NoSilencedErrors.Discouraged
			@posix_kill( $pid, 15 );
			return;
		}
		$dis = ini_get( 'disable_functions' );
		if ( is_string( $dis ) && $dis !== '' && str_contains( strtolower( $dis ), 'exec' ) ) {
			return;
		}
		// phpcs:ignore WordPress.PHP.DiscouragedPHPFunctions.system_call_exec,WordPress.PHP.NoSilencedErrors.Discouraged
		@exec( 'kill -TERM ' . $pid . ' 2>/dev/null' );
	}

	/**
	 * Spawn mysqldump in a detached shell so it is not tied to PHP max_execution_time; poll with mysqldump_poll_background_job().
	 *
	 * @param array<string, mixed> $state
	 */
	private function mysqldump_start_background_job( string $job_dir, string $dest_final, array &$state ): bool {
		$built = $this->mysqldump_build_argv_unix();
		if ( ! $built ) {
			return false;
		}

		$tmp  = $dest_final . '.mysqldump.part';
		$err  = $dest_final . '.mysqldump.stderr';
		$exf  = $dest_final . '.mysqldump.exit';
		$pidf = $dest_final . '.mysqldump.pid';
		$sh   = trailingslashit( $job_dir ) . '.wpps-mysqldump-async.sh';
		$this->mysqldump_unlink_if_present( [ $tmp, $err, $exf, $pidf, $dest_final, $sh ] );

		$argv_line = implode( ' ', $built['argv'] );
		$body      = "#!/bin/bash\n";
		$body     .= "set -u\n";
		$body     .= 'FINAL=' . escapeshellarg( $dest_final ) . "\n";
		$body     .= 'TMP=' . escapeshellarg( $tmp ) . "\n";
		$body     .= 'ERR=' . escapeshellarg( $err ) . "\n";
		$body     .= 'EXITF=' . escapeshellarg( $exf ) . "\n";
		$body     .= 'PIDF=' . escapeshellarg( $pidf ) . "\n";
		$body     .= 'rm -f "$EXITF" "$FINAL" "$PIDF"' . "\n";
		$body     .= $argv_line . ' >"$TMP" 2>"$ERR" &' . "\n";
		$body     .= 'mpid=$!' . "\n";
		$body     .= 'echo "$mpid" >"$PIDF"' . "\n";
		$body     .= 'wait $mpid' . "\n";
		$body     .= 'code=$?' . "\n";
		$body     .= 'rm -f "$PIDF"' . "\n";
		$body     .= 'echo "$code" >"$EXITF"' . "\n";
		$body     .= 'if [ "$code" -eq 0 ]; then mv -f "$TMP" "$FINAL"; else rm -f "$TMP"; fi' . "\n";

		// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_file_put_contents
		if ( false === file_put_contents( $sh, $body ) ) {
			if ( $built['cnf_tmp'] ) {
				$this->mysqldump_unlink_if_present( [ $built['cnf_tmp'] ] );
			}
			return false;
		}
		// phpcs:ignore WordPress.PHP.NoSilencedErrors.Discouraged
		@chmod( $sh, 0700 );

		$bash = '/bin/bash';
		if ( ! is_executable( $bash ) ) {
			$bash = 'bash';
		}
		$nohup = '';
		foreach ( [ '/usr/bin/nohup', '/bin/nohup' ] as $nh ) {
			if ( is_executable( $nh ) ) {
				$nohup = $nh . ' ';
				break;
			}
		}
		$launch = 'cd ' . escapeshellarg( $job_dir ) . ' && ' . $nohup . $bash . ' ' . escapeshellarg( $sh ) . ' </dev/null >/dev/null 2>&1 &';
		// phpcs:ignore WordPress.PHP.DiscouragedPHPFunctions.system_call_shell_exec,WordPress.PHP.NoSilencedErrors.Discouraged
		@shell_exec( $launch );

		$state['db_mysqldump_async']      = true;
		$state['db_mysqldump_started']    = time();
		$state['db_mysqldump_sh']         = $sh;
		$state['db_mysqldump_cnf']        = $built['cnf_tmp'];
		$state['db_mysqldump_tmp']        = $tmp;
		$state['db_mysqldump_err']        = $err;
		$state['db_mysqldump_exit']       = $exf;
		$this->log_line( __( 'mysqldump started in background (avoids PHP max_execution_time for the dump itself).', 'wppowersuite-lite' ) );
		return true;
	}

	/**
	 * @param array<string, mixed> $state
	 * @return bool|null Null = still running, true = success, false = failure (use PHP export)
	 */
	private function mysqldump_poll_background_job( string $dest_final, array &$state ): ?bool {
		$exf = $dest_final . '.mysqldump.exit';
		if ( empty( $state['db_mysqldump_async'] ) ) {
			return false;
		}
		$started = isset( $state['db_mysqldump_started'] ) ? (int) $state['db_mysqldump_started'] : 0;
		if ( $started > 0 && ( time() - $started ) > self::MYSQLDUMP_ASYNC_STALL_SECONDS ) {
			$this->log_line( __( 'Background mysqldump timed out waiting for completion; falling back to PHP export (chunked).', 'wppowersuite-lite' ) );
			$this->mysqldump_try_terminate_async_dump( $dest_final );
			$this->mysqldump_clear_async_state( $state, $dest_final );
			return false;
		}

		if ( ! is_readable( $exf ) || filesize( $exf ) < 1 ) {
			return null;
		}
		// phpcs:ignore WordPress.WP.AlternativeFunctions.file_get_contents_file_get_contents
		$raw = (string) file_get_contents( $exf );
		$code = (int) trim( $raw );

		$err = $dest_final . '.mysqldump.stderr';
		if ( is_readable( $err ) && filesize( $err ) > 0 ) {
			// phpcs:ignore WordPress.WP.AlternativeFunctions.file_get_contents_file_get_contents
			$stderr = (string) file_get_contents( $err );
			$meaningful = preg_replace( '/.*Using a password on the command line.*\n?/i', '', $stderr );
			if ( trim( $meaningful ) !== '' ) {
				$this->log_line( 'mysqldump stderr: ' . trim( substr( $meaningful, 0, 500 ) ) );
			}
		}

		$this->mysqldump_clear_async_state( $state, $dest_final );

		if ( 0 !== $code || ! is_file( $dest_final ) || filesize( $dest_final ) < 32 ) {
			if ( is_file( $dest_final ) ) {
				// phpcs:ignore WordPress.PHP.NoSilencedErrors.Discouraged
				@unlink( $dest_final );
			}
			$this->log_line( __( 'mysqldump failed; falling back to PHP export (chunked).', 'wppowersuite-lite' ) );
			return false;
		}

		$this->prepend_database_sql_headers_to_file( $dest_final );
		return true;
	}

	/**
	 * @param array<string, mixed> $state
	 */
	private function mysqldump_clear_async_state( array &$state, string $dest_final ): void {
		$paths = [
			isset( $state['db_mysqldump_sh'] ) ? (string) $state['db_mysqldump_sh'] : '',
			isset( $state['db_mysqldump_cnf'] ) && is_string( $state['db_mysqldump_cnf'] ) ? $state['db_mysqldump_cnf'] : '',
			$dest_final . '.mysqldump.part',
			$dest_final . '.mysqldump.stderr',
			$dest_final . '.mysqldump.exit',
			$dest_final . '.mysqldump.pid',
		];
		$this->mysqldump_unlink_if_present( $paths );

		unset(
			$state['db_mysqldump_async'],
			$state['db_mysqldump_started'],
			$state['db_mysqldump_sh'],
			$state['db_mysqldump_cnf'],
			$state['db_mysqldump_tmp'],
			$state['db_mysqldump_err'],
			$state['db_mysqldump_exit']
		);
	}

	/**
	 * Run mysqldump in one shot (same PHP request). Returns false → use background job or PHP export.
	 */
	private function try_export_database_mysqldump( string $dest_file ): bool {
		if ( strtoupper( substr( PHP_OS, 0, 3 ) ) === 'WIN' ) {
			$this->log_line( __( 'Using PHP database export on Windows (mysqldump shell redirect unreliable).', 'wppowersuite-lite' ) );
			return false;
		}
		if ( ! self::exec_function_available() ) {
			// Calling a disabled exec() is fatal on PHP 8+; fall back to the PHP export.
			$this->log_line( __( 'PHP exec() is disabled on this host; using PHP database export (chunked).', 'wppowersuite-lite' ) );
			return false;
		}
		$built = $this->mysqldump_build_argv_unix();
		if ( ! $built ) {
			return false;
		}

		$err_file = $dest_file . '.stderr';
		$cmd      = implode( ' ', $built['argv'] ) . ' > ' . escapeshellarg( $dest_file ) . ' 2>' . escapeshellarg( $err_file );

		$output = [];
		$code   = 0;
		// phpcs:ignore WordPress.PHP.DiscouragedPHPFunctions.system_call_exec,WordPress.PHP.NoSilencedErrors.Discouraged
		@exec( $cmd, $output, $code );

		if ( $built['cnf_tmp'] ) {
			$this->mysqldump_unlink_if_present( [ $built['cnf_tmp'] ] );
		}

		if ( is_readable( $err_file ) && filesize( $err_file ) > 0 ) {
			// phpcs:ignore WordPress.WP.AlternativeFunctions.file_get_contents_file_get_contents
			$stderr = (string) file_get_contents( $err_file );
			$meaningful = preg_replace( '/.*Using a password on the command line.*\n?/i', '', $stderr );
			if ( trim( $meaningful ) !== '' ) {
				$this->log_line( 'mysqldump stderr: ' . trim( substr( $meaningful, 0, 500 ) ) );
			}
		}
		$this->mysqldump_unlink_if_present( [ $err_file ] );

		if ( $code !== 0 || ! file_exists( $dest_file ) || filesize( $dest_file ) < 32 ) {
			$this->mysqldump_unlink_if_present( [ $dest_file ] );
			$this->log_line( __( 'mysqldump failed; falling back to PHP export (chunked).', 'wppowersuite-lite' ) );
			return false;
		}
		$this->prepend_database_sql_headers_to_file( $dest_file );
		return true;
	}

	/**
	 * Stream database to SQL using PHP across multiple backup ticks (survives low max_execution_time).
	 * Phases: tables → views → routines → triggers → events.
	 *
	 * @param array<string, mixed> $state Job state; uses keys db_php_*.
	 * @return bool True when export is complete.
	 */
	private function export_database_php_resumable( string $dest_file, array &$state ): bool {
		global $wpdb;

		$phase = isset( $state['db_php_phase'] ) && is_string( $state['db_php_phase'] ) ? $state['db_php_phase'] : 'tables';

		$tables = isset( $state['db_php_tables'] ) && is_array( $state['db_php_tables'] ) ? $state['db_php_tables'] : null;
		if ( ! is_array( $tables ) ) {
			$tables = $this->get_backup_table_list();
			if ( $tables === [] ) {
				throw new \Exception( __( 'Could not read database table list.', 'wppowersuite-lite' ) );
			}
			$state['db_php_tables']             = $tables;
			$state['db_php_table_index']        = 0;
			$state['db_php_row_offset']         = 0;
			$state['db_php_schema_written_idx'] = -1;
			$state['db_php_phase']              = 'tables';
			$phase                              = 'tables';
			// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fopen
			$fh = fopen( $dest_file, 'wb' );
			if ( ! $fh ) {
				throw new \Exception( __( 'Could not open database export file for writing.', 'wppowersuite-lite' ) );
			}
			fwrite( $fh, "SET FOREIGN_KEY_CHECKS=0;\nSET SQL_MODE=\"NO_AUTO_VALUE_ON_ZERO\";\nSET NAMES utf8mb4;\n" ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fwrite
			fwrite( $fh, $this->get_database_dump_sql_header_lines() ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fwrite
			fclose( $fh ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose
			$state['db_php_started'] = true;
		}

		if ( 'tables' === $phase ) {
			$tables_done = $this->export_database_php_tables_tick( $dest_file, $state, $tables );
			if ( ! $tables_done ) {
				return false;
			}
			$state['db_php_phase'] = 'views';
			$phase                 = 'views';
		}

		if ( 'views' === $phase ) {
			if ( ! $this->export_database_php_objects_tick( $dest_file, $state, 'views' ) ) {
				return false;
			}
			$state['db_php_phase'] = 'routines';
			$phase                 = 'routines';
		}
		if ( 'routines' === $phase ) {
			if ( ! $this->export_database_php_objects_tick( $dest_file, $state, 'routines' ) ) {
				return false;
			}
			$state['db_php_phase'] = 'triggers';
			$phase                 = 'triggers';
		}
		if ( 'triggers' === $phase ) {
			if ( ! $this->export_database_php_objects_tick( $dest_file, $state, 'triggers' ) ) {
				return false;
			}
			$state['db_php_phase'] = 'events';
			$phase                 = 'events';
		}
		if ( 'events' === $phase ) {
			if ( ! $this->export_database_php_objects_tick( $dest_file, $state, 'events' ) ) {
				return false;
			}
		}

		// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fopen
		$fh = fopen( $dest_file, 'ab' );
		if ( $fh ) {
			fwrite( $fh, "\nSET FOREIGN_KEY_CHECKS=1;\n" ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fwrite
			fclose( $fh ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose
		}

		unset(
			$state['db_php_tables'],
			$state['db_php_table_index'],
			$state['db_php_row_offset'],
			$state['db_php_started'],
			$state['db_php_schema_written_idx'],
			$state['db_php_phase'],
			$state['db_php_order_by'],
			$state['db_php_obj_list'],
			$state['db_php_obj_index'],
			$state['db_export_progress_label']
		);

		return true;
	}

	/**
	 * @param list<string>         $tables
	 * @param array<string, mixed> $state
	 */
	private function export_database_php_tables_tick( string $dest_file, array &$state, array $tables ): bool {
		global $wpdb;

		$n_tables = count( $tables );
		$idx      = isset( $state['db_php_table_index'] ) ? (int) $state['db_php_table_index'] : 0;
		$offset   = isset( $state['db_php_row_offset'] ) ? (int) $state['db_php_row_offset'] : 0;
		$sch_idx  = isset( $state['db_php_schema_written_idx'] ) ? (int) $state['db_php_schema_written_idx'] : -1;

		// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fopen
		$fh = fopen( $dest_file, 'ab' );
		if ( ! $fh ) {
			throw new \Exception( __( 'Could not open database export file for appending.', 'wppowersuite-lite' ) );
		}

		$deadline = microtime( true ) + self::DB_PHP_EXPORT_MAX_SECONDS;
		$inserts  = 0;
		$chunk    = self::DB_PHP_EXPORT_ROW_CHUNK;

		try {
			while ( $idx < $n_tables && microtime( true ) < $deadline && $inserts < self::DB_PHP_EXPORT_MAX_INSERTS ) {
				$table = $tables[ $idx ];
				if ( ! preg_match( '/^[A-Za-z0-9_]+$/', $table ) ) {
					// Never skip data silently — the backup would report success while incomplete.
					$this->log_line(
						sprintf(
							/* translators: %s: database table name */
							__( 'WARNING: table "%s" was NOT exported (unsupported characters in table name; PHP export supports letters, digits and underscores only). Use mysqldump or rename the table.', 'wppowersuite-lite' ),
							$table
						)
					);
					++$idx;
					$offset = 0;
					unset( $state['db_php_order_by'] );
					continue;
				}
				if ( $sch_idx !== $idx ) {
					// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name validated
					$create = $wpdb->get_row( "SHOW CREATE TABLE `{$table}`", ARRAY_N );
					if ( ! is_array( $create ) || empty( $create[1] ) ) {
						$this->log_line(
							sprintf(
								/* translators: %s: database table name */
								__( 'WARNING: table "%s" was NOT exported (SHOW CREATE TABLE failed — missing privilege or the table vanished mid-backup).', 'wppowersuite-lite' ),
								$table
							)
						);
						++$idx;
						$offset = 0;
						unset( $state['db_php_order_by'] );
						continue;
					}
					fwrite( $fh, "\nDROP TABLE IF EXISTS `{$table}`;\n" . $create[1] . ";\n\n" ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fwrite
					$sch_idx                            = $idx;
					$state['db_php_schema_written_idx'] = $sch_idx;
					unset( $state['db_php_order_by'] );
				}

				// Stable pagination: without ORDER BY, LIMIT/OFFSET pages can shift while other
				// requests write to the table, silently skipping or duplicating rows.
				if ( ! isset( $state['db_php_order_by'] ) || ! is_string( $state['db_php_order_by'] ) ) {
					$state['db_php_order_by'] = $this->php_export_order_by_clause( $table );
				}
				$order_sql = '' !== $state['db_php_order_by'] ? ' ORDER BY ' . $state['db_php_order_by'] : '';

				// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- identifiers validated
				$rows = $wpdb->get_results(
					$wpdb->prepare( "SELECT * FROM `{$table}`{$order_sql} LIMIT %d OFFSET %d", $chunk, $offset ),
					ARRAY_A
				);
				// phpcs:enable
				if ( ! is_array( $rows ) || count( $rows ) === 0 ) {
					fwrite( $fh, "-- wpps:rowcount {$table} {$offset}\n" ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fwrite
					++$idx;
					$offset = 0;
					unset( $state['db_php_order_by'] );
					continue;
				}

				$cols    = array_keys( $rows[0] );
				$col_sql = '`' . implode( '`,`', array_map( 'esc_sql', $cols ) ) . '`';
				/*
				 * Extended (multi-row) INSERTs, like mysqldump: one statement per row makes
				 * the import an order of magnitude slower (per-statement parse + autocommit
				 * fsync). Statements are capped at 512 KiB to stay far below any
				 * max_allowed_packet setting.
				 */
				$insert_head = "INSERT INTO `{$table}` ({$col_sql}) VALUES\n";
				$tuples      = [];
				$tuple_bytes = 0;
				foreach ( $rows as $row ) {
					$vals = [];
					foreach ( $row as $v ) {
						$vals[] = $this->format_sql_insert_value( $v );
					}
					$tuple = '(' . implode( ',', $vals ) . ')';
					if ( $tuples !== [] && $tuple_bytes + strlen( $tuple ) > 524288 ) {
						fwrite( $fh, $insert_head . implode( ",\n", $tuples ) . ";\n" ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fwrite
						$tuples      = [];
						$tuple_bytes = 0;
					}
					$tuples[]     = $tuple;
					$tuple_bytes += strlen( $tuple );
				}
				if ( $tuples !== [] ) {
					fwrite( $fh, $insert_head . implode( ",\n", $tuples ) . ";\n" ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fwrite
				}
				$row_n    = count( $rows );
				$inserts += $row_n;
				$offset  += $row_n;
				if ( $row_n < $chunk ) {
					// Recorded so the importer can verify nothing was lost in transit.
					fwrite( $fh, "-- wpps:rowcount {$table} {$offset}\n" ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fwrite
					++$idx;
					$offset = 0;
					unset( $state['db_php_order_by'] );
				}
			}
		} finally {
			fclose( $fh ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose
		}

		$state['db_php_table_index']        = $idx;
		$state['db_php_row_offset']         = $offset;
		$state['db_php_schema_written_idx'] = $sch_idx;
		if ( $idx >= $n_tables ) {
			unset( $state['db_php_order_by'] );
		}
		$state['db_export_progress_label']  = sprintf(
			/* translators: 1: table index (1-based), 2: total tables */
			__( 'Exporting database… table %1$d of %2$d (PHP)', 'wppowersuite-lite' ),
			min( $idx + 1, max( 1, $n_tables ) ),
			$n_tables
		);

		return $idx >= $n_tables;
	}

	/**
	 * ORDER BY clause on the primary key for stable LIMIT/OFFSET pagination ('' when no
	 * usable primary key exists — falls back to unordered pages, matching old behaviour).
	 *
	 * @param string $table Validated table name ([A-Za-z0-9_]+ only).
	 */
	private function php_export_order_by_clause( string $table ): string {
		global $wpdb;

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name validated by caller
		$rows = $wpdb->get_results( "SHOW KEYS FROM `{$table}` WHERE Key_name = 'PRIMARY'", ARRAY_A );
		if ( ! is_array( $rows ) || $rows === [] ) {
			return '';
		}
		usort(
			$rows,
			static function ( $a, $b ) {
				return ( (int) ( $a['Seq_in_index'] ?? 0 ) ) <=> ( (int) ( $b['Seq_in_index'] ?? 0 ) );
			}
		);
		$cols = [];
		foreach ( $rows as $row ) {
			$col = isset( $row['Column_name'] ) ? (string) $row['Column_name'] : '';
			if ( ! preg_match( '/^[A-Za-z0-9_]+$/', $col ) ) {
				return '';
			}
			$cols[] = "`{$col}`";
		}

		return implode( ',', $cols );
	}

	/**
	 * Export views / routines / triggers / events (one phase list, resumable by index).
	 *
	 * @param array<string, mixed> $state
	 * @param 'views'|'routines'|'triggers'|'events' $kind
	 */
	private function export_database_php_objects_tick( string $dest_file, array &$state, string $kind ): bool {
		global $wpdb;

		$list_key = 'db_php_obj_list';
		$idx_key  = 'db_php_obj_index';
		$list     = isset( $state[ $list_key ] ) && is_array( $state[ $list_key ] ) ? $state[ $list_key ] : null;

		if ( ! is_array( $list ) ) {
			$list = $this->collect_database_objects_for_export( $kind );
			$state[ $list_key ] = $list;
			$state[ $idx_key ]  = 0;
			if ( $list === [] ) {
				unset( $state[ $list_key ], $state[ $idx_key ] );
				return true;
			}
		}

		$idx      = isset( $state[ $idx_key ] ) ? (int) $state[ $idx_key ] : 0;
		$n        = count( $list );
		$deadline = microtime( true ) + self::DB_PHP_EXPORT_MAX_SECONDS;

		// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fopen
		$fh = fopen( $dest_file, 'ab' );
		if ( ! $fh ) {
			throw new \Exception( __( 'Could not open database export file for appending.', 'wppowersuite-lite' ) );
		}

		try {
			while ( $idx < $n && microtime( true ) < $deadline ) {
				$item = $list[ $idx ];
				if ( ! is_array( $item ) || empty( $item['sql'] ) ) {
					++$idx;
					continue;
				}
				$name = isset( $item['name'] ) ? (string) $item['name'] : '';
				$sql  = (string) $item['sql'];
				if ( $name !== '' ) {
					fwrite( $fh, "\n-- {$kind}: {$name}\n" ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fwrite
				}
				fwrite( $fh, $sql . ( str_ends_with( trim( $sql ), ';' ) ? "\n" : ";\n" ) ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fwrite
				++$idx;
			}
		} finally {
			fclose( $fh ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose
		}

		if ( $idx >= $n ) {
			unset( $state[ $list_key ], $state[ $idx_key ] );
			$state['db_export_progress_label'] = sprintf(
				/* translators: %s: object kind (views, routines, …) */
				__( 'Exported database %s (PHP)', 'wppowersuite-lite' ),
				$kind
			);
			return true;
		}

		$state[ $idx_key ]                 = $idx;
		$state['db_export_progress_label'] = sprintf(
			/* translators: 1: kind, 2: index, 3: total */
			__( 'Exporting database %1$s… %2$d / %3$d (PHP)', 'wppowersuite-lite' ),
			$kind,
			$idx + 1,
			$n
		);

		return false;
	}

	/**
	 * @param 'views'|'routines'|'triggers'|'events' $kind
	 * @return list<array{name: string, sql: string}>
	 */
	private function collect_database_objects_for_export( string $kind ): array {
		global $wpdb;

		$db_name = defined( 'DB_NAME' ) ? (string) DB_NAME : '';
		if ( $db_name === '' ) {
			return [];
		}
		$out = [];

		try {
			if ( 'views' === $kind ) {
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$rows = $wpdb->get_results(
					$wpdb->prepare(
						'SELECT TABLE_NAME FROM information_schema.VIEWS WHERE TABLE_SCHEMA = %s',
						$db_name
					),
					ARRAY_A
				);
				$allowed = array_fill_keys( $this->get_backup_table_list(), true );
				if ( is_array( $rows ) ) {
					foreach ( $rows as $row ) {
						$name = (string) ( $row['TABLE_NAME'] ?? '' );
						if ( $name === '' || ( is_multisite() && ! isset( $allowed[ $name ] ) ) ) {
							continue;
						}
						// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
						$create = $wpdb->get_row( "SHOW CREATE VIEW `{$name}`", ARRAY_N );
						if ( is_array( $create ) && ! empty( $create[1] ) ) {
							$out[] = [
								'name' => $name,
								'sql'  => "DROP VIEW IF EXISTS `{$name}`;\n" . $create[1],
							];
						}
					}
				}
				return $out;
			}

			if ( 'routines' === $kind ) {
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$rows = $wpdb->get_results(
					$wpdb->prepare(
						'SELECT ROUTINE_NAME, ROUTINE_TYPE FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA = %s',
						$db_name
					),
					ARRAY_A
				);
				if ( is_array( $rows ) ) {
					foreach ( $rows as $row ) {
						$name = (string) ( $row['ROUTINE_NAME'] ?? '' );
						$type = strtoupper( (string) ( $row['ROUTINE_TYPE'] ?? '' ) );
						if ( $name === '' || ! in_array( $type, [ 'PROCEDURE', 'FUNCTION' ], true ) ) {
							continue;
						}
						// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
						$create = $wpdb->get_row( "SHOW CREATE {$type} `{$name}`", ARRAY_N );
						// SHOW CREATE PROCEDURE returns Create Procedure at index 2.
						$sql = '';
						if ( is_array( $create ) ) {
							$sql = (string) ( $create[2] ?? $create[1] ?? '' );
						}
						if ( $sql !== '' ) {
							$drop = 'PROCEDURE' === $type ? "DROP PROCEDURE IF EXISTS `{$name}`" : "DROP FUNCTION IF EXISTS `{$name}`";
							$out[] = [
								'name' => $name,
								'sql'  => "DELIMITER ;;\n{$drop};;\n{$sql};;\nDELIMITER ;",
							];
						}
					}
				}
				return $out;
			}

			if ( 'triggers' === $kind ) {
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$rows = $wpdb->get_results(
					$wpdb->prepare(
						'SELECT TRIGGER_NAME, EVENT_OBJECT_TABLE FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA = %s',
						$db_name
					),
					ARRAY_A
				);
				$allowed = array_fill_keys( $this->get_backup_table_list(), true );
				if ( is_array( $rows ) ) {
					foreach ( $rows as $row ) {
						$name  = (string) ( $row['TRIGGER_NAME'] ?? '' );
						$table = (string) ( $row['EVENT_OBJECT_TABLE'] ?? '' );
						if ( $name === '' || ( is_multisite() && $table !== '' && ! isset( $allowed[ $table ] ) ) ) {
							continue;
						}
						// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
						$create = $wpdb->get_row( "SHOW CREATE TRIGGER `{$name}`", ARRAY_N );
						$sql    = is_array( $create ) ? (string) ( $create[2] ?? $create[1] ?? '' ) : '';
						if ( $sql !== '' ) {
							$out[] = [
								'name' => $name,
								'sql'  => "DROP TRIGGER IF EXISTS `{$name}`;\n{$sql}",
							];
						}
					}
				}
				return $out;
			}

			if ( 'events' === $kind ) {
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$rows = $wpdb->get_results(
					$wpdb->prepare(
						'SELECT EVENT_NAME FROM information_schema.EVENTS WHERE EVENT_SCHEMA = %s',
						$db_name
					),
					ARRAY_A
				);
				if ( is_array( $rows ) ) {
					foreach ( $rows as $row ) {
						$name = (string) ( $row['EVENT_NAME'] ?? '' );
						if ( $name === '' ) {
							continue;
						}
						// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
						$create = $wpdb->get_row( "SHOW CREATE EVENT `{$name}`", ARRAY_N );
						$sql    = is_array( $create ) ? (string) ( $create[3] ?? $create[1] ?? '' ) : '';
						if ( $sql !== '' ) {
							$out[] = [
								'name' => $name,
								'sql'  => "DROP EVENT IF EXISTS `{$name}`;\n{$sql}",
							];
						}
					}
				}
			}
		} catch ( \Throwable $e ) {
			$this->log_line( sprintf( 'Skipping %s export (privilege or schema error): %s', $kind, $e->getMessage() ) );
			return [];
		}

		return $out;
	}

}
