<?php
/**
 * File Path: includes/data/class-mbs-universe-reading-order-dao.php
 *
 * Universe Reading Order DAO
 *
 * Data-access layer for the `mbs_universe_reading_order` term_meta key.
 * Extends MBS_Reading_Order_DAO_Base (D-005, Session 117) which provides all
 * shared logic; this class supplies the Universe-specific implementations.
 *
 * Reading order is an attribute of a universe term that records the
 * recommended sequence for reading the books in that universe. The admin
 * sets this via drag-and-drop on the Settings -> Universes tab.
 *
 * Storage shape:
 *     term_meta('mbs_universe_reading_order') = JSON-encoded array of
 *     book post IDs, in reading-order sequence. e.g. "[42,127,33]"
 *
 * Lazy initialization (R1):
 *     When the term_meta is absent, get_ordered_book_ids() computes a
 *     default order from post_title ascending. The computed default is
 *     NOT persisted -- it becomes the starting state the admin sees when
 *     first expanding a universe card and is locked in on first Save.
 *
 * Universe vs. Collection:
 *     Unlike collections, universes do not store a per-book position
 *     postmeta (no mbs_universe_{term_id}_position field). The default
 *     reading order therefore falls back to alphabetical by title. The
 *     admin drag-and-drop persisted here is the only ordering mechanism.
 *
 * PRO-gated:
 *     The reading-order consumer (taxonomy-mbs-universe.php) applies the
 *     persisted order only when mbs_is_pro() is true. Saving via this
 *     DAO is also PRO-gated in MBS_Tab_Universes::save().
 *
 * Per project SQL rules: all raw SQL for this domain lives in this file.
 *
 * @package    My_Book_Showroom
 * @subpackage Includes/Data
 * @since      1.0.0
 */

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

class MBS_Universe_Reading_Order_DAO extends MBS_Reading_Order_DAO_Base { // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedClassFound -- mbs_/MBS_ prefix is correct; scanner not configured with plugin prefix

	/** term_meta key for the persisted reading order. */
	public const META_KEY = 'mbs_universe_reading_order';

	// -------------------------------------------------------------------------
	// Base class implementation
	// -------------------------------------------------------------------------

	/**
	 * @return string mbs_universe taxonomy slug.
	 */
	protected static function taxonomy(): string {
		return MBS_TAX_UNIVERSE;
	}

	/**
	 * Return the mbs_reading_order.type partition key for this subclass.
	 *
	 * @return string
	 * @since 1.5.0
	 */
	protected static function type_key(): string {
		return 'universe';
	}

