<?php
/**
 * Paginated database entity browser for admin UI modules.
 *
 * @package DatabaseCleanup
 */

namespace DatabaseCleanup\Includes;

use DatabaseCleanup\Module;

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

class Entity_Browser {

	public const DEFAULT_PER_PAGE = 20;

	public const MAX_PER_PAGE = 500;

	/**
	 * Upper bound on rows loaded when an ownership ("belongs to") filter is active,
	 * since ownership is computed and cannot be expressed in SQL. The default,
	 * no-filter browsing path pages entirely in SQL with no such cap.
	 */
	private const SCAN_CAP = 20000;

	private static function belongs_all( string $belongs_to ): bool {
		return $belongs_to === '' || $belongs_to === 'all';
	}

	/**
	 * Prepare a SQL string only when it carries placeholders.
	 *
	 * @param array<int, mixed> $params
	 * @return string
	 */
	private static function q( string $sql, array $params ): string {
		global $wpdb;

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		return $params !== [] ? $wpdb->prepare( $sql, ...$params ) : $sql;
	}

	/**
	 * Clamp a page against a total row count and return the SQL limit/offset.
	 *
	 * @return array{page:int,offset:int,limit:int}
	 */
	private static function page_bounds( int $total, int $page, int $per_page ): array {
		$per_page    = self::sanitize_per_page( $per_page );
		$total_pages = max( 1, (int) ceil( $total / $per_page ) );
		$page        = min( max( 1, $page ), $total_pages );

		return [
			'page'   => $page,
			'offset' => ( $page - 1 ) * $per_page,
			'limit'  => $per_page,
		];
	}

	/**
	 * Compact ownership payload for the browser "Belongs to" column/badges.
	 *
	 * @param array<string, mixed> $ownership
	 * @return array<string, mixed>
	 */
	private static function owner_payload( array $ownership ): array {
		$candidates = [];
		foreach ( (array) ( $ownership['owners'] ?? [] ) as $owner ) {
			if ( ! is_array( $owner ) ) {
				continue;
			}
			$candidates[] = [
				'type'   => (string) ( $owner['type'] ?? '' ),
				'label'  => (string) ( $owner['label'] ?? '' ),
				'status' => (string) ( $owner['status'] ?? '' ),
			];
		}

		return [
			'type'       => (string) ( $ownership['type'] ?? 'unknown' ),
			'label'      => (string) ( $ownership['label'] ?? '' ),
			'status'     => (string) ( $ownership['status'] ?? '' ),
			'confidence' => (int) ( $ownership['confidence'] ?? 0 ),
			'by'         => (string) ( $ownership['by'] ?? 'heuristic' ),
			'scanned'    => ! empty( $ownership['scanned'] ),
			'manual'     => ! empty( $ownership['manual'] ),
			'candidates' => array_slice( $candidates, 0, 5 ),
		];
	}

	/**
	 * Finalize an already SQL-paged, SQL-sorted result set (no PHP sort/slice).
	 *
	 * @param list<array<string, mixed>>          $slice
	 * @param list<array{label:string,value:string}> $summary
	 * @return array<string, mixed>
	 */
	private static function paginate_prequeried( array $slice, int $total, int $page, int $per_page, array $summary, bool $truncated = false ): array {
		$per_page    = self::sanitize_per_page( $per_page );
		$total_pages = max( 1, (int) ceil( $total / $per_page ) );
		$page        = min( max( 1, $page ), $total_pages );

		foreach ( $slice as &$item ) {
			unset( $item['_sort'], $item['_ownership'] );
			if ( isset( $item['_event'] ) && is_array( $item['_event'] ) ) {
				$item['id'] = base64_encode( wp_json_encode( $item['_event'] ) ?: '' );
				unset( $item['_event'] );
			}
		}
		unset( $item );

		return [
			'items'       => $slice,
			'total'       => $total,
			'page'        => $page,
			'per_page'    => $per_page,
			'total_pages' => $total_pages,
			'truncated'   => $truncated,
			'summary'     => $summary,
		];
	}

	public static function sanitize_per_page( int $per_page ): int {
		if ( $per_page < 1 ) {
			return self::DEFAULT_PER_PAGE;
		}

		return min( self::MAX_PER_PAGE, absint( $per_page ) );
	}

	/**
	 * @return list<int>
	 */
	public static function per_page_options(): array {
		return [ 20, 50, 100, 200, 500 ];
	}

	/**
	 * @param array<string, mixed> $args
	 * @return array<string, mixed>
	 */
	public static function list_items( string $type, array $args = [] ): array {
		$type = sanitize_key( $type );

		return match ( $type ) {
			'tables'       => self::list_tables( $args ),
			'options'      => self::list_options( $args ),
			'post_meta'    => self::list_post_meta( $args ),
			'user_meta'    => self::list_user_meta( $args ),
			'comment_meta' => self::list_comment_meta( $args ),
			'term_meta'    => self::list_term_meta( $args ),
			'transients'   => self::list_transients( $args ),
			'cron'         => self::list_cron( $args ),
			'post_types'   => self::list_post_types( $args ),
			default        => [
				'items'       => [],
				'total'       => 0,
				'page'        => 1,
				'per_page'    => self::DEFAULT_PER_PAGE,
				'total_pages' => 0,
			],
		};
	}

	/**
	 * @param array<string, mixed> $args
	 * @return array<string, mixed>
	 */
	public static function run_action( string $type, string $action, array $items, array $args = [] ): array {
		$type   = sanitize_key( $type );
		$action = sanitize_key( $action );

		if ( self::action_requires_typed_confirm( $type, $action ) && empty( $args['typed_confirm_valid'] ) ) {
			return [
				'affected' => 0,
				'message'  => __( 'Type CLEAN to confirm this action.', 'wp-powersuite' ),
			];
		}

		if ( $action === 'clean_orphans' ) {
			return self::clean_orphans( $type, $args );
		}

		return match ( $type ) {
			'tables'       => self::action_tables( $action, $items, $args ),
			'options'      => self::action_options( $action, $items, $args ),
			'post_meta'    => self::action_meta_ids( 'post', $action, $items ),
			'user_meta'    => self::action_meta_ids( 'user', $action, $items ),
			'comment_meta' => self::action_meta_ids( 'comment', $action, $items ),
			'term_meta'    => self::action_meta_ids( 'term', $action, $items ),
			'transients'   => self::action_transients( $action, $items ),
			'cron'         => self::action_cron( $action, $items ),
			'post_types'   => self::action_post_types( $action, $items, $args ),
			default        => [ 'affected' => 0, 'message' => __( 'Unknown entity type.', 'wp-powersuite' ) ],
		};
	}

	/**
	 * Bulk-remove every detected orphan/expired item of a type, reusing the
	 * existing listing + per-item delete logic (so all protections still apply).
	 *
	 * @param array<string, mixed> $args
	 * @return array<string, mixed>
	 */
	/**
	 * Per-type orphan/expired cleanup configuration (list filter + delete action).
	 *
	 * @return array<string, array{list: array<string, string>, action: string}>
	 */
	private static function orphan_clean_map(): array {
		return [
			'tables'       => [ 'list' => [ 'filter' => 'orphan' ], 'action' => 'drop' ],
			'options'      => [ 'list' => [ 'belongs_to' => 'orphan' ], 'action' => 'delete' ],
			'post_meta'    => [ 'list' => [ 'filter' => 'orphaned' ], 'action' => 'delete' ],
			'user_meta'    => [ 'list' => [ 'filter' => 'orphaned' ], 'action' => 'delete' ],
			'comment_meta' => [ 'list' => [ 'filter' => 'orphaned' ], 'action' => 'delete' ],
			'term_meta'    => [ 'list' => [ 'filter' => 'orphaned' ], 'action' => 'delete' ],
			'transients'   => [ 'list' => [ 'filter' => 'expired' ], 'action' => 'delete' ],
			'cron'         => [ 'list' => [ 'filter' => 'orphan' ], 'action' => 'delete' ],
		];
	}

	/**
	 * Preview the items a bulk orphan cleanup would remove (for user review).
	 *
	 * @param array<string, mixed> $args
	 * @return array{action: string, items: list<array{id:string,label:string,detail:string,protected:bool}>, total:int, shown:int}
	 */
	public static function orphan_preview( string $type, array $args, int $limit = 500 ): array {
		$map = self::orphan_clean_map();

		if ( ! isset( $map[ $type ] ) ) {
			return [ 'action' => '', 'items' => [], 'total' => 0, 'shown' => 0 ];
		}

		$list = self::list_items(
			$type,
			array_merge(
				[ 'settings' => $args['settings'] ?? [] ],
				$map[ $type ]['list'],
				[ 'page' => 1, 'per_page' => $limit ]
			)
		);

		$items = is_array( $list['items'] ?? null ) ? $list['items'] : [];
		$out   = [];

		foreach ( $items as $item ) {
			$label = (string) ( $item['name'] ?? $item['meta_key'] ?? $item['hook'] ?? $item['id'] ?? '' );

			$detail = '';
			if ( isset( $item['post_id'] ) || isset( $item['user_id'] ) || isset( $item['comment_id'] ) || isset( $item['term_id'] ) ) {
				$parent  = (string) ( $item['post_id'] ?? $item['user_id'] ?? $item['comment_id'] ?? $item['term_id'] ?? '' );
				$detail  = sprintf( /* translators: %s: parent object id */ __( 'parent #%s (missing)', 'wp-powersuite' ), $parent );
			} elseif ( ! empty( $item['belongs_to'] ) ) {
				$detail = (string) $item['belongs_to'];
			}

			$out[] = [
				'id'        => (string) ( $item['id'] ?? '' ),
				'label'     => $label,
				'detail'    => $detail,
				'protected' => ! empty( $item['protected'] ),
			];
		}

		return [
			'action' => (string) $map[ $type ]['action'],
			'items'  => $out,
			'total'  => (int) ( $list['total'] ?? count( $out ) ),
			'shown'  => count( $out ),
		];
	}

