row( "SELECT * FROM {$table} WHERE id = %d", array( $id ) ); } /** * Finds the candidate holding a check-in token. * * @since 0.1.0 * * @param string $token Plain token from the browser. * @return object|null */ public function by_token( string $token ): ?object { if ( ! preg_match( '/^[a-f0-9]{64}$/D', $token ) ) { return null; } $table = Tables::name( 'candidates' ); return $this->row( "SELECT * FROM {$table} WHERE token_hash = %s LIMIT 1", array( hash( 'sha256', $token ) ) ); } /** * Finds a candidate by email within a session. * * @since 0.1.0 * * @param int $session_id Session id. * @param string $email Email. * @return object|null */ public function by_email( int $session_id, string $email ): ?object { $table = Tables::name( 'candidates' ); return $this->row( "SELECT * FROM {$table} WHERE session_id = %d AND email_key = %s LIMIT 1", array( $session_id, Identity::email_key( $email ) ) ); } /** * Every record for one email across sessions (privacy export/erase). * * @since 0.1.0 * * @param string $email Email. * @return object[] */ public function all_by_email( string $email ): array { $table = Tables::name( 'candidates' ); return $this->rows( "SELECT * FROM {$table} WHERE email_key = %s ORDER BY id ASC", array( Identity::email_key( $email ) ) ); } /** * Matches a check-in attempt: the email must exist in the session and the phone number must match. * * @since 0.1.0 * * @param int $session_id Session id. * @param string $email Email. * @param string $phone Phone as typed. * @return object|null */ public function by_identity( int $session_id, string $email, string $phone ): ?object { $candidate = $this->by_email( $session_id, $email ); if ( ! $candidate || ! hash_equals( (string) $candidate->phone_normalized, Identity::phone( $phone ) ) ) { return null; } return $candidate; } /** * Every candidate of a session in queue order (bounded). * * @since 0.1.0 * * @param int $session_id Session id. * @param int $limit Maximum rows. * @return object[] */ public function for_session( int $session_id, int $limit = 2000 ): array { $table = Tables::name( 'candidates' ); return $this->rows( "SELECT * FROM {$table} WHERE session_id = %d ORDER BY queue_order ASC, id ASC LIMIT %d", array( $session_id, max( 1, min( 5000, $limit ) ) ) ); } /** * A page of candidates. * * @since 0.1.0 * * @param int $session_id Session id. * @param array $args page, per_page, search, status. * @return array{items: object[], total: int} */ public function query( int $session_id, array $args ): array { list( , $per_page, $offset ) = $this->paging( $args ); $table = Tables::name( 'candidates' ); $where = array( 'session_id = %d' ); $params = array( $session_id ); if ( ! empty( $args['search'] ) ) { $like = '%' . $this->wpdb->esc_like( (string) $args['search'] ) . '%'; $where[] = '( full_name LIKE %s OR email LIKE %s OR phone LIKE %s )'; $params[] = $like; $params[] = $like; $params[] = $like; } if ( ! empty( $args['status'] ) ) { $where[] = 'status = %s'; $params[] = (string) $args['status']; } $clause = implode( ' AND ', $where ); $total = (int) $this->value( "SELECT COUNT(*) FROM {$table} WHERE {$clause}", $params ); $items = $this->rows( "SELECT * FROM {$table} WHERE {$clause} ORDER BY queue_order ASC, id ASC LIMIT %d OFFSET %d", array_merge( $params, array( $per_page, $offset ) ) ); return array( 'items' => $items, 'total' => $total, ); } /** * Candidates per status in a session. * * @since 0.1.0 * * @param int $session_id Session id. * @return array */ public function status_counts( int $session_id ): array { $table = Tables::name( 'candidates' ); $counts = array(); foreach ( $this->rows( "SELECT status, COUNT(*) AS total FROM {$table} WHERE session_id = %d GROUP BY status", array( $session_id ) ) as $row ) { $counts[ $row->status ] = (int) $row->total; } return $counts; } /** * Highest queue position in a session. * * @since 0.1.0 * * @param int $session_id Session id. * @return int */ public function max_order( int $session_id ): int { $table = Tables::name( 'candidates' ); return (int) $this->value( "SELECT COALESCE(MAX(queue_order),0) FROM {$table} WHERE session_id = %d", array( $session_id ) ); } /** * Inserts a candidate. * * @since 0.1.0 * * @param array $data Columns. * @return int New id, 0 on failure (including a duplicate email). */ public function insert( array $data ): int { $data['created_at'] = gmdate( 'Y-m-d H:i:s' ); $data['updated_at'] = $data['created_at']; return $this->insert_row( Tables::name( 'candidates' ), $data ); } /** * Updates a candidate. * * @since 0.1.0 * * @param int $id Candidate id. * @param array $data Columns. * @return bool */ public function update( int $id, array $data ): bool { $data['updated_at'] = gmdate( 'Y-m-d H:i:s' ); return $this->update_row( Tables::name( 'candidates' ), $id, $data ); } /** * Records that the candidate's page is open: one cheap indexed write used by every heartbeat. * * @since 0.1.0 * * @param int $id Candidate id. * @param string $now UTC DATETIME. * @return bool */ public function touch( int $id, string $now ): bool { $table = Tables::name( 'candidates' ); return false !== $this->execute( "UPDATE {$table} SET last_seen_at = %s WHERE id = %d", array( $now, $id ) ); } /** * Deletes a candidate with the e-mail and activity records that name them. * * @since 0.1.0 * * @param int $id Candidate id. * @return bool */ public function delete( int $id ): bool { foreach ( array( 'emails', 'events' ) as $key ) { if ( ! $this->delete_where( Tables::name( $key ), array( 'candidate_id' => $id ) ) ) { return false; } } return $this->delete_where( Tables::name( 'candidates' ), array( 'id' => $id ) ); } /** * Atomically claims a reminder so only one cron worker sends it. * * @since 0.1.0 * * @param int $id Candidate id. * @param string $kind `email` or `join`. * @param string $now UTC DATETIME. * @return bool True when this caller won the claim. */ public function claim_reminder( int $id, string $kind, string $now ): bool { $table = Tables::name( 'candidates' ); $columns = array( 'email' => array( 'reminder_status', 'reminder_sent_at' ), 'join' => array( 'join_reminder_status', 'join_reminder_sent_at' ), ); if ( ! isset( $columns[ $kind ] ) ) { return false; } list( $status, $sent ) = $columns[ $kind ]; return 1 === $this->execute( "UPDATE {$table} SET {$status} = 'processing', updated_at = %s WHERE id = %d AND {$status} = 'pending' AND {$sent} IS NULL", array( $now, $id ) ); } /** * The first queued candidate whose page is open. * * @since 0.1.0 * * @param int $session_id Session id. * @param string $cutoff UTC DATETIME; candidates seen before this are offline. * @return object|null */ public function ready_next( int $session_id, string $cutoff ): ?object { $table = Tables::name( 'candidates' ); return $this->row( "SELECT * FROM {$table} WHERE session_id = %d AND status = 'queued' AND last_seen_at >= %s ORDER BY queue_order ASC, id ASC LIMIT 1", array( $session_id, $cutoff ) ); } /** * How many online queued candidates are in front of this one. * * @since 0.1.0 * * @param object $candidate Candidate row. * @param string $cutoff UTC DATETIME. * @return int */ public function ahead( object $candidate, string $cutoff ): int { $table = Tables::name( 'candidates' ); return (int) $this->value( "SELECT COUNT(*) FROM {$table} WHERE session_id = %d AND status = 'queued' AND last_seen_at >= %s AND ( queue_order < %d OR ( queue_order = %d AND id < %d ) )", array( (int) $candidate->session_id, $cutoff, (int) $candidate->queue_order, (int) $candidate->queue_order, (int) $candidate->id ) ); } /** * Ids of a session's candidates. * * @since 0.1.0 * * @param int $session_id Session id. * @return int[] */ public function ids_for_session( int $session_id ): array { $table = Tables::name( 'candidates' ); return array_map( 'intval', $this->column( "SELECT id FROM {$table} WHERE session_id = %d ORDER BY id ASC", array( $session_id ) ) ); } }