<?php

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

class AIEO_Product_Lifetime {

	const TABLE = 'aieo_product_lifetime';

	
	const HOOK = 'aieo_product_lifetime_build';

	const SCHEMA_OPTION  = 'aieo_product_lifetime_schema';
	const SCHEMA_VERSION = 1;

	public static function table() {
		global $wpdb;
		return $wpdb->prefix . self::TABLE;
	}

	
	public static function maybe_install() {
		if ( (int) get_option( self::SCHEMA_OPTION, 0 ) >= self::SCHEMA_VERSION ) {
			return;
		}
		if ( ! function_exists( 'aieo_install_schema_once' ) ) {
			require_once AIEO_DIR . 'includes/sql/sql-runtime-helpers.php';
		}
		aieo_install_schema_once( self::SCHEMA_OPTION, self::SCHEMA_VERSION, array( __CLASS__, 'install' ) );
	}

	public static function install() {
		global $wpdb;
		$t       = self::table();
		$collate = function_exists( 'aieo_get_charset_collate' ) ? aieo_get_charset_collate() : $wpdb->get_charset_collate();
		return false !== $wpdb->query( "CREATE TABLE IF NOT EXISTS `{$t}` (
			`product_id` BIGINT UNSIGNED NOT NULL,
			`views` BIGINT UNSIGNED NOT NULL DEFAULT 0,
			`product_views` BIGINT UNSIGNED NOT NULL DEFAULT 0,
			`product_views_30d` INT UNSIGNED NOT NULL DEFAULT 0,
			`sessions` BIGINT UNSIGNED NOT NULL DEFAULT 0,
			`cart_adds` BIGINT UNSIGNED NOT NULL DEFAULT 0,
			`cart_sessions` BIGINT UNSIGNED NOT NULL DEFAULT 0,
			`landings` BIGINT UNSIGNED NOT NULL DEFAULT 0,
			`exits` BIGINT UNSIGNED NOT NULL DEFAULT 0,
			`bounces` BIGINT UNSIGNED NOT NULL DEFAULT 0,
			`avg_time_sec` DOUBLE DEFAULT NULL,
			`avg_scroll_pct` DOUBLE DEFAULT NULL,
			`occupancies` BIGINT UNSIGNED NOT NULL DEFAULT 0,
			`avg_engaged_sec` DOUBLE DEFAULT NULL,
			`first_date` DATE DEFAULT NULL,
			`first_viewed_date` DATE DEFAULT NULL,
			`last_viewed_date` DATE DEFAULT NULL,
			`updated_at` DATETIME NOT NULL,
			PRIMARY KEY (`product_id`),
			KEY `product_views` (`product_views`)
		) ENGINE=InnoDB {$collate}" );
	}

	
	public static function queue() {
		if ( ! wp_next_scheduled( self::HOOK ) ) {
			wp_schedule_single_event( time() + 3 * MINUTE_IN_SECONDS, self::HOOK );
		}
	}

	
	public static function rebuild() {
		global $wpdb;
		$daily = $wpdb->prefix . 'aieo_engagement_daily';
		if ( (string) $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $daily ) ) ) !== $daily ) {
			return 0;
		}
		self::maybe_install();
		$t     = self::table();
		$stamp = gmdate( 'Y-m-d H:i:s' );
		
		$since = gmdate( 'Y-m-d', time() - 30 * DAY_IN_SECONDS );
		$ok    = $wpdb->query( $wpdb->prepare(
			"INSERT INTO `{$t}`
				(product_id, views, product_views, product_views_30d, sessions, cart_adds, cart_sessions, landings, exits, bounces,
				 avg_time_sec, avg_scroll_pct, occupancies, avg_engaged_sec, first_date, first_viewed_date, last_viewed_date, updated_at)
			 SELECT product_id, SUM(views), SUM(product_views), SUM(CASE WHEN `date` >= %s THEN product_views ELSE 0 END),
				SUM(unique_sessions), SUM(cart_adds), SUM(unique_cart_sessions), SUM(landings), SUM(last_page), SUM(bounces),
				SUM(avg_time_sec * views) / NULLIF(SUM(views), 0), SUM(avg_scroll_pct * views) / NULLIF(SUM(views), 0),
				SUM(occupancies), SUM(CASE WHEN occupancies > 0 THEN avg_engaged_sec * occupancies ELSE 0 END) / NULLIF(SUM(occupancies), 0),
				MIN(`date`), MIN(CASE WHEN product_views > 0 THEN `date` END), MAX(CASE WHEN product_views > 0 THEN `date` END), %s
			   FROM `{$daily}` WHERE product_id > 0 GROUP BY product_id
			 ON DUPLICATE KEY UPDATE
				views = VALUES(views), product_views = VALUES(product_views), product_views_30d = VALUES(product_views_30d),
				sessions = VALUES(sessions), cart_adds = VALUES(cart_adds), cart_sessions = VALUES(cart_sessions),
				landings = VALUES(landings), exits = VALUES(exits), bounces = VALUES(bounces),
				avg_time_sec = VALUES(avg_time_sec), avg_scroll_pct = VALUES(avg_scroll_pct),
				occupancies = VALUES(occupancies), avg_engaged_sec = VALUES(avg_engaged_sec),
				first_date = VALUES(first_date), first_viewed_date = VALUES(first_viewed_date), last_viewed_date = VALUES(last_viewed_date),
				updated_at = VALUES(updated_at)",
			$since,
			$stamp
		) );
		if ( false === $ok ) {
			return 0; 
		}
		$wpdb->query(
			"DELETE l FROM `{$t}` l
			   LEFT JOIN ( SELECT DISTINCT product_id FROM `{$daily}` WHERE product_id > 0 ) d ON d.product_id = l.product_id
			  WHERE d.product_id IS NULL"
		);
		return (int) $wpdb->get_var( "SELECT COUNT(*) FROM `{$t}`" );
	}
}