<?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;

	
	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 );

		$counts = array(
			'engagement_daily'        => 0,
			'attribution_daily'       => 0,
			'blog_engagement_daily'   => 0,
			'content_engagement_daily' => 0,
			'content_vitals'          => 0,
			'cart_removal_daily'      => 0,
			'reco_daily'              => 0,
			'landing_funnel_daily'    => 0,
			'conversion_buckets'      => 0,
		);

		$counts['engagement_daily']      = self::compute_engagement_daily( $date );
		$counts['attribution_daily']     = self::compute_attribution_daily( $date );
		$counts['blog_engagement_daily'] = self::compute_blog_engagement_daily( $date );

		
		
		
		
		
		if ( ! class_exists( 'AIEO_Content_Vitals' ) ) {
			require_once AIEO_DIR . 'includes/class-aieo-content-vitals.php';
		}
		$counts['content_engagement_daily'] = AIEO_Content_Vitals::compute_daily( $date );
		$cv                                 = AIEO_Content_Vitals::build_vitals();
		$counts['content_vitals']           = (int) ( $cv['rows'] ?? 0 );

		$counts['cart_removal_daily']    = self::compute_cart_removal_daily( $date );
		$counts['reco_daily']            = self::compute_reco_daily( $date );
		$counts['landing_funnel_daily']  = self::compute_landing_funnel_daily( $date );

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

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

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

		
		
		
		
		if ( ! class_exists( 'AIEO_SEO_Write_Queue' ) ) {
			require_once AIEO_DIR . 'includes/seo/class-aieo-seo-write-queue.php';
		}
		$wq = AIEO_SEO_Write_Queue::run();
		$counts['ai_write_queue'] = wp_json_encode( $wq );

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

	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 ) {
		global $wpdb;
		$journeys = $wpdb->prefix . 'aieo_session_journeys';
		$target   = $wpdb->prefix . 'aieo_engagement_daily';
		if ( ! self::table_exists( $journeys ) || ! 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;

		
		
		$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 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;

		
		
		$urls = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT page_url,
						COUNT(*) AS views,
						COUNT(DISTINCT session_id) AS visitors,
						AVG(time_on_page) AS avg_time,
						AVG(scroll_depth) AS avg_scroll
				   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 <> ''
				  GROUP BY page_url",
				$start_ms,
				$end_ms
			),
			ARRAY_A
		);
		if ( empty( $urls ) ) { return 0; }

		$by_post = array();
		foreach ( $urls as $u ) {
			$post_id = (int) url_to_postid( (string) $u['page_url'] );
			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_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;
	}
}
