<?php
/**
 * Submission repository.
 *
 * @package FormPilot
 * @since   1.0.0
 */

declare( strict_types = 1 );

namespace FormPilot\Database\Repositories;

use FormPilot\Database\AbstractRepository;
use FormPilot\Database\QueryBuilder;
use FormPilot\Database\Schema;
use FormPilot\Fields\FieldTypes;
use FormPilot\Models\Submission;
use FormPilot\Uploads\UploadManager;
use wpdb;

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * Stores and reads submissions and the values attached to them.
 *
 * @since 1.0.0
 */
final class SubmissionRepository extends AbstractRepository {

	/**
	 * Columns that may be sorted on.
	 */
	private const SORTABLE = array( 'id', 'form_id', 'status', 'created_at' );

	/**
	 * Constructor.
	 *
	 * @param wpdb|null           $db    Optional. Connection to use. Defaults to the global one.
	 * @param FormRepository|null $forms Optional. Repository owning the submission counter.
	 */
	public function __construct( ?wpdb $db = null, private ?FormRepository $forms = null ) {
		parent::__construct( $db );
	}

	/**
	 * Returns the submissions table.
	 */
	protected function table(): string {
		return Schema::table( Schema::SUBMISSIONS );
	}

	/**
	 * Returns the writable columns and their formats.
	 *
	 * @return array<string, string> Formats keyed by column name.
	 */
	protected function formats(): array {
		return array(
			'form_id'    => '%d',
			'user_id'    => '%d',
			'status'     => '%s',
			'entry_key'  => '%s',
			'ip_address' => '%s',
			'user_agent' => '%s',
			'referer'    => '%s',
			'post_id'    => '%d',
			'created_at' => '%s',
			'updated_at' => '%s',
		);
	}

	/**
	 * Stores a submission and its values.
	 *
	 * @param int                                                         $form_id Form the submission belongs to.
	 * @param array<int, array{field_id: int, key: string, value: mixed}> $entries Submitted values.
	 * @param array<string, mixed>                                        $context {
	 *     Optional. Request metadata.
	 *
	 *     @type int    $user_id    Logged in user, 0 for visitors.
	 *     @type string $ip_address Client address.
	 *     @type string $user_agent Client user agent.
	 *     @type string $referer    Page the form was submitted from.
	 *     @type int    $post_id    Post holding the form.
	 * }
	 *
	 * @return int The new submission ID, or 0 on failure.
	 */
	public function create( int $form_id, array $entries, array $context = array() ): int {
		if ( $form_id <= 0 ) {
			return 0;
		}

		$now = $this->now();

		$columns = array(
			'form_id'    => $form_id,
			'user_id'    => (int) ( $context['user_id'] ?? 0 ),
			'status'     => Submission::STATUS_UNREAD,
			'entry_key'  => $this->generate_entry_key(),
			'ip_address' => mb_substr( (string) ( $context['ip_address'] ?? '' ), 0, 100 ),
			'user_agent' => mb_substr( (string) ( $context['user_agent'] ?? '' ), 0, 255 ),
			'referer'    => mb_substr( (string) ( $context['referer'] ?? '' ), 0, 255 ),
			'post_id'    => (int) ( $context['post_id'] ?? 0 ),
			'created_at' => $now,
			'updated_at' => $now,
		);

		/*
		 * The entry row and its values commit together. Written separately, a meta
		 * insert refused by the server — an oversized value, a deadlock, a full
		 * disk — used to leave a visibly successful submission whose answers were
		 * never stored, while the notification, the webhook and every integration
		 * still received the complete set.
		 */
		$submission_id = $this->transaction(
			function () use ( $columns, $entries ): int|false {
				$submission_id = $this->insert_row( $columns );

				if ( 0 === $submission_id ) {
					return false;
				}

				foreach ( $entries as $entry ) {
					$key = (string) ( $entry['key'] ?? '' );

					// A keyless entry has no column to live in and never had one.
					if ( '' === $key ) {
						continue;
					}

					if ( ! $this->add_meta( $submission_id, (int) ( $entry['field_id'] ?? 0 ), $key, $entry['value'] ?? '' ) ) {
						return false;
					}
				}

				return $submission_id;
			}
		);

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

		/**
		 * Fires after a submission and its values have been stored.
		 *
		 * Notifications and integrations attach here.
		 *
		 * @since 1.0.0
		 *
		 * @param int                                                         $submission_id New submission ID.
		 * @param int                                                         $form_id       Form the submission belongs to.
		 * @param array<int, array{field_id: int, key: string, value: mixed}> $entries       Stored values.
		 */
		do_action( 'formpilot_submission_created', $submission_id, $form_id, $entries );

		return $submission_id;
	}

