<?php
/**
 * Wpdb-backed audit-event repository.
 *
 * Phase 2 (KI-006) production {@see AuditEventRepository}
 * implementation that persists `affilun_audit_events` rows through
 * the WordPress `$wpdb` driver. Mirrors {@see WpdbLogRepository}'s
 * append-only structure because both surfaces share the same
 * write-once / paginate-read / never-mutate lifecycle: §10.1
 * declares the audit trail as append-only evidence and corrections
 * are themselves recorded as new audit rows so the trail remains
 * tamper-evident.
 *
 * Design notes:
 *
 *   - The physical table name is resolved through the injected
 *     {@see TableNameResolver} (see {@see PrefixedTableNameResolver}
 *     for the production default) and cached on the instance so
 *     every method emits the same identifier even when the host
 *     site mutates `$wpdb->prefix` mid-request (for example via
 *     `switch_to_blog()`). Call sites that need per-request prefix
 *     lookup should construct a fresh repository after each switch,
 *     matching the stability contract on
 *     {@see PrefixedTableNameResolver}.
 *
 *   - The resolved name is interpolated into the SQL string
 *     directly because `$wpdb->prepare()` cannot bind identifiers —
 *     the resolver is the trust boundary here, and its input comes
 *     from a class-level constant, not user data.
 *
 *   - Driver errors (`$wpdb->last_error`) are promoted to
 *     {@see RuntimeException} via {@see self::assertNoDriverError()}
 *     so call sites can catch at a single boundary without
 *     importing driver classes.
 *
 *   - `$wpdb` is typed as `object` rather than the concrete `\wpdb`
 *     class so this file stays trivially loadable in unit tests
 *     that run outside a WordPress environment. The repository
 *     still requires the real interface surface at call time; the
 *     abstract type just keeps PHPStan and the autoloader from
 *     pulling in `\wpdb` for pure-unit runs.
 *
 *   - {@see self::save()} is INSERT-ONLY. Records that already
 *     carry a non-null id raise {@see RuntimeException} per the
 *     §10.1 append-only invariant. Inserts allocate the
 *     auto-increment primary key and return a new record carrying
 *     the assigned id, matching the {@see WpdbLogRepository}
 *     convention.
 *
 *   - {@see self::findPaginated()} issues two queries: a
 *     `SELECT COUNT(*)` under the same WHERE clause for the
 *     pre-pagination total, and a `SELECT *` with
 *     `LIMIT $perPage OFFSET $offset` for the page slice. Both
 *     pin the same WHERE arguments through `$wpdb->prepare()` so
 *     the total reported on the result envelope cannot disagree
 *     with the row set. Ordering is fixed at
 *     `created_at DESC, id DESC` because §12.4 does not expose a
 *     sort parameter.
 *
 *   - {@see self::countByAuditKind()} issues a single
 *     `SELECT audit_kind, COUNT(*) ... GROUP BY audit_kind` query
 *     so the database does the grouping rather than this layer
 *     materializing every row. Kinds with zero occurrences are
 *     OMITTED from the returned map per the contract.
 *
 * @package Affilun\Infrastructure\Persistence
 */

declare(strict_types=1);

// phpcs:disable Generic.Files.LineLength.TooLong -- Developer-facing exception strings include full class/method context.

// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter -- Repository SQL uses table-name resolver output plus prepared values.

namespace Affilun\Infrastructure\Persistence;

use DateTimeImmutable;
use DateTimeZone;
use InvalidArgumentException;
use RuntimeException;

/**
 * {@see AuditEventRepository} implementation backed by WordPress's
 * `$wpdb`.
 *
 * See the file-level docblock for the table-name resolution
 * strategy, the §10.1 append-only invariant the
 * {@see self::save()} path enforces, the
 * {@see self::findPaginated()} query strategy, and the
 * {@see self::countByAuditKind()} grouping behavior.
 */
final class WpdbAuditEventRepository implements AuditEventRepository {

