<?php
/**
 * Database export engine.
 *
 * @package SpinxQuickMigrate
 */

namespace SpinxQM;

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

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Export engine: this class is a low-level SQL dumper for arbitrary tables. Direct $wpdb access has no Core API equivalent, dumps must read LIVE/uncached data, and the emitted DROP/CREATE statements are intentional. All identifiers/values are prepared (%i/%s).
/**
 * Streams selected database tables to a resumable plain SQL dump, applying find/replace rules.
 */
class Database_Exporter {
	/**
	 * Estimated on-disk size (bytes) of the given tables, used to seed the
	 * progress total before the dump starts.
	 *
	 * @param array<int,string> $tables Table names to measure.
	 * @return int
	 */
	public static function estimated_size( array $tables ): int {
		$tables = Security::sanitize_tables( $tables );
		if ( empty( $tables ) ) {
			return 0;
		}
		return (int) array_sum( self::table_sizes( $tables ) );
	}

	/**
	 * Smooth, byte-based progress size: the estimated size of every table already
	 * fully dumped, PLUS a row-proportional slice of the table currently being
	 * dumped. This makes the progress bar climb continuously as rows stream out,
	 * instead of sitting flat across a large table and then jumping when it
	 * finishes (the old behaviour, which only counted whole completed tables).
	 *
	 * @param array<string,int> $sizes     Estimated bytes per table.
	 * @param array<int,string> $tables    Ordered table list.
	 * @param int               $committed Bytes of the tables already fully dumped.
	 * @param int               $ti        Index of the table currently being dumped.
	 * @param int               $offset    Rows already dumped from the current table.
	 * @param int               $count     Total rows in the current table (<=0 = unknown/none).
	 */
	private static function progress_size( array $sizes, array $tables, int $committed, int $ti, int $offset, int $count ): int {
		if ( $ti < count( $tables ) && $count > 0 ) {
			$frac       = min( 1.0, max( 0.0, $offset / $count ) );
			$committed += (int) round( ( $sizes[ $tables[ $ti ] ] ?? 0 ) * $frac );
		}
		return $committed;
	}

