<?php

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

class AIEO_Aggregate_Cron {

	const HOOK              = 'aieo_aggregate_rollups';
	const WEEKLY_BUCKET_OPT = 'aieo_aggregate_buckets_last_run';
	const TOP_PRODUCT_LIMIT = 200;

	
	const STATUS_OPTION = 'aieo_aggregate_last_run';

	
	const BLOG_PATH_LIMIT = 1500;

	
	const RUN_BUDGET_SECS = 900;

	
	const STEP_TIME_LIMIT = 300;

	
	private static $started_at = 0.0;

	
	private static $status = array();

	
	const RECO_ASSIST_WINDOW_DAYS = 7;

	
	const RECO_SURFACE_RECOMPUTE_DAYS = self::RECO_ASSIST_WINDOW_DAYS + 1;

	
	const RECO_SURFACE_GEN_OPTION = 'aieo_reco_surface_daily_gen';

	
	const MARKS_OPTION = 'aieo_aggregate_marks';

	
	const FOLD_STEPS = array(
		'engagement_daily'         => 0,
		'engagement_occupancy'     => 0,
		'attribution_daily'        => 0,
		'blog_engagement_daily'    => 0,
		'content_engagement_daily' => 0,
		'seo_cooccurrence'         => 7,
		'cart_removal_daily'       => 0,
		'reco_daily'               => 0,
		'reco_surface_daily'       => self::RECO_SURFACE_RECOMPUTE_DAYS,
		'reco_arm_daily'           => self::RECO_SURFACE_RECOMPUTE_DAYS,
		'reco_origin_daily'        => self::RECO_SURFACE_RECOMPUTE_DAYS,
		'landing_funnel_daily'     => 0,
		'landing_campaign_daily'   => 3,
		'entry_credit_daily'       => 7,
		'exit_daily'               => 0,
	);

	
	public static function folded_before() {
		$marks = (array) get_option( self::MARKS_OPTION, array() );
		$low   = '';
		foreach ( self::FOLD_STEPS as $step => $recompute ) {
			if ( empty( $marks[ $step ] ) || ! preg_match( '/^\d{4}-\d{2}-\d{2}$/', (string) $marks[ $step ] ) ) {
				return '';
			}
			$safe = gmdate( 'Y-m-d', strtotime( $marks[ $step ] . ' 00:00:00 UTC' ) - (int) $recompute * DAY_IN_SECONDS );
			$low  = ( '' === $low || $safe < $low ) ? $safe : $low;
		}
		return $low;
	}

	
	public static function fold_holders() {
		$marks = (array) get_option( self::MARKS_OPTION, array() );
		$low   = self::folded_before();
		$out   = array();
		foreach ( self::FOLD_STEPS as $step => $recompute ) {
			$mark = isset( $marks[ $step ] ) ? (string) $marks[ $step ] : '';
			if ( '' === $low ) {
				if ( '' === $mark ) {
					$out[ $step ] = ''; 
				}
			} elseif ( gmdate( 'Y-m-d', strtotime( $mark . ' 00:00:00 UTC' ) - (int) $recompute * DAY_IN_SECONDS ) === $low ) {
				$out[ $step ] = $mark; 
			}
		}
		return $out;
	}

	
	private static function record_mark( $name ) {
		$date = isset( self::$status['_date'] ) ? (string) self::$status['_date'] : '';
		if ( ! isset( self::FOLD_STEPS[ $name ] ) || ! preg_match( '/^\d{4}-\d{2}-\d{2}$/', $date ) ) {
			return;
		}
		$marks = (array) get_option( self::MARKS_OPTION, array() );
		if ( empty( $marks[ $name ] ) || (string) $marks[ $name ] < $date ) {
			$marks[ $name ] = $date;
			update_option( self::MARKS_OPTION, $marks, false );
		}
	}

	
	public static function maybe_schedule() {
		if ( wp_next_scheduled( self::HOOK ) ) { return; }
		$now      = current_time( 'timestamp', true );
		$next_run = strtotime( gmdate( 'Y-m-d', $now ) . ' 03:00:00 UTC' );
		if ( $next_run <= $now ) {
			$next_run = strtotime( '+1 day', $next_run );
		}
		wp_schedule_event( $next_run, 'daily', self::HOOK );
	}

