<?php
/**
 * Pending action repository.
 *
 * @package AgentFirewall
 */

declare(strict_types=1);

namespace AgentFirewall\Repository;

use AgentFirewall\Model\PendingAction;
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_pending (approval queue).
 */
final class PendingRepository {

	/**
	 * 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_pending';
	}

	/**
	 * Queue a pending action.
	 *
	 * Required keys: ledger_id, agent_id, ability_name, stored_input (array),
	 * expires_at (DateTimeImmutable). Optional: initial_entity_modified_at,
	 * request (the REST replay envelope, AF-18.2), initial_fingerprint (a
	 * provider's drift hash, AF-25.4).
	 *
	 * The fingerprint column is named only when there is a fingerprint, so a
	 * site part-way through the DB_VERSION 11 upgrade still queues holds.
	 * Optional too: required_cap and requires_admin (AF-25.8). A row an
	 * extension claimed, which a site part-way through the DB_VERSION 12
	 * upgrade cannot store, is stored without them and so needs an
	 * administrator: stricter, never lost.
	 *
	 * @param array<string, mixed> $data Row data.
	 */
	public function create( array $data ): PendingAction {
		/** @var DateTimeImmutable $expires_at */
		$expires_at = $data['expires_at'];
		$initial    = $data['initial_entity_modified_at'] ?? null;
		$request    = isset( $data['request'] ) && is_array( $data['request'] ) ? $data['request'] : null;

		$fingerprint = isset( $data['initial_fingerprint'] ) && is_string( $data['initial_fingerprint'] ) ? $data['initial_fingerprint'] : null;

		$row = array(
			'ledger_id'                  => (int) $data['ledger_id'],
			'agent_id'                   => (int) $data['agent_id'],
			'ability_name'               => (string) $data['ability_name'],
			'stored_input'               => (string) wp_json_encode( $data['stored_input'] ),
			'request_json'               => null === $request ? null : (string) wp_json_encode( $request ),
			'initial_entity_modified_at' => $initial instanceof DateTimeImmutable ? $initial->format( 'Y-m-d H:i:s' ) : null,
			'expires_at'                 => $expires_at->format( 'Y-m-d H:i:s' ),
			'status'                     => 'pending',
		);
		if ( null !== $fingerprint ) {
			$row['initial_fingerprint'] = $fingerprint;
		}
		$capability = isset( $data['required_cap'] ) && is_string( $data['required_cap'] ) ? $data['required_cap'] : 'manage_options';
		$admin      = true === ( $data['requires_admin'] ?? false );
		$claimed    = 'manage_options' !== $capability || $admin;

		$formats = array_merge( array( '%d', '%d' ), array_fill( 0, count( $row ) - 2, '%s' ) );
		// Quiet on the first try only: the fallback below reports any error
		// that is not the missing columns.
		$quiet    = $this->wpdb->suppress_errors( true );
		$inserted = $claimed
			? $this->wpdb->insert(
				$this->table(),
				$row + array(
					'required_cap'   => $capability,
					'requires_admin' => $admin ? 1 : 0,
				),
				array_merge( $formats, array( '%s', '%d' ) )
			)
			: false;
		$this->wpdb->suppress_errors( $quiet );
		if ( false === $inserted ) {
			$capability = 'manage_options';
			$admin      = false;
			$this->wpdb->insert( $this->table(), $row, $formats );
		}

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

		return new PendingAction(
			$id,
			(int) $data['ledger_id'],
			(int) $data['agent_id'],
			(string) $data['ability_name'],
			(array) $data['stored_input'],
			$initial instanceof DateTimeImmutable ? $initial : null,
			$expires_at,
			'pending',
			null,
			null,
			$request,
			$fingerprint,
			$capability,
			$admin
		);
	}

	/**
	 * Ledger ids of actions still waiting for a human.
	 *
	 * Retention uses this to keep their audit records however old they are: a
	 * queued action whose ledger entry has been pruned is a request nobody can
	 * account for.
	 *
	 * @return list<int>
	 */
	public function ledger_ids_awaiting_decision(): array {
		$ids = $this->wpdb->get_col(
			$this->wpdb->prepare( "SELECT ledger_id FROM {$this->table()} WHERE status = %s", 'pending' )
		);

		return array_values( array_map( 'intval', $ids ?? array() ) );
	}

