| 1 |
<?php |
| 2 |
|
| 3 |
namespace FluentCart\App\Modules\MCP\Tools; |
| 4 |
|
| 5 |
use FluentCart\App\Models\Subscription; |
| 6 |
use FluentCart\App\Models\OrderTransaction; |
| 7 |
use FluentCart\App\Modules\MCP\Support\MCPHelper; |
| 8 |
use FluentCart\App\Modules\MCP\Support\PermissionGate; |
| 9 |
use FluentCart\App\Modules\MCP\Support\PaymentProjector; |
| 10 |
use FluentCart\App\Modules\MCP\Support\ProductFinancialsCalculator as Calc; |
| 11 |
|
| 12 |
/** |
| 13 |
* get-upcoming-payments — the renewal cohort view: expected billings grouped by |
| 14 |
* date over a window, separating finite (split-pay) installments from open-ended |
| 15 |
* recurring renewals, with an at_risk figure derived from past_due subscriptions |
| 16 |
* and the store's historical renewal success rate. |
| 17 |
* |
| 18 |
* It anchors on next_billing_date (never created_at), steps by each |
| 19 |
* subscription's interval, and stops finite plans at their remaining bills — |
| 20 |
* the exact projection get-product-financials uses, via the shared |
| 21 |
* PaymentProjector, so the two never disagree. |
| 22 |
* |
| 23 |
* Single-currency (defaults to store currency); other currencies present are |
| 24 |
* listed in meta.other_currencies rather than silently summed. |
| 25 |
*/ |
| 26 |
class PaymentScheduleTools |
| 27 |
{ |
| 28 |
/** Statuses that produce a future charge and so feed the projection. */ |
| 29 |
const FORWARD_STATUSES = ['active', 'past_due', 'trialing']; |
| 30 |
|
| 31 |
/** Safety ceiling on subscriptions loaded for one projection. */ |
| 32 |
const MAX_SUBS = 50000; |
| 33 |
|
| 34 |
public static function definitions() |
| 35 |
{ |
| 36 |
return [ |
| 37 |
'fluent-cart/get-upcoming-payments' => [ |
| 38 |
'label' => __('Get Upcoming Payments', 'fluent-cart'), |
| 39 |
'description' => __('Forward view of expected subscription billings grouped by date, split into finite_installments (split-pay, bill_times > 0) vs recurring_renewals (open-ended), plus an at_risk figure from past_due subscriptions discounted by the store\'s historical renewal success rate. Anchors on next_billing_date, steps by each plan\'s interval, stops finite plans at their remaining bills. Optional product_id/variation_id scope to one product. Single currency: store default, others in meta.other_currencies. date_from/date_to default to now..+90 days. Money is {amount, amount_cents, currency, display}.', 'fluent-cart'), |
| 40 |
'input_schema' => [ |
| 41 |
'type' => 'object', |
| 42 |
'properties' => [ |
| 43 |
'product_id' => ['type' => 'integer', 'description' => 'Limit to subscriptions for one product. Omit for the whole store.'], |
| 44 |
'variation_id' => ['type' => 'integer', 'description' => 'Limit to one variation.'], |
| 45 |
'currency' => ['type' => 'string', 'description' => 'ISO currency for the (single-currency) report. Defaults to store currency.'], |
| 46 |
'date_from' => ['type' => 'string', 'description' => 'ISO 8601 or YYYY-MM-DD, UTC. Window start. Default now.'], |
| 47 |
'date_to' => ['type' => 'string', 'description' => 'ISO 8601 or YYYY-MM-DD, UTC. Window end. Default 90 days out.'], |
| 48 |
'bucket' => ['type' => 'string', 'enum' => ['day', 'month'], 'default' => 'day', 'description' => 'Calendar granularity of the schedule.'], |
| 49 |
], |
| 50 |
], |
| 51 |
'execute_callback' => [self::class, 'getUpcomingPayments'], |
| 52 |
'permission_callback' => function () { |
| 53 |
return PermissionGate::can('reports/view'); |
| 54 |
}, |
| 55 |
'annotations' => ['readonly' => true], |
| 56 |
], |
| 57 |
]; |
| 58 |
} |
| 59 |
|
| 60 |
public static function getUpcomingPayments($params = []) |
| 61 |
{ |
| 62 |
$productId = !empty($params['product_id']) ? (int) $params['product_id'] : null; |
| 63 |
$variationId = !empty($params['variation_id']) ? (int) $params['variation_id'] : null; |
| 64 |
$currency = !empty($params['currency']) ? strtoupper(sanitize_text_field($params['currency'])) : MCPHelper::currencyCode(); |
| 65 |
$bucket = (isset($params['bucket']) && $params['bucket'] === 'month') ? 'month' : 'day'; |
| 66 |
|
| 67 |
$window = self::resolveWindow($params); |
| 68 |
if (is_wp_error($window)) { |
| 69 |
return $window; |
| 70 |
} |
| 71 |
|
| 72 |
$load = self::loadSubs($productId, $variationId); |
| 73 |
list($kept, $otherCurrencies) = Calc::filterByCurrency($load['rows'], $currency); |
| 74 |
|
| 75 |
// Build projection descriptors (drop rows with no usable next-bill anchor). |
| 76 |
$projSubs = []; |
| 77 |
$pastDueSubs = []; |
| 78 |
foreach ($kept as $row) { |
| 79 |
$ps = self::toProjSub($row); |
| 80 |
if ($ps === null) { |
| 81 |
continue; |
| 82 |
} |
| 83 |
$projSubs[] = $ps; |
| 84 |
if ($row['status'] === 'past_due') { |
| 85 |
$pastDueSubs[] = $ps; |
| 86 |
} |
| 87 |
} |
| 88 |
|
| 89 |
$fromTs = strtotime($window['from'] . ' UTC'); |
| 90 |
$toTs = strtotime($window['to'] . ' UTC'); |
| 91 |
|
| 92 |
$projection = PaymentProjector::project($projSubs, $fromTs, $toTs, $bucket); |
| 93 |
$pastDueProj = PaymentProjector::project($pastDueSubs, $fromTs, $toTs, $bucket); |
| 94 |
|
| 95 |
$buckets = self::formatBuckets($projection['buckets'], $currency); |
| 96 |
$totals = self::totals($projection['buckets']); |
| 97 |
$pastDueTotal = self::totals($pastDueProj['buckets'])['total_expected']; |
| 98 |
|
| 99 |
$successRate = self::renewalSuccessRate(); |
| 100 |
// Unknown history -> treat the whole past_due expectation as at risk. |
| 101 |
$atRiskCents = ($successRate === null) |
| 102 |
? $pastDueTotal |
| 103 |
: (int) round($pastDueTotal * (1 - $successRate)); |
| 104 |
|
| 105 |
$data = [ |
| 106 |
'window' => [ |
| 107 |
'from' => MCPHelper::toIso8601($window['from']), |
| 108 |
'to' => MCPHelper::toIso8601($window['to']), |
| 109 |
'bucket' => $bucket, |
| 110 |
], |
| 111 |
'schedule' => $buckets, |
| 112 |
'totals' => [ |
| 113 |
'finite_installments' => MCPHelper::money($totals['finite'], $currency), |
| 114 |
'recurring_renewals' => MCPHelper::money($totals['recurring'], $currency), |
| 115 |
'total_expected' => MCPHelper::money($totals['total_expected'], $currency), |
| 116 |
'expected_charges' => $totals['finite_count'] + $totals['recurring_count'], |
| 117 |
], |
| 118 |
'at_risk' => [ |
| 119 |
'amount' => MCPHelper::money($atRiskCents, $currency), |
| 120 |
'from_past_due_expected' => MCPHelper::money($pastDueTotal, $currency), |
| 121 |
'past_due_subscriptions' => count($pastDueSubs), |
| 122 |
'historical_success_rate' => $successRate, |
| 123 |
'basis' => $successRate === null |
| 124 |
? 'no renewal history yet — full past_due expectation shown as at risk' |
| 125 |
: 'store-wide renewal charge success rate (succeeded / (succeeded + failed))', |
| 126 |
], |
| 127 |
]; |
| 128 |
|
| 129 |
$meta = [ |
| 130 |
'currency' => $currency, |
| 131 |
'other_currencies' => $otherCurrencies, |
| 132 |
'forward_statuses' => self::FORWARD_STATUSES, |
| 133 |
'note' => 'Projected from next_billing_date, stepping by each plan\'s interval; finite plans stop at remaining bills. Forward-billing statuses only (active, past_due, trialing).', |
| 134 |
]; |
| 135 |
if ($load['truncated']) { |
| 136 |
$meta['warnings'] = [sprintf( |
| 137 |
/* translators: %1$d: subscription load cap */ |
| 138 |
__('More than %1$d subscriptions matched; the projection uses the first %1$d and may be incomplete.', 'fluent-cart'), |
| 139 |
self::MAX_SUBS |
| 140 |
)]; |
| 141 |
} |
| 142 |
|
| 143 |
return MCPHelper::envelope(self::summary($totals, $atRiskCents, $currency, count($buckets)), $data, $meta); |
| 144 |
} |
| 145 |
|
| 146 |
// ----------------------------------------------------------------- |
| 147 |
// Loaders / builders |
| 148 |
// ----------------------------------------------------------------- |
| 149 |
|
| 150 |
/** |
| 151 |
* Forward-billing subscriptions, optionally scoped to one product/variation. |
| 152 |
* Currency is derived per row from the config JSON (there is no currency |
| 153 |
* column) so the caller can single-currency scope. Reads a limited column set |
| 154 |
* so the model's heavy $appends accessors never fire. |
| 155 |
*/ |
| 156 |
private static function loadSubs($productId, $variationId) |
| 157 |
{ |
| 158 |
$store = MCPHelper::currencyCode(); |
| 159 |
|
| 160 |
$query = Subscription::query()->whereIn('status', self::FORWARD_STATUSES); |
| 161 |
if ($productId !== null) { |
| 162 |
$query->where('product_id', $productId); |
| 163 |
} |
| 164 |
if ($variationId !== null) { |
| 165 |
$query->where('variation_id', $variationId); |
| 166 |
} |
| 167 |
|
| 168 |
/** @var \FluentCart\Framework\Database\Orm\Collection $subs */ |
| 169 |
$subs = $query |
| 170 |
->orderBy('id', 'ASC') |
| 171 |
->limit(self::MAX_SUBS + 1) |
| 172 |
->get(['id', 'billing_interval', 'recurring_total', 'bill_count', 'bill_times', 'status', 'next_billing_date', 'variation_id', 'config']); |
| 173 |
|
| 174 |
$truncated = false; |
| 175 |
if (method_exists($subs, 'count') && $subs->count() > self::MAX_SUBS) { |
| 176 |
$truncated = true; |
| 177 |
$subs = $subs->slice(0, self::MAX_SUBS); |
| 178 |
} |
| 179 |
|
| 180 |
$rows = []; |
| 181 |
foreach ($subs as $sub) { |
| 182 |
$config = is_array($sub->config) ? $sub->config : []; |
| 183 |
$cur = isset($config['currency']) && $config['currency'] !== '' ? strtoupper((string) $config['currency']) : strtoupper($store); |
| 184 |
$rows[] = [ |
| 185 |
'currency' => $cur, |
| 186 |
'billing_interval' => $sub->billing_interval, |
| 187 |
'recurring_total' => (int) $sub->recurring_total, |
| 188 |
'bill_count' => (int) $sub->bill_count, |
| 189 |
'bill_times' => (int) $sub->bill_times, |
| 190 |
'status' => (string) $sub->status, |
| 191 |
'next_billing_date' => $sub->next_billing_date, |
| 192 |
]; |
| 193 |
} |
| 194 |
|
| 195 |
return ['rows' => $rows, 'truncated' => $truncated]; |
| 196 |
} |
| 197 |
|
| 198 |
/** Normalize a sub row into a PaymentProjector descriptor, or null if it can't bill. */ |
| 199 |
private static function toProjSub($row) |
| 200 |
{ |
| 201 |
$anchor = isset($row['next_billing_date']) ? $row['next_billing_date'] : null; |
| 202 |
if ($anchor === null || $anchor === '' || strpos((string) $anchor, '0000-00-00') === 0) { |
| 203 |
return null; |
| 204 |
} |
| 205 |
$billTimes = (int) $row['bill_times']; |
| 206 |
$billCount = (int) $row['bill_count']; |
| 207 |
$settle = Calc::settlement($billTimes); |
| 208 |
|
| 209 |
return [ |
| 210 |
'settlement' => $settle, |
| 211 |
'interval' => Calc::normalizeInterval($row['billing_interval']), |
| 212 |
'recur' => (int) $row['recurring_total'], |
| 213 |
'remaining_bills' => ($settle === 'finite') ? max(0, $billTimes - $billCount) : -1, |
| 214 |
'anchor' => (string) $anchor, |
| 215 |
'status' => $row['status'], |
| 216 |
]; |
| 217 |
} |
| 218 |
|
| 219 |
private static function formatBuckets($buckets, $currency) |
| 220 |
{ |
| 221 |
$out = []; |
| 222 |
foreach ($buckets as $period => $b) { |
| 223 |
$out[] = [ |
| 224 |
'period' => $period, |
| 225 |
'finite_installments' => MCPHelper::money((int) $b['finite'], $currency), |
| 226 |
'recurring_renewals' => MCPHelper::money((int) $b['recurring'], $currency), |
| 227 |
'total_expected' => MCPHelper::money((int) ($b['finite'] + $b['recurring']), $currency), |
| 228 |
'finite_count' => (int) $b['finite_count'], |
| 229 |
'recurring_count' => (int) $b['recurring_count'], |
| 230 |
]; |
| 231 |
} |
| 232 |
return $out; |
| 233 |
} |
| 234 |
|
| 235 |
private static function totals($buckets) |
| 236 |
{ |
| 237 |
$t = ['finite' => 0, 'recurring' => 0, 'finite_count' => 0, 'recurring_count' => 0]; |
| 238 |
foreach ($buckets as $b) { |
| 239 |
$t['finite'] += (int) $b['finite']; |
| 240 |
$t['recurring'] += (int) $b['recurring']; |
| 241 |
$t['finite_count'] += (int) $b['finite_count']; |
| 242 |
$t['recurring_count'] += (int) $b['recurring_count']; |
| 243 |
} |
| 244 |
$t['total_expected'] = $t['finite'] + $t['recurring']; |
| 245 |
return $t; |
| 246 |
} |
| 247 |
|
| 248 |
/** |
| 249 |
* Store-wide renewal charge success rate: succeeded / (succeeded + failed) over |
| 250 |
* transaction_type=charge, order_type=renewal. null when there is no renewal |
| 251 |
* history to learn from. Store-wide (not product-scoped) because renewal |
| 252 |
* success is gateway/dunning-driven, and per-product scoping would not scale. |
| 253 |
*/ |
| 254 |
private static function renewalSuccessRate() |
| 255 |
{ |
| 256 |
$base = OrderTransaction::query() |
| 257 |
->where('transaction_type', 'charge') |
| 258 |
->where('order_type', 'renewal'); |
| 259 |
|
| 260 |
$succeeded = (int) (clone $base)->where('status', 'succeeded')->count(); |
| 261 |
$failed = (int) (clone $base)->where('status', 'failed')->count(); |
| 262 |
$total = $succeeded + $failed; |
| 263 |
|
| 264 |
return $total > 0 ? round($succeeded / $total, 4) : null; |
| 265 |
} |
| 266 |
|
| 267 |
private static function resolveWindow($params) |
| 268 |
{ |
| 269 |
$tz = new \DateTimeZone('UTC'); |
| 270 |
$from = !empty($params['date_from']) ? self::instant($params['date_from'], $tz, false) : gmdate('Y-m-d H:i:s'); |
| 271 |
$to = !empty($params['date_to']) ? self::instant($params['date_to'], $tz, true) : gmdate('Y-m-d H:i:s', strtotime('+90 days')); |
| 272 |
|
| 273 |
if ($from === null || $to === null) { |
| 274 |
return MCPHelper::error('invalid_date', __('date_from / date_to must be ISO 8601 or YYYY-MM-DD.', 'fluent-cart'), ['fields' => ['date_from', 'date_to']]); |
| 275 |
} |
| 276 |
return ['from' => $from, 'to' => $to]; |
| 277 |
} |
| 278 |
|
| 279 |
/** Parse a date bound to UTC 'Y-m-d H:i:s'; date-only snaps to the day edge. */ |
| 280 |
private static function instant($value, $tz, $isEnd) |
| 281 |
{ |
| 282 |
try { |
| 283 |
$dt = new \DateTime((string) $value, $tz); |
| 284 |
} catch (\Exception $e) { |
| 285 |
return null; |
| 286 |
} |
| 287 |
if (preg_match('/^\d{4}-\d{2}-\d{2}$/', trim((string) $value))) { |
| 288 |
$dt->setTime($isEnd ? 23 : 0, $isEnd ? 59 : 0, $isEnd ? 59 : 0); |
| 289 |
} |
| 290 |
$dt->setTimezone(new \DateTimeZone('UTC')); |
| 291 |
return $dt->format('Y-m-d H:i:s'); |
| 292 |
} |
| 293 |
|
| 294 |
private static function summary($totals, $atRiskCents, $currency, $bucketCount) |
| 295 |
{ |
| 296 |
return sprintf( |
| 297 |
/* translators: 1: total expected, 2: number of buckets, 3: at-risk amount */ |
| 298 |
__('%1$s expected across %2$d billing periods; %3$s at risk from past-due plans.', 'fluent-cart'), |
| 299 |
MCPHelper::displayAmount($totals['total_expected'], $currency), |
| 300 |
$bucketCount, |
| 301 |
MCPHelper::displayAmount($atRiskCents, $currency) |
| 302 |
); |
| 303 |
} |
| 304 |
} |
| 305 |
|