	/**
	 * Stores a single value of a submission.
	 *
	 * Arrays and file payloads are JSON encoded, so a checkbox group and an
	 * upload both round trip through one column.
	 *
	 * @param int    $submission_id Submission the value belongs to.
	 * @param int    $field_id      Field the value came from.
	 * @param string $key           Field key, used as the meta key.
	 * @param mixed  $value         Value to store.
	 *
	 * @return bool True when the row was written.
	 */
	public function add_meta( int $submission_id, int $field_id, string $key, mixed $value ): bool {
		if ( $submission_id <= 0 || '' === $key ) {
			return false;
		}

		$stored = is_scalar( $value ) ? (string) $value : (string) wp_json_encode( $value );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Repository write.
		$inserted = $this->db->insert(
			Schema::table( Schema::SUBMISSION_META ),
			array(
				'submission_id' => $submission_id,
				'field_id'      => $field_id,
				'meta_key'      => $key, // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key -- A column of this plugin's own table in an insert, not a meta query.
				'meta_value'    => $stored, // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_value -- As above.
			),
			array( '%d', '%d', '%s', '%s' )
		);

		if ( false === $inserted ) {
			$this->report_error( 'insert' );

			return false;
		}

		return true;
	}

	/**
	 * Returns a page of submissions.
	 *
	 * @param array<string, mixed> $args {
	 *     Optional. Query arguments.
	 *
	 *     @type string $search   Term matched against the stored values and the entry key.
	 *     @type int    $form     Restrict to a single form.
	 *     @type string $status   Restrict to a single status.
	 *     @type string $orderby  Column to sort on. Default 'created_at'.
	 *     @type string $order    Sort direction. Default 'DESC'.
	 *     @type int    $per_page Rows per page. Default 20.
	 *     @type int    $page     Page number, one based. Default 1.
	 *     @type int    $after_id Return only rows past this ID. Default 0.
	 *     @type string $date_from Earliest submission date, `Y-m-d` in site time.
	 *     @type string $date_to   Latest submission date, `Y-m-d` in site time, inclusive.
	 *     @type int    $user      User who submitted. Use -1 for guests. Default 0, meaning any.
	 * }
	 *
	 * @return array<int, Submission> The matching submissions.
	 */
	public function query( array $args = array() ): array {
		$args = $this->parse_args( $args );

		list( $per_page, $page ) = $this->pagination( $args );

		$query = $this->build_query( $args )
			->order_by( (string) $args['orderby'], (string) $args['order'], self::SORTABLE, 'created_at' )
			->paginate( $per_page, $page );

		return $this->hydrate(
			$this->select( $query ),
			static fn( array $row ): Submission => Submission::from_row( $row )
		);
	}

	/**
	 * Counts the submissions matching a query.
	 *
	 * @param array<string, mixed> $args Same arguments as {@see SubmissionRepository::query()}.
	 */
	public function count( array $args = array() ): int {
		return $this->count_rows( $this->build_query( $this->parse_args( $args ) ) );
	}

	/**
	 * Finds a submission by ID.
	 *
	 * @param int $id Submission ID.
	 *
	 * @return Submission|null The submission, or null when it does not exist.
	 */
	public function find( int $id ): ?Submission {
		$row = $this->find_row( $id );

		return null === $row ? null : Submission::from_row( $row );
	}

	/**
	 * Returns the stored values of a submission.
	 *
	 * @param int $submission_id Submission ID.
	 *
	 * @return array<string, string> Raw values keyed by field key.
	 */
	public function meta( int $submission_id ): array {
		if ( $submission_id <= 0 ) {
			return array();
		}

		$table = Schema::table( Schema::SUBMISSION_META );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name comes from the schema, values are prepared.
		$rows = $this->db->get_results(
			$this->db->prepare( "SELECT meta_key, meta_value FROM {$table} WHERE submission_id = %d ORDER BY id ASC", $submission_id ),
			ARRAY_A
		);

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

		$values = array();

		foreach ( $rows as $row ) {
			$values[ (string) $row['meta_key'] ] = (string) $row['meta_value'];
		}

		return $values;
	}

