table_name = $wpdb->prefix . 'aipt_chatbot_messages'; /** * Filter the maximum retry attempts for queued messages. * * @since 2.5.0 * @param int $max_retries Default maximum retry attempts. */ $this->max_retries = (int) apply_filters('aipt_chatbot_max_retries', self::DEFAULT_MAX_RETRIES); $this->max_retries = max(1, min(ChatbotConstants::MAX_RETRY_ATTEMPTS, $this->max_retries)); } /** * Get maximum retry attempts. * * @return int Maximum retry attempts. */ public function getMaxRetries(): int { return $this->max_retries; } /** * Queue a message for later processing. * * Only stores email if explicit consent is given (GDPR compliance). * Email is encrypted at rest using AES-256-GCM encryption. * * @param string $message Customer's message. * @param string $session_id Session identifier. * @param string $error_msg Optional error message for logging. * @param string $user_name Optional user name from pre-chat form. * @param string $user_email Optional user email from pre-chat form. * @param bool $consent_given Whether user gave consent for PII storage. * @param int|null $consent_timestamp Unix timestamp when consent was given. * @param string $record_type Type of record: 'first_contact' or 'error'. Defaults to 'error'. * @return int Queue record ID. */ public function enqueue( string $message, string $session_id, string $error_msg = '', string $user_name = '', string $user_email = '', bool $consent_given = false, ?int $consent_timestamp = null, string $record_type = 'error' ): int { global $wpdb; $sanitized_email = $user_email ? sanitize_email( $user_email ) : ''; $email_to_store = null; if ( $consent_given && ! empty( $sanitized_email ) ) { $email_to_store = Encryption::encrypt( $sanitized_email ); } $valid_record_types = [ 'first_contact', 'error' ]; if ( ! in_array( $record_type, $valid_record_types, true ) ) { $record_type = 'error'; } $status = ( $record_type === 'first_contact' ) ? 'info' : 'queued'; $result = $wpdb->insert( $this->table_name, [ 'session_id' => sanitize_text_field( $session_id ), 'user_name' => $user_name ? sanitize_text_field( mb_substr( $user_name, 0, ChatbotConstants::maxUserNameLength(), 'UTF-8' ) ) : null, 'user_email' => $email_to_store, 'message' => sanitize_textarea_field( mb_substr( $message, 0, ChatbotConstants::maxMessageLength(), 'UTF-8' ) ), 'status' => $status, 'error_message' => $error_msg ? sanitize_text_field( mb_substr( $error_msg, 0, 255, 'UTF-8' ) ) : null, 'consent_given' => $consent_given ? 1 : null, 'consent_timestamp' => $consent_given && $consent_timestamp ? $consent_timestamp : null, 'record_type' => $record_type, 'created_at' => current_time( 'mysql' ), 'updated_at' => current_time( 'mysql' ), ], [ '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%s', '%s', '%s' ] ); if ( $result === false ) { throw new \RuntimeException( 'Failed to queue message.' ); } $queue_id = (int) $wpdb->insert_id; /** * Action fired when a message is queued. * * @since 2.5.0 * @param int $queue_id The queue record ID. * @param string $message The queued message. * @param string $session_id The session identifier. */ do_action( 'aipt_chatbot_message_queued', $queue_id, $message, $session_id ); return $queue_id; } /** * Check if a session already has a first_contact record. * * Used to prevent duplicate first contact tracking for the same session. * * @param string $session_id Session identifier. * @return bool True if first contact already exists. */ public function hasFirstContact( string $session_id ): bool { global $wpdb; $count = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$this->table_name} WHERE session_id = %s AND record_type = 'first_contact'", sanitize_text_field( $session_id ) ) ); return $count > 0; } /** * Track first contact for a user session. * * Records the user's first message with their contact information. * Only tracks users who have provided email with consent (GDPR compliance). * This method is idempotent - it won't create duplicate first_contact records. * * Note: There's a small race condition window between hasFirstContact() check * and the actual insert. In the unlikely event of concurrent requests, the worst * case is a duplicate first_contact record which is acceptable. The database * index on (session_id, record_type) makes the check fast. * * @param string $message Customer's first message. * @param string $session_id Session identifier. * @param string $user_name User name from pre-chat form. * @param string $user_email User email from pre-chat form. * @param bool $consent_given Whether user gave consent for PII storage. * @param int|null $consent_timestamp Unix timestamp when consent was given. * @return int|null Queue record ID or null if not tracked. */ public function trackFirstContact( string $message, string $session_id, string $user_name, string $user_email, bool $consent_given, ?int $consent_timestamp ): ?int { if ( ! $consent_given || empty( $user_email ) ) { return null; } if ( $this->hasFirstContact( $session_id ) ) { return null; } try { return $this->enqueue( $message, $session_id, '', $user_name, $user_email, $consent_given, $consent_timestamp, 'first_contact' ); } catch ( \Exception $e ) { return null; } } /** * Decrypt user email for display in admin panel. * * Handles three cases: * 1. Encrypted email (AIPT_ENC_ prefix) - attempts decryption * 2. Legacy unencrypted email (plain text) - returns as-is * 3. Empty/null input - returns null * * @param string|null $encrypted_email Encrypted email from database. * @return string|null Decrypted email or null if empty/failed. */ public function decryptEmail( ?string $encrypted_email ): ?string { if ( empty( $encrypted_email ) ) { return null; } if ( ! Encryption::is_encrypted( $encrypted_email ) ) { return is_email( $encrypted_email ) ? $encrypted_email : null; } $decrypted = Encryption::decrypt( $encrypted_email ); if ( $decrypted === $encrypted_email ) { return null; } return ! empty( $decrypted ) ? $decrypted : null; } /** * Get queued messages. * * @param string $status Filter by status. * @param int $page Page number. * @param int $per_page Items per page. * @return array{messages: array, total: int, pages: int} */ public function getQueued(string $status = 'queued', int $page = 1, int $per_page = 20): array { global $wpdb; $page = max(1, $page); $per_page = min(ChatbotConstants::maxPaginationPerPage(), max(1, $per_page)); $offset = ($page - 1) * $per_page; $valid_statuses = ['queued', 'responded', 'retried', 'failed', 'delivered', 'info']; $status = in_array($status, $valid_statuses, true) ? $status : 'queued'; $total = (int) $wpdb->get_var($wpdb->prepare( "SELECT COUNT(*) FROM {$this->table_name} WHERE status = %s", $status )); $messages = $wpdb->get_results($wpdb->prepare( "SELECT * FROM {$this->table_name} WHERE status = %s ORDER BY created_at DESC LIMIT %d OFFSET %d", $status, $per_page, $offset ), ARRAY_A); return [ 'messages' => $messages ?: [], 'total' => $total, 'pages' => (int) ceil($total / $per_page), ]; } /** * Get a single queued message by ID. * * @param int $id Queue record ID. * @return array|null Message data or null if not found. */ public function get(int $id): ?array { global $wpdb; $message = $wpdb->get_row($wpdb->prepare( "SELECT * FROM {$this->table_name} WHERE id = %d", $id ), ARRAY_A); return $message ?: null; } /** * Mark message as responded with admin response. * * @param int $id Queue record ID. * @param string $response Admin's response text. * @return bool True on success. */ public function markResponded(int $id, string $response): bool { global $wpdb; $result = $wpdb->update( $this->table_name, [ 'status' => 'responded', 'admin_response' => sanitize_textarea_field($response), 'updated_at' => current_time('mysql'), ], ['id' => $id], ['%s', '%s', '%s'], ['%d'] ); if ($result !== false) { /** * Action fired when admin responds to a queued message. * * @since 2.5.0 * @param int $id The queue record ID. * @param string $response The admin's response. */ do_action('aipt_chatbot_admin_responded', $id, $response); } return $result !== false; } /** * Mark message as responded with AI response (from retry). * * @param int $id Queue record ID. * @param string $ai_response AI-generated response. * @return bool True on success. */ public function markRetriedSuccess(int $id, string $ai_response): bool { global $wpdb; $result = $wpdb->update( $this->table_name, [ 'status' => 'responded', 'ai_response' => wp_kses_post($ai_response), 'updated_at' => current_time('mysql'), ], ['id' => $id], ['%s', '%s', '%s'], ['%d'] ); return $result !== false; } /** * Record a successfully answered chatbot exchange for admin review. * * Stores only the exchange text needed for review: no contact fields or * consent metadata are duplicated for successful AI-handled messages. * * @param string $message Customer's message. * @param string $session_id Session identifier. * @param string $ai_response AI-generated response. * @return int Queue record ID. */ public function recordSuccessfulExchange( string $message, string $session_id, string $ai_response ): int { global $wpdb; $result = $wpdb->insert( $this->table_name, [ 'session_id' => sanitize_text_field( $session_id ), 'user_name' => null, 'user_email' => null, 'message' => sanitize_textarea_field( mb_substr( $message, 0, ChatbotConstants::maxMessageLength(), 'UTF-8' ) ), 'status' => 'responded', 'ai_response' => wp_kses_post( $ai_response ), 'consent_given' => null, 'consent_timestamp' => null, 'record_type' => 'conversation', 'created_at' => current_time( 'mysql' ), 'updated_at' => current_time( 'mysql' ), ], [ '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%s', '%s', '%s' ] ); if ( $result === false ) { throw new \RuntimeException( 'Failed to record chatbot exchange.' ); } return (int) $wpdb->insert_id; } /** * Increment retry count and optionally mark as failed. * * @param int $id Queue record ID. * @param string $error_msg Error message from retry attempt. * @return bool True on success, false if max retries reached. */ public function incrementRetry( int $id, string $error_msg = '' ): bool { global $wpdb; $error_msg_sanitized = $error_msg ? sanitize_text_field( mb_substr( $error_msg, 0, 255, 'UTF-8' ) ) : null; $updated_at = current_time( 'mysql' ); $result = $wpdb->query( $wpdb->prepare( "UPDATE {$this->table_name} SET retry_count = retry_count + 1, status = CASE WHEN retry_count + 1 >= %d THEN 'failed' ELSE 'retried' END, error_message = %s, updated_at = %s WHERE id = %d", $this->max_retries, $error_msg_sanitized, $updated_at, $id ) ); if ( false === $result || 0 === $result ) { return false; } $message = $this->get( $id ); return $message && (int) $message['retry_count'] < $this->max_retries; } /** * Delete a queued message. * * @param int $id Queue record ID. * @return bool True on success. */ public function delete(int $id): bool { global $wpdb; $result = $wpdb->delete( $this->table_name, ['id' => $id], ['%d'] ); return $result !== false; } /** * Get messages pending retry. * * @param int $limit Maximum messages to retrieve. * @return array Array of queue records. */ public function getPendingRetry(int $limit = 10): array { global $wpdb; $limit = min(100, max(1, $limit)); $messages = $wpdb->get_results($wpdb->prepare( "SELECT * FROM {$this->table_name} WHERE status IN ('queued', 'retried') AND retry_count < %d ORDER BY created_at ASC LIMIT %d", $this->max_retries, $limit ), ARRAY_A); return $messages ?: []; } /** * Clean up old records. * * @param int $days_old Delete records older than this many days. * @return int Number of records deleted. */ public function cleanup(int $days_old = 30): int { global $wpdb; $days_old = max(1, $days_old); $cutoff_timestamp = current_time( 'timestamp' ) - ( $days_old * DAY_IN_SECONDS ); $cutoff_date = wp_date( 'Y-m-d H:i:s', $cutoff_timestamp ); $result = $wpdb->query($wpdb->prepare( "DELETE FROM {$this->table_name} WHERE created_at < %s", $cutoff_date )); return $result !== false ? (int) $result : 0; } /** * Get queue statistics. * * Includes separate stats for first_contact records (tracked users) * and error records (messages needing attention). * * @return array Statistics array. */ public function getStats(): array { global $wpdb; $results = $wpdb->get_results( "SELECT status, record_type, COUNT(*) as count FROM {$this->table_name} GROUP BY status, record_type", ARRAY_A ); $valid_statuses = ['queued', 'responded', 'retried', 'failed', 'delivered', 'info']; $stats = [ 'queued' => 0, 'responded' => 0, 'retried' => 0, 'failed' => 0, 'delivered' => 0, 'info' => 0, 'total' => 0, 'first_contact' => 0, 'error' => 0, 'conversation' => 0, ]; foreach ($results as $row) { $status = $row['status'] ?? ''; $record_type = $row['record_type'] ?? 'error'; $count = (int) $row['count']; if (in_array($status, $valid_statuses, true)) { $stats[$status] += $count; $stats['total'] += $count; } if ($record_type === 'first_contact') { $stats['first_contact'] += $count; } elseif ($record_type === 'conversation') { $stats['conversation'] += $count; } else { $stats['error'] += $count; } } return $stats; } /** * Get messages for a specific session. * * @param string $session_id Session identifier. * @param int $limit Maximum messages to retrieve. * @return array Array of messages. */ public function getBySession(string $session_id, int $limit = 50): array { global $wpdb; $messages = $wpdb->get_results($wpdb->prepare( "SELECT * FROM {$this->table_name} WHERE session_id = %s ORDER BY created_at DESC LIMIT %d", $session_id, min(100, max(1, $limit)) ), ARRAY_A); return $messages ?: []; } /** * Get pending admin responses for widget polling. * * Returns messages that have been responded to by admin but not yet * delivered to the customer widget. * * @param string $session_id Session identifier. * @param int $limit Maximum messages to retrieve. * @return array Array of messages with admin responses. */ public function getPendingResponses(string $session_id, int $limit = 10): array { global $wpdb; $sql = "SELECT id, session_id, message, admin_response, created_at, updated_at FROM {$this->table_name} WHERE session_id = %s AND status = 'responded' AND admin_response IS NOT NULL AND admin_response != '' ORDER BY updated_at DESC LIMIT %d"; $messages = $wpdb->get_results($wpdb->prepare($sql, sanitize_text_field($session_id), min(50, max(1, $limit))), ARRAY_A); return $messages ?: []; } /** * Mark message as delivered to customer (after polling retrieval). * * @param int $id Queue record ID. * @return bool True on success. */ public function markDelivered(int $id): bool { global $wpdb; $result = $wpdb->update( $this->table_name, [ 'status' => 'delivered', 'updated_at' => current_time('mysql'), ], ['id' => $id], ['%s', '%s'], ['%d'] ); if ($result !== false) { /** * Action fired when a message is delivered to customer. * * @since 2.5.0 * @param int $id The queue record ID. */ do_action('aipt_chatbot_message_delivered', $id); } return $result !== false; } /** * Bulk delete messages by IDs. * * @param array $ids Array of queue record IDs. * @return int Number of deleted records. */ public function bulkDelete(array $ids): int { global $wpdb; if (empty($ids)) { return 0; } $ids = array_map('absint', $ids); $ids = array_filter($ids); if (empty($ids)) { return 0; } $placeholders = implode(',', array_fill(0, count($ids), '%d')); $sql = "DELETE FROM {$this->table_name} WHERE id IN ({$placeholders})"; $result = $wpdb->query($wpdb->prepare($sql, ...$ids)); return $result !== false ? (int) $result : 0; } /** * Bulk update status for messages by IDs. * * @param array $ids Array of queue record IDs. * @param string $status New status value. * @return int Number of updated records. */ public function bulkUpdateStatus(array $ids, string $status): int { global $wpdb; if (empty($ids)) { return 0; } $valid_statuses = ['queued', 'responded', 'retried', 'failed', 'delivered', 'info']; if (!in_array($status, $valid_statuses, true)) { return 0; } $ids = array_map('absint', $ids); $ids = array_filter($ids); if (empty($ids)) { return 0; } $placeholders = implode(',', array_fill(0, count($ids), '%d')); $sql = "UPDATE {$this->table_name} SET status = %s, updated_at = %s WHERE id IN ({$placeholders})"; $result = $wpdb->query($wpdb->prepare($sql, $status, current_time('mysql'), ...$ids)); return $result !== false ? (int) $result : 0; } /** * Get all messages with optional filters for admin panel. * * Supports filtering by status, record_type, and search query. * Also supports searching by user_name (plaintext). * * @param array $args Query arguments. * @return array{messages: array, total: int, pages: int} */ public function getAllMessages(array $args = []): array { global $wpdb; $defaults = [ 'status' => '', 'record_type' => '', 'search' => '', 'page' => 1, 'per_page' => 20, ]; $args = wp_parse_args($args, $defaults); $page = max(1, (int) $args['page']); $per_page = min(ChatbotConstants::maxPaginationPerPage(), max(1, (int) $args['per_page'])); $offset = ($page - 1) * $per_page; $where_clauses = []; $prepare_values = []; $valid_statuses = ['queued', 'responded', 'retried', 'failed', 'delivered', 'info']; if (!empty($args['status']) && in_array($args['status'], $valid_statuses, true)) { $where_clauses[] = 'status = %s'; $prepare_values[] = $args['status']; } $valid_record_types = ['first_contact', 'error', 'conversation']; if (!empty($args['record_type']) && in_array($args['record_type'], $valid_record_types, true)) { $where_clauses[] = 'record_type = %s'; $prepare_values[] = $args['record_type']; } if (!empty($args['search'])) { $search = '%' . $wpdb->esc_like(sanitize_text_field($args['search'])) . '%'; $where_clauses[] = '(message LIKE %s OR admin_response LIKE %s OR session_id LIKE %s OR user_name LIKE %s)'; $prepare_values[] = $search; $prepare_values[] = $search; $prepare_values[] = $search; $prepare_values[] = $search; } $where_sql = !empty($where_clauses) ? 'WHERE ' . implode(' AND ', $where_clauses) : ''; $count_sql = "SELECT COUNT(*) FROM {$this->table_name} {$where_sql}"; if (!empty($prepare_values)) { $total = (int) $wpdb->get_var($wpdb->prepare($count_sql, ...$prepare_values)); } else { $total = (int) $wpdb->get_var($count_sql); } $prepare_values_for_query = $prepare_values; $prepare_values_for_query[] = $per_page; $prepare_values_for_query[] = $offset; $query = "SELECT * FROM {$this->table_name} {$where_sql} ORDER BY created_at DESC LIMIT %d OFFSET %d"; $messages = $wpdb->get_results($wpdb->prepare($query, ...$prepare_values_for_query), ARRAY_A); if ( ! is_array( $messages ) ) { $db_error = $wpdb->last_error; if ( $db_error ) { throw new \RuntimeException( esc_html( sprintf( 'MessageQueue::get_messages DB error: %s', $db_error ) ) ); } $messages = array(); } return [ 'messages' => $messages, 'total' => $total, 'pages' => (int) ceil($total / $per_page), ]; } }