<?php

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

class AIEO_Session_Attribution_Bridge {

	
	private static function resolve_period( $period ) {
		$allowed = array( 'today', 'yesterday', 'last_7_days', 'last_30_days', 'last_90_days', 'this_month', 'last_month' );
		$period  = is_string( $period ) ? $period : 'last_7_days';
		if ( ! in_array( $period, $allowed, true ) ) {
			$period = 'last_7_days';
		}
		$now   = current_time( 'timestamp', true );
		$today = strtotime( gmdate( 'Y-m-d 00:00:00', $now ) . ' UTC' );
		switch ( $period ) {
			case 'today':
				$since = $today; $until = $now; break;
			case 'yesterday':
				$since = $today - DAY_IN_SECONDS; $until = $today - 1; break;
			case 'last_30_days':
				$since = $today - 30 * DAY_IN_SECONDS; $until = $now; break;
			case 'last_90_days':
				$since = $today - 90 * DAY_IN_SECONDS; $until = $now; break;
			case 'this_month':
				$since = strtotime( gmdate( 'Y-m-01 00:00:00', $now ) . ' UTC' ); $until = $now; break;
			case 'last_month':
				$first_this = strtotime( gmdate( 'Y-m-01 00:00:00', $now ) . ' UTC' );
				$since      = strtotime( '-1 month', $first_this );
				$until      = $first_this - 1;
				break;
			case 'last_7_days':
			default:
				$since = $today - 7 * DAY_IN_SECONDS; $until = $now; break;
		}
		return array(
			'since_dt' => gmdate( 'Y-m-d H:i:s', $since ),
			'until_dt' => gmdate( 'Y-m-d H:i:s', $until ),
			'since_ts' => (int) $since,
			'until_ts' => (int) $until,
			'label'    => $period,
		);
	}

	
	private static function table_exists( $table ) {
		global $wpdb;
		return (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s",
			$table
		) ) > 0;
	}

	
	public static function get_session_journey_for_addcart( $event_id ) {
		global $wpdb;
		$event_id = (int) $event_id;
		if ( $event_id <= 0 ) {
			return new WP_Error( 'aieo_invalid_input', __( 'event_id is required.', 'ai-eshop-optimizer' ) );
		}
		$ae      = $wpdb->prefix . 'aieo_addcart_events';
		$ses     = $wpdb->prefix . 'aieo_sessions';
		$statics = $wpdb->prefix . 'aieo_sessions_statics';

		if ( ! self::table_exists( $ae ) ) {
			return array( 'note' => __( 'wp_aieo_addcart_events not installed.', 'ai-eshop-optimizer' ) );
		}

		$row = $wpdb->get_row( $wpdb->prepare(
			"SELECT id, event_id, session_id, user_id, product_id, source, source_pid, source_meta, ts_added
			   FROM {$ae}
			  WHERE id = %d",
			$event_id
		), ARRAY_A );
		if ( empty( $row ) ) {
			return new WP_Error( 'aieo_not_found', __( 'Cart-add event not found.', 'ai-eshop-optimizer' ) );
		}
		$session_id = (string) $row['session_id'];
		$product_id = (int) $row['product_id'];

		$journey = array(
			'event'              => $row,
			'pseudonym'          => (int) $row['user_id'] > 0 ? 'Customer #' . (int) $row['user_id'] : 'Anonymous',
			'landing_page'       => '',
			'referrer'           => '',
			'device_type'        => '',
			'prior_product_views' => array(),
			'converting_scroll_depth' => null,
			'converting_time_on_page' => null,
		);

		if ( self::table_exists( $statics ) ) {
			$stats = $wpdb->get_row( $wpdb->prepare(
				"SELECT landing_page, referrer, device_type, session_duration_seconds
				   FROM {$statics}
				  WHERE session_id = %s",
				$session_id
			), ARRAY_A );
			if ( ! empty( $stats ) ) {
				$journey['landing_page'] = (string) $stats['landing_page'];
				$journey['referrer']     = (string) $stats['referrer'];
				$journey['device_type']  = (string) $stats['device_type'];
				$journey['session_duration_seconds'] = (int) $stats['session_duration_seconds'];
			}
		}

		if ( self::table_exists( $ses ) ) {
			$ts_until = strtotime( $row['ts_added'] . ' UTC' );
			$views    = $wpdb->get_results( $wpdb->prepare(
				"SELECT product_id, timestamp, scroll_depth, time_on_page
				   FROM {$ses}
				  WHERE session_id = %s
				    AND event_type = 'product_view'
				    AND timestamp <= %d
				  ORDER BY timestamp ASC
				  LIMIT 50",
				$session_id, (int) $ts_until
			), ARRAY_A );
			foreach ( (array) $views as $v ) {
				$pid = (int) $v['product_id'];
				$journey['prior_product_views'][] = array(
					'product_id' => $pid,
					'timestamp'  => (int) $v['timestamp'],
				);
				if ( $pid === $product_id ) {
					$journey['converting_scroll_depth'] = $v['scroll_depth'] !== null ? (int) $v['scroll_depth'] : null;
					$journey['converting_time_on_page'] = $v['time_on_page'] !== null ? (int) $v['time_on_page'] : null;
				}
			}
		}

		return $journey;
	}

	
	public static function get_addcart_funnel_by_landing_page( $period ) {
		global $wpdb;
		$p   = self::resolve_period( $period );
		$lfd = $wpdb->prefix . 'aieo_landing_funnel_daily';

		if ( ! self::table_exists( $lfd ) ) {
			return array( 'period' => $p, 'rows' => array(), 'note' => __( 'wp_aieo_landing_funnel_daily not installed.', 'ai-eshop-optimizer' ) );
		}

		
		
		
		
		
		$rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT landing_page,
			        SUM(visitors)        AS visitors,
			        SUM(product_viewers) AS product_viewers,
			        SUM(cart_adders)     AS cart_adders,
			        SUM(cart_adds)       AS cart_adds
			   FROM {$lfd}
			  WHERE date BETWEEN DATE(%s) AND DATE(%s)
			  GROUP BY landing_page
			  ORDER BY visitors DESC
			  LIMIT 20",
			$p['since_dt'], $p['until_dt']
		), ARRAY_A );

		$out = array();
		foreach ( (array) $rows as $r ) {
			$visitors_n = (int) $r['visitors'];
			$viewers_n  = (int) $r['product_viewers'];
			$adders_n   = (int) $r['cart_adders'];
			$out[] = array(
				'landing_page'    => (string) $r['landing_page'],
				'visitors'        => $visitors_n,
				'product_viewers' => $viewers_n,
				'cart_adders'     => $adders_n,
				'cart_adds'       => (int) $r['cart_adds'],
				'view_rate'       => $visitors_n > 0 ? round( $viewers_n / $visitors_n, 4 ) : 0.0,
				'add_rate'        => $visitors_n > 0 ? round( $adders_n / $visitors_n, 4 ) : 0.0,
			);
		}
		usort( $out, static function ( $a, $b ) {
			return $b['cart_adds'] <=> $a['cart_adds'];
		} );
		return array( 'period' => $p, 'rows' => $out );
	}

	
	public static function get_attribution_with_engagement( $period ) {
		global $wpdb;
		$p       = self::resolve_period( $period );
		$ae      = $wpdb->prefix . 'aieo_addcart_events';
		$ses     = $wpdb->prefix . 'aieo_sessions';
		$statics = $wpdb->prefix . 'aieo_sessions_statics';

		if ( ! self::table_exists( $ae ) ) {
			return array( 'period' => $p, 'rows' => array(), 'note' => __( 'wp_aieo_addcart_events not installed.', 'ai-eshop-optimizer' ) );
		}

		$ae_rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT source,
			        COUNT(*)                          AS cart_adds,
			        COUNT(DISTINCT session_id)        AS sessions,
			        SUM(quantity)                     AS units,
			        SUM(unit_price * quantity)        AS revenue
			   FROM {$ae}
			  WHERE ts_added BETWEEN %s AND %s
			  GROUP BY source",
			$p['since_dt'], $p['until_dt']
		), ARRAY_A );

		$out = array();
		foreach ( (array) $ae_rows as $r ) {
			$out[ (string) $r['source'] ] = array(
				'source'                       => (string) $r['source'],
				'cart_adds'                    => (int) $r['cart_adds'],
				'sessions'                     => (int) $r['sessions'],
				'units'                        => (int) $r['units'],
				'revenue'                      => (float) $r['revenue'],
				'avg_session_duration_seconds' => null,
				'avg_scroll_depth'             => null,
				'avg_time_on_page'             => null,
			);
		}

		if ( self::table_exists( $statics ) ) {
			$eng_dur = $wpdb->get_results( $wpdb->prepare(
				"SELECT a.source AS source,
				        AVG(s.session_duration_seconds) AS avg_dur
				   FROM {$ae} a
				   JOIN {$statics} s ON s.session_id = a.session_id
				  WHERE a.ts_added BETWEEN %s AND %s
				  GROUP BY a.source",
				$p['since_dt'], $p['until_dt']
			), ARRAY_A );
			foreach ( (array) $eng_dur as $r ) {
				$src = (string) $r['source'];
				if ( isset( $out[ $src ] ) ) {
					$out[ $src ]['avg_session_duration_seconds'] = $r['avg_dur'] !== null ? round( (float) $r['avg_dur'], 2 ) : null;
				}
			}
		}

		
		
		
		
		
		
		$ad = $wpdb->prefix . 'aieo_attribution_daily';
		if ( self::table_exists( $ad ) ) {
			$eng_pv = $wpdb->get_results( $wpdb->prepare(
				"SELECT source,
				        SUM(avg_scroll_pct * eng_view_n) / NULLIF(SUM(eng_view_n),0) AS avg_scroll,
				        SUM(avg_time_sec  * eng_view_n) / NULLIF(SUM(eng_view_n),0) AS avg_time,
				        SUM(eng_view_n) AS n
				   FROM {$ad}
				  WHERE date BETWEEN DATE(%s) AND DATE(%s)
				  GROUP BY source",
				$p['since_dt'], $p['until_dt']
			), ARRAY_A );
			foreach ( (array) $eng_pv as $r ) {
				$src = (string) $r['source'];
				if ( isset( $out[ $src ] ) && (int) $r['n'] > 0 ) {
					$out[ $src ]['avg_scroll_depth'] = $r['avg_scroll'] !== null ? round( (float) $r['avg_scroll'], 2 ) : null;
					$out[ $src ]['avg_time_on_page'] = $r['avg_time']   !== null ? round( (float) $r['avg_time'], 2 ) : null;
				}
			}
		}

		$rows = array_values( $out );
		usort( $rows, static function ( $a, $b ) {
			return $b['cart_adds'] <=> $a['cart_adds'];
		} );
		return array( 'period' => $p, 'rows' => $rows );
	}

	
	public static function get_abandoned_carts( $period, $min_value = 0 ) {
		global $wpdb;
		$p   = self::resolve_period( $period );
		$ae  = $wpdb->prefix . 'aieo_addcart_events';
		$ses = $wpdb->prefix . 'aieo_sessions';
		$min_value = max( 0.0, (float) $min_value );

		if ( ! self::table_exists( $ae ) ) {
			return array( 'period' => $p, 'rows' => array(), 'note' => __( 'wp_aieo_addcart_events not installed.', 'ai-eshop-optimizer' ) );
		}

		
		$ae_rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT session_id,
			        user_id,
			        COUNT(*)                                                 AS adds,
			        SUM(quantity * unit_price)                               AS est_value,
			        MAX(ts_added)                                            AS last_activity,
			        MAX(order_id)                                            AS any_order
			   FROM {$ae}
			  WHERE ts_added BETWEEN %s AND %s
			  GROUP BY session_id, user_id
			 HAVING est_value >= %f
			  ORDER BY est_value DESC
			  LIMIT 100",
			$p['since_dt'], $p['until_dt'], $min_value
		), ARRAY_A );
		if ( empty( $ae_rows ) ) {
			return array( 'period' => $p, 'rows' => array() );
		}

		
		
		$purchased = array();
		if ( self::table_exists( $ses ) ) {
			
			$ids = array_map( static function ( $r ) { return (string) $r['session_id']; }, $ae_rows );
			if ( ! empty( $ids ) ) {
				$placeholders = implode( ',', array_fill( 0, count( $ids ), '%s' ) );
				$args         = array_merge( $ids, array( $p['since_ts'], $p['until_ts'] ) );
				$rows         = $wpdb->get_col( $wpdb->prepare(
					"SELECT DISTINCT session_id
					   FROM {$ses}
					  WHERE session_id IN ({$placeholders})
					    AND event_type = 'purchase_complete'
					    AND timestamp BETWEEN %d AND %d",
					$args
				) );
				foreach ( (array) $rows as $sid ) { $purchased[ (string) $sid ] = true; }
			}
		}

		$out = array();
		foreach ( $ae_rows as $r ) {
			$sid = (string) $r['session_id'];
			if ( isset( $purchased[ $sid ] ) ) { continue; }
			if ( (int) $r['any_order'] > 0 ) { continue; }
			$uid = (int) $r['user_id'];

			
			$items = $wpdb->get_results( $wpdb->prepare(
				"SELECT product_id, SUM(quantity) AS qty, SUM(quantity * unit_price) AS subtotal
				   FROM {$ae}
				  WHERE session_id = %s
				    AND ts_added BETWEEN %s AND %s
				  GROUP BY product_id
				  ORDER BY qty DESC
				  LIMIT 5",
				$sid, $p['since_dt'], $p['until_dt']
			), ARRAY_A );
			$products = array();
			foreach ( (array) $items as $it ) {
				$products[] = array(
					'product_id' => (int) $it['product_id'],
					'quantity'   => (int) $it['qty'],
					'subtotal'   => (float) $it['subtotal'],
				);
			}

			$out[] = array(
				'pseudonym'        => $uid > 0 ? 'Customer #' . $uid : 'Anonymous',
				'session_id'       => $sid,
				'adds'             => (int) $r['adds'],
				'est_value'        => round( (float) $r['est_value'], 2 ),
				'last_activity_ts' => (string) $r['last_activity'],
				'products'         => $products,
			);
		}
		return array( 'period' => $p, 'min_value' => $min_value, 'rows' => $out );
	}

	
	public static function get_cart_removal_patterns( $period ) {
		global $wpdb;
		$p   = self::resolve_period( $period );
		$crd = $wpdb->prefix . 'aieo_cart_removal_daily';

		if ( ! self::table_exists( $crd ) ) {
			return array( 'period' => $p, 'rows' => array(), 'note' => __( 'wp_aieo_cart_removal_daily not installed.', 'ai-eshop-optimizer' ) );
		}

		
		
		
		
		
		$rows_db = $wpdb->get_results( $wpdb->prepare(
			"SELECT product_id, SUM(adds) AS adds, SUM(removes) AS removes
			   FROM {$crd}
			  WHERE date BETWEEN DATE(%s) AND DATE(%s)
			  GROUP BY product_id
			 HAVING removes > 0",
			$p['since_dt'], $p['until_dt']
		), ARRAY_A );

		$rows = array();
		foreach ( (array) $rows_db as $r ) {
			$pid       = (int) $r['product_id'];
			$add_count = (int) $r['adds'];
			$rm        = (int) $r['removes'];
			$rows[] = array(
				'product_id'   => $pid,
				'product_name' => get_the_title( $pid ),
				'adds'         => $add_count,
				'removes'      => $rm,
				'remove_rate'  => $add_count > 0 ? round( $rm / $add_count, 4 ) : 0.0,
			);
		}
		usort( $rows, static function ( $a, $b ) {
			if ( $b['remove_rate'] === $a['remove_rate'] ) {
				return $b['removes'] <=> $a['removes'];
			}
			return $b['remove_rate'] <=> $a['remove_rate'];
		} );
		return array( 'period' => $p, 'rows' => array_slice( $rows, 0, 50 ) );
	}

	
	public static function get_rating_vs_conversion( $period ) {
		global $wpdb;
		$p   = self::resolve_period( $period );
		$ae  = $wpdb->prefix . 'aieo_addcart_events';
		$ses = $wpdb->prefix . 'aieo_sessions';
		$rs  = $wpdb->prefix . 'aieo_rating_stats';

		if ( ! self::table_exists( $rs ) || ! self::table_exists( $ae ) ) {
			return array( 'period' => $p, 'rows' => array(), 'note' => __( 'Rating-stats or addcart tables not installed.', 'ai-eshop-optimizer' ) );
		}

		
		$buckets_def = array(
			'1.0-2.0' => array( 1.0, 2.0 ),
			'2.0-3.0' => array( 2.0, 3.0 ),
			'3.0-4.0' => array( 3.0, 4.0 ),
			'4.0-5.0' => array( 4.0, 5.001 ),
		);

		$rating_rows = $wpdb->get_results(
			"SELECT product_id, sum_total, count_total FROM {$rs} WHERE count_total > 0"
		, ARRAY_A );
		$product_bucket = array();
		foreach ( (array) $rating_rows as $r ) {
			$ct = (int) $r['count_total'];
			if ( $ct <= 0 ) { continue; }
			$avg = (float) $r['sum_total'] / max( 1, $ct );
			$bucket = null;
			foreach ( $buckets_def as $label => $range ) {
				if ( $avg >= $range[0] && $avg < $range[1] ) { $bucket = $label; break; }
			}
			if ( $bucket !== null ) {
				$product_bucket[ (int) $r['product_id'] ] = $bucket;
			}
		}

		
		
		
		
		
		
		
		
		$ed = $wpdb->prefix . 'aieo_engagement_daily';
		$ed_rows = self::table_exists( $ed ) ? $wpdb->get_results( $wpdb->prepare(
			"SELECT product_id, SUM(product_views) AS views, SUM(cart_adds) AS cart_adds
			   FROM {$ed}
			  WHERE date BETWEEN DATE(%s) AND DATE(%s)
			  GROUP BY product_id",
			$p['since_dt'], $p['until_dt']
		), ARRAY_A ) : array();

		$agg = array();
		foreach ( array_keys( $buckets_def ) as $label ) {
			$agg[ $label ] = array( 'bucket' => $label, 'products' => 0, 'views' => 0, 'cart_adds' => 0 );
		}
		foreach ( (array) $ed_rows as $v ) {
			$pid = (int) $v['product_id'];
			if ( ! isset( $product_bucket[ $pid ] ) ) { continue; }
			$label = $product_bucket[ $pid ];
			$agg[ $label ]['views']     += (int) $v['views'];
			$agg[ $label ]['cart_adds'] += (int) $v['cart_adds'];
		}
		$bucket_products = array();
		foreach ( $product_bucket as $pid => $label ) {
			if ( ! isset( $bucket_products[ $label ] ) ) { $bucket_products[ $label ] = 0; }
			$bucket_products[ $label ]++;
		}
		foreach ( $agg as $label => &$row ) {
			$row['products']      = isset( $bucket_products[ $label ] ) ? $bucket_products[ $label ] : 0;
			$row['cart_add_rate'] = $row['views'] > 0 ? round( $row['cart_adds'] / $row['views'], 4 ) : null;
		}
		unset( $row );

		return array( 'period' => $p, 'rows' => array_values( $agg ) );
	}

	
	public static function get_need_vs_conversion( $period ) {
		global $wpdb;
		$p   = self::resolve_period( $period );
		$ae  = $wpdb->prefix . 'aieo_addcart_events';
		$ses = $wpdb->prefix . 'aieo_sessions';
		$tr  = $wpdb->term_relationships;
		$tt  = $wpdb->term_taxonomy;
		$t   = $wpdb->terms;

		if ( ! self::table_exists( $ae ) ) {
			return array( 'period' => $p, 'rows' => array(), 'note' => __( 'wp_aieo_addcart_events not installed.', 'ai-eshop-optimizer' ) );
		}

		
		$need_rows = $wpdb->get_results(
			"SELECT tr.object_id AS product_id, t.term_id AS term_id, t.name AS name
			   FROM {$tr} tr
			   JOIN {$tt} tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
			   JOIN {$t}  t  ON t.term_id = tt.term_id
			  WHERE tt.taxonomy = 'pa_need'"
		, ARRAY_A );
		$product_terms = array();
		$term_names    = array();
		foreach ( (array) $need_rows as $r ) {
			$pid = (int) $r['product_id'];
			$tid = (int) $r['term_id'];
			$product_terms[ $pid ][] = $tid;
			$term_names[ $tid ]      = (string) $r['name'];
		}

		
		
		
		
		
		$ed = $wpdb->prefix . 'aieo_engagement_daily';
		$ed_rows = self::table_exists( $ed ) ? $wpdb->get_results( $wpdb->prepare(
			"SELECT product_id, SUM(product_views) AS views, SUM(cart_adds) AS cart_adds
			   FROM {$ed}
			  WHERE date BETWEEN DATE(%s) AND DATE(%s)
			  GROUP BY product_id",
			$p['since_dt'], $p['until_dt']
		), ARRAY_A ) : array();

		$agg = array();
		foreach ( (array) $ed_rows as $v ) {
			$pid = (int) $v['product_id'];
			if ( empty( $product_terms[ $pid ] ) ) { continue; }
			foreach ( $product_terms[ $pid ] as $tid ) {
				if ( ! isset( $agg[ $tid ] ) ) {
					$agg[ $tid ] = array( 'term_id' => $tid, 'name' => $term_names[ $tid ] ?? '', 'views' => 0, 'cart_adds' => 0 );
				}
				$agg[ $tid ]['views']     += (int) $v['views'];
				$agg[ $tid ]['cart_adds'] += (int) $v['cart_adds'];
			}
		}
		foreach ( $agg as &$row ) {
			$row['cart_add_rate'] = $row['views'] > 0 ? round( $row['cart_adds'] / $row['views'], 4 ) : null;
		}
		unset( $row );

		$out = array_values( $agg );
		usort( $out, static function ( $a, $b ) {
			return $b['cart_adds'] <=> $a['cart_adds'];
		} );
		return array( 'period' => $p, 'rows' => $out );
	}

	
	public static function get_blog_engagement_leaderboard( $period, $limit = 20 ) {
		global $wpdb;
		$p   = self::resolve_period( $period );
		$ses = $wpdb->prefix . 'aieo_sessions';
		$limit = max( 1, min( 100, (int) $limit ) );

		
		
		
		
		
		
		$bed = $wpdb->prefix . 'aieo_blog_engagement_daily';
		if ( ! self::table_exists( $bed ) ) {
			return array( 'period' => $p, 'rows' => array(), 'note' => __( 'wp_aieo_blog_engagement_daily not installed.', 'ai-eshop-optimizer' ) );
		}

		$rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT post_id,
			        SUM(views)    AS views,
			        SUM(visitors) AS visitors,
			        SUM(views * avg_time_sec)   / NULLIF(SUM(views),0) AS avg_time_on_page,
			        SUM(views * avg_scroll_pct) / NULLIF(SUM(views),0) AS avg_scroll_depth
			   FROM {$bed}
			  WHERE date BETWEEN DATE(%s) AND DATE(%s)
			  GROUP BY post_id
			  ORDER BY views DESC
			  LIMIT %d",
			$p['since_dt'], $p['until_dt'], $limit
		), ARRAY_A );

		$out = array();
		foreach ( (array) $rows as $r ) {
			$pid = (int) $r['post_id'];
			$out[] = array(
				'post_id'          => $pid,
				'title'            => get_the_title( $pid ),
				'page_url'         => get_permalink( $pid ),
				'views'            => (int) $r['views'],
				'visitors'         => (int) $r['visitors'],
				'avg_time_on_page' => $r['avg_time_on_page'] !== null ? round( (float) $r['avg_time_on_page'], 2 ) : null,
				'avg_scroll_depth' => $r['avg_scroll_depth'] !== null ? round( (float) $r['avg_scroll_depth'], 2 ) : null,
			);
		}
		return array( 'period' => $p, 'rows' => $out );
	}

	
	public static function get_blog_to_product_conversion( $period ) {
		global $wpdb;
		$p   = self::resolve_period( $period );
		$ses = $wpdb->prefix . 'aieo_sessions';
		$ae  = $wpdb->prefix . 'aieo_addcart_events';

		if ( ! self::table_exists( $ses ) ) {
			return array( 'period' => $p, 'rows' => array(), 'note' => __( 'wp_aieo_sessions not installed.', 'ai-eshop-optimizer' ) );
		}

		
		$pages = $wpdb->get_results( $wpdb->prepare(
			"SELECT page_url,
			        COUNT(DISTINCT session_id) AS visitors,
			        MIN(timestamp)             AS first_seen_ts
			   FROM {$ses}
			  WHERE event_type = 'page_view'
			    AND product_id IS NULL
			    AND category_id IS NULL
			    AND page_url IS NOT NULL
			    AND page_url <> ''
			    AND timestamp BETWEEN %d AND %d
			  GROUP BY page_url
			  ORDER BY visitors DESC
			  LIMIT 20",
			$p['since_ts'], $p['until_ts']
		), ARRAY_A );
		if ( empty( $pages ) ) {
			return array( 'period' => $p, 'rows' => array() );
		}

		$rows = array();
		foreach ( (array) $pages as $pg ) {
			$page_url = (string) $pg['page_url'];

			
			
			$prod_viewers = (int) $wpdb->get_var( $wpdb->prepare(
				"SELECT COUNT(DISTINCT pv.session_id)
				   FROM {$ses} bv
				   JOIN {$ses} pv
				     ON pv.session_id = bv.session_id
				    AND pv.event_type = 'product_view'
				    AND pv.timestamp >= bv.timestamp
				  WHERE bv.event_type = 'page_view'
				    AND bv.page_url   = %s
				    AND bv.timestamp BETWEEN %d AND %d",
				$page_url, $p['since_ts'], $p['until_ts']
			) );

			
			
			$ae_exists = self::table_exists( $ae );
			$blog_post_adders   = 0;
			$same_session_adders = 0;
			if ( $ae_exists ) {
				$blog_post_adders = (int) $wpdb->get_var( $wpdb->prepare(
					"SELECT COUNT(DISTINCT session_id)
					   FROM {$ae}
					  WHERE source = 'blog_post'
					    AND source_meta LIKE %s
					    AND ts_added BETWEEN %s AND %s",
					'%' . $wpdb->esc_like( $page_url ) . '%',
					$p['since_dt'], $p['until_dt']
				) );

				$same_session_adders = (int) $wpdb->get_var( $wpdb->prepare(
					"SELECT COUNT(DISTINCT a.session_id)
					   FROM {$ses} bv
					   JOIN {$ae} a ON a.session_id = bv.session_id
					  WHERE bv.event_type = 'page_view'
					    AND bv.page_url   = %s
					    AND bv.timestamp BETWEEN %d AND %d
					    AND a.ts_added BETWEEN %s AND %s",
					$page_url, $p['since_ts'], $p['until_ts'], $p['since_dt'], $p['until_dt']
				) );
			}

			$rows[] = array(
				'page_url'              => $page_url,
				'visitors'              => (int) $pg['visitors'],
				'product_viewers'       => $prod_viewers,
				'cart_adders_blog_post' => $blog_post_adders,
				'cart_adders_session'   => $same_session_adders,
				'cart_add_rate'         => (int) $pg['visitors'] > 0 ? round( $same_session_adders / (int) $pg['visitors'], 4 ) : 0.0,
			);
		}
		return array( 'period' => $p, 'rows' => $rows );
	}
}