<?php
/**
 * Database dump, staged restore, search/replace and table swap.
 *
 * Dump format: one SQL statement per line. Table names use the placeholder
 * prefix {{MNSM}} so the archive can be restored under any table prefix.
 * Restores go into temporary "mnsmtmp_" tables first; the live tables are only
 * touched by a single RENAME TABLE at the very end.
 */

defined( 'ABSPATH' ) || exit;

// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders, WordPress.DB.DirectDatabaseQuery, PluginCheck.Security.DirectDB, WordPress.WP.AlternativeFunctions
// Reason: this class dumps and restores arbitrary tables, so results must not
// be cached. All queries go through $wpdb; every query built here uses
// $wpdb->prepare() with %i for identifiers (the number of placeholders depends
// on the table's columns, hence PreparedSQLPlaceholders). The only unprepared
// statements are lines of the plugin's own dump, which restore_step() limits
// to DROP/CREATE/INSERT on the temporary prefix. Dump lines are pure ASCII
// (non-ASCII values are hex-encoded) so wpdb::query() runs them unchanged.
// The SQL dump can be gigabytes, so it is streamed line by line with fopen()/
// fgets() rather than loaded whole through WP_Filesystem.

final class Marchnote_Database {

	const PLACEHOLDER = '{{MNSM}}';
	const TMP_PREFIX  = 'mnsmtmp_';
	const OLD_PREFIX  = 'mnsmold_';
	const ROWS        = 500;
	const STMT_BYTES  = 1000000;

	/* ------------------------------------------------------------------ */
	/* Helpers                                                             */
	/* ------------------------------------------------------------------ */

	/**
	 * Run a statement through $wpdb, throwing on failure. Errors are not
	 * printed, so they cannot corrupt an AJAX or WP-CLI response.
	 */
	public static function exec( $sql ) {
		global $wpdb;
		$suppress = $wpdb->suppress_errors( true );
		$ok       = $wpdb->query( $sql );
		$wpdb->suppress_errors( $suppress );
		if ( false === $ok ) {
			throw new RuntimeException( 'Database error: ' . esc_html( $wpdb->last_error ) );
		}
	}

	/**
	 * Escape a string for the dump file, which is executed later by exec().
	 */
	private static function escape( $value ) {
		return strtr(
			$value,
			array(
				'\\'   => '\\\\',
				"\0"   => '\\0',
				"\n"   => '\\n',
				"\r"   => '\\r',
				"'"    => "\\'",
				'"'    => '\\"',
				"\x1a" => '\\Z',
			)
		);
	}

	/**
	 * Quote an identifier for the dump file. Live queries use %i instead.
	 */
	private static function ident( $name ) {
		return '`' . str_replace( '`', '``', $name ) . '`';
	}

	/**
	 * A comma-separated %i placeholder per name, for $wpdb->prepare().
	 */
	private static function ids( array $names ) {
		return implode( ', ', array_fill( 0, count( $names ), '%i' ) );
	}

	private static function session() {
		self::exec( "SET SESSION sql_mode = 'NO_AUTO_VALUE_ON_ZERO'" );
		self::exec( 'SET SESSION foreign_key_checks = 0' );
		self::exec( 'SET SESSION unique_checks = 0' );
	}

	/**
	 * Base tables whose names start with $prefix.
	 */
	public static function tables( $prefix ) {
		global $wpdb;
		$out = array();
		foreach ( (array) $wpdb->get_results( 'SHOW FULL TABLES', ARRAY_N ) as $row ) {
			if ( 'BASE TABLE' === $row[1] && 0 === strpos( $row[0], $prefix ) ) {
				$out[] = $row[0];
			}
		}
		sort( $out );
		return $out;
	}

	/**
	 * Column types, primary key and the column usable for keyset pagination.
	 */
	private static function meta( $table ) {
		global $wpdb;
		$cols = array();
		$pk   = array();
		foreach ( (array) $wpdb->get_results( $wpdb->prepare( 'SHOW COLUMNS FROM %i', $table ), ARRAY_A ) as $c ) {
			$cols[ $c['Field'] ] = strtolower( $c['Type'] );
			if ( 'PRI' === $c['Key'] ) {
				$pk[] = $c['Field'];
			}
		}
		$keyset = ( 1 === count( $pk ) && preg_match( '/int/', $cols[ $pk[0] ] ) ) ? $pk[0] : null;
		return array(
			'cols'   => $cols,
			'pk'     => $pk,
			'keyset' => $keyset,
		);
	}

