<?php
/**
 * File Path: includes/export/class-mbs-catalog-xlsx-exporter.php
 *
 * XLSX Catalog Exporter
 *
 * Produces the 7-tab normalized catalog XLSX for download. Reads all
 * published books via MBS_Book_Serializer::serialize(), maps each to
 * the 7-tab schema defined in MBS-XLSX-IEF-SPEC.md §4, and writes the
 * file via MBS_XLSX_Builder::build_multi_sheet().
 *
 * Rule 1: Single responsibility — export orchestration only. No helpers
 *         duplicated from MBS_Book_Export_Data (safe_cell, query args).
 * Rule 2: All reads via MBS_Book_Serializer. Zero direct get_post_meta()
 *         or $wpdb calls.
 * Rule 3: MBS_Book_Export_Data::safe_cell() applied to every string column.
 *         PRO gate enforced in download(). Nonce enforced by the calling tab
 *         handler via MBS_Ajax_Import_Export_Guard.
 *
 * @package MyBookShowroom
 * @since   1.3.0
 */

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

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

	/** Canonical tab names in output order. */
	const TAB_NAMES = array(
		'Books', 'Formats', 'Buy Links',
		'Endorsements', 'Contributors', 'Awards', 'Memberships',
		'Discussion Questions', 'Downloadable Files',
		'ARC Reviewers',
		'Section Order',
		'Taxonomy', 'Manifest',
	);

	// ── Public API ────────────────────────────────────────────────────────────

	/**
	 * Build and stream the 7-tab catalog XLSX as a file download.
	 *
	 * PRO-gated. Writes to a temp file, streams it, then deletes the temp.
	 * Does not return on success -- exits after streaming.
	 *
	 * @return void
	 * @since 1.3.0
	 */
	/**
	 * Build the 7-tab catalog XLSX to a caller-specified filesystem path.
	 *
	 * Allows other classes (e.g. MBS_Exporter_Zip) to embed the catalog
	 * as a file inside a larger archive without triggering an HTTP download.
	 * PRO-gated: returns WP_Error when the license check fails.
	 *
	 * @param string $dest_path Absolute filesystem path for the output file.
	 * @return true|\WP_Error   true on success; WP_Error on failure.
	 * @since 1.3.1
	 */
	public static function build_to_path( string $dest_path, array $file_map = array() ): true|\WP_Error {
		if ( ! mbs_is_pro() ) {
			return new \WP_Error( 'license_required', 'XLSX catalog export requires a PRO license.' );
		}
		$sheets = self::collect_sheets( $file_map );
		return MBS_XLSX_Builder::build_multi_sheet( $sheets, $dest_path );
	}

	public static function download(): void {
		if ( ! mbs_is_pro() ) {
			wp_send_json_error( array( 'message' => 'XLSX catalog export requires a PRO license.' ) );
		}

		$temp_dir  = MBS_CSV_Temp_Manager::get_temp_dir();
		$dest_path = trailingslashit( $temp_dir ) . 'catalog_' . get_current_user_id()
		           . '_' . wp_generate_password( 12, false ) . '.xlsx';

		$sheets = self::collect_sheets();
		$result = MBS_XLSX_Builder::build_multi_sheet( $sheets, $dest_path );

		if ( is_wp_error( $result ) ) {
			wp_delete_file( $dest_path );  // R8-L-003: prevent partial temp file on build failure
			wp_send_json_error( array( 'message' => $result->get_error_message() ) );
		}

		$filename = 'mbs-catalog-' . gmdate( 'Y-m-d' ) . '.xlsx';
		header( 'Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' );
		header( 'Content-Disposition: attachment; filename="' . $filename . '"' );
		header( 'Content-Length: ' . filesize( $dest_path ) );
		header( 'Cache-Control: no-cache, no-store, must-revalidate' );

		// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_readfile
		readfile( $dest_path );
		wp_delete_file( $dest_path );
		exit;
	}

	// ── Sheet collection ──────────────────────────────────────────────────────

	/**
	 * Query all published books and assemble the 7-tab sheet array.
	 *
	 * Uses MBS_Book_Export_Data::get_all_books_query_args() for canonical
	 * shared WP_Query args (Cardinal Rule 1 -- no inline duplication).
	 *
	 * @return array ['Books' => [...], 'Formats' => [...], ...]
	 */
	private static function collect_sheets( array $file_map = array() ): array {
		$sheets = array_fill_keys( self::TAB_NAMES, array() );

		// Header rows.
		$sheets['Books']        = array( self::headers_tab1() );
		$sheets['Formats']      = array( self::headers_tab2() );
		$sheets['Buy Links']    = array( self::headers_tab3() );
		$sheets['Endorsements'] = array( self::headers_tab4() );
		$sheets['Contributors'] = array( self::headers_tab5() );
		$sheets['Awards']       = array( self::headers_tab6() );
		$sheets['Memberships']          = array( self::headers_tab7() );
		$sheets['Discussion Questions'] = array( self::headers_tab8() );
		$sheets['Downloadable Files']   = array( self::headers_tab9() );
		$sheets['ARC Reviewers']        = array( self::headers_tab10() );
		$sheets['Section Order']        = array( self::headers_section_order() );
		$sheets['Taxonomy']             = array( self::headers_taxonomy() );
		$sheets['Manifest']             = array( self::headers_manifest() );

		// Canonical shared query args -- never duplicate inline (PLAN-R2-H-001).
		$query = new WP_Query( MBS_Book_Export_Data::get_all_books_query_args() );

		$index = 1;
		// R3-C-007: WP_Query::$posts returns WP_Post objects, not integers.
		// Cast (int) on a WP_Post object returns 1 in PHP 8.0 (deprecated)
		// and TypeError in PHP 8.1+. Extract ID explicitly with ->ID.
		foreach ( $query->posts as $post ) {
			$post_id = (int) $post->ID;
			$obj     = MBS_Book_Serializer::serialize( $post_id );
			$title = $obj['post_title'] ?? '';

			$sheets['Books'][]        = self::map_book_to_tab1( $index, $obj, $file_map[ $post_id ] ?? array() );
			$sheets['Formats']        = array_merge( $sheets['Formats'],      self::map_formats( $index, $title, $obj, $file_map[ $post_id ] ?? array() ) );
			$sheets['Buy Links']      = array_merge( $sheets['Buy Links'],    self::map_buy_links( $index, $title, $obj ) );
			$sheets['Endorsements']   = array_merge( $sheets['Endorsements'], self::map_endorsements( $index, $title, $obj ) );
			$sheets['Contributors']   = array_merge( $sheets['Contributors'], self::map_contributors( $index, $title, $obj, $file_map[ $post_id ] ?? array() ) );
			$sheets['Awards']         = array_merge( $sheets['Awards'],       self::map_awards( $index, $title, $obj ) );
			$sheets['Memberships']          = array_merge( $sheets['Memberships'],         self::map_memberships( $index, $title, $obj, $file_map[ $post_id ] ?? array() ) );
			$sheets['Discussion Questions'] = array_merge( $sheets['Discussion Questions'], self::map_discussion_questions( $index, $title, $obj ) );
			$sheets['Downloadable Files']   = array_merge( $sheets['Downloadable Files'],  self::map_downloadable_files( $index, $title, $file_map[ $post_id ] ?? array() ) );
			$sheets['ARC Reviewers']        = array_merge( $sheets['ARC Reviewers'],       self::map_arc_reviewers( $index, $title, $obj ) );
			$sheets['Section Order']        = array_merge( $sheets['Section Order'],        self::map_section_order( $index, $title, $obj ) );

			$index++;
		}
		// R5-L-004: Defensive -- reset global post state after WP_Query loop.
		wp_reset_postdata();

		// ── Tab 11: Taxonomy ────────────────────────────────────────────────
		foreach ( self::collect_taxonomy_rows() as $tax_row ) {
			$sheets['Taxonomy'][] = $tax_row;
		}

		// ── Tab 12: Manifest — book_count from the WP_Query total above ────
		$sheets['Manifest'][] = self::manifest_row( $index - 1 );

		return $sheets;
	}

	// ── Tab 1 mapping ─────────────────────────────────────────────────────────

	/**
	 * Map one MBS Book Object to a Tab 1 row (columns A-AW, indices 0-48).
	 *
	 * @param int   $index 1-based book index (Col A).
	 * @param array $obj   MBS Book Object from MBS_Book_Serializer::serialize().
	 * @return array       Dense row array of 49 string values.
	 */
	private static function map_book_to_tab1( int $index, array $obj, array $media = array() ): array {
		$meta = $obj['post_meta']               ?? array();
		$tax  = $obj['taxonomies']              ?? array();
		$cls  = $obj['classifications']         ?? array();
		$ro   = $obj['reading_order_positions'] ?? array();
		$col  = $obj['collection_positions']    ?? array();
		$imgs = $obj['images']                  ?? array();
		$bc   = $obj['book_club']               ?? array();
		$arc  = $obj['arc']                     ?? array();

		// First series/universe/collection memberships.
		$series     = $tax[ MBS_TAX_SERIES ][0]     ?? '';
		$universe   = $tax[ MBS_TAX_UNIVERSE ][0]   ?? '';
		$collection = $tax[ MBS_TAX_COLLECTION ][0] ?? '';

		// Publisher: postmeta first; fall back to taxonomy term name (PLAN-R2-M-002).
		$publisher = $meta['mbs_publisher'] ?? '';
		if ( '' === $publisher ) {
			$publisher = $tax[ MBS_TAX_PUBLISHER ][0] ?? '';
		}

		// Reading order positions (PLAN-C-008).
		$series_terms = $tax[ MBS_TAX_SERIES ] ?? array();
		$series_ro    = '';
		if ( ! empty( $series_terms ) ) {
			$series_term_ids = array_keys( $ro['series'] ?? array() );
			if ( ! empty( $series_term_ids ) ) {
				$series_ro = (string) ( $ro['series'][ $series_term_ids[0] ] ?? '' );
			}
		}

		$universe_terms = $tax[ MBS_TAX_UNIVERSE ] ?? array();
		$universe_ro    = '';
		$universe_pos   = '';
		if ( ! empty( $universe_terms ) ) {
			$universe_term_ids = array_keys( $ro['universe'] ?? array() );
			if ( ! empty( $universe_term_ids ) ) {
				$universe_ro  = (string) ( $ro['universe'][ $universe_term_ids[0] ] ?? '' );
				$universe_pos = $universe_ro;
			}
		}

		// Abbreviation for the canonical safe_cell delegate (PLAN-H-005 / PLAN-R2-M-001).
		// No local safe_cell() method -- delegate to MBS_Book_Export_Data throughout.
		$sc = 'MBS_Book_Export_Data::safe_cell';

		return array(
			$index,                                                                          // A  0
			$sc( $obj['post_title'] ?? '' ),                                                 // B  1
			$sc( implode( '|', $tax[ MBS_TAX_AUTHOR ] ?? array() ) ),                       // C  2
			$sc( $meta['mbs_teaser']           ?? '' ),                                      // D  3
			$sc( $meta['mbs_tagline']          ?? '' ),                                      // E  4
			$sc( $meta['mbs_synopsis']         ?? '' ),                                      // F  5
			$sc( $publisher ),                                                               // G  6
			$sc( $tax[ MBS_TAX_IMPRINT ][0]    ?? '' ),                                      // H  7
			$sc( $meta['mbs_pub_date']         ?? '' ),                                      // I  8
			$sc( $meta['mbs_copyright_year']   ?? '' ),                                      // J  9
			$sc( $meta['mbs_coming_soon']      ?? '' ),                                      // K 10
			$sc( $meta['mbs_lifecycle_state']  ?? '' ),                                      // L 11
			$sc( $meta['mbs_edition']          ?? '' ),                                      // M 12
			$sc( implode( '|', $tax[ MBS_TAX_GENRE ] ?? array() ) ),                        // N 13
			$sc( implode( '|', $tax[ MBS_TAX_TAG ]   ?? array() ) ),                        // O 14
			$sc( implode( '|', $cls['bisac']   ?? array() ) ),                               // P 15
			$sc( implode( '|', $cls['thema']   ?? array() ) ),                               // Q 16
			$sc( implode( '|', $cls['bic']     ?? array() ) ),                               // R 17
			$sc( implode( '|', $cls['bowker']  ?? array() ) ),                               // S 18
			$sc( $meta['mbs_audience_code']    ?? '' ),                                      // T 19
			$sc( $meta['mbs_age_range_from']   ?? '' ),                                      // U 20
			$sc( $meta['mbs_age_range_to']     ?? '' ),                                      // V 21
			$sc( $meta['mbs_grade_range_from'] ?? '' ),                                      // W 22
			$sc( $meta['mbs_grade_range_to']   ?? '' ),                                      // X 23
			$sc( $meta['mbs_illustration_count'] ?? '' ),                                    // Y 24
			$sc( implode( '|', $cls['illustration'] ?? array() ) ),                         // Z 25
			$sc( $meta['mbs_lccn']             ?? '' ),                                      // AA 26
			$sc( $meta['mbs_publication_time'] ?? '' ),                                      // AB 27
			$sc( $meta['mbs_featured']         ?? '' ),                                      // AC 28
			$sc( $meta['mbs_look_inside_text'] ?? '' ),                                      // AD 29
			$sc( $meta['mbs_toc']              ?? '' ),                                      // AE 30
			$sc( self::flatten_related_products( $obj ) ),                                   // AF 31
			$sc( $meta['_mbs_admin_notes']     ?? '' ),                                      // AG 32
			$sc( $imgs['cover_url']            ?? '' ),                                      // AH 33
			$sc( $media['cover'] ?? '' ),                                                   // AI 34 cover_file
			$sc( $imgs['banner_url']           ?? '' ),                                      // AJ 35
			$sc( $media['banner'] ?? '' ),                                                  // AK 36 banner_file
			$sc( $imgs['trailer_video_url']    ?? '' ),                                      // AL 37
			$sc( $media['trailer'] ?? '' ),                                                 // AM 38 trailer_file
			$sc( $imgs['spinning_video_url'] ?? '' ),                                        // AN 39 spinning_video_url
			$sc( $media['spin'] ?? '' ),                                                     // AO 40 spinning_video_file
			$sc( $series ),                                                                  // AP 41
			$sc( $meta['mbs_series_position']  ?? '' ),                                      // AQ 42
			$sc( $series_ro ),                                                               // AR 43
			$sc( $universe ),                                                                // AS 44
			// AT (45): Legacy mbs_universe_position placeholder -- same value as AU (46)
			// after PLAN-R2-H-003 removed the separate postmeta. (R5-L-003)
			$sc( $universe_pos ),                                                            // AT 45 legacy placeholder
			$sc( $universe_ro ),                                                             // AU 46 universe reading-order position
			$sc( $collection ),                                                              // AV 47
			$sc( (string) ( $col[ $collection ] ?? '' ) ),                                   // AW 48
			$sc( $media['series_banner']     ?? '' ),                                        // AX 49 series_banner_file
			$sc( $media['collection_banner'] ?? '' ),                                        // AY 50 collection_banner_file
			$sc( $media['universe_banner']   ?? '' ),                                        // AZ 51 universe_banner_file
			$sc( ( $bc['enabled'] ?? false ) ? '1' : '0' ),                                 // BA 52 book_club_visible
			$sc( $bc['guide'] ?? '' ),                                                       // BB 53 discussion_guide
			$sc( $arc['status']             ?? 'not_started' ),                              // BC 54 arc_status
			$sc( (string) ( $arc['copy_count']         ?? 0 ) ),                             // BD 55 arc_copy_count
			$sc( $arc['distribution_date']  ?? '' ),                                         // BE 56 arc_distribution_date
			$sc( $arc['close_date']         ?? '' ),                                         // BF 57 arc_close_date
			$sc( $arc['target_review_date'] ?? '' ),                                         // BG 58 arc_target_review_date
			$sc( implode( '|', $tax[ MBS_TAX_THEME ]           ?? array() ) ),               // BH 59 themes
			$sc( implode( '|', $tax[ MBS_TAX_CONTENT_WARNING ] ?? array() ) ),               // BI 60 content_warnings
			$sc( $meta['mbs_language']        ?? '' ),                                        // BJ 61 language
			$sc( (string) ( $meta['mbs_word_count']       ?? 0 ) ),                          // BK 62 word_count
			$sc( ( $meta['mbs_show_word_count'] ?? false ) ? '1' : '0' ),                    // BL 63 show_word_count
			$sc( $meta['mbs_publisher_url']    ?? '' ),                                       // BM 64 publisher_url
			$sc( $meta['mbs_goodreads_url']    ?? '' ),                                       // BN 65 goodreads_url
			$sc( $meta['mbs_storygraph_url']   ?? '' ),                                       // BO 66 storygraph_url
			$sc( $meta['mbs_bookbub_url']      ?? '' ),                                       // BP 67 bookbub_url
			$sc( $meta['mbs_share_text']        ?? '' ),                                       // BQ 68 share_text
			$sc( ( $meta['mbs_enable_reader_reviews'] ?? false ) ? '1' : '0' ),               // BR 69 enable_reader_reviews
		);
	}

	/** @return string[] Tab 1 header row. */
	private static function headers_tab1(): array {
		return array(
			'Index','Book Title','Author','Teaser','Tagline','Synopsis',
			'Publisher','Imprint','Pub Date','Copyright Year','Coming Soon',
			'Lifecycle State','Edition Statement','Genre','Tags',
			'BISAC Codes','Thema Codes','BIC Codes','Bowker Codes',
			'Audience Code','Age Range From','Age Range To',
			'Grade Range From','Grade Range To','Illustration Count',
			'Illustration Types','LCCN','Publication Time','Featured',
			'Look Inside Text','TOC','Related Products','Admin Notes',
			'Cover Image URL','Cover Image File','Banner Image URL',
			'Banner Image File','Trailer Video URL','Trailer Video File',
			'Spinning Video URL','Spinning Video File',
			'Series','Series Position','Series Reading Order',
			'Universe','Universe Position','Universe Reading Order',
			'Collection','Collection Position',
			'Series Banner File','Collection Banner File','Universe Banner File',
			'Book Club Visible','Discussion Guide',
			'ARC Status','ARC Copy Count','ARC Distribution Date','ARC Close Date','ARC Target Review Date',
			'Themes','Content Warnings',
			'Language','Word Count','Show Word Count',
			'Publisher URL','Goodreads URL','Storygraph URL','BookBub URL',
			'Social Share Text','Enable Reader Reviews',
		);
	}

	// ── Tab 2 mapping ─────────────────────────────────────────────────────────

	/** @return array[] Zero or more Tab 2 rows for this book's format rows. */
	private static function map_formats( int $index, string $title, array $obj, array $media = array() ): array {
		$rows = array();
		$sc   = 'MBS_Book_Export_Data::safe_cell';
		foreach ( $obj['format_rows'] ?? array() as $fr ) {
			$fkey           = '' !== ( $fr['isbn13'] ?? '' ) ? $fr['isbn13'] : ( $fr['format_label'] ?? '' );
			$fmt_cover_file = $media['formats'][ $fkey ]['cover'] ?? '';
			// Derive a readable Format Label from the Format Key when the DB field is blank,
			// preventing permanently-empty labels on round-trip exports from sites that
			// created format rows without explicitly naming them.
			$fmt_label = $fr['format_label'] ?? '';
			if ( '' === $fmt_label ) {
				static $label_defaults = array(
					'ebook'       => 'eBook',
					'paperback'   => 'Paperback',
					'hardcover'   => 'Hardcover',
					'audiobook'   => 'Audiobook',
					'boardbook'   => 'Board Book',
					'large_print' => 'Large Print',
					'other'       => 'Other',
				);
				$fmt_key   = $fr['format'] ?? '';
				$fmt_label = $label_defaults[ $fmt_key ] ?? ucwords( str_replace( '_', ' ', $fmt_key ) );
			}
			$rows[] = array(
				$index,
				$sc( $title ),
				$sc( $fmt_label ),
				$sc( $fr['format']         ?? '' ),
				$sc( $fr['isbn13']         ?? '' ),
				$sc( $fr['asin']           ?? '' ),
				$sc( $fr['price']          ?? '' ),
				$sc( $fr['price_type']     ?? '' ),
				$sc( $fr['currency_code']  ?? '' ),
				$sc( $fr['page_length']    ?? '' ),
				$sc( $fr['edition_number'] ?? '' ),
				$sc( $fr['edition_type']   ?? '' ),
				$sc( $fr['pub_date']       ?? '' ),
				$sc( $fr['supplier_name']  ?? '' ),
				$sc( $fr['discount_code']  ?? '' ),
				$sc( $fr['on_sale_date']   ?? '' ),
				$sc( $fr['returnability']  ?? '' ),
				$sc( $fr['narrator']       ?? '' ),
				$sc( $fr['duration_hh']    ?? '' ),
				$sc( $fr['duration_mm']    ?? '' ),
				$sc( $fr['audio_sample_url'] ?? '' ),
				$sc( $fmt_cover_file ),                           // col 21 cover_image_file
				$sc( $media['formats'][ $fkey ]['audio'] ?? '' ), // col 22 audio_sample_file
			);
		}
		return $rows;
	}

	/** @return string[] Tab 2 header row. */
	private static function headers_tab2(): array {
		return array(
			'Index','Book Title','Format Label','Format Key',
			'ISBN-13','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',
			'Audio Sample URL','Cover Image File','Audio Sample File',
		);
	}

	// ── Tab 3 mapping ─────────────────────────────────────────────────────────

	/** @return array[] Tab 3 buy link rows. */
	private static function map_buy_links( int $index, string $title, array $obj ): array {
		$rows = array();
		// Book-level links.
		foreach ( $obj['book_buy_links'] ?? array() as $lnk ) {
			$rows[] = self::buy_link_row( $index, $title, 'book', '', $lnk );
		}
		// Format-level links.
		foreach ( $obj['format_rows'] ?? array() as $fr ) {
			$label = $fr['format_label'] ?? '';
			foreach ( $fr['buy_links'] ?? array() as $lnk ) {
				$rows[] = self::buy_link_row( $index, $title, 'format', $label, $lnk );
			}
		}
		return $rows;
	}

	/** Build one Tab 3 row. */
	private static function buy_link_row( int $index, string $title, string $level, string $format_label, array $lnk ): array {
		$sc = 'MBS_Book_Export_Data::safe_cell';
		return array(
			$index,
			$sc( $title ),
			$sc( $lnk['retailer_key']     ?? '' ),
			$sc( $level ),
			$sc( $format_label ),
			$sc( $lnk['url']              ?? '' ),
			$sc( $lnk['locale_us']        ?? '' ),
			$sc( $lnk['locale_uk']        ?? '' ),
			$sc( $lnk['locale_ca']        ?? '' ),
			$sc( $lnk['locale_au']        ?? '' ),
			$sc( $lnk['locale_de']        ?? '' ),
			$sc( $lnk['locale_jp']        ?? '' ),
			$sc( $lnk['link_type']        ?? 'buy' ),
			$sc( $lnk['link_label']       ?? '' ),
			$sc( $lnk['is_direct_author'] ?? '0' ),
			$sc( (string) ( $lnk['sort_order'] ?? '0' ) ),
		);
	}

	/** @return string[] Tab 3 header row. */
	private static function headers_tab3(): array {
		return array(
			'Index','Book Title','Retailer Key','Link Level','Format Label',
			'URL','Locale US','Locale UK','Locale CA','Locale AU','Locale DE','Locale JP',
			'Link Type','Link Label','Is Direct Author','Sort Order',
		);
	}

	// ── Tab 4 mapping ─────────────────────────────────────────────────────────

	/** @return array[] Tab 4 endorsement rows. */
	private static function map_endorsements( int $index, string $title, array $obj ): array {
		$rows = array();
		$sc   = 'MBS_Book_Export_Data::safe_cell';
		foreach ( $obj['endorsements'] ?? array() as $e ) {
			$rows[] = array(
				$index,
				$sc( $title ),
				$sc( $e['attributed_to']     ?? '' ),
				$sc( $e['quote_text']        ?? '' ),
				$sc( $e['attribution_title'] ?? '' ),
				$sc( $e['is_visible']        ?? '1' ),
				$sc( (string) ( $e['sort_order'] ?? '0' ) ),
			);
		}
		return $rows;
	}

	/** @return string[] Tab 4 header row. */
	private static function headers_tab4(): array {
		return array( 'Index','Book Title','Endorser Name','Quote Text','Attribution Title','Is Visible','Sort Order' );
	}

	// ── Tab 5 mapping ─────────────────────────────────────────────────────────

	/**
	 * @return array[] Tab 5 contributor rows.
	 *
	 * PLAN-C-006: key is 'onix_role_code' (not 'onix_role').
	 */
	private static function map_contributors( int $index, string $title, array $obj, array $media = array() ): array {
		$rows = array();
		$sc   = 'MBS_Book_Export_Data::safe_cell';
		foreach ( $obj['contributors'] ?? array() as $c ) {
			$name = $c['contributor_name'] ?? '';
			// Resolve headshot: prefer file_map (ZIP-relative filename built by
			// MBS_Exporter_Zip::collect_media_queue()) over the serialised book object
			// value (which is always empty after a WordPress round-trip).
			$headshot = $media['headshots'][ $name ] ?? ( $c['author_headshot_file'] ?? '' );
			$rows[] = array(
				$index,
				$sc( $title ),
				$sc( $name ),
				$sc( $c['onix_role_code']     ?? 'A01' ),
				$sc( $c['role_label']         ?? '' ),
				$sc( $c['statement']          ?? '' ),
				$sc( $headshot ),
				$sc( $c['author_bio']         ?? '' ),
				$sc( $c['author_website_url'] ?? '' ),
			);
		}
		return $rows;
	}

	/** @return string[] Tab 5 header row. */
	private static function headers_tab5(): array {
		return array( 'Index','Book Title','Contributor Name','ONIX Role Code','Role Label','Statement','Headshot File','Author Bio','Author Website URL' );
	}

	// ── Tab 6 mapping ─────────────────────────────────────────────────────────

	/**
	 * @return array[] Tab 6 award rows.
	 *
	 * PLAN-C-005: keys are 'name' and 'year' (not 'title' / 'award_year').
	 */
	private static function map_awards( int $index, string $title, array $obj ): array {
		$rows = array();
		$sc   = 'MBS_Book_Export_Data::safe_cell';
		foreach ( $obj['awards'] ?? array() as $a ) {
			$rows[] = array(
				$index,
				$sc( $title ),
				$sc( $a['name']   ?? '' ),
				$sc( $a['year']   ?? '' ),
				$sc( $a['status'] ?? '' ),
				$sc( (string) ( $a['sort_order'] ?? '0' ) ),
			);
		}
		return $rows;
	}

	/** @return string[] Tab 6 header row. */
	private static function headers_tab6(): array {
		return array( 'Index','Book Title','Award Name','Award Year','Status','Sort Order' );
	}

	// ── Tab 7 mapping ─────────────────────────────────────────────────────────

	/**
	 * Emit Tab 7 rows for books with overflow memberships.
	 *
	 * Tab 1 holds the FIRST series/universe/collection membership only.
	 * Tab 7 holds memberships for books that have MORE than one of any type.
	 *
	 * PLAN-R2-H-002: emit ONLY overflow terms (array_slice index 1+), not all terms.
	 * R5-C-001: iterate $tax[MBS_TAX_COLLECTION] (alphabetical, deterministic), NOT
	 *            $col_positions (indeterminate DAO order).
	 *
	 * @return array[]
	 */
	private static function map_memberships( int $index, string $title, array $obj, array $media = array() ): array {
		$rows = array();
		$sc   = 'MBS_Book_Export_Data::safe_cell';
		$tax  = $obj['taxonomies'] ?? array();

		// Series memberships beyond the first.
		$series_all = $tax[ MBS_TAX_SERIES ] ?? array();
		if ( count( $series_all ) > 1 ) {
			foreach ( array_slice( $series_all, 1 ) as $term_name ) {
				$rows[] = array( $index, $sc( $title ), $sc( $term_name ), 'series', '', '', $sc( $media['overflow_banners']['series'][ $term_name ] ?? '' ) );
			}
		}

		// Universe memberships beyond the first.
		$universe_all = $tax[ MBS_TAX_UNIVERSE ] ?? array();
		if ( count( $universe_all ) > 1 ) {
			foreach ( array_slice( $universe_all, 1 ) as $term_name ) {
				$rows[] = array( $index, $sc( $title ), $sc( $term_name ), 'universe', '', '', $sc( $media['overflow_banners']['universe'][ $term_name ] ?? '' ) );
			}
		}

		// Collection memberships beyond the first.
		// R5-C-001: drive from $tax[MBS_TAX_COLLECTION] (alphabetical, deterministic),
		// use $col_positions only as a lookup table for position values.
		$col_all       = $tax[ MBS_TAX_COLLECTION ] ?? array();
		$col_positions = $obj['collection_positions'] ?? array();
		if ( count( $col_all ) > 1 ) {
			foreach ( array_slice( $col_all, 1 ) as $term_name ) {
				$pos    = $col_positions[ $term_name ] ?? '';
				$rows[] = array( $index, $sc( $title ), $sc( $term_name ), 'collection', $sc( (string) $pos ), '', $sc( $media['overflow_banners']['collection'][ $term_name ] ?? '' ) );
			}
		}

		return $rows;
	}

	/** @return string[] Tab 7 header row. */
	private static function headers_tab7(): array {
		return array( 'Index','Book Title','Term Name','Membership Type','Position','Reading Order','Banner File' );
	}

	// ── Tab 8 mapping ─────────────────────────────────────────────────────────

	/**
	 * @return array[] Tab 8 (Discussion Questions) rows for this book.
	 */
	private static function map_discussion_questions( int $index, string $title, array $obj ): array {
		$rows = array();
		$sc   = 'MBS_Book_Export_Data::safe_cell';
		foreach ( $obj['book_club']['questions'] ?? array() as $question ) {
			if ( '' === (string) $question ) { continue; }
			$rows[] = array(
				$index,
				$sc( $title ),
				$sc( (string) $question ),
			);
		}
		return $rows;
	}

	/** @return string[] Tab 8 header row. */
	private static function headers_tab8(): array {
		return array( 'Index', 'Book Title', 'Question' );
	}

	// ── Tab 9 mapping ─────────────────────────────────────────────────────────

	/**
	 * @return array[] Tab 9 (Downloadable Files) rows for this book.
	 *
	 * Uses file_map (populated by MBS_Exporter_Zip) for ZIP-relative filenames
	 * and labels. Empty when exporting XLSX without a ZIP (no file_map provided).
	 */
	private static function map_downloadable_files( int $index, string $title, array $media = array() ): array {
		$rows = array();
		$sc   = 'MBS_Book_Export_Data::safe_cell';
		foreach ( $media['files'] ?? array() as $f ) {
			$zip_name = (string) ( $f['zip_name'] ?? '' );
			if ( '' === $zip_name ) { continue; }
			$rows[] = array(
				$index,
				$sc( $title ),
				$sc( $zip_name ),
				$sc( (string) ( $f['label'] ?? '' ) ),
			);
		}
		return $rows;
	}

	/** @return string[] Tab 9 header row. */
	private static function headers_tab9(): array {
		return array( 'Index', 'Book Title', 'File', 'Label' );
	}

	// ── Tab 10 mapping ─────────────────────────────────────────────────────

	/**
	 * Map ARC reviewer rows for Tab 10.
	 *
	 * Emits one row per reviewer in obj['arc']['reviewers']. Review URLs
	 * are pipe-joined into a single cell (col H, index 7).
	 *
	 * @param int    $index 1-based book index.
	 * @param string $title Book title.
	 * @param array  $obj   MBS Book Object from MBS_Book_Serializer::serialize().
	 * @return array[]      Zero or more Tab 10 rows.
	 */
	private static function map_arc_reviewers( int $index, string $title, array $obj ): array {
		$rows = array();
		$sc   = 'MBS_Book_Export_Data::safe_cell';
		foreach ( $obj['arc']['reviewers'] ?? array() as $r ) {
			$urls_str = implode( '|', array_filter( (array) ( $r['review_urls'] ?? array() ) ) );
			$rows[] = array(
				$index,
				$sc( $title ),
				$sc( $r['reviewer_name']  ?? '' ),
				$sc( $r['contact_method'] ?? '' ),
				$sc( $r['contact_type']   ?? '' ),
				$sc( $r['date_sent']      ?? '' ),
				$sc( (string) ( $r['review_posted'] ?? 0 ) ),
				$sc( $urls_str ),
			);
		}
		return $rows;
	}

	/** @return string[] Tab 10 header row. */
	private static function headers_tab10(): array {
		return array( 'Index', 'Book Title', 'Reviewer Name', 'Contact Method', 'Contact Type', 'Date Sent', 'Review Posted', 'Review URLs' );
	}

	// ── Helpers ───────────────────────────────────────────────────────────────


	// ── Tab 12 — Section Order ───────────────────────────────────────────

	/** @return string[] Tab 12 header row. */
	private static function headers_section_order(): array {
		return array( 'Index', 'Book Title', 'Section Key', 'Sort Order', 'Is Visible' );
	}

	/**
	 * Build Tab 12 (Section Order) rows for one book.
	 *
	 * Reads section_order[] from the book object (populated by
	 * MBS_Book_Serializer::build_section_order(), which calls
	 * MBS_Section_Order_DAO::get_order_admin()). Exports the fully-resolved
	 * order -- even for books that rely on global defaults -- so that a
	 * round-trip import pins the display order regardless of the destination
	 * site's global defaults.
	 *
	 * @param int    $index 1-based book index.
	 * @param string $title Book title.
	 * @param array  $obj   MBS Book Object.
	 * @return array[]      Zero or more Tab 12 rows.
	 */
	private static function map_section_order( int $index, string $title, array $obj ): array {
		$rows = array();
		$sc   = 'MBS_Book_Export_Data::safe_cell';
		foreach ( $obj['section_order'] ?? array() as $s ) {
			$rows[] = array(
				$index,
				$sc( $title ),
				$sc( $s['key']        ?? '' ),
				(string) (int) ( $s['sort_order'] ?? 0 ),
				$s['is_visible'] ? '1' : '0',
			);
		}
		return $rows;
	}

	// ── Tab 11 — Taxonomy ─────────────────────────────────────────────────

	/** @return string[] Tab 11 header row. */
	private static function headers_taxonomy(): array {
		return array( 'Taxonomy', 'Term Name', 'Slug', 'Description', 'Parent Name', 'Meta Key', 'Meta Value' );
	}

	/**
	 * Build all Tab 11 rows from every MBS taxonomy.
	 *
	 * One row per term for terms with no meta; one row per meta key for terms
	 * that have meta. Parent stored as parent term name (empty = root term).
	 *
	 * @return array[] Rows for Tab 11.
	 */
	private static function collect_taxonomy_rows(): array {
		$rows    = array();
		$tax_data = self::collect_taxonomy_data();

		foreach ( $tax_data as $tax_slug => $terms ) {
			foreach ( $terms as $term ) {
				$parent_name = '';
				if ( ! empty( $term['parent'] ) ) {
					$parent_obj  = get_term( (int) $term['parent'], $tax_slug );
					$parent_name = ( $parent_obj instanceof \WP_Term ) ? $parent_obj->name : '';
				}
				$meta = (array) ( $term['term_meta'] ?? array() );
				$sc = 'MBS_Book_Export_Data::safe_cell'; // V6-A-003: formula injection defense.
				if ( empty( $meta ) ) {
					$rows[] = array(
						$sc( $tax_slug ),
						$sc( $term['name'] ),
						$sc( $term['slug'] ),
						$sc( $term['description'] ),
						$sc( $parent_name ),
						'',
						'',
					);
				} else {
					foreach ( $meta as $meta_key => $meta_value ) {
						$meta_str = is_scalar( $meta_value ) ? (string) $meta_value : wp_json_encode( $meta_value );
						$rows[] = array(
							$sc( $tax_slug ),
							$sc( $term['name'] ),
							$sc( $term['slug'] ),
							$sc( $term['description'] ),
							$sc( $parent_name ),
							$sc( $meta_key ),
							$sc( $meta_str ),
						);
					}
				}
			}
		}

		return $rows;
	}

	/**
	 * Collect all MBS taxonomy terms with their metadata.
	 *
	 * Moved from MBS_Exporter_Zip. Returns the nested array used by
	 * collect_taxonomy_rows() to build Tab 11.
	 *
	 * @return array<string, array[]> Keyed by taxonomy slug.
	 */
	private static function collect_taxonomy_data(): array {
		$mbs_taxonomies = array(
			'mbs_author', 'mbs_genre', 'mbs_series', 'mbs_tag',
			'mbs_publisher', 'mbs_imprint', 'mbs_universe', 'mbs_collection',
			'mbs_contributor', 'mbs_theme', 'mbs_content_warning',
		);
		$result = array();
		foreach ( $mbs_taxonomies as $tax ) {
			$terms = get_terms( array( 'taxonomy' => $tax, 'hide_empty' => false ) );
			if ( is_wp_error( $terms ) || empty( $terms ) ) {
				$result[ $tax ] = array();
				continue;
			}
			$result[ $tax ] = array();
			foreach ( $terms as $term ) {
				$result[ $tax ][] = array(
					'term_id'     => $term->term_id,
					'name'        => $term->name,
					'slug'        => $term->slug,
					'description' => $term->description,
					'parent'      => $term->parent,
					'term_meta'   => self::get_term_meta_safe( $term->term_id ),
				);
			}
		}
		return $result;
	}

	/**
	 * Return all non-private term meta for a term.
	 *
	 * Moved from MBS_Exporter_Zip. Filters out _-prefixed WP internal keys.
	 *
	 * @param int $term_id
	 * @return array<string, mixed>
	 */
	private static function get_term_meta_safe( int $term_id ): array {
		$all_meta = get_term_meta( $term_id );
		$result   = array();
		foreach ( (array) $all_meta as $key => $values ) {
			if ( str_starts_with( $key, '_' ) ) { continue; }
			// SA-M7: Restrict unserialize() to non-object types to prevent object injection.
			$result[ $key ] = ( count( $values ) === 1 )
				? ( is_serialized( $values[0] ) ? unserialize( $values[0], array( 'allowed_classes' => false ) ) : $values[0] )
				: array_map(
					static function( $v ) {
						return is_serialized( $v ) ? unserialize( $v, array( 'allowed_classes' => false ) ) : $v;
					},
					$values
				);
		}
		return $result;
	}

	// ── Tab 12 — Manifest ──────────────────────────────────────────────────

	/** @return string[] Tab 12 header row. */
	private static function headers_manifest(): array {
		return array( 'Schema Version', 'Export Date', 'Plugin Version', 'Site URL', 'Book Count' );
	}

	/**
	 * Build the single Tab 12 data row.
	 *
	 * @param int $book_count Total books exported.
	 * @return string[]
	 */
	private static function manifest_row( int $book_count ): array {
		return array(
			MBS_Book_Serializer::SCHEMA_VERSION,
			gmdate( 'c' ),
			MBS_VERSION,
			home_url(),
			(string) $book_count,
		);
	}

	/**
	 * Flatten related_products array to pipe-delimited "ISBN:relation_code" pairs.
	 *
	 * PLAN-C-007: key is 'related_isbn' (not 'isbn').
	 *
	 * @param array $obj MBS Book Object.
	 * @return string
	 */
	private static function flatten_related_products( array $obj ): string {
		$parts = array();
		foreach ( $obj['related_products'] ?? array() as $rp ) {
			$parts[] = ( $rp['related_isbn'] ?? '' ) . ':' . ( $rp['relation_code'] ?? '' );
		}
		return implode( '|', $parts );
	}
}
