<?php
/**
 * File Path: includes/data/class-mbs-series-reading-order-dao.php
 *
 * Series Reading Order DAO
 *
 * Data-access layer for the `mbs_series_reading_order` term_meta key.
 *
 * Extends MBS_Reading_Order_DAO_Base (DUP-001, Session 136) to eliminate
 * duplication with MBS_Collection_Reading_Order_DAO and
 * MBS_Universe_Reading_Order_DAO. This class implements the three abstract
 * methods required by the base and retains series-specific public API
 * (term_id_from_tt_id) plus backward-compat aliases
 * (get_books_in_series, prune_from_all_series) so all existing callers are
 * unchanged.
 *
 * Shared logic removed (~200 lines):
 *   get_ordered_book_ids, has_persisted_order, save_reading_order,
 *   append_book, prune_book, get_stored_order -- all inherited from base.
 *
 * @package    My_Book_Showroom
 * @subpackage Includes/Data
 * @since      1.0.0
 */

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

// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter -- same reason: $wpdb->prefix table names are safe
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- DAO: table names via $wpdb->prefix and $bm_table are trusted server-controlled values, not user input
// phpcs:disable WordPress.DB.PreparedSQL.UnescapedDBParameter -- DAO: $sql built from $wpdb->prepare() output; safe
class MBS_Series_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_series_reading_order';


	// -------------------------------------------------------------------------
	// Abstract method implementations (required by MBS_Reading_Order_DAO_Base)
	// -------------------------------------------------------------------------

	/**
	 * Return the taxonomy slug this DAO operates on.
	 *
	 * @return string
	 * @since 1.0.0
	 */
	protected static function taxonomy(): string {
		return MBS_TAX_SERIES;
	}

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

	/**
	 * Compute the default reading order for a series that has no persisted
	 * order yet: all books sorted by MIN(mbs_series_position) ascending,
	 * books with no position placed last, ties broken by title.
	 *
	 * ARCH-SERIES-001: series position read from mbs_book_meta.series_position
	 * (Sprint 163-C). Postmeta JOIN eliminated. DECIMAL(10,2) column sorts natively.
	 *
	 * @param int $term_id mbs_series term ID.
	 * @return int[]
	 * @since 1.0.0
	 */
	protected static function compute_default_order( int $term_id ): array {
		global $wpdb;

		// Sprint 163-C: read series_position from mbs_book_meta instead of
		// wp_postmeta (FLAG-1 resolution). No postmeta JOIN.
		$bm_table = $wpdb->prefix . 'mbs_book_meta';

		$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
			 LEFT JOIN {$bm_table} bm ON bm.post_id = p.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
			 ORDER BY ISNULL(MIN(bm.series_position)),
			          MIN(bm.series_position) ASC,
			          p.post_title ASC",
			MBS_TAX_SERIES,
			$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, PluginCheck.Security.DirectDB.UnescapedDBParameter -- DAO layer -- direct DB access is intentional per MBS architecture; $sql built from $wpdb->prepare() output
	}

	/**
	 * Build a display map for rendering the reading-order admin drag list.
	 *
	 * Returned shape:
	 *     [ <book_id> => [ 'title' => string, 'series_number' => string|null, 'thumb' => string ] ]
	 *
	 * Note: $term_id is accepted for interface compatibility with the abstract
	 * base but is not used by the query -- series position comes from
	 * mbs_book_meta.series_position (Sprint 163-C), not postmeta. ARCH-SERIES-001.
	 *
	 * @param int[]    $book_ids Ordered list of book post IDs.
	 * @param int|null $term_id  Unused; retained for abstract interface compatibility.
	 * @return array<int, array{title:string, series_number:?string, thumb:string}>
	 * @since 1.0.0
	 */
	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' ) );

		// Sprint 163-C: read series_position from mbs_book_meta (FLAG-1 resolution).
		$bm_table = $wpdb->prefix . 'mbs_book_meta';

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $placeholders built from count only.
		$sql = $wpdb->prepare(
			"SELECT p.ID, p.post_title,
			        bm.series_position AS series_number
			 FROM {$wpdb->posts} p
			 LEFT JOIN {$bm_table} bm ON bm.post_id = p.ID
			 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 WordPress.DB.PreparedSQL.InterpolatedNotPrepared

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

	// -------------------------------------------------------------------------
	// Reading order API overrides (4.34: mbs_book_meta column-backed storage)
	// -------------------------------------------------------------------------

	/**
	 * Return books in this series ordered by reading order, falling back to
	 * series position when no reading order position is set.
	 *
	 * Reads from mbs_book_meta directly (no junction table). COALESCEs
	 * series_reading_order_position with series_position for ordering.
	 *
	 * @param int $term_id mbs_series term ID.
	 * @return int[]
	 * @since 4.34.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_series' 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(COALESCE(bm.series_reading_order_position, bm.series_position)) ASC,
			   COALESCE(bm.series_reading_order_position, bm.series_position) ASC,
			   p.post_title ASC",
			$term_id
		) ) );
	}

	/**
	 * Return books in this series ordered by series_position only (browse order).
	 *
	 * Used by MBS_Archive_Reader for 'order' display mode.
	 * Must NOT use series_reading_order_position.
	 *
	 * @param int $term_id mbs_series 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_series' 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.series_position) ASC,
			   bm.series_position ASC,
			   p.post_title ASC",
			$term_id
		) ) );
	}

	/**
	 * Whether any book in this series has a series_reading_order_position set.
	 *
	 * Overrides base class to check mbs_book_meta instead of term_meta flag.
	 *
	 * @param int $term_id mbs_series term ID.
	 * @return bool
	 * @since 4.34.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_series' 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.series_reading_order_position IS NOT NULL
			 LIMIT 1",
			$term_id
		) );
	}

	/**
	 * Persist reading order positions to mbs_book_meta.
	 *
	 * Maps submitted ordinal positions (1, 2, 3...) to
	 * mbs_book_meta.series_reading_order_position as DECIMAL.
	 * Books in the series absent from the submitted list have their
	 * position NULLed. Does NOT call parent::save_reading_order().
	 *
	 * @param int   $term_id         mbs_series 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 {
		global $wpdb;
		$bm_table = $wpdb->prefix . 'mbs_book_meta';

		// V3-B-009: Filter submitted IDs to only books that actually belong to
		// this series. Prevents crafted AJAX POSTs from injecting positions for
		// books from other series.
		$member_ids = get_objects_in_term( $term_id, MBS_TAX_SERIES );
		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 series 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( 'series_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( 'series_reading_order_position' => (float) $position ),
				array( 'post_id' => (int) $post_id ),
				array( '%f' ), array( '%d' )
			);
			$position++;
		}

		// Safety net (parity with MBS_Reading_Order_DAO_Base): append any series
		// member omitted from the submission so every book carries a real
		// position. A stale form cannot silently leave a book without a place in
		// the order (which would otherwise sort last via ISNULL).
		$submitted_ints = array_map( 'intval', $ordered_post_ids );
		foreach ( $all_ids as $post_id ) {
			if ( in_array( (int) $post_id, $submitted_ints, true ) ) {
				continue;
			}
			// 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( 'series_reading_order_position' => (float) $position ),
				array( 'post_id' => (int) $post_id ),
				array( '%f' ), array( '%d' )
			);
			$position++;
		}
	}

	// -------------------------------------------------------------------------
	// Reading-order mutation overrides (F-001, Session 197)
	//
	// The base class append_book()/prune_book() read the mbs_reading_order
	// junction table via get_stored_order(), but the series DAO stores order
	// positions in mbs_book_meta.series_reading_order_position (Sprint 4.34) and
	// never writes the term_meta init flag the base relies on. Inherited, both
	// methods were silent no-ops for every series. These overrides operate on
	// mbs_book_meta directly.
	// -------------------------------------------------------------------------

	/**
	 * Append a book to the end of this series' persisted reading order.
	 *
	 * No-op when the series has no persisted order yet (R1: auto-append must
	 * not silently initialise an order). Idempotent: a book that already
	 * carries a position keeps it.
	 *
	 * @param int $term_id mbs_series term ID.
	 * @param int $book_id Book post ID to append.
	 * @return void
	 * @since 1.3.9
	 */
	public static function append_book( int $term_id, int $book_id ): void {
		if ( ! static::has_persisted_order( $term_id ) ) {
			return; // Never persisted -- leave uninitialised.
		}

		// Idempotent: a book already in the order keeps its position.
		$existing = MBS_Book_Meta_DAO::get( $book_id, 'mbs_series_reading_order_position', null );
		if ( null !== $existing && '' !== $existing ) {
			return;
		}

		global $wpdb;
		$bm_table = $wpdb->prefix . 'mbs_book_meta';

		// Next position = current max position within this series + 1.
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- DAO layer: {$bm_table} is $wpdb->prefix . fixed name; term_id is bound via %d
		$max_pos = (float) $wpdb->get_var( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- DAO layer -- direct DB access is intentional per MBS architecture
			"SELECT MAX(bm.series_reading_order_position)
			 FROM {$bm_table} bm
			 INNER JOIN {$wpdb->term_relationships} tr ON tr.object_id = bm.post_id
			 INNER JOIN {$wpdb->term_taxonomy} tt
			   ON tt.term_taxonomy_id = tr.term_taxonomy_id
			   AND tt.taxonomy = 'mbs_series' AND tt.term_id = %d",
			$term_id
		) );
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		MBS_Book_Meta_DAO::upsert( $book_id, 'mbs_series_reading_order_position', $max_pos + 1 );
	}

	/**
	 * Remove a book from this series' persisted reading order.
	 *
	 * Mirrors MBS_Collection_Reading_Order_DAO::prune_book() two-step removal:
	 *   1. Clear series_reading_order_position in mbs_book_meta.
	 *   2. Detach the book from the series term so get_ordered_book_ids()
	 *      (which INNER JOINs term_relationships) no longer returns it.
	 *
	 * No-op when the series has no persisted reading order yet.
	 *
	 * @param int $term_id mbs_series term ID.
	 * @param int $book_id Book post ID to remove.
	 * @return void
	 * @since 1.3.9
	 */
	public static function prune_book( int $term_id, int $book_id ): void {
		if ( ! static::has_persisted_order( $term_id ) ) {
			return;
		}
		MBS_Book_Meta_DAO::upsert( $book_id, 'mbs_series_reading_order_position', '' );
		wp_remove_object_terms( $book_id, $term_id, static::taxonomy() );
	}

	// -------------------------------------------------------------------------
	// Backward-compat aliases (all existing callers remain unchanged)
	// -------------------------------------------------------------------------

	/**
	 * Alias for get_books_in_term(). Preserved so all existing call sites
	 * (class-mbs-series-sync.php, tests) continue to compile without changes.
	 *
	 * @param int $term_id mbs_series term ID.
	 * @return int[]
	 * @since 1.0.0
	 */
	public static function get_books_in_series( int $term_id ): array {
		return static::get_books_in_term( $term_id );
	}

	/**
	 * Alias for prune_from_all(). Preserved so class-mbs-series-sync.php
	 * continues to compile without changes.
	 *
	 * @param int $book_id Book post ID being removed.
	 * @return void
	 * @since 1.0.0
	 */
	public static function prune_from_all_series( int $book_id ): void {
		static::prune_from_all( $book_id );
	}

	// -------------------------------------------------------------------------
	// Series-specific utilities
	// -------------------------------------------------------------------------

	/**
	 * Resolve a term_taxonomy_id to its term_id.
	 *
	 * Used by MBS_Series_Sync when diffing new vs. old tt_id arrays passed
	 * to the set_object_terms hook. Raw SQL lives here (data-layer rule).
	 *
	 * @param int $tt_id term_taxonomy_id.
	 * @return int term_id, or 0 if not found.
	 * @since 1.0.0
	 */
	public static function term_id_from_tt_id( int $tt_id ): int {
		if ( $tt_id <= 0 ) {
			return 0;
		}
		global $wpdb;
		$term_id = $wpdb->get_var( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			"SELECT term_id FROM {$wpdb->term_taxonomy} WHERE term_taxonomy_id = %d",
			$tt_id
		) );
		return (int) $term_id;
	}
}
