<?php
/**
 * BatchRequest model.
 *
 * Provides CRUD + query helpers for the plugin's custom ai_content_by_parallax_batch_requests table.
 * Includes lightweight object-cache memoization for read queries.
 *
 * Notes on PHPCS / PluginCheck:
 * - Table/column identifiers cannot be placeholders in $wpdb->prepare(), so they are constructed from // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Identifiers/SQL fragments are internally constructed; values use placeholders.
 *   internal allow-lists and escaped/backticked.
 * - All dynamic WHERE values use placeholders via $wpdb->prepare(). // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Identifiers/SQL fragments are internally constructed; values use placeholders.
 * - Some sniffs still flag “dynamic SQL variables” even when prepared; where unavoidable, we use
 *   narrow, justified phpcs:ignore annotations.
 *
 * @package AIContentByParallax
 */

namespace AIContentByParallax\Models;

defined( 'ABSPATH' ) || exit;

use AIContentByParallax\Api\MiddlewareRequest;
use AIContentByParallax\Core\Logger;
use AIContentByParallax\Enums\BatchRequestStatus;

/**
 * Class BatchRequest
 */
class BatchRequest {

	/**
	 * Cache group name for wp_cache_* calls.
	 *
	 * @var string
	 */
	private const CACHE_GROUP = 'ai_content_by_parallax_batch_requests';

	/**
	 * Cache version key (bumped to invalidate all cache entries).
	 *
	 * @var string
	 */
	private const CACHE_VERSION_KEY = 'cache_version';

	/**
	 * Get the raw table name (unquoted) for $wpdb CRUD helpers (insert/update/delete).
	 *
	 * IMPORTANT: $wpdb CRUD helpers expect unquoted identifiers.
	 *
	 * @return string
	 */
	protected static function table_name_raw(): string {
		global $wpdb;
		return $wpdb->prefix . 'ai_content_by_parallax_batch_requests';
	}

	/**
	 * Get a SQL-safe, backticked identifier for manual SQL.
	 *
	 * NOTE: Identifiers (table/column names) cannot be used as placeholders in $wpdb->prepare().
	 * This value is derived from internal code (not user input) and escaped/backticked.
	 *
	 * @return string
	 */
	protected static function table_name_sql(): string {
		return '`' . esc_sql( self::table_name_raw() ) . '`';
	}

	/**
	 * Get a SQL-safe, backticked column identifier.
	 *
	 * Column name MUST be validated against an allow-list before calling this.
	 *
	 * @param string $column Column name from allow-list.
	 * @return string
	 */
	private static function column_sql( string $column ): string {
		return '`' . str_replace( '`', '', $column ) . '`';
	}

	/**
	 * Get cache TTL in seconds.
	 *
	 * @return int
	 */
	private static function cache_ttl(): int {
		$default = defined( 'MINUTE_IN_SECONDS' ) ? (int) MINUTE_IN_SECONDS : 60;

		/**
		 * Filter cache TTL for batch request reads.
		 *
		 * @param int $default Default TTL.
		 */
		return (int) apply_filters( 'ai_content_by_parallax_batch_request_cache_ttl', $default );
	}

	/**
	 * Get current cache version.
	 *
	 * @return string
	 */
	private static function get_cache_version(): string {
		$version = wp_cache_get( self::CACHE_VERSION_KEY, self::CACHE_GROUP );

		if ( false === $version ) {
			$version = microtime( true );
			wp_cache_set( self::CACHE_VERSION_KEY, $version, self::CACHE_GROUP, 0 );
		}

		return (string) $version;
	}

