<?php
/**
 * Poll, choice, and ballot storage.
 *
 * Ballots store a salted hash only. There is no user id and no address
 * column. Counts are the only thing a poll page can show.
 *
 * @package Poller
 */

namespace BmltEnabled\Poller;

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

// These queries use this plugin's own tables. Ballot totals change on every vote, so they are not cached.
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

class Repository {

	public const DB_VERSION = '4';
	public const VERSION_OPTION = 'poller_db_version';

	public function install(): void {
		global $wpdb;

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$charset = $wpdb->get_charset_collate();
		$polls = $this->table( 'polls' );
		$choices = $this->table( 'choices' );
		$ballots = $this->table( 'ballots' );
		$votes = $this->table( 'votes' );
		$words = $this->table( 'words' );
		$hidden = $this->table( 'hidden_words' );

		dbDelta(
			"CREATE TABLE {$polls} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            code varchar(8) NOT NULL,
            title varchar(160) NOT NULL DEFAULT '',
            question text NOT NULL,
            image_id bigint(20) unsigned NOT NULL DEFAULT 0,
            kind varchar(16) NOT NULL DEFAULT 'text',
            selection varchar(16) NOT NULL DEFAULT 'single',
            budget smallint unsigned NOT NULL DEFAULT 0,
            per_choice smallint unsigned NOT NULL DEFAULT 0,
            spend_all tinyint(1) unsigned NOT NULL DEFAULT 0,
            status varchar(16) NOT NULL DEFAULT 'open',
            revision bigint(20) unsigned NOT NULL DEFAULT 0,
            round int unsigned NOT NULL DEFAULT 1,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY code (code)
        ) {$charset};"
		);

