<?php
/**
 * Closed layouts for inactive-addon request-case cleanup and additive setup.
 *
 * @package LoggedAI
 */

namespace LoggedAI;

defined( 'ABSPATH' ) || exit;

/** Schema inspection only. No read method installs or changes storage. */
final class RequestCaseLayout {

	public const VERSION = 2;

	/**
	 * Declare the nullable additive request-case relation columns.
	 *
	 * Nullable additions leave every legacy row and its existing meaning intact.
	 *
	 * @return array Fixed new column names and their supported nullable SQL types.
	 */
	public static function additions(): array {
		return array(
			'relation_version' => '?tinyint unsigned',
			'probe_id'         => '?bigint unsigned',
			'probe_uid'        => '?char(36)',
			'proof_id'         => '?bigint unsigned',
			'proof_key'        => '?char(64)',
			'connector_uid'    => '?char(36)',
			'source_key'       => '?char(64)',
			'source_binding'   => '?char(64)',
			'request_binding'  => '?char(64)',
			'window_seconds'   => '?int unsigned',
			'late_seconds'     => '?int unsigned',
		);
	}

	/**
	 * Declare the additive probe and proof reference indexes.
	 *
	 * @return array Fixed index uniqueness and ordered column names.
	 */
	public static function indexes(): array {
		return array(
			'probe_ref' => array( false, array( 'network_id', 'site_id', 'probe_id', 'probe_uid', 'id' ) ),
			'proof_ref' => array( false, array( 'network_id', 'site_id', 'case_uid', 'proof_id', 'id' ) ),
		);
	}

	/**
	 * Inspect only a known compatible legacy or additive request-case schema layout.
	 *
	 * Return a known complete version, or a known incomplete additive layout for explicit setup.
	 * Unknown names, types, defaults, generated fields or prefix indexes are never repaired blindly.
	 *
	 * @param \wpdb $db Native database connection used for the current bounded read or mutation.
	 *
	 * @return array|null Null means incompatible or unavailable; missing lists are fixed local names.
	 */
	public static function inspect( \wpdb $db ): ?array {
		$table = ScopedRequestCasePrivacy::table( $db );
		if ( ! preg_match( '/\A[a-zA-Z0-9_]{1,64}\z/D', $table ) ) {
			return null;
		}
		$columns = self::rows( $db, $db->prepare( 'SHOW COLUMNS FROM %i', $table ), 31 );
		if ( null === $columns ) {
			return null;
		}
		$engine = self::rows( $db, $db->prepare( 'SELECT ENGINE AS engine FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=%s LIMIT 1', $table ), 1 );
		if ( array( array( 'engine' => 'InnoDB' ) ) !== $engine ) {
			return null;
		}
		$indexes = self::rows( $db, $db->prepare( 'SHOW INDEX FROM %i', $table ), 50 );
		return null !== $indexes ? self::reduce( $columns, $indexes ) : null;
	}

	/**
	 * Read bounded acknowledged native metadata without accepting stale cached query results.
	 *
	 * Exact acknowledged native results; a refused query cannot reuse a prior result cache.
	 *
	 * @param \wpdb  $db    Native database connection used for the current bounded read or mutation.
	 * @param string $sql   Fixed prepared SELECT or SHOW metadata statement.
	 * @param int    $limit Maximum acknowledged native metadata row count for this fixed read.
	 *
	 * @return array|null Exact native scalar metadata rows, or null for a refused, malformed or oversized read.
	 */
	public static function rows( \wpdb $db, string $sql, int $limit ): ?array {
		if ( $limit < 1 || $limit > 101 || ! preg_match( '/\A(?:SELECT|SHOW)\s/i', $sql ) ) {
			return null;
		}
		$selected = $db->query( $sql );
		if ( ! is_int( $selected ) || $selected < 0 || $selected > $limit || '' !== $db->last_error || $db->remove_placeholder_escape( $sql ) !== $db->last_query || $selected !== $db->num_rows || ! is_array( $db->last_result ) || ! array_is_list( $db->last_result ) || count( $db->last_result ) !== $selected ) {
			return null;
		}
		// Core get_results(null) returns null; copy only the acknowledged native row objects.
		$rows = array();
		foreach ( $db->last_result as $cached ) {
			if ( ! $cached instanceof \stdClass || 'stdClass' !== get_class( $cached ) ) { // @phpstan-ignore instanceof.alwaysTrue (wpdb::last_result is public and untyped; retain this cached-row object check without changing short-circuit reads.)
				return null;
			}
			$row = get_object_vars( $cached );
			if ( count( $row ) > 64 ) {
				return null;
			}
			foreach ( $row as $name => $value ) {
				if ( ! is_string( $name ) || ( null !== $value && ! is_string( $value ) && ! is_int( $value ) ) || ( is_string( $value ) && strlen( $value ) > 16384 ) ) {
					return null;
				}
			}
			$rows[] = $row;
		}
		return $rows;
	}

