<?php
/**
 * Entry storage.
 *
 * Entries live in their own table rather than wp_posts. The reason is not
 * performance (a few thousand rows would be fine), but that retention becomes
 * a single DELETE, and entries never need defensive exclusion from sitemaps,
 * search or post queries. The cost is that the privacy exporter and eraser must
 * be wired by hand; they are, below.
 *
 * The entry is written before the notification email is attempted, and the
 * delivery outcome is recorded against it. A mail failure can therefore never
 * lose an enquiry, and failures are visible instead of silent.
 *
 * @package AccessibleForms
 */

namespace AccessibleForms;

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-owned tables and atomic option leases use prepared SQL; live permissions, deletion and queue state must not use stale object-cache rows.

if ( ! defined( 'ABSPATH' ) ) {
	exit();
}

/**
 * CRUD and lifecycle for stored submissions.
 */
class Entries {
	/**
	 * JSON flags for stored answers. Non-Latin text and slashes are stored as
	 * written, so they stay readable in the database and searchable.
	 */
	const JSON_FLAGS = JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES;
	/** Imported entries explicitly kept by hand, including Spam and Needs review. */
	const IMPORT_KEEP = -2;

	/**
	 * Option recording the state of the search_text column: 1 once the column
	 * exists, 2 once every older entry has been indexed.
	 */
	const SEARCH_OPTION = 'accessible_forms_search_index';

	/**
	 * Fully qualified table name.
	 *
	 * @return string
	 */
	public static function table_name() {
		global $wpdb;

		return $wpdb->prefix . 'accessible_form_entries';
	}

	/**
	 * Create or upgrade the table.
	 */
	public static function install() {
		global $wpdb;

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$table   = self::table_name();
		$collate = $wpdb->get_charset_collate();

		$sql = "CREATE TABLE {$table} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			form_id BIGINT UNSIGNED NOT NULL,
			created_at DATETIME NOT NULL,
			time_version TINYINT UNSIGNED NOT NULL DEFAULT 0,
			time_original LONGTEXT NULL,
			status VARCHAR(20) NOT NULL DEFAULT 'received',
			spam_reason VARCHAR(40) NULL,
			is_read TINYINT(1) NOT NULL DEFAULT 0,
			fields LONGTEXT NOT NULL,
			search_text LONGTEXT NULL,
			snapshot LONGTEXT NULL,
			request_key CHAR(64) NULL,
			payload_hash CHAR(64) NULL,
			workflow VARCHAR(20) NOT NULL DEFAULT 'new',
			assignee BIGINT UNSIGNED NOT NULL DEFAULT 0,
			due_date DATE NULL,
			notes LONGTEXT NULL,
			trashed_at DATETIME NULL,
			expires_at DATETIME NULL,
			retention_days INT NOT NULL DEFAULT -1,
			page_url VARCHAR(255) NULL,
			mail_status VARCHAR(20) NULL,
			mail_error TEXT NULL,
			ip_hash CHAR(64) NOT NULL,
			user_agent VARCHAR(255) NULL,
			resolved_at DATETIME NULL,
			first_reply_at DATETIME NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY request_key (request_key),
			KEY workflow_assignee (workflow, assignee),
			KEY expires_at (expires_at),
			KEY form_created (form_id, created_at),
			KEY status_read (status, is_read),
			KEY status_created (status, created_at),
			KEY time_version (time_version, id),
			KEY form_resolved (form_id, resolved_at),
			KEY form_replied (form_id, first_reply_at),
			KEY created_list (created_at, id, status),
			KEY status_read_list (status, is_read, created_at, id),
			KEY workflow_list (workflow, assignee, created_at, id, status),
			KEY form_overview (form_id, status, is_read, workflow, mail_status, created_at, time_version)
		) {$collate};";