	/**
	 * Prepared SELECT for one page of rows.
	 *
	 * @param string[] $cols  Columns to select; empty for all.
	 * @param array[]  $conds Conditions as array( sql_with_placeholders, ...args ).
	 */
	private static function page_sql( $table, array $cols, array $meta, array $conds, $state ) {
		global $wpdb;
		$sql   = 'SELECT ' . ( $cols ? self::ids( $cols ) : '*' ) . ' FROM %i';
		$args  = array_merge( $cols, array( $table ) );
		$where = array();
		foreach ( $conds as $c ) {
			$where[] = array_shift( $c );
			$args    = array_merge( $args, $c );
		}
		if ( $meta['keyset'] && null !== $state['last'] ) {
			$where[] = '%i > %d';
			$args[]  = $meta['keyset'];
			$args[]  = (int) $state['last'];
		}
		if ( $where ) {
			$sql .= ' WHERE ' . implode( ' AND ', $where );
		}
		if ( $meta['keyset'] ) {
			$sql   .= ' ORDER BY %i LIMIT %d';
			$args[] = $meta['keyset'];
			$args[] = self::ROWS;
		} else {
			if ( $meta['pk'] ) {
				$sql .= ' ORDER BY ' . self::ids( $meta['pk'] );
				$args = array_merge( $args, $meta['pk'] );
			}
			$sql   .= ' LIMIT %d, %d';
			$args[] = (int) $state['offset'];
			$args[] = self::ROWS;
		}
		return $wpdb->prepare( $sql, $args );
	}

	/* ------------------------------------------------------------------ */
	/* Export                                                              */
	/* ------------------------------------------------------------------ */

	public static function dump_init() {
		global $wpdb;
		$charset = $wpdb->charset ? $wpdb->charset : $wpdb->get_var( 'SELECT @@character_set_connection' );
		return array(
			'tables'  => self::tables( $wpdb->prefix ),
			'charset' => preg_match( '/^\w+$/', (string) $charset ) ? $charset : 'utf8mb4',
			'ti'      => 0,
			'last'    => null,
			'offset'  => 0,
			'header'  => false,
			'size'    => 0,
			'rows'    => 0,
		);
	}

	/**
	 * Dump as much as fits before $deadline. Returns true when finished.
	 */
	public static function dump_step( array &$st, $file, array $opts, $deadline ) {
		global $wpdb;
		$fh = fopen( $file, 'c+b' );
		if ( ! $fh ) {
			throw new RuntimeException( 'Could not open the database dump file.' );
		}
		ftruncate( $fh, $st['size'] );
		fseek( $fh, $st['size'] );

		while ( $st['ti'] < count( $st['tables'] ) ) {
			$table = $st['tables'][ $st['ti'] ];
			$alias = self::PLACEHOLDER . substr( $table, strlen( $wpdb->prefix ) );

			if ( ! $st['header'] ) {
				$create = $wpdb->get_row( $wpdb->prepare( 'SHOW CREATE TABLE %i', $table ), ARRAY_N );
				if ( ! $create ) {
					throw new RuntimeException( 'Could not read the structure of table ' . esc_html( $table ) . '.' );
				}
				$ddl = preg_replace( '/^CREATE TABLE `[^`]+`/', 'CREATE TABLE ' . self::ident( $alias ), $create[1], 1 );
				$ddl = str_replace( array( "\r\n", "\n", "\r" ), ' ', $ddl );
				fwrite( $fh, 'DROP TABLE IF EXISTS ' . self::ident( $alias ) . ";\n" . $ddl . ";\n" );
				$st['header'] = true;
			}

			$meta  = self::meta( $table );
			$names = array_keys( $meta['cols'] );
			$cols  = '(' . implode( ', ', array_map( array( __CLASS__, 'ident' ), $names ) ) . ')';
			$conds = self::dump_conditions( $table, $opts );

			do {
				$rows = $wpdb->get_results( self::page_sql( $table, array(), $meta, $conds, $st ), ARRAY_N );
				if ( $wpdb->last_error ) {
					throw new RuntimeException( 'Could not read table ' . esc_html( $table . ': ' . $wpdb->last_error ) );
				}
				if ( ! $rows ) {
					break;
				}
				$buf = '';
				foreach ( $rows as $row ) {
					$vals = array();
					foreach ( $row as $k => $v ) {
						$vals[] = self::encode( $v, $meta['cols'][ $names[ $k ] ], $st['charset'] );
					}
					$tuple = '(' . implode( ',', $vals ) . ')';
					if ( '' !== $buf && strlen( $buf ) + strlen( $tuple ) > self::STMT_BYTES ) {
						fwrite( $fh, $buf . ";\n" );
						$buf = '';
					}
					$buf .= ( '' === $buf ? 'INSERT INTO ' . self::ident( $alias ) . " $cols VALUES " : ',' ) . $tuple;
				}
				fwrite( $fh, $buf . ";\n" );

				$n             = count( $rows );
				$st['rows']   += $n;
				$st['offset'] += $n;
				if ( $meta['keyset'] ) {
					$st['last'] = $rows[ $n - 1 ][ array_search( $meta['keyset'], $names, true ) ];
				}
				fflush( $fh );
				$st['size'] = ftell( $fh );

				if ( microtime( true ) > $deadline ) {
					fclose( $fh );
					return false;
				}
			} while ( count( $rows ) === self::ROWS );

			$st['ti']++;
			$st['last']   = null;
			$st['offset'] = 0;
			$st['header'] = false;
			fflush( $fh );
			$st['size'] = ftell( $fh );
		}
		fclose( $fh );
		return true;
	}

