<?php
/**
 * Store Reports Module.
 *
 * Advanced reporting: export orders, customers, and products to
 * CSV / XLSX / XML / JSON with full column control, drag-and-drop
 * reordering, custom rename, saved column presets, comprehensive
 * filters, and saveable filter presets.
 *
 * @package JWP_STK
 */

namespace JWP_STK\Modules\StoreReports;

use JWP_STK\Modules\Base_Module;

defined( 'ABSPATH' ) || exit;

/**
 * Module class.
 */
class Module extends Base_Module {

	// ─── Lifecycle ────────────────────────────────────────────────────────────

	/**
	 * Return the module slug.
	 *
	 * @return string
	 */
	public function get_slug() {
		return 'store-reports';
	}

	/**
	 * Return the module default settings.
	 *
	 * @return array
	 */
	public function get_defaults() {
		return array(
			'max_export_rows'       => 2000,
			'report_retention_days' => 7,
		);
	}

	/**
	 * Initialize the module — hook REST routes + schedule cleanup cron.
	 *
	 * @return void
	 */
	public function init() {
		$this->maybe_create_schedules_table();
		$this->maybe_create_report_files_table();

		add_action( 'rest_api_init', array( $this, 'register_rest_routes' ) );
		\JWP_STK\Jobs\Job_Manager::get_instance()->register_handler( 'store-reports', array( $this, 'handle_report_job' ) );

		// Hook into job deletion so we can clean up files and report_files rows.
		add_action( 'jwp_stk_job_before_delete', array( $this, 'on_job_before_delete' ), 10, 2 );

		// Chained ActionScheduler steps for real progress updates.
		add_action( 'jwp_stk_sr_build_rows', array( $this, 'handle_build_rows' ), 10, 1 );
		add_action( 'jwp_stk_sr_write_file', array( $this, 'handle_write_file' ), 10, 1 );

		// Schedule daily cleanup if not already scheduled.
		if ( ! wp_next_scheduled( 'jwp_stk_cleanup_store_reports' ) ) {
			wp_schedule_event( time(), 'daily', 'jwp_stk_cleanup_store_reports' );
		}
		add_action( 'jwp_stk_cleanup_store_reports', array( $this, 'cleanup_old_reports' ) );

		// Hourly cron to fire due scheduled reports.
		if ( ! wp_next_scheduled( 'jwp_stk_sr_process_schedules' ) ) {
			wp_schedule_event( time(), 'hourly', 'jwp_stk_sr_process_schedules' );
		}
		add_action( 'jwp_stk_sr_process_schedules', array( $this, 'process_scheduled_reports' ) );

		// Per-schedule ActionScheduler actions for precise firing.
		add_action( 'jwp_stk_sr_run_single_schedule', array( $this, 'run_single_scheduled_report' ), 10, 1 );
	}