	/**
	 * Resumable SQL dump. Processes a bounded slice of work per call — yielding
	 * on the per-tick time budget, a cancel, or a pause — and appends *plain*
	 * SQL to $sql_path (gzip is applied later in a single pass via compress_file,
	 * so the dump itself is trivially resumable across cron ticks). Returns the
	 * updated checkpoint so the next tick continues from the same table/offset.
	 *
	 * @param array               $args    Export args (tables, rules, advanced).
	 * @param string|null         $job_id  Job to report progress / honour cancel+pause.
	 * @param string              $sql_path Absolute path to the plain .sql file.
	 * @param array<string,mixed> $cp      Checkpoint: {ti,offset,sizes,done_size,header_done,count}.
	 * @return array{ok:bool,done:bool,cancelled:bool,done_size:int,checkpoint:array<string,mixed>,message?:string}
	 */
	public static function dump_batch( array $args, ?string $job_id, string $sql_path, array $cp ): array {
		global $wpdb;

		$settings   = Helpers::get_settings();
		$batch_size = max( 100, absint( $settings['sql_rows_per_batch'] ) );

		// Validated table list and compiled rules are computed once at job start
		// and carried in the checkpoint, so a step never re-runs SHOW TABLES or
		// re-parses rules. Fall back to computing them if absent (older jobs).
		$tables = ( isset( $cp['tables'] ) && is_array( $cp['tables'] ) && $cp['tables'] )
			? array_values( $cp['tables'] )
			: Security::sanitize_tables( $args['tables'] ?? array() );
		if ( empty( $tables ) ) {
			return array(
				'ok'         => false,
				'done'       => true,
				'cancelled'  => false,
				'done_size'  => 0,
				'checkpoint' => $cp,
				'message'    => __( 'No valid tables selected.', 'spinx-quick-migrate' ),
			);
		}
		$rules = ( array_key_exists( 'rules', $cp ) && is_array( $cp['rules'] ) ) ? $cp['rules'] : self::build_rules( $args );

		$total       = count( $tables );
		$ti          = (int) ( $cp['ti'] ?? 0 );
		$offset      = (int) ( $cp['offset'] ?? 0 );
		$sizes       = isset( $cp['sizes'] ) && is_array( $cp['sizes'] ) ? $cp['sizes'] : self::table_sizes( $tables );
		$done_size   = (int) ( $cp['done_size'] ?? 0 );
		$header_done = ! empty( $cp['header_done'] );
		$count       = isset( $cp['count'] ) ? (int) $cp['count'] : -1;

		// Post-type filter (when the export is limited to selected types). wp_posts
		// is filtered by post_type; wp_postmeta/wp_comments by the included-post-ID
		// set built at job init (Rest_Controller::build_post_filter). A missing set
		// (false) fails open — no meta filtering — rather than dropping everything.
		$selected_types = array();
		$post_filter    = null;
		if ( 'selected' === (string) ( $args['post_types_mode'] ?? 'all' ) ) {
			foreach ( (array) ( $args['post_types'] ?? array() ) as $pt_name ) {
				$pt_name = (string) $pt_name;
				if ( '' !== $pt_name ) {
					$selected_types[] = $pt_name;
				}
			}
			if ( ! empty( $selected_types ) && $job_id ) {
				$cached      = get_transient( 'spinxqm_job_postids_' . $job_id );
				$post_filter = is_array( $cached ) ? $cached : null;
			} else {
				$selected_types = array();
			}
		}

		try {
			$spl = new \SplFileObject( $sql_path, $header_done ? 'ab' : 'wb' );
		} catch ( \RuntimeException $e ) {
			return array(
				'ok'         => false,
				'done'       => true,
				'cancelled'  => false,
				'done_size'  => $done_size,
				'checkpoint' => $cp,
				'message'    => __( 'Unable to create export file.', 'spinx-quick-migrate' ),
			);
		}

		// Byte position at the start of this step — the loop yields once it has
		// written roughly chunk_bytes() of SQL (the primary per-request limiter).
		$spl->fseek( 0, SEEK_END );
		$start_pos = (int) $spl->ftell();
		$max_bytes = Batch_Settings::chunk_bytes( 'database' );

		if ( ! $header_done ) {
			self::write_chunk( $spl, null, '-- SPINX Quick Migrate SQL Export ' . gmdate( 'c' ) );
			self::write_chunk( $spl, null, 'SET FOREIGN_KEY_CHECKS=0;' );
			$header_done = true;
		}

		$deadline  = time() + Batch_Settings::time_budget( 'database' );
		$cancelled = false;

		while ( $ti < $total ) {
			if ( Helpers::is_job_cancelled( $job_id ) ) {
				$cancelled = true;
				break;
			}
			if ( Helpers::is_job_paused( $job_id ) ) {
				break; // Yield; resume continues from this checkpoint.
			}

			$table = $tables[ $ti ];

			if ( 0 === $offset ) {
				$create = $wpdb->get_row( $wpdb->prepare( 'SHOW CREATE TABLE %i', $table ), ARRAY_N );
				if ( empty( $create[1] ) ) {
					$done_size += (int) ( $sizes[ $table ] ?? 0 );
					++$ti;
					$offset = 0;
					$count  = -1;
					continue;
				}
				self::write_chunk( $spl, null, '' );
				self::write_chunk( $spl, null, '-- Table: ' . $table );
				self::write_chunk( $spl, null, 'DROP TABLE IF EXISTS `' . $table . '`;' );
				self::write_chunk( $spl, null, $create[1] . ';' );
				$count = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i', $table ) );
			}

			if ( $count < 0 ) {
				$count = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i', $table ) );
			}

			if ( $offset < $count ) {
				$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i LIMIT %d OFFSET %d', $table, $batch_size, $offset ), ARRAY_A );
				if ( ! empty( $rows ) ) {
					self::write_insert_batch( $spl, null, $table, $rows, $rules, $args, $selected_types, $post_filter );
				}
				$offset += $batch_size;
			}

			if ( $offset >= $count ) {
				$done_size += (int) ( $sizes[ $table ] ?? 0 );
				++$ti;
				$offset = 0;
				$count  = -1;
			}

			if ( $job_id ) {
				$current = $ti < $total ? $tables[ $ti ] : '';
				Helpers::update_job(
					$job_id,
					array(
						'status'         => 'running',
						/* translators: %s: database table name currently being saved */
						'message'        => '' !== $current ? sprintf( __( 'Saving %s', 'spinx-quick-migrate' ), $current ) : __( 'Saving database…', 'spinx-quick-migrate' ),
						'current_item'   => $current,
						'processed'      => $ti,
						'total'          => $total,
						'processed_size' => self::progress_size( $sizes, $tables, $done_size, $ti, $offset, $count ),
					)
				);
			}

			// Yield once this step has produced ~chunk_bytes of SQL, or the soft
			// time budget elapsed. The browser fires the next step immediately.
			if ( ( (int) $spl->ftell() - $start_pos ) >= $max_bytes || time() >= $deadline ) {
				break;
			}
		}

		$done = ( $ti >= $total );
		if ( $done ) {
			self::write_chunk( $spl, null, 'SET FOREIGN_KEY_CHECKS=1;' );
		}
		self::finish_file( $spl, null );

		return array(
			'ok'         => true,
			'done'       => $done,
			'cancelled'  => $cancelled,
			'done_size'  => self::progress_size( $sizes, $tables, $done_size, $ti, $offset, $count ),
			'checkpoint' => array(
				'ti'          => $ti,
				'offset'      => $offset,
				'sizes'       => $sizes,
				'done_size'   => $done_size,
				'header_done' => $header_done,
				'count'       => $count,
				'tables'      => $tables,
				'rules'       => $rules,
			),
		);
	}

