<?php
/**
 * Image Scanner
 * Scans posts/pages for unprotected <img> tags and provides
 * bulk upload + auto-replace functionality
 */
class Affirm_Scanner {

    public function __construct() {
        add_action('wp_ajax_affirm_scan_images', array($this, 'ajax_scan_images'));
        add_action('wp_ajax_affirm_get_scan_results', array($this, 'ajax_get_scan_results'));
        add_action('wp_ajax_affirm_protect_image', array($this, 'ajax_protect_image'));
        add_action('wp_ajax_affirm_protect_bulk', array($this, 'ajax_protect_bulk'));
        add_action('wp_ajax_affirm_dismiss_image', array($this, 'ajax_dismiss_image'));
        add_action('wp_ajax_affirm_bulk_process_status', array($this, 'ajax_bulk_process_status'));
        add_action('wp_ajax_affirm_get_dashboard', array($this, 'ajax_get_dashboard'));
        add_action('wp_ajax_affirm_upload_image', array($this, 'ajax_upload_image'));
        add_action('wp_ajax_affirm_get_vault_images', array($this, 'ajax_get_vault_images'));

        // WP Cron hook for background bulk processing
        add_action('affirm_bulk_process_batch', array($this, 'process_bulk_batch'));
    }

    /**
     * AJAX: Scan all posts/pages for unprotected images
     */
    public function ajax_scan_images() {
        check_ajax_referer('affirm_admin_nonce', 'nonce');
        if (!current_user_can('manage_options')) {
            wp_send_json_error(array('message' => 'Unauthorized'));
        }

        global $wpdb;
        $table = $wpdb->prefix . 'affirm_scanned_images';

        // Get all published posts and pages
        $post_types = apply_filters('affirm_scannable_post_types', array('post', 'page', 'product'));
        $posts = get_posts(array(
            'post_type'      => $post_types,
            'post_status'    => 'publish',
            'posts_per_page' => -1,
            'fields'         => 'ids',
        ));

        $found = 0;
        $skipped = 0;

        foreach ($posts as $post_id) {
            $content = get_post_field('post_content', $post_id);
            if (empty($content)) continue;

            // Find all <img> tags
            preg_match_all('/<img[^>]+>/i', $content, $matches);
            if (empty($matches[0])) continue;

            foreach ($matches[0] as $img_tag) {
                // Skip images already protected by aFFirmFirst
                if (strpos($img_tag, 'data-affirm-id') !== false) {
                    $skipped++;
                    continue;
                }
                if (strpos($img_tag, 'affirmfirst-') !== false) {
                    $skipped++;
                    continue;
                }

                // Extract src
                if (!preg_match('/src=["\']([^"\']+)["\']/i', $img_tag, $src_match)) {
                    continue;
                }
                $src = $src_match[1];

                // Skip data URIs, SVGs, and external tracking pixels
                if (strpos($src, 'data:') === 0) continue;
                if (strpos($src, '.svg') !== false) continue;
                if (strpos($src, 'pixel') !== false || strpos($src, 'tracking') !== false) continue;

                // Extract additional attributes
                $alt = '';
                if (preg_match('/alt=["\']([^"\']*?)["\']/i', $img_tag, $alt_match)) {
                    $alt = $alt_match[1];
                }
                $width = 0;
                if (preg_match('/width=["\']?(\d+)/i', $img_tag, $w_match)) {
                    $width = (int) $w_match[1];
                }
                $height = 0;
                if (preg_match('/height=["\']?(\d+)/i', $img_tag, $h_match)) {
                    $height = (int) $h_match[1];
                }

                // Check if already in scan results. Custom table; $table from $wpdb->prefix (safe), names cannot be placeholders.
                // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
                $exists = $wpdb->get_var($wpdb->prepare(
                    "SELECT id FROM $table WHERE post_id = %d AND image_src = %s",
                    $post_id, $src
                ));
                // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

                if ($exists) {
                    // Update the img_tag in case content changed. Custom table write; object caching not applicable.
                    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                    $wpdb->update($table, array(
                        'img_tag'    => $img_tag,
                        'alt_text'   => $alt,
                        'width'      => $width,
                        'height'     => $height,
                        'scanned_at' => current_time('mysql'),
                    ), array('id' => $exists));
                    continue;
                }

                // Insert new scan result. Custom table write; object caching not applicable.
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                $wpdb->insert($table, array(
                    'post_id'    => $post_id,
                    'image_src'  => $src,
                    'img_tag'    => $img_tag,
                    'alt_text'   => $alt,
                    'width'      => $width,
                    'height'     => $height,
                    'status'     => 'pending',
                    'scanned_at' => current_time('mysql'),
                ));

                $found++;
            }
        }

        // Clean up entries for posts that no longer exist or images removed
        $this->cleanup_stale_entries();

        // Get summary
        $summary = $this->get_scan_summary();

        wp_send_json_success(array(
            'newFound'  => $found,
            'skipped'   => $skipped,
            'postsScanned' => count($posts),
            'summary'   => $summary,
        ));
    }

