<?php
/**
 * Dashboard Repository
 *
 * Handles complex queries for dashboard metrics and charts.
 * This repository provides data for KPIs, sparklines, and time-series charts.
 *
 * @package Awesomemotive\Rewardswp\Repositories
 * @since 1.1.0
 */

namespace Awesomemotive\Rewardswp\Repositories;

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

use Awesomemotive\Rewardswp\Abstracts\AbstractRepository;
use Awesomemotive\Rewardswp\Database\DatabaseHandler;
use Awesomemotive\Rewardswp\Database\Tables\MembersTable;
use Awesomemotive\Rewardswp\Database\Tables\PointsTable;
use Awesomemotive\Rewardswp\Database\Tables\ReferralsTable;
use Awesomemotive\Rewardswp\Database\Tables\RewardsTable;
use Awesomemotive\Rewardswp\Database\Tables\EventsTable;
use Awesomemotive\Rewardswp\Interfaces\IntegrationInterface;

/**
 * Class DashboardRepository
 *
 * Provides complex queries for dashboard analytics:
 * - Member statistics and growth
 * - Points activity and redemption rates
 * - Referral metrics
 * - Revenue calculations
 * - Time-series data for charts
 *
 * @since 1.1.0
 */
class DashboardRepository extends AbstractRepository {

	/**
	 * Members table instance.
	 *
	 * @since 1.1.0
	 *
	 * @var MembersTable
	 */
	private MembersTable $members_table;

	/**
	 * Points table instance.
	 *
	 * @since 1.1.0
	 *
	 * @var PointsTable
	 */
	private PointsTable $points_table;

	/**
	 * Referrals table instance.
	 *
	 * @since 1.1.0
	 *
	 * @var ReferralsTable
	 */
	private ReferralsTable $referrals_table;

	/**
	 * Rewards table instance.
	 *
	 * @since 1.1.0
	 *
	 * @var RewardsTable
	 */
	private RewardsTable $rewards_table;

	/**
	 * Events table instance.
	 *
	 * @since 1.1.0
	 *
	 * @var EventsTable
	 */
	private EventsTable $events_table;

	/**
	 * Integration interface instance.
	 *
	 * @since 1.1.0
	 *
	 * @var IntegrationInterface
	 */
	private IntegrationInterface $integration;

	/**
	 * Constructor.
	 *
	 * @since 1.1.0
	 *
	 * @param DatabaseHandler      $db_handler      Database handler instance.
	 * @param MembersTable         $members_table   Members table instance.
	 * @param PointsTable          $points_table    Points table instance.
	 * @param ReferralsTable       $referrals_table Referrals table instance.
	 * @param RewardsTable         $rewards_table   Rewards table instance.
	 * @param EventsTable          $events_table    Events table instance.
	 * @param IntegrationInterface $integration     Integration interface instance.
	 */
	public function __construct(
		DatabaseHandler $db_handler,
		MembersTable $members_table,
		PointsTable $points_table,
		ReferralsTable $referrals_table,
		RewardsTable $rewards_table,
		EventsTable $events_table,
		IntegrationInterface $integration
	) {
		parent::__construct( $db_handler );
		$this->members_table   = $members_table;
		$this->points_table    = $points_table;
		$this->referrals_table = $referrals_table;
		$this->rewards_table   = $rewards_table;
		$this->events_table    = $events_table;
		$this->integration     = $integration;
	}

	// ========================================================================
	// PHASE 1: New Members KPI
	// ========================================================================

	/**
	 * Get the count of new members created within a date range.
	 *
	 * Per PDF guidelines for "New members":
	 * - Include: Customers who joined the program (first-ever membership record)
	 * - Statuses: Include 'active' and 'guest' only
	 * - Anchor by: created_at timestamp
	 * - Exclude: Test/sandbox users, blocked/excluded members
	 *
	 * @since 1.1.0
	 *
	 * @param string $start_date Start date in 'Y-m-d H:i:s' format.
	 * @param string $end_date   End date in 'Y-m-d H:i:s' format.
	 *
	 * @return int Number of new members in the period.
	 */
	public function get_new_members_count( string $start_date, string $end_date ): int {
		$sql = "
			SELECT COUNT(*) as count
			FROM {$this->members_table->get_prefixed_table_name()}
			WHERE created_at >= %s
			  AND created_at <= %s
			  AND status IN ('active', 'guest')
		";

		$result = $this->get_var( $sql, [ $start_date, $end_date ] );

		return (int) ( $result ?? 0 );
	}

	/**
	 * Get the total count of eligible members in the system.
	 *
	 * Per PDF guidelines: "Total eligible = members not Excluded (and not test/blocked/deleted)"
	 * Only counts members with status IN ('active', 'guest').
	 *
	 * Used for displaying total member count in KPIs and as denominator for percentages.
	 *
	 * @since 1.1.0
	 *
	 * @return int Total number of eligible members.
	 */
	public function get_total_members_count(): int {
		$sql = "
			SELECT COUNT(*) as count
			FROM {$this->members_table->get_prefixed_table_name()}
			WHERE status IN ('active', 'guest')
		";

		$result = $this->get_var( $sql, [] );

		return (int) ( $result ?? 0 );
	}

	/**
	 * Get new member counts grouped by day within a date range.
	 *
	 * Returns an associative array with dates as keys and member counts as values.
	 * Used for the Member Growth chart on the Reports page.
	 *
	 * @since 1.1.0
	 *
	 * @param string $start_date Start date in 'Y-m-d H:i:s' format.
	 * @param string $end_date   End date in 'Y-m-d H:i:s' format.
	 *
	 * @return array<string, int> Array of dates => member counts.
	 */
	public function get_new_members_by_day( string $start_date, string $end_date ): array {
		$sql = "
			SELECT DATE(created_at) as date, COUNT(*) as count
			FROM {$this->members_table->get_prefixed_table_name()}
			WHERE created_at >= %s
			  AND created_at <= %s
			  AND status IN ('active', 'guest')
			GROUP BY DATE(created_at)
			ORDER BY date ASC
		";

		$results = $this->query( $sql, [ $start_date, $end_date ] );

		$daily = [];
		foreach ( $results as $row ) {
			$daily[ $row['date'] ] = (int) $row['count'];
		}
		return $daily;
	}

	/**
	 * Get the total count of eligible members before a specific date.
	 *
	 * Used to calculate the base count for cumulative member growth charts.
	 *
	 * @since 1.1.0
	 *
	 * @param string $date Date in 'Y-m-d H:i:s' format.
	 *
	 * @return int Total number of eligible members created before the date.
	 */
	public function get_total_members_before_date( string $date ): int {
		$sql = "
			SELECT COUNT(*) as count
			FROM {$this->members_table->get_prefixed_table_name()}
			WHERE created_at < %s
			  AND status IN ('active', 'guest')
		";

		return (int) ( $this->get_var( $sql, [ $date ] ) ?? 0 );
	}

	// ========================================================================
	// PHASE 2: Completed Referrals KPI
	// ========================================================================

	/**
	 * Get the count of completed referrals within a date range.
	 *
	 * Per PDF guidelines for "Completed referrals":
	 * - Count referrals that BECAME completed in the period (use converted_at timestamp)
	 * - Exclude test/sandbox users and blocked/deleted members
	 * - Respect program rules (self-referrals, minimum order value already enforced by status)
	 * - Pure completion count (don't subtract reversals)
	 *
	 * @since 1.1.0
	 *
	 * @param string $start_date Start date in 'Y-m-d H:i:s' format.
	 * @param string $end_date   End date in 'Y-m-d H:i:s' format.
	 * @param string $status     Referral status to filter by. Default 'completed'.
	 *
	 * @return int Number of referrals with given status in the period.
	 */
	public function get_referrals_count_by_status( string $start_date, string $end_date, string $status = 'completed' ): int {
		$sql = "
			SELECT COUNT(*) as count
			FROM {$this->referrals_table->get_prefixed_table_name()} r
			INNER JOIN {$this->members_table->get_prefixed_table_name()} m
				ON r.advocate_id = m.id
			WHERE r.status = %s
			  AND r.converted_at >= %s
			  AND r.converted_at <= %s
			  AND m.status IN ('active', 'guest')
		";

		$result = $this->get_var( $sql, [ $status, $start_date, $end_date ] );

		return (int) ( $result ?? 0 );
	}

	/**
	 * Get referrals count grouped by day for sparkline charts.
	 *
	 * Per PDF: Uses converted_at (completion timestamp) and excludes blocked/excluded members.
	 *
	 * Returns an associative array where keys are dates (Y-m-d) and values are counts.
	 * Days without referrals will not be included (caller should fill gaps).
	 *
	 * @since 1.1.0
	 *
	 * @param string $start_date Start date in 'Y-m-d H:i:s' format.
	 * @param string $end_date   End date in 'Y-m-d H:i:s' format.
	 * @param string $status     Referral status to filter by. Default 'completed'.
	 *
	 * @return array Associative array with date as key and count as value.
	 */
	public function get_referrals_by_day( string $start_date, string $end_date, string $status = 'completed' ): array {
		$sql = "
			SELECT DATE(r.converted_at) as date, COUNT(*) as count
			FROM {$this->referrals_table->get_prefixed_table_name()} r
			INNER JOIN {$this->members_table->get_prefixed_table_name()} m
				ON r.advocate_id = m.id
			WHERE r.status = %s
			  AND r.converted_at >= %s
			  AND r.converted_at <= %s
			  AND m.status IN ('active', 'guest')
			GROUP BY DATE(r.converted_at)
			ORDER BY date ASC
		";

		$results = $this->query( $sql, [ $status, $start_date, $end_date ] );

		// Convert to associative array for easier lookup.
		$data = [];
		foreach ( $results as $row ) {
			$data[ $row['date'] ] = (int) $row['count'];
		}

		return $data;
	}

	// ========================================================================
	// PHASE 3: Active Members KPI
	// ========================================================================

	/**
	 * Get the count of active members within a date range.
	 *
	 * Per PDF guidelines, a member is "active" if they performed at least one qualifying action:
	 * - Earned points from program rules (excludes manual admin adjustments)
	 * - Redeemed points for a reward
	 * - Received a referral reward (counts as earn)
	 *
	 * Eligibility:
	 * - Only counts members with status NOT IN ('excluded', 'blocked', 'pending', 'inactive')
	 * - Uses eligible member set at period end
	 *
	 * Exclusions:
	 * - Manual admin adjustments (source_type = 'admin' or 'manual')
	 * - Test/sandbox users
	 * - Pure reversals/expiry with no qualifying action
	 *
	 * @since 1.1.0
	 *
	 * @param string $start_date Start date in 'Y-m-d H:i:s' format.
	 * @param string $end_date   End date in 'Y-m-d H:i:s' format.
	 *
	 * @return int Number of active members in the period.
	 */
	public function get_active_members_count( string $start_date, string $end_date ): int {
		$sql = "
			SELECT COUNT(DISTINCT m.id) as count
			FROM {$this->members_table->get_prefixed_table_name()} m
			WHERE m.status IN ('active', 'guest')
			AND (
				-- Earned points from program rules (exclude admin adjustments).
				EXISTS (
					SELECT 1
					FROM {$this->points_table->get_prefixed_table_name()} p
					WHERE p.member_id = m.id
					  AND p.type = 'earned'
					  AND p.status = 'approved'
					  AND (p.source_type IS NULL OR p.source_type NOT IN ('admin', 'manual'))
					  AND p.created_at >= %s
					  AND p.created_at <= %s
				)
				OR
				-- Redeemed/spent points for a reward.
				EXISTS (
					SELECT 1
					FROM {$this->points_table->get_prefixed_table_name()} p
					WHERE p.member_id = m.id
					  AND p.type = 'spent'
					  AND p.status = 'approved'
					  AND p.created_at >= %s
					  AND p.created_at <= %s
				)
				OR
				-- Received a referral reward (counts as earn).
				EXISTS (
					SELECT 1
					FROM {$this->rewards_table->get_prefixed_table_name()} rw
					WHERE rw.advocate_id = m.id
					  AND rw.status IN ('pending', 'issued', 'redeemed')
					  AND rw.created_at >= %s
					  AND rw.created_at <= %s
				)
			)
		";

		$result = $this->get_var(
			$sql,
			[
				$start_date,
				$end_date, // Earned points.
				$start_date,
				$end_date, // Redeemed points.
				$start_date,
				$end_date, // Referral rewards.
			]
		);

		return (int) ( $result ?? 0 );
	}

	// ========================================================================
	// PHASE 4: Points Activity KPI
	// ========================================================================

