<?php

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

class AIEO_Visitor_Lifetime {

	const HOOK = 'aieo_visitor_lifetime_tick';
	const MORE = 'aieo_visitor_lifetime_more'; 
	const LOCK = 'aieo_visitor_lifetime';

	
	const MARK = 'aieo_visitor_lifetime_mark';

	
	const WINDOW_MS = 21600000;

	
	const SETTLE_START_MS = 172800000;

	
	const SETTLE_LAST_MS = 86400000;

	
	const FORCE_MS = 604800000;

	
	const BUDGET = 20.0;

	
	const COLUMNS = array( 'session_id', 'first_timestamp', 'last_timestamp', 'total_product_views', 'total_add_to_cart', 'session_duration_seconds', 'device_type', 'pages_viewed', 'channel', 'entry_channel', 'landing_path', 'landing_page', 'referrer_host', 'referrer' );

	
	const UPSERT = 'ON DUPLICATE KEY UPDATE
		first_channel   = IF( VALUES(first_seen) < first_seen, VALUES(first_channel), first_channel ),
		first_landing   = IF( VALUES(first_seen) < first_seen, VALUES(first_landing), first_landing ),
		first_referrer  = IF( VALUES(first_seen) < first_seen, VALUES(first_referrer), first_referrer ),
		first_device    = IF( VALUES(first_seen) < first_seen, VALUES(first_device), first_device ),
		first_seen      = LEAST( first_seen, VALUES(first_seen) ),
		last_channel    = IF( VALUES(last_seen) >= last_seen, VALUES(last_channel), last_channel ),
		last_landing    = IF( VALUES(last_seen) >= last_seen, VALUES(last_landing), last_landing ),
		last_referrer   = IF( VALUES(last_seen) >= last_seen, VALUES(last_referrer), last_referrer ),
		last_device     = IF( VALUES(last_seen) >= last_seen, VALUES(last_device), last_device ),
		last_seen       = GREATEST( last_seen, VALUES(last_seen) ),
		visits          = visits + VALUES(visits),
		pages           = pages + VALUES(pages),
		product_views   = product_views + VALUES(product_views),
		carts           = carts + VALUES(carts),
		session_seconds = session_seconds + VALUES(session_seconds),
		updated         = VALUES(updated)';