	/**
	 * Returns the stored values of several submissions at once.
	 *
	 * Exports and list screens would otherwise run one query per row; this keeps
	 * a page or a batch to a single round trip.
	 *
	 * @param array<int, int> $submission_ids Submission IDs.
	 *
	 * @return array<int, array<string, string>> Values keyed by submission ID, then by field key.
	 */
	public function meta_for_many( array $submission_ids ): array {
		$ids = array_values( array_filter( array_map( 'absint', $submission_ids ) ) );

		if ( array() === $ids ) {
			return array();
		}

		$table        = Schema::table( Schema::SUBMISSION_META );
		$placeholders = implode( ', ', array_fill( 0, count( $ids ), '%d' ) );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Placeholders are generated, values are prepared.
		$rows = $this->db->get_results(
			$this->db->prepare(
				"SELECT submission_id, meta_key, meta_value FROM {$table} WHERE submission_id IN ( {$placeholders} ) ORDER BY id ASC",
				$ids
			),
			ARRAY_A
		);

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

		$values = array();

		foreach ( $rows as $row ) {
			$values[ (int) $row['submission_id'] ][ (string) $row['meta_key'] ] = (string) $row['meta_value'];
		}

		return $values;
	}

	/**
	 * Marks a submission as read.
	 *
	 * @param int $id Submission ID.
	 *
	 * @return bool True when the status changed.
	 */
	public function mark_read( int $id ): bool {
		if ( $id <= 0 ) {
			return false;
		}

		return $this->update_row(
			$id,
			array(
				'status'     => Submission::STATUS_READ,
				'updated_at' => $this->now(),
			)
		);
	}

	/**
	 * Deletes a submission, its values and its uploads.
	 *
	 * @param int $id Submission ID.
	 *
	 * @return bool True when the submission was removed.
	 */
	public function delete( int $id ): bool {
		if ( $id <= 0 ) {
			return false;
		}

		$submission = $this->find( $id );

		if ( ! $submission instanceof Submission ) {
			return false;
		}

		// Read the file list while the rows still exist; unlink only after commit.
		$uploads = $this->upload_paths( $id );

		$deleted = $this->transaction(
			function () use ( $id ): bool {
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Child table, outside the CRUD helpers.
				$this->db->delete( Schema::table( Schema::SUBMISSION_META ), array( 'submission_id' => $id ), array( '%d' ) );

				/*
				 * The delivery log holds a full copy of the submitted payload and
				 * doubles as the retry queue. Leaving its rows behind means an
				 * erasure request deletes the entry the administrator can see and
				 * keeps the one they cannot — and schedules retries for a
				 * submission that no longer exists.
				 */
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Child table, outside the CRUD helpers.
				$this->db->delete( Schema::table( Schema::WEBHOOK_LOG ), array( 'submission_id' => $id ), array( '%d' ) );

				return $this->delete_row( $id );
			}
		);

		if ( false === $deleted ) {
			return false;
		}

		/*
		 * Files are removed after the commit: the filesystem cannot roll back,
		 * so deleting first would destroy attachments for a delete that failed.
		 */
		$manager = new UploadManager();

		foreach ( $uploads as $payload ) {
			$manager->delete( $payload );
		}

		$this->decrement_form_count( $submission->form_id() );

		/**
		 * Fires after a submission has been deleted.
		 *
		 * @since 1.0.0
		 *
		 * @param int $id Submission ID.
		 */
		do_action( 'formpilot_submission_deleted', $id );

		return true;
	}

	/**
	 * Deletes several submissions.
	 *
	 * @param array<int, int> $ids Submission IDs.
	 */
	public function delete_many( array $ids ): int {
		$deleted = 0;

		foreach ( $ids as $id ) {
			if ( $this->delete( (int) $id ) ) {
				++$deleted;
			}
		}

		return $deleted;
	}

