<?php
/**
 * Installation and database setup.
 *
 * @package TrustLens
 * @since   1.0.0
 */

defined( 'ABSPATH' ) || exit;

/**
 * TrustLens installation class.
 *
 * @since 1.0.0
 */
class TrustLens_Install {

	/**
	 * Check whether this site can execute Pro functionality.
	 *
	 * Kept local to install flow so activation can enforce safe defaults
	 * without depending on includes/functions.php load order.
	 *
	 * @since 1.1.5
	 * @return bool True when a paid Freemius plan is active.
	 */
	private static function can_use_pro_features(): bool {
		if ( ! function_exists( 'wstl_fs' ) ) {
			return false;
		}

		$fs = wstl_fs();
		if ( ! is_object( $fs ) || ! method_exists( $fs, 'is_not_paying' ) ) {
			return false;
		}

		return ! $fs->is_not_paying();
	}

	/**
	 * Run on plugin activation.
	 *
	 * @since 1.0.0
	 */
	public static function activate(): void {
		self::create_tables();
		self::create_options();
		self::schedule_events();
		self::schedule_one_time_backfills();

		// Schedule the Store Trust Network sync only when it is enabled + licensed.
		if ( class_exists( 'TrustLens_Network_Sync' ) && function_exists( 'wstl_can_use_network' )
			&& get_option( 'trustlens_module_store_network_enabled', false ) && wstl_can_use_network() ) {
			TrustLens_Network_Sync::schedule();
		}

		// Schedule welcome summary email (24 hours after activation).
		if ( ! get_option( 'trustlens_welcome_summary_sent' ) ) {
			wp_schedule_single_event( time() + DAY_IN_SECONDS, 'trustlens/welcome_summary' );
		}

		// Flush rewrite rules.
		flush_rewrite_rules();
	}

	/**
	 * Run on plugin deactivation.
	 *
	 * @since 1.0.0
	 */
	public static function deactivate(): void {
		self::unschedule_events();

		// Unschedule scheduled reports.
		if ( class_exists( 'TrustLens_Scheduled_Reports' ) ) {
			TrustLens_Scheduled_Reports::unschedule_reports();
		}

		// Unschedule automation-log retention.
		if ( class_exists( 'TrustLens_Automation_Retention_Cron' ) ) {
			TrustLens_Automation_Retention_Cron::unschedule();
		}

		// Cancel any queued webhook dispatches so they don't fire after the
		// engine has been deactivated.
		if ( class_exists( 'TrustLens_Automation' ) ) {
			TrustLens_Automation::unschedule_pending_webhooks();
		}

		// Stop the Store Trust Network sync.
		if ( class_exists( 'TrustLens_Network_Sync' ) ) {
			TrustLens_Network_Sync::unschedule();
		}

		flush_rewrite_rules();
	}

	/**
	 * Create database tables.
	 *
	 * @since 1.0.0
	 */
	public static function create_tables(): void {
		global $wpdb;

		// Always run the lightweight column migrations — they're idempotent
		// and cover installs that were activated before a column was added.
		// The heavy dbDelta block below is fast-path skipped when the version
		// matches AND every expected table is present.
		$installed_version = get_option( 'trustlens_db_version' );
		if ( $installed_version === TRUSTLENS_DB_VERSION && self::all_expected_tables_exist() ) {
			self::ensure_customer_columns();
			self::ensure_hash_column_support();
			return;
		}

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

		$charset_collate = $wpdb->get_charset_collate();

		// Customers table.
		$table_customers = $wpdb->prefix . 'trustlens_customers';
		$sql_customers = "CREATE TABLE {$table_customers} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			email_hash varchar(64) NOT NULL,
			customer_id bigint(20) unsigned DEFAULT NULL,
			customer_email varchar(255) DEFAULT NULL,
			customer_type enum('user','guest') DEFAULT 'guest',
			trust_score tinyint(3) unsigned DEFAULT 50,
			segment enum('vip','trusted','normal','caution','risk','critical') DEFAULT 'normal',
			total_orders int(10) unsigned DEFAULT 0,
			total_order_value decimal(12,2) DEFAULT 0.00,
			total_refunds int(10) unsigned DEFAULT 0,
			total_refund_value decimal(12,2) DEFAULT 0.00,
			full_refunds int(10) unsigned DEFAULT 0,
			partial_refunds int(10) unsigned DEFAULT 0,
			return_rate decimal(5,2) DEFAULT 0.00,
			total_coupons_used int(10) unsigned DEFAULT 0,
			first_order_coupons int(10) unsigned DEFAULT 0,
			coupon_then_refund int(10) unsigned DEFAULT 0,
			total_disputes int(10) unsigned DEFAULT 0,
			disputes_won int(10) unsigned DEFAULT 0,
			disputes_lost int(10) unsigned DEFAULT 0,
			linked_accounts int(10) unsigned DEFAULT 0,
			cancelled_orders int(10) unsigned DEFAULT 0,
			order_edits int(10) unsigned DEFAULT 0,
			reviews_before_refund int(10) unsigned DEFAULT 0,
			shipping_address_hash varchar(64) DEFAULT NULL,
			last_ip varchar(45) DEFAULT NULL,
			is_blocked tinyint(1) DEFAULT 0,
			is_allowlisted tinyint(1) DEFAULT 0,
			admin_notes text DEFAULT NULL,
			first_order_date datetime DEFAULT NULL,
			last_order_date datetime DEFAULT NULL,
			last_refund_date datetime DEFAULT NULL,
			score_updated_at datetime DEFAULT NULL,
			created_at datetime DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			UNIQUE KEY idx_email_hash (email_hash),
			KEY idx_customer_id (customer_id),
			KEY idx_segment (segment),
			KEY idx_trust_score (trust_score),
			KEY idx_return_rate (return_rate),
			KEY idx_shipping_hash (shipping_address_hash),
			KEY idx_is_blocked (is_blocked)
		) $charset_collate;";

