<?php
/**
 * DB exporter — produces portable SQL for the site's tables in row chunks.
 *
 * Stateless helpers driven by the job runner: it exports one bounded chunk per
 * call so a huge table never lands in memory at once.
 *
 * @package AI_SiteArk
 * Copyright (c) 2026 AtoZ INFOWAY INC.
 * Licensed under the GNU General Public License, version 2 or later.
 */

namespace AtoZ\SiteArk\Backup;

defined( 'ABSPATH' ) || exit;

/**
 * Class DB_Exporter
 */
class DB_Exporter {

	/**
	 * Base tables belonging to this site (by prefix). Views are excluded — they
	 * carry no rows and are exported separately as CREATE VIEW (see views()).
	 *
	 * @return string[]
	 */
	public static function tables() {
		global $wpdb;
		$like = $wpdb->esc_like( $wpdb->prefix ) . '%';
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- table/identifier sanitized via sanitize_table() and backtick-quoted.
		$rows = $wpdb->get_results( $wpdb->prepare( 'SHOW FULL TABLES LIKE %s', $like ), ARRAY_N );

		// P5: tables the user chose to leave out of backups entirely.
		$skip = array();
		foreach ( (array) ( \AtoZ\SiteArk\Helpers\Settings::get()['db_exclude_tables'] ?? array() ) as $t ) {
			$t = strtolower( trim( (string) $t ) );
			if ( '' !== $t ) {
				$skip[ $t ] = true;
			}
		}

		$out = array();
		foreach ( (array) $rows as $r ) {
			// Column 1 is Table_type ('BASE TABLE' | 'VIEW'); absent on ancient servers.
			if ( isset( $r[1] ) && 'BASE TABLE' !== strtoupper( (string) $r[1] ) ) {
				continue;
			}
			if ( isset( $skip[ strtolower( (string) $r[0] ) ] ) ) {
				continue;
			}
			$out[] = $r[0];
		}
		return $out;
	}

	/**
	 * P5: a WHERE fragment that drops rows nobody wants inside a backup.
	 *
	 * Post revisions, transients and spam comments are pure bloat — on a long-lived site
	 * they routinely dwarf the real content. Each is opt-in, and returns '' when off, so
	 * the normal query is untouched.
	 *
	 * The clause is built only from literals we control; the table name is sanitized by
	 * the caller. It must be applied identically to count_rows() and export_rows(), or
	 * progress reporting would disagree with what is written.
	 *
	 * @param string $table Fully-qualified table name.
	 * @return string '' or a ' WHERE …' fragment.
	 */
	public static function row_filter( $table ) {
		global $wpdb;
		$s     = \AtoZ\SiteArk\Helpers\Settings::get();
		$table = strtolower( (string) $table );

		if ( ! empty( $s['db_exclude_revisions'] ) && strtolower( $wpdb->posts ) === $table ) {
			return " WHERE `post_type` != 'revision'";
		}
		if ( ! empty( $s['db_exclude_spam'] ) && strtolower( $wpdb->comments ) === $table ) {
			return " WHERE `comment_approved` != 'spam'";
		}
		if ( ! empty( $s['db_exclude_transients'] ) && strtolower( $wpdb->options ) === $table ) {
			// Covers _transient_*, _transient_timeout_* and the site-wide variants.
			return " WHERE `option_name` NOT LIKE '\\_transient\\_%'"
				. " AND `option_name` NOT LIKE '\\_site\\_transient\\_%'";
		}
		return '';
	}

	/**
	 * Views belonging to this site: [ name => "CREATE ... VIEW …" ] (DEFINER stripped).
	 *
	 * @return array
	 */
	public static function views() {
		global $wpdb;
		$like = $wpdb->esc_like( $wpdb->prefix ) . '%';
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- table/identifier sanitized via sanitize_table() and backtick-quoted.
		$rows = $wpdb->get_results( $wpdb->prepare( 'SHOW FULL TABLES LIKE %s', $like ), ARRAY_N );
		$out  = array();
		foreach ( (array) $rows as $r ) {
			if ( ! isset( $r[1] ) || 'VIEW' !== strtoupper( (string) $r[1] ) ) {
				continue;
			}
			$name = self::sanitize_table( $r[0] );
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- table/identifier sanitized via sanitize_table() and backtick-quoted.
			$cr  = $wpdb->get_row( "SHOW CREATE VIEW `{$name}`", ARRAY_A );
			$sql = is_array( $cr ) && isset( $cr['Create View'] ) ? (string) $cr['Create View'] : '';
			if ( '' !== $sql ) {
				$out[ $name ] = self::strip_definer( $sql );
			}
		}
		return $out;
	}

