<?php
/**
 * Structural fingerprinting of CREATE TABLE DDL.
 *
 * @package Shiftora\Migration
 */

declare(strict_types=1);

namespace Shiftora\Migration;

use RuntimeException;

// phpcs:disable WordPress.Security.EscapeOutput.ExceptionNotEscaped -- exception messages are not rendered as HTML output.

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

/**
 * Collapses a CREATE TABLE statement to the structure that must survive the move
 * from source to staging.
 *
 * dbDelta rewrites prefixes and reshapes DDL (bigint(20) -> bigint), so raw SQL
 * equality can't work. Parsing respects commas inside types (decimal(10,2)) and
 * literals (enum('a','b')).
 */
final class SchemaFingerprint {

	/**
	 * Appended to a constraint name while the definition sits on a staging
	 * table. Specific enough that no source schema carries it.
	 */
	public const STAGED_CONSTRAINT_SUFFIX = '_shiftora_staged';

	/**
	 * Normalize a CREATE TABLE statement (or SHOW CREATE TABLE output).
	 *
	 * @return array{
	 *   columns: array<string, array{type: string, unsigned: bool, nullable: bool, auto_increment: bool, default: null|string}>,
	 *   indexes: array<string, array{kind: string, unique: bool, columns: string[]}>,
	 *   constraints: array<string, array{kind: string, ...}>
	 * }
	 */
	public static function fingerprint( string $ddl ): array {
		// Versioned server comments (/*!80023 ... */) are noise on both sides.
		$ddl = (string) preg_replace( '/\/\*![0-9]*.*?\*\//s', '', $ddl );

		// A bare %s outside a string literal means a template placeholder leaked
		// into the DDL. Reject it rather than certify a guessed shape.
		if ( self::has_sprintf_marker( $ddl ) ) {
			throw new RuntimeException( 'SchemaFingerprint: sprintf marker %s found in DDL, outside the fingerprint contract.' );
		}

		$open = strpos( $ddl, '(' );
		if ( false === $open ) {
			throw new RuntimeException( 'SchemaFingerprint: no column list found in DDL.' );
		}
		$body = self::balanced( $ddl, $open );

		$columns     = [];
		$indexes     = [];
		$constraints = [];
		foreach ( self::split_top_level( $body ) as $def ) {
			$def = trim( $def );
			if ( '' === $def ) {
				continue;
			}
			if ( self::is_constraint_definition( $def ) ) {
				$parsed            = self::parse_constraint( $def );
				$constraints[ $parsed['key'] ] = $parsed['value'];
				continue;
			}
			if ( self::is_index_definition( $def ) ) {
				$indexed = self::parse_index( $def );
				$name    = $indexed['name'];
				unset( $indexed['name'] );
				$indexes[ $name ] = $indexed;
				continue;
			}
			$col    = self::parse_column( $def );
			$name   = $col['name'];
			unset( $col['name'] );
			$columns[ $name ] = $col;
		}

		ksort( $columns );
		ksort( $indexes );
		ksort( $constraints );

		return [ 'columns' => $columns, 'indexes' => $indexes, 'constraints' => $constraints ];
	}

	/**
	 * The same fingerprint with table constraints removed.
	 *
	 * Staging tables are built without constraints (dbDelta can't carry them, and the
	 * destination still holds the outgoing table's names), so the pre-commit gate masks
	 * them. Constraints are verified on the live table after the swap.
	 */
	public static function without_constraints( array $fp ): array {
		$fp['constraints'] = [];
		return $fp;
	}

	/**
	 * The constraint clauses of a CREATE TABLE, in source order, ready for
	 * ALTER TABLE ... ADD. Each is a bare definition, not a full statement.
	 *
	 * @return string[]
	 */
	public static function constraint_clauses( string $ddl ): array {
		$parts = self::body_split( $ddl );
		if ( null === $parts ) {
			return [];
		}

		$clauses = [];
		foreach ( $parts['defs'] as $def ) {
			if ( self::is_constraint_definition( $def ) ) {
				$clauses[] = $def;
			}
		}
		return $clauses;
	}

