<?php
/**
 * Database optimization engine.
 *
 * @package AceloraSiteAccelerator
 */

namespace Acelora\SiteAccelerator\Modules;

use Acelora\SiteAccelerator\Includes\Logger;
use Acelora\SiteAccelerator\Includes\Settings;

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

/**
 * Cleans revisions, drafts, trash, spam, transients, and orphaned metadata.
 */
class Database_Optimizer {

	/**
	 * Settings.
	 *
	 * @var Settings
	 */
	private $settings;

	/**
	 * Logger.
	 *
	 * @var Logger
	 */
	private $logger;

	/**
	 * Constructor.
	 *
	 * @param Settings $settings Settings.
	 * @param Logger   $logger   Logger.
	 */
	public function __construct( Settings $settings, Logger $logger ) {
		$this->settings = $settings;
		$this->logger   = $logger;
	}

	/**
	 * Register hooks.
	 *
	 * @return void
	 */
	public function register() {}

	/**
	 * Run database cleanup and optimization.
	 *
	 * @return array
	 */
	public function cleanup() {
		global $wpdb;

		$result = array(
			'revisions'          => $this->delete_posts_by_type( 'revision' ),
			'auto_drafts'        => $this->delete_posts_by_status( 'auto-draft' ),
			'trash_posts'        => $this->delete_posts_by_status( 'trash' ),
			'spam_comments'      => $this->delete_comments_by_status( 'spam' ),
			'trash_comments'     => $this->delete_comments_by_status( 'trash' ),
			'expired_transients' => $this->delete_expired_transients(),
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Database cleanup tools must directly remove orphaned metadata.
			'orphan_postmeta'    => absint( $wpdb->query( "DELETE pm FROM {$wpdb->postmeta} pm LEFT JOIN {$wpdb->posts} p ON p.ID = pm.post_id WHERE p.ID IS NULL" ) ),
			'orphan_commentmeta' => absint( $wpdb->query( "DELETE cm FROM {$wpdb->commentmeta} cm LEFT JOIN {$wpdb->comments} c ON c.comment_ID = cm.comment_id WHERE c.comment_ID IS NULL" ) ),
			'orphan_termmeta'    => absint( $wpdb->query( "DELETE tm FROM {$wpdb->termmeta} tm LEFT JOIN {$wpdb->terms} t ON t.term_id = tm.term_id WHERE t.term_id IS NULL" ) ),
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			'tables_optimized'   => $this->optimize_tables(),
		);

		$this->logger->log( 'info', 'Database cleanup completed.', $result );

		return $result;
	}

	/**
	 * Repair database tables.
	 *
	 * @return int
	 */
	public function repair_tables() {
		global $wpdb;

		$count = 0;
		$like  = $wpdb->esc_like( $wpdb->prefix ) . '%';
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Table discovery is required for an administrator-triggered repair tool.
		$tables = $wpdb->get_col( $wpdb->prepare( 'SHOW TABLES LIKE %s', $like ) );

		foreach ( (array) $tables as $table ) {
			$table_name = $this->prepare_table_identifier( $table );

			if ( '' === $table_name ) {
				continue;
			}

			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Explicit table maintenance action requested by an administrator after validating the table name against the site prefix.
			$wpdb->query( 'REPAIR TABLE ' . $table_name );
			$count++;
		}

		$this->logger->log( 'info', 'Database repair completed.', array( 'tables' => $count ) );

		return $count;
	}

	/**
	 * Delete posts by type.
	 *
	 * @param string $type Post type.
	 * @return int
	 */
	private function delete_posts_by_type( $type ) {
		$ids = get_posts(
			array(
				'post_type'      => $type,
				'post_status'    => 'any',
				'posts_per_page' => 500,
				'fields'         => 'ids',
				'no_found_rows'  => true,
			)
		);

		foreach ( $ids as $id ) {
			wp_delete_post_revision( $id );
		}

		return count( $ids );
	}

	/**
	 * Delete posts by status.
	 *
	 * @param string $status Post status.
	 * @return int
	 */
	private function delete_posts_by_status( $status ) {
		$ids = get_posts(
			array(
				'post_type'      => 'any',
				'post_status'    => $status,
				'posts_per_page' => 500,
				'fields'         => 'ids',
				'no_found_rows'  => true,
			)
		);

		foreach ( $ids as $id ) {
			wp_delete_post( $id, true );
		}

		return count( $ids );
	}

	/**
	 * Delete comments by status.
	 *
	 * @param string $status Comment status.
	 * @return int
	 */
	private function delete_comments_by_status( $status ) {
		$comments = get_comments(
			array(
				'status' => $status,
				'number' => 500,
				'fields' => 'ids',
			)
		);

		foreach ( $comments as $comment_id ) {
			wp_delete_comment( $comment_id, true );
		}

		return count( $comments );
	}

	/**
	 * Delete expired transients.
	 *
	 * @return int
	 */
	private function delete_expired_transients() {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Direct transient timeout lookup is required for batch cleanup.
		$timeouts = $wpdb->get_col(
			$wpdb->prepare(
				"SELECT option_name FROM {$wpdb->options} WHERE option_name LIKE %s AND option_value < %d LIMIT 1000",
				$wpdb->esc_like( '_transient_timeout_' ) . '%',
				time()
			)
		);

		foreach ( (array) $timeouts as $timeout ) {
			$key = str_replace( '_transient_timeout_', '', $timeout );
			delete_transient( $key );
		}

		return count( (array) $timeouts );
	}

	/**
	 * Optimize WordPress tables.
	 *
	 * @return int
	 */
	private function optimize_tables() {
		global $wpdb;

		$count  = 0;
		$like   = $wpdb->esc_like( $wpdb->prefix ) . '%';
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Table discovery is required for an administrator-triggered optimization tool.
		$tables = $wpdb->get_col( $wpdb->prepare( 'SHOW TABLES LIKE %s', $like ) );

		foreach ( (array) $tables as $table ) {
			$table_name = $this->prepare_table_identifier( $table );

			if ( '' === $table_name ) {
				continue;
			}

			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Explicit table maintenance action requested by an administrator after validating the table name against the site prefix.
			$wpdb->query( 'OPTIMIZE TABLE ' . $table_name );
			$count++;
		}

		return $count;
	}

	/**
	 * Validate and escape a WordPress table identifier.
	 *
	 * @param string $table Table name.
	 * @return string
	 */
	private function prepare_table_identifier( $table ) {
		global $wpdb;

		$table = (string) $table;

		if ( 0 !== strpos( $table, $wpdb->prefix ) || ! preg_match( '/^[A-Za-z0-9_]+$/', $table ) ) {
			return '';
		}

		return '`' . str_replace( '`', '``', $table ) . '`';
	}
}