	/**
	 * Get the sum of earned points within a date range.
	 *
	 * Per Point.php: type = 'earned' (linha 195-197)
	 * Only counts approved points from program rules (excludes admin adjustments).
	 *
	 * Formula (per PDF): Earned = Σ(earn entries) - Σ(earn reversals).
	 * Earn reversals are recorded as type='spent' with source_type indicating refund/cancellation.
	 *
	 * @since 1.1.0
	 *
	 * @param string $start_date Start date in 'Y-m-d H:i:s' format.
	 * @param string $end_date   End date in 'Y-m-d H:i:s' format.
	 *
	 * @return int Net points earned in the period (earned - reversals).
	 */
	public function get_points_earned( string $start_date, string $end_date ): int {
		// Get total earned points (excluding admin/manual).
		$earned_sql = "
			SELECT COALESCE(SUM(p.points), 0) as total
			FROM {$this->points_table->get_prefixed_table_name()} p
			INNER JOIN {$this->members_table->get_prefixed_table_name()} m
				ON p.member_id = m.id
			WHERE p.type = 'earned'
			  AND p.status = 'approved'
			  AND (p.source_type IS NULL OR p.source_type NOT IN ('admin', 'manual'))
			  AND p.created_at >= %s
			  AND p.created_at <= %s
			  AND m.status IN ('active', 'guest')
		";

		$earned = (int) ( $this->get_var( $earned_sql, [ $start_date, $end_date ] ) ?? 0 );

		// Get earn reversals (spent entries from refunds/cancellations).
		// These are negative adjustments to earned points.
		$reversals_sql = "
			SELECT COALESCE(SUM(p.points), 0) as total
			FROM {$this->points_table->get_prefixed_table_name()} p
			INNER JOIN {$this->members_table->get_prefixed_table_name()} m
				ON p.member_id = m.id
			WHERE p.type = 'spent'
			  AND p.status = 'approved'
			  AND p.source_type IN ('refund_adjustment', 'order_cancelled', 'order_refunded')
			  AND p.created_at >= %s
			  AND p.created_at <= %s
			  AND m.status IN ('active', 'guest')
		";

		$reversals = (int) ( $this->get_var( $reversals_sql, [ $start_date, $end_date ] ) ?? 0 );

		// Net earned = earned - reversals (can't go below 0).
		return (int) max( 0, $earned - $reversals );
	}

	/**
	 * Get the sum of redeemed/spent points within a date range.
	 *
	 * Per Point.php: type = 'spent' (linha 195)
	 * Only counts approved points. Points are stored as positive values.
	 *
	 * Formula (per PDF): Redeemed = Σ(redemptions) - Σ(redemption reversals).
	 * Only counts actual reward redemptions (excludes earn reversals recorded as spent).
	 * Redemption reversals are recorded as type='earned' with source_type indicating reinstatement.
	 *
	 * @since 1.1.0
	 *
	 * @param string $start_date Start date in 'Y-m-d H:i:s' format.
	 * @param string $end_date   End date in 'Y-m-d H:i:s' format.
	 *
	 * @return int Net points redeemed in the period (redemptions - reversals).
	 */
	public function get_points_redeemed( string $start_date, string $end_date ): int {
		// Get total redemptions (spent entries, excluding earn reversals).
		// Earn reversals have specific source_type values and should not count as redemptions.
		$redeemed_sql = "
			SELECT COALESCE(SUM(p.points), 0) as total
			FROM {$this->points_table->get_prefixed_table_name()} p
			INNER JOIN {$this->members_table->get_prefixed_table_name()} m
				ON p.member_id = m.id
			WHERE p.type = 'spent'
			  AND p.status = 'approved'
			  AND (p.source_type IS NULL OR p.source_type NOT IN ('refund_adjustment', 'order_cancelled', 'order_refunded'))
			  AND p.created_at >= %s
			  AND p.created_at <= %s
			  AND m.status IN ('active', 'guest')
		";

		$redeemed = (int) ( $this->get_var( $redeemed_sql, [ $start_date, $end_date ] ) ?? 0 );

		// Get redemption reversals (earned entries from refunded/cancelled rewards).
		// These are points reinstated after a redemption was reversed.
		$reversals_sql = "
			SELECT COALESCE(SUM(p.points), 0) as total
			FROM {$this->points_table->get_prefixed_table_name()} p
			INNER JOIN {$this->members_table->get_prefixed_table_name()} m
				ON p.member_id = m.id
			WHERE p.type = 'earned'
			  AND p.status = 'approved'
			  AND p.source_type IN ('redemption_reversal', 'reward_cancelled', 'reward_refunded')
			  AND p.created_at >= %s
			  AND p.created_at <= %s
			  AND m.status IN ('active', 'guest')
		";

		$reversals = (int) ( $this->get_var( $reversals_sql, [ $start_date, $end_date ] ) ?? 0 );

		// Net redeemed = redeemed - reversals (can't go below 0).
		return (int) max( 0, $redeemed - $reversals );
	}

	/**
	 * Get points earned grouped by day for the chart.
	 *
	 * Returns daily earned points (program credits - reversals) for the specified period.
	 *
	 * @since 1.0.0
	 *
	 * @param string $start_date Start date in 'Y-m-d H:i:s' format.
	 * @param string $end_date   End date in 'Y-m-d H:i:s' format.
	 *
	 * @return array Associative array with date (Y-m-d) as key and net earned points as value.
	 */
	public function get_points_earned_by_day( string $start_date, string $end_date ): array {
		// Get daily earned points (excluding admin/manual).
		$earned_sql = "
			SELECT
				DATE(p.created_at) as date,
				COALESCE(SUM(p.points), 0) as earned
			FROM {$this->points_table->get_prefixed_table_name()} p
			INNER JOIN {$this->members_table->get_prefixed_table_name()} m
				ON p.member_id = m.id
			WHERE p.type = 'earned'
			  AND p.status = 'approved'
			  AND (p.source_type IS NULL OR p.source_type NOT IN ('admin', 'manual'))
			  AND p.created_at >= %s
			  AND p.created_at <= %s
			  AND m.status IN ('active', 'guest')
			GROUP BY DATE(p.created_at)
			ORDER BY date ASC
		";

		$earned_results = $this->query( $earned_sql, [ $start_date, $end_date ] );

		$earned_by_day = [];
		foreach ( $earned_results as $row ) {
			$earned_by_day[ $row['date'] ] = (float) $row['earned'];
		}

		// Get daily earn reversals (spent entries from refunds/cancellations).
		$reversals_sql = "
			SELECT
				DATE(p.created_at) as date,
				COALESCE(SUM(p.points), 0) as reversals
			FROM {$this->points_table->get_prefixed_table_name()} p
			INNER JOIN {$this->members_table->get_prefixed_table_name()} m
				ON p.member_id = m.id
			WHERE p.type = 'spent'
			  AND p.status = 'approved'
			  AND p.source_type IN ('refund_adjustment', 'order_cancelled', 'order_refunded')
			  AND p.created_at >= %s
			  AND p.created_at <= %s
			  AND m.status IN ('active', 'guest')
			GROUP BY DATE(p.created_at)
			ORDER BY date ASC
		";

		$reversals_results = $this->query( $reversals_sql, [ $start_date, $end_date ] );

		$reversals_by_day = [];
		foreach ( $reversals_results as $row ) {
			$reversals_by_day[ $row['date'] ] = (float) $row['reversals'];
		}

		// Combine earned and reversals: net = earned - reversals.
		$all_dates = array_unique( array_merge( array_keys( $earned_by_day ), array_keys( $reversals_by_day ) ) );
		sort( $all_dates );

		$net_by_day = [];
		foreach ( $all_dates as $date ) {
			$earned              = $earned_by_day[ $date ] ?? 0.0;
			$reversals           = $reversals_by_day[ $date ] ?? 0.0;
			$net_by_day[ $date ] = max( 0, $earned - $reversals );
		}

		return $net_by_day;
	}

	/**
	 * Get points redeemed grouped by day for the chart.
	 *
	 * Returns daily redeemed points (redemptions - reversals) for the specified period.
	 *
	 * @since 1.0.0
	 *
	 * @param string $start_date Start date in 'Y-m-d H:i:s' format.
	 * @param string $end_date   End date in 'Y-m-d H:i:s' format.
	 *
	 * @return array Associative array with date (Y-m-d) as key and net redeemed points as value.
	 */
	public function get_points_redeemed_by_day( string $start_date, string $end_date ): array {
		// Get daily redemptions (spent entries, excluding earn reversals).
		$redeemed_sql = "
			SELECT
				DATE(p.created_at) as date,
				COALESCE(SUM(p.points), 0) as redeemed
			FROM {$this->points_table->get_prefixed_table_name()} p
			INNER JOIN {$this->members_table->get_prefixed_table_name()} m
				ON p.member_id = m.id
			WHERE p.type = 'spent'
			  AND p.status = 'approved'
			  AND (p.source_type IS NULL OR p.source_type NOT IN ('refund_adjustment', 'order_cancelled', 'order_refunded'))
			  AND p.created_at >= %s
			  AND p.created_at <= %s
			  AND m.status IN ('active', 'guest')
			GROUP BY DATE(p.created_at)
			ORDER BY date ASC
		";

		$redeemed_results = $this->query( $redeemed_sql, [ $start_date, $end_date ] );

		$redeemed_by_day = [];
		foreach ( $redeemed_results as $row ) {
			$redeemed_by_day[ $row['date'] ] = (float) $row['redeemed'];
		}

		// Get daily redemption reversals (earned entries from refunded/cancelled rewards).
		$reversals_sql = "
			SELECT
				DATE(p.created_at) as date,
				COALESCE(SUM(p.points), 0) as reversals
			FROM {$this->points_table->get_prefixed_table_name()} p
			INNER JOIN {$this->members_table->get_prefixed_table_name()} m
				ON p.member_id = m.id
			WHERE p.type = 'earned'
			  AND p.status = 'approved'
			  AND p.source_type IN ('redemption_reversal', 'reward_cancelled', 'reward_refunded')
			  AND p.created_at >= %s
			  AND p.created_at <= %s
			  AND m.status IN ('active', 'guest')
			GROUP BY DATE(p.created_at)
			ORDER BY date ASC
		";

		$reversals_results = $this->query( $reversals_sql, [ $start_date, $end_date ] );

		$reversals_by_day = [];
		foreach ( $reversals_results as $row ) {
			$reversals_by_day[ $row['date'] ] = (float) $row['reversals'];
		}

		// Combine redeemed and reversals: net = redeemed - reversals.
		$all_dates = array_unique( array_merge( array_keys( $redeemed_by_day ), array_keys( $reversals_by_day ) ) );
		sort( $all_dates );

		$net_by_day = [];
		foreach ( $all_dates as $date ) {
			$redeemed            = $redeemed_by_day[ $date ] ?? 0.0;
			$reversals           = $reversals_by_day[ $date ] ?? 0.0;
			$net_by_day[ $date ] = max( 0, $redeemed - $reversals );
		}

		return $net_by_day;
	}

	// ========================================================================
	// PHASE 5: First-time Redeemers KPI
	// ========================================================================

	/**
	 * Get count of members whose first-ever redemption happened in the period.
	 *
	 * Per PDF guidelines:
	 * - Only member-initiated redemptions (exclude admin/manual adjustments)
	 * - Exclude test/sandbox users and blocked/deleted members
	 * - If first redemption was fully reversed, use next earliest non-reversed
	 *
	 * Formula: Count members whose earliest valid redemption falls in [start_date, end_date].
	 *
	 * @since 1.1.0
	 *
	 * @param string $start_date Start date in 'Y-m-d H:i:s' format.
	 * @param string $end_date   End date in 'Y-m-d H:i:s' format.
	 *
	 * @return int Count of first-time redeemers.
	 */
	public function get_first_time_redeemers_count( string $start_date, string $end_date ): int {
		// Find members whose first valid redemption (type='spent', excluding reversals) is in the period.
		// We exclude admin/manual and earn reversals (refund_adjustment, etc.).
		$sql = "
			SELECT COUNT(*) as count
			FROM (
				SELECT
					p.member_id,
					MIN(p.created_at) as first_redemption_date
				FROM {$this->points_table->get_prefixed_table_name()} p
				INNER JOIN {$this->members_table->get_prefixed_table_name()} m
					ON p.member_id = m.id
				WHERE p.type = 'spent'
				  AND p.status = 'approved'
				  AND (p.source_type IS NULL OR p.source_type NOT IN ('admin', 'manual', 'refund_adjustment', 'order_cancelled', 'order_refunded'))
				  AND m.status IN ('active', 'guest')
				GROUP BY p.member_id
			) first_redeems
			WHERE first_redemption_date >= %s
			  AND first_redemption_date <= %s
		";

		$result = $this->get_var( $sql, [ $start_date, $end_date ] );

		return (int) ( $result ?? 0 );
	}

	/**
	 * Get days-to-first-redemption data for first-time redeemers in a period.
	 *
	 * Returns array of days (integers) representing time from first earn to first redeem
	 * for members whose first redemption happened in the period.
	 *
	 * Per PDF: Only include members who have a valid earn on or before their first redemption.
	 * Members with no prior earn are excluded from the sample.
	 *
	 * @since 1.1.0
	 *
	 * @param string $start_date Start date in 'Y-m-d H:i:s' format.
	 * @param string $end_date   End date in 'Y-m-d H:i:s' format.
	 *
	 * @return array Array of integers (days between first earn and first redeem).
	 */
	public function get_days_to_first_redemption( string $start_date, string $end_date ): array {
		// Get members whose first redemption is in the period,
		// and calculate days from their first earn to first redeem.
		$sql = "
			SELECT
				TIMESTAMPDIFF(DAY, first_earn.first_earn_date, first_redeem.first_redemption_date) as days_to_redeem
			FROM (
				-- First valid redemption per member.
				SELECT
					p.member_id,
					MIN(p.created_at) as first_redemption_date
				FROM {$this->points_table->get_prefixed_table_name()} p
				INNER JOIN {$this->members_table->get_prefixed_table_name()} m
					ON p.member_id = m.id
				WHERE p.type = 'spent'
				  AND p.status = 'approved'
				  AND (p.source_type IS NULL OR p.source_type NOT IN ('admin', 'manual', 'refund_adjustment', 'order_cancelled', 'order_refunded'))
				  AND m.status IN ('active', 'guest')
				GROUP BY p.member_id
			) first_redeem
			INNER JOIN (
				-- First valid earn per member (excluding admin/manual).
				SELECT
					p.member_id,
					MIN(p.created_at) as first_earn_date
				FROM {$this->points_table->get_prefixed_table_name()} p
				WHERE p.type = 'earned'
				  AND p.status = 'approved'
				  AND (p.source_type IS NULL OR p.source_type NOT IN ('admin', 'manual'))
				GROUP BY p.member_id
			) first_earn
			ON first_redeem.member_id = first_earn.member_id
			WHERE first_redeem.first_redemption_date >= %s
			  AND first_redeem.first_redemption_date <= %s
			  AND first_earn.first_earn_date <= first_redeem.first_redemption_date
		";

		$results = $this->query( $sql, [ $start_date, $end_date ] );

		// Extract days_to_redeem values into a simple array.
		$days = [];
		if ( ! empty( $results ) ) {
			foreach ( $results as $row ) {
				$days[] = (int) $row['days_to_redeem'];
			}
		}

		return $days;
	}