	/**
	 * The ALTER TABLE keyword a constraint clause is dropped under.
	 *
	 * Only a foreign key and a check are named definitions a caller can detach; any
	 * other tracked constraint is an index and goes with the table.
	 *
	 * Checks drop differently per server: MySQL has DROP CHECK from 8.0.16 and DROP
	 * CONSTRAINT from 8.0.19, MariaDB only DROP CONSTRAINT. MariaDB adds a check to
	 * every JSON column, so that path is common there.
	 *
	 * @param \wpdb $wpdb The connection the ALTER will run on.
	 */
	public static function constraint_drop_keyword( string $clause, \wpdb $wpdb ): string {
		if ( 1 === preg_match( '/\sFOREIGN KEY\b/i', $clause ) ) {
			return 'FOREIGN KEY';
		}

		return self::server_is_mariadb( $wpdb ) ? 'CONSTRAINT' : 'CHECK';
	}

	/**
	 * Whether the connection is to MariaDB rather than MySQL. Cached per
	 * request, since the server does not change under a running process.
	 */
	public static function server_is_mariadb( \wpdb $wpdb ): bool {
		static $is_mariadb = null;

		if ( null === $is_mariadb ) {
			// Memoized in the static above, so a cache layer would add nothing.
			$info       = method_exists( $wpdb, 'db_server_info' )
				? (string) $wpdb->db_server_info()
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery -- server metadata, nothing to cache
				: (string) $wpdb->get_var( 'SELECT VERSION()' );
			$is_mariadb = false !== stripos( $info, 'mariadb' );
		}

		return $is_mariadb;
	}

	/**
	 * The declared name of a constraint clause, or '' when it has none.
	 *
	 * SHOW CREATE TABLE only prints auto-generated names, so an unnamed clause can't
	 * collide with another table's name.
	 */
	public static function constraint_name( string $clause ): string {
		if ( ! preg_match( '/^CONSTRAINT\s+(?:`([^`]+)`|(\S+))\s+/i', $clause, $m ) ) {
			return '';
		}

		return '' !== ( $m[1] ?? '' ) ? $m[1] : $m[2];
	}

	/**
	 * Rewrite a constraint clause to a name the outgoing table can't already hold.
	 *
	 * Constraint names are unique across a schema. A reserved suffix lets the
	 * definition travel with the table and be renamed back after the swap, which is
	 * also how a pull (no schema payload at commit time) delivers foreign keys.
	 *
	 * @return string|null Null for a clause with no name to rewrite.
	 */
	public static function scope_constraint( string $clause ): ?string {
		$name = self::constraint_name( $clause );
		if ( '' === $name ) {
			return null;
		}
		if ( str_ends_with( $name, self::STAGED_CONSTRAINT_SUFFIX ) ) {
			return null;
		}
		if ( strlen( $name ) + strlen( self::STAGED_CONSTRAINT_SUFFIX ) > 64 ) {
			throw new RuntimeException(
				"Constraint `{$name}` is too long to carry across staging, and leaving it behind would drop it silently."
			);
		}

		$scoped = $name . self::STAGED_CONSTRAINT_SUFFIX;

		return (string) preg_replace(
			'/^(CONSTRAINT\s+)(?:`[^`]+`|\S+)/i',
			'$1`' . $scoped . '`',
			$clause,
			1
		);
	}

	/**
	 * Whether a clause carries the reserved suffix added for staging.
	 */
	public static function is_staged_constraint( string $clause ): bool {
		$name = self::constraint_name( $clause );

		return '' !== $name && str_ends_with( $name, self::STAGED_CONSTRAINT_SUFFIX );
	}

