<?php

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

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

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',
			'landing_funnel_daily'   => $p . 'aieo_landing_funnel_daily',
		);
	}
}

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

		global $wpdb;
		$charset  = $wpdb->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,
			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['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 );
		}

		update_option( 'aieo_rollups_schema_version', AIEO_ROLLUPS_SCHEMA_VERSION, false );
	}
}
