<?php
/**
 * SQL dump generator.
 *
 * @package Shiftora\Backup
 */

declare(strict_types=1);

namespace Shiftora\Backup;

use RuntimeException;
use Shiftora\Migration\KeyCursor;
use Shiftora\Support\Db\SqlLiteral;
use Shiftora\Support\SessionSqlMode;

// phpcs:disable WordPress.Security.EscapeOutput.ExceptionNotEscaped, WordPress.WP.AlternativeFunctions.rename_rename -- Exception messages are not output; the dump is renamed into place once complete, which WP_Filesystem cannot do atomically.

// Prevent direct file access.
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * Generates a SQL dump (CREATE TABLE plus chunked INSERTs) for one or more tables.
 *
 * Uses only wpdb so it works on managed hosts that restrict exec() and mysqldump.
 */
class SqlDumper {

	private const CHUNK_SIZE = 5000;

	/** Row filters of a dump, each tied to the one table it applies to. */
	private const FILTER_TRANSIENTS = 'transients';
	private const FILTER_SPAM       = 'spam';

	/** Maximum bytes per INSERT statement, to stay well under max_allowed_packet. */
	private const MAX_INSERT_BYTES = 4_194_304; // 4 MiB

	/** @var bool Runtime flag: skip spam comments during dump. */
	private bool $exclude_spam = false;

	/** @var bool Runtime flag: skip transient options during dump. */
	private bool $exclude_transients = false;

	/**
	 * Column types per table, so each table is described once per dump.
	 *
	 * @var array<string, array<string, string>>
	 */
	private array $column_types = [];

	public function __construct(
		private readonly \wpdb $wpdb
	) {}

	/** Closes a plain file handle. A method so a close failure can be overridden and observed. */
	protected function close_file( $handle ): bool {
		// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose -- Closes a streamed dump handle; WP_Filesystem has no append or stream API.
		return (bool) fclose( $handle );
	}

	/** Closes a gzip handle, like close_file(). */
	protected function close_gz( $handle ): bool {
		// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose -- Closes a streamed gzip dump handle; WP_Filesystem has no append or stream API.
		return (bool) gzclose( $handle );
	}

