<?php
/**
 * Private, streaming SQL backup with an actual non-running restore verification.
 *
 * @package ABeCeBooking
 */
namespace ABeCe\Booking\Infrastructure\Database;

use RuntimeException;

/** No copied WordPress site is bootstrapped when the backup is verified. */
final class DatabaseSnapshot {
	/** @param \wpdb $wpdb Locked source database connection. */
	public function __construct( private readonly \wpdb $wpdb ) {}

	/** @return list<string> */
	public function tables(): array {
		$rows = $this->wpdb->get_results( 'SHOW FULL TABLES', ARRAY_N );
		$this->check();
		$names = array();
		foreach ( $rows as $row ) {
			if ( $row[1] !== 'BASE TABLE' || ! str_starts_with( $row[0], $this->wpdb->prefix ) ) {
				throw new RuntimeException( 'ABECE_BOOKING_SHARED_DATABASE_OR_VIEW' );
			}
			$names[] = $row[0];
		}
		sort( $names, SORT_STRING );
		if ( $this->wpdb->get_results( 'SHOW TRIGGERS', ARRAY_A ) ) {
			throw new RuntimeException( 'ABECE_BOOKING_UNVERIFIED_DATABASE_TRIGGER' );
		}
		$this->check();
		return $names;
	}

	/** @param list<string> $tables Exact inventory of all site database tables. @return void */
	public function lock( array $tables ): void {
		$parts = array();
		foreach ( $tables as $table ) {
			$parts[] = $this->wpdb->prepare( '%i WRITE', $table );
		}
		if ( ! $parts || $this->wpdb->query( 'LOCK TABLES ' . implode( ',', $parts ) ) === false ) {
			throw new RuntimeException( 'ABECE_BOOKING_TABLE_WRITE_LOCK_FAILED' );
		}
	}

	/** @return void */
	public function unlock(): void {
		$this->wpdb->query( 'UNLOCK TABLES' );
	}

	/**
	 * Capture structural metadata and byte-safe row fingerprints in bounded batches.
	 *
	 * @param \wpdb         $connection Source or non-running verification database.
	 * @param list<string>  $tables Exact physical table names.
	 * @param callable|null $emit Optional trusted SQL backup statement writer.
	 * @return array<string, array<string, mixed>>
	 */
	public static function fingerprint( \wpdb $connection, array $tables, ?callable $emit = null ): array {
		$timezone = $connection->get_var( 'SELECT @@SESSION.time_zone' );
		if ( ! is_string( $timezone ) || $connection->last_error || $connection->query( "SET SESSION time_zone='+00:00'" ) === false ) {
			throw new RuntimeException( 'ABECE_BOOKING_SNAPSHOT_TIMEZONE_FAILED' );
		}
		try {
			return self::fingerprint_utc( $connection, $tables, $emit );
		} finally {
			$connection->query( $connection->prepare( 'SET SESSION time_zone=%s', $timezone ) );
		}
	}

