<?php
/**
 * Historical data sync.
 *
 * Imports existing WooCommerce orders to build customer profiles.
 *
 * @package TrustLens
 * @since   1.0.0
 */

defined( 'ABSPATH' ) || exit;

/**
 * Historical sync class.
 *
 * @since 1.0.0
 */
class TrustLens_Historical_Sync {

	/**
	 * Batch size for background processing.
	 *
	 * @var int
	 */
	const BATCH_SIZE = 100;

	/**
	 * Batch size for AJAX-driven processing (smaller for real-time progress).
	 *
	 * @var int
	 */
	const AJAX_BATCH_SIZE = 10;

	/**
	 * Option key for sync status.
	 *
	 * @var string
	 */
	const STATUS_OPTION = 'trustlens_sync_status';

	/**
	 * Single instance.
	 *
	 * @var TrustLens_Historical_Sync|null
	 */
	private static ?TrustLens_Historical_Sync $instance = null;

	/**
	 * Get instance.
	 *
	 * @since 1.0.0
	 * @return TrustLens_Historical_Sync
	 */
	public static function instance(): TrustLens_Historical_Sync {
		if ( null === self::$instance ) {
			self::$instance = new self();
		}
		return self::$instance;
	}

	/**
	 * Constructor.
	 *
	 * @since 1.0.0
	 */
	private function __construct() {
		$this->init_hooks();
	}

	/**
	 * Initialize hooks.
	 *
	 * @since 1.0.0
	 */
	private function init_hooks(): void {
		// Action Scheduler hooks.
		add_action( 'trustlens/sync_batch', array( $this, 'process_batch' ) );
		add_action( 'trustlens/sync_complete', array( $this, 'complete_sync' ) );
	}

	/**
	 * Get sync status.
	 *
	 * @since 1.0.0
	 * @return array Status data.
	 */
	public function get_status(): array {
		$default = array(
			'status'           => 'idle', // idle, running, completed, error.
			'total_orders'     => 0,
			'processed_orders' => 0,
			'total_customers'  => 0,
			'current_page'     => 0,
			'started_at'       => null,
			'completed_at'     => null,
			'error'            => null,
		);

		return wp_parse_args( get_option( self::STATUS_OPTION, array() ), $default );
	}

	/**
	 * Update sync status.
	 *
	 * @since 1.0.0
	 * @param array $data Status data to update.
	 */
	private function update_status( array $data ): void {
		$status = $this->get_status();
		$status = array_merge( $status, $data );
		update_option( self::STATUS_OPTION, $status, false );
	}

	/**
	 * Start the sync process.
	 *
	 * @since 1.0.0
	 * @param bool $schedule_background Whether to schedule background processing via Action Scheduler.
	 *                                  Pass false for AJAX-driven processing where the browser controls the loop.
	 * @return bool True if started, false if already running.
	 */
	public function start( bool $schedule_background = true ): bool {
		$status = $this->get_status();

		// Don't start if already running.
		if ( 'running' === $status['status'] ) {
			return false;
		}

		// Count total orders to process.
		$total_orders = $this->count_orders();

		if ( 0 === $total_orders ) {
			$this->update_status( array(
				'status'       => 'completed',
				'error'        => __( 'No orders found to sync.', 'trustlens' ),
				'completed_at' => current_time( 'mysql' ),
			) );
			return false;
		}

		// Initialize sync status.
		$this->update_status( array(
			'status'           => 'running',
			'total_orders'     => $total_orders,
			'processed_orders' => 0,
			'total_customers'  => 0,
			'current_page'     => 0,
			'started_at'       => current_time( 'mysql' ),
			'completed_at'     => null,
			'error'            => null,
		) );

		if ( $schedule_background ) {
			$this->schedule_batch( 1 );
		}

		return true;
	}