    /**
     * AJAX: Get current scan results with pagination
     */
    public function ajax_get_scan_results() {
        check_ajax_referer('affirm_admin_nonce', 'nonce');
        if (!current_user_can('manage_options')) {
            wp_send_json_error(array('message' => 'Unauthorized'));
        }

        global $wpdb;

        $page = isset($_POST['page']) ? max(1, (int) $_POST['page']) : 1;
        $per_page = isset($_POST['per_page']) ? max(1, min(100, (int) $_POST['per_page'])) : 20;
        $status_filter = isset($_POST['status_filter']) ? sanitize_text_field(wp_unslash($_POST['status_filter'])) : 'all';
        $search = isset($_POST['search']) ? sanitize_text_field(wp_unslash($_POST['search'])) : '';

        $has_status = ($status_filter !== 'all');
        $has_search = ($search !== '');
        $like = $has_search ? '%' . $wpdb->esc_like($search) . '%' : '';
        $offset = ($page - 1) * $per_page;

        // Get total count. Each query is a fully literal string; user values are
        // passed only as prepared placeholders. Custom table reads; object caching not applicable.
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        if ($has_status && $has_search) {
            $total = (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM {$wpdb->prefix}affirm_scanned_images s LEFT JOIN {$wpdb->posts} p ON s.post_id = p.ID WHERE 1=1 AND s.status = %s AND (s.image_src LIKE %s OR p.post_title LIKE %s)",
                $status_filter, $like, $like
            ));
        } elseif ($has_status) {
            $total = (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM {$wpdb->prefix}affirm_scanned_images s LEFT JOIN {$wpdb->posts} p ON s.post_id = p.ID WHERE 1=1 AND s.status = %s",
                $status_filter
            ));
        } elseif ($has_search) {
            $total = (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM {$wpdb->prefix}affirm_scanned_images s LEFT JOIN {$wpdb->posts} p ON s.post_id = p.ID WHERE 1=1 AND (s.image_src LIKE %s OR p.post_title LIKE %s)",
                $like, $like
            ));
        } else {
            $total = (int) $wpdb->get_var(
                "SELECT COUNT(*) FROM {$wpdb->prefix}affirm_scanned_images s LEFT JOIN {$wpdb->posts} p ON s.post_id = p.ID WHERE 1=1"
            );
        }

        // Get paginated results.
        if ($has_status && $has_search) {
            $results = $wpdb->get_results($wpdb->prepare(
                "SELECT s.*, p.post_title, p.post_type FROM {$wpdb->prefix}affirm_scanned_images s LEFT JOIN {$wpdb->posts} p ON s.post_id = p.ID WHERE 1=1 AND s.status = %s AND (s.image_src LIKE %s OR p.post_title LIKE %s) ORDER BY s.scanned_at DESC LIMIT %d OFFSET %d",
                $status_filter, $like, $like, $per_page, $offset
            ));
        } elseif ($has_status) {
            $results = $wpdb->get_results($wpdb->prepare(
                "SELECT s.*, p.post_title, p.post_type FROM {$wpdb->prefix}affirm_scanned_images s LEFT JOIN {$wpdb->posts} p ON s.post_id = p.ID WHERE 1=1 AND s.status = %s ORDER BY s.scanned_at DESC LIMIT %d OFFSET %d",
                $status_filter, $per_page, $offset
            ));
        } elseif ($has_search) {
            $results = $wpdb->get_results($wpdb->prepare(
                "SELECT s.*, p.post_title, p.post_type FROM {$wpdb->prefix}affirm_scanned_images s LEFT JOIN {$wpdb->posts} p ON s.post_id = p.ID WHERE 1=1 AND (s.image_src LIKE %s OR p.post_title LIKE %s) ORDER BY s.scanned_at DESC LIMIT %d OFFSET %d",
                $like, $like, $per_page, $offset
            ));
        } else {
            $results = $wpdb->get_results($wpdb->prepare(
                "SELECT s.*, p.post_title, p.post_type FROM {$wpdb->prefix}affirm_scanned_images s LEFT JOIN {$wpdb->posts} p ON s.post_id = p.ID WHERE 1=1 ORDER BY s.scanned_at DESC LIMIT %d OFFSET %d",
                $per_page, $offset
            ));
        }
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

        // Get summary
        $summary = $this->get_scan_summary();

        wp_send_json_success(array(
            'results' => $results,
            'total'   => (int) $total,
            'page'    => $page,
            'pages'   => ceil($total / $per_page),
            'summary' => $summary,
        ));
    }

    /**
     * AJAX: Protect a single image (upload to vault + replace in content)
     */
    public function ajax_protect_image() {
        check_ajax_referer('affirm_admin_nonce', 'nonce');
        if (!current_user_can('manage_options')) {
            wp_send_json_error(array('message' => 'Unauthorized'));
        }

        $scan_id = isset($_POST['scan_id']) ? (int) $_POST['scan_id'] : 0;
        if (!$scan_id) {
            wp_send_json_error(array('message' => 'Invalid scan ID'));
        }

        $result = $this->protect_single_image($scan_id);

        if (is_wp_error($result)) {
            wp_send_json_error(array('message' => $result->get_error_message()));
        }

        wp_send_json_success($result);
    }

    /**
     * AJAX: Start bulk protection (schedules background processing)
     */
    public function ajax_protect_bulk() {
        check_ajax_referer('affirm_admin_nonce', 'nonce');
        if (!current_user_can('manage_options')) {
            wp_send_json_error(array('message' => 'Unauthorized'));
        }

        global $wpdb;
        $table = $wpdb->prefix . 'affirm_scanned_images';

        $scan_ids = isset($_POST['scan_ids']) ? array_map('intval', (array) $_POST['scan_ids']) : array();
        $all_pending = isset($_POST['all_pending']) && $_POST['all_pending'] === 'true';

        if ($all_pending) {
            // Custom table read; $table from $wpdb->prefix (safe), names cannot be placeholders.
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
            $scan_ids = $wpdb->get_col("SELECT id FROM $table WHERE status = 'pending'");
        }

        if (empty($scan_ids)) {
            wp_send_json_error(array('message' => 'No images selected'));
        }

        // Mark selected images as 'queued'. $ids_placeholder is a list of %d placeholders; values prepared safely.
        $ids_placeholder = implode(',', array_fill(0, count($scan_ids), '%d'));
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
        $wpdb->query($wpdb->prepare(
            "UPDATE $table SET status = 'queued' WHERE id IN ($ids_placeholder) AND status = 'pending'",
            $scan_ids
        ));
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare

        // Store bulk job info as a transient so it auto-expires and never leaves
        // an orphaned option behind. Kept for 1 hour for progress polling.
        $job_id = 'affirm_bulk_' . time();
        set_transient($job_id, array(
            'total'     => count($scan_ids),
            'processed' => 0,
            'succeeded' => 0,
            'failed'    => 0,
            'started'   => time(),
        ), HOUR_IN_SECONDS);
        update_option('affirm_current_bulk_job', $job_id);

        // Schedule immediate cron event for batch processing
        if (!wp_next_scheduled('affirm_bulk_process_batch')) {
            wp_schedule_single_event(time(), 'affirm_bulk_process_batch');
        }

        // Also process first batch immediately via loopback
        $this->trigger_async_batch();

        wp_send_json_success(array(
            'jobId'   => $job_id,
            'queued'  => count($scan_ids),
            'message' => 'Bulk protection started',
        ));
    }

    /**
     * AJAX: Dismiss a scanned image (mark as ignored)
     */
    public function ajax_dismiss_image() {
        check_ajax_referer('affirm_admin_nonce', 'nonce');
        if (!current_user_can('manage_options')) {
            wp_send_json_error(array('message' => 'Unauthorized'));
        }

        global $wpdb;
        $table = $wpdb->prefix . 'affirm_scanned_images';

        $scan_id = isset($_POST['scan_id']) ? (int) $_POST['scan_id'] : 0;
        if (!$scan_id) {
            wp_send_json_error(array('message' => 'Invalid scan ID'));
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $wpdb->update($table, array('status' => 'dismissed'), array('id' => $scan_id));

        wp_send_json_success(array('message' => 'Image dismissed'));
    }

    /**
     * AJAX: Get bulk process status
     */
    public function ajax_bulk_process_status() {
        check_ajax_referer('affirm_admin_nonce', 'nonce');
        if (!current_user_can('manage_options')) {
            wp_send_json_error(array('message' => 'Unauthorized'));
        }

        $job_id = get_option('affirm_current_bulk_job', '');
        if (!$job_id) {
            wp_send_json_success(array('active' => false));
        }

        $job = get_transient($job_id);
        if (empty($job)) {
            wp_send_json_success(array('active' => false));
        }

        $is_complete = ($job['processed'] >= $job['total']);

        wp_send_json_success(array(
            'active'    => !$is_complete,
            'jobId'     => $job_id,
            'total'     => $job['total'],
            'processed' => $job['processed'],
            'succeeded' => $job['succeeded'],
            'failed'    => $job['failed'],
            'percent'   => $job['total'] > 0 ? round(($job['processed'] / $job['total']) * 100) : 0,
        ));
    }

    /**
     * WP Cron: Process a batch of queued images
     */
    public function process_bulk_batch() {
        global $wpdb;
        $table = $wpdb->prefix . 'affirm_scanned_images';

        $batch_size = 5; // Process 5 at a time to avoid timeouts
        // Custom table read; $table from $wpdb->prefix (safe), names cannot be placeholders.
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $queued = $wpdb->get_results($wpdb->prepare(
            "SELECT id FROM $table WHERE status = 'queued' ORDER BY id ASC LIMIT %d",
            $batch_size
        ));
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

        if (empty($queued)) {
            // All done — clear the pointer. The job transient is left to expire
            // on its own so status checks still work for up to an hour.
            $job_id = get_option('affirm_current_bulk_job', '');
            if ($job_id) {
                delete_option('affirm_current_bulk_job');
            }
            return;
        }

        $job_id = get_option('affirm_current_bulk_job', '');
        $job = $job_id ? get_transient($job_id) : false;
        if (!is_array($job)) $job = array();

        foreach ($queued as $item) {
            $result = $this->protect_single_image($item->id);

            if (!empty($job)) {
                $job['processed']++;
                if (is_wp_error($result)) {
                    $job['failed']++;
                } else {
                    $job['succeeded']++;
                }
                set_transient($job_id, $job, HOUR_IN_SECONDS);
            }
        }

        // Check if more items remain. Custom table read; $table from $wpdb->prefix (safe).
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $remaining = $wpdb->get_var("SELECT COUNT(*) FROM $table WHERE status = 'queued'");
        if ($remaining > 0) {
            // Schedule next batch
            wp_schedule_single_event(time() + 2, 'affirm_bulk_process_batch');
            $this->trigger_async_batch();
        }
    }

    /**
     * Protect a single image: upload to vault, replace in post content
     */
    private function protect_single_image($scan_id) {
        global $wpdb;
        $table = $wpdb->prefix . 'affirm_scanned_images';

        // Custom table read; $table from $wpdb->prefix (safe), names cannot be placeholders.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $entry = $wpdb->get_row($wpdb->prepare("SELECT * FROM $table WHERE id = %d", $scan_id));
        if (!$entry) {
            return new WP_Error('not_found', 'Scan entry not found');
        }

        if ($entry->status === 'protected' || $entry->status === 'dismissed') {
            return new WP_Error('already_processed', 'Image already processed');
        }

        // Mark as processing
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $wpdb->update($table, array('status' => 'processing'), array('id' => $scan_id));

        // Resolve relative URLs to absolute
        $image_url = $entry->image_src;
        if (strpos($image_url, '//') === 0) {
            $image_url = 'https:' . $image_url;
        } elseif (strpos($image_url, '/') === 0) {
            $image_url = home_url($image_url);
        }

        // Upload to aFFirmFirst vault via plugin API
        $upload_result = Affirm_Api::upload_from_url($image_url, basename($image_url));

        if (is_wp_error($upload_result)) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->update($table, array(
                'status'        => 'failed',
                'error_message' => $upload_result->get_error_message(),
            ), array('id' => $scan_id));
            return $upload_result;
        }

        $affirm_image_id = isset($upload_result['id']) ? $upload_result['id'] : '';
        if (!$affirm_image_id) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->update($table, array(
                'status'        => 'failed',
                'error_message' => 'No image ID returned from API',
            ), array('id' => $scan_id));
            return new WP_Error('no_id', 'No image ID returned from API');
        }

        // Replace the <img> tag in the post content with protected embed
        $post = get_post($entry->post_id);
        if (!$post) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->update($table, array(
                'status'        => 'failed',
                'error_message' => 'Post not found',
            ), array('id' => $scan_id));
            return new WP_Error('post_not_found', 'Post not found');
        }

        $old_tag = $entry->img_tag;
        $new_tag = sprintf(
            '<img src="%s" alt="%s" data-affirm-id="%s" data-affirm-protected="true" width="%s" height="%s" />',
            esc_url($image_url),
            esc_attr($entry->alt_text),
            esc_attr($affirm_image_id),
            esc_attr($entry->width ? $entry->width : 'auto'),
            esc_attr($entry->height ? $entry->height : 'auto')
        );

        $new_content = str_replace($old_tag, $new_tag, $post->post_content);

        if ($new_content !== $post->post_content) {
            wp_update_post(array(
                'ID'           => $post->ID,
                'post_content' => $new_content,
            ));
        }

        // Sync the new image to media library
        $this->sync_protected_image($affirm_image_id, $upload_result);

        // Update scan entry
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $wpdb->update($table, array(
            'status'          => 'protected',
            'affirm_image_id' => $affirm_image_id,
            'protected_at'    => current_time('mysql'),
        ), array('id' => $scan_id));

        return array(
            'scanId'       => $scan_id,
            'affirmId'     => $affirm_image_id,
            'postId'       => $entry->post_id,
            'message'      => 'Image protected successfully',
        );
    }

    /**
     * Create a virtual media library attachment for the newly protected image
     */
    private function sync_protected_image($affirm_image_id, $image_data) {
        // Check if attachment already exists
        $existing = get_posts(array(
            'post_type'      => 'attachment',
            'meta_key'       => 'affirm_image_id',
            'meta_value'     => $affirm_image_id,
            'posts_per_page' => 1,
        ));

        if (!empty($existing)) return;

        $filename = isset($image_data['originalFilename']) ? $image_data['originalFilename'] : 'image.webp';
        $mime = isset($image_data['mimeType']) ? $image_data['mimeType'] : 'image/webp';

        $attachment_id = wp_insert_attachment(array(
            'post_title'     => sanitize_file_name(pathinfo($filename, PATHINFO_FILENAME)),
            'post_mime_type' => $mime,
            'post_status'    => 'inherit',
            'guid'           => 'affirmfirst://' . $affirm_image_id,
        ));

        if (is_wp_error($attachment_id)) return;

        update_post_meta($attachment_id, 'affirm_image_id', $affirm_image_id);
        update_post_meta($attachment_id, '_wp_attached_file', 'affirmfirst/' . $affirm_image_id);

        $width = isset($image_data['width']) ? (int) $image_data['width'] : 0;
        $height = isset($image_data['height']) ? (int) $image_data['height'] : 0;

        update_post_meta($attachment_id, '_wp_attachment_metadata', array(
            'width'  => $width,
            'height' => $height,
            'file'   => 'affirmfirst/' . $affirm_image_id,
            'sizes'  => array(),
        ));
    }

    /**
     * Get scan summary counts
     */
    private function get_scan_summary() {
        global $wpdb;
        $table = $wpdb->prefix . 'affirm_scanned_images';

        // Custom table summary counts; $table from $wpdb->prefix (safe), names cannot be placeholders.
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $total = (int) $wpdb->get_var("SELECT COUNT(*) FROM $table");
        $pending = (int) $wpdb->get_var("SELECT COUNT(*) FROM $table WHERE status = 'pending'");
        $queued = (int) $wpdb->get_var("SELECT COUNT(*) FROM $table WHERE status = 'queued'");
        $processing = (int) $wpdb->get_var("SELECT COUNT(*) FROM $table WHERE status = 'processing'");
        $protected = (int) $wpdb->get_var("SELECT COUNT(*) FROM $table WHERE status = 'protected'");
        $failed = (int) $wpdb->get_var("SELECT COUNT(*) FROM $table WHERE status = 'failed'");
        $dismissed = (int) $wpdb->get_var("SELECT COUNT(*) FROM $table WHERE status = 'dismissed'");
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

        return array(
            'total'      => $total,
            'pending'    => $pending,
            'queued'     => $queued,
            'processing' => $processing,
            'protected'  => $protected,
            'failed'     => $failed,
            'dismissed'  => $dismissed,
        );
    }

    /**
     * Remove stale scan entries for deleted posts or removed images
     */
    private function cleanup_stale_entries() {
        global $wpdb;
        $table = $wpdb->prefix . 'affirm_scanned_images';

        // Remove entries where the post no longer exists. Custom table; $table from $wpdb->prefix (safe).
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $wpdb->query(
            "DELETE s FROM $table s
             LEFT JOIN {$wpdb->posts} p ON s.post_id = p.ID
             WHERE p.ID IS NULL"
        );

        // Remove entries where the image tag is no longer in the post content (only pending/failed)
        $stale = $wpdb->get_results(
            "SELECT s.id, s.post_id, s.img_tag FROM $table s
             WHERE s.status IN ('pending', 'failed')"
        );
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

        foreach ($stale as $entry) {
            $content = get_post_field('post_content', $entry->post_id);
            if ($content && strpos($content, $entry->img_tag) === false) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                $wpdb->delete($table, array('id' => $entry->id));
            }
        }
    }

    /**
     * Trigger async batch processing via loopback request
     */
    private function trigger_async_batch() {
        $url = admin_url('admin-ajax.php');
        wp_remote_post($url, array(
            'timeout'   => 0.01,
            'blocking'  => false,
            'sslverify' => false,
            'body'      => array(
                'action' => 'affirm_cron_trigger',
            ),
        ));
    }

    /**
     * AJAX: Get dashboard data (plan, usage, recent images)
     */
    public function ajax_get_dashboard() {
        check_ajax_referer('affirm_admin_nonce', 'nonce');
        if (!current_user_can('manage_options')) {
            wp_send_json_error(array('message' => 'Unauthorized'));
        }

        $result = Affirm_Api::get('dashboard');

        if (is_wp_error($result)) {
            wp_send_json_error(array('message' => $result->get_error_message()));
        }

        wp_send_json_success($result);
    }

    /**
     * AJAX: Upload image file to the vault
     */
    public function ajax_upload_image() {
        check_ajax_referer('affirm_admin_nonce', 'nonce');
        if (!current_user_can('manage_options')) {
            wp_send_json_error(array('message' => 'Unauthorized'));
        }

        if (empty($_FILES['file']) || !isset($_FILES['file']['tmp_name'])) {
            wp_send_json_error(array('message' => 'No file provided'));
        }

        // Sanitize the individual upload fields we use. tmp_name is a server-side
        // path; name is sanitized as a filename; size is cast to int.
        $file = array(
            'tmp_name' => sanitize_text_field(wp_unslash($_FILES['file']['tmp_name'])),
            'name'     => isset($_FILES['file']['name']) ? sanitize_file_name(wp_unslash($_FILES['file']['name'])) : 'upload',
            'size'     => isset($_FILES['file']['size']) ? (int) $_FILES['file']['size'] : 0,
        );

        // Validate file type
        $allowed = array('image/jpeg', 'image/png', 'image/webp', 'image/heic', 'image/avif');
        $finfo = finfo_open(FILEINFO_MIME_TYPE);
        $mime = finfo_file($finfo, $file['tmp_name']);
        finfo_close($finfo);

        if (!in_array($mime, $allowed)) {
            wp_send_json_error(array('message' => 'Invalid file type: ' . $mime));
        }

        // Validate file size (max 50MB)
        if ($file['size'] > 50 * 1024 * 1024) {
            wp_send_json_error(array('message' => 'File too large. Maximum 50MB.'));
        }

        $result = Affirm_Api::upload_file($file['tmp_name'], $file['name'], $mime);

        if (is_wp_error($result)) {
            wp_send_json_error(array('message' => $result->get_error_message()));
        }

        // The original never enters WordPress — it stays encrypted in the vault.
        // What DOES get added is the protected proxy, so the image is immediately
        // selectable in the media manager and in every page builder. Best-effort:
        // if the vault has not generated the preview yet, the Media Library
        // screen's sync picks it up.
        Affirm_Media_Library::ensure_attachment($result);

        wp_send_json_success(array(
            'message' => 'Image uploaded successfully',
            'image'   => $result,
        ));
    }

    /**
     * AJAX: Get vault images list
     */
    public function ajax_get_vault_images() {
        check_ajax_referer('affirm_admin_nonce', 'nonce');
        if (!current_user_can('manage_options')) {
            wp_send_json_error(array('message' => 'Unauthorized'));
        }

        $result = Affirm_Api::get('images');

        if (is_wp_error($result)) {
            wp_send_json_error(array('message' => $result->get_error_message()));
        }

        wp_send_json_success($result);
    }
}