<?php
/**
 * Read layer: builds every figure the dashboard shows.
 *
 * Past, completed days are served from the rollup tables; "today" is always
 * computed live from raw events; realtime reads the last few minutes of raw.
 *
 * Note (MVP approximation): multi-day "visitors" is the sum of daily uniques,
 * so a person visiting on two days counts twice over a range. This is a common
 * simplification (documented) to be refined later with a distinct-over-range
 * pass or HLL sketches.
 *
 * @package Defog
 */

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

class Defog_Query {

	const TOP_N = 6;

	/** Allowed metrics → daily rollup column. */
	private static $metric_cols = array(
		'visitors'  => 'visitors',
		'pageviews' => 'pageviews',
		'sessions'  => 'sessions',
	);

	/** Allowed dimensions → raw column. */
	private static $dim_cols = array(
		'source'  => 'source',
		'country' => 'country',
		'device'  => 'device',
		'page'    => 'path',
	);

	/**
	 * Resolve a range key into concrete dates.
	 *
	 * @param string $range today|7d|30d.
	 * @return array
	 */
	public function range_dates( $range ) {
		$today = defog_today();
		if ( 'today' === $range ) {
			$yest = wp_date( 'Y-m-d', strtotime( $today . ' -1 day' ) );
			return array(
				'granularity' => 'hour',
				'start'       => $today,
				'end'         => $today,
				'prev_start'  => $yest,
				'prev_end'    => $yest,
				'cmp'         => __( 'yesterday', 'fogless-analytics' ),
				'label'       => __( 'today', 'fogless-analytics' ),
			);
		}

		$len   = ( '30d' === $range ) ? 30 : 7;
		$start = wp_date( 'Y-m-d', strtotime( $today . ' -' . ( $len - 1 ) . ' days' ) );
		$pend  = wp_date( 'Y-m-d', strtotime( $start . ' -1 day' ) );
		$pstart = wp_date( 'Y-m-d', strtotime( $pend . ' -' . ( $len - 1 ) . ' days' ) );

		return array(
			'granularity' => 'day',
			'start'       => $start,
			'end'         => $today,
			'prev_start'  => $pstart,
			'prev_end'    => $pend,
			/* translators: %d: number of days. */
			'cmp'         => sprintf( __( 'previous %d days', 'fogless-analytics' ), $len ),
			/* translators: %d: number of days. */
			'label'       => sprintf( __( 'last %d days', 'fogless-analytics' ), $len ),
		);
	}

	/* --------------------------------------------------------------------- */
	/* KPIs                                                                    */
	/* --------------------------------------------------------------------- */

	/**
	 * KPI block with deltas vs the previous comparable period.
	 *
	 * @param string $range Range key.
	 * @return array
	 */
	public function kpis( $range ) {
		$r    = $this->range_dates( $range );
		$cur  = $this->sum_kpis( $r['start'], $r['end'] );
		$prev = $this->sum_kpis( $r['prev_start'], $r['prev_end'] );

		// Headline "Visitors" is a true distinct-over-range count (each person once),
		// not the sum of daily uniques. Bounce keeps its own daily-based math below.
		$cur_visitors  = $this->distinct_visitors( $r['start'], $r['end'] );
		$prev_visitors = $this->distinct_visitors( $r['prev_start'], $r['prev_end'] );

		$cur_bounce  = $cur['visitors'] ? $cur['bounces'] / $cur['visitors'] * 100 : 0;
		$prev_bounce = $prev['visitors'] ? $prev['bounces'] / $prev['visitors'] * 100 : 0;

		return array(
			'visitors'  => array(
				'val' => $cur_visitors,
				'd'   => $this->pct( $cur_visitors, $prev_visitors ),
			),
			'pageviews' => array(
				'val' => $cur['pageviews'],
				'd'   => $this->pct( $cur['pageviews'], $prev['pageviews'] ),
			),
			'sessions'  => array(
				'val' => $cur['sessions'],
				'd'   => $this->pct( $cur['sessions'], $prev['sessions'] ),
			),
			'bounce'    => array(
				'val' => round( $cur_bounce, 1 ),
				'd'   => round( $cur_bounce - $prev_bounce, 1 ), // Percentage-point change.
			),
			'cmp'       => $r['cmp'],
			'label'     => $r['label'],
		);
	}

