<?php
/**
 * What campaigns this site has sent.
 *
 * @package TextTango
 */

declare( strict_types=1 );

namespace TextTango\Marketing;

use TextTango\Analytics\Stats;
use TextTango\Install\Schema;
use TextTango\Support\Paging;

defined( 'ABSPATH' ) || exit;

/**
 * The history of campaigns this site has sent (spec §8).
 *
 * ### Why this is a table now
 *
 * It used to be a single option holding the newest fifty, trimmed on every
 * write with `array_slice( $history, 0, 50 )`. The reasoning at the time was
 * that a shop sends a handful of campaigns a month, so fifty was years of
 * history and an option that cannot grow never makes `wp_options` slow.
 *
 * The flaw is what the trim actually did. Campaign fifty-one was not hidden
 * behind a cap that could be lifted later — it was **deleted**. What a shop
 * sent, to how many people, and what it cost was destroyed as it went, and the
 * shops that hit fifty fastest were the ones sending most.
 *
 * It also feeds the spend caps. A campaign puts no rows in the message log,
 * because the platform fans it out, so {@see self::messages_since()} is the only
 * thing that makes a five-thousand-message blast visible to a five-hundred-a-day
 * ceiling. Discarding rows quietly narrowed the window that ceiling could see
 * over — the cap stopped counting spending that had actually happened.
 *
 * ### Why a bulk campaign's recipient list is not kept
 *
 * The numbers a campaign went to are personal data, and a second copy here would
 * give the privacy exporter and eraser a second place to look. The platform
 * holds the campaign and its recipients; this holds the counts, which is what
 * the merchant is reading.
 *
 * A **personal** campaign is the exception, and unavoidably so: it is sent from
 * here rather than by the platform, so this site is the only place that knows
 * who is left to send to and which code each person was given. Those rows live
 * in their own table — see {@see \TextTango\Marketing\PersonalCampaign}.
 *
 * Holding them puts an obligation on the plugin rather than discharging one, and
 * this comment claimed that obligation was met for two releases before it was:
 * the exporter, the eraser and the retention sweep all reached the message log
 * and none of them reached that table. They do now, and
 * `tests/Unit/PrivacyCoverageTest.php` holds every table declaring a `recipient`
 * column to all three — because the failure was not that somebody forgot, it was
 * that nothing would have noticed.
 */
final class CampaignLog {

	/**
	 * The option this history used to live in.
	 *
	 * Kept as a name so the migration in {@see Schema} and the uninstall cleanup
	 * both refer to one spelling. Nothing writes it any more.
	 */
	public const OPTION = 'texttango_campaigns';

	/**
	 * Statuses the platform reports for a campaign that has not finished.
	 */
	public const IN_FLIGHT = array( 'queued', 'scheduled', 'processing', 'running', 'paused' );

	/**
	 * One request to the platform, fanned out there.
	 *
	 * The default, and what every campaign before personal codes was. Stored as
	 * the empty string on rows written before the column existed, which is why
	 * every rule below asks whether a campaign is *personal* rather than whether
	 * it is bulk.
	 */
	public const DELIVERY_BULK = 'bulk';

	/**
	 * One message per recipient, sent from here.
	 *
	 * ### Why this changes how a campaign is counted
	 *
	 * A bulk campaign writes **no** rows to the message log — the platform is
	 * handed one request and fans it out — which is the entire reason
	 * {@see self::messages_since()} exists and why `Budget::usage()` adds it to
	 * the logged count.
	 *
	 * A personal campaign is the opposite. Every recipient gets a different body,
	 * so it cannot use the bulk endpoint at all; it sends through
	 * `Sender::send()` like anything else, and each send writes its own row. Its
	 * recipients are therefore **already counted** wherever messages are counted.
	 *
	 * Adding them again would count every one of them twice — halving a
	 * merchant's effective spend cap and doubling what the analytics report. So
	 * every aggregate in this class excludes them, and says so where it does.
	 */
	public const DELIVERY_PERSONAL = 'personal';