	public static function unschedule() {
		$ts = wp_next_scheduled( self::HOOK );
		if ( $ts ) { wp_unschedule_event( $ts, self::HOOK ); }
	}

	
	public static function run( $date_arg = null ) {
		$date = self::resolve_date( $date_arg );
		aieo_debug_log( 'AIEO_Aggregate_Cron: starting for date ' . $date );

		self::$started_at = microtime( true );
		self::$status     = array( '_date' => $date, '_started' => gmdate( 'Y-m-d H:i:s' ) );

		$counts = array(
			'engagement_daily'        => 0,
			'engagement_occupancy'    => 0,
			'attribution_daily'       => 0,
			'blog_engagement_daily'   => 0,
			'content_engagement_daily' => 0,
			'content_vitals'          => 0,
			'product_lifetime'        => 0,
			'seo_cooccurrence'        => 0,
			'cart_removal_daily'      => 0,
			'reco_daily'              => 0,
			'reco_surface_daily'      => 0,
			'reco_arm_daily'          => 0,
			'reco_origin_daily'       => 0,
			'landing_funnel_daily'    => 0,
			'entry_credit_daily'      => 0,
			'conversion_buckets'      => 0,
		);

		$counts['engagement_daily'] = self::step( 'engagement_daily', static function () use ( $date ) {
			return self::compute_engagement_daily( $date );
		} );

		
		
		
		
		
		$counts['engagement_occupancy'] = self::step( 'engagement_occupancy', static function () use ( $date ) {
			if ( ! class_exists( 'AIEO_Page_Occupancy' ) ) {
				require_once AIEO_DIR . 'includes/class-aieo-page-occupancy.php';
			}
			return AIEO_Page_Occupancy::compute_daily( $date );
		} );

		$counts['attribution_daily'] = self::step( 'attribution_daily', static function () use ( $date ) {
			return self::compute_attribution_daily( $date );
		} );
		$counts['blog_engagement_daily'] = self::step( 'blog_engagement_daily', static function () use ( $date ) {
			return self::compute_blog_engagement_daily( $date );
		} );

		
		
		
		
		
		$counts['content_engagement_daily'] = self::step( 'content_engagement_daily', static function () use ( $date ) {
			if ( ! class_exists( 'AIEO_Content_Vitals' ) ) {
				require_once AIEO_DIR . 'includes/class-aieo-content-vitals.php';
			}
			return AIEO_Content_Vitals::compute_daily( $date );
		} );
		$counts['content_vitals'] = self::step( 'content_vitals', static function () {
			if ( ! class_exists( 'AIEO_Content_Vitals' ) ) {
				require_once AIEO_DIR . 'includes/class-aieo-content-vitals.php';
			}
			$cv = AIEO_Content_Vitals::build_vitals();
			return (int) ( $cv['rows'] ?? 0 );
		} );
		
		
		
		$counts['product_lifetime'] = self::step( 'product_lifetime', static function () {
			if ( ! class_exists( 'AIEO_Product_Lifetime' ) ) {
				require_once AIEO_DIR . 'includes/class-aieo-product-lifetime.php';
			}
			return AIEO_Product_Lifetime::rebuild();
		} );

		
		
		
		
		
		
		$counts['seo_cooccurrence'] = self::step( 'seo_cooccurrence', static function () use ( $date ) {
			if ( ! class_exists( 'AIEO_SEO_Cooccurrence' ) ) {
				
				
				
				require_once AIEO_DIR . 'includes/seo/class-aieo-seo-cooccurrence.php';
			}
			return AIEO_SEO_Cooccurrence::compute_window( $date );
		} );

		$counts['cart_removal_daily'] = self::step( 'cart_removal_daily', static function () use ( $date ) {
			return self::compute_cart_removal_daily( $date );
		} );
		$counts['reco_daily'] = self::step( 'reco_daily', static function () use ( $date ) {
			return self::compute_reco_daily( $date );
		} );

		
		
		
		
		
		
		
		$counts['reco_surface_daily'] = self::step( 'reco_surface_daily', static function () use ( $date ) {
			$rsd_ts  = strtotime( $date . ' 12:00:00 UTC' );
			$written = 0;
			for ( $i = 0; $i < self::RECO_SURFACE_RECOMPUTE_DAYS; $i++ ) {
				$written += self::compute_reco_surface_daily( gmdate( 'Y-m-d', $rsd_ts - $i * DAY_IN_SECONDS ) );
			}
			return $written;
		} );

		
		
		
		
		
		
		$counts['reco_arm_daily'] = self::step( 'reco_arm_daily', static function () use ( $date ) {
			$ts      = strtotime( $date . ' 12:00:00 UTC' );
			$written = 0;
			for ( $i = 0; $i < self::RECO_SURFACE_RECOMPUTE_DAYS; $i++ ) {
				$written += self::compute_reco_arm_daily( gmdate( 'Y-m-d', $ts - $i * DAY_IN_SECONDS ) );
			}
			return $written;
		} );

		
		
		
		$counts['reco_origin_daily'] = self::step( 'reco_origin_daily', static function () use ( $date ) {
			$ts      = strtotime( $date . ' 12:00:00 UTC' );
			$written = 0;
			for ( $i = 0; $i < self::RECO_SURFACE_RECOMPUTE_DAYS; $i++ ) {
				$written += self::compute_reco_origin_daily( gmdate( 'Y-m-d', $ts - $i * DAY_IN_SECONDS ) );
			}
			return $written;
		} );

		$counts['landing_funnel_daily'] = self::step( 'landing_funnel_daily', static function () use ( $date ) {
			return self::compute_landing_funnel_daily( $date );
		} );

		
		
		
		
		
		
		
		
		
		$counts['landing_canonical'] = self::step( 'landing_canonical', static function () {
			if ( ! class_exists( 'AIEO_Landing_Stats' ) ) {
				require_once AIEO_DIR . 'includes/class-aieo-landing-stats.php';
			}
			return AIEO_Landing_Stats::backfill();
		} );
		$counts['landing_seeded'] = self::step( 'landing_seeded', static function () {
			return AIEO_Landing_Stats::maybe_seed();
		} );
		
		
		
		$counts['exit_stamped'] = self::step( 'exit_stamped', static function () {
			return AIEO_Landing_Stats::stamp_exits();
		} );
		$counts['landing_campaign_daily'] = self::step( 'landing_campaign_daily', static function () use ( $date ) {
			$date_ts = strtotime( $date . ' 12:00:00 UTC' );
			$written = 0;
			for ( $i = 0; $i < 3; $i++ ) {
				$written += AIEO_Landing_Stats::compute_daily( gmdate( 'Y-m-d', $date_ts - $i * DAY_IN_SECONDS ) );
			}
			return $written;
		} );
		
		
		
		
		
		$counts['entry_credit_daily'] = self::step( 'entry_credit_daily', static function () use ( $date ) {
			$date_ts = strtotime( $date . ' 12:00:00 UTC' );
			$written = 0;
			for ( $i = 0; $i < 7; $i++ ) {
				$written += AIEO_Landing_Stats::compute_entry_credit_daily( gmdate( 'Y-m-d', $date_ts - $i * DAY_IN_SECONDS ) );
			}
			return $written;
		} );
		$counts['exit_daily'] = self::step( 'exit_daily', static function () use ( $date ) {
			$date_ts = strtotime( $date . ' 12:00:00 UTC' );
			$written = 0;
			for ( $i = 0; $i < 3; $i++ ) {
				$written += AIEO_Landing_Stats::compute_exit_daily( gmdate( 'Y-m-d', $date_ts - $i * DAY_IN_SECONDS ) );
			}
			return $written;
		} );

		
		$last = (int) get_option( self::WEEKLY_BUCKET_OPT, 0 );
		if ( ( time() - $last ) >= ( 6 * DAY_IN_SECONDS ) ) {
			$counts['conversion_buckets'] = self::step( 'conversion_buckets', static function () {
				return self::compute_conversion_buckets();
			} );
			update_option( self::WEEKLY_BUCKET_OPT, time(), false );
		}

		
		
		
		
		
		
		
		
		$counts['conversions_sweep'] = self::step( 'conversions_sweep', static function () {
			foreach ( array(
				'includes/dmm/class-aieo-dmm-conversions-registry.php',
				'includes/dmm/class-aieo-dmm-internal-promotions.php',
			) as $f ) {
				if ( file_exists( AIEO_DIR . $f ) ) { require_once AIEO_DIR . $f; }
			}
			if ( ! class_exists( 'AIEO_DMM_Internal_Promotions' ) ) { return 0; }
			return AIEO_DMM_Internal_Promotions::sweep_recent( 3 );
		} );

		
		
		
		
		
		$counts['gsc_history'] = self::step( 'gsc_history', static function () {
			if ( ! class_exists( 'AIEO_GSC_History' ) ) {
				require_once AIEO_DIR . 'includes/seo/class-aieo-gsc-history.php';
			}
			$r = AIEO_GSC_History::maybe_sync();
			return is_array( $r ) ? (int) ( $r['written'] ?? 0 ) : $r;
		} );

		$counts['dfs_rankings'] = self::step( 'dfs_rankings', static function () {
			if ( ! class_exists( 'AIEO_DFS_Rankings' ) ) {
				require_once AIEO_DIR . 'includes/seo/class-aieo-dfs-rankings.php';
			}
			$dfs = AIEO_DFS_Rankings::maybe_pull();
			return isset( $dfs['rows'] ) ? (int) $dfs['rows'] : ( isset( $dfs['skipped'] ) ? $dfs['skipped'] : 0 );
		} );

		
		
		
		
		$counts['ai_write_queue'] = self::step( 'ai_write_queue', array( __CLASS__, 'ai_write_queue_step' ) );

		
		
		
		$counts['seo_autofix_free'] = self::step( 'seo_autofix_free', static function () {
			if ( ! class_exists( 'AIEO_SEO_Fixer' ) ) {
				require_once AIEO_DIR . 'includes/seo/class-aieo-seo-fixer.php';
			}
			return wp_json_encode( AIEO_SEO_Fixer::run_autopilot() );
		} );

		self::$status['_finished'] = gmdate( 'Y-m-d H:i:s' );
		self::$status['_secs']     = round( microtime( true ) - self::$started_at, 2 );
		self::persist_status();

		aieo_debug_log( 'AIEO_Aggregate_Cron: done. counts=' . wp_json_encode( $counts ) );
		return $counts;
	}

	
	public static function ai_write_queue_step() {
		if ( ! class_exists( 'AIEO_SEO_Write_Queue' ) ) {
			require_once AIEO_DIR . 'includes/seo/class-aieo-seo-write-queue.php';
		}
		if ( ! AIEO_SEO_Write_Queue::nightly() ) {
			return wp_json_encode( array( 'skipped' => 'nightly_off' ) );
		}
		return wp_json_encode( AIEO_SEO_Write_Queue::run() );
	}

	
	private static function step( $name, $fn ) {
		$elapsed = microtime( true ) - self::$started_at;
		if ( self::RUN_BUDGET_SECS > 0 && $elapsed > self::RUN_BUDGET_SECS ) {
			self::$status[ $name ] = array( 'ok' => false, 'skipped' => 'run_budget_spent' );
			self::persist_status();
			aieo_debug_log( 'AIEO_Aggregate_Cron: SKIPPED ' . $name . ' — ' . self::RUN_BUDGET_SECS . 's run budget already spent.' );
			return 0;
		}

		
		if ( function_exists( 'set_time_limit' ) ) {
			
			@set_time_limit( self::STEP_TIME_LIMIT );
		}

		self::$status[ $name ] = array( 'ok' => null, 'state' => 'running', 'at' => gmdate( 'H:i:s' ) );
		self::persist_status();

		$t0   = microtime( true );
		$mark = function_exists( 'aieo_sql_error_mark' ) ? aieo_sql_error_mark() : 0;
		try {
			$rows = $fn();
			
			$sql_errors = function_exists( 'aieo_sql_errors_since' ) ? aieo_sql_errors_since( $mark ) : array();
			if ( $sql_errors ) {
				throw new \RuntimeException( $sql_errors[0] . ( count( $sql_errors ) > 1 ? ' (+' . ( count( $sql_errors ) - 1 ) . ' more)' : '' ) );
			}
		} catch ( \Throwable $e ) {
			self::$status[ $name ] = array(
				'ok'    => false,
				'secs'  => round( microtime( true ) - $t0, 2 ),
				'error' => substr( $e->getMessage(), 0, 300 ),
			);
			self::persist_status();
			aieo_debug_log( 'AIEO_Aggregate_Cron: FAILED ' . $name . ' — ' . $e->getMessage() );
			return 0;
		}

		self::$status[ $name ] = array(
			'ok'   => true,
			'rows' => is_scalar( $rows ) ? $rows : null,
			'secs' => round( microtime( true ) - $t0, 2 ),
		);
		self::persist_status();
		self::record_mark( $name );
		return $rows;
	}

	
	private static function persist_status() {
		update_option( self::STATUS_OPTION, self::$status, false );
	}

	
	public static function last_run_status() {
		$s       = get_option( self::STATUS_OPTION, array() );
		$died    = array();
		$fails   = array();
		$skipped = array();
		foreach ( (array) $s as $k => $v ) {
			if ( '_' === substr( (string) $k, 0, 1 ) || ! is_array( $v ) ) { continue; }
			if ( isset( $v['state'] ) && 'running' === $v['state'] ) { $died[] = $k; }
			
			
			
			
			
			if ( ! empty( $v['skipped'] ) ) {
				$skipped[] = $k;
				continue;
			}
			if ( isset( $v['ok'] ) && false === $v['ok'] ) { $fails[] = $k; }
		}
		return array(
			'steps'      => $s,
			'died_in'    => $died,
			'failed'     => $fails,
			'skipped'    => $skipped,
			'incomplete' => empty( $s['_finished'] ),
		);
	}

	
	public static function last_run_summary() {
		$st    = self::last_run_status();
		$steps = array();
		foreach ( (array) $st['steps'] as $k => $v ) {
			if ( '_' === substr( (string) $k, 0, 1 ) || ! is_array( $v ) ) { continue; }
			$steps[ $k ] = $v;
		}

		$total = count( $steps );
		if ( 0 === $total ) {
			return array();
		}

		$n_skipped = count( $st['skipped'] );
		$n_failed  = count( $st['failed'] );
		$n_died    = count( $st['died_in'] );
		$done      = $total - $n_skipped - $n_failed - $n_died;

		
		
		if ( 0 === $n_skipped && 0 === $n_failed && 0 === $n_died ) {
			return array(
				'level'  => 'ok',
				'text'   => sprintf(
					
					_n(
						'Last night\'s measurement: %d step completed.',
						'Last night\'s measurement: all %d steps completed.',
						$total,
						'ai-eshop-optimizer'
					),
					$total
				),
				'detail' => '',
			);
		}

		$text = sprintf(
			
			__( 'Last night\'s measurement: %1$d of %2$d steps completed.', 'ai-eshop-optimizer' ),
			max( 0, $done ),
			$total
		);

		$parts = array();
		if ( $n_skipped > 0 ) {
			$parts[] = sprintf(
				
				_n( '%d step skipped for time', '%d steps skipped for time', $n_skipped, 'ai-eshop-optimizer' ),
				$n_skipped
			);
		}
		if ( $n_failed > 0 ) {
			$parts[] = sprintf(
				
				_n( '%d step failed', '%d steps failed', $n_failed, 'ai-eshop-optimizer' ),
				$n_failed
			);
		}
		if ( $n_died > 0 ) {
			$parts[] = sprintf(
				
				_n( '%d step stopped part-way', '%d steps stopped part-way', $n_died, 'ai-eshop-optimizer' ),
				$n_died
			);
		}
		if ( ! empty( $parts ) ) {
			$text .= ' ' . implode( ', ', $parts ) . '.';
		}

		
		
		$detail = '';
		if ( $n_skipped > 0 && 0 === $n_failed && 0 === $n_died ) {
			$detail = sprintf(
				
				__( 'Nothing is broken. The nightly run stops after %d seconds so it cannot monopolise your server, and the steps it had not reached wait for the next night. On a small server this is normal. Reports fed by a skipped step will look empty until it runs.', 'ai-eshop-optimizer' ),
				(int) self::RUN_BUDGET_SECS
			);
		} elseif ( $n_died > 0 ) {
			$detail = __( 'A step stopped part-way, which usually means the server ran out of memory or time while it was working. The steps after it did not run.', 'ai-eshop-optimizer' );
		}

		return array(
			'level'  => ( $n_failed > 0 || $n_died > 0 ) ? 'warn' : 'info',
			'text'   => $text,
			'detail' => $detail,
		);
	}

