<?php

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

require_once __DIR__ . '/dmm/sql/subjects-schema.php';

class AIEO_Interaction_Summary {

	const HOOK_TICK   = 'aieo_interaction_summary_tick';
	const BATCH       = 5000;
	const COLL        = 'utf8mb4_general_ci'; 
	const SETTLE_MIN  = 2;     
	const MIN_ID_LEN  = 16;    

	const W_RECO_CLICK = 0.5;
	
	const W_IMAGE_CLICK = 2.5;
	const W_WISHLIST   = 3.0;
	const W_CART        = 5.0;
	const W_CONVERSION  = 12.0;
	const W_REMOVE      = -2.0;

	
	private static $booted = false;

	public static function bootstrap() {
		if ( self::$booted ) {
			return;
		}
		self::$booted = true;
		add_action( self::HOOK_TICK, array( __CLASS__, 'tick' ) );
		add_filter( 'aieo_archive_move_max_id', array( __CLASS__, 'move_max_id' ), 10, 2 );
		add_filter( 'pre_as_schedule_recurring_action', array( __CLASS__, 'dedup_pre_schedule' ), 5, 6 );
		add_action( 'admin_init', array( __CLASS__, 'maybe_schedule' ), 30 );
		add_action( 'init', array( __CLASS__, 'maybe_schedule' ), 30 );
	}

	
	public static function move_max_id( $max_id, $table_slug ) {
		$cursors = array(
			'aieo_sessions'       => array( 'aieo_interaction_hw_sessions_view', 'aieo_interaction_hw_sessions_imgclick', 'aieo_interaction_hw_sessions_sig' ),
			'aieo_reco_events'    => array( 'aieo_interaction_hw_reco' ),
			'aieo_addcart_events' => array( 'aieo_interaction_hw_addcart' ),
		);
		foreach ( $cursors[ $table_slug ] ?? array() as $opt ) {
			$c = get_option( $opt, null );
			if ( null !== $c && false !== $c ) {
				$max_id = min( (int) $max_id, (int) $c );
			}
		}
		return (int) $max_id;
	}

	public static function maybe_schedule() {
		if ( class_exists( 'AIEO_Schedule_Gate', false ) && ! AIEO_Schedule_Gate::due( __METHOD__ ) ) {
			return; 
		}
		static $checked = false;
		if ( $checked ) {
			return;
		}
		$checked = true;
		if ( get_transient( 'aieo_interaction_sched_lock' ) ) {
			return;
		}
		set_transient( 'aieo_interaction_sched_lock', 1, 5 * MINUTE_IN_SECONDS );
		if ( function_exists( 'as_next_scheduled_action' ) && function_exists( 'as_schedule_recurring_action' ) ) {
			if ( false === as_next_scheduled_action( self::HOOK_TICK, null, 'aieo' ) ) {
				as_schedule_recurring_action( time() + HOUR_IN_SECONDS, HOUR_IN_SECONDS, self::HOOK_TICK, array(), 'aieo' );
			}
		} elseif ( ! wp_next_scheduled( self::HOOK_TICK ) ) {
			wp_schedule_event( time() + HOUR_IN_SECONDS, 'hourly', self::HOOK_TICK );
		}
	}

	public static function dedup_pre_schedule( $pre, $timestamp, $interval, $hook, $args = array(), $group = '' ) {
		if ( self::HOOK_TICK !== $hook || 'aieo' !== $group ) {
			return $pre;
		}
		global $wpdb;
		$lk = 'aieo_interaction_sched';
		if ( 1 !== (int) $wpdb->get_var( $wpdb->prepare( 'SELECT GET_LOCK(%s, 0)', $lk ) ) ) {
			return 1;
		}
		try {
			
			$existing = (int) $wpdb->get_var( $wpdb->prepare(
				"SELECT a.action_id FROM {$wpdb->prefix}actionscheduler_actions a
				 LEFT JOIN {$wpdb->prefix}actionscheduler_groups g ON g.group_id = a.group_id
				 WHERE a.hook = %s AND a.status IN ('pending','in-progress') AND g.slug = %s
				 ORDER BY a.action_id ASC LIMIT 1",
				self::HOOK_TICK,
				'aieo'
			) );
			return $existing > 0 ? $existing : $pre;
		} finally {
			$wpdb->query( $wpdb->prepare( 'SELECT RELEASE_LOCK(%s)', $lk ) );
		}
	}

	
	
	

