<?php
/**
 * File Path: includes/data/class-mbs-book-stats-dao.php
 *
 * Book Stats DAO
 *
 * Single-row DAO for the mbs_book_stats table, which stores computed review
 * aggregates (average rating and review count) per book. Aggregates are
 * recalculated by MBS_Review_Handler::recalculate_rating() on every comment
 * status transition.
 *
 * Kept separate from MBS_Book_Meta_DAO because review stats are computed
 * values (updated by reader actions) while book_meta holds editorial values
 * (updated by author/publisher metabox saves). Separating them prevents
 * review submission writes from contending with editorial writes.
 *
 * Write rule: WRITE-PROTOCOL.md §A + §B.
 *
 * @package MyBookShowroom
 * @since   1.7.0
 */

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * DAO for mbs_book_stats.
 *
 * @since 1.7.0
 */
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- DAO/migration: table names via $wpdb->prefix are trusted server-controlled values, not user input
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter -- same reason: $wpdb->prefix table names are safe; $where_sql is built from sanitized server-controlled inputs
class MBS_Book_Stats_DAO { // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedClassFound -- mbs_/MBS_ prefix is correct; scanner not configured with plugin prefix

	// -------------------------------------------------------------------------
	// Write
	// -------------------------------------------------------------------------

	/**
	 * Upsert review stats for a book.
	 *
	 * Called by MBS_Review_Handler::recalculate_rating() after every comment
	 * status transition. Sets review_avg to NULL when count is zero so the
	 * front-end template can distinguish "no reviews" from "average = 0.00".
	 *
	 * @param int   $post_id Book post ID.
	 * @param float $avg     Calculated average rating (0.0 when count = 0).
	 * @param int   $count   Count of approved reviews.
	 * @return void
	 */
	public static function upsert_stats( int $post_id, float $avg, int $count ): void {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_book_stats';

		if ( 0 === $count ) {
			// No approved reviews — store NULL avg so templates can suppress the block.
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
				$wpdb->prepare(
					"INSERT INTO {$table} (post_id, review_avg, review_count)
					 VALUES (%d, NULL, %d)
					 ON DUPLICATE KEY UPDATE review_avg = NULL, review_count = VALUES(review_count)",
					$post_id,
					$count
				)
			);
		} else {
			$avg_str = number_format( $avg, 2, '.', '' );
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
				$wpdb->prepare(
					"INSERT INTO {$table} (post_id, review_avg, review_count)
					 VALUES (%d, %s, %d)
					 ON DUPLICATE KEY UPDATE review_avg = VALUES(review_avg),
					                         review_count = VALUES(review_count)",
					$post_id,
					$avg_str,
					$count
				)
			);
		}
	}

	// -------------------------------------------------------------------------
	// Read
	// -------------------------------------------------------------------------

	/**
	 * Retrieve review stats for a book.
	 *
	 * @param int $post_id Book post ID.
	 * @return object|null Row with float review_avg (or null) and int review_count,
	 *                     or null if no stats row exists for this book.
	 */
	public static function get_stats( int $post_id ): ?object {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_book_stats';

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$row = $wpdb->get_row( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			$wpdb->prepare(
				"SELECT review_avg, review_count FROM {$table} WHERE post_id = %d LIMIT 1",
				$post_id
			)
		);

		return $row ?: null;
	}

	/**
	 * Return the top-rated books by average review score.
	 *
	 * Only books with at least $min_reviews approved reviews are included.
	 * Returns an array of objects with post_id, review_avg, review_count,
	 * ordered by review_avg DESC then review_count DESC.
	 *
	 * @param int $limit       Maximum rows to return. Default 10.
	 * @param int $min_reviews Minimum review count to qualify. Default 5.
	 * @return object[] Array of stdClass rows.
	 */
	public static function get_top_rated( int $limit = 10, int $min_reviews = 5 ): array {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_book_stats';

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			$wpdb->prepare(
				"SELECT post_id, review_avg, review_count
				 FROM {$table}
				 WHERE review_count >= %d
				   AND review_avg IS NOT NULL
				 ORDER BY review_avg DESC, review_count DESC
				 LIMIT %d",
				$min_reviews,
				$limit
			)
		);

		return (array) $rows;
	}
	// -------------------------------------------------------------------------
	// Delete
	// -------------------------------------------------------------------------

	/**
	 * Delete the mbs_book_stats row for a book.
	 *
	 * Called from the before_delete_post hook so no orphan rows remain after
	 * book deletion.
	 *
	 * @param int $post_id Book post ID.
	 * @return void
	 */
	public static function delete( int $post_id ): void {
		global $wpdb;
		$wpdb->delete( $wpdb->prefix . 'mbs_book_stats', array( 'post_id' => $post_id ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
	}

}
