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

583 lines 17.5 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	'seo_tool_bots',
222	'social_media_bots',
223	'archiver_bots',
224	'generic_bots',
225	'other_bots',
226	'unknown_bots'
227	);
228
229	foreach ($defaults as $key) {
230	// Only insert if it doesn't exist yet; never reset existing counters
231	$this->wpdb->query(
232	$this->wpdb->prepare(
233	"INSERT IGNORE INTO {$this->table} (stat_key, stat_value) VALUES (%s, 0)",
234	$key
235	)
236	);
237	}
238	}
239
240	// ------------------------------------------------------------------
241	// Write operations
242	// ------------------------------------------------------------------
243
244	/**
245	* Record a bot detection event.
246	*
247	* Uses INSERT ... ON DUPLICATE KEY UPDATE so that repeated visits from
248	* the same bot_name + ip_address simply increment hit_count and refresh
249	* last_seen_at / user_agent / url instead of creating duplicate rows.
250	*
251	* @param string $bot_name Bot identifier (e.g. "Googlebot")
252	* @param string $bot_type Category key (e.g. "search_engine")
253	* @param string $user_agent Full user-agent string
254	* @param string\|null $ip_address Client IP address
255	* @param string\|null $detection_method How the bot was detected
256	* @return bool True on success
257	*/
258	public function add_detection($bot_name, $bot_type, $user_agent, $ip_address = null, $detection_method = null) {
259	$url = isset($_SERVER['REQUEST_URI']) ? home_url($_SERVER['REQUEST_URI']) : '';
260	$now = current_time('mysql');
261
262	$bot_name_clean = sanitize_text_field($bot_name);
263	$bot_type_clean = sanitize_text_field($bot_type);
264	$user_agent_clean = sanitize_textarea_field($user_agent);
265	$ip_clean = sanitize_text_field($ip_address ?? '');
266	$method_clean = sanitize_text_field($detection_method ?? '');
267	$url_clean = esc_url_raw($url);
268
269	// Upsert: insert new or increment existing
270	$result = $this->wpdb->query(
271	$this->wpdb->prepare(
272	"INSERT INTO {$this->logs_table}
273	(bot_name, bot_type, user_agent, ip_address, detection_method, url, hit_count, first_seen_at, last_seen_at)
274	VALUES (%s, %s, %s, %s, %s, %s, 1, %s, %s)
275	ON DUPLICATE KEY UPDATE
276	hit_count = hit_count + 1,
277	last_seen_at = VALUES(last_seen_at),
278	user_agent = VALUES(user_agent),
279	url = VALUES(url),
280	bot_type = VALUES(bot_type),
281	detection_method = VALUES(detection_method)",
282	$bot_name_clean,
283	$bot_type_clean,
284	$user_agent_clean,
285	$ip_clean,
286	$method_clean,
287	$url_clean,
288	$now,
289	$now
290	)
291	);
292
293	if ($result === false) {
294	return false;
295	}
296
297	// Update aggregate statistics
298	$this->increment_stat('total_detections');
299
300	$category_key = $this->get_category_stat_key($bot_type_clean);
301	if ($category_key) {
302	$this->increment_stat($category_key);
303	}
304
305	// Evict oldest entries beyond the cap
306	$this->enforce_log_cap();
307
308	return true;
309	}
310
311	/**
312	* Increment API calls saved counter
313	*
314	* @param int $count Number of calls saved
315	* @return bool
316	*/
317	public function increment_api_calls_saved($count = 1) {
318	return $this->increment_stat('api_calls_saved', $count);
319	}
320
321	/**
322	* Reset all statistics and logs
323	*
324	* @return bool
325	*/
326	public function reset_statistics() {
327	$a = $this->wpdb->query("TRUNCATE TABLE {$this->table}");
328	$b = $this->wpdb->query("TRUNCATE TABLE {$this->logs_table}");
329
330	$this->initialize_default_stats();
331
332	return $a !== false && $b !== false;
333	}
334
335	// ------------------------------------------------------------------
336	// Read operations
337	// ------------------------------------------------------------------
338
339	/**
340	* Get aggregate statistics
341	*
342	* @return array
343	*/
344	public function get_statistics() {
345	$rows = $this->wpdb->get_results(
346	"SELECT stat_key, stat_value FROM {$this->table}",
347	OBJECT_K
348	);
349
350	$result = array(
351	'total_detections' => 0,
352	'api_calls_saved' => 0,
353	'breakdown' => array(
354	'search_engine' => 0,
355	'seo_tool' => 0,
356	'social_media' => 0,
357	'archiver' => 0,
358	'generic' => 0,
359	'other' => 0,
360	'unknown' => 0
361	)
362	);
363
364	if (!$rows) {
365	return $result;
366	}
367
368	$val = function ($key) use ($rows) {
369	return isset($rows[$key]) ? (int)$rows[$key]->stat_value : 0;
370	};
371
372	$result['total_detections'] = $val('total_detections');
373	$result['api_calls_saved'] = $val('api_calls_saved');
374
375	$result['breakdown']['search_engine'] = $val('search_engine_bots');
376	$result['breakdown']['seo_tool'] = $val('seo_tool_bots');
377	$result['breakdown']['social_media'] = $val('social_media_bots');
378	$result['breakdown']['archiver'] = $val('archiver_bots');
379	$result['breakdown']['generic'] = $val('generic_bots');
380	$result['breakdown']['other'] = $val('other_bots');
381	$result['breakdown']['unknown'] = $val('unknown_bots');
382
383	return $result;
384	}
385
386	/**
387	* Get unique bot entries ordered by most recently seen
388	*
389	* @param int $limit Max rows to return
390	* @param int $offset Pagination offset
391	* @return array
392	*/
393	public function get_recent_requests($limit = 100, $offset = 0) {
394	$results = $this->wpdb->get_results(
395	$this->wpdb->prepare(
396	"SELECT * FROM {$this->logs_table}
397	ORDER BY last_seen_at DESC, hit_count DESC
398	LIMIT %d OFFSET %d",
399	$limit,
400	$offset
401	),
402	ARRAY_A
403	);
404
405	return $results ?: array();
406	}
407
408	/**
409	* Get total count of unique bot entries
410	*
411	* @return int
412	*/
413	public function get_total_log_count() {
414	return (int)$this->wpdb->get_var("SELECT COUNT(*) FROM {$this->logs_table}");
415	}
416
417	/**
418	* Get logs filtered by bot type
419	*
420	* @param string $bot_type Category key
421	* @param int $limit Max rows
422	* @return array
423	*/
424	public function get_logs_by_type($bot_type, $limit = 100) {
425	return $this->wpdb->get_results(
426	$this->wpdb->prepare(
427	"SELECT * FROM {$this->logs_table}
428	WHERE bot_type = %s
429	ORDER BY last_seen_at DESC, hit_count DESC
430	LIMIT %d",
431	$bot_type,
432	$limit
433	),
434	ARRAY_A
435	) ?: array();
436	}
437
438	/**
439	* Get logs within a date range (based on last_seen_at)
440	*
441	* @param string $start_date Y-m-d H:i:s
442	* @param string $end_date Y-m-d H:i:s
443	* @param int $limit Max rows
444	* @return array
445	*/
446	public function get_logs_by_date_range($start_date, $end_date, $limit = 1000) {
447	return $this->wpdb->get_results(
448	$this->wpdb->prepare(
449	"SELECT * FROM {$this->logs_table}
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	return $this->wpdb->delete(
469	$this->logs_table,
470	array('id' => (int)$log_id),
471	array('%d')
472	) !== false;
473	}
474
475	// ------------------------------------------------------------------
476	// Internal helpers
477	// ------------------------------------------------------------------
478
479	/**
480	* Atomically increment a stat counter using a single query
481	*
482	* @param string $stat_key The statistic key
483	* @param int $increment Amount to add
484	* @return bool
485	*/
486	private function increment_stat($stat_key, $increment = 1) {
487	// Use INSERT ... ON DUPLICATE KEY UPDATE for atomic upsert
488	return $this->wpdb->query(
489	$this->wpdb->prepare(
490	"INSERT INTO {$this->table} (stat_key, stat_value)
491	VALUES (%s, %d)
492	ON DUPLICATE KEY UPDATE stat_value = stat_value + %d",
493	$stat_key,
494	$increment,
495	$increment
496	)
497	) !== false;
498	}
499
500	/**
501	* Map bot_type to its aggregate stat key
502	*
503	* @param string $bot_type Bot category
504	* @return string Stat key
505	*/
506	private function get_category_stat_key($bot_type) {
507	$map = array(
508	'search_engine' => 'search_engine_bots',
509	'seo_tool' => 'seo_tool_bots',
510	'social_media' => 'social_media_bots',
511	'archiver' => 'archiver_bots',
512	'generic' => 'generic_bots',
513	'other' => 'other_bots',
514	'unknown' => 'unknown_bots',
515	'ip_based' => 'other_bots'
516	);
517
518	return isset($map[$bot_type]) ? $map[$bot_type] : 'other_bots';
519	}
520
521	/**
522	* Enforce the maximum number of unique log entries.
523	* Deletes the oldest entries (by last_seen_at) beyond the cap.
524	*
525	* @return void
526	*/
527	private function enforce_log_cap() {
528	$count = $this->get_total_log_count();
529
530	if ($count <= self::MAX_LOG_ENTRIES) {
531	return;
532	}
533
534	// Find the ID threshold: keep the newest MAX_LOG_ENTRIES rows
535	$threshold_id = $this->wpdb->get_var(
536	$this->wpdb->prepare(
537	"SELECT id FROM {$this->logs_table}
538	ORDER BY last_seen_at DESC, id DESC
539	LIMIT 1 OFFSET %d",
540	self::MAX_LOG_ENTRIES
541	)
542	);
543
544	if ($threshold_id) {
545	$this->wpdb->query(
546	$this->wpdb->prepare(
547	"DELETE FROM {$this->logs_table}
548	WHERE id <= %d
549	AND id NOT IN (
550	SELECT id FROM (
551	SELECT id FROM {$this->logs_table}
552	ORDER BY last_seen_at DESC, id DESC
553	LIMIT %d
554	) AS keep_rows
555	)",
556	$threshold_id,
557	self::MAX_LOG_ENTRIES
558	)
559	);
560	}
561	}
562
563	// ------------------------------------------------------------------
564	// Uninstall
565	// ------------------------------------------------------------------
566
567	/**
568	* Drop all tables (for plugin uninstall)
569	*
570	* @return void
571	*/
572	public static function drop_tables() {
573	global $wpdb;
574	$table = $wpdb->prefix . self::$table_name;
575	$logs_table = $wpdb->prefix . self::$logs_table_name;
576
577	$wpdb->query("DROP TABLE IF EXISTS {$table}");
578	$wpdb->query("DROP TABLE IF EXISTS {$logs_table}");
579
580	delete_option('metasync_otto_bot_stats_db_version');
581	}
582	}
583