<?php
/**
 * File Path: includes/import/class-mbs-catalog-xlsx-mapper.php
 *
 * XLSX Catalog Mapper
 *
 * Converts the multi-tab 2-D array from MBS_XLSX_Reader::get_sheets() into
 * an ordered array of MBS Book Objects ready for MBS_Book_Hydrator::hydrate().
 *
 * Rule 1: Pure array transformation. No DB, no file I/O.
 * Rule 2: Zero direct DB calls -- no wpdb, no get_post_meta, no DAO calls.
 * Rule 3: sanitize_book_object() applied to every book object before return.
 *         reading_orders and extra_memberships are fully sanitized (PLAN-R2-H-006).
 *
 * Column index reference (0-based, Tab 1 spec section 4.2):
 *   A=0  B=1  C=2  D=3  E=4  F=5  G=6  H=7  I=8  J=9  K=10 L=11 M=12
 *   N=13 O=14 P=15 Q=16 R=17 S=18 T=19 U=20 V=21 W=22 X=23 Y=24 Z=25
 *   AA=26 AB=27 AC=28 AD=29 AE=30 AF=31 AG=32 AH=33 AI=34 AJ=35 AK=36
 *   AL=37 AM=38 AN=39 AO=40 AP=41 AQ=42 AR=43 AS=44 AT=45 AU=46 AV=47 AW=48
 *   AX=49 AY=50 AZ=51 BA=52 BB=53 BC=54 BD=55 BE=56 BF=57 BG=58
 *   BH=59 BI=60 BJ=61 BK=62 BL=63 BM=64 BN=65 BO=66 BP=67 BQ=68 BR=69
 *
 * @package MyBookShowroom
 * @since   1.3.0
 */

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

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

	// -- Public API ------------------------------------------------------------------

	/**
	 * Map a multi-tab sheet array to MBS Book Objects.
	 *
	 * @param array $sheets Output of MBS_XLSX_Reader::get_sheets().
	 * @return array {
	 *   @type array[] $books  Ordered array of MBS Book Objects (sanitized).
	 *   @type string[] $errors Row-level error strings.
	 * }
	 * @since 1.3.0
	 */
	public static function map( array $sheets ): array {
		$errors = self::validate_structure( $sheets );
		if ( ! empty( $errors ) ) {
			return array( 'books' => array(), 'errors' => $errors );
		}

		[ 'books' => $books, 'errors' => $tab1_errors ] = self::index_tab1( $sheets['Books'] ?? array() );
		$errors = array_merge( $errors, $tab1_errors );
		$errors = array_merge( $errors, self::apply_tab2( $sheets['Formats']      ?? array(), $books ) );
		$errors = array_merge( $errors, self::apply_tab3( $sheets['Buy Links']    ?? array(), $books ) );
		$errors = array_merge( $errors, self::apply_tab4( $sheets['Endorsements'] ?? array(), $books ) );
		$errors = array_merge( $errors, self::apply_tab5( $sheets['Contributors'] ?? array(), $books ) );
		$errors = array_merge( $errors, self::apply_tab6( $sheets['Awards']       ?? array(), $books ) );
		$errors = array_merge( $errors, self::apply_tab7( $sheets['Memberships']          ?? array(), $books ) );
		$errors = array_merge( $errors, self::apply_tab8( $sheets['Discussion Questions']  ?? array(), $books ) );
		$errors = array_merge( $errors, self::apply_tab9( $sheets['Downloadable Files']    ?? array(), $books ) );
		$errors = array_merge( $errors, self::apply_tab10( $sheets['ARC Reviewers']        ?? array(), $books ) );
		$errors = array_merge( $errors, self::apply_tab12( $sheets['Section Order']        ?? array(), $books ) );

		$sanitized = array();
		foreach ( $books as $book ) {
			$sanitized[] = self::sanitize_book_object( $book );
		}

		return array( 'books' => $sanitized, 'errors' => $errors );
	}

	// -- Structure validation --------------------------------------------------------

	/**
	 * Ensure the "Books" tab is present and has at least one data row.
	 *
	 * @param array $sheets
	 * @return string[]
	 */
	private static function validate_structure( array $sheets ): array {
		$errors = array();
		if ( ! isset( $sheets['Books'] ) || count( $sheets['Books'] ) < 2 ) {
			$errors[] = 'Tab "Books" is missing or contains no data rows. Aborting import.';
		}
		return $errors;
	}

	// -- Tab 1 indexing --------------------------------------------------------------

	/**
	 * Build [index => book_object_stub] from Tab 1 rows.
	 *
	 * Row 0 is the header; skipped. Col A (index 0) is the integer FK.
	 *
	 * @param array $rows Tab 1 rows including header row.
	 * @return array { books: array<int, array>, errors: string[] }
	 */
	private static function index_tab1( array $rows ): array {
		$books  = array();
		$errors = array();
		foreach ( $rows as $i => $row ) {
			if ( 0 === $i ) {
				continue;
			}
			$idx = (int) ( $row[0] ?? 0 );
			if ( $idx < 1 ) {
				continue;
			}
			// V7-D-001-mapper: Skip rows with empty title — hydrator will reject them silently otherwise.
			$title = trim( (string) ( $row[1] ?? '' ) );
			if ( '' === $title ) {
				$errors[] = sprintf(
					// translators: %d is the row number in the XLSX Books tab.
					__( 'Tab "Books" row %d: missing title — skipped.', 'my-book-showroom' ),
					$i + 1
				);
				continue;
			}
			$books[ $idx ] = self::map_tab1_row( $row );
		}
		return array( 'books' => $books, 'errors' => $errors );
	}

	/**
	 * Map one Tab 1 data row to a partial MBS Book Object.
	 *
	 * Corrections applied:
	 * - PLAN-R2-H-003: universe position key omitted from post_meta. Col AT (index 45)
	 *   is now exposed via reading_orders['universe_pos'] for the importer's ro_map.
	 * - PLAN-R2-M-002: publisher also assigned to taxonomies[MBS_TAX_PUBLISHER].
	 * - PLAN-R2-M-003: taxonomy constants used.
	 *
	 * @param array $row Dense row (0-indexed, columns A-AW = indices 0-48).
	 * @return array Partial MBS Book Object.
	 */
	private static function map_tab1_row( array $row ): array {
		$c = static function ( int $n ) use ( $row ): string {
			return trim( (string) ( $row[ $n ] ?? '' ) );
		};

		return array(
			'post_title'   => $c( 1 ),
			'post_content' => '',
			'post_status'  => 'draft',
			'post_meta'    => array(
				'mbs_teaser'              => $c( 3 ),
				'mbs_tagline'             => $c( 4 ),
				'mbs_synopsis'            => $c( 5 ),
				'mbs_publisher'           => $c( 6 ),
				'mbs_pub_date'            => $c( 8 ),
				'mbs_copyright_year'      => $c( 9 ),
				'mbs_coming_soon'         => $c( 10 ),
				'mbs_lifecycle_state'     => $c( 11 ),
				'mbs_edition'             => $c( 12 ),
				'mbs_audience_code'       => $c( 19 ),
				'mbs_age_range_from'      => $c( 20 ),
				'mbs_age_range_to'        => $c( 21 ),
				'mbs_grade_range_from'    => $c( 22 ),
				'mbs_grade_range_to'      => $c( 23 ),
				'mbs_illustration_count'  => $c( 24 ),
				'mbs_lccn'                => $c( 26 ),
				'mbs_publication_time'    => $c( 27 ),
				'mbs_featured'            => $c( 28 ),
				'mbs_look_inside_text'    => $c( 29 ),
				'mbs_toc'                 => $c( 30 ),
				'_mbs_admin_notes'        => $c( 32 ),
				'mbs_series_position'     => $c( 42 ),
				'mbs_language'            => $c( 61 ),  // BJ
				'mbs_word_count'          => $c( 62 ),  // BK
				'mbs_show_word_count'     => $c( 63 ),  // BL
				'mbs_publisher_url'       => $c( 64 ),  // BM
				'mbs_goodreads_url'       => $c( 65 ),  // BN
				'mbs_storygraph_url'      => $c( 66 ),  // BO
				'mbs_bookbub_url'         => $c( 67 ),  // BP
				'mbs_share_text'          => $c( 68 ),  // BQ
				'mbs_enable_reader_reviews' => $c( 69 ),  // BR
				// NOTE: universe position key intentionally omitted from post_meta (PLAN-R2-H-003).
				// Col AT (index 45) feeds reading_orders['universe_pos'] below.
			),
			'taxonomies'   => array(
				MBS_TAX_AUTHOR           => self::pipe_split( $c( 2 ) ),
				MBS_TAX_IMPRINT          => self::pipe_split( $c( 7 ) ),
				MBS_TAX_GENRE            => self::pipe_split( $c( 13 ) ),
				MBS_TAX_TAG              => self::pipe_split( $c( 14 ) ),
				MBS_TAX_SERIES           => self::pipe_split( $c( 41 ) ),
				MBS_TAX_UNIVERSE         => self::pipe_split( $c( 44 ) ),
				MBS_TAX_COLLECTION       => self::pipe_split( $c( 47 ) ),
				MBS_TAX_THEME            => self::pipe_split( $c( 59 ) ),  // BH
				MBS_TAX_CONTENT_WARNING  => self::pipe_split( $c( 60 ) ),  // BI
				// Publisher also assigned as taxonomy term (PLAN-R2-M-002).
				MBS_TAX_PUBLISHER        => self::pipe_split( $c( 6 ) ),
			),
			'classifications' => array(
				'bisac'        => self::pipe_split( $c( 15 ) ),
				'thema'        => self::pipe_split( $c( 16 ) ),
				'bic'          => self::pipe_split( $c( 17 ) ),
				'bowker'       => self::pipe_split( $c( 18 ) ),
				'illustration' => self::pipe_split( $c( 25 ) ),
			),
			'related_products' => self::parse_related_products( $c( 31 ) ),
			'images'           => array(
				'cover_url'    => $c( 33 ),  // AH
				'cover_file'   => $c( 34 ),  // AI
				'banner_url'   => $c( 35 ),  // AJ
				'banner_file'  => $c( 36 ),  // AK
				'trailer_url'  => $c( 37 ),  // AL
				'trailer_file' => $c( 38 ),  // AM
				'spinning_video_url'  => $c( 39 ),  // AN -- book-level spinning video
				'spinning_video_file' => $c( 40 ),  // AO
			),
			// R3-C-005: collection_positions is the hydrator-expected key ([name => int]).
			// Constructed here from Tab 1 col AV (collection name) and col AW (position).
			// Non-zero position required; empty col AW means "no position set".
			'collection_positions' => ( '' !== $c( 47 ) && '' !== $c( 48 ) && (int) $c( 48 ) > 0 )
				? array( $c( 47 ) => (int) $c( 48 ) )
				: array(),
			'reading_orders' => array(
				// PLAN-R2-H-003: universe_pos from col AT feeds universe ro_map in importer.
				'series_ro'    => $c( 43 ),  // AR -- series reading order
				'universe_pos' => $c( 45 ),  // AT -- universe position (feeds ro_map, not postmeta)
				'universe_ro'  => $c( 46 ),  // AU -- universe reading order
				// collection_pos removed from reading_orders (R3-C-005):
				// collection positions now live in collection_positions[] for hydrator.
			),
			// Term banner filenames from Tab 1 cols AX-AZ (Task 29).
			'term_banners' => array(
				'series_banner'     => $c( 49 ),  // AX
				'collection_banner' => $c( 50 ),  // AY
				'universe_banner'   => $c( 51 ),  // AZ
			),
			'format_rows'       => array(),
			'book_buy_links'    => array(),
			'endorsements'      => array(),
			'contributors'      => array(),
			'awards'            => array(),
			'extra_memberships' => array(),
			// BC-5: Book club fields from Tab 1 cols BA=52 (enabled) and BB=53 (guide).
			// Questions (Tab 8) and files (Tab 9) are populated by apply_tab8/9().
			'book_club' => array(
				'enabled'          => $c( 52 ),  // BA -- book club visible flag ('1' or '0')
				'discussion_guide' => $c( 53 ),  // BB -- HTML discussion guide
				'questions'        => array(),
				'files'            => array(),
			),
			// ARC-3: ARC campaign fields from Tab 1 cols BC=54 to BG=58.
			// Reviewer rows (Tab 10) are populated by apply_tab10().
			'arc' => array(
				'status'             => $c( 54 ),  // BC -- arc campaign status
				'copy_count'         => $c( 55 ),  // BD -- copies distributed
				'distribution_date'  => $c( 56 ),  // BE -- distribution date
				'close_date'         => $c( 57 ),  // BF -- arc close date
				'target_review_date' => $c( 58 ),  // BG -- target review date
				'reviewers'          => array(),
			),
			'section_order'           => array(),
			'reading_order_positions' => array(
				'series'   => array(),
				'universe' => array(),
			),
		);
	}

	// -- Subordinate tab appliers ----------------------------------------------------

	/**
	 * Apply Tab 2 (Formats) rows to the books map.
	 *
	 * Each row is keyed by col A (book index). Creates one format_rows entry
	 * per row and leaves buy_links as an empty array for apply_tab3() to fill.
	 *
	 * @param array  $rows  Tab 2 rows (including header row 0).
	 * @param array &$books Books map by index; modified in place.
	 * @return string[] Row-level error messages.
	 * @since 1.3.0
	 */
	private static function apply_tab2( array $rows, array &$books ): array {
		$errors = array();
		foreach ( $rows as $i => $row ) {
			if ( 0 === $i ) continue;
			$idx = (int) ( $row[0] ?? 0 );
			if ( ! isset( $books[ $idx ] ) ) {
				$errors[] = sprintf( 'Tab "Formats" row %d: index %d not found in Books tab -- skipped.', $i + 1, $idx );
				continue;
			}
			$c = static function ( int $n ) use ( $row ): string { return trim( (string) ( $row[ $n ] ?? '' ) ); };
			$books[ $idx ]['format_rows'][] = array(
				'format_label'     => $c( 2 ),
				'format'           => $c( 3 ),
				'isbn13'           => $c( 4 ),
				'asin'             => $c( 5 ),
				'price'            => $c( 6 ),
				'price_type'       => $c( 7 ),
				'currency_code'    => $c( 8 ),
				'page_length'      => $c( 9 ),
				'edition_number'   => $c( 10 ),
				'edition_type'     => $c( 11 ),
				'pub_date'         => $c( 12 ),
				'supplier_name'    => $c( 13 ),
				'discount_code'    => $c( 14 ),
				'on_sale_date'     => $c( 15 ),
				'returnability'    => $c( 16 ),
				'narrator'         => $c( 17 ),
				'duration_hh'      => $c( 18 ),
				'duration_mm'      => $c( 19 ),
				'audio_sample_url' => $c( 20 ),
				'cover_file'       => $c( 21 ),
				'audio_file'       => $c( 22 ),
				'buy_links'        => array(),
			);
		}
		return $errors;
	}

	/**
	 * Apply Tab 3 (Buy Links) rows.
	 *
	 * R3-M-004 CONSTRAINT: Format-level links are matched to format rows by format_label
	 * (col D of Tab 3 matched to format_rows[i]['format_label']). format_label MUST be
	 * unique within a single book's format rows. If two format rows share the same label,
	 * links are routed only to the first match and the second is silently skipped.
	 * Document this constraint in the XLSX user guide: "Each format must have a unique label."
	 *
	 * @param array  $rows  Tab 3 rows (including header row 0).
	 * @param array &$books Books map by index; modified in place.
	 * @return string[] Row-level error messages.
	 * @since 1.3.0
	 */
	private static function apply_tab3( array $rows, array &$books ): array {
		$errors = array();
		foreach ( $rows as $i => $row ) {
			if ( 0 === $i ) continue;
			$idx = (int) ( $row[0] ?? 0 );
			if ( ! isset( $books[ $idx ] ) ) {
				$errors[] = sprintf( 'Tab "Buy Links" row %d: index %d not found -- skipped.', $i + 1, $idx );
				continue;
			}
			$c     = static function ( int $n ) use ( $row ): string { return trim( (string) ( $row[ $n ] ?? '' ) ); };
			$level = $c( 3 );  // Col D: book | format
			$link  = array(
				'retailer_key'     => $c( 2 ),
				'url'              => $c( 5 ),
				'locale_us'        => $c( 6 ),
				'locale_uk'        => $c( 7 ),
				'locale_ca'        => $c( 8 ),
				'locale_au'        => $c( 9 ),
				'locale_de'        => $c( 10 ),
				'locale_jp'        => $c( 11 ),
				'link_type'        => $c( 12 ) ?: 'buy',
				'link_label'       => $c( 13 ),
				'is_direct_author' => $c( 14 ),
				'sort_order'       => $c( 15 ),
			);
			if ( 'format' === $level ) {
				$fmt_label   = $c( 4 );
				$match_count = 0;
				foreach ( $books[ $idx ]['format_rows'] as &$fr ) {
					if ( $fr['format_label'] === $fmt_label ) {
						$match_count++;
						$fr['buy_links'][] = $link;
						break;  // First match only -- see R3-M-004 constraint above.
					}
				}
				unset( $fr );
				if ( 0 === $match_count ) {
					// R3-M-004: warn when format_label from Tab 3 does not match any Tab 2 row.
					$errors[] = sprintf(
						'Tab "Buy Links" row %d: format label "%s" not found in Tab 2 for book index %d -- link skipped.',
						$i + 1, $fmt_label, $idx
					);
				}
			} else {
				$books[ $idx ]['book_buy_links'][] = $link;
			}
		}
		return $errors;
	}

	/**
	 * Apply Tab 4 (Endorsements) rows.
	 *
	 * Correction R5-L-001: is_visible and sort_order added with safe defaults
	 * so the hydrator receives complete endorsement objects.
	 *
	 * @param array  $rows  Tab 4 rows (including header row 0).
	 * @param array &$books Books map by index; modified in place.
	 * @return string[] Row-level error messages.
	 * @since 1.3.0
	 */
	private static function apply_tab4( array $rows, array &$books ): array {
		$errors = array();
		foreach ( $rows as $i => $row ) {
			if ( 0 === $i ) continue;
			$idx = (int) ( $row[0] ?? 0 );
			if ( ! isset( $books[ $idx ] ) ) {
				$errors[] = sprintf( 'Tab "Endorsements" row %d: index %d not found -- skipped.', $i + 1, $idx );
				continue;
			}
			$c = static function ( int $n ) use ( $row ): string { return trim( (string) ( $row[ $n ] ?? '' ) ); };
			$books[ $idx ]['endorsements'][] = array(
				'attributed_to'     => $c( 2 ),
				'quote_text'        => $c( 3 ),
				'attribution_title' => $c( 4 ),
				'is_visible'        => $c( 5 ) ?: '1',
				'sort_order'        => $c( 6 ) ?: '0',
			);
		}
		return $errors;
	}

	/**
	 * Apply Tab 5 (Contributors) rows.
	 *
	 * Correction PLAN-C-006: key is onix_role_code (not onix_role).
	 * Correction R5-H-001: statement key preserved raw; sanitized later in
	 * sanitize_book_object() via sanitize_textarea_field() to preserve line breaks.
	 *
	 * @param array  $rows  Tab 5 rows (including header row 0).
	 * @param array &$books Books map by index; modified in place.
	 * @return string[] Row-level error messages.
	 * @since 1.3.0
	 */
	private static function apply_tab5( array $rows, array &$books ): array {
		$errors = array();
		foreach ( $rows as $i => $row ) {
			if ( 0 === $i ) continue;
			$idx = (int) ( $row[0] ?? 0 );
			if ( ! isset( $books[ $idx ] ) ) {
				$errors[] = sprintf( 'Tab "Contributors" row %d: index %d not found -- skipped.', $i + 1, $idx );
				continue;
			}
			$c = static function ( int $n ) use ( $row ): string { return trim( (string) ( $row[ $n ] ?? '' ) ); };
			$books[ $idx ]['contributors'][] = array(
				'contributor_name'    => $c( 2 ),
				'onix_role_code'      => $c( 3 ) ?: 'A01',
				'role_label'          => $c( 4 ),
				'statement'           => $c( 5 ),
				'author_headshot_file' => $c( 6 ),
				'author_bio'          => $c( 7 ),
				'author_website_url'  => $c( 8 ),
			);
		}
		return $errors;
	}

	/**
	 * Apply Tab 6 (Awards) rows.
	 *
	 * Correction PLAN-C-005: keys are name and year (not title / award_year).
	 * These must match the keys MBS_Book_Hydrator expects in its awards input.
	 * Correction R5-L-002: sort_order key added with default '0'.
	 *
	 * @param array  $rows  Tab 6 rows (including header row 0).
	 * @param array &$books Books map by index; modified in place.
	 * @return string[] Row-level error messages.
	 * @since 1.3.0
	 */
	private static function apply_tab6( array $rows, array &$books ): array {
		$errors = array();
		foreach ( $rows as $i => $row ) {
			if ( 0 === $i ) continue;
			$idx = (int) ( $row[0] ?? 0 );
			if ( ! isset( $books[ $idx ] ) ) {
				$errors[] = sprintf( 'Tab "Awards" row %d: index %d not found -- skipped.', $i + 1, $idx );
				continue;
			}
			$c = static function ( int $n ) use ( $row ): string { return trim( (string) ( $row[ $n ] ?? '' ) ); };
			$books[ $idx ]['awards'][] = array(
				'name'       => $c( 2 ),
				'year'       => $c( 3 ),
				'status'     => $c( 4 ),
				'sort_order' => $c( 5 ) ?: '0',
			);
		}
		return $errors;
	}

	/**
	 * Apply Tab 7 (Memberships) overflow rows.
	 *
	 * Extra memberships are processed by the importer after core taxonomy
	 * assignments have been made, allowing overflow collections, series, and
	 * universes to be handled without widening the Tab 1 column range.
	 *
	 * @param array  $rows  Tab 7 rows (including header row 0).
	 * @param array &$books Books map by index; modified in place.
	 * @return string[] Row-level error messages.
	 * @since 1.3.0
	 */
	private static function apply_tab7( array $rows, array &$books ): array {
		$errors = array();
		foreach ( $rows as $i => $row ) {
			if ( 0 === $i ) continue;
			$idx = (int) ( $row[0] ?? 0 );
			if ( ! isset( $books[ $idx ] ) ) {
				$errors[] = sprintf( 'Tab "Memberships" row %d: index %d not found -- skipped.', $i + 1, $idx );
				continue;
			}
			$c = static function ( int $n ) use ( $row ): string { return trim( (string) ( $row[ $n ] ?? '' ) ); };
			$books[ $idx ]['extra_memberships'][] = array(
				'term_name'       => $c( 2 ),
				'membership_type' => $c( 3 ),
				'position'        => $c( 4 ),
				'reading_order'   => $c( 5 ),
				'banner_file'     => $c( 6 ),
			);
		}
		return $errors;
	}

	/**
	 * Apply Tab 8 (Discussion Questions) rows to the books map.
	 *
	 * Each row is keyed by col A (book index). Col C (index 2) is the question text.
	 * Empty question strings are silently skipped.
	 *
	 * @param array  $rows  Tab 8 rows (including header row 0).
	 * @param array &$books Books map by index; modified in place.
	 * @return string[] Row-level error messages.
	 */
	private static function apply_tab8( array $rows, array &$books ): array {
		$errors = array();
		foreach ( $rows as $i => $row ) {
			if ( 0 === $i ) continue;
			$idx = (int) ( $row[0] ?? 0 );
			if ( ! isset( $books[ $idx ] ) ) {
				$errors[] = sprintf( 'Tab "Discussion Questions" row %d: index %d not found -- skipped.', $i + 1, $idx );
				continue;
			}
			$c        = static function ( int $n ) use ( $row ): string { return trim( (string) ( $row[ $n ] ?? '' ) ); };
			$question = $c( 2 );
			if ( '' !== $question ) {
				$books[ $idx ]['book_club']['questions'][] = $question;
			}
		}
		return $errors;
	}

	/**
	 * Apply Tab 9 (Downloadable Files) rows to the books map.
	 *
	 * Each row is keyed by col A (book index). Col C (index 2) is the ZIP-relative
	 * filename; col D (index 3) is the optional display label.
	 *
	 * @param array  $rows  Tab 9 rows (including header row 0).
	 * @param array &$books Books map by index; modified in place.
	 * @return string[] Row-level error messages.
	 */
	private static function apply_tab9( array $rows, array &$books ): array {
		$errors = array();
		foreach ( $rows as $i => $row ) {
			if ( 0 === $i ) continue;
			$idx = (int) ( $row[0] ?? 0 );
			if ( ! isset( $books[ $idx ] ) ) {
				$errors[] = sprintf( 'Tab "Downloadable Files" row %d: index %d not found -- skipped.', $i + 1, $idx );
				continue;
			}
			$c    = static function ( int $n ) use ( $row ): string { return trim( (string) ( $row[ $n ] ?? '' ) ); };
			$file = $c( 2 );
			if ( '' !== $file ) {
				$books[ $idx ]['book_club']['files'][] = array(
					'file'  => $file,
					'label' => $c( 3 ),
				);
			}
		}
		return $errors;
	}

	/**
	 * Apply Tab 10 (ARC Reviewers) rows to the books map.
	 *
	 * Each row is keyed by col A (book index). Populates arc['reviewers'][].
	 * Review URLs are stored as a pipe-separated string in col H (index 7);
	 * this method splits them back into an array.
	 *
	 * @param array  $rows  Tab 10 rows (including header row 0).
	 * @param array &$books Books map by index; modified in place.
	 * @return string[]     Row-level error messages.
	 * @since 1.3.4
	 */
	private static function apply_tab10( array $rows, array &$books ): array {
		$errors = array();
		foreach ( $rows as $i => $row ) {
			if ( 0 === $i ) continue;
			$idx = (int) ( $row[0] ?? 0 );
			if ( ! isset( $books[ $idx ] ) ) {
				$errors[] = sprintf( 'Tab "ARC Reviewers" row %d: index %d not found in Books tab — skipped.', $i + 1, $idx );
				continue;
			}
			$c = static function ( int $n ) use ( $row ): string {
				return trim( (string) ( $row[ $n ] ?? '' ) );
			};
			// Col H (index 7): pipe-separated review URLs.
			$urls_raw  = $c( 7 );
			$url_array = '' !== $urls_raw
				? array_values( array_filter( array_map( 'trim', explode( '|', $urls_raw ) ) ) )
				: array();
			$books[ $idx ]['arc']['reviewers'][] = array(
				'reviewer_name'  => $c( 2 ),
				'contact_method' => $c( 3 ),
				'contact_type'   => $c( 4 ),
				'date_sent'      => $c( 5 ),
				'review_posted'  => $c( 6 ),
				'review_urls'    => $url_array,
			);
		}
		return $errors;
	}

	// -- Sanitization ----------------------------------------------------------------

	/**
	 * Sanitize all string values in a book object before returning to caller.
	 *
	 * Correction PLAN-R2-H-006: reading_orders and extra_memberships added.
	 * These contain user-supplied strings used in DB term lookups and DAO calls.
	 *
	 * Uses sanitize_textarea_field() for multi-line content fields; all
	 * others use sanitize_text_field(). Rule 3: trust nothing from the sheet.
	 *
	 * @param array $obj Partial MBS Book Object.
	 * @return array Sanitized copy.
	 */
	/**
	 * Apply Tab 12 (Section Order) rows to the books map.
	 *
	 * Each row is keyed by col A (book index). Columns:
	 *   0 = Index, 1 = Book Title, 2 = Section Key, 3 = Sort Order, 4 = Is Visible.
	 * Populates section_order[] on each book object so that MBS_Book_Hydrator
	 * can call MBS_Section_Order_DAO::save_order() + save_visibility().
	 *
	 * @param array  $rows  Tab 12 rows (including header row 0).
	 * @param array &$books Books map by index; modified in place.
	 * @return string[]     Row-level error messages.
	 */
	private static function apply_tab12( array $rows, array &$books ): array {
		$errors = array();
		foreach ( $rows as $i => $row ) {
			if ( 0 === $i ) continue;
			$idx = (int) ( $row[0] ?? 0 );
			if ( ! isset( $books[ $idx ] ) ) {
				$errors[] = sprintf( 'Tab "Section Order" row %d: index %d not found -- skipped.', $i + 1, $idx );
				continue;
			}
			$section_key = sanitize_key( (string) ( $row[2] ?? '' ) );
			if ( '' === $section_key ) {
				continue;
			}
			$books[ $idx ]['section_order'][] = array(
				'key'        => $section_key,
				'sort_order' => (int) ( $row[3] ?? 0 ),
				'is_visible' => '0' !== trim( (string) ( $row[4] ?? '1' ) ),
			);
		}
		return $errors;
	}

	private static function sanitize_book_object( array $obj ): array {
		$textarea_keys = array( 'mbs_synopsis', 'mbs_teaser', 'mbs_look_inside_text', 'mbs_toc' );

		$obj['post_title'] = sanitize_text_field( $obj['post_title'] ?? '' );

		foreach ( $obj['post_meta'] ?? array() as $key => $val ) {
			$obj['post_meta'][ $key ] = in_array( $key, $textarea_keys, true )
				? sanitize_textarea_field( (string) $val )
				: sanitize_text_field( (string) $val );
		}

		foreach ( $obj['taxonomies'] ?? array() as $tax => $terms ) {
			$obj['taxonomies'][ $tax ] = array_map( 'sanitize_text_field', array_filter( (array) $terms ) );
		}

		foreach ( $obj['classifications'] ?? array() as $type => $codes ) {
			$obj['classifications'][ $type ] = array_map( 'sanitize_text_field', array_filter( (array) $codes ) );
		}

		// Sanitize image URLs.
		foreach ( $obj['images'] ?? array() as $key => $val ) {
			$obj['images'][ $key ] = str_ends_with( $key, '_url' )
				? esc_url_raw( (string) $val )
				: sanitize_text_field( (string) $val );
		}

		// Sanitize reading orders (numeric positions) -- PLAN-R2-H-006.
		foreach ( $obj['reading_orders'] ?? array() as $key => $val ) {
			$obj['reading_orders'][ $key ] = sanitize_text_field( (string) $val );
		}

		// Sanitize term banner filenames (Task 29).
		foreach ( $obj['term_banners'] ?? array() as $key => $val ) {
			$obj['term_banners'][ $key ] = sanitize_text_field( (string) $val );
		}

		// Sanitize extra memberships (user-supplied term names) -- PLAN-R2-H-006.
		foreach ( $obj['extra_memberships'] ?? array() as &$m ) {
			$m['term_name']       = sanitize_text_field( $m['term_name']       ?? '' );
			$m['membership_type'] = sanitize_text_field( $m['membership_type'] ?? '' );
			$m['position']        = sanitize_text_field( $m['position']        ?? '' );
			$m['reading_order']   = sanitize_text_field( $m['reading_order']   ?? '' );
			$m['banner_file']     = sanitize_text_field( $m['banner_file']     ?? '' );
		}
		unset( $m );

		// R7-L-002: Shared locale key list used by both buy-link sanitization passes.
		$locale_keys = array( 'locale_us', 'locale_uk', 'locale_ca', 'locale_au', 'locale_de', 'locale_jp' );

		// Sanitize format rows.
		$str_keys = array(
			'format_label', 'format', 'isbn13', 'asin', 'price', 'price_type',
			'currency_code', 'page_length', 'edition_number', 'edition_type',
			'pub_date', 'supplier_name', 'discount_code', 'on_sale_date',
			'returnability', 'narrator', 'duration_hh', 'duration_mm',
		);
		foreach ( $obj['format_rows'] ?? array() as &$fr ) {
			foreach ( $str_keys as $k ) {
				$fr[ $k ] = sanitize_text_field( $fr[ $k ] ?? '' );
			}
			$fr['audio_sample_url'] = esc_url_raw( $fr['audio_sample_url'] ?? '' );
			$fr['cover_file']       = sanitize_text_field( $fr['cover_file'] ?? '' );
			$fr['audio_file']       = sanitize_text_field( $fr['audio_file'] ?? '' );
			// R7-L-002: Sanitize format-row buy links.
			foreach ( $fr['buy_links'] ?? array() as &$flnk ) {
				$flnk['retailer_key']     = sanitize_key( $flnk['retailer_key']     ?? '' );
				$flnk['url']              = esc_url_raw(  $flnk['url']              ?? '' );
				$flnk['link_label']       = sanitize_text_field( $flnk['link_label'] ?? '' );
				$flnk['link_type']        = sanitize_text_field( $flnk['link_type']  ?? 'buy' );
				$flnk['is_direct_author'] = (int) (bool) ( $flnk['is_direct_author'] ?? false );
				$flnk['sort_order']       = (int) ( $flnk['sort_order'] ?? 0 );
				foreach ( $locale_keys as $lk ) {
					$flnk[ $lk ] = sanitize_text_field( $flnk[ $lk ] ?? '' );
				}
			}
			unset( $flnk );
		}
		unset( $fr );

		// Sanitize endorsements.
		foreach ( $obj['endorsements'] ?? array() as &$e ) {
			$e['attributed_to']     = sanitize_text_field( $e['attributed_to']     ?? '' );
			$e['quote_text']        = sanitize_textarea_field( $e['quote_text']     ?? '' );
			$e['attribution_title'] = sanitize_text_field( $e['attribution_title'] ?? '' );
			$e['is_visible']        = (int) ( $e['is_visible']  ?? 1 );   // R5-L-001
			$e['sort_order']        = (int) ( $e['sort_order']  ?? 0 );   // R5-L-001
		}
		unset( $e );

		// Sanitize contributors.
		foreach ( $obj['contributors'] ?? array() as &$c ) {
			$c['contributor_name']    = sanitize_text_field( $c['contributor_name']    ?? '' );
			$c['onix_role_code']      = sanitize_text_field( $c['onix_role_code']      ?? 'A01' );
			$c['role_label']          = sanitize_text_field( $c['role_label']          ?? '' );
			$c['statement']           = sanitize_textarea_field( $c['statement']       ?? '' );  // R5-H-001
			$c['author_headshot_file'] = sanitize_text_field( $c['author_headshot_file'] ?? '' );
			$c['author_bio']          = sanitize_textarea_field( $c['author_bio']      ?? '' );
			$c['author_website_url']  = esc_url_raw( $c['author_website_url']          ?? '' );
		}
		unset( $c );

		// Sanitize awards.
		foreach ( $obj['awards'] ?? array() as &$a ) {
			$a['name']       = sanitize_text_field( $a['name']   ?? '' );  // PLAN-C-005
			$a['year']       = sanitize_text_field( $a['year']   ?? '' );  // PLAN-C-005
			$a['status']     = sanitize_text_field( $a['status'] ?? '' );
			$a['sort_order'] = (int) ( $a['sort_order'] ?? 0 );   // R5-L-002
		}
		unset( $a );

		// Sanitize book-level buy links -- R7-L-002.
		foreach ( $obj['book_buy_links'] ?? array() as &$lnk ) {
			$lnk['retailer_key']     = sanitize_key( $lnk['retailer_key']     ?? '' );
			$lnk['url']              = esc_url_raw(  $lnk['url']              ?? '' );
			$lnk['link_label']       = sanitize_text_field( $lnk['link_label'] ?? '' );
			$lnk['link_type']        = sanitize_text_field( $lnk['link_type']  ?? 'buy' );
			$lnk['is_direct_author'] = (int) (bool) ( $lnk['is_direct_author'] ?? false );
			$lnk['sort_order']       = (int) ( $lnk['sort_order'] ?? 0 );
			foreach ( $locale_keys as $lk ) {
				$lnk[ $lk ] = sanitize_text_field( $lnk[ $lk ] ?? '' );
			}
		}
		unset( $lnk );

		// Sanitize collection_positions map -- R7-L-003.
		// Keys are term names; values are int positions. Both come from untrusted XLSX.
		$sanitized_col_pos = array();
		foreach ( $obj['collection_positions'] ?? array() as $term_name => $pos ) {
			$clean_name = sanitize_text_field( (string) $term_name );
			if ( '' !== $clean_name ) {
				$sanitized_col_pos[ $clean_name ] = (int) $pos;
			}
		}
		$obj['collection_positions'] = $sanitized_col_pos;

		// Sanitize book_club section (BC-5).
		if ( isset( $obj['book_club'] ) ) {
			$obj['book_club']['enabled']          = sanitize_text_field( $obj['book_club']['enabled']          ?? '' );
			$obj['book_club']['discussion_guide'] = wp_kses_post(        $obj['book_club']['discussion_guide'] ?? '' );
			$obj['book_club']['questions']        = array_values( array_filter( array_map(
				'sanitize_textarea_field',
				(array) ( $obj['book_club']['questions'] ?? array() )
			) ) );
			foreach ( $obj['book_club']['files'] ?? array() as &$bf ) {
				$bf['file']  = sanitize_text_field( $bf['file']  ?? '' );
				$bf['label'] = sanitize_text_field( $bf['label'] ?? '' );
			}
			unset( $bf );
		}

		// Sanitize arc section (ARC-3).
		if ( isset( $obj['arc'] ) ) {
			$obj['arc']['status']             = sanitize_text_field( $obj['arc']['status']             ?? 'not_started' );
			$obj['arc']['copy_count']         = (int) ( $obj['arc']['copy_count'] ?? 0 );
			$obj['arc']['distribution_date']  = sanitize_text_field( $obj['arc']['distribution_date']  ?? '' );
			$obj['arc']['close_date']         = sanitize_text_field( $obj['arc']['close_date']         ?? '' );
			$obj['arc']['target_review_date'] = sanitize_text_field( $obj['arc']['target_review_date'] ?? '' );
			foreach ( $obj['arc']['reviewers'] ?? array() as &$r ) {
				$r['reviewer_name']  = sanitize_text_field( $r['reviewer_name']  ?? '' );
				$r['contact_method'] = sanitize_text_field( $r['contact_method'] ?? '' );
				$r['contact_type']   = sanitize_text_field( $r['contact_type']   ?? '' );
				$r['date_sent']      = sanitize_text_field( $r['date_sent']      ?? '' );
				$r['review_posted']  = (int) (bool) ( $r['review_posted'] ?? 0 );
				$r['review_urls']    = array_values( array_filter( array_map(
					'esc_url_raw', (array) ( $r['review_urls'] ?? array() )
				) ) );
			}
			unset( $r );
		}

		return $obj;
	}

	// -- Helpers ---------------------------------------------------------------------

	/**
	 * Split a pipe-delimited string into a filtered array.
	 *
	 * Empty segments (consecutive pipes, leading/trailing pipes) are removed.
	 * Each surviving segment is trimmed of whitespace.
	 *
	 * @param string $raw Pipe-delimited raw string from XLSX cell.
	 * @return string[] Ordered, filtered, trimmed values.
	 * @since 1.3.0
	 */
	private static function pipe_split( string $raw ): array {
		return array_values( array_filter( array_map( 'trim', explode( '|', $raw ) ) ) );
	}

	/**
	 * Parse "ISBN:relation_code|..." to structured array.
	 *
	 * Correction PLAN-C-007: key is related_isbn (not isbn).
	 *
	 * @param string $raw Pipe-delimited "ISBN:code" pairs from XLSX cell.
	 * @return array[]
	 * @since 1.3.0
	 */
	private static function parse_related_products( string $raw ): array {
		$result = array();
		foreach ( self::pipe_split( $raw ) as $pair ) {
			$parts = explode( ':', $pair, 2 );
			if ( 2 === count( $parts ) ) {
				$result[] = array(
					'related_isbn'  => sanitize_text_field( $parts[0] ),
					'relation_code' => sanitize_text_field( $parts[1] ),
				);
			}
		}
		return $result;
	}
}
