<?php
/**
 * File Path: includes/import/class-mbs-book-hydrator.php
 *
 * MBS Book Hydrator
 *
 * Accepts one MBS Book Object array, sanitizes all fields, and writes the
 * complete book to the database via the appropriate DAOs. Single
 * responsibility: one book, complete write, via DAOs only (Rule 1 / Sprint
 * Plan §3.1). No file I/O. No ZIP or archive handling.
 *
 * Key constraints (Sprint Plan §Phase 3):
 *
 *   (1) Duplicate detection -- build a flattened check-array before calling
 *       MBS_Duplicate_Detector::find_duplicate(). The ISBN lives in
 *       format_rows[*].isbn13, not at the top level. Skip wp_insert_post()
 *       if find_duplicate() returns non-null (IEF-C-001).
 *
 *   (2) Sanitization -- post_content via wp_kses_post(); all other text
 *       meta via sanitize_text_field() unless the field stores rich text
 *       (IEF-B-013).
 *
 *   (3) Dual-write -- five post_meta keys are managed by MBS_Book_Meta_DAO
 *       and require both update_post_meta() AND MBS_Book_Meta_DAO::upsert()
 *       on every import: mbs_word_count, mbs_lifecycle_state, mbs_featured,
 *       mbs_coming_soon, mbs_series_position (IEF-B-006 / IEF-C-004).
 *
 *   (4) Endorsements -- call MBS_Endorsement_DAO::insert() (the new
 *       single-row method). Do NOT call save_from_post() (IEF-B-007).
 *
 *   (5) Contributors -- MBS_Contributor_Book_DAO::insert() requires a
 *       term_id, not a name string. Resolve contributor_name to an
 *       mbs_contributor taxonomy term via term_exists() / wp_insert_term()
 *       before calling insert() (IEF-C-003).
 *
 *   (6) Collection positions -- call
 *       MBS_Book_Collection_Position_DAO::upsert($post_id, $term_id, $position)
 *       (IEF-B-015 / IEF-C-005). Entries with position <= 0 are skipped and
 *       logged to the error result array rather than silently passed to upsert().
 *
 *   (7) Phase 3 scope -- does NOT write format-row media attachment IDs
 *       (cover_image_id, spinning_video_id, audio_sample_id). Those columns
 *       are populated in Phase 4 after MBS_Import_Media_Sideloader is
 *       available (IEF-B-005).
 *
 * @package    My_Book_Showroom
 * @since      1.0.0
 */

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

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

	/**
	 * Post_meta keys that are managed by MBS_Book_Meta_DAO and require a
	 * dual-write: update_post_meta() AND MBS_Book_Meta_DAO::upsert().
	 *
	 * Source: MBS_Book_Meta_DAO::KEY_MAP intersected with §4 post_meta keys.
	 * NOTE: mbs_isbn13 is in KEY_MAP but lives in format_rows (not postmeta);
	 *       it is intentionally absent from this list (IEF-B-006 / IEF-C-004).
	 */
	const DUAL_WRITE_KEYS = array(
		'mbs_word_count',
		'mbs_lifecycle_state',
		'mbs_featured',
		'mbs_coming_soon',
		'mbs_series_position',
	);

	/**
	 * Post_meta keys whose values are stored as integers.
	 */
	const INT_META_KEYS = array(
		'mbs_word_count',
		'mbs_illustration_count',
	);

	/**
	 * Post_meta keys whose values are stored as booleans (0/1).
	 */
	const BOOL_META_KEYS = array(
		'mbs_coming_soon',
		'mbs_featured',
		'mbs_show_word_count',
		'mbs_enable_reader_reviews',
	);

	/**
	 * Post_meta keys that hold URLs and must be sanitized with esc_url_raw()
	 * to reject javascript: scheme URIs and other unsafe values (V2-B-001).
	 */
	const URL_META_KEYS = array(
		'mbs_goodreads_url',
		'mbs_storygraph_url',
		'mbs_bookbub_url',
		'mbs_publisher_url',
	);

	/**
	 * Post_meta keys that hold multi-line text and must use
	 * sanitize_textarea_field() to preserve newlines (V2-B-002).
	 */
	const TEXTAREA_META_KEYS = array(
		'mbs_teaser',
		'mbs_synopsis',
		'mbs_look_inside_text',
		'mbs_toc',
	);

	/**
	 * Hydrate one MBS Book Object into the database.
	 *
	 * @param array $book_object  MBS Book Object (Sprint Plan §4 shape).
	 * @param array $options {
	 *   Optional behavioral flags.
	 *   @type bool   $skip_duplicate_check  Skip duplicate detection. Default false.
	 *   @type string $post_status           Override post_status. Default: value in $book_object.
	 * }
	 * @return int|WP_Error New post ID on success, WP_Error on failure.
	 *                      WP_Error code 'duplicate' is returned when an
	 *                      existing book matches via MBS_Duplicate_Detector.
	 */
	public static function hydrate( array $book_object, array $options = array() ): int|\WP_Error {
		$errors = array(); // Accumulate non-fatal warnings.

		// —— Empty-object guard ——————————————————————————————————————————————
		if ( empty( $book_object ) || '' === trim( $book_object['post_title'] ?? '' ) ) {
			return new \WP_Error(
				'empty_book',
				__( 'Book object is empty or missing a title.', 'my-book-showroom' )
			);
		}

		// ── Duplicate detection (IEF-C-001) ──────────────────────────────────
		$skip_dup = ! empty( $options['skip_duplicate_check'] );
		if ( ! $skip_dup ) {
			$check = array(
				'title'  => $book_object['post_title'] ?? '',
				'author' => $book_object['taxonomies']['mbs_author'][0] ?? '',
				'isbn13' => '',
			);
			foreach ( $book_object['format_rows'] ?? array() as $fr ) {
				if ( ! empty( $fr['isbn13'] ) ) {
					$check['isbn13'] = $fr['isbn13'];
					break;
				}
			}
			$existing_id = MBS_Duplicate_Detector::find_duplicate( $check );
			if ( $existing_id ) {
				return new \WP_Error(
					'duplicate',
					sprintf(
						/* translators: 1: book title, 2: existing post ID */
						__( 'Duplicate book skipped: "%1$s" matches existing post ID %2$d.', 'my-book-showroom' ),
						esc_html( $book_object['post_title'] ?? '' ),
						$existing_id
					),
					array( 'existing_id' => $existing_id )
				);
			}
		}

		// ── Insert the post ───────────────────────────────────────────────────
		$post_status = isset( $options['post_status'] )
			? sanitize_key( $options['post_status'] )
			: sanitize_key( $book_object['post_status'] ?? 'draft' );

		$post_id = wp_insert_post(
			array(
				'post_title'   => sanitize_text_field( $book_object['post_title'] ?? '' ),
				'post_content' => wp_kses_post( $book_object['post_content'] ?? '' ),
				'post_status'  => $post_status,
				'post_type'    => MBS_POST_TYPE,
			),
			true // Return WP_Error on failure.
		);

		if ( is_wp_error( $post_id ) ) {
			return $post_id;
		}

		// ── Post meta ─────────────────────────────────────────────────────────
		self::write_post_meta( $post_id, $book_object['post_meta'] ?? array(), $errors );

		// ── Taxonomies ────────────────────────────────────────────────────────
		self::write_taxonomies( $post_id, $book_object['taxonomies'] ?? array() );

		// ── Collection positions (IEF-B-015 / IEF-C-005) ─────────────────────
		self::write_collection_positions( $post_id, $book_object['collection_positions'] ?? array(), $errors );

		// ── Classifications ───────────────────────────────────────────────────
		self::write_classifications( $post_id, $book_object['classifications'] ?? array() );

		// ── Format rows (Phase 3: no media attachment IDs — IEF-B-005) ───────
		self::write_format_rows( $post_id, $book_object['format_rows'] ?? array() );

		// ── Book-level buy links ──────────────────────────────────────────────
		self::write_book_buy_links( $post_id, $book_object['book_buy_links'] ?? array() );

		// ── Endorsements (IEF-B-007: insert(), NOT save_from_post()) ─────────
		self::write_endorsements( $post_id, $book_object['endorsements'] ?? array() );

		// ── Awards ────────────────────────────────────────────────────────────
		if ( ! empty( $book_object['awards'] ) ) {
			MBS_Book_Awards_DAO::save( $post_id, $book_object['awards'] );
		}

		// ── Contributors (IEF-C-003: resolve name → term_id first) ───────────
		self::write_contributors( $post_id, $book_object['contributors'] ?? array(), $errors );

		// ── Related products ──────────────────────────────────────────────────
		self::write_related_products( $post_id, $book_object['related_products'] ?? array() );
		self::write_section_order( $post_id, $book_object['section_order'] ?? array() );

		// Non-fatal warnings are attached as post_meta for the admin result panel.
		// The caller may inspect them via get_post_meta( $post_id, '_mbs_import_warnings', true ).
		if ( ! empty( $errors ) ) {
			update_post_meta( $post_id, '_mbs_import_warnings', $errors );
		}

		return $post_id;
	}

	// -------------------------------------------------------------------------
	// Private section writers
	// -------------------------------------------------------------------------

	/**
	 * Write post_meta fields.
	 *
	 * Dual-writes the five DUAL_WRITE_KEYS via update_post_meta() AND
	 * MBS_Book_Meta_DAO::upsert(). All other keys use update_post_meta() only.
	 *
	 * @param int   $post_id
	 * @param array $meta    post_meta section of the MBS Book Object.
	 * @param array $errors  Accumulator for non-fatal messages (passed by ref).
	 */
	private static function write_post_meta( int $post_id, array $meta, array &$errors ): void {
		$all_keys = array_merge(
			self::INT_META_KEYS,
			self::BOOL_META_KEYS,
			array(
				'mbs_teaser',
				'mbs_tagline',
				'mbs_synopsis',
				'mbs_publisher',
				'mbs_publisher_url',
				'mbs_pub_date',
				'mbs_lifecycle_state',
				'mbs_series_position',
				'mbs_look_inside_text',
				'mbs_toc',
				'mbs_lccn',
				'mbs_audience_code',
				'mbs_age_range_from',
				'mbs_age_range_to',
				'mbs_edition',
				'mbs_goodreads_url',
				'mbs_storygraph_url',
				'mbs_bookbub_url',
				'mbs_publication_time',
				'mbs_grade_range_from',
				'mbs_grade_range_to',
				'mbs_copyright_year',
				'_mbs_admin_notes',
				'mbs_language',
				'mbs_share_text',
			)
		);

		foreach ( $all_keys as $key ) {
			if ( ! array_key_exists( $key, $meta ) ) {
				continue; // Absent keys are skipped gracefully.
			}

			$raw = $meta[ $key ];

			// Cast to the correct type before writing.
			if ( in_array( $key, self::INT_META_KEYS, true ) ) {
				$value = (int) $raw;
			} elseif ( in_array( $key, self::BOOL_META_KEYS, true ) ) {
				$value = (int) (bool) $raw;
			} elseif ( in_array( $key, self::URL_META_KEYS, true ) ) {
				$value = esc_url_raw( (string) $raw ); // V2-B-001: reject javascript: URIs.
			} elseif ( in_array( $key, self::TEXTAREA_META_KEYS, true ) ) {
				$value = sanitize_textarea_field( (string) $raw ); // V2-B-002: preserve newlines.
			} else {
				$value = sanitize_text_field( (string) $raw );
			}

			update_post_meta( $post_id, $key, $value );

			// Dual-write to MBS_Book_Meta_DAO for indexed keys (IEF-B-006).
			if ( in_array( $key, self::DUAL_WRITE_KEYS, true ) ) {
				MBS_Book_Meta_DAO::upsert( $post_id, $key, $value );
			}
		}
	}

	/**
	 * Write taxonomy terms for all MBS taxonomies.
	 *
	 * Creates terms that do not already exist. Uses wp_set_object_terms() to
	 * replace any existing term associations on this post.
	 *
	 * @param int   $post_id
	 * @param array $taxonomies Taxonomy section: array<string, string[]>.
	 */
	private static function write_taxonomies( int $post_id, array $taxonomies ): void {
		$supported = array(
			'mbs_author',
			'mbs_genre',
			'mbs_series',
			'mbs_tag',
			'mbs_publisher',
			'mbs_imprint',
			'mbs_universe',
			'mbs_collection',
			'mbs_theme',
			'mbs_content_warning',
		);

		foreach ( $supported as $tax ) {
			if ( ! isset( $taxonomies[ $tax ] ) || ! is_array( $taxonomies[ $tax ] ) ) {
				continue;
			}
			$names = array_filter( array_map( 'sanitize_text_field', $taxonomies[ $tax ] ) );
			if ( empty( $names ) ) {
				continue;
			}
			// wp_set_object_terms() accepts term names and creates them if absent.
			wp_set_object_terms( $post_id, array_values( $names ), $tax );
		}
	}

	/**
	 * Write collection positions.
	 *
	 * Resolves each collection name to its mbs_collection term_id, then calls
	 * MBS_Book_Collection_Position_DAO::upsert(). Entries with position <= 0
	 * are logged to $errors rather than passed to upsert() (IEF-C-005).
	 *
	 * @param int   $post_id
	 * @param array $positions  Map of collection name => position integer.
	 * @param array $errors     Accumulator (passed by ref).
	 */
	private static function write_collection_positions( int $post_id, array $positions, array &$errors ): void {
		foreach ( $positions as $collection_name => $position ) {
			$position = (int) $position;
			if ( $position <= 0 ) {
				$errors[] = sprintf(
					'Collection position skipped: "%s" has position %d (must be > 0).',
					esc_html( (string) $collection_name ),
					$position
				);
				continue;
			}

			$term = get_term_by( 'name', (string) $collection_name, 'mbs_collection' );
			if ( ! $term instanceof \WP_Term ) {
				// Term was not created by write_taxonomies() -- skip silently.
				// (This can happen if mbs_collection was absent from taxonomies[].)
				$errors[] = sprintf(
					'Collection position skipped: term "%s" not found in mbs_collection.',
					esc_html( (string) $collection_name )
				);
				continue;
			}

			MBS_Book_Collection_Position_DAO::upsert( $post_id, (int) $term->term_id, $position );
		}
	}

	/**
	 * Write classification codes for all supported schemes.
	 *
	 * @param int   $post_id
	 * @param array $classifications  Map of scheme => string[].
	 */
	private static function write_classifications( int $post_id, array $classifications ): void {
		$supported_schemes = array( 'bisac', 'thema', 'bic', 'bowker', 'illustration' );
		foreach ( $supported_schemes as $scheme ) {
			if ( empty( $classifications[ $scheme ] ) || ! is_array( $classifications[ $scheme ] ) ) {
				continue;
			}
			// Sanitize each code then pass the whole array to save_book_codes().
			// save_book_codes() deletes existing rows for this scheme before inserting,
			// ensuring a clean replace on import.
			$codes = array_values( array_filter(
				array_map( 'sanitize_text_field', $classifications[ $scheme ] )
			) );
			if ( ! empty( $codes ) ) {
				MBS_Classification_DAO::save_book_codes( $post_id, $scheme, $codes );
			}
		}
	}

	/**
	 * Write format rows and their nested per-format buy links.
	 *
	 * Phase 3 scope: media attachment ID columns (cover_image_id,
	 * spinning_video_id, audio_sample_id) are NOT written here -- they are
	 * populated in Phase 4 by MBS_Import_Media_Sideloader (IEF-B-005).
	 *
	 * @param int   $post_id
	 * @param array $format_rows
	 */
	private static function write_format_rows( int $post_id, array $format_rows ): void {
		foreach ( $format_rows as $idx => $fr ) {
			$buy_links = $fr['buy_links'] ?? array();

			// Remove keys that Phase 3 does not write to avoid MBS_Format_Row_DAO
			// overwriting media columns with null/empty values.
			// Phase 4 sets cover_image_id, spinning_video_id, audio_sample_id.
			// post_id is passed as first arg to save(); do not include in $data.
			// save() sets $data['post_id'] = $post_id internally on insert.
			$row_data = array(
				'format'         => sanitize_text_field( $fr['format'] ?? '' ),
				'format_label'   => sanitize_text_field( $fr['format_label'] ?? '' ),
				'isbn13'         => sanitize_text_field( $fr['isbn13'] ?? '' ),
				'asin'           => sanitize_text_field( $fr['asin'] ?? '' ),
				'price'          => sanitize_text_field( $fr['price'] ?? '' ),
				'page_length'    => (int) ( $fr['page_length'] ?? 0 ),
				'edition_number' => (int) ( $fr['edition_number'] ?? 0 ),
				'edition_type'   => sanitize_text_field( $fr['edition_type'] ?? '' ),
				'pub_date'       => sanitize_text_field( $fr['pub_date'] ?? '' ),
				'sort_order'     => $idx,
				'price_type'     => sanitize_text_field( $fr['price_type']     ?? '' ),
				'currency_code'  => ( preg_match( '/^[A-Z]{3}$/', $fr['currency_code'] ?? '' ) ? $fr['currency_code'] : 'USD' ), // V2-D-040: ISO 4217.
				'supplier_name'  => sanitize_text_field( $fr['supplier_name']  ?? '' ),
				'discount_code'  => sanitize_text_field( $fr['discount_code']  ?? '' ),
				'on_sale_date'   => sanitize_text_field( $fr['on_sale_date']   ?? '' ),
				'returnability'  => sanitize_text_field( $fr['returnability']  ?? '' ),
				'narrator'       => sanitize_text_field( $fr['narrator']       ?? '' ),
				'duration_hh'    => (int) ( $fr['duration_hh'] ?? 0 ),
				'duration_mm'    => (int) ( $fr['duration_mm'] ?? 0 ),
			);

			$format_row_id = MBS_Format_Row_DAO::save( $post_id, $row_data );

			// Write per-format buy links if the row was saved successfully.
			if ( $format_row_id && ! empty( $buy_links ) ) {
				self::write_format_buy_links( $post_id, (int) $format_row_id, $buy_links );
			}
		}
	}

	/**
	 * Write per-format buy links.
	 *
	 * @param int   $post_id
	 * @param int   $format_row_id
	 * @param array $buy_links
	 */
	private static function write_format_buy_links( int $post_id, int $format_row_id, array $buy_links ): void {
		foreach ( $buy_links as $idx => $link ) {
			$data = array(
				'post_id'       => $post_id,
				'format_row_id' => $format_row_id,
				'retailer_key'  => sanitize_key( $link['retailer_key'] ?? '' ),
				'url'           => esc_url_raw( $link['url'] ?? '' ),
				'link_label'    => sanitize_text_field( $link['link_label'] ?? '' ),
				'sort_order'       => (int) ( $link['sort_order'] ?? $idx ),
				'locale_us'        => sanitize_text_field( $link['locale_us']        ?? '' ),
				'locale_uk'        => sanitize_text_field( $link['locale_uk']        ?? '' ),
				'locale_ca'        => sanitize_text_field( $link['locale_ca']        ?? '' ),
				'locale_au'        => sanitize_text_field( $link['locale_au']        ?? '' ),
				'locale_de'        => sanitize_text_field( $link['locale_de']        ?? '' ),
				'locale_jp'        => sanitize_text_field( $link['locale_jp']        ?? '' ),
				'link_type'        => sanitize_text_field( $link['link_type']        ?? 'buy' ),
				'is_direct_author' => (int) (bool) ( $link['is_direct_author']      ?? false ),
			);
			MBS_Buy_Link_DAO::save( $data );
		}
	}

	/**
	 * Write book-level buy links (format_row_id absent -- DA-001).
	 *
	 * Per DA-001 compliance, format_row_id must be completely absent from the
	 * $data array passed to MBS_Buy_Link_DAO::save() for book-level links.
	 *
	 * @param int   $post_id
	 * @param array $buy_links
	 */
	private static function write_book_buy_links( int $post_id, array $buy_links ): void {
		foreach ( $buy_links as $idx => $link ) {
			// DA-001: format_row_id intentionally absent from this array.
			$data = array(
				'post_id'          => $post_id,
				'retailer_key'     => sanitize_key( $link['retailer_key'] ?? '' ),
				'url'              => esc_url_raw( $link['url'] ?? '' ),
				'link_label'       => sanitize_text_field( $link['link_label'] ?? '' ),
				'is_direct_author' => (int) (bool) ( $link['is_direct_author'] ?? false ),
				'sort_order'       => (int) ( $link['sort_order'] ?? $idx ),
				'locale_us'        => sanitize_text_field( $link['locale_us'] ?? '' ),
				'locale_uk'        => sanitize_text_field( $link['locale_uk'] ?? '' ),
				'locale_ca'        => sanitize_text_field( $link['locale_ca'] ?? '' ),
				'locale_au'        => sanitize_text_field( $link['locale_au'] ?? '' ),
				'locale_de'        => sanitize_text_field( $link['locale_de'] ?? '' ),
				'locale_jp'        => sanitize_text_field( $link['locale_jp'] ?? '' ),
				'link_type'        => sanitize_text_field( $link['link_type'] ?? 'buy' ),
			);
			MBS_Buy_Link_DAO::save( $data );
		}
	}

	/**
	 * Write endorsements via MBS_Endorsement_DAO::insert().
	 *
	 * IEF-B-007: Must NOT call save_from_post(). That method is for admin
	 * form submissions and uses an update-in-place pattern that would
	 * soft-delete all existing endorsements if the submitted data is empty.
	 * insert() is the correct path for import writes.
	 *
	 * @param int   $post_id
	 * @param array $endorsements
	 */
	private static function write_endorsements( int $post_id, array $endorsements ): void {
		foreach ( $endorsements as $idx => $endorsement ) {
			$row = array(
				'quote_text'        => sanitize_textarea_field( $endorsement['quote_text'] ?? '' ),
				'attributed_to'     => sanitize_text_field( $endorsement['attributed_to'] ?? '' ),
				'attribution_title' => sanitize_text_field( $endorsement['attribution_title'] ?? '' ),
				'is_visible'        => (int) (bool) ( $endorsement['is_visible'] ?? true ),
				'sort_order'        => (int) ( $endorsement['sort_order'] ?? $idx ),
			);
			if ( '' === $row['quote_text'] ) {
				continue; // Skip blank endorsements silently.
			}
			MBS_Endorsement_DAO::insert( $post_id, $row );
		}
	}

	/**
	 * Write contributors.
	 *
	 * IEF-C-003: MBS_Contributor_Book_DAO::insert() requires a taxonomy
	 * term_id, not a name string. Resolve contributor_name to an
	 * mbs_contributor term via term_exists() / wp_insert_term() before calling
	 * insert(). Silently passing a name as term_id would insert a row with
	 * term_id = 0, breaking all contributor displays.
	 *
	 * @param int   $post_id
	 * @param array $contributors
	 * @param array $errors  Accumulator (passed by ref).
	 */
	private static function write_contributors( int $post_id, array $contributors, array &$errors ): void {
		foreach ( $contributors as $idx => $contributor ) {
			$name = sanitize_text_field( $contributor['contributor_name'] ?? '' );
			if ( '' === $name ) {
				continue;
			}

			// Resolve or create the mbs_contributor taxonomy term.
			$term_exists = term_exists( $name, MBS_TAX_CONTRIBUTOR );
			if ( $term_exists ) {
				$term_id = (int) ( $term_exists['term_id'] ?? 0 );
			} else {
				$inserted = wp_insert_term( $name, MBS_TAX_CONTRIBUTOR );
				if ( is_wp_error( $inserted ) ) {
					$errors[] = sprintf(
						'Contributor skipped: could not create term for "%s" — %s.',
						esc_html( $name ),
						$inserted->get_error_message()
					);
					continue;
				}
				$term_id = (int) $inserted['term_id'];
			}

			if ( $term_id <= 0 ) {
				$errors[] = sprintf(
					'Contributor skipped: term_id resolved to 0 for "%s".',
					esc_html( $name )
				);
				continue;
			}

			MBS_Contributor_Book_DAO::insert( array(
				'post_id'        => $post_id,
				'term_id'        => $term_id,
				'onix_role_code' => sanitize_text_field( $contributor['onix_role_code'] ?? 'A01' ),
				'role_label'     => sanitize_text_field( $contributor['role_label'] ?? '' ),
				'sort_order'     => (int) ( $contributor['sort_order'] ?? $idx ),
				'public'         => (int) (bool) ( $contributor['public'] ?? true ),
				'statement'      => sanitize_textarea_field( $contributor['statement'] ?? '' ),
			) );
		}
	}

	/**
	 * Write related products.
	 *
	 * Sprint Plan §4 IEF-C-002: import all entries regardless of tier
	 * (Q5 policy).
	 *
	 * @param int   $post_id
	 * @param array $related_products
	 */
	private static function write_related_products( int $post_id, array $related_products ): void {
		foreach ( $related_products as $idx => $product ) {
			$isbn = sanitize_text_field( $product['related_isbn'] ?? '' );
			$code = sanitize_text_field( $product['relation_code'] ?? '' );
			if ( '' === $isbn || '' === $code ) {
				continue;
			}
			MBS_Related_Products_DAO::upsert(
				$post_id,
				$code,
				$isbn,
				null,   // related_format -- not in §4 schema; null is safe.
				$idx    // sort_order.
			);
		}
	}
	/**
	 * Write per-book section order and visibility to the mbs_section_order table.
	 *
	 * Sorted by sort_order ascending before saving so the DB rows reflect the
	 * source display order exactly. After save_order() creates the rows,
	 * save_visibility() updates is_visible for each section.
	 *
	 * No-op when $section_rows is empty -- leaves the book falling through to
	 * the site's global defaults, which is safe for archives produced before
	 * the SO (section order) sprint.
	 *
	 * @param int   $post_id      Book post ID.
	 * @param array $section_rows Each item: ['key' => string, 'sort_order' => int, 'is_visible' => bool].
	 * @return void
	 */
	private static function write_section_order( int $post_id, array $section_rows ): void {
		if ( empty( $section_rows ) ) {
			return;
		}

		// Sort by sort_order so save_order() receives keys in display order.
		usort( $section_rows, static function( $a, $b ) {
			return (int) ( $a['sort_order'] ?? 0 ) <=> (int) ( $b['sort_order'] ?? 0 );
		} );

		$ordered_keys   = array_column( $section_rows, 'key' );
		$visibility_map = array();
		foreach ( $section_rows as $s ) {
			$visibility_map[ (string) $s['key'] ] = (bool) ( $s['is_visible'] ?? true );
		}

		MBS_Section_Order_DAO::save_order( $post_id, $ordered_keys );
		MBS_Section_Order_DAO::save_visibility( $post_id, $visibility_map );
	}

}
