WP_REST_Server::READABLE, 'callback' => array( $this, 'get_stats' ), 'permission_callback' => array( $this, 'can_view_stats' ), ) ); } /** * Only site admins (incl. Application Password auth) may read stats. * * @since 1.5.0 * @return bool */ public function can_view_stats() { return current_user_can( 'manage_options' ); } /** * Aggregate chatbot statistics. * * Response shape is consumed by the workspace dashboard's ChatbotStats * type — keep key names in sync with dashboard-stats.ts. * * @since 1.5.0 * @return WP_REST_Response */ public function get_stats() { global $wpdb; $chatbots = $wpdb->prefix . 'a1ai_chatbots'; $conversations = $wpdb->prefix . 'a1ai_conversations'; $messages = $wpdb->prefix . 'a1ai_messages'; // Aggregate COUNT queries over plugin-owned tables; no user input involved. // phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared $total_chatbots = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$chatbots}" ); // "Active" = chatbots that had at least one conversation in the last 30 days. $active_chatbots = (int) $wpdb->get_var( "SELECT COUNT(DISTINCT chatbot_id) FROM {$conversations} WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)" ); $conversations_total = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$conversations}" ); $conversations_7days = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$conversations} WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)" ); $conversations_30days = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$conversations} WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)" ); $messages_total = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$messages}" ); $messages_7days = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$messages} WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)" ); $last_conversation = $wpdb->get_var( "SELECT MAX(created_at) FROM {$conversations}" ); // phpcs:enable return rest_ensure_response( array( 'chatbots' => array( 'total' => $total_chatbots, 'active' => $active_chatbots, ), 'conversations' => array( 'total' => $conversations_total, 'last7Days' => $conversations_7days, 'last30Days' => $conversations_30days, ), 'messages' => array( 'total' => $messages_total, 'last7Days' => $messages_7days, ), 'lastConversationAt' => $last_conversation ? mysql2date( 'c', $last_conversation, false ) : null, ) ); } }