	const FIELDS = 'subject_id, first_seen, last_seen, visits, pages, product_views, carts, session_seconds, first_channel, first_landing, first_referrer, first_device, last_channel, last_landing, last_referrer, last_device, updated';

	
	private static function upsert( $select ) {
		$n = implode( ', ', array_map( static function ( $f ) { return 'n_' . trim( $f ); }, explode( ',', self::FIELDS ) ) );
		return 'INSERT INTO `' . self::table() . '` (' . self::FIELDS . ") SELECT {$n} FROM ( {$select} ) n ORDER BY n_first_seen " . self::UPSERT;
	}

	
	public static function folded_before_ms() {
		return (int) get_option( self::MARK, 0 );
	}

	
	public static function tick( $budget = self::BUDGET ) {
		global $wpdb;
		if ( ! self::ready() ) {
			return 0;
		}
		if ( 1 !== (int) $wpdb->get_var( $wpdb->prepare( 'SELECT GET_LOCK(%s, 0)', self::LOCK ) ) ) {
			return 0;
		}
		$done = 0;
		try {
			$now   = (int) round( microtime( true ) * 1000 );
			$limit = $now - self::SETTLE_START_MS;
			$from  = self::folded_before_ms();
			$t0    = microtime( true );
			while ( microtime( true ) - $t0 < $budget ) {
				$next = self::next_visit_ms( $from );
				if ( null === $next || $next >= $limit ) {
					if ( $limit > $from ) {
						update_option( self::MARK, $limit, false ); 
					}
					return $done;
				}
				$to = min( $next + self::WINDOW_MS, $limit );
				if ( self::window_waits( $next, $to, $now ) || ! self::fold( $next, $to ) ) {
					if ( $next > $from ) {
						update_option( self::MARK, $next, false ); 
					}
					return $done;
				}
				update_option( self::MARK, $to, false );
				$from = $to;
				$done++;
			}
			if ( ! wp_next_scheduled( self::MORE ) ) {
				wp_schedule_single_event( time() + MINUTE_IN_SECONDS, self::MORE );
			}
			return $done;
		} finally {
			$wpdb->query( $wpdb->prepare( 'SELECT RELEASE_LOCK(%s)', self::LOCK ) );
		}
	}

	
	public static function merge( $survivor_id, $loser_id ) {
		global $wpdb;
		$t = self::table();
		if ( ! self::has_table( $t ) ) {
			return;
		}
		$cols = array();
		foreach ( explode( ',', self::FIELDS ) as $f ) {
			$f      = trim( $f );
			$cols[] = ( 'subject_id' === $f ? '%d' : ( 'updated' === $f ? 'UTC_TIMESTAMP()' : $f ) ) . ' AS n_' . $f;
		}
		$wpdb->query( $wpdb->prepare(
			self::upsert( 'SELECT ' . implode( ', ', $cols ) . ' FROM `' . $t . '` WHERE subject_id = %d' ),
			(int) $survivor_id,
			(int) $loser_id
		) );
		$wpdb->query( $wpdb->prepare( 'DELETE FROM `' . $t . '` WHERE subject_id = %d', (int) $loser_id ) );
	}

	
	private static function next_visit_ms( $from ) {
		global $wpdb;
		$next = null;
		foreach ( self::sources() as $src ) {
			$v = $wpdb->get_var( $wpdb->prepare( "SELECT MIN(first_timestamp) FROM `{$src}` WHERE first_timestamp >= %d", (int) $from ) );
			if ( null !== $v && ( null === $next || (int) $v < $next ) ) {
				$next = (int) $v;
			}
		}
		return $next;
	}

	
	private static function window_waits( $from, $to, $now ) {
		global $wpdb;
		$si   = aieo_session_identity_table();
		$coll = self::collation();
		foreach ( self::sources() as $src ) {
			if ( $wpdb->get_var( $wpdb->prepare(
				"SELECT 1 FROM `{$src}` v JOIN `{$si}` si ON si.session_id = v.session_id COLLATE {$coll}
				  WHERE v.first_timestamp >= %d AND v.first_timestamp < %d AND si.resolved_via = 'pending' LIMIT 1",
				$from,
				$to
			) ) ) {
				return true;
			}
			if ( $to > $now - self::FORCE_MS && $wpdb->get_var( $wpdb->prepare(
				"SELECT 1 FROM `{$src}` WHERE first_timestamp >= %d AND first_timestamp < %d AND last_timestamp >= %d LIMIT 1",
				$from,
				$to,
				$now - self::SETTLE_LAST_MS
			) ) ) {
				return true;
			}
		}
		return false;
	}

	
	private static function fold( $from, $to ) {
		global $wpdb;
		$si   = aieo_session_identity_table();
		$sub  = aieo_subjects_table();
		$coll = self::collation();
		
		
		$branches = array();
		foreach ( self::sources() as $src ) {
			$have = array_flip( self::columns( $src ) );
			$list = array();
			foreach ( self::COLUMNS as $c ) {
				$list[] = ( isset( $have[ $c ] ) ? "`{$c}`" : 'NULL' ) . " AS `{$c}`";
			}
			$branches[] = $wpdb->prepare( 'SELECT ' . implode( ', ', $list ) . " FROM `{$src}` WHERE first_timestamp >= %d AND first_timestamp < %d", $from, $to );
		}
		$merged   = implode( ', ', array_map( static function ( $c ) { return "MAX(`{$c}`) AS `{$c}`"; }, array_slice( self::COLUMNS, 1 ) ) );
		$channel  = "LEFT( COALESCE( NULLIF( v.channel, '' ), NULLIF( v.entry_channel, '' ), '' ), 40 )";
		$landing  = "LEFT( COALESCE( NULLIF( v.landing_path, '' ), SUBSTRING_INDEX( v.landing_page, '?', 1 ), '' ), 255 )";
		$referrer = "LEFT( COALESCE( NULLIF( v.referrer_host, '' ), SUBSTRING_INDEX( SUBSTRING_INDEX( v.referrer, '/', 3 ), '/', -1 ), '' ), 190 )";
		$device   = "LEFT( COALESCE( v.device_type, '' ), 50 )";
		$res      = $wpdb->query( self::upsert(
			"SELECT COALESCE( NULLIF( s.merged_into, 0 ), si.subject_id ) AS n_subject_id,
			        DATE_ADD( DATE '1970-01-01', INTERVAL ( v.first_timestamp DIV 1000 ) SECOND ) AS n_first_seen,
			        DATE_ADD( DATE '1970-01-01', INTERVAL ( GREATEST( v.first_timestamp, COALESCE( v.last_timestamp, 0 ) ) DIV 1000 ) SECOND ) AS n_last_seen,
			        1 AS n_visits, COALESCE( v.pages_viewed, 0 ) AS n_pages, COALESCE( v.total_product_views, 0 ) AS n_product_views,
			        COALESCE( v.total_add_to_cart, 0 ) AS n_carts, COALESCE( v.session_duration_seconds, 0 ) AS n_session_seconds,
			        {$channel} AS n_first_channel, {$landing} AS n_first_landing, {$referrer} AS n_first_referrer, {$device} AS n_first_device,
			        {$channel} AS n_last_channel, {$landing} AS n_last_landing, {$referrer} AS n_last_referrer, {$device} AS n_last_device,
			        UTC_TIMESTAMP() AS n_updated
			   FROM ( SELECT session_id, {$merged} FROM ( " . implode( ' UNION ALL ', $branches ) . " ) u GROUP BY session_id ) v
			   JOIN `{$si}` si ON si.session_id = v.session_id COLLATE {$coll}
			   LEFT JOIN `{$sub}` s ON s.subject_id = si.subject_id
			  WHERE si.subject_id > 0"
		) );
		if ( false === $res && function_exists( 'aieo_debug_log' ) ) {
			aieo_debug_log( 'AIEO_Visitor_Lifetime: window ' . $from . ' failed, mark held: ' . $wpdb->last_error );
		}
		return false !== $res;
	}

	
	private static function sources() {
		global $wpdb;
		$out = array();
		foreach ( array( 'aieo_sessions_statics', 'aieo_sessions_statics_archive' ) as $t ) {
			if ( self::has_table( $wpdb->prefix . $t ) ) {
				$out[] = $wpdb->prefix . $t;
			}
		}
		return $out;
	}

	private static function ready() {
		return function_exists( 'aieo_session_identity_table' ) && self::has_table( self::table() )
			&& self::has_table( aieo_session_identity_table() ) && array() !== self::sources();
	}

	private static function table() {
		global $wpdb;
		return $wpdb->prefix . 'aieo_visitor_lifetime';
	}

	
	private static function collation() {
		static $c = null;
		if ( null === $c ) {
			global $wpdb;
			$found = (string) $wpdb->get_var( $wpdb->prepare(
				"SELECT COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = 'session_id' LIMIT 1",
				aieo_session_identity_table()
			) );
			$c = preg_match( '/^[a-z0-9_]+$/i', $found ) ? $found : 'utf8mb4_general_ci';
		}
		return $c;
	}

	
	private static function columns( $table ) {
		global $wpdb;
		return array_map( 'strval', (array) $wpdb->get_col( "SHOW COLUMNS FROM `{$table}`" ) );
	}

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