	/**
	 * Logical, unprefixed AFFILUN table name this repository targets.
	 *
	 * Named as a class-level constant so tests and diagnostics can
	 * reference the exact name without duplicating the string
	 * literal, and so a future rename has a single location to
	 * update.
	 */
	public const TABLE_LOGICAL_NAME = 'affilun_audit_events';

	/**
	 * Insertable column names in canonical §10.2 order. The `id`
	 * column is omitted because it is driver-managed.
	 *
	 * @var list<string>
	 */
	private const COLUMNS = array(
		'event_type',
		'audit_kind',
		'actor_type',
		'actor_id',
		'target_type',
		'target_id',
		'action',
		'result',
		'safe_summary',
		'changed_fields_json',
		'context_json',
		'created_at',
	);

	/**
	 * `$wpdb`-format placeholders for {@see self::COLUMNS}, in the
	 * same order. Used to feed the format-list argument of
	 * `$wpdb->insert()`.
	 *
	 * @var array<string, string>
	 */
	private const COLUMN_FORMATS = array(
		'event_type'          => '%s',
		'audit_kind'          => '%s',
		'actor_type'          => '%s',
		'actor_id'            => '%s',
		'target_type'         => '%s',
		'target_id'           => '%s',
		'action'              => '%s',
		'result'              => '%s',
		'safe_summary'        => '%s',
		'changed_fields_json' => '%s',
		'context_json'        => '%s',
		'created_at'          => '%s',
	);

	/**
	 * `$wpdb`-shaped collaborator. Typed as `object` so the file
	 * stays trivially loadable in unit tests; see file-level
	 * docblock for the rationale.
	 */
	private object $wpdb;

	/**
	 * Cached physical table name. Resolved once at construction
	 * time so repeated calls emit the same identifier even when
	 * the host site mutates `$wpdb->prefix` mid-request.
	 */
	private string $tableName;

	/**
	 * Construct the repository.
	 *
	 * @param object            $wpdb       WordPress `$wpdb`-like
	 *                                      object exposing
	 *                                      `insert()`, `delete()`,
	 *                                      `prepare()`, `get_row()`,
	 *                                      `get_var()`,
	 *                                      `get_results()`, the
	 *                                      `last_error` property,
	 *                                      and the `insert_id`
	 *                                      property.
	 * @param TableNameResolver $tableNames Resolver used to derive
	 *                                      the physical
	 *                                      `affilun_audit_events` name.
	 */
	public function __construct( object $wpdb, TableNameResolver $tableNames ) {
		$this->wpdb      = $wpdb;
		$this->tableName = $tableNames->resolve( self::TABLE_LOGICAL_NAME );
	}

	/**
	 * Physical table name this repository targets, resolved once
	 * at construction time through the injected
	 * {@see TableNameResolver}.
	 *
	 * Exposed primarily for tests and diagnostics; production
	 * code should not need to interpolate the name by hand.
	 */
	public function tableName(): string {
		return $this->tableName;
	}

