<?php
/**
 * File Path: includes/data/class-mbs-book-meta-dao.php
 *
 * Book Meta DAO
 *
 * Single-row DAO for the mbs_book_meta table, which stores scalar
 * postmeta keys that are accessed via SQL WHERE clause or need a fast
 * single-column lookup: mbs_featured, mbs_lifecycle_state, mbs_coming_soon,
 * mbs_isbn13, mbs_word_count, mbs_series_position (Sprint 163-C), and
 * mbs_title_sort_key (Sprint 163-C).
 *
 * All public methods accept the canonical postmeta key name (e.g. 'mbs_featured')
 * and map it to the table column internally. Unknown keys are silently ignored.
 *
 * Write rule: WRITE-PROTOCOL.md §A + §B.
 *
 * @package MyBookShowroom
 * @since   1.7.0
 */

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

/**
 * DAO for mbs_book_meta.
 *
 * @since 1.7.0
 */
// 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_Meta_DAO { // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedClassFound -- mbs_/MBS_ prefix is correct; scanner not configured with plugin prefix

	/**
	 * Maps postmeta key name => mbs_book_meta column name.
	 *
	 * All column names are whitelisted here; they are safe to interpolate
	 * into SQL strings without further sanitisation.
	 *
	 * @var array<string,string>
	 */
	private const KEY_MAP = array(
		'mbs_isbn13'          => 'isbn13',
		'mbs_lifecycle_state' => 'lifecycle_state',
		'mbs_featured'        => 'featured',
		'mbs_coming_soon'     => 'coming_soon',
		'mbs_word_count'      => 'word_count',
		'mbs_series_position' => 'series_position',
		'mbs_title_sort_key'               => 'title_sort_key',
		'mbs_series_reading_order_position' => 'series_reading_order_position',
		'mbs_series_reading_order_hidden'   => 'series_reading_order_hidden',
		'mbs_universe_reading_order_hidden'    => 'universe_reading_order_hidden',
		'mbs_universe_reading_order_position'  => 'universe_reading_order_position',
	);

	/**
	 * Normalise a raw postmeta value for storage in the given column.
	 *
	 * Returns null when the value should be stored as SQL NULL (i.e. the
	 * field is "not set"). The caller must branch on === null before calling
	 * prepare() to avoid passing null through a typed placeholder.
	 *
	 * @param string $column    Table column name (from KEY_MAP).
	 * @param mixed  $raw_value The raw value as stored in postmeta.
	 * @return int|string|null  Normalised value, or null.
	 */
	private static function normalise( string $column, $raw_value ) {
		$v = (string) $raw_value;
		switch ( $column ) {
			case 'featured':
			case 'coming_soon':
				return ( '1' === $v ) ? 1 : null;

			case 'word_count':
				$int = absint( $v );
				return ( $int > 0 ) ? $int : null;

			case 'series_position':
				$f = (float) $v;
				return ( $f > 0.0 ) ? (string) number_format( $f, 2, '.', '' ) : null;

			case 'title_sort_key':
				// NOT NULL DEFAULT '' — never stored as SQL NULL.
				return $v; // empty string is valid

			case 'series_reading_order_position':
				// NULL = not set; 0.0 is a valid position.
				if ( '' === trim( $v ) ) {
					return null;
				}
				$f = max( 0.0, (float) $v );
				return (string) number_format( $f, 2, '.', '' );

			case 'universe_reading_order_position':
				// NULL = not set; 0.0 is a valid position.
				if ( '' === trim( $v ) ) {
					return null;
				}
				$f = max( 0.0, (float) $v );
				return (string) number_format( $f, 2, '.', '' );

			case 'series_reading_order_hidden':
			case 'universe_reading_order_hidden':
				// Always 0 or 1 — never SQL NULL.
				return ( 1 === (int) $v || '1' === $v ) ? 1 : 0;

			case 'isbn13':
			case 'lifecycle_state':
			default:
				return ( '' !== $v ) ? $v : null;
		}
	}

	// -------------------------------------------------------------------------
	// Generic read / write
	// -------------------------------------------------------------------------

	/**
	 * Upsert a single field for a book.
	 *
	 * On first write for a book, inserts the row. On subsequent writes,
	 * updates only the one column. NULL normalised values write SQL NULL to
	 * the column (i.e. clearing the field).
	 *
	 * Called after every update_post_meta() for the five managed keys.
	 *
	 * @param int    $post_id  Book post ID.
	 * @param string $meta_key Canonical postmeta key (e.g. 'mbs_featured').
	 * @param mixed  $value    Raw value to store (same format as postmeta).
	 * @return void
	 */
	public static function upsert( int $post_id, string $meta_key, $value ): void {
		$column = self::KEY_MAP[ $meta_key ] ?? null;
		if ( null === $column ) {
			return;
		}

		global $wpdb;
		$table  = $wpdb->prefix . 'mbs_book_meta';
		$normal = self::normalise( $column, $value );

		if ( null === $normal ) {
			// Null path: insert skeleton row then SET column = NULL on conflict.
			// Cannot pass null through prepare() placeholders.
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
				$wpdb->prepare(
					"INSERT INTO {$table} (post_id) VALUES (%d)
					 ON DUPLICATE KEY UPDATE {$column} = NULL",
					$post_id
				)
			);
		} else {
			$placeholder = is_int( $normal ) ? '%d' : '%s';
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- safe: variadic ...$params / ...$row_ids correctly matches prepared placeholders; PHPCS cannot statically verify spread arg count
			$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- 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 spread correctly matches prepared placeholders; PHPCS cannot statically verify spread arg count
					"INSERT INTO {$table} (post_id, {$column}) VALUES (%d, {$placeholder})
					 ON DUPLICATE KEY UPDATE {$column} = VALUES({$column})",
					$post_id,
					$normal
				)
			);
		}

		// V2-A-003: invalidate object-cache entry so next get() reflects the write.
		wp_cache_delete( "mbs_bm_{$post_id}_{$meta_key}", 'mbs_book_meta' );
	}

	/**
	 * Retrieve a single field for a book.
	 *
	 * Returns $default when the book has no row in mbs_book_meta or when
	 * the column is NULL.
	 *
	 * @param int    $post_id  Book post ID.
	 * @param string $meta_key Canonical postmeta key (e.g. 'mbs_lifecycle_state').
	 * @param mixed  $default  Value to return when the field is absent or NULL.
	 * @return mixed Column value, or $default.
	 */
	public static function get( int $post_id, string $meta_key, $default = null ) {
		$column = self::KEY_MAP[ $meta_key ] ?? null;
		if ( null === $column ) {
			return $default;
		}

		// V2-A-003: object-cache layer -- avoids repeated DB hits on archive pages.
		// V3-E-012: wrap raw DB value in array sentinel so cached null is
		// distinguishable from a cache miss (wp_cache_get returns false on miss).
		$cache_key   = "mbs_bm_{$post_id}_{$meta_key}";
		$cache_group = 'mbs_book_meta';
		$cached      = wp_cache_get( $cache_key, $cache_group );
		if ( false !== $cached ) {
			$raw = $cached['v']; // unwrap sentinel
			return null !== $raw ? $raw : $default;
		}

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

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$result = $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
			$wpdb->prepare(
				"SELECT {$column} FROM {$table} WHERE post_id = %d LIMIT 1",
				$post_id
			)
		);

		$raw_db_value = ( null !== $result ) ? $result : null;
		wp_cache_set( $cache_key, array( 'v' => $raw_db_value ), $cache_group );
		return null !== $raw_db_value ? $raw_db_value : $default;
	}

	// -------------------------------------------------------------------------
	// ID-list readers (replace meta_query in WP_Query / WP_Query args)
	// -------------------------------------------------------------------------

	/**
	 * Return post IDs of all books marked as featured.
	 *
	 * Replaces: WP_Query meta_query key=mbs_featured value=1.
	 *
	 * @return int[] Array of post IDs. Empty array when none.
	 * @since 1.7.0
	 */
	public static function get_featured_ids(): array {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_book_meta';

		// 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
			"SELECT post_id FROM {$table} WHERE featured = 1"
		);

		return array_map( 'intval', (array) $rows );
	}

	/**
	 * Return post IDs of all books in the coming-soon lifecycle state.
	 *
	 * Replaces: WP_Query meta_query key=mbs_coming_soon value=1.
	 *
	 * @return int[] Array of post IDs. Empty array when none.
	 * @since 1.7.0
	 */
	public static function get_coming_soon_ids(): array {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_book_meta';

		// 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
			"SELECT post_id FROM {$table} WHERE coming_soon = 1"
		);

		return array_map( 'intval', (array) $rows );
	}

	/**
	 * Return post IDs of all books with a given lifecycle state.
	 *
	 * Replaces: WP_Query meta_query key=mbs_lifecycle_state value=$state.
	 *
	 * @param string $state Lifecycle state value (e.g. 'preorder', 'coming_soon').
	 * @return int[] Array of post IDs. Empty array when none.
	 * @since 1.7.0
	 */
	public static function get_lifecycle_state_ids( string $state ): array {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_book_meta';

		$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 post_id FROM {$table} WHERE lifecycle_state = %s",
				$state
			)
		);

		return array_map( 'intval', (array) $rows );
	}

	/**
	 * Find the post ID of the book with a given ISBN-13.
	 *
	 * @param string $isbn ISBN-13 string (e.g. '9781234567890').
	 * @return int|null Post ID, or null if not found.
	 * @since 1.7.0
	 */
	public static function find_by_isbn( string $isbn ): ?int {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_book_meta';

		$result = $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			$wpdb->prepare(
				"SELECT post_id FROM {$table} WHERE isbn13 = %s LIMIT 1",
				$isbn
			)
		);

		return ( null !== $result ) ? (int) $result : null;
	}
	// -------------------------------------------------------------------------
	// title_sort_key ordered ID list
	// -------------------------------------------------------------------------

	/**
	 * Return all book post IDs ordered by title_sort_key.
	 *
	 * Used by MBS_Book_Query when orderby=title_sort to retrieve a pre-sorted
	 * list from mbs_book_meta.title_sort_key without a postmeta JOIN (Sprint 163-C).
	 *
	 * @param string $order 'ASC' or 'DESC'.
	 * @return int[] Post IDs in sort order.
	 * @since 1.7.0
	 */
	public static function get_ids_ordered_by_title_sort( string $order = 'ASC' ): array {
		global $wpdb;
		$table      = $wpdb->prefix . 'mbs_book_meta';
		$safe_order = ( 'ASC' === strtoupper( $order ) ) ? 'ASC' : 'DESC';
		// 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
			"SELECT post_id FROM {$table}
			 WHERE title_sort_key != ''
			 ORDER BY title_sort_key {$safe_order}"
		);
		return array_map( 'intval', (array) $rows );
	}

	// -------------------------------------------------------------------------
	// Sprint RO-Display typed write helpers (Group 5)
	// -------------------------------------------------------------------------

	/**
	 * Set the suggested reading-order position for a book in its series.
	 *
	 * Pass null to clear the position (falls back to series_position in queries).
	 *
	 * @param int        $post_id Book post ID.
	 * @param float|null $pos     Position value (0.0 = first; null = unset).
	 * @return void
	 */
	public static function set_series_reading_order_position( int $post_id, ?float $pos ): void {
		self::upsert( $post_id, 'mbs_series_reading_order_position', null === $pos ? '' : (string) $pos );
	}

	/**
	 * Set the series reading-order hidden flag for a book.
	 *
	 * @param int $post_id Book post ID.
	 * @param int $val     1 = hidden; 0 = visible.
	 * @return void
	 */
	public static function set_series_reading_order_hidden( int $post_id, int $val ): void {
		self::upsert( $post_id, 'mbs_series_reading_order_hidden', $val ? '1' : '0' );
	}

	/**
	 * Set the universe reading-order hidden flag for a book.
	 *
	 * @param int $post_id Book post ID.
	 * @param int $val     1 = hidden; 0 = visible.
	 * @return void
	 */
	public static function set_universe_reading_order_hidden( int $post_id, int $val ): void {
		self::upsert( $post_id, 'mbs_universe_reading_order_hidden', $val ? '1' : '0' );
	}

	/**
	 * Set the universe reading-order position for a book (UX-RO-002).
	 *
	 * Pass null to clear the position (falls back to post_title in queries).
	 *
	 * @param int        $post_id Book post ID.
	 * @param float|null $pos     Position value (0.0 = first; null = unset).
	 * @return void
	 */
	public static function set_universe_reading_order_position( int $post_id, ?float $pos ): void {
		self::upsert( $post_id, 'mbs_universe_reading_order_position', null === $pos ? '' : (string) $pos );
	}

	// -------------------------------------------------------------------------
	// Sprint RO-Display typed read helpers (Group 8)
	// -------------------------------------------------------------------------

	/**
	 * Get the series reading order position for a single book.
	 *
	 * Delegates to the generic get() method which reads from
	 * mbs_book_meta.series_reading_order_position.
	 *
	 * @param int $post_id Book post ID.
	 * @return float|null Position, or null if not set.
	 */
	public static function get_series_ro_position( int $post_id ): ?float {
		$val = self::get( $post_id, 'mbs_series_reading_order_position', null );
		return ( null !== $val && '' !== (string) $val ) ? (float) $val : null;
	}

	/**
	 * Get the series reading-order hidden flag for a book.
	 *
	 * @param int $post_id Book post ID.
	 * @return int 1 = hidden; 0 = visible.
	 */
	public static function get_series_reading_order_hidden( int $post_id ): int {
		return (int) self::get( $post_id, 'mbs_series_reading_order_hidden', 0 );
	}

	/**
	 * Get the universe reading-order hidden flag for a book.
	 *
	 * @param int $post_id Book post ID.
	 * @return int 1 = hidden; 0 = visible.
	 */
	public static function get_universe_reading_order_hidden( int $post_id ): int {
		return (int) self::get( $post_id, 'mbs_universe_reading_order_hidden', 0 );
	}

	/**
	 * Get the universe reading-order position for a book (UX-RO-002).
	 *
	 * Returns null when no position has been set.
	 *
	 * @param int $post_id Book post ID.
	 * @return float|null Position value, or null if unset.
	 */
	public static function get_universe_reading_order_position( int $post_id ): ?float {
		$val = self::get( $post_id, 'mbs_universe_reading_order_position', null );
		return ( null !== $val && '' !== (string) $val ) ? (float) $val : null;
	}

	// -------------------------------------------------------------------------
	// Delete
	// -------------------------------------------------------------------------

	/**
	 * Delete the mbs_book_meta row for a book.
	 *
	 * Called from the before_delete_post hook so no orphan rows remain after
	 * book deletion.
	 *
	 * @param int $post_id Book post ID.
	 * @return void
	 */
	public static function delete( int $post_id ): void {
		global $wpdb;
		$wpdb->delete( $wpdb->prefix . 'mbs_book_meta', array( 'post_id' => $post_id ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
		// Invalidate object-cache entries for every managed key so subsequent get() calls
		// do not return stale cached values after the row has been deleted.
		foreach ( array_keys( self::KEY_MAP ) as $meta_key ) {
			wp_cache_delete( "mbs_bm_{$post_id}_{$meta_key}", 'mbs_book_meta' );
		}
	}

}