	/**
	 * Process a single batch via AJAX and return progress.
	 *
	 * Unlike process_batch() which schedules the next batch in the background,
	 * this method returns after one batch so the browser can update the progress
	 * bar and trigger the next batch.
	 *
	 * @since 1.0.0
	 * @return array Current sync status with progress percentage.
	 */
	public function process_ajax_batch(): array {
		$status = $this->get_status();

		if ( 'running' !== $status['status'] ) {
			return array_merge( $status, array( 'progress' => $this->get_progress() ) );
		}

		$page   = $status['current_page'] + 1;
		$orders = $this->get_orders( $page, self::AJAX_BATCH_SIZE );

		if ( empty( $orders ) ) {
			$this->complete_sync();
			return array_merge( $this->get_status(), array( 'progress' => 100 ) );
		}

		$new_customers_processed = $this->process_orders_for_batch( $orders );

		$processed       = $status['processed_orders'] + count( $orders );
		$total_customers = $status['total_customers'] + $new_customers_processed;

		$this->update_status( array(
			'processed_orders' => $processed,
			'total_customers'  => $total_customers,
			'current_page'     => $page,
		) );

		// Check if all orders have been processed.
		if ( $processed >= $status['total_orders'] ) {
			$this->complete_sync();
		}

		$new_status             = $this->get_status();
		$new_status['progress'] = $this->get_progress();
		return $new_status;
	}

	/**
	 * Stop the sync process.
	 *
	 * @since 1.0.0
	 */
	public function stop(): void {
		// Unschedule pending batches.
		if ( function_exists( 'as_unschedule_all_actions' ) ) {
			as_unschedule_all_actions( 'trustlens/sync_batch' );
		}

		$this->update_status( array(
			'status' => 'idle',
		) );
	}

	/**
	 * Reset sync and clear all data.
	 *
	 * @since 1.0.0
	 */
	public function reset(): void {
		$this->stop();

		wstl_reset_plugin_data();

		$this->update_status( array(
			'status'           => 'idle',
			'total_orders'     => 0,
			'processed_orders' => 0,
			'total_customers'  => 0,
			'current_page'     => 0,
			'started_at'       => null,
			'completed_at'     => null,
			'error'            => null,
		) );
	}

	/**
	 * Schedule a batch for processing.
	 *
	 * @since 1.0.0
	 * @param int $page Page number.
	 */
	private function schedule_batch( int $page ): void {
		if ( function_exists( 'wstl_ensure_single_action' ) ) {
			// Replace-semantics: collapses duplicate pending rows with the same
			// hook+args+group (race-free across concurrent callers).
			wstl_ensure_single_action( 'trustlens/sync_batch', array( 'page' => $page ), 'trustlens' );

			// Nudge WP-Cron to process the Action Scheduler queue promptly.
			// Without this, local/dev environments may never fire the job.
			if ( function_exists( 'spawn_cron' ) ) {
				spawn_cron();
			}
		} else {
			// Fallback: process immediately (not ideal for large stores).
			$this->process_batch( $page );
		}
	}

	/**
	 * Process a batch of orders.
	 *
	 * Wrapped in try/catch so a single malformed order can't leave the sync
	 * status hung at 'running' forever — on catastrophic failure we transition
	 * to 'failed' with a readable error so the merchant can retry from the UI.
	 *
	 * @since 1.0.0
	 * @param int $page Page number.
	 */
	public function process_batch( int $page ): void {
		try {
			$status = $this->get_status();

			// Verify still running.
			if ( 'running' !== $status['status'] ) {
				return;
			}

			// Get orders for this batch.
			$orders = $this->get_orders( $page, self::BATCH_SIZE );

			if ( empty( $orders ) ) {
				// No more orders, complete sync.
				if ( function_exists( 'wstl_ensure_single_action' ) ) {
					wstl_ensure_single_action( 'trustlens/sync_complete', array(), 'trustlens' );
				} else {
					$this->complete_sync();
				}
				return;
			}

			// Track unique customers in this batch.
			$new_customers_processed = $this->process_orders_for_batch( $orders );

			// Update status.
			$processed       = $status['processed_orders'] + count( $orders );
			$total_customers = $status['total_customers'] + $new_customers_processed;

			$this->update_status( array(
				'processed_orders' => $processed,
				'total_customers'  => $total_customers,
				'current_page'     => $page,
			) );

			// Schedule next batch.
			$this->schedule_batch( $page + 1 );
		} catch ( \Throwable $e ) {
			// Transition to a terminal failure state so the user can retry from
			// the UI instead of seeing "running" forever. No auto-reschedule —
			// we don't know if the failure is transient or poisonous to retry.
			$this->update_status( array(
				'status'       => 'failed',
				'error'        => $e->getMessage(),
				'completed_at' => current_time( 'mysql' ),
			) );
			// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- Operational log.
			error_log( sprintf(
				'[TrustLens] Historical sync batch %d failed: %s',
				$page,
				$e->getMessage()
			) );
		}
	}

