history_table = $wpdb->prefix . 'aipt_bulk_generator_history'; $this->automation_jobs_table = $wpdb->prefix . 'aipt_automation_jobs'; $this->job_logs_table = $wpdb->prefix . 'aipt_job_logs'; $this->job_products_table = $wpdb->prefix . 'aipt_job_products'; $this->draft_content_table = $wpdb->prefix . 'aipt_draft_content'; $this->chatbot_messages_table = $wpdb->prefix . 'aipt_chatbot_messages'; $this->chatbot_jobs_table = $wpdb->prefix . 'aipt_chatbot_jobs'; $this->store_index_profiles_table = $wpdb->prefix . 'aipt_store_index_profiles'; $this->seo_meta_history_table = $wpdb->prefix . 'aipt_seo_meta_history'; $this->prompt_templates_table = $wpdb->prefix . 'aipt_prompt_templates'; $this->rate_limit_counters_table = $wpdb->prefix . 'aipt_rate_limit_counters'; $this->request_claims_table = $wpdb->prefix . 'aipt_request_claims'; $this->content_snapshots_table = $wpdb->prefix . 'aipt_content_snapshots'; $this->content_review_table = $wpdb->prefix . 'aipt_content_review'; $this->blueprints_table = $wpdb->prefix . 'aipt_blueprints'; $this->brand_profiles_table = $wpdb->prefix . 'aipt_brand_profiles'; $this->blueprint_sources_table = $wpdb->prefix . 'aipt_blueprint_sources'; $this->blueprint_source_cache_table = $wpdb->prefix . 'aipt_blueprint_source_cache'; $this->blueprint_assignments_table = $wpdb->prefix . 'aipt_blueprint_assignments'; $this->blueprint_snapshots_table = $wpdb->prefix . 'aipt_blueprint_snapshots'; $this->blueprint_results_table = $wpdb->prefix . 'aipt_blueprint_results'; $this->blueprint_decisions_table = $wpdb->prefix . 'aipt_blueprint_decisions'; $this->catalog_watch_table = $wpdb->prefix . 'aipt_catalog_watch'; $this->mcp_connections_table = $wpdb->prefix . 'aipt_mcp_connections'; $this->mcp_oauth_clients_table = $wpdb->prefix . 'aipt_mcp_oauth_clients'; $this->mcp_oauth_codes_table = $wpdb->prefix . 'aipt_mcp_oauth_codes'; $this->mcp_tokens_table = $wpdb->prefix . 'aipt_mcp_tokens'; $this->mcp_audit_log_table = $wpdb->prefix . 'aipt_mcp_audit_log'; if (is_admin()) { add_action('init', [$this, 'check_tables'], 1); } $this->maybe_bootstrap_schema(); add_action('upgrader_process_complete', [ $this, 'on_upgrader_process_complete' ], 10, 2); } /** * Ensures DB schema is present and up-to-date with minimal overhead. * - Runs very early on every request * - Uses a short transient lock to avoid race conditions * - Throttles full checks with a transient to reduce overhead */ private function maybe_bootstrap_schema(): void { if (self::$tables_checked) { return; } if (defined('WP_UNINSTALL_PLUGIN')) { self::$tables_checked = true; return; } $target_version = self::DB_SCHEMA_VERSION; $current_schema = get_option('aipt_schema_version', '0'); if ($current_schema === $target_version) { $this->verify_required_tables(); $this->maybe_reapply_pending_schema_migrations($current_schema); $this->run_migrations_locked(); self::$tables_checked = true; return; } $lock_token = $this->acquire_migration_lock(); if ($lock_token === null) { return; } try { require_once ABSPATH . 'wp-admin/includes/upgrade.php'; $this->create_plugin_tables(); $this->apply_schema_migrations($current_schema); update_option('aipt_schema_version', $target_version, false); $this->run_migrations(); } catch (\Throwable $e) { set_transient('aipt_schema_migration_error', $e->getMessage(), HOUR_IN_SECONDS); } finally { $this->release_migration_lock($lock_token); } self::$tables_checked = true; } /** * Re-run apply_schema_migrations() even when aipt_schema_version already * equals DB_SCHEMA_VERSION. * * apply_schema_migrations() only runs from the version-DRIFT branch of * maybe_bootstrap_schema()/check_tables() by default. That works for a * genuine version bump, but DDL can also be added to that method body * WITHOUT bumping DB_SCHEMA_VERSION (e.g. several blocks landed here * while 2.7.2 was still unreleased WIP, all sharing the same constant). * A site that already stamped the current version before that DDL was * added would otherwise never execute it. Every block inside * apply_schema_migrations() already guards itself with a permanent-lock * option or an idempotent DROP TABLE IF EXISTS, so simply calling it * again here is safe — this is the general fix, not a one-off patch for * a single missed block. Throttled with its own transient (mirrors * verify_required_tables()'s pattern) so this costs nothing on the * overwhelming majority of requests where nothing is pending. * * @param string $current_schema Current stored schema version (passed through unchanged). * @return void */ private function maybe_reapply_pending_schema_migrations(string $current_schema): void { $throttle_key = 'aipt_reapply_schema_migrations_throttle'; if (get_transient($throttle_key)) { return; } $lock_token = $this->acquire_migration_lock(); if ($lock_token === null) { return; } try { require_once ABSPATH . 'wp-admin/includes/upgrade.php'; $this->apply_schema_migrations($current_schema); } catch (\Throwable $e) { set_transient('aipt_schema_migration_error', $e->getMessage(), HOUR_IN_SECONDS); } finally { $this->release_migration_lock($lock_token); set_transient($throttle_key, true, 5 * MINUTE_IN_SECONDS); } } /** * Runs all migration operations. */ private function run_migrations() { $current_version = get_option('aipt_version', '0'); if (version_compare($current_version, AIPT_VERSION, '<')) { update_option('aipt_previous_version', $current_version, false); $this->cleanup_removed_ui_variant_storage(); self::maybeStampGrandfatherFlag(); self::maybeNormalizeLegacyRecurringCycles(); update_option('aipt_version', AIPT_VERSION, false); } } /** * Run the legacy recurring-cycle normalization once per site. * * Recurring jobs that were preparing, running or paused before cycles * re-armed still store the start of the cycle they are working through; * see AutomationJobManager::clearLegacyInProgressCycleRuns(). A permanent * option guards it: it must never touch jobs whose cycles the re-arm * manages. Called from the version-bump migrations and, as a fallback, * from apply_schema_migrations(). * * @return void */ private static function maybeNormalizeLegacyRecurringCycles(): void { if ( null !== get_option( 'aipt_recurring_legacy_cycles_normalized', null ) ) { return; } try { ( new \AIPT\Api\AutomationJobs\AutomationJobManager() )->clearLegacyInProgressCycleRuns(); } catch ( \Throwable $e ) { return; } update_option( 'aipt_recurring_legacy_cycles_normalized', true, false ); } /** * Run the one-time content structure execution-mode upgrade once per site, * guarded by a permanent option WRITTEN LAST — only after * `ModeUpgrader::run()` itself returns without throwing. A thrown * \Throwable leaves the guard unset so the next schema-migration pass * (this method re-runs on every request via * maybe_reapply_pending_schema_migrations(), see this class' own * docblock) tries again. * * Replaces the retired `Migration\LegacyPromptMigrator` wholesale (Prompt * Builder is retired completely and content structure no longer converts * its settings). * `ModeUpgrader::run()` is itself idempotent and resumable (an already- * decided generator's mode and an already-seeded brand profile are never * touched again), so calling it again here never duplicates anything * created by an earlier pass. Never touches product content, never * starts a job. * * @return void */ private static function maybeRunContentStructureMigration(): void { if ( null !== get_option( 'aipt_content_structure_migration_v1_done', null ) ) { return; } try { \AIPT\Core\ContentStructure\Execution\ModeUpgrader::run(); } catch ( \Throwable $e ) { return; } update_option( 'aipt_content_structure_migration_v1_done', true, false ); } /** * Run the one-time plugin-wide content mode upgrade (2.8.1) once per * site, guarded by a permanent option WRITTEN LAST — only after * `ContentModeUpgrader::run()` itself returns without throwing. Mirrors * {@see self::maybeRunContentStructureMigration()}'s own guard-ordering * contract exactly, for the same reason: a thrown \Throwable must leave * the guard unset so the next schema-migration pass (this method * re-runs on every request via maybe_reapply_pending_schema_migrations(), * see this class' own docblock) tries again. * * Must also run on a fresh install: `ContentModeUpgrader::run()`'s own * per-step guards (an existing `aipt_content_mode` option, existing * `aipt_{short,full}_custom_prompt` options) already make every step a * no-op once `Activator::activate()` has seeded them, so calling this * here too is harmless and keeps one call site responsible for every * site shape (fresh install, 2.7.x, 2.8.0). * * @return void */ private static function maybeRunContentModeMigration(): void { if ( null !== get_option( 'aipt_content_mode_migration_v1_done', null ) ) { return; } try { \AIPT\Core\ContentStructure\Execution\ContentModeUpgrader::run(); } catch ( \Throwable $e ) { return; } update_option( 'aipt_content_mode_migration_v1_done', true, false ); } /** * Run the Store Index free-content-fields back-fill (description + tags, * freed for every plan in the 2026-09 tier decision) once per site. * * `ProfileManager::migrateExistingProfilesToIncludeFreeContentFields()` * owns the eligibility rule and its own permanent option guard; this * wrapper only coordinates WHEN it runs, the same role this class plays * for the other one-time data migrations above. Called from * apply_schema_migrations() rather than an admin_init hook so a site * that auto-updates without an admin ever opening wp-admin still picks * it up, matching this class' own maybe_bootstrap_schema() role for * auto-updates. * * @return void */ private static function maybeBackfillStoreIndexFreeContentFields(): void { try { \AIPT\Api\StoreIndex\ProfileManager::migrateExistingProfilesToIncludeFreeContentFields(); } catch ( \Throwable $e ) { } } /** * Keep the Shop Assistant pre-chat form on for stores that saved settings * before it became opt-in, once per site. `ChatbotSettings` owns the * eligibility rule and its own permanent option guard. * * @return void */ private static function maybeKeepChatbotPreChatForm(): void { try { \AIPT\Api\Chatbot\ChatbotSettings::migrateExistingStoresToKeepPreChatForm(); } catch ( \Throwable $e ) { } } private static function maybeStampGrandfatherFlag(): void { if ( false !== get_option( 'aipt_automation_grandfathered', false ) ) { return; } global $wpdb; $count = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->prefix}aipt_automation_jobs" ); if ( $count >= 1 ) { update_option( 'aipt_automation_grandfathered', true, false ); } } /** * Atomically acquire the shared schema-migration lock. * * Returns a unique owner token on success, or null when another request * already holds the lock (the caller must then run no migration work). * The token must be handed back to release_migration_lock() so a request * only ever releases the lock it actually owns — an unconditional delete * would otherwise clear a lock a concurrent path (schema-drift branch / * check_tables) holds under the same key. * * WordPress exposes no true compare-and-set for transients, so this uses * a set-then-read-back guard: after writing our token we re-read the lock * and only proceed if our token survived, resolving the common * two-request acquire race without requiring a persistent object cache. * * @since 2.7.1 * @return string|null Owner token when acquired, null otherwise. */ private function acquire_migration_lock(): ?string { $lock_key = 'aipt_schema_migration_lock'; if (get_site_transient($lock_key)) { return null; } $token = uniqid('aipt_migration_', true); set_site_transient($lock_key, $token, 60); if (get_site_transient($lock_key) !== $token) { return null; } return $token; } /** * Release the shared schema-migration lock, but only if this request * still owns it. Preserves a lock a concurrent request acquired after * ours expired instead of blindly clearing it. * * @since 2.7.1 * @param string $token Owner token returned by acquire_migration_lock(). * @return void */ private function release_migration_lock(string $token): void { $lock_key = 'aipt_schema_migration_lock'; if (get_site_transient($lock_key) === $token) { delete_site_transient($lock_key); } } /** * Runs the version-bump migrations under the shared schema-migration * lock so cleanup_removed_ui_variant_storage() and the aipt_version * update cannot execute concurrently across requests. * * Only used by the schema-current branch of maybe_bootstrap_schema — the * one migration path that does not already hold the lock. Callers that * already hold it (the schema-drift branch and check_tables) call * run_migrations() directly inside their own lock-held section. * * @since 2.7.1 * @return void */ private function run_migrations_locked(): void { if (!version_compare(get_option('aipt_version', '0'), AIPT_VERSION, '<')) { return; } $token = $this->acquire_migration_lock(); if ($token === null) { return; } try { $this->run_migrations(); } catch (\Throwable $e) { set_transient('aipt_schema_migration_error', $e->getMessage(), HOUR_IN_SECONDS); } finally { $this->release_migration_lock($token); } } private function cleanup_removed_ui_variant_storage(): void { delete_option('aipt_ui_variant'); delete_metadata('user', 0, 'aipt_ui_variant', '', true); delete_option('aipt_ui_variant_metrics'); delete_metadata('user', 0, 'aipt_ui_modal_snoozed_until', '', true); delete_option('aipt_variant_telemetry_last_reported'); delete_transient('aipt_variant_telemetry_failure_backoff'); } /** * Ensures schema immediately after plugin update/install. * * This only triggers once during update/install; no overhead on normal requests. * * @param mixed $upgrader WP_Upgrader instance. * @param array $hook_extra Extra hook arguments. * @return void */ public function on_upgrader_process_complete($upgrader, $hook_extra) { if (empty($hook_extra['type']) || $hook_extra['type'] !== 'plugin') { return; } if (empty($hook_extra['action']) || !in_array($hook_extra['action'], ['update', 'install'], true)) { return; } if (empty($hook_extra['plugins']) || !is_array($hook_extra['plugins'])) { return; } $plugin_file = trailingslashit(AIPT_PLUGIN_DIR) . 'ai-product-tools.php'; $this_plugin = function_exists('plugin_basename') ? plugin_basename($plugin_file) : 'ai-product-tools/ai-product-tools.php'; if (!in_array($this_plugin, $hook_extra['plugins'], true)) { return; } $this->check_tables(); } public function check_tables() { if (self::$tables_checked) { return; } $lock_token = $this->acquire_migration_lock(); if ($lock_token === null) { return; } try { $target_version = self::DB_SCHEMA_VERSION; $current_schema = get_option('aipt_schema_version', '0'); if ($current_schema !== $target_version) { require_once ABSPATH . 'wp-admin/includes/upgrade.php'; $this->create_plugin_tables(); $this->apply_schema_migrations($current_schema); update_option('aipt_schema_version', $target_version, false); } $this->run_migrations(); } catch (\Throwable $e) { set_transient('aipt_schema_migration_error', $e->getMessage(), HOUR_IN_SECONDS); } finally { $this->release_migration_lock($lock_token); } self::$tables_checked = true; } /** * Create required tables during plugin activation * This method is called from Activator.php * * Uses dbDelta() for idempotent table creation and schema updates. */ public function create_activation_tables() { global $wpdb; require_once ABSPATH . 'wp-admin/includes/upgrade.php'; $charset_collate = $wpdb->get_charset_collate(); update_option('aipt_db_version', self::DB_SCHEMA_VERSION, false); $this->create_plugin_tables(); } /** * Create all plugin database tables. * * Creates tables for: * - Automation jobs (jobs, logs, products, draft content) * - Chatbot messages queue * - Rate limit counters (atomic DB-backed rate limiting) * * Uses dbDelta() for idempotent table creation and schema updates. * dbDelta() handles both creation and updates, so no need to check if tables exist. * * Note: dbDelta() requires specific formatting: * - Two spaces before PRIMARY KEY definition * - Each field on its own line * - Use KEY instead of INDEX * - ON UPDATE CURRENT_TIMESTAMP is not supported; timestamps handled in PHP */ private function create_plugin_tables() { global $wpdb; require_once ABSPATH . 'wp-admin/includes/upgrade.php'; $charset_collate = $wpdb->get_charset_collate(); $sql_jobs = "CREATE TABLE {$this->automation_jobs_table} ( id bigint(20) NOT NULL AUTO_INCREMENT, name varchar(255) NOT NULL, description text DEFAULT NULL, tool_type varchar(50) NOT NULL, status varchar(20) NOT NULL DEFAULT 'pending', progress int(3) NOT NULL DEFAULT 0, total_products int(11) NOT NULL DEFAULT 0, processed_products int(11) NOT NULL DEFAULT 0, failed_products int(11) NOT NULL DEFAULT 0, settings longtext NOT NULL, schedule_config longtext NOT NULL, product_filter longtext DEFAULT NULL, next_run datetime DEFAULT NULL, started_at datetime DEFAULT NULL, completed_at datetime DEFAULT NULL, error_message text DEFAULT NULL, created_by bigint(20) NOT NULL, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at datetime DEFAULT NULL, last_progress_logged_at datetime DEFAULT NULL, PRIMARY KEY (id), KEY status (status), KEY tool_type (tool_type), KEY next_run (next_run), KEY created_by (created_by), KEY idx_status_created (status,created_at) ) {$charset_collate};"; $sql_logs = "CREATE TABLE {$this->job_logs_table} ( id bigint(20) NOT NULL AUTO_INCREMENT, job_id bigint(20) NOT NULL, level varchar(20) NOT NULL, message text NOT NULL, context longtext DEFAULT NULL, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY job_id (job_id), KEY level (level), KEY created_at (created_at), KEY idx_job_logs_filtering (job_id,level,created_at) ) {$charset_collate};"; $sql_products = "CREATE TABLE {$this->job_products_table} ( id bigint(20) NOT NULL AUTO_INCREMENT, job_id bigint(20) NOT NULL, product_id bigint(20) NOT NULL, status varchar(20) NOT NULL DEFAULT 'pending', result longtext DEFAULT NULL, error_message text DEFAULT NULL, processed_at datetime DEFAULT NULL, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, processing_token varchar(64) DEFAULT NULL, processing_started_at datetime DEFAULT NULL, PRIMARY KEY (id), KEY job_id (job_id), KEY product_id (product_id), KEY status (status), KEY processing_token (processing_token), KEY idx_job_status_id (job_id,status,id), KEY idx_job_claim_processing (job_id,status,processing_token,id), UNIQUE KEY job_product (job_id,product_id) ) {$charset_collate};"; $sql_chatbot = "CREATE TABLE {$this->chatbot_messages_table} ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, session_id varchar(36) NOT NULL, user_name varchar(100) DEFAULT NULL, user_email varchar(500) DEFAULT NULL, message text NOT NULL, status varchar(20) NOT NULL DEFAULT 'queued', admin_response text DEFAULT NULL, ai_response text DEFAULT NULL, retry_count tinyint(3) unsigned NOT NULL DEFAULT 0, error_message varchar(255) DEFAULT NULL, consent_given tinyint(1) DEFAULT NULL, consent_timestamp bigint(20) unsigned DEFAULT NULL, record_type varchar(20) NOT NULL DEFAULT 'error', created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_session_id (session_id), KEY idx_session_status (session_id,status), KEY idx_status_created (status,created_at), KEY idx_queue_retrieval (status,retry_count,created_at), KEY idx_record_type (record_type), KEY idx_session_record_type (session_id,record_type) ) {$charset_collate};"; $sql_chatbot_jobs = "CREATE TABLE {$this->chatbot_jobs_table} ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, job_uuid varchar(36) NOT NULL, session_id varchar(36) NOT NULL, client_request_id varchar(64) NOT NULL, status_token varchar(64) NOT NULL, status varchar(20) NOT NULL DEFAULT 'pending', payload longtext NOT NULL, response longtext DEFAULT NULL, attempts smallint(5) unsigned NOT NULL DEFAULT 0, claim_token varchar(36) DEFAULT NULL, claimed_at datetime DEFAULT NULL, next_attempt_at datetime DEFAULT NULL, expires_at datetime DEFAULT NULL, error_code varchar(64) DEFAULT NULL, error_detail text DEFAULT NULL, created_at datetime NOT NULL, completed_at datetime DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY job_uuid (job_uuid), UNIQUE KEY uniq_session_request (session_id,client_request_id), KEY idx_status_created (status,created_at), KEY idx_claim_expires (claim_token,expires_at) ) {$charset_collate};"; $sql_store_index_profiles = "CREATE TABLE {$this->store_index_profiles_table} ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, name varchar(50) NOT NULL, is_default tinyint(1) NOT NULL DEFAULT 0, is_active tinyint(1) NOT NULL DEFAULT 0, settings longtext NOT NULL, status longtext NOT NULL, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at datetime DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY name (name), KEY is_default (is_default), KEY is_active (is_active), KEY idx_active_default (is_active,is_default) ) {$charset_collate};"; $sql_prompt_templates = "CREATE TABLE {$this->prompt_templates_table} ( id bigint(20) NOT NULL AUTO_INCREMENT, name varchar(255) NOT NULL, description text DEFAULT NULL, generator_type varchar(50) NOT NULL, prompt_data longtext NOT NULL, wizard_state longtext DEFAULT NULL, is_default tinyint(1) NOT NULL DEFAULT 0, source varchar(20) NOT NULL DEFAULT 'local', community_id varchar(36) DEFAULT NULL, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at datetime DEFAULT NULL, PRIMARY KEY (id), KEY generator_type (generator_type), KEY source (source), KEY idx_generator_default (generator_type,is_default) ) {$charset_collate};"; dbDelta( $sql_jobs ); dbDelta( $sql_logs ); dbDelta( $sql_products ); dbDelta( $sql_chatbot ); dbDelta( $sql_chatbot_jobs ); dbDelta( $sql_store_index_profiles ); dbDelta( $sql_prompt_templates ); $this->create_rate_limit_counters_table(); $this->create_request_claims_table(); $this->create_content_snapshots_table(); $this->create_content_review_table(); $this->create_draft_content_table(); $this->create_blueprints_table(); $this->create_brand_profiles_table(); $this->create_blueprint_sources_table(); $this->create_blueprint_source_cache_table(); $this->create_blueprint_assignments_table(); $this->create_blueprint_snapshots_table(); $this->create_blueprint_results_table(); $this->create_blueprint_decisions_table(); $this->create_catalog_watch_table(); $this->create_mcp_connections_table(); $this->create_mcp_oauth_clients_table(); $this->create_mcp_oauth_codes_table(); $this->create_mcp_tokens_table(); $this->create_mcp_audit_log_table(); $this->ensure_composite_indexes(); $this->ensure_default_store_index_profile(); } /** * Apply schema migrations that are not supported by dbDelta(). * * @param string $current_schema Current stored schema version. * @return void */ private function apply_schema_migrations(string $current_schema): void { if ( null === get_option( 'aipt_gf_stamped', null ) ) { $preexisting = ( '0' !== $current_schema ) || ( '0' !== get_option( 'aipt_version', '0' ) ); add_option( 'aipt_gf_content_review', $preexisting, '', false ); add_option( 'aipt_gf_draft_review', $preexisting || rest_sanitize_boolean( get_option( 'aipt_automation_grandfathered', false ) ), '', false ); add_option( 'aipt_gf_stamped', true, '', false ); } self::maybeNormalizeLegacyRecurringCycles(); global $wpdb; if (version_compare($current_schema, '2.6.0', '<')) { $wpdb->query("DROP TABLE IF EXISTS {$wpdb->prefix}aipt_image_alt_history"); } if ( null === get_option( 'aipt_draft_content_legacy_reset', null ) ) { $wpdb->query("DROP TABLE IF EXISTS {$this->draft_content_table}"); $this->create_draft_content_table(); $this->ensure_composite_indexes(); update_option( 'aipt_draft_content_legacy_reset', true, false ); } $wpdb->query("DROP TABLE IF EXISTS {$this->history_table}"); $wpdb->query("DROP TABLE IF EXISTS {$this->seo_meta_history_table}"); $this->create_content_review_table(); $this->create_content_snapshots_table(); $this->create_draft_content_table(); $this->create_mcp_tokens_table(); $this->create_mcp_oauth_codes_table(); $this->ensure_composite_indexes(); self::maybeRunContentStructureMigration(); self::maybeRunContentModeMigration(); self::maybeBackfillStoreIndexFreeContentFields(); \AIPT\Api\Chatbot\ChatbotSettings::persistLegacyErrorSettingsMigration(); self::maybeKeepChatbotPreChatForm(); } /** * Add composite (multi-column) indexes via direct ALTER TABLE with a * SHOW INDEX precondition. * * dbDelta() has a long-standing bug detecting existing multi-column * composite indexes when re-running CREATE TABLE statements (see * https://core.trac.wordpress.org/ticket/19207), producing "Duplicate * key name" warnings that pollute the error log on every migration tick. * We manage these specific composite indexes outside dbDelta so they * can be added once and stay quiet on subsequent runs. * * Safe to call on every migration: * - SHOW INDEX confirms the key is absent before issuing ALTER. * - $wpdb->suppress_errors() prevents wpdb from printing the harmless * duplicate-key warning if a concurrent request races us between the * SHOW INDEX check and the ALTER statement. * * @since 2.6.0 * @return void */ private function ensure_composite_indexes(): void { global $wpdb; $indexes = [ $this->automation_jobs_table => [ 'idx_status_next_run_created' => '(status, next_run, created_at)', ], $this->job_products_table => [ 'idx_status_processing_started' => '(status, processing_started_at)', ], $this->draft_content_table => [ 'idx_status_type_created' => '(status, content_type, created_at)', 'idx_job_status_created' => '(job_id, status, created_at)', ], $this->chatbot_messages_table => [ 'idx_record_type_created' => '(record_type, created_at)', ], $this->blueprint_sources_table => [ 'idx_scope' => '(scope_type, scope_id)', ], $this->blueprint_assignments_table => [ 'idx_target' => '(target_type, target_id)', ], $this->blueprint_snapshots_table => [ 'idx_kind_generator' => '(kind, generator)', ], $this->blueprint_results_table => [ 'idx_subject' => '(subject_type, subject_id)', ], $this->content_snapshots_table => [ 'idx_subject_lookup' => '(subject_type, subject_id, field_type, created_at)', ], ]; foreach ($indexes as $table => $table_indexes) { foreach ($table_indexes as $index_name => $columns) { $existing = $wpdb->get_var($wpdb->prepare( "SHOW INDEX FROM `{$table}` WHERE Key_name = %s", $index_name )); if (null !== $existing) { continue; } $previous_suppress = $wpdb->suppress_errors(true); $wpdb->query("ALTER TABLE `{$table}` ADD KEY `{$index_name}` {$columns}"); $wpdb->suppress_errors($previous_suppress); } } } /** * Verify that all required tables exist and create any missing ones. * * This handles edge cases where schema version was set but some tables * weren't created (e.g., new table added in same version after initial release). * * @since 2.6.0 * @return void */ private function verify_required_tables(): void { $throttle_key = 'aipt_verify_tables_throttle'; if (get_transient($throttle_key)) { return; } global $wpdb; $required_tables = [ $this->automation_jobs_table, $this->job_logs_table, $this->job_products_table, $this->draft_content_table, $this->chatbot_messages_table, $this->chatbot_jobs_table, $this->store_index_profiles_table, $this->prompt_templates_table, $this->rate_limit_counters_table, $this->request_claims_table, $this->content_snapshots_table, $this->content_review_table, $this->blueprints_table, $this->brand_profiles_table, $this->blueprint_sources_table, $this->blueprint_source_cache_table, $this->blueprint_assignments_table, $this->blueprint_snapshots_table, $this->blueprint_results_table, $this->blueprint_decisions_table, $this->catalog_watch_table, $this->mcp_connections_table, $this->mcp_oauth_clients_table, $this->mcp_oauth_codes_table, $this->mcp_tokens_table, $this->mcp_audit_log_table, ]; $escaped_prefix = $wpdb->esc_like($wpdb->prefix . 'aipt_') . '%'; $existing_tables = $wpdb->get_col($wpdb->prepare("SHOW TABLES LIKE %s", $escaped_prefix)); $missing_tables = array_diff($required_tables, $existing_tables); if (empty($missing_tables)) { set_transient($throttle_key, true, 5 * MINUTE_IN_SECONDS); return; } require_once ABSPATH . 'wp-admin/includes/upgrade.php'; $core_tables = [ $this->automation_jobs_table, $this->job_logs_table, $this->job_products_table, $this->draft_content_table, $this->chatbot_messages_table, $this->chatbot_jobs_table, $this->store_index_profiles_table, $this->prompt_templates_table, ]; $core_tables_missing = array_intersect($missing_tables, $core_tables); if (!empty($core_tables_missing)) { $this->create_plugin_tables(); } else { if (in_array($this->rate_limit_counters_table, $missing_tables, true)) { $this->create_rate_limit_counters_table(); } if (in_array($this->request_claims_table, $missing_tables, true)) { $this->create_request_claims_table(); } if (in_array($this->content_snapshots_table, $missing_tables, true)) { $this->create_content_snapshots_table(); } if (in_array($this->content_review_table, $missing_tables, true)) { $this->create_content_review_table(); } if (in_array($this->blueprints_table, $missing_tables, true)) { $this->create_blueprints_table(); } if (in_array($this->brand_profiles_table, $missing_tables, true)) { $this->create_brand_profiles_table(); } if (in_array($this->blueprint_sources_table, $missing_tables, true)) { $this->create_blueprint_sources_table(); } if (in_array($this->blueprint_source_cache_table, $missing_tables, true)) { $this->create_blueprint_source_cache_table(); } if (in_array($this->blueprint_assignments_table, $missing_tables, true)) { $this->create_blueprint_assignments_table(); } if (in_array($this->blueprint_snapshots_table, $missing_tables, true)) { $this->create_blueprint_snapshots_table(); } if (in_array($this->blueprint_results_table, $missing_tables, true)) { $this->create_blueprint_results_table(); } if (in_array($this->blueprint_decisions_table, $missing_tables, true)) { $this->create_blueprint_decisions_table(); } if (in_array($this->catalog_watch_table, $missing_tables, true)) { $this->create_catalog_watch_table(); } if (in_array($this->mcp_connections_table, $missing_tables, true)) { $this->create_mcp_connections_table(); } if (in_array($this->mcp_oauth_clients_table, $missing_tables, true)) { $this->create_mcp_oauth_clients_table(); } if (in_array($this->mcp_oauth_codes_table, $missing_tables, true)) { $this->create_mcp_oauth_codes_table(); } if (in_array($this->mcp_tokens_table, $missing_tables, true)) { $this->create_mcp_tokens_table(); } if (in_array($this->mcp_audit_log_table, $missing_tables, true)) { $this->create_mcp_audit_log_table(); } } set_transient($throttle_key, true, 5 * MINUTE_IN_SECONDS); } /** * Create Rate Limit Counters table. * * Provides atomic DB-backed rate limiting for the chatbot and other endpoints. * Uses INSERT ... ON DUPLICATE KEY UPDATE for race-free counter increments. * * @since 2.6.0 */ private function create_rate_limit_counters_table(): void { if ( ! function_exists( 'dbDelta' ) ) { require_once ABSPATH . 'wp-admin/includes/upgrade.php'; } global $wpdb; $charset_collate = $wpdb->get_charset_collate(); $sql = "CREATE TABLE {$this->rate_limit_counters_table} ( bucket_key varchar(191) NOT NULL, counter int(10) UNSIGNED NOT NULL DEFAULT 0, window_start bigint(20) UNSIGNED NOT NULL, window_seconds int(10) UNSIGNED NOT NULL, PRIMARY KEY (bucket_key) ) {$charset_collate};"; dbDelta($sql); } /** * Create the Request Claims table. * * Backs an atomic "has anyone already started this exact request" * check for any REST route that accepts a client-supplied idempotency * key: an `INSERT IGNORE` keyed on `claim_key` lets only the first of a * set of concurrent, identically-keyed requests through, with the * caller completing or releasing its claim afterward. * * ALWAYS created (free build too): content structure's dedup (Blueprint * test/regenerate, voice test, voice suggestions) runs on every plan. * * @since 2.8.1 */ private function create_request_claims_table(): void { if ( ! function_exists( 'dbDelta' ) ) { require_once ABSPATH . 'wp-admin/includes/upgrade.php'; } global $wpdb; $charset_collate = $wpdb->get_charset_collate(); $sql = "CREATE TABLE {$this->request_claims_table} ( claim_key varchar(191) NOT NULL, status varchar(10) NOT NULL DEFAULT 'pending', payload longtext DEFAULT NULL, created_at bigint(20) UNSIGNED NOT NULL, PRIMARY KEY (claim_key) ) {$charset_collate};"; dbDelta($sql); } /** * Create the unified content-snapshot store table. * * Backing store for the premium unified rollback/history feature: a slim * indexed table holding the pre-AI value captured immediately before each * apply, keyed by product_id + field_type (+ subject_id for per-attachment * fields like image alt). SEO snapshots additionally carry plugin_slug + * variant_index so a restore writes back through the same SEO-plugin adapter * and variant slot that produced the value. * * ALWAYS created (free build too): the core SnapshotStore writes here, while * the table must survive a license downgrade so no premium-era rows are * orphaned (Premium reuses the free schema — see src/Premium/AGENTS.md). * Pruning is cron-batched and lives in the core SnapshotStore; it never * runs inline during an apply. * * `subject_type` + `taxonomy` (@since 2.8.0) add a term-domain subject * alongside product/attachment rows. A term row carries * subject_type='term', product_id=0, subject_id=, and the * taxonomy slug; every existing product/attachment row keeps the * subject_type default 'product' and an empty taxonomy. See * {@see \AIPT\Core\ContentSnapshots\SnapshotSubject} for the dispatch. * * @since 2.7.2 */ private function create_content_snapshots_table(): void { if ( ! function_exists( 'dbDelta' ) ) { require_once ABSPATH . 'wp-admin/includes/upgrade.php'; } global $wpdb; $charset_collate = $wpdb->get_charset_collate(); $sql = "CREATE TABLE {$this->content_snapshots_table} ( id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, product_id bigint(20) UNSIGNED NOT NULL, field_type varchar(32) NOT NULL, subject_id bigint(20) UNSIGNED DEFAULT NULL, linked_snapshot_id bigint(20) UNSIGNED DEFAULT NULL, snapshot_value longtext NULL, generated_value longtext NULL, value_format varchar(20) NOT NULL DEFAULT 'text', status varchar(20) NOT NULL DEFAULT 'available', is_original tinyint(1) NOT NULL DEFAULT 0, plugin_slug varchar(50) NOT NULL DEFAULT '', variant_index smallint(5) UNSIGNED NOT NULL DEFAULT 0, source varchar(32) NOT NULL DEFAULT '', applied_by bigint(20) UNSIGNED NOT NULL DEFAULT 0, blueprint_id bigint(20) UNSIGNED NOT NULL DEFAULT 0, blueprint_name varchar(190) NOT NULL DEFAULT '', blueprint_revision int(10) UNSIGNED NOT NULL DEFAULT 0, blueprint_builtin tinyint(1) NOT NULL DEFAULT 0, subject_type varchar(12) NOT NULL DEFAULT 'product', taxonomy varchar(32) NOT NULL DEFAULT '', created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY product_field_created (product_id, field_type, created_at), KEY product_field_original (product_id, field_type, subject_id, is_original), KEY prune_window (field_type, is_original, created_at), KEY seo_restore (product_id, field_type, plugin_slug, variant_index), KEY timeline (created_at, id), KEY source_created (source, created_at), KEY actor_created (applied_by, created_at) ) {$charset_collate};"; dbDelta($sql); } /** * Create the content-review staging table. * * Lightweight replacement for the legacy history tables. Stores generated * content that has not yet been applied to the product. The UNIQUE key on * (product_id, tool, field_type, subject_id, variant_index) ensures a * regenerate for the same target is an UPDATE, never an append. * * `subject_id` is NOT NULL DEFAULT 0 — MySQL permits multiple NULLs inside * a UNIQUE index, so a nullable subject_id would silently defeat the whole * upsert guarantee for product-level fields (Description, Title, FAQ, and * scalar SEO rows). Every writer must normalize a missing sub-target to 0. * * `staged_token` (CR-26, CodeRabbit round1) is nullable: it holds the * target's live optimistic-concurrency token at the moment * ContentReviewStore::stage()/restagePending() wrote the row, so * approve()'s conflict guard can compare it against the live token * unconditionally. NULL only for rows staged before this column existed * — see ContentReviewController::reviewConflictGuard() for that fallback. * * ALWAYS created (free build too): the schema must exist on every install. * Only the review ACTIONS are Business-gated. * * @since 2.7.2 */ private function create_content_review_table(): void { if ( ! function_exists( 'dbDelta' ) ) { require_once ABSPATH . 'wp-admin/includes/upgrade.php'; } global $wpdb; $charset_collate = $wpdb->get_charset_collate(); $sql = "CREATE TABLE {$this->content_review_table} ( id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, product_id bigint(20) UNSIGNED NOT NULL, tool varchar(32) NOT NULL, field_type varchar(32) NOT NULL, subject_id bigint(20) UNSIGNED NOT NULL DEFAULT 0, generated_value longtext NULL, value_format varchar(20) NOT NULL DEFAULT 'text', variant_index smallint(5) UNSIGNED NOT NULL DEFAULT 0, plugin_slug varchar(50) NOT NULL DEFAULT '', apply_context text NULL, status varchar(20) NOT NULL DEFAULT 'pending', created_by bigint(20) UNSIGNED NOT NULL DEFAULT 0, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, version bigint(20) UNSIGNED NOT NULL DEFAULT 0, staged_token varchar(64) NULL DEFAULT NULL, blueprint_id bigint(20) UNSIGNED NOT NULL DEFAULT 0, blueprint_name varchar(190) NOT NULL DEFAULT '', blueprint_revision int(10) UNSIGNED NOT NULL DEFAULT 0, blueprint_builtin tinyint(1) NOT NULL DEFAULT 0, PRIMARY KEY (id), UNIQUE KEY review_target (product_id, tool, field_type, subject_id, variant_index), KEY status_created (status, created_at), KEY product_tool (product_id, tool) ) {$charset_collate};"; dbDelta($sql); } /** * Create the aipt_draft_content table. * * Backing store for Automation Jobs' draft-review workflow, now also * surfaced as the Content Bank "Drafts" tab. Extracted into its own * method (rather than inlined in create_plugin_tables() via dbDelta, * as it was before) so the one-time legacy reset in * apply_schema_migrations() can DROP the table and recreate it fresh * with this exact DDL, without duplicating the SQL string. * * `record_kind` is the row-type discriminator. Only 'review' rows * (pending automation approval) are written as of 2.7.2 — the legacy * 'backup' kind (removed OriginalBackupService) is no longer produced, * but the column and the four-column UNIQUE key keep this shape stable. * This is the FINAL shape — a fresh install and a post-reset upgrade * both get it directly; there is no intermediate shape to migrate from. * * ALWAYS created (free build too): the core Automation Jobs pipeline * writes here regardless of plan; only the review ACTIONS are * Business-gated (see DraftContentController). * * @since 2.7.2 */ private function create_draft_content_table(): void { if ( ! function_exists( 'dbDelta' ) ) { require_once ABSPATH . 'wp-admin/includes/upgrade.php'; } global $wpdb; $charset_collate = $wpdb->get_charset_collate(); $sql = "CREATE TABLE {$this->draft_content_table} ( id bigint(20) NOT NULL AUTO_INCREMENT, job_id bigint(20) NOT NULL, product_id bigint(20) NOT NULL, content_type varchar(50) NOT NULL, record_kind varchar(20) NOT NULL DEFAULT 'review', original_content longtext DEFAULT NULL, generated_content longtext NOT NULL, status varchar(20) NOT NULL DEFAULT 'pending', created_by bigint(20) NOT NULL, approved_by bigint(20) DEFAULT NULL, approved_at datetime DEFAULT NULL, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at datetime DEFAULT NULL, blueprint_id bigint(20) unsigned NOT NULL DEFAULT 0, blueprint_name varchar(190) NOT NULL DEFAULT '', blueprint_revision int(10) unsigned NOT NULL DEFAULT 0, blueprint_builtin tinyint(1) NOT NULL DEFAULT 0, PRIMARY KEY (id), KEY job_id (job_id), KEY product_id (product_id), KEY content_type (content_type), KEY status (status), KEY created_by (created_by), UNIQUE KEY job_product_content_kind (job_id,product_id,content_type,record_kind), KEY idx_status_created (status,created_at) ) {$charset_collate};"; dbDelta( $sql ); } /** * Create the aipt_blueprints table. * * Persisted content structure definitions (source of truth; compiled * prompts are a runtime artifact, never stored here). `revision` is a * plain incrementing integer bound by BlueprintStore::update()'s * conditional UPDATE — a different concept from `aipt_blueprint_results`' * optimistic-concurrency `version` column and from a product content * snapshot. `brand_profile_id` 0 means no brand profile selected. * * ALWAYS created (free build too): content structure is a Free feature, * no license gate on this table. * * @since 2.8.0 */ private function create_blueprints_table(): void { if ( ! function_exists( 'dbDelta' ) ) { require_once ABSPATH . 'wp-admin/includes/upgrade.php'; } global $wpdb; $charset_collate = $wpdb->get_charset_collate(); $sql = "CREATE TABLE {$this->blueprints_table} ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, generator varchar(20) NOT NULL, name varchar(190) NOT NULL, status varchar(20) NOT NULL DEFAULT 'active', schema_version smallint(5) unsigned NOT NULL DEFAULT 1, revision int(10) unsigned NOT NULL DEFAULT 1, definition longtext NOT NULL, definition_hash char(64) NOT NULL DEFAULT '', brand_profile_id bigint(20) unsigned NOT NULL DEFAULT 0, origin varchar(60) NOT NULL DEFAULT '', created_by bigint(20) unsigned NOT NULL DEFAULT 0, updated_by bigint(20) unsigned NOT NULL DEFAULT 0, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY generator (generator), KEY brand_profile_id (brand_profile_id), KEY updated_at (updated_at), KEY origin (origin) ) {$charset_collate};"; dbDelta( $sql ); } /** * Create the aipt_brand_profiles table. * * Shared, reusable brand-voice records. `profile` is the approved JSON * (BrandProfileData shape); `draft` holds pending AI-suggested changes * that a user must review before they become part of `profile` — never * merged automatically. * * ALWAYS created (free build too): brand context is Free today and * must not become paid by introducing a gate here. * * @since 2.8.0 */ private function create_brand_profiles_table(): void { if ( ! function_exists( 'dbDelta' ) ) { require_once ABSPATH . 'wp-admin/includes/upgrade.php'; } global $wpdb; $charset_collate = $wpdb->get_charset_collate(); $sql = "CREATE TABLE {$this->brand_profiles_table} ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, name varchar(190) NOT NULL, revision int(10) unsigned NOT NULL DEFAULT 1, profile longtext NOT NULL, draft longtext DEFAULT NULL, origin varchar(60) NOT NULL DEFAULT '', created_by bigint(20) unsigned NOT NULL DEFAULT 0, updated_by bigint(20) unsigned NOT NULL DEFAULT 0, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY name (name) ) {$charset_collate};"; dbDelta( $sql ); } /** * Create the aipt_blueprint_sources table. * * Shared, scope-bound external-source records (store/category/blueprint/ * product). `kind='url'` holds a literal URL; `kind='product_url_field'` * reads the URL from a product field per product and caches content in * aipt_blueprint_source_cache instead of here. `(scope_type, scope_id)` * is a multi-column KEY added via ensure_composite_indexes() — see that * method's docblock for why composite KEYs are kept out of the dbDelta * string here. * * ALWAYS created (free build too): Sources is part of the Free content * structure feature set. * * @since 2.8.0 */ private function create_blueprint_sources_table(): void { if ( ! function_exists( 'dbDelta' ) ) { require_once ABSPATH . 'wp-admin/includes/upgrade.php'; } global $wpdb; $charset_collate = $wpdb->get_charset_collate(); $sql = "CREATE TABLE {$this->blueprint_sources_table} ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, name varchar(190) NOT NULL, kind varchar(20) NOT NULL, url text, url_field varchar(190) NOT NULL DEFAULT '', scope_type varchar(20) NOT NULL, scope_id bigint(20) unsigned NOT NULL DEFAULT 0, enabled tinyint(1) NOT NULL DEFAULT 1, status varchar(30) NOT NULL DEFAULT 'pending', content_type varchar(100) NOT NULL DEFAULT '', extracted longtext DEFAULT NULL, content_hash char(64) NOT NULL DEFAULT '', content_version int(10) unsigned NOT NULL DEFAULT 0, http_status smallint(5) unsigned NOT NULL DEFAULT 0, error_code varchar(60) NOT NULL DEFAULT '', error_message varchar(255) NOT NULL DEFAULT '', final_url text, bytes int(10) unsigned NOT NULL DEFAULT 0, last_checked_at datetime DEFAULT NULL, stale_after_days smallint(5) unsigned NOT NULL DEFAULT 7, version bigint(20) unsigned NOT NULL DEFAULT 0, created_by bigint(20) unsigned NOT NULL DEFAULT 0, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY status (status) ) {$charset_collate};"; dbDelta( $sql ); } /** * Create the aipt_blueprint_source_cache table. * * Per-product cached content for `product_url_field` sources. The * UNIQUE key is (source_id, product_id) — never source_id alone — so * one product's fetched page content can never serve another product * (an explicit per-product isolation requirement). Both columns are * plain NOT NULL (no sentinel default needed: a cache row always has a * real source and a real product). * * ALWAYS created (free build too). * * @since 2.8.0 */ private function create_blueprint_source_cache_table(): void { if ( ! function_exists( 'dbDelta' ) ) { require_once ABSPATH . 'wp-admin/includes/upgrade.php'; } global $wpdb; $charset_collate = $wpdb->get_charset_collate(); $sql = "CREATE TABLE {$this->blueprint_source_cache_table} ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, source_id bigint(20) unsigned NOT NULL, product_id bigint(20) unsigned NOT NULL, url text, url_hash char(64) NOT NULL DEFAULT '', status varchar(30) NOT NULL DEFAULT 'pending', extracted longtext DEFAULT NULL, content_hash char(64) NOT NULL DEFAULT '', content_version int(10) unsigned NOT NULL DEFAULT 0, http_status smallint(5) unsigned NOT NULL DEFAULT 0, error_code varchar(60) NOT NULL DEFAULT '', fetched_at datetime DEFAULT NULL, expires_at datetime DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY source_product (source_id,product_id), KEY product_id (product_id), KEY expires_at (expires_at) ) {$charset_collate};"; dbDelta( $sql ); } /** * Create the aipt_blueprint_assignments table. * * One row per (generator, target) binding of a Blueprint to the store * default, a category, a product or a term. The UNIQUE key makes a * store default unique per generator and allows only one assignment per * category/product/term per generator — a second POST for the same * target is an update, never a duplicate row. `taxonomy` and `target_id` * are NOT NULL with sentinel defaults ('' / 0), never nullable: MySQL * allows multiple NULLs inside a UNIQUE index, which would silently * defeat this exact uniqueness guarantee (mirrors aipt_content_review's * subject_id convention). `(target_type, target_id)` is a multi-column * KEY added via ensure_composite_indexes(). * * ALWAYS created (free build too): assignment is part of the Free * content structure feature set (no new paid tier). * * @since 2.8.0 */ private function create_blueprint_assignments_table(): void { if ( ! function_exists( 'dbDelta' ) ) { require_once ABSPATH . 'wp-admin/includes/upgrade.php'; } global $wpdb; $charset_collate = $wpdb->get_charset_collate(); $sql = "CREATE TABLE {$this->blueprint_assignments_table} ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, generator varchar(20) NOT NULL, blueprint_id bigint(20) unsigned NOT NULL, target_type varchar(20) NOT NULL, taxonomy varchar(32) NOT NULL DEFAULT '', target_id bigint(20) unsigned NOT NULL DEFAULT 0, include_children tinyint(1) NOT NULL DEFAULT 0, priority smallint(5) unsigned NOT NULL DEFAULT 10, version bigint(20) unsigned NOT NULL DEFAULT 0, created_by bigint(20) unsigned NOT NULL DEFAULT 0, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY generator_target (generator,target_type,taxonomy,target_id), KEY blueprint_id (blueprint_id) ) {$charset_collate};"; dbDelta( $sql ); } /** * Create the aipt_blueprint_snapshots table. * * Immutable execution snapshots: `kind='definition'` self-contained * compile-time pins (blueprint + brand profile copy + source config), * `kind='run'` frozen assignment rows for one generator's run. The * UNIQUE `hash` key is what ExecutionSnapshotStore::findOrCreate() races * safely against (content-addressed: same canonical payload -> same * row, never duplicated). `(kind, generator)` is a multi-column KEY * added via ensure_composite_indexes(). * * ALWAYS created (free build too): snapshot integrity backs every * generator's run, free or paid. * * @since 2.8.0 */ private function create_blueprint_snapshots_table(): void { if ( ! function_exists( 'dbDelta' ) ) { require_once ABSPATH . 'wp-admin/includes/upgrade.php'; } global $wpdb; $charset_collate = $wpdb->get_charset_collate(); $sql = "CREATE TABLE {$this->blueprint_snapshots_table} ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, kind varchar(12) NOT NULL, hash char(64) NOT NULL, generator varchar(20) NOT NULL, blueprint_id bigint(20) unsigned NOT NULL DEFAULT 0, blueprint_revision int(10) unsigned NOT NULL DEFAULT 0, brand_profile_id bigint(20) unsigned NOT NULL DEFAULT 0, brand_revision int(10) unsigned NOT NULL DEFAULT 0, payload longtext NOT NULL, created_by bigint(20) unsigned NOT NULL DEFAULT 0, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, last_used_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY hash (hash), KEY last_used_at (last_used_at), KEY blueprint_id (blueprint_id) ) {$charset_collate};"; dbDelta( $sql ); } /** * Create the aipt_blueprint_results table. * * Preview/test results and single-apply provenance. `result_key` is a * random 32-hex lookup token (never the auto-increment id) handed to the * client; the UNIQUE key is what ResultStore::create() relies on never * colliding in practice (random_bytes(16) keyspace) while still * detecting the astronomically unlikely collision rather than silently * overwriting another subject's result. `snapshot_id` 0 means an * unsaved draft was used. `(subject_type, subject_id)` is a * multi-column KEY added via ensure_composite_indexes(). * * ALWAYS created (free build too). * * @since 2.8.0 */ private function create_blueprint_results_table(): void { if ( ! function_exists( 'dbDelta' ) ) { require_once ABSPATH . 'wp-admin/includes/upgrade.php'; } global $wpdb; $charset_collate = $wpdb->get_charset_collate(); $sql = "CREATE TABLE {$this->blueprint_results_table} ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, result_key char(32) NOT NULL, generator varchar(20) NOT NULL, subject_type varchar(12) NOT NULL, subject_id bigint(20) unsigned NOT NULL, product_id bigint(20) unsigned NOT NULL DEFAULT 0, origin varchar(16) NOT NULL DEFAULT 'test', blueprint_id bigint(20) unsigned NOT NULL DEFAULT 0, snapshot_id bigint(20) unsigned NOT NULL DEFAULT 0, definition_hash char(64) NOT NULL DEFAULT '', context_hash char(64) NOT NULL DEFAULT '', status varchar(20) NOT NULL DEFAULT 'ok', eligible tinyint(1) NOT NULL DEFAULT 0, payload longtext NOT NULL, created_by bigint(20) unsigned NOT NULL DEFAULT 0, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, expires_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, applied_at datetime DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY result_key (result_key), KEY expires_at (expires_at), KEY product_id (product_id), KEY created_by (created_by) ) {$charset_collate};"; dbDelta( $sql ); } /** * Create the aipt_blueprint_decisions table. * * Source-conflict acceptance decisions, bound to the exact source * content versions the user saw. The UNIQUE key includes * `versions_hash`: when any competing source's content_version (or the * product's own modified token, folded into that hash by the caller) * changes, a NEW row is written rather than updating the old one — the * stale decision simply stops matching and the conflict is * re-evaluated, never silently reused for different underlying content. * * ALWAYS created (free build too). * * @since 2.8.0 */ private function create_blueprint_decisions_table(): void { if ( ! function_exists( 'dbDelta' ) ) { require_once ABSPATH . 'wp-admin/includes/upgrade.php'; } global $wpdb; $charset_collate = $wpdb->get_charset_collate(); $sql = "CREATE TABLE {$this->blueprint_decisions_table} ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, subject_type varchar(12) NOT NULL, subject_id bigint(20) unsigned NOT NULL, field_key varchar(190) NOT NULL, chosen_ref varchar(40) NOT NULL, chosen_value_hash char(64) NOT NULL DEFAULT '', versions_hash char(64) NOT NULL DEFAULT '', decided_by bigint(20) unsigned NOT NULL DEFAULT 0, decided_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY subject_field_versions (subject_type,subject_id,field_key,versions_hash) ) {$charset_collate};"; dbDelta( $sql ); } /** * Create the aipt_catalog_watch table. * * Catalog watch's result per published product and content type, from * the AI-free structure check run in the background * (Premium's CatalogWatchStore). `reasons` * holds the check's own warning codes, comma-separated; `blueprint_id` * 0 means the built-in structure. Readers join wp_posts, so a product * that stops being published never counts; a full sweep deletes the * rows it did not touch. * * ALWAYS created (free build too); only a Business sweep writes it. * * @since 2.8.0 */ private function create_catalog_watch_table(): void { if ( ! function_exists( 'dbDelta' ) ) { require_once ABSPATH . 'wp-admin/includes/upgrade.php'; } global $wpdb; $charset_collate = $wpdb->get_charset_collate(); $sql = "CREATE TABLE {$this->catalog_watch_table} ( product_id bigint(20) unsigned NOT NULL, generator varchar(20) NOT NULL, status varchar(20) NOT NULL, reasons varchar(255) NOT NULL DEFAULT '', blueprint_id bigint(20) unsigned NOT NULL DEFAULT 0, blueprint_revision int(10) unsigned NOT NULL DEFAULT 0, checked_at datetime NOT NULL, PRIMARY KEY (product_id,generator), KEY status_generator (status,generator), KEY blueprint_status (blueprint_id,status), KEY checked_at (checked_at) ) {$charset_collate};"; dbDelta( $sql ); } /** * Create the MCP connections table (2.8.2). * * One row per OAuth grant or API key issued to an agent. `status` * tracks the connection's lifecycle (active/revoked/disabled); * `revoked_reason`/`disabled_reason` record why it left `active`. * `custom_tool_overrides` is Business-only JSON — the free build always * clamps it to null on read (see src/Core/Mcp/AGENTS.md). * * @since 2.8.2 */ private function create_mcp_connections_table(): void { if ( ! function_exists( 'dbDelta' ) ) { require_once ABSPATH . 'wp-admin/includes/upgrade.php'; } global $wpdb; $charset_collate = $wpdb->get_charset_collate(); $sql = "CREATE TABLE {$this->mcp_connections_table} ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, auth_type varchar(20) NOT NULL, user_id bigint(20) unsigned NOT NULL, oauth_client_id bigint(20) unsigned NOT NULL DEFAULT 0, label varchar(190) NOT NULL DEFAULT '', permission_level varchar(20) NOT NULL DEFAULT 'content_editor', custom_tool_overrides longtext DEFAULT NULL, status varchar(20) NOT NULL DEFAULT 'active', revoked_reason varchar(40) NOT NULL DEFAULT '', disabled_reason varchar(40) NOT NULL DEFAULT '', created_by_ip_hash varchar(64) NOT NULL DEFAULT '', created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, last_used_at datetime DEFAULT NULL, revoked_at datetime DEFAULT NULL, revoked_by bigint(20) unsigned NOT NULL DEFAULT 0, PRIMARY KEY (id), KEY user_id (user_id), KEY status_created (status,created_at), KEY oauth_client_id (oauth_client_id) ) {$charset_collate};"; dbDelta( $sql ); } /** * Create the MCP OAuth clients table (2.8.2). * * Holds both Dynamic Client Registration and Client ID Metadata * Document clients. `client_id` is stored raw (a CIMD client's id is * its own https URL, which can exceed a safely-indexable column * width), so every lookup goes through the indexed `client_id_hash`. * * @since 2.8.2 */ private function create_mcp_oauth_clients_table(): void { if ( ! function_exists( 'dbDelta' ) ) { require_once ABSPATH . 'wp-admin/includes/upgrade.php'; } global $wpdb; $charset_collate = $wpdb->get_charset_collate(); $sql = "CREATE TABLE {$this->mcp_oauth_clients_table} ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, client_id text NOT NULL, client_id_hash char(64) NOT NULL, registration_mode varchar(20) NOT NULL DEFAULT 'dcr', client_name varchar(190) NOT NULL DEFAULT '', redirect_uris longtext NOT NULL, grant_types longtext NOT NULL, token_endpoint_auth_method varchar(30) NOT NULL DEFAULT 'none', software_id varchar(190) NOT NULL DEFAULT '', software_version varchar(60) NOT NULL DEFAULT '', raw_metadata longtext DEFAULT NULL, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, last_used_at datetime DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY client_id_hash (client_id_hash), KEY registration_mode_created (registration_mode,created_at) ) {$charset_collate};"; dbDelta( $sql ); } /** * Create the MCP OAuth authorization codes table (2.8.2). * * One row per issued authorization code. `code_hash` is the primary * key, so the atomic single-use redemption the OAuth flow performs on * this row (`UPDATE ... SET used_at = %s WHERE code_hash = %s AND * used_at IS NULL AND expires_at > %s`) can never affect more than one * row. * * @since 2.8.2 */ private function create_mcp_oauth_codes_table(): void { if ( ! function_exists( 'dbDelta' ) ) { require_once ABSPATH . 'wp-admin/includes/upgrade.php'; } global $wpdb; $charset_collate = $wpdb->get_charset_collate(); $sql = "CREATE TABLE {$this->mcp_oauth_codes_table} ( code_hash char(64) NOT NULL, client_id_hash char(64) NOT NULL, user_id bigint(20) unsigned NOT NULL, redirect_uri text NOT NULL, code_challenge varchar(128) NOT NULL, code_challenge_method varchar(10) NOT NULL DEFAULT 'S256', permission_level varchar(20) NOT NULL DEFAULT 'content_editor', resource varchar(500) NOT NULL DEFAULT '', expires_at datetime NOT NULL, used_at datetime DEFAULT NULL, connection_id bigint(20) unsigned DEFAULT NULL, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (code_hash), KEY expires_at (expires_at), KEY client_id_hash (client_id_hash) ) {$charset_collate};"; dbDelta( $sql ); } /** * Create the MCP tokens table (2.8.2). * * Covers all three credential types (`api_key`, `oauth_access`, * `oauth_refresh`) in one table, distinguished by `token_type`. * `revoke_reason` ('rotated'|'revoked'|'connection_revoked') lets the * credential cleaner and the refresh-reuse detector apply different * retention rules per reason: a 'rotated' row must * survive past its own `expires_at` so reuse of an already-rotated * refresh token can still be detected. * * `grace_reused_at` bounds the refresh-reuse grace window to exactly one * compensating reissue per rotated token: it is set the first time a * just-rotated token is presented again within the grace window, and any * further presentation (inside or outside the window) is then treated as * genuine reuse rather than minting another independent token pair. * * @since 2.8.2 */ private function create_mcp_tokens_table(): void { if ( ! function_exists( 'dbDelta' ) ) { require_once ABSPATH . 'wp-admin/includes/upgrade.php'; } global $wpdb; $charset_collate = $wpdb->get_charset_collate(); $sql = "CREATE TABLE {$this->mcp_tokens_table} ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, connection_id bigint(20) unsigned NOT NULL, token_hash char(64) NOT NULL, token_type varchar(20) NOT NULL, paired_token_id bigint(20) unsigned NOT NULL DEFAULT 0, permission_level varchar(20) NOT NULL DEFAULT '', expires_at datetime DEFAULT NULL, last_used_at datetime DEFAULT NULL, revoked_at datetime DEFAULT NULL, revoke_reason varchar(20) NOT NULL DEFAULT '', grace_reused_at datetime DEFAULT NULL, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY token_hash (token_hash), KEY connection_type (connection_id,token_type), KEY expires_at (expires_at), KEY paired_token_id (paired_token_id) ) {$charset_collate};"; dbDelta( $sql ); } /** * Create the MCP audit log table (2.8.2). * * Append-only. `request_summary` is redacted and size-capped by the * writer ({@see \AIPT\Core\Mcp\Database\AuditLogStore::write()}) before * it ever reaches this column — never a raw secret or raw generated * text. * * @since 2.8.2 */ private function create_mcp_audit_log_table(): void { if ( ! function_exists( 'dbDelta' ) ) { require_once ABSPATH . 'wp-admin/includes/upgrade.php'; } global $wpdb; $charset_collate = $wpdb->get_charset_collate(); $sql = "CREATE TABLE {$this->mcp_audit_log_table} ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, connection_id bigint(20) unsigned NOT NULL DEFAULT 0, user_id bigint(20) unsigned NOT NULL DEFAULT 0, event_type varchar(32) NOT NULL, tool_name varchar(80) NOT NULL DEFAULT '', outcome varchar(20) NOT NULL DEFAULT '', status_code varchar(40) NOT NULL DEFAULT '', request_summary longtext DEFAULT NULL, subject_type varchar(20) NOT NULL DEFAULT '', subject_id bigint(20) unsigned NOT NULL DEFAULT 0, ip_hash varchar(64) NOT NULL DEFAULT '', user_agent varchar(255) NOT NULL DEFAULT '', duration_ms int(10) unsigned NOT NULL DEFAULT 0, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY connection_created (connection_id,created_at), KEY user_created (user_id,created_at), KEY event_type_created (event_type,created_at), KEY tool_name_created (tool_name,created_at), KEY created_at (created_at) ) {$charset_collate};"; dbDelta( $sql ); } /** * Ensure default Store Index profile exists. * * Creates a default profile with default settings if none exists. * Handles concurrent writers gracefully by catching duplicate key errors. * * @since 2.5.0 */ private function ensure_default_store_index_profile(): void { global $wpdb; $sql = "SELECT id FROM {$this->store_index_profiles_table} WHERE is_default = %d"; $default_exists = $wpdb->get_var($wpdb->prepare($sql, 1)); if ( $default_exists ) { return; } $settings = array( 'enabled' => false, 'max_products' => 10000, 'batch_size' => 100, 'categories' => array(), 'tags' => array(), 'stock_filter' => 'all', 'date_filter_days' => 0, 'filter_mode' => 'all', 'scheduled_enabled' => false, 'schedule_frequency' => 'daily', 'included_fields' => \AIPT\Api\StoreIndex\ProfileManager::getDefaultIncludedFields(), ); $status = array( 'status' => 'idle', 'last_build' => null, 'product_count' => 0, 'category_count' => 0, 'file_size' => 0, 'trimmed_count' => 0, 'shard_count' => 0, 'error' => null, 'build_progress' => 0, 'progress_percent' => 0, 'processed_product_count' => 0, 'estimated_total_products' => 0, 'build_job_id' => 0, ); $suppress = $wpdb->suppress_errors( true ); $result = $wpdb->insert( $this->store_index_profiles_table, array( 'name' => 'Default', 'is_default' => 1, 'is_active' => 1, 'settings' => wp_json_encode( $settings ), 'status' => wp_json_encode( $status ), 'created_at' => current_time( 'mysql' ), ), array( '%s', '%d', '%d', '%s', '%s', '%s' ) ); $wpdb->suppress_errors( $suppress ); if ( false === $result ) { $last_error = $wpdb->last_error; if ( strpos( $last_error, 'Duplicate entry' ) !== false || strpos( $last_error, '1062' ) !== false ) { return; } error_log( sprintf( '[AIPT] Migration: Failed to create default Store Index profile: %s', $last_error ?: 'Unknown database error' ) ); } } /** * Drop all plugin tables. * * Should only be called during plugin uninstallation. * Validates the calling context to ensure this is a legitimate uninstall: * * - WP-CLI context: Trusted privileged environment * - WP_UNINSTALL_PLUGIN: Standard WordPress uninstall mechanism * - Admin with manage_options: Web-based uninstall with proper capability * * @throws \RuntimeException If called from untrusted context. * @return void */ public function drop_tables(): void { $is_cli = defined('WP_CLI') && WP_CLI; $is_wp_uninstall = defined('WP_UNINSTALL_PLUGIN'); $has_capability = current_user_can('manage_options'); if (!$is_cli && !$is_wp_uninstall && !$has_capability) { throw new \RuntimeException('Insufficient permissions to drop tables.'); } global $wpdb; try { $wpdb->query("DROP TABLE IF EXISTS {$this->history_table}"); $wpdb->query("DROP TABLE IF EXISTS {$this->draft_content_table}"); $wpdb->query("DROP TABLE IF EXISTS {$this->job_products_table}"); $wpdb->query("DROP TABLE IF EXISTS {$this->job_logs_table}"); $wpdb->query("DROP TABLE IF EXISTS {$this->automation_jobs_table}"); $wpdb->query("DROP TABLE IF EXISTS {$this->chatbot_messages_table}"); $wpdb->query("DROP TABLE IF EXISTS {$this->chatbot_jobs_table}"); $wpdb->query("DROP TABLE IF EXISTS {$this->store_index_profiles_table}"); $wpdb->query("DROP TABLE IF EXISTS {$this->seo_meta_history_table}"); $wpdb->query("DROP TABLE IF EXISTS {$this->prompt_templates_table}"); $wpdb->query("DROP TABLE IF EXISTS {$this->rate_limit_counters_table}"); $wpdb->query("DROP TABLE IF EXISTS {$this->request_claims_table}"); $wpdb->query("DROP TABLE IF EXISTS {$this->content_snapshots_table}"); $wpdb->query("DROP TABLE IF EXISTS {$this->content_review_table}"); $wpdb->query("DROP TABLE IF EXISTS {$this->blueprint_decisions_table}"); $wpdb->query("DROP TABLE IF EXISTS {$this->catalog_watch_table}"); $wpdb->query("DROP TABLE IF EXISTS {$this->blueprint_results_table}"); $wpdb->query("DROP TABLE IF EXISTS {$this->blueprint_snapshots_table}"); $wpdb->query("DROP TABLE IF EXISTS {$this->blueprint_assignments_table}"); $wpdb->query("DROP TABLE IF EXISTS {$this->blueprint_source_cache_table}"); $wpdb->query("DROP TABLE IF EXISTS {$this->blueprint_sources_table}"); $wpdb->query("DROP TABLE IF EXISTS {$this->brand_profiles_table}"); $wpdb->query("DROP TABLE IF EXISTS {$this->blueprints_table}"); $wpdb->query("DROP TABLE IF EXISTS {$this->mcp_audit_log_table}"); $wpdb->query("DROP TABLE IF EXISTS {$this->mcp_tokens_table}"); $wpdb->query("DROP TABLE IF EXISTS {$this->mcp_oauth_codes_table}"); $wpdb->query("DROP TABLE IF EXISTS {$this->mcp_oauth_clients_table}"); $wpdb->query("DROP TABLE IF EXISTS {$this->mcp_connections_table}"); delete_option('aipt_db_version'); delete_option('aipt_chatbot_settings'); delete_option('aipt_chatbot_appearance_presets'); } catch (\Exception $e) { throw $e; } } }