<?php
/**
 * Ledger repository.
 *
 * @package AgentFirewall
 */

declare(strict_types=1);

namespace AgentFirewall\Repository;

use AgentFirewall\Model\LedgerEntry;
use DateTimeImmutable;
use wpdb;

defined( 'ABSPATH' ) || exit; // @codeCoverageIgnore

// phpcs:disable WordPress.DB.DirectDatabaseQuery -- Repositories are the sanctioned data-access layer for plugin-owned tables (ADR-6); caching happens above this layer.
// phpcs:disable WordPress.DB.PreparedSQL -- Table names derive from $wpdb->prefix (trusted); every value is bound via prepare() placeholders, verified by PHPStan.

/**
 * Data access for adaf_ledger (append-only; only status/result mutate, ADR-6).
 */
final class LedgerRepository {

	/**
	 * Constructor.
	 *
	 * @param wpdb $wpdb WordPress database.
	 */
	public function __construct( private readonly wpdb $wpdb ) {
	}

	/**
	 * Fully prefixed table name for the site WordPress is on now. Worked
	 * out per query, so after switch_to_blog() it is that site's table
	 * rather than the one this repository was created on (ADR-16, found
	 * 2026-10-16).
	 */
	private function table(): string {
		return $this->wpdb->prefix . 'adaf_ledger';
	}

	/**
	 * Append a ledger row.
	 *
	 * Required keys: ts (DateTimeImmutable), agent_id, wp_user_id, source,
	 * action_cat, entity_type, status. Optional: entity_id, ability_name,
	 * input (array), result (array), before_hash, chain_hash, prev_id.
	 *
	 * @param array<string, mixed> $data Row data.
	 * @return int Inserted row id.
	 */
	public function insert( array $data ): int {
		/** @var DateTimeImmutable $ts */
		$ts = $data['ts'];

		$this->wpdb->insert(
			$this->table(),
			array(
				'ts'           => $ts->format( 'Y-m-d H:i:s' ),
				'agent_id'     => (int) $data['agent_id'],
				'wp_user_id'   => (int) $data['wp_user_id'],
				'source'       => (string) $data['source'],
				'action_cat'   => (string) $data['action_cat'],
				'entity_type'  => (string) $data['entity_type'],
				'entity_id'    => isset( $data['entity_id'] ) ? (int) $data['entity_id'] : null,
				'entity_label' => isset( $data['entity_label'] ) ? (string) $data['entity_label'] : null,
				'ability_name' => isset( $data['ability_name'] ) ? (string) $data['ability_name'] : null,
				'status'       => (string) $data['status'],
				'input_json'   => isset( $data['input'] ) ? (string) wp_json_encode( $data['input'] ) : null,
				'result_json'  => isset( $data['result'] ) ? (string) wp_json_encode( $data['result'] ) : null,
				'before_hash'  => isset( $data['before_hash'] ) ? (string) $data['before_hash'] : null,
				'chain_hash'   => isset( $data['chain_hash'] ) ? (string) $data['chain_hash'] : null,
				'policy_id'    => isset( $data['policy_id'] ) ? (int) $data['policy_id'] : null,
				'rule_index'   => isset( $data['rule_index'] ) ? (int) $data['rule_index'] : null,
				'prev_id'      => isset( $data['prev_id'] ) ? (int) $data['prev_id'] : null,
			) + (
				// Named only when there are facts, so a row without them
				// still inserts on a site part-way through the upgrade.
				isset( $data['facts'] ) && array() !== $data['facts'] ? array( 'facts_json' => (string) wp_json_encode( $data['facts'] ) ) : array()
			),
			array( '%s', '%d', '%d', '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%d', '%s' )
		);

		return (int) $this->wpdb->insert_id;
	}

