<?php
/**
 * Eventin Stats Query
 *
 * @package Aisentic\Integrations\Eventin
 */

declare(strict_types=1);

namespace Aisentic\Integrations\Eventin;

defined( 'ABSPATH' ) || exit;

/**
 * All SQL for the Eventin stats tools: counts of events, bookings and
 * attendees, plus speakers, organizers and schedules.
 *
 * Why SQL and not WP_Query: the sandbox has 251,014 bookings. Loading them
 * would take minutes. One GROUP BY takes 0.05 to 0.7 s for most questions.
 * A site-wide grouping plus a second filter takes about 5 s, so results
 * are cached for 5 minutes.
 * Keeping all SQL here lets tests replace this class with a fake.
 */
class Eventin_Stats_Query {

	/**
	 * How long a count is kept, in seconds.
	 */
	private const CACHE_TTL = 5 * MINUTE_IN_SECONDS;

	/**
	 * The two record types, and where each one keeps its fields.
	 *
	 * Bookings are `etn-order` posts. Attendees are `etn-attendee` posts.
	 * The two use different meta keys for the same idea.
	 */
	private const RECORDS = [
		'booking'  => [
			'post_type' => 'etn-order',
			'event'     => 'event_id',
			'status'    => 'status',
			'ticket'    => '',
		],
		'attendee' => [
			'post_type' => 'etn-attendee',
			'event'     => 'etn_event_id',
			'status'    => 'etn_status',
			'ticket'    => 'ticket_name',
		],
	];

	/**
	 * Count events by when they run.
	 *
	 * Upcoming, ongoing and past use the same date rules as
	 * eventin-list-events, so the two tools never disagree.
	 *
	 * @return array{all:int,published:int,upcoming:int,ongoing:int,past:int,no_date:int,draft:int}
	 */
	public function count_events(): array {
		return $this->cached(
			'events',
			function () {
				global $wpdb;

				$today = current_time( 'Y-m-d' );

				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- cached above.
				$row = $wpdb->get_row(
					$wpdb->prepare(
						"SELECT
							COUNT(*) AS all_events,
							SUM( p.post_status = 'publish' ) AS published,
							SUM( p.post_status = 'publish' AND s.meta_value >= %s ) AS upcoming,
							SUM( p.post_status = 'publish' AND s.meta_value <= %s AND e.meta_value >= %s ) AS ongoing,
							SUM( p.post_status = 'publish' AND e.meta_value < %s AND e.meta_value <> '' ) AS past,
							SUM( p.post_status = 'publish' AND ( s.meta_value IS NULL OR s.meta_value = '' ) ) AS no_date,
							SUM( p.post_status = 'draft' ) AS draft
						FROM {$wpdb->posts} p
						LEFT JOIN (
							SELECT post_id, MIN( meta_value ) AS meta_value
							FROM {$wpdb->postmeta}
							WHERE meta_key = 'etn_start_date'
							GROUP BY post_id
						) s ON s.post_id = p.ID
						LEFT JOIN (
							SELECT post_id, MAX( meta_value ) AS meta_value
							FROM {$wpdb->postmeta}
							WHERE meta_key = 'etn_end_date'
							GROUP BY post_id
						) e ON e.post_id = p.ID
						WHERE p.post_type = 'etn'
						AND p.post_status NOT IN ( 'trash', 'auto-draft', 'inherit' )",
						$today,
						$today,
						$today,
						$today
					),
					ARRAY_A
				);

				$row = is_array( $row ) ? $row : [];

				return [
					'all'       => (int) ( $row['all_events'] ?? 0 ),
					'published' => (int) ( $row['published'] ?? 0 ),
					'upcoming'  => (int) ( $row['upcoming'] ?? 0 ),
					'ongoing'   => (int) ( $row['ongoing'] ?? 0 ),
					'past'      => (int) ( $row['past'] ?? 0 ),
					'no_date'   => (int) ( $row['no_date'] ?? 0 ),
					'draft'     => (int) ( $row['draft'] ?? 0 ),
				];
			}
		);
	}

