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.10.0, at includes/api/class-usage-analytics-endpoint.php

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