<?php
/**
 * Replay a .sql dump into this install through the shared staging engine.
 *
 * @package Shiftora\Import
 */

declare(strict_types=1);

namespace Shiftora\Import;

use RuntimeException;
use Shiftora\Migration\DatabaseImporter;
use Shiftora\Migration\TransferRegistry;
use Shiftora\Support\Db\PeerSql;
use Shiftora\Support\Db\TableDdl;
use Shiftora\Support\SessionSqlMode;
use Shiftora\Support\SqlStatementStream;
use WP_Error;
use WP_REST_Response;

// phpcs:disable WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Exception messages are not output.

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

/**
 * Runs a dump file on disk, regardless of how the bytes got here.
 *
 * Every CREATE TABLE / INSERT INTO head is rewritten to a token-scoped staging
 * table, then one atomic RENAME swaps the set in via finalize_staging(). Live
 * data is untouched until then, so a failed import rolls the staging tables
 * back instead of leaving half-applied schema and rows.
 */
final class DumpImporter {

	/**
	 * Core-table stems used to locate a dump's source prefix. Keep the trailing
	 * underscore: reconcile_role_keys() matches the prefix verbatim.
	 */
	private const CORE_STEMS = [ 'options', 'usermeta', 'users', 'posts', 'comments', 'links', 'term_taxonomy' ];

	public function __construct(
		private readonly ?DatabaseImporter $importer = null,
		private readonly ?\wpdb $wpdb = null
	) {}