	private static function resolve_date( $date_arg ) {
		if ( $date_arg && preg_match( '/^\d{4}-\d{2}-\d{2}$/', (string) $date_arg ) ) {
			return $date_arg;
		}
		
		return gmdate( 'Y-m-d', current_time( 'timestamp', true ) - DAY_IN_SECONDS );
	}

	
	
	

	
	public static function compute_engagement_daily( $date, $journeys = null, $target = null ) {
		global $wpdb;
		$nightly  = null === $journeys;
		$journeys = $nightly ? $wpdb->prefix . 'aieo_session_journeys' : (string) $journeys;
		$target   = null === $target ? $wpdb->prefix . 'aieo_engagement_daily' : (string) $target;
		if ( ! self::table_exists( $journeys ) || ! self::table_exists( $target ) ) { return 0; }
		if ( $nightly && ! self::live_events_hold_day( $date ) ) { return 0; }

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

		
		
		$sql = $wpdb->prepare(
			"INSERT INTO {$target}
				(date, product_id, views, product_views, unique_sessions, avg_time_sec, avg_scroll_pct, avg_clicks,
				 cart_adds, unique_cart_sessions, cart_add_rate, landings, last_page, bounces,
				 avg_time_to_cart, created_at)
			 SELECT %s AS d,
					j.product_id,
					COUNT(*) AS views,
					SUM(CASE WHEN j.event_type = 'product_view' THEN 1 ELSE 0 END) AS product_views,
					COUNT(DISTINCT j.session_id) AS unique_sessions,
					COALESCE(AVG(j.time_on_page), 0) AS avg_time_sec,
					COALESCE(AVG(j.max_scroll_depth), 0) AS avg_scroll_pct,
					COALESCE(AVG(j.click_count), 0) AS avg_clicks,
					SUM(CASE WHEN j.event_type = 'add_to_cart' THEN 1 ELSE 0 END) AS cart_adds,
					COUNT(DISTINCT CASE WHEN j.did_add_to_cart = 1 THEN j.session_id END) AS unique_cart_sessions,
					CASE WHEN COUNT(*) > 0 THEN SUM(j.did_add_to_cart) / COUNT(*) ELSE 0 END AS cart_add_rate,
					SUM(j.is_first_event) AS landings,
					SUM(j.is_last_event) AS last_page,
					SUM(CASE WHEN j.is_first_event = 1 AND j.is_last_event = 1 THEN 1 ELSE 0 END) AS bounces,
					-- time_to_cart is NULL on views that never converted; count those
					-- as 0 so the metric is a views-weighted mean (exactly what the
					-- From_Daily roll-up reconstructs). The legacy proc averaged only
					-- the converting rows, but no consumer reads avg_time_to_cart.
					AVG(COALESCE(j.time_to_cart, 0)) AS avg_time_to_cart,
					UTC_TIMESTAMP() AS created_at
			   FROM {$journeys} j
			  WHERE j.product_id IS NOT NULL AND j.product_id > 0
				AND j.event_timestamp BETWEEN %d AND %d
			  GROUP BY j.product_id
			 ON DUPLICATE KEY UPDATE
				views = VALUES(views),
				product_views = VALUES(product_views),
				unique_sessions = VALUES(unique_sessions),
				avg_time_sec = VALUES(avg_time_sec),
				avg_scroll_pct = VALUES(avg_scroll_pct),
				avg_clicks = VALUES(avg_clicks),
				cart_adds = VALUES(cart_adds),
				unique_cart_sessions = VALUES(unique_cart_sessions),
				cart_add_rate = VALUES(cart_add_rate),
				landings = VALUES(landings),
				last_page = VALUES(last_page),
				bounces = VALUES(bounces),
				avg_time_to_cart = VALUES(avg_time_to_cart),
				created_at = VALUES(created_at)",
			$date,
			$start_ms,
			$end_ms
		);

		$res = $wpdb->query( $sql );
		return (int) $res;
	}

	
	public static function live_events_hold_day( $date ) {
		global $wpdb;
		$archive = $wpdb->prefix . 'aieo_sessions_archive';
		if ( ! self::table_exists( $archive ) ) { return true; }
		$start_ms = strtotime( $date . ' 00:00:00 UTC' ) * 1000;
		return null === $wpdb->get_var( $wpdb->prepare(
			"SELECT 1 FROM {$archive} WHERE timestamp BETWEEN %d AND %d LIMIT 1",
			$start_ms,
			$start_ms + DAY_IN_SECONDS * 1000 - 1
		) );
	}

	
	public static function compute_engagement_daily_range( $days_back = 30 ) {
		$days_back = max( 1, (int) $days_back );
		$now       = current_time( 'timestamp', true );
		$written   = 0;
		for ( $i = 0; $i < $days_back; $i++ ) {
			$date     = gmdate( 'Y-m-d', $now - $i * DAY_IN_SECONDS );
			$written += self::compute_engagement_daily( $date );
		}
		return $written;
	}

	
	
	

	
	public static function compute_attribution_daily( $date ) {
		global $wpdb;
		$ae     = $wpdb->prefix . 'aieo_addcart_events';
		$ses    = $wpdb->prefix . 'aieo_sessions';
		$target = $wpdb->prefix . 'aieo_attribution_daily';
		if ( ! self::table_exists( $ae ) || ! self::table_exists( $target ) ) { return 0; }

		$day_start = $date . ' 00:00:00';
		$day_end   = $date . ' 23:59:59';

		
		
		
		
		
		
		
		$eng_select = self::table_exists( $ses )
			? ", (SELECT SUM(e.scroll_depth) FROM {$ses} e WHERE e.session_id = a.session_id AND e.product_id = a.product_id AND e.event_type = 'product_view') AS conv_scroll_sum"
				. ", (SELECT SUM(e.time_on_page) FROM {$ses} e WHERE e.session_id = a.session_id AND e.product_id = a.product_id AND e.event_type = 'product_view') AS conv_time_sum"
				. ", (SELECT COUNT(*) FROM {$ses} e WHERE e.session_id = a.session_id AND e.product_id = a.product_id AND e.event_type = 'product_view') AS conv_n"
			: ', 0 AS conv_scroll_sum, 0 AS conv_time_sum, 0 AS conv_n';

		
		
		
		
		
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT a.source, a.source_meta, a.quantity, a.unit_price, a.session_id, a.order_id{$eng_select}
				   FROM {$ae} a
				  WHERE a.ts_added BETWEEN %s AND %s",
				$day_start,
				$day_end
			),
			ARRAY_A
		);

		$bucket = array();
		foreach ( $rows as $r ) {
			$src     = $r['source'] !== '' ? (string) $r['source'] : 'unknown';
			$term_id = 0;
			$term_tx = '';
			if ( ! empty( $r['source_meta'] ) ) {
				$meta = json_decode( (string) $r['source_meta'], true );
				if ( is_array( $meta ) ) {
					if ( isset( $meta['archive_term_id'] ) ) { $term_id = (int) $meta['archive_term_id']; }
					if ( isset( $meta['archive_term_tax'] ) ) { $term_tx = (string) $meta['archive_term_tax']; }
					if ( ! $term_id && isset( $meta['term_id'] ) ) { $term_id = (int) $meta['term_id']; }
					if ( ! $term_tx && isset( $meta['taxonomy'] ) ) { $term_tx = (string) $meta['taxonomy']; }
				}
			}
			$key = $src . '|' . $term_id . '|' . $term_tx;
			if ( ! isset( $bucket[ $key ] ) ) {
				$bucket[ $key ] = array(
					'source'           => $src,
					'archive_term_id'  => $term_id,
					'archive_term_tax' => $term_tx,
					'cart_adds'        => 0,
					'units'            => 0,
					'session_set'      => array(),
					'converted_adds'   => 0,
					'scroll_sum'       => 0.0,
					'time_sum'         => 0.0,
					'view_n'           => 0,
					'revenue'          => 0.0,
				);
			}
			$bucket[ $key ]['cart_adds']++;
			$bucket[ $key ]['units']          += max( 1, (int) $r['quantity'] );
			$bucket[ $key ]['converted_adds'] += ( (int) $r['order_id'] > 0 ) ? 1 : 0;
			$sid = (string) $r['session_id'];
			if ( $sid !== '' ) { $bucket[ $key ]['session_set'][ $sid ] = 1; }
			$cn = (int) $r['conv_n'];
			if ( $cn > 0 ) {
				$bucket[ $key ]['scroll_sum'] += (float) $r['conv_scroll_sum'];
				$bucket[ $key ]['time_sum']   += (float) $r['conv_time_sum'];
				$bucket[ $key ]['view_n']     += $cn;
			}
			$bucket[ $key ]['revenue'] += ( (float) $r['unit_price'] ) * ( max( 1, (int) $r['quantity'] ) );
		}

		if ( empty( $bucket ) ) { return 0; }

		$written = 0;
		foreach ( $bucket as $b ) {
			$wpdb->query( $wpdb->prepare(
				"INSERT INTO {$target}
					(date, source, archive_term_id, archive_term_tax, cart_adds, units, sessions, converted_adds, avg_scroll_pct, avg_time_sec, eng_view_n, revenue, created_at)
				 VALUES (%s, %s, %d, %s, %d, %d, %d, %d, %f, %f, %d, %f, UTC_TIMESTAMP())
				 ON DUPLICATE KEY UPDATE
					archive_term_tax = VALUES(archive_term_tax),
					cart_adds = VALUES(cart_adds),
					units = VALUES(units),
					sessions = VALUES(sessions),
					converted_adds = VALUES(converted_adds),
					avg_scroll_pct = VALUES(avg_scroll_pct),
					avg_time_sec = VALUES(avg_time_sec),
					eng_view_n = VALUES(eng_view_n),
					revenue = VALUES(revenue),
					created_at = VALUES(created_at)",
				$date,
				substr( $b['source'], 0, 64 ),
				$b['archive_term_id'],
				substr( $b['archive_term_tax'], 0, 64 ),
				$b['cart_adds'],
				$b['units'],
				count( $b['session_set'] ),
				$b['converted_adds'],
				( $b['view_n'] > 0 ? $b['scroll_sum'] / $b['view_n'] : 0 ),
				( $b['view_n'] > 0 ? $b['time_sum'] / $b['view_n'] : 0 ),
				$b['view_n'],
				$b['revenue']
			) );
			$written++;
		}
		return $written;
	}

	
	
	

	
	public static function compute_blog_engagement_daily( $date ) {
		global $wpdb;
		$ses    = $wpdb->prefix . 'aieo_sessions';
		$ae     = $wpdb->prefix . 'aieo_addcart_events';
		$target = $wpdb->prefix . 'aieo_blog_engagement_daily';
		if ( ! self::table_exists( $ses ) || ! self::table_exists( $target ) ) { return 0; }

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

		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		$nf   = "REGEXP_REPLACE( SUBSTRING_INDEX( LEFT( COALESCE( page_url, '' ), 512 ), '#', 1 ), '^https?://[^/]+', '' )";
		$bare = "SUBSTRING_INDEX( {$nf}, '?', 1 )";
		
		$path = "IF( CHAR_LENGTH( {$bare} ) > 1 AND RIGHT( {$bare}, 1 ) = '/', LEFT( {$bare}, CHAR_LENGTH( {$bare} ) - 1 ), {$bare} )";

		
		$urls = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT IF( p.path = '', '/', p.path ) AS page_path,
						COUNT(*) AS views,
						COUNT(DISTINCT p.session_id) AS visitors,
						AVG(p.time_on_page) AS avg_time,
						AVG(p.scroll_depth) AS avg_scroll
				   FROM ( SELECT {$path} AS path, session_id, time_on_page, scroll_depth
							FROM {$ses}
						   WHERE event_type = 'page_view'
							 AND timestamp BETWEEN %d AND %d
							 AND ( product_id IS NULL OR product_id = 0 )
							 AND ( category_id IS NULL OR category_id = 0 )
							 AND page_url IS NOT NULL AND page_url <> '' ) p
				  GROUP BY p.path
				  ORDER BY views DESC
				  LIMIT %d",
				$start_ms,
				$end_ms,
				self::BLOG_PATH_LIMIT + 1
			),
			ARRAY_A
		);
		if ( empty( $urls ) ) { return 0; }

		
		
		if ( count( $urls ) > self::BLOG_PATH_LIMIT ) {
			$urls = array_slice( $urls, 0, self::BLOG_PATH_LIMIT );
			aieo_debug_log( 'AIEO_Aggregate_Cron: blog_engagement_daily ' . $date . ' capped at ' . self::BLOG_PATH_LIMIT . ' paths (busiest kept); some low-traffic posts omitted for this day.' );
		}

		
		
		
		
		
		
		
		
		
		
		
		
		
		$seg_of = static function ( $path ) {
			$seg = (string) substr( strrchr( '/' . rtrim( (string) $path, '/' ), '/' ), 1 );
			return $seg;
		};
		$probe = array();
		foreach ( $urls as $u ) {
			$seg = $seg_of( $u['page_path'] );
			if ( '' === $seg ) { continue; }
			$probe[ strtolower( $seg ) ]              = true;
			$probe[ strtolower( urldecode( $seg ) ) ] = true;
		}
		$candidates = array();
		foreach ( array_chunk( array_keys( $probe ), 500 ) as $chunk ) {
			$ph = implode( ',', array_fill( 0, count( $chunk ), '%s' ) );
			
			$got = $wpdb->get_col( $wpdb->prepare(
				"SELECT post_name FROM {$wpdb->posts}
				  WHERE post_type = 'post' AND post_status IN ('publish','private')
					AND post_name IN ({$ph})",
				$chunk
			) );
			foreach ( (array) $got as $s ) {
				$candidates[ strtolower( (string) $s ) ]              = true;
				$candidates[ strtolower( urldecode( (string) $s ) ) ] = true;
			}
		}

		$by_post = array();
		foreach ( $urls as $u ) {
			$seg = $seg_of( $u['page_path'] );
			if ( '' === $seg ) { continue; }
			if ( ! isset( $candidates[ strtolower( $seg ) ] ) && ! isset( $candidates[ strtolower( urldecode( $seg ) ) ] ) ) {
				continue; 
			}
			
			$post_id = (int) url_to_postid( home_url( (string) $u['page_path'] ) );
			if ( $post_id <= 0 ) { continue; }
			if ( get_post_type( $post_id ) !== 'post' ) { continue; }
			if ( ! isset( $by_post[ $post_id ] ) ) {
				$by_post[ $post_id ] = array(
					'views'      => 0,
					'visitors'   => 0,
					'time_sum'   => 0.0,
					'scroll_sum' => 0.0,
					'time_n'     => 0,
					'scroll_n'   => 0,
				);
			}
			$by_post[ $post_id ]['visitors']   += (int) $u['visitors'];
			$by_post[ $post_id ]['views']      += (int) $u['views'];
			$by_post[ $post_id ]['time_sum']   += ( (float) $u['avg_time'] ) * ( (int) $u['views'] );
			$by_post[ $post_id ]['scroll_sum'] += ( (float) $u['avg_scroll'] ) * ( (int) $u['views'] );
			$by_post[ $post_id ]['time_n']     += (int) $u['views'];
			$by_post[ $post_id ]['scroll_n']   += (int) $u['views'];
		}
		if ( empty( $by_post ) ) { return 0; }

		
		$blog_carts = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT source_meta, COUNT(*) AS n
				   FROM {$ae}
				  WHERE ts_added BETWEEN %s AND %s
					AND source = 'blog_post'
				  GROUP BY source_meta",
				$day_start,
				$day_end
			),
			ARRAY_A
		);
		$carts_by_post = array();
		foreach ( $blog_carts as $r ) {
			if ( empty( $r['source_meta'] ) ) { continue; }
			$meta = json_decode( (string) $r['source_meta'], true );
			if ( ! is_array( $meta ) || empty( $meta['post_id'] ) ) { continue; }
			$pid = (int) $meta['post_id'];
			if ( ! isset( $carts_by_post[ $pid ] ) ) { $carts_by_post[ $pid ] = 0; }
			$carts_by_post[ $pid ] += (int) $r['n'];
		}

		$written = 0;
		foreach ( $by_post as $post_id => $agg ) {
			$views  = (int) $agg['views'];
			$visit  = (int) $agg['visitors'];
			$avg_t  = $agg['time_n']   > 0 ? $agg['time_sum'] / $agg['time_n']     : 0;
			$avg_s  = $agg['scroll_n'] > 0 ? $agg['scroll_sum'] / $agg['scroll_n'] : 0;
			$adds   = isset( $carts_by_post[ $post_id ] ) ? (int) $carts_by_post[ $post_id ] : 0;
			$wpdb->query( $wpdb->prepare(
				"INSERT INTO {$target}
					(date, post_id, views, visitors, avg_time_sec, avg_scroll_pct, downstream_cart_adds, created_at)
				 VALUES (%s, %d, %d, %d, %f, %f, %d, UTC_TIMESTAMP())
				 ON DUPLICATE KEY UPDATE
					views = VALUES(views),
					visitors = VALUES(visitors),
					avg_time_sec = VALUES(avg_time_sec),
					avg_scroll_pct = VALUES(avg_scroll_pct),
					downstream_cart_adds = VALUES(downstream_cart_adds),
					created_at = VALUES(created_at)",
				$date, (int) $post_id, $views, $visit, $avg_t, $avg_s, $adds
			) );
			$written++;
		}
		return $written;
	}

	
	
	

	
	public static function compute_cart_removal_daily( $date ) {
		global $wpdb;
		$ses    = $wpdb->prefix . 'aieo_sessions';
		$target = $wpdb->prefix . 'aieo_cart_removal_daily';
		if ( ! self::table_exists( $ses ) || ! self::table_exists( $target ) ) { return 0; }

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

		
		
		
		
		
		
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT a.product_id,
						COUNT(*) AS adds,
						SUM(CASE WHEN EXISTS (
								SELECT 1 FROM {$ses} r
								 WHERE r.session_id = a.session_id
								   AND r.product_id = a.product_id
								   AND r.event_type = 'remove_from_cart'
								   AND r.timestamp >= a.timestamp
								   AND r.timestamp <= %d
							) THEN 1 ELSE 0 END) AS removes
				   FROM {$ses} a
				  WHERE a.event_type = 'add_to_cart'
					AND a.product_id IS NOT NULL AND a.product_id > 0
					AND a.timestamp BETWEEN %d AND %d
				  GROUP BY a.product_id
				  HAVING adds > 0",
				$end_ms,
				$start_ms,
				$end_ms
			),
			ARRAY_A
		);

		$written = 0;
		foreach ( $rows as $r ) {
			$adds  = (int) $r['adds'];
			$rems  = (int) $r['removes'];
			$rate  = $adds > 0 ? ( $rems / $adds ) : 0;
			$wpdb->query( $wpdb->prepare(
				"INSERT INTO {$target}
					(date, product_id, adds, removes, remove_rate, created_at)
				 VALUES (%s, %d, %d, %d, %f, UTC_TIMESTAMP())
				 ON DUPLICATE KEY UPDATE
					adds = VALUES(adds),
					removes = VALUES(removes),
					remove_rate = VALUES(remove_rate),
					created_at = VALUES(created_at)",
				$date, (int) $r['product_id'], $adds, $rems, $rate
			) );
			$written++;
		}
		return $written;
	}

	
	
	

	
	public static function compute_conversion_buckets() {
		global $wpdb;
		$ses    = $wpdb->prefix . 'aieo_sessions';
		$target = $wpdb->prefix . 'aieo_conversion_buckets';
		if ( ! self::table_exists( $ses ) || ! self::table_exists( $target ) ) { return 0; }
		if ( ! class_exists( 'AIEO_Conversion_Probability_Model' ) ) { return 0; }

		
		
		
		$since_ms = ( current_time( 'timestamp', true ) - 30 * DAY_IN_SECONDS ) * 1000;
		$top      = $wpdb->get_col( $wpdb->prepare(
			"SELECT product_id
			   FROM {$ses}
			  WHERE event_type = 'product_view'
				AND product_id IS NOT NULL AND product_id > 0
				AND timestamp >= %d
			  GROUP BY product_id
			  ORDER BY COUNT(*) DESC
			  LIMIT %d",
			$since_ms,
			self::TOP_PRODUCT_LIMIT
		) );
		if ( empty( $top ) ) { return 0; }

		$written = 0;
		foreach ( $top as $pid ) {
			$pid    = (int) $pid;
			$bucket = AIEO_Conversion_Probability_Model::compute_bucket_table( $pid );
			if ( is_wp_error( $bucket ) || empty( $bucket['rows'] ) ) { continue; }
			foreach ( $bucket['rows'] as $row ) {
				$sq = isset( $row['scroll_quartile'] ) ? (int) $row['scroll_quartile'] : ( isset( $row['scroll_bucket'] ) ? (int) $row['scroll_bucket'] : 0 );
				$tq = isset( $row['time_quartile'] )   ? (int) $row['time_quartile']   : ( isset( $row['time_bucket'] )   ? (int) $row['time_bucket']   : 0 );
				$wpdb->query( $wpdb->prepare(
					"INSERT INTO {$target}
						(product_id, scroll_quartile, time_quartile, viewers, cart_adders, p_cart_add, computed_at)
					 VALUES (%d, %d, %d, %d, %d, %f, UTC_TIMESTAMP())
					 ON DUPLICATE KEY UPDATE
						viewers = VALUES(viewers),
						cart_adders = VALUES(cart_adders),
						p_cart_add = VALUES(p_cart_add),
						computed_at = VALUES(computed_at)",
					$pid,
					$sq,
					$tq,
					isset( $row['viewers'] ) ? (int) $row['viewers'] : 0,
					isset( $row['cart_adders'] ) ? (int) $row['cart_adders'] : 0,
					isset( $row['p_cart_add'] ) ? (float) $row['p_cart_add'] : 0.0
				) );
				$written++;
			}
		}
		return $written;
	}

	
	
	

	
	public static function compute_reco_daily( $date ) {
		global $wpdb;
		$reco   = $wpdb->prefix . 'aieo_reco_events';
		$target = $wpdb->prefix . 'aieo_reco_daily';
		if ( ! self::table_exists( $reco ) || ! self::table_exists( $target ) ) { return 0; }

		$day_start = $date . ' 00:00:00';
		$day_end   = $date . ' 23:59:59';

		$sql = $wpdb->prepare(
			"INSERT INTO {$target}
				(date, surface, anchor_pid, target_pid, anchor_term_id, anchor_term_tax,
				 impressions, reco_clicks, sessions, created_at)
			 SELECT %s AS d,
					surface, anchor_pid, target_pid, anchor_term_id, anchor_term_tax,
					SUM(CASE WHEN event_type = 'impression' THEN 1 ELSE 0 END) AS impressions,
					SUM(CASE WHEN event_type = 'reco_click' THEN 1 ELSE 0 END) AS reco_clicks,
					COUNT(DISTINCT session_id) AS sessions,
					UTC_TIMESTAMP() AS created_at
			   FROM {$reco}
			  WHERE ts BETWEEN %s AND %s
			  GROUP BY surface, anchor_pid, target_pid, anchor_term_id, anchor_term_tax
			 ON DUPLICATE KEY UPDATE
				impressions = VALUES(impressions),
				reco_clicks = VALUES(reco_clicks),
				sessions = VALUES(sessions),
				created_at = VALUES(created_at)",
			$date,
			$day_start,
			$day_end
		);
		return (int) $wpdb->query( $sql );
	}

	
	
	

	
	public static function compute_reco_surface_daily( $date ) {
		global $wpdb;
		$reco    = $wpdb->prefix . 'aieo_reco_events';
		$addcart = $wpdb->prefix . 'aieo_addcart_events';
		$target  = $wpdb->prefix . 'aieo_reco_surface_daily';
		if ( ! self::table_exists( $reco ) || ! self::table_exists( $addcart ) || ! self::table_exists( $target ) ) { return 0; }
		
		
		if ( ! self::ensure_display_conditions() ) {
			aieo_debug_log( 'AIEO_Aggregate_Cron: AIEO_Display_Conditions unavailable, skipping reco_surface_daily for ' . $date );
			return 0;
		}

		
		
		$day_start = $date . ' 00:00:00';
		$day_end   = $date . ' 23:59:59';

		
		
		
		$shown      = self::verb_types_sql( 'shown' );
		$engaged    = self::verb_types_sql( 'engaged' );
		$suppressed = self::verb_types_sql( 'suppressed' );

		$sql = $wpdb->prepare(
			"INSERT INTO {$target}
				(stat_date, surface, position, impressions, clicks, suppressions, assisted_adds, assisted_purchases, created_at)
			 SELECT %s AS d,
					r.surface,
					r.position,
					COUNT(DISTINCT CASE WHEN r.event_type IN ({$shown}) THEN r.id END)      AS impressions,
					COUNT(DISTINCT CASE WHEN r.event_type IN ({$engaged}) THEN r.id END)    AS clicks,
					COUNT(DISTINCT CASE WHEN r.event_type IN ({$suppressed}) THEN r.id END) AS suppressions,
					COUNT(DISTINCT CASE WHEN e.id IS NOT NULL THEN r.id END) AS assisted_adds,
					COUNT(DISTINCT CASE WHEN e.order_id > 0 THEN r.id END)          AS assisted_purchases,
					UTC_TIMESTAMP() AS created_at
			   FROM {$reco} r
			   LEFT JOIN {$addcart} e
				 ON e.session_id = r.session_id
				AND e.product_id = r.target_pid
				AND e.ts_added   > r.ts
				AND e.ts_added   < DATE_ADD(r.ts, INTERVAL %d DAY)
				-- Only product-reco rows carry a real product in target_pid.
				-- Popup rows carry the popup's post id and promo rows carry a
				-- slot id, so without this the join could match a coincidental
				-- id collision and credit a popup with an assisted add.
				AND r.event_type IN ('impression','reco_click')
			  WHERE r.ts BETWEEN %s AND %s
			  GROUP BY r.surface, r.position
			 ON DUPLICATE KEY UPDATE
				impressions = VALUES(impressions),
				clicks = VALUES(clicks),
				suppressions = VALUES(suppressions),
				assisted_adds = VALUES(assisted_adds),
				assisted_purchases = VALUES(assisted_purchases),
				created_at = VALUES(created_at)",
			$date,
			self::RECO_ASSIST_WINDOW_DAYS,
			$day_start,
			$day_end
		);
		$written = (int) $wpdb->query( $sql );

		
		
		
		
		
		
		
		
		
		if ( $written > 0 ) {
			update_option( self::RECO_SURFACE_GEN_OPTION, (string) time(), false );
		}

		return $written;
	}

	
	
	

	
	public static function compute_reco_arm_daily( $date ) {
		global $wpdb;
		$reco    = $wpdb->prefix . 'aieo_reco_events';
		$addcart = $wpdb->prefix . 'aieo_addcart_events';
		$target  = $wpdb->prefix . 'aieo_reco_arm_daily';
		if ( ! self::table_exists( $reco ) || ! self::table_exists( $addcart ) || ! self::table_exists( $target ) ) { return 0; }
		if ( ! self::ensure_display_conditions() ) {
			aieo_debug_log( 'AIEO_Aggregate_Cron: AIEO_Display_Conditions unavailable, skipping reco_arm_daily for ' . $date );
			return 0;
		}

		$day_start = $date . ' 00:00:00';
		$day_end   = $date . ' 23:59:59';

		
		$shown      = self::verb_types_sql( 'shown' );
		$engaged    = self::verb_types_sql( 'engaged' );
		$suppressed = self::verb_types_sql( 'suppressed' );

		
		
		
		
		
		
		
		
		
		
		$sql = $wpdb->prepare(
			"INSERT INTO {$target}
				(stat_date, surface, arm, impressions, clicks, suppressions, assisted_adds,
				 assisted_purchases, sessions, click_sessions, add_sessions, created_at)
			 SELECT %s AS d,
					r.surface,
					r.arm,
					COUNT(DISTINCT CASE WHEN r.event_type IN ({$shown}) THEN r.id END)      AS impressions,
					COUNT(DISTINCT CASE WHEN r.event_type IN ({$engaged}) THEN r.id END)    AS clicks,
					COUNT(DISTINCT CASE WHEN r.event_type IN ({$suppressed}) THEN r.id END) AS suppressions,
					COUNT(DISTINCT CASE WHEN e.id IS NOT NULL THEN r.id END) AS assisted_adds,
					COUNT(DISTINCT CASE WHEN e.order_id > 0 THEN r.id END)   AS assisted_purchases,
					COUNT(DISTINCT CASE WHEN r.event_type NOT IN ({$suppressed}) THEN r.session_id END) AS sessions,
					COUNT(DISTINCT CASE WHEN r.event_type IN ({$engaged}) THEN r.session_id END) AS click_sessions,
					COUNT(DISTINCT CASE WHEN e.id IS NOT NULL THEN r.session_id END) AS add_sessions,
					UTC_TIMESTAMP() AS created_at
			   FROM {$reco} r
			   LEFT JOIN {$addcart} e
				 ON e.session_id = r.session_id
				AND e.product_id = r.target_pid
				AND e.ts_added   > r.ts
				AND e.ts_added   < DATE_ADD(r.ts, INTERVAL %d DAY)
				AND r.event_type IN ('impression','reco_click')
			  WHERE r.ts BETWEEN %s AND %s
			  GROUP BY r.surface, r.arm
			 ON DUPLICATE KEY UPDATE
				impressions = VALUES(impressions),
				clicks = VALUES(clicks),
				suppressions = VALUES(suppressions),
				assisted_adds = VALUES(assisted_adds),
				assisted_purchases = VALUES(assisted_purchases),
				sessions = VALUES(sessions),
				click_sessions = VALUES(click_sessions),
				add_sessions = VALUES(add_sessions),
				created_at = VALUES(created_at)",
			$date,
			self::RECO_ASSIST_WINDOW_DAYS,
			$day_start,
			$day_end
		);
		return (int) $wpdb->query( $sql );
	}

	
	
	

	
	public static function compute_reco_origin_daily( $date ) {
		global $wpdb;
		$reco    = $wpdb->prefix . 'aieo_reco_events';
		$addcart = $wpdb->prefix . 'aieo_addcart_events';
		$target  = $wpdb->prefix . 'aieo_reco_origin_daily';
		if ( ! self::table_exists( $reco ) || ! self::table_exists( $addcart ) || ! self::table_exists( $target ) ) { return 0; }
		if ( ! self::ensure_display_conditions() ) {
			aieo_debug_log( 'AIEO_Aggregate_Cron: AIEO_Display_Conditions unavailable, skipping reco_origin_daily for ' . $date );
			return 0;
		}

		$day_start = $date . ' 00:00:00';
		$day_end   = $date . ' 23:59:59';

		$shown      = self::verb_types_sql( 'shown' );
		$engaged    = self::verb_types_sql( 'engaged' );
		$suppressed = self::verb_types_sql( 'suppressed' );

		$sql = $wpdb->prepare(
			"INSERT INTO {$target}
				(stat_date, surface, arm, origin_id, impressions, clicks, suppressions,
				 assisted_adds, assisted_purchases, sessions, click_sessions, add_sessions, created_at)
			 SELECT %s AS d,
					r.surface,
					r.arm,
					r.origin_id,
					COUNT(DISTINCT CASE WHEN r.event_type IN ({$shown}) THEN r.id END)      AS impressions,
					COUNT(DISTINCT CASE WHEN r.event_type IN ({$engaged}) THEN r.id END)    AS clicks,
					COUNT(DISTINCT CASE WHEN r.event_type IN ({$suppressed}) THEN r.id END) AS suppressions,
					COUNT(DISTINCT CASE WHEN e.id IS NOT NULL THEN r.id END) AS assisted_adds,
					COUNT(DISTINCT CASE WHEN e.order_id > 0 THEN r.id END)   AS assisted_purchases,
					-- Suppressed rows are page views where the surface did NOT
					-- appear. Same exclusion, same reason, as reco_arm_daily.
					COUNT(DISTINCT CASE WHEN r.event_type NOT IN ({$suppressed}) THEN r.session_id END) AS sessions,
					COUNT(DISTINCT CASE WHEN r.event_type IN ({$engaged}) THEN r.session_id END) AS click_sessions,
					COUNT(DISTINCT CASE WHEN e.id IS NOT NULL THEN r.session_id END) AS add_sessions,
					UTC_TIMESTAMP() AS created_at
			   FROM {$reco} r
			   LEFT JOIN {$addcart} e
				 ON e.session_id = r.session_id
				AND e.product_id = r.target_pid
				AND e.ts_added   > r.ts
				AND e.ts_added   < DATE_ADD(r.ts, INTERVAL %d DAY)
				AND r.event_type IN ('impression','reco_click')
			  WHERE r.ts BETWEEN %s AND %s
			  GROUP BY r.surface, r.arm, r.origin_id
			 ON DUPLICATE KEY UPDATE
				impressions = VALUES(impressions),
				clicks = VALUES(clicks),
				suppressions = VALUES(suppressions),
				assisted_adds = VALUES(assisted_adds),
				assisted_purchases = VALUES(assisted_purchases),
				sessions = VALUES(sessions),
				click_sessions = VALUES(click_sessions),
				add_sessions = VALUES(add_sessions),
				created_at = VALUES(created_at)",
			$date,
			self::RECO_ASSIST_WINDOW_DAYS,
			$day_start,
			$day_end
		);
		return (int) $wpdb->query( $sql );
	}

	
	
	

	
	public static function compute_landing_funnel_daily( $date ) {
		global $wpdb;
		$statics = $wpdb->prefix . 'aieo_sessions_statics';
		$ses     = $wpdb->prefix . 'aieo_sessions';
		$ae      = $wpdb->prefix . 'aieo_addcart_events';
		$target  = $wpdb->prefix . 'aieo_landing_funnel_daily';
		if ( ! self::table_exists( $statics ) || ! self::table_exists( $target ) ) { return 0; }

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

		$visitors = $wpdb->get_results( $wpdb->prepare(
			"SELECT landing_page, COUNT(DISTINCT session_id) AS visitors
			   FROM {$statics}
			  WHERE first_timestamp BETWEEN %d AND %d
				AND landing_page IS NOT NULL AND landing_page <> ''
			  GROUP BY landing_page",
			$start_ms, $end_ms
		), ARRAY_A );
		if ( empty( $visitors ) ) { return 0; }

		$viewers_map = array();
		if ( self::table_exists( $ses ) ) {
			$viewers = $wpdb->get_results( $wpdb->prepare(
				"SELECT s.landing_page AS landing_page, COUNT(DISTINCT s.session_id) AS product_viewers
				   FROM {$statics} s
				   JOIN {$ses} e
					 ON e.session_id = s.session_id
					AND e.event_type = 'product_view'
					AND e.timestamp BETWEEN %d AND %d
				  WHERE s.first_timestamp BETWEEN %d AND %d
					AND s.landing_page IS NOT NULL AND s.landing_page <> ''
				  GROUP BY s.landing_page",
				$start_ms, $end_ms, $start_ms, $end_ms
			), ARRAY_A );
			foreach ( (array) $viewers as $v ) {
				$viewers_map[ (string) $v['landing_page'] ] = (int) $v['product_viewers'];
			}
		}

		$adders_map = array();
		$adds_map   = array();
		if ( self::table_exists( $ae ) ) {
			$adders = $wpdb->get_results( $wpdb->prepare(
				"SELECT s.landing_page AS landing_page,
						COUNT(DISTINCT a.session_id) AS cart_adders,
						COUNT(*)                     AS cart_adds
				   FROM {$statics} s
				   JOIN {$ae} a
					 ON a.session_id = s.session_id
					AND a.ts_added BETWEEN %s AND %s
				  WHERE s.first_timestamp BETWEEN %d AND %d
					AND s.landing_page IS NOT NULL AND s.landing_page <> ''
				  GROUP BY s.landing_page",
				$day_start, $day_end, $start_ms, $end_ms
			), ARRAY_A );
			foreach ( (array) $adders as $a ) {
				$lp = (string) $a['landing_page'];
				$adders_map[ $lp ] = (int) $a['cart_adders'];
				$adds_map[ $lp ]   = (int) $a['cart_adds'];
			}
		}

		$written = 0;
		foreach ( (array) $visitors as $v ) {
			$lp_full = (string) $v['landing_page'];
			$wpdb->query( $wpdb->prepare(
				"INSERT INTO {$target}
					(date, landing_page, visitors, product_viewers, cart_adders, cart_adds, created_at)
				 VALUES (%s, %s, %d, %d, %d, %d, UTC_TIMESTAMP())
				 ON DUPLICATE KEY UPDATE
					visitors = VALUES(visitors),
					product_viewers = VALUES(product_viewers),
					cart_adders = VALUES(cart_adders),
					cart_adds = VALUES(cart_adds),
					created_at = VALUES(created_at)",
				$date,
				substr( $lp_full, 0, 190 ),
				(int) $v['visitors'],
				isset( $viewers_map[ $lp_full ] ) ? $viewers_map[ $lp_full ] : 0,
				isset( $adders_map[ $lp_full ] ) ? $adders_map[ $lp_full ] : 0,
				isset( $adds_map[ $lp_full ] ) ? $adds_map[ $lp_full ] : 0
			) );
			$written++;
		}
		return $written;
	}

	
	
	

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

	
	private static function ensure_display_conditions() {
		if ( class_exists( 'AIEO_Display_Conditions' ) ) { return true; }
		$local = dirname( __DIR__ ) . '/class-aieo-display-conditions.php';
		if ( file_exists( $local ) ) { require_once $local; }
		if ( ! class_exists( 'AIEO_Display_Conditions' )
			&& defined( 'AIEO_DIR' )
			&& file_exists( AIEO_DIR . 'includes/class-aieo-display-conditions.php' ) ) {
			require_once AIEO_DIR . 'includes/class-aieo-display-conditions.php';
		}
		return class_exists( 'AIEO_Display_Conditions' );
	}

	
	private static function verb_types_sql( $verb ) {
		return AIEO_Display_Conditions::raw_types_sql( $verb );
	}
}