prefix . 'aieo_waitlist'; $tbl_s = $wpdb->prefix . 'aieo_sessions'; $tbl_sub = $wpdb->prefix . 'aieo_push_subscriptions'; $tbl_pm = $wpdb->postmeta; $tbl_p = $wpdb->posts; $tbl_u = $wpdb->users; $limit = max(1, (int) $limit); $subs = $wpdb->get_results( "SELECT sub.subscription_id, sub.user_id, sub.session_token, u.user_email AS email FROM {$tbl_sub} sub LEFT JOIN {$tbl_u} u ON u.ID = sub.user_id AND sub.user_id > 0 WHERE sub.status = 'active' LIMIT 5000", ARRAY_A ); if (empty($subs)) { return array(); } $subs_by_user_id = array(); $subs_by_session = array(); $subs_by_email = array(); foreach ($subs as $s) { $uid = (int) $s['user_id']; $tok = (string) $s['session_token']; $email = (string) $s['email']; $sub_row = array('subscription_id' => (int) $s['subscription_id'], 'user_id' => $uid); if ($uid > 0) { $subs_by_user_id[$uid][] = $sub_row; if ($email !== '') { $subs_by_email[strtolower($email)][] = $sub_row; } } elseif ($tok !== '') { $subs_by_session[$tok][] = $sub_row; } } if (empty($subs_by_user_id) && empty($subs_by_session) && empty($subs_by_email)) { return array(); } $candidates = array(); if (!empty($subs_by_email)) { $emails = array_keys($subs_by_email); $placeholders = implode(',', array_fill(0, count($emails), '%s')); $waitlist_rows = $wpdb->get_results($wpdb->prepare( "SELECT LOWER(w.email) AS email_norm, COALESCE(NULLIF(w.variation_id,0), w.product_id) AS stock_pid FROM {$tbl_w} w JOIN {$tbl_pm} ss ON ss.post_id = COALESCE(NULLIF(w.variation_id,0), w.product_id) AND ss.meta_key = '_stock_status' AND ss.meta_value = 'instock' WHERE w.status = 'subscribed' AND w.notified_at IS NULL AND LOWER(w.email) IN ({$placeholders})", $emails ), ARRAY_A); foreach ($waitlist_rows as $r) { $email_norm = (string) $r['email_norm']; if (isset($subs_by_email[$email_norm])) { $candidates[] = array( 'sub_rows' => $subs_by_email[$email_norm], 'product_id' => (int) $r['stock_pid'], ); } } } if (!empty($subs_by_user_id)) { $user_ids = array_keys($subs_by_user_id); $in_users = implode(',', array_map('intval', $user_ids)); $session_rows = $wpdb->get_results( "SELECT s.user_id, s.product_id FROM {$tbl_s} s JOIN {$tbl_pm} ss ON ss.post_id = s.product_id AND ss.meta_key = '_stock_status' AND ss.meta_value = 'instock' JOIN {$tbl_pm} rs ON rs.post_id = s.product_id AND rs.meta_key = '_aieo_restocked_at' AND rs.meta_value >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL 14 DAY) WHERE s.event_type = 'product_view' AND s.product_id IS NOT NULL AND s.user_id IN ({$in_users}) AND s.created_at >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL 30 DAY)", ARRAY_A ); foreach ($session_rows as $r) { $uid = (int) $r['user_id']; $candidates[] = array( 'sub_rows' => isset($subs_by_user_id[$uid]) ? $subs_by_user_id[$uid] : array(), 'product_id' => (int) $r['product_id'], ); } } if (!empty($subs_by_session)) { $tokens = array_keys($subs_by_session); $placeholders = implode(',', array_fill(0, count($tokens), '%s')); $anon_sessions = $wpdb->get_results($wpdb->prepare( "SELECT s.session_id, s.product_id FROM {$tbl_s} s JOIN {$tbl_pm} ss ON ss.post_id = s.product_id AND ss.meta_key = '_stock_status' AND ss.meta_value = 'instock' JOIN {$tbl_pm} rs ON rs.post_id = s.product_id AND rs.meta_key = '_aieo_restocked_at' AND rs.meta_value >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL 14 DAY) WHERE s.event_type = 'product_view' AND s.product_id IS NOT NULL AND s.session_id IN ({$placeholders}) AND s.created_at >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL 30 DAY)", $tokens ), ARRAY_A); foreach ($anon_sessions as $r) { $tok = (string) $r['session_id']; $candidates[] = array( 'sub_rows' => isset($subs_by_session[$tok]) ? $subs_by_session[$tok] : array(), 'product_id' => (int) $r['product_id'], ); } } if (empty($candidates)) { return array(); } $pids = array_values(array_unique(array_map( static function ($c) { return (int) $c['product_id']; }, $candidates ))); if (empty($pids)) { return array(); } $in_pids = implode(',', $pids); $titles = $wpdb->get_results( "SELECT ID, post_title FROM {$tbl_p} WHERE post_status = 'publish' AND post_type IN ('product', 'product_variation') AND ID IN ({$in_pids})", ARRAY_A ); $title_by_pid = array(); foreach ($titles as $t) { $title_by_pid[(int) $t['ID']] = (string) $t['post_title']; } $out = array(); $seen = array(); foreach ($candidates as $c) { $pid = $c['product_id']; if (!isset($title_by_pid[$pid])) { continue; } foreach ($c['sub_rows'] as $sub_row) { $key = $sub_row['subscription_id'] . ':' . $pid; if (isset($seen[$key])) { continue; } $seen[$key] = true; $url = (string) get_permalink($pid); $out[] = array( 'subscription_id' => $sub_row['subscription_id'], 'user_id' => $sub_row['user_id'], 'target_product_id' => $pid, 'target_url' => $url ?: home_url('/'), 'payload_data' => array( 'product_name' => (string) $title_by_pid[$pid], ), ); if (count($out) >= $limit) { break 2; } } } return $out; } }