	/**
	 * Sync a single customer from their orders.
	 *
	 * @since 1.0.0
	 * @param string   $email      Customer email.
	 * @param string   $email_hash Email hash.
	 * @param WC_Order $order      Sample order for customer data.
	 * @return bool True when a new customer row is inserted, false when updated.
	 */
	private function sync_customer( string $email, string $email_hash, WC_Order $order ): bool {
		global $wpdb;

		// Get customer stats from all their orders.
		$stats = $this->get_customer_stats( $email );

		// Determine customer type.
		$customer_id = $order->get_customer_id();
		$customer_type = $customer_id > 0 ? 'user' : 'guest';

		// Calculate return rate.
		$return_rate = $stats['total_orders'] > 0
			? ( $stats['total_refunds'] / $stats['total_orders'] ) * 100
			: 0;

		// Check if customer already exists.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix, safe.
		$existing = $wpdb->get_var( $wpdb->prepare(
			"SELECT id FROM {$wpdb->prefix}trustlens_customers WHERE email_hash = %s",
			$email_hash
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		$data = array(
			'email_hash'         => $email_hash,
			'customer_email'     => $email,
			'customer_id'        => $customer_id,
			'customer_type'      => $customer_type,
			'total_orders'       => $stats['total_orders'],
			'total_refunds'      => $stats['total_refunds'],
			'full_refunds'       => $stats['full_refunds'],
			'partial_refunds'    => $stats['partial_refunds'],
			'total_coupons_used' => $stats['total_coupons_used'],
			'first_order_coupons'=> $stats['first_order_coupons'],
			'coupon_then_refund' => $stats['coupon_then_refund'],
			'cancelled_orders'   => $stats['cancelled_orders'],
			'total_order_value'  => $stats['total_order_value'],
			'total_refund_value' => $stats['total_refund_value'],
			'return_rate'        => $return_rate,
			'first_order_date'   => $stats['first_order_date'],
			'last_order_date'    => $stats['last_order_date'],
			'updated_at'         => current_time( 'mysql' ),
		);

		if ( $existing ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Table name from $wpdb->prefix, safe.
			$wpdb->update(
				$wpdb->prefix . 'trustlens_customers',
				$data,
				array( 'email_hash' => $email_hash ),
				array( '%s', '%s', '%d', '%s', '%d', '%d', '%d', '%d', '%d', '%d', '%d', '%d', '%f', '%f', '%f', '%s', '%s', '%s' ),
				array( '%s' )
			);

			// Rebuild per-category order/refund aggregates from historical orders.
			$this->rebuild_customer_category_stats( $email_hash, $email );
			// Rebuild historical events timeline used by analytics/reports.
			$this->rebuild_customer_historical_events( $email_hash, $email );
			// Queue score calculation.
			wstl_queue_score_update( $email_hash );
			return false;
		} else {
			$data['created_at'] = current_time( 'mysql' );
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Table name from $wpdb->prefix, safe.
			$wpdb->insert(
				$wpdb->prefix . 'trustlens_customers',
				$data
			);

			// Rebuild per-category order/refund aggregates from historical orders.
			$this->rebuild_customer_category_stats( $email_hash, $email );
			// Rebuild historical events timeline used by analytics/reports.
			$this->rebuild_customer_historical_events( $email_hash, $email );
			// Queue score calculation.
			wstl_queue_score_update( $email_hash );
			return true;
		}
	}

	/**
	 * Process order IDs for a batch and sync each unique customer once.
	 *
	 * @since 1.1.0
	 * @param array $orders Order IDs.
	 * @return int Number of newly inserted customers.
	 */
	private function process_orders_for_batch( array $orders ): int {
		$new_customers_processed = 0;
		$customers_processed = array();
		$linked_accounts_enabled = class_exists( 'TrustLens_Linked_Accounts' ) && get_option( 'trustlens_module_linked_accounts_enabled', true );
		$brand_detector = class_exists( 'TrustLens_Card_Brand_Detector' ) ? new TrustLens_Card_Brand_Detector() : null;
		$brand_updates  = 0;

		foreach ( $orders as $order_id ) {
			$order = $this->resolve_sync_order( (int) $order_id );

			if ( ! $order ) {
				continue;
			}

			// Card-brand capture (Free) — runs per order regardless of customer
			// dedup so brand meta lands on every historical order, not just the
			// first one per customer. Powers the dashboard speedometer and the
			// Pro Chargeback Monitor's per-brand view.
			if ( null !== $brand_detector && 'updated' === $brand_detector->capture_historical_order( $order ) ) {
				++$brand_updates;
			}

			// Backfill linked-account fingerprints from historical orders.
			if ( $linked_accounts_enabled ) {
				// Ensure base customer row exists so linked_count/linked_accounts updates can persist.
				wstl_ensure_customer_record( wstl_get_customer_key( $order ), $order );
				TrustLens_Linked_Accounts::instance()->track_order_fingerprints( $order );
			}

			$email = $this->get_sync_order_email( $order );

			if ( empty( $email ) ) {
				continue;
			}

			$email_hash = wstl_get_email_hash( $email );

			if ( isset( $customers_processed[ $email_hash ] ) ) {
				continue;
			}

			if ( $this->sync_customer( $email, $email_hash, $order ) ) {
				$new_customers_processed++;
			}

			$customers_processed[ $email_hash ] = true;
		}

		// If the batch wrote any card-brand meta, invalidate the Chargeback
		// Monitor's ratio/trend cache so the next dashboard render reflects it.
		if ( $brand_updates > 0 && class_exists( 'TrustLens_Chargeback_Monitor' ) ) {
			TrustLens_Chargeback_Monitor::clear_ratios_cache();
		}

		return $new_customers_processed;
	}

	/**
	 * Resolve an order ID to a syncable WC_Order instance.
	 *
	 * Refund objects do not expose billing methods consistently across storage engines.
	 * For refunds, we always resolve to the parent order.
	 *
	 * @since 1.1.1
	 * @param int $order_id Order (or refund) ID.
	 * @return WC_Order|null
	 */
	private function resolve_sync_order( int $order_id ): ?WC_Order {
		$order = wc_get_order( $order_id );
		if ( ! $order ) {
			return null;
		}

		if ( is_a( $order, 'WC_Order_Refund' ) ) {
			$parent_id = (int) $order->get_parent_id();
			if ( $parent_id > 0 ) {
				$parent_order = wc_get_order( $parent_id );
				if ( $parent_order instanceof WC_Order ) {
					return $parent_order;
				}
			}
			return null;
		}

		return $order instanceof WC_Order ? $order : null;
	}

	/**
	 * Get billing email for sync from a resolved order object.
	 *
	 * @since 1.1.1
	 * @param WC_Order $order WooCommerce order.
	 * @return string
	 */
	private function get_sync_order_email( WC_Order $order ): string {
		if ( is_callable( array( $order, 'get_billing_email' ) ) ) {
			return sanitize_email( (string) $order->get_billing_email() );
		}

		return '';
	}

	/**
	 * Get aggregated stats for a customer from their orders.
	 *
	 * @since 1.0.0
	 * @param string $email Customer email.
	 * @return array Customer stats.
	 */
	private function get_customer_stats( string $email ): array {
		$stats = array(
			'total_orders'       => 0,
			'total_refunds'      => 0,
			'full_refunds'       => 0,
			'partial_refunds'    => 0,
			'total_coupons_used' => 0,
			'first_order_coupons'=> 0,
			'coupon_then_refund' => 0,
			'cancelled_orders'   => 0,
			'total_order_value'  => 0.0,
			'total_refund_value' => 0.0,
			'first_order_date'   => null,
			'last_order_date'    => null,
		);
		$first_order_coupon_count = 0;

		// Query all orders for this customer. Return objects directly so the
		// loop doesn't re-fetch each order one-by-one (N+1) via wc_get_order().
		$orders = wc_get_orders( array(
			'billing_email' => $email,
			'limit'         => -1,
			'return'        => 'objects',
			'type'          => 'shop_order',
			'status'        => array( 'completed', 'processing', 'refunded', 'cancelled' ),
		) );

		foreach ( $orders as $order ) {
			if ( ! $order instanceof WC_Order ) {
				continue;
			}

			$order_status = $order->get_status();
			$order_date = $order->get_date_created();
			$order_total = (float) $order->get_total();
			$coupon_count = count( $order->get_coupon_codes() );

			// Track dates.
			if ( $order_date ) {
				$date_string = $order_date->date( 'Y-m-d H:i:s' );
				if ( null === $stats['first_order_date'] || $date_string < $stats['first_order_date'] ) {
					$stats['first_order_date'] = $date_string;
					$first_order_coupon_count = $coupon_count;
				}
				if ( null === $stats['last_order_date'] || $date_string > $stats['last_order_date'] ) {
					$stats['last_order_date'] = $date_string;
				}
			}

			$stats['total_coupons_used'] += $coupon_count;

			// Count by status.
			if ( 'cancelled' === $order_status ) {
				$stats['cancelled_orders']++;
				continue; // Don't count cancelled in totals.
			}

			// Count completed/processing orders.
			if ( in_array( $order_status, array( 'completed', 'processing' ), true ) ) {
				$stats['total_orders']++;
				$stats['total_order_value'] += $order_total;
			}

			// Count refunds.
			if ( 'refunded' === $order_status ) {
				$stats['total_orders']++;
				$stats['total_order_value'] += $order_total;
			}

			// Check for refunds on this order.
			$refunds = $order->get_refunds();
			if ( ! empty( $refunds ) ) {
				$refund_total = 0.0;
				$refund_count = 0;
				foreach ( $refunds as $refund ) {
					$refund_total += abs( (float) $refund->get_total() );
					$refund_count++;
				}

				if ( $refund_total > 0 ) {
					$stats['total_refunds']++;
					$stats['total_refund_value'] += $refund_total;

					if ( $coupon_count > 0 ) {
						// Match runtime behavior where each refund action on coupon orders increments abuse count.
						$stats['coupon_then_refund'] += max( 1, $refund_count );
					}

					// Full vs partial refund.
					if ( $refund_total >= ( $order_total * 0.9 ) ) {
						$stats['full_refunds']++;
					} else {
						$stats['partial_refunds']++;
					}
				}
			}
		}

		$stats['first_order_coupons'] = $first_order_coupon_count;

		return $stats;
	}

	/**
	 * Rebuild category order/refund aggregates for a customer from historical orders.
	 *
	 * @since 1.1.2
	 * @param string $email_hash Customer email hash.
	 * @param string $email      Customer email.
	 */
	private function rebuild_customer_category_stats( string $email_hash, string $email ): void {
		global $wpdb;

		$table = $wpdb->prefix . 'trustlens_category_stats';
		if ( ! $this->table_exists( $table ) ) {
			return;
		}

		$orders = wc_get_orders( array(
			'billing_email' => $email,
			'limit'         => -1,
			'return'        => 'objects',
			'type'          => 'shop_order',
			'status'        => array( 'completed', 'processing', 'refunded', 'cancelled' ),
		) );

		$aggregates = array();

		foreach ( $orders as $order ) {
			if ( ! $order instanceof WC_Order ) {
				continue;
			}

			foreach ( $order->get_items() as $item ) {
				$product_id = (int) $item->get_product_id();
				if ( $product_id < 1 ) {
					continue;
				}

				$categories = wp_get_post_terms( $product_id, 'product_cat', array( 'fields' => 'slugs' ) );
				if ( empty( $categories ) || is_wp_error( $categories ) ) {
					continue;
				}

				$item_total = (float) $item->get_total();
				foreach ( $categories as $category_slug ) {
					if ( ! isset( $aggregates[ $category_slug ] ) ) {
						$aggregates[ $category_slug ] = array(
							'order_count'  => 0,
							'order_value'  => 0.0,
							'refund_count' => 0,
							'refund_value' => 0.0,
						);
					}
					$aggregates[ $category_slug ]['order_count']++;
					$aggregates[ $category_slug ]['order_value'] += $item_total;
				}
			}

			$refunds = $order->get_refunds();
			foreach ( $refunds as $refund ) {
				$per_refund_category = array();

				foreach ( $refund->get_items() as $refund_item ) {
					$product_id = (int) $refund_item->get_product_id();
					if ( $product_id < 1 ) {
						continue;
					}

					$categories = wp_get_post_terms( $product_id, 'product_cat', array( 'fields' => 'slugs' ) );
					if ( empty( $categories ) || is_wp_error( $categories ) ) {
						continue;
					}

					$refund_amount = abs( (float) $refund_item->get_total() );
					foreach ( $categories as $category_slug ) {
						if ( ! isset( $per_refund_category[ $category_slug ] ) ) {
							$per_refund_category[ $category_slug ] = 0.0;
						}
						$per_refund_category[ $category_slug ] += $refund_amount;
					}
				}

				foreach ( $per_refund_category as $category_slug => $refund_amount ) {
					if ( ! isset( $aggregates[ $category_slug ] ) ) {
						$aggregates[ $category_slug ] = array(
							'order_count'  => 0,
							'order_value'  => 0.0,
							'refund_count' => 0,
							'refund_value' => 0.0,
						);
					}
					$aggregates[ $category_slug ]['refund_count']++;
					$aggregates[ $category_slug ]['refund_value'] += $refund_amount;
				}
			}
		}

		// Replace existing per-customer category aggregates.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Internal maintenance write.
		$wpdb->delete( $table, array( 'email_hash' => $email_hash ), array( '%s' ) );

		foreach ( $aggregates as $category_slug => $stat ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Internal maintenance write.
			$wpdb->insert(
				$table,
				array(
					'email_hash'    => $email_hash,
					'category_slug' => $category_slug,
					'order_count'   => (int) $stat['order_count'],
					'order_value'   => (float) $stat['order_value'],
					'refund_count'  => (int) $stat['refund_count'],
					'refund_value'  => (float) $stat['refund_value'],
					'created_at'    => current_time( 'mysql' ),
					'updated_at'    => current_time( 'mysql' ),
				),
				array( '%s', '%s', '%d', '%f', '%d', '%f', '%s', '%s' )
			);
		}
	}

	/**
	 * Rebuild historical timeline events for a customer.
	 *
	 * This backfills analytics/reporting data from existing WooCommerce history
	 * and is idempotent for re-syncs (removes previously generated sync events first).
	 *
	 * @since 1.1.2
	 * @param string $email_hash Customer email hash.
	 * @param string $email      Customer email.
	 */
	private function rebuild_customer_historical_events( string $email_hash, string $email ): void {
		global $wpdb;

		$table = $wpdb->prefix . 'trustlens_events';
		if ( ! $this->table_exists( $table ) ) {
			return;
		}

		// Remove prior synthetic events generated by historical sync for this customer.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$wpdb->query( $wpdb->prepare(
			"DELETE FROM {$table}
			 WHERE email_hash = %s
			   AND event_data LIKE %s",
			$email_hash,
			'%"source":"historical_sync"%'
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		$orders = wc_get_orders( array(
			'billing_email' => $email,
			'limit'         => -1,
			'return'        => 'objects',
			'type'          => 'shop_order',
			'status'        => array( 'completed', 'processing', 'refunded', 'cancelled' ),
			'orderby'       => 'date',
			'order'         => 'ASC',
		) );

		$first_order_logged = false;

		foreach ( $orders as $order ) {
			if ( ! $order instanceof WC_Order ) {
				continue;
			}

			$order_id = (int) $order->get_id();
			$order_date = $this->format_wc_datetime( $order->get_date_created() );
			$order_total = (float) $order->get_total();
			$order_status = (string) $order->get_status();
			$coupons = $order->get_coupon_codes();

			$this->insert_historical_event(
				$email_hash,
				'order_created',
				array(
					'source'      => 'historical_sync',
					'order_id'    => $order_id,
					'order_total' => $order_total,
				),
				$order_id,
				$order_date
			);

			if ( 'completed' === $order_status ) {
				$this->insert_historical_event(
					$email_hash,
					'order_completed',
					array(
						'source'      => 'historical_sync',
						'order_id'    => $order_id,
						'order_total' => $order_total,
					),
					$order_id,
					$order_date
				);
			} elseif ( 'cancelled' === $order_status ) {
				$this->insert_historical_event(
					$email_hash,
					'order_cancelled',
					array(
						'source'      => 'historical_sync',
						'order_id'    => $order_id,
						'order_total' => $order_total,
					),
					$order_id,
					$order_date
				);
			}

			if ( ! empty( $coupons ) ) {
				$this->insert_historical_event(
					$email_hash,
					'coupon_used',
					array(
						'source'         => 'historical_sync',
						'order_id'       => $order_id,
						'coupons'        => $coupons,
						'discount_total' => (float) $order->get_discount_total(),
						'is_first_order' => ! $first_order_logged,
					),
					$order_id,
					$order_date
				);
			}

			$first_order_logged = true;

			foreach ( $order->get_refunds() as $refund ) {
				$refund_id = (int) $refund->get_id();
				$refund_date = $this->format_wc_datetime( $refund->get_date_created() );
				$refund_total = abs( (float) $refund->get_total() );
				$is_full_refund = ( $order_total > 0 ) ? ( $refund_total >= ( $order_total * 0.9 ) ) : false;

				$order_timestamp = $order->get_date_created() ? $order->get_date_created()->getTimestamp() : current_time( 'timestamp' );
				$refund_timestamp = $refund->get_date_created() ? $refund->get_date_created()->getTimestamp() : current_time( 'timestamp' );
				$days_since_order = max( 0, wstl_days_between( $order_timestamp, $refund_timestamp ) );

				$this->insert_historical_event(
					$email_hash,
					$is_full_refund ? 'full_refund' : 'partial_refund',
					array(
						'source'        => 'historical_sync',
						'order_id'      => $order_id,
						'refund_id'     => $refund_id,
						'refund_amount' => $refund_total,
						'order_total'   => $order_total,
						'is_full_refund'=> $is_full_refund,
						'refund_reason' => (string) $refund->get_reason(),
						'days_since_order' => $days_since_order,
					),
					$order_id,
					$refund_date
				);

				if ( ! empty( $coupons ) ) {
					$this->insert_historical_event(
						$email_hash,
						'coupon_refund',
						array(
							'source'    => 'historical_sync',
							'order_id'  => $order_id,
							'refund_id' => $refund_id,
							'coupons'   => $coupons,
						),
						$order_id,
						$refund_date
					);
				}
			}
		}
	}

	/**
	 * Insert a synthetic historical event row.
	 *
	 * @since 1.1.2
	 * @param string $email_hash Customer email hash.
	 * @param string $event_type Event type.
	 * @param array  $event_data Event payload.
	 * @param int    $order_id   Related order ID.
	 * @param string $created_at Event datetime in MySQL format.
	 */
	private function insert_historical_event( string $email_hash, string $event_type, array $event_data, int $order_id, string $created_at ): void {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Internal maintenance insert.
		$wpdb->insert(
			$wpdb->prefix . 'trustlens_events',
			array(
				'email_hash' => $email_hash,
				'event_type' => $event_type,
				'event_data' => wp_json_encode( $event_data ),
				'order_id'   => $order_id,
				'created_at' => $created_at,
			),
			array( '%s', '%s', '%s', '%d', '%s' )
		);
	}

	/**
	 * Convert WC_DateTime to local MySQL datetime string.
	 *
	 * @since 1.1.2
	 * @param WC_DateTime|null $datetime WooCommerce datetime.
	 * @return string
	 */
	private function format_wc_datetime( $datetime ): string {
		if ( $datetime instanceof WC_DateTime ) {
			return $datetime->date( 'Y-m-d H:i:s' );
		}

		return current_time( 'mysql' );
	}

	/**
	 * Check whether a database table exists.
	 *
	 * @since 1.1.2
	 * @param string $table_name Full table name.
	 * @return bool
	 */
	private function table_exists( string $table_name ): bool {
		global $wpdb;
		static $cache = array();

		if ( isset( $cache[ $table_name ] ) ) {
			return (bool) $cache[ $table_name ];
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- SHOW TABLES check, no user input.
		$result = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name ) );
		$exists = ( $result === $table_name );
		$cache[ $table_name ] = $exists;
		return $exists;
	}

	/**
	 * Complete the sync process.
	 *
	 * @since 1.0.0
	 */
	public function complete_sync(): void {
		$total_customers = $this->get_profiled_customer_count();
		$status = $this->get_status();
		$this->update_status( array(
			'status'           => 'completed',
			'processed_orders' => max( (int) $status['processed_orders'], (int) $status['total_orders'] ),
			'total_customers'  => $total_customers,
			'completed_at'     => current_time( 'mysql' ),
		) );

		// Clear caches.
		wstl_clear_plugin_transients();

		/**
		 * Fires when historical sync is completed.
		 *
		 * @since 1.0.0
		 * @param array $status Final sync status.
		 */
		do_action( 'trustlens/sync_completed', $this->get_status() );
	}

	/**
	 * Count profiled customers in TrustLens table.
	 *
	 * @since 1.1.3
	 * @return int
	 */
	private function get_profiled_customer_count(): int {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Aggregate read on internal table.
		$count = $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->prefix}trustlens_customers" );
		return (int) $count;
	}