	/**
	 * Append a row chained to the one named by its prev_id, unless another
	 * row got there first (AF-24.5, ADR-17).
	 *
	 * The unique key on prev_id lets only one row follow a given row, so an
	 * append that read the same head as a concurrent one fails here instead
	 * of forking the chain. That failure is expected under concurrency, so
	 * it is kept out of the error output; the caller reads the head again.
	 *
	 * @param array<string, mixed> $data Row data, prev_id included.
	 * @return int|null Inserted row id; null when another row already follows
	 *                  that predecessor; 0 when the insert failed otherwise.
	 */
	public function insert_chained( array $data ): ?int {
		$suppressed = $this->wpdb->suppress_errors( true );
		$id         = $this->insert( $data );
		$taken      = null;
		if ( $id <= 0 ) {
			// Asked of the table rather than read from the error text, which
			// differs between MySQL, MariaDB and SQLite.
			$taken = $this->wpdb->get_var(
				$this->wpdb->prepare(
					"SELECT id FROM {$this->table()} WHERE prev_id = %d",
					(int) ( $data['prev_id'] ?? 0 )
				)
			);
		}
		$this->wpdb->suppress_errors( $suppressed );

		if ( $id > 0 ) {
			return $id;
		}

		return null === $taken ? 0 : null;
	}

	/**
	 * Find by primary key.
	 *
	 * @param int $id Row id.
	 */
	public function find( int $id ): ?LedgerEntry {
		$row = $this->wpdb->get_row(
			$this->wpdb->prepare( "SELECT * FROM {$this->table()} WHERE id = %d", $id ),
			ARRAY_A
		);

		return null === $row ? null : $this->hydrate( $row );
	}

	/**
	 * Filterable, paginated query. Newest first.
	 *
	 * Supported filters: agent_id, status, entity_type, entity_id,
	 * since (DateTimeImmutable), until (DateTimeImmutable), limit, offset.
	 *
	 * @param array<string, mixed> $filters Filters.
	 * @return list<LedgerEntry>
	 */
	public function query( array $filters = array() ): array {
		list( $where, $values ) = $this->build_where( $filters );

		$sql = "SELECT * FROM {$this->table()}";
		if ( array() !== $where ) {
			$sql .= ' WHERE ' . implode( ' AND ', $where );
		}
		$sql .= ' ORDER BY ts DESC, id DESC';

		$limit    = isset( $filters['limit'] ) ? max( 1, (int) $filters['limit'] ) : 50;
		$offset   = isset( $filters['offset'] ) ? max( 0, (int) $filters['offset'] ) : 0;
		$sql     .= ' LIMIT %d OFFSET %d';
		$values[] = $limit;
		$values[] = $offset;

		$rows = $this->wpdb->get_results(
			// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- Placeholders are built dynamically above; every value is bound.
			$this->wpdb->prepare( $sql, $values ),
			ARRAY_A
		);

		return array_values( array_map( array( $this, 'hydrate' ), $rows ?? array() ) );
	}

	/**
	 * One page of rows matching query()'s filters, newest id first, strictly
	 * below an id (keyset paging, for exports).
	 *
	 * Offsets shift when rows arrive mid-read and a busy site writes while an
	 * export runs; keyset paging below a fixed upper id reads every row that
	 * existed when the export began exactly once, and none written after.
	 *
	 * @param array<string, mixed> $filters   Same filters as query(); limit/offset ignored.
	 * @param int                  $before_id Exclusive upper bound on id.
	 * @param int                  $limit     Page size.
	 * @return list<LedgerEntry>
	 */
	public function query_before( array $filters, int $before_id, int $limit ): array {
		list( $where, $values ) = $this->build_where( $filters );
		$where[]                = 'id < %d';
		$values[]               = $before_id;
		$values[]               = max( 1, $limit );

		$sql = "SELECT * FROM {$this->table()} WHERE " . implode( ' AND ', $where ) . ' ORDER BY id DESC LIMIT %d';

		$rows = $this->wpdb->get_results(
			// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- Placeholders are built dynamically above; every value is bound.
			$this->wpdb->prepare( $sql, $values ),
			ARRAY_A
		);

		return array_values( array_map( array( $this, 'hydrate' ), $rows ?? array() ) );
	}

