<?php

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

if ( ! defined( 'AIEO_ROLLUPS_SCHEMA_VERSION' ) ) {
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	define( 'AIEO_ROLLUPS_SCHEMA_VERSION', 11 );
}

if ( ! function_exists( 'aieo_rollups_table_names' ) ) {
	
	function aieo_rollups_table_names() {
		global $wpdb;
		$p = $wpdb->prefix;
		return array(
			'engagement_daily'       => $p . 'aieo_engagement_daily',
			'attribution_daily'      => $p . 'aieo_attribution_daily',
			'conversion_buckets'     => $p . 'aieo_conversion_buckets',
			'blog_engagement_daily'  => $p . 'aieo_blog_engagement_daily',
			'cart_removal_daily'     => $p . 'aieo_cart_removal_daily',
			'reco_daily'             => $p . 'aieo_reco_daily',
			'reco_surface_daily'     => $p . 'aieo_reco_surface_daily',
			'reco_arm_daily'         => $p . 'aieo_reco_arm_daily',
			'reco_origin_daily'      => $p . 'aieo_reco_origin_daily',
			'landing_funnel_daily'   => $p . 'aieo_landing_funnel_daily',
		);
	}
}

if ( ! function_exists( 'maybe_install_rollup_schema' ) ) {
	
	function maybe_install_rollup_schema() {
		
		
		
		
		if ( (int) get_option( 'aieo_rollups_schema_version', 0 ) >= AIEO_ROLLUPS_SCHEMA_VERSION ) {
			return;
		}

		
		
		if ( ! function_exists( 'aieo_install_schema_once' ) && defined( 'AIEO_DIR' ) && file_exists( AIEO_DIR . 'includes/sql/sql-runtime-helpers.php' ) ) {
			require_once AIEO_DIR . 'includes/sql/sql-runtime-helpers.php';
		}

		if ( ! function_exists( 'aieo_install_schema_once' ) ) {
			
			
			aieo_install_rollup_schema_ddl();
			update_option( 'aieo_rollups_schema_version', AIEO_ROLLUPS_SCHEMA_VERSION, false );
			return;
		}

		aieo_install_schema_once(
			'aieo_rollups_schema_version',
			AIEO_ROLLUPS_SCHEMA_VERSION,
			'aieo_install_rollup_schema_ddl'
		);
	}
}