	/**
	 * Find events by title.
	 *
	 * Exact title matches first. Only if there are none, titles that contain
	 * the text. So "Tech Meetup" does not also return "Tech Meetup Afterparty".
	 *
	 * @param string $name  Title, or part of it.
	 * @param int    $limit Most rows to return.
	 * @return array<int, array{id:int,title:string,start_date:string,post_status:string}>
	 */
	public function find_events( string $name, int $limit = 6 ): array {
		global $wpdb;

		$name = trim( $name );

		if ( '' === $name ) {
			return [];
		}

		$select = "SELECT p.ID AS id, p.post_title AS title, p.post_status, COALESCE( s.meta_value, '' ) AS start_date
			FROM {$wpdb->posts} p
			LEFT JOIN {$wpdb->postmeta} s ON s.post_id = p.ID AND s.meta_key = 'etn_start_date'
			WHERE p.post_type = 'etn'
			AND p.post_status NOT IN ( 'trash', 'auto-draft', 'inherit' )";

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $select holds only table names.
		$rows = $wpdb->get_results( $wpdb->prepare( "{$select} AND p.post_title = %s ORDER BY s.meta_value DESC LIMIT %d", $name, $limit ), ARRAY_A );

		if ( empty( $rows ) ) {
			$like = '%' . $wpdb->esc_like( $name ) . '%';
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $select holds only table names.
			$rows = $wpdb->get_results( $wpdb->prepare( "{$select} AND p.post_title LIKE %s ORDER BY s.meta_value DESC LIMIT %d", $like, $limit ), ARRAY_A );
		}

		return array_map(
			static function ( $row ) {
				return [
					'id'          => (int) $row['id'],
					'title'       => (string) $row['title'],
					'start_date'  => (string) $row['start_date'],
					'post_status' => (string) $row['post_status'],
				];
			},
			is_array( $rows ) ? $rows : []
		);
	}

	/**
	 * Event titles by ID, for naming the rows of a per-event count.
	 *
	 * @param int[] $ids Event IDs.
	 * @return array<int, string>
	 */
	public function event_titles( array $ids ): array {
		global $wpdb;

		$ids = array_values( array_filter( array_map( 'intval', $ids ) ) );

		if ( empty( $ids ) ) {
			return [];
		}

		$placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- placeholders built above.
		$rows = $wpdb->get_results( $wpdb->prepare( "SELECT ID, post_title FROM {$wpdb->posts} WHERE ID IN ({$placeholders})", $ids ), ARRAY_A );

		$titles = [];
		foreach ( (array) $rows as $row ) {
			$titles[ (int) $row['ID'] ] = (string) $row['post_title'];
		}

		return $titles;
	}

	/**
	 * Count bookings or attendees, with optional filters and one grouping.
	 *
	 * @param string $record   'booking' or 'attendee'.
	 * @param array  $filters  Any of: event_id (int), status (string),
	 *                         ticket (string, attendees only),
	 *                         date_from / date_to (Y-m-d, bookings only).
	 * @param string $group_by '', 'status', 'event', 'ticket' or 'date'.
	 * @return array{total:int,groups:array<string,int>}
	 */
	public function count_records( string $record, array $filters = [], string $group_by = '' ): array {
		if ( ! isset( self::RECORDS[ $record ] ) ) {
			return [
				'total'  => 0,
				'groups' => [],
			];
		}

		$key = $record . ':' . $group_by . ':' . wp_json_encode( $filters );

		return $this->cached(
			$key,
			function () use ( $record, $filters, $group_by ) {
				return $this->run_count( self::RECORDS[ $record ], $filters, $group_by );
			}
		);
	}

	/**
	 * Net revenue for one event, from Eventin's own report.
	 *
	 * Uses the same function as Eventin's dashboard, so the numbers match.
	 * It handles refunds and tax like Eventin. A plain SUM would not.
	 *
	 * @param int $event_id Event ID.
	 * @return float|null Null when Eventin's report is not available.
	 */
	public function event_revenue( int $event_id ): ?float {
		if ( ! class_exists( '\Eventin\Reports\RevenueReport' ) ) {
			return null;
		}

		return (float) \Eventin\Reports\RevenueReport::get_total_revenue( [], $event_id );
	}

