<?php

namespace MediaTrim\Core;

use MediaTrim\Traits\Singleton;

defined('ABSPATH') || exit;

/**
 * Class Cleaner
 *
 * Handles the logic for scanning media library for unused and duplicate files.
 *
 * @package MediaTrim\Core
 */
class Cleaner
{

    use Singleton;

    /**
     * Store scan results table name
     *
     * @var string
     */
    private $table_name;

    /**
     * Constructor
     */
    public function __construct()
    {
        global $wpdb;
        $this->table_name = $wpdb->prefix . 'mtrim_scan_results';
        
        $this->init_hooks();
    }

    /**
     * Initialize hooks
     */
    public function init_hooks()
    {
        add_action('wp_ajax_mtrim_start_scan', [$this, 'ajax_start_scan']);
        add_action('wp_ajax_mtrim_get_scan_progress', [$this, 'ajax_get_scan_progress']);
        add_action('wp_ajax_mtrim_get_unused_media', [$this, 'ajax_get_unused_media']);
        add_action('wp_ajax_mtrim_get_duplicates', [$this, 'ajax_get_duplicates']);
        add_action('wp_ajax_mtrim_delete_media', [$this, 'ajax_delete_media']);
        add_action('wp_ajax_mtrim_delete_all_unused', [$this, 'ajax_delete_all_unused']);
        add_action('wp_ajax_mtrim_keep_duplicate', [$this, 'ajax_keep_duplicate']);
        add_action('wp_ajax_mtrim_auto_merge_duplicates', [$this, 'ajax_auto_merge_duplicates']);
        add_action('wp_ajax_mtrim_bulk_delete_media', [$this, 'ajax_bulk_delete_media']);
        add_action('wp_ajax_mtrim_get_unused_dates', [$this, 'ajax_get_unused_dates']);
        
        // Handle physical deletion from media library
        add_action('delete_attachment', [$this, 'handle_attachment_deletion']);
    }

    /**
     * Handle removal of scan results when an attachment is physically deleted.
     *
     * @param int $attachment_id The attachment ID being deleted.
     */
    public function handle_attachment_deletion($attachment_id)
    {
        global $wpdb;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $wpdb->delete($this->table_name, ['attachment_id' => $attachment_id], ['%d']);
    }

    /**
     * AJAX handler to get unused media results
     */
    public function ajax_get_unused_media()
    {
        check_ajax_referer('mtrim_admin_nonce', 'nonce');

        if (!current_user_can('manage_options')) {
            wp_send_json_error(['message' => __('Unauthorized', 'media-trim')]);
        }

        $page = isset($_POST['page']) ? absint($_POST['page']) : 1;
        $limit = isset($_POST['limit']) ? absint($_POST['limit']) : 20;
        $offset = ($page - 1) * $limit;
        $search = isset($_POST['search']) ? sanitize_text_field(wp_unslash($_POST['search'])) : '';
        $mtrim_date = isset($_POST['mtrim_date']) ? sanitize_text_field(wp_unslash($_POST['mtrim_date'])) : '';

        global $wpdb;

        $where = "WHERE status = 'unused'";
        $params = [];

        if (!empty($search)) {
            $where .= " AND (file_path LIKE %s OR attachment_id = %d)";
            $params[] = '%' . $wpdb->esc_like($search) . '%';
            $params[] = is_numeric($search) ? (int) $search : 0;
        }

        if (!empty($mtrim_date)) {
            $where .= " AND created_at LIKE %s";
            $params[] = $mtrim_date . '%';
        }

        // Get total count
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name is safely constructed from $wpdb->prefix.
        $count_sql = "SELECT COUNT(*) FROM $this->table_name $where";
        if (!empty($params)) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query uses prepare() with placeholders.
            $total = $wpdb->get_var($wpdb->prepare($count_sql, $params));
        } else {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- No user input when params is empty.
            $total = $wpdb->get_var($count_sql);
        }

        // Get results
        $sql = "SELECT * FROM $this->table_name $where ORDER BY created_at DESC LIMIT %d, %d";
        $final_params = array_merge($params, [$offset, $limit]);

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query uses prepare() with placeholders.
        $results = $wpdb->get_results($wpdb->prepare($sql, $final_params));