	/**
	 * The source's own name for a clause scoped for staging, otherwise unchanged.
	 */
	public static function unscope_constraint( string $clause ): string {
		$name = self::constraint_name( $clause );
		if ( '' === $name || ! str_ends_with( $name, self::STAGED_CONSTRAINT_SUFFIX ) ) {
			return $clause;
		}

		$original = substr( $name, 0, -strlen( self::STAGED_CONSTRAINT_SUFFIX ) );

		return (string) preg_replace(
			'/^(CONSTRAINT\s+)(?:`[^`]+`|\S+)/i',
			'$1`' . $original . '`',
			$clause,
			1
		);
	}

	/**
	 * The same CREATE TABLE with every table-level constraint renamed onto the staging suffix.
	 *
	 * A staging copy can't be created while the live table still carries the source's
	 * names (a restore hits this rebuilding a dump beside the data it replaces).
	 * Definitions needing no rename, and statements with no column list, come back
	 * byte-identical.
	 *
	 * @throws RuntimeException When a name is too long to carry the suffix.
	 */
	public static function scope_constraint_names( string $ddl ): string {
		$parts = self::body_split( $ddl );
		if ( null === $parts ) {
			return $ddl;
		}

		$defs     = [];
		$rewrites = 0;

		foreach ( $parts['defs'] as $def ) {
			if ( self::is_constraint_definition( $def ) ) {
				$scoped = self::scope_constraint( $def );
				if ( null !== $scoped ) {
					$def       = $scoped;
					$rewrites++;
				}
			}
			$defs[] = $def;
		}

		if ( 0 === $rewrites ) {
			return $ddl;
		}

		return self::reassemble( $parts, $defs );
	}

	/**
	 * The same CREATE TABLE with every table-level constraint removed.
	 *
	 * dbDelta reads a line starting with CONSTRAINT as a column named `constraint`.
	 * Column definitions stay untouched: MySQL ignores an inline REFERENCES and hoists
	 * an inline CHECK into a top-level CONSTRAINT.
	 */
	public static function strip_constraints( string $ddl ): string {
		$parts = self::body_split( $ddl );
		if ( null === $parts ) {
			return $ddl;
		}

		$kept = [];
		foreach ( $parts['defs'] as $def ) {
			if ( ! self::is_constraint_definition( $def ) ) {
				$kept[] = $def;
			}
		}

		return self::reassemble( $parts, $kept );
	}

	public static function matches( array $a, array $b ): bool {
		return $a === $b;
	}

	/**
	 * Human-readable differences for the finalize error message.
	 *
	 * @return string[]
	 */
	public static function describe( array $a, array $b ): array {
		$diffs = [];
		$names = array_unique( array_merge( array_keys( $a['columns'] ), array_keys( $b['columns'] ) ) );
		sort( $names );
		foreach ( $names as $name ) {
			$x = $a['columns'][ $name ] ?? null;
			$y = $b['columns'][ $name ] ?? null;
			if ( $x === $y ) {
				continue;
			}
			if ( null === $y ) {
				$diffs[] = "column `{$name}` is missing on the receiver";
			} elseif ( null === $x ) {
				$diffs[] = "column `{$name}` is unexpected on the receiver";
			} else {
				foreach ( [ 'type', 'unsigned', 'nullable', 'auto_increment', 'default' ] as $attr ) {
					if ( $x[ $attr ] !== $y[ $attr ] ) {
						$diffs[] = sprintf(
							'column `%s` %s: %s != %s',
							$name,
							$attr,
							self::describe_value( $x[ $attr ] ),
							self::describe_value( $y[ $attr ] )
						);
					}
				}
			}
		}
		$index_names = array_unique( array_merge( array_keys( $a['indexes'] ), array_keys( $b['indexes'] ) ) );
		sort( $index_names );
		foreach ( $index_names as $name ) {
			if ( $a['indexes'][ $name ] !== $b['indexes'][ $name ] ) {
				$diffs[] = "index `{$name}` differs";
			}
		}
		$constraint_names = array_unique( array_merge(
			array_keys( $a['constraints'] ?? [] ),
			array_keys( $b['constraints'] ?? [] )
		) );
		sort( $constraint_names );
		foreach ( $constraint_names as $name ) {
			if ( ( $a['constraints'][ $name ] ?? null ) !== ( $b['constraints'][ $name ] ?? null ) ) {
				$diffs[] = "constraint `{$name}` differs";
			}
		}
		return $diffs;
	}