	/**
	 * Compress a plain file to a single-member gzip in one streaming pass (no
	 * memory buffering, standard .gz that every importer reads). Used to gzip a
	 * resumable dump at finalize. Level defaults to 6 (the balanced zlib default).
	 *
	 * @param string $plain_path Absolute path to the uncompressed source file.
	 * @param string $gz_path    Absolute path of the .gz file to write.
	 * @param int    $level      Compression level (clamped to 1–9).
	 * @return bool True on success, false if a file could not be opened.
	 */
	public static function compress_file( string $plain_path, string $gz_path, int $level = 6 ): bool {
		if ( ! file_exists( $plain_path ) ) {
			return false;
		}
		try {
			$in  = new \SplFileObject( $plain_path, 'rb' );
			$out = new \SplFileObject( $gz_path, 'wb' );
		} catch ( \RuntimeException $e ) {
			return false;
		}
		$ctx = deflate_init( ZLIB_ENCODING_GZIP, array( 'level' => max( 1, min( 9, $level ) ) ) );
		while ( ! $in->eof() ) {
			$chunk = $in->fread( 262144 );
			if ( false === $chunk || '' === $chunk ) {
				break;
			}
			$out->fwrite( deflate_add( $ctx, $chunk, ZLIB_NO_FLUSH ) );
		}
		$out->fwrite( deflate_add( $ctx, '', ZLIB_FINISH ) );
		$out->fflush();
		return true;
	}

	/**
	 * Write one batch insert.
	 *
	 * @param \SplFileObject       $spl            Open export file handle.
	 * @param \DeflateContext|null $deflate        Incremental gzip context, or null for plain output.
	 * @param string               $table          Table the rows belong to.
	 * @param array<int,array>     $rows           Rows fetched for this batch (ARRAY_A).
	 * @param array<int,array>     $rules          Compiled find/replace rules.
	 * @param array<string,mixed>  $args           Export args (advanced exclusion options).
	 * @param array<int,string>    $selected_types Post types to keep (empty = no post-type filter).
	 * @param array<int,int>|null  $post_filter    Map of included post IDs (id => 1), or null for none.
	 * @return void
	 */
	private static function write_insert_batch( \SplFileObject $spl, ?\DeflateContext $deflate, string $table, array $rows, array $rules, array $args, array $selected_types = array(), ?array $post_filter = null ): void {
		if ( empty( $rows ) ) {
			return;
		}
		$columns       = array_keys( $rows[0] );
		$quoted_cols   = array_map(
			static function ( $column ) {
				return '`' . $column . '`';
			},
			$columns
		);
		$prefix        = 'INSERT INTO `' . $table . '` (' . implode( ',', $quoted_cols ) . ') VALUES ';
		$cap           = Batch_Settings::max_insert_bytes();
		$rows_sql      = array();
		$buf_bytes     = 0;
		$replace_stats = array();

		foreach ( $rows as $row ) {
			if ( self::should_skip_row( $table, $row, $args, $selected_types, $post_filter ) ) {
				continue;
			}
			$values = array();
			foreach ( $columns as $column ) {
				$value = $row[ $column ];
				if ( is_string( $value ) && ! empty( $rules ) ) {
					$value = Serialized_Replacer::replace_value( $value, $rules, $replace_stats );
				}
				$values[] = self::sql_value( $value );
			}
			$tuple = '(' . implode( ',', $values ) . ')';
			// Keep each INSERT under the byte cap so it never exceeds max_allowed_packet
			// on import (and the importer's statement buffer stays bounded). Flush the
			// rows gathered so far before a tuple would push past the cap.
			if ( ! empty( $rows_sql ) && ( $buf_bytes + strlen( $tuple ) + 1 ) > $cap ) {
				self::write_chunk( $spl, $deflate, $prefix . implode( ',', $rows_sql ) . ';' );
				$rows_sql  = array();
				$buf_bytes = 0;
			}
			$rows_sql[] = $tuple;
			$buf_bytes += strlen( $tuple ) + 1;
		}
		if ( ! empty( $rows_sql ) ) {
			self::write_chunk( $spl, $deflate, $prefix . implode( ',', $rows_sql ) . ';' );
		}
	}

