| 1 |
<?php |
| 2 |
|
| 3 |
namespace FluentBooking\App\Modules\MCP\Tools; |
| 4 |
|
| 5 |
use FluentBooking\App\Models\CalendarSlot; |
| 6 |
use FluentBooking\App\Modules\MCP\Support\MCPHelper; |
| 7 |
use FluentBooking\App\Modules\MCP\Support\PermissionGate; |
| 8 |
use FluentBooking\App\Services\BookingReportService; |
| 9 |
use FluentBooking\Framework\Support\Arr; |
| 10 |
|
| 11 |
defined('ABSPATH') || exit; |
| 12 |
|
| 13 |
/** |
| 14 |
* One aggregation tool where the 34-tool draft had four. |
| 15 |
* |
| 16 |
* `get-booking-stats`, `get-booking-trend`, `get-top-events` and |
| 17 |
* `get-host-utilization` are all the same query with a different GROUP BY, and |
| 18 |
* shipping them separately would have cost four schemas of resident context to |
| 19 |
* express one idea. A model that understands "group by X, measure Y" can |
| 20 |
* produce all four and the ones nobody thought to name. |
| 21 |
* |
| 22 |
* The response is aggregates only. It never returns booking rows — an agent |
| 23 |
* that wants rows has list-bookings, which is paginated and masks PII. |
| 24 |
*/ |
| 25 |
class ReportTools |
| 26 |
{ |
| 27 |
public static function definitions() |
| 28 |
{ |
| 29 |
return [ |
| 30 |
'fluent-booking/query-bookings' => [ |
| 31 |
'label' => __('Query bookings', 'fluent-booking'), |
| 32 |
'description' => __('Aggregate bookings by one or two dimensions. Answers "how many bookings per host last month", "which event types get cancelled most", "what hours do people book". Returns totals only, never booking rows.', 'fluent-booking'), |
| 33 |
'input_schema' => [ |
| 34 |
'type' => 'object', |
| 35 |
'properties' => [ |
| 36 |
'group_by' => [ |
| 37 |
'type' => 'array', |
| 38 |
'description' => __('One or two dimensions. Omit for a single total over the whole range.', 'fluent-booking'), |
| 39 |
'items' => [ |
| 40 |
'type' => 'string', |
| 41 |
'enum' => array_keys(BookingReportService::dimensions()), |
| 42 |
], |
| 43 |
], |
| 44 |
'metrics' => [ |
| 45 |
'type' => 'array', |
| 46 |
'description' => __('Defaults to count. Rates are fractions of that group\'s bookings, 0 to 1.', 'fluent-booking'), |
| 47 |
'items' => [ |
| 48 |
'type' => 'string', |
| 49 |
'enum' => BookingReportService::metrics(), |
| 50 |
], |
| 51 |
], |
| 52 |
'date_field' => [ |
| 53 |
'type' => 'string', |
| 54 |
'description' => __('Which timestamp the range and the day/month/weekday/hour dimensions read. start_time is when the meeting is; created_at is when it was booked. Defaults to start_time.', 'fluent-booking'), |
| 55 |
'enum' => BookingReportService::dateFields(), |
| 56 |
], |
| 57 |
'from' => [ |
| 58 |
'type' => 'string', |
| 59 |
'description' => __('Y-m-d, inclusive. Defaults to 29 days before to.', 'fluent-booking'), |
| 60 |
], |
| 61 |
'to' => [ |
| 62 |
'type' => 'string', |
| 63 |
'description' => __('Y-m-d, inclusive. Defaults to today. Maximum span 366 days.', 'fluent-booking'), |
| 64 |
], |
| 65 |
'timezone' => [ |
| 66 |
'type' => 'string', |
| 67 |
'description' => __('IANA zone the day, weekday and hour buckets are expressed in. Defaults to the site timezone.', 'fluent-booking'), |
| 68 |
], |
| 69 |
'filters' => [ |
| 70 |
'type' => 'object', |
| 71 |
'description' => __('Narrow the set before grouping. Any of: status (array), event_id, calendar_id, host_id, event_type, source.', 'fluent-booking'), |
| 72 |
], |
| 73 |
'having' => [ |
| 74 |
'type' => 'object', |
| 75 |
'description' => __('Drop small groups, e.g. {"metric":"count","op":">=","value":5}.', 'fluent-booking'), |
| 76 |
], |
| 77 |
'order_by' => [ |
| 78 |
'type' => 'string', |
| 79 |
'description' => __('A metric or a grouped dimension. Time series default to chronological, everything else to largest first.', 'fluent-booking'), |
| 80 |
], |
| 81 |
'order' => [ |
| 82 |
'type' => 'string', |
| 83 |
'enum' => ['asc', 'desc'], |
| 84 |
], |
| 85 |
'limit' => [ |
| 86 |
'type' => 'integer', |
| 87 |
'description' => __('Groups to return. Default 50, maximum 200.', 'fluent-booking'), |
| 88 |
], |
| 89 |
], |
| 90 |
], |
| 91 |
'annotations' => [ |
| 92 |
'title' => __('Query bookings', 'fluent-booking'), |
| 93 |
'readonly' => true, |
| 94 |
], |
| 95 |
'permission_callback' => [PermissionGate::class, 'readGate'], |
| 96 |
'execute_callback' => [self::class, 'queryBookings'], |
| 97 |
], |
| 98 |
]; |
| 99 |
} |
| 100 |
|
| 101 |
/** |
| 102 |
* @param array $params |
| 103 |
* @return array|\WP_Error |
| 104 |
*/ |
| 105 |
public static function queryBookings($params = []) |
| 106 |
{ |
| 107 |
$timezone = MCPHelper::resolveTimezone(Arr::get($params, 'timezone', '')); |
| 108 |
|
| 109 |
$result = BookingReportService::aggregate([ |
| 110 |
'group_by' => Arr::get($params, 'group_by', []), |
| 111 |
'metrics' => Arr::get($params, 'metrics', []), |
| 112 |
'date_field' => Arr::get($params, 'date_field', 'start_time'), |
| 113 |
'from' => Arr::get($params, 'from'), |
| 114 |
'to' => Arr::get($params, 'to'), |
| 115 |
'timezone' => $timezone, |
| 116 |
'filters' => Arr::get($params, 'filters', []), |
| 117 |
'having' => Arr::get($params, 'having'), |
| 118 |
'order_by' => Arr::get($params, 'order_by', ''), |
| 119 |
'order' => Arr::get($params, 'order', ''), |
| 120 |
'limit' => Arr::get($params, 'limit', 50), |
| 121 |
]); |
| 122 |
|
| 123 |
if (is_wp_error($result)) { |
| 124 |
return MCPHelper::error($result->get_error_code(), $result->get_error_message()); |
| 125 |
} |
| 126 |
|
| 127 |
$rows = self::labelRows($result['rows'], $result['group_by']); |
| 128 |
|
| 129 |
$meta = [ |
| 130 |
'date_field' => $result['date_field'], |
| 131 |
'from' => $result['range']['from'], |
| 132 |
'to' => $result['range']['to'], |
| 133 |
'days' => $result['range']['days'], |
| 134 |
'group_count' => count($rows), |
| 135 |
'group_by' => $result['group_by'], |
| 136 |
'group_by_labels' => self::dimensionLabels($result['group_by']), |
| 137 |
'timezone' => $timezone, |
| 138 |
'scope' => PermissionGate::currentScope(), |
| 139 |
]; |
| 140 |
|
| 141 |
if ($result['truncated']) { |
| 142 |
$meta['truncated'] = true; |
| 143 |
$meta['truncation_note'] = sprintf( |
| 144 |
/* translators: %d: the number of groups returned */ |
| 145 |
__('More groups matched than the limit of %d. Raise limit, add a having filter, or narrow the range.', 'fluent-booking'), |
| 146 |
$result['limit'] |
| 147 |
); |
| 148 |
} |
| 149 |
|
| 150 |
// Time buckets were shifted by a fixed offset, so say which one. An |
| 151 |
// agent reporting "most bookings at 9am" needs to know whose 9am. |
| 152 |
if (self::hasTimeDimension($result['group_by'])) { |
| 153 |
$offset = BookingReportService::offsetSeconds($timezone, $result['range']['from']); |
| 154 |
$meta['bucket_offset'] = sprintf('%s%02d:%02d', $offset < 0 ? '-' : '+', abs($offset) / 3600, (abs($offset) % 3600) / 60); |
| 155 |
$meta['bucket_note'] = __('Day, weekday and hour buckets use one fixed offset for the whole range. A range crossing a daylight-saving change can place bookings on the far side an hour out.', 'fluent-booking'); |
| 156 |
} |
| 157 |
|
| 158 |
return MCPHelper::success( |
| 159 |
[ |
| 160 |
'rows' => $rows, |
| 161 |
'totals' => self::totals($result['rows'], $result['metrics']), |
| 162 |
], |
| 163 |
$meta, |
| 164 |
$rows ? '' : 'No bookings matched. Widen the range, or drop a filter.' |
| 165 |
); |
| 166 |
} |
| 167 |
|
| 168 |
/** |
| 169 |
* Ids are not answers. A report grouped by host or event type resolves them |
| 170 |
* to names here, in one query per dimension, so the agent does not have to |
| 171 |
* spend a round-trip per row working out what "event 7" is. |
| 172 |
* |
| 173 |
* @return array |
| 174 |
*/ |
| 175 |
/** |
| 176 |
* Human names for the dimensions grouped on, so an agent rendering a table |
| 177 |
* does not have to invent a header for `event_type`. |
| 178 |
* |
| 179 |
* @return array |
| 180 |
*/ |
| 181 |
private static function dimensionLabels($groupBy) |
| 182 |
{ |
| 183 |
$dimensions = BookingReportService::dimensions(); |
| 184 |
$labels = []; |
| 185 |
|
| 186 |
foreach ((array) $groupBy as $dimension) { |
| 187 |
if (isset($dimensions[$dimension]['label'])) { |
| 188 |
$labels[$dimension] = $dimensions[$dimension]['label']; |
| 189 |
} |
| 190 |
} |
| 191 |
|
| 192 |
return $labels; |
| 193 |
} |
| 194 |
|
| 195 |
private static function labelRows($rows, $groupBy) |
| 196 |
{ |
| 197 |
if (!$rows) { |
| 198 |
return $rows; |
| 199 |
} |
| 200 |
|
| 201 |
$labels = []; |
| 202 |
|
| 203 |
foreach ($groupBy as $dimension) { |
| 204 |
if ($dimension === 'event') { |
| 205 |
$ids = array_unique(array_filter(array_column($rows, 'event'))); |
| 206 |
$labels['event'] = $ids |
| 207 |
? CalendarSlot::whereIn('id', $ids)->pluck('title', 'id')->toArray() |
| 208 |
: []; |
| 209 |
} |
| 210 |
|
| 211 |
if ($dimension === 'host') { |
| 212 |
$ids = array_unique(array_filter(array_column($rows, 'host'))); |
| 213 |
|
| 214 |
// One query for the lot rather than one per host: a report can |
| 215 |
// return up to 200 groups. |
| 216 |
if ($ids) { |
| 217 |
cache_users(array_map('intval', $ids)); |
| 218 |
} |
| 219 |
|
| 220 |
foreach ($ids as $id) { |
| 221 |
$user = get_userdata($id); |
| 222 |
$labels['host'][$id] = $user ? MCPHelper::untrusted($user->display_name, 200) : sprintf('#%d', $id); |
| 223 |
} |
| 224 |
} |
| 225 |
|
| 226 |
if ($dimension === 'weekday') { |
| 227 |
// MySQL DAYOFWEEK is 1 = Sunday. |
| 228 |
$labels['weekday'] = [ |
| 229 |
1 => __('Sunday', 'fluent-booking'), |
| 230 |
2 => __('Monday', 'fluent-booking'), |
| 231 |
3 => __('Tuesday', 'fluent-booking'), |
| 232 |
4 => __('Wednesday', 'fluent-booking'), |
| 233 |
5 => __('Thursday', 'fluent-booking'), |
| 234 |
6 => __('Friday', 'fluent-booking'), |
| 235 |
7 => __('Saturday', 'fluent-booking'), |
| 236 |
]; |
| 237 |
} |
| 238 |
} |
| 239 |
|
| 240 |
if (!$labels) { |
| 241 |
return $rows; |
| 242 |
} |
| 243 |
|
| 244 |
foreach ($rows as &$row) { |
| 245 |
foreach ($labels as $dimension => $map) { |
| 246 |
if (isset($row[$dimension]) && isset($map[$row[$dimension]])) { |
| 247 |
$row[$dimension . '_label'] = $map[$row[$dimension]]; |
| 248 |
} |
| 249 |
} |
| 250 |
} |
| 251 |
|
| 252 |
return $rows; |
| 253 |
} |
| 254 |
|
| 255 |
/** |
| 256 |
* Column totals, so an agent does not have to sum the rows itself and get |
| 257 |
* it wrong. Rates are omitted: averaging per-group rates is not the rate |
| 258 |
* over the whole set, and computing the real one would need the numerators |
| 259 |
* this response does not carry. |
| 260 |
* |
| 261 |
* @return array |
| 262 |
*/ |
| 263 |
private static function totals($rows, $metrics) |
| 264 |
{ |
| 265 |
$totals = []; |
| 266 |
|
| 267 |
foreach (['count', 'distinct_attendees', 'total_minutes'] as $metric) { |
| 268 |
if (in_array($metric, $metrics, true)) { |
| 269 |
$totals[$metric] = array_sum(array_column($rows, $metric)); |
| 270 |
} |
| 271 |
} |
| 272 |
|
| 273 |
// distinct_attendees cannot be summed across groups without |
| 274 |
// double-counting anyone who appears in two of them. |
| 275 |
if (isset($totals['distinct_attendees'])) { |
| 276 |
$totals['distinct_attendees_note'] = __('Summed across groups, so an attendee in two groups is counted twice.', 'fluent-booking'); |
| 277 |
} |
| 278 |
|
| 279 |
return $totals; |
| 280 |
} |
| 281 |
|
| 282 |
/** |
| 283 |
* @return bool |
| 284 |
*/ |
| 285 |
private static function hasTimeDimension($groupBy) |
| 286 |
{ |
| 287 |
return (bool) array_intersect((array) $groupBy, ['day', 'month', 'weekday', 'hour']); |
| 288 |
} |
| 289 |
} |
| 290 |
|