	public static function column_count( array $fp ): int {
		return count( $fp['columns'] );
	}

	/**
	 * A fingerprint value, readable in a failure message.
	 *
	 * PHP prints null as nothing, which would make a missing default look like an
	 * empty-string one.
	 */
	private static function describe_value( mixed $value ): string {
		if ( null === $value ) {
			return '<none>';
		}
		if ( is_bool( $value ) ) {
			return $value ? 'yes' : 'no';
		}
		if ( '' === (string) $value ) {
			return 'empty-string';
		}

		return (string) $value;
	}

	/**
	 * The head, definitions and tail of a CREATE TABLE.
	 *
	 * head runs through the opening parenthesis, tail starts at the matching close,
	 * defs are the trimmed non-empty entries between. Null when there is no column list.
	 *
	 * @return array{head: string, defs: list<string>, tail: string}|null
	 */
	private static function body_split( string $ddl ): ?array {
		$open = strpos( $ddl, '(' );
		if ( false === $open ) {
			return null;
		}

		$body = self::balanced( $ddl, $open );
		$defs = [];
		foreach ( self::split_top_level( $body ) as $def ) {
			$def = trim( $def );
			if ( '' !== $def ) {
				$defs[] = $def;
			}
		}

		return [
			'head' => substr( $ddl, 0, $open + 1 ),
			'defs' => $defs,
			'tail' => substr( $ddl, $open + 1 + strlen( $body ) ),
		];
	}

	/**
	 * A body rebuilt from a replacement list of definitions.
	 *
	 * @param array{head: string, defs: list<string>, tail: string} $parts
	 * @param list<string>                                          $defs
	 */
	private static function reassemble( array $parts, array $defs ): string {
		return $parts['head']
			. ( [] === $defs ? '' : "\n\t" . implode( ",\n\t", $defs ) . "\n" )
			. $parts['tail'];
	}

	/** @return string[] */
	private static function split_top_level( string $block ): array {
		if ( '' === $block ) {
			return [];
		}
		$parts = [];
		$buf   = '';
		$depth = 0;
		$len   = strlen( $block );
		for ( $i = 0; $i < $len; $i++ ) {
			$c = $block[ $i ];
			if ( "'" === $c ) {
				$end = self::skip_literal( $block, $i );
				$buf .= substr( $block, $i, $end - $i );
				$i    = $end - 1;
				continue;
			}
			if ( '(' === $c ) {
				$depth++;
			} elseif ( ')' === $c ) {
				$depth--;
			}
			if ( ',' === $c && 0 === $depth ) {
				$parts[] = trim( $buf );
				$buf     = '';
				continue;
			}
			$buf .= $c;
		}
		$parts[] = trim( $buf );
		return $parts;
	}

	/** True when the text holds a %s outside a single-quoted literal. */
	private static function has_sprintf_marker( string $s ): bool {
		$len = strlen( $s );
		for ( $i = 0; $i < $len; $i++ ) {
			if ( "'" === $s[ $i ] ) {
				$i = self::skip_literal( $s, $i ) - 1;
				continue;
			}
			if ( '%' === $s[ $i ] && $i + 1 < $len && 's' === $s[ $i + 1 ] ) {
				return true;
			}
		}
		return false;
	}

