db->insert( $this->table, array( 'booking_id' => $booking_id, 'channel' => $channel, 'template' => mb_substr( $template, 0, 60 ), 'recipient' => mb_substr( $recipient, 0, 191 ), 'status' => $ok ? 'sent' : 'failed', 'error' => mb_substr( $error, 0, 255 ), 'created_at' => self::now(), ) ); } /** * A booking's messages, newest first. * * @param int $booking_id Booking. * @return array> */ public function for_booking( int $booking_id ): array { // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery $rows = (array) $this->db->get_results( $this->db->prepare( "SELECT * FROM {$this->table} WHERE booking_id = %d ORDER BY id DESC LIMIT 100", $booking_id ), ARRAY_A ); return array_map( static fn( array $row ): array => array( 'channel' => (string) $row['channel'], 'template' => (string) $row['template'], 'recipient' => (string) $row['recipient'], 'status' => (string) $row['status'], 'error' => (string) $row['error'], 'created_at' => self::ts( $row['created_at'] ), ), $rows ); } /** * Failed messages since a time, for the health check. * * @param int $since Unix time. */ public function failures_since( int $since ): int { // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery return (int) $this->db->get_var( $this->db->prepare( "SELECT COUNT(*) FROM {$this->table} WHERE status = 'failed' AND created_at >= %s", self::dt( $since ) ) ); } /** * Whether a once-per-booking message was sent. * * @param int $booking_id Booking. * @param string $kind Kind, as given to claim(). */ public function was_sent( int $booking_id, string $kind ): bool { $sent = $this->db->prefix . 'schedulereed_sent'; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery return null !== $this->db->get_var( $this->db->prepare( "SELECT booking_id FROM {$sent} WHERE booking_id = %d AND kind = %s", $booking_id, $kind ) ); } /** * Mark a once-per-booking message as sent. False when it already was. * * @param int $booking_id Booking. * @param string $kind e.g. "reminder_24". */ public function claim( int $booking_id, string $kind ): bool { $sent = $this->db->prefix . 'schedulereed_sent'; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery return 1 === (int) $this->db->query( $this->db->prepare( "INSERT IGNORE INTO {$sent} (booking_id, kind, sent_at) VALUES (%d, %s, %s)", $booking_id, mb_substr( $kind, 0, 60 ), self::now() ) ); } /** * Forget sent reminders of a booking, e.g. after it moves to another day. * * @param int $booking_id Booking. * @param string $prefix Kinds starting with this. */ public function unclaim( int $booking_id, string $prefix ): void { $sent = $this->db->prefix . 'schedulereed_sent'; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery $this->db->query( $this->db->prepare( "DELETE FROM {$sent} WHERE booking_id = %d AND kind LIKE %s", $booking_id, $this->db->esc_like( $prefix ) . '%' ) ); } }