<?php
/**
 * Query and mutation boundary for the owner-only WordPress comment board.
 *
 * This class intentionally exposes summaries rather than raw guest sessions,
 * share tokens, or encrypted storage paths.  The admin screen can therefore
 * provide a useful review overview without becoming an alternate data-export
 * channel.
 *
 * @package CommentMate
 */

declare(strict_types=1);

namespace CommentMate;

defined('ABSPATH') || exit;

final class Admin_Board {
	/**
	 * Returns the current active/paused review plus one compact row per visible
	 * comment.  Deleted comments and deleted assets are deliberately omitted.
	 *
	 * @return array{review:array<string,mixed>|null,comments:list<array<string,mixed>>}
	 */
	public function current(): array {
		$review = (new Review_Service())->latest();
		if (!is_array($review)) {
			return array('review' => null, 'comments' => array());
		}

		return array(
			'review'   => $review,
			'comments' => $this->comments((int) $review['id']),
		);
	}

	/**
	 * @return list<array<string,mixed>>
	 */
	public function comments(int $review_id): array {
		if ($review_id < 1) {
			return array();
		}

		global $wpdb;
		$comments = Database::table('commentmate_comments');
		$pages = Database::table('commentmate_pages');
		$messages = Database::table('commentmate_comment_messages');
		$attachments = Database::table('commentmate_attachments');
		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Live private review board data comes from plugin tables; fresh reads preserve visible deletions and review locks.
			$wpdb->prepare('SELECT c.id, c.review_id, c.comment_number, c.page_id, c.status, c.author_name, c.anchor_json, c.body_html, c.screenshot_state,
					c.created_at, c.updated_at, c.resolved_at, p.canonical_url, p.page_title,
					COUNT(DISTINCT m.id) AS message_count,
					COUNT(DISTINCT CASE WHEN a.message_id IS NULL OR am.id IS NOT NULL THEN a.id END) AS attachment_count
				FROM %i AS c
				INNER JOIN %i AS p ON p.id = c.page_id
				LEFT JOIN %i AS m ON m.comment_id = c.id AND m.deleted_at IS NULL
				LEFT JOIN %i AS a ON a.comment_id = c.id AND a.deleted_at IS NULL
				LEFT JOIN %i AS am ON am.id = a.message_id AND am.comment_id = c.id AND am.deleted_at IS NULL
				WHERE c.review_id = %d AND c.deleted_at IS NULL
				GROUP BY c.id, c.review_id, c.comment_number, c.page_id, c.status, c.author_name, c.anchor_json, c.body_html, c.screenshot_state,
					c.created_at, c.updated_at, c.resolved_at, p.canonical_url, p.page_title
				ORDER BY CASE WHEN c.status = \'open\' THEN 0 ELSE 1 END, c.updated_at DESC, c.id DESC
				LIMIT 1000', $comments, $pages, $messages, $attachments, $messages, $review_id),
			ARRAY_A
		);

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

		$ids = array_values(array_filter(array_map(static fn (array $row): int => (int) ($row['id'] ?? 0), $rows)));
		$assets = $this->assets_for_comments($review_id, $ids);
		$messages_by_comment = $this->messages_for_comments($review_id, $ids);
		$output = array();
		foreach ($rows as $row) {
			$comment_id = (int) $row['id'];
			$output[] = $this->output($row, $assets[$comment_id] ?? array(), $messages_by_comment[$comment_id] ?? array());
		}

		return $output;
	}