	/**
	 * Triggers on this site's tables: [ name => { table, sql } ] (DEFINER stripped).
	 *
	 * @return array
	 */
	public static function triggers() {
		global $wpdb;
		$prefix = $wpdb->prefix;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$rows = $wpdb->get_results( 'SHOW TRIGGERS', ARRAY_A );
		$out  = array();
		foreach ( (array) $rows as $r ) {
			$table = isset( $r['Table'] ) ? (string) $r['Table'] : '';
			$name  = isset( $r['Trigger'] ) ? self::sanitize_table( $r['Trigger'] ) : '';
			// Only triggers attached to this site's own prefixed tables.
			if ( '' === $name || '' === $table || 0 !== strpos( $table, $prefix ) ) {
				continue;
			}
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- table/identifier sanitized via sanitize_table() and backtick-quoted.
			$cr  = $wpdb->get_row( "SHOW CREATE TRIGGER `{$name}`", ARRAY_A );
			$sql = is_array( $cr ) && isset( $cr['SQL Original Statement'] ) ? (string) $cr['SQL Original Statement'] : '';
			if ( '' !== $sql ) {
				$out[ $name ] = array( 'table' => $table, 'sql' => self::strip_definer( $sql ) );
			}
		}
		return $out;
	}

	/**
	 * Scheduled events in this database: [ name => "CREATE EVENT …" ] (DEFINER stripped).
	 *
	 * @return array
	 */
	public static function events() {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$db = (string) $wpdb->get_var( 'SELECT DATABASE()' );
		if ( '' === $db ) {
			return array();
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- table/identifier sanitized via sanitize_table() and backtick-quoted.
		$rows = $wpdb->get_results( $wpdb->prepare( 'SHOW EVENTS WHERE Db = %s', $db ), ARRAY_A );
		$out  = array();
		foreach ( (array) $rows as $r ) {
			$name = isset( $r['Name'] ) ? self::sanitize_table( $r['Name'] ) : '';
			if ( '' === $name ) {
				continue;
			}
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- table/identifier sanitized via sanitize_table() and backtick-quoted.
			$cr  = $wpdb->get_row( "SHOW CREATE EVENT `{$name}`", ARRAY_A );
			$sql = is_array( $cr ) && isset( $cr['Create Event'] ) ? (string) $cr['Create Event'] : '';
			if ( '' !== $sql ) {
				$out[ $name ] = self::strip_definer( $sql );
			}
		}
		return $out;
	}

	/**
	 * Remove a `DEFINER=`user`@`host`` clause so a restore never fails on a user
	 * that doesn't exist on the target server (and can't be used for definer-context
	 * privilege escalation from a crafted archive).
	 *
	 * @param string $sql SQL.
	 * @return string
	 */
	public static function strip_definer( $sql ) {
		return (string) preg_replace( '/\sDEFINER\s*=\s*`(?:[^`]|``)*`@`(?:[^`]|``)*`/i', '', (string) $sql );
	}

	/**
	 * Run a $wpdb read and retry it a few times on a transient error (deadlock,
	 * lock-wait timeout, "server has gone away") before giving up.
	 *
	 * @param callable $fn       Closure that performs the query and returns its result.
	 * @param int      $attempts Max attempts.
	 * @return mixed
	 */
	public static function retry( callable $fn, $attempts = 3 ) {
		global $wpdb;
		$res = null;
		for ( $i = 0; $i < max( 1, (int) $attempts ); $i++ ) {
			$wpdb->last_error = '';
			$res              = $fn();
			if ( '' === (string) $wpdb->last_error || ! self::is_transient_error( $wpdb->last_error ) ) {
				return $res;
			}
			if ( $i < $attempts - 1 ) {
				usleep( 150000 * ( $i + 1 ) ); // 150ms, 300ms back-off.
			}
		}
		return $res;
	}

	/**
	 * Whether a DB error string is a transient one worth retrying.
	 *
	 * @param string $err Error string.
	 * @return bool
	 */
	private static function is_transient_error( $err ) {
		$err = strtolower( (string) $err );
		if ( '' === $err ) {
			return false;
		}
		foreach ( array( 'deadlock', 'lock wait timeout', 'try restarting transaction', 'server has gone away', 'lost connection' ) as $needle ) {
			if ( false !== strpos( $err, $needle ) ) {
				return true;
			}
		}
		return false;
	}

	/**
	 * Row count for a table.
	 *
	 * @param string $table Table name.
	 * @return int
	 */
	public static function count_rows( $table ) {
		global $wpdb;
		$table = self::sanitize_table( $table );
		$where = self::row_filter( $table );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- table/identifier sanitized via sanitize_table() and backtick-quoted.
		return (int) $wpdb->get_var( "SELECT COUNT(*) FROM `{$table}`{$where}" );
	}

	/**
	 * DROP + CREATE TABLE statement.
	 *
	 * @param string $table Table name.
	 * @return string
	 */
	public static function create_statement( $table ) {
		global $wpdb;
		$table = self::sanitize_table( $table );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- table/identifier sanitized via sanitize_table() and backtick-quoted.
		$row = $wpdb->get_row( "SHOW CREATE TABLE `{$table}`", ARRAY_N );
		$create = isset( $row[1] ) ? $row[1] : '';
		if ( ! $create ) {
			return '';
		}
		return "DROP TABLE IF EXISTS `{$table}`;\n" . $create . ";\n";
	}

	/**
	 * Build a multi-row INSERT for one chunk of a table (empty string when past the end).
	 *
	 * @param string $table  Table name.
	 * @param int    $offset Row offset.
	 * @param int    $limit  Rows per chunk.
	 * @param array  $pairs  Optional find→replace map applied (serialization-safe) to
	 *                       string values before they are written (export-time search-replace).
	 * @return array{sql:string,rows:int}
	 */
	public static function export_rows( $table, $offset, $limit, array $pairs = array() ) {
		global $wpdb;
		$table  = self::sanitize_table( $table );
		$offset = max( 0, (int) $offset );
		$limit  = max( 1, (int) $limit );

		// P5: the same filter count_rows() uses, so progress matches what is written.
		// Rows the filter drops are never counted, so the caller's offset (which advances
		// by rows actually returned) stays consistent across chunks.
		$where = self::row_filter( $table );
		$order = self::order_by( $table );

		$rows = self::retry(
			function () use ( $wpdb, $table, $offset, $limit, $where, $order ) {
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- table/identifier sanitized via sanitize_table() and backtick-quoted; $order is built from SHOW KEYS column names, backtick-quoted.
				return $wpdb->get_results( "SELECT * FROM `{$table}`{$where}{$order} LIMIT {$offset}, {$limit}", ARRAY_A );
			}
		);
		// A non-empty last_error after the retries means the read genuinely FAILED — not an
		// empty end-of-table result. The caller must flag this, never treat it as "table
		// finished" (which would silently drop the rest of the table from a "complete" backup).
		$err = (string) $wpdb->last_error;
		if ( empty( $rows ) ) {
			return array( 'sql' => '', 'rows' => 0, 'err' => $err );
		}

		$columns = array_keys( $rows[0] );
		$col_sql = '`' . implode( '`,`', array_map( 'esc_sql', $columns ) ) . '`';

		$values = array();
		foreach ( $rows as $row ) {
			$vals = array();
			foreach ( $columns as $col ) {
				$v = $row[ $col ];
				if ( ! empty( $pairs ) && is_string( $v ) && '' !== $v ) {
					$v = \AtoZ\SiteArk\Restore\Search_Replace::replace_value( $v, $pairs );
				}
				$vals[] = self::quote( $v );
			}
			$values[] = '(' . implode( ',', $vals ) . ')';
		}

		$sql = "INSERT INTO `{$table}` ({$col_sql}) VALUES\n" . implode( ",\n", $values ) . ";\n";
		return array( 'sql' => $sql, 'rows' => count( $rows ), 'err' => '' );
	}

	/**
	 * Quote a single value for an INSERT (NULL-safe, escaped).
	 *
	 * @param mixed $value Value.
	 * @return string
	 */
	private static function quote( $value ) {
		if ( null === $value ) {
			return 'NULL';
		}
		return "'" . esc_sql( (string) $value ) . "'";
	}

	/**
	 * Strip backticks from a table name (defence-in-depth; names come from SHOW TABLES).
	 *
	 * @param string $table Table name.
	 * @return string
	 */
	private static function sanitize_table( $table ) {
		return str_replace( '`', '', (string) $table );
	}

	/**
	 * A stable `ORDER BY <primary-key columns>` clause for a table (or '' if it has no primary
	 * key). Chunked export uses OFFSET paging across many steps; without a deterministic order,
	 * concurrent INSERT/DELETE on a live site can shift the unordered result set and cause rows
	 * to be skipped or duplicated across chunks. Cached per table (SHOW KEYS runs once).
	 *
	 * @param string $table Table name (already sanitized).
	 * @return string
	 */
	private static function order_by( $table ) {
		static $cache = array();
		if ( isset( $cache[ $table ] ) ) {
			return $cache[ $table ];
		}
		global $wpdb;
		$safe = str_replace( '`', '``', $table );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- backtick-quoted internal identifier.
		$keys = $wpdb->get_results( "SHOW KEYS FROM `{$safe}` WHERE Key_name = 'PRIMARY'", ARRAY_A );
		$cols = array();
		if ( is_array( $keys ) ) {
			usort( $keys, static function ( $a, $b ) { return (int) $a['Seq_in_index'] <=> (int) $b['Seq_in_index']; } );
			foreach ( $keys as $k ) {
				if ( ! empty( $k['Column_name'] ) ) {
					$cols[] = '`' . str_replace( '`', '``', (string) $k['Column_name'] ) . '`';
				}
			}
		}
		$order          = $cols ? ' ORDER BY ' . implode( ', ', $cols ) : '';
		$cache[ $table ] = $order;
		return $order;
	}
}