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.10.0, at includes/core/class-database.php

611 lines 21.1 KB

Raw Download Zip

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