	/**
	 * Returns one complete, frontend-safe comment after an owner mutation.  The
	 * classic WordPress board only needs summaries, but the owner review surface
	 * needs the anchor, thread, and assets to replace its optimistic UI state
	 * without losing data.
	 *
	 * @return array<string,mixed>|null
	 */
	public function comment(int $review_id, int $comment_id): ?array {
		if ($review_id < 1 || $comment_id < 1) {
			return null;
		}

		global $wpdb;
		$comments = Database::table('commentmate_comments');
		$pages = Database::table('commentmate_pages');
		$messages = Database::table('commentmate_comment_messages');
		$attachments = Database::table('commentmate_attachments');
		$row = $wpdb->get_row( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Live private review board data comes from plugin tables; fresh reads preserve visible deletions and review locks.
			$wpdb->prepare('SELECT c.id, c.review_id, c.comment_number, c.page_id, c.status, c.author_name, c.anchor_json, c.body_html, c.screenshot_state,
					c.created_at, c.updated_at, c.resolved_at, p.canonical_url, p.page_title,
					COUNT(DISTINCT m.id) AS message_count,
					COUNT(DISTINCT CASE WHEN a.message_id IS NULL OR am.id IS NOT NULL THEN a.id END) AS attachment_count
				FROM %i AS c
				INNER JOIN %i AS p ON p.id = c.page_id
				LEFT JOIN %i AS m ON m.comment_id = c.id AND m.deleted_at IS NULL
				LEFT JOIN %i AS a ON a.comment_id = c.id AND a.deleted_at IS NULL
				LEFT JOIN %i AS am ON am.id = a.message_id AND am.comment_id = c.id AND am.deleted_at IS NULL
				WHERE c.review_id = %d AND c.id = %d AND c.deleted_at IS NULL
				GROUP BY c.id, c.review_id, c.comment_number, c.page_id, c.status, c.author_name, c.anchor_json, c.body_html, c.screenshot_state,
					c.created_at, c.updated_at, c.resolved_at, p.canonical_url, p.page_title
				LIMIT 1', $comments, $pages, $messages, $attachments, $messages, $review_id, $comment_id),
			ARRAY_A
		);
		if (!is_array($row)) {
			return null;
		}

		$assets = $this->assets_for_comments($review_id, array($comment_id));
		$messages_by_comment = $this->messages_for_comments($review_id, array($comment_id));
		return $this->output($row, $assets[$comment_id] ?? array(), $messages_by_comment[$comment_id] ?? array());
	}

	/**
	 * Owner-only status change used by the non-JavaScript board controls.
	 *
	 * @return array<string,mixed>|\WP_Error
	 */
	public function set_status(int $review_id, int $comment_id, string $status, int $owner_user_id): array|\WP_Error {
		if ($review_id < 1 || $comment_id < 1 || !in_array($status, array('open', 'resolved'), true)) {
			return Security::error('invalid_comment_status', __('A valid comment and status are required.', 'commentmate'), 400);
		}

		global $wpdb;
		$comments = Database::table('commentmate_comments');
		$reviews = Database::table('commentmate_reviews');
		if (false === $wpdb->query('START TRANSACTION')) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Explicit transaction boundary for the surrounding plugin-table operation; caching cannot provide atomicity or row locks.
			return Security::error('comment_not_saved', __('The comment status could not be updated.', 'commentmate'), 503);
		}
		$review = $wpdb->get_row( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Live private review board data comes from plugin tables; fresh reads preserve visible deletions and review locks.
			$wpdb->prepare('SELECT id, cleanup_locked FROM %i WHERE id = %d FOR UPDATE', $reviews, $review_id),
			ARRAY_A
		);
		if (!is_array($review)) {
			$wpdb->query('ROLLBACK'); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Explicit transaction boundary for the surrounding plugin-table operation; caching cannot provide atomicity or row locks.
			return Security::error('review_not_found', __('That review is no longer available.', 'commentmate'), 404);
		}
		if ((int) $review['cleanup_locked'] !== 0) {
			$wpdb->query('ROLLBACK'); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Explicit transaction boundary for the surrounding plugin-table operation; caching cannot provide atomicity or row locks.
			return Security::error('review_cleanup_locked', __('This review is locked for verified cleanup.', 'commentmate'), 409);
		}
		$updated = $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Live private review board data comes from plugin tables; fresh reads preserve visible deletions and review locks.
			$comments,
			array(
				'status'                 => $status,
				'updated_at'             => current_time('mysql', true),
				'resolved_at'            => 'resolved' === $status ? current_time('mysql', true) : null,
				'resolved_by_session_id' => null,
			),
			array(
				'id'        => $comment_id,
				'review_id' => $review_id,
				'deleted_at' => null,
			),
			array('%s', '%s', '%s', '%d'),
			array('%d', '%d', '%s')
		);
		if (false === $updated) {
			$wpdb->query('ROLLBACK'); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Explicit transaction boundary for the surrounding plugin-table operation; caching cannot provide atomicity or row locks.
			return Security::error('comment_not_saved', __('The comment status could not be updated.', 'commentmate'), 503);
		}
		if (0 === $updated) {
			// A retry can legitimately produce no changed columns when it lands in
			// the same second as the original status update. Distinguish that from a
			// concurrently deleted comment so the owner endpoint remains idempotent.
			$exists = $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Live private review board data comes from plugin tables; fresh reads preserve visible deletions and review locks.
				$wpdb->prepare('SELECT id FROM %i WHERE id = %d AND review_id = %d AND deleted_at IS NULL LIMIT 1', $comments, $comment_id, $review_id)
			);
			if (null === $exists) {
				$wpdb->query('ROLLBACK'); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Explicit transaction boundary for the surrounding plugin-table operation; caching cannot provide atomicity or row locks.
				return Security::error('comment_not_found', __('That comment is no longer available.', 'commentmate'), 404);
			}
		}

		if (false === $wpdb->query('COMMIT')) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Explicit transaction boundary for the surrounding plugin-table operation; caching cannot provide atomicity or row locks.
			$wpdb->query('ROLLBACK'); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Explicit transaction boundary for the surrounding plugin-table operation; caching cannot provide atomicity or row locks.
			return Security::error('comment_not_saved', __('The comment status could not be updated.', 'commentmate'), 503);
		}

		$payload = $this->comment($review_id, $comment_id);
		if (!is_array($payload)) {
			return Security::error('comment_not_found', __('That comment is no longer available.', 'commentmate'), 404);
		}
		Audit::record($review_id, 'admin', $owner_user_id, 'comment_status_changed', array('comment_id' => $comment_id, 'status' => $status));
		return $payload;
	}

	/**
	 * Retained boundary for old REST/admin-post callers. Reviewer text is never
	 * rewritten by the owner; author-only guest editing uses Comment_Service.
	 *
	 * @return array<string,mixed>|\WP_Error
	 */
	public function update_body(int $review_id, int $comment_id, string $body, int $owner_user_id): array|\WP_Error {
		return Security::error('comment_not_owned', __('Only the comment creator may edit it. Administrators can copy, resolve or delete feedback.', 'commentmate'), 403);
	}

	/**
	 * Soft-deletes a whole comment thread and retires associated private asset
	 * rows. Promoted Media Library records retain their disposition, so cleanup
	 * preserves the approved WordPress Media files.
	 *
	 * @return true|\WP_Error
	 */
	public function delete_comment(int $review_id, int $comment_id, int $owner_user_id): bool|\WP_Error {
		$transaction = $this->lock_review_for_comment_mutation($review_id);
		if (is_wp_error($transaction)) {
			return $transaction;
		}

		global $wpdb;
		$comments = Database::table('commentmate_comments');
		$messages = Database::table('commentmate_comment_messages');
		$attachments = Database::table('commentmate_attachments');
		$now = current_time('mysql', true);
		$deleted = $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Live private review board data comes from plugin tables; fresh reads preserve visible deletions and review locks.
			$comments,
			array('deleted_at' => $now, 'updated_at' => $now),
			array('id' => $comment_id, 'review_id' => $review_id, 'deleted_at' => null),
			array('%s', '%s'),
			array('%d', '%d', '%s')
		);
		if (false === $deleted || 0 === $deleted) {
			$wpdb->query('ROLLBACK'); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Explicit transaction boundary for the surrounding plugin-table operation; caching cannot provide atomicity or row locks.
			return Security::error('comment_not_found', __('That comment is no longer available.', 'commentmate'), 404);
		}
		$queries = array(
			$wpdb->prepare('UPDATE %i SET deleted_at = %s WHERE comment_id = %d AND deleted_at IS NULL', $messages, $now, $comment_id),
			$wpdb->prepare('UPDATE %i
				SET disposition = CASE WHEN disposition = \'promoted\' THEN \'promoted\' ELSE \'deleted\' END, deleted_at = %s
				WHERE review_id = %d AND comment_id = %d AND deleted_at IS NULL', $attachments, $now, $review_id, $comment_id),
		);
		foreach ($queries as $query) {
			if (false === $wpdb->query($query)) { // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Every element in this local query array is a prepared statement; integer ID lists generate only %d placeholders, never SQL values. Live private review board data comes from plugin tables; fresh reads preserve visible deletions and review locks.
				$wpdb->query('ROLLBACK'); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Explicit transaction boundary for the surrounding plugin-table operation; caching cannot provide atomicity or row locks.
				return Security::error('comment_not_saved', __('The comment could not be deleted.', 'commentmate'), 503);
			}
		}
		if (false === $wpdb->query('COMMIT')) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Explicit transaction boundary for the surrounding plugin-table operation; caching cannot provide atomicity or row locks.
			$wpdb->query('ROLLBACK'); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Explicit transaction boundary for the surrounding plugin-table operation; caching cannot provide atomicity or row locks.
			return Security::error('comment_not_saved', __('The comment could not be deleted.', 'commentmate'), 503);
		}

		Audit::record($review_id, 'admin', $owner_user_id, 'comment_deleted', array('comment_id' => $comment_id));
		return true;
	}

	/**
	 * Soft-deletes all visible comment threads in a review, or only the resolved
	 * threads, in one review-locked transaction. Promoted Media Library files
	 * remain untouched just as they do for individual comment deletion.
	 *
	 * @return int|\WP_Error Number of comment threads removed.
	 */
	public function delete_comments(int $review_id, string $scope, int $owner_user_id): int|\WP_Error {
		if (!in_array($scope, array('all', 'resolved'), true)) {
			return Security::error('invalid_comment_delete_scope', __('Choose the comments to remove and try again.', 'commentmate'), 400);
		}

		$transaction = $this->lock_review_for_comment_mutation($review_id);
		if (is_wp_error($transaction)) {
			return $transaction;
		}

		global $wpdb;
		$comments = Database::table('commentmate_comments');
		$messages = Database::table('commentmate_comment_messages');
		$attachments = Database::table('commentmate_attachments');
		$comment_ids = $wpdb->get_col( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Live private review board data comes from plugin tables; fresh reads preserve visible deletions and review locks.
			$wpdb->prepare(
				"SELECT id FROM %i WHERE review_id = %d AND deleted_at IS NULL AND (%s = 'all' OR status = 'resolved') FOR UPDATE",
				$comments, $review_id, $scope
			)
		);
		$comment_ids = array_values(array_filter(array_map('absint', is_array($comment_ids) ? $comment_ids : array())));
		if (empty($comment_ids)) {
			if (false === $wpdb->query('COMMIT')) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Explicit transaction boundary for the surrounding plugin-table operation; caching cannot provide atomicity or row locks.
				$wpdb->query('ROLLBACK'); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Explicit transaction boundary for the surrounding plugin-table operation; caching cannot provide atomicity or row locks.
				return Security::error('comment_not_saved', __('The comments could not be deleted.', 'commentmate'), 503);
			}
			return 0;
		}

		$placeholders = implode(',', array_fill(0, count($comment_ids), '%d'));
		$now = current_time('mysql', true);
		$comment_query = $wpdb->prepare( // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- wpdb accepts one argument array; the fixed identifier/value prefix is followed by exactly one bound integer per generated %d placeholder.
			"UPDATE %i SET deleted_at = %s, updated_at = %s WHERE review_id = %d AND id IN ({$placeholders}) AND deleted_at IS NULL", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- This fragment contains only count-matched literal %d placeholders; every ID and the table identifier are bound by prepare, never interpolated.
			array_merge(array($comments, $now, $now, $review_id), $comment_ids)
		);
		$message_query = $wpdb->prepare( // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- wpdb accepts one argument array; the fixed identifier/value prefix is followed by exactly one bound integer per generated %d placeholder.
			"UPDATE %i SET deleted_at = %s WHERE comment_id IN ({$placeholders}) AND deleted_at IS NULL", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- This fragment contains only count-matched literal %d placeholders; every ID and the table identifier are bound by prepare, never interpolated.
			array_merge(array($messages, $now), $comment_ids)
		);
		$attachment_query = $wpdb->prepare( // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- wpdb accepts one argument array; the fixed identifier/value prefix is followed by exactly one bound integer per generated %d placeholder.
			"UPDATE %i SET disposition = CASE WHEN disposition = 'promoted' THEN 'promoted' ELSE 'deleted' END, deleted_at = %s WHERE review_id = %d AND comment_id IN ({$placeholders}) AND deleted_at IS NULL", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- This fragment contains only count-matched literal %d placeholders; every ID and the table identifier are bound by prepare, never interpolated.
			array_merge(array($attachments, $now, $review_id), $comment_ids)
		);
		foreach (array($comment_query, $message_query, $attachment_query) as $query) {
			if (false === $wpdb->query($query)) { // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Every element in this local query array is a prepared statement; integer ID lists generate only %d placeholders, never SQL values. Live private review board data comes from plugin tables; fresh reads preserve visible deletions and review locks.
				$wpdb->query('ROLLBACK'); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Explicit transaction boundary for the surrounding plugin-table operation; caching cannot provide atomicity or row locks.
				return Security::error('comment_not_saved', __('The comments could not be deleted.', 'commentmate'), 503);
			}
		}
		if (false === $wpdb->query('COMMIT')) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Explicit transaction boundary for the surrounding plugin-table operation; caching cannot provide atomicity or row locks.
			$wpdb->query('ROLLBACK'); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Explicit transaction boundary for the surrounding plugin-table operation; caching cannot provide atomicity or row locks.
			return Security::error('comment_not_saved', __('The comments could not be deleted.', 'commentmate'), 503);
		}

		$deleted_count = count($comment_ids);
		Audit::record($review_id, 'admin', $owner_user_id, 'comments_deleted', array('scope' => $scope, 'count' => $deleted_count));
		return $deleted_count;
	}

	/**
	 * The link intentionally contains no secret.  A logged-in owner can use it
	 * to navigate to the page while the normal secure review session continues
	 * to govern access to comment data.
	 */
	public static function context_url(string $canonical_url, int $comment_id, int $comment_number = 0): string {
		return add_query_arg(
			array(
				'review'     => 'true',
				'commentmate-focus' => max(1, $comment_number > 0 ? $comment_number : $comment_id),
				'commentmate-focus-kind' => 'number',
			),
			$canonical_url
		);
	}

	/**
	 * @param array<string,mixed> $row
	 * @param list<array<string,mixed>> $assets
	 * @param list<array<string,mixed>> $messages
	 * @return array<string,mixed>
	 */
	private function output(array $row, array $assets = array(), array $messages = array()): array {
		$body = wp_strip_all_tags((string) $row['body_html']);
		$anchor = json_decode((string) ($row['anchor_json'] ?? ''), true);
		return array(
			'id'              => (int) $row['id'],
			'commentNumber'   => (int) ($row['comment_number'] ?? $row['id']),
			'reviewId'        => (int) $row['review_id'],
			'pageId'          => (int) ($row['page_id'] ?? 0),
			'status'          => (string) $row['status'],
			'authorName'      => (string) $row['author_name'],
			'body'            => $body,
			'bodyHtml'        => (string) $row['body_html'],
			'anchor'          => is_array($anchor) ? $anchor : array(),
			'screenshotState' => (string) $row['screenshot_state'],
			'createdAt'       => (string) $row['created_at'],
			'updatedAt'       => (string) $row['updated_at'],
			'resolvedAt'      => $row['resolved_at'] ? (string) $row['resolved_at'] : null,
			'url'             => (string) $row['canonical_url'],
			'pageTitle'       => (string) $row['page_title'],
			'messageCount'    => (int) $row['message_count'],
			'attachmentCount' => (int) $row['attachment_count'],
			'assets'          => $assets,
			'messages'        => $messages,
			'contextUrl'      => self::context_url((string) $row['canonical_url'], (int) $row['id'], (int) ($row['comment_number'] ?? 0)),
		);
	}

	/**
	 * Returns only board-safe asset metadata. It never returns a storage key or
	 * checksum. Private review files keep their authenticated routes; a verified
	 * promoted Media Library image may include its already-public media URL.
	 *
	 * @param list<int> $comment_ids
	 * @return array<int,list<array<string,mixed>>>
	 */
	private function assets_for_comments(int $review_id, array $comment_ids): array {
		if ($review_id < 1 || empty($comment_ids)) {
			return array();
		}
		$ids = array_values(array_unique(array_filter(array_map('absint', $comment_ids))));
		if (empty($ids)) {
			return array();
		}

		global $wpdb;
		$attachments = Database::table('commentmate_attachments');
		$messages = Database::table('commentmate_comment_messages');
		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Live private review board data comes from plugin tables; fresh reads preserve visible deletions and review locks.
			$wpdb->prepare(
				"SELECT a.id, a.comment_id, a.message_id, a.mime_type, a.original_name, a.byte_size,
					a.disposition, a.media_attachment_id, a.created_at
				FROM %i AS a
				LEFT JOIN %i AS m ON m.id = a.message_id AND m.comment_id = a.comment_id AND m.deleted_at IS NULL
				WHERE a.review_id = %d AND a.comment_id IN (" . implode(',', array_fill(0, count($ids), '%d')) . ")
					AND a.deleted_at IS NULL AND (a.message_id IS NULL OR m.id IS NOT NULL)
				ORDER BY a.comment_id ASC, a.id ASC",
				array_merge(array($attachments, $messages, $review_id), $ids)
			),
			ARRAY_A
		);
		$output = array();
		foreach (is_array($rows) ? $rows : array() as $row) {
			$comment_id = (int) $row['comment_id'];
			$output[$comment_id] ??= array();
			$output[$comment_id][] = array(
				'id'                => (int) $row['id'],
				'commentId'         => $comment_id,
				'messageId'         => null === $row['message_id'] ? null : (int) $row['message_id'],
				'mimeType'          => (string) $row['mime_type'],
				'originalName'      => (string) $row['original_name'],
				'byteSize'          => (int) $row['byte_size'],
				'disposition'       => (string) $row['disposition'],
				'mediaAttachmentId' => null === $row['media_attachment_id'] ? null : (int) $row['media_attachment_id'],
				'mediaUrl'          => Asset_Service::promoted_media_url($row),
				'createdAt'         => (string) $row['created_at'],
				'kind'              => 0 === strpos((string) $row['original_name'], 'commentmate-screenshot-') ? 'screenshot' : 'attachment',
			);
		}

		return $output;
	}

	/**
	 * @return true|\WP_Error
	 */
	private function lock_review_for_comment_mutation(int $review_id): bool|\WP_Error {
		if ($review_id < 1) {
			return Security::error('review_not_found', __('That review is no longer available.', 'commentmate'), 404);
		}
		global $wpdb;
		$reviews = Database::table('commentmate_reviews');
		if (false === $wpdb->query('START TRANSACTION')) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Explicit transaction boundary for the surrounding plugin-table operation; caching cannot provide atomicity or row locks.
			return Security::error('comment_not_saved', __('The comment could not be updated.', 'commentmate'), 503);
		}
		$review = $wpdb->get_row($wpdb->prepare('SELECT id, cleanup_locked FROM %i WHERE id = %d FOR UPDATE', $reviews, $review_id), ARRAY_A); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Live private review board data comes from plugin tables; fresh reads preserve visible deletions and review locks.
		if (!is_array($review)) {
			$wpdb->query('ROLLBACK'); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Explicit transaction boundary for the surrounding plugin-table operation; caching cannot provide atomicity or row locks.
			return Security::error('review_not_found', __('That review is no longer available.', 'commentmate'), 404);
		}
		if ((int) $review['cleanup_locked'] !== 0) {
			$wpdb->query('ROLLBACK'); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Explicit transaction boundary for the surrounding plugin-table operation; caching cannot provide atomicity or row locks.
			return Security::error('review_cleanup_locked', __('This review is locked for verified cleanup.', 'commentmate'), 409);
		}

		return true;
	}

	/**
	 * @return string|\WP_Error
	 */
	private function sanitise_body(string $body): string|\WP_Error {
		$body = trim($body);
		if ('' === $body || strlen($body) > 20000) {
			return Security::error('invalid_comment', __('Comment text must be between 1 and 20,000 bytes.', 'commentmate'), 400);
		}
		$allowed = array(
			'p' => array(), 'br' => array(), 'strong' => array(), 'b' => array(), 'em' => array(), 'i' => array(),
			'a' => array('href' => true, 'title' => true), 'ul' => array(), 'ol' => array(), 'li' => array(), 'code' => array(),
		);
		$sanitised = trim(wp_kses($body, $allowed));
		if ('' === trim(wp_strip_all_tags($sanitised))) {
			return Security::error('invalid_comment', __('Comment text is required.', 'commentmate'), 400);
		}

		return $sanitised;
	}

	/**
	 * @param list<int> $comment_ids
	 * @return array<int,list<array<string,mixed>>>
	 */
	private function messages_for_comments(int $review_id, array $comment_ids): array {
		if ($review_id < 1 || empty($comment_ids)) {
			return array();
		}
		$ids = array_values(array_unique(array_filter(array_map('absint', $comment_ids))));
		if (empty($ids)) {
			return array();
		}

		global $wpdb;
		$messages = Database::table('commentmate_comment_messages');
		$comments = Database::table('commentmate_comments');
		$sessions = Database::table('commentmate_guest_sessions');
		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Live private review board data comes from plugin tables; fresh reads preserve visible deletions and review locks.
			$wpdb->prepare(
				"SELECT m.id, m.comment_id, m.body_html, m.created_at, m.edited_at,
					COALESCE(s.guest_name, c.author_name, 'Guest') AS author_name
				FROM %i AS m
				INNER JOIN %i AS c ON c.id = m.comment_id AND c.deleted_at IS NULL
				LEFT JOIN %i AS s ON s.id = m.author_session_id
				WHERE c.review_id = %d AND m.comment_id IN (" . implode(',', array_fill(0, count($ids), '%d')) . ') AND m.deleted_at IS NULL
				ORDER BY m.comment_id ASC, m.created_at ASC, m.id ASC',
				array_merge(array($messages, $comments, $sessions, $review_id), $ids)
			),
			ARRAY_A
		);
		$output = array();
		foreach (is_array($rows) ? $rows : array() as $row) {
			$comment_id = (int) $row['comment_id'];
			$output[$comment_id] ??= array();
			$output[$comment_id][] = array(
				'id'        => (int) $row['id'],
				'authorName' => (string) $row['author_name'],
				'body'      => wp_strip_all_tags((string) $row['body_html']),
				'bodyHtml'  => (string) $row['body_html'],
				'createdAt' => (string) $row['created_at'],
				'editedAt'  => $row['edited_at'] ? (string) $row['edited_at'] : null,
			);
		}

		return $output;
	}
}