<?php
namespace Careback;

defined( 'ABSPATH' ) || exit;

class Requests_Repository {
	/*
	 * Which statuses hold or have consumed units of a line is a property of the
	 * status (Statuses::consuming()), and which request types take no unit with
	 * them a property of the route (Routes::non_consuming_types()).
	 *
	 * `duplicate` holds nothing: it is a validation flag over an already counted
	 * set, so counting it would double up, while terminal outcomes keep their
	 * units claimed. Rejected and cancelled release theirs back to the pool.
	 *
	 * A guarantee claim holds nothing either. It is about a fault in an item the
	 * customer keeps: nothing is being sent back for good and nothing is being
	 * settled against the line. Counting it would make a second claim on the same
	 * item impossible — and a fault can recur, or be reported again after a
	 * repair — and would close the statutory routes on a line nobody has
	 * actually claimed.
	 */

	/** Longest note kept on a history entry written by an operator. */
	const NOTE_CHARS = 500;

	/** Longest browser string (user agent) kept on a request: the size of its column. */
	const USER_AGENT_MAX = 255;

	/**
	 * Filter: the row insert() writes ( array $row, array $data ). An add-on
	 * adds the columns it owns; the free part's columns are written as it set
	 * them.
	 */
	const INSERT_FILTER = 'careback_request_insert';

	/**
	 * Filter: the fields a change of the free part writes to a row ( array
	 * $fields, array $request, string $change ) — `status` (update_status()),
	 * `arrival_cleared` (clear_goods_arrival()). Same rule as the insert.
	 */
	const UPDATE_FILTER = 'careback_request_update';

	/**
	 * Filter: conditions the request list is narrowed by ( string[] $clauses,
	 * array $args ). Each a complete condition without placeholders.
	 */
	const WHERE_FILTER = 'careback_requests_where';

	/** Filter: orderings the request list knows, key => ORDER BY clause ( array $orderings ). */
	const ORDERINGS_FILTER = 'careback_requests_orderings';

	/** Filter: the columns for_order() reads ( string[] $columns ). */
	const ORDER_COLUMNS_FILTER = 'careback_order_request_columns';

	/** Flag of a request filed over units another request already holds. */
	const SUPERSEDES_FLAG = 'supersedes_claim';

	/** Flag of a request whose units a later statutory withdrawal reaches over. */
	const SUPERSEDED_FLAG = 'superseded_claim';

	/**
	 * Values the `goods_origin` column takes: the customer posted the parcel, the
	 * carrier sent it back uncollected, or the goods never left the warehouse.
	 * An empty column means no answer was recorded.
	 */
	const GOODS_ORIGINS = array( 'customer', 'carrier_return', 'not_dispatched' );

	/** Origin assumed when a status change records an arrival without naming one. */
	const DEFAULT_GOODS_ORIGIN = 'customer';

	/** Origin on which the customer posted nothing, so no return carriage exists. */
	const CARRIER_RETURN_ORIGIN = 'carrier_return';

	/** The recorded origin of a row, or '' on a row that carries no answer. */
	public static function goods_origin( array $row ) {
		$value = isset( $row['goods_origin'] ) ? (string) $row['goods_origin'] : '';

		return in_array( $value, self::GOODS_ORIGINS, true ) ? $value : '';
	}

	/**
	 * True when the goods came back without the customer sending them, so return
	 * carriage cannot be withheld from their refund.
	 */
	public static function carriage_unavailable( array $row ) {
		return self::CARRIER_RETURN_ORIGIN === self::goods_origin( $row );
	}

	/**
	 * True when the row carries a statutory declaration: it was filed as a
	 * withdrawal, or a withdrawal was declared with it and its type changed later.
	 */
	public static function is_statutory( array $row ) {
		$type     = isset( $row['type'] ) ? (string) $row['type'] : '';
		$original = isset( $row['original_type'] ) ? (string) $row['original_type'] : '';

		return 'withdrawal' === $type || 'withdrawal' === $original;
	}

	/** Adds a flag to a comma-separated flag list, without repeating it. */
	public static function add_flag( $flags, $flag ) {
		$list = array_values( array_filter( array_map( 'trim', explode( ',', (string) $flags ) ), 'strlen' ) );

		if ( ! in_array( (string) $flag, $list, true ) ) {
			$list[] = (string) $flag;
		}

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

	/** Removes a flag from a comma-separated flag list, keeping the others. */
	public static function remove_flag( $flags, $flag ) {
		$list = array_filter(
			array_map( 'trim', explode( ',', (string) $flags ) ),
			static function ( $entry ) use ( $flag ) {
				return '' !== $entry && (string) $flag !== $entry;
			}
		);

		return implode( ',', array_values( $list ) );
	}

	/** True when a comma-separated flag list carries the flag. */
	public static function has_flag( $flags, $flag ) {
		return in_array( (string) $flag, array_map( 'trim', explode( ',', (string) $flags ) ), true );
	}

	/**
	 * True when the row carries either overlap flag: it was filed over units
	 * another request holds, or a later statutory withdrawal reaches over its own.
	 */
	public static function overlaps_claim( array $row ) {
		$flags = isset( $row['flags'] ) ? $row['flags'] : '';

		return self::has_flag( $flags, self::SUPERSEDES_FLAG ) || self::has_flag( $flags, self::SUPERSEDED_FLAG );
	}

	/**
	 * Ids of the requests named in the overlap entries of a history, from the
	 * entry written on the reaching request and from the one written on the
	 * request reached over.
	 *
	 * @return array<int,int> Each id once, in the order it appears.
	 */
	public static function overlap_ids( array $row ) {
		$history = json_decode( isset( $row['history'] ) ? (string) $row['history'] : '', true );

		if ( ! is_array( $history ) ) {
			return array();
		}

		$ids = array();

		foreach ( $history as $event ) {
			$kind = isset( $event['kind'] ) ? (string) $event['kind'] : '';

			if ( 'supersedes' !== $kind && 'superseded' !== $kind ) {
				continue;
			}

			if ( preg_match_all( '/#(\d+)/', isset( $event['note'] ) ? (string) $event['note'] : '', $found ) ) {
				foreach ( $found[1] as $id ) {
					$ids[] = (int) $id;
				}
			}
		}

		return array_values( array_unique( array_filter( $ids ) ) );
	}

	/**
	 * Records that an operator released money on a request whose units another
	 * request also holds.
	 *
	 * @param int    $reference Request the operator checked this payout against.
	 * @param string $note      Wording taken instead, when no overlapping request
	 *                          can be named on the row any more.
	 */
	public function record_payout_override( $id, $reference, $note = '' ) {
		// Every overlapping request is named, not just one: the operator answers
		// for all of them at a single gate, so the entry has to say which ones.
		// A bare number is still accepted, which is what older callers pass.
		$ids = array_values( array_unique( array_filter( array_map( 'intval', (array) $reference ) ) ) );

		return $this->append_history(
			$id,
			array(
				'kind' => 'payout_override',
				'note' => $ids ? '#' . implode( ', #', $ids ) : mb_substr( trim( (string) $note ), 0, self::NOTE_CHARS ),
			)
		);
	}

	/**
	 * Records why a request carrying a statutory declaration was closed without
	 * settling it: the ground picked from the list, the request it points at
	 * where the ground is `duplicate`, and the operator's own wording.
	 *
	 * The ground is stored on the row because the refund clock reads it; the
	 * wording stays in the history, where it is read by people.
	 *
	 * @param string $grounds Wording entered by the operator.
	 * @param string $ground  One of Deadlines::CLOSURE_GROUNDS, or ''.
	 * @param int    $ref     Request the ground points at, or 0.
	 */
	public function record_closure_grounds( $id, $grounds, $ground = '', $ref = 0 ) {
		global $wpdb;

		$grounds = trim( (string) $grounds );
		$ground  = in_array( (string) $ground, Deadlines::CLOSURE_GROUNDS, true ) ? (string) $ground : '';

		if ( '' === $grounds && '' === $ground ) {
			return false;
		}

		if ( '' !== $ground ) {
			$wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- the plugin's own table: no WordPress API writes it, no cache holds its rows.
				Database::table( 'requests' ),
				array(
					'closure_ground'     => $ground,
					'closure_ground_ref' => 'duplicate' === $ground ? absint( $ref ) : 0,
					'updated_at'         => current_time( 'mysql', true ),
				),
				array( 'id' => (int) $id ),
				array( '%s', '%d', '%s' ),
				array( '%d' )
			);
		}

		if ( '' === $grounds ) {
			return true;
		}

		return $this->append_history(
			$id,
			array(
				'kind'   => 'closure_grounds',
				'ground' => $ground,
				'note'   => mb_substr( $grounds, 0, self::NOTE_CHARS ),
			)
		);
	}

