$value) { if (in_array((string)$status, ['trash', 'auto-draft', 'inherit'], true)) continue; $active_games += (int)$value; } return $active_games; } function affgsc_send_installation_status(string $event = 'heartbeat', bool $blocking = true): array { $event = in_array($event, ['activate', 'heartbeat', 'deactivate', 'uninstall'], true) ? $event : 'heartbeat'; $endpoint = affgsc_sanitize_https_url((string)affgsc_setting( 'installation_endpoint', 'https://affgate.com/wp-json/affgate-games/v1/installations' )); if ($endpoint === '') return ['ok' => false, 'message' => 'Installation endpoint is empty']; if (affgsc_install_api_secret() === '') return ['ok' => true, 'message' => 'This site has not connected to AffGate']; $site_url = home_url('/'); $payload = [ 'event' => $event, 'install_id' => affgsc_install_id(), 'site_url_hash' => hash('sha256', $site_url), 'site_url' => esc_url_raw($site_url), 'site_host' => strtolower((string)(wp_parse_url($site_url, PHP_URL_HOST) ?: '')), 'installed_at' => (string)get_option('affgsc_installed_at', ''), 'active_games' => affgsc_active_game_count(), 'plugin_version' => AFFGSC_VERSION, ]; $res = affgsc_signed_remote_post($endpoint, 'installation', $payload, [ 'timeout' => $blocking ? 8 : 1, 'blocking' => $blocking, 'limit_response_size' => 65536, ]); if (is_wp_error($res)) return ['ok' => false, 'message' => $res->get_error_message()]; if (!$blocking) return ['ok' => true, 'message' => 'Installation event queued']; $code = (int)wp_remote_retrieve_response_code($res); return $code >= 200 && $code < 300 ? ['ok' => true, 'message' => 'Installation status sent'] : ['ok' => false, 'message' => 'HTTP ' . $code]; } function affgsc_installation_heartbeat(): void { if (affgsc_install_api_secret() === '') return; affgsc_send_installation_status('heartbeat', true); if ((string)affgsc_setting('show_interest_chart', '0') === '1' || (string)affgsc_setting('show_country_chart', '0') === '1' ) { affgsc_schedule_frequency_refresh(30); } } function affgsc_maybe_register_installation(): void { affgsc_ensure_installation_secret(); } function affgsc_uninstall_plugin(): void { affgsc_send_installation_status('uninstall', true); if ((string)affgsc_setting('delete_data_on_uninstall', '0') === '1') { affgsc_delete_all_plugin_data(); } } function affgsc_delete_all_plugin_data(): void { global $wpdb; affgsc_unschedule_events(); affgsc_register_content_types(); $delete_passes = 0; do { $game_ids = get_posts([ 'post_type' => AFFGSC_POST_TYPE, 'post_status' => array_values(get_post_stati()), 'fields' => 'ids', 'posts_per_page' => 100, 'orderby' => 'ID', 'order' => 'ASC', 'no_found_rows' => true, ]); $deleted_this_pass = 0; foreach ((array)$game_ids as $post_id) { if (wp_delete_post((int)$post_id, true) instanceof WP_Post) $deleted_this_pass++; } $delete_passes++; } while (!empty($game_ids) && $deleted_this_pass > 0 && $delete_passes < 10000); $created_page_ids = get_posts([ 'post_type' => 'page', 'post_status' => array_values(get_post_stati()), 'fields' => 'ids', 'posts_per_page' => -1, // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key -- One-time uninstall cleanup of plugin-owned pages. 'meta_key' => '_affgsc_created_catalog_page', // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_value -- One-time uninstall cleanup of plugin-owned pages. 'meta_value' => '1', 'no_found_rows' => true, ]); foreach ((array)$created_page_ids as $page_id) wp_delete_post((int)$page_id, true); $cached_attachment_ids = get_posts([ 'post_type' => 'attachment', 'post_status' => array_values(get_post_stati()), 'fields' => 'ids', 'posts_per_page' => -1, // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key -- One-time uninstall cleanup of plugin-owned attachments. 'meta_key' => '_affgsc_cached_game_image', // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_value -- One-time uninstall cleanup of plugin-owned attachments. 'meta_value' => '1', 'no_found_rows' => true, ]); foreach ((array)$cached_attachment_ids as $attachment_id) wp_delete_attachment((int)$attachment_id, true); foreach ([AFFGSC_TAX_PROVIDER, AFFGSC_TAX_TYPE, AFFGSC_TAX_THEME, AFFGSC_TAX_VOLATILITY] as $taxonomy) { $term_ids = get_terms(['taxonomy' => $taxonomy, 'hide_empty' => false, 'fields' => 'ids']); if (is_wp_error($term_ids)) continue; foreach ((array)$term_ids as $term_id) wp_delete_term((int)$term_id, $taxonomy); } foreach ([affgsc_ratings_table(), affgsc_reports_table(), affgsc_frequency_table()] as $table) { // Tables are plugin-owned and removed only after the administrator opted in to uninstall cleanup. // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange $wpdb->query('DROP TABLE IF EXISTS `' . esc_sql($table) . '`'); } // One-time uninstall cleanup cannot use the Options API because the option names are discovered by prefix. // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $wpdb->query($wpdb->prepare( "DELETE FROM {$wpdb->options} WHERE option_name LIKE %s", $wpdb->esc_like('affgsc_') . '%' )); } function affgsc_stats(): array { $stats = get_option(AFFGSC_STATS_OPT, []); return is_array($stats) ? $stats : []; } function affgsc_write_stats(array $stats): void { update_option(AFFGSC_STATS_OPT, $stats, false); } function affgsc_ratings_table(): string { global $wpdb; return $wpdb->prefix . 'affgsc_ratings'; } function affgsc_reports_table(): string { global $wpdb; return $wpdb->prefix . 'affgsc_reports'; } function affgsc_frequency_table(): string { global $wpdb; return $wpdb->prefix . 'affgsc_frequency'; } function affgsc_install_tables(): void { global $wpdb; require_once ABSPATH . 'wp-admin/includes/upgrade.php'; $charset = $wpdb->get_charset_collate(); $table = affgsc_ratings_table(); $sql = "CREATE TABLE {$table} ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, post_id BIGINT UNSIGNED NOT NULL, source_id BIGINT UNSIGNED NOT NULL, voter_hash CHAR(64) NOT NULL, ip_hash CHAR(64) NOT NULL, rating TINYINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, PRIMARY KEY (id), UNIQUE KEY post_voter (post_id, voter_hash), KEY post_id (post_id), KEY source_id (source_id), KEY ip_hash (ip_hash) ) {$charset};"; dbDelta($sql); $reports_table = affgsc_reports_table(); $reports_sql = "CREATE TABLE {$reports_table} ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, post_id BIGINT UNSIGNED NOT NULL, source_id BIGINT UNSIGNED NOT NULL, provider_slug VARCHAR(200) NOT NULL DEFAULT '', voter_hash CHAR(64) NOT NULL, ip_hash CHAR(64) NOT NULL, report_date DATE NOT NULL, message VARCHAR(500) NOT NULL DEFAULT '', page_url TEXT NULL, demo_url TEXT NULL, created_at DATETIME NOT NULL, PRIMARY KEY (id), UNIQUE KEY post_voter_day (post_id, voter_hash, report_date), KEY post_id (post_id), KEY source_id (source_id), KEY provider_slug (provider_slug), KEY report_date (report_date), KEY ip_hash (ip_hash) ) {$charset};"; dbDelta($reports_sql); $frequency_table = affgsc_frequency_table(); $frequency_sql = "CREATE TABLE {$frequency_table} ( source_id BIGINT UNSIGNED NOT NULL, monthly LONGTEXT NOT NULL, countries LONGTEXT NOT NULL, data_updated_at VARCHAR(40) NOT NULL DEFAULT '', feed_generated_at VARCHAR(40) NOT NULL DEFAULT '', synced_at DATETIME NOT NULL, PRIMARY KEY (source_id), KEY feed_generated_at (feed_generated_at) ) {$charset};"; dbDelta($frequency_sql); update_option('affgsc_db_version', AFFGSC_DB_VERSION, false); } function affgsc_maybe_install_tables(): void { if ((string)get_option('affgsc_db_version', '') !== AFFGSC_DB_VERSION) { affgsc_install_tables(); } } function affgsc_maybe_cleanup_old_reports(): void { if (get_transient('affgsc_reports_cleanup_done')) { return; } affgsc_cleanup_old_reports(); set_transient('affgsc_reports_cleanup_done', 1, DAY_IN_SECONDS); } function affgsc_cleanup_old_reports(): void { global $wpdb; $table = affgsc_reports_table(); $cutoff = gmdate('Y-m-d H:i:s', current_time('timestamp') - 30 * DAY_IN_SECONDS); // The table is plugin-owned; the timestamp value 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 created_at < %s", $cutoff)); } function affgsc_local_stats(): array { $stats = get_option(AFFGSC_LOCAL_STATS_OPT, []); return is_array($stats) ? $stats : []; } function affgsc_write_local_stats(array $stats): void { update_option(AFFGSC_LOCAL_STATS_OPT, $stats, false); } function affgsc_delete_game_data(int $post_id): void { if ($post_id <= 0 || get_post_type($post_id) !== AFFGSC_POST_TYPE) { return; } global $wpdb; $source_id = (int)get_post_meta($post_id, 'affgsc_source_id', true); // Plugin-owned rows are deleted together with their game post. // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $wpdb->delete(affgsc_ratings_table(), ['post_id' => $post_id], ['%d']); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $wpdb->delete(affgsc_reports_table(), ['post_id' => $post_id], ['%d']); wp_cache_delete('rating_summary_' . $post_id, 'affgsc'); if ($source_id <= 0) { return; } // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $wpdb->delete(affgsc_frequency_table(), ['source_id' => $source_id], ['%d']); wp_cache_delete('frequency_' . $source_id, 'affgsc'); $outbound = affgsc_stats(); unset($outbound['games'][(string)$source_id]); if (isset($outbound['reports']) && is_array($outbound['reports'])) { $outbound['reports'] = array_values(array_filter($outbound['reports'], static function ($report) use ($source_id): bool { return !is_array($report) || (int)($report['game_id'] ?? 0) !== $source_id; })); } affgsc_write_stats($outbound); $local = affgsc_local_stats(); foreach ($local as $date => &$games) { if (is_array($games)) { unset($games[(string)$source_id]); } } unset($games); affgsc_write_local_stats($local); } function affgsc_record_local_event(int $source_id, string $event): void { if ($source_id <= 0 || !in_array($event, ['launch', 'real_play', 'report'], true)) return; $date = function_exists('wp_date') ? wp_date('Y-m-d') : gmdate('Y-m-d'); $stats = affgsc_local_stats(); if (!isset($stats[$date]) || !is_array($stats[$date])) { $stats[$date] = []; } $key = (string)$source_id; if (!isset($stats[$date][$key]) || !is_array($stats[$date][$key])) { $stats[$date][$key] = ['game_id' => $source_id, 'launches' => 0, 'real_play_clicks' => 0, 'reports' => 0]; } if ($event === 'launch') $stats[$date][$key]['launches'] = (int)($stats[$date][$key]['launches'] ?? 0) + 1; if ($event === 'real_play') $stats[$date][$key]['real_play_clicks'] = (int)($stats[$date][$key]['real_play_clicks'] ?? 0) + 1; if ($event === 'report') $stats[$date][$key]['reports'] = (int)($stats[$date][$key]['reports'] ?? 0) + 1; $stats['updated_at'] = affgsc_now(); affgsc_write_local_stats($stats); } function affgsc_record_event(int $post_id, string $event): void { if (!affgsc_is_public_game($post_id) || !in_array($event, ['view', 'launch', 'real_play'], true)) return; $source_id = (int)get_post_meta($post_id, 'affgsc_source_id', true); if ($source_id <= 0) return; if ((string)affgsc_setting('share_usage_stats', '0') === '1') { $stats = affgsc_stats(); $key = (string)$source_id; if (!isset($stats['games'][$key])) { $stats['games'][$key] = ['game_id' => $source_id, 'views' => 0, 'launches' => 0, 'real_play_clicks' => 0]; } if ($event === 'view') $stats['games'][$key]['views'] = (int)($stats['games'][$key]['views'] ?? 0) + 1; if ($event === 'launch') $stats['games'][$key]['launches'] = (int)($stats['games'][$key]['launches'] ?? 0) + 1; if ($event === 'real_play') $stats['games'][$key]['real_play_clicks'] = (int)($stats['games'][$key]['real_play_clicks'] ?? 0) + 1; $stats['updated_at'] = affgsc_now(); affgsc_write_stats($stats); } affgsc_record_local_event($source_id, $event); } function affgsc_report_provider_slug(int $post_id): string { $terms = taxonomy_exists(AFFGSC_TAX_PROVIDER) ? get_the_terms($post_id, AFFGSC_TAX_PROVIDER) : []; if (is_wp_error($terms) || empty($terms[0])) return ''; return sanitize_title((string)$terms[0]->slug); } function affgsc_report_message(string $message): string { $message = wp_strip_all_tags($message); $message = sanitize_textarea_field($message); $message = preg_replace('/\s+/', ' ', trim($message)); return function_exists('mb_substr') ? mb_substr((string)$message, 0, 500) : substr((string)$message, 0, 500); } function affgsc_record_report(int $post_id, string $page_url, string $reason = 'User reported demo error'): array { global $wpdb; if (!affgsc_is_public_game($post_id)) { return ['ok' => false, 'message' => __('Invalid game.', 'affgate-slot-catalog')]; } $source_id = (int)get_post_meta($post_id, 'affgsc_source_id', true); if ($source_id <= 0) { return ['ok' => false, 'message' => __('Invalid game source.', 'affgate-slot-catalog')]; } $report_date = function_exists('wp_date') ? wp_date('Y-m-d') : gmdate('Y-m-d'); $voter_hash = affgsc_rating_voter_hash(true); $ip_hash = affgsc_rating_ip_hash(); $table = affgsc_reports_table(); // The table is plugin-owned and all visitor-derived values are prepared. // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared $existing = (int)$wpdb->get_var($wpdb->prepare( "SELECT id FROM {$table} WHERE post_id = %d AND report_date = %s AND (voter_hash = %s OR ip_hash = %s) LIMIT 1", $post_id, $report_date, $voter_hash, $ip_hash )); // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared if ($existing > 0) { return ['ok' => false, 'already_reported' => true, 'message' => __('You have already reported this game today.', 'affgate-slot-catalog')]; } $clean_reason = affgsc_report_message($reason); if ($clean_reason === '') { $clean_reason = __('User reported demo error', 'affgate-slot-catalog'); } $now = current_time('mysql'); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery $inserted = $wpdb->insert($table, [ 'post_id' => $post_id, 'source_id' => $source_id, 'provider_slug' => affgsc_report_provider_slug($post_id), 'voter_hash' => $voter_hash, 'ip_hash' => $ip_hash, 'report_date' => $report_date, 'message' => $clean_reason, 'page_url' => esc_url_raw($page_url), 'demo_url' => esc_url_raw((string)get_post_meta($post_id, 'affgsc_demo_url', true)), 'created_at' => $now, ], ['%d', '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s']); if (!$inserted) { return ['ok' => false, 'message' => __('Report could not be saved.', 'affgate-slot-catalog')]; } if ((string)affgsc_setting('share_usage_stats', '0') === '1') { $stats = affgsc_stats(); if (!isset($stats['reports']) || !is_array($stats['reports'])) { $stats['reports'] = []; } $stats['reports'][] = [ 'game_id' => $source_id, 'provider_slug' => affgsc_report_provider_slug($post_id), 'page_url' => esc_url_raw($page_url), 'demo_url' => esc_url_raw((string)get_post_meta($post_id, 'affgsc_demo_url', true)), 'reason' => $clean_reason, 'created_at' => affgsc_now(), ]; $stats['reports'] = array_slice($stats['reports'], -500); $stats['updated_at'] = affgsc_now(); affgsc_write_stats($stats); } affgsc_record_local_event($source_id, 'report'); return ['ok' => true, 'message' => __('Report sent. Thank you.', 'affgate-slot-catalog')]; } function affgsc_ajax_record_event(): void { check_ajax_referer('affgsc_public_action', 'nonce'); if (affgsc_public_rate_limited('record_event', 120, HOUR_IN_SECONDS)) { wp_send_json_error(['message' => __('Too many requests. Please try again later.', 'affgate-slot-catalog')], 429); } $post_id = isset($_POST['post_id']) && is_scalar($_POST['post_id']) ? absint(wp_unslash((string)$_POST['post_id'])) : 0; $event = isset($_POST['event']) && is_scalar($_POST['event']) ? sanitize_key(wp_unslash((string)$_POST['event'])) : ''; if (!affgsc_is_public_game($post_id) || !in_array($event, ['launch', 'real_play'], true)) { wp_send_json_error(['message' => __('Invalid event.', 'affgate-slot-catalog')], 400); } affgsc_record_event($post_id, $event); wp_send_json_success(['ok' => true]); } function affgsc_ajax_report_demo(): void { check_ajax_referer('affgsc_public_action', 'nonce'); if (affgsc_public_rate_limited('report_demo', 30, HOUR_IN_SECONDS)) { wp_send_json_error(['message' => __('Too many requests. Please try again later.', 'affgate-slot-catalog')], 429); } $post_id = isset($_POST['post_id']) && is_scalar($_POST['post_id']) ? absint(wp_unslash((string)$_POST['post_id'])) : 0; $page_url = isset($_POST['page_url']) && is_scalar($_POST['page_url']) ? esc_url_raw(wp_unslash((string)$_POST['page_url'])) : ''; $reason = isset($_POST['reason']) && is_scalar($_POST['reason']) ? affgsc_report_message(sanitize_textarea_field(wp_unslash((string)$_POST['reason']))) : ''; if ($reason === '') { wp_send_json_error(['ok' => false, 'message' => __('Please describe the problem before sending the report.', 'affgate-slot-catalog')], 400); } $result = affgsc_record_report($post_id, $page_url, $reason); if (!empty($result['ok'])) { wp_send_json_success($result); } wp_send_json_error($result, !empty($result['already_reported']) ? 409 : 400); } function affgsc_rating_ip_hash(): string { $ip = isset($_SERVER['REMOTE_ADDR']) ? sanitize_text_field(wp_unslash((string)$_SERVER['REMOTE_ADDR'])) : ''; $ua = isset($_SERVER['HTTP_USER_AGENT']) ? sanitize_text_field(wp_unslash((string)$_SERVER['HTTP_USER_AGENT'])) : ''; return hash_hmac('sha256', $ip . '|' . $ua, wp_salt('auth')); } function affgsc_rating_voter_hash(bool $create_cookie = true): string { if (is_user_logged_in()) { return hash_hmac('sha256', 'user:' . get_current_user_id(), wp_salt('auth')); } $cookie = isset($_COOKIE['affgsc_voter_id']) ? sanitize_text_field((string)wp_unslash($_COOKIE['affgsc_voter_id'])) : ''; if (!preg_match('/^[a-f0-9-]{32,40}$/i', $cookie)) { if (!$create_cookie) { return ''; } $cookie = wp_generate_uuid4(); $_COOKIE['affgsc_voter_id'] = $cookie; $path = defined('COOKIEPATH') && COOKIEPATH ? COOKIEPATH : '/'; $domain = defined('COOKIE_DOMAIN') ? COOKIE_DOMAIN : ''; setcookie('affgsc_voter_id', $cookie, [ 'expires' => time() + YEAR_IN_SECONDS, 'path' => $path, 'domain' => $domain, 'secure' => is_ssl(), 'httponly' => true, 'samesite' => 'Lax', ]); } return hash_hmac('sha256', 'anon:' . $cookie, wp_salt('auth')); } function affgsc_rating_summary(int $post_id): array { global $wpdb; if ($post_id <= 0) return ['average' => 0.0, 'count' => 0, 'user_rating' => 0]; $table = affgsc_ratings_table(); $summary_cache_key = 'rating_summary_' . $post_id; $row = wp_cache_get($summary_cache_key, 'affgsc'); if (false === $row) { // The table is plugin-owned and post_id is prepared. // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared $row = $wpdb->get_row($wpdb->prepare("SELECT COUNT(*) AS total, AVG(rating) AS average FROM {$table} WHERE post_id = %d", $post_id), ARRAY_A); wp_cache_set($summary_cache_key, is_array($row) ? $row : [], 'affgsc', HOUR_IN_SECONDS); } $voter_hash = affgsc_rating_voter_hash(false); // This visitor-specific lookup must not be shared through the object cache. // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared $user_rating = $voter_hash !== '' ? (int)$wpdb->get_var($wpdb->prepare("SELECT rating FROM {$table} WHERE post_id = %d AND voter_hash = %s LIMIT 1", $post_id, $voter_hash)) : 0; return [ 'average' => isset($row['average']) ? round((float)$row['average'], 2) : 0.0, 'count' => isset($row['total']) ? (int)$row['total'] : 0, 'user_rating' => $user_rating, ]; } function affgsc_rate_game(int $post_id, int $rating): array { global $wpdb; $rating = min(5, max(1, $rating)); if (!affgsc_is_public_game($post_id)) { return ['ok' => false, 'message' => __('Invalid game.', 'affgate-slot-catalog')]; } $source_id = (int)get_post_meta($post_id, 'affgsc_source_id', true); if ($source_id <= 0) { return ['ok' => false, 'message' => __('Invalid game source.', 'affgate-slot-catalog')]; } $table = affgsc_ratings_table(); $voter_hash = affgsc_rating_voter_hash(true); $ip_hash = affgsc_rating_ip_hash(); // This visitor-specific duplicate-vote lookup must not be cached. // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared $existing = (int)$wpdb->get_var($wpdb->prepare("SELECT rating FROM {$table} WHERE post_id = %d AND (voter_hash = %s OR ip_hash = %s) LIMIT 1", $post_id, $voter_hash, $ip_hash)); if ($existing > 0) { $summary = affgsc_rating_summary($post_id); $summary['user_rating'] = $existing; return [ 'ok' => false, 'already_voted' => true, 'message' => __('You have already rated this game.', 'affgate-slot-catalog'), 'summary' => $summary, ]; } $now = current_time('mysql'); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery $inserted = $wpdb->insert($table, [ 'post_id' => $post_id, 'source_id' => $source_id, 'voter_hash' => $voter_hash, 'ip_hash' => $ip_hash, 'rating' => $rating, 'created_at' => $now, 'updated_at' => $now, ], ['%d', '%d', '%s', '%s', '%d', '%s', '%s']); if (!$inserted) { return ['ok' => false, 'message' => __('Rating could not be saved.', 'affgate-slot-catalog')]; } wp_cache_delete('rating_summary_' . $post_id, 'affgsc'); $summary = affgsc_rating_summary($post_id); update_post_meta($post_id, 'affgsc_rating_average', (string)$summary['average']); update_post_meta($post_id, 'affgsc_rating_count', (int)$summary['count']); return ['ok' => true, 'message' => __('Thank you for rating.', 'affgate-slot-catalog'), 'summary' => $summary]; } function affgsc_ajax_rate_game(): void { check_ajax_referer('affgsc_public_action', 'nonce'); if (affgsc_public_rate_limited('rate_game', 30, HOUR_IN_SECONDS)) { wp_send_json_error(['message' => __('Too many requests. Please try again later.', 'affgate-slot-catalog')], 429); } $post_id = isset($_POST['post_id']) && is_scalar($_POST['post_id']) ? absint(wp_unslash((string)$_POST['post_id'])) : 0; $rating = isset($_POST['rating']) && is_scalar($_POST['rating']) ? absint(wp_unslash((string)$_POST['rating'])) : 0; $result = affgsc_rate_game($post_id, $rating); if (!empty($result['ok'])) { wp_send_json_success($result); } wp_send_json_error($result, !empty($result['already_voted']) ? 409 : 400); } function affgsc_send_stats(): array { if ((string)affgsc_setting('share_usage_stats', '0') !== '1') { return ['ok' => true, 'message' => 'Usage sharing is disabled']; } $endpoint = affgsc_sanitize_https_url((string)affgsc_setting('stats_endpoint', '')); if ($endpoint === '') return ['ok' => false, 'message' => 'Stats endpoint is empty']; $connection = affgsc_ensure_installation_secret(); if (empty($connection['ok'])) return $connection; $payload = get_option('affgsc_stats_outbox', []); $payload = is_array($payload) ? $payload : []; if (!$payload) { $stats = affgsc_stats(); $games = isset($stats['games']) && is_array($stats['games']) ? array_values($stats['games']) : []; $reports = isset($stats['reports']) && is_array($stats['reports']) ? array_values($stats['reports']) : []; $ratings = affgsc_rating_export_rows(); if (!$games && !$reports && !$ratings) return ['ok' => true, 'message' => 'No stats to send']; $site_url = home_url('/'); $payload = [ 'batch_id' => wp_generate_uuid4(), 'install_id' => affgsc_install_id(), 'site_url_hash' => hash('sha256', $site_url), 'site_url' => esc_url_raw($site_url), 'site_host' => strtolower((string)(wp_parse_url($site_url, PHP_URL_HOST) ?: '')), 'installed_at' => (string)get_option('affgsc_installed_at', ''), 'active_games' => affgsc_active_game_count(), 'plugin_version' => AFFGSC_VERSION, 'period' => function_exists('wp_date') ? wp_date('Y-m-d') : gmdate('Y-m-d'), 'stats' => $games, 'reports' => $reports, 'ratings' => $ratings, ]; update_option('affgsc_stats_outbox', $payload, false); affgsc_write_stats([]); } $res = affgsc_signed_remote_post($endpoint, 'stats', $payload, [ 'timeout' => 20, 'limit_response_size' => MB_IN_BYTES, ]); if (is_wp_error($res)) { return ['ok' => false, 'message' => $res->get_error_message()]; } $code = (int)wp_remote_retrieve_response_code($res); if ($code < 200 || $code >= 300) { return ['ok' => false, 'message' => 'HTTP ' . $code]; } delete_option('affgsc_stats_outbox'); return ['ok' => true, 'message' => 'Stats sent']; } function affgsc_stats_summary(int $days = 0, int $limit = 50): array { $stats = affgsc_local_stats(); $since = $days > 0 ? strtotime('-' . $days . ' days') : 0; $rows = []; foreach ($stats as $date => $games) { if (!is_array($games) || $date === 'updated_at') continue; if ($since > 0 && strtotime((string)$date) < $since) continue; foreach ($games as $source_id => $values) { if (!is_array($values)) continue; $key = (string)$source_id; if (!isset($rows[$key])) { $rows[$key] = ['game_id' => (int)$source_id, 'launches' => 0, 'real_play_clicks' => 0, 'reports' => 0]; } $rows[$key]['launches'] += (int)($values['launches'] ?? 0); $rows[$key]['real_play_clicks'] += (int)($values['real_play_clicks'] ?? 0); $rows[$key]['reports'] += (int)($values['reports'] ?? 0); } } $ratings = affgsc_rating_export_rows(); foreach ($ratings as $rating_row) { $key = (string)(int)($rating_row['game_id'] ?? 0); if ($key === '0') continue; if (!isset($rows[$key])) { $rows[$key] = ['game_id' => (int)$key, 'launches' => 0, 'real_play_clicks' => 0, 'reports' => 0]; } } usort($rows, static function (array $a, array $b): int { $score_a = (int)$a['launches'] + (int)$a['real_play_clicks']; $score_b = (int)$b['launches'] + (int)$b['real_play_clicks']; return $score_b <=> $score_a; }); if ($limit > 0) { $rows = array_slice($rows, 0, $limit); } foreach ($rows as &$row) { $post_id = function_exists('affgsc_find_post_by_source') ? affgsc_find_post_by_source((int)$row['game_id']) : 0; $row['post_id'] = $post_id; $row['title'] = $post_id > 0 ? get_the_title($post_id) : ('#' . $row['game_id']); $row['edit_url'] = $post_id > 0 ? get_edit_post_link($post_id, '') : ''; $rating = $post_id > 0 ? affgsc_rating_summary($post_id) : ['average' => 0, 'count' => 0]; $row['rating_average'] = (float)($rating['average'] ?? 0); $row['rating_count'] = (int)($rating['count'] ?? 0); } unset($row); return $rows; } function affgsc_reports_query(array $args = []): array { global $wpdb; $table = affgsc_reports_table(); $limit = max(1, min(100, (int)($args['limit'] ?? 50))); $offset = max(0, (int)($args['offset'] ?? 0)); $where = ['1=1']; $params = []; if (!empty($args['post_id'])) { $where[] = 'post_id = %d'; $params[] = (int)$args['post_id']; } if (!empty($args['provider_slug'])) { $where[] = 'provider_slug = %s'; $params[] = sanitize_title((string)$args['provider_slug']); } $sql = "SELECT * FROM {$table} WHERE " . implode(' AND ', $where) . " ORDER BY created_at DESC LIMIT %d OFFSET %d"; $params[] = $limit; $params[] = $offset; // The table and SQL fragments are plugin-owned; all variable values are prepared. // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.NotPrepared $rows = $wpdb->get_results($wpdb->prepare($sql, $params), ARRAY_A); return is_array($rows) ? $rows : []; } function affgsc_reports_count(array $args = []): int { global $wpdb; $table = affgsc_reports_table(); $where = ['1=1']; $params = []; if (!empty($args['post_id'])) { $where[] = 'post_id = %d'; $params[] = (int)$args['post_id']; } if (!empty($args['provider_slug'])) { $where[] = 'provider_slug = %s'; $params[] = sanitize_title((string)$args['provider_slug']); } $sql = "SELECT COUNT(*) FROM {$table} WHERE " . implode(' AND ', $where); // The table and SQL fragments are plugin-owned; optional variable values are prepared. // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.NotPrepared return $params ? (int)$wpdb->get_var($wpdb->prepare($sql, $params)) : (int)$wpdb->get_var($sql); } function affgsc_rating_export_rows(): array { global $wpdb; $table = affgsc_ratings_table(); // Aggregate export from the plugin-owned ratings table; no visitor input is interpolated. // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared $rows = $wpdb->get_results("SELECT source_id AS game_id, COUNT(*) AS rating_count, AVG(rating) AS rating_average FROM {$table} GROUP BY source_id", ARRAY_A); if (!is_array($rows)) return []; return array_map(static function (array $row): array { return [ 'game_id' => (int)($row['game_id'] ?? 0), 'rating_count' => (int)($row['rating_count'] ?? 0), 'rating_average' => round((float)($row['rating_average'] ?? 0), 2), ]; }, $rows); }