<?php
/**
 * Database Overhead check.
 *
 * @package advanced-analytics
 *
 * @since 4.9.0
 */

declare(strict_types=1);

namespace ADVAN\Checks\Speed;

use ADVAN\Checks\Abstract_Check;

// Exit if accessed directly.
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

if ( ! class_exists( 'ADVAN\Checks\Speed\Database_Overhead_Check' ) ) {

	/**
	 * Checks for unnecessary data bloating your database.
	 *
	 * @since 4.9.0
	 */
	class Database_Overhead_Check extends Abstract_Check {

		/**
		 * Check identifier.
		 *
		 * @var string Check identifier.
		 *
		 * @since 4.9.0
		 */
		protected static string $id = 'database-overhead';

		/**
		 * Check category.
		 *
		 * @var string Check category.
		 *
		 * @since 4.9.0
		 */
		protected static string $category = 'speed';

		/**
		 * Check weight (1-10).
		 *
		 * @var int Check weight.
		 *
		 * @since 4.9.0
		 */
		protected static int $weight = 6;

		/**
		 * Check title (translatable).
		 *
		 * @return string Check title.
		 *
		 * @since 4.9.0
		 */
		public static function get_title(): string {
			return \__( 'Data Overhead', '0-day-analytics' );
		}

		/**
		 * Check description (translatable).
		 *
		 * @return string Check description.
		 *
		 * @since 4.9.0
		 */
		public static function get_description(): string {
			return \__( 'Checks for unnecessary data bloating your database.', '0-day-analytics' );
		}

		/**
		 * Run the check.
		 *
		 * @return array Check result.
		 *
		 * @since 4.9.0
		 */
		public static function run(): array {
			global $wpdb;

			// Count spam comments.
			$spam_count = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->comments} WHERE comment_approved = 'spam'" );

			// Count trashed comments.
			$trash_comments = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->comments} WHERE comment_approved = 'trash'" );

			// Count trashed posts.
			$trash_posts = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_status = 'trash'" );

			// Count revisions.
			$revisions = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_type = 'revision'" );

			// Count auto-drafts.
			$auto_drafts = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_status = 'auto-draft'" );

			// Count expired transients.
			$expired_transients = (int) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT COUNT(*) FROM {$wpdb->options}
					WHERE option_name LIKE %s
					AND option_value < UNIX_TIMESTAMP()",
					$wpdb->esc_like( '_transient_timeout_' ) . '%'
				)
			);

			$total_junk = $spam_count + $trash_comments + $trash_posts + $auto_drafts + $expired_transients;

			$details = array(
				'spam_comments'      => $spam_count,
				'trash_comments'     => $trash_comments,
				'trash_posts'        => $trash_posts,
				'revisions'          => $revisions,
				'auto_drafts'        => $auto_drafts,
				'expired_transients' => $expired_transients,
				'total_junk'         => $total_junk,
			);

			// Build issues list.
			$issues = array();
			if ( $spam_count > 100 ) {
				$issues[] = sprintf(
				/* translators: %s: number */
					\__( '%s spam comments', '0-day-analytics' ),
					static::format_number( $spam_count )
				);
			}
			if ( $trash_posts > 50 ) {
				$issues[] = sprintf(
				/* translators: %s: number */
					\__( '%s trashed posts', '0-day-analytics' ),
					static::format_number( $trash_posts )
				);
			}
			if ( $expired_transients > 100 ) {
				$issues[] = sprintf(
				/* translators: %s: number */
					\__( '%s expired temporary data entries', '0-day-analytics' ),
					static::format_number( $expired_transients )
				);
			}
			if ( $revisions > 1000 ) {
				$issues[] = sprintf(
				/* translators: %s: number */
					\__( '%s post revisions', '0-day-analytics' ),
					static::format_number( $revisions )
				);
			}

			if ( count( $issues ) >= 2 || $total_junk > 1000 ) {
				return static::fail(
					'medium',
					sprintf(
					/* translators: %s: list of issues */
						\__( 'Your database has accumulated unnecessary data: %s. This bloat slows down database queries and backups.', '0-day-analytics' ),
						implode( ', ', $issues )
					),
					\__( 'Use a database cleanup plugin like WP-Optimize or Advanced Database Cleaner to safely remove this data. Many caching plugins also include database optimization tools.', '0-day-analytics' ),
					$details
				);
			}

			if ( ! empty( $issues ) ) {
				return static::warning(
					sprintf(
					/* translators: %s: list of issues */
						\__( 'Your database has some accumulated overhead: %s. While not critical, periodic cleanup helps keep your site fast.', '0-day-analytics' ),
						implode( ', ', $issues )
					),
					\__( 'Consider using a database cleanup plugin or your hosting control panel to clean up old data periodically.', '0-day-analytics' ),
					$details
				);
			}

			return static::pass(
				\__( 'Your database is clean with minimal overhead. Spam, trash, and temporary data are at acceptable levels.', '0-day-analytics' ),
				$details
			);
		}
	}
}