<?php
/**
 * Report queries.
 *
 * @package Visitrix
 */

defined( 'ABSPATH' ) || exit;

// Custom tables: direct queries are expected here; whole reports are cached in transients.
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

/**
 * Builds the dashboard report for a date range.
 *
 * Bounce = visit with a single pageview that lasted less than 15 seconds (Yandex Metrica definition).
 */
class Visitrix_Stats {

	/** Seconds below which a one-page visit is a bounce. */
	const BOUNCE_SECONDS = 15;

	/**
	 * Full report, cached briefly.
	 *
	 * @param string $from Y-m-d (site timezone).
	 * @param string $to   Y-m-d (site timezone).
	 * @return array
	 */
	public static function report( $from, $to ) {
		$key    = 'visitrix_r_' . md5( $from . '|' . $to . '|' . VISITRIX_VERSION . '|' . (int) get_option( 'visitrix_cache_gen', 0 ) );
		$cached = get_transient( $key );
		if ( false !== $cached ) {
			return $cached;
		}

		$tz      = wp_timezone();
		$d_from  = new DateTimeImmutable( $from, $tz );
		$d_to    = new DateTimeImmutable( $to, $tz );
		$days    = (int) $d_from->diff( $d_to )->days + 1;
		$hourly  = $days <= 3;
		$start   = $from . ' 00:00:00';
		$end     = $to . ' 23:59:59';
		$p_start = $d_from->modify( '-' . $days . ' days' )->format( 'Y-m-d' ) . ' 00:00:00';
		$p_end   = $d_from->modify( '-1 day' )->format( 'Y-m-d' ) . ' 23:59:59';

		$report = array(
			'from'      => $from,
			'to'        => $to,
			'hourly'    => $hourly,
			'totals'    => self::totals( $start, $end ),
			'previous'  => self::totals( $p_start, $p_end ),
			'series'    => self::series( $d_from, $d_to, $hourly ),
			'sources'   => self::dimension( 'source_type', $start, $end, 10 ),
			'details'   => self::source_details( $start, $end ),
			'search'    => self::dimension( 'source_name', $start, $end, 10, 'search' ),
			'returning' => self::returning( $start, $end ),
			'loyalty'   => self::loyalty( $start, $end ),
			'devices'   => self::dimension( 'device', $start, $end, 5 ),
			'browsers'  => self::dimension( 'browser', $start, $end, 10 ),
			'os'        => self::dimension( 'os', $start, $end, 10 ),
			'landing'   => self::dimension( 'landing', $start, $end, 10 ),
			'pages'     => self::pages( $start, $end ),
			'utm'       => self::utm( $start, $end ),
			'goals'     => self::goals( $start, $end ),
			'content'   => self::content( $start, $end ),
			'generated' => current_time( 'mysql' ),
		);

		$is_live = $to >= current_time( 'Y-m-d' );
		set_transient( $key, $report, $is_live ? MINUTE_IN_SECONDS : HOUR_IN_SECONDS );

		return $report;
	}

