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

declare(strict_types=1);

namespace ADVAN\Checks\Migration;

use ADVAN\Checks\Abstract_Check;

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

if ( ! class_exists( 'ADVAN\Checks\Migration\Tables_Overhead_Check' ) ) {

	/**
	 * Checks for fragmented/overhead space in WordPress database tables.
	 *
	 * MySQL tables accumulate overhead (data_free) when rows are deleted or
	 * updated. Optimising tables reclaims this space, reduces backup size, and
	 * can improve query performance.
	 *
	 * @since 4.9.0
	 */
	class Tables_Overhead_Check extends Abstract_Check {

		/**
		 * Check identifier (slug).
		 *
		 * @var string Check identifier (slug).
		 *
		 * @since 4.9.0
		 */
		protected static string $id = 'tables-overhead';

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

		/**
		 * Check weight (1-10).
		 *
		 * Higher-overhead databases create larger, slower migration transfers.
		 *
		 * @var int Check weight (1-10).
		 *
		 * @since 4.9.0
		 */
		protected static int $weight = 7;

		/**
		 * Overhead ratio considered acceptable (below this → pass).
		 *
		 * @since 4.9.0
		 */
		private const PASS_THRESHOLD = 0.05; // 5 %

		/**
		 * Overhead ratio that triggers a warning (below this → warning, above → fail).
		 *
		 * @since 4.9.0
		 */
		private const WARN_THRESHOLD = 0.15; // 15 %

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

		/**
		 * Check description (translatable).
		 *
		 * @return string Check description.
		 *
		 * @since 4.9.0
		 */
		public static function get_description(): string {
			return \__( 'Checks for fragmented overhead space in WordPress database tables that wastes storage and slows migrations.', '0-day-analytics' );
		}

		/**
		 * Run the check.
		 *
		 * Queries information_schema.TABLES for every table belonging to the current
		 * WordPress installation (filtered by table prefix) and aggregates:
		 *  - data_free  → wasted / fragmented bytes (overhead)
		 *  - data_length + index_length → actual used bytes
		 *
		 * @return array Check result array.
		 *
		 * @since 4.9.0
		 */
		public static function run(): array {
			global $wpdb;

			// Fetch per-table size and overhead for this database / prefix.
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					'SELECT
						table_name       AS name,
						COALESCE(data_length, 0) + COALESCE(index_length, 0) AS used_bytes,
						COALESCE(data_free, 0)                                AS free_bytes
					FROM information_schema.TABLES
					WHERE table_schema = %s
					  AND table_name   LIKE %s
					ORDER BY free_bytes DESC',
					DB_NAME,
					$wpdb->esc_like( $wpdb->prefix ) . '%'
				)
			);

			// If nothing came back we cannot grade the site – return informational.
			if ( empty( $rows ) ) {
				return static::info(
					\__( 'Could not retrieve table statistics. Your database user may lack access to information_schema.', '0-day-analytics' )
				);
			}

			$total_used     = 0;
			$total_overhead = 0;
			$top_tables     = array();

			foreach ( $rows as $row ) {
				$used     = (int) $row->used_bytes;
				$overhead = (int) $row->free_bytes;

				$total_used     += $used;
				$total_overhead += $overhead;

				// Collect the five tables with the most overhead for the detail panel.
				if ( $overhead > 0 && count( $top_tables ) < 5 ) {
					$top_tables[] = array(
						'name'     => $row->name,
						'overhead' => static::format_bytes( $overhead ),
						'size'     => static::format_bytes( $used ),
					);
				}
			}

			$total_size   = $total_used + $total_overhead;
			$overhead_pct = $total_size > 0 ? $total_overhead / $total_size : 0.0;

			$details = array(
				'total_size'    => static::format_bytes( $total_size ),
				'overhead_size' => static::format_bytes( $total_overhead ),
				'overhead_pct'  => round( $overhead_pct * 100, 1 ),
				'table_count'   => count( $rows ),
				'top_tables'    => $top_tables,
			);

			// ── Pass ────────────────────────────────────────────────────────
			if ( $overhead_pct < self::PASS_THRESHOLD ) {
				return static::pass(
					sprintf(
						/* translators: 1: overhead size, 2: overhead percent */
						\__( 'Your database tables have %1$s of overhead space (%2$s%% of total size). This is a normal amount of fragmentation and does not require immediate action.', '0-day-analytics' ),
						static::format_bytes( $total_overhead ),
						(string) round( $overhead_pct * 100, 1 )
					),
					$details
				);
			}

			// ── Warning ──────────────────────────────────────────────────────
			if ( $overhead_pct < self::WARN_THRESHOLD ) {
				return static::warning(
					sprintf(
						/* translators: 1: overhead size, 2: overhead percent */
						\__( 'Your database tables have %1$s of overhead space (%2$s%% of total size). While not critical yet, optimising your tables soon will help reclaim this space before migration.', '0-day-analytics' ),
						static::format_bytes( $total_overhead ),
						(string) round( $overhead_pct * 100, 1 )
					),
					\__( 'Schedule a database optimisation before migrating. Plugins such as WP-Optimize or Advanced Database Cleaner can do this safely. Always create a full backup first.', '0-day-analytics' ),
					$details
				);
			}

			// ── Fail ─────────────────────────────────────────────────────────
			return static::fail(
				'medium',
				sprintf(
					/* translators: 1: overhead size, 2: overhead percent */
					\__( 'Your database tables have %1$s of fragmented overhead space (%2$s%% of total size). This significant wasted space will slow down the migration transfer and inflate backup archives.', '0-day-analytics' ),
					static::format_bytes( $total_overhead ),
					(string) round( $overhead_pct * 100, 1 )
				),
				\__( 'Run table optimisation immediately to reclaim this space before migrating. Use WP-Optimize, Advanced Database Cleaner, or WP-Sweep. Schedule automatic weekly optimisations going forward, and always perform a full backup before running optimisation.', '0-day-analytics' ),
				$details
			);
		}
	}
}