metasync / database / class-db-migrations.php

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

521 lines 20.7 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	// Create Robots.txt Backups Table
200	require_once dirname(__FILE__, 2) . '/robots-txt/class-metasync-robots-txt-database.php';
201	$robots_db = Metasync_Robots_Txt_Database::get_instance();
202	$table_name_robots = esc_sql($wpdb->prefix . 'metasync_robots_txt_backups');
203	if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table_name_robots)) != $table_name_robots) {
204	$robots_db->create_table();
205	}
206
207	}
208
209	/**
210	* deactivation of migration.
211	*/
212	public static function deactivation()
213	{
214	global $wpdb;
215	// require_once dirname(__FILE__, 2) . '/404-monitor/class-metasync-404-monitor-database.php';
216	// $tableName = esc_sql($wpdb->prefix . Metasync_Error_Monitor_Database::$table_name);
217
218	/* drop wp_metasync_404_logs table */
219	// $sql = "DROP TABLE IF EXISTS `$tableName` ";
220	// $wpdb->query($sql);
221
222	// require_once dirname(__FILE__, 2) . '/redirections/class-metasync-redirection-database.php';
223	// $tableNameRedirection = esc_sql($wpdb->prefix . Metasync_Redirection_Database::$table_name);
224
225	/* drop wp_metasync_redirections table */
226	// $sql = "DROP TABLE IF EXISTS `$tableNameRedirection` ";
227	// $wpdb->query($sql);
228
229	require_once dirname(__FILE__, 2) . '/heartbeat-error-monitor/class-metasync-heartbeat-error-monitor-database.php';
230	$tableNameHeartBeatErrorMonitor = esc_sql($wpdb->prefix . Metasync_HeartBeat_Error_Monitor_Database::$table_name);
231	/* drop wp_metasync_redirections table */
232	$sql = "DROP TABLE IF EXISTS `$tableNameHeartBeatErrorMonitor` ";
233	$wpdb->query($sql);
234	}
235
236	/**
237	* Run version-specific migrations
238	*/
239	public static function run_version_migrations($from_version, $to_version)
240	{
241	// If from_version is 9.9.9, always run all migrations
242	$force_run = ($from_version === '9.9.9');
243
244	// Migration for versions 2.5.4+ - Enhanced 404 monitor and redirections
245	if ($force_run \|\| version_compare($to_version, '2.5.4', '>=')) {
246	self::migrate_enhanced_features_v2_5_4();
247	}
248
249	// Migration for versions 2.5.6+ - Robots.txt management
250	if ($force_run \|\| version_compare($to_version, '2.5.6', '>=')) {
251	self::migrate_robots_txt_v2_5_6();
252	}
253
254	// Migration for versions 2.5.9+ - OTTO Excluded URLs
255	if ($force_run \|\| version_compare($to_version, '2.5.9', '>=')) {
256	self::migrate_otto_excluded_urls_v2_5_9();
257	}
258
259	// Add more version-specific migrations here as needed
260	// if (version_compare($from_version, '1.1.0', '<')) {
261	// self::migrate_something_v1_1();
262	// }
263	}
264
265	/**
266	* Migrate enhanced features for version 2.5.4+
267	*/
268	private static function migrate_enhanced_features_v2_5_4()
269	{
270	global $wpdb;
271	$collate = $wpdb->get_charset_collate();
272
273	// Load WordPress upgrade functions for dbDelta
274	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
275
276	// Enhanced 404 Error Monitor Table
277	require_once dirname(__FILE__, 2) . '/404-monitor/class-metasync-404-monitor-database.php';
278	$tableName404Monitor = esc_sql($wpdb->prefix . Metasync_Error_Monitor_Database::$table_name);
279
280	if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s ", $tableName404Monitor)) != $tableName404Monitor) {
281	// Table doesn't exist, create enhanced version
282	$table_sql = "CREATE TABLE {$tableName404Monitor} (
283	id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
284	uri TEXT NOT NULL,
285	hits_count BIGINT(20) unsigned NOT NULL DEFAULT '1',
286	date_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
287	user_agent TEXT NULL,
288	referer TEXT NULL,
289	ip_address VARCHAR(45) NULL,
290	PRIMARY KEY id (id),
291	KEY uri (uri(191)),
292	KEY hits_count (hits_count),
293	KEY date_time (date_time)
294	) $collate;";
295
296	dbDelta($table_sql);
297	} else {
298	// Table exists, check for missing columns and add them
299	$columns = $wpdb->get_col("DESCRIBE {$tableName404Monitor}");
300
301	// Add referer column if it doesn't exist
302	if (!in_array('referer', $columns)) {
303	$wpdb->query("ALTER TABLE {$tableName404Monitor} ADD COLUMN referer TEXT NULL AFTER user_agent");
304	}
305
306	// Add ip_address column if it doesn't exist
307	if (!in_array('ip_address', $columns)) {
308	$wpdb->query("ALTER TABLE {$tableName404Monitor} ADD COLUMN ip_address VARCHAR(45) NULL AFTER referer");
309	}
310
311	// Update uri column to TEXT if it's VARCHAR(255)
312	$uri_column = $wpdb->get_row("SHOW COLUMNS FROM {$tableName404Monitor} LIKE 'uri'");
313	if ($uri_column && strpos($uri_column->Type, 'varchar') !== false) {
314	$wpdb->query("ALTER TABLE {$tableName404Monitor} MODIFY COLUMN uri TEXT NOT NULL");
315	}
316
317	// Update user_agent column to TEXT if it's VARCHAR(255)
318	$ua_column = $wpdb->get_row("SHOW COLUMNS FROM {$tableName404Monitor} LIKE 'user_agent'");
319	if ($ua_column && strpos($ua_column->Type, 'varchar') !== false) {
320	$wpdb->query("ALTER TABLE {$tableName404Monitor} MODIFY COLUMN user_agent TEXT NULL");
321	}
322
323	// Add missing indexes
324	$indexes = $wpdb->get_results("SHOW INDEX FROM {$tableName404Monitor}");
325	$index_names = array_column($indexes, 'Key_name');
326
327	if (!in_array('hits_count', $index_names)) {
328	$wpdb->query("ALTER TABLE {$tableName404Monitor} ADD KEY hits_count (hits_count)");
329	}
330
331	if (!in_array('date_time', $index_names)) {
332	$wpdb->query("ALTER TABLE {$tableName404Monitor} ADD KEY date_time (date_time)");
333	}
334	}
335
336	// Enhanced Redirections Table with new columns
337	require_once dirname(__FILE__, 2) . '/redirections/class-metasync-redirection-database.php';
338	$tableNameRedirection = esc_sql($wpdb->prefix . Metasync_Redirection_Database::$table_name);
339
340	if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s ", $tableNameRedirection)) == $tableNameRedirection) {
341	// Table exists, check for new columns
342	$columns = $wpdb->get_col("DESCRIBE {$tableNameRedirection}");
343
344	// Add pattern_type column if it doesn't exist
345	if (!in_array('pattern_type', $columns)) {
346	$wpdb->query("ALTER TABLE {$tableNameRedirection} ADD COLUMN pattern_type ENUM('exact', 'contain', 'start', 'end', 'regex') NOT NULL DEFAULT 'exact' AFTER status");
347	}
348
349	// Add regex_pattern column if it doesn't exist
350	if (!in_array('regex_pattern', $columns)) {
351	$wpdb->query("ALTER TABLE {$tableNameRedirection} ADD COLUMN regex_pattern TEXT NULL AFTER pattern_type");
352	}
353
354	// Add description column if it doesn't exist
355	if (!in_array('description', $columns)) {
356	$wpdb->query("ALTER TABLE {$tableNameRedirection} ADD COLUMN description TEXT NULL AFTER regex_pattern");
357	}
358
359	// Add timestamp columns if they don't exist
360	if (!in_array('created_at', $columns)) {
361	$wpdb->query("ALTER TABLE {$tableNameRedirection} ADD COLUMN created_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' AFTER description");
362	}
363
364	if (!in_array('updated_at', $columns)) {
365	$wpdb->query("ALTER TABLE {$tableNameRedirection} ADD COLUMN updated_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' AFTER created_at");
366	}
367
368	if (!in_array('last_accessed_at', $columns)) {
369	$wpdb->query("ALTER TABLE {$tableNameRedirection} ADD COLUMN last_accessed_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' AFTER updated_at");
370	}
371
372	// Add indexes if they don't exist
373	$indexes = $wpdb->get_results("SHOW INDEX FROM {$tableNameRedirection}");
374	$index_names = array_column($indexes, 'Key_name');
375
376	if (!in_array('pattern_type', $index_names)) {
377	$wpdb->query("ALTER TABLE {$tableNameRedirection} ADD KEY pattern_type (pattern_type)");
378	}
379
380	if (!in_array('created_at', $index_names)) {
381	$wpdb->query("ALTER TABLE {$tableNameRedirection} ADD KEY created_at (created_at)");
382	}
383
384	// Set default pattern_type for existing records
385	$wpdb->query("UPDATE {$tableNameRedirection} SET pattern_type = 'exact' WHERE pattern_type IS NULL OR pattern_type = ''");
386	}
387	}
388
389	/**
390	* Migrate robots.txt management for version 2.5.6+
391	*/
392	private static function migrate_robots_txt_v2_5_6()
393	{
394	global $wpdb;
395
396	// Create Robots.txt Backups Table
397	require_once dirname(__FILE__, 2) . '/robots-txt/class-metasync-robots-txt-database.php';
398	$robots_db = Metasync_Robots_Txt_Database::get_instance();
399	$table_name = esc_sql($wpdb->prefix . 'metasync_robots_txt_backups');
400
401	// Check if table already exists
402	if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table_name)) != $table_name) {
403	// Table doesn't exist, create it
404	$robots_db->create_table();
405	}
406	}
407
408	/**
409	* Migrate OTTO Excluded URLs for version 2.5.9+
410	*/
411	private static function migrate_otto_excluded_urls_v2_5_9()
412	{
413	global $wpdb;
414	$collate = $wpdb->get_charset_collate();
415
416	// Load WordPress upgrade functions for dbDelta
417	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
418
419	// Create OTTO Excluded URLs Table
420	require_once dirname(__FILE__, 2) . '/otto/class-metasync-otto-excluded-urls-database.php';
421	$tableNameOttoExcludedURLs = esc_sql($wpdb->prefix . Metasync_Otto_Excluded_URLs_Database::$table_name);
422
423	// Check if table already exists
424	if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $tableNameOttoExcludedURLs)) != $tableNameOttoExcludedURLs) {
425	// Table doesn't exist, create it
426	$table_sql = "CREATE TABLE {$tableNameOttoExcludedURLs} (
427	id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
428	url_pattern TEXT NOT NULL,
429	pattern_type ENUM('exact', 'contain', 'start', 'end', 'regex') NOT NULL DEFAULT 'exact',
430	description TEXT NULL,
431	status VARCHAR(25) NOT NULL DEFAULT 'active',
432	is_permanent TINYINT(1) NOT NULL DEFAULT 0,
433	auto_excluded TINYINT(1) NOT NULL DEFAULT 0,
434	recheck_after DATETIME NULL DEFAULT NULL,
435	created_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
436	PRIMARY KEY id (id),
437	KEY status (status),
438	KEY pattern_type (pattern_type),
439	KEY created_at (created_at),
440	KEY is_permanent (is_permanent),
441	KEY auto_excluded (auto_excluded),
442	KEY recheck_after (recheck_after),
443	UNIQUE KEY url_pattern_type_unique (url_pattern(191), pattern_type)
444	) $collate;";
445
446	dbDelta($table_sql);
447
448	// Log successful migration
449	// error_log('MetaSync: OTTO Excluded URLs table created successfully (v2.5.9)');
450	} else {
451	// Table exists, verify structure and add any missing columns if needed
452	$columns = $wpdb->get_col("DESCRIBE {$tableNameOttoExcludedURLs}");
453
454	// Check for required columns and add if missing
455	$missing_columns = false;
456
457	if (!in_array('pattern_type', $columns)) {
458	$wpdb->query("ALTER TABLE {$tableNameOttoExcludedURLs} ADD COLUMN pattern_type ENUM('exact', 'contain', 'start', 'end', 'regex') NOT NULL DEFAULT 'exact' AFTER url_pattern");
459	$missing_columns = true;
460	}
461
462	if (!in_array('description', $columns)) {
463	$wpdb->query("ALTER TABLE {$tableNameOttoExcludedURLs} ADD COLUMN description TEXT NULL AFTER pattern_type");
464	$missing_columns = true;
465	}
466
467	if (!in_array('status', $columns)) {
468	$wpdb->query("ALTER TABLE {$tableNameOttoExcludedURLs} ADD COLUMN status VARCHAR(25) NOT NULL DEFAULT 'active' AFTER description");
469	$missing_columns = true;
470	}
471
472	if (!in_array('is_permanent', $columns)) {
473	$wpdb->query("ALTER TABLE {$tableNameOttoExcludedURLs} ADD COLUMN is_permanent TINYINT(1) NOT NULL DEFAULT 0 AFTER status");
474	$wpdb->query("ALTER TABLE {$tableNameOttoExcludedURLs} ADD KEY is_permanent (is_permanent)");
475	}
476
477	if (!in_array('auto_excluded', $columns)) {
478	$wpdb->query("ALTER TABLE {$tableNameOttoExcludedURLs} ADD COLUMN auto_excluded TINYINT(1) NOT NULL DEFAULT 0 AFTER is_permanent");
479	$wpdb->query("ALTER TABLE {$tableNameOttoExcludedURLs} ADD KEY auto_excluded (auto_excluded)");
480	// Backfill: mark existing 404 exclusions as auto_excluded
481	$wpdb->query("UPDATE {$tableNameOttoExcludedURLs} SET auto_excluded = 1 WHERE description = 'Auto-excluded: 404'");
482	}
483
484	if (!in_array('recheck_after', $columns)) {
485	$wpdb->query("ALTER TABLE {$tableNameOttoExcludedURLs} ADD COLUMN recheck_after DATETIME NULL DEFAULT NULL AFTER auto_excluded");
486	$wpdb->query("ALTER TABLE {$tableNameOttoExcludedURLs} ADD KEY recheck_after (recheck_after)");
487	// Backfill: set recheck_after = created_at + 7 days for auto-excluded URLs
488	$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')");
489	}
490
491	// Check and add indexes if they don't exist
492	$indexes = $wpdb->get_results("SHOW INDEX FROM {$tableNameOttoExcludedURLs}");
493	$index_names = array_column($indexes, 'Key_name');
494
495	if (!in_array('status', $index_names)) {
496	$wpdb->query("ALTER TABLE {$tableNameOttoExcludedURLs} ADD KEY status (status)");
497	}
498
499	if (!in_array('pattern_type', $index_names)) {
500	$wpdb->query("ALTER TABLE {$tableNameOttoExcludedURLs} ADD KEY pattern_type (pattern_type)");
501	}
502
503	if (!in_array('created_at', $index_names)) {
504	$wpdb->query("ALTER TABLE {$tableNameOttoExcludedURLs} ADD KEY created_at (created_at)");
505	}
506
507	// Add unique index on url_pattern + pattern_type to prevent duplicates at database level
508	// Note: TEXT columns need a prefix length for indexing (767 is max for UTF8)
509	if (!in_array('url_pattern_type_unique', $index_names)) {
510	$wpdb->query("ALTER TABLE {$tableNameOttoExcludedURLs} ADD UNIQUE KEY url_pattern_type_unique (url_pattern(191), pattern_type)");
511	}
512
513	// if ($missing_columns) {
514	// error_log('MetaSync: OTTO Excluded URLs table structure updated (v2.5.9)');
515	// }
516	}
517	}
518
519
520	}
521