	// ========================================================================
	// PHASE 6: Orders Using Rewards KPI
	// ========================================================================

	/**
	 * Get count of orders that used RewardsWP rewards.
	 *
	 * Per PDF: Orders that had points redemption OR referral.
	 *
	 * Detection methods:
	 * 1. Points redemption: Point (spent) → Reward → Coupon → Order
	 * 2. Referral: Order has referral (referrals.order_id matches)
	 *
	 * @since 1.1.0
	 *
	 * @param string $start_date Start date in 'Y-m-d H:i:s' format.
	 * @param string $end_date   End date in 'Y-m-d H:i:s' format.
	 *
	 * @return int Count of orders using rewards.
	 */
	public function get_orders_using_rewards_count( string $start_date, string $end_date ): int {
		global $wpdb;

		// Get current integration source.
		$integration_source = $this->integration->get_source();

		// Determine table based on integration and HPOS.
		// For WooCommerce, check if HPOS is enabled via the integration helper.
		$is_hpos = false;
		if ( 'woocommerce' === $integration_source && method_exists( $this->integration, 'is_hpos_enabled' ) ) {
			$is_hpos = $this->integration->is_hpos_enabled();
		}

		if ( 'woocommerce' === $integration_source ) {
			if ( $is_hpos ) {
				// HPOS mode.
				$orders_table = $wpdb->prefix . 'wc_orders';
				$date_field   = 'date_created_gmt';
				$status_field = 'status';
				$statuses     = "'wc-processing', 'wc-completed'";

				// HPOS uses wp_wc_order_coupon_lookup table.
				$sql = "
					SELECT COUNT(DISTINCT o.id) as count
					FROM {$orders_table} o
					WHERE o.{$date_field} >= %s
					  AND o.{$date_field} <= %s
					  AND o.{$status_field} IN ({$statuses})
					  AND (
					      -- Order has points redemption: Point → Reward → Coupon → Order
					      EXISTS (
					          SELECT 1
					          FROM {$this->points_table->get_prefixed_table_name()} p
					          INNER JOIN {$this->rewards_table->get_prefixed_table_name()} r
					              ON p.source_id = r.id
					          INNER JOIN {$this->rewards_table->get_prefixed_meta_table_name()} rm
					              ON r.id = rm.reward_id AND rm.meta_key = 'coupon_code'
					          INNER JOIN {$wpdb->posts} cp
					              ON cp.post_type = 'shop_coupon' AND cp.post_title = rm.meta_value
					          INNER JOIN {$wpdb->prefix}wc_order_coupon_lookup ocl
					              ON ocl.coupon_id = cp.ID
					          WHERE p.type = 'spent'
					            AND p.source_type = 'reward_redemption'
					            AND p.status = 'approved'
					            AND ocl.order_id = o.id
					      )
					      OR
					      -- Order has referral
					      EXISTS (
					          SELECT 1
					          FROM {$this->referrals_table->get_prefixed_table_name()} r
					          WHERE r.order_id = o.id
					            AND r.source = %s
					      )
					  )
				";
			} else {
				// Legacy mode.
				$orders_table = $wpdb->posts;
				$date_field   = 'post_date_gmt';
				$status_field = 'post_status';
				$statuses     = "'wc-processing', 'wc-completed'";

				// Legacy uses order items in postmeta.
				$sql = "
					SELECT COUNT(DISTINCT o.ID) as count
					FROM {$orders_table} o
					WHERE {$date_field} >= %s
					  AND {$date_field} <= %s
					  AND o.post_type = 'shop_order'
					  AND {$status_field} IN ({$statuses})
					  AND (
					      -- Order has points redemption: Point → Reward → Coupon → Order
					      EXISTS (
					          SELECT 1
					          FROM {$this->points_table->get_prefixed_table_name()} p
					          INNER JOIN {$this->rewards_table->get_prefixed_table_name()} r
					              ON p.source_id = r.id
					          INNER JOIN {$this->rewards_table->get_prefixed_meta_table_name()} rm
					              ON r.id = rm.reward_id AND rm.meta_key = 'coupon_code'
					          INNER JOIN {$wpdb->posts} cp
					              ON cp.post_type = 'shop_coupon' AND cp.post_title = rm.meta_value
					          INNER JOIN {$wpdb->prefix}woocommerce_order_items oi
					              ON oi.order_id = o.ID AND oi.order_item_type = 'coupon' AND oi.order_item_name = cp.post_title
					          WHERE p.type = 'spent'
					            AND p.source_type = 'reward_redemption'
					            AND p.status = 'approved'
					      )
					      OR
					      -- Order has referral
					      EXISTS (
					          SELECT 1
					          FROM {$this->referrals_table->get_prefixed_table_name()} r
					          WHERE r.order_id = o.ID
					            AND r.source = %s
					      )
					  )
				";
			}
		} else {
			// EDD.
			$orders_table = $wpdb->posts;
			$date_field   = 'post_date_gmt';
			$status_field = 'post_status';
			$statuses     = "'publish', 'edd_subscription'";

			// EDD stores coupons in _edd_payment_user_info meta (discount field).
			$sql = "
				SELECT COUNT(DISTINCT o.ID) as count
				FROM {$orders_table} o
				WHERE {$date_field} >= %s
				  AND {$date_field} <= %s
				  AND o.post_type = 'edd_payment'
				  AND {$status_field} IN ({$statuses})
				  AND (
				      -- Order has points redemption: Point → Reward → Coupon (via user_info discount)
				      EXISTS (
				          SELECT 1
				          FROM {$this->points_table->get_prefixed_table_name()} p
				          INNER JOIN {$this->rewards_table->get_prefixed_table_name()} r
				              ON p.source_id = r.id
				          INNER JOIN {$this->rewards_table->get_prefixed_meta_table_name()} rm
				              ON r.id = rm.reward_id AND rm.meta_key = 'coupon_code'
				          INNER JOIN {$wpdb->postmeta} pm_user_info
				              ON pm_user_info.post_id = o.ID
				              AND pm_user_info.meta_key = '_edd_payment_user_info'
				          WHERE p.type = 'spent'
				            AND p.source_type = 'reward_redemption'
				            AND p.status = 'approved'
				            AND pm_user_info.meta_value LIKE CONCAT('%%', rm.meta_value, '%%')
				      )
				      OR
				      -- Order has referral
				      EXISTS (
				          SELECT 1
				          FROM {$this->referrals_table->get_prefixed_table_name()} r
				          WHERE r.order_id = o.ID
				            AND r.source = %s
				      )
				  )
			";
		}

		$result = $this->get_var(
			$sql,
			[
				$start_date,
				$end_date,
				$integration_source,
			]
		);

		return (int) ( $result ?? 0 );
	}

	/**
	 * Get count of total eligible orders (all orders in period with valid status).
	 *
	 * @since 1.1.0
	 *
	 * @param string $start_date Start date in 'Y-m-d H:i:s' format.
	 * @param string $end_date   End date in 'Y-m-d H:i:s' format.
	 *
	 * @return int Count of total eligible orders.
	 */
	public function get_total_eligible_orders_count( string $start_date, string $end_date ): int {
		global $wpdb;

		// Get current integration source.
		$integration_source = $this->integration->get_source();

		// Determine table based on integration and HPOS.
		// For WooCommerce, check if HPOS is enabled via the integration helper.
		$is_hpos = false;
		if ( 'woocommerce' === $integration_source && method_exists( $this->integration, 'is_hpos_enabled' ) ) {
			$is_hpos = $this->integration->is_hpos_enabled();
		}

		if ( 'woocommerce' === $integration_source ) {
			if ( $is_hpos ) {
				// HPOS mode.
				$orders_table = $wpdb->prefix . 'wc_orders';
				$date_field   = 'date_created_gmt';
				$status_field = 'status';
				$statuses     = "'wc-processing', 'wc-completed'";
			} else {
				// Legacy mode.
				$orders_table = $wpdb->posts;
				$date_field   = 'post_date_gmt';
				$status_field = 'post_status';
				$statuses     = "'wc-processing', 'wc-completed'";
			}
		} else {
			// EDD.
			$orders_table = $wpdb->posts;
			$date_field   = 'post_date_gmt';
			$status_field = 'post_status';
			$statuses     = "'publish', 'edd_subscription'";
		}

		// Build WHERE clause based on table type.
		if ( 'woocommerce' === $integration_source && ! $is_hpos ) {
			// Legacy WC: Need to add post_type filter and use o. prefix.
			$sql = "
				SELECT COUNT(*) as count
				FROM {$orders_table} o
				WHERE o.{$date_field} >= %s
				  AND o.{$date_field} <= %s
				  AND o.post_type = 'shop_order'
				  AND o.{$status_field} IN ({$statuses})
			";
		} elseif ( 'easy_digital_downloads' === $integration_source ) {
			// EDD: Need to add post_type filter and use o. prefix.
			$sql = "
				SELECT COUNT(*) as count
				FROM {$orders_table} o
				WHERE o.{$date_field} >= %s
				  AND o.{$date_field} <= %s
				  AND o.post_type = 'edd_payment'
				  AND o.{$status_field} IN ({$statuses})
			";
		} else {
			// HPOS: No post_type needed, use o. prefix for consistency.
			$sql = "
				SELECT COUNT(*) as count
				FROM {$orders_table} o
				WHERE o.{$date_field} >= %s
				  AND o.{$date_field} <= %s
				  AND o.{$status_field} IN ({$statuses})
			";
		}

		$result = $this->get_var( $sql, [ $start_date, $end_date ] );

		return (int) ( $result ?? 0 );
	}

	/**
	 * Get daily count of orders using rewards.
	 *
	 * Returns the count of orders that used RewardsWP incentives (points redemption
	 * or referral rewards) grouped by date for time-series charts.
	 *
	 * @since 1.1.0
	 *
	 * @param string $start_date Start date in 'Y-m-d H:i:s' format.
	 * @param string $end_date   End date in 'Y-m-d H:i:s' format.
	 *
	 * @return array Associative array with date keys (Y-m-d) and count values.
	 */
	public function get_orders_using_rewards_by_day( string $start_date, string $end_date ): array {
		global $wpdb;

		// Get current integration source.
		$integration_source = $this->integration->get_source();

		// Determine table based on integration and HPOS.
		$is_hpos = false;
		if ( 'woocommerce' === $integration_source && method_exists( $this->integration, 'is_hpos_enabled' ) ) {
			$is_hpos = $this->integration->is_hpos_enabled();
		}

		if ( 'woocommerce' === $integration_source ) {
			if ( $is_hpos ) {
				// HPOS mode.
				$orders_table = $wpdb->prefix . 'wc_orders';
				$date_field   = 'date_created_gmt';
				$status_field = 'status';
				$statuses     = "'wc-processing', 'wc-completed'";

				$sql = "
					SELECT DATE(o.{$date_field}) as date, COUNT(DISTINCT o.id) as count
					FROM {$orders_table} o
					WHERE o.{$date_field} >= %s
					  AND o.{$date_field} <= %s
					  AND o.{$status_field} IN ({$statuses})
					  AND (
					      EXISTS (
					          SELECT 1
					          FROM {$this->points_table->get_prefixed_table_name()} p
					          INNER JOIN {$this->rewards_table->get_prefixed_table_name()} r
					              ON p.source_id = r.id
					          INNER JOIN {$this->rewards_table->get_prefixed_meta_table_name()} rm
					              ON r.id = rm.reward_id AND rm.meta_key = 'coupon_code'
					          INNER JOIN {$wpdb->posts} cp
					              ON cp.post_type = 'shop_coupon' AND cp.post_title = rm.meta_value
					          INNER JOIN {$wpdb->prefix}wc_order_coupon_lookup ocl
					              ON ocl.coupon_id = cp.ID
					          WHERE p.type = 'spent'
					            AND p.source_type = 'reward_redemption'
					            AND p.status = 'approved'
					            AND ocl.order_id = o.id
					      )
					      OR
					      EXISTS (
					          SELECT 1
					          FROM {$this->referrals_table->get_prefixed_table_name()} r
					          WHERE r.order_id = o.id
					            AND r.source = %s
					      )
					  )
					GROUP BY DATE(o.{$date_field})
					ORDER BY date ASC
				";
			} else {
				// Legacy mode.
				$orders_table = $wpdb->posts;
				$date_field   = 'post_date_gmt';
				$status_field = 'post_status';
				$statuses     = "'wc-processing', 'wc-completed'";

				$sql = "
					SELECT DATE(o.{$date_field}) as date, COUNT(DISTINCT o.ID) as count
					FROM {$orders_table} o
					WHERE o.{$date_field} >= %s
					  AND o.{$date_field} <= %s
					  AND o.post_type = 'shop_order'
					  AND o.{$status_field} IN ({$statuses})
					  AND (
					      EXISTS (
					          SELECT 1
					          FROM {$this->points_table->get_prefixed_table_name()} p
					          INNER JOIN {$this->rewards_table->get_prefixed_table_name()} r
					              ON p.source_id = r.id
					          INNER JOIN {$this->rewards_table->get_prefixed_meta_table_name()} rm
					              ON r.id = rm.reward_id AND rm.meta_key = 'coupon_code'
					          INNER JOIN {$wpdb->posts} cp
					              ON cp.post_type = 'shop_coupon' AND cp.post_title = rm.meta_value
					          INNER JOIN {$wpdb->prefix}woocommerce_order_items oi
					              ON oi.order_id = o.ID AND oi.order_item_type = 'coupon' AND oi.order_item_name = cp.post_title
					          WHERE p.type = 'spent'
					            AND p.source_type = 'reward_redemption'
					            AND p.status = 'approved'
					      )
					      OR
					      EXISTS (
					          SELECT 1
					          FROM {$this->referrals_table->get_prefixed_table_name()} r
					          WHERE r.order_id = o.ID
					            AND r.source = %s
					      )
					  )
					GROUP BY DATE(o.{$date_field})
					ORDER BY date ASC
				";
			}
		} else {
			// EDD.
			$orders_table = $wpdb->posts;
			$date_field   = 'post_date_gmt';
			$status_field = 'post_status';
			$statuses     = "'publish', 'edd_subscription'";

			$sql = "
				SELECT DATE(o.{$date_field}) as date, COUNT(DISTINCT o.ID) as count
				FROM {$orders_table} o
				WHERE o.{$date_field} >= %s
				  AND o.{$date_field} <= %s
				  AND o.post_type = 'edd_payment'
				  AND o.{$status_field} IN ({$statuses})
				  AND (
				      EXISTS (
				          SELECT 1
				          FROM {$this->points_table->get_prefixed_table_name()} p
				          INNER JOIN {$this->rewards_table->get_prefixed_table_name()} r
				              ON p.source_id = r.id
				          INNER JOIN {$this->rewards_table->get_prefixed_meta_table_name()} rm
				              ON r.id = rm.reward_id AND rm.meta_key = 'coupon_code'
				          INNER JOIN {$wpdb->postmeta} pm_user_info
				              ON pm_user_info.post_id = o.ID
				              AND pm_user_info.meta_key = '_edd_payment_user_info'
				          WHERE p.type = 'spent'
				            AND p.source_type = 'reward_redemption'
				            AND p.status = 'approved'
				            AND pm_user_info.meta_value LIKE CONCAT('%%', rm.meta_value, '%%')
				      )
				      OR
				      EXISTS (
				          SELECT 1
				          FROM {$this->referrals_table->get_prefixed_table_name()} r
				          WHERE r.order_id = o.ID
				            AND r.source = %s
				      )
				  )
				GROUP BY DATE(o.{$date_field})
				ORDER BY date ASC
			";
		}

		$results = $this->query(
			$sql,
			[
				$start_date,
				$end_date,
				$integration_source,
			]
		);

		// Convert to associative array with date keys.
		$data = [];
		if ( $results ) {
			foreach ( $results as $row ) {
				$data[ $row['date'] ] = (int) $row['count'];
			}
		}

		return $data;
	}

