← All changes | includes/api/class-usage-analytics-endpoint.php +478 -153 1.0.2 → 2.9.0 View file →

		@@ -90,32 +90,54 @@
90	90	];
91	91
92	92	/**
93	93	* Claude pricing per 1M tokens (USD)
94		- * Using proper model aliases only
	94	+ * Model IDs sourced from https://docs.anthropic.com/en/docs/about-claude/models
95	95	*/
96	96	private const CLAUDE_PRICING = [
97		- // Claude 4 models (use -0 aliases)
98		- 'claude-sonnet-4-0' => [
	97	+ // Current models (recommended)
	98	+ 'claude-opus-5' => [
	99	+ 'input' => 5.00,
	100	+ 'output' => 25.00
	101	+ ],
	102	+ 'claude-opus-4-8' => [
	103	+ 'input' => 5.00,
	104	+ 'output' => 25.00
	105	+ ],
	106	+ 'claude-sonnet-5' => [
99	107	'input' => 3.00,
100	108	'output' => 15.00
101	109	],
102		- 'claude-opus-4-0' => [
103		- 'input' => 15.00,
104		- 'output' => 75.00
	110	+ 'claude-haiku-4-5' => [
	111	+ 'input' => 1.00,
	112	+ 'output' => 5.00
105	113	],
106		- // Claude 3.x models (use -latest aliases)
107		- 'claude-3-7-sonnet-latest' => [
	114	+ // Claude 4.x models
	115	+ 'claude-opus-4-6' => [
	116	+ 'input' => 5.00,
	117	+ 'output' => 25.00
	118	+ ],
	119	+ 'claude-sonnet-4-6' => [
108	120	'input' => 3.00,
109	121	'output' => 15.00
110	122	],
111		- 'claude-3-5-sonnet-latest' => [
	123	+ // Claude 4.5 models
	124	+ 'claude-haiku-4-5-20251001' => [
	125	+ 'input' => 1.00,
	126	+ 'output' => 5.00
	127	+ ],
	128	+ // Claude 3.5 models (legacy)
	129	+ 'claude-3-5-sonnet-20241022' => [
112	130	'input' => 3.00,
113	131	'output' => 15.00
114	132	],
115		- 'claude-3-5-haiku-latest' => [
	133	+ 'claude-3-5-haiku-20241022' => [
116	134	'input' => 0.80,
117	135	'output' => 4.00
	136	+ ],
	137	+ 'claude-3-opus-20240229' => [
	138	+ 'input' => 15.00,
	139	+ 'output' => 75.00
118	140	]
119	141	];
120	142
121	143	/**
		@@ -121,8 +143,26 @@
121	143	/**
122	144	* Gemini pricing per 1M tokens (USD)
123	145	*/
124	146	private const GEMINI_PRICING = [
	147	+ // Gemini 3.x models (tiered models use the base <=200k-token rate)
	148	+ 'gemini-3.1-pro' => [
	149	+ 'input' => 2.00,
	150	+ 'output' => 12.00
	151	+ ],
	152	+ // The id the UI offers; 3.1 Pro ships only under -preview.
	153	+ 'gemini-3.1-pro-preview' => [
	154	+ 'input' => 2.00,
	155	+ 'output' => 12.00
	156	+ ],
	157	+ 'gemini-3.5-flash' => [
	158	+ 'input' => 1.50,
	159	+ 'output' => 9.00
	160	+ ],
	161	+ 'gemini-3.1-flash-lite' => [
	162	+ 'input' => 0.25,
	163	+ 'output' => 1.50
	164	+ ],
125	165	// Gemini 2.5 models
126	166	'gemini-2.5-flash' => [
127	167	'input' => 0.30,
128	168	'output' => 2.50
		@@ -131,15 +171,15 @@
131	171	'input' => 0.10,
132	172	'output' => 0.40
133	173	],
134	174	'gemini-2.5-pro' => [
135		- 'input' => 3.50,
136		- 'output' => 10.50
	175	+ 'input' => 1.25,
	176	+ 'output' => 10.00
137	177	],
138	178	// Gemini 2.0 models
139	179	'gemini-2.0-flash' => [
140		- 'input' => 0.075,
141		- 'output' => 0.30
	180	+ 'input' => 0.10,
	181	+ 'output' => 0.40
142	182	],
143	183	// Gemini 1.5 models
144	184	'gemini-1.5-flash' => [
145	185	'input' => 0.075,
		@@ -149,10 +189,48 @@
149	189	'input' => 1.25,
150	190	'output' => 5.00
151	191	]
152	192	];
153		-
	193	+
154	194	/**
	195	+ * OpenRouter pricing per 1M tokens (USD)
	196	+ *
	197	+ * OpenRouter passes through each upstream model's pricing; these are
	198	+ * representative rates for the curated model list used for cost estimates.
	199	+ */
	200	+ private const OPENROUTER_PRICING = [
	201	+ 'openai/gpt-4o-mini' => [
	202	+ 'input' => 0.15,
	203	+ 'output' => 0.60
	204	+ ],
	205	+ 'anthropic/claude-sonnet-5' => [
	206	+ 'input' => 3.00,
	207	+ 'output' => 15.00
	208	+ ],
	209	+ 'google/gemini-3.5-flash' => [
	210	+ 'input' => 1.50,
	211	+ 'output' => 9.00
	212	+ ],
	213	+ // Retired upstream, kept so historical usage rows still price correctly.
	214	+ 'anthropic/claude-3.5-sonnet' => [
	215	+ 'input' => 3.00,
	216	+ 'output' => 15.00
	217	+ ],
	218	+ 'google/gemini-2.0-flash-001' => [
	219	+ 'input' => 0.10,
	220	+ 'output' => 0.40
	221	+ ],
	222	+ 'meta-llama/llama-3.3-70b-instruct' => [
	223	+ 'input' => 0.12,
	224	+ 'output' => 0.30
	225	+ ],
	226	+ 'deepseek/deepseek-chat' => [
	227	+ 'input' => 0.14,
	228	+ 'output' => 0.28
	229	+ ]
	230	+ ];
	231	+
	232	+ /**
155	233	* Time saved estimates per action (minutes)
156	234	*/
157	235	private const TIME_SAVED_ESTIMATES = [
158	236	'seo_metadata' => 20,
		@@ -188,8 +266,9 @@
188	266	'permission_callback' => [$this, 'check_permissions'],
189	267	'args' => [
190	268	'period' => [
191	269	'default' => '30d',
	270	+ 'type' => 'string',
192	271	'enum' => ['7d', '30d', '90d', 'all'],
193	272	'sanitize_callback' => 'sanitize_key'
194	273	],
195	274	'user_id' => [
		@@ -207,20 +286,32 @@
207	286	'permission_callback' => [$this, 'check_permissions'],
208	287	'args' => [
209	288	'period' => [
210	289	'default' => '30d',
	290	+ 'type' => 'string',
211	291	'enum' => ['7d', '30d', '90d', 'all'],
212	292	'sanitize_callback' => 'sanitize_key'
213	293	],
214		- 'group_by' => [
215		- 'default' => 'day',
216		- 'enum' => ['day', 'week', 'month'],
217		- 'sanitize_callback' => 'sanitize_key'
218		- ],
219	294	'user_id' => [
220	295	'default' => 0,
221	296	'type' => 'integer',
222	297	'sanitize_callback' => 'absint'
	298	+ ],
	299	+ // Declared because the handler reads them. They were validated
	300	+ // only by the handler's own clamping, so they had no type
	301	+ // coercion and did not appear in the endpoint's schema.
	302	+ 'page' => [
	303	+ 'default' => 1,
	304	+ 'type' => 'integer',
	305	+ 'minimum' => 1,
	306	+ 'sanitize_callback' => 'absint'
	307	+ ],
	308	+ 'per_page' => [
	309	+ 'default' => 20,
	310	+ 'type' => 'integer',
	311	+ 'minimum' => 10,
	312	+ 'maximum' => 100,
	313	+ 'sanitize_callback' => 'absint'
223	314	]
224	315	]
225	316	]);
226	317
		@@ -231,14 +322,16 @@
231	322	'permission_callback' => [$this, 'check_permissions'],
232	323	'args' => [
233	324	'period' => [
234	325	'default' => '30d',
	326	+ 'type' => 'string',
235	327	'enum' => ['7d', '30d', '90d', 'all'],
236	328	'sanitize_callback' => 'sanitize_key'
237	329	],
238	330	'provider' => [
239	331	'default' => 'all',
240		- 'enum' => ['all', 'openai', 'claude'],
	332	+ 'type' => 'string',
	333	+ 'enum' => ['all', 'openai', 'claude', 'gemini', 'openrouter', 'openai_compatible'],
241	334	'sanitize_callback' => 'sanitize_key'
242	335	],
243	336	'user_id' => [
244	337	'default' => 0,
		@@ -254,9 +347,9 @@
254	347	*
255	348	* @param WP_REST_Request $request Request object
256	349	* @return WP_REST_Response|WP_Error Response object
257	350	*/
258		- public function get_overview_metrics(WP_REST_Request $request): WP_REST_Response|WP_Error {
	351	+ public function get_overview_metrics(WP_REST_Request $request) {
259	352	$period = $request->get_param('period');
260	353	$user_id = $request->get_param('user_id') ?: get_current_user_id();
261	354
262	355	try {
		@@ -297,9 +390,8 @@
297	390	'ai_actions' => $ai_metrics['total_actions'],
298	391	'features_used_count' => $ai_metrics['features_used_count'],
299	392	'most_used_feature' => $ai_metrics['most_used_feature'],
300	393	'most_used_count' => $ai_metrics['most_used_count'],
301		- 'success_rate' => $ai_metrics['success_rate'],
302	394	'content_briefs' => $brief_metrics['total_briefs'],
303	395	'feature_breakdown' => $ai_metrics['feature_breakdown'],
304	396	'provider_breakdown' => $cost_data['by_provider']
305	397	],
		@@ -328,9 +420,9 @@
328	420	*
329	421	* @param WP_REST_Request $request Request object
330	422	* @return bool|WP_Error Permission result
331	423	*/
332		- public function check_permissions(WP_REST_Request $request): bool|WP_Error {
	424	+ public function check_permissions(WP_REST_Request $request) {
333	425	// Check if user is logged in
334	426	if (!is_user_logged_in()) {
335	427	return new WP_Error(
336	428	'not_logged_in',
		@@ -363,8 +455,34 @@
363	455	return true;
364	456	}
365	457
366	458	/**
	459	+ * Bind the cache-invalidation listeners for the whole request lifecycle.
	460	+ *
	461	+ * The listeners used to be registered only by the constructor, which runs
	462	+ * on rest_api_init — so usage logged during cron, WP-CLI or an admin-post
	463	+ * request found no listener and the cached overview rode out its full TTL.
	464	+ * Called from API\Manager::init() on every request instead.
	465	+ *
	466	+ * @since 2.2.1
	467	+ * @return void
	468	+ */
	469	+ public static function boot_cache_invalidation(): void {
	470	+ static $booted = false;
	471	+
	472	+ if ($booted) {
	473	+ return;
	474	+ }
	475	+
	476	+ $booted = true;
	477	+
	478	+ // Constructing the endpoint registers the listeners; the guard in
	479	+ // setup_cache_invalidation() keeps a later REST construction from
	480	+ // double-binding them.
	481	+ new self();
	482	+ }
	483	+
	484	+ /**
367	485	* Set up cache invalidation hooks
368	486	*
369	487	* @since 1.0.0
370	488	* @return void
		@@ -369,8 +487,23 @@
369	487	* @since 1.0.0
370	488	* @return void
371	489	*/
372	490	private function setup_cache_invalidation(): void {
	491	+ // The endpoint is constructed more than once per request — once on
	492	+ // init via boot_cache_invalidation(), again on rest_api_init, and
	493	+ // potentially by callers resolving it on demand. Bind once per
	494	+ // request, or every event invalidates N times.
	495	+ //
	496	+ // A has_action() check cannot do this: the callback is [$this, ...]
	497	+ // and each construction is a different instance, so it never matches.
	498	+ static $bound = false;
	499	+
	500	+ if ($bound) {
	501	+ return;
	502	+ }
	503	+
	504	+ $bound = true;
	505	+
373	506	// Invalidate analytics cache when AI usage is logged
374	507	add_action('thinkrank_ai_usage_logged', [$this, 'invalidate_analytics_cache']);
375	508
376	509	// Invalidate analytics cache when SEO scores are updated
		@@ -391,21 +524,59 @@
391	524	$this->invalidate_cache_pattern('thinkrank_analytics_*');
392	525	}
393	526
394	527	/**
	528	+ * Get the cutoff datetime string for a period.
	529	+ * Returns null for 'all' (no date restriction).
	530	+ *
	531	+ * @param string $period Period string
	532	+ * @return string|null Cutoff datetime in MySQL format, or null for all time
	533	+ */
	534	+ private function get_date_cutoff(string $period): ?string {
	535	+ switch ($period) {
	536	+ case '7d':
	537	+ $days = 7;
	538	+ break;
	539	+ case '30d':
	540	+ $days = 30;
	541	+ break;
	542	+ case '90d':
	543	+ $days = 90;
	544	+ break;
	545	+ case 'all':
	546	+ $days = null;
	547	+ break;
	548	+ default:
	549	+ $days = 30;
	550	+ }
	551	+ if ($days === null) {
	552	+ return null;
	553	+ }
	554	+ return gmdate('Y-m-d H:i:s', strtotime("-{$days} days"));
	555	+ }
	556	+
	557	+ /**
395	558	* Get date condition for SQL queries
396	559	*
	560	+ * @deprecated Use get_date_cutoff() with parameterized queries instead.
	561	+ * Kept for back-compat with get_previous_period_condition() parsing.
	562	+ *
397	563	* @param string $period Period string
398	564	* @return string SQL date condition
399	565	*/
400	566	private function get_date_condition(string $period): string {
401		- return match($period) {
402		- '7d' => "AND created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)",
403		- '30d' => "AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)",
404		- '90d' => "AND created_at >= DATE_SUB(NOW(), INTERVAL 90 DAY)",
405		- 'all' => "",
406		- default => "AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)"
407		- };
	567	+ switch ($period) {
	568	+ case '7d':
	569	+ return "AND created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)";
	570	+ case '30d':
	571	+ return "AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)";
	572	+ case '90d':
	573	+ return "AND created_at >= DATE_SUB(NOW(), INTERVAL 90 DAY)";
	574	+ case 'all':
	575	+ return "";
	576	+ default:
	577	+ return "AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)";
	578	+ }
408	579	}
409	580
410	581	/**
411	582	* Calculate costs from usage data
		@@ -417,8 +588,13 @@
417	588	$costs = [
418	589	'openai' => 0,
419	590	'claude' => 0,
420	591	'gemini' => 0,
	592	+ 'openrouter' => 0,
	593	+ // Costed only when the user told us what their endpoint charges;
	594	+ // otherwise it stays 0 and the UI shows "—" rather than implying
	595	+ // that a local model was free of charge or that we know the price.
	596	+ 'openai_compatible' => 0,
421	597	'total' => 0,
422	598	'by_provider' => []
423	599	];
424	600
		@@ -423,43 +599,51 @@
423	599	];
424	600
425	601	foreach ($usage_data as $usage) {
426	602	$tokens = (int) $usage['tokens_used'];
427		- $provider = $usage['provider'];
	603	+ $provider = (string) $usage['provider'];
428	604
429		- // Estimate 70% input, 30% output tokens
430		- $input_tokens = $tokens * 0.7;
431		- $output_tokens = $tokens * 0.3;
	605	+ // Unknown provider: no pricing table, so it cannot be costed. Skip
	606	+ // rather than let `+=` invent a key that the total below misses.
	607	+ if (!isset($costs[$provider])) {
	608	+ continue;
	609	+ }
432	610
433		- $cost = 0;
	611	+ // Price at the model the request actually used. Reading only the
	612	+ // provider meant every row was costed at that provider's default
	613	+ // model, so this total disagreed with the per-record figures in
	614	+ // the Usage Breakdown tab — by 4.5x on a gpt-4o-mini workload.
	615	+ $metadata = !empty($usage['metadata']) ? json_decode((string) $usage['metadata'], true) : [];
	616	+ $model = is_array($metadata) && !empty($metadata['actual_model'])
	617	+ ? (string) $metadata['actual_model']
	618	+ : $this->get_default_model($provider);
434	619
435		- // Use the robust pricing helper for consistent cost calculation
436		- $pricing = $this->get_model_pricing($provider);
437		- if ($pricing) {
438		- $cost = ($input_tokens * $pricing['input'] / 1000000) +
439		- ($output_tokens * $pricing['output'] / 1000000);
440		- $costs[$provider] += $cost;
	620	+ // Single source of truth for per-row pricing, shared with
	621	+ // get_detailed_usage_breakdown() so both tabs always agree.
	622	+ $costs[$provider] += $this->calculate_record_cost($provider, $tokens, $model);
	623	+ }
	624	+
	625	+ $costs['total'] = $costs['openai'] + $costs['claude'] + $costs['gemini'] + $costs['openrouter'] + $costs['openai_compatible'];
	626	+
	627	+ // Report only providers that actually incurred cost. Emitting all four
	628	+ // unconditionally meant a site with no AI usage rendered four ranked
	629	+ // rows at "$0.0000 (0%)" — reading as "four providers were used and
	630	+ // each was free" — and made the panel's own "No provider cost data"
	631	+ // empty state unreachable.
	632	+ $costs['by_provider'] = [];
	633	+ foreach (['openai', 'claude', 'gemini', 'openrouter', 'openai_compatible'] as $provider) {
	634	+ if ($costs[$provider] <= 0) {
	635	+ continue;
441	636	}
	637	+
	638	+ $costs['by_provider'][$provider] = [
	639	+ 'cost' => round($costs[$provider], 4),
	640	+ 'percentage' => $costs['total'] > 0
	641	+ ? round(($costs[$provider] / $costs['total']) * 100, 1)
	642	+ : 0
	643	+ ];
442	644	}
443	645
444		- $costs['total'] = $costs['openai'] + $costs['claude'] + $costs['gemini'];
445		-
446		- // Format provider breakdown
447		- $costs['by_provider'] = [
448		- 'openai' => [
449		- 'cost' => round($costs['openai'], 4),
450		- 'percentage' => $costs['total'] > 0 ? round(($costs['openai'] / $costs['total']) * 100, 1) : 0
451		- ],
452		- 'claude' => [
453		- 'cost' => round($costs['claude'], 4),
454		- 'percentage' => $costs['total'] > 0 ? round(($costs['claude'] / $costs['total']) * 100, 1) : 0
455		- ],
456		- 'gemini' => [
457		- 'cost' => round($costs['gemini'], 4),
458		- 'percentage' => $costs['total'] > 0 ? round(($costs['gemini'] / $costs['total']) * 100, 1) : 0
459		- ]
460		- ];
461		-
462	646	return $costs;
463	647	}
464	648
465	649	/**
		@@ -491,35 +675,63 @@
491	675
492	676	// Get table name and escape it properly (table names cannot be parameterized)
493	677	$table_name = esc_sql($this->database->get_table('ai_usage'));
494	678
495		- // Get all AI usage data for the user and period
496		- $base_query = "
497		- SELECT
498		- provider,
499		- action,
500		- tokens_used,
501		- created_at
502		- FROM `{$table_name}`
503		- WHERE user_id = %d
504		- ";
	679	+ $cutoff = $this->get_date_cutoff($this->resolve_period_from_condition($date_condition));
505	680
506		- // Prepare and execute query with proper parameter binding to prevent SQL injection
507		- // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Analytics data is real-time and shouldn't be cached
508		- $usage_data = $wpdb->get_results(
509		- // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Base query contains table name, date condition is from controlled source
510		- $wpdb->prepare($base_query, $user_id) . " $date_condition",
511		- ARRAY_A
512		- );
	681	+ if ($cutoff !== null) {
	682	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	683	+ $usage_data = $wpdb->get_results(
	684	+ $wpdb->prepare(
	685	+ // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table_name is escaped via esc_sql().
	686	+ "SELECT provider, action, tokens_used, metadata, created_at FROM `{$table_name}` WHERE user_id = %d AND created_at >= %s",
	687	+ $user_id,
	688	+ $cutoff
	689	+ ),
	690	+ ARRAY_A
	691	+ );
	692	+ } else {
	693	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	694	+ $usage_data = $wpdb->get_results(
	695	+ $wpdb->prepare(
	696	+ // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table_name is escaped via esc_sql().
	697	+ "SELECT provider, action, tokens_used, metadata, created_at FROM `{$table_name}` WHERE user_id = %d",
	698	+ $user_id
	699	+ ),
	700	+ ARRAY_A
	701	+ );
	702	+ }
513	703
514	704	if (empty($usage_data)) {
	705	+ // Must return the same shape as the populated path below —
	706	+ // get_overview_metrics() reads every key unconditionally, so a
	707	+ // short array here surfaces as undefined-key warnings and null
	708	+ // fields for any user with no AI usage yet (i.e. a fresh install).
	709	+ // The values mirror what the loop below produces for zero rows.
	710	+ //
	711	+ // The change fields are COMPUTED here rather than hardcoded to 0.
	712	+ // An empty current window does not mean "nothing changed": a user
	713	+ // whose usage fell from five actions last month to none this month
	714	+ // was shown a 0 — rendered as the same em-dash a genuinely flat
	715	+ // period gets — instead of the -100% that actually happened.
	716	+ $previous = $this->get_previous_period_data($user_id, $date_condition);
	717	+
515	718	return [
516	719	'total_actions' => 0,
517	720	'total_tokens' => 0,
518	721	'feature_breakdown' => [],
	722	+ 'features_used_count' => 0,
	723	+ 'most_used_feature' => '',
	724	+ 'most_used_count' => 0,
519	725	'usage_data' => [],
520		- 'cost_change' => 0,
521		- 'time_saved_change' => 0
	726	+ 'cost_change' => $this->calculate_percentage_change(
	727	+ array_key_exists('total_cost', $previous) ? $previous['total_cost'] : 0,
	728	+ 0.0
	729	+ ),
	730	+ 'time_saved_change' => $this->calculate_percentage_change(
	731	+ array_key_exists('time_saved', $previous) ? $previous['time_saved'] : 0,
	732	+ 0.0
	733	+ )
522	734	];
523	735	}
524	736
525	737	// Calculate feature breakdown and new metrics
		@@ -551,20 +763,24 @@
551	763	$most_used_count = $count;
552	764	}
553	765	}
554	766
555		- // Calculate success rate (assuming all logged actions are successful for now)
556		- // In future, we could track failed attempts separately
557		- $success_rate = $total_actions > 0 ? 100 : 0;
	767	+ // No success rate here on purpose. It used to be
	768	+ // `$total_actions > 0 ? 100 : 0` — a constant presented as a
	769	+ // measurement, and one that could only ever read 100% or 0%. Failed
	770	+ // AI calls are never written to this table, so there is nothing to
	771	+ // compute a rate from; the KPI card is gone until there is.
558	772
559	773	// Calculate changes from previous period
560	774	$previous_period_data = $this->get_previous_period_data($user_id, $date_condition);
	775	+ // Note the lack of `?? 0`: a null here means "no previous period",
	776	+ // and coalescing it to zero would turn that back into a fake 100%.
561	777	$cost_change = $this->calculate_percentage_change(
562		- $previous_period_data['total_cost'] ?? 0,
	778	+ array_key_exists('total_cost', $previous_period_data) ? $previous_period_data['total_cost'] : 0,
563	779	$this->calculate_total_cost($usage_data)
564	780	);
565	781	$time_saved_change = $this->calculate_percentage_change(
566		- $previous_period_data['time_saved'] ?? 0,
	782	+ array_key_exists('time_saved', $previous_period_data) ? $previous_period_data['time_saved'] : 0,
567	783	$this->calculate_time_saved($feature_breakdown)
568	784	);
569	785
570	786	return [
		@@ -573,9 +789,8 @@
573	789	'feature_breakdown' => $feature_breakdown,
574	790	'features_used_count' => $features_used_count,
575	791	'most_used_feature' => $most_used_feature,
576	792	'most_used_count' => $most_used_count,
577		- 'success_rate' => $success_rate,
578	793	'usage_data' => $usage_data,
579	794	'cost_change' => $cost_change,
580	795	'time_saved_change' => $time_saved_change
581	796	];
		@@ -590,45 +805,65 @@
590	805	*/
591	806	private function get_seo_metrics(int $user_id, string $date_condition): array {
592	807	global $wpdb;
593	808
594		- // Get table name and escape it properly (table names cannot be parameterized)
595	809	$table_name = esc_sql($this->database->get_table('seo_scores'));
	810	+ $cutoff = $this->get_date_cutoff($this->resolve_period_from_condition($date_condition));
596	811
597		- // Get content optimized count and average score
598		- $base_query = "
599		- SELECT
600		- COUNT(DISTINCT post_id) as content_optimized,
601		- AVG(overall_score) as average_score
602		- FROM `{$table_name}`
603		- WHERE user_id = %d
604		- ";
	812	+ if ($cutoff !== null) {
	813	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	814	+ $result = $wpdb->get_row(
	815	+ $wpdb->prepare(
	816	+ // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table_name is escaped via esc_sql().
	817	+ "SELECT COUNT(DISTINCT post_id) as content_optimized, AVG(overall_score) as average_score FROM `{$table_name}` WHERE user_id = %d AND created_at >= %s",
	818	+ $user_id,
	819	+ $cutoff
	820	+ ),
	821	+ ARRAY_A
	822	+ );
	823	+ } else {
	824	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	825	+ $result = $wpdb->get_row(
	826	+ $wpdb->prepare(
	827	+ // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table_name is escaped via esc_sql().
	828	+ "SELECT COUNT(DISTINCT post_id) as content_optimized, AVG(overall_score) as average_score FROM `{$table_name}` WHERE user_id = %d",
	829	+ $user_id
	830	+ ),
	831	+ ARRAY_A
	832	+ );
	833	+ }
605	834
606		- // Prepare and execute query with proper parameter binding to prevent SQL injection
607		- // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Analytics data is real-time and shouldn't be cached
608		- $result = $wpdb->get_row(
609		- // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Base query contains table name, date condition is from controlled source
610		- $wpdb->prepare($base_query, $user_id) . " $date_condition",
611		- ARRAY_A
612		- );
	835	+ if (!$result || (int) $result['content_optimized'] === 0) {
	836	+ // Same reasoning as the empty branch in get_ai_usage_metrics():
	837	+ // an empty current window is not "no change". A user who
	838	+ // optimized three posts last month and none this month should
	839	+ // see -100%, not the em-dash a flat period gets — and for `all`
	840	+ // there is no previous window, so the change is null.
	841	+ $previous = $this->get_previous_seo_data($user_id, $date_condition);
613	842
614		- if (!$result || $result['content_optimized'] == 0) {
615	843	return [
616	844	'content_optimized' => 0,
617	845	'average_seo_score' => 0,
618		- 'content_optimized_change' => 0,
619		- 'seo_score_change' => 0
	846	+ 'content_optimized_change' => $this->calculate_percentage_change(
	847	+ array_key_exists('content_optimized', $previous) ? $previous['content_optimized'] : 0,
	848	+ 0.0
	849	+ ),
	850	+ 'seo_score_change' => $this->calculate_percentage_change(
	851	+ array_key_exists('average_seo_score', $previous) ? $previous['average_seo_score'] : 0,
	852	+ 0.0
	853	+ )
620	854	];
621	855	}
622	856
623	857	// Calculate changes from previous period
624	858	$previous_seo_data = $this->get_previous_seo_data($user_id, $date_condition);
	859	+ // As above: no `?? 0`, so a null "no previous period" survives.
625	860	$content_optimized_change = $this->calculate_percentage_change(
626		- $previous_seo_data['content_optimized'] ?? 0,
	861	+ array_key_exists('content_optimized', $previous_seo_data) ? $previous_seo_data['content_optimized'] : 0,
627	862	(int) $result['content_optimized']
628	863	);
629	864	$seo_score_change = $this->calculate_percentage_change(
630		- $previous_seo_data['average_seo_score'] ?? 0,
	865	+ array_key_exists('average_seo_score', $previous_seo_data) ? $previous_seo_data['average_seo_score'] : 0,
631	866	round((float) $result['average_score'], 1)
632	867	);
633	868
634	869	return [
		@@ -648,25 +883,32 @@
648	883	*/
649	884	private function get_content_brief_metrics(int $user_id, string $date_condition): array {
650	885	global $wpdb;
651	886
652		- // Get table name and escape it properly (table names cannot be parameterized)
653	887	$table_name = esc_sql($this->database->get_table('content_briefs'));
	888	+ $cutoff = $this->get_date_cutoff($this->resolve_period_from_condition($date_condition));
654	889
655		- // Get content briefs count
656		- $base_query = "
657		- SELECT COUNT(*) as total_briefs
658		- FROM `{$table_name}`
659		- WHERE user_id = %d
660		- ";
	890	+ if ($cutoff !== null) {
	891	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	892	+ $result = $wpdb->get_var(
	893	+ $wpdb->prepare(
	894	+ // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table_name is escaped via esc_sql().
	895	+ "SELECT COUNT(*) as total_briefs FROM `{$table_name}` WHERE user_id = %d AND created_at >= %s",
	896	+ $user_id,
	897	+ $cutoff
	898	+ )
	899	+ );
	900	+ } else {
	901	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	902	+ $result = $wpdb->get_var(
	903	+ $wpdb->prepare(
	904	+ // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table_name is escaped via esc_sql().
	905	+ "SELECT COUNT(*) as total_briefs FROM `{$table_name}` WHERE user_id = %d",
	906	+ $user_id
	907	+ )
	908	+ );
	909	+ }
661	910
662		- // Prepare and execute query with proper parameter binding to prevent SQL injection
663		- // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Analytics data is real-time and shouldn't be cached
664		- $result = $wpdb->get_var(
665		- // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Base query contains table name, date condition is from controlled source
666		- $wpdb->prepare($base_query, $user_id) . " $date_condition"
667		- );
668		-
669	911	return [
670	912	'total_briefs' => (int) $result ?: 0
671	913	];
672	914	}
		@@ -676,14 +918,23 @@
676	918	*
677	919	* @param WP_REST_Request $request Request object
678	920	* @return WP_REST_Response|WP_Error Response object
679	921	*/
680		- public function get_usage_breakdown(WP_REST_Request $request): WP_REST_Response|WP_Error {
	922	+ public function get_usage_breakdown(WP_REST_Request $request) {
681	923	try {
682		- $user_id = get_current_user_id();
	924	+ // Mirrors get_overview_metrics(). The two endpoints declared the
	925	+ // same `user_id` argument but only overview honoured it, so the
	926	+ // same query string described two different users depending on
	927	+ // which one you asked. check_permissions() already requires
	928	+ // manage_options before another user's id is accepted.
	929	+ $user_id = $request->get_param('user_id') ?: get_current_user_id();
683	930	$period = $request->get_param('period') ?? '30d';
684		- $page = max(1, (int) $request->get_param('page') ?? 1);
685		- $per_page = min(100, max(10, (int) $request->get_param('per_page') ?? 20));
	931	+ // `(int)` binds tighter than `??`, so `(int) null` is 0 and the
	932	+ // `?? 20` fallback was unreachable — per_page silently defaulted to
	933	+ // the max(10, 0) floor of 10 rather than the 20 it advertises, and
	934	+ // page to max(1, 0) = 1 by luck rather than intent (#394).
	935	+ $page = max(1, (int) ($request->get_param('page') ?? 1));
	936	+ $per_page = min(100, max(10, (int) ($request->get_param('per_page') ?? 20)));
686	937	$offset = ($page - 1) * $per_page;
687	938
688	939	// Get date range for queries
689	940	$date_condition = $this->get_date_condition($period);
		@@ -699,9 +950,10 @@
699	950	'pagination' => [
700	951	'page' => $page,
701	952	'per_page' => $per_page,
702	953	'total_records' => $total_records,
703		- 'total_pages' => ceil($total_records / $per_page)
	954	+ // (int) so it serialises as 2, not 2.0.
	955	+ 'total_pages' => $per_page > 0 ? (int) ceil($total_records / $per_page) : 0
704	956	],
705	957	'period' => $period
706	958	]
707	959	], 200);
		@@ -720,14 +972,16 @@
720	972	*
721	973	* @param WP_REST_Request $request Request object
722	974	* @return WP_REST_Response|WP_Error Response object
723	975	*/
724		- public function get_cost_analysis(WP_REST_Request $request): WP_REST_Response|WP_Error {
	976	+ public function get_cost_analysis(WP_REST_Request $request) {
	977	+ // Return 200 with success:false so the frontend can render an
	978	+ // "unavailable" state — apiFetch rejects on non-2xx, which would
	979	+ // otherwise surface as a generic hard error.
725	980	return new WP_REST_Response([
726		- 'success' => true,
727		- 'data' => [
728		- 'message' => 'Cost analysis endpoint - to be implemented in Phase 2'
729		- ]
	981	+ 'success' => false,
	982	+ 'data' => null,
	983	+ 'message' => 'Cost analysis is not yet implemented.'
730	984	], 200);
731	985	}
732	986
733	987	/**
		@@ -747,14 +1001,22 @@
747	1001	* @param float $old_value Previous period value
748	1002	* @param float $new_value Current period value
749	1003	* @return float Percentage change
750	1004	*/
751		- private function calculate_percentage_change(float $old_value, float $new_value): float {
752		- if ($old_value == 0) {
753		- return $new_value > 0 ? 100 : 0;
	1005	+ private function calculate_percentage_change($old_value, float $new_value): ?float {
	1006	+ // No previous period at all (the 'all' range).
	1007	+ if (null === $old_value) {
	1008	+ return null;
754	1009	}
755	1010
756		- return round((($new_value - $old_value) / $old_value) * 100, 1);
	1011	+ if ((float) $old_value === 0.0) {
	1012	+ // Growth from nothing has no percentage. Reporting a flat 100%
	1013	+ // dressed it up as a measured change; null lets the UI say "new"
	1014	+ // (or say nothing) instead of inventing a number.
	1015	+ return $new_value > 0 ? null : 0.0;
	1016	+ }
	1017	+
	1018	+ return round((($new_value - (float) $old_value) / (float) $old_value) * 100, 1);
757	1019	}
758	1020
759	1021	/**
760	1022	* Get previous period data for comparison
		@@ -771,17 +1033,24 @@
771	1033
772	1034	// Extract the interval from current date condition to calculate previous period
773	1035	$previous_date_condition = $this->get_previous_period_condition($current_date_condition);
774	1036
	1037	+ // No preceding window: report "not comparable" rather than querying a
	1038	+ // made-up one.
	1039	+ if (null === $previous_date_condition) {
	1040	+ return ['total_cost' => null, 'time_saved' => null];
	1041	+ }
	1042	+
775	1043	// Prepare and execute query with proper parameter binding to prevent SQL injection
776	1044	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is properly escaped, date condition is from controlled source
777		- // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Analytics data is real-time and shouldn't be cached
	1045	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Analytics data is real-time and shouldn't be cached
778	1046	$usage_data = $wpdb->get_results(
779	1047	$wpdb->prepare("
780	1048	SELECT
781	1049	provider,
782	1050	action,
783		- tokens_used
	1051	+ tokens_used,
	1052	+ metadata
784	1053	FROM `{$table_name}`
785	1054	WHERE user_id = %d
786	1055	{$previous_date_condition}
787	1056	", $user_id),
		@@ -838,11 +1107,11 @@
838	1107	ORDER BY created_at DESC
839	1108	LIMIT %d OFFSET %d
840	1109	";
841	1110
842		- // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Analytics data is real-time, table name and date condition are validated internally
	1111	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Analytics data is real-time, table name and date condition are validated internally
843	1112	$usage_data = $wpdb->get_results(
844		- // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- SQL is properly prepared with placeholders
	1113	+ // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- SQL is properly prepared with placeholders
845	1114	$wpdb->prepare($sql, $user_id, $limit, $offset),
846	1115	ARRAY_A
847	1116	);
848	1117
		@@ -887,11 +1156,11 @@
887	1156	WHERE user_id = %d
888	1157	{$date_condition}
889	1158	";
890	1159
891		- // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Analytics data is real-time, table name and date condition are validated internally
	1160	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Analytics data is real-time, table name and date condition are validated internally
892	1161	$count = $wpdb->get_var(
893		- // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- SQL is properly prepared with placeholders
	1162	+ // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- SQL is properly prepared with placeholders
894	1163	$wpdb->prepare($sql, $user_id)
895	1164	);
896	1165
897	1166	return (int) $count;
		@@ -917,9 +1186,9 @@
917	1186	$input_tokens = $tokens_used * 0.7;
918	1187	$output_tokens = $tokens_used * 0.3;
919	1188
920	1189	return (($input_tokens / 1000000) * $pricing['input']) +
921		- (($output_tokens / 1000000) * $pricing['output']);
	1190	+ (($output_tokens / 1000000) * $pricing['output']);
922	1191	}
923	1192
924	1193	/**
925	1194	* Get pricing for any model with intelligent fallbacks
		@@ -941,10 +1210,10 @@
941	1210	// Try specific model first, fallback to recommended default
942	1211	if ($model && isset(self::CLAUDE_PRICING[$model])) {
943	1212	return self::CLAUDE_PRICING[$model];
944	1213	}
945		- return self::CLAUDE_PRICING['claude-3-7-sonnet-latest'] ??
946		- self::CLAUDE_PRICING['claude-3-5-sonnet-latest'] ?? null;
	1214	+ return self::CLAUDE_PRICING['claude-sonnet-5'] ??
	1215	+ self::CLAUDE_PRICING['claude-sonnet-4-6'] ?? null;
947	1216
948	1217	case 'gemini':
949	1218	// Try specific model first, fallback to default
950	1219	if ($model && isset(self::GEMINI_PRICING[$model])) {
		@@ -949,10 +1218,31 @@
949	1218	// Try specific model first, fallback to default
950	1219	if ($model && isset(self::GEMINI_PRICING[$model])) {
951	1220	return self::GEMINI_PRICING[$model];
952	1221	}
953		- return self::GEMINI_PRICING['gemini-2.5-flash'] ?? null;
	1222	+ return self::GEMINI_PRICING['gemini-3.5-flash'] ?? null;
954	1223
	1224	+ case 'openrouter':
	1225	+ // Try specific model first, fallback to default
	1226	+ if ($model && isset(self::OPENROUTER_PRICING[$model])) {
	1227	+ return self::OPENROUTER_PRICING[$model];
	1228	+ }
	1229	+ return self::OPENROUTER_PRICING['openai/gpt-4o-mini'] ?? null;
	1230	+
	1231	+ case 'openai_compatible':
	1232	+ // There is no price table for someone else's server: it may be
	1233	+ // a free local model, an Azure contract or a hosted open model.
	1234	+ // The only honest number is the one the administrator entered,
	1235	+ // as a flat per-1M-token rate applied to both directions.
	1236	+ //
	1237	+ // Read at report time, so changing the rate (or repointing the
	1238	+ // provider at another server) re-costs past rows too. Accepted:
	1239	+ // storing a price per row would mean a schema change for an
	1240	+ // estimate the administrator typed in the first place.
	1241	+ $price = (float) \ThinkRank\Core\Settings::instance()->get('openai_compatible_price_per_million', 0);
	1242	+
	1243	+ return $price > 0 ? ['input' => $price, 'output' => $price] : null;
	1244	+
955	1245	default:
956	1246	return null;
957	1247	}
958	1248	}
		@@ -965,13 +1255,18 @@
965	1255	*/
966	1256	private function get_default_model(string $provider): string {
967	1257	switch ($provider) {
968	1258	case 'openai':
969		- return 'gpt-5-nano';
	1259	+ return \ThinkRank\Core\Settings::DEFAULT_OPENAI_MODEL;
970	1260	case 'claude':
971		- return 'claude-3-7-sonnet-latest'; // Use recommended model alias
	1261	+ return \ThinkRank\Core\Settings::DEFAULT_CLAUDE_MODEL;
972	1262	case 'gemini':
973		- return 'gemini-2.5-flash'; // Keep stable default model
	1263	+ return \ThinkRank\Core\Settings::DEFAULT_GEMINI_MODEL;
	1264	+ case 'openrouter':
	1265	+ return \ThinkRank\Core\Settings::DEFAULT_OPENROUTER_MODEL;
	1266	+ case 'openai_compatible':
	1267	+ // Whatever the user pointed us at; there is no default.
	1268	+ return (string) \ThinkRank\Core\Settings::instance()->get('openai_compatible_model', '');
974	1269	default:
975	1270	return 'unknown';
976	1271	}
977	1272	}
		@@ -990,11 +1285,16 @@
990	1285	$table_name = esc_sql($this->database->get_table('seo_scores'));
991	1286
992	1287	$previous_date_condition = $this->get_previous_period_condition($current_date_condition);
993	1288
	1289	+ // See get_previous_period_data(): no preceding window, no comparison.
	1290	+ if (null === $previous_date_condition) {
	1291	+ return ['content_optimized' => null, 'average_seo_score' => null];
	1292	+ }
	1293	+
994	1294	// Prepare and execute query with proper parameter binding to prevent SQL injection
995	1295	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is properly escaped, date condition is from controlled source
996		- // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Analytics data is real-time and shouldn't be cached
	1296	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Analytics data is real-time and shouldn't be cached
997	1297	$result = $wpdb->get_row(
998	1298	$wpdb->prepare("
999	1299	SELECT
1000	1300	COUNT(DISTINCT post_id) as content_optimized,
		@@ -1017,14 +1317,39 @@
1017	1317	];
1018	1318	}
1019	1319
1020	1320	/**
	1321	+ * Resolve a period key from a legacy SQL date condition string.
	1322	+ * Used internally so the new parameterized helpers can derive the period.
	1323	+ *
	1324	+ * @param string $condition Legacy date condition string
	1325	+ * @return string Period key
	1326	+ */
	1327	+ private function resolve_period_from_condition(string $condition): string {
	1328	+ if (strpos($condition, 'INTERVAL 7') !== false) { return '7d';
	1329	+ }
	1330	+ if (strpos($condition, 'INTERVAL 30') !== false) { return '30d';
	1331	+ }
	1332	+ if (strpos($condition, 'INTERVAL 90') !== false) { return '90d';
	1333	+ }
	1334	+ if (empty(trim($condition))) { return 'all';
	1335	+ }
	1336	+ return '30d';
	1337	+ }
	1338	+
	1339	+ /**
1021	1340	* Convert current period condition to previous period condition
1022	1341	*
	1342	+ * Returns null when there is no preceding window to compare against.
	1343	+ * `all` produces an empty date condition, which used to fall through to a
	1344	+ * hardcoded 30–60 day fallback — so "all time" was compared against an
	1345	+ * arbitrary month and reported a large, meaningless increase. "No
	1346	+ * comparison" is now representable instead of being a parse failure.
	1347	+ *
1023	1348	* @param string $current_condition Current period SQL condition
1024		- * @return string Previous period SQL condition
	1349	+ * @return string|null Previous period SQL condition, or null when none exists
1025	1350	*/
1026		- private function get_previous_period_condition(string $current_condition): string {
	1351	+ private function get_previous_period_condition(string $current_condition): ?string {
1027	1352	// Extract interval from conditions like "AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)"
1028	1353	if (preg_match('/INTERVAL (\d+) (\w+)/', $current_condition, $matches)) {
1029	1354	$interval = (int) $matches[1];
1030	1355	$unit = $matches[2];
		@@ -1036,9 +1361,9 @@
1036	1361	return "AND created_at >= DATE_SUB(NOW(), INTERVAL {$start_interval} {$unit})
1037	1362	AND created_at < DATE_SUB(NOW(), INTERVAL {$end_interval} {$unit})";
1038	1363	}
1039	1364
1040		- // Fallback for unknown conditions
1041		- return "AND created_at >= DATE_SUB(NOW(), INTERVAL 60 DAY)
1042		- AND created_at < DATE_SUB(NOW(), INTERVAL 30 DAY)";
	1365	+ // No interval means no window — 'all'. Comparing every record ever
	1366	+ // against a fabricated 30-day slice is not a trend.
	1367	+ return null;
1043	1368	}
1044	1369	}