<?php
/**
 * Database schema management.
 *
 * @package SubmissionInbox
 */

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Activation/uninstall routines intentionally manage this plugin's custom database tables.
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names come from plugin-owned $wpdb->prefix values returned by get_table_names().

/**
 * Creates and removes plugin tables.
 */
class SUBIN_Database {
	const OPTION_KEY        = 'subin_db_version';
	const LOCK_OPTION       = 'subin_db_migration_lock';
	const LAST_ERROR_OPTION = 'subin_db_migration_error';
	const NOTE_ACTIVITY_REDACTION_CURSOR_OPTION = 'subin_note_activity_redaction_cursor';
	const MAIL_EVENT_AUDIT_CURSOR_OPTION        = 'subin_mail_event_audit_cursor';
	const MAIL_EVENT_AUDIT_COMPLETE_OPTION      = 'subin_mail_event_audit_complete';
	const MAIL_EVENT_AUDIT_HOOK                 = 'subin_backfill_mail_event_audit';
	const MAIL_EVENT_AUDIT_BATCH_SIZE           = 250;
	const LEGACY_NOTE_DELETE_CURSOR_OPTION      = 'subin_legacy_note_delete_cursor';
	const LEGACY_NOTE_DELETE_COMPLETE_OPTION    = 'subin_legacy_note_delete_complete';
	const NOTE_ACTIVITY_REDACTION_HOOK          = 'subin_redact_legacy_note_activity';
	const NOTE_ACTIVITY_REDACTION_BATCH_SIZE    = 250;
	const VERSION           = '1.22.0';
	const LOCK_TTL          = 900;

	/**
	 * Get plugin table names.
	 *
	 * @return array<string,string>
	 */
	public static function get_table_names() {
		global $wpdb;

		return array(
			'forms'        => $wpdb->prefix . 'subin_forms',
			'form_fields'  => $wpdb->prefix . 'subin_form_fields',
			'form_analytics' => $wpdb->prefix . 'subin_form_analytics_events',
			'form_folders' => $wpdb->prefix . 'subin_form_folders',
			'form_folder_map' => $wpdb->prefix . 'subin_form_folder_map',
			'form_tags'    => $wpdb->prefix . 'subin_form_tags',
			'form_tag_map' => $wpdb->prefix . 'subin_form_tag_map',
			'leads'        => $wpdb->prefix . 'subin_leads',
			'lead_fields'  => $wpdb->prefix . 'subin_lead_fields',
			'mail_events'  => $wpdb->prefix . 'subin_mail_events',
			'auto_reply_templates' => $wpdb->prefix . 'subin_auto_reply_templates',
			'auto_reply_rules' => $wpdb->prefix . 'subin_auto_reply_rules',
			'lead_notes'   => $wpdb->prefix . 'subin_lead_notes',
			'lead_activity' => $wpdb->prefix . 'subin_lead_activity',
			'sync_events'  => $wpdb->prefix . 'subin_sync_events',
			'notification_jobs' => $wpdb->prefix . 'subin_notification_jobs',
			'submission_receipts' => $wpdb->prefix . 'subin_submission_receipts',
			'rate_limits'   => $wpdb->prefix . 'subin_rate_limits',
		);
	}

	/**
	 * Create database tables.
	 *
	 * @return true|WP_Error
	 */
	public static function create_tables() {
		global $wpdb;

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$tables          = self::get_table_names();
		$charset_collate = $wpdb->get_charset_collate();

		$sql = array();

		$sql[] = "CREATE TABLE {$tables['forms']} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			title VARCHAR(255) NOT NULL DEFAULT '',
			slug VARCHAR(191) NOT NULL DEFAULT '',
			status VARCHAR(30) NOT NULL DEFAULT 'active',
			template_key VARCHAR(100) NOT NULL DEFAULT '',
			description TEXT NULL,
			layout_template VARCHAR(100) NOT NULL DEFAULT 'clean_card',
			style_preset VARCHAR(100) NOT NULL DEFAULT 'default',
			header_title VARCHAR(255) NULL,
			header_description TEXT NULL,
			footer_note TEXT NULL,
			privacy_text TEXT NULL,
			success_message TEXT NULL,
			submit_button_text VARCHAR(100) NOT NULL DEFAULT 'Send',
			notification_email VARCHAR(255) NULL,
			notification_subject TEXT NULL,
			notification_enabled TINYINT(1) NOT NULL DEFAULT 1,
			save_leads TINYINT(1) NOT NULL DEFAULT 1,
			settings_json LONGTEXT NULL,
			created_at DATETIME NOT NULL,
			updated_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY slug (slug),
			KEY status (status),
			KEY template_key (template_key),
			KEY created_at (created_at)
		) {$charset_collate};";

