← All changes | includes/database/class-database-schema.php +565 -54 1.10.0 → 2.7.0 View file →

		@@ -19,8 +19,13 @@
19	19	declare(strict_types=1);
20	20
21	21	namespace ThinkRank\Database;
22	22
	23	+// Prevent direct access
	24	+if (!defined('ABSPATH')) {
	25	+ exit;
	26	+}
	27	+
23	28	/**
24	29	* Database Schema Manager Class
25	30	*
26	31	* Handles creation, management, and optimization of all SEO database tables.
		@@ -43,11 +48,84 @@
43	48	*
44	49	* @since 1.0.0
45	50	* @var string
46	51	*/
47		- private string $db_version = '1.0.0';
	52	+ private string $db_version = '1.9.2';
48	53
49	54	/**
	55	+ * Widest single indexed COLUMN InnoDB accepts on a COMPACT/REDUNDANT row
	56	+ * format, in bytes.
	57	+ *
	58	+ * The limit is per column, not per key: a key may total well over this as
	59	+ * long as no one column contributes more than 767 bytes. MySQL 5.7+ and
	60	+ * MariaDB 10.2+ default to DYNAMIC and raise it to 3072, but MySQL 5.6-era
	61	+ * servers (and anything with innodb_large_prefix off) enforce 767 — and on
	62	+ * a UNIQUE key it is fatal, because uniqueness cannot be guaranteed from a
	63	+ * truncated prefix, so the whole CREATE TABLE is rejected and the table
	64	+ * never exists (#298). A non-unique key is silently truncated instead.
	65	+ *
	66	+ * utf8mb4 costs 4 bytes per character, so varchar(191) = 764 bytes is the
	67	+ * widest column that fits — the same reason WordPress core uses 191.
	68	+ *
	69	+ * @since 1.30.0
	70	+ * @var int
	71	+ */
	72	+ private const MAX_INDEX_COLUMN_BYTES = 767;
	73	+
	74	+ /**
	75	+ * Wide keys replaced by prefixed equivalents, as new name => legacy name.
	76	+ *
	77	+ * dbDelta never drops or rewrites an existing index, so installs created
	78	+ * before #298 keep their full-width key. The prefixed key is added under a
	79	+ * new name (dbDelta only adds what is absent) and the legacy one is dropped
	80	+ * here once its replacement is confirmed present — never before, so a
	81	+ * failed ALTER leaves the table exactly as it was.
	82	+ *
	83	+ * @since 1.30.0
	84	+ * @var array<string, array<string, string>>
	85	+ */
	86	+ private const REPLACED_WIDE_INDEXES = [
	87	+ 'seo_settings' => ['unique_setting_v2' => 'unique_setting'],
	88	+ 'seo_social' => ['unique_social_meta_v2' => 'unique_social_meta'],
	89	+ 'ai_cache' => ['unique_cache_key' => 'cache_key'],
	90	+ ];
	91	+
	92	+ /**
	93	+ * Transient caching a verified-complete schema, so the missing-table probe
	94	+ * costs one query per hour on a healthy site rather than one per request.
	95	+ *
	96	+ * @since 1.28.0
	97	+ * @var string
	98	+ */
	99	+ private const TABLES_VERIFIED_TRANSIENT = 'thinkrank_schema_verified';
	100	+
	101	+ /**
	102	+ * Option holding why the last create_tables() run left a table missing.
	103	+ *
	104	+ * A rejected CREATE TABLE is the one schema failure the plugin cannot
	105	+ * recover from on its own: needs_update() re-runs creation on every request
	106	+ * precisely because a missing table is normally self-healing, so a database
	107	+ * that refuses the statement loops silently forever while every settings
	108	+ * screen fails. dbDelta swallows the error, so capture it here — it names
	109	+ * the cause (denied CREATE privilege, unsupported collation, index width)
	110	+ * that nothing else on the site reports.
	111	+ *
	112	+ * @since 1.32.1
	113	+ * @var string
	114	+ */
	115	+ private const CREATE_FAILURE_OPTION = 'thinkrank_schema_create_error';
	116	+
	117	+ /**
	118	+ * Throttle for the create-failure log line. Creation is re-attempted on
	119	+ * every request while a table is missing, and one log entry per request
	120	+ * per table would bury the error it is meant to surface.
	121	+ *
	122	+ * @since 1.32.1
	123	+ * @var string
	124	+ */
	125	+ private const CREATE_FAILURE_LOGGED_TRANSIENT = 'thinkrank_schema_create_error_logged';
	126	+
	127	+ /**
50	128	* Database table definitions with specifications
51	129	*
52	130	* Consolidated table definitions for all ThinkRank tables (11 total):
53	131	* - SEO Tables (7): Core SEO functionality with context-aware structure
		@@ -110,9 +188,11 @@
110	188	'description' => 'AI response caching for performance optimization',
111	189	'primary_key' => 'id',
112	190	'indexes' => ['cache_key', 'expires_at', 'created_at'],
113	191	'composite_indexes' => [
114		- 'cache_lookup' => ['cache_key', 'expires_at'],
	192	+ // cache_key is varchar(255) — 1020 bytes in utf8mb4, so it is
	193	+ // prefixed here for the same reason as the unique key (#298).
	194	+ 'cache_lookup' => ['cache_key(191)', 'expires_at'],
115	195	'cleanup_expired' => ['expires_at', 'created_at']
116	196	],
117	197	'foreign_keys' => []
118	198	],
		@@ -150,10 +230,28 @@
150	230	],
151	231	'instant_indexing_logs' => [
152	232	'description' => 'Log of IndexNow URL submissions',
153	233	'primary_key' => 'id',
154		- 'indexes' => ['status', 'response_code', 'created_at'],
	234	+ 'indexes' => ['url', 'status', 'response_code', 'created_at'],
155	235	'foreign_keys' => []
	236	+ ],
	237	+ 'email_report_logs' => [
	238	+ 'description' => 'Audit + dedupe log for scheduled SEO email reports',
	239	+ 'primary_key' => 'id',
	240	+ 'indexes' => ['site_id', 'status', 'sent_at', 'period_start'],
	241	+ 'composite_indexes' => [
	242	+ 'dedupe_key' => ['site_id', 'period_start', 'recipient_hash'],
	243	+ 'site_recent' => ['site_id', 'sent_at']
	244	+ ],
	245	+ 'foreign_keys' => []
	246	+ ],
	247	+
	248	+ // === AI VISIBILITY TABLES (1) ===
	249	+ 'ai_traffic' => [
	250	+ 'description' => 'Daily aggregate counters for AI referral traffic, AI crawler hits, and the all-traffic baseline',
	251	+ 'primary_key' => 'id',
	252	+ 'indexes' => ['day', 'kind'],
	253	+ 'foreign_keys' => []
156	254	]
157	255	];
158	256
159	257	/**
		@@ -173,9 +271,11 @@
173	271	private array $table_categories = [
174	272	'seo' => ['seo_settings', 'seo_analysis', 'seo_keywords', 'seo_schema', 'seo_social', 'seo_performance', 'seo_local', 'instant_indexing_logs'],
175	273	'ai' => ['ai_cache', 'ai_usage'],
176	274	'content' => ['content_briefs'],
177		- 'scoring' => ['seo_scores']
	275	+ 'scoring' => ['seo_scores'],
	276	+ 'reporting' => ['email_report_logs'],
	277	+ 'ai_visibility' => ['ai_traffic']
178	278	];
179	279
180	280	/**
181	281	* Constructor
		@@ -186,12 +286,16 @@
186	286	global $wpdb;
187	287	$this->wpdb = $wpdb;
188	288
189	289	// Set database configuration
190		- $this->db_config = [
191		- 'charset' => $wpdb->charset ?: 'utf8mb4',
192		- 'collate' => $wpdb->collate ?: 'utf8mb4_unicode_ci'
193		- ];
	290	+ // Ask WordPress for the clause rather than assembling one. Charset and
	291	+ // collation are not independent: DB_COLLATE is empty on most installs,
	292	+ // so a per-value fallback pairs the site's real charset with a default
	293	+ // collation that may not belong to it — `CHARACTER SET utf8 COLLATE
	294	+ // utf8mb4_unicode_ci` is rejected outright (MySQL 1253), and dbDelta
	295	+ // reports nothing, so every table silently fails to be created.
	296	+ // get_charset_collate() omits COLLATE when there is none to state.
	297	+ $this->db_config = ['charset_collate' => $wpdb->get_charset_collate()];
194	298	}
195	299
196	300	/**
197	301	* Create all database tables
		@@ -221,8 +325,14 @@
221	325
222	326	// Create table using dbDelta for WordPress compatibility
223	327	$result = dbDelta($sql);
224	328
	329	+ // dbDelta reports nothing when the database refuses the
	330	+ // statement, so wpdb's own error is the only account of why.
	331	+ // Read it now: table_exists() runs a query of its own, and
	332	+ // every wpdb query starts by clearing last_error.
	333	+ $db_error = (string) $this->wpdb->last_error;
	334	+
225	335	// Verify table creation
226	336	if ($this->table_exists($full_table_name)) {
227	337	$results['tables_created'][] = $full_table_name;
228	338
		@@ -232,9 +342,10 @@
232	342	// Add constraints if needed
233	343	$this->add_table_constraints($table_name);
234	344	} else {
235	345	$results['tables_failed'][] = $full_table_name;
236		- $results['errors'][] = "Failed to create table: {$full_table_name}";
	346	+ $results['errors'][] = "Failed to create table: {$full_table_name}"
	347	+ . ('' !== $db_error ? ' — ' . $db_error : '');
237	348	$results['success'] = false;
238	349	}
239	350	} catch (\Exception $e) {
240	351	$results['tables_failed'][] = $this->get_table_name($table_name);
		@@ -242,18 +353,76 @@
242	353	$results['success'] = false;
243	354	}
244	355	}
245	356
	357	+ // Retire the pre-#298 full-width keys now that their prefixed
	358	+ // replacements are in place.
	359	+ $this->drop_replaced_wide_indexes();
	360	+
	361	+ // Evict REST envelope keys that earlier saves stored as settings.
	362	+ $this->purge_envelope_setting_rows();
	363	+
	364	+ // And every other key no manager declares, stored the same way.
	365	+ $this->purge_unknown_setting_rows();
	366	+
246	367	// Update database version
247	368	if ($results['success']) {
248	369	update_option('thinkrank_seo_db_version', $this->db_version);
249	370	update_option('thinkrank_seo_db_created', current_time('mysql'));
	371	+ delete_option(self::CREATE_FAILURE_OPTION);
	372	+ } else {
	373	+ $this->record_create_failure($results['errors']);
250	374	}
251	375
	376	+ // The schema just changed, so any cached "verified complete" answer is
	377	+ // stale either way — drop it and let the next probe re-check.
	378	+ delete_transient(self::TABLES_VERIFIED_TRANSIENT);
	379	+
252	380	return $results;
253	381	}
254	382
255	383	/**
	384	+ * Keep the reason a table could not be created, and say it out loud once.
	385	+ *
	386	+ * @since 1.32.1
	387	+ *
	388	+ * @param string[] $errors Failure messages from create_tables().
	389	+ * @return void
	390	+ */
	391	+ private function record_create_failure(array $errors): void {
	392	+ $reason = implode('; ', array_filter($errors));
	393	+
	394	+ if ('' === $reason) {
	395	+ return;
	396	+ }
	397	+
	398	+ update_option(self::CREATE_FAILURE_OPTION, $reason, false);
	399	+
	400	+ if (get_transient(self::CREATE_FAILURE_LOGGED_TRANSIENT)) {
	401	+ return;
	402	+ }
	403	+
	404	+ set_transient(self::CREATE_FAILURE_LOGGED_TRANSIENT, 1, HOUR_IN_SECONDS);
	405	+
	406	+ // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- deliberate diagnostic; the UI can only report that a table is missing, never why.
	407	+ error_log('ThinkRank [schema]: table creation failed — ' . $reason);
	408	+ }
	409	+
	410	+ /**
	411	+ * Why the last table creation attempt failed, if it did.
	412	+ *
	413	+ * Read by the SEO managers so a "settings table does not exist" message can
	414	+ * name the database error behind it instead of guessing at causes.
	415	+ *
	416	+ * @since 1.32.1
	417	+ *
	418	+ * @return string Failure reason, or '' if creation last succeeded.
	419	+ */
	420	+ public static function get_last_create_failure(): string {
	421	+ return (string) get_option(self::CREATE_FAILURE_OPTION, '');
	422	+ }
	423	+
	424	+ /**
256	425	* Drop all database tables
257	426	*
258	427	* @since 1.0.0
259	428	*
		@@ -270,9 +439,9 @@
270	439	foreach (array_keys($this->table_definitions) as $table_name) {
271	440	try {
272	441	$full_table_name = $this->get_table_name($table_name);
273	442
274		- // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Plugin deactivation requires direct schema changes, DDL cannot be prepared, table name is validated
	443	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Plugin deactivation requires direct schema changes, DDL cannot be prepared, table name is validated
275	444	$result = $this->wpdb->query("DROP TABLE IF EXISTS `{$full_table_name}`");
276	445
277	446	if ($result !== false) {
278	447	$results['tables_dropped'][] = $full_table_name;
		@@ -305,12 +474,78 @@
305	474	* @return bool True if update needed
306	475	*/
307	476	public function needs_update(): bool {
308	477	$current_version = get_option('thinkrank_seo_db_version', '0.0.0');
309		- return version_compare($current_version, $this->db_version, '<');
	478	+
	479	+ if (version_compare($current_version, $this->db_version, '<')) {
	480	+ return true;
	481	+ }
	482	+
	483	+ // Version-only gating has now failed twice (#252, #270): if tables are
	484	+ // added but the version isn't moved — or a table is dropped, or an
	485	+ // upgrade half-completes — the stored version matches, the gate says
	486	+ // "nothing to do", and the feature is dead with no way back except
	487	+ // deactivate/reactivate. So also heal when a registered table is
	488	+ // actually missing. dbDelta only creates what's absent, making this
	489	+ // safe to re-run.
	490	+ return !empty($this->missing_tables());
310	491	}
311	492
312	493	/**
	494	+ * Registered tables that don't exist in the database.
	495	+ *
	496	+ * One `SHOW TABLES LIKE` for all of them, and the healthy answer is cached
	497	+ * so a correct install pays at most one extra query per hour rather than
	498	+ * one per request. The cache is cleared whenever tables are created.
	499	+ *
	500	+ * @since 1.28.0
	501	+ *
	502	+ * @return string[] Missing table names (full, prefixed).
	503	+ */
	504	+ public function missing_tables(): array {
	505	+ $cached = get_transient(self::TABLES_VERIFIED_TRANSIENT);
	506	+ if ($cached === $this->db_version) {
	507	+ return [];
	508	+ }
	509	+
	510	+ $expected = [];
	511	+ foreach (array_keys($this->table_definitions) as $table) {
	512	+ $expected[] = $this->get_table_name($table);
	513	+ }
	514	+
	515	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- schema probe; result cached below.
	516	+ $existing = (array) $this->wpdb->get_col(
	517	+ // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- table name is $wpdb->prefix plus a literal, and every value is passed as a placeholder replacement.
	518	+ $this->wpdb->prepare(
	519	+ 'SHOW TABLES LIKE %s',
	520	+ // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- table name is $wpdb->prefix plus a literal, and every value is passed as a placeholder replacement.
	521	+ $this->wpdb->esc_like($this->wpdb->prefix . 'thinkrank_') . '%'
	522	+ )
	523	+ );
	524	+ // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
	525	+ // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
	526	+
	527	+ $missing = array_values(array_diff($expected, $existing));
	528	+
	529	+ if (empty($missing)) {
	530	+ set_transient(self::TABLES_VERIFIED_TRANSIENT, $this->db_version, HOUR_IN_SECONDS);
	531	+ }
	532	+
	533	+ return $missing;
	534	+ }
	535	+
	536	+ /**
	537	+ * The schema version this plugin build expects (the target of needs_update).
	538	+ *
	539	+ * @since 1.23.0
	540	+ *
	541	+ * @return string Expected schema version, e.g. "1.2.0".
	542	+ */
	543	+ public function get_schema_version(): string {
	544	+ return $this->db_version;
	545	+ }
	546	+
	547	+ /**
313	548	* Get database status and information
314	549	*
315	550	* @since 1.0.0
316	551	*
		@@ -361,9 +596,9 @@
361	596	// Get table size before optimization
362	597	$size_before = $this->get_table_size($full_table_name);
363	598
364	599	// Optimize table
365		- // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table optimization requires direct database access, DDL cannot be prepared, table name is validated
	600	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table optimization requires direct database access, DDL cannot be prepared, table name is validated
366	601	$result = $this->wpdb->query("OPTIMIZE TABLE `{$full_table_name}`");
367	602
368	603	if ($result !== false) {
369	604	$size_after = $this->get_table_size($full_table_name);
		@@ -409,11 +644,11 @@
409	644	* @param string $table_name Full table name
410	645	* @return bool True if table exists
411	646	*/
412	647	private function table_exists(string $table_name): bool {
413		- // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Schema validation requires direct database access
	648	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Schema validation requires direct database access
414	649	$result = $this->wpdb->get_var(
415		- // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- SQL is properly prepared with placeholders
	650	+ // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- SQL is properly prepared with placeholders
416	651	$this->wpdb->prepare("SHOW TABLES LIKE %s", $table_name)
417	652	);
418	653
419	654	return $result === $table_name;
		@@ -425,13 +660,21 @@
425	660	* @since 1.0.0
426	661	*
427	662	* @param string $table_name Table name
428	663	* @return string SQL for table creation
	664	+ *
	665	+ * @throws \InvalidArgumentException On failure.
429	666	*/
430	667	private function get_table_sql(string $table_name): string {
431	668	$full_table_name = $this->get_table_name($table_name);
432		- $charset_collate = "DEFAULT CHARACTER SET {$this->db_config['charset']} COLLATE {$this->db_config['collate']}";
433	669
	670	+ // Pin the engine instead of inheriting the server's
	671	+ // default_storage_engine. The indexes below assume InnoDB: MyISAM caps
	672	+ // a key at 1000 bytes (seo_settings and seo_social exceed it) and
	673	+ // rejects descending indexes (seo_schema), so on a server defaulting
	674	+ // to MyISAM those tables were never created (#725).
	675	+ $charset_collate = trim('ENGINE=InnoDB ' . $this->db_config['charset_collate']);
	676	+
434	677	switch ($table_name) {
435	678	// SEO Tables
436	679	case 'seo_settings':
437	680	return $this->get_seo_settings_table_sql($full_table_name, $charset_collate);
		@@ -458,9 +701,15 @@
458	701	case 'seo_scores':
459	702	return $this->get_seo_scores_table_sql($full_table_name, $charset_collate);
460	703	case 'instant_indexing_logs':
461	704	return $this->get_instant_indexing_logs_table_sql($full_table_name, $charset_collate);
	705	+ case 'email_report_logs':
	706	+ return $this->get_email_report_logs_table_sql($full_table_name, $charset_collate);
462	707
	708	+ // AI Visibility Tables
	709	+ case 'ai_traffic':
	710	+ return $this->get_ai_traffic_table_sql($full_table_name, $charset_collate);
	711	+
463	712	default:
464	713	throw new \InvalidArgumentException('Unknown table: ' . esc_html($table_name));
465	714	}
466	715	}
		@@ -489,9 +738,9 @@
489	738	updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
490	739	created_by bigint(20) unsigned NULL,
491	740	updated_by bigint(20) unsigned NULL,
492	741	PRIMARY KEY (setting_id),
493		- UNIQUE KEY unique_setting (context_type, context_id, setting_category, setting_key),
	742	+ UNIQUE KEY unique_setting_v2 (context_type, context_id, setting_category, setting_key(191)),
494	743	KEY idx_context (context_type, context_id),
495	744	KEY idx_category (setting_category),
496	745	KEY idx_active (is_active),
497	746	KEY idx_created (created_at),
		@@ -634,9 +883,9 @@
634	883	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
635	884	updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
636	885	created_by bigint(20) unsigned NULL,
637	886	PRIMARY KEY (social_id),
638		- UNIQUE KEY unique_social_meta (context_type, context_id, platform, meta_key),
	887	+ UNIQUE KEY unique_social_meta_v2 (context_type, context_id, platform, meta_key(191)),
639	888	KEY idx_context (context_type, context_id),
640	889	KEY idx_platform (platform),
641	890	KEY idx_type (meta_type),
642	891	KEY idx_optimized (is_optimized),
		@@ -742,9 +991,9 @@
742	991	cache_data longtext NOT NULL,
743	992	expires_at bigint(20) unsigned NOT NULL,
744	993	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
745	994	PRIMARY KEY (id),
746		- UNIQUE KEY cache_key (cache_key),
	995	+ UNIQUE KEY unique_cache_key (cache_key(191)),
747	996	KEY expires_at_idx (expires_at),
748	997	KEY created_at_idx (created_at)
749	998	) {$charset_collate};";
750	999	}
		@@ -821,8 +1070,9 @@