	/**
	 * Everyone with the speaker or the organizer role.
	 *
	 * Eventin stores both as WordPress users with a role. One user can have
	 * both roles.
	 *
	 * @param string $role 'speaker' or 'organizer'.
	 * @return array<int, array{id:int,name:string,email:string,designation:string,company:string,groups:int[]}>
	 *         Keyed by user ID.
	 */
	public function people( string $role ): array {
		return $this->cached(
			'people:' . $role,
			function () use ( $role ) {
				$people = [];

				foreach ( get_users( [ 'role' => 'etn-' . $role ] ) as $user ) {
					$id = (int) $user->ID;

					$people[ $id ] = [
						'id'          => $id,
						'name'        => (string) $user->display_name,
						'email'       => (string) ( get_user_meta( $id, 'etn_speaker_website_email', true ) ? get_user_meta( $id, 'etn_speaker_website_email', true ) : $user->user_email ),
						'designation' => (string) ( get_user_meta( $id, 'etn_speaker_designation', true ) ? get_user_meta( $id, 'etn_speaker_designation', true ) : get_user_meta( $id, 'designation', true ) ),
						'company'     => (string) ( get_user_meta( $id, 'etn_company_name', true ) ? get_user_meta( $id, 'etn_company_name', true ) : get_user_meta( $id, 'company_name', true ) ),
						'groups'      => self::id_list( get_user_meta( $id, 'etn_speaker_group', true ) ),
					];
				}

				return $people;
			}
		);
	}

	/**
	 * Which speakers or organizers each event has.
	 *
	 * An event lists people by ID, or by group. For a group, everyone in the
	 * group counts, the same as on Eventin's event screen.
	 *
	 * @param string $role 'speaker' or 'organizer'.
	 * @return array<int, int[]> Event ID => user IDs. Events with nobody are left out.
	 */
	public function event_people( string $role ): array {
		return $this->cached(
			'event_people:' . $role,
			function () use ( $role ) {
				global $wpdb;

				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- cached above.
				$rows = $wpdb->get_results(
					$wpdb->prepare(
						"SELECT p.ID AS id, l.meta_value AS list, t.meta_value AS type, g.meta_value AS grp
						FROM {$wpdb->posts} p
						LEFT JOIN {$wpdb->postmeta} l ON l.post_id = p.ID AND l.meta_key = %s
						LEFT JOIN {$wpdb->postmeta} t ON t.post_id = p.ID AND t.meta_key = %s
						LEFT JOIN {$wpdb->postmeta} g ON g.post_id = p.ID AND g.meta_key = %s
						WHERE p.post_type = 'etn'
						AND p.post_status NOT IN ( 'trash', 'auto-draft', 'inherit' )",
						'etn_event_' . $role,
						$role . '_type',
						$role . '_group'
					),
					ARRAY_A
				);

				$people = null;
				$events = [];

				foreach ( is_array( $rows ) ? $rows : [] as $row ) {
					if ( 'group' === (string) $row['type'] ) {
						$people = $people ?? $this->people( $role );
						$groups = self::id_list( $row['grp'] );
						$ids    = array_keys( array_filter( $people, static fn( $p ) => array_intersect( $groups, $p['groups'] ) ) );
					} else {
						$ids = self::id_list( $row['list'] );
					}

					if ( ! empty( $ids ) ) {
						$events[ (int) $row['id'] ] = array_values( array_unique( $ids ) );
					}
				}

				return $events;
			}
		);
	}

