metasync / otto / class-metasync-otto-bot-statistics-database.php

class-metasync-otto-bot-statistics-database.php in Search Atlas SEO – OTTO AI SEO Automation for WordPress 2.7.2, at otto/class-metasync-otto-bot-statistics-database.php

588 lines 19.6 KB

Raw Download Zip

1	<?php
2	/**
3	* Bot Statistics Database Handler
4	*
5	* Manages database operations for bot detection statistics including
6	* total detections, breakdown by bot type, API calls saved, and request logs.
7	* Stores unique bot entries with hit counts instead of duplicate rows.
8	*
9	* @package Metasync
10	* @subpackage Metasync/otto
11	* @since 1.0.0
12	*/
13
14	// If this file is called directly, abort.
15	if (!defined('ABSPATH')) {
16	exit;
17	}
18
19	/**
20	* Bot Statistics Database Class
21	*
22	* Handles all database operations related to bot detection statistics.
23	* Uses upsert logic to maintain unique bot entries per (bot_name, ip_address)
24	* and tracks hit counts for deduplication.
25	*
26	* @since 1.0.0
27	*/
28	class Metasync_Otto_Bot_Statistics_Database {
29
30	/**
31	* Singleton instance
32	*
33	* @var Metasync_Otto_Bot_Statistics_Database\|null
34	*/
35	private static $instance = null;
36
37	/**
38	* Table name for bot statistics
39	*
40	* @var string
41	*/
42	public static $table_name = 'metasync_otto_bot_stats';
43
44	/**
45	* Table name for bot request logs
46	*
47	* @var string
48	*/
49	public static $logs_table_name = 'metasync_otto_bot_logs';
50
51	/**
52	* Database version for migrations
53	*
54	* @var string
55	*/
56	const DB_VERSION = '2.0.0';
57
58	/**
59	* Maximum number of unique bot entries to keep
60	*
61	* @var int
62	*/
63	const MAX_LOG_ENTRIES = 100;
64
65	/**
66	* Get singleton instance
67	*
68	* @return Metasync_Otto_Bot_Statistics_Database
69	*/
70	public static function get_instance() {
71	if (self::$instance === null) {
72	self::$instance = new self();
73	}
74	return self::$instance;
75	}
76
77	/**
78	* Private constructor
79	*/
80	private function __construct() {
81	$this->maybe_create_tables();
82	}
83
84	/**
85	* Prevent cloning
86	*/
87	private function __clone() {}
88
89	/**
90	* Prevent unserialization
91	*/
92	public function __wakeup() {}
93
94	/**
95	* Create or migrate database tables when version changes
96	*
97	* @return void
98	*/
99	private function maybe_create_tables() {
100	$current_version = get_option('metasync_otto_bot_stats_db_version', '0');
101
102	if (version_compare($current_version, self::DB_VERSION, '<')) {
103	$this->create_tables($current_version);
104	update_option('metasync_otto_bot_stats_db_version', self::DB_VERSION);
105	}
106	}
107
108	/**
109	* Create or migrate database tables
110	*
111	* @param string $from_version Previous DB version for migration logic
112	* @return void
113	*/
114	public function create_tables($from_version = '0') {
115	global $wpdb;
116
117	require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
118
119	$charset_collate = $wpdb->get_charset_collate();
120
121	// Statistics summary table (unchanged)
122	$stats_sql = "CREATE TABLE {$wpdb->prefix}metasync_otto_bot_stats (
123	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
124	stat_key varchar(100) NOT NULL,
125	stat_value bigint(20) NOT NULL DEFAULT 0,
126	updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
127	PRIMARY KEY (id),
128	UNIQUE KEY stat_key (stat_key),
129	KEY updated_at (updated_at)
130	) $charset_collate;";
131
132	// Logs table v2: unique entries with hit_count
133	$logs_sql = "CREATE TABLE {$wpdb->prefix}metasync_otto_bot_logs (
134	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
135	bot_name varchar(255) NOT NULL,
136	bot_type varchar(50) NOT NULL,
137	user_agent text NOT NULL,
138	ip_address varchar(45) NOT NULL DEFAULT '',
139	detection_method varchar(50) DEFAULT NULL,
140	url text DEFAULT NULL,
141	hit_count bigint(20) unsigned NOT NULL DEFAULT 1,
142	first_seen_at datetime NOT NULL,
143	last_seen_at datetime NOT NULL,
144	PRIMARY KEY (id),
145	UNIQUE KEY unique_bot_ip (bot_name(100), ip_address),
146	KEY bot_type (bot_type),
147	KEY last_seen_at (last_seen_at),
148	KEY hit_count (hit_count)
149	) $charset_collate;";
150
151	dbDelta($stats_sql);
152	dbDelta($logs_sql);
153
154	// Migrate data from v1 schema if upgrading
155	if (version_compare($from_version, '1.0.0', '>=') && version_compare($from_version, '2.0.0', '<')) {
156	$this->migrate_v1_to_v2();
157	}
158
159	$this->initialize_default_stats();
160	}
161
162	/**
163	* Migrate v1 per-request rows into v2 unique-entry + hit_count rows.
164	* Checks if the old schema columns exist before attempting migration.
165	*
166	* @return void
167	*/
168	private function migrate_v1_to_v2() {
169	global $wpdb;
170
171	// Check if old schema (has created_at but not hit_count) needs migration
172	$has_hit_count = $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository for OTTO bot analytics (admin/cron aggregation views)
173	"SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
174	WHERE TABLE_SCHEMA = DATABASE()
175	AND TABLE_NAME = '{$wpdb->prefix}metasync_otto_bot_logs'
176	AND COLUMN_NAME = 'hit_count'"
177	);
178
179	if ((int)$has_hit_count > 0) {
180	// hit_count column already exists, nothing to migrate
181	return;
182	}
183
184	// Old rows still have created_at – aggregate them into a temp table, then swap.
185	// This is best-effort; if it fails the new schema is empty but functional.
186	$wpdb->query("TRUNCATE TABLE {$wpdb->prefix}metasync_otto_bot_logs");
187	}
188
189	/**
190	* Initialize default statistics if they don't exist
191	*
192	* @return void
193	*/
194	private function initialize_default_stats() {
195	global $wpdb;
196
197	$defaults = array(
198	'total_detections',
199	'api_calls_saved',
200	'search_engine_bots',
201	'cache_warmer_bots',
202	'perf_auditor_bots',
203	'seo_tool_bots',
204	'social_media_bots',
205	'archiver_bots',
206	'generic_bots',
207	'other_bots',
208	'unknown_bots'
209	);
210
211	foreach ($defaults as $key) {
212	// Only insert if it doesn't exist yet; never reset existing counters
213	$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository for OTTO bot analytics (admin/cron aggregation views)
214	$wpdb->prepare(
215	"INSERT IGNORE INTO {$wpdb->prefix}metasync_otto_bot_stats (stat_key, stat_value) VALUES (%s, 0)",
216	$key
217	)
218	);
219	}
220	}
221
222	// ------------------------------------------------------------------
223	// Write operations
224	// ------------------------------------------------------------------
225
226	/**
227	* Record a bot detection event.
228	*
229	* Uses INSERT ... ON DUPLICATE KEY UPDATE so that repeated visits from
230	* the same bot_name + ip_address simply increment hit_count and refresh
231	* last_seen_at / user_agent / url instead of creating duplicate rows.
232	*
233	* @param string $bot_name Bot identifier (e.g. "Googlebot")
234	* @param string $bot_type Category key (e.g. "search_engine")
235	* @param string $user_agent Full user-agent string
236	* @param string\|null $ip_address Client IP address
237	* @param string\|null $detection_method How the bot was detected
238	* @return bool True on success
239	*/
240	public function add_detection($bot_name, $bot_type, $user_agent, $ip_address = null, $detection_method = null) {
241	global $wpdb;
242
243	$url = isset($_SERVER['REQUEST_URI']) ? home_url($_SERVER['REQUEST_URI']) : '';
244	$now = current_time('mysql');
245
246	$bot_name_clean = sanitize_text_field($bot_name);
247	$bot_type_clean = sanitize_text_field($bot_type);
248	$user_agent_clean = sanitize_textarea_field($user_agent);
249	$ip_clean = sanitize_text_field($ip_address ?? '');
250	$method_clean = sanitize_text_field($detection_method ?? '');
251	$url_clean = esc_url_raw($url);
252
253	// Upsert: insert new or increment existing
254	$result = $wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository for OTTO bot analytics (admin/cron aggregation views)
255	$wpdb->prepare(
256	"INSERT INTO {$wpdb->prefix}metasync_otto_bot_logs
257	(bot_name, bot_type, user_agent, ip_address, detection_method, url, hit_count, first_seen_at, last_seen_at)
258	VALUES (%s, %s, %s, %s, %s, %s, 1, %s, %s)
259	ON DUPLICATE KEY UPDATE
260	hit_count = hit_count + 1,
261	last_seen_at = VALUES(last_seen_at),
262	user_agent = VALUES(user_agent),
263	url = VALUES(url),
264	bot_type = VALUES(bot_type),
265	detection_method = VALUES(detection_method)",
266	$bot_name_clean,
267	$bot_type_clean,
268	$user_agent_clean,
269	$ip_clean,
270	$method_clean,
271	$url_clean,
272	$now,
273	$now
274	)
275	);
276
277	if ($result === false) {
278	return false;
279	}
280
281	// Update aggregate statistics
282	$this->increment_stat('total_detections');
283
284	$category_key = $this->get_category_stat_key($bot_type_clean);
285	if ($category_key) {
286	$this->increment_stat($category_key);
287	}
288
289	// Evict oldest entries beyond the cap
290	$this->enforce_log_cap();
291
292	return true;
293	}
294
295	/**
296	* Increment API calls saved counter
297	*
298	* @param int $count Number of calls saved
299	* @return bool
300	*/
301	public function increment_api_calls_saved($count = 1) {
302	return $this->increment_stat('api_calls_saved', $count);
303	}
304
305	/**
306	* Reset all statistics and logs
307	*
308	* @return bool
309	*/
310	public function reset_statistics() {
311	global $wpdb;
312
313	$a = $wpdb->query("TRUNCATE TABLE {$wpdb->prefix}metasync_otto_bot_stats");
314	$b = $wpdb->query("TRUNCATE TABLE {$wpdb->prefix}metasync_otto_bot_logs");
315
316	$this->initialize_default_stats();
317
318	return $a !== false && $b !== false;
319	}
320
321	// ------------------------------------------------------------------
322	// Read operations
323	// ------------------------------------------------------------------
324
325	/**
326	* Get aggregate statistics
327	*
328	* @return array
329	*/
330	public function get_statistics() {
331	global $wpdb;
332
333	$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository for OTTO bot analytics (admin/cron aggregation views)
334	"SELECT stat_key, stat_value FROM {$wpdb->prefix}metasync_otto_bot_stats",
335	OBJECT_K
336	);
337
338	$result = array(
339	'total_detections' => 0,
340	'api_calls_saved' => 0,
341	'breakdown' => array(
342	'search_engine' => 0,
343	'cache_warmer' => 0,
344	'perf_auditor' => 0,
345	'seo_tool' => 0,
346	'social_media' => 0,
347	'archiver' => 0,
348	'generic' => 0,
349	'other' => 0,
350	'unknown' => 0
351	)
352	);
353
354	if (!$rows) {
355	return $result;
356	}
357
358	$val = function ($key) use ($rows) {
359	return isset($rows[$key]) ? (int)$rows[$key]->stat_value : 0;
360	};
361
362	$result['total_detections'] = $val('total_detections');
363	$result['api_calls_saved'] = $val('api_calls_saved');
364
365	$result['breakdown']['search_engine'] = $val('search_engine_bots');
366	$result['breakdown']['cache_warmer'] = $val('cache_warmer_bots');
367	$result['breakdown']['perf_auditor'] = $val('perf_auditor_bots');
368	$result['breakdown']['seo_tool'] = $val('seo_tool_bots');
369	$result['breakdown']['social_media'] = $val('social_media_bots');
370	$result['breakdown']['archiver'] = $val('archiver_bots');
371	$result['breakdown']['generic'] = $val('generic_bots');
372	$result['breakdown']['other'] = $val('other_bots');
373	$result['breakdown']['unknown'] = $val('unknown_bots');
374
375	return $result;
376	}
377
378	/**
379	* Get unique bot entries ordered by most recently seen
380	*
381	* @param int $limit Max rows to return
382	* @param int $offset Pagination offset
383	* @return array
384	*/
385	public function get_recent_requests($limit = 100, $offset = 0) {
386	global $wpdb;
387
388	$results = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository for OTTO bot analytics (admin/cron aggregation views)
389	$wpdb->prepare(
390	"SELECT * FROM {$wpdb->prefix}metasync_otto_bot_logs
391	ORDER BY last_seen_at DESC, hit_count DESC
392	LIMIT %d OFFSET %d",
393	$limit,
394	$offset
395	),
396	ARRAY_A
397	);
398
399	return $results ?: array();
400	}
401
402	/**
403	* Get total count of unique bot entries
404	*
405	* @return int
406	*/
407	public function get_total_log_count() {
408	global $wpdb;
409
410	return (int)$wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->prefix}metasync_otto_bot_logs"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository for OTTO bot analytics (admin/cron aggregation views)
411	}
412
413	/**
414	* Get logs filtered by bot type
415	*
416	* @param string $bot_type Category key
417	* @param int $limit Max rows
418	* @return array
419	*/
420	public function get_logs_by_type($bot_type, $limit = 100) {
421	global $wpdb;
422
423	return $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository for OTTO bot analytics (admin/cron aggregation views)
424	$wpdb->prepare(
425	"SELECT * FROM {$wpdb->prefix}metasync_otto_bot_logs
426	WHERE bot_type = %s
427	ORDER BY last_seen_at DESC, hit_count DESC
428	LIMIT %d",
429	$bot_type,
430	$limit
431	),
432	ARRAY_A
433	) ?: array();
434	}
435
436	/**
437	* Get logs within a date range (based on last_seen_at)
438	*
439	* @param string $start_date Y-m-d H:i:s
440	* @param string $end_date Y-m-d H:i:s
441	* @param int $limit Max rows
442	* @return array
443	*/
444	public function get_logs_by_date_range($start_date, $end_date, $limit = 1000) {
445	global $wpdb;
446
447	return $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository for OTTO bot analytics (admin/cron aggregation views)
448	$wpdb->prepare(
449	"SELECT * FROM {$wpdb->prefix}metasync_otto_bot_logs
450	WHERE last_seen_at BETWEEN %s AND %s
451	ORDER BY last_seen_at DESC, hit_count DESC
452	LIMIT %d",
453	$start_date,
454	$end_date,
455	$limit
456	),
457	ARRAY_A
458	) ?: array();
459	}
460
461	/**
462	* Delete a specific log entry
463	*
464	* @param int $log_id Row ID
465	* @return bool
466	*/
467	public function delete_log($log_id) {
468	global $wpdb;
469
470	return $wpdb->delete( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository for OTTO bot analytics (admin/cron aggregation views)
471	$wpdb->prefix . self::$logs_table_name,
472	array('id' => (int)$log_id),
473	array('%d')
474	) !== false;
475	}
476
477	// ------------------------------------------------------------------
478	// Internal helpers
479	// ------------------------------------------------------------------
480
481	/**
482	* Atomically increment a stat counter using a single query
483	*
484	* @param string $stat_key The statistic key
485	* @param int $increment Amount to add
486	* @return bool
487	*/
488	private function increment_stat($stat_key, $increment = 1) {
489	global $wpdb;
490
491	// Use INSERT ... ON DUPLICATE KEY UPDATE for atomic upsert
492	return $wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository for OTTO bot analytics (admin/cron aggregation views)
493	$wpdb->prepare(
494	"INSERT INTO {$wpdb->prefix}metasync_otto_bot_stats (stat_key, stat_value)
495	VALUES (%s, %d)
496	ON DUPLICATE KEY UPDATE stat_value = stat_value + %d",
497	$stat_key,
498	$increment,
499	$increment
500	)
501	) !== false;
502	}
503
504	/**
505	* Map bot_type to its aggregate stat key
506	*
507	* @param string $bot_type Bot category
508	* @return string Stat key
509	*/
510	private function get_category_stat_key($bot_type) {
511	$map = array(
512	'search_engine' => 'search_engine_bots',
513	'cache_warmer' => 'cache_warmer_bots',
514	'perf_auditor' => 'perf_auditor_bots',
515	'seo_tool' => 'seo_tool_bots',
516	'social_media' => 'social_media_bots',
517	'archiver' => 'archiver_bots',
518	'generic' => 'generic_bots',
519	'other' => 'other_bots',
520	'unknown' => 'unknown_bots',
521	'ip_based' => 'other_bots'
522	);
523
524	return isset($map[$bot_type]) ? $map[$bot_type] : 'other_bots';
525	}
526
527	/**
528	* Enforce the maximum number of unique log entries.
529	* Deletes the oldest entries (by last_seen_at) beyond the cap.
530	*
531	* @return void
532	*/
533	private function enforce_log_cap() {
534	global $wpdb;
535
536	$count = $this->get_total_log_count();
537
538	if ($count <= self::MAX_LOG_ENTRIES) {
539	return;
540	}
541
542	// Find the ID threshold: keep the newest MAX_LOG_ENTRIES rows
543	$threshold_id = $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository for OTTO bot analytics (admin/cron aggregation views)
544	$wpdb->prepare(
545	"SELECT id FROM {$wpdb->prefix}metasync_otto_bot_logs
546	ORDER BY last_seen_at DESC, id DESC
547	LIMIT 1 OFFSET %d",
548	self::MAX_LOG_ENTRIES
549	)
550	);
551
552	if ($threshold_id) {
553	$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository for OTTO bot analytics (admin/cron aggregation views)
554	$wpdb->prepare(
555	"DELETE FROM {$wpdb->prefix}metasync_otto_bot_logs
556	WHERE id <= %d
557	AND id NOT IN (
558	SELECT id FROM (
559	SELECT id FROM {$wpdb->prefix}metasync_otto_bot_logs
560	ORDER BY last_seen_at DESC, id DESC
561	LIMIT %d
562	) AS keep_rows
563	)",
564	$threshold_id,
565	self::MAX_LOG_ENTRIES
566	)
567	);
568	}
569	}
570
571	// ------------------------------------------------------------------
572	// Uninstall
573	// ------------------------------------------------------------------
574
575	/**
576	* Drop all tables (for plugin uninstall)
577	*
578	* @return void
579	*/
580	public static function drop_tables() {
581	global $wpdb;
582	$wpdb->query("DROP TABLE IF EXISTS {$wpdb->prefix}metasync_otto_bot_stats"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- uninstall cleanup — intentional table removal when the plugin is deleted
583	$wpdb->query("DROP TABLE IF EXISTS {$wpdb->prefix}metasync_otto_bot_logs"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- uninstall cleanup — intentional table removal when the plugin is deleted
584
585	delete_option('metasync_otto_bot_stats_db_version');
586	}
587	}
588