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.10.0, at includes/database/class-database-schema.php

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