<?php
/**
 * Database migration runner.
 *
 * Uses dbDelta for idempotent table creation and versioned migrations
 * to evolve the schema across plugin updates.
 *
 * @package AICommerce
 */

declare(strict_types=1);

namespace AICommerce\Core;

/**
 * Manages custom database tables and schema migrations.
 */
class Database {

	/**
	 * WordPress database abstraction.
	 *
	 * @var \wpdb
	 */
	private \wpdb $wpdb;

	/**
	 * Option key that stores the currently-installed DB schema version.
	 *
	 * @var string
	 */
	private const DB_VERSION_OPTION = 'aicommerce_db_version';

	/**
	 * Constructor.
	 *
	 * @param \wpdb $wpdb WordPress database instance.
	 */
	public function __construct( \wpdb $wpdb ) {
		$this->wpdb = $wpdb;
	}

	/**
	 * Run all pending migrations.
	 *
	 * Called on every admin_init; returns early if schema is already current.
	 *
	 * @return void
	 */
	public function migrate(): void {
		$installed_version = get_option( self::DB_VERSION_OPTION, '0.0.0' );

		if ( version_compare( $installed_version, AICOMMERCE_DB_VERSION, '>=' ) ) {
			return;
		}

		$this->run_migrations( $installed_version );

		update_option( self::DB_VERSION_OPTION, AICOMMERCE_DB_VERSION, false );
	}

	/**
	 * Execute migrations that are newer than the installed version.
	 *
	 * Each migration method is named `migrate_to_{version}` with dots replaced
	 * by underscores. They are called in ascending version order.
	 *
	 * @param string $from_version The currently installed version.
	 *
	 * @return void
	 */
	private function run_migrations( string $from_version ): void {
		$migrations = $this->get_migrations();

		foreach ( $migrations as $version => $method ) {
			if ( version_compare( $from_version, $version, '<' ) ) {
				$this->{$method}();
			}
		}
	}

	/**
	 * Return the ordered list of migration versions and their methods.
	 *
	 * @return array<string, string> Version => method name.
	 */
	private function get_migrations(): array {
		return array(
			'1.0.0' => 'migrate_to_1_0_0',
			'1.1.0' => 'migrate_to_1_1_0',
			'1.2.0' => 'migrate_to_1_2_0',
			'1.3.0' => 'migrate_to_1_3_0',
			'1.4.0' => 'migrate_to_1_4_0',
			'1.5.0' => 'migrate_to_1_5_0',
			'1.6.0' => 'migrate_to_1_6_0',
		);
	}

	/**
	 * Migration: 1.0.0 — Create initial custom tables.
	 *
	 * @return void
	 */
	private function migrate_to_1_0_0(): void {
		$charset_collate = $this->wpdb->get_charset_collate();
		$prefix          = $this->wpdb->prefix;

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

		// Feed tracking table.
		$sql_feeds = "CREATE TABLE {$prefix}aicommerce_feeds (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			feed_type varchar(50) NOT NULL,
			status varchar(20) NOT NULL DEFAULT 'pending',
			file_path varchar(500) NOT NULL DEFAULT '',
			file_url varchar(500) NOT NULL DEFAULT '',
			product_count bigint(20) unsigned NOT NULL DEFAULT 0,
			file_hash varchar(64) NOT NULL DEFAULT '',
			error_message text,
			generated_at datetime DEFAULT NULL,
			created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY feed_type_status (feed_type, status)
		) {$charset_collate};";

