<?php
/**
 * Database analyzer.
 *
 * Analyzes database bloat including revisions, drafts, trash, and transients.
 *
 * @package MonitorCat\Modules\Speed\Analyzer
 */

declare(strict_types=1);

namespace MonitorCat\Modules\Speed\Analyzer;

defined( 'ABSPATH' ) || exit;

/**
 * Analyzes WordPress database for bloat and optimization opportunities.
 */
final class DatabaseAnalyzer {

	/**
	 * Run the database analysis.
	 *
	 * @return array{score: int, status: string, label: string, checks: array<int, array{label: string, status: string, message: string, value: string|null}>}
	 */
	public function analyze(): array {
		$checks = array();

		$checks[] = $this->check_revisions();
		$checks[] = $this->check_auto_drafts();
		$checks[] = $this->check_trashed_posts();
		$checks[] = $this->check_spam_comments();
		$checks[] = $this->check_trashed_comments();
		$checks[] = $this->check_expired_transients();
		$checks[] = $this->check_autoloaded_options();

		$score  = $this->calculate_score( $checks );
		$status = $this->determine_status( $score );

		return array(
			'score'  => $score,
			'status' => $status,
			'label'  => __( 'Database', 'monitor-cat' ),
			'checks' => $checks,
		);
	}

	/**
	 * Get database stats without running a full analysis.
	 *
	 * @return array<string, int>
	 */
	public function get_stats(): array {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$revisions = (int) $wpdb->get_var(
			"SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_type = 'revision'"
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$auto_drafts = (int) $wpdb->get_var(
			"SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_status = 'auto-draft'"
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$trashed_posts = (int) $wpdb->get_var(
			"SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_status = 'trash'"
		);

		$comment_counts   = wp_count_comments();
		$spam_comments    = (int) $comment_counts->spam;
		$trashed_comments = (int) $comment_counts->trash;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$expired_transients = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$wpdb->options} WHERE option_name LIKE %s AND option_value < %d",
				$wpdb->esc_like( '_transient_timeout_' ) . '%',
				time()
			)
		);

