<?php

declare(strict_types=1);

namespace SiteFort\Core\Database;

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

final class SchemaInspector {

	private const MAX_IDENTIFIER_LENGTH = 64;
	private const STANDARD_CHARSETS = [ 'utf8mb4', 'utf8', 'utf8mb3', 'latin1' ];

	/** @var array<string,bool> */
	private static array $table_exists_cache = [];

	/** @var array<string,array<string,mixed>|null> */
	private static array $column_cache = [];

	/** @var array<string,bool> */
	private static array $index_cache = [];

	/** @var array<string,string> */
	private static array $ddl_charset_collate_cache = [];

	/** @var bool|null Memoized InnoDB availability for the current request. */
	private static ?bool $innodb_supported = null;

	/**
	 * Whether the database server can provide InnoDB.
	 */
	public static function innodb_supported(): bool {
		if ( null !== self::$innodb_supported ) {
			return self::$innodb_supported;
		}

		global $wpdb;

		self::$innodb_supported = false;
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- One-time engine capability probe.
		$rows = $wpdb->get_results( 'SHOW ENGINES', ARRAY_A );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
		if ( is_array( $rows ) ) {
			foreach ( $rows as $row ) {
				if ( 'innodb' === strtolower( (string) ( $row['Engine'] ?? '' ) )
					&& in_array( strtoupper( (string) ( $row['Support'] ?? '' ) ), [ 'YES', 'DEFAULT' ], true ) ) {
					self::$innodb_supported = true;
					break;
				}
			}
		}

		return self::$innodb_supported;
	}

	/**
	 * Clear memoized schema lookups for the current request.
	 *
	 * @param string|null $table_name Optional table name to invalidate selectively.
	 * @return void
	 */
	public static function flush_cache( ?string $table_name = null ): void {
		if ( null === $table_name || '' === $table_name ) {
			self::$table_exists_cache = [];
			self::$column_cache = [];
			self::$index_cache = [];
			self::$ddl_charset_collate_cache = [];
			return;
		}

		unset( self::$table_exists_cache[ $table_name ] );

		foreach ( array_keys( self::$column_cache ) as $cache_key ) {
			if ( str_starts_with( $cache_key, $table_name . '|' ) ) {
				unset( self::$column_cache[ $cache_key ] );
			}
		}

		foreach ( array_keys( self::$index_cache ) as $cache_key ) {
			if ( str_starts_with( $cache_key, $table_name . '|' ) ) {
				unset( self::$index_cache[ $cache_key ] );
			}
		}
	}

