<?php
/**
 * Runtime cache safety, diagnostics, and database maintenance.
 *
 * This module intentionally keeps destructive operations opt-in. Plugin updates
 * may create the small usage table and migrate settings, but never delete rows
 * from the translation table automatically.
 *
 * @package AITMultilingualTranslate
 */

if (!defined('ABSPATH')) {
    exit;
}

final class AITMT_Runtime_Maintenance
{
    const USAGE_SCHEMA_VERSION = 1;
    const SETTINGS_MIGRATION_VERSION = 4;
    const CRON_HOOK = 'aitmt_runtime_cache_cleanup_event';

    /**
     * Register hooks used by both the fallback and legacy engines.
     */
    public static function bootstrap()
    {
        add_action('admin_init', array(__CLASS__, 'maybe_install'));
        add_action('init', array(__CLASS__, 'maybe_install'), 1);
        add_action('admin_menu', array(__CLASS__, 'register_admin_page'), 99);
        add_action('admin_post_aitmt_database_maintenance', array(__CLASS__, 'handle_admin_action'));
        add_action('admin_post_aitmt_rebuild_scanner_queue', array(__CLASS__, 'handle_rebuild_scanner_queue'));
        add_action('admin_notices', array(__CLASS__, 'render_migration_notice'));
        add_action(self::CRON_HOOK, array(__CLASS__, 'scheduled_cleanup'));
        add_action('init', array(__CLASS__, 'sync_cleanup_schedule'), 30);

        if (defined('WP_CLI') && WP_CLI) {
            add_action('cli_init', array(__CLASS__, 'register_cli_command'));
        }
    }

    /**
     * Install the small runtime usage table and safe settings.
     */
    public static function maybe_install()
    {
        self::maybe_mark_scanner_maintenance();

        if ((int) get_option('aitmt_runtime_usage_schema_version', 0) < self::USAGE_SCHEMA_VERSION) {
            self::create_usage_table();
        }

        if ((int) get_option('aitmt_runtime_safety_migration_version', 0) < self::SETTINGS_MIGRATION_VERSION) {
            self::migrate_safe_settings();
        }
    }

    /**
     * Activation entry point.
     */
    public static function activate()
    {
        self::maybe_mark_scanner_maintenance();
        self::create_usage_table();
        self::migrate_safe_settings();
        self::sync_cleanup_schedule();
    }

    /**
     * Record an upgrade notice without modifying translation rows.
     *
     * The 0.4.1 scanner can identify obsolete aggregated scanner records, but
     * cleanup remains an explicit administrator action with a dry run. Fresh
     * installs do not receive an upgrade notice.
     */
    private static function maybe_mark_scanner_maintenance()
    {
        $current = defined('AITMT_VERSION') ? (string) AITMT_VERSION : '';
        if ('' === $current) {
            return;
        }

        $recorded = (string) get_option('aitmt_runtime_release_version', '');
        $legacy = (string) get_option('aitmt_rewrite_version', '');
        $previous = '' !== $recorded ? $recorded : $legacy;

        if ('' !== $previous && version_compare($previous, '0.4.1', '<') && version_compare($current, '0.4.1', '>=')) {
            update_option('aitmt_scanner_maintenance_notice', '1', false);
        }

        update_option('aitmt_runtime_release_version', $current, false);
    }

    /**
     * Deactivation entry point.
     */
    public static function deactivate()
    {
        wp_clear_scheduled_hook(self::CRON_HOOK);
    }

    /**
     * Add settings without deleting or rewriting existing translations.
     */
    private static function migrate_safe_settings()
    {
        $options = get_option('aitmt_options', array());
        $options = is_array($options) ? $options : array();
        $backup_option = 'aitmt_options_backup_pre_0_4_0';

        if (false === get_option($backup_option, false)) {
            add_option(
                $backup_option,
                array(
                    'plugin_version' => defined('AITMT_VERSION') ? AITMT_VERSION : '',
                    'created_at' => current_time('mysql'),
                    'options' => $options,
                ),
                '',
                false
            );
        }

        $defaults = array(
            'runtime_page_translation' => '0',
            'runtime_db_storage' => '0',
            'runtime_auto_cleanup' => '0',
            'runtime_cache_ttl_days' => 30,
            'runtime_max_rows_per_language' => 5000,
            'runtime_min_text_length' => 3,
            'runtime_max_text_length' => 800,
            'runtime_excluded_paths' => "/wp-json/\n/feed/",
            'runtime_excluded_selectors' => '.notranslate, [translate="no"], [data-aitmt-no-translate], [data-no-translate], [data-ai-translate-ignore]',
            'runtime_excluded_source_types' => '',
            'runtime_translate_woocommerce_private' => '0',
            'default_frontend_language' => '',
            'claude_api_key' => '',
            'claude_model' => 'claude-haiku-4-5-20251001',
            'yandex_translate_api_key' => '',
            'yandex_translate_folder_id' => '',
            'yandexgpt_api_key' => '',
            'yandexgpt_folder_id' => '',
            'yandexgpt_model' => 'yandexgpt-lite/latest',
            'mistral_api_key' => '',
            'mistral_model' => 'mistral-small-latest',
            'deepseek_api_key' => '',
            'deepseek_model' => 'deepseek-v4-flash',
            'qwen_api_key' => '',
            'qwen_model' => 'qwen3.7-plus',
            'qwen_region' => 'us',
            'baidu_api_key' => '',
            'baidu_model' => 'ernie-5.0',
        );
        $changed = false;

        foreach ($defaults as $key => $value) {
            if (!array_key_exists($key, $options)) {
                $options[$key] = $value;
                $changed = true;
            }
        }

        $legacy_excluded_paths = "/cart/\n/checkout/\n/my-account/\n/wp-json/\n/feed/";
        if ($legacy_excluded_paths === ($options['runtime_excluded_paths'] ?? '')) {
            $options['runtime_excluded_paths'] = $defaults['runtime_excluded_paths'];
            $changed = true;
        }

        if ($changed && !update_option('aitmt_options', $options, false)) {
            update_option('aitmt_migration_error', __('The settings migration could not be saved. Existing settings were left available in the migration backup.', 'ait-multilingual-translate'), false);
            return;
        }

        $saved = get_option('aitmt_options', array());
        if (!is_array($saved) || array_diff_key($defaults, $saved)) {
            update_option('aitmt_migration_error', __('The settings migration verification failed. Review the migration backup before changing provider settings.', 'ait-multilingual-translate'), false);
            return;
        }

        delete_option('aitmt_migration_error');
        update_option('aitmt_runtime_safety_migration_version', self::SETTINGS_MIGRATION_VERSION, false);
    }

    /**
     * Show a migration failure without exposing stored settings or API keys.
     */
    public static function render_migration_notice()
    {
        if (!current_user_can('manage_options')) {
            return;
        }

        $message = get_option('aitmt_migration_error', '');
        if ('' === (string) $message) {
            if ('1' !== (string) get_option('aitmt_scanner_maintenance_notice', '0')) {
                return;
            }
            ?>
            <div class="notice notice-info">
                <p>
                    <?php esc_html_e('AIT Multilingual Translate preserved all existing translations during the update. A manual scanner maintenance tool is available to preview obsolete aggregated scanner records and rebuild only missing queue items.', 'ait-multilingual-translate'); ?>
                    <a href="<?php echo esc_url(admin_url('admin.php?page=ait-multilingual-translate-database')); ?>"><?php esc_html_e('Review scanner maintenance', 'ait-multilingual-translate'); ?></a>
                </p>
            </div>
            <?php
            return;
        }
        ?>
        <div class="notice notice-error"><p><?php echo esc_html((string) $message); ?></p></div>
        <?php
    }