	/**
	 * Check row-level exclusion options.
	 *
	 * @param string              $table          Table the row belongs to.
	 * @param array<string,mixed> $row            Row data keyed by column name.
	 * @param array<string,mixed> $args           Export args (advanced exclusion options).
	 * @param array<int,string>   $selected_types Post types to keep (empty = no post-type filter).
	 * @param array<int,int>|null $post_filter    Map of included post IDs (id => 1) for meta/comment
	 *                                            tables; null = no post-type filter on those tables.
	 * @return bool True when the row should be left out of the dump.
	 */
	private static function should_skip_row( string $table, array $row, array $args, array $selected_types = array(), ?array $post_filter = null ): bool {
		// Post-type filter (only when the user limited the export to specific types).
		// wp_posts is filtered by its own post_type; wp_postmeta / wp_comments are
		// filtered by whether their parent post survived (the included-ID set).
		if ( ! empty( $selected_types ) || null !== $post_filter ) {
			global $wpdb;
			if ( $table === $wpdb->posts ) {
				if ( ! empty( $selected_types ) && isset( $row['post_type'] ) && ! in_array( (string) $row['post_type'], $selected_types, true ) ) {
					return true;
				}
			} elseif ( $table === $wpdb->postmeta ) {
				if ( null !== $post_filter && isset( $row['post_id'] ) && ! isset( $post_filter[ (int) $row['post_id'] ] ) ) {
					return true;
				}
			} elseif ( $table === $wpdb->comments ) {
				if ( null !== $post_filter && isset( $row['comment_post_ID'] ) && ! isset( $post_filter[ (int) $row['comment_post_ID'] ] ) ) {
					return true;
				}
			}
		}

		$adv      = isset( $args['advanced'] ) && is_array( $args['advanced'] ) ? $args['advanced'] : array();
		$table_lc = strtolower( $table );

		if ( ! empty( $adv['exclude_spam_comments'] ) && false !== strpos( $table_lc, 'comments' ) ) {
			if ( isset( $row['comment_approved'] ) && 'spam' === strtolower( (string) $row['comment_approved'] ) ) {
				return true;
			}
		}

		if ( ! empty( $adv['exclude_transients'] ) && false !== strpos( $table_lc, 'options' ) ) {
			if ( isset( $row['option_name'] ) ) {
				$name = (string) $row['option_name'];
				if ( 0 === strpos( $name, '_transient_' ) || 0 === strpos( $name, '_site_transient_' ) ) {
					return true;
				}
			}
		}

		if ( ! empty( $adv['exclude_revisions'] ) && false !== strpos( $table_lc, 'posts' ) ) {
			if ( isset( $row['post_type'] ) && 'revision' === (string) $row['post_type'] ) {
				return true;
			}
		}

		return false;
	}

	/**
	 * Build export find/replace rules.
	 *
	 * @param array<string,mixed> $args Export args holding `standard_rules` / `custom_rules`.
	 * @return array<int,array> Rules as {find, replace, case_sensitive}.
	 */
	private static function build_rules( array $args ): array {
		$rules = array();
		foreach ( array( 'standard_rules', 'custom_rules' ) as $bucket ) {
			if ( empty( $args[ $bucket ] ) || ! is_array( $args[ $bucket ] ) ) {
				continue;
			}
			foreach ( $args[ $bucket ] as $rule ) {
				if ( ! is_array( $rule ) ) {
					continue;
				}
				$find    = isset( $rule['find'] ) ? (string) $rule['find'] : '';
				$replace = isset( $rule['replace'] ) ? (string) $rule['replace'] : '';
				// Run a rule only when BOTH source and destination are set. A blank
				// source or destination is skipped so the export never overwrites
				// database values with empty data.
				if ( '' === trim( $find ) || '' === trim( $replace ) ) {
					continue;
				}
				$rules[] = array(
					'find'           => $find,
					'replace'        => $replace,
					'case_sensitive' => ! empty( $rule['case_sensitive'] ),
				);
			}
		}
		return $rules;
	}