	/**
	 * Returns the upload payloads stored with a submission.
	 *
	 * @param int $submission_id Submission ID.
	 *
	 * @return array<int, array<string, mixed>> Stored upload payloads.
	 */
	private function upload_paths( int $submission_id ): array {
		$uploads = array();

		/*
		 * Read the rows, not `meta()`: that keys by `meta_key` and so collapses
		 * two answers sharing a key into one, which for uploads means a file left
		 * on disk after the entry pointing at it is gone.
		 */
		foreach ( $this->raw_values( $submission_id ) as $raw ) {
			$decoded = json_decode( $raw, true );

			if ( is_array( $decoded ) && ! empty( $decoded['file'] ) ) {
				$uploads[] = $decoded;
			}
		}

		return $uploads;
	}

	/**
	 * Returns every stored value of a submission, duplicates included.
	 *
	 * @param int $submission_id Submission ID.
	 *
	 * @return array<int, string> Raw values, in storage order.
	 */
	private function raw_values( int $submission_id ): array {
		if ( $submission_id <= 0 ) {
			return array();
		}

		$table = Schema::table( Schema::SUBMISSION_META );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name comes from the schema, values are prepared.
		$rows = $this->db->get_col(
			$this->db->prepare( "SELECT meta_value FROM {$table} WHERE submission_id = %d ORDER BY id ASC", $submission_id )
		);

		return is_array( $rows ) ? array_map( 'strval', $rows ) : array();
	}

	/**
	 * Lowers the cached submission counter of the owning form.
	 *
	 * @param int $form_id Form the deleted entry belonged to.
	 */
	private function decrement_form_count( int $form_id ): void {
		if ( $form_id <= 0 ) {
			return;
		}

		/*
		 * Delegated rather than repeated: the counter belongs to the forms
		 * table, and a second copy of that UPDATE here would be one more place
		 * to forget the GREATEST() guard against an unsigned column wrapping.
		 */
		$this->forms()->decrement_submission_count( $form_id );
	}

	/**
	 * Returns the form repository, built on first use.
	 */
	private function forms(): FormRepository {
		if ( ! $this->forms instanceof FormRepository ) {
			$this->forms = new FormRepository( $this->db );
		}

		return $this->forms;
	}

	/**
	 * Returns every upload path the stored submissions still point at.
	 *
	 * Used by the orphan cleaner to decide which files on disk are still in use.
	 *
	 * Answers to a deleted field keep their payload and carry `field_id = 0`
	 * ({@see \FormPilot\Database\Repositories\FieldRepository::delete()}),
	 * so they are collected through a second, index-backed pass. Without it the
	 * cleaner would treat every upload belonging to a removed field as garbage.
	 *
	 * @return array<int, string>|null Absolute paths, or null when the scan failed.
	 */
	public function referenced_files(): ?array {
		$table  = Schema::table( Schema::SUBMISSION_META );
		$fields = Schema::table( Schema::FIELDS );

		/*
		 * Joining the field definitions replaces a `LIKE '%"file":%'` scan of
		 * every stored value with an indexed lookup: `type` narrows the fields
		 * table, `field_id` narrows the meta table. On a site with millions of
		 * answers that is the difference between a full scan and a few rows.
		 *
		 * The second arm is bounded the same way: `field_id = 0` uses the
		 * `field_id` key, so the LIKE only ever sees detached rows.
		 */

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Maintenance scan, table name comes from the schema.
		$rows = $this->db->get_col(
			$this->db->prepare(
				"SELECT m.meta_value
				FROM {$table} m
				INNER JOIN {$fields} f ON f.id = m.field_id
				WHERE f.type = %s
				UNION ALL
				SELECT d.meta_value
				FROM {$table} d
				WHERE d.field_id = 0 AND d.meta_value LIKE %s",
				FieldTypes::FILE,
				'%' . $this->db->esc_like( '"file"' ) . '%'
			)
		);

		/*
		 * `null` here is a failed query, not an empty result — a renamed table, a
		 * lost connection, a half-applied migration. Saying so lets the caller
		 * stand down instead of concluding that every file on disk is unreferenced.
		 *
		 * The test is `last_error`, not the return type. `wpdb::get_col()` builds
		 * its result from `$this->last_result` and *always* returns an array, so a
		 * failed query and an empty table both come back as `array()` — an
		 * `is_array()` check here would be dead code and the guard would never
		 * fire. `last_error` is cleared at the start of every query, so reading it
		 * immediately afterwards describes this query and no other.
		 */
		if ( '' !== trim( (string) $this->db->last_error ) ) {
			$this->report_error( 'select' );

			return null;
		}

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

		$paths = array();

		foreach ( $rows as $raw ) {
			$decoded = json_decode( (string) $raw, true );

			if ( is_array( $decoded ) && ! empty( $decoded['file'] ) ) {
				$paths[] = (string) $decoded['file'];
			}
		}

		return $paths;
	}