	/**
	 * Title, dates and status of some events.
	 *
	 * @param int[] $ids Event IDs.
	 * @return array<int, array{id:int,title:string,start_date:string,end_date:string,post_status:string}>
	 *         Keyed by event ID, latest start first.
	 */
	public function events_info( array $ids ): array {
		global $wpdb;

		$ids = array_values( array_filter( array_map( 'intval', $ids ) ) );

		if ( empty( $ids ) ) {
			return [];
		}

		$placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- placeholders built above.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT p.ID AS id, p.post_title AS title, p.post_status, COALESCE( s.meta_value, '' ) AS start_date, COALESCE( e.meta_value, '' ) AS end_date
				FROM {$wpdb->posts} p
				LEFT JOIN {$wpdb->postmeta} s ON s.post_id = p.ID AND s.meta_key = 'etn_start_date'
				LEFT JOIN {$wpdb->postmeta} e ON e.post_id = p.ID AND e.meta_key = 'etn_end_date'
				WHERE p.ID IN ({$placeholders})
				ORDER BY s.meta_value DESC",
				$ids
			),
			ARRAY_A
		);
		// phpcs:enable

		$events = [];
		foreach ( is_array( $rows ) ? $rows : [] as $row ) {
			$events[ (int) $row['id'] ] = [
				'id'          => (int) $row['id'],
				'title'       => (string) $row['title'],
				'start_date'  => (string) $row['start_date'],
				'end_date'    => (string) $row['end_date'],
				'post_status' => (string) $row['post_status'],
			];
		}

		return $events;
	}

	/**
	 * Names of speaker and organizer groups.
	 *
	 * @param int[] $ids Group (term) IDs.
	 * @return array<int, string>
	 */
	public function group_names( array $ids ): array {
		$names = [];

		foreach ( array_unique( array_map( 'intval', $ids ) ) as $id ) {
			$term = get_term( $id, 'etn_speaker_category' );

			if ( $term && ! is_wp_error( $term ) ) {
				$names[ $id ] = (string) $term->name;
			}
		}

		return $names;
	}

	/**
	 * Every schedule, with its sessions.
	 *
	 * A schedule is one day of an agenda: an `etn-schedule` post with a date
	 * and sessions (Eventin calls them topics). Events link to schedules;
	 * schedules do not link back.
	 *
	 * @return array<int, array{id:int,title:string,date:string,sessions:array}>
	 *         Keyed by schedule ID. Each session has topic, start, end, room and speakers (user IDs).
	 */
	public function schedules(): array {
		return $this->cached(
			'schedules',
			function () {
				global $wpdb;

				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- cached above.
				$rows = $wpdb->get_results(
					"SELECT p.ID AS id, p.post_title, COALESCE( t.meta_value, '' ) AS title, COALESCE( d.meta_value, '' ) AS date, tp.meta_value AS topics
					FROM {$wpdb->posts} p
					LEFT JOIN {$wpdb->postmeta} t ON t.post_id = p.ID AND t.meta_key = 'etn_schedule_title'
					LEFT JOIN {$wpdb->postmeta} d ON d.post_id = p.ID AND d.meta_key = 'etn_schedule_date'
					LEFT JOIN {$wpdb->postmeta} tp ON tp.post_id = p.ID AND tp.meta_key = 'etn_schedule_topics'
					WHERE p.post_type = 'etn-schedule'
					AND p.post_status NOT IN ( 'trash', 'auto-draft', 'inherit' )
					ORDER BY d.meta_value DESC",
					ARRAY_A
				);

				$schedules = [];

				foreach ( is_array( $rows ) ? $rows : [] as $row ) {
					$sessions = [];
					$topics   = maybe_unserialize( (string) $row['topics'] );

					foreach ( is_array( $topics ) ? $topics : [] as $topic ) {
						if ( ! is_array( $topic ) ) {
							continue;
						}

						// Eventin spells these keys "shedule".
						$sessions[] = [
							'topic'    => (string) ( $topic['etn_schedule_topic'] ?? '' ),
							'start'    => (string) ( $topic['etn_shedule_start_time'] ?? '' ),
							'end'      => (string) ( $topic['etn_shedule_end_time'] ?? '' ),
							'room'     => (string) ( $topic['etn_shedule_room'] ?? '' ),
							'speakers' => self::id_list( $topic['speakers'] ?? [] ),
						];
					}

					$schedules[ (int) $row['id'] ] = [
						'id'       => (int) $row['id'],
						'title'    => '' !== trim( (string) $row['title'] ) ? (string) $row['title'] : (string) $row['post_title'],
						'date'     => (string) $row['date'],
						'sessions' => $sessions,
					];
				}

				return $schedules;
			}
		);
	}

	/**
	 * Which schedules each event uses.
	 *
	 * @return array<int, int[]> Event ID => schedule IDs. Events with none are left out.
	 */
	public function event_schedules(): array {
		return $this->cached(
			'event_schedules',
			function () {
				global $wpdb;

				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- cached above.
				$rows = $wpdb->get_results(
					"SELECT p.ID AS id, m.meta_value AS list
					FROM {$wpdb->posts} p
					INNER JOIN {$wpdb->postmeta} m ON m.post_id = p.ID AND m.meta_key = 'etn_event_schedule'
					WHERE p.post_type = 'etn'
					AND p.post_status NOT IN ( 'trash', 'auto-draft', 'inherit' )",
					ARRAY_A
				);

				$events = [];
				foreach ( is_array( $rows ) ? $rows : [] as $row ) {
					$ids = self::id_list( $row['list'] );

					if ( ! empty( $ids ) ) {
						$events[ (int) $row['id'] ] = $ids;
					}
				}

				return $events;
			}
		);
	}

	/**
	 * Read a list of IDs in any format Eventin stores it.
	 *
	 * Formats seen: PHP array, serialized array (sometimes with a trailing
	 * newline), JSON, a single "16", and lists with 0 for "nobody".
	 *
	 * @param mixed $value Stored value.
	 * @return int[] Positive IDs only.
	 */
	public static function id_list( $value ): array {
		if ( is_string( $value ) ) {
			$value   = trim( $value );
			$decoded = json_decode( $value, true );
			$value   = null !== $decoded ? $decoded : maybe_unserialize( $value );
		}

		$value = is_array( $value ) ? $value : [ $value ];
		$ids   = [];

		foreach ( $value as $item ) {
			if ( is_numeric( $item ) && (int) $item > 0 ) {
				$ids[] = (int) $item;
			}
		}

		return array_values( array_unique( $ids ) );
	}

	/**
	 * Build and run one count query.
	 *
	 * Starts from wp_postmeta and fixes the join order with STRAIGHT_JOIN.
	 * Why: MySQL chose to start from wp_posts and took 5 s instead of 0.7 s.
	 *
	 * @param array  $map      One entry of RECORDS.
	 * @param array  $filters  See count_records().
	 * @param string $group_by See count_records().
	 * @return array{total:int,groups:array<string,int>}
	 */
	private function run_count( array $map, array $filters, string $group_by ): array {
		global $wpdb;

		// Meta joins: alias => meta key. Values to match go in $where.
		$metas  = [];
		$where  = [];
		$params = [];

		if ( ! empty( $filters['event_id'] ) ) {
			$metas['ev'] = $map['event'];
			$where[]     = 'ev.meta_value = %s';
			$params[]    = (string) (int) $filters['event_id'];
		}

		if ( isset( $filters['status'] ) && '' !== $filters['status'] ) {
			$metas['st'] = $map['status'];
			$where[]     = 'st.meta_value = %s';
			$params[]    = (string) $filters['status'];
		}

		if ( '' !== $map['ticket'] && isset( $filters['ticket'] ) && '' !== $filters['ticket'] ) {
			$metas['tk'] = $map['ticket'];
			$where[]     = 'tk.meta_value = %s';
			$params[]    = (string) $filters['ticket'];
		}

		if ( ! empty( $filters['date_from'] ) ) {
			$where[]  = 'p.post_date >= %s';
			$params[] = $filters['date_from'] . ' 00:00:00';
		}

		if ( ! empty( $filters['date_to'] ) ) {
			$where[]  = 'p.post_date <= %s';
			$params[] = $filters['date_to'] . ' 23:59:59';
		}

		$group_expr = '';

		switch ( $group_by ) {
			case 'status':
				$metas['st'] = $map['status'];
				$group_expr  = "COALESCE( st.meta_value, '' )";
				break;
			case 'event':
				$metas['ev'] = $map['event'];
				$group_expr  = 'ev.meta_value';
				break;
			case 'ticket':
				if ( '' !== $map['ticket'] ) {
					$metas['tk'] = $map['ticket'];
					$group_expr  = "COALESCE( tk.meta_value, '' )";
				}
				break;
			case 'date':
				$group_expr = $this->is_long_range( $filters ) ? "DATE_FORMAT( p.post_date, '%%Y-%%m' )" : 'DATE( p.post_date )';
				break;
		}

		// Put the event filter first, because it matches the fewest rows. Others keep their order.
		$order = array_keys( $metas );
		usort(
			$order,
			static function ( $a, $b ) use ( $filters ) {
				$rank = static function ( $alias ) use ( $filters ) {
					if ( 'ev' === $alias && ! empty( $filters['event_id'] ) ) {
						return 0;
					}
					return 1;
				};
				return $rank( $a ) <=> $rank( $b );
			}
		);

		$join_params = [];

		if ( empty( $order ) ) {
			$from = "{$wpdb->posts} p";
		} else {
			$first         = array_shift( $order );
			$from          = "{$wpdb->postmeta} {$first} STRAIGHT_JOIN {$wpdb->posts} p ON p.ID = {$first}.post_id";
			$join_params[] = $metas[ $first ];

			foreach ( $order as $alias ) {
				$from         .= " INNER JOIN {$wpdb->postmeta} {$alias} ON {$alias}.post_id = p.ID AND {$alias}.meta_key = %s";
				$join_params[] = $metas[ $alias ];
			}

			array_unshift( $where, "{$first}.meta_key = %s" );
		}

		// The first meta key goes into WHERE, every other one into its JOIN.
		$first_key = array_shift( $join_params );
		$all       = array_merge( $join_params, null === $first_key ? [] : [ $first_key ], $params, [ $map['post_type'] ] );

		$where[] = 'p.post_type = %s';
		$where[] = "p.post_status NOT IN ( 'trash', 'auto-draft' )";

		// DISTINCT: a post with two rows for the same meta key would
		// otherwise be counted twice.
		$select = '' === $group_expr ? 'COUNT( DISTINCT p.ID ) AS c' : "{$group_expr} AS g, COUNT( DISTINCT p.ID ) AS c";
		$sql    = "SELECT {$select} FROM {$from} WHERE " . implode( ' AND ', $where );

		if ( '' !== $group_expr ) {
			$sql .= ' GROUP BY g ORDER BY c DESC';
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- every value is a placeholder; cached by the caller.
		$rows = $wpdb->get_results( $wpdb->prepare( $sql, $all ), ARRAY_A );
		$rows = is_array( $rows ) ? $rows : [];

		if ( '' === $group_expr ) {
			return [
				'total'  => (int) ( $rows[0]['c'] ?? 0 ),
				'groups' => [],
			];
		}

		$groups = [];
		$total  = 0;
		foreach ( $rows as $row ) {
			$groups[ (string) $row['g'] ] = (int) $row['c'];
			$total                       += (int) $row['c'];
		}

		if ( 'date' === $group_by ) {
			ksort( $groups );
		}

		return [
			'total'  => $total,
			'groups' => $groups,
		];
	}

	/**
	 * Is the date range longer than about a month?
	 *
	 * Also true when a date is missing. Long ranges are grouped by month,
	 * so a year does not return 365 rows.
	 *
	 * @param array $filters Filters with date_from / date_to.
	 * @return bool
	 */
	private function is_long_range( array $filters ): bool {
		if ( empty( $filters['date_from'] ) || empty( $filters['date_to'] ) ) {
			return true;
		}

		$days = ( strtotime( $filters['date_to'] ) - strtotime( $filters['date_from'] ) ) / DAY_IN_SECONDS;

		return $days > 31;
	}

	/**
	 * Run a count once and keep the result for a few minutes.
	 *
	 * Why: follow-up questions often use the same numbers, and the slowest
	 * query takes about 5 seconds. The key also includes today's date.
	 *
	 * @param string   $key     Cache key.
	 * @param callable $compute Returns the value to keep.
	 * @return array
	 */
	private function cached( string $key, callable $compute ): array {
		$transient = 'aisentic_etn_stats_' . md5( $key . '|' . current_time( 'Y-m-d' ) );
		$value     = get_transient( $transient );

		if ( is_array( $value ) ) {
			return $value;
		}

		$value = $compute();
		set_transient( $transient, $value, self::CACHE_TTL );

		return $value;
	}
}