	private static function clean_orphans( string $type, array $args ): array {
		$map = self::orphan_clean_map();

		if ( ! isset( $map[ $type ] ) ) {
			return [ 'affected' => 0, 'message' => __( 'Bulk cleanup is not available for this type.', 'wp-powersuite' ) ];
		}

		$sub_action = (string) $map[ $type ]['action'];
		$total      = 0;

		// Bounded loop: each pass lists up to 500 detected items and deletes them.
		for ( $i = 0; $i < 40; $i++ ) {
			$list = self::list_items(
				$type,
				array_merge(
					[ 'settings' => $args['settings'] ?? [] ],
					$map[ $type ]['list'],
					[ 'page' => 1, 'per_page' => 500 ]
				)
			);

			$items = is_array( $list['items'] ?? null ) ? $list['items'] : [];
			$ids   = [];

			foreach ( $items as $item ) {
				if ( isset( $item['id'] ) ) {
					$ids[] = (string) $item['id'];
				}
			}

			if ( $ids === [] ) {
				break;
			}

			$result = self::run_action(
				$type,
				$sub_action,
				$ids,
				array_merge( $args, [ 'typed_confirm_valid' => true ] )
			);

			$affected = (int) ( $result['affected'] ?? 0 );
			$total   += $affected;

			// Nothing deletable remains (e.g. all protected) — stop to avoid looping.
			if ( $affected === 0 ) {
				break;
			}
		}

		if ( $type === 'tables' ) {
			$message = sprintf(
				/* translators: %d: number of tables deleted */
				_n( '%d orphan table deleted.', '%d orphan tables deleted.', $total, 'wp-powersuite' ),
				$total
			);
		} elseif ( $type === 'transients' ) {
			$message = sprintf(
				/* translators: %d: number of transients deleted */
				_n( '%d expired transient deleted.', '%d expired transients deleted.', $total, 'wp-powersuite' ),
				$total
			);
		} else {
			$message = sprintf(
				/* translators: %d: number of items removed */
				_n( '%d orphan item removed.', '%d orphan items removed.', $total, 'wp-powersuite' ),
				$total
			);
		}

		if ( $total === 0 ) {
			$message = __( 'No removable orphan items were found.', 'wp-powersuite' );
		}

		return [ 'affected' => $total, 'message' => $message ];
	}

	public static function action_requires_typed_confirm( string $type, string $action ): bool {
		$type   = sanitize_key( $type );
		$action = sanitize_key( $action );

		if ( $action === 'clean_orphans' ) {
			return true;
		}

		if ( in_array( $action, [ 'optimize', 'repair', 'convert_innodb', 'drop' ], true ) ) {
			return true;
		}

		return match ( $type ) {
			'post_meta', 'user_meta', 'comment_meta', 'term_meta' => $action === 'delete',
			'options', 'transients' => in_array( $action, [ 'delete', 'autoload_yes', 'autoload_no' ], true ),
			'cron'       => $action === 'delete',
			'post_types' => $action === 'delete_posts',
			default      => false,
		};
	}