        // Enhancing results with WP attachment details
        foreach ($results as &$result) {
            $thumb_url = wp_get_attachment_thumb_url($result->attachment_id);
            $result->url = $thumb_url ? $thumb_url : wp_get_attachment_url($result->attachment_id);
            $result->filename = basename($result->file_path);
            $result->formatted_size = size_format($result->file_size);
            $result->date = get_the_date('', $result->attachment_id);
        }

        wp_send_json_success([
            'items' => $results,
            'total' => (int) $total,
            'pages' => ceil($total / $limit),
            'page'  => $page
        ]);
    }

    /**
     * AJAX handler to get unique months for unused media
     */
    public function ajax_get_unused_dates()
    {
        check_ajax_referer('mtrim_admin_nonce', 'nonce');

        if (!current_user_can('manage_options')) {
            wp_send_json_error(['message' => __('Unauthorized', 'media-trim')]);
        }

        global $wpdb;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $results = $wpdb->get_results("SELECT DISTINCT DATE_FORMAT(created_at, '%Y-%m') as value FROM $this->table_name WHERE status = 'unused' ORDER BY created_at DESC");

        $dates = [];
        foreach ($results as $res) {
            if (!$res->value) continue;
            $dates[] = [
                'value' => $res->value,
                'label' => date_i18n('F Y', strtotime($res->value . '-01'))
            ];
        }

        wp_send_json_success($dates);
    }

    /**
     * AJAX handler to get duplicates results
     */
    public function ajax_get_duplicates()
    {
        check_ajax_referer('mtrim_admin_nonce', 'nonce');

        if (!current_user_can('manage_options')) {
            wp_send_json_error(['message' => __('Unauthorized', 'media-trim')]);
        }

        $page = isset($_POST['page']) ? absint($_POST['page']) : 1;
        $limit = isset($_POST['limit']) ? absint($_POST['limit']) : 20;
        $offset = ($page - 1) * $limit;

        global $wpdb;

        // Find hashes that appear more than once.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is safely constructed.
        $all_hashes = $wpdb->get_col("SELECT file_hash FROM $this->table_name GROUP BY file_hash HAVING COUNT(*) > 1");
        $total = count($all_hashes);

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is safely constructed.
        $hashes = $wpdb->get_col($wpdb->prepare(
            "SELECT file_hash FROM $this->table_name GROUP BY file_hash HAVING COUNT(*) > 1 LIMIT %d, %d",
            $offset,
            $limit
        ));
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        
        $groups = [];
        foreach ($hashes as $hash) {
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
            $items = $wpdb->get_results($wpdb->prepare(
                "SELECT * FROM $this->table_name WHERE file_hash = %s",
                $hash
            ));
            // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

            foreach ($items as &$item) {
                $thumb_url = wp_get_attachment_thumb_url($item->attachment_id);
                $item->url = $thumb_url ? $thumb_url : wp_get_attachment_url($item->attachment_id);
                $item->filename = basename($item->file_path);
                $item->formatted_size = size_format($item->file_size);
            }

            $groups[] = [
                'hash' => $hash,
                'items' => $items
            ];
        }

        wp_send_json_success([
            'items' => $groups,
            'total' => (int) $total,
            'pages' => ceil($total / $limit),
            'page'  => $page
        ]);
    }

    /**
     * AJAX handler to start or continue a scan
     */
    public function ajax_start_scan()
    {
        check_ajax_referer('mtrim_admin_nonce', 'nonce');

        if (!current_user_can('manage_options')) {
            wp_send_json_error(['message' => __('Unauthorized', 'media-trim')]);
        }

        $offset = isset($_POST['offset']) ? absint($_POST['offset']) : 0;
        $batch_size = 50;

        if ($offset === 0) {
            global $wpdb;
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is safely constructed.
            $wpdb->query("TRUNCATE TABLE $this->table_name");
        }

        $results = $this->scan_batch($offset, $batch_size);

        // Update scan manager state
        $scan_manager = ScanManager::get_instance();
        if ($offset === 0) {
            $scan_manager->save_state('unused', 0, $results['total']);
        } else {
            $scan_manager->save_state('unused', $results['next_offset'], $results['total']);
        }

        // If complete, log to history
        if ($results['is_complete']) {
            global $wpdb;
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is safely constructed.
            $found_unused = $wpdb->get_var("SELECT COUNT(*) FROM $this->table_name WHERE status = 'unused' AND attachment_id > 0");
            $scan_manager->complete_scan('unused', ['found' => $found_unused]);
        }

        wp_send_json_success($results);
    }

    /**
     * AJAX handler to get scan progress
     */
    public function ajax_get_scan_progress()
    {
        check_ajax_referer('mtrim_admin_nonce', 'nonce');

        if (!current_user_can('manage_options')) {
            wp_send_json_error(['message' => __('Unauthorized', 'media-trim')]);
        }

        $state = ScanManager::get_instance()->get_state('unused');
        wp_send_json_success($state);
    }

    /**
     * Scan a batch of media items
     *
     * @param int $offset The starting offset.
     * @param int $limit  Number of items to scan.
     * @return array Scan results summary.
     */
    public function scan_batch($offset = 0, $limit = 50)
    {
        $args = [
            'post_type'      => 'attachment',
            'post_status'    => 'inherit',
            'posts_per_page' => $limit,
            'offset'         => $offset,
            'fields'         => 'ids',
        ];

        $query = new \WP_Query($args);
        $ids = $query->posts;
        $total = $query->found_posts;

        foreach ($ids as $id) {
            $is_used = $this->is_media_used($id);
            $this->save_scan_result($id, $is_used ? 'used' : 'unused');
        }

        return [
            'scanned' => count($ids),
            'total'   => $total,
            'next_offset' => $offset + count($ids),
            'is_complete' => ($offset + count($ids)) >= $total,
        ];
    }

    /**
     * Check if a specific media item is being used
     *
     * @param int $attachment_id The attachment ID.
     * @return bool True if used, false otherwise.
     */
    public function is_media_used($attachment_id)
    {
        global $wpdb;

        // 1. Check if it's a featured image on an active post.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $is_featured = $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM $wpdb->postmeta pm 
             JOIN $wpdb->posts p ON pm.post_id = p.ID 
             WHERE pm.meta_key = '_thumbnail_id' 
             AND pm.meta_value = %d 
             AND p.post_status NOT IN ('trash', 'auto-draft') 
             AND p.post_type != 'revision'",
            $attachment_id
        ));
        if ($is_featured) return true;

        // 2. Check if used in post content (via URL)
        $url = wp_get_attachment_url($attachment_id);
        if ($url) {
            $relative_url = str_replace(home_url(), '', $url);
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $is_in_content = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM $wpdb->posts 
                 WHERE post_content LIKE %s 
                 AND post_status NOT IN ('trash', 'auto-draft') 
                 AND post_type != 'revision'",
                '%' . $wpdb->esc_like($relative_url) . '%'
            ));
            if ($is_in_content) return true;
        }

        // 3. Check gallery shortcode [gallery ids="..."]
        if ($this->check_gallery_shortcode($attachment_id)) return true;

        // 4. Check Gutenberg blocks (wp:gallery, wp:image)
        if ($this->check_gutenberg_blocks($attachment_id)) return true;

        // 5. Check custom postmeta fields
        if ($this->check_postmeta($attachment_id)) return true;

        // 6. Check options table (site logo, theme options, etc.)
        if ($this->check_options_table($attachment_id)) return true;

        // 7. Check widget content
        if ($this->check_widget_content($attachment_id)) return true;

        return false;
    }

    /**
     * Check if attachment is used in gallery shortcode
     *
     * @param int $attachment_id The attachment ID.
     * @return bool
     */
    private function check_gallery_shortcode($attachment_id)
    {
        global $wpdb;

        // Search for [gallery ids="..."] containing the attachment ID
        $patterns = [
            '%[gallery%ids="' . $attachment_id . '"%',
            '%[gallery%ids="' . $attachment_id . ',%',
            '%[gallery%ids="%,' . $attachment_id . ',%',
            '%[gallery%ids="%,' . $attachment_id . '"%',
        ];

        foreach ($patterns as $pattern) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $found = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM $wpdb->posts 
                 WHERE post_content LIKE %s 
                 AND post_status NOT IN ('trash', 'auto-draft') 
                 AND post_type != 'revision'",
                $pattern
            ));
            if ($found) return true;
        }

        return false;
    }

    /**
     * Check if attachment is used in Gutenberg blocks
     *
     * @param int $attachment_id The attachment ID.
     * @return bool
     */
    private function check_gutenberg_blocks($attachment_id)
    {
        global $wpdb;

        // Check for wp:image block with the attachment ID
        $image_block_pattern = '%<!-- wp:image {"id":' . $attachment_id . '%';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $found = $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM $wpdb->posts 
             WHERE post_content LIKE %s 
             AND post_status NOT IN ('trash', 'auto-draft') 
             AND post_type != 'revision'",
            $image_block_pattern
        ));
        if ($found) return true;

        // Check for wp:gallery block containing the attachment ID
        $gallery_block_patterns = [
            '%<!-- wp:gallery%"ids":[' . $attachment_id . '%',
            '%<!-- wp:gallery%"ids":[%,' . $attachment_id . '%',
        ];

        foreach ($gallery_block_patterns as $pattern) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $found = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM $wpdb->posts 
                 WHERE post_content LIKE %s 
                 AND post_status NOT IN ('trash', 'auto-draft') 
                 AND post_type != 'revision'",
                $pattern
            ));
            if ($found) return true;
        }

        // Check for wp:media-text block
        $media_text_pattern = '%<!-- wp:media-text {"mediaId":' . $attachment_id . '%';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $found = $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM $wpdb->posts 
             WHERE post_content LIKE %s 
             AND post_status NOT IN ('trash', 'auto-draft') 
             AND post_type != 'revision'",
            $media_text_pattern
        ));
        if ($found) return true;

        // Check for wp:cover block
        $cover_pattern = '%<!-- wp:cover {"id":' . $attachment_id . '%';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $found = $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM $wpdb->posts 
             WHERE post_content LIKE %s 
             AND post_status NOT IN ('trash', 'auto-draft') 
             AND post_type != 'revision'",
            $cover_pattern
        ));
        if ($found) return true;

        return false;
    }

    /**
     * Check if attachment is used in custom postmeta fields
     *
     * @param int $attachment_id The attachment ID.
     * @return bool
     */
    private function check_postmeta($attachment_id)
    {
        global $wpdb;

        // Check for attachment ID as meta value (numeric).
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $found = $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM $wpdb->postmeta pm
             JOIN $wpdb->posts p ON pm.post_id = p.ID
             WHERE pm.meta_value = %s 
             AND pm.meta_key NOT LIKE %s
             AND p.post_status NOT IN ('trash', 'auto-draft')
             AND p.post_type != 'revision'",
            (string) $attachment_id,
            $wpdb->esc_like( '_' ) . '%'
        ));
        if ($found) return true;

        // Check for URL in meta values
        $url = wp_get_attachment_url($attachment_id);
        if ($url) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $found = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM $wpdb->postmeta pm
                 JOIN $wpdb->posts p ON pm.post_id = p.ID
                 WHERE pm.meta_value LIKE %s
                 AND p.post_status NOT IN ('trash', 'auto-draft')
                 AND p.post_type != 'revision'",
                '%' . $wpdb->esc_like($url) . '%'
            ));
            if ($found) return true;
        }

        return false;
    }

    /**
     * Check if attachment is used in options table
     *
     * @param int $attachment_id The attachment ID.
     * @return bool
     */
    private function check_options_table($attachment_id)
    {
        global $wpdb;

        // Common option names that store media IDs
        $media_options = [
            'site_logo',
            'site_icon',
            'custom_logo',
        ];

        foreach ($media_options as $option) {
            $value = get_option($option);
            if ($value && (int) $value === (int) $attachment_id) {
                return true;
            }
        }

        // Check for attachment ID in serialized options.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $found = $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM $wpdb->options WHERE option_value LIKE %s",
            '%' . $wpdb->esc_like('"' . $attachment_id . '"') . '%'
        ));
        if ($found) return true;

        // Check for URL in options
        $url = wp_get_attachment_url($attachment_id);
        if ($url) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $found = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM $wpdb->options WHERE option_value LIKE %s",
                '%' . $wpdb->esc_like($url) . '%'
            ));
            if ($found) return true;
        }

        return false;
    }

    /**
     * Check if attachment is used in widget content
     *
     * @param int $attachment_id The attachment ID.
     * @return bool
     */
    private function check_widget_content($attachment_id)
    {
        global $wpdb;

        // Get all widget options.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $widgets = $wpdb->get_results(
            "SELECT option_value FROM $wpdb->options WHERE option_name LIKE 'widget_%'"
        );

        $url = wp_get_attachment_url($attachment_id);

        foreach ($widgets as $widget) {
            $data = maybe_unserialize($widget->option_value);
            
            if (is_array($data)) {
                $serialized = serialize($data);
                
                // Check for attachment ID
                if (strpos($serialized, '"' . $attachment_id . '"') !== false) {
                    return true;
                }
                
                // Check for URL
                if ($url && strpos($serialized, $url) !== false) {
                    return true;
                }
            }
        }

        return false;
    }

    /**
     * Save scan result to database
     *
     * @param int    $attachment_id The attachment ID.
     * @param string $status        'used' or 'unused'.
     */
    public function save_scan_result($attachment_id, $status)
    {
        global $wpdb;

        $file_path = get_attached_file($attachment_id);
        $file_hash = $file_path ? md5_file($file_path) : '';
        $file_size = $file_path ? filesize($file_path) : 0;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $wpdb->replace(
            $this->table_name,
            [
                'attachment_id' => $attachment_id,
                'file_hash'     => $file_hash,
                'status'        => $status,
                'file_path'     => $file_path,
                'file_size'     => $file_size,
            ],
            ['%d', '%s', '%s', '%s', '%d']
        );
    }

    /**
     * AJAX handler to move a single media item to trash
     */
    public function ajax_delete_media()
    {
        check_ajax_referer('mtrim_admin_nonce', 'nonce');

        if (!current_user_can('manage_options')) {
            wp_send_json_error(['message' => __('Unauthorized', 'media-trim')]);
        }

        $attachment_id = isset($_POST['attachment_id']) ? absint($_POST['attachment_id']) : 0;

        if (!$attachment_id) {
            wp_send_json_error(['message' => __('Invalid attachment ID', 'media-trim')]);
        }

        if (Trash::get_instance()->move_to_trash($attachment_id)) {
            // Update status in scan results.
            global $wpdb;
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->delete($this->table_name, ['attachment_id' => $attachment_id], ['%d']);
            
            wp_send_json_success(['message' => __('Media moved to trash', 'media-trim')]);
        } else {
            wp_send_json_error(['message' => __('Failed to move media to trash', 'media-trim')]);
        }
    }

    /**
     * AJAX handler to move selected media items to trash
     */
    public function ajax_bulk_delete_media()
    {
        check_ajax_referer('mtrim_admin_nonce', 'nonce');

        if (!current_user_can('manage_options')) {
            wp_send_json_error(['message' => __('Unauthorized', 'media-trim')]);
        }

        $json_ids = isset($_POST['attachment_ids']) ? sanitize_text_field(wp_unslash($_POST['attachment_ids'])) : '';
        $attachment_ids = json_decode($json_ids, true);
        $attachment_ids = is_array($attachment_ids) ? array_map('absint', $attachment_ids) : [];

        if (empty($attachment_ids)) {
            wp_send_json_error(['message' => __('No items selected', 'media-trim')]);
        }

        $count = 0;
        foreach ($attachment_ids as $id) {
            if (Trash::get_instance()->move_to_trash($id)) {
                global $wpdb;
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                $wpdb->delete($this->table_name, ['attachment_id' => $id], ['%d']);
                $count++;
            }
        }

        wp_send_json_success([
            /* translators: %d: number of items moved to trash */
            'message' => sprintf(__('%d items moved to trash', 'media-trim'), $count)
        ]);
    }

    /**
     * AJAX handler to move all unused media to trash
     */
    public function ajax_delete_all_unused()
    {
        check_ajax_referer('mtrim_admin_nonce', 'nonce');

        if (!current_user_can('manage_options')) {
            wp_send_json_error(['message' => __('Unauthorized', 'media-trim')]);
        }

        global $wpdb;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is safely constructed.
        $unused_ids = $wpdb->get_col("SELECT attachment_id FROM $this->table_name WHERE status = 'unused'");

        if (empty($unused_ids)) {
            wp_send_json_error(['message' => __('No unused media found', 'media-trim')]);
        }

        $count = 0;
        foreach ($unused_ids as $id) {
            if (Trash::get_instance()->move_to_trash($id)) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                $wpdb->delete($this->table_name, ['attachment_id' => $id], ['%d']);
                $count++;
            }
        }

        /* translators: %d: number of items moved to trash */
        wp_send_json_success(['message' => sprintf(__('%d items moved to trash', 'media-trim'), $count)]);
    }

    /**
     * AJAX handler to keep one duplicate and trash others
     */
    public function ajax_keep_duplicate()
    {
        check_ajax_referer('mtrim_admin_nonce', 'nonce');

        if (!current_user_can('manage_options')) {
            wp_send_json_error(['message' => __('Unauthorized', 'media-trim')]);
        }

        $attachment_id = isset($_POST['attachment_id']) ? absint($_POST['attachment_id']) : 0;
        $hash = isset($_POST['file_hash']) ? sanitize_text_field(wp_unslash($_POST['file_hash'])) : '';

        if (!$attachment_id || empty($hash)) {
            wp_send_json_error(['message' => __('Missing parameters', 'media-trim')]);
        }

        global $wpdb;
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $duplicates = $wpdb->get_col($wpdb->prepare(
            "SELECT attachment_id FROM $this->table_name WHERE file_hash = %s AND attachment_id != %d",
            $hash,
            $attachment_id
        ));
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

        $count = 0;
        foreach ($duplicates as $id) {
            // Replace references before trashing
            $this->replace_attachment_references($id, $attachment_id);

            if (Trash::get_instance()->move_to_trash($id)) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                $wpdb->delete($this->table_name, ['attachment_id' => $id], ['%d']);
                $count++;
            }
        }

        // Also remove the "kept" one from scan results as it's no longer part of a duplicate group.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $wpdb->delete($this->table_name, ['attachment_id' => $attachment_id], ['%d']);

        /* translators: %d: number of duplicates moved to trash */
        wp_send_json_success(['message' => sprintf(__('%d duplicates moved to trash', 'media-trim'), $count)]);
    }

    /**
     * AJAX handler to auto-merge all duplicates
     */
    public function ajax_auto_merge_duplicates()
    {
        check_ajax_referer('mtrim_admin_nonce', 'nonce');

        if (!current_user_can('manage_options')) {
            wp_send_json_error(['message' => __('Unauthorized', 'media-trim')]);
        }

        global $wpdb;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is safely constructed.
        $hashes = $wpdb->get_col("SELECT file_hash FROM $this->table_name GROUP BY file_hash HAVING COUNT(*) > 1");

        $total_deleted = 0;
        foreach ($hashes as $hash) {
            // Get all IDs for this hash, ordered by ID (keep the oldest).
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
            $ids = $wpdb->get_col($wpdb->prepare(
                "SELECT attachment_id FROM $this->table_name WHERE file_hash = %s ORDER BY attachment_id ASC",
                $hash
            ));
            // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

            if (count($ids) > 1) {
                $keep_id = array_shift($ids);
                foreach ($ids as $id) {
                    // Replace references before trashing
                    $this->replace_attachment_references($id, $keep_id);

                    if (Trash::get_instance()->move_to_trash($id)) {
                        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                        $wpdb->delete($this->table_name, ['attachment_id' => $id], ['%d']);
                        $total_deleted++;
                    }
                }
                // Also remove the "kept" one from scan results.
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                $wpdb->delete($this->table_name, ['attachment_id' => $keep_id], ['%d']);
            }
        }

        /* translators: %d: number of files moved to trash */
        wp_send_json_success(['message' => sprintf(__('Auto-merged: %d files moved to trash', 'media-trim'), $total_deleted)]);
    }

    /**
     * Replace references to an old attachment ID with a new one across the database.
     *
     * @param int $old_id The attachment ID being removed/merged.
     * @param int $new_id The attachment ID being kept.
     */
    public function replace_attachment_references($old_id, $new_id)
    {
        global $wpdb;
        $old_id = (int) $old_id;
        $new_id = (int) $new_id;

        if ($old_id === $new_id) {
            return;
        }

        $old_urls = $this->get_all_attachment_urls($old_id);
        $new_urls = $this->get_all_attachment_urls($new_id);

        if (empty($old_urls) || empty($new_urls)) {
            return;
        }

        // 1. Update Featured Images
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.SlowDBQuery.slow_db_query_meta_value, WordPress.DB.SlowDBQuery.slow_db_query_meta_key -- Required for bulk media merge operation.
        $wpdb->update(
            $wpdb->postmeta,
            ['meta_value' => $new_id],
            ['meta_key' => '_thumbnail_id', 'meta_value' => $old_id]
        );
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.SlowDBQuery.slow_db_query_meta_value, WordPress.DB.SlowDBQuery.slow_db_query_meta_key

        // 2. Update Post Content (Block attributes and URLs)
        // IDs in block JSON
        $old_block_id_query = '"id":' . $old_id;
        $new_block_id_query = '"id":' . $new_id;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Required for bulk content update during media merge.
        $wpdb->query($wpdb->prepare(
            "UPDATE {$wpdb->posts} SET post_content = REPLACE(post_content, %s, %s)",
            $old_block_id_query,
            $new_block_id_query
        ));

        // Media IDs in block JSON
        $old_media_id_query = '"mediaId":' . $old_id;
        $new_media_id_query = '"mediaId":' . $new_id;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Required for bulk content update during media merge.
        $wpdb->query($wpdb->prepare(
            "UPDATE {$wpdb->posts} SET post_content = REPLACE(post_content, %s, %s)",
            $old_media_id_query,
            $new_media_id_query
        ));

        // wp-image-ID class names
        $old_class_query = 'wp-image-' . $old_id;
        $new_class_query = 'wp-image-' . $new_id;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Required for bulk content update during media merge.
        $wpdb->query($wpdb->prepare(
            "UPDATE {$wpdb->posts} SET post_content = REPLACE(post_content, %s, %s)",
            $old_class_query,
            $new_class_query
        ));

        // URLs in content (Iterate through all sizes)
        foreach ($old_urls as $size => $old_url) {
            $new_url = isset($new_urls[$size]) ? $new_urls[$size] : $new_urls['full'];
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Required for bulk content update during media merge.
            $wpdb->query($wpdb->prepare(
                "UPDATE {$wpdb->posts} SET post_content = REPLACE(post_content, %s, %s)",
                $old_url,
                $new_url
            ));
        }

        // 3. Update Custom Postmeta (Numeric IDs)
        // Only target known attachment-related meta keys to avoid corrupting unrelated data.
        $attachment_meta_keys = [
            '_thumbnail_id',
            '_product_image_id',
            '_wc_attachment_source',
            'background_image',
            'header_image',
            'logo',
        ];

        foreach ($attachment_meta_keys as $meta_key) {
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.SlowDBQuery.slow_db_query_meta_value, WordPress.DB.SlowDBQuery.slow_db_query_meta_key -- Required for bulk media merge operation.
            $wpdb->update(
                $wpdb->postmeta,
                ['meta_value' => (string) $new_id],
                ['meta_key' => $meta_key, 'meta_value' => (string) $old_id]
            );
            // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.SlowDBQuery.slow_db_query_meta_value, WordPress.DB.SlowDBQuery.slow_db_query_meta_key
        }

        // 4. Update Options Table
        // Only handle known safe options to avoid corrupting serialized data.
        $logo_options = ['site_logo', 'custom_logo', 'site_icon'];
        foreach ($logo_options as $option) {
            if ((int) get_option($option) === $old_id) {
                update_option($option, $new_id);
            }
        }
    }

    /**
     * Get all public URLs for a media attachment, including all intermediate sizes.
     *
     * @param int $attachment_id The attachment ID.
     * @return array Array of size names to URLs.
     */
    private function get_all_attachment_urls($attachment_id)
    {
        $urls = [];
        $full_url = wp_get_attachment_url($attachment_id);
        
        if (!$full_url) {
            return $urls;
        }

        $urls['full'] = $full_url;
        
        $meta = wp_get_attachment_metadata($attachment_id);
        if ($meta && isset($meta['sizes'])) {
            $base_url = str_replace(basename($full_url), '', $full_url);
            foreach ($meta['sizes'] as $size => $data) {
                $urls[$size] = $base_url . $data['file'];
            }
        }

        return $urls;
    }
}