	public static function tick() {
		if ( ! self::ready() ) {
			return;
		}
		global $wpdb;
		$lock = 'aieo_interaction_summary';
		if ( 1 !== (int) $wpdb->get_var( $wpdb->prepare( 'SELECT GET_LOCK(%s, 0)', $lock ) ) ) {
			return;
		}
		try {
			$more = false;
			$more = self::run_sessions_views() || $more;
			$more = self::run_image_clicks() || $more;
			$more = self::run_sessions_signed() || $more;
			$more = self::run_reco_clicks() || $more;
			$more = self::run_addcart() || $more;
			$more = self::run_conversions() || $more;
			if ( $more && function_exists( 'as_enqueue_async_action' ) ) {
				as_enqueue_async_action( self::HOOK_TICK, array(), 'aieo' );
			}
		} finally {
			$wpdb->query( $wpdb->prepare( 'SELECT RELEASE_LOCK(%s)', $lock ) );
		}
	}

	
	private static function run_sessions_views() {
		global $wpdb;
		$s  = $wpdb->prefix . 'aieo_sessions';
		$si = aieo_session_identity_table();
		$is = aieo_interaction_summary_table();
		list( $from, $to, $frontier ) = self::window( 'aieo_interaction_hw_sessions_view', $s, 'created_at' );
		if ( $to <= $from ) {
			return false;
		}
		$coll = self::collation();
		$len  = self::MIN_ID_LEN;
		
		$res = $wpdb->query( $wpdb->prepare(
			"INSERT INTO `{$is}` (subject_id, item_id, item_type, views, weight, first_at, last_at)
			 SELECT si.subject_id, s.product_id, 'product', 1,
			        (1 + LEAST(COALESCE(s.time_on_page,0)/120, 1) + LEAST(COALESCE(s.scroll_depth,0)/100, 1)),
			        CONVERT_TZ(FROM_UNIXTIME(s.`timestamp`/1000), @@session.time_zone, '+00:00'),
			        CONVERT_TZ(FROM_UNIXTIME(s.`timestamp`/1000), @@session.time_zone, '+00:00')
			 FROM `{$s}` s
			 JOIN `{$si}` si ON si.session_id = s.session_id COLLATE {$coll}
			 WHERE s.id > %d AND s.id <= %d
			   AND s.event_type = 'product_view' AND s.product_id > 0
			   AND si.subject_id > 0 AND CHAR_LENGTH(s.session_id) >= {$len}
			 ON DUPLICATE KEY UPDATE
			   views = views + 1,
			   weight = weight + VALUES(weight),
			   first_at = LEAST(COALESCE(first_at, VALUES(first_at)), VALUES(first_at)),
			   last_at  = GREATEST(COALESCE(last_at, VALUES(last_at)), VALUES(last_at))",
			$from,
			$to
		) );
		return self::advance( 'aieo_interaction_hw_sessions_view', $res, $to, $frontier );
	}

	
	
