get_var( "SELECT COUNT(*) FROM {$wpdb->prefix}trustbio_authors WHERE is_active = 1" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
$tbd_categories = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->prefix}trustbio_author_categories WHERE is_enabled = 1" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
$tbd_boxes = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->prefix}trustbio_author_boxes WHERE box_type = 'default'" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
$tbd_rev_bars = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->prefix}trustbio_review_bars" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
// ── Content health ─────────────────────────────────────────────────────────
$tbd_enabled_types = trustbio_get_enabled_post_types();
/*
* One %s placeholder per post type, and the types passed as arguments.
*
* esc_sql() on each value did prevent injection, but an escaped string
* pasted into SQL is not a prepared statement: the escaping and the query
* are separated by whatever edits either of them later. Building the
* placeholder list from the array and letting prepare() bind the values
* keeps them together.
*/
$tbd_total_posts = 0;
/*
* Guarded, because an empty list is reachable: a site that unticks every
* post type gets array() back. The old concatenated version degraded to
* IN ('') and quietly returned nought; a placeholder list would degrade to
* IN () instead, which is a syntax error. Not running the query at all is
* the honest version of "no post types, no posts".
*/
if ( ! empty( $tbd_enabled_types ) ) {
$tbd_type_slots = implode( ', ', array_fill( 0, count( $tbd_enabled_types ), '%s' ) );
/*
* phpcs cannot see a placeholder it did not read in the string literal,
* so a list of them built at runtime reads to it as an unprepared
* query. The list contains nothing but placeholders; every value is
* bound by prepare(). This is the limitation behind core ticket 54042,
* linked from the review notes.
*/
// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
$tbd_total_posts = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_status = 'publish' AND post_type IN ({$tbd_type_slots})", $tbd_enabled_types ) );
}
// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
$tbd_reviewed_posts = (int) $wpdb->get_var(
$wpdb->prepare(
"SELECT COUNT(DISTINCT pm.post_id) FROM {$wpdb->postmeta} pm INNER JOIN {$wpdb->posts} p ON p.ID = pm.post_id WHERE pm.meta_key = %s AND pm.meta_value != '' AND p.post_status = 'publish'", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
'_trustbio_review_date'
)
);
$tbd_stale_months = (int) get_option( 'trustbio_review_stale_months', 12 );
$tbd_stale_date = gmdate( 'Y-m-d', strtotime( "-{$tbd_stale_months} months" ) );
// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
$tbd_overdue = (int) $wpdb->get_var(
$wpdb->prepare(
"SELECT COUNT(*) FROM {$wpdb->postmeta} pm INNER JOIN {$wpdb->posts} p ON p.ID = pm.post_id WHERE pm.meta_key = %s AND pm.meta_value != '' AND pm.meta_value < %s AND p.post_status = 'publish'", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
'_trustbio_review_date',
$tbd_stale_date
)
);
$tbd_pct = $tbd_total_posts > 0 ? round( ( $tbd_reviewed_posts / $tbd_total_posts ) * 100 ) : 0;
// ── Recent reviewed posts ──────────────────────────────────────────────────
// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
$tbd_recent = $wpdb->get_results(
$wpdb->prepare(
"SELECT p.ID, p.post_title, pm.meta_value AS review_date, pm2.meta_value AS reviewer_id
FROM {$wpdb->posts} p
INNER JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id AND pm.meta_key = %s
LEFT JOIN {$wpdb->postmeta} pm2 ON p.ID = pm2.post_id AND pm2.meta_key = %s
WHERE p.post_status = 'publish' AND pm.meta_value != ''
ORDER BY pm.meta_value DESC
LIMIT 6", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
'_trustbio_review_date',
'_trustbio_reviewer_id'
),
ARRAY_A
);
// Pre-fetch all reviewer names used in the "Recently Reviewed" list — one query instead of N.
$tbd_reviewer_map = array();
$tbd_reviewer_ids = array_filter( array_unique( array_column( (array) $tbd_recent, 'reviewer_id' ) ) );
if ( ! empty( $tbd_reviewer_ids ) ) {
// One %d placeholder per id, with the ids passed as arguments.
$tbd_rev_ids = array_map( 'intval', $tbd_reviewer_ids );
$tbd_rev_slots = implode( ', ', array_fill( 0, count( $tbd_rev_ids ), '%d' ) );
// See the note above on the runtime-built placeholder list.
// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
$tbd_reviewer_rows = $wpdb->get_results( $wpdb->prepare( "SELECT id, display_name FROM {$wpdb->prefix}trustbio_authors WHERE id IN ({$tbd_rev_slots})", $tbd_rev_ids ) );
foreach ( $tbd_reviewer_rows as $tbd_rev_row ) {
$tbd_reviewer_map[ (int) $tbd_rev_row->id ] = $tbd_rev_row->display_name;
}
}
// ── Setup checklist state ──────────────────────────────────────────────────
$tbd_checks = array(
'category' => $tbd_categories > 0,
'author' => $tbd_authors > 0,
'box' => $tbd_boxes > 0,
'schema' => (bool) get_option( 'trustbio_enable_schema', 1 ),
'review_bar' => $tbd_rev_bars > 0,
);
$tbd_all_done = ! in_array( false, $tbd_checks, true );
// ── Progress bar colour ────────────────────────────────────────────────────
$tbd_bar_class = $tbd_pct >= 80 ? '' : ( $tbd_pct >= 40 ? 'tbd-progress-bar__fill--warn' : 'tbd-progress-bar__fill--empty' );
// ── Quick URL helpers ──────────────────────────────────────────────────────
$tbd_url_authors = admin_url( 'admin.php?page=trustbio-authors' );
$tbd_url_cats = admin_url( 'admin.php?page=trustbio-categories' );
$tbd_url_boxes = admin_url( 'admin.php?page=trustbio-boxes' );
$tbd_url_bars = admin_url( 'admin.php?page=trustbio-review-bars' );
$tbd_url_settings = admin_url( 'admin.php?page=trustbio-settings' );
$tbd_url_new_box = admin_url( 'admin.php?page=trustbio-boxes&action=new' );
$tbd_url_new_bar = admin_url( 'admin.php?page=trustbio-review-bars&action=new' );
?>