	/**
	 * Get daily count of total eligible orders.
	 *
	 * Returns the count of all orders with valid statuses grouped by date
	 * for time-series charts.
	 *
	 * @since 1.1.0
	 *
	 * @param string $start_date Start date in 'Y-m-d H:i:s' format.
	 * @param string $end_date   End date in 'Y-m-d H:i:s' format.
	 *
	 * @return array Associative array with date keys (Y-m-d) and count values.
	 */
	public function get_total_eligible_orders_by_day( string $start_date, string $end_date ): array {
		global $wpdb;

		// Get current integration source.
		$integration_source = $this->integration->get_source();

		// Determine table based on integration and HPOS.
		$is_hpos = false;
		if ( 'woocommerce' === $integration_source && method_exists( $this->integration, 'is_hpos_enabled' ) ) {
			$is_hpos = $this->integration->is_hpos_enabled();
		}

		if ( 'woocommerce' === $integration_source ) {
			if ( $is_hpos ) {
				// HPOS mode.
				$orders_table = $wpdb->prefix . 'wc_orders';
				$date_field   = 'date_created_gmt';
				$status_field = 'status';
				$statuses     = "'wc-processing', 'wc-completed'";

				$sql = "
					SELECT DATE(o.{$date_field}) as date, COUNT(*) as count
					FROM {$orders_table} o
					WHERE o.{$date_field} >= %s
					  AND o.{$date_field} <= %s
					  AND o.{$status_field} IN ({$statuses})
					GROUP BY DATE(o.{$date_field})
					ORDER BY date ASC
				";
			} else {
				// Legacy mode.
				$orders_table = $wpdb->posts;
				$date_field   = 'post_date_gmt';
				$status_field = 'post_status';
				$statuses     = "'wc-processing', 'wc-completed'";

				$sql = "
					SELECT DATE(o.{$date_field}) as date, COUNT(*) as count
					FROM {$orders_table} o
					WHERE o.{$date_field} >= %s
					  AND o.{$date_field} <= %s
					  AND o.post_type = 'shop_order'
					  AND o.{$status_field} IN ({$statuses})
					GROUP BY DATE(o.{$date_field})
					ORDER BY date ASC
				";
			}
		} else {
			// EDD.
			$orders_table = $wpdb->posts;
			$date_field   = 'post_date_gmt';
			$status_field = 'post_status';
			$statuses     = "'publish', 'edd_subscription'";

			$sql = "
				SELECT DATE(o.{$date_field}) as date, COUNT(*) as count
				FROM {$orders_table} o
				WHERE o.{$date_field} >= %s
				  AND o.{$date_field} <= %s
				  AND o.post_type = 'edd_payment'
				  AND o.{$status_field} IN ({$statuses})
				GROUP BY DATE(o.{$date_field})
				ORDER BY date ASC
			";
		}

		$results = $this->query( $sql, [ $start_date, $end_date ] );

		// Convert to associative array with date keys.
		$data = [];
		if ( $results ) {
			foreach ( $results as $row ) {
				$data[ $row['date'] ] = (int) $row['count'];
			}
		}

		return $data;
	}

	/**
	 * Get rewards-applied revenue for the given period.
	 *
	 * Returns product sales after discounts, refunds, total discounts, and net revenue
	 * for orders that used RewardsWP incentives (points redemption or referrals).
	 *
	 * Formula: Net Revenue = Product Sales After Discounts − Product Refunds Posted
	 *
	 * Product Sales After Discounts:
	 * - WooCommerce HPOS: Uses total_amount (includes everything)
	 * - WooCommerce Legacy: Order Total − Shipping − Tax
	 * - EDD: Uses total (digital products, no shipping)
	 *
	 * @since 1.0.0
	 *
	 * @param string $start_date Start date (Y-m-d H:i:s format).
	 * @param string $end_date   End date (Y-m-d H:i:s format).
	 *
	 * @return array {
	 *     @type float $product_sales Eligible product sales after discounts.
	 *     @type float $refunds       Product refunds posted in period.
	 *     @type float $discounts     Total discounts applied (for footer display).
	 *     @type float $net_revenue   Net revenue (product_sales - refunds).
	 * }
	 */
	public function get_rewards_applied_revenue( string $start_date, string $end_date ): array {
		global $wpdb;

		// Get current integration source.
		$integration_source = $this->integration->get_source();

		// Determine table based on integration and HPOS.
		$is_hpos = false;
		if ( 'woocommerce' === $integration_source && method_exists( $this->integration, 'is_hpos_enabled' ) ) {
			$is_hpos = $this->integration->is_hpos_enabled();
		}

		$product_sales = 0.0;
		$refunds       = 0.0;
		$discounts     = 0.0;

		if ( 'woocommerce' === $integration_source ) {
			if ( $is_hpos ) {
				// HPOS mode.
				$orders_table = $wpdb->prefix . 'wc_orders';

				// Get product sales for eligible orders.
				// Note: HPOS stores totals in wp_wc_orders.total_amount.
				// For discounts, we'll calculate from order meta or set to 0.
				$sql = "
					SELECT
						COALESCE(SUM(o.total_amount), 0) as product_sales,
						0 as discounts
					FROM {$orders_table} o
					WHERE o.date_created_gmt >= %s
					  AND o.date_created_gmt <= %s
					  AND o.status IN ('wc-processing', 'wc-completed')
					  AND o.type = 'shop_order'
					  AND (
						  -- Order has points redemption: Point → Reward → Coupon → Order
						  EXISTS (
							  SELECT 1
							  FROM {$this->points_table->get_prefixed_table_name()} p
							  INNER JOIN {$this->rewards_table->get_prefixed_table_name()} r
								  ON p.source_id = r.id
							  INNER JOIN {$this->rewards_table->get_prefixed_meta_table_name()} rm
								  ON r.id = rm.reward_id AND rm.meta_key = 'coupon_code'
							  INNER JOIN {$wpdb->posts} cp
								  ON cp.post_type = 'shop_coupon' AND cp.post_title = rm.meta_value
							  INNER JOIN {$wpdb->prefix}wc_order_coupon_lookup ocl
								  ON ocl.coupon_id = cp.ID
							  WHERE p.type = 'spent'
								AND p.source_type = 'reward_redemption'
								AND p.status = 'approved'
								AND ocl.order_id = o.id
						  )
						  OR
						  -- Order has referral
						  EXISTS (
							  SELECT 1
							  FROM {$this->referrals_table->get_prefixed_table_name()} r
							  WHERE r.order_id = o.id
								AND r.source = %s
						  )
					  )
				";

				$results = $this->query( $sql, [ $start_date, $end_date, $integration_source ] );
				$row     = $results[0] ?? [];

				$product_sales = isset( $row['product_sales'] ) ? (float) $row['product_sales'] : 0.0;
				$discounts     = isset( $row['discounts'] ) ? (float) $row['discounts'] : 0.0;

				// Get refunds for eligible orders.
				$sql_refunds = "
					SELECT COALESCE(ABS(SUM(r.total_amount)), 0) as refunds_total
					FROM {$orders_table} r
					WHERE r.type = 'shop_order_refund'
					  AND r.date_created_gmt >= %s
					  AND r.date_created_gmt <= %s
					  AND r.parent_order_id IN (
						  SELECT o.id
						  FROM {$orders_table} o
						  WHERE o.date_created_gmt >= %s
							AND o.date_created_gmt <= %s
							AND o.status IN ('wc-processing', 'wc-completed')
							AND o.type = 'shop_order'
							AND (
								EXISTS (
									SELECT 1
									FROM {$this->points_table->get_prefixed_table_name()} p
									INNER JOIN {$this->rewards_table->get_prefixed_table_name()} r
										ON p.source_id = r.id
									INNER JOIN {$this->rewards_table->get_prefixed_meta_table_name()} rm
										ON r.id = rm.reward_id AND rm.meta_key = 'coupon_code'
									INNER JOIN {$wpdb->posts} cp
										ON cp.post_type = 'shop_coupon' AND cp.post_title = rm.meta_value
									INNER JOIN {$wpdb->prefix}wc_order_coupon_lookup ocl
										ON ocl.coupon_id = cp.ID
									WHERE p.type = 'spent'
									  AND p.source_type = 'reward_redemption'
									  AND p.status = 'approved'
									  AND ocl.order_id = o.id
								)
								OR
								EXISTS (
									SELECT 1
									FROM {$this->referrals_table->get_prefixed_table_name()} r
									WHERE r.order_id = o.id
									  AND r.source = %s
								)
							)
					  )
				";

				$refunds = (float) $this->get_var(
					$sql_refunds,
					[ $start_date, $end_date, $start_date, $end_date, $integration_source ]
				);

			} else {
				// Legacy mode.
				$orders_table = $wpdb->posts;

				// Get product sales for eligible orders.
				$sql = "
					SELECT
						COALESCE(SUM(
							CAST(pm_total.meta_value AS DECIMAL(10,2))
							- COALESCE(CAST(pm_shipping.meta_value AS DECIMAL(10,2)), 0)
							- COALESCE(CAST(pm_tax.meta_value AS DECIMAL(10,2)), 0)
						), 0) as product_sales,
						COALESCE(SUM(CAST(pm_discount.meta_value AS DECIMAL(10,2))), 0) as discounts
					FROM {$orders_table} o
					LEFT JOIN {$wpdb->postmeta} pm_total
						ON o.ID = pm_total.post_id AND pm_total.meta_key = '_order_total'
					LEFT JOIN {$wpdb->postmeta} pm_shipping
						ON o.ID = pm_shipping.post_id AND pm_shipping.meta_key = '_order_shipping'
					LEFT JOIN {$wpdb->postmeta} pm_tax
						ON o.ID = pm_tax.post_id AND pm_tax.meta_key = '_order_tax'
					LEFT JOIN {$wpdb->postmeta} pm_discount
						ON o.ID = pm_discount.post_id AND pm_discount.meta_key = '_cart_discount'
					WHERE o.post_type = 'shop_order'
					  AND o.post_date_gmt >= %s
					  AND o.post_date_gmt <= %s
					  AND o.post_status IN ('wc-processing', 'wc-completed')
					  AND (
						  -- Order has points redemption
						  EXISTS (
							  SELECT 1
							  FROM {$this->points_table->get_prefixed_table_name()} p
							  INNER JOIN {$this->rewards_table->get_prefixed_table_name()} r
								  ON p.source_id = r.id
							  INNER JOIN {$this->rewards_table->get_prefixed_meta_table_name()} rm
								  ON r.id = rm.reward_id AND rm.meta_key = 'coupon_code'
							  INNER JOIN {$wpdb->posts} cp
								  ON cp.post_type = 'shop_coupon' AND cp.post_title = rm.meta_value
							  INNER JOIN {$wpdb->postmeta} pm_coupons
								  ON o.ID = pm_coupons.post_id
								  AND pm_coupons.meta_key = '_order_coupon_codes'
								  AND pm_coupons.meta_value LIKE CONCAT('%', cp.post_title, '%')
							  WHERE p.type = 'spent'
								AND p.source_type = 'reward_redemption'
								AND p.status = 'approved'
						  )
						  OR
						  -- Order has referral
						  EXISTS (
							  SELECT 1
							  FROM {$this->referrals_table->get_prefixed_table_name()} r
							  WHERE r.order_id = o.ID
								AND r.source = %s
						  )
					  )
				";

				$results = $this->query( $sql, [ $start_date, $end_date, $integration_source ] );
				$row     = $results[0] ?? [];

				$product_sales = isset( $row['product_sales'] ) ? (float) $row['product_sales'] : 0.0;
				$discounts     = isset( $row['discounts'] ) ? (float) $row['discounts'] : 0.0;

				// Get refunds for eligible orders.
				$sql_refunds = "
					SELECT COALESCE(ABS(SUM(CAST(pm.meta_value AS DECIMAL(10,2)))), 0) as refunds_total
					FROM {$orders_table} r
					INNER JOIN {$wpdb->postmeta} pm
						ON r.ID = pm.post_id AND pm.meta_key = '_order_total'
					WHERE r.post_type = 'shop_order_refund'
					  AND r.post_date_gmt >= %s
					  AND r.post_date_gmt <= %s
					  AND r.post_parent IN (
						  SELECT o.ID
						  FROM {$orders_table} o
						  WHERE o.post_type = 'shop_order'
							AND o.post_date_gmt >= %s
							AND o.post_date_gmt <= %s
							AND o.post_status IN ('wc-processing', 'wc-completed')
							AND (
								EXISTS (
									SELECT 1
									FROM {$this->points_table->get_prefixed_table_name()} p
									INNER JOIN {$this->rewards_table->get_prefixed_table_name()} r
										ON p.source_id = r.id
									INNER JOIN {$this->rewards_table->get_prefixed_meta_table_name()} rm
										ON r.id = rm.reward_id AND rm.meta_key = 'coupon_code'
									INNER JOIN {$wpdb->posts} cp
										ON cp.post_type = 'shop_coupon' AND cp.post_title = rm.meta_value
									INNER JOIN {$wpdb->postmeta} pm_coupons
										ON o.ID = pm_coupons.post_id
										AND pm_coupons.meta_key = '_order_coupon_codes'
										AND pm_coupons.meta_value LIKE CONCAT('%', cp.post_title, '%')
									WHERE p.type = 'spent'
									  AND p.source_type = 'reward_redemption'
									  AND p.status = 'approved'
								)
								OR
								EXISTS (
									SELECT 1
									FROM {$this->referrals_table->get_prefixed_table_name()} r
									WHERE r.order_id = o.ID
									  AND r.source = %s
								)
							)
					  )
				";

				$refunds = (float) $this->get_var(
					$sql_refunds,
					[ $start_date, $end_date, $start_date, $end_date, $integration_source ]
				);
			}
		} elseif ( 'edd' === $integration_source ) {
			// EDD mode.
			$orders_table = $wpdb->prefix . 'edd_orders';

			// Get product sales for eligible orders (EDD total already excludes shipping/tax for digital products).
			$sql = "
				SELECT
					COALESCE(SUM(o.total), 0) as product_sales,
					COALESCE(SUM(o.discount), 0) as discounts
				FROM {$orders_table} o
				WHERE o.date_created >= %s
				  AND o.date_created <= %s
				  AND o.status IN ('complete', 'revoked')
				  AND o.type = 'sale'
				  AND (
					  -- Order has points redemption
					  EXISTS (
						  SELECT 1
						  FROM {$this->points_table->get_prefixed_table_name()} p
						  INNER JOIN {$this->rewards_table->get_prefixed_table_name()} r
							  ON p.source_id = r.id
						  INNER JOIN {$this->rewards_table->get_prefixed_meta_table_name()} rm
							  ON r.id = rm.reward_id AND rm.meta_key = 'coupon_code'
						  INNER JOIN {$wpdb->posts} cp
							  ON cp.post_type = 'edd_discount' AND cp.post_title = rm.meta_value
						  INNER JOIN {$wpdb->prefix}edd_order_adjustments oa
							  ON oa.object_id = o.id
							  AND oa.object_type = 'order'
							  AND oa.type = 'discount'
							  AND oa.description LIKE CONCAT('%', cp.post_title, '%')
						  WHERE p.type = 'spent'
							AND p.source_type = 'reward_redemption'
							AND p.status = 'approved'
					  )
					  OR
					  -- Order has referral
					  EXISTS (
						  SELECT 1
						  FROM {$this->referrals_table->get_prefixed_table_name()} r
						  WHERE r.order_id = o.id
							AND r.source = %s
					  )
				  )
			";

			$results = $this->query( $sql, [ $start_date, $end_date, $integration_source ] );
			$row     = $results[0] ?? [];

			$product_sales = isset( $row['product_sales'] ) ? (float) $row['product_sales'] : 0.0;
			$discounts     = isset( $row['discounts'] ) ? (float) $row['discounts'] : 0.0;

			// Get refunds for eligible orders.
			$sql_refunds = "
				SELECT COALESCE(SUM(r.total), 0) as refunds_total
				FROM {$orders_table} r
				WHERE r.type = 'refund'
				  AND r.date_created >= %s
				  AND r.date_created <= %s
				  AND r.parent IN (
					  SELECT o.id
					  FROM {$orders_table} o
					  WHERE o.date_created >= %s
						AND o.date_created <= %s
						AND o.status IN ('complete', 'revoked')
						AND o.type = 'sale'
						AND (
							EXISTS (
								SELECT 1
								FROM {$this->points_table->get_prefixed_table_name()} p
								INNER JOIN {$this->rewards_table->get_prefixed_table_name()} r
									ON p.source_id = r.id
								INNER JOIN {$this->rewards_table->get_prefixed_meta_table_name()} rm
									ON r.id = rm.reward_id AND rm.meta_key = 'coupon_code'
								INNER JOIN {$wpdb->posts} cp
									ON cp.post_type = 'edd_discount' AND cp.post_title = rm.meta_value
								INNER JOIN {$wpdb->prefix}edd_order_adjustments oa
									ON oa.object_id = o.id
									AND oa.object_type = 'order'
									AND oa.type = 'discount'
									AND oa.description LIKE CONCAT('%', cp.post_title, '%')
								WHERE p.type = 'spent'
								  AND p.source_type = 'reward_redemption'
								  AND p.status = 'approved'
							)
							OR
							EXISTS (
								SELECT 1
								FROM {$this->referrals_table->get_prefixed_table_name()} r
								WHERE r.order_id = o.id
								  AND r.source = %s
							)
						)
				  )
			";

			$refunds = (float) $this->get_var(
				$sql_refunds,
				[ $start_date, $end_date, $start_date, $end_date, $integration_source ]
			);
		}

		$net_revenue = $product_sales - $refunds;

		return [
			'product_sales' => $product_sales,
			'refunds'       => $refunds,
			'discounts'     => $discounts,
			'net_revenue'   => $net_revenue,
		];
	}