	public static function table_exists( string $table_name ): bool {
		$table_name = self::table_name_for_read( $table_name );
		if ( null === $table_name ) {
			return false;
		}

		if ( array_key_exists( $table_name, self::$table_exists_cache ) ) {
			return self::$table_exists_cache[ $table_name ];
		}

		global $wpdb;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Schema metadata is memoized per request; persistent caching can become stale after migrations.
		$result = $wpdb->get_var(
			$wpdb->prepare(
				'SELECT TABLE_NAME
				FROM information_schema.tables
				WHERE table_schema = DATABASE()
				AND table_name = %s
				LIMIT 1',
				$table_name
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching

		self::$table_exists_cache[ $table_name ] = is_string( $result ) && $result === $table_name;
		if ( ! self::$table_exists_cache[ $table_name ] ) {
			self::$table_exists_cache[ $table_name ] = self::table_exists_via_show( $table_name );
		}

		return self::$table_exists_cache[ $table_name ];
	}

	public static function has_columns( string $table_name, array $columns ): bool {
		$table_name = self::table_name_for_read( $table_name );
		if ( null === $table_name ) {
			return false;
		}

		if ( empty( $columns ) ) {
			return true;
		}

		if ( ! self::table_exists( $table_name ) ) {
			return false;
		}

		foreach ( $columns as $column ) {
			if ( null === self::get_column( $table_name, (string) $column ) ) {
				return false;
			}
		}

		return true;
	}

	public static function get_column( string $table_name, string $column ): ?array {
		$table_name = self::table_name_for_read( $table_name );
		if ( null === $table_name ) {
			return null;
		}

		if ( ! self::table_exists( $table_name ) ) {
			return null;
		}

		$cache_key = $table_name . '|' . $column;
		if ( array_key_exists( $cache_key, self::$column_cache ) ) {
			return self::$column_cache[ $cache_key ];
		}

		global $wpdb;
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Schema metadata is memoized per request; persistent caching can become stale after migrations.
		$row = $wpdb->get_row(
			$wpdb->prepare(
				'SELECT
					COLUMN_NAME AS Field,
					COLUMN_TYPE AS Type,
					IS_NULLABLE AS `Null`,
					COLUMN_KEY AS `Key`,
					COLUMN_DEFAULT AS `Default`,
					EXTRA AS Extra,
					COLLATION_NAME AS Collation
				FROM information_schema.columns
				WHERE table_schema = DATABASE()
				AND table_name = %s
				AND column_name = %s
				LIMIT 1',
				$table_name,
				$column
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching

		self::$column_cache[ $cache_key ] = is_array( $row ) ? $row : null;
		if ( null === self::$column_cache[ $cache_key ] ) {
			self::$column_cache[ $cache_key ] = self::get_column_via_show( $table_name, $column );
		}

		return self::$column_cache[ $cache_key ];
	}

	public static function has_indexes( string $table_name, array $indexes ): bool {
		$table_name = self::table_name_for_read( $table_name );
		if ( null === $table_name ) {
			return false;
		}

		if ( empty( $indexes ) || ! self::table_exists( $table_name ) ) {
			return empty( $indexes );
		}

		global $wpdb;

		foreach ( $indexes as $index_name ) {
			$cache_key = $table_name . '|' . $index_name;
			if ( array_key_exists( $cache_key, self::$index_cache ) ) {
				if ( ! self::$index_cache[ $cache_key ] ) {
					return false;
				}

				continue;
			}

			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Schema metadata is memoized per request; persistent caching can become stale after migrations.
			$index = $wpdb->get_var(
				$wpdb->prepare(
					"SELECT INDEX_NAME
					FROM information_schema.statistics
					WHERE table_schema = DATABASE()
					AND table_name = %s
					AND index_name = %s
					LIMIT 1",
					$table_name,
					$index_name
				)
			);
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching

			self::$index_cache[ $cache_key ] = is_string( $index ) && $index === $index_name;
			if ( ! self::$index_cache[ $cache_key ] ) {
				self::$index_cache[ $cache_key ] = self::has_index_via_show( $table_name, (string) $index_name );
			}
			if ( ! self::$index_cache[ $cache_key ] ) {
				return false;
			}
		}

		return true;
	}

	public static function table_identifier( string $table_name ): string {
		$table_name = self::validate_table_name( $table_name );

		return '`' . str_replace( '`', '``', $table_name ) . '`';
	}

	/**
	 * Build the table-options clause (engine + charset/collation) for plugin DDL.
	 *
	 * Requests InnoDB when the host supports it (the scanner uses transactions),
	 * otherwise leaves the engine at the server default. Charset comes from
	 * get_charset_collate(); a leaked placeholder such as WORDPRESS_DB_CHARSET is
	 * dropped in favour of the best supported standard charset.
	 */
	public static function validated_charset_collate( \wpdb $wpdb ): string {
		$clause = (string) $wpdb->get_charset_collate();
		$cache_key = implode(
			'|',
			[
				(string) ( $wpdb->prefix ?? '' ),
				(string) ( $wpdb->charset ?? '' ),
				(string) ( $wpdb->collate ?? '' ),
				$clause,
			]
		);

		if ( array_key_exists( $cache_key, self::$ddl_charset_collate_cache ) ) {
			return self::$ddl_charset_collate_cache[ $cache_key ];
		}

		$raw_charset = self::extract_charset_from_clause( $clause );
		if ( $raw_charset === '' && isset( $wpdb->charset ) ) {
			$raw_charset = (string) $wpdb->charset;
		}

		$charset = self::normalize_standard_charset( $raw_charset );
		if ( $charset === '' ) {
			$charset = self::best_available_charset( $wpdb );
		}

		$engine = self::innodb_supported() ? 'ENGINE=InnoDB ' : '';

		if ( $charset === '' ) {
			self::$ddl_charset_collate_cache[ $cache_key ] = trim( $engine );
			return self::$ddl_charset_collate_cache[ $cache_key ];
		}

		$raw_collation = self::extract_collation_from_clause( $clause );
		if ( $raw_collation === '' && isset( $wpdb->collate ) ) {
			$raw_collation = (string) $wpdb->collate;
		}

		$collation = self::normalize_collation_for_charset( $raw_collation, $charset );
		if ( $collation === '' ) {
			$collation = self::best_available_collation( $wpdb, $charset );
		}
		if ( $collation === '' ) {
			self::$ddl_charset_collate_cache[ $cache_key ] = $engine . 'DEFAULT CHARACTER SET ' . $charset;
			return self::$ddl_charset_collate_cache[ $cache_key ];
		}

		self::$ddl_charset_collate_cache[ $cache_key ] = $engine . 'DEFAULT CHARACTER SET ' . $charset . ' COLLATE ' . $collation;
		return self::$ddl_charset_collate_cache[ $cache_key ];
	}

	public static function ensure_table_uses_innodb( string $table_name ): bool {
		$table_name = self::table_name_for_read( $table_name );
		if ( null === $table_name || ! self::table_exists( $table_name ) ) {
			return false;
		}

		// No InnoDB on this host: keep the server default engine.
		if ( ! self::innodb_supported() ) {
			return false;
		}

		global $wpdb;

		$engine = self::table_engine( $table_name );
		if ( 'innodb' === strtolower( $engine ) ) {
			return true;
		}

		$table_identifier = self::table_identifier( $table_name );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.SchemaChange,PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Converts a plugin-owned table to the required transactional engine.
		$result = $wpdb->query( "ALTER TABLE {$table_identifier} ENGINE=InnoDB" );
		self::flush_cache( $table_name );

		if ( false === $result ) {
			sitefort_log(
				'[SiteFort] Table engine migration failed for '
				. $table_name
				. ' from '
				. sanitize_text_field( $engine !== '' ? $engine : 'unknown' )
				. ( $wpdb->last_error !== '' ? ': ' . sanitize_text_field( $wpdb->last_error ) : '' )
			);
			return false;
		}

		$engine = self::table_engine( $table_name );
		if ( 'innodb' === strtolower( $engine ) ) {
			return true;
		}

		sitefort_log(
			'[SiteFort] Table engine migration did not produce InnoDB for '
			. $table_name
			. '; current engine: '
			. sanitize_text_field( $engine !== '' ? $engine : 'unknown' )
		);

		return false;
	}

	/**
	 * True when the table is InnoDB, or when the host cannot provide InnoDB at all.
	 *
	 * Keeps schema checks from looping on a conversion that can never succeed.
	 */
	public static function table_engine_ready( string $table_name ): bool {
		return ! self::innodb_supported() || self::table_uses_innodb( $table_name );
	}

	public static function table_uses_innodb( string $table_name ): bool {
		$table_name = self::table_name_for_read( $table_name );
		if ( null === $table_name || ! self::table_exists( $table_name ) ) {
			return false;
		}

		return 'innodb' === strtolower( self::table_engine( $table_name ) );
	}

	public static function column_uses_collation( string $table_name, string $column, string $collation ): bool {
		$column_info = self::get_column( $table_name, $column );
		if ( ! is_array( $column_info ) ) {
			return false;
		}

		return strtolower( (string) ( $column_info['Collation'] ?? '' ) ) === strtolower( $collation );
	}

	public static function validate_table_name( string $table_name ): string {
		if (
			$table_name === ''
			|| strlen( $table_name ) > self::MAX_IDENTIFIER_LENGTH
			|| ! preg_match( '/^[A-Za-z0-9_]+$/', $table_name )
		) {
			throw new \InvalidArgumentException( 'Invalid table identifier.' );
		}

		return $table_name;
	}

	private static function table_engine( string $table_name ): string {
		global $wpdb;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads plugin-owned table metadata during schema bootstrap.
		$engine = $wpdb->get_var(
			$wpdb->prepare(
				'SELECT ENGINE
				FROM information_schema.tables
				WHERE table_schema = DATABASE()
				AND table_name = %s
				LIMIT 1',
				$table_name
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching

		if ( is_string( $engine ) && '' !== $engine ) {
			return $engine;
		}

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- SHOW fallback for hosts with limited information_schema visibility.
		$row = $wpdb->get_row(
			$wpdb->prepare( 'SHOW TABLE STATUS WHERE Name = %s', $table_name ),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching

		if ( is_array( $row ) && isset( $row['Engine'] ) && is_string( $row['Engine'] ) ) {
			return $row['Engine'];
		}

		return '';
	}

	private static function table_name_for_read( string $table_name ): ?string {
		try {
			return self::validate_table_name( $table_name );
		} catch ( \InvalidArgumentException $e ) {
			return null;
		}
	}

	private static function table_exists_via_show( string $table_name ): bool {
		global $wpdb;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Fallback metadata lookup for managed hosts that restrict information_schema visibility.
		$found = $wpdb->get_var(
			$wpdb->prepare(
				'SHOW TABLES LIKE %s',
				$table_name
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching

		return is_string( $found ) && strtolower( $found ) === strtolower( $table_name );
	}

	/**
	 * @return array<string,mixed>|null
	 */
	private static function get_column_via_show( string $table_name, string $column ): ?array {
		global $wpdb;

		$table_identifier = self::table_identifier( $table_name );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Fallback metadata lookup against a validated plugin-owned identifier.
		$row = $wpdb->get_row(
			$wpdb->prepare(
				"SHOW FULL COLUMNS FROM {$table_identifier} LIKE %s",
				$column
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter

		return is_array( $row ) ? $row : null;
	}

	private static function has_index_via_show( string $table_name, string $index_name ): bool {
		global $wpdb;

		$table_identifier = self::table_identifier( $table_name );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Fallback metadata lookup against a validated plugin-owned identifier.
		$index = $wpdb->get_var(
			$wpdb->prepare(
				"SHOW INDEX FROM {$table_identifier} WHERE Key_name = %s",
				$index_name
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter

		return is_string( $index ) && $index === $index_name;
	}

	/**
	 * Normalize a COLUMN_DEFAULT value from information_schema for comparisons.
	 *
	 * MariaDB can expose string defaults wrapped in quotes (for example
	 * "'staged'"), while MySQL commonly returns the bare scalar. This helper
	 * lets schema checks compare the effective default instead of vendor-
	 * specific formatting.
	 */
	public static function normalize_default_value( $default ): string {
		if ( null === $default ) {
			return '';
		}

		$default = (string) $default;
		$length  = strlen( $default );

		if ( $length >= 2 ) {
			$first = $default[0];
			$last  = $default[ $length - 1 ];

			if ( ( $first === "'" && $last === "'" ) || ( $first === '"' && $last === '"' ) ) {
				$default = substr( $default, 1, -1 );
			}
		}

		return str_replace( [ "\\'", '\\"' ], [ "'", '"' ], $default );
	}

	private static function extract_charset_from_clause( string $clause ): string {
		if ( preg_match( '/\b(?:DEFAULT\s+(?:CHARACTER\s+SET|CHARSET)|CHARACTER\s+SET|CHARSET)\s*=?\s*([A-Za-z0-9_]+)/i', $clause, $matches ) === 1 ) {
			return (string) ( $matches[1] ?? '' );
		}

		return '';
	}

	private static function extract_collation_from_clause( string $clause ): string {
		if ( preg_match( '/\bCOLLATE\s*=?\s*([A-Za-z0-9_]+)/i', $clause, $matches ) === 1 ) {
			return (string) ( $matches[1] ?? '' );
		}

		return '';
	}

	private static function normalize_standard_charset( string $charset ): string {
		$charset = strtolower( trim( $charset ) );
		if ( $charset === '' || ! preg_match( '/^[a-z0-9_]+$/', $charset ) ) {
			return '';
		}

		return in_array( $charset, self::STANDARD_CHARSETS, true ) ? $charset : '';
	}

	private static function best_available_charset( \wpdb $wpdb ): string {
		$property_charset = isset( $wpdb->charset ) ? self::normalize_standard_charset( (string) $wpdb->charset ) : '';
		if ( $property_charset !== '' ) {
			return $property_charset;
		}

		if ( method_exists( $wpdb, 'has_cap' ) && $wpdb->has_cap( 'utf8mb4' ) ) {
			return 'utf8mb4';
		}

		return 'utf8';
	}

	private static function best_available_collation( \wpdb $wpdb, string $charset ): string {
		$candidates = [];

		if ( isset( $wpdb->prefix ) && is_string( $wpdb->prefix ) && $wpdb->prefix !== '' ) {
			$candidates = [
				$wpdb->prefix . 'options',
				$wpdb->prefix . 'posts',
				$wpdb->prefix . 'postmeta',
				$wpdb->prefix . 'users',
			];
		}

		foreach ( $candidates as $table_name ) {
			$collation = self::table_collation( $wpdb, $table_name );
			$collation = self::normalize_collation_for_charset( $collation, $charset );
			if ( $collation !== '' ) {
				return $collation;
			}
		}

		// As a final fallback, the schema default can still be useful when it
		// matches the chosen charset.
		$schema_collation = self::schema_default_collation( $wpdb );
		return self::normalize_collation_for_charset( $schema_collation, $charset );
	}

	private static function normalize_collation_for_charset( string $collation, string $charset ): string {
		$collation = strtolower( trim( $collation ) );
		if ( $collation === '' || ! preg_match( '/^[a-z0-9_]+$/', $collation ) ) {
			return '';
		}

		return str_starts_with( $collation, $charset . '_' ) ? $collation : '';
	}

	private static function table_collation( \wpdb $wpdb, string $table_name ): string {
		if ( self::table_name_for_read( $table_name ) === null ) {
			return '';
		}

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads table metadata once during schema bootstrap.
		$collation = $wpdb->get_var(
			$wpdb->prepare(
				'SELECT TABLE_COLLATION
				FROM information_schema.tables
				WHERE table_schema = DATABASE()
				AND table_name = %s
				LIMIT 1',
				$table_name
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching

		if ( is_string( $collation ) && $collation !== '' ) {
			return $collation;
		}

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- SHOW fallback for hosts with limited information_schema visibility.
		$row = $wpdb->get_row(
			$wpdb->prepare(
				'SHOW TABLE STATUS LIKE %s',
				$table_name
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching

		return is_array( $row ) && isset( $row['Collation'] ) ? (string) $row['Collation'] : '';
	}

	private static function schema_default_collation( \wpdb $wpdb ): string {
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads schema metadata once during schema bootstrap.
		$collation = $wpdb->get_var(
			'SELECT DEFAULT_COLLATION_NAME
			FROM information_schema.SCHEMATA
			WHERE SCHEMA_NAME = DATABASE()
			LIMIT 1'
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching

		return is_string( $collation ) ? $collation : '';
	}
}