	/**
	 * Applies the query defaults.
	 *
	 * @param array<string, mixed> $args Raw arguments.
	 *
	 * @return array<string, mixed> The normalised arguments.
	 */
	private function parse_args( array $args ): array {
		return array_merge(
			array(
				'search'   => '',
				'form'     => 0,
				'status'   => '',
				'include'  => array(),
				'orderby'  => 'created_at',
				'order'    => 'DESC',
				'per_page' => 20,
				'page'     => 1,
				'after_id' => 0,
				'date_from' => '',
				'date_to'   => '',
				'user'      => 0,
			),
			$args
		);
	}

	/**
	 * Describes the filters of a query.
	 *
	 * Shared by {@see SubmissionRepository::query()} and
	 * {@see SubmissionRepository::count()} so a listing and its pagination total
	 * can never disagree about what they are counting.
	 *
	 * @param array<string, mixed> $args Normalised query arguments.
	 *
	 * @return QueryBuilder The builder, without sorting or pagination.
	 */
	private function build_query( array $args ): QueryBuilder {
		$query = $this->query_builder()
			->where_equals( 'form_id', (int) ( $args['form'] ?? 0 ), '%d' )
			->where_equals( 'status', (string) ( $args['status'] ?? '' ) )
			->where_in( 'id', (array) ( $args['include'] ?? array() ) );

		$after = (int) ( $args['after_id'] ?? 0 );

		/*
		 * Walking a large set by remembering the last ID instead of by page:
		 * `LIMIT 200 OFFSET 400000` makes the server count past four hundred
		 * thousand rows before returning any, and it does that again for every
		 * batch. This clause uses the primary key and stays flat.
		 */
		if ( $after > 0 ) {
			$query->where( 'id > %d', array( $after ) );
		}

		$this->apply_dates( $query, $args );
		$this->apply_user( $query, $args );

		$term = is_scalar( $args['search'] ?? '' ) ? trim( (string) $args['search'] ) : '';

		if ( '' === $term ) {
			return $query;
		}

		/*
		 * Searching entries means searching their answers, which live in another
		 * table. The subquery is the one clause the typed helpers cannot express.
		 *
		 * Note the leading wildcard: it cannot use an index, so this scans the
		 * meta table. Combining a search with a form filter keeps it bounded.
		 */
		$meta = Schema::table( Schema::SUBMISSION_META );
		$like = $this->like( $term );

		return $query->where(
			"( entry_key = %s OR ip_address LIKE %s OR id IN ( SELECT submission_id FROM {$meta} WHERE meta_value LIKE %s ) )",
			array( $term, $like, $like )
		);
	}

	/**
	 * Narrows a query to a range of submission dates.
	 *
	 * Submissions are stored with `current_time( 'mysql' )`, which is site time,
	 * and the dates come from a date picker the site's administrator is looking
	 * at. Both sides are therefore already in the same zone and no conversion is
	 * wanted: converting would move the boundary by the offset and quietly drop
	 * the first or last hours of a day.
	 *
	 * The upper bound is exclusive on the following midnight rather than
	 * `<= date 23:59:59`, so an entry stored in the last second of a day is still
	 * inside the range.
	 *
	 * @param QueryBuilder         $query Query being built.
	 * @param array<string, mixed> $args  Normalised query arguments.
	 */
	private function apply_dates( QueryBuilder $query, array $args ): void {
		$from = $this->date( $args['date_from'] ?? '' );
		$to   = $this->date( $args['date_to'] ?? '' );

		if ( '' !== $from ) {
			$query->where( 'created_at >= %s', array( $from . ' 00:00:00' ) );
		}

		if ( '' !== $to ) {
			$query->where( 'created_at < %s', array( gmdate( 'Y-m-d', strtotime( $to . ' +1 day' ) ) . ' 00:00:00' ) );
		}
	}