	/**
	 * Find by primary key.
	 *
	 * @param int $id Row id.
	 */
	public function find( int $id ): ?PendingAction {
		$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 );
	}

	/**
	 * List rows in a given status, oldest first.
	 *
	 * Oldest first because the queue is worked from the front: the action
	 * that has been held longest is the one closest to expiring.
	 *
	 * @param string            $status       Status filter.
	 * @param int               $limit        Max rows.
	 * @param int               $offset       Rows to skip.
	 * @param list<string>|null $capabilities Only rows these capabilities
	 *                                        decide on their own (AF-25.8);
	 *                                        null for every row.
	 * @return list<PendingAction>
	 */
	public function list_by_status( string $status, int $limit = 100, int $offset = 0, ?array $capabilities = null ): array {
		list( $where, $args ) = self::decidable( $capabilities );
		$rows                 = $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT * FROM {$this->table()} WHERE status = %s{$where} ORDER BY id ASC LIMIT %d OFFSET %d",
				array_merge( array( $status ), $args, array( max( 1, $limit ), max( 0, $offset ) ) )
			),
			ARRAY_A
		);

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

	/**
	 * Pending holds whose ledger row says they await AI review, with when
	 * they were held (AF-21.4, ADR-15 §2).
	 *
	 * Matched on the ledger result the pipeline writes, so holds waiting
	 * for a human cannot crowd these out of a sweep. The caller still reads
	 * the ledger row: a row that has since gained a verdict still matches.
	 *
	 * @param int $limit Max rows.
	 * @return list<array{pending: PendingAction, held_at: DateTimeImmutable}>
	 */
	public function list_awaiting_ai( int $limit ): array {
		$ledger = $this->wpdb->prefix . 'adaf_ledger';
		$rows   = $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT p.*, l.ts AS held_at FROM {$this->table()} p INNER JOIN {$ledger} l ON l.id = p.ledger_id WHERE p.status = 'pending' AND l.result_json LIKE %s ORDER BY p.id ASC LIMIT %d",
				'%' . $this->wpdb->esc_like( '"awaits_ai":true' ) . '%',
				max( 1, $limit )
			),
			ARRAY_A
		);

		$listed = array();
		foreach ( $rows ?? array() as $row ) {
			$listed[] = array(
				'pending' => $this->hydrate( $row ),
				'held_at' => new DateTimeImmutable( (string) $row['held_at'] ),
			);
		}

		return $listed;
	}

	/**
	 * Count rows in a given status.
	 *
	 * Filtered exactly as {@see self::list_by_status()} filters, so the menu
	 * bubble and the queue screen can never disagree about what is waiting.
	 *
	 * @param string            $status       Status filter.
	 * @param list<string>|null $capabilities As for list_by_status().
	 */
	public function count_by_status( string $status, ?array $capabilities = null ): int {
		list( $where, $args ) = self::decidable( $capabilities );

		return (int) $this->wpdb->get_var(
			$this->wpdb->prepare( "SELECT COUNT(*) FROM {$this->table()} WHERE status = %s{$where}", array_merge( array( $status ), $args ) )
		);
	}

	/**
	 * The WHERE clause keeping rows a user holding only these capabilities
	 * may decide: their capability is one of them, and manage_options is not
	 * needed as well. None keeps no row, and neither does a schema not yet
	 * upgraded.
	 *
	 * @param list<string>|null $capabilities Capabilities held; null for every row.
	 * @return array{0: string, 1: list<string>} Clause and its arguments.
	 */
	private static function decidable( ?array $capabilities ): array {
		if ( null === $capabilities ) {
			return array( '', array() );
		}
		// Before the DB_VERSION 12 columns exist nobody but an
		// administrator decides anything, and the filter could not run.
		if ( array() === $capabilities || (int) get_option( 'adaf_db_version', 0 ) < 12 ) {
			return array( ' AND 1 = 0', array() );
		}

		$placeholders = implode( ', ', array_fill( 0, count( $capabilities ), '%s' ) );

		return array( " AND requires_admin = 0 AND required_cap IN ({$placeholders})", array_values( array_map( 'strval', $capabilities ) ) );
	}

	/**
	 * Decisions taken on held actions since a moment, counted per outcome.
	 *
	 * @param DateTimeImmutable $since Inclusive start.
	 * @return array<string, int>
	 */
	public function decided_counts_since( DateTimeImmutable $since ): array {
		$rows = $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT status, COUNT(*) AS n FROM {$this->table()} WHERE decided_at IS NOT NULL AND decided_at >= %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;
	}

	/**
	 * When the longest-waiting held action was held, or null when none is.
	 *
	 * Read from the ledger row that recorded the hold: the pending row keeps
	 * only its expiry, and the TTL that expiry came from can since have
	 * changed.
	 */
	public function oldest_held_at(): ?DateTimeImmutable {
		$ledger = $this->wpdb->prefix . 'adaf_ledger';
		$ts     = $this->wpdb->get_var(
			"SELECT MIN(l.ts) FROM {$this->table()} p INNER JOIN {$ledger} l ON l.id = p.ledger_id WHERE p.status = 'pending'"
		);

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

	/**
	 * Claim a pending action for approval, before anything runs.
	 *
	 * The one atomic step that makes approval run an action at most once:
	 * only one caller's UPDATE matches `status = 'pending'`, so a second
	 * approver - a person, or the AI evaluator - finds it no longer pending.
	 * The claim time goes in decided_at, so a claim left by a request that
	 * died can be recovered. A hold past its expiry is never claimed, even
	 * before the sweep has marked it (ADR-8 §5).
	 *
	 * @param int               $id Pending row id.
	 * @param DateTimeImmutable $at Claim time.
	 */
	public function claim_for_approval( int $id, DateTimeImmutable $at ): bool {
		return 1 === $this->wpdb->query(
			(string) $this->wpdb->prepare(
				"UPDATE {$this->table()} SET status = 'approving', decided_at = %s WHERE id = %d AND status = 'pending' AND expires_at >= %s",
				$at->format( 'Y-m-d H:i:s' ),
				$id,
				$at->format( 'Y-m-d H:i:s' )
			)
		);
	}

	/**
	 * Settle a claimed approval to its outcome - also one the stale sweep
	 * closed as interrupted while it was still running, so a slow run that
	 * finishes is recorded as what it did. Nothing else can hold an
	 * interrupted claim: it is never requeued.
	 *
	 * @param int               $id         Pending row id.
	 * @param string            $status     approved, refused or drift_conflict.
	 * @param int|null          $decided_by Deciding WP user.
	 * @param DateTimeImmutable $decided_at Decision time.
	 */
	public function settle_approval( int $id, string $status, ?int $decided_by, DateTimeImmutable $decided_at ): bool {
		return 1 === $this->wpdb->query(
			(string) $this->wpdb->prepare(
				"UPDATE {$this->table()} SET status = %s, decided_by = %d, decided_at = %s, request_json = NULL WHERE id = %d AND status IN ('approving', 'interrupted')",
				$status,
				(int) $decided_by,
				$decided_at->format( 'Y-m-d H:i:s' ),
				$id
			)
		);
	}

	/**
	 * Return a claimed approval to the queue: nothing ran, or it failed in
	 * a way the approver may retry.
	 *
	 * @param int $id Pending row id.
	 */
	public function release_approval( int $id ): bool {
		return 1 === $this->wpdb->query(
			(string) $this->wpdb->prepare(
				"UPDATE {$this->table()} SET status = 'pending', decided_at = NULL WHERE id = %d AND status = 'approving'",
				$id
			)
		);
	}

	/**
	 * Close claims older than a moment as interrupted: the request holding
	 * them died mid-run, or is still running far longer than any approval
	 * should. Never requeued - nothing can tell whether the action ran, and
	 * a second run is the worse failure (AF-24.2).
	 *
	 * @param DateTimeImmutable $before Claims made before this are stale.
	 * @return list<int> Ledger ids of the holds closed.
	 */
	public function close_stale_approvals( DateTimeImmutable $before ): array {
		$cutoff = $before->format( 'Y-m-d H:i:s' );
		$rows   = $this->wpdb->get_results(
			(string) $this->wpdb->prepare(
				"SELECT id, ledger_id FROM {$this->table()} WHERE status = 'approving' AND decided_at < %s",
				$cutoff
			),
			ARRAY_A
		);

		$closed = array();
		foreach ( is_array( $rows ) ? $rows : array() as $row ) {
			$updated = $this->wpdb->query(
				(string) $this->wpdb->prepare(
					"UPDATE {$this->table()} SET status = 'interrupted', request_json = NULL WHERE id = %d AND status = 'approving' AND decided_at < %s",
					(int) $row['id'],
					$cutoff
				)
			);
			if ( 1 === $updated ) {
				$closed[] = (int) $row['ledger_id'];
			}
		}

		return $closed;
	}

	/**
	 * Close one claimed approval as interrupted: its run crashed, so it may
	 * have taken effect (AF-24.2).
	 *
	 * @param int $id Pending row id.
	 */
	public function interrupt_approval( int $id ): bool {
		return 1 === $this->wpdb->query(
			(string) $this->wpdb->prepare(
				"UPDATE {$this->table()} SET status = 'interrupted', request_json = NULL WHERE id = %d AND status = 'approving'",
				$id
			)
		);
	}

	/**
	 * Transition a still-pending action to a decided status.
	 *
	 * Guarded by `status = 'pending'` in the WHERE clause so concurrent
	 * decisions cannot double-apply. Clears the replay envelope: once
	 * decided, nothing can re-send the request, and it is an unredacted
	 * copy of what the agent sent (AF-18.2).
	 *
	 * @param int               $id         Row id.
	 * @param string            $status     approved|rejected|expired|drift_conflict|refused.
	 * @param int|null          $decided_by Deciding WP user.
	 * @param DateTimeImmutable $decided_at Decision timestamp.
	 * @return bool Whether a row transitioned.
	 */
	public function transition( int $id, string $status, ?int $decided_by, DateTimeImmutable $decided_at ): bool {
		$updated = $this->wpdb->query(
			(string) $this->wpdb->prepare(
				"UPDATE {$this->table()} SET status = %s, decided_by = %d, decided_at = %s, request_json = NULL WHERE id = %d AND status = 'pending'",
				$status,
				(int) $decided_by,
				$decided_at->format( 'Y-m-d H:i:s' ),
				$id
			)
		);

		return 1 === $updated;
	}

	/**
	 * Overdue pending rows, oldest first.
	 *
	 * @param DateTimeImmutable $now   Reference time.
	 * @param int               $limit Max rows.
	 * @return list<PendingAction>
	 */
	public function list_overdue( DateTimeImmutable $now, int $limit = 200 ): array {
		$rows = $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT * FROM {$this->table()} WHERE status = 'pending' AND expires_at < %s ORDER BY id ASC LIMIT %d",
				$now->format( 'Y-m-d H:i:s' ),
				$limit
			),
			ARRAY_A
		);

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

	/**
	 * Expire all overdue pending rows, clearing their replay envelopes as a
	 * decision does.
	 *
	 * @param DateTimeImmutable $now Reference time.
	 * @return int Number of rows expired.
	 */
	public function expire_overdue( DateTimeImmutable $now ): int {
		$updated = $this->wpdb->query(
			(string) $this->wpdb->prepare(
				"UPDATE {$this->table()} SET status = 'expired', decided_at = %s, request_json = NULL WHERE status = 'pending' AND expires_at < %s",
				$now->format( 'Y-m-d H:i:s' ),
				$now->format( 'Y-m-d H:i:s' )
			)
		);

		return is_int( $updated ) ? $updated : 0;
	}

	/**
	 * Map a database row to the value object.
	 *
	 * @param array<string, string|null> $row Raw row.
	 */
	private function hydrate( array $row ): PendingAction {
		$input   = json_decode( (string) $row['stored_input'], true );
		$request = isset( $row['request_json'] ) ? json_decode( (string) $row['request_json'], true ) : null;

		return new PendingAction(
			(int) $row['id'],
			(int) $row['ledger_id'],
			(int) $row['agent_id'],
			(string) $row['ability_name'],
			is_array( $input ) ? $input : array(),
			null === $row['initial_entity_modified_at'] ? null : new DateTimeImmutable( (string) $row['initial_entity_modified_at'] ),
			new DateTimeImmutable( (string) $row['expires_at'] ),
			(string) $row['status'],
			null === $row['decided_by'] || '0' === $row['decided_by'] ? null : (int) $row['decided_by'],
			null === $row['decided_at'] ? null : new DateTimeImmutable( (string) $row['decided_at'] ),
			is_array( $request ) ? $request : null,
			isset( $row['initial_fingerprint'] ) ? (string) $row['initial_fingerprint'] : null,
			// Coalesced to the strictest answer: a row read part-way through
			// the DB_VERSION 12 upgrade needs an administrator.
			isset( $row['required_cap'] ) && '' !== $row['required_cap'] ? (string) $row['required_cap'] : 'manage_options',
			isset( $row['requires_admin'] ) && '1' === (string) $row['requires_admin']
		);
	}
}