if ( ! function_exists( 'aieo_install_rollup_schema_ddl' ) ) {
	
	function aieo_install_rollup_schema_ddl() {
		global $wpdb;
		$charset  = aieo_get_charset_collate();
		$tables   = aieo_rollups_table_names();

		if ( ! function_exists( 'dbDelta' ) ) {
			require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		}

		$ddl = array();

		$ddl[] = "CREATE TABLE {$tables['engagement_daily']} (
			date DATE NOT NULL,
			product_id BIGINT UNSIGNED NOT NULL,
			views INT UNSIGNED NOT NULL DEFAULT 0,
			product_views INT UNSIGNED NOT NULL DEFAULT 0,
			unique_sessions INT UNSIGNED NOT NULL DEFAULT 0,
			avg_time_sec FLOAT NOT NULL DEFAULT 0,
			avg_scroll_pct FLOAT NOT NULL DEFAULT 0,
			avg_clicks FLOAT NOT NULL DEFAULT 0,
			cart_adds INT UNSIGNED NOT NULL DEFAULT 0,
			unique_cart_sessions INT UNSIGNED NOT NULL DEFAULT 0,
			cart_add_rate FLOAT NOT NULL DEFAULT 0,
			landings INT UNSIGNED NOT NULL DEFAULT 0,
			last_page INT UNSIGNED NOT NULL DEFAULT 0,
			bounces INT UNSIGNED NOT NULL DEFAULT 0,
			avg_time_to_cart FLOAT NOT NULL DEFAULT 0,
			occupancies INT UNSIGNED NOT NULL DEFAULT 0,
			avg_engaged_sec FLOAT NOT NULL DEFAULT 0,
			terminated_occupancies INT UNSIGNED NOT NULL DEFAULT 0,
			avg_away_sec FLOAT NOT NULL DEFAULT 0,
			unterminated_occupancies INT UNSIGNED NOT NULL DEFAULT 0,
			parked_occupancies INT UNSIGNED NOT NULL DEFAULT 0,
			avg_hidden_sec FLOAT NOT NULL DEFAULT 0,
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY  (date, product_id),
			KEY product_date (product_id, date)
		) {$charset};";

		$ddl[] = "CREATE TABLE {$tables['attribution_daily']} (
			date DATE NOT NULL,
			source VARCHAR(64) NOT NULL DEFAULT 'unknown',
			archive_term_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
			archive_term_tax VARCHAR(64) NOT NULL DEFAULT '',
			cart_adds INT UNSIGNED NOT NULL DEFAULT 0,
			units INT UNSIGNED NOT NULL DEFAULT 0,
			sessions INT UNSIGNED NOT NULL DEFAULT 0,
			converted_adds INT UNSIGNED NOT NULL DEFAULT 0,
			avg_scroll_pct FLOAT NOT NULL DEFAULT 0,
			avg_time_sec FLOAT NOT NULL DEFAULT 0,
			eng_view_n INT UNSIGNED NOT NULL DEFAULT 0,
			revenue DECIMAL(12,2) NOT NULL DEFAULT 0.00,
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY  (date, source, archive_term_id),
			KEY source_date (source, date)
		) {$charset};";

		$ddl[] = "CREATE TABLE {$tables['conversion_buckets']} (
			product_id BIGINT UNSIGNED NOT NULL,
			scroll_quartile TINYINT UNSIGNED NOT NULL,
			time_quartile TINYINT UNSIGNED NOT NULL,
			viewers INT UNSIGNED NOT NULL DEFAULT 0,
			cart_adders INT UNSIGNED NOT NULL DEFAULT 0,
			p_cart_add FLOAT NOT NULL DEFAULT 0,
			computed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY  (product_id, scroll_quartile, time_quartile)
		) {$charset};";

		$ddl[] = "CREATE TABLE {$tables['blog_engagement_daily']} (
			date DATE NOT NULL,
			post_id BIGINT UNSIGNED NOT NULL,
			views INT UNSIGNED NOT NULL DEFAULT 0,
			visitors INT UNSIGNED NOT NULL DEFAULT 0,
			avg_time_sec FLOAT NOT NULL DEFAULT 0,
			avg_scroll_pct FLOAT NOT NULL DEFAULT 0,
			downstream_cart_adds INT UNSIGNED NOT NULL DEFAULT 0,
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY  (date, post_id),
			KEY post_date (post_id, date)
		) {$charset};";

		$ddl[] = "CREATE TABLE {$tables['cart_removal_daily']} (
			date DATE NOT NULL,
			product_id BIGINT UNSIGNED NOT NULL,
			adds INT UNSIGNED NOT NULL DEFAULT 0,
			removes INT UNSIGNED NOT NULL DEFAULT 0,
			remove_rate FLOAT NOT NULL DEFAULT 0,
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY  (date, product_id),
			KEY product_date (product_id, date)
		) {$charset};";

		$ddl[] = "CREATE TABLE {$tables['reco_daily']} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			date DATE NOT NULL,
			surface VARCHAR(40) NOT NULL DEFAULT 'unknown',
			anchor_pid BIGINT UNSIGNED NOT NULL DEFAULT 0,
			target_pid BIGINT UNSIGNED NOT NULL DEFAULT 0,
			anchor_term_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
			anchor_term_tax VARCHAR(32) NOT NULL DEFAULT '',
			impressions INT UNSIGNED NOT NULL DEFAULT 0,
			reco_clicks INT UNSIGNED NOT NULL DEFAULT 0,
			sessions INT UNSIGNED NOT NULL DEFAULT 0,
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY  (id),
			UNIQUE KEY grain (date, surface, anchor_pid, target_pid, anchor_term_id, anchor_term_tax),
			KEY surface_date (surface, date),
			KEY term_date (anchor_term_id, date)
		) {$charset};";

		
		
		
		
		
		
		
		
		
		$ddl[] = "CREATE TABLE {$tables['reco_surface_daily']} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			stat_date DATE NOT NULL,
			surface VARCHAR(40) NOT NULL DEFAULT 'unknown',
			position SMALLINT UNSIGNED NOT NULL DEFAULT 0,
			impressions INT UNSIGNED NOT NULL DEFAULT 0,
			clicks INT UNSIGNED NOT NULL DEFAULT 0,
			suppressions INT UNSIGNED NOT NULL DEFAULT 0,
			assisted_adds INT UNSIGNED NOT NULL DEFAULT 0,
			assisted_purchases INT UNSIGNED NOT NULL DEFAULT 0,
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY  (id),
			UNIQUE KEY grain (stat_date, surface, position),
			KEY surface_date (surface, stat_date)
		) {$charset};";

		
		
		
		
		
		
		
		
		
		
		
		$ddl[] = "CREATE TABLE {$tables['reco_arm_daily']} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			stat_date DATE NOT NULL,
			surface VARCHAR(40) NOT NULL DEFAULT 'unknown',
			arm VARCHAR(16) NOT NULL DEFAULT '',
			impressions INT UNSIGNED NOT NULL DEFAULT 0,
			clicks INT UNSIGNED NOT NULL DEFAULT 0,
			suppressions INT UNSIGNED NOT NULL DEFAULT 0,
			assisted_adds INT UNSIGNED NOT NULL DEFAULT 0,
			assisted_purchases INT UNSIGNED NOT NULL DEFAULT 0,
			sessions INT UNSIGNED NOT NULL DEFAULT 0,
			click_sessions INT UNSIGNED NOT NULL DEFAULT 0,
			add_sessions INT UNSIGNED NOT NULL DEFAULT 0,
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY  (id),
			UNIQUE KEY grain (stat_date, surface, arm),
			KEY arm_date (arm, stat_date)
		) {$charset};";

		
		
		
		
		
		
		
		
		
		
		
		$ddl[] = "CREATE TABLE {$tables['reco_origin_daily']} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			stat_date DATE NOT NULL,
			surface VARCHAR(40) NOT NULL DEFAULT 'unknown',
			arm VARCHAR(16) NOT NULL DEFAULT '',
			origin_id INT UNSIGNED NOT NULL DEFAULT 0,
			impressions INT UNSIGNED NOT NULL DEFAULT 0,
			clicks INT UNSIGNED NOT NULL DEFAULT 0,
			suppressions INT UNSIGNED NOT NULL DEFAULT 0,
			assisted_adds INT UNSIGNED NOT NULL DEFAULT 0,
			assisted_purchases INT UNSIGNED NOT NULL DEFAULT 0,
			sessions INT UNSIGNED NOT NULL DEFAULT 0,
			click_sessions INT UNSIGNED NOT NULL DEFAULT 0,
			add_sessions INT UNSIGNED NOT NULL DEFAULT 0,
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY  (id),
			UNIQUE KEY grain (stat_date, surface, arm, origin_id),
			KEY origin_date (origin_id, stat_date)
		) {$charset};";

		$ddl[] = "CREATE TABLE {$tables['landing_funnel_daily']} (
			date DATE NOT NULL,
			landing_page VARCHAR(190) NOT NULL DEFAULT '',
			visitors INT UNSIGNED NOT NULL DEFAULT 0,
			product_viewers INT UNSIGNED NOT NULL DEFAULT 0,
			cart_adders INT UNSIGNED NOT NULL DEFAULT 0,
			cart_adds INT UNSIGNED NOT NULL DEFAULT 0,
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY  (date, landing_page),
			KEY visitors_date (visitors, date)
		) {$charset};";

		foreach ( $ddl as $sql ) {
			dbDelta( $sql );
		}
	}
}