<?php

/**
 * Admin AdminRest
 * Handles all ajax requests for admin area
 *
 * @since 1.0
 * @package ArchiveMaster
 */

// Namespace.
namespace ARCHM\Admin;

use ARCHM\Features\ActivityLogDB;
use ARCHM\Features\BackgroundProcess;
use ARCHM\Features\Sqlite;
use ARCHM\Helpers\Logger;

// Exit if accessed directly.
defined('ABSPATH') || exit(1);

if ( ! class_exists(__NAMESPACE__ . '\AdminRest') ) {
    /**
     * Admin AdminRest
     *
     * @since 1.1.0
     */
    class AdminRest extends \WP_REST_Controller {


        // Use utilities trait.
        use \ARCHM\Helpers\Utilities;
        use \ARCHM\Helpers\DBHelper;
        use \ARCHM\Helpers\WcStorageHandler;
        use \ARCHM\Helpers\BGProcessHelper;

        /**
         * Singleton instance
         *
         * @var self
         */
        private static $instance;

        /**
         * API Version
         *
         * @var string
         */
        protected $version = 'v1';

        /**
         * API Namespace
         *
         * @var string
         */
        protected $namespace = 'archm/v1';

        /**
         * Returns the singleton instance
         *
         * @return self
         */
        public static function get_instance() {
            if ( ! self::$instance ) {
                self::$instance = new self();
            }
            return self::$instance;
        }

        /**
         * Constructor
         */
        public function __construct() {
            $this->set_remote_db_settings();
            $this->set_main_deb_settings();
            add_action('rest_api_init', [ $this, 'register_routes' ]);
        }

        /**
         * Register REST API routes
         */
        public function register_routes() {
            $routes = [
                'update-settings'            => [
                    'methods'             => \WP_REST_Server::EDITABLE,
                    'callback'            => [ $this, 'update_settings' ],
                    'permission_callback' => [ $this, 'check_admin_permissions' ],
                    'args'                => $this->get_endpoint_args_for_item_schema(true),
                ],
                'rds-connection'             => [
                    'methods'             => \WP_REST_Server::EDITABLE,
                    'callback'            => [ $this, 'rds_connection' ],
                    'permission_callback' => [ $this, 'check_admin_permissions' ],
                    'args'                => $this->get_rds_connection_args(),
                ],
                'archive-post'               => [
                    'methods'             => \WP_REST_Server::EDITABLE,
                    'callback'            => [ $this, 'archive_post' ],
                    'permission_callback' => [ $this, 'check_admin_permissions' ],
                    'args'                => $this->get_endpoint_args_for_item_schema(true),
                ],
                'install-wordpress'          => [
                    'methods'             => \WP_REST_Server::EDITABLE,
                    'callback'            => [ $this, 'install_wordpress' ],
                    'permission_callback' => [ $this, 'check_admin_permissions' ],
                    'args'                => $this->get_endpoint_args_for_item_schema(true),
                ],
                'get-archived-order'         => [
                    'methods'             => \WP_REST_Server::EDITABLE,
                    'callback'            => [ $this, 'get_archived_order' ],
                    'permission_callback' => [ $this, 'check_admin_permissions' ],
                    'args'                => $this->get_endpoint_args_for_item_schema(true),
                ],
                'remote-archive-status'      => [
                    'methods'             => \WP_REST_Server::EDITABLE,
                    'callback'            => [ $this, 'remote_archive_status' ],
                    'permission_callback' => [ $this, 'check_admin_permissions' ],
                    'args'                => $this->get_endpoint_args_for_item_schema(true),
                ],
                'unarchive-orders'           => [
                    'methods'             => \WP_REST_Server::EDITABLE,
                    'callback'            => [ $this, 'unarchived_order' ],
                    'permission_callback' => [ $this, 'check_admin_permissions' ],
                    'args'                => $this->get_endpoint_args_for_item_schema(true),
                ],
                'delete-unarchived-orders'   => [
                    'methods'             => \WP_REST_Server::EDITABLE,
                    'callback'            => [ $this, 'delete_unarchived_order' ],
                    'permission_callback' => [ $this, 'check_admin_permissions' ],
                    'args'                => $this->get_endpoint_args_for_item_schema(true),
                ],
                'logs'                       => [
                    'methods'             => \WP_REST_Server::READABLE,
                    'callback'            => [ $this, 'get_logs' ],
                    'permission_callback' => [ $this, 'check_admin_permissions' ],
                    'args'                => [
                        'current_page' => [
                            'required'          => true,
                            'type'              => 'integer',
                            'sanitize_callback' => 'absint',
                            'validate_callback' => function ( $param ) {
                                return is_numeric($param) && $param > 0;
                            },
                        ],
                    ],
                ],
                'delete-logs'                => [
                    'methods'             => \WP_REST_Server::EDITABLE,
                    'callback'            => [ $this, 'delete_logs' ],
                    'permission_callback' => [ $this, 'check_admin_permissions' ],
                    'args'                => $this->get_endpoint_args_for_item_schema(true),
                ],
                'calculate-site-order'       => [
                    'methods'             => \WP_REST_Server::EDITABLE,
                    'callback'            => [ $this, 'calculate_site_order' ],
                    'permission_callback' => [ $this, 'check_admin_permissions' ],
                    'args'                => $this->get_endpoint_args_for_item_schema(true),
                ],
                'site-order-byschecdule'     => [
                    'methods'             => \WP_REST_Server::EDITABLE,
                    'callback'            => [ $this, 'site_order_byschecdule' ],
                    'permission_callback' => [ $this, 'check_admin_permissions' ],
                    'args'                => $this->get_endpoint_args_for_item_schema(true),
                ],
                'cancel-archiving'           => [
                    'methods'             => \WP_REST_Server::EDITABLE,
                    'callback'            => [ $this, 'cancel_archiving' ],
                    'permission_callback' => [ $this, 'check_admin_permissions' ],
                    'args'                => $this->get_endpoint_args_for_item_schema(true),
                ],
                'check-background-archiving' => [
                    'methods'             => \WP_REST_Server::EDITABLE,
                    'callback'            => [ $this, 'check_background_archiving' ],
                    'permission_callback' => [ $this, 'check_admin_permissions' ],
                    'args'                => $this->get_endpoint_args_for_item_schema(true),
                ],
                'search-orders'              => [
                    'methods'             => \WP_REST_Server::EDITABLE,
                    'callback'            => [ $this, 'search_orders' ],
                    'permission_callback' => [ $this, 'check_admin_permissions' ],
                    'args'                => $this->get_endpoint_args_for_item_schema(true),
                ],
                'orders-info'                => [
                    'methods'             => \WP_REST_Server::EDITABLE,
                    'callback'            => [ $this, 'get_order_info' ],
                    'permission_callback' => [ $this, 'check_admin_permissions' ],
                    'args'                => $this->get_endpoint_args_for_item_schema(true),
                ],
                'check-duplicate-orders'     => [
                    'methods'             => \WP_REST_Server::EDITABLE,
                    'callback'            => [ $this, 'check_ubplivate_order' ],
                    'permission_callback' => [ $this, 'check_admin_permissions' ],
                    'args'                => $this->get_endpoint_args_for_item_schema(true),
                ],
                'permanently-delete-orders'  => [
                    'methods'             => \WP_REST_Server::EDITABLE,
                    'callback'            => [ $this, 'permanently_delete_orders' ],
                    'permission_callback' => [ $this, 'check_admin_permissions' ],
                    'args'                => $this->get_endpoint_args_for_item_schema(true),
                ],
                'check-missing-archive-data' => [
                    'methods'             => \WP_REST_Server::READABLE,
                    'callback'            => [ $this, 'check_missing_archive_data' ],
                    'permission_callback' => [ $this, 'check_admin_permissions' ],
                ],
                'recover-archive-data'       => [
                    'methods'             => \WP_REST_Server::EDITABLE,
                    'callback'            => [ $this, 'recover_archive_data' ],
                    'permission_callback' => [ $this, 'check_admin_permissions' ],
                    'args'                => $this->get_endpoint_args_for_item_schema(true),
                ],
                'archive-single-order'       => [
                    'methods'             => \WP_REST_Server::EDITABLE,
                    'callback'            => [ $this, 'archive_single_order' ],
                    'permission_callback' => [ $this, 'check_admin_permissions' ],
                    'args'                => $this->get_single_order_args(),
                ],
                'analytics-filter'           => [
                    [
                        'methods'             => \WP_REST_Server::READABLE,
                        'callback'            => [ $this, 'get_analytics_filter' ],
                        'permission_callback' => [ $this, 'check_admin_permissions' ],
                    ],
                    [
                        'methods'             => \WP_REST_Server::EDITABLE,
                        'callback'            => [ $this, 'set_analytics_filter' ],
                        'permission_callback' => [ $this, 'check_admin_permissions' ],
                        'args'                => [
                            'mode' => [
                                'required'          => true,
                                'type'              => 'string',
                                'sanitize_callback' => 'sanitize_text_field',
                                'validate_callback' => function ( $param ) {
                                    return in_array($param, [ 'exclude', 'combine', 'archived_only' ], true);
                                },
                            ],
                        ],
                    ],
                ],
                'custom-order-meta-keys'     => [
                    'methods'             => \WP_REST_Server::READABLE,
                    'callback'            => [ $this, 'get_custom_order_meta_keys' ],
                    'permission_callback' => [ $this, 'check_admin_permissions' ],
                ],
            ];

            foreach ( $routes as $route => $args ) {
                // Handle routes with multiple method handlers (array of arrays)
                if ( isset($args[0]) && is_array($args[0]) ) {
                    foreach ( $args as $route_args ) {
                        register_rest_route($this->namespace, '/' . $route, $route_args);
                    }
                } else {
                    register_rest_route($this->namespace, '/' . $route, $args);
                }
            }
        }

        /**
         * Check admin permissions
         *
         * @return bool|WP_Error
         */
        public function check_admin_permissions( \WP_REST_Request $request ) {
            // Check if this is an internal call from background process
            $internal_key = $request->get_header('X-Internal-Request');
            if ( $internal_key === $this->get_internal_request_key() ) {
                return true; // Bypass nonce check for internal calls
            }

            $nonce = $request->get_header('X-WP-Nonce');

            if ( ! wp_verify_nonce($nonce, 'wp_rest') ) {
                return new \WP_Error('rest_forbidden', __('Invalid nonce.', 'archive-master'), [ 'status' => 403 ]);
            }

            if ( ! current_user_can('manage_options') ) {
                return new \WP_Error(
                    'rest_forbidden',
                    __('You do not have permission to do this', 'archive-master'),
                    [ 'status' => 403 ]
                );
            }

            return true;
        }

        /**
         * Generate internal request key for background processes
         *
         * @return string
         */
        private function get_internal_request_key() {
            // Use a combination of site-specific data to create a unique key
            return hash('sha256', SECURE_AUTH_KEY . get_option('siteurl') . 'internal_request');
        }

        /**
         * Updates user settings
         *
         * @param WP_REST_Request $request Request object
         * @return WP_REST_Response|WP_Error
         */
        public function update_settings( $request ) {
            $inputs          = $this->io()->sanitize($request->get_params());
            $default_options = $this->option()->get_defaults();

            if ( function_exists('master_archive_pro') ) {
                $is_pro = master_archive_pro()->license_status;
                if ( $is_pro ) {
                    $default_options = array_merge($default_options, master_archive_pro()->get_pro_options());
                }
            }

            if ( array_key_exists('rds_credential', $inputs) ) {
                unset($inputs['rds_credential']);
            }

            // Check if archm_analytics_sync_enabled is being updated
            $analytics_sync_key       = 'analytics_sync_enabled';
            $old_analytics_sync_value = null;
            $new_analytics_sync_value = null;

            if ( isset($inputs[ $analytics_sync_key ]) ) {
                // Get old value before updating
                $old_analytics_sync_value = $this->option()->get($analytics_sync_key, false);
                $new_analytics_sync_value = $inputs[ $analytics_sync_key ];

                // Logger::logMessage('AdminRest: analytics_sync_enabled update detected - Old: ' . var_export($old_analytics_sync_value, true) . ', New: ' . var_export($new_analytics_sync_value, true));
            }

            foreach ( $default_options as $key => $default ) {
                if ( isset($inputs[ $key ]) ) {
                    $new_value = $inputs[ $key ];
                    $this->option()->update($key, $new_value);
                }
            }

            // Fire hook for analytics_sync_enabled option update if it was changed
            if ( null !== $old_analytics_sync_value && null !== $new_analytics_sync_value ) {
                // Convert to boolean if needed (handle string 'true'/'false' or 1/0)
                $old_value = filter_var($old_analytics_sync_value, FILTER_VALIDATE_BOOLEAN);
                $new_value = filter_var($new_analytics_sync_value, FILTER_VALIDATE_BOOLEAN);

                // Logger::logMessage('AdminRest: Firing update_option_archm_analytics_sync_enabled hook - Old: ' . var_export($old_value, true) . ', New: ' . var_export($new_value, true));

                // Fire the action hook with old and new values
                // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedHooknameFound -- This follows WordPress core hook pattern update_option_{option_name}
                do_action('update_option_archm_analytics_sync_enabled', $old_value, $new_value);

                // Logger::logMessage('AdminRest: Hook update_option_archm_analytics_sync_enabled fired successfully');
            }

            return rest_ensure_response(
                [
                    'success' => true,
                    'message' => __('Settings updated', 'archive-master'),
                ]
            );
        }

        /**
         * Check RDS connection and save
         *
         * @param WP_REST_Request $request Request object
         * @return WP_REST_Response|WP_Error
         */
        public function rds_connection( $request ) {
            $rds_inputs = $this->io()->sanitize($request->get_params());

            $database = $rds_inputs['rds_dbname'];
            $endpoint = $rds_inputs['rds_endpoint'];
            $user     = $rds_inputs['rds_username'];
            $port     = $rds_inputs['rds_port'];
            $password = $rds_inputs['rds_password'];

            if ( empty($endpoint) || empty($password) || empty($user) || empty($port) || empty($database) ) {
                return new \WP_Error(
                    'missing_parameters',
                    __('Endpoint, User, port, and password are required.', 'archive-master'),
                    [ 'status' => 400 ]
                );
            }

            $check_db = $this->db_migration()->check_db_connection($endpoint, $port, $user, $password, $database, true);

            if ( ! $check_db['success'] ) {
                $this->option()->update('rds_db_connection', false);
                return new \WP_Error(
                    'connection_failed',
                    $check_db['message'],
                    [ 'status' => 400 ]
                );
            }

            foreach ( $rds_inputs as $key => $value ) {
                $this->option()->update($key, $value);
            }

            $this->option()->update('rds_db_connection', true);

            return rest_ensure_response(
                [
                    'success' => true,
                    'message' => __('RDS endpoint and password saved successfully.', 'archive-master'),
                ]
            );
        }

        /**
         * Archive post
         *
         * @param WP_REST_Request $request
         * @return WP_REST_Response|WP_Error
         */
        public function archive_post( $request ) {
            try {
                $archive_info = null;

                $queue_ids = $this->option()->get('queue_batch_ids');

                $selected_database = get_option('archm_selected_database', 'sqlite');

                if ( 'sqlite' === $selected_database || 'google_drive' === $selected_database ) {
                    if ( ! extension_loaded('pdo_sqlite') ) {
                        return new \WP_Error(
                            'disabled_pdo_sqlite',
                            'We detected that the PDO SQLite driver is missing from your server (the pdo_sqlite extension is not loaded). Please make sure that SQLite is enabled in your PHP installation before proceeding.',
                            [ 'status' => 400 ]
                        );
                    }

                    // Create SQLite database file before archiving if it doesn't exist
                    $sqlite = Sqlite::get_instance();
                    if ( ! $sqlite->create_database_for_archiving() ) {
                        return new \WP_Error(
                            'sqlite_creation_failed',
                            'Failed to create SQLite database file for archiving. Please check file permissions.',
                            [ 'status' => 500 ]
                        );
                    }
                }

                if ( ! $queue_ids ) {
                    $archive_orders_count = $this->db_migration()->get_total_orders();
                    $this->option()->update('archive_orders_count', $archive_orders_count);
                    $this->option()->update('background_process_running', true);
                    $this->option()->update('background_process_stop', false);
                    $this->option()->update('archive_cancelled', false);
                }

                $archive_info = $this->db_migration()->start_archiving();

                //Logger::logMessage($archive_info);

                if ( $archive_info['status'] ) {
                    $archive_status = [
                        'remotePostCount' => $archive_info['remotePostCount'],
                        'mainPostCount'   => $archive_info['mainPostCount'],
                        'bgProcess'       => $archive_info['bgProcess'],
                        'archiveLoop'     => $archive_info['archiveLoop'],
                    ];

                    /* error_log('Archive Master DEBUG: Triggering archm_sqlite_changed hook from AdminRest::archive_post'); */
                    do_action('archm_sqlite_changed');

                    return rest_ensure_response(
                        [
                            'success' => true,
                            'message' => $archive_info['message'],
                            'data'    => $archive_status,
                        ]
                    );
                }

                return new \WP_Error(
                    'archive_failed',
                    $archive_info['message'],
                    [ 'status' => 400 ]
                );

            } catch ( \Exception $e ) {
                return new \WP_Error(
                    'rest_error',
                    $e->getMessage(),
                    [ 'status' => 500 ]
                );
            }
        }

        /**
         * Install WordPress in remote database
         *
         * @param WP_REST_Request $request
         * @return WP_REST_Response|WP_Error
         */
        public function install_wordpress( $request ) {
            try {
                $schema_status = $this->db_migration()->get_schema_status();

                if ( ! $schema_status['status'] ) {
                    $create_schema = $this->db_migration()->create_schema();
                    if ( $create_schema['status'] ) {
                        return rest_ensure_response(
                            [
                                'success' => true,
                                'message' => __('Successfully WordPress Schema Created! Now Start archiving', 'archive-master'),
                            ]
                        );
                    }

                    throw new \Exception(__('Failed to create WordPress schema', 'archive-master'));
                }

                return rest_ensure_response(
                    [
                        'success' => true,
                        'message' => __('Ready to Archive', 'archive-master'),
                    ]
                );

            } catch ( \Exception $e ) {
                return new \WP_Error(
                    'rest_error',
                    $e->getMessage(),
                    [ 'status' => 500 ]
                );
            }
        }

        /**
         * Get archived orders
         *
         * @param WP_REST_Request $request
         * @return WP_REST_Response|WP_Error
         */
        public function get_archived_order( $request ) {
            try {
                $pagination_info   = $this->io()->sanitize($request->get_params());
                $table_name        = $this->wpdb->prefix . 'archived_orders';
                $selected_database = get_option('archm_selected_database');

                // Logger::logMessage($pagination_info);

                if ( 'sqlite' === $selected_database || 'google_drive' === $selected_database ) {
                    $sqlite          = Sqlite::get_instance();
                    $orders_per_page = (int) $pagination_info['ordersperpage'];
                    $start_index     = (int) $pagination_info['startindex'];

                    // Build where clauses for filters
                    $where_clauses = [];
                    $query_params  = [];

                    // Handle status filters
                    if ( ! empty($pagination_info['filter_status']) && is_array($pagination_info['filter_status']) ) {
                        $status_conditions = [];
                        foreach ( $pagination_info['filter_status'] as $status ) {
                            $status_conditions[] = "json_extract(json(order_data), '$.status') = ?";
                            $query_params[]      = $status;
                        }
                        if ( ! empty($status_conditions) ) {
                            $where_clauses[] = '(' . implode(' OR ', $status_conditions) . ')';
                        }
                    }

                    // Handle date range filters
                    if ( ! empty($pagination_info['filter_date_from']) ) {
                        $where_clauses[] = "json_extract(json(order_data), '$.date_created.date') >= ?";
                        $query_params[]  = $pagination_info['filter_date_from'] . ' 00:00:00';
                    }
                    if ( ! empty($pagination_info['filter_date_to']) ) {
                        $where_clauses[] = "json_extract(json(order_data), '$.date_created.date') <= ?";
                        $query_params[]  = $pagination_info['filter_date_to'] . ' 23:59:59';
                    }

                    // Build the final where clause
                    $where_sql = ! empty($where_clauses) ? 'WHERE ' . implode(' AND ', $where_clauses) : '';

                    // Construct the main query
                    $query = "SELECT * FROM {$table_name}
                             {$where_sql}
                             ORDER BY id DESC
                             LIMIT ? OFFSET ?";

                    // phpcs:disable WordPress.DB.RestrictedClasses.mysql__PDO
                    // phpcs:disable WordPress.DB.RestrictedClasses.mysql__PDO
                    $stmt = $sqlite->sqdb->prepare($query);
                    // error_log($query);

                    // Bind filter parameters
                    foreach ( $query_params as $i => $param ) {
                        $stmt->bindValue($i + 1, $param);
                    }
                    // Bind pagination parameters
                    $stmt->bindValue(count($query_params) + 1, $orders_per_page, \PDO::PARAM_INT);
                    $stmt->bindValue(count($query_params) + 2, $start_index, \PDO::PARAM_INT);

                    $stmt->execute();
                    $orders = $stmt->fetchAll(\PDO::FETCH_ASSOC);

                    if ( $orders ) {
                        // Count total results with filters
                        $count_query = "SELECT COUNT(*) as count FROM {$table_name} {$where_sql}";
                        $count_stmt  = $sqlite->sqdb->prepare($count_query);

                        // Bind parameters for count query (excluding pagination)
                        foreach ( $query_params as $i => $param ) {
                            $count_stmt->bindValue($i + 1, $param);
                        }

                        $count_stmt->execute();
                        $result      = $count_stmt->fetch(\PDO::FETCH_ASSOC);
                        $total_order = $result['count'];
                    }
                    // phpcs:enable WordPress.DB.RestrictedClasses.mysql__PDO
                }

                if ( 'rds' === $selected_database || 'custom_mysql' === $selected_database ) {
                    if ( ! $this->is_bd_connected() ) {
                        $response['dbConnectionError'] = true;
                        $this->io()->send_json(false, __('Database not connected', 'archive-master'), $response);
                    }

                    // Set remote db instance
                    $this->set_db_instance();

                    // Check if remote_db was initialized
                    if ( null === $this->remote_db ) {
                        $response['dbConnectionError'] = true;
                        $this->io()->send_json(false, __('Remote database connection not available. Please check your database credentials.', 'archive-master'), $response);
                    }

                    // Build where clauses for filters
                    $where_clauses = [];
                    $query_params  = [];

                    // Handle status filters
                    if ( ! empty($pagination_info['filter_status']) && is_array($pagination_info['filter_status']) ) {
                        $status_conditions = [];
                        foreach ( $pagination_info['filter_status'] as $status ) {
                            $status_conditions[] = "JSON_EXTRACT(order_data, '$.status') = %s";
                            $query_params[]      = $status;
                        }
                        if ( ! empty($status_conditions) ) {
                            $where_clauses[] = '(' . implode(' OR ', $status_conditions) . ')';
                        }
                    }

                    // Handle date range filters
                    if ( ! empty($pagination_info['filter_date_from']) ) {
                        $where_clauses[] = "JSON_UNQUOTE(JSON_EXTRACT(order_data, '$.date_created.date')) >= %s";
                        $query_params[]  = $pagination_info['filter_date_from'] . ' 00:00:00';
                    }
                    if ( ! empty($pagination_info['filter_date_to']) ) {
                        $where_clauses[] = "JSON_UNQUOTE(JSON_EXTRACT(order_data, '$.date_created.date')) <= %s";
                        $query_params[]  = $pagination_info['filter_date_to'] . ' 23:59:59';
                    }

                    // Build the final where clause
                    $where_sql = ! empty($where_clauses) ? 'WHERE ' . implode(' AND ', $where_clauses) : '';

                    // Construct the main query with pagination
                    $query = $this->remote_db->prepare(
                        "SELECT * FROM {$table_name}
                        {$where_sql}
                        ORDER BY id DESC
                        LIMIT %d OFFSET %d",
                        array_merge(
                            $query_params,
                            [ (int) $pagination_info['ordersperpage'], (int) $pagination_info['startindex'] ]
                        )
                    );

                    $orders = $this->remote_db->get_results($query, ARRAY_A);

                    if ( $orders ) {
                        // Count total results with filters
                        $count_query = $this->remote_db->prepare(
                            "SELECT COUNT(*) AS total_orders FROM {$table_name} {$where_sql}",
                            $query_params
                        );
                        $total_order = $this->remote_db->get_var($count_query);
                    }
                }

                if ( $orders ) {
                    foreach ( $orders as $key => $order ) {
                        $orders[ $key ]['is_selected'] = false;
                    }
                    return rest_ensure_response(
                        [
                            'success' => true,
                            'message' => __('Archived Orders', 'archive-master'),
                            'data'    => [
                                'orders'      => $orders,
                                'total_order' => $total_order,
                            ],
                        ]
                    );
                }

                return rest_ensure_response(
                    [
                        'success' => false,
                        'message' => __('No orders archived in RDS', 'archive-master'),
                        'data'    => [],
                    ]
                );

            } catch ( \Exception $e ) {
                return new \WP_Error(
                    'rest_error',
                    $e->getMessage(),
                    [ 'status' => 500 ]
                );
            }
        }

        /**
         * Get archived orders
         *
         * @param WP_REST_Request $request
         * @return WP_REST_Response|WP_Error
         */
        public function remote_archive_status( $request ) {
            try {
                $archived_orders      = count(get_option('archm_queue_batch_ids', []));
                $total_archived_order = get_option('archm_archive_orders_count', 0);
                $loop                 = true;
                if ( 0 !== (int) $archived_orders && 0 !== (int) $total_archived_order && ( (int) $archived_orders === (int) $total_archived_order ) ) {
                    delete_option('archm_queue_batch_ids');
                    delete_option('archm_archive_orders_count');
                    $this->option()->update('background_process_running', false);
                    $loop = false;
                }

                return rest_ensure_response(
                    [
                        'success' => true,
                        'message' => __('Order Status', 'archive-master'),
                        'data'    => [
                            'archivedOrders'     => $archived_orders,
                            'totalArchivedOrder' => $total_archived_order,
                            'loop'               => $loop,
                        ],
                    ]
                );
            } catch ( \Exception $e ) {
                return new \WP_Error(
                    'rest_error',
                    $e->getMessage(),
                    [ 'status' => 500 ]
                );
            }
        }

        /**
         * Unarchive orders
         *
         * @param WP_REST_Request $request
         * @return WP_REST_Response|WP_Error
         */
        public function unarchived_order( $request ) {
            try {
                $perams             = $this->io()->sanitize($request->get_json_params());
                $orders             = $perams['orders'];
                $unarchiving_status = $perams['unarciev_status'];
                $allow_duplicate    = $perams['allow_duplicate_unarchiving'];
                $unarchive_info     = $this->procesed_unarchived_order($orders, $unarchiving_status, $allow_duplicate);
                do_action('archm_sqlite_changed');
                do_action('archm_order_unarchived');
                return rest_ensure_response($unarchive_info);

            } catch ( \Exception $e ) {
                return new \WP_Error(
                    'rest_error',
                    $e->getMessage(),
                    [ 'status' => 500 ]
                );
            }
        }

        /**
         * Fixed Processed Unarchive Orders method with cancellation awareness
         */
        public function procesed_unarchived_order( $orders, $unarchiving_status, $allow_duplicate ) {
            // Disable order-sync hooks to prevent performance issues during unarchiving
            $this->disable_order_sync_hooks();

            $unarchived_order_ids         = [];
            $archived_order_ids_for_cache = []; // Track original archived order IDs for cache deletion
            $duplicate_orders             = [];
            $has_duplicate_order          = false;

            try {
                // error_log('AdminRest: Starting to process ' . count($orders) . ' orders');

                foreach ( $orders as $index => $order ) {
                    // CHECK FOR CANCELLATION BEFORE EACH ORDER
                    if ( get_option('arcm_unarchive_cancelled', false) ) {
                        break; // Stop processing immediately
                    }

                    $order_data        = json_decode($order['order_data'], true);
                    $ordered_product   = json_decode($order['ordered_products'], true);

                    if ( ! is_array( $order_data ) || ! isset( $order_data['id'] ) ) {
                        continue;
                    }

                    $original_order_id = $order_data['id'];
                    $is_bublicat_order = false;

                    // error_log('AdminRest: Processing order ID: ' . $original_order_id . ' (' . ($index + 1) . '/' . count($orders) . ')');

                    // Check if order with this ID already exists
                    $existing_order = wc_get_order($original_order_id);
                    if ( $existing_order && $existing_order->get_id() ) {
                        $is_bublicat_order = true;
                        if ( ! $allow_duplicate ) {
                            $has_duplicate_order = true;
                            if ( 'bulk' === $unarchiving_status ) {
                                $duplicate_orders[] = $order;
                                continue;
                            }
                        }
                    }

                    // Process the individual order
                    $result = $this->restore_order($order_data, $ordered_product, $unarchived_order_ids, $is_bublicat_order);

                    // Track original archived order ID for cache deletion (always use original ID from archived data)
                    if ( false !== $result ) {
                        $archived_order_ids_for_cache[] = $original_order_id;
                    }

                    // Commented out logging for performance
                    // if ( false !== $result ) {
                    // error_log('AdminRest: Successfully processed order ID: ' . $original_order_id);
                    // } else {
                    // error_log('AdminRest: Failed to process order ID: ' . $original_order_id);
                    // }

                                   // Add a small delay to allow cancellation detection
                    usleep(10000); // 0.01 second delay between orders
                }

                // Final check after processing - commented out for performance
                // if ( get_option('arcm_unarchive_cancelled', false) ) {
                // error_log('AdminRest: Final cancellation check - process was cancelled');
                // error_log('AdminRest: Total orders actually processed: ' . count($unarchived_order_ids));
                // }

                if ( ! $allow_duplicate ) {
                    if ( count($duplicate_orders) > 0 ) {
                        $existing_duplicate_orders = get_option('arcm_dublicate_order', []);
                        $duplicate_orders          = array_merge($existing_duplicate_orders, $duplicate_orders);
                        update_option('arcm_dublicate_order', $duplicate_orders);
                    }
                } else {
                    $existing_duplicate_orders = get_option('arcm_dublicate_order', []);
                    if ( ! empty($existing_duplicate_orders) ) {
                        delete_option('arcm_dublicate_order');
                    }
                }

                // error_log('AdminRest: Completed processing. Unarchived: ' . count($unarchived_order_ids) . ', Duplicates: ' . count($duplicate_orders));

                // Trigger analytics cache clearing after all orders have been unarchived
                // Use original archived order IDs for cache deletion (these match what's stored in cache)
                if ( ! empty($archived_order_ids_for_cache) ) {
                    do_action('archm_order_unarchived', array_unique($archived_order_ids_for_cache));
                }

                return [
                    'success' => true,
                    'message' => __('Successfully Unarchived Orders', 'archive-master'),
                    'data'    => [
                        'unarchiveOrder'  => $unarchived_order_ids,
                        'hasDuplicate'    => $has_duplicate_order,
                        'duplicateOrders' => $duplicate_orders,
                    ],
                ];
            } finally {
                // Always re-enable hooks, even if there's an error or cancellation
                $this->enable_order_sync_hooks();
            }
        }

        /**
         * Disable WooCommerce emails during order restoration
         */
        private function disable_woocommerce_emails() {
            // Remove all email notification actions
            $email_actions = [
                'woocommerce_order_status_pending_to_processing_notification',
                'woocommerce_order_status_pending_to_completed_notification',
                'woocommerce_order_status_pending_to_on-hold_notification',
                'woocommerce_order_status_failed_to_processing_notification',
                'woocommerce_order_status_failed_to_completed_notification',
                'woocommerce_order_status_failed_to_on-hold_notification',
                'woocommerce_order_status_cancelled_to_processing_notification',
                'woocommerce_order_status_cancelled_to_completed_notification',
                'woocommerce_order_status_cancelled_to_on-hold_notification',
                'woocommerce_new_order_notification',
                'woocommerce_order_status_refunded_notification',
                'woocommerce_order_fully_refunded_notification',
                'woocommerce_order_partially_refunded_notification',
            ];

            foreach ( $email_actions as $action ) {
                remove_all_actions($action);
            }

            // Also disable via filter
            add_filter('woocommerce_email_enabled_new_order', '__return_false', 999);
            add_filter('woocommerce_email_enabled_customer_processing_order', '__return_false', 999);
            add_filter('woocommerce_email_enabled_customer_completed_order', '__return_false', 999);
            add_filter('woocommerce_email_enabled_customer_on_hold_order', '__return_false', 999);
            add_filter('woocommerce_email_enabled_customer_refunded_order', '__return_false', 999);
            add_filter('woocommerce_email_enabled_customer_partially_refunded_order', '__return_false', 999);
        }

        /**
         * Re-enable WooCommerce emails after order restoration
         */
        private function enable_woocommerce_emails() {
            remove_filter('woocommerce_email_enabled_new_order', '__return_false', 999);
            remove_filter('woocommerce_email_enabled_customer_processing_order', '__return_false', 999);
            remove_filter('woocommerce_email_enabled_customer_completed_order', '__return_false', 999);
            remove_filter('woocommerce_email_enabled_customer_on_hold_order', '__return_false', 999);
            remove_filter('woocommerce_email_enabled_customer_refunded_order', '__return_false', 999);
            remove_filter('woocommerce_email_enabled_customer_partially_refunded_order', '__return_false', 999);
        }

        /**
         * Ensure billing email passes WooCommerce validation on save (empty/invalid emails in archive would throw).
         *
         * @param mixed $email    Raw email from archived order.
         * @param int   $order_id WooCommerce order ID being restored.
         * @return string
         */
        private function normalize_billing_email_for_restore( $email, $order_id ) {
            $email = is_string( $email ) ? sanitize_email( trim( $email ) ) : '';
            if ( $email && is_email( $email ) ) {
                return $email;
            }
            $host = wp_parse_url( home_url(), PHP_URL_HOST );
            if ( empty( $host ) || ! is_string( $host ) ) {
                $host = 'example.com';
            }
            $host = preg_replace( '/[^a-zA-Z0-9.-]/', '', $host );
            if ( '' === $host ) {
                $host = 'example.com';
            }
            $fallback = sprintf( 'archived-restore-%d@%s', (int) $order_id, $host );
            if ( is_email( $fallback ) ) {
                return $fallback;
            }

            return 'archived-restore-' . (int) $order_id . '@example.com';
        }

        /**
         * Enhanced restore_order method that returns success/failure status
         */
        private function restore_order( $order_data, $ordered_product, &$unarchived_order_ids, $is_bublicat_order ) {
            global $wpdb;

            try {
                // Check for cancellation at the start of restore_order
                if ( get_option('arcm_unarchive_cancelled', false) ) {
                    return false;
                }

                $original_order_id = $order_data['id'];

                if ( ! $this->is_hpos_enabled() ) {
                    // When HPOS is disabled, orders are stored as posts
                    // We need to insert directly into wp_posts table to preserve the original ID

                    if ( ! $is_bublicat_order ) {
                        // First, insert the post with the original ID
                        // Set post_author to current user if available, otherwise use admin (ID 1)
                        $post_author = get_current_user_id() > 0 ? get_current_user_id() : 1;

                        // Generate proper GUID for the order
                        $guid = home_url('/?post_type=shop_order&p=' . $original_order_id);

                        $post_data = [
                            'ID'                    => $original_order_id,
                            'post_author'           => $post_author,
                            'post_date'             => $order_data['date_created']['date'],
                            'post_date_gmt'         => get_gmt_from_date($order_data['date_created']['date']),
                            'post_content'          => '',
                            'post_title'            => 'Order &ndash; ' . $order_data['date_created']['date'],
                            'post_excerpt'          => '',
                            'post_status'           => 'wc-' . $order_data['status'],
                            'comment_status'        => 'open',
                            'ping_status'           => 'closed',
                            'post_password'         => uniqid('order_'),
                            'post_name'             => 'wc_order_' . $original_order_id,
                            'to_ping'               => '',
                            'pinged'                => '',
                            'post_modified'         => current_time('mysql'),
                            'post_modified_gmt'     => current_time('mysql', 1),
                            'post_content_filtered' => '',
                            'post_parent'           => 0,
                            'guid'                  => $guid,
                            'menu_order'            => 0,
                            'post_type'             => 'shop_order',
                            'post_mime_type'        => '',
                            'comment_count'         => 0,
                        ];

                                                                           // Insert the post directly
                        $result = $wpdb->insert($wpdb->posts, $post_data); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery

                        if ( ! $result ) {
                            throw new \Exception('Failed to insert order post');
                        }

                        // CRITICAL: Clear all caches FIRST before creating WC_Order object
                        // This ensures WordPress loads fresh data from database
                        wp_cache_delete($original_order_id, 'posts');
                        wp_cache_delete($original_order_id, 'post_meta');
                        clean_post_cache($original_order_id);
                        wc_delete_shop_order_transients($original_order_id);

                        // Now create the WC_Order object from the existing post
                        $order = new \WC_Order($original_order_id);
                    } else {
                        // If it's a duplicate, create a new order normally
                        $order = new \WC_Order();
                    }
                } else {
                    // HPOS is enabled, use the existing logic
                    $order = new \WC_Order();
                    if ( ! $is_bublicat_order ) {
                        $order->set_id($original_order_id);
                    }
                }

                // Check for cancellation before setting order data
                if ( get_option('arcm_unarchive_cancelled', false) ) {
                    return false;
                }

                // Unarchive must not link customer_id to WP users with missing/invalid emails — WC_Customer / wc_create_new_customer
                // will throw "Please provide a valid email address." during save. Keep original id in meta for reference.
                $stored_customer_id = isset( $order_data['customer_id'] ) ? (int) $order_data['customer_id'] : 0;
                if ( $stored_customer_id > 0 ) {
                    $order->update_meta_data( '_archm_original_customer_id', $stored_customer_id );
                }
                $order->set_customer_id( 0 );

                $order->set_date_created($order_data['date_created']['date']);
                $order->set_currency($order_data['currency']);
                $order->set_total($order_data['total']);
                $order->set_status($order_data['status']);
                $order->set_prices_include_tax($order_data['prices_include_tax']);
                $order->set_payment_method($order_data['payment_method']);

                // Mark order as restored to prevent new order emails
                $order->update_meta_data('_created_via', 'restore');

                // Set customer IP address and user agent if available
                if ( isset($order_data['customer_ip_address']) ) {
                    $order->set_customer_ip_address($order_data['customer_ip_address']);
                }
                if ( isset($order_data['customer_user_agent']) ) {
                    $order->set_customer_user_agent($order_data['customer_user_agent']);
                }

                // Set billing data
                $order->set_billing_first_name($order_data['billing']['first_name']);
                $order->set_billing_last_name($order_data['billing']['last_name']);
                $order->set_billing_company(isset($order_data['billing']['company']) ? $order_data['billing']['company'] : '');
                $order->set_billing_address_1(isset($order_data['billing']['address_1']) ? $order_data['billing']['address_1'] : '');
                $order->set_billing_address_2(isset($order_data['billing']['address_2']) ? $order_data['billing']['address_2'] : '');
                $order->set_billing_city($order_data['billing']['city']);
                $order->set_billing_state(isset($order_data['billing']['state']) ? $order_data['billing']['state'] : '');
                $order->set_billing_postcode($order_data['billing']['postcode']);
                $order->set_billing_country($order_data['billing']['country']);
                $billing_email_raw = isset( $order_data['billing']['email'] ) ? $order_data['billing']['email'] : '';
                $order->set_billing_email( $this->normalize_billing_email_for_restore( $billing_email_raw, (int) $original_order_id ) );
                $order->set_billing_phone($order_data['billing']['phone']);

                // Set shipping data
                $order->set_shipping_first_name(isset($order_data['shipping']['first_name']) ? $order_data['shipping']['first_name'] : $order_data['billing']['first_name']);
                $order->set_shipping_last_name(isset($order_data['shipping']['last_name']) ? $order_data['shipping']['last_name'] : $order_data['billing']['last_name']);
                $order->set_shipping_company(isset($order_data['shipping']['company']) ? $order_data['shipping']['company'] : '');
                $order->set_shipping_address_1($order_data['shipping']['address_1']);
                $order->set_shipping_address_2($order_data['shipping']['address_2']);
                $order->set_shipping_city($order_data['shipping']['city']);
                $order->set_shipping_state(isset($order_data['shipping']['state']) ? $order_data['shipping']['state'] : '');
                $order->set_shipping_postcode($order_data['shipping']['postcode']);
                $order->set_shipping_country($order_data['shipping']['country']);

                // Add products
                foreach ( $ordered_product as $item ) {
                    // Check for cancellation before adding each product
                    if ( get_option('arcm_unarchive_cancelled', false) ) {
                        return false;
                    }

                    // Check if this is a variable product with a variation
                    // If variation_id exists and is not 0, load the variation product instead of parent
                    $product_id = isset($item['variation_id']) && ! empty($item['variation_id']) && (int) $item['variation_id'] > 0
                        ? (int) $item['variation_id']
                        : (int) $item['product_id'];

                    $product = wc_get_product($product_id);
                    if ( $product ) {
                        $order->add_product($product, $item['quantity']);
                    }
                }

                // Restore coupons if they exist in archived order data
                // Handle both new format (array with coupon data) and old format (object with item IDs)
                if ( ! empty($order_data['coupon_lines']) ) {
                    // Check if coupon_lines is an array (new format with actual coupon data)
                    if ( is_array($order_data['coupon_lines']) ) {
                        foreach ( $order_data['coupon_lines'] as $coupon_line ) {
                            // Check for cancellation before adding each coupon
                            if ( get_option('arcm_unarchive_cancelled', false) ) {
                                return false;
                            }

                            // Skip if this is just an item ID key (old format)
                            if ( ! is_array($coupon_line) || ( isset($coupon_line['legacy_values']) && ! isset($coupon_line['code']) ) ) {
                                continue;
                            }

                            // Extract coupon data - handle multiple data formats
                            $coupon_code     = '';
                            $discount_amount = 0;
                            $discount_tax    = 0;

                            // Handle different data structures
                            if ( isset($coupon_line['code']) ) {
                                // Direct array format (from format_coupon_lines or enhanced archiving)
                                $coupon_code     = $coupon_line['code'];
                                $discount_amount = isset($coupon_line['discount']) ? floatval($coupon_line['discount']) : 0;
                                $discount_tax    = isset($coupon_line['discount_tax']) ? floatval($coupon_line['discount_tax']) : 0;
                            } elseif ( isset($coupon_line['name']) ) {
                                // WC_Order_Item_Coupon format (from get_data() - name is alias for code)
                                $coupon_code     = $coupon_line['name'];
                                $discount_amount = isset($coupon_line['discount']) ? floatval($coupon_line['discount']) : 0;
                                $discount_tax    = isset($coupon_line['discount_tax']) ? floatval($coupon_line['discount_tax']) : 0;
                            }

                            // Fallback: try to get coupon code from meta_data if not found directly
                            if ( empty($coupon_code) && ! empty($coupon_line['meta_data']) && is_array($coupon_line['meta_data']) ) {
                                foreach ( $coupon_line['meta_data'] as $meta ) {
                                    if ( isset($meta['key']) && 'coupon_code' === $meta['key'] && isset($meta['value']) ) {
                                        $coupon_code = $meta['value'];
                                        break;
                                    }
                                }
                            }

                            // Only add coupon if we have a valid code
                            if ( ! empty($coupon_code) ) {
                                try {
                                    // Create coupon item
                                    $coupon_item = new \WC_Order_Item_Coupon();
                                    $coupon_item->set_code($coupon_code);
                                    $coupon_item->set_discount($discount_amount);
                                    $coupon_item->set_discount_tax($discount_tax);

                                    // Add meta data if available
                                    if ( ! empty($coupon_line['meta_data']) && is_array($coupon_line['meta_data']) ) {
                                        foreach ( $coupon_line['meta_data'] as $meta ) {
                                            if ( isset($meta['key']) && isset($meta['value']) ) {
                                                // Skip coupon_code meta as we already set it via set_code()
                                                if ( 'coupon_code' !== $meta['key'] ) {
                                                    $coupon_item->add_meta_data($meta['key'], $meta['value']);
                                                }
                                            }
                                        }
                                    }

                                    // Add coupon item to order
                                    $order->add_item($coupon_item);
                                } catch ( \Exception $e ) {
                                    // Log error but continue with order restoration
                                    Logger::logError('AdminRest: Failed to restore coupon "' . $coupon_code . '" for order ID ' . $original_order_id . ': ' . $e->getMessage());
                                }
                            }
                        }
                    } elseif ( is_object($order_data['coupon_lines']) ) {
                        // Old format: coupon_lines is an object with item IDs as keys
                        // This format doesn't contain actual coupon data, so we skip creating coupon items
                        // The discount_total will still be set below to preserve the order total
                        // No action needed - old format handled by discount_total preservation
                        $order->set_discount_total($order_data['discount_total']);
                    }
                }

                // Restore tax items if they exist in archived order data
                if ( ! empty($order_data['tax_lines']) && is_array($order_data['tax_lines']) ) {
                    foreach ( $order_data['tax_lines'] as $tax_line ) {
                        // Check for cancellation before adding each tax item
                        if ( get_option('arcm_unarchive_cancelled', false) ) {
                            return false;
                        }

                        // Skip if this is not a valid tax line array
                        if ( ! is_array($tax_line) ) {
                            continue;
                        }

                        // Extract tax data
                        $rate_code          = isset($tax_line['rate_code']) ? $tax_line['rate_code'] : '';
                        $rate_id            = isset($tax_line['rate_id']) ? intval($tax_line['rate_id']) : 0;
                        $label              = isset($tax_line['label']) ? $tax_line['label'] : '';
                        $compound           = isset($tax_line['compound']) ? (bool) $tax_line['compound'] : false;
                        $tax_total          = isset($tax_line['tax_total']) ? floatval($tax_line['tax_total']) : 0;
                        $shipping_tax_total = isset($tax_line['shipping_tax_total']) ? floatval($tax_line['shipping_tax_total']) : 0;

                        // Only add tax item if we have valid data
                        if ( ! empty($rate_code) || $rate_id > 0 ) {
                            try {
                                // Create tax item
                                $tax_item = new \WC_Order_Item_Tax();

                                if ( ! empty($rate_code) ) {
                                    $tax_item->set_rate_code($rate_code);
                                }
                                if ( $rate_id > 0 ) {
                                    $tax_item->set_rate_id($rate_id);
                                }
                                if ( ! empty($label) ) {
                                    $tax_item->set_label($label);
                                }
                                $tax_item->set_compound($compound);
                                $tax_item->set_tax_total($tax_total);
                                $tax_item->set_shipping_tax_total($shipping_tax_total);

                                // Add meta data if available
                                if ( ! empty($tax_line['meta_data']) && is_array($tax_line['meta_data']) ) {
                                    foreach ( $tax_line['meta_data'] as $meta ) {
                                        if ( isset($meta['key']) && isset($meta['value']) ) {
                                            $tax_item->add_meta_data($meta['key'], $meta['value']);
                                        }
                                    }
                                }

                                // Add tax item to order
                                $order->add_item($tax_item);
                            } catch ( \Exception $e ) {
                                // Log error but continue with order restoration
                                Logger::logError('AdminRest: Failed to restore tax item "' . $rate_code . '" (rate_id: ' . $rate_id . ') for order ID ' . $original_order_id . ': ' . $e->getMessage());
                            }
                        }
                    }
                }

                // Set tax totals if available (for backward compatibility and to ensure totals are correct)
                // Note: WooCommerce automatically calculates total_tax from cart_tax + shipping_tax
                if ( isset($order_data['cart_tax']) ) {
                    $order->set_cart_tax(floatval($order_data['cart_tax']));
                }
                if ( isset($order_data['shipping_tax']) ) {
                    $order->set_shipping_tax(floatval($order_data['shipping_tax']));
                }
                // total_tax is calculated automatically by WooCommerce from cart_tax + shipping_tax
                // No need to set it explicitly as set_total_tax() is a protected method

                // Set discount total if available (for backward compatibility)
                if ( isset($order_data['discount_total']) ) {
                    $order->set_discount_total(floatval($order_data['discount_total']));
                }

                // Final cancellation check before saving
                if ( get_option('arcm_unarchive_cancelled', false) ) {
                    return false;
                }

                // Last-line normalize — guest order + billing must be valid before WC persists (customer sync).
                $billing_for_save = $order->get_billing_email( 'edit' );
                if ( '' === $billing_for_save && isset( $order_data['billing']['email'] ) ) {
                    $billing_for_save = $order_data['billing']['email'];
                }
                $order->set_billing_email( $this->normalize_billing_email_for_restore( $billing_for_save, (int) $original_order_id ) );

                // Disable WooCommerce emails before saving to prevent confirmation emails
                $this->disable_woocommerce_emails();

                // Mark order as already having sent the new order email
                $order->update_meta_data('_new_order_email_sent', 'true');

                // Save the order
                $main_order_id = $order->save();

                // Re-enable WooCommerce emails after saving
                $this->enable_woocommerce_emails();

                if ( $main_order_id ) {
                    // Additional fixes for HPOS-enabled orders
                    if ( $this->is_hpos_enabled() && ! $is_bublicat_order ) {
                        // CRITICAL FIX: Even with HPOS enabled, WordPress capability checks may still require a wp_posts entry
                        // Check if placeholder post exists in wp_posts
                        $existing_post = $wpdb->get_var($wpdb->prepare("SELECT ID FROM {$wpdb->posts} WHERE ID = %d AND post_type = 'shop_order'", $main_order_id));

                        if ( ! $existing_post ) {

                            // Create placeholder post in wp_posts for capability checks
                            $post_author = get_current_user_id() > 0 ? get_current_user_id() : 1;
                            $guid        = home_url('/?post_type=shop_order&p=' . $main_order_id);

                            $post_data = [
                                'ID'                    => $main_order_id,
                                'post_author'           => $post_author,
                                'post_date'             => $order_data['date_created']['date'],
                                'post_date_gmt'         => get_gmt_from_date($order_data['date_created']['date']),
                                'post_content'          => '',
                                'post_title'            => 'Order &ndash; ' . $order_data['date_created']['date'],
                                'post_excerpt'          => '',
                                'post_status'           => 'wc-' . $order_data['status'],
                                'comment_status'        => 'closed',
                                'ping_status'           => 'closed',
                                'post_password'         => '',
                                'post_name'             => 'order-' . $main_order_id,
                                'to_ping'               => '',
                                'pinged'                => '',
                                'post_modified'         => current_time('mysql'),
                                'post_modified_gmt'     => current_time('mysql', 1),
                                'post_content_filtered' => '',
                                'post_parent'           => 0,
                                'guid'                  => $guid,
                                'menu_order'            => 0,
                                'post_type'             => 'shop_order',
                                'post_mime_type'        => '',
                                'comment_count'         => 0,
                            ];

                            $result = $wpdb->insert($wpdb->posts, $post_data);
                            if ( $result ) {
                                clean_post_cache($main_order_id);
                                wp_cache_delete($main_order_id, 'posts');
                            }
                        }

                        // Check what's actually in the HPOS orders table
                        $orders_table = $wpdb->prefix . 'wc_orders';
                        // Table name is safe - comes from $wpdb->prefix which is sanitized by WordPress core
                        // Table names cannot be parameterized in prepared statements, so interpolation is required
                        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name validated: $wpdb->prefix is WordPress-sanitized, 'wc_orders' is a literal string
                        $order_row = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$orders_table} WHERE id = %d", $main_order_id), ARRAY_A);

                        if ( $order_row ) {
                            // CRITICAL FIX: Ensure customer_id is 0 (not NULL) for guest orders in HPOS table
                            // WordPress capability checks query HPOS table directly, and NULL != 0
                            $customer_id_value = isset($order_row['customer_id']) ? $order_row['customer_id'] : null;

                            // If customer_id is NULL, empty string, or not set, set it to 0 for guest orders
                            if ( null === $customer_id_value || '' === $customer_id_value || ( ! isset($order_row['customer_id']) ) ) {
                                $wpdb->update(
                                    $orders_table,
                                    [ 'customer_id' => 0 ],
                                    [ 'id' => $main_order_id ],
                                    [ '%d' ],
                                    [ '%d' ]
                                );
                            }

                            // Also ensure parent_order_id is NULL (not empty string) if it should be empty
                            $parent_order_id_value = isset($order_row['parent_order_id']) ? $order_row['parent_order_id'] : null;
                            if ( empty($parent_order_id_value) && null !== $parent_order_id_value ) {
                                $wpdb->update(
                                    $orders_table,
                                    [ 'parent_order_id' => null ],
                                    [ 'id' => $main_order_id ],
                                    [ null ],
                                    [ '%d' ]
                                );
                            }
                        }

                        // Clear all order-related caches
                        wp_cache_delete($main_order_id, 'orders');
                        wp_cache_delete($original_order_id, 'orders');
                        wc_delete_shop_order_transients($main_order_id);

                    }

                    // Fix post_status in wp_posts table when HPOS is disabled
                    if ( ! $this->is_hpos_enabled() && ! $is_bublicat_order ) {
                        // Ensure the post_status is correctly set in wp_posts table
                        $correct_post_status = 'wc-' . $order_data['status'];
                        $wpdb->update(
                            $wpdb->posts,
                            [ 'post_status' => $correct_post_status ],
                            [ 'ID' => $original_order_id ],
                            [ '%s' ],
                            [ '%d' ]
                        );

                        // Verify and update post_type to ensure it's recognized as shop_order
                        $wpdb->update(
                            $wpdb->posts,
                            [ 'post_type' => 'shop_order' ],
                            [ 'ID' => $original_order_id ],
                            [ '%s' ],
                            [ '%d' ]
                        );
                    }

                    // Clear caches
                    if ( ! $this->is_hpos_enabled() ) {
                        wp_cache_delete($original_order_id, 'posts');
                        wp_cache_delete($original_order_id, 'post_meta');
                        wc_delete_shop_order_transients($original_order_id);
                        clean_post_cache($original_order_id);
                    }

                    // Refresh the order object to ensure all data is synced
                    $order = wc_get_order($main_order_id);
                    if ( $order ) {
                        // Restore custom order meta keys if they were archived
                        if ( isset($order_data['custom_meta']) && is_array($order_data['custom_meta']) ) {
                            foreach ( $order_data['custom_meta'] as $meta_key => $meta_value ) {
                                $order->update_meta_data($meta_key, $meta_value);
                            }
                            // Save the order to persist meta data changes
                            $order->save();
                        }

                        // Trigger WooCommerce hooks to ensure proper integration,
                        // but keep transactional emails disabled during restore.
                        $this->disable_woocommerce_emails();
                        // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedHooknameFound -- This is a WooCommerce core hook pattern woocommerce_order_status_{status}
                        do_action('woocommerce_order_status_' . $order_data['status'], $main_order_id, $order);
                        $this->enable_woocommerce_emails();
                    }

                    $unarchived_order_ids[] = $is_bublicat_order ? $original_order_id : $main_order_id;
                    return true; // Success
                }

                return false; // Failed to save

            } catch ( \Exception $e ) {
                // Ensure emails are re-enabled even if there's an error
                $this->enable_woocommerce_emails();
                return false;
            }
        }

        /**
         * Delete unarchived orders
         *
         * @param WP_REST_Request $request
         * @return WP_REST_Response|WP_Error
         */
        public function delete_unarchived_order( $request ) {
            try {
                $order_ids         = $this->io()->sanitize($request->get_json_params());
                $selected_database = get_option('archm_selected_database');
                $order_deleted     = $this->processed_unarchive_delete($order_ids);

                if ( 'google_drive' === $selected_database ) {
                    if ( function_exists('master_archive_pro') ) {
                        // Ensure Google Drive sync classes are loaded
                        if ( ! class_exists('\ArchiveMaster\GoogleDrive\Sync') ) {
                            // Load required classes from Pro plugin
                            require_once WP_PLUGIN_DIR . '/archive-master-pro/includes/class-cloud.php';
                            require_once WP_PLUGIN_DIR . '/archive-master-pro/includes/google-drive/class-google-drive-sync.php';
                        }

                        $google_sync = new \ArchiveMaster\GoogleDrive\Sync();
                        $google_sync->sync_sqlite_database();
                    }
                }

                if ( $order_deleted ) {
                    // Sync archived orders to Google Sheets if order-sync plugin is active
                    if ( $this->is_order_sync_plugin_active() ) {
                        $this->sync_orders_to_google_sheets();
                    }
                    return rest_ensure_response(
                        [
                            'success' => true,
                            'message' => __('Successfully deleted orders', 'archive-master'),
                            // 'data'    => [ 'deleted_count' => $result ],
                        ]
                    );
                }

                return rest_ensure_response(
                    [
                        'success' => false,
                        'message' => __('Failed to archive orders', 'archive-master'),
                    ]
                );

            } catch ( \Exception $e ) {
                return new \WP_Error(
                    'rest_error',
                    $e->getMessage(),
                    [ 'status' => 500 ]
                );
            }
        }

        /**
         * Processed unarchive delete
         *
         * @param array $order_ids
         * @return bool
         */
        public function processed_unarchive_delete( $order_ids ) {
            $table_name        = $this->wpdb->prefix . 'archived_orders';
            $selected_database = get_option('archm_selected_database');
            $order_deleted     = false;
            $ids_in_clause     = ! empty($order_ids) ? implode(',', array_map('intval', $order_ids)) : [];

            if ( 'sqlite' === $selected_database || 'google_drive' === $selected_database ) {
                // Always try to delete from SQLite (common storage) regardless of selected storage
                $sqlite = Sqlite::get_instance();

                // Check if SQLite is connected before attempting deletion
                if ( $sqlite->is_connected() ) {
                    try {
                        $query = "DELETE FROM {$table_name} WHERE id IN ({$ids_in_clause})";

                        // Execute the query with proper error handling
                        $result = $sqlite->sqdb->exec($query);
                        if ( false !== $result ) {
                            $order_deleted = true;
                        }

                            // Trigger hook for Google Drive sync
                            // do_action('archm_sqlite_changed');

                    } catch ( \PDOException $e ) {
                        Logger::logError('AdminRest: SQLite deletion failed with PDO error: ' . $e->getMessage());
                    }
                }
            }

            // Also delete from selected storage if it's a remote database
            if ( 'rds' === $selected_database || 'custom_mysql' === $selected_database ) {
                $remote_deleted = false;

                try {
                    // Set remote db instance
                    $this->set_db_instance();

                    $delete_query = "DELETE FROM %i WHERE id IN ({$ids_in_clause})";
                    $query        = $this->remote_db->prepare($delete_query, $table_name); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
                    $result       = $this->remote_db->query($query);

                    if ( false !== $result ) {
                        $remote_deleted = true;
                    }
                } catch ( \Exception $e ) {
                    Logger::logError('AdminRest: ' . $selected_database . ' deletion failed with error: ' . $e->getMessage());
                }

                // For remote databases, we need both SQLite and remote deletion to succeed
                // However, we return success if at least SQLite deletion worked (primary storage)
                if ( $remote_deleted ) {
                    $order_deleted = true;
                } elseif ( $order_deleted ) {
                    // SQLite deletion worked but remote failed - log warning but don't fail completely
                    Logger::logError('AdminRest: WARNING - SQLite deletion succeeded but ' . $selected_database . ' deletion failed');
                }
            }

            return $order_deleted;
        }

        /**
         * Get logs
         *
         * @param WP_REST_Request $request
         * @return WP_REST_Response|WP_Error
         */
        public function get_logs( $request ) {
            try {
                $current_page = $request->get_param('current_page');
                // Logger::logMessage('current page:' . $current_page);
                $activity_log = new ActivityLogDB();
                $response     = $activity_log->get_logs_by_page( (int) $current_page, 20);

                if ( $response['success'] ) {
                    return rest_ensure_response(
                        [
                            'success' => true,
                            'message' => __('Retrieved Logs', 'archive-master'),
                            'data'    => $response,
                        ]
                    );
                }

                return rest_ensure_response(
                    [
                        'success' => false,
                        'message' => __('No Logs Found', 'archive-master'),
                        'data'    => $response,
                    ]
                );

            } catch ( \Exception $e ) {
                return new \WP_Error(
                    'rest_error',
                    $e->getMessage(),
                    [ 'status' => 500 ]
                );
            }
        }

        /**
         * Delete logs
         *
         * @param WP_REST_Request $request
         * @return WP_REST_Response|WP_Error
         */
        public function delete_logs( $request ) {
            try {
                $logs         = $this->io()->sanitize($request->get_param('logs'));
                $activity_log = new ActivityLogDB();
                $response     = $activity_log->delete_logs($logs);

                if ( $response['success'] ) {
                    return rest_ensure_response(
                        [
                            'success' => true,
                            'message' => __('Successfully deleted logs', 'archive-master'),
                        ]
                    );

                }
            } catch ( \Exception $e ) {
                return new \WP_Error(
                    'rest_error',
                    $e->getMessage(),
                    [ 'status' => 500 ]
                );
            }
        }

        /**
         * Calculate the archive order
         *
         * @param WP_REST_Request $request
         * @return WP_REST_Response|WP_Error
         */
        public function calculate_site_order( $request ) {
            try {
                // Define the table names (adjust if necessary for your setup).
                $orders_table = $this->wpdb->prefix . 'wc_orders';

                // Get the current date and time.
                $current_date     = current_time('mysql');
                $order_count      = 0;
                $older_then_month = '';

                // Define the durations.
                $durations = [
                    '6_months' => gmdate('Y-m-d H:i:s', strtotime('-6 months', strtotime($current_date))),
                    '3_months' => gmdate('Y-m-d H:i:s', strtotime('-3 months', strtotime($current_date))),
                    '1_month'  => gmdate('Y-m-d H:i:s', strtotime('-1 month', strtotime($current_date))),
                    '15_days'  => gmdate('Y-m-d H:i:s', strtotime('-15 days', strtotime($current_date))),
                ];

                // Check if advanced order filtering is enabled (Pro feature)
                $apply_advanced_filter = false;
                if ( function_exists('master_archive_pro') && class_exists('\ARCHM_PRO\Features\AdvancedOrderFilterEvaluator') ) {
                    $filter_evaluator      = \ARCHM_PRO\Features\AdvancedOrderFilterEvaluator::get_instance();
                    $apply_advanced_filter = $filter_evaluator->is_filtering_enabled();
                }

                // Fetch counts for each duration.
                foreach ( $durations as $key => $date ) {
                    if ( $apply_advanced_filter ) {
                        // Get order IDs and filter them through advanced filter
                        $order_ids = $this->get_order_ids_for_count($date);
                        if ( ! empty($order_ids) ) {
                            $filter_evaluator = \ARCHM_PRO\Features\AdvancedOrderFilterEvaluator::get_instance();
                            $order_count      = $filter_evaluator->count_filtered_orders($order_ids);
                        } else {
                            $order_count = 0;
                        }
                    } else {
                                                                                            // Use standard count query
                        $order_count = $this->wpdb->get_var($this->get_count_query($date)); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
                    }

                    if ( (int) $order_count ) {
                        $older_then_month = $key;
                        break;
                    }
                }

                $total_order = $this->wpdb->get_var($this->get_total_count_query()); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

                return rest_ensure_response(
                    [
                        'success' => true,
                        'message' => '',
                        'data'    => [
                            'ordersToarchive' => $order_count,
                            'olderMonths'     => $older_then_month,
                            'totalOrder'      => $total_order,
                        ],
                    ]
                );

            } catch ( \Exception $e ) {
                return new \WP_Error(
                    'rest_error',
                    $e->getMessage(),
                    [ 'status' => 500 ]
                );
            }
        }

        /**
         * Get site order on changng date time
         *
         * @param WP_REST_Request $request
         * @return WP_REST_Response|WP_Error
         */
        public function site_order_byschecdule( $request ) {
            try {
                $params = $this->io()->sanitize($request->get_params());

                // Logger::logMessage($params);

                if ( empty($params['archive_range']) ) {
                    throw new \Exception(__('Missing required parameter: older_then_date', 'archive-master'));
                }

                // $current_date     = current_time('mysql');
                $older_then_schedule = '-' . str_replace('_', ' ', $params['archive_range']);
                // $date             = gmdate('Y-m-d H:i:s', strtotime($older_then_month, strtotime($current_date)));
                $date = $params['archive_range_datetime'];

                if ( false === $date ) {
                    throw new \Exception(__('Invalid date format', 'archive-master'));
                }

                // Check if advanced order filtering is enabled (Pro feature)
                $apply_advanced_filter = false;
                if ( function_exists('master_archive_pro') && class_exists('\ARCHM_PRO\Features\AdvancedOrderFilterEvaluator') ) {
                    $filter_evaluator      = \ARCHM_PRO\Features\AdvancedOrderFilterEvaluator::get_instance();
                    $apply_advanced_filter = $filter_evaluator->is_filtering_enabled();
                }

                if ( $apply_advanced_filter ) {
                    // Get order IDs and filter them through advanced filter
                    $order_ids = $this->get_order_ids_for_count($date);
                    if ( ! empty($order_ids) ) {
                        $filter_evaluator = \ARCHM_PRO\Features\AdvancedOrderFilterEvaluator::get_instance();
                        $order_count      = $filter_evaluator->count_filtered_orders($order_ids);
                    } else {
                        $order_count = 0;
                    }
                } else {
                                                                                        // Use standard count query
                    $order_count = $this->wpdb->get_var($this->get_count_query($date)); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
                }

                if ( $this->wpdb->last_error ) {
                    throw new \Exception($this->wpdb->last_error);
                }

                $total_order = $this->wpdb->get_var($this->get_total_count_query()); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
                if ( $this->wpdb->last_error ) {
                    throw new \Exception($this->wpdb->last_error);
                }

                return new \WP_REST_Response(
                    [
                        'success' => true,
                        'data'    => [
                            'ordersToarchive'   => $order_count,
                            'olderThenSchedule' => str_replace('-', ' ', $older_then_schedule),
                            'date'              => gmdate('j M, Y', strtotime($date)),
                            'totalOrder'        => $total_order,
                            'lastArchivedOrder' => get_option('archm_last_archive_order', ''),
                        ],
                    ],
                    200
                );
            } catch ( \Exception $e ) {
                // Logger::logMessage('Site order schedule error: ' . $e->getMessage());
                return new \WP_REST_Response(
                    [
                        'success' => false,
                        'message' => __('Failed to get site orders by schedule', 'archive-master'),
                        'error'   => $e->getMessage(),
                    ],
                    500
                );
            }
        }

        /**
         * Remove bacnground archiving on cancel archiving
         *
         * @param WP_REST_Request $request
         * @return WP_REST_Response|WP_Error
         */
        public function cancel_archiving() {
            try {
                // Logger::logMessage('background process checking runing or not for RDS');
                $bg_process = new BackgroundProcess();
                $bg_process->stop_archive_process();
                $archived_orders      = count(get_option('archm_queue_batch_ids', []));
                $total_archived_order = get_option('archm_archive_orders_count', 0);
                delete_option('archm_queue_ids');
                delete_option('archm_queue_batch_ids');
                delete_option('archm_archive_orders_count');
                $this->option()->update('background_process_running', false);
                $this->option()->update('background_process_stop', true);

                return new \WP_REST_Response(
                    [
                        'success' => true,
                        'data'    => [
                            'archivedOrders'     => $archived_orders,
                            'totalArchivedOrder' => $total_archived_order,
                        ],
                    ],
                    200
                );
            } catch ( \Exception $e ) {
                // Logger::logMessage('Cancel archiving error: ' . $e->getMessage());
                return new \WP_REST_Response(
                    [
                        'success' => false,
                        'message' => __('Failed to cancel archiving', 'archive-master'),
                        'error'   => $e->getMessage(),
                    ],
                    500
                );
            }
        }

        /**
         * Check background archiving
         *
         * @param WP_REST_Request $request
         * @return WP_REST_Response|WP_Error
         */
        public function check_background_archiving() {
            try {
                $bg_archive_status = get_option('archm_background_process_running');

                if ( $bg_archive_status ) {
                    return new \WP_REST_Response(
                        [
                            'success' => true,
                            'data'    => [
                                'message' => __('Background process is running', 'archive-master'),
                            ],
                        ],
                        200
                    );
                }

                return new \WP_REST_Response(
                    [
                        'success' => false,
                        'data'    => [
                            'message' => __('Background process finished', 'archive-master'),
                        ],
                    ],
                    200
                );

            } catch ( \Exception $e ) {
                // Logger::logMessage('Check background archiving error: ' . $e->getMessage());
                return new \WP_REST_Response(
                    [
                        'success' => false,
                        'message' => __('Failed to check background archiving status', 'archive-master'),
                        'error'   => $e->getMessage(),
                    ],
                    500
                );
            }
        }

        /**
         * Search archived orders
         *
         * @param WP_REST_Request $request
         * @return WP_REST_Response|WP_Error
         */
        public function search_orders( $request ) {
            try {
                $search_params     = $request->get_params();
                $table_name        = $this->wpdb->prefix . 'archived_orders';
                $selected_database = get_option('archm_selected_database');

                // Logger::logMessage('Search orders params: ' . json_encode($search_params));
                // // Logger::logError($search_params);

                if ( 'sqlite' === $selected_database || 'google_drive' === $selected_database ) {
                    $sqlite = Sqlite::get_instance();

                    // Build SQLite query conditions
                    $where_clauses = [];
                    $query_params  = [];

                    // Handle search by order ID, customer email, customer name, and custom meta values
                    if ( ! empty($search_params['search']) ) {
                        $search_term     = $search_params['search'];
                        $where_clauses[] = "(
                            json_extract(order_data, '$.id') LIKE ? OR
                            lower(json_extract(order_data, '$.billing.email')) LIKE ? OR
                            lower(json_extract(order_data, '$.billing.first_name') || ' ' || json_extract(order_data, '$.billing.last_name')) LIKE ? OR
                            lower(json_extract(order_data, '$.custom_meta')) LIKE ?
                        )";
                        $search_like    = '%' . strtolower($search_term) . '%';
                        $query_params[] = $search_like;
                        $query_params[] = $search_like;
                        $query_params[] = $search_like;
                        $query_params[] = $search_like;
                    }

                    // Handle multiple status filters
                    if ( ! empty($search_params['filter_status']) && is_array($search_params['filter_status']) ) {
                        $status_conditions = [];
                        foreach ( $search_params['filter_status'] as $status ) {
                            // First parse the JSON string and then extract status
                            $status_conditions[] = "json_extract(json(order_data), '$.status') = ?";
                            $query_params[]      = $status;
                        }
                        if ( ! empty($status_conditions) ) {
                            $where_clauses[] = '(' . implode(' OR ', $status_conditions) . ')';
                        }
                    }

                    // Handle date range filter
                    if ( ! empty($search_params['filter_date_from']) ) {
                        $where_clauses[] = "json_extract(order_data, '$.date_created.date') >= ?";
                        $query_params[]  = $search_params['filter_date_from'] . ' 00:00:00';
                    }

                    if ( ! empty($search_params['filter_date_to']) ) {
                        $where_clauses[] = "json_extract(order_data, '$.date_created.date') <= ?";
                        $query_params[]  = $search_params['filter_date_to'] . ' 23:59:59';
                    }

                    $where_sql = ! empty($where_clauses) ? 'WHERE ' . implode(' AND ', $where_clauses) : '';

                    // Get orders with pagination
                    $query = "SELECT * FROM {$table_name}
                             {$where_sql}
                             ORDER BY id DESC
                             LIMIT ? OFFSET ?";

                    $stmt = $sqlite->sqdb->prepare($query);
                    // error_log($query);

                    // Bind all parameters
                    foreach ( $query_params as $i => $param ) {
                        $stmt->bindValue($i + 1, $param);
                    }
                    // Bind pagination parameters
                    // phpcs:disable WordPress.DB.RestrictedClasses.mysql__PDO
                    $stmt->bindValue(count($query_params) + 1, (int) $search_params['ordersPerPage'], \PDO::PARAM_INT);
                    // phpcs:enable WordPress.DB.RestrictedClasses.mysql__PDO
                    // phpcs:disable WordPress.DB.RestrictedClasses.mysql__PDO
                    $stmt->bindValue(count($query_params) + 2, (int) $search_params['startIndex'], \PDO::PARAM_INT);
                    // phpcs:enable WordPress.DB.RestrictedClasses.mysql__PDO

                    $stmt->execute();
                    // phpcs:disable WordPress.DB.RestrictedClasses.mysql__PDO
                    $orders = $stmt->fetchAll(\PDO::FETCH_ASSOC);
                    // phpcs:enable WordPress.DB.RestrictedClasses.mysql__PDO

                    if ( $orders ) {
                        // Count total results
                        $count_query = "SELECT COUNT(*) as count FROM {$table_name} {$where_sql}";
                        $count_stmt  = $sqlite->sqdb->prepare($count_query);

                        // Bind parameters for count query (excluding pagination)
                        foreach ( $query_params as $i => $param ) {
                            $count_stmt->bindValue($i + 1, $param);
                        }

                        $count_stmt->execute();
                        // phpcs:disable WordPress.DB.RestrictedClasses.mysql__PDO
                        $result = $count_stmt->fetch(\PDO::FETCH_ASSOC);
                        // phpcs:enable WordPress.DB.RestrictedClasses.mysql__PDO
                        $total_order = $result['count'];
                    }
                    // phpcs:enable WordPress.DB.RestrictedClasses.mysql__PDO
                }

                if ( 'rds' === $selected_database || 'custom_mysql' === $selected_database ) {
                    if ( ! $this->is_bd_connected() ) {
                        throw new \Exception(__('Database not connected', 'archive-master'));
                    }

                    // Set remote db instance
                    $this->set_db_instance();

                    // Build search query
                    $where_clauses = [];
                    $query_params  = [];

                    // Handle search by order ID, customer email, customer name, and custom meta values
                    if ( ! empty($search_params['search']) ) {
                        $search_term     = $search_params['search'];
                        $where_clauses[] = "(
                            JSON_EXTRACT(order_data, '$.id') LIKE %s OR
                            LOWER(JSON_UNQUOTE(JSON_EXTRACT(order_data, '$.billing.email'))) LIKE %s OR
                            LOWER(CONCAT(
                                JSON_UNQUOTE(JSON_EXTRACT(order_data, '$.billing.first_name')),
                                ' ',
                                JSON_UNQUOTE(JSON_EXTRACT(order_data, '$.billing.last_name'))
                            )) LIKE %s OR
                            LOWER(CAST(JSON_EXTRACT(order_data, '$.custom_meta') AS CHAR)) LIKE %s
                        )";
                        $search_like    = '%' . strtolower($search_term) . '%';
                        $query_params[] = $search_like;
                        $query_params[] = $search_like;
                        $query_params[] = $search_like;
                        $query_params[] = $search_like;
                    }

                    // Handle multiple status filters
                    if ( ! empty($search_params['filter_status']) && is_array($search_params['filter_status']) ) {
                        $status_conditions = [];
                        foreach ( $search_params['filter_status'] as $status ) {
                            // Parse the JSON string first, then extract status
                            $status_conditions[] = "JSON_UNQUOTE(JSON_EXTRACT(JSON_EXTRACT(order_data, '$'), '$.status')) = %s";
                            $query_params[]      = $status;
                        }
                        if ( ! empty($status_conditions) ) {
                            $where_clauses[] = '(' . implode(' OR ', $status_conditions) . ')';
                        }
                    }

                    // Handle date range filter
                    if ( ! empty($search_params['filter_date_from']) ) {
                        $where_clauses[] = "JSON_EXTRACT(order_data, '$.date_created') >= %s";
                        $query_params[]  = $search_params['filter_date_from'] . ' 00:00:00';
                    }

                    if ( ! empty($search_params['filter_date_to']) ) {
                        $where_clauses[] = "JSON_EXTRACT(order_data, '$.date_created') <= %s";
                        $query_params[]  = $search_params['filter_date_to'] . ' 23:59:59';
                    }

                    $where_sql = ! empty($where_clauses) ? 'WHERE ' . implode(' AND ', $where_clauses) : '';

                    $order_query = $this->remote_db->prepare(
                        "SELECT * FROM {$table_name}
                        {$where_sql}
                        ORDER BY ID DESC
                        LIMIT %d OFFSET %d",
                        array_merge($query_params, [ $search_params['ordersPerPage'], $search_params['startIndex'] ])
                    );

                    // Logger::logMessage($order_query);
                    $orders = $this->remote_db->get_results($order_query, ARRAY_A);

                    if ( $orders ) {
                        $count_query = $this->remote_db->prepare(
                            "SELECT COUNT(*) FROM {$table_name} {$where_sql}",
                            $query_params
                        );
                        $total_order = $this->remote_db->get_var($count_query);
                    }
                }

                if ( $orders ) {
                    foreach ( $orders as $key => $order ) {
                        $orders[ $key ]['is_selected'] = false;
                    }
                    return new \WP_REST_Response(
                        [
                            'success' => true,
                            'message' => __('Search results', 'archive-master'),
                            'data'    => [
                                'orders'      => $orders,
                                'total_order' => $total_order,
                            ],
                        ],
                        200
                    );
                }

                return new \WP_REST_Response(
                    [
                        'success' => false,
                        'message' => __('No orders found', 'archive-master'),
                        'data'    => [],
                    ],
                    404
                );
            } catch ( \Exception $e ) {
                // Logger::logMessage('Search orders error: ' . $e->getMessage());
                return new \WP_REST_Response(
                    [
                        'success' => false,
                        'message' => __('Failed to search orders', 'archive-master'),
                        'error'   => $e->getMessage(),
                    ],
                    500
                );
            }
        }

        /**
         * Search archived orders
         *
         * @param WP_REST_Request $request
         * @return mixed
         */
        public function get_order_info() {
            try {
                $table_name           = $this->wpdb->prefix . 'archived_orders';
                $selected_database    = get_option('archm_selected_database');
                $total_archived_order = 0;

                if ( 'sqlite' === $selected_database || 'google_drive' === $selected_database ) {
                    try {
                        $sqlite = Sqlite::get_instance();

                        // Check if SQLite is properly connected
                        if ( $sqlite->is_connected() ) {
                            $order_count_query = "SELECT COUNT(*) AS total_orders FROM {$table_name} WHERE 1";
                            $stmt              = $sqlite->sqdb->prepare($order_count_query);

                            if ( ! $stmt ) {
                                throw new \Exception('Failed to prepare SQLite query');
                            }

                            $stmt->execute();
                            $result = $stmt->fetch(\PDO::FETCH_ASSOC); // phpcs:ignore WordPress.DB.RestrictedClasses.mysql__PDO

                            if ( false === $result ) {
                                throw new \Exception('Failed to fetch SQLite results');
                            }

                            $total_archived_order = $result['total_orders'];
                        }
                    } catch ( \PDOException $e ) {
                        throw new \Exception('SQLite Database Error: ' . $e->getMessage());
                    }
                }

                if ( 'rds' === $selected_database || 'custom_mysql' === $selected_database ) {
                    if ( ! $this->is_bd_connected() ) {
                        throw new \Exception(__('Remote database connection failed', 'archive-master'));
                    }

                    // Set remote db instance
                    $this->set_db_instance();

                    // Check if remote_db was initialized
                    if ( null === $this->remote_db ) {
                        throw new \Exception(__('Remote database connection not available. Please check your database credentials.', 'archive-master'));
                    }

                    if ( $this->remote_db->last_error ) {
                        $db_type = ( 'custom_mysql' === $selected_database ) ? 'Custom MySQL' : 'RDS';
                        throw new \Exception($db_type . ' Database Error: ' . $this->remote_db->last_error);
                    }

                    $total_archived_order = $this->remote_db->get_var(
                        $this->remote_db->prepare('SELECT COUNT(*) AS total_orders FROM %i WHERE 1', $table_name)
                    );

                    if ( $this->remote_db->last_error ) {
                        throw new \Exception('RDS Database Error: ' . $this->remote_db->last_error);
                    }
                }

                                                                                     // Get total orders count
                $total_order = $this->wpdb->get_var($this->get_total_count_query()); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

                if ( $this->wpdb->last_error ) {
                    throw new \Exception('WordPress Database Error: ' . $this->wpdb->last_error);
                }

                if ( ! isset($total_archived_order) ) {
                    return new \WP_REST_Response(
                        [
                            'success' => false,
                            'message' => __('No archived orders found', 'archive-master'),
                        ],
                        404
                    );
                }

                return new \WP_REST_Response(
                    [
                        'success' => true,
                        'message' => __('Archived Order Information', 'archive-master'),
                        'data'    => [
                            'archived_order'    => (int) $total_archived_order,
                            'total_order'       => (int) $total_order,
                            'selected_database' => $selected_database,
                        ],
                    ],
                    200
                );

            } catch ( \Exception $e ) {
                // Logger::logMessage('Get order info error: ' . $e->getMessage());
                return new \WP_REST_Response(
                    [
                        'success' => false,
                        'message' => __('Failed to get order information', 'archive-master'),
                        'error'   => $e->getMessage(),
                    ],
                    500
                );
            }
        }

        /**
         * Search archived orders
         *
         * @param WP_REST_Request $request
         * @return mixed
         */
        public function check_ubplivate_order( $request ) {
            try {
                $params = $request->get_params();

                $dublicate_orders = $this->wpdb->get_results($this->count_total_bulicate_order($params['orderIds'])); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

                if ( ! empty($dublicate_orders) ) {
                    return new \WP_REST_Response(
                        [
                            'success' => true,
                            'data'    => [
                                'hasDuplicates'  => true,
                                'duplcateOrders' => $dublicate_orders,
                            ],
                        ],
                        200
                    );
                } else {
                    return new \WP_REST_Response(
                        [
                            'success' => true,
                            'data'    => [
                                'hasDuplicates'  => false,
                                'duplcateOrders' => $dublicate_orders,
                            ],
                        ],
                        200
                    );
                }
            } catch ( \Exception $e ) {
                return new \WP_REST_Response(
                    [
                        'success' => false,
                        'message' => __('Duplicate check failed', 'archive-master'),
                        'error'   => $e->getMessage(),
                    ],
                    500
                );
            }
        }

        /**
         * Get RDS connection argument
         *
         * @return mixed
         */
        // phpcs:ignore PHPCompatibility.FunctionDeclarations.NewReturnTypeDeclarations.arrayFound
        private function get_rds_connection_args(): array {
            return [
                'rds_dbname'   => [
                    'required'          => true,
                    'type'              => 'string',
                    'sanitize_callback' => 'sanitize_text_field',
                    'validate_callback' => [ $this, 'validate_database_name' ],
                ],
                'rds_endpoint' => [
                    'required'          => true,
                    'type'              => 'string',
                    'sanitize_callback' => 'sanitize_text_field',
                    'validate_callback' => [ $this, 'validate_endpoint' ],
                ],
                'rds_username' => [
                    'required'          => true,
                    'type'              => 'string',
                    'sanitize_callback' => 'sanitize_text_field',
                ],
                'rds_port'     => [
                    'required'          => true,
                    'type'              => 'integer',
                    'sanitize_callback' => 'absint',
                    'validate_callback' => [ $this, 'validate_port' ],
                ],
                'rds_password' => [
                    'required' => true,
                    'type'     => 'string',
                ],
            ];
        }

        /**
         * Validate database name
         *
         * @param [type] $value
         * @return mixed
         */
        // phpcs:ignore PHPCompatibility.FunctionDeclarations.NewReturnTypeDeclarations.boolFound
        public function validate_database_name( $value ): bool {
            // Only allow alphanumeric characters, underscores, and hyphens
            return (bool) preg_match('/^[a-zA-Z0-9_-]+$/', $value);
        }

        /**
         * Validate RDS endpint
         *
         * @param [type] $value
         * @return mixed
         */
        // phpcs:ignore PHPCompatibility.FunctionDeclarations.NewReturnTypeDeclarations.boolFound
        public function validate_endpoint( $value ): bool {
            // Basic endpoint validation
            if ( empty($value) ) {
                return false;
            }

            // Allow hostnames or IP addresses
            if ( filter_var($value, FILTER_VALIDATE_IP) ) {
                return true;
            }

            // Validate hostname format
            return (bool) preg_match('/^[a-zA-Z0-9-]+(\.[a-zA-Z0-9-]+)*\.[a-zA-Z]{2,}$/', $value);
        }

        /**
         * Validate RDS port
         *
         * @param [type] $value
         * @return mixed
         */
        // phpcs:ignore PHPCompatibility.FunctionDeclarations.NewReturnTypeDeclarations.boolFound
        public function validate_port( $value ): bool {
            return $value > 0 && $value <= 65535;
        }

        /**
         * Permanently delete archived orders
         *
         * @param WP_REST_Request $request
         * @return WP_REST_Response|WP_Error
         */
        public function permanently_delete_orders( $request ) {
            try {
                $params = $this->io()->sanitize($request->get_json_params());
                $orders = $params['orders'];

                if ( empty($orders) ) {
                    return new \WP_Error(
                        'missing_orders',
                        __('No orders provided for deletion', 'archive-master'),
                        [ 'status' => 400 ]
                    );
                }

                $table_name        = $this->wpdb->prefix . 'archived_orders';
                $selected_database = get_option('archm_selected_database');
                $orders_deleted    = false;

                // Create array of order IDs (archived order IDs for deletion)
                $order_ids = array_map(
                    function ( $order ) {
                        return $order['id'];
                    },
                    $orders
                );

                // Extract WooCommerce order IDs from order_data for analytics cache deletion
                $woocommerce_order_ids = [];
                foreach ( $orders as $order ) {
                    if ( isset($order['order_data']) ) {
                        $order_data = json_decode($order['order_data'], true);
                        if ( $order_data && isset($order_data['id']) ) {
                            $woocommerce_order_ids[] = intval($order_data['id']);
                        }
                    }
                }

                $ids_in_clause = implode(',', array_map('intval', $order_ids));

                // Always try to delete from SQLite (common storage) regardless of selected storage
                $sqlite = Sqlite::get_instance();

                // Check if SQLite is connected before attempting deletion
                if ( $sqlite->is_connected() ) {
                    try {
                        $query = "DELETE FROM {$table_name} WHERE id IN ({$ids_in_clause})";

                        // Execute the query with proper error handling
                        $result = $sqlite->sqdb->exec($query);
                        if ( false !== $result ) {
                            // error_log('AdminRest: Permanently deleted ' . $result . ' orders from SQLite');
                            $orders_deleted = true;

                            // Trigger hook for Google Drive sync
                            do_action('archm_sqlite_data_deleted', $order_ids);
                        }
                    } catch ( \PDOException $e ) {
                        Logger::logError('AdminRest: SQLite permanent deletion failed with PDO error: ' . $e->getMessage());
                    }
                }

                // Also delete from selected storage if it's a remote database
                if ( 'rds' === $selected_database || 'custom_mysql' === $selected_database ) {
                    if ( ! $this->is_bd_connected() ) {
                        // error_log('AdminRest: ' . $selected_database . ' database not connected for permanent deletion');
                        return new \WP_Error(
                            'db_connection_error',
                            __('Database not connected', 'archive-master'),
                            [ 'status' => 500 ]
                        );
                    }

                    $remote_deleted = false;

                    try {
                        // Set remote db instance
                        $this->set_db_instance();

                        $delete_query = "DELETE FROM %i WHERE id IN ({$ids_in_clause})";
                        $query        = $this->remote_db->prepare($delete_query, $table_name);
                        $result       = $this->remote_db->query($query);

                        if ( false !== $result ) {
                            $remote_deleted = true;
                            $affected_rows  = $this->remote_db->rows_affected;
                            // error_log('AdminRest: Permanently deleted ' . $affected_rows . ' orders from ' . $selected_database);
                        }
                    } catch ( \Exception $e ) {
                        Logger::logError('AdminRest: ' . $selected_database . ' permanent deletion failed with error: ' . $e->getMessage());
                    }

                    // For remote databases, we need both SQLite and remote deletion to succeed
                    // However, we return success if at least SQLite deletion worked (primary storage)
                    if ( $remote_deleted ) {
                        $orders_deleted = true;
                    } elseif ( $orders_deleted ) {
                        // SQLite deletion worked but remote failed - log warning but don't fail completely
                        Logger::logError('AdminRest: WARNING - SQLite permanent deletion succeeded but ' . $selected_database . ' deletion failed');
                    }
                }

                if ( function_exists('master_archive_pro') ) {
                    // Ensure Google Drive sync classes are loaded
                    if ( ! class_exists('\ArchiveMaster\GoogleDrive\Sync') ) {
                        // Load required classes from Pro plugin
                        require_once WP_PLUGIN_DIR . '/archive-master-pro/includes/class-cloud.php';
                        require_once WP_PLUGIN_DIR . '/archive-master-pro/includes/google-drive/class-google-drive-sync.php';
                    }

                    $google_sync = new \ArchiveMaster\GoogleDrive\Sync();
                    $google_sync->sync_sqlite_database();

                    // Delete analytics cache entries for permanently deleted orders
                    // Use WooCommerce order IDs (not archived order IDs) for cache deletion
                    if ( class_exists('\ARCHM_PRO\Features\AnalyticsCacheTable') && ! empty($woocommerce_order_ids) ) {
                        $cache_table = \ARCHM_PRO\Features\AnalyticsCacheTable::get_instance();
                        if ( $cache_table && $cache_table->table_exists() ) {
                            $cache_deleted = $cache_table->delete_by_order_ids($woocommerce_order_ids);
                            // Cache deletion attempted - result logged if needed
                            // error_log('AdminRest: Cache deletion result: ' . ( $cache_deleted ? 'success' : 'failed' ));
                        }
                    }
                }

                if ( $orders_deleted ) {
                    return rest_ensure_response(
                        [
                            'success' => true,
                            'message' => __('Orders permanently deleted successfully', 'archive-master'),
                            'data'    => [
                                'deleted_count' => count($orders),
                            ],
                        ]
                    );
                }

                return new \WP_Error(
                    'deletion_failed',
                    __('Failed to delete orders', 'archive-master'),
                    [ 'status' => 500 ]
                );

            } catch ( \Exception $e ) {
                // Logger::logMessage('Permanent delete orders error: ' . $e->getMessage());
                return new \WP_Error(
                    'rest_error',
                    $e->getMessage(),
                    [ 'status' => 500 ]
                );
            }
        }

        /**
         * Check if local archive data file is missing
         *
         * @param WP_REST_Request $request
         * @return WP_REST_Response|WP_Error
         */
        public function check_missing_archive_data( $request ) {
            try {
                $selected_database = get_option('archm_selected_database', 'sqlite');

                // Only check for SQLite database files
                if ( 'sqlite' !== $selected_database && 'google_drive' !== $selected_database ) {
                    return rest_ensure_response(
                        [
                            'success' => true,
                            'data'    => [
                                'is_missing' => false,
                                'message'    => __('Not using SQLite database', 'archive-master'),
                            ],
                        ]
                    );
                }

                // Check if any archiving has been done before
                $last_archive_order = get_option('archm_last_archive_order');
                if ( empty($last_archive_order) ) {
                    return rest_ensure_response(
                        [
                            'success' => true,
                            'data'    => [
                                'is_missing' => false,
                                'message'    => __('No archiving has been performed yet', 'archive-master'),
                            ],
                        ]
                    );
                }

                $db_directory = WP_CONTENT_DIR . '/archive-master-db';
                $hash         = get_option('archm_sqlite_db_file_hash');

                if ( ! $hash ) {
                    return rest_ensure_response(
                        [
                            'success' => true,
                            'data'    => [
                                'is_missing' => false,
                                'message'    => __('No archive database hash found', 'archive-master'),
                            ],
                        ]
                    );
                }

                $file_path  = $db_directory . '/' . $hash . '.db';
                $is_missing = ! file_exists($file_path);

                // Check if Google Drive is connected (Pro feature)
                $is_google_drive_connected = false;
                if ( function_exists('master_archive_pro') ) {
                    $access_token              = get_option('archive_master_google_access_token');
                    $refresh_token             = get_option('archive_master_google_refresh_token');
                    $is_google_drive_connected = ! empty($access_token) && ! empty($refresh_token);
                }

                return rest_ensure_response(
                    [
                        'success' => true,
                        'data'    => [
                            'is_missing'                => $is_missing,
                            'is_google_drive_connected' => $is_google_drive_connected,
                            'file_path'                 => $file_path,
                            'hash'                      => $hash,
                            'message'                   => $is_missing ? __('Archive database file is missing', 'archive-master') : __('Archive database file exists', 'archive-master'),
                        ],
                    ]
                );

            } catch ( \Exception $e ) {
                return new \WP_Error(
                    'rest_error',
                    $e->getMessage(),
                    [ 'status' => 500 ]
                );
            }
        }

        /**
         * Recover archive data from Google Drive
         *
         * @param WP_REST_Request $request
         * @return WP_REST_Response|WP_Error
         */
        public function recover_archive_data( $request ) {
            try {
                // Check if Pro version is available
                if ( ! function_exists('master_archive_pro') ) {
                    return new \WP_Error(
                        'pro_required',
                        __('Archive Master Pro is required for Google Drive recovery', 'archive-master'),
                        [ 'status' => 400 ]
                    );
                }

                $selected_database = get_option('archm_selected_database', 'sqlite');

                if ( 'sqlite' !== $selected_database && 'google_drive' !== $selected_database ) {
                    return new \WP_Error(
                        'invalid_database',
                        __('Google Drive recovery is only available for SQLite database', 'archive-master'),
                        [ 'status' => 400 ]
                    );
                }

                $hash = get_option('archm_sqlite_db_file_hash');

                if ( ! $hash ) {
                    return new \WP_Error(
                        'no_hash',
                        __('No archive database hash found', 'archive-master'),
                        [ 'status' => 400 ]
                    );
                }

                // Ensure Google Drive sync classes are loaded
                if ( ! class_exists('\ArchiveMaster\GoogleDrive\Sync') ) {
                    // Load required classes from Pro plugin
                    require_once WP_PLUGIN_DIR . '/archive-master-pro/includes/class-cloud.php';
                    require_once WP_PLUGIN_DIR . '/archive-master-pro/includes/google-drive/class-google-drive-sync.php';
                }

                $google_sync = new \ArchiveMaster\GoogleDrive\Sync();

                // Check if we have valid access token
                $access_token = $google_sync->get_access_token();
                if ( ! $access_token ) {
                    return new \WP_Error(
                        'no_access_token',
                        __('Google Drive is not connected or access token expired', 'archive-master'),
                        [ 'status' => 400 ]
                    );
                }

                // Get the folder ID where backups are stored
                $folder_id = $this->get_google_drive_folder_id($access_token);
                if ( ! $folder_id ) {
                    return new \WP_Error(
                        'folder_not_found',
                        __('Archive Master Backup folder not found in Google Drive', 'archive-master'),
                        [ 'status' => 404 ]
                    );
                }

                // Look for the backup file in Google Drive
                $backup_file_id = $this->find_backup_file_in_drive($access_token, $folder_id, $hash);
                if ( ! $backup_file_id ) {
                    return new \WP_Error(
                        'backup_not_found',
                        __('No matching backup file found in Google Drive', 'archive-master'),
                        [ 'status' => 404 ]
                    );
                }

                // Download and extract the backup file
                $recovery_result = $this->download_and_extract_backup($access_token, $backup_file_id, $hash);

                if ( $recovery_result['success'] ) {
                    return rest_ensure_response(
                        [
                            'success' => true,
                            'message' => __('Archive data recovered successfully from Google Drive', 'archive-master'),
                            'data'    => $recovery_result,
                        ]
                    );
                } else {
                    return new \WP_Error(
                        'recovery_failed',
                        $recovery_result['message'],
                        [ 'status' => 500 ]
                    );
                }
            } catch ( \Exception $e ) {
                return new \WP_Error(
                    'rest_error',
                    $e->getMessage(),
                    [ 'status' => 500 ]
                );
            }
        }

        /**
         * Get Google Drive folder ID for Archive Master Backup
         *
         * @param string $access_token
         * @return string|false
         */
        private function get_google_drive_folder_id( $access_token ) {
            $folder_name = 'Archive Master Backup';
            $url         = 'https://www.googleapis.com/drive/v3/files?q=name="' . urlencode($folder_name) . '" and mimeType="application/vnd.google-apps.folder" and trashed=false';

            $response = wp_remote_get($url, [
                'headers' => [
                    'Authorization' => 'Bearer ' . $access_token,
                ],
            ]);

            if ( is_wp_error($response) ) {
                return false;
            }

            $response_body = json_decode(wp_remote_retrieve_body($response), true);

            if ( isset($response_body['files']) && ! empty($response_body['files']) ) {
                return $response_body['files'][0]['id'];
            }

            return false;
        }

        /**
         * Find backup file in Google Drive that matches the hash
         *
         * @param string $access_token
         * @param string $folder_id
         * @param string $hash
         * @return string|false
         */
        /**
         * Filter custom meta keys, excluding WooCommerce defaults
         *
         * @param array $meta_keys Array of meta keys to filter
         * @param array $wc_default_meta_keys Array of WooCommerce default meta keys to exclude
         * @return array Filtered array of custom meta keys
         */
        private function filter_custom_meta_keys( $meta_keys, $wc_default_meta_keys ) {
            $filtered = [];
            foreach ( $meta_keys as $key ) {
                if ( $this->is_custom_meta_key($key, $wc_default_meta_keys) ) {
                    $filtered[] = $key;
                }
            }
            return $filtered;
        }

        /**
         * Check if a meta key is a custom key (not a WooCommerce default)
         *
         * @param string $key Meta key to check
         * @param array  $wc_default_meta_keys Array of WooCommerce default meta keys
         * @return bool True if custom key, false if WooCommerce default
         */
        private function is_custom_meta_key( $key, $wc_default_meta_keys ) {
            // Exclude keys that start with underscore (hidden/internal)
            if ( strpos($key, '_') === 0 ) {
                return false;
            }
            // Exclude known WooCommerce defaults
            if ( in_array($key, $wc_default_meta_keys, true) ) {
                return false;
            }
            // Exclude keys that match WooCommerce patterns
            if ( preg_match('/^_wc_|^_woocommerce_|^_order_|^_billing_|^_shipping_|^_payment_/', $key) ) {
                return false;
            }
            return true;
        }

        private function find_backup_file_in_drive( $access_token, $folder_id, $hash ) {
            $hash      = get_option('archm_sqlite_db_file_hash');
            $file_name = $hash ? $hash . '.zip' : 'archive-master-sqlite.zip';
            $url       = 'https://www.googleapis.com/drive/v3/files?q=name="' . urlencode($file_name) . '" and "' . $folder_id . '" in parents and trashed=false';

            $response = wp_remote_get($url, [
                'headers' => [
                    'Authorization' => 'Bearer ' . $access_token,
                ],
            ]);

            if ( is_wp_error($response) ) {
                return false;
            }

            $response_body = json_decode(wp_remote_retrieve_body($response), true);

            if ( isset($response_body['files']) && ! empty($response_body['files']) ) {
                // For now, return the first file found
                // In a more sophisticated implementation, we could check file metadata
                // or download and verify the hash matches
                return $response_body['files'][0]['id'];
            }

            return false;
        }

        /**
         * Download and extract backup from Google Drive
         *
         * @param string $access_token
         * @param string $file_id
         * @param string $hash
         * @return array
         */
        private function download_and_extract_backup( $access_token, $file_id, $hash ) {
            $db_directory = WP_CONTENT_DIR . '/archive-master-db';

            // Ensure directory exists
            if ( ! is_dir($db_directory) ) {
                wp_mkdir_p($db_directory);
            }

            // Download the zip file from Google Drive
            $download_url = 'https://www.googleapis.com/drive/v3/files/' . $file_id . '?alt=media';

            $response = wp_remote_get($download_url, [
                'headers' => [
                    'Authorization' => 'Bearer ' . $access_token,
                ],
                'timeout' => 300, // 5 minutes for large files
            ]);

            if ( is_wp_error($response) ) {
                return [
                    'success' => false,
                    'message' => __('Failed to download backup from Google Drive: ', 'archive-master') . $response->get_error_message(),
                ];
            }

            $response_code = wp_remote_retrieve_response_code($response);
            if ( 200 !== $response_code ) {
                return [
                    'success' => false,
                    'message' => __('Failed to download backup from Google Drive. Response code: ', 'archive-master') . $response_code,
                ];
            }

            // Save the downloaded zip file temporarily
            $temp_zip_path = $db_directory . '/temp-recovery.zip';
            $zip_content   = wp_remote_retrieve_body($response);

            if ( ! file_put_contents($temp_zip_path, $zip_content) ) {
                return [
                    'success' => false,
                    'message' => __('Failed to save downloaded backup file', 'archive-master'),
                ];
            }

            // Extract the zip file
            $zip        = new \ZipArchive();
            $zip_result = $zip->open($temp_zip_path);

            if ( true !== $zip_result ) {
                wp_delete_file($temp_zip_path); // Clean up
                return [
                    'success' => false,
                    'message' => __('Failed to open downloaded backup zip file. Error code: ', 'archive-master') . $zip_result,
                ];
            }

            // Look for the database file with matching hash
            $extracted = false;
            $num_files = $zip->numFiles; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase
            for ( $i = 0; $i < $num_files; $i++ ) {
                $file_info = $zip->statIndex($i);
                $filename  = $file_info['name'];

                // Check if this is our database file
                if ( $filename === $hash . '.db' ) {
                    $target_path = $db_directory . '/' . $hash . '.db';

                    // Extract the specific file
                    if ( $zip->extractTo($db_directory, $filename) ) {
                        $extracted = true;
                        break;
                    }
                }
            }

            $zip->close();
            wp_delete_file($temp_zip_path); // Clean up temp file

            if ( ! $extracted ) {
                return [
                    'success' => false,
                    'message' => __('Database file with matching hash not found in backup', 'archive-master'),
                ];
            }

            // Verify the extracted file exists and is readable
            $final_path = $db_directory . '/' . $hash . '.db';
            if ( ! file_exists($final_path) || ! is_readable($final_path) ) {
                return [
                    'success' => false,
                    'message' => __('Extracted database file is not accessible', 'archive-master'),
                ];
            }

            return [
                'success'   => true,
                'message'   => __('Archive data recovered successfully', 'archive-master'),
                'file_path' => $final_path,
                'hash'      => $hash,
            ];
        }

        /**
         * Get single order archive argument validation
         *
         * @return array
         */
        // phpcs:ignore PHPCompatibility.FunctionDeclarations.NewReturnTypeDeclarations.arrayFound
        private function get_single_order_args(): array {
            return [
                'order_id' => [
                    'required'          => true,
                    'type'              => 'integer',
                    'sanitize_callback' => 'absint',
                    'validate_callback' => function ( $param ) {
                        return is_numeric($param) && $param > 0;
                    },
                ],
            ];
        }

        /**
         * Archive single order
         *
         * @param WP_REST_Request $request
         * @return WP_REST_Response|WP_Error
         */
        public function archive_single_order( $request ) {
            try {
                $order_id = $request->get_param('order_id');

                // Validate that the order exists
                $order = wc_get_order($order_id);
                if ( ! $order ) {
                    return new \WP_Error(
                        'invalid_order',
                        __('Order not found.', 'archive-master'),
                        [ 'status' => 404 ]
                    );
                }

                // Check if order is already archived (shouldn't exist in main DB if archived)
                if ( $order->get_status() === 'trash' ) {
                    return new \WP_Error(
                        'order_trashed',
                        __('Cannot archive a trashed order.', 'archive-master'),
                        [ 'status' => 400 ]
                    );
                }

                $selected_database = get_option('archm_selected_database', 'sqlite');

                // Check database connection and setup
                if ( 'sqlite' === $selected_database || 'google_drive' === $selected_database ) {
                    if ( ! extension_loaded('pdo_sqlite') ) {
                        return new \WP_Error(
                            'disabled_pdo_sqlite',
                            __('SQLite PDO extension is not loaded. Please enable SQLite support.', 'archive-master'),
                            [ 'status' => 400 ]
                        );
                    }

                    // Create SQLite database file before archiving if it doesn't exist
                    $sqlite = \ARCHM\Features\Sqlite::get_instance();
                    if ( ! $sqlite->create_database_for_archiving() ) {
                        return new \WP_Error(
                            'sqlite_creation_failed',
                            __('Failed to create SQLite database file for archiving. Please check file permissions.', 'archive-master'),
                            [ 'status' => 500 ]
                        );
                    }
                } elseif ( 'rds' === $selected_database || 'custom_mysql' === $selected_database ) {
                    if ( ! $this->is_bd_connected() ) {
                        return new \WP_Error(
                            'db_connection_error',
                            __('Remote database is not connected. Please check your database settings.', 'archive-master'),
                            [ 'status' => 500 ]
                        );
                    }
                    $this->set_db_instance();
                }

                // Prepare order products array for analytics aggregation (similar to BackgroundProcess.php)
                $order_products = [];
                foreach ( $order->get_items() as $item_id => $item ) {
                    if ( is_a($item, 'WC_Order_Item_Product') ) {
                        $order_products[] = [
                            'product_id'   => $item->get_product_id(),
                            'variation_id' => $item->get_variation_id(),
                            'quantity'     => $item->get_quantity(),
                            'total'        => $item->get_total(),
                        ];
                    }
                }

                // Archive the single order
                $archive_result = $this->process_single_order_archive($order, $selected_database);

                if ( $archive_result['success'] ) {
                    // Aggregate analytics data before deleting order (Pro feature)
                    // This matches the behavior in BackgroundProcess.php
                    $sync_enabled = get_option('archm_analytics_sync_enabled', false);
                    if ( $sync_enabled && function_exists('master_archive_pro') && class_exists('\ARCHM_PRO\Features\AnalyticsAggregator') ) {
                        try {
                            $aggregator = \ARCHM_PRO\Features\AnalyticsAggregator::get_instance();
                            $aggregator->aggregate_order($order, $order_products);
                        } catch ( \Exception $e ) {
                            // Log error but don't stop archiving process
                            \ARCHM\Helpers\Logger::logMessage('Failed to aggregate analytics for order #' . $order_id . ': ' . $e->getMessage());
                        }
                    }

                    // Delete the order from main database after successful archiving
                    $delete_result = $this->delete_single_order($order_id);

                    if ( $delete_result['success'] ) {
                        // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase
                        $log_DB = new ActivityLogDB();

                        // Log the archiving activity
                        // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase
                        $log_DB->insert_log([
                            // translators: %d is the order ID
                            'activity' => sprintf(__('Order #%d archived and deleted successfully', 'archive-master'), $order_id),
                        ]);

                        // CRITICAL: Clear the archived orders count cache IMMEDIATELY and SYNCHRONOUSLY
                        // This ensures the filter appears instantly when redirecting to orders page
                        delete_transient('archm_total_archived_orders_count');

                        // Trigger action hook for analytics cache clearing (also clears count cache via hook)
                        do_action('archm_order_archived', $order_id);

                        // Update analytics cache version to invalidate WooCommerce cache (similar to BackgroundProcess complete method)
                        if ( $sync_enabled ) {
                            update_option('archived_analytics_version', time());
                        }

                        // Trigger hook for cache clearing when multiple orders were archived
                        do_action('archm_order_archived');

                        return rest_ensure_response([
                            'success' => true,
                            // translators: %d is the order ID
                            'message' => sprintf(__('Order #%d has been successfully archived and removed from the main database.', 'archive-master'), $order_id),
                            'data'    => [
                                'order_id'    => $order_id,
                                'archived_at' => current_time('mysql'),
                                'database'    => $selected_database,
                            ],
                        ]);
                    } else {
                        // If deletion failed, we should probably remove from archive as well for consistency
                        return new \WP_Error(
                            'delete_failed',
                            __('Order was archived but could not be deleted from main database. Please try again.', 'archive-master'),
                            [ 'status' => 500 ]
                        );
                    }
                } else {
                    return new \WP_Error(
                        'archive_failed',
                        $archive_result['message'],
                        [ 'status' => 500 ]
                    );
                }
            } catch ( \Exception $e ) {
                return new \WP_Error(
                    'rest_error',
                    $e->getMessage(),
                    [ 'status' => 500 ]
                );
            }
        }

        /**
         * Process single order archiving
         *
         * @param WC_Order $order
         * @param string $selected_database
         * @return array
         */
        private function process_single_order_archive( $order, $selected_database ) {
            try {
                // Check Subscription exclusion option
                $exclude_subscriptions = get_option('archm_exclude_subscriptions', false);
                if ( $exclude_subscriptions && class_exists('\ARCHM\Helpers\SubscriptionHelper') ) {
                    if ( \ARCHM\Helpers\SubscriptionHelper::is_subscription_order($order) ) {
                        return [
                            'success' => false,
                            'message' => __('Order skipped: active subscription (based on exclusion option).', 'archive-master'),
                        ];
                    }
                }

                // Check Membership exclusion option
                $exclude_memberships = get_option('archm_exclude_memberships', false);
                if ( $exclude_memberships && class_exists('\ARCHM\Helpers\MembershipHelper') ) {
                    if ( \ARCHM\Helpers\MembershipHelper::is_membership_order($order) ) {
                        return [
                            'success' => false,
                            'message' => __('Order skipped: active membership (based on exclusion option).', 'archive-master'),
                        ];
                    }
                }

                // Prepare order data for archiving
                $order_data     = $this->prepare_order_data_for_archive($order);
                $order_products = $this->prepare_order_products_for_archive($order);

                $table_name = $this->wpdb->prefix . 'archived_orders';

                if ( 'sqlite' === $selected_database || 'google_drive' === $selected_database ) {
                    $sqlite = \ARCHM\Features\Sqlite::get_instance();

                    $insert_query = "INSERT INTO {$table_name} (archived_order_id, order_data, ordered_products, created_at) VALUES (?, ?, ?, ?)";
                    $stmt         = $sqlite->sqdb->prepare($insert_query);

                    $result = $stmt->execute([
                        $order->get_id(),
                        json_encode($order_data),
                        json_encode($order_products),
                        current_time('mysql'),
                    ]);

                    if ( ! $result ) {
                        return [
                            'success' => false,
                            'message' => __('Failed to archive order in SQLite database.', 'archive-master'),
                        ];
                    }
                } elseif ( 'rds' === $selected_database || 'custom_mysql' === $selected_database ) {
                    $result = $this->remote_db->insert(
                        $table_name,
                        [
                            'archived_order_id' => $order->get_id(),
                            'order_data'        => json_encode($order_data),
                            'ordered_products'  => json_encode($order_products),
                            'date'              => current_time('mysql'),
                        ]
                    );

                    if ( ! $result ) {
                        return [
                            'success' => false,
                            'message' => __('Failed to archive order in remote database.', 'archive-master'),
                        ];
                    }
                }

                // Trigger Google Drive sync if Pro version is available and Google Drive is selected
                if ( function_exists('master_archive_pro') && 'google_drive' === $selected_database ) {
                    // Ensure Google Drive sync classes are loaded
                    if ( ! class_exists('\ArchiveMaster\GoogleDrive\Sync') ) {
                        require_once WP_PLUGIN_DIR . '/archive-master-pro/includes/class-cloud.php';
                        require_once WP_PLUGIN_DIR . '/archive-master-pro/includes/google-drive/class-google-drive-sync.php';
                    }

                    $google_sync = new \ArchiveMaster\GoogleDrive\Sync();
                    $google_sync->sync_sqlite_database();
                }

                return [
                    'success' => true,
                    'message' => __('Order archived successfully.', 'archive-master'),
                ];

            } catch ( \Exception $e ) {
                return [
                    'success' => false,
                    'message' => __('Archive process failed: ', 'archive-master') . $e->getMessage(),
                ];
            }
        }

        /**
         * Prepare order data for archiving
         *
         * @param WC_Order $order
         * @return array
         */
        private function prepare_order_data_for_archive( $order ) {
            // Extract coupon data for proper archiving
            $coupon_data = [];
            foreach ( $order->get_items('coupon') as $item_id => $coupon_item ) {
                if ( is_a($coupon_item, 'WC_Order_Item_Coupon') ) {
                    $coupon_meta_data = [];
                    foreach ( $coupon_item->get_meta_data() as $meta ) {
                        $coupon_meta_data[] = [
                            'key'   => $meta->key,
                            'value' => $meta->value,
                        ];
                    }
                    $coupon_data[] = [
                        'code'         => $coupon_item->get_code(),
                        'discount'     => $coupon_item->get_discount(),
                        'discount_tax' => $coupon_item->get_discount_tax(),
                        'meta_data'    => $coupon_meta_data,
                    ];
                }
            }

            // Extract tax data for proper archiving
            $tax_data = [];
            foreach ( $order->get_items('tax') as $item_id => $tax_item ) {
                if ( is_a($tax_item, 'WC_Order_Item_Tax') ) {
                    $tax_meta_data = [];
                    foreach ( $tax_item->get_meta_data() as $meta ) {
                        $tax_meta_data[] = [
                            'key'   => $meta->key,
                            'value' => $meta->value,
                        ];
                    }
                    $tax_data[] = [
                        'rate_code'          => $tax_item->get_rate_code(),
                        'rate_id'            => $tax_item->get_rate_id(),
                        'label'              => $tax_item->get_label(),
                        'compound'           => $tax_item->get_compound(),
                        'tax_total'          => $tax_item->get_tax_total(),
                        'shipping_tax_total' => $tax_item->get_shipping_tax_total(),
                        'meta_data'          => $tax_meta_data,
                    ];
                }
            }

            return [
                'id'                   => $order->get_id(),
                'status'               => $order->get_status(),
                'currency'             => $order->get_currency(),
                'total'                => $order->get_total(),
                'discount_total'       => $order->get_discount_total(),
                'cart_tax'             => $order->get_cart_tax(),
                'shipping_tax'         => $order->get_shipping_tax(),
                'total_tax'            => $order->get_total_tax(),
                'customer_id'          => $order->get_customer_id(),
                'payment_method'       => $order->get_payment_method(),
                'payment_method_title' => $order->get_payment_method_title(),
                'date_created'         => [
                    'date' => $order->get_date_created() ? $order->get_date_created()->format('Y-m-d H:i:s') : '',
                ],
                'date_modified'        => [
                    'date' => $order->get_date_modified() ? $order->get_date_modified()->format('Y-m-d H:i:s') : '',
                ],
                'prices_include_tax'   => $order->get_prices_include_tax(),
                'customer_ip_address'  => $order->get_customer_ip_address(),
                'customer_user_agent'  => $order->get_customer_user_agent(),
                'billing'              => [
                    'first_name' => $order->get_billing_first_name(),
                    'last_name'  => $order->get_billing_last_name(),
                    'company'    => $order->get_billing_company(),
                    'address_1'  => $order->get_billing_address_1(),
                    'address_2'  => $order->get_billing_address_2(),
                    'city'       => $order->get_billing_city(),
                    'state'      => $order->get_billing_state(),
                    'postcode'   => $order->get_billing_postcode(),
                    'country'    => $order->get_billing_country(),
                    'email'      => $order->get_billing_email(),
                    'phone'      => $order->get_billing_phone(),
                ],
                'shipping'             => [
                    'first_name' => $order->get_shipping_first_name(),
                    'last_name'  => $order->get_shipping_last_name(),
                    'company'    => $order->get_shipping_company(),
                    'address_1'  => $order->get_shipping_address_1(),
                    'address_2'  => $order->get_shipping_address_2(),
                    'city'       => $order->get_shipping_city(),
                    'state'      => $order->get_shipping_state(),
                    'postcode'   => $order->get_shipping_postcode(),
                    'country'    => $order->get_shipping_country(),
                ],
                'coupon_lines'         => $coupon_data,
                'tax_lines'            => $tax_data,
            ];

            // Include custom order meta keys if enabled and selected
            $enable_custom_meta = $this->option()->get('enable_related_order_metadata_archiving', false);
            if ( $enable_custom_meta ) {
                $selected_meta_keys = $this->option()->get('related_order_metadata_selected', []);
                if ( ! empty($selected_meta_keys) && is_array($selected_meta_keys) ) {
                    $custom_meta = [];
                    foreach ( $selected_meta_keys as $meta_key ) {
                        $meta_value = $order->get_meta($meta_key);
                        if ( '' !== $meta_value ) {
                            $custom_meta[ $meta_key ] = $meta_value;
                        }
                    }
                    if ( ! empty($custom_meta) ) {
                        $order_data['custom_meta'] = $custom_meta;
                    }
                }
            }

            return $order_data;
        }

        /**
         * Prepare order products for archiving
         *
         * @param WC_Order $order
         * @return array
         */
        private function prepare_order_products_for_archive( $order ) {
            $products = [];
            foreach ( $order->get_items() as $item ) {
                $product = $item->get_product();
                if ( $product ) {
                    $products[] = [
                        'product_id' => $product->get_id(),
                        'name'       => $item->get_name(),
                        'quantity'   => $item->get_quantity(),
                        'total'      => $item->get_total(),
                        'subtotal'   => $item->get_subtotal(),
                    ];
                }
            }
            return $products;
        }

        /**
         * Delete single order from main database
         *
         * @param int $order_id
         * @return array
         */
        private function delete_single_order( $order_id ) {
            try {
                $order = wc_get_order($order_id);
                if ( ! $order ) {
                    return [
                        'success' => false,
                        'message' => __('Order not found for deletion.', 'archive-master'),
                    ];
                }

                // IMPORTANT: Trigger woocommerce_trash_order hook BEFORE permanent deletion
                // This allows order-sync plugins (like Google Sheets sync) to detect and sync the deletion
                // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedHooknameFound
                // Only trigger hook if Google Sheets order-sync plugin is active
                if ( $this->is_order_sync_plugin_active() ) {
                    do_action('woocommerce_trash_order', $order_id);
                }

                // Force delete the order permanently
                $result = $order->delete(true);

                if ( $result ) {
                    // Clear any related caches
                    wp_cache_delete($order_id, 'posts');
                    if ( function_exists('wc_delete_shop_order_transients') ) {
                        wc_delete_shop_order_transients($order_id);
                    }

                    return [
                        'success' => true,
                        'message' => __('Order deleted successfully.', 'archive-master'),
                    ];
                } else {
                    return [
                        'success' => false,
                        'message' => __('Failed to delete order from main database.', 'archive-master'),
                    ];
                }
            } catch ( \Exception $e ) {
                return [
                    'success' => false,
                    'message' => __('Error deleting order: ', 'archive-master') . $e->getMessage(),
                ];
            }
        }

        /**
         * Check if Pro plugin analytics features are available
         *
         * @return bool
         */
        private function is_pro_analytics_available() {
            return function_exists('master_archive_pro') &&
            class_exists('\ARCHM_PRO\Features\AnalyticsCacheManager');
        }

        /**
         * Get analytics filter mode
         *
         * @param \WP_REST_Request $request
         * @return \WP_REST_Response
         */
        public function get_analytics_filter( $request ) {
            // Check if Pro is available
            if ( ! $this->is_pro_analytics_available() ) {
                return new \WP_REST_Response([
                    'success' => false,
                    'message' => __('Analytics features require Archive Master Pro.', 'archive-master'),
                ], 403);
            }

            $filter_manager = \ARCHM_PRO\Analytics\ArchivedDataFilterManager::get_instance();
            $mode           = $filter_manager->get_filter_mode();

            return new \WP_REST_Response([
                'mode'    => $mode,
                'success' => true,
            ], 200);
        }

        /**
         * Set analytics filter mode
         *
         * @param \WP_REST_Request $request
         * @return \WP_REST_Response
         */
        public function set_analytics_filter( $request ) {
            // Check if Pro is available
            if ( ! $this->is_pro_analytics_available() ) {
                return new \WP_REST_Response([
                    'success' => false,
                    'message' => __('Analytics features require Archive Master Pro.', 'archive-master'),
                ], 403);
            }

            $mode = $request->get_param('mode');

            if ( ! $mode || ! in_array($mode, [ 'exclude', 'combine', 'archived_only' ], true) ) {
                return new \WP_REST_Response([
                    'success' => false,
                    'message' => __('Invalid filter mode.', 'archive-master'),
                ], 400);
            }

            $filter_manager = \ARCHM_PRO\Analytics\ArchivedDataFilterManager::get_instance();
            $result         = $filter_manager->set_filter_mode($mode);

            if ( $result ) {
                // Invalidate WooCommerce analytics cache via Pro method if available
                if ( class_exists('\ARCHM_PRO\Features\AnalyticsCacheManager') ) {
                    $cache_manager = \ARCHM_PRO\Features\AnalyticsCacheManager::get_instance();
                    $cache_manager->clear_analytics_cache();
                } else {
                    delete_option('archm_analytics_version');
                }

                return new \WP_REST_Response([
                    'success' => true,
                    'mode'    => $mode,
                    'message' => __('Filter mode updated successfully.', 'archive-master'),
                ], 200);
            } else {
                return new \WP_REST_Response([
                    'success' => false,
                    'message' => __('Failed to update filter mode.', 'archive-master'),
                ], 500);
            }
        }

        /**
         * Get custom order meta keys (excluding WooCommerce defaults)
         *
         * @param \WP_REST_Request $request
         * @return \WP_REST_Response
         */
        public function get_custom_order_meta_keys( $request ) {
            global $wpdb;

            try {
                // Check if HPOS (High-Performance Order Storage) is enabled
                $hpos_enabled = $this->is_hpos_enabled();
                if ( class_exists('\Automattic\WooCommerce\Utilities\OrderUtil') ) {
                    $hpos_enabled = \Automattic\WooCommerce\Utilities\OrderUtil::custom_orders_table_usage_is_enabled();
                }

                // Get all order meta keys from the database
                if ( $hpos_enabled ) {
                    // HPOS: Use wc_orders_meta table
                    $meta_keys = $wpdb->get_col(
                        "SELECT DISTINCT meta_key
                        FROM {$wpdb->prefix}wc_orders_meta
                        WHERE meta_key NOT LIKE '\\_%'
                        ORDER BY meta_key ASC"
                    );
                } else {
                    // Traditional: Use postmeta table
                    $meta_keys = $wpdb->get_col(
                        $wpdb->prepare(
                            "SELECT DISTINCT pm.meta_key
                            FROM {$wpdb->postmeta} AS pm
                            INNER JOIN {$wpdb->posts} AS p ON p.ID = pm.post_id
                            WHERE p.post_type = %s 
                            AND pm.meta_key NOT LIKE %s
                            ORDER BY pm.meta_key ASC",
                            'shop_order',
                            '\\_%'
                        )
                    );
                }

                // Define WooCommerce core default meta keys to exclude
                $wc_default_meta_keys = [
                    // Billing fields
                    '_billing_address_1',
                    '_billing_address_2',
                    '_billing_city',
                    '_billing_company',
                    '_billing_country',
                    '_billing_email',
                    '_billing_first_name',
                    '_billing_last_name',
                    '_billing_phone',
                    '_billing_postcode',
                    '_billing_state',
                    // Shipping fields
                    '_shipping_address_1',
                    '_shipping_address_2',
                    '_shipping_city',
                    '_shipping_company',
                    '_shipping_country',
                    '_shipping_first_name',
                    '_shipping_last_name',
                    '_shipping_method',
                    '_shipping_postcode',
                    '_shipping_state',
                    '_shipping_phone',
                    // Payment fields
                    '_payment_method',
                    '_payment_method_title',
                    // Order fields
                    '_completed_date',
                    '_customer_email',
                    '_customer_user',
                    '_date_completed',
                    '_date_paid',
                    '_fee_total',
                    '_order_currency',
                    '_order_discount',
                    '_order_key',
                    '_order_number',
                    '_order_shipping',
                    '_order_shipping_tax',
                    '_order_total',
                    '_order_tax',
                    '_order_version',
                    '_paid_date',
                    '_prices_include_tax',
                    '_shipping_tax_total',
                    '_tax_total',
                    '_cart_discount',
                    '_discount_total',
                    // Transaction fields
                    '_transaction_id',
                    '_transaction_id_original',
                    // Other common WC fields
                    '_created_via',
                    '_customer_ip_address',
                    '_customer_user_agent',
                    '_download_permissions_granted',
                    '_recorded_sales',
                    '_order_stock_reduced',
                    '_edit_lock',
                    '_edit_last',
                ];

                // Filter out WooCommerce default meta keys
                $custom_meta_keys = $this->filter_custom_meta_keys($meta_keys, $wc_default_meta_keys);

                // Format as options for Select component
                $options = array_map(function ( $key ) {
                    return [
                        'value' => $key,
                        'label' => $key,
                    ];
                }, array_values($custom_meta_keys));

                return new \WP_REST_Response([
                    'success' => true,
                    'data'    => $options,
                    'count'   => count($options),
                ], 200);

            } catch ( \Exception $e ) {
                return new \WP_REST_Response([
                    'success' => false,
                    'message' => __('Failed to fetch custom order meta keys: ', 'archive-master') . $e->getMessage(),
                    'data'    => [],
                ], 500);
            }
        }
    }

    // Instantiate.
    AdminRest::get_instance();
}
