<?php
/**
 * File Path: includes/migrations/class-mbs-migration-4-10.php
 *
 * Migration 4.10 — mbs_book_stats table
 *
 * Creates mbs_book_stats with one row per book, storing computed review
 * aggregates (average rating and review count) that are recalculated by
 * MBS_Review_Handler on every comment status transition. Keeping review
 * stats separate from the editorial mbs_book_meta table prevents review
 * submission writes from touching the editorial row.
 *
 * Backfills from mbs_review_average and mbs_review_count postmeta.
 * PRIMARY KEY on post_id enforces one stats row per book.
 *
 * @package MyBookShowroom
 * @since   1.7.0
 */

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

/**
 * Migration 4.10: mbs_book_stats table.
 */
// 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_Migration_4_10 implements MBS_Migration { // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedClassFound -- mbs_/MBS_ prefix is correct; scanner not configured with plugin prefix

	/**
	 * @return string Migration version string.
	 */
	public function version(): string {
		return '4.10';
	}

	/**
	 * Run the migration.
	 *
	 * Idempotent: SHOW TABLES guard prevents double-create.
	 * INSERT IGNORE on backfill is safe to replay.
	 *
	 * @return void
	 */
	public function up(): void {
		global $wpdb;

		$table           = $wpdb->prefix . 'mbs_book_stats';
		$charset_collate = $wpdb->get_charset_collate();

		// --- DDL: create table if not present ----------------------------------
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$exists = $wpdb->get_var( "SHOW TABLES LIKE '{$table}'" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
		if ( $exists !== $table ) {
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.SchemaChange
			$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- DAO layer -- direct DB access is intentional per MBS architecture
				"CREATE TABLE {$table} (
					post_id      BIGINT UNSIGNED NOT NULL,
					review_avg   DECIMAL(3,2)    NULL DEFAULT NULL,
					review_count INT UNSIGNED    NOT NULL DEFAULT 0,
					PRIMARY KEY (post_id),
					KEY idx_review_avg (review_avg)
				) {$charset_collate}"
			);
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.SchemaChange
		}

		// --- Backfill: collect post_ids that have either review key -----------
		$meta_keys    = array( 'mbs_review_average', 'mbs_review_count' );
		$placeholders = implode( ',', array_fill( 0, count( $meta_keys ), '%s' ) );

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$post_ids = $wpdb->get_col( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			$wpdb->prepare( // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- safe: variadic ...$params / ...$row_ids correctly matches prepared placeholders; PHPCS cannot statically verify spread arg count
				"SELECT DISTINCT post_id FROM {$wpdb->postmeta}
				 WHERE meta_key IN ({$placeholders})", // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- safe: variadic ...$params spread correctly matches prepared placeholders; PHPCS cannot statically verify spread arg count
				...$meta_keys
			)
		);

		foreach ( (array) $post_ids as $raw_post_id ) {
			$post_id = (int) $raw_post_id;

			$avg_raw   = get_post_meta( $post_id, 'mbs_review_average', true );
			$count_raw = get_post_meta( $post_id, 'mbs_review_count',   true );

			$count = absint( $count_raw );

			// review_avg: NULL when no approved reviews (count=0) or unset.
			if ( '' === (string) $avg_raw || 0 === $count ) {
				// 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 IGNORE INTO {$table} (post_id, review_count)
						 VALUES (%d, %d)",
						$post_id,
						$count
					)
				);
			} else {
				$avg = round( (float) $avg_raw, 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 IGNORE INTO {$table} (post_id, review_avg, review_count)
						 VALUES (%d, %s, %d)",
						$post_id,
						number_format( $avg, 2, '.', '' ),
						$count
					)
				);
			}
		}
	}
}
