| @@ -3,8 +3,9 @@ | ||
| 3 | 3 | namespace FluentCart\App\Modules\MCP\Tools; |
| 4 | 4 | |
| 5 | 5 | use FluentCart\App\Helpers\Helper; |
| 6 | 6 | use FluentCart\App\Models\Customer; |
| 7 | +use FluentCart\App\Modules\MCP\Support\AdvancedSearch; | |
| 7 | 8 | use FluentCart\App\Modules\MCP\Support\MCPHelper; |
| 8 | 9 | use FluentCart\App\Modules\MCP\Support\PermissionGate; |
| 9 | 10 | use FluentCart\Api\Resource\CustomerResource; |
| 10 | 11 | |
| @@ -27,22 +28,23 @@ | ||
| 27 | 28 | { |
| 28 | 29 | return [ |
| 29 | 30 | 'fluent-cart/list-customers' => [ |
| 30 | 31 | 'label' => __('List Customers', 'fluent-cart'), |
| 31 | - 'description' => __('Find and filter customers. Compact rows with LTV, order count, AOV, and location. For one customer\'s full history use get-customer. min_ltv is in store currency (e.g. 500), not cents.', 'fluent-cart'), | |
| 32 | + 'description' => __('Find and filter customers. Compact rows with LTV, order count, AOV, and location. For one customer\'s full history use get-customer. min_ltv is in store currency (e.g. 500), not cents. For conditions these flat filters cannot express (OR groups, buyers of a specific product/variation, relative purchase-date windows, labels) pass advanced_filters — call get-search-schema entity=customers first (Pro).', 'fluent-cart'), | |
| 32 | 33 | 'input_schema' => [ |
| 33 | 34 | 'type' => 'object', |
| 34 | 35 | 'properties' => [ |
| 35 | 36 | 'search' => ['type' => 'string', 'description' => 'Matches name or email.'], |
| 36 | 37 | 'status' => ['type' => 'string', 'enum' => ['active', 'archived']], |
| 37 | - 'country' => ['type' => 'string', 'description' => 'ISO-2 country code.'], | |
| 38 | - 'state' => ['type' => 'string'], | |
| 39 | - 'city' => ['type' => 'string'], | |
| 38 | + 'country' => ['type' => 'string', 'description' => 'ISO-2 country code. Matches the customer PROFILE country, which is captured once when the customer record is created and never refreshed — it can differ from the billing address (see location_source on each row). For geography taken from the address on the order, filter list-orders by country instead.'], | |
| 39 | + 'state' => ['type' => 'string', 'description' => 'Customer profile state — same caveat as country.'], | |
| 40 | + 'city' => ['type' => 'string', 'description' => 'Customer profile city — same caveat as country.'], | |
| 40 | 41 | 'min_ltv' => ['type' => 'number', 'description' => 'Minimum lifetime value in store currency.'], |
| 41 | 42 | 'min_purchase_count' => ['type' => 'integer'], |
| 42 | 43 | 'first_purchase_after' => ['type' => 'string', 'description' => 'YYYY-MM-DD or ISO 8601, UTC.'], |
| 43 | 44 | 'last_purchase_after' => ['type' => 'string', 'description' => 'YYYY-MM-DD or ISO 8601, UTC.'], |
| 44 | 45 | 'last_purchase_before' => ['type' => 'string', 'description' => 'YYYY-MM-DD or ISO 8601, UTC.'], |
| 46 | + 'advanced_filters' => ['type' => 'array', 'items' => ['type' => ['object', 'array']], 'description' => 'Pro: condition groups {property, operator, value} — outer array = OR groups, inner = AND. Call get-search-schema entity=customers FIRST for properties/operators/format. AND-combines with the other filters here. An empty array means no advanced filter.'], | |
| 45 | 47 | 'sort_by' => ['type' => 'string', 'enum' => ['id', 'ltv', 'purchase_count', 'last_purchase_date', 'created_at'], 'default' => 'ltv'], |
| 46 | 48 | 'sort_type' => ['type' => 'string', 'enum' => ['ASC', 'DESC'], 'default' => 'DESC'], |
| 47 | 49 | 'page' => ['type' => 'integer', 'default' => 1], |
| 48 | 50 | 'per_page' => ['type' => 'integer', 'default' => 15, 'description' => 'Max 100.'], |
| @@ -56,9 +58,13 @@ | ||
| 56 | 58 | ], |
| 57 | 59 | |
| 58 | 60 | 'fluent-cart/get-customer' => [ |
| 59 | 61 | 'label' => __('Get Customer', 'fluent-cart'), |
| 60 | - 'description' => __('Full profile + metrics for one customer. Identify by customer_id OR email. Add include[] for orders, subscriptions, addresses, labels, notes. Use with_orders_limit to bound order history.', 'fluent-cart'), | |
| 62 | + 'description' => sprintf( | |
| 63 | + /* translators: %1$s: comma-separated include[] section names */ | |
| 64 | + __('Full profile + metrics for one customer. Identify by customer_id OR email. Add include[] for any of: %1$s (orders carry their line items: product id, title, quantity; subscriptions carry plan_type so installment plans are distinguishable from recurring ones). Use with_orders_limit to bound order history.', 'fluent-cart'), | |
| 65 | + implode(', ', self::includeSections()) | |
| 66 | + ), | |
| 61 | 67 | 'input_schema' => [ |
| 62 | 68 | 'type' => 'object', |
| 63 | 69 | 'properties' => [ |
| 64 | 70 | 'customer_id' => ['type' => 'integer'], |
| @@ -64,9 +70,9 @@ | ||
| 64 | 70 | 'customer_id' => ['type' => 'integer'], |
| 65 | 71 | 'email' => ['type' => 'string'], |
| 66 | 72 | 'include' => [ |
| 67 | 73 | 'type' => 'array', |
| 68 | - 'items' => ['type' => 'string', 'enum' => ['orders', 'subscriptions', 'addresses', 'labels', 'notes']], | |
| 74 | + 'items' => ['type' => 'string', 'enum' => self::includeSections()], | |
| 69 | 75 | 'description' => 'Optional sections. Profile + metrics are always returned.', |
| 70 | 76 | ], |
| 71 | 77 | 'with_orders_limit' => ['type' => 'integer', 'default' => 10, 'description' => 'Cap on orders when include has orders. Max 50.'], |
| 72 | 78 | ], |
| @@ -100,12 +106,44 @@ | ||
| 100 | 106 | 'execute_callback' => [self::class, 'upsertCustomer'], |
| 101 | 107 | 'permission_callback' => function () { |
| 102 | 108 | return PermissionGate::can('customers/manage'); |
| 103 | 109 | }, |
| 110 | + // Upsert by id/email with if_exists — repeating the same call | |
| 111 | + // converges to the same record (idempotent). Archives rather than | |
| 112 | + // hard-deletes, so not destructive. | |
| 113 | + 'annotations' => ['readonly' => false, 'destructive' => false, 'idempotent' => true], | |
| 104 | 114 | ], |
| 105 | 115 | ]; |
| 106 | 116 | } |
| 107 | 117 | |
| 118 | + /** | |
| 119 | + * The sections get-customer's include[] accepts. Filterable so an add-on that | |
| 120 | + * owns its own customer-scoped entity (Pro's licences, for one) can offer it | |
| 121 | + * as an include instead of forcing the agent into a second list-* call and a | |
| 122 | + * manual join by customer. | |
| 123 | + * | |
| 124 | + * A section added here MUST be populated by a listener on | |
| 125 | + * fluent_cart/mcp_customer_data — an include the schema advertises but | |
| 126 | + * nothing fills is worse than no include at all. | |
| 127 | + * | |
| 128 | + * @return array | |
| 129 | + */ | |
| 130 | + private static function includeSections() | |
| 131 | + { | |
| 132 | + $sections = ['orders', 'subscriptions', 'addresses', 'labels', 'notes']; | |
| 133 | + | |
| 134 | + /** | |
| 135 | + * Extra include[] section names for get-customer. | |
| 136 | + * | |
| 137 | + * @since 1.0.0 | |
| 138 | + * | |
| 139 | + * @param array $sections section names offered in the include[] enum | |
| 140 | + */ | |
| 141 | + $sections = apply_filters('fluent_cart/mcp_customer_include_sections', $sections); | |
| 142 | + | |
| 143 | + return array_values(array_unique(array_map('strval', (array) $sections))); | |
| 144 | + } | |
| 145 | + | |
| 108 | 146 | public static function upsertCustomer($params = []) |
| 109 | 147 | { |
| 110 | 148 | $ifExists = (isset($params['if_exists']) && in_array($params['if_exists'], ['merge', 'skip', 'error'], true)) ? $params['if_exists'] : 'merge'; |
| 111 | 149 | |
| @@ -211,10 +249,24 @@ | ||
| 211 | 249 | |
| 212 | 250 | public static function listCustomers($params = []) |
| 213 | 251 | { |
| 214 | 252 | $paging = MCPHelper::pagination($params); |
| 215 | - $query = Customer::query(); | |
| 216 | 253 | |
| 254 | + // advanced_filters routes through the admin filter engine (validated | |
| 255 | + // first — a bad condition errors, never silently drops); the named | |
| 256 | + // filters below then AND onto the same query either way. | |
| 257 | + $advWarnings = []; | |
| 258 | + if (!empty($params['advanced_filters'])) { | |
| 259 | + $built = AdvancedSearch::buildQuery('customers', $params['advanced_filters']); | |
| 260 | + if (is_wp_error($built)) { | |
| 261 | + return $built; | |
| 262 | + } | |
| 263 | + $query = $built['query']; | |
| 264 | + $advWarnings = $built['warnings']; | |
| 265 | + } else { | |
| 266 | + $query = Customer::query(); | |
| 267 | + } | |
| 268 | + | |
| 217 | 269 | if (!empty($params['search'])) { |
| 218 | 270 | $like = '%' . sanitize_text_field($params['search']) . '%'; |
| 219 | 271 | $query->where(function ($q) use ($like) { |
| 220 | 272 | $q->where('email', 'LIKE', $like) |
| @@ -257,8 +309,12 @@ | ||
| 257 | 309 | if ($sortBy !== 'id') { |
| 258 | 310 | $query->orderBy('id', 'DESC'); |
| 259 | 311 | } |
| 260 | 312 | |
| 313 | + // Eager-load both address relations the location string reads, so a page | |
| 314 | + // of rows costs two extra queries instead of two per row. | |
| 315 | + $query->with(['primary_billing_address', 'billing_address']); | |
| 316 | + | |
| 261 | 317 | $paginator = $query->paginate($paging['per_page'], ['*'], 'page', $paging['page']); |
| 262 | 318 | $total = self::total($paginator); |
| 263 | 319 | |
| 264 | 320 | $rows = []; |
| @@ -265,8 +321,13 @@ | ||
| 265 | 321 | foreach (MCPHelper::paginatorItems($paginator) as $customer) { |
| 266 | 322 | $rows[] = self::formatRow($customer); |
| 267 | 323 | } |
| 268 | 324 | |
| 325 | + $meta = MCPHelper::pagingMeta($paginator); | |
| 326 | + if ($advWarnings) { | |
| 327 | + $meta['warnings'] = $advWarnings; | |
| 328 | + } | |
| 329 | + | |
| 269 | 330 | return MCPHelper::envelope( |
| 270 | 331 | sprintf( |
| 271 | 332 | /* translators: %d: number of matching customers */ |
| 272 | 333 | _n('%d customer found.', '%d customers found.', $total, 'fluent-cart'), |
| @@ -272,16 +333,17 @@ | ||
| 272 | 333 | _n('%d customer found.', '%d customers found.', $total, 'fluent-cart'), |
| 273 | 334 | $total |
| 274 | 335 | ), |
| 275 | 336 | ['customers' => $rows], |
| 276 | - MCPHelper::pagingMeta($paginator) | |
| 337 | + $meta | |
| 277 | 338 | ); |
| 278 | 339 | } |
| 279 | 340 | |
| 280 | 341 | private static function formatRow($customer) |
| 281 | 342 | { |
| 282 | - $ltv = (int) $customer->ltv; | |
| 283 | - $count = (int) $customer->purchase_count; | |
| 343 | + $ltv = (int) $customer->ltv; | |
| 344 | + $count = (int) $customer->purchase_count; | |
| 345 | + $location = self::locationBlock($customer); | |
| 284 | 346 | |
| 285 | 347 | return [ |
| 286 | 348 | 'customer_id' => (int) $customer->id, |
| 287 | 349 | 'label' => self::label($customer, $ltv), |
| @@ -290,9 +352,10 @@ | ||
| 290 | 352 | 'status' => $customer->status, |
| 291 | 353 | 'ltv' => MCPHelper::moneyCompact($ltv), |
| 292 | 354 | 'purchase_count' => $count, |
| 293 | 355 | 'aov' => MCPHelper::moneyCompact(self::aovCents($ltv, $count)), |
| 294 | - 'location' => self::location($customer), | |
| 356 | + 'location' => $location['location'], | |
| 357 | + 'location_source' => $location['location_source'], | |
| 295 | 358 | 'last_purchase_date' => MCPHelper::toIso8601($customer->last_purchase_date), |
| 296 | 359 | ]; |
| 297 | 360 | } |
| 298 | 361 | |
| @@ -318,19 +381,21 @@ | ||
| 318 | 381 | if (is_wp_error($customer)) { |
| 319 | 382 | return $customer; |
| 320 | 383 | } |
| 321 | 384 | |
| 322 | - $include = isset($params['include']) ? (array) $params['include'] : []; | |
| 323 | - $ltv = (int) $customer->ltv; | |
| 324 | - $count = (int) $customer->purchase_count; | |
| 385 | + $include = isset($params['include']) ? (array) $params['include'] : []; | |
| 386 | + $ltv = (int) $customer->ltv; | |
| 387 | + $count = (int) $customer->purchase_count; | |
| 388 | + $location = self::locationBlock($customer); | |
| 325 | 389 | |
| 326 | 390 | $data = [ |
| 327 | - 'customer_id' => (int) $customer->id, | |
| 328 | - 'name' => MCPHelper::personName($customer), | |
| 329 | - 'email' => $customer->email, | |
| 330 | - 'status' => $customer->status, | |
| 331 | - 'wp_user_id' => $customer->user_id ? (int) $customer->user_id : null, | |
| 332 | - 'location' => self::location($customer), | |
| 391 | + 'customer_id' => (int) $customer->id, | |
| 392 | + 'name' => MCPHelper::personName($customer), | |
| 393 | + 'email' => $customer->email, | |
| 394 | + 'status' => $customer->status, | |
| 395 | + 'wp_user_id' => $customer->user_id ? (int) $customer->user_id : null, | |
| 396 | + 'location' => $location['location'], | |
| 397 | + 'location_source' => $location['location_source'], | |
| 333 | 398 | 'metrics' => [ |
| 334 | 399 | 'ltv' => MCPHelper::money($ltv), |
| 335 | 400 | 'purchase_count' => $count, |
| 336 | 401 | 'aov' => MCPHelper::money(self::aovCents($ltv, $count)), |
| @@ -356,8 +421,23 @@ | ||
| 356 | 421 | if (in_array('notes', $include, true)) { |
| 357 | 422 | $data['notes'] = MCPHelper::htmlToText($customer->notes); |
| 358 | 423 | } |
| 359 | 424 | |
| 425 | + /** | |
| 426 | + * The assembled get-customer payload, for add-on sections registered | |
| 427 | + * through fluent_cart/mcp_customer_include_sections. Listeners should add | |
| 428 | + * their key only when it is present in $context['include']. | |
| 429 | + * | |
| 430 | + * @since 1.0.0 | |
| 431 | + * | |
| 432 | + * @param array $data the customer payload | |
| 433 | + * @param array $context { customer: Customer, include: string[] } | |
| 434 | + */ | |
| 435 | + $data = apply_filters('fluent_cart/mcp_customer_data', $data, [ | |
| 436 | + 'customer' => $customer, | |
| 437 | + 'include' => $include, | |
| 438 | + ]); | |
| 439 | + | |
| 360 | 440 | return MCPHelper::envelope(self::label($customer, $ltv), $data); |
| 361 | 441 | } |
| 362 | 442 | |
| 363 | 443 | private static function resolve($params) |
| @@ -409,17 +489,37 @@ | ||
| 409 | 489 | } |
| 410 | 490 | |
| 411 | 491 | private static function orders($customer, $limit) |
| 412 | 492 | { |
| 413 | - $orders = $customer->orders()->orderBy('id', 'DESC')->limit($limit)->get(); | |
| 493 | + // Trimmed order_items eager load, same as list-orders: without the items | |
| 494 | + // there is no way to tell WHAT the customer bought from this view, which | |
| 495 | + // is the whole point of a customer's order history. | |
| 496 | + $orders = $customer->orders() | |
| 497 | + ->with(['order_items' => function ($q) { | |
| 498 | + $q->select(['id', 'order_id', 'post_id', 'post_title', 'title', 'quantity']); | |
| 499 | + }]) | |
| 500 | + ->orderBy('id', 'DESC')->limit($limit)->get(); | |
| 414 | 501 | $out = []; |
| 415 | 502 | foreach ($orders as $order) { |
| 503 | + $items = []; | |
| 504 | + if ($order->relationLoaded('order_items')) { | |
| 505 | + foreach ($order->order_items as $item) { | |
| 506 | + $items[] = [ | |
| 507 | + 'product_id' => (int) $item->post_id, | |
| 508 | + 'title' => $item->getDisplayTitle(), | |
| 509 | + 'quantity' => (int) $item->quantity, | |
| 510 | + ]; | |
| 511 | + } | |
| 512 | + } | |
| 416 | 513 | $out[] = [ |
| 417 | 514 | 'order_id' => (int) $order->id, |
| 418 | - 'number' => $order->invoice_no ? $order->invoice_no : (string) $order->id, | |
| 515 | + // null until an invoice number is assigned — same contract as | |
| 516 | + // list-orders and get-order. | |
| 517 | + 'number' => $order->invoice_no ? $order->invoice_no : null, | |
| 419 | 518 | 'status' => $order->status, |
| 420 | 519 | 'payment_status' => $order->payment_status, |
| 421 | 520 | 'total' => MCPHelper::moneyCompact($order->total_amount), |
| 521 | + 'items' => $items, | |
| 422 | 522 | 'created_at' => MCPHelper::toIso8601($order->created_at), |
| 423 | 523 | ]; |
| 424 | 524 | } |
| 425 | 525 | return $out; |
| @@ -424,20 +524,34 @@ | ||
| 424 | 524 | } |
| 425 | 525 | return $out; |
| 426 | 526 | } |
| 427 | 527 | |
| 528 | + /** | |
| 529 | + * Nested subscription rows. plan_type and the installment counters are the | |
| 530 | + * same derivation list-subscriptions / get-subscription use (bill_times > 0), | |
| 531 | + * carried here because otherwise the only way to tell a lifetime licence paid | |
| 532 | + * in N installments from a genuine recurring plan on this payload was to | |
| 533 | + * string-match "(Split Pay)" in item_name — the two are structurally | |
| 534 | + * identical without it, and they are opposite kinds of revenue. | |
| 535 | + */ | |
| 428 | 536 | private static function subscriptions($customer) |
| 429 | 537 | { |
| 430 | 538 | $subs = $customer->subscriptions()->orderBy('id', 'DESC')->get(); |
| 431 | 539 | $out = []; |
| 432 | 540 | foreach ($subs as $sub) { |
| 541 | + $isInstallment = $sub->isInstallment(); | |
| 433 | 542 | $out[] = [ |
| 434 | - 'id' => (int) $sub->id, | |
| 435 | - 'status' => $sub->status, | |
| 436 | - 'item_name' => $sub->item_name, | |
| 437 | - 'recurring_total' => MCPHelper::moneyCompact($sub->recurring_total), | |
| 438 | - 'billing_interval' => $sub->billing_interval, | |
| 439 | - 'next_billing_date' => MCPHelper::toIso8601($sub->next_billing_date), | |
| 543 | + 'id' => (int) $sub->id, | |
| 544 | + 'status' => $sub->status, | |
| 545 | + 'item_name' => $sub->item_name, | |
| 546 | + 'plan_type' => $isInstallment ? 'installment' : 'recurring', | |
| 547 | + 'recurring_total' => MCPHelper::moneyCompact($sub->recurring_total), | |
| 548 | + 'billing_interval' => $sub->billing_interval, | |
| 549 | + 'next_billing_date' => MCPHelper::toIso8601($sub->next_billing_date), | |
| 550 | + 'bill_times' => (int) $sub->bill_times, | |
| 551 | + 'installments_paid' => (int) $sub->bill_count, | |
| 552 | + 'installments_remaining' => $sub->installmentsRemaining(), | |
| 553 | + 'total_contract_value' => $isInstallment ? MCPHelper::moneyCompact($sub->totalContractValue()) : null, | |
| 440 | 554 | ]; |
| 441 | 555 | } |
| 442 | 556 | return $out; |
| 443 | 557 | } |
| @@ -454,12 +568,69 @@ | ||
| 454 | 568 | } |
| 455 | 569 | return $out; |
| 456 | 570 | } |
| 457 | 571 | |
| 458 | - private static function location($customer) | |
| 572 | + /** | |
| 573 | + * The convenience location string, derived from the customer's primary | |
| 574 | + * billing address rather than the fct_customers city/state/country columns. | |
| 575 | + * | |
| 576 | + * Those columns are a WRITE-ONCE snapshot: CustomerResource::create() seeds | |
| 577 | + * them when the customer row is first inserted and nothing refreshes them | |
| 578 | + * afterwards (CheckoutApi::updateExistingCustomer() deliberately touches only | |
| 579 | + * user_id), and the value seeded at checkout can be the country the frontend | |
| 580 | + * GUESSED from the browser timezone before the buyer typed anything. The | |
| 581 | + * billing address is the value the buyer actually entered, so it wins; the | |
| 582 | + * profile columns are the fallback for a customer with no address row. | |
| 583 | + * | |
| 584 | + * In practice the two rarely conflict — on the 2,320-customer reference store | |
| 585 | + * they never do (zero rows where both are set and differ). What this ordering | |
| 586 | + * buys is correctness when a stale snapshot DOES diverge, plus coverage: 7 | |
| 587 | + * customers there have an address but no profile value, so reading the profile | |
| 588 | + * alone would report no location for a customer whose address is on file. | |
| 589 | + * location_source names which source produced the string, so an agent seeing | |
| 590 | + * `location` next to an `addresses` include can tell whether they should agree. | |
| 591 | + * | |
| 592 | + * @return array { location: string|null, location_source: string|null } | |
| 593 | + */ | |
| 594 | + private static function locationBlock($customer) | |
| 459 | 595 | { |
| 596 | + $address = self::billingAddress($customer); | |
| 597 | + if ($address) { | |
| 598 | + $parts = array_filter([$address->city, $address->state, $address->country]); | |
| 599 | + if ($parts) { | |
| 600 | + return ['location' => implode(', ', $parts), 'location_source' => 'billing_address']; | |
| 601 | + } | |
| 602 | + } | |
| 603 | + | |
| 460 | 604 | $parts = array_filter([$customer->city, $customer->state, $customer->country]); |
| 461 | - return $parts ? implode(', ', $parts) : null; | |
| 605 | + if ($parts) { | |
| 606 | + return ['location' => implode(', ', $parts), 'location_source' => 'customer_profile']; | |
| 607 | + } | |
| 608 | + | |
| 609 | + return ['location' => null, 'location_source' => null]; | |
| 610 | + } | |
| 611 | + | |
| 612 | + /** | |
| 613 | + * The customer's primary billing address, falling back to any billing address | |
| 614 | + * when none is flagged primary (real stores have both). Relations are checked | |
| 615 | + * before loading so a caller that eager-loaded them (list-customers) pays no | |
| 616 | + * per-row query. | |
| 617 | + */ | |
| 618 | + private static function billingAddress($customer) | |
| 619 | + { | |
| 620 | + if (!$customer->relationLoaded('primary_billing_address')) { | |
| 621 | + $customer->load('primary_billing_address'); | |
| 622 | + } | |
| 623 | + if ($customer->primary_billing_address) { | |
| 624 | + return $customer->primary_billing_address; | |
| 625 | + } | |
| 626 | + | |
| 627 | + if (!$customer->relationLoaded('billing_address')) { | |
| 628 | + $customer->load('billing_address'); | |
| 629 | + } | |
| 630 | + $addresses = $customer->billing_address; | |
| 631 | + | |
| 632 | + return ($addresses && count($addresses)) ? $addresses[0] : null; | |
| 462 | 633 | } |
| 463 | 634 | |
| 464 | 635 | private static function aovCents($ltvCents, $count) |
| 465 | 636 | { |