	/**
	 * Count total orders to process.
	 *
	 * @since 1.0.0
	 * @return int Order count.
	 */
	private function count_orders(): int {
		$query = wc_get_orders( array(
			'limit'    => 1,
			'return'   => 'ids',
			'paginate' => true,
			'type'     => 'shop_order',
			'status'   => array( 'completed', 'processing', 'refunded', 'cancelled' ),
		) );

		if ( is_object( $query ) && isset( $query->total ) ) {
			return (int) $query->total;
		}

		// Fallback for environments that don't return paginated object as expected.
		if ( is_array( $query ) ) {
			return count( $query );
		}

		return 0;
	}

	/**
	 * Get orders for a specific page.
	 *
	 * @since 1.0.0
	 * @param int $page     Page number.
	 * @param int $per_page Orders per page.
	 * @return array Order IDs.
	 */
	private function get_orders( int $page, int $per_page ): array {
		return wc_get_orders( array(
			'limit'   => $per_page,
			'page'    => $page,
			'return'  => 'ids',
			'type'    => 'shop_order',
			'status'  => array( 'completed', 'processing', 'refunded', 'cancelled' ),
			'orderby' => 'ID',
			'order'   => 'ASC',
		) );
	}

	/**
	 * Get progress percentage.
	 *
	 * @since 1.0.0
	 * @return int Progress 0-100.
	 */
	public function get_progress(): int {
		$status = $this->get_status();

		if ( $status['total_orders'] < 1 ) {
			return 0;
		}

		return min( 100, (int) round( ( $status['processed_orders'] / $status['total_orders'] ) * 100 ) );
	}
}
