<?php
/**
 * Pure-PHP MySQL dumper (no mysqldump binary required).
 *
 * Output is a single .sql file containing CREATE + INSERT statements for
 * every table on the current $wpdb connection.
 *
 * @package Backreload\Core
 */

namespace Backreload\Core;

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

class DatabaseDumper {

	public function dump( string $output_path, ?Logger $logger = null ): void {
		global $wpdb;

		$fh = @fopen( $output_path, 'w' ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fopen -- WP_Filesystem buffers whole files and cannot stream; the SQL dumper writes multi-GB dumps via direct handles under the plugin's own wp-content/backreload-backups paths.
		if ( ! $fh ) {
			throw new \RuntimeException( esc_html(  "Cannot open dump file: {$output_path}"  )  );
		}

		// Point-in-time consistency. Without this, each table (and each
		// LIMIT/OFFSET page) is read at a different instant, so rows written
		// between, say, dumping wp_posts and wp_postmeta produce referential
		// tears — orphan meta, half-written orders — and OFFSET paging can
		// dup/skip rows under concurrent writes. A REPEATABLE READ snapshot
		// freezes a single consistent view for the whole dump, fixing both.
		//
		// Caveats, by design:
		//   - InnoDB only. MyISAM tables are non-transactional and won't
		//     participate in the snapshot — they're read at whatever state
		//     they happen to be in. That's no worse than before, and modern
		//     WordPress is InnoDB end-to-end.
		//   - Must run on the same connection as the SELECTs. $wpdb uses one
		//     connection (HyperDB/replica splitting is an unsupported edge),
		//     so the START TRANSACTION and the get_results() below share it.
		$started_txn = false;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->query( 'SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
		if ( false !== $wpdb->query( 'START TRANSACTION WITH CONSISTENT SNAPSHOT' ) ) {
			$started_txn = true;
		}

		try {
			// phpcs:disable WordPress.WP.AlternativeFunctions.file_system_operations_fwrite -- WP_Filesystem buffers whole files and cannot stream; the SQL dumper writes multi-GB dumps via direct handles under the plugin's own wp-content/backreload-backups paths.
			fwrite( $fh, "-- Backreload database dump\n" );
			fwrite( $fh, "-- Generated: " . gmdate( 'c' ) . "\n" );
			fwrite( $fh, "SET FOREIGN_KEY_CHECKS=0;\n" );
			fwrite( $fh, "SET NAMES utf8mb4;\n\n" );
			// phpcs:enable WordPress.WP.AlternativeFunctions.file_system_operations_fwrite

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin reads/writes its own custom tables; results are transient backup state and must not be cached.
			$tables = $wpdb->get_col( 'SHOW TABLES' );
			foreach ( $tables as $table ) {
				$logger && $logger->info( "Dumping table: {$table}" );
				$this->dump_table( $fh, (string) $table );
			}

			fwrite( $fh, "SET FOREIGN_KEY_CHECKS=1;\n" ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fwrite -- WP_Filesystem buffers whole files and cannot stream; the SQL dumper writes multi-GB dumps via direct handles under the plugin's own wp-content/backreload-backups paths.
		} finally {
			if ( $started_txn ) {
				// Read-only transaction — COMMIT and ROLLBACK are equivalent;
				// COMMIT is the conventional close. Releases the snapshot's
				// read view so it doesn't pin undo logs longer than needed.
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
				$wpdb->query( 'COMMIT' );
			}
			fclose( $fh ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose -- WP_Filesystem buffers whole files and cannot stream; the SQL dumper writes multi-GB dumps via direct handles under the plugin's own wp-content/backreload-backups paths.
		}
	}

	private function dump_table( $fh, string $table ): void {
		global $wpdb;

		// Schema
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.SchemaChange -- Table name from $wpdb->prefix (never user input), cannot be bound via prepare(); reads the schema of the site's own tables, result is transient and uncacheable.
		$create = $wpdb->get_row( "SHOW CREATE TABLE `{$table}`", ARRAY_N );
		// phpcs:disable WordPress.WP.AlternativeFunctions.file_system_operations_fwrite -- WP_Filesystem buffers whole files and cannot stream; the SQL dumper writes multi-GB dumps via direct handles under the plugin's own wp-content/backreload-backups paths.
		fwrite( $fh, "\n-- ----- Table: {$table} -----\n" );
		fwrite( $fh, "DROP TABLE IF EXISTS `{$table}`;\n" );
		fwrite( $fh, ( $create[1] ?? '' ) . ";\n" );
		// phpcs:enable WordPress.WP.AlternativeFunctions.file_system_operations_fwrite

		// Determine which columns are INSERTABLE. Generated columns
		// (VIRTUAL/STORED GENERATED) reject an explicit value on INSERT
		// (MySQL error 3105) — emitting them produces a dump that cannot be
		// restored at all. We read information_schema to find them and
		// exclude them from BOTH the column list and the SELECT, so the
		// generated column simply re-derives itself on restore from the
		// CREATE TABLE definition.
		$insertable = $this->insertable_columns( $table );

		if ( empty( $insertable ) ) {
			// Couldn't introspect (permissions on information_schema, exotic
			// engine, etc.). Fall back to the old SELECT * behaviour rather
			// than skip the table's data entirely. A table with a generated
			// column will still fail to restore in this fallback, but that's
			// strictly no worse than before this fix and only happens when
			// introspection is unavailable.
			$select_list = '*';
			$col_clause  = null; // signal "use row keys"
		} else {
			$select_list = implode( ',', array_map( [ $this, 'quote_ident' ], $insertable ) );
			$col_clause  = '(' . $select_list . ')';
		}

		// Rows — page through to stay memory-bounded. OFFSET paging is
		// correctness-safe here because the whole dump runs inside the
		// REPEATABLE READ snapshot opened in dump(): the row set is frozen,
		// so no row can shift between pages.
		$page = 0;
		$page_size = 1000;
		while ( true ) {
			$offset = $page * $page_size;
			// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Table/column identifiers derive from $wpdb->prefix and a fixed allow-list of introspected column names (never user input); cannot be bound via prepare(); page reads of the site's own tables, transient and uncacheable.
			$rows = $wpdb->get_results(
				"SELECT {$select_list} FROM `{$table}` LIMIT {$page_size} OFFSET {$offset}",
				ARRAY_A
			);
			// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
			if ( empty( $rows ) ) {
				break;
			}

			foreach ( $rows as $row ) {
				if ( null === $col_clause ) {
					$cols   = array_map( [ $this, 'quote_ident' ], array_keys( $row ) );
					$prefix = '(' . implode( ',', $cols ) . ')';
				} else {
					$prefix = $col_clause;
				}
				$vals = array_map( [ $this, 'quote' ], array_values( $row ) );
				fwrite( $fh, "INSERT INTO `{$table}` {$prefix} VALUES (" . implode( ',', $vals ) . ");\n" ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fwrite -- WP_Filesystem buffers whole files and cannot stream; the SQL dumper writes multi-GB dumps via direct handles under the plugin's own wp-content/backreload-backups paths.
			}
			$page++;
		}
	}

	/**
	 * Column names of $table that are safe to INSERT into — i.e. every column
	 * EXCEPT generated (VIRTUAL/STORED) ones, in ordinal order. Returns an
	 * empty array if information_schema can't be read, signalling the caller
	 * to fall back to SELECT *.
	 *
	 * @return string[]
	 */
	private function insertable_columns( string $table ): array {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
		$cols = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT COLUMN_NAME, EXTRA FROM information_schema.COLUMNS
				 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s
				 ORDER BY ORDINAL_POSITION',
				$table
			),
			ARRAY_A
		);
		if ( empty( $cols ) ) {
			return [];
		}
		$out = [];
		foreach ( $cols as $c ) {
			// EXTRA contains "VIRTUAL GENERATED" or "STORED GENERATED" for
			// generated columns (MySQL 5.7+/MariaDB 10.2+). Case-insensitive
			// substring match keeps it robust across server variants.
			if ( false !== stripos( (string) ( $c['EXTRA'] ?? '' ), 'GENERATED' ) ) {
				continue;
			}
			$out[] = (string) $c['COLUMN_NAME'];
		}
		return $out;
	}

	/** Backtick-quote a SQL identifier, doubling any embedded backtick. */
	private function quote_ident( string $name ): string {
		return '`' . str_replace( '`', '``', $name ) . '`';
	}

	private function quote( $v ): string {
		global $wpdb;
		if ( null === $v )                              return 'NULL';
		if ( is_int( $v ) || is_float( $v ) )           return (string) $v;
		return "'" . esc_sql( (string) $v ) . "'";
	}
}