	/**
	 * Capture absolute TIMESTAMP values in UTC, including microseconds.
	 *
	 * @param \wpdb $connection Connection normalized by fingerprint().
	 * @param list<string> $tables Exact physical table names.
	 * @param callable|null $emit Trusted backup statement writer.
	 * @return array<string, array<string, mixed>>
	 */
	private static function fingerprint_utc( \wpdb $connection, array $tables, ?callable $emit ): array {
		$result = array();
		foreach ( $tables as $table ) {
			$ddl     = $connection->get_row( $connection->prepare( 'SHOW CREATE TABLE %i', $table ), ARRAY_N );
			$columns = $connection->get_results( $connection->prepare( 'SHOW FULL COLUMNS FROM %i', $table ), ARRAY_A );
			$indexes = $connection->get_results( $connection->prepare( 'SHOW INDEX FROM %i', $table ), ARRAY_A );
			if ( $connection->last_error || ! $ddl || ! $columns || ! $indexes ) {
				throw new RuntimeException( 'ABECE_BOOKING_SNAPSHOT_METADATA_FAILED' );
			}
			$order  = array();
			$binary = array();
			foreach ( $columns as &$column ) {
				unset( $column['Privileges'] ); // Account grants are not column structure.
				$binary[ $column['Field'] ] = (bool) preg_match( '/(?:blob|binary|bit)/i', $column['Type'] );
				if ( preg_match( '/(?:VIRTUAL|STORED) GENERATED/i', $column['Extra'] ) ) {
					throw new RuntimeException( 'ABECE_BOOKING_UNVERIFIED_GENERATED_COLUMN' );
				}
			}
			unset( $column );
			foreach ( $indexes as &$index ) {
				if ( $index['Key_name'] === 'PRIMARY' ) {
					$order[] = $index['Column_name'];
				}
				unset( $index['Cardinality'], $index['Table'] );
			}
			unset( $index );
			if ( ! $order ) {
				throw new RuntimeException( 'ABECE_BOOKING_BACKUP_REQUIRES_PRIMARY_KEY' );
			}
			if ( $emit ) {
				$emit( $ddl[1] );
			}
			$hashes = array();
			$offset = 0;
			do {
				$order_sql = implode( ',', array_fill( 0, count( $order ), '%i' ) );
				$rows      = $connection->get_results( $connection->prepare( 'SELECT * FROM %i ORDER BY ' . $order_sql . ' LIMIT %d OFFSET %d', $table, ...array_merge( $order, array( 500, $offset ) ) ), ARRAY_A );
				if ( $connection->last_error ) {
					throw new RuntimeException( 'ABECE_BOOKING_SNAPSHOT_ROWS_FAILED' );
				}
				foreach ( $rows as $row ) {
					$bytes  = '';
					$values = array();
					foreach ( $row as $field => $value ) {
						$bytes .= $value === null ? 'N;' : 'S' . strlen( (string) $value ) . ':' . $value . ';';
						$hex    = "X'" . bin2hex( (string) $value ) . "'";
						// Bare hex assigned to a numeric column is a numeric binary value, not decimal text.
						$values[] = $value === null ? 'NULL' : ( $binary[ $field ] ? $hex : 'CONVERT(' . $hex . ' USING utf8mb4)' );
					}
					$hashes[] = hash( 'sha256', $bytes );
					if ( $emit ) {
						// Every data byte is encoded as hexadecimal; no customer SQL is interpolated.
						$emit( $connection->prepare( 'INSERT INTO %i VALUES ', $table ) . '(' . implode( ',', $values ) . ')' );
					}
				}
				$offset += count( $rows );
			} while ( count( $rows ) === 500 );
			sort( $hashes, SORT_STRING );
			$result[ $table ] = array(
				'rows'    => $offset,
				'sha256'  => hash( 'sha256', implode( '', $hashes ) ),
				'ddl'     => $ddl[1],
				'columns' => $columns,
				'indexes' => $indexes,
			);
		}
		return $result;
	}