	/**
	 * Rows since a moment, counted per status (absent statuses omitted).
	 *
	 * @param DateTimeImmutable $since Inclusive start.
	 * @return array<string, int>
	 */
	public function status_counts_since( DateTimeImmutable $since ): array {
		$rows = $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT status, COUNT(*) AS n FROM {$this->table()} WHERE ts >= %s GROUP BY status ORDER BY status",
				$since->format( 'Y-m-d H:i:s' )
			),
			ARRAY_A
		);

		$counts = array();
		foreach ( $rows ?? array() as $row ) {
			$counts[ (string) $row['status'] ] = (int) $row['n'];
		}
		return $counts;
	}

	/**
	 * One agent's rows since a moment, counted per status - the recent
	 * history the AI evaluator's summary carries (AF-21.4).
	 *
	 * @param int               $agent_id Agent.
	 * @param DateTimeImmutable $since    Inclusive start.
	 * @return array<string, int>
	 */
	public function status_counts_for_agent_since( int $agent_id, DateTimeImmutable $since ): array {
		$rows = $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT status, COUNT(*) AS n FROM {$this->table()} WHERE agent_id = %d AND ts >= %s GROUP BY status ORDER BY status",
				$agent_id,
				$since->format( 'Y-m-d H:i:s' )
			),
			ARRAY_A
		);

		$counts = array();
		foreach ( $rows ?? array() as $row ) {
			$counts[ (string) $row['status'] ] = (int) $row['n'];
		}
		return $counts;
	}

	/**
	 * Denials since a moment, counted for each reason asked about.
	 *
	 * The reason lives in the result_json the pipeline writes,
	 * {"denial_reason":"..."}; matched there with an escaped LIKE so the
	 * count stays one query per reason in SQL.
	 *
	 * @param DateTimeImmutable $since   Inclusive start.
	 * @param list<string>      $reasons Evaluation::REASON_* values.
	 * @return array<string, int> Reason => count, in the order asked.
	 */
	public function denial_reason_counts_since( DateTimeImmutable $since, array $reasons ): array {
		$counts = array();
		foreach ( $reasons as $reason ) {
			$counts[ $reason ] = (int) $this->wpdb->get_var(
				$this->wpdb->prepare(
					"SELECT COUNT(*) FROM {$this->table()} WHERE status = 'denied' AND ts >= %s AND result_json LIKE %s",
					$since->format( 'Y-m-d H:i:s' ),
					'%' . $this->wpdb->esc_like( '"denial_reason":"' . $reason . '"' ) . '%'
				)
			);
		}
		return $counts;
	}

	/**
	 * Refused attempts on the firewall's own controls since a moment
	 * (AF-24.1): `refused` rows on the `firewall` target, which nothing else
	 * writes.
	 *
	 * @param DateTimeImmutable $since Period start.
	 */
	public function count_control_plane_refusals_since( DateTimeImmutable $since ): int {
		return (int) $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT COUNT(*) FROM {$this->table()} WHERE status = 'refused' AND entity_type = 'firewall' AND ts >= %s",
				$since->format( 'Y-m-d H:i:s' )
			)
		);
	}

	/**
	 * Count rows matching the same filters query() accepts.
	 *
	 * @param array<string, mixed> $filters Filters.
	 */
	public function count( array $filters = array() ): int {
		list( $where, $values ) = $this->build_where( $filters );

		$sql = "SELECT COUNT(*) FROM {$this->table()}";
		if ( array() !== $where ) {
			$sql .= ' WHERE ' . implode( ' AND ', $where );
		}

		if ( array() === $values ) {
			return (int) $this->wpdb->get_var( $sql );
		}

		return (int) $this->wpdb->get_var(
			// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- Placeholders built dynamically; every value bound.
			$this->wpdb->prepare( $sql, $values )
		);
	}

	/**
	 * Shared WHERE builder for query()/count().
	 *
	 * @param array<string, mixed> $filters Filters.
	 * @return array{0: list<string>, 1: list<int|string>}
	 */
	private function build_where( array $filters ): array {
		$where  = array();
		$values = array();

		if ( isset( $filters['agent_id'] ) ) {
			$where[]  = 'agent_id = %d';
			$values[] = (int) $filters['agent_id'];
		}
		if ( isset( $filters['status'] ) ) {
			$where[]  = 'status = %s';
			$values[] = (string) $filters['status'];
		}
		if ( isset( $filters['entity_type'] ) ) {
			$where[]  = 'entity_type = %s';
			$values[] = (string) $filters['entity_type'];
		}
		if ( isset( $filters['entity_id'] ) ) {
			$where[]  = 'entity_id = %d';
			$values[] = (int) $filters['entity_id'];
		}
		if ( isset( $filters['policy_id'] ) ) {
			$where[]  = 'policy_id = %d';
			$values[] = (int) $filters['policy_id'];
		}
		// Entries no rule covered, which is what the built-in defaults decided.
		// Deliberately separate from policy_id rather than overloading 0: the
		// distinction between "no filter" and "filter for none" is too easy to
		// lose through a sanitizer that turns an empty string into an integer.
		if ( ! empty( $filters['default_decision'] ) ) {
			// A network rule's decision has no site policy either (ADR-16
			// §2), nor does an extension default's (AF-25.10); neither is
			// the defaults.
			$where[]  = 'policy_id IS NULL AND ( result_json IS NULL OR ( result_json NOT LIKE %s AND result_json NOT LIKE %s ) )';
			$values[] = '%' . $this->wpdb->esc_like( '"network":true' ) . '%';
			$values[] = '%' . $this->wpdb->esc_like( '"extension_default":' ) . '%';
		}
		if ( ! empty( $filters['extension_decision'] ) ) {
			$where[]  = 'policy_id IS NULL AND result_json LIKE %s';
			$values[] = '%' . $this->wpdb->esc_like( '"extension_default":' ) . '%';
		}
		if ( ! empty( $filters['network_decision'] ) ) {
			$where[]  = 'result_json LIKE %s';
			$values[] = '%' . $this->wpdb->esc_like( '"network":true' ) . '%';
		}
		if ( isset( $filters['since'] ) && $filters['since'] instanceof DateTimeImmutable ) {
			$where[]  = 'ts >= %s';
			$values[] = $filters['since']->format( 'Y-m-d H:i:s' );
		}
		if ( isset( $filters['until'] ) && $filters['until'] instanceof DateTimeImmutable ) {
			$where[]  = 'ts <= %s';
			$values[] = $filters['until']->format( 'Y-m-d H:i:s' );
		}

		return array( $where, $values );
	}

	/**
	 * Count an agent's performed writes since a timestamp (rate limiting).
	 *
	 * "Performed" = allowed, pending (queued intents consume budget too) or
	 * bypassed (answered by another plugin before the firewall could
	 * evaluate it, so it probably happened - ADR-13) or interrupted (an
	 * approval cut off mid-run, so it may have happened - AF-24.2); denied and refused
	 * attempts and reads never count.
	 *
	 * @param int                $agent_id Acting agent.
	 * @param DateTimeImmutable  $since    Window start (inclusive).
	 */
	public function count_writes_since( int $agent_id, DateTimeImmutable $since ): int {
		return (int) $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT COUNT(*) FROM {$this->table()} WHERE agent_id = %d AND ts >= %s AND action_cat != 'read' AND status IN ('allowed', 'pending', 'bypassed', 'interrupted')",
				$agent_id,
				$since->format( 'Y-m-d H:i:s' )
			)
		);
	}

	/**
	 * Timestamp of the oldest write counted by the rate limiter within a
	 * window, or null when there are none.
	 *
	 * Mirrors count_writes_since()'s definition of a counted write exactly, so
	 * the retry hint describes the same set the limit does.
	 *
	 * @param int               $agent_id Acting agent.
	 * @param DateTimeImmutable $since    Window start (inclusive).
	 */
	public function oldest_write_since( int $agent_id, DateTimeImmutable $since ): ?DateTimeImmutable {
		$ts = $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT ts FROM {$this->table()} WHERE agent_id = %d AND ts >= %s AND action_cat != 'read' AND status IN ('allowed', 'pending', 'bypassed', 'interrupted') ORDER BY ts ASC LIMIT 1",
				$agent_id,
				$since->format( 'Y-m-d H:i:s' )
			)
		);

		return null === $ts ? null : new DateTimeImmutable( (string) $ts );
	}

	/**
	 * An agent's values of one fact since a timestamp, oldest first, over the
	 * rows the rate limiter counts (budgets, ADR-18 §6).
	 *
	 * The rows come through the agent/time index and the fact is read in
	 * PHP: JSON_EXTRACT behaves differently across the MySQL and MariaDB
	 * versions WordPress supports. The LIKE only narrows the rows to decode;
	 * a row whose fact is not a finite number is skipped.
	 *
	 * @param int               $agent_id Acting agent.
	 * @param string            $fact     Fact name.
	 * @param DateTimeImmutable $since    Window start (inclusive).
	 * @param int               $limit    Most rows to read.
	 * @return list<array{0: int, 1: int|float}>|null Unix time and value;
	 *                                               null when the read failed
	 *                                               or more than $limit rows
	 *                                               match, so the sum would
	 *                                               be short.
	 */
	public function fact_values_since( int $agent_id, string $fact, DateTimeImmutable $since, int $limit ): ?array {
		// The query flushes last_error, so it describes this query alone.
		$rows = $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT ts, facts_json FROM {$this->table()} WHERE agent_id = %d AND ts >= %s AND action_cat != 'read' AND status IN ('allowed', 'pending', 'bypassed', 'interrupted') AND facts_json LIKE %s ORDER BY ts ASC, id ASC LIMIT %d",
				$agent_id,
				$since->format( 'Y-m-d H:i:s' ),
				'%' . $this->wpdb->esc_like( (string) wp_json_encode( $fact ) . ':' ) . '%',
				$limit + 1
			),
			ARRAY_A
		);
		// A failed query still returns an empty array, and a budget read as
		// empty would let everything through, so a failure says so.
		if ( ! is_array( $rows ) || '' !== $this->wpdb->last_error || count( $rows ) > $limit ) {
			return null;
		}

		$values = array();
		foreach ( $rows as $row ) {
			$facts = json_decode( (string) $row['facts_json'], true );
			$value = is_array( $facts ) ? ( $facts[ $fact ] ?? null ) : null;
			if ( is_int( $value ) || ( is_float( $value ) && is_finite( $value ) ) ) {
				$values[] = array( ( new DateTimeImmutable( (string) $row['ts'] ) )->getTimestamp(), $value );
			}
		}

		return $values;
	}

	/**
	 * Count an agent's rows in one status against one kind of target since
	 * a timestamp - how many refusals of that kind it has had recorded
	 * recently, for the refusal throttle.
	 *
	 * @param int               $agent_id    Acting agent.
	 * @param string            $status      Ledger status.
	 * @param string            $entity_type Kind of target.
	 * @param DateTimeImmutable $since       Window start (inclusive).
	 */
	public function count_status_since( int $agent_id, string $status, string $entity_type, DateTimeImmutable $since ): int {
		return (int) $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT COUNT(*) FROM {$this->table()} WHERE agent_id = %d AND status = %s AND entity_type = %s AND ts >= %s",
				$agent_id,
				$status,
				$entity_type,
				$since->format( 'Y-m-d H:i:s' )
			)
		);
	}

	/**
	 * Rows after a given id, ascending; for chain verification walks.
	 *
	 * @param int $after_id Exclusive lower bound (0 = from the start).
	 * @param int $limit    Batch size.
	 * @return list<LedgerEntry>
	 */
	public function after( int $after_id, int $limit = 500 ): array {
		$rows = $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT * FROM {$this->table()} WHERE id > %d ORDER BY id ASC LIMIT %d",
				$after_id,
				$limit
			),
			ARRAY_A
		);

		return array_values( array_map( array( $this, 'hydrate' ), $rows ?? array() ) );
	}

	/**
	 * The row the next chained row follows - the last one - with its chain
	 * hash, read together so they describe the same row (AF-1.3, AF-24.5).
	 *
	 * The last row by id, whatever its hash: a row whose hash was cleared
	 * still has its place in the order, and its successor must follow it,
	 * not take the place of the row that already follows the one before.
	 * Verification reports the cleared row either way.
	 *
	 * @return array{id: int, hash: string|null}|null Null when the ledger is empty.
	 */
	public function chain_head(): ?array {
		$row = $this->wpdb->get_row(
			"SELECT id, chain_hash FROM {$this->table()} ORDER BY id DESC LIMIT 1",
			ARRAY_A
		);

		return is_array( $row )
			? array(
				'id'   => (int) $row['id'],
				'hash' => null === $row['chain_hash'] ? null : (string) $row['chain_hash'],
			)
			: null;
	}

	/**
	 * How often each policy rule has decided an action.
	 *
	 * Keyed by policy id, then by rule index. Rules that have never decided
	 * anything are absent rather than present with a zero, so the caller
	 * cannot mistake "no history" for "fired zero times" - the ledger only
	 * knows about rules it has seen, not about rules that exist.
	 *
	 * @return array<int, array<int, array{hits: int, last_fired: string}>>
	 */
	public function rule_usage(): array {
		$rows = $this->wpdb->get_results(
			"SELECT policy_id, rule_index, COUNT(*) AS hits, MAX(ts) AS last_fired
			FROM {$this->table()}
			WHERE policy_id IS NOT NULL AND rule_index IS NOT NULL
			GROUP BY policy_id, rule_index",
			ARRAY_A
		);

		$usage = array();
		foreach ( $rows ?? array() as $row ) {
			$usage[ (int) $row['policy_id'] ][ (int) $row['rule_index'] ] = array(
				'hits'       => (int) $row['hits'],
				'last_fired' => (string) $row['last_fired'],
			);
		}

		return $usage;
	}

	/**
	 * The longest run of entries from the start of the chain that may be
	 * removed: older than the cutoff, and not required by anything else.
	 *
	 * Deliberately a prefix rather than a filtered set. Verification resumes
	 * from the hash of the last removed row, which only works if what was
	 * removed is a contiguous run from the beginning; deleting a row from the
	 * middle would leave the row after it chained to something that no longer
	 * exists. So the scan stops at the first entry that has to stay, and that
	 * entry blocks everything behind it until it stops being protected.
	 *
	 * @param DateTimeImmutable $cutoff    Keep entries at or after this time.
	 * @param list<int>         $protected Ids that must never be removed.
	 * @param int               $limit     Maximum ids to return.
	 * @return list<int>
	 */
	public function prunable_prefix( DateTimeImmutable $cutoff, array $protected = array(), int $limit = 1000 ): array {
		$rows = $this->wpdb->get_results(
			$this->wpdb->prepare( "SELECT id, ts FROM {$this->table()} ORDER BY id ASC LIMIT %d", max( 1, $limit ) ),
			ARRAY_A
		);

		$keep     = array_flip( array_map( 'intval', $protected ) );
		$cutoff_s = $cutoff->format( 'Y-m-d H:i:s' );
		$ids      = array();

		foreach ( $rows ?? array() as $row ) {
			$id = (int) $row['id'];
			if ( isset( $keep[ $id ] ) || (string) $row['ts'] >= $cutoff_s ) {
				break;
			}

			$ids[] = $id;
		}

		return $ids;
	}

	/**
	 * Timestamp of the oldest retained entry, or null when the ledger is
	 * empty. This is how far back any "has never happened" claim can reach.
	 */
	public function oldest_timestamp(): ?string {
		$ts = $this->wpdb->get_var( "SELECT ts FROM {$this->table()} ORDER BY id ASC LIMIT 1" );

		return null === $ts ? null : (string) $ts;
	}

	/**
	 * The chain hash of one entry.
	 *
	 * @param int $id Row id.
	 */
	public function chain_hash_of( int $id ): ?string {
		$hash = $this->wpdb->get_var(
			$this->wpdb->prepare( "SELECT chain_hash FROM {$this->table()} WHERE id = %d", $id )
		);

		return null === $hash ? null : (string) $hash;
	}

	/**
	 * Delete entries by id. Used only by retention (ADR-6): the ledger is
	 * otherwise append-only.
	 *
	 * @param list<int> $ids Row ids.
	 * @return int Rows removed.
	 */
	public function delete_ids( array $ids ): int {
		if ( array() === $ids ) {
			return 0;
		}

		$placeholders = implode( ', ', array_fill( 0, count( $ids ), '%d' ) );

		// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- Placeholders built dynamically; every id is bound.
		$sql = $this->wpdb->prepare( "DELETE FROM {$this->table()} WHERE id IN ({$placeholders})", array_map( 'intval', $ids ) );
		if ( null === $sql ) {
			return 0;
		}

		$removed = (int) $this->wpdb->query( $sql );

		/**
		 * Fires after ledger rows are deleted - by retention, which is the
		 * only caller - so the Agency network index can drop them too and
		 * follow each site's retention (AF-22.2e).
		 *
		 * @param list<int> $ids Ledger row ids deleted.
		 */
		do_action( 'adaf_ledger_deleted', array_values( array_map( 'intval', $ids ) ) );

		return $removed;
	}

	/**
	 * Update the mutable status column (lifecycle transitions only, ADR-6).
	 *
	 * @param int    $id     Row id.
	 * @param string $status New status.
	 */
	public function update_status( int $id, string $status ): void {
		$this->wpdb->update(
			$this->table(),
			array( 'status' => $status ),
			array( 'id' => $id ),
			array( '%s' ),
			array( '%d' )
		);

		/**
		 * Fires after a ledger row's status changes - a hold approved,
		 * rejected or expired - so the Agency network index stays current
		 * (AF-22.2e).
		 *
		 * @param int    $id     Ledger row id.
		 * @param string $status New status.
		 */
		do_action( 'adaf_ledger_status_changed', $id, $status );
	}

	/**
	 * Change a row's status only if it still has an expected one, so a
	 * write that lost a race does not overwrite the winner's.
	 *
	 * @param int    $id   Row id.
	 * @param string $from Status the row must still have.
	 * @param string $to   New status.
	 * @return bool Whether the row changed.
	 */
	public function update_status_from( int $id, string $from, string $to ): bool {
		$changed = 1 === $this->wpdb->query(
			(string) $this->wpdb->prepare(
				"UPDATE {$this->table()} SET status = %s WHERE id = %d AND status = %s",
				$to,
				$id,
				$from
			)
		);

		if ( $changed ) {
			/** This action is documented in src/Repository/LedgerRepository.php */
			do_action( 'adaf_ledger_status_changed', $id, $to );
		}

		return $changed;
	}

	/**
	 * Update the mutable result column.
	 *
	 * @param int                  $id     Row id.
	 * @param array<string, mixed> $result Execution result payload.
	 */
	public function update_result( int $id, array $result ): void {
		$this->wpdb->update(
			$this->table(),
			array( 'result_json' => (string) wp_json_encode( $result ) ),
			array( 'id' => $id ),
			array( '%s' ),
			array( '%d' )
		);
	}

	/**
	 * Map a database row to the value object.
	 *
	 * @param array<string, string|null> $row Raw row.
	 */
	private function hydrate( array $row ): LedgerEntry {
		$input  = null === $row['input_json'] ? null : json_decode( (string) $row['input_json'], true );
		$result = null === $row['result_json'] ? null : json_decode( (string) $row['result_json'], true );
		$facts  = isset( $row['facts_json'] ) ? json_decode( (string) $row['facts_json'], true ) : null;

		return new LedgerEntry(
			(int) $row['id'],
			new DateTimeImmutable( (string) $row['ts'] ),
			(int) $row['agent_id'],
			(int) $row['wp_user_id'],
			(string) $row['source'],
			(string) $row['action_cat'],
			(string) $row['entity_type'],
			null === $row['entity_id'] ? null : (int) $row['entity_id'],
			null === $row['ability_name'] ? null : (string) $row['ability_name'],
			(string) $row['status'],
			is_array( $input ) ? $input : null,
			is_array( $result ) ? $result : null,
			null === $row['before_hash'] ? null : (string) $row['before_hash'],
			null === $row['chain_hash'] ? null : (string) $row['chain_hash'],
			null === $row['policy_id'] ? null : (int) $row['policy_id'],
			null === $row['rule_index'] ? null : (int) $row['rule_index'],
			// Coalesced, not indexed directly: a site part-way through the
			// upgrade can read rows before the column lands.
			isset( $row['entity_label'] ) ? (string) $row['entity_label'] : null,
			isset( $row['prev_id'] ) ? (int) $row['prev_id'] : null,
			is_array( $facts ) ? $facts : array()
		);
	}
}