<?php
/**
 * File Path: includes/data/class-mbs-book-club-dao.php
 *
 * Book Club DAO
 *
 * Data-access layer for the three book club resource tables introduced in
 * migration 4.13:
 *
 *   mbs_book_club            — one row per book: enabled flag + guide HTML.
 *   mbs_book_club_questions  — ordered discussion questions (one row per question).
 *   mbs_book_club_files      — ordered downloadable files (one row per attachment).
 *
 * Migration 4-13 seeded these tables from the corresponding legacy postmeta
 * keys; all reads and writes now go through this DAO. Direct $wpdb usage
 * is not permitted.
 *
 * @package    My_Book_Showroom
 * @subpackage Includes/Data
 * @since      1.7.0
 */

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

// 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_Club_DAO { // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedClassFound -- mbs_/MBS_ prefix is correct; scanner not configured with plugin prefix

	// -------------------------------------------------------------------------
	// Header table (mbs_book_club) — one row per book
	// -------------------------------------------------------------------------

	/**
	 * Upsert the book-level header row: enabled flag and guide HTML.
	 *
	 * Uses INSERT ... ON DUPLICATE KEY UPDATE so new books get a row and
	 * existing books get their header updated atomically.
	 *
	 * @param int    $post_id Book post ID.
	 * @param bool   $enabled Whether the book club section is visible on the front end.
	 * @param string $guide   Discussion guide HTML (wp_kses_post-sanitized by caller).
	 * @return void
	 */
	public static function upsert_header( int $post_id, bool $enabled, string $guide ): void {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_book_club';
		// 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, enabled, guide)
				 VALUES (%d, %d, %s)
				 ON DUPLICATE KEY UPDATE enabled = %d, guide = %s",
				$post_id,
				(int) $enabled,
				$guide,
				(int) $enabled,
				$guide
			)
		);
	}

	/**
	 * Get the header row for a book.
	 *
	 * Returns a safe-default array when no row exists (pre-migration book or a
	 * book that has never been saved with the book club metabox).
	 *
	 * @param int $post_id Book post ID.
	 * @return array{enabled: bool, guide: string}
	 */
	public static function get_header( int $post_id ): array {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_book_club';
		// 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 enabled, guide FROM {$table} WHERE post_id = %d LIMIT 1",
				$post_id
			),
			ARRAY_A
		);
		if ( null === $row ) {
			return array( 'enabled' => false, 'guide' => '' );
		}
		return array(
			'enabled' => (bool) $row['enabled'],
			'guide'   => (string) $row['guide'],
		);
	}

	// -------------------------------------------------------------------------
	// Questions table (mbs_book_club_questions)
	// -------------------------------------------------------------------------

	/**
	 * Replace all discussion questions for a book.
	 *
	 * Deletes existing rows then inserts the supplied questions in order.
	 * Passing an empty array clears all questions.
	 *
	 * @param int      $post_id   Book post ID.
	 * @param string[] $questions Ordered array of question strings.
	 * @return void
	 */
	public static function replace_questions( int $post_id, array $questions ): void {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_book_club_questions';
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$wpdb->query( $wpdb->prepare( "DELETE FROM {$table} WHERE post_id = %d", $post_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
		foreach ( array_values( $questions ) as $position => $question ) {
			$q = (string) $question;
			if ( '' === $q ) {
				continue;
			}
			$wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
				$table,
				array(
					'post_id'  => $post_id,
					'position' => $position,
					'question' => $q,
				),
				array( '%d', '%d', '%s' )
			);
		}
	}

	/**
	 * Get ordered discussion questions for a book.
	 *
	 * @param int $post_id Book post ID.
	 * @return string[] Ordered array of question strings.
	 */
	public static function get_questions( int $post_id ): array {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_book_club_questions';
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $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(
				"SELECT question FROM {$table}
				 WHERE post_id = %d
				 ORDER BY position ASC, id ASC",
				$post_id
			)
		);
		return array_values( array_map( 'strval', (array) $rows ) );
	}

	// -------------------------------------------------------------------------
	// Files table (mbs_book_club_files)
	// -------------------------------------------------------------------------

	/**
	 * Replace all downloadable files for a book.
	 *
	 * Deletes existing rows then inserts the supplied file records in order.
	 * Passing an empty array clears all files.
	 *
	 * Each element of $files must be an array with keys:
	 *   'id'    => int  (WP attachment post ID)
	 *   'label' => string (optional display label)
	 *
	 * @param int                          $post_id Book post ID.
	 * @param array<array{id:int,label:string}> $files   Ordered array of file records.
	 * @return void
	 */
	public static function replace_files( int $post_id, array $files ): void {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_book_club_files';
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$wpdb->query( $wpdb->prepare( "DELETE FROM {$table} WHERE post_id = %d", $post_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
		foreach ( array_values( $files ) as $position => $item ) {
			$att_id = absint( $item['id'] ?? 0 );
			if ( $att_id <= 0 ) {
				continue;
			}
			$label = (string) ( $item['label'] ?? '' );
			$wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
				$table,
				array(
					'post_id'       => $post_id,
					'position'      => $position,
					'attachment_id' => $att_id,
					'label'         => $label,
				),
				array( '%d', '%d', '%d', '%s' )
			);
		}
	}

	/**
	 * Get ordered downloadable files for a book.
	 *
	 * @param int $post_id Book post ID.
	 * @return array<array{id:int,label:string}> Ordered array of file records.
	 */
	public static function get_files( int $post_id ): array {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_book_club_files';
		// 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 attachment_id, label FROM {$table}
				 WHERE post_id = %d
				 ORDER BY position ASC, id ASC",
				$post_id
			),
			ARRAY_A
		);
		$result = array();
		foreach ( (array) $rows as $row ) {
			$result[] = array(
				'id'    => (int) $row['attachment_id'],
				'label' => (string) $row['label'],
			);
		}
		return $result;
	}

	// -------------------------------------------------------------------------
	// Cleanup
	// -------------------------------------------------------------------------

	/**
	 * Delete all book club data for a book across all three tables.
	 *
	 * Called on non-PRO save to clear any lingering data.
	 *
	 * @param int $post_id Book post ID.
	 * @return void
	 */
	public static function delete_all_for_book( int $post_id ): void {
		global $wpdb;
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$wpdb->query( $wpdb->prepare( "DELETE FROM {$wpdb->prefix}mbs_book_club           WHERE post_id = %d", $post_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$wpdb->query( $wpdb->prepare( "DELETE FROM {$wpdb->prefix}mbs_book_club_questions WHERE post_id = %d", $post_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$wpdb->query( $wpdb->prepare( "DELETE FROM {$wpdb->prefix}mbs_book_club_files     WHERE post_id = %d", $post_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
	}
}