	/**
	 * Write and import a complete SQL backup before a source table is renamed.
	 *
	 * @param string       $directory Private directory outside WordPress and Git.
	 * @param string       $verification_db Separate empty, non-running verification database.
	 * @param list<string> $tables Full locked inventory.
	 * @return array<string, mixed> Verified backup manifest, with no raw customer rows.
	 */
	public function create_verified( string $directory, string $verification_db, array $tables ): array {
		$directory = self::private_directory( $directory );
		if ( ! preg_match( '/^abece_booking_verify_[a-z0-9_]{1,40}$/D', $verification_db ) || $verification_db === DB_NAME ) {
			throw new RuntimeException( 'ABECE_BOOKING_INVALID_VERIFICATION_DATABASE' );
		}
		$verify = new \wpdb( DB_USER, DB_PASSWORD, $verification_db, DB_HOST );
		$verify->suppress_errors( true );
		if ( $verify->get_var( 'SELECT DATABASE()' ) !== $verification_db || $verify->get_results( 'SHOW FULL TABLES' ) || $verify->last_error ) {
			throw new RuntimeException( 'ABECE_BOOKING_VERIFICATION_DATABASE_NOT_EMPTY' );
		}
		$base        = $directory . '/schema12-' . gmdate( 'Ymd-His' ) . '-' . bin2hex( random_bytes( 6 ) );
		$sql_path    = $base . '.sql';
		$record_path = $base . '.jsonl';
		$sql         = fopen( $sql_path, 'x+b' );
		$records     = fopen( $record_path, 'x+b' );
		if ( ! $sql || ! $records || ! chmod( $sql_path, 0600 ) || ! chmod( $record_path, 0600 ) ) {
			throw new RuntimeException( 'ABECE_BOOKING_PRIVATE_BACKUP_UNWRITABLE' );
		}
		$emit = static function ( string $statement ) use ( $sql, $records ): void {
			$plain = $statement . ";\n";
			$json  = wp_json_encode( $statement ) . "\n";
			if ( fwrite( $sql, $plain ) !== strlen( $plain ) || fwrite( $records, $json ) !== strlen( $json ) ) {
				throw new RuntimeException( 'ABECE_BOOKING_BACKUP_WRITE_FAILED' );
			}
		};
		try {
			$emit( 'SET NAMES utf8mb4' );
			$emit( "SET SESSION time_zone='+00:00'" );
			$emit( "SET SESSION sql_mode='NO_AUTO_VALUE_ON_ZERO'" );
			$emit( 'SET FOREIGN_KEY_CHECKS=0' );
			$before = self::fingerprint( $this->wpdb, $tables, $emit );
			$emit( 'COMMIT' );
			$emit( 'SET FOREIGN_KEY_CHECKS=1' );
			if ( ! fflush( $sql ) || ! fflush( $records ) || ! fsync( $sql ) || ! fsync( $records ) ) {
				throw new RuntimeException( 'ABECE_BOOKING_BACKUP_FLUSH_FAILED' );
			}
			rewind( $records );
			// The verification database is isolated and offline. Group row inserts
			// between DDL commits instead of forcing a disk commit for every row.
			if ( $verify->query( 'SET SESSION autocommit=0' ) === false ) {
				throw new RuntimeException( 'ABECE_BOOKING_REAL_BACKUP_IMPORT_FAILED' );
			}
			$check = hash_init( 'sha256' );
			while ( ( $line = fgets( $records ) ) !== false ) {
				$statement = json_decode( $line, true, 512, JSON_THROW_ON_ERROR );
				if ( ! is_string( $statement ) ) {
					throw new RuntimeException( 'ABECE_BOOKING_BACKUP_RECORD_INVALID' );
				}
				hash_update( $check, $statement . ";\n" );
				if ( $verify->query( $statement ) === false ) {
					throw new RuntimeException( 'ABECE_BOOKING_REAL_BACKUP_IMPORT_FAILED' );
				}
			}
			$sql_hash = hash_file( 'sha256', $sql_path );
			if ( hash_final( $check ) !== $sql_hash ) {
				throw new RuntimeException( 'ABECE_BOOKING_BACKUP_SQL_HASH_FAILED' );
			}
			$restored = self::fingerprint( $verify, $tables );
			self::compare( $before, $restored );
			$manifest      = array(
				'database'              => DB_NAME,
				'prefix'                => $this->wpdb->prefix,
				'sql'                   => $sql_path,
				'sql_sha256'            => $sql_hash,
				'records'               => $record_path,
				'records_sha256'        => hash_file( 'sha256', $record_path ),
				'verification_database' => $verification_db,
				'restored_and_verified' => true,
				'tables'                => $before,
			);
			$path          = $base . '.json';
			$manifest_file = fopen( $path, 'x+b' );
			if ( ! $manifest_file || ! chmod( $path, 0600 ) ) {
				throw new RuntimeException( 'ABECE_BOOKING_BACKUP_MANIFEST_FAILED' );
			}
			try {
				$encoded = wp_json_encode( $manifest, JSON_PRETTY_PRINT );
				if ( fwrite( $manifest_file, $encoded ) !== strlen( $encoded ) || ! fflush( $manifest_file ) || ! fsync( $manifest_file ) ) {
					throw new RuntimeException( 'ABECE_BOOKING_BACKUP_MANIFEST_FAILED' );
				}
			} finally {
				fclose( $manifest_file );
			}
			$manifest['manifest']        = $path;
			$manifest['manifest_sha256'] = hash_file( 'sha256', $path );
			return $manifest;
		} finally {
			fclose( $sql );
			fclose( $records );
			$verify->close();
		}
	}