	/**
	 * Get rewards-applied revenue grouped by day for sparkline chart.
	 *
	 * Returns daily product sales, refunds, and net revenue for orders that used RewardsWP.
	 *
	 * @since 1.0.0
	 *
	 * @param string $start_date Start date (Y-m-d H:i:s format).
	 * @param string $end_date   End date (Y-m-d H:i:s format).
	 *
	 * @return array Array of daily data indexed by date (Y-m-d format). Each entry contains:
	 *               - 'product_sales': Product sales after discounts for that day.
	 *               - 'refunds': Refunds posted on that day.
	 *               - 'net': Net revenue (product_sales - refunds) for that day.
	 */
	public function get_rewards_applied_revenue_by_day( string $start_date, string $end_date ): array {
		global $wpdb;

		// Get current integration source.
		$integration_source = $this->integration->get_source();

		// Determine table based on integration and HPOS.
		$is_hpos = false;
		if ( 'woocommerce' === $integration_source && method_exists( $this->integration, 'is_hpos_enabled' ) ) {
			$is_hpos = $this->integration->is_hpos_enabled();
		}

		$daily_sales   = [];
		$daily_refunds = [];

		if ( 'woocommerce' === $integration_source ) {
			if ( $is_hpos ) {
				// HPOS mode - Get daily product sales.
				$orders_table = $wpdb->prefix . 'wc_orders';

				$sql = "
					SELECT
						DATE(o.date_created_gmt) as date,
						COALESCE(SUM(o.total_amount), 0) as product_sales
					FROM {$orders_table} o
					WHERE o.date_created_gmt >= %s
					  AND o.date_created_gmt <= %s
					  AND o.status IN ('wc-processing', 'wc-completed')
					  AND o.type = 'shop_order'
					  AND (
						  EXISTS (
							  SELECT 1
							  FROM {$this->points_table->get_prefixed_table_name()} p
							  INNER JOIN {$this->rewards_table->get_prefixed_table_name()} r
								  ON p.source_id = r.id
							  INNER JOIN {$this->rewards_table->get_prefixed_meta_table_name()} rm
								  ON r.id = rm.reward_id AND rm.meta_key = 'coupon_code'
							  INNER JOIN {$wpdb->posts} cp
								  ON cp.post_type = 'shop_coupon' AND cp.post_title = rm.meta_value
							  INNER JOIN {$wpdb->prefix}wc_order_coupon_lookup ocl
								  ON ocl.coupon_id = cp.ID
							  WHERE p.type = 'spent'
								AND p.source_type = 'reward_redemption'
								AND p.status = 'approved'
								AND ocl.order_id = o.id
						  )
						  OR
						  EXISTS (
							  SELECT 1
							  FROM {$this->referrals_table->get_prefixed_table_name()} r
							  WHERE r.order_id = o.id
								AND r.source = %s
						  )
					  )
					GROUP BY DATE(o.date_created_gmt)
					ORDER BY date ASC
				";

				$results = $this->query( $sql, [ $start_date, $end_date, $integration_source ] );

				foreach ( $results as $row ) {
					$daily_sales[ $row['date'] ] = (float) $row['product_sales'];
				}

				// Get daily refunds.
				$sql_refunds = "
					SELECT
						DATE(r.date_created_gmt) as date,
						COALESCE(ABS(SUM(r.total_amount)), 0) as refunds_total
					FROM {$orders_table} r
					WHERE r.type = 'shop_order_refund'
					  AND r.date_created_gmt >= %s
					  AND r.date_created_gmt <= %s
					  AND r.parent_order_id IN (
						  SELECT o.id
						  FROM {$orders_table} o
						  WHERE o.status IN ('wc-processing', 'wc-completed')
							AND o.type = 'shop_order'
							AND (
								EXISTS (
									SELECT 1
									FROM {$this->points_table->get_prefixed_table_name()} p
									INNER JOIN {$this->rewards_table->get_prefixed_table_name()} r
										ON p.source_id = r.id
									INNER JOIN {$this->rewards_table->get_prefixed_meta_table_name()} rm
										ON r.id = rm.reward_id AND rm.meta_key = 'coupon_code'
									INNER JOIN {$wpdb->posts} cp
										ON cp.post_type = 'shop_coupon' AND cp.post_title = rm.meta_value
									INNER JOIN {$wpdb->prefix}wc_order_coupon_lookup ocl
										ON ocl.coupon_id = cp.ID
									WHERE p.type = 'spent'
									  AND p.source_type = 'reward_redemption'
									  AND p.status = 'approved'
									  AND ocl.order_id = o.id
								)
								OR
								EXISTS (
									SELECT 1
									FROM {$this->referrals_table->get_prefixed_table_name()} r
									WHERE r.order_id = o.id
									  AND r.source = %s
								)
							)
					  )
					GROUP BY DATE(r.date_created_gmt)
					ORDER BY date ASC
				";

				$results = $this->query( $sql_refunds, [ $start_date, $end_date, $integration_source ] );

				foreach ( $results as $row ) {
					$daily_refunds[ $row['date'] ] = (float) $row['refunds_total'];
				}
			} else {
				// Legacy mode - Get daily product sales.
				$orders_table = $wpdb->posts;

				$sql = "
					SELECT
						DATE(o.post_date_gmt) as date,
						COALESCE(SUM(
							CAST(pm_total.meta_value AS DECIMAL(10,2))
							- COALESCE(CAST(pm_shipping.meta_value AS DECIMAL(10,2)), 0)
							- COALESCE(CAST(pm_tax.meta_value AS DECIMAL(10,2)), 0)
						), 0) as product_sales
					FROM {$orders_table} o
					LEFT JOIN {$wpdb->postmeta} pm_total
						ON o.ID = pm_total.post_id AND pm_total.meta_key = '_order_total'
					LEFT JOIN {$wpdb->postmeta} pm_shipping
						ON o.ID = pm_shipping.post_id AND pm_shipping.meta_key = '_order_shipping'
					LEFT JOIN {$wpdb->postmeta} pm_tax
						ON o.ID = pm_tax.post_id AND pm_tax.meta_key = '_order_tax'
					WHERE o.post_type = 'shop_order'
					  AND o.post_date_gmt >= %s
					  AND o.post_date_gmt <= %s
					  AND o.post_status IN ('wc-processing', 'wc-completed')
					  AND (
						  EXISTS (
							  SELECT 1
							  FROM {$this->points_table->get_prefixed_table_name()} p
							  INNER JOIN {$this->rewards_table->get_prefixed_table_name()} r
								  ON p.source_id = r.id
							  INNER JOIN {$this->rewards_table->get_prefixed_meta_table_name()} rm
								  ON r.id = rm.reward_id AND rm.meta_key = 'coupon_code'
							  INNER JOIN {$wpdb->posts} cp
								  ON cp.post_type = 'shop_coupon' AND cp.post_title = rm.meta_value
							  INNER JOIN {$wpdb->postmeta} pm_coupons
								  ON o.ID = pm_coupons.post_id
								  AND pm_coupons.meta_key = '_order_coupon_codes'
								  AND pm_coupons.meta_value LIKE CONCAT('%', cp.post_title, '%')
							  WHERE p.type = 'spent'
								AND p.source_type = 'reward_redemption'
								AND p.status = 'approved'
						  )
						  OR
						  EXISTS (
							  SELECT 1
							  FROM {$this->referrals_table->get_prefixed_table_name()} r
							  WHERE r.order_id = o.ID
								AND r.source = %s
						  )
					  )
					GROUP BY DATE(o.post_date_gmt)
					ORDER BY date ASC
				";

				$results = $this->query( $sql, [ $start_date, $end_date, $integration_source ] );

				foreach ( $results as $row ) {
					$daily_sales[ $row['date'] ] = (float) $row['product_sales'];
				}

				// Get daily refunds.
				$sql_refunds = "
					SELECT
						DATE(r.post_date_gmt) as date,
						COALESCE(ABS(SUM(CAST(pm.meta_value AS DECIMAL(10,2)))), 0) as refunds_total
					FROM {$orders_table} r
					INNER JOIN {$wpdb->postmeta} pm
						ON r.ID = pm.post_id AND pm.meta_key = '_order_total'
					WHERE r.post_type = 'shop_order_refund'
					  AND r.post_date_gmt >= %s
					  AND r.post_date_gmt <= %s
					  AND r.post_parent IN (
						  SELECT o.ID
						  FROM {$orders_table} o
						  WHERE o.post_type = 'shop_order'
							AND o.post_status IN ('wc-processing', 'wc-completed')
							AND (
								EXISTS (
									SELECT 1
									FROM {$this->points_table->get_prefixed_table_name()} p
									INNER JOIN {$this->rewards_table->get_prefixed_table_name()} r
										ON p.source_id = r.id
									INNER JOIN {$this->rewards_table->get_prefixed_meta_table_name()} rm
										ON r.id = rm.reward_id AND rm.meta_key = 'coupon_code'
									INNER JOIN {$wpdb->posts} cp
										ON cp.post_type = 'shop_coupon' AND cp.post_title = rm.meta_value
									INNER JOIN {$wpdb->postmeta} pm_coupons
										ON o.ID = pm_coupons.post_id
										AND pm_coupons.meta_key = '_order_coupon_codes'
										AND pm_coupons.meta_value LIKE CONCAT('%', cp.post_title, '%')
									WHERE p.type = 'spent'
									  AND p.source_type = 'reward_redemption'
									  AND p.status = 'approved'
								)
								OR
								EXISTS (
									SELECT 1
									FROM {$this->referrals_table->get_prefixed_table_name()} r
									WHERE r.order_id = o.ID
									  AND r.source = %s
								)
							)
					  )
					GROUP BY DATE(r.post_date_gmt)
					ORDER BY date ASC
				";

				$results = $this->query( $sql_refunds, [ $start_date, $end_date, $integration_source ] );

				foreach ( $results as $row ) {
					$daily_refunds[ $row['date'] ] = (float) $row['refunds_total'];
				}
			}
		} elseif ( 'edd' === $integration_source ) {
			// EDD mode - Get daily product sales.
			$orders_table = $wpdb->prefix . 'edd_orders';

			$sql = "
				SELECT
					DATE(o.date_created) as date,
					COALESCE(SUM(o.total), 0) as product_sales
				FROM {$orders_table} o
				WHERE o.date_created >= %s
				  AND o.date_created <= %s
				  AND o.status IN ('complete', 'revoked')
				  AND o.type = 'sale'
				  AND (
					  EXISTS (
						  SELECT 1
						  FROM {$this->points_table->get_prefixed_table_name()} p
						  INNER JOIN {$this->rewards_table->get_prefixed_table_name()} r
							  ON p.source_id = r.id
						  INNER JOIN {$this->rewards_table->get_prefixed_meta_table_name()} rm
							  ON r.id = rm.reward_id AND rm.meta_key = 'coupon_code'
						  INNER JOIN {$wpdb->posts} cp
							  ON cp.post_type = 'edd_discount' AND cp.post_title = rm.meta_value
						  INNER JOIN {$wpdb->prefix}edd_order_adjustments oa
							  ON oa.object_id = o.id
							  AND oa.object_type = 'order'
							  AND oa.type = 'discount'
							  AND oa.description LIKE CONCAT('%', cp.post_title, '%')
						  WHERE p.type = 'spent'
							AND p.source_type = 'reward_redemption'
							AND p.status = 'approved'
					  )
					  OR
					  EXISTS (
						  SELECT 1
						  FROM {$this->referrals_table->get_prefixed_table_name()} r
						  WHERE r.order_id = o.id
							AND r.source = %s
					  )
				  )
				GROUP BY DATE(o.date_created)
				ORDER BY date ASC
			";

			$results = $this->query( $sql, [ $start_date, $end_date, $integration_source ] );

			foreach ( $results as $row ) {
				$daily_sales[ $row['date'] ] = (float) $row['product_sales'];
			}

			// Get daily refunds.
			$sql_refunds = "
				SELECT
					DATE(r.date_created) as date,
					COALESCE(SUM(r.total), 0) as refunds_total
				FROM {$orders_table} r
				WHERE r.type = 'refund'
				  AND r.date_created >= %s
				  AND r.date_created <= %s
				  AND r.parent IN (
					  SELECT o.id
					  FROM {$orders_table} o
					  WHERE o.status IN ('complete', 'revoked')
						AND o.type = 'sale'
						AND (
							EXISTS (
								SELECT 1
								FROM {$this->points_table->get_prefixed_table_name()} p
								INNER JOIN {$this->rewards_table->get_prefixed_table_name()} r
									ON p.source_id = r.id
								INNER JOIN {$this->rewards_table->get_prefixed_meta_table_name()} rm
									ON r.id = rm.reward_id AND rm.meta_key = 'coupon_code'
								INNER JOIN {$wpdb->posts} cp
									ON cp.post_type = 'edd_discount' AND cp.post_title = rm.meta_value
								INNER JOIN {$wpdb->prefix}edd_order_adjustments oa
									ON oa.object_id = o.id
									AND oa.object_type = 'order'
									AND oa.type = 'discount'
									AND oa.description LIKE CONCAT('%', cp.post_title, '%')
								WHERE p.type = 'spent'
								  AND p.source_type = 'reward_redemption'
								  AND p.status = 'approved'
							)
							OR
							EXISTS (
								SELECT 1
								FROM {$this->referrals_table->get_prefixed_table_name()} r
								WHERE r.order_id = o.id
								  AND r.source = %s
							)
						)
				  )
				GROUP BY DATE(r.date_created)
				ORDER BY date ASC
			";

			$results = $this->query( $sql_refunds, [ $start_date, $end_date, $integration_source ] );

			foreach ( $results as $row ) {
				$daily_refunds[ $row['date'] ] = (float) $row['refunds_total'];
			}
		}

		// Combine sales and refunds into daily net revenue.
		$all_dates = array_unique( array_merge( array_keys( $daily_sales ), array_keys( $daily_refunds ) ) );
		sort( $all_dates );

		$daily_data = [];
		foreach ( $all_dates as $date ) {
			$sales   = $daily_sales[ $date ] ?? 0.0;
			$refunds = $daily_refunds[ $date ] ?? 0.0;

			$daily_data[ $date ] = [
				'product_sales' => $sales,
				'refunds'       => $refunds,
				'net'           => $sales - $refunds,
			];
		}

		return $daily_data;
	}

