<?php
/**
 * Analytics and reporting.
 *
 * @package TrustLens
 * @since   1.1.0
 */

defined( 'ABSPATH' ) || exit;

/**
 * Analytics class.
 *
 * Provides data for charts and reports.
 *
 * @since 1.1.0
 */
class TrustLens_Analytics {

	/**
	 * Check if a table exists in the database.
	 *
	 * @since 1.2.0
	 * @param string $table Table name (without prefix).
	 * @return bool True if table exists.
	 */
	private static function table_exists( string $table ): bool {
		global $wpdb;

		$full_table = $wpdb->prefix . $table;
		// 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', $full_table ) );

		return $result === $full_table;
	}

	/**
	 * Get segment distribution.
	 *
	 * @since 1.1.0
	 * @return array Segment counts.
	 */
	public static function get_segment_distribution(): array {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Simple aggregate query, table name from $wpdb->prefix is safe.
		$results = $wpdb->get_results(
			"SELECT segment, COUNT(*) as count
			 FROM {$wpdb->prefix}trustlens_customers
			 GROUP BY segment"
		);

		$segments = array(
			'vip'      => 0,
			'trusted'  => 0,
			'normal'   => 0,
			'caution'  => 0,
			'risk'     => 0,
			'critical' => 0,
		);

		foreach ( $results as $row ) {
			if ( isset( $segments[ $row->segment ] ) ) {
				$segments[ $row->segment ] = (int) $row->count;
			}
		}

		return $segments;
	}