	/** @param array $before Original snapshot. @param array $after New snapshot. @param array $map Old-to-new physical identifiers. @return void */
	public static function compare( array $before, array $after, array $map = array() ): void {
		$quoted = array();
		foreach ( $map as $old => $new ) {
			$quoted[ '`' . $old . '`' ] = '`' . $new . '`';
		}
		foreach ( $before as $old => $expected ) {
			$new = $map[ $old ] ?? $old;
			$got = $after[ $new ] ?? null;
			if ( ! $got ) {
				throw new RuntimeException( 'ABECE_BOOKING_SNAPSHOT_TABLE_MISSING' );
			}
			$expected['ddl'] = strtr( $expected['ddl'], $quoted );
			// SHOW FULL COLUMNS independently proves charset/collation equality.
			$expected['ddl'] = preg_replace( '/ CHARACTER SET [a-z0-9_]+/i', '', $expected['ddl'] );
			$got['ddl']      = preg_replace( '/ CHARACTER SET [a-z0-9_]+/i', '', $got['ddl'] );
			if ( $expected !== $got ) {
				do_action( 'abece_booking_schema13_comparison_failed', $old, $expected, $got );
				throw new RuntimeException( 'ABECE_BOOKING_SNAPSHOT_DATA_OR_STRUCTURE_CHANGED' );
			}
		}
		if ( count( $before ) !== count( $after ) ) {
			throw new RuntimeException( 'ABECE_BOOKING_SNAPSHOT_INVENTORY_CHANGED' );
		}
	}

	/** @param string $directory Explicit CLI backup destination. @return string */
	public static function private_directory( string $directory ): string {
		$path = realpath( $directory );
		if ( ! $path || ! is_dir( $path ) || ! is_writable( $path ) || ( fileperms( $path ) & 0077 ) !== 0 ) {
			throw new RuntimeException( 'ABECE_BOOKING_BACKUP_DIRECTORY_MUST_BE_PRIVATE' );
		}
		foreach ( array( ABSPATH, WP_CONTENT_DIR, dirname( __DIR__, 3 ) ) as $public ) {
			$root = realpath( $public );
			if ( $root && ( $path === $root || str_starts_with( $path, $root . DIRECTORY_SEPARATOR ) ) ) {
				throw new RuntimeException( 'ABECE_BOOKING_BACKUP_DIRECTORY_IS_PUBLIC' );
			}
		}
		for ( $parent = $path; dirname( $parent ) !== $parent; $parent = dirname( $parent ) ) {
			if ( file_exists( $parent . '/.git' ) ) {
				throw new RuntimeException( 'ABECE_BOOKING_BACKUP_DIRECTORY_IS_IN_GIT' );
			}
		}
		return $path;
	}

	/** @return void */
	private function check(): void {
		if ( $this->wpdb->last_error ) {
			throw new RuntimeException( 'ABECE_BOOKING_DATABASE_SNAPSHOT_FAILED' );
		}
	}
}