	/**
	 * @return array<string, mixed>
	 */
	public static function browser_config( string $type ): array {
		return match ( sanitize_key( $type ) ) {
			'tables' => [
				'title'       => __( 'Tables', 'wp-powersuite' ),
				'description' => __( 'Browse database tables, view size and overhead, inspect structure, then optimize or repair selected tables.', 'wp-powersuite' ),
				'columns'     => [
					[ 'key' => 'name', 'label' => __( 'Table', 'wp-powersuite' ), 'sortable' => true ],
					[ 'key' => 'rows', 'label' => __( 'Rows', 'wp-powersuite' ), 'sortable' => true ],
					[ 'key' => 'size', 'label' => __( 'Size', 'wp-powersuite' ), 'sortable' => true ],
					[ 'key' => 'overhead', 'label' => __( 'Overhead', 'wp-powersuite' ), 'sortable' => true ],
					[ 'key' => 'engine', 'label' => __( 'Engine', 'wp-powersuite' ), 'sortable' => true ],
					[ 'key' => 'core', 'label' => __( 'Type', 'wp-powersuite' ), 'sortable' => false ],
					[ 'key' => 'belongs_to', 'label' => __( 'Belongs to', 'wp-powersuite' ), 'sortable' => true ],
				],
				'actions'     => [
					[ 'id' => 'optimize', 'label' => __( 'Optimize', 'wp-powersuite' ), 'confirm' => __( 'Optimize selected tables? Large tables may lock briefly.', 'wp-powersuite' ) ],
					[ 'id' => 'repair', 'label' => __( 'Repair', 'wp-powersuite' ), 'confirm' => __( 'Repair selected tables?', 'wp-powersuite' ) ],
					[ 'id' => 'convert_innodb', 'label' => __( 'Convert to InnoDB', 'wp-powersuite' ), 'confirm' => __( 'Convert selected tables to InnoDB?', 'wp-powersuite' ) ],
					[ 'id' => 'drop', 'label' => __( 'Delete table', 'wp-powersuite' ), 'destructive' => true, 'confirm' => __( 'Permanently DROP the selected table(s)? Only tables from uninstalled plugins can be dropped. Core, active, and inactive-but-installed plugin tables are skipped. This cannot be undone.', 'wp-powersuite' ) ],
				],
				'filters'     => [
					[ 'key' => 'filter', 'label' => __( 'Show', 'wp-powersuite' ), 'options' => [
						'all'       => __( 'All tables', 'wp-powersuite' ),
						'core'      => __( 'WordPress core', 'wp-powersuite' ),
						'non_core'  => __( 'Non-core', 'wp-powersuite' ),
						'overhead'  => __( 'Has overhead', 'wp-powersuite' ),
						'orphan'    => __( 'Orphan (uninstalled)', 'wp-powersuite' ),
					] ],
					self::belongs_filter_group(),
				],
				'viewable'    => true,
				'detail_type' => 'table',
				'bulk'        => [
					'label'   => __( 'Delete all orphan tables', 'wp-powersuite' ),
					'confirm' => __( 'Permanently DROP every table left by uninstalled plugins? Tables for active or inactive-but-installed plugins are never dropped. This cannot be undone.', 'wp-powersuite' ),
				],
			],
			'options' => [
				'title'       => __( 'Options', 'wp-powersuite' ),
				'description' => __( 'Review site options, autoload status, and size. View values, toggle autoload, or delete selected options.', 'wp-powersuite' ),
				'columns'     => [
					[ 'key' => 'name', 'label' => __( 'Option', 'wp-powersuite' ), 'sortable' => true ],
					[ 'key' => 'size', 'label' => __( 'Size', 'wp-powersuite' ), 'sortable' => true ],
					[ 'key' => 'autoload', 'label' => __( 'Autoload', 'wp-powersuite' ), 'sortable' => true ],
					[ 'key' => 'belongs_to', 'label' => __( 'Belongs to', 'wp-powersuite' ), 'sortable' => true ],
					[ 'key' => 'value_preview', 'label' => __( 'Value', 'wp-powersuite' ), 'sortable' => false ],
				],
				'actions'     => [
					[ 'id' => 'delete', 'label' => __( 'Delete', 'wp-powersuite' ), 'confirm' => __( 'Delete selected options? Core options are skipped automatically.', 'wp-powersuite' ) ],
					[ 'id' => 'autoload_yes', 'label' => __( 'Set autoload ON', 'wp-powersuite' ), 'confirm' => __( 'Enable autoload for selected options?', 'wp-powersuite' ) ],
					[ 'id' => 'autoload_no', 'label' => __( 'Set autoload OFF', 'wp-powersuite' ), 'confirm' => __( 'Disable autoload for selected options?', 'wp-powersuite' ) ],
				],
				'filters'     => [
					[ 'key' => 'filter', 'label' => __( 'Show', 'wp-powersuite' ), 'options' => [
						'all'      => __( 'All options', 'wp-powersuite' ),
						'autoload' => __( 'Autoloaded', 'wp-powersuite' ),
						'no_autoload' => __( 'Not autoloaded', 'wp-powersuite' ),
						'big'      => __( 'Large (>150 KB)', 'wp-powersuite' ),
					] ],
					self::belongs_filter_group(),
				],
				'viewable'    => true,
				'detail_type' => 'option',
				'bulk'        => [
					'label'   => __( 'Delete all orphaned options', 'wp-powersuite' ),
					'confirm' => __( 'Delete every option left by uninstalled plugins/themes? Options for active or inactive-but-installed add-ons, plus all protected/core options, are skipped.', 'wp-powersuite' ),
				],
			],
			'post_meta' => [
				'title'       => __( 'Post Meta', 'wp-powersuite' ),
				'description' => __( 'Browse post meta rows. Filter by orphaned or duplicated entries.', 'wp-powersuite' ),
				'columns'     => [
					[ 'key' => 'meta_key', 'label' => __( 'Meta key', 'wp-powersuite' ), 'sortable' => true ],
					[ 'key' => 'post_id', 'label' => __( 'Post ID', 'wp-powersuite' ), 'sortable' => true ],
					[ 'key' => 'size', 'label' => __( 'Size', 'wp-powersuite' ), 'sortable' => true ],
					[ 'key' => 'belongs_to', 'label' => __( 'Belongs to', 'wp-powersuite' ), 'sortable' => true ],
					[ 'key' => 'value_preview', 'label' => __( 'Value', 'wp-powersuite' ), 'sortable' => false ],
				],
				'actions'     => [
					[ 'id' => 'delete', 'label' => __( 'Delete', 'wp-powersuite' ), 'confirm' => __( 'Delete selected post meta rows?', 'wp-powersuite' ) ],
				],
				'filters'     => [
					[ 'key' => 'filter', 'label' => __( 'Show', 'wp-powersuite' ), 'options' => [
						'all'         => __( 'All post meta', 'wp-powersuite' ),
						'orphaned'    => __( 'Orphaned', 'wp-powersuite' ),
						'duplicated'  => __( 'Duplicated', 'wp-powersuite' ),
					] ],
					self::belongs_filter_group(),
				],
				'viewable'    => true,
				'detail_type' => 'post_meta',
				'bulk'        => [
					'label'   => __( 'Delete all orphaned rows', 'wp-powersuite' ),
					'confirm' => __( 'Delete every orphaned post meta row (parent post no longer exists)? Protected keys are skipped.', 'wp-powersuite' ),
				],
			],
			'user_meta' => [
				'title'       => __( 'User Meta', 'wp-powersuite' ),
				'description' => __( 'Browse user meta rows. Filter by orphaned or duplicated entries.', 'wp-powersuite' ),
				'columns'     => [
					[ 'key' => 'meta_key', 'label' => __( 'Meta key', 'wp-powersuite' ), 'sortable' => true ],
					[ 'key' => 'user_id', 'label' => __( 'User ID', 'wp-powersuite' ), 'sortable' => true ],
					[ 'key' => 'size', 'label' => __( 'Size', 'wp-powersuite' ), 'sortable' => true ],
					[ 'key' => 'belongs_to', 'label' => __( 'Belongs to', 'wp-powersuite' ), 'sortable' => true ],
					[ 'key' => 'value_preview', 'label' => __( 'Value', 'wp-powersuite' ), 'sortable' => false ],
				],
				'actions'     => [
					[ 'id' => 'delete', 'label' => __( 'Delete', 'wp-powersuite' ), 'confirm' => __( 'Delete selected user meta rows?', 'wp-powersuite' ) ],
				],
				'filters'     => [
					[ 'key' => 'filter', 'label' => __( 'Show', 'wp-powersuite' ), 'options' => [
						'all'         => __( 'All user meta', 'wp-powersuite' ),
						'orphaned'    => __( 'Orphaned', 'wp-powersuite' ),
						'duplicated'  => __( 'Duplicated', 'wp-powersuite' ),
					] ],
					self::belongs_filter_group(),
				],
				'viewable'    => true,
				'detail_type' => 'user_meta',
				'bulk'        => [
					'label'   => __( 'Delete all orphaned rows', 'wp-powersuite' ),
					'confirm' => __( 'Delete every orphaned user meta row (parent user no longer exists)? Protected keys are skipped.', 'wp-powersuite' ),
				],
			],
			'comment_meta' => [
				'title'       => __( 'Comment Meta', 'wp-powersuite' ),
				'description' => __( 'Browse comment meta rows. Filter by orphaned or duplicated entries.', 'wp-powersuite' ),
				'columns'     => [
					[ 'key' => 'meta_key', 'label' => __( 'Meta key', 'wp-powersuite' ), 'sortable' => true ],
					[ 'key' => 'comment_id', 'label' => __( 'Comment ID', 'wp-powersuite' ), 'sortable' => true ],
					[ 'key' => 'size', 'label' => __( 'Size', 'wp-powersuite' ), 'sortable' => true ],
					[ 'key' => 'belongs_to', 'label' => __( 'Belongs to', 'wp-powersuite' ), 'sortable' => true ],
					[ 'key' => 'value_preview', 'label' => __( 'Value', 'wp-powersuite' ), 'sortable' => false ],
				],
				'actions'     => [
					[ 'id' => 'delete', 'label' => __( 'Delete', 'wp-powersuite' ), 'confirm' => __( 'Delete selected comment meta rows?', 'wp-powersuite' ) ],
				],
				'filters'     => [
					[ 'key' => 'filter', 'label' => __( 'Show', 'wp-powersuite' ), 'options' => [
						'all'         => __( 'All comment meta', 'wp-powersuite' ),
						'orphaned'    => __( 'Orphaned', 'wp-powersuite' ),
						'duplicated'  => __( 'Duplicated', 'wp-powersuite' ),
					] ],
					self::belongs_filter_group(),
				],
				'viewable'    => true,
				'detail_type' => 'comment_meta',
				'bulk'        => [
					'label'   => __( 'Delete all orphaned rows', 'wp-powersuite' ),
					'confirm' => __( 'Delete every orphaned comment meta row (parent comment no longer exists)? Protected keys are skipped.', 'wp-powersuite' ),
				],
			],
			'term_meta' => [
				'title'       => __( 'Term Meta', 'wp-powersuite' ),
				'description' => __( 'Browse term meta rows. Filter by orphaned or duplicated entries.', 'wp-powersuite' ),
				'columns'     => [
					[ 'key' => 'meta_key', 'label' => __( 'Meta key', 'wp-powersuite' ), 'sortable' => true ],
					[ 'key' => 'term_id', 'label' => __( 'Term ID', 'wp-powersuite' ), 'sortable' => true ],
					[ 'key' => 'size', 'label' => __( 'Size', 'wp-powersuite' ), 'sortable' => true ],
					[ 'key' => 'belongs_to', 'label' => __( 'Belongs to', 'wp-powersuite' ), 'sortable' => true ],
					[ 'key' => 'value_preview', 'label' => __( 'Value', 'wp-powersuite' ), 'sortable' => false ],
				],
				'actions'     => [
					[ 'id' => 'delete', 'label' => __( 'Delete', 'wp-powersuite' ), 'confirm' => __( 'Delete selected term meta rows?', 'wp-powersuite' ) ],
				],
				'filters'     => [
					[ 'key' => 'filter', 'label' => __( 'Show', 'wp-powersuite' ), 'options' => [
						'all'         => __( 'All term meta', 'wp-powersuite' ),
						'orphaned'    => __( 'Orphaned', 'wp-powersuite' ),
						'duplicated'  => __( 'Duplicated', 'wp-powersuite' ),
					] ],
					self::belongs_filter_group(),
				],
				'viewable'    => true,
				'detail_type' => 'term_meta',
				'bulk'        => [
					'label'   => __( 'Delete all orphaned rows', 'wp-powersuite' ),
					'confirm' => __( 'Delete every orphaned term meta row (parent term no longer exists)? Protected keys are skipped.', 'wp-powersuite' ),
				],
			],
			'transients' => [
				'title'       => __( 'Transients', 'wp-powersuite' ),
				'description' => __( 'Browse transients stored in the options table.', 'wp-powersuite' ),
				'columns'     => [
					[ 'key' => 'name', 'label' => __( 'Transient', 'wp-powersuite' ), 'sortable' => true ],
					[ 'key' => 'size', 'label' => __( 'Size', 'wp-powersuite' ), 'sortable' => true ],
					[ 'key' => 'expired', 'label' => __( 'Status', 'wp-powersuite' ), 'sortable' => true ],
					[ 'key' => 'timeout', 'label' => __( 'Expires', 'wp-powersuite' ), 'sortable' => true ],
					[ 'key' => 'belongs_to', 'label' => __( 'Belongs to', 'wp-powersuite' ), 'sortable' => true ],
					[ 'key' => 'value_preview', 'label' => __( 'Value', 'wp-powersuite' ), 'sortable' => false ],
				],
				'actions'     => [
					[ 'id' => 'delete', 'label' => __( 'Delete', 'wp-powersuite' ), 'confirm' => __( 'Delete selected transients?', 'wp-powersuite' ) ],
				],
				'filters'     => [
					[ 'key' => 'filter', 'label' => __( 'Show', 'wp-powersuite' ), 'options' => [
						'all'     => __( 'All transients', 'wp-powersuite' ),
						'expired' => __( 'Expired', 'wp-powersuite' ),
						'active'  => __( 'Active', 'wp-powersuite' ),
						'big'     => __( 'Large (>150 KB)', 'wp-powersuite' ),
					] ],
					self::belongs_filter_group(),
				],
				'viewable'    => true,
				'detail_type' => 'transient',
				'bulk'        => [
					'label'   => __( 'Delete all expired', 'wp-powersuite' ),
					'confirm' => __( 'Delete every expired transient? Active transients are kept.', 'wp-powersuite' ),
				],
			],
			'cron' => [
				'title'       => __( 'Cron Jobs', 'wp-powersuite' ),
				'description' => __( 'Review scheduled cron events and remove stale or unwanted hooks.', 'wp-powersuite' ),
				'columns'     => [
					[ 'key' => 'hook', 'label' => __( 'Hook', 'wp-powersuite' ), 'sortable' => true ],
					[ 'key' => 'schedule', 'label' => __( 'Schedule', 'wp-powersuite' ), 'sortable' => true ],
					[ 'key' => 'next_run', 'label' => __( 'Next run', 'wp-powersuite' ), 'sortable' => true ],
					[ 'key' => 'orphan', 'label' => __( 'Hook status', 'wp-powersuite' ), 'sortable' => true ],
					[ 'key' => 'belongs_to', 'label' => __( 'Belongs to', 'wp-powersuite' ), 'sortable' => true ],
					[ 'key' => 'args_preview', 'label' => __( 'Args', 'wp-powersuite' ), 'sortable' => false ],
				],
				'actions'     => [
					[ 'id' => 'delete', 'label' => __( 'Delete', 'wp-powersuite' ), 'confirm' => __( 'Delete selected cron events?', 'wp-powersuite' ) ],
				],
				'filters'     => [
					[ 'key' => 'filter', 'label' => __( 'Show', 'wp-powersuite' ), 'options' => [
						'all'     => __( 'All cron events', 'wp-powersuite' ),
						'orphan'  => __( 'Orphan hooks', 'wp-powersuite' ),
					] ],
					self::belongs_filter_group(),
				],
				'viewable'    => true,
				'detail_type' => 'cron',
				'bulk'        => [
					'label'   => __( 'Delete all orphan hooks', 'wp-powersuite' ),
					'confirm' => __( 'Delete every cron event whose hook has no registered callback? Protected hooks are skipped.', 'wp-powersuite' ),
				],
			],
			'post_types' => [
				'title'       => __( 'Post Types', 'wp-powersuite' ),
				'description' => __( 'Review post counts by type. Bulk delete is blocked for posts, pages, attachments, templates, and page-builder libraries.', 'wp-powersuite' ),
				'columns'     => [
					[ 'key' => 'name', 'label' => __( 'Post type', 'wp-powersuite' ), 'sortable' => true ],
					[ 'key' => 'label', 'label' => __( 'Label', 'wp-powersuite' ), 'sortable' => true ],
					[ 'key' => 'count', 'label' => __( 'Posts', 'wp-powersuite' ), 'sortable' => true ],
					[ 'key' => 'public', 'label' => __( 'Public', 'wp-powersuite' ), 'sortable' => false ],
					[ 'key' => 'belongs_to', 'label' => __( 'Belongs to', 'wp-powersuite' ), 'sortable' => false ],
				],
				'actions'     => [
					[ 'id' => 'delete_posts', 'label' => __( 'Delete all posts', 'wp-powersuite' ), 'confirm' => __( 'Permanently delete ALL posts of the selected type(s)? Posts, pages, templates, and other protected types are never deleted. This cannot be undone.', 'wp-powersuite' ) ],
				],
				'filters'     => [
					[ 'key' => 'filter', 'label' => __( 'Show', 'wp-powersuite' ), 'options' => [
						'all'        => __( 'All post types', 'wp-powersuite' ),
						'non_public' => __( 'Non-public only', 'wp-powersuite' ),
						'empty'      => __( 'Zero posts', 'wp-powersuite' ),
					] ],
				],
				'viewable'    => true,
				'detail_type' => 'post_type',
			],
			default => [],
		};
	}

	/**
	 * @param array<string, mixed> $args
	 * @return array<string, mixed>
	 */
	private static function list_tables( array $args ): array {
		global $wpdb;

		$page     = max( 1, absint( $args['page'] ?? 1 ) );
		$search   = sanitize_text_field( (string) ( $args['search'] ?? '' ) );
		$filter      = sanitize_key( (string) ( $args['filter'] ?? 'all' ) );
		$belongs_to  = sanitize_key( (string) ( $args['belongs_to'] ?? 'all' ) );
		$sort_by     = sanitize_key( (string) ( $args['sort_by'] ?? 'size' ) );
		$sort_dir = strtoupper( (string) ( $args['sort_dir'] ?? 'DESC' ) ) === 'ASC' ? 'ASC' : 'DESC';
		$db_name  = defined( 'DB_NAME' ) ? DB_NAME : '';

		if ( $db_name === '' ) {
			return self::empty_page( $page, self::per_page_from_args( $args ) );
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT TABLE_NAME AS name, TABLE_ROWS AS rows_count, (DATA_LENGTH + INDEX_LENGTH) AS size_bytes, DATA_FREE AS overhead_bytes, ENGINE AS engine
				FROM information_schema.TABLES
				WHERE TABLE_SCHEMA = %s AND TABLE_NAME LIKE %s
				ORDER BY TABLE_NAME ASC",
				$db_name,
				$wpdb->esc_like( (string) $wpdb->prefix ) . '%'
			),
			ARRAY_A
		);

