clicks map. The digest and the * dashboard both need traffic, and neither should spend a GSC quota * call when the other just fetched the same numbers. */ const TRAFFIC_TRANSIENT = 'abds_seo_priority_traffic'; /** * How long the traffic map stays cached. GSC data lags ~2 days anyway, * so a fresher cache would buy nothing but quota burn. */ const TRAFFIC_TTL = 12 * HOUR_IN_SECONDS; /** * Days of traffic counted per page. */ const TRAFFIC_WINDOW_DAYS = 28; /** * Max pages pulled from GSC for the traffic map. */ const TRAFFIC_ROW_LIMIT = 500; /** * Max scanned pages considered per run, newest scan first. A ceiling * keeps this bounded on a large site — the attention list only ever * shows the worst handful, so widening the candidate pool past this * changes the output only in pathological cases. */ const MAX_CANDIDATES = 500; /** * Decay source resolution. Null when the Pro add-on (which ships the * decay/rank modules) is not installed — the attention list then ranks on * the free scanner + content-score signals alone. * * @var ABDS_SEO_Decay_Analyzer|null */ protected ?ABDS_SEO_Decay_Analyzer $decay; /** * GSC connection state. Null without the Pro add-on. * * @var ABDS_SEO_GSC_Settings|null */ protected ?ABDS_SEO_GSC_Settings $gsc_settings; /** * GSC API client. Null without the Pro add-on. * * @var ABDS_SEO_GSC_Client|null */ protected ?ABDS_SEO_GSC_Client $gsc_client; /** * Constructor. The decay/GSC/rank signals come from the Pro add-on; when it * is absent those classes don't exist, so each is guarded and left null and * the ranking degrades to the free signals rather than fatally instantiating * a missing class. * * @param ABDS_SEO_Decay_Analyzer|null $decay Optional override. * @param ABDS_SEO_GSC_Settings|null $gsc_settings Optional override. * @param ABDS_SEO_GSC_Client|null $gsc_client Optional override. */ public function __construct( ?ABDS_SEO_Decay_Analyzer $decay = null, ?ABDS_SEO_GSC_Settings $gsc_settings = null, ?ABDS_SEO_GSC_Client $gsc_client = null ) { $this->decay = $decay ?? ( class_exists( 'ABDS_SEO_Decay_Analyzer' ) ? new ABDS_SEO_Decay_Analyzer() : null ); $this->gsc_settings = $gsc_settings ?? ( class_exists( 'ABDS_SEO_GSC_Settings' ) ? new ABDS_SEO_GSC_Settings() : null ); $this->gsc_client = $gsc_client ?? ( class_exists( 'ABDS_SEO_GSC_Client' ) ? new ABDS_SEO_GSC_Client() : null ); } /** * Action Scheduler hook that refreshes the traffic cache off-request. */ const REFRESH_HOOK = 'abds_seo_refresh_traffic_map'; /** * Registers the background traffic-refresh handler. * * @param ABDS_SEO_Loader $loader Hook loader. */ public function register_hooks( ABDS_SEO_Loader $loader ): void { $loader->add_action( self::REFRESH_HOOK, $this, 'refresh_traffic_map' ); } /** * Action Scheduler callback: warms the traffic cache. */ public function refresh_traffic_map(): void { self::flush_traffic_cache(); $this->traffic_map( true ); } /** * Returns the ranked attention list. * * @param int $limit Max rows; 0 = all. * @param bool $allow_fetch Whether a cache miss may call GSC inline. Screens * pass false so a page load never blocks on an HTTP * round trip; the background refresh passes true. * @return array{items:array, traffic_available:bool, window_days:int} */ public function get_attention_list( int $limit = 0, bool $allow_fetch = true ): array { $candidates = $this->fetch_candidates(); $traffic = $this->traffic_map( $allow_fetch ); // decay_map must honour allow_fetch too: with GSC connected, its // per-keyword analyze_entry() would otherwise make one synchronous // GSC call each, defeating the whole point of allow_fetch=false on a // page render. $decay = $this->decay_map( $allow_fetch ); // R3: when candidates came from a completed audit run, each row // carries audit_url_id instead of issues_json (see // fetch_candidates_from_audit_run()) — its issues are looked up // from one batched query below rather than per-row, the same // "gather once, look up in the loop" shape traffic/decay already use. // audit_url_id is genuinely optional (fetch_candidates_from_page_scores()'s // rows never carry it at all — confirmed real bug, neborak.com.ua: on // any site that hasn't completed an R3 Site Audit yet, EVERY row took // this path and wp_list_pluck() emitted an undefined-property warning // for each one). The null-coalesce below reads it the same way the // isset() check just below already does, instead of wp_list_pluck() // accessing the property directly. $audit_url_ids = array_filter( array_map( static function ( $row ) { return $row->audit_url_id ?? null; }, $candidates ) ); $issues_by_audit_url = array() !== $audit_url_ids ? $this->issues_by_audit_url_id( array_map( 'intval', $audit_url_ids ) ) : array(); $pages = array(); foreach ( $candidates as $row ) { $post_id = (int) $row->ID; $analysis = $decay[ $post_id ] ?? array(); $issues = isset( $row->audit_url_id ) ? ABDS_SEO_Priority_Scorer::issues_from_audit_rows( $issues_by_audit_url[ (int) $row->audit_url_id ] ?? array() ) : ABDS_SEO_Priority_Scorer::issues_from_blob( $row->issues_json ); $pages[] = array( 'post_id' => $post_id, 'title' => (string) $row->post_title, 'edit_link' => get_edit_post_link( $post_id, 'raw' ), 'permalink' => get_permalink( $post_id ), 'issues' => $issues, 'content_score' => null !== $row->content_score ? (int) round( (float) $row->content_score ) : null, 'decay_trend' => (string) ( $analysis['trend'] ?? '' ), 'decay_confidence' => (float) ( $analysis['confidence'] ?? 0 ), 'decay_source' => (string) ( $analysis['source'] ?? '' ), 'traffic' => $traffic[ $post_id ] ?? null, ); } $ranked = ABDS_SEO_Priority_Scorer::rank( $pages, $limit ); foreach ( $ranked as &$item ) { $item['reason_labels'] = array_values( array_filter( array_map( array( 'ABDS_SEO_Priority_Scorer', 'reason_label' ), $item['reasons'] ) ) ); } unset( $item ); return array( 'items' => $ranked, 'traffic_available' => null !== $traffic, 'window_days' => self::TRAFFIC_WINDOW_DAYS, ); } /** * Fetches ranking candidates — from the latest completed R3 audit run * when one exists (richer, normalized per-issue data), falling back to * the legacy page_scores-based query otherwise. The fallback matters: * a site that has only ever run the pre-R3 scanner (never a real * Site Audit) must not see "Pages that need attention" go empty the * day this ships — the coordinator's own explicit requirement (D3 * planning note, 28Aug26). * * @return array Raw rows — each either legacy-shaped * (issues_json) or audit-shaped (audit_url_id), * never both; get_attention_list() tells them * apart with isset($row->audit_url_id). */ protected function fetch_candidates(): array { $run_id = $this->latest_completed_audit_run_id(); return null !== $run_id ? $this->fetch_candidates_from_audit_run( $run_id ) : $this->fetch_candidates_from_page_scores(); } /** * The most recent completed wp_abds_seo_audit_runs row, or null if the * site has never finished one (never run a Step-2 Site Audit, or one is * still in progress — a running/failed run's own audit_urls rows aren't * a stable candidate set to rank on). * * @return ?int */ protected function latest_completed_audit_run_id(): ?int { global $wpdb; $runs_table = $wpdb->prefix . 'abds_seo_audit_runs'; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- $runs_table is built only from $wpdb->prefix plus a hardcoded literal, never user input. Admin-only read, not a hot path. $id = $wpdb->get_var( "SELECT id FROM {$runs_table} WHERE status = 'completed' ORDER BY id DESC LIMIT 1" ); return $id ? (int) $id : null; } /** * R3 candidate query: published pages of public post types that the * given audit run actually checked, content_score still sourced from * page_scores (LEFT JOIN — unchanged meaning, "average content * placement/readability score," which audit_urls.score doesn't * represent on its own since it blends every registered check * together) while issues come from the richer normalized audit data * (looked up separately, see get_attention_list()). * * @param int $run_id wp_abds_seo_audit_runs row ID. * @return array */ protected function fetch_candidates_from_audit_run( int $run_id ): array { global $wpdb; $post_types = array_values( get_post_types( array( 'public' => true ) ) ); $placeholders = implode( ', ', array_fill( 0, count( $post_types ), '%s' ) ); $posts_table = $wpdb->posts; $audit_urls_table = $wpdb->prefix . 'abds_seo_audit_urls'; $page_scores_table = $wpdb->prefix . 'abds_seo_page_scores'; // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- tables are $wpdb->posts / $wpdb->prefix plus hardcoded literals, never user input; $placeholders is a generated %s list whose values are all passed through $wpdb->prepare() (phpcs's static placeholder counter cannot see into a variable-built string). Admin-only read. $rows = $wpdb->get_results( $wpdb->prepare( "SELECT p.ID, p.post_title, ps.content_score, au.id AS audit_url_id FROM {$posts_table} p INNER JOIN {$audit_urls_table} au ON au.object_type = 'post' AND au.object_id = p.ID AND au.run_id = %d AND au.status = 'completed' LEFT JOIN {$page_scores_table} ps ON ps.post_id = p.ID WHERE p.post_status = %s AND p.post_type IN ({$placeholders}) LIMIT %d", array_merge( array( $run_id, 'publish' ), $post_types, array( self::MAX_CANDIDATES ) ) ) ); // phpcs:enable if ( $rows ) { _prime_post_caches( wp_list_pluck( $rows, 'ID' ), false, false ); } return $rows; } /** * Legacy candidate query (pre-R3): scanned, published pages of public * post types, one query — looping the scanner per post would be an N+1 * (see ABDS_SEO_Reports for the same reasoning). Only pages with a * page_scores row are candidates: a never-scanned page has no signal * to rank on. * * @return array Raw rows. */ protected function fetch_candidates_from_page_scores(): array { global $wpdb; $post_types = array_values( get_post_types( array( 'public' => true ) ) ); $placeholders = implode( ', ', array_fill( 0, count( $post_types ), '%s' ) ); $scores_table = $wpdb->prefix . 'abds_seo_page_scores'; $posts_table = $wpdb->posts; // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $posts_table/$scores_table are $wpdb->posts / $wpdb->prefix plus a hardcoded literal, never user input; $placeholders is a generated %s list whose values are all passed through $wpdb->prepare() (phpcs's static placeholder counter cannot see into a variable-built string). Result is cached by the caller via the traffic/decay transients; this is an admin-only read. $rows = $wpdb->get_results( $wpdb->prepare( "SELECT p.ID, p.post_title, ps.content_score, ps.issues_json FROM {$posts_table} p INNER JOIN {$scores_table} ps ON ps.post_id = p.ID WHERE p.post_status = %s AND p.post_type IN ({$placeholders}) ORDER BY ps.last_scanned DESC LIMIT %d", array_merge( array( 'publish' ), $post_types, array( self::MAX_CANDIDATES ) ) ) ); // phpcs:enable // Prime the post cache in one query: get_permalink()/ // get_edit_post_link() below would otherwise fetch each post // individually and turn this back into the N+1 the JOIN avoids. if ( $rows ) { _prime_post_caches( wp_list_pluck( $rows, 'ID' ), false, false ); } return $rows; } /** * Batched wp_abds_seo_audit_issues lookup for a set of audit_url_ids — * one query for the whole candidate list, not one per page (the same * "gather once" shape traffic_map()/decay_map() already use). * * @param int[] $audit_url_ids wp_abds_seo_audit_urls row IDs to fetch issues for. * @return array> audit_url_id => its raw issue rows. */ protected function issues_by_audit_url_id( array $audit_url_ids ): array { if ( array() === $audit_url_ids ) { return array(); } global $wpdb; $issues_table = $wpdb->prefix . 'abds_seo_audit_issues'; $placeholders = implode( ', ', array_fill( 0, count( $audit_url_ids ), '%d' ) ); $sql = "SELECT audit_url_id, issue_key, severity, message FROM {$issues_table} WHERE audit_url_id IN ({$placeholders})"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- $issues_table is built only from $wpdb->prefix plus a hardcoded literal, never user input; $sql is a fixed literal built just above (no runtime input in it), $placeholders is a generated %d list whose values are all passed through $wpdb->prepare(). Admin-only read. $rows = $wpdb->get_results( $wpdb->prepare( $sql, $audit_url_ids ) ); $grouped = array(); foreach ( $rows as $row ) { $grouped[ (int) $row->audit_url_id ][] = $row; } return $grouped; } /** * Builds post_id => clicks for the traffic window, or null when GSC * isn't connected (or errors) — null means "unknown", which the scorer * treats as no traffic weighting at all, rather than pretending every * page has zero traffic. * * @param bool $allow_fetch Whether a cache miss may hit the GSC API now. * @return ?array Clicks per post ID, or null if unavailable. */ public function traffic_map( bool $allow_fetch = true ): ?array { // No Pro add-on -> no decay/GSC classes -> traffic is simply "unknown". if ( null === $this->decay || null === $this->gsc_client || ! $this->decay->gsc_available() ) { return null; } $cached = get_transient( self::TRAFFIC_TRANSIENT ); if ( is_array( $cached ) ) { return $cached; } if ( ! $allow_fetch ) { // Nothing cached and we mustn't block this request on an HTTP // call: queue the fetch and rank without traffic this once. $this->schedule_refresh(); return null; } $rows = $this->gsc_client->query( gmdate( 'Y-m-d', time() - self::TRAFFIC_WINDOW_DAYS * DAY_IN_SECONDS ), gmdate( 'Y-m-d', time() - DAY_IN_SECONDS ), array( 'page' ), self::TRAFFIC_ROW_LIMIT ); if ( is_wp_error( $rows ) ) { return null; } $map = array(); foreach ( $rows as $row ) { $url = (string) ( $row['keys'][0] ?? '' ); if ( '' === $url ) { continue; } $post_id = url_to_postid( $url ); if ( $post_id > 0 ) { $map[ $post_id ] = ( $map[ $post_id ] ?? 0 ) + (int) $row['clicks']; } } set_transient( self::TRAFFIC_TRANSIENT, $map, self::TRAFFIC_TTL ); return $map; } /** * Builds post_id => decay analysis from the tracked keywords, keeping * the worst (declining, highest confidence) entry per post — one page * can track several keywords, and the page's priority should reflect * its most alarming one. * * @param bool $allow_gsc Whether decay may fetch its GSC-clicks source. * False on a page render, so this doesn't make one * synchronous GSC call per tracked keyword. * @return array Decay analysis per post ID. */ protected function decay_map( bool $allow_gsc = true ): array { // No Pro add-on -> no decay analyzer / rank-tracker settings -> no decay // signal; the attention list ranks on scanner issues + content score. if ( null === $this->decay || ! class_exists( 'ABDS_SEO_Rank_Tracker_Settings' ) ) { return array(); } $config = ( new ABDS_SEO_Rank_Tracker_Settings() )->get(); $map = array(); foreach ( $config['keywords'] as $entry ) { $post_id = isset( $entry['post_id'] ) && $entry['post_id'] ? (int) $entry['post_id'] : 0; if ( $post_id <= 0 ) { continue; } $analysis = $this->decay->analyze_entry( $entry, $allow_gsc ); if ( ! isset( $map[ $post_id ] ) || $this->is_worse( $analysis, $map[ $post_id ] ) ) { $map[ $post_id ] = $analysis; } } return $map; } /** * Whether one decay analysis is more alarming than another. * * @param array $candidate New analysis. * @param array $current Analysis already kept for the post. * @return bool */ protected function is_worse( array $candidate, array $current ): bool { $candidate_declining = 'declining' === ( $candidate['trend'] ?? '' ); $current_declining = 'declining' === ( $current['trend'] ?? '' ); if ( $candidate_declining !== $current_declining ) { return $candidate_declining; } return (float) ( $candidate['confidence'] ?? 0 ) > (float) ( $current['confidence'] ?? 0 ); } /** * Queues a one-off background traffic refresh, unless one is already * pending — several admin page loads in a row must not pile up * duplicate GSC calls. */ protected function schedule_refresh(): void { if ( ! function_exists( 'as_schedule_single_action' ) || as_has_scheduled_action( self::REFRESH_HOOK ) ) { return; } as_schedule_single_action( time() + MINUTE_IN_SECONDS, self::REFRESH_HOOK, array(), 'abds-seo' ); } /** * Drops the cached traffic map, so the next read re-queries GSC. */ public static function flush_traffic_cache(): void { delete_transient( self::TRAFFIC_TRANSIENT ); } }