	/**
	 * Persist the given audit-event record.
	 *
	 * INSERT-ONLY: rejects records that already carry a non-null
	 * id with {@see RuntimeException} per the §10.1 append-only
	 * invariant.
	 *
	 * @param AuditEventRecord $record Record to persist; id MUST
	 *                                 be null.
	 *
	 * @throws RuntimeException When `$record` already carries a
	 *                          non-null id, when `$wpdb` reports
	 *                          an error, when `$wpdb->insert()`
	 *                          returns false without a driver
	 *                          error, or when `$wpdb->insert_id`
	 *                          is not populated.
	 */
	public function save( AuditEventRecord $record ): AuditEventRecord {
		if ( $record->id() !== null ) {
			$message = sprintf(
				'WpdbAuditEventRepository::save() rejects records with a non-null id (got %d); '
				. 'affilun_audit_events is append-only per database-schema.md §10.1.',
				$record->id()
			);
			// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
			throw new RuntimeException( $message );
		}

		$row     = $this->columnPayload( $record );
		$formats = $this->formatsFor( array_keys( $row ) );

		$this->clearLastError();

		/** @var false|int $result */
		$result = $this->wpdb->insert( $this->tableName, $row, $formats );

		$this->assertNoDriverError( 'insert' );

		if ( $result === false ) {
			$message = 'WpdbAuditEventRepository::save(): $wpdb->insert() returned false without raising a driver error.';
			// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
			throw new RuntimeException( $message );
		}

		$insertId = property_exists( $this->wpdb, 'insert_id' ) ? (int) $this->wpdb->insert_id : 0;
		if ( $insertId <= 0 ) {
			$message = 'WpdbAuditEventRepository::save(): $wpdb->insert() did not populate insert_id.';
			// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
			throw new RuntimeException( $message );
		}

		return $record->withId( $insertId );
	}

	/**
	 * Look up a record by its primary key.
	 *
	 * Returns null when no row matches; raises
	 * {@see InvalidArgumentException} for non-positive ids so a
	 * programming error at the call site surfaces loudly instead
	 * of silently missing.
	 *
	 * @param int $id Primary key to look up.
	 *
	 * @throws InvalidArgumentException When `$id` is non-positive.
	 *                                  Driver errors surface as
	 *                                  RuntimeException via
	 *                                  {@see self::fetchOne()}.
	 */
	public function findById( int $id ): ?AuditEventRecord {
		if ( $id <= 0 ) {
			$message = sprintf(
				'WpdbAuditEventRepository::findById() requires a positive id, got %d.',
				$id
			);
			// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
			throw new InvalidArgumentException( $message );
		}

		return $this->fetchOne(
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name resolved from a class-level constant, not user input.
			'SELECT * FROM ' . $this->tableName . ' WHERE id = %d LIMIT 1',
			$id
		);
	}

	/**
	 * Remove the row identified by the given id.
	 *
	 * Reserved for the §10.4 retention purge path; application-
	 * layer audit writers MUST NOT call this during normal
	 * operation. Idempotent: returns true when a row was
	 * removed, false when no row matched.
	 *
	 * Non-positive ids raise {@see InvalidArgumentException} so
	 * a programming error at the call site surfaces loudly
	 * instead of silently missing.
	 *
	 * @param int $id Primary key of the row to delete.
	 *
	 * @throws InvalidArgumentException When `$id` is non-positive.
	 * @throws RuntimeException         When `$wpdb` reports an error.
	 */
	public function delete( int $id ): bool {
		if ( $id <= 0 ) {
			$message = sprintf(
				'WpdbAuditEventRepository::delete() requires a positive id, got %d.',
				$id
			);
			// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
			throw new InvalidArgumentException( $message );
		}

		$this->clearLastError();

		/** @var false|int $result */
		$result = $this->wpdb->delete(
			$this->tableName,
			array( 'id' => $id ),
			array( '%d' )
		);

		$this->assertNoDriverError( 'delete' );

		if ( $result === false ) {
			$message = sprintf(
				'WpdbAuditEventRepository::delete(): $wpdb->delete() returned false for id %d '
				. 'without raising a driver error.',
				$id
			);
			// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
			throw new RuntimeException( $message );
		}

		return (int) $result > 0;
	}

	/**
	 * Total number of rows currently stored in
	 * `affilun_audit_events`.
	 *
	 * Backed by a `SELECT COUNT(*)` query rather than
	 * materializing every row.
	 *
	 * @throws RuntimeException When `$wpdb` reports an error.
	 */
	public function count(): int {
		$this->clearLastError();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Counting AFFILUN-owned rows; caching handled by caller where relevant.
		$value = $this->wpdb->get_var(
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared -- Table name resolved from a class-level constant, not user input.
			'SELECT COUNT(*) FROM ' . $this->tableName
		);

		$this->assertNoDriverError( 'count' );

		return (int) $value;
	}