		// Crawler visit log table.
		$sql_crawler_log = "CREATE TABLE {$prefix}aicommerce_crawler_log (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			bot_name varchar(100) NOT NULL,
			user_agent varchar(500) NOT NULL DEFAULT '',
			request_url varchar(2048) NOT NULL,
			request_method varchar(10) NOT NULL DEFAULT 'GET',
			response_code smallint unsigned NOT NULL DEFAULT 200,
			visited_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY bot_name_visited (bot_name, visited_at),
			KEY visited_at (visited_at)
		) {$charset_collate};";

		// AI-attributed revenue tracking.
		$sql_attribution = "CREATE TABLE {$prefix}aicommerce_attribution (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			order_id bigint(20) unsigned NOT NULL,
			referrer_source varchar(100) NOT NULL,
			referrer_url varchar(2048) NOT NULL DEFAULT '',
			landing_url varchar(2048) NOT NULL DEFAULT '',
			session_id varchar(64) NOT NULL DEFAULT '',
			revenue decimal(12,4) NOT NULL DEFAULT 0.0000,
			currency varchar(3) NOT NULL DEFAULT 'USD',
			attributed_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY order_id (order_id),
			KEY referrer_source (referrer_source),
			KEY attributed_at (attributed_at)
		) {$charset_collate};";

		// Agent transaction audit log.
		$sql_agent_log = "CREATE TABLE {$prefix}aicommerce_agent_log (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			agent_id varchar(255) NOT NULL,
			agent_platform varchar(100) NOT NULL DEFAULT '',
			action varchar(100) NOT NULL,
			request_payload longtext,
			response_payload longtext,
			order_id bigint(20) unsigned DEFAULT NULL,
			status varchar(20) NOT NULL DEFAULT 'success',
			ip_address varchar(45) NOT NULL DEFAULT '',
			created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY agent_platform_action (agent_platform, action),
			KEY order_id (order_id),
			KEY created_at (created_at)
		) {$charset_collate};";

		// Readiness score cache.
		$sql_readiness = "CREATE TABLE {$prefix}aicommerce_readiness_cache (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			product_id bigint(20) unsigned NOT NULL,
			overall_score tinyint unsigned NOT NULL DEFAULT 0,
			schema_score tinyint unsigned NOT NULL DEFAULT 0,
			content_score tinyint unsigned NOT NULL DEFAULT 0,
			feed_score tinyint unsigned NOT NULL DEFAULT 0,
			crawler_score tinyint unsigned NOT NULL DEFAULT 0,
			agentic_score tinyint unsigned NOT NULL DEFAULT 0,
			details longtext,
			calculated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			UNIQUE KEY product_id (product_id),
			KEY overall_score (overall_score)
		) {$charset_collate};";

		dbDelta( $sql_feeds );
		dbDelta( $sql_crawler_log );
		dbDelta( $sql_attribution );
		dbDelta( $sql_agent_log );
		dbDelta( $sql_readiness );
	}

	/**
	 * Migration: 1.1.0 — Add product cache table for feed change detection.
	 *
	 * @return void
	 */
	private function migrate_to_1_1_0(): void {
		$charset_collate = $this->wpdb->get_charset_collate();
		$prefix          = $this->wpdb->prefix;

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

		$sql_product_cache = "CREATE TABLE {$prefix}aicommerce_product_cache (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			product_id bigint(20) unsigned NOT NULL,
			data_hash varchar(64) NOT NULL,
			feed_data longtext,
			last_synced_at datetime DEFAULT NULL,
			created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			UNIQUE KEY product_id (product_id),
			KEY data_hash (data_hash),
			KEY last_synced_at (last_synced_at)
		) {$charset_collate};";

		dbDelta( $sql_product_cache );
	}

	/**
	 * Migration: 1.2.0 — Add feed_type column for per-feed dirty tracking.
	 *
	 * Drops the old UNIQUE constraint on product_id alone and replaces it
	 * with a composite unique on (product_id, feed_type).
	 *
	 * @return void
	 */
	private function migrate_to_1_2_0(): void {
		$prefix = $this->wpdb->prefix;
		$table  = $prefix . 'aicommerce_product_cache';

		// Add feed_type column if it doesn't exist.
		// Safe: internal $wpdb->prefix table names; user values use placeholders.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$column_exists = $this->wpdb->get_var(
			$this->wpdb->prepare(
				'SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = %s AND COLUMN_NAME = %s',
				$table,
				'feed_type'
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		if ( '0' === $column_exists || 0 === (int) $column_exists ) {
			// Safe: internal $wpdb->prefix table names; user values use placeholders.
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$this->wpdb->query( "ALTER TABLE {$table} ADD COLUMN feed_type varchar(50) NOT NULL DEFAULT '' AFTER product_id" );
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

			// Drop old unique key and add composite unique.
			// Safe: internal $wpdb->prefix table names; user values use placeholders.
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$this->wpdb->query( "ALTER TABLE {$table} DROP INDEX product_id" );
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

			// Safe: internal $wpdb->prefix table names; user values use placeholders.
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$this->wpdb->query( "ALTER TABLE {$table} ADD UNIQUE KEY product_feed (product_id, feed_type)" );
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		}
	}

	/**
	 * Migration: 1.3.0 — Update crawler_log table with new columns.
	 *
	 * Adds bot_family, ip_address, page_type, and request_time columns.
	 * Drops the old visited_at column in favor of request_time.
	 *
	 * @return void
	 */
	private function migrate_to_1_3_0(): void {
		$prefix = $this->wpdb->prefix;
		$table  = $prefix . 'aicommerce_crawler_log';

		// Add bot_family column if it doesn't exist.
		// Safe: internal $wpdb->prefix table names; user values use placeholders.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$bot_family_exists = $this->wpdb->get_var(
			$this->wpdb->prepare(
				'SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = %s AND COLUMN_NAME = %s',
				$table,
				'bot_family'
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		if ( '0' === $bot_family_exists || 0 === (int) $bot_family_exists ) {
			// Safe: internal $wpdb->prefix table names; user values use placeholders.
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$this->wpdb->query( "ALTER TABLE {$table} ADD COLUMN bot_family varchar(50) NOT NULL DEFAULT '' AFTER bot_name" );
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		}

		// Add ip_address column if it doesn't exist.
		// Safe: internal $wpdb->prefix table names; user values use placeholders.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$ip_exists = $this->wpdb->get_var(
			$this->wpdb->prepare(
				'SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = %s AND COLUMN_NAME = %s',
				$table,
				'ip_address'
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		if ( '0' === $ip_exists || 0 === (int) $ip_exists ) {
			// Safe: internal $wpdb->prefix table names; user values use placeholders.
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$this->wpdb->query( "ALTER TABLE {$table} ADD COLUMN ip_address varchar(45) NOT NULL DEFAULT '' AFTER user_agent" );
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		}

		// Add page_type column if it doesn't exist.
		// Safe: internal $wpdb->prefix table names; user values use placeholders.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$page_type_exists = $this->wpdb->get_var(
			$this->wpdb->prepare(
				'SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = %s AND COLUMN_NAME = %s',
				$table,
				'page_type'
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		if ( '0' === $page_type_exists || 0 === (int) $page_type_exists ) {
			// Safe: internal $wpdb->prefix table names; user values use placeholders.
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$this->wpdb->query( "ALTER TABLE {$table} ADD COLUMN page_type varchar(50) NOT NULL DEFAULT '' AFTER request_url" );
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		}

		// Add request_time column if it doesn't exist.
		// Safe: internal $wpdb->prefix table names; user values use placeholders.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$request_time_exists = $this->wpdb->get_var(
			$this->wpdb->prepare(
				'SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = %s AND COLUMN_NAME = %s',
				$table,
				'request_time'
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		if ( '0' === $request_time_exists || 0 === (int) $request_time_exists ) {
			// Safe: internal $wpdb->prefix table names; user values use placeholders.
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$this->wpdb->query( "ALTER TABLE {$table} ADD COLUMN request_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER page_type" );
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

			// Copy data from visited_at to request_time.
			// Safe: internal $wpdb->prefix table names; user values use placeholders.
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$this->wpdb->query( "UPDATE {$table} SET request_time = visited_at" );
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

			// Add index on bot_family + request_time.
			// Safe: internal $wpdb->prefix table names; user values use placeholders.
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$this->wpdb->query( "ALTER TABLE {$table} ADD KEY bot_family_time (bot_family, request_time)" );
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		}
	}

	/**
	 * Migration: 1.4.0 — Create bulk optimization job tracking tables.
	 *
	 * @return void
	 */
	private function migrate_to_1_4_0(): void {
		$charset_collate = $this->wpdb->get_charset_collate();
		$prefix          = $this->wpdb->prefix;

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

		$sql_bulk_jobs = "CREATE TABLE {$prefix}aicommerce_bulk_jobs (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			batch_id varchar(36) NOT NULL,
			status varchar(20) NOT NULL DEFAULT 'pending',
			total_products int(11) NOT NULL DEFAULT 0,
			processed_products int(11) NOT NULL DEFAULT 0,
			failed_products int(11) NOT NULL DEFAULT 0,
			provider varchar(20) NOT NULL,
			options longtext DEFAULT NULL,
			cost_estimate_usd decimal(10,4) DEFAULT 0.0000,
			actual_cost_usd decimal(10,4) DEFAULT 0.0000,
			started_at datetime DEFAULT NULL,
			completed_at datetime DEFAULT NULL,
			error_log longtext DEFAULT NULL,
			created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			UNIQUE KEY batch_id (batch_id),
			KEY status_created (status, created_at)
		) {$charset_collate};";

		$sql_bulk_product_status = "CREATE TABLE {$prefix}aicommerce_bulk_product_status (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			batch_id varchar(36) NOT NULL,
			product_id bigint(20) unsigned NOT NULL,
			status varchar(20) NOT NULL DEFAULT 'pending',
			error_message text DEFAULT NULL,
			processed_at datetime DEFAULT NULL,
			PRIMARY KEY (id),
			UNIQUE KEY batch_product (batch_id, product_id),
			KEY batch_status (batch_id, status)
		) {$charset_collate};";

		dbDelta( $sql_bulk_jobs );
		dbDelta( $sql_bulk_product_status );
	}

	/**
	 * Migration: 1.5.0 — Create agent checkout sessions table.
	 *
	 * @return void
	 */
	private function migrate_to_1_5_0(): void {
		$charset_collate = $this->wpdb->get_charset_collate();
		$prefix          = $this->wpdb->prefix;

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

		$sql = "CREATE TABLE {$prefix}aicommerce_agent_sessions (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			session_id varchar(64) NOT NULL,
			stripe_session_id varchar(100) DEFAULT NULL,
			agent_identity varchar(100) DEFAULT NULL,
			status varchar(20) NOT NULL DEFAULT 'pending',
			cart_items longtext NOT NULL,
			total_amount decimal(10,2) DEFAULT NULL,
			currency varchar(10) DEFAULT NULL,
			checkout_url varchar(1000) DEFAULT NULL,
			wc_order_id bigint(20) unsigned DEFAULT NULL,
			ip_address varchar(45) DEFAULT NULL,
			expires_at datetime NOT NULL,
			created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			UNIQUE KEY session_id (session_id),
			KEY stripe_session (stripe_session_id),
			KEY status_expires (status, expires_at),
			KEY wc_order (wc_order_id)
		) {$charset_collate};";

		dbDelta( $sql );
	}

	/**
	 * Migration: 1.6.0 — Create Share-of-Answer tracking tables.
	 *
	 * @return void
	 */
	private function migrate_to_1_6_0(): void {
		$charset_collate = $this->wpdb->get_charset_collate();
		$prefix          = $this->wpdb->prefix;

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

		$sql_results = "CREATE TABLE {$prefix}aicommerce_soa_results (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			query_text varchar(500) NOT NULL,
			query_category varchar(100) DEFAULT NULL,
			platform varchar(50) NOT NULL,
			own_store_mentioned tinyint(1) NOT NULL DEFAULT 0,
			competitor_mentions longtext DEFAULT NULL,
			response_snippet varchar(500) DEFAULT NULL,
			checked_at datetime NOT NULL,
			PRIMARY KEY (id),
			KEY platform_date (platform, checked_at),
			KEY category_date (query_category, checked_at),
			KEY own_mention (own_store_mentioned, checked_at)
		) {$charset_collate};";

		$sql_daily = "CREATE TABLE {$prefix}aicommerce_soa_daily (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			date date NOT NULL,
			platform varchar(50) NOT NULL,
			queries_run int DEFAULT 0,
			own_mentions int DEFAULT 0,
			share_of_answer decimal(5,2) DEFAULT 0,
			PRIMARY KEY (id),
			UNIQUE KEY date_platform (date, platform),
			KEY date (date)
		) {$charset_collate};";

		$sql_competitors = "CREATE TABLE {$prefix}aicommerce_soa_competitors (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			competitor_domain varchar(200) NOT NULL,
			competitor_name varchar(200) DEFAULT NULL,
			is_active tinyint(1) DEFAULT 1,
			added_at datetime DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			UNIQUE KEY domain (competitor_domain)
		) {$charset_collate};";

		dbDelta( $sql_results );
		dbDelta( $sql_daily );
		dbDelta( $sql_competitors );
	}

	/**
	 * Get a table name with the WP prefix applied.
	 *
	 * @param string $table Unprefixed table name (e.g., 'aicommerce_feeds').
	 *
	 * @return string Full table name with prefix.
	 */
	public function table( string $table ): string {
		return $this->wpdb->prefix . $table;
	}

	/**
	 * Drop all plugin tables.
	 *
	 * Called only on uninstall via uninstall.php.
	 *
	 * @return void
	 */
	public function drop_tables(): void {
		$tables = array(
			'aicommerce_feeds',
			'aicommerce_crawler_log',
			'aicommerce_attribution',
			'aicommerce_agent_log',
			'aicommerce_readiness_cache',
			'aicommerce_product_cache',
			'aicommerce_bulk_jobs',
			'aicommerce_bulk_product_status',
			'aicommerce_agent_sessions',
			'aicommerce_soa_results',
			'aicommerce_soa_daily',
			'aicommerce_soa_competitors',
		);

		foreach ( $tables as $table ) {
			// Safe: internal $wpdb->prefix table names; user values use placeholders.
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$this->wpdb->query(
				"DROP TABLE IF EXISTS {$this->wpdb->prefix}{$table}"
			);
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		}

		delete_option( self::DB_VERSION_OPTION );
	}
}