prefix . 'vasp_evt_index'; } function vasp_event_aggregate_table() { global $wpdb; return $wpdb->prefix . 'vasp_evt_aggregate'; } function vasp_event_install_storage_schema() { global $wpdb; require_once ABSPATH . 'wp-admin/includes/upgrade.php'; // dbDelta receives prepared identifiers; charset/collation are also identifiers. $charset = $wpdb->charset; $collation = $wpdb->collate; $index_table = vasp_event_index_table(); $aggregate_table = vasp_event_aggregate_table(); // Version 2 adds is_internal to the aggregate identity. dbDelta does not // reliably replace an existing primary key, so remove the v1 key first. $installed_version = (int) get_option(VASP_EVENT_STORAGE_SCHEMA_OPTION, 0); $aggregate_exists = $wpdb->get_var($wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like($aggregate_table))) === $aggregate_table; if ($installed_version === 1 && $aggregate_exists) { $wpdb->query($wpdb->prepare("ALTER TABLE %i DROP PRIMARY KEY", $aggregate_table)); } // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- Optional suffixes below contain only fixed %i placeholders; table, charset and collation are bound identifiers. dbDelta($wpdb->prepare("CREATE TABLE %i ( event_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, event_uid char(64) NOT NULL, occurred_at datetime NOT NULL, event_type varchar(64) NOT NULL, object_type varchar(32) NOT NULL DEFAULT '', object_id bigint(20) unsigned NOT NULL DEFAULT 0, object_title varchar(255) NOT NULL DEFAULT '', canonical_url text NOT NULL, default_object_id bigint(20) unsigned NOT NULL DEFAULT 0, default_object_title varchar(255) NOT NULL DEFAULT '', default_canonical_url text NOT NULL, session_id varchar(191) NOT NULL DEFAULT '', wp_user_id bigint(20) unsigned NOT NULL DEFAULT 0, wp_username varchar(60) NOT NULL DEFAULT '', country char(2) NOT NULL DEFAULT 'ZZ', language varchar(20) NOT NULL DEFAULT 'unknown', is_internal tinyint(1) unsigned NOT NULL DEFAULT 0, payload longtext NOT NULL, delivery_status varchar(20) NOT NULL DEFAULT 'pending', delivered_at datetime DEFAULT NULL, PRIMARY KEY (event_id), UNIQUE KEY event_uid (event_uid), KEY occurred_at (occurred_at), KEY type_time (event_type, occurred_at), KEY object_time (object_type, object_id, occurred_at), KEY default_object_time (object_type, default_object_id, occurred_at), KEY session_time (session_id, occurred_at), KEY user_time (wp_user_id, occurred_at), KEY username_time (wp_username, occurred_at), KEY country_time (country, occurred_at), KEY language_time (language, occurred_at), KEY internal_time (is_internal, occurred_at), KEY delivery_queue (delivery_status, event_id), KEY object_title (object_title(191)), KEY default_object_title (default_object_title(191)) )" . ($charset ? " DEFAULT CHARACTER SET %i" : "") . ($collation ? " COLLATE %i" : "") . ";", array_merge([$index_table], $charset ? [$charset] : [], $collation ? [$collation] : []) )); // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- Optional suffixes below contain only fixed %i placeholders; table, charset and collation are bound identifiers. dbDelta($wpdb->prepare("CREATE TABLE %i ( aggregate_date date NOT NULL, object_type varchar(32) NOT NULL DEFAULT '', object_id bigint(20) unsigned NOT NULL DEFAULT 0, event_type varchar(64) NOT NULL, country char(2) NOT NULL DEFAULT 'ZZ', language varchar(20) NOT NULL DEFAULT 'unknown', is_internal tinyint(1) unsigned NOT NULL DEFAULT 0, object_title varchar(255) NOT NULL DEFAULT '', canonical_url text NOT NULL, event_count bigint(20) unsigned NOT NULL DEFAULT 0, PRIMARY KEY (aggregate_date, object_type, object_id, event_type, country, language, is_internal), KEY analytics (object_type, event_type, aggregate_date), KEY country_date (country, aggregate_date), KEY language_date (language, aggregate_date) )" . ($charset ? " DEFAULT CHARACTER SET %i" : "") . ($collation ? " COLLATE %i" : "") . ";", array_merge([$aggregate_table], $charset ? [$charset] : [], $collation ? [$collation] : []) )); // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared // Version 3 adds aggregate-only event totals so the dashboard never counts // the raw index during a page request. Seed existing indexed rows once. if ($installed_version > 0 && $installed_version < 3) { $wpdb->query($wpdb->prepare("DELETE FROM %i WHERE object_type = '__total__'", $aggregate_table)); $wpdb->query($wpdb->prepare("INSERT INTO %i (aggregate_date, object_type, object_id, event_type, country, language, is_internal, object_title, canonical_url, event_count) SELECT DATE(occurred_at), '__total__', 0, event_type, country, language, is_internal, '', '', COUNT(*) FROM %i GROUP BY DATE(occurred_at), event_type, country, language, is_internal ON DUPLICATE KEY UPDATE event_count = VALUES(event_count)", $aggregate_table, $index_table)); } // Version 4 makes delivery state explicit per indexed row. This turns the // existing index into a conventional outbox and allows accurate recovery // and administrator monitoring without adding another queue table. if ($installed_version > 0 && $installed_version < 4) { $legacy_cursor = max(0, (int) get_option(VASP_EVENT_LEGACY_DELIVERY_CURSOR_OPTION, 0)); if ($legacy_cursor > 0) { $wpdb->query($wpdb->prepare( "UPDATE %i SET delivery_status = 'acknowledged', delivered_at = occurred_at WHERE event_id <= %d", $index_table, $legacy_cursor )); } delete_option(VASP_EVENT_LEGACY_DELIVERY_CURSOR_OPTION); if (function_exists('vasp_event_update_delivery_status') && $wpdb->get_var($wpdb->prepare("SELECT event_id FROM %i WHERE delivery_status = 'pending' LIMIT 1", $index_table)) ) { vasp_event_update_delivery_status(['pending_hint' => true]); } } if (vasp_event_storage_schema_verified(false)) { update_option(VASP_EVENT_STORAGE_SCHEMA_OPTION, VASP_EVENT_STORAGE_SCHEMA_VERSION, false); $GLOBALS['vasp_event_storage_schema_ready'] = true; } else { delete_option(VASP_EVENT_STORAGE_SCHEMA_OPTION); unset($GLOBALS['vasp_event_storage_schema_ready']); vasp_log('Event storage schema installation remains incomplete', 'error'); } } /** Verify the delivery schema rather than trusting the version marker alone. */ function vasp_event_storage_schema_verified(bool $require_version = true): bool { global $wpdb; if ($require_version && (int) get_option(VASP_EVENT_STORAGE_SCHEMA_OPTION, 0) !== VASP_EVENT_STORAGE_SCHEMA_VERSION) { return false; } $index = vasp_event_index_table(); $aggregate = vasp_event_aggregate_table(); if ($wpdb->get_var($wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like($index))) !== $index || $wpdb->get_var($wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like($aggregate))) !== $aggregate ) { return false; } $columns = $wpdb->get_col($wpdb->prepare("SHOW COLUMNS FROM %i", $index), 0); $indexes = $wpdb->get_results($wpdb->prepare("SHOW INDEX FROM %i", $index), ARRAY_A); if ($wpdb->last_error || !is_array($columns) || !is_array($indexes)) { return false; } $index_names = array_values(array_unique(array_map(static function (array $row): string { return (string) ($row['Key_name'] ?? ''); }, $indexes))); foreach (['event_uid', 'payload', 'delivery_status', 'delivered_at'] as $column) { if (!in_array($column, $columns, true)) { return false; } } return in_array('event_uid', $index_names, true) && in_array('delivery_queue', $index_names, true); } function vasp_event_storage_schema_ready() { if (!empty($GLOBALS['vasp_event_storage_schema_ready'])) { return true; } $GLOBALS['vasp_event_storage_schema_ready'] = vasp_event_storage_schema_verified(); return $GLOBALS['vasp_event_storage_schema_ready']; } function vasp_event_normalize_country($country) { $country = strtoupper((string) $country); return preg_match('/^[A-Z]{2}$/', $country) ? $country : 'ZZ'; } function vasp_event_normalize_type($event_type) { return preg_replace('/[^a-z0-9._-]/', '', strtolower((string) $event_type)); } /** Return whether a stored event contributes to local analytics projections. */ function vasp_event_delivery_status_is_projected($delivery_status) { return in_array((string) $delivery_status, ['pending', 'acknowledged', 'discarded'], true); } function vasp_event_storage_fields($event) { $data = isset($event['data']) && is_array($event['data']) ? $event['data'] : []; $type = isset($data['content_type']) ? sanitize_key($data['content_type']) : ''; $object_id = (int) ($data['content_id'] ?? 0); if (!$object_id && !empty($data['product_id'])) { $object_id = (int) $data['product_id']; $type = 'product'; } if (!$object_id && !empty($data['order_id'])) { $object_id = (int) $data['order_id']; $type = 'order'; } return [ 'object_type' => $type, 'object_id' => $object_id, 'object_title' => (string) ($data['product_title'] ?? $data['page_title'] ?? $data['object_title'] ?? ''), 'canonical_url' => (string) ($data['product_url'] ?? $data['page_url'] ?? $data['url'] ?? $event['url'] ?? ''), 'default_object_id' => (int) ($data['default_content_id'] ?? $object_id), 'default_object_title' => (string) ($data['default_title'] ?? $data['product_title'] ?? $data['page_title'] ?? ''), 'default_canonical_url' => (string) ($data['default_url'] ?? $data['product_url'] ?? $data['page_url'] ?? $event['url'] ?? ''), ]; } function vasp_event_uid($event) { if (!empty($event['event_uid'])) { return preg_replace('/[^a-f0-9]/i', '', (string) $event['event_uid']); } $data = $event['data'] ?? []; unset($data['timestamp']); $fingerprint = [ $event['event_type'] ?? '', $event['session_id'] ?? '', isset($event['recorded_at']) ? sprintf('%.6F', (float) $event['recorded_at']) : '', $data, ]; return hash('sha256', wp_json_encode($fingerprint)); } function vasp_event_projection_objects($event, $fields) { $type = $event['event_type'] ?? ''; if ($type === 'cart.added' && $fields['object_id']) { $fields['object_type'] = 'product'; $fields['object_id'] = $fields['default_object_id'] ?: $fields['object_id']; $fields['object_title'] = $fields['default_object_title'] ?: $fields['object_title']; $fields['canonical_url'] = $fields['default_canonical_url'] ?: $fields['canonical_url']; return [$fields]; } if ($type === 'order.completed') { $objects = []; foreach ((array) ($event['data']['order_items'] ?? []) as $item) { $product_id = (int) ($item['product_id'] ?? 0); if (!$product_id) { continue; } $objects[] = [ 'object_type' => 'product', 'object_id' => (int) ($item['default_product_id'] ?? $product_id), 'object_title' => (string) ($item['default_product_title'] ?? $item['product_title'] ?? ''), 'canonical_url' => (string) ($item['default_product_url'] ?? $item['product_url'] ?? ''), ]; } return $objects; } return []; } function vasp_event_increment_projection($event, $fields) { global $wpdb; $table = vasp_event_aggregate_table(); $type = vasp_event_normalize_type($event['event_type'] ?? ''); $timestamp = (string) ($event['timestamp'] ?? current_time('mysql')); $date = preg_match('/^\d{4}-\d{2}-\d{2}/', $timestamp) ? substr($timestamp, 0, 10) : current_time('Y-m-d'); $country = vasp_event_normalize_country($event['country'] ?? 'ZZ'); $language = sanitize_text_field($event['language'] ?? 'unknown'); $total_updated = $wpdb->query($wpdb->prepare( "INSERT INTO %i (aggregate_date, object_type, object_id, event_type, country, language, is_internal, object_title, canonical_url, event_count) VALUES (%s, '__total__', 0, %s, %s, %s, %d, '', '', 1) ON DUPLICATE KEY UPDATE event_count = event_count + 1", $table, $date, $type, $country, $language, !empty($event['is_internal']) ? 1 : 0 )); if ($total_updated === false || $wpdb->last_error) { return false; } foreach (vasp_event_projection_objects($event, $fields) as $object) { $wpdb->query($wpdb->prepare( "INSERT INTO %i (aggregate_date, object_type, object_id, event_type, country, language, is_internal, object_title, canonical_url, event_count) VALUES (%s, %s, %d, %s, %s, %s, %d, %s, %s, 1) ON DUPLICATE KEY UPDATE event_count = event_count + 1, object_title = VALUES(object_title), canonical_url = VALUES(canonical_url)", $table, $date, $object['object_type'], $object['object_id'], $type, $country, $language, !empty($event['is_internal']) ? 1 : 0, $object['object_title'], $object['canonical_url'] )); if ($wpdb->last_error) { return false; } } return true; } /** Remove one indexed event from its aggregate projections. */ function vasp_event_decrement_projection($event, $fields) { global $wpdb; $table = vasp_event_aggregate_table(); $type = vasp_event_normalize_type($event['event_type'] ?? ''); $timestamp = (string) ($event['timestamp'] ?? current_time('mysql')); $date = preg_match('/^\d{4}-\d{2}-\d{2}/', $timestamp) ? substr($timestamp, 0, 10) : current_time('Y-m-d'); $country = vasp_event_normalize_country($event['country'] ?? 'ZZ'); $language = sanitize_text_field($event['language'] ?? 'unknown'); $is_internal = !empty($event['is_internal']) ? 1 : 0; $objects = array_merge([[ 'object_type' => '__total__', 'object_id' => 0, ]], vasp_event_projection_objects($event, $fields)); foreach ($objects as $object) { $params = [ $date, (string) $object['object_type'], (int) $object['object_id'], $type, $country, $language, $is_internal, ]; $updated = $wpdb->query($wpdb->prepare( "UPDATE %i SET event_count = event_count - 1 WHERE aggregate_date = %s AND object_type = %s AND object_id = %d AND event_type = %s AND country = %s AND language = %s AND is_internal = %d AND event_count > 0", array_merge([$table], $params) )); if ($updated === false || $wpdb->last_error) { return false; } $wpdb->query($wpdb->prepare( "DELETE FROM %i WHERE aggregate_date = %s AND object_type = %s AND object_id = %d AND event_type = %s AND country = %s AND language = %s AND is_internal = %d AND event_count = 0", array_merge([$table], $params) )); if ($wpdb->last_error) { return false; } } return true; } function vasp_event_store_indexed($event) { global $wpdb; $vasp_index_table = vasp_event_index_table(); if (!vasp_event_storage_schema_ready()) { $GLOBALS['vasp_event_index_last_inserted'] = false; return false; } if (vasp_event_is_bot_user_agent($event['user_agent'] ?? '')) { $GLOBALS['vasp_event_index_last_inserted'] = false; return false; } $fields = vasp_event_storage_fields($event); $payload = wp_json_encode($event['data'] ?? []); $uid = vasp_event_uid($event); $country = vasp_event_normalize_country($event['country'] ?? 'ZZ'); $language = sanitize_text_field($event['language'] ?? 'unknown'); $occurred = $event['timestamp'] ?? current_time('mysql'); $delivery_status = ($event['delivery_status'] ?? '') === 'ignored' ? 'ignored' : 'pending'; $wpdb->query('START TRANSACTION'); $inserted = $wpdb->query($wpdb->prepare( 'INSERT IGNORE INTO %i (event_uid, occurred_at, event_type, object_type, object_id, object_title, canonical_url, default_object_id, default_object_title, default_canonical_url, session_id, wp_user_id, wp_username, country, language, is_internal, payload, delivery_status) VALUES (%s,%s,%s,%s,%d,%s,%s,%d,%s,%s,%s,%d,%s,%s,%s,%d,%s,%s)', $vasp_index_table, $uid, $occurred, vasp_event_normalize_type($event['event_type'] ?? ''), $fields['object_type'], $fields['object_id'], $fields['object_title'], $fields['canonical_url'], $fields['default_object_id'], $fields['default_object_title'], $fields['default_canonical_url'], (string) ($event['session_id'] ?? ''), (int) ($event['wp_user_id'] ?? 0), (string) ($event['wp_username'] ?? ''), $country, $language, !empty($event['is_internal']) ? 1 : 0, $payload, $delivery_status )); if ($inserted === 1 && (!vasp_event_delivery_status_is_projected($delivery_status) || vasp_event_increment_projection($event, $fields))) { $wpdb->query('COMMIT'); $GLOBALS['vasp_event_index_last_inserted'] = true; return true; } if ($inserted === 0 && !$wpdb->last_error) { $wpdb->query('COMMIT'); $GLOBALS['vasp_event_index_last_inserted'] = false; return true; } $wpdb->query('ROLLBACK'); $GLOBALS['vasp_event_index_last_inserted'] = false; return false; } function vasp_event_increment_bot_excluded() { static $counted = false; global $wpdb; if ($counted || !vasp_event_storage_schema_ready()) { return; } $counted = true; $table = vasp_event_aggregate_table(); $wpdb->query($wpdb->prepare( "INSERT INTO %i (aggregate_date, object_type, object_id, event_type, country, language, is_internal, object_title, canonical_url, event_count) VALUES (%s, '', 0, 'bot.excluded', 'ZZ', 'unknown', 0, '', '', 1) ON DUPLICATE KEY UPDATE event_count = event_count + 1", $table, current_time('Y-m-d') )); } function vasp_event_query_events($args = []) { global $wpdb; $vasp_index_table = vasp_event_index_table(); if (!vasp_event_storage_schema_ready()) { return ['items' => [], 'total' => 0]; } $defaults = ['page' => 1, 'per_page' => 50, 'search' => '', 'event_type' => '', 'delivery_status' => '', 'country' => '', 'language' => '', 'is_internal' => null, 'date_from' => '', 'date_to' => '', 'orderby' => 'occurred_at', 'order' => 'desc']; $args = wp_parse_args($args, $defaults); $where = ['1=1']; $params = [$vasp_index_table]; foreach (['event_type', 'country', 'language'] as $field) { if ($args[$field] !== '') { $where[] = '%i = %s'; array_push($params, $field, $args[$field]); } } if (in_array($args['delivery_status'], ['pending', 'acknowledged', 'discarded', 'ignored'], true)) { $where[] = 'delivery_status = %s'; $params[] = $args['delivery_status']; } if ($args['is_internal'] !== null && $args['is_internal'] !== '') { $where[] = 'is_internal = %d'; $params[] = (int) $args['is_internal']; } if ($args['date_from']) { $where[] = 'occurred_at >= %s'; $params[] = $args['date_from'] . ' 00:00:00'; } if ($args['date_to']) { $where[] = 'occurred_at <= %s'; $params[] = $args['date_to'] . ' 23:59:59'; } if ($args['search'] !== '') { $search = trim((string) $args['search']); if ($search !== '' && ctype_digit($search)) { $where[] = '(object_id = %d OR default_object_id = %d OR wp_user_id = %d)'; array_push($params, (int) $search, (int) $search, (int) $search); } else { $like = '%' . $wpdb->esc_like($search) . '%'; $where[] = '(event_type LIKE %s OR object_title LIKE %s OR default_object_title LIKE %s OR canonical_url LIKE %s OR default_canonical_url LIKE %s OR session_id LIKE %s OR wp_username LIKE %s)'; array_push($params, $like, $like, $like, $like, $like, $like, $like); } } $where_sql = implode(' AND ', $where); $count_sql = 'SELECT COUNT(*) FROM %i WHERE ' . $where_sql; // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- WHERE is composed only of fixed placeholder clauses above; table/columns use %i and every value is bound. $total = (int) $wpdb->get_var($wpdb->prepare($count_sql, $params)); $per_page = max(1, min(500, (int) $args['per_page'])); $offset = (max(1, (int) $args['page']) - 1) * $per_page; $orderby = in_array($args['orderby'], ['occurred_at', 'event_type'], true) ? $args['orderby'] : 'occurred_at'; $order = strtolower((string) $args['order']) === 'asc' ? 'ASC' : 'DESC'; $query_params = array_merge($params, [$orderby, $per_page, $offset]); // SQL keywords cannot be bound as values. Both branches are fixed literals. $sort_sql = $order === 'ASC' ? ' ORDER BY %i ASC, event_id ASC LIMIT %d OFFSET %d' : ' ORDER BY %i DESC, event_id DESC LIMIT %d OFFSET %d'; $sql = 'SELECT event_id, event_uid, occurred_at, event_type, object_type, object_id, object_title, canonical_url, default_object_id, default_object_title, default_canonical_url, session_id, wp_user_id, wp_username, country, language, is_internal, delivery_status, delivered_at, payload FROM %i WHERE ' . $where_sql . $sort_sql; // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Fixed WHERE and ASC/DESC templates only; table/sort columns use %i and every value/limit is bound. $rows = $wpdb->get_results($wpdb->prepare($sql, $query_params), ARRAY_A); foreach ($rows as &$row) { $row['data'] = json_decode($row['payload'], true) ?: []; $row['country_code'] = $row['country']; $row['timestamp'] = $row['occurred_at']; $row['url'] = $row['canonical_url']; unset($row['payload']); } return ['items' => $rows, 'total' => $total]; } function vasp_event_get_filter_options() { global $wpdb; if (!vasp_event_storage_schema_ready()) { return ['event_types' => [], 'countries' => [], 'languages' => []]; } $table = vasp_event_index_table(); $map = function($values) { $result = []; foreach ($values as $value) { $result[$value] = $value; } return $result; }; return [ 'event_types' => $map($wpdb->get_col($wpdb->prepare("SELECT DISTINCT event_type FROM %i ORDER BY event_type", $table))), 'countries' => $map($wpdb->get_col($wpdb->prepare("SELECT DISTINCT country FROM %i ORDER BY country", $table))), 'languages' => $map($wpdb->get_col($wpdb->prepare("SELECT DISTINCT language FROM %i ORDER BY language", $table))), ]; } function vasp_event_get_analytics_projection($args = []) { global $wpdb; if (!vasp_event_storage_schema_ready()) { return ['total_events' => 0, 'top_products' => [], 'bots_excluded' => 0]; } $site_timezone = wp_timezone(); $args = wp_parse_args($args, ['date_from' => wp_date('Y-m-d', time() - 29 * DAY_IN_SECONDS, $site_timezone), 'date_to' => wp_date('Y-m-d', time(), $site_timezone), 'country' => '', 'language' => '', 'limit' => 10]); $vasp_aggregate_table = vasp_event_aggregate_table(); $params = [$vasp_aggregate_table, $args['date_from'], $args['date_to'], (int) (bool) $args['country'], $args['country'], (int) (bool) $args['language'], $args['language']]; $limit = max(1, min(100, (int) $args['limit'])); $total_events = (int) $wpdb->get_var($wpdb->prepare("SELECT COALESCE(SUM(event_count),0) FROM %i WHERE aggregate_date BETWEEN %s AND %s AND (%d = 0 OR country = %s) AND (%d = 0 OR language = %s) AND object_type='__total__'", $params)); return [ 'total_events' => $total_events, 'top_products' => $wpdb->get_results($wpdb->prepare("SELECT object_id, MAX(object_title) title, MAX(canonical_url) url, SUM(CASE WHEN event_type='cart.added' THEN event_count ELSE 0 END) added_to_cart, SUM(CASE WHEN event_type='order.completed' THEN event_count ELSE 0 END) purchases, SUM(event_count) total FROM %i WHERE aggregate_date BETWEEN %s AND %s AND (%d = 0 OR country = %s) AND (%d = 0 OR language = %s) AND object_type = 'product' AND event_type IN ('cart.added', 'order.completed') GROUP BY object_id ORDER BY total DESC, purchases DESC, added_to_cart DESC LIMIT %d", array_merge($params, [$limit])), ARRAY_A), 'bots_excluded' => (int) $wpdb->get_var($wpdb->prepare("SELECT COALESCE(SUM(event_count),0) FROM %i WHERE aggregate_date BETWEEN %s AND %s AND (%d = 0 OR country = %s) AND (%d = 0 OR language = %s) AND event_type='bot.excluded'", $params)), ]; } function vasp_event_backfill_index($session_limit = 20) { global $wpdb; $state = get_option(VASP_EVENT_INDEX_BACKFILL_OPTION, ['cursor' => 0, 'sessions_scanned' => 0, 'events_indexed' => 0, 'bots_skipped' => 0, 'complete' => false]); if (!vasp_event_storage_schema_ready() || !empty($state['complete'])) { return $state; } $bounded_limit = max(1, min(100, (int) $session_limit)); $vasp_options_table = $wpdb->options; $query_sessions = function($cursor) use ($wpdb, $bounded_limit, $vasp_options_table) { return $wpdb->get_results($wpdb->prepare( "SELECT option_id, option_name, option_value FROM %i WHERE option_id > %d AND option_name LIKE %s AND option_name NOT LIKE %s ORDER BY option_id ASC LIMIT %d", $vasp_options_table, (int) $cursor, $wpdb->esc_like('_transient_vasp_session_') . '%', $wpdb->esc_like('_transient_timeout_') . '%', $bounded_limit ), ARRAY_A); }; $rows = $query_sessions((int) $state['cursor']); if ($wpdb->last_error || !is_array($rows)) { vasp_create_error(VASP_ERROR_DATABASE_ERROR, ['reason' => 'event_backfill_read_failed']); return $state; } foreach ($rows as $row) { $events = maybe_unserialize($row['option_value']); $state['sessions_scanned']++; $state['cursor'] = (int) $row['option_id']; foreach ((array) $events as $event) { if (!is_array($event)) { continue; } if (vasp_event_is_bot_user_agent($event['user_agent'] ?? '')) { $state['bots_skipped']++; continue; } if (!vasp_event_validate_payload( (string) ($event['event_type'] ?? ''), isset($event['data']) && is_array($event['data']) ? $event['data'] : [], $event['url'] ?? null )) { continue; } $event_user_id = (int) ($event['wp_user_id'] ?? 0); $is_internal = $event_user_id > 0 && user_can($event_user_id, 'manage_options'); if ($is_internal && vasp_event_should_ignore_internal_traffic()) { continue; } $event['is_internal'] = $is_internal ? 1 : 0; if ($is_internal && empty($event['wp_username'])) { $user = get_userdata($event_user_id); $event['wp_username'] = $user ? $user->user_login : ''; } if (vasp_event_store_indexed($event) && !empty($GLOBALS['vasp_event_index_last_inserted'])) { $state['events_indexed']++; } } } if (count($rows) < $bounded_limit) { $state['complete'] = true; $state['cursor'] = 0; } update_option(VASP_EVENT_INDEX_BACKFILL_OPTION, $state, false); return $state; } function vasp_event_purge_expired_index($limit = 250) { global $wpdb; $vasp_index_table = vasp_event_index_table(); $vasp_aggregate_table = vasp_event_aggregate_table(); $limit = max(1, min(500, (int) $limit)); $cutoff = wp_date('Y-m-d H:i:s', time() - vasp_event_retention_days() * DAY_IN_SECONDS, wp_timezone()); $date = substr($cutoff, 0, 10); $wpdb->query($wpdb->prepare( "DELETE FROM %i WHERE occurred_at < %s AND delivery_status IN ('acknowledged', 'discarded', 'ignored') LIMIT %d", $vasp_index_table, $cutoff, $limit )); $wpdb->query($wpdb->prepare( 'DELETE FROM %i WHERE aggregate_date < %s LIMIT %d', $vasp_aggregate_table, $date, $limit )); } function vasp_event_get_index_status() { $state = get_option(VASP_EVENT_INDEX_BACKFILL_OPTION, []); return [ 'schema_ready' => vasp_event_storage_schema_ready(), 'backfill_complete' => !empty($state['complete']), 'sessions_scanned' => (int) ($state['sessions_scanned'] ?? 0), 'events_indexed' => (int) ($state['events_indexed'] ?? 0), 'bots_skipped' => (int) ($state['bots_skipped'] ?? 0), 'delivery' => function_exists('vasp_event_get_delivery_status') ? vasp_event_get_delivery_status() : [], ]; } /** Clear indexed event runtime data while keeping the installed schema. */ function vasp_event_reset_indexed_storage() { global $wpdb; $vasp_index_table = vasp_event_index_table(); $vasp_aggregate_table = vasp_event_aggregate_table(); $schema_ready = vasp_event_storage_schema_ready(); if (!$schema_ready && $wpdb->last_error) { return false; } if ($schema_ready) { if ($wpdb->query($wpdb->prepare('DELETE FROM %i', $vasp_index_table)) === false) { return false; } if ($wpdb->query($wpdb->prepare('DELETE FROM %i', $vasp_aggregate_table)) === false) { return false; } } delete_option(VASP_EVENT_INDEX_BACKFILL_OPTION); if (function_exists('vasp_event_reset_delivery_state')) { vasp_event_reset_delivery_state(); } return true; } /** Drop event storage and all schema/backfill markers during full uninstall. */ function vasp_event_uninstall_indexed_storage() { global $wpdb; $vasp_index_table = vasp_event_index_table(); $vasp_aggregate_table = vasp_event_aggregate_table(); $wpdb->query($wpdb->prepare( 'DROP TABLE IF EXISTS %i', $vasp_index_table)); $wpdb->query($wpdb->prepare( 'DROP TABLE IF EXISTS %i', $vasp_aggregate_table)); delete_option(VASP_EVENT_INDEX_BACKFILL_OPTION); delete_option(VASP_EVENT_STORAGE_SCHEMA_OPTION); if (function_exists('vasp_event_reset_delivery_state')) { vasp_event_reset_delivery_state(); } unset($GLOBALS['vasp_event_storage_schema_ready']); return true; }