'GET', 'callback' => array( $this, 'board' ), 'permission_callback' => static fn(): bool => current_user_can( 'manage_options' ), 'args' => array( 'post_type' => array( 'type' => 'string', 'default' => '' ), 'lang' => array( 'type' => 'string', 'default' => '' ), ), ) ); register_rest_route( self::NAMESPACE, '/site-analyzer/weakest', array( 'methods' => 'GET', 'callback' => array( $this, 'weakest' ), 'permission_callback' => static fn(): bool => current_user_can( 'manage_options' ), 'args' => array( 'post_type' => array( 'type' => 'string', 'default' => '' ), 'lang' => array( 'type' => 'string', 'default' => '' ), 'metric' => array( 'type' => 'string', 'default' => 'geo', 'enum' => array( 'geo', 'seo' ) ), 'page' => array( 'type' => 'integer', 'default' => 1, 'minimum' => 1 ), 'per_page' => array( 'type' => 'integer', 'default' => 25, 'enum' => array( 25, 50, 100 ) ), ), ) ); } public function board( WP_REST_Request $request ): WP_REST_Response { global $wpdb; $type = sanitize_key( (string) $request['post_type'] ); $lang = sanitize_key( (string) $request['lang'] ); // Two-minute cache, except while a bulk analysis is running: then the // numbers are changing by the second and the whole point of watching // is to see them move. $job = get_option( 'airanksuite_bulk_job' ); $live = is_array( $job ) && 'running' === ( $job['state'] ?? '' ); // The generation counter is bumped when a bulk run finishes, so a // finished analysis is visible at once instead of after the TTL. $key = self::CACHE . '_' . md5( $type . '|' . $lang . '|' . (int) get_option( 'airanksuite_analyze_gen', 0 ) ); $cached = $live ? false : get_transient( $key ); if ( is_array( $cached ) ) { return new WP_REST_Response( $cached ); } $types = \AiRankSuite\Support\Inventory::eligible_types(); if ( '' !== $type && in_array( $type, $types, true ) ) { $types = array( $type ); } $in = implode( ',', array_fill( 0, count( $types ), '%s' ) ); $scope = Multilingual::sql_scope_args( $lang ); // Base: published posts in scope, joined to the two score rows. Every // statement below spells that base out in full — nothing is assembled // into the SQL but the generated %s list — and the language scope is // the pair of flagged lookups described at Multilingual::sql_scope_args(). $params = array_merge( $types, $scope['args'] ); // 1. Totals and averages. // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared $tot = $wpdb->get_row( $wpdb->prepare( "SELECT COUNT(*) AS total, SUM(g.meta_id IS NOT NULL) AS analyzed, AVG(CAST(g.meta_value AS DECIMAL(5,1))) AS avg_geo, AVG(CAST(s.meta_value AS DECIMAL(5,1))) AS avg_seo, SUM(CAST(g.meta_value AS DECIMAL(5,1)) < 40) AS weak_geo, SUM(CAST(s.meta_value AS DECIMAL(5,1)) < 40) AS weak_seo, SUM(CAST(g.meta_value AS DECIMAL(5,1)) >= 80) AS strong_geo, SUM(CAST(s.meta_value AS DECIMAL(5,1)) >= 80) AS strong_seo FROM {$wpdb->posts} p LEFT JOIN {$wpdb->postmeta} g ON g.post_id = p.ID AND g.meta_key = '_airanksuite_geo_score' LEFT JOIN {$wpdb->postmeta} s ON s.post_id = p.ID AND s.meta_key = '_airanksuite_seo_score' WHERE p.post_status = 'publish' AND p.post_type IN ({$in}) AND ( %d = 0 OR EXISTS ( SELECT 1 FROM %i airanksuite_x WHERE airanksuite_x.%i = p.ID AND airanksuite_x.%i = %s AND airanksuite_x.%i LIKE %s ) ) AND ( %d = 0 OR EXISTS ( SELECT 1 FROM {$wpdb->term_relationships} airanksuite_lr INNER JOIN {$wpdb->term_taxonomy} airanksuite_lt ON airanksuite_lt.term_taxonomy_id = airanksuite_lr.term_taxonomy_id AND airanksuite_lt.taxonomy = 'language' INNER JOIN {$wpdb->terms} airanksuite_lg ON airanksuite_lg.term_id = airanksuite_lt.term_id WHERE airanksuite_lr.object_id = p.ID AND airanksuite_lg.slug = %s ) )", $params ) ); // 2. Distribution, both metrics. // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared $bands = $wpdb->get_results( $wpdb->prepare( "SELECT LEAST(4, FLOOR(CAST(g.meta_value AS DECIMAL(5,1)) / 20)) AS gb, LEAST(4, FLOOR(CAST(s.meta_value AS DECIMAL(5,1)) / 20)) AS sb, COUNT(*) AS n FROM {$wpdb->posts} p LEFT JOIN {$wpdb->postmeta} g ON g.post_id = p.ID AND g.meta_key = '_airanksuite_geo_score' LEFT JOIN {$wpdb->postmeta} s ON s.post_id = p.ID AND s.meta_key = '_airanksuite_seo_score' WHERE p.post_status = 'publish' AND p.post_type IN ({$in}) AND ( %d = 0 OR EXISTS ( SELECT 1 FROM %i airanksuite_x WHERE airanksuite_x.%i = p.ID AND airanksuite_x.%i = %s AND airanksuite_x.%i LIKE %s ) ) AND ( %d = 0 OR EXISTS ( SELECT 1 FROM {$wpdb->term_relationships} airanksuite_lr INNER JOIN {$wpdb->term_taxonomy} airanksuite_lt ON airanksuite_lt.term_taxonomy_id = airanksuite_lr.term_taxonomy_id AND airanksuite_lt.taxonomy = 'language' INNER JOIN {$wpdb->terms} airanksuite_lg ON airanksuite_lg.term_id = airanksuite_lt.term_id WHERE airanksuite_lr.object_id = p.ID AND airanksuite_lg.slug = %s ) ) AND g.meta_id IS NOT NULL GROUP BY gb, sb", $params ) ); $dist_geo = array_fill( 0, 5, 0 ); $dist_seo = array_fill( 0, 5, 0 ); foreach ( (array) $bands as $b ) { $dist_geo[ max( 0, min( 4, (int) $b->gb ) ) ] += (int) $b->n; if ( null !== $b->sb ) { $dist_seo[ max( 0, min( 4, (int) $b->sb ) ) ] += (int) $b->n; } } // 3. Per post type. // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared $by_type = $wpdb->get_results( $wpdb->prepare( "SELECT p.post_type AS type, COUNT(*) AS total, SUM(g.meta_id IS NOT NULL) AS analyzed, AVG(CAST(g.meta_value AS DECIMAL(5,1))) AS avg_geo, AVG(CAST(s.meta_value AS DECIMAL(5,1))) AS avg_seo, SUM(CAST(g.meta_value AS DECIMAL(5,1)) < 40) AS weak FROM {$wpdb->posts} p LEFT JOIN {$wpdb->postmeta} g ON g.post_id = p.ID AND g.meta_key = '_airanksuite_geo_score' LEFT JOIN {$wpdb->postmeta} s ON s.post_id = p.ID AND s.meta_key = '_airanksuite_seo_score' WHERE p.post_status = 'publish' AND p.post_type IN ({$in}) AND ( %d = 0 OR EXISTS ( SELECT 1 FROM %i airanksuite_x WHERE airanksuite_x.%i = p.ID AND airanksuite_x.%i = %s AND airanksuite_x.%i LIKE %s ) ) AND ( %d = 0 OR EXISTS ( SELECT 1 FROM {$wpdb->term_relationships} airanksuite_lr INNER JOIN {$wpdb->term_taxonomy} airanksuite_lt ON airanksuite_lt.term_taxonomy_id = airanksuite_lr.term_taxonomy_id AND airanksuite_lt.taxonomy = 'language' INNER JOIN {$wpdb->terms} airanksuite_lg ON airanksuite_lg.term_id = airanksuite_lt.term_id WHERE airanksuite_lr.object_id = p.ID AND airanksuite_lg.slug = %s ) ) GROUP BY p.post_type ORDER BY total DESC", $params ), ARRAY_A ); $type_labels = array(); foreach ( get_post_types( array( 'public' => true ), 'objects' ) as $obj ) { if ( ! in_array( $obj->name, \AiRankSuite\Support\Inventory::eligible_types(), true ) ) { continue; } $type_labels[ $obj->name ] = \AiRankSuite\Support\Inventory::type_label( (string) $obj->name ); } // 4. Per language, when a multilingual plugin is active. One query per // language is fine — sites have a handful. $by_lang = array(); foreach ( Multilingual::languages() as $code => $language ) { $ls = Multilingual::sql_scope_args( (string) $code ); if ( ! $ls['active'] ) { continue; } // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared $row = $wpdb->get_row( $wpdb->prepare( "SELECT COUNT(*) AS total, SUM(g.meta_id IS NOT NULL) AS analyzed, AVG(CAST(g.meta_value AS DECIMAL(5,1))) AS avg_geo, AVG(CAST(s.meta_value AS DECIMAL(5,1))) AS avg_seo, SUM(CAST(g.meta_value AS DECIMAL(5,1)) < 40) AS weak FROM {$wpdb->posts} p LEFT JOIN {$wpdb->postmeta} g ON g.post_id = p.ID AND g.meta_key = '_airanksuite_geo_score' LEFT JOIN {$wpdb->postmeta} s ON s.post_id = p.ID AND s.meta_key = '_airanksuite_seo_score' WHERE p.post_status = 'publish' AND p.post_type IN ({$in}) AND ( %d = 0 OR EXISTS ( SELECT 1 FROM %i airanksuite_x WHERE airanksuite_x.%i = p.ID AND airanksuite_x.%i = %s AND airanksuite_x.%i LIKE %s ) ) AND ( %d = 0 OR EXISTS ( SELECT 1 FROM {$wpdb->term_relationships} airanksuite_lr INNER JOIN {$wpdb->term_taxonomy} airanksuite_lt ON airanksuite_lt.term_taxonomy_id = airanksuite_lr.term_taxonomy_id AND airanksuite_lt.taxonomy = 'language' INNER JOIN {$wpdb->terms} airanksuite_lg ON airanksuite_lg.term_id = airanksuite_lt.term_id WHERE airanksuite_lr.object_id = p.ID AND airanksuite_lg.slug = %s ) )", array_merge( $types, $ls['args'] ) ), ARRAY_A ); if ( $row && (int) $row['total'] > 0 ) { $by_lang[] = array( 'code' => (string) $code, 'label' => (string) ( $language['label'] ?? $code ), 'rtl' => in_array( substr( (string) $code, 0, 2 ), array( 'ar', 'he', 'fa', 'ur', 'ps', 'sd', 'ug', 'yi' ), true ), 'total' => (int) $row['total'], 'analyzed' => (int) $row['analyzed'], 'avg_geo' => (int) round( (float) $row['avg_geo'] ), 'avg_seo' => (int) round( (float) $row['avg_seo'] ), 'weak' => (int) $row['weak'], ); } } // 5. Site-wide top issues: the seven area scores are stored one row // each (Dimensions::save), so "which area is coldest across the // site" is one GROUP BY, not a scan of every analysis blob. $areas = array(); try { $areas = \AiRankSuite\Modules\GeoScore\Services\Dimensions::board(); } catch ( \Throwable $e ) { $areas = array(); } // 6. History: the site AI score over time already exists; the SEO // average is sampled here so a trend can start from this release. $history = get_option( 'airanksuite_geo_score_history' ); $series = array(); foreach ( array_slice( is_array( $history ) ? $history : array(), -30 ) as $point ) { $series[] = array( 'time' => (int) ( $point['time'] ?? $point['calculated_at'] ?? 0 ), 'value' => (int) ( $point['value'] ?? 0 ), ); } $this->sample( (int) round( (float) ( $tot->avg_geo ?? 0 ) ), (int) round( (float) ( $tot->avg_seo ?? 0 ) ), (int) ( $tot->analyzed ?? 0 ), (int) ( $tot->total ?? 0 ), (int) ( $tot->weak_geo ?? 0 ), (int) ( $tot->weak_seo ?? 0 ) ); $samples = get_option( 'airanksuite_site_analyzer_samples', array() ); // 7. What a run would cost in time: the analyzer is local and fast; the // slice size and a measured per-post cost give a real estimate. $per_post_ms = (int) get_option( 'airanksuite_analyze_ms_per_post', 120 ); $unscored = max( 0, (int) ( $tot->total ?? 0 ) - (int) ( $tot->analyzed ?? 0 ) ); $payload = array( 'scope' => array( 'post_type' => $type, 'lang' => $lang ), 'total' => (int) ( $tot->total ?? 0 ), 'analyzed' => (int) ( $tot->analyzed ?? 0 ), 'unscored' => $unscored, 'avg_geo' => (int) round( (float) ( $tot->avg_geo ?? 0 ) ), 'avg_seo' => (int) round( (float) ( $tot->avg_seo ?? 0 ) ), 'weak' => array( 'geo' => (int) ( $tot->weak_geo ?? 0 ), 'seo' => (int) ( $tot->weak_seo ?? 0 ) ), 'strong' => array( 'geo' => (int) ( $tot->strong_geo ?? 0 ), 'seo' => (int) ( $tot->strong_seo ?? 0 ) ), 'dist' => array( 'geo' => $dist_geo, 'seo' => $dist_seo ), 'by_type' => array_map( static fn( $r ) => array( 'type' => (string) $r['type'], 'label' => (string) ( $type_labels[ $r['type'] ] ?? $r['type'] ), 'total' => (int) $r['total'], 'analyzed' => (int) $r['analyzed'], 'avg_geo' => (int) round( (float) $r['avg_geo'] ), 'avg_seo' => (int) round( (float) $r['avg_seo'] ), 'weak' => (int) $r['weak'], ), (array) $by_type ), 'by_lang' => $by_lang, 'areas' => $areas, 'history' => $series, 'samples' => is_array( $samples ) ? array_slice( $samples, -60 ) : array(), // Coverage does not have to wait for the daily sampler: every // analysis stamps the day it ran, so the curve of "how much of the // site had been scored by then" is already in the database. On a // site that has been running for weeks this is real history on the // first page load rather than in a fortnight. // Only worth reconstructing while the daily sampler has nothing to // draw. Once it holds two points it is the cheaper and more precise // source, and this scan — twenty thousand meta rows, ~16 MB — is // skipped entirely. 'coverage_history' => count( (array) $samples ) < 2 ? $this->coverage_history( (int) ( $tot->total ?? 0 ) ) : array(), 'estimate' => array( 'unscored' => $unscored, 'seconds' => (int) ceil( $unscored * $per_post_ms / 1000 ), 'per_post_ms' => $per_post_ms, ), 'types' => array_map( static fn( $t ) => array( 'id' => $t, 'label' => (string) ( $type_labels[ $t ] ?? $t ) ), \AiRankSuite\Support\Inventory::eligible_types() ), 'languages' => array_values( array_map( static fn( $l ) => array( 'code' => (string) $l['code'], 'label' => (string) ( $l['label'] ?? $l['code'] ) ), Multilingual::languages() ) ), ); if ( ! $live ) { set_transient( $key, $payload, 2 * MINUTE_IN_SECONDS ); } return new WP_REST_Response( $payload ); } /** * The weakest pages, paged. Only the two score rows are read; the title * comes from the posts table in the same query. */ public function weakest( WP_REST_Request $request ): WP_REST_Response { global $wpdb; $type = sanitize_key( (string) $request['post_type'] ); $lang = sanitize_key( (string) $request['lang'] ); $metric = 'seo' === $request['metric'] ? '_airanksuite_seo_score' : '_airanksuite_geo_score'; $page = max( 1, (int) $request['page'] ); $per = in_array( (int) $request['per_page'], array( 25, 50, 100 ), true ) ? (int) $request['per_page'] : 25; $types = \AiRankSuite\Support\Inventory::eligible_types(); if ( '' !== $type && in_array( $type, $types, true ) ) { $types = array( $type ); } $in = implode( ',', array_fill( 0, count( $types ), '%s' ) ); $scope = Multilingual::sql_scope_args( $lang ); // Both statements are written out in full; only the generated %s list // is interpolated. The language scope is the pair of flagged lookups // described at Multilingual::sql_scope_args(). $params = array_merge( array( $metric ), $types, $scope['args'] ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared $total = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$wpdb->posts} p INNER JOIN {$wpdb->postmeta} m ON m.post_id = p.ID AND m.meta_key = %s LEFT JOIN {$wpdb->postmeta} g ON g.post_id = p.ID AND g.meta_key = '_airanksuite_geo_score' LEFT JOIN {$wpdb->postmeta} s ON s.post_id = p.ID AND s.meta_key = '_airanksuite_seo_score' WHERE p.post_status = 'publish' AND p.post_type IN ({$in}) AND ( %d = 0 OR EXISTS ( SELECT 1 FROM %i airanksuite_x WHERE airanksuite_x.%i = p.ID AND airanksuite_x.%i = %s AND airanksuite_x.%i LIKE %s ) ) AND ( %d = 0 OR EXISTS ( SELECT 1 FROM {$wpdb->term_relationships} airanksuite_lr INNER JOIN {$wpdb->term_taxonomy} airanksuite_lt ON airanksuite_lt.term_taxonomy_id = airanksuite_lr.term_taxonomy_id AND airanksuite_lt.taxonomy = 'language' INNER JOIN {$wpdb->terms} airanksuite_lg ON airanksuite_lg.term_id = airanksuite_lt.term_id WHERE airanksuite_lr.object_id = p.ID AND airanksuite_lg.slug = %s ) )", $params ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared $rows = $wpdb->get_results( $wpdb->prepare( "SELECT p.ID, p.post_title, p.post_type, CAST(g.meta_value AS DECIMAL(5,1)) AS geo, CAST(s.meta_value AS DECIMAL(5,1)) AS seo FROM {$wpdb->posts} p INNER JOIN {$wpdb->postmeta} m ON m.post_id = p.ID AND m.meta_key = %s LEFT JOIN {$wpdb->postmeta} g ON g.post_id = p.ID AND g.meta_key = '_airanksuite_geo_score' LEFT JOIN {$wpdb->postmeta} s ON s.post_id = p.ID AND s.meta_key = '_airanksuite_seo_score' WHERE p.post_status = 'publish' AND p.post_type IN ({$in}) AND ( %d = 0 OR EXISTS ( SELECT 1 FROM %i airanksuite_x WHERE airanksuite_x.%i = p.ID AND airanksuite_x.%i = %s AND airanksuite_x.%i LIKE %s ) ) AND ( %d = 0 OR EXISTS ( SELECT 1 FROM {$wpdb->term_relationships} airanksuite_lr INNER JOIN {$wpdb->term_taxonomy} airanksuite_lt ON airanksuite_lt.term_taxonomy_id = airanksuite_lr.term_taxonomy_id AND airanksuite_lt.taxonomy = 'language' INNER JOIN {$wpdb->terms} airanksuite_lg ON airanksuite_lg.term_id = airanksuite_lt.term_id WHERE airanksuite_lr.object_id = p.ID AND airanksuite_lg.slug = %s ) ) ORDER BY CAST(m.meta_value AS DECIMAL(5,1)) ASC, p.ID ASC LIMIT %d OFFSET %d", array_merge( $params, array( $per, ( $page - 1 ) * $per ) ) ), ARRAY_A ); return new WP_REST_Response( array( 'items' => array_map( static fn( $r ) => array( 'id' => (int) $r['ID'], 'title' => (string) $r['post_title'], 'type' => (string) $r['post_type'], 'geo' => (int) round( (float) $r['geo'] ), 'seo' => (int) round( (float) $r['seo'] ), 'edit' => (string) get_edit_post_link( (int) $r['ID'], 'raw' ), ), (array) $rows ), 'pagination' => array( 'page' => $page, 'per_page' => $per, 'total' => $total, 'pages' => (int) ceil( $total / $per ), ), ) ); } /** * Keep one sample per day of the site averages, so a trend exists. * * Bounded at 120 days; written at most once a day; free. */ /** * Pages scored per day, cumulative, reconstructed from stored analyses. * * The timestamp lives inside the JSON blob rather than its own column, so * this reads the meta rows and groups them in PHP. That is affordable * because the whole payload is cached behind the analyzer transient and * invalidated by the analysis generation counter, and because the row set * is capped: beyond the cap the oldest days are simply not drawn, which is * better than a slow dashboard. * * @param int $total Published pages in scope, for the percentage. * @return array */ private function coverage_history( int $total ): array { global $wpdb; // Read in pages and discard each batch as it is folded in: holding // twenty thousand JSON blobs at once costs more memory than the whole // rest of the request. $per_day = array(); $batch = 2000; $cap = 20000; for ( $offset = 0; $offset < $cap; $offset += $batch ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared $rows = $wpdb->get_col( $wpdb->prepare( "SELECT meta_value FROM {$wpdb->postmeta} WHERE meta_key = %s LIMIT %d OFFSET %d", '_airanksuite_analysis', $batch, $offset ) ); if ( empty( $rows ) ) { break; } foreach ( $rows as $raw ) { if ( ! is_string( $raw ) || false === strpos( $raw, 'analyzed_at' ) ) { continue; } $decoded = json_decode( $raw, true ); $at = is_array( $decoded ) ? (int) ( $decoded['analyzed_at'] ?? 0 ) : 0; if ( $at <= 0 ) { continue; } $day = gmdate( 'Y-m-d', $at ); $per_day[ $day ] = ( $per_day[ $day ] ?? 0 ) + 1; } unset( $rows ); } if ( count( $per_day ) < 2 ) { // One day of analyses is not a curve; the caller falls back to the // daily samples rather than drawing a single-point line. return array(); } ksort( $per_day ); $out = array(); $cumulative = 0; foreach ( array_slice( $per_day, -60, null, true ) as $day => $count ) { $cumulative += (int) $count; $out[] = array( 'day' => (string) $day, 'scored' => $cumulative, 'coverage' => $total > 0 ? (int) round( 100 * min( $cumulative, $total ) / $total ) : 0, ); } return $out; } private function sample( int $geo, int $seo, int $analyzed, int $total = 0, int $weak_geo = 0, int $weak_seo = 0 ): void { if ( 0 === $analyzed ) { return; } $samples = get_option( 'airanksuite_site_analyzer_samples', array() ); $samples = is_array( $samples ) ? $samples : array(); $today = gmdate( 'Y-m-d' ); $point = array( 'day' => $today, 'geo' => $geo, 'seo' => $seo, 'analyzed' => $analyzed, 'total' => $total, 'weak_geo' => $weak_geo, 'weak_seo' => $weak_seo, ); $last = end( $samples ); // One point per day, holding the day's latest reading — so a run made // this afternoon shows on today's point rather than tomorrow's. if ( is_array( $last ) && ( $last['day'] ?? '' ) === $today ) { $samples[ count( $samples ) - 1 ] = $point; } else { $samples[] = $point; } update_option( 'airanksuite_site_analyzer_samples', array_slice( $samples, -120 ), false ); } }