<?php
/**
 * Database clean-up: table sizes.
 *
 * @package AdminPowerups
 */

namespace SmartWP\AdminPowerups\Modules\DatabaseCleanup;

defined( 'ABSPATH' ) || exit;

/**
 * Which tables exist (cheap, every request) and their sizes (from `information_schema.TABLES`,
 * only when a scan starts). The sizes are the server's estimates, so they are always shown as
 * "about"; `LENGTH()` is never run on value columns.
 *
 * @since 1.0.0
 */
final class Sizes {

	/**
	 * Tables read this request: name => sizes.
	 *
	 * @var array<string, array{rows: int, avg: int, data: int, index: int, free: int}>|null
	 */
	private static $tables = null;

	/**
	 * Lowercased names of this site's tables that start with its prefix (null: not read yet).
	 *
	 * @var array<string, bool>|null
	 */
	private static $prefixed = null;

	/**
	 * Other tables asked about this request: lowercased name => exists.
	 *
	 * @var array<string, bool>
	 */
	private static $other = array();

	/**
	 * Sizes of the whitelisted tables that exist, keyed by their whitelisted name (read at scan
	 * time only: `information_schema` can be slow on servers with many tables).
	 *
	 * @since 1.0.0
	 * @return array<string, array{rows: int, avg: int, data: int, index: int, free: int}>
	 */
	public static function read(): array {
		global $wpdb;
		if ( null !== self::$tables ) {
			return self::$tables;
		}
		self::$tables = array();
		$names        = Checks::allowed_tables();
		$canonical    = array();
		foreach ( $names as $name ) {
			$canonical[ strtolower( $name ) ] = $name;
		}
		$in    = implode( ',', array_fill( 0, count( $names ), '%s' ) );
		$quiet = $wpdb->suppress_errors( true );
		// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- one schema read per scan; $in is one %s per name.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT TABLE_NAME AS name, TABLE_ROWS AS row_count, AVG_ROW_LENGTH AS avg_len, DATA_LENGTH AS data_len, INDEX_LENGTH AS index_len, DATA_FREE AS data_free FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME IN ({$in})",
				$names
			)
		);
		// phpcs:enable
		$wpdb->suppress_errors( $quiet );
		foreach ( (array) $rows as $row ) {
			if ( ! is_object( $row ) || ! isset( $row->name ) || ! isset( $canonical[ strtolower( (string) $row->name ) ] ) ) {
				continue;
			}
			self::$tables[ $canonical[ strtolower( (string) $row->name ) ] ] = array(
				'rows'  => (int) $row->row_count,
				'avg'   => (int) $row->avg_len,
				'data'  => (int) $row->data_len,
				'index' => (int) $row->index_len,
				'free'  => (int) $row->data_free,
			);
		}
		return self::$tables;
	}

	/**
	 * Whether a table exists. WordPress's own tables always do; tables with this site's prefix
	 * come from one `SHOW TABLES LIKE 'prefix%'` per request (three family lists on a multisite main
	 * site, whose prefix also matches subsite tables); others are looked up one by one.
	 * Names are compared case-insensitively.
	 *
	 * @since 1.0.0
	 * @param string $table Table name.
	 * @return bool
	 */
	public static function exists( string $table ): bool {
		global $wpdb;
		$key = strtolower( $table );
		if ( in_array( $key, array_map( 'strtolower', Checks::core_tables() ), true ) ) {
			return true;
		}
		$prefix = strtolower( $wpdb->prefix );
		if ( '' !== $prefix && str_starts_with( $key, $prefix ) ) {
			if ( null === self::$prefixed ) {
				self::$prefixed = array();
				// On a multisite main site the prefix also matches every subsite's tables: list only
				// the families the whitelist uses there (three short lists instead of the network).
				$patterns = is_multisite() && is_main_site()
					? array( 'wc_', 'woocommerce_', 'actionscheduler_' )
					: array( '' );
				$quiet    = $wpdb->suppress_errors( true );
				foreach ( $patterns as $family ) {
					// phpcs:ignore WordPress.DB.DirectDatabaseQuery -- schema list, once per request.
					foreach ( (array) $wpdb->get_col( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $wpdb->prefix . $family ) . '%' ) ) as $name ) {
						self::$prefixed[ strtolower( (string) $name ) ] = true;
					}
				}
				$wpdb->suppress_errors( $quiet );
			}
			return isset( self::$prefixed[ $key ] );
		}
		if ( ! isset( self::$other[ $key ] ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery -- schema check, memoized per request.
			self::$other[ $key ] = strtolower( (string) $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table ) ) ) ) === $key;
		}
		return self::$other[ $key ];
	}

	/**
	 * Estimated bytes of a number of rows of a table (rows × average row length), from the sizes
	 * read this request or else those stored with the last scan.
	 *
	 * @since 1.0.0
	 * @param string $table Table name.
	 * @param int    $rows  Rows.
	 * @return int
	 */
	public static function bytes( string $table, int $rows ): int {
		$sizes = null !== self::$tables ? self::$tables : Results::get()['sizes'];
		$size  = $sizes[ $table ] ?? null;
		return is_array( $size ) && $rows > 0 ? $rows * max( 0, (int) ( $size['avg'] ?? 0 ) ) : 0;
	}

	/**
	 * Forget what was read (tests, after a scan).
	 *
	 * @since 1.0.0
	 * @return void
	 */
	public static function flush(): void {
		self::$tables   = null;
		self::$prefixed = null;
		self::$other    = array();
	}
}