	/**
	 * Replays a dump and returns the result in the shape the endpoints return.
	 *
	 * @param string $dump_path       Dump on disk; read, never moved.
	 * @param string $declared_prefix Source table prefix when the caller knows it; empty means infer it, or refuse.
	 *
	 * @throws RuntimeException Never; failures come back as WP_Error.
	 */
	public function import( string $dump_path, string $declared_prefix = '' ): WP_REST_Response|WP_Error {
		$wpdb     = $this->wpdb ?? $GLOBALS['wpdb'];
		$importer = $this->importer ?? new DatabaseImporter( $wpdb );

		// Debris from an earlier crashed import would eat the space this one needs.
		try {
			( new TransferRegistry( $wpdb ) )->sweep_stale_staging();
		} catch ( \Throwable $e ) {
			// The sweep is housekeeping; the import must not fail on it.
		}

		$token    = bin2hex( random_bytes( 6 ) );
		$staged   = [];
		$executed = 0;

		// Derive the source prefix in a pre-pass so every table strips against the
		// same boundary. A dump with disagreeing tables, or nothing to infer from, must
		// name its source_prefix; guessing would misname tables.
		try {
			$src_prefix = $this->resolve_source_prefix( $dump_path, $wpdb->prefix, $declared_prefix, $importer );
		} catch ( RuntimeException $e ) {
			return new WP_Error( 'shiftora_source_prefix_required', $e->getMessage(), [ 'status' => 400 ] );
		}

		// Foreign-key checks are off for the replay and the final RENAME so the dump swaps in as one unit; restored afterwards.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery -- a session setting, nothing to cache
		$fk_before = (string) ( $wpdb->get_var( 'SELECT @@foreign_key_checks' ) ?? '1' );
		$sql_mode_before = SessionSqlMode::relax( $wpdb );
		TableDdl::set_foreign_key_checks( $wpdb, '0' );

		try {
			foreach ( SqlStatementStream::stream( $dump_path ) as $sql ) {
				$sql = trim( $sql );
				if ( '' === $sql || $this->is_client_directive( $sql ) ) {
					continue;
				}

				// A dump is the admin's own file, so it is not held to the peer allow-list, but
				// nothing in a restore has a reason to read or write the database server's files.
				$classified = PeerSql::reaches_server_files( $sql ) ? null : $this->classify_statement( $sql );
				if ( null === $classified ) {
					return $this->abort(
						$importer,
						$staged,
						$token,
						$wpdb,
						$fk_before,
						$sql_mode_before,
						new WP_Error(
							'shiftora_disallowed_sql',
							sprintf(
								/* translators: %s: SQL statement preview */
								__( 'The SQL file contains a disallowed statement: %s', 'shiftora' ),
								$this->preview( $sql )
							),
							[ 'status' => 400 ]
						)
					);
				}

				// This class owns foreign-key state, and a dump's DROP TABLE IF EXISTS preamble must not run against live tables.
if ( 'set_fk' === $classified['kind'] || 'drop' === $classified['kind'] || 'set_session' === $classified['kind'] ) {
				continue;
			}

				$bare = $this->bare_name( $classified['name'], $wpdb->prefix, $src_prefix );
				if ( $importer->is_skipped_table( $bare ) ) {
					continue;
				}

				if ( ! in_array( $bare, $staged, true ) ) {
					$staged[] = $bare;
				}

				$sql = $this->rewrite_head_to_staging( $sql, $classified['kind'], $importer->staging_name_for( $bare, $token ) );

				if ( 'create' === $classified['kind'] ) {
					$importer->create_staging_from_dump( $importer->staging_name_for( $bare, $token ), $sql );
					$executed++;
					continue;
				}

				$result = $wpdb->query( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter -- the admin's own dump statement, replayed as written
				if ( false === $result ) {
					$error = trim( (string) $wpdb->last_error );
					if ( '' === $error ) {
						$error = __( 'Unknown SQL execution error.', 'shiftora' );
					}

					return $this->abort(
						$importer,
						$staged,
						$token,
						$wpdb,
						$fk_before,
						$sql_mode_before,
						new WP_Error(
							'shiftora_import_error',
							sprintf(
								/* translators: 1: database error message, 2: SQL statement preview */
								__( 'Import aborted. Error: %1$s Statement: %2$s', 'shiftora' ),
								$error,
								$this->preview( $sql )
							),
							[ 'status' => 500 ]
						)
					);
				}
				$executed++;
			}
		} catch ( RuntimeException $e ) {
			return $this->abort(
				$importer,
				$staged,
				$token,
				$wpdb,
				$fk_before,
				$sql_mode_before,
				new WP_Error( 'shiftora_read_failed', $e->getMessage(), [ 'status' => 500 ] )
			);
		}

		if ( [] === $staged ) {
			TableDdl::set_foreign_key_checks( $wpdb, $fk_before );
			SessionSqlMode::restore( $wpdb, $sql_mode_before );
			return new WP_Error(
				'shiftora_empty_file',
				__( 'No importable tables found in the file.', 'shiftora' ),
				[ 'status' => 400 ]
			);
		}

		try {
			$importer->finalize_staging( $staged, 0, $src_prefix, $token );
		} catch ( RuntimeException $e ) {
			return $this->abort(
				$importer,
				$staged,
				$token,
				$wpdb,
				$fk_before,
				$sql_mode_before,
				new WP_Error( 'shiftora_import_error', $e->getMessage(), [ 'status' => 500 ] )
			);
		}

		// The swap is done and nothing references the shiftora_old_* tables, so checks come back on before they are dropped.
		TableDdl::set_foreign_key_checks( $wpdb, $fk_before );
		SessionSqlMode::restore( $wpdb, $sql_mode_before );
		$importer->drop_old_tables( $staged, $token );

		return new WP_REST_Response(
			[
				'success'  => true,
				'executed' => $executed,
				'message'  => sprintf(
					/* translators: %d: number of statements */
					__( '%d SQL statements imported successfully.', 'shiftora' ),
					$executed
				),
			],
			200
		);
	}

	/** Rolls back any staged tables, restores foreign-key checks and SQL mode, and hands the error back. Used for every failure after staging may have started. */
	private function abort( DatabaseImporter $importer, array $staged, string $token, \wpdb $wpdb, string $fk_before, string $sql_mode_before, WP_Error $error ): WP_Error {
		if ( [] !== $staged ) {
			$importer->rollback_staging( $staged, 0, $token );
		}
		TableDdl::set_foreign_key_checks( $wpdb, $fk_before );
		SessionSqlMode::restore( $wpdb, $sql_mode_before );
		return $error;
	}

	/** Directives and wrappers that dump tools emit around the real statements; safe to skip, none change table data. */
	private function is_client_directive( string $sql ): bool {
		$sql = ltrim( $sql );

		return 1 === preg_match(
			'/^(?:DELIMITER\s+|LOCK TABLES\b|UNLOCK TABLES\b|START TRANSACTION\b|COMMIT\b|ROLLBACK\b|--|\#|\/\*)/i',
			$sql
		);
	}

	/**
	 * Classifies a statement head into a dump-safe kind. Anything that mutates
	 * live rows (UPDATE/DELETE/ALTER) is left unmatched and rejected by the caller.
	 *
	 * @return array{kind: string, name: string}|null
	 */
	private function classify_statement( string $sql ): ?array {
		$sql = trim( $sql );

		if ( 1 === preg_match( '/^SET\s+FOREIGN_KEY_CHECKS\s*=\s*[01]\s*;?$/i', $sql ) ) {
			return [ 'kind' => 'set_fk', 'name' => '' ];
		}
		if ( 1 === preg_match(
			'/^SET\s+(?:'
			. 'NAMES\s+[A-Za-z0-9_]+'                              // connection charset
			. '|@@(?:SESSION|GLOBAL)\.[A-Za-z0-9_]+\b.*'           // binlog / gtid toggles
			. '|@[A-Za-z0-9_]+\s*=\s*@@[A-Za-z0-9_.]+'             // saved/restored state pairs
			. ')\s*;?$/i',
			$sql
		) ) {
			// mysqldump's preamble and footer only toggle session state for the tool's own benefit.
			return [ 'kind' => 'set_session', 'name' => '' ];
		}
		if ( 1 === preg_match( '/^(CREATE\s+TABLE(?:\s+IF\s+NOT\s+EXISTS)?)\s+(`?)([A-Za-z0-9_$]+)(`?)(?=\s|;|$)/i', $sql, $m ) ) {
			return [ 'kind' => 'create', 'name' => $m[3] ];
		}
		if ( 1 === preg_match( '/^(INSERT\s+INTO)\s+(`?)([A-Za-z0-9_$]+)(`?)(?=\s|;|$)/i', $sql, $m ) ) {
			return [ 'kind' => 'insert', 'name' => $m[3] ];
		}
		if ( 1 === preg_match( '/^(DROP\s+TABLE\s+IF\s+EXISTS)\s+(`?)([A-Za-z0-9_$]+)(`?)(?=\s|;|$)/i', $sql, $m ) ) {
			return [ 'kind' => 'drop', 'name' => $m[3] ];
		}

		return null;
	}

	/**
	 * Derives the effective source prefix for a dump before any table is mapped.
	 * A declared prefix wins. Otherwise: no foreign tables means the dump came from
	 * this install's namespace; one inferred prefix shared by every foreign table
	 * is that prefix; anything else is rejected rather than guessed.
	 *
	 * @throws RuntimeException When the caller must supply a source_prefix.
	 */
	private function resolve_source_prefix( string $dump_path, string $local_prefix, string $declared_prefix, DatabaseImporter $importer ): string {
		if ( '' !== $declared_prefix ) {
			return $declared_prefix;
		}

		$names = [];
		foreach ( SqlStatementStream::stream( $dump_path ) as $sql ) {
			$sql = trim( $sql );
			if ( '' === $sql || $this->is_client_directive( $sql ) ) {
				continue;
			}

			$classified = $this->classify_statement( $sql );
			if ( null === $classified || ! in_array( $classified['kind'], [ 'create', 'insert' ], true ) ) {
				continue;
			}
			if ( $importer->is_skipped_table( $classified['name'] ) ) {
				continue;
			}
			$names[ $classified['name'] ] = true;
		}

		$foreign    = false;
		$candidates = [];
		foreach ( array_keys( $names ) as $name ) {
			if ( ! str_starts_with( $name, $local_prefix ) ) {
				$foreign = true;
			}

			foreach ( self::CORE_STEMS as $stem ) {
				if ( ! str_ends_with( $name, $stem ) ) {
					continue;
				}
				$candidate = substr( $name, 0, -strlen( $stem ) );
				if ( '' !== trim( $candidate, '_' ) && $candidate !== $local_prefix && ! str_starts_with( $name, $local_prefix ) ) {
					$candidates[ $candidate ] = true;
				}
				break;
			}
		}

		if ( ! $foreign ) {
			return '';
		}

		// A plugin table can end in a core stem too (wpx_yoast_seo_links looks like
		// prefix wpx_yoast_seo_). Every site has an options table, so prefer a candidate
		// the dump has one under; a partial dump without one falls back to the stems alone.
		$with_options = array_filter(
			array_keys( $candidates ),
			static fn( string $candidate ): bool => isset( $names[ $candidate . 'options' ] )
		);
		$agreed = [] !== $with_options ? array_values( $with_options ) : array_keys( $candidates );
		if ( 1 === count( $agreed ) ) {
			$prefix = $agreed[0];
			foreach ( array_keys( $names ) as $name ) {
				if ( ! str_starts_with( $name, $local_prefix ) && ! str_starts_with( $name, $prefix ) ) {
					throw new RuntimeException(
						sprintf(
							/* translators: %s: table name that carries no inferable prefix */
							__( 'The SQL file mixes tables that share no single prefix (first foreign table: %s). Pass the source prefix explicitly.', 'shiftora' ),
							$name
						)
					);
				}
			}

			return $prefix;
		}

		if ( 0 === count( $agreed ) ) {
			throw new RuntimeException(
				sprintf(
					/* translators: %s: comma-separated foreign table names */
					__( 'No source prefix can be inferred for the SQL file (tables: %s). Pass the source prefix explicitly.', 'shiftora' ),
					implode( ', ', array_slice( array_keys( $names ), 0, 4 ) )
				)
			);
		}

		throw new RuntimeException(
			sprintf(
				/* translators: %1$s: inferred prefix; %2$s: table that disagrees */
				__( 'Conflicting source prefixes in the SQL file (%1$s and others across the tables). Pass the source prefix explicitly.', 'shiftora' ),
				implode( ', ', array_slice( $agreed, 0, 2 ) )
			)
		);
	}

	/**
	 * Maps a dump table name to the bare name finalize_staging() expects. The
	 * source prefix is checked before the local one so overlapping prefixes
	 * (wp_modern_ vs wp_) strip to the right stem; a name under neither is kept.
	 */
	private function bare_name( string $table, string $local_prefix, string $src_prefix ): string {
		if ( '' !== $src_prefix ) {
			if ( str_starts_with( $table, $src_prefix ) ) {
				return substr( $table, strlen( $src_prefix ) );
			}
			if ( str_starts_with( $table, $local_prefix ) ) {
				return substr( $table, strlen( $local_prefix ) );
			}

			return $table;
		}

		return str_starts_with( $table, $local_prefix )
			? substr( $table, strlen( $local_prefix ) )
			: $table;
	}

	/** Points a CREATE TABLE / INSERT INTO head at its staging table; only the head changes, so row values are never rewritten. */
	private function rewrite_head_to_staging( string $sql, string $kind, string $staging ): string {
		$pattern = 'create' === $kind
			? '/^(\s*CREATE\s+TABLE(?:\s+IF\s+NOT\s+EXISTS)?\s+)(`?)[A-Za-z0-9_$]+(`?)/i'
			: '/^(\s*INSERT\s+INTO\s+)(`?)[A-Za-z0-9_$]+(`?)/i';

		return (string) preg_replace_callback(
			$pattern,
			static fn( array $m ): string => $m[1] . '`' . $staging . '`',
			$sql
		);
	}

	/** Compacts a statement to a one-line preview for error messages. */
	private function preview( string $sql ): string {
		$preview = trim( (string) preg_replace( '/\s+/', ' ', $sql ) );

		return mb_substr( $preview, 0, 180 );
	}

// phpcs:enable WordPress.Security.EscapeOutput.ExceptionNotEscaped
}