	/**
	 * Check whether the items table exists (cached).
	 *
	 * @return bool
	 */
	private static function items_table_exists(): bool {
		$ttl = defined( 'MINUTE_IN_SECONDS' ) ? (int) MINUTE_IN_SECONDS : 60;

		return (bool) self::remember(
			'items_table_exists',
			'',
			static function (): bool {
				global $wpdb;

				$items_table = $wpdb->prefix . 'ai_content_by_parallax_batch_request_items';

				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Table existence check cached via remember().
				// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier is internal + escaped.
				$table_exists = $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Cached via remember().
					$wpdb->prepare( 'SHOW TABLES LIKE %s', $items_table ) // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Identifier fragment is internally constructed.
				);

				return ! empty( $table_exists );
			},
			$ttl
		);
	}

	/**
	 * Bump cache version to invalidate all memoized entries.
	 *
	 * @return void
	 */
	private static function bump_cache_version(): void {
		wp_cache_set( self::CACHE_VERSION_KEY, microtime( true ), self::CACHE_GROUP, 0 );
	}

	/**
	 * Build a cache key for a given context + suffix.
	 *
	 * @param string $context Namespace within cache group.
	 * @param mixed  $suffix  Suffix data (scalar/array/object).
	 * @return string
	 */
	private static function cache_key( string $context, $suffix = '' ): string {
		$context_key  = preg_replace( '/[^a-z0-9_\-]/', '', strtolower( $context ) );
		$suffix_value = is_scalar( $suffix ) ? (string) $suffix : wp_json_encode( $suffix );

		if ( false === $suffix_value ) {
			// phpcs:ignore WordPress.PHP.DiscouragedPHPFunctions.serialize_serialize -- Fallback only.
			$suffix_value = serialize( $suffix );
		}

		return md5( self::get_cache_version() ) . ':' . $context_key . ':' . md5( (string) $suffix_value );
	}

	/**
	 * Memoize a computed value via object cache.
	 *
	 * @param string   $context  Cache namespace.
	 * @param mixed    $suffix   Cache key suffix (array ok).
	 * @param callable $callback Computes the value when cache miss.
	 * @param int|null $ttl      TTL in seconds.
	 * @return mixed
	 */
	private static function remember( string $context, $suffix, callable $callback, ?int $ttl = null ) {
		$key    = self::cache_key( $context, $suffix );
		$found  = false;
		$cached = wp_cache_get( $key, self::CACHE_GROUP, false, $found );

		if ( $found ) {
			return $cached;
		}

		$value = $callback();
		wp_cache_set( $key, $value, self::CACHE_GROUP, $ttl ?? self::cache_ttl() );

		return $value;
	}

	/**
	 * Invalidate all memoized entries for this model.
	 *
	 * @return void
	 */
	private static function invalidate_cache(): void {
		self::bump_cache_version();
	}

	/**
	 * Allowed columns for SELECT / ORDER BY.
	 *
	 * @return array<int,string>
	 */
	private static function get_allowed_columns(): array {
		return array(
			'id',
			'batch_request_id',
			'status',
			'type',
			'total_expected',
			'batches_done',
			'metadata',
			'created_at',
			'updated_at',
		);
	}

	/**
	 * Sanitize ORDER BY column.
	 *
	 * @param string $orderby Requested column.
	 * @return string
	 */
	private static function sanitize_orderby( string $orderby ): string {
		$allowed = self::get_allowed_columns();
		return in_array( $orderby, $allowed, true ) ? $orderby : 'created_at';
	}

	/**
	 * Sanitize ORDER direction.
	 *
	 * @param string $order Requested direction.
	 * @return string
	 */
	private static function sanitize_order( string $order ): string {
		$order = strtoupper( $order );
		return in_array( $order, array( 'ASC', 'DESC' ), true ) ? $order : 'DESC';
	}

	/**
	 * Sanitize SELECT columns.
	 *
	 * Returns either "*" or a comma-separated list of backticked allow-listed columns.
	 *
	 * @param mixed $select Requested select.
	 * @return string
	 */
	private static function sanitize_select( $select ): string {
		if ( '*' === $select ) {
			return '*';
		}

		$allowed = self::get_allowed_columns();

		if ( is_string( $select ) ) {
			$select = array_map( 'trim', explode( ',', $select ) );
		}

		if ( ! is_array( $select ) ) {
			$select = $allowed;
		}

		$sanitized = array();

		foreach ( $select as $column ) {
			if ( in_array( $column, $allowed, true ) ) {
				$sanitized[] = self::column_sql( $column );
			}
		}

		if ( empty( $sanitized ) ) {
			$sanitized = array_map(
				static function ( string $col ): string {
					return '`' . $col . '`';
				},
				$allowed
			);
		}

		return implode( ', ', $sanitized );
	}

	/**
	 * Build a prepared WHERE fragment (no placeholders).
	 *
	 * Returns a fragment that starts with " AND ...".
	 *
	 * @param array<string,mixed> $args Query args.
	 * @return string
	 */
	private static function build_where_fragment( array $args ): string {
		global $wpdb;

		$fragments = array();

		if ( ! empty( $args['id'] ) ) {
			$fragments[] = $wpdb->prepare( ' AND id = %d', absint( $args['id'] ) );
		}

		if ( ! empty( $args['ids'] ) ) {
			$ids = array_values( array_filter( array_map( 'absint', (array) $args['ids'] ) ) );

			if ( ! empty( $ids ) ) {
				$placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
				$sql          = ' AND id IN (' . $placeholders . ')';

				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Placeholders are generated internally; values prepared below.
				$fragments[] = call_user_func_array( array( $wpdb, 'prepare' ), array_merge( array( $sql ), $ids ) );
			}
		}

		if ( ! empty( $args['batch_request_id'] ) ) {
			$fragments[] = $wpdb->prepare(
				' AND batch_request_id = %s',
				sanitize_text_field( (string) $args['batch_request_id'] )
			);
		}

		if ( ! empty( $args['batch_request_ids'] ) ) {
			$values = array_values( array_filter( array_map( 'sanitize_text_field', (array) $args['batch_request_ids'] ) ) );

			if ( ! empty( $values ) ) {
				$placeholders = implode( ',', array_fill( 0, count( $values ), '%s' ) );
				$sql          = ' AND batch_request_id IN (' . $placeholders . ')';

				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Placeholders are generated internally; values prepared below.
				$fragments[] = call_user_func_array( array( $wpdb, 'prepare' ), array_merge( array( $sql ), $values ) );
			}
		}

		if ( ! empty( $args['types'] ) ) {
			$types = array_values( array_filter( array_map( 'sanitize_text_field', (array) $args['types'] ) ) );

			if ( ! empty( $types ) ) {
				$placeholders = implode( ',', array_fill( 0, count( $types ), '%s' ) );
				$sql          = ' AND type IN (' . $placeholders . ')';

				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Placeholders are generated internally; values prepared below.
				$fragments[] = call_user_func_array( array( $wpdb, 'prepare' ), array_merge( array( $sql ), $types ) );
			}
		}

		if ( ! empty( $args['status__in'] ) ) {
			$statuses = array_values( array_filter( array_map( 'sanitize_text_field', (array) $args['status__in'] ) ) );

			if ( ! empty( $statuses ) ) {
				$placeholders = implode( ',', array_fill( 0, count( $statuses ), '%s' ) );
				$sql          = ' AND status IN (' . $placeholders . ')';

				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Placeholders are generated internally; values prepared below.
				$fragments[] = call_user_func_array( array( $wpdb, 'prepare' ), array_merge( array( $sql ), $statuses ) );
			}
		}

		if ( ! empty( $args['status__not_in'] ) ) {
			$statuses = array_values( array_filter( array_map( 'sanitize_text_field', (array) $args['status__not_in'] ) ) );

			if ( ! empty( $statuses ) ) {
				$placeholders = implode( ',', array_fill( 0, count( $statuses ), '%s' ) );
				$sql          = ' AND status NOT IN (' . $placeholders . ')';

				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Placeholders are generated internally; values prepared below.
				$fragments[] = call_user_func_array( array( $wpdb, 'prepare' ), array_merge( array( $sql ), $statuses ) );
			}
		}

		return implode( '', $fragments );
	}

	/**
	 * Query rows from the batch requests table.
	 *
	 * @param array<string,mixed> $args Query args.
	 * @return array<int,array<string,mixed>>
	 */
	public static function query( array $args = array() ): array {
		$defaults = array(
			'types'             => array(),
			'status__in'        => array(),
			'status__not_in'    => array(),
			'ids'               => array(),
			'id'                => null,
			'batch_request_id'  => null,
			'batch_request_ids' => array(),
			'limit'             => null,
			'offset'            => null,
			'orderby'           => 'created_at',
			'order'             => 'DESC',
			'select'            => '*',
			'maybe_unserialize' => true,
		);

		$args    = wp_parse_args( $args, $defaults );
		$orderby = self::sanitize_orderby( (string) $args['orderby'] );
		$order   = self::sanitize_order( (string) $args['order'] );
		$select  = self::sanitize_select( $args['select'] );

		$cache_suffix = array_merge(
			$args,
			array(
				'_sanitized_orderby' => $orderby,
				'_sanitized_order'   => $order,
				'_sanitized_select'  => $select,
			)
		);

		return self::remember(
			'query',
			$cache_suffix,
			function () use ( $args, $orderby, $order, $select ) {
				global $wpdb;

				$table       = self::table_name_sql();
				$orderby_sql = self::column_sql( $orderby );

				// Identifiers are allow-listed; escape to satisfy static analysis.
				$select_sql  = esc_sql( $select );
				$table_sql   = esc_sql( $table );
				$orderby_sql = esc_sql( $orderby_sql );
				$order_sql   = esc_sql( $order );

				// build_where_fragment() returns fully prepared SQL fragments.
				$where_fragment = self::build_where_fragment( $args );

				// Pagination placeholders.
				$pagination_format = '';
				$pagination_args   = array();

				if ( null !== $args['limit'] ) {
					$pagination_format .= ' LIMIT %d';
					$pagination_args[]  = max( 1, (int) $args['limit'] );
				}

				if ( null !== $args['offset'] ) {
					$pagination_format .= ' OFFSET %d';
					$pagination_args[]  = max( 0, (int) $args['offset'] );
				}

				// Final SQL format string.
				$sql_format =
					'SELECT ' . $select_sql .
					' FROM ' . $table_sql .
					' WHERE 1=%d' .
					$where_fragment .
					' ORDER BY ' . $orderby_sql . ' ' . $order_sql .
					$pagination_format;

				// Always include at least one placeholder.
				$prepare_args = array_merge( array( 1 ), $pagination_args );

				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Identifiers are allow-listed; dynamic values passed via prepare().
				$prepared_sql = call_user_func_array(
					array( $wpdb, 'prepare' ),
					array_merge( array( $sql_format ), $prepare_args )
				);

				$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Cached via remember(); SQL prepared above; false positive from PluginCheck.
					$prepared_sql, // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Prepared above; identifiers are allow-listed.
					ARRAY_A
				);

				if ( ! empty( $args['maybe_unserialize'] ) && ! empty( $rows ) ) {
					foreach ( $rows as &$row ) {
						if ( isset( $row['metadata'] ) ) {
							$row['metadata'] = maybe_unserialize( $row['metadata'] );
						}
					}
				}

				return is_array( $rows ) ? $rows : array();
			},
			self::cache_ttl()
		);
	}

	/**
	 * Count rows matching given filters.
	 *
	 * @param array<string,mixed> $args Filters.
	 * @return int
	 */
	public static function count( array $args = array() ): int {
		$defaults = array(
			'types'             => array(),
			'status__in'        => array(),
			'status__not_in'    => array(),
			'ids'               => array(),
			'id'                => null,
			'batch_request_id'  => null,
			'batch_request_ids' => array(),
		);

		$args = wp_parse_args( $args, $defaults );

		return (int) self::remember(
			'count',
			$args,
			function () use ( $args ) {
				global $wpdb;

				$table_sql = esc_sql( self::table_name_sql() );

				// build_where_fragment() returns fully prepared SQL fragments (no placeholders).
				$where_fragment = self::build_where_fragment( $args );

				// Build as a format string and prepare it to satisfy PluginCheck.
				$sql_format =
					'SELECT COUNT(*) FROM ' . $table_sql .
					' WHERE 1=%d' .
					$where_fragment;

				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Identifiers are allow-listed; values are passed via prepare().
				$prepared_sql = $wpdb->prepare( $sql_format, 1 );

				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Cached via remember().
				return (int) $wpdb->get_var( $prepared_sql ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Cached via remember(); SQL prepared; identifiers allow-listed; PluginCheck false positive.
			},
			self::cache_ttl()
		);
	}

	/**
	 * Get paginated results for list tables / endpoints.
	 *
	 * @param array<string,mixed> $args Pagination + filters.
	 * @return array<string,mixed>
	 */
	public static function get_paginated( array $args = array() ): array {
		$per_page = isset( $args['per_page'] ) ? max( 1, (int) $args['per_page'] ) : 20;
		$page     = isset( $args['page'] ) ? max( 1, (int) $args['page'] ) : 1;

		$query_args = array(
			'types'             => $args['types'] ?? array(),
			'status__in'        => $args['status__in'] ?? array(),
			'status__not_in'    => $args['status__not_in'] ?? array(),
			'ids'               => $args['ids'] ?? array(),
			'id'                => $args['id'] ?? null,
			'batch_request_id'  => $args['batch_request_id'] ?? null,
			'batch_request_ids' => $args['batch_request_ids'] ?? array(),
			'orderby'           => $args['orderby'] ?? 'created_at',
			'order'             => $args['order'] ?? 'DESC',
			'limit'             => $per_page,
			'offset'            => ( $page - 1 ) * $per_page,
			'maybe_unserialize' => $args['maybe_unserialize'] ?? true,
			'select'            => $args['select'] ?? '*',
		);

		$items = self::query( $query_args );

		$count_args = $query_args;
		unset( $count_args['limit'], $count_args['offset'], $count_args['orderby'], $count_args['order'], $count_args['maybe_unserialize'], $count_args['select'] );

		$total_items = self::count( $count_args );
		$total_pages = max( 1, (int) ceil( $total_items / $per_page ) );

		return array(
			'items'       => $items,
			'total_items' => $total_items,
			'per_page'    => $per_page,
			'page'        => $page,
			'total_pages' => $total_pages,
		);
	}

	/**
	 * Fetch a batch request by remote batch_request_id string.
	 *
	 * @param string $identifier Remote identifier.
	 * @return array<string,mixed>|null
	 */
	public static function get_by_batch_identifier( string $identifier ): ?array {
		$identifier = trim( $identifier );

		if ( '' === $identifier ) {
			return null;
		}

		$row = self::remember(
			'by_remote_id',
			$identifier,
			function () use ( $identifier ) {
				global $wpdb;

				$table_sql  = esc_sql( self::table_name_sql() );
				$where_base = $wpdb->prepare( ' WHERE batch_request_id = %s LIMIT 1', $identifier );

				$sql = 'SELECT * FROM ' . $table_sql . $where_base;

				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Cached via remember(); identifiers are allow-listed; value is prepared above.
				$result = $wpdb->get_row( $sql, ARRAY_A );

				if ( $result && isset( $result['metadata'] ) ) {
					$result['metadata'] = maybe_unserialize( $result['metadata'] );
				}

				if ( empty( $result ) ) {
					return null;
				}

				return $result;
			},
			self::cache_ttl()
		);

		if ( empty( $row ) ) {
			return null;
		}

		return $row;
	}

	/**
	 * Create a new batch request row.
	 *
	 * @param array<string,mixed> $data Data to insert.
	 * @return int|false Inserted row ID or false on failure.
	 */
	public static function create( array $data ) {
		global $wpdb;

		$table = self::table_name_raw();

		Logger::write_log( 'info', "Creating batch request in table: {$table} with data: ", $data );

		$wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Insert into custom table; cache invalidated below.
			$table,
			array(
				'batch_request_id' => isset( $data['batch_request_id'] ) ? sanitize_text_field( (string) $data['batch_request_id'] ) : 'batch_request_' . wp_generate_password( 12, false ),
				'type'             => isset( $data['type'] ) ? sanitize_text_field( (string) $data['type'] ) : 'default',
				'status'           => isset( $data['status'] ) ? sanitize_text_field( (string) $data['status'] ) : 'processing',
				'total_expected'   => (int) ( $data['total_expected'] ?? 0 ),
				'batches_done'     => 0,
				'metadata'         => maybe_serialize( $data['metadata'] ?? array() ),
				'created_at'       => current_time( 'mysql' ),
				'updated_at'       => current_time( 'mysql' ),
			),
			array(
				'%s',
				'%s',
				'%s',
				'%d',
				'%d',
				'%s',
				'%s',
				'%s',
			)
		);

		if ( $wpdb->last_error ) {
			Logger::write_log( 'info', 'Database error on insert: ' . $wpdb->last_error );
			return false;
		}

		Logger::write_log( 'info', 'Batch Request created with ID: ' . $wpdb->insert_id );

		self::invalidate_cache();

		return (int) $wpdb->insert_id;
	}

	/**
	 * Delete a batch request by row ID.
	 *
	 * @param int $id Row ID.
	 * @return int|false Rows affected or false on failure.
	 */
	public static function delete( int $id ) {
		global $wpdb;

		$table = self::table_name_raw();

		Logger::write_log( 'info', "Deleting batch request from table: {$table} with ID: {$id}" );

		$deleted = $wpdb->delete( $table, array( 'id' => $id ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table delete; cache invalidated below.

		if ( false !== $deleted ) {
			self::invalidate_cache();
		}

		return $deleted;
	}

	/**
	 * Delete a batch request and related items (if items table exists).
	 *
	 * @param int $id Batch request row ID.
	 * @return bool
	 */
	public static function delete_with_items( int $id ): bool {
		global $wpdb;

		$items_table = $wpdb->prefix . 'ai_content_by_parallax_batch_request_items';

		if ( self::items_table_exists() ) {
			$items_deleted = $wpdb->delete( $items_table, array( 'batch_request_id' => $id ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table delete; memoized table check prevents repeated queries.
			if ( false === $items_deleted && $wpdb->last_error ) {
				Logger::write_log( 'info', 'Failed to delete batch request items: ' . $wpdb->last_error );
				return false;
			}
		}

		return false !== self::delete( $id );
	}

	/**
	 * Update/merge one metadata key into the serialized metadata array.
	 *
	 * @param int    $id         Row ID.
	 * @param string $meta_key   Metadata key.
	 * @param mixed  $meta_value Metadata value.
	 * @return void
	 */
	public static function update_field_meta( int $id, string $meta_key, $meta_value ): void {
		global $wpdb;

		$table_raw = self::table_name_raw();

		// Use cached read to avoid an uncached direct query.
		$existing = self::get( $id );
		if ( empty( $existing ) || ! isset( $existing['metadata'] ) ) {
			return;
		}

		$metadata = $existing['metadata'];

		if ( ! is_array( $metadata ) ) {
			$metadata = maybe_unserialize( $metadata );
		}

		if ( ! is_array( $metadata ) ) {
			$metadata = array();
		}

		$metadata[ $meta_key ] = $meta_value;

		$updated = $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table update; cache invalidated below.
			$table_raw,
			array(
				'metadata'   => maybe_serialize( $metadata ),
				'updated_at' => current_time( 'mysql' ),
			),
			array( 'id' => $id ),
			array( '%s', '%s' ),
			array( '%d' )
		);

		if ( false !== $updated ) {
			self::invalidate_cache();
		}
	}

	/**
	 * Update status field.
	 *
	 * @param int    $id     Row ID.
	 * @param string $status New status.
	 * @return int|false
	 */
	public static function update_status( int $id, string $status ) {
		global $wpdb;

		$table = self::table_name_raw();

		$updated = $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table update; cache invalidated below.
			$table,
			array(
				'status'     => sanitize_text_field( $status ),
				'updated_at' => current_time( 'mysql' ),
			),
			array( 'id' => $id ),
			array( '%s', '%s' ),
			array( '%d' )
		);

		if ( false !== $updated ) {
			self::invalidate_cache();
		}

		return $updated;
	}

	/**
	 * Update batches_done field.
	 *
	 * @param int $id           Row ID.
	 * @param int $batches_done Batches done.
	 * @return int|false
	 */
	public static function update_batches_done( int $id, int $batches_done ) {
		global $wpdb;

		$table = self::table_name_raw();

		$updated = $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table update; cache invalidated below.
			$table,
			array(
				'batches_done' => (int) $batches_done,
				'updated_at'   => current_time( 'mysql' ),
			),
			array( 'id' => $id ),
			array( '%d', '%s' ),
			array( '%d' )
		);

		if ( false !== $updated ) {
			self::invalidate_cache();
		}

		return $updated;
	}

	/**
	 * Whether any batch requests exist for a given type.
	 *
	 * @param string $type Type string.
	 * @return bool
	 */
	public static function exists( string $type ): bool {
		return self::count(
			array(
				'types' => array( $type ),
			)
		) > 0;
	}

	/**
	 * Get a single batch request by row ID.
	 *
	 * @param int $id Row ID.
	 * @return array<string,mixed>|null
	 */
	public static function get( int $id ): ?array {
		return self::remember(
			'single',
			$id,
			function () use ( $id ) {
				global $wpdb;

				$table_sql  = esc_sql( self::table_name_sql() );
				$where_base = $wpdb->prepare( ' WHERE id = %d', $id );

				$sql = 'SELECT * FROM ' . $table_sql . $where_base;

				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Cached via remember(); identifiers are allow-listed; value is prepared above.
				$row = $wpdb->get_row( $sql, ARRAY_A );

				if ( $row && isset( $row['metadata'] ) ) {
					$row['metadata'] = maybe_unserialize( $row['metadata'] );
				}

				if ( empty( $row ) ) {
					return null;
				}

				return $row;
			},
			self::cache_ttl()
		);
	}

	/**
	 * Clear all rows (truncate table).
	 *
	 * @return int|false
	 */
	public static function clear_all() {
		global $wpdb;

		// TRUNCATE cannot use placeholders for identifiers.
		// Table name is internal, escaped, and backticked.
		$table_raw = self::table_name_raw();
		$table_sql = '`' . str_replace( '`', '', esc_sql( $table_raw ) ) . '`';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Intentional maintenance operation on custom table; identifier is internal+escaped; cache invalidated below.
		$deleted = $wpdb->query(
			'TRUNCATE TABLE ' . $table_sql // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Identifier is internal + escaped/backticked.
		);

		if ( false !== $deleted ) {
			self::invalidate_cache();
		}

		return $deleted;
	}

	/**
	 * Get all batch requests.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public static function get_all(): array {
		return self::query(
			array(
				'orderby' => 'created_at',
				'order'   => 'DESC',
			)
		);
	}

	/**
	 * Whether any batch requests exist.
	 *
	 * @return bool
	 */
	public static function has_any(): bool {
		return self::count() > 0;
	}

	/**
	 * Pull latest status for a single batch request.
	 *
	 * @param int|string $identifier Row ID (int-like) or remote batch_request_id (string).
	 * @return array<string,mixed>
	 */
	public static function pull_single( $identifier ): array {
		$batch_request = null;

		if ( is_numeric( $identifier ) ) {
			$batch_request = self::get( (int) $identifier );
		}

		if ( ! $batch_request ) {
			$batch_request = self::get_by_batch_identifier( (string) $identifier );
		}

		if ( ! $batch_request ) {
			Logger::write_log( 'info', "pull_single: No batch request found for identifier {$identifier}" );
			return array(
				'error'      => 'not_found',
				'identifier' => $identifier,
			);
		}

		$status_response = MiddlewareRequest::get_batch_request_status( (string) $batch_request['batch_request_id'] );
		$status_array    = json_decode( $status_response, true );

		if ( JSON_ERROR_NONE !== json_last_error() || ! is_array( $status_array ) ) {
			Logger::write_log( 'info', "pull_single: Failed to decode response for batch request {$batch_request['batch_request_id']}: {$status_response}" );
			return array(
				'error'         => 'decode_failed',
				'batch_request' => $batch_request,
				'raw_response'  => $status_response,
			);
		}

		if ( isset( $status_array['error'] ) ) {
			Logger::write_log( 'info', "pull_single: API error for batch request {$batch_request['batch_request_id']}: " . wp_json_encode( $status_array ) );
			return array(
				'error'         => 'api_error',
				'batch_request' => $batch_request,
				'status'        => $status_array,
			);
		}

		self::apply_status_update( $batch_request, $status_array );

		$updated_batch_request = self::get( (int) $batch_request['id'] );

		return array(
			'batch_request' => null !== $updated_batch_request ? $updated_batch_request : $batch_request,
			'status'        => $status_array,
		);
	}

	/**
	 * Refresh status from the middleware for every non-terminal batch request.
	 *
	 * Only processes records whose status is not already 'completed' or 'failed',
	 * so the cron job never makes API calls for records that cannot change.
	 *
	 * @return bool
	 */
	public static function pull_all(): bool {
		$batch_requests = self::query(
			array(
				'status__not_in' => BatchRequestStatus::terminal_statuses(),
				'orderby'        => 'created_at',
				'order'          => 'DESC',
			)
		);

		foreach ( $batch_requests as $batch_request ) {
			$batch_request_id    = (string) $batch_request['batch_request_id'];
			$array_json_response = MiddlewareRequest::get_batch_request_status( $batch_request_id );
			$batch_request_array = json_decode( $array_json_response, true );

			if ( empty( $batch_request_array ) || ! is_array( $batch_request_array ) ) {
				Logger::write_log( 'info', "Failed to decode JSON response for batch request ID: {$batch_request_id}" );
				continue;
			}

			self::apply_status_update( $batch_request, $batch_request_array );
		}

		return true;
	}

	/**
	 * Count batch requests whose status is not completed/failed.
	 *
	 * @return int
	 */
	public static function get_incomplete_count(): int {
		return self::count(
			array(
				'status__not_in' => BatchRequestStatus::terminal_statuses(),
			)
		);
	}

	/**
	 * Whether any incomplete batch requests exist.
	 *
	 * @return bool
	 */
	public static function has_incomplete_batch_requests(): bool {
		return self::get_incomplete_count() > 0;
	}

	/**
	 * Apply decoded middleware status information to a stored batch request record.
	 *
	 * @param array<string,mixed> $batch_request       Existing batch request row.
	 * @param array<string,mixed> $batch_request_array Decoded status payload.
	 * @return void
	 */
	private static function apply_status_update( array $batch_request, array $batch_request_array ): void {
		$id = (int) $batch_request['id'];

		if ( isset( $batch_request_array['processedItems'] ) && (int) $batch_request_array['processedItems'] > 0 ) {
			self::update_batches_done( $id, (int) $batch_request_array['processedItems'] );
		} elseif ( isset( $batch_request_array['processedProductItems'] ) ) {
			self::update_batches_done( $id, (int) $batch_request_array['processedProductItems'] );
		} elseif ( isset( $batch_request_array['processedCategoryItems'] ) ) {
			self::update_batches_done( $id, (int) $batch_request_array['processedCategoryItems'] );
		}

		if ( isset( $batch_request_array['failedItems'] ) ) {
			self::update_field_meta( $id, 'failedItems', $batch_request_array['failedItems'] );
		} else {
			self::update_field_meta( $id, 'failedItems', 0 );
		}

		if ( isset( $batch_request_array['status'] ) ) {
			$raw_status = $batch_request_array['status'];

			Logger::write_log( 'info', "Updating batch request ID {$id} status from raw value: {$raw_status}" );

			// API v2 returns string enums (e.g. "Complete"); v1 returned integers.
			if ( is_numeric( $raw_status ) ) {
				$enum_case = BatchRequestStatus::from_api_code( (int) $raw_status );
			} else {
				$enum_case = BatchRequestStatus::from_api_status( (string) $raw_status );
			}

			if ( null === $enum_case ) {
				Logger::write_log( 'warning', "Unrecognized status value '{$raw_status}' for batch request ID {$id}; skipping status update." );
			} else {
				Logger::write_log( 'info', "Updating batch request ID {$id} status to {$enum_case->value}" );
				self::update_status( $id, $enum_case->value );
			}
		}

		if ( isset( $batch_request_array['batchesDone'] ) ) {
			self::update_batches_done( $id, (int) $batch_request_array['batchesDone'] );
		}
	}
}