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

1,245 lines 47.1 KB

Raw Download Zip

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