<?php
/**
 * File Path: includes/export/class-mbs-book-bible-tab-writer.php
 *
 * Book Bible Tab Writer
 *
 * Populates all six worksheet tabs of a Book Bible workbook. Extracted from
 * MBS_Book_Bible_Exporter (REF-001) to separate sheet-population logic from
 * AJAX routing and data gathering.
 *
 * Usage:
 *   $data    = ( new MBS_Book_Bible_Data_Gatherer() )->gather( $post_id );
 *   $builder = ( new MBS_Book_Bible_Tab_Writer() )->build( $data );
 *   $bytes   = $builder->build();
 *
 * @package MyBookShowroom
 * @since   1.1.0
 */

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


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

	use MBS_Book_Bible_Cell_Helpers;

	/**
	 * Common ONIX edition-type code to human-readable label map (L-001).
	 * Used in Tab 5 (Formats & Retail) and Tab 7 (Copyright Page, PUB tier).
	 *
	 * @since 2.6.0
	 */
	const EDITION_TYPE_LABELS = array(
		'UBR' => 'Unabridged', 'ABR' => 'Abridged',   'ILL' => 'Illustrated',
		'ENH' => 'Enhanced',   'REV' => 'Revised',     'NED' => 'New edition',
		'BRL' => 'Braille',    'LTE' => 'Large print', 'ORG' => 'Original',
		'RPT' => 'Reprint',
	);

	/**
	 * Instantiate and populate a MBS_Book_Bible_Builder with all tabs (6 for PRO, 8 for PUB).
	 *
	 * @param array $data From MBS_Book_Bible_Data_Gatherer::gather().
	 * @return MBS_Book_Bible_Builder
	 * @since 1.1.0
	 */
	public function build( array $data ): MBS_Book_Bible_Builder {
		$b = new MBS_Book_Bible_Builder();
		$this->build_tab1( $b, $data );
		$this->build_tab2( $b, $data );
		$this->build_tab3( $b, $data );
		$this->build_tab4( $b, $data );
		$this->build_tab5( $b, $data );
		$this->build_tab6( $b, $data );
		$this->build_extra_tabs( $b, $data );
		$this->build_specs_sheet( $b );
		if ( '' !== $data['cover']['bytes'] ) {
			$b->set_cover_image(
				$data['cover']['bytes'],
				$data['cover']['ext'],
				$data['cover']['w'],
				$data['cover']['h']
			);
		}
		return $b;
	}

	/**
	 * Factory: return the correct writer subclass for the current license tier.
	 * PUB tier returns MBS_Book_Bible_Tab_Writer_Pub; all others return a base instance.
	 *
	 * @return static
	 * @since  2.6.0
	 */
	public static function make(): static {
		return mbs_is_pub() ? new MBS_Book_Bible_Tab_Writer_Pub() : new static();
	}

	/**
	 * Extension point for tier-specific tabs. No-op in the base class.
	 * Override in a subclass to add sheets between Tab 6 and the Specs lookup tab.
	 *
	 * @param MBS_Book_Bible_Builder $b    Builder instance.
	 * @param array                  $data Gathered book data.
	 * @return void
	 * @since 2.6.0
	 */
	protected function build_extra_tabs( MBS_Book_Bible_Builder $b, array $data ): void {}

	// -------------------------------------------------------------------------
	// Tab builders
	// -------------------------------------------------------------------------

	/**
	 * Tab 1: Title & Overview
	 *
	 * @param MBS_Book_Bible_Builder $b    Builder instance.
	 * @param array                  $data Gathered book data.
	 * @return void
	 */
	private function build_tab1( MBS_Book_Bible_Builder $b, array $data ): void {
		$s = $b->add_sheet( 'Title & Overview' );
		$this->colophon( $b, $s, $data );

		$this->hdr( $b, $s, 3, 'PUBLICATION DATA' );

		// Row 4: Title / Tagline
		$this->two( $b, $s, 4,
			'Title',     $data['title'],   false,
			'Tagline',   $data['tagline'], true
		);
		// Row 5: Publisher / Imprint
		$this->two( $b, $s, 5,
			'Publisher', $data['publisher'], false,
			'Imprint',   $data['imprint'],   true
		);
		// Row 6: Pub Date / Copyright Year (both expected)
		$this->two( $b, $s, 6,
			'Pub Date',       $data['pub_date'],       true,
			'Copyright Year', $data['copyright_year'], true
		);
		// Row 7: Coming Soon / Edition Stmt
		$this->two( $b, $s, 7,
			'Coming Soon',  $data['coming_soon'] ? 'Yes' : 'No', false,
			'Edition Stmt', $data['edition_stmt'],                false
		);
		// Row 8: Series / Series #
		$this->two( $b, $s, 8,
			'Series',   $data['series_name'],   false,
			'Series #', $data['series_number'], false
		);
		// Row 9: LCCN (amber if empty) / Pub Reference (amber always)
		$this->two_ds( $b, $s, 9,
			'LCCN',          $data['lccn'],     ( '' === $data['lccn'] ) ? 5 : 4,
			'Pub Reference', $data['pub_ref'],  5
		);
		// Row 10: Admin Notes full-width wrapText
		$this->lbl( $b, $s, 10, 'Admin Notes', $data['admin_notes'], 1, 7, false, true );

		$this->sp( $b, $s, 11 );
		$this->hdr( $b, $s, 12, 'BOOK BIBLE COMPLETENESS' );

		// Row 13: completeness summary (pre-computed by MBS_Book_Bible_Data_Gatherer::gather())
		$comp  = $data['completion'];
		$ctxt  = "Completeness: {$comp['score']}/14";
		if ( ! empty( $comp['missing'] ) ) {
			$ctxt .= ' -- Review: ' . implode( ', ', $comp['missing'] );
		}
		$cstyle = $comp['score'] >= 12 ? 4 : ( $comp['score'] >= 8 ? 5 : 2 );
		$this->fill( $b, $s, 13, 0, 7, $ctxt, $cstyle );
	}

	/**
	 * Tab 2: Marketing
	 *
	 * @param MBS_Book_Bible_Builder $b    Builder instance.
	 * @param array                  $data Gathered book data.
	 * @return void
	 */
	private function build_tab2( MBS_Book_Bible_Builder $b, array $data ): void {
		$s = $b->add_sheet( 'Marketing' );
		$this->colophon( $b, $s, $data );

		$this->hdr( $b, $s, 3, 'MARKETING COPY' );

		// Row 4: Tagline
		$this->lbl( $b, $s, 4, 'Tagline', $data['tagline'], 1, 7, true );

		// Row 5: "Teaser / Short Blurb" label merged cols 0-1, style 3
		$this->fill( $b, $s, 5, 0, 1, 'Teaser / Short Blurb', 3 );

		// Rows 6-8: teaser (0-7 merge, wrapText)
		$this->mrow( $b, $s, 6, 8, 0, 7, $data['teaser'], ( '' === $data['teaser'] ) ? 5 : 4 );

		// Row 9: "Synopsis / Back Cover Copy" label merged cols 0-1, style 3
		$this->fill( $b, $s, 9, 0, 1, 'Synopsis / Back Cover Copy', 3 );

		// Rows 10-18: synopsis (0-7 merge, wrapText)
		$this->mrow( $b, $s, 10, 18, 0, 7, $data['synopsis'], ( '' === $data['synopsis'] ) ? 5 : 4 );

		$this->sp( $b, $s, 19 );
		$this->hdr( $b, $s, 20, 'GENRE & TAGS' );

		$this->lbl( $b, $s, 21, 'Genre', implode( ', ', $data['genre_terms'] ), 1, 7, false );
		$this->lbl( $b, $s, 22, 'Tags',  implode( ', ', $data['tag_terms'] ),   1, 7, false );

		$this->sp( $b, $s, 23 );
		$this->hdr( $b, $s, 24, 'KDP KEYWORDS (7 max)' );

		for ( $i = 0; $i < 7; $i++ ) {
			$this->lbl( $b, $s, 25 + $i,
				'Keyword ' . ( $i + 1 ),
				$data['kdp_keywords'][ $i ],
				1, 7, true
			);
		}

		// Validate: KDP keywords must be 25 characters or fewer (including spaces).
		$b->set_validation( $s, 'B26:B32', 'textLength', '25',
			'Keyword Too Long',
			'KDP keywords must be 25 characters or fewer, including spaces.' );

		// Row 32: italic attribution note
		$this->fill( $b, $s, 32, 0, 7,
			'Source: post tags (taxonomy: mbs_tag) -- verify before KDP submission', 1 );
	}

	/**
	 * Tab 3: Classification
	 *
	 * @param MBS_Book_Bible_Builder $b    Builder instance.
	 * @param array                  $data Gathered book data.
	 * @return void
	 */
	private function build_tab3( MBS_Book_Bible_Builder $b, array $data ): void {
		$s = $b->add_sheet( 'Classification' );
		$this->colophon( $b, $s, $data );

		// ---- BISAC codes ----
		$this->hdr( $b, $s, 3, 'BISAC CATEGORIES' );
		for ( $i = 0; $i < 3; $i++ ) {
			$code = $data['bisac_codes'][ $i ] ?? '';
			$desc = $code ? ( $data['bisac_descriptions'][ $code ] ?? '' ) : '';
			$val  = ( $code && $desc ) ? "{$code} -- {$desc}" : $code;
			$this->lbl( $b, $s, 4 + $i, 'BISAC ' . ( $i + 1 ), $val, 1, 7, true );
		}

		// ---- Thema codes ----
		$this->sp( $b, $s, 7 );
		$this->hdr( $b, $s, 8, 'THEMA CODES' );
		for ( $i = 0; $i < 3; $i++ ) {
			$code = $data['thema_codes'][ $i ] ?? '';
			$desc = $code ? ( $data['thema_descriptions'][ $code ] ?? '' ) : '';
			$val  = ( $code && $desc ) ? "{$code} -- {$desc}" : $code;
			$this->lbl( $b, $s, 9 + $i, 'THEMA ' . ( $i + 1 ), $val, 1, 7, true );
		}

		// ---- BIC codes (dynamic count -- derived from Thema via mbs_bic_thema_map) ----
		$row = 12;
		$this->sp( $b, $s, $row++ );
		$this->hdr( $b, $s, $row++, 'BIC CODES' );
		if ( empty( $data['bic_codes'] ) ) {
			$this->fill( $b, $s, $row++, 0, 7,
				'(No BIC codes -- add Thema codes above to generate BIC mapping)', 5 );
		} else {
			$bic_display = 0;
			foreach ( $data['bic_codes'] as $bic ) {
				$bc = $bic['bic_code'];
				// H-005: Thema qualifier codes start with a digit -- not valid BIC codes.
				if ( isset( $bc[0] ) && ctype_digit( $bc[0] ) ) {
					continue;
				}
				$desc = $data['bic_descriptions'][ $bc ] ?? '';
				$val  = $desc ? "{$bc} -- {$desc}" : $bc;
				$this->lbl( $b, $s, $row++, 'BIC ' . ( ++$bic_display ), $val, 1, 7, false );
			}
			if ( 0 === $bic_display ) {
				$this->fill( $b, $s, $row++, 0, 7,
					'(No valid BIC codes -- Thema qualifier codes filtered out)', 5 );
			}
		}

		// ---- Bowker codes ----
		$this->sp( $b, $s, $row++ );
		$this->hdr( $b, $s, $row++, 'BOWKER CATEGORIES' );
		for ( $i = 0; $i < 2; $i++ ) {
			$this->lbl( $b, $s, $row++, 'Bowker ' . ( $i + 1 ),
				$data['bowker_codes'][ $i ] ?? '', 1, 7, true );
		}

		// ---- Audience & Age ----
		$this->sp( $b, $s, $row++ );
		$this->hdr( $b, $s, $row++, 'AUDIENCE & AGE' );
		// Decode ONIX audience code to human-readable label.
		$onix_audience_labels = array(
			'01' => 'General / Trade',
			'02' => 'Children / Juvenile',
			'03' => 'Young Adult',
			'04' => 'Primary / Elementary',
			'05' => 'Secondary / High School',
			'06' => 'Tertiary / Higher Education',
			'07' => 'Professional / Vocational',
			'08' => 'Scholarly / Academic',
		);
		$aud_code  = (string) $data['audience'];
		$aud_label = $onix_audience_labels[ $aud_code ] ?? '';
		$aud_val   = '' !== $aud_label ? "{$aud_code} -- {$aud_label}" : $aud_code;
		$this->lbl( $b, $s, $row++, 'Audience', $aud_val, 1, 7, false );
		$from    = $data['age_range_from'];
		$to      = $data['age_range_to'];
		$age_set = static fn( string $v ): bool => '' !== $v && '0' !== $v;
		$age     = ( $age_set( $from ) || $age_set( $to ) ) ? "{$from} -- {$to}" : '';
		$this->lbl( $b, $s, $row++, 'Age Range', $age, 1, 7, false );

		// ---- LC Cataloging ----
		$this->sp( $b, $s, $row++ );
		$this->hdr( $b, $s, $row++, 'LC CATALOGING' );
		$name_inv = $data['author_data'][0]['name_inv']   ?? '';
		$birth_yr = $data['author_data'][0]['birth_year'] ?? '';
		$this->lbl( $b, $s, $row++, 'LCCN',               $data['lccn'], 1, 7, true );
		$this->lbl( $b, $s, $row++, 'LC Name (inverted)', $name_inv,     1, 7, true );
		$this->lbl( $b, $s, $row++, 'Author Birth Year',  $birth_yr,     1, 7, true );
		$this->fill( $b, $s, $row++, 0, 1, 'LC CIP Block', 3 );
		$cip_start = $row;
		$cip_end   = $row + 2;
		if ( '' !== $name_inv && '' !== $birth_yr ) {
			$author_nm = $data['author_data'][0]['name'] ?? '';
			$cip       = "{$name_inv}, {$birth_yr}-\n  {$data['title']} / {$author_nm}.";
			$this->mrow( $b, $s, $cip_start, $cip_end, 0, 7, $cip, 4 );
		} else {
			$this->mrow( $b, $s, $cip_start, $cip_end, 0, 7,
				'[Name inverted and birth year required -- see LC Cataloging above]', 5 );
		}
	}

	/**
	 * Tab 4: Contributors
	 *
	 * @param MBS_Book_Bible_Builder $b    Builder instance.
	 * @param array                  $data Gathered book data.
	 * @return void
	 */
	private function build_tab4( MBS_Book_Bible_Builder $b, array $data ): void {
		$s = $b->add_sheet( 'Contributors' );
		$this->colophon( $b, $s, $data );

		$this->hdr( $b, $s, 3, 'PRIMARY AUTHOR(S)' );

		$row = 4;
		if ( empty( $data['author_data'] ) ) {
			$this->fill( $b, $s, $row, 0, 7,
				'No primary author set -- add in Book Editor.', 5 );
			$row++;
		} else {
			foreach ( $data['author_data'] as $a ) {
				$this->lbl( $b, $s, $row,     'Name',            $a['name'],       1, 7, false );
				$this->lbl( $b, $s, $row + 1, 'Name (inverted)', $a['name_inv'],   1, 7, true );
				$this->lbl( $b, $s, $row + 2, 'Birth Year',      $a['birth_year'], 1, 7, true );
				$this->lbl( $b, $s, $row + 3, 'Biography',       $a['bio'],        1, 7, true, true );
				$this->sp( $b, $s, $row + 4 );
				$row += 5;
			}
		}

		$this->hdr( $b, $s, $row, 'ADDITIONAL CONTRIBUTORS' );
		$row++;

		// Column header row
		$b->set_cell( $s, $row, 0, 'Role', 3 );
		$this->fill( $b, $s, $row, 1, 3, 'Name', 3 );
		$this->fill( $b, $s, $row, 4, 7, 'Statement', 3 );
		$row++;

		if ( empty( $data['contributors'] ) ) {
			$this->fill( $b, $s, $row, 0, 7,
				'No additional contributors -- add in Book Editor.', 5 );
		} else {
			foreach ( $data['contributors'] as $c ) {
				$role = (string) ( $c->role_label ?? ( $c->onix_role_code ?? '' ) );
				$name = (string) ( $c->contributor_name ?? '' );
				$stmt = (string) ( $c->statement ?? '' );
				$b->set_cell( $s, $row, 0, $role, 4 );
				$this->fill( $b, $s, $row, 1, 3, $name, 4 );
				$this->fill( $b, $s, $row, 4, 7, $stmt, 4, true );
				$row++;
			}
		}
	}

	/**
	 * Tab 5: Formats & Retail
	 *
	 * @param MBS_Book_Bible_Builder $b    Builder instance.
	 * @param array                  $data Gathered book data.
	 * @return void
	 */
	private function build_tab5( MBS_Book_Bible_Builder $b, array $data ): void {
		$s = $b->add_sheet( 'Formats & Retail' );
		$this->colophon( $b, $s, $data );

		$this->hdr( $b, $s, 3, 'FORMATS & EDITIONS' );

		$row = 4;
		if ( empty( $data['format_rows'] ) ) {
			$this->fill( $b, $s, $row, 0, 7,
				'No editions on record -- add in Book Editor.', 5 );
			$row++;
		} else {
			foreach ( $data['format_rows'] as $r ) {
				$label = strtoupper( $r->format_label ?: ( $r->format ?? '' ) );
				// +0: format sub-header (style 2)
				$this->fill( $b, $s, $row, 0, 7, $label, 2 );
				// +1: ISBN-13 (amber if empty)
				$this->lbl( $b, $s, $row + 1, 'ISBN-13',
					(string) ( $r->isbn13 ?? '' ), 1, 7, true );
				// +2: ASIN
				$this->lbl( $b, $s, $row + 2, 'ASIN',
					(string) ( $r->asin ?? '' ), 1, 7, false );
				// +3: Subtitle
				$this->lbl( $b, $s, $row + 3, 'Subtitle',
					(string) ( $r->subtitle ?? '' ), 1, 7, false );
				// +4: Page Count (amber if 0 or null)
				$pg = ( ! empty( $r->page_length ) ) ? (string) (int) $r->page_length : '';
				$this->lbl( $b, $s, $row + 4, 'Page Count', $pg, 1, 7, true );
				// +5: Edition Number
				$en = ( isset( $r->edition_number ) && null !== $r->edition_number )
					? (string) (int) $r->edition_number : '';
				$this->lbl( $b, $s, $row + 5, 'Edition Number', $en, 1, 7, false );
				// +6: Edition Type -- decode ONIX code to human-readable label (L-001).
				$et_raw   = strtoupper( (string) ( $r->edition_type ?? '' ) );
				$et_label = self::EDITION_TYPE_LABELS[ $et_raw ] ?? '';
				$et_val   = '' !== $et_label ? "{$et_raw} -- {$et_label}" : (string) ( $r->edition_type ?? '' );
				$this->lbl( $b, $s, $row + 6, 'Edition Type', $et_val, 1, 7, false );
				// +7: Pub Date (per-edition)
				$this->lbl( $b, $s, $row + 7, 'Pub Date',
					(string) ( $r->pub_date ?? '' ), 1, 7, false );
				// +8 through +13: PUB supply-chain fields.
				// Always emitted so the full field set is visible in the Book Bible;
				// fields will be empty for FREE/PRO tiers.
				$this->lbl( $b, $s, $row + 8,  'Supplier Name',
					(string) ( $r->supplier_name ?? '' ), 1, 7, false );
				$this->lbl( $b, $s, $row + 9,  'Price Type',
					(string) ( $r->price_type    ?? '' ), 1, 7, false );
				$this->lbl( $b, $s, $row + 10, 'Currency',
					(string) ( $r->currency_code ?? '' ), 1, 7, false );
				$this->lbl( $b, $s, $row + 11, 'Discount Code',
					(string) ( $r->discount_code ?? '' ), 1, 7, false );
				$this->lbl( $b, $s, $row + 12, 'On-Sale Date',
					(string) ( $r->on_sale_date  ?? '' ), 1, 7, false );
				$this->lbl( $b, $s, $row + 13, 'Returnability',
					(string) ( $r->returnability ?? '' ), 1, 7, false );
				// +14: spacer
				$this->sp( $b, $s, $row + 14 );
				$row += 15;
			}
		}

		$this->hdr( $b, $s, $row, 'BUY LINKS' );
		$row++;

		// Column header row
		$b->set_cell( $s, $row, 0, 'Retailer', 3 );
		$this->fill( $b, $s, $row, 1, 5, 'URL', 3 );
		$this->fill( $b, $s, $row, 6, 7, 'Scope', 3 );
		$row++;

		if ( empty( $data['buy_links'] ) ) {
			$this->fill( $b, $s, $row, 0, 7, 'No buy links -- add in Book Editor.', 5 );
		} else {
			foreach ( $data['buy_links'] as $bl ) {
				$scope = ( null === $bl->format_row_id ) ? 'Shared' : 'Per-edition';
				$key   = (string) ( $bl->retailer_key ?? '' );
				if ( '' === $key && ! empty( $bl->url ) ) {
					$parsed = wp_parse_url( (string) $bl->url );
					$key    = $parsed['host'] ?? '';
				}
				$b->set_cell( $s, $row, 0, $key, 4 );
				$this->fill( $b, $s, $row, 1, 5, (string) ( $bl->url ?? '' ), 4 );
				$this->fill( $b, $s, $row, 6, 7, $scope, 4 );
				$row++;
			}
		}

		$this->sp( $b, $s, $row );
		$row++;

		$this->hdr( $b, $s, $row, 'READER PLATFORMS' );
		$row++;

		// Column header row
		$b->set_cell( $s, $row, 0, 'Platform', 3 );
		$this->fill( $b, $s, $row, 1, 7, 'URL', 3 );
		$row++;

		if ( empty( $data['platforms'] ) ) {
			$this->fill( $b, $s, $row, 0, 7, 'No reader platform links -- add in Book Editor.', 5 );
		} else {
			foreach ( $data['platforms'] as $pl ) {
				$key = (string) ( $pl->retailer_key ?? '' );
				if ( '' === $key && ! empty( $pl->link_label ) ) {
					$key = (string) $pl->link_label;
				}
				if ( '' === $key && ! empty( $pl->url ) ) {
					$parsed = wp_parse_url( (string) $pl->url );
					$key    = $parsed['host'] ?? '';
				}
				$b->set_cell( $s, $row, 0, $key, 4 );
				$this->fill( $b, $s, $row, 1, 7, (string) ( $pl->url ?? '' ), 4 );
				$row++;
			}
		}
	}

	/**
	 * Tab 6: Production Notes
	 *
	 * @param MBS_Book_Bible_Builder $b    Builder instance.
	 * @param array                  $data Gathered book data.
	 * @return void
	 */
	private function build_tab6( MBS_Book_Bible_Builder $b, array $data ): void {
		$s = $b->add_sheet( 'Production Notes' );
		$this->colophon( $b, $s, $data );

		$this->hdr( $b, $s, 3, 'PRODUCTION NOTES' );

		// Row 4: Publisher Reference (amber always)
		$b->set_cell( $s, 4, 0, 'Publisher Reference', 3 );
		$this->fill( $b, $s, 4, 1, 7, $data['pub_ref'], 5 );

		// Row 5: formula note (style 1 attribution, 0-7 merge)
		$this->fill( $b, $s, 5, 0, 7,
			'Formula: [Author Initials]_[Series Initials][#]_[Title Initials] -- verify before use', 1 );

		$this->sp( $b, $s, 6 );
		$this->hdr( $b, $s, 7, 'RELATED PRODUCTS / READING ORDER' );

		// Column header row
		$b->set_cell( $s, 8, 0, 'Relation Code', 3 );
		$this->fill( $b, $s, 8, 1, 4, 'ISBN-13', 3 );
		$this->fill( $b, $s, 8, 5, 7, 'Format', 3 );

		$row = 9;
		if ( empty( $data['related'] ) ) {
			$this->fill( $b, $s, $row, 0, 7, 'No related products on record.', 5 );
			$row++;
		} else {
			foreach ( $data['related'] as $r ) {
				$b->set_cell( $s, $row, 0, (string) ( $r->relation_code  ?? '' ), 4 );
				$this->fill( $b, $s, $row, 1, 4, (string) ( $r->related_isbn   ?? '' ), 4 );
				$this->fill( $b, $s, $row, 5, 7, (string) ( $r->related_format ?? '' ), 4 );
				$row++;
			}
		}

		// PRINT PRODUCTION SPECS section (dynamic offset)
		$this->hdr( $b, $s, $row, 'PRINT PRODUCTION SPECS (MANUAL)' );
		$row++;

		// Dropdown options for each print production spec (OOXML list validation).
		$spec_opts = array(
			'Trim Size'    => '"6 × 9,5 × 8,5.5 × 8.5,5.25 × 8,5.06 × 7.81,6.14 × 9.21,6.69 × 9.61,7 × 10,7.44 × 9.69,7.5 × 9.25,8 × 10,8.25 × 6,8.25 × 8.25"',
			'Paper Type'   => '"White,Cream"',
			'Binding'      => '"Paperback — Perfect Bound,Hardback — Case Laminate"',
			'Bleed'        => '"Bleed,No Bleed"',
			'Color Mode'   => '"Black & White,Standard Color"',
			'Cover Finish' => '"Gloss,Matte"',
		);
		foreach ( $spec_opts as $spec => $opts ) {
			$b->set_cell( $s, $row, 0, $spec, 3 );
			if ( 'Trim Size' === $spec ) {
				// Col B: amber dropdown (single cell — description auto-fills C-H via formula).
				$b->set_cell( $s, $row, 1, '', 5 );
				// Cols C-H: merged formula — looks up full description from Specs lookup sheet.
				$excel_row = $row + 1;
				$formula   = 'IF(B' . $excel_row . '<>"",INDEX(Specs!$B$2:$B$14,MATCH(B' . $excel_row . ',Specs!$A$2:$A$14,0)),"")';
				$this->fill_formula( $b, $s, $row, 2, 7, $formula, 4 );
			} else {
				$this->fill( $b, $s, $row, 1, 7, '', 5 );
			}
			$b->set_validation( $s, 'B' . ( $row + 1 ), 'list', $opts );
			// Turn the cell(s) white once a selection is made (CF expression, dxfId 0).
			$excel_r = $row + 1;
			if ( 'Trim Size' === $spec ) {
				$b->set_conditional_format( $s, 'B' . $excel_r, '$B' . $excel_r . '<>""' );
			} else {
				$b->set_conditional_format( $s, 'B' . $excel_r . ':H' . $excel_r, '$B' . $excel_r . '<>""' );
			}
			$row++;
		}

		// Italic note
		$this->fill( $b, $s, $row, 0, 7,
			'Print specs not stored in My Book Showroom -- enter from your print provider.', 1 );
	}

	/**
	 * Build the Specs lookup sheet (used by Tab 6 Trim Size formula lookup).
	 *
	 * Column A = short trim size code (e.g. "6 x 9").
	 * Column B = full description with inches and centimetres.
	 * Rows 2-14 correspond to INDEX/MATCH ranges Specs!$B$2:$B$14 and Specs!$A$2:$A$14.
	 *
	 * @param MBS_Book_Bible_Builder $b Builder instance.
	 * @return void
	 * @since 1.3.0
	 */
	private function build_specs_sheet( MBS_Book_Bible_Builder $b ): void {
		$s = $b->add_sheet( 'Specs' );

		// Header row (row 0 = Excel row 1).
		$b->set_cell( $s, 0, 0, 'Code',        2 );
		$b->set_cell( $s, 0, 1, 'Description', 2 );

		// Trim size data -- 13 entries matching Tab 6 dropdown options.
		// Row indices 1-13 = Excel rows 2-14 (matches Specs!$A$2:$A$14 formula ranges).
		$trim_sizes = array(
			array( '6 × 9',       '6 in. × 9 in. (15.24 cm × 22.86 cm)'      ),
			array( '5 × 8',       '5 in. × 8 in. (12.70 cm × 20.32 cm)'      ),
			array( '5.5 × 8.5',   '5.5 in. × 8.5 in. (13.97 cm × 21.59 cm)' ),
			array( '5.25 × 8',    '5.25 in. × 8 in. (13.34 cm × 20.32 cm)'  ),
			array( '5.06 × 7.81', '5.06 in. × 7.81 in. (12.85 cm × 19.84 cm)' ),
			array( '6.14 × 9.21', '6.14 in. × 9.21 in. (15.60 cm × 23.39 cm)' ),
			array( '6.69 × 9.61', '6.69 in. × 9.61 in. (16.99 cm × 24.41 cm)' ),
			array( '7 × 10',      '7 in. × 10 in. (17.78 cm × 25.40 cm)'     ),
			array( '7.44 × 9.69', '7.44 in. × 9.69 in. (18.90 cm × 24.61 cm)' ),
			array( '7.5 × 9.25',  '7.5 in. × 9.25 in. (19.05 cm × 23.50 cm)' ),
			array( '8 × 10',      '8 in. × 10 in. (20.32 cm × 25.40 cm)'     ),
			array( '8.25 × 6',    '8.25 in. × 6 in. (20.96 cm × 15.24 cm)'  ),
			array( '8.25 × 8.25', '8.25 in. × 8.25 in. (20.96 cm × 20.96 cm)' ),
		);

		foreach ( $trim_sizes as $i => $pair ) {
			$b->set_cell( $s, $i + 1, 0, $pair[0], 4 );
			$b->set_cell( $s, $i + 1, 1, $pair[1], 4 );
		}
	}
}
