thinkrank / includes / api / class-usage-analytics-endpoint.php

class-usage-analytics-endpoint.php in ThinkRank AI SEO – AI SEO Plugin for WordPress: Schema, XML Sitemaps, Meta Tags, Search Console & Local SEO 1.32.0, at includes/api/class-usage-analytics-endpoint.php

1,206 lines 43.2 KB

Raw Download Zip

1	<?php
2	/**
3	* Usage Analytics REST API Endpoint
4	*
5	* Handles REST API endpoints for usage analytics data including AI usage, costs, and performance metrics
6	*
7	* @package ThinkRank\API
8	* @since 1.0.0
9	*/
10
11	declare(strict_types=1);
12
13	namespace ThinkRank\API;
14
15	use ThinkRank\Core\Database;
16	use ThinkRank\API\Traits\API_Cache;
17	use WP_REST_Request;
18	use WP_REST_Response;
19	use WP_Error;
20
21	// Prevent direct access
22	if (!defined('ABSPATH')) {
23	exit;
24	}
25
26	// Load API Cache trait
27	require_once THINKRANK_PLUGIN_DIR . 'includes/api/traits/trait-api-cache.php';
28
29	/**
30	* Usage Analytics Endpoint Class
31	*
32	* Provides REST API endpoints for:
33	* - /wp-json/thinkrank/v1/analytics/overview
34	* - /wp-json/thinkrank/v1/analytics/usage
35	* - /wp-json/thinkrank/v1/analytics/costs
36	*
37	* @since 1.0.0
38	*/
39	class Usage_Analytics_Endpoint {
40
41	use API_Cache;
42
43	/**
44	* Database instance
45	*
46	* @var Database
47	*/
48	private Database $database;
49
50	/**
51	* OpenAI pricing per 1M tokens (USD)
52	*/
53	private const OPENAI_PRICING = [
54	// GPT-5 models (official pricing from OpenAI)
55	'gpt-5' => [
56	'input' => 1.25,
57	'output' => 10.00
58	],
59	'gpt-5-mini' => [
60	'input' => 0.25,
61	'output' => 2.00
62	],
63	'gpt-5-nano' => [
64	'input' => 0.05,
65	'output' => 0.40
66	],
67	// GPT-4 models
68	'gpt-4.1' => [
69	'input' => 2.50,
70	'output' => 10.00
71	],
72	'gpt-4o' => [
73	'input' => 2.50,
74	'output' => 10.00
75	],
76	'gpt-4-turbo' => [
77	'input' => 10.00,
78	'output' => 30.00
79	],
80	// O3 models
81	'o3-mini' => [
82	'input' => 1.25,
83	'output' => 5.00
84	],
85	// Mini models
86	'gpt-4o-mini' => [
87	'input' => 0.15,
88	'output' => 0.60
89	]
90	];
91
92	/**
93	* Claude pricing per 1M tokens (USD)
94	* Model IDs sourced from https://docs.anthropic.com/en/docs/about-claude/models
95	*/
96	private const CLAUDE_PRICING = [
97	// Current models (recommended)
98	'claude-opus-4-8' => [
99	'input' => 5.00,
100	'output' => 25.00
101	],
102	'claude-sonnet-5' => [
103	'input' => 3.00,
104	'output' => 15.00
105	],
106	'claude-haiku-4-5' => [
107	'input' => 1.00,
108	'output' => 5.00
109	],
110	// Claude 4.x models
111	'claude-opus-4-6' => [
112	'input' => 5.00,
113	'output' => 25.00
114	],
115	'claude-sonnet-4-6' => [
116	'input' => 3.00,
117	'output' => 15.00
118	],
119	// Claude 4.5 models
120	'claude-haiku-4-5-20251001' => [
121	'input' => 1.00,
122	'output' => 5.00
123	],
124	// Claude 3.5 models (legacy)
125	'claude-3-5-sonnet-20241022' => [
126	'input' => 3.00,
127	'output' => 15.00
128	],
129	'claude-3-5-haiku-20241022' => [
130	'input' => 0.80,
131	'output' => 4.00
132	],
133	'claude-3-opus-20240229' => [
134	'input' => 15.00,
135	'output' => 75.00
136	]
137	];
138
139	/**
140	* Gemini pricing per 1M tokens (USD)
141	*/
142	private const GEMINI_PRICING = [
143	// Gemini 3.x models (tiered models use the base <=200k-token rate)
144	'gemini-3.1-pro' => [
145	'input' => 2.00,
146	'output' => 12.00
147	],
148	'gemini-3.5-flash' => [
149	'input' => 1.50,
150	'output' => 9.00
151	],
152	'gemini-3.1-flash-lite' => [
153	'input' => 0.25,
154	'output' => 1.50
155	],
156	// Gemini 2.5 models
157	'gemini-2.5-flash' => [
158	'input' => 0.30,
159	'output' => 2.50
160	],
161	'gemini-2.5-flash-lite' => [
162	'input' => 0.10,
163	'output' => 0.40
164	],
165	'gemini-2.5-pro' => [
166	'input' => 1.25,
167	'output' => 10.00
168	],
169	// Gemini 2.0 models
170	'gemini-2.0-flash' => [
171	'input' => 0.10,
172	'output' => 0.40
173	],
174	// Gemini 1.5 models
175	'gemini-1.5-flash' => [
176	'input' => 0.075,
177	'output' => 0.30
178	],
179	'gemini-1.5-pro' => [
180	'input' => 1.25,
181	'output' => 5.00
182	]
183	];
184
185	/**
186	* OpenRouter pricing per 1M tokens (USD)
187	*
188	* OpenRouter passes through each upstream model's pricing; these are
189	* representative rates for the curated model list used for cost estimates.
190	*/
191	private const OPENROUTER_PRICING = [
192	'openai/gpt-4o-mini' => [
193	'input' => 0.15,
194	'output' => 0.60
195	],
196	'anthropic/claude-3.5-sonnet' => [
197	'input' => 3.00,
198	'output' => 15.00
199	],
200	'google/gemini-2.0-flash-001' => [
201	'input' => 0.10,
202	'output' => 0.40
203	],
204	'meta-llama/llama-3.3-70b-instruct' => [
205	'input' => 0.12,
206	'output' => 0.30
207	],
208	'deepseek/deepseek-chat' => [
209	'input' => 0.14,
210	'output' => 0.28
211	]
212	];
213
214	/**
215	* Time saved estimates per action (minutes)
216	*/
217	private const TIME_SAVED_ESTIMATES = [
218	'seo_metadata' => 20,
219	'content_analysis' => 15,
220	'content_brief' => 45,
221	'seo_score' => 10
222	];
223
224	/**
225	* Constructor
226	*/
227	public function __construct() {
228	$this->database = new Database();
229
230	// Configure caching for analytics endpoints
231	$this->set_cache_prefix('thinkrank_analytics_');
232	$this->set_cache_duration(600); // 10 minutes for analytics data
233
234	// Set up cache invalidation hooks
235	$this->setup_cache_invalidation();
236	}
237
238	/**
239	* Register REST API routes
240	*
241	* @return void
242	*/
243	public function register_routes(): void {
244	// Overview metrics endpoint
245	register_rest_route('thinkrank/v1', '/analytics/overview', [
246	'methods' => 'GET',
247	'callback' => [$this, 'get_overview_metrics'],
248	'permission_callback' => [$this, 'check_permissions'],
249	'args' => [
250	'period' => [
251	'default' => '30d',
252	'enum' => ['7d', '30d', '90d', 'all'],
253	'sanitize_callback' => 'sanitize_key'
254	],
255	'user_id' => [
256	'default' => 0,
257	'type' => 'integer',
258	'sanitize_callback' => 'absint'
259	]
260	]
261	]);
262
263	// Usage breakdown endpoint
264	register_rest_route('thinkrank/v1', '/analytics/usage', [
265	'methods' => 'GET',
266	'callback' => [$this, 'get_usage_breakdown'],
267	'permission_callback' => [$this, 'check_permissions'],
268	'args' => [
269	'period' => [
270	'default' => '30d',
271	'enum' => ['7d', '30d', '90d', 'all'],
272	'sanitize_callback' => 'sanitize_key'
273	],
274	'group_by' => [
275	'default' => 'day',
276	'enum' => ['day', 'week', 'month'],
277	'sanitize_callback' => 'sanitize_key'
278	],
279	'user_id' => [
280	'default' => 0,
281	'type' => 'integer',
282	'sanitize_callback' => 'absint'
283	]
284	]
285	]);
286
287	// Cost analysis endpoint
288	register_rest_route('thinkrank/v1', '/analytics/costs', [
289	'methods' => 'GET',
290	'callback' => [$this, 'get_cost_analysis'],
291	'permission_callback' => [$this, 'check_permissions'],
292	'args' => [
293	'period' => [
294	'default' => '30d',
295	'enum' => ['7d', '30d', '90d', 'all'],
296	'sanitize_callback' => 'sanitize_key'
297	],
298	'provider' => [
299	'default' => 'all',
300	'enum' => ['all', 'openai', 'claude', 'gemini', 'openrouter'],
301	'sanitize_callback' => 'sanitize_key'
302	],
303	'user_id' => [
304	'default' => 0,
305	'type' => 'integer',
306	'sanitize_callback' => 'absint'
307	]
308	]
309	]);
310	}
311
312	/**
313	* Get overview metrics
314	*
315	* @param WP_REST_Request $request Request object
316	* @return WP_REST_Response\|WP_Error Response object
317	*/
318	public function get_overview_metrics(WP_REST_Request $request) {
319	$period = $request->get_param('period');
320	$user_id = $request->get_param('user_id') ?: get_current_user_id();
321
322	try {
323	// Use cached response wrapper for performance
324	$response_data = $this->cached_response(
325	'overview_metrics',
326	function() use ($period, $user_id) {
327	// Get date range for queries
328	$date_condition = $this->get_date_condition($period);
329
330	// Get AI usage metrics
331	$ai_metrics = $this->get_ai_usage_metrics($user_id, $date_condition);
332
333	// Get SEO metrics
334	$seo_metrics = $this->get_seo_metrics($user_id, $date_condition);
335
336	// Get content brief metrics
337	$brief_metrics = $this->get_content_brief_metrics($user_id, $date_condition);
338
339	// Calculate costs
340	$cost_data = $this->calculate_costs($ai_metrics['usage_data'] ?? []);
341
342	// Calculate time saved
343	$time_saved = $this->calculate_time_saved($ai_metrics['feature_breakdown'] ?? []);
344
345	return [
346	'success' => true,
347	'data' => [
348	'content_optimized' => $seo_metrics['content_optimized'],
349	'content_optimized_change' => $seo_metrics['content_optimized_change'],
350	'average_seo_score' => $seo_metrics['average_seo_score'],
351	'seo_score_change' => $seo_metrics['seo_score_change'],
352	'total_tokens' => $ai_metrics['total_tokens'],
353	'total_cost' => $cost_data['total'],
354	'cost_change' => $ai_metrics['cost_change'],
355	'time_saved' => $time_saved,
356	'time_saved_change' => $ai_metrics['time_saved_change'],
357	'ai_actions' => $ai_metrics['total_actions'],
358	'features_used_count' => $ai_metrics['features_used_count'],
359	'most_used_feature' => $ai_metrics['most_used_feature'],
360	'most_used_count' => $ai_metrics['most_used_count'],
361	'success_rate' => $ai_metrics['success_rate'],
362	'content_briefs' => $brief_metrics['total_briefs'],
363	'feature_breakdown' => $ai_metrics['feature_breakdown'],
364	'provider_breakdown' => $cost_data['by_provider']
365	],
366	'period' => $period,
367	'generated_at' => current_time('c')
368	];
369	},
370	['period' => $period],
371	null, // Use default cache duration
372	$user_id
373	);
374
375	return new WP_REST_Response($response_data, 200);
376
377	} catch (\Exception $e) {
378	return new WP_Error(
379	'analytics_error',
380	'Failed to retrieve analytics data: ' . $e->getMessage(),
381	['status' => 500]
382	);
383	}
384	}
385
386	/**
387	* Check permissions for analytics endpoints
388	*
389	* @param WP_REST_Request $request Request object
390	* @return bool\|WP_Error Permission result
391	*/
392	public function check_permissions(WP_REST_Request $request) {
393	// Check if user is logged in
394	if (!is_user_logged_in()) {
395	return new WP_Error(
396	'not_logged_in',
397	'You must be logged in to view analytics data.',
398	['status' => 401]
399	);
400	}
401
402	// Check if user can edit posts (basic content management capability)
403	if (!current_user_can('edit_posts')) {
404	return new WP_Error(
405	'insufficient_permissions',
406	'You do not have permission to view analytics data.',
407	['status' => 403]
408	);
409	}
410
411	// If requesting another user's data, check admin permissions
412	$requested_user_id = $request->get_param('user_id');
413	if ($requested_user_id && $requested_user_id !== get_current_user_id()) {
414	if (!current_user_can('manage_options')) {
415	return new WP_Error(
416	'insufficient_permissions',
417	'You do not have permission to view other users\' analytics data.',
418	['status' => 403]
419	);
420	}
421	}
422
423	return true;
424	}
425
426	/**
427	* Set up cache invalidation hooks
428	*
429	* @since 1.0.0
430	* @return void
431	*/
432	private function setup_cache_invalidation(): void {
433	// Invalidate analytics cache when AI usage is logged
434	add_action('thinkrank_ai_usage_logged', [$this, 'invalidate_analytics_cache']);
435
436	// Invalidate analytics cache when SEO scores are updated
437	add_action('thinkrank_seo_score_updated', [$this, 'invalidate_analytics_cache']);
438
439	// Invalidate analytics cache when content briefs are created
440	add_action('thinkrank_content_brief_created', [$this, 'invalidate_analytics_cache']);
441	}
442
443	/**
444	* Invalidate analytics cache
445	*
446	* @since 1.0.0
447	* @return void
448	*/
449	public function invalidate_analytics_cache(): void {
450	// Clear all analytics cache entries
451	$this->invalidate_cache_pattern('thinkrank_analytics_*');
452	}
453
454	/**
455	* Get the cutoff datetime string for a period.
456	* Returns null for 'all' (no date restriction).
457	*
458	* @param string $period Period string
459	* @return string\|null Cutoff datetime in MySQL format, or null for all time
460	*/
461	private function get_date_cutoff(string $period): ?string {
462	switch ($period) {
463	case '7d':
464	$days = 7;
465	break;
466	case '30d':
467	$days = 30;
468	break;
469	case '90d':
470	$days = 90;
471	break;
472	case 'all':
473	$days = null;
474	break;
475	default:
476	$days = 30;
477	}
478	if ($days === null) {
479	return null;
480	}
481	return gmdate('Y-m-d H:i:s', strtotime("-{$days} days"));
482	}
483
484	/**
485	* Get date condition for SQL queries
486	*
487	* @deprecated Use get_date_cutoff() with parameterized queries instead.
488	* Kept for back-compat with get_previous_period_condition() parsing.
489	*
490	* @param string $period Period string
491	* @return string SQL date condition
492	*/
493	private function get_date_condition(string $period): string {
494	switch ($period) {
495	case '7d':
496	return "AND created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)";
497	case '30d':
498	return "AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)";
499	case '90d':
500	return "AND created_at >= DATE_SUB(NOW(), INTERVAL 90 DAY)";
501	case 'all':
502	return "";
503	default:
504	return "AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)";
505	}
506	}
507
508	/**
509	* Calculate costs from usage data
510	*
511	* @param array $usage_data Usage data array
512	* @return array Cost breakdown
513	*/
514	private function calculate_costs(array $usage_data): array {
515	$costs = [
516	'openai' => 0,
517	'claude' => 0,
518	'gemini' => 0,
519	'openrouter' => 0,
520	'total' => 0,
521	'by_provider' => []
522	];
523
524	foreach ($usage_data as $usage) {
525	$tokens = (int) $usage['tokens_used'];
526	$provider = $usage['provider'];
527
528	// Estimate 70% input, 30% output tokens
529	$input_tokens = $tokens * 0.7;
530	$output_tokens = $tokens * 0.3;
531
532	$cost = 0;
533
534	// Use the robust pricing helper for consistent cost calculation
535	$pricing = $this->get_model_pricing($provider);
536	if ($pricing) {
537	$cost = ($input_tokens * $pricing['input'] / 1000000) +
538	($output_tokens * $pricing['output'] / 1000000);
539	$costs[$provider] += $cost;
540	}
541	}
542
543	$costs['total'] = $costs['openai'] + $costs['claude'] + $costs['gemini'] + $costs['openrouter'];
544
545	// Format provider breakdown
546	$costs['by_provider'] = [
547	'openai' => [
548	'cost' => round($costs['openai'], 4),
549	'percentage' => $costs['total'] > 0 ? round(($costs['openai'] / $costs['total']) * 100, 1) : 0
550	],
551	'claude' => [
552	'cost' => round($costs['claude'], 4),
553	'percentage' => $costs['total'] > 0 ? round(($costs['claude'] / $costs['total']) * 100, 1) : 0
554	],
555	'gemini' => [
556	'cost' => round($costs['gemini'], 4),
557	'percentage' => $costs['total'] > 0 ? round(($costs['gemini'] / $costs['total']) * 100, 1) : 0
558	],
559	'openrouter' => [
560	'cost' => round($costs['openrouter'], 4),
561	'percentage' => $costs['total'] > 0 ? round(($costs['openrouter'] / $costs['total']) * 100, 1) : 0
562	]
563	];
564
565	return $costs;
566	}
567
568	/**
569	* Calculate time saved from feature usage
570	*
571	* @param array $feature_breakdown Feature usage breakdown
572	* @return int Time saved in minutes
573	*/
574	private function calculate_time_saved(array $feature_breakdown): int {
575	$total_time_saved = 0;
576
577	foreach ($feature_breakdown as $feature => $count) {
578	$time_per_action = self::TIME_SAVED_ESTIMATES[$feature] ?? 15; // Default 15 minutes
579	$total_time_saved += $count * $time_per_action;
580	}
581
582	return $total_time_saved;
583	}
584
585	/**
586	* Get AI usage metrics from database
587	*
588	* @param int $user_id User ID
589	* @param string $date_condition SQL date condition
590	* @return array AI usage metrics
591	*/
592	private function get_ai_usage_metrics(int $user_id, string $date_condition): array {
593	global $wpdb;
594
595	// Get table name and escape it properly (table names cannot be parameterized)
596	$table_name = esc_sql($this->database->get_table('ai_usage'));
597
598	$cutoff = $this->get_date_cutoff($this->resolve_period_from_condition($date_condition));
599
600	if ($cutoff !== null) {
601	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
602	$usage_data = $wpdb->get_results(
603	$wpdb->prepare(
604	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table_name is escaped via esc_sql().
605	"SELECT provider, action, tokens_used, created_at FROM `{$table_name}` WHERE user_id = %d AND created_at >= %s",
606	$user_id,
607	$cutoff
608	),
609	ARRAY_A
610	);
611	} else {
612	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
613	$usage_data = $wpdb->get_results(
614	$wpdb->prepare(
615	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table_name is escaped via esc_sql().
616	"SELECT provider, action, tokens_used, created_at FROM `{$table_name}` WHERE user_id = %d",
617	$user_id
618	),
619	ARRAY_A
620	);
621	}
622
623	if (empty($usage_data)) {
624	// Must return the same shape as the populated path below —
625	// get_overview_metrics() reads every key unconditionally, so a
626	// short array here surfaces as undefined-key warnings and null
627	// fields for any user with no AI usage yet (i.e. a fresh install).
628	// The values mirror what the loop below produces for zero rows.
629	return [
630	'total_actions' => 0,
631	'total_tokens' => 0,
632	'feature_breakdown' => [],
633	'features_used_count' => 0,
634	'most_used_feature' => '',
635	'most_used_count' => 0,
636	'success_rate' => 0,
637	'usage_data' => [],
638	'cost_change' => 0,
639	'time_saved_change' => 0
640	];
641	}
642
643	// Calculate feature breakdown and new metrics
644	$feature_breakdown = [];
645	$total_actions = 0;
646	$total_tokens = 0;
647
648	foreach ($usage_data as $row) {
649	$action = $row['action'];
650	$tokens = (int) $row['tokens_used'];
651
652	if (!isset($feature_breakdown[$action])) {
653	$feature_breakdown[$action] = 0;
654	}
655	$feature_breakdown[$action]++;
656	$total_actions++;
657	$total_tokens += $tokens;
658	}
659
660	// Calculate new metrics
661	$features_used_count = count($feature_breakdown);
662
663	// Find most used feature
664	$most_used_feature = '';
665	$most_used_count = 0;
666	foreach ($feature_breakdown as $feature => $count) {
667	if ($count > $most_used_count) {
668	$most_used_feature = $feature;
669	$most_used_count = $count;
670	}
671	}
672
673	// Calculate success rate (assuming all logged actions are successful for now)
674	// In future, we could track failed attempts separately
675	$success_rate = $total_actions > 0 ? 100 : 0;
676
677	// Calculate changes from previous period
678	$previous_period_data = $this->get_previous_period_data($user_id, $date_condition);
679	$cost_change = $this->calculate_percentage_change(
680	$previous_period_data['total_cost'] ?? 0,
681	$this->calculate_total_cost($usage_data)
682	);
683	$time_saved_change = $this->calculate_percentage_change(
684	$previous_period_data['time_saved'] ?? 0,
685	$this->calculate_time_saved($feature_breakdown)
686	);
687
688	return [
689	'total_actions' => $total_actions,
690	'total_tokens' => $total_tokens,
691	'feature_breakdown' => $feature_breakdown,
692	'features_used_count' => $features_used_count,
693	'most_used_feature' => $most_used_feature,
694	'most_used_count' => $most_used_count,
695	'success_rate' => $success_rate,
696	'usage_data' => $usage_data,
697	'cost_change' => $cost_change,
698	'time_saved_change' => $time_saved_change
699	];
700	}
701
702	/**
703	* Get SEO metrics from database
704	*
705	* @param int $user_id User ID
706	* @param string $date_condition SQL date condition
707	* @return array SEO metrics
708	*/
709	private function get_seo_metrics(int $user_id, string $date_condition): array {
710	global $wpdb;
711
712	$table_name = esc_sql($this->database->get_table('seo_scores'));
713	$cutoff = $this->get_date_cutoff($this->resolve_period_from_condition($date_condition));
714
715	if ($cutoff !== null) {
716	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
717	$result = $wpdb->get_row(
718	$wpdb->prepare(
719	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table_name is escaped via esc_sql().
720	"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",
721	$user_id,
722	$cutoff
723	),
724	ARRAY_A
725	);
726	} else {
727	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
728	$result = $wpdb->get_row(
729	$wpdb->prepare(
730	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table_name is escaped via esc_sql().
731	"SELECT COUNT(DISTINCT post_id) as content_optimized, AVG(overall_score) as average_score FROM `{$table_name}` WHERE user_id = %d",
732	$user_id
733	),
734	ARRAY_A
735	);
736	}
737
738	if (!$result \|\| (int) $result['content_optimized'] === 0) {
739	return [
740	'content_optimized' => 0,
741	'average_seo_score' => 0,
742	'content_optimized_change' => 0,
743	'seo_score_change' => 0
744	];
745	}
746
747	// Calculate changes from previous period
748	$previous_seo_data = $this->get_previous_seo_data($user_id, $date_condition);
749	$content_optimized_change = $this->calculate_percentage_change(
750	$previous_seo_data['content_optimized'] ?? 0,
751	(int) $result['content_optimized']
752	);
753	$seo_score_change = $this->calculate_percentage_change(
754	$previous_seo_data['average_seo_score'] ?? 0,
755	round((float) $result['average_score'], 1)
756	);
757
758	return [
759	'content_optimized' => (int) $result['content_optimized'],
760	'average_seo_score' => round((float) $result['average_score'], 1),
761	'content_optimized_change' => $content_optimized_change,
762	'seo_score_change' => $seo_score_change
763	];
764	}
765
766	/**
767	* Get content brief metrics from database
768	*
769	* @param int $user_id User ID
770	* @param string $date_condition SQL date condition
771	* @return array Content brief metrics
772	*/
773	private function get_content_brief_metrics(int $user_id, string $date_condition): array {
774	global $wpdb;
775
776	$table_name = esc_sql($this->database->get_table('content_briefs'));
777	$cutoff = $this->get_date_cutoff($this->resolve_period_from_condition($date_condition));
778
779	if ($cutoff !== null) {
780	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
781	$result = $wpdb->get_var(
782	$wpdb->prepare(
783	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table_name is escaped via esc_sql().
784	"SELECT COUNT(*) as total_briefs FROM `{$table_name}` WHERE user_id = %d AND created_at >= %s",
785	$user_id,
786	$cutoff
787	)
788	);
789	} else {
790	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
791	$result = $wpdb->get_var(
792	$wpdb->prepare(
793	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table_name is escaped via esc_sql().
794	"SELECT COUNT(*) as total_briefs FROM `{$table_name}` WHERE user_id = %d",
795	$user_id
796	)
797	);
798	}
799
800	return [
801	'total_briefs' => (int) $result ?: 0
802	];
803	}
804
805	/**
806	* Get detailed usage breakdown
807	*
808	* @param WP_REST_Request $request Request object
809	* @return WP_REST_Response\|WP_Error Response object
810	*/
811	public function get_usage_breakdown(WP_REST_Request $request) {
812	try {
813	$user_id = get_current_user_id();
814	$period = $request->get_param('period') ?? '30d';
815	$page = max(1, (int) $request->get_param('page') ?? 1);
816	$per_page = min(100, max(10, (int) $request->get_param('per_page') ?? 20));
817	$offset = ($page - 1) * $per_page;
818
819	// Get date range for queries
820	$date_condition = $this->get_date_condition($period);
821
822	// Get detailed usage breakdown
823	$usage_data = $this->get_detailed_usage_breakdown($user_id, $date_condition, $per_page, $offset);
824	$total_records = $this->get_usage_breakdown_count($user_id, $date_condition);
825
826	return new WP_REST_Response([
827	'success' => true,
828	'data' => [
829	'usage_records' => $usage_data,
830	'pagination' => [
831	'page' => $page,
832	'per_page' => $per_page,
833	'total_records' => $total_records,
834	'total_pages' => ceil($total_records / $per_page)
835	],
836	'period' => $period
837	]
838	], 200);
839
840	} catch (\Exception $e) {
841	return new WP_Error(
842	'usage_breakdown_failed',
843	'Failed to get usage breakdown: ' . $e->getMessage(),
844	['status' => 500]
845	);
846	}
847	}
848
849	/**
850	* Get cost analysis (placeholder for Phase 2)
851	*
852	* @param WP_REST_Request $request Request object
853	* @return WP_REST_Response\|WP_Error Response object
854	*/
855	public function get_cost_analysis(WP_REST_Request $request) {
856	// Return 200 with success:false so the frontend can render an
857	// "unavailable" state — apiFetch rejects on non-2xx, which would
858	// otherwise surface as a generic hard error.
859	return new WP_REST_Response([
860	'success' => false,
861	'data' => null,
862	'message' => 'Cost analysis is not yet implemented.'
863	], 200);
864	}
865
866	/**
867	* Calculate total cost from usage data
868	*
869	* @param array $usage_data Usage data array
870	* @return float Total cost
871	*/
872	private function calculate_total_cost(array $usage_data): float {
873	$cost_data = $this->calculate_costs($usage_data);
874	return $cost_data['total'];
875	}
876
877	/**
878	* Calculate percentage change between two values
879	*
880	* @param float $old_value Previous period value
881	* @param float $new_value Current period value
882	* @return float Percentage change
883	*/
884	private function calculate_percentage_change(float $old_value, float $new_value): float {
885	if ((float) $old_value === 0.0) {
886	return $new_value > 0 ? 100 : 0;
887	}
888
889	return round((($new_value - $old_value) / $old_value) * 100, 1);
890	}
891
892	/**
893	* Get previous period data for comparison
894	*
895	* @param int $user_id User ID
896	* @param string $current_date_condition Current period date condition
897	* @return array Previous period data
898	*/
899	private function get_previous_period_data(int $user_id, string $current_date_condition): array {
900	global $wpdb;
901
902	// Get table name and escape it properly (table names cannot be parameterized)
903	$table_name = esc_sql($this->database->get_table('ai_usage'));
904
905	// Extract the interval from current date condition to calculate previous period
906	$previous_date_condition = $this->get_previous_period_condition($current_date_condition);
907
908	// Prepare and execute query with proper parameter binding to prevent SQL injection
909	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is properly escaped, date condition is from controlled source
910	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Analytics data is real-time and shouldn't be cached
911	$usage_data = $wpdb->get_results(
912	$wpdb->prepare("
913	SELECT
914	provider,
915	action,
916	tokens_used
917	FROM `{$table_name}`
918	WHERE user_id = %d
919	{$previous_date_condition}
920	", $user_id),
921	ARRAY_A
922	);
923	// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
924
925	if (empty($usage_data)) {
926	return ['total_cost' => 0, 'time_saved' => 0];
927	}
928
929	// Calculate feature breakdown for time saved
930	$feature_breakdown = [];
931	foreach ($usage_data as $row) {
932	$action = $row['action'];
933	if (!isset($feature_breakdown[$action])) {
934	$feature_breakdown[$action] = 0;
935	}
936	$feature_breakdown[$action]++;
937	}
938
939	return [
940	'total_cost' => $this->calculate_total_cost($usage_data),
941	'time_saved' => $this->calculate_time_saved($feature_breakdown)
942	];
943	}
944
945	/**
946	* Get detailed usage breakdown with pagination
947	*
948	* @param int $user_id User ID
949	* @param string $date_condition SQL date condition
950	* @param int $limit Number of records to return
951	* @param int $offset Offset for pagination
952	* @return array Detailed usage records
953	*/
954	private function get_detailed_usage_breakdown(int $user_id, string $date_condition, int $limit, int $offset): array {
955	global $wpdb;
956
957	$table_name = esc_sql($this->database->get_table('ai_usage'));
958
959	$sql = "
960	SELECT
961	id,
962	provider,
963	action,
964	tokens_used,
965	post_id,
966	metadata,
967	created_at
968	FROM `{$table_name}`
969	WHERE user_id = %d
970	{$date_condition}
971	ORDER BY created_at DESC
972	LIMIT %d OFFSET %d
973	";
974
975	// 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
976	$usage_data = $wpdb->get_results(
977	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- SQL is properly prepared with placeholders
978	$wpdb->prepare($sql, $user_id, $limit, $offset),
979	ARRAY_A
980	);
981
982	// Process and enrich the data
983	$processed_data = [];
984	foreach ($usage_data as $record) {
985	$metadata = !empty($record['metadata']) ? json_decode($record['metadata'], true) : [];
986	$model = $metadata['actual_model'] ?? $this->get_default_model($record['provider']);
987	$cost = $this->calculate_record_cost($record['provider'], (int) $record['tokens_used'], $model);
988
989	$processed_data[] = [
990	'id' => (int) $record['id'],
991	'provider' => $record['provider'],
992	'model' => $model,
993	'action' => $record['action'],
994	'tokens_used' => (int) $record['tokens_used'],
995	'estimated_cost' => $cost,
996	'post_id' => $record['post_id'] ? (int) $record['post_id'] : null,
997	'created_at' => $record['created_at'],
998	'formatted_date' => wp_date('M j, Y g:i A', strtotime($record['created_at']))
999	];
1000	}
1001
1002	return $processed_data;
1003	}
1004
1005	/**
1006	* Get total count of usage records for pagination
1007	*
1008	* @param int $user_id User ID
1009	* @param string $date_condition SQL date condition
1010	* @return int Total record count
1011	*/
1012	private function get_usage_breakdown_count(int $user_id, string $date_condition): int {
1013	global $wpdb;
1014
1015	$table_name = esc_sql($this->database->get_table('ai_usage'));
1016
1017	$sql = "
1018	SELECT COUNT(*)
1019	FROM `{$table_name}`
1020	WHERE user_id = %d
1021	{$date_condition}
1022	";
1023
1024	// 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
1025	$count = $wpdb->get_var(
1026	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- SQL is properly prepared with placeholders
1027	$wpdb->prepare($sql, $user_id)
1028	);
1029
1030	return (int) $count;
1031	}
1032
1033	/**
1034	* Calculate cost for a single record using the robust pricing helper
1035	*
1036	* @param string $provider AI provider
1037	* @param int $tokens_used Number of tokens used
1038	* @param string $model Optional specific model name
1039	* @return float Estimated cost
1040	*/
1041	private function calculate_record_cost(string $provider, int $tokens_used, string $model = ''): float {
1042	// Get pricing using the robust helper method
1043	$pricing = $this->get_model_pricing($provider, $model);
1044
1045	if (!$pricing) {
1046	return 0.0;
1047	}
1048
1049	// Estimate 70% input, 30% output tokens
1050	$input_tokens = $tokens_used * 0.7;
1051	$output_tokens = $tokens_used * 0.3;
1052
1053	return (($input_tokens / 1000000) * $pricing['input']) +
1054	(($output_tokens / 1000000) * $pricing['output']);
1055	}
1056
1057	/**
1058	* Get pricing for any model with intelligent fallbacks
1059	*
1060	* @param string $provider AI provider
1061	* @param string $model Model name (optional)
1062	* @return array\|null Pricing array with 'input' and 'output' keys, or null if not found
1063	*/
1064	private function get_model_pricing(string $provider, string $model = ''): ?array {
1065	switch ($provider) {
1066	case 'openai':
1067	// Try specific model first, fallback to default
1068	if ($model && isset(self::OPENAI_PRICING[$model])) {
1069	return self::OPENAI_PRICING[$model];
1070	}
1071	return self::OPENAI_PRICING['gpt-4o'] ?? null;
1072
1073	case 'claude':
1074	// Try specific model first, fallback to recommended default
1075	if ($model && isset(self::CLAUDE_PRICING[$model])) {
1076	return self::CLAUDE_PRICING[$model];
1077	}
1078	return self::CLAUDE_PRICING['claude-sonnet-5'] ??
1079	self::CLAUDE_PRICING['claude-sonnet-4-6'] ?? null;
1080
1081	case 'gemini':
1082	// Try specific model first, fallback to default
1083	if ($model && isset(self::GEMINI_PRICING[$model])) {
1084	return self::GEMINI_PRICING[$model];
1085	}
1086	return self::GEMINI_PRICING['gemini-2.5-flash'] ?? null;
1087
1088	case 'openrouter':
1089	// Try specific model first, fallback to default
1090	if ($model && isset(self::OPENROUTER_PRICING[$model])) {
1091	return self::OPENROUTER_PRICING[$model];
1092	}
1093	return self::OPENROUTER_PRICING['openai/gpt-4o-mini'] ?? null;
1094
1095	default:
1096	return null;
1097	}
1098	}
1099
1100	/**
1101	* Get default model for provider using proper aliases
1102	*
1103	* @param string $provider AI provider
1104	* @return string Default model name
1105	*/
1106	private function get_default_model(string $provider): string {
1107	switch ($provider) {
1108	case 'openai':
1109	return \ThinkRank\Core\Settings::DEFAULT_OPENAI_MODEL;
1110	case 'claude':
1111	return \ThinkRank\Core\Settings::DEFAULT_CLAUDE_MODEL;
1112	case 'gemini':
1113	return \ThinkRank\Core\Settings::DEFAULT_GEMINI_MODEL;
1114	case 'openrouter':
1115	return \ThinkRank\Core\Settings::DEFAULT_OPENROUTER_MODEL;
1116	default:
1117	return 'unknown';
1118	}
1119	}
1120
1121	/**
1122	* Get previous SEO data for comparison
1123	*
1124	* @param int $user_id User ID
1125	* @param string $current_date_condition Current period date condition
1126	* @return array Previous SEO data
1127	*/
1128	private function get_previous_seo_data(int $user_id, string $current_date_condition): array {
1129	global $wpdb;
1130
1131	// Get table name and escape it properly (table names cannot be parameterized)
1132	$table_name = esc_sql($this->database->get_table('seo_scores'));
1133
1134	$previous_date_condition = $this->get_previous_period_condition($current_date_condition);
1135
1136	// Prepare and execute query with proper parameter binding to prevent SQL injection
1137	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is properly escaped, date condition is from controlled source
1138	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Analytics data is real-time and shouldn't be cached
1139	$result = $wpdb->get_row(
1140	$wpdb->prepare("
1141	SELECT
1142	COUNT(DISTINCT post_id) as content_optimized,
1143	AVG(overall_score) as average_score
1144	FROM `{$table_name}`
1145	WHERE user_id = %d
1146	{$previous_date_condition}
1147	", $user_id),
1148	ARRAY_A
1149	);
1150	// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
1151
1152	if (!$result) {
1153	return ['content_optimized' => 0, 'average_seo_score' => 0];
1154	}
1155
1156	return [
1157	'content_optimized' => (int) $result['content_optimized'],
1158	'average_seo_score' => round((float) $result['average_score'], 1)
1159	];
1160	}
1161
1162	/**
1163	* Resolve a period key from a legacy SQL date condition string.
1164	* Used internally so the new parameterized helpers can derive the period.
1165	*
1166	* @param string $condition Legacy date condition string
1167	* @return string Period key
1168	*/
1169	private function resolve_period_from_condition(string $condition): string {
1170	if (strpos($condition, 'INTERVAL 7') !== false) { return '7d';
1171	}
1172	if (strpos($condition, 'INTERVAL 30') !== false) { return '30d';
1173	}
1174	if (strpos($condition, 'INTERVAL 90') !== false) { return '90d';
1175	}
1176	if (empty(trim($condition))) { return 'all';
1177	}
1178	return '30d';
1179	}
1180
1181	/**
1182	* Convert current period condition to previous period condition
1183	*
1184	* @param string $current_condition Current period SQL condition
1185	* @return string Previous period SQL condition
1186	*/
1187	private function get_previous_period_condition(string $current_condition): string {
1188	// Extract interval from conditions like "AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)"
1189	if (preg_match('/INTERVAL (\d+) (\w+)/', $current_condition, $matches)) {
1190	$interval = (int) $matches[1];
1191	$unit = $matches[2];
1192
1193	// Calculate previous period: if current is last 30 days, previous is 30-60 days ago
1194	$start_interval = $interval * 2;
1195	$end_interval = $interval;
1196
1197	return "AND created_at >= DATE_SUB(NOW(), INTERVAL {$start_interval} {$unit})
1198	AND created_at < DATE_SUB(NOW(), INTERVAL {$end_interval} {$unit})";
1199	}
1200
1201	// Fallback for unknown conditions
1202	return "AND created_at >= DATE_SUB(NOW(), INTERVAL 60 DAY)
1203	AND created_at < DATE_SUB(NOW(), INTERVAL 30 DAY)";
1204	}
1205	}
1206