	/**
	 * The SQL predicate that keeps personal campaigns out of an aggregate.
	 *
	 * Written once rather than four times. `<>` rather than a comparison to
	 * `DELIVERY_BULK`, because rows written before the column existed hold the
	 * empty string and must keep counting.
	 */
	private const NOT_PERSONAL = "delivery <> 'personal'";

	/**
	 * Columns the table holds, and how `$wpdb` should bind each one.
	 *
	 * @return array<string, string>
	 */
	private static function columns(): array {
		return array(
			'uuid'                => '%s',
			'name'                => '%s',
			'body'                => '%s',
			'status'              => '%s',
			'mode'                => '%s',
			'delivery'            => '%s',
			'segment'             => '%s',
			'coupon'              => '%s',
			'recipients'          => '%d',
			'supplied'            => '%d',
			'accepted'            => '%d',
			'delivered'           => '%d',
			'failed'              => '%d',
			'pending'             => '%d',
			'sent'                => '%d',
			'segments'            => '%d',
			'credits'             => '%d',
			'cost'                => '%f',
			'excluded_suppressed' => '%d',
			'excluded_unusable'   => '%d',
			'sandbox'             => '%d',
			'scheduled_at'        => '%s',
			'dispatched_at'       => '%s',
			'refreshed'           => '%s',
			'created_at'          => '%s',
		);
	}

	/**
	 * Flatten a campaign record into table columns.
	 *
	 * `excluded` arrives as a nested array of two counts and is stored as two
	 * columns, since a serialised blob in a column nothing can query is a column
	 * that quietly stops being read.
	 *
	 * @param array<string, mixed> $row A campaign record.
	 * @return array<string, mixed>
	 */
	private static function to_columns( array $row ): array {
		$excluded = isset( $row['excluded'] ) && is_array( $row['excluded'] ) ? $row['excluded'] : array();

		$flat = array(
			'excluded_suppressed' => (int) ( $excluded['suppressed'] ?? $row['excluded_suppressed'] ?? 0 ),
			'excluded_unusable'   => (int) ( $excluded['unusable'] ?? $row['excluded_unusable'] ?? 0 ),
		);

		foreach ( self::columns() as $column => $format ) {
			if ( isset( $flat[ $column ] ) || ! array_key_exists( $column, $row ) ) {
				continue;
			}

			/*
			 * A null stays null rather than becoming '' — and for `cost` that is
			 * the whole point. Null means the platform has not told us what this
			 * campaign cost; 0.0000 means it cost nothing, which is what a
			 * sandboxed campaign genuinely did. Casting the first into the second
			 * would make an unreported campaign look free, and a total that
			 * quietly treats unknown as zero is the bug this column exists to fix.
			 */
			if ( null === $row[ $column ] ) {
				$flat[ $column ] = null;

				continue;
			}

			if ( '%d' === $format ) {
				$flat[ $column ] = (int) $row[ $column ];
			} elseif ( '%f' === $format ) {
				$flat[ $column ] = (float) $row[ $column ];
			} else {
				$flat[ $column ] = (string) $row[ $column ];
			}
		}

		return $flat;
	}

	/**
	 * Put the two excluded counts back into the shape the screens read.
	 *
	 * @param array<string, mixed> $row A table row.
	 * @return array<string, mixed>
	 */
	private static function from_columns( array $row ): array {
		$row['excluded'] = array(
			'suppressed' => (int) ( $row['excluded_suppressed'] ?? 0 ),
			'unusable'   => (int) ( $row['excluded_unusable'] ?? 0 ),
		);

		foreach ( self::columns() as $column => $format ) {
			// `isset()` is deliberate: a null column keeps its null, so a caller
			// can tell "not reported" from "cost nothing".
			if ( ! isset( $row[ $column ] ) ) {
				continue;
			}

			if ( '%d' === $format ) {
				$row[ $column ] = (int) $row[ $column ];
			} elseif ( '%f' === $format ) {
				$row[ $column ] = (float) $row[ $column ];
			}
		}

		return $row;
	}

