thinkrank / includes / core / class-database.php

class-database.php in ThinkRank AI SEO – AI SEO Plugin for WordPress: Schema, XML Sitemaps, Meta Tags, Search Console & Local SEO 1.0.2, at includes/core/class-database.php

610 lines 21.2 KB

Raw Download Zip

1	<?php
2	/**
3	* Database Manager Class
4	*
5	* Handles database operations and provides repository pattern
6	*
7	* @package ThinkRank\Core
8	* @since 1.0.0
9	*/
10
11	declare(strict_types=1);
12
13	namespace ThinkRank\Core;
14
15	// Prevent direct access
16	if (!defined('ABSPATH')) {
17	exit;
18	}
19
20	/**
21	* Database Class
22	*
23	* Single Responsibility: Database operations and query management
24	* Repository Pattern: Abstraction layer for data access
25	*
26	* @since 1.0.0
27	*/
28	class Database {
29
30	/**
31	* WordPress database instance
32	*
33	* @var \wpdb
34	*/
35	private \wpdb $wpdb;
36
37	/**
38	* Table names
39	*
40	* @var array
41	*/
42	private array $tables;
43
44	/**
45	* Constructor
46	*/
47	public function __construct() {
48	global $wpdb;
49	$this->wpdb = $wpdb;
50
51	$this->tables = [
52	'ai_cache' => $wpdb->prefix . 'thinkrank_ai_cache',
53	'ai_usage' => $wpdb->prefix . 'thinkrank_ai_usage',
54	'content_briefs' => $wpdb->prefix . 'thinkrank_content_briefs',
55	'seo_scores' => $wpdb->prefix . 'thinkrank_seo_scores',
56	'seo_performance' => $wpdb->prefix . 'thinkrank_seo_performance',
57	];
58	}
59
60	/**
61	* Initialize database operations
62	*
63	* @return void
64	*/
65	public function init(): void {
66	// Add any initialization hooks here
67	add_action('thinkrank_cache_cleanup', [$this, 'cleanup_expired_cache']);
68
69	// Hook the missing usage analytics cron handler
70	add_action('thinkrank_usage_analytics', [$this, 'process_weekly_analytics']);
71	}
72
73	/**
74	* Get table name
75	*
76	* @param string $table Table identifier
77	* @return string Full table name
78	* @throws \InvalidArgumentException If table doesn't exist
79	*/
80	public function get_table(string $table): string {
81	if (!isset($this->tables[$table])) {
82	throw new \InvalidArgumentException(sprintf("Table '%s' not found", esc_html($table)));
83	}
84
85	return $this->tables[$table];
86	}
87
88	/**
89	* Execute prepared query safely
90	*
91	* @param string $query SQL query with placeholders
92	* @param array $args Query arguments
93	* @return mixed Query result
94	*/
95	public function query(string $query, array $args = []) {
96	if (!empty($args)) {
97	// Prepare the query first, then execute
98	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Query is prepared in the line below
99	$prepared_query = $this->wpdb->prepare($query, $args);
100	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Using prepared query from above
101	return $this->wpdb->query($prepared_query);
102	}
103
104	// For queries without parameters, execute directly (safe for static queries)
105	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- No user input in static queries
106	return $this->wpdb->query($query);
107	}
108
109	/**
110	* Get single row
111	*
112	* @param string $query SQL query with placeholders
113	* @param array $args Query arguments
114	* @param string $output Output type (OBJECT, ARRAY_A, ARRAY_N)
115	* @return mixed Single row result
116	*/
117	public function get_row(string $query, array $args = [], string $output = OBJECT) {
118	if (!empty($args)) {
119	// Prepare the query first, then execute
120	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Query is prepared in the line below
121	$prepared_query = $this->wpdb->prepare($query, $args);
122	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Using prepared query from above
123	return $this->wpdb->get_row($prepared_query, $output);
124	}
125
126	// For queries without parameters, execute directly (safe for static queries)
127	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- No user input in static queries
128	return $this->wpdb->get_row($query, $output);
129	}
130
131	/**
132	* Get multiple rows
133	*
134	* @param string $query SQL query with placeholders
135	* @param array $args Query arguments
136	* @param string $output Output type (OBJECT, ARRAY_A, ARRAY_N)
137	* @return array Multiple rows result
138	*/
139	public function get_results(string $query, array $args = [], string $output = OBJECT): array {
140	if (!empty($args)) {
141	// Prepare the query first, then execute
142	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Query is prepared in the line below
143	$prepared_query = $this->wpdb->prepare($query, $args);
144	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Using prepared query from above
145	$results = $this->wpdb->get_results($prepared_query, $output);
146	} else {
147	// For queries without parameters, execute directly (safe for static queries)
148	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- No user input in static queries
149	$results = $this->wpdb->get_results($query, $output);
150	}
151
152	return is_array($results) ? $results : [];
153	}
154
155	/**
156	* Get single variable
157	*
158	* @param string $query SQL query with placeholders
159	* @param array $args Query arguments
160	* @return mixed Single variable result
161	*/
162	public function get_var(string $query, array $args = []) {
163	if (!empty($args)) {
164	// Prepare the query first, then execute
165	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Query is prepared in the line below
166	$prepared_query = $this->wpdb->prepare($query, $args);
167	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Using prepared query from above
168	return $this->wpdb->get_var($prepared_query);
169	}
170
171	// For queries without parameters, execute directly (safe for static queries)
172	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- No user input in static queries
173	return $this->wpdb->get_var($query);
174	}
175
176	/**
177	* Insert data into table
178	*
179	* @param string $table Table identifier
180	* @param array $data Data to insert
181	* @param array $format Data format (optional)
182	* @return int\|false Insert ID or false on failure
183	*/
184	public function insert(string $table, array $data, array $format = []) {
185	$table_name = $this->get_table($table);
186
187	$result = $this->wpdb->insert($table_name, $data, $format);
188
189	if (false === $result) {
190	$this->log_error('Insert failed', [
191	'table' => $table,
192	'data' => $data,
193	'error' => $this->wpdb->last_error
194	]);
195	return false;
196	}
197
198	return $this->wpdb->insert_id;
199	}
200
201	/**
202	* Update data in table
203	*
204	* @param string $table Table identifier
205	* @param array $data Data to update
206	* @param array $where Where conditions
207	* @param array $format Data format (optional)
208	* @param array $where_format Where format (optional)
209	* @return int\|false Number of rows updated or false on failure
210	*/
211	public function update(string $table, array $data, array $where, array $format = [], array $where_format = []) {
212	$table_name = $this->get_table($table);
213
214	$result = $this->wpdb->update($table_name, $data, $where, $format, $where_format);
215
216	if (false === $result) {
217	$this->log_error('Update failed', [
218	'table' => $table,
219	'data' => $data,
220	'where' => $where,
221	'error' => $this->wpdb->last_error
222	]);
223	}
224
225	return $result;
226	}
227
228	/**
229	* Delete data from table
230	*
231	* @param string $table Table identifier
232	* @param array $where Where conditions
233	* @param array $where_format Where format (optional)
234	* @return int\|false Number of rows deleted or false on failure
235	*/
236	public function delete(string $table, array $where, array $where_format = []) {
237	$table_name = $this->get_table($table);
238
239	$result = $this->wpdb->delete($table_name, $where, $where_format);
240
241	if (false === $result) {
242	$this->log_error('Delete failed', [
243	'table' => $table,
244	'where' => $where,
245	'error' => $this->wpdb->last_error
246	]);
247	}
248
249	return $result;
250	}
251
252	/**
253	* Start database transaction
254	*
255	* @return void
256	*/
257	public function start_transaction(): void {
258	// Transaction commands don't need preparation as they contain no user input
259	$this->wpdb->query('START TRANSACTION');
260	}
261
262	/**
263	* Commit database transaction
264	*
265	* @return void
266	*/
267	public function commit(): void {
268	// Transaction commands don't need preparation as they contain no user input
269	$this->wpdb->query('COMMIT');
270	}
271
272	/**
273	* Rollback database transaction
274	*
275	* @return void
276	*/
277	public function rollback(): void {
278	// Transaction commands don't need preparation as they contain no user input
279	$this->wpdb->query('ROLLBACK');
280	}
281
282	/**
283	* Get last database error
284	*
285	* @return string Last error message
286	*/
287	public function get_last_error(): string {
288	return $this->wpdb->last_error;
289	}
290
291	/**
292	* Clean up expired cache entries
293	*
294	* @return void
295	*/
296	public function cleanup_expired_cache(): void {
297	$cache_table = $this->get_table('ai_cache');
298
299	// Check WordPress version for %i support (introduced in 6.2)
300	if (version_compare($GLOBALS['wp_version'], '6.2', '>=')) {
301	// Use %i placeholder for table identifier (WordPress 6.2+)
302	// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnsupportedIdentifierPlaceholder
303	$deleted = $this->wpdb->query(
304	$this->wpdb->prepare(
305	'DELETE FROM %i WHERE expires_at < %s',
306	$cache_table,
307	current_time('mysql')
308	)
309	);
310	// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnsupportedIdentifierPlaceholder
311	} else {
312	// Fallback for older WordPress versions - table name is escaped and safe
313	$escaped_table = esc_sql($cache_table);
314	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared
315	$deleted = $this->wpdb->query(
316	$this->wpdb->prepare(
317	"DELETE FROM `{$escaped_table}` WHERE expires_at < %s",
318	current_time('mysql')
319	)
320	);
321	// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared
322	}
323
324	if ($deleted > 0) {
325	$this->log_error(
326	sprintf('Cleaned up %d expired cache entries', $deleted),
327	['type' => 'info']
328	);
329	}
330	}
331
332	/**
333	* Get database statistics
334	*
335	* @return array Database statistics
336	*/
337	public function get_stats(): array {
338	$stats = [];
339
340	foreach ($this->tables as $key => $table) {
341	// Check WordPress version for %i support (introduced in 6.2)
342	if (version_compare($GLOBALS['wp_version'], '6.2', '>=')) {
343	// Use %i placeholder for table identifier (WordPress 6.2+)
344	// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnsupportedIdentifierPlaceholder
345	$count = $this->wpdb->get_var(
346	$this->wpdb->prepare('SELECT COUNT(*) FROM %i', $table)
347	);
348	// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnsupportedIdentifierPlaceholder
349	} else {
350	// Fallback for older WordPress versions - table name is from our controlled list
351	$escaped_table = esc_sql($table);
352	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is properly escaped and from controlled source
353	$count = $this->wpdb->get_var("SELECT COUNT(*) FROM `{$escaped_table}`");
354	}
355	$stats[$key] = (int) $count;
356	}
357
358	return $stats;
359	}
360
361	/**
362	* Log database errors
363	*
364	* @param string $message Error message
365	* @param array $context Error context
366	* @return void
367	*/
368	private function log_error(string $message, array $context = []): void {
369	// Only log errors if both WP_DEBUG and custom debug flag are enabled
370	if (!defined('WP_DEBUG') \|\| !WP_DEBUG) {
371	return;
372	}
373
374	if (!defined('THINKRANK_DEBUG_LOGGING') \|\| !THINKRANK_DEBUG_LOGGING) {
375	return;
376	}
377
378	// Use WordPress error logging function only in debug mode
379	// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
380	error_log('ThinkRank Database Error: ' . $message . ' - ' . wp_json_encode($context));
381	}
382
383	/**
384	* Log debug information
385	*
386	* @param string $message Debug message
387	* @param array $context Debug context
388	* @return void
389	*/
390	private function log_debug(string $message, array $context = []): void {
391	// Only log debug info if both WP_DEBUG and custom debug flag are enabled
392	if (!defined('WP_DEBUG') \|\| !WP_DEBUG) {
393	return;
394	}
395
396	if (!defined('THINKRANK_DEBUG_LOGGING') \|\| !THINKRANK_DEBUG_LOGGING) {
397	return;
398	}
399
400	// Use WordPress error logging function only in debug mode
401	// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
402	error_log('ThinkRank Database Debug: ' . $message . ' - ' . wp_json_encode($context));
403	}
404
405	/**
406	* Process weekly analytics (handles thinkrank_usage_analytics cron job)
407	*
408	* @return void
409	*/
410	public function process_weekly_analytics(): void {
411	try {
412	// 1. Clean old data from analytics tables
413	$cleanup_results = $this->cleanup_old_analytics_data();
414
415	// 2. Clear analytics cache to force fresh calculations
416	$cache_results = $this->clear_analytics_cache();
417
418	// Log successful processing
419	$this->log_debug('Weekly analytics processing completed', [
420	'cleanup_results' => $cleanup_results,
421	'cache_results' => $cache_results,
422	'processed_at' => current_time('mysql')
423	]);
424
425	} catch (\Exception $e) {
426	// Log error but don't throw to prevent cron job failures
427	$this->log_debug('Weekly analytics processing failed', [
428	'error' => $e->getMessage(),
429	'trace' => $e->getTraceAsString()
430	]);
431	}
432	}
433
434	/**
435	* Clean old data from analytics tables
436	*
437	* @return array Cleanup results
438	*/
439	private function cleanup_old_analytics_data(): array {
440	$results = [];
441
442	// Define retention periods (in days)
443	$retention_config = [
444	'ai_usage' => 365, // 1 year
445	'seo_scores' => 180, // 6 months
446	'content_briefs' => 90, // 3 months
447	'seo_performance' => 90 // 3 months (performance data grows fast)
448	];
449
450	foreach ($retention_config as $table_key => $retention_days) {
451	try {
452	$deleted_count = $this->cleanup_table_data($table_key, $retention_days);
453	$results[$table_key] = [
454	'deleted_records' => $deleted_count,
455	'retention_days' => $retention_days,
456	'success' => true
457	];
458	} catch (\Exception $e) {
459	$results[$table_key] = [
460	'deleted_records' => 0,
461	'retention_days' => $retention_days,
462	'success' => false,
463	'error' => $e->getMessage()
464	];
465	}
466	}
467
468	// Also call the existing performance data cleanup method
469	try {
470	$performance_collector = new \ThinkRank\SEO\Performance_Data_Collector();
471	$performance_deleted = $performance_collector->cleanup_old_data(30); // Keep 30 days of detailed performance data
472	$results['performance_detailed'] = [
473	'deleted_records' => $performance_deleted,
474	'retention_days' => 30,
475	'success' => true
476	];
477	} catch (\Exception $e) {
478	$results['performance_detailed'] = [
479	'deleted_records' => 0,
480	'retention_days' => 30,
481	'success' => false,
482	'error' => $e->getMessage()
483	];
484	}
485
486	return $results;
487	}
488
489	/**
490	* Clean data from specific table
491	*
492	* @param string $table_key Table identifier
493	* @param int $retention_days Number of days to keep
494	* @return int Number of deleted records
495	*/
496	private function cleanup_table_data(string $table_key, int $retention_days): int {
497	$table_name = $this->get_table($table_key);
498	$cutoff_date = gmdate('Y-m-d H:i:s', strtotime("-{$retention_days} days"));
499
500	// Get the appropriate date column for each table
501	$date_columns = [
502	'ai_usage' => 'created_at',
503	'seo_scores' => 'calculated_at',
504	'content_briefs' => 'created_at',
505	'seo_performance' => 'measured_at'
506	];
507
508	$date_column = $date_columns[$table_key] ?? 'created_at';
509
510	// Delete old records
511	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Analytics cleanup requires direct database access, table and column names are validated internally
512	$deleted = $this->wpdb->query(
513	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- SQL is properly prepared with placeholders
514	$this->wpdb->prepare(
515	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table and column names are validated internally
516	"DELETE FROM `{$table_name}` WHERE {$date_column} < %s",
517	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $cutoff_date is validated and used as parameter
518	$cutoff_date
519	)
520	);
521
522	return $deleted !== false ? (int)$deleted : 0;
523	}
524
525	/**
526	* Clear analytics-related cache
527	*
528	* @return array Cache clearing results
529	*/
530	private function clear_analytics_cache(): array {
531	$cache_keys = [
532	'analytics_overview_7d',
533	'analytics_overview_30d',
534	'analytics_overview_90d',
535	'usage_breakdown_weekly',
536	'usage_breakdown_monthly',
537	'cost_analysis_7d',
538	'cost_analysis_30d',
539	'cost_analysis_90d'
540	];
541
542	$cleared_count = 0;
543
544	foreach ($cache_keys as $key) {
545	if (delete_transient($key)) {
546	$cleared_count++;
547	}
548	}
549
550	// Also clear any user-specific analytics cache
551	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Cache cleanup requires direct database access
552	$user_cache_deleted = $this->wpdb->query(
553	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- wpdb->options is WordPress core table, safe to use
554	"DELETE FROM {$this->wpdb->options}
555	WHERE option_name LIKE '_transient_thinkrank_analytics_%'
556	OR option_name LIKE '_transient_timeout_thinkrank_analytics_%'"
557	);
558
559	return [
560	'transients_cleared' => $cleared_count,
561	'user_cache_cleared' => $user_cache_deleted !== false ? (int)$user_cache_deleted : 0,
562	'total_cleared' => $cleared_count + ($user_cache_deleted !== false ? (int)$user_cache_deleted : 0)
563	];
564	}
565
566	/**
567	* Get table sizes for monitoring
568	*
569	* @since 1.0.0
570	*
571	* @return array Table sizes in MB
572	*/
573	public function get_table_sizes(): array {
574	$sizes = [];
575	$tables = [
576	'ai_usage',
577	'seo_scores',
578	'content_briefs',
579	'seo_performance'
580	];
581
582	foreach ($tables as $table_key) {
583	$table_name = $this->get_table($table_key);
584
585	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Table size monitoring requires direct database access
586	$size_result = $this->wpdb->get_row(
587	$this->wpdb->prepare(
588	"SELECT
589	table_name,
590	ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb,
591	table_rows
592	FROM information_schema.TABLES
593	WHERE table_schema = %s AND table_name = %s",
594	DB_NAME,
595	$table_name
596	)
597	);
598
599	if ($size_result) {
600	$sizes[$table_key] = [
601	'size_mb' => (float) $size_result->size_mb,
602	'rows' => (int) $size_result->table_rows
603	];
604	}
605	}
606
607	return $sizes;
608	}
609	}
610