<?php
if ( ! defined( 'ABSPATH' ) ) exit;
class BCHAT_DB {
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin tables require direct DB access; no WP API alternative exists.
private static function prefix() {
global $wpdb;
return $wpdb->prefix . 'BCHAT_';
}
/* ─── TOPICS ─── */
public static function get_topics( $args = array() ) {
global $wpdb;
$table = self::prefix() . 'topics';
$where = '1=1';
$params = array();
if ( ! empty( $args['language'] ) ) {
$where .= ' AND language = %s';
$params[] = $args['language'];
}
$sql = "SELECT * FROM $table WHERE $where ORDER BY name ASC";
return $params ? $wpdb->get_results( $wpdb->prepare( $sql, $params ) ) : $wpdb->get_results( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table is static prefix, no user input
}
public static function get_topic( $id ) {
global $wpdb;
return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM " . self::prefix() . "topics WHERE id = %d", $id ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- self::prefix() returns static safe table prefix, no user input
}
public static function insert_topic( $data ) {
global $wpdb;
$data['slug'] = sanitize_title( $data['name'] );
$wpdb->insert( self::prefix() . 'topics', $data );
return $wpdb->insert_id;
}
public static function delete_topic( $id ) {
global $wpdb;
$wpdb->delete( self::prefix() . 'topics', array( 'id' => $id ), array( '%d' ) );
}
/* ─── CALENDAR ─── */
public static function get_calendar_items( $args = array() ) {
global $wpdb;
$table = self::prefix() . 'calendar';
$where = '1=1';
$params = array();
if ( ! empty( $args['status'] ) ) {
$where .= ' AND status = %s';
$params[] = $args['status'];
}
if ( ! empty( $args['topic_id'] ) ) {
$where .= ' AND topic_id = %d';
$params[] = intval( $args['topic_id'] );
}
if ( ! empty( $args['language'] ) ) {
$where .= ' AND language = %s';
$params[] = $args['language'];
}
$order = ! empty( $args['orderby'] )
? 'ORDER BY ' . esc_sql( $args['orderby'] ) . ' DESC'
: 'ORDER BY (c.source IN (\'quick\',\'optimizer\')) DESC, c.created_at DESC';
$sql = "SELECT c.*, t.name as topic_name FROM $table c LEFT JOIN " . self::prefix() . "topics t ON c.topic_id = t.id WHERE $where $order";
return $params ? $wpdb->get_results( $wpdb->prepare( $sql, $params ) ) : $wpdb->get_results( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- table names are static, no user input
}
public static function get_calendar_item( $id ) {
global $wpdb;
$table = self::prefix() . 'calendar';
return $wpdb->get_row( $wpdb->prepare( "SELECT c.*, t.name as topic_name FROM $table c LEFT JOIN " . self::prefix() . "topics t ON c.topic_id = t.id WHERE c.id = %d", $id ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- table names are static prefixes
}
public static function insert_calendar_item( $data ) {
global $wpdb;
$wpdb->insert( self::prefix() . 'calendar', $data );
return $wpdb->insert_id;
}
public static function update_calendar_item( $id, $data ) {
global $wpdb;
return $wpdb->update( self::prefix() . 'calendar', $data, array( 'id' => $id ), null, array( '%d' ) );
}
public static function delete_calendar_item( $id ) {
global $wpdb;
$wpdb->delete( self::prefix() . 'calendar', array( 'id' => $id ), array( '%d' ) );
}
public static function get_counts_by_status() {
global $wpdb;
$table = self::prefix() . 'calendar';
$rows = $wpdb->get_results( "SELECT status, COUNT(*) as cnt FROM $table GROUP BY status" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table is static prefix + table name
$counts = array( 'pending' => 0, 'confirmed' => 0, 'to_review' => 0, 'rejected' => 0, 'generated' => 0 );
foreach ( $rows as $r ) {
$counts[ $r->status ] = (int) $r->cnt;
}
return $counts;
}
public static function get_latest_scheduled_date() {
global $wpdb;
$table = self::prefix() . 'calendar';
return $wpdb->get_var( "SELECT scheduled_date FROM $table WHERE scheduled_date IS NOT NULL ORDER BY scheduled_date DESC LIMIT 1" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table is static prefix + table name
}
}