	/**
	 * Look up a paginated, filtered slice of stored rows.
	 *
	 * Implements {@see AuditEventRepository::findPaginated()}
	 * against `$wpdb`. The WHERE clause is built dynamically by
	 * appending one fragment per non-null filter on `$query`;
	 * every parameterized value is bound through
	 * `$wpdb->prepare()`. The ordering is fixed at
	 * `created_at DESC, id DESC` because §12.4 does not expose a
	 * sort parameter.
	 *
	 * Two queries are issued: a `SELECT COUNT(*)` under the same
	 * WHERE clause for the pre-pagination total, and a
	 * `SELECT *` with `LIMIT $perPage OFFSET $offset` for the
	 * page slice. Both pin the same WHERE arguments through
	 * `$wpdb->prepare()` so the total reported on the result
	 * envelope cannot disagree with the row set.
	 *
	 * @param AuditEventListQuery $query Validated list-query inputs.
	 *
	 * @throws RuntimeException When `$wpdb` reports an error or
	 *                          when a returned row cannot be
	 *                          decoded into an
	 *                          {@see AuditEventRecord}.
	 */
	public function findPaginated( AuditEventListQuery $query ): AuditEventListResult {
		$whereSql  = '';
		$whereArgs = array();

		if ( $query->auditKind() !== null ) {
			$whereSql   .= ' AND audit_kind = %s';
			$whereArgs[] = $query->auditKind();
		}

		if ( $query->actorId() !== null ) {
			$whereSql   .= ' AND actor_id = %s';
			$whereArgs[] = $query->actorId();
		}

		if ( $query->targetType() !== null ) {
			$whereSql   .= ' AND target_type = %s';
			$whereArgs[] = $query->targetType();
		}

		if ( $query->targetId() !== null ) {
			$whereSql   .= ' AND target_id = %s';
			$whereArgs[] = $query->targetId();
		}

		// Phase 2 KI-006 follow-up: §12.4 context-based filters
		// (request_id / correlation_id / job_id). These three
		// values are stored inside the `context_json` payload
		// rather than first-class columns; honor them via
		// JSON_EXTRACT, which is available on every supported
		// RDBMS per database-schema.md §3.3 (MySQL 5.7.22+ /
		// MySQL 8.0+ / MariaDB 10.3+). JSON_UNQUOTE strips the
		// surrounding double quotes JSON_EXTRACT adds to string
		// scalars so the equality comparison sees the raw
		// caller-supplied filter value rather than a quoted form.
		// Spec §10.3 explicitly recommends a generated-column /
		// function-index pair on these same JSON paths so an
		// operator that hits scan limits can opt into the
		// indexed form without changing this query shape.
		if ( $query->requestId() !== null ) {
			$whereSql   .= " AND JSON_UNQUOTE(JSON_EXTRACT(context_json, '$.request_id')) = %s";
			$whereArgs[] = $query->requestId();
		}

		if ( $query->correlationId() !== null ) {
			$whereSql   .= " AND JSON_UNQUOTE(JSON_EXTRACT(context_json, '$.correlation_id')) = %s";
			$whereArgs[] = $query->correlationId();
		}

		if ( $query->jobId() !== null ) {
			$whereSql   .= " AND JSON_UNQUOTE(JSON_EXTRACT(context_json, '$.job_id')) = %s";
			$whereArgs[] = $query->jobId();
		}

		$whereClause = $whereSql === '' ? '' : ' WHERE 1=1' . $whereSql;

		$page    = $query->page();
		$perPage = $query->perPage();
		$offset  = $query->offset();

		// Total count under the same WHERE clause, before pagination.
		$countSql = 'SELECT COUNT(*) FROM ' . $this->tableName . $whereClause;
		$this->clearLastError();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Counting AFFILUN-owned rows; caching handled by caller where relevant.
		$totalRaw = $whereArgs !== array()
			? $this->wpdb->get_var(
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name resolved from a class-level constant; values bound immediately here.
				$this->wpdb->prepare( $countSql, ...$whereArgs )
			)
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Constant count query contains no user-supplied values when there are no WHERE args.
			: $this->wpdb->get_var( $countSql );
		$this->assertNoDriverError( 'count' );
		$total = (int) $totalRaw;

		// Page slice under the same WHERE clause, sorted newest-first.
		$selectSql  = 'SELECT * FROM ' . $this->tableName
			. $whereClause
			. ' ORDER BY created_at DESC, id DESC'
			. ' LIMIT %d OFFSET %d';
		$selectArgs = array_merge( $whereArgs, array( $perPage, $offset ) );

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name resolved from a class-level constant; values bound via prepare().
		$items = $this->fetchMany( $selectSql, ...$selectArgs );

		return new AuditEventListResult( $items, $total, $page, $perPage );
	}

