<?php
/**
 * Adapts CREATE TABLE statements from another server so they run here.
 *
 * Deterministic rewrites: table names to temporary names (including FOREIGN KEY
 * references), unique constraint names, and MySQL-only versioned comments on MariaDB.
 * Then the statement is tried; known errors trigger one fallback each and a retry:
 *   1273/1115 unknown collation/charset → this server's utf8mb4 collation
 *   1286 unknown engine → InnoDB
 *   1071 key too long → ROW_FORMAT=DYNAMIC
 *   1064 syntax error → versioned comments removed
 * Every change is recorded so the restore report can list it.
 *
 * @package AccessNow\Replica
 */

namespace AccessNow\Replica\Db;

defined( 'ABSPATH' ) || exit;

/**
 * DDL normaliser.
 */
final class Ddl_Normalizer {

	/**
	 * Connection.
	 *
	 * @var Connection
	 */
	private $db;

	/**
	 * Whether this server is MariaDB.
	 *
	 * @var bool
	 */
	private $mariadb;

	/**
	 * Changes made (for the report).
	 *
	 * @var string[]
	 */
	public $changes = array();

	/**
	 * Creates the normaliser.
	 *
	 * @param Connection $db Connection.
	 */
	public function __construct( Connection $db ) {
		$this->db      = $db;
		$this->mariadb = false !== stripos( $db->server_info(), 'mariadb' );
	}

	/**
	 * Creates a table from a source CREATE statement under a new name.
	 *
	 * @param string                $create    Source CREATE TABLE.
	 * @param array<string, string> $names     Source table name => new name (for FK references).
	 * @param string                $fk_prefix Prefix for constraint names (unique per job).
	 * @throws Db_Exception When the table cannot be created even after fallbacks.
	 */
	public function create( $create, array $names, $fk_prefix ) {
		$sql = $this->rewrite( $create, $names, $fk_prefix );
		for ( $attempt = 0; $attempt < 5; $attempt++ ) {
			try {
				$this->db->raw( $sql );
				return;
			} catch ( Db_Exception $e ) {
				$fixed = $this->fallback( $sql, $e->getCode() );
				if ( null === $fixed || $fixed === $sql ) {
					throw $e;
				}
				$sql = $fixed;
			}
		}
		$this->db->raw( $sql );
	}

	/**
	 * Deterministic rewrites.
	 *
	 * @param string $create    Statement.
	 * @param array  $names     Name map.
	 * @param string $fk_prefix Constraint prefix.
	 */
	private function rewrite( $create, array $names, $fk_prefix ) {
		// Table name (the first identifier) and references to other restored tables.
		$renamed = 0;
		$sql     = preg_replace_callback(
			'/^CREATE TABLE `((?:[^`]|``)+)`/',
			static function ( $m ) use ( $names ) {
				$name = str_replace( '``', '`', $m[1] );
				return 'CREATE TABLE ' . Connection::ident( $names[ $name ] ?? $name );
			},
			$create,
			1,
			$renamed
		);
		if ( 1 !== $renamed ) {
			// Never run anything but a CREATE TABLE of the temporary table.
			throw new \RuntimeException( 'A table definition in the backup is not a CREATE TABLE statement.' );
		}
		$sql = preg_replace_callback(
			'/REFERENCES `((?:[^`]|``)+)`/',
			static function ( $m ) use ( $names ) {
				$name = str_replace( '``', '`', $m[1] );
				return 'REFERENCES ' . Connection::ident( $names[ $name ] ?? $name );
			},
			(string) $sql
		);
		// Constraint names are unique per database: the live table may still use the same ones.
		$n   = 0;
		$sql = preg_replace_callback(
			'/CONSTRAINT `(?:[^`]|``)+`/',
			static function () use ( $fk_prefix, &$n ) {
				++$n;
				return 'CONSTRAINT ' . Connection::ident( $fk_prefix . $n );
			},
			(string) $sql
		);
		if ( ! $this->mariadb ) {
			// MariaDB → MySQL: function defaults need brackets in MySQL (DEFAULT uuid() → DEFAULT (uuid())),
			// except the timestamp functions MySQL accepts bare.
			$fixed = preg_replace( '/\bDEFAULT\s+(?!(?:current_timestamp|now|localtime|localtimestamp)\b)([a-z_][a-z0-9_]*\((?:[^()\'"]|\([^()]*\))*\))/i', 'DEFAULT ($1)', (string) $sql );
			// Aria/MariaDB-only table options and engines.
			$fixed = preg_replace( '/\s(PAGE_CHECKSUM|TRANSACTIONAL)=\d+/i', '', (string) $fixed );
			$fixed = preg_replace( '/ENGINE=(Aria|Maria)\b/i', 'ENGINE=InnoDB', (string) $fixed );
			if ( $fixed !== $sql ) {
				$this->changes[] = 'Adapted MariaDB-specific table definitions for MySQL (function defaults, Aria engine).';
				$sql             = $fixed;
			}
		}
		if ( $this->mariadb ) {
			// MySQL 8 versioned comments (e.g. /*!80016 DEFAULT ENCRYPTION='N' */) would run on MariaDB.
			$stripped = preg_replace( '#/\*!80\d{3}.*?\*/#s', '', (string) $sql );
			if ( $stripped !== $sql ) {
				$this->changes[] = 'Removed MySQL 8 options not supported by MariaDB.';
				$sql             = $stripped;
			}
		}
		return (string) $sql;
	}