	/**
	 * Narrows a query to the entries of one user.
	 *
	 * @param QueryBuilder         $query Query being built.
	 * @param array<string, mixed> $args  Normalised query arguments.
	 */
	private function apply_user( QueryBuilder $query, array $args ): void {
		$user = (int) ( $args['user'] ?? 0 );

		if ( 0 === $user ) {
			return;
		}

		/*
		 * Guests are stored as user 0, which is also the value that means "do not
		 * filter", so asking for them needs a value of its own.
		 */
		$query->where( 'user_id = %d', array( $user < 0 ? 0 : $user ) );
	}

	/**
	 * Validates a date coming from the request.
	 *
	 * @param mixed $value Candidate date.
	 *
	 * @return string A `Y-m-d` date, or an empty string.
	 */
	private function date( mixed $value ): string {
		$value = is_scalar( $value ) ? trim( (string) $value ) : '';

		if ( 1 !== preg_match( '/^\d{4}-\d{2}-\d{2}$/', $value ) ) {
			return '';
		}

		list( $year, $month, $day ) = array_map( 'intval', explode( '-', $value ) );

		return checkdate( $month, $day, $year ) ? $value : '';
	}

	/**
	 * Counts the matching submissions per status in one query.
	 *
	 * The status links above the table need three numbers. Asking for them one at
	 * a time is three scans of the same rows, which on a large table costs more
	 * than the listing itself.
	 *
	 * @param array<string, mixed> $args Same arguments as {@see SubmissionRepository::query()}.
	 *
	 * @return array<string, int> Counts keyed by status, plus `all`.
	 */
	public function count_by_status( array $args = array() ): array {
		$args           = $this->parse_args( $args );
		$args['status'] = '';

		$query  = $this->build_query( $args );
		$sql    = $query->aggregate_sql( 'status, COUNT(*) AS total', 'status' );
		$params = $query->count_params();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Grouped aggregate outside the CRUD helpers; every value is bound below.
		$rows = $this->db->get_results(
			array() === $params ? $sql : $this->db->prepare( $sql, $params ), // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			ARRAY_A
		);

		$counts = array( 'all' => 0 );

		foreach ( array_keys( Submission::statuses() ) as $status ) {
			$counts[ $status ] = 0;
		}

		foreach ( is_array( $rows ) ? $rows : array() as $row ) {
			$status = (string) ( $row['status'] ?? '' );
			$total  = (int) ( $row['total'] ?? 0 );

			$counts[ $status ] = ( $counts[ $status ] ?? 0 ) + $total;
			$counts['all']    += $total;
		}

		return $counts;
	}

	/**
	 * Returns the users who have submitted, newest first.
	 *
	 * Capped rather than complete: the filter is a dropdown, and a site with ten
	 * thousand submitting users needs a search box, not a longer list.
	 *
	 * @param int $limit Optional. Largest number of users returned. Default 100.
	 *
	 * @return array<int, int> User IDs.
	 */
	public function submitting_user_ids( int $limit = 100 ): array {
		$limit = max( 1, min( 500, $limit ) );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Distinct lookup outside the CRUD helpers.
		$ids = $this->db->get_col(
			$this->db->prepare(
				sprintf(
					'SELECT DISTINCT user_id FROM %s WHERE user_id > 0 ORDER BY user_id DESC LIMIT %%d',
					Schema::table( Schema::SUBMISSIONS )
				),
				$limit
			)
		);

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

	/**
	 * Sets the status of several submissions.
	 *
	 * @param array<int, int> $ids    Submission IDs.
	 * @param string          $status One of the {@see Submission} status constants.
	 */
	public function mark_status( array $ids, string $status ): int {
		if ( ! array_key_exists( $status, Submission::statuses() ) ) {
			return 0;
		}

		$changed = 0;
		$now     = $this->now();

		foreach ( $ids as $id ) {
			$id = (int) $id;

			if ( $id <= 0 ) {
				continue;
			}

			if ( $this->update_row( $id, array( 'status' => $status, 'updated_at' => $now ) ) ) {
				++$changed;
			}
		}

		return $changed;
	}

	/**
	 * Counts the submissions of a form.
	 *
	 * @param int $form_id Form ID.
	 */
	public function count_for_form( int $form_id ): int {
		if ( $form_id <= 0 ) {
			return 0;
		}

		return $this->count_rows( $this->query_builder()->where_equals( 'form_id', $form_id, '%d' ) );
	}

	/**
	 * Builds the public token of a submission.
	 */
	private function generate_entry_key(): string {
		return wp_generate_password( 32, false, false );
	}
}