	/**
	 * Dumps tables to a file.
	 *
	 * @param string[] $tables List of full table names (including prefix).
	 * @param array{
	 *   exclude_spam?: bool,
	 *   exclude_transients?: bool,
	 *   compress?: bool,
	 * }              $options Optional dump parameters.
	 * @return int File size in bytes.
	 * @throws RuntimeException On I/O errors.
	 */
	public function dump( array $tables, string $file_path, array $options = [] ): int {
		$this->exclude_spam       = ! empty( $options['exclude_spam'] );
		$this->exclude_transients = ! empty( $options['exclude_transients'] );
		$compress                 = ! empty( $options['compress'] );

		$dir = dirname( $file_path );
		if ( ! wp_mkdir_p( $dir ) ) {
			throw new RuntimeException( "Cannot create backup directory: {$dir}" );
		}

		// Write to a temp file and rename after a successful dump, so a killed
		// process cannot leave a partial file that looks like a backup.
		$tmp_path = $file_path . '.shiftora-tmp';

		// The digest covers the exact bytes written so restores can verify the file;
		// a trailing SHA-256 line records it and is excluded from it.
		$hash_ctx = hash_init( 'sha256' );

		// The dump is streamed table by table, and WP_Filesystem has no append or stream API, so PHP handles write it inside the guarded backups or exports folder.
		if ( $compress ) {
			// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fopen -- WP_Filesystem has no append or stream API, so the dump is written through a PHP handle inside the guarded folder.
			$handle = gzopen( $tmp_path, 'wb9' );
			$write  = static function ( $h, string $bytes ) use ( $hash_ctx ): void {
				self::write_all( $h, $bytes, 'gzwrite' );
				hash_update( $hash_ctx, $bytes );
			};
			$write_raw = static fn( $h, string $bytes ) => self::write_all( $h, $bytes, 'gzwrite' );
		} else {
			$handle = fopen( $tmp_path, 'wb' ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fopen -- Plain-file counterpart of the gzopen above; WP_Filesystem has no append or stream API.
			$write  = static function ( $h, string $bytes ) use ( $hash_ctx ): void {
				self::write_all( $h, $bytes, 'fwrite' );
				hash_update( $hash_ctx, $bytes );
			};
			$write_raw = static fn( $h, string $bytes ) => self::write_all( $h, $bytes, 'fwrite' );
		}

		if ( ! $handle ) {
			throw new RuntimeException( "Cannot open backup file for writing: {$tmp_path}" );
		}

		// The restorer reads a backslash as an escape, so values must be escaped by a
		// session that does too, whatever sql_mode this connection runs with.
		$sql_mode_before = SessionSqlMode::relax( $this->wpdb );

		try {
			$write( $handle, "-- Shiftora SQL Backup\n" );
			$write( $handle, '-- Generated: ' . gmdate( 'Y-m-d H:i:s' ) . " UTC\n" );
			$write( $handle, "-- Tables: " . implode( ', ', $tables ) . "\n\n" );
			$write( $handle, "SET FOREIGN_KEY_CHECKS=0;\n\n" );

			foreach ( $tables as $table ) {
				$this->dump_table( $handle, $table, $write );
			}

			$write( $handle, "SET FOREIGN_KEY_CHECKS=1;\n" );
			// The digest line bypasses the hashing writer so it is not part of its own digest.
			$write_raw( $handle, '-- SHA-256: ' . hash_final( $hash_ctx, false ) . "\n" );
		} catch ( \Throwable $e ) {
			wp_delete_file( $tmp_path );
			throw $e;
		} finally {
			SessionSqlMode::restore( $this->wpdb, $sql_mode_before );
		}

		// A failed close may have truncated the last buffer; do not promote it.
		$closed = $compress ? $this->close_gz( $handle ) : $this->close_file( $handle );
		if ( ! $closed ) {
			wp_delete_file( $tmp_path );
			throw new RuntimeException( "Failed to close backup file: {$tmp_path}" );
		}

		// Atomic promotion to the final path.
		if ( ! rename( $tmp_path, $file_path ) ) {
			wp_delete_file( $tmp_path );
			throw new RuntimeException( "Could not rename temp backup file to: {$file_path}" );
		}

		return (int) filesize( $file_path );
	}

	/**
	 * Keeps writing until the stream takes all the bytes. fwrite and gzwrite can
	 * write less than requested, and a short dump is a corrupt backup.
	 *
	 * @param callable $write Underlying write function (fwrite/gzwrite).
	 * @throws RuntimeException When the stream refuses to take more bytes.
	 */
	private static function write_all( $handle, string $bytes, callable $write ): void {
		$offset = 0;
		$length = strlen( $bytes );

		while ( $offset < $length ) {
			$written = $write( $handle, substr( $bytes, $offset ) );
			if ( false === $written || 0 === $written ) {
				throw new RuntimeException( 'Failed to write complete backup content.' );
			}
			$offset += $written;
		}
	}

	/**
	 * Dumps a single table: CREATE TABLE plus chunked INSERTs.
	 *
	 * @param callable $write Write function (fwrite or gzwrite wrapper).
	 * @throws RuntimeException On query failure.
	 */
	private function dump_table( $handle, string $table, callable $write ): void {
		$wpdb   = $this->wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery -- server metadata, nothing to cache
		$create = $wpdb->get_row( $wpdb->prepare( 'SHOW CREATE TABLE %i', $table ), ARRAY_N );
		if ( empty( $create[1] ) ) {
			throw new RuntimeException( "Failed to get CREATE TABLE for: {$table}" );
		}

		$write( $handle, "-- Table: {$table}\n" );
		$write( $handle, 'DROP TABLE IF EXISTS ' . SqlLiteral::identifier( $table ) . ";\n" );
		$write( $handle, $create[1] . ";\n\n" );

		$filter = '';
		if ( $this->exclude_transients && $table === $this->wpdb->options ) {
			$filter = self::FILTER_TRANSIENTS;
		} elseif ( $this->exclude_spam && $table === $this->wpdb->comments ) {
			$filter = self::FILTER_SPAM;
		}

		$key_cols = $this->find_keyset_columns( $table );

		if ( [] !== $key_cols && '' === $filter ) {
			$this->dump_table_keyset( $handle, $table, $write, $key_cols );
		} else {
			$this->dump_table_offset( $handle, $table, $write, $filter, $key_cols );
		}

		$write( $handle, "\n" );
	}

	/**
	 * Finds the ordered keyset columns (PRIMARY, else first unique NOT NULL index).
	 *
	 * @return list<string> Ordered key columns, empty when none usable.
	 */
	private function find_keyset_columns( string $table ): array {
		// No ORDER BY: MariaDB rejects it in SHOW INDEX, so the picker sorts in PHP.
		$wpdb    = $this->wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery -- server metadata, nothing to cache
		$indexes = $wpdb->get_results( $wpdb->prepare( 'SHOW INDEX FROM %i', $table ), ARRAY_A );

		return is_array( $indexes ) ? KeyCursor::pick_keyset_columns( $indexes ) : [];
	}

	private function dump_table_keyset( $handle, string $table, callable $write, array $key_cols ): void {
		$wpdb   = $this->wpdb;
		$cursor = null;

		do {
			// $cursor must be the decoded tuple; page_sql() throws on an encoded JSON string.
			$rows = KeyCursor::fetch_page( $wpdb, $table, $key_cols, $cursor, self::CHUNK_SIZE );

			// A failed query must not read as the end of data; check last_error before the next query clears it.
			if ( null === $rows || ( [] === $rows && '' !== $this->wpdb->last_error ) ) {
				throw new RuntimeException( "Query failed while dumping table {$table}: {$this->wpdb->last_error}" );
			}

			if ( ! empty( $rows ) ) {
				$this->write_insert_chunk( $handle, $table, $rows, $write );
				$last_row = end( $rows );
				$tuple    = [];
				foreach ( $key_cols as $key_col ) {
					$tuple[] = isset( $last_row[ $key_col ] ) ? (string) $last_row[ $key_col ] : '';
				}
				$cursor = $tuple;
			}
		} while ( count( $rows ) === self::CHUNK_SIZE );
	}

	/**
	 * Pages through a table by offset, for a filtered dump or a table with no
	 * usable key.
	 *
	 * Always ordered: LIMIT/OFFSET without ORDER BY can return rows in a different
	 * order per page, duplicating some and dropping others. Orders by the key when
	 * there is one, otherwise by every column position.
	 *
	 * @param string       $filter   One of the self::FILTER_ constants, or '' for the whole table.
	 * @param list<string> $key_cols Usable key columns, or empty.
	 */
	private function dump_table_offset( $handle, string $table, callable $write, string $filter, array $key_cols ): void {
		// Every column in table order when there is no key, which is what ORDER BY 1, 2, ... sorts by.
		$order      = [] !== $key_cols ? $key_cols : array_map( 'strval', array_keys( $this->column_types( $table ) ) );
		if ( [] === $order ) {
			// Paging without an order can repeat or drop rows, so a table whose columns cannot be read is not dumped.
			throw new RuntimeException( "Cannot order table {$table} for dumping: its columns could not be read." );
		}

		$wpdb       = $this->wpdb;
		$exclusions = match ( $filter ) {
			self::FILTER_TRANSIENTS => [
				[ 'option_name', 'NOT LIKE', $wpdb->esc_like( '_transient_' ) . '%' ],
				[ 'option_name', 'NOT LIKE', $wpdb->esc_like( '_site_transient_' ) . '%' ],
			],
			self::FILTER_SPAM       => [ [ 'comment_approved', '<>', 'spam' ] ],
			default                 => [],
		};
		$offset     = 0;
		do {
			$rows = KeyCursor::fetch_page( $wpdb, $table, $order, null, self::CHUNK_SIZE, $offset, $exclusions );

			if ( null === $rows || ( [] === $rows && '' !== $this->wpdb->last_error ) ) {
				throw new RuntimeException( "Query failed while dumping table {$table}: {$this->wpdb->last_error}" );
			}

			if ( ! empty( $rows ) ) {
				$this->write_insert_chunk( $handle, $table, $rows, $write );
			}

			$offset += self::CHUNK_SIZE;
		} while ( count( $rows ) === self::CHUNK_SIZE );
	}

	/**
	 * Column types for a table, read once per dump.
	 *
	 * @return array<string, string> Column name to lower-cased type.
	 */
	private function column_types( string $table ): array {
		if ( ! isset( $this->column_types[ $table ] ) ) {
			$types = [];
			$wpdb = $this->wpdb;
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery -- server metadata, nothing to cache
			foreach ( (array) $wpdb->get_results( $wpdb->prepare( 'SHOW COLUMNS FROM %i', $table ) ) as $column ) {
				$types[ (string) $column->Field ] = strtolower( (string) $column->Type );
			}
			$this->column_types[ $table ] = $types;
		}

		return $this->column_types[ $table ];
	}

	private static function is_binary_type( string $type ): bool {
		return 1 === preg_match( '/^(?:(?:tiny|medium|long)?blob|(?:var)?binary|bit|geometry|point|linestring|polygon|multipoint|multilinestring|multipolygon|geometrycollection)\b/', $type );
	}

	/**
	 * Writes a chunk of rows as an INSERT statement.
	 *
	 * @param array<int, array<string, mixed>> $rows
	 */
	private function write_insert_chunk( $handle, string $table, array $rows, callable $write ): void {
		// A reconnect since the fetch can have changed the session, so its mode is read again for this page.
		SqlLiteral::session_mode_changed( $this->wpdb );

		$columns = array_keys( $rows[0] );
		$prefix  = 'INSERT INTO ' . SqlLiteral::identifier( $table ) . ' (' . SqlLiteral::identifiers( $columns ) . ") VALUES\n  ";
		$buffer  = $prefix;
		$first   = true;
		$types   = $this->column_types( $table );

		foreach ( $rows as $row ) {
			$vals = [];
			foreach ( $row as $column => $v ) {
				// Binary columns go as hex, because a quoted value that is not valid UTF-8 is cut short on restore.
				$vals[] = SqlLiteral::value( $v, self::is_binary_type( $types[ $column ] ?? '' ) );
			}
			$line = ( $first ? '' : ",\n  " ) . '(' . implode( ', ', $vals ) . ')';

			if ( strlen( $buffer ) + strlen( $line ) + 3 > self::MAX_INSERT_BYTES && ! $first ) {
				$write( $handle, $buffer . ";\n" );
				$buffer = $prefix . '(' . implode( ', ', $vals ) . ')';
				$first  = false;
			} else {
				$buffer .= $line;
				$first   = false;
			}
		}

		if ( ! $first ) {
			$write( $handle, $buffer . ";\n" );
		}
	}

// phpcs:enable WordPress.Security.EscapeOutput.ExceptionNotEscaped, WordPress.WP.AlternativeFunctions.rename_rename
}