	/**
	 * Get trust score trends over time.
	 *
	 * @since 1.1.0
	 * @param int $days Number of days to look back.
	 * @return array Daily averages.
	 */
	public static function get_score_trends( int $days = 30 ): array {
		global $wpdb;

		// Use only score_update signals so we show actual 0-100 trust scores, not module deltas.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix, safe.
		$results = $wpdb->get_results( $wpdb->prepare(
			"SELECT
				DATE(created_at) as date,
				AVG(signal_score) as avg_score,
				COUNT(DISTINCT email_hash) as customers
			 FROM {$wpdb->prefix}trustlens_signals
			 WHERE module_id = 'score_update'
			   AND created_at >= DATE_SUB(%s, INTERVAL %d DAY)
			 GROUP BY DATE(created_at)
			 ORDER BY date ASC",
			current_time( 'mysql' ),
			$days
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		$data = array();
		foreach ( $results as $row ) {
			$data[] = array(
				'date'      => $row->date,
				'avg_score' => round( (float) $row->avg_score, 1 ),
				'customers' => (int) $row->customers,
			);
		}

		return $data;
	}

	/**
	 * Get refund trends over time.
	 *
	 * @since 1.1.0
	 * @param int $days Number of days to look back.
	 * @return array Daily refund data.
	 */
	public static function get_refund_trends( int $days = 30 ): array {
		global $wpdb;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix, safe.
		$results = $wpdb->get_results( $wpdb->prepare(
			"SELECT
				DATE(created_at) as date,
				COUNT(*) as refund_count,
				SUM(CASE WHEN event_type = 'full_refund' THEN 1 ELSE 0 END) as full_refunds,
				SUM(CASE WHEN event_type = 'partial_refund' THEN 1 ELSE 0 END) as partial_refunds
			 FROM {$wpdb->prefix}trustlens_events
			 WHERE event_type IN ('full_refund', 'partial_refund')
			   AND created_at >= DATE_SUB(%s, INTERVAL %d DAY)
			 GROUP BY DATE(created_at)
			 ORDER BY date ASC",
			current_time( 'mysql' ),
			$days
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		$data = array();
		foreach ( $results as $row ) {
			$data[] = array(
				'date'            => $row->date,
				'total'           => (int) $row->refund_count,
				'full_refunds'    => (int) $row->full_refunds,
				'partial_refunds' => (int) $row->partial_refunds,
			);
		}

		return $data;
	}

	/**
	 * Get segment changes over time.
	 *
	 * @since 1.1.0
	 * @param int $days Number of days to look back.
	 * @return array Segment change data.
	 */
	public static function get_segment_changes( int $days = 30 ): array {
		global $wpdb;

		$segment_like = $wpdb->esc_like( 'segment_' ) . '%';

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix, safe.
		$results = $wpdb->get_results( $wpdb->prepare(
			"SELECT
				DATE(created_at) as date,
				event_type,
				COUNT(*) as count
			 FROM {$wpdb->prefix}trustlens_events
			 WHERE event_type LIKE %s
			   AND created_at >= DATE_SUB(%s, INTERVAL %d DAY)
			 GROUP BY DATE(created_at), event_type
			 ORDER BY date ASC",
			$segment_like,
			current_time( 'mysql' ),
			$days
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		return $results;
	}

	/**
	 * Get top returners.
	 *
	 * @since 1.1.0
	 * @param int $limit Number of customers to return.
	 * @return array Top returners.
	 */
	public static function get_top_returners( int $limit = 10 ): array {
		global $wpdb;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix, safe.
		return $wpdb->get_results( $wpdb->prepare(
			"SELECT
				email_hash,
				customer_email,
				total_orders,
				total_refunds,
				return_rate,
				total_refund_value,
				segment,
				trust_score
			 FROM {$wpdb->prefix}trustlens_customers
			 WHERE total_refunds > 0
			 ORDER BY return_rate DESC, total_refund_value DESC
			 LIMIT %d",
			$limit
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	/**
	 * Get summary statistics.
	 *
	 * @since 1.1.0
	 * @return array Summary stats.
	 */
	public static function get_summary_stats(): array {
		global $wpdb;

		$customers_table = $wpdb->prefix . 'trustlens_customers';
		$events_table = $wpdb->prefix . 'trustlens_events';

		// Customer stats.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Aggregate stats query, table name from $wpdb->prefix is safe.
		$customer_stats = $wpdb->get_row(
			"SELECT
				COUNT(*) as total_customers,
				AVG(trust_score) as avg_trust_score,
				AVG(return_rate) as avg_return_rate,
				SUM(is_blocked) as blocked_count,
				SUM(is_allowlisted) as allowlisted_count,
				SUM(total_orders) as total_orders,
				SUM(total_refunds) as total_refunds,
				SUM(total_order_value) as total_order_value,
				SUM(total_refund_value) as total_refund_value
			 FROM {$customers_table}"
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		// Recent activity (last 24 hours).
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix, prepared query.
		$recent_activity = $wpdb->get_row( $wpdb->prepare(
			"SELECT
				COUNT(*) as events_24h,
				COUNT(DISTINCT email_hash) as active_customers_24h
			 FROM {$events_table}
			 WHERE created_at >= DATE_SUB(%s, INTERVAL %d HOUR)",
			current_time( 'mysql' ),
			24
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		// New high-risk customers this week.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix, prepared query.
		$new_high_risk = $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(*)
			 FROM {$customers_table}
			 WHERE segment IN ('risk', 'critical')
			   AND created_at >= DATE_SUB(%s, INTERVAL %d DAY)",
			current_time( 'mysql' ),
			7
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		return array(
			'total_customers'       => (int) $customer_stats->total_customers,
			'avg_trust_score'       => round( (float) $customer_stats->avg_trust_score, 1 ),
			'avg_return_rate'       => round( (float) $customer_stats->avg_return_rate, 1 ),
			'blocked_count'         => (int) $customer_stats->blocked_count,
			'allowlisted_count'     => (int) $customer_stats->allowlisted_count,
			'total_orders'          => (int) $customer_stats->total_orders,
			'total_refunds'         => (int) $customer_stats->total_refunds,
			'total_order_value'     => (float) $customer_stats->total_order_value,
			'total_refund_value'    => (float) $customer_stats->total_refund_value,
			'events_24h'            => (int) $recent_activity->events_24h,
			'active_customers_24h'  => (int) $recent_activity->active_customers_24h,
			'new_high_risk_week'    => (int) $new_high_risk,
		);
	}

	/**
	 * Live activity telemetry for the Command Center "Live Telemetry" card.
	 *
	 * Each metric carries its current value, the prior-period value (so the card
	 * can show movement), and the window it covers. Events and Orders also get a
	 * rolling-24h hourly series (index 0 = ~24h ago … 23 = current hour) for
	 * inline sparklines. Direct, un-cached queries to match the sibling methods.
	 *
	 * @since 1.2.7
	 * @return array
	 */
	public static function get_live_telemetry(): array {
		global $wpdb;

		$events_table    = $wpdb->prefix . 'trustlens_events';
		$customers_table = $wpdb->prefix . 'trustlens_customers';

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names from $wpdb->prefix; all literals.

		// Anchor every window to WP-local "now" so it matches how created_at is
		// stored (current_time( 'mysql' )). MySQL NOW() returns the DB server's
		// timezone, which skews every window on any site whose WP timezone isn't
		// UTC — and, worse, pushes recent events' TIMESTAMPDIFF negative so the
		// newest sparkline buckets get dropped by the 0..23 guard in $to_buckets().
		$now = current_time( 'mysql' );

		// Events: last 24h vs the 24h before that.
		$events_24h      = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$events_table} WHERE created_at >= DATE_SUB( %s, INTERVAL 24 HOUR )", $now ) );
		$events_prev_24h = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$events_table} WHERE created_at >= DATE_SUB( %s, INTERVAL 48 HOUR ) AND created_at < DATE_SUB( %s, INTERVAL 24 HOUR )", $now, $now ) );

		// Active customers (distinct) 24h vs prior 24h.
		$active_24h      = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(DISTINCT email_hash) FROM {$events_table} WHERE created_at >= DATE_SUB( %s, INTERVAL 24 HOUR )", $now ) );
		$active_prev_24h = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(DISTINCT email_hash) FROM {$events_table} WHERE created_at >= DATE_SUB( %s, INTERVAL 48 HOUR ) AND created_at < DATE_SUB( %s, INTERVAL 24 HOUR )", $now, $now ) );

		// Orders (order_created) 24h vs prior 24h.
		$orders_24h      = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$events_table} WHERE event_type = 'order_created' AND created_at >= DATE_SUB( %s, INTERVAL 24 HOUR )", $now ) );
		$orders_prev_24h = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$events_table} WHERE event_type = 'order_created' AND created_at >= DATE_SUB( %s, INTERVAL 48 HOUR ) AND created_at < DATE_SUB( %s, INTERVAL 24 HOUR )", $now, $now ) );

		// New high-risk customers: this 7d vs prior 7d.
		$high_risk_7d      = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$customers_table} WHERE segment IN ('risk','critical') AND created_at >= DATE_SUB( %s, INTERVAL 7 DAY )", $now ) );
		$high_risk_prev_7d = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$customers_table} WHERE segment IN ('risk','critical') AND created_at >= DATE_SUB( %s, INTERVAL 14 DAY ) AND created_at < DATE_SUB( %s, INTERVAL 7 DAY )", $now, $now ) );

		// Rolling-24h hourly buckets for events and orders.
		$events_rows = $wpdb->get_results( $wpdb->prepare( "SELECT TIMESTAMPDIFF( HOUR, created_at, %s ) AS hours_ago, COUNT(*) AS c FROM {$events_table} WHERE created_at >= DATE_SUB( %s, INTERVAL 24 HOUR ) GROUP BY hours_ago", $now, $now ) );
		$orders_rows = $wpdb->get_results( $wpdb->prepare( "SELECT TIMESTAMPDIFF( HOUR, created_at, %s ) AS hours_ago, COUNT(*) AS c FROM {$events_table} WHERE event_type = 'order_created' AND created_at >= DATE_SUB( %s, INTERVAL 24 HOUR ) GROUP BY hours_ago", $now, $now ) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		$to_buckets = static function ( $rows ) {
			$buckets = array_fill( 0, 24, 0 );
			foreach ( (array) $rows as $row ) {
				$ha = (int) $row->hours_ago;
				if ( $ha >= 0 && $ha < 24 ) {
					// Chronological: oldest hour on the left, current hour on the right.
					$buckets[ 23 - $ha ] = (int) $row->c;
				}
			}
			return $buckets;
		};

		return array(
			'events'    => array( 'value' => $events_24h, 'prev' => $events_prev_24h, 'window' => '24h', 'spark' => $to_buckets( $events_rows ) ),
			'active'    => array( 'value' => $active_24h, 'prev' => $active_prev_24h, 'window' => '24h' ),
			'orders'    => array( 'value' => $orders_24h, 'prev' => $orders_prev_24h, 'window' => '24h', 'spark' => $to_buckets( $orders_rows ) ),
			'high_risk' => array( 'value' => $high_risk_7d, 'prev' => $high_risk_prev_7d, 'window' => '7d' ),
		);
	}


	/**
	 * Get ROI calculator data for a rolling window (Pro feature).
	 *
	 * Calculates money saved and at risk from TrustLens actions over the last
	 * $days, counting back from now.
	 *
	 * @since 1.2.0
	 * @param int $days Number of days to analyze.
	 * @return array ROI data.
	 */
	public static function get_roi_data( int $days = 30 ): array {
		list( $start, $end ) = self::rolling_range( $days );

		return self::get_roi_data_range( $start, $end );
	}

	/**
	 * Get ROI calculator data for an explicit datetime window.
	 *
	 * Every period-scoped metric is measured strictly within the half-open
	 * window [$start, $end), so callers can request an arbitrary calendar
	 * window (e.g. a single month) rather than only a rolling "last N days"
	 * window anchored to the present.
	 *
	 * @since 1.3.0
	 * @param string $start Inclusive window start ('Y-m-d H:i:s', site-local).
	 * @param string $end   Exclusive window end ('Y-m-d H:i:s', site-local).
	 * @return array ROI data.
	 */
	public static function get_roi_data_range( string $start, string $end ): array {
		global $wpdb;

		$customers_table = $wpdb->prefix . 'trustlens_customers';

		// Money protected: Orders held for review that weren't refunded.
		$orders_held_value = self::calculate_orders_held_value( $start, $end );

		// Money protected: Checkout blocks that prevented high-risk orders.
		$blocked_checkout_value = self::calculate_blocked_checkout_value( $start, $end );

		// Money protected: Chargebacks prevented (estimated from flagged customers).
		$chargeback_prevention = self::estimate_chargeback_prevention( $start, $end );

		// Money at risk: Unflagged orders from high-risk customers.
		$money_at_risk = self::calculate_money_at_risk( $start, $end );

		// Total refund value from high-risk customers (actual losses).
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix, prepared query.
		$high_risk_refunds = $wpdb->get_var( $wpdb->prepare(
			"SELECT SUM(c.total_refund_value)
			 FROM {$customers_table} c
			 WHERE c.segment IN ('risk', 'critical')
			   AND c.last_refund_date >= %s
			   AND c.last_refund_date < %s",
			$start,
			$end
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		$action_counts = self::get_automation_action_counts( $start, $end );
		$blocked_checkout_count = self::get_event_count( 'checkout_blocked', $start, $end );

		// Calculate ROI percentage.
		$total_protected = $orders_held_value + $blocked_checkout_value + $chargeback_prevention;
		$total_at_risk = $money_at_risk + (float) $high_risk_refunds;
		$roi_percentage = $total_at_risk > 0 ? ( $total_protected / $total_at_risk ) * 100 : 0;

		return array(
			'period_days'              => self::range_days( $start, $end ),
			'money_protected'          => array(
				'orders_held'          => round( $orders_held_value, 2 ),
				'blocked_checkouts'    => round( $blocked_checkout_value, 2 ),
				'chargeback_prevention'=> round( $chargeback_prevention, 2 ),
				'total'                => round( $total_protected, 2 ),
			),
			'money_at_risk'            => array(
				'unflagged_orders'     => round( $money_at_risk, 2 ),
				'high_risk_refunds'    => round( (float) $high_risk_refunds, 2 ),
				'total'                => round( $total_at_risk, 2 ),
			),
			'roi_percentage'           => round( $roi_percentage, 1 ),
			'actions_taken'            => $action_counts,
			'summary'                  => array(
				'orders_held'          => $action_counts['hold_order'] ?? 0,
				'checkouts_blocked'    => $blocked_checkout_count,
				'accounts_required'    => $action_counts['require_verification'] ?? 0,
				'customers_blocked'    => $action_counts['block_customer'] ?? 0,
			),
		);
	}

	/**
	 * Build a rolling [start, end) window ending at the present moment.
	 *
	 * Boundaries are expressed in site-local time to match how the
	 * trustlens_* tables store created_at (via current_time( 'mysql' )).
	 *
	 * @since 1.3.0
	 * @param int $days Number of days to span, counting back from now.
	 * @return array{0:string,1:string} [ $start, $end ] as 'Y-m-d H:i:s'.
	 */
	private static function rolling_range( int $days ): array {
		$now_ts = current_time( 'timestamp' );

		// Half-open [start, end): the end is the first instant NOT counted. Push it
		// one second past "now" so an event stamped in the current second (created_at
		// == now, at DATETIME's one-second resolution) still falls inside a rolling
		// "up to now" window under the exclusive `< end` bound that every window query
		// uses. This lets one uniform half-open convention serve both rolling windows
		// and calendar windows (whose exclusive end must not be double-counted).
		return array(
			gmdate( 'Y-m-d H:i:s', $now_ts - $days * DAY_IN_SECONDS ),
			gmdate( 'Y-m-d H:i:s', $now_ts + 1 ),
		);
	}

	/**
	 * Number of whole days spanned by a [start, end) window.
	 *
	 * @since 1.3.0
	 * @param string $start Window start ('Y-m-d H:i:s').
	 * @param string $end   Window end ('Y-m-d H:i:s').
	 * @return int
	 */
	private static function range_days( string $start, string $end ): int {
		$days = (int) round( ( strtotime( $end ) - strtotime( $start ) ) / DAY_IN_SECONDS );

		return max( 1, $days );
	}

	/**
	 * Calculate value of orders held for review.
	 *
	 * @since 1.2.0
	 * @param string $start Window start ('Y-m-d H:i:s').
	 * @param string $end   Window end ('Y-m-d H:i:s').
	 * @return float Value.
	 */
	private static function calculate_orders_held_value( string $start, string $end ): float {
		global $wpdb;

		if ( ! self::table_exists( 'trustlens_automation_logs' ) ) {
			return 0.0;
		}

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix, safe.
		$held_orders = $wpdb->get_col( $wpdb->prepare(
			"SELECT DISTINCT order_id
			 FROM {$wpdb->prefix}trustlens_automation_logs
			 WHERE action_type = 'hold_order'
			   AND created_at >= %s
			   AND created_at < %s",
			$start,
			$end
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		$total = 0.0;
		foreach ( $held_orders as $order_id ) {
			$order = wc_get_order( $order_id );
			if ( $order && ! $order->has_status( array( 'refunded', 'cancelled' ) ) ) {
				$total += (float) $order->get_total();
			}
		}
		return $total;
	}

	/**
	 * Calculate estimated value of blocked checkouts.
	 *
	 * @since 1.2.0
	 * @param string $start Window start ('Y-m-d H:i:s').
	 * @param string $end   Window end ('Y-m-d H:i:s').
	 * @return float Value.
	 */
	private static function calculate_blocked_checkout_value( string $start, string $end ): float {
		global $wpdb;

		// Count blocked checkouts.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix, safe.
		$blocked_count = $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(*)
			 FROM {$wpdb->prefix}trustlens_events
			 WHERE event_type = 'checkout_blocked'
			   AND created_at >= %s
			   AND created_at < %s",
			$start,
			$end
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		// Estimate value based on average order value of blocked customers.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Aggregate query, table name from $wpdb->prefix is safe.
		$avg_order = $wpdb->get_var(
			"SELECT AVG(total_order_value / NULLIF(total_orders, 0))
			 FROM {$wpdb->prefix}trustlens_customers
			 WHERE is_blocked = 1
			   AND total_orders > 0"
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

		// Use store average if no blocked customer data.
		if ( ! $avg_order ) {
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Aggregate query, table name from $wpdb->prefix is safe.
			$avg_order = $wpdb->get_var(
				"SELECT AVG(total_order_value / NULLIF(total_orders, 0))
				 FROM {$wpdb->prefix}trustlens_customers
				 WHERE segment IN ('risk', 'critical')
				   AND total_orders > 0"
			);
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		}

		// Default to $50 if no data.
		$avg_order = $avg_order ?: 50;

		// Estimate: blocked checkouts × avg order value × estimated fraud rate (60%).
		return (int) $blocked_count * (float) $avg_order * 0.6;
	}

	/**
	 * Estimate chargeback prevention value.
	 *
	 * @since 1.2.0
	 * @param string $start Window start ('Y-m-d H:i:s').
	 * @param string $end   Window end ('Y-m-d H:i:s').
	 * @return float Estimated value.
	 */
	private static function estimate_chargeback_prevention( string $start, string $end ): float {
		global $wpdb;

		// Get customers with disputes who were flagged before dispute.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix, safe.
		$flagged_disputes = $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(DISTINCT e.email_hash)
			 FROM {$wpdb->prefix}trustlens_events e
			 JOIN {$wpdb->prefix}trustlens_customers c ON e.email_hash = c.email_hash
			 WHERE e.event_type = 'dispute_created'
			   AND e.created_at >= %s
			   AND e.created_at < %s
			   AND c.segment IN ('risk', 'critical')",
			$start,
			$end
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		// Get average dispute value.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix, safe.
		$avg_dispute = $wpdb->get_var( $wpdb->prepare(
			"SELECT AVG(CAST(JSON_UNQUOTE(JSON_EXTRACT(event_data, '$.amount')) AS DECIMAL(12,2)))
			 FROM {$wpdb->prefix}trustlens_events
			 WHERE event_type = 'dispute_created'
			   AND created_at >= %s
			   AND created_at < %s",
			$start,
			$end
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		$avg_dispute = $avg_dispute ?: 100;

		// Estimate: flagged customers who had disputes × chargeback fee savings.
		// Industry average chargeback fee is ~$25 + the order value.
		return (int) $flagged_disputes * ( (float) $avg_dispute + 25 );
	}

	/**
	 * Calculate money at risk from unflagged high-risk orders.
	 *
	 * @since 1.2.0
	 * @param string $start Window start ('Y-m-d H:i:s').
	 * @param string $end   Window end ('Y-m-d H:i:s').
	 * @return float Value at risk.
	 */
	private static function calculate_money_at_risk( string $start, string $end ): float {
		global $wpdb;

		// Get recent order value from high-risk customers who weren't blocked.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix, safe.
		$at_risk = $wpdb->get_var( $wpdb->prepare(
			"SELECT SUM(CAST(JSON_UNQUOTE(JSON_EXTRACT(e.event_data, '$.order_total')) AS DECIMAL(12,2)))
			 FROM {$wpdb->prefix}trustlens_events e
			 JOIN {$wpdb->prefix}trustlens_customers c ON e.email_hash = c.email_hash
			 WHERE e.event_type = 'order_created'
			   AND e.created_at >= %s
			   AND e.created_at < %s
			   AND c.segment IN ('caution', 'risk', 'critical')
			   AND c.is_blocked = 0",
			$start,
			$end
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		// Apply risk factor based on segment distribution.
		// Caution: 10% risk, Risk: 30% risk, Critical: 60% risk.
		if ( ! $at_risk ) {
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix, safe.
			$at_risk = $wpdb->get_var( $wpdb->prepare(
				"SELECT SUM(
					CASE c.segment
						WHEN 'caution' THEN (c.total_order_value / NULLIF(c.total_orders, 0)) * 0.1
						WHEN 'risk' THEN (c.total_order_value / NULLIF(c.total_orders, 0)) * 0.3
						WHEN 'critical' THEN (c.total_order_value / NULLIF(c.total_orders, 0)) * 0.6
						ELSE 0
					END
				)
				 FROM {$wpdb->prefix}trustlens_customers c
				 WHERE c.segment IN ('caution', 'risk', 'critical')
				   AND c.is_blocked = 0
				   AND c.last_order_date >= %s
				   AND c.last_order_date < %s",
				$start,
				$end
			) );
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		}

		return (float) $at_risk;
	}

	/**
	 * Get ROI trends over time.
	 *
	 * @since 1.2.0
	 * @param int $days Days to analyze.
	 * @return array Daily ROI data.
	 */
	public static function get_roi_trends( int $days = 30 ): array {
		global $wpdb;

		$automation_by_date = self::get_automation_action_counts_by_date( $days );
		$blocked_checkout_by_date = self::get_event_counts_by_date( 'checkout_blocked', $days );
		$all_dates = array_unique( array_merge( array_keys( $automation_by_date ), array_keys( $blocked_checkout_by_date ) ) );

		sort( $all_dates );

		$data = array();
		foreach ( $all_dates as $date ) {
			$actions = $automation_by_date[ $date ] ?? array();
			$data[] = array(
				'date'              => $date,
				'orders_held'       => (int) ( $actions['hold_order'] ?? 0 ),
				'checkouts_blocked' => (int) ( $blocked_checkout_by_date[ $date ] ?? 0 ),
				'accounts_required' => (int) ( $actions['require_verification'] ?? 0 ),
			);
		}

		return $data;
	}

	/**
	 * Get normalized automation action counts for a window.
	 *
	 * @since 1.1.5
	 * @param string $start Window start ('Y-m-d H:i:s').
	 * @param string $end   Window end ('Y-m-d H:i:s').
	 * @return array
	 */
	private static function get_automation_action_counts( string $start, string $end ): array {
		$rows = self::get_automation_action_rows( $start, $end );

		return self::normalize_action_rows( $rows );
	}

	/**
	 * Get normalized automation action counts grouped by date.
	 *
	 * @since 1.1.5
	 * @param int $days Days to analyze.
	 * @return array
	 */
	private static function get_automation_action_counts_by_date( int $days ): array {
		list( $start, $end ) = self::rolling_range( $days );
		$rows = self::get_automation_action_rows( $start, $end, true );
		$grouped = array();

		foreach ( $rows as $row ) {
			$date = (string) $row->date;
			if ( ! isset( $grouped[ $date ] ) ) {
				$grouped[ $date ] = array();
			}
			$key = (string) $row->action_type;
			$grouped[ $date ][ $key ] = ( $grouped[ $date ][ $key ] ?? 0 ) + (int) $row->count;
		}

		return $grouped;
	}

	/**
	 * Get raw automation action rows for a window.
	 *
	 * @since 1.1.5
	 * @param string $start          Window start ('Y-m-d H:i:s').
	 * @param string $end            Window end ('Y-m-d H:i:s').
	 * @param bool   $group_by_date  Whether to include date grouping.
	 * @return array
	 */
	private static function get_automation_action_rows( string $start, string $end, bool $group_by_date = false ): array {
		global $wpdb;

		if ( ! self::table_exists( 'trustlens_automation_logs' ) ) {
			return array();
		}

		$select = $group_by_date ? 'DATE(created_at) as date, action_type, COUNT(*) as count' : 'action_type, COUNT(*) as count';
		$group_by = $group_by_date ? 'DATE(created_at), action_type' : 'action_type';
		$order_by = $group_by_date ? 'date ASC, action_type ASC' : 'action_type ASC';

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix, safe.
		return $wpdb->get_results( $wpdb->prepare(
			"SELECT {$select}
			 FROM {$wpdb->prefix}trustlens_automation_logs
			 WHERE created_at >= %s
			   AND created_at < %s
			 GROUP BY {$group_by}
			 ORDER BY {$order_by}",
			$start,
			$end
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	/**
	 * Normalize grouped action rows into canonical action keys.
	 *
	 * @since 1.1.5
	 * @param array $rows Raw query rows.
	 * @return array
	 */
	private static function normalize_action_rows( array $rows ): array {
		$counts = array();

		foreach ( $rows as $row ) {
			$key = (string) $row->action_type;
			$counts[ $key ] = ( $counts[ $key ] ?? 0 ) + (int) $row->count;
		}

		return $counts;
	}

	/**
	 * Get count of a single event type for a period.
	 *
	 * @since 1.1.5
	 * @param string $event_type Event type.
	 * @param string $start      Window start ('Y-m-d H:i:s').
	 * @param string $end        Window end ('Y-m-d H:i:s').
	 * @return int
	 */
	private static function get_event_count( string $event_type, string $start, string $end ): int {
		global $wpdb;

		// Uniform half-open [start, end): rolling_range() sets $end one second past
		// "now", so a same-second event still counts here under the exclusive `<`
		// bound — while a calendar-month caller's exclusive month boundary can't be
		// double-counted across two adjacent months (which the earlier inclusive `<=`
		// bound did).
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix, safe.
		$count = $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(*)
			 FROM {$wpdb->prefix}trustlens_events
			 WHERE event_type = %s
			   AND created_at >= %s
			   AND created_at < %s",
			$event_type,
			$start,
			$end
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		return (int) $count;
	}

	/**
	 * Get per-day counts for a single event type.
	 *
	 * @since 1.1.5
	 * @param string $event_type Event type.
	 * @param int    $days       Days to analyze.
	 * @return array
	 */
	private static function get_event_counts_by_date( string $event_type, int $days ): array {
		global $wpdb;

		list( $start, $end ) = self::rolling_range( $days );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix, safe.
		$rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT DATE(created_at) as date, COUNT(*) as count
			 FROM {$wpdb->prefix}trustlens_events
			 WHERE event_type = %s
			   AND created_at >= %s
			   AND created_at < %s
			 GROUP BY DATE(created_at)
			 ORDER BY date ASC",
			$event_type,
			$start,
			$end
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		$counts = array();
		foreach ( $rows as $row ) {
			$counts[ $row->date ] = (int) $row->count;
		}

		return $counts;
	}

	/**
	 * Get monthly ROI summary for reporting.
	 *
	 * @since 1.2.0
	 * @param int $months Number of months to include.
	 * @return array Monthly summaries.
	 */
	public static function get_monthly_roi_summary( int $months = 6 ): array {
		$summaries = array();

		// Anchor on site-local time so month boundaries line up with how the
		// trustlens_* tables store created_at (via current_time( 'mysql' )).
		$first_of_this_month = strtotime( gmdate( 'Y-m-01 00:00:00', current_time( 'timestamp' ) ) );

		for ( $i = 0; $i < $months; $i++ ) {
			$month_start_ts = strtotime( "-{$i} months", $first_of_this_month );
			$next_month_ts  = strtotime( '+1 month', $month_start_ts );

			$start = gmdate( 'Y-m-d H:i:s', $month_start_ts );
			$end   = gmdate( 'Y-m-d H:i:s', $next_month_ts );

			// Measure each month over its own calendar window, not a rolling
			// window anchored to today.
			$roi_data = self::get_roi_data_range( $start, $end );

			$summaries[] = array(
				'month'           => gmdate( 'Y-m', $month_start_ts ),
				'month_label'     => gmdate( 'F Y', $month_start_ts ),
				'money_protected' => $roi_data['money_protected']['total'],
				'money_at_risk'   => $roi_data['money_at_risk']['total'],
				'roi_percentage'  => $roi_data['roi_percentage'],
				'actions_taken'   => array_sum( $roi_data['summary'] ),
			);
		}

		return array_reverse( $summaries );
	}

	/**
	 * Count network early-warning detections in the last N days.
	 *
	 * An early-warning is a shopper a linked store flagged before they could build
	 * history here (see TrustLens_Module_Store_Network). This is a count, never a
	 * money figure.
	 *
	 * @since 1.3.6
	 * @param int $days Rolling window in days.
	 * @return int
	 */
	public static function get_network_early_warnings( int $days = 30 ): int {
		list( $start, $end ) = self::rolling_range( $days );
		return self::get_event_count( 'network_early_warning', $start, $end );
	}
}