    /**
     * Runtime usage metadata is kept outside the large translation table.
     */
    private static function create_usage_table()
    {
        global $wpdb;

        $table = self::usage_table_sql();
        if ('' === $table) {
            return;
        }

        $charset_collate = $wpdb->get_charset_collate();
        $sql = "CREATE TABLE {$table} (
            translation_id bigint(20) unsigned NOT NULL,
            source_hash char(64) NOT NULL,
            target_language varchar(16) NOT NULL,
            context varchar(80) NOT NULL DEFAULT 'html',
            use_count bigint(20) unsigned NOT NULL DEFAULT 0,
            first_used_at datetime NOT NULL,
            last_used_at datetime NOT NULL,
            PRIMARY KEY  (translation_id),
            KEY source_target (source_hash, target_language),
            KEY last_used_at (last_used_at)
        ) {$charset_collate};";

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        dbDelta($sql);
        update_option('aitmt_runtime_usage_schema_version', self::USAGE_SCHEMA_VERSION, false);
    }

    /**
     * Return the validated translation table name.
     */
    public static function translation_table_sql()
    {
        global $wpdb;

        $table = $wpdb->prefix . 'aitmt_translations';

        return preg_match('/^[A-Za-z0-9_]+$/', $table) ? esc_sql($table) : '';
    }

    /**
     * Return the validated runtime usage table name.
     */
    private static function usage_table_sql()
    {
        global $wpdb;

        $table = $wpdb->prefix . 'aitmt_runtime_usage';

        return preg_match('/^[A-Za-z0-9_]+$/', $table) ? esc_sql($table) : '';
    }

    /**
     * Record a local runtime cache hit without storing visitor data.
     *
     * @param array $row Translation row.
     */
    public static function record_usage($row)
    {
        global $wpdb;

        if (!is_array($row) || empty($row['id']) || empty($row['source_hash'])) {
            return;
        }

        if ((int) get_option('aitmt_runtime_usage_schema_version', 0) < self::USAGE_SCHEMA_VERSION) {
            self::create_usage_table();
        }
        $table = self::usage_table_sql();
        $now = current_time('mysql');
        if ('' === $table) {
            return;
        }

        $sql = $wpdb->prepare(
            "INSERT INTO {$table} (translation_id, source_hash, target_language, context, use_count, first_used_at, last_used_at)
            VALUES (%d, %s, %s, %s, 1, %s, %s)
            ON DUPLICATE KEY UPDATE use_count = use_count + 1, last_used_at = VALUES(last_used_at)",
            absint($row['id']),
            sanitize_text_field((string) $row['source_hash']),
            sanitize_key((string) ($row['target_language'] ?? '')),
            sanitize_key((string) ($row['context'] ?? 'html')),
            $now,
            $now
        );
        $wpdb->query($sql); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query is prepared above and table is validated.
    }

    /**
     * Check the configured runtime cache ceiling.
     */
    public static function runtime_capacity_available($target_language)
    {
        global $wpdb;

        $options = function_exists('aitmt_fallback_options') ? aitmt_fallback_options() : array();
        $limit = max(100, absint($options['runtime_max_rows_per_language'] ?? 5000));
        $language = sanitize_key((string) $target_language);
        $cache_key = 'aitmt_runtime_count_' . $language;
        $count = get_transient($cache_key);

        if (false === $count) {
            $table = self::translation_table_sql();
            if ('' === $table) {
                return false;
            }
            $count = (int) $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT COUNT(*) FROM {$table} WHERE target_language = %s AND source_type IN (%s, %s)",
                    $language,
                    'page_load',
                    'legacy_runtime'
                )
            ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Values are prepared and table is validated.
            set_transient($cache_key, $count, 5 * MINUTE_IN_SECONDS);
        }

        return (int) $count < $limit;
    }

    /**
     * Increment cached runtime row count after a successful insert.
     */
    public static function increment_runtime_count($target_language)
    {
        $key = 'aitmt_runtime_count_' . sanitize_key((string) $target_language);
        $count = get_transient($key);
        if (false !== $count) {
            set_transient($key, ((int) $count) + 1, 5 * MINUTE_IN_SECONDS);
        }
    }

    /**
     * Keep a small set of aggregate, non-sensitive runtime counters.
     */
    public static function increment_counter($name, $amount = 1)
    {
        $name = sanitize_key((string) $name);
        if ('' === $name) {
            return;
        }

        $day = gmdate('Y-m-d');
        $counters = get_option('aitmt_runtime_diagnostics', array());
        $counters = is_array($counters) ? $counters : array();
        if (!isset($counters[$day]) || !is_array($counters[$day])) {
            $counters[$day] = array();
        }
        $counters[$day][$name] = absint($counters[$day][$name] ?? 0) + max(1, absint($amount));

        if (count($counters) > 14) {
            ksort($counters);
            $counters = array_slice($counters, -14, null, true);
        }

        update_option('aitmt_runtime_diagnostics', $counters, false);
    }

    /**
     * Return today's aggregate diagnostics without exposing translated content.
     *
     * @return array<string,int>
     */
    public static function diagnostics_today()
    {
        $counters = get_option('aitmt_runtime_diagnostics', array());
        $counters = is_array($counters) ? $counters : array();
        $day = gmdate('Y-m-d');
        $today = isset($counters[$day]) && is_array($counters[$day]) ? $counters[$day] : array();

        return array_map('absint', $today);
    }

    /**
     * Register the maintenance page.
     */
    public static function register_admin_page()
    {
        add_submenu_page(
            AITMT_PUBLIC_SLUG,
            __('Database Maintenance', 'ait-multilingual-translate'),
            __('Database Maintenance', 'ait-multilingual-translate'),
            'manage_options',
            'ait-multilingual-translate-database',
            array(__CLASS__, 'render_admin_page')
        );
    }

    /**
     * Render diagnostics and explicit maintenance controls.
     */
    public static function render_admin_page()
    {
        if (!current_user_can('manage_options')) {
            return;
        }

        delete_option('aitmt_scanner_maintenance_notice');

        $result = get_transient('aitmt_maintenance_result_' . get_current_user_id());
        $summary = self::database_summary();
        $operations = self::operations();
        $obsolete_preview = self::obsolete_scanner_candidates(20);
        ?>
        <div class="wrap aitmt-admin-wrap">
            <h1><?php esc_html_e('AIT Multilingual Translate - Database Maintenance', 'ait-multilingual-translate'); ?></h1>
            <p><?php esc_html_e('Inspect and clean translation data in small, resumable batches. Updates never delete translations automatically.', 'ait-multilingual-translate'); ?></p>

            <?php if (is_array($result)) : ?>
                <div class="notice <?php echo !empty($result['ok']) ? 'notice-success' : 'notice-warning'; ?> is-dismissible">
                    <p><?php echo esc_html((string) ($result['message'] ?? '')); ?></p>
                </div>
            <?php endif; ?>

            <table class="widefat striped" style="max-width:1100px">
                <tbody>
                    <tr><th><?php esc_html_e('Translation rows', 'ait-multilingual-translate'); ?></th><td><?php echo esc_html(number_format_i18n($summary['rows'])); ?></td></tr>
                    <tr><th><?php esc_html_e('Data size', 'ait-multilingual-translate'); ?></th><td><?php echo esc_html(size_format($summary['data_bytes'])); ?></td></tr>
                    <tr><th><?php esc_html_e('Index size', 'ait-multilingual-translate'); ?></th><td><?php echo esc_html(size_format($summary['index_bytes'])); ?></td></tr>
                    <tr><th><?php esc_html_e('Empty source type', 'ait-multilingual-translate'); ?></th><td><?php echo esc_html(number_format_i18n($summary['empty_source_type'])); ?></td></tr>
                    <tr><th><?php esc_html_e('Page-load rows', 'ait-multilingual-translate'); ?></th><td><?php echo esc_html(number_format_i18n($summary['page_load'])); ?></td></tr>
                    <tr><th><?php esc_html_e('Legacy runtime rows', 'ait-multilingual-translate'); ?></th><td><?php echo esc_html(number_format_i18n($summary['legacy_runtime'])); ?></td></tr>
                </tbody>
            </table>

            <h2><?php esc_html_e('Grouped statistics', 'ait-multilingual-translate'); ?></h2>
            <div style="display:grid;grid-template-columns:repeat(auto-fit,minmax(260px,1fr));gap:16px;max-width:1100px">
                <?php
                self::render_group_table(__('Source type', 'ait-multilingual-translate'), $summary['by_source_type']);
                self::render_group_table(__('Status', 'ait-multilingual-translate'), $summary['by_status']);
                self::render_group_table(__('Target language', 'ait-multilingual-translate'), $summary['by_target_language']);
                ?>
            </div>

            <h2><?php esc_html_e('Dry run and batched maintenance', 'ait-multilingual-translate'); ?></h2>
            <form method="post" action="<?php echo esc_url(admin_url('admin-post.php')); ?>" style="max-width:1100px">
                <input type="hidden" name="action" value="aitmt_database_maintenance">
                <?php wp_nonce_field('aitmt_database_maintenance'); ?>
                <table class="form-table">
                    <tr>
                        <th><label for="aitmt-maintenance-operation"><?php esc_html_e('Operation', 'ait-multilingual-translate'); ?></label></th>
                        <td>
                            <select id="aitmt-maintenance-operation" name="operation">
                                <?php foreach ($operations as $key => $label) : ?>
                                    <option value="<?php echo esc_attr($key); ?>"><?php echo esc_html($label); ?></option>
                                <?php endforeach; ?>
                            </select>
                        </td>
                    </tr>
                    <tr>
                        <th><label for="aitmt-maintenance-age"><?php esc_html_e('Minimum age in days', 'ait-multilingual-translate'); ?></label></th>
                        <td><input id="aitmt-maintenance-age" type="number" min="1" max="3650" name="age_days" value="30"></td>
                    </tr>
                    <tr>
                        <th><label for="aitmt-maintenance-batch"><?php esc_html_e('Batch size', 'ait-multilingual-translate'); ?></label></th>
                        <td><input id="aitmt-maintenance-batch" type="number" min="50" max="5000" name="batch_size" value="500"></td>
                    </tr>
                </table>
                <p>
                    <button class="button" name="maintenance_mode" value="dry-run"><?php esc_html_e('Dry run', 'ait-multilingual-translate'); ?></button>
                </p>
                <p>
                    <label>
                        <input type="checkbox" name="confirm_apply" value="1">
                        <?php esc_html_e('I understand that Apply changes one confirmed batch. Run Dry run first.', 'ait-multilingual-translate'); ?>
                    </label>
                </p>
                <p>
                    <button class="button button-primary" name="maintenance_mode" value="apply"><?php esc_html_e('Apply one batch', 'ait-multilingual-translate'); ?></button>
                </p>
            </form>

            <h2><?php esc_html_e('Scanner-derived Translation Memory', 'ait-multilingual-translate'); ?></h2>
            <p style="max-width:1100px">
                <?php esc_html_e('Use Obsolete scanner-derived translations in the maintenance selector to preview records that no longer match any current scanner source. Manual/imported translations and every still-valid Translation Memory entry are always preserved. Nothing is removed during a plugin update.', 'ait-multilingual-translate'); ?>
            </p>
            <p><strong><?php echo esc_html(sprintf(
                /* translators: 1: rows, 2: characters. */
                __('Detected obsolete scanner candidates: %1$s row(s), %2$s character(s).', 'ait-multilingual-translate'),
                number_format_i18n(absint($obsolete_preview['count'] ?? 0)),
                number_format_i18n(absint($obsolete_preview['characters'] ?? 0))
            )); ?></strong></p>
            <?php if (!empty($obsolete_preview['rows'])) : ?>
                <div style="overflow:auto;max-width:1100px;max-height:360px">
                    <table class="widefat striped">
                        <thead><tr>
                            <th><?php esc_html_e('Source type', 'ait-multilingual-translate'); ?></th>
                            <th><?php esc_html_e('Object', 'ait-multilingual-translate'); ?></th>
                            <th><?php esc_html_e('Field', 'ait-multilingual-translate'); ?></th>
                            <th><?php esc_html_e('Original text preview', 'ait-multilingual-translate'); ?></th>
                            <th><?php esc_html_e('Status', 'ait-multilingual-translate'); ?></th>
                        </tr></thead>
                        <tbody>
                        <?php foreach ($obsolete_preview['rows'] as $obsolete_row) : ?>
                            <tr>
                                <td><code><?php echo esc_html((string) ($obsolete_row['source_type'] ?? '')); ?></code></td>
                                <td><?php echo esc_html(number_format_i18n(absint($obsolete_row['object_id'] ?? 0))); ?></td>
                                <td><code><?php echo esc_html((string) ($obsolete_row['field_key'] ?? '')); ?></code></td>
                                <td><?php echo esc_html(wp_html_excerpt((string) ($obsolete_row['source_text'] ?? ''), 180, '...')); ?></td>
                                <td><code><?php echo esc_html((string) ($obsolete_row['status'] ?? '')); ?></code></td>
                            </tr>
                        <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            <?php endif; ?>

            <form method="post" action="<?php echo esc_url(admin_url('admin-post.php')); ?>" style="max-width:1100px">
                <input type="hidden" name="action" value="aitmt_rebuild_scanner_queue">
                <?php wp_nonce_field('aitmt_rebuild_scanner_queue'); ?>
                <p><?php esc_html_e('Rebuild scans the current site and queues only missing translations. It does not delete or overwrite published, draft, manual, or imported translations.', 'ait-multilingual-translate'); ?></p>
                <button class="button" type="submit"><?php esc_html_e('Rebuild scanner queue', 'ait-multilingual-translate'); ?></button>
            </form>

            <h2><?php esc_html_e('Runtime diagnostics (last 14 days)', 'ait-multilingual-translate'); ?></h2>
            <pre style="max-width:1100px;overflow:auto;background:#fff;padding:12px;border:1px solid #ccd0d4"><?php echo esc_html(wp_json_encode(get_option('aitmt_runtime_diagnostics', array()), JSON_PRETTY_PRINT)); ?></pre>
            <p><code>wp aitmt maintenance stats</code></p>
            <p><code>wp aitmt maintenance dry-run --operation=stale-page-load --age-days=30</code></p>
            <p><code>wp aitmt maintenance cleanup --operation=stale-page-load --age-days=30 --batch-size=1000 --yes</code></p>
            <p><code>wp aitmt maintenance classify --batch-size=1000 --yes</code></p>
            <p><code>wp aitmt maintenance index --dry-run</code></p>
            <p><code>wp aitmt maintenance analyze --yes</code> / <code>wp aitmt maintenance optimize --yes</code></p>
        </div>
        <?php
    }

    /**
     * Available explicit operations.
     */
    private static function operations()
    {
        $operations = array(
            'queued' => __('Clear all queued jobs (translations are preserved)', 'ait-multilingual-translate'),
            'failed' => __('Clear all failed jobs (published/manual/import translations are preserved)', 'ait-multilingual-translate'),
            'stale-page-load' => __('Delete stale page-load cache rows', 'ait-multilingual-translate'),
            'all-runtime' => __('Clear generated runtime translation cache (page-load and legacy rows)', 'ait-multilingual-translate'),
            'legacy-runtime' => __('Delete confirmed legacy runtime rows', 'ait-multilingual-translate'),
            'old-failed' => __('Delete old failed rows', 'ait-multilingual-translate'),
            'empty-source-type' => __('Delete old unclassified runtime candidates', 'ait-multilingual-translate'),
            'orphaned' => __('Delete rows linked to missing posts', 'ait-multilingual-translate'),
            'obsolete-scanner' => __('Delete obsolete scanner-derived translations', 'ait-multilingual-translate'),
            'classify' => __('Classify high-confidence legacy rows', 'ait-multilingual-translate'),
            'analyze' => __('Analyze translation table', 'ait-multilingual-translate'),
            'optimize' => __('Optimize translation table (manual only)', 'ait-multilingual-translate'),
        );
        foreach (self::scanner_source_types() as $source_type) {
            $operations['queued-source-' . $source_type] = sprintf(
                /* translators: %s: scanner source type. */
                __('Clear queued jobs from source: %s', 'ait-multilingual-translate'),
                $source_type
            );
        }

        return $operations;
    }

    /**
     * Render one compact grouped statistics table.
     *
     * @param string $heading Table heading.
     * @param array  $rows Grouped rows.
     */
    private static function render_group_table($heading, $rows)
    {
        ?>
        <table class="widefat striped">
            <thead><tr><th><?php echo esc_html($heading); ?></th><th><?php esc_html_e('Rows', 'ait-multilingual-translate'); ?></th></tr></thead>
            <tbody>
                <?php foreach ((array) $rows as $row) : ?>
                    <tr>
                        <td><code><?php echo esc_html('' === (string) $row['label'] ? '(empty)' : (string) $row['label']); ?></code></td>
                        <td><?php echo esc_html(number_format_i18n(absint($row['rows']))); ?></td>
                    </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
        <?php
    }

    /**
     * Handle one dry-run or one confirmed maintenance batch.
     */
    public static function handle_admin_action()
    {
        if (!current_user_can('manage_options')) {
            wp_die(esc_html__('Permission denied.', 'ait-multilingual-translate'));
        }

        check_admin_referer('aitmt_database_maintenance');
        $operation = isset($_POST['operation']) ? sanitize_key(wp_unslash((string) $_POST['operation'])) : '';
        $mode = isset($_POST['maintenance_mode']) ? sanitize_key(wp_unslash((string) $_POST['maintenance_mode'])) : 'dry-run';
        $age_days = isset($_POST['age_days']) ? max(1, min(3650, absint($_POST['age_days']))) : 30;
        $batch_size = isset($_POST['batch_size']) ? max(50, min(5000, absint($_POST['batch_size']))) : 500;
        $confirmed = !empty($_POST['confirm_apply']) && '1' === sanitize_text_field(wp_unslash((string) $_POST['confirm_apply']));
        $result = self::run_operation($operation, $mode, $age_days, $batch_size, $confirmed);

        set_transient('aitmt_maintenance_result_' . get_current_user_id(), $result, 5 * MINUTE_IN_SECONDS);
        wp_safe_redirect(admin_url('admin.php?page=ait-multilingual-translate-database'));
        exit;
    }

    /**
     * Re-run the current scanner without deleting or overwriting translations.
     */
    public static function handle_rebuild_scanner_queue()
    {
        if (!current_user_can('manage_options')) {
            wp_die(esc_html__('Permission denied.', 'ait-multilingual-translate'));
        }

        check_admin_referer('aitmt_rebuild_scanner_queue');
        if (!function_exists('aitmt_fallback_scan_site')) {
            $result = array('ok' => false, 'message' => __('The scanner is unavailable.', 'ait-multilingual-translate'));
        } else {
            $scan = aitmt_fallback_scan_site();
            $result = array(
                'ok' => !empty($scan['ok']),
                'message' => (string) ($scan['message'] ?? __('Scanner rebuild finished.', 'ait-multilingual-translate')),
            );
        }

        set_transient('aitmt_maintenance_result_' . get_current_user_id(), $result, 5 * MINUTE_IN_SECONDS);
        wp_safe_redirect(admin_url('admin.php?page=ait-multilingual-translate-database'));
        exit;
    }

    /**
     * Execute a controlled maintenance operation.
     */
    private static function run_operation($operation, $mode, $age_days, $batch_size, $confirmed)
    {
        $operations = self::operations();
        if (!isset($operations[$operation])) {
            return array('ok' => false, 'message' => __('Unknown maintenance operation.', 'ait-multilingual-translate'));
        }

        if (in_array($operation, array('analyze', 'optimize'), true)) {
            if ('apply' !== $mode || !$confirmed) {
                return array('ok' => true, 'message' => sprintf('%s is manual and will run only after Apply confirmation.', $operations[$operation]));
            }
            return self::run_table_command($operation);
        }

        if ('classify' === $operation) {
            $count = self::count_classifiable_rows($batch_size);
            if ('dry-run' === $mode) {
                self::store_dry_run_token($operation, $age_days, $count);
                return array('ok' => true, 'message' => sprintf('Dry run: up to %d high-confidence row(s) can be classified in the next batch.', $count));
            }
            if (!$confirmed || !self::consume_dry_run_token($operation, $age_days)) {
                return array('ok' => false, 'message' => __('Run Dry run first, then confirm Apply.', 'ait-multilingual-translate'));
            }
            $changed = self::classify_batch($batch_size);
            return array('ok' => true, 'message' => sprintf('Classified %d row(s). Repeat dry run to continue.', $changed));
        }

        $stats = self::operation_stats($operation, $age_days);
        if ('dry-run' === $mode) {
            self::store_dry_run_token($operation, $age_days, $stats['rows']);
            return array(
                'ok' => true,
                'message' => sprintf(
                    'Dry run: %1$d row(s), approximately %2$s. No data changed.',
                    $stats['rows'],
                    size_format($stats['characters'])
                ),
            );
        }

        if (!$confirmed || !self::consume_dry_run_token($operation, $age_days)) {
            return array('ok' => false, 'message' => __('Run Dry run first, then confirm Apply.', 'ait-multilingual-translate'));
        }

        $deleted = self::delete_batch($operation, $age_days, $batch_size);
        return array('ok' => true, 'message' => sprintf('Deleted %d row(s) in this batch. Repeat dry run to review what remains.', $deleted));
    }

    /**
     * Store short-lived proof that the administrator reviewed a dry run.
     */
    private static function store_dry_run_token($operation, $age_days, $count)
    {
        update_user_meta(
            get_current_user_id(),
            'aitmt_maintenance_dry_run',
            array(
                'operation' => sanitize_key($operation),
                'age_days' => absint($age_days),
                'count' => absint($count),
                'expires' => time() + 15 * MINUTE_IN_SECONDS,
            )
        );
    }

    /**
     * Validate and consume dry-run proof.
     */
    private static function consume_dry_run_token($operation, $age_days)
    {
        $token = get_user_meta(get_current_user_id(), 'aitmt_maintenance_dry_run', true);
        delete_user_meta(get_current_user_id(), 'aitmt_maintenance_dry_run');

        return is_array($token)
            && hash_equals((string) ($token['operation'] ?? ''), (string) $operation)
            && absint($token['age_days'] ?? 0) === absint($age_days)
            && absint($token['expires'] ?? 0) >= time();
    }

    /**
     * Build a safe SQL condition for maintenance candidates.
     */
    private static function operation_condition($operation, $age_days)
    {
        $cutoff = gmdate('Y-m-d H:i:s', time() - (max(1, absint($age_days)) * DAY_IN_SECONDS));

        if (0 === strpos($operation, 'queued-source-')) {
            $source_type = sanitize_key(substr($operation, strlen('queued-source-')));
            if (in_array($source_type, self::scanner_source_types(), true)) {
                return array("status = %s AND source_type = %s", array('queued', $source_type));
            }
            return array('1 = 0', array());
        }

        switch ($operation) {
            case 'queued':
                return array("status = %s", array('queued'));
            case 'failed':
                return array("status = %s", array('failed'));
            case 'stale-page-load':
                return array("source_type = %s AND updated_at < %s", array('page_load', $cutoff));
            case 'all-runtime':
                return array("source_type IN (%s, %s)", array('page_load', 'legacy_runtime'));
            case 'legacy-runtime':
                return array("source_type = %s AND updated_at < %s", array('legacy_runtime', $cutoff));
            case 'old-failed':
                return array("status = %s AND updated_at < %s", array('failed', $cutoff));
            case 'empty-source-type':
                return array(
                    "source_type = %s AND object_id = 0 AND field_key = %s AND status IN (%s, %s) AND updated_at < %s",
                    array('', '', 'queued', 'failed', $cutoff),
                );
            case 'orphaned':
                return array("object_id > 0 AND updated_at < %s", array($cutoff));
            default:
                return array('1 = 0', array());
        }
    }

    /**
     * Count rows and characters without modifying data.
     */
    private static function operation_stats($operation, $age_days)
    {
        global $wpdb;

        $table = self::translation_table_sql();
        if ('' === $table) {
            return array('rows' => 0, 'characters' => 0);
        }
        if ('stale-page-load' === $operation) {
            return self::stale_runtime_stats($age_days);
        }

        if ('orphaned' === $operation) {
            return self::orphaned_stats($age_days);
        }
        if ('obsolete-scanner' === $operation) {
            return self::obsolete_scanner_stats();
        }

        list($where, $args) = self::operation_condition($operation, $age_days);
        $sql = "SELECT COUNT(*) AS rows_count, COALESCE(SUM(CHAR_LENGTH(source_text)), 0) AS characters FROM {$table} WHERE {$where}";
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- The SQL template and condition are selected from closed internal lists; only values are variable and prepared.
        $prepared = empty($args) ? $sql : $wpdb->prepare($sql, ...$args);
        $row = $wpdb->get_row($prepared, ARRAY_A); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Dynamic condition is selected from a closed list and values are prepared.

        return array(
            'rows' => absint($row['rows_count'] ?? 0),
            'characters' => absint($row['characters'] ?? 0),
        );
    }

    /**
     * Delete one bounded batch. Orphan checks are performed through WordPress.
     */
    private static function delete_batch($operation, $age_days, $batch_size)
    {
        global $wpdb;

        $table = self::translation_table_sql();
        if ('' === $table) {
            return 0;
        }
        if ('stale-page-load' === $operation) {
            $rows = self::stale_runtime_rows($age_days, $batch_size);
        } elseif ('orphaned' === $operation) {
            $rows = self::orphaned_rows($age_days, $batch_size);
        } elseif ('obsolete-scanner' === $operation) {
            $rows = self::obsolete_scanner_rows($batch_size);
        } else {
            list($where, $args) = self::operation_condition($operation, $age_days);
            $sql = "SELECT id, object_id FROM {$table} WHERE {$where} ORDER BY id ASC LIMIT %d";
            $args[] = max(1, absint($batch_size));
            $rows = $wpdb->get_results($wpdb->prepare($sql, ...$args), ARRAY_A); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Dynamic condition is selected from a closed list and values are prepared.
        }
        $ids = array();

        foreach ((array) $rows as $row) {
            $ids[] = absint($row['id']);
        }

        if (empty($ids)) {
            return 0;
        }
        $placeholders = implode(',', array_fill(0, count($ids), '%d'));
        $usage_table = self::usage_table_sql();
        if ('' !== $usage_table) {
            $usage_query = $wpdb->prepare("DELETE FROM {$usage_table} WHERE translation_id IN ({$placeholders})", ...$ids);
            $wpdb->query($usage_query); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- IDs are prepared and table is validated.
        }
        $query = $wpdb->prepare("DELETE FROM {$table} WHERE id IN ({$placeholders})", ...$ids);

        return (int) $wpdb->query($query); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- IDs are prepared and table is validated.
    }

    /**
     * Return scanner source types, including auto-detected public post types.
     *
     * @return array<int,string>
     */
    private static function scanner_source_types()
    {
        $types = array('site', 'post', 'page', 'product', 'elementor', 'elementor_library', 'sectionbuilder', 'litho-mega-menu', 'taxonomy', 'menu', 'widget', 'page_meta');
        foreach ((array) get_post_types(array('public' => true), 'names') as $post_type) {
            $post_type = sanitize_key((string) $post_type);
            if ('' === $post_type || 'attachment' === $post_type) {
                continue;
            }
            $types[] = $post_type;
            $types[] = $post_type . '_meta';
        }
        if (function_exists('aitmt_fallback_current_scanner_manifest')) {
            $manifest = aitmt_fallback_current_scanner_manifest();
            $types = array_merge($types, (array) ($manifest['source_types'] ?? array()));
        }

        return array_values(array_unique(array_filter(array_map('sanitize_key', $types))));
    }

    /**
     * Build the current source hash manifest without writing queue rows.
     *
     * @return array<string,bool>
     */
    private static function current_scanner_hashes()
    {
        if (!function_exists('aitmt_fallback_current_scanner_manifest')) {
            return array();
        }
        $manifest = aitmt_fallback_current_scanner_manifest();

        return is_array($manifest['hashes'] ?? null) ? $manifest['hashes'] : array();
    }

    /**
     * Iterate scanner-derived rows that are no longer present in the manifest.
     *
     * Manual/imported rows are excluded in SQL and still-valid hashes are
     * excluded in PHP. This method never modifies data.
     *
     * @param int $result_limit Maximum obsolete rows to return; zero counts all.
     * @return array{rows:array<int,array<string,mixed>>,count:int,characters:int}
     */
    private static function obsolete_scanner_candidates($result_limit = 0)
    {
        global $wpdb;

        $table = self::translation_table_sql();
        $types = self::scanner_source_types();
        $current_hashes = self::current_scanner_hashes();
        $result = array('rows' => array(), 'count' => 0, 'characters' => 0);
        if ('' === $table || empty($types)) {
            return $result;
        }

        $type_placeholders = implode(',', array_fill(0, count($types), '%s'));
        $last_id = 0;
        $page_size = 1000;

        do {
            $args = array_merge($types, array('manual', 'import', 'manual', 'import', $last_id, $page_size));
            $sql = "SELECT id, source_hash, source_text, source_type, object_id, field_key, status, provider FROM {$table}
                WHERE source_type IN ({$type_placeholders})
                AND status NOT IN (%s, %s)
                AND provider NOT IN (%s, %s)
                AND id > %d
                ORDER BY id ASC LIMIT %d";
            $rows = $wpdb->get_results($wpdb->prepare($sql, ...$args), ARRAY_A); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Placeholders and table are controlled; all values are prepared.

            foreach ((array) $rows as $row) {
                $last_id = max($last_id, absint($row['id'] ?? 0));
                if (!self::is_obsolete_scanner_row($row, $current_hashes, $types)) {
                    continue;
                }
                $result['count']++;
                $result['characters'] += function_exists('mb_strlen')
                    ? mb_strlen((string) ($row['source_text'] ?? ''), 'UTF-8')
                    : strlen((string) ($row['source_text'] ?? ''));
                if (0 === $result_limit || count($result['rows']) < $result_limit) {
                    $result['rows'][] = $row;
                }
                if ($result_limit > 0 && count($result['rows']) >= $result_limit) {
                    break 2;
                }
            }
        } while (count($rows) === $page_size);

        return $result;
    }

    /**
     * Decide whether a scanner row is safe to offer for explicit cleanup.
     *
     * This guard is intentionally stricter than the SQL prefilter so the
     * preservation rules remain testable and cannot be bypassed by a future
     * query change.
     *
     * @param array              $row            Translation row.
     * @param array<string,bool> $current_hashes Hashes in the current scan.
     * @param array<int,string>  $scanner_types  Scanner-owned source types.
     * @return bool
     */
    public static function is_obsolete_scanner_row($row, $current_hashes, $scanner_types)
    {
        $row = is_array($row) ? $row : array();
        $current_hashes = is_array($current_hashes) ? $current_hashes : array();
        $scanner_types = is_array($scanner_types) ? $scanner_types : array();
        $source_hash = (string) ($row['source_hash'] ?? '');
        $source_type = sanitize_key((string) ($row['source_type'] ?? ''));
        $status = sanitize_key((string) ($row['status'] ?? ''));
        $provider = sanitize_key((string) ($row['provider'] ?? ''));

        if ('' === $source_hash || !in_array($source_type, $scanner_types, true)) {
            return false;
        }

        if (in_array($status, array('manual', 'import'), true) || in_array($provider, array('manual', 'import'), true)) {
            return false;
        }

        return !isset($current_hashes[$source_hash]);
    }

    /**
     * Preview obsolete scanner-derived rows.
     *
     * @return array{rows:int,characters:int}
     */
    private static function obsolete_scanner_stats()
    {
        $candidates = self::obsolete_scanner_candidates(0);

        return array(
            'rows' => absint($candidates['count'] ?? 0),
            'characters' => absint($candidates['characters'] ?? 0),
        );
    }

    /**
     * Select one bounded batch of obsolete scanner-derived rows.
     *
     * @param int $batch_size Batch size.
     * @return array<int,array<string,mixed>>
     */
    private static function obsolete_scanner_rows($batch_size)
    {
        $candidates = self::obsolete_scanner_candidates(max(1, absint($batch_size)));

        return (array) ($candidates['rows'] ?? array());
    }

    /**
     * Count page-load rows using last use time when metadata exists.
     */
    private static function stale_runtime_stats($age_days)
    {
        global $wpdb;

        $table = self::translation_table_sql();
        $usage_table = self::usage_table_sql();
        if ('' === $table || '' === $usage_table) {
            return array('rows' => 0, 'characters' => 0);
        }

        $cutoff = gmdate('Y-m-d H:i:s', time() - (max(1, absint($age_days)) * DAY_IN_SECONDS));
        $row = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT COUNT(*) AS rows_count, COALESCE(SUM(CHAR_LENGTH(t.source_text)), 0) AS characters
                FROM {$table} t
                LEFT JOIN {$usage_table} u ON u.translation_id = t.id
                WHERE t.source_type = %s AND COALESCE(u.last_used_at, t.updated_at) < %s",
                'page_load',
                $cutoff
            ),
            ARRAY_A
        ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Values are prepared and table names are validated.

        return array(
            'rows' => absint($row['rows_count'] ?? 0),
            'characters' => absint($row['characters'] ?? 0),
        );
    }

    /**
     * Select one resumable page-load cleanup batch.
     */
    private static function stale_runtime_rows($age_days, $batch_size)
    {
        global $wpdb;

        $table = self::translation_table_sql();
        $usage_table = self::usage_table_sql();
        if ('' === $table || '' === $usage_table) {
            return array();
        }

        $cutoff = gmdate('Y-m-d H:i:s', time() - (max(1, absint($age_days)) * DAY_IN_SECONDS));

        return $wpdb->get_results(
            $wpdb->prepare(
                "SELECT t.id, t.object_id
                FROM {$table} t
                LEFT JOIN {$usage_table} u ON u.translation_id = t.id
                WHERE t.source_type = %s AND COALESCE(u.last_used_at, t.updated_at) < %s
                ORDER BY t.id ASC LIMIT %d",
                'page_load',
                $cutoff,
                max(1, absint($batch_size))
            ),
            ARRAY_A
        ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Values are prepared and table names are validated.
    }

    /**
     * Count only rows whose referenced post no longer exists.
     */
    private static function orphaned_stats($age_days)
    {
        global $wpdb;

        $table = self::translation_table_sql();
        $posts = preg_match('/^[A-Za-z0-9_]+$/', $wpdb->posts) ? esc_sql($wpdb->posts) : '';
        if ('' === $table || '' === $posts) {
            return array('rows' => 0, 'characters' => 0);
        }

        $cutoff = gmdate('Y-m-d H:i:s', time() - (max(1, absint($age_days)) * DAY_IN_SECONDS));
        $row = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT COUNT(*) AS rows_count, COALESCE(SUM(CHAR_LENGTH(t.source_text)), 0) AS characters
                FROM {$table} t
                LEFT JOIN {$posts} p ON p.ID = t.object_id
                WHERE t.object_id > 0 AND p.ID IS NULL AND t.updated_at < %s",
                $cutoff
            ),
            ARRAY_A
        ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Value is prepared and table names are validated.

        return array(
            'rows' => absint($row['rows_count'] ?? 0),
            'characters' => absint($row['characters'] ?? 0),
        );
    }

    /**
     * Select one resumable orphan cleanup batch.
     */
    private static function orphaned_rows($age_days, $batch_size)
    {
        global $wpdb;

        $table = self::translation_table_sql();
        $posts = preg_match('/^[A-Za-z0-9_]+$/', $wpdb->posts) ? esc_sql($wpdb->posts) : '';
        if ('' === $table || '' === $posts) {
            return array();
        }

        $cutoff = gmdate('Y-m-d H:i:s', time() - (max(1, absint($age_days)) * DAY_IN_SECONDS));

        return $wpdb->get_results(
            $wpdb->prepare(
                "SELECT t.id, t.object_id
                FROM {$table} t
                LEFT JOIN {$posts} p ON p.ID = t.object_id
                WHERE t.object_id > 0 AND p.ID IS NULL AND t.updated_at < %s
                ORDER BY t.id ASC LIMIT %d",
                $cutoff,
                max(1, absint($batch_size))
            ),
            ARRAY_A
        ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Values are prepared and table names are validated.
    }

    /**
     * Count high-confidence rows in the next classification batch.
     */
    private static function count_classifiable_rows($batch_size)
    {
        global $wpdb;

        $table = self::translation_table_sql();
        if ('' === $table) {
            return 0;
        }

        return (int) $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(*) FROM (
                    SELECT id FROM {$table}
                    WHERE source_type = %s
                    AND (
                        object_id > 0
                        OR field_key LIKE %s
                        OR field_key LIKE %s
                        OR field_key LIKE %s
                        OR field_key LIKE %s
                        OR field_key LIKE %s
                        OR field_key LIKE %s
                        OR field_key LIKE %s
                    )
                    ORDER BY id ASC LIMIT %d
                ) candidates",
                '',
                'dom\_%',
                'elementor\_%',
                'wpfd\_%',
                'menu\_%',
                'widget\_%',
                'taxonomy\_%',
                'seo\_%',
                max(1, absint($batch_size))
            )
        ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Values are prepared and table is validated.
    }

    /**
     * Classify only rows with reliable metadata.
     */
    private static function classify_batch($batch_size)
    {
        global $wpdb;

        $table = self::translation_table_sql();
        if ('' === $table) {
            return 0;
        }
        $rows = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT id, object_id, field_key, status FROM {$table}
                WHERE source_type = %s
                AND (
                    object_id > 0
                    OR field_key LIKE %s
                    OR field_key LIKE %s
                    OR field_key LIKE %s
                    OR field_key LIKE %s
                    OR field_key LIKE %s
                    OR field_key LIKE %s
                    OR field_key LIKE %s
                )
                ORDER BY id ASC LIMIT %d",
                '',
                'dom\_%',
                'elementor\_%',
                'wpfd\_%',
                'menu\_%',
                'widget\_%',
                'taxonomy\_%',
                'seo\_%',
                max(1, absint($batch_size))
            ),
            ARRAY_A
        ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Values are prepared and table is validated.
        $changed = 0;

        foreach ((array) $rows as $row) {
            $source_type = '';
            if (!empty($row['object_id'])) {
                $post_type = get_post_type(absint($row['object_id']));
                if ($post_type) {
                    $source_type = sanitize_key($post_type);
                }
            } else {
                $field_key = (string) $row['field_key'];
                $field_types = array(
                    'dom_' => 'legacy_runtime',
                    'elementor_' => 'elementor',
                    'wpfd_' => 'wpfd_file',
                    'menu_' => 'menu',
                    'widget_' => 'widget',
                    'taxonomy_' => 'taxonomy',
                    'seo_' => 'seo',
                );
                foreach ($field_types as $prefix => $type) {
                    if (0 === strpos($field_key, $prefix)) {
                        $source_type = $type;
                        break;
                    }
                }
            }

            if ('' === $source_type) {
                continue;
            }

            $updated = $wpdb->update(
                $table,
                array('source_type' => $source_type),
                array('id' => absint($row['id'])),
                array('%s'),
                array('%d')
            );
            if (false !== $updated) {
                $changed += (int) $updated;
            }
        }

        return $changed;
    }

    /**
     * Basic table size and runtime summary.
     */
    public static function database_summary()
    {
        global $wpdb;

        $summary = array(
            'rows' => 0,
            'data_bytes' => 0,
            'index_bytes' => 0,
            'empty_source_type' => 0,
            'page_load' => 0,
            'legacy_runtime' => 0,
            'by_source_type' => array(),
            'by_status' => array(),
            'by_target_language' => array(),
        );
        $table = self::translation_table_sql();
        if ('' === $table) {
            return $summary;
        }

        $summary['rows'] = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$table}"); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- No user input and table is validated.
        $groups = $wpdb->get_results("SELECT source_type, COUNT(*) AS rows_count FROM {$table} GROUP BY source_type", ARRAY_A); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- No user input and table is validated.
        foreach ((array) $groups as $group) {
            $type = (string) ($group['source_type'] ?? '');
            $summary['by_source_type'][] = array(
                'label' => $type,
                'rows' => absint($group['rows_count'] ?? 0),
            );
            if ('' === $type) {
                $summary['empty_source_type'] = absint($group['rows_count'] ?? 0);
            } elseif (isset($summary[$type])) {
                $summary[$type] = absint($group['rows_count'] ?? 0);
            }
        }

        $status_groups = $wpdb->get_results("SELECT status AS label, COUNT(*) AS rows_count FROM {$table} GROUP BY status ORDER BY rows_count DESC", ARRAY_A); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- No user input and table is validated.
        foreach ((array) $status_groups as $group) {
            $summary['by_status'][] = array(
                'label' => (string) ($group['label'] ?? ''),
                'rows' => absint($group['rows_count'] ?? 0),
            );
        }

        $language_groups = $wpdb->get_results("SELECT target_language AS label, COUNT(*) AS rows_count FROM {$table} GROUP BY target_language ORDER BY rows_count DESC", ARRAY_A); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- No user input and table is validated.
        foreach ((array) $language_groups as $group) {
            $summary['by_target_language'][] = array(
                'label' => (string) ($group['label'] ?? ''),
                'rows' => absint($group['rows_count'] ?? 0),
            );
        }

        $database = sanitize_text_field((string) DB_NAME);
        $size = $wpdb->get_row(
            $wpdb->prepare(
                'SELECT COALESCE(data_length, 0) AS data_bytes, COALESCE(index_length, 0) AS index_bytes FROM information_schema.tables WHERE table_schema = %s AND table_name = %s LIMIT 1',
                $database,
                $wpdb->prefix . 'aitmt_translations'
            ),
            ARRAY_A
        );
        if (is_array($size)) {
            $summary['data_bytes'] = absint($size['data_bytes'] ?? 0);
            $summary['index_bytes'] = absint($size['index_bytes'] ?? 0);
        }

        return $summary;
    }

    /**
     * Run ANALYZE or OPTIMIZE only after explicit confirmation.
     */
    private static function run_table_command($operation)
    {
        global $wpdb;

        $table = self::translation_table_sql();
        if ('' === $table) {
            return array('ok' => false, 'message' => __('Translation table is unavailable.', 'ait-multilingual-translate'));
        }
        $command = 'optimize' === $operation ? 'OPTIMIZE' : 'ANALYZE';
        $wpdb->query("{$command} TABLE {$table}"); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Command is selected from a closed list and table is validated.

        return array('ok' => true, 'message' => sprintf('%s TABLE completed.', $command));
    }

    /**
     * Daily runtime cleanup is opt-in and bounded.
     */
    public static function sync_cleanup_schedule()
    {
        $options = function_exists('aitmt_fallback_options') ? aitmt_fallback_options() : array();
        $enabled = '1' === (string) ($options['runtime_auto_cleanup'] ?? '0');
        $scheduled = wp_next_scheduled(self::CRON_HOOK);

        if ($enabled && !$scheduled) {
            wp_schedule_event(time() + HOUR_IN_SECONDS, 'daily', self::CRON_HOOK);
        } elseif (!$enabled && $scheduled) {
            wp_clear_scheduled_hook(self::CRON_HOOK);
        }
    }

    /**
     * Delete at most 250 expired runtime rows per daily run.
     */
    public static function scheduled_cleanup()
    {
        $options = function_exists('aitmt_fallback_options') ? aitmt_fallback_options() : array();
        if ('1' !== (string) ($options['runtime_auto_cleanup'] ?? '0')) {
            return;
        }
        $days = max(1, absint($options['runtime_cache_ttl_days'] ?? 30));
        self::delete_batch('stale-page-load', $days, 250);
    }

    /**
     * Register WP-CLI maintenance commands.
     */
    public static function register_cli_command()
    {
        WP_CLI::add_command('aitmt maintenance', array(__CLASS__, 'cli'));
    }

    /**
     * WP-CLI dispatcher.
     *
     * ## EXAMPLES
     *
     *     wp aitmt maintenance stats
     *     wp aitmt maintenance dry-run --operation=stale-page-load --age-days=30
     *     wp aitmt maintenance cleanup --operation=stale-page-load --age-days=30 --batch-size=1000 --yes
     *
     * @param array $args Positional arguments.
     * @param array $assoc_args Named arguments.
     */
    public static function cli($args, $assoc_args)
    {
        $command = sanitize_key((string) ($args[0] ?? 'stats'));
        $operation = sanitize_key((string) ($assoc_args['operation'] ?? 'stale-page-load'));
        $age_days = max(1, absint($assoc_args['age-days'] ?? 30));
        $batch_size = max(1, min(10000, absint($assoc_args['batch-size'] ?? 1000)));

        if ('stats' === $command) {
            WP_CLI::print_value(self::database_summary(), array('format' => 'json'));
            return;
        }

        if ('dry-run' === $command) {
            $stats = self::operation_stats($operation, $age_days);
            WP_CLI::print_value($stats, array('format' => 'json'));
            return;
        }

        if ('cleanup' === $command) {
            WP_CLI::confirm('Delete one bounded batch of matching rows?', $assoc_args);
            $deleted = self::delete_batch($operation, $age_days, $batch_size);
            WP_CLI::success(sprintf('Deleted %d row(s). Run dry-run again to inspect remaining candidates.', $deleted));
            return;
        }

        if ('classify' === $command) {
            if (empty($assoc_args['yes'])) {
                WP_CLI::line(sprintf('Dry run: up to %d row(s) are classifiable in the next batch.', self::count_classifiable_rows($batch_size)));
                return;
            }
            WP_CLI::confirm('Classify one bounded batch of high-confidence legacy rows?', $assoc_args);
            WP_CLI::success(sprintf('Classified %d row(s).', self::classify_batch($batch_size)));
            return;
        }

        if ('analyze' === $command || 'optimize' === $command) {
            WP_CLI::confirm(strtoupper($command) . ' the translation table?', $assoc_args);
            $result = self::run_table_command($command);
            WP_CLI::success($result['message']);
            return;
        }

        if ('index' === $command) {
            self::cli_index($assoc_args);
            return;
        }

        if ('self-test' === $command) {
            self::cli_self_test();
            return;
        }

        WP_CLI::error('Unknown command. Use stats, dry-run, cleanup, classify, index, analyze, optimize, or self-test.');
    }

    /**
     * Verify/add the fixed unique lookup index after collision checks.
     */
    private static function cli_index($assoc_args)
    {
        global $wpdb;

        $table = self::translation_table_sql();
        $indexes = $wpdb->get_results("SHOW INDEX FROM {$table}", ARRAY_A); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table is validated.
        foreach ((array) $indexes as $index) {
            if ('aitmt_lookup' === strtolower((string) ($index['Key_name'] ?? ''))) {
                WP_CLI::success('Unique lookup index already exists.');
                return;
            }
        }

        $duplicates = (int) $wpdb->get_var(
            "SELECT COUNT(*) FROM (
                SELECT source_hash, source_language, target_language, context
                FROM {$table}
                GROUP BY source_hash, source_language, target_language, context
                HAVING COUNT(*) > 1
            ) duplicate_groups"
        ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- No user input and table is validated.
        WP_CLI::line(sprintf('Duplicate lookup groups: %d.', $duplicates));

        if ($duplicates > 0 || empty($assoc_args['yes'])) {
            if ($duplicates > 0) {
                WP_CLI::warning('Index was not added because collisions exist.');
            } else {
                WP_CLI::line('Dry run only. Add --yes to create the index.');
            }
            return;
        }

        WP_CLI::confirm('Add the unique lookup index? This may lock a large table.', $assoc_args);
        $wpdb->query("ALTER TABLE {$table} ADD UNIQUE KEY aitmt_lookup (source_hash, source_language, target_language, context)"); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Fixed SQL and validated table.
        WP_CLI::success('Unique lookup index added.');
    }

    /**
     * Lightweight safety checks that do not call a provider.
     */
    private static function cli_self_test()
    {
        $checks = array(
            'unicode_whitespace_normalized' => function_exists('aitmt_fallback_normalize_text') && 'Hello world' === aitmt_fallback_normalize_text(" Hello\xc2\xa0  world "),
            'data_uri_rejected' => function_exists('aitmt_fallback_non_translatable_reason') && 'data_uri' === aitmt_fallback_non_translatable_reason('data:text/plain;base64,SGVsbG8='),
            'unknown_source_rejected' => function_exists('aitmt_fallback_normalize_source_type') && '' === aitmt_fallback_normalize_source_type('unknown'),
            'runtime_default_off' => function_exists('aitmt_fallback_default_options') && '0' === (string) (aitmt_fallback_default_options()['runtime_page_translation'] ?? '1'),
            'runtime_storage_default_off' => function_exists('aitmt_fallback_default_options') && '0' === (string) (aitmt_fallback_default_options()['runtime_db_storage'] ?? '1'),
            'background_default_off' => function_exists('aitmt_fallback_default_options') && '0' === (string) (aitmt_fallback_default_options()['cron_enabled'] ?? '1'),
            'scan_on_save_default_off' => function_exists('aitmt_fallback_default_options') && '0' === (string) (aitmt_fallback_default_options()['scan_on_save'] ?? '1'),
            'unknown_not_allowed' => function_exists('aitmt_fallback_is_valid_queue_source_type') && !aitmt_fallback_is_valid_queue_source_type('unknown'),
            'woocommerce_private_detected' => function_exists('aitmt_fallback_is_woocommerce_private_request') && self::with_request_uri('/checkout/', 'aitmt_fallback_is_woocommerce_private_request'),
        );

        foreach ($checks as $name => $passed) {
            if (!$passed) {
                WP_CLI::error('Self-test failed: ' . $name);
            }
            WP_CLI::line('PASS ' . $name);
        }
        WP_CLI::success('Runtime safety self-test passed.');
    }

    /**
     * Run a request-path predicate without permanently changing the CLI request.
     *
     * @param string   $request_uri Request path.
     * @param callable $callback    Predicate to execute.
     * @return bool
     */
    private static function with_request_uri($request_uri, $callback)
    {
        $had_request_uri = isset($_SERVER['REQUEST_URI']);
        $previous = $had_request_uri ? sanitize_text_field(wp_unslash($_SERVER['REQUEST_URI'])) : null;
        $_SERVER['REQUEST_URI'] = $request_uri;
        $result = (bool) call_user_func($callback);

        if ($had_request_uri) {
            $_SERVER['REQUEST_URI'] = $previous;
        } else {
            unset($_SERVER['REQUEST_URI']);
        }

        return $result;
    }
}