<?php
/**
 * File Path: includes/data/class-mbs-collection-reading-order-dao.php
 *
 * Collection Reading Order DAO
 *
 * Data-access layer for the `mbs_collection_reading_order` term_meta key.
 * Extends MBS_Reading_Order_DAO_Base (D-005, Session 117) which provides all
 * shared logic; this class supplies the Collection-specific implementations.
 *
 * Reading order is an attribute of a collection term that records the
 * recommended sequence for reading the books in that collection. The admin
 * sets this via drag-and-drop on the Settings -> Collections tab.
 *
 * Storage shape:
 *     term_meta('mbs_collection_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 the mbs_book_collection_positions table (via
 *     MBS_Book_Collection_Position_DAO) ascending, then by title for
 *     stability. The computed default is NOT persisted — it becomes the
 *     starting state the admin sees when first expanding a collection card
 *     and is locked in on first Save.
 *
 * Relationship to book-editor position data:
 *     The canonical per-book position within a collection is stored in the
 *     mbs_book_collection_positions table (MBS_Book_Collection_Position_DAO),
 *     managed by the Book Header metabox (F-016). The legacy postmeta key
 *     mbs_collection_{term_id}_position was retired by migration 4.17. This
 *     DAO never writes to the position table.
 *     The reading-order term_meta managed here is the admin drag-and-drop
 *     override that powers the Collections settings tab.
 *
 * 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;
}

// 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_Collection_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_collection_reading_order';

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

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

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

	/**
	 * Compute the default reading order for a collection with no persisted
	 * order: books sorted by mbs_book_collection_positions table (via DAO)
	 * ascending (unset positions last), then by title for stability.
	 *
	 * Uses CAST … AS DECIMAL so non-numeric position values sort correctly
	 * alongside numeric ones (consistent with how the Series DAO handles it).
	 *
	 * @param int $term_id mbs_collection term ID.
	 * @return int[]
	 */
	protected static function compute_default_order( int $term_id ): array {
		global $wpdb;

		// Phase 8: read positions from mbs_book_collection_positions table
		// instead of the postmeta JOIN. Falls back to 0 for books with no row.
		$position_map = MBS_Book_Collection_Position_DAO::get_positions_for_collection( $term_id );

		$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')
			 GROUP BY p.ID",
			MBS_TAX_COLLECTION,
			$term_id,
			MBS_POST_TYPE
		);

		$book_ids = 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

		// Sort by position ASC (books with no position sort last), then by title.
		$titles = array();
		foreach ( $book_ids as $id ) {
			$post = get_post( $id );
			$titles[ $id ] = $post ? $post->post_title : '';
		}

		usort( $book_ids, static function( $a, $b ) use ( $position_map, $titles ) {
			$pos_a = $position_map[ $a ] ?? 0;
			$pos_b = $position_map[ $b ] ?? 0;
			$a_unset = $pos_a <= 0;
			$b_unset = $pos_b <= 0;
			if ( $a_unset !== $b_unset ) {
				return $a_unset ? 1 : -1; // unset positions sort last
			}
			if ( $pos_a !== $pos_b ) {
				return $pos_a <=> $pos_b;
			}
			return strcmp( $titles[ $a ] ?? '', $titles[ $b ] ?? '' );
		} );

		return $book_ids;
	}

	/**
	 * Build a display map for rendering the reading-order drag list.
	 *
	 * Returned shape:
	 *     [
	 *       <book_id> => [
	 *         'title'    => string,
	 *         'position' => string|null,  // from mbs_book_collection_positions DAO
	 *         '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.
	 *
	 * @param int[]    $book_ids Ordered list of book post IDs.
	 * @param int|null $term_id  mbs_collection term ID (required; used for position lookup).
	 * @return array<int, array{title:string, position:?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;
		// Phase 8: read positions from mbs_book_collection_positions DAO.
		$position_map = MBS_Book_Collection_Position_DAO::get_positions_for_collection( (int) $term_id );

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

		// phpcs:ignore 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 spread correctly matches prepared placeholders; PHPCS cannot statically verify spread arg count
			$book_ids
		);

		$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
			$pos        = $position_map[ $id ] ?? null;
			$map[ $id ] = array(
				'title'    => (string) $rows[ $id ]->post_title,
				'position' => ( null === $pos || 0 === $pos ) ? null : (string) $pos,
				'thumb'    => is_string( $thumb_url ) ? $thumb_url : '',
			);
		}
		return $map;
	}

	// -------------------------------------------------------------------------
	// Reading order API overrides (4.34: collection-positions-backed storage)
	// -------------------------------------------------------------------------

	/**
	 * Return books in reading order via mbs_book_collection_positions (no junction table).
	 *
	 * Bypasses get_stored_order() entirely — delegates directly to compute_default_order()
	 * which reads from MBS_Book_Collection_Position_DAO.
	 *
	 * @param int $term_id mbs_collection term ID.
	 * @return int[]
	 * @since 4.34.0
	 */
	public static function get_ordered_book_ids( int $term_id ): array {
		return static::compute_default_order( $term_id );
	}

	/**
	 * Return books in native collection browse order (same as reading order for collections).
	 *
	 * @param int $term_id mbs_collection term ID.
	 * @return int[]
	 * @since 4.34.0
	 */
	public static function get_order_sorted_ids( int $term_id ): array {
		return static::compute_default_order( $term_id );
	}

	/**
	 * Persist reading order to mbs_book_collection_positions only.
	 *
	 * Does NOT call parent::save_reading_order() (no junction table write).
	 * Does NOT write term_meta presence flag.
	 *
	 * @param int   $term_id         mbs_collection term ID.
	 * @param int[] $ordered_post_ids Submitted order.
	 * @return void
	 * @since 4.34.0
	 */
	public static function save_reading_order( int $term_id, array $ordered_post_ids ): void {
		// Sanitize to positive ints, deduplicate.
		$submitted = array();
		foreach ( $ordered_post_ids as $id ) {
			$id = (int) $id;
			if ( $id > 0 && ! in_array( $id, $submitted, true ) ) {
				$submitted[] = $id;
			}
		}

		// Clear positions for all books in this collection.
		$all_ids = static::get_books_in_term( $term_id );
		foreach ( $all_ids as $post_id ) {
			MBS_Book_Collection_Position_DAO::delete( $post_id, $term_id );
		}

		// Write ordinal positions for submitted books.
		$position = 1;
		foreach ( $submitted as $post_id ) {
			MBS_Book_Collection_Position_DAO::upsert( $post_id, $term_id, $position );
			$position++;
		}
		// NOTE: do NOT write mbs_reading_order junction table rows.
		// NOTE: do NOT write term_meta presence flag.
	}

	/**
	 * Check whether any reading-order position has been persisted for this collection.
	 *
	 * Overrides base class (which checks term_meta presence flag) because the
	 * 4.34 refactor stores order in mbs_book_collection_positions instead of
	 * term_meta + mbs_reading_order. The presence flag is never written by
	 * save_reading_order() so the base-class check always returned false.
	 *
	 * @param int $term_id mbs_collection term ID.
	 * @return bool True when at least one book has a stored position.
	 * @since 4.34.0
	 */
	public static function has_persisted_order( int $term_id ): bool {
		$positions = MBS_Book_Collection_Position_DAO::get_positions_for_collection( $term_id );
		return ! empty( $positions );
	}

	/**
	 * Remove a single book from this collection's reading order.
	 *
	 * Overrides base class (which deleted from mbs_reading_order table) because
	 * the 4.34 refactor stores order in mbs_book_collection_positions.
	 *
	 * Two-step removal:
	 *   1. Delete the position row so has_persisted_order() reflects the change.
	 *   2. Remove the book from the term relationship so compute_default_order()
	 *      (which queries wp_term_relationships) no longer includes the book.
	 *
	 * @param int $term_id mbs_collection term ID.
	 * @param int $book_id Book post ID being removed.
	 * @return void
	 * @since 4.34.0
	 */
	public static function prune_book( int $term_id, int $book_id ): void {
		MBS_Book_Collection_Position_DAO::delete( $book_id, $term_id );
		wp_remove_object_terms( $book_id, $term_id, static::taxonomy() );
	}

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

	/**
	 * Alias for get_books_in_term(). Retained for call-site compatibility.
	 *
	 * @param int $term_id mbs_collection term ID.
	 * @return int[]
	 */
	public static function get_books_in_collection( 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_collections( int $book_id ): void {
		static::prune_from_all( $book_id );
	}
}