	/**
	 * Compute the default reading order for a universe with no persisted
	 * order: books sorted by post_title ascending for a stable, predictable
	 * starting sequence.
	 *
	 * Unlike Collection, Universe has no per-book position postmeta, so
	 * alphabetical by title is the only stable fallback.
	 *
	 * @param int $term_id mbs_universe term ID.
	 * @return int[]
	 */
	protected static function compute_default_order( int $term_id ): array {
		global $wpdb;

		$sql = $wpdb->prepare(
			"SELECT p.ID
			 FROM {$wpdb->posts} p
			 INNER JOIN {$wpdb->term_relationships} tr ON tr.object_id = p.ID
			 INNER JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
			 WHERE tt.taxonomy = %s
			   AND tt.term_id = %d
			   AND p.post_type = %s
			   AND p.post_status NOT IN ('trash', 'auto-draft')
			 ORDER BY p.post_title ASC",
			MBS_TAX_UNIVERSE,
			$term_id,
			MBS_POST_TYPE
		);

		return array_map( 'intval', (array) $wpdb->get_col( $sql ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- DAO layer -- direct DB access is intentional per MBS architecture
	}

	/**
	 * Build a display map for rendering the reading-order drag list.
	 *
	 * Returned shape:
	 *     [
	 *       <book_id> => [
	 *         'title' => string,
	 *         'thumb' => string,   // thumbnail URL or '' if none
	 *       ],
	 *       ...
	 *     ]
	 *
	 * The returned array preserves the order of $book_ids so the caller can
	 * iterate it directly for render output. $term_id is unused for Universe
	 * (no per-book position postmeta exists).
	 *
	 * @param int[]    $book_ids Ordered list of book post IDs.
	 * @param int|null $term_id  Unused; present for base-class signature compatibility.
	 * @return array<int, array{title:string, thumb:string}>
	 */
	public static function get_book_display_map( array $book_ids, ?int $term_id = null ): array {
		if ( empty( $book_ids ) ) {
			return array();
		}

		global $wpdb;
		$placeholders = implode( ',', array_fill( 0, count( $book_ids ), '%d' ) );

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $placeholders built from count only.
		$sql = $wpdb->prepare(
			"SELECT p.ID, p.post_title
			 FROM {$wpdb->posts} p
			 WHERE p.ID IN ({$placeholders})
			 GROUP BY p.ID", // 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
			$book_ids
		);
		// phpcs:enable

		$rows = $wpdb->get_results( $sql, OBJECT_K ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- DAO layer -- direct DB access is intentional per MBS architecture

		$map = array();
		foreach ( $book_ids as $id ) {
			$id = (int) $id;
			if ( ! isset( $rows[ $id ] ) ) {
				continue;
			}
			$thumb_url  = get_the_post_thumbnail_url( $id, array( 40, 60 ) ); // R5-exempt: reading order data DAO context
			$map[ $id ] = array(
				'title' => (string) $rows[ $id ]->post_title,
				'thumb' => is_string( $thumb_url ) ? $thumb_url : '',
			);
		}
		return $map;
	}

	// -------------------------------------------------------------------------
	// Reading order API overrides (4.34 + UX-RO-002)
	// -------------------------------------------------------------------------

	/**
	 * Return books in the universe in browse order (alphabetical by title).
	 *
	 * Universe has no canonical position number (unlike series_position),
	 * so the browse order falls back to post_title ascending. Used by
	 * MBS_Archive_Reader for 'order' display mode.
	 *
	 * @param int $term_id mbs_universe term ID.
	 * @return int[]
	 * @since 4.34.0
	 */
	public static function get_order_sorted_ids( int $term_id ): array {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
		return array_map( 'intval', (array) $wpdb->get_col( $wpdb->prepare(
			"SELECT p.ID
			 FROM {$wpdb->posts} p
			 INNER JOIN {$wpdb->term_relationships} tr ON tr.object_id = p.ID
			 INNER JOIN {$wpdb->term_taxonomy} tt
			   ON tt.term_taxonomy_id = tr.term_taxonomy_id
			   AND tt.taxonomy = 'mbs_universe' AND tt.term_id = %d
			 WHERE p.post_status = 'publish'
			 ORDER BY p.post_title ASC",
			$term_id
		) ) );
	}

	/**
	 * Return books in this universe ordered by universe_reading_order_position.
	 *
	 * Books with no position set are sorted last, then alphabetically by title.
	 * Reads from mbs_book_meta directly (UX-RO-002). Used by MBS_Archive_Reader
	 * for 'reading_order' display mode.
	 *
	 * @param int $term_id mbs_universe term ID.
	 * @return int[]
	 * @since 4.37.0
	 */
	public static function get_ordered_book_ids( int $term_id ): array {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
		return array_map( 'intval', (array) $wpdb->get_col( $wpdb->prepare(
			"SELECT p.ID
			 FROM {$wpdb->posts} p
			 INNER JOIN {$wpdb->term_relationships} tr ON tr.object_id = p.ID
			 INNER JOIN {$wpdb->term_taxonomy} tt
			   ON tt.term_taxonomy_id = tr.term_taxonomy_id
			   AND tt.taxonomy = 'mbs_universe' AND tt.term_id = %d
			 LEFT JOIN {$wpdb->prefix}mbs_book_meta bm ON bm.post_id = p.ID
			 WHERE p.post_status = 'publish'
			 ORDER BY
			   ISNULL(bm.universe_reading_order_position) ASC,
			   bm.universe_reading_order_position ASC,
			   p.post_title ASC",
			$term_id
		) ) );
	}

	/**
	 * Whether any book in this universe has a universe_reading_order_position set.
	 *
	 * Overrides base class to check mbs_book_meta instead of term_meta flag.
	 *
	 * @param int $term_id mbs_universe term ID.
	 * @return bool
	 * @since 4.37.0
	 */
	public static function has_persisted_order( int $term_id ): bool {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
		return (bool) $wpdb->get_var( $wpdb->prepare(
			"SELECT 1
			 FROM {$wpdb->posts} p
			 INNER JOIN {$wpdb->term_relationships} tr ON tr.object_id = p.ID
			 INNER JOIN {$wpdb->term_taxonomy} tt
			   ON tt.term_taxonomy_id = tr.term_taxonomy_id
			   AND tt.taxonomy = 'mbs_universe' AND tt.term_id = %d
			 INNER JOIN {$wpdb->prefix}mbs_book_meta bm ON bm.post_id = p.ID
			 WHERE p.post_status = 'publish'
			   AND bm.universe_reading_order_position IS NOT NULL
			 LIMIT 1",
			$term_id
		) );
	}

	/**
	 * Persist reading order positions to mbs_book_meta (UX-RO-002).
	 *
	 * Maps submitted ordinal positions (1, 2, 3...) to
	 * mbs_book_meta.universe_reading_order_position as DECIMAL.
	 * Books in the universe absent from the submitted list have their
	 * position NULLed. Does NOT call parent::save_reading_order().
	 *
	 * @param int   $term_id         mbs_universe term ID.
	 * @param int[] $ordered_post_ids Submitted order.
	 * @return void
	 * @since 4.37.0
	 */
	public static function save_reading_order( int $term_id, array $ordered_post_ids ): void {
		global $wpdb;
		$bm_table = $wpdb->prefix . 'mbs_book_meta';

		// V3-B-014: Filter submitted IDs to only books that actually belong to
		// this universe. Prevents crafted AJAX POSTs from injecting positions
		// for books from other universes.
		$member_ids = get_objects_in_term( $term_id, MBS_TAX_UNIVERSE );
		if ( is_wp_error( $member_ids ) || ! is_array( $member_ids ) ) {
			$member_ids = array();
		}
		$member_ids = array_map( 'intval', $member_ids );
		if ( empty( $member_ids ) ) {
			// Fallback: direct DB query as authority when object-cache returns empty.
			$member_ids = static::get_books_in_term( $term_id );
		}
		$ordered_post_ids = array_values(
			array_filter(
				$ordered_post_ids,
				static function( $post_id ) use ( $member_ids ) {
					return in_array( (int) $post_id, $member_ids, true );
				}
			)
		);

		// Null-out all books in this universe first.
		$all_ids = static::get_books_in_term( $term_id );
		foreach ( $all_ids as $post_id ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
			$wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
				$bm_table,
				array( 'universe_reading_order_position' => null ),
				array( 'post_id' => $post_id ),
				array( '%s' ), array( '%d' )
			);
		}

		// Write ordinal positions for submitted books.
		$position = 1;
		foreach ( $ordered_post_ids as $post_id ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
			$wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
				$bm_table,
				array( 'universe_reading_order_position' => (float) $position ),
				array( 'post_id' => (int) $post_id ),
				array( '%f' ), array( '%d' )
			);
			$position++;
		}
	}

	// -------------------------------------------------------------------------
	// Backward-compatibility aliases
	// -------------------------------------------------------------------------

	/**
	 * Alias for get_books_in_term(). Retained for call-site compatibility.
	 *
	 * @param int $term_id mbs_universe term ID.
	 * @return int[]
	 */
	public static function get_books_in_universe( int $term_id ): array {
		return static::get_books_in_term( $term_id );
	}

	/**
	 * Alias for prune_from_all(). Retained for call-site compatibility.
	 *
	 * @param int $book_id Book post ID being removed.
	 * @return void
	 */
	public static function prune_from_all_universes( int $book_id ): void {
		static::prune_from_all( $book_id );
	}
}