	/**
	 * Sum KPI counters across a date range (rollups for past days + live today).
	 *
	 * @param string $from Y-m-d.
	 * @param string $to   Y-m-d.
	 * @return array
	 */
	private function sum_kpis( $from, $to ) {
		global $wpdb;
		$daily = defog_table( 'stats_daily' );
		$today = defog_today();
		$agg   = array(
			'visitors'  => 0,
			'pageviews' => 0,
			'sessions'  => 0,
			'bounces'   => 0,
		);

		$complete_to = ( $to >= $today ) ? wp_date( 'Y-m-d', strtotime( $today . ' -1 day' ) ) : $to;
		if ( $from <= $complete_to ) {
			$row = $wpdb->get_row(
				$wpdb->prepare(
					"SELECT COALESCE(SUM(visitors),0) v, COALESCE(SUM(pageviews),0) p, COALESCE(SUM(sessions),0) s, COALESCE(SUM(bounces),0) b
					 FROM %i WHERE stat_date BETWEEN %s AND %s",
					$daily,
					$from,
					$complete_to
				),
				ARRAY_A
			);
			if ( $row ) {
				$agg['visitors']  += (int) $row['v'];
				$agg['pageviews'] += (int) $row['p'];
				$agg['sessions']  += (int) $row['s'];
				$agg['bounces']   += (int) $row['b'];
			}
		}

		if ( $to >= $today && $from <= $today ) {
			$live = $this->today_kpis();
			foreach ( $agg as $k => $_ ) {
				$agg[ $k ] += $live[ $k ];
			}
		}

		return $agg;
	}