		dbDelta( $sql_customers );

		// Events table.
		$table_events = $wpdb->prefix . 'trustlens_events';
		$sql_events = "CREATE TABLE {$table_events} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			email_hash varchar(64) NOT NULL,
			event_type varchar(50) NOT NULL,
			event_data longtext DEFAULT NULL,
			order_id bigint(20) unsigned DEFAULT NULL,
			created_at datetime DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY idx_email_hash (email_hash),
			KEY idx_event_type (event_type),
			KEY idx_order_id (order_id),
			KEY idx_created_at (created_at)
		) $charset_collate;";

		dbDelta( $sql_events );

		// Signals table.
		$table_signals = $wpdb->prefix . 'trustlens_signals';
		$sql_signals = "CREATE TABLE {$table_signals} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			email_hash varchar(64) NOT NULL,
			module_id varchar(50) NOT NULL,
			signal_score smallint(6) DEFAULT 0,
			signal_reason varchar(255) DEFAULT NULL,
			created_at datetime DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY idx_email_hash (email_hash),
			KEY idx_module_id (module_id),
			KEY idx_module_created (module_id, created_at)
		) $charset_collate;";

		dbDelta( $sql_signals );

		// Category stats table.
		// Note: category_slug capped at 100 chars (was 200) so the
		// (email_hash + category_slug) unique index fits within the InnoDB
		// 767-byte row limit on older MySQL configs (64 + 100 chars × 4 bytes
		// utf8mb4 = 656 bytes). WooCommerce term slugs are well under 100.
		$table_category_stats = $wpdb->prefix . 'trustlens_category_stats';
		$sql_category_stats = "CREATE TABLE {$table_category_stats} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			email_hash varchar(64) NOT NULL,
			category_slug varchar(100) NOT NULL,
			order_count int(10) unsigned DEFAULT 0,
			order_value decimal(12,2) DEFAULT 0.00,
			refund_count int(10) unsigned DEFAULT 0,
			refund_value decimal(12,2) DEFAULT 0.00,
			created_at datetime DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			UNIQUE KEY idx_email_category (email_hash, category_slug),
			KEY idx_email_hash (email_hash),
			KEY idx_category_slug (category_slug)
		) $charset_collate;";

		dbDelta( $sql_category_stats );

		// Fingerprints table — used by linked-accounts detection *and* by
		// card-testing defense. Linked-accounts rows have (email_hash, type='canvas'|…)
		// tuples; card-testing rows use sentinels (email_hash='', type='tl_device_session')
		// so one row exists per unique device hash regardless of email.
		$table_fingerprints = $wpdb->prefix . 'trustlens_fingerprints';
		$sql_fingerprints = "CREATE TABLE {$table_fingerprints} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			email_hash varchar(64) NOT NULL,
			fingerprint_type varchar(50) NOT NULL,
			fingerprint_hash varchar(64) NOT NULL,
			first_seen datetime DEFAULT CURRENT_TIMESTAMP,
			last_seen datetime DEFAULT CURRENT_TIMESTAMP,
			times_seen int(10) unsigned DEFAULT 1,
			country_code varchar(2) DEFAULT NULL,
			fp_source varchar(16) NOT NULL DEFAULT 'browser',
			decline_count_24h int(10) unsigned NOT NULL DEFAULT 0,
			taint_flag tinyint(1) NOT NULL DEFAULT 0,
			taint_reason varchar(64) DEFAULT NULL,
			tainted_at datetime DEFAULT NULL,
			PRIMARY KEY (id),
			UNIQUE KEY idx_unique_fingerprint (email_hash, fingerprint_type, fingerprint_hash),
			KEY idx_email_hash (email_hash),
			KEY idx_fingerprint_type (fingerprint_type),
			KEY idx_fingerprint_hash (fingerprint_hash),
			KEY idx_taint_flag (taint_flag)
		) $charset_collate;";

		dbDelta( $sql_fingerprints );

		// Webhook logs table.
		$table_webhook_logs = $wpdb->prefix . 'trustlens_webhook_logs';
		$sql_webhook_logs = "CREATE TABLE {$table_webhook_logs} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			endpoint_id varchar(36) DEFAULT NULL,
			endpoint_url varchar(500) NOT NULL,
			event varchar(50) NOT NULL,
			payload longtext DEFAULT NULL,
			response_code smallint(3) unsigned DEFAULT NULL,
			response_body text DEFAULT NULL,
			created_at datetime DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY idx_endpoint_id (endpoint_id),
			KEY idx_event (event),
			KEY idx_created_at (created_at)
		) $charset_collate;";

		dbDelta( $sql_webhook_logs );

		// Automation logs table. `status` / `duration_ms` / `error_message`
		// columns added in 1.5.0 to support the run-history inspector UI.
		$table_automation_logs = $wpdb->prefix . 'trustlens_automation_logs';
		$sql_automation_logs = "CREATE TABLE {$table_automation_logs} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			rule_id varchar(36) NOT NULL,
			rule_name varchar(255) DEFAULT NULL,
			email_hash varchar(64) NOT NULL,
			trigger_type varchar(50) NOT NULL,
			action_type varchar(50) NOT NULL,
			order_id bigint(20) unsigned DEFAULT NULL,
			status varchar(16) NOT NULL DEFAULT 'ok',
			duration_ms int(10) unsigned DEFAULT NULL,
			error_message text DEFAULT NULL,
			created_at datetime DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY idx_rule_id (rule_id),
			KEY idx_email_hash (email_hash),
			KEY idx_created_at (created_at),
			KEY idx_status (status),
			KEY idx_rule_created (rule_id, created_at)
		) $charset_collate;";

		dbDelta( $sql_automation_logs );

		// Velocity events table (card-testing defense, 1.4.0+). Append-only log of
		// submission / decline / gate-block events keyed by fingerprint, queried by
		// rolling window. Purged daily by retention cron.
		$table_velocity = $wpdb->prefix . 'trustlens_velocity_events';
		$sql_velocity   = "CREATE TABLE {$table_velocity} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			fingerprint_hash varchar(64) NOT NULL,
			event_type varchar(32) NOT NULL,
			ip varbinary(16) DEFAULT NULL,
			email_hash varchar(64) DEFAULT NULL,
			decline_code varchar(64) DEFAULT NULL,
			order_id bigint(20) unsigned DEFAULT NULL,
			created_at datetime NOT NULL,
			PRIMARY KEY (id),
			KEY idx_fp_created (fingerprint_hash, created_at),
			KEY idx_created (created_at),
			KEY idx_event_type_created (event_type, created_at)
		) $charset_collate;";

		dbDelta( $sql_velocity );

		// Disputes table (chargeback worklist). One row per gateway dispute,
		// keyed on the gateway's dispute_id for idempotent webhook upserts.
		// dispute_id capped at 191 chars so the UNIQUE index fits the InnoDB
		// 767-byte limit under utf8mb4 (191 × 4 = 764 bytes).
		$table_disputes = $wpdb->prefix . 'trustlens_disputes';
		$sql_disputes = "CREATE TABLE {$table_disputes} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			dispute_id varchar(191) NOT NULL,
			order_id bigint(20) unsigned DEFAULT NULL,
			email_hash varchar(64) NOT NULL,
			gateway varchar(20) NOT NULL DEFAULT 'manual',
			reason varchar(50) DEFAULT NULL,
			amount decimal(12,2) DEFAULT 0.00,
			currency varchar(3) DEFAULT NULL,
			status varchar(30) NOT NULL DEFAULT 'needs_response',
			is_open tinyint(1) NOT NULL DEFAULT 1,
			outcome varchar(10) DEFAULT NULL,
			due_by datetime DEFAULT NULL,
			opened_at datetime DEFAULT CURRENT_TIMESTAMP,
			closed_at datetime DEFAULT NULL,
			updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			UNIQUE KEY idx_dispute_id (dispute_id),
			KEY idx_email_hash (email_hash),
			KEY idx_order_id (order_id),
			KEY idx_open_due (is_open, due_by)
		) $charset_collate;";

		dbDelta( $sql_disputes );

		// Store Trust Network flags (Pro, 1.3.5+). One row per (customer group-hash,
		// reporting store). is_local=1 rows are THIS store's own contributions (served
		// to peers); is_local=0 rows are pulled from peers and drive cross-store scoring.
		$table_network = $wpdb->prefix . 'trustlens_network_flags';
		$sql_network   = "CREATE TABLE {$table_network} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			group_hash varchar(64) NOT NULL,
			level varchar(10) NOT NULL DEFAULT 'caution',
			reason varchar(100) DEFAULT NULL,
			source_site varchar(191) NOT NULL DEFAULT 'self',
			is_local tinyint(1) NOT NULL DEFAULT 0,
			updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			created_at datetime DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			UNIQUE KEY idx_group_source (group_hash, source_site),
			KEY idx_group (group_hash),
			KEY idx_local_updated (is_local, updated_at)
		) $charset_collate;";

		dbDelta( $sql_network );

		self::ensure_customer_columns();
		self::ensure_hash_column_support();

		// Update version.
		update_option( 'trustlens_db_version', TRUSTLENS_DB_VERSION );
	}

	/**
	 * Add every expected column to the customers table if it's missing.
	 *
	 * Idempotent — safe to call on both fresh installs (after dbDelta) and
	 * upgrade paths (when the fast-path version gate short-circuits dbDelta).
	 *
	 * @since 1.2.0
	 */
	private static function ensure_customer_columns(): void {
		global $wpdb;

		$table_customers = $wpdb->prefix . 'trustlens_customers';
		self::add_column_if_missing( $table_customers, 'linked_count', 'int(10) unsigned DEFAULT 0 AFTER linked_accounts' );
		self::add_column_if_missing( $table_customers, 'tags', 'text DEFAULT NULL AFTER admin_notes' );

		// First-class "flagged for review" state (1.5.1) — the customer-level
		// form of the automation flag_review action, surfaced + filterable in the
		// Customers screen alongside is_blocked / is_allowlisted.
		self::add_column_if_missing( $table_customers, 'is_flagged', 'tinyint(1) DEFAULT 0 AFTER is_allowlisted' );
		self::add_index_if_missing( $table_customers, 'idx_is_flagged', 'is_flagged' );

		// trustlens_automation_logs columns added in 1.5.0 — idempotent for
		// installs that already ran `dbDelta` on the older schema.
		$table_automation_logs = $wpdb->prefix . 'trustlens_automation_logs';
		self::add_column_if_missing( $table_automation_logs, 'status',        "varchar(16) NOT NULL DEFAULT 'ok' AFTER order_id" );
		self::add_column_if_missing( $table_automation_logs, 'duration_ms',   'int(10) unsigned DEFAULT NULL AFTER status' );
		self::add_column_if_missing( $table_automation_logs, 'error_message', 'text DEFAULT NULL AFTER duration_ms' );

		// Composite index for the inspector queries (all filter by rule_id +
		// a created_at range). With only the two single-column indexes MySQL
		// picks one and scans the other; this covers both in one seek.
		self::add_index_if_missing( $table_automation_logs, 'idx_rule_created', 'rule_id, created_at' );
	}

	/**
	 * Add column to table if it doesn't exist.
	 *
	 * @since 1.1.0
	 * @param string $table  Table name.
	 * @param string $column Column name.
	 * @param string $definition Column definition.
	 */
	private static function add_column_if_missing( string $table, string $column, string $definition ): void {
		global $wpdb;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- SHOW COLUMNS for schema check, table name from trusted internal array.
		$column_exists = $wpdb->get_results( $wpdb->prepare(
			"SHOW COLUMNS FROM {$table} LIKE %s",
			$column
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		if ( empty( $column_exists ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- ALTER TABLE for schema migration, variables are from trusted internal array.
			$wpdb->query( "ALTER TABLE {$table} ADD COLUMN {$column} {$definition}" );
		}
	}

	/**
	 * Add a secondary index to a table if it doesn't already exist. Mirrors
	 * add_column_if_missing() but keyed on the index name — dbDelta can miss
	 * composite-index additions on upgrade paths where the table already
	 * exists with the same columns.
	 *
	 * @since 1.2.2
	 * @param string $table      Fully-qualified table name.
	 * @param string $index_name Target index name.
	 * @param string $columns    Column expression, e.g. 'rule_id, created_at'.
	 */
	private static function add_index_if_missing( string $table, string $index_name, string $columns ): void {
		global $wpdb;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- SHOW INDEX for schema check; table from trusted internal array.
		$exists = $wpdb->get_results( $wpdb->prepare(
			"SHOW INDEX FROM {$table} WHERE Key_name = %s",
			$index_name
		) );
		// phpcs:enable

		if ( empty( $exists ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- ALTER TABLE for schema migration; identifiers from trusted internal array.
			$wpdb->query( "ALTER TABLE {$table} ADD INDEX {$index_name} ({$columns})" );
		}
	}

	/**
	 * Check whether every plugin-owned table currently exists.
	 *
	 * Used to bypass the version-only fast path in create_tables() so that
	 * installs which already match the current DB version still get any
	 * tables that were added in later releases.
	 *
	 * @since 1.2.0
	 * @return bool True when all expected tables exist.
	 */
	private static function all_expected_tables_exist(): bool {
		global $wpdb;

		$expected = array(
			'trustlens_customers',
			'trustlens_events',
			'trustlens_signals',
			'trustlens_category_stats',
			'trustlens_fingerprints',
			'trustlens_webhook_logs',
			'trustlens_automation_logs',
			'trustlens_velocity_events',
			'trustlens_disputes',
			'trustlens_network_flags',
		);

		foreach ( $expected as $name ) {
			$table = $wpdb->prefix . $name;
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- SHOW TABLES check, table from $wpdb->prefix.
			if ( ! $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) ) {
				return false;
			}
		}

		return true;
	}

	/**
	 * Ensure hash columns support the keyed HMAC-SHA256 format.
	 *
	 * @since 1.1.5
	 * @return void
	 */
	public static function ensure_hash_column_support(): void {
		global $wpdb;

		$columns = array(
			$wpdb->prefix . 'trustlens_customers'      => array(
				'email_hash'            => 'varchar(64) NOT NULL',
				'shipping_address_hash' => 'varchar(64) DEFAULT NULL',
			),
			$wpdb->prefix . 'trustlens_events'         => array(
				'email_hash' => 'varchar(64) NOT NULL',
			),
			$wpdb->prefix . 'trustlens_signals'        => array(
				'email_hash' => 'varchar(64) NOT NULL',
			),
			$wpdb->prefix . 'trustlens_category_stats' => array(
				'email_hash' => 'varchar(64) NOT NULL',
			),
			$wpdb->prefix . 'trustlens_fingerprints'   => array(
				'email_hash'       => 'varchar(64) NOT NULL',
				'fingerprint_hash' => 'varchar(64) NOT NULL',
			),
			$wpdb->prefix . 'trustlens_automation_logs' => array(
				'email_hash' => 'varchar(64) NOT NULL',
			),
		);

		foreach ( $columns as $table => $table_columns ) {
			foreach ( $table_columns as $column => $definition ) {
				self::modify_column_if_needed( $table, $column, $definition );
			}
		}
	}

	/**
	 * Modify a column if it still uses the old hash width.
	 *
	 * @since 1.1.5
	 * @param string $table      Table name.
	 * @param string $column     Column name.
	 * @param string $definition Target column definition.
	 * @return void
	 */
	private static function modify_column_if_needed( string $table, string $column, string $definition ): void {
		global $wpdb;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- SHOW COLUMNS for schema inspection, table names from trusted internal definitions.
		$column_data = $wpdb->get_row( $wpdb->prepare(
			"SHOW COLUMNS FROM {$table} LIKE %s",
			$column
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		if ( ! $column_data || false !== strpos( strtolower( (string) $column_data->Type ), 'varchar(64)' ) ) {
			return;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- ALTER TABLE for controlled plugin schema migration.
		$wpdb->query( "ALTER TABLE {$table} MODIFY COLUMN {$column} {$definition}" );
	}

	/**
	 * Create default options.
	 *
	 * @since 1.0.0
	 */
	public static function create_options(): void {
		// Defaults come from the single canonical source (wstl_setting_defaults())
		// so seeding, the settings forms, and runtime consumers never drift.
		// Defensive: during activation the normal loader hasn't run, so make sure
		// the helper is available before we call it.
		if ( ! function_exists( 'wstl_setting_defaults' ) ) {
			require_once dirname( __DIR__ ) . '/includes/functions.php';
		}
		$defaults = array_merge(
			wstl_setting_defaults(),
			array( 'trustlens_installed_timestamp' => time() )
		);

		foreach ( $defaults as $key => $value ) {
			if ( get_option( $key ) === false ) {
				update_option( $key, $value );
			}
		}
	}

	/**
	 * Schedule recurring events.
	 *
	 * @since 1.0.0
	 */
	public static function schedule_events(): void {
		// Schedule daily cleanup.
		if ( ! wp_next_scheduled( 'trustlens/daily_cleanup' ) ) {
			wp_schedule_event( time(), 'daily', 'trustlens/daily_cleanup' );
		}

		// Pro-only: daily digest and monthly report (handlers in class-notifications-pro.php).
		$has_pro_notifications = file_exists( TRUSTLENS_PLUGIN_DIR . 'includes/class-notifications-pro.php' ) && self::can_use_pro_features();
		if ( $has_pro_notifications ) {
			if ( ! wp_next_scheduled( 'trustlens/daily_digest' ) ) {
				$tomorrow_7am = strtotime( 'tomorrow 7:00am' );
				wp_schedule_event( $tomorrow_7am, 'daily', 'trustlens/daily_digest' );
			}
			if ( ! wp_next_scheduled( 'trustlens/monthly_report' ) ) {
				$first_of_month = strtotime( 'first day of next month 8:00am' );
				wp_schedule_event( $first_of_month, 'monthly', 'trustlens/monthly_report' );
			}
		}

		// Schedule weekly summary (Monday 7 AM) — free notification.
		if ( ! wp_next_scheduled( 'trustlens/weekly_summary' ) ) {
			$next_monday = strtotime( 'next monday 7:00am' );
			wp_schedule_event( $next_monday, 'weekly', 'trustlens/weekly_summary' );
		}

		// Pro-only: daily chargeback ratio alert check. The Monitor class itself
		// ships with Free as of 1.2.1, so gating here is license-only.
		if ( self::can_use_pro_features() && ! wp_next_scheduled( 'trustlens/chargeback_alert_check' ) ) {
			wp_schedule_event( time() + HOUR_IN_SECONDS, 'daily', 'trustlens/chargeback_alert_check' );
		}

		// Pro-only: hourly retry of dispute-report attestations that failed to
		// register (e.g. the verification service was briefly unreachable), so a
		// printed verify link/QR self-heals without reopening the report.
		if ( self::can_use_pro_features() && ! wp_next_scheduled( 'trustlens/attestation_retry' ) ) {
			wp_schedule_event( time() + HOUR_IN_SECONDS, 'hourly', 'trustlens/attestation_retry' );
		}
	}

	/**
	 * Unschedule events on deactivation.
	 *
	 * @since 1.0.0
	 */
	public static function unschedule_events(): void {
		$events = array(
			'trustlens/daily_cleanup',
			'trustlens/daily_digest',
			'trustlens/weekly_summary',
			'trustlens/monthly_report',
			'trustlens/welcome_summary',
			'trustlens/chargeback_alert_check',
			'trustlens/attestation_retry',
			'trustlens/card_testing/retention_cleanup',
			'trustlens/card_testing/alert_send',
			'trustlens/automation/retention_cleanup',
		);

		foreach ( $events as $event ) {
			wp_clear_scheduled_hook( $event );
		}
	}

	/**
	 * Backfill the disputes table from pre-1.3.0 dispute_created events.
	 *
	 * Idempotent: skips any dispute_id already present. A dispute is marked
	 * closed when a later dispute_closed event exists for the same dispute_id.
	 * Legacy events never captured a deadline, so backfilled rows have due_by = NULL.
	 *
	 * @since 1.3.0
	 * @return int Rows inserted.
	 */
	public static function backfill_disputes(): int {
		global $wpdb;

		if ( ! class_exists( 'TrustLens_Dispute_Repository' ) ) {
			require_once TRUSTLENS_PLUGIN_DIR . 'includes/class-dispute-repository.php';
		}

		$events = $wpdb->prefix . 'trustlens_events';
		$repo   = new TrustLens_Dispute_Repository();

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table from $wpdb->prefix; value prepared.
		$rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT email_hash, event_data, order_id FROM {$events} WHERE event_type = %s ORDER BY id ASC",
			'dispute_created'
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		if ( empty( $rows ) ) {
			return 0;
		}

		$inserted = 0;
		foreach ( $rows as $row ) {
			$data       = json_decode( (string) $row->event_data, true );
			$dispute_id = is_array( $data ) && ! empty( $data['dispute_id'] ) ? (string) $data['dispute_id'] : '';
			if ( '' === $dispute_id || $repo->get_dispute( $dispute_id ) ) {
				continue;
			}

			$repo->upsert_dispute( array(
				'dispute_id' => $dispute_id,
				'order_id'   => (int) $row->order_id,
				'email_hash' => (string) $row->email_hash,
				'gateway'    => isset( $data['source'] ) ? (string) $data['source'] : 'manual',
				'reason'     => isset( $data['reason'] ) ? (string) $data['reason'] : null,
				'amount'     => isset( $data['amount'] ) ? (float) $data['amount'] : 0.0,
				'currency'   => isset( $data['currency'] ) ? (string) $data['currency'] : null,
				'status'     => isset( $data['status'] ) ? (string) $data['status'] : 'needs_response',
				'due_by'     => null, // Legacy events never captured a deadline.
			) );

			// Close it if a resolution event exists for this dispute_id.
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table from $wpdb->prefix; value prepared.
			$closed = $wpdb->get_var( $wpdb->prepare(
				"SELECT event_data FROM {$events} WHERE event_type = %s AND event_data LIKE %s ORDER BY id DESC LIMIT 1",
				'dispute_closed',
				'%' . $wpdb->esc_like( $dispute_id ) . '%'
			) );
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			if ( $closed ) {
				$cdata = json_decode( (string) $closed, true );
				$won   = is_array( $cdata ) && ! empty( $cdata['won'] );
				$repo->mark_closed( $dispute_id, $won ? 'won' : 'lost', 'closed' );
			}

			$inserted++;
		}

		return $inserted;
	}

	/**
	 * Check if database needs upgrade.
	 *
	 * @since 1.0.0
	 */
	public static function maybe_upgrade(): void {
		$installed_version = get_option( 'trustlens_db_version' );

		if ( $installed_version !== TRUSTLENS_DB_VERSION ) {
			self::create_tables();
			self::backfill_disputes();
			self::schedule_one_time_backfills();
			self::migrate_option_autoload();
		}

		// Always ensure hash columns are wide enough for SHA-256 (64 chars).
		// This handles sites where create_tables() ran but the column migration was skipped.
		self::ensure_hash_column_support();
	}

	/**
	 * Flip large, contextually-read options off autoload.
	 *
	 * `trustlens_automation_rules` and `trustlens_report_schedules` are read only
	 * when a trigger fires or from cron/admin — never on a typical request — yet
	 * were stored with the default autoload='yes', loading the whole blob into the
	 * alloptions cache on every page load. New writes now pass autoload=false;
	 * this flips the flag on existing rows during upgrade. wp_set_option_autoload()
	 * is WP 6.4+ (our minimum); guarded for safety.
	 *
	 * @since 1.3.8
	 * @return void
	 */
	private static function migrate_option_autoload(): void {
		if ( ! function_exists( 'wp_set_option_autoload' ) ) {
			return;
		}
		foreach ( array( 'trustlens_automation_rules', 'trustlens_report_schedules' ) as $option ) {
			if ( false !== get_option( $option, false ) ) {
				wp_set_option_autoload( $option, false );
			}
		}
	}

	/**
	 * Schedule the one-time backfill actions that run on activation / upgrade.
	 *
	 * Inlined here (rather than delegating to the owning module classes) because
	 * this runs inside the install/upgrade path, which may execute before the
	 * main plugin classes are loaded on `plugins_loaded:10`. Each scheduler uses
	 * replace-semantics via `wstl_ensure_single_action()` so repeated calls —
	 * fresh activation, silent update, manual re-activation — always collapse
	 * into exactly one pending row per hook.
	 *
	 * **Deferred-to-init safety**: Action Scheduler's procedural API (`as_*`)
	 * short-circuits with a `_doing_it_wrong` warning unless
	 * `$data_store_initialized` is true, which AS sets during an `init:1`
	 * callback. `maybe_upgrade()` runs on `plugins_loaded:5`, which is earlier
	 * than that — so if we're called before `action_scheduler_init` has fired,
	 * we defer until it has. The activation path (`register_activation_hook`)
	 * always runs after `init`, so it executes the body immediately.
	 *
	 * Fixed in 1.1.9: replaces the runaway-prone admin_init scheduler and the
	 * raw `as_enqueue_async_action()` calls that caused unbounded fan-out.
	 * See BUG-ACTIONSCHEDULER-RUNAWAY.md for background.
	 *
	 * Must be `public` so it can serve as the `action_scheduler_init` callback.
	 *
	 * @since 1.1.9
	 */
	public static function schedule_one_time_backfills(): void {
		// Defer until Action Scheduler's procedural API is safe to use.
		// action_scheduler_init fires on init:1 after AS flips its
		// $data_store_initialized flag. Checking did_action() lets us run
		// inline when we're already past init (activation path) and defer
		// when we're running early (upgrade-from-plugins_loaded path).
		if ( ! did_action( 'action_scheduler_init' ) ) {
			add_action( 'action_scheduler_init', array( __CLASS__, 'schedule_one_time_backfills' ) );
			return;
		}

		// The helper lives in functions.php; load it if we're running earlier
		// than wstl_init() (e.g. during activation request before plugins_loaded:10).
		if ( ! function_exists( 'wstl_ensure_single_action' ) ) {
			require_once TRUSTLENS_PLUGIN_DIR . 'includes/functions.php';
		}

		// Shipping-anomalies country-code backfill (added in 1.2.0).
		if ( ! get_option( 'trustlens_country_code_backfill_done', false ) ) {
			wstl_ensure_single_action( 'trustlens/backfill_address_country_codes', array(), 'trustlens' );
		}

		// Email-hash backfill (added in 1.2.0).
		if ( ! get_option( 'trustlens_email_hash_backfill_done', false ) ) {
			wstl_ensure_single_action( 'trustlens/backfill_email_hash_meta', array(), 'trustlens' );
		}

		// Return-rate reconcile (1.3.5). Versions before this stored return_rate with
		// an off-by-one (counter double-counted in the same UPDATE). The formula is
		// fixed going forward, but existing rows stay wrong until each customer next
		// transacts — this one-shot pass recomputes them all with the canonical
		// total_refunds / total_orders * 100.
		if ( ! get_option( 'trustlens_return_rate_reconcile_done', false ) ) {
			wstl_ensure_single_action( 'trustlens/reconcile_return_rates', array(), 'trustlens' );
		}
	}

	/**
	 * One-shot reconcile of stored return_rate values (fixes the pre-1.3.5
	 * off-by-one). A single arithmetic UPDATE across the customers table; runs
	 * once via Action Scheduler and then sets its done flag. updated_at is
	 * preserved (set to itself) so this correction does not mark every customer
	 * as freshly updated.
	 *
	 * @since 1.3.5
	 * @return void
	 */
	public static function reconcile_return_rates(): void {
		global $wpdb;

		$table = $wpdb->prefix . 'trustlens_customers';
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- One-time data migration; table name from $wpdb->prefix, no user input.
		$wpdb->query(
			"UPDATE {$table}
			 SET return_rate = CASE
			         WHEN total_orders > 0 THEN ( total_refunds / total_orders ) * 100
			         ELSE 0
			     END,
			     updated_at = updated_at"
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		update_option( 'trustlens_return_rate_reconcile_done', true );

		if ( function_exists( 'wstl_invalidate_customer_caches' ) ) {
			wstl_invalidate_customer_caches();
		}
	}
}

// Check for database upgrades on plugins_loaded.
add_action( 'plugins_loaded', array( 'TrustLens_Install', 'maybe_upgrade' ), 5 );

// One-shot return-rate reconcile worker (scheduled by schedule_one_time_backfills()).
add_action( 'trustlens/reconcile_return_rates', array( 'TrustLens_Install', 'reconcile_return_rates' ) );
