<?php
/**
 * Questions about the own data.
 *
 * @package Dealroot
 */

namespace Dealroot;

defined( 'ABSPATH' ) || exit;

/**
 * Answers a question in plain words, such as "How many enquiries came from Google in September?",
 * from the leads of the site. The assistant only turns the question into a query, given the names
 * of the channels, topics and forms; the numbers are counted here, so they are always right, and
 * no enquiry leaves the site for it.
 */
class Ai_Ask {

	/**
	 * Days looked at when the question names no period.
	 */
	const DEFAULT_DAYS = 90;

	/**
	 * Rows in an answer.
	 */
	const MAX_ROWS = 15;

	/**
	 * User meta with the conversation.
	 */
	const HISTORY = 'dealroot_ask_history';

	/**
	 * Questions and answers kept in the conversation.
	 */
	const HISTORY_SIZE = 30;

	/**
	 * Hooks.
	 */
	public function register() {
		add_action( 'wp_ajax_dealroot_ai_ask', array( $this, 'ajax' ) );
		add_action( 'wp_ajax_dealroot_ai_ask_clear', array( $this, 'ajax_clear' ) );
	}

	/**
	 * The conversation of the current user with the assistant, oldest first.
	 *
	 * @return array[] Each with q (question), a (answer) and t (time).
	 */
	public static function history() {
		$history = get_user_meta( get_current_user_id(), self::HISTORY, true );

		return is_array( $history ) ? array_values( $history ) : array();
	}

	/**
	 * Keeps a question and its answer in the conversation.
	 *
	 * @param string $question Question.
	 * @param array  $answer   Answer as sent to the browser.
	 */
	private static function keep( $question, array $answer ) {
		if ( ! get_current_user_id() ) {
			return;
		}

		unset( $answer['left'] );

		$history   = self::history();
		$history[] = array(
			'q' => $question,
			'a' => $answer,
			't' => time(),
		);

		update_user_meta( get_current_user_id(), self::HISTORY, array_slice( $history, -self::HISTORY_SIZE ) );
	}

	/**
	 * Starts a new conversation.
	 */
	public function ajax_clear() {
		check_ajax_referer( 'dealroot_ai_ask', 'nonce' );

		if ( ! current_user_can( Admin\Admin::capability() ) ) {
			wp_send_json_error( null, 403 );
		}

		delete_user_meta( get_current_user_id(), self::HISTORY );
		delete_user_meta( get_current_user_id(), 'dealroot_ask_last' );

		wp_send_json_success();
	}

	/**
	 * Answers the question of the overview.
	 */
	public function ajax() {
		check_ajax_referer( 'dealroot_ai_ask', 'nonce' );

		if ( ! current_user_can( Admin\Admin::capability() ) ) {
			wp_send_json_error( null, 403 );
		}

		$question = isset( $_POST['question'] ) ? mb_substr( sanitize_text_field( wp_unslash( $_POST['question'] ) ), 0, 300 ) : '';
		$answer   = '' === $question ? new \WP_Error( 'empty', __( 'Type a question first.', 'dealroot' ) ) : self::answer( $question );

		if ( is_wp_error( $answer ) ) {
			if ( '' !== $question ) {
				self::keep(
					$question,
					array(
						'text'  => $answer->get_error_message(),
						'error' => true,
					)
				);
			}

			wp_send_json_error(
				array(
					'message' => $answer->get_error_message(),
					'left'    => Ai::fast_left(),
				)
			);
		}

		self::keep( $question, $answer );

		wp_send_json_success( $answer + array( 'left' => Ai::fast_left() ) );
	}

	/**
	 * Questions to try, each with its query, so they are answered at once.
	 *
	 * @return array<string, array> Question => query.
	 */
	public static function examples() {
		$today = wp_date( 'Y-m-d' );

		return array(
			__( 'Which channel brought the most customers this year?', 'dealroot' ) => array(
				'metric' => 'customers',
				'group'  => 'channel',
				'from'   => wp_date( 'Y' ) . '-01-01',
				'to'     => $today,
			),
			__( 'How many enquiries did we get last month, per week?', 'dealroot' ) => array(
				'metric' => 'leads',
				'group'  => 'week',
				'from'   => wp_date( 'Y-m-01', strtotime( 'first day of last month' ) ),
				'to'     => wp_date( 'Y-m-t', strtotime( 'first day of last month' ) ),
			),
			__( 'How fast did we answer in the last 30 days?', 'dealroot' ) => array(
				'metric' => 'response_hours',
				'group'  => 'none',
				'from'   => wp_date( 'Y-m-d', time() - 29 * DAY_IN_SECONDS ),
				'to'     => $today,
			),
			__( 'Which devices did visitors use this month?', 'dealroot' ) => array(
				'metric' => 'visits',
				'group'  => 'device',
				'from'   => wp_date( 'Y-m-01' ),
				'to'     => $today,
			),
		);
	}

