<?php
/**
 * Optimizer tab, DB orphaned table cleanup, plugin stack scanner, DB intelligence, and AI debug log.
 *
 * @package CSDT_DevTools
 */

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

/**
 * Optimizer tab: orphaned-table cleanup, plugin-stack redundancy scanner,
 * DB intelligence, and the AI debug log.
 *
 * @since 1.0.0
 */
class CSDT_Optimizer {

    /**
     * Table-name suffixes (after $wpdb->prefix) that belong to a plugin that is running right now.
     *
     * WHY THIS IS NOT A GUESS FROM THE NAME. Until 2026-09-30 the orphan scan reported 11 LIVE tables
     * on production as orphaned: all seven of CloudScale Site Analytics' (~500,000 rows) and four of
     * this plugin's own. The only entry here was keyed on 'cloudscale-wordpress-free-analytics', a
     * slug that plugin has not used in months, and nothing at all excluded csdt_, so the scanner
     * listed its own firewall and activity logs as abandoned. The same list gates the archive
     * handler below, so a live table cannot be renamed into the recycle bin even by a direct request.
     *
     * @return string[]
     */
    public static function active_owned_table_suffixes(): array {
        // This code is running, so its own tables are in use by definition.
        $owned = array( 'csdt_' );
        // Keyed on the folder name. Both analytics slugs, because the old one is still what an
        // install that never re-downloaded the plugin carries.
        $slug_suffix_map = array(
            // cspv_analytics_ since Site Analytics 2.9.516; cs_analytics_ before it, kept so a
            // site still on an older copy is not told its live tables are orphans.
            'cloudscale-site-analytics'           => array( 'cspv_analytics_', 'cs_analytics_' ),
            'cloudscale-wordpress-free-analytics' => array( 'cspv_analytics_', 'cs_analytics_' ),
        );
        foreach ( (array) get_option( 'active_plugins', array() ) as $plugin_file ) {
            $slug = dirname( (string) $plugin_file );
            if ( isset( $slug_suffix_map[ $slug ] ) ) {
                $owned = array_merge( $owned, $slug_suffix_map[ $slug ] );
            }
        }
        return array_values( array_unique( $owned ) );
    }

