thinkrank / includes / database / class-database-schema.php

class-database-schema.php in ThinkRank AI SEO – AI SEO Plugin for WordPress: Schema, XML Sitemaps, Meta Tags, Search Console & Local SEO 1.27.0, at includes/database/class-database-schema.php

1,342 lines 52.0 KB

Raw Download Zip

1	<?php
2
3	/**
4	* Database Schema Manager Class
5	*
6	* Comprehensive database schema implementation for ThinkRank SEO plugin.
7	* Creates and manages all 11 ThinkRank tables with proper indexes, constraints,
8	* and WordPress-compliant database operations following 2025 best practices.
9	*
10	* Tables managed:
11	* - SEO Tables (7): Settings, Analysis, Keywords, Schema, Social, Performance, Local
12	* - AI/Core Tables (4): AI Cache, AI Usage, Content Briefs, SEO Scores
13	*
14	* @package ThinkRank
15	* @subpackage Database
16	* @since 1.0.0
17	*/
18
19	declare(strict_types=1);
20
21	namespace ThinkRank\Database;
22
23	/**
24	* Database Schema Manager Class
25	*
26	* Handles creation, management, and optimization of all SEO database tables.
27	* Implements WordPress database standards with proper indexing and constraints.
28	*
29	* @since 1.0.0
30	*/
31	class Database_Schema {
32
33	/**
34	* WordPress database instance
35	*
36	* @since 1.0.0
37	* @var \wpdb
38	*/
39	private \wpdb $wpdb;
40
41	/**
42	* Database version for schema tracking
43	*
44	* @since 1.0.0
45	* @var string
46	*/
47	private string $db_version = '1.2.0';
48
49	/**
50	* Database table definitions with specifications
51	*
52	* Consolidated table definitions for all ThinkRank tables (11 total):
53	* - SEO Tables (7): Core SEO functionality with context-aware structure
54	* - AI/Core Tables (4): AI caching, usage tracking, content briefs, and scoring
55	*
56	* @since 1.0.0
57	* @var array
58	*/
59	private array $table_definitions = [
60	// === SEO TABLES (7) ===
61	'seo_settings' => [
62	'description' => 'Universal SEO settings storage with context-aware structure',
63	'primary_key' => 'setting_id',
64	'indexes' => ['context_type', 'context_id', 'setting_category', 'is_active'],
65	'foreign_keys' => []
66	],
67	'seo_analysis' => [
68	'description' => 'SEO analysis results and scoring data',
69	'primary_key' => 'analysis_id',
70	'indexes' => ['context_type', 'context_id', 'analysis_type', 'created_at'],
71	'composite_indexes' => [
72	'context_analysis_date' => ['context_type', 'analysis_type', 'created_at'],
73	'context_recent' => ['context_type', 'context_id', 'created_at']
74	],
75	'foreign_keys' => []
76	],
77	'seo_keywords' => [
78	'description' => 'Keyword tracking and optimization data',
79	'primary_key' => 'keyword_id',
80	'indexes' => ['context_type', 'context_id', 'keyword_type', 'keyword_hash'],
81	'foreign_keys' => []
82	],
83	'seo_schema' => [
84	'description' => 'Schema markup storage and validation',
85	'primary_key' => 'schema_id',
86	'indexes' => ['context_type', 'context_id', 'schema_type', 'is_active'],
87	'foreign_keys' => []
88	],
89	'seo_social' => [
90	'description' => 'Social media meta and optimization data',
91	'primary_key' => 'social_id',
92	'indexes' => ['context_type', 'context_id', 'platform', 'is_active'],
93	'foreign_keys' => []
94	],
95	'seo_performance' => [
96	'description' => 'Performance metrics and Core Web Vitals data',
97	'primary_key' => 'performance_id',
98	'indexes' => ['context_type', 'context_id', 'metric_type', 'measured_at'],
99	'foreign_keys' => []
100	],
101	'seo_local' => [
102	'description' => 'Local SEO and business data storage',
103	'primary_key' => 'local_id',
104	'indexes' => ['context_type', 'context_id', 'business_type', 'is_active'],
105	'foreign_keys' => []
106	],
107
108	// === AI/CORE TABLES (4) ===
109	'ai_cache' => [
110	'description' => 'AI response caching for performance optimization',
111	'primary_key' => 'id',
112	'indexes' => ['cache_key', 'expires_at', 'created_at'],
113	'composite_indexes' => [
114	'cache_lookup' => ['cache_key', 'expires_at'],
115	'cleanup_expired' => ['expires_at', 'created_at']
116	],
117	'foreign_keys' => []
118	],
119	'ai_usage' => [
120	'description' => 'AI usage tracking and token consumption monitoring',
121	'primary_key' => 'id',
122	'indexes' => ['user_id', 'action', 'provider', 'created_at'],
123	'composite_indexes' => [
124	'user_analytics' => ['user_id', 'created_at', 'provider'],
125	'provider_action' => ['provider', 'action', 'created_at'],
126	'user_provider_date' => ['user_id', 'provider', 'created_at']
127	],
128	'foreign_keys' => []
129	],
130	'content_briefs' => [
131	'description' => 'Generated content briefs storage and management',
132	'primary_key' => 'id',
133	'indexes' => ['user_id', 'content_type', 'created_at'],
134	'composite_indexes' => [
135	'user_content_date' => ['user_id', 'content_type', 'created_at'],
136	'user_recent' => ['user_id', 'created_at']
137	],
138	'foreign_keys' => []
139	],
140	'seo_scores' => [
141	'description' => 'SEO score calculations and historical tracking',
142	'primary_key' => 'id',
143	'indexes' => ['post_id', 'user_id', 'overall_score', 'grade', 'calculated_at', 'created_at'],
144	'composite_indexes' => [
145	'post_user_date' => ['post_id', 'user_id', 'created_at'],
146	'user_score_date' => ['user_id', 'overall_score', 'created_at'],
147	'post_latest' => ['post_id', 'calculated_at']
148	],
149	'foreign_keys' => []
150	],
151	'instant_indexing_logs' => [
152	'description' => 'Log of IndexNow URL submissions',
153	'primary_key' => 'id',
154	'indexes' => ['status', 'response_code', 'created_at'],
155	'foreign_keys' => []
156	],
157	'email_report_logs' => [
158	'description' => 'Audit + dedupe log for scheduled SEO email reports',
159	'primary_key' => 'id',
160	'indexes' => ['site_id', 'status', 'sent_at', 'period_start'],
161	'composite_indexes' => [
162	'dedupe_key' => ['site_id', 'period_start', 'recipient_hash'],
163	'site_recent' => ['site_id', 'sent_at']
164	],
165	'foreign_keys' => []
166	]
167	];
168
169	/**
170	* WordPress database charset and collation
171	*
172	* @since 1.0.0
173	* @var array
174	*/
175	private array $db_config;
176
177	/**
178	* Table categories for better organization and maintenance
179	*
180	* @since 1.0.0
181	* @var array
182	*/
183	private array $table_categories = [
184	'seo' => ['seo_settings', 'seo_analysis', 'seo_keywords', 'seo_schema', 'seo_social', 'seo_performance', 'seo_local', 'instant_indexing_logs'],
185	'ai' => ['ai_cache', 'ai_usage'],
186	'content' => ['content_briefs'],
187	'scoring' => ['seo_scores'],
188	'reporting' => ['email_report_logs']
189	];
190
191	/**
192	* Constructor
193	*
194	* @since 1.0.0
195	*/
196	public function __construct() {
197	global $wpdb;
198	$this->wpdb = $wpdb;
199
200	// Set database configuration
201	$this->db_config = [
202	'charset' => $wpdb->charset ?: 'utf8mb4',
203	'collate' => $wpdb->collate ?: 'utf8mb4_unicode_ci'
204	];
205	}
206
207	/**
208	* Create all database tables
209	*
210	* @since 1.0.0
211	*
212	* @return array Creation results with success/failure status
213	*/
214	public function create_tables(): array {
215	$results = [
216	'success' => true,
217	'tables_created' => [],
218	'tables_failed' => [],
219	'errors' => [],
220	'total_tables' => count($this->table_definitions)
221	];
222
223	// Require WordPress upgrade functions
224	if (!function_exists('dbDelta')) {
225	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
226	}
227
228	foreach ($this->table_definitions as $table_name => $definition) {
229	try {
230	$full_table_name = $this->get_table_name($table_name);
231	$sql = $this->get_table_sql($table_name);
232
233	// Create table using dbDelta for WordPress compatibility
234	$result = dbDelta($sql);
235
236	// Verify table creation
237	if ($this->table_exists($full_table_name)) {
238	$results['tables_created'][] = $full_table_name;
239
240	// Create indexes
241	$this->create_table_indexes($table_name);
242
243	// Add constraints if needed
244	$this->add_table_constraints($table_name);
245	} else {
246	$results['tables_failed'][] = $full_table_name;
247	$results['errors'][] = "Failed to create table: {$full_table_name}";
248	$results['success'] = false;
249	}
250	} catch (\Exception $e) {
251	$results['tables_failed'][] = $this->get_table_name($table_name);
252	$results['errors'][] = "Error creating {$table_name}: " . $e->getMessage();
253	$results['success'] = false;
254	}
255	}
256
257	// Update database version
258	if ($results['success']) {
259	update_option('thinkrank_seo_db_version', $this->db_version);
260	update_option('thinkrank_seo_db_created', current_time('mysql'));
261	}
262
263	return $results;
264	}
265
266	/**
267	* Drop all database tables
268	*
269	* @since 1.0.0
270	*
271	* @return array Deletion results
272	*/
273	public function drop_tables(): array {
274	$results = [
275	'success' => true,
276	'tables_dropped' => [],
277	'tables_failed' => [],
278	'errors' => []
279	];
280
281	foreach (array_keys($this->table_definitions) as $table_name) {
282	try {
283	$full_table_name = $this->get_table_name($table_name);
284
285	// 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
286	$result = $this->wpdb->query("DROP TABLE IF EXISTS `{$full_table_name}`");
287
288	if ($result !== false) {
289	$results['tables_dropped'][] = $full_table_name;
290	} else {
291	$results['tables_failed'][] = $full_table_name;
292	$results['errors'][] = "Failed to drop table: {$full_table_name}";
293	$results['success'] = false;
294	}
295	} catch (\Exception $e) {
296	$results['tables_failed'][] = $this->get_table_name($table_name);
297	$results['errors'][] = "Error dropping {$table_name}: " . $e->getMessage();
298	$results['success'] = false;
299	}
300	}
301
302	// Clean up options
303	if ($results['success']) {
304	delete_option('thinkrank_seo_db_version');
305	delete_option('thinkrank_seo_db_created');
306	}
307
308	return $results;
309	}
310
311	/**
312	* Check if database schema needs updates
313	*
314	* @since 1.0.0
315	*
316	* @return bool True if update needed
317	*/
318	public function needs_update(): bool {
319	$current_version = get_option('thinkrank_seo_db_version', '0.0.0');
320	return version_compare($current_version, $this->db_version, '<');
321	}
322
323	/**
324	* The schema version this plugin build expects (the target of needs_update).
325	*
326	* @since 1.23.0
327	*
328	* @return string Expected schema version, e.g. "1.2.0".
329	*/
330	public function get_schema_version(): string {
331	return $this->db_version;
332	}
333
334	/**
335	* Get database status and information
336	*
337	* @since 1.0.0
338	*
339	* @return array Database status information
340	*/
341	public function get_database_status(): array {
342	$status = [
343	'version' => get_option('thinkrank_seo_db_version', 'Not installed'),
344	'created_at' => get_option('thinkrank_seo_db_created', 'Unknown'),
345	'tables' => [],
346	'total_records' => 0,
347	'database_size' => 0,
348	'needs_update' => $this->needs_update()
349	];
350
351	foreach (array_keys($this->table_definitions) as $table_name) {
352	$full_table_name = $this->get_table_name($table_name);
353	$table_info = $this->get_table_info($full_table_name);
354
355	$status['tables'][$table_name] = $table_info;
356	$status['total_records'] += $table_info['row_count'];
357	$status['database_size'] += $table_info['data_size'];
358	}
359
360	return $status;
361	}
362
363	/**
364	* Optimize all database tables
365	*
366	* @since 1.0.0
367	*
368	* @return array Optimization results
369	*/
370	public function optimize_tables(): array {
371	$results = [
372	'success' => true,
373	'tables_optimized' => [],
374	'tables_failed' => [],
375	'space_saved' => 0,
376	'errors' => []
377	];
378
379	foreach (array_keys($this->table_definitions) as $table_name) {
380	try {
381	$full_table_name = $this->get_table_name($table_name);
382
383	// Get table size before optimization
384	$size_before = $this->get_table_size($full_table_name);
385
386	// Optimize table
387	// 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
388	$result = $this->wpdb->query("OPTIMIZE TABLE `{$full_table_name}`");
389
390	if ($result !== false) {
391	$size_after = $this->get_table_size($full_table_name);
392	$space_saved = $size_before - $size_after;
393
394	$results['tables_optimized'][] = [
395	'table' => $full_table_name,
396	'space_saved' => $space_saved
397	];
398	$results['space_saved'] += $space_saved;
399	} else {
400	$results['tables_failed'][] = $full_table_name;
401	$results['errors'][] = "Failed to optimize table: {$full_table_name}";
402	$results['success'] = false;
403	}
404	} catch (\Exception $e) {
405	$results['tables_failed'][] = $this->get_table_name($table_name);
406	$results['errors'][] = "Error optimizing {$table_name}: " . $e->getMessage();
407	$results['success'] = false;
408	}
409	}
410
411	return $results;
412	}
413
414	/**
415	* Get full table name with WordPress prefix
416	*
417	* @since 1.0.0
418	*
419	* @param string $table_name Base table name
420	* @return string Full table name with prefix
421	*/
422	private function get_table_name(string $table_name): string {
423	return $this->wpdb->prefix . 'thinkrank_' . $table_name;
424	}
425
426	/**
427	* Check if table exists
428	*
429	* @since 1.0.0
430	*
431	* @param string $table_name Full table name
432	* @return bool True if table exists
433	*/
434	private function table_exists(string $table_name): bool {
435	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Schema validation requires direct database access
436	$result = $this->wpdb->get_var(
437	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- SQL is properly prepared with placeholders
438	$this->wpdb->prepare("SHOW TABLES LIKE %s", $table_name)
439	);
440
441	return $result === $table_name;
442	}
443
444	/**
445	* Get SQL for creating a specific table
446	*
447	* @since 1.0.0
448	*
449	* @param string $table_name Table name
450	* @return string SQL for table creation
451	*/
452	private function get_table_sql(string $table_name): string {
453	$full_table_name = $this->get_table_name($table_name);
454	$charset_collate = "DEFAULT CHARACTER SET {$this->db_config['charset']} COLLATE {$this->db_config['collate']}";
455
456	switch ($table_name) {
457	// SEO Tables
458	case 'seo_settings':
459	return $this->get_seo_settings_table_sql($full_table_name, $charset_collate);
460	case 'seo_analysis':
461	return $this->get_seo_analysis_table_sql($full_table_name, $charset_collate);
462	case 'seo_keywords':
463	return $this->get_seo_keywords_table_sql($full_table_name, $charset_collate);
464	case 'seo_schema':
465	return $this->get_seo_schema_table_sql($full_table_name, $charset_collate);
466	case 'seo_social':
467	return $this->get_seo_social_table_sql($full_table_name, $charset_collate);
468	case 'seo_performance':
469	return $this->get_seo_performance_table_sql($full_table_name, $charset_collate);
470	case 'seo_local':
471	return $this->get_seo_local_table_sql($full_table_name, $charset_collate);
472
473	// AI/Core Tables
474	case 'ai_cache':
475	return $this->get_ai_cache_table_sql($full_table_name, $charset_collate);
476	case 'ai_usage':
477	return $this->get_ai_usage_table_sql($full_table_name, $charset_collate);
478	case 'content_briefs':
479	return $this->get_content_briefs_table_sql($full_table_name, $charset_collate);
480	case 'seo_scores':
481	return $this->get_seo_scores_table_sql($full_table_name, $charset_collate);
482	case 'instant_indexing_logs':
483	return $this->get_instant_indexing_logs_table_sql($full_table_name, $charset_collate);
484	case 'email_report_logs':
485	return $this->get_email_report_logs_table_sql($full_table_name, $charset_collate);
486
487	default:
488	throw new \InvalidArgumentException('Unknown table: ' . esc_html($table_name));
489	}
490	}
491
492	/**
493	* Get SQL for SEO Settings table
494	*
495	* @since 1.0.0
496	*
497	* @param string $table_name Full table name
498	* @param string $charset_collate Charset and collation
499	* @return string SQL for table creation
500	*/
501	private function get_seo_settings_table_sql(string $table_name, string $charset_collate): string {
502	return "CREATE TABLE `{$table_name}` (
503	setting_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
504	context_type varchar(50) NOT NULL DEFAULT 'site',
505	context_id bigint(20) unsigned NULL,
506	setting_category varchar(100) NOT NULL DEFAULT 'general',
507	setting_key varchar(255) NOT NULL,
508	setting_value longtext NULL,
509	setting_type varchar(50) NOT NULL DEFAULT 'string',
510	is_active tinyint(1) NOT NULL DEFAULT 1,
511	priority int(11) NOT NULL DEFAULT 0,
512	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
513	updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
514	created_by bigint(20) unsigned NULL,
515	updated_by bigint(20) unsigned NULL,
516	PRIMARY KEY (setting_id),
517	UNIQUE KEY unique_setting (context_type, context_id, setting_category, setting_key),
518	KEY idx_context (context_type, context_id),
519	KEY idx_category (setting_category),
520	KEY idx_active (is_active),
521	KEY idx_created (created_at),
522	KEY idx_updated (updated_at),
523	KEY idx_context_cat_active (context_type, context_id, setting_category, is_active)
524	) {$charset_collate};";
525	}
526
527	/**
528	* Get SQL for SEO Analysis table
529	*
530	* @since 1.0.0
531	*
532	* @param string $table_name Full table name
533	* @param string $charset_collate Charset and collation
534	* @return string SQL for table creation
535	*/
536	private function get_seo_analysis_table_sql(string $table_name, string $charset_collate): string {
537	return "CREATE TABLE `{$table_name}` (
538	analysis_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
539	context_type varchar(50) NOT NULL DEFAULT 'site',
540	context_id bigint(20) unsigned NULL,
541	analysis_type varchar(100) NOT NULL,
542	analysis_data longtext NULL,
543	score int(11) NOT NULL DEFAULT 0,
544	status varchar(50) NOT NULL DEFAULT 'pending',
545	ai_confidence decimal(3,2) NULL,
546	recommendations longtext NULL,
547	validation_errors longtext NULL,
548	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
549	updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
550	analyzed_by bigint(20) unsigned NULL,
551	PRIMARY KEY (analysis_id),
552	KEY idx_context (context_type, context_id),
553	KEY idx_type (analysis_type),
554	KEY idx_status (status),
555	KEY idx_score (score),
556	KEY idx_created (created_at),
557	KEY idx_confidence (ai_confidence)
558	) {$charset_collate};";
559	}
560
561	/**
562	* Get SQL for SEO Keywords table
563	*
564	* @since 1.0.0
565	*
566	* @param string $table_name Full table name
567	* @param string $charset_collate Charset and collation
568	* @return string SQL for table creation
569	*/
570	private function get_seo_keywords_table_sql(string $table_name, string $charset_collate): string {
571	return "CREATE TABLE `{$table_name}` (
572	keyword_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
573	context_type varchar(50) NOT NULL DEFAULT 'site',
574	context_id bigint(20) unsigned NULL,
575	keyword_text varchar(500) NOT NULL,
576	keyword_hash varchar(64) NOT NULL,
577	keyword_type varchar(50) NOT NULL DEFAULT 'primary',
578	search_volume int(11) NULL,
579	competition_score decimal(3,2) NULL,
580	difficulty_score decimal(3,2) NULL,
581	density decimal(5,2) NULL,
582	position int(11) NULL,
583	ranking_url varchar(2048) NULL,
584	is_tracking tinyint(1) NOT NULL DEFAULT 0,
585	is_active tinyint(1) NOT NULL DEFAULT 1,
586	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
587	updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
588	tracked_by bigint(20) unsigned NULL,
589	PRIMARY KEY (keyword_id),
590	UNIQUE KEY unique_keyword (context_type, context_id, keyword_hash),
591	KEY idx_context (context_type, context_id),
592	KEY idx_type (keyword_type),
593	KEY idx_hash (keyword_hash),
594	KEY idx_tracking (is_tracking),
595	KEY idx_active (is_active),
596	KEY idx_position (position),
597	KEY idx_created (created_at),
598	FULLTEXT KEY ft_keyword (keyword_text)
599	) {$charset_collate};";
600	}
601
602	/**
603	* Get SQL for SEO Schema table (Optimized Version 2.0)
604	*
605	* @since 1.0.0
606	* @updated 2.0.0 - Optimized structure with fewer columns and better indexes
607	*
608	* @param string $table_name Full table name
609	* @param string $charset_collate Charset and collation
610	* @return string SQL for table creation
611	*/
612	private function get_seo_schema_table_sql(string $table_name, string $charset_collate): string {
613	// Check MySQL version for JSON column support with caching
614	$schema_data_type = $this->get_mysql_json_support() ? 'JSON' : 'longtext';
615
616	return "CREATE TABLE `{$table_name}` (
617	schema_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
618	context_type varchar(50) NOT NULL DEFAULT 'site',
619	context_id bigint(20) unsigned NULL,
620	schema_type varchar(100) NOT NULL,
621	schema_data {$schema_data_type} NOT NULL,
622	validation_status varchar(50) NOT NULL DEFAULT 'pending',
623	is_active tinyint(1) NOT NULL DEFAULT 1,
624	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
625	updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
626	PRIMARY KEY (schema_id),
627	KEY idx_context_active (context_type, context_id, is_active),
628	KEY idx_type_active (schema_type, is_active),
629	KEY idx_created (created_at),
630	KEY idx_context_schema_active (context_type, schema_type, is_active, created_at DESC),
631	KEY idx_validation_active (validation_status, is_active)
632	) {$charset_collate};";
633	}
634
635	/**
636	* Get SQL for SEO Social table
637	*
638	* @since 1.0.0
639	*
640	* @param string $table_name Full table name
641	* @param string $charset_collate Charset and collation
642	* @return string SQL for table creation
643	*/
644	private function get_seo_social_table_sql(string $table_name, string $charset_collate): string {
645	return "CREATE TABLE `{$table_name}` (
646	social_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
647	context_type varchar(50) NOT NULL DEFAULT 'site',
648	context_id bigint(20) unsigned NULL,
649	platform varchar(50) NOT NULL,
650	meta_type varchar(100) NOT NULL,
651	meta_key varchar(255) NOT NULL,
652	meta_value longtext NULL,
653	image_url varchar(2048) NULL,
654	image_width int(11) NULL,
655	image_height int(11) NULL,
656	is_optimized tinyint(1) NOT NULL DEFAULT 0,
657	is_active tinyint(1) NOT NULL DEFAULT 1,
658	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
659	updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
660	created_by bigint(20) unsigned NULL,
661	PRIMARY KEY (social_id),
662	UNIQUE KEY unique_social_meta (context_type, context_id, platform, meta_key),
663	KEY idx_context (context_type, context_id),
664	KEY idx_platform (platform),
665	KEY idx_type (meta_type),
666	KEY idx_optimized (is_optimized),
667	KEY idx_active (is_active),
668	KEY idx_created (created_at)
669	) {$charset_collate};";
670	}
671
672	/**
673	* Get SQL for SEO Performance table
674	*
675	* @since 1.0.0
676	*
677	* @param string $table_name Full table name
678	* @param string $charset_collate Charset and collation
679	* @return string SQL for table creation
680	*/
681	private function get_seo_performance_table_sql(string $table_name, string $charset_collate): string {
682	return "CREATE TABLE `{$table_name}` (
683	performance_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
684	context_type varchar(50) NOT NULL DEFAULT 'site',
685	context_id bigint(20) unsigned NULL,
686	metric_type varchar(100) NOT NULL,
687	metric_value decimal(10,4) NOT NULL,
688	metric_unit varchar(50) NOT NULL DEFAULT 'score',
689	threshold_good decimal(10,4) NULL,
690	threshold_poor decimal(10,4) NULL,
691	status varchar(50) NOT NULL DEFAULT 'unknown',
692	device_type varchar(20) NOT NULL DEFAULT 'desktop',
693	connection_type varchar(50) NULL,
694	measured_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
695	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
696	measured_by varchar(100) NULL,
697	PRIMARY KEY (performance_id),
698	KEY idx_context (context_type, context_id),
699	KEY idx_metric (metric_type),
700	KEY idx_status (status),
701	KEY idx_device (device_type),
702	KEY idx_measured (measured_at),
703	KEY idx_created (created_at),
704	KEY idx_value (metric_value)
705	) {$charset_collate};";
706	}
707
708	/**
709	* Get SQL for SEO Local table
710	*
711	* @since 1.0.0
712	*
713	* @param string $table_name Full table name
714	* @param string $charset_collate Charset and collation
715	* @return string SQL for table creation
716	*/
717	private function get_seo_local_table_sql(string $table_name, string $charset_collate): string {
718	return "CREATE TABLE `{$table_name}` (
719	local_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
720	context_type varchar(50) NOT NULL DEFAULT 'site',
721	context_id bigint(20) unsigned NULL,
722	business_type varchar(100) NOT NULL DEFAULT 'LocalBusiness',
723	business_name varchar(255) NOT NULL,
724	business_address longtext NULL,
725	business_phone varchar(50) NULL,
726	business_email varchar(255) NULL,
727	business_website varchar(2048) NULL,
728	latitude decimal(10,8) NULL,
729	longitude decimal(11,8) NULL,
730	google_place_id varchar(255) NULL,
731	google_my_business_url varchar(2048) NULL,
732	business_hours longtext NULL,
733	nap_consistency_score int(11) NOT NULL DEFAULT 0,
734	local_seo_score int(11) NOT NULL DEFAULT 0,
735	is_verified tinyint(1) NOT NULL DEFAULT 0,
736	is_active tinyint(1) NOT NULL DEFAULT 1,
737	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
738	updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
739	created_by bigint(20) unsigned NULL,
740	PRIMARY KEY (local_id),
741	UNIQUE KEY unique_business (context_type, context_id),
742	KEY idx_context (context_type, context_id),
743	KEY idx_type (business_type),
744	KEY idx_location (latitude, longitude),
745	KEY idx_verified (is_verified),
746	KEY idx_active (is_active),
747	KEY idx_nap_score (nap_consistency_score),
748	KEY idx_local_score (local_seo_score),
749	KEY idx_created (created_at)
750	) {$charset_collate};";
751	}
752
753	/**
754	* Get SQL for AI Cache table
755	*
756	* @since 1.0.0
757	*
758	* @param string $table_name Full table name
759	* @param string $charset_collate Charset and collation
760	* @return string SQL for table creation
761	*/
762	private function get_ai_cache_table_sql(string $table_name, string $charset_collate): string {
763	return "CREATE TABLE `{$table_name}` (
764	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
765	cache_key varchar(255) NOT NULL,
766	cache_data longtext NOT NULL,
767	expires_at bigint(20) unsigned NOT NULL,
768	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
769	PRIMARY KEY (id),
770	UNIQUE KEY cache_key (cache_key),
771	KEY expires_at_idx (expires_at),
772	KEY created_at_idx (created_at)
773	) {$charset_collate};";
774	}
775
776	/**
777	* Get SQL for AI Usage table
778	*
779	* @since 1.0.0
780	*
781	* @param string $table_name Full table name
782	* @param string $charset_collate Charset and collation
783	* @return string SQL for table creation
784	*/
785	private function get_ai_usage_table_sql(string $table_name, string $charset_collate): string {
786	return "CREATE TABLE `{$table_name}` (
787	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
788	user_id bigint(20) unsigned NOT NULL,
789	action varchar(100) NOT NULL,
790	tokens_used int(11) NOT NULL DEFAULT 0,
791	provider varchar(50) NOT NULL,
792	post_id bigint(20) unsigned NULL,
793	metadata longtext NULL,
794	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
795	PRIMARY KEY (id),
796	KEY user_id_idx (user_id),
797	KEY action_idx (action),
798	KEY created_at_idx (created_at),
799	KEY provider_idx (provider)
800	) {$charset_collate};";
801	}
802
803	/**
804	* Get SQL for Content Briefs table
805	*
806	* @since 1.0.0
807	*
808	* @param string $table_name Full table name
809	* @param string $charset_collate Charset and collation
810	* @return string SQL for table creation
811	*/
812	private function get_content_briefs_table_sql(string $table_name, string $charset_collate): string {
813	return "CREATE TABLE `{$table_name}` (
814	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
815	user_id bigint(20) unsigned NOT NULL,
816	title varchar(255) NOT NULL,
817	target_keywords text NOT NULL,
818	content_type varchar(50) NOT NULL DEFAULT 'blog_post',
819	brief_data longtext NOT NULL,
820	parsing_status varchar(50) NOT NULL DEFAULT 'success',
821	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
822	updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
823	PRIMARY KEY (id),
824	KEY user_id_idx (user_id),
825	KEY created_at_idx (created_at),
826	KEY content_type_idx (content_type),
827	KEY parsing_status_idx (parsing_status)
828	) {$charset_collate};";
829	}
830
831	/**
832	* Get SQL for Instant Indexing Logs table
833	*
834	* @since 1.0.0
835	*
836	* @param string $table_name Full table name
837	* @param string $charset_collate Charset and collation
838	* @return string SQL for table creation
839	*/
840	private function get_instant_indexing_logs_table_sql(string $table_name, string $charset_collate): string {
841	return "CREATE TABLE `{$table_name}` (
842	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
843	url varchar(2048) NOT NULL,
844	status varchar(50) NOT NULL,
845	response_code int(11) NULL,
846	response_message text NULL,
847	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
848	PRIMARY KEY (id),
849	KEY idx_status (status),
850	KEY idx_response_code (response_code),
851	KEY idx_created (created_at)
852	) {$charset_collate};";
853	}
854
855	/**
856	* Get SQL for SEO Scores table
857	*
858	* @since 1.0.0
859	*
860	* @param string $table_name Full table name
861	* @param string $charset_collate Charset and collation
862	* @return string SQL for table creation
863	*/
864	private function get_seo_scores_table_sql(string $table_name, string $charset_collate): string {
865	return "CREATE TABLE `{$table_name}` (
866	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
867	post_id bigint(20) unsigned NOT NULL,
868	user_id bigint(20) unsigned NOT NULL,
869	overall_score int(11) NOT NULL,
870	score_breakdown longtext NOT NULL,
871	suggestions longtext NOT NULL,
872	grade varchar(2) NOT NULL,
873	readability_score varchar(100) DEFAULT NULL,
874	content_quality varchar(100) DEFAULT NULL,
875	algorithm_version varchar(20) NOT NULL DEFAULT '2024.1',
876	calculated_at datetime NOT NULL,
877	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
878	PRIMARY KEY (id),
879	KEY post_id_idx (post_id),
880	KEY user_id_idx (user_id),
881	KEY created_at_idx (created_at),
882	KEY overall_score_idx (overall_score)
883	) {$charset_collate};";
884	}
885
886	/**
887	* Get SQL for Email Report Logs table.
888	*
889	* Audit + dedupe log for scheduled SEO email reports. The
890	* `unique_send` constraint on (site_id, period_start, recipient_hash)
891	* is what prevents a given site from being sent the same period twice
892	* to the same recipient — required by the PRD.
893	*
894	* `recipient_hash` is a sha256 of the lowercased, sorted recipient list
895	* (so [a@x, b@x] and [b@x, a@x] dedupe to the same row).
896	*
897	* @since 1.9.0
898	*
899	* @param string $table_name Full table name.
900	* @param string $charset_collate Charset and collation.
901	* @return string SQL for table creation.
902	*/
903	private function get_email_report_logs_table_sql(string $table_name, string $charset_collate): string {
904	return "CREATE TABLE `{$table_name}` (
905	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
906	site_id bigint(20) unsigned NOT NULL DEFAULT 0,
907	period_start datetime NOT NULL,
908	period_end datetime NOT NULL,
909	recipient_hash char(64) NOT NULL,
910	recipient_count smallint(5) unsigned NOT NULL DEFAULT 1,
911	frequency_days smallint(5) unsigned NOT NULL DEFAULT 30,
912	status varchar(20) NOT NULL DEFAULT 'pending',
913	error_message text NULL,
914	sent_at datetime NULL,
915	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
916	PRIMARY KEY (id),
917	UNIQUE KEY unique_send (site_id, period_start, recipient_hash),
918	KEY idx_site (site_id),
919	KEY idx_status (status),
920	KEY idx_sent (sent_at),
921	KEY idx_period (period_start)
922	) {$charset_collate};";
923	}
924
925	/**
926	* Create indexes for a specific table
927	*
928	* @since 1.0.0
929	*
930	* @param string $table_name Table name
931	* @return bool Success status
932	*/
933	private function create_table_indexes(string $table_name): bool {
934	$full_table_name = $this->get_table_name($table_name);
935	$definition = $this->table_definitions[$table_name] ?? [];
936
937	$success = true;
938
939	// Create single column indexes
940	if (!empty($definition['indexes'])) {
941	foreach ($definition['indexes'] as $index_name) {
942	try {
943	// Check if index already exists
944	if ($this->index_exists($full_table_name, $index_name)) {
945	continue;
946	}
947
948	$index_sql = $this->get_index_sql($full_table_name, $index_name);
949	if ($index_sql) {
950	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Index creation requires direct schema changes, DDL cannot be prepared
951	$result = $this->wpdb->query($index_sql);
952	if (false === $result) {
953	$success = false;
954	}
955	}
956	} catch (\Exception $e) {
957	$success = false;
958	}
959	}
960	}
961
962	// Create composite indexes for performance optimization
963	if (!empty($definition['composite_indexes'])) {
964	foreach ($definition['composite_indexes'] as $index_name => $columns) {
965	try {
966	// Check if index already exists
967	if ($this->index_exists($full_table_name, "idx_{$index_name}")) {
968	continue;
969	}
970
971	$index_sql = $this->get_composite_index_sql($full_table_name, $index_name, $columns);
972	if ($index_sql) {
973	// 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
974	$result = $this->wpdb->query($index_sql);
975	if (false === $result) {
976	$success = false;
977	}
978	}
979	} catch (\Exception $e) {
980	$success = false;
981	}
982	}
983	}
984
985	return $success;
986	}
987
988	/**
989	* Add constraints for a specific table
990	*
991	* @since 1.0.0
992	*
993	* @param string $table_name Table name
994	* @return bool Success status
995	*/
996	private function add_table_constraints(string $table_name): bool {
997	$full_table_name = $this->get_table_name($table_name);
998	$definition = $this->table_definitions[$table_name] ?? [];
999
1000	if (empty($definition['foreign_keys'])) {
1001	return true;
1002	}
1003
1004	$success = true;
1005	foreach ($definition['foreign_keys'] as $constraint) {
1006	try {
1007	$constraint_sql = $this->get_constraint_sql($full_table_name, $constraint);
1008	if ($constraint_sql) {
1009	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Constraint creation requires direct schema changes, DDL cannot be prepared
1010	$result = $this->wpdb->query($constraint_sql);
1011	if (false === $result) {
1012	$success = false;
1013	}
1014	}
1015	} catch (\Exception $e) {
1016	$success = false;
1017	}
1018	}
1019
1020	return $success;
1021	}
1022
1023	/**
1024	* Get index SQL for a table
1025	*
1026	* @since 1.0.0
1027	*
1028	* @param string $table_name Full table name
1029	* @param string $index_name Index name
1030	* @return string Index SQL
1031	*/
1032	private function get_index_sql(string $table_name, string $index_name): string {
1033	// Most indexes are already created in the table definition
1034	// This method is for additional indexes if needed
1035	return '';
1036	}
1037
1038	/**
1039	* Get composite index SQL for performance optimization
1040	*
1041	* @since 1.0.0
1042	*
1043	* @param string $table_name Full table name
1044	* @param string $index_name Index name
1045	* @param array $columns Column names for composite index
1046	* @return string Composite index SQL
1047	*/
1048	private function get_composite_index_sql(string $table_name, string $index_name, array $columns): string {
1049	if (empty($columns)) {
1050	return '';
1051	}
1052
1053	// Escape column names
1054	$escaped_columns = array_map(function ($column) {
1055	return "`{$column}`";
1056	}, $columns);
1057
1058	$columns_sql = implode(', ', $escaped_columns);
1059	$index_name_escaped = esc_sql($index_name);
1060
1061	return "CREATE INDEX `idx_{$index_name_escaped}` ON `{$table_name}` ({$columns_sql})";
1062	}
1063
1064	/**
1065	* Get constraint SQL for a table
1066	*
1067	* @since 1.0.0
1068	*
1069	* @param string $table_name Full table name
1070	* @param array $constraint Constraint definition
1071	* @return string Constraint SQL
1072	*/
1073	private function get_constraint_sql(string $table_name, array $constraint): string {
1074	// Foreign key constraints would be defined here
1075	// Currently not implemented as tables are designed to be independent
1076	return '';
1077	}
1078
1079	/**
1080	* Get table information
1081	*
1082	* @since 1.0.0
1083	*
1084	* @param string $table_name Full table name
1085	* @return array Table information
1086	*/
1087	private function get_table_info(string $table_name): array {
1088	$info = [
1089	'exists' => false,
1090	'row_count' => 0,
1091	'data_size' => 0,
1092	'index_size' => 0,
1093	'total_size' => 0,
1094	'created' => null,
1095	'updated' => null
1096	];
1097
1098	if (!$this->table_exists($table_name)) {
1099	return $info;
1100	}
1101
1102	$info['exists'] = true;
1103
1104	// Get row count
1105	// 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
1106	$row_count = $this->wpdb->get_var("SELECT COUNT(*) FROM `{$table_name}`");
1107	$info['row_count'] = (int) $row_count;
1108
1109	// Get table size information
1110	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table size information requires direct database access
1111	$size_info = $this->wpdb->get_row(
1112	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- SQL is properly prepared with placeholders
1113	$this->wpdb->prepare(
1114	"SELECT
1115	data_length as data_size,
1116	index_length as index_size,
1117	(data_length + index_length) as total_size,
1118	create_time as created,
1119	update_time as updated
1120	FROM information_schema.TABLES
1121	WHERE table_schema = %s AND table_name = %s",
1122	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- DB_NAME is a WordPress constant, safe to use
1123	DB_NAME,
1124	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table_name is validated and used as parameter
1125	$table_name
1126	),
1127	ARRAY_A
1128	);
1129
1130	if ($size_info) {
1131	$info['data_size'] = (int) $size_info['data_size'];
1132	$info['index_size'] = (int) $size_info['index_size'];
1133	$info['total_size'] = (int) $size_info['total_size'];
1134	$info['created'] = $size_info['created'];
1135	$info['updated'] = $size_info['updated'];
1136	}
1137
1138	return $info;
1139	}
1140
1141	/**
1142	* Get table size in bytes
1143	*
1144	* @since 1.0.0
1145	*
1146	* @param string $table_name Full table name
1147	* @return int Table size in bytes
1148	*/
1149	private function get_table_size(string $table_name): int {
1150	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table size calculation requires direct database access
1151	$size = $this->wpdb->get_var(
1152	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- SQL is properly prepared with placeholders
1153	$this->wpdb->prepare(
1154	"SELECT (data_length + index_length) as total_size
1155	FROM information_schema.TABLES
1156	WHERE table_schema = %s AND table_name = %s",
1157	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- DB_NAME is a WordPress constant, safe to use
1158	DB_NAME,
1159	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table_name is validated and used as parameter
1160	$table_name
1161	)
1162	);
1163
1164	return (int) $size;
1165	}
1166
1167	/**
1168	* Get tables by category for better organization
1169	*
1170	* @since 1.0.0
1171	*
1172	* @param string $category Category name (seo, ai, content, scoring)
1173	* @return array Table names in the category
1174	*/
1175	public function get_tables_by_category(string $category): array {
1176	return $this->table_categories[$category] ?? [];
1177	}
1178
1179	/**
1180	* Get all table categories
1181	*
1182	* @since 1.0.0
1183	*
1184	* @return array All table categories with their tables
1185	*/
1186	public function get_table_categories(): array {
1187	return $this->table_categories;
1188	}
1189
1190	/**
1191	* Get table count by category
1192	*
1193	* @since 1.0.0
1194	*
1195	* @return array Table counts per category
1196	*/
1197	public function get_table_count_by_category(): array {
1198	$counts = [];
1199	foreach ($this->table_categories as $category => $tables) {
1200	$counts[$category] = count($tables);
1201	}
1202	$counts['total'] = count($this->table_definitions);
1203	return $counts;
1204	}
1205
1206	/**
1207	* Validate table definition structure
1208	*
1209	* @since 1.0.0
1210	*
1211	* @param string $table_name Table name to validate
1212	* @return array Validation results
1213	*/
1214	public function validate_table_definition(string $table_name): array {
1215	$definition = $this->table_definitions[$table_name] ?? null;
1216
1217	if (!$definition) {
1218	return [
1219	'valid' => false,
1220	'errors' => ["Table definition not found: {$table_name}"]
1221	];
1222	}
1223
1224	$errors = [];
1225	$required_keys = ['description', 'primary_key', 'indexes', 'foreign_keys'];
1226
1227	foreach ($required_keys as $key) {
1228	if (!isset($definition[$key])) {
1229	$errors[] = "Missing required key '{$key}' in table definition for {$table_name}";
1230	}
1231	}
1232
1233	return [
1234	'valid' => empty($errors),
1235	'errors' => $errors
1236	];
1237	}
1238
1239	/**
1240	* Add composite indexes to existing tables for performance optimization
1241	*
1242	* @since 1.0.0
1243	*
1244	* @return bool Success status
1245	*/
1246	public function add_performance_indexes(): bool {
1247	$success = true;
1248
1249	foreach ($this->table_definitions as $table_name => $definition) {
1250	if (!empty($definition['composite_indexes'])) {
1251	$full_table_name = $this->get_table_name($table_name);
1252
1253	// Check if table exists before adding indexes
1254	if (!$this->table_exists($full_table_name)) {
1255	continue;
1256	}
1257
1258	foreach ($definition['composite_indexes'] as $index_name => $columns) {
1259	try {
1260	// Check if index already exists
1261	if ($this->index_exists($full_table_name, "idx_{$index_name}")) {
1262	continue;
1263	}
1264
1265	$index_sql = $this->get_composite_index_sql($full_table_name, $index_name, $columns);
1266	if ($index_sql) {
1267	// 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
1268	$result = $this->wpdb->query($index_sql);
1269	if (false === $result) {
1270	$success = false;
1271	// Index creation failed - logged in database operations
1272	}
1273	}
1274	} catch (\Exception $e) {
1275	$success = false;
1276	// Exception during index creation - logged in database operations
1277	}
1278	}
1279	}
1280	}
1281
1282	return $success;
1283	}
1284
1285	/**
1286	* Check if an index exists on a table
1287	*
1288	* @since 1.0.0
1289	*
1290	* @param string $table_name Full table name
1291	* @param string $index_name Index name
1292	* @return bool Whether index exists
1293	*/
1294	private function index_exists(string $table_name, string $index_name): bool {
1295	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Index existence check requires direct database access
1296	$result = $this->wpdb->get_var(
1297	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- SQL is properly prepared with placeholders
1298	$this->wpdb->prepare(
1299	"SELECT COUNT(*) FROM information_schema.statistics
1300	WHERE table_schema = %s AND table_name = %s AND index_name = %s",
1301	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- DB_NAME is a WordPress constant, safe to use
1302	DB_NAME,
1303	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table_name is validated and used as parameter
1304	$table_name,
1305	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $index_name is validated and used as parameter
1306	$index_name
1307	)
1308	);
1309
1310	return (int) $result > 0;
1311	}
1312
1313	/**
1314	* Check if MySQL supports JSON column type with caching
1315	*
1316	* Uses WordPress's built-in database version detection and caches the result
1317	* to avoid repeated database queries during schema creation.
1318	*
1319	* @since 1.0.0
1320	* @return bool True if MySQL 5.7+ supports JSON columns
1321	*/
1322	private function get_mysql_json_support(): bool {
1323	// Check if we have cached result
1324	static $json_support = null;
1325
1326	if ($json_support !== null) {
1327	return $json_support;
1328	}
1329
1330	// Use WordPress's built-in database version method
1331	global $wpdb;
1332
1333	// Get MySQL version using WordPress method (safer than direct query)
1334	$mysql_version = $wpdb->db_version();
1335
1336	// Cache the result for subsequent calls
1337	$json_support = version_compare($mysql_version, '5.7.0', '>=');
1338
1339	return $json_support;
1340	}
1341	}
1342