	/**
	 * Get referral revenue total for the given period.
	 *
	 * Returns product sales and refunds for orders linked to completed referrals.
	 *
	 * Formula: Net Revenue = Product Sales − Product Refunds Posted
	 *
	 * @since 1.0.0
	 *
	 * @param string $start_date Start date (Y-m-d H:i:s format).
	 * @param string $end_date   End date (Y-m-d H:i:s format).
	 *
	 * @return array {
	 *     @type float $product_sales Product sales from referral orders.
	 *     @type float $refunds       Product refunds posted in period.
	 *     @type float $net_revenue   Net revenue (product_sales - refunds).
	 * }
	 */
	public function get_referral_revenue_total( string $start_date, string $end_date ): array {
		global $wpdb;

		// Get current integration source.
		$integration_source = $this->integration->get_source();

		// Determine table based on integration and HPOS.
		$is_hpos = false;
		if ( 'woocommerce' === $integration_source && method_exists( $this->integration, 'is_hpos_enabled' ) ) {
			$is_hpos = $this->integration->is_hpos_enabled();
		}

		$product_sales = 0.0;
		$refunds       = 0.0;

		if ( 'woocommerce' === $integration_source ) {
			if ( $is_hpos ) {
				// HPOS mode.
				$orders_table = $wpdb->prefix . 'wc_orders';

				// Get product sales from orders with completed referrals.
				// Use DISTINCT to ensure each order is counted only once.
				$sql = "
					SELECT COALESCE(SUM(total_amount), 0) as product_sales
					FROM (
						SELECT DISTINCT o.id, o.total_amount
						FROM {$orders_table} o
						WHERE o.date_created_gmt >= %s
						  AND o.date_created_gmt <= %s
						  AND o.status IN ('wc-processing', 'wc-completed')
						  AND o.type = 'shop_order'
						  AND EXISTS (
							  SELECT 1
							  FROM {$this->referrals_table->get_prefixed_table_name()} ref
							  WHERE ref.order_id = o.id
								AND ref.status = 'completed'
								AND ref.source = %s
						  )
					) unique_orders
				";

				$product_sales = (float) $this->get_var( $sql, [ $start_date, $end_date, $integration_source ] );

				// Get refunds for referral orders.
				$sql_refunds = "
					SELECT COALESCE(ABS(SUM(r.total_amount)), 0) as refunds_total
					FROM {$orders_table} r
					WHERE r.type = 'shop_order_refund'
					  AND r.date_created_gmt >= %s
					  AND r.date_created_gmt <= %s
					  AND r.parent_order_id IN (
						  SELECT o.id
						  FROM {$orders_table} o
						  INNER JOIN {$this->referrals_table->get_prefixed_table_name()} ref
							  ON ref.order_id = o.id
							  AND ref.status = 'completed'
							  AND ref.source = %s
						  WHERE o.status IN ('wc-processing', 'wc-completed')
							AND o.type = 'shop_order'
					  )
				";

				$refunds = (float) $this->get_var( $sql_refunds, [ $start_date, $end_date, $integration_source ] );

			} else {
				// Legacy mode.
				$orders_table = $wpdb->posts;

				// Get product sales from orders with completed referrals.
				// Use DISTINCT to ensure each order is counted only once.
				$sql = "
					SELECT COALESCE(SUM(product_sales), 0) as product_sales
					FROM (
						SELECT DISTINCT
							o.ID,
							CAST(pm_total.meta_value AS DECIMAL(10,2))
								- COALESCE(CAST(pm_shipping.meta_value AS DECIMAL(10,2)), 0)
								- COALESCE(CAST(pm_tax.meta_value AS DECIMAL(10,2)), 0) as product_sales
						FROM {$orders_table} o
						LEFT JOIN {$wpdb->postmeta} pm_total
							ON o.ID = pm_total.post_id AND pm_total.meta_key = '_order_total'
						LEFT JOIN {$wpdb->postmeta} pm_shipping
							ON o.ID = pm_shipping.post_id AND pm_shipping.meta_key = '_order_shipping'
						LEFT JOIN {$wpdb->postmeta} pm_tax
							ON o.ID = pm_tax.post_id AND pm_tax.meta_key = '_order_tax'
						WHERE o.post_type = 'shop_order'
						  AND o.post_date_gmt >= %s
						  AND o.post_date_gmt <= %s
						  AND o.post_status IN ('wc-processing', 'wc-completed')
						  AND EXISTS (
							  SELECT 1
							  FROM {$this->referrals_table->get_prefixed_table_name()} ref
							  WHERE ref.order_id = o.ID
								AND ref.status = 'completed'
								AND ref.source = %s
						  )
					) unique_orders
				";

				$product_sales = (float) $this->get_var( $sql, [ $start_date, $end_date, $integration_source ] );

				// Get refunds for referral orders.
				$sql_refunds = "
					SELECT COALESCE(ABS(SUM(CAST(pm.meta_value AS DECIMAL(10,2)))), 0) as refunds_total
					FROM {$orders_table} r
					INNER JOIN {$wpdb->postmeta} pm
						ON r.ID = pm.post_id AND pm.meta_key = '_order_total'
					WHERE r.post_type = 'shop_order_refund'
					  AND r.post_date_gmt >= %s
					  AND r.post_date_gmt <= %s
					  AND r.post_parent IN (
						  SELECT o.ID
						  FROM {$orders_table} o
						  INNER JOIN {$this->referrals_table->get_prefixed_table_name()} ref
							  ON ref.order_id = o.ID
							  AND ref.status = 'completed'
							  AND ref.source = %s
						  WHERE o.post_type = 'shop_order'
							AND o.post_status IN ('wc-processing', 'wc-completed')
					  )
				";

				$refunds = (float) $this->get_var( $sql_refunds, [ $start_date, $end_date, $integration_source ] );
			}
		} elseif ( 'edd' === $integration_source ) {
			// EDD mode.
			$orders_table = $wpdb->prefix . 'edd_orders';

			// Get product sales from orders with completed referrals.
			// Use DISTINCT to ensure each order is counted only once.
			$sql = "
				SELECT COALESCE(SUM(total), 0) as product_sales
				FROM (
					SELECT DISTINCT o.id, o.total
					FROM {$orders_table} o
					WHERE o.date_created >= %s
					  AND o.date_created <= %s
					  AND o.status IN ('complete', 'revoked')
					  AND o.type = 'sale'
					  AND EXISTS (
						  SELECT 1
						  FROM {$this->referrals_table->get_prefixed_table_name()} ref
						  WHERE ref.order_id = o.id
							AND ref.status = 'completed'
							AND ref.source = %s
					  )
				) unique_orders
			";

			$product_sales = (float) $this->get_var( $sql, [ $start_date, $end_date, $integration_source ] );

			// Get refunds for referral orders.
			$sql_refunds = "
				SELECT COALESCE(SUM(r.total), 0) as refunds_total
				FROM {$orders_table} r
				WHERE r.type = 'refund'
				  AND r.date_created >= %s
				  AND r.date_created <= %s
				  AND r.parent IN (
					  SELECT o.id
					  FROM {$orders_table} o
					  INNER JOIN {$this->referrals_table->get_prefixed_table_name()} ref
						  ON ref.order_id = o.id
						  AND ref.status = 'completed'
						  AND ref.source = %s
					  WHERE o.status IN ('complete', 'revoked')
						AND o.type = 'sale'
				  )
			";

			$refunds = (float) $this->get_var( $sql_refunds, [ $start_date, $end_date, $integration_source ] );
		}

		$net_revenue = $product_sales - $refunds;

		return [
			'product_sales' => $product_sales,
			'refunds'       => $refunds,
			'net_revenue'   => $net_revenue,
		];
	}