	/**
	 * Appends one entry to a request history, stamped with the moment and the
	 * operator making it. Nothing else on the row is changed.
	 *
	 * Public (API 1, api/data.md §2): an add-on records its own history entries
	 * with it, so every entry carries the same `at` and `by`.
	 *
	 * @param int   $id    Request.
	 * @param array $entry Entry fields; `at` and `by` are added here.
	 * @return bool False for an unknown request or a failed write.
	 */
	public function append_history( $id, array $entry ) {
		global $wpdb;

		$request = $this->get( $id );

		if ( ! $request ) {
			return false;
		}

		$now     = current_time( 'mysql', true );
		$history = json_decode( (string) $request['history'], true );
		$history = is_array( $history ) ? $history : array();

		$history[] = array_merge(
			$entry,
			array(
				'at' => $now,
				'by' => 'admin:' . get_current_user_id(),
			)
		);

		return (bool) $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- the plugin's own table: no WordPress API writes it, no cache holds its rows.
			Database::table( 'requests' ),
			array(
				'history'    => wp_json_encode( $history ),
				'updated_at' => $now,
			),
			array( 'id' => (int) $id ),
			array( '%s', '%s' ),
			array( '%d' )
		);
	}

	/**
	 * The columns of this table an add-on may set through record_change(): the
	 * type and its first declaration, the agreement or outcome that settles a
	 * request and the consent behind it, the closing ground (api/data.md §2).
	 * Every other column of this table is written by Careback's own writers.
	 */
	const ADDON_COLUMNS = array( 'type', 'original_type', 'resolution', 'resolution_at', 'resolution_note', 'consent_quote', 'consent_at', 'closure_ground' );

	/**
	 * Writes an add-on's change to a request in one UPDATE: the add-on's own
	 * columns, the columns of ADDON_COLUMNS, a flag set or taken off, one history
	 * entry, and `updated_at` (API 1, api/data.md §2).
	 *
	 * Careback keeps the format of what it stores: the flag list is worked out
	 * by add_flag() and remove_flag() on the flags as stored, the entry is
	 * appended to the history as stored, and the moment of the change is both
	 * `updated_at` and the entry's `at`.
	 *
	 * @param int   $id     Request.
	 * @param array $change What changes, each key optional:
	 *                      - `fields`: column => value (a scalar or null): the
	 *                        add-on's own columns and those of ADDON_COLUMNS, and
	 *                        `updated_at`, the moment of the change as
	 *                        `Y-m-d H:i:s` (now when not given);
	 *                      - `add_flag`, `remove_flag`: a flag, a plain name;
	 *                      - `history`: one entry, its fields in their order;
	 *                        `at` is set first to the moment, and `by` is the
	 *                        operator (`admin:<user id>`) when it names none;
	 *                      - `if_updated_at`: write only while the row's
	 *                        `updated_at` is still this.
	 * @return int|false Rows changed (0 when `if_updated_at` no longer matches);
	 *                   false for an unknown request, a failed write, or a
	 *                   change it refuses (any other key or column, a name that
	 *                   is not a plain column, a moment not written
	 *                   `Y-m-d H:i:s`), which writes nothing.
	 */
	public function record_change( $id, array $change ) {
		global $wpdb;

		$fields = isset( $change['fields'] ) ? $change['fields'] : array();
		$now    = isset( $fields['updated_at'] ) ? $fields['updated_at'] : current_time( 'mysql', true );

		if ( ! self::change_allowed( $change, $fields ) || ! is_string( $now ) || ! preg_match( '/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/', $now ) ) {
			return false;
		}

		$request = $this->get( $id );

		if ( ! $request ) {
			return false;
		}

		unset( $fields['updated_at'] );

		if ( isset( $change['add_flag'] ) || isset( $change['remove_flag'] ) ) {
			$flags = isset( $request['flags'] ) ? (string) $request['flags'] : '';
			$flags = isset( $change['add_flag'] ) ? self::add_flag( $flags, $change['add_flag'] ) : $flags;

			$fields['flags'] = isset( $change['remove_flag'] ) ? self::remove_flag( $flags, $change['remove_flag'] ) : $flags;
		}

		if ( isset( $change['history'] ) ) {
			$history = json_decode( (string) $request['history'], true );
			$history = is_array( $history ) ? $history : array();
			$entry   = array_merge( array( 'at' => $now ), $change['history'], array( 'at' => $now ) );

			if ( ! array_key_exists( 'by', $entry ) ) {
				$entry['by'] = 'admin:' . get_current_user_id();
			}

			$history[]         = $entry;
			$fields['history'] = wp_json_encode( $history );
		}

		$fields['updated_at'] = $now;

		$where  = array( 'id' => (int) $id );
		$format = array( '%d' );

		if ( isset( $change['if_updated_at'] ) ) {
			$where['updated_at'] = (string) $change['if_updated_at'];
			$format[]            = '%s';
		}

		// The plugin's own table: no WordPress API writes it, and no cache holds its rows.
		return $wpdb->update( Database::table( 'requests' ), $fields, $where, self::formats( $fields ), $format ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	}

	/**
	 * Whether record_change() takes a change: only its keys; plain column names,
	 * none of Careback's but those of ADDON_COLUMNS and `updated_at`; scalar or
	 * null values; flags that are plain names; one entry with named fields.
	 *
	 * @param array $change The change.
	 * @param mixed $fields Its `fields`.
	 * @return bool
	 */
	private static function change_allowed( array $change, $fields ) {
		if ( array_diff( array_keys( $change ), array( 'fields', 'add_flag', 'remove_flag', 'history', 'if_updated_at' ) ) || ! is_array( $fields ) ) {
			return false;
		}

		$own = array_diff( Database::columns( 'requests' ), self::ADDON_COLUMNS, array( 'updated_at' ) );

		foreach ( $fields as $column => $value ) {
			if ( ! is_string( $column ) || ! preg_match( '/^[a-z0-9_]+$/', $column ) || in_array( $column, $own, true ) || ( null !== $value && ! is_scalar( $value ) ) ) {
				return false;
			}
		}

		foreach ( array( 'add_flag', 'remove_flag' ) as $key ) {
			if ( isset( $change[ $key ] ) && ! ( is_string( $change[ $key ] ) && preg_match( '/^[a-z0-9_]+$/', $change[ $key ] ) ) ) {
				return false;
			}
		}

		if ( isset( $change['history'] ) && ( ! is_array( $change['history'] ) || ! $change['history'] || array_filter( array_keys( $change['history'] ), 'is_int' ) ) ) {
			return false;
		}

		return ! isset( $change['if_updated_at'] ) || is_string( $change['if_updated_at'] );
	}

	public function insert( array $data ) {
		global $wpdb;

		$now = current_time( 'mysql', true );

		$history = array(
			array(
				'at'     => $now,
				'status' => (string) $data['status'],
				'by'     => 'customer',
				'note'   => 'created',
			),
		);

		// Written when the request reaches over units another request holds; the
		// note names those requests.
		if ( ! empty( $data['history_note'] ) ) {
			$history[] = array(
				'at'   => $now,
				'kind' => 'supersedes',
				'by'   => 'customer',
				'note' => (string) $data['history_note'],
			);
		}

		// The consent with the shop's returns policy given on the form (N-12):
		// which page, under its title then, and the moment it last changed.
		if ( ! empty( $data['policy'] ) && is_array( $data['policy'] ) ) {
			$history[] = array(
				'at'      => $now,
				'kind'    => Returns_Policy::HISTORY_KIND,
				'by'      => 'customer',
				'note'    => isset( $data['policy']['title'] ) ? (string) $data['policy']['title'] : '',
				'page'    => isset( $data['policy']['page'] ) ? (int) $data['policy']['page'] : 0,
				'changed' => isset( $data['policy']['changed'] ) ? (string) $data['policy']['changed'] : '',
			);
		}

		$row = array(
			'order_id'      => (int) $data['order_id'],
			'order_number'  => (string) $data['order_number'],
			'email_hash'    => (string) $data['email_hash'],
			'customer_name' => (string) $data['customer_name'],
			'items'         => wp_json_encode( $data['items'] ),
			'reason'        => isset( $data['reason'] ) ? (string) $data['reason'] : null,
			'comment'       => isset( $data['comment'] ) ? (string) $data['comment'] : null,
			'flags'         => isset( $data['flags'] ) ? (string) $data['flags'] : '',
			'type'          => (string) $data['type'],
			'original_type' => isset( $data['original_type'] ) ? (string) $data['original_type'] : '',
			'status'        => (string) $data['status'],
			'channel'       => isset( $data['channel'] ) ? (string) $data['channel'] : 'portal',
			'delivery_state' => isset( $data['delivery_state'] ) ? (string) $data['delivery_state'] : '',
			'declaration_received_at' => isset( $data['declaration_received_at'] ) ? (string) $data['declaration_received_at'] : $now,
			'declaration_sent_at'     => isset( $data['declaration_sent_at'] ) ? (string) $data['declaration_sent_at'] : $now,
			'history'       => wp_json_encode( $history ),
			'token_jti'     => (string) $data['token_jti'],
			'ip_hash'       => (string) $data['ip_hash'],
			'user_agent'              => isset( $data['user_agent'] ) ? mb_substr( (string) $data['user_agent'], 0, self::USER_AGENT_MAX ) : '',
			'created_at'    => $now,
			'updated_at'    => $now,
		);

		// Until the table setup of an update has run, the column is not there yet.
		if ( ! Database::schema_at_least( Database::USER_AGENT_SCHEMA ) ) {
			unset( $row['user_agent'] );
		}

		/**
		 * Filters the row insert() writes. An add-on adds the columns it owns,
		 * read from what the caller handed over; the free part's columns are
		 * written as it set them. See api/data.md.
		 *
		 * @param array $row  Column => value.
		 * @param array $data What the caller handed insert().
		 */
		$row = self::with_added( $row, apply_filters( self::INSERT_FILTER, $row, $data ) ); // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.DynamicHooknameFound -- careback_request_insert, the final prefix.

		// One format per column, worked out from the value, so the list can never
		// fall out of step with the columns whoever added them.
		$wpdb->insert( Database::table( 'requests' ), $row, self::formats( $row ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- the plugin's own table: no WordPress API writes it.

		$id = (int) $wpdb->insert_id;

		if ( $id ) {
			do_action( 'careback_request_created', $id, $data );
		}

		return $id;
	}

	/**
	 * The free part's fields with the columns an add-on added, the free part's
	 * own written as it set them.
	 *
	 * @param array $fields The free part's column => value.
	 * @param mixed $added  What the filter returned.
	 * @return array
	 */
	private static function with_added( array $fields, $added ) {
		if ( ! is_array( $added ) ) {
			return $fields;
		}

		foreach ( $added as $column => $value ) {
			if ( ! is_string( $column ) || ! preg_match( '/^[a-z0-9_]+$/', $column ) || array_key_exists( $column, $fields ) ) {
				continue;
			}

			$fields[ $column ] = $value;
		}

		return $fields;
	}

	/**
	 * The $wpdb format of each value: `%d` for an integer, `%f` for a float,
	 * `%s` for everything else (null is written as NULL).
	 *
	 * @param array $fields Column => value.
	 * @return string[]
	 */
	private static function formats( array $fields ) {
		$formats = array();

		foreach ( $fields as $value ) {
			$formats[] = is_int( $value ) ? '%d' : ( is_float( $value ) ? '%f' : '%s' );
		}

		return $formats;
	}

	/**
	 * Units of each line already claimed by requests that hold or consumed them.
	 *
	 * @return array<int,int> line_item_id => quantity claimed.
	 */
	public function consumed_units( $order_id ) {
		return $this->claimed_units( $order_id, false );
	}

	/**
	 * Units of each line claimed by requests that carry a statutory declaration.
	 *
	 * @return array<int,int> line_item_id => quantity claimed.
	 */
	public function statutory_units( $order_id ) {
		return $this->claimed_units( $order_id, true );
	}

	/**
	 * @param bool $statutory_only Counts only the requests carrying a statutory
	 *                             declaration.
	 * @return array<int,int> line_item_id => quantity claimed.
	 */
	private function claimed_units( $order_id, $statutory_only ) {
		global $wpdb;

		$table     = Database::table( 'requests' );
		$consuming = Statuses::consuming();

		if ( ! $consuming ) {
			return array();
		}

		$placeholders = implode( ',', array_fill( 0, count( $consuming ), '%s' ) );
		$params       = array_merge( array( $table, (int) $order_id ), $consuming );
		$filter       = $statutory_only ? " AND ( type = 'withdrawal' OR original_type = 'withdrawal' )" : '';

		// A type that takes no unit with it is left out of every count, so a
		// guarantee claim neither blocks a later request nor is blocked by one.
		$skip     = Routes::non_consuming_types();
		$excluded = $skip ? ' AND type NOT IN (' . implode( ',', array_fill( 0, count( $skip ), '%s' ) ) . ')' : '';
		$params   = array_merge( $params, $skip );

		$rows = $wpdb->get_col( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- the plugin's own table, live data; $filter and $excluded are fixed SQL with %s placeholders.
			$wpdb->prepare( // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- $params holds one value per placeholder, the IN lists' too.
				"SELECT items FROM %i WHERE order_id = %d AND status IN ({$placeholders}){$filter}{$excluded}", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- lists of %s placeholders and a fixed condition.
				$params
			)
		);

		$claimed = array();

		foreach ( (array) $rows as $json ) {
			foreach ( (array) json_decode( $json, true ) as $item ) {
				if ( empty( $item['line_item_id'] ) ) {
					continue;
				}

				$id = (int) $item['line_item_id'];
				$qty = isset( $item['quantity'] ) ? max( 1, (int) $item['quantity'] ) : 1;

				$claimed[ $id ] = isset( $claimed[ $id ] ) ? $claimed[ $id ] + $qty : $qty;
			}
		}

		return $claimed;
	}

	/**
	 * Claims per line, so the portal can name the request holding a unit and tell
	 * a statutory claim from one made under a voluntary offer.
	 *
	 * @return array<int,array> line_item_id => [ { id, status, quantity, statutory }, ... ]
	 */
	public function claims_by_line( $order_id ) {
		global $wpdb;

		$table     = Database::table( 'requests' );
		$consuming = Statuses::consuming();

		if ( ! $consuming ) {
			return array();
		}

		$placeholders = implode( ',', array_fill( 0, count( $consuming ), '%s' ) );
		$params       = array_merge( array( $table, (int) $order_id ), $consuming );

		// Same exclusion as claimed_units(): a guarantee claim holds no unit, so
		// it is never named as a request another one reaches over.
		$skip     = Routes::non_consuming_types();
		$excluded = $skip ? ' AND type NOT IN (' . implode( ',', array_fill( 0, count( $skip ), '%s' ) ) . ')' : '';
		$params   = array_merge( $params, $skip );

		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- the plugin's own table, live data; $excluded is fixed SQL with %s placeholders.
			$wpdb->prepare( // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- $params holds one value per placeholder, the IN lists' too.
				"SELECT id, status, type, original_type, items FROM %i WHERE order_id = %d AND status IN ({$placeholders}){$excluded} ORDER BY created_at ASC", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- lists of %s placeholders.
				$params
			),
			ARRAY_A
		);

		$map = array();

		foreach ( (array) $rows as $row ) {
			foreach ( (array) json_decode( $row['items'], true ) as $item ) {
				if ( empty( $item['line_item_id'] ) ) {
					continue;
				}

				$map[ (int) $item['line_item_id'] ][] = array(
					'id'        => (int) $row['id'],
					'status'    => (string) $row['status'],
					'quantity'  => isset( $item['quantity'] ) ? max( 1, (int) $item['quantity'] ) : 1,
					'statutory' => self::is_statutory( $row ),
				);
			}
		}

		return $map;
	}

	/**
	 * Ids of the requests holding units on the given lines under a voluntary
	 * offer. Claims carrying a statutory declaration are left out: such a claim
	 * is not reached over, it stands on its own.
	 *
	 * Shared by the two paths that file a request, the portal and the operator's
	 * manual entry, so a declaration recorded by hand carries the same overlap
	 * marks as one filed through the portal.
	 *
	 * @param int            $order_id Order the lines belong to.
	 * @param array<int,int> $lines    Lines filled beyond what was free.
	 * @return array<int,int> Request ids, each once, in filing order.
	 */
	public function claims_reached( $order_id, array $lines ) {
		$claims = $this->claims_by_line( $order_id );
		$ids    = array();

		foreach ( $lines as $line ) {
			if ( empty( $claims[ (int) $line ] ) ) {
				continue;
			}

			foreach ( $claims[ (int) $line ] as $claim ) {
				if ( ! empty( $claim['statutory'] ) ) {
					continue;
				}

				$ids[] = (int) $claim['id'];
			}
		}

		return array_values( array_unique( $ids ) );
	}

	/**
	 * Records that lines of the order were left out of a manually recorded
	 * request because earlier requests already hold every unit on them. Nothing
	 * about the request is decided here; the entry exists so the omission is
	 * readable long after the notice that announced it has gone.
	 *
	 * @param int    $id   Request the lines were left out of.
	 * @param string $note The lines and the requests holding them.
	 */
	public function record_items_left_out( $id, $note ) {
		$note = trim( (string) $note );

		if ( '' === $note ) {
			return false;
		}

		return $this->append_history(
			(int) $id,
			array(
				'kind' => 'items_left_out',
				'note' => mb_substr( $note, 0, self::NOTE_CHARS ),
			)
		);
	}

	/**
	 * Records that a later statutory withdrawal reaches over the units this
	 * request holds: sets the flag and names that withdrawal in the history.
	 *
	 * The request keeps its status, its items and its own clock; nothing about it
	 * is decided here.
	 *
	 * @param int $id Request the withdrawal reaches over.
	 * @param int $by Request reaching over it.
	 */
	public function mark_superseded( $id, $by ) {
		global $wpdb;

		$request = $this->get( $id );

		if ( ! $request ) {
			return false;
		}

		$current = isset( $request['flags'] ) ? (string) $request['flags'] : '';
		$flags   = self::add_flag( $current, self::SUPERSEDED_FLAG );

		if ( $flags === $current ) {
			return false;
		}

		$now     = current_time( 'mysql', true );
		$history = json_decode( (string) $request['history'], true );
		$history = is_array( $history ) ? $history : array();

		$history[] = array(
			'at'   => $now,
			'kind' => 'superseded',
			'by'   => 'customer',
			'note' => '#' . (int) $by,
		);

		return (bool) $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- the plugin's own table: no WordPress API writes it, no cache holds its rows.
			Database::table( 'requests' ),
			array(
				'flags'      => $flags,
				'history'    => wp_json_encode( $history ),
				'updated_at' => $now,
			),
			array( 'id' => (int) $id ),
			array( '%s', '%s', '%s' ),
			array( '%d' )
		);
	}

	/**
	 * Sets a flag on a request without touching its status, its items or its
	 * clock. Returns false when the row already carries it.
	 *
	 * @param string $kind History entry recorded with the flag.
	 * @param string $note Note stored on that entry.
	 */
	public function add_request_flag( $id, $flag, $kind, $note = '' ) {
		global $wpdb;

		$request = $this->get( $id );

		if ( ! $request ) {
			return false;
		}

		$current = isset( $request['flags'] ) ? (string) $request['flags'] : '';
		$flags   = self::add_flag( $current, $flag );

		if ( $flags === $current ) {
			return false;
		}

		$now     = current_time( 'mysql', true );
		$history = json_decode( (string) $request['history'], true );
		$history = is_array( $history ) ? $history : array();

		$history[] = array(
			'at'   => $now,
			'kind' => (string) $kind,
			'by'   => 'system',
			'note' => mb_substr( trim( (string) $note ), 0, self::NOTE_CHARS ),
		);

		return (bool) $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- the plugin's own table: no WordPress API writes it, no cache holds its rows.
			Database::table( 'requests' ),
			array(
				'flags'      => $flags,
				'history'    => wp_json_encode( $history ),
				'updated_at' => $now,
			),
			array( 'id' => (int) $id ),
			array( '%s', '%s', '%s' ),
			array( '%d' )
		);
	}

	/**
	 * Records that the customer evidenced the goods were sent back.
	 *
	 * This ends the right to withhold payment on its own, without waiting for the
	 * parcel, so it is recorded as its own fact rather than inferred from status.
	 */
	public function record_dispatch_proof( $id, $reference = '', $at = '', $by = '' ) {
		global $wpdb;

		$request = $this->get( $id );

		if ( ! $request ) {
			return false;
		}

		// Write-once. This date is the evidence that the customer sent the goods
		// back in time, and it ends the shop's right to withhold payment. A second
		// write moves that evidence, which is the one thing it must not do — a
		// correction is a deliberate act with its own record, not a re-save of the
		// same form.
		$stamped = isset( $request['dispatch_proof_at'] ) ? (string) $request['dispatch_proof_at'] : '';

		if ( '' !== $stamped && '0000-00-00 00:00:00' !== $stamped ) {
			return false;
		}

		$now     = current_time( 'mysql', true );
		$history = json_decode( (string) $request['history'], true );
		$history = is_array( $history ) ? $history : array();

		$history[] = array(
			'at'   => $now,
			'kind' => 'dispatch_proof',
			// Since 0.69.0 the customer can supply this themselves through the
			// portal, and who supplied it is part of the evidence: an operator
			// reading the history has to be able to tell a tracking number the
			// customer typed from one the shop entered on their behalf.
			'by'   => '' !== (string) $by ? (string) $by : 'admin:' . get_current_user_id(),
			'note' => (string) $reference,
		);

		return (bool) $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- the plugin's own table: no WordPress API writes it, no cache holds its rows.
			Database::table( 'requests' ),
			array(
				'dispatch_proof_at' => $at ? (string) $at : $now,
				'history'           => wp_json_encode( $history ),
				'updated_at'        => $now,
			),
			array( 'id' => (int) $id ),
			array( '%s', '%s', '%s' ),
			array( '%d' )
		);
	}

	/**
	 * Records where the goods came from and, on a row that carries no arrival date
	 * yet, when they reached the shop.
	 *
	 * `goods_received_at` is written only while it is empty: the date already
	 * stored is the fact the withholding right lapsed on, and no later entry
	 * replaces it. The origin itself can be recorded on a row whose arrival was
	 * stamped before this answer existed.
	 *
	 * @param string $origin One of self::GOODS_ORIGINS; anything else is taken as
	 *                       self::DEFAULT_GOODS_ORIGIN.
	 * @param string $at     Arrival date as `Y-m-d H:i:s`; empty means now.
	 * @return bool
	 */
	public function record_goods_arrival( $id, $origin, $at = '' ) {
		global $wpdb;

		$request = $this->get( $id );

		if ( ! $request ) {
			return false;
		}

		$origin = in_array( (string) $origin, self::GOODS_ORIGINS, true )
			? (string) $origin
			: self::DEFAULT_GOODS_ORIGIN;

		$now     = current_time( 'mysql', true );
		$history = json_decode( (string) $request['history'], true );
		$history = is_array( $history ) ? $history : array();

		$history[] = array(
			'at'   => $now,
			'kind' => 'goods_arrival',
			'by'   => 'admin:' . get_current_user_id(),
			'note' => $origin,
		);

		$fields = array(
			'goods_origin' => $origin,
			'history'      => wp_json_encode( $history ),
			'updated_at'   => $now,
		);
		$formats = array( '%s', '%s', '%s' );

		$stamped = isset( $request['goods_received_at'] ) ? (string) $request['goods_received_at'] : '';

		if ( '' === $stamped || '0000-00-00 00:00:00' === $stamped ) {
			$fields['goods_received_at'] = '' !== (string) $at ? (string) $at : $now;
			$formats[]                   = '%s';
		}

		return false !== $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- the plugin's own table: no WordPress API writes it, no cache holds its rows.
			Database::table( 'requests' ),
			$fields,
			array( 'id' => (int) $id ),
			$formats,
			array( '%d' )
		);
	}

	/**
	 * Corrects the day the goods reached the shop on a request that already has
	 * one. Nothing else moves: the refund deadline is counted from the
	 * declaration, not from the arrival, so this changes the record and not the
	 * clock.
	 *
	 * @param string $at Date as `Y-m-d H:i:s`, already validated by the caller.
	 */
	public function correct_goods_arrival( $id, $at ) {
		global $wpdb;

		$request = $this->get( $id );
		$at      = trim( (string) $at );

		if ( ! $request || '' === $at ) {
			return false;
		}

		$was = isset( $request['goods_received_at'] ) ? (string) $request['goods_received_at'] : '';

		if ( '' === $was || '0000-00-00 00:00:00' === $was || $was === $at ) {
			return false;
		}

		$now     = current_time( 'mysql', true );
		$history = json_decode( (string) $request['history'], true );
		$history = is_array( $history ) ? $history : array();

		$history[] = array(
			'at'   => $now,
			'kind' => 'goods_arrival_corrected',
			'by'   => 'admin:' . get_current_user_id(),
			'note' => $was . ' -> ' . $at,
		);

		return false !== $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- the plugin's own table: no WordPress API writes it, no cache holds its rows.
			Database::table( 'requests' ),
			array(
				'goods_received_at' => $at,
				'history'           => wp_json_encode( $history ),
				'updated_at'        => $now,
			),
			array( 'id' => (int) $id ),
			array( '%s', '%s', '%s' ),
			array( '%d' )
		);
	}

	/**
	 * Takes back an arrival that turned out not to be one: the parcel was empty,
	 * or held something else. The date and the origin both go.
	 *
	 * A refund clock kept on the row is recomputed by whoever keeps it, through
	 * `careback_request_update` (change `arrival_cleared`): it returns to
	 * suspended only where nothing else is already running it. Article 13(3)
	 * of Directive 2011/83/EU gives **two** ends to the right to withhold — the
	 * goods coming back, **or** the customer supplying proof that they were
	 * sent — and the second is usually the earlier one.
	 *
	 * The status is left where the operator put it. What arrived is a fact about
	 * the parcel; where the request stands is their decision.
	 *
	 * @param string $note What was found in the parcel.
	 */
	public function clear_goods_arrival( $id, $note ) {
		global $wpdb;

		$request = $this->get( $id );
		$note    = trim( (string) $note );

		if ( ! $request || '' === $note ) {
			return false;
		}

		$was = isset( $request['goods_received_at'] ) ? (string) $request['goods_received_at'] : '';

		if ( '' === $was || '0000-00-00 00:00:00' === $was ) {
			return false;
		}

		$now     = current_time( 'mysql', true );
		$history = json_decode( (string) $request['history'], true );
		$history = is_array( $history ) ? $history : array();

		$history[] = array(
			'at'   => $now,
			'kind' => 'goods_arrival_cleared',
			'by'   => 'admin:' . get_current_user_id(),
			'note' => mb_substr( $note, 0, self::NOTE_CHARS ),
		);

		$fields = array(
			'goods_received_at' => null,
			'goods_origin'      => '',
			'history'           => wp_json_encode( $history ),
			'updated_at'        => $now,
		);

		/**
		 * Filters the fields written when an arrival is taken back. See
		 * api/data.md.
		 *
		 * @param array  $fields  Column => value.
		 * @param array  $request The row as read before the change.
		 * @param string $change  `arrival_cleared`.
		 */
		$fields = self::with_added( $fields, apply_filters( self::UPDATE_FILTER, $fields, $request, 'arrival_cleared' ) ); // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.DynamicHooknameFound -- careback_request_update, the final prefix.

		return false !== $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- the plugin's own table: no WordPress API writes it, no cache holds its rows.
			Database::table( 'requests' ),
			$fields,
			array( 'id' => (int) $id ),
			self::formats( $fields ),
			array( '%d' )
		);
	}

	/**
	 * Corrects proof of dispatch that is already on record.
	 *
	 * Separate from recording it, and requires an account of why: the first date
	 * is evidence about when the customer posted, and moving it silently would
	 * rewrite the record that decides whether they were in time.
	 *
	 * @param string $at   New date, already validated by the caller.
	 * @param string $note Why it is being corrected.
	 */
	public function correct_dispatch_proof( $id, $at, $note ) {
		global $wpdb;

		$request = $this->get( $id );
		$at      = trim( (string) $at );
		$note    = trim( (string) $note );

		if ( ! $request || '' === $at || '' === $note ) {
			return false;
		}

		$was = isset( $request['dispatch_proof_at'] ) ? (string) $request['dispatch_proof_at'] : '';

		if ( '' === $was || '0000-00-00 00:00:00' === $was || $was === $at ) {
			return false;
		}

		$now     = current_time( 'mysql', true );
		$history = json_decode( (string) $request['history'], true );
		$history = is_array( $history ) ? $history : array();

		$history[] = array(
			'at'   => $now,
			'kind' => 'dispatch_proof_corrected',
			'by'   => 'admin:' . get_current_user_id(),
			'note' => $was . ' -> ' . $at . ' — ' . mb_substr( $note, 0, self::NOTE_CHARS ),
		);

		return false !== $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- the plugin's own table: no WordPress API writes it, no cache holds its rows.
			Database::table( 'requests' ),
			array( 'dispatch_proof_at' => $at, 'history' => wp_json_encode( $history ), 'updated_at' => $now ),
			array( 'id' => (int) $id ),
			array( '%s', '%s', '%s' ),
			array( '%d' )
		);
	}

	public function get( $id ) {
		global $wpdb;
		$table = Database::table( 'requests' );

		return $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', $table, (int) $id ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- the plugin's own table; live request data, not cached.
	}

	/**
	 * The free part's orderings of the list screen, as key => ORDER BY clause.
	 *
	 * Only fixed clauses ever reach the query; the key arriving from the
	 * request is matched against them rather than interpolated, so no operator
	 * input can shape the SQL.
	 */
	const ORDERINGS = array(
		'newest' => 'created_at DESC',
		'oldest' => 'created_at ASC',
	);

	/**
	 * Every ordering the list knows: the free part's and those an add-on adds
	 * through `careback_requests_orderings`. Each clause is fixed code, never
	 * operator input; the free part's own keys keep their clauses.
	 *
	 * @return array<string,string>
	 */
	public static function orderings() {
		/**
		 * Filters the orderings the request list knows. See api/data.md.
		 *
		 * @param array $orderings Key => ORDER BY clause.
		 */
		$orderings = apply_filters( self::ORDERINGS_FILTER, self::ORDERINGS ); // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.DynamicHooknameFound -- careback_requests_orderings, the final prefix.
		$out       = self::ORDERINGS;

		foreach ( is_array( $orderings ) ? $orderings : array() as $key => $clause ) {
			if ( is_string( $key ) && is_string( $clause ) && '' !== trim( $clause ) && ! isset( $out[ $key ] ) ) {
				$out[ $key ] = $clause;
			}
		}

		return $out;
	}

	/**
	 * One page of the request list.
	 *
	 * @param string|string[] $status  Status, or statuses ticked; empty for all.
	 * @param int             $limit   Rows per page.
	 * @param int             $offset  Rows skipped.
	 * @param string          $search  Request id, order number or name.
	 * @param string          $orderby Key of orderings(); `newest` when unknown.
	 * @param string[]        $type    Routes ticked; empty for all.
	 * @param array           $args    The list's query arguments, handed to
	 *                                 `careback_requests_where` (the screen passes
	 *                                 the whole query of `careback_requests_query_args`).
	 * @return array<int,array>
	 */
	public function get_list( $status = '', $limit = 50, $offset = 0, $search = '', $orderby = 'newest', $type = array(), $args = array() ) {
		global $wpdb;
		$table = Database::table( 'requests' );

		// The WHERE clause as it goes through prepare(): see filters().
		list( $params, $where ) = $this->filters( $status, $search, $type, $args );

		$orderings = self::orderings();
		// An add-on's clause is fixed code and may hold a literal %; doubled, the
		// prepare() below reads it as one and not as a placeholder.
		$order = self::escape_percent( isset( $orderings[ $orderby ] ) ? $orderings[ $orderby ] : $orderings['newest'] );

		$params   = array_merge( array( $table ), $params );
		$params[] = absint( $limit );
		$params[] = absint( $offset );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- the plugin's own table, live data; WHERE from filters() (placeholders, fixed clauses), ORDER BY from orderings().
		return $wpdb->get_results(
			$wpdb->prepare( // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- $params holds one value per placeholder of the WHERE clause, then LIMIT and OFFSET.
				"SELECT * FROM %i {$where} ORDER BY {$order} LIMIT %d OFFSET %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- see filters() and orderings().
				$params
			),
			ARRAY_A
		);
	}

	/**
	 * How many requests the list holds.
	 *
	 * @param string|string[] $status Status, or statuses ticked; empty for all.
	 * @param string          $search Request id, order number or name.
	 * @param string[]        $type   Routes ticked; empty for all.
	 * @param array           $args   The list's query arguments, as for get_list().
	 * @return int
	 */
	public function count( $status = '', $search = '', $type = array(), $args = array() ) {
		global $wpdb;
		$table = Database::table( 'requests' );

		list( $params, $prepared ) = $this->filters( $status, $search, $type, $args );

		// The same conditions as get_list(), escaped the same way for prepare().
		// Always prepared: the table name is a placeholder too, so there is one
		// even when nothing is ticked and an add-on's conditions take none.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- the plugin's own table, live data; WHERE from filters() (placeholders, fixed clauses).
		return (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM %i {$prepared}", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- see filters().
				array_merge( array( $table ), $params )
			)
		);
	}

	/**
	 * The parameters of the list's WHERE clause, and that clause as it goes
	 * through prepare(): there a literal % in an add-on's condition is
	 * doubled, so it is not read as a placeholder and the parameters stay in
	 * step.
	 *
	 * @param string|string[] $status Status, or statuses ticked.
	 * @param string          $search Request id, order number or name.
	 * @param string[]        $type   Routes ticked.
	 * @param array           $args   The list's query arguments.
	 * @return array{0:array,1:string}
	 */
	private function filters( $status, $search, $type = array(), $args = array() ) {
		$clauses = array();
		$params  = array();
		$escaped = array();

		// An add-on's narrowing (careback_requests_where). Written first because
		// it takes no parameters, so it cannot come between a clause and the
		// arguments of the block below it.
		foreach ( self::where_clauses( $args ) as $clause ) {
			$clauses[] = $clause;
			$escaped[] = self::escape_percent( $clause );
		}

		$own = count( $clauses );

		// $status may be a single status string or a list of statuses (multi-select).
		$statuses = is_array( $status )
			? array_values( array_filter( array_map( 'strval', $status ), 'strlen' ) )
			: ( '' !== (string) $status ? array( (string) $status ) : array() );

		if ( ! empty( $statuses ) ) {
			$placeholders = implode( ',', array_fill( 0, count( $statuses ), '%s' ) );
			$clauses[]    = "status IN ({$placeholders})"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			foreach ( $statuses as $s ) {
				$params[] = $s;
			}
		}

		// Inserted whole, and before the search block. That block writes its clause
		// on one line and its two parameters several lines below; anything slipped
		// between them silently puts the placeholders and the arguments out of step.
		$types = array_values( array_filter( array_map( 'strval', (array) $type ), 'strlen' ) );

		if ( ! empty( $types ) ) {
			$slots     = implode( ',', array_fill( 0, count( $types ), '%s' ) );
			$clauses[] = "type IN ({$slots})"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

			foreach ( $types as $one ) {
				$params[] = $one;
			}
		}

		$search = trim( (string) $search );
		if ( '' !== $search ) {
			// Match the request id (used by the "view" links from an order), the
			// order number, or the customer name.
			if ( ctype_digit( $search ) ) {
				$clauses[] = '( id = %d OR order_number = %s OR customer_name LIKE %s )';
				$params[]  = (int) $search;
			} else {
				$clauses[] = '( order_number = %s OR customer_name LIKE %s )';
			}
			$params[] = $search;
			$params[] = '%' . $search . '%';
		}

		$prepared = array_merge( $escaped, array_slice( $clauses, $own ) );
		$prepared = $prepared ? 'WHERE ' . implode( ' AND ', $prepared ) : '';

		return array( $params, $prepared );
	}

	/**
	 * A clause of trusted code with every literal % doubled, so a prepare() it
	 * goes through reads it as it is written.
	 *
	 * @param string $sql Clause.
	 * @return string
	 */
	private static function escape_percent( $sql ) {
		return str_replace( '%', '%%', (string) $sql );
	}

	/**
	 * The conditions an add-on narrows the list by, each a complete condition
	 * without placeholders, wrapped in brackets so its own AND/OR stays its own.
	 *
	 * @param mixed $args The list's query arguments.
	 * @return string[]
	 */
	private static function where_clauses( $args ) {
		/**
		 * Filters the conditions the request list is narrowed by. Each is a
		 * complete SQL condition with no placeholders, from trusted code only: an
		 * add-on prepares its own values (never operator or customer input
		 * written in as it came). A literal % is safe. See api/data.md.
		 *
		 * @param string[] $clauses Conditions, empty from the free part.
		 * @param array    $args    The list's query arguments.
		 */
		$clauses = apply_filters( self::WHERE_FILTER, array(), is_array( $args ) ? $args : array() ); // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.DynamicHooknameFound -- careback_requests_where, the final prefix.
		$out     = array();

		foreach ( is_array( $clauses ) ? $clauses : array() as $clause ) {
			if ( is_string( $clause ) && '' !== trim( $clause ) ) {
				$out[] = '( ' . trim( $clause ) . ' )';
			}
		}

		return $out;
	}

	/**
	 * Status changes are appended to an immutable history so a dispute can be
	 * reconstructed later, and broadcast so integrations can react.
	 */
	public function update_status( $id, $status, $by = 'admin' ) {
		global $wpdb;

		$request = $this->get( $id );
		if ( ! $request ) {
			return false;
		}

		$previous = (string) $request['status'];
		if ( $previous === $status ) {
			return true;
		}

		// Only a status a registration names is ever written. One stored by an
		// add-on that is no longer active is shown, never set.
		if ( ! Statuses::is_registered( (string) $status ) ) {
			return false;
		}

		// The integrity rule is enforced here, for every caller, not only for the
		// screen that hides the option. A carrier integration, a cron job or a
		// future adapter reaching this method directly gets the same answer the
		// operator's dropdown gives.
		//
		// The four confirmation gates deliberately stay in the admin: they take an
		// answer from a person, and a gate that silently passes when nobody is
		// there to answer is worse than no gate. That is also what keeps
		// automation out of every money and closing status.
		if ( ! Statuses::allowed_for_type(
			(string) $status,
			(string) $request['type'],
			isset( $request['resolution'] ) ? (string) $request['resolution'] : '',
			$previous
		) ) {
			return false;
		}

		// And the move itself. Skipping forward is allowed — settling on the day a
		// declaration arrives is lawful — but a settled request is not walked back
		// into the pipeline, and an integration does not decide that money is owed.
		if ( ! Status_Policy::allows( $previous, (string) $status, (string) $by ) ) {
			return false;
		}

		$now     = current_time( 'mysql', true );
		// Read from the two sets rather than written out again: the list was a
		// third copy of them, and a status added to the sets and not to the copy
		// leaves `closed_at` null — which ages the row from its creation date
		// instead of its conclusion and hides it from every settlement figure.
		$closed  = Statuses::is( $status, 'settled' ) || Statuses::is( $status, 'closing' );
		$history = json_decode( (string) $request['history'], true );
		$history = is_array( $history ) ? $history : array();

		$history[] = array(
			'at'     => $now,
			'status' => (string) $status,
			'by'     => (string) $by,
			'note'   => 'from ' . $previous,
		);

		$fields = array(
			'status'     => (string) $status,
			'history'    => wp_json_encode( $history ),
			'updated_at' => $now,
		);

		// Stamped when a request concludes and never cleared afterwards. Retention
		// ages a closed row from this date and every settlement figure is measured
		// against it, so a status change that blanked it would quietly move a row
		// years back in the anonymisation queue.
		if ( $closed && empty( $request['closed_at'] ) ) {
			$fields['closed_at'] = $now;
		}

		// Arrival of the goods ends the right to withhold payment. The refund
		// deadline itself is counted from the declaration, not from here.
		if ( 'received' === $status && empty( $request['goods_received_at'] ) ) {
			$fields['goods_received_at'] = $now;
		}

		/**
		 * Filters the fields a status change writes. See api/data.md.
		 *
		 * @param array  $fields  Column => value.
		 * @param array  $request The row as read before the change.
		 * @param string $change  `status`.
		 */
		$fields = self::with_added( $fields, apply_filters( self::UPDATE_FILTER, $fields, $request, 'status' ) ); // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.DynamicHooknameFound -- careback_request_update, the final prefix.

		$result = $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- the plugin's own table: no WordPress API writes it, no cache holds its rows.
			Database::table( 'requests' ),
			$fields,
			array( 'id' => (int) $id ),
			self::formats( $fields ),
			array( '%d' )
		);

		// Fired only once the row actually moved. It used to fire whichever way the
		// write went, so a failed update still sent the customer a status email and
		// still released a reservation — for a state the database never held.
		if ( false === $result ) {
			return false;
		}

		do_action( 'careback_status_changed', (int) $id, (string) $status, $previous, $request );

		return $result;
	}

	/**
	 * Open requests for an order, for the customer-facing status view and the
	 * banner on the order screen.
	 *
	 * The statutory dates are selected because the clocks read them directly:
	 * a row missing them would report every request as suspended. An add-on
	 * reads columns of its own along (`careback_order_request_columns`).
	 */
	public function for_order( $order_id ) {
		global $wpdb;
		$table = Database::table( 'requests' );

		$columns = implode( ', ', self::order_columns() );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- the plugin's own table, live data; column names checked in order_columns().
		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT {$columns} FROM %i WHERE order_id = %d ORDER BY created_at DESC", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- plain column names, see order_columns().
				$table,
				(int) $order_id
			),
			ARRAY_A
		);
	}

	/**
	 * The columns for_order() reads: the free part's and those an add-on adds.
	 * Plain column names only.
	 *
	 * @return string[]
	 */
	private static function order_columns() {
		$own = array( 'id', 'order_id', 'status', 'type', 'original_type', 'resolution', 'items', 'created_at', 'declaration_received_at', 'goods_received_at', 'dispatch_proof_at' );

		/**
		 * Filters the columns for_order() reads. See api/data.md.
		 *
		 * @param string[] $columns Column names.
		 */
		$columns = apply_filters( self::ORDER_COLUMNS_FILTER, $own ); // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.DynamicHooknameFound -- careback_order_request_columns, the final prefix.
		$out     = array();

		foreach ( is_array( $columns ) ? $columns : array() as $column ) {
			if ( is_string( $column ) && preg_match( '/^[a-z0-9_]+$/', $column ) && ! in_array( $column, $out, true ) ) {
				$out[] = $column;
			}
		}

		return array_values( array_unique( array_merge( $out, array_diff( $own, $out ) ) ) );
	}

	/** Reason breakdown for the admin overview. */
	public function reason_counts() {
		global $wpdb;
		$table = Database::table( 'requests' );

		return $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- the plugin's own table; live request data, not cached.
			$wpdb->prepare( 'SELECT reason, COUNT(*) AS total FROM %i GROUP BY reason ORDER BY total DESC', $table ),
			ARRAY_A
		);
	}

	/**
	 * Moves every filed request from one reason key to another.
	 *
	 * Only the request rows are rewritten. Aggregated statistics are left alone:
	 * a period is counted once, after it has ended, and rewriting a closed period
	 * would restate figures that were already reported.
	 *
	 * @param string $from Key being retired.
	 * @param string $to   Key the requests are moved onto.
	 * @return int Requests moved.
	 */
	public function move_reason( $from, $to ) {
		global $wpdb;

		$from = (string) $from;
		$to   = (string) $to;

		if ( '' === $from || '' === $to || $from === $to ) {
			return 0;
		}

		$moved = $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- the plugin's own table: no WordPress API writes it, no cache holds its rows.
			Database::table( 'requests' ),
			array( 'reason' => $to, 'updated_at' => current_time( 'mysql', true ) ),
			array( 'reason' => $from ),
			array( '%s', '%s' ),
			array( '%s' )
		);

		return false === $moved ? 0 : (int) $moved;
	}

	/** Requests belonging to an email address, for data subject requests. */
	public function for_email( $email ) {
		global $wpdb;
		$table = Database::table( 'requests' );

		return $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- the plugin's own table; live request data, not cached.
			$wpdb->prepare(
				'SELECT * FROM %i WHERE email_hash = %s ORDER BY created_at DESC',
				$table,
				Hash::email( $email )
			),
			ARRAY_A
		);
	}

	/**
	 * Strips the personal fields from a request, keeping the commercial row.
	 *
	 * `token_jti` carries a UNIQUE index, so it is set to a per-row derived value
	 * rather than emptied.
	 *
	 * @return int|false False when the id names no request, as record_costs()
	 *                   answers; otherwise the rows changed, which is 0 for a
	 *                   request already erased.
	 */
	public function anonymise( $id ) {
		global $wpdb;

		$request = $this->get( $id );

		if ( ! $request ) {
			return false;
		}

		$fields  = array(
			'customer_name'   => '',
			'email_hash'      => '',
			'ip_hash'         => '',
			'comment'         => null,
			'resolution_note' => null,
			'consent_quote'   => null,
			'history'         => null,
			'token_jti'       => self::anonymised_jti( $id ),
			'user_agent'      => '',
		);
		$formats = array( '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' );

		// A row of a table the update's setup has not reached yet has no browser
		// column: the column and its format, the last of each, are left out.
		if ( ! array_key_exists( 'user_agent', $request ) ) {
			unset( $fields['user_agent'] );
			array_pop( $formats );
		}

		return $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- the plugin's own table: no WordPress API writes it, no cache holds its rows.
			Database::table( 'requests' ),
			$fields,
			array( 'id' => (int) $id ),
			$formats,
			array( '%d' )
		);
	}

	/**
	 * Replacement for a consumed `token_jti`. Unique per row and recognisable as
	 * anonymised, and short enough for the column.
	 */
	public static function anonymised_jti( $id ) {
		return substr( 'anon-' . (int) $id . '-' . md5( 'careback-anon-' . (int) $id ), 0, 32 );
	}
}