	/**
	 * One fallback for a known error, or null.
	 *
	 * @param string $sql   Statement.
	 * @param int    $errno MySQL error.
	 * @return string|null
	 */
	private function fallback( $sql, $errno ) {
		switch ( (int) $errno ) {
			case 1273: // Unknown collation.
			case 1115: // Unknown character set.
				$target = $this->best_collation();
				$new    = preg_replace( '/\b(COLLATE[= ]\s*)[a-z0-9_]+/i', '${1}' . $target, $sql );
				$new    = preg_replace( '/\b(CHARSET|CHARACTER SET)([= ]\s*)(utf8mb3|utf8)\b/i', '${1}${2}utf8mb4', (string) $new );
				$this->changes[] = 'Collation changed to ' . $target . ' (the original is not available on this server).';
				return (string) $new;
			case 1286: // Unknown storage engine.
				$this->changes[] = 'Storage engine changed to InnoDB.';
				$new = preg_replace( '/ENGINE=\w+/i', 'ENGINE=InnoDB', $sql );
				return (string) preg_replace( '/\s(PAGE_CHECKSUM|TRANSACTIONAL)=\d+/i', '', (string) $new );
			case 1071: // Key too long.
				if ( false === stripos( $sql, 'ROW_FORMAT=DYNAMIC' ) ) {
					$this->changes[] = 'ROW_FORMAT=DYNAMIC added (index too long).';
					return rtrim( (string) preg_replace( '/\sROW_FORMAT=\w+/i', '', $sql ) ) . ' ROW_FORMAT=DYNAMIC';
				}
				return null;
			case 1064: // Syntax error.
				$new = preg_replace( '#/\*!\d{5}.*?\*/#s', '', $sql );
				if ( $new !== $sql ) {
					$this->changes[] = 'Removed version-specific options.';
				}
				return (string) $new;
		}
		return null;
	}

	/**
	 * Best available utf8mb4 collation on this server.
	 */
	private function best_collation() {
		static $best = null;
		if ( null === $best ) {
			$available = array();
			foreach ( $this->db->rows( "SHOW COLLATION WHERE Charset = 'utf8mb4'" ) as $row ) {
				$available[ $row['Collation'] ] = true;
			}
			foreach ( array( 'utf8mb4_unicode_520_ci', 'utf8mb4_0900_ai_ci', 'utf8mb4_uca1400_ai_ci', 'utf8mb4_unicode_ci', 'utf8mb4_general_ci' ) as $candidate ) {
				if ( isset( $available[ $candidate ] ) ) {
					$best = $candidate;
					break;
				}
			}
			$best = $best ? $best : 'utf8mb4_general_ci';
		}
		return $best;
	}
}