	/**
	 * Group every stored row by `audit_kind` and return the
	 * per-kind counts.
	 *
	 * See {@see AuditEventRepository::countByAuditKind()} for
	 * the contract; in particular, kinds with zero occurrences
	 * are omitted so callers default-fill against
	 * {@see AuditEventListQuery::VALID_AUDIT_KINDS}.
	 *
	 * Backed by a single
	 * `SELECT audit_kind, COUNT(*) ... GROUP BY audit_kind`
	 * query so the database does the grouping rather than this
	 * layer materializing every row.
	 *
	 * @return array<string, int>
	 *
	 * @throws RuntimeException When `$wpdb` reports an error.
	 */
	public function countByAuditKind(): array {
		$this->clearLastError();

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared -- Table name resolved from a class-level constant, not user input.
		$sql = 'SELECT audit_kind, COUNT(*) AS c FROM ' . $this->tableName . ' GROUP BY audit_kind';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Aggregating AFFILUN-owned rows; caching handled by caller where relevant.
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- SQL is a constant statement built from class-level identifiers; no user input bound.
		$rows = $this->wpdb->get_results( $sql, 'ARRAY_A' );

		$this->assertNoDriverError( 'select' );

		if ( ! is_array( $rows ) ) {
			$message = 'WpdbAuditEventRepository::countByAuditKind(): $wpdb->get_results() returned a non-array result set.';
			// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
			throw new RuntimeException( $message );
		}

		$counts = array();
		foreach ( $rows as $row ) {
			if ( ! is_array( $row ) ) {
				$message = 'WpdbAuditEventRepository::countByAuditKind(): $wpdb->get_results() returned a non-array row.';
				// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
				throw new RuntimeException( $message );
			}
			if ( ! array_key_exists( 'audit_kind', $row ) || ! array_key_exists( 'c', $row ) ) {
				$message = 'WpdbAuditEventRepository::countByAuditKind(): grouped row missing "audit_kind" or "c" key.';
				// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
				throw new RuntimeException( $message );
			}
			$counts[ (string) $row['audit_kind'] ] = (int) $row['c'];
		}

		return $counts;
	}

