<?php
/**
 * Persisted invoice totals, so list headers, the dashboard and the reports can be
 * answered with SQL instead of loading every invoice as a model.
 *
 * The invoice total is computed by the model (items, discount, tax, per-invoice
 * overrides, legacy fallbacks to the global tax setting). Until 2.4.0 it was never
 * stored, so anything that needed "the sum of all open invoices" instantiated
 * thousands of models per page view. This service writes the computed total to
 * post meta whenever a document is saved, backfills documents that pre-date it,
 * and drops the cached value whenever something it depends on changes (the
 * document's own items/discount/tax meta, or the global tax settings).
 *
 * @package EasyInvoice
 * @since 2.4.0
 */

namespace EasyInvoice\Services;

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

class InvoiceTotalsCache {

	const META_TOTAL = '_easy_invoice_cached_total';
	const META_AT    = '_easy_invoice_cached_at';
	const OPTION_COMPLETE = 'easy_invoice_totals_cache_complete';

	/**
	 * Bump whenever the total arithmetic changes (calculateTotals, rounding, discount
	 * caps…): every stored total is then dropped and rebuilt in the background.
	 */
	const CACHE_VERSION = '2';

	/** Statuses that still carry a balance. Mirrors InvoiceBalance::outstanding(). */
	const OPEN_STATUSES = [ 'available', 'unpaid', 'partial', 'overdue', 'sent', 'pending' ];

	/** Meta keys whose change makes a cached total stale. */
	const DEPENDENT_META = [
		'_easy_invoice_items', '_easy_invoice_discount_type', '_easy_invoice_discount_value',
		'_easy_invoice_discount_calculation_method', '_easy_invoice_tax_enabled', '_easy_invoice_tax_rate',
		'_easy_invoice_prices_include_tax', '_easy_invoice_customer_country', '_easy_invoice_customer_vat_number',
	];

	/** Options whose change makes every cached total stale (legacy invoices follow them). */
	const DEPENDENT_OPTIONS = [
		'easy_invoice_tax_enabled', 'easy_invoice_tax_rate', 'easy_invoice_prices_include_tax',
		'easy_invoice_reverse_charge_enabled', 'easy_invoice_company_country', 'easy_invoice_company_vat_number',
	];

	/**
	 * Store the model's current total.
	 *
	 * @param \EasyInvoice\Models\Invoice $invoice Saved invoice.
	 */
	public static function store( $invoice ): void {
		if ( ! is_callable( [ $invoice, 'getId' ] ) || ! is_callable( [ $invoice, 'getTotal' ] ) ) {
			return;
		}
		$id = (int) $invoice->getId();
		if ( $id <= 0 ) {
			return;
		}
		update_post_meta( $id, self::META_TOTAL, (string) round( (float) $invoice->getTotal(), 2 ) );
		update_post_meta( $id, self::META_AT, (string) time() );
		self::bumpVersion();
	}

	/**
	 * Forget one invoice's cached total (recomputed by the next backfill or save).
	 */
	public static function invalidate( int $invoice_id ): void {
		delete_post_meta( $invoice_id, self::META_TOTAL );
		delete_post_meta( $invoice_id, self::META_AT );
		delete_option( self::OPTION_COMPLETE );
		self::bumpVersion();
	}

	/**
	 * Forget every cached total (a global tax setting changed).
	 */
	public static function invalidateAll(): void {
		global $wpdb;
		$wpdb->query( $wpdb->prepare( "DELETE FROM {$wpdb->postmeta} WHERE meta_key IN (%s, %s)", self::META_TOTAL, self::META_AT ) );
		wp_cache_flush();
		delete_option( self::OPTION_COMPLETE );
		self::bumpVersion();
		self::scheduleBackfill();
	}

	/**
	 * Site-wide aggregates are identical for every admin who opens a list, so they are
	 * memoised in a transient keyed by a version that every invoice, payment or credit
	 * write bumps (see bumpVersion()).
	 */
	private static function memo( string $key, callable $compute ) {
		// Fixed key per aggregate; the version travels inside the value. Keying by
		// version instead left a new transient row behind after every write.
		$version = (int) get_option( 'easy_invoice_stats_version', 1 );
		$tkey    = 'ei_stats_' . $key;
		$hit     = get_transient( $tkey );
		if ( is_array( $hit ) && isset( $hit['v'], $hit['d'] ) && (int) $hit['v'] === $version ) {
			return $hit['d'];
		}
		$value = $compute();
		set_transient( $tkey, [ 'v' => $version, 'd' => $value ], 10 * MINUTE_IN_SECONDS );
		self::$bumped = false; // a later write in this request must bump again
		return $value;
	}

