metasync / database / class-db-migrations.php

class-db-migrations.php in Search Atlas SEO – OTTO AI SEO Automation for WordPress 2.5.23, at database/class-db-migrations.php

512 lines 20.3 KB

Raw Download Zip

1	<?php
2
3	/**
4	* The database migration for the plugin.
5	*
6	* @since 1.0.0
7	* @package Metasync
8	* @subpackage Metasync/database
9	* @author Engineering Team <support@searchatlas.com>
10	*/
11	// Some Plugins declare class name DBMigration to avoid conflict, renamed the class
12	class MetaSync_DBMigration
13	{
14
15	/**
16	* activation of migration.
17	*/
18	public static function activation()
19	{
20	self::run_migrations();
21	}
22
23	/**
24	* Run all database migrations
25	*/
26	public static function run_migrations()
27	{
28	global $wpdb;
29	$collate = $wpdb->get_charset_collate();
30
31	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
32
33	// Create 404 Monitor Table
34	require_once dirname(__FILE__, 2) . '/404-monitor/class-metasync-404-monitor-database.php';
35	$tableName = esc_sql($wpdb->prefix . Metasync_Error_Monitor_Database::$table_name);
36
37	if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $tableName)) != $tableName) {
38	$table_sql = "CREATE TABLE {$tableName} (
39	id BIGINT(20) unsigned NOT NULL AUTO_INCREMENT,
40	uri VARCHAR(255) NOT NULL,
41	date_time DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
42	hits_count BIGINT(20) unsigned NOT NULL DEFAULT 1,
43	user_agent VARCHAR(255) NOT NULL DEFAULT '',
44	PRIMARY KEY id (id),
45	KEY uri (uri(191))
46	) $collate;";
47
48	dbDelta($table_sql);
49	}
50
51	// Create Redirections Table
52	require_once dirname(__FILE__, 2) . '/redirections/class-metasync-redirection-database.php';
53	$tableNameRedirection = esc_sql($wpdb->prefix . Metasync_Redirection_Database::$table_name);
54
55	if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s ", $tableNameRedirection)) != $tableNameRedirection) {
56	$table_sql = "CREATE TABLE {$tableNameRedirection} (
57	id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
58	sources_from TEXT NOT NULL,
59	url_redirect_to TEXT NOT NULL,
60	http_code SMALLINT(4) unsigned NOT NULL DEFAULT 301,
61	hits_count BIGINT(20) unsigned NOT NULL DEFAULT '0',
62	status VARCHAR(25) NOT NULL DEFAULT 'active',
63	pattern_type ENUM('exact', 'contain', 'start', 'end', 'regex') NOT NULL DEFAULT 'exact',
64	regex_pattern TEXT NULL,
65	description TEXT NULL,
66	created_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
67	updated_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
68	last_accessed_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
69	PRIMARY KEY id (id),
70	KEY status (status),
71	KEY pattern_type (pattern_type),
72	KEY created_at (created_at),
73	KEY idx_active_redirects (status, sources_from(191))
74	) $collate;";
75
76	dbDelta($table_sql);
77	} else {
78	// Check if new columns exist and add them if they don't
79	$columns = $wpdb->get_col("DESCRIBE {$tableNameRedirection}");
80
81	if (!in_array('pattern_type', $columns)) {
82	$wpdb->query("ALTER TABLE {$tableNameRedirection} ADD COLUMN pattern_type ENUM('exact', 'contain', 'start', 'end', 'regex') NOT NULL DEFAULT 'exact' AFTER status");
83	}
84
85	if (!in_array('regex_pattern', $columns)) {
86	$wpdb->query("ALTER TABLE {$tableNameRedirection} ADD COLUMN regex_pattern TEXT NULL AFTER pattern_type");
87	}
88
89	if (!in_array('description', $columns)) {
90	$wpdb->query("ALTER TABLE {$tableNameRedirection} ADD COLUMN description TEXT NULL AFTER regex_pattern");
91	}
92
93	// Add indexes if they don't exist
94	$indexes = $wpdb->get_results("SHOW INDEX FROM {$tableNameRedirection}");
95	$index_names = array_column($indexes, 'Key_name');
96
97	if (!in_array('pattern_type', $index_names)) {
98	$wpdb->query("ALTER TABLE {$tableNameRedirection} ADD KEY pattern_type (pattern_type)");
99	}
100
101	if (!in_array('created_at', $index_names)) {
102	$wpdb->query("ALTER TABLE {$tableNameRedirection} ADD KEY created_at (created_at)");
103	}
104
105	// PERFORMANCE OPTIMIZATION: Add composite index for active redirects lookup
106	if (!in_array('idx_active_redirects', $index_names)) {
107	$wpdb->query("ALTER TABLE {$tableNameRedirection} ADD KEY idx_active_redirects (status, sources_from(191))");
108	}
109
110	// Set default pattern_type for existing records
111	$wpdb->query("UPDATE {$tableNameRedirection} SET pattern_type = 'exact' WHERE pattern_type IS NULL OR pattern_type = ''");
112	}
113
114	// Create HeartBeat Error Monitor Table
115	require_once dirname(__FILE__, 2) . '/heartbeat-error-monitor/class-metasync-heartbeat-error-monitor-database.php';
116	$tableNameHeartBeatErrorMonitor = esc_sql($wpdb->prefix . Metasync_HeartBeat_Error_Monitor_Database::$table_name);
117
118	if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s ", $tableNameHeartBeatErrorMonitor)) != $tableNameHeartBeatErrorMonitor) {
119	$table_sql = "CREATE TABLE {$tableNameHeartBeatErrorMonitor} (
120	id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
121	attribute_name VARCHAR(25) NOT NULL DEFAULT '',
122	object_count VARCHAR(25) NOT NULL DEFAULT '',
123	error_description TEXT NULL,
124	created_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
125	PRIMARY KEY id (id)
126	) $collate;";
127
128	dbDelta($table_sql);
129	}
130
131	// Create Sync History Table
132	require_once dirname(__FILE__, 2) . '/sync-history/class-metasync-sync-history-database.php';
133	$tableNameSyncHistory = esc_sql($wpdb->prefix . Metasync_Sync_History_Database::$table_name);
134
135	if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s ", $tableNameSyncHistory)) != $tableNameSyncHistory) {
136	$table_sql = "CREATE TABLE {$tableNameSyncHistory} (
137	id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
138	title VARCHAR(255) NOT NULL DEFAULT '',
139	source VARCHAR(50) NOT NULL DEFAULT '',
140	status VARCHAR(25) NOT NULL DEFAULT 'draft',
141	content_type VARCHAR(50) NOT NULL DEFAULT '',
142	url TEXT NULL,
143	meta_data TEXT NULL,
144	created_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
145	PRIMARY KEY id (id),
146	KEY source (source),
147	KEY status (status),
148	KEY created_at (created_at),
149	KEY idx_dedup (source, created_at),
150	KEY idx_search (title(50), source, created_at)
151	) $collate;";
152
153	dbDelta($table_sql);
154	} else {
155	// PERFORMANCE OPTIMIZATION: Add composite indexes to existing tables
156	// Check and add indexes if they don't exist
157	$indexes = $wpdb->get_results("SHOW INDEX FROM {$tableNameSyncHistory}");
158	$index_names = array_column($indexes, 'Key_name');
159
160	// Add deduplication index (source, created_at)
161	if (!in_array('idx_dedup', $index_names)) {
162	$wpdb->query("ALTER TABLE {$tableNameSyncHistory} ADD KEY idx_dedup (source, created_at)");
163	}
164
165	// Add search index (title(50), source, created_at)
166	if (!in_array('idx_search', $index_names)) {
167	$wpdb->query("ALTER TABLE {$tableNameSyncHistory} ADD KEY idx_search (title(50), source, created_at)");
168	}
169	}
170
171	// Create OTTO Excluded URLs Table
172	require_once dirname(__FILE__, 2) . '/otto/class-metasync-otto-excluded-urls-database.php';
173	$tableNameOttoExcludedURLs = esc_sql($wpdb->prefix . Metasync_Otto_Excluded_URLs_Database::$table_name);
174
175	if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s ", $tableNameOttoExcludedURLs)) != $tableNameOttoExcludedURLs) {
176	$table_sql = "CREATE TABLE {$tableNameOttoExcludedURLs} (
177	id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
178	url_pattern TEXT NOT NULL,
179	pattern_type ENUM('exact', 'contain', 'start', 'end', 'regex') NOT NULL DEFAULT 'exact',
180	description TEXT NULL,
181	status VARCHAR(25) NOT NULL DEFAULT 'active',
182	is_permanent TINYINT(1) NOT NULL DEFAULT 0,
183	auto_excluded TINYINT(1) NOT NULL DEFAULT 0,
184	recheck_after DATETIME NULL DEFAULT NULL,
185	created_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
186	PRIMARY KEY id (id),
187	KEY status (status),
188	KEY pattern_type (pattern_type),
189	KEY created_at (created_at),
190	KEY is_permanent (is_permanent),
191	KEY auto_excluded (auto_excluded),
192	KEY recheck_after (recheck_after),
193	UNIQUE KEY url_pattern_type_unique (url_pattern(191), pattern_type)
194	) $collate;";
195
196	dbDelta($table_sql);
197	}
198	}
199
200	/**
201	* deactivation of migration.
202	*/
203	public static function deactivation()
204	{
205	global $wpdb;
206	// require_once dirname(__FILE__, 2) . '/404-monitor/class-metasync-404-monitor-database.php';
207	// $tableName = esc_sql($wpdb->prefix . Metasync_Error_Monitor_Database::$table_name);
208
209	/* drop wp_metasync_404_logs table */
210	// $sql = "DROP TABLE IF EXISTS `$tableName` ";
211	// $wpdb->query($sql);
212
213	// require_once dirname(__FILE__, 2) . '/redirections/class-metasync-redirection-database.php';
214	// $tableNameRedirection = esc_sql($wpdb->prefix . Metasync_Redirection_Database::$table_name);
215
216	/* drop wp_metasync_redirections table */
217	// $sql = "DROP TABLE IF EXISTS `$tableNameRedirection` ";
218	// $wpdb->query($sql);
219
220	require_once dirname(__FILE__, 2) . '/heartbeat-error-monitor/class-metasync-heartbeat-error-monitor-database.php';
221	$tableNameHeartBeatErrorMonitor = esc_sql($wpdb->prefix . Metasync_HeartBeat_Error_Monitor_Database::$table_name);
222	/* drop wp_metasync_redirections table */
223	$sql = "DROP TABLE IF EXISTS `$tableNameHeartBeatErrorMonitor` ";
224	$wpdb->query($sql);
225	}
226
227	/**
228	* Run version-specific migrations
229	*/
230	public static function run_version_migrations($from_version, $to_version)
231	{
232	// If from_version is 9.9.9, always run all migrations
233	$force_run = ($from_version === '9.9.9');
234
235	// Migration for versions 2.5.4+ - Enhanced 404 monitor and redirections
236	if ($force_run \|\| version_compare($to_version, '2.5.4', '>=')) {
237	self::migrate_enhanced_features_v2_5_4();
238	}
239
240	// Migration for versions 2.5.6+ - Robots.txt management
241	if ($force_run \|\| version_compare($to_version, '2.5.6', '>=')) {
242	self::migrate_robots_txt_v2_5_6();
243	}
244
245	// Migration for versions 2.5.9+ - OTTO Excluded URLs
246	if ($force_run \|\| version_compare($to_version, '2.5.9', '>=')) {
247	self::migrate_otto_excluded_urls_v2_5_9();
248	}
249
250	// Add more version-specific migrations here as needed
251	// if (version_compare($from_version, '1.1.0', '<')) {
252	// self::migrate_something_v1_1();
253	// }
254	}
255
256	/**
257	* Migrate enhanced features for version 2.5.4+
258	*/
259	private static function migrate_enhanced_features_v2_5_4()
260	{
261	global $wpdb;
262	$collate = $wpdb->get_charset_collate();
263
264	// Load WordPress upgrade functions for dbDelta
265	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
266
267	// Enhanced 404 Error Monitor Table
268	require_once dirname(__FILE__, 2) . '/404-monitor/class-metasync-404-monitor-database.php';
269	$tableName404Monitor = esc_sql($wpdb->prefix . Metasync_Error_Monitor_Database::$table_name);
270
271	if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s ", $tableName404Monitor)) != $tableName404Monitor) {
272	// Table doesn't exist, create enhanced version
273	$table_sql = "CREATE TABLE {$tableName404Monitor} (
274	id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
275	uri TEXT NOT NULL,
276	hits_count BIGINT(20) unsigned NOT NULL DEFAULT '1',
277	date_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
278	user_agent TEXT NULL,
279	referer TEXT NULL,
280	ip_address VARCHAR(45) NULL,
281	PRIMARY KEY id (id),
282	KEY uri (uri(191)),
283	KEY hits_count (hits_count),
284	KEY date_time (date_time)
285	) $collate;";
286
287	dbDelta($table_sql);
288	} else {
289	// Table exists, check for missing columns and add them
290	$columns = $wpdb->get_col("DESCRIBE {$tableName404Monitor}");
291
292	// Add referer column if it doesn't exist
293	if (!in_array('referer', $columns)) {
294	$wpdb->query("ALTER TABLE {$tableName404Monitor} ADD COLUMN referer TEXT NULL AFTER user_agent");
295	}
296
297	// Add ip_address column if it doesn't exist
298	if (!in_array('ip_address', $columns)) {
299	$wpdb->query("ALTER TABLE {$tableName404Monitor} ADD COLUMN ip_address VARCHAR(45) NULL AFTER referer");
300	}
301
302	// Update uri column to TEXT if it's VARCHAR(255)
303	$uri_column = $wpdb->get_row("SHOW COLUMNS FROM {$tableName404Monitor} LIKE 'uri'");
304	if ($uri_column && strpos($uri_column->Type, 'varchar') !== false) {
305	$wpdb->query("ALTER TABLE {$tableName404Monitor} MODIFY COLUMN uri TEXT NOT NULL");
306	}
307
308	// Update user_agent column to TEXT if it's VARCHAR(255)
309	$ua_column = $wpdb->get_row("SHOW COLUMNS FROM {$tableName404Monitor} LIKE 'user_agent'");
310	if ($ua_column && strpos($ua_column->Type, 'varchar') !== false) {
311	$wpdb->query("ALTER TABLE {$tableName404Monitor} MODIFY COLUMN user_agent TEXT NULL");
312	}
313
314	// Add missing indexes
315	$indexes = $wpdb->get_results("SHOW INDEX FROM {$tableName404Monitor}");
316	$index_names = array_column($indexes, 'Key_name');
317
318	if (!in_array('hits_count', $index_names)) {
319	$wpdb->query("ALTER TABLE {$tableName404Monitor} ADD KEY hits_count (hits_count)");
320	}
321
322	if (!in_array('date_time', $index_names)) {
323	$wpdb->query("ALTER TABLE {$tableName404Monitor} ADD KEY date_time (date_time)");
324	}
325	}
326
327	// Enhanced Redirections Table with new columns
328	require_once dirname(__FILE__, 2) . '/redirections/class-metasync-redirection-database.php';
329	$tableNameRedirection = esc_sql($wpdb->prefix . Metasync_Redirection_Database::$table_name);
330
331	if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s ", $tableNameRedirection)) == $tableNameRedirection) {
332	// Table exists, check for new columns
333	$columns = $wpdb->get_col("DESCRIBE {$tableNameRedirection}");
334
335	// Add pattern_type column if it doesn't exist
336	if (!in_array('pattern_type', $columns)) {
337	$wpdb->query("ALTER TABLE {$tableNameRedirection} ADD COLUMN pattern_type ENUM('exact', 'contain', 'start', 'end', 'regex') NOT NULL DEFAULT 'exact' AFTER status");
338	}
339
340	// Add regex_pattern column if it doesn't exist
341	if (!in_array('regex_pattern', $columns)) {
342	$wpdb->query("ALTER TABLE {$tableNameRedirection} ADD COLUMN regex_pattern TEXT NULL AFTER pattern_type");
343	}
344
345	// Add description column if it doesn't exist
346	if (!in_array('description', $columns)) {
347	$wpdb->query("ALTER TABLE {$tableNameRedirection} ADD COLUMN description TEXT NULL AFTER regex_pattern");
348	}
349
350	// Add timestamp columns if they don't exist
351	if (!in_array('created_at', $columns)) {
352	$wpdb->query("ALTER TABLE {$tableNameRedirection} ADD COLUMN created_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' AFTER description");
353	}
354
355	if (!in_array('updated_at', $columns)) {
356	$wpdb->query("ALTER TABLE {$tableNameRedirection} ADD COLUMN updated_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' AFTER created_at");
357	}
358
359	if (!in_array('last_accessed_at', $columns)) {
360	$wpdb->query("ALTER TABLE {$tableNameRedirection} ADD COLUMN last_accessed_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' AFTER updated_at");
361	}
362
363	// Add indexes if they don't exist
364	$indexes = $wpdb->get_results("SHOW INDEX FROM {$tableNameRedirection}");
365	$index_names = array_column($indexes, 'Key_name');
366
367	if (!in_array('pattern_type', $index_names)) {
368	$wpdb->query("ALTER TABLE {$tableNameRedirection} ADD KEY pattern_type (pattern_type)");
369	}
370
371	if (!in_array('created_at', $index_names)) {
372	$wpdb->query("ALTER TABLE {$tableNameRedirection} ADD KEY created_at (created_at)");
373	}
374
375	// Set default pattern_type for existing records
376	$wpdb->query("UPDATE {$tableNameRedirection} SET pattern_type = 'exact' WHERE pattern_type IS NULL OR pattern_type = ''");
377	}
378	}
379
380	/**
381	* Migrate robots.txt management for version 2.5.6+
382	*/
383	private static function migrate_robots_txt_v2_5_6()
384	{
385	global $wpdb;
386
387	// Create Robots.txt Backups Table
388	require_once dirname(__FILE__, 2) . '/robots-txt/class-metasync-robots-txt-database.php';
389	$robots_db = Metasync_Robots_Txt_Database::get_instance();
390	$table_name = esc_sql($wpdb->prefix . 'metasync_robots_txt_backups');
391
392	// Check if table already exists
393	if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table_name)) != $table_name) {
394	// Table doesn't exist, create it
395	$robots_db->create_table();
396	}
397	}
398
399	/**
400	* Migrate OTTO Excluded URLs for version 2.5.9+
401	*/
402	private static function migrate_otto_excluded_urls_v2_5_9()
403	{
404	global $wpdb;
405	$collate = $wpdb->get_charset_collate();
406
407	// Load WordPress upgrade functions for dbDelta
408	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
409
410	// Create OTTO Excluded URLs Table
411	require_once dirname(__FILE__, 2) . '/otto/class-metasync-otto-excluded-urls-database.php';
412	$tableNameOttoExcludedURLs = esc_sql($wpdb->prefix . Metasync_Otto_Excluded_URLs_Database::$table_name);
413
414	// Check if table already exists
415	if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $tableNameOttoExcludedURLs)) != $tableNameOttoExcludedURLs) {
416	// Table doesn't exist, create it
417	$table_sql = "CREATE TABLE {$tableNameOttoExcludedURLs} (
418	id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
419	url_pattern TEXT NOT NULL,
420	pattern_type ENUM('exact', 'contain', 'start', 'end', 'regex') NOT NULL DEFAULT 'exact',
421	description TEXT NULL,
422	status VARCHAR(25) NOT NULL DEFAULT 'active',
423	is_permanent TINYINT(1) NOT NULL DEFAULT 0,
424	auto_excluded TINYINT(1) NOT NULL DEFAULT 0,
425	recheck_after DATETIME NULL DEFAULT NULL,
426	created_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
427	PRIMARY KEY id (id),
428	KEY status (status),
429	KEY pattern_type (pattern_type),
430	KEY created_at (created_at),
431	KEY is_permanent (is_permanent),
432	KEY auto_excluded (auto_excluded),
433	KEY recheck_after (recheck_after),
434	UNIQUE KEY url_pattern_type_unique (url_pattern(191), pattern_type)
435	) $collate;";
436
437	dbDelta($table_sql);
438
439	// Log successful migration
440	// error_log('MetaSync: OTTO Excluded URLs table created successfully (v2.5.9)');
441	} else {
442	// Table exists, verify structure and add any missing columns if needed
443	$columns = $wpdb->get_col("DESCRIBE {$tableNameOttoExcludedURLs}");
444
445	// Check for required columns and add if missing
446	$missing_columns = false;
447
448	if (!in_array('pattern_type', $columns)) {
449	$wpdb->query("ALTER TABLE {$tableNameOttoExcludedURLs} ADD COLUMN pattern_type ENUM('exact', 'contain', 'start', 'end', 'regex') NOT NULL DEFAULT 'exact' AFTER url_pattern");
450	$missing_columns = true;
451	}
452
453	if (!in_array('description', $columns)) {
454	$wpdb->query("ALTER TABLE {$tableNameOttoExcludedURLs} ADD COLUMN description TEXT NULL AFTER pattern_type");
455	$missing_columns = true;
456	}
457
458	if (!in_array('status', $columns)) {
459	$wpdb->query("ALTER TABLE {$tableNameOttoExcludedURLs} ADD COLUMN status VARCHAR(25) NOT NULL DEFAULT 'active' AFTER description");
460	$missing_columns = true;
461	}
462
463	if (!in_array('is_permanent', $columns)) {
464	$wpdb->query("ALTER TABLE {$tableNameOttoExcludedURLs} ADD COLUMN is_permanent TINYINT(1) NOT NULL DEFAULT 0 AFTER status");
465	$wpdb->query("ALTER TABLE {$tableNameOttoExcludedURLs} ADD KEY is_permanent (is_permanent)");
466	}
467
468	if (!in_array('auto_excluded', $columns)) {
469	$wpdb->query("ALTER TABLE {$tableNameOttoExcludedURLs} ADD COLUMN auto_excluded TINYINT(1) NOT NULL DEFAULT 0 AFTER is_permanent");
470	$wpdb->query("ALTER TABLE {$tableNameOttoExcludedURLs} ADD KEY auto_excluded (auto_excluded)");
471	// Backfill: mark existing 404 exclusions as auto_excluded
472	$wpdb->query("UPDATE {$tableNameOttoExcludedURLs} SET auto_excluded = 1 WHERE description = 'Auto-excluded: 404'");
473	}
474
475	if (!in_array('recheck_after', $columns)) {
476	$wpdb->query("ALTER TABLE {$tableNameOttoExcludedURLs} ADD COLUMN recheck_after DATETIME NULL DEFAULT NULL AFTER auto_excluded");
477	$wpdb->query("ALTER TABLE {$tableNameOttoExcludedURLs} ADD KEY recheck_after (recheck_after)");
478	// Backfill: set recheck_after = created_at + 7 days for auto-excluded URLs
479	$wpdb->query("UPDATE {$tableNameOttoExcludedURLs} SET recheck_after = DATE_ADD(created_at, INTERVAL 7 DAY) WHERE auto_excluded = 1 AND (recheck_after IS NULL OR recheck_after = '0000-00-00 00:00:00')");
480	}
481
482	// Check and add indexes if they don't exist
483	$indexes = $wpdb->get_results("SHOW INDEX FROM {$tableNameOttoExcludedURLs}");
484	$index_names = array_column($indexes, 'Key_name');
485
486	if (!in_array('status', $index_names)) {
487	$wpdb->query("ALTER TABLE {$tableNameOttoExcludedURLs} ADD KEY status (status)");
488	}
489
490	if (!in_array('pattern_type', $index_names)) {
491	$wpdb->query("ALTER TABLE {$tableNameOttoExcludedURLs} ADD KEY pattern_type (pattern_type)");
492	}
493
494	if (!in_array('created_at', $index_names)) {
495	$wpdb->query("ALTER TABLE {$tableNameOttoExcludedURLs} ADD KEY created_at (created_at)");
496	}
497
498	// Add unique index on url_pattern + pattern_type to prevent duplicates at database level
499	// Note: TEXT columns need a prefix length for indexing (767 is max for UTF8)
500	if (!in_array('url_pattern_type_unique', $index_names)) {
501	$wpdb->query("ALTER TABLE {$tableNameOttoExcludedURLs} ADD UNIQUE KEY url_pattern_type_unique (url_pattern(191), pattern_type)");
502	}
503
504	// if ($missing_columns) {
505	// error_log('MetaSync: OTTO Excluded URLs table structure updated (v2.5.9)');
506	// }
507	}
508	}
509
510
511	}
512