		dbDelta( $sql );
	}

	/**
	 * Store a submission.
	 *
	 * @param int    $form_id Form post ID.
	 * @param array  $values  Validated values.
	 * @param string $status  'received' or 'spam'.
	 * @param string $reason  Spam layer identifier, or a note on an accepted entry.
	 * @return int Inserted row ID, or 0 on failure.
	 */
	public static function insert(
		$form_id,
		array $values,
		$status = 'received',
		$reason = '',
		array $context = array()
	) {
		global $wpdb;

		$agent = isset( $_SERVER['HTTP_USER_AGENT'] )
			? substr( sanitize_text_field( wp_unslash( $_SERVER['HTTP_USER_AGENT'] ) ), 0, 255 )
			: '';

		$schema   = Schema::get_live( $form_id ) ?: Schema::defaults();
		$privacy  = $schema['privacy'] ?? array();
		$days     = self::retention_for( $schema );
		$snapshot = self::snapshot( $form_id, $schema );
		$page     = substr( (string) wp_parse_url( Submission::current_url(), PHP_URL_PATH ), 0, 255 );

		$data    = array(
			'form_id'        => (int) $form_id,
			'created_at'     => EntryTime::utc(),
			'status'         => in_array( $status, array( 'received', 'review', 'spam' ), true ) ? $status : 'received',
			'spam_reason'    => $reason ? substr( $reason, 0, 40 ) : null,
			'is_read'        => 0,
			'fields'         => wp_json_encode( $values, self::JSON_FLAGS ),
			'snapshot'       => wp_json_encode( $snapshot, self::JSON_FLAGS ),
			'request_key'    => $context['request_key'] ?? null,
			'payload_hash'   => $context['payload_hash'] ?? null,
			'retention_days' => $days,
			'expires_at'     => $days > 0 ? gmdate( 'Y-m-d H:i:s', time() + $days * DAY_IN_SECONDS ) : null,
			'page_url'       => $page,
			'mail_status'    => null,
			'mail_error'     => null,
			'ip_hash'        => ! empty( $privacy['minimal_metadata'] ) ? '' : Spam::ip_hash(),
			'user_agent'     => ! empty( $privacy['minimal_metadata'] ) ? '' : $agent,
		);
		$formats = array( '%d', '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%s', '%s' );
		$data['time_version'] = EntryTime::VERSION;
		$formats[] = '%d';
		if ( self::search_column_ready() ) {
			$data['search_text'] = self::search_text( $values, $snapshot['schema'], array() );
			$formats[]           = '%s';
		}

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table.
		$ok = $wpdb->insert( self::table_name(), $data, $formats );

		return $ok ? (int) $wpdb->insert_id : 0;
	}

	/**
	 * Store a message received elsewhere: by another form plugin on this site, or
	 * by Accessible Forms on another site. No notification is sent, since the
	 * message was handled where it arrived. Importing the same message again
	 * changes nothing: its source key becomes the request key.
	 *
	 * @param int   $form_id Form ID.
	 * @param array $values  Answers keyed by field key.
	 * @param array $args    `key` unique source ID; `created_at` UTC as Y-m-d H:i:s;
	 *                       `status` received, review or spam; `read`; `page` path;
	 *                       `source` label; `keep` true to keep until deleted rather
	 *                       than follow the retention policy from the original date;
	 *                       `time_version` when the source's dates have an older
	 *                       provenance than this site's.
	 * @return int|string Entry ID, `duplicate`, or 0 when it could not be stored.
	 */
	public static function import( $form_id, array $values, array $args ) {
		global $wpdb;
		$request = hash( 'sha256', 'import:' . (string) ( $args['key'] ?? '' ) );
		if ( '' === (string) ( $args['key'] ?? '' ) ) {
			return 0;
		}
		if ( self::by_request( $request ) ) {
			return 'duplicate';
		}
		$schema  = Schema::get_live( $form_id ) ?: ( Schema::get( $form_id, true ) ?: Schema::defaults() );
		$created = $args['created_at'] ?? EntryTime::utc();
		$parsed  = is_string( $created ) ? EntryTime::parse( $created ) : null;
		if ( null === $parsed ) {
			return 0;
		}
		$version = isset( $args['time_version'] ) ? max( 0, min( EntryTime::VERSION, (int) $args['time_version'] ) ) : EntryTime::VERSION;
		$days    = empty( $args['keep'] ) ? self::retention_for( $schema ) : self::IMPORT_KEEP;
		$status  = in_array( $args['status'] ?? '', array( 'spam', 'review' ), true ) ? $args['status'] : 'received';
		$snapshot = is_array( $args['snapshot'] ?? null )
			? self::portable_snapshot( array( 'fields' => $values, 'snapshot' => $args['snapshot'] ) )
			: self::snapshot( $form_id, $schema );
		$snapshot['imported'] = array(
			'source' => sanitize_text_field( (string) ( $args['source'] ?? '' ) ),
			'at'     => EntryTime::utc(),
		);
		$data    = array(
			'form_id'        => (int) $form_id,
			'created_at'     => $created,
			'status'         => $status,
			'spam_reason'    => 'spam' === $status ? 'imported' : null,
			'is_read'        => empty( $args['read'] ) ? 0 : 1,
			'fields'         => wp_json_encode( $values, self::JSON_FLAGS ),
			'snapshot'       => wp_json_encode( $snapshot, self::JSON_FLAGS ),
			'request_key'    => $request,
			'payload_hash'   => hash( 'sha256', (string) wp_json_encode( $values, self::JSON_FLAGS ) ),
			'retention_days' => $days,
			'expires_at'     => $days > 0 && ( $version & 1 ) ? EntryTime::utc( $parsed + $days * DAY_IN_SECONDS ) : null,
			'page_url'       => substr( (string) ( $args['page'] ?? '' ), 0, 255 ),
			// Nothing to send: the message was handled where it arrived.
			'mail_status'    => 'skipped',
			'mail_error'     => null,
			// Addresses and browsers from another system are not carried over.
			'ip_hash'        => '',
			'user_agent'     => '',
			'time_version'   => $version,
		);
		$formats = array( '%d', '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%d' );
		if ( self::search_column_ready() ) {
			$data['search_text'] = self::search_text( $values, $snapshot['schema'], array() );
			$formats[]           = '%s';
		}
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table.
		$ok = $wpdb->insert( self::table_name(), $data, $formats );
		return $ok ? (int) $wpdb->insert_id : 0;
	}

	/** Days a new entry of this form is kept: the form's own policy, else the site's. */
	private static function retention_for( array $schema ) {
		$privacy = $schema['privacy'] ?? array();
		return isset( $privacy['retention_days'] ) && (int) $privacy['retention_days'] >= 0
			? (int) $privacy['retention_days']
			: (int) Settings::get()['retention_days'];
	}

	/** The form as it was when an entry arrived, without destinations and secrets. */
	private static function snapshot( $form_id, array $schema ) {
		// Integration destinations and secrets never stay with an entry. Active add-ons
		// remove their own; data of inactive ones, which nothing can read, is left out.
		$kept = array_diff_key( $schema, array_flip( array( 'webhook', 'access' ) ) );
		foreach ( Extensions::inactive( $kept['extensions'] ?? array() ) as $slug ) {
			unset( $kept['extensions'][ $slug ] );
		}
		foreach ( (array) ( $kept['fields'] ?? array() ) as $index => $field ) {
			foreach ( Extensions::inactive( $field['extensions'] ?? array() ) as $slug ) {
				unset( $kept['fields'][ $index ]['extensions'][ $slug ] );
			}
		}
		return array(
			'title'   => get_the_title( $form_id ),
			'version' => Revisions::version( $schema ),
			'schema'  => (array) Extensions::filter( 'accessible_forms_entry_snapshot', $kept ),
		);
	}

	/** Historical answer labels only; destinations, secrets and add-on settings never travel. */
	public static function portable_snapshot( array $entry ) {
		$snapshot = is_array( $entry['snapshot'] ?? null ) ? $entry['snapshot'] : array();
		$fields   = array();
		foreach ( (array) ( $snapshot['schema']['fields'] ?? array() ) as $field ) {
			if ( is_array( $field ) && is_string( $field['name'] ?? null ) ) {
				$fields[ $field['name'] ] = $field;
			}
		}
		$kept = array();
		foreach ( (array) ( $entry['fields'] ?? array() ) as $name => $value ) {
			$field  = $fields[ $name ] ?? array();
			$labels = array();
			foreach ( (array) ( $field['option_labels'] ?? array() ) as $option => $label ) {
				$labels[ $option ] = FormOptions::text( $label, 500 );
			}
			$kept[] = array(
				'name'          => (string) $name,
				'uid'           => FormOptions::text( $field['uid'] ?? '', 100 ),
				'label'         => FormOptions::text( $field['label'] ?? $entry['labels'][ $name ] ?? $name, 500 ),
				'type'          => FormOptions::text( $field['type'] ?? 'text', 100 ),
				'option_labels' => $labels,
			);
		}
		return array(
			'title'   => FormOptions::text( $snapshot['title'] ?? $entry['form_title'] ?? '', 500 ),
			'version' => FormOptions::text( $snapshot['version'] ?? 'legacy', 100 ),
			'schema'  => array( 'fields' => $kept ),
		);
	}

	/**
	 * Record the outcome of the notification email.
	 *
	 * @param int    $id     Entry ID.
	 * @param string $status 'sent', 'failed' or 'skipped'.
	 * @param string $error  Error detail, when the send failed.
	 */
	public static function set_mail_status( $id, $status, $error = '' ) {
		global $wpdb;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->update(
			self::table_name(),
			array(
				'mail_status' => substr( (string) $status, 0, 20 ),
				'mail_error'  => $error ? (string) $error : null,
			),
			array( 'id' => (int) $id ),
			array( '%s', '%s' ),
			array( '%d' )
		);
	}

	/**
	 * Mark entries read or unread.
	 *
	 * @param int[] $ids  Entry IDs.
	 * @param bool  $read Whether they are read.
	 * @return int Rows changed.
	 */
	public static function set_read( array $ids, $read = true ) {
		return self::bulk_update( $ids, array( 'is_read' => $read ? 1 : 0 ), array( '%d' ) );
	}

	/**
	 * Move entries between received and spam.
	 *
	 * @param int[]  $ids    Entry IDs.
	 * @param string $status 'received' or 'spam'.
	 * @return int Rows changed.
	 */
	public static function set_status( array $ids, $status, $reason = null ) {
		$status = in_array( $status, array( 'received', 'review', 'spam', 'trash' ), true ) ? $status : 'received';
		if ( null !== $reason ) {
			$reason = '' === (string) $reason ? null : substr( (string) $reason, 0, 40 );
		}

		$changed = self::bulk_update(
			$ids,
			array(
				'status'      => $status,
				'spam_reason' => null !== $reason || 'spam' !== $status ? $reason : 'manual',
				'trashed_at'  => 'trash' === $status ? current_time( 'mysql', true ) : null,
			),
			array( '%s', '%s', '%s' )
		);
		if ( 'received' !== $status ) {
			foreach ( $ids as $id ) {
				Notifications::cancel( absint( $id ) );
			}
		}
		return $changed;
	}

	/**
	 * Apply the same column values to several rows.
	 *
	 * @param int[] $ids     Entry IDs.
	 * @param array $data    Column => value.
	 * @param array $formats Value formats.
	 * @return int Rows changed.
	 */
	private static function bulk_update( array $ids, array $data, array $formats ) {
		global $wpdb;

		$ids     = array_filter( array_map( 'intval', $ids ) );
		$changed = 0;

		foreach ( $ids as $id ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$changed += (int) $wpdb->update( self::table_name(), $data, array( 'id' => $id ), $formats, array( '%d' ) );
		}

		return $changed;
	}

	/**
	 * Number of unread, non-spam entries.
	 *
	 * @return int
	 */
	public static function unread_count() {
		global $wpdb;

		$table = self::table_name();

		return (int) $wpdb->get_var(
			$wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE status = 'received' AND is_read = 0", $table )
		);
	}

	/**
	 * Number of submissions from the last week whose notification email
	 * failed, limited to the forms the current user may read.
	 *
	 * @return int
	 */
	public static function recent_mail_failures() {
		return self::count(
			Capabilities::query(
				array(
					'mail_status'  => 'failed',
					'failed_since' => true,
				)
			)
		);
	}

	/**
	 * Condition for "a notification failed in the last 7 days": a failed job
	 * last tried in that window, or a submission from that window whose
	 * notification failed before jobs were recorded. A resend that fails on
	 * an older submission counts, because the failure itself is recent.
	 *
	 * Admin\Inbox::latest_failure() repeats this window in its own query.
	 *
	 * @return array{0: string, 1: array} SQL fragment and its parameters.
	 */
	public static function recent_failure_clause() {
		return array(
			"(created_at >= %s OR (time_version & 1) = 0 OR id IN (SELECT entry_id FROM %i WHERE status = 'failed' AND next_at >= %s))",
			array(
				self::recent_failure_cutoff(),
				Notifications::table(),
				// Jobs are timed in UTC; a failed job's next_at is set at its last attempt.
				gmdate( 'Y-m-d H:i:s', time() - WEEK_IN_SECONDS ),
			),
		);
	}

	/**
	 * Start of the "last 7 days" window for failed notifications, in the
	 * UTC. Historical dates without confirmed provenance remain visibly unconfirmed.
	 *
	 * @return string
	 */
	public static function recent_failure_cutoff() {
		return EntryTime::utc( time() - WEEK_IN_SECONDS );
	}

	/**
	 * Days that spam and held-for-review submissions are kept before the daily
	 * cleanup deletes them.
	 *
	 * @return int
	 */
	public static function held_retention_days() {
		return max( 1, (int) ( Settings::get()['spam_retention_days'] ?? 30 ) );
	}

	/**
	 * When the daily cleanup will delete a spam or held-for-review submission.
	 *
	 * The cleanup deletes these once they are older than the spam retention
	 * period, or earlier if the submission's own retention runs out first.
	 *
	 * @param array $entry Hydrated entry.
	 * @return int Unix timestamp, or 0 when the entry is not deleted this way.
	 */
	public static function held_expiry( array $entry ) {
		if ( self::IMPORT_KEEP === (int) ( $entry['retention_days'] ?? -1 ) || ! in_array( $entry['status'] ?? '', array( 'spam', 'review' ), true ) ) {
			return 0;
		}
		$created = EntryTime::timestamp( $entry );
		$expiry = null === $created ? 0 : $created + self::held_retention_days() * DAY_IN_SECONDS;
		if ( ! empty( $entry['expires_at'] ) ) {
			$own = strtotime( $entry['expires_at'] . ' UTC' );
			if ( $own && ( ! $expiry || $own < $expiry ) ) {
				$expiry = $own;
			}
		}
		return $expiry;
	}

	/**
	 * Held submissions the current user can read: how many there are, and how
	 * many the daily cleanup will delete within the given number of days.
	 *
	 * @param string $status 'review' or 'spam'.
	 * @param int    $within Look-ahead in days.
	 * @return array{total:int, expiring:int}
	 */
	public static function held_counts( $status = 'review', $within = 7 ) {
		$status = 'spam' === $status ? 'spam' : 'review';

		return array(
			'total'    => self::count( Capabilities::query( array( 'status' => $status ) ) ),
			'expiring' => self::count(
				Capabilities::query(
					array(
						'status'        => $status,
						'held_expiring' => max( 0, (int) $within ),
					)
				)
			),
		);
	}

	/**
	 * Counts behind the admin menu badge: unread received submissions and
	 * submissions held for review, for the current user.
	 *
	 * @return array{unread:int, review:int}
	 */
	public static function attention_counts() {
		if ( ! current_user_can( 'read_accessible_form_entries' ) ) {
			return array(
				'unread' => 0,
				'review' => 0,
			);
		}
		return array(
			'unread' => self::count(
				Capabilities::query(
					array(
						'status'  => 'received',
						'is_read' => 0,
					)
				)
			),
			'review' => self::count( Capabilities::query( array( 'status' => 'review' ) ) ),
		);
	}

	/**
	 * Fetch a single entry.
	 *
	 * @param int $id Entry ID.
	 * @return array|null
	 */
	public static function get( $id ) {
		global $wpdb;

		$table = self::table_name();

		$row = $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', $table, (int) $id ), ARRAY_A );

		return $row ? self::hydrate( $row ) : null;
	}

	/**
	 * The neighbouring entry in a given direction, for prev/next navigation.
	 *
	 * @param array  $entry     Current entry.
	 * @param string $direction 'prev' (newer) or 'next' (older).
	 * @return int Entry ID, or 0.
	 */
	public static function adjacent_id( array $entry, $direction, array $view = array() ) {
		$args = array_merge(
			$view ?: array(
				'form_id' => $entry['form_id'],
				'status'  => $entry['status'],
			),
			array(
				'orderby'  => 'id',
				'order'    => 'prev' === $direction ? 'ASC' : 'DESC',
				'per_page' => 1,
				'page'     => 1,
			)
		);
		$args[ 'prev' === $direction ? 'after_id' : 'max_id' ] =
			'prev' === $direction ? $entry['id'] : $entry['id'] - 1;
		$rows = self::query( Capabilities::query( $args ) );
		return $rows ? $rows[0]['id'] : 0;
	}
	/**
	 * Summary numbers for one form. Counts of received, review and spam entries
	 * cover the period when $days is set; unresolved is always the current total.
	 *
	 * @param int $form_id Form ID.
	 * @param int $days    Period in days, or 0 for everything still stored.
	 * @return array Empty when the current user cannot read this form's entries.
	 */
	public static function overview( $form_id, $days = 0 ) {
		if ( ! Capabilities::form( $form_id ) ) {
			return array();
		}
		global $wpdb;
		$since = $days > 0 ? EntryTime::utc( time() - (int) $days * DAY_IN_SECONDS ) : '0001-01-01 00:00:00';
		$window = (int) ( $days > 0 );
		$row   = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT
					SUM(CASE WHEN status = 'received' AND (%d = 0 OR (time_version & 1) = 1) AND created_at >= %s THEN 1 ELSE 0 END) AS received,
					SUM(CASE WHEN status = 'received' AND is_read = 0 THEN 1 ELSE 0 END) AS unread,
					SUM(CASE WHEN status = 'received' AND workflow <> 'resolved' THEN 1 ELSE 0 END) AS unresolved,
					SUM(CASE WHEN status = 'review' AND (%d = 0 OR (time_version & 1) = 1) AND created_at >= %s THEN 1 ELSE 0 END) AS review,
					SUM(CASE WHEN status = 'spam' AND (%d = 0 OR (time_version & 1) = 1) AND created_at >= %s THEN 1 ELSE 0 END) AS spam,
					SUM(CASE WHEN time_version <> 7 THEN 1 ELSE 0 END) AS time_unconfirmed,
					SUM(CASE WHEN status = 'received' AND mail_status IN ('failed','uncertain') THEN 1 ELSE 0 END) AS mail_issues
				FROM %i WHERE form_id = %d",
				$window, $since,
				$window, $since,
				$window, $since,
				self::table_name(),
				$form_id
			),
			ARRAY_A
		);
		$out   = array_map( 'intval', $row ?: array() );
		if ( $days > 0 ) {
			$out['resolve_median'] = self::median_seconds( $form_id, 'resolved_at', $since );
			$out['reply_median']   = self::median_seconds( $form_id, 'first_reply_at', $since );
		}
		return $out;
	}

	/**
	 * Current unread, unresolved and needs-review counts for several forms in
	 * one query. Callers must pass only forms the current user may read.
	 *
	 * @param int[] $form_ids Form IDs.
	 * @return array Form ID => counts.
	 */
	public static function overview_many( array $form_ids ) {
		$form_ids = array_values( array_filter( array_map( 'absint', $form_ids ) ) );
		if ( ! $form_ids ) {
			return array();
		}
		global $wpdb;
		// One grouped pass over the form_created index; filtered to the
		// readable forms below, which keeps the SQL fully static.
		$rows   = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT form_id,
					SUM(CASE WHEN status = 'received' AND is_read = 0 THEN 1 ELSE 0 END) AS unread,
					SUM(CASE WHEN status = 'received' AND workflow <> 'resolved' THEN 1 ELSE 0 END) AS unresolved,
					SUM(CASE WHEN status = 'review' THEN 1 ELSE 0 END) AS review
				FROM %i WHERE form_id IN (" . implode( ',', array_fill( 0, count( $form_ids ), '%d' ) ) . ") GROUP BY form_id",
				array_merge( array( self::table_name() ), $form_ids )
			),
			ARRAY_A
		);
		$wanted = array_flip( $form_ids );
		$out    = array();
		foreach ( $rows ?: array() as $row ) {
			if ( isset( $wanted[ (int) $row['form_id'] ] ) ) {
				$out[ (int) $row['form_id'] ] = array_map( 'intval', $row );
			}
		}
		return $out;
	}

	/**
	 * Median time from submission to a later timestamp, for entries whose
	 * timestamp falls in the period. Based on the most recent 1,000 entries.
	 *
	 * @param int    $form_id Form ID.
	 * @param string $column  'resolved_at' or 'first_reply_at'.
	 * @param string $since   Period start (UTC).
	 * @return array{seconds:int|null, count:int}
	 */
	private static function median_seconds( $form_id, $column, $since ) {
		global $wpdb;
		$column = 'first_reply_at' === $column ? 'first_reply_at' : 'resolved_at';
		$bits = EntryTime::FIELDS[ $column ] | 1;
		$rows   = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT created_at, %i AS done FROM %i WHERE form_id = %d AND status = %s AND (time_version & %d) = %d AND %i IS NOT NULL AND %i >= %s ORDER BY %i DESC LIMIT 1000',
				$column,
				self::table_name(),
				$form_id,
				'received',
				$bits,
				$bits,
				$column,
				$column,
				$since,
				$column
			),
			ARRAY_A
		);
		$spans = array();
		foreach ( $rows ?: array() as $row ) {
			$start = EntryTime::parse( $row['created_at'] ); $end = EntryTime::parse( $row['done'] );
			if ( null !== $start && null !== $end && $end >= $start ) { $spans[] = $end - $start; }
		}
		return array(
			'seconds' => self::median( $spans ),
			'count'   => count( $spans ),
		);
	}

	/**
	 * Median of a list of numbers.
	 *
	 * @param int[] $values Values.
	 * @return int|null Null for an empty list.
	 */
	public static function median( array $values ) {
		if ( ! $values ) {
			return null;
		}
		sort( $values );
		$middle = intdiv( count( $values ), 2 );
		return count( $values ) % 2
			? (int) $values[ $middle ]
			: (int) round( ( $values[ $middle - 1 ] + $values[ $middle ] ) / 2 );
	}

	/**
	 * Query entries.
	 *
	 * @param array $args form_id, status, search, orderby, order, per_page, page.
	 * @return array[]
	 */
	public static function query( array $args = array() ) {
		global $wpdb;

		$args = wp_parse_args(
			$args,
			array(
				'form_id'  => 0,
				'status'   => '',
				'search'   => '',
				'orderby'  => 'created_at',
				'order'    => 'DESC',
				'per_page' => 20,
				'page'     => 1,
			)
		);

		$table            = self::table_name();
		[$where, $params] = self::build_where( $args );

		$orderby = in_array( $args['orderby'], array( 'created_at', 'id', 'form_id', 'status' ), true )
			? $args['orderby']
			: 'created_at';
		$order   = 'ASC' === strtoupper( $args['order'] ) ? 'ASC' : 'DESC';

		$per_page = max( 1, min( 500, (int) $args['per_page'] ) );
		$offset   = max( 0, ( (int) $args['page'] - 1 ) * $per_page );

		$params[] = $per_page;
		$params[] = $offset;

		// Find a page of IDs first. Deep pages must not load every skipped answer/snapshot.
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table, column and direction are allow-listed above.
		// Unscoped substring searches need a sequential text scan. MySQL can otherwise
		// walk the date index and fetch a million large rows individually for a rare term.
		$scan = ! empty( $args['search'] ) && empty( $args['form_id'] ) && empty( $args['date_from'] ) && empty( $args['date_to'] ) && 'created_at' === $orderby ? 'FORCE INDEX (PRIMARY)' : '';
		$sql = "SELECT id FROM {$table} {$scan} {$where} ORDER BY {$orderby} {$order}, id {$order} LIMIT %d OFFSET %d";

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- SQL structure is internal; column/order are allowlisted and values are prepared.
		$ids = $wpdb->get_col( $wpdb->prepare( $sql, $params ) );
		if ( ! $ids ) { return array(); }
		// Repeat the filters while fetching: a changed status/access scope cannot leak a row.
		[$where, $params] = self::build_where( $args );
		$sql = 'SELECT * FROM ' . $table . ' FORCE INDEX (PRIMARY) ' . ( $where ?: 'WHERE 1=1' ) . ' AND id IN (' . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ')';
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Internal SQL shape and prepared values; IDs originate in the scoped first query.
		$rows = $wpdb->get_results( $wpdb->prepare( $sql, array_merge( $params, $ids ) ), ARRAY_A );
		$rows = array_column( $rows ?: array(), null, 'id' );
		$out = array();
		foreach ( $ids as $id ) { if ( isset( $rows[ $id ] ) ) { $out[] = self::hydrate( $rows[ $id ] ); } }
		return $out;
	}

	/**
	 * Count entries matching a query.
	 *
	 * @param array $args Same shape as query().
	 * @return int
	 */
	public static function count( array $args = array() ) {
		global $wpdb;

		$args  = wp_parse_args(
			$args,
			array(
				'form_id' => 0,
				'status'  => '',
				'search'  => '',
			)
		);
		$table = self::table_name();

		[$where, $params] = self::build_where( $args );

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is internal.
		$sql = "SELECT COUNT(*) FROM {$table} {$where}";

		if ( $params ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- SQL structure is internal; column/order are allowlisted and values are prepared.
			return (int) $wpdb->get_var( $wpdb->prepare( $sql, $params ) );
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- SQL structure is internal; column/order are allowlisted and values are prepared.
		return (int) $wpdb->get_var( $sql );
	}

	/**
	 * Delete entries by ID.
	 *
	 * @param int[] $ids Entry IDs.
	 * @return int Rows removed.
	 */
	public static function delete( array $ids ) {
		global $wpdb;

		$ids = array_filter( array_map( 'intval', $ids ) );

		if ( ! $ids ) {
			return 0;
		}

		$table   = self::table_name();
		$removed = 0;
		foreach ( $ids as $id ) {
			if ( ! self::get( $id ) ) { continue; }
			if ( false === $wpdb->query( 'BEGIN' ) ) {
				continue;
			}
			// Erasing listeners still see the entry, and owned database changes share the transaction.
			if ( ! Extensions::notify( 'accessible_forms_entry_erasing', $id ) ||
				! Notifications::forget( $id ) || ! Files::forget( $id ) ||
				false === $wpdb->delete( Audit::table(), array( 'entry_id' => $id ), array( '%d' ) ) ||
				false === $wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE option_name LIKE %s', $wpdb->options, $wpdb->esc_like( 'accessible_forms_reply_' . $id . '_' ) . '%' ) ) ||
				1 !== $wpdb->delete( $table, array( 'id' => $id ), array( '%d' ) ) ) {
				$wpdb->query( 'ROLLBACK' );
				continue;
			}
			if ( false === $wpdb->query( 'COMMIT' ) ) {
				$wpdb->query( 'ROLLBACK' );
				continue;
			}
			++$removed;
		}
		return $removed;
	}

	/**
	 * Daily retention purge.
	 */
	public static function purge() {
		global $wpdb;
		$settings  = Settings::get();
		$days      = (int) $settings['retention_days'];
		$spam_days = max( 1, (int) ( $settings['spam_retention_days'] ?? 30 ) );
		$now       = gmdate( 'Y-m-d H:i:s' );
		$legacy    = $days > 0 ? EntryTime::utc( time() - $days * DAY_IN_SECONDS ) : '0001-01-01 00:00:00';
		$ids       = $wpdb->get_col(
			$wpdb->prepare(
				"SELECT id FROM %i WHERE (retention_days <> %d AND ((expires_at IS NOT NULL AND expires_at <= %s) OR ((time_version & 1) = 1 AND retention_days = -1 AND created_at < %s) OR ((time_version & 1) = 1 AND status IN ('spam','review') AND created_at < %s))) OR (status = 'trash' AND trashed_at < %s) ORDER BY id LIMIT 500",
				self::table_name(),
				self::IMPORT_KEEP,
				$now,
				$legacy,
				EntryTime::utc( time() - $spam_days * DAY_IN_SECONDS ),
				gmdate( 'Y-m-d H:i:s', time() - 30 * DAY_IN_SECONDS )
			)
		);
		self::delete( $ids ?: array() );
		update_option( 'accessible_forms_last_cleanup', time(), false );
		if ( count( $ids ?: array() ) === 500 && ! wp_next_scheduled( 'accessible_forms_purge_batch' ) ) {
			wp_schedule_single_event( time() + 60, 'accessible_forms_purge_batch' );
		}
	}

	/**
	 * Build a WHERE clause and its parameters.
	 *
	 * @param array $args Query arguments.
	 * @return array{0: string, 1: array}
	 */
	private static function build_where( array $args ) {
		$clauses = array();
		$params  = array();
		if ( ! empty( $args['_unconfirmed_time'] ) ) { $clauses[] = 'time_version <> 7'; }

		foreach ( array(
			'after_id' => '>',
			'max_id'   => '<=',
		) as $key => $operator ) {
			if ( isset( $args[ $key ] ) ) {
				$clauses[] = 'id ' . $operator . ' %d';
				$params[]  = max( 0, (int) $args[ $key ] );
			}
		}

		if ( ! empty( $args['form_id'] ) ) {
			$clauses[] = 'form_id = %d';
			$params[]  = (int) $args['form_id'];
		}

		if ( ! empty( $args['status'] ) ) {
			$clauses[] = 'status = %s';
			$params[]  = in_array( $args['status'], array( 'spam', 'review', 'trash', 'received' ), true )
				? $args['status']
				: 'received';
		}

		if ( empty( $args['status'] ) && ! empty( $args['_access_cap'] ) ) {
			$clauses[] = "status <> 'trash'";
		}
		// Unread is a view of received entries, never a stored entry status.
		if ( 'unread' === ( $args['status'] ?? '' ) ) {
			$clauses[] = 'is_read = 0';
		}
		if ( ! empty( $args['search'] ) ) {
			[$search_clause, $search_params] = self::search_clause( (string) $args['search'] );

			$clauses[] = $search_clause;
			$params    = array_merge( $params, $search_params );
		}
		// Internal filters, never read from a request.
		if ( isset( $args['held_expiring'] ) ) {
			// Never infer retention from an unconfirmed historical wall-clock value.
			$within    = (int) $args['held_expiring'] * DAY_IN_SECONDS;
			$clauses[] = '(retention_days <> %d AND (((time_version & 1) = 1 AND created_at < %s) OR (expires_at IS NOT NULL AND expires_at < %s)))';
			$params[]  = self::IMPORT_KEEP;
			$params[]  = EntryTime::utc( time() + $within - self::held_retention_days() * DAY_IN_SECONDS );
			$params[]  = gmdate( 'Y-m-d H:i:s', time() + $within );
		}
		if ( ! empty( $args['failed_since'] ) ) {
			[$failure_clause, $failure_params] = self::recent_failure_clause();

			$clauses[] = $failure_clause;
			$params    = array_merge( $params, $failure_params );
		}

		foreach ( array( 'workflow', 'mail_status', 'assignee', 'is_read' ) as $filter ) {
			if ( isset( $args[ $filter ] ) && '' !== (string) $args[ $filter ] ) {
				$clauses[] = $filter . ' = %s';
				$params[]  = (string) $args[ $filter ];
			}
		}
		foreach ( array(
			'date_from' => '>=',
			'date_to'   => '<',
		) as $filter => $comparison ) {
			if ( ! empty( $args[ $filter ] ) && preg_match( '/^\d{4}-\d{2}-\d{2}$/', $args[ $filter ] ) ) {
				$bounds = EntryTime::day_bounds( $args[ $filter ] );
				if ( ! $bounds ) { $clauses[] = '1 = 0'; continue; }
				$clauses[] = '(((time_version & 1) = 1 AND created_at ' . $comparison . ' %s) OR ((time_version & 1) = 0 AND created_at ' . $comparison . ' %s))';
				$params[] = $bounds[ 'date_to' === $filter ? 1 : 0 ];
				$calendar = new \DateTimeImmutable( $args[ $filter ], new \DateTimeZone( 'UTC' ) );
				$params[] = ( 'date_to' === $filter ? $calendar->modify( '+1 day' ) : $calendar )->format( 'Y-m-d 00:00:00' );
			}
		}
		if ( ! empty( $args['_access_cap'] ) ) {
			global $wpdb;
			if ( ! current_user_can( $args['_access_cap'] ) ) {
				$clauses[] = '1 = 0';
			} elseif ( ! current_user_can( 'manage_accessible_forms_settings' ) ) {
				$clauses[] = "form_id IN (SELECT ID FROM {$wpdb->posts} WHERE post_type = 'accessible_form')";
				// Add-ons narrow access per form with the same boundary as Capabilities::form().
				$scope = Extensions::filter( 'accessible_forms_entries_access_sql', Capabilities::access_scope( get_current_user_id() ), get_current_user_id() );
				if ( is_array( $scope ) && 2 === count( $scope ) ) {
					$clauses = array_merge( $clauses, (array) $scope[0] );
					$params  = array_merge( $params, (array) $scope[1] );
				}
			}
		}

		$where = $clauses ? 'WHERE ' . implode( ' AND ', $clauses ) : '';

		return array( $where, $params );
	}

	/**
	 * The search condition and its parameters.
	 *
	 * Searches the answers and private notes as plain text, never the JSON
	 * keys around them, so searching for a question's name does not match
	 * every entry. An entry not yet indexed by the upgrade is matched against
	 * its stored JSON as before, including the escaped form of non-Latin text.
	 *
	 * @param string $term Search text.
	 * @return array{0: string, 1: array}
	 */
	private static function search_clause( $term ) {
		global $wpdb;

		$like = '%' . $wpdb->esc_like( $term ) . '%';

		$legacy  = array( 'fields LIKE %s', 'notes LIKE %s' );
		$params  = array( $like, $like );
		$escaped = substr( (string) wp_json_encode( $term ), 1, -1 );
		if ( '' !== $escaped && $escaped !== $term ) {
			// Older answers and notes both stored non-Latin text escaped.
			$legacy[] = 'fields LIKE %s';
			$legacy[] = 'notes LIKE %s';
			$params[] = '%' . $wpdb->esc_like( $escaped ) . '%';
			$params[] = '%' . $wpdb->esc_like( $escaped ) . '%';
		}
		$legacy = '(' . implode( ' OR ', $legacy ) . ')';
		if ( self::search_column_ready() ) {
			return array(
				'(search_text LIKE %s OR (search_text IS NULL AND ' . $legacy . '))',
				array_merge( array( $like ), $params ),
			);
		}
		return array( $legacy, $params );
	}

	/**
	 * Whether the search_text column exists, so entries should fill it.
	 *
	 * @return bool
	 */
	public static function search_column_ready() {
		return (int) get_option( self::SEARCH_OPTION, 0 ) >= 1;
	}

	/**
	 * Plain searchable text for an entry: every answer, the choice labels shown
	 * for those answers, and the private notes, one per line.
	 *
	 * @param array $values Stored answers.
	 * @param array $schema Form schema saved with the entry.
	 * @param array $notes  Private notes and replies.
	 * @return string
	 */
	public static function search_text( array $values, array $schema, array $notes ) {
		$parts   = array();
		$collect = static function ( $value ) use ( &$parts ) {
			if ( is_scalar( $value ) ) {
				$parts[] = (string) $value;
			}
		};
		array_walk_recursive( $values, $collect );
		if ( $schema ) {
			$shown = Choices::display( $schema, $values );
			array_walk_recursive( $shown, $collect );
		}
		foreach ( $notes as $note ) {
			if ( is_array( $note ) ) {
				$collect( $note['text'] ?? '' );
				$collect( $note['subject'] ?? '' );
			}
		}
		$parts = array_unique( array_filter( array_map( 'trim', $parts ), 'strlen' ) );

		return implode( "\n", $parts );
	}

	/**
	 * Re-encode a row's answers and snapshot without escaping and fill its
	 * search text.
	 * Used by the upgrade in Migration.
	 *
	 * @param array $row Raw row with id, fields, snapshot and notes.
	 * @return bool Whether the row was written.
	 */
	public static function reindex_row( array $row ) {
		global $wpdb;

		$values   = json_decode( (string) $row['fields'], true );
		$snapshot = json_decode( (string) ( $row['snapshot'] ?? '' ), true );
		$notes    = json_decode( (string) ( $row['notes'] ?? '' ), true );

		// Unreadable answers keep a NULL search text, so search still falls back to the stored JSON.
		$data    = array(
			'search_text' => is_array( $values )
				? self::search_text(
					$values,
					is_array( $snapshot ) ? ( $snapshot['schema'] ?? array() ) : array(),
					is_array( $notes ) ? $notes : array()
				)
				: null,
		);
		$formats = array( '%s' );
		// Only JSON that decoded cleanly is rewritten, so nothing unreadable is lost.
		if ( is_array( $values ) ) {
			$data['fields'] = wp_json_encode( $values, self::JSON_FLAGS );
			$formats[]      = '%s';
		}
		if ( is_array( $snapshot ) ) {
			$data['snapshot'] = wp_json_encode( $snapshot, self::JSON_FLAGS );
			$formats[]        = '%s';
		}
		// Notes are left as stored: they can change while a batch runs, and
		// rewriting them from this earlier read could drop a note just added.

		return false !== $wpdb->update( self::table_name(), $data, array( 'id' => (int) $row['id'] ), $formats, array( '%d' ) );
	}

	/**
	 * Decode a raw row.
	 *
	 * @param array $row Database row.
	 * @return array
	 */
	private static function hydrate( array $row ) {
		$decoded         = json_decode( (string) $row['fields'], true );
		$row['snapshot'] = json_decode( (string) ( $row['snapshot'] ?? '' ), true ) ?: array();
		$row['notes']    = json_decode( (string) ( $row['notes'] ?? '' ), true ) ?: array();
		$row['fields']   = is_array( $decoded ) ? $decoded : array();
		$row['id']       = (int) $row['id'];
		$row['form_id']  = (int) $row['form_id'];
		$row['is_read']  = ! empty( $row['is_read'] );

		return $row;
	}

	/**
	 * Register the privacy exporter and eraser.
	 *
	 * Required because entries are not posts: WordPress's built-in tools would
	 * otherwise never see them.
	 */
	public static function register_privacy_hooks() {
		// Registered here because this runs on every request: the search index
		// upgrade keeps entries searchable without a plugin reactivation.
		Migration::register();

		add_filter(
			'wp_privacy_personal_data_exporters',
			static function ( $exporters ) {
				$exporters['accessible-forms'] = array(
					'exporter_friendly_name' => __( 'Accessible Forms submissions', 'accessible-forms-by-accessible-org' ),
					'callback'               => array( self::class, 'privacy_export' ),
				);

				return $exporters;
			}
		);

		add_filter(
			'wp_privacy_personal_data_erasers',
			static function ( $erasers ) {
				$erasers['accessible-forms'] = array(
					'eraser_friendly_name' => __( 'Accessible Forms submissions', 'accessible-forms-by-accessible-org' ),
					'callback'             => array( self::class, 'privacy_erase' ),
				);

				return $erasers;
			}
		);
	}

	/**
	 * Find entries whose values contain a given email address.
	 *
	 * @param string $email Email address.
	 * @return array[]
	 */
	private static function find_by_email( $email, $page = 1 ) {
		global $wpdb;

		$table = self::table_name();
		$email = sanitize_email( $email );
		if ( ! is_email( $email ) ) {
			return array();
		}
		// Match an entire JSON string, never a substring of another person's
		// email. Older rows escaped slashes and newer rows do not; match either.
		$like   = '%' . $wpdb->esc_like( wp_json_encode( $email, self::JSON_FLAGS ) ) . '%';
		$legacy = '%' . $wpdb->esc_like( wp_json_encode( $email ) ) . '%';
		$offset = max( 0, ( (int) $page - 1 ) * 100 );

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT * FROM %i WHERE fields LIKE %s OR fields LIKE %s ORDER BY id ASC LIMIT 100 OFFSET %d',
				$table,
				$like,
				$legacy,
				$offset
			),
			ARRAY_A
		);

		return array_map( array( self::class, 'hydrate' ), $rows ? $rows : array() );
	}

	/**
	 * Privacy exporter callback.
	 *
	 * @param string $email Email address being exported.
	 * @param int    $page  Page number, 100 entries per batch.
	 * @return array
	 */
	public static function privacy_export( $email, $page = 1 ) {
		$export = array();

		$entries = self::find_by_email( $email, $page );
		foreach ( $entries as $entry ) {
			$data = array(
				array(
					'name'  => __( 'Submitted', 'accessible-forms-by-accessible-org' ),
					'value' => EntryTime::export( $entry ),
				),
			);

			foreach (
				array(
					'page_url'   => __( 'Page', 'accessible-forms-by-accessible-org' ),
					'user_agent' => __( 'Browser', 'accessible-forms-by-accessible-org' ),
					'ip_hash'    => __( 'IP address hash', 'accessible-forms-by-accessible-org' ),
				)
				as $key => $label
			) {
				$data[] = array(
					'name'  => $label,
					'value' => (string) $entry[ $key ],
				);
			}
			$labels = self::labels( $entry );
			$data[] = array(
				'name'  => __( 'Original form version', 'accessible-forms-by-accessible-org' ),
				'value' => $entry['snapshot']['version'] ?? 'legacy',
			);
			foreach ( Files::listing( $entry['id'] ) as $file ) {
				$data[] = array(
					'name'  => __( 'Private attachment', 'accessible-forms-by-accessible-org' ),
					'value' => $file['filename'] . ' (' . size_format( $file['size'] ) . ')',
				);
			}
			foreach ( Notifications::history( $entry['id'] ) as $job ) {
				$data[] = array(
					'name'  => __( 'Notification history', 'accessible-forms-by-accessible-org' ),
					'value' => $job['status'] . ': ' . ( $job['history'] ?? '' ),
				);
			}
			foreach ( $entry['notes'] as $note ) {
				$data[] = array(
					'name'  => 'reply' === ( $note['kind'] ?? '' )
						? __( 'Reply sent to you', 'accessible-forms-by-accessible-org' )
						: __( 'Internal note', 'accessible-forms-by-accessible-org' ),
					'value' => $note['text'],
				);
			}
			foreach ( self::display_values( $entry ) as $key => $value ) {
				$data[] = array(
					'name'  => $labels[ $key ] ?? $key,
					'value' => is_array( $value ) ? implode( ', ', $value ) : (string) $value,
				);
			}

			$export[] = array(
				'group_id'    => 'accessible-forms',
				'group_label' => __( 'Accessible Forms submissions', 'accessible-forms-by-accessible-org' ),
				'item_id'     => 'af-entry-' . $entry['id'],
				'data'        => $data,
			);
		}

		return array(
			'data' => $export,
			'done' => count( $entries ) < 100,
		);
	}

	/**
	 * Privacy eraser callback.
	 *
	 * @param string $email Email address being erased.
	 * @param int    $page  Page number; each deletion reads the first remaining batch.
	 * @return array
	 */
	public static function privacy_erase( $email, $page = 1 ) {
		$ids = wp_list_pluck( self::find_by_email( $email, 1 ), 'id' );
		$n   = self::delete( $ids );

		return array(
			'items_removed'  => $n > 0,
			'items_retained' => $n < count( $ids ),
			'messages'       =>
				$n < count( $ids )
					? array( __( 'Some entries could not be erased. Please try again.', 'accessible-forms-by-accessible-org' ) )
					: array(),
			'done'           => count( $ids ) < 100 || $n < count( $ids ),
		);
	}

	/** Stable labels for new entries; legacy entries explicitly retain their field keys. */
	public static function display_values( array $entry ) {
		return Choices::display( $entry['snapshot']['schema'] ?? array(), $entry['fields'] );
	}
	public static function labels( array $entry ) {
		$labels = array();
		foreach ( $entry['snapshot']['schema']['fields'] ?? array() as $field ) {
			$labels[ $field['name'] ] = $field['label'];
		}
		return $labels;
	}

	public static function by_request( $key ) {
		global $wpdb;
		$row = $wpdb->get_row(
			$wpdb->prepare( 'SELECT * FROM %i WHERE request_key = %s', self::table_name(), $key ),
			ARRAY_A
		);
		return $row ? self::hydrate( $row ) : null;
	}

	public static function update_workflow( $id, array $input ) {
		$lease = Locks::acquire( 'entry:' . absint( $id ) );
		if ( ! $lease ) {
			return new \WP_Error(
				'busy',
				__( 'Another change is being saved. Try again in a moment.', 'accessible-forms-by-accessible-org' )
			);
		}
		try {
			return self::save_workflow( $id, $input );
		} finally {
			Locks::release( $lease );
		}
	}
	private static function save_workflow( $id, array $input ) {
		global $wpdb;
		$entry = self::get( $id );
		if ( ! Capabilities::entry( $entry, 'manage_accessible_form_entries' ) ) {
			return new \WP_Error( 'forbidden', __( 'You cannot manage this submission.', 'accessible-forms-by-accessible-org' ) );
		}
		$workflow = in_array( $input['workflow'] ?? '', array( 'new', 'in_progress', 'resolved' ), true )
			? $input['workflow']
			: $entry['workflow'];
		$assignee = absint( $input['assignee'] ?? $entry['assignee'] );
		if ( $assignee && ! Capabilities::form( $entry['form_id'], 'read_accessible_form_entries', $assignee ) ) {
			return new \WP_Error(
				'assignee',
				__( 'Choose a team member who can read this form’s submissions.', 'accessible-forms-by-accessible-org' )
			);
		}
		// A missing key keeps the current date; an empty value clears it.
		$due = sanitize_text_field( array_key_exists( 'due_date', $input ) ? (string) $input['due_date'] : (string) $entry['due_date'] );
		if (
			$due &&
			( ! preg_match( '/^\d{4}-\d{2}-\d{2}$/', $due ) ||
				! checkdate( (int) substr( $due, 5, 2 ), (int) substr( $due, 8, 2 ), (int) substr( $due, 0, 4 ) ) )
		) {
			return new \WP_Error( 'date', __( 'Choose a valid follow-up date.', 'accessible-forms-by-accessible-org' ) );
		}
		$notes = $entry['notes'];
		$note  = sanitize_textarea_field( $input['note'] ?? '' );
		if ( $note ) {
			$chars = function_exists( 'mb_strlen' ) ? mb_strlen( $note, 'UTF-8' ) : strlen( $note );
			if ( $chars > 4000 || strlen( $note ) > 16000 || count( $notes ) >= 100 ) {
				return new \WP_Error(
					'limit',
					__( 'Use no more than 4,000 characters per note and 100 notes per entry.', 'accessible-forms-by-accessible-org' )
				);
			}
			$record = array(
				'text'  => $note,
				'actor' => get_current_user_id(),
				'at'    => current_time( 'mysql', true ),
			);
			// Replies sent from the entry screen carry where and how they were sent.
			$meta = isset( $input['note_meta'] ) && is_array( $input['note_meta'] ) ? $input['note_meta'] : array();
			if ( 'reply' === ( $meta['kind'] ?? '' ) ) {
				$record['kind']     = 'reply';
				$record['to']       = sanitize_email( $meta['to'] ?? '' );
				$record['subject']  = sanitize_text_field( $meta['subject'] ?? '' );
				$record['delivery'] = in_array( $meta['delivery'] ?? '', array( 'uncertain', 'test' ), true ) ? $meta['delivery'] : 'sent';
				$record['token']    = preg_match( '/^[a-f0-9-]{36}$/', (string) ( $meta['token'] ?? '' ) ) ? $meta['token'] : '';
			}
			$notes[] = $record;
		}
		$now = EntryTime::utc();
		$mask = (int) ( $entry['time_version'] ?? 0 );
		// Stamp the moment an entry becomes resolved. An entry that was already
		// resolved keeps its value, including none for entries resolved before
		// this was recorded, so later edits never skew time-to-resolve.
		if ( 'resolved' !== $workflow ) {
			$resolved = null;
			$mask |= EntryTime::FIELDS['resolved_at'];
		} elseif ( 'resolved' === $entry['workflow'] ) {
			$resolved = $entry['resolved_at'] ?? null;
		} else {
			$resolved = $now;
			$mask |= EntryTime::FIELDS['resolved_at'];
		}
		$replied = ! empty( $entry['first_reply_at'] )
			? $entry['first_reply_at']
			: ( ! empty( $input['replied'] ) ? $now : null );
		if ( empty( $entry['first_reply_at'] ) ) { $mask |= EntryTime::FIELDS['first_reply_at']; }
		$data    = array(
			'workflow'       => $workflow,
			'assignee'       => $assignee,
			'due_date'       => $due ?: null,
			'notes'          => wp_json_encode( $notes, self::JSON_FLAGS ),
			'resolved_at'    => $resolved,
			'first_reply_at' => $replied,
			'time_version'   => $mask,
		);
		$formats = array( '%s', '%d', '%s', '%s', '%s', '%s', '%d' );
		if ( self::search_column_ready() ) {
			// Notes are searchable, so the search text follows them.
			$data['search_text'] = self::search_text( $entry['fields'], $entry['snapshot']['schema'] ?? array(), $notes );
			$formats[]           = '%s';
		}
		$ok = $wpdb->update( self::table_name(), $data, array( 'id' => $id ), $formats, array( '%d' ) );
		if ( false === $ok ) {
			return new \WP_Error(
				'storage',
				__( 'Your changes could not be saved. Please try again.', 'accessible-forms-by-accessible-org' )
			);
		}
		Audit::record( 'entry_updated', $entry['form_id'], $id );
		return true;
	}
}