	/** Text inside the top-level paren group that starts at $open. */
	private static function balanced( string $ddl, int $open ): string {
		$depth = 0;
		$len   = strlen( $ddl );
		for ( $i = $open; $i < $len; $i++ ) {
			$c = $ddl[ $i ];
			if ( "'" === $c ) {
				$i = self::skip_literal( $ddl, $i ) - 1;
				continue;
			}
			if ( '(' === $c ) {
				$depth++;
			} elseif ( ')' === $c ) {
				$depth--;
				if ( 0 === $depth ) {
					return substr( $ddl, $open + 1, $i - $open - 1 );
				}
			}
		}
		throw new RuntimeException( 'SchemaFingerprint: unbalanced column list in DDL.' );
	}

	/**
	 * Index just past a single-quoted string literal starting at $s[$i].
	 *
	 * Handles backslash-escaped (`it\'s`) and doubled (`O''Brien`) quotes, which a
	 * plain toggling scanner would end early and skew paren depth.
	 */
	private static function skip_literal( string $s, int $i ): int {
		$i++;
		$len = strlen( $s );
		while ( $i < $len ) {
			$c = $s[ $i ];
			if ( '\\' === $c && $i + 1 < $len ) {
				$i += 2;
				continue;
			}
			if ( "'" === $c ) {
				if ( $i + 1 < $len && "'" === $s[ $i + 1 ] ) {
					$i += 2;
					continue;
				}
				return $i + 1;
			}
			$i++;
		}
		return $i;
	}

	/**
	 * A table-level FOREIGN KEY or CHECK clause.
	 *
	 * CONSTRAINT is optional in MySQL, so a bare `FOREIGN KEY (` or `CHECK (` must
	 * match. Anchoring CONSTRAINT on a following keyword excludes a column named
	 * `constraint`.
	 */
	private static function is_constraint_definition( string $def ): bool {
		return (bool) preg_match(
			'/^(?:CONSTRAINT\b(?:\s+`[^`]+`|\s+[a-zA-Z0-9_$]+)?\s+(?:FOREIGN KEY|CHECK)\b|FOREIGN KEY\b|CHECK\s*\()/i',
			$def
		);
	}

	private static function is_index_definition( string $def ): bool {
		return (bool) preg_match( '/^(PRIMARY KEY|UNIQUE( KEY| INDEX)?|KEY|INDEX|FULLTEXT( KEY| INDEX)?)\b/i', $def );
	}

	/**
	 * Read one FOREIGN KEY or CHECK clause into a comparable shape.
	 *
	 * @return array{key: string, value: array<string, mixed>}
	 */
	private static function parse_constraint( string $def ): array {
		$name = '';
		if ( preg_match( '/^CONSTRAINT\s+(?:`([^`]*)`|(\S+))\s+/i', $def, $m ) ) {
			$name = ( '' !== ( $m[1] ?? '' ) ? $m[1] : $m[2] );
			$def  = substr( $def, strlen( $m[0] ) );
		}

		if ( 0 === stripos( $def, 'FOREIGN KEY' ) ) {
			return self::parse_foreign_key( $def, $name );
		}
		if ( 0 === stripos( $def, 'CHECK' ) ) {
			$expression = trim( (string) preg_replace( '/\s+/', ' ', substr( $def, 5 ) ) );
			return [
				'key'   => 'check:' . ( '' !== $name ? $name : md5( $expression ) ),
				'value' => [
					'kind'       => 'check',
					'name'       => $name,
					'expression' => $expression,
				],
			];
		}

		throw new RuntimeException( 'SchemaFingerprint: unsupported constraint clause in DDL.' );
	}