		$sql[] = "CREATE TABLE {$tables['form_fields']} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			form_id BIGINT UNSIGNED NOT NULL,
			field_type VARCHAR(50) NOT NULL DEFAULT 'text',
			field_key VARCHAR(191) NOT NULL,
			field_label VARCHAR(255) NOT NULL DEFAULT '',
			placeholder VARCHAR(255) NULL,
			help_text TEXT NULL,
			is_required TINYINT(1) NOT NULL DEFAULT 0,
			field_options LONGTEXT NULL,
			sort_order INT UNSIGNED NOT NULL DEFAULT 0,
			created_at DATETIME NOT NULL,
			updated_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY form_id (form_id),
			KEY field_type (field_type),
			KEY field_key (field_key),
			KEY sort_order (sort_order),
			KEY form_sort (form_id, sort_order, id)
		) {$charset_collate};";

		$sql[] = "CREATE TABLE {$tables['form_analytics']} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			form_id BIGINT UNSIGNED NOT NULL,
			event_type VARCHAR(20) NOT NULL,
			event_key CHAR(64) NOT NULL,
			occurred_on DATE NOT NULL,
			created_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY event_key (event_key),
			KEY form_date_event (form_id, occurred_on, event_type),
			KEY occurred_on (occurred_on)
		) ENGINE=InnoDB {$charset_collate};";

		$sql[] = "CREATE TABLE {$tables['form_folders']} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			name VARCHAR(255) NOT NULL DEFAULT '',
			slug VARCHAR(191) NOT NULL DEFAULT '',
			created_at DATETIME NOT NULL,
			updated_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY slug (slug),
			KEY name (name)
		) {$charset_collate};";

		$sql[] = "CREATE TABLE {$tables['form_folder_map']} (
			form_id BIGINT UNSIGNED NOT NULL,
			folder_id BIGINT UNSIGNED NOT NULL,
			created_at DATETIME NOT NULL,
			PRIMARY KEY  (form_id),
			KEY folder_id (folder_id),
			KEY folder_form (folder_id, form_id)
		) {$charset_collate};";

		$sql[] = "CREATE TABLE {$tables['form_tags']} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			name VARCHAR(255) NOT NULL DEFAULT '',
			slug VARCHAR(191) NOT NULL DEFAULT '',
			created_at DATETIME NOT NULL,
			updated_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY slug (slug),
			KEY name (name)
		) {$charset_collate};";

		$sql[] = "CREATE TABLE {$tables['form_tag_map']} (
			form_id BIGINT UNSIGNED NOT NULL,
			tag_id BIGINT UNSIGNED NOT NULL,
			created_at DATETIME NOT NULL,
			PRIMARY KEY  (form_id, tag_id),
			KEY tag_id (tag_id),
			KEY tag_form (tag_id, form_id)
		) {$charset_collate};";

		$sql[] = "CREATE TABLE {$tables['leads']} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			provider VARCHAR(50) NOT NULL DEFAULT '',
			form_id VARCHAR(100) NOT NULL DEFAULT '',
			form_name VARCHAR(255) NOT NULL DEFAULT '',
			source_url TEXT NULL,
			referrer TEXT NULL,
			customer_name VARCHAR(255) NULL,
			customer_email VARCHAR(255) NULL,
			customer_phone VARCHAR(100) NULL,
			message_preview TEXT NULL,
			status VARCHAR(30) NOT NULL DEFAULT 'new',
			status_version BIGINT UNSIGNED NOT NULL DEFAULT 1,
			email_status VARCHAR(30) NOT NULL DEFAULT 'unknown',
			email_status_version BIGINT UNSIGNED NOT NULL DEFAULT 1,
			auto_reply_status VARCHAR(30) NOT NULL DEFAULT 'not_configured',
			auto_reply_status_version BIGINT UNSIGNED NOT NULL DEFAULT 1,
			workflow_status VARCHAR(30) NOT NULL DEFAULT 'open',
			assigned_user_id BIGINT UNSIGNED NULL,
			follow_up_at DATETIME NULL,
			priority VARCHAR(30) NOT NULL DEFAULT 'normal',
			workflow_version BIGINT UNSIGNED NOT NULL DEFAULT 1,
			last_activity_at DATETIME NULL,
			submitted_at DATETIME NULL,
			expires_at DATETIME NULL,
			created_at DATETIME NOT NULL,
			updated_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY status (status),
			KEY email_status (email_status),
			KEY auto_reply_status (auto_reply_status),
			KEY workflow_status (workflow_status),
			KEY assigned_user_id (assigned_user_id),
			KEY follow_up_at (follow_up_at),
			KEY priority (priority),
			KEY provider (provider),
			KEY submitted_at (submitted_at),
			KEY customer_email (customer_email),
			KEY expires_at (expires_at),
			KEY provider_form_submitted (provider, form_id, submitted_at, id),
			KEY status_submitted (status, submitted_at, id),
			KEY email_status_submitted (email_status, submitted_at, id),
			KEY auto_reply_status_submitted (auto_reply_status, submitted_at, id),
			KEY updated_cursor (updated_at, id)
		) {$charset_collate};";

		$sql[] = "CREATE TABLE {$tables['lead_fields']} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			lead_id BIGINT UNSIGNED NOT NULL,
			field_key VARCHAR(191) NOT NULL,
			field_label VARCHAR(255) NULL,
			field_value LONGTEXT NULL,
			is_sensitive TINYINT(1) NOT NULL DEFAULT 0,
			created_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY lead_id (lead_id),
			KEY field_key (field_key),
			KEY is_sensitive (is_sensitive)
		) {$charset_collate};";

		$sql[] = "CREATE TABLE {$tables['mail_events']} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			lead_id BIGINT UNSIGNED NULL,
			correlation_id VARCHAR(64) NULL,
			source VARCHAR(100) NOT NULL DEFAULT 'unknown',
			event_kind VARCHAR(40) NULL,
			parent_event_id BIGINT UNSIGNED NULL,
			retry_of_event_id BIGINT UNSIGNED NULL,
			job_id BIGINT UNSIGNED NULL,
			rule_id BIGINT UNSIGNED NULL,
			template_id BIGINT UNSIGNED NULL,
			rule_version INT UNSIGNED NULL,
			provider VARCHAR(64) NULL,
			form_id VARCHAR(190) NULL,
			form_name VARCHAR(255) NULL,
			rule_name VARCHAR(190) NULL,
			template_name VARCHAR(255) NULL,
			delivery_mode VARCHAR(20) NULL,
			scheduled_at DATETIME NULL,
			reason_code VARCHAR(100) NULL,
			to_email TEXT NULL,
			from_email VARCHAR(255) NULL,
			subject TEXT NULL,
			message_hash VARCHAR(64) NULL,
			status VARCHAR(30) NOT NULL DEFAULT 'pending',
			provider_status VARCHAR(32) NULL,
			provider_message_id VARCHAR(191) NULL,
			provider_evidence_source VARCHAR(64) NULL,
			provider_evidence_at DATETIME NULL,
			error_message TEXT NULL,
			created_at DATETIME NOT NULL,
			updated_at DATETIME NOT NULL,
			completed_at DATETIME NULL,
			PRIMARY KEY  (id),
			KEY lead_id (lead_id),
			KEY status (status),
			KEY source (source),
			KEY created_at (created_at),
			KEY correlation_id (correlation_id),
			KEY lead_created (lead_id, created_at, id),
			KEY status_created (status, created_at, id),
			KEY source_created (source, created_at, id),
			KEY event_kind_created (event_kind, created_at, id),
			KEY mail_parent (parent_event_id, id),
			KEY mail_retry (retry_of_event_id, id),
			KEY mail_job (job_id, source, id),
			KEY mail_rule_created (rule_id, created_at, id),
			KEY mail_template_created (template_id, created_at, id),
			KEY mail_provider_form_created (provider, form_id(100), created_at, id),
			KEY mail_schedule (delivery_mode, scheduled_at, id),
			KEY mail_reason_created (reason_code, created_at, id),
			KEY mail_provider_evidence (provider_evidence_source, provider_message_id(100), id),
			KEY provider_status_created (provider_status, provider_evidence_at, id),
			KEY updated_cursor (updated_at, id)
		) {$charset_collate};";

		$sql[] = "CREATE TABLE {$tables['auto_reply_templates']} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			template_key VARCHAR(100) NOT NULL DEFAULT '',
			name VARCHAR(255) NOT NULL DEFAULT '',
			status VARCHAR(30) NOT NULL DEFAULT 'draft',
			subject TEXT NOT NULL,
			body LONGTEXT NULL,
			preheader TEXT NULL,
			project_json LONGTEXT NULL,
			rendered_html LONGTEXT NULL,
			rendered_css LONGTEXT NULL,
			created_at DATETIME NOT NULL,
			updated_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY template_key (template_key),
			KEY status (status),
			KEY created_at (created_at),
			KEY updated_at (updated_at)
		) {$charset_collate};";

		$sql[] = "CREATE TABLE {$tables['auto_reply_rules']} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			name VARCHAR(190) NOT NULL,
			provider VARCHAR(64) NOT NULL,
			form_id VARCHAR(190) NOT NULL,
			form_name VARCHAR(255) NULL,
			visitor_email_field VARCHAR(190) NOT NULL,
			visitor_email_field_label VARCHAR(255) NULL,
			template_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
			enabled TINYINT(1) NOT NULL DEFAULT 1,
			status VARCHAR(20) NOT NULL DEFAULT 'published',
			rule_version INT UNSIGNED NOT NULL DEFAULT 1,
			priority INT UNSIGNED NOT NULL DEFAULT 100,
			condition_match VARCHAR(10) NOT NULL DEFAULT 'all',
			conditions_json LONGTEXT NULL,
			subject TEXT NULL,
			body LONGTEXT NULL,
			preheader TEXT NULL,
			from_name VARCHAR(255) NULL,
			from_email VARCHAR(255) NULL,
			reply_to_name VARCHAR(255) NULL,
			reply_to_email VARCHAR(255) NULL,
			delivery_mode VARCHAR(20) NOT NULL DEFAULT 'immediate',
			delay_seconds INT UNSIGNED NOT NULL DEFAULT 0,
			created_at DATETIME NOT NULL,
			updated_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY provider (provider),
			KEY form_id (form_id),
			KEY template_id (template_id),
			KEY enabled (enabled),
			KEY priority (priority),
			KEY policy_delivery (provider, form_id(100), enabled, status, priority, id)
		) {$charset_collate};";

		$sql[] = "CREATE TABLE {$tables['lead_notes']} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			lead_id BIGINT UNSIGNED NOT NULL,
			user_id BIGINT UNSIGNED NULL,
			note LONGTEXT NOT NULL,
			note_version BIGINT UNSIGNED NOT NULL DEFAULT 1,
			sync_footprint TINYINT(1) NOT NULL DEFAULT 0,
			created_at DATETIME NOT NULL,
			updated_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY lead_id (lead_id),
			KEY user_id (user_id),
			KEY created_at (created_at)
		) {$charset_collate};";

		$sql[] = "CREATE TABLE {$tables['lead_activity']} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			lead_id BIGINT UNSIGNED NOT NULL,
			actor_user_id BIGINT UNSIGNED NULL,
			activity_type VARCHAR(100) NOT NULL DEFAULT '',
			activity_message TEXT NULL,
			metadata_json LONGTEXT NULL,
			created_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY lead_id (lead_id),
			KEY actor_user_id (actor_user_id),
			KEY activity_type (activity_type),
			KEY created_at (created_at)
		) {$charset_collate};";

		$sql[] = "CREATE TABLE {$tables['sync_events']} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			event_type VARCHAR(100) NOT NULL DEFAULT '',
			object_type VARCHAR(100) NOT NULL DEFAULT '',
			object_id VARCHAR(191) NOT NULL DEFAULT '',
			payload_json LONGTEXT NULL,
			status VARCHAR(30) NOT NULL DEFAULT 'pending',
			attempts INT UNSIGNED NOT NULL DEFAULT 0,
			claim_token VARCHAR(64) NULL,
			lease_until DATETIME NULL,
			next_attempt_at DATETIME NULL,
			last_error TEXT NULL,
			created_at DATETIME NOT NULL,
			updated_at DATETIME NOT NULL,
			sent_at DATETIME NULL,
			PRIMARY KEY  (id),
			KEY event_type (event_type),
			KEY object_type (object_type),
			KEY object_id (object_id),
			KEY status (status),
			KEY created_at (created_at),
			KEY status_next_attempt (status, next_attempt_at, id),
			KEY status_lease (status, lease_until, id),
			KEY claim_token (claim_token, id)
		) {$charset_collate};";

		$sql[] = "CREATE TABLE {$tables['notification_jobs']} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			receipt_id BIGINT UNSIGNED NULL,
			idempotency_key VARCHAR(191) NOT NULL,
			email_type VARCHAR(50) NOT NULL DEFAULT 'admin_notification',
			lead_id BIGINT UNSIGNED NULL,
			retry_of_event_id BIGINT UNSIGNED NULL,
			payload_json LONGTEXT NOT NULL,
			payload_version SMALLINT UNSIGNED NOT NULL DEFAULT 0,
			payload_hash CHAR(64) NOT NULL DEFAULT '',
			payload_redacted_at DATETIME NULL,
			status VARCHAR(30) NOT NULL DEFAULT 'pending',
			attempts INT UNSIGNED NOT NULL DEFAULT 0,
			available_at DATETIME NOT NULL,
			lease_token VARCHAR(64) NULL,
			lease_until DATETIME NULL,
			last_error TEXT NULL,
			created_at DATETIME NOT NULL,
			updated_at DATETIME NOT NULL,
			sent_at DATETIME NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY idempotency_key (idempotency_key),
			UNIQUE KEY receipt_email_type (receipt_id, email_type),
			KEY receipt_id (receipt_id),
			KEY lead_id (lead_id),
			KEY retry_of_event (retry_of_event_id, id),
			KEY status_available (status, available_at, id),
			KEY status_lease (status, lease_until, id),
			KEY status_redacted (status, payload_redacted_at, id),
			KEY lease_token (lease_token, id),
			KEY sent_at (sent_at)
		) {$charset_collate};";

		$sql[] = "CREATE TABLE {$tables['rate_limits']} (
			rate_key VARCHAR(191) NOT NULL,
			attempts INT UNSIGNED NOT NULL DEFAULT 0,
			window_started_at DATETIME NOT NULL,
			expires_at DATETIME NOT NULL,
			PRIMARY KEY  (rate_key),
			KEY expires_at (expires_at)
		) {$charset_collate};";

		$sql[] = "CREATE TABLE {$tables['submission_receipts']} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			submission_key CHAR(64) NOT NULL,
			request_hash CHAR(64) NOT NULL,
			provider VARCHAR(50) NOT NULL DEFAULT '',
			form_id VARCHAR(100) NOT NULL DEFAULT '',
			lead_id BIGINT UNSIGNED NULL,
			status VARCHAR(30) NOT NULL DEFAULT 'processing',
			response_json LONGTEXT NULL,
			notification_job_count INT UNSIGNED NOT NULL DEFAULT 0,
			created_at DATETIME NOT NULL,
			updated_at DATETIME NOT NULL,
			completed_at DATETIME NULL,
			expires_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY submission_key (submission_key),
			KEY lead_id (lead_id),
			KEY provider_form_created (provider, form_id, created_at, id),
			KEY status_expires (status, expires_at, id),
			KEY expires_at (expires_at, id)
		) ENGINE=InnoDB {$charset_collate};";

		foreach ( $sql as $statement ) {
			$wpdb->last_error = '';
			dbDelta( $statement );
			if ( '' !== (string) $wpdb->last_error ) {
				return new WP_Error(
					'subin_schema_change_failed',
					__( 'Submission Inbox could not update its database schema.', 'submission-inbox' ),
					array( 'database_error' => (string) $wpdb->last_error )
				);
			}
		}

		return true;
	}

	/**
	 * Create or update tables and store the schema version only after verification.
	 *
	 * @return true|WP_Error
	 */
	public static function install_or_upgrade() {
		$previous_version = (string) get_option( self::OPTION_KEY, '' );
		$lock_token = self::acquire_migration_lock();
		if ( is_wp_error( $lock_token ) ) {
			return $lock_token;
		}

		try {
			$created = self::create_tables();
			if ( is_wp_error( $created ) ) {
				return self::record_migration_error( $created );
			}

			$engines_ready = self::ensure_transactional_engines();
			if ( is_wp_error( $engines_ready ) ) {
				return self::record_migration_error( $engines_ready );
			}

			$migrated = self::migrate_auto_reply_rules();
			if ( is_wp_error( $migrated ) ) {
				return self::record_migration_error( $migrated );
			}

			$mail_events_backfilled = self::backfill_mail_event_audit_fields();
			if ( is_wp_error( $mail_events_backfilled ) ) {
				return self::record_migration_error( $mail_events_backfilled );
			}

			$legacy_delete_pending = false !== get_option( self::LEGACY_NOTE_DELETE_CURSOR_OPTION, false );
			$legacy_delete_complete = (bool) get_option( self::LEGACY_NOTE_DELETE_COMPLETE_OPTION, false );
			if ( $legacy_delete_pending || ! $legacy_delete_complete ) {
				$reconciled = self::reconcile_legacy_deleted_note_sync();
				if ( is_wp_error( $reconciled ) ) {
					return self::record_migration_error( $reconciled );
				}
			}

			$redacted = self::redact_legacy_note_activity_metadata();
			if ( is_wp_error( $redacted ) ) {
				return self::record_migration_error( $redacted );
			}

			$backfilled = self::backfill_note_sync_footprints(
				array(),
				'' === $previous_version || version_compare( $previous_version, '1.18.0', '<' )
			);
			if ( is_wp_error( $backfilled ) ) {
				return self::record_migration_error( $backfilled );
			}

			$backfilled = self::backfill_sync_retry_schedule();
			if ( is_wp_error( $backfilled ) ) {
				return self::record_migration_error( $backfilled );
			}

			$backfilled = self::backfill_auto_reply_status_projection();
			if ( is_wp_error( $backfilled ) ) {
				return self::record_migration_error( $backfilled );
			}

			$verified = self::verify_schema();
			if ( is_wp_error( $verified ) ) {
				return self::record_migration_error( $verified );
			}

			update_option( self::OPTION_KEY, self::VERSION, false );
			if ( self::VERSION !== (string) get_option( self::OPTION_KEY, '' ) ) {
				return self::record_migration_error(
					new WP_Error(
						'subin_schema_version_not_saved',
						__( 'Submission Inbox updated its tables but could not save the schema version.', 'submission-inbox' )
					)
				);
			}

			delete_option( self::LAST_ERROR_OPTION );

			return true;
		} catch ( Throwable $throwable ) {
			return self::record_migration_error(
				new WP_Error(
					'subin_schema_upgrade_exception',
					__( 'Submission Inbox encountered an unexpected database upgrade error.', 'submission-inbox' ),
					array( 'exception' => sanitize_text_field( $throwable->getMessage() ) )
				)
			);
		} finally {
			self::release_migration_lock( $lock_token );
		}
	}

	/**
	 * Migrate legacy built-in form auto-reply settings into rule rows.
	 *
	 * @return true|WP_Error
	 */
	private static function migrate_auto_reply_rules() {
		return self::migrate_legacy_auto_reply_settings( self::get_table_names() );
	}

	/**
	 * Backfill only event facts that can be derived from plugin-owned history.
	 *
	 * Legacy rows never receive provider delivery claims. Their terminal time is
	 * the last timestamp the old schema recorded, and ambiguous mail purposes are
	 * explicitly classified as generic.
	 *
	 * @return true|WP_Error
	 */
	private static function backfill_mail_event_audit_fields() {
		global $wpdb;
		if ( (bool) get_option( self::MAIL_EVENT_AUDIT_COMPLETE_OPTION, false ) ) {
			return true;
		}

		$tables = self::get_table_names();
		$table  = $tables['mail_events'];
		$leads     = $tables['leads'];
		$rules     = $tables['auto_reply_rules'];
		$templates = $tables['auto_reply_templates'];
		$cursor = absint( get_option( self::MAIL_EVENT_AUDIT_CURSOR_OPTION, 0 ) );
		$ids = $wpdb->get_col(
			$wpdb->prepare(
				"SELECT id FROM {$table} WHERE id > %d ORDER BY id ASC LIMIT %d",
				$cursor,
				self::MAIL_EVENT_AUDIT_BATCH_SIZE
			)
		); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		if ( '' !== (string) $wpdb->last_error ) {
			return self::database_error( 'subin_mail_event_audit_backfill_read_failed' );
		}
		$ids = array_values( array_filter( array_map( 'absint', is_array( $ids ) ? $ids : array() ) ) );
		if ( empty( $ids ) ) {
			update_option( self::MAIL_EVENT_AUDIT_COMPLETE_OPTION, 1, false );
			delete_option( self::MAIL_EVENT_AUDIT_CURSOR_OPTION );
			wp_clear_scheduled_hook( self::MAIL_EVENT_AUDIT_HOOK );
			return 1 === absint( get_option( self::MAIL_EVENT_AUDIT_COMPLETE_OPTION, 0 ) );
		}

		$placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
		$sql = "UPDATE {$table} events
			LEFT JOIN {$leads} leads ON leads.id = events.lead_id
			LEFT JOIN {$rules} rules ON rules.id = events.rule_id
			LEFT JOIN {$templates} templates ON templates.id = events.template_id
			SET events.provider = COALESCE(NULLIF(events.provider, ''), NULLIF(leads.provider, ''), NULLIF(events.source, '')),
				events.form_id = COALESCE(NULLIF(events.form_id, ''), NULLIF(leads.form_id, '')),
				events.form_name = COALESCE(NULLIF(events.form_name, ''), NULLIF(leads.form_name, ''), NULLIF(rules.form_name, '')),
				events.rule_name = COALESCE(NULLIF(events.rule_name, ''), NULLIF(rules.name, '')),
				events.template_name = COALESCE(NULLIF(events.template_name, ''), NULLIF(templates.name, '')),
				events.event_kind = COALESCE(NULLIF(events.event_kind, ''), CASE
					WHEN events.source = 'submission_inbox_auto_reply' AND events.reason_code = 'manual_test' THEN 'auto_reply_test'
					WHEN events.source = 'submission_inbox_auto_reply' THEN 'visitor_confirmation'
					WHEN events.source = 'submission_inbox_form' THEN 'admin_notification'
					ELSE 'generic' END),
				events.completed_at = CASE
					WHEN events.completed_at IS NULL AND events.status IN ('sent', 'failed', 'unknown', 'no_email_generated') THEN events.updated_at
					ELSE events.completed_at END
			WHERE events.id IN ({$placeholders})";
		$wpdb->last_error = '';
		$updated = $wpdb->query( $wpdb->prepare( $sql, $ids ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		if ( false === $updated || '' !== (string) $wpdb->last_error ) {
			return self::database_error( 'subin_mail_event_audit_backfill_failed' );
		}

		$cursor = max( $ids );
		update_option( self::MAIL_EVENT_AUDIT_CURSOR_OPTION, $cursor, false );
		if ( $cursor !== absint( get_option( self::MAIL_EVENT_AUDIT_CURSOR_OPTION, 0 ) ) ) {
			return new WP_Error( 'subin_mail_event_audit_cursor_failed', __( 'Submission Inbox could not save the Email Log migration cursor.', 'submission-inbox' ) );
		}
		self::schedule_mail_event_audit_backfill();

		return true;
	}

	/** Continue the bounded Email Log audit migration from WP-Cron. */
	public static function continue_mail_event_audit_backfill() {
		$lock = self::acquire_migration_lock();
		if ( is_wp_error( $lock ) ) {
			self::schedule_mail_event_audit_backfill();
			return;
		}
		try {
			$result = self::backfill_mail_event_audit_fields();
			if ( is_wp_error( $result ) ) {
				self::record_migration_error( $result );
				self::schedule_mail_event_audit_backfill();
			}
		} finally {
			self::release_migration_lock( $lock );
		}
	}

	/** Ensure the next bounded audit batch is scheduled. */
	public static function schedule_mail_event_audit_backfill() {
		if ( ! wp_next_scheduled( self::MAIL_EVENT_AUDIT_HOOK ) ) {
			wp_schedule_single_event( time() + MINUTE_IN_SECONDS, self::MAIL_EVENT_AUDIT_HOOK );
		}
	}

	/**
	 * Remove private note excerpts written by legacy activity code.
	 *
	 * Note activity metadata is plugin-owned. Keeping only stable identifiers and
	 * versions makes an edited or deleted note disappear from every local table,
	 * including installations upgraded from a version that copied excerpts.
	 *
	 * @param array<string,string> $tables Optional isolated table map for tests.
	 * @return true|WP_Error
	 */
	public static function redact_legacy_note_activity_metadata( array $tables = array() ) {
		global $wpdb;

		$is_isolated = ! empty( $tables );
		$tables      = $is_isolated ? $tables : self::get_table_names();
		$table       = (string) ( $tables['lead_activity'] ?? '' );
		if ( '' === $table ) {
			return new WP_Error(
				'subin_note_activity_migration_table_invalid',
				__( 'Submission Inbox could not prepare legacy note activity cleanup.', 'submission-inbox' )
			);
		}

		$batch_size = $is_isolated ? 500 : self::NOTE_ACTIVITY_REDACTION_BATCH_SIZE;
		$last_id    = $is_isolated ? 0 : absint( get_option( self::NOTE_ACTIVITY_REDACTION_CURSOR_OPTION, 0 ) );
		do {
			$wpdb->last_error = '';
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT id, metadata_json FROM {$table}
					WHERE id > %d AND activity_type IN (%s, %s, %s)
						AND (metadata_json LIKE %s OR metadata_json LIKE %s OR metadata_json LIKE %s)
					ORDER BY id ASC
					LIMIT %d",
					$last_id,
					'note_added',
					'note_updated',
					'note_deleted',
					'%' . $wpdb->esc_like( 'previous_excerpt' ) . '%',
					'%' . $wpdb->esc_like( 'updated_excerpt' ) . '%',
					'%' . $wpdb->esc_like( 'deleted_excerpt' ) . '%',
					$batch_size
				),
				ARRAY_A
			); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			if ( '' !== (string) $wpdb->last_error ) {
				return self::database_error( 'subin_note_activity_migration_read_failed' );
			}
			$rows = is_array( $rows ) ? $rows : array();

			foreach ( $rows as $row ) {
				$activity_id = absint( $row['id'] ?? 0 );
				$last_id     = max( $last_id, $activity_id );
				$legacy      = json_decode( (string) ( $row['metadata_json'] ?? '' ), true );
				$clean       = array();
				if ( is_array( $legacy ) ) {
					foreach ( array( 'note_id', 'note_version', 'from_version', 'to_version' ) as $key ) {
						if ( isset( $legacy[ $key ] ) && is_scalar( $legacy[ $key ] ) ) {
							$value = absint( $legacy[ $key ] );
							if ( $value > 0 ) {
								$clean[ $key ] = $value;
							}
						}
					}
				}

				$encoded = wp_json_encode( $clean );
				if ( ! is_string( $encoded ) ) {
					return new WP_Error(
						'subin_note_activity_migration_encode_failed',
						__( 'Submission Inbox could not sanitize legacy note activity.', 'submission-inbox' )
					);
				}
				if ( $encoded === (string) ( $row['metadata_json'] ?? '' ) ) {
					continue;
				}
				$updated = $wpdb->update(
					$table,
					array( 'metadata_json' => $encoded ),
					array( 'id' => $activity_id ),
					array( '%s' ),
					array( '%d' )
				);
				if ( false === $updated ) {
					return self::database_error( 'subin_note_activity_migration_write_failed' );
				}
			}
			if ( ! $is_isolated ) {
				if ( $batch_size === count( $rows ) && $last_id > 0 ) {
					update_option( self::NOTE_ACTIVITY_REDACTION_CURSOR_OPTION, $last_id, false );
					if ( $last_id !== absint( get_option( self::NOTE_ACTIVITY_REDACTION_CURSOR_OPTION, 0 ) ) ) {
						return new WP_Error(
							'subin_note_activity_migration_cursor_failed',
							__( 'Submission Inbox could not save the legacy note cleanup cursor.', 'submission-inbox' )
						);
					}
					self::schedule_note_activity_redaction();
				} else {
					delete_option( self::NOTE_ACTIVITY_REDACTION_CURSOR_OPTION );
					if ( false === get_option( self::LEGACY_NOTE_DELETE_CURSOR_OPTION, false ) ) {
						wp_clear_scheduled_hook( self::NOTE_ACTIVITY_REDACTION_HOOK );
					}
				}

				return true;
			}
		} while ( $batch_size === count( $rows ) );

		return true;
	}

	/**
	 * Reconcile notes deleted before versioned sync tombstones existed.
	 *
	 * Production work is bounded and cursored. Cloud delivery pauses while the
	 * cursor exists, so an old queued aggregate cannot upload deleted note text
	 * between batches.
	 *
	 * @param array<string,string> $tables Optional isolated table map for tests.
	 * @param bool|null            $ever_connected Optional durable connection evidence override for tests.
	 * @return true|WP_Error
	 */
	public static function reconcile_legacy_deleted_note_sync( array $tables = array(), $ever_connected = null ) {
		global $wpdb;

		$is_isolated = ! empty( $tables );
		$tables      = $is_isolated ? $tables : self::get_table_names();
		foreach ( array( 'leads', 'lead_notes', 'lead_activity' ) as $key ) {
			if ( empty( $tables[ $key ] ) ) {
				return new WP_Error( 'subin_legacy_note_delete_tables_invalid', __( 'Submission Inbox could not prepare legacy note deletion cleanup.', 'submission-inbox' ) );
			}
		}
		if ( ! class_exists( '\SUBIN\Sync\SyncEventRepository' ) ) {
			return new WP_Error( 'subin_legacy_note_delete_repository_missing', __( 'Submission Inbox could not load note deletion cleanup.', 'submission-inbox' ) );
		}

		$batch_size     = $is_isolated ? 500 : 100;
		$last_id        = $is_isolated ? 0 : absint( get_option( self::LEGACY_NOTE_DELETE_CURSOR_OPTION, 0 ) );
		$ever_connected = null === $ever_connected ? self::has_durable_cloud_connection_evidence() : (bool) $ever_connected;
		if ( ! $is_isolated && false === get_option( self::LEGACY_NOTE_DELETE_CURSOR_OPTION, false ) ) {
			delete_option( self::LEGACY_NOTE_DELETE_COMPLETE_OPTION );
			update_option( self::LEGACY_NOTE_DELETE_CURSOR_OPTION, 0, false );
			if ( false === get_option( self::LEGACY_NOTE_DELETE_CURSOR_OPTION, false ) ) {
				return new WP_Error( 'subin_legacy_note_delete_guard_failed', __( 'Submission Inbox could not pause Cloud sync for the legacy note deletion cleanup.', 'submission-inbox' ) );
			}
		}
		do {
			$wpdb->last_error = '';
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT id, lead_id, metadata_json, created_at
					FROM {$tables['lead_activity']}
					WHERE id > %d AND activity_type = %s
					ORDER BY id ASC LIMIT %d",
					$last_id,
					'note_deleted',
					$batch_size
				),
				ARRAY_A
			); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			if ( ! is_array( $rows ) || '' !== (string) $wpdb->last_error ) {
				return self::database_error( 'subin_legacy_note_delete_read_failed' );
			}

			foreach ( $rows as $row ) {
				$activity_id = absint( $row['id'] ?? 0 );
				$lead_id     = absint( $row['lead_id'] ?? 0 );
				$last_id     = max( $last_id, $activity_id );
				$metadata    = json_decode( (string) ( $row['metadata_json'] ?? '' ), true );
				$note_id     = is_array( $metadata ) ? absint( $metadata['note_id'] ?? 0 ) : 0;
				if ( $lead_id <= 0 || $note_id <= 0 ) {
					continue;
				}
				$wpdb->last_error = '';
				$note_exists = absint(
					$wpdb->get_var(
						$wpdb->prepare(
							"SELECT id FROM {$tables['lead_notes']} WHERE id = %d AND lead_id = %d LIMIT 1",
							$note_id,
							$lead_id
						)
					)
				);
				if ( '' !== (string) $wpdb->last_error ) {
					return self::database_error( 'subin_legacy_note_delete_note_read_failed' );
				}
				if ( $note_exists > 0 ) {
					continue;
				}
				if ( false === $wpdb->query( 'START TRANSACTION' ) ) {
					return self::database_error( 'subin_legacy_note_delete_transaction_failed' );
				}
				$wpdb->last_error = '';
				$locked_lead = absint(
					$wpdb->get_var(
						$wpdb->prepare(
							"SELECT id FROM {$tables['leads']} WHERE id = %d LIMIT 1 FOR UPDATE",
							$lead_id
						)
					)
				);
				if ( '' !== (string) $wpdb->last_error ) {
					$error = self::database_error( 'subin_legacy_note_delete_lead_lock_failed' );
					$wpdb->query( 'ROLLBACK' );
					return $error;
				}
				if ( $locked_lead !== $lead_id ) {
					$wpdb->query( 'ROLLBACK' );
					continue;
				}

				$from_version = is_array( $metadata ) ? absint( $metadata['from_version'] ?? $metadata['note_version'] ?? 1 ) : 1;
				$version      = max( 2, $from_version + 1, is_array( $metadata ) ? absint( $metadata['to_version'] ?? 0 ) : 0 );
				$occurred_at  = sanitize_text_field( (string) ( $row['created_at'] ?? current_time( 'mysql' ) ) );
				$staged       = ( new \SUBIN\Sync\SyncEventRepository() )->stage_note_delete_in_transaction(
					$lead_id,
					array(
						'lead' => array( 'id' => $lead_id ),
						'note' => array(
							'id'             => $note_id,
							'note_version'   => $version,
							'deleted'        => true,
							'sync_footprint' => $ever_connected,
							'created_at'     => $occurred_at,
							'updated_at'     => $occurred_at,
						),
					),
					false
				);
				if ( is_wp_error( $staged ) ) {
					$wpdb->query( 'ROLLBACK' );
					return $staged;
				}
				if ( false === $wpdb->query( 'COMMIT' ) ) {
					$error = self::database_error( 'subin_legacy_note_delete_commit_failed' );
					$wpdb->query( 'ROLLBACK' );
					return $error;
				}
			}

			if ( ! $is_isolated ) {
				if ( $batch_size === count( $rows ) && $last_id > 0 ) {
					delete_option( self::LEGACY_NOTE_DELETE_COMPLETE_OPTION );
					update_option( self::LEGACY_NOTE_DELETE_CURSOR_OPTION, $last_id, false );
					if ( $last_id !== absint( get_option( self::LEGACY_NOTE_DELETE_CURSOR_OPTION, 0 ) ) ) {
						return new WP_Error( 'subin_legacy_note_delete_cursor_failed', __( 'Submission Inbox could not save the legacy note deletion cursor.', 'submission-inbox' ) );
					}
					self::schedule_note_activity_redaction();
				} else {
					delete_option( self::LEGACY_NOTE_DELETE_CURSOR_OPTION );
					update_option( self::LEGACY_NOTE_DELETE_COMPLETE_OPTION, 1, false );
					if ( 1 !== absint( get_option( self::LEGACY_NOTE_DELETE_COMPLETE_OPTION, 0 ) ) ) {
						return new WP_Error( 'subin_legacy_note_delete_complete_failed', __( 'Submission Inbox could not finish the legacy note deletion cleanup.', 'submission-inbox' ) );
					}
					if ( false === get_option( self::NOTE_ACTIVITY_REDACTION_CURSOR_OPTION, false ) ) {
						wp_clear_scheduled_hook( self::NOTE_ACTIVITY_REDACTION_HOOK );
					}
				}

				return true;
			}
		} while ( $batch_size === count( $rows ) );

		return true;
	}

	/**
	 * Determine whether this site has durable evidence of a prior Cloud connection.
	 *
	 * The numeric site id survives disconnects, while an active token covers a
	 * connection that has not yet persisted an id. Never-connected sites must not
	 * emit historical note identifiers merely because they upgrade.
	 *
	 * @return bool
	 */
	private static function has_durable_cloud_connection_evidence() {
		$settings = get_option( 'subin_settings', array() );
		if ( ! is_array( $settings ) ) {
			return false;
		}

		return absint( $settings['cloud_sync_site_id'] ?? 0 ) > 0
			|| '' !== sanitize_text_field( (string) ( $settings['cloud_sync_site_token'] ?? '' ) );
	}

	/**
	 * Continue the legacy deleted-note reconciliation only when a cursor exists.
	 *
	 * @return true|WP_Error
	 */
	public static function continue_legacy_deleted_note_sync() {
		if ( false === get_option( self::LEGACY_NOTE_DELETE_CURSOR_OPTION, false ) ) {
			return true;
		}

		return self::reconcile_legacy_deleted_note_sync();
	}

	/**
	 * Continue a bounded legacy note-activity cleanup from its persisted cursor.
	 *
	 * @return true|WP_Error
	 */
	public static function continue_note_activity_redaction() {
		if ( false === get_option( self::NOTE_ACTIVITY_REDACTION_CURSOR_OPTION, false ) ) {
			return true;
		}

		return self::redact_legacy_note_activity_metadata();
	}

	/**
	 * Continue every bounded note-privacy migration from admin or cron.
	 *
	 * @return true|WP_Error
	 */
	public static function continue_note_privacy_migrations() {
		$reconciled = self::continue_legacy_deleted_note_sync();
		if ( is_wp_error( $reconciled ) ) {
			return $reconciled;
		}

		return self::continue_note_activity_redaction();
	}

	/**
	 * Schedule the next bounded cleanup batch without creating duplicate events.
	 *
	 * @return void
	 */
	private static function schedule_note_activity_redaction() {
		if ( ! wp_next_scheduled( self::NOTE_ACTIVITY_REDACTION_HOOK ) ) {
			wp_schedule_single_event( time() + MINUTE_IN_SECONDS, self::NOTE_ACTIVITY_REDACTION_HOOK );
		}
	}

	/**
	 * Migrate legacy built-in form auto-reply settings into policy rows.
	 *
	 * The explicit table map makes the data migration independently testable on
	 * isolated legacy tables without changing the site's stored schema version.
	 * Existing policy rows always win and the legacy settings JSON is never
	 * rewritten or removed.
	 *
	 * @param array<string,string> $tables Plugin table names.
	 * @return true|WP_Error
	 */
	public static function migrate_legacy_auto_reply_settings( array $tables ) {
		global $wpdb;

		foreach ( array( 'forms', 'form_fields', 'auto_reply_rules' ) as $required_table ) {
			if ( empty( $tables[ $required_table ] ) || ! is_string( $tables[ $required_table ] ) ) {
				return new WP_Error(
					'subin_auto_reply_migration_tables_invalid',
					__( 'Submission Inbox could not prepare the auto-reply migration.', 'submission-inbox' )
				);
			}
		}

		$wpdb->last_error = '';
		$forms = $wpdb->get_results( "SELECT id, title, settings_json FROM {$tables['forms']} WHERE settings_json IS NOT NULL AND settings_json != ''", ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
		if ( '' !== (string) $wpdb->last_error || ! is_array( $forms ) ) {
			return self::database_error( 'subin_auto_reply_migration_read_failed' );
		}

		foreach ( $forms as $form ) {
			$form_id  = absint( $form['id'] ?? 0 );
			$settings = json_decode( (string) ( $form['settings_json'] ?? '' ), true );
			if ( $form_id <= 0 || ! is_array( $settings ) || ! is_array( $settings['auto_reply'] ?? null ) ) {
				continue;
			}

			$config = $settings['auto_reply'];
			if ( ! self::has_legacy_auto_reply_configuration( $config ) ) {
				continue;
			}

			$template_id = absint( $config['template_id'] ?? 0 );
			$raw_field   = $config['recipient_field_key']
				?? $config['email_field_key']
				?? $config['auto_reply_email_field_key']
				?? $config['recipient_field_id']
				?? $config['email_field_id']
				?? $config['auto_reply_email_field_id']
				?? '';
			$field_key   = sanitize_key( is_scalar( $raw_field ) ? (string) $raw_field : '' );
			$field_id    = absint( $raw_field );

			$wpdb->last_error = '';
			$existing = absint(
				$wpdb->get_var(
					$wpdb->prepare(
						"SELECT id FROM {$tables['auto_reply_rules']} WHERE provider = %s AND form_id = %s LIMIT 1", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
						'submission_inbox_form',
						(string) $form_id
					)
				)
			);
			if ( '' !== (string) $wpdb->last_error ) {
				return self::database_error( 'subin_auto_reply_migration_lookup_failed' );
			}
			if ( $existing > 0 ) {
				continue;
			}

			$wpdb->last_error = '';
			$field = null;
			if ( '' !== $field_key || $field_id > 0 ) {
				$field = $wpdb->get_row(
					$wpdb->prepare(
						"SELECT id, field_key, field_label FROM {$tables['form_fields']}
						WHERE form_id = %d AND field_type = %s AND (field_key = %s OR id = %d)
						ORDER BY CASE WHEN field_key = %s THEN 0 ELSE 1 END, id ASC LIMIT 1", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
						$form_id,
						'email',
						$field_key,
						$field_id,
						$field_key
					),
					ARRAY_A
				);
			}
			if ( '' !== (string) $wpdb->last_error ) {
				return self::database_error( 'subin_auto_reply_migration_field_failed' );
			}
			if ( is_array( $field ) ) {
				$field_key = sanitize_key( (string) ( $field['field_key'] ?? $field_key ) );
			}

			$now  = current_time( 'mysql' );
			$name = sprintf(
				/* translators: %s: form title. */
				__( '%s auto-reply', 'submission-inbox' ),
				sanitize_text_field( (string) ( $form['title'] ?? __( 'Form', 'submission-inbox' ) ) )
			);

			$inserted = $wpdb->insert(
				$tables['auto_reply_rules'],
				array(
					'name'                      => $name,
					'provider'                  => 'submission_inbox_form',
					'form_id'                   => (string) $form_id,
					'form_name'                 => sanitize_text_field( (string) ( $form['title'] ?? '' ) ),
					'visitor_email_field'       => $field_key,
					'visitor_email_field_label' => sanitize_text_field( (string) ( is_array( $field ) ? ( $field['field_label'] ?? $field_key ) : $field_key ) ),
					'template_id'               => $template_id,
					'enabled'                   => ! empty( $config['enabled'] ) ? 1 : 0,
					'status'                    => 'published',
					'rule_version'              => 1,
					'priority'                  => 100,
					'condition_match'           => 'all',
					'conditions_json'           => '[]',
					'subject'                   => '',
					'body'                      => '',
					'preheader'                 => '',
					'from_name'                 => '',
					'from_email'                => '',
					'reply_to_name'             => '',
					'reply_to_email'            => '',
					'delivery_mode'             => 'immediate',
					'delay_seconds'             => 0,
					'created_at'                => $now,
					'updated_at'                => $now,
				),
				array( '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%s', '%d', '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%s' )
			);
			if ( false === $inserted ) {
				return self::database_error( 'subin_auto_reply_migration_insert_failed' );
			}
		}

		return true;
	}

	/**
	 * Determine whether legacy settings contain an intentional auto-reply setup.
	 *
	 * @param array<string,mixed> $config Legacy auto-reply settings.
	 * @return bool
	 */
	private static function has_legacy_auto_reply_configuration( array $config ) {
		if ( array_key_exists( 'enabled', $config ) && ! empty( $config['enabled'] ) ) {
			return true;
		}

		if ( absint( $config['template_id'] ?? 0 ) > 0 ) {
			return true;
		}

		foreach ( array( 'recipient_field_key', 'email_field_key', 'auto_reply_email_field_key' ) as $key ) {
			if ( isset( $config[ $key ] ) && '' !== trim( is_scalar( $config[ $key ] ) ? (string) $config[ $key ] : '' ) ) {
				return true;
			}
		}

		foreach ( array( 'recipient_field_id', 'email_field_id', 'auto_reply_email_field_id' ) as $key ) {
			if ( absint( $config[ $key ] ?? 0 ) > 0 ) {
				return true;
			}
		}

		return false;
	}

	/**
	 * Populate retry timestamps for queue rows created by older versions.
	 *
	 * @return true|WP_Error
	 */
	private static function backfill_sync_retry_schedule() {
		global $wpdb;

		$tables = self::get_table_names();
		$updated = $wpdb->query(
			"UPDATE {$tables['sync_events']}
			SET next_attempt_at = updated_at
			WHERE next_attempt_at IS NULL
				AND status IN ('pending', 'failed')" // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
		);

		return false === $updated ? self::database_error( 'subin_sync_retry_backfill_failed' ) : true;
	}

	/**
	 * Backfill the visitor auto-reply projection from the newest historical event.
	 *
	 * Existing non-default values always win so a repeated migration cannot turn a
	 * newly scheduled reply back into an older terminal state.
	 *
	 * @param array<string,string> $tables Optional isolated table map for tests.
	 * @return true|WP_Error
	 */
	public static function backfill_auto_reply_status_projection( array $tables = array() ) {
		global $wpdb;

		$tables = ! empty( $tables ) ? $tables : self::get_table_names();
		if ( empty( $tables['leads'] ) || empty( $tables['mail_events'] ) ) {
			return new WP_Error(
				'subin_auto_reply_status_migration_tables_invalid',
				__( 'Submission Inbox could not prepare the auto-reply status migration.', 'submission-inbox' )
			);
		}

		$updated = $wpdb->query(
			"UPDATE {$tables['leads']} leads
			INNER JOIN (
				SELECT events.lead_id, CASE events.status
					WHEN 'sent' THEN 'sent'
					WHEN 'failed' THEN 'failed'
					WHEN 'no_email_generated' THEN 'no_email_generated'
					WHEN 'pending' THEN 'scheduled'
					ELSE 'unknown'
				END AS projected_status
				FROM {$tables['mail_events']} events
				INNER JOIN (
					SELECT lead_id, MAX(id) AS event_id
					FROM {$tables['mail_events']}
					WHERE source = 'submission_inbox_auto_reply'
						AND lead_id IS NOT NULL
						AND lead_id > 0
					GROUP BY lead_id
				) latest ON latest.event_id = events.id
			) projection ON projection.lead_id = leads.id
			SET leads.auto_reply_status = projection.projected_status,
				leads.auto_reply_status_version = leads.auto_reply_status_version + 1
			WHERE leads.auto_reply_status IN ('', 'unknown', 'not_configured')
				AND leads.auto_reply_status <> projection.projected_status" // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared
		);

		return false === $updated ? self::database_error( 'subin_auto_reply_status_backfill_failed' ) : true;
	}

	/**
	 * Preserve whether an existing note may already have a remote representation.
	 *
	 * Older queue rows did not have an indexed note id and terminal payloads may
	 * already be redacted. Marking notes on any lead with sync history is a
	 * conservative one-time backfill; new notes start at zero and are marked only
	 * when their own versioned event is committed to the local queue.
	 *
	 * @param array<string,string> $tables Optional isolated table map for tests.
	 * @param bool                 $mark_all_existing Conservatively mark pre-versioning notes when Cloud was connected.
	 * @param bool|null            $ever_connected Optional durable connection evidence override for tests.
	 * @return true|WP_Error
	 */
	public static function backfill_note_sync_footprints( array $tables = array(), $mark_all_existing = false, $ever_connected = null ) {
		global $wpdb;

		$is_isolated = ! empty( $tables );
		$tables      = $is_isolated ? $tables : self::get_table_names();
		if ( empty( $tables['lead_notes'] ) || empty( $tables['sync_events'] ) ) {
			return new WP_Error(
				'subin_note_sync_footprint_tables_invalid',
				__( 'Submission Inbox could not prepare note sync history.', 'submission-inbox' )
			);
		}
		$updated = $wpdb->query(
			"UPDATE {$tables['lead_notes']} notes
			INNER JOIN (
				SELECT DISTINCT CAST(object_id AS UNSIGNED) AS lead_id
				FROM {$tables['sync_events']}
				WHERE object_type = 'lead'
			) history ON history.lead_id = notes.lead_id
			SET notes.sync_footprint = 1
			WHERE notes.sync_footprint = 0" // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared
		);
		if ( false === $updated ) {
			return self::database_error( 'subin_note_sync_footprint_backfill_failed' );
		}

		$ever_connected = null === $ever_connected ? self::has_durable_cloud_connection_evidence() : (bool) $ever_connected;
		if ( $mark_all_existing && $ever_connected ) {
			$updated = $wpdb->query(
				"UPDATE {$tables['lead_notes']} SET sync_footprint = 1 WHERE sync_footprint = 0" // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared
			);
			if ( false === $updated ) {
				return self::database_error( 'subin_note_sync_footprint_legacy_backfill_failed' );
			}
		} elseif ( ! $is_isolated && $ever_connected ) {
			$settings = get_option( SUBIN_SettingsRepository::OPTION_KEY, array() );
			if ( is_array( $settings ) && ! empty( $settings['cloud_sync_full_data_consent'] ) ) {
				$updated = $wpdb->query(
					"UPDATE {$tables['lead_notes']} SET sync_footprint = 1 WHERE sync_footprint = 0" // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared
				);
				if ( false === $updated ) {
					return self::database_error( 'subin_note_sync_footprint_consent_backfill_failed' );
				}
			}
		}

		return true;
	}

	/**
	 * Verify required tables, columns, and critical indexes.
	 *
	 * @return true|WP_Error
	 */
	public static function verify_schema() {
		global $wpdb;

		$tables = self::get_table_names();
		$required_columns = array(
			'forms'                => array( 'id', 'settings_json' ),
			'form_fields'          => array( 'id', 'form_id', 'field_key' ),
			'form_analytics'       => array( 'id', 'form_id', 'event_type', 'event_key', 'occurred_on', 'created_at' ),
			'form_folders'         => array( 'id', 'name', 'slug', 'created_at', 'updated_at' ),
			'form_folder_map'      => array( 'form_id', 'folder_id', 'created_at' ),
			'form_tags'            => array( 'id', 'name', 'slug', 'created_at', 'updated_at' ),
			'form_tag_map'         => array( 'form_id', 'tag_id', 'created_at' ),
			'leads'                => array( 'id', 'submitted_at', 'expires_at', 'status_version', 'email_status', 'email_status_version', 'auto_reply_status', 'auto_reply_status_version', 'workflow_version' ),
			'lead_fields'          => array( 'id', 'lead_id', 'field_value' ),
			'mail_events'          => array( 'id', 'lead_id', 'correlation_id', 'source', 'event_kind', 'parent_event_id', 'retry_of_event_id', 'status', 'created_at', 'updated_at', 'completed_at', 'job_id', 'rule_id', 'template_id', 'rule_version', 'provider', 'form_id', 'form_name', 'rule_name', 'template_name', 'delivery_mode', 'scheduled_at', 'reason_code', 'provider_status', 'provider_message_id', 'provider_evidence_source', 'provider_evidence_at' ),
			'auto_reply_templates' => array( 'id', 'template_key' ),
			'auto_reply_rules'     => array( 'id', 'provider', 'form_id', 'enabled', 'status', 'rule_version', 'priority', 'condition_match', 'conditions_json', 'subject', 'body', 'preheader', 'from_name', 'from_email', 'reply_to_name', 'reply_to_email', 'delivery_mode', 'delay_seconds' ),
			'lead_notes'           => array( 'id', 'lead_id', 'note_version', 'sync_footprint' ),
			'lead_activity'        => array( 'id', 'lead_id' ),
			'sync_events'          => array( 'id', 'status', 'claim_token', 'lease_until', 'next_attempt_at' ),
			'notification_jobs'    => array( 'id', 'receipt_id', 'idempotency_key', 'email_type', 'lead_id', 'retry_of_event_id', 'payload_version', 'payload_hash', 'payload_redacted_at', 'status', 'available_at', 'lease_token', 'lease_until' ),
			'submission_receipts'  => array( 'id', 'submission_key', 'request_hash', 'provider', 'form_id', 'lead_id', 'status', 'response_json', 'notification_job_count', 'expires_at' ),
			'rate_limits'          => array( 'rate_key', 'attempts', 'window_started_at', 'expires_at' ),
		);
		$required_indexes = array(
			'form_fields'       => array( 'form_sort' ),
			'form_analytics'    => array( 'event_key', 'form_date_event', 'occurred_on' ),
			'form_folders'      => array( 'slug', 'name' ),
			'form_folder_map'   => array( 'PRIMARY', 'folder_id', 'folder_form' ),
			'form_tags'         => array( 'slug', 'name' ),
			'form_tag_map'      => array( 'PRIMARY', 'tag_id', 'tag_form' ),
			'leads'             => array( 'expires_at', 'provider_form_submitted', 'status_submitted', 'email_status_submitted', 'auto_reply_status_submitted', 'updated_cursor' ),
			'mail_events'       => array( 'correlation_id', 'lead_created', 'status_created', 'source_created', 'event_kind_created', 'mail_parent', 'mail_retry', 'mail_job', 'mail_rule_created', 'mail_template_created', 'mail_provider_form_created', 'mail_schedule', 'mail_reason_created', 'mail_provider_evidence', 'provider_status_created', 'updated_cursor' ),
			'auto_reply_rules'  => array( 'policy_delivery' ),
			'sync_events'       => array( 'status_next_attempt', 'status_lease', 'claim_token' ),
			'notification_jobs' => array( 'idempotency_key', 'receipt_email_type', 'receipt_id', 'retry_of_event', 'status_available', 'status_lease', 'status_redacted', 'lease_token' ),
			'submission_receipts' => array( 'submission_key', 'lead_id', 'provider_form_created', 'status_expires', 'expires_at' ),
			'rate_limits'       => array( 'PRIMARY', 'expires_at' ),
		);
		$missing = array();

		foreach ( $required_columns as $table_key => $columns ) {
			$table = $tables[ $table_key ];
			if ( ! self::table_exists( $table ) ) {
				$missing[] = $table_key . ':table';
				continue;
			}

			$wpdb->last_error = '';
			$found_columns = array_map(
				'strtolower',
				(array) $wpdb->get_col( "SHOW COLUMNS FROM {$table}", 0 ) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
			);
			if ( '' !== (string) $wpdb->last_error ) {
				return self::database_error( 'subin_schema_column_check_failed' );
			}

			foreach ( $columns as $column ) {
				if ( ! in_array( strtolower( $column ), $found_columns, true ) ) {
					$missing[] = $table_key . ':column:' . $column;
				}
			}

			if ( empty( $required_indexes[ $table_key ] ) ) {
				continue;
			}

			$wpdb->last_error = '';
			$found_indexes = array_map(
				'strtolower',
				(array) $wpdb->get_col( "SHOW INDEX FROM {$table}", 2 ) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
			);
			if ( '' !== (string) $wpdb->last_error ) {
				return self::database_error( 'subin_schema_index_check_failed' );
			}

			foreach ( $required_indexes[ $table_key ] as $index ) {
				if ( ! in_array( strtolower( $index ), $found_indexes, true ) ) {
					$missing[] = $table_key . ':index:' . $index;
				}
			}
		}

		foreach ( self::transactional_table_keys() as $table_key ) {
			$table = $tables[ $table_key ];
			$wpdb->last_error = '';
			$engine = $wpdb->get_var(
				$wpdb->prepare(
					'SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s',
					$table
				)
			);
			if ( '' !== (string) $wpdb->last_error ) {
				return self::database_error( 'subin_schema_engine_check_failed' );
			}
			if ( 'innodb' !== strtolower( (string) $engine ) ) {
				$missing[] = $table_key . ':engine:innodb';
			}
		}

		if ( ! empty( $missing ) ) {
			return new WP_Error(
				'subin_schema_verification_failed',
				__( 'Submission Inbox database schema verification failed.', 'submission-inbox' ),
				array( 'missing' => $missing )
			);
		}

		return true;
	}

	/**
	 * Convert every table participating in submission acceptance to InnoDB.
	 *
	 * @return true|WP_Error
	 */
	private static function ensure_transactional_engines() {
		global $wpdb;

		$tables = self::get_table_names();
		foreach ( self::transactional_table_keys() as $table_key ) {
			$table = $tables[ $table_key ];
			$wpdb->last_error = '';
			$engine = $wpdb->get_var(
				$wpdb->prepare(
					'SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s',
					$table
				)
			);
			if ( '' !== (string) $wpdb->last_error || '' === (string) $engine ) {
				return self::database_error( 'subin_transaction_engine_read_failed' );
			}
			if ( 'innodb' === strtolower( (string) $engine ) ) {
				continue;
			}

			$converted = $wpdb->query( "ALTER TABLE {$table} ENGINE=InnoDB" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared
			if ( false === $converted ) {
				return self::database_error( 'subin_transaction_engine_migration_failed' );
			}
		}

		return true;
	}

	/**
	 * List tables that must share the acceptance transaction.
	 *
	 * @return array<int,string>
	 */
	private static function transactional_table_keys() {
		return array(
			'forms',
			'form_fields',
			'form_analytics',
			'form_folder_map',
			'form_tag_map',
			'leads',
			'lead_fields',
			'mail_events',
			'auto_reply_rules',
			'lead_notes',
			'lead_activity',
			'sync_events',
			'notification_jobs',
			'submission_receipts',
			'rate_limits',
		);
	}

	/**
	 * Check a plugin-owned table name.
	 *
	 * @param string $table Table name.
	 * @return bool
	 */
	public static function table_exists( $table ) {
		global $wpdb;

		if ( ! in_array( $table, array_values( self::get_table_names() ), true ) ) {
			return false;
		}

		$found = $wpdb->get_var(
			$wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table ) )
		);

		return (string) $found === (string) $table;
	}

	/**
	 * Run a lightweight schema version check suitable for early bootstrap.
	 *
	 * @return true|WP_Error
	 */
	public static function maybe_upgrade() {
		$stored_version = (string) get_option( self::OPTION_KEY, '' );

		if ( '' === $stored_version || version_compare( $stored_version, self::VERSION, '<' ) ) {
			return self::install_or_upgrade();
		}

		return true;
	}

	/**
	 * Alias for early bootstrap callers that need a ready schema.
	 *
	 * @return true|WP_Error
	 */
	public static function ensure_ready() {
		return self::maybe_upgrade();
	}

	/**
	 * Acquire a per-site migration lock using atomic option creation.
	 *
	 * @return string|WP_Error
	 */
	private static function acquire_migration_lock() {
		$current = get_option( self::LOCK_OPTION, false );
		if ( false !== $current ) {
			$acquired_at = is_array( $current ) ? absint( $current['acquired_at'] ?? 0 ) : 0;
			if ( $acquired_at > 0 && ( time() - $acquired_at ) < self::LOCK_TTL ) {
				return new WP_Error(
					'subin_schema_upgrade_locked',
					__( 'Submission Inbox database upgrade is already running.', 'submission-inbox' ),
					array( 'retryable' => true )
				);
			}

			delete_option( self::LOCK_OPTION );
		}

		$token = wp_generate_uuid4();
		$locked = add_option(
			self::LOCK_OPTION,
			array(
				'token'       => $token,
				'acquired_at' => time(),
			),
			'',
			false
		);

		if ( ! $locked ) {
			return new WP_Error(
				'subin_schema_upgrade_locked',
				__( 'Submission Inbox database upgrade is already running.', 'submission-inbox' ),
				array( 'retryable' => true )
			);
		}

		return $token;
	}

	/**
	 * Release the lock only when it is still owned by this process.
	 *
	 * @param string $token Lock token.
	 * @return void
	 */
	private static function release_migration_lock( $token ) {
		$current = get_option( self::LOCK_OPTION, array() );
		$current_token = is_array( $current ) ? (string) ( $current['token'] ?? '' ) : '';

		if ( '' !== $current_token && hash_equals( $current_token, (string) $token ) ) {
			delete_option( self::LOCK_OPTION );
		}
	}

	/**
	 * Persist a redacted migration failure for diagnostics and retry on the next request.
	 *
	 * @param WP_Error $error Migration error.
	 * @return WP_Error
	 */
	private static function record_migration_error( WP_Error $error ) {
		$data = $error->get_error_data();
		$details = is_array( $data ) ? $data : array();

		update_option(
			self::LAST_ERROR_OPTION,
			array(
				'code'       => sanitize_key( $error->get_error_code() ),
				'message'    => sanitize_text_field( $error->get_error_message() ),
				'details'    => array_map( 'sanitize_text_field', array_filter( $details, 'is_scalar' ) ),
				'occurred_at' => current_time( 'mysql' ),
			),
			false
		);

		do_action( 'subin_database_migration_failed', $error );

		return $error;
	}

	/**
	 * Build a consistent database error without exposing it publicly.
	 *
	 * @param string $code Error code.
	 * @return WP_Error
	 */
	private static function database_error( $code ) {
		global $wpdb;

		return new WP_Error(
			sanitize_key( $code ),
			__( 'Submission Inbox could not complete a database migration.', 'submission-inbox' ),
			array( 'database_error' => sanitize_text_field( (string) $wpdb->last_error ) )
		);
	}

	/**
	 * Drop plugin tables.
	 *
	 * @return void
	 */
	public static function drop_tables() {
		global $wpdb;

		$tables = self::get_table_names();

		foreach ( $tables as $table ) {
			$wpdb->query( "DROP TABLE IF EXISTS {$table}" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		}
	}
}
