array( 'signals' => 7, 'journeys' => 30, 'commerce' => 180, 'visits' => 90, 'reco' => 30, 'intent' => 30 ), 'balanced' => array( 'signals' => 14, 'journeys' => 90, 'commerce' => 365, 'visits' => 180, 'reco' => 90, 'intent' => 90 ), 'full' => array( 'signals' => 30, 'journeys' => 180, 'commerce' => 730, 'visits' => 365, 'reco' => 180, 'intent' => 180 ), ); const FLOORS = array( 'signals' => 3, 'journeys' => 14, 'commerce' => 30, 'visits' => 30, 'reco' => 14, 'intent' => 14 ); const SIGNAL_TYPES = array( 'scroll_depth', 'tab_hidden', 'tab_visible', 'click', 'product_image_click' ); const COMMERCE_TYPES = array( 'add_to_cart', 'remove_from_cart', 'checkout_step', 'checkout_timing', 'purchase_complete', 'goal', 'cart_quantity', 'coupon_applied', 'coupon_removed', 'coupon_refused' ); const LEGACY = array( 'aieo_sessions_archive' => 90, 'aieo_sessions_statics_archive' => 90, 'aieo_reco_events_archive' => 90, 'aieo_addcart_events_archive' => 730, 'aieo_intent_events_archive' => 90 ); public static function settings() { $saved = get_option( self::OPTION ); if ( is_array( $saved ) && isset( $saved['preset'] ) ) { $preset = isset( self::PRESETS[ $saved['preset'] ] ) || 'custom' === $saved['preset'] ? (string) $saved['preset'] : 'balanced'; $days = 'custom' === $preset ? (array) ( $saved['days'] ?? array() ) : self::PRESETS[ $preset ]; return array( 'preset' => $preset, 'days' => self::clamp( $days ), 'hour' => self::hour( $saved['hour'] ?? self::HOUR ) ); } $days = self::PRESETS['balanced']; $own = get_option( 'aieo_session_retention_days', null ); if ( null !== $own && '' !== $own && false !== $own && (int) $own !== $days['journeys'] ) { $days['journeys'] = (int) $own; return array( 'preset' => 'custom', 'days' => self::clamp( $days ), 'hour' => self::HOUR ); } return array( 'preset' => 'balanced', 'days' => self::clamp( $days ), 'hour' => self::HOUR ); } public static function save( $preset, array $days = array(), $hour = self::HOUR ) { $preset = isset( self::PRESETS[ $preset ] ) || 'custom' === $preset ? (string) $preset : 'balanced'; $value = array( 'preset' => $preset, 'days' => self::clamp( 'custom' === $preset ? $days : self::PRESETS[ $preset ] ), 'hour' => self::hour( $hour ) ); update_option( self::OPTION, $value, false ); self::schedule( true ); return self::settings(); } public static function clamp( array $days ) { $out = array(); foreach ( self::KINDS as $kind ) { $n = isset( $days[ $kind ] ) ? (int) $days[ $kind ] : self::PRESETS['balanced'][ $kind ]; $out[ $kind ] = max( self::FLOORS[ $kind ], min( 3650, $n ) ); } $out['signals'] = min( $out['signals'], $out['journeys'] ); return $out; } private static function hour( $hour ) { return max( 0, min( 23, (int) $hour ) ); } public static function schedule( $again = false ) { $next = wp_next_scheduled( self::HOOK ); if ( $next && ! $again ) { return; } if ( $next ) { wp_clear_scheduled_hook( self::HOOK ); } $hour = self::settings()['hour']; $at = strtotime( gmdate( 'Y-m-d' ) . sprintf( ' %02d:%02d:00 UTC', $hour, self::MINUTE ) ); if ( $at <= time() ) { $at += DAY_IN_SECONDS; } wp_schedule_event( $at, 'daily', self::HOOK ); } public static function plan( $now = 0 ) { global $wpdb; $now = $now ? (int) $now : time(); $days = self::settings()['days']; $fold = self::fold_day(); $held = self::held_by(); $p = $wpdb->prefix; $sig = self::in_list( self::SIGNAL_TYPES ); $com = self::in_list( self::COMMERCE_TYPES ); $plan = array(); if ( ! class_exists( 'AIEO_Visitor_Lifetime' ) ) { require_once __DIR__ . '/class-aieo-visitor-lifetime.php'; } $lifetime = $wpdb->prepare( 'first_timestamp < %d', AIEO_Visitor_Lifetime::folded_before_ms() ); $sightings = class_exists( 'AIEO_Sighting' ) ? AIEO_Sighting::not_sql( 'event_type' ) : "event_type NOT IN ('offer_sighting', 'offer_engaged')"; $targets = array( 'signals' => array( 'aieo_sessions_archive', 'timestamp', true, "event_type IN ({$sig})", 'signals' ), 'journeys' => array( 'aieo_sessions_archive', 'timestamp', true, "(event_type IS NULL OR event_type NOT IN ({$sig}, {$com}))", 'journeys' ), 'commerce' => array( 'aieo_sessions_archive', 'timestamp', true, "event_type IN ({$com})", 'commerce' ), 'visits' => array( 'aieo_sessions_statics_archive', 'first_timestamp', true, $lifetime, 'visits' ), 'reco' => array( 'aieo_reco_events_archive', 'ts', false, $sightings, 'reco' ), 'carts' => array( 'aieo_addcart_events_archive', 'ts_added', false, '', 'commerce' ), 'intent' => array( 'aieo_intent_events_archive', 'ts', false, '', 'intent' ), ); $legacy_done = array(); foreach ( $targets as $id => $t ) { list( $table, $time, $ms, $where, $kind ) = $t; $why = $held; if ( '' === $why && in_array( $table, array( 'aieo_sessions_archive', 'aieo_sessions_statics_archive' ), true ) && self::repair_pending() ) { $why = __( 'the one-time repair of past engagement still reads the archive', 'ai-eshop-optimizer' ); } if ( '' !== $why ) { $plan[ $id ] = array( 'table' => $p . $table, 'time' => 'archived_at', 'ms' => false, 'where' => in_array( $id, array( 'visits', 'reco' ), true ) ? $where : '', 'cut' => gmdate( 'Y-m-d H:i:s', $now - self::LEGACY[ $table ] * DAY_IN_SECONDS ), 'rule' => 'legacy', 'held' => $why, 'skip' => isset( $legacy_done[ $table ] ), ); $legacy_done[ $table ] = true; continue; } $window = gmdate( 'Y-m-d', $now - $days[ $kind ] * DAY_IN_SECONDS ); $cut = min( $window, $fold ); $plan[ $id ] = array( 'table' => $p . $table, 'time' => $time, 'ms' => $ms, 'where' => $where, 'cut' => $cut, 'rule' => $cut < $window ? 'fold' : 'window', 'held' => '', 'skip' => false, ); } return $plan; } private static function fold_day() { if ( ! class_exists( 'AIEO_Aggregate_Cron' ) ) { require_once __DIR__ . '/cron/class-aieo-aggregate-cron.php'; } return AIEO_Aggregate_Cron::folded_before(); } public static function held_by() { if ( '' !== self::fold_day() ) { return ''; } $holders = AIEO_Aggregate_Cron::fold_holders(); $none = array_keys( array_filter( $holders, static function ( $m ) { return '' === $m; } ) ); $why = __( 'the nightly summaries have not completed yet', 'ai-eshop-optimizer' ); return $none && count( $none ) <= 3 ? $why . ' (' . implode( ', ', $none ) . ')' : $why; } private static function repair_pending() { $state = get_option( 'aieo_engagement_repair' ); return is_array( $state ) && in_array( $state['status'] ?? '', array( 'pending', 'running', 'failed' ), true ); } private static function in_list( array $values ) { global $wpdb; return implode( ', ', array_map( static function ( $v ) use ( $wpdb ) { return $wpdb->prepare( '%s', $v ); }, $values ) ); } public static function run( $budget = self::BUDGET ) { global $wpdb; $lock = self::LOCK . '_' . md5( ( defined( 'DB_NAME' ) ? DB_NAME : '' ) . '.' . $wpdb->prefix ); if ( 1 !== (int) $wpdb->get_var( $wpdb->prepare( 'SELECT GET_LOCK(%s, 0)', $lock ) ) ) { return array_merge( (array) get_option( self::STATE, array() ), array( 'busy' => true ) ); } try { $t0 = microtime( true ); $state = array( 'started' => gmdate( 'Y-m-d H:i:s' ), 'targets' => array(), 'more' => false ); foreach ( self::plan() as $id => $target ) { $rec = array( 'deleted' => 0, 'cut' => $target['cut'], 'rule' => $target['rule'], 'held' => $target['held'] ); if ( empty( $target['skip'] ) && self::table_exists( $target['table'] ) ) { list( $rec['deleted'], $left ) = self::delete( $target, $t0, $budget ); $state['more'] = $state['more'] || $left; } $state['targets'][ $id ] = $rec; } $state['finished'] = gmdate( 'Y-m-d H:i:s' ); $state['secs'] = round( microtime( true ) - $t0, 1 ); update_option( self::STATE, $state, false ); if ( $state['more'] ) { AIEO_Background_Queue::queue( self::MORE, 2 * MINUTE_IN_SECONDS ); } return $state; } finally { $wpdb->query( $wpdb->prepare( 'SELECT RELEASE_LOCK(%s)', $lock ) ); } } private static function delete( array $target, $t0, $budget ) { global $wpdb; if ( 'legacy' === $target['rule'] ) { $where = $wpdb->prepare( "`{$target['time']}` < %s", $target['cut'] ); } elseif ( $target['ms'] ) { $where = $wpdb->prepare( "`{$target['time']}` < %d", strtotime( $target['cut'] . ' 00:00:00 UTC' ) * 1000 ); } else { $where = $wpdb->prepare( "`{$target['time']}` < %s", $target['cut'] . ' 00:00:00' ); } $where .= '' !== $target['where'] ? ' AND ' . $target['where'] : ''; $deleted = 0; while ( microtime( true ) - $t0 < $budget ) { $ids = $wpdb->get_col( $wpdb->prepare( "SELECT id FROM {$target['table']} WHERE {$where} ORDER BY `{$target['time']}` LIMIT %d", self::BATCH ) ); if ( empty( $ids ) ) { return array( $deleted, false ); } $n = (int) $wpdb->query( "DELETE FROM {$target['table']} WHERE id IN (" . implode( ',', array_map( 'intval', $ids ) ) . ')' ); if ( $n < 1 ) { return array( $deleted, false ); } $deleted += $n; if ( count( $ids ) < self::BATCH ) { return array( $deleted, false ); } usleep( self::PAUSE ); } return array( $deleted, true ); } private static function table_exists( $table ) { global $wpdb; return $table === $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table ) ) ); } }