<?php
/**
 * Customer repository for database operations.
 *
 * @package TrustLens
 * @since   1.0.0
 */

defined( 'ABSPATH' ) || exit;

/**
 * Customer repository class.
 *
 * @since 1.0.0
 */
class TrustLens_Customer_Repository {

	/**
	 * Table name.
	 *
	 * @var string
	 */
	private string $table;

	/**
	 * Constructor.
	 *
	 * @since 1.0.0
	 */
	public function __construct() {
		global $wpdb;
		$this->table = $wpdb->prefix . 'trustlens_customers';
	}

	/**
	 * Get customer by email hash.
	 *
	 * @since 1.0.0
	 * @param string $email_hash Customer email hash.
	 * @return object|null Customer object or null.
	 */
	public function get_by_hash( string $email_hash ): ?object {
		global $wpdb;

		$email_hash = wstl_normalize_hash( $email_hash );
		if ( ! wstl_is_email_hash( $email_hash ) ) {
			return null;
		}

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix, safe.
		$customer = $wpdb->get_row( $wpdb->prepare(
			"SELECT * FROM {$this->table} WHERE email_hash = %s",
			$email_hash
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		return $customer ?: null;
	}

	/**
	 * Search customers by email for the header command palette.
	 *
	 * Riskiest matches first (lowest trust score) so a fraud lookup surfaces the
	 * accounts that matter. Matches against the stored customer_email.
	 *
	 * @since 1.2.7
	 * @param string $term  Search term (>= 2 chars).
	 * @param int    $limit Max results (clamped 1–10).
	 * @return array<int,object> Rows: email_hash, customer_email, segment, trust_score.
	 */
	public function search( string $term, int $limit = 6 ): array {
		global $wpdb;

		$term = trim( $term );
		if ( strlen( $term ) < 2 ) {
			return array();
		}
		$limit = max( 1, min( 10, $limit ) );
		$like  = '%' . $wpdb->esc_like( $term ) . '%';

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix; term prepared.
		$rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT email_hash, customer_email, segment, trust_score
			 FROM {$this->table}
			 WHERE customer_email LIKE %s
			 ORDER BY trust_score ASC
			 LIMIT %d",
			$like,
			$limit
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * Get customer by ID.
	 *
	 * @since 1.0.0
	 * @param int $id Customer ID.
	 * @return object|null Customer object or null.
	 */
	public function get_by_id( int $id ): ?object {
		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.
		$customer = $wpdb->get_row( $wpdb->prepare(
			"SELECT * FROM {$this->table} WHERE id = %d",
			$id
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		return $customer ?: null;
	}

	/**
	 * Get customer by WP user ID.
	 *
	 * @since 1.0.0
	 * @param int $customer_id WooCommerce customer ID.
	 * @return object|null Customer object or null.
	 */
	public function get_by_customer_id( int $customer_id ): ?object {
		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.
		$customer = $wpdb->get_row( $wpdb->prepare(
			"SELECT * FROM {$this->table} WHERE customer_id = %d",
			$customer_id
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		return $customer ?: null;
	}

	/**
	 * Update customer data.
	 *
	 * @since 1.0.0
	 * @param string $email_hash Customer email hash.
	 * @param array  $data       Data to update.
	 * @return bool True on success.
	 */
	public function update( string $email_hash, array $data ): bool {
		global $wpdb;

		$customer = $this->get_by_hash( $email_hash );
		if ( ! $customer ) {
			return false;
		}

		$data['updated_at'] = current_time( 'mysql' );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Table name from $wpdb->prefix, safe.
		$result = $wpdb->update(
			$this->table,
			$data,
			array( 'email_hash' => $customer->email_hash )
		);

		return $result !== false;
	}

	/**
	 * Insert or update customer.
	 *
	 * @since 1.0.0
	 * @param array $data Customer data.
	 * @return bool True on success.
	 */
	public function upsert( array $data ): bool {
		global $wpdb;

		if ( empty( $data['email_hash'] ) ) {
			return false;
		}

		$existing = $this->get_by_hash( $data['email_hash'] );

		if ( $existing ) {
			return $this->update( $data['email_hash'], $data );
		}

		$data['created_at'] = current_time( 'mysql' );
		$data['updated_at'] = current_time( 'mysql' );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Table name from $wpdb->prefix, safe.
		$result = $wpdb->insert( $this->table, $data );

		return $result !== false;
	}

	/**
	 * Delete customer.
	 *
	 * @since 1.0.0
	 * @param string $email_hash Customer email hash.
	 * @return bool True on success.
	 */
	public function delete( string $email_hash ): bool {
		global $wpdb;

		$customer = $this->get_by_hash( $email_hash );
		if ( ! $customer ) {
			return false;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Table name from $wpdb->prefix, safe.
		$result = $wpdb->delete(
			$this->table,
			array( 'email_hash' => $customer->email_hash )
		);

		return $result !== false;
	}

	/**
	 * Decode the JSON `tags` column into a clean list of string tags.
	 *
	 * @since 1.5.1
	 * @param mixed $raw Raw column value (JSON string or null).
	 * @return array<int,string>
	 */
	private function decode_tags( $raw ): array {
		if ( empty( $raw ) ) {
			return array();
		}
		$tags = json_decode( (string) $raw, true );
		if ( ! is_array( $tags ) ) {
			return array();
		}
		return array_values( array_filter( $tags, 'is_string' ) );
	}

	/**
	 * Add a tag to a customer, deduplicated, in the canonical `tags` JSON column.
	 * Single source of truth for tagging — the automation engine and bulk
	 * operations both route through here so tags land in one place and stay
	 * removable.
	 *
	 * @since 1.5.1
	 * @param string $email_hash Customer email hash.
	 * @param string $tag        Tag to add.
	 * @return bool True on success.
	 */
	public function add_tag( string $email_hash, string $tag ): bool {
		$tag = trim( $tag );
		if ( '' === $tag ) {
			return false;
		}
		$customer = $this->get_by_hash( $email_hash );
		if ( ! $customer ) {
			return false;
		}
		$tags = $this->decode_tags( $customer->tags ?? '' );
		if ( ! in_array( $tag, $tags, true ) ) {
			$tags[] = $tag;
		}
		return $this->update( $email_hash, array( 'tags' => wp_json_encode( array_values( $tags ) ) ) );
	}

	/**
	 * Remove a tag from a customer's canonical `tags` JSON column.
	 *
	 * @since 1.5.1
	 * @param string $email_hash Customer email hash.
	 * @param string $tag        Tag to remove.
	 * @return bool True on success.
	 */
	public function remove_tag( string $email_hash, string $tag ): bool {
		$customer = $this->get_by_hash( $email_hash );
		if ( ! $customer ) {
			return false;
		}
		$tags     = $this->decode_tags( $customer->tags ?? '' );
		$filtered = array_values( array_filter(
			$tags,
			static function ( $t ) use ( $tag ) {
				return $t !== $tag;
			}
		) );
		return $this->update( $email_hash, array( 'tags' => wp_json_encode( $filtered ) ) );
	}

	/**
	 * Get customers with pagination.
	 *
	 * @since 1.0.0
	 * @param array $args Query arguments.
	 * @return array Customers array.
	 */
	public function get_customers( array $args = array() ): array {
		global $wpdb;

		$defaults = array(
			'per_page' => 20,
			'page'     => 1,
			'orderby'  => 'trust_score',
			'order'    => 'ASC',
			'segment'  => '',
			'search'   => '',
		);

		$args = wp_parse_args( $args, $defaults );

		$where = array( '1=1' );
		$values = array();

		// Filter by segment.
		if ( ! empty( $args['segment'] ) ) {
			$where[] = 'segment = %s';
			$values[] = $args['segment'];
		}

		// Search by email.
		if ( ! empty( $args['search'] ) ) {
			$where[] = 'customer_email LIKE %s';
			$values[] = '%' . $wpdb->esc_like( $args['search'] ) . '%';
		}

		$this->apply_advanced_filters( $args, $where, $values );

		$where_sql = implode( ' AND ', $where );

		// Sanitize orderby.
		$allowed_orderby = array(
			'trust_score', 'total_orders', 'total_refunds', 'return_rate',
			'total_refund_value', 'last_order_date', 'created_at',
		);
		$orderby = in_array( $args['orderby'], $allowed_orderby, true ) ? $args['orderby'] : 'trust_score';
		$order = strtoupper( $args['order'] ) === 'DESC' ? 'DESC' : 'ASC';

		$offset = ( $args['page'] - 1 ) * $args['per_page'];

		$sql = "SELECT * FROM {$this->table} WHERE {$where_sql} ORDER BY {$orderby} {$order} LIMIT %d OFFSET %d";
		$values[] = $args['per_page'];
		$values[] = $offset;

		if ( ! empty( $values ) ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $sql is built above with whitelisted orderby/order and table from $wpdb->prefix.
			$sql = $wpdb->prepare( $sql, $values );
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- SQL is prepared above when values exist, orderby/order are whitelisted, table from $wpdb->prefix.
		return $wpdb->get_results( $sql );
	}

	/**
	 * Append score-range, return-rate and status filters to a WHERE builder.
	 * Shared by get_customers() and count() so both stay in sync.
	 *
	 * @since 1.3.0
	 * @param array $args   Query args.
	 * @param array $where  WHERE clauses, by reference.
	 * @param array $values Prepared values, by reference.
	 * @return void
	 */
	private function apply_advanced_filters( array $args, array &$where, array &$values ): void {
		if ( isset( $args['score_min'] ) && '' !== $args['score_min'] ) {
			$where[]  = 'trust_score >= %d';
			$values[] = (int) $args['score_min'];
		}
		if ( isset( $args['score_max'] ) && '' !== $args['score_max'] ) {
			$where[]  = 'trust_score <= %d';
			$values[] = (int) $args['score_max'];
		}
		if ( isset( $args['rate_min'] ) && '' !== $args['rate_min'] ) {
			$where[]  = 'return_rate >= %f';
			$values[] = (float) $args['rate_min'];
		}
		$status = $args['status'] ?? '';
		if ( 'blocked' === $status ) {
			$where[] = 'is_blocked = 1';
		} elseif ( 'allowlisted' === $status ) {
			$where[] = 'is_allowlisted = 1';
		} elseif ( 'flagged' === $status ) {
			$where[] = 'is_flagged = 1';
		}
	}

	/**
	 * Count customers.
	 *
	 * @since 1.0.0
	 * @param array $args Query arguments.
	 * @return int Customer count.
	 */
	public function count( array $args = array() ): int {
		global $wpdb;

		$where = array( '1=1' );
		$values = array();

		if ( ! empty( $args['segment'] ) ) {
			$where[] = 'segment = %s';
			$values[] = $args['segment'];
		}

		if ( ! empty( $args['search'] ) ) {
			$where[] = 'customer_email LIKE %s';
			$values[] = '%' . $wpdb->esc_like( $args['search'] ) . '%';
		}

		$this->apply_advanced_filters( $args, $where, $values );

		$where_sql = implode( ' AND ', $where );
		$sql = "SELECT COUNT(*) FROM {$this->table} WHERE {$where_sql}";

		if ( ! empty( $values ) ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $sql is built above with table from $wpdb->prefix.
			$sql = $wpdb->prepare( $sql, $values );
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- SQL is prepared above when values exist, table from $wpdb->prefix.
		return (int) $wpdb->get_var( $sql );
	}

	/**
	 * Get customers by segment.
	 *
	 * @since 1.0.0
	 * @param string $segment Segment name.
	 * @param int    $limit   Number of customers.
	 * @return array Customers array.
	 */
	public function get_by_segment( string $segment, 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 * FROM {$this->table}
			 WHERE segment = %s AND is_allowlisted = 0
			 ORDER BY trust_score ASC, total_refund_value DESC
			 LIMIT %d",
			$segment,
			$limit
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	/**
	 * Get high-risk customers.
	 *
	 * @since 1.0.0
	 * @param int $limit Number of customers.
	 * @return array Customers array.
	 */
	public function get_high_risk( int $limit = 10 ): array {
		global $wpdb;

		$cache_key = 'wstl_high_risk_' . $this->cache_generation() . '_' . $limit;
		$cached = get_transient( $cache_key );

		if ( $cached !== false ) {
			return $cached;
		}

		// 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 * FROM {$this->table}
			 WHERE segment IN ('risk', 'critical') AND is_allowlisted = 0
			 ORDER BY FIELD(segment, 'critical', 'risk'), trust_score ASC
			 LIMIT %d",
			$limit
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		set_transient( $cache_key, $results, 5 * MINUTE_IN_SECONDS );

		return $results;
	}

	/**
	 * Get segment counts.
	 *
	 * @since 1.0.0
	 * @return array Segment counts.
	 */
	public function get_segment_counts(): array {
		global $wpdb;

		$cache_key = 'wstl_segment_counts_' . $this->cache_generation();
		$cached = get_transient( $cache_key );

		if ( $cached !== false ) {
			return $cached;
		}

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Aggregate query, table from $wpdb->prefix, result cached in transient above.
		$results = $wpdb->get_results(
			"SELECT segment, COUNT(*) as count FROM {$this->table} GROUP BY segment",
			OBJECT_K
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

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

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

		set_transient( $cache_key, $counts, 5 * MINUTE_IN_SECONDS );

		return $counts;
	}

	/**
	 * Increment customer stats.
	 *
	 * @since 1.0.0
	 * @param string $email_hash Customer email hash.
	 * @param string $field      Field to increment.
	 * @param int    $amount     Amount to add.
	 * @return bool True on success.
	 */
	public function increment( string $email_hash, string $field, int $amount = 1 ): bool {
		global $wpdb;

		$allowed_fields = array(
			'total_orders', 'total_refunds', 'full_refunds', 'partial_refunds',
			'total_coupons_used', 'first_order_coupons', 'coupon_then_refund',
			'total_disputes', 'disputes_won', 'disputes_lost',
			'linked_accounts', 'cancelled_orders', 'order_edits', 'reviews_before_refund',
		);

		if ( ! in_array( $field, $allowed_fields, true ) ) {
			return false;
		}

		$customer = $this->get_by_hash( $email_hash );
		if ( ! $customer ) {
			return false;
		}

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table from $wpdb->prefix, $field validated against allowlist above.
		$result = $wpdb->query( $wpdb->prepare(
			"UPDATE {$this->table} SET {$field} = {$field} + %d, updated_at = %s WHERE email_hash = %s",
			$amount,
			current_time( 'mysql' ),
			$customer->email_hash
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		return $result !== false;
	}

	/**
	 * Update return rate for customer.
	 *
	 * @since 1.0.0
	 * @param string $email_hash Customer email hash.
	 * @return bool True on success.
	 */
	public function update_return_rate( string $email_hash ): bool {
		global $wpdb;

		$customer = $this->get_by_hash( $email_hash );
		if ( ! $customer ) {
			return false;
		}

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix, safe.
		$result = $wpdb->query( $wpdb->prepare(
			"UPDATE {$this->table}
			 SET return_rate = CASE
			     WHEN total_orders > 0 THEN (total_refunds / total_orders) * 100
			     ELSE 0
			 END,
			 updated_at = %s
			 WHERE email_hash = %s",
			current_time( 'mysql' ),
			$customer->email_hash
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		return $result !== false;
	}

	/**
	 * Get the site-wide average return rate across customers with orders.
	 *
	 * Cached in a transient for one hour. Staleness is acceptable — this is
	 * used only as a comparison value on a single customer's detail page.
	 *
	 * @since 1.2.1
	 * @return float Average return rate as a percentage (0–100). Zero if no data.
	 */
	public function get_avg_return_rate(): float {
		$cached = get_transient( 'wstl_avg_return_rate' );
		if ( false !== $cached ) {
			return (float) $cached;
		}

		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.
		$avg = $wpdb->get_var(
			"SELECT AVG(return_rate) FROM {$this->table} WHERE total_orders > 0"
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		$avg = null === $avg ? 0.0 : (float) $avg;

		// One hour is intentional — this is a comparison baseline, not a live counter.
		set_transient( 'wstl_avg_return_rate', $avg, HOUR_IN_SECONDS );

		return $avg;
	}

	/**
	 * Get a weekly return rate trend for a customer.
	 *
	 * Derives a time series from the trustlens_events table by bucketing
	 * order_completed vs refund events into ISO weeks. Always returns
	 * exactly $weeks items, oldest first. Weeks with zero orders report
	 * rate=null so the trend chart can draw a gap.
	 *
	 * Cached in a transient for 5 minutes, keyed by the first 16 chars of
	 * the email hash (collision-safe, stays well under WP transient key
	 * length limits).
	 *
	 * @since 1.2.1
	 * @param string $email_hash Customer email hash.
	 * @param int    $weeks      Number of weeks to return. Default 12.
	 * @return array<int,array{week:string,orders:int,refunds:int,rate:?float}>
	 */
	public function get_return_rate_trend( string $email_hash, int $weeks = 12 ): array {
		if ( $weeks < 1 ) {
			return array();
		}

		$email_hash = wstl_normalize_hash( $email_hash );
		if ( ! wstl_is_email_hash( $email_hash ) ) {
			return array();
		}

		$cache_key = 'wstl_trend_' . substr( $email_hash, 0, 16 ) . '_' . $weeks;
		$cached    = get_transient( $cache_key );
		if ( false !== $cached && is_array( $cached ) ) {
			return $cached;
		}

		global $wpdb;
		$events_table = $wpdb->prefix . 'trustlens_events';

		// 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 YEARWEEK(created_at, 3) AS yw,
			        SUM(CASE WHEN event_type = 'order_completed' THEN 1 ELSE 0 END) AS orders,
			        SUM(CASE WHEN event_type IN ('full_refund','partial_refund') THEN 1 ELSE 0 END) AS refunds
			 FROM {$events_table}
			 WHERE email_hash = %s
			   AND created_at >= DATE_SUB(%s, INTERVAL %d WEEK)
			   AND event_type IN ('order_completed','full_refund','partial_refund')
			 GROUP BY yw
			 ORDER BY yw ASC",
			$email_hash,
			current_time( 'mysql' ),
			$weeks
		) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		// Index the SQL results by ISO year-week key (e.g. "202611").
		$by_week = array();
		if ( is_array( $rows ) ) {
			foreach ( $rows as $row ) {
				$by_week[ (string) $row->yw ] = $row;
			}
		}

		// Build a complete series including weeks with zero activity.
		$trend = array();
		for ( $i = $weeks - 1; $i >= 0; $i-- ) {
			// Anchor to WP-local now so the ISO-week key matches YEARWEEK(created_at),
			// which reads the locally-stored (current_time) wall-clock date. A UTC base
			// (strtotime + gmdate) drifted a week either side of the boundary on non-UTC
			// sites, so the join below missed and drew phantom gaps in the trend.
			$ts  = current_time( 'timestamp' ) - $i * WEEK_IN_SECONDS;
			$key = gmdate( 'oW', $ts ); // ISO year (4) + ISO week (2) = "202611"

			$orders  = 0;
			$refunds = 0;
			if ( isset( $by_week[ $key ] ) ) {
				$orders  = (int) $by_week[ $key ]->orders;
				$refunds = (int) $by_week[ $key ]->refunds;
			}

			$rate = $orders > 0 ? ( $refunds / $orders ) * 100.0 : null;

			$trend[] = array(
				'week'    => $key,
				'orders'  => $orders,
				'refunds' => $refunds,
				'rate'    => $rate,
			);
		}

		set_transient( $cache_key, $trend, 5 * MINUTE_IN_SECONDS );

		return $trend;
	}

	/**
	 * Current cache generation for generation-scoped transients.
	 *
	 * Generation-scoped cache keys (high-risk lists for any limit, segment
	 * counts) embed this value so they can all be invalidated at once by
	 * bumping a single counter, regardless of how many distinct keys exist.
	 *
	 * @since 1.3.0
	 * @return int
	 */
	private function cache_generation(): int {
		return max( 1, (int) get_option( 'wstl_cache_generation', 1 ) );
	}

	/**
	 * Invalidate caches.
	 *
	 * Bumps the cache generation so every generation-scoped transient is
	 * invalidated in one step — including high-risk lists cached under
	 * arbitrary limits (e.g. 5, 10, 25). Stale entries expire via their own
	 * short TTL.
	 *
	 * @since 1.0.0
	 */
	public function invalidate_caches(): void {
		update_option( 'wstl_cache_generation', $this->cache_generation() + 1, false );
	}
}