	/**
	 * Get referral revenue grouped by day for chart.
	 *
	 * Returns daily product sales and refunds for orders linked to completed referrals.
	 *
	 * @since 1.0.0
	 *
	 * @param string $start_date Start date (Y-m-d H:i:s format).
	 * @param string $end_date   End date (Y-m-d H:i:s format).
	 *
	 * @return array Array of daily data indexed by date (Y-m-d format). Each entry contains:
	 *               - 'product_sales': Product sales for that day.
	 *               - 'refunds': Refunds posted on that day.
	 *               - 'net': Net revenue (product_sales - refunds) for that day.
	 */
	public function get_referral_revenue_by_day( string $start_date, string $end_date ): array {
		global $wpdb;

		// Get current integration source.
		$integration_source = $this->integration->get_source();

		// Determine table based on integration and HPOS.
		$is_hpos = false;
		if ( 'woocommerce' === $integration_source && method_exists( $this->integration, 'is_hpos_enabled' ) ) {
			$is_hpos = $this->integration->is_hpos_enabled();
		}

		$daily_sales   = [];
		$daily_refunds = [];

		if ( 'woocommerce' === $integration_source ) {
			if ( $is_hpos ) {
				// HPOS mode - Get daily product sales.
				// Use subquery to get unique orders first, then aggregate by date.
				$orders_table = $wpdb->prefix . 'wc_orders';

				$sql = "
					SELECT
						date,
						COALESCE(SUM(total_amount), 0) as product_sales
					FROM (
						SELECT DISTINCT
							o.id,
							DATE(o.date_created_gmt) as date,
							o.total_amount
						FROM {$orders_table} o
						WHERE o.date_created_gmt >= %s
						  AND o.date_created_gmt <= %s
						  AND o.status IN ('wc-processing', 'wc-completed')
						  AND o.type = 'shop_order'
						  AND EXISTS (
							  SELECT 1
							  FROM {$this->referrals_table->get_prefixed_table_name()} ref
							  WHERE ref.order_id = o.id
								AND ref.status = 'completed'
								AND ref.source = %s
						  )
					) unique_orders
					GROUP BY date
					ORDER BY date ASC
				";

				$results = $this->query( $sql, [ $start_date, $end_date, $integration_source ] );

				foreach ( $results as $row ) {
					$daily_sales[ $row['date'] ] = (float) $row['product_sales'];
				}

				// Get daily refunds.
				$sql_refunds = "
					SELECT
						DATE(r.date_created_gmt) as date,
						COALESCE(ABS(SUM(r.total_amount)), 0) as refunds_total
					FROM {$orders_table} r
					WHERE r.type = 'shop_order_refund'
					  AND r.date_created_gmt >= %s
					  AND r.date_created_gmt <= %s
					  AND r.parent_order_id IN (
						  SELECT o.id
						  FROM {$orders_table} o
						  INNER JOIN {$this->referrals_table->get_prefixed_table_name()} ref
							  ON ref.order_id = o.id
							  AND ref.status = 'completed'
							  AND ref.source = %s
						  WHERE o.status IN ('wc-processing', 'wc-completed')
							AND o.type = 'shop_order'
					  )
					GROUP BY DATE(r.date_created_gmt)
					ORDER BY date ASC
				";

				$results = $this->query( $sql_refunds, [ $start_date, $end_date, $integration_source ] );

				foreach ( $results as $row ) {
					$daily_refunds[ $row['date'] ] = (float) $row['refunds_total'];
				}
			} else {
				// Legacy mode - Get daily product sales.
				// Use subquery to ensure unique orders.
				$orders_table = $wpdb->posts;

				$sql = "
					SELECT
						date,
						COALESCE(SUM(product_sales), 0) as product_sales
					FROM (
						SELECT DISTINCT
							o.ID as order_id,
							DATE(o.post_date_gmt) as date,
							CAST(pm_total.meta_value AS DECIMAL(10,2))
								- COALESCE(CAST(pm_shipping.meta_value AS DECIMAL(10,2)), 0)
								- COALESCE(CAST(pm_tax.meta_value AS DECIMAL(10,2)), 0) as product_sales
						FROM {$orders_table} o
						LEFT JOIN {$wpdb->postmeta} pm_total
							ON o.ID = pm_total.post_id AND pm_total.meta_key = '_order_total'
						LEFT JOIN {$wpdb->postmeta} pm_shipping
							ON o.ID = pm_shipping.post_id AND pm_shipping.meta_key = '_order_shipping'
						LEFT JOIN {$wpdb->postmeta} pm_tax
							ON o.ID = pm_tax.post_id AND pm_tax.meta_key = '_order_tax'
						WHERE o.post_type = 'shop_order'
						  AND o.post_date_gmt >= %s
						  AND o.post_date_gmt <= %s
						  AND o.post_status IN ('wc-processing', 'wc-completed')
						  AND EXISTS (
							  SELECT 1
							  FROM {$this->referrals_table->get_prefixed_table_name()} ref
							  WHERE ref.order_id = o.ID
								AND ref.status = 'completed'
								AND ref.source = %s
						  )
					) unique_orders
					GROUP BY date
					ORDER BY date ASC
				";

				$results = $this->query( $sql, [ $start_date, $end_date, $integration_source ] );

				foreach ( $results as $row ) {
					$daily_sales[ $row['date'] ] = (float) $row['product_sales'];
				}

				// Get daily refunds.
				$sql_refunds = "
					SELECT
						DATE(r.post_date_gmt) as date,
						COALESCE(ABS(SUM(CAST(pm.meta_value AS DECIMAL(10,2)))), 0) as refunds_total
					FROM {$orders_table} r
					INNER JOIN {$wpdb->postmeta} pm
						ON r.ID = pm.post_id AND pm.meta_key = '_order_total'
					WHERE r.post_type = 'shop_order_refund'
					  AND r.post_date_gmt >= %s
					  AND r.post_date_gmt <= %s
					  AND r.post_parent IN (
						  SELECT o.ID
						  FROM {$orders_table} o
						  INNER JOIN {$this->referrals_table->get_prefixed_table_name()} ref
							  ON ref.order_id = o.ID
							  AND ref.status = 'completed'
							  AND ref.source = %s
						  WHERE o.post_type = 'shop_order'
							AND o.post_status IN ('wc-processing', 'wc-completed')
					  )
					GROUP BY DATE(r.post_date_gmt)
					ORDER BY date ASC
				";

				$results = $this->query( $sql_refunds, [ $start_date, $end_date, $integration_source ] );

				foreach ( $results as $row ) {
					$daily_refunds[ $row['date'] ] = (float) $row['refunds_total'];
				}
			}
		} elseif ( 'edd' === $integration_source ) {
			// EDD mode - Get daily product sales.
			// Use subquery to ensure unique orders.
			$orders_table = $wpdb->prefix . 'edd_orders';

			$sql = "
				SELECT
					date,
					COALESCE(SUM(total), 0) as product_sales
				FROM (
					SELECT DISTINCT
						o.id,
						DATE(o.date_created) as date,
						o.total
					FROM {$orders_table} o
					WHERE o.date_created >= %s
					  AND o.date_created <= %s
					  AND o.status IN ('complete', 'revoked')
					  AND o.type = 'sale'
					  AND EXISTS (
						  SELECT 1
						  FROM {$this->referrals_table->get_prefixed_table_name()} ref
						  WHERE ref.order_id = o.id
							AND ref.status = 'completed'
							AND ref.source = %s
					  )
				) unique_orders
				GROUP BY date
				ORDER BY date ASC
			";

			$results = $this->query( $sql, [ $start_date, $end_date, $integration_source ] );

			foreach ( $results as $row ) {
				$daily_sales[ $row['date'] ] = (float) $row['product_sales'];
			}

			// Get daily refunds.
			$sql_refunds = "
				SELECT
					DATE(r.date_created) as date,
					COALESCE(SUM(r.total), 0) as refunds_total
				FROM {$orders_table} r
				WHERE r.type = 'refund'
				  AND r.date_created >= %s
				  AND r.date_created <= %s
				  AND r.parent IN (
					  SELECT o.id
					  FROM {$orders_table} o
					  INNER JOIN {$this->referrals_table->get_prefixed_table_name()} ref
						  ON ref.order_id = o.id
						  AND ref.status = 'completed'
						  AND ref.source = %s
					  WHERE o.status IN ('complete', 'revoked')
						AND o.type = 'sale'
				  )
				GROUP BY DATE(r.date_created)
				ORDER BY date ASC
			";

			$results = $this->query( $sql_refunds, [ $start_date, $end_date, $integration_source ] );

			foreach ( $results as $row ) {
				$daily_refunds[ $row['date'] ] = (float) $row['refunds_total'];
			}
		}

		// Combine sales and refunds into daily net revenue.
		$all_dates = array_unique( array_merge( array_keys( $daily_sales ), array_keys( $daily_refunds ) ) );
		sort( $all_dates );

		$daily_data = [];
		foreach ( $all_dates as $date ) {
			$sales   = $daily_sales[ $date ] ?? 0.0;
			$refunds = $daily_refunds[ $date ] ?? 0.0;

			$daily_data[ $date ] = [
				'product_sales' => $sales,
				'refunds'       => $refunds,
				'net'           => $sales - $refunds,
			];
		}

		return $daily_data;
	}

	/**
	 * Get top referrers by completed referral count.
	 *
	 * @since 1.1.0
	 *
	 * @param string $start_date Start date (Y-m-d H:i:s format).
	 * @param string $end_date   End date (Y-m-d H:i:s format).
	 * @param int    $limit      Maximum number of results to return.
	 *
	 * @return array Array of top referrers with advocate_id, first_name, last_name, email, referral_count.
	 */
	public function get_top_referrers( string $start_date, string $end_date, int $limit = 5 ): array {
		$referrals_table = $this->referrals_table->get_prefixed_table_name();
		$members_table   = $this->members_table->get_prefixed_table_name();

		$sql = "
			SELECT
				r.advocate_id,
				m.first_name,
				m.last_name,
				m.email,
				COUNT(*) as referral_count
			FROM {$referrals_table} r
			LEFT JOIN {$members_table} m ON r.advocate_id = m.id
			WHERE r.status = 'completed'
				AND r.converted_at >= %s
				AND r.converted_at <= %s
			GROUP BY r.advocate_id
			ORDER BY referral_count DESC
			LIMIT %d
		";

		return $this->query( $sql, [ $start_date, $end_date, $limit ] );
	}

	/**
	 * Get most redeemed rewards by count.
	 *
	 * @since 1.1.0
	 *
	 * @param string $start_date Start date (Y-m-d H:i:s format).
	 * @param string $end_date   End date (Y-m-d H:i:s format).
	 * @param int    $limit      Maximum number of results to return.
	 *
	 * @return array Array of top rewards with reward_type, amount, free_product_name, redemption_count.
	 */
	public function get_top_redeemed_rewards( string $start_date, string $end_date, int $limit = 5 ): array {
		$rewards_table      = $this->rewards_table->get_prefixed_table_name();
		$rewards_meta_table = $this->rewards_table->get_prefixed_meta_table_name();

		$sql = "
			SELECT
				r.reward_type,
				r.amount,
				COALESCE(rm_desc.meta_value, rm_product.meta_value) as free_product_name,
				COUNT(*) as redemption_count
			FROM {$rewards_table} r
			LEFT JOIN {$rewards_meta_table} rm_product
				ON r.id = rm_product.reward_id AND rm_product.meta_key = 'free_product_name'
			LEFT JOIN {$rewards_meta_table} rm_desc
				ON r.id = rm_desc.reward_id AND rm_desc.meta_key = 'reward_description'
			WHERE r.status = 'redeemed'
				AND r.applied_at >= %s
				AND r.applied_at <= %s
			GROUP BY r.reward_type, r.amount, free_product_name
			ORDER BY redemption_count DESC
			LIMIT %d
		";

		return $this->query( $sql, [ $start_date, $end_date, $limit ] );
	}