		dbDelta(
			"CREATE TABLE {$choices} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            poll_id bigint(20) unsigned NOT NULL,
            label varchar(160) NOT NULL DEFAULT '',
            image_id bigint(20) unsigned NOT NULL DEFAULT 0,
            position smallint unsigned NOT NULL DEFAULT 0,
            first_round int unsigned NOT NULL DEFAULT 1,
            last_round int unsigned NOT NULL DEFAULT 0,
            PRIMARY KEY  (id),
            KEY poll_id (poll_id)
        ) {$charset};"
		);

		dbDelta(
			"CREATE TABLE {$ballots} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            poll_id bigint(20) unsigned NOT NULL,
            round int unsigned NOT NULL DEFAULT 1,
            voter_token char(64) NOT NULL,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY poll_round_voter (poll_id, round, voter_token)
        ) {$charset};"
		);

		// Before rounds, a browser had one ballot per poll. dbDelta adds the
		// new key above but never drops an index, so drop the old one here.
		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW INDEX FROM %i WHERE Key_name = %s', $ballots, 'poll_voter' ) ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange -- Upgrade from the one-ballot-per-poll key.
			$wpdb->query( $wpdb->prepare( 'ALTER TABLE %i DROP INDEX poll_voter', $ballots ) );
		}

		dbDelta(
			"CREATE TABLE {$votes} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            ballot_id bigint(20) unsigned NOT NULL,
            choice_id bigint(20) unsigned NOT NULL,
            weight smallint unsigned NOT NULL DEFAULT 1,
            PRIMARY KEY  (id),
            UNIQUE KEY ballot_choice (ballot_id, choice_id),
            KEY choice_id (choice_id)
        ) {$charset};"
		);

		// Word cloud entries. Voter text lives here, apart from the choices the admin owns.
		// Words are matched in PHP, not by the column collation, which may ignore case and accents.
		dbDelta(
			"CREATE TABLE {$words} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            ballot_id bigint(20) unsigned NOT NULL,
            word varchar(64) NOT NULL DEFAULT '',
            normalized varchar(64) NOT NULL DEFAULT '',
            PRIMARY KEY  (id),
            KEY ballot_id (ballot_id)
        ) {$charset};"
		);

		dbDelta(
			"CREATE TABLE {$hidden} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            poll_id bigint(20) unsigned NOT NULL,
            round int unsigned NOT NULL DEFAULT 1,
            normalized varchar(64) NOT NULL DEFAULT '',
            PRIMARY KEY  (id),
            KEY poll_id (poll_id)
        ) {$charset};"
		);

		update_option( self::VERSION_OPTION, self::DB_VERSION );
	}

	public function drop_tables(): void {
		global $wpdb;

		foreach ( [ 'words', 'hidden_words', 'votes', 'ballots', 'choices', 'polls' ] as $name ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange -- Uninstall removes this plugin's tables.
			$wpdb->query( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', $this->table( $name ) ) );
		}
		delete_option( self::VERSION_OPTION );
	}

	/**
	 * @param array<string, mixed> $data
	 */
	public function insert_poll( array $data ): int {
		global $wpdb;

		$now = $this->now();
		$wpdb->insert(
			$this->table( 'polls' ),
			[
				'code' => $data['code'],
				'title' => $data['title'],
				'question' => $data['question'],
				'image_id' => $data['image_id'],
				'kind' => $data['kind'],
				'selection' => $data['selection'],
				'budget' => $data['budget'],
				'per_choice' => $data['per_choice'],
				'spend_all' => $data['spend_all'] ? 1 : 0,
				'status' => $data['status'],
				'revision' => 0,
				'created_at' => $now,
				'updated_at' => $now,
			],
			[ '%s', '%s', '%s', '%d', '%s', '%s', '%d', '%d', '%d', '%s', '%d', '%s', '%s' ]
		);

		return (int) $wpdb->insert_id;
	}

	/**
	 * @param array<string, mixed> $data
	 */
	public function update_poll( int $id, array $data ): void {
		global $wpdb;

		$wpdb->update(
			$this->table( 'polls' ),
			[
				'title' => $data['title'],
				'question' => $data['question'],
				'image_id' => $data['image_id'],
				'kind' => $data['kind'],
				'selection' => $data['selection'],
				'budget' => $data['budget'],
				'per_choice' => $data['per_choice'],
				'spend_all' => $data['spend_all'] ? 1 : 0,
				'status' => $data['status'],
				'updated_at' => $this->now(),
			],
			[ 'id' => $id ],
			[ '%s', '%s', '%d', '%s', '%s', '%d', '%d', '%d', '%s', '%s' ],
			[ '%d' ]
		);
		$this->touch( $id );
	}

	public function set_status( int $id, string $status ): void {
		global $wpdb;

		$wpdb->query(
			$wpdb->prepare(
				'UPDATE %i SET status = %s, revision = revision + 1, updated_at = %s WHERE id = %d',
				$this->table( 'polls' ),
				$status,
				$this->now(),
				$id
			)
		);
	}

	public function delete_poll( int $id ): void {
		global $wpdb;

		$wpdb->query(
			$wpdb->prepare(
				'DELETE v FROM %i v INNER JOIN %i b ON b.id = v.ballot_id WHERE b.poll_id = %d',
				$this->table( 'votes' ),
				$this->table( 'ballots' ),
				$id
			)
		);
		$wpdb->query(
			$wpdb->prepare(
				'DELETE w FROM %i w INNER JOIN %i b ON b.id = w.ballot_id WHERE b.poll_id = %d',
				$this->table( 'words' ),
				$this->table( 'ballots' ),
				$id
			)
		);
		$wpdb->delete( $this->table( 'hidden_words' ), [ 'poll_id' => $id ], [ '%d' ] );
		$wpdb->delete( $this->table( 'ballots' ), [ 'poll_id' => $id ], [ '%d' ] );
		$wpdb->delete( $this->table( 'choices' ), [ 'poll_id' => $id ], [ '%d' ] );
		$wpdb->delete( $this->table( 'polls' ), [ 'id' => $id ], [ '%d' ] );
	}

	/**
	 * Start a new round on a poll. Nothing is deleted: the ballots, votes,
	 * words and hidden words of earlier rounds stay stored, and every
	 * current-results query reads only the poll's current round, so the
	 * live tally starts empty. The code, choices, rules and status are kept.
	 *
	 * One UPDATE on the poll row, which takes the row lock cast() and
	 * save_poll() take, so a vote arriving mid-reset waits and lands in the
	 * new round. Bumps the revision so open room displays and voter pages
	 * refresh. Returns false if the poll does not exist.
	 */
	public function reset_poll( int $id ): bool {
		global $wpdb;

		$updated = $wpdb->query(
			$wpdb->prepare(
				'UPDATE %i SET round = round + 1, revision = revision + 1, updated_at = %s WHERE id = %d',
				$this->table( 'polls' ),
				$this->now(),
				$id
			)
		);
		return (int) $updated > 0;
	}

	/**
	 * Earlier rounds that got answers, newest first. Rounds nobody answered
	 * are left out, since there is nothing to show for them.
	 *
	 * @return array<int, array{round: int, ballots: int, first_at: string, last_at: string}>
	 */
	public function past_rounds( int $poll_id ): array {
		global $wpdb;

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT b.round, COUNT(*) AS ballots, MIN(b.created_at) AS first_at, MAX(b.updated_at) AS last_at
            FROM %i b
            INNER JOIN %i p ON p.id = b.poll_id
            WHERE b.poll_id = %d AND b.round < p.round
            GROUP BY b.round
            ORDER BY b.round DESC',
				$this->table( 'ballots' ),
				$this->table( 'polls' ),
				$poll_id
			),
			ARRAY_A
		);

		if ( ! is_array( $rows ) ) {
			return [];
		}
		return array_map(
			static function ( $row ) {
				return [
					'round' => (int) $row['round'],
					'ballots' => (int) $row['ballots'],
					'first_at' => (string) $row['first_at'],
					'last_at' => (string) $row['last_at'],
				];
			},
			$rows
		);
	}

	/**
	 * Copy a poll's setup and choices into a new, closed poll with its own
	 * code, starting at round 1. Ballots, votes, words and hidden words of
	 * every round are not copied, and neither are retired choices.
	 *
	 * Returns the new poll's id, or null if the poll does not exist or the
	 * copy could not be saved.
	 */
	public function duplicate_poll( int $id ): ?int {
		$poll = $this->get_poll( $id );
		if ( ! $poll ) {
			return null;
		}

		try {
			$code = $this->next_code();
		} catch ( \RuntimeException $e ) {
			return null;
		}

		// The title column holds 160 characters, so trim the name to fit the suffix.
		$suffix = ' ' . __( '(copy)', 'polenta' );
		$room = 160 - ( function_exists( 'mb_strlen' ) ? mb_strlen( $suffix ) : strlen( $suffix ) );
		$title = function_exists( 'mb_substr' ) ? mb_substr( $poll['title'], 0, $room ) : substr( $poll['title'], 0, $room );

		$copy = $this->insert_poll(
			[
				'code' => $code,
				'title' => $title . $suffix,
				'question' => $poll['question'],
				'image_id' => $poll['image_id'],
				'kind' => $poll['kind'],
				'selection' => $poll['selection'],
				'budget' => $poll['budget'],
				'per_choice' => $poll['per_choice'],
				'spend_all' => $poll['spend_all'],
				'status' => 'closed',
			]
		);
		if ( ! $copy ) {
			return null;
		}

		$choices = array_map(
			static function ( $choice ) {
				return [
					'label' => $choice['label'],
					'image_id' => $choice['image_id'],
				];
			},
			$this->get_choices( $id )
		);
		if ( ! $this->replace_choices( $copy, $choices ) ) {
			$this->delete_poll( $copy );
			return null;
		}

		return $copy;
	}

	public function get_poll( int $id ): ?array {
		global $wpdb;

		$row = $wpdb->get_row(
			$wpdb->prepare(
				'SELECT * FROM %i WHERE id = %d',
				$this->table( 'polls' ),
				$id
			),
			ARRAY_A
		);

		return is_array( $row ) ? self::hydrate_poll( $row ) : null;
	}

	public function get_poll_by_code( string $code ): ?array {
		global $wpdb;

		$row = $wpdb->get_row(
			$wpdb->prepare(
				'SELECT * FROM %i WHERE code = %s',
				$this->table( 'polls' ),
				$code
			),
			ARRAY_A
		);

		return is_array( $row ) ? self::hydrate_poll( $row ) : null;
	}

	/**
	 * @return array<int, array<string, mixed>>
	 */
	public function list_polls(): array {
		global $wpdb;

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT p.*, (SELECT COUNT(*) FROM %i b WHERE b.poll_id = p.id AND b.round = p.round) AS ballots
            FROM %i p
            ORDER BY p.created_at DESC',
				$this->table( 'ballots' ),
				$this->table( 'polls' )
			),
			ARRAY_A
		);

		if ( ! is_array( $rows ) ) {
			return [];
		}

		return array_map( [ self::class, 'hydrate_poll' ], $rows );
	}

	public function next_code(): string {
		for ( $i = 0; $i < 12; $i++ ) {
			$code = Codes::generate();
			if ( $this->get_poll_by_code( $code ) === null ) {
				return $code;
			}
		}

		throw new \RuntimeException( 'code' );
	}

	/**
	 * @param array<int, array{label: string, image_id: int}> $choices
	 */
	public function replace_choices( int $poll_id, array $choices ): bool {
		$round = $this->current_round( $poll_id );
		if ( $this->ballot_count( $poll_id, $round ) > 0 ) {
			return false;
		}

		$this->write_choices( $poll_id, $round, $choices );
		$this->touch( $poll_id );
		return true;
	}

	/**
	 * Update a poll, and its choices when nobody has voted yet in the current
	 * round. The poll row
	 * stays locked for the whole save, the same lock cast() takes, so a vote
	 * sees either the old choices and rules or the new ones, never a mix.
	 *
	 * Returns false when the current round has ballots. Then the choices and voting rules
	 * already stored are kept, and only the other fields are saved.
	 *
	 * @param array<string, mixed> $fields
	 * @param array<int, array{label: string, image_id: int}>|null $choices null leaves the choices alone.
	 */
	public function save_poll( int $id, array $fields, ?array $choices ): bool {
		global $wpdb;

		$wpdb->query( 'START TRANSACTION' );
		$rules = $wpdb->get_row(
			$wpdb->prepare(
				'SELECT kind, selection, budget, per_choice, spend_all, round FROM %i WHERE id = %d FOR UPDATE',
				$this->table( 'polls' ),
				$id
			),
			ARRAY_A
		);
		if ( ! is_array( $rules ) ) {
			$wpdb->query( 'ROLLBACK' );
			return false;
		}

		$round = max( 1, (int) $rules['round'] );
		$open = $this->ballot_count( $id, $round ) === 0;
		if ( $open && null !== $choices ) {
			$this->write_choices( $id, $round, $choices );
		} elseif ( ! $open ) {
			$fields = [
				'kind' => (string) $rules['kind'],
				'selection' => (string) $rules['selection'],
				'budget' => (int) $rules['budget'],
				'per_choice' => (int) $rules['per_choice'],
				'spend_all' => ! empty( $rules['spend_all'] ),
			] + $fields;
		}
		$this->update_poll( $id, $fields );
		$wpdb->query( 'COMMIT' );
		return $open;
	}

	/**
	 * Replace the live choices. Only called while the current round has no
	 * ballots. Choices added in this round are deleted. Choices from an
	 * earlier round are retired instead, so that round's results still show
	 * the choices people voted on.
	 *
	 * @param array<int, array{label: string, image_id: int}> $choices
	 */
	private function write_choices( int $poll_id, int $round, array $choices ): void {
		global $wpdb;

		$table = $this->table( 'choices' );
		$wpdb->query(
			$wpdb->prepare(
				'DELETE FROM %i WHERE poll_id = %d AND last_round = 0 AND first_round >= %d',
				$table,
				$poll_id,
				$round
			)
		);
		$wpdb->query(
			$wpdb->prepare(
				'UPDATE %i SET last_round = %d WHERE poll_id = %d AND last_round = 0',
				$table,
				$round - 1,
				$poll_id
			)
		);
		$position = 0;
		foreach ( $choices as $choice ) {
			$wpdb->insert(
				$this->table( 'choices' ),
				[
					'poll_id' => $poll_id,
					'label' => $choice['label'],
					'image_id' => $choice['image_id'],
					'position' => $position,
					'first_round' => $round,
				],
				[ '%d', '%s', '%d', '%d', '%d' ]
			);
			$position++;
		}
	}

	/**
	 * The choices people vote on now.
	 *
	 * @return array<int, array<string, mixed>>
	 */
	public function get_choices( int $poll_id ): array {
		global $wpdb;

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT * FROM %i WHERE poll_id = %d AND last_round = 0 ORDER BY position ASC, id ASC',
				$this->table( 'choices' ),
				$poll_id
			),
			ARRAY_A
		);

		return self::hydrate_choices( $rows );
	}

	/**
	 * The choices a given round was voted on, including ones retired since.
	 *
	 * @return array<int, array<string, mixed>>
	 */
	public function round_choices( int $poll_id, int $round ): array {
		global $wpdb;

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT * FROM %i
            WHERE poll_id = %d AND first_round <= %d AND ( last_round = 0 OR last_round >= %d )
            ORDER BY position ASC, id ASC',
				$this->table( 'choices' ),
				$poll_id,
				$round,
				$round
			),
			ARRAY_A
		);

		return self::hydrate_choices( $rows );
	}

	/**
	 * @param mixed $rows
	 * @return array<int, array<string, mixed>>
	 */
	private static function hydrate_choices( $rows ): array {
		if ( ! is_array( $rows ) ) {
			return [];
		}

		return array_map(
			static function ( $row ) {
				return [
					'id' => (int) $row['id'],
					'poll_id' => (int) $row['poll_id'],
					'label' => (string) $row['label'],
					'image_id' => (int) $row['image_id'],
					'position' => (int) $row['position'],
				];
			},
			$rows
		);
	}

	/**
	 * The poll's current round, or 1 for a poll that does not exist.
	 */
	public function current_round( int $poll_id ): int {
		global $wpdb;

		$round = $wpdb->get_var(
			$wpdb->prepare(
				'SELECT round FROM %i WHERE id = %d',
				$this->table( 'polls' ),
				$poll_id
			)
		);
		return max( 1, (int) $round );
	}

	/**
	 * @param int|null $round null reads the poll's current round.
	 */
	public function ballot_count( int $poll_id, ?int $round = null ): int {
		global $wpdb;

		return (int) $wpdb->get_var(
			$wpdb->prepare(
				'SELECT COUNT(*) FROM %i WHERE poll_id = %d AND round = %d',
				$this->table( 'ballots' ),
				$poll_id,
				$round ?? $this->current_round( $poll_id )
			)
		);
	}

	/**
	 * @param int|null $round null reads the poll's current round.
	 * @return array<int, int> choice id => votes, or dots on a dot voting poll
	 */
	public function choice_counts( int $poll_id, ?int $round = null ): array {
		global $wpdb;

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT v.choice_id, SUM(v.weight) AS votes
            FROM %i v
            INNER JOIN %i b ON b.id = v.ballot_id
            WHERE b.poll_id = %d AND b.round = %d
            GROUP BY v.choice_id',
				$this->table( 'votes' ),
				$this->table( 'ballots' ),
				$poll_id,
				$round ?? $this->current_round( $poll_id )
			),
			ARRAY_A
		);

		$counts = [];
		if ( ! is_array( $rows ) ) {
			return $counts;
		}
		foreach ( $rows as $row ) {
			$counts[ (int) $row['choice_id'] ] = (int) $row['votes'];
		}
		return $counts;
	}

	/**
	 * This browser's ballot in a round. A ballot from an earlier round is
	 * not returned, so after a reset the voter sees an unanswered poll.
	 *
	 * @param int|null $round null reads the poll's current round.
	 */
	public function get_ballot( int $poll_id, string $voter_token, ?int $round = null ): ?array {
		global $wpdb;

		$row = $wpdb->get_row(
			$wpdb->prepare(
				'SELECT * FROM %i WHERE poll_id = %d AND round = %d AND voter_token = %s',
				$this->table( 'ballots' ),
				$poll_id,
				$round ?? $this->current_round( $poll_id ),
				$voter_token
			),
			ARRAY_A
		);

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

		return [
			'id' => (int) $row['id'],
			'poll_id' => (int) $row['poll_id'],
			'round' => (int) $row['round'],
			'voter_token' => (string) $row['voter_token'],
		];
	}

	/**
	 * @return array<int, int> choice id => weight
	 */
	public function ballot_weights( int $ballot_id ): array {
		global $wpdb;

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT choice_id, weight FROM %i WHERE ballot_id = %d',
				$this->table( 'votes' ),
				$ballot_id
			),
			ARRAY_A
		);

		$weights = [];
		if ( ! is_array( $rows ) ) {
			return $weights;
		}
		foreach ( $rows as $row ) {
			$weights[ (int) $row['choice_id'] ] = (int) $row['weight'];
		}
		return $weights;
	}

	/**
	 * This ballot's word cloud entries, as they were shown back.
	 *
	 * @return array<int, string>
	 */
	public function ballot_words( int $ballot_id ): array {
		global $wpdb;

		$rows = $wpdb->get_col(
			$wpdb->prepare(
				'SELECT word FROM %i WHERE ballot_id = %d ORDER BY id ASC',
				$this->table( 'words' ),
				$ballot_id
			)
		);
		return is_array( $rows ) ? array_map( 'strval', $rows ) : [];
	}

	/**
	 * Word cloud entries for Tally::words(), one row per entry. Never which
	 * ballot. Not grouped in SQL: the column collation could merge "Hope" with
	 * "hope", and Tally needs each spelling to pick the one to show.
	 *
	 * @param int|null $round null reads the poll's current round.
	 * @return array<int, array{key: string, text: string, count: int}>
	 */
	public function word_rows( int $poll_id, ?int $round = null ): array {
		global $wpdb;

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT w.normalized, w.word
            FROM %i w
            INNER JOIN %i b ON b.id = w.ballot_id
            WHERE b.poll_id = %d AND b.round = %d',
				$this->table( 'words' ),
				$this->table( 'ballots' ),
				$poll_id,
				$round ?? $this->current_round( $poll_id )
			),
			ARRAY_A
		);

		if ( ! is_array( $rows ) ) {
			return [];
		}
		return array_map(
			static function ( $row ) {
				return [
					'key' => (string) $row['normalized'],
					'text' => (string) $row['word'],
					'count' => 1,
				];
			},
			$rows
		);
	}

	/**
	 * @param int|null $round null reads the poll's current round.
	 * @return array<int, string> keys an admin hid from this poll's cloud in that round
	 */
	public function hidden_words( int $poll_id, ?int $round = null ): array {
		global $wpdb;

		$rows = $wpdb->get_col(
			$wpdb->prepare(
				'SELECT normalized FROM %i WHERE poll_id = %d AND round = %d ORDER BY normalized ASC',
				$this->table( 'hidden_words' ),
				$poll_id,
				$round ?? $this->current_round( $poll_id )
			)
		);
		return is_array( $rows ) ? array_map( 'strval', $rows ) : [];
	}

	/**
	 * Hide a word from the current round's cloud, or show it again. Bumps the
	 * revision so open pages pick it up on their next refresh.
	 */
	public function set_word_hidden( int $poll_id, string $key, bool $hidden ): void {
		global $wpdb;

		$table = $this->table( 'hidden_words' );
		$round = $this->current_round( $poll_id );
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT id, normalized FROM %i WHERE poll_id = %d AND round = %d',
				$table,
				$poll_id,
				$round
			),
			ARRAY_A
		);
		$found = [];
		foreach ( is_array( $rows ) ? $rows : [] as $row ) {
			if ( (string) $row['normalized'] === $key ) {
				$found[] = (int) $row['id'];
			}
		}

		if ( $hidden && ! $found ) {
			$wpdb->insert(
				$table,
				[
					'poll_id' => $poll_id,
					'round' => $round,
					'normalized' => $key,
				],
				[ '%d', '%d', '%s' ]
			);
		} elseif ( ! $hidden ) {
			foreach ( $found as $id ) {
				$wpdb->delete( $table, [ 'id' => $id ], [ '%d' ] );
			}
		}
		$this->touch( $poll_id );
	}

	/**
	 * Replace this browser's word cloud entries. Throws RuntimeException('closed')
	 * or RuntimeException('invalid') after rolling back.
	 *
	 * The entries are checked again against the locked poll row, like cast().
	 *
	 * @param array<int, array{text: string, key: string}> $words
	 */
	public function cast_words( int $poll_id, string $voter_token, array $words ): void {
		global $wpdb;

		$polls = $this->table( 'polls' );
		$table = $this->table( 'words' );
		$now = $this->now();

		$wpdb->query( 'START TRANSACTION' );

		$poll = $wpdb->get_row(
			$wpdb->prepare(
				'SELECT id, status, selection, budget, round FROM %i WHERE id = %d FOR UPDATE',
				$polls,
				$poll_id
			)
		);
		if ( ! $poll || 'open' !== $poll->status ) {
			$wpdb->query( 'ROLLBACK' );
			throw new \RuntimeException( 'closed' );
		}

		$checked = BallotRules::words( array_column( $words, 'text' ), (int) $poll->budget );
		if ( 'words' !== $poll->selection || null !== $checked['error'] || $checked['words'] !== $words ) {
			$wpdb->query( 'ROLLBACK' );
			throw new \RuntimeException( 'invalid' );
		}

		$ballot_id = $this->upsert_ballot( $poll_id, (int) $poll->round, $voter_token, $now );
		$wpdb->delete( $table, [ 'ballot_id' => $ballot_id ], [ '%d' ] );
		foreach ( $words as $word ) {
			$ok = $wpdb->insert(
				$table,
				[
					'ballot_id' => $ballot_id,
					'word' => $word['text'],
					'normalized' => $word['key'],
				],
				[ '%d', '%s', '%s' ]
			);
			if ( ! $ok ) {
				$wpdb->query( 'ROLLBACK' );
				throw new \RuntimeException( 'invalid' );
			}
		}

		$wpdb->query(
			$wpdb->prepare(
				'UPDATE %i SET revision = revision + 1, updated_at = %s WHERE id = %d',
				$polls,
				$now,
				$poll_id
			)
		);
		$wpdb->query( 'COMMIT' );
	}

	/**
	 * Find or add this browser's ballot for the round inside a cast
	 * transaction. The round comes from the locked poll row. Rolls back
	 * and throws RuntimeException('invalid') when there is no row to use.
	 */
	private function upsert_ballot( int $poll_id, int $round, string $voter_token, string $now ): int {
		global $wpdb;

		$ballots = $this->table( 'ballots' );
		$ballot_id = (int) $wpdb->get_var(
			$wpdb->prepare(
				'SELECT id FROM %i WHERE poll_id = %d AND round = %d AND voter_token = %s LIMIT 1 FOR UPDATE',
				$ballots,
				$poll_id,
				$round,
				$voter_token
			)
		);

		if ( 0 === $ballot_id ) {
			$inserted = $wpdb->insert(
				$ballots,
				[
					'poll_id' => $poll_id,
					'round' => $round,
					'voter_token' => $voter_token,
					'created_at' => $now,
					'updated_at' => $now,
				],
				[ '%d', '%d', '%s', '%s', '%s' ]
			);
			if ( ! $inserted ) {
				$ballot_id = (int) $wpdb->get_var(
					$wpdb->prepare(
						'SELECT id FROM %i WHERE poll_id = %d AND round = %d AND voter_token = %s LIMIT 1',
						$ballots,
						$poll_id,
						$round,
						$voter_token
					)
				);
			} else {
				$ballot_id = (int) $wpdb->insert_id;
			}
			if ( 0 === $ballot_id ) {
				$wpdb->query( 'ROLLBACK' );
				throw new \RuntimeException( 'invalid' );
			}
		} else {
			$wpdb->update(
				$ballots,
				[ 'updated_at' => $now ],
				[ 'id' => $ballot_id ],
				[ '%s' ],
				[ '%d' ]
			);
		}
		return $ballot_id;
	}

	/**
	 * Replace this browser's ballot. Throws RuntimeException('closed')
	 * or RuntimeException('invalid') after rolling back.
	 *
	 * The weights are checked again against the locked poll row, so a rule
	 * change saved between the first check and this write cannot slip through.
	 *
	 * @param array<int, int> $weights choice id => weight
	 */
	public function cast( int $poll_id, string $voter_token, array $weights ): void {
		global $wpdb;

		$polls = $this->table( 'polls' );
		$choices = $this->table( 'choices' );
		$votes = $this->table( 'votes' );
		$now = $this->now();

		$wpdb->query( 'START TRANSACTION' );

		$poll = $wpdb->get_row(
			$wpdb->prepare(
				'SELECT id, status, selection, budget, per_choice, spend_all, round FROM %i WHERE id = %d FOR UPDATE',
				$polls,
				$poll_id
			)
		);
		if ( ! $poll || 'open' !== $poll->status ) {
			$wpdb->query( 'ROLLBACK' );
			throw new \RuntimeException( 'closed' );
		}

		$choice_ids = array_values( array_map( 'intval', array_keys( $weights ) ) );
		$submitted = 'dots' === $poll->selection ? $weights : $choice_ids;
		$checked = BallotRules::weigh( (array) $poll, $submitted, $choice_ids );
		if ( count( $choice_ids ) === 0 || null !== $checked['error'] || $checked['weights'] != $weights ) {
			$wpdb->query( 'ROLLBACK' );
			throw new \RuntimeException( 'invalid' );
		}

		$placeholders = implode( ',', array_fill( 0, count( $choice_ids ), '%d' ) );
		// The IN list is only repeated %d placeholders, one per choice id.
        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$found = $wpdb->get_col(
			$wpdb->prepare(
				"SELECT id FROM %i WHERE poll_id = %d AND last_round = 0 AND id IN ({$placeholders})",
				...array_merge( [ $choices, $poll_id ], $choice_ids )
			)
		);
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$found = is_array( $found ) ? array_map( 'intval', $found ) : [];
		sort( $found );
		$expected = array_map( 'intval', $choice_ids );
		sort( $expected );
		if ( $found !== $expected ) {
			$wpdb->query( 'ROLLBACK' );
			throw new \RuntimeException( 'invalid' );
		}

		$ballot_id = $this->upsert_ballot( $poll_id, (int) $poll->round, $voter_token, $now );

		$wpdb->delete( $votes, [ 'ballot_id' => $ballot_id ], [ '%d' ] );
		foreach ( $expected as $choice_id ) {
			$ok = $wpdb->insert(
				$votes,
				[
					'ballot_id' => $ballot_id,
					'choice_id' => $choice_id,
					'weight' => (int) $weights[ $choice_id ],
				],
				[ '%d', '%d', '%d' ]
			);
			if ( ! $ok ) {
				$wpdb->query( 'ROLLBACK' );
				throw new \RuntimeException( 'invalid' );
			}
		}

		$wpdb->query(
			$wpdb->prepare(
				'UPDATE %i SET revision = revision + 1, updated_at = %s WHERE id = %d',
				$polls,
				$now,
				$poll_id
			)
		);
		$wpdb->query( 'COMMIT' );
	}

	private function touch( int $id ): void {
		global $wpdb;

		$wpdb->query(
			$wpdb->prepare(
				'UPDATE %i SET revision = revision + 1, updated_at = %s WHERE id = %d',
				$this->table( 'polls' ),
				$this->now(),
				$id
			)
		);
	}

	private function table( string $name ): string {
		global $wpdb;

		return $wpdb->prefix . 'poller_' . $name;
	}

	/**
	 * @param array<string, mixed> $row
	 * @return array<string, mixed>
	 */
	public static function hydrate_poll( array $row ): array {
		return [
			'id' => (int) $row['id'],
			'code' => (string) $row['code'],
			'title' => (string) $row['title'],
			'question' => (string) $row['question'],
			'image_id' => (int) $row['image_id'],
			'kind' => (string) $row['kind'],
			'selection' => (string) $row['selection'],
			'budget' => (int) ( $row['budget'] ?? 0 ),
			'per_choice' => (int) ( $row['per_choice'] ?? 0 ),
			'spend_all' => ! empty( $row['spend_all'] ),
			'status' => (string) $row['status'],
			'revision' => (int) $row['revision'],
			'round' => max( 1, (int) ( $row['round'] ?? 1 ) ),
			'created_at' => (string) $row['created_at'],
			'updated_at' => (string) $row['updated_at'],
			'ballots' => isset( $row['ballots'] ) ? (int) $row['ballots'] : null,
		];
	}

	private function now(): string {
		return current_time( 'mysql', true );
	}
}