<?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 = [];

	/**
	 * 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
				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 a safe charset/collation clause for plugin-managed DDL.
	 *
	 * Healthy WordPress installs should flow through the native
	 * `get_charset_collate()` value unchanged. On misconfigured hosts where a
	 * placeholder such as `WORDPRESS_DB_CHARSET` leaks into SQL, this method
	 * drops the invalid token and falls back to the best supported standard
	 * charset for the current environment.
	 */
	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 );
		}

		if ( $charset === '' ) {
			self::$ddl_charset_collate_cache[ $cache_key ] = '';
			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 ] = 'DEFAULT CHARACTER SET ' . $charset;
			return self::$ddl_charset_collate_cache[ $cache_key ];
		}

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

	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_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 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 : '';
	}
}