	/** @var bool Whether the version was already bumped since the last memoised read. */
	private static $bumped = false;

	/**
	 * Something that feeds the aggregates changed: forget every memoised value.
	 */
	public static function bumpVersion(): void {
		if ( self::$bumped ) {
			return; // already bumped since the last read; one UPDATE per burst of writes
		}
		self::$bumped = true;
		update_option( 'easy_invoice_stats_version', (int) get_option( 'easy_invoice_stats_version', 1 ) + 1, false );
	}

	/**
	 * Published invoices that have no cached total yet.
	 */
	public static function missingCount(): int {
		global $wpdb;
		return (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(*) FROM {$wpdb->posts} p
			 LEFT JOIN {$wpdb->postmeta} c ON c.post_id = p.ID AND c.meta_key = %s
			 WHERE p.post_type = 'easy_invoice' AND p.post_status = 'publish' AND c.meta_id IS NULL",
			self::META_TOTAL
		) );
	}

	/**
	 * Compute and store totals for up to $limit invoices that lack one.
	 *
	 * @return int How many are still missing afterwards.
	 */
	public static function backfill( int $limit = 200 ): int {
		global $wpdb;
		$ids = $wpdb->get_col( $wpdb->prepare(
			"SELECT p.ID FROM {$wpdb->posts} p
			 LEFT JOIN {$wpdb->postmeta} c ON c.post_id = p.ID AND c.meta_key = %s
			 WHERE p.post_type = 'easy_invoice' AND p.post_status = 'publish' AND c.meta_id IS NULL
			 ORDER BY p.ID DESC LIMIT %d",
			self::META_TOTAL,
			max( 1, $limit )
		) );
		if ( ! $ids ) {
			return 0;
		}
		$ids = array_map( 'intval', $ids );
		update_meta_cache( 'post', $ids );
		$repository = \EasyInvoice\Providers\InvoiceServiceProvider::getInvoiceRepository();
		foreach ( $ids as $id ) {
			$invoice = $repository->find( $id );
			if ( $invoice ) {
				self::store( $invoice );
			} else {
				// Not loadable as an invoice: mark it so the query does not pick it up again.
				update_post_meta( $id, self::META_TOTAL, '0' );
				update_post_meta( $id, self::META_AT, (string) time() );
			}
		}
		return self::missingCount();
	}

	/**
	 * Queue a background backfill (one-off cron event, re-queued until nothing is missing).
	 */
	public static function scheduleBackfill(): void {
		if ( ! wp_next_scheduled( 'easy_invoice_totals_backfill' ) ) {
			wp_schedule_single_event( time() + 5, 'easy_invoice_totals_backfill' );
		}
	}

	/**
	 * Cron callback: backfill in chunks until done.
	 */
	public static function cronBackfill(): void {
		$remaining = self::backfill( 500 );
		if ( $remaining > 0 ) {
			wp_schedule_single_event( time() + 10, 'easy_invoice_totals_backfill' );
		} else {
			update_option( self::OPTION_COMPLETE, '1', false );
		}
	}

	/**
	 * Make sure the cache is usable for a statistics query: backfill a bounded number
	 * inline and leave the rest to cron.
	 */
	public static function ensure( int $inline_limit = 200 ): void {
		static $done = false;
		if ( $done ) {
			return;
		}
		$done = true;
		// Once every invoice has a stored total the flag is set; invalidate() and
		// invalidateAll() clear it, and save() stores a total for every new invoice,
		// so there is nothing to re-count on each view.
		if ( '1' === get_option( self::OPTION_COMPLETE, '' ) ) {
			return;
		}
		if ( self::missingCount() > 0 ) {
			$remaining = self::backfill( $inline_limit );
			if ( $remaining > 0 ) {
				self::scheduleBackfill();
				return;
			}
		}
		update_option( self::OPTION_COMPLETE, '1', false );
	}

	/**
	 * SQL fragment resolving an invoice's currency ('global' / '' → site currency).
	 */
	private static function currencyExpr( string $alias ): string {
		global $wpdb;
		$site = strtoupper( (string) get_option( 'easy_invoice_currency_code', 'USD' ) );
		return $wpdb->prepare( "UPPER(COALESCE(NULLIF(NULLIF({$alias}.meta_value, ''), 'global'), %s))", $site ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	}

	/**
	 * Subquery: completed payment sums per invoice.
	 */
	private static function paidSubquery(): string {
		global $wpdb;
		return "SELECT inv.meta_value AS invoice_id, SUM(CAST(amt.meta_value AS DECIMAL(18,4))) AS paid
				FROM {$wpdb->posts} pp
				INNER JOIN {$wpdb->postmeta} inv ON inv.post_id = pp.ID AND inv.meta_key = '_invoice_id'
				INNER JOIN {$wpdb->postmeta} amt ON amt.post_id = pp.ID AND amt.meta_key = '_amount'
				INNER JOIN {$wpdb->postmeta} st  ON st.post_id  = pp.ID AND st.meta_key  = '_status' AND st.meta_value = 'completed'
				WHERE pp.post_type = 'easy_invoice_payment' AND pp.post_status = 'publish'
				GROUP BY inv.meta_value";
	}

	/**
	 * Subquery: credit note sums per invoice.
	 */
	private static function creditSubquery(): string {
		global $wpdb;
		return "SELECT inv.meta_value AS invoice_id, SUM(CAST(tot.meta_value AS DECIMAL(18,4))) AS credited
				FROM {$wpdb->posts} cp
				INNER JOIN {$wpdb->postmeta} inv ON inv.post_id = cp.ID AND inv.meta_key = '_easy_invoice_credited_invoice_id'
				LEFT  JOIN {$wpdb->postmeta} tot ON tot.post_id = cp.ID AND tot.meta_key = '_easy_invoice_total'
				WHERE cp.post_type = 'easy_invoice_credit' AND cp.post_status = 'publish'
				GROUP BY inv.meta_value";
	}

	/**
	 * Outstanding balances across all open invoices, by currency, with the overdue split.
	 * Same shape as InvoiceBalance::outstanding().
	 *
	 * @return array{count:int,overdue_count:int,amount:array<string,float>,overdue_amount:array<string,float>}
	 */
	public static function outstanding(): array {
		return self::snapshot()['outstanding'];
	}

	/**
	 * Site-wide figures the list headers, dashboard and clients page all need,
	 * from one pass over the stored totals: outstanding balances (by currency, with
	 * the overdue split) and paid revenue net of credits. Memoised together so a
	 * page that shows both pays for one scan, not three.
	 */
	public static function snapshot(): array {
		return self::memo( 'snapshot', [ self::class, 'computeSnapshot' ] );
	}

	/** @internal */
	public static function computeSnapshot(): array {
		global $wpdb;
		self::ensure();
		$today  = gmdate( 'Y-m-d', current_time( 'timestamp' ) );
		$open   = "'" . implode( "','", array_map( 'esc_sql', self::OPEN_STATUSES ) ) . "'";
		$cur    = self::currencyExpr( 'cur' );
		$paid   = self::paidSubquery();
		$credit = self::creditSubquery();
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- fragments above are prepared / constant.
		$rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT {$cur} AS currency,
			        (s.meta_value = 'paid') AS is_paid,
			        (s.meta_value IN ({$open})) AS is_open,
			        (CASE WHEN dd.meta_value IS NOT NULL AND dd.meta_value <> '' AND dd.meta_value < %s THEN 1 ELSE 0 END) AS overdue,
			        COUNT(*) AS n,
			        SUM(CAST(c.meta_value AS DECIMAL(18,4)) - COALESCE(cr.credited, 0)) AS net_total,
			        SUM(CASE WHEN CAST(c.meta_value AS DECIMAL(18,4)) - COALESCE(pd.paid, 0) - COALESCE(cr.credited, 0) > 0.004 THEN 1 ELSE 0 END) AS n_due,
			        SUM(GREATEST(0, CAST(c.meta_value AS DECIMAL(18,4)) - COALESCE(pd.paid, 0) - COALESCE(cr.credited, 0))) AS due
			 FROM {$wpdb->posts} p
			 INNER JOIN {$wpdb->postmeta} s  ON s.post_id = p.ID AND s.meta_key = '_easy_invoice_status'
			 INNER JOIN {$wpdb->postmeta} c  ON c.post_id = p.ID AND c.meta_key = %s
			 LEFT  JOIN {$wpdb->postmeta} cur ON cur.post_id = p.ID AND cur.meta_key = '_easy_invoice_currency_code'
			 LEFT  JOIN {$wpdb->postmeta} dd  ON dd.post_id = p.ID AND dd.meta_key = '_easy_invoice_due_date'
			 LEFT  JOIN ({$paid}) pd   ON pd.invoice_id = p.ID
			 LEFT  JOIN ({$credit}) cr ON cr.invoice_id = p.ID
			 WHERE p.post_type = 'easy_invoice' AND p.post_status = 'publish'
			   AND (s.meta_value = 'paid' OR s.meta_value IN ({$open}))
			 GROUP BY currency, is_paid, is_open, overdue",
			$today,
			self::META_TOTAL
		), ARRAY_A );
		$counts = $wpdb->get_row(
			"SELECT COUNT(*) AS n, COUNT(DISTINCT NULLIF(cl.meta_value, '0')) AS clients
			 FROM {$wpdb->posts} p
			 LEFT JOIN {$wpdb->postmeta} cl ON cl.post_id = p.ID AND cl.meta_key = '_easy_invoice_client_id'
			 WHERE p.post_type = 'easy_invoice' AND p.post_status = 'publish'",
			ARRAY_A
		);
		// phpcs:enable
		$out     = [ 'count' => 0, 'overdue_count' => 0, 'amount' => [], 'overdue_amount' => [], 'count_by_currency' => [] ];
		$revenue = []; $paid_n = 0;
		foreach ( (array) $rows as $r ) {
			$c = (string) $r['currency'];
			if ( (int) $r['is_paid'] ) {
				$revenue[ $c ] = [ 'amount' => round( ( $revenue[ $c ]['amount'] ?? 0 ) + (float) $r['net_total'], 2 ), 'symbol' => \EasyInvoice\Helpers\CurrencyHelper::getCurrencySymbol( $c ) ];
				$paid_n += (int) $r['n'];
			}
			if ( (int) $r['is_open'] && (int) $r['n_due'] > 0 ) {
				$out['count']                  += (int) $r['n_due'];
				$out['count_by_currency'][ $c ] = ( $out['count_by_currency'][ $c ] ?? 0 ) + (int) $r['n_due'];
				$out['amount'][ $c ]            = round( ( $out['amount'][ $c ] ?? 0 ) + (float) $r['due'], 2 );
				if ( (int) $r['overdue'] ) {
					$out['overdue_count']       += (int) $r['n_due'];
					$out['overdue_amount'][ $c ] = round( ( $out['overdue_amount'][ $c ] ?? 0 ) + (float) $r['due'], 2 );
				}
			}
		}
		return [
			'outstanding' => $out,
			'paid'        => [
				'revenue'       => $revenue,
				'paid_count'    => $paid_n,
				'invoice_count' => (int) ( $counts['n'] ?? 0 ),
				'client_count'  => (int) ( $counts['clients'] ?? 0 ),
			],
		];
	}

	/** @internal */
	public static function computeOutstanding(): array {
		global $wpdb;
		self::ensure();
		$today  = gmdate( 'Y-m-d', current_time( 'timestamp' ) );
		$in     = "'" . implode( "','", array_map( 'esc_sql', self::OPEN_STATUSES ) ) . "'";
		$cur    = self::currencyExpr( 'cur' );
		$paid   = self::paidSubquery();
		$credit = self::creditSubquery();
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- fragments above are prepared / constant.
		$rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT {$cur} AS currency,
			        (CASE WHEN dd.meta_value IS NOT NULL AND dd.meta_value <> '' AND dd.meta_value < %s THEN 1 ELSE 0 END) AS overdue,
			        COUNT(*) AS n,
			        SUM(GREATEST(0, CAST(c.meta_value AS DECIMAL(18,4)) - COALESCE(pd.paid, 0) - COALESCE(cr.credited, 0))) AS due
			 FROM {$wpdb->posts} p
			 INNER JOIN {$wpdb->postmeta} s  ON s.post_id = p.ID AND s.meta_key = '_easy_invoice_status' AND s.meta_value IN ({$in})
			 INNER JOIN {$wpdb->postmeta} c  ON c.post_id = p.ID AND c.meta_key = %s
			 LEFT  JOIN {$wpdb->postmeta} cur ON cur.post_id = p.ID AND cur.meta_key = '_easy_invoice_currency_code'
			 LEFT  JOIN {$wpdb->postmeta} dd  ON dd.post_id = p.ID AND dd.meta_key = '_easy_invoice_due_date'
			 LEFT  JOIN ({$paid}) pd   ON pd.invoice_id = p.ID
			 LEFT  JOIN ({$credit}) cr ON cr.invoice_id = p.ID
			 WHERE p.post_type = 'easy_invoice' AND p.post_status = 'publish'
			   AND CAST(c.meta_value AS DECIMAL(18,4)) - COALESCE(pd.paid, 0) - COALESCE(cr.credited, 0) > 0.004
			 GROUP BY currency, overdue",
			$today,
			self::META_TOTAL
		), ARRAY_A );
		// phpcs:enable
		$out = [ 'count' => 0, 'overdue_count' => 0, 'amount' => [], 'overdue_amount' => [], 'count_by_currency' => [] ];
		foreach ( (array) $rows as $r ) {
			$c = (string) $r['currency'];
			$out['count']       += (int) $r['n'];
			$out['count_by_currency'][ $c ] = ( $out['count_by_currency'][ $c ] ?? 0 ) + (int) $r['n'];
			$out['amount'][ $c ] = round( ( $out['amount'][ $c ] ?? 0 ) + (float) $r['due'], 2 );
			if ( (int) $r['overdue'] ) {
				$out['overdue_count']       += (int) $r['n'];
				$out['overdue_amount'][ $c ] = round( ( $out['overdue_amount'][ $c ] ?? 0 ) + (float) $r['due'], 2 );
			}
		}
		return $out;
	}

	/**
	 * Revenue from paid invoices (issued in the range when given), net of credit notes,
	 * grouped by currency; plus the number of invoices issued and distinct clients billed.
	 *
	 * @return array{revenue:array<string,array{amount:float,symbol:string}>,paid_count:int,invoice_count:int,client_count:int}
	 */
	public static function paidRevenue( string $start_date = '', string $end_date = '' ): array {
		if ( '' === $start_date && '' === $end_date ) {
			return self::snapshot()['paid'];
		}
		return self::memo( 'paid_' . md5( $start_date . '|' . $end_date ), static function () use ( $start_date, $end_date ) { return self::computePaidRevenue( $start_date, $end_date ); } );
	}

	/** @internal */
	public static function computePaidRevenue( string $start_date = '', string $end_date = '' ): array {
		global $wpdb;
		self::ensure();
		$cur    = self::currencyExpr( 'cur' );
		$credit = self::creditSubquery();
		$where  = '';
		$args   = [ self::META_TOTAL ];
		if ( '' !== $start_date ) { $where .= ' AND d.meta_value >= %s'; $args[] = $start_date; }
		if ( '' !== $end_date )   { $where .= ' AND d.meta_value <= %s'; $args[] = $end_date; }
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT {$cur} AS currency, COUNT(*) AS n,
			        SUM(CAST(c.meta_value AS DECIMAL(18,4)) - COALESCE(cr.credited, 0)) AS revenue
			 FROM {$wpdb->posts} p
			 INNER JOIN {$wpdb->postmeta} s ON s.post_id = p.ID AND s.meta_key = '_easy_invoice_status' AND s.meta_value = 'paid'
			 INNER JOIN {$wpdb->postmeta} c ON c.post_id = p.ID AND c.meta_key = %s
			 LEFT  JOIN {$wpdb->postmeta} d ON d.post_id = p.ID AND d.meta_key = '_easy_invoice_issue_date'
			 LEFT  JOIN {$wpdb->postmeta} cur ON cur.post_id = p.ID AND cur.meta_key = '_easy_invoice_currency_code'
			 LEFT  JOIN ({$credit}) cr ON cr.invoice_id = p.ID
			 WHERE p.post_type = 'easy_invoice' AND p.post_status = 'publish' {$where}
			 GROUP BY currency",
			...$args
		), ARRAY_A );
		$count_sql  = "SELECT COUNT(*) AS n, COUNT(DISTINCT NULLIF(cl.meta_value, '0')) AS clients
			 FROM {$wpdb->posts} p
			 LEFT JOIN {$wpdb->postmeta} d  ON d.post_id = p.ID AND d.meta_key = '_easy_invoice_issue_date'
			 LEFT JOIN {$wpdb->postmeta} cl ON cl.post_id = p.ID AND cl.meta_key = '_easy_invoice_client_id'
			 WHERE p.post_type = 'easy_invoice' AND p.post_status = 'publish' " . $where;
		$count_args = array_slice( $args, 1 );
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnnecessaryPrepare -- $count_sql is built above from constants plus %s placeholders; prepared when it has any.
		$counts     = $wpdb->get_row( $count_args ? $wpdb->prepare( $count_sql, ...$count_args ) : $count_sql, ARRAY_A );
		// phpcs:enable
		$revenue = []; $paid = 0;
		foreach ( (array) $rows as $r ) {
			$c = (string) $r['currency'];
			$revenue[ $c ] = [ 'amount' => round( (float) $r['revenue'], 2 ), 'symbol' => \EasyInvoice\Helpers\CurrencyHelper::getCurrencySymbol( $c ) ];
			$paid += (int) $r['n'];
		}
		return [
			'revenue'       => $revenue,
			'paid_count'    => $paid,
			'invoice_count' => (int) ( $counts['n'] ?? 0 ),
			'client_count'  => (int) ( $counts['clients'] ?? 0 ),
		];
	}

	/**
	 * Invoice counts by display status (paid / partial / unpaid / overdue / draft / canceled)
	 * for invoices issued in the range.
	 *
	 * @return array<string,int>
	 */
	public static function statusCounts( string $start_date = '', string $end_date = '' ): array {
		return self::memo( 'status_' . md5( $start_date . '|' . $end_date ), static function () use ( $start_date, $end_date ) { return self::computeStatusCounts( $start_date, $end_date ); } );
	}

	/** @internal */
	public static function computeStatusCounts( string $start_date = '', string $end_date = '' ): array {
		global $wpdb;
		$today = gmdate( 'Y-m-d', current_time( 'timestamp' ) );
		$where = ''; $args = [ $today ];
		if ( '' !== $start_date ) { $where .= ' AND d.meta_value >= %s'; $args[] = $start_date; }
		if ( '' !== $end_date )   { $where .= ' AND d.meta_value <= %s'; $args[] = $end_date; }
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT CASE
			          WHEN s.meta_value IN ('paid','completed') THEN 'paid'
			          WHEN s.meta_value IN ('partial','partially_paid') THEN 'partial'
			          WHEN s.meta_value = 'draft' THEN 'draft'
			          WHEN s.meta_value IN ('cancelled','canceled') THEN 'canceled'
			          WHEN s.meta_value = 'overdue' THEN 'overdue'
			          WHEN s.meta_value IN ('available','unpaid','sent','pending') AND dd.meta_value IS NOT NULL AND dd.meta_value <> '' AND dd.meta_value < %s THEN 'overdue'
			          WHEN s.meta_value IN ('available','unpaid','sent','pending') THEN 'unpaid'
			          ELSE 'other' END AS k, COUNT(*) AS n
			 FROM {$wpdb->posts} p
			 INNER JOIN {$wpdb->postmeta} s ON s.post_id = p.ID AND s.meta_key = '_easy_invoice_status'
			 LEFT  JOIN {$wpdb->postmeta} d  ON d.post_id = p.ID AND d.meta_key = '_easy_invoice_issue_date'
			 LEFT  JOIN {$wpdb->postmeta} dd ON dd.post_id = p.ID AND dd.meta_key = '_easy_invoice_due_date'
			 WHERE p.post_type = 'easy_invoice' AND p.post_status = 'publish' {$where}
			 GROUP BY k",
			...$args
		), ARRAY_A );
		// phpcs:enable
		$out = [];
		foreach ( (array) $rows as $r ) {
			$out[ (string) $r['k'] ] = (int) $r['n'];
		}
		return $out;
	}

	/**
	 * Clients ranked by paid revenue (net of credits) in the range.
	 *
	 * @return array<int,array{id:int,total_amount:array<string,array{amount:float,symbol:string}>,total_invoices:int,last_invoice:string}>
	 */
	public static function topClients( string $start_date = '', string $end_date = '', int $limit = 10 ): array {
		return self::memo( 'top_' . md5( $start_date . '|' . $end_date . '|' . $limit ), static function () use ( $start_date, $end_date, $limit ) { return self::computeTopClients( $start_date, $end_date, $limit ); } );
	}

	/** @internal */
	public static function computeTopClients( string $start_date = '', string $end_date = '', int $limit = 10 ): array {
		global $wpdb;
		self::ensure();
		$cur    = self::currencyExpr( 'cur' );
		$credit = self::creditSubquery();
		$where  = ''; $args = [ self::META_TOTAL ];
		if ( '' !== $start_date ) { $where .= ' AND d.meta_value >= %s'; $args[] = $start_date; }
		if ( '' !== $end_date )   { $where .= ' AND d.meta_value <= %s'; $args[] = $end_date; }
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT CAST(cl.meta_value AS UNSIGNED) AS client_id, {$cur} AS currency,
			        COUNT(*) AS n, MAX(d.meta_value) AS last_issue,
			        SUM(CASE WHEN s.meta_value = 'paid' THEN CAST(COALESCE(c.meta_value, '0') AS DECIMAL(18,4)) - COALESCE(cr.credited, 0) ELSE 0 END) AS revenue
			 FROM {$wpdb->posts} p
			 INNER JOIN {$wpdb->postmeta} cl ON cl.post_id = p.ID AND cl.meta_key = '_easy_invoice_client_id' AND cl.meta_value <> '' AND cl.meta_value <> '0'
			 INNER JOIN {$wpdb->postmeta} s  ON s.post_id = p.ID AND s.meta_key = '_easy_invoice_status'
			 LEFT  JOIN {$wpdb->postmeta} c  ON c.post_id = p.ID AND c.meta_key = %s
			 LEFT  JOIN {$wpdb->postmeta} d  ON d.post_id = p.ID AND d.meta_key = '_easy_invoice_issue_date'
			 LEFT  JOIN {$wpdb->postmeta} cur ON cur.post_id = p.ID AND cur.meta_key = '_easy_invoice_currency_code'
			 LEFT  JOIN ({$credit}) cr ON cr.invoice_id = p.ID
			 WHERE p.post_type = 'easy_invoice' AND p.post_status = 'publish' {$where}
			 GROUP BY client_id, currency",
			...$args
		), ARRAY_A );
		// phpcs:enable
		$site    = strtoupper( (string) get_option( 'easy_invoice_currency_code', 'USD' ) );
		$clients = [];
		foreach ( (array) $rows as $r ) {
			$cid = (int) $r['client_id'];
			if ( ! isset( $clients[ $cid ] ) ) {
				$clients[ $cid ] = [ 'id' => $cid, 'total_amount' => [], 'total_invoices' => 0, 'last_invoice' => '' ];
			}
			$clients[ $cid ]['total_invoices'] += (int) $r['n'];
			if ( (string) $r['last_issue'] > $clients[ $cid ]['last_invoice'] ) {
				$clients[ $cid ]['last_invoice'] = (string) $r['last_issue'];
			}
			$rev = round( (float) $r['revenue'], 2 );
			if ( $rev > 0 ) {
				$c = (string) $r['currency'];
				$clients[ $cid ]['total_amount'][ $c ] = [ 'amount' => round( ( $clients[ $cid ]['total_amount'][ $c ]['amount'] ?? 0 ) + $rev, 2 ), 'symbol' => \EasyInvoice\Helpers\CurrencyHelper::getCurrencySymbol( $c ) ];
			}
		}
		$score = static function ( $row ) use ( $site ) {
			$sum = 0.0;
			foreach ( $row['total_amount'] as $data ) {
				$sum += (float) $data['amount'];
			}
			return [ $sum, (float) ( $row['total_amount'][ $site ]['amount'] ?? 0 ), $row['total_invoices'] ];
		};
		uasort( $clients, static function ( $a, $b ) use ( $score ) {
			return $score( $b ) <=> $score( $a );
		} );
		return array_slice( $clients, 0, $limit, true );
	}
}