	/**
	 * True distinct visitors across a range (each person counted once).
	 *
	 * Improves on the sum-of-daily-uniques approximation by counting straight from
	 * raw events, which are retained for the retention window (comfortably covering
	 * the 7d/30d ranges). If the range reaches back before retained raw exists, it
	 * falls back to the daily sum so figures are never silently under-counted.
	 *
	 * @param string $from Y-m-d.
	 * @param string $to   Y-m-d.
	 * @return int
	 */
	private function distinct_visitors( $from, $to ) {
		global $wpdb;
		// Oldest day for which raw events are guaranteed present (matches prune()).
		$raw_floor = wp_date( 'Y-m-d', strtotime( '-' . defog_retention_days() . ' days' ) );

		if ( $from < $raw_floor ) {
			return (int) $this->sum_kpis( $from, $to )['visitors'];
		}

		$events = defog_table( 'events' );
		return (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(DISTINCT visitor_hash) FROM %i WHERE occurred_at BETWEEN %s AND %s",
					$events,
				$from . ' 00:00:00',
				$to . ' 23:59:59'
			)
		);
	}

	/**
	 * Live KPI counters for the current local day from raw events.
	 *
	 * @return array
	 */
	private function today_kpis() {
		global $wpdb;
		$events = defog_table( 'events' );
		$start  = defog_today() . ' 00:00:00';
		$end    = defog_today() . ' 23:59:59';

		$pageviews = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE occurred_at BETWEEN %s AND %s",
					$events, $start, $end ) );
		$visitors  = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(DISTINCT visitor_hash) FROM %i WHERE occurred_at BETWEEN %s AND %s",
					$events, $start, $end ) );
		$sessions  = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COALESCE(SUM(is_new_session),0) FROM %i WHERE occurred_at BETWEEN %s AND %s",
					$events, $start, $end ) );
		$bounces   = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM (SELECT visitor_hash FROM %i WHERE occurred_at BETWEEN %s AND %s GROUP BY visitor_hash HAVING COUNT(*) = 1) t",
					$events,
				$start,
				$end
			)
		);

		return compact( 'visitors', 'pageviews', 'sessions', 'bounces' );
	}

	/* --------------------------------------------------------------------- */
	/* Time series                                                             */
	/* --------------------------------------------------------------------- */

	/**
	 * Build the main chart series.
	 *
	 * @param string $range  Range key.
	 * @param string $metric visitors|pageviews|sessions.
	 * @return array{labels:array,current:array,previous:array}
	 */
	public function series( $range, $metric ) {
		if ( ! isset( self::$metric_cols[ $metric ] ) ) {
			$metric = 'visitors';
		}
		$r = $this->range_dates( $range );

		if ( 'hour' === $r['granularity'] ) {
			$labels = array();
			for ( $h = 0; $h < 24; $h++ ) {
				$labels[] = sprintf( '%02d:00', $h );
			}
			return array(
				'labels'   => $labels,
				'current'  => $this->hourly_series( $r['start'], $metric ),
				'previous' => $this->hourly_series( $r['prev_start'], $metric ),
			);
		}

		$labels = $this->date_list( $r['start'], $r['end'] );
		return array(
			'labels'   => array_map(
				function ( $d ) {
					return wp_date( 'M j', strtotime( $d ) );
				},
				$labels
			),
			'current'  => $this->daily_series( $r['start'], $r['end'], $metric ),
			'previous' => $this->daily_series( $r['prev_start'], $r['prev_end'], $metric ),
		);
	}

	/**
	 * 24-bucket hourly series for one day (raw).
	 *
	 * @param string $date   Y-m-d.
	 * @param string $metric Metric.
	 * @return int[]
	 */
	private function hourly_series( $date, $metric ) {
		global $wpdb;
		$events = defog_table( 'events' );
		$start  = $date . ' 00:00:00';
		$end    = $date . ' 23:59:59';

		// One fully literal query per metric (no expression interpolation).
		if ( 'pageviews' === $metric ) {
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT HOUR(occurred_at) h, COUNT(*) val FROM %i WHERE occurred_at BETWEEN %s AND %s GROUP BY h",
					$events,
					$start,
					$end
				)
			);
		} elseif ( 'sessions' === $metric ) {
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT HOUR(occurred_at) h, COALESCE(SUM(is_new_session),0) val FROM %i WHERE occurred_at BETWEEN %s AND %s GROUP BY h",
					$events,
					$start,
					$end
				)
			);
		} else {
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT HOUR(occurred_at) h, COUNT(DISTINCT visitor_hash) val FROM %i WHERE occurred_at BETWEEN %s AND %s GROUP BY h",
					$events,
					$start,
					$end
				)
			);
		}

		$buckets = array_fill( 0, 24, 0 );
		foreach ( (array) $rows as $row ) {
			$buckets[ (int) $row->h ] = (int) $row->val;
		}
		return $buckets;
	}

	/**
	 * Daily series across a range (rollups + live today).
	 *
	 * @param string $from   Y-m-d.
	 * @param string $to     Y-m-d.
	 * @param string $metric Metric.
	 * @return int[]
	 */
	private function daily_series( $from, $to, $metric ) {
		global $wpdb;
		$daily  = defog_table( 'stats_daily' );
		$today  = defog_today();
		$column = self::$metric_cols[ $metric ];
		$dates  = $this->date_list( $from, $to );

		$map = array();
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT stat_date, %i val FROM %i WHERE stat_date BETWEEN %s AND %s",
					$column, $daily,
				$from,
				$to
			)
		);
		foreach ( (array) $rows as $row ) {
			$map[ $row->stat_date ] = (int) $row->val;
		}

		$out = array();
		foreach ( $dates as $d ) {
			if ( $d === $today ) {
				$live  = $this->today_kpis();
				$out[] = (int) $live[ $metric ];
			} else {
				$out[] = isset( $map[ $d ] ) ? $map[ $d ] : 0;
			}
		}
		return $out;
	}

	/* --------------------------------------------------------------------- */
	/* Dimensions                                                              */
	/* --------------------------------------------------------------------- */

	/**
	 * Top labels for a dimension across the range.
	 *
	 * @param string $range     Range key.
	 * @param string $dimension source|country|device|page.
	 * @return array[]
	 */
	public function dimension( $range, $dimension ) {
		if ( ! isset( self::$dim_cols[ $dimension ] ) ) {
			return array();
		}
		global $wpdb;
		$dim_table = defog_table( 'dim_daily' );
		$events    = defog_table( 'events' );
		$column    = self::$dim_cols[ $dimension ];
		$r         = $this->range_dates( $range );
		$today     = defog_today();

		$totals = array(); // label => [visitors, pageviews]

		$complete_to = ( $r['end'] >= $today ) ? wp_date( 'Y-m-d', strtotime( $today . ' -1 day' ) ) : $r['end'];
		if ( $r['start'] <= $complete_to ) {
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT label, SUM(visitors) v, SUM(pageviews) p FROM %i
					 WHERE dimension = %s AND stat_date BETWEEN %s AND %s GROUP BY label",
					$dim_table,
					$dimension,
					$r['start'],
					$complete_to
				)
			);
			foreach ( (array) $rows as $row ) {
				$this->accumulate( $totals, $row->label, (int) $row->v, (int) $row->p );
			}
		}

		if ( $r['end'] >= $today && $r['start'] <= $today ) {
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT %i label, COUNT(DISTINCT visitor_hash) v, COUNT(*) p FROM %i
					 WHERE occurred_at BETWEEN %s AND %s GROUP BY %i",
					$column,
					$events,
					$today . ' 00:00:00',
					$today . ' 23:59:59',
					$column
				)
			);
			foreach ( (array) $rows as $row ) {
				$this->accumulate( $totals, $row->label, (int) $row->v, (int) $row->p );
			}
		}

		$sort_key = ( 'page' === $dimension ) ? 'pageviews' : 'visitors';
		uasort(
			$totals,
			function ( $a, $b ) use ( $sort_key ) {
				return $b[ $sort_key ] <=> $a[ $sort_key ];
			}
		);

		$out = array();
		foreach ( array_slice( $totals, 0, self::TOP_N, true ) as $label => $vals ) {
			$out[] = array(
				'label'     => (string) $label,
				'visitors'  => $vals['visitors'],
				'pageviews' => $vals['pageviews'],
			);
		}
		return $out;
	}

	/**
	 * Merge a label's counters into the running totals.
	 */
	private function accumulate( &$totals, $label, $visitors, $pageviews ) {
		$label = (string) $label;
		if ( ! isset( $totals[ $label ] ) ) {
			$totals[ $label ] = array(
				'visitors'  => 0,
				'pageviews' => 0,
			);
		}
		$totals[ $label ]['visitors']  += $visitors;
		$totals[ $label ]['pageviews'] += $pageviews;
	}

	/* --------------------------------------------------------------------- */
	/* Events / actions                                                        */
	/* --------------------------------------------------------------------- */

	/**
	 * User-action block for a range (outbound clicks; extensible to more types),
	 * grouped by category with the top labels in each. Null when there are none.
	 *
	 * @param string $range Range key.
	 * @return array|null
	 */
	public function actions( $range ) {
		global $wpdb;
		$table = defog_table( 'actions' );
		$r     = $this->range_dates( $range );
		$start = $r['start'] . ' 00:00:00';
		$end   = $r['end'] . ' 23:59:59';

		$cats = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT category, COUNT(*) c, COUNT(DISTINCT visitor_hash) v FROM %i
				 WHERE occurred_at BETWEEN %s AND %s GROUP BY category ORDER BY c DESC",
					$table,
				$start,
				$end
			)
		);
		if ( ! $cats ) {
			return null;
		}

		$categories = array();
		$total      = 0;
		foreach ( (array) $cats as $cat ) {
			$items = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT label, COUNT(*) c, COUNT(DISTINCT visitor_hash) v FROM %i
					 WHERE category = %s AND occurred_at BETWEEN %s AND %s
					 GROUP BY label ORDER BY c DESC LIMIT %d",
					$table,
					$cat->category,
					$start,
					$end,
					self::TOP_N
				)
			);
			$categories[] = array(
				'key'      => (string) $cat->category,
				'count'    => (int) $cat->c,
				'visitors' => (int) $cat->v,
				'items'    => array_map(
					function ( $x ) {
						return array(
							'label'    => (string) $x->label,
							'count'    => (int) $x->c,
							'visitors' => (int) $x->v,
						);
					},
					(array) $items
				),
			);
			$total += (int) $cat->c;
		}

		return array(
			'total'      => $total,
			'categories' => $categories,
		);
	}

	/* --------------------------------------------------------------------- */
	/* Realtime                                                                */
	/* --------------------------------------------------------------------- */

	/**
	 * Realtime snapshot: active visitors, last-30-min sparkline, active pages,
	 * live sources. Always "now" — independent of any selected range.
	 *
	 * @return array
	 */
	public function realtime() {
		global $wpdb;

		// Short-lived cache: this endpoint is polled, and several open dashboard
		// tabs would otherwise repeat the same four aggregate queries. Uses the
		// object cache only — no database writes, no options-table growth.
		$cached = wp_cache_get( 'realtime', 'defog' );
		if ( false !== $cached ) {
			return $cached;
		}

		$events = defog_table( 'events' );

		$win5  = $this->ago( 300 );
		$win30 = $this->ago( 1800 );

		$now = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(DISTINCT visitor_hash) FROM %i WHERE occurred_at >= %s",
					$events, $win5 ) );

		// Per-minute pageviews for the last 30 minutes.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT DATE_FORMAT(occurred_at, '%%Y-%%m-%%d %%H:%%i') m, COUNT(*) c FROM %i WHERE occurred_at >= %s GROUP BY m",
					$events,
				$win30
			)
		);
		$by_minute = array();
		foreach ( (array) $rows as $row ) {
			$by_minute[ $row->m ] = (int) $row->c;
		}
		$last30 = array();
		for ( $i = 29; $i >= 0; $i-- ) {
			$key      = gmdate( 'Y-m-d H:i', current_time( 'timestamp' ) - ( $i * 60 ) );
			$last30[] = isset( $by_minute[ $key ] ) ? $by_minute[ $key ] : 0;
		}

		$pages = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT path label, COUNT(DISTINCT visitor_hash) n FROM %i WHERE occurred_at >= %s GROUP BY path ORDER BY n DESC LIMIT 6",
					$events,
				$win5
			)
		);
		$sources = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT source label, COUNT(DISTINCT visitor_hash) n FROM %i WHERE occurred_at >= %s GROUP BY source ORDER BY n DESC LIMIT 5",
					$events,
				$win5
			)
		);

		$out = array(
			'now'     => $now,
			'last30'  => $last30,
			'pages'   => array_map( fn( $r ) => array( 'label' => $r->label, 'n' => (int) $r->n ), (array) $pages ),
			'sources' => array_map( fn( $r ) => array( 'label' => $r->label, 'n' => (int) $r->n ), (array) $sources ),
		);

		wp_cache_set( 'realtime', $out, 'defog', 10 );

		return $out;
	}

	/* --------------------------------------------------------------------- */
	/* E-commerce (WooCommerce)                                                */
	/* --------------------------------------------------------------------- */

	/**
	 * Full e-commerce block for a range, or null when WooCommerce is inactive.
	 *
	 * @param string $range Range key.
	 * @return array|null
	 */
	public function ecommerce( $range ) {
		if ( ! class_exists( 'WooCommerce' ) ) {
			return null;
		}
		global $wpdb;
		$orders = defog_table( 'orders' );
		$items  = defog_table( 'order_items' );
		$r      = $this->range_dates( $range );

		$start  = $r['start'] . ' 00:00:00';
		$end    = $r['end'] . ' 23:59:59';
		$pstart = $r['prev_start'] . ' 00:00:00';
		$pend   = $r['prev_end'] . ' 23:59:59';

		$cur  = $this->sum_orders( $start, $end );
		$prev = $this->sum_orders( $pstart, $pend );

		$visitors  = $this->sum_kpis( $r['start'], $r['end'] )['visitors'];
		$pvisitors = $this->sum_kpis( $r['prev_start'], $r['prev_end'] )['visitors'];

		$aov  = $cur['orders'] ? $cur['revenue'] / $cur['orders'] : 0;
		$paov = $prev['orders'] ? $prev['revenue'] / $prev['orders'] : 0;
		$cr   = $visitors ? $cur['orders'] / $visitors * 100 : 0;
		$pcr  = $pvisitors ? $prev['orders'] / $pvisitors * 100 : 0;

		$ch_rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT source, SUM(total) rev, COUNT(*) o FROM %i WHERE occurred_at BETWEEN %s AND %s GROUP BY source ORDER BY rev DESC",
					$orders,
				$start,
				$end
			)
		);
		$channels = array_map(
			function ( $x ) {
				return array(
					'label'   => $x->source,
					'revenue' => (float) $x->rev,
					'orders'  => (int) $x->o,
				);
			},
			(array) $ch_rows
		);

		$pr_rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT product_id, MAX(name) name, SUM(qty) units, SUM(total) rev FROM %i WHERE occurred_at BETWEEN %s AND %s GROUP BY product_id ORDER BY rev DESC LIMIT 6",
					$items,
				$start,
				$end
			)
		);
		$products = array_map(
			function ( $x ) {
				return array(
					'name'    => $x->name,
					'units'   => (int) $x->units,
					'revenue' => (float) $x->rev,
				);
			},
			(array) $pr_rows
		);

		return array(
			'currency' => function_exists( 'get_woocommerce_currency' ) ? get_woocommerce_currency() : 'USD',
			'kpis'     => array(
				'revenue' => array(
					'val' => round( $cur['revenue'] ),
					'd'   => $this->pct( $cur['revenue'], $prev['revenue'] ),
				),
				'orders'  => array(
					'val' => $cur['orders'],
					'd'   => $this->pct( $cur['orders'], $prev['orders'] ),
				),
				'cr'      => array(
					'val' => round( $cr, 2 ),
					'd'   => round( $cr - $pcr, 2 ),
				),
				'aov'     => array(
					'val' => round( $aov ),
					'd'   => $this->pct( $aov, $paov ),
				),
			),
			'channels' => $channels,
			'products' => $products,
			'series'   => $this->ecom_series( $range ),
			'funnel'   => $this->funnel( $range ),
		);
	}

	/**
	 * Conversion funnel stages for a range (distinct visitors per stage).
	 *
	 * @param string $range Range key.
	 * @return array[]
	 */
	public function funnel( $range ) {
		global $wpdb;
		$events = defog_table( 'events' );
		$fn     = defog_table( 'funnel' );
		$r      = $this->range_dates( $range );

		$visitors = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(DISTINCT visitor_hash) FROM %i WHERE occurred_at BETWEEN %s AND %s",
					$events,
				$r['start'] . ' 00:00:00',
				$r['end'] . ' 23:59:59'
			)
		);

		$stage = function ( $s ) use ( $wpdb, $fn, $r ) {
			return (int) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT COUNT(DISTINCT visitor_hash) FROM %i WHERE stage = %s AND day BETWEEN %s AND %s",
					$fn,
					$s,
					$r['start'],
					$r['end']
				)
			);
		};

		return array(
			array( 'key' => 'visitors', 'count' => $visitors ),
			array( 'key' => 'product', 'count' => $stage( 'product' ) ),
			array( 'key' => 'cart', 'count' => $stage( 'cart' ) ),
			array( 'key' => 'checkout', 'count' => $stage( 'checkout' ) ),
			array( 'key' => 'purchase', 'count' => $stage( 'purchase' ) ),
		);
	}

	/**
	 * Sum revenue + order count over a datetime window.
	 *
	 * @param string $from mysql datetime.
	 * @param string $to   mysql datetime.
	 * @return array{revenue:float,orders:int}
	 */
	private function sum_orders( $from, $to ) {
		global $wpdb;
		$orders = defog_table( 'orders' );
		$row    = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT COALESCE(SUM(total),0) rev, COUNT(*) o FROM %i WHERE occurred_at BETWEEN %s AND %s",
					$orders,
				$from,
				$to
			),
			ARRAY_A
		);
		return array(
			'revenue' => (float) $row['rev'],
			'orders'  => (int) $row['o'],
		);
	}

	/**
	 * Revenue/orders series (current + previous) for the sales chart.
	 *
	 * @param string $range Range key.
	 * @return array
	 */
	private function ecom_series( $range ) {
		$r = $this->range_dates( $range );

		if ( 'hour' === $r['granularity'] ) {
			$labels = array();
			for ( $h = 0; $h < 24; $h++ ) {
				$labels[] = sprintf( '%02d:00', $h );
			}
			$cur  = $this->ecom_hourly( $r['start'] );
			$prev = $this->ecom_hourly( $r['prev_start'] );
			return array(
				'labels'       => $labels,
				'revenue'      => $cur['revenue'],
				'orders'       => $cur['orders'],
				'prevRevenue'  => $prev['revenue'],
				'prevOrders'   => $prev['orders'],
			);
		}

		$cur_dates  = $this->date_list( $r['start'], $r['end'] );
		$prev_dates = $this->date_list( $r['prev_start'], $r['prev_end'] );
		$cur_map    = $this->ecom_daily_map( $r['start'], $r['end'] );
		$prev_map   = $this->ecom_daily_map( $r['prev_start'], $r['prev_end'] );

		$revenue     = array();
		$ordersc     = array();
		$prevRevenue = array();
		$prevOrders  = array();
		foreach ( $cur_dates as $d ) {
			$revenue[] = isset( $cur_map[ $d ] ) ? $cur_map[ $d ]['rev'] : 0;
			$ordersc[] = isset( $cur_map[ $d ] ) ? $cur_map[ $d ]['o'] : 0;
		}
		foreach ( $prev_dates as $d ) {
			$prevRevenue[] = isset( $prev_map[ $d ] ) ? $prev_map[ $d ]['rev'] : 0;
			$prevOrders[]  = isset( $prev_map[ $d ] ) ? $prev_map[ $d ]['o'] : 0;
		}

		return array(
			'labels'      => array_map(
				function ( $d ) {
					return wp_date( 'M j', strtotime( $d ) );
				},
				$cur_dates
			),
			'revenue'     => $revenue,
			'orders'      => $ordersc,
			'prevRevenue' => $prevRevenue,
			'prevOrders'  => $prevOrders,
		);
	}

	/**
	 * Daily revenue/order map for a date range.
	 *
	 * @param string $from Y-m-d.
	 * @param string $to   Y-m-d.
	 * @return array
	 */
	private function ecom_daily_map( $from, $to ) {
		global $wpdb;
		$orders = defog_table( 'orders' );
		$rows   = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT DATE(occurred_at) d, SUM(total) rev, COUNT(*) o FROM %i WHERE occurred_at BETWEEN %s AND %s GROUP BY d",
					$orders,
				$from . ' 00:00:00',
				$to . ' 23:59:59'
			)
		);
		$map = array();
		foreach ( (array) $rows as $row ) {
			$map[ $row->d ] = array(
				'rev' => (float) $row->rev,
				'o'   => (int) $row->o,
			);
		}
		return $map;
	}

	/**
	 * 24-bucket hourly revenue/orders for one day.
	 *
	 * @param string $date Y-m-d.
	 * @return array{revenue:int[],orders:int[]}
	 */
	private function ecom_hourly( $date ) {
		global $wpdb;
		$orders = defog_table( 'orders' );
		$rows   = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT HOUR(occurred_at) h, SUM(total) rev, COUNT(*) o FROM %i WHERE occurred_at BETWEEN %s AND %s GROUP BY h",
					$orders,
				$date . ' 00:00:00',
				$date . ' 23:59:59'
			)
		);
		$rev = array_fill( 0, 24, 0 );
		$ord = array_fill( 0, 24, 0 );
		foreach ( (array) $rows as $row ) {
			$rev[ (int) $row->h ] = (float) $row->rev;
			$ord[ (int) $row->h ] = (int) $row->o;
		}
		return array(
			'revenue' => $rev,
			'orders'  => $ord,
		);
	}

	/* --------------------------------------------------------------------- */
	/* Page detail (drill-down)                                                */
	/* --------------------------------------------------------------------- */

	/**
	 * Behavioural detail for a single page over a range.
	 *
	 * @param string $range Range key.
	 * @param string $path  Page path.
	 * @return array
	 */
	public function page_detail( $range, $path ) {
		global $wpdb;
		$events = defog_table( 'events' );
		$r      = $this->range_dates( $range );
		$start  = $r['start'] . ' 00:00:00';
		$end    = $r['end'] . ' 23:59:59';

		$row = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT
					COUNT(*) pv,
					COUNT(DISTINCT visitor_hash) uniq,
					COALESCE(SUM(is_new_session),0) entrances,
					COALESCE(SUM(is_exit),0) exits,
					COALESCE(SUM(CASE WHEN is_new_session = 1 AND is_exit = 1 THEN 1 ELSE 0 END),0) bounces,
					COALESCE(AVG(NULLIF(engaged,0)),0) avg_time
				 FROM %i WHERE path = %s AND occurred_at BETWEEN %s AND %s",
					$events,
				$path,
				$start,
				$end
			),
			ARRAY_A
		);

		$pv        = (int) $row['pv'];
		$entrances = (int) $row['entrances'];
		$exits     = (int) $row['exits'];
		$bounces   = (int) $row['bounces'];

		$sources = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT source label, COUNT(DISTINCT visitor_hash) n FROM %i WHERE path = %s AND occurred_at BETWEEN %s AND %s GROUP BY source ORDER BY n DESC LIMIT 6",
					$events,
				$path,
				$start,
				$end
			)
		);
		$devices = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT device label, COUNT(*) n FROM %i WHERE path = %s AND occurred_at BETWEEN %s AND %s GROUP BY device ORDER BY n DESC",
					$events,
				$path,
				$start,
				$end
			)
		);

		return array(
			'path'      => $path,
			'pageviews' => $pv,
			'visitors'  => (int) $row['uniq'],
			'avgTime'   => (int) round( (float) $row['avg_time'] ),
			'bounce'    => $entrances ? round( $bounces / $entrances * 100, 1 ) : 0,
			'entry'     => $pv ? round( $entrances / $pv * 100, 1 ) : 0,
			'exit'      => $pv ? round( $exits / $pv * 100, 1 ) : 0,
			'sources'   => array_map(
				function ( $x ) {
					return array( 'label' => $x->label, 'visitors' => (int) $x->n );
				},
				(array) $sources
			),
			'devices'   => array_map(
				function ( $x ) {
					return array( 'label' => $x->label, 'count' => (int) $x->n );
				},
				(array) $devices
			),
			'series'    => $this->page_series( $range, $path ),
		);
	}

	/**
	 * Views-over-time series for a single page.
	 *
	 * @param string $range Range key.
	 * @param string $path  Page path.
	 * @return array{labels:array,values:array}
	 */
	private function page_series( $range, $path ) {
		global $wpdb;
		$events = defog_table( 'events' );
		$r      = $this->range_dates( $range );

		if ( 'hour' === $r['granularity'] ) {
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT HOUR(occurred_at) k, COUNT(*) v FROM %i WHERE path = %s AND occurred_at BETWEEN %s AND %s GROUP BY k",
					$events,
					$path,
					$r['start'] . ' 00:00:00',
					$r['start'] . ' 23:59:59'
				)
			);
			$buckets = array_fill( 0, 24, 0 );
			foreach ( (array) $rows as $row ) {
				$buckets[ (int) $row->k ] = (int) $row->v;
			}
			$labels = array();
			for ( $h = 0; $h < 24; $h++ ) {
				$labels[] = sprintf( '%02d:00', $h );
			}
			return array( 'labels' => $labels, 'values' => $buckets );
		}

		$dates = $this->date_list( $r['start'], $r['end'] );
		$rows  = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT DATE(occurred_at) k, COUNT(*) v FROM %i WHERE path = %s AND occurred_at BETWEEN %s AND %s GROUP BY k",
					$events,
				$path,
				$r['start'] . ' 00:00:00',
				$r['end'] . ' 23:59:59'
			)
		);
		$map = array();
		foreach ( (array) $rows as $row ) {
			$map[ $row->k ] = (int) $row->v;
		}
		$values = array();
		$labels = array();
		foreach ( $dates as $d ) {
			$values[] = isset( $map[ $d ] ) ? $map[ $d ] : 0;
			$labels[] = wp_date( 'M j', strtotime( $d ) );
		}
		return array( 'labels' => $labels, 'values' => $values );
	}

	/* --------------------------------------------------------------------- */
	/* Utilities                                                               */
	/* --------------------------------------------------------------------- */

	/**
	 * Local "now minus N seconds" as a mysql string matching stored times.
	 */
	private function ago( $seconds ) {
		return gmdate( 'Y-m-d H:i:s', current_time( 'timestamp' ) - $seconds );
	}

	/**
	 * Inclusive list of Y-m-d dates between two dates.
	 *
	 * @return string[]
	 */
	private function date_list( $from, $to ) {
		$out = array();
		$cur = strtotime( $from );
		$end = strtotime( $to );
		while ( $cur <= $end ) {
			$out[] = wp_date( 'Y-m-d', $cur );
			$cur   = strtotime( '+1 day', $cur );
		}
		return $out;
	}

	/**
	 * Percentage change guarded against divide-by-zero.
	 */
	private function pct( $cur, $prev ) {
		if ( $prev <= 0 ) {
			return $cur > 0 ? 100.0 : 0.0;
		}
		return round( ( $cur - $prev ) / $prev * 100, 1 );
	}
}
