<?php

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

class AIEO_Landing_Stats {

	const SCHEMA_OPT = 'aieo_landing_stats_schema';
	
	
	const SCHEMA_VER = '2026-11-03a';

	
	const TIER_PAGEVIEW = 0;
	const TIER_ENGAGED  = 1;
	const TIER_GOAL     = 2;
	const TIER_CART     = 3;
	const TIER_PURCHASE = 4;
	const SEED_OPT   = 'aieo_landing_stats_seeded';

	
	const NON_REVENUE_STATUSES = array( 'wc-cancelled', 'wc-refunded', 'wc-failed', 'wc-checkout-draft', 'auto-draft', 'trash' );

	public static function register() {
		
		
		
		add_action( 'aieo_archive_run', array( __CLASS__, 'canonicalise_pass' ) );
	}

	
	public static function canonicalise_pass() {
		self::ensure_schema();
		$n = self::backfill();
		return $n + self::stamp_exits();
	}

	
	
	

	public static function ensure_schema() {
		global $wpdb;
		if ( get_option( self::SCHEMA_OPT ) === self::SCHEMA_VER ) {
			return;
		}
		
		
		
		
		
		
		if ( get_transient( self::SCHEMA_OPT . '_retry' ) ) {
			return;
		}
		set_transient( self::SCHEMA_OPT . '_retry', 1, HOUR_IN_SECONDS );
		if ( ! AIEO_Schema_Lock::acquire( self::SCHEMA_OPT ) ) {
			return;
		}
		$ok = true;

		$cols = array(
			'landing_path'  => "VARCHAR(191) NOT NULL DEFAULT ''",
			'utm_source'    => "VARCHAR(100) NOT NULL DEFAULT ''",
			'utm_medium'    => "VARCHAR(100) NOT NULL DEFAULT ''",
			'utm_campaign'  => "VARCHAR(191) NOT NULL DEFAULT ''",
			'utm_content'   => "VARCHAR(191) NOT NULL DEFAULT ''",
			'utm_term'      => "VARCHAR(191) NOT NULL DEFAULT ''",
			'utm_id'        => "VARCHAR(100) NOT NULL DEFAULT ''",
			'exit_path'     => "VARCHAR(191) NOT NULL DEFAULT ''",
			
			
			
			'referrer_host' => "VARCHAR(100) NOT NULL DEFAULT ''",
			'channel'       => "VARCHAR(32) NOT NULL DEFAULT ''",
			
			
			
			'pages_viewed'  => 'SMALLINT UNSIGNED NOT NULL DEFAULT 0',
			'value_tier'    => 'TINYINT UNSIGNED NOT NULL DEFAULT 0',
		);
		foreach ( array( $wpdb->prefix . 'aieo_sessions_statics', $wpdb->prefix . 'aieo_sessions_statics_archive' ) as $table ) {
			if ( ! self::table_exists( $table ) ) {
				continue;
			}
			foreach ( $cols as $name => $ddl ) {
				$ok = self::ensure_column( $table, $name, $ddl ) && $ok;
			}
			$ok = self::ensure_key( $table, 'landing_path', 'landing_path' ) && $ok;
		}

		
		
		
		
		
		
		
		$daily = $wpdb->prefix . 'aieo_landing_campaign_daily';
		
		
		
		if ( self::table_exists( $daily ) && ! self::column_exists( $daily, 'referrer_host' ) ) {
			
			$wpdb->query( "DROP TABLE `{$daily}`" );
			delete_option( self::SEED_OPT );
		}
		if ( ! self::table_exists( $daily ) ) {
			
			$ok = ( false !== $wpdb->query( "CREATE TABLE IF NOT EXISTS `{$daily}` (
				`date` DATE NOT NULL,
				`grain_hash` CHAR(32) NOT NULL,
				`landing_path` VARCHAR(191) NOT NULL DEFAULT '',
				`utm_source` VARCHAR(100) NOT NULL DEFAULT '',
				`utm_medium` VARCHAR(100) NOT NULL DEFAULT '',
				`utm_campaign` VARCHAR(191) NOT NULL DEFAULT '',
				`utm_id` VARCHAR(100) NOT NULL DEFAULT '',
				`referrer_host` VARCHAR(100) NOT NULL DEFAULT '',
				`channel` VARCHAR(32) NOT NULL DEFAULT '',
				`sessions` INT UNSIGNED NOT NULL DEFAULT 0,
				`cart_adders` INT UNSIGNED NOT NULL DEFAULT 0,
				`cart_adds` INT UNSIGNED NOT NULL DEFAULT 0,
				`goals` INT UNSIGNED NOT NULL DEFAULT 0,
				`created_at` DATETIME NOT NULL,
				PRIMARY KEY (`date`, `grain_hash`),
				KEY `utm_campaign` (`utm_campaign`(80)),
				KEY `landing_path` (`landing_path`(120)),
				KEY `referrer_host` (`referrer_host`),
				KEY `channel` (`channel`)
			) ENGINE=InnoDB " . self::charset() ) ) && $ok;
		}

		
		
		
		$exit_daily = $wpdb->prefix . 'aieo_exit_daily';
		if ( ! self::table_exists( $exit_daily ) ) {
			
			$ok = ( false !== $wpdb->query( "CREATE TABLE IF NOT EXISTS `{$exit_daily}` (
				`date` DATE NOT NULL,
				`exit_path` VARCHAR(191) NOT NULL DEFAULT '',
				`sessions` INT UNSIGNED NOT NULL DEFAULT 0,
				`converted_sessions` INT UNSIGNED NOT NULL DEFAULT 0,
				`cart_sessions` INT UNSIGNED NOT NULL DEFAULT 0,
				`avg_duration_seconds` INT UNSIGNED NOT NULL DEFAULT 0,
				`created_at` DATETIME NOT NULL,
				PRIMARY KEY (`date`, `exit_path`),
				KEY `exit_path` (`exit_path`(120))
			) ENGINE=InnoDB " . self::charset() ) ) && $ok;
		}

		
		
		if ( self::table_exists( $daily ) ) {
			$ok = self::ensure_column( $daily, 'engaged_sessions', 'INT UNSIGNED NOT NULL DEFAULT 0' ) && $ok;
			$ok = self::ensure_column( $daily, 'purchases', 'INT UNSIGNED NOT NULL DEFAULT 0' ) && $ok;
			
			
			
			
			
			
			
			
			
			
			
			$ok = self::ensure_column( $daily, 'revenue', 'DECIMAL(14,2) NOT NULL DEFAULT 0.00' ) && $ok;
		}

		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		$credit = $wpdb->prefix . 'aieo_entry_credit_daily';
		if ( ! self::table_exists( $credit ) ) {
			
			
			
			
			
			$ok = ( false !== $wpdb->query( "CREATE TABLE IF NOT EXISTS `{$credit}` (
				`date` DATE NOT NULL,
				`entry_hash` CHAR(32) NOT NULL,
				`entry_path` VARCHAR(191) NOT NULL DEFAULT '',
				`object_type` VARCHAR(32) NOT NULL DEFAULT '',
				`object_id` BIGINT UNSIGNED NOT NULL DEFAULT 0,
				`orders` INT UNSIGNED NOT NULL DEFAULT 0,
				`revenue` DECIMAL(14,2) NOT NULL DEFAULT 0.00,
				`created_at` DATETIME NOT NULL,
				PRIMARY KEY (`date`, `entry_hash`),
				KEY `entry_path` (`entry_path`(120)),
				KEY `object` (`object_type`, `object_id`)
			) ENGINE=InnoDB " . self::charset() ) ) && $ok;
		}

		$ok = self::ensure_view() && $ok;
		$ok = self::ensure_exit_view() && $ok;

		
		
		
		
		
		$statics_t = $wpdb->prefix . 'aieo_sessions_statics';
		if ( self::table_exists( $statics_t ) && self::column_exists( $statics_t, 'value_tier' ) ) {
			
			$wpdb->query( "UPDATE `{$statics_t}` SET exit_path = '' WHERE exit_path <> '' AND value_tier = 0 AND pages_viewed = 0" );
		}

		
		
		
		if ( $ok ) {
			update_option( self::SCHEMA_OPT, self::SCHEMA_VER, false );
			delete_transient( self::SCHEMA_OPT . '_retry' );
		}
	}

	
	private static function ensure_exit_view() {
		global $wpdb;
		$view    = $wpdb->prefix . 'aieo_exit_facts';
		$statics = $wpdb->prefix . 'aieo_sessions_statics';
		$archive = $wpdb->prefix . 'aieo_sessions_statics_archive';

		$select = static function ( $table ) {
			return "SELECT DATE_ADD(DATE '1970-01-01', INTERVAL FLOOR(last_timestamp / 86400000) DAY) AS `day`,
					exit_path,
					COUNT(*) AS sessions,
					SUM(COALESCE(total_add_to_cart, 0) > 0) AS cart_sessions,
					ROUND(AVG(COALESCE(session_duration_seconds, 0))) AS avg_duration_seconds
				FROM `{$table}`
				WHERE exit_path NOT IN ('', '-')
				GROUP BY 1, 2";
		};

		$body = $select( $statics );
		if ( self::table_exists( $archive ) ) {
			$body .= ' UNION ALL ' . $select( $archive );
		}
		
		return false !== $wpdb->query( "CREATE OR REPLACE SQL SECURITY INVOKER VIEW `{$view}` AS {$body}" );
	}

	
	private static function ensure_view() {
		global $wpdb;
		$view    = $wpdb->prefix . 'aieo_landing_facts';
		$statics = $wpdb->prefix . 'aieo_sessions_statics';
		$archive = $wpdb->prefix . 'aieo_sessions_statics_archive';

		$select = static function ( $table ) {
			return "SELECT DATE_ADD(DATE '1970-01-01', INTERVAL FLOOR(first_timestamp / 86400000) DAY) AS `day`,
					landing_path, utm_source, utm_medium, utm_campaign, utm_id,
					referrer_host, channel,
					COUNT(*) AS sessions,
					SUM(COALESCE(total_add_to_cart, 0)) AS cart_adds,
					SUM(COALESCE(total_events, 0)) AS events
				FROM `{$table}`
				WHERE landing_path <> ''
				GROUP BY 1, 2, 3, 4, 5, 6, 7, 8";
		};

		$body = $select( $statics );
		if ( self::table_exists( $archive ) ) {
			$body .= ' UNION ALL ' . $select( $archive );
		}
		
		return false !== $wpdb->query( "CREATE OR REPLACE SQL SECURITY INVOKER VIEW `{$view}` AS {$body}" );
	}

	
	
	

	
	public static function canonicalise( $url, $referrer = '' ) {
		$out = array(
			'landing_path' => '', 'utm_source' => '', 'utm_medium' => '',
			'utm_campaign' => '', 'utm_content' => '', 'utm_term' => '', 'utm_id' => '',
			'referrer_host' => '', 'channel' => '',
		);
		$url        = trim( (string) $url );
		$has_gclid  = false;
		$has_fbclid = false;
		if ( '' !== $url ) {
			$path = wp_parse_url( $url, PHP_URL_PATH );
			$out['landing_path'] = substr( is_string( $path ) && '' !== $path ? $path : '/', 0, 191 );

			$query = wp_parse_url( $url, PHP_URL_QUERY );
			if ( is_string( $query ) && '' !== $query ) {
				parse_str( $query, $params );
				$caps = array(
					'utm_source' => 100, 'utm_medium' => 100, 'utm_campaign' => 191,
					'utm_content' => 191, 'utm_term' => 191, 'utm_id' => 100,
				);
				foreach ( $caps as $p => $cap ) {
					if ( isset( $params[ $p ] ) && is_string( $params[ $p ] ) ) {
						$out[ $p ] = substr( sanitize_text_field( $params[ $p ] ), 0, $cap );
					}
				}
				$has_gclid  = isset( $params['gclid'] ) || isset( $params['gad_source'] ) || isset( $params['gad_campaignid'] );
				$has_fbclid = isset( $params['fbclid'] );
				
				
				
				
				
				
				if ( '' === $out['utm_source'] && $has_gclid ) {
					$out['utm_source'] = 'google';
					$out['utm_medium'] = 'cpc';
				}
			}
		}

		
		$ref_host = strtolower( (string) wp_parse_url( trim( (string) $referrer ), PHP_URL_HOST ) );
		$ref_host = preg_replace( '/^www\./', '', $ref_host );
		$home     = preg_replace( '/^www\./', '', strtolower( (string) wp_parse_url( home_url(), PHP_URL_HOST ) ) );
		if ( '' !== $ref_host && $ref_host !== $home ) {
			$out['referrer_host'] = substr( $ref_host, 0, 100 );
		}

		$out['channel'] = self::classify_channel( $out, $has_gclid, $has_fbclid );
		return $out;
	}

	
	private static function classify_channel( array $c, $has_gclid, $has_fbclid ) {
		$s  = strtolower( $c['utm_source'] );
		$m  = strtolower( $c['utm_medium'] );
		$rh = $c['referrer_host'];
		if ( false !== strpos( $m, 'cpc' ) || false !== strpos( $m, 'paid' ) || false !== strpos( $m, 'ppc' ) ) {
			if ( false !== strpos( $s, 'google' ) ) { return 'paid_google'; }
			if ( false !== strpos( $s, 'facebook' ) || false !== strpos( $s, 'instagram' ) || false !== strpos( $s, 'meta' ) || false !== strpos( $s, 'fb' ) || 'ig' === $s ) { return 'paid_facebook'; }
			if ( false !== strpos( $s, 'tiktok' ) ) { return 'paid_tiktok'; }
			if ( false !== strpos( $s, 'bing' ) || false !== strpos( $s, 'microsoft' ) ) { return 'paid_microsoft'; }
			return 'paid_other';
		}
		if ( $has_gclid ) { return 'paid_google'; }
		if ( false !== strpos( $m, 'email' ) || false !== strpos( $s, 'newsletter' ) || false !== strpos( $s, 'klaviyo' ) ) { return 'newsletter'; }
		if ( false !== strpos( $m, 'social' ) || in_array( $s, array( 'facebook', 'instagram', 'tiktok', 'ig', 'fb' ), true ) ) { return 'social'; }
		if ( $has_fbclid ) { return 'social'; }
		if ( '' !== $s || '' !== $m || '' !== $c['utm_campaign'] ) { return 'campaign_other'; }
		if ( '' !== $rh ) {
			foreach ( array( 'google.', 'bing.', 'yahoo.', 'duckduckgo.', 'ecosia.' ) as $se ) {
				if ( false !== strpos( $rh, $se ) ) { return 'organic_search'; }
			}
			foreach ( array( 'facebook.', 'instagram.', 'tiktok.', 'twitter.', 'x.com', 't.co', 'linkedin.', 'pinterest.', 'youtube.', 'fb.' ) as $sh ) {
				if ( false !== strpos( $rh, $sh ) ) { return 'social'; }
			}
			return 'referral';
		}
		return 'direct';
	}

	
	public static function backfill( $limit = 20000, $start_ms = 0, $end_ms = 0 ) {
		global $wpdb;
		self::ensure_schema();
		$done = 0;
		foreach ( array( $wpdb->prefix . 'aieo_sessions_statics', $wpdb->prefix . 'aieo_sessions_statics_archive' ) as $table ) {
			if ( ! self::table_exists( $table ) || ! self::column_exists( $table, 'channel' ) ) {
				continue;
			}
			$window = ( $start_ms > 0 && $end_ms > 0 )
				? $wpdb->prepare( ' AND first_timestamp BETWEEN %d AND %d', $start_ms, $end_ms )
				: '';
			while ( $done < $limit ) {
				$chunk = min( 2000, $limit - $done );
				
				
				
				
				$rows = $wpdb->get_results( $wpdb->prepare(
					"SELECT id, landing_page, referrer FROM `{$table}`
					  WHERE channel = ''{$window}
					  LIMIT %d",
					$chunk
				), ARRAY_A );
				if ( empty( $rows ) ) {
					break;
				}
				foreach ( $rows as $row ) {
					$c = self::canonicalise( (string) $row['landing_page'], (string) $row['referrer'] );
					if ( '' === $c['landing_path'] && '' !== trim( (string) $row['landing_page'] ) ) {
						$c['landing_path'] = '/'; 
					}
					
					$wpdb->query( $wpdb->prepare(
						"UPDATE `{$table}` SET landing_path = %s, utm_source = %s, utm_medium = %s,
							utm_campaign = %s, utm_content = %s, utm_term = %s, utm_id = %s,
							referrer_host = %s, channel = %s
						  WHERE id = %d",
						$c['landing_path'], $c['utm_source'], $c['utm_medium'],
						$c['utm_campaign'], $c['utm_content'], $c['utm_term'], $c['utm_id'],
						$c['referrer_host'], $c['channel'],
						(int) $row['id']
					) );
					$done++;
				}
			}
		}
		return $done;
	}

	
	public static function maybe_seed() {
		global $wpdb;
		if ( get_option( self::SEED_OPT ) ) {
			return 0;
		}
		$statics = $wpdb->prefix . 'aieo_sessions_statics';
		if ( ! self::table_exists( $statics ) || ! self::column_exists( $statics, 'channel' ) ) {
			return 0;
		}
		
		
		$pending = $wpdb->get_var( "SELECT 1 FROM `{$statics}` WHERE channel = '' LIMIT 1" );
		if ( $pending ) {
			return 0;
		}
		$days = 0;
		for ( $i = 1; $i <= 30; $i++ ) {
			$d = gmdate( 'Y-m-d', time() - $i * DAY_IN_SECONDS );
			if ( self::compute_daily( $d ) > 0 ) {
				$days++;
			}
		}
		update_option( self::SEED_OPT, gmdate( 'Y-m-d' ), false );
		return $days;
	}

	
	
	

	
	public static function stamp_exits( $limit = 20000 ) {
		global $wpdb;
		self::ensure_schema();
		$statics = $wpdb->prefix . 'aieo_sessions_statics';
		if ( ! self::table_exists( $statics ) || ! self::column_exists( $statics, 'exit_path' ) ) {
			return 0;
		}
		$settled_ms = ( time() - 6 * HOUR_IN_SECONDS ) * 1000;

		$event_tables = array();
		foreach ( array( $wpdb->prefix . 'aieo_sessions', $wpdb->prefix . 'aieo_sessions_archive' ) as $t ) {
			if ( self::table_exists( $t ) ) {
				$event_tables[] = $t;
			}
		}
		if ( empty( $event_tables ) ) {
			return 0;
		}

		$ae = $wpdb->prefix . 'aieo_addcart_events';
		$has_ae = self::table_exists( $ae );

		$done = 0;
		while ( $done < $limit ) {
			$chunk = min( 500, $limit - $done );
			
			$sessions = $wpdb->get_results( $wpdb->prepare(
				"SELECT id, session_id, session_duration_seconds FROM `{$statics}`
				  WHERE exit_path = '' AND last_timestamp < %d
				  LIMIT %d",
				$settled_ms, $chunk
			), ARRAY_A );
			if ( empty( $sessions ) ) {
				break;
			}
			$by_sid   = array();
			$duration = array();
			foreach ( $sessions as $s ) {
				$sid              = (string) $s['session_id'];
				$by_sid[ $sid ]   = (int) $s['id'];
				$duration[ $sid ] = (int) $s['session_duration_seconds'];
			}
			$in = implode( ',', array_map( static function ( $sid ) use ( $wpdb ) {
				return $wpdb->prepare( '%s', $sid );
			}, array_keys( $by_sid ) ) );

			
			
			$last = array();
			foreach ( $event_tables as $t ) {
				
				$rows = $wpdb->get_results(
					"SELECT e.session_id, MIN(e.page_url) AS page_url
					   FROM `{$t}` e
					   JOIN ( SELECT session_id, MAX(`timestamp`) AS mt
								FROM `{$t}`
							   WHERE session_id IN ({$in}) AND page_url IS NOT NULL AND page_url <> ''
							   GROUP BY session_id ) x
						 ON x.session_id = e.session_id AND e.`timestamp` = x.mt
					  WHERE e.page_url IS NOT NULL AND e.page_url <> ''
					  GROUP BY e.session_id",
					ARRAY_A
				);
				foreach ( (array) $rows as $r ) {
					$sid = (string) $r['session_id'];
					if ( ! isset( $last[ $sid ] ) ) {
						$last[ $sid ] = (string) $r['page_url'];
					}
				}
			}

			
			
			
			$pages = array();
			$purch = array();
			$goal  = array();
			foreach ( $event_tables as $t ) {
				
				$aggs = $wpdb->get_results(
					"SELECT session_id,
							COUNT(DISTINCT CASE WHEN event_type = 'page_view' AND page_url <> '' THEN page_url END) AS pages,
							MAX(CASE WHEN order_id > 0 THEN 1 ELSE 0 END) AS purch,
							MAX(CASE WHEN event_type = 'goal' THEN 1 ELSE 0 END) AS goal
					   FROM `{$t}`
					  WHERE session_id IN ({$in})
					  GROUP BY session_id",
					ARRAY_A
				);
				foreach ( (array) $aggs as $a ) {
					$sid           = (string) $a['session_id'];
					$pages[ $sid ] = max( isset( $pages[ $sid ] ) ? $pages[ $sid ] : 0, (int) $a['pages'] );
					$purch[ $sid ] = max( isset( $purch[ $sid ] ) ? $purch[ $sid ] : 0, (int) $a['purch'] );
					$goal[ $sid ]  = max( isset( $goal[ $sid ] ) ? $goal[ $sid ] : 0, (int) $a['goal'] );
				}
			}
			$cart = array();
			if ( $has_ae ) {
				
				foreach ( (array) $wpdb->get_col( "SELECT DISTINCT session_id FROM `{$ae}` WHERE session_id IN ({$in})" ) as $sid ) {
					$cart[ (string) $sid ] = 1;
				}
			}

			foreach ( $by_sid as $sid => $id ) {
				if ( isset( $last[ $sid ] ) ) {
					$c    = self::canonicalise( $last[ $sid ] );
					$path = '' !== $c['landing_path'] ? $c['landing_path'] : '/';
				} else {
					
					
					$path = '-';
				}
				$pv  = isset( $pages[ $sid ] ) ? $pages[ $sid ] : 0;
				$dur = isset( $duration[ $sid ] ) ? $duration[ $sid ] : 0;
				if ( ! empty( $purch[ $sid ] ) ) {
					$tier = self::TIER_PURCHASE;
				} elseif ( isset( $cart[ $sid ] ) ) {
					$tier = self::TIER_CART;
				} elseif ( ! empty( $goal[ $sid ] ) ) {
					$tier = self::TIER_GOAL;
				} elseif ( $pv >= 3 || $dur >= 90 ) {
					$tier = self::TIER_ENGAGED;
				} else {
					$tier = self::TIER_PAGEVIEW;
				}
				
				$wpdb->query( $wpdb->prepare(
					"UPDATE `{$statics}` SET exit_path = %s, pages_viewed = %d, value_tier = %d WHERE id = %d",
					$path, $pv, $tier, $id
				) );
				$done++;
			}
		}
		return $done;
	}

	
	public static function compute_exit_daily( $date ) {
		global $wpdb;
		self::ensure_schema();
		$statics = $wpdb->prefix . 'aieo_sessions_statics';
		$ses     = $wpdb->prefix . 'aieo_sessions';
		$daily   = $wpdb->prefix . 'aieo_exit_daily';
		if ( ! self::table_exists( $statics ) || ! self::table_exists( $daily ) || ! self::column_exists( $statics, 'exit_path' ) ) {
			return 0;
		}
		$start_ms = strtotime( $date . ' 00:00:00 UTC' ) * 1000;
		$end_ms   = strtotime( $date . ' 23:59:59 UTC' ) * 1000 + 999;

		$goal_join = self::table_exists( $ses )
			? "LEFT JOIN ( SELECT DISTINCT session_id FROM `{$ses}` WHERE event_type = 'goal' ) g ON g.session_id = s.session_id"
			: 'LEFT JOIN ( SELECT NULL AS session_id ) g ON 1 = 0';

		
		$rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT s.exit_path,
					COUNT(*) AS sessions,
					SUM(g.session_id IS NOT NULL) AS converted_sessions,
					SUM(COALESCE(s.total_add_to_cart, 0) > 0) AS cart_sessions,
					ROUND(AVG(COALESCE(s.session_duration_seconds, 0))) AS avg_duration
			   FROM `{$statics}` s
			   {$goal_join}
			  WHERE s.last_timestamp BETWEEN %d AND %d AND s.exit_path NOT IN ('', '-')
			  GROUP BY s.exit_path",
			$start_ms, $end_ms
		), ARRAY_A );
		if ( empty( $rows ) ) {
			return 0;
		}

		$written = 0;
		foreach ( $rows as $r ) {
			
			$wpdb->query( $wpdb->prepare(
				"INSERT INTO `{$daily}`
					(`date`, exit_path, sessions, converted_sessions, cart_sessions, avg_duration_seconds, created_at)
				 VALUES (%s, %s, %d, %d, %d, %d, UTC_TIMESTAMP())
				 ON DUPLICATE KEY UPDATE
					sessions = VALUES(sessions), converted_sessions = VALUES(converted_sessions),
					cart_sessions = VALUES(cart_sessions), avg_duration_seconds = VALUES(avg_duration_seconds),
					created_at = VALUES(created_at)",
				$date, $r['exit_path'], (int) $r['sessions'], (int) $r['converted_sessions'],
				(int) $r['cart_sessions'], (int) $r['avg_duration']
			) );
			$written++;
		}
		return $written;
	}

	
	
	

	
	public static function compute_daily( $date ) {
		global $wpdb;
		self::ensure_schema();
		$statics  = $wpdb->prefix . 'aieo_sessions_statics';
		$st_arch  = $wpdb->prefix . 'aieo_sessions_statics_archive';
		$ses      = $wpdb->prefix . 'aieo_sessions';
		$ses_arch = $wpdb->prefix . 'aieo_sessions_archive';
		$ae       = $wpdb->prefix . 'aieo_addcart_events';
		$orders_t = $wpdb->prefix . 'wc_orders';
		$daily    = $wpdb->prefix . 'aieo_landing_campaign_daily';
		if ( ! self::table_exists( $statics ) || ! self::table_exists( $daily ) ) {
			return 0;
		}
		$start_ms  = (int) ( strtotime( $date . ' 00:00:00 UTC' ) * 1000 );
		$end_ms    = (int) ( strtotime( $date . ' 23:59:59 UTC' ) * 1000 + 999 );
		$day_start = $date . ' 00:00:00';
		$day_end   = $date . ' 23:59:59';

		
		
		self::backfill( 100000, $start_ms, $end_ms );

		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		$st_cols  = 'session_id, landing_path, utm_source, utm_medium, utm_campaign, utm_id, referrer_host, channel, pages_viewed, session_duration_seconds';
		$st_where = "WHERE first_timestamp BETWEEN {$start_ms} AND {$end_ms} AND landing_path <> ''";
		$stat_src = "(SELECT {$st_cols} FROM `{$statics}` {$st_where}"
			. ( self::table_exists( $st_arch ) ? " UNION ALL SELECT {$st_cols} FROM `{$st_arch}` {$st_where}" : '' )
			. ')';

		
		$groups = $wpdb->get_results(
			"SELECT landing_path, utm_source, utm_medium, utm_campaign, referrer_host,
					MAX(utm_id) AS utm_id, MAX(channel) AS channel,
					COUNT(DISTINCT session_id) AS sessions
			   FROM {$stat_src} s
			  GROUP BY landing_path, utm_source, utm_medium, utm_campaign, referrer_host",
			ARRAY_A
		);
		if ( empty( $groups ) ) {
			return 0;
		}

		
		
		
		
		
		
		
		$key = static function ( $r ) {
			return mb_strtolower(
				$r['landing_path'] . "\x1f" . $r['utm_source'] . "\x1f" . $r['utm_medium'] . "\x1f" . $r['utm_campaign'] . "\x1f" . $r['referrer_host'],
				'UTF-8'
			);
		};

		$carts = array();
		if ( self::table_exists( $ae ) ) {
			
			
			
			$rows = $wpdb->get_results( $wpdb->prepare(
				"SELECT s.landing_path, s.utm_source, s.utm_medium, s.utm_campaign, s.referrer_host,
						COUNT(DISTINCT a.session_id) AS cart_adders, COUNT(DISTINCT a.id) AS cart_adds
				   FROM {$stat_src} s
				   JOIN `{$ae}` a ON a.session_id = s.session_id AND a.ts_added BETWEEN %s AND %s
				  GROUP BY s.landing_path, s.utm_source, s.utm_medium, s.utm_campaign, s.referrer_host",
				$day_start, $day_end
			), ARRAY_A );
			foreach ( (array) $rows as $r ) {
				$carts[ $key( $r ) ] = array( (int) $r['cart_adders'], (int) $r['cart_adds'] );
			}
		}

		
		
		
		$engaged = array();
		
		$rows = $wpdb->get_results(
			"SELECT landing_path, utm_source, utm_medium, utm_campaign, referrer_host,
					COUNT(DISTINCT session_id) AS n
			   FROM {$stat_src} s
			  WHERE ( pages_viewed >= 3 OR session_duration_seconds >= 90 )
			  GROUP BY landing_path, utm_source, utm_medium, utm_campaign, referrer_host",
			ARRAY_A
		);
		foreach ( (array) $rows as $r ) {
			$engaged[ $key( $r ) ] = (int) $r['n'];
		}
		
		
		
		
		
		
		
		$ev_cols = 'id, session_id, event_type, order_id, `timestamp`';
		$ev_where = "WHERE `timestamp` BETWEEN {$start_ms} AND {$end_ms}";
		$ev_src   = ( self::table_exists( $ses ) && self::table_exists( $ses_arch ) )
			? "(SELECT {$ev_cols} FROM `{$ses}` {$ev_where} UNION ALL SELECT {$ev_cols} FROM `{$ses_arch}` {$ev_where})"
			: "(SELECT {$ev_cols} FROM `{$ses}` {$ev_where})";

		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		$purchases = array();
		$revenue   = array();
		$non_rev   = "'" . implode( "','", self::NON_REVENUE_STATUSES ) . "'";
		if ( self::table_exists( $ses ) && self::table_exists( $orders_t ) ) {
			
			$rows = $wpdb->get_results(
				"SELECT s.landing_path, s.utm_source, s.utm_medium, s.utm_campaign, s.referrer_host,
						COUNT(DISTINCT s.session_id) AS n,
						ROUND(SUM(o.total_amount), 2) AS rev
				   FROM {$stat_src} s
				   JOIN ( SELECT DISTINCT ev.session_id, ev.order_id FROM {$ev_src} ev
						   WHERE ev.order_id > 0 ) e
					 ON e.session_id = s.session_id
				   JOIN `{$orders_t}` o
					 ON o.id = e.order_id AND o.status NOT IN ({$non_rev})
				  GROUP BY s.landing_path, s.utm_source, s.utm_medium, s.utm_campaign, s.referrer_host",
				ARRAY_A
			);
			foreach ( (array) $rows as $r ) {
				$purchases[ $key( $r ) ] = (int) $r['n'];
				$revenue[ $key( $r ) ]   = (float) $r['rev'];
			}
		} elseif ( self::table_exists( $ses ) ) {
			
			
			
			
			
			$rows = $wpdb->get_results(
				"SELECT s.landing_path, s.utm_source, s.utm_medium, s.utm_campaign, s.referrer_host,
						COUNT(DISTINCT s.session_id) AS n
				   FROM {$stat_src} s
				   JOIN {$ev_src} e ON e.session_id = s.session_id AND e.order_id > 0
				  GROUP BY s.landing_path, s.utm_source, s.utm_medium, s.utm_campaign, s.referrer_host",
				ARRAY_A
			);
			foreach ( (array) $rows as $r ) {
				$purchases[ $key( $r ) ] = (int) $r['n'];
			}
		}

		$goals = array();
		if ( self::table_exists( $ses ) ) {
			
			
			
			
			$rows = $wpdb->get_results(
				"SELECT s.landing_path, s.utm_source, s.utm_medium, s.utm_campaign, s.referrer_host,
						COUNT(DISTINCT e.id) AS goals
				   FROM {$stat_src} s
				   JOIN {$ev_src} e ON e.session_id = s.session_id AND e.event_type = 'goal'
				  GROUP BY s.landing_path, s.utm_source, s.utm_medium, s.utm_campaign, s.referrer_host",
				ARRAY_A
			);
			foreach ( (array) $rows as $r ) {
				$goals[ $key( $r ) ] = (int) $r['goals'];
			}
		}

		$written = 0;
		foreach ( $groups as $g ) {
			$k  = $key( $g );
			$ca = isset( $carts[ $k ] ) ? $carts[ $k ] : array( 0, 0 );
			
			$wpdb->query( $wpdb->prepare(
				"INSERT INTO `{$daily}`
					(`date`, grain_hash, landing_path, utm_source, utm_medium, utm_campaign, utm_id,
					 referrer_host, channel, sessions, engaged_sessions, cart_adders, cart_adds,
					 goals, purchases, revenue, created_at)
				 VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %d, %d, %d, %d, %d, %d, %f, UTC_TIMESTAMP())
				 ON DUPLICATE KEY UPDATE
					utm_id = VALUES(utm_id), channel = VALUES(channel), sessions = VALUES(sessions),
					engaged_sessions = VALUES(engaged_sessions),
					cart_adders = VALUES(cart_adders), cart_adds = VALUES(cart_adds),
					goals = VALUES(goals), purchases = VALUES(purchases),
					revenue = VALUES(revenue), created_at = VALUES(created_at)",
				$date, md5( $k ), $g['landing_path'], $g['utm_source'], $g['utm_medium'], $g['utm_campaign'],
				(string) $g['utm_id'], (string) $g['referrer_host'], (string) $g['channel'],
				(int) $g['sessions'],
				isset( $engaged[ $k ] ) ? $engaged[ $k ] : 0,
				$ca[0], $ca[1],
				isset( $goals[ $k ] ) ? $goals[ $k ] : 0,
				isset( $purchases[ $k ] ) ? $purchases[ $k ] : 0,
				isset( $revenue[ $k ] ) ? $revenue[ $k ] : 0.0
			) );
			$written++;
		}
		return $written;
	}

	
	
	

	
	public static function compute_entry_credit_daily( $date ) {
		global $wpdb;
		self::ensure_schema();

		$orders_t = $wpdb->prefix . 'wc_orders';
		$meta_t   = $wpdb->prefix . 'wc_orders_meta';
		$credit   = $wpdb->prefix . 'aieo_entry_credit_daily';
		if ( ! self::table_exists( $orders_t ) || ! self::table_exists( $meta_t ) || ! self::table_exists( $credit ) ) {
			return 0;
		}
		if ( ! class_exists( 'AIEO_Path_Resolver' ) ) {
			require_once AIEO_DIR . 'includes/seo/class-aieo-path-resolver.php';
		}

		$non_rev = "'" . implode( "','", self::NON_REVENUE_STATUSES ) . "'";

		
		
		
		$rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT o.id, MAX(m.meta_value) AS entry, MAX(o.total_amount) AS total
			   FROM `{$orders_t}` o
			   JOIN `{$meta_t}` m
				 ON m.order_id = o.id AND m.meta_key = '_wc_order_attribution_session_entry'
			  WHERE o.date_created_gmt >= %s AND o.date_created_gmt <= %s
				AND o.status NOT IN ({$non_rev})
				AND m.meta_value <> ''
			  GROUP BY o.id",
			$date . ' 00:00:00',
			$date . ' 23:59:59'
		), ARRAY_A );

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

		$agg = array();
		foreach ( $rows as $r ) {
			$path = AIEO_Path_Resolver::normalise( (string) $r['entry'] );
			if ( '' === $path ) {
				continue;
			}
			if ( ! isset( $agg[ $path ] ) ) {
				$agg[ $path ] = array( 'orders' => 0, 'revenue' => 0.0 );
			}
			$agg[ $path ]['orders']++;
			$agg[ $path ]['revenue'] += (float) $r['total'];
		}
		if ( empty( $agg ) ) {
			return 0;
		}

		
		
		$map = AIEO_Path_Resolver::resolve_many( array_keys( $agg ) );

		$written = 0;
		foreach ( $agg as $path => $a ) {
			$hit = isset( $map[ $path ] ) ? $map[ $path ] : null;
			$wpdb->query( $wpdb->prepare(
				
				"INSERT INTO `{$credit}`
					(`date`, entry_hash, entry_path, object_type, object_id, orders, revenue, created_at)
				 VALUES (%s, %s, %s, %s, %d, %d, %f, UTC_TIMESTAMP())
				 ON DUPLICATE KEY UPDATE
					entry_path = VALUES(entry_path), object_type = VALUES(object_type),
					object_id = VALUES(object_id), orders = VALUES(orders),
					revenue = VALUES(revenue), created_at = VALUES(created_at)",
				$date,
				md5( $path ),
				
				
				mb_substr( $path, 0, 191 ),
				$hit ? $hit['type'] : '',
				$hit ? $hit['id'] : 0,
				(int) $a['orders'],
				(float) $a['revenue']
			) );
			$written++;
		}
		return $written;
	}

	
	
	

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

	
	public static function revenue_measurable() {
		global $wpdb;
		return self::table_exists( $wpdb->prefix . 'wc_orders' );
	}

	private static function column_exists( $table, $column ) {
		global $wpdb;
		
		return (bool) $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$table}` LIKE %s", $column ) );
	}

	
	private static function ensure_column( $table, $column, $ddl ) {
		global $wpdb;
		if ( self::column_exists( $table, $column ) ) {
			return true;
		}
		
		return false !== $wpdb->query( "ALTER TABLE `{$table}` ADD COLUMN `{$column}` {$ddl}" );
	}

	
	private static function ensure_key( $table, $key, $column ) {
		global $wpdb;
		
		$exists = $wpdb->get_var( $wpdb->prepare( "SHOW INDEX FROM `{$table}` WHERE Key_name = %s", $key ) );
		if ( $exists ) {
			return true;
		}
		
		return false !== $wpdb->query( "ALTER TABLE `{$table}` ADD KEY `{$key}` (`{$column}`)" );
	}

	
	private static function charset() {
		global $wpdb;
		$collation = $wpdb->get_var( $wpdb->prepare(
			'SELECT TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s',
			$wpdb->posts
		) );
		if ( ! is_string( $collation ) || '' === $collation ) {
			return 'DEFAULT CHARACTER SET utf8mb4';
		}
		$charset = strtok( $collation, '_' );
		return "DEFAULT CHARACTER SET {$charset} COLLATE {$collation}";
	}
}