← All changes | includes/api/class-usage-analytics-endpoint.php +457 -153 1.0.0 → 2.7.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'],
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,9 @@
417	588	$costs = [
418	589	'openai' => 0,
419	590	'claude' => 0,
420	591	'gemini' => 0,
	592	+ 'openrouter' => 0,
421	593	'total' => 0,
422	594	'by_provider' => []
423	595	];
424	596
		@@ -423,43 +595,51 @@
423	595	];
424	596
425	597	foreach ($usage_data as $usage) {
426	598	$tokens = (int) $usage['tokens_used'];
427		- $provider = $usage['provider'];
	599	+ $provider = (string) $usage['provider'];
428	600
429		- // Estimate 70% input, 30% output tokens
430		- $input_tokens = $tokens * 0.7;
431		- $output_tokens = $tokens * 0.3;
	601	+ // Unknown provider: no pricing table, so it cannot be costed. Skip
	602	+ // rather than let `+=` invent a key that the total below misses.
	603	+ if (!isset($costs[$provider])) {
	604	+ continue;
	605	+ }
432	606
433		- $cost = 0;
	607	+ // Price at the model the request actually used. Reading only the
	608	+ // provider meant every row was costed at that provider's default
	609	+ // model, so this total disagreed with the per-record figures in
	610	+ // the Usage Breakdown tab — by 4.5x on a gpt-4o-mini workload.
	611	+ $metadata = !empty($usage['metadata']) ? json_decode((string) $usage['metadata'], true) : [];
	612	+ $model = is_array($metadata) && !empty($metadata['actual_model'])
	613	+ ? (string) $metadata['actual_model']
	614	+ : $this->get_default_model($provider);
434	615
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;
	616	+ // Single source of truth for per-row pricing, shared with
	617	+ // get_detailed_usage_breakdown() so both tabs always agree.
	618	+ $costs[$provider] += $this->calculate_record_cost($provider, $tokens, $model);
	619	+ }
	620	+
	621	+ $costs['total'] = $costs['openai'] + $costs['claude'] + $costs['gemini'] + $costs['openrouter'];
	622	+
	623	+ // Report only providers that actually incurred cost. Emitting all four
	624	+ // unconditionally meant a site with no AI usage rendered four ranked
	625	+ // rows at "$0.0000 (0%)" — reading as "four providers were used and
	626	+ // each was free" — and made the panel's own "No provider cost data"
	627	+ // empty state unreachable.
	628	+ $costs['by_provider'] = [];
	629	+ foreach (['openai', 'claude', 'gemini', 'openrouter'] as $provider) {
	630	+ if ($costs[$provider] <= 0) {
	631	+ continue;
441	632	}
	633	+
	634	+ $costs['by_provider'][$provider] = [
	635	+ 'cost' => round($costs[$provider], 4),
	636	+ 'percentage' => $costs['total'] > 0
	637	+ ? round(($costs[$provider] / $costs['total']) * 100, 1)
	638	+ : 0
	639	+ ];
442	640	}
443	641
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	642	return $costs;
463	643	}
464	644
465	645	/**
		@@ -491,35 +671,63 @@
491	671
492	672	// Get table name and escape it properly (table names cannot be parameterized)
493	673	$table_name = esc_sql($this->database->get_table('ai_usage'));
494	674
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		- ";
	675	+ $cutoff = $this->get_date_cutoff($this->resolve_period_from_condition($date_condition));
505	676
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		- );
	677	+ if ($cutoff !== null) {
	678	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	679	+ $usage_data = $wpdb->get_results(
	680	+ $wpdb->prepare(
	681	+ // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table_name is escaped via esc_sql().
	682	+ "SELECT provider, action, tokens_used, metadata, created_at FROM `{$table_name}` WHERE user_id = %d AND created_at >= %s",
	683	+ $user_id,
	684	+ $cutoff
	685	+ ),
	686	+ ARRAY_A
	687	+ );
	688	+ } else {
	689	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	690	+ $usage_data = $wpdb->get_results(
	691	+ $wpdb->prepare(
	692	+ // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table_name is escaped via esc_sql().
	693	+ "SELECT provider, action, tokens_used, metadata, created_at FROM `{$table_name}` WHERE user_id = %d",
	694	+ $user_id
	695	+ ),
	696	+ ARRAY_A
	697	+ );
	698	+ }
513	699
514	700	if (empty($usage_data)) {
	701	+ // Must return the same shape as the populated path below —
	702	+ // get_overview_metrics() reads every key unconditionally, so a
	703	+ // short array here surfaces as undefined-key warnings and null
	704	+ // fields for any user with no AI usage yet (i.e. a fresh install).
	705	+ // The values mirror what the loop below produces for zero rows.
	706	+ //
	707	+ // The change fields are COMPUTED here rather than hardcoded to 0.
	708	+ // An empty current window does not mean "nothing changed": a user
	709	+ // whose usage fell from five actions last month to none this month
	710	+ // was shown a 0 — rendered as the same em-dash a genuinely flat
	711	+ // period gets — instead of the -100% that actually happened.
	712	+ $previous = $this->get_previous_period_data($user_id, $date_condition);
	713	+
515	714	return [
516	715	'total_actions' => 0,
517	716	'total_tokens' => 0,
518	717	'feature_breakdown' => [],
	718	+ 'features_used_count' => 0,
	719	+ 'most_used_feature' => '',
	720	+ 'most_used_count' => 0,
519	721	'usage_data' => [],
520		- 'cost_change' => 0,
521		- 'time_saved_change' => 0
	722	+ 'cost_change' => $this->calculate_percentage_change(
	723	+ array_key_exists('total_cost', $previous) ? $previous['total_cost'] : 0,
	724	+ 0.0
	725	+ ),
	726	+ 'time_saved_change' => $this->calculate_percentage_change(
	727	+ array_key_exists('time_saved', $previous) ? $previous['time_saved'] : 0,
	728	+ 0.0
	729	+ )
522	730	];
523	731	}
524	732
525	733	// Calculate feature breakdown and new metrics
		@@ -551,20 +759,24 @@
551	759	$most_used_count = $count;
552	760	}
553	761	}
554	762
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;
	763	+ // No success rate here on purpose. It used to be
	764	+ // `$total_actions > 0 ? 100 : 0` — a constant presented as a
	765	+ // measurement, and one that could only ever read 100% or 0%. Failed
	766	+ // AI calls are never written to this table, so there is nothing to
	767	+ // compute a rate from; the KPI card is gone until there is.
558	768
559	769	// Calculate changes from previous period
560	770	$previous_period_data = $this->get_previous_period_data($user_id, $date_condition);
	771	+ // Note the lack of `?? 0`: a null here means "no previous period",
	772	+ // and coalescing it to zero would turn that back into a fake 100%.
561	773	$cost_change = $this->calculate_percentage_change(
562		- $previous_period_data['total_cost'] ?? 0,
	774	+ array_key_exists('total_cost', $previous_period_data) ? $previous_period_data['total_cost'] : 0,
563	775	$this->calculate_total_cost($usage_data)
564	776	);
565	777	$time_saved_change = $this->calculate_percentage_change(
566		- $previous_period_data['time_saved'] ?? 0,
	778	+ array_key_exists('time_saved', $previous_period_data) ? $previous_period_data['time_saved'] : 0,
567	779	$this->calculate_time_saved($feature_breakdown)
568	780	);
569	781
570	782	return [
		@@ -573,9 +785,8 @@
573	785	'feature_breakdown' => $feature_breakdown,
574	786	'features_used_count' => $features_used_count,
575	787	'most_used_feature' => $most_used_feature,
576	788	'most_used_count' => $most_used_count,
577		- 'success_rate' => $success_rate,
578	789	'usage_data' => $usage_data,
579	790	'cost_change' => $cost_change,
580	791	'time_saved_change' => $time_saved_change
581	792	];
		@@ -590,45 +801,65 @@
590	801	*/
591	802	private function get_seo_metrics(int $user_id, string $date_condition): array {
592	803	global $wpdb;
593	804
594		- // Get table name and escape it properly (table names cannot be parameterized)
595	805	$table_name = esc_sql($this->database->get_table('seo_scores'));
	806	+ $cutoff = $this->get_date_cutoff($this->resolve_period_from_condition($date_condition));
596	807
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		- ";
	808	+ if ($cutoff !== null) {
	809	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	810	+ $result = $wpdb->get_row(
	811	+ $wpdb->prepare(
	812	+ // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table_name is escaped via esc_sql().
	813	+ "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",
	814	+ $user_id,
	815	+ $cutoff
	816	+ ),
	817	+ ARRAY_A
	818	+ );
	819	+ } else {
	820	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	821	+ $result = $wpdb->get_row(
	822	+ $wpdb->prepare(
	823	+ // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table_name is escaped via esc_sql().
	824	+ "SELECT COUNT(DISTINCT post_id) as content_optimized, AVG(overall_score) as average_score FROM `{$table_name}` WHERE user_id = %d",
	825	+ $user_id
	826	+ ),
	827	+ ARRAY_A
	828	+ );
	829	+ }
605	830
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		- );
	831	+ if (!$result || (int) $result['content_optimized'] === 0) {
	832	+ // Same reasoning as the empty branch in get_ai_usage_metrics():
	833	+ // an empty current window is not "no change". A user who
	834	+ // optimized three posts last month and none this month should
	835	+ // see -100%, not the em-dash a flat period gets — and for `all`
	836	+ // there is no previous window, so the change is null.
	837	+ $previous = $this->get_previous_seo_data($user_id, $date_condition);
613	838
614		- if (!$result || $result['content_optimized'] == 0) {
615	839	return [
616	840	'content_optimized' => 0,
617	841	'average_seo_score' => 0,
618		- 'content_optimized_change' => 0,
619		- 'seo_score_change' => 0
	842	+ 'content_optimized_change' => $this->calculate_percentage_change(
	843	+ array_key_exists('content_optimized', $previous) ? $previous['content_optimized'] : 0,
	844	+ 0.0
	845	+ ),
	846	+ 'seo_score_change' => $this->calculate_percentage_change(
	847	+ array_key_exists('average_seo_score', $previous) ? $previous['average_seo_score'] : 0,
	848	+ 0.0
	849	+ )
620	850	];
621	851	}
622	852
623	853	// Calculate changes from previous period
624	854	$previous_seo_data = $this->get_previous_seo_data($user_id, $date_condition);
	855	+ // As above: no `?? 0`, so a null "no previous period" survives.
625	856	$content_optimized_change = $this->calculate_percentage_change(
626		- $previous_seo_data['content_optimized'] ?? 0,
	857	+ array_key_exists('content_optimized', $previous_seo_data) ? $previous_seo_data['content_optimized'] : 0,
627	858	(int) $result['content_optimized']
628	859	);
629	860	$seo_score_change = $this->calculate_percentage_change(
630		- $previous_seo_data['average_seo_score'] ?? 0,
	861	+ array_key_exists('average_seo_score', $previous_seo_data) ? $previous_seo_data['average_seo_score'] : 0,
631	862	round((float) $result['average_score'], 1)
632	863	);
633	864
634	865	return [
		@@ -648,25 +879,32 @@
648	879	*/
649	880	private function get_content_brief_metrics(int $user_id, string $date_condition): array {
650	881	global $wpdb;
651	882
652		- // Get table name and escape it properly (table names cannot be parameterized)
653	883	$table_name = esc_sql($this->database->get_table('content_briefs'));
	884	+ $cutoff = $this->get_date_cutoff($this->resolve_period_from_condition($date_condition));
654	885
655		- // Get content briefs count
656		- $base_query = "
657		- SELECT COUNT(*) as total_briefs
658		- FROM `{$table_name}`
659		- WHERE user_id = %d
660		- ";
	886	+ if ($cutoff !== null) {
	887	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	888	+ $result = $wpdb->get_var(
	889	+ $wpdb->prepare(
	890	+ // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table_name is escaped via esc_sql().
	891	+ "SELECT COUNT(*) as total_briefs FROM `{$table_name}` WHERE user_id = %d AND created_at >= %s",
	892	+ $user_id,
	893	+ $cutoff
	894	+ )
	895	+ );
	896	+ } else {
	897	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	898	+ $result = $wpdb->get_var(
	899	+ $wpdb->prepare(
	900	+ // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table_name is escaped via esc_sql().
	901	+ "SELECT COUNT(*) as total_briefs FROM `{$table_name}` WHERE user_id = %d",
	902	+ $user_id
	903	+ )
	904	+ );
	905	+ }
661	906
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	907	return [
670	908	'total_briefs' => (int) $result ?: 0
671	909	];
672	910	}
		@@ -676,14 +914,23 @@
676	914	*
677	915	* @param WP_REST_Request $request Request object
678	916	* @return WP_REST_Response|WP_Error Response object
679	917	*/
680		- public function get_usage_breakdown(WP_REST_Request $request): WP_REST_Response|WP_Error {
	918	+ public function get_usage_breakdown(WP_REST_Request $request) {
681	919	try {
682		- $user_id = get_current_user_id();
	920	+ // Mirrors get_overview_metrics(). The two endpoints declared the
	921	+ // same `user_id` argument but only overview honoured it, so the
	922	+ // same query string described two different users depending on
	923	+ // which one you asked. check_permissions() already requires
	924	+ // manage_options before another user's id is accepted.
	925	+ $user_id = $request->get_param('user_id') ?: get_current_user_id();
683	926	$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));
	927	+ // `(int)` binds tighter than `??`, so `(int) null` is 0 and the
	928	+ // `?? 20` fallback was unreachable — per_page silently defaulted to
	929	+ // the max(10, 0) floor of 10 rather than the 20 it advertises, and
	930	+ // page to max(1, 0) = 1 by luck rather than intent (#394).
	931	+ $page = max(1, (int) ($request->get_param('page') ?? 1));
	932	+ $per_page = min(100, max(10, (int) ($request->get_param('per_page') ?? 20)));
686	933	$offset = ($page - 1) * $per_page;
687	934
688	935	// Get date range for queries
689	936	$date_condition = $this->get_date_condition($period);
		@@ -699,9 +946,10 @@
699	946	'pagination' => [
700	947	'page' => $page,
701	948	'per_page' => $per_page,
702	949	'total_records' => $total_records,
703		- 'total_pages' => ceil($total_records / $per_page)
	950	+ // (int) so it serialises as 2, not 2.0.
	951	+ 'total_pages' => $per_page > 0 ? (int) ceil($total_records / $per_page) : 0
704	952	],
705	953	'period' => $period
706	954	]
707	955	], 200);
		@@ -720,14 +968,16 @@
