perf: aggregate wp_get_presence_summary() in SQL

[i-am-chitti]

Related: #205

Rewrites wp_get_presence_summary() to aggregate in SQL instead of pulling every unexpired row into PHP. Query count now scales with room/prefix count, not concurrent users.

• GROUP BY room for entry counts instead of one row per entry
• COUNT(DISTINCT user_id) for the site-wide total
• one COUNT(DISTINCT user_id) ... room IN (...) per prefix, since distinct users don't sum across rooms
• stays portable: no SUBSTRING_INDEX(), prefix grouping stays PHP-side

Regression test added asserting query count doesn't grow with entry count.

Partially addresses #205. wp_get_active_rooms() and the REST rooms endpoint have the same shape and need a separate pass. Following up on the issue.

Use of AI Tools

AI assistance: Yes
Tool(s): Claude Code
Model(s): Claude Sonnet 5
Used for: Investigation, implementation, and tests

[github-actions]

The following accounts have interacted with this PR and/or linked issues. I will continue to update these lists as activity occurs. You can also manually ask me to refresh this list by adding the props-bot label.

Core Committers: Use this line as a base for the props when committing in SVN:

Props iamchitti, joefusco.

To understand the WordPress project's expectations around crediting contributors, please review the Contributor Attribution page in the Core Handbook.

[github-actions]

▶ Preview in WordPress Playground

Boots a fresh WordPress with this PR's presence-api build, seeds 5 demo users, and drops you on the dashboard.

_{Stress-test variant: 40 demo users · Built from 714ea72ff6945ebc4b5db3e5d3e5be55293e280c. Auto-updates when you push.}

[codecov]

Codecov Report

✅ All modified and coverable lines are covered by tests.
✅ Project coverage is 75.93%. Comparing base (8fdedf8) to head (714ea72).
⚠️ Report is 19 commits behind head on main.

Additional details and impacted files

@@             Coverage Diff              @@
##               main     #277      +/-   ##
============================================
+ Coverage     75.87%   75.93%   +0.06%     
  Complexity      176      176              
============================================
  Files            14       14              
  Lines          1832     1845      +13     
============================================
+ Hits           1390     1401      +11     
- Misses          442      444       +2     

Flag	Coverage Δ
multisite	75.93% <100.00%> (+0.06%)	⬆️
phpunit	74.63% <100.00%> (+0.07%)	⬆️

Flags with carried forward coverage won't be shown. Click here to find out more.

🚀 New features to boost your workflow:

• ❄️ Test Analytics: Detect flaky tests, report on failures, and find test suite problems.
• 📦 JS Bundle Analysis: Save yourself from yourself by tracking and limiting bundle sizes in JS merges.

[josephfusco]

Going to release 0.1.19 next, and save this for the following release

[josephfusco]

Pushed a fix for the regression test. The original assertion compared num_queries before and after adding entries to existing rooms, but the pre-fix version already issued exactly one query regardless of entry count, so that comparison passes against either implementation and doesn't actually catch a revert. I checked this by reverting just includes/functions.php locally and rerunning the test.

Replaced it with a check on query shape instead: capture the SQL via the query filter and assert every query touching the presence table uses GROUP BY or COUNT(), which is the actual thing that changed. Confirmed it fails against the old implementation and passes against this one.

Everything else here looks solid, the cross-room distinct-user dedup case (the trickiest part of this rewrite) is already covered by the existing test_get_presence_summary test and passes.