__( 'AI Product Tools chatbot conversations', 'ai-product-tools' ), 'callback' => [ self::class, 'export' ], ]; return $exporters; } /** * @param array $erasers Registered erasers. * @return array */ public static function registerEraser( $erasers ): array { $erasers = is_array( $erasers ) ? $erasers : []; $erasers['ai-product-tools-chatbot'] = [ 'eraser_friendly_name' => __( 'AI Product Tools chatbot conversations', 'ai-product-tools' ), 'callback' => [ self::class, 'erase' ], ]; return $erasers; } /** * Export the chatbot records stored for an email address. * * @param string $email_address Visitor email. * @param int $page 1-based page. * @return array{data: array, done: bool} */ public static function export( $email_address, $page = 1 ): array { global $wpdb; $email = self::normalizeEmail( $email_address ); $page = max( 1, (int) $page ); if ( '' === $email ) { return [ 'data' => [], 'done' => true ]; } $offset = ( $page - 1 ) * self::BATCH_SIZE; $messages = $wpdb->prefix . 'aipt_chatbot_messages'; $jobs = $wpdb->prefix . 'aipt_chatbot_jobs'; $queue = new MessageQueue(); $items = []; $first_ids = []; $message_rows = $wpdb->get_results( $wpdb->prepare( "SELECT id, session_id, user_name, user_email, message, ai_response, admin_response, consent_timestamp, created_at FROM {$messages} WHERE user_email IS NOT NULL ORDER BY id ASC LIMIT %d OFFSET %d", self::BATCH_SIZE, $offset ), ARRAY_A ); $job_rows = $wpdb->get_results( $wpdb->prepare( "SELECT id, session_id, payload, response, created_at FROM {$jobs} WHERE payload LIKE %s ORDER BY id ASC LIMIT %d OFFSET %d", self::payloadLikePattern( $email ), self::BATCH_SIZE, $offset ), ARRAY_A ); $message_rows = is_array( $message_rows ) ? $message_rows : []; $job_rows = is_array( $job_rows ) ? $job_rows : []; foreach ( $message_rows as $row ) { if ( self::normalizeEmail( $queue->decryptEmail( (string) $row['user_email'] ) ) !== $email ) { continue; } $session_id = (string) $row['session_id']; if ( ! isset( $first_ids[ $session_id ] ) ) { $first_ids[ $session_id ] = (int) $row['id']; } $items[] = self::messageItem( $row, $email ); } foreach ( self::emaillessSessionRows( $first_ids, $messages ) as $row ) { $items[] = self::messageItem( $row, '' ); } foreach ( $job_rows as $row ) { $payload = json_decode( (string) $row['payload'], true ); if ( ! is_array( $payload ) || self::normalizeEmail( $payload['user_email'] ?? '' ) !== $email ) { continue; } $items[] = [ 'group_id' => 'aipt-chatbot-jobs', 'group_label' => __( 'Chatbot requests', 'ai-product-tools' ), 'group_description' => __( 'Chat requests processed in the background for the store chatbot.', 'ai-product-tools' ), 'item_id' => 'aipt-chatbot-job-' . (int) $row['id'], 'data' => [ [ 'name' => __( 'Name', 'ai-product-tools' ), 'value' => (string) ( $payload['user_name'] ?? '' ) ], [ 'name' => __( 'Email', 'ai-product-tools' ), 'value' => $email ], [ 'name' => __( 'Message', 'ai-product-tools' ), 'value' => (string) ( $payload['message'] ?? '' ) ], [ 'name' => __( 'Reply', 'ai-product-tools' ), 'value' => self::replyText( $row['response'] ) ], [ 'name' => __( 'Session', 'ai-product-tools' ), 'value' => (string) $row['session_id'] ], [ 'name' => __( 'Date', 'ai-product-tools' ), 'value' => (string) $row['created_at'] ], ], ]; } return [ 'data' => $items, 'done' => count( $message_rows ) < self::BATCH_SIZE && count( $job_rows ) < self::BATCH_SIZE, ]; } /** * Erase the chatbot records stored for an email address. * * Each call scans at most ERASE_SCAN_BATCH message rows after a cursor kept * in a transient and deletes the sessions it matched, so decrypting a large * table never has to fit in one request. The cursor is a row id, so the * deletions do not shift the next page. Page 1 restarts the scan and also * matches the job payloads, which need no decryption. * * @param string $email_address Visitor email. * @param int $page 1-based page. * @return array{items_removed: bool, items_retained: bool, messages: array, done: bool} */ public static function erase( $email_address, $page = 1 ): array { global $wpdb; $result = [ 'items_removed' => false, 'items_retained' => false, 'messages' => [], 'done' => true, ]; $email = self::normalizeEmail( $email_address ); if ( '' === $email ) { return $result; } $page = max( 1, (int) $page ); $cursor_key = self::ERASE_CURSOR_PREFIX . md5( $email ); $last_id = 1 === $page ? 0 : (int) get_transient( $cursor_key ); $messages = $wpdb->prefix . 'aipt_chatbot_messages'; $jobs = $wpdb->prefix . 'aipt_chatbot_jobs'; $queue = new MessageQueue(); $sessions = []; $rows = $wpdb->get_results( $wpdb->prepare( "SELECT id, session_id, user_email FROM {$messages} WHERE user_email IS NOT NULL AND id > %d ORDER BY id ASC LIMIT %d", $last_id, self::ERASE_SCAN_BATCH ), ARRAY_A ); $rows = is_array( $rows ) ? $rows : []; foreach ( $rows as $row ) { $last_id = (int) $row['id']; if ( self::normalizeEmail( $queue->decryptEmail( (string) $row['user_email'] ) ) === $email ) { $sessions[ (string) $row['session_id'] ] = true; } } if ( 1 === $page ) { $job_rows = $wpdb->get_results( $wpdb->prepare( "SELECT session_id, payload FROM {$jobs} WHERE payload LIKE %s", self::payloadLikePattern( $email ) ), ARRAY_A ); foreach ( is_array( $job_rows ) ? $job_rows : [] as $row ) { $payload = json_decode( (string) $row['payload'], true ); if ( is_array( $payload ) && self::normalizeEmail( $payload['user_email'] ?? '' ) === $email ) { $sessions[ (string) $row['session_id'] ] = true; } } } $session_ids = array_keys( $sessions ); $removed = 0; if ( ! empty( $session_ids ) ) { $placeholders = implode( ',', array_fill( 0, count( $session_ids ), '%s' ) ); foreach ( [ $messages, $jobs ] as $table ) { $deleted = $wpdb->query( $wpdb->prepare( "DELETE FROM {$table} WHERE session_id IN ({$placeholders})", $session_ids ) ); if ( false === $deleted ) { $result['items_retained'] = true; $result['messages'][] = __( 'Some chatbot records could not be deleted.', 'ai-product-tools' ); continue; } $removed += (int) $deleted; } } $result['items_removed'] = $removed > 0; $result['done'] = count( $rows ) < self::ERASE_SCAN_BATCH; if ( $result['done'] ) { delete_transient( $cursor_key ); } else { set_transient( $cursor_key, $last_id, self::ERASE_CURSOR_TTL ); } return $result; } /** * Export item for one stored chat message. * * @param array $row Message row. * @param string $email Matched email; empty for rows that carry none. */ private static function messageItem( array $row, string $email ): array { return [ 'group_id' => 'aipt-chatbot-messages', 'group_label' => __( 'Chatbot messages', 'ai-product-tools' ), 'group_description' => __( 'Messages and contact details a visitor gave to the store chatbot.', 'ai-product-tools' ), 'item_id' => 'aipt-chatbot-message-' . (int) $row['id'], 'data' => [ [ 'name' => __( 'Name', 'ai-product-tools' ), 'value' => (string) $row['user_name'] ], [ 'name' => __( 'Email', 'ai-product-tools' ), 'value' => $email ], [ 'name' => __( 'Message', 'ai-product-tools' ), 'value' => (string) $row['message'] ], [ 'name' => __( 'AI reply', 'ai-product-tools' ), 'value' => (string) $row['ai_response'] ], [ 'name' => __( 'Store reply', 'ai-product-tools' ), 'value' => (string) $row['admin_response'] ], [ 'name' => __( 'Session', 'ai-product-tools' ), 'value' => (string) $row['session_id'] ], [ 'name' => __( 'Consent given', 'ai-product-tools' ), 'value' => self::formatTimestamp( $row['consent_timestamp'] ) ], [ 'name' => __( 'Date', 'ai-product-tools' ), 'value' => (string) $row['created_at'] ], ], ]; } /** * Rows without an email that sit in the same sessions as the matched rows. * * The eraser deletes whole sessions, so the export lists them too. A * session's email-less rows are listed once, on the page that holds its * first row carrying an email. * * @param array $first_ids Session id => id of its first matched row on this page. * @param string $table Messages table name. * @return array[] */ private static function emaillessSessionRows( array $first_ids, string $table ): array { global $wpdb; if ( empty( $first_ids ) ) { return []; } $session_ids = array_keys( $first_ids ); $placeholders = implode( ',', array_fill( 0, count( $session_ids ), '%s' ) ); $firsts = $wpdb->get_results( $wpdb->prepare( "SELECT session_id, MIN(id) AS first_id FROM {$table} WHERE user_email IS NOT NULL AND session_id IN ({$placeholders}) GROUP BY session_id", $session_ids ), ARRAY_A ); $owned = []; foreach ( is_array( $firsts ) ? $firsts : [] as $row ) { $session_id = (string) $row['session_id']; if ( isset( $first_ids[ $session_id ] ) && (int) $row['first_id'] === $first_ids[ $session_id ] ) { $owned[] = $session_id; } } if ( empty( $owned ) ) { return []; } $placeholders = implode( ',', array_fill( 0, count( $owned ), '%s' ) ); $rows = $wpdb->get_results( $wpdb->prepare( "SELECT id, session_id, user_name, message, ai_response, admin_response, consent_timestamp, created_at FROM {$table} WHERE user_email IS NULL AND session_id IN ({$placeholders}) ORDER BY id ASC", $owned ), ARRAY_A ); return is_array( $rows ) ? $rows : []; } /** * @param mixed $email Raw email, or null when decryption failed. */ private static function normalizeEmail( $email ): string { return strtolower( trim( sanitize_email( (string) $email ) ) ); } /** * LIKE pattern that narrows job payloads to those holding the email. * Callers re-check the decoded payload, so this only has to be a * superset of the real matches. */ private static function payloadLikePattern( string $email ): string { global $wpdb; $encoded = trim( (string) wp_json_encode( $email ), '"' ); return '%"user_email":"' . $wpdb->esc_like( $encoded ) . '"%'; } /** * @param mixed $timestamp Unix timestamp from the consent column. */ private static function formatTimestamp( $timestamp ): string { $timestamp = (int) $timestamp; return $timestamp > 0 ? gmdate( 'Y-m-d H:i:s', $timestamp ) . ' UTC' : ''; } /** * @param mixed $response_json Stored job response JSON. */ private static function replyText( $response_json ): string { $response = json_decode( (string) $response_json, true ); $reply = is_array( $response ) && isset( $response['data']['response'] ) ? $response['data']['response'] : ''; return is_scalar( $reply ) ? (string) $reply : ''; } }