720	968	*
721	969	* @param WP_REST_Request $request Request object
722	970	* @return WP_REST_Response|WP_Error Response object
723	971	*/
724		- public function get_cost_analysis(WP_REST_Request $request): WP_REST_Response|WP_Error {
	972	+ public function get_cost_analysis(WP_REST_Request $request) {
	973	+ // Return 200 with success:false so the frontend can render an
	974	+ // "unavailable" state — apiFetch rejects on non-2xx, which would
	975	+ // otherwise surface as a generic hard error.
725	976	return new WP_REST_Response([
726		- 'success' => true,
727		- 'data' => [
728		- 'message' => 'Cost analysis endpoint - to be implemented in Phase 2'
729		- ]
	977	+ 'success' => false,
	978	+ 'data' => null,
	979	+ 'message' => 'Cost analysis is not yet implemented.'
730	980	], 200);
731	981	}
732	982
733	983	/**
		@@ -747,14 +997,22 @@
747	997	* @param float $old_value Previous period value
748	998	* @param float $new_value Current period value
749	999	* @return float Percentage change
750	1000	*/
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;
	1001	+ private function calculate_percentage_change($old_value, float $new_value): ?float {
	1002	+ // No previous period at all (the 'all' range).
	1003	+ if (null === $old_value) {
	1004	+ return null;
754	1005	}
755	1006
756		- return round((($new_value - $old_value) / $old_value) * 100, 1);
	1007	+ if ((float) $old_value === 0.0) {
	1008	+ // Growth from nothing has no percentage. Reporting a flat 100%
	1009	+ // dressed it up as a measured change; null lets the UI say "new"
	1010	+ // (or say nothing) instead of inventing a number.
	1011	+ return $new_value > 0 ? null : 0.0;
	1012	+ }
	1013	+
	1014	+ return round((($new_value - (float) $old_value) / (float) $old_value) * 100, 1);
757	1015	}
758	1016
759	1017	/**
760	1018	* Get previous period data for comparison
		@@ -771,17 +1029,24 @@
771	1029
772	1030	// Extract the interval from current date condition to calculate previous period
773	1031	$previous_date_condition = $this->get_previous_period_condition($current_date_condition);
774	1032
	1033	+ // No preceding window: report "not comparable" rather than querying a
	1034	+ // made-up one.
	1035	+ if (null === $previous_date_condition) {
	1036	+ return ['total_cost' => null, 'time_saved' => null];
	1037	+ }
	1038	+
775	1039	// Prepare and execute query with proper parameter binding to prevent SQL injection
776	1040	// 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
	1041	+ // 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	1042	$usage_data = $wpdb->get_results(
779	1043	$wpdb->prepare("
780	1044	SELECT
781	1045	provider,
782	1046	action,
783		- tokens_used
	1047	+ tokens_used,
	1048	+ metadata
784	1049	FROM `{$table_name}`
785	1050	WHERE user_id = %d
786	1051	{$previous_date_condition}
787	1052	", $user_id),
		@@ -838,11 +1103,11 @@
838	1103	ORDER BY created_at DESC
839	1104	LIMIT %d OFFSET %d
840	1105	";
841	1106
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
	1107	+ // 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	1108	$usage_data = $wpdb->get_results(
844		- // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- SQL is properly prepared with placeholders
	1109	+ // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- SQL is properly prepared with placeholders
845	1110	$wpdb->prepare($sql, $user_id, $limit, $offset),
846	1111	ARRAY_A
847	1112	);
848	1113
		@@ -887,11 +1152,11 @@
887	1152	WHERE user_id = %d
888	1153	{$date_condition}
889	1154	";
890	1155
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
	1156	+ // 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	1157	$count = $wpdb->get_var(
893		- // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- SQL is properly prepared with placeholders
	1158	+ // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- SQL is properly prepared with placeholders
894	1159	$wpdb->prepare($sql, $user_id)
895	1160	);
896	1161
897	1162	return (int) $count;
		@@ -917,9 +1182,9 @@
917	1182	$input_tokens = $tokens_used * 0.7;
918	1183	$output_tokens = $tokens_used * 0.3;
919	1184
920	1185	return (($input_tokens / 1000000) * $pricing['input']) +
921		- (($output_tokens / 1000000) * $pricing['output']);
	1186	+ (($output_tokens / 1000000) * $pricing['output']);
922	1187	}
923	1188
924	1189	/**
925	1190	* Get pricing for any model with intelligent fallbacks
		@@ -941,10 +1206,10 @@
941	1206	// Try specific model first, fallback to recommended default
942	1207	if ($model && isset(self::CLAUDE_PRICING[$model])) {
943	1208	return self::CLAUDE_PRICING[$model];
944	1209	}
945		- return self::CLAUDE_PRICING['claude-3-7-sonnet-latest'] ??
946		- self::CLAUDE_PRICING['claude-3-5-sonnet-latest'] ?? null;
	1210	+ return self::CLAUDE_PRICING['claude-sonnet-5'] ??
	1211	+ self::CLAUDE_PRICING['claude-sonnet-4-6'] ?? null;
947	1212
948	1213	case 'gemini':
949	1214	// Try specific model first, fallback to default
950	1215	if ($model && isset(self::GEMINI_PRICING[$model])) {
		@@ -949,10 +1214,17 @@
949	1214	// Try specific model first, fallback to default
950	1215	if ($model && isset(self::GEMINI_PRICING[$model])) {
951	1216	return self::GEMINI_PRICING[$model];
952	1217	}
953		- return self::GEMINI_PRICING['gemini-2.5-flash'] ?? null;
	1218	+ return self::GEMINI_PRICING['gemini-3.5-flash'] ?? null;
954	1219
	1220	+ case 'openrouter':
	1221	+ // Try specific model first, fallback to default
	1222	+ if ($model && isset(self::OPENROUTER_PRICING[$model])) {
	1223	+ return self::OPENROUTER_PRICING[$model];
	1224	+ }
	1225	+ return self::OPENROUTER_PRICING['openai/gpt-4o-mini'] ?? null;
	1226	+
955	1227	default:
956	1228	return null;
957	1229	}
958	1230	}
		@@ -965,13 +1237,15 @@
965	1237	*/
966	1238	private function get_default_model(string $provider): string {
967	1239	switch ($provider) {
968	1240	case 'openai':
969		- return 'gpt-5-nano';
	1241	+ return \ThinkRank\Core\Settings::DEFAULT_OPENAI_MODEL;
970	1242	case 'claude':
971		- return 'claude-3-7-sonnet-latest'; // Use recommended model alias
	1243	+ return \ThinkRank\Core\Settings::DEFAULT_CLAUDE_MODEL;
972	1244	case 'gemini':
973		- return 'gemini-2.5-flash'; // Keep stable default model
	1245	+ return \ThinkRank\Core\Settings::DEFAULT_GEMINI_MODEL;
	1246	+ case 'openrouter':
	1247	+ return \ThinkRank\Core\Settings::DEFAULT_OPENROUTER_MODEL;
974	1248	default:
975	1249	return 'unknown';
976	1250	}
977	1251	}
		@@ -990,11 +1264,16 @@
990	1264	$table_name = esc_sql($this->database->get_table('seo_scores'));
991	1265
992	1266	$previous_date_condition = $this->get_previous_period_condition($current_date_condition);
993	1267
	1268	+ // See get_previous_period_data(): no preceding window, no comparison.
	1269	+ if (null === $previous_date_condition) {
	1270	+ return ['content_optimized' => null, 'average_seo_score' => null];
	1271	+ }
	1272	+
994	1273	// Prepare and execute query with proper parameter binding to prevent SQL injection
995	1274	// 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
	1275	+ // 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	1276	$result = $wpdb->get_row(
998	1277	$wpdb->prepare("
999	1278	SELECT
1000	1279	COUNT(DISTINCT post_id) as content_optimized,
		@@ -1017,14 +1296,39 @@
1017	1296	];
1018	1297	}
1019	1298
1020	1299	/**
	1300	+ * Resolve a period key from a legacy SQL date condition string.
	1301	+ * Used internally so the new parameterized helpers can derive the period.
	1302	+ *
	1303	+ * @param string $condition Legacy date condition string
	1304	+ * @return string Period key
	1305	+ */
	1306	+ private function resolve_period_from_condition(string $condition): string {
	1307	+ if (strpos($condition, 'INTERVAL 7') !== false) { return '7d';
	1308	+ }
	1309	+ if (strpos($condition, 'INTERVAL 30') !== false) { return '30d';
	1310	+ }
	1311	+ if (strpos($condition, 'INTERVAL 90') !== false) { return '90d';
	1312	+ }
	1313	+ if (empty(trim($condition))) { return 'all';
	1314	+ }
	1315	+ return '30d';
	1316	+ }
	1317	+
	1318	+ /**
1021	1319	* Convert current period condition to previous period condition
1022	1320	*
	1321	+ * Returns null when there is no preceding window to compare against.
	1322	+ * `all` produces an empty date condition, which used to fall through to a
	1323	+ * hardcoded 30–60 day fallback — so "all time" was compared against an
	1324	+ * arbitrary month and reported a large, meaningless increase. "No
	1325	+ * comparison" is now representable instead of being a parse failure.
	1326	+ *
1023	1327	* @param string $current_condition Current period SQL condition
1024		- * @return string Previous period SQL condition
	1328	+ * @return string|null Previous period SQL condition, or null when none exists
1025	1329	*/
1026		- private function get_previous_period_condition(string $current_condition): string {
	1330	+ private function get_previous_period_condition(string $current_condition): ?string {
1027	1331	// Extract interval from conditions like "AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)"
1028	1332	if (preg_match('/INTERVAL (\d+) (\w+)/', $current_condition, $matches)) {
1029	1333	$interval = (int) $matches[1];
1030	1334	$unit = $matches[2];
		@@ -1036,9 +1340,9 @@
1036	1340	return "AND created_at >= DATE_SUB(NOW(), INTERVAL {$start_interval} {$unit})
1037	1341	AND created_at < DATE_SUB(NOW(), INTERVAL {$end_interval} {$unit})";
1038	1342	}
1039	1343
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)";
	1344	+ // No interval means no window — 'all'. Comparing every record ever
	1345	+ // against a fabricated 30-day slice is not a trend.
	1346	+ return null;
1043	1347	}
1044	1348	}