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.0, at otto/class-metasync-otto-bot-statistics-database.php

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