	/**
	 * Prepare and execute a SELECT, then decode every row into an
	 * {@see AuditEventRecord}.
	 *
	 * Centralizes the multi-row decoding boilerplate used by
	 * {@see self::findPaginated()} so the per-method body stays
	 * focused on query construction.
	 *
	 * @param string $sql  SQL containing placeholders.
	 * @param mixed  ...$args Placeholder values.
	 *
	 * @return list<AuditEventRecord>
	 *
	 * @throws RuntimeException When `$wpdb` reports an error or
	 *                          when a returned row cannot be
	 *                          decoded into an
	 *                          {@see AuditEventRecord}.
	 */
	private function fetchMany( string $sql, ...$args ): array {
		$this->clearLastError();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Bulk read of AFFILUN-owned rows; caching handled by caller where relevant.
		$rows = $this->wpdb->get_results(
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name resolved from a class-level constant; values bound immediately here.
			$this->wpdb->prepare( $sql, ...$args ),
			'ARRAY_A'
		);

		$this->assertNoDriverError( 'select' );

		if ( ! is_array( $rows ) ) {
			$message = 'WpdbAuditEventRepository::fetchMany(): $wpdb->get_results() returned a non-array result set.';
			// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
			throw new RuntimeException( $message );
		}

		$result = array();
		foreach ( $rows as $row ) {
			if ( ! is_array( $row ) ) {
				$message = 'WpdbAuditEventRepository::fetchMany(): $wpdb->get_results() returned a non-array row.';
				// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
				throw new RuntimeException( $message );
			}
			/** @var array<string, mixed> $row */
			$result[] = AuditEventRecord::fromArray( $row );
		}

		/** @var list<AuditEventRecord> $result */
		return $result;
	}

	/**
	 * Prepare and execute a SELECT, then decode the first row into an
	 * {@see AuditEventRecord}, or return null when the query
	 * matched no row.
	 *
	 * Centralizes the error-promotion and decoding paths shared
	 * by {@see self::findById()} so the per-method body stays
	 * focused on query construction.
	 *
	 * @param string $sql  SQL containing placeholders.
	 * @param mixed  ...$args Placeholder values.
	 *
	 * @throws RuntimeException When `$wpdb` reports an error or
	 *                          when the returned row cannot be
	 *                          decoded into an
	 *                          {@see AuditEventRecord}.
	 */
	private function fetchOne( string $sql, ...$args ): ?AuditEventRecord {
		$this->clearLastError();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Single-row read of AFFILUN-owned rows; caching handled by caller where relevant.
		$row = $this->wpdb->get_row(
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name resolved from a class-level constant; values bound immediately here.
			$this->wpdb->prepare( $sql, ...$args ),
			'ARRAY_A'
		);

		$this->assertNoDriverError( 'select' );

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

		if ( ! is_array( $row ) ) {
			$message = 'WpdbAuditEventRepository::fetchOne(): $wpdb->get_row() returned a non-array row.';
			// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
			throw new RuntimeException( $message );
		}

		/** @var array<string, mixed> $row */
		return AuditEventRecord::fromArray( $row );
	}

	/**
	 * Build the column payload handed to `$wpdb->insert()`.
	 *
	 * Delegates to {@see AuditEventRecord::toArray()} for the
	 * encoded row shape (timestamp formatted as a MySQL
	 * `DATETIME` string, nullable columns preserved as `null`)
	 * and strips the `id` entry because that column is
	 * driver-managed.
	 *
	 * @param AuditEventRecord $record Record to encode.
	 *
	 * @return array<string, string|int|null>
	 */
	private function columnPayload( AuditEventRecord $record ): array {
		$row = $record->toArray();
		unset( $row['id'] );

		/** @var array<string, string|int|null> $filtered */
		$filtered = array();
		foreach ( self::COLUMNS as $column ) {
			$filtered[ $column ] = $row[ $column ] ?? null;
		}

		return $filtered;
	}

	/**
	 * Produce the `$wpdb` format list for the given column names,
	 * in the order supplied.
	 *
	 * The WordPress `$wpdb->insert()` API expects the format list
	 * to line up positionally with the column payload, so this
	 * helper preserves the caller's ordering.
	 *
	 * @param list<string> $columns Column names in payload order.
	 *
	 * @return list<string> Matching format placeholders.
	 *
	 * @throws RuntimeException When a column has no registered
	 *                          format.
	 */
	private function formatsFor( array $columns ): array {
		$formats = array();
		foreach ( $columns as $column ) {
			if ( ! array_key_exists( $column, self::COLUMN_FORMATS ) ) {
				$message = sprintf(
					'WpdbAuditEventRepository::formatsFor(): no format registered for column "%s".',
					$column
				);
				// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
				throw new RuntimeException( $message );
			}
			$formats[] = self::COLUMN_FORMATS[ $column ];
		}

		return $formats;
	}

