<?php
/**
 * Optimize tables cleanup task.
 *
 * @package DatabaseCleanup
 */

namespace DatabaseCleanup\Includes\Tasks;

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

class Task_Optimize_Tables extends Abstract_Cleanup_Task {

	private const ROW_THRESHOLD = 100000;

	public function get_id(): string {
		return 'optimize_tables';
	}

	public function get_label(): string {
		return __( 'Optimize tables', 'wp-powersuite' );
	}

	public function get_description(): string {
		return __( 'Run OPTIMIZE TABLE on WordPress core tables after cleanup. Skips very large tables to reduce timeout risk.', 'wp-powersuite' );
	}

	public function is_safe(): bool {
		return false;
	}

	public function scan( array $settings ): array {
		unset( $settings );

		$tables = $this->eligible_tables();

		return [
			'count'           => count( $tables ),
			'estimated_bytes' => 0,
			'details'         => [
				'tables' => array_column( $tables, 'name' ),
				'skipped_large' => $this->skipped_large_tables(),
			],
		];
	}

	public function clean( array $settings, int $batch_size, mixed $cursor = null ): array {
		global $wpdb;

		unset( $settings, $batch_size );

		$tables  = $this->eligible_tables();
		$index   = is_array( $cursor ) ? absint( $cursor['index'] ?? 0 ) : 0;
		$optimized = 0;

		if ( ! isset( $tables[ $index ] ) ) {
			return [
				'deleted' => 0,
				'done'    => true,
				'cursor'  => null,
				'bytes'   => 0,
			];
		}

		$table = (string) $tables[ $index ]['name'];

		if ( $this->is_core_table( $table ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$wpdb->query( "OPTIMIZE TABLE `{$table}`" );
			++$optimized;
		}

		$next = $index + 1;
		$done = ! isset( $tables[ $next ] );

		return [
			'deleted' => $optimized,
			'done'    => $done,
			'cursor'  => $done ? null : [ 'index' => $next ],
			'bytes'   => 0,
		];
	}

	/**
	 * @return list<array{name:string, rows:int}>
	 */
	private function eligible_tables(): array {
		global $wpdb;

		$core   = array_values( array_unique( array_merge( $wpdb->tables( 'all', true ), [ $wpdb->usermeta, $wpdb->blogs, $wpdb->blogmeta, $wpdb->registration_log, $wpdb->signups, $wpdb->site, $wpdb->sitemeta ] ) ) );
		$result = [];

		foreach ( $core as $table ) {
			if ( ! is_string( $table ) || $table === '' ) {
				continue;
			}

			if ( str_starts_with( $table, $wpdb->prefix . 'wpps_' ) ) {
				continue;
			}

			$rows = $this->estimated_table_rows( $table );

			if ( $rows > self::ROW_THRESHOLD ) {
				continue;
			}

			$result[] = [
				'name' => $table,
				'rows' => $rows,
			];
		}

		return $result;
	}

	/**
	 * @return list<string>
	 */
	private function skipped_large_tables(): array {
		global $wpdb;

		$skipped = [];

		foreach ( $this->core_table_names() as $table ) {
			if ( str_starts_with( $table, $wpdb->prefix . 'wpps_' ) ) {
				continue;
			}

			$rows = $this->estimated_table_rows( $table );

			if ( $rows > self::ROW_THRESHOLD ) {
				$skipped[] = $table;
			}
		}

		return $skipped;
	}

	/**
	 * @return list<string>
	 */
	private function core_table_names(): array {
		global $wpdb;

		return array_values(
			array_unique(
				array_filter(
					array_merge( $wpdb->tables( 'all', true ), [ $wpdb->usermeta ] ),
					static fn( $table ): bool => is_string( $table ) && $table !== ''
				)
			)
		);
	}

	private function is_core_table( string $table ): bool {
		global $wpdb;

		if ( str_starts_with( $table, $wpdb->prefix . 'wpps_' ) ) {
			return false;
		}

		return in_array( $table, $this->core_table_names(), true );
	}

	private function estimated_table_rows( string $table ): int {
		global $wpdb;

		$db_name = defined( 'DB_NAME' ) ? DB_NAME : '';

		if ( $db_name === '' ) {
			return 0;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$rows = $wpdb->get_var(
			$wpdb->prepare(
				'SELECT TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s',
				$db_name,
				$table
			)
		);

		return max( 0, (int) $rows );
	}
}