		$items = [];

		foreach ( (array) $rows as $row ) {
			$name       = (string) ( $row['name'] ?? '' );
			$overhead   = (int) ( $row['overhead_bytes'] ?? 0 );
			$size_bytes = (int) ( $row['size_bytes'] ?? 0 );
			$engine     = (string) ( $row['engine'] ?? '' );
			$core       = Protected_Items::is_core_table( $name );

			$recommend       = '';
			$recommend_level = '';
			if ( $overhead >= 262144 || ( $overhead > 0 && $size_bytes > 0 && $overhead >= (int) round( $size_bytes * 0.1 ) ) ) {
				$recommend       = __( 'Optimize', 'wp-powersuite' );
				$recommend_level = 'warn';
			} elseif ( strtoupper( $engine ) === 'MYISAM' ) {
				$recommend       = __( 'Convert', 'wp-powersuite' );
				$recommend_level = 'info';
			}

			$ownership = Item_Ownership::resolve( 'table', $name );

			if ( $search !== '' && stripos( $name, $search ) === false ) {
				continue;
			}

			if ( $filter === 'core' && ! $core ) {
				continue;
			}
			if ( $filter === 'non_core' && $core ) {
				continue;
			}
			if ( $filter === 'overhead' && $overhead <= 0 ) {
				continue;
			}
			if ( $filter === 'orphan' && ! Item_Ownership::is_deletable_orphan( $ownership ) ) {
				continue;
			}

			$items[] = [
				'id'         => $name,
				'name'       => $name,
				'rows'       => number_format_i18n( (int) ( $row['rows_count'] ?? 0 ) ),
				'size'       => Module::formatBytes( (int) ( $row['size_bytes'] ?? 0 ) ),
				'overhead'   => Module::formatBytes( $overhead ),
				'engine'     => $engine !== '' ? $engine : '—',
				'core'       => $core ? __( 'Core', 'wp-powersuite' ) : __( 'Custom', 'wp-powersuite' ),
				'belongs_to' => Item_Ownership::format_belongs_to( $ownership ),
				'owner'      => self::owner_payload( $ownership ),
				'recommend'       => $recommend,
				'recommend_level' => $recommend_level,
				'flag'       => Item_Ownership::is_deletable_orphan( $ownership ) ? 'orphan' : '',
				// Only lock the checkbox for true non-droppable tables when browsing orphans;
				// optimize/repair still need selectable core/plugin tables.
				'protected'  => $filter === 'orphan' && ! Protected_Items::is_destructive_table_action_allowed( $name, 'drop' ),
				'viewable'   => true,
				'_ownership' => $ownership,
				'_sort'      => [
					'name'       => $name,
					'rows'       => (int) ( $row['rows_count'] ?? 0 ),
					'size'       => (int) ( $row['size_bytes'] ?? 0 ),
					'overhead'   => $overhead,
					'engine'     => (string) ( $row['engine'] ?? '' ),
					'belongs_to' => (string) ( $ownership['label'] ?? '' ),
				],
			];
		}

		$filtered = self::apply_belongs_filter( $items, $belongs_to );