	/**
	 * Clear `$wpdb->last_error` before a call so the post-call
	 * probe only sees errors produced by the current statement.
	 */
	private function clearLastError(): void {
		if ( property_exists( $this->wpdb, 'last_error' ) ) {
			$this->wpdb->last_error = '';
		}
	}

	/**
	 * Raise a {@see RuntimeException} when `$wpdb->last_error` is
	 * populated, carrying the driver message verbatim for
	 * diagnosis.
	 *
	 * @param string $operation Short operation label used in the
	 *                          error message (for example
	 *                          `insert`, `delete`, `select`).
	 *
	 * @throws RuntimeException When a driver error is detected.
	 */
	private function assertNoDriverError( string $operation ): void {
		if ( ! property_exists( $this->wpdb, 'last_error' ) ) {
			return;
		}

		$lastError = (string) $this->wpdb->last_error;
		if ( $lastError === '' ) {
			return;
		}

		$message = sprintf(
			'WpdbAuditEventRepository: $wpdb reported an error during %s: %s',
			$operation,
			$lastError
		);
		// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
		throw new RuntimeException( $message );
	}

	/**
	 * Delete every row whose `created_at` is strictly older than
	 * the supplied UTC cutoff and return the number of rows
	 * removed.
	 *
	 * Phase 2 KI-006 follow-up: implements the §10.4 retention
	 * purge contract documented on
	 * {@see AuditEventRepository::deleteOlderThan()}.
	 *
	 * Issues a single prepared
	 * `DELETE FROM affilun_audit_events WHERE created_at < %s` query
	 * and reads the affected-row count from `$wpdb->rows_affected`
	 * (populated by `$wpdb->query()` on every DELETE / UPDATE /
	 * INSERT) so the call is one round-trip per sweep regardless
	 * of how many rows the cutoff matches.
	 *
	 * Strict-less-than comparison: a row whose `created_at`
	 * exactly equals the cutoff is preserved (so a row landing
	 * on the boundary survives at least one full sweep before
	 * disappearing on the next tick). The cutoff is normalized
	 * to UTC and formatted via {@see AuditEventRecord::MYSQL_DATETIME_FORMAT}
	 * so a non-UTC clock cannot smuggle in an off-by-one drift
	 * relative to the column's UTC storage convention.
	 *
	 * Non-int / negative `rows_affected` results coerce to 0
	 * defensively; the §10.4 retention sweep should never
	 * underflow but a hostile / mocked `$wpdb` should not be
	 * able to inject a negative count into the audit summary
	 * the worker emits.
	 *
	 * @param DateTimeImmutable $cutoff UTC cutoff instant.
	 *
	 * @return int Number of rows removed.
	 *
	 * @throws RuntimeException When `$wpdb` reports an error.
	 */
	public function deleteOlderThan( DateTimeImmutable $cutoff ): int {
		$normalizedCutoff = $cutoff->setTimezone( new DateTimeZone( 'UTC' ) );
		$cutoffString     = $normalizedCutoff->format( AuditEventRecord::MYSQL_DATETIME_FORMAT );

		$this->clearLastError();

		$sql = $this->wpdb->prepare(
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared -- Table name resolved from a class-level constant; cutoff value bound via prepare().
			'DELETE FROM ' . $this->tableName . ' WHERE created_at < %s',
			$cutoffString
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- $sql is already prepared above; retention purge is a AFFILUN-owned bulk delete with no caching surface.
		$this->wpdb->query( $sql );

		$this->assertNoDriverError( 'delete' );

		$affected = property_exists( $this->wpdb, 'rows_affected' )
			? $this->wpdb->rows_affected
			: 0;

		if ( ! is_int( $affected ) || $affected < 0 ) {
			return 0;
		}

		return $affected;
	}
}