<?php

declare(strict_types=1);

namespace SecurWP\Core\Database;

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

final class SchemaInspector {

	private const MAX_IDENTIFIER_LENGTH = 64;

	/** @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 = [];

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

		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;

		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 ] ) {
				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 ) . '`';
	}

	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;
		}
	}

	/**
	 * 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 );
	}
}