821	1070	response_code int(11) NULL,
822	1071	response_message text NULL,
823	1072	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
824	1073	PRIMARY KEY (id),
	1074	+ KEY idx_url (url(191)),
825	1075	KEY idx_status (status),
826	1076	KEY idx_response_code (response_code),
827	1077	KEY idx_created (created_at)
828	1078	) {$charset_collate};";
		@@ -859,8 +1109,48 @@
859	1109	) {$charset_collate};";
860	1110	}
861	1111
862	1112	/**
	1113	+ * Get SQL for Email Report Logs table.
	1114	+ *
	1115	+ * Audit + dedupe log for scheduled SEO email reports. The
	1116	+ * `unique_send` constraint on (site_id, period_start, recipient_hash)
	1117	+ * is what prevents a given site from being sent the same period twice
	1118	+ * to the same recipient — required by the PRD.
	1119	+ *
	1120	+ * `recipient_hash` is a sha256 of the lowercased, sorted recipient list
	1121	+ * (so [a@x, b@x] and [b@x, a@x] dedupe to the same row).
	1122	+ *
	1123	+ * @since 1.9.0
	1124	+ *
	1125	+ * @param string $table_name Full table name.
	1126	+ * @param string $charset_collate Charset and collation.
	1127	+ * @return string SQL for table creation.
	1128	+ */
	1129	+ private function get_email_report_logs_table_sql(string $table_name, string $charset_collate): string {
	1130	+ return "CREATE TABLE `{$table_name}` (
	1131	+ id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
	1132	+ site_id bigint(20) unsigned NOT NULL DEFAULT 0,
	1133	+ period_start datetime NOT NULL,
	1134	+ period_end datetime NOT NULL,
	1135	+ recipient_hash char(64) NOT NULL,
	1136	+ recipient_count smallint(5) unsigned NOT NULL DEFAULT 1,
	1137	+ frequency_days smallint(5) unsigned NOT NULL DEFAULT 30,
	1138	+ status varchar(20) NOT NULL DEFAULT 'pending',
	1139	+ attempts smallint(5) unsigned NOT NULL DEFAULT 1,
	1140	+ error_message text NULL,
	1141	+ sent_at datetime NULL,
	1142	+ created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	1143	+ PRIMARY KEY (id),
	1144	+ UNIQUE KEY unique_send (site_id, period_start, recipient_hash),
	1145	+ KEY idx_site (site_id),
	1146	+ KEY idx_status (status),
	1147	+ KEY idx_sent (sent_at),
	1148	+ KEY idx_period (period_start)
	1149	+ ) {$charset_collate};";
	1150	+ }
	1151	+
	1152	+ /**
863	1153	* Create indexes for a specific table
864	1154	*
865	1155	* @since 1.0.0
866	1156	*
		@@ -883,9 +1173,9 @@
883	1173	}
884	1174
885	1175	$index_sql = $this->get_index_sql($full_table_name, $index_name);
886	1176	if ($index_sql) {
887		- // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.PreparedSQL.NotPrepared -- Index creation requires direct schema changes, DDL cannot be prepared
	1177	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Index creation requires direct schema changes, DDL cannot be prepared
888	1178	$result = $this->wpdb->query($index_sql);
889	1179	if (false === $result) {
890	1180	$success = false;
891	1181	}
		@@ -906,9 +1196,9 @@
906	1196	}
907	1197
908	1198	$index_sql = $this->get_composite_index_sql($full_table_name, $index_name, $columns);
909	1199	if ($index_sql) {
910		- // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.PreparedSQL.NotPrepared -- Composite index creation requires direct schema changes, DDL cannot be prepared
	1200	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Composite index creation requires direct schema changes, DDL cannot be prepared
911	1201	$result = $this->wpdb->query($index_sql);
912	1202	if (false === $result) {
913	1203	$success = false;
914	1204	}
		@@ -942,9 +1232,9 @@
942	1232	foreach ($definition['foreign_keys'] as $constraint) {
943	1233	try {
944	1234	$constraint_sql = $this->get_constraint_sql($full_table_name, $constraint);
945	1235	if ($constraint_sql) {
946		- // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.PreparedSQL.NotPrepared -- Constraint creation requires direct schema changes, DDL cannot be prepared
	1236	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Constraint creation requires direct schema changes, DDL cannot be prepared
947	1237	$result = $this->wpdb->query($constraint_sql);
948	1238	if (false === $result) {
949	1239	$success = false;
950	1240	}
		@@ -986,10 +1276,15 @@
986	1276	if (empty($columns)) {
987	1277	return '';
988	1278	}
989	1279
990		- // Escape column names
	1280	+ // Escape column names, preserving an optional key prefix — `col(191)`
	1281	+ // stays a prefix rather than becoming part of the column name (#298).
991	1282	$escaped_columns = array_map(function ($column) {
	1283	+ if (preg_match('/^([A-Za-z0-9_]+)\((\d+)\)$/', trim($column), $matches)) {
	1284	+ return "`{$matches[1]}`({$matches[2]})";
	1285	+ }
	1286	+
992	1287	return "`{$column}`";
993	1288	}, $columns);
994	1289
995	1290	$columns_sql = implode(', ', $escaped_columns);
		@@ -1038,16 +1333,16 @@
1038	1333
1039	1334	$info['exists'] = true;
1040	1335
1041	1336	// Get row count
1042		- // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table statistics require direct database access, table name cannot be prepared, table name is validated
	1337	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table statistics require direct database access, table name cannot be prepared, table name is validated
1043	1338	$row_count = $this->wpdb->get_var("SELECT COUNT(*) FROM `{$table_name}`");
1044	1339	$info['row_count'] = (int) $row_count;
1045	1340
1046	1341	// Get table size information
1047		- // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Table size information requires direct database access
	1342	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table size information requires direct database access
1048	1343	$size_info = $this->wpdb->get_row(
1049		- // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- SQL is properly prepared with placeholders
	1344	+ // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- SQL is properly prepared with placeholders
1050	1345	$this->wpdb->prepare(
1051	1346	"SELECT
1052	1347	data_length as data_size,
1053	1348	index_length as index_size,
		@@ -1055,11 +1350,11 @@
1055	1350	create_time as created,
1056	1351	update_time as updated
1057	1352	FROM information_schema.TABLES
1058	1353	WHERE table_schema = %s AND table_name = %s",
1059		- // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- DB_NAME is a WordPress constant, safe to use
	1354	+ // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- DB_NAME is a WordPress constant, safe to use
1060	1355	DB_NAME,
1061		- // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $table_name is validated and used as parameter
	1356	+ // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table_name is validated and used as parameter
1062	1357	$table_name
1063	1358	),
1064	1359	ARRAY_A
1065	1360	);
		@@ -1083,18 +1378,18 @@
1083	1378	* @param string $table_name Full table name
1084	1379	* @return int Table size in bytes
1085	1380	*/
1086	1381	private function get_table_size(string $table_name): int {
1087		- // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Table size calculation requires direct database access
	1382	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table size calculation requires direct database access
1088	1383	$size = $this->wpdb->get_var(
1089		- // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- SQL is properly prepared with placeholders
	1384	+ // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- SQL is properly prepared with placeholders
1090	1385	$this->wpdb->prepare(
1091	1386	"SELECT (data_length + index_length) as total_size
1092	1387	FROM information_schema.TABLES
1093	1388	WHERE table_schema = %s AND table_name = %s",
1094		- // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- DB_NAME is a WordPress constant, safe to use
	1389	+ // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- DB_NAME is a WordPress constant, safe to use
1095	1390	DB_NAME,
1096		- // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $table_name is validated and used as parameter
	1391	+ // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table_name is validated and used as parameter
1097	1392	$table_name
1098	1393	)
1099	1394	);
1100	1395
		@@ -1200,9 +1495,9 @@
1200	1495	}
1201	1496
1202	1497	$index_sql = $this->get_composite_index_sql($full_table_name, $index_name, $columns);
1203	1498	if ($index_sql) {
1204		- // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.PreparedSQL.NotPrepared -- Performance index creation requires direct schema changes, DDL cannot be prepared
	1499	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Performance index creation requires direct schema changes, DDL cannot be prepared
1205	1500	$result = $this->wpdb->query($index_sql);
1206	1501	if (false === $result) {
1207	1502	$success = false;
1208	1503	// Index creation failed - logged in database operations
		@@ -1219,8 +1514,199 @@
1219	1514	return $success;
1220	1515	}
1221	1516
1222	1517	/**
	1518	+ * Drop the full-width keys replaced by prefixed ones in #298.
	1519	+ *
	1520	+ * Installs created before the fix carry a key that spans more bytes than a
	1521	+ * 767-byte-limit server accepts; the prefixed replacement is added by
	1522	+ * dbDelta under a new name, and only once that replacement is confirmed
	1523	+ * present is the legacy key dropped. If the ALTER that adds the prefixed
	1524	+ * key failed — the one realistic cause being two existing rows that differ
	1525	+ * only past the prefix — nothing is dropped and the table keeps working
	1526	+ * exactly as before.
	1527	+ *
	1528	+ * @since 1.30.0
	1529	+ *
	1530	+ * @return void
	1531	+ */
	1532	+ /**
	1533	+ * Delete settings rows that hold a REST envelope instead of a setting.
	1534	+ *
	1535	+ * A caller that posted a settings endpoint's whole response body back as
	1536	+ * `settings` wrote `settings`, `schema`, `context_type` and `context_id`
	1537	+ * as rows. get_settings() returns every stored row, so those four then
	1538	+ * round-tripped into every later request — a serialized copy of the
	1539	+ * settings plus their JSON schema, several KB per save. Nothing reads
	1540	+ * them; sanitize_settings() now drops them on the way in, and this clears
	1541	+ * what is already stored.
	1542	+ *
	1543	+ * @since 2.0.1
	1544	+ *
	1545	+ * @return void
	1546	+ */
	1547	+ private function purge_envelope_setting_rows(): void {
	1548	+ $table = $this->get_table_name('seo_settings');
	1549	+
	1550	+ if (!$this->table_exists($table)) {
	1551	+ return;
	1552	+ }
	1553	+
	1554	+ // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- one-off cleanup; the table name comes from $wpdb->prefix and the keys are placeholders.
	1555	+ $this->wpdb->query(
	1556	+ $this->wpdb->prepare(
	1557	+ "DELETE FROM `{$table}` WHERE `setting_key` IN (%s, %s, %s, %s)",
	1558	+ 'settings',
	1559	+ 'schema',
	1560	+ 'context_type',
	1561	+ 'context_id'
	1562	+ )
	1563	+ );
	1564	+ // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter
	1565	+
	1566	+ if (function_exists('wp_cache_flush_group')) {
	1567	+ wp_cache_flush_group('thinkrank_seo');
	1568	+ }
	1569	+ }
	1570	+
	1571	+ /**
	1572	+ * Settings categories owned by an SEO manager, and the class that owns them.
	1573	+ *
	1574	+ * Used only by purge_unknown_setting_rows(). A category absent here is left
	1575	+ * alone rather than guessed at.
	1576	+ *
	1577	+ * @since 2.0.1
	1578	+ *
	1579	+ * @var array<string, string>
	1580	+ */
	1581	+ private const SETTINGS_CATEGORY_MANAGERS = [
	1582	+ 'site_identity' => \ThinkRank\SEO\Site_Identity_Manager::class,
	1583	+ 'sitemap' => \ThinkRank\SEO\Sitemap_Generator::class,
	1584	+ 'image_seo' => \ThinkRank\SEO\Image_SEO_Manager::class,
	1585	+ 'schema_management_system' => \ThinkRank\SEO\Schema_Management_System::class,
	1586	+ 'llms_txt' => \ThinkRank\SEO\LLMs_Txt_Manager::class,
	1587	+ 'social_meta' => \ThinkRank\SEO\Social_Meta_Manager::class,
	1588	+ 'seo_settings' => \ThinkRank\SEO\SEO_Settings_Manager::class,
	1589	+ 'content_optimization_manager' => \ThinkRank\SEO\Content_Optimization_Manager::class,
	1590	+ 'performance_monitoring_manager' => \ThinkRank\SEO\Performance_Monitoring_Manager::class,
	1591	+ 'ai_content_analyzer' => \ThinkRank\SEO\AI_Content_Analyzer::class,
	1592	+ ];
	1593	+
	1594	+ /**
	1595	+ * Delete settings rows holding keys no manager declares.
	1596	+ *
	1597	+ * The envelope purge above cleared four specific keys; this clears the
	1598	+ * general case behind them (#452). Any key a client posted was written as
	1599	+ * a row, and because get_settings() returns every row for a category — and
	1600	+ * save_settings() merges what it read before writing — a stray was echoed
	1601	+ * into every later response and rewritten on every save, so it never aged
	1602	+ * out on its own.
	1603	+ *
	1604	+ * Deliberately conservative: a category with no manager in the map, and a
	1605	+ * manager that cannot be constructed, are skipped rather than cleared, and
	1606	+ * the judgement is the manager's own accepts_setting_key() — the same gate
	1607	+ * the save path now applies, so the migration cannot delete a row the
	1608	+ * plugin would accept today.
	1609	+ *
	1610	+ * @since 2.0.1
	1611	+ *
	1612	+ * @return void
	1613	+ */
	1614	+ private function purge_unknown_setting_rows(): void {
	1615	+ $table = $this->get_table_name('seo_settings');
	1616	+
	1617	+ if (!$this->table_exists($table)) {
	1618	+ return;
	1619	+ }
	1620	+
	1621	+ // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- one-off cleanup; the table name comes from $wpdb->prefix and every value is a placeholder.
	1622	+ foreach (self::SETTINGS_CATEGORY_MANAGERS as $category => $class) {
	1623	+ if (!class_exists($class)) {
	1624	+ continue;
	1625	+ }
	1626	+
	1627	+ try {
	1628	+ $manager = new $class();
	1629	+ } catch (\Throwable $e) {
	1630	+ continue;
	1631	+ }
	1632	+
	1633	+ if (!method_exists($manager, 'accepts_setting_key')) {
	1634	+ continue;
	1635	+ }
	1636	+
	1637	+ $rows = $this->wpdb->get_results(
	1638	+ $this->wpdb->prepare(
	1639	+ "SELECT DISTINCT `setting_key`, `context_type` FROM `{$table}` WHERE `setting_category` = %s",
	1640	+ $category
	1641	+ )
	1642	+ );
	1643	+
	1644	+ if (empty($rows)) {
	1645	+ continue;
	1646	+ }
	1647	+
	1648	+ $unknown = [];
	1649	+
	1650	+ foreach ($rows as $row) {
	1651	+ $context = (string) $row->context_type;
	1652	+
	1653	+ if (!$manager->accepts_setting_key((string) $row->setting_key, $context)) {
	1654	+ $unknown[] = (string) $row->setting_key;
	1655	+ }
	1656	+ }
	1657	+
	1658	+ $unknown = array_values(array_unique($unknown));
	1659	+
	1660	+ if (empty($unknown)) {
	1661	+ continue;
	1662	+ }
	1663	+
	1664	+ $placeholders = implode(', ', array_fill(0, count($unknown), '%s'));
	1665	+
	1666	+ $this->wpdb->query(
	1667	+ $this->wpdb->prepare(
	1668	+ "DELETE FROM `{$table}` WHERE `setting_category` = %s AND `setting_key` IN ({$placeholders})",
	1669	+ array_merge([$category], $unknown)
	1670	+ )
	1671	+ );
	1672	+ }
	1673	+ // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter
	1674	+
	1675	+ if (function_exists('wp_cache_flush_group')) {
	1676	+ wp_cache_flush_group('thinkrank_seo');
	1677	+ }
	1678	+ }
	1679	+
	1680	+ private function drop_replaced_wide_indexes(): void {
	1681	+ foreach (self::REPLACED_WIDE_INDEXES as $table => $renames) {
	1682	+ $full_table_name = $this->get_table_name($table);
	1683	+
	1684	+ if (!$this->table_exists($full_table_name)) {
	1685	+ continue;
	1686	+ }
	1687	+
	1688	+ foreach ($renames as $current_index => $legacy_index) {
	1689	+ if (!$this->index_exists($full_table_name, $legacy_index)) {
	1690	+ continue;
	1691	+ }
	1692	+
	1693	+ if (!$this->index_exists($full_table_name, $current_index)) {
	1694	+ // The replacement is not there yet; keep the old key so the
	1695	+ // upsert still has a unique constraint to collide against.
	1696	+ continue;
	1697	+ }
	1698	+
	1699	+ // phpcs:disable WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- DDL cannot be prepared; both names come from a class constant and the table name from $wpdb->prefix.
	1700	+ $this->wpdb->query(
	1701	+ "ALTER TABLE `{$full_table_name}` DROP INDEX `" . esc_sql($legacy_index) . '`'
	1702	+ );
	1703	+ // phpcs:enable WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter
	1704	+ }
	1705	+ }
	1706	+ }
	1707	+
	1708	+ /**
1223	1709	* Check if an index exists on a table
1224	1710	*
1225	1711	* @since 1.0.0
1226	1712	*
		@@ -1228,19 +1714,19 @@
1228	1714	* @param string $index_name Index name
1229	1715	* @return bool Whether index exists
1230	1716	*/
1231	1717	private function index_exists(string $table_name, string $index_name): bool {
1232		- // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Index existence check requires direct database access
	1718	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Index existence check requires direct database access
1233	1719	$result = $this->wpdb->get_var(
1234		- // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- SQL is properly prepared with placeholders
	1720	+ // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- SQL is properly prepared with placeholders
1235	1721	$this->wpdb->prepare(
1236	1722	"SELECT COUNT(*) FROM information_schema.statistics
1237	1723	WHERE table_schema = %s AND table_name = %s AND index_name = %s",
1238		- // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- DB_NAME is a WordPress constant, safe to use
	1724	+ // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- DB_NAME is a WordPress constant, safe to use
1239	1725	DB_NAME,
1240		- // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $table_name is validated and used as parameter
	1726	+ // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table_name is validated and used as parameter
1241	1727	$table_name,
1242		- // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $index_name is validated and used as parameter
	1728	+ // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $index_name is validated and used as parameter
1243	1729	$index_name
1244	1730	)
1245	1731	);
1246	1732
		@@ -1247,32 +1733,57 @@
1247	1733	return (int) $result > 0;
1248	1734	}
1249	1735
1250	1736	/**
1251		- * Check if MySQL supports JSON column type with caching
	1737	+ * Check if the database server supports the JSON column type
1252	1738	*
1253		- * Uses WordPress's built-in database version detection and caches the result
1254		- * to avoid repeated database queries during schema creation.
	1739	+ * MySQL added JSON in 5.7.8 and MariaDB in 10.2.7. MariaDB's own 10.x
	1740	+ * number passes any MySQL threshold, and on older PHP the server string
	1741	+ * carries a `5.5.5-` replication prefix that db_version() reads as the
	1742	+ * version, so MariaDB's version is taken from the server string itself.
	1743	+ * Neither lookup queries the database.
1255	1744	*
1256	1745	* @since 1.0.0
1257		- * @return bool True if MySQL 5.7+ supports JSON columns
	1746	+ * @return bool True if the server supports JSON columns
1258	1747	*/
1259	1748	private function get_mysql_json_support(): bool {
1260		- // Check if we have cached result
1261		- static $json_support = null;
	1749	+ global $wpdb;
1262	1750
1263		- if ($json_support !== null) {
1264		- return $json_support;
	1751	+ $server_info = method_exists($wpdb, 'db_server_info') ? (string) $wpdb->db_server_info() : '';
	1752	+
	1753	+ if (preg_match('/(\d+(?:\.\d+)+)-MariaDB/i', $server_info, $matches)) {
	1754	+ return version_compare($matches[1], '10.2.7', '>=');
1265	1755	}
1266	1756
1267		- // Use WordPress's built-in database version method
1268		- global $wpdb;
	1757	+ return version_compare((string) $wpdb->db_version(), '5.7.8', '>=');
	1758	+ }
1269	1759
1270		- // Get MySQL version using WordPress method (safer than direct query)
1271		- $mysql_version = $wpdb->db_version();
1272		-
1273		- // Cache the result for subsequent calls
1274		- $json_support = version_compare($mysql_version, '5.7.0', '>=');
1275		-
1276		- return $json_support;
	1760	+ /**
	1761	+ * Get SQL for the AI traffic table.
	1762	+ *
	1763	+ * Daily aggregate counters only — one row per (day, kind, source, path).
	1764	+ * `kind` is 'referral' (human visit from an AI platform), 'crawler' (AI
	1765	+ * bot user-agent), or 'baseline' (all human pageviews, for the share-of-
	1766	+ * traffic figure). No IPs, no user agents, no per-visit rows: aggregates
	1767	+ * keep the table small and the feature privacy-clean.
	1768	+ *
	1769	+ * @since 1.27.0
	1770	+ *
	1771	+ * @param string $table_name Full table name
	1772	+ * @param string $charset_collate Charset and collation
	1773	+ * @return string SQL for table creation
	1774	+ */
	1775	+ private function get_ai_traffic_table_sql(string $table_name, string $charset_collate): string {
	1776	+ return "CREATE TABLE `{$table_name}` (
	1777	+ id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
	1778	+ day date NOT NULL,
	1779	+ kind varchar(12) NOT NULL,
	1780	+ source varchar(40) NOT NULL DEFAULT '',
	1781	+ path varchar(191) NOT NULL DEFAULT '',
	1782	+ hits bigint(20) unsigned NOT NULL DEFAULT 1,
	1783	+ PRIMARY KEY (id),
	1784	+ UNIQUE KEY uniq_bucket (day, kind, source, path),
	1785	+ KEY idx_day (day),
	1786	+ KEY idx_kind (kind)
	1787	+ ) {$charset_collate};";
1277	1788	}
1278	1789	}