	private static function dump_conditions( $table, array $opts ) {
		global $wpdb;
		$c = array();
		if ( $table === $wpdb->posts && ! empty( $opts['no_revisions'] ) ) {
			$c[] = array( '%i <> %s', 'post_type', 'revision' );
		}
		if ( $table === $wpdb->comments && ! empty( $opts['no_spam'] ) ) {
			$c[] = array( '%i <> %s', 'comment_approved', 'spam' );
		}
		if ( $table === $wpdb->options && ! empty( $opts['no_transients'] ) ) {
			$c[] = array( '%i NOT LIKE %s', 'option_name', $wpdb->esc_like( '_transient_' ) . '%' );
			$c[] = array( '%i NOT LIKE %s', 'option_name', $wpdb->esc_like( '_site_transient_' ) . '%' );
		}
		return $c;
	}

	/**
	 * SQL literal for the dump. Non-ASCII text is written as a hex literal
	 * with a charset introducer (e.g. _utf8mb4 0xC3A9): the line stays pure
	 * ASCII for wpdb::query() and MySQL still converts it to the column's
	 * charset on restore.
	 */
	private static function encode( $v, $type, $charset ) {
		if ( null === $v ) {
			return 'NULL';
		}
		if ( preg_match( '/blob|binary|^bit|geometry|point|polygon|linestring/', $type ) ) {
			return '' === $v ? "''" : '0x' . bin2hex( $v );
		}
		if ( preg_match( '/[^\x00-\x7F]/', $v ) ) {
			return '_' . $charset . ' 0x' . bin2hex( $v );
		}
		return "'" . self::escape( $v ) . "'";
	}

	/* ------------------------------------------------------------------ */
	/* Import                                                              */
	/* ------------------------------------------------------------------ */