		return self::paginate( $filtered, $page, $sort_by, $sort_dir, self::per_page_from_args( $args ), false, self::build_summary( 'tables', $filtered ) );
	}

	/**
	 * @param array<string, mixed> $args
	 */
	private static function per_page_from_args( array $args ): int {
		return self::sanitize_per_page( (int) ( $args['per_page'] ?? self::DEFAULT_PER_PAGE ) );
	}

	/**
	 * @param array<string, mixed> $args
	 * @return array<string, mixed>
	 */
	private static function list_options( array $args ): array {
		global $wpdb;

		$page       = max( 1, absint( $args['page'] ?? 1 ) );
		$search     = sanitize_text_field( (string) ( $args['search'] ?? '' ) );
		$filter     = sanitize_key( (string) ( $args['filter'] ?? 'all' ) );
		$belongs_to = sanitize_key( (string) ( $args['belongs_to'] ?? 'all' ) );
		$sort_by    = sanitize_key( (string) ( $args['sort_by'] ?? 'size' ) );
		$sort_dir   = strtoupper( (string) ( $args['sort_dir'] ?? 'DESC' ) ) === 'ASC' ? 'ASC' : 'DESC';
		$per_page   = self::per_page_from_args( $args );

		$where    = "option_name NOT LIKE '\_transient\_%' AND option_name NOT LIKE '\_site\_transient\_%'";
		$params   = [];

		if ( $search !== '' ) {
			$where   .= ' AND option_name LIKE %s';
			$params[] = '%' . $wpdb->esc_like( $search ) . '%';
		}

		if ( $filter === 'autoload' ) {
			$where .= " AND autoload IN ('yes','on','auto')";
		} elseif ( $filter === 'no_autoload' ) {
			$where .= " AND autoload NOT IN ('yes','on','auto')";
		} elseif ( $filter === 'big' ) {
			$where .= ' AND LENGTH(option_value) > 153600';
		}

		$order_map = [ 'name' => 'option_name', 'size' => 'LENGTH(option_value)', 'autoload' => 'autoload' ];
		$select    = "option_id, option_name, option_value, autoload, LENGTH(option_value) AS size_bytes";

		// Fast path: no ownership filter and a SQL-sortable column — page entirely in SQL.
		if ( self::belongs_all( $belongs_to ) && isset( $order_map[ $sort_by ] ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
			$total  = (int) $wpdb->get_var( self::q( "SELECT COUNT(*) FROM {$wpdb->options} WHERE {$where}", $params ) );
			$paging = self::page_bounds( $total, $page, $per_page );
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
			$rows   = $wpdb->get_results(
				self::q( "SELECT {$select} FROM {$wpdb->options} WHERE {$where} ORDER BY {$order_map[ $sort_by ]} {$sort_dir}, option_id ASC LIMIT {$paging['limit']} OFFSET {$paging['offset']}", $params ),
				ARRAY_A
			);

			$items = array_map( [ self::class, 'build_option_item' ], (array) $rows );

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
			$agg = $wpdb->get_row(
				self::q(
					"SELECT COUNT(*) c, COALESCE(SUM(LENGTH(option_value)),0) total_size,
					SUM(CASE WHEN autoload IN ('yes','on','auto') THEN 1 ELSE 0 END) auto_count,
					COALESCE(SUM(CASE WHEN autoload IN ('yes','on','auto') THEN LENGTH(option_value) ELSE 0 END),0) auto_size
					FROM {$wpdb->options} WHERE {$where}",
					$params
				),
				ARRAY_A
			);

			$summary = self::summary_chips(
				[
					[ __( 'Options', 'wp-powersuite' ), number_format_i18n( (int) ( $agg['c'] ?? $total ) ) ],
					[ __( 'Total size', 'wp-powersuite' ), Module::formatBytes( (int) ( $agg['total_size'] ?? 0 ) ) ],
					[ __( 'Autoloaded', 'wp-powersuite' ), number_format_i18n( (int) ( $agg['auto_count'] ?? 0 ) ) ],
					[ __( 'Autoload size', 'wp-powersuite' ), Module::formatBytes( (int) ( $agg['auto_size'] ?? 0 ) ) ],
				]
			);

			return self::paginate_prequeried( $items, $total, $paging['page'], $per_page, $summary );
		}

		// Ownership-filter path: bounded scan of names, resolve ownership (store O(1)), filter.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
		$rows      = $wpdb->get_results( self::q( "SELECT {$select} FROM {$wpdb->options} WHERE {$where} ORDER BY option_id ASC LIMIT " . self::SCAN_CAP, $params ), ARRAY_A );
		$truncated = count( (array) $rows ) >= self::SCAN_CAP;
		$items     = self::apply_belongs_filter( array_map( [ self::class, 'build_option_item' ], (array) $rows ), $belongs_to );

		return self::paginate( $items, $page, $sort_by, $sort_dir, $per_page, $truncated, self::build_summary( 'options', $items ) );
	}

	/**
	 * @param array<string, mixed> $row
	 * @return array<string, mixed>
	 */
	private static function build_option_item( array $row ): array {
		$name      = (string) ( $row['option_name'] ?? '' );
		$size      = (int) ( $row['size_bytes'] ?? 0 );
		$auto      = in_array( (string) ( $row['autoload'] ?? '' ), [ 'yes', 'on', 'auto' ], true );
		$ownership = Item_Ownership::resolve( 'option', $name );

		return [
			'id'            => (string) (int) ( $row['option_id'] ?? 0 ),
			'name'          => $name,
			'size'          => Module::formatBytes( $size ),
			'autoload'      => $auto ? __( 'Yes', 'wp-powersuite' ) : __( 'No', 'wp-powersuite' ),
			'belongs_to'    => Item_Ownership::format_belongs_to( $ownership ),
			'owner'         => self::owner_payload( $ownership ),
			'value_preview' => self::preview_value( (string) ( $row['option_value'] ?? '' ) ),
			'protected'     => Protected_Items::is_protected_option( $name ),
			'viewable'      => true,
			'_ownership'    => $ownership,
			'_sort'         => [
				'name'       => $name,
				'size'       => $size,
				'autoload'   => $auto ? 1 : 0,
				'belongs_to' => (string) ( $ownership['label'] ?? '' ),
			],
		];
	}

	/**
	 * @param array<string, mixed> $args
	 * @return array<string, mixed>
	 */
	private static function list_post_meta( array $args ): array {
		return self::list_meta( 'post', $args );
	}

	/**
	 * @param array<string, mixed> $args
	 * @return array<string, mixed>
	 */
	private static function list_user_meta( array $args ): array {
		return self::list_meta( 'user', $args );
	}

	/**
	 * @param array<string, mixed> $args
	 * @return array<string, mixed>
	 */
	private static function list_comment_meta( array $args ): array {
		return self::list_meta( 'comment', $args );
	}

	/**
	 * @param array<string, mixed> $args
	 * @return array<string, mixed>
	 */
	private static function list_term_meta( array $args ): array {
		return self::list_meta( 'term', $args );
	}

	/**
	 * @param array<string, mixed> $args
	 * @return array<string, mixed>
	 */
	private static function list_meta( string $meta_type, array $args ): array {
		global $wpdb;

		$page     = max( 1, absint( $args['page'] ?? 1 ) );
		$search   = sanitize_text_field( (string) ( $args['search'] ?? '' ) );
		$filter      = sanitize_key( (string) ( $args['filter'] ?? 'all' ) );
		$belongs_to  = sanitize_key( (string) ( $args['belongs_to'] ?? 'all' ) );
		$sort_by     = sanitize_key( (string) ( $args['sort_by'] ?? 'size' ) );
		$sort_dir = strtoupper( (string) ( $args['sort_dir'] ?? 'DESC' ) ) === 'ASC' ? 'ASC' : 'DESC';

		if ( $meta_type === 'user' ) {
			$table       = $wpdb->usermeta;
			$id_col      = 'umeta_id';
			$parent_col  = 'user_id';
			$parent_join = "LEFT JOIN {$wpdb->users} u ON u.ID = m.user_id";
			// Exclude parent_id = 0 — some plugins store global settings rows that way.
			$orphan_sql  = 'u.ID IS NULL AND m.user_id > 0';
			$orphan_expr = '(u.ID IS NULL AND m.user_id > 0)';
			$dup_sql     = self::duplicated_meta_where( $table, $id_col, $parent_col, 'm' );
			$parent_key  = 'user_id';
			$detail_type = 'user_meta';
		} elseif ( $meta_type === 'comment' ) {
			$table       = $wpdb->commentmeta;
			$id_col      = 'meta_id';
			$parent_col  = 'comment_id';
			$parent_join = "LEFT JOIN {$wpdb->comments} c ON c.comment_ID = m.comment_id";
			$orphan_sql  = 'c.comment_ID IS NULL AND m.comment_id > 0';
			$orphan_expr = '(c.comment_ID IS NULL AND m.comment_id > 0)';
			$dup_sql     = self::duplicated_meta_where( $table, $id_col, $parent_col, 'm' );
			$parent_key  = 'comment_id';
			$detail_type = 'comment_meta';
		} elseif ( $meta_type === 'term' ) {
			$table       = $wpdb->termmeta;
			$id_col      = 'meta_id';
			$parent_col  = 'term_id';
			$parent_join = "LEFT JOIN {$wpdb->terms} t ON t.term_id = m.term_id";
			$orphan_sql  = 't.term_id IS NULL AND m.term_id > 0';
			$orphan_expr = '(t.term_id IS NULL AND m.term_id > 0)';
			$dup_sql     = self::duplicated_meta_where( $table, $id_col, $parent_col, 'm' );
			$parent_key  = 'term_id';
			$detail_type = 'term_meta';
		} else {
			$table       = $wpdb->postmeta;
			$id_col      = 'meta_id';
			$parent_col  = 'post_id';
			$parent_join = "LEFT JOIN {$wpdb->posts} p ON p.ID = m.post_id";
			$orphan_sql  = 'p.ID IS NULL AND m.post_id > 0';
			$orphan_expr = '(p.ID IS NULL AND m.post_id > 0)';
			$dup_sql     = self::duplicated_meta_where( $table, $id_col, $parent_col, 'm' );
			$parent_key  = 'post_id';
			$detail_type = 'post_meta';
		}

		$per_page = self::per_page_from_args( $args );
		$where    = '1=1';
		$params   = [];

		if ( $filter === 'orphaned' ) {
			$where = $orphan_sql;
		} elseif ( $filter === 'duplicated' ) {
			$where = $dup_sql;
		}

		if ( $search !== '' ) {
			$where   .= ' AND m.meta_key LIKE %s';
			$params[] = '%' . $wpdb->esc_like( $search ) . '%';
		}

		$select = "m.{$id_col} AS id, m.{$parent_col} AS parent_id, m.meta_key, m.meta_value, LENGTH(m.meta_value) + LENGTH(m.meta_key) AS size_bytes, {$orphan_expr} AS is_orphan";
		$from   = "FROM {$table} m {$parent_join} WHERE {$where}";

		$order_map = [
			'meta_key'  => 'm.meta_key',
			$parent_key => "m.{$parent_col}",
			'size'      => 'LENGTH(m.meta_value) + LENGTH(m.meta_key)',
		];

		$build = fn( array $row ): array => self::build_meta_item( $row, $parent_key, $detail_type, $meta_type );

		if ( self::belongs_all( $belongs_to ) && isset( $order_map[ $sort_by ] ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
			$total  = (int) $wpdb->get_var( self::q( "SELECT COUNT(*) {$from}", $params ) );
			$paging = self::page_bounds( $total, $page, $per_page );
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
			$rows   = $wpdb->get_results(
				self::q( "SELECT {$select} {$from} ORDER BY {$order_map[ $sort_by ]} {$sort_dir}, m.{$id_col} ASC LIMIT {$paging['limit']} OFFSET {$paging['offset']}", $params ),
				ARRAY_A
			);

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
			$agg = $wpdb->get_row(
				self::q( "SELECT COUNT(*) c, COALESCE(SUM(LENGTH(m.meta_value) + LENGTH(m.meta_key)),0) total_size, COALESCE(SUM({$orphan_expr}),0) orphaned {$from}", $params ),
				ARRAY_A
			);

			$summary = self::summary_chips(
				[
					[ __( 'Rows', 'wp-powersuite' ), number_format_i18n( (int) ( $agg['c'] ?? $total ) ) ],
					[ __( 'Total size', 'wp-powersuite' ), Module::formatBytes( (int) ( $agg['total_size'] ?? 0 ) ) ],
					[ __( 'Orphaned', 'wp-powersuite' ), number_format_i18n( (int) ( $agg['orphaned'] ?? 0 ) ) ],
				]
			);

			return self::paginate_prequeried( array_map( $build, (array) $rows ), $total, $paging['page'], $per_page, $summary );
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
		$rows      = $wpdb->get_results( self::q( "SELECT {$select} {$from} ORDER BY m.{$id_col} ASC LIMIT " . self::SCAN_CAP, $params ), ARRAY_A );
		$truncated = count( (array) $rows ) >= self::SCAN_CAP;
		$items     = self::apply_belongs_filter( array_map( $build, (array) $rows ), $belongs_to );

		return self::paginate( $items, $page, $sort_by, $sort_dir, $per_page, $truncated, self::build_summary( $detail_type, $items ) );
	}

	/**
	 * @param array<string, mixed> $row
	 * @return array<string, mixed>
	 */
	private static function build_meta_item( array $row, string $parent_key, string $detail_type, string $meta_type ): array {
		$meta_key  = (string) ( $row['meta_key'] ?? '' );
		$meta_id   = (string) (int) ( $row['id'] ?? 0 );
		$is_orphan = ! empty( $row['is_orphan'] );
		$ownership = Item_Ownership::resolve(
			$detail_type,
			$meta_id,
			[
				'meta_key' => $meta_key,
				'orphaned' => $is_orphan,
			]
		);

		return [
			'id'            => $meta_id,
			'meta_key'      => $meta_key,
			$parent_key     => (string) (int) ( $row['parent_id'] ?? 0 ),
			'size'          => Module::formatBytes( (int) ( $row['size_bytes'] ?? 0 ) ),
			'belongs_to'    => Item_Ownership::format_belongs_to( $ownership ),
			'owner'         => self::owner_payload( $ownership ),
			'value_preview' => self::preview_value( (string) ( $row['meta_value'] ?? '' ) ),
			'protected'     => Protected_Items::is_protected_meta_key( $meta_key, $meta_type ),
			'flag'          => $is_orphan ? 'orphan' : '',
			'viewable'      => true,
			'_ownership'    => $ownership,
			'_sort'         => [
				'meta_key'   => $meta_key,
				$parent_key  => (int) ( $row['parent_id'] ?? 0 ),
				'size'       => (int) ( $row['size_bytes'] ?? 0 ),
				'belongs_to' => (string) ( $ownership['label'] ?? '' ),
			],
		];
	}

	/**
	 * @param array<string, mixed> $args
	 * @return array<string, mixed>
	 */
	private static function list_transients( array $args ): array {
		global $wpdb;

		$page       = max( 1, absint( $args['page'] ?? 1 ) );
		$search     = sanitize_text_field( (string) ( $args['search'] ?? '' ) );
		$filter     = sanitize_key( (string) ( $args['filter'] ?? 'all' ) );
		$belongs_to = sanitize_key( (string) ( $args['belongs_to'] ?? 'all' ) );
		$sort_by    = sanitize_key( (string) ( $args['sort_by'] ?? 'size' ) );
		$sort_dir   = strtoupper( (string) ( $args['sort_dir'] ?? 'DESC' ) ) === 'ASC' ? 'ASC' : 'DESC';
		$per_page   = self::per_page_from_args( $args );
		$now        = time();

		// Join each transient to its timeout row in one query (no per-row get_option).
		$timeout_expr = "CAST(t.option_value AS UNSIGNED)";
		$expired_expr = "({$timeout_expr} > 0 AND {$timeout_expr} < {$now})";

		$where  = "o.option_name LIKE '\_transient\_%' OR o.option_name LIKE '\_site\_transient\_%'";
		$where  = "({$where}) AND o.option_name NOT LIKE '\_transient\_timeout\_%' AND o.option_name NOT LIKE '\_site\_transient\_timeout\_%'";
		$params = [];

		if ( $search !== '' ) {
			$where   .= ' AND o.option_name LIKE %s';
			$params[] = '%' . $wpdb->esc_like( $search ) . '%';
		}

		if ( $filter === 'expired' ) {
			$where .= " AND {$expired_expr}";
		} elseif ( $filter === 'active' ) {
			$where .= " AND NOT {$expired_expr}";
		} elseif ( $filter === 'big' ) {
			$where .= ' AND LENGTH(o.option_value) > 153600';
		}

		$select = "o.option_id, o.option_name, o.option_value, LENGTH(o.option_value) AS size_bytes, {$timeout_expr} AS timeout, {$expired_expr} AS is_expired";
		$from   = "FROM {$wpdb->options} o LEFT JOIN {$wpdb->options} t ON t.option_name = REPLACE(o.option_name, '_transient_', '_transient_timeout_') WHERE {$where}";

		$order_map = [ 'name' => 'o.option_name', 'size' => 'LENGTH(o.option_value)', 'expired' => $expired_expr, 'timeout' => $timeout_expr ];
		$build     = fn( array $row ): array => self::build_transient_item( $row );

		if ( self::belongs_all( $belongs_to ) && isset( $order_map[ $sort_by ] ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
			$total  = (int) $wpdb->get_var( self::q( "SELECT COUNT(*) {$from}", $params ) );
			$paging = self::page_bounds( $total, $page, $per_page );
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
			$rows   = $wpdb->get_results(
				self::q( "SELECT {$select} {$from} ORDER BY {$order_map[ $sort_by ]} {$sort_dir}, o.option_id ASC LIMIT {$paging['limit']} OFFSET {$paging['offset']}", $params ),
				ARRAY_A
			);

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
			$agg = $wpdb->get_row(
				self::q(
					"SELECT COUNT(*) c, COALESCE(SUM(LENGTH(o.option_value)),0) total_size,
					COALESCE(SUM({$expired_expr}),0) expired_count,
					COALESCE(SUM(CASE WHEN {$expired_expr} THEN LENGTH(o.option_value) ELSE 0 END),0) expired_size {$from}",
					$params
				),
				ARRAY_A
			);

			$summary = self::summary_chips(
				[
					[ __( 'Transients', 'wp-powersuite' ), number_format_i18n( (int) ( $agg['c'] ?? $total ) ) ],
					[ __( 'Total size', 'wp-powersuite' ), Module::formatBytes( (int) ( $agg['total_size'] ?? 0 ) ) ],
					[ __( 'Expired', 'wp-powersuite' ), number_format_i18n( (int) ( $agg['expired_count'] ?? 0 ) ) ],
					[ __( 'Expired size', 'wp-powersuite' ), Module::formatBytes( (int) ( $agg['expired_size'] ?? 0 ) ) ],
				]
			);

			return self::paginate_prequeried( array_map( $build, (array) $rows ), $total, $paging['page'], $per_page, $summary );
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
		$rows      = $wpdb->get_results( self::q( "SELECT {$select} {$from} ORDER BY o.option_id ASC LIMIT " . self::SCAN_CAP, $params ), ARRAY_A );
		$truncated = count( (array) $rows ) >= self::SCAN_CAP;
		$items     = self::apply_belongs_filter( array_map( $build, (array) $rows ), $belongs_to );

		return self::paginate( $items, $page, $sort_by, $sort_dir, $per_page, $truncated, self::build_summary( 'transients', $items ) );
	}

	/**
	 * @param array<string, mixed> $row
	 * @return array<string, mixed>
	 */
	private static function build_transient_item( array $row ): array {
		$full_name = (string) ( $row['option_name'] ?? '' );
		$name      = preg_replace( '/^_site_transient_/', '', preg_replace( '/^_transient_/', '', $full_name ) ?? $full_name ) ?? $full_name;
		$size      = (int) ( $row['size_bytes'] ?? 0 );
		$timeout   = (int) ( $row['timeout'] ?? 0 );
		$expired   = ! empty( $row['is_expired'] );
		$ownership = Item_Ownership::resolve( 'transient', $name );

		return [
			'id'            => $name,
			'name'          => $name,
			'size'          => Module::formatBytes( $size ),
			'expired'       => $expired ? __( 'Expired', 'wp-powersuite' ) : __( 'Active', 'wp-powersuite' ),
			'timeout'       => $timeout > 0 ? wp_date( get_option( 'date_format' ) . ' ' . get_option( 'time_format' ), $timeout ) : '—',
			'belongs_to'    => Item_Ownership::format_belongs_to( $ownership ),
			'owner'         => self::owner_payload( $ownership ),
			'value_preview' => self::preview_value( (string) ( $row['option_value'] ?? '' ) ),
			'protected'     => Protected_Items::is_protected_transient( $name ),
			'flag'          => $expired ? 'expired' : '',
			'viewable'      => true,
			'_ownership'    => $ownership,
			'_sort'         => [
				'name'       => $name,
				'size'       => $size,
				'expired'    => $expired ? 1 : 0,
				'timeout'    => $timeout,
				'belongs_to' => (string) ( $ownership['label'] ?? '' ),
			],
		];
	}

	/**
	 * @param array<string, mixed> $args
	 * @return array<string, mixed>
	 */
	private static function list_cron( array $args ): array {
		$page       = max( 1, absint( $args['page'] ?? 1 ) );
		$search     = sanitize_text_field( (string) ( $args['search'] ?? '' ) );
		$filter     = sanitize_key( (string) ( $args['filter'] ?? 'all' ) );
		$belongs_to = sanitize_key( (string) ( $args['belongs_to'] ?? 'all' ) );
		$sort_by    = sanitize_key( (string) ( $args['sort_by'] ?? 'next_run' ) );
		$sort_dir   = strtoupper( (string) ( $args['sort_dir'] ?? 'ASC' ) ) === 'DESC' ? 'DESC' : 'ASC';

		$cron  = _get_cron_array();
		$items = [];

		if ( is_array( $cron ) ) {
			foreach ( $cron as $timestamp => $hooks ) {
				if ( ! is_array( $hooks ) ) {
					continue;
				}
				foreach ( $hooks as $hook => $events ) {
					if ( ! is_array( $events ) ) {
						continue;
					}
					if ( $search !== '' && stripos( (string) $hook, $search ) === false ) {
						continue;
					}
					$hook_str   = (string) $hook;
					$is_orphan  = Item_Ownership::is_cron_hook_orphan( $hook_str );
					if ( $filter === 'orphan' && ! $is_orphan ) {
						continue;
					}
					$ownership  = Item_Ownership::resolve(
						'cron',
						$hook_str,
						[
							'hook'     => $hook_str,
							'orphaned' => $is_orphan,
						]
					);
					foreach ( $events as $hash => $event ) {
						if ( ! is_array( $event ) ) {
							continue;
						}
						$schedule = isset( $event['schedule'] ) ? (string) $event['schedule'] : __( 'Single event', 'wp-powersuite' );
						$args_raw = $event['args'] ?? [];
						$items[]  = [
							'id'           => md5( $hook_str . '|' . (string) $timestamp . '|' . (string) $hash ),
							'hook'         => $hook_str,
							'schedule'     => $schedule,
							'next_run'     => wp_date( get_option( 'date_format' ) . ' ' . get_option( 'time_format' ), (int) $timestamp ),
							'orphan'       => $is_orphan ? __( 'Orphan', 'wp-powersuite' ) : __( 'Registered', 'wp-powersuite' ),
							'belongs_to'   => Item_Ownership::format_belongs_to( $ownership ),
							'owner'        => self::owner_payload( $ownership ),
							'args_preview' => self::preview_value( wp_json_encode( $args_raw ) ?: '[]' ),
							'flag'         => $is_orphan ? 'orphan' : '',
							'viewable'     => true,
							'_ownership'   => $ownership,
							'_event'       => [
								'hook'      => $hook_str,
								'timestamp' => (int) $timestamp,
								'hash'      => (string) $hash,
								'args'      => is_array( $args_raw ) ? $args_raw : [],
							],
							'_sort'        => [
								'hook'       => $hook_str,
								'schedule'   => $schedule,
								'next_run'   => (int) $timestamp,
								'orphan'     => $is_orphan ? 1 : 0,
								'belongs_to' => (string) ( $ownership['label'] ?? '' ),
							],
						];
					}
				}
			}
		}

		$filtered = self::apply_belongs_filter( $items, $belongs_to );

		return self::paginate( $filtered, $page, $sort_by, $sort_dir, self::per_page_from_args( $args ), false, self::build_summary( 'cron', $filtered ) );
	}

	/**
	 * @param array<string, mixed> $args
	 * @return array<string, mixed>
	 */
	private static function list_post_types( array $args ): array {
		$page       = max( 1, absint( $args['page'] ?? 1 ) );
		$search     = sanitize_text_field( (string) ( $args['search'] ?? '' ) );
		$filter     = sanitize_key( (string) ( $args['filter'] ?? 'all' ) );
		$belongs_to = sanitize_key( (string) ( $args['belongs_to'] ?? 'all' ) );
		$sort_by    = sanitize_key( (string) ( $args['sort_by'] ?? 'count' ) );
		$sort_dir = strtoupper( (string) ( $args['sort_dir'] ?? 'DESC' ) ) === 'ASC' ? 'ASC' : 'DESC';

		$types = get_post_types( [], 'objects' );
		$items = [];

		foreach ( $types as $type => $object ) {
			if ( $search !== '' && stripos( $type, $search ) === false && stripos( (string) ( $object->labels->name ?? '' ), $search ) === false ) {
				continue;
			}

			$counts = wp_count_posts( $type );
			$count  = (int) ( $counts->publish ?? 0 )
				+ (int) ( $counts->draft ?? 0 )
				+ (int) ( $counts->pending ?? 0 )
				+ (int) ( $counts->future ?? 0 )
				+ (int) ( $counts->private ?? 0 )
				+ (int) ( $counts->trash ?? 0 );

			if ( $filter === 'non_public' && ! empty( $object->public ) ) {
				continue;
			}
			if ( $filter === 'empty' && $count > 0 ) {
				continue;
			}

			$ownership = Item_Ownership::resolve( 'post_type', $type );
			$protected = ! Protected_Items::is_bulk_post_type_delete_allowed( $type );

			$items[] = [
				'id'         => $type,
				'name'       => $type,
				'label'      => (string) ( $object->labels->name ?? $type ),
				'count'      => number_format_i18n( $count ),
				'public'     => ! empty( $object->public ) ? __( 'Yes', 'wp-powersuite' ) : __( 'No', 'wp-powersuite' ),
				'belongs_to' => Item_Ownership::format_belongs_to( $ownership ),
				'owner'      => self::owner_payload( $ownership ),
				'protected'  => $protected,
				'viewable'   => true,
				'_ownership' => $ownership,
				'_sort'      => [
					'name'       => $type,
					'label'      => (string) ( $object->labels->name ?? $type ),
					'count'      => $count,
					'belongs_to' => (string) ( $ownership['label'] ?? '' ),
				],
			];
		}

		$filtered = self::apply_belongs_filter( $items, $belongs_to );

		return self::paginate( $filtered, $page, $sort_by, $sort_dir, self::per_page_from_args( $args ), false, self::build_summary( 'post_types', $filtered ) );
	}

	/**
	 * @param list<string> $items
	 * @param array<string, mixed> $args
	 * @return array<string, mixed>
	 */
	private static function action_tables( string $action, array $items, array $args ): array {
		global $wpdb;

		$settings            = is_array( $args['settings'] ?? null ) ? $args['settings'] : [];
		$maintenance_allowed = ! empty( $settings['optimize_tables'] );
		$affected            = 0;
		$skipped             = 0;

		if ( $action === 'empty' ) {
			return [
				'affected' => 0,
				'message'  => __( 'Truncate (empty) table action is disabled for safety.', 'wp-powersuite' ),
			];
		}

		if ( ! in_array( $action, [ 'optimize', 'repair', 'convert_innodb', 'drop' ], true ) ) {
			return [
				'affected' => 0,
				'message'  => __( 'Unsupported table action.', 'wp-powersuite' ),
			];
		}

		if ( in_array( $action, [ 'optimize', 'repair', 'convert_innodb' ], true ) && ! $maintenance_allowed ) {
			return [
				'affected' => 0,
				'message'  => __( 'Enable table optimization in Database Cleanup settings before running maintenance actions.', 'wp-powersuite' ),
			];
		}

		$prefix = (string) $wpdb->prefix;

		foreach ( $items as $table ) {
			$table = (string) $table;

			if ( ! preg_match( '/^[a-zA-Z0-9_]+$/', $table ) || ! Protected_Items::is_table_maintenance_allowed( $table ) ) {
				++$skipped;
				continue;
			}

			if ( $action === 'drop' ) {
				// Only allow dropping current-site, non-core, non-recognized-plugin tables
				// whose owner plugin/theme is not installed (true orphans).
				if ( $prefix !== '' && ! str_starts_with( $table, $prefix ) ) {
					++$skipped;
					continue;
				}

				if ( ! Protected_Items::is_destructive_table_action_allowed( $table, 'drop' ) ) {
					++$skipped;
					continue;
				}

				$ownership = Item_Ownership::resolve( 'table', $table );
				if ( ! Item_Ownership::is_deletable_orphan( $ownership ) ) {
					++$skipped;
					continue;
				}

				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$wpdb->query( "DROP TABLE IF EXISTS `{$table}`" );
				++$affected;
				continue;
			}

			if ( Protected_Items::is_protected_plugin_table( $table ) && ! Protected_Items::is_core_table( $table ) ) {
				++$skipped;
				continue;
			}

			if ( $action === 'optimize' ) {
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$wpdb->query( "OPTIMIZE TABLE `{$table}`" );
				++$affected;
			} elseif ( $action === 'repair' ) {
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$wpdb->query( "REPAIR TABLE `{$table}`" );
				++$affected;
			} elseif ( $action === 'convert_innodb' ) {
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$wpdb->query( "ALTER TABLE `{$table}` ENGINE=InnoDB" );
				++$affected;
			}
		}

		$message = $action === 'drop'
			? sprintf(
				/* translators: %d: number of tables deleted */
				_n( '%d table deleted.', '%d tables deleted.', $affected, 'wp-powersuite' ),
				$affected
			)
			: sprintf(
				/* translators: %d: number of tables affected */
				_n( '%d table processed.', '%d tables processed.', $affected, 'wp-powersuite' ),
				$affected
			);

		if ( $skipped > 0 ) {
			$message .= ' ' . sprintf(
				/* translators: %d: number of skipped tables */
				_n( '%d table skipped (protected).', '%d tables skipped (protected).', $skipped, 'wp-powersuite' ),
				$skipped
			);
		}

		return [
			'affected' => $affected,
			'message'  => $message,
		];
	}

	/**
	 * @param list<string> $items
	 * @param array<string, mixed> $args
	 * @return array<string, mixed>
	 */
	private static function action_options( string $action, array $items, array $args ): array {
		global $wpdb;

		unset( $args );
		$affected = 0;
		$skipped  = 0;

		foreach ( $items as $item ) {
			if ( is_numeric( $item ) ) {
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$row = $wpdb->get_row( $wpdb->prepare( "SELECT option_name FROM {$wpdb->options} WHERE option_id = %d", (int) $item ), ARRAY_A );
				$name = is_array( $row ) ? (string) ( $row['option_name'] ?? '' ) : '';
			} else {
				$name = (string) $item;
			}

			if ( $name === '' || Protected_Items::is_protected_option( $name ) ) {
				++$skipped;
				continue;
			}

			if ( $action === 'delete' ) {
				$ownership = Item_Ownership::resolve( 'option', $name );
				$type      = (string) ( $ownership['type'] ?? '' );
				$status    = (string) ( $ownership['status'] ?? '' );

				// Never delete options belonging to an installed (active or inactive) plugin/theme.
				if ( in_array( $type, [ 'plugin', 'theme' ], true ) && $status !== 'not_installed' ) {
					++$skipped;
					continue;
				}

				if ( delete_option( $name ) ) {
					++$affected;
				}
			} elseif ( $action === 'autoload_yes' ) {
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$wpdb->update( $wpdb->options, [ 'autoload' => 'yes' ], [ 'option_name' => $name ], [ '%s' ], [ '%s' ] );
				++$affected;
			} elseif ( $action === 'autoload_no' ) {
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$wpdb->update( $wpdb->options, [ 'autoload' => 'no' ], [ 'option_name' => $name ], [ '%s' ], [ '%s' ] );
				++$affected;
			}
		}

		$message = sprintf(
			_n( '%d option updated.', '%d options updated.', $affected, 'wp-powersuite' ),
			$affected
		);

		if ( $skipped > 0 && $action === 'delete' ) {
			$message .= ' ' . sprintf(
				_n( '%d protected option skipped.', '%d protected options skipped.', $skipped, 'wp-powersuite' ),
				$skipped
			);
		}

		return [
			'affected' => $affected,
			'message'  => $message,
		];
	}

	/**
	 * @param list<string> $items
	 * @return array<string, mixed>
	 */
	private static function action_meta_ids( string $meta_type, string $action, array $items ): array {
		global $wpdb;

		$affected = 0;
		$skipped  = 0;

		if ( $action !== 'delete' ) {
			return [ 'affected' => 0, 'message' => __( 'Unsupported action.', 'wp-powersuite' ) ];
		}

		$table_map = [
			'post'    => [ $wpdb->postmeta, 'meta_id' ],
			'user'    => [ $wpdb->usermeta, 'umeta_id' ],
			'comment' => [ $wpdb->commentmeta, 'meta_id' ],
			'term'    => [ $wpdb->termmeta, 'meta_id' ],
		];

		if ( ! isset( $table_map[ $meta_type ] ) ) {
			return [ 'affected' => 0, 'message' => __( 'Unsupported meta type.', 'wp-powersuite' ) ];
		}

		[ $table, $id_col ] = $table_map[ $meta_type ];

		foreach ( $items as $id ) {
			$meta_id = (int) $id;
			if ( $meta_id <= 0 ) {
				continue;
			}

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$row = $wpdb->get_row( $wpdb->prepare( "SELECT meta_key FROM {$table} WHERE {$id_col} = %d", $meta_id ), ARRAY_A );
			$key = is_array( $row ) ? (string) ( $row['meta_key'] ?? '' ) : '';

			if ( $key === '' || Protected_Items::is_protected_meta_key( $key, $meta_type ) ) {
				++$skipped;
				continue;
			}

			if ( delete_metadata_by_mid( $meta_type, $meta_id ) ) {
				++$affected;
			}
		}

		$message = sprintf(
			_n( '%d meta row deleted.', '%d meta rows deleted.', $affected, 'wp-powersuite' ),
			$affected
		);

		if ( $skipped > 0 ) {
			$message .= ' ' . sprintf(
				_n( '%d protected meta row skipped.', '%d protected meta rows skipped.', $skipped, 'wp-powersuite' ),
				$skipped
			);
		}

		return [
			'affected' => $affected,
			'message'  => $message,
		];
	}

	/**
	 * @param list<string> $items
	 * @return array<string, mixed>
	 */
	private static function action_transients( string $action, array $items ): array {
		$affected = 0;

		if ( $action !== 'delete' ) {
			return [ 'affected' => 0, 'message' => __( 'Unsupported action.', 'wp-powersuite' ) ];
		}

		foreach ( $items as $name ) {
			$name = (string) $name;
			if ( $name === '' || Protected_Items::is_protected_transient( $name ) ) {
				continue;
			}
			if ( delete_transient( $name ) || delete_site_transient( $name ) ) {
				++$affected;
			}
		}

		return [
			'affected' => $affected,
			'message'  => sprintf(
				_n( '%d transient deleted.', '%d transients deleted.', $affected, 'wp-powersuite' ),
				$affected
			),
		];
	}

	/**
	 * @param list<string> $items
	 * @return array<string, mixed>
	 */
	private static function action_cron( string $action, array $items ): array {
		$affected = 0;

		if ( $action !== 'delete' ) {
			return [ 'affected' => 0, 'message' => __( 'Unsupported action.', 'wp-powersuite' ) ];
		}

		$map = [];
		foreach ( $items as $encoded ) {
			$data = json_decode( base64_decode( (string) $encoded, true ) ?: '', true );
			if ( ! is_array( $data ) ) {
				continue;
			}
			$map[] = $data;
		}

		foreach ( $map as $event ) {
			$hook = (string) ( $event['hook'] ?? '' );
			$ts   = (int) ( $event['timestamp'] ?? 0 );
			$args = is_array( $event['args'] ?? null ) ? $event['args'] : [];

			if ( $hook === '' || $ts <= 0 || Protected_Items::is_protected_cron_hook( $hook ) ) {
				continue;
			}

			// Refuse to delete cron events that still belong to an installed plugin/theme.
			$ownership = Item_Ownership::resolve( 'cron', $hook, [ 'hook' => $hook ] );
			$type      = (string) ( $ownership['type'] ?? '' );
			$status    = (string) ( $ownership['status'] ?? '' );
			if ( in_array( $type, [ 'plugin', 'theme', 'wordpress' ], true ) && $status !== 'not_installed' ) {
				continue;
			}

			if ( ! self::cron_event_exists( $hook, $ts, $args ) ) {
				continue;
			}

			if ( wp_unschedule_event( $ts, $hook, $args ) !== false ) {
				++$affected;
			}
		}

		return [
			'affected' => $affected,
			'message'  => sprintf(
				_n( '%d cron event removed.', '%d cron events removed.', $affected, 'wp-powersuite' ),
				$affected
			),
		];
	}

	/**
	 * @param list<string> $items
	 * @param array<string, mixed> $args
	 * @return array<string, mixed>
	 */
	private static function action_post_types( string $action, array $items, array $args ): array {
		$settings  = is_array( $args['settings'] ?? null ) ? $args['settings'] : [];
		$excluded  = self::browser_excluded_post_types( $settings );
		$affected  = 0;
		$remaining = 0;
		$skipped   = 0;

		if ( $action !== 'delete_posts' ) {
			return [ 'affected' => 0, 'message' => __( 'Unsupported action.', 'wp-powersuite' ) ];
		}

		foreach ( $items as $type ) {
			$type = sanitize_key( (string) $type );

			if (
				$type === ''
				|| ! Protected_Items::is_bulk_post_type_delete_allowed( $type )
				|| in_array( $type, $excluded, true )
			) {
				++$skipped;
				continue;
			}

			$post_ids = get_posts(
				[
					'post_type'              => $type,
					'post_status'            => 'any',
					'fields'                 => 'ids',
					'posts_per_page'         => 200,
					'no_found_rows'          => false,
					'update_post_meta_cache' => false,
					'update_post_term_cache' => false,
				]
			);

			foreach ( $post_ids as $post_id ) {
				if ( wp_delete_post( (int) $post_id, true ) ) {
					++$affected;
				}
			}

			$count_query = new \WP_Query(
				[
					'post_type'              => $type,
					'post_status'            => 'any',
					'fields'                 => 'ids',
					'posts_per_page'         => 1,
					'no_found_rows'          => false,
					'update_post_meta_cache' => false,
					'update_post_term_cache' => false,
				]
			);
			$remaining += (int) $count_query->found_posts;
		}

		$message = sprintf(
			/* translators: %d: number of posts deleted */
			_n( '%d post deleted.', '%d posts deleted.', $affected, 'wp-powersuite' ),
			$affected
		);

		if ( $remaining > 0 ) {
			$message .= ' ' . sprintf(
				/* translators: %d: remaining posts */
				__( '%d posts remain — run again to continue.', 'wp-powersuite' ),
				$remaining
			);
		}

		if ( $skipped > 0 ) {
			$message .= ' ' . sprintf(
				/* translators: %d: skipped post types */
				_n( '%d protected post type skipped.', '%d protected post types skipped.', $skipped, 'wp-powersuite' ),
				$skipped
			);
		}

		return [
			'affected'  => $affected,
			'remaining' => $remaining,
			'message'   => $message,
		];
	}

	/**
	 * @param list<array<string, mixed>> $items
	 * @return array<string, mixed>
	 */
	private static function paginate( array $items, int $page, string $sort_by, string $sort_dir, int $per_page, bool $truncated = false, array $summary = [] ): array {
		usort(
			$items,
			static function ( array $a, array $b ) use ( $sort_by, $sort_dir ): int {
				$av = $a['_sort'][ $sort_by ] ?? '';
				$bv = $b['_sort'][ $sort_by ] ?? '';
				$cmp = is_numeric( $av ) && is_numeric( $bv ) ? (float) $av <=> (float) $bv : strcasecmp( (string) $av, (string) $bv );

				return $sort_dir === 'DESC' ? -$cmp : $cmp;
			}
		);

		$per_page    = self::sanitize_per_page( $per_page );
		$total       = count( $items );
		$total_pages = max( 1, (int) ceil( $total / $per_page ) );
		$page        = min( $page, $total_pages );
		$offset      = ( $page - 1 ) * $per_page;
		$slice       = array_slice( $items, $offset, $per_page );

		foreach ( $slice as &$item ) {
			unset( $item['_sort'], $item['_ownership'] );
			if ( isset( $item['_event'] ) && is_array( $item['_event'] ) ) {
				$item['id'] = base64_encode( wp_json_encode( $item['_event'] ) ?: '' );
				unset( $item['_event'] );
			}
		}
		unset( $item );

		return [
			'items'       => $slice,
			'total'       => $total,
			'page'        => $page,
			'per_page'    => $per_page,
			'total_pages' => $total_pages,
			'truncated'   => $truncated,
			'summary'     => $summary,
		];
	}

	/**
	 * Build summary stat chips for a browser tab from the fully filtered item set.
	 *
	 * @param list<array<string, mixed>> $items
	 * @return list<array{label:string,value:string}>
	 */
	private static function build_summary( string $type, array $items ): array {
		$count = count( $items );
		$size  = 0;

		foreach ( $items as $item ) {
			$size += (int) ( $item['_sort']['size'] ?? 0 );
		}

		switch ( $type ) {
			case 'tables':
				$overhead = 0;
				$orphan   = 0;
				foreach ( $items as $item ) {
					$overhead += (int) ( $item['_sort']['overhead'] ?? 0 );
					if ( ( $item['flag'] ?? '' ) === 'orphan' ) {
						++$orphan;
					}
				}

				return self::summary_chips(
					[
						[ __( 'Tables', 'wp-powersuite' ), number_format_i18n( $count ) ],
						[ __( 'Total size', 'wp-powersuite' ), Module::formatBytes( $size ) ],
						[ __( 'Overhead', 'wp-powersuite' ), Module::formatBytes( $overhead ) ],
						[ __( 'Orphan tables', 'wp-powersuite' ), number_format_i18n( $orphan ) ],
					]
				);

			case 'options':
				$auto_count = 0;
				$auto_size  = 0;
				foreach ( $items as $item ) {
					if ( (int) ( $item['_sort']['autoload'] ?? 0 ) === 1 ) {
						++$auto_count;
						$auto_size += (int) ( $item['_sort']['size'] ?? 0 );
					}
				}

				return self::summary_chips(
					[
						[ __( 'Options', 'wp-powersuite' ), number_format_i18n( $count ) ],
						[ __( 'Total size', 'wp-powersuite' ), Module::formatBytes( $size ) ],
						[ __( 'Autoloaded', 'wp-powersuite' ), number_format_i18n( $auto_count ) ],
						[ __( 'Autoload size', 'wp-powersuite' ), Module::formatBytes( $auto_size ) ],
					]
				);

			case 'post_meta':
			case 'user_meta':
			case 'comment_meta':
			case 'term_meta':
				$orphaned = 0;
				foreach ( $items as $item ) {
					if ( ( $item['flag'] ?? '' ) === 'orphan' ) {
						++$orphaned;
					}
				}

				return self::summary_chips(
					[
						[ __( 'Rows', 'wp-powersuite' ), number_format_i18n( $count ) ],
						[ __( 'Total size', 'wp-powersuite' ), Module::formatBytes( $size ) ],
						[ __( 'Orphaned', 'wp-powersuite' ), number_format_i18n( $orphaned ) ],
					]
				);

			case 'transients':
				$expired_count = 0;
				$expired_size  = 0;
				foreach ( $items as $item ) {
					if ( ( $item['flag'] ?? '' ) === 'expired' ) {
						++$expired_count;
						$expired_size += (int) ( $item['_sort']['size'] ?? 0 );
					}
				}

				return self::summary_chips(
					[
						[ __( 'Transients', 'wp-powersuite' ), number_format_i18n( $count ) ],
						[ __( 'Total size', 'wp-powersuite' ), Module::formatBytes( $size ) ],
						[ __( 'Expired', 'wp-powersuite' ), number_format_i18n( $expired_count ) ],
						[ __( 'Expired size', 'wp-powersuite' ), Module::formatBytes( $expired_size ) ],
					]
				);

			case 'cron':
				$orphan = 0;
				foreach ( $items as $item ) {
					if ( ( $item['flag'] ?? '' ) === 'orphan' ) {
						++$orphan;
					}
				}

				return self::summary_chips(
					[
						[ __( 'Events', 'wp-powersuite' ), number_format_i18n( $count ) ],
						[ __( 'Orphan hooks', 'wp-powersuite' ), number_format_i18n( $orphan ) ],
					]
				);

			case 'post_types':
				$posts = 0;
				foreach ( $items as $item ) {
					$posts += (int) ( $item['_sort']['count'] ?? 0 );
				}

				return self::summary_chips(
					[
						[ __( 'Post types', 'wp-powersuite' ), number_format_i18n( $count ) ],
						[ __( 'Total posts', 'wp-powersuite' ), number_format_i18n( $posts ) ],
					]
				);

			default:
				return [];
		}
	}

	/**
	 * @param list<array{0:string,1:string}> $pairs
	 * @return list<array{label:string,value:string}>
	 */
	private static function summary_chips( array $pairs ): array {
		$chips = [];

		foreach ( $pairs as $pair ) {
			$chips[] = [
				'label' => (string) ( $pair[0] ?? '' ),
				'value' => (string) ( $pair[1] ?? '' ),
			];
		}

		return $chips;
	}

	/**
	 * @return array<string, mixed>
	 */
	private static function empty_page( int $page, int $per_page = self::DEFAULT_PER_PAGE ): array {
		return [
			'items'       => [],
			'total'       => 0,
			'page'        => $page,
			'per_page'    => self::sanitize_per_page( $per_page ),
			'total_pages' => 0,
		];
	}

	private static function preview_value( string $value ): string {
		$value = trim( $value );
		if ( $value === '' ) {
			return '—';
		}
		if ( is_serialized( $value ) ) {
			return '[serialized] ' . substr( $value, 0, 40 ) . ( strlen( $value ) > 40 ? '…' : '' );
		}
		if ( strlen( $value ) > 60 ) {
			return substr( $value, 0, 60 ) . '…';
		}

		return $value;
	}

	private static function duplicated_meta_where( string $table, string $pk, string $parent_col, string $alias ): string {
		return "EXISTS (
			SELECT 1 FROM (
				SELECT {$parent_col} AS parent_id, meta_key, MD5(meta_value) AS vhash, MIN({$pk}) AS min_pk, COUNT(*) AS cnt
				FROM {$table}
				GROUP BY {$parent_col}, meta_key, MD5(meta_value)
				HAVING cnt > 1
			) dupg
			WHERE dupg.parent_id = {$alias}.{$parent_col}
				AND dupg.meta_key = {$alias}.meta_key
				AND dupg.vhash = MD5({$alias}.meta_value)
				AND {$alias}.{$pk} > dupg.min_pk
		)";
	}

	/**
	 * @param array<string, mixed> $settings
	 * @return list<string>
	 */
	private static function browser_excluded_post_types( array $settings ): array {
		$excluded = Protected_Items::protected_post_types();

		if ( ! empty( $settings['excluded_post_types'] ) && is_array( $settings['excluded_post_types'] ) ) {
			foreach ( $settings['excluded_post_types'] as $type ) {
				$key = sanitize_key( (string) $type );
				if ( $key !== '' ) {
					$excluded[] = $key;
				}
			}
		}

		return array_values( array_unique( $excluded ) );
	}

	/**
	 * @param array<int, mixed> $args
	 */
	private static function cron_event_exists( string $hook, int $timestamp, array $args ): bool {
		$cron = _get_cron_array();

		if ( ! is_array( $cron ) || ! isset( $cron[ $timestamp ][ $hook ] ) ) {
			return false;
		}

		foreach ( (array) $cron[ $timestamp ][ $hook ] as $event ) {
			if ( ! is_array( $event ) ) {
				continue;
			}

			$event_args = is_array( $event['args'] ?? null ) ? $event['args'] : [];

			if ( $event_args === $args ) {
				return true;
			}
		}

		return false;
	}

	/**
	 * @return array<string, mixed>
	 */
	private static function belongs_filter_group(): array {
		return [
			'key'     => 'belongs_to',
			'label'   => __( 'Belongs to', 'wp-powersuite' ),
			'options' => Item_Ownership::belongs_to_filter_options(),
		];
	}

	/**
	 * @param list<array<string, mixed>> $items
	 * @return list<array<string, mixed>>
	 */
	private static function apply_belongs_filter( array $items, string $filter ): array {
		if ( $filter === '' || $filter === 'all' ) {
			return $items;
		}

		return array_values(
			array_filter(
				$items,
				static function ( array $item ) use ( $filter ): bool {
					$ownership = is_array( $item['_ownership'] ?? null ) ? $item['_ownership'] : [];

					return Item_Ownership::matches_belongs_filter( $ownership, $filter );
				}
			)
		);
	}
}