	private static function run_image_clicks() {
		global $wpdb;
		$s  = $wpdb->prefix . 'aieo_sessions';
		$si = aieo_session_identity_table();
		$is = aieo_interaction_summary_table();
		list( $from, $to, $frontier ) = self::window( 'aieo_interaction_hw_sessions_imgclick', $s, 'created_at' );
		if ( $to <= $from ) {
			return false;
		}
		$coll = self::collation();
		$len  = self::MIN_ID_LEN;
		$w    = self::W_IMAGE_CLICK;
		
		$res = $wpdb->query( $wpdb->prepare(
			"INSERT INTO `{$is}` (subject_id, item_id, item_type, image_clicks, weight, first_at, last_at)
			 SELECT si.subject_id, s.product_id, 'product', 1, {$w},
			        CONVERT_TZ(FROM_UNIXTIME(s.`timestamp`/1000), @@session.time_zone, '+00:00'),
			        CONVERT_TZ(FROM_UNIXTIME(s.`timestamp`/1000), @@session.time_zone, '+00:00')
			 FROM `{$s}` s
			 JOIN `{$si}` si ON si.session_id = s.session_id COLLATE {$coll}
			 WHERE s.id > %d AND s.id <= %d
			   AND s.event_type = 'product_image_click' AND s.product_id > 0
			   AND si.subject_id > 0 AND CHAR_LENGTH(s.session_id) >= {$len}
			 ON DUPLICATE KEY UPDATE
			   image_clicks = image_clicks + 1,
			   weight = weight + VALUES(weight),
			   first_at = LEAST(COALESCE(first_at, VALUES(first_at)), VALUES(first_at)),
			   last_at  = GREATEST(COALESCE(last_at, VALUES(last_at)), VALUES(last_at))",
			$from,
			$to
		) );
		return self::advance( 'aieo_interaction_hw_sessions_imgclick', $res, $to, $frontier );
	}

	
	private static function run_sessions_signed() {
		global $wpdb;
		$s  = $wpdb->prefix . 'aieo_sessions';
		$si = aieo_session_identity_table();
		$is = aieo_interaction_summary_table();
		list( $from, $to, $frontier ) = self::window( 'aieo_interaction_hw_sessions_sig', $s, 'created_at' );
		if ( $to <= $from ) {
			return false;
		}
		$coll   = self::COLL;
		$len    = self::MIN_ID_LEN;
		$w_wish = self::W_WISHLIST;
		$w_rem  = self::W_REMOVE;
		
		$res = $wpdb->query( $wpdb->prepare(
			"INSERT INTO `{$is}` (subject_id, item_id, item_type, wishlists, removes, weight, first_at, last_at)
			 SELECT si.subject_id, s.product_id, 'product',
			        CASE WHEN s.event_type='add_to_wishlist' THEN 1 ELSE 0 END,
			        CASE WHEN s.event_type='remove_from_cart' THEN 1 ELSE 0 END,
			        CASE WHEN s.event_type='add_to_wishlist' THEN %f ELSE %f END,
			        CONVERT_TZ(FROM_UNIXTIME(s.`timestamp`/1000), @@session.time_zone, '+00:00'),
			        CONVERT_TZ(FROM_UNIXTIME(s.`timestamp`/1000), @@session.time_zone, '+00:00')
			 FROM `{$s}` s
			 JOIN `{$si}` si ON si.session_id = s.session_id COLLATE {$coll}
			 WHERE s.id > %d AND s.id <= %d
			   AND s.event_type IN ('add_to_wishlist','remove_from_cart')
			   AND s.product_id > 0 AND si.subject_id > 0 AND CHAR_LENGTH(s.session_id) >= {$len}
			 ON DUPLICATE KEY UPDATE
			   wishlists = wishlists + VALUES(wishlists),
			   removes   = removes + VALUES(removes),
			   weight    = weight + VALUES(weight),
			   first_at  = LEAST(COALESCE(first_at, VALUES(first_at)), VALUES(first_at)),
			   last_at   = GREATEST(COALESCE(last_at, VALUES(last_at)), VALUES(last_at))",
			$w_wish,
			$w_rem,
			$from,
			$to
		) );
		return self::advance( 'aieo_interaction_hw_sessions_sig', $res, $to, $frontier );
	}

	
	private static function run_reco_clicks() {
		global $wpdb;
		$r   = $wpdb->prefix . 'aieo_reco_events';
		$sub = $wpdb->prefix . 'aieo_subjects';
		$is  = aieo_interaction_summary_table();
		list( $from, $to, $frontier ) = self::window( 'aieo_interaction_hw_reco', $r, 'ts' );
		if ( $to <= $from ) {
			return false;
		}
		$coll = self::collation();
		$len  = self::MIN_ID_LEN;
		$w    = self::W_RECO_CLICK;
		
		$res = $wpdb->query( $wpdb->prepare(
			"INSERT INTO `{$is}` (subject_id, item_id, item_type, weight, first_at, last_at)
			 SELECT sub.subject_id, r.target_pid, 'product', %f, r.ts, r.ts
			 FROM `{$r}` r
			 JOIN `{$sub}` sub ON sub.visitor_key = r.session_id COLLATE {$coll}
			 WHERE r.id > %d AND r.id <= %d
			   AND r.event_type = 'reco_click' AND r.target_pid > 0
			   AND sub.subject_id > 0 AND sub.merged_into = 0 AND CHAR_LENGTH(r.session_id) >= {$len}
			 ON DUPLICATE KEY UPDATE
			   weight   = weight + VALUES(weight),
			   first_at = LEAST(COALESCE(first_at, VALUES(first_at)), VALUES(first_at)),
			   last_at  = GREATEST(COALESCE(last_at, VALUES(last_at)), VALUES(last_at))",
			$w,
			$from,
			$to
		) );
		return self::advance( 'aieo_interaction_hw_reco', $res, $to, $frontier );
	}

	
	private static function run_addcart() {
		global $wpdb;
		$a   = $wpdb->prefix . 'aieo_addcart_events';
		$sub = $wpdb->prefix . 'aieo_subjects';
		$is  = aieo_interaction_summary_table();
		if ( ! self::has_table( $a ) ) {
			return false; 
		}
		list( $from, $to, $frontier ) = self::window( 'aieo_interaction_hw_addcart', $a, 'ts_added' );
		if ( $to <= $from ) {
			return false;
		}
		$coll = self::collation();
		$len  = self::MIN_ID_LEN;
		$w_c  = self::W_CART;
		
		$res = $wpdb->query( $wpdb->prepare(
			"INSERT INTO `{$is}` (subject_id, item_id, item_type, carts, sale_ctx, weight, first_at, last_at)
			 SELECT sub.subject_id, a.product_id, 'product',
			        1,
			        CASE WHEN a.bcs_was_on_sale = 1 THEN 1 ELSE 0 END,
			        %f, a.ts_added, a.ts_added
			 FROM `{$a}` a
			 JOIN `{$sub}` sub ON sub.visitor_key = a.session_id COLLATE {$coll}
			 WHERE a.id > %d AND a.id <= %d
			   AND a.product_id > 0 AND sub.subject_id > 0 AND sub.merged_into = 0
			   AND CHAR_LENGTH(a.session_id) >= {$len}
			 ON DUPLICATE KEY UPDATE
			   carts    = carts + VALUES(carts),
			   sale_ctx = sale_ctx + VALUES(sale_ctx),
			   weight   = weight + VALUES(weight),
			   first_at = LEAST(COALESCE(first_at, VALUES(first_at)), VALUES(first_at)),
			   last_at  = GREATEST(COALESCE(last_at, VALUES(last_at)), VALUES(last_at))",
			$w_c,
			$from,
			$to
		) );
		return self::advance( 'aieo_interaction_hw_addcart', $res, $to, $frontier );
	}

	
	private static function run_conversions() {
		global $wpdb;
		$a   = $wpdb->prefix . 'aieo_addcart_events';
		$sub = $wpdb->prefix . 'aieo_subjects';
		$is  = aieo_interaction_summary_table();
		if ( ! self::has_table( $a ) || ! self::column_exists( $a, 'backfilled_at' ) ) {
			return false; 
		}
		$cur_ts = (string) get_option( 'aieo_interaction_hw_conv_ts', '1970-01-01 00:00:00' );
		$cur_id = (int) get_option( 'aieo_interaction_hw_conv_id', 0 );

		
		
		$rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT id, backfilled_at FROM `{$a}`
			 WHERE order_id > 0 AND backfilled_at IS NOT NULL
			   AND backfilled_at <= (UTC_TIMESTAMP() - INTERVAL %d MINUTE)
			   AND ( backfilled_at > %s OR ( backfilled_at = %s AND id > %d ) )
			 ORDER BY backfilled_at ASC, id ASC LIMIT %d",
			self::SETTLE_MIN,
			$cur_ts,
			$cur_ts,
			$cur_id,
			self::BATCH
		), ARRAY_A );
		if ( empty( $rows ) ) {
			return false;
		}
		$ids  = array_map( static function ( $r ) {
			return (int) $r['id'];
		}, $rows );
		$last = end( $rows );

		$coll = self::collation();
		$len  = self::MIN_ID_LEN;
		$w_cv = self::W_CONVERSION;
		$in   = implode( ',', array_map( 'intval', $ids ) );
		
		$res = $wpdb->query( $wpdb->prepare(
			"INSERT INTO `{$is}` (subject_id, item_id, item_type, conversions, weight, first_at, last_at)
			 SELECT sub.subject_id, a.product_id, 'product', 1, %f, a.ts_added, a.backfilled_at
			 FROM `{$a}` a
			 JOIN `{$sub}` sub ON sub.visitor_key = a.session_id COLLATE {$coll}
			 WHERE a.id IN ({$in}) AND a.product_id > 0 AND sub.subject_id > 0
			   AND sub.merged_into = 0 AND CHAR_LENGTH(a.session_id) >= {$len}
			 ON DUPLICATE KEY UPDATE
			   conversions = conversions + VALUES(conversions),
			   weight      = weight + VALUES(weight),
			   last_at     = GREATEST(COALESCE(last_at, VALUES(last_at)), VALUES(last_at))",
			$w_cv
		) );
		if ( false === $res ) {
			self::log( 'run_conversions insert failed' );
			return false;
		}
		update_option( 'aieo_interaction_hw_conv_ts', (string) $last['backfilled_at'], false );
		update_option( 'aieo_interaction_hw_conv_id', (int) $last['id'], false );
		return count( $rows ) >= self::BATCH;
	}

	
	
	

	
	private static function window( $opt, $table, $timecol ) {
		global $wpdb;
		$from = (int) get_option( $opt, 0 );
		
		$frontier = (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT MAX(id) FROM `{$table}` WHERE `{$timecol}` <= (UTC_TIMESTAMP() - INTERVAL %d MINUTE)",
			self::SETTLE_MIN
		) );
		if ( $frontier <= $from ) {
			return array( $from, $from, $frontier );
		}
		$to = min( $frontier, $from + self::BATCH );
		return array( $from, $to, $frontier );
	}

	
	private static function advance( $opt, $query_result, $to, $frontier ) {
		if ( false === $query_result ) {
			self::log( $opt . ' insert failed — cursor held' );
			return false; 
		}
		update_option( $opt, (int) $to, false );
		return $frontier > $to;
	}

	
	
	

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

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

	private static function column_exists( $table, $col ) {
		global $wpdb;
		if ( ! self::has_table( $table ) ) {
			return false;
		}
		
		return (bool) $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$table}` LIKE %s", $col ) );
	}

	private static function log( $msg ) {
		if ( function_exists( 'aieo_debug_log' ) ) {
			aieo_debug_log( 'AIEO_Interaction_Summary: ' . $msg );
		}
	}

	private static function ready() {
		if ( ! function_exists( 'aieo_interaction_summary_table' ) || ! function_exists( 'aieo_session_identity_table' ) ) {
			return false;
		}
		global $wpdb;
		$is = aieo_interaction_summary_table();
		
		return (string) $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $is ) ) === $is;
	}
}