	public static function drop_tmp_tables() {
		global $wpdb;
		foreach ( array( self::TMP_PREFIX, self::OLD_PREFIX ) as $p ) {
			foreach ( self::tables( $p ) as $t ) {
				self::exec( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', $t ) );
			}
		}
	}

	public static function restore_init( $file ) {
		self::drop_tmp_tables();
		global $wpdb;
		$has_0900 = (bool) $wpdb->get_var( $wpdb->prepare( 'SHOW COLLATION LIKE %s', 'utf8mb4_0900_ai_ci' ) );
		return array(
			'pos'      => 0,
			'size'     => (int) filesize( $file ),
			'has_0900' => $has_0900,
			'stmts'    => 0,
		);
	}

	/**
	 * Execute statements from the dump into mnsmtmp_ tables. True when done.
	 */
	public static function restore_step( array &$st, $file, $deadline ) {
		self::session();
		$fh = fopen( $file, 'rb' );
		if ( ! $fh ) {
			throw new RuntimeException( 'Could not open the database dump.' );
		}
		fseek( $fh, $st['pos'] );
		$re = '/^(DROP TABLE IF EXISTS|CREATE TABLE|INSERT INTO) `' . preg_quote( self::PLACEHOLDER, '/' ) . '/';

		while ( false !== ( $line = fgets( $fh ) ) ) {
			$line = rtrim( $line, "\r\n" );
			if ( '' !== $line ) {
				// Only the three statement types we write are accepted.
				if ( ! preg_match( $re, substr( $line, 0, 48 ), $m ) ) {
					throw new RuntimeException( 'The database dump contains an unexpected statement.' );
				}
				$line = $m[1] . ' `' . self::TMP_PREFIX . substr( $line, strlen( $m[0] ) );
				if ( 'CREATE TABLE' === $m[1] && ! $st['has_0900'] ) {
					$line = str_replace( 'utf8mb4_0900_ai_ci', 'utf8mb4_unicode_520_ci', $line );
				}
				self::exec( $line );
				$st['stmts']++;
			}
			$st['pos'] = ftell( $fh );
			if ( microtime( true ) > $deadline ) {
				fclose( $fh );
				return false;
			}
		}
		fclose( $fh );
		return true;
	}

	public static function replace_init() {
		return array(
			'tables'  => self::tables( self::TMP_PREFIX ),
			'ti'      => 0,
			'last'    => null,
			'offset'  => 0,
			'changed' => 0,
		);
	}

	/**
	 * Run search/replace over mnsmtmp_ tables. True when done.
	 */
	public static function replace_step( array &$st, Marchnote_Replacer $rep, $deadline ) {
		global $wpdb;
		self::session();

		while ( $st['ti'] < count( $st['tables'] ) ) {
			$table = $st['tables'][ $st['ti'] ];
			$meta  = self::meta( $table );
			$text  = array();
			foreach ( $meta['cols'] as $name => $type ) {
				if ( preg_match( '/char|text|json/', $type ) && ! in_array( $name, $meta['pk'], true ) ) {
					$text[] = $name;
				}
			}

			if ( $meta['pk'] && $text ) {
				$select = array_merge( $meta['pk'], $text );
				do {
					$rows = $wpdb->get_results( self::page_sql( $table, $select, $meta, array(), $st ), ARRAY_A );
					if ( ! $rows ) {
						break;
					}
					foreach ( $rows as $row ) {
						$set  = array();
						$args = array( $table );
						foreach ( $text as $col ) {
							if ( null === $row[ $col ] ) {
								continue;
							}
							$new = $rep->replace( $row[ $col ] );
							if ( $new !== $row[ $col ] ) {
								$set[]  = '%i = %s';
								$args[] = $col;
								$args[] = $new;
							}
						}
						if ( $set ) {
							$where = array();
							foreach ( $meta['pk'] as $k ) {
								$where[] = '%i = %s';
								$args[]  = $k;
								$args[]  = (string) $row[ $k ];
							}
							self::exec( $wpdb->prepare( 'UPDATE %i SET ' . implode( ', ', $set ) . ' WHERE ' . implode( ' AND ', $where ), $args ) );
							$st['changed']++;
						}
					}
					$n             = count( $rows );
					$st['offset'] += $n;
					if ( $meta['keyset'] ) {
						$st['last'] = $rows[ $n - 1 ][ $meta['keyset'] ];
					}
					if ( microtime( true ) > $deadline ) {
						return false;
					}
				} while ( self::ROWS === $n );
			}

			$st['ti']++;
			$st['last']   = null;
			$st['offset'] = 0;
		}
		return true;
	}

	/**
	 * Swap the restored tables in and repair prefix-dependent keys.
	 */
	public static function swap( $source_prefix, array $keep ) {
		global $wpdb;
		self::session();
		$prefix = $wpdb->prefix;
		$tmp    = self::tables( self::TMP_PREFIX );
		$opts   = self::TMP_PREFIX . 'options';

		if ( ! in_array( $opts, $tmp, true ) || ! $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE option_name = %s', $opts, 'siteurl' ) ) ) {
			throw new RuntimeException( 'The imported database has no options table; the live site was left untouched.' );
		}

		// Role and capability keys embed the table prefix.
		if ( $source_prefix && $source_prefix !== $prefix ) {
			self::exec( $wpdb->prepare( 'UPDATE %i SET option_name = %s WHERE option_name = %s', $opts, $prefix . 'user_roles', $source_prefix . 'user_roles' ) );
			$um = self::TMP_PREFIX . 'usermeta';
			if ( in_array( $um, $tmp, true ) ) {
				self::exec(
					$wpdb->prepare(
						'UPDATE %i SET meta_key = CONCAT(%s, SUBSTRING(meta_key, %d)) WHERE meta_key LIKE %s',
						$um,
						$prefix,
						strlen( $source_prefix ) + 1,
						$wpdb->esc_like( $source_prefix ) . '%'
					)
				);
			}
		}

		// Values that must reflect the destination, not the source.
		foreach ( $keep as $name => $value ) {
			self::exec( $wpdb->prepare( 'DELETE FROM %i WHERE option_name = %s', $opts, $name ) );
			self::exec( $wpdb->prepare( 'INSERT INTO %i (option_name, option_value, autoload) VALUES (%s, %s, %s)', $opts, $name, maybe_serialize( $value ), 'yes' ) );
		}
		self::exec( $wpdb->prepare( 'DELETE FROM %i WHERE option_name = %s', $opts, 'rewrite_rules' ) );

		// One RENAME TABLE statement is atomic: the site is never half-swapped.
		$args = array();
		foreach ( self::tables( $prefix ) as $t ) {
			array_push( $args, $t, self::OLD_PREFIX . substr( $t, strlen( $prefix ) ) );
		}
		foreach ( $tmp as $t ) {
			array_push( $args, $t, $prefix . substr( $t, strlen( self::TMP_PREFIX ) ) );
		}
		$renames = implode( ', ', array_fill( 0, count( $args ) / 2, '%i TO %i' ) );
		self::exec( $wpdb->prepare( 'RENAME TABLE ' . $renames, $args ) );

		foreach ( self::tables( self::OLD_PREFIX ) as $t ) {
			self::exec( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', $t ) );
		}
		wp_cache_flush();
	}
}