	/** @return array{key: string, value: array<string, mixed>} */
	private static function parse_foreign_key( string $def, string $name ): array {
		$open = strpos( $def, '(' );
		if ( false === $open ) {
			throw new RuntimeException( 'SchemaFingerprint: FOREIGN KEY without a column list.' );
		}
		$columns = self::split_name_list( self::balanced( $def, $open ) );

		$rest = substr( $def, $open + 1 + strlen( self::balanced( $def, $open ) ) + 1 );
		if ( ! preg_match( '/^\s*REFERENCES\s+(`[^`]+`|\S+)/i', $rest, $m ) ) {
			throw new RuntimeException( 'SchemaFingerprint: FOREIGN KEY without a REFERENCES target.' );
		}
		$ref  = trim( $m[1], '`' );
		$ropen = strpos( $rest, '(' );
		if ( false === $ropen ) {
			throw new RuntimeException( 'SchemaFingerprint: REFERENCES without a column list.' );
		}
		$ref_columns = self::split_name_list( self::balanced( $rest, $ropen ) );
		$tail        = substr( $rest, $ropen + 1 + strlen( self::balanced( $rest, $ropen ) ) + 1 );

		$action = static function ( string $keyword, string $text ): ?string {
			return preg_match(
				'/\b' . $keyword . '\s+(CASCADE|SET NULL|NO ACTION|RESTRICT|SET DEFAULT)\b/i',
				$text,
				$am
			) ? strtoupper( $am[1] ) : null;
		};

		return [
			'key'   => 'foreign:' . ( '' !== $name ? $name : md5( $def ) ),
			'value' => [
				'kind'        => 'foreign',
				'name'        => $name,
				'columns'     => $columns,
				'ref'         => $ref,
				'ref_columns' => $ref_columns,
				'on_delete'   => $action( 'ON DELETE', $tail ),
				'on_update'   => $action( 'ON UPDATE', $tail ),
			],
		];
	}

	/** @return string[] Backticks stripped, surrounding space trimmed. */
	private static function split_name_list( string $list ): array {
		$names = [];
		foreach ( explode( ',', $list ) as $part ) {
			$part = trim( $part );
			if ( '' !== $part ) {
				$names[] = trim( $part, '`' );
			}
		}
		return $names;
	}

	private static function parse_index( string $def ): array {
		$abbrev = $def;

		if ( preg_match( '/^PRIMARY KEY/i', $abbrev ) ) {
			$kind   = 'PRIMARY';
			$unique = true;
		} elseif ( preg_match( '/^UNIQUE( KEY| INDEX)?/i', $abbrev ) ) {
			$kind   = 'UNIQUE';
			$unique = true;
		} elseif ( preg_match( '/^FULLTEXT( KEY| INDEX)?/i', $abbrev ) ) {
			$kind   = 'FULLTEXT';
			$unique = false;
		} else {
			$kind   = 'INDEX';
			$unique = false;
		}

		preg_match( '/^(PRIMARY KEY|UNIQUE( KEY| INDEX)?|KEY|INDEX|FULLTEXT( KEY| INDEX)?)\s*(?:`?([a-zA-Z0-9_]+)`?)?/i', $abbrev, $mn );
		// g1 = leading keyword alternation, g2/g3 = optional inner groups of
		// UNIQUE/FULLTEXT, g4 = the (backticked) name.
		$name = 'PRIMARY' === $kind ? 'PRIMARY' : ( $mn[4] ?? '' );

		// The column list is the first balanced paren group after the
		// keyword/name. A plain `\(([^()]*)\)` regex fails on functional
		// indexes, which nest parens.
		$first = strpos( $abbrev, '(' );
		if ( false === $first ) {
			return [ 'name' => $name, 'kind' => $kind, 'unique' => $unique, 'columns' => [] ];
		}
		$inner = self::balanced( $abbrev, $first );

		$cols = [];
		foreach ( self::split_top_level( $inner ) as $c ) {
			$c = trim( $c );
			if ( '' === $c ) {
				continue;
			}
			// A segment is a column with an optional prefix length. Anything
			// else (an expression, an index order suffix) is unsupported, so
			// reject the whole index instead of storing a guess.
			if ( ! preg_match( '/^`?([a-zA-Z0-9_]+)`?(\(\d+\))?$/', $c, $cm ) ) {
				throw new RuntimeException( "SchemaFingerprint: unsupported index segment \"{$c}\" (expressions and index ordering are outside the fingerprint contract)." );
			}
			$cols[] = trim( $cm[1], '`' ) . ( $cm[2] ?? '' );
		}

		return [
			'name'    => $name,
			'kind'    => $kind,
			'unique'  => $unique,
			'columns' => $cols,
		];
	}

