invalidate(); } } /** * Read cached coverage with bounded cold queries. * * @return array */ public function get(): array { $cached = get_transient( 'altlumen_stats_cache' ); if ( is_array( $cached ) && isset( $cached['library'], $cached['content'], $cached['languages'] ) ) { return $cached; } global $wpdb; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Cold aggregate is cached for 12 hours; one grouped metadata pass avoids repeated joins and duplicate counts across 50,000 attachments. $row = $wpdb->get_row( $wpdb->prepare( "SELECT COUNT(*) total, COALESCE(SUM(m.with_alt),0) with_alt, COALESCE(SUM(m.decorative),0) decorative, COALESCE(SUM(m.waiting),0) waiting, COALESCE(SUM(CASE WHEN COALESCE(m.with_alt,0)=0 AND COALESCE(m.excluded,0)=0 THEN 1 ELSE 0 END),0) missing FROM {$wpdb->posts} p LEFT JOIN (SELECT post_id, MAX(CASE WHEN meta_key=%s AND TRIM(meta_value)<>%s THEN 1 ELSE 0 END) with_alt, MAX(CASE WHEN meta_key=%s AND meta_value=%s THEN 1 ELSE 0 END) decorative, MAX(CASE WHEN meta_key=%s AND meta_value=%s THEN 1 ELSE 0 END) waiting, MAX(CASE WHEN meta_key=%s AND meta_value IN (%s,%s,%s) THEN 1 ELSE 0 END) excluded FROM {$wpdb->postmeta} WHERE meta_key IN (%s,%s) GROUP BY post_id) m ON m.post_id=p.ID WHERE p.post_type=%s AND p.post_status=%s AND p.post_mime_type LIKE %s", '_wp_attachment_image_alt', '', '_altlumen_status', 'decorative', '_altlumen_status', 'suggested', '_altlumen_status', 'manual', 'skipped', 'decorative', '_wp_attachment_image_alt', '_altlumen_status', 'attachment', 'inherit', 'image/%' ), ARRAY_A ); $library = array_map( 'intval', $row ? $row : array( 'total' => 0, 'with_alt' => 0, 'decorative' => 0, 'waiting' => 0, 'missing' => 0, ) ); $library['percent'] = $library['total'] ? round( 100 * $library['with_alt'] / $library['total'], 1 ) : 0; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Same 12-hour cache; scalar language metadata replaces serialized LIKE scans. $languages = $wpdb->get_results( $wpdb->prepare( "SELECT m.meta_value language,COUNT(DISTINCT m.post_id) images FROM {$wpdb->postmeta} m INNER JOIN {$wpdb->posts} p ON p.ID=m.post_id WHERE m.meta_key=%s AND p.post_type=%s GROUP BY m.meta_value ORDER BY images DESC LIMIT %d", '_altlumen_lang', 'attachment', 100 ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Scalar scan counts keep cold content coverage bounded and cacheable. $content = $wpdb->get_row( $wpdb->prepare( "SELECT COALESCE(SUM(CASE WHEN meta_key=%s THEN CAST(meta_value AS UNSIGNED) ELSE 0 END),0) images,COALESCE(SUM(CASE WHEN meta_key=%s THEN CAST(meta_value AS UNSIGNED) ELSE 0 END),0) missing FROM {$wpdb->postmeta} WHERE meta_key IN (%s,%s)", '_altlumen_content_image_count', '_altlumen_content_missing_count', '_altlumen_content_image_count', '_altlumen_content_missing_count' ), ARRAY_A ); $content = array_map( 'intval', $content ? $content : array( 'images' => 0, 'missing' => 0, ) ); $content['percent'] = $content['images'] ? round( 100 * max( 0, $content['images'] - $content['missing'] ) / $content['images'], 1 ) : 0; $scan = (array) get_option( 'altlumen_scan_job', array() ); $content['last_scan'] = $scan['finished_at'] ?? ''; $content['scan_status'] = $scan['status'] ?? 'idle'; $value = array( 'library' => $library, 'content' => $content, 'waiting' => $library['waiting'], 'languages' => $languages, 'generated_at' => gmdate( 'c' ), ); set_transient( 'altlumen_stats_cache', $value, 12 * HOUR_IN_SECONDS ); return $value; } }