	/**
	 * Get recent activity for the dashboard activity feed.
	 *
	 * Fetches recent events from multiple tables (points, referrals, members) and
	 * merges them into a single chronological feed.
	 *
	 * @since 1.1.0
	 *
	 * @param int $limit Maximum number of activities to return. Default 7.
	 *
	 * @return array Array of activity rows with: activity_type, member_id, first_name,
	 *               last_name, user_id, amount, source_type, meta, event_time.
	 */
	public function get_recent_activity( int $limit = 7 ): array {
		global $wpdb;

		$points_table    = $this->points_table->get_prefixed_table_name();
		$referrals_table = $this->referrals_table->get_prefixed_table_name();
		$members_table   = $this->members_table->get_prefixed_table_name();
		$rewards_table   = $this->rewards_table->get_prefixed_table_name();

		// Use UNION ALL to fetch from multiple tables, then sort and limit.
		// Each sub-query fetches more than needed to ensure we have enough after merging.
		$sub_limit = $limit * 3;

		// Get current integration source to build appropriate query.
		$integration_source = $this->integration->get_source();

		// Determine HPOS mode for WooCommerce.
		$is_hpos = false;
		if ( 'woocommerce' === $integration_source && method_exists( $this->integration, 'is_hpos_enabled' ) ) {
			$is_hpos = $this->integration->is_hpos_enabled();
		}

		// Build the Points spent (reward redemptions) subquery based on active integration.
		$points_spent_query = $this->build_points_spent_activity_query(
			$integration_source,
			$is_hpos,
			$sub_limit,
			$points_table,
			$members_table,
			$rewards_table,
			$referrals_table
		);

		$sql = "
			(
				-- Points earned (purchases, reviews, signups, referral bonuses)
				SELECT
					CASE
						WHEN p.source_type IN ('purchase', 'first_order') THEN 'order_placed'
						ELSE 'points_earned'
					END as activity_type,
					p.member_id,
					m.first_name,
					m.last_name,
					m.email,
					m.user_id,
					p.points as amount,
					p.source_type,
					p.source_id,
					p.note_public as meta,
					NULL as reward_type,
					NULL as reward_amount,
					NULL as free_product_name,
					p.created_at as event_time
				FROM {$points_table} p
				INNER JOIN {$members_table} m ON p.member_id = m.id
				WHERE p.type = 'earned'
				  AND p.status = 'approved'
				  AND m.status IN ('active', 'guest')
				  AND (p.source_type IS NULL OR p.source_type NOT IN ('admin', 'manual'))
				ORDER BY p.created_at DESC
				LIMIT {$sub_limit}
			)
			UNION ALL
			{$points_spent_query}
			UNION ALL
			(
				-- Completed referrals
				SELECT
					'referral_completed' as activity_type,
					r.advocate_id as member_id,
					m.first_name,
					m.last_name,
					m.email,
					m.user_id,
					NULL as amount,
					r.source as source_type,
					r.order_id as source_id,
					COALESCE(rm.first_name, r.referred_email) as meta,
					NULL as reward_type,
					NULL as reward_amount,
					NULL as free_product_name,
					r.converted_at as event_time
				FROM {$referrals_table} r
				INNER JOIN {$members_table} m ON r.advocate_id = m.id
				LEFT JOIN {$members_table} rm ON r.referred_user_id = rm.user_id
				WHERE r.status = 'completed'
				  AND r.converted_at IS NOT NULL
				  AND m.status IN ('active', 'guest')
				ORDER BY r.converted_at DESC
				LIMIT {$sub_limit}
			)
			UNION ALL
			(
				-- New member joins (exclude if they have signup points, since that event will show instead)
				SELECT
					'member_joined' as activity_type,
					m.id as member_id,
					m.first_name,
					m.last_name,
					m.email,
					m.user_id,
					NULL as amount,
					NULL as source_type,
					NULL as source_id,
					NULL as meta,
					NULL as reward_type,
					NULL as reward_amount,
					NULL as free_product_name,
					m.created_at as event_time
				FROM {$members_table} m
				WHERE m.status IN ('active', 'guest')
				  AND NOT EXISTS (
				      SELECT 1 FROM {$points_table} p2
				      WHERE p2.member_id = m.id
				        AND p2.source_type = 'signup'
				        AND p2.type = 'earned'
				        AND p2.status = 'approved'
				  )
				ORDER BY m.created_at DESC
				LIMIT {$sub_limit}
			)
			ORDER BY event_time DESC
			LIMIT %d
		";

		return $this->query( $sql, [ $limit ], false );
	}

	/**
	 * Build the Points spent (reward redemptions) subquery for activity feed.
	 *
	 * This method builds integration-specific SQL for the reward redemptions part
	 * of the activity feed, avoiding references to tables that may not exist.
	 *
	 * @since 1.1.0
	 *
	 * @param string $integration_source The current integration source (woocommerce, easy_digital_downloads, unknown).
	 * @param bool   $is_hpos            Whether WooCommerce HPOS is enabled.
	 * @param int    $sub_limit          The limit for the subquery.
	 * @param string $points_table       The prefixed points table name.
	 * @param string $members_table      The prefixed members table name.
	 * @param string $rewards_table      The prefixed rewards table name.
	 * @param string $referrals_table    The prefixed referrals table name.
	 *
	 * @return string The SQL subquery for points spent activities.
	 */
	private function build_points_spent_activity_query(
		string $integration_source,
		bool $is_hpos,
		int $sub_limit,
		string $points_table,
		string $members_table,
		string $rewards_table,
		string $referrals_table
	): string {
		global $wpdb;

		$rewards_meta_table = $this->rewards_table->get_prefixed_meta_table_name();

		if ( 'woocommerce' === $integration_source ) {
			if ( $is_hpos ) {
				// WooCommerce HPOS mode - use wc_order_coupon_lookup table.
				return "(
					-- Points spent (reward redemptions) - WooCommerce HPOS
					SELECT
						'reward_redeemed' as activity_type,
						p.member_id,
						m.first_name,
						m.last_name,
						m.email,
						m.user_id,
						ABS(p.points) as amount,
						p.source_type,
						COALESCE(ref.order_id, ocl.order_id) as source_id,
						p.note_public as meta,
						r.reward_type,
						r.amount as reward_amount,
						COALESCE(rm_desc.meta_value, rm_product.meta_value) as free_product_name,
						p.created_at as event_time
					FROM {$points_table} p
					INNER JOIN {$members_table} m ON p.member_id = m.id
					LEFT JOIN {$rewards_table} r ON p.source_id = r.id
					LEFT JOIN {$referrals_table} ref ON r.referral_id = ref.id AND r.referral_id > 0
					LEFT JOIN {$rewards_meta_table} rm ON r.id = rm.reward_id AND rm.meta_key = 'coupon_code'
					LEFT JOIN {$rewards_meta_table} rm_product ON r.id = rm_product.reward_id AND rm_product.meta_key = 'free_product_name'
					LEFT JOIN {$rewards_meta_table} rm_desc ON r.id = rm_desc.reward_id AND rm_desc.meta_key = 'reward_description'
					LEFT JOIN {$wpdb->posts} cp ON cp.post_title = rm.meta_value AND cp.post_type = 'shop_coupon'
					LEFT JOIN (
						SELECT ocl_inner.coupon_id, MAX(ocl_inner.order_id) as order_id
						FROM {$wpdb->prefix}wc_order_coupon_lookup ocl_inner
						INNER JOIN {$wpdb->prefix}wc_orders o ON o.id = ocl_inner.order_id
						WHERE o.status IN ('wc-processing', 'wc-completed')
						GROUP BY ocl_inner.coupon_id
					) ocl ON ocl.coupon_id = cp.ID
					WHERE p.type = 'spent'
					  AND p.status = 'approved'
					  AND m.status IN ('active', 'guest')
					  AND p.source_type = 'reward_redemption'
					ORDER BY p.created_at DESC
					LIMIT {$sub_limit}
				)";
			} else {
				// WooCommerce Legacy mode - use woocommerce_order_items table.
				return "(
					-- Points spent (reward redemptions) - WooCommerce Legacy
					SELECT
						'reward_redeemed' as activity_type,
						p.member_id,
						m.first_name,
						m.last_name,
						m.email,
						m.user_id,
						ABS(p.points) as amount,
						p.source_type,
						COALESCE(ref.order_id, oi.order_id) as source_id,
						p.note_public as meta,
						r.reward_type,
						r.amount as reward_amount,
						COALESCE(rm_desc.meta_value, rm_product.meta_value) as free_product_name,
						p.created_at as event_time
					FROM {$points_table} p
					INNER JOIN {$members_table} m ON p.member_id = m.id
					LEFT JOIN {$rewards_table} r ON p.source_id = r.id
					LEFT JOIN {$referrals_table} ref ON r.referral_id = ref.id AND r.referral_id > 0
					LEFT JOIN {$rewards_meta_table} rm ON r.id = rm.reward_id AND rm.meta_key = 'coupon_code'
					LEFT JOIN {$rewards_meta_table} rm_product ON r.id = rm_product.reward_id AND rm_product.meta_key = 'free_product_name'
					LEFT JOIN {$rewards_meta_table} rm_desc ON r.id = rm_desc.reward_id AND rm_desc.meta_key = 'reward_description'
					LEFT JOIN {$wpdb->posts} cp ON cp.post_title = rm.meta_value AND cp.post_type = 'shop_coupon'
					LEFT JOIN (
						SELECT oi_inner.order_item_name, MAX(oi_inner.order_id) as order_id
						FROM {$wpdb->prefix}woocommerce_order_items oi_inner
						INNER JOIN {$wpdb->posts} o ON o.ID = oi_inner.order_id
						WHERE oi_inner.order_item_type = 'coupon'
						  AND o.post_type = 'shop_order'
						  AND o.post_status IN ('wc-processing', 'wc-completed')
						GROUP BY oi_inner.order_item_name
					) oi ON oi.order_item_name = cp.post_title
					WHERE p.type = 'spent'
					  AND p.status = 'approved'
					  AND m.status IN ('active', 'guest')
					  AND p.source_type = 'reward_redemption'
					ORDER BY p.created_at DESC
					LIMIT {$sub_limit}
				)";
			}
		} elseif ( 'easy_digital_downloads' === $integration_source ) {
			// EDD mode - use edd_order_adjustments table.
			return "(
				-- Points spent (reward redemptions) - EDD
				SELECT
					'reward_redeemed' as activity_type,
					p.member_id,
					m.first_name,
					m.last_name,
					m.email,
					m.user_id,
					ABS(p.points) as amount,
					p.source_type,
					COALESCE(ref.order_id, oa.object_id) as source_id,
					p.note_public as meta,
					r.reward_type,
					r.amount as reward_amount,
					COALESCE(rm_desc.meta_value, rm_product.meta_value) as free_product_name,
					p.created_at as event_time
				FROM {$points_table} p
				INNER JOIN {$members_table} m ON p.member_id = m.id
				LEFT JOIN {$rewards_table} r ON p.source_id = r.id
				LEFT JOIN {$referrals_table} ref ON r.referral_id = ref.id AND r.referral_id > 0
				LEFT JOIN {$rewards_meta_table} rm ON r.id = rm.reward_id AND rm.meta_key = 'coupon_code'
				LEFT JOIN {$rewards_meta_table} rm_product ON r.id = rm_product.reward_id AND rm_product.meta_key = 'free_product_name'
				LEFT JOIN {$rewards_meta_table} rm_desc ON r.id = rm_desc.reward_id AND rm_desc.meta_key = 'reward_description'
				LEFT JOIN (
					SELECT oa_inner.description, MAX(oa_inner.object_id) as object_id
					FROM {$wpdb->prefix}edd_order_adjustments oa_inner
					INNER JOIN {$wpdb->prefix}edd_orders o ON o.id = oa_inner.object_id
					WHERE oa_inner.object_type = 'order'
					  AND oa_inner.type = 'discount'
					  AND o.status IN ('complete', 'revoked')
					GROUP BY oa_inner.description
				) oa ON oa.description LIKE CONCAT('%', rm.meta_value, '%')
				WHERE p.type = 'spent'
				  AND p.status = 'approved'
				  AND m.status IN ('active', 'guest')
				  AND p.source_type = 'reward_redemption'
				ORDER BY p.created_at DESC
				LIMIT {$sub_limit}
			)";
		} else {
			// Unknown integration - don't try to lookup order via coupon tables.
			// Just show the redemption with referral order_id if available.
			return "(
				-- Points spent (reward redemptions) - No integration
				SELECT
					'reward_redeemed' as activity_type,
					p.member_id,
					m.first_name,
					m.last_name,
					m.email,
					m.user_id,
					ABS(p.points) as amount,
					p.source_type,
					ref.order_id as source_id,
					p.note_public as meta,
					r.reward_type,
					r.amount as reward_amount,
					COALESCE(rm_desc.meta_value, rm_product.meta_value) as free_product_name,
					p.created_at as event_time
				FROM {$points_table} p
				INNER JOIN {$members_table} m ON p.member_id = m.id
				LEFT JOIN {$rewards_table} r ON p.source_id = r.id
				LEFT JOIN {$referrals_table} ref ON r.referral_id = ref.id AND r.referral_id > 0
				LEFT JOIN {$rewards_meta_table} rm_product ON r.id = rm_product.reward_id AND rm_product.meta_key = 'free_product_name'
				LEFT JOIN {$rewards_meta_table} rm_desc ON r.id = rm_desc.reward_id AND rm_desc.meta_key = 'reward_description'
				WHERE p.type = 'spent'
				  AND p.status = 'approved'
				  AND m.status IN ('active', 'guest')
				  AND p.source_type = 'reward_redemption'
				ORDER BY p.created_at DESC
				LIMIT {$sub_limit}
			)";
		}
	}
}