	/** @return array{name: string, type: string, unsigned: bool, nullable: bool, auto_increment: bool, default: null|string} */
	private static function parse_column( string $def ): array {
		if ( preg_match( '/^`([^`]+)`\s*(.+)$/s', $def, $m ) ) {
			$name = $m[1];
			$rest = $m[2];
		} elseif ( preg_match( '/^([a-zA-Z0-9_]+)\s+(.+)$/s', $def, $m ) ) {
			$name = $m[1];
			$rest = $m[2];
		} else {
			$name = '';
			$rest = $def;
		}

		$col = [
			'name'           => $name,
			'type'           => '',
			'unsigned'       => false,
			'nullable'       => true,
			'auto_increment' => false,
			'default'        => null,
			'generated'      => false,
		];

		$tokens = self::tokens( $rest );
		for ( $i = 0, $n = count( $tokens ); $i < $n; $i++ ) {
			$tok   = $tokens[ $i ];
			$upper = strtoupper( $tok );
			if ( '' === $col['type'] ) {
				$col['type'] = self::normalize_type( $tok );
				continue;
			}
			if ( 'UNSIGNED' === $upper ) {
				$col['unsigned'] = true;
			} elseif ( 'NOT' === $upper && isset( $tokens[ $i + 1 ] ) && 'NULL' === strtoupper( $tokens[ $i + 1 ] ) ) {
				$col['nullable'] = false;
				$i++;
			} elseif ( 'NULL' === $upper ) {
				$col['nullable'] = true;
			} elseif ( 'AUTO_INCREMENT' === $upper ) {
				$col['auto_increment'] = true;
			} elseif ( 'DEFAULT' === $upper && isset( $tokens[ $i + 1 ] ) ) {
				$col['default'] = self::normalize_default( $tokens[ $i + 1 ] );
				$i++;
			} elseif ( 'STORED' === $upper ) {
				$col['generated'] = 'stored';
			} elseif ( 'VIRTUAL' === $upper || 'GENERATED' === $upper ) {
				// VIRTUAL is the default storage, and GENERATED precedes the AS
				// clause; a later STORED token overrides this.
				$col['generated'] = $col['generated'] ?: 'virtual';
			} elseif ( 'AS' === $upper && isset( $tokens[ $i + 1 ] ) && str_starts_with( $tokens[ $i + 1 ], '(' ) ) {
				// Generated column, explicit (`GENERATED ALWAYS AS (...)`) or implicit
				// (`b int AS (a + 1)`). The destination recreates it from the same CREATE TABLE,
				// so the expression isn't compared; skip it so it isn't read as a type or default.
				$col['generated'] = $col['generated'] ?: 'virtual';
				$i++;
			}
			// CHARACTER SET / COLLATE / COMMENT / ON UPDATE are ignored since
			// they do not affect the structural verdict. An inline REFERENCES is
			// ignored too: MySQL parses and drops it, so neither side's
			// SHOW CREATE TABLE shows it.
		}

		return $col;
	}