	/**
	 * The answer to a question: numbers counted from the leads, or a few words from what is known
	 * about the site when it is not a question of counting.
	 *
	 * @param string $question Question.
	 * @return array{title: string, period: string, columns: string[], rows: array, total: string, text: string}|\WP_Error
	 */
	public static function answer( $question ) {
		global $wpdb;

		$question = trim( (string) $question );
		$examples = self::examples();

		if ( isset( $examples[ $question ] ) ) {
			return self::run( $examples[ $question ] + array( 'title' => $question ) );
		}

		// A question that goes on from the one before, e.g. "and last month?", is read with it.
		$previous = self::previous();

		// The same question on the same day means the same query; only the numbers are counted again.
		$key   = 'dealroot_ask_' . md5( mb_strtolower( $question ) . '|' . Ai::user_language() . '|' . wp_date( 'Y-m-d' ) . '|' . $previous );
		$query = get_transient( $key );

		if ( ! is_array( $query ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$forms = $wpdb->get_col( $wpdb->prepare( "SELECT form_name FROM %i WHERE form_name <> '' AND status <> 'spam' GROUP BY form_name ORDER BY COUNT(*) DESC LIMIT 30", Installer::table() ) );
			$query = Ai::ask(
				'query',
				array(
					'question' => $question,
					'today'    => wp_date( 'Y-m-d' ),
					'lang'     => Ai::user_language(),
					'channels' => array_keys( Attribution::channel_labels() ),
					'topics'   => array_slice( Ai::known_topics(), 0, 40 ),
					'forms'    => array_map( 'strval', (array) $forms ),
					'facts'    => self::facts(),
					'previous' => $previous,
				),
				60
			);

			if ( is_wp_error( $query ) ) {
				return $query;
			}

			set_transient( $key, $query, DAY_IN_SECONDS );
		}

		// Told to change something, e.g. "Move Meier to contacted": done, and said what was done.
		if ( ! empty( $query['command'] ) ) {
			return self::act( $question );
		}

		$report = self::report( $question, $query );

		if ( $report ) {
			return $report;
		}

		if ( ! empty( $query['understood'] ) ) {
			self::remember( $question, $query );

			// "Export", "CSV", "download": the leads to download instead of the link to the list.
			$query['export'] = (bool) preg_match( '/(csv|excel|export|esport|download|herunterlad|télécharg|descarg|scaric|pobierz|выгруз|экспорт|скача|вивантаж|експорт|завантаж)/iu', $question );

			return self::run( $query ) + array( 'text' => '' );
		}

		$text = sanitize_text_field( (string) ( $query['answer'] ?? '' ) );

		if ( '' === $text ) {
			return new \WP_Error( 'not_understood', __( 'This can only be answered about your enquiries, customers and revenue. Try for example: “Which channel brought the most customers this year?”', 'dealroot' ) );
		}

		return array(
			'title'   => '',
			'period'  => '',
			'columns' => array( '', '' ),
			'rows'    => array(),
			'total'   => '',
			'text'    => $text,
		);
	}

	/**
	 * The question asked just before by this user and how it was read, for a question that goes
	 * on from it.
	 *
	 * @return string "" when there was none in the last half hour.
	 */
	private static function previous() {
		$last = get_user_meta( get_current_user_id(), 'dealroot_ask_last', true );

		if ( ! is_array( $last ) || (int) ( $last['time'] ?? 0 ) < time() - 30 * MINUTE_IN_SECONDS ) {
			return '';
		}

		return mb_substr( (string) $last['question'] . ' → ' . (string) $last['query'], 0, 500 );
	}

	/**
	 * Keeps the question and how it was read, for the next one.
	 *
	 * @param string $question Question.
	 * @param array  $query    Query.
	 */
	private static function remember( $question, array $query ) {
		if ( ! get_current_user_id() ) {
			return;
		}

		$kept = array_filter( array_intersect_key( $query, array_flip( array( 'metric', 'group', 'from', 'to', 'channel', 'topic', 'form', 'status', 'device', 'person', 'city', 'campaign', 'source' ) ) ), 'strlen' );

		update_user_meta(
			get_current_user_id(),
			'dealroot_ask_last',
			array(
				'question' => mb_substr( $question, 0, 300 ),
				'query'    => (string) wp_json_encode( $kept ),
				'time'     => time(),
			)
		);
	}

	/**
	 * Carries out an order given in the box, as the assistant in Telegram does.
	 *
	 * @param string $order Order.
	 * @return array Answer.
	 */
	private static function act( $order ) {
		$done = Telegram_Assistant::on_screen( $order );
		$lead = $done['lead'] ? Leads::get( $done['lead'] ) : null;

		return array(
			'title'   => '',
			'period'  => '',
			'columns' => array( '', '' ),
			'rows'    => array(),
			'total'   => '',
			'text'    => '' !== $done['text'] ? $done['text'] : __( 'I did not see what to change. Say who and what, e.g. “Move Meier to contacted”.', 'dealroot' ),
			'link'    => $lead ? array(
				'url'   => admin_url( 'admin.php?page=dealroot&lead=' . (int) $lead->id ),
				'label' => __( 'Open the lead', 'dealroot' ),
			) : null,
			'done'    => '' !== $done['action'],
		);
	}

	/**
	 * A report asked for, e.g. "Report for September" or "PDF for last month": the link to the
	 * report of that period.
	 *
	 * @param string $question Question.
	 * @param array  $query    What the assistant read from it, with from and to.
	 * @return array|null Answer with the link, null when no report was asked for.
	 */
	private static function report( $question, array $query ) {
		if ( ! preg_match( '/(report|bericht|auswertung|übersicht|отч[её]т|звіт|rapport|informe|rapporto|raport|sprawozdanie|verslag|pdf)/iu', $question ) ) {
			return null;
		}

		$zone = wp_timezone();
		$from = self::day( (string) ( $query['from'] ?? '' ), $zone );
		$to   = self::day( (string) ( $query['to'] ?? '' ), $zone );
		$to   = $to ? $to : new \DateTimeImmutable( 'today', $zone );
		$from = $from ? $from : $to->modify( '-' . self::DEFAULT_DAYS . ' days' );

		if ( $from > $to ) {
			list( $from, $to ) = array( $to, $from );
		}

		$format = get_option( 'date_format' );
		$period = wp_date( $format, $from->getTimestamp() ) . ' – ' . wp_date( $format, $to->getTimestamp() );

		return array(
			'title'   => __( 'Your report', 'dealroot' ),
			'period'  => $period,
			'columns' => array( '', '' ),
			'rows'    => array(),
			'total'   => '',
			/* translators: %s: period */
			'text'    => sprintf( __( 'Enquiries, channels, devices, costs and revenue for %s, ready to save as a PDF.', 'dealroot' ), $period ),
			'link'    => array(
				'url'   => Admin\Report_Page::url( '', $from->format( 'Y-m-d' ), $to->format( 'Y-m-d' ) ),
				'label' => __( 'Open the report', 'dealroot' ),
			),
		);
	}

	/**
	 * What is known about the site, for questions that are not about counting. Only figures and
	 * names of the site itself, no enquiry and no person.
	 *
	 * @return string[]
	 */
	public static function facts() {
		global $wpdb;

		$table = Installer::table();
		$month = gmdate( 'Y-m-d H:i:s', (int) strtotime( wp_date( 'Y-m-01 00:00:00' ) . ' ' . wp_timezone_string() ) );
		$last  = gmdate( 'Y-m-d H:i:s', (int) strtotime( wp_date( 'Y-m-01 00:00:00', strtotime( 'first day of last month' ) ) . ' ' . wp_timezone_string() ) );
		$since = gmdate( 'Y-m-d H:i:s', time() - 30 * DAY_IN_SECONDS );

		$midnight = gmdate( 'Y-m-d H:i:s', ( new \DateTimeImmutable( 'today', wp_timezone() ) )->getTimestamp() );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$this_month = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE status <> 'spam' AND created_at >= %s", $table, $month ) );
		$last_month = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE status <> 'spam' AND created_at >= %s AND created_at < %s", $table, $last, $month ) );
		$won        = $wpdb->get_row( $wpdb->prepare( "SELECT COUNT(*) AS deals, COALESCE(SUM(value), 0) AS revenue FROM %i WHERE status = 'won' AND won_at >= %s", $table, $month ) );
		$today      = $wpdb->get_row( $wpdb->prepare( "SELECT SUM(CASE WHEN created_at >= %s THEN 1 ELSE 0 END) AS leads, SUM(CASE WHEN status = 'won' AND won_at >= %s THEN 1 ELSE 0 END) AS deals, SUM(CASE WHEN status = 'won' AND won_at >= %s THEN COALESCE(value, 0) ELSE 0 END) AS revenue FROM %i WHERE status <> 'spam' AND ( created_at >= %s OR won_at >= %s )", $midnight, $midnight, $midnight, $table, $midnight, $midnight ) );
		$waiting    = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE status = 'new' AND created_at >= %s", $table, $since ) );
		$channel    = $wpdb->get_row( $wpdb->prepare( "SELECT channel, COUNT(*) AS leads FROM %i WHERE status <> 'spam' AND created_at >= %s GROUP BY channel ORDER BY leads DESC LIMIT 1", $table, $since ) );
		$total      = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE status <> 'spam'", $table ) );
		// phpcs:enable

		$facts = array(
			'Name of the site: ' . wp_specialchars_decode( get_bloginfo( 'name' ), ENT_QUOTES ),
			'Address of the site: ' . home_url( '/' ),
			'Today: ' . wp_date( 'l, Y-m-d' ),
			'Enquiries this month so far: ' . $this_month . '; last month: ' . $last_month . '; all time: ' . $total,
			'Won this month: ' . (int) ( $won->deals ?? 0 ) . ' deals worth ' . Admin\Format::money( (float) ( $won->revenue ?? 0 ), (string) Settings::get( 'currency' ) ),
			'Today so far: ' . (int) ( $today->leads ?? 0 ) . ' enquiries, ' . (int) ( $today->deals ?? 0 ) . ' deals won worth ' . Admin\Format::money( (float) ( $today->revenue ?? 0 ), (string) Settings::get( 'currency' ) ),
			'New enquiries of the last 30 days still waiting for an answer: ' . $waiting,
		);

		if ( '' !== (string) get_bloginfo( 'description' ) ) {
			$facts[] = 'Tagline of the site: ' . wp_specialchars_decode( get_bloginfo( 'description' ), ENT_QUOTES );
		}

		if ( $channel ) {
			$facts[] = 'Most enquiries of the last 30 days came from: ' . Attribution::channel_label( (string) $channel->channel ) . ' (' . (int) $channel->leads . ')';
		}

		$topics = array_slice( Ai::known_topics(), 0, 5 );
		if ( $topics ) {
			$facts[] = 'What customers ask for most: ' . implode( ', ', $topics );
		}

		$facts = array_merge( $facts, self::more_facts() );

		$facts[] = 'Telegram notifications: ' . ( Notifier::telegram_ready() ? 'on' : 'off' );
		$facts[] = 'Dealroot Pro: ' . ( defined( 'DEALROOT_PRO_VERSION' ) ? 'installed' : 'not installed' );

		/**
		 * Filters what the assistant knows about the site when answering a question.
		 *
		 * @param string[] $facts Facts, in English.
		 */
		return array_values( array_slice( array_map( 'strval', (array) apply_filters( 'dealroot_ask_facts', $facts ) ), 0, 40 ) );
	}

	/**
	 * Figures of the last weeks and months, so that a question the assistant cannot count still
	 * gets an answer from what is known.
	 *
	 * @return string[]
	 */
	private static function more_facts() {
		$money = static fn( array $amounts ) => Admin\Format::money_list( $amounts );
		$facts = array();

		foreach ( array(
			'this_month' => 'This month',
			'last_month' => 'Last month',
			'90d'        => 'Last 90 days',
		) as $period => $name ) {
			$rows   = Reports::breakdown( $period, 'channel', 'first' );
			$totals = Reports::totals( $rows );
			$line   = $name . ': ' . $totals['leads'] . ' enquiries, ' . $totals['won'] . ' won (' . Admin\Format::percent( $totals['won'], $totals['leads'] ) . '), revenue ' . $money( $totals['revenue'] ) . ', ' . $totals['visits'] . ' visits';

			if ( array_sum( $totals['cost'] ) > 0 ) {
				$line .= ', ad costs ' . $money( $totals['cost'] ) . ', ' . Admin\Format::per( $totals['cost'], $totals['leads'] ) . ' per enquiry, return on ad spend ' . Admin\Format::roas( $totals['revenue'], $totals['cost'] );
			}

			$facts[] = $line;

			if ( '90d' === $period ) {
				uasort( $rows, static fn( $a, $b ) => $b['leads'] <=> $a['leads'] );

				$best = array();
				foreach ( array_slice( $rows, 0, 4, true ) as $key => $row ) {
					$best[] = Reports::label( 'channel', (string) $key ) . ' ' . $row['leads'] . ' enquiries, ' . $row['won'] . ' won, ' . $money( $row['revenue'] );
				}

				if ( $best ) {
					$facts[] = 'Channels of the last 90 days: ' . implode( '; ', $best );
				}

				if ( $totals['won'] ) {
					$facts[] = 'Average deal of the last 90 days: ' . Admin\Format::per( $totals['revenue'], $totals['won'] );
				}
			}
		}

		foreach ( array(
			'campaign' => 'Campaigns',
			'city'     => 'Cities',
			'device'   => 'Devices',
		) as $dimension => $name ) {
			$rows = array_filter( Reports::breakdown( '90d', $dimension, 'first' ), static fn( $row ) => $row['leads'] > 0 );
			unset( $rows[''] );
			uasort( $rows, static fn( $a, $b ) => array( $b['won'], $b['leads'] ) <=> array( $a['won'], $a['leads'] ) );

			$list = array();
			foreach ( array_slice( $rows, 0, 3, true ) as $key => $row ) {
				$list[] = Reports::label( $dimension, (string) $key ) . ' ' . $row['leads'] . ' enquiries, ' . $row['won'] . ' won';
			}

			if ( $list ) {
				$facts[] = $name . ' of the last 90 days: ' . implode( '; ', $list );
			}
		}

		$response = Reports::response_time( '30d' );
		if ( null !== $response ) {
			$facts[] = 'Usual time to the first answer in the last 30 days: ' . Admin\Format::duration( (int) $response );
		}

		$lost = Lost_Reasons::stats( 90 );
		if ( $lost['total'] ) {
			$labels  = Lost_Reasons::labels();
			$reasons = array();
			foreach ( $lost['total'] as $reason => $count ) {
				$reasons[] = ( $labels[ $reason ] ?? $reason ) . ' ' . $count;
			}
			$facts[] = 'Why deals were lost in the last 90 days: ' . implode( ', ', $reasons );
		}

		$coach = Ai_Coach::current( Ai::user_language() );
		if ( $coach && '' !== $coach['advice'] ) {
			$facts[] = 'Advice of the week: ' . $coach['advice'] . ( '' !== $coach['focus'] ? ' Worth doing next: ' . $coach['focus'] : '' );
		}

		return $facts;
	}

	/**
	 * Metrics with their names.
	 *
	 * @return array<string, string>
	 */
	private static function metrics() {
		return array(
			'leads'          => __( 'Leads', 'dealroot' ),
			'customers'      => __( 'Customers', 'dealroot' ),
			'revenue'        => __( 'Revenue', 'dealroot' ),
			'conversion'     => __( 'Became customers', 'dealroot' ),
			'response_hours' => __( 'Time to first answer', 'dealroot' ),
			'visits'         => __( 'Visits', 'dealroot' ),
			'lost'           => __( 'Lost', 'dealroot' ),
			'waiting'        => __( 'Waiting for an answer', 'dealroot' ),
			'deal_value'     => __( 'Average deal', 'dealroot' ),
			'clicks'         => __( 'Contact clicks', 'dealroot' ),
			'cost'           => __( 'Ad costs', 'dealroot' ),
			'cost_per_lead'  => __( 'Per enquiry', 'dealroot' ),
			'roas'           => __( 'Return on ad spend', 'dealroot' ),
		);
	}

	/**
	 * Counts what a query asks for.
	 *
	 * @param array $query Query from the assistant.
	 * @return array
	 */
	public static function run( array $query ) {
		$metric = array_key_exists( $query['metric'] ?? '', self::metrics() ) ? $query['metric'] : 'leads';
		$group  = array_key_exists( $query['group'] ?? '', self::groups() ) || 'none' === ( $query['group'] ?? '' ) ? $query['group'] : 'none';
		$zone   = wp_timezone();
		$from   = self::day( (string) ( $query['from'] ?? '' ), $zone );
		$to     = self::day( (string) ( $query['to'] ?? '' ), $zone );

		if ( ! $from ) {
			$from = ( new \DateTimeImmutable( 'today', $zone ) )->modify( '-' . self::DEFAULT_DAYS . ' days' );
		}

		$to = $to ? $to->modify( '+1 day' ) : new \DateTimeImmutable( 'tomorrow', $zone );

		$filters = array(
			'channel'  => (string) ( $query['channel'] ?? '' ),
			'form'     => (string) ( $query['form'] ?? '' ),
			'status'   => in_array( $query['status'] ?? '', Leads::STATUSES, true ) ? (string) $query['status'] : '',
			'topic'    => (string) ( $query['topic'] ?? '' ),
			'device'   => in_array( $query['device'] ?? '', array( 'mobile', 'tablet', 'desktop' ), true ) ? (string) $query['device'] : '',
			'person'   => mb_substr( trim( (string) ( $query['person'] ?? '' ) ), 0, 100 ),
			'city'     => mb_substr( trim( (string) ( $query['city'] ?? '' ) ), 0, 100 ),
			'campaign' => mb_substr( trim( (string) ( $query['campaign'] ?? '' ) ), 0, 100 ),
			'source'   => mb_substr( trim( (string) ( $query['source'] ?? '' ) ), 0, 100 ),
		);

		if ( 'lost_reason' === $group || 'lost' === $metric ) {
			$filters['status'] = 'lost';
		}

		$labels = self::metrics();
		$result = in_array( $metric, array( 'cost', 'cost_per_lead', 'roas' ), true )
			? self::costs( $metric, $group, $from, $to, $filters['channel'] )
			: self::count( $metric, $group, $from, $to, $filters, $zone );
		$last   = $to->modify( '-1 day' );

		// The same span right before, when asked to compare.
		if ( ! empty( $query['compare'] ) ) {
			$days   = (int) round( ( $to->getTimestamp() - $from->getTimestamp() ) / DAY_IN_SECONDS );
			$before = self::run(
				array_merge(
					$query,
					array(
						'group'   => 'none',
						'compare' => false,
						'from'    => $from->modify( '-' . $days . ' days' )->format( 'Y-m-d' ),
						'to'      => $from->modify( '-1 day' )->format( 'Y-m-d' ),
					)
				)
			);

			/* translators: 1: number of the period, 2: number of days, 3: number of the days before */
			$result['total'] = sprintf( __( '%1$s, the %2$s days before: %3$s', 'dealroot' ), $result['total'], number_format_i18n( $days ), $before['total'] );
		}

		// The leads behind the figure, in the list, or to download.
		$link = null;
		$days = $from->format( 'Y-m-d' ) . '_' . $last->format( 'Y-m-d' );

		if ( ! in_array( $metric, array( 'visits', 'clicks', 'cost', 'cost_per_lead', 'roas' ), true ) ) {
			$statuses = array(
				'customers'  => 'won',
				'revenue'    => 'won',
				'deal_value' => 'won',
				'lost'       => 'lost',
				'waiting'    => 'new',
			);
			$args     = array_filter(
				array(
					'page'    => Admin\Admin::LEADS_PAGE,
					'period'  => $days,
					'channel' => $filters['channel'],
					'status'  => $statuses[ $metric ] ?? $filters['status'],
					's'       => $filters['person'],
				),
				'strlen'
			);

			$link = ! empty( $query['export'] )
				? array(
					'url'   => wp_nonce_url( add_query_arg( array_merge( $args, array( 'action' => 'dealroot_export' ) ), admin_url( 'admin-post.php' ) ), 'dealroot_export' ),
					'label' => __( 'Download these leads as CSV', 'dealroot' ),
				)
				: array(
					'url'   => add_query_arg( $args, admin_url( 'admin.php' ) ),
					'label' => __( 'Show these leads', 'dealroot' ),
				);
		}

		// Nothing counted: said in words, with the day Dealroot started when the period is older.
		if ( empty( $result['rows'] ) && '' === (string) ( $result['text'] ?? '' ) && ! preg_match( '/[1-9]/', (string) $result['total'] ) ) {
			$since          = Reports::tracking_since();
			$result['text'] = '' !== $since && $from->format( 'Y-m-d' ) < $since
				/* translators: %s: date */
				? sprintf( __( 'Nothing in this time. Dealroot has been recording since %s, so earlier days have no data.', 'dealroot' ), wp_date( get_option( 'date_format' ), (int) strtotime( $since . ' 12:00:00' ), $zone ) )
				: __( 'Nothing in this time.', 'dealroot' );
			$result['total'] = '';
		}

		return array(
			'title'   => '' !== (string) ( $query['title'] ?? '' ) ? sanitize_text_field( (string) $query['title'] ) : $labels[ $metric ],
			/* translators: 1: first day, 2: last day */
			'period'  => sprintf( __( '%1$s to %2$s', 'dealroot' ), wp_date( get_option( 'date_format' ), $from->getTimestamp(), $zone ), wp_date( get_option( 'date_format' ), $last->getTimestamp(), $zone ) ),
			'columns' => array( 'none' === $result['group'] ? '' : self::group_label( $result['group'] ), $labels[ $metric ] ),
			'rows'    => 'none' === $result['group'] ? array() : $result['rows'],
			'bars'    => 'none' === $result['group'] ? array() : ( $result['bars'] ?? array() ),
			'links'   => 'none' === $result['group'] ? array() : ( $result['links'] ?? array() ),
			'total'   => $result['total'],
			'text'    => $result['text'] ?? '',
			'link'    => $link,
		);
	}

	/**
	 * Counts leads, visits or contact clicks.
	 *
	 * @param string             $metric  Metric.
	 * @param string             $group   Grouping.
	 * @param \DateTimeImmutable $from    First day.
	 * @param \DateTimeImmutable $to      Day after the last.
	 * @param array              $filters Filters.
	 * @param \DateTimeZone      $zone    Time zone of the site.
	 * @return array{group: string, rows: array, total: string}
	 */
	private static function count( $metric, $group, $from, $to, array $filters, $zone ) {
		if ( 'visits' === $metric ) {
			// Visits know no topic, form, status or person.
			$group = in_array( $group, array( 'channel', 'source', 'campaign', 'city', 'device', 'month', 'week', 'day', 'weekday' ), true ) ? $group : 'none';
			$rows  = self::visits( $from, $to, $filters['channel'], $filters['device'], $zone );
		} elseif ( 'clicks' === $metric ) {
			$group = in_array( $group, array( 'channel', 'source', 'campaign', 'month', 'week', 'day', 'weekday', 'hour' ), true ) ? $group : 'none';
			$rows  = self::clicks( $from, $to, $filters['channel'] );
		} else {
			// Deals and revenue count on the day they were won, e.g. "deals today".
			$rows = self::leads( $from, $to, $filters, in_array( $metric, array( 'customers', 'revenue', 'deal_value' ), true ) );
		}

		$groups = array();

		foreach ( (array) $rows as $row ) {
			foreach ( self::keys( $row, $group, $zone, $metric ) as $key => $label ) {
				if ( ! isset( $groups[ $key ] ) ) {
					$groups[ $key ] = self::item( $label );
				}

				self::add( $groups[ $key ], $row );
			}
		}

		if ( in_array( $group, array( 'month', 'week', 'day', 'weekday', 'hour' ), true ) ) {
			ksort( $groups );
		} else {
			uasort(
				$groups,
				static function ( $a, $b ) use ( $metric ) {
					return self::sort_value( $b, $metric ) <=> self::sort_value( $a, $metric );
				}
			);
		}

		$total = self::item( '' );

		foreach ( (array) $rows as $row ) {
			self::add( $total, $row );
		}

		$lines = array();
		$bars  = array();
		$links = array();

		foreach ( array_slice( $groups, 0, in_array( $group, array( 'month', 'week', 'day', 'hour' ), true ) ? 60 : self::MAX_ROWS, true ) as $key => $item ) {
			$lines[] = array( $item['label'], self::show( $item, $metric ) );
			$bars[]  = round( abs( self::sort_value( $item, $metric ) ), 2 );
			$links[] = 0 === strpos( (string) $key, 'l:' ) ? admin_url( 'admin.php?page=dealroot&lead=' . (int) substr( (string) $key, 2 ) ) : '';
		}

		return array(
			'group' => $group,
			'rows'  => $lines,
			'bars'  => $bars,
			'links' => $links,
			'total' => self::show( $total, $metric ),
		);
	}

	/**
	 * Ad costs, the cost of an enquiry or the return on ad spend, from the report of the period.
	 *
	 * @param string             $metric  Metric.
	 * @param string             $group   Grouping.
	 * @param \DateTimeImmutable $from    First day.
	 * @param \DateTimeImmutable $to      Day after the last.
	 * @param string             $channel Channel or "".
	 * @return array{group: string, rows: array, total: string, text: string}
	 */
	private static function costs( $metric, $group, $from, $to, $channel ) {
		$group  = in_array( $group, array( 'channel', 'source', 'campaign' ), true ) ? $group : 'none';
		$period = $from->format( 'Y-m-d' ) . '_' . $to->modify( '-1 day' )->format( 'Y-m-d' );
		$rows   = Reports::breakdown( $period, 'none' === $group ? 'channel' : $group, 'first' );

		if ( '' !== $channel && 'channel' === ( 'none' === $group ? 'channel' : $group ) ) {
			$rows = array_intersect_key( $rows, array( $channel => true ) );
		}

		$value = static function ( array $row ) use ( $metric ) {
			if ( 'cost' === $metric ) {
				return Admin\Format::money_list( $row['cost'] );
			}

			return 'roas' === $metric ? Admin\Format::roas( $row['revenue'], $row['cost'] ) : Admin\Format::per( $row['cost'], $row['leads'] );
		};

		uasort(
			$rows,
			static function ( $a, $b ) {
				return array_sum( $b['cost'] ) <=> array_sum( $a['cost'] );
			}
		);

		$lines = array();
		foreach ( array_slice( array_filter( $rows, static fn( $row ) => array_sum( $row['cost'] ) > 0 ), 0, self::MAX_ROWS, true ) as $key => $row ) {
			$lines[] = array( Reports::label( $group, (string) $key ), $value( $row ) );
		}

		$total = Reports::totals( $rows );

		return array(
			'group' => $group,
			'rows'  => $lines,
			'total' => array_sum( $total['cost'] ) > 0 ? $value( $total ) : '—',
			'text'  => array_sum( $total['cost'] ) > 0 ? '' : __( 'No ad costs are known for this period. With Dealroot Pro, connect Google Ads, Microsoft Advertising or Meta to see them here.', 'dealroot' ),
		);
	}

	/**
	 * An empty group.
	 *
	 * @param string $label Label.
	 * @return array
	 */
	private static function item( $label ) {
		return array(
			'label'   => $label,
			'leads'   => 0,
			'won'     => 0,
			'lost'    => 0,
			'waiting' => 0,
			'revenue' => array(),
			'hours'   => array(),
		);
	}

	/**
	 * The leads of a query. Each filter is left out when it is empty.
	 *
	 * @param \DateTimeImmutable $from    First day.
	 * @param \DateTimeImmutable $to      Day after the last.
	 * @param array              $filters channel, device, form, status, topic, person, city, campaign, source.
	 * @param bool               $by_won  Whether the days are those the deals were won.
	 * @return object[]
	 */
	private static function leads( $from, $to, array $filters, $by_won = false ) {
		global $wpdb;

		$like = static function ( $value ) use ( $wpdb ) {
			return '%' . $wpdb->esc_like( (string) $value ) . '%';
		};

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$rows = (array) $wpdb->get_results(
			$wpdb->prepare(
				"SELECT id, name, email, created_at, contacted_at, won_at, status, value, currency, channel, source, campaign, ai_topics, ai_summary, form_name, city, country, device, browser, landing_page, lost_reason FROM %i
				WHERE status <> 'spam' AND ( CASE WHEN %d = 1 THEN status = 'won' AND won_at >= %s AND won_at < %s ELSE created_at >= %s AND created_at < %s END )
					AND ( %s = '' OR channel = %s ) AND ( %s = '' OR device = %s ) AND ( %s = '' OR form_name = %s ) AND ( %s = '' OR status = %s )
					AND ( %s = '' OR CONCAT('|', ai_topics, '|') LIKE %s )
					AND ( %s = '' OR name LIKE %s OR email LIKE %s )
					AND ( %s = '' OR city LIKE %s ) AND ( %s = '' OR campaign LIKE %s OR last_campaign LIKE %s ) AND ( %s = '' OR source LIKE %s OR last_source LIKE %s )
				ORDER BY id DESC LIMIT 20000",
				Installer::table(),
				$by_won ? 1 : 0,
				self::utc( $from ),
				self::utc( $to ),
				self::utc( $from ),
				self::utc( $to ),
				$filters['channel'],
				$filters['channel'],
				$filters['device'],
				$filters['device'],
				$filters['form'],
				$filters['form'],
				$filters['status'],
				$filters['status'],
				$filters['topic'],
				'%|' . $wpdb->esc_like( $filters['topic'] ) . '|%',
				$filters['person'],
				$like( $filters['person'] ),
				$like( $filters['person'] ),
				$filters['city'],
				$like( $filters['city'] ),
				$filters['campaign'],
				$like( $filters['campaign'] ),
				$like( $filters['campaign'] ),
				$filters['source'],
				$like( $filters['source'] ),
				$like( $filters['source'] )
			)
		);

		// Grouped by the day they were won.
		if ( $by_won ) {
			foreach ( $rows as $row ) {
				$row->created_at = $row->won_at;
			}
		}

		return $rows;
	}

	/**
	 * The visits of a query, one row per day and kind of visitor, each dated at noon of its day.
	 *
	 * @param \DateTimeImmutable $from    First day.
	 * @param \DateTimeImmutable $to      Day after the last.
	 * @param string             $channel Channel or "".
	 * @param string             $device  Kind of device or "".
	 * @param \DateTimeZone      $zone    Time zone of the site.
	 * @return object[]
	 */
	private static function visits( $from, $to, $channel, $device, $zone ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$rows = (array) $wpdb->get_results(
			$wpdb->prepare(
				"SELECT day, channel, source, campaign, device, city, SUM(visits) AS visits FROM %i
				WHERE day >= %s AND day < %s AND ( %s = '' OR channel = %s ) AND ( %s = '' OR device = %s )
				GROUP BY day, channel, source, campaign, device, city LIMIT 50000",
				Installer::visits_table(),
				$from->format( 'Y-m-d' ),
				$to->format( 'Y-m-d' ),
				$channel,
				$channel,
				$device,
				$device
			)
		);

		foreach ( $rows as $row ) {
			$row->created_at = gmdate( 'Y-m-d H:i:s', ( new \DateTimeImmutable( $row->day . ' 12:00', $zone ) )->getTimestamp() );
		}

		return $rows;
	}

	/**
	 * Clicks on contact links in a period, each counted once.
	 *
	 * @param \DateTimeImmutable $from    First day.
	 * @param \DateTimeImmutable $to      Day after the last.
	 * @param string             $channel Channel or "".
	 * @return object[]
	 */
	private static function clicks( $from, $to, $channel ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$rows = (array) $wpdb->get_results(
			$wpdb->prepare(
				"SELECT created_at, channel, source, campaign, 1 AS visits FROM %i WHERE created_at >= %s AND created_at < %s AND ( %s = '' OR channel = %s ) LIMIT 50000",
				Installer::events_table(),
				self::utc( $from ),
				self::utc( $to ),
				$channel,
				$channel
			)
		);

		return $rows;
	}

	/**
	 * Adds one lead to the numbers of a group.
	 *
	 * @param array  $item Group.
	 * @param object $row  Lead.
	 */
	private static function add( array &$item, $row ) {
		// A row of visits or clicks counts its number.
		if ( isset( $row->visits ) ) {
			$item['leads'] += (int) $row->visits;
			return;
		}

		++$item['leads'];

		if ( 'won' === $row->status ) {
			++$item['won'];
			$currency = '' !== (string) $row->currency ? (string) $row->currency : (string) Settings::get( 'currency' );

			$item['revenue'][ $currency ] = ( $item['revenue'][ $currency ] ?? 0 ) + (float) $row->value;
		}

		if ( 'lost' === $row->status ) {
			++$item['lost'];
		}

		if ( 'new' === $row->status && empty( $row->contacted_at ) ) {
			++$item['waiting'];
		}

		if ( ! empty( $row->contacted_at ) ) {
			$item['hours'][] = max( 0, strtotime( $row->contacted_at . ' UTC' ) - strtotime( $row->created_at . ' UTC' ) );
		}
	}

	/**
	 * Groups a lead belongs to: key => label.
	 *
	 * @param object        $row    Lead.
	 * @param string        $group  Grouping.
	 * @param \DateTimeZone $zone   Time zone of the site.
	 * @param string        $metric Metric.
	 * @return array<string, string>
	 */
	private static function keys( $row, $group, $zone, $metric = 'leads' ) {
		$time  = (int) strtotime( $row->created_at . ' UTC' );
		$empty = __( '(none)', 'dealroot' );

		switch ( $group ) {
			case 'channel':
				return array( 'c:' . $row->channel => '' !== (string) $row->channel ? Attribution::channel_label( $row->channel ) : __( 'Not tracked', 'dealroot' ) );
			case 'source':
				return array( 'k:' . $row->source => '' !== (string) $row->source ? Sources::label( (string) $row->source ) : $empty );
			case 'campaign':
			case 'city':
				return array( 'k:' . $row->$group => '' !== (string) $row->$group ? (string) $row->$group : $empty );
			case 'country':
				return array( 'k:' . $row->country => '' !== (string) $row->country ? Geo::country_label( (string) $row->country ) : $empty );
			case 'device':
				return array( 'd:' . $row->device => '' !== (string) $row->device ? Devices::type_label( $row->device ) : __( 'Not tracked', 'dealroot' ) );
			case 'browser':
				return array( 'b:' . $row->browser => '' !== (string) $row->browser ? Devices::browser_label( (string) $row->browser ) : __( 'Not tracked', 'dealroot' ) );
			case 'landing':
				$path = '' !== (string) $row->landing_page ? (string) wp_parse_url( (string) $row->landing_page, PHP_URL_PATH ) : '';
				return array( 'p:' . $path => '' !== $path ? $path : $empty );
			case 'lost_reason':
				$reasons = Lost_Reasons::labels();
				return 'lost' === $row->status ? array( 'r:' . $row->lost_reason => $reasons[ $row->lost_reason ] ?? __( 'No reason given', 'dealroot' ) ) : array();
			case 'form':
				return array( 'k:' . $row->form_name => '' !== (string) $row->form_name ? (string) $row->form_name : $empty );
			case 'status':
				$labels = Leads::status_labels();
				return array( 's:' . $row->status => $labels[ $row->status ] ?? $row->status );
			case 'topic':
				$keys = array();
				foreach ( array_filter( explode( '|', (string) $row->ai_topics ), 'strlen' ) as $topic ) {
					$keys[ 't:' . $topic ] = $topic;
				}
				return $keys ? $keys : array( 't:' => $empty );
			case 'month':
				return array( wp_date( 'Y-m', $time, $zone ) => wp_date( 'F Y', $time, $zone ) );
			case 'week':
				/* translators: %s: number of the week */
				return array( wp_date( 'o-W', $time, $zone ) => sprintf( __( 'Week %s', 'dealroot' ), wp_date( 'W, o', $time, $zone ) ) );
			case 'day':
				return array( wp_date( 'Y-m-d', $time, $zone ) => wp_date( get_option( 'date_format' ), $time, $zone ) );
			case 'weekday':
				return array( wp_date( 'N', $time, $zone ) => wp_date( 'l', $time, $zone ) );
			case 'hour':
				$hour = (int) wp_date( 'G', $time, $zone );
				return array( sprintf( '%02d', $hour ) => sprintf( '%02d:00–%02d:00', $hour, ( $hour + 1 ) % 24 ) );
			case 'lead':
				$name = '' !== trim( (string) $row->name ) ? (string) $row->name : ( '' !== (string) $row->email ? (string) $row->email : '#' . (int) $row->id );

				// What they wanted, when the question is about the enquiries themselves.
				if ( in_array( $metric, array( 'leads', 'waiting', 'lost' ), true ) && '' !== (string) ( $row->ai_summary ?? '' ) ) {
					$name .= ' – ' . wp_html_excerpt( (string) $row->ai_summary, 90, '…' );
				}

				return array( 'l:' . (int) $row->id => $name );
			default:
				return array( 'all' => '' );
		}
	}

	/**
	 * The number of a group the answer is about, for sorting.
	 *
	 * @param array  $item   Group.
	 * @param string $metric Metric.
	 * @return float
	 */
	private static function sort_value( array $item, $metric ) {
		switch ( $metric ) {
			case 'customers':
				return $item['won'];
			case 'revenue':
				return array_sum( $item['revenue'] );
			case 'deal_value':
				return $item['won'] ? array_sum( $item['revenue'] ) / $item['won'] : 0;
			case 'conversion':
				return $item['leads'] ? $item['won'] / $item['leads'] : 0;
			case 'response_hours':
				return -self::median( $item['hours'] );
			case 'lost':
				return $item['lost'];
			case 'waiting':
				return $item['waiting'];
			default:
				return $item['leads'];
		}
	}

	/**
	 * The number of a group the answer is about, as shown.
	 *
	 * @param array  $item   Group.
	 * @param string $metric Metric.
	 * @return string
	 */
	private static function show( array $item, $metric ) {
		switch ( $metric ) {
			case 'customers':
				return number_format_i18n( $item['won'] );
			case 'revenue':
				return Admin\Format::money_list( $item['revenue'] );
			case 'deal_value':
				return Admin\Format::per( $item['revenue'], $item['won'] );
			case 'conversion':
				return $item['leads'] ? number_format_i18n( $item['won'] / $item['leads'] * 100, 1 ) . ' %' : '—';
			case 'response_hours':
				return $item['hours'] ? Admin\Format::duration( (int) self::median( $item['hours'] ) ) : '—';
			case 'lost':
				return number_format_i18n( $item['lost'] );
			case 'waiting':
				return number_format_i18n( $item['waiting'] );
			default:
				return number_format_i18n( $item['leads'] );
		}
	}

	/**
	 * Middle value.
	 *
	 * @param int[] $values Values.
	 * @return float
	 */
	private static function median( array $values ) {
		if ( ! $values ) {
			return 0.0;
		}

		sort( $values );
		$middle = intdiv( count( $values ), 2 );

		return count( $values ) % 2 ? (float) $values[ $middle ] : ( $values[ $middle - 1 ] + $values[ $middle ] ) / 2;
	}

	/**
	 * Groupings with their names.
	 *
	 * @return array<string, string>
	 */
	private static function groups() {
		return array(
			'channel'     => __( 'Channel', 'dealroot' ),
			'source'      => __( 'Source', 'dealroot' ),
			'campaign'    => __( 'Campaign', 'dealroot' ),
			'topic'       => __( 'Topic', 'dealroot' ),
			'form'        => __( 'Form', 'dealroot' ),
			'city'        => __( 'City', 'dealroot' ),
			'country'     => __( 'Country', 'dealroot' ),
			'device'      => __( 'Device', 'dealroot' ),
			'browser'     => __( 'Browser', 'dealroot' ),
			'landing'     => __( 'Landing page', 'dealroot' ),
			'lost_reason' => __( 'Why it was lost', 'dealroot' ),
			'month'       => __( 'Month', 'dealroot' ),
			'week'        => __( 'Week', 'dealroot' ),
			'day'         => __( 'Day', 'dealroot' ),
			'weekday'     => __( 'Day of the week', 'dealroot' ),
			'hour'        => __( 'Time of day', 'dealroot' ),
			'status'      => __( 'Status', 'dealroot' ),
			'lead'        => __( 'Enquiry', 'dealroot' ),
		);
	}

	/**
	 * Name of a grouping.
	 *
	 * @param string $group Grouping.
	 * @return string
	 */
	private static function group_label( $group ) {
		return self::groups()[ $group ] ?? '';
	}

	/**
	 * A day of the site, or null.
	 *
	 * @param string        $day  YYYY-MM-DD.
	 * @param \DateTimeZone $zone Time zone of the site.
	 * @return \DateTimeImmutable|null
	 */
	private static function day( $day, $zone ) {
		$date = preg_match( '/^\d{4}-\d{2}-\d{2}$/', $day ) ? \DateTimeImmutable::createFromFormat( '!Y-m-d', $day, $zone ) : false;

		return $date ? $date : null;
	}

	/**
	 * A time of the site as stored, in UTC.
	 *
	 * @param \DateTimeImmutable $time Time.
	 * @return string
	 */
	private static function utc( \DateTimeImmutable $time ) {
		return $time->setTimezone( new \DateTimeZone( 'UTC' ) )->format( 'Y-m-d H:i:s' );
	}
}