	/**
	 * Classify known request-case metadata and list only fixed supported additive omissions.
	 *
	 * Pure metadata reduction. No DDL and no source row is inspected here.
	 *
	 * @param array $columns Acknowledged bounded request-case column metadata.
	 * @param array $indexes Acknowledged bounded request-case index metadata.
	 *
	 * @return array|null Known layout version and missing additions, or null for incompatible metadata.
	 */
	public static function reduce( array $columns, array $indexes ): ?array {
		$legacy = ScopedRequestCasePrivacy::columns( 1 );
		$types  = $legacy + self::additions();
		if ( ! array_is_list( $columns ) || count( $columns ) < count( $legacy ) || count( $columns ) > count( $types ) || ! array_is_list( $indexes ) || count( $indexes ) > 50 ) {
			return null;
		}
		$found = array();
		foreach ( $columns as $column ) {
			if ( ! is_array( $column ) ) {
				return null;
			}
			$name = $column['Field'] ?? null;
			if ( ! is_string( $name ) || ! isset( $types[ $name ] ) || isset( $found[ $name ] ) || ! is_string( $column['Type'] ?? null ) || ! array_key_exists( 'Default', $column ) || null !== $column['Default'] ) {
				return null;
			}
			$type = $types[ $name ];
			if ( ltrim( $type, '?' ) !== preg_replace( '/\b(bigint|int|tinyint)\([0-9]+\)/', '$1', strtolower( $column['Type'] ) ) || ( $column['Null'] ?? null ) !== ( str_starts_with( $type, '?' ) ? 'YES' : 'NO' ) || ( $column['Extra'] ?? null ) !== ( 'id' === $name ? 'auto_increment' : '' ) ) {
				return null;
			}
			$found[ $name ] = true;
		}
		if ( array_diff_key( $legacy, $found ) ) {
			return null;
		}
		$legacy_indexes = ScopedRequestCasePrivacy::indexes( 1 );
		$expected       = $legacy_indexes + self::indexes();
		$seen           = array();
		foreach ( $indexes as $index ) {
			if ( ! is_array( $index ) ) {
				return null;
			}
			$name = $index['Key_name'] ?? null;
			$seq  = $index['Seq_in_index'] ?? null;
			if ( ! is_string( $name ) || ! isset( $expected[ $name ] ) || ! in_array( $seq, array( 1, 2, 3, 4, 5, '1', '2', '3', '4', '5' ), true ) || isset( $seen[ $name ][ (int) $seq ] ) || ! array_key_exists( 'Sub_part', $index ) || null !== $index['Sub_part'] || (string) ( $index['Non_unique'] ?? '' ) !== ( $expected[ $name ][0] ? '0' : '1' ) || 'BTREE' !== ( $index['Index_type'] ?? null ) || ! is_string( $index['Column_name'] ?? null ) || ! isset( $found[ $index['Column_name'] ] ) ) {
				return null;
			}
			$seen[ $name ][ (int) $seq ] = $index['Column_name'];
		}
		foreach ( $seen as $name => $fields ) {
			ksort( $fields );
			if ( array_keys( $fields ) !== range( 1, count( $expected[ $name ][1] ) ) || array_values( $fields ) !== $expected[ $name ][1] ) {
				return null;
			}
		}
		if ( array_diff_key( $legacy_indexes, $seen ) ) {
			return null;
		}
		$missing_columns = array_keys( array_diff_key( self::additions(), $found ) );
		$missing_indexes = array_keys( array_diff_key( self::indexes(), $seen ) );
		$version         = ! $missing_columns && ! $missing_indexes ? 2 : ( count( $found ) === count( $legacy ) && count( $seen ) === count( $legacy_indexes ) ? 1 : 0 );
		return array(
			'version'         => $version,
			'missing_columns' => $missing_columns,
			'missing_indexes' => $missing_indexes,
		);
	}
}