    public static function ajax_db_orphaned_scan(): void {
        check_ajax_referer( CSDT_DevTools::OPTIMIZER_NONCE, 'nonce' );
        if ( ! current_user_can( 'manage_options' ) ) {
            wp_send_json_error( 'Unauthorized', 403 );
        }

        global $wpdb;
        $prefix     = $wpdb->prefix;
        $prefix_len = strlen( $prefix );
        $core       = CSDT_Site_Audit::core_table_suffixes();

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $all_tables = $wpdb->get_col( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $prefix ) . '%' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- optimizer custom table operations; WP_Query not applicable

        // Tables owned by a running plugin are never orphans. See active_owned_table_suffixes().
        $active_plugin_suffixes = self::active_owned_table_suffixes();

        $non_core = array_values(
            array_filter(
                $all_tables,
                function ( $t ) use ( $prefix_len, $core, $active_plugin_suffixes ) {
                    $suffix = substr( $t, $prefix_len );
                    if ( in_array( $suffix, $core, true ) ) {
                        return false;
                    }
                    foreach ( $active_plugin_suffixes as $sfx ) {
                        if ( str_starts_with( $suffix, $sfx ) ) {
                            return false;
                        }
                    }
                    return true;
                }
            )
        );

        if ( empty( $non_core ) ) {
            wp_send_json_success( array( 'tables' => array() ) );
            return;
        }

        // Fetch row counts and sizes from information_schema
        // Table names never contain a comma, so the list is ONE %s value for FIND_IN_SET() and
        // the statement is a literal; no placeholder list is assembled into it.
        $rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- optimizer custom table operations; WP_Query not applicable
            $wpdb->prepare(
                // `rows` BACKTICKED. It is a reserved word in MariaDB 10.2+ (window frames), so the
                // bare alias was a syntax error, get_results() returned nothing, and every table in
                // the scan showed 0 rows and 0 KB, including ~500,000 rows of live analytics, which
                // is exactly the figure that makes "Drop Selected" look harmless.
                "SELECT TABLE_NAME as name, TABLE_ROWS as `rows`,
                        ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024) as size_kb,
                        TABLE_TYPE as table_type,
                        DATE_FORMAT(CREATE_TIME, '%%Y-%%m-%%d') as created_date
                 FROM information_schema.TABLES
                 WHERE TABLE_SCHEMA = DATABASE() AND FIND_IN_SET( TABLE_NAME, %s )",
                implode( ',', $non_core )
            )
        );

        $size_map    = array();
        $rows_map    = array();
        $type_map    = array();
        $created_map = array();
        foreach ( $rows as $r ) {
            $size_map[ $r->name ]    = (int) $r->size_kb;
            $rows_map[ $r->name ]    = (int) $r->rows;
            $type_map[ $r->name ]    = $r->table_type ?? 'BASE TABLE';
            $created_map[ $r->name ] = $r->created_date ?? '';
        }

        $result = array();
        foreach ( $non_core as $table ) {
            $suffix   = substr( $table, $prefix_len );
            $result[] = array(
                'table'        => $table,
                'plugin'       => CSDT_Site_Audit::guess_plugin_from_suffix( $suffix ),
                'rows'         => $rows_map[ $table ] ?? 0,
                'size_kb'      => $size_map[ $table ] ?? 0,
                'table_type'   => $type_map[ $table ] ?? 'BASE TABLE',
                'created_date' => $created_map[ $table ] ?? '',
            );
        }

        usort( $result, fn( $a, $b ) => strcmp( $a['plugin'] . $a['table'], $b['plugin'] . $b['table'] ) );

        wp_send_json_success( array( 'tables' => $result ) );
    }

    public static function ajax_db_identify_table(): void {
        check_ajax_referer( CSDT_DevTools::OPTIMIZER_NONCE, 'nonce' );
        if ( ! current_user_can( 'manage_options' ) ) {
            wp_send_json_error( 'Unauthorized', 403 );
        }

        // phpcs:ignore WordPress.Security.ValidatedSanitizedInput.InputNotSanitized
        $table_names = json_decode( sanitize_text_field( wp_unslash( $_POST['table_names'] ?? '' ) ), true ); // A JSON list of table names: no markup, no whitespace to keep.
        if ( ! is_array( $table_names ) || empty( $table_names ) ) {
            wp_send_json_error( 'No tables specified.' );
            return;
        }

        global $wpdb;

        // Build table descriptions: name + columns for each
        $descriptions = array();
        foreach ( $table_names as $table ) {
            $table = sanitize_text_field( $table );
            if ( ! $table ) {
				continue; }
            // Only describe/exfiltrate this install's own tables. A name outside
            // $wpdb->prefix belongs to another app sharing the DB and must not have
            // its schema shipped to the AI vendor.
            if ( 0 !== strpos( $table, $wpdb->prefix ) ) {
                continue;
            }
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery
            $cols = $wpdb->get_col( $wpdb->prepare( 'SHOW COLUMNS FROM %i', $table ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- optimizer custom table operations; WP_Query not applicable
            if ( $cols ) {
                $descriptions[] = '- ' . $table . ': ' . implode( ', ', $cols );
            }
        }

        if ( empty( $descriptions ) ) {
            wp_send_json_error( 'No valid tables found.' );
            return;
        }

        $prompt = "Identify which WordPress plugin or theme created each of these database tables.\n"
                . "Return ONLY a valid JSON object. Each key is the full table name. Each value is an object with:\n"
                . "  \"plugin\": plugin name (2-5 words)\n"
                . "  \"description\": one sentence describing what the plugin does\n"
                . "  \"url\": plugin homepage URL (wordpress.org/plugins/... or official site)\n"
                . "  \"confidence\": \"High\", \"Medium\", or \"Low\"\n"
                . "No markdown, no explanation, only the JSON.\n\n"
                . "Tables:\n" . implode( "\n", $descriptions );

        // NB: no set_time_limit() here. WordPress.org disallows plugins raising PHP
        // execution limits. The single AI HTTP request below carries its own timeout;
        // if a host's max_execution_time is too low for the call, that is the host's
        // configuration to change, not this plugin's.
        try {
            $raw_text = CSDT_AI_Dispatcher::call( '', $prompt, '_auto', 8192 );
        } catch ( \RuntimeException $e ) {
            wp_send_json_error( 'AI error: ' . $e->getMessage() );
            return;
        }

        if ( ! $raw_text ) {
            wp_send_json_error( 'AI did not respond.' );
            return;
        }

        // Strip optional ```json ... ``` fences
        $raw_text = preg_replace( '/^```(?:json)?\s*/i', '', $raw_text );
        $raw_text = preg_replace( '/\s*```\s*$/i', '', $raw_text );

        $map = json_decode( trim( $raw_text ), true );
        if ( ! is_array( $map ) ) {
            wp_send_json_error( 'AI returned unexpected format.' );
            return;
        }

        // Sanitize values, support both flat string and object per entry
        $clean = array();
        foreach ( $map as $tbl => $entry ) {
            $key = sanitize_text_field( $tbl );
            if ( is_array( $entry ) ) {
                $clean[ $key ] = array(
                    'plugin'      => sanitize_text_field( $entry['plugin'] ?? '' ),
                    'description' => sanitize_text_field( $entry['description'] ?? '' ),
                    'url'         => esc_url_raw( $entry['url'] ?? '' ),
                    'confidence'  => in_array( $entry['confidence'] ?? '', array( 'High', 'Medium', 'Low' ), true )
                                    ? $entry['confidence'] : 'Low',
                );
            } else {
                $clean[ $key ] = array( 'plugin' => sanitize_text_field( (string) $entry ) );
            }
        }

        wp_send_json_success( array( 'map' => $clean ) );
    }

    public static function ajax_db_archive_tables(): void {
        check_ajax_referer( CSDT_DevTools::OPTIMIZER_NONCE, 'nonce' );
        if ( ! current_user_can( 'manage_options' ) ) {
            wp_send_json_error( 'Unauthorized', 403 );
        }

        // A MINTED TEST SESSION MUST NOT REACH THIS. Both scoped test roles hold manage_options, so
        // the check above is a capability check and not an identity one. Added 2026-08-17 with seven
        // sibling handlers, after widening tests/db-access-escalation-test.php to enumerate every
        // registration style and every file rather than `array( __CLASS__,` in cloudscale-cyber-devtools.php.
        if ( CSDT_DevTools::viewer_is_test_session() ) {
            wp_send_json_error( 'Not available to a test session.', 403 );
        }

        // phpcs:ignore WordPress.Security.ValidatedSanitizedInput.InputNotSanitized
        $tables = json_decode( sanitize_text_field( wp_unslash( $_POST['tables'] ?? '' ) ), true ); // A JSON list of table names: no markup, no whitespace to keep.
        if ( ! is_array( $tables ) || empty( $tables ) ) {
            wp_send_json_error( 'No tables specified.' );
            return;
        }

        global $wpdb;
        $prefix     = $wpdb->prefix;
        $core       = CSDT_Site_Audit::core_table_suffixes();
        $prefix_len = strlen( $prefix );
        $date       = gmdate( 'Ymd' );

        $archived = array();
        $errors   = array();

        foreach ( $tables as $table ) {
            $table = sanitize_text_field( $table );
            if ( ! str_starts_with( $table, $prefix ) ) {
                $errors[] = $table . ' (wrong prefix)';
                continue;
            }
            if ( in_array( substr( $table, $prefix_len ), $core, true ) ) {
                $errors[] = $table . ' (core, protected)';
                continue;
            }
            $owned_by_active = false;
            foreach ( self::active_owned_table_suffixes() as $sfx ) {
                if ( str_starts_with( substr( $table, $prefix_len ), $sfx ) ) {
                    $owned_by_active = true;
                    break;
                }
            }
            if ( $owned_by_active ) {
                $errors[] = $table . ' (in use by an active plugin, protected)';
                continue;
            }
            $new_name = '_trash_' . $date . '_' . $table;
            // Avoid collision
            $i = 1;
            while ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $new_name ) ) ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery
                $new_name = '_trash_' . $date . '_' . $i . '_' . $table;
                ++$i;
            }
            // Check if this is a VIEW, RENAME TABLE doesn't work on views
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery
            $table_type = $wpdb->get_var(
                $wpdb->prepare(
                    'SELECT TABLE_TYPE FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s',
                    $table
                )
            );

            if ( $table_type === 'VIEW' ) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery
                $ok = $wpdb->query( $wpdb->prepare( 'DROP VIEW %i', $table ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- optimizer custom table operations; WP_Query not applicable
                if ( $ok === false ) {
                    $db_err   = $wpdb->last_error ?: 'unknown MySQL error';
                    $errors[] = $table . ' (VIEW drop failed: ' . $db_err . ')';
                } else {
                    $archived[] = $table . ' (VIEW dropped)';
                }
            } else {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery
                $ok = $wpdb->query( $wpdb->prepare( 'RENAME TABLE %i TO %i', $table, $new_name ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- optimizer custom table operations; WP_Query not applicable
                if ( $ok === false ) {
                    $db_err   = $wpdb->last_error ?: 'unknown MySQL error';
                    $errors[] = $table . ' (' . $db_err . ')';
                } else {
                    $archived[] = $table;
                }
            }
        }

        if ( ! empty( $errors ) ) {
            wp_send_json_error(
                array(
					'archived' => count( $archived ),
					'message'  => 'Archived ' . count( $archived ) . ', failed: ' . implode( ', ', $errors ),
                )
            );
            return;
        }

        wp_send_json_success(
            array(
				'archived' => count( $archived ),
				'message'  => 'Moved ' . count( $archived ) . ' table(s) to Recycle Bin.',
            )
        );
    }

    public static function ajax_db_trash_scan(): void {
        check_ajax_referer( CSDT_DevTools::OPTIMIZER_NONCE, 'nonce' );
        if ( ! current_user_can( 'manage_options' ) ) {
            wp_send_json_error( 'Unauthorized', 403 );
        }

        global $wpdb;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery
        $all   = $wpdb->get_col( 'SHOW TABLES' );
        $trash = array_values( array_filter( $all, fn( $t ) => preg_match( '/^_trash_\d{8}_/', $t ) ) );

        if ( empty( $trash ) ) {
            wp_send_json_success( array( 'tables' => array() ) );
            return;
        }

        // Table names never contain a comma, so the list is ONE %s value for FIND_IN_SET() and
        // the statement is a literal; no placeholder list is assembled into it.
        $rows        = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- optimizer custom table operations; WP_Query not applicable
            $wpdb->prepare(
                "SELECT TABLE_NAME as name, TABLE_ROWS as row_count, ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024) as size_kb,
                        DATE_FORMAT(CREATE_TIME, '%%Y-%%m-%%d') as created_date
                 FROM information_schema.TABLES
                 WHERE TABLE_SCHEMA = DATABASE() AND FIND_IN_SET( TABLE_NAME, %s )",
                implode( ',', $trash )
            )
        );
        $size_map    = array();
        $rows_map    = array();
        $created_map = array();
        foreach ( $rows as $r ) {
            $size_map[ $r->name ]    = (int) $r->size_kb;
            $rows_map[ $r->name ]    = (int) $r->row_count;
            $created_map[ $r->name ] = $r->created_date ?? '';
        }

        $result = array();
        foreach ( $trash as $t ) {
            // Derive original table name by stripping _trash_YYYYMMDD_ or _trash_YYYYMMDD_N_ prefix
            $original = preg_replace( '/^_trash_\d{8}_(?:\d+_)?/', '', $t );
            $suffix   = substr( $original, strlen( $wpdb->prefix ) );
            $result[] = array(
                'trash_table'    => $t,
                'original_table' => $original,
                'size_kb'        => $size_map[ $t ] ?? 0,
                'rows'           => $rows_map[ $t ] ?? 0,
                'created_date'   => $created_map[ $t ] ?? '',
                'plugin'         => CSDT_Site_Audit::guess_plugin_from_suffix( $suffix ),
                'plugin_url'     => CSDT_Site_Audit::guess_plugin_url_from_suffix( $suffix ),
            );
        }

        usort( $result, fn( $a, $b ) => strcmp( $a['original_table'], $b['original_table'] ) );

        wp_send_json_success( array( 'tables' => $result ) );
    }

    public static function ajax_db_restore_tables(): void {
        check_ajax_referer( CSDT_DevTools::OPTIMIZER_NONCE, 'nonce' );
        if ( ! current_user_can( 'manage_options' ) ) {
            wp_send_json_error( 'Unauthorized', 403 );
        }

        // A MINTED TEST SESSION MUST NOT REACH THIS. Both scoped test roles hold manage_options, so
        // the check above is a capability check and not an identity one. Added 2026-08-17 with seven
        // sibling handlers, after widening tests/db-access-escalation-test.php to enumerate every
        // registration style and every file rather than `array( __CLASS__,` in cloudscale-cyber-devtools.php.
        if ( CSDT_DevTools::viewer_is_test_session() ) {
            wp_send_json_error( 'Not available to a test session.', 403 );
        }

        // phpcs:ignore WordPress.Security.ValidatedSanitizedInput.InputNotSanitized
        $tables = json_decode( sanitize_text_field( wp_unslash( $_POST['tables'] ?? '' ) ), true ); // A JSON list of table names: no markup, no whitespace to keep.
        if ( ! is_array( $tables ) || empty( $tables ) ) {
            wp_send_json_error( 'No tables specified.' );
            return;
        }

        global $wpdb;
        $restored = array();
        $errors   = array();

        foreach ( $tables as $table ) {
            $table = sanitize_text_field( $table );
            if ( ! preg_match( '/^_trash_\d{8}_/', $table ) ) {
                $errors[] = $table . ' (not a trash table)';
                continue;
            }
            $original = preg_replace( '/^_trash_\d{8}_(?:\d+_)?/', '', $table );
            // If original name is already taken, skip
            if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $original ) ) ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery
                $errors[] = $table . ' (original name ' . $original . ' already exists)';
                continue;
            }
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery
            $ok = $wpdb->query( $wpdb->prepare( 'RENAME TABLE %i TO %i', $table, $original ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- optimizer custom table operations; WP_Query not applicable
            if ( $ok === false ) {
                $errors[] = $table;
            } else {
                $restored[] = $original;
            }
        }

        if ( ! empty( $errors ) ) {
            wp_send_json_error(
                array(
					'restored' => count( $restored ),
					'message'  => 'Restored ' . count( $restored ) . ', failed: ' . implode( ', ', $errors ),
                )
            );
            return;
        }

        wp_send_json_success(
            array(
				'restored' => count( $restored ),
				'message'  => 'Restored ' . count( $restored ) . ' table(s) successfully.',
            )
        );
    }

    public static function ajax_db_drop_tables(): void {
        check_ajax_referer( CSDT_DevTools::OPTIMIZER_NONCE, 'nonce' );
        if ( ! current_user_can( 'manage_options' ) ) {
            wp_send_json_error( 'Unauthorized', 403 );
        }

        // A MINTED TEST SESSION MUST NOT REACH THIS. Both scoped test roles hold manage_options, so
        // the check above is a capability check and not an identity one. Added 2026-08-17 with seven
        // sibling handlers, after widening tests/db-access-escalation-test.php to enumerate every
        // registration style and every file rather than `array( __CLASS__,` in cloudscale-cyber-devtools.php.
        if ( CSDT_DevTools::viewer_is_test_session() ) {
            wp_send_json_error( 'Not available to a test session.', 403 );
        }

        // phpcs:ignore WordPress.Security.ValidatedSanitizedInput.InputNotSanitized
        $tables_json = sanitize_text_field( wp_unslash( $_POST['tables'] ?? '' ) ); // A JSON list of table names.
        $tables      = json_decode( $tables_json, true );
        if ( ! is_array( $tables ) || empty( $tables ) ) {
            wp_send_json_error( 'No tables specified.' );
            return;
        }

        global $wpdb;
        $dropped = array();
        $errors  = array();

        foreach ( $tables as $table ) {
            $table = sanitize_text_field( $table );
            // Only allow dropping tables in the recycle bin (_trash_ prefix)
            if ( ! preg_match( '/^_trash_\d{8}_/', $table ) ) {
                $errors[] = $table . ' (not in recycle bin, archive first)';
                continue;
            }
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery
            $result = $wpdb->query( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', $table ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- optimizer custom table operations; WP_Query not applicable
            if ( $result === false ) {
                $errors[] = $table;
            } else {
                $dropped[] = $table;
            }
        }

        if ( ! empty( $errors ) ) {
            wp_send_json_error(
                array(
					'dropped' => count( $dropped ),
					'message' => 'Dropped ' . count( $dropped ) . ', failed: ' . implode( ', ', $errors ),
                )
            );
            return;
        }

        wp_send_json_success(
            array(
				'dropped' => count( $dropped ),
				'message' => 'Permanently deleted ' . count( $dropped ) . ' table(s).',
            )
        );
    }

    // ── Optimizer: Plugin Stack Scanner ──────────────────────────────

    public static function ajax_plugin_stack_scan(): void {
        check_ajax_referer( CSDT_DevTools::OPTIMIZER_NONCE, 'nonce' );
        if ( ! current_user_can( 'manage_options' ) ) {
            wp_send_json_error( 'Unauthorized', 403 );
        }
        if ( ! function_exists( 'get_plugins' ) ) {
            require_once ABSPATH . 'wp-admin/includes/plugin.php';
        }
        $replacements = self::get_plugin_replacements();
        $active       = (array) get_option( 'active_plugins', array() );
        $all_plugins  = get_plugins();
        $matched      = array();
        $total_saving = 0;

        $active_set = array_flip( $active );
        foreach ( $all_plugins as $plugin_file => $info ) {
            if ( ! isset( $replacements[ $plugin_file ] ) ) {
                continue;
            }
            $r         = $replacements[ $plugin_file ];
            $is_active = isset( $active_set[ $plugin_file ] );
            $matched[] = array(
                'file'    => $plugin_file,
                'name'    => ! empty( $info['Name'] ) ? $info['Name'] : $r['name'],
                'version' => $info['Version'] ?? '',
                'feature' => $r['feature'],
                'tab'     => $r['tab'],
                'cost'    => $r['cost'],
                'active'  => $is_active,
            );
            if ( $is_active ) {
                $total_saving += $r['cost'];
            }
        }

        $match_count = count( $matched );
        set_transient(
            'csdt_plugin_stack_last',
            array(
				'match_count'  => $match_count,
				'total_saving' => $total_saving,
				'at'           => time(),
			),
			7 * DAY_IN_SECONDS
        );

        wp_send_json_success(
            array(
				'matched'      => $matched,
				'total_saving' => $total_saving,
				'active_count' => count( $active ),
            )
        );
    }

    private static function get_plugin_replacements(): array {
        return array(
            // Security scanners / firewalls
            'wordfence/wordfence.php'                   => array(
				'name'    => 'Wordfence Security',
				'feature' => 'AI Cyber Audit + Quick Fixes + Brute Force Protection',
				'tab'     => 'security',
				'cost'    => 119,
			),
            'better-wp-security/better-wp-security.php' => array(
				'name'    => 'iThemes Security',
				'feature' => 'Hide Login URL + Brute Force + Security Audit',
				'tab'     => 'login',
				'cost'    => 99,
			),
            'all-in-one-wp-security-and-firewall/wp-security.php' => array(
				'name'    => 'All-In-One Security & Firewall',
				'feature' => 'Hide Login URL + Brute Force + Hardening Quick Fixes',
				'tab'     => 'login',
				'cost'    => 0,
			),
            'sucuri-scanner/sucuri.php'                 => array(
				'name'    => 'Sucuri Security',
				'feature' => 'AI Cyber Audit + Server Logs',
				'tab'     => 'security',
				'cost'    => 199,
			),
            'wp-cerber/wp-cerber.php'                   => array(
				'name'    => 'WP Cerber Security',
				'feature' => 'Brute Force Protection + Hide Login URL',
				'tab'     => 'login',
				'cost'    => 99,
			),
            'shield-security/icwp-wpsf.php'             => array(
				'name'    => 'Shield Security',
				'feature' => 'AI Cyber Audit + Brute Force + Login Security',
				'tab'     => 'security',
				'cost'    => 69,
			),
            // Two-factor authentication
            'wp-2fa/wp-2fa.php'                         => array(
				'name'    => 'WP 2FA',
				'feature' => 'Two-Factor Auth (email OTP, TOTP, Passkeys)',
				'tab'     => 'login',
				'cost'    => 79,
			),
            'miniorange-2-factor-authentication/miniorange_2_factor_authentication.php' => array(
				'name'    => 'miniOrange 2FA',
				'feature' => 'Two-Factor Auth (email OTP, TOTP)',
				'tab'     => 'login',
				'cost'    => 99,
			),
            'google-authenticator/google-authenticator.php' => array(
				'name'    => 'Google Authenticator',
				'feature' => 'Two-Factor Auth (TOTP authenticator app)',
				'tab'     => 'login',
				'cost'    => 0,
			),
            'duo-wordpress/duo.php'                     => array(
				'name'    => 'Duo Two-Factor Auth',
				'feature' => 'Two-Factor Authentication',
				'tab'     => 'login',
				'cost'    => 0,
			),
            'two-factor/two-factor.php'                 => array(
				'name'    => 'Two Factor',
				'feature' => 'Two-Factor Auth (email OTP, TOTP, Passkeys)',
				'tab'     => 'login',
				'cost'    => 0,
			),
            // Login protection / hide login
            'limit-login-attempts-reloaded/limit-login-attempts-reloaded.php' => array(
				'name'    => 'Limit Login Attempts Reloaded',
				'feature' => 'Brute Force Protection (per-account lockout)',
				'tab'     => 'login',
				'cost'    => 0,
			),
            'loginpress/loginpress.php'                 => array(
				'name'    => 'LoginPress',
				'feature' => 'Hide Login URL',
				'tab'     => 'login',
				'cost'    => 49,
			),
            'wps-hide-login/wps-hide-login.php'         => array(
				'name'    => 'WPS Hide Login',
				'feature' => 'Hide Login URL',
				'tab'     => 'login',
				'cost'    => 0,
			),
            'rename-wp-login/rename-wp-login.php'       => array(
				'name'    => 'Rename wp-login.php',
				'feature' => 'Hide Login URL',
				'tab'     => 'login',
				'cost'    => 0,
			),
            'sf-move-login/sf-move-login.php'           => array(
				'name'    => 'Move Login',
				'feature' => 'Hide Login URL',
				'tab'     => 'login',
				'cost'    => 0,
			),
            // SMTP
            'wp-mail-smtp/wp_mail_smtp.php'             => array(
				'name'    => 'WP Mail SMTP',
				'feature' => 'SMTP Mail (authenticated delivery + email log)',
				'tab'     => 'mail',
				'cost'    => 49,
			),
            'post-smtp/postman-smtp.php'                => array(
				'name'    => 'Post SMTP',
				'feature' => 'SMTP Mail (authenticated delivery)',
				'tab'     => 'mail',
				'cost'    => 0,
			),
            'easy-wp-smtp/easy-wp-smtp.php'             => array(
				'name'    => 'Easy WP SMTP',
				'feature' => 'SMTP Mail',
				'tab'     => 'mail',
				'cost'    => 0,
			),
            'fluent-smtp/fluent-smtp.php'               => array(
				'name'    => 'FluentSMTP',
				'feature' => 'SMTP Mail',
				'tab'     => 'mail',
				'cost'    => 0,
			),
            'sendgrid-email-delivery-simplified/wpsendgrid.php' => array(
				'name'    => 'SendGrid',
				'feature' => 'SMTP Mail (use any SMTP provider)',
				'tab'     => 'mail',
				'cost'    => 0,
			),
            // Code syntax highlighting
            'enlighter/enlighter.php'                   => array(
				'name'    => 'Enlighter Syntax Highlighter',
				'feature' => 'Code Block (37 languages, 14 themes, zero CDN)',
				'tab'     => 'migrate',
				'cost'    => 29,
			),
            'syntaxhighlighter/syntaxhighlighter.php'   => array(
				'name'    => 'SyntaxHighlighter Evolved',
				'feature' => 'Code Block (37 languages)',
				'tab'     => 'migrate',
				'cost'    => 0,
			),
            'prismatic/prismatic.php'                   => array(
				'name'    => 'Prismatic',
				'feature' => 'Code Block (37 languages, 14 themes)',
				'tab'     => 'migrate',
				'cost'    => 29,
			),
            'code-syntax-block/index.php'               => array(
				'name'    => 'Code Syntax Block',
				'feature' => 'Code Block (Gutenberg block)',
				'tab'     => 'migrate',
				'cost'    => 0,
			),
            'urvanov-syntax-highlighter/urvanov-syntax-highlighter.php' => array(
				'name'    => 'Urvanov Syntax Highlighter',
				'feature' => 'Code Block',
				'tab'     => 'migrate',
				'cost'    => 0,
			),
            // Log viewers / debug tools
            'wp-log-viewer/wp-log-viewer.php'           => array(
				'name'    => 'WP Log Viewer',
				'feature' => 'Server Logs (live search, tail mode, multiple sources)',
				'tab'     => 'logs',
				'cost'    => 0,
			),
            'query-monitor/query-monitor.php'           => array(
				'name'    => 'Query Monitor',
				'feature' => 'Performance Monitor + Server Logs',
				'tab'     => 'logs',
				'cost'    => 0,
			),
            'debug-bar/debug-bar.php'                   => array(
				'name'    => 'Debug Bar',
				'feature' => 'Performance Monitor',
				'tab'     => 'logs',
				'cost'    => 0,
			),
            // Social / OG images
            'wordpress-seo/wp-seo.php'                  => array(
				'name'    => 'Yoast SEO',
				'feature' => 'Thumbnails (og:image generation + social preview scan)',
				'tab'     => 'thumbnails',
				'cost'    => 99,
			),
            'seo-by-rank-math/rank-math.php'            => array(
				'name'    => 'Rank Math SEO',
				'feature' => 'Thumbnails (og:image generation)',
				'tab'     => 'thumbnails',
				'cost'    => 0,
			),
        );
    }

    // ── Optimizer: Update Risk Scanner ───────────────────────────────

    public static function ajax_update_risk_scan(): void {
        check_ajax_referer( CSDT_DevTools::OPTIMIZER_NONCE, 'nonce' );
        if ( ! current_user_can( 'manage_options' ) ) {
            wp_send_json_error( 'Unauthorized', 403 );
        }

        $update_data = get_site_transient( 'update_plugins' );
        if ( ! $update_data || empty( $update_data->response ) ) {
            // Force a fresh check
            wp_update_plugins();
            $update_data = get_site_transient( 'update_plugins' );
        }

        if ( ! function_exists( 'get_plugins' ) ) {
            require_once ABSPATH . 'wp-admin/includes/plugin.php';
        }
        $all_plugins = get_plugins();
        $plugins     = array();

        if ( ! empty( $update_data->response ) ) {
            foreach ( $update_data->response as $plugin_file => $data ) {
                $info      = $all_plugins[ $plugin_file ] ?? array();
                $plugins[] = array(
                    'file'            => $plugin_file,
                    'slug'            => $data->slug ?? dirname( $plugin_file ),
                    'name'            => ! empty( $info['Name'] ) ? $info['Name'] : ( $data->slug ?? $plugin_file ),
                    'current_version' => $info['Version'] ?? '?',
                    'new_version'     => $data->new_version ?? '?',
                );
            }
        }

        wp_send_json_success( array( 'plugins' => $plugins ) );
    }

    public static function ajax_update_risk_assess(): void {
        check_ajax_referer( CSDT_DevTools::OPTIMIZER_NONCE, 'nonce' );
        if ( ! current_user_can( 'manage_options' ) ) {
            wp_send_json_error( 'Unauthorized', 403 );
        }

        $slug            = sanitize_text_field( wp_unslash( $_POST['slug'] ?? '' ) ); // phpcs:ignore WordPress.Security.NonceVerification.Missing
        $current_version = sanitize_text_field( wp_unslash( $_POST['current_version'] ?? '' ) ); // phpcs:ignore WordPress.Security.NonceVerification.Missing
        $new_version     = sanitize_text_field( wp_unslash( $_POST['new_version'] ?? '' ) ); // phpcs:ignore WordPress.Security.NonceVerification.Missing
        $plugin_name     = sanitize_text_field( wp_unslash( $_POST['name'] ?? $slug ) ); // phpcs:ignore WordPress.Security.NonceVerification.Missing

        if ( ! $slug ) {
            wp_send_json_error( 'Missing slug' );
        }

        // Fetch changelog from WordPress.org
        $changelog = '';
        $api_url   = add_query_arg(
            array(
				'action'                    => 'plugin_information',
				'request[slug]'             => $slug,
				'request[fields][sections]' => '1',
            ),
            'https://api.wordpress.org/plugins/info/1.2/'
        );

        $response = wp_remote_get( $api_url, array( 'user-agent' => CSDT_DevTools::USER_AGENT, 'timeout' => 10 ) );
        $body = CSDT_Http::json( $response )['data'];
        if ( ! empty( $body['sections']['changelog'] ) ) {
            $changelog = wp_strip_all_tags( $body['sections']['changelog'] );
            $changelog = mb_substr( $changelog, 0, 3000 );
        }

        $has_key = CSDT_AI_Dispatcher::has_key();

        if ( $has_key && $changelog ) {
            $system   = 'You are a WordPress plugin update risk assessor. Given a plugin name, version numbers, and changelog, classify the update as exactly one of: "patch" (security fix or bug fix, apply immediately), "minor" (new features, low breaking risk), or "breaking" (major version, deprecated APIs, DB migrations, or significant structural changes, review before applying). Respond with ONLY valid JSON, no other text: {"risk":"patch","reason":"One sentence."}';
            $user_msg = "Plugin: {$plugin_name}\nCurrent version: {$current_version}\nNew version: {$new_version}\n\nChangelog:\n{$changelog}";
            try {
                $raw  = CSDT_AI_Dispatcher::call( $system, $user_msg, '_auto', CSDT_AI_Dispatcher::DEFAULT_MAX_TOKENS );
                $raw  = preg_replace( '/^```(?:json)?\s*/i', '', trim( $raw ) );
                $raw  = preg_replace( '/\s*```$/', '', $raw );
                $data = json_decode( $raw, true );
                if ( is_array( $data ) && ! empty( $data['risk'] ) ) {
                    wp_send_json_success(
                        array(
							'risk'   => in_array( $data['risk'], array( 'patch', 'minor', 'breaking' ), true ) ? $data['risk'] : 'minor',
							'reason' => sanitize_text_field( $data['reason'] ?? '' ),
							'source' => 'ai',
                        )
                    );
                    return;
                }
            } catch ( \Throwable $e ) {
                // no-alert: logged, then answered from the semver fallback below, which the
                // response labels source "semver" and "AI unavailable".
                CSDT_Monitor::log_exception( $e, __METHOD__ );
            }
        }

        // Semver fallback
        $risk = self::update_risk_from_semver( $current_version, $new_version );
        wp_send_json_success(
            array(
				'risk'   => $risk,
				'reason' => $changelog ? 'Based on version number change (AI unavailable).' : 'No changelog found, assessed from version number only.',
				'source' => 'semver',
            )
        );
    }

    private static function update_risk_from_semver( string $current, string $new ): string {
        preg_match( '/^(\d+)\.(\d+)/', $current, $cm );
        preg_match( '/^(\d+)\.(\d+)/', $new, $nm );
        $c_maj = (int) ( $cm[1] ?? 0 );
        $n_maj = (int) ( $nm[1] ?? 0 );
        $c_min = (int) ( $cm[2] ?? 0 );
        $n_min = (int) ( $nm[2] ?? 0 );
        if ( $n_maj > $c_maj ) {
			return 'breaking';
        }
        if ( $n_min > $c_min ) {
			return 'minor';
        }
        return 'patch';
    }

    // ── Optimizer: Database Intelligence Engine ──────────────────────

    public static function ajax_db_intelligence_scan(): void {
        check_ajax_referer( CSDT_DevTools::OPTIMIZER_NONCE, 'nonce' );
        if ( ! current_user_can( 'manage_options' ) ) {
            wp_send_json_error( 'Unauthorized', 403 );
        }

        global $wpdb;

        // Autoloaded options
        $al                = $wpdb->get_row( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
            $wpdb->prepare(
                "SELECT COUNT(*) AS cnt, COALESCE(SUM(LENGTH(option_value)),0) AS total_bytes
                 FROM %i
                 WHERE autoload IN ('yes','on','1','true')",
                $wpdb->options
            ),
            ARRAY_A
        );
        $autoload_total_kb = round( (float) $al['total_bytes'] / 1024, 1 );
        $autoload_count    = (int) $al['cnt'];

        $top_autoloaded = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
            $wpdb->prepare(
                "SELECT option_name, ROUND(LENGTH(option_value)/1024,1) AS size_kb
                 FROM %i
                 WHERE autoload IN ('yes','on','1','true')
                 ORDER BY LENGTH(option_value) DESC
                 LIMIT 10",
                $wpdb->options
            ),
            ARRAY_A
        );

        // Expired transients
        $tr                    = $wpdb->get_row( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
            $wpdb->prepare(
                'SELECT COUNT(*) AS cnt, COALESCE(SUM(LENGTH(option_name)+LENGTH(option_value)),0) AS total_bytes
                 FROM %i
                 WHERE option_name LIKE %s
                   AND CAST(option_value AS UNSIGNED) < UNIX_TIMESTAMP()',
                $wpdb->options,
                $wpdb->esc_like( '_transient_timeout_' ) . '%'
            ),
            ARRAY_A
        );
        $expired_transients    = (int) $tr['cnt'];
        $expired_transients_kb = round( (float) $tr['total_bytes'] / 1024, 1 );

        // Post revisions
        $rv              = $wpdb->get_row( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
            $wpdb->prepare(
                "SELECT COUNT(*) AS cnt,
                        COALESCE(SUM(LENGTH(post_content)+LENGTH(post_title)+LENGTH(post_excerpt)),0) AS total_bytes
                 FROM %i
                 WHERE post_type = 'revision'",
                $wpdb->posts
            ),
            ARRAY_A
        );
        $revisions_count = (int) $rv['cnt'];
        $revisions_kb    = round( (float) $rv['total_bytes'] / 1024, 1 );

        // Orphaned postmeta
        $orphan_count = (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
            $wpdb->prepare(
                'SELECT COUNT(*) FROM %i pm
                 WHERE NOT EXISTS (SELECT 1 FROM %i p WHERE p.ID = pm.post_id)',
                $wpdb->postmeta,
                $wpdb->posts
            )
        );

        // Table sizes
        $tables            = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
            $wpdb->prepare(
                'SELECT TABLE_NAME AS tbl,
                        TABLE_ROWS AS `rows`,
                        ROUND(DATA_LENGTH/1024,0) AS data_kb,
                        ROUND(INDEX_LENGTH/1024,0) AS index_kb,
                        ROUND(COALESCE(DATA_FREE,0)/1024,0) AS overhead_kb,
                        ENGINE AS engine
                 FROM information_schema.TABLES
                 WHERE TABLE_SCHEMA = %s
                 ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC',
                DB_NAME
            ),
            ARRAY_A
        );
        $total_db_kb       = 0;
        $total_overhead_kb = 0;
        foreach ( (array) $tables as $t ) {
            $total_db_kb       += (int) $t['data_kb'] + (int) $t['index_kb'];
            $total_overhead_kb += (int) $t['overhead_kb'];
        }

        // Rule-based findings
        $findings = array();

        if ( $autoload_total_kb > 300 ) {
            $top_names  = implode(
                ', ',
                array_map(
                    function ( $r ) {
                        return $r['option_name'] . ' (' . $r['size_kb'] . ' KB)'; },
                    array_slice( (array) $top_autoloaded, 0, 5 )
                )
            );
            $findings[] = array(
                'title'      => 'Large Autoload Cache (' . $autoload_total_kb . ' KB)',
                'detail'     => $autoload_count . ' options autoload on every page load, consuming ' . $autoload_total_kb . ' KB. Top offenders: ' . $top_names . '.',
                'fix'        => 'Review the top autoloaded options. Deactivate unused plugins that add large rows. Consider a plugin like Auctollo Autoload Manager to flip individual options to not autoload.',
                'severity'   => $autoload_total_kb > 1000 ? 'high' : 'medium',
                'fix_action' => null,
            );
        }

        if ( $expired_transients > 20 ) {
            $findings[] = array(
                'title'      => 'Expired Transients (' . $expired_transients . ')',
                'detail'     => $expired_transients . ' expired transients are still in the database, consuming ' . $expired_transients_kb . ' KB. They bloat the wp_options table and inflate autoload queries.',
                'fix'        => 'Click Fix It to delete all expired transients immediately. They regenerate on demand as needed.',
                'severity'   => $expired_transients > 200 ? 'medium' : 'low',
                'fix_action' => 'db_delete_expired_transients',
            );
        }

        if ( $revisions_count > 200 ) {
            $findings[] = array(
                'title'      => 'Post Revisions (' . number_format( $revisions_count ) . ' rows, ' . $revisions_kb . ' KB)',
                'detail'     => number_format( $revisions_count ) . ' post revisions stored, using ' . $revisions_kb . ' KB. WordPress stores unlimited revisions by default, inflating the wp_posts table.',
                'fix'        => "Click Fix It to delete all revisions. Going forward, add define('WP_POST_REVISIONS', 5) to wp-config.php to cap future revisions per post.",
                'severity'   => $revisions_count > 1000 ? 'medium' : 'low',
                'fix_action' => 'db_delete_revisions',
            );
        }

        if ( $orphan_count > 50 ) {
            $findings[] = array(
                'title'      => 'Orphaned Post Meta (' . number_format( $orphan_count ) . ' rows)',
                'detail'     => number_format( $orphan_count ) . ' rows in wp_postmeta reference posts that no longer exist. Left behind by deleted posts or poorly-cleaned-up plugins.',
                'fix'        => 'Click Fix It to delete all orphaned postmeta rows.',
                'severity'   => 'low',
                'fix_action' => 'db_delete_orphaned_postmeta',
            );
        }

        if ( $total_overhead_kb > 1024 ) {
            $overhead_mb = round( $total_overhead_kb / 1024, 1 );
            $findings[]  = array(
                'title'      => 'Table Fragmentation (' . $overhead_mb . ' MB reclaimable)',
                'detail'     => $overhead_mb . ' MB of overhead detected from deleted rows. OPTIMIZE TABLE reclaims this space and can improve query performance.',
                'fix'        => 'Click Fix It to run OPTIMIZE TABLE across all tables. May take a few seconds on large databases.',
                'severity'   => $total_overhead_kb > 10240 ? 'medium' : 'low',
                'fix_action' => 'db_optimize_tables',
            );
        }

        if ( empty( $findings ) ) {
            $findings[] = array(
                'title'      => 'Database looks healthy',
                'detail'     => 'No significant bloat detected. Autoload size, transients, revisions, and postmeta are all within normal thresholds.',
                'fix'        => 'No action needed.',
                'severity'   => 'info',
                'fix_action' => null,
            );
        }

        wp_send_json_success(
            array(
				'stats'    => array(
					'autoload_total_kb'     => $autoload_total_kb,
					'autoload_count'        => $autoload_count,
					'top_autoloaded'        => $top_autoloaded,
					'expired_transients'    => $expired_transients,
					'expired_transients_kb' => $expired_transients_kb,
					'revisions_count'       => $revisions_count,
					'revisions_kb'          => $revisions_kb,
					'orphaned_postmeta'     => $orphan_count,
					'total_db_kb'           => $total_db_kb,
					'total_overhead_kb'     => $total_overhead_kb,
					'tables'                => $tables,
				),
				'findings' => $findings,
            )
        );
    }

    public static function ajax_db_intelligence_fix(): void {
        check_ajax_referer( CSDT_DevTools::OPTIMIZER_NONCE, 'nonce' );
        if ( ! current_user_can( 'manage_options' ) ) {
            wp_send_json_error( 'Unauthorized', 403 );
        }

        // A MINTED TEST SESSION MUST NOT REACH THIS. Both scoped test roles hold manage_options, so
        // the check above is a capability check and not an identity one. Added 2026-08-17 with seven
        // sibling handlers, after widening tests/db-access-escalation-test.php to enumerate every
        // registration style and every file rather than `array( __CLASS__,` in cloudscale-cyber-devtools.php.
        if ( CSDT_DevTools::viewer_is_test_session() ) {
            wp_send_json_error( 'Not available to a test session.', 403 );
        }

        $fix_id = isset( $_POST['fix_id'] ) ? sanitize_key( wp_unslash( $_POST['fix_id'] ) ) : '';
        global $wpdb;

        switch ( $fix_id ) {
            case 'db_delete_expired_transients':
                $deleted = (int) $wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
                    $wpdb->prepare(
                        "DELETE a, b
                         FROM %i a
                         LEFT JOIN %i b
                             ON b.option_name = REPLACE(a.option_name,'_transient_timeout_','_transient_')
                         WHERE a.option_name LIKE %s
                           AND CAST(a.option_value AS UNSIGNED) < UNIX_TIMESTAMP()",
                        $wpdb->options,
                        $wpdb->options,
                        $wpdb->esc_like( '_transient_timeout_' ) . '%'
                    )
                );
                wp_send_json_success( array( 'message' => 'Deleted ' . intdiv( $deleted, 2 ) . ' expired transients.' ) );
                return;

            case 'db_delete_revisions':
                $count = (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
                    $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE post_type = 'revision'", $wpdb->posts )
                );
                $wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
                    $wpdb->prepare(
                        "DELETE pm FROM %i pm
                         INNER JOIN %i p ON p.ID = pm.post_id
                         WHERE p.post_type = 'revision'",
                        $wpdb->postmeta,
                        $wpdb->posts
                    )
                );
                $wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
                    $wpdb->prepare( "DELETE FROM %i WHERE post_type = 'revision'", $wpdb->posts )
                );
                wp_send_json_success( array( 'message' => 'Deleted ' . number_format( $count ) . ' revisions.' ) );
                return;

            case 'db_delete_orphaned_postmeta':
                $count = (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
                    $wpdb->prepare(
                        'SELECT COUNT(*) FROM %i pm
                         WHERE NOT EXISTS (SELECT 1 FROM %i p WHERE p.ID = pm.post_id)',
                        $wpdb->postmeta,
                        $wpdb->posts
                    )
                );
                $wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
                    $wpdb->prepare(
                        'DELETE pm FROM %i pm
                         WHERE NOT EXISTS (SELECT 1 FROM %i p WHERE p.ID = pm.post_id)',
                        $wpdb->postmeta,
                        $wpdb->posts
                    )
                );
                wp_send_json_success( array( 'message' => 'Deleted ' . number_format( $count ) . ' orphaned meta rows.' ) );
                return;

            case 'db_optimize_tables':
                $db_tables = $wpdb->get_col( 'SHOW TABLES' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
                foreach ( (array) $db_tables as $tbl ) {
                    $wpdb->query( $wpdb->prepare( 'OPTIMIZE TABLE %i', $tbl ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.NotPrepared
                }
                wp_send_json_success( array( 'message' => 'Optimized ' . count( (array) $db_tables ) . ' tables.' ) );
                return;

            default:
                wp_send_json_error( 'Unknown fix ID' );
        }
    }
}