	/**
	 * Estimated on-disk byte size per table (data + index length) from
	 * information_schema, used to drive the processed/total size readout.
	 *
	 * @param array<int,string> $tables Table names to measure.
	 * @return array<string,int>
	 */
	private static function table_sizes( array $tables ): array {
		global $wpdb;
		$out = array();
		foreach ( $tables as $table ) {
			$bytes         = $wpdb->get_var(
				$wpdb->prepare(
					'SELECT ( DATA_LENGTH + INDEX_LENGTH ) FROM information_schema.TABLES WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s',
					$wpdb->dbname,
					$table
				)
			);
			$out[ $table ] = null === $bytes ? 0 : (int) $bytes;
		}
		return $out;
	}

	/**
	 * Convert value to SQL literal. Emits hex literals for binary (non-UTF-8) byte content
	 * so BLOB columns survive the round-trip, mirroring `mysqldump --hex-blob`.
	 *
	 * @param mixed $value Column value to convert.
	 * @return string
	 */
	private static function sql_value( mixed $value ): string {
		global $wpdb;
		if ( null === $value ) {
			return 'NULL';
		}
		if ( is_int( $value ) || is_float( $value ) ) {
			return (string) $value;
		}
		if ( is_bool( $value ) ) {
			return $value ? '1' : '0';
		}
		$string = (string) $value;
		if ( '' === $string ) {
			return "''";
		}
		if ( self::is_binary_string( $string ) ) {
			return '0x' . bin2hex( $string );
		}
		// CRITICAL: esc_sql() → wpdb::_real_escape() appends add_placeholder_escape()
		// since WP 4.8.3, replacing every literal `%` with a random `{sha256}` token.
		// Normally $wpdb->query() strips it before hitting MySQL — but our dump is written
		// to a .sql file and re-run via raw mysqli, which never strips it, so `%postname%`
		// etc. was stored as `{hash}postname{hash}`. remove_placeholder_escape() reverses
		// it so the dump carries a literal `%`.
		return "'" . $wpdb->remove_placeholder_escape( esc_sql( $string ) ) . "'";
	}

	/**
	 * Heuristic: byte content that isn't valid UTF-8 is treated as binary and hex-escaped.
	 *
	 * @param string $value Raw byte string to test.
	 * @return bool
	 */
	private static function is_binary_string( string $value ): bool {
		if ( '' === $value ) {
			return false;
		}
		if ( preg_match( '//u', $value ) === 1 ) {
			return false;
		}
		return true;
	}

	/**
	 * Write a line to the export stream. Uses SplFileObject (object method, not the
	 * discouraged global file functions) and an incremental gzip deflate context when
	 * compression is enabled — this streams to disk without buffering the file in memory.
	 *
	 * @param \SplFileObject       $spl     Open export file handle.
	 * @param \DeflateContext|null $deflate Incremental gzip context, or null for plain output.
	 * @param string               $line    Line to write (a newline is appended).
	 * @return void
	 */
	private static function write_chunk( \SplFileObject $spl, ?\DeflateContext $deflate, string $line ): void {
		$chunk = $line . "\n";
		if ( null !== $deflate ) {
			$spl->fwrite( deflate_add( $deflate, $chunk, ZLIB_NO_FLUSH ) );
			return;
		}
		$spl->fwrite( $chunk );
	}

	/**
	 * Finalise the export stream: flush the trailing gzip frame (if any) and the buffer to disk.
	 *
	 * @param \SplFileObject       $spl     Open export file handle.
	 * @param \DeflateContext|null $deflate Incremental gzip context, or null for plain output.
	 * @return void
	 */
	private static function finish_file( \SplFileObject $spl, ?\DeflateContext $deflate ): void {
		if ( null !== $deflate ) {
			$spl->fwrite( deflate_add( $deflate, '', ZLIB_FINISH ) );
		}
		$spl->fflush();
	}
}