<?php

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

class AIEO_Page_Occupancy {

	
	const ARRIVAL_COALESCE_MS = 300;

	
	const TERM_UNTERMINATED = 0;
	const TERM_PAGE_EXIT    = 1;
	const TERM_NEXT_ARRIVAL = 2;

	
	const PARKED_ENGAGED_MAX_MS    = 5000;
	const PARKED_AWAY_MIN_MS = 5000;

	
	const CLOCK_FLOOR_MS   = 1577836800000; 
	const CLOCK_FUTURE_MS  = 172800000;     

	
	const VISIT_IDLE_MS = 7200000; 

	
	const TMP_SESS = 'aieo_tmp_occ_sess';
	const TMP_EV   = 'aieo_tmp_occ_ev';
	const TMP_EV2  = 'aieo_tmp_occ_ev2';
	const TMP_EVM  = 'aieo_tmp_occ_evm';
	const TMP_OCC  = 'aieo_tmp_occ';
	const TMP_NEXT = 'aieo_tmp_occ_next';
	const TMP_HID  = 'aieo_tmp_occ_hid';

	
	public static function enabled() {
		return (bool) apply_filters( 'aieo_page_occupancy_enabled', true );
	}

	private static function table_exists( $table ) {
		global $wpdb;
		return (string) $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) === $table;
	}

	
	public static function arrival_types() {
		return apply_filters( 'aieo_occupancy_arrival_types', array( 'page_view', 'product_view', 'category_view' ) );
	}

	public static function interaction_types() {
		return apply_filters( 'aieo_occupancy_interaction_types', array(
			'scroll_depth',
			'click',
			'product_image_click',
			'add_to_cart',
			'search',
			'chat_open',
			'chat_message',
			'purchase_complete',
		) );
	}

	public static function departure_types() {
		return apply_filters( 'aieo_occupancy_departure_types', array( 'page_exit' ) );
	}

	
	public static function location_agnostic_types() {
		return apply_filters( 'aieo_occupancy_location_agnostic_types', array( 'search' ) );
	}

	
	public static function page_match_mode() {
		$m = (string) apply_filters( 'aieo_occupancy_page_match', 'path' );
		return in_array( $m, array( 'path', 'path_query', 'none' ), true ) ? $m : 'path';
	}

	
	public static function visit_idle_ms() {
		$ms = (int) apply_filters( 'aieo_occupancy_visit_idle_ms', self::VISIT_IDLE_MS );
		return $ms > 0 ? $ms : 0;
	}

	
	private static function in_list( array $types ) {
		global $wpdb;
		$out = array();
		foreach ( $types as $t ) {
			$out[] = "'" . esc_sql( (string) $t ) . "'";
		}
		return implode( ',', $out );
	}

	
	public static function supports_window_functions() {
		global $wpdb;
		$info = (string) $wpdb->db_server_info();
		if ( false !== stripos( $info, 'mariadb' ) ) {
			if ( preg_match( '/(\d+)\.(\d+)/', $info, $m ) ) {
				return ( (int) $m[1] > 10 ) || ( 10 === (int) $m[1] && (int) $m[2] >= 2 );
			}
			return false;
		}
		return version_compare( (string) $wpdb->db_version(), '8.0', '>=' );
	}

	
	
	

	
	public static function build( $source_table, $start_ms, $end_ms ) {
		global $wpdb;

		if ( ! self::enabled() || ! self::supports_window_functions() ) {
			return '';
		}
		if ( ! self::table_exists( $source_table ) ) {
			return '';
		}

		$p     = $wpdb->prefix;
		$sess  = $p . self::TMP_SESS;
		$ev    = $p . self::TMP_EV;
		$ev2   = $p . self::TMP_EV2;
		$evm   = $p . self::TMP_EVM;
		$occ   = $p . self::TMP_OCC;
		$nxt   = $p . self::TMP_NEXT;
		$hid   = $p . self::TMP_HID;

		self::drop_temp();

		
		
		
		$cc = function_exists( 'aieo_column_charset_collate' ) ? aieo_column_charset_collate( $source_table, 'session_id' ) : '';

		$arrivals     = self::in_list( self::arrival_types() );
		$interactions = self::in_list( self::interaction_types() );
		$departures   = self::in_list( self::departure_types() );
		$loose        = self::in_list( self::location_agnostic_types() );
		if ( '' === $loose ) {
			$loose = "''";
		}

		$clock_lo = (int) self::CLOCK_FLOOR_MS;
		$clock_hi = ( time() * 1000 ) + (int) self::CLOCK_FUTURE_MS;

		$start_ms = (int) $start_ms;
		$end_ms   = (int) $end_ms;

		
		$wpdb->query( "CREATE TEMPORARY TABLE `{$sess}` (
			session_id VARCHAR(190) NOT NULL,
			PRIMARY KEY (session_id)
		) ENGINE=InnoDB {$cc}" );
		$wpdb->query( $wpdb->prepare(
			"INSERT IGNORE INTO `{$sess}` (session_id)
			 SELECT DISTINCT LEFT(session_id, 190)
			   FROM `{$source_table}`
			  WHERE event_type IN ({$arrivals})
				AND timestamp BETWEEN %d AND %d",
			$start_ms,
			$end_ms
		) );

		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		$wpdb->query( "CREATE TEMPORARY TABLE `{$ev}` (
			eid BIGINT UNSIGNED NOT NULL,
			session_id VARCHAR(190) NOT NULL,
			ts BIGINT NOT NULL,
			klass TINYINT UNSIGNED NOT NULL,
			is_loose TINYINT UNSIGNED NOT NULL DEFAULT 0,
			page_url VARCHAR(512) NOT NULL DEFAULT '',
			url_nf VARCHAR(512) NOT NULL DEFAULT '',
			url_path VARCHAR(320) NOT NULL DEFAULT '',
			product_id BIGINT UNSIGNED NULL,
			is_product TINYINT UNSIGNED NOT NULL DEFAULT 0,
			is_category TINYINT UNSIGNED NOT NULL DEFAULT 0,
			scroll_depth SMALLINT UNSIGNED NOT NULL DEFAULT 0,
			click_count SMALLINT UNSIGNED NOT NULL DEFAULT 0,
			PRIMARY KEY (eid),
			KEY k_sess (session_id, ts, eid)
		) ENGINE=InnoDB {$cc}" );

		
		
		$nf   = "IF( u.nf = '', '/', u.nf )";
		$bare = "SUBSTRING_INDEX( {$nf}, '?', 1 )";
		$path = "IF( CHAR_LENGTH( {$bare} ) > 1 AND RIGHT( {$bare}, 1 ) = '/',
					 LEFT( {$bare}, CHAR_LENGTH( {$bare} ) - 1 ), {$bare} )";

		$wpdb->query( $wpdb->prepare(
			"INSERT INTO `{$ev}` (eid, session_id, ts, klass, is_loose, page_url, url_nf, url_path, product_id, is_product, is_category, scroll_depth, click_count)
			 SELECT u.id, u.sid, u.ts, u.klass, u.is_loose, u.raw,
					{$nf},
					{$path},
					u.product_id, u.is_product, u.is_category, u.scroll_depth, u.click_count
			   FROM (
				SELECT s.id,
					   LEFT( s.session_id, 190 ) AS sid,
					   s.timestamp AS ts,
					   CASE
						   WHEN s.event_type IN ({$arrivals})     THEN 1
						   WHEN s.event_type IN ({$interactions}) THEN 2
						   WHEN s.event_type IN ({$departures})   THEN 3
						   WHEN s.event_type = 'tab_hidden'       THEN 4
						   WHEN s.event_type = 'tab_visible'      THEN 5
						   ELSE 6
					   END AS klass,
					   CASE WHEN s.event_type IN ({$loose}) THEN 1 ELSE 0 END AS is_loose,
					   LEFT( COALESCE( s.page_url, '' ), 512 ) AS raw,
					   REGEXP_REPLACE(
						   SUBSTRING_INDEX( LEFT( COALESCE( s.page_url, '' ), 512 ), '#', 1 ),
						   '^https?://[^/]+', '' ) AS nf,
					   s.product_id,
					   CASE WHEN s.event_type = 'product_view'  THEN 1 ELSE 0 END AS is_product,
					   CASE WHEN s.event_type = 'category_view' THEN 1 ELSE 0 END AS is_category,
					   COALESCE( s.scroll_depth, 0 ) AS scroll_depth,
					   COALESCE( s.click_count, 0 )  AS click_count
				  FROM `{$source_table}` s
				  JOIN `{$sess}` z ON z.session_id = LEFT( s.session_id, 190 )
				 WHERE s.timestamp BETWEEN %d AND %d
			   ) u",
			$clock_lo,
			$clock_hi
		) );

		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		$coalesce = (int) self::ARRIVAL_COALESCE_MS;
		$idle     = self::visit_idle_ms();
		$visit_flag = $idle > 0
			? "CASE WHEN prev_ts IS NULL OR ( ts - prev_ts ) > {$idle} THEN 1 ELSE 0 END"
			: "CASE WHEN prev_ts IS NULL THEN 1 ELSE 0 END";

		$wpdb->query( "CREATE TEMPORARY TABLE `{$ev2}` (
			PRIMARY KEY (eid),
			KEY k_occ (session_id, occ_rn, ts)
		) ENGINE=InnoDB {$cc} AS
		SELECT eid, session_id, ts, klass, is_loose, page_url, url_nf, url_path,
			   product_id, is_product, is_category, scroll_depth, click_count,
			   is_occ_start, rn, visit_rn, occ_rn,
			   MAX( CASE WHEN is_occ_start = 1 THEN url_path END )
				 OVER ( PARTITION BY session_id, visit_rn, occ_rn ) AS occ_path,
			   MAX( CASE WHEN is_occ_start = 1 THEN url_nf END )
				 OVER ( PARTITION BY session_id, visit_rn, occ_rn ) AS occ_nf
		  FROM (
			SELECT eid, session_id, ts, klass, is_loose, page_url, url_nf, url_path,
				   product_id, is_product, is_category, scroll_depth, click_count,
				   is_occ_start, rn, visit_rn,
				   MAX( CASE WHEN is_occ_start = 1 THEN rn END )
					 OVER ( PARTITION BY session_id, visit_rn ORDER BY rn
							ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS occ_rn
			  FROM (
				SELECT eid, session_id, ts, klass, is_loose, page_url, url_nf, url_path,
					   product_id, is_product, is_category, scroll_depth, click_count,
					   is_occ_start, rn,
					   MAX( CASE WHEN is_visit_start = 1 THEN rn END )
						 OVER ( PARTITION BY session_id ORDER BY rn
								ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS visit_rn
				  FROM (
					SELECT eid, session_id, ts, klass, is_loose, page_url, url_nf, url_path,
						   product_id, is_product, is_category, scroll_depth, click_count, rn,
						   {$visit_flag} AS is_visit_start,
						   CASE
							   WHEN klass <> 1 THEN 0
							   WHEN prev_arr_ts IS NULL THEN 1
							   WHEN prev_arr_url <> url_nf THEN 1
							   WHEN ( ts - prev_arr_ts ) > {$coalesce} THEN 1
							   ELSE 0
						   END AS is_occ_start
					  FROM (
						SELECT eid, session_id, ts, klass, is_loose, page_url, url_nf, url_path,
							   product_id, is_product, is_category, scroll_depth, click_count,
							   ROW_NUMBER() OVER ( PARTITION BY session_id ORDER BY ts, eid ) AS rn,
							   LAG( ts )     OVER ( PARTITION BY session_id ORDER BY ts, eid ) AS prev_ts,
							   LAG( ts )     OVER ( PARTITION BY session_id, klass ORDER BY ts, eid ) AS prev_arr_ts,
							   LAG( url_nf ) OVER ( PARTITION BY session_id, klass ORDER BY ts, eid ) AS prev_arr_url
						  FROM `{$ev}`
					  ) ranked
				  ) flagged
			  ) visited
		  ) numbered" );

		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		$mode = self::page_match_mode();
		if ( 'none' === $mode ) {
			$match = '1 = 1';
		} else {
			$cmp   = ( 'path_query' === $mode ) ? 'url_nf = occ_nf' : 'url_path = occ_path';
			$match = "( is_occ_start = 1 OR is_loose = 1 OR {$cmp} )";
		}
		$wpdb->query( "CREATE TEMPORARY TABLE `{$evm}` (
			PRIMARY KEY (eid),
			KEY k_occ (session_id, occ_rn, ts)
		) ENGINE=InnoDB {$cc} AS
		SELECT eid, session_id, ts, klass, is_occ_start, visit_rn, occ_rn,
			   page_url, url_nf, url_path,
			   product_id, is_product, is_category, scroll_depth, click_count,
			   MIN( CASE WHEN klass = 2 THEN ts END )
				 OVER ( PARTITION BY session_id, occ_rn ORDER BY ts, eid
						ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING ) AS next_int_ts
		  FROM `{$ev2}`
		 WHERE occ_rn IS NOT NULL AND {$match}" );
		$wpdb->query( "ALTER TABLE `{$evm}` MODIFY next_int_ts BIGINT NULL" );

		
		
		
		
		$wpdb->query( "CREATE TEMPORARY TABLE `{$occ}` (
			PRIMARY KEY (session_id, occ_rn),
			KEY k_prod (product_id, arrival_ts)
		) ENGINE=InnoDB {$cc} AS
		SELECT session_id, occ_rn,
			   MIN( CASE WHEN is_occ_start = 1 THEN eid END )      AS occ_eid,
			   MIN( visit_rn )                                     AS visit_rn,
			   MIN( CASE WHEN is_occ_start = 1 THEN page_url END ) AS page_url,
			   MIN( CASE WHEN is_occ_start = 1 THEN url_path END ) AS page_path,
			   MAX( CASE WHEN klass = 1 THEN product_id END )      AS product_id,
			   MAX( is_product )                                   AS is_product,
			   MAX( is_category )                                  AS is_category,
			   MIN( CASE WHEN klass = 1 THEN ts END )              AS arrival_ts,
			   SUM( klass = 2 )                                    AS n_int,
			   MAX( CASE WHEN klass = 2 THEN ts END )              AS last_int_ts,
			   SUM( klass = 3 )                                    AS n_exit,
			   MAX( CASE WHEN klass = 3 THEN ts END )              AS last_exit_ts,
			   SUM( klass = 4 )                                    AS n_hidden,
			   SUM( klass = 5 )                                    AS n_visible,
			   MAX( ts )                                           AS last_any_ts,
			   MAX( scroll_depth )                                 AS max_scroll,
			   MAX( click_count )                                  AS max_clicks,
			   CAST( 0 AS SIGNED )  AS next_arrival_ts,
			   CAST( 0 AS SIGNED )  AS last_action_ts,
			   CAST( 0 AS SIGNED )  AS occ_end_ts,
			   CAST( 0 AS SIGNED )  AS hidden_in_span_ms,
			   CAST( 0 AS SIGNED )  AS engaged_ms,
			   CAST( 0 AS SIGNED )  AS away_ms,
			   CAST( 0 AS SIGNED )  AS away_floor_ms,
			   CAST( 0 AS SIGNED )  AS terminator
		  FROM `{$evm}`
		 GROUP BY session_id, occ_rn" );

		
		
		$wpdb->query( "ALTER TABLE `{$occ}`
			MODIFY next_arrival_ts     BIGINT NOT NULL DEFAULT 0,
			MODIFY last_action_ts      BIGINT NOT NULL DEFAULT 0,
			MODIFY occ_end_ts          BIGINT NOT NULL DEFAULT 0,
			MODIFY hidden_in_span_ms   BIGINT NOT NULL DEFAULT 0,
			MODIFY engaged_ms          BIGINT NOT NULL DEFAULT 0,
			MODIFY away_ms       BIGINT NULL DEFAULT NULL,
			MODIFY away_floor_ms BIGINT NOT NULL DEFAULT 0,
			MODIFY terminator          TINYINT UNSIGNED NOT NULL DEFAULT 0" );

		
		
		
		
		
		
		
		
		
		
		
		$wpdb->query( "CREATE TEMPORARY TABLE `{$nxt}` (
			PRIMARY KEY (session_id, occ_rn)
		) ENGINE=InnoDB {$cc} AS
		SELECT session_id, occ_rn,
			   LEAD( arrival_ts ) OVER ( PARTITION BY session_id, visit_rn ORDER BY occ_rn ) AS nxt_ts
		  FROM `{$occ}`" );
		$wpdb->query( "ALTER TABLE `{$nxt}` MODIFY nxt_ts BIGINT NULL" );
		$wpdb->query( "UPDATE `{$occ}` o JOIN `{$nxt}` n
						  ON n.session_id = o.session_id AND n.occ_rn = o.occ_rn
						 SET o.next_arrival_ts = COALESCE( n.nxt_ts, 0 )" );

		
		
		
		
		
		
		
		$wpdb->query( "UPDATE `{$occ}` SET last_action_ts = GREATEST(
			arrival_ts,
			COALESCE( last_int_ts,  arrival_ts ),
			COALESCE( last_exit_ts, arrival_ts )
		)" );
		$wpdb->query( $wpdb->prepare(
			"UPDATE `{$occ}` SET terminator = CASE
				WHEN n_exit > 0 AND last_exit_ts >= last_action_ts THEN %d
				WHEN next_arrival_ts > 0 AND next_arrival_ts >= last_action_ts THEN %d
				WHEN n_exit > 0 THEN %d
				WHEN next_arrival_ts > 0 THEN %d
				ELSE %d END",
			self::TERM_PAGE_EXIT,
			self::TERM_NEXT_ARRIVAL,
			self::TERM_PAGE_EXIT,
			self::TERM_NEXT_ARRIVAL,
			self::TERM_UNTERMINATED
		) );
		$wpdb->query( $wpdb->prepare(
			"UPDATE `{$occ}` SET occ_end_ts = CASE
				WHEN terminator = %d THEN GREATEST( last_exit_ts, last_action_ts )
				WHEN terminator = %d THEN GREATEST( next_arrival_ts, last_action_ts )
				ELSE 0 END",
			self::TERM_PAGE_EXIT,
			self::TERM_NEXT_ARRIVAL
		) );

		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		$wpdb->query( "CREATE TEMPORARY TABLE `{$hid}` (
			PRIMARY KEY (session_id, occ_rn)
		) ENGINE=InnoDB {$cc} AS
		SELECT b.session_id, b.occ_rn,
			   SUM( GREATEST( 0,
					LEAST(
						CASE
							WHEN b.nxt_klass = 5           THEN b.nxt_ts
							WHEN b.next_int_ts IS NOT NULL THEN b.next_int_ts
							ELSE o.last_action_ts
						END,
						o.last_action_ts )
					- GREATEST( b.ts, o.arrival_ts )
			   ) ) AS hid_ms
		  FROM (
			SELECT session_id, occ_rn, ts, eid, klass, next_int_ts,
				   LEAD( ts )    OVER w AS nxt_ts,
				   LEAD( klass ) OVER w AS nxt_klass
			  FROM (
				SELECT session_id, occ_rn, ts, eid, klass, next_int_ts,
					   LAG( klass ) OVER w2 AS prev_klass
				  FROM `{$evm}`
				 WHERE klass IN (4,5)
				WINDOW w2 AS ( PARTITION BY session_id, occ_rn ORDER BY ts, eid )
			  ) runs
			 WHERE runs.prev_klass IS NULL OR runs.prev_klass <> runs.klass
			WINDOW w AS ( PARTITION BY session_id, occ_rn ORDER BY ts, eid )
		  ) b
		  JOIN `{$occ}` o ON o.session_id = b.session_id AND o.occ_rn = b.occ_rn
		 WHERE b.klass = 4
		 GROUP BY b.session_id, b.occ_rn" );
		$wpdb->query( "ALTER TABLE `{$hid}` MODIFY hid_ms BIGINT NOT NULL DEFAULT 0" );
		$wpdb->query( "UPDATE `{$occ}` o JOIN `{$hid}` h
						  ON h.session_id = o.session_id AND h.occ_rn = o.occ_rn
						 SET o.hidden_in_span_ms = h.hid_ms" );

		
		
		
		
		
		$wpdb->query( "UPDATE `{$occ}` SET
			engaged_ms          = GREATEST( 0, ( last_action_ts - arrival_ts ) - hidden_in_span_ms ),
			away_ms       = CASE WHEN terminator > 0
									   THEN ( occ_end_ts - arrival_ts ) - GREATEST( 0, ( last_action_ts - arrival_ts ) - hidden_in_span_ms )
									   ELSE NULL END,
			away_floor_ms = hidden_in_span_ms + GREATEST( 0, last_any_ts - last_action_ts )" );

		return $occ;
	}

	
	public static function drop_temp() {
		global $wpdb;
		$p = $wpdb->prefix;
		foreach ( array( self::TMP_HID, self::TMP_NEXT, self::TMP_OCC, self::TMP_EVM, self::TMP_EV2, self::TMP_EV, self::TMP_SESS ) as $t ) {
			$wpdb->query( "DROP TEMPORARY TABLE IF EXISTS `{$p}{$t}`" );
		}
	}

	
	
	

	
	public static function compute_daily( $date, $source_table = null ) {
		global $wpdb;

		if ( ! self::enabled() ) {
			return 0;
		}
		if ( ! preg_match( '/^\d{4}-\d{2}-\d{2}$/', (string) $date ) ) {
			return 0;
		}

		$target = $wpdb->prefix . 'aieo_engagement_daily';
		if ( ! self::table_exists( $target ) || ! self::has_occupancy_columns() ) {
			return 0;
		}
		if ( null === $source_table ) {
			$source_table = $wpdb->prefix . 'aieo_sessions';
		}

		$start_ms = strtotime( $date . ' 00:00:00 UTC' ) * 1000;
		$end_ms   = strtotime( $date . ' 23:59:59 UTC' ) * 1000 + 999;

		$occ = self::build( $source_table, $start_ms, $end_ms );
		if ( '' === $occ ) {
			return 0;
		}

		$parked_e = (int) self::PARKED_ENGAGED_MAX_MS;
		$parked_h = (int) self::PARKED_AWAY_MIN_MS;

		$rows = $wpdb->query( $wpdb->prepare(
			"INSERT INTO `{$target}`
				(date, product_id, occupancies, avg_engaged_sec, terminated_occupancies,
				 avg_away_sec, unterminated_occupancies, parked_occupancies, avg_hidden_sec)
			 SELECT %s,
					o.product_id,
					COUNT(*),
					AVG( o.engaged_ms ) / 1000,
					SUM( o.terminator > 0 ),
					COALESCE( AVG( o.away_ms ), 0 ) / 1000,
					SUM( o.terminator = 0 ),
					SUM( CASE WHEN o.engaged_ms <= %d AND o.away_ms >= %d THEN 1 ELSE 0 END ),
					-- Terminated-only, so it shares avg_away_sec's denominator
					-- and the documented off-screen-subtraction decomposition
					-- cannot go negative.
					COALESCE( AVG( CASE WHEN o.terminator > 0 THEN o.hidden_in_span_ms END ), 0 ) / 1000
			   FROM `{$occ}` o
			  WHERE o.product_id IS NOT NULL AND o.product_id > 0
				AND o.arrival_ts BETWEEN %d AND %d
			  GROUP BY o.product_id
			 ON DUPLICATE KEY UPDATE
				occupancies              = VALUES(occupancies),
				avg_engaged_sec          = VALUES(avg_engaged_sec),
				terminated_occupancies   = VALUES(terminated_occupancies),
				avg_away_sec       = VALUES(avg_away_sec),
				unterminated_occupancies = VALUES(unterminated_occupancies),
				parked_occupancies       = VALUES(parked_occupancies),
				avg_hidden_sec           = VALUES(avg_hidden_sec)",
			$date,
			$parked_e,
			$parked_h,
			$start_ms,
			$end_ms
		) );

		self::drop_temp();

		return (int) $rows;
	}

	
	private static function has_occupancy_columns() {
		global $wpdb;
		static $ok = null;
		if ( null !== $ok ) {
			return $ok;
		}
		$target = $wpdb->prefix . 'aieo_engagement_daily';
		$cols   = $wpdb->get_col( "SHOW COLUMNS FROM `{$target}`" );
		$ok     = is_array( $cols ) && in_array( 'avg_engaged_sec', $cols, true ) && in_array( 'avg_away_sec', $cols, true );
		return $ok;
	}

	
	
	

	
	public static function recompute_from_archive( $from_date, $to_date ) {
		global $wpdb;

		$out = array( 'days' => 0, 'rows' => 0, 'skipped' => 0 );

		$archive = $wpdb->prefix . 'aieo_sessions_archive';
		if ( ! self::table_exists( $archive ) ) {
			return $out;
		}
		$from = strtotime( $from_date . ' 00:00:00 UTC' );
		$to   = strtotime( $to_date . ' 00:00:00 UTC' );
		if ( ! $from || ! $to || $to < $from ) {
			return $out;
		}

		for ( $ts = $from; $ts <= $to; $ts += DAY_IN_SECONDS ) {
			$date = gmdate( 'Y-m-d', $ts );
			$n    = self::compute_daily( $date, $archive );
			$out['days']++;
			if ( $n > 0 ) {
				$out['rows'] += $n;
			} else {
				$out['skipped']++;
			}
		}

		return $out;
	}

	
	public static function archive_span() {
		global $wpdb;
		$archive = $wpdb->prefix . 'aieo_sessions_archive';
		if ( ! self::table_exists( $archive ) ) {
			return array();
		}
		$row = $wpdb->get_row( $wpdb->prepare(
			"SELECT MIN(timestamp) AS lo, MAX(timestamp) AS hi
			   FROM `{$archive}`
			  WHERE timestamp BETWEEN %d AND %d",
			(int) self::CLOCK_FLOOR_MS,
			( time() * 1000 ) + (int) self::CLOCK_FUTURE_MS
		) );
		if ( ! $row || ! $row->lo ) {
			return array();
		}
		return array( gmdate( 'Y-m-d', (int) ( $row->lo / 1000 ) ), gmdate( 'Y-m-d', (int) ( $row->hi / 1000 ) ) );
	}

	
	
	

	
	public static function summarise( $occ ) {
		global $wpdb;
		if ( '' === $occ ) {
			return array();
		}
		$row = $wpdb->get_row(
			"SELECT COUNT(*) AS occupancies,
					AVG( engaged_ms ) / 1000                       AS engaged_mean_sec,
					MAX( engaged_ms ) / 1000                       AS engaged_max_sec,
					SUM( engaged_ms ) / 1000                       AS engaged_pool_sec,
					SUM( engaged_ms = 0 )                          AS engaged_zero,
					SUM( engaged_ms > 600000 )                     AS engaged_over_10min,
					SUM( CASE WHEN engaged_ms > 600000 THEN engaged_ms ELSE 0 END ) / 1000
																   AS engaged_over_10min_pool_sec,
					SUM( terminator = 0 )                          AS unterminated,
					SUM( terminator = 1 )                          AS term_page_exit,
					SUM( terminator = 2 )                          AS term_next_arrival,
					AVG( away_ms ) / 1000                    AS hesitation_mean_sec,
					MAX( away_ms ) / 1000                    AS hesitation_max_sec,
					SUM( away_ms > 0 )                       AS hesitation_nonzero,
					SUM( engaged_ms = 0 AND away_ms > 0 )    AS parked_tabs,
					AVG( hidden_in_span_ms ) / 1000                AS hidden_mean_sec
			   FROM `{$occ}`",
			ARRAY_A
		);
		if ( ! is_array( $row ) ) {
			return array();
		}
		foreach ( array( 'engaged_ms' => 'engaged', 'away_ms' => 'hesitation' ) as $col => $label ) {
			foreach ( array( '50', '90', '99', '99.9' ) as $q ) {
				$key = $label . '_p' . str_replace( '.', '_', $q ) . '_sec';
				$row[ $key ] = (float) $wpdb->get_var(
					"SELECT PERCENTILE_CONT({$q}/100) WITHIN GROUP (ORDER BY {$col}) OVER () FROM `{$occ}` LIMIT 1"
				) / 1000;
			}
		}
		
		$row['engaged_hesitation_pearson'] = (float) $wpdb->get_var(
			"SELECT ( AVG(e*h) - AVG(e)*AVG(h) ) / NULLIF( STDDEV_POP(e) * STDDEV_POP(h), 0 )
			   FROM ( SELECT engaged_ms/1000 AS e, away_ms/1000 AS h FROM `{$occ}` WHERE terminator > 0 ) z"
		);
		return $row;
	}
}