$item) { $value[$key] = affgsc_feed_canonicalize($item); } return $value; } function affgsc_feed_canonical_json(array $value): string { $json = wp_json_encode(affgsc_feed_canonicalize($value), JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE); return is_string($json) ? $json : ''; } function affgsc_verify_manifest_signature(array $manifest): array { if (!function_exists('sodium_crypto_sign_verify_detached')) { return ['ok' => false, 'error' => 'The PHP Sodium extension is required to verify the AffGate feed']; } $signature_meta = isset($manifest['signature']) && is_array($manifest['signature']) ? $manifest['signature'] : []; if ((string)($signature_meta['algorithm'] ?? '') !== 'ed25519') { return ['ok' => false, 'error' => 'The feed manifest is not signed with Ed25519']; } $public_key_encoded = sanitize_text_field((string)($signature_meta['public_key'] ?? '')); $signature_encoded = sanitize_text_field((string)($signature_meta['value'] ?? '')); $public_key = base64_decode($public_key_encoded, true); $signature = base64_decode($signature_encoded, true); if (!is_string($public_key) || strlen($public_key) !== SODIUM_CRYPTO_SIGN_PUBLICKEYBYTES || !is_string($signature) || strlen($signature) !== SODIUM_CRYPTO_SIGN_BYTES ) { return ['ok' => false, 'error' => 'The feed signature contains an invalid public key or signature']; } $key_id = substr(hash('sha256', $public_key), 0, 32); if (!hash_equals($key_id, strtolower((string)($signature_meta['key_id'] ?? '')))) { return ['ok' => false, 'error' => 'The feed signing key identifier is invalid']; } $trusted_key_id = affgsc_trusted_feed_key_id($public_key_encoded); if ($trusted_key_id === '' || !hash_equals($trusted_key_id, $key_id)) { return ['ok' => false, 'error' => 'The feed was signed by an untrusted key']; } $unsigned = $manifest; unset($unsigned['signature']); $canonical = affgsc_feed_canonical_json($unsigned); if ($canonical === '' || !sodium_crypto_sign_verify_detached($signature, $canonical, $public_key)) { return ['ok' => false, 'error' => 'The AffGate feed manifest signature is invalid']; } update_option('affgsc_feed_public_key', $public_key_encoded, false); update_option('affgsc_feed_key_id', $key_id, false); return ['ok' => true, 'error' => '']; } function affgsc_fetch_manifest(bool $force = false): array { $cached = get_option(AFFGSC_MANIFEST_OPT, []); if (!$force && is_array($cached) && !empty($cached['provider_files'])) { return $cached; } if (!$force) { return ['provider_files' => []]; } $manifest = affgsc_remote_get_json((string)affgsc_setting('manifest_url')); if (!empty($manifest['error'])) { return $manifest; } if ((string)($manifest['schema'] ?? '') !== 'affgate-games-feed') { return ['error' => 'Invalid AffGate manifest schema']; } $verified = affgsc_verify_manifest_signature($manifest); if (empty($verified['ok'])) return ['error' => (string)$verified['error']]; $old_time = is_array($cached) ? strtotime((string)($cached['generated_at'] ?? '')) : false; $new_time = strtotime((string)($manifest['generated_at'] ?? '')); if ($old_time && $new_time && $new_time < $old_time) { return ['error' => 'The AffGate manifest is older than the version already trusted by this site']; } $manifest['cached_at'] = affgsc_now(); update_option(AFFGSC_MANIFEST_OPT, $manifest, false); return $manifest; } function affgsc_refresh_manifest_cache(): void { $manifest = affgsc_fetch_manifest(true); if (!empty($manifest['error']) && !wp_next_scheduled('affgsc_refresh_manifest_cache')) { wp_schedule_single_event(time() + HOUR_IN_SECONDS, 'affgsc_refresh_manifest_cache', [], true); } elseif (empty($manifest['error'])) { affgsc_schedule_frequency_refresh(30); } } function affgsc_maybe_prime_manifest(): void { $cached = get_option(AFFGSC_MANIFEST_OPT, []); if (is_array($cached) && !empty($cached['provider_files'])) return; if (get_transient('affgsc_manifest_prime_lock')) return; set_transient('affgsc_manifest_prime_lock', 1, HOUR_IN_SECONDS); affgsc_refresh_manifest_cache(); } function affgsc_manifest_provider_files(array $manifest): array { $files = isset($manifest['provider_files']) && is_array($manifest['provider_files']) ? $manifest['provider_files'] : []; $out = []; foreach ($files as $file) { if (!is_array($file)) continue; $slug = sanitize_title((string)($file['provider_slug'] ?? '')); $url = affgsc_sanitize_https_url((string)($file['url'] ?? '')); if ($slug === '' || $url === '') continue; $out[$slug] = $file; } ksort($out); return $out; } function affgsc_fetch_provider_payload(array $provider_file): array { $url = affgsc_sanitize_https_url((string)($provider_file['url'] ?? '')); if ($url === '') return ['error' => 'Empty provider file URL']; return affgsc_remote_get_json($url, $provider_file); } function affgsc_schedule_frequency_refresh(int $delay = 5): void { if ((string)affgsc_setting('show_interest_chart', '0') !== '1' && (string)affgsc_setting('show_country_chart', '0') !== '1' ) { return; } $scheduled_for = wp_next_scheduled('affgsc_refresh_frequency_data'); $target = time() + max(1, $delay); if ($scheduled_for && $scheduled_for > $target + 5) { wp_unschedule_event($scheduled_for, 'affgsc_refresh_frequency_data'); $scheduled_for = false; } if (!$scheduled_for) { wp_schedule_single_event($target, 'affgsc_refresh_frequency_data', [], true); } if (function_exists('spawn_cron')) { spawn_cron(time()); } } function affgsc_write_frequency_sync_status(array $status): void { $status['checked_at'] = affgsc_now(); update_option('affgsc_frequency_sync_status', $status, false); if (empty($status['ok']) && ((string)affgsc_setting('show_interest_chart', '0') === '1' || (string)affgsc_setting('show_country_chart', '0') === '1') ) { affgsc_schedule_frequency_refresh(6 * HOUR_IN_SECONDS); } } function affgsc_frequency_monthly_values($raw): array { if (!is_array($raw)) return []; $monthly = []; foreach ($raw as $month => $value) { $month = (string)$month; if (!preg_match('/^\d{4}-(0[1-9]|1[0-2])$/', $month) || !is_numeric($value)) continue; $monthly[$month] = max(0, (int)round((float)$value)); } ksort($monthly, SORT_STRING); return count($monthly) > 12 ? array_slice($monthly, -12, null, true) : $monthly; } function affgsc_frequency_series_values($raw): array { if (!is_array($raw)) return []; $series = []; foreach (['slot', 'demo', 'freeplay'] as $key) { $monthly = affgsc_frequency_monthly_values($raw[$key] ?? []); if ($monthly) $series[$key] = $monthly; } return $series; } function affgsc_frequency_country_values($raw): array { if (!is_array($raw) || !is_numeric($raw['total'] ?? null) || !is_array($raw['top'] ?? null)) return []; $total = max(0.0, (float)$raw['total']); $top = []; foreach (array_slice($raw['top'], 0, 7) as $item) { if (!is_array($item) || !is_numeric($item['value'] ?? null)) continue; $value = max(0.0, (float)$item['value']); if ($value <= 0) continue; $code = strtoupper(sanitize_text_field((string)($item['code'] ?? ''))); if (!preg_match('/^[A-Z]{2}$/', $code)) $code = ''; $name = substr(sanitize_text_field((string)($item['name'] ?? '')), 0, 120); if ($code === '' && $name === '') continue; $top[] = [ 'code' => $code, 'name' => $name, 'value' => round($value, 4), ]; } if (!$top || $total <= 0) return []; return ['total' => round($total, 4), 'top' => $top]; } function affgsc_upsert_frequency_rows(array $rows): bool { if (!$rows) return true; global $wpdb; $table = affgsc_frequency_table(); $placeholders = []; $values = []; foreach ($rows as $row) { $placeholders[] = '(%d,%s,%s,%s,%s,%s)'; array_push( $values, (int)$row['source_id'], (string)$row['monthly'], (string)$row['countries'], (string)$row['data_updated_at'], (string)$row['feed_generated_at'], (string)$row['synced_at'] ); } $sql = "INSERT INTO {$table} (source_id, monthly, countries, data_updated_at, feed_generated_at, synced_at) VALUES " . implode(',', $placeholders) . ' ON DUPLICATE KEY UPDATE monthly=VALUES(monthly), countries=VALUES(countries), data_updated_at=VALUES(data_updated_at), feed_generated_at=VALUES(feed_generated_at), synced_at=VALUES(synced_at)'; // The table and placeholder list are plugin-generated; every row value is prepared. // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.NotPrepared $saved = $wpdb->query($wpdb->prepare($sql, $values)) !== false; if ($saved) { foreach ($rows as $row) { wp_cache_delete('frequency_' . (int)$row['source_id'], 'affgsc'); } } return $saved; } function affgsc_refresh_frequency_data(): void { if ((string)affgsc_setting('show_interest_chart', '0') !== '1' && (string)affgsc_setting('show_country_chart', '0') !== '1' ) return; if (get_transient('affgsc_frequency_sync_lock')) return; set_transient('affgsc_frequency_sync_lock', 1, 10 * MINUTE_IN_SECONDS); try { $connection = affgsc_ensure_installation_secret(); if (empty($connection['ok'])) { affgsc_write_frequency_sync_status(['ok' => false, 'error' => (string)($connection['message'] ?? 'Could not connect securely to AffGate')]); return; } $manifest = affgsc_fetch_manifest(true); if (!empty($manifest['error'])) { affgsc_write_frequency_sync_status(['ok' => false, 'error' => (string)$manifest['error']]); return; } $file = isset($manifest['frequency_file']) && is_array($manifest['frequency_file']) ? $manifest['frequency_file'] : []; $url = affgsc_sanitize_https_url((string)($file['url'] ?? '')); if ($url === '') { affgsc_write_frequency_sync_status(['ok' => false, 'error' => 'Frequency file is not available in the manifest']); return; } $payload = affgsc_remote_get_json($url, $file); if (!empty($payload['error'])) { affgsc_write_frequency_sync_status(['ok' => false, 'error' => (string)$payload['error'], 'url' => $url]); return; } if ( (string)($payload['schema'] ?? '') !== 'affgate-game-search-frequency' || (int)($payload['version'] ?? 0) < 2 || !isset($payload['games']) || !is_array($payload['games']) ) { affgsc_write_frequency_sync_status(['ok' => false, 'error' => 'Invalid frequency feed schema', 'url' => $url]); return; } $existing = affgsc_existing_source_ids_map(); $feed_generated_at = substr(sanitize_text_field((string)($payload['generated_at'] ?? '')), 0, 40); if ($feed_generated_at === '') { $feed_generated_at = gmdate('Y-m-d\TH:i:s\Z'); } $synced_at = current_time('mysql', true); $rows = []; $saved = 0; foreach ($payload['games'] as $game) { if (!is_array($game)) continue; $source_id = absint($game['source_id'] ?? 0); if ($source_id <= 0 || !isset($existing[$source_id])) continue; $series = affgsc_frequency_series_values($game['series'] ?? []); $countries = affgsc_frequency_country_values($game['countries'] ?? []); if (!$series && !$countries) continue; $encoded = wp_json_encode($series, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE); $countries_encoded = wp_json_encode($countries, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE); if (!is_string($encoded) || !is_string($countries_encoded)) continue; $rows[] = [ 'source_id' => $source_id, 'monthly' => $encoded, 'countries' => $countries_encoded, 'data_updated_at' => substr(sanitize_text_field((string)($game['updated_at'] ?? '')), 0, 40), 'feed_generated_at' => $feed_generated_at, 'synced_at' => $synced_at, ]; if (count($rows) >= 200) { if (!affgsc_upsert_frequency_rows($rows)) { affgsc_write_frequency_sync_status(['ok' => false, 'error' => 'Could not save frequency data', 'url' => $url]); return; } $saved += count($rows); $rows = []; } } if ($rows) { if (!affgsc_upsert_frequency_rows($rows)) { affgsc_write_frequency_sync_status(['ok' => false, 'error' => 'Could not save frequency data', 'url' => $url]); return; } $saved += count($rows); } global $wpdb; $table = affgsc_frequency_table(); // The table is plugin-owned; the feed timestamp is prepared. // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared $wpdb->query($wpdb->prepare("DELETE FROM {$table} WHERE feed_generated_at <> %s", $feed_generated_at)); affgsc_write_frequency_sync_status([ 'ok' => true, 'url' => $url, 'generated_at' => $feed_generated_at, 'source_month' => sanitize_text_field((string)($payload['source_month'] ?? '')), 'games_saved' => $saved, ]); } finally { delete_transient('affgsc_frequency_sync_lock'); } }