	/** @return string[] Whitespace tokens, keeping balanced parens and literals whole. */
	private static function tokens( string $s ): array {
		$tokens = [];
		$buf    = '';
		$depth  = 0;
		$len    = strlen( $s );
		for ( $i = 0; $i < $len; $i++ ) {
			$c = $s[ $i ];
			if ( "'" === $c ) {
				$end = self::skip_literal( $s, $i );
				$buf .= substr( $s, $i, $end - $i );
				$i    = $end - 1;
				continue;
			}
			if ( '(' === $c ) {
				$depth++;
			} elseif ( ')' === $c ) {
				$depth--;
			}
			if ( ( ' ' === $c || "\t" === $c ) && 0 === $depth ) {
				if ( '' !== $buf ) {
					$tokens[] = $buf;
					$buf      = '';
				}
				continue;
			}
			$buf .= $c;
		}
		if ( '' !== $buf ) {
			$tokens[] = $buf;
		}
		return $tokens;
	}

	private static function normalize_type( string $tok ): string {
		$t = trim( $tok );

		// ENUM/SET literal case and escaped quotes are semantic: lowercasing the
		// whole token would let a receiver that changed 'Draft' to 'draft'
		// pass. Only the type keyword is folded.
		$paren = strpos( $t, '(' );
		if ( false === $paren ) {
			return strtolower( $t );
		}
		$head = strtolower( trim( substr( $t, 0, $paren ) ) );
		$args = trim( substr( $t, $paren ) );
		// dbDelta drops integer display widths (bigint(20) -> bigint); decimal
		// and numeric precision must survive.
		if ( in_array( $head, [ 'tinyint', 'smallint', 'mediumint', 'int', 'bigint' ], true ) ) {
			$args = (string) preg_replace( '/^\(\d+\)$/', '', $args );
		}
		return $head . $args;
	}

	private static function normalize_default( string $tok ): ?string {
		$raw = trim( $tok );
		if ( '' === $raw ) {
			return null;
		}
		// Strip the outer quotes but keep the literal's own bytes, escapes
		// included, so a receiver that only changed the quote still shows up.
		// MySQL also accepts double-quoted literals by default, and SHOW CREATE
		// may echo either form.
		$quote = null;
		if ( "'" === $raw[0] && str_ends_with( $raw, "'" ) && strlen( $raw ) >= 2 ) {
			$quote = "'";
		} elseif ( '"' === $raw[0] && str_ends_with( $raw, '"' ) && strlen( $raw ) >= 2 ) {
			$quote = '"';
		}
		if ( null !== $quote ) {
			return substr( $raw, 1, -1 );
		}
		// Only values dbDelta can reproduce are accepted: NULL, an unquoted number or the
		// current-timestamp family. DEFAULT NULL and no DEFAULT fold together, since MySQL
		// omits the clause on a nullable column and MariaDB writes it out.
		if ( 'null' === strtolower( $raw ) ) {
			return null;
		}

		if ( preg_match( '/^-?\d+(\.\d+)?$/', $raw ) ) {
			return $raw;
		}

		// Timestamp family, one spelling each: NOW and CURRENT_TIMESTAMP are
		// synonyms, as are LOCALTIME and LOCALTIMESTAMP, and each appears with
		// and without parentheses depending on the engine.
		if ( preg_match( '/^(current_timestamp|now)(\(\d*\))?$/i', $raw, $now ) ) {
			return 'current_timestamp' . self::precision_of( $now[2] ?? '' );
		}
		if ( preg_match( '/^(localtime|localtimestamp)(\(\d*\))?$/i', $raw, $local ) ) {
			return 'localtimestamp' . self::precision_of( $local[2] ?? '' );
		}

		// Expression default (MySQL 8 `DEFAULT (expr)`), function call or bit literal.
		// The destination applies the same CREATE TABLE, so the clause is carried verbatim
		// and compared as-is. A receiver that reformats it shows as a mismatch.
		return $raw;
	}

	/**
	 * The fractional-seconds part of a timestamp default, kept only when it matters.
	 * CURRENT_TIMESTAMP and CURRENT_TIMESTAMP() fold to no suffix; (3) is kept so a
	 * receiver that cut the precision is detected.
	 */
	private static function precision_of( string $paren ): string {
		$digits = trim( $paren, '()' );

		return '' === $digits ? '' : '(' . $digits . ')';
	}

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