	/**
	 * Key metrics for a range.
	 *
	 * @param string $start Local datetime.
	 * @param string $end   Local datetime.
	 * @return array
	 */
	public static function totals( $start, $end ) {
		$db = Visitrix_DB::db();
		$row = $db->get_row(
			$db->prepare(
				'SELECT COUNT(*) AS visits,
					COUNT(DISTINCT visitor_key) AS visitors,
					COALESCE(SUM(pageviews), 0) AS pageviews,
					COALESCE(SUM(pageviews <= 1 AND duration < %d), 0) AS bounces,
					COALESCE(AVG(duration), 0) AS duration,
					COUNT(DISTINCT CASE WHEN is_new = 1 THEN visitor_key END) AS new_visitors,
					COUNT(DISTINCT CASE WHEN id_type <> \'a\' THEN visitor_key END) AS known_visitors,
					COALESCE(SUM(id_type = \'a\'), 0) AS anon_visits
				FROM %i WHERE engaged = 1 AND started_at BETWEEN %s AND %s',
				self::BOUNCE_SECONDS,
				Visitrix_DB::visits_table(),
				$start,
				$end
			),
			ARRAY_A
		);
		if ( ! is_array( $row ) ) {
			$row = array_fill_keys( array( 'visits', 'visitors', 'pageviews', 'bounces', 'duration', 'new_visitors', 'known_visitors', 'anon_visits' ), 0 );
		}

		$visits   = (int) $row['visits'];
		$visitors = (int) $row['visitors'];
		$known    = (int) $row['known_visitors'];

		return array(
			'visits'       => $visits,
			'visitors'     => $visitors,
			'pageviews'    => (int) $row['pageviews'],
			'bounce'       => $visits ? round( $row['bounces'] / $visits * 100, 1 ) : 0,
			'depth'        => $visits ? round( $row['pageviews'] / $visits, 2 ) : 0,
			'duration'     => (int) round( $row['duration'] ),
			'new_visitors' => $known ? round( $row['new_visitors'] / $known * 100, 1 ) : 0,
			'anon_visits'  => (int) $row['anon_visits'],
		);
	}

	/**
	 * Time series bucketed by hour or day, gaps filled with zeros.
	 *
	 * @param DateTimeImmutable $from   Start day.
	 * @param DateTimeImmutable $to     End day.
	 * @param bool              $hourly Hourly buckets.
	 * @return array
	 */
	private static function series( DateTimeImmutable $from, DateTimeImmutable $to, $hourly ) {
		$db = Visitrix_DB::db();
		$len  = $hourly ? 13 : 10; // "Y-m-d H" or "Y-m-d" prefix of the DATETIME.
		$rows = $db->get_results(
			$db->prepare(
				'SELECT LEFT(started_at, %d) AS b, COUNT(*) AS v, COUNT(DISTINCT visitor_key) AS u, SUM(pageviews) AS p,
					COUNT(DISTINCT CASE WHEN is_new = 1 THEN visitor_key END) AS n
				FROM %i WHERE engaged = 1 AND started_at BETWEEN %s AND %s GROUP BY b ORDER BY b',
				$len,
				Visitrix_DB::visits_table(),
				$from->format( 'Y-m-d' ) . ' 00:00:00',
				$to->format( 'Y-m-d' ) . ' 23:59:59'
			),
			OBJECT_K
		);

		$out  = array();
		$now  = current_time( $hourly ? 'Y-m-d H' : 'Y-m-d' );
		$step = $hourly ? '+1 hour' : '+1 day';
		$fmt  = $hourly ? 'Y-m-d H' : 'Y-m-d';
		// Buckets are local wall-clock times; iterate in UTC so DST switches neither repeat nor skip an hour.
		$utc    = new DateTimeZone( 'UTC' );
		$cursor = new DateTimeImmutable( $from->format( 'Y-m-d' ) . ' 00:00:00', $utc );
		$last   = new DateTimeImmutable( $to->format( 'Y-m-d' ) . ' 23:00:00', $utc );

		while ( $cursor <= $last ) {
			$b = $cursor->format( $fmt );
			if ( $b > $now ) {
				break;
			}
			$out[]  = array(
				'b' => $b,
				'v' => isset( $rows[ $b ] ) ? (int) $rows[ $b ]->v : 0,
				'u' => isset( $rows[ $b ] ) ? (int) $rows[ $b ]->u : 0,
				'p' => isset( $rows[ $b ] ) ? (int) $rows[ $b ]->p : 0,
				'n' => isset( $rows[ $b ] ) ? (int) $rows[ $b ]->n : 0,
			);
			$cursor = $cursor->modify( $step );
		}
		return $out;
	}

	/**
	 * Breakdown by a visits column with Metrica-style metrics.
	 *
	 * @param string $column One of the whitelisted columns.
	 * @param string $start  Local datetime.
	 * @param string $end    Local datetime.
	 * @param int    $limit  Rows.
	 * @param string $only   Optional source_type filter.
	 * @return array
	 */
	private static function dimension( $column, $start, $end, $limit, $only = '' ) {
		$db = Visitrix_DB::db();
		if ( ! in_array( $column, array( 'source_type', 'source_name', 'device', 'browser', 'os', 'landing' ), true ) ) {
			return array();
		}

		$rows = $db->get_results(
			$db->prepare(
				'SELECT %i AS name, COUNT(*) AS visits, COUNT(DISTINCT visitor_key) AS visitors,
					SUM(pageviews <= 1 AND duration < %d) / COUNT(*) * 100 AS bounce,
					AVG(pageviews) AS depth, AVG(duration) AS duration
				FROM %i
				WHERE engaged = 1 AND started_at BETWEEN %s AND %s AND ( %s = \'\' OR source_type = %s )
				GROUP BY name ORDER BY visits DESC LIMIT %d',
				$column,
				self::BOUNCE_SECONDS,
				Visitrix_DB::visits_table(),
				$start,
				$end,
				$only,
				$only,
				$limit
			),
			ARRAY_A
		);
		return self::rows( $rows );
	}

	/**
	 * Sources broken down by type + name (sites, engines, networks).
	 *
	 * @param string $start Local datetime.
	 * @param string $end   Local datetime.
	 * @return array
	 */
	private static function source_details( $start, $end ) {
		$db = Visitrix_DB::db();
		$rows = $db->get_results(
			$db->prepare(
				'SELECT source_type AS type, source_name AS name, COUNT(*) AS visits, COUNT(DISTINCT visitor_key) AS visitors,
					SUM(pageviews <= 1 AND duration < %d) / COUNT(*) * 100 AS bounce,
					AVG(pageviews) AS depth, AVG(duration) AS duration
				FROM %i
				WHERE engaged = 1 AND started_at BETWEEN %s AND %s AND source_type NOT IN (\'direct\', \'internal\')
				GROUP BY source_type, source_name ORDER BY visits DESC LIMIT 20',
				self::BOUNCE_SECONDS,
				Visitrix_DB::visits_table(),
				$start,
				$end
			),
			ARRAY_A
		);
		return self::rows( $rows );
	}

	/**
	 * UTM campaigns.
	 *
	 * @param string $start Local datetime.
	 * @param string $end   Local datetime.
	 * @return array
	 */
	private static function utm( $start, $end ) {
		$db = Visitrix_DB::db();
		$rows = $db->get_results(
			$db->prepare(
				'SELECT CONCAT_WS(\' / \', utm_source, NULLIF(utm_medium, \'\'), NULLIF(utm_campaign, \'\')) AS name,
					COUNT(*) AS visits, COUNT(DISTINCT visitor_key) AS visitors,
					SUM(pageviews <= 1 AND duration < %d) / COUNT(*) * 100 AS bounce,
					AVG(pageviews) AS depth, AVG(duration) AS duration
				FROM %i
				WHERE engaged = 1 AND started_at BETWEEN %s AND %s AND utm_source <> \'\'
				GROUP BY name ORDER BY visits DESC LIMIT 10',
				self::BOUNCE_SECONDS,
				Visitrix_DB::visits_table(),
				$start,
				$end
			),
			ARRAY_A
		);
		return self::rows( $rows );
	}

	/**
	 * New vs returning visitors. A visitor is new if their first visit falls inside the period,
	 * so every visitor belongs to exactly one group. Anonymous-mode visits are excluded.
	 *
	 * @param string $start Local datetime.
	 * @param string $end   Local datetime.
	 * @return array
	 */
	private static function returning( $start, $end ) {
		$db = Visitrix_DB::db();
		$rows = $db->get_results(
			$db->prepare(
				'SELECT IF(vr.first_seen >= %s, \'1\', \'0\') AS name, COUNT(*) AS visits, COUNT(DISTINCT v.visitor_key) AS visitors,
					SUM(v.pageviews <= 1 AND v.duration < %d) / COUNT(*) * 100 AS bounce,
					AVG(v.pageviews) AS depth, AVG(v.duration) AS duration
				FROM %i v INNER JOIN %i vr ON vr.visitor_key = v.visitor_key
				WHERE v.engaged = 1 AND v.id_type <> \'a\' AND v.started_at BETWEEN %s AND %s
				GROUP BY name ORDER BY name DESC',
				$start,
				self::BOUNCE_SECONDS,
				Visitrix_DB::visits_table(),
				Visitrix_DB::visitors_table(),
				$start,
				$end
			),
			ARRAY_A
		);
		return self::rows( $rows );
	}

	/**
	 * Loyalty: visitors of the period grouped by their total number of visits.
	 *
	 * @param string $start Local datetime.
	 * @param string $end   Local datetime.
	 * @return array
	 */
	private static function loyalty( $start, $end ) {
		$db = Visitrix_DB::db();
		$rows = $db->get_results(
			$db->prepare(
				'SELECT CASE WHEN vr.visits <= 1 THEN \'1\' WHEN vr.visits = 2 THEN \'2\' WHEN vr.visits <= 5 THEN \'3-5\' WHEN vr.visits <= 10 THEN \'6-10\' ELSE \'11+\' END AS name,
					COUNT(DISTINCT v.visitor_key) AS visitors
				FROM %i v INNER JOIN %i vr ON vr.visitor_key = v.visitor_key
				WHERE v.engaged = 1 AND v.id_type <> \'a\' AND v.started_at BETWEEN %s AND %s
				GROUP BY name',
				Visitrix_DB::visits_table(),
				Visitrix_DB::visitors_table(),
				$start,
				$end
			),
			OBJECT_K
		);
		$out = array();
		foreach ( array( '1', '2', '3-5', '6-10', '11+' ) as $bucket ) {
			$out[] = array(
				'name'     => $bucket,
				'visitors' => isset( $rows[ $bucket ] ) ? (int) $rows[ $bucket ]->visitors : 0,
			);
		}
		return $out;
	}

	/**
	 * Visitors active during the last 5 minutes.
	 *
	 * @return int
	 */
	public static function online() {
		$db = Visitrix_DB::db();
		return (int) $db->get_var(
			$db->prepare(
				'SELECT COUNT(DISTINCT visitor_key) FROM %i WHERE ts_last >= %d AND engaged = 1',
				Visitrix_DB::visits_table(),
				time() - 5 * MINUTE_IN_SECONDS
			)
		);
	}

	/**
	 * Most viewed pages.
	 *
	 * @param string $start Local datetime.
	 * @param string $end   Local datetime.
	 * @return array
	 */
	private static function pages( $start, $end ) {
		$db = Visitrix_DB::db();
		$rows = $db->get_results(
			$db->prepare(
				'SELECT h.path AS name, MAX(h.title) AS title, COUNT(*) AS views, COUNT(DISTINCT h.visit_id) AS visits,
					AVG(NULLIF(h.scroll, 0)) AS scroll
				FROM %i h INNER JOIN %i v ON v.id = h.visit_id AND v.engaged = 1
				WHERE h.created_at BETWEEN %s AND %s
				GROUP BY h.path ORDER BY views DESC LIMIT 15',
				Visitrix_DB::hits_table(),
				Visitrix_DB::visits_table(),
				$start,
				$end
			),
			ARRAY_A
		);
		return array_map(
			static function ( $r ) {
				return array(
					'name'   => (string) $r['name'],
					'title'  => (string) $r['title'],
					'views'  => (int) $r['views'],
					'visits' => (int) $r['visits'],
					'scroll' => null === $r['scroll'] ? null : (int) round( (float) $r['scroll'] ),
				);
			},
			(array) $rows
		);
	}

	/**
	 * Goals: reached goals, converted visits and conversions per traffic source.
	 *
	 * @param string $start Local datetime.
	 * @param string $end   Local datetime.
	 * @return array
	 */
	private static function goals( $start, $end ) {
		$db = Visitrix_DB::db();
		$goals  = Visitrix_DB::goals_table();
		$visits = Visitrix_DB::visits_table();

		$rows = $db->get_results(
			$db->prepare(
				'SELECT g.type, g.target, COUNT(*) AS hits, COUNT(DISTINCT g.visit_id) AS visits
				FROM %i g INNER JOIN %i v ON v.id = g.visit_id AND v.engaged = 1
				WHERE g.created_at BETWEEN %s AND %s
				GROUP BY g.type, g.target ORDER BY visits DESC, hits DESC LIMIT 30',
				$goals,
				$visits,
				$start,
				$end
			),
			ARRAY_A
		);
		$by_type = $db->get_results(
			$db->prepare(
				'SELECT g.type AS name, COUNT(DISTINCT g.visit_id) AS visits
				FROM %i g INNER JOIN %i v ON v.id = g.visit_id AND v.engaged = 1
				WHERE g.created_at BETWEEN %s AND %s GROUP BY g.type ORDER BY visits DESC',
				$goals,
				$visits,
				$start,
				$end
			),
			ARRAY_A
		);
		$by_source = $db->get_results(
			$db->prepare(
				'SELECT v.source_type AS name, COUNT(DISTINCT g.visit_id) AS visits
				FROM %i g INNER JOIN %i v ON v.id = g.visit_id AND v.engaged = 1
				WHERE g.created_at BETWEEN %s AND %s GROUP BY v.source_type',
				$goals,
				$visits,
				$start,
				$end
			),
			ARRAY_A
		);
		$converted = (int) $db->get_var(
			$db->prepare(
				'SELECT COUNT(DISTINCT g.visit_id) FROM %i g INNER JOIN %i v ON v.id = g.visit_id AND v.engaged = 1
				WHERE g.created_at BETWEEN %s AND %s',
				$goals,
				$visits,
				$start,
				$end
			)
		);

		$cast = static function ( $list ) {
			return array_map(
				static function ( $r ) {
					return array(
						'name'   => (string) $r['name'],
						'visits' => (int) $r['visits'],
					);
				},
				(array) $list
			);
		};

		return array(
			'converted' => $converted,
			'items'     => array_map(
				static function ( $r ) {
					$target = (string) $r['target'];
					// Contact Form 7 forms are reported by ID: show their title instead.
					if ( 'form' === $r['type'] && preg_match( '/^cf7-(\d+)$/', $target, $m ) ) {
						$title  = html_entity_decode( get_the_title( (int) $m[1] ), ENT_QUOTES, 'UTF-8' );
						$target = '' !== $title ? $title : $target;
					}
					return array(
						'type'   => (string) $r['type'],
						'target' => $target,
						'hits'   => (int) $r['hits'],
						'visits' => (int) $r['visits'],
					);
				},
				(array) $rows
			),
			'types'     => $cast( $by_type ),
			'sources'   => $cast( $by_source ),
		);
	}

	/**
	 * WordPress content: posts, categories, tags and authors by pageviews.
	 *
	 * @param string $start Local datetime.
	 * @param string $end   Local datetime.
	 * @return array
	 */
	private static function content( $start, $end ) {
		$db = Visitrix_DB::db();
		$hits   = Visitrix_DB::hits_table();
		$visits = Visitrix_DB::visits_table();

		$posts = $db->get_results(
			$db->prepare(
				'SELECT h.post_id AS id, COUNT(*) AS views, COUNT(DISTINCT h.visit_id) AS visits, AVG(NULLIF(h.scroll, 0)) AS scroll
				FROM %i h INNER JOIN %i v ON v.id = h.visit_id AND v.engaged = 1
				WHERE h.created_at BETWEEN %s AND %s AND h.post_id > 0
				GROUP BY h.post_id ORDER BY views DESC LIMIT 15',
				$hits,
				$visits,
				$start,
				$end
			),
			ARRAY_A
		);

		if ( Visitrix_DB::is_external() ) {
			list( $terms, $authors ) = self::content_external( $start, $end );
		} else {
			list( $terms, $authors ) = self::content_joined( $start, $end );
		}

		$named = static function ( $list, $namer ) {
			$out = array();
			foreach ( (array) $list as $r ) {
				$name = (string) call_user_func( $namer, (int) $r['id'] );
				$item = array(
					'name'   => '' !== $name ? $name : '#' . (int) $r['id'],
					'views'  => (int) $r['views'],
					'visits' => (int) $r['visits'],
				);
				if ( array_key_exists( 'scroll', $r ) ) {
					$item['scroll'] = null === $r['scroll'] ? null : (int) round( (float) $r['scroll'] );
					$item['url']    = (string) get_permalink( (int) $r['id'] );
				}
				$out[] = $item;
			}
			return $out;
		};
		$term_name = static function ( $id ) {
			$term = get_term( $id );
			return ( $term && ! is_wp_error( $term ) ) ? $term->name : '';
		};

		return array(
			'posts'      => $named(
				$posts,
				static function ( $id ) {
					return html_entity_decode( get_the_title( $id ), ENT_QUOTES, 'UTF-8' );
				}
			),
			'categories' => $named( $terms['category'], $term_name ),
			'tags'       => $named( $terms['post_tag'], $term_name ),
			'authors'    => $named(
				$authors,
				static function ( $id ) {
					return get_the_author_meta( 'display_name', $id );
				}
			),
		);
	}

	/**
	 * Categories, tags and authors when the statistics live in the WordPress database: one join each.
	 *
	 * @param string $start Local datetime.
	 * @param string $end   Local datetime.
	 * @return array [terms by taxonomy, authors]
	 */
	private static function content_joined( $start, $end ) {
		global $wpdb;
		$hits   = Visitrix_DB::hits_table();
		$visits = Visitrix_DB::visits_table();

		$terms = array();
		foreach ( array( 'category', 'post_tag' ) as $taxonomy ) {
			$terms[ $taxonomy ] = $wpdb->get_results(
				$wpdb->prepare(
					'SELECT tt.term_id AS id, COUNT(*) AS views, COUNT(DISTINCT h.visit_id) AS visits
					FROM %i h
					INNER JOIN %i v ON v.id = h.visit_id AND v.engaged = 1
					INNER JOIN %i tr ON tr.object_id = h.post_id
					INNER JOIN %i tt ON tt.term_taxonomy_id = tr.term_taxonomy_id AND tt.taxonomy = %s
					WHERE h.created_at BETWEEN %s AND %s AND h.post_id > 0
					GROUP BY tt.term_id ORDER BY views DESC LIMIT 15',
					$hits,
					$visits,
					$wpdb->term_relationships,
					$wpdb->term_taxonomy,
					$taxonomy,
					$start,
					$end
				),
				ARRAY_A
			);
		}

		$authors = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT p.post_author AS id, COUNT(*) AS views, COUNT(DISTINCT h.visit_id) AS visits
				FROM %i h
				INNER JOIN %i v ON v.id = h.visit_id AND v.engaged = 1
				INNER JOIN %i p ON p.ID = h.post_id
				WHERE h.created_at BETWEEN %s AND %s AND h.post_id > 0
				GROUP BY p.post_author ORDER BY views DESC LIMIT 15',
				$hits,
				$visits,
				$wpdb->posts,
				$start,
				$end
			),
			ARRAY_A
		);

		return array( $terms, $authors );
	}

	/**
	 * Categories, tags and authors when the statistics live in another database: post views come from
	 * there, terms and authors from WordPress, distinct visits are counted per item in the statistics database.
	 *
	 * @param string $start Local datetime.
	 * @param string $end   Local datetime.
	 * @return array [terms by taxonomy, authors]
	 */
	private static function content_external( $start, $end ) {
		global $wpdb;
		$db     = Visitrix_DB::db();
		$hits   = Visitrix_DB::hits_table();
		$visits = Visitrix_DB::visits_table();

		$views = array();
		foreach ( (array) $db->get_results(
			$db->prepare(
				'SELECT h.post_id AS id, COUNT(*) AS views
				FROM %i h INNER JOIN %i v ON v.id = h.visit_id AND v.engaged = 1
				WHERE h.created_at BETWEEN %s AND %s AND h.post_id > 0
				GROUP BY h.post_id ORDER BY views DESC LIMIT 5000',
				$hits,
				$visits,
				$start,
				$end
			),
			ARRAY_A
		) as $r ) {
			$views[ (int) $r['id'] ] = (int) $r['views'];
		}
		$empty = array( array( 'category' => array(), 'post_tag' => array() ), array() );
		if ( ! $views ) {
			return $empty;
		}
		$ids = array_keys( $views );

		// Groups of posts: term id => post ids, author id => post ids.
		$groups = array( 'category' => array(), 'post_tag' => array(), 'author' => array() );
		$objects = wp_get_object_terms( $ids, array( 'category', 'post_tag' ), array( 'fields' => 'all_with_object_id' ) );
		foreach ( is_wp_error( $objects ) ? array() : $objects as $t ) {
			$groups[ $t->taxonomy ][ (int) $t->term_id ][] = (int) $t->object_id;
		}
		foreach ( array_chunk( $ids, 1000 ) as $chunk ) {
			$in = implode( ',', array_fill( 0, count( $chunk ), '%d' ) );
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- $in contains only %d placeholders.
			$rows = $wpdb->get_results( $wpdb->prepare( "SELECT ID, post_author FROM %i WHERE ID IN ($in)", array_merge( array( $wpdb->posts ), $chunk ) ) );
			foreach ( (array) $rows as $r ) {
				$groups['author'][ (int) $r->post_author ][] = (int) $r->ID;
			}
		}

		$top = static function ( $group ) use ( $db, $hits, $visits, $views, $start, $end ) {
			$list = array();
			foreach ( $group as $id => $posts ) {
				$sum = 0;
				foreach ( $posts as $post ) {
					$sum += $views[ $post ] ?? 0;
				}
				$list[] = array( 'id' => $id, 'views' => $sum, 'posts' => array_unique( $posts ) );
			}
			usort(
				$list,
				static function ( $a, $b ) {
					return $b['views'] <=> $a['views'];
				}
			);
			$list = array_slice( $list, 0, 15 );
			foreach ( $list as &$item ) {
				$in = implode( ',', array_fill( 0, count( $item['posts'] ), '%d' ) );
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- $in contains only %d placeholders.
				$item['visits'] = (int) $db->get_var( $db->prepare( "SELECT COUNT(DISTINCT h.visit_id) FROM %i h INNER JOIN %i v ON v.id = h.visit_id AND v.engaged = 1 WHERE h.created_at BETWEEN %s AND %s AND h.post_id IN ($in)", array_merge( array( $hits, $visits, $start, $end ), $item['posts'] ) ) );
				unset( $item['posts'] );
			}
			unset( $item );
			return $list;
		};

		return array(
			array(
				'category' => $top( $groups['category'] ),
				'post_tag' => $top( $groups['post_tag'] ),
			),
			$top( $groups['author'] ),
		);
	}

	/**
	 * Scroll map of one page: share of pageviews that reached each 10% of the page height.
	 *
	 * @param string $path   Page path.
	 * @param string $device desktop|mobile|tablet or '' for all.
	 * @param string $start  Local datetime.
	 * @param string $end    Local datetime.
	 * @return array{total:int,levels:int[]}
	 */
	public static function scroll_map( $path, $device, $start, $end ) {
		$db = Visitrix_DB::db();
		$row = $db->get_row(
			$db->prepare(
				'SELECT COUNT(*) AS total,
					SUM(h.scroll >= 10) AS d10, SUM(h.scroll >= 20) AS d20, SUM(h.scroll >= 30) AS d30, SUM(h.scroll >= 40) AS d40,
					SUM(h.scroll >= 50) AS d50, SUM(h.scroll >= 60) AS d60, SUM(h.scroll >= 70) AS d70, SUM(h.scroll >= 80) AS d80,
					SUM(h.scroll >= 90) AS d90, SUM(h.scroll >= 100) AS d100
				FROM %i h INNER JOIN %i v ON v.id = h.visit_id AND v.engaged = 1
				WHERE h.path = %s AND h.scroll > 0 AND h.created_at BETWEEN %s AND %s AND ( %s = \'\' OR v.device = %s )',
				Visitrix_DB::hits_table(),
				Visitrix_DB::visits_table(),
				$path,
				$start,
				$end,
				$device,
				$device
			),
			ARRAY_A
		);
		$total  = $row ? (int) $row['total'] : 0;
		$levels = array();
		foreach ( range( 10, 100, 10 ) as $d ) {
			$levels[] = $total ? (int) round( (int) $row[ 'd' . $d ] / $total * 100 ) : 0;
		}
		return array(
			'total'  => $total,
			'levels' => $levels,
		);
	}

	/**
	 * Pages that have scroll data, for the scroll map page picker.
	 *
	 * @param string $start Local datetime.
	 * @param string $end   Local datetime.
	 * @return array
	 */
	public static function scroll_pages( $start, $end ) {
		$db = Visitrix_DB::db();
		$rows = $db->get_results(
			$db->prepare(
				'SELECT path, MAX(title) AS title, COUNT(*) AS views FROM %i
				WHERE scroll > 0 AND created_at BETWEEN %s AND %s
				GROUP BY path ORDER BY views DESC LIMIT 50',
				Visitrix_DB::hits_table(),
				$start,
				$end
			),
			ARRAY_A
		);
		return array_map(
			static function ( $r ) {
				return array(
					'path'  => (string) $r['path'],
					'title' => (string) $r['title'],
					'views' => (int) $r['views'],
				);
			},
			(array) $rows
		);
	}

	/**
	 * Casts numeric columns.
	 *
	 * @param array|null $rows Rows.
	 * @return array
	 */
	private static function rows( $rows ) {
		$out = array();
		foreach ( (array) $rows as $r ) {
			$item = array(
				'name'     => (string) $r['name'],
				'visits'   => (int) $r['visits'],
				'visitors' => (int) $r['visitors'],
				'bounce'   => round( (float) $r['bounce'], 1 ),
				'depth'    => round( (float) $r['depth'], 2 ),
				'duration' => (int) round( (float) $r['duration'] ),
			);
			if ( isset( $r['type'] ) ) {
				$item['type'] = (string) $r['type'];
			}
			$out[] = $item;
		}
		return $out;
	}
}