	/**
	 * Insert one campaign, ignoring one that is already there.
	 *
	 * Used by {@see self::record()} and by the migration out of the old option.
	 * The unique key on `uuid` is what makes running the migration twice safe.
	 *
	 * @param array<string, mixed> $campaign A campaign record.
	 * @return bool Whether a row is now present for this campaign.
	 */
	public static function insert( array $campaign ): bool {
		global $wpdb;

		$uuid = (string) ( $campaign['uuid'] ?? '' );

		if ( '' === $uuid ) {
			return false;
		}

		if ( null !== self::find( $uuid ) ) {
			return true;
		}

		$values = self::to_columns( $campaign );

		if ( ! isset( $values['created_at'] ) || '' === (string) $values['created_at'] ) {
			$values['created_at'] = gmdate( 'Y-m-d H:i:s' );
		}

		$formats = array();

		foreach ( array_keys( $values ) as $column ) {
			$formats[] = self::columns()[ $column ] ?? '%s';
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table.
		return false !== $wpdb->insert( Schema::campaigns_table_name(), $values, $formats );
	}

	/**
	 * Remember a campaign that has just been accepted.
	 *
	 * @param array<string, mixed> $campaign A campaign record.
	 */
	public static function record( array $campaign ): void {
		self::insert( $campaign );

		// The analytics count campaigns from this table, so a new one changes
		// every figure on both surfaces.
		Stats::flush();
	}

	/**
	 * Merge new fields into a remembered campaign.
	 *
	 * @param string               $uuid   Campaign UUID.
	 * @param array<string, mixed> $fields Fields to set.
	 * @return bool Whether the campaign was found.
	 */
	public static function update( string $uuid, array $fields ): bool {
		global $wpdb;

		if ( '' === $uuid || null === self::find( $uuid ) ) {
			return false;
		}

		$values = self::to_columns( $fields );

		unset( $values['uuid'], $values['created_at'] );

		// A progress refresh that resolved nothing must not blank the row.
		$values = array_filter(
			$values,
			static fn( $value, string $column ): bool => array_key_exists( $column, $fields )
				|| ( isset( $fields['excluded'] ) && in_array( $column, array( 'excluded_suppressed', 'excluded_unusable' ), true ) ),
			ARRAY_FILTER_USE_BOTH
		);

		if ( empty( $values ) ) {
			return true;
		}

		$formats = array();

		foreach ( array_keys( $values ) as $column ) {
			$formats[] = self::columns()[ $column ] ?? '%s';
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table.
		$wpdb->update( Schema::campaigns_table_name(), $values, array( 'uuid' => $uuid ), $formats, array( '%s' ) );

		// Where a campaign's cost and delivered counts land, so the figures
		// behind both screens have just changed.
		Stats::flush();

		return true;
	}

	/**
	 * One remembered campaign.
	 *
	 * @param string $uuid Campaign UUID.
	 * @return array<string, mixed>|null
	 */
	public static function find( string $uuid ): ?array {
		global $wpdb;

		if ( '' === $uuid ) {
			return null;
		}

		$table = Schema::campaigns_table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom table; the name comes from $wpdb->prefix.
		$row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE uuid = %s", $uuid ), ARRAY_A );

		return is_array( $row ) ? self::from_columns( $row ) : null;
	}

	/**
	 * Every remembered campaign, most recent first.
	 *
	 * Whole-table read, so it is for the counting rules and the tests that check
	 * them rather than for a screen. The screens use {@see self::paged()}.
	 *
	 * @return array<int, array<string, mixed>>
	 */
	public static function all(): array {
		global $wpdb;

		$table = Schema::campaigns_table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom table; the name comes from $wpdb->prefix.
		$rows = $wpdb->get_results( "SELECT * FROM {$table} ORDER BY created_at DESC, id DESC", ARRAY_A );

		return is_array( $rows ) ? array_map( array( self::class, 'from_columns' ), $rows ) : array();
	}

	/**
	 * One page of history, most recent first.
	 *
	 * @param Paging $paging Which page.
	 * @return array<int, array<string, mixed>>
	 */
	public static function paged( Paging $paging ): array {
		global $wpdb;

		$table = Schema::campaigns_table_name();

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom table; the name comes from $wpdb->prefix.
		$sql = "SELECT * FROM {$table} ORDER BY created_at DESC, id DESC LIMIT %d OFFSET %d";

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom table; prepared on the line above.
		$rows = $wpdb->get_results( $wpdb->prepare( $sql, $paging->per_page(), $paging->offset() ), ARRAY_A );

		return is_array( $rows ) ? array_map( array( self::class, 'from_columns' ), $rows ) : array();
	}

	/**
	 * How many campaigns are remembered.
	 */
	public static function count(): int {
		global $wpdb;

		$table = Schema::campaigns_table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom table; the name comes from $wpdb->prefix.
		return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" );
	}

	/**
	 * Campaigns the platform has not finished with.
	 *
	 * @return array<int, array<string, mixed>>
	 */
	public static function in_flight(): array {
		global $wpdb;

		$table        = Schema::campaigns_table_name();
		$placeholders = implode( ', ', array_fill( 0, count( self::IN_FLIGHT ), '%s' ) );

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom table from $wpdb->prefix; the placeholders are counted from a constant.
		$sql = "SELECT * FROM {$table} WHERE status IN ( {$placeholders} ) ORDER BY created_at DESC, id DESC";

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom table; prepared on the line above.
		$rows = $wpdb->get_results( $wpdb->prepare( $sql, self::IN_FLIGHT ), ARRAY_A );

		return is_array( $rows ) ? array_map( array( self::class, 'from_columns' ), $rows ) : array();
	}

	/**
	 * Campaigns that went out and were never priced.
	 *
	 * A campaign's cost arrives on the poll that sees it settle. One that
	 * finished before this site knew to ask for a cost is never polled again —
	 * {@see self::in_flight()} only returns campaigns still running — so it
	 * would stay unpriced forever, and the spend total would stay short by
	 * exactly the amount it is trying to report.
	 *
	 * Oldest first, so a backlog empties in the order it accumulated. Personal
	 * campaigns are excluded: they were never sent to the platform, so there is
	 * nothing there to ask, and their cost already sits in the message log.
	 * Sandboxed ones cost nothing to begin with.
	 *
	 * @param int $limit How many to return.
	 * @return array<int, array<string, mixed>>
	 */
	public static function unpriced( int $limit = 5 ): array {
		global $wpdb;

		$table = Schema::campaigns_table_name();

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom table; the name comes from $wpdb->prefix.
		$sql = "SELECT * FROM {$table}
			WHERE cost IS NULL
			AND sandbox = 0
			AND dispatched_at <> ''
			AND " . self::NOT_PERSONAL . '
			ORDER BY created_at ASC, id ASC
			LIMIT %d';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom table; prepared on the line above.
		$rows = $wpdb->get_results( $wpdb->prepare( $sql, max( 1, $limit ) ), ARRAY_A );

		return is_array( $rows ) ? array_map( array( self::class, 'from_columns' ), $rows ) : array();
	}

	/**
	 * How many messages campaigns have accounted for since a moment.
	 *
	 * Spend caps count rows in the message log, and a campaign puts none there —
	 * the platform fans it out, so the individual sends are never seen locally.
	 * Without this a five thousand message blast is invisible to a five hundred
	 * a day cap, and order updates carry on flowing while the budget it was
	 * meant to protect has already been spent several times over.
	 *
	 * Counted from the recipient list this site submitted rather than from what
	 * the platform reports delivered, because the money is committed at the
	 * moment the campaign is accepted.
	 *
	 * The rules are {@see self::count_since()}, which stays pure and is the
	 * reference this query is tested against — a spend cap that quietly starts
	 * counting differently is the one bug here that costs real money.
	 *
	 * @param string $since UTC datetime, `Y-m-d H:i:s`.
	 */
	public static function messages_since( string $since ): int {
		global $wpdb;

		$table = Schema::campaigns_table_name();

		/*
		 * The three exclusions, in the same order and with the same meaning as
		 * the pure implementation below: sandboxed campaigns cost nothing; a
		 * campaign cancelled before it dispatched cost nothing, while one
		 * cancelled part way through counts in full because how much of it went
		 * out is not knowable from here; and anything older than the window is
		 * outside the question.
		 */
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom table; the name comes from $wpdb->prefix.
		$sql = "SELECT COALESCE( SUM( recipients ), 0 ) FROM {$table}
			WHERE sandbox = 0
			AND NOT ( status = 'cancelled' AND dispatched_at = '' )
			AND " . self::NOT_PERSONAL . '
			AND created_at >= %s';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom table; prepared on the line above.
		return (int) $wpdb->get_var( $wpdb->prepare( $sql, $since ) );
	}

	/**
	 * Whether a campaign was sent from here, one message at a time.
	 *
	 * The PHP twin of {@see self::NOT_PERSONAL}. Kept as one function so the two
	 * pure rules cannot come to disagree with each other or with the three
	 * queries, which is the failure that would silently halve a spend cap.
	 *
	 * @param array<string, mixed> $row A campaign record.
	 */
	public static function is_personal( array $row ): bool {
		return self::DELIVERY_PERSONAL === (string) ( $row['delivery'] ?? '' );
	}

	/**
	 * The counting, over a history passed in.
	 *
	 * Pure, so the rule that sandboxed and cancelled campaigns cost nothing can
	 * be tested without an options table — and so the SQL above has something to
	 * be checked against.
	 *
	 * @param array<int, array<string, mixed>> $history Campaign records.
	 * @param string                           $since   UTC datetime, `Y-m-d H:i:s`.
	 */
	public static function count_since( array $history, string $since ): int {
		$total = 0;

		foreach ( $history as $row ) {
			if ( ! empty( $row['sandbox'] ) ) {
				continue;
			}

			// Cancelled before it sent, so nothing was billed. A campaign
			// cancelled part way through still counts in full, because how much
			// of it went out is not knowable from here.
			if ( 'cancelled' === (string) ( $row['status'] ?? '' ) && empty( $row['dispatched_at'] ) ) {
				continue;
			}

			// Sent from here rather than by the platform, so every one of its
			// messages is already a row in the message log. Counting it here as
			// well would count it twice — see self::DELIVERY_PERSONAL.
			if ( self::is_personal( $row ) ) {
				continue;
			}

			if ( (string) ( $row['created_at'] ?? '' ) < $since ) {
				continue;
			}

			$total += (int) ( $row['recipients'] ?? 0 );
		}

		return $total;
	}

	/**
	 * What campaigns have actually sent since a moment, for the analytics.
	 *
	 * ### Why this is not {@see self::count_since()}
	 *
	 * They ask different questions and must keep answering them differently.
	 *
	 * The spend cap asks **what is committed**, so it counts a campaign the
	 * moment the platform accepts it — including one scheduled for next week,
	 * because the credits are spoken for and a cap that ignored them would let a
	 * merchant schedule their way straight through it.
	 *
	 * The analytics ask **what has gone out**. A campaign scheduled for Friday
	 * has sent nothing on Tuesday, and counting it would report messages that do
	 * not exist yet and drag the delivery rate down while it waits. This is the
	 * same distinction {@see \TextTango\Analytics\Stats::SENT} already draws by
	 * leaving queued messages out of the sent total.
	 *
	 * So: the two exclusions above — sandboxed, and cancelled before dispatch —
	 * and a third, never dispatched at all. Pure, and kept beside the rule it
	 * differs from so the difference stays deliberate rather than becoming drift.
	 *
	 * @param array<int, array<string, mixed>> $history Campaign records.
	 * @param string                           $since   UTC datetime, `Y-m-d H:i:s`.
	 * @return array{campaigns: int, recipients: int, delivered: int, failed: int, credits: int}
	 */
	public static function sent_since( array $history, string $since ): array {
		$totals = array(
			'campaigns'  => 0,
			'recipients' => 0,
			'delivered'  => 0,
			'failed'     => 0,
			'credits'    => 0,
		);

		foreach ( $history as $row ) {
			if ( ! empty( $row['sandbox'] ) ) {
				continue;
			}

			// Never dispatched, so nothing has been sent yet — whether it is
			// scheduled for later or was cancelled before it started.
			if ( '' === (string) ( $row['dispatched_at'] ?? '' ) ) {
				continue;
			}

			// Already counted in the message log, one row per recipient.
			if ( self::is_personal( $row ) ) {
				continue;
			}

			if ( (string) ( $row['created_at'] ?? '' ) < $since ) {
				continue;
			}

			++$totals['campaigns'];
			$totals['recipients'] += (int) ( $row['recipients'] ?? 0 );
			$totals['delivered']  += (int) ( $row['delivered'] ?? 0 );
			$totals['failed']     += (int) ( $row['failed'] ?? 0 );
			$totals['credits']    += (int) ( $row['credits'] ?? 0 );
		}

		return $totals;
	}

	/**
	 * The same totals, from the table.
	 *
	 * The SQL twin of {@see self::sent_since()}, carrying the same three
	 * exclusions in the same order.
	 *
	 * @param string $since UTC datetime, `Y-m-d H:i:s`.
	 * @return array{campaigns: int, recipients: int, delivered: int, failed: int, credits: int, cost: float, unpriced: int}
	 */
	public static function totals_since( string $since ): array {
		global $wpdb;

		$table = Schema::campaigns_table_name();

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom table; the name comes from $wpdb->prefix.
		$sql = "SELECT COUNT(*) AS campaigns,
				COALESCE( SUM( recipients ), 0 ) AS recipients,
				COALESCE( SUM( delivered ), 0 ) AS delivered,
				COALESCE( SUM( failed ), 0 ) AS failed,
				COALESCE( SUM( credits ), 0 ) AS credits,
				COALESCE( SUM( cost ), 0 ) AS cost,
				SUM( cost IS NULL ) AS unpriced
			FROM {$table}
			WHERE sandbox = 0
			AND dispatched_at <> ''
			AND " . self::NOT_PERSONAL . '
			AND created_at >= %s';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom table; prepared on the line above.
		$row = $wpdb->get_row( $wpdb->prepare( $sql, $since ), ARRAY_A );

		return array(
			'campaigns'  => (int) ( $row['campaigns'] ?? 0 ),
			'recipients' => (int) ( $row['recipients'] ?? 0 ),
			'delivered'  => (int) ( $row['delivered'] ?? 0 ),
			'failed'     => (int) ( $row['failed'] ?? 0 ),
			'credits'    => (int) ( $row['credits'] ?? 0 ),
			'cost'       => (float) ( $row['cost'] ?? 0 ),
			/*
			 * How many of those campaigns the platform has not priced yet.
			 *
			 * `SUM( cost IS NULL )` rather than a second query: MySQL counts a
			 * true predicate as 1, so it comes back in the same pass. The screens
			 * use it to say that a total is still partial — which is the honest
			 * thing to do, given the bug being fixed here was a total that was
			 * quietly short and said nothing about it.
			 */
			'unpriced'   => (int) ( $row['unpriced'] ?? 0 ),
		);
	}

	/**
	 * Recipients per day, for the chart.
	 *
	 * Bucketed by `created_at` rather than `dispatched_at`, which is stored as
	 * ISO-8601 with an offset (`2026-08-02T22:35:00+00:00`) and which MySQL's
	 * `DATE()` will not reliably parse. For a campaign sent as soon as it is
	 * composed — which is nearly all of them — the two are the same minute; a
	 * campaign scheduled for later lands on the day it was written, which is a
	 * day early and still better than a day parsed wrong.
	 *
	 * @param string $since UTC datetime, `Y-m-d H:i:s`.
	 * @return array<string, int> `Y-m-d` => recipients.
	 */
	public static function daily_since( string $since ): array {
		global $wpdb;

		$table = Schema::campaigns_table_name();

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom table; the name comes from $wpdb->prefix.
		$sql = "SELECT DATE( created_at ) AS d, COALESCE( SUM( recipients ), 0 ) AS n
			FROM {$table}
			WHERE sandbox = 0
			AND dispatched_at <> ''
			AND " . self::NOT_PERSONAL . '
			AND created_at >= %s
			GROUP BY DATE( created_at )';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom table; prepared on the line above.
		$rows = $wpdb->get_results( $wpdb->prepare( $sql, $since ), ARRAY_A );

		$daily = array();

		foreach ( (array) $rows as $row ) {
			$daily[ (string) $row['d'] ] = (int) $row['n'];
		}

		return $daily;
	}
}