	/**
	 * Create the jwp_stk_schedules table if it does not yet exist.
	 *
	 * Uses its own version option so it is independent of the plugin DB upgrader.
	 *
	 * @return void
	 */
	private function maybe_create_schedules_table(): void {
		if ( '1.0' === get_option( 'jwp_stk_schedules_db_version' ) ) {
			return;
		}

		global $wpdb;

		$table           = $wpdb->prefix . 'jwp_stk_schedules';
		$charset_collate = $wpdb->get_charset_collate();

		$sql = "CREATE TABLE {$table} (
			id bigint(20) NOT NULL AUTO_INCREMENT,
			schedule_id varchar(36) NOT NULL,
			module varchar(64) NOT NULL,
			type varchar(64) NOT NULL,
			title varchar(255) NOT NULL,
			frequency varchar(20) NOT NULL,
			day_of_week tinyint(1) DEFAULT NULL,
			day_of_month tinyint(2) DEFAULT NULL,
			time_of_day varchar(5) NOT NULL DEFAULT '06:00',
			payload longtext NOT NULL,
			status varchar(20) NOT NULL DEFAULT 'active',
			last_run_at datetime DEFAULT NULL,
			last_run_status varchar(20) DEFAULT NULL,
			next_run_at datetime DEFAULT NULL,
			created_at datetime NOT NULL,
			user_id bigint(20) NOT NULL,
			PRIMARY KEY (id),
			UNIQUE KEY schedule_id (schedule_id),
			KEY module_status (module, status),
			KEY next_run_at (next_run_at)
		) {$charset_collate};";

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta( $sql );
		update_option( 'jwp_stk_schedules_db_version', '1.0' );
	}

	/**
	 * Remove report jobs (and their files) older than the configured retention period.
	 *
	 * Runs via the `jwp_stk_cleanup_store_reports` cron event.
	 *
	 * @return void
	 */
	public function cleanup_old_reports(): void {
		$days = max( 1, (int) $this->get_setting( 'report_retention_days', 7 ) );
		\JWP_STK\Jobs\Job_Manager::get_instance()->delete_jobs_older_than( 'store-reports', $days );
	}

	/**
	 * Return the fully-qualified report_files table name.
	 *
	 * @return string
	 */
	private function get_report_files_table(): string {
		global $wpdb;
		return $wpdb->prefix . 'jwp_stk_report_files';
	}

	/**
	 * Create the jwp_stk_report_files table if it does not yet exist.
	 *
	 * Stores file_path and share data per job_id, owned entirely by this module.
	 *
	 * @return void
	 */
	private function maybe_create_report_files_table(): void {
		if ( '1.0' === get_option( 'jwp_stk_report_files_db_version' ) ) {
			return;
		}

		global $wpdb;
		$table           = $this->get_report_files_table();
		$charset_collate = $wpdb->get_charset_collate();

		$sql = "CREATE TABLE {$table} (
			id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			job_id varchar(36) NOT NULL,
			file_path varchar(500) DEFAULT NULL,
			share_token varchar(64) DEFAULT NULL,
			share_mode varchar(20) DEFAULT NULL,
			share_expires_at datetime DEFAULT NULL,
			PRIMARY KEY (id),
			UNIQUE KEY job_id (job_id),
			KEY share_token (share_token)
		) {$charset_collate};";

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta( $sql );
		update_option( 'jwp_stk_report_files_db_version', '1.0' );
	}

	/**
	 * Return the absolute path to the report output directory, creating it with
	 * access protection if it does not exist yet.
	 *
	 * @return string
	 */
	public function get_files_dir(): string {
		$upload_dir = wp_upload_dir();
		$dir        = trailingslashit( $upload_dir['basedir'] ) . 'jwp-stk-jobs';

		if ( ! is_dir( $dir ) ) {
			wp_mkdir_p( $dir );
		}

		$index = $dir . '/index.php';
		if ( ! file_exists( $index ) ) {
			// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_file_put_contents
			file_put_contents( $index, '<?php // Silence is golden.' );
		}

		$htaccess = $dir . '/.htaccess';
		if ( ! file_exists( $htaccess ) ) {
			// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_file_put_contents
			file_put_contents( $htaccess, 'deny from all' );
		}

		return $dir;
	}

	/**
	 * Insert or update the file_path for a report job in report_files.
	 *
	 * @param string $job_id    Job UUID.
	 * @param string $file_path Absolute path to the output file.
	 * @return void
	 */
	private function set_report_file( string $job_id, string $file_path ): void {
		global $wpdb;
		$table = $this->get_report_files_table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->replace(
			$table,
			array(
				'job_id'    => $job_id,
				'file_path' => $file_path,
			),
			array( '%s', '%s' )
		);
	}

	/**
	 * Fired by `jwp_stk_job_before_delete` — clean up file and report_files row
	 * for store-reports jobs before the job row is removed from the queue.
	 *
	 * @param string $job_id Job UUID.
	 * @param string $module Module slug.
	 * @return void
	 */
	public function on_job_before_delete( string $job_id, string $module ): void {
		if ( 'store-reports' !== $module ) {
			return;
		}

		global $wpdb;
		$table = $this->get_report_files_table();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$row = $wpdb->get_row(
			$wpdb->prepare( "SELECT file_path FROM {$table} WHERE job_id = %s", $job_id ) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		);

		if ( $row && ! empty( $row->file_path ) && file_exists( $row->file_path ) ) {
			wp_delete_file( $row->file_path );
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->delete( $table, array( 'job_id' => $job_id ), array( '%s' ) );
	}

	/**
	 * Permission callback — requires the manage_woocommerce capability.
	 *
	 * @return bool
	 */
	public function check_permission(): bool {
		return current_user_can( 'manage_woocommerce' ); // phpcs:ignore WordPress.WP.Capabilities.Unknown
	}

	// ─── REST Routes ──────────────────────────────────────────────────────────

	/**
	 * Register all store-reports REST API routes.
	 *
	 * @return void
	 */
	public function register_rest_routes() {
		// Generate report (download content).
		register_rest_route(
			'jwp-stk/v1',
			'/store-reports/report',
			array(
				'methods'             => \WP_REST_Server::CREATABLE,
				'callback'            => array( $this, 'rest_report' ),
				'permission_callback' => array( $this, 'check_permission' ),
				'args'                => array(
					'type'    => array(
						'required'          => true,
						'type'              => 'string',
						'enum'              => array( 'orders', 'customers', 'products' ),
						'sanitize_callback' => 'sanitize_key',
					),
					'format'  => array(
						'required'          => false,
						'type'              => 'string',
						'default'           => 'csv',
						'enum'              => array( 'csv', 'xlsx', 'xml', 'json' ),
						'sanitize_callback' => 'sanitize_key',
					),
					'columns' => array(
						'required' => false,
						'type'     => 'array',
						'default'  => array(),
					),
					'filters' => array(
						'required' => false,
						'type'     => 'object',
						'default'  => array(),
					),
				),
			)
		);

		// List all presets.
		register_rest_route(
			'jwp-stk/v1',
			'/store-reports/presets',
			array(
				'methods'             => \WP_REST_Server::READABLE,
				'callback'            => array( $this, 'rest_get_presets' ),
				'permission_callback' => array( $this, 'check_permission' ),
			)
		);

		// Save a preset.
		register_rest_route(
			'jwp-stk/v1',
			'/store-reports/presets',
			array(
				'methods'             => \WP_REST_Server::CREATABLE,
				'callback'            => array( $this, 'rest_save_preset' ),
				'permission_callback' => array( $this, 'check_permission' ),
				'args'                => array(
					'preset_type' => array(
						'required'          => true,
						'type'              => 'string',
						'enum'              => array( 'column', 'filter' ),
						'sanitize_callback' => 'sanitize_key',
					),
					'name'        => array(
						'required'          => true,
						'type'              => 'string',
						'sanitize_callback' => 'sanitize_text_field',
					),
					'report_type' => array(
						'required'          => true,
						'type'              => 'string',
						'enum'              => array( 'orders', 'customers', 'products' ),
						'sanitize_callback' => 'sanitize_key',
					),
					'data'        => array(
						'required'          => true,
						'type'              => 'object',
						'sanitize_callback' => array( $this, 'sanitize_preset_data' ),
					),
				),
			)
		);

		// Delete a preset.
		register_rest_route(
			'jwp-stk/v1',
			'/store-reports/presets/(?P<id>[a-zA-Z0-9._-]+)',
			array(
				'methods'             => \WP_REST_Server::DELETABLE,
				'callback'            => array( $this, 'rest_delete_preset' ),
				'permission_callback' => array( $this, 'check_permission' ),
				'args'                => array(
					'id' => array(
						'required'          => true,
						'type'              => 'string',
						'sanitize_callback' => 'sanitize_text_field',
					),
				),
			)
		);

		// Update (rename/replace data of) an existing preset.
		register_rest_route(
			'jwp-stk/v1',
			'/store-reports/presets/(?P<id>[a-zA-Z0-9._-]+)',
			array(
				'methods'             => \WP_REST_Server::EDITABLE,
				'callback'            => array( $this, 'rest_update_preset' ),
				'permission_callback' => array( $this, 'check_permission' ),
				'args'                => array(
					'id' => array(
						'required'          => true,
						'type'              => 'string',
						'sanitize_callback' => 'sanitize_text_field',
					),
				),
			)
		);

		// Get dynamic filter options (statuses, payment methods, etc.).
		register_rest_route(
			'jwp-stk/v1',
			'/store-reports/options',
			array(
				'methods'             => \WP_REST_Server::READABLE,
				'callback'            => array( $this, 'rest_get_options' ),
				'permission_callback' => array( $this, 'check_permission' ),
			)
		);

		// Queue a background report generation job.
		register_rest_route(
			'jwp-stk/v1',
			'/store-reports/generate',
			array(
				'methods'             => \WP_REST_Server::CREATABLE,
				'callback'            => array( $this, 'rest_generate' ),
				'permission_callback' => array( $this, 'check_permission' ),
				'args'                => array(
					'type'    => array(
						'required'          => true,
						'type'              => 'string',
						'enum'              => array( 'orders', 'customers', 'products' ),
						'sanitize_callback' => 'sanitize_key',
					),
					'format'  => array(
						'required'          => false,
						'type'              => 'string',
						'default'           => 'csv',
						'enum'              => array( 'csv', 'xlsx', 'xml', 'json' ),
						'sanitize_callback' => 'sanitize_key',
					),
					'columns' => array(
						'required' => false,
						'type'     => 'array',
						'default'  => array(),
					),
					'filters' => array(
						'required' => false,
						'type'     => 'object',
						'default'  => array(),
					),
					'title'   => array(
						'required'          => false,
						'type'              => 'string',
						'default'           => '',
						'sanitize_callback' => 'sanitize_text_field',
					),
				),
			)
		);

		// List all scheduled reports.
		register_rest_route(
			'jwp-stk/v1',
			'/store-reports/schedules',
			array(
				'methods'             => \WP_REST_Server::READABLE,
				'callback'            => array( $this, 'rest_get_schedules' ),
				'permission_callback' => array( $this, 'check_permission' ),
			)
		);

		// Create a scheduled report.
		register_rest_route(
			'jwp-stk/v1',
			'/store-reports/schedules',
			array(
				'methods'             => \WP_REST_Server::CREATABLE,
				'callback'            => array( $this, 'rest_create_schedule' ),
				'permission_callback' => array( $this, 'check_permission' ),
				'args'                => array(
					'title'        => array(
						'required'          => true,
						'type'              => 'string',
						'sanitize_callback' => 'sanitize_text_field',
					),
					'type'         => array(
						'required'          => true,
						'type'              => 'string',
						'enum'              => array( 'orders', 'customers', 'products' ),
						'sanitize_callback' => 'sanitize_key',
					),
					'frequency'    => array(
						'required'          => true,
						'type'              => 'string',
						'enum'              => array( 'daily', 'weekly', 'bi-weekly', 'monthly', 'bi-monthly' ),
						'sanitize_callback' => 'sanitize_key',
					),
					'time_of_day'  => array(
						'required'          => false,
						'type'              => 'string',
						'default'           => '06:00',
						'sanitize_callback' => 'sanitize_text_field',
					),
					'day_of_week'  => array(
						'required' => false,
						'type'     => array( 'integer', 'null' ),
					),
					'day_of_month' => array(
						'required' => false,
						'type'     => array( 'integer', 'null' ),
					),
					'format'       => array(
						'required'          => false,
						'type'              => 'string',
						'default'           => 'csv',
						'enum'              => array( 'csv', 'xlsx', 'xml', 'json' ),
						'sanitize_callback' => 'sanitize_key',
					),
					'columns'      => array(
						'required' => false,
						'type'     => 'array',
						'default'  => array(),
					),
					'filters'      => array(
						'required' => false,
						'type'     => 'object',
						'default'  => array(),
					),
				),
			)
		);

		// Update (pause/resume/rename) a scheduled report.
		register_rest_route(
			'jwp-stk/v1',
			'/store-reports/schedules/(?P<schedule_id>[a-zA-Z0-9._-]+)',
			array(
				'methods'             => 'PATCH',
				'callback'            => array( $this, 'rest_update_schedule' ),
				'permission_callback' => array( $this, 'check_permission' ),
				'args'                => array(
					'schedule_id' => array(
						'required'          => true,
						'type'              => 'string',
						'sanitize_callback' => 'sanitize_text_field',
					),
					'status'      => array(
						'required'          => false,
						'type'              => 'string',
						'enum'              => array( 'active', 'paused' ),
						'sanitize_callback' => 'sanitize_key',
					),
					'title'       => array(
						'required'          => false,
						'type'              => 'string',
						'sanitize_callback' => 'sanitize_text_field',
					),
				),
			)
		);

		// Delete a scheduled report.
		register_rest_route(
			'jwp-stk/v1',
			'/store-reports/schedules/(?P<schedule_id>[a-zA-Z0-9._-]+)',
			array(
				'methods'             => \WP_REST_Server::DELETABLE,
				'callback'            => array( $this, 'rest_delete_schedule' ),
				'permission_callback' => array( $this, 'check_permission' ),
				'args'                => array(
					'schedule_id' => array(
						'required'          => true,
						'type'              => 'string',
						'sanitize_callback' => 'sanitize_text_field',
					),
				),
			)
		);

		// ── Job-list, download, share, and public-share routes ────────────────

		$id_param = array(
			'id' => array(
				'required'          => true,
				'type'              => 'string',
				'sanitize_callback' => 'sanitize_text_field',
			),
		);

		// List report jobs for the current user (paginated, includes share data).
		register_rest_route(
			'jwp-stk/v1',
			'/store-reports/jobs',
			array(
				'methods'             => \WP_REST_Server::READABLE,
				'callback'            => array( $this, 'rest_list_report_jobs' ),
				'permission_callback' => array( $this, 'check_permission' ),
				'args'                => array(
					'page'     => array(
						'required'          => false,
						'type'              => 'integer',
						'default'           => 1,
						'minimum'           => 1,
						'sanitize_callback' => 'absint',
					),
					'per_page' => array(
						'required'          => false,
						'type'              => 'integer',
						'default'           => 10,
						'minimum'           => 1,
						'maximum'           => 100,
						'sanitize_callback' => 'absint',
					),
				),
			)
		);

		// Download a completed report's output file.
		register_rest_route(
			'jwp-stk/v1',
			'/store-reports/jobs/(?P<id>[a-zA-Z0-9_-]+)/download',
			array(
				'methods'             => \WP_REST_Server::READABLE,
				'callback'            => array( $this, 'rest_download_report_job' ),
				'permission_callback' => array( $this, 'check_permission' ),
				'args'                => $id_param,
			)
		);

		// Create or revoke a public share link for a completed report.
		register_rest_route(
			'jwp-stk/v1',
			'/store-reports/jobs/(?P<id>[a-zA-Z0-9_-]+)/share',
			array(
				// POST — generate a new share token.
				array(
					'methods'             => \WP_REST_Server::CREATABLE,
					'callback'            => array( $this, 'rest_create_report_share' ),
					'permission_callback' => array( $this, 'check_permission' ),
					'args'                => array_merge(
						$id_param,
						array(
							'expires_in' => array(
								'required' => true,
								'type'     => 'string',
								'enum'     => array( 'one-view', '24h', '7d', 'no-limit' ),
							),
						)
					),
				),
				// DELETE — remove an existing share token.
				array(
					'methods'             => \WP_REST_Server::DELETABLE,
					'callback'            => array( $this, 'rest_delete_report_share' ),
					'permission_callback' => array( $this, 'check_permission' ),
					'args'                => $id_param,
				),
			)
		);

		// Public (unauthenticated) share download — token acts as the credential.
		register_rest_route(
			'jwp-stk/v1',
			'/share/(?P<token>[a-zA-Z0-9]+)',
			array(
				'methods'             => \WP_REST_Server::READABLE,
				'callback'            => array( $this, 'rest_public_report_share' ),
				'permission_callback' => '__return_true',
				'args'                => array(
					'token' => array(
						'required'          => true,
						'type'              => 'string',
						'sanitize_callback' => 'sanitize_text_field',
					),
				),
			)
		);
	}

	// ─── REST Handlers ────────────────────────────────────────────────────────

	// ── Report-jobs endpoints ─────────────────────────────────────────────────

	/**
	 * List report jobs for the current user (paginated).
	 * LEFT JOINs with report_files so share + file data is included.
	 *
	 * @param \WP_REST_Request $request Request object.
	 * @return \WP_REST_Response
	 */
	public function rest_list_report_jobs( \WP_REST_Request $request ): \WP_REST_Response {
		global $wpdb;
		$jobs_table  = \JWP_STK\Jobs\Job_Manager::get_instance()->get_table();
		$files_table = $this->get_report_files_table();
		$user_id     = get_current_user_id();
		$page        = max( 1, (int) $request->get_param( 'page' ) );
		$per_page    = max( 1, min( 100, (int) $request->get_param( 'per_page' ) ) );
		$offset      = ( $page - 1 ) * $per_page;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$total = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$jobs_table} WHERE user_id = %d AND module = 'store-reports'",
				$user_id
			)
		);

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT j.*, rf.file_path, rf.share_token, rf.share_mode, rf.share_expires_at
				 FROM {$jobs_table} j
				 LEFT JOIN {$files_table} rf ON j.job_id = rf.job_id
				 WHERE j.user_id = %d AND j.module = 'store-reports'
				 ORDER BY j.created_at DESC
				 LIMIT %d OFFSET %d",
				$user_id,
				$per_page,
				$offset
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		$jobs = array();
		foreach ( $rows as $row ) {
			$share_url = null;
			if ( ! empty( $row->share_token ) ) {
				$share_url = rest_url( 'jwp-stk/v1/share/' . $row->share_token );
			}
			$jobs[] = array(
				'job_id'           => $row->job_id,
				'module'           => $row->module,
				'type'             => $row->type,
				'payload'          => null !== json_decode( $row->payload, true ) ? json_decode( $row->payload, true ) : array(),
				'status'           => $row->status,
				'progress'         => (int) $row->progress,
				'total_rows'       => (int) $row->total_rows,
				'created_at'       => $row->created_at,
				'completed_at'     => $row->completed_at,
				'duration_seconds' => null !== $row->duration_seconds ? (int) $row->duration_seconds : null,
				'error_message'    => $row->error_message,
				'file_available'   => ! empty( $row->file_path ) && file_exists( $row->file_path ),
				'share_token'      => $row->share_token ?? null,
				'share_mode'       => $row->share_mode ?? null,
				'share_expires_at' => $row->share_expires_at ?? null,
				'share_url'        => $share_url,
			);
		}

		$total_pages = $total > 0 ? (int) ceil( $total / $per_page ) : 1;
		$response    = new \WP_REST_Response( $jobs, 200 );
		$response->header( 'X-WP-Total', (string) $total );
		$response->header( 'X-WP-TotalPages', (string) $total_pages );

		return $response;
	}

	/**
	 * Download a completed report's output file.
	 *
	 * Returns: { content (string|base64), filename, count, binary }.
	 *
	 * @param \WP_REST_Request $request Request object.
	 * @return \WP_REST_Response|\WP_Error
	 */
	public function rest_download_report_job( \WP_REST_Request $request ) {
		global $wpdb;
		$job_id      = $request->get_param( 'id' );
		$jobs_table  = \JWP_STK\Jobs\Job_Manager::get_instance()->get_table();
		$files_table = $this->get_report_files_table();
		$user_id     = get_current_user_id();

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$row = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT j.job_id, j.status, j.total_rows, rf.file_path
				 FROM {$jobs_table} j
				 LEFT JOIN {$files_table} rf ON j.job_id = rf.job_id
				 WHERE j.job_id = %s AND j.user_id = %d",
				$job_id,
				$user_id
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		if ( ! $row ) {
			return new \WP_Error(
				'jwp_stk_job_not_found',
				__( 'Job not found.', 'jetix-store-toolkit' ),
				array( 'status' => 404 )
			);
		}

		if ( 'completed' !== $row->status ) {
			return new \WP_Error(
				'jwp_stk_job_not_ready',
				__( 'Job is not ready for download.', 'jetix-store-toolkit' ),
				array( 'status' => 409 )
			);
		}

		if ( empty( $row->file_path ) || ! file_exists( $row->file_path ) ) {
			return new \WP_Error(
				'jwp_stk_file_missing',
				__( 'Output file is missing.', 'jetix-store-toolkit' ),
				array( 'status' => 410 )
			);
		}

		$ext    = pathinfo( $row->file_path, PATHINFO_EXTENSION );
		$binary = 'xlsx' === $ext;

		// phpcs:ignore WordPress.WP.AlternativeFunctions.file_get_contents_file_get_contents
		$raw_content = file_get_contents( $row->file_path );
		$content     = $binary ? base64_encode( $raw_content ) : $raw_content; // phpcs:ignore WordPress.PHP.DiscouragedPHPFunctions.obfuscation_base64_encode

		return new \WP_REST_Response(
			array(
				'content'  => $content,
				'filename' => basename( $row->file_path ),
				'count'    => (int) $row->total_rows,
				'binary'   => $binary,
			),
			200
		);
	}

	/**
	 * POST /jwp-stk/v1/store-reports/jobs/{id}/share — generate a public share token.
	 *
	 * @param \WP_REST_Request $request Request object.
	 * @return \WP_REST_Response|\WP_Error
	 */
	public function rest_create_report_share( \WP_REST_Request $request ) {
		global $wpdb;
		$job_id      = $request->get_param( 'id' );
		$expires_in  = $request->get_param( 'expires_in' );
		$user_id     = get_current_user_id();
		$jobs_table  = \JWP_STK\Jobs\Job_Manager::get_instance()->get_table();
		$files_table = $this->get_report_files_table();

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$row = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT j.job_id, j.status, rf.file_path
				 FROM {$jobs_table} j
				 LEFT JOIN {$files_table} rf ON j.job_id = rf.job_id
				 WHERE j.job_id = %s AND j.user_id = %d",
				$job_id,
				$user_id
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		if ( ! $row ) {
			return new \WP_Error(
				'jwp_stk_job_not_found',
				__( 'Job not found.', 'jetix-store-toolkit' ),
				array( 'status' => 404 )
			);
		}

		if ( 'completed' !== $row->status ) {
			return new \WP_Error(
				'jwp_stk_job_not_ready',
				__( 'Only completed reports can be shared.', 'jetix-store-toolkit' ),
				array( 'status' => 400 )
			);
		}

		if ( empty( $row->file_path ) || ! file_exists( $row->file_path ) ) {
			return new \WP_Error(
				'jwp_stk_file_missing',
				__( 'Report file not found.', 'jetix-store-toolkit' ),
				array( 'status' => 410 )
			);
		}

		// Compute expiry datetime (null = unlimited).
		$expires_at = null;
		if ( '24h' === $expires_in ) {
			$expires_at = gmdate( 'Y-m-d H:i:s', strtotime( '+24 hours' ) );
		} elseif ( '7d' === $expires_in ) {
			$expires_at = gmdate( 'Y-m-d H:i:s', strtotime( '+7 days' ) );
		}

		// Generate a cryptographically random token.
		$token = wp_generate_password( 48, false );

		// Persist — use raw query so NULL can be set for share_expires_at.
		if ( null === $expires_at ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$wpdb->query(
				$wpdb->prepare(
					// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
					"INSERT INTO {$files_table} (job_id, file_path, share_token, share_mode, share_expires_at)
					 VALUES (%s, %s, %s, %s, NULL)
					 ON DUPLICATE KEY UPDATE share_token = VALUES(share_token), share_mode = VALUES(share_mode), share_expires_at = NULL",
					$job_id,
					$row->file_path,
					$token,
					$expires_in
				)
			);
		} else {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$wpdb->query(
				$wpdb->prepare(
					// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
					"INSERT INTO {$files_table} (job_id, file_path, share_token, share_mode, share_expires_at)
					 VALUES (%s, %s, %s, %s, %s)
					 ON DUPLICATE KEY UPDATE share_token = VALUES(share_token), share_mode = VALUES(share_mode), share_expires_at = VALUES(share_expires_at)",
					$job_id,
					$row->file_path,
					$token,
					$expires_in,
					$expires_at
				)
			);
		}

		return new \WP_REST_Response(
			array(
				'share_token'      => $token,
				'share_mode'       => $expires_in,
				'share_expires_at' => $expires_at,
				'share_url'        => rest_url( 'jwp-stk/v1/share/' . $token ),
			),
			200
		);
	}

	/**
	 * DELETE /jwp-stk/v1/store-reports/jobs/{id}/share — revoke a share token.
	 *
	 * @param \WP_REST_Request $request Request object.
	 * @return \WP_REST_Response|\WP_Error
	 */
	public function rest_delete_report_share( \WP_REST_Request $request ) {
		global $wpdb;
		$job_id      = $request->get_param( 'id' );
		$user_id     = get_current_user_id();
		$jobs_table  = \JWP_STK\Jobs\Job_Manager::get_instance()->get_table();
		$files_table = $this->get_report_files_table();

		// Verify ownership.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$exists = $wpdb->get_var(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				"SELECT job_id FROM {$jobs_table} WHERE job_id = %s AND user_id = %d",
				$job_id,
				$user_id
			)
		);

		if ( ! $exists ) {
			return new \WP_Error(
				'jwp_stk_job_not_found',
				__( 'Job not found.', 'jetix-store-toolkit' ),
				array( 'status' => 404 )
			);
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->query(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				"UPDATE {$files_table} SET share_token = NULL, share_mode = NULL, share_expires_at = NULL WHERE job_id = %s",
				$job_id
			)
		);

		return new \WP_REST_Response( array( 'disabled' => true ), 200 );
	}

	/**
	 * GET /jwp-stk/v1/share/{token} — public, unauthenticated file download.
	 *
	 * Validates the share token, respects expiry and one-view semantics, then
	 * streams the file directly to the browser.
	 *
	 * @param \WP_REST_Request $request Request object.
	 * @return \WP_Error|void Returns WP_Error on failure; exits on success.
	 */
	public function rest_public_report_share( \WP_REST_Request $request ) {
		global $wpdb;
		$files_table = $this->get_report_files_table();
		$token       = $request->get_param( 'token' );

		// Rate-limit unauthenticated requests per IP (30 req / min) to prevent DoS.
		$ip_hash  = hash( 'sha256', isset( $_SERVER['REMOTE_ADDR'] ) ? (string) $_SERVER['REMOTE_ADDR'] : '' ); // phpcs:ignore WordPress.Security.ValidatedSanitizedInput
		$rate_key = 'jwp_stk_shr_rl_' . substr( $ip_hash, 0, 32 );
		$hits     = (int) get_transient( $rate_key );
		if ( $hits >= 30 ) {
			return new \WP_Error(
				'jwp_stk_rate_limited',
				__( 'Too many requests. Please try again later.', 'jetix-store-toolkit' ),
				array( 'status' => 429 )
			);
		}
		set_transient( $rate_key, $hits + 1, MINUTE_IN_SECONDS );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$row = $wpdb->get_row(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				"SELECT job_id, file_path, share_token, share_mode, share_expires_at FROM {$files_table} WHERE share_token = %s",
				$token
			)
		);

		if ( ! $row ) {
			return new \WP_Error(
				'jwp_stk_invalid_token',
				__( 'Invalid or expired share link.', 'jetix-store-toolkit' ),
				array( 'status' => 404 )
			);
		}

		// Check datetime expiry (24h / 7d modes).
		if ( ! empty( $row->share_expires_at ) && strtotime( $row->share_expires_at ) < time() ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$wpdb->query(
				$wpdb->prepare(
					// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
					"UPDATE {$files_table} SET share_token = NULL, share_mode = NULL, share_expires_at = NULL WHERE job_id = %s",
					$row->job_id
				)
			);
			return new \WP_Error(
				'jwp_stk_share_expired',
				__( 'This share link has expired.', 'jetix-store-toolkit' ),
				array( 'status' => 410 )
			);
		}

		// Verify the file still exists.
		if ( empty( $row->file_path ) || ! file_exists( $row->file_path ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$wpdb->query(
				$wpdb->prepare(
					// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
					"UPDATE {$files_table} SET share_token = NULL, share_mode = NULL, share_expires_at = NULL WHERE job_id = %s",
					$row->job_id
				)
			);
			return new \WP_Error(
				'jwp_stk_file_missing',
				__( 'This report no longer exists.', 'jetix-store-toolkit' ),
				array( 'status' => 404 )
			);
		}

		$file_path = $row->file_path;

		// Guard against path traversal: ensure the file lives inside the expected
		// jobs upload directory, even if a DB value were ever tampered with.
		$upload    = wp_upload_dir();
		$jobs_base = realpath( trailingslashit( $upload['basedir'] ) . 'jwp-stk-jobs' );
		$real_file = realpath( $file_path );
		if ( false === $jobs_base || false === $real_file || 0 !== strpos( $real_file, $jobs_base . DIRECTORY_SEPARATOR ) ) {
			return new \WP_Error(
				'jwp_stk_file_invalid',
				__( 'Invalid file path.', 'jetix-store-toolkit' ),
				array( 'status' => 403 )
			);
		}

		// Invalidate one-view token before serving.
		if ( 'one-view' === $row->share_mode ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$wpdb->query(
				$wpdb->prepare(
					// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
					"UPDATE {$files_table} SET share_token = NULL, share_mode = NULL, share_expires_at = NULL WHERE job_id = %s",
					$row->job_id
				)
			);
		}

		// Detect MIME type from extension.
		$ext              = strtolower( (string) pathinfo( $file_path, PATHINFO_EXTENSION ) );
		$content_type_map = array(
			'csv'  => 'text/csv; charset=utf-8',
			'xlsx' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
			'xml'  => 'application/xml',
			'json' => 'application/json',
		);
		$content_type     = $content_type_map[ $ext ] ?? 'application/octet-stream';
		$filename         = basename( $file_path );

		// Flush any output buffers so the file stream is not corrupted.
		while ( ob_get_level() ) {
			ob_end_clean();
		}

		nocache_headers();
		header( 'Content-Type: ' . $content_type );
		header( 'Content-Disposition: attachment; filename="' . rawurlencode( $filename ) . '"' );
		header( 'Content-Length: ' . (string) filesize( $file_path ) ); // phpcs:ignore WordPress.PHP.NoSilencedErrors
		// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_readfile
		readfile( $file_path );
		exit;
	}

	/**
	 * Generate a report and return downloadable content.
	 *
	 * @param \WP_REST_Request $request Request object.
	 * @return \WP_REST_Response|\WP_Error
	 */
	public function rest_report( \WP_REST_Request $request ) {
		$type     = $request->get_param( 'type' );
		$format   = $request->get_param( 'format' );
		$raw_cols = (array) $request->get_param( 'columns' );
		$filters  = (array) $request->get_param( 'filters' );
		$settings = $this->get_settings();
		$max_rows = max( 1, min( 10000, (int) ( $settings['max_export_rows'] ?? 2000 ) ) );

		// Validate and whitelist columns.
		$allowed = $this->allowed_column_keys( $type );
		$columns = array();
		foreach ( $raw_cols as $col ) {
			$key   = sanitize_text_field( $col['key'] ?? '' );
			$label = sanitize_text_field( $col['label'] ?? '' );
			if ( $this->is_valid_column_key( $key, $allowed ) ) {
				$default_label = $this->get_default_label( $type, $key );
				$columns[]     = array(
					'key'   => $key,
					'label' => ( '' !== $label ) ? $label : $default_label,
				);
			}
		}

		// Fall back to a sensible default column set.
		if ( empty( $columns ) ) {
			$columns = $this->default_columns( $type );
		}

		// Query data.
		switch ( $type ) {
			case 'customers':
				list( $data, $count ) = $this->query_customers( $filters, $max_rows );
				break;
			case 'products':
				list( $data, $count ) = $this->query_products( $filters, $max_rows );
				break;
			default:
				list( $data, $count ) = $this->query_orders( $filters, $max_rows );
				break;
		}

		// Build rows.
		$headers = array_column( $columns, 'label' );
		$keys    = array_column( $columns, 'key' );
		$rows    = array();

		foreach ( $data as $item ) {
			$row = array();
			foreach ( $keys as $key ) {
				$row[] = $this->sanitize_cell_value( $this->get_value( $type, $item, $key ) );
			}
			$rows[] = $row;
		}

		// Format output.
		$date   = gmdate( 'Y-m-d' );
		$binary = false;

		switch ( $format ) {
			case 'xlsx':
				$content  = $this->to_xlsx( $headers, $rows );
				$filename = "{$type}-{$date}.xlsx";
				$binary   = true;
				break;
			case 'xml':
				$content  = $this->to_xml( $type, $headers, $rows );
				$filename = "{$type}-{$date}.xml";
				break;
			case 'json':
				$content  = $this->to_json( $headers, $rows );
				$filename = "{$type}-{$date}.json";
				break;
			default: // csv.
				$content  = $this->to_csv( $headers, $rows );
				$filename = "{$type}-{$date}.csv";
				break;
		}

		return new \WP_REST_Response(
			array(
				'content'  => $binary ? base64_encode( $content ) : $content, // phpcs:ignore WordPress.PHP.DiscouragedPHPFunctions.obfuscation_base64_encode
				'filename' => $filename,
				'count'    => $count,
				'binary'   => $binary,
			),
			200
		);
	}

	/**
	 * Return all saved presets.
	 *
	 * @return \WP_REST_Response
	 */
	public function rest_get_presets() {
		return new \WP_REST_Response( $this->get_presets_data(), 200 );
	}

	/**
	 * Save a new preset.
	 *
	 * @param \WP_REST_Request $request Request object.
	 * @return \WP_REST_Response|\WP_Error
	 */
	public function rest_save_preset( \WP_REST_Request $request ) {
		$preset_type = $request->get_param( 'preset_type' );
		$name        = $request->get_param( 'name' );
		$report_type = $request->get_param( 'report_type' );
		$data        = $request->get_param( 'data' );

		if ( '' === trim( $name ) ) {
			return new \WP_Error(
				'jwp_stk_preset_name_required',
				__( 'Preset name is required.', 'jetix-store-toolkit' ),
				array( 'status' => 400 )
			);
		}

		$presets = $this->get_presets_data();
		$key     = 'column' === $preset_type ? 'column_presets' : 'filter_presets';

		$preset = array(
			'id'          => uniqid( '', true ),
			'name'        => $name,
			'report_type' => $report_type,
			'data'        => $data,
		);

		$presets[ $key ][] = $preset;
		$this->save_presets_data( $presets );

		return new \WP_REST_Response( $preset, 201 );
	}

	/**
	 * Delete a preset by ID.
	 *
	 * @param \WP_REST_Request $request Request object.
	 * @return \WP_REST_Response|\WP_Error
	 */
	public function rest_delete_preset( \WP_REST_Request $request ) {
		$id      = $request->get_param( 'id' );
		$presets = $this->get_presets_data();

		foreach ( array( 'column_presets', 'filter_presets' ) as $key ) {
			$presets[ $key ] = array_values(
				array_filter( $presets[ $key ], fn( $p ) => $p['id'] !== $id )
			);
		}

		$this->save_presets_data( $presets );
		return new \WP_REST_Response( array( 'deleted' => true ), 200 );
	}

	/**
	 * Update an existing preset by ID.
	 *
	 * @param \WP_REST_Request $request Request object.
	 * @return \WP_REST_Response|\WP_Error
	 */
	public function rest_update_preset( \WP_REST_Request $request ) {
		$id      = $request->get_param( 'id' );
		$presets = $this->get_presets_data();

		foreach ( array( 'column_presets', 'filter_presets' ) as $key ) {
			foreach ( $presets[ $key ] as &$preset ) {
				if ( $preset['id'] === $id ) {
					$name = $request->get_param( 'name' );
					$data = $request->get_param( 'data' );
					if ( null !== $name ) {
						$preset['name'] = sanitize_text_field( $name );
					}
					if ( null !== $data ) {
						$preset['data'] = $this->sanitize_preset_data( $data );
					}
					$this->save_presets_data( $presets );
					return new \WP_REST_Response( $preset, 200 );
				}
			}
			unset( $preset );
		}

		return new \WP_Error(
			'jwp_stk_preset_not_found',
			__( 'Preset not found.', 'jetix-store-toolkit' ),
			array( 'status' => 404 )
		);
	}


	/**
	 * Return dynamic options used to populate filter UI dropdowns.
	 *
	 * @return \WP_REST_Response
	 */
	public function rest_get_options() {
		// All order statuses (built-in + custom from Custom Order Statuses module).
		$order_statuses = array();
		foreach ( wc_get_order_statuses() as $slug => $label ) {
			$order_statuses[] = array(
				'value' => $slug,
				'label' => $label,
			);
		}

		// Enabled payment gateways.
		$payment_methods = array(
			array(
				'value' => '',
				'label' => __( 'Any', 'jetix-store-toolkit' ),
			),
		);
		$gateways        = WC()->payment_gateways ? WC()->payment_gateways->get_available_payment_gateways() : array();
		foreach ( $gateways as $id => $gateway ) {
			$payment_methods[] = array(
				'value' => $id,
				'label' => $gateway->get_title(),
			);
		}

		// Shipping methods from all zones.
		$shipping_methods = array(
			array(
				'value' => '',
				'label' => __( 'Any', 'jetix-store-toolkit' ),
			),
		);
		$zones            = \WC_Shipping_Zones::get_zones();
		foreach ( $zones as $zone_data ) {
			$zone = new \WC_Shipping_Zone( $zone_data['id'] );
			foreach ( $zone->get_shipping_methods( true ) as $method ) {
				$shipping_methods[] = array(
					'value' => $method->get_rate_id(),
					'label' => $zone_data['zone_name'] . ' — ' . $method->get_title(),
				);
			}
		}

		// Product categories.
		$product_categories = array();
		$cats               = get_terms(
			array(
				'taxonomy'   => 'product_cat',
				'hide_empty' => false,
			)
		);
		if ( ! is_wp_error( $cats ) ) {
			foreach ( $cats as $cat ) {
				$product_categories[] = array(
					'value' => $cat->term_id,
					'label' => $cat->name,
				);
			}
		}

		// Products (for order filtering — up to 500 by title).
		$order_products = array();
		$raw_products   = wc_get_products(
			array(
				'limit'   => 500,
				'orderby' => 'title',
				'order'   => 'ASC',
				'return'  => 'objects',
				'status'  => array( 'publish', 'draft', 'private' ),
			)
		);
		foreach ( $raw_products as $product ) {
			$order_products[] = array(
				'value' => $product->get_id(),
				'label' => $product->get_name(),
			);
		}

		// Product types.
		$product_types = array(
			array(
				'value' => '',
				'label' => __( 'Any', 'jetix-store-toolkit' ),
			),
		);
		foreach ( wc_get_product_types() as $slug => $label ) {
			$product_types[] = array(
				'value' => $slug,
				'label' => $label,
			);
		}

		return new \WP_REST_Response(
			array(
				'order_statuses'     => $order_statuses,
				'payment_methods'    => $payment_methods,
				'shipping_methods'   => $shipping_methods,
				'product_categories' => $product_categories,
				'product_types'      => $product_types,
				'order_products'     => $order_products,
			),
			200
		);
	}

	// ─── Column Definitions ───────────────────────────────────────────────────

	/**
	 * Return grouped column definitions for the given report type.
	 *
	 * @param string $type Report type (orders|customers|products).
	 * @return array
	 */
	private function get_column_definitions( string $type ): array {
		switch ( $type ) {
			case 'customers':
				return $this->customer_columns_grouped();
			case 'products':
				return $this->product_columns_grouped();
			default:
				return $this->order_columns_grouped();
		}
	}

	/**
	 * Return grouped column definitions for orders.
	 *
	 * @return array
	 */
	private function order_columns_grouped(): array {
		return array(
			array(
				'group'   => __( 'Order', 'jetix-store-toolkit' ),
				'columns' => array(
					array(
						'key'   => 'order_id',
						'label' => __( 'Order ID', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'date',
						'label' => __( 'Date', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'status',
						'label' => __( 'Status', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'currency',
						'label' => __( 'Currency', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'customer_note',
						'label' => __( 'Customer Note', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'transaction_id',
						'label' => __( 'Transaction ID', 'jetix-store-toolkit' ),
					),
				),
			),
			array(
				'group'   => __( 'Billing', 'jetix-store-toolkit' ),
				'columns' => array(
					array(
						'key'   => 'billing_first_name',
						'label' => __( 'First Name', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'billing_last_name',
						'label' => __( 'Last Name', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'billing_email',
						'label' => __( 'Email', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'billing_phone',
						'label' => __( 'Phone', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'billing_company',
						'label' => __( 'Company', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'billing_address_1',
						'label' => __( 'Address Line 1', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'billing_address_2',
						'label' => __( 'Address Line 2', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'billing_city',
						'label' => __( 'City', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'billing_state',
						'label' => __( 'State / Region', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'billing_postcode',
						'label' => __( 'Postcode', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'billing_country',
						'label' => __( 'Country Code', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'billing_address',
						'label' => __( 'Full Billing Address', 'jetix-store-toolkit' ),
					),
				),
			),
			array(
				'group'   => __( 'Shipping', 'jetix-store-toolkit' ),
				'columns' => array(
					array(
						'key'   => 'shipping_first_name',
						'label' => __( 'First Name', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'shipping_last_name',
						'label' => __( 'Last Name', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'shipping_company',
						'label' => __( 'Company', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'shipping_address_1',
						'label' => __( 'Address Line 1', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'shipping_address_2',
						'label' => __( 'Address Line 2', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'shipping_city',
						'label' => __( 'City', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'shipping_state',
						'label' => __( 'State / Region', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'shipping_postcode',
						'label' => __( 'Postcode', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'shipping_country',
						'label' => __( 'Country Code', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'shipping_address',
						'label' => __( 'Full Shipping Address', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'shipping_method',
						'label' => __( 'Shipping Method', 'jetix-store-toolkit' ),
					),
				),
			),
			array(
				'group'   => __( 'Line Items', 'jetix-store-toolkit' ),
				'columns' => array(
					array(
						'key'   => 'items',
						'label' => __( 'Items Summary', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'items_count',
						'label' => __( 'Item Count', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'items_skus',
						'label' => __( 'Item SKUs', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'items_with_variations',
						'label' => __( 'Items + Variation Attributes', 'jetix-store-toolkit' ),
					),
				),
			),
			array(
				'group'   => __( 'Totals & Payment', 'jetix-store-toolkit' ),
				'columns' => array(
					array(
						'key'   => 'payment_method',
						'label' => __( 'Payment Method', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'subtotal',
						'label' => __( 'Subtotal', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'discount_total',
						'label' => __( 'Discount', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'shipping_cost',
						'label' => __( 'Shipping Cost', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'fee_total',
						'label' => __( 'Fees', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'tax_total',
						'label' => __( 'Tax', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'total',
						'label' => __( 'Total', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'tax_lines',
						'label' => __( 'Tax Breakdown', 'jetix-store-toolkit' ),
					),
				),
			),
			array(
				'group'   => __( 'Coupons', 'jetix-store-toolkit' ),
				'columns' => array(
					array(
						'key'   => 'coupons',
						'label' => __( 'Coupons Used', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'coupon_discount',
						'label' => __( 'Coupon Discount', 'jetix-store-toolkit' ),
					),
				),
			),
			array(
				'group'   => __( 'Customer', 'jetix-store-toolkit' ),
				'columns' => array(
					array(
						'key'   => 'customer_id',
						'label' => __( 'Customer ID', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'customer_username',
						'label' => __( 'Username', 'jetix-store-toolkit' ),
					),
				),
			),
		);
	}

	/**
	 * Return grouped column definitions for customers.
	 *
	 * @return array
	 */
	private function customer_columns_grouped(): array {
		return array(
			array(
				'group'   => __( 'Identity', 'jetix-store-toolkit' ),
				'columns' => array(
					array(
						'key'   => 'customer_id',
						'label' => __( 'Customer ID', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'username',
						'label' => __( 'Username', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'first_name',
						'label' => __( 'First Name', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'last_name',
						'label' => __( 'Last Name', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'name',
						'label' => __( 'Full Name', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'email',
						'label' => __( 'Email', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'phone',
						'label' => __( 'Phone', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'user_role',
						'label' => __( 'Role', 'jetix-store-toolkit' ),
					),
				),
			),
			array(
				'group'   => __( 'Billing Address', 'jetix-store-toolkit' ),
				'columns' => array(
					array(
						'key'   => 'billing_company',
						'label' => __( 'Company', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'billing_address',
						'label' => __( 'Address Line 1', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'billing_city',
						'label' => __( 'City', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'billing_state',
						'label' => __( 'State / Region', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'billing_postcode',
						'label' => __( 'Postcode', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'billing_country',
						'label' => __( 'Country Code', 'jetix-store-toolkit' ),
					),
				),
			),
			array(
				'group'   => __( 'Stats', 'jetix-store-toolkit' ),
				'columns' => array(
					array(
						'key'   => 'registered',
						'label' => __( 'Registered Date', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'total_orders',
						'label' => __( 'Total Orders', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'total_spent',
						'label' => __( 'Total Spent', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'average_order_value',
						'label' => __( 'Avg. Order Value', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'last_order_date',
						'label' => __( 'Last Order Date', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'last_order_amount',
						'label' => __( 'Last Order Amount', 'jetix-store-toolkit' ),
					),
				),
			),
		);
	}

	/**
	 * Return grouped column definitions for products.
	 *
	 * @return array
	 */
	private function product_columns_grouped(): array {
		return array(
			array(
				'group'   => __( 'Product', 'jetix-store-toolkit' ),
				'columns' => array(
					array(
						'key'   => 'product_id',
						'label' => __( 'Product ID', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'sku',
						'label' => __( 'SKU', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'name',
						'label' => __( 'Name', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'type',
						'label' => __( 'Type', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'status',
						'label' => __( 'Status', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'visibility',
						'label' => __( 'Visibility', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'short_description',
						'label' => __( 'Short Description', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'description',
						'label' => __( 'Description', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'featured',
						'label' => __( 'Featured', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'variations_count',
						'label' => __( 'Variations Count', 'jetix-store-toolkit' ),
					),
				),
			),
			array(
				'group'   => __( 'Pricing', 'jetix-store-toolkit' ),
				'columns' => array(
					array(
						'key'   => 'regular_price',
						'label' => __( 'Regular Price', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'sale_price',
						'label' => __( 'Sale Price', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'price',
						'label' => __( 'Current Price', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'sale_start',
						'label' => __( 'Sale Start Date', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'sale_end',
						'label' => __( 'Sale End Date', 'jetix-store-toolkit' ),
					),
				),
			),
			array(
				'group'   => __( 'Inventory', 'jetix-store-toolkit' ),
				'columns' => array(
					array(
						'key'   => 'stock_status',
						'label' => __( 'Stock Status', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'stock_quantity',
						'label' => __( 'Stock Quantity', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'backorders',
						'label' => __( 'Backorders', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'low_stock_amount',
						'label' => __( 'Low Stock Threshold', 'jetix-store-toolkit' ),
					),
				),
			),
			array(
				'group'   => __( 'Organisation', 'jetix-store-toolkit' ),
				'columns' => array(
					array(
						'key'   => 'categories',
						'label' => __( 'Categories', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'tags',
						'label' => __( 'Tags', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'weight',
						'label' => __( 'Weight', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'dimensions',
						'label' => __( 'Dimensions', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'reviews_allowed',
						'label' => __( 'Reviews Allowed', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'date_created',
						'label' => __( 'Date Created', 'jetix-store-toolkit' ),
					),
					array(
						'key'   => 'date_modified',
						'label' => __( 'Date Modified', 'jetix-store-toolkit' ),
					),
				),
			),
		);
	}

	// ─── Column Helpers ───────────────────────────────────────────────────────

	/**
	 * Return a flat list of allowed column keys for a report type.
	 *
	 * @param string $type Report type.
	 * @return string[]
	 */
	private function allowed_column_keys( string $type ): array {
		$keys = array();
		foreach ( $this->get_column_definitions( $type ) as $group ) {
			foreach ( $group['columns'] as $col ) {
				$keys[] = $col['key'];
			}
		}
		return $keys;
	}

	/**
	 * Validate a column key — either in the whitelist or a safe meta: prefix.
	 *
	 * @param string $key     Column key.
	 * @param array  $allowed Whitelisted keys.
	 * @return bool
	 */
	private function is_valid_column_key( string $key, array $allowed ): bool {
		if ( in_array( $key, $allowed, true ) ) {
			return true;
		}
		// Allow meta:<key> where <key> is alphanumeric / underscore / hyphen.
		if ( 0 === strpos( $key, 'meta:' ) ) {
			return (bool) preg_match( '/^[a-zA-Z0-9_\-]+$/', substr( $key, 5 ) );
		}
		return false;
	}

	/**
	 * Return the default translated label for a built-in column key.
	 *
	 * @param string $type Report type.
	 * @param string $key  Column key.
	 * @return string
	 */
	private function get_default_label( string $type, string $key ): string {
		foreach ( $this->get_column_definitions( $type ) as $group ) {
			foreach ( $group['columns'] as $col ) {
				if ( $col['key'] === $key ) {
					return $col['label'];
				}
			}
		}
		// Custom meta field — use raw key.
		if ( 0 === strpos( $key, 'meta:' ) ) {
			return substr( $key, 5 );
		}
		return $key;
	}

	/**
	 * Return sensible default columns for a report type.
	 *
	 * @param string $type Report type.
	 * @return array
	 */
	private function default_columns( string $type ): array {
		$defaults = array(
			'orders'    => array( 'order_id', 'date', 'status', 'billing_first_name', 'billing_last_name', 'billing_email', 'total' ),
			'customers' => array( 'customer_id', 'name', 'email', 'registered', 'total_orders', 'total_spent' ),
			'products'  => array( 'product_id', 'sku', 'name', 'type', 'status', 'price', 'stock_status' ),
		);
		$cols     = array();
		foreach ( ( $defaults[ $type ] ?? array() ) as $key ) {
			$cols[] = array(
				'key'   => $key,
				'label' => $this->get_default_label( $type, $key ),
			);
		}
		return $cols;
	}

	// ─── Query Methods ────────────────────────────────────────────────────────

	/**
	 * Query WooCommerce orders with filters.
	 *
	 * @param array $filters Filters array from request.
	 * @param int   $max     Max rows.
	 * @return array [ WC_Order[], count ]
	 */
	private function query_orders( array $filters, int $max ): array {
		$args = array(
			'limit'   => $max,
			'orderby' => 'date',
			'order'   => 'DESC',
			'return'  => 'objects',
			'type'    => 'shop_order',
		);

		// Date range.
		$date_from = $this->clean_date( $filters['date_from'] ?? '' );
		$date_to   = $this->clean_date( $filters['date_to'] ?? '' );
		if ( $date_from && $date_to ) {
			$args['date_created'] = $date_from . '...' . $date_to;
		} elseif ( $date_from ) {
			$args['date_created'] = '>=' . $date_from;
		} elseif ( $date_to ) {
			$args['date_created'] = '<=' . $date_to;
		}

		// Statuses (multi).
		$statuses = (array) ( $filters['statuses'] ?? array() );
		if ( ! empty( $statuses ) ) {
			$valid   = array_keys( wc_get_order_statuses() );
			$cleaned = array();
			foreach ( $statuses as $s ) {
				$s = sanitize_text_field( $s );
				$s = ( 0 === strpos( $s, 'wc-' ) ) ? $s : 'wc-' . $s;
				if ( in_array( $s, $valid, true ) ) {
					$cleaned[] = $s;
				}
			}
			if ( ! empty( $cleaned ) ) {
				$args['status'] = $cleaned;
			}
		}

		// Payment method (gateway ID).
		$payment = sanitize_text_field( $filters['payment_method'] ?? '' );
		if ( $payment ) {
			$args['payment_method'] = $payment;
		}

		$orders = wc_get_orders( $args );

		// Post-filter: coupon, total range, shipping method, product IDs, category IDs, custom meta.
		$coupon              = strtolower( sanitize_text_field( $filters['coupon_code'] ?? '' ) );
		$total_min           = isset( $filters['total_min'] ) && '' !== $filters['total_min'] ? (float) $filters['total_min'] : null;
		$total_max           = isset( $filters['total_max'] ) && '' !== $filters['total_max'] ? (float) $filters['total_max'] : null;
		$shipping_method     = sanitize_text_field( $filters['shipping_method'] ?? '' );
		$meta_key            = sanitize_key( $filters['meta_key'] ?? '' );
		$meta_val            = sanitize_text_field( $filters['meta_value'] ?? '' );
		$filter_product_ids  = array_map( 'absint', array_filter( (array) ( $filters['product_ids'] ?? array() ) ) );
		$filter_category_ids = array_map( 'absint', array_filter( (array) ( $filters['category_ids'] ?? array() ) ) );

		// Pre-compute product IDs belonging to the selected categories (single DB query).
		$products_in_categories = array();
		if ( ! empty( $filter_category_ids ) ) {
			$cat_posts              = get_posts(
				array(
					'post_type'      => 'product',
					'post_status'    => 'any',
					'fields'         => 'ids',
					'posts_per_page' => -1,
					'tax_query'      => array( // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_tax_query
						array(
							'taxonomy' => 'product_cat',
							'field'    => 'term_id',
							'terms'    => $filter_category_ids,
							'operator' => 'IN',
						),
					),
				)
			);
			$products_in_categories = array_map( 'intval', (array) $cat_posts );
		}

		if ( $coupon || null !== $total_min || null !== $total_max || $shipping_method || $meta_key || ! empty( $filter_product_ids ) || ! empty( $products_in_categories ) ) {
			$orders = array_values(
				array_filter(
					$orders,
					function ( $order ) use ( $coupon, $total_min, $total_max, $shipping_method, $meta_key, $meta_val, $filter_product_ids, $products_in_categories ) {
						if ( $coupon ) {
							$used = array_map( 'strtolower', $order->get_coupon_codes() );
							if ( ! in_array( $coupon, $used, true ) ) {
								return false;
							}
						}
						if ( null !== $total_min && $order->get_total() < $total_min ) {
							return false;
						}
						if ( null !== $total_max && $order->get_total() > $total_max ) {
							return false;
						}
						if ( $shipping_method ) {
							$matched = false;
							foreach ( $order->get_items( 'shipping' ) as $item ) {
								if ( $item->get_method_id() . ':' . $item->get_instance_id() === $shipping_method ) {
									$matched = true;
									break;
								}
							}
							if ( ! $matched ) {
								return false;
							}
						}
						if ( $meta_key ) {
							$val = $order->get_meta( $meta_key );
							if ( $meta_val && (string) $val !== $meta_val ) {
								return false;
							}
							if ( ! $meta_val && '' === (string) $val ) {
								return false;
							}
						}
						if ( ! empty( $filter_product_ids ) || ! empty( $products_in_categories ) ) {
							$order_product_ids = array();
							foreach ( $order->get_items() as $item ) {
								$pid = (int) $item->get_product_id();
								if ( $pid ) {
									$order_product_ids[] = $pid;
								}
							}
							if ( ! empty( $filter_product_ids ) && ! array_intersect( $filter_product_ids, $order_product_ids ) ) {
								return false;
							}
							if ( ! empty( $products_in_categories ) && ! array_intersect( $products_in_categories, $order_product_ids ) ) {
								return false;
							}
						}
						return true;
					}
				)
			);
		}

		return array( $orders, count( $orders ) );
	}

	/**
	 * Query WP users (customers) with filters.
	 *
	 * Supports two opt-in toggles via $filters:
	 *   - include_guests          (bool) — add guest customers sourced from order billing data.
	 *   - include_all_purchasers  (bool) — include registered users of any role who have ordered,
	 *                                      not just those with the customer/subscriber role.
	 *
	 * @param array $filters Filters array.
	 * @param int   $max     Max rows (0 = unlimited).
	 * @return array { 0: (WP_User|\stdClass)[], 1: int }
	 */
	private function query_customers( array $filters, int $max ): array {
		$include_guests         = ! empty( $filters['include_guests'] );
		$include_all_purchasers = ! empty( $filters['include_all_purchasers'] );

		// ── Registered-user query ─────────────────────────────────────────────
		if ( $include_all_purchasers ) {
			// Collect every distinct registered customer ID from order history.
			$purchaser_ids = $this->get_all_order_customer_ids();
			$args          = array(
				'include' => ( ! empty( $purchaser_ids ) ) ? $purchaser_ids : array( -1 ), // -1 avoids returning all users on empty
				'number'  => -1,
				'orderby' => 'registered',
				'order'   => 'DESC',
				'fields'  => 'all',
			);
		} else {
			$args = array(
				'role__in' => array( 'customer', 'subscriber' ),
				'number'   => $max,
				'orderby'  => 'registered',
				'order'    => 'DESC',
				'fields'   => 'all',
			);
		}

		$date_from = $this->clean_date( $filters['date_from'] ?? '' );
		$date_to   = $this->clean_date( $filters['date_to'] ?? '' );
		if ( $date_from || $date_to ) {
			$args['date_query'] = array( 'inclusive' => true );
			if ( $date_from ) {
				$args['date_query']['after'] = $date_from;
			}
			if ( $date_to ) {
				$args['date_query']['before'] = $date_to;
			}
		}

		$users = get_users( $args );

		// ── Guest customers ───────────────────────────────────────────────────
		// Guest date filter applies to order creation date (no registration date).
		$guests = $include_guests ? $this->get_guest_customers( $date_from, $date_to ) : array();

		// ── Merge and post-filter ─────────────────────────────────────────────
		$all       = array_merge( $users, $guests );
		$min_ord   = isset( $filters['min_orders'] ) && '' !== $filters['min_orders'] ? (int) $filters['min_orders'] : null;
		$max_ord   = isset( $filters['max_orders'] ) && '' !== $filters['max_orders'] ? (int) $filters['max_orders'] : null;
		$min_spent = isset( $filters['min_spent'] ) && '' !== $filters['min_spent'] ? (float) $filters['min_spent'] : null;
		$max_spent = isset( $filters['max_spent'] ) && '' !== $filters['max_spent'] ? (float) $filters['max_spent'] : null;

		if ( null !== $min_ord || null !== $max_ord || null !== $min_spent || null !== $max_spent ) {
			$all = array_values(
				array_filter(
					$all,
					function ( $user ) use ( $min_ord, $max_ord, $min_spent, $max_spent ) {
						if ( $user instanceof \WP_User ) {
							$count = wc_get_customer_order_count( $user->ID );
							$spent = (float) wc_get_customer_total_spent( $user->ID );
						} else {
							$count = $user->_total_orders;
							$spent = $user->_total_spent;
						}
						if ( null !== $min_ord && $count < $min_ord ) {
							return false;
						}
						if ( null !== $max_ord && $count > $max_ord ) {
							return false;
						}
						if ( null !== $min_spent && $spent < $min_spent ) {
							return false;
						}
						if ( null !== $max_spent && $spent > $max_spent ) {
							return false;
						}
						return true;
					}
				)
			);
		}

		// Apply max-rows cap after merging/filtering.
		if ( $max > 0 && count( $all ) > $max ) {
			$all = array_slice( $all, 0, $max );
		}

		return array( $all, count( $all ) );
	}

	/**
	 * Query WooCommerce products with filters.
	 *
	 * @param array $filters Filters array.
	 * @param int   $max     Max rows.
	 * @return array [ WC_Product[], count ]
	 */
	private function query_products( array $filters, int $max ): array {
		$args = array(
			'limit'   => $max,
			'orderby' => 'date',
			'order'   => 'DESC',
			'return'  => 'objects',
			'status'  => 'any',
		);

		$statuses = (array) ( $filters['statuses'] ?? array() );
		if ( ! empty( $statuses ) ) {
			$allowed = array( 'publish', 'draft', 'pending', 'private' );
			$cleaned = array_values( array_filter( array_map( 'sanitize_text_field', $statuses ), fn( $s ) => in_array( $s, $allowed, true ) ) );
			if ( ! empty( $cleaned ) ) {
				$args['status'] = $cleaned;
			}
		}

		$product_type = sanitize_key( $filters['product_type'] ?? '' );
		if ( $product_type ) {
			$args['type'] = $product_type;
		}

		$stock_status = sanitize_key( $filters['stock_status'] ?? '' );
		if ( $stock_status ) {
			$args['stock_status'] = $stock_status;
		}

		$category_ids = array_map( 'intval', (array) ( $filters['category_ids'] ?? array() ) );
		if ( ! empty( $category_ids ) ) {
			$slugs = array();
			foreach ( $category_ids as $id ) {
				$term = get_term( $id, 'product_cat' );
				if ( $term && ! is_wp_error( $term ) ) {
					$slugs[] = $term->slug;
				}
			}
			if ( ! empty( $slugs ) ) {
				$args['category'] = $slugs;
			}
		}

		$products = wc_get_products( $args );
		return array( $products, count( $products ) );
	}

	// ─── Value Extraction ─────────────────────────────────────────────────────

	/**
	 * Dispatch value extraction to the correct type handler.
	 *
	 * @param string $type  Report type.
	 * @param mixed  $item  WC_Order|WP_User|WC_Product.
	 * @param string $key   Column key.
	 * @return string
	 */
	private function get_value( string $type, $item, string $key ): string {
		switch ( $type ) {
			case 'customers':
				return $this->get_customer_value( $item, $key );
			case 'products':
				return $this->get_product_value( $item, $key );
			default:
				return $this->get_order_value( $item, $key );
		}
	}

	/**
	 * Extract a value from a WC_Order for the given column key.
	 *
	 * @param \WC_Order $order Order object.
	 * @param string    $key   Column key.
	 * @return string
	 */
	private function get_order_value( \WC_Order $order, string $key ): string {
		if ( 0 === strpos( $key, 'meta:' ) ) {
			return (string) $order->get_meta( substr( $key, 5 ) );
		}
		switch ( $key ) {
			// Order.
			case 'order_id':
				return (string) $order->get_id();
			case 'date':
				$d = $order->get_date_created();
				return $d ? $d->date( 'Y-m-d H:i:s' ) : '';
			case 'status':
				return wc_get_order_status_name( $order->get_status() );
			case 'currency':
				return $order->get_currency();
			case 'customer_note':
				return $order->get_customer_note();
			case 'transaction_id':
				return $order->get_transaction_id();
			// Billing.
			case 'billing_first_name':
				return $order->get_billing_first_name();
			case 'billing_last_name':
				return $order->get_billing_last_name();
			case 'billing_email':
				return $order->get_billing_email();
			case 'billing_phone':
				return $order->get_billing_phone();
			case 'billing_company':
				return $order->get_billing_company();
			case 'billing_address_1':
				return $order->get_billing_address_1();
			case 'billing_address_2':
				return $order->get_billing_address_2();
			case 'billing_city':
				return $order->get_billing_city();
			case 'billing_state':
				return $order->get_billing_state();
			case 'billing_postcode':
				return $order->get_billing_postcode();
			case 'billing_country':
				return $order->get_billing_country();
			case 'billing_address':
				return implode( ', ', array_filter( array( $order->get_billing_address_1(), $order->get_billing_address_2(), $order->get_billing_city(), $order->get_billing_state(), $order->get_billing_postcode(), $order->get_billing_country() ) ) );
			// Shipping.
			case 'shipping_first_name':
				return $order->get_shipping_first_name();
			case 'shipping_last_name':
				return $order->get_shipping_last_name();
			case 'shipping_company':
				return $order->get_shipping_company();
			case 'shipping_address_1':
				return $order->get_shipping_address_1();
			case 'shipping_address_2':
				return $order->get_shipping_address_2();
			case 'shipping_city':
				return $order->get_shipping_city();
			case 'shipping_state':
				return $order->get_shipping_state();
			case 'shipping_postcode':
				return $order->get_shipping_postcode();
			case 'shipping_country':
				return $order->get_shipping_country();
			case 'shipping_address':
				return implode( ', ', array_filter( array( $order->get_shipping_address_1(), $order->get_shipping_address_2(), $order->get_shipping_city(), $order->get_shipping_state(), $order->get_shipping_postcode(), $order->get_shipping_country() ) ) );
			case 'shipping_method':
				$methods = array();
				foreach ( $order->get_items( 'shipping' ) as $s ) {
					$methods[] = $s->get_name();
				}
				return implode( ', ', $methods );
			// Line Items.
			case 'items':
				$items = array();
				foreach ( $order->get_items() as $item ) {
					$items[] = $item->get_name() . ' ×' . $item->get_quantity();
				}
				return implode( '; ', $items );
			case 'items_count':
				return (string) $order->get_item_count();
			case 'items_skus':
				$skus = array();
				foreach ( $order->get_items() as $item ) {
					$p = $item->get_product();
					if ( $p ) {
						$skus[] = $p->get_sku();
					}
				}
				return implode( '; ', array_filter( $skus ) );
			case 'items_with_variations':
				$items = array();
				foreach ( $order->get_items() as $item ) {
					$meta  = $item->get_formatted_meta_data( '' );
					$attrs = array();
					foreach ( $meta as $m ) {
						if ( '' !== $m->key && '_' !== $m->key[0] ) {
							$attrs[] = $m->display_key . ': ' . wp_strip_all_tags( $m->display_value );
						}
					}
					$s = $item->get_name() . ' ×' . $item->get_quantity();
					if ( ! empty( $attrs ) ) {
						$s .= ' (' . implode( ', ', $attrs ) . ')';
					}
					$items[] = $s;
				}
				return implode( '; ', $items );
			// Totals.
			case 'payment_method':
				return $order->get_payment_method_title();
			case 'subtotal':
				return (string) $order->get_subtotal();
			case 'discount_total':
				return (string) $order->get_discount_total();
			case 'shipping_cost':
				return (string) $order->get_shipping_total();
			case 'fee_total':
				$fee = 0.0;
				foreach ( $order->get_items( 'fee' ) as $f ) {
					$fee += (float) $f->get_total();
				}
				return (string) $fee;
			case 'tax_total':
				return (string) $order->get_total_tax();
			case 'total':
				return (string) $order->get_total();
			case 'tax_lines':
				$taxes = array();
				foreach ( $order->get_taxes() as $tax ) {
					$taxes[] = $tax->get_label() . ': ' . $tax->get_tax_total();
				}
				return implode( '; ', $taxes );
			// Coupons.
			case 'coupons':
				return implode( ', ', $order->get_coupon_codes() );
			case 'coupon_discount':
				return (string) $order->get_discount_total();
			// Customer.
			case 'customer_id':
				return (string) $order->get_customer_id();
			case 'customer_username':
				$uid  = $order->get_customer_id();
				$user = $uid ? get_user_by( 'id', $uid ) : null;
				return $user ? $user->user_login : '';
			default:
				return '';
		}
	}

	/**
	 * Extract a value from a WP_User (or guest stdClass) for the given column key.
	 *
	 * @param \WP_User|\stdClass $user WP user object or guest customer stdClass.
	 * @param string             $key  Column key.
	 * @return string
	 */
	private function get_customer_value( $user, string $key ): string {
		// Guest customer — delegate to dedicated extractor.
		if ( $user instanceof \stdClass && ! empty( $user->is_guest ) ) {
			return $this->get_guest_customer_value( $user, $key );
		}

		if ( 0 === strpos( $key, 'meta:' ) ) {
			return (string) get_user_meta( $user->ID, substr( $key, 5 ), true );
		}
		$customer = new \WC_Customer( $user->ID );
		switch ( $key ) {
			case 'customer_id':
				return (string) $user->ID;
			case 'username':
				return $user->user_login;
			case 'first_name':
				return ( '' !== $customer->get_first_name() ) ? $customer->get_first_name() : $user->first_name;
			case 'last_name':
				return ( '' !== $customer->get_last_name() ) ? $customer->get_last_name() : $user->last_name;
			case 'name':
				$fn = ( '' !== $customer->get_first_name() ) ? $customer->get_first_name() : $user->first_name;
				$ln = ( '' !== $customer->get_last_name() ) ? $customer->get_last_name() : $user->last_name;
				return trim( $fn . ' ' . $ln );
			case 'email':
				return $user->user_email;
			case 'phone':
				return $customer->get_billing_phone();
			case 'user_role':
				$roles = (array) $user->roles;
				return implode( ', ', $roles );
			case 'billing_company':
				return $customer->get_billing_company();
			case 'billing_address':
				return $customer->get_billing_address_1();
			case 'billing_city':
				return $customer->get_billing_city();
			case 'billing_state':
				return $customer->get_billing_state();
			case 'billing_postcode':
				return $customer->get_billing_postcode();
			case 'billing_country':
				return $customer->get_billing_country();
			case 'registered':
				return $user->user_registered;
			case 'total_orders':
				return (string) wc_get_customer_order_count( $user->ID );
			case 'total_spent':
				return (string) wc_get_customer_total_spent( $user->ID );
			case 'average_order_value':
				$count = wc_get_customer_order_count( $user->ID );
				$spent = (float) wc_get_customer_total_spent( $user->ID );
				return $count > 0 ? (string) round( $spent / $count, 2 ) : '0';
			case 'last_order_date':
				$lo = wc_get_customer_last_order( $user->ID );
				if ( $lo ) {
					$d = $lo->get_date_created();
					return $d ? $d->date( 'Y-m-d' ) : '';
				}
				return '';
			case 'last_order_amount':
				$lo = wc_get_customer_last_order( $user->ID );
				return $lo ? (string) $lo->get_total() : '';
			default:
				return '';
		}
	}

	/**
	 * Extract a value from a WC_Product for the given column key.
	 *
	 * @param \WC_Product $product Product object.
	 * @param string      $key     Column key.
	 * @return string
	 */
	private function get_product_value( \WC_Product $product, string $key ): string {
		if ( 0 === strpos( $key, 'meta:' ) ) {
			return (string) $product->get_meta( substr( $key, 5 ) );
		}
		switch ( $key ) {
			case 'product_id':
				return (string) $product->get_id();
			case 'sku':
				return $product->get_sku();
			case 'name':
				return $product->get_name();
			case 'type':
				return $product->get_type();
			case 'status':
				return $product->get_status();
			case 'visibility':
				return $product->get_catalog_visibility();
			case 'short_description':
				return wp_strip_all_tags( $product->get_short_description() );
			case 'description':
				return wp_strip_all_tags( $product->get_description() );
			case 'featured':
				return $product->is_featured() ? 'Yes' : 'No';
			case 'variations_count':
				return $product->is_type( 'variable' ) ? (string) count( $product->get_children() ) : '';
			case 'regular_price':
				return (string) $product->get_regular_price();
			case 'sale_price':
				return (string) $product->get_sale_price();
			case 'price':
				return (string) $product->get_price();
			case 'sale_start':
				$d = $product->get_date_on_sale_from();
				return $d ? $d->date( 'Y-m-d' ) : '';
			case 'sale_end':
				$d = $product->get_date_on_sale_to();
				return $d ? $d->date( 'Y-m-d' ) : '';
			case 'stock_status':
				$opts = wc_get_product_stock_status_options();
				return $opts[ $product->get_stock_status() ] ?? $product->get_stock_status();
			case 'stock_quantity':
				return $product->managing_stock() ? (string) $product->get_stock_quantity() : '';
			case 'backorders':
				return $product->get_backorders();
			case 'low_stock_amount':
				return (string) $product->get_low_stock_amount();
			case 'categories':
				$cats = get_the_terms( $product->get_id(), 'product_cat' );
				return ( $cats && ! is_wp_error( $cats ) ) ? implode( ', ', wp_list_pluck( $cats, 'name' ) ) : '';
			case 'tags':
				$tags = get_the_terms( $product->get_id(), 'product_tag' );
				return ( $tags && ! is_wp_error( $tags ) ) ? implode( ', ', wp_list_pluck( $tags, 'name' ) ) : '';
			case 'weight':
				return $product->get_weight();
			case 'dimensions':
				$l = $product->get_length();
				$w = $product->get_width();
				$h = $product->get_height();
				return ( $l || $w || $h ) ? "{$l}×{$w}×{$h}" : '';
			case 'reviews_allowed':
				return $product->get_reviews_allowed() ? 'Yes' : 'No';
			case 'date_created':
				$d = $product->get_date_created();
				return $d ? $d->date( 'Y-m-d' ) : '';
			case 'date_modified':
				$d = $product->get_date_modified();
				return $d ? $d->date( 'Y-m-d' ) : '';
			default:
				return '';
		}
	}

	// ─── Format Output ────────────────────────────────────────────────────────

	/**
	 * Build a CSV string from headers and rows.
	 *
	 * @param string[] $headers Column header labels.
	 * @param array[]  $rows    Data rows.
	 * @return string
	 */
	private function to_csv( array $headers, array $rows ): string {
		$handle = fopen( 'php://temp', 'r+' ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fopen
		fputcsv( $handle, $headers );
		foreach ( $rows as $row ) {
			fputcsv( $handle, $row );
		}
		rewind( $handle );
		$csv = stream_get_contents( $handle );
		fclose( $handle ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose
		return $csv;
	}

	/**
	 * Build a proper XLSX file (ZIP+XML) with frozen header row and estimated
	 * column widths. Falls back to CSV if ZipArchive is unavailable.
	 *
	 * @param string[] $headers Column header labels.
	 * @param array[]  $rows    Data rows.
	 * @return string Binary XLSX content.
	 */
	private function to_xlsx( array $headers, array $rows ): string {
		if ( ! class_exists( 'ZipArchive' ) ) {
			return $this->to_csv( $headers, $rows );
		}

		$tmp = tempnam( sys_get_temp_dir(), 'jwp_stk_xlsx_' );
		$zip = new \ZipArchive();

		if ( true !== $zip->open( $tmp, \ZipArchive::OVERWRITE | \ZipArchive::CREATE ) ) {
			return $this->to_csv( $headers, $rows );
		}

		$all_rows = array_merge( array( $headers ), $rows );
		$num_cols = count( $headers );

		// Estimate column widths from max content length (capped at 60).
		$widths = array_fill( 0, $num_cols, 8 );
		foreach ( $all_rows as $row ) {
			foreach ( $row as $ci => $cell ) {
				$len = mb_strlen( (string) $cell );
				if ( isset( $widths[ $ci ] ) && $len + 2 > $widths[ $ci ] ) {
					$widths[ $ci ] = min( $len + 2, 60 );
				}
			}
		}

		$zip->addFromString( '[Content_Types].xml', $this->xlsx_content_types() );
		$zip->addFromString( '_rels/.rels', $this->xlsx_root_rels() );
		$zip->addFromString( 'xl/workbook.xml', $this->xlsx_workbook() );
		$zip->addFromString( 'xl/_rels/workbook.xml.rels', $this->xlsx_workbook_rels() );
		$zip->addFromString( 'xl/styles.xml', $this->xlsx_styles() );
		$zip->addFromString( 'xl/worksheets/sheet1.xml', $this->xlsx_sheet( $all_rows, $widths, $num_cols ) );
		$zip->close();

		$content = file_get_contents( $tmp ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_get_contents_file_get_contents
		wp_delete_file( $tmp );
		return ( false !== $content ) ? $content : '';
	}

	/**
	 * Build the [Content_Types].xml file for the XLSX package.
	 *
	 * @return string
	 */
	private function xlsx_content_types(): string {
		return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
			. '<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">'
			. '<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>'
			. '<Default Extension="xml" ContentType="application/xml"/>'
			. '<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>'
			. '<Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>'
			. '<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>'
			. '</Types>';
	}

	/**
	 * Build the _rels/.rels file for the XLSX package.
	 *
	 * @return string
	 */
	private function xlsx_root_rels(): string {
		return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
			. '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'
			. '<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>'
			. '</Relationships>';
	}

	/**
	 * Build the xl/workbook.xml file for the XLSX package.
	 *
	 * @return string
	 */
	private function xlsx_workbook(): string {
		return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
			. '<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">'
			. '<sheets><sheet name="Report" sheetId="1" r:id="rId1"/></sheets>'
			. '</workbook>';
	}

	/**
	 * Build the xl/_rels/workbook.xml.rels file for the XLSX package.
	 *
	 * @return string
	 */
	private function xlsx_workbook_rels(): string {
		return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
			. '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'
			. '<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>'
			. '<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>'
			. '</Relationships>';
	}

	/**
	 * Build the xl/styles.xml file for the XLSX package.
	 *
	 * Returns styles XML with normal (index 0) and bold header (index 1) cell formats.
	 *
	 * @return string
	 */
	private function xlsx_styles(): string {
		return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
			. '<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'
			. '<fonts count="2">'
			. '<font><name val="Calibri"/><sz val="11"/></font>'
			. '<font><b/><name val="Calibri"/><sz val="11"/></font>'
			. '</fonts>'
			. '<fills count="2">'
			. '<fill><patternFill patternType="none"/></fill>'
			. '<fill><patternFill patternType="gray125"/></fill>'
			. '</fills>'
			. '<borders count="1"><border><left/><right/><top/><bottom/><diagonal/></border></borders>'
			. '<cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs>'
			. '<cellXfs count="2">'
			. '<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>'
			. '<xf numFmtId="0" fontId="1" fillId="0" borderId="0" xfId="0"/>'
			. '</cellXfs>'
			. '</styleSheet>';
	}

	/**
	 * Build sheet1.xml with frozen header row and column widths.
	 *
	 * @param array $all_rows  All rows including headers as row 0.
	 * @param int[] $widths    Estimated column widths.
	 * @param int   $num_cols  Number of columns.
	 * @return string
	 */
	private function xlsx_sheet( array $all_rows, array $widths, int $num_cols ): string {
		// sheetViews: freeze row 1.
		$sheet_views = '<sheetViews><sheetView workbookViewId="0">'
			. '<pane ySplit="1" topLeftCell="A2" activePane="bottomLeft" state="frozen"/>'
			. '</sheetView></sheetViews>';

		// cols widths.
		$cols_xml = '<cols>';
		for ( $ci = 0; $ci < $num_cols; $ci++ ) {
			$n         = $ci + 1;
			$w         = $widths[ $ci ] ?? 12;
			$cols_xml .= sprintf( '<col min="%d" max="%d" width="%d" bestFit="1" customWidth="1"/>', $n, $n, $w );
		}
		$cols_xml .= '</cols>';

		// sheetData.
		$sheet_data = '<sheetData>';
		foreach ( $all_rows as $ri => $row ) {
			$row_num     = $ri + 1;
			$is_header   = ( 0 === $ri );
			$sheet_data .= sprintf( '<row r="%d">', $row_num );
			foreach ( $row as $ci => $cell ) {
				$col_letter  = $this->xlsx_col_letter( $ci );
				$cell_ref    = $col_letter . $row_num;
				$val         = $this->xlsx_esc( (string) $cell );
				$style       = $is_header ? ' s="1"' : '';
				$sheet_data .= sprintf( '<c r="%s"%s t="inlineStr"><is><t>%s</t></is></c>', $cell_ref, $style, $val );
			}
			$sheet_data .= '</row>';
		}
		$sheet_data .= '</sheetData>';

		return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
			. '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'
			. $sheet_views
			. $cols_xml
			. $sheet_data
			. '</worksheet>';
	}

	/**
	 * Convert 0-based column index to Excel letter(s) (A, B, …, Z, AA, AB …).
	 *
	 * @param int $index 0-based index.
	 * @return string
	 */
	private function xlsx_col_letter( int $index ): string {
		$letter = '';
		$n      = $index;
		do {
			$letter = chr( 65 + ( $n % 26 ) ) . $letter;
			$n      = (int) ( $n / 26 ) - 1;
		} while ( $n >= 0 );
		return $letter;
	}

	/**
	 * Escape a string for use as an XML text node value.
	 *
	 * @param string $s Raw string.
	 * @return string
	 */
	private function xlsx_esc( string $s ): string {
		return htmlspecialchars( $s, ENT_XML1 | ENT_COMPAT, 'UTF-8' );
	}

	/**
	 * Build an XML document from headers and rows.
	 *
	 * @param string   $type    Report type (orders|customers|products).
	 * @param string[] $headers Column header labels.
	 * @param array[]  $rows    Data rows.
	 * @return string XML string.
	 */
	private function to_xml( string $type, array $headers, array $rows ): string {
		$item_map = array(
			'orders'    => 'order',
			'customers' => 'customer',
			'products'  => 'product',
		);
		$root     = $type;
		$item_tag = $item_map[ $type ] ?? 'item';
		$tags     = array_map( array( $this, 'header_to_xml_tag' ), $headers );

		$xml = '<?xml version="1.0" encoding="UTF-8"?>' . "\n<{$root}>\n";
		foreach ( $rows as $row ) {
			$xml .= "  <{$item_tag}>\n";
			foreach ( $tags as $ti => $tag ) {
				$val  = htmlspecialchars( (string) ( $row[ $ti ] ?? '' ), ENT_XML1 | ENT_COMPAT, 'UTF-8' );
				$xml .= "    <{$tag}>{$val}</{$tag}>\n";
			}
			$xml .= "  </{$item_tag}>\n";
		}
		$xml .= "</{$root}>";
		return $xml;
	}

	/**
	 * Convert a header label to a safe XML element tag.
	 *
	 * @param string $header Column header.
	 * @return string
	 */
	private function header_to_xml_tag( string $header ): string {
		$tag = preg_replace( '/[^a-z0-9_]/', '', strtolower( str_replace( array( ' ', '-' ), '_', $header ) ) );
		if ( '' === $tag || is_numeric( $tag[0] ) ) {
			$tag = 'field_' . $tag;
		}
		return $tag;
	}

	/**
	 * Build a JSON array-of-objects from headers and rows.
	 *
	 * @param string[] $headers Column header labels.
	 * @param array[]  $rows    Data rows.
	 * @return string JSON string.
	 */
	private function to_json( array $headers, array $rows ): string {
		$result = array();
		foreach ( $rows as $row ) {
			$obj = array();
			foreach ( $headers as $hi => $header ) {
				$obj[ $header ] = $row[ $hi ] ?? '';
			}
			$result[] = $obj;
		}
		$encoded = wp_json_encode( $result, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE );
		return ( false !== $encoded ) ? $encoded : '[]';
	}

	// ─── Scheduled Reports ────────────────────────────────────────────────────

	/**
	 * Format a DB schedule row for JSON output.
	 *
	 * @param array $row Raw DB row.
	 * @return array
	 */
	private function format_schedule( array $row ): array {
		$payload = json_decode( $row['payload'], true );
		return array(
			'schedule_id'     => $row['schedule_id'],
			'title'           => $row['title'],
			'type'            => $row['type'],
			'frequency'       => $row['frequency'],
			'day_of_week'     => null !== $row['day_of_week'] ? (int) $row['day_of_week'] : null,
			'day_of_month'    => null !== $row['day_of_month'] ? (int) $row['day_of_month'] : null,
			'time_of_day'     => $row['time_of_day'],
			'format'          => $payload['format'] ?? 'csv',
			'columns'         => $payload['columns'] ?? array(),
			'filters'         => $payload['filters'] ?? array(),
			'status'          => $row['status'],
			'last_run_at'     => $row['last_run_at'],
			'last_run_status' => $row['last_run_status'],
			'next_run_at'     => $row['next_run_at'],
			'created_at'      => $row['created_at'],
			'user_id'         => (int) $row['user_id'],
		);
	}

	/**
	 * Register (or replace) an ActionScheduler single action for a scheduled report.
	 *
	 * Any previously-registered action for the same schedule_id is cancelled first
	 * to avoid double-firing.
	 *
	 * @param string $schedule_id   UUID of the schedule row.
	 * @param string $next_run_utc  UTC MySQL datetime ("Y-m-d H:i:s"), or empty to cancel only.
	 * @return void
	 */
	private function schedule_as_action( string $schedule_id, string $next_run_utc ): void {
		as_unschedule_action( 'jwp_stk_sr_run_single_schedule', array( 'schedule_id' => $schedule_id ), 'jwp-stk' );
		if ( empty( $next_run_utc ) ) {
			return;
		}
		$timestamp = strtotime( $next_run_utc );
		if ( $timestamp > 0 ) {
			as_schedule_single_action( $timestamp, 'jwp_stk_sr_run_single_schedule', array( 'schedule_id' => $schedule_id ), 'jwp-stk' );
		}
	}

	/**
	 * Cancel any pending ActionScheduler action for a scheduled report.
	 *
	 * @param string $schedule_id UUID of the schedule row.
	 * @return void
	 */
	private function unschedule_as_action( string $schedule_id ): void {
		as_unschedule_action( 'jwp_stk_sr_run_single_schedule', array( 'schedule_id' => $schedule_id ), 'jwp-stk' );
	}

	/**
	 * ActionScheduler handler — fires a single scheduled report at its exact due time.
	 *
	 * Called by the `jwp_stk_sr_run_single_schedule` AS action. Queues the background
	 * export job, updates last_run_at / next_run_at, then registers the next AS action.
	 *
	 * @param string $schedule_id UUID of the schedule row.
	 * @return void
	 */
	public function run_single_scheduled_report( string $schedule_id ): void {
		global $wpdb;

		$table = $wpdb->prefix . 'jwp_stk_schedules';
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$schedule = $wpdb->get_row(
			$wpdb->prepare( "SELECT * FROM {$table} WHERE schedule_id = %s AND status = 'active'", $schedule_id ), // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			ARRAY_A
		);

		if ( ! $schedule ) {
			return;
		}

		$now     = current_time( 'mysql', true );
		$payload = json_decode( $schedule['payload'], true );
		$jm      = \JWP_STK\Jobs\Job_Manager::get_instance();

		$job_id = $jm->queue_job(
			'store-reports',
			$schedule['type'],
			array(
				'format'      => $payload['format'] ?? 'csv',
				'columns'     => $payload['columns'] ?? array(),
				'filters'     => $payload['filters'] ?? array(),
				'schedule_id' => $schedule['schedule_id'],
				'title'       => $schedule['title'],
			),
			(int) $schedule['user_id']
		);

		$next_run   = $this->calculate_next_run(
			$schedule['frequency'],
			null !== $schedule['day_of_week'] ? (int) $schedule['day_of_week'] : null,
			null !== $schedule['day_of_month'] ? (int) $schedule['day_of_month'] : null,
			$schedule['time_of_day']
		);
		$run_status = is_wp_error( $job_id ) ? 'failed' : 'queued';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->update(
			$table,
			array(
				'last_run_at'     => $now,
				'last_run_status' => $run_status,
				'next_run_at'     => $next_run,
			),
			array( 'schedule_id' => $schedule_id ),
			array( '%s', '%s', '%s' ),
			array( '%s' )
		);

		// Schedule the next precise AS action.
		if ( $next_run ) {
			$this->schedule_as_action( $schedule_id, $next_run );
		}
	}

	/**
	 * Calculate the next UTC run datetime for a schedule.
	 *
	 * @param string   $frequency    daily|weekly|monthly.
	 * @param int|null $day_of_week  0=Sunday … 6=Saturday (for weekly).
	 * @param int|null $day_of_month 1–28 (for monthly).
	 * @param string   $time_of_day  HH:MM in site-local time.
	 * @return string UTC MySQL datetime, or '' on error.
	 */
	private function calculate_next_run( string $frequency, ?int $day_of_week, ?int $day_of_month, string $time_of_day ): string {
		$tz    = wp_timezone();
		$now   = new \DateTimeImmutable( 'now', $tz );
		$parts = explode( ':', $time_of_day );
		$hour  = max( 0, min( 23, (int) ( $parts[0] ?? 6 ) ) );
		$min   = max( 0, min( 59, (int) ( $parts[1] ?? 0 ) ) );

		switch ( $frequency ) {
			case 'daily':
				$candidate = $now->setTime( $hour, $min, 0 );
				if ( $candidate <= $now ) {
					$candidate = $candidate->modify( '+1 day' );
				}
				break;

			case 'weekly':
				$target_dow  = max( 0, min( 6, $day_of_week ?? 1 ) );
				$current_dow = (int) $now->format( 'w' ); // 0=Sunday
				$diff        = ( $target_dow - $current_dow + 7 ) % 7;
				$candidate   = $now->setTime( $hour, $min, 0 );
				if ( 0 === $diff && $candidate <= $now ) {
					$diff = 7;
				}
				if ( $diff > 0 ) {
					$candidate = $candidate->modify( "+{$diff} days" );
				}
				break;

			case 'bi-weekly':
				$target_dow  = max( 0, min( 6, $day_of_week ?? 1 ) );
				$current_dow = (int) $now->format( 'w' );
				$diff        = ( $target_dow - $current_dow + 7 ) % 7;
				$candidate   = $now->setTime( $hour, $min, 0 );
				if ( 0 === $diff && $candidate <= $now ) {
					$diff = 14;
				} elseif ( 0 === $diff ) { // phpcs:ignore Generic.CodeAnalysis.EmptyStatement
					// Same day but time not yet passed — run this week, next in 14 days.
				} else {
					$candidate = $candidate->modify( "+{$diff} days" );
					// Next occurrence is 14 days after the first upcoming target day.
					// We just return the first upcoming target day here.
				}
				if ( $diff >= 14 ) {
					$candidate = $now->setTime( $hour, $min, 0 )->modify( "+{$diff} days" );
				} elseif ( $diff > 0 ) {
					$candidate = $now->setTime( $hour, $min, 0 )->modify( "+{$diff} days" );
				}
				break;

			case 'monthly':
				$target_dom = max( 1, min( 28, $day_of_month ?? 1 ) );
				$candidate  = $now
					->setDate( (int) $now->format( 'Y' ), (int) $now->format( 'n' ), $target_dom )
					->setTime( $hour, $min, 0 );
				if ( $candidate <= $now ) {
					$candidate = $candidate->modify( '+1 month' );
				}
				break;

			case 'bi-monthly':
				$target_dom = max( 1, min( 28, $day_of_month ?? 1 ) );
				$candidate  = $now
					->setDate( (int) $now->format( 'Y' ), (int) $now->format( 'n' ), $target_dom )
					->setTime( $hour, $min, 0 );
				if ( $candidate <= $now ) {
					$candidate = $candidate->modify( '+2 months' );
				}
				break;

			default:
				return '';
		}

		return $candidate->setTimezone( new \DateTimeZone( 'UTC' ) )->format( 'Y-m-d H:i:s' );
	}

	/**
	 * REST: List all scheduled reports.
	 *
	 * @return \WP_REST_Response
	 */
	public function rest_get_schedules(): \WP_REST_Response {
		global $wpdb;

		$table = $wpdb->prefix . 'jwp_stk_schedules';
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$rows = $wpdb->get_results(
			"SELECT * FROM {$table} WHERE module = 'store-reports' ORDER BY created_at DESC", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
			ARRAY_A
		);

		$schedules = array_map( array( $this, 'format_schedule' ), ( ! empty( $rows ) ) ? $rows : array() );
		return new \WP_REST_Response( $schedules, 200 );
	}

	/**
	 * REST: Create a new scheduled report.
	 *
	 * @param \WP_REST_Request $request Request object.
	 * @return \WP_REST_Response|\WP_Error
	 */
	public function rest_create_schedule( \WP_REST_Request $request ) {
		global $wpdb;

		$table        = $wpdb->prefix . 'jwp_stk_schedules';
		$title        = $request->get_param( 'title' );
		$type         = $request->get_param( 'type' );
		$frequency    = $request->get_param( 'frequency' );
		$time_of_day  = $request->get_param( 'time_of_day' ) ?? '06:00';
		$day_of_week  = $request->get_param( 'day_of_week' );
		$day_of_month = $request->get_param( 'day_of_month' );
		$format       = $request->get_param( 'format' ) ?? 'csv';
		$raw_cols     = (array) $request->get_param( 'columns' );
		$filters      = (array) $request->get_param( 'filters' );

		// Validate time format HH:MM.
		if ( ! preg_match( '/^\d{2}:\d{2}$/', $time_of_day ) ) {
			$time_of_day = '06:00';
		}

		// Whitelist columns.
		$allowed = $this->allowed_column_keys( $type );
		$columns = array();
		foreach ( $raw_cols as $col ) {
			$key   = sanitize_text_field( $col['key'] ?? '' );
			$label = sanitize_text_field( $col['label'] ?? '' );
			if ( $this->is_valid_column_key( $key, $allowed ) ) {
				$columns[] = array(
					'key'   => $key,
					'label' => ( '' !== $label ) ? $label : $this->get_default_label( $type, $key ),
				);
			}
		}
		if ( empty( $columns ) ) {
			$columns = $this->default_columns( $type );
		}

		$schedule_id  = wp_generate_uuid4();
		$next_run     = $this->calculate_next_run(
			$frequency,
			null !== $day_of_week ? (int) $day_of_week : null,
			null !== $day_of_month ? (int) $day_of_month : null,
			$time_of_day
		);
		$now          = current_time( 'mysql', true );
		$payload_json = wp_json_encode(
			array(
				'format'  => $format,
				'columns' => $columns,
				'filters' => $filters,
			)
		);

		$data    = array(
			'schedule_id' => $schedule_id,
			'module'      => 'store-reports',
			'type'        => $type,
			'title'       => $title,
			'frequency'   => $frequency,
			'time_of_day' => $time_of_day,
			'payload'     => $payload_json,
			'status'      => 'active',
			'next_run_at' => $next_run,
			'created_at'  => $now,
			'user_id'     => get_current_user_id(),
		);
		$formats = array( '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d' );

		if ( null !== $day_of_week ) {
			$data['day_of_week'] = (int) $day_of_week;
			$formats[]           = '%d';
		}
		if ( null !== $day_of_month ) {
			$data['day_of_month'] = (int) $day_of_month;
			$formats[]            = '%d';
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->insert( $table, $data, $formats );

		if ( $wpdb->last_error ) {
			return new \WP_Error(
				'jwp_stk_sr_db_error',
				__( 'Failed to save schedule.', 'jetix-store-toolkit' ),
				array( 'status' => 500 )
			);
		}

		$this->schedule_as_action( $schedule_id, $next_run );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$row = $wpdb->get_row(
			$wpdb->prepare( "SELECT * FROM {$table} WHERE schedule_id = %s", $schedule_id ), // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			ARRAY_A
		);

		return new \WP_REST_Response( $this->format_schedule( $row ), 201 );
	}

	/**
	 * REST: Update a scheduled report (pause/resume/rename).
	 *
	 * @param \WP_REST_Request $request Request object.
	 * @return \WP_REST_Response|\WP_Error
	 */
	public function rest_update_schedule( \WP_REST_Request $request ) {
		global $wpdb;

		$table       = $wpdb->prefix . 'jwp_stk_schedules';
		$schedule_id = $request->get_param( 'schedule_id' );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$existing = $wpdb->get_row(
			$wpdb->prepare( "SELECT * FROM {$table} WHERE schedule_id = %s", $schedule_id ), // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			ARRAY_A
		);
		if ( ! $existing ) {
			return new \WP_Error(
				'jwp_stk_sr_not_found',
				__( 'Schedule not found.', 'jetix-store-toolkit' ),
				array( 'status' => 404 )
			);
		}

		$update  = array();
		$formats = array();

		$status = $request->get_param( 'status' );
		if ( null !== $status ) {
			$update['status'] = $status;
			$formats[]        = '%s';
			// Recalculate next_run_at when re-activating.
			if ( 'active' === $status ) {
				$update['next_run_at'] = $this->calculate_next_run(
					$existing['frequency'],
					null !== $existing['day_of_week'] ? (int) $existing['day_of_week'] : null,
					null !== $existing['day_of_month'] ? (int) $existing['day_of_month'] : null,
					$existing['time_of_day']
				);
				$formats[]             = '%s';
			}
		}

		$title = $request->get_param( 'title' );
		if ( null !== $title ) {
			$update['title'] = sanitize_text_field( $title );
			$formats[]       = '%s';
		}

		if ( empty( $update ) ) {
			return new \WP_Error(
				'jwp_stk_sr_no_changes',
				__( 'No changes provided.', 'jetix-store-toolkit' ),
				array( 'status' => 400 )
			);
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->update( $table, $update, array( 'schedule_id' => $schedule_id ), $formats, array( '%s' ) );

		// Sync ActionScheduler action with the new status / next_run_at.
		if ( isset( $update['status'] ) ) {
			if ( 'paused' === $update['status'] ) {
				$this->unschedule_as_action( $schedule_id );
			} elseif ( 'active' === $update['status'] && ! empty( $update['next_run_at'] ) ) {
				$this->schedule_as_action( $schedule_id, $update['next_run_at'] );
			}
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$updated = $wpdb->get_row(
			$wpdb->prepare( "SELECT * FROM {$table} WHERE schedule_id = %s", $schedule_id ), // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			ARRAY_A
		);
		return new \WP_REST_Response( $this->format_schedule( $updated ), 200 );
	}

	/**
	 * REST: Delete a scheduled report.
	 *
	 * @param \WP_REST_Request $request Request object.
	 * @return \WP_REST_Response|\WP_Error
	 */
	public function rest_delete_schedule( \WP_REST_Request $request ) {
		global $wpdb;

		$table       = $wpdb->prefix . 'jwp_stk_schedules';
		$schedule_id = $request->get_param( 'schedule_id' );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$existing = $wpdb->get_row(
			$wpdb->prepare( "SELECT id FROM {$table} WHERE schedule_id = %s", $schedule_id ), // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			ARRAY_A
		);
		if ( ! $existing ) {
			return new \WP_Error(
				'jwp_stk_sr_not_found',
				__( 'Schedule not found.', 'jetix-store-toolkit' ),
				array( 'status' => 404 )
			);
		}

		$this->unschedule_as_action( $schedule_id );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->delete( $table, array( 'schedule_id' => $schedule_id ), array( '%s' ) );
		return new \WP_REST_Response( array( 'deleted' => true ), 200 );
	}

	/**
	 * Cron handler — fires due scheduled reports.
	 *
	 * Runs hourly via the jwp_stk_sr_process_schedules event and queues
	 * a background job for each active schedule whose next_run_at has passed.
	 *
	 * @return void
	 */
	public function process_scheduled_reports(): void {
		global $wpdb;

		$table = $wpdb->prefix . 'jwp_stk_schedules';
		$now   = current_time( 'mysql', true ); // UTC.

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$schedules = $wpdb->get_results(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				"SELECT * FROM {$table} WHERE status = 'active' AND next_run_at IS NOT NULL AND next_run_at <= %s",
				$now
			),
			ARRAY_A
		);

		if ( empty( $schedules ) ) {
			return;
		}

		$jm = \JWP_STK\Jobs\Job_Manager::get_instance();

		foreach ( $schedules as $schedule ) {
			$payload = json_decode( $schedule['payload'], true );
			$format  = $payload['format'] ?? 'csv';
			$columns = $payload['columns'] ?? array();
			$filters = $payload['filters'] ?? array();

			$job_id = $jm->queue_job(
				'store-reports',
				$schedule['type'],
				array(
					'format'      => $format,
					'columns'     => $columns,
					'filters'     => $filters,
					'schedule_id' => $schedule['schedule_id'],
					'title'       => $schedule['title'],
				),
				(int) $schedule['user_id']
			);

			$next_run   = $this->calculate_next_run(
				$schedule['frequency'],
				null !== $schedule['day_of_week'] ? (int) $schedule['day_of_week'] : null,
				null !== $schedule['day_of_month'] ? (int) $schedule['day_of_month'] : null,
				$schedule['time_of_day']
			);
			$run_status = is_wp_error( $job_id ) ? 'failed' : 'queued';

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$wpdb->update(
				$table,
				array(
					'last_run_at'     => $now,
					'last_run_status' => $run_status,
					'next_run_at'     => $next_run,
				),
				array( 'schedule_id' => $schedule['schedule_id'] ),
				array( '%s', '%s', '%s' ),
				array( '%s' )
			);

			// Ensure the next run has a precise ActionScheduler action.
			if ( $next_run ) {
				$this->schedule_as_action( $schedule['schedule_id'], $next_run );
			}
		}
	}

	// ─── Preset Storage ───────────────────────────────────────────────────────

	/**
	 * Retrieve all saved preset data from the database.
	 *
	 * @return array
	 */
	private function get_presets_data(): array {
		$raw = get_option( 'jwp_stk_store_reports_presets', null );
		if ( ! is_array( $raw ) ) {
			return array(
				'column_presets' => array(),
				'filter_presets' => array(),
			);
		}
		return array(
			'column_presets' => (array) ( $raw['column_presets'] ?? array() ),
			'filter_presets' => (array) ( $raw['filter_presets'] ?? array() ),
		);
	}

	/**
	 * Persist preset data to the database.
	 *
	 * @param array $data Presets data array.
	 * @return void
	 */
	private function save_presets_data( array $data ): void {
		update_option( 'jwp_stk_store_reports_presets', $data, false );
	}

	// ─── Background Job Handler ─────────────────────────────────────────────────

	/**
	 * Queue a background report generation job via the global Job_Manager.
	 *
	 * Validates and whitelists columns before queuing so the handler receives clean data.
	 *
	 * @param \WP_REST_Request $request Request object.
	 * @return \WP_REST_Response|\WP_Error
	 */
	public function rest_generate( \WP_REST_Request $request ) {
		$type     = $request->get_param( 'type' );
		$format   = $request->get_param( 'format' );
		$title    = sanitize_text_field( $request->get_param( 'title' ) ?? '' );
		$raw_cols = (array) $request->get_param( 'columns' );
		$filters  = (array) $request->get_param( 'filters' );

		// Validate and whitelist columns.
		$allowed = $this->allowed_column_keys( $type );
		$columns = array();
		foreach ( $raw_cols as $col ) {
			$key   = sanitize_text_field( $col['key'] ?? '' );
			$label = sanitize_text_field( $col['label'] ?? '' );
			if ( $this->is_valid_column_key( $key, $allowed ) ) {
				$default_label = $this->get_default_label( $type, $key );
				$columns[]     = array(
					'key'   => $key,
					'label' => ( '' !== $label ) ? $label : $default_label,
				);
			}
		}
		if ( empty( $columns ) ) {
			$columns = $this->default_columns( $type );
		}

		$job_id = \JWP_STK\Jobs\Job_Manager::get_instance()->queue_job(
			'store-reports',
			$type,
			array(
				'format'  => $format,
				'columns' => $columns,
				'filters' => $filters,
				'title'   => $title,
			),
			get_current_user_id()
		);

		if ( is_wp_error( $job_id ) ) {
			return $job_id;
		}

		return new \WP_REST_Response( array( 'job_id' => $job_id ), 201 );
	}

	/**
	 * Handle a store-reports background job dispatched by the global Job_Manager.
	 *
	 * Called by Job_Manager::process_job() — never directly.
	 *
	 * @param array    $job    Full DB row as assoc array; $job['payload'] is JSON-decoded.
	 * @param callable $update Progress callback: $update( int $progress, ?string $file_path = null, ?int $total_rows = null ).
	 * @return void
	 * @throws \JWP_STK\Jobs\JobChainedException When chaining to the next job phase.
	 */
	public function handle_report_job( array $job, callable $update ): void {
		$job_id   = $job['job_id'];
		$type     = $job['type'];
		$payload  = $job['payload'];
		$format   = $payload['format'] ?? 'csv';
		$columns  = $payload['columns'] ?? array();
		$filters  = $payload['filters'] ?? array();
		$settings = $this->get_settings();
		$max_rows = max( 1, min( 50000, (int) ( $settings['max_export_rows'] ?? 2000 ) ) );

		if ( empty( $columns ) ) {
			$columns = $this->default_columns( $type );
		}

		// ── Phase 1: Query data ───────────────────────────────────────────────
		switch ( $type ) {
			case 'customers':
				list( $data, $count ) = $this->query_customers( $filters, $max_rows );
				break;
			case 'products':
				list( $data, $count ) = $this->query_products( $filters, $max_rows );
				break;
			default:
				list( $data, $count ) = $this->query_orders( $filters, $max_rows );
				break;
		}

		$update( 25, $count );

		// Store queried data for step 2.
		set_transient(
			'jwp_stk_sr_data_' . $job_id,
			array(
				'type'        => $type,
				'format'      => $format,
				'columns'     => $columns,
				'data'        => $data,
				'count'       => $count,
				'created_at'  => $job['created_at'],
				'schedule_id' => $payload['schedule_id'] ?? null,
				'title'       => $payload['title'] ?? null,
			),
			HOUR_IN_SECONDS
		);

		// Schedule step 2 and hand off — tell Job_Manager not to auto-complete.
		as_enqueue_async_action( 'jwp_stk_sr_build_rows', array( 'job_id' => $job_id ), 'jwp-stk' );
		throw new \JWP_STK\Jobs\JobChainedException();
	}

	/**
	 * Step 2: Build row arrays from queried data and store for step 3.
	 * Triggered by the 'jwp_stk_sr_build_rows' AS action.
	 *
	 * @param string $job_id Job UUID.
	 * @return void
	 */
	public function handle_build_rows( string $job_id ): void {
		$jm = \JWP_STK\Jobs\Job_Manager::get_instance();

		if ( $jm->is_cancelled( $job_id ) ) {
			return;
		}

		$transient = get_transient( 'jwp_stk_sr_data_' . $job_id );
		if ( false === $transient ) {
			$jm->fail_job( $job_id, 'Step 2: queried data transient expired or missing.' );
			return;
		}

		$type    = $transient['type'];
		$columns = $transient['columns'];
		$data    = $transient['data'];
		$headers = array_column( $columns, 'label' );
		$keys    = array_column( $columns, 'key' );
		$rows    = array();

		foreach ( $data as $item ) {
			$row_data = array();
			foreach ( $keys as $key ) {
				$row_data[] = $this->sanitize_cell_value( $this->get_value( $type, $item, $key ) );
			}
			$rows[] = $row_data;
		}

		$jm->update_progress( $job_id, 65 );

		// Store built rows for step 3.
		set_transient(
			'jwp_stk_sr_rows_' . $job_id,
			array(
				'type'        => $type,
				'format'      => $transient['format'],
				'headers'     => $headers,
				'rows'        => $rows,
				'count'       => $transient['count'],
				'created_at'  => $transient['created_at'],
				'schedule_id' => $transient['schedule_id'] ?? null,
				'title'       => $transient['title'] ?? null,
			),
			HOUR_IN_SECONDS
		);
		delete_transient( 'jwp_stk_sr_data_' . $job_id );

		as_enqueue_async_action( 'jwp_stk_sr_write_file', array( 'job_id' => $job_id ), 'jwp-stk' );
	}

	/**
	 * Step 3: Format and write the output file, then mark the job complete.
	 * Triggered by the 'jwp_stk_sr_write_file' AS action.
	 *
	 * @param string $job_id Job UUID.
	 * @return void
	 */
	public function handle_write_file( string $job_id ): void {
		$jm = \JWP_STK\Jobs\Job_Manager::get_instance();

		if ( $jm->is_cancelled( $job_id ) ) {
			delete_transient( 'jwp_stk_sr_rows_' . $job_id );
			return;
		}

		$transient = get_transient( 'jwp_stk_sr_rows_' . $job_id );
		if ( false === $transient ) {
			$jm->fail_job( $job_id, 'Step 3: built-rows transient expired or missing.' );
			return;
		}

		$type    = $transient['type'];
		$format  = $transient['format'];
		$headers = $transient['headers'];
		$rows    = $transient['rows'];
		$count   = $transient['count'];

		$date = gmdate( 'Y-m-d' );

		switch ( $format ) {
			case 'xlsx':
				$content  = $this->to_xlsx( $headers, $rows );
				$filename = "{$type}-{$date}.xlsx";
				break;
			case 'xml':
				$content  = $this->to_xml( $type, $headers, $rows );
				$filename = "{$type}-{$date}.xml";
				break;
			case 'json':
				$content  = $this->to_json( $headers, $rows );
				$filename = "{$type}-{$date}.json";
				break;
			default:
				$content  = $this->to_csv( $headers, $rows );
				$filename = "{$type}-{$date}.csv";
				break;
		}

		$dir       = $this->get_files_dir();
		$file_path = $dir . '/' . $job_id . '.' . $format;

		// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_file_put_contents
		file_put_contents( $file_path, $content );

		$this->set_report_file( $job_id, $file_path );
		$jm->update_progress( $job_id, 95, $count );
		delete_transient( 'jwp_stk_sr_rows_' . $job_id );
		$jm->complete_job( $job_id, $transient['created_at'] );

		// Mark schedule last run as success if this job originated from one.
		$schedule_id = $transient['schedule_id'] ?? null;
		if ( $schedule_id ) {
			global $wpdb;
			$table = $wpdb->prefix . 'jwp_stk_schedules';
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$wpdb->update(
				$table,
				array( 'last_run_status' => 'success' ),
				array( 'schedule_id' => $schedule_id ),
				array( '%s' ),
				array( '%s' )
			);
		}
	}

	// ─── Helpers ─────────────────────────────────────────────────────────────

	/**
	 * Recursively sanitize preset data array (keys via sanitize_key, values via sanitize_text_field).
	 *
	 * @param mixed $data Raw preset data.
	 * @return array
	 */
	public function sanitize_preset_data( $data ): array {
		$out = array();
		foreach ( (array) $data as $k => $v ) {
			$clean_key = sanitize_key( $k );
			if ( is_array( $v ) ) {
				$out[ $clean_key ] = $this->sanitize_preset_data( $v );
			} else {
				$out[ $clean_key ] = sanitize_text_field( (string) $v );
			}
		}
		return $out;
	}

	/**
	 * Sanitize a date string; returns empty string if invalid.
	 *
	 * @param mixed $value Raw value.
	 * @return string YYYY-MM-DD or ''.
	 */
	private function clean_date( $value ): string {
		$v = sanitize_text_field( (string) $value );
		return preg_match( '/^\d{4}-\d{2}-\d{2}$/', $v ) ? $v : '';
	}

	/**
	 * Return all distinct registered customer IDs that appear in any WooCommerce order.
	 * Uses an HPOS-aware single DB query for efficiency.
	 *
	 * @return int[]
	 */
	private function get_all_order_customer_ids(): array {
		global $wpdb;

		if (
			class_exists( '\Automattic\WooCommerce\Utilities\OrderUtil' ) &&
			\Automattic\WooCommerce\Utilities\OrderUtil::custom_orders_table_usage_is_enabled()
		) {
			// High Performance Order Storage.
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$ids = $wpdb->get_col(
				"SELECT DISTINCT customer_id
				 FROM {$wpdb->prefix}wc_orders
				 WHERE type = 'shop_order'
				   AND customer_id > 0"
			);
		} else {
			// Legacy post-meta storage.
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$ids = $wpdb->get_col(
				$wpdb->prepare(
					"SELECT DISTINCT pm.meta_value
					 FROM {$wpdb->postmeta} pm
					 INNER JOIN {$wpdb->posts} p ON p.ID = pm.post_id
					 WHERE pm.meta_key = '_customer_user'
					   AND pm.meta_value > %d
					   AND p.post_type = 'shop_order'",
					0
				)
			);
		}

		return array_map( 'intval', ( ! empty( $ids ) ) ? $ids : array() );
	}

	/**
	 * Build guest-customer objects from WooCommerce orders with no registered user.
	 * Groups orders by billing email; the most recent order supplies billing details.
	 * Note: capped at 10 000 guest orders — sufficient for most stores.
	 *
	 * @param string $date_from Order date lower bound (Y-m-d) or ''.
	 * @param string $date_to   Order date upper bound (Y-m-d) or ''.
	 * @return \stdClass[]
	 */
	private function get_guest_customers( string $date_from, string $date_to ): array {
		$order_args = array(
			'customer_id' => 0,
			'limit'       => 10000,
			'return'      => 'objects',
			'type'        => 'shop_order',
			'orderby'     => 'date',
			'order'       => 'DESC',
		);

		if ( $date_from || $date_to ) {
			if ( $date_from && $date_to ) {
				$order_args['date_created'] = $date_from . '...' . $date_to;
			} elseif ( $date_from ) {
				$order_args['date_created'] = '>=' . $date_from;
			} else {
				$order_args['date_created'] = '<=' . $date_to;
			}
		}

		$orders   = wc_get_orders( $order_args );
		$by_email = array();

		foreach ( $orders as $order ) {
			$email = strtolower( trim( $order->get_billing_email() ) );
			if ( ! $email ) {
				continue;
			}
			if ( ! isset( $by_email[ $email ] ) ) {
				$by_email[ $email ] = array(
					'order'        => $order,
					'total_orders' => 0,
					'total_spent'  => 0.0,
				);
			}
			++$by_email[ $email ]['total_orders'];
			$by_email[ $email ]['total_spent'] += (float) $order->get_total();
		}

		$guests = array();
		foreach ( $by_email as $data ) {
			$o                    = $data['order'];
			$g                    = new \stdClass();
			$g->is_guest          = true;
			$g->ID                = 0;
			$g->user_login        = '';
			$g->user_email        = $o->get_billing_email();
			$g->user_registered   = '';
			$g->roles             = array( 'guest' );
			$g->first_name        = $o->get_billing_first_name();
			$g->last_name         = $o->get_billing_last_name();
			$g->billing_phone     = $o->get_billing_phone();
			$g->billing_company   = $o->get_billing_company();
			$g->billing_address_1 = $o->get_billing_address_1();
			$g->billing_city      = $o->get_billing_city();
			$g->billing_state     = $o->get_billing_state();
			$g->billing_postcode  = $o->get_billing_postcode();
			$g->billing_country   = $o->get_billing_country();
			$g->_total_orders     = $data['total_orders'];
			$g->_total_spent      = $data['total_spent'];
			$g->_last_order       = $o;
			$guests[]             = $g;
		}

		return $guests;
	}

	/**
	 * Extract a value from a guest customer stdClass object.
	 *
	 * @param \stdClass $guest Guest customer object.
	 * @param string    $key   Column key.
	 * @return string
	 */
	private function get_guest_customer_value( \stdClass $guest, string $key ): string {
		switch ( $key ) {
			case 'customer_id':
				return '0';
			case 'username':
				return '(guest)';
			case 'first_name':
				return $guest->first_name;
			case 'last_name':
				return $guest->last_name;
			case 'name':
				return trim( $guest->first_name . ' ' . $guest->last_name );
			case 'email':
				return $guest->user_email;
			case 'phone':
				return $guest->billing_phone;
			case 'user_role':
				return 'guest';
			case 'billing_company':
				return $guest->billing_company;
			case 'billing_address':
				return $guest->billing_address_1;
			case 'billing_city':
				return $guest->billing_city;
			case 'billing_state':
				return $guest->billing_state;
			case 'billing_postcode':
				return $guest->billing_postcode;
			case 'billing_country':
				return $guest->billing_country;
			case 'registered':
				return '';
			case 'total_orders':
				return (string) $guest->_total_orders;
			case 'total_spent':
				return (string) $guest->_total_spent;
			case 'average_order_value':
				return $guest->_total_orders > 0
					? (string) round( $guest->_total_spent / $guest->_total_orders, 2 )
					: '0';
			case 'last_order_date':
				$d = $guest->_last_order->get_date_created();
				return $d ? $d->date( 'Y-m-d' ) : '';
			case 'last_order_amount':
				return (string) $guest->_last_order->get_total();
			default:
				return '';
		}
	}

	/**
	 * Prefix cell values starting with formula characters to prevent CSV injection.
	 *
	 * @param string $value Raw cell value.
	 * @return string
	 */
	private function sanitize_cell_value( string $value ): string {
		if ( '' !== $value && in_array( $value[0], array( '=', '+', '-', '@' ), true ) ) {
			return ' ' . $value;
		}
		return $value;
	}
}