		return array(
			'revisions'          => $revisions,
			'auto_drafts'        => $auto_drafts,
			'trashed_posts'      => $trashed_posts,
			'spam_comments'      => $spam_comments,
			'trashed_comments'   => $trashed_comments,
			'expired_transients' => $expired_transients,
		);
	}

	/**
	 * Check post revisions count.
	 *
	 * @return array{label: string, status: string, message: string, value: string|null}
	 */
	private function check_revisions(): array {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$count = (int) $wpdb->get_var(
			"SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_type = 'revision'"
		);

		if ( $count <= 50 ) {
			return array(
				'label'   => __( 'Post Revisions', 'monitor-cat' ),
				'status'  => 'good',
				/* translators: %d: number of revisions */
				'message' => sprintf( __( '%d post revision(s) found. Database is clean.', 'monitor-cat' ), $count ),
				'value'   => (string) $count,
			);
		}

		if ( $count <= 500 ) {
			return array(
				'label'   => __( 'Post Revisions', 'monitor-cat' ),
				'status'  => 'warning',
				/* translators: %d: number of revisions */
				'message' => sprintf( __( '%d post revisions found. Consider cleaning up old revisions.', 'monitor-cat' ), $count ),
				'value'   => (string) $count,
			);
		}

		return array(
			'label'   => __( 'Post Revisions', 'monitor-cat' ),
			'status'  => 'critical',
			/* translators: %d: number of revisions */
			'message' => sprintf( __( '%d post revisions found. This is causing significant database bloat.', 'monitor-cat' ), $count ),
			'value'   => (string) $count,
		);
	}

	/**
	 * Check auto-draft count.
	 *
	 * @return array{label: string, status: string, message: string, value: string|null}
	 */
	private function check_auto_drafts(): array {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$count = (int) $wpdb->get_var(
			"SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_status = 'auto-draft'"
		);

		if ( $count <= 5 ) {
			return array(
				'label'   => __( 'Auto-Drafts', 'monitor-cat' ),
				'status'  => 'good',
				/* translators: %d: number of auto-drafts */
				'message' => sprintf( __( '%d auto-draft(s) found.', 'monitor-cat' ), $count ),
				'value'   => (string) $count,
			);
		}

		if ( $count <= 50 ) {
			return array(
				'label'   => __( 'Auto-Drafts', 'monitor-cat' ),
				'status'  => 'warning',
				/* translators: %d: number of auto-drafts */
				'message' => sprintf( __( '%d auto-drafts found. These can be safely removed.', 'monitor-cat' ), $count ),
				'value'   => (string) $count,
			);
		}

		return array(
			'label'   => __( 'Auto-Drafts', 'monitor-cat' ),
			'status'  => 'critical',
			/* translators: %d: number of auto-drafts */
			'message' => sprintf( __( '%d auto-drafts found. Clean these up to reduce database size.', 'monitor-cat' ), $count ),
			'value'   => (string) $count,
		);
	}

	/**
	 * Check trashed posts count.
	 *
	 * @return array{label: string, status: string, message: string, value: string|null}
	 */
	private function check_trashed_posts(): array {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$count = (int) $wpdb->get_var(
			"SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_status = 'trash'"
		);

		if ( $count <= 10 ) {
			return array(
				'label'   => __( 'Trashed Posts', 'monitor-cat' ),
				'status'  => 'good',
				/* translators: %d: number of trashed posts */
				'message' => sprintf( __( '%d trashed post(s) found.', 'monitor-cat' ), $count ),
				'value'   => (string) $count,
			);
		}

		if ( $count <= 100 ) {
			return array(
				'label'   => __( 'Trashed Posts', 'monitor-cat' ),
				'status'  => 'warning',
				/* translators: %d: number of trashed posts */
				'message' => sprintf( __( '%d trashed posts found. Empty the trash to free up space.', 'monitor-cat' ), $count ),
				'value'   => (string) $count,
			);
		}

		return array(
			'label'   => __( 'Trashed Posts', 'monitor-cat' ),
			'status'  => 'critical',
			/* translators: %d: number of trashed posts */
			'message' => sprintf( __( '%d trashed posts found. This is adding unnecessary bloat.', 'monitor-cat' ), $count ),
			'value'   => (string) $count,
		);
	}

	/**
	 * Check spam comments count.
	 *
	 * @return array{label: string, status: string, message: string, value: string|null}
	 */
	private function check_spam_comments(): array {
		$comment_counts = wp_count_comments();
		$count          = (int) $comment_counts->spam;

		if ( $count <= 10 ) {
			return array(
				'label'   => __( 'Spam Comments', 'monitor-cat' ),
				'status'  => 'good',
				/* translators: %d: number of spam comments */
				'message' => sprintf( __( '%d spam comment(s) found.', 'monitor-cat' ), $count ),
				'value'   => (string) $count,
			);
		}

		if ( $count <= 100 ) {
			return array(
				'label'   => __( 'Spam Comments', 'monitor-cat' ),
				'status'  => 'warning',
				/* translators: %d: number of spam comments */
				'message' => sprintf( __( '%d spam comments found. Delete them to clean the database.', 'monitor-cat' ), $count ),
				'value'   => (string) $count,
			);
		}

		return array(
			'label'   => __( 'Spam Comments', 'monitor-cat' ),
			'status'  => 'critical',
			/* translators: %d: number of spam comments */
			'message' => sprintf( __( '%d spam comments found. This is significantly bloating your database.', 'monitor-cat' ), $count ),
			'value'   => (string) $count,
		);
	}

	/**
	 * Check trashed comments count.
	 *
	 * @return array{label: string, status: string, message: string, value: string|null}
	 */
	private function check_trashed_comments(): array {
		$comment_counts = wp_count_comments();
		$count          = (int) $comment_counts->trash;

		if ( $count <= 10 ) {
			return array(
				'label'   => __( 'Trashed Comments', 'monitor-cat' ),
				'status'  => 'good',
				/* translators: %d: number of trashed comments */
				'message' => sprintf( __( '%d trashed comment(s) found.', 'monitor-cat' ), $count ),
				'value'   => (string) $count,
			);
		}

		if ( $count <= 100 ) {
			return array(
				'label'   => __( 'Trashed Comments', 'monitor-cat' ),
				'status'  => 'warning',
				/* translators: %d: number of trashed comments */
				'message' => sprintf( __( '%d trashed comments found. Empty the comment trash to free space.', 'monitor-cat' ), $count ),
				'value'   => (string) $count,
			);
		}

		return array(
			'label'   => __( 'Trashed Comments', 'monitor-cat' ),
			'status'  => 'critical',
			/* translators: %d: number of trashed comments */
			'message' => sprintf( __( '%d trashed comments found. This is adding unnecessary bloat.', 'monitor-cat' ), $count ),
			'value'   => (string) $count,
		);
	}

	/**
	 * Check expired transients count.
	 *
	 * @return array{label: string, status: string, message: string, value: string|null}
	 */
	private function check_expired_transients(): array {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$count = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$wpdb->options} WHERE option_name LIKE %s AND option_value < %d",
				$wpdb->esc_like( '_transient_timeout_' ) . '%',
				time()
			)
		);

		if ( $count <= 20 ) {
			return array(
				'label'   => __( 'Expired Transients', 'monitor-cat' ),
				'status'  => 'good',
				/* translators: %d: number of expired transients */
				'message' => sprintf( __( '%d expired transient(s) found.', 'monitor-cat' ), $count ),
				'value'   => (string) $count,
			);
		}

		if ( $count <= 200 ) {
			return array(
				'label'   => __( 'Expired Transients', 'monitor-cat' ),
				'status'  => 'warning',
				/* translators: %d: number of expired transients */
				'message' => sprintf( __( '%d expired transients found. Clean them up for better performance.', 'monitor-cat' ), $count ),
				'value'   => (string) $count,
			);
		}

		return array(
			'label'   => __( 'Expired Transients', 'monitor-cat' ),
			'status'  => 'critical',
			/* translators: %d: number of expired transients */
			'message' => sprintf( __( '%d expired transients found. This is bloating the options table.', 'monitor-cat' ), $count ),
			'value'   => (string) $count,
		);
	}

	/**
	 * Check autoloaded options size.
	 *
	 * @return array{label: string, status: string, message: string, value: string|null}
	 */
	private function check_autoloaded_options(): array {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$size = (int) $wpdb->get_var(
			"SELECT SUM(LENGTH(option_value)) FROM {$wpdb->options} WHERE autoload = 'yes'"
		);

		$formatted = size_format( $size );

		// Less than 800 KB is good.
		if ( $size < 819200 ) {
			return array(
				'label'   => __( 'Autoloaded Options', 'monitor-cat' ),
				'status'  => 'good',
				/* translators: %s: human-readable size */
				'message' => sprintf( __( 'Autoloaded data size is %s. Within healthy limits.', 'monitor-cat' ), $formatted ),
				'value'   => $formatted,
			);
		}

		// Less than 2 MB is warning.
		if ( $size < 2097152 ) {
			return array(
				'label'   => __( 'Autoloaded Options', 'monitor-cat' ),
				'status'  => 'warning',
				/* translators: %s: human-readable size */
				'message' => sprintf( __( 'Autoloaded data size is %s. This is larger than recommended and may slow queries.', 'monitor-cat' ), $formatted ),
				'value'   => $formatted,
			);
		}

		return array(
			'label'   => __( 'Autoloaded Options', 'monitor-cat' ),
			'status'  => 'critical',
			/* translators: %s: human-readable size */
			'message' => sprintf( __( 'Autoloaded data size is %s. This is very large and will slow every page load.', 'monitor-cat' ), $formatted ),
			'value'   => $formatted,
		);
	}

	/**
	 * Calculate the overall score from checks.
	 *
	 * @param array<int, array{label: string, status: string, message: string, value: string|null}> $checks The checks array.
	 * @return int Score from 0 to 100.
	 */
	private function calculate_score( array $checks ): int {
		if ( empty( $checks ) ) {
			return 0;
		}

		$total_weight = 0;
		$earned       = 0;
		$weights      = array( 20, 10, 10, 15, 10, 15, 20 );

		foreach ( $checks as $index => $check ) {
			$weight        = $weights[ $index ] ?? 15;
			$total_weight += $weight;

			if ( 'good' === $check['status'] ) {
				$earned += $weight;
			} elseif ( 'warning' === $check['status'] ) {
				$earned += (int) round( $weight * 0.5 );
			}
		}

		return $total_weight > 0 ? (int) round( ( $earned / $total_weight ) * 100 ) : 0;
	}

	/**
	 * Determine overall status from score.
	 *
	 * @param int $score The calculated score.
	 * @return string 'good', 'warning', or 'critical'.
	 */
	private function determine_status( int $score ): string {
		if ( $score >= 80 ) {
			return 'good';
		}

		if ( $score >= 50 ) {
			return 'warning';
		}

		return 'critical';
	}
}
