<?php
/**
 * File Path: includes/export/class-mbs-book-bible-builder.php
 *
 * Book Bible OOXML Builder
 *
 * Builds a multi-sheet OOXML/XLSX workbook for the Book Bible export.
 * Returns raw bytes -- does NOT write to disk. Standalone class; does NOT
 * extend MBS_XLSX_Builder (which is single-sheet, no-style, no-image).
 *
 * Usage:
 *   $b = new MBS_Book_Bible_Builder();
 *   $s = $b->add_sheet( 'Title & Overview' );
 *   $b->set_cell( $s, 0, 0, 'Hello', 6 );
 *   $b->set_cover_image( $bytes, 'jpg', 800, 1200 );
 *   $xlsx_bytes = $b->build();
 *
 * Style indices:
 *   0 Default       — no fill, Calibri 11, black, no border
 *   1 Attribution   — no fill, Calibri 10, #888888, italic, no border
 *   2 Section hdr   — navy fill, Calibri 11, white, bold, no border
 *   3 Label         — light-gray fill, Calibri 10, #444444, no border
 *   4 Data          — white fill, Calibri 11, black, thin border #CCCCCC
 *   5 Amber         — amber fill, Calibri 11, #856404, thin border #F0AD4E
 *   6 Title block   — no fill, Calibri 16, #1B3A6B, bold, no border
 *   7-13 Wrap variants — same as 0-6 but with wrapText="1". Use N+7 for any
 *         style N that needs text wrapping. Handled automatically by set_cell()
 *         $wrap parameter -- callers never reference 7-13 directly.
 *
 * @package MyBookShowroom
 * @since   1.0.0
 */

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

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

	/**
	 * Sheet cell data: [ sheet_idx ][ row ][ col ] => array with keys
	 * v (value), s (style), t (type), m (merge range), w (wrapText).
	 *
	 * @var array<int, array<int, array<int, array<string, mixed>>>>
	 */
	private array $sheets = [];

	/**
	 * Display names, index-matched to $sheets.
	 *
	 * @var string[]
	 */
	private array $sheet_names = [];

	/**
	 * Custom row heights keyed [sheet_idx][row_idx] => float (points).
	 * Rows absent from this array use the sheet default row height.
	 *
	 * @var array<int, array<int, float>>
	 */
	private array $row_heights = [];

	/**
	 * Data-validation rules keyed [sheet_idx][], each entry an associative array
	 * with keys: sqref, type, formula1, error_title, error_msg.
	 *
	 * Populated by set_validation(); emitted by build_datavalidations().
	 *
	 * @var array<int, list<array<string, string>>>
	 */
	private array $validations = [];

	/**
	 * Conditional formatting expression rules: [sheet_idx][], each entry an
	 * associative array with keys: sqref, formula.
	 *
	 * Populated by set_conditional_format(); emitted by build_conditional_formatting().
	 *
	 * @var array<int, list<array<string, string>>>
	 */
	private array $conditional_formats = [];

	/**
	 * Raw image bytes for embedded cover (null = no cover).
	 *
	 * @var string|null
	 */
	private ?string $cover_image_bytes = null;

	/**
	 * Image extension: 'png' or 'jpg'.
	 *
	 * @var string
	 */
	private string $cover_ext = 'png';

	/**
	 * Cover original width in pixels.
	 *
	 * @var int
	 */
	private int $cover_w = 0;

	/**
	 * Cover original height in pixels.
	 *
	 * @var int
	 */
	private int $cover_h = 0;

	/**
	 * Register a new sheet and return its 0-based index.
	 *
	 * @param string $name Sheet display name.
	 * @return int 0-based sheet index.
	 * @since 1.0.0
	 */
	public function add_sheet( string $name ): int {
		$idx                    = count( $this->sheets );
		$this->sheets[]         = [];
		$this->sheet_names[]    = $name;
		return $idx;
	}

	/**
	 * Set a single cell value.
	 *
	 * @param int         $sheet       0-based sheet index.
	 * @param int         $row         0-based row number.
	 * @param int         $col         0-based column number.
	 * @param string      $value       Cell value.
	 * @param int         $style       Style index 0-6.
	 * @param string      $type        OOXML type: 's' (string) or 'n' (number).
	 * @param string|null $merge_range A1-notation merge range for top-left cell.
	 * @param bool        $wrap        Whether to apply wrapText alignment.
	 * @return void
	 * @since 1.0.0
	 */
	public function set_cell(
		int $sheet, int $row, int $col, string $value,
		int $style = 0, string $type = 's',
		?string $merge_range = null, bool $wrap = false
	): void {
		$this->sheets[ $sheet ][ $row ][ $col ] = [
			'v' => $value,
			's' => $style,
			't' => $type,
			'm' => $merge_range,
			'w' => $wrap,
		];
	}

	/**
	 * Attach cover image for embedding in Tab 1.
	 *
	 * @param string $bytes Raw image bytes.
	 * @param string $ext   'png' or 'jpg'.
	 * @param int    $w     Original width in pixels.
	 * @param int    $h     Original height in pixels.
	 * @return void
	 * @since 1.0.0
	 */
	public function set_cover_image( string $bytes, string $ext, int $w, int $h ): void {
		$this->cover_image_bytes = $bytes;
		$this->cover_ext         = $ext;
		$this->cover_w           = $w;
		$this->cover_h           = $h;
	}

	/**
	 * Set a custom row height for a specific sheet and row.
	 *
	 * The height is written as ht="N" customHeight="1" on the <row> element.
	 * Rows without a custom height use Excel's default (15pt).
	 *
	 * @param int   $sheet  0-based sheet index.
	 * @param int   $row    0-based row number.
	 * @param float $height Row height in points.
	 * @return void
	 * @since 1.0.0
	 */
	public function set_row_height( int $sheet, int $row, float $height ): void {
		$this->row_heights[ $sheet ][ $row ] = $height;
	}

	/**
	 * Assemble and return raw XLSX bytes. Caller streams directly -- no disk write.
	 *
	 * @return string Raw XLSX bytes.
	 * @throws RuntimeException If ZipArchive is unavailable or temp file cannot be created.
	 * @since 1.0.0
	 */
	public function build(): string {
		if ( ! class_exists( 'ZipArchive' ) ) {
			throw new \RuntimeException( 'ZipArchive is not available on this server.' );
		}

		$tmp = sys_get_temp_dir() . DIRECTORY_SEPARATOR . 'mbs-bible-' . uniqid() . '.xlsx';
		$zip = new \ZipArchive();
		if ( true !== $zip->open( $tmp, \ZipArchive::CREATE | \ZipArchive::OVERWRITE ) ) {
			throw new \RuntimeException( 'Could not create temp XLSX file.' );
		}

		$zip->addFromString( '[Content_Types].xml',         $this->build_content_types() );
		$zip->addFromString( '_rels/.rels',                 $this->build_dot_rels() );
		$zip->addFromString( 'xl/workbook.xml',             $this->build_workbook_xml() );
		$zip->addFromString( 'xl/_rels/workbook.xml.rels',  $this->build_workbook_rels() );
		$zip->addFromString( 'xl/styles.xml',               $this->build_styles() );

		foreach ( array_keys( $this->sheets ) as $idx ) {
			$zip->addFromString( "xl/worksheets/sheet" . ( $idx + 1 ) . ".xml", $this->build_sheet( $idx ) );
			$rels = $this->build_sheet_rels( $idx );
			if ( '' !== $rels ) {
				$zip->addFromString( "xl/worksheets/_rels/sheet" . ( $idx + 1 ) . ".xml.rels", $rels );
			}
		}

		if ( null !== $this->cover_image_bytes ) {
			$zip->addFromString( 'xl/drawings/drawing1.xml',              $this->build_drawing() );
			$zip->addFromString( 'xl/drawings/_rels/drawing1.xml.rels',   $this->build_drawing_rels() );
			$zip->addFromString( 'xl/media/cover.' . $this->cover_ext,    $this->cover_image_bytes );
		}

		$zip->close();

		$bytes = file_get_contents( $tmp ); // phpcs:ignore WordPress.WP.AlternativeFunctions
		wp_delete_file( $tmp ); // phpcs:ignore WordPress.PHP.NoSilencedErrors
		return ( false !== $bytes ) ? $bytes : '';
	}

	// -------------------------------------------------------------------------
	// Private OOXML assembly — workbook-level
	// -------------------------------------------------------------------------

	/**
	 * Build [Content_Types].xml.
	 *
	 * @return string XML string.
	 * @since 1.0.0
	 */
	private function build_content_types(): string {
		$overrides = '';
		foreach ( array_keys( $this->sheets ) as $idx ) {
			$n           = $idx + 1;
			$overrides  .= '<Override PartName="/xl/worksheets/sheet' . $n . '.xml"'
				. ' ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>' . "\n";
		}

		$drawing_ct = '';
		if ( null !== $this->cover_image_bytes ) {
			$drawing_ct  = '<Override PartName="/xl/drawings/drawing1.xml"'
				. ' ContentType="application/vnd.openxmlformats-officedocument.drawing+xml"/>' . "\n";
			$img_ct      = ( 'png' === $this->cover_ext )
				? 'image/png' : 'image/jpeg';
			$drawing_ct .= '<Default Extension="' . $this->cover_ext . '" ContentType="' . $img_ct . '"/>' . "\n";
		}

		return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . "\n"
			. '<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">' . "\n"
			. '<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>' . "\n"
			. '<Default Extension="xml" ContentType="application/xml"/>' . "\n"
			. '<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>' . "\n"
			. '<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>' . "\n"
			. $overrides
			. $drawing_ct
			. '</Types>';
	}

	/**
	 * Build _rels/.rels.
	 *
	 * @return string XML string.
	 * @since 1.0.0
	 */
	private function build_dot_rels(): string {
		return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . "\n"
			. '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'
			. '<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>'
			. '</Relationships>';
	}

	/**
	 * Build xl/_rels/workbook.xml.rels.
	 *
	 * @return string XML string.
	 * @since 1.0.0
	 */
	private function build_workbook_rels(): string {
		$rels = '';
		foreach ( array_keys( $this->sheets ) as $idx ) {
			$n    = $idx + 1;
			$rels .= '<Relationship Id="rId' . $n . '" '
				. 'Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" '
				. 'Target="worksheets/sheet' . $n . '.xml"/>' . "\n";
		}
		$n_styles = count( $this->sheets ) + 1;
		$rels    .= '<Relationship Id="rId' . $n_styles . '" '
			. 'Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" '
			. 'Target="styles.xml"/>' . "\n";

		return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . "\n"
			. '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">' . "\n"
			. $rels
			. '</Relationships>';
	}

	/**
	 * Build xl/workbook.xml.
	 *
	 * @return string XML string.
	 * @since 1.0.0
	 */
	private function build_workbook_xml(): string {
		$sheets_xml = '';
		foreach ( $this->sheet_names as $idx => $name ) {
			$n           = $idx + 1;
			$sheets_xml .= '<sheet name="' . self::xml_safe( $name ) . '" sheetId="' . $n . '" r:id="rId' . $n . '"/>' . "\n";
		}
		return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . "\n"
			. '<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" '
			. 'xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">'
			. '<sheets>' . "\n" . $sheets_xml . '</sheets>'
			. '</workbook>';
	}

	// -------------------------------------------------------------------------
	// Private OOXML assembly — styles.xml helpers (REF-005)
	// -------------------------------------------------------------------------

	/**
	 * Build xl/styles.xml — delegates each section to a named helper.
	 *
	 * @return string XML string.
	 * @since 1.0.0
	 */
	private function build_styles(): string {
		return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . "\n"
			. '<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'
			. $this->build_number_formats()
			. $this->build_fonts()
			. $this->build_fills()
			. $this->build_borders()
			. '<cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs>'
			. $this->build_xfs()
			. '<cellStyles count="1"><cellStyle name="Normal" xfId="0" builtinId="0"/></cellStyles>'
			. '<dxfs count="1"><dxf><fill><patternFill patternType="solid"><fgColor rgb="FFFFFFFF"/><bgColor rgb="FFFFFFFF"/></patternFill></fill></dxf></dxfs>'
			. '</styleSheet>';
	}

	/**
	 * Build the <numFmts> section.
	 *
	 * No custom number formats are defined; all xf entries use numFmtId="0".
	 *
	 * @return string XML fragment.
	 * @since 1.1.0
	 */
	private function build_number_formats(): string {
		return '<numFmts count="0"/>';
	}

	/**
	 * Build the <fonts> section — 7 font definitions matching style indices 0-6.
	 *
	 * @return string XML fragment.
	 * @since 1.1.0
	 */
	private function build_fonts(): string {
		return '<fonts count="7">'
			. '<font><sz val="11"/><color rgb="FF000000"/><name val="Calibri"/></font>'
			. '<font><sz val="10"/><color rgb="FF888888"/><i/><name val="Calibri"/></font>'
			. '<font><sz val="11"/><color rgb="FFFFFFFF"/><b/><name val="Calibri"/></font>'
			. '<font><sz val="10"/><color rgb="FF444444"/><name val="Calibri"/></font>'
			. '<font><sz val="11"/><color rgb="FF000000"/><name val="Calibri"/></font>'
			. '<font><sz val="11"/><color rgb="FF856404"/><name val="Calibri"/></font>'
			. '<font><sz val="16"/><color rgb="FF1B3A6B"/><b/><name val="Calibri"/></font>'
			. '</fonts>';
	}

	/**
	 * Build the <fills> section.
	 *
	 * Indices 0-1 are reserved by the OOXML spec (none / gray125).
	 * Indices 2-6 map to style fills for section header, label, data, amber, title.
	 *
	 * @return string XML fragment.
	 * @since 1.1.0
	 */
	private function build_fills(): string {
		return '<fills count="7">'
			. '<fill><patternFill patternType="none"/></fill>'
			. '<fill><patternFill patternType="gray125"/></fill>'
			. '<fill><patternFill patternType="solid"><fgColor rgb="FF1B3A6B"/></patternFill></fill>'
			. '<fill><patternFill patternType="solid"><fgColor rgb="FFF2F2F2"/></patternFill></fill>'
			. '<fill><patternFill patternType="solid"><fgColor rgb="FFFFFFFF"/></patternFill></fill>'
			. '<fill><patternFill patternType="solid"><fgColor rgb="FFFFF3CD"/></patternFill></fill>'
			. '<fill><patternFill patternType="solid"><fgColor rgb="FFFFFFFF"/></patternFill></fill>'
			. '</fills>';
	}

	/**
	 * Build the <borders> section.
	 *
	 * 3 entries: none, thin CCCCCC (data cells), thin F0AD4E (amber cells).
	 *
	 * @return string XML fragment.
	 * @since 1.1.0
	 */
	private function build_borders(): string {
		return '<borders count="3">'
			. '<border><left/><right/><top/><bottom/><diagonal/></border>'
			. '<border><left style="thin"><color rgb="FFCCCCCC"/></left>'
			.        '<right style="thin"><color rgb="FFCCCCCC"/></right>'
			.        '<top style="thin"><color rgb="FFCCCCCC"/></top>'
			.        '<bottom style="thin"><color rgb="FFCCCCCC"/></bottom>'
			.        '<diagonal/></border>'
			. '<border><left style="thin"><color rgb="FFF0AD4E"/></left>'
			.        '<right style="thin"><color rgb="FFF0AD4E"/></right>'
			.        '<top style="thin"><color rgb="FFF0AD4E"/></top>'
			.        '<bottom style="thin"><color rgb="FFF0AD4E"/></bottom>'
			.        '<diagonal/></border>'
			. '</borders>';
	}

	/**
	 * Build the <cellXfs> section — 14 entries.
	 *
	 * Indices 0-6 are base styles; indices 7-13 are the same styles with
	 * wrapText="1". set_cell() adds 7 to the style index when $wrap is true.
	 *
	 * @return string XML fragment.
	 * @since 1.1.0
	 */
	private function build_xfs(): string {
		return '<cellXfs count="14">'
			// Base styles (0-6)
			. '<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>'
			. '<xf numFmtId="0" fontId="1" fillId="0" borderId="0" xfId="0"/>'
			. '<xf numFmtId="0" fontId="2" fillId="2" borderId="0" xfId="0">'
			.   '<alignment horizontal="left"/></xf>'
			. '<xf numFmtId="0" fontId="3" fillId="3" borderId="0" xfId="0"/>'
			. '<xf numFmtId="0" fontId="4" fillId="4" borderId="1" xfId="0"/>'
			. '<xf numFmtId="0" fontId="5" fillId="5" borderId="2" xfId="0"/>'
			. '<xf numFmtId="0" fontId="6" fillId="0" borderId="0" xfId="0"/>'
			// Wrap variants (7-13 = 0-6 + wrapText)
			. '<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"><alignment wrapText="1"/></xf>'
			. '<xf numFmtId="0" fontId="1" fillId="0" borderId="0" xfId="0"><alignment wrapText="1"/></xf>'
			. '<xf numFmtId="0" fontId="2" fillId="2" borderId="0" xfId="0"><alignment horizontal="left" wrapText="1"/></xf>'
			. '<xf numFmtId="0" fontId="3" fillId="3" borderId="0" xfId="0"><alignment wrapText="1"/></xf>'
			. '<xf numFmtId="0" fontId="4" fillId="4" borderId="1" xfId="0"><alignment wrapText="1" vertical="top"/></xf>'
			. '<xf numFmtId="0" fontId="5" fillId="5" borderId="2" xfId="0"><alignment wrapText="1" vertical="top"/></xf>'
			. '<xf numFmtId="0" fontId="6" fillId="0" borderId="0" xfId="0"><alignment wrapText="1"/></xf>'
			. '</cellXfs>';
	}

	// -------------------------------------------------------------------------
	// Private OOXML assembly — worksheet helpers (REF-005)
	// -------------------------------------------------------------------------

	/**
	 * Build xl/worksheets/sheet{n}.xml by delegating to three helpers.
	 *
	 * @param int $idx 0-based sheet index.
	 * @return string XML string.
	 * @since 1.0.0
	 */
	private function build_sheet( int $idx ): string {
		$rows_result = $this->build_sheet_rows( $idx );
		return $this->build_sheet_header( $idx )
			. $rows_result['xml']
			. $this->build_sheet_footer( $idx, $rows_result['merges'] );
	}

	/**
	 * Build the XML declaration, <worksheet> opening tag, and <sheetData> opener.
	 *
	 * Includes the xmlns:r namespace declaration only when the sheet has a
	 * drawing reference (sheet index 0 with cover image set).
	 *
	 * @param int $idx 0-based sheet index.
	 * @return string XML fragment.
	 * @since 1.1.0
	 */
	private function build_sheet_header( int $idx ): string {
		$has_drawing = ( 0 === $idx && null !== $this->cover_image_bytes );
		$ns_r        = $has_drawing
			? ' xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"'
			: '';

		return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . "\n"
			. '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"' . $ns_r . '>'
			. '<sheetFormatPr defaultColWidth="20" defaultRowHeight="15"/>'
			. '<sheetData>' . "\n";
	}

	/**
	 * Build the row and cell XML for a single sheet.
	 *
	 * Returns an array with two keys:
	 *   'xml'    => string  — all <row> elements ready to embed inside <sheetData>
	 *   'merges' => array   — A1-notation merge range strings for <mergeCells>
	 *
	 * Also emits empty <row> elements for rows that have a custom height set but
	 * no cell data (height-only rows).
	 *
	 * @param int $idx 0-based sheet index.
	 * @return array{xml: string, merges: string[]}
	 * @since 1.1.0
	 */
	private function build_sheet_rows( int $idx ): array {
		$cells    = $this->sheets[ $idx ] ?? [];
		$sheet_xml = '';
		$merges   = [];

		$row_keys = array_keys( $cells );
		if ( ! empty( $row_keys ) ) {
			sort( $row_keys );
			foreach ( $row_keys as $r ) {
				$col_keys = array_keys( $cells[ $r ] );
				sort( $col_keys );
				$row_xml = '';
				foreach ( $col_keys as $c ) {
					$cell     = $cells[ $r ][ $c ];
					$cell_ref = $this->col_letter( $c ) . ( $r + 1 );
					// Style N+7 = style N with wrapText; handled entirely in styles.xml.
					$s_idx    = (int) $cell['s'] + ( $cell['w'] ? 7 : 0 );
					$s_attr   = ' s="' . $s_idx . '"';
					// Formula cell: emit <f>formula</f><v/> with t="str" for text result.
					if ( isset( $cell['f'] ) && '' !== $cell['f'] ) {
						$t_attr = ' t="str"';
						$v_xml  = '<f>' . self::xml_safe( $cell['f'] ) . '</f><v/>';
					} elseif ( 's' === $cell['t'] ) {
						// Inline string value.
						$t_attr = ' t="inlineStr"';
						$v_xml  = '<is><t xml:space="preserve">' . self::xml_safe( $cell['v'] ) . '</t></is>';
					} else {
						$t_attr = '';
						$v_xml  = '<v>' . self::xml_safe( $cell['v'] ) . '</v>';
					}

					$row_xml .= '<c r="' . $cell_ref . '"' . $s_attr . $t_attr . '>' . $v_xml . '</c>';

					// Collect merge range from top-left cell
					if ( null !== $cell['m'] ) {
						$merges[] = $cell['m'];
					}
				}
				$ht_attr    = isset( $this->row_heights[ $idx ][ $r ] )
					? ' ht="' . $this->row_heights[ $idx ][ $r ] . '" customHeight="1"'
					: '';
				$sheet_xml .= '<row r="' . ( $r + 1 ) . '"' . $ht_attr . '>' . $row_xml . '</row>' . "\n";
			}
		}

		// Emit empty <row> elements for rows that have a custom height but no cells.
		$height_rows  = array_keys( $this->row_heights[ $idx ] ?? [] );
		$emitted_rows = array_keys( $cells );
		foreach ( array_diff( $height_rows, $emitted_rows ) as $hr ) {
			$ht         = $this->row_heights[ $idx ][ $hr ];
			$sheet_xml .= '<row r="' . ( $hr + 1 ) . '" ht="' . $ht . '" customHeight="1"/>' . "\n";
		}

		return [ 'xml' => $sheet_xml, 'merges' => $merges ];
	}

	/**
	 * Build the closing portion of a sheet XML: </sheetData>, optional
	 * <mergeCells>, optional <drawing>, and </worksheet>.
	 *
	 * @param int      $idx    0-based sheet index.
	 * @param string[] $merges A1-notation merge ranges collected by build_sheet_rows().
	 * @return string XML fragment.
	 * @since 1.1.0
	 */
	private function build_sheet_footer( int $idx, array $merges ): string {
		$merge_xml = '';
		if ( ! empty( $merges ) ) {
			$merge_xml = '<mergeCells count="' . count( $merges ) . '">';
			foreach ( $merges as $m ) {
				$merge_xml .= '<mergeCell ref="' . self::xml_safe( $m ) . '"/>';
			}
			$merge_xml .= '</mergeCells>';
		}

		$drawing_ref = '';
		if ( 0 === $idx && null !== $this->cover_image_bytes ) {
			$drawing_ref = '<drawing r:id="rId1"/>';
		}


		$validation_xml = $this->build_datavalidations( $idx );
		$cf_xml         = $this->build_conditional_formatting( $idx );

		return '</sheetData>' . $merge_xml . $cf_xml . $validation_xml . $drawing_ref . '</worksheet>';
	}

	/**
	 * Build xl/worksheets/_rels/sheet{n}.xml.rels for sheets with drawings.
	 * Only sheet index 0 (Tab 1) has a drawing when cover image is set.
	 *
	 * @param int $idx 0-based sheet index.
	 * @return string XML string, or empty string if no drawing.
	 * @since 1.0.0
	 */
	private function build_sheet_rels( int $idx ): string {
		if ( 0 !== $idx || null === $this->cover_image_bytes ) {
			return '';
		}
		return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . "\n"
			. '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'
			. '<Relationship Id="rId1" '
			. 'Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing" '
			. 'Target="../drawings/drawing1.xml"/>'
			. '</Relationships>';
	}

	/**
	 * Build xl/drawings/drawing1.xml — xdr:oneCellAnchor for cover image.
	 * Absolute EMU extent (3 200 000 × 4 800 000) guarantees 2:3 portrait ratio
	 * regardless of column widths. oneCellAnchor ignores cell grid for sizing.
	 *
	 * @return string XML string.
	 * @since 1.0.0
	 */
	private function build_drawing(): string {
		// oneCellAnchor: absolute EMU extent guarantees portrait ratio regardless of
		// column widths. 3 200 000 × 4 800 000 EMU = ~3.5 in × ~5.25 in (2:3 ratio).
		$final_w  = 3200000;  // ~3.5 in wide
		$final_h  = 4800000;  // ~5.25 in tall (2:3 ratio)
		$from_col = 8;        // column I -- past the 8-column data grid (A-H)
		$from_row = 3;

		return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . "\n"
			. '<xdr:wsDr xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing" '
			. 'xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" '
			. 'xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">'
			. '<xdr:oneCellAnchor>'
			. '<xdr:from>'
			. '<xdr:col>' . $from_col . '</xdr:col><xdr:colOff>0</xdr:colOff>'
			. '<xdr:row>' . $from_row . '</xdr:row><xdr:rowOff>0</xdr:rowOff>'
			. '</xdr:from>'
			. '<xdr:ext cx="' . $final_w . '" cy="' . $final_h . '"/>'
			. '<xdr:pic>'
			. '<xdr:nvPicPr>'
			. '<xdr:cNvPr id="2" name="Cover"/>'
			. '<xdr:cNvPicPr><a:picLocks noChangeAspect="1"/></xdr:cNvPicPr>'
			. '</xdr:nvPicPr>'
			. '<xdr:blipFill>'
			. '<a:blip r:embed="rId1"/>'
			. '<a:stretch><a:fillRect/></a:stretch>'
			. '</xdr:blipFill>'
			. '<xdr:spPr>'
			. '<a:xfrm><a:off x="0" y="0"/><a:ext cx="' . $final_w . '" cy="' . $final_h . '"/></a:xfrm>'
			. '<a:prstGeom prst="rect"><a:avLst/></a:prstGeom>'
			. '</xdr:spPr>'
			. '</xdr:pic>'
			. '<xdr:clientData/>'
			. '</xdr:oneCellAnchor>'
			. '</xdr:wsDr>';
	}

	/**
	 * Build xl/drawings/_rels/drawing1.xml.rels.
	 *
	 * @return string XML string.
	 * @since 1.0.0
	 */
	private function build_drawing_rels(): string {
		$ct = ( 'png' === $this->cover_ext )
			? 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/image'
			: 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/image';

		return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . "\n"
			. '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'
			. '<Relationship Id="rId1" Type="' . $ct . '" Target="../media/cover.' . $this->cover_ext . '"/>'
			. '</Relationships>';
	}

	/**
	 * Convert 0-based column index to A1-notation letter(s).
	 *
	 * 0 -> A, 25 -> Z, 26 -> AA, 27 -> AB, etc.
	 *
	 * @param int $col 0-based column index.
	 * @return string Column letter(s).
	 * @since 1.0.0
	 */
	private function col_letter( int $col ): string {
		$letter = '';
		$n      = $col + 1;
		while ( $n > 0 ) {
			$n--;
			$letter = chr( 65 + ( $n % 26 ) ) . $letter;
			$n      = (int) ( $n / 26 );
		}
		return $letter;
	}

	/**
	 * Escape XML special characters for safe embedding in attribute values and content.
	 *
	 * @param string $v Raw string value.
	 * @return string XML-safe string.
	 * @since 1.0.0
	 */

	/**
	 * Escape only amp, lt, gt -- NOT quotes. Safe for element text nodes only.
	 *
	 * Used for <formula1> content in dataValidation elements: the outer
	 * double-quotes that delimit OOXML list options are significant and must
	 * not be entity-encoded as &quot;.
	 *
	 * @param string $v Raw string.
	 * @return string XML text-node-safe string.
	 * @since 1.2.0
	 */
	private static function xml_safe_text( string $v ): string {
		return str_replace( [ '&', '<', '>' ], [ '&amp;', '&lt;', '&gt;' ], $v );
	}

	/**
	 * Register a data-validation rule for a cell or range.
	 *
	 * For list validations $formula1 must be the quoted comma-separated option
	 * string including outer double-quotes, e.g. '"Red,Green,Blue"'. For
	 * textLength validations $formula1 is just the integer limit, e.g. '25'.
	 *
	 * @param int    $sheet       0-based sheet index.
	 * @param string $sqref       Cell ref or range, e.g. 'B25:B31'.
	 * @param string $type        'list' or 'textLength'.
	 * @param string $formula1    OOXML formula1 value.
	 * @param string $error_title Error alert title (empty = no error alert).
	 * @param string $error_msg   Error alert body (empty = no error alert).
	 * @return void
	 * @since 1.2.0
	 */
	public function set_validation(
		int $sheet, string $sqref, string $type,
		string $formula1, string $error_title = '', string $error_msg = ''
	): void {
		$this->validations[ $sheet ][] = [
			'sqref'       => $sqref,
			'type'        => $type,
			'formula1'    => $formula1,
			'error_title' => $error_title,
			'error_msg'   => $error_msg,
		];
	}

	/**
	 * Register an expression-based conditional formatting rule using white fill (dxfId 0).
	 *
	 * Turns the target sqref range white when the formula evaluates to true.
	 * Typical use: '$B25<>""' to clear amber fill once a dropdown has a value.
	 *
	 * @param int    $sheet   0-based sheet index.
	 * @param string $sqref   Cell range, e.g. 'B25:H25'.
	 * @param string $formula Excel formula without leading '=', e.g. '$B25<>""'.
	 * @return void
	 * @since 1.4.0
	 */
	public function set_conditional_format( int $sheet, string $sqref, string $formula ): void {
		$this->conditional_formats[ $sheet ][] = [
			'sqref'   => $sqref,
			'formula' => $formula,
		];
	}

	/**
	 * Set a formula cell.
	 *
	 * The formula string must NOT include a leading '=' — Excel adds that.
	 * Use xml_safe()-compatible characters: the builder will XML-escape the
	 * formula when writing the <f> element, so write '<>' not '&lt;&gt;'.
	 *
	 * @param int         $sheet       0-based sheet index.
	 * @param int         $row         0-based row number.
	 * @param int         $col         0-based column number.
	 * @param string      $formula     Excel formula string (without leading '=').
	 * @param int         $style       Style index 0-6.
	 * @param string|null $merge_range A1-notation merge range for top-left cell.
	 * @param bool        $wrap        Whether to apply wrapText alignment.
	 * @return void
	 * @since 1.3.0
	 */
	public function set_formula(
		int $sheet, int $row, int $col, string $formula,
		int $style = 4, ?string $merge_range = null, bool $wrap = false
	): void {
		$this->sheets[ $sheet ][ $row ][ $col ] = [
			'v' => '',
			'f' => $formula,
			's' => $style,
			't' => 's',
			'm' => $merge_range,
			'w' => $wrap,
		];
	}

	/**
	 * Build the <dataValidations> XML block for the given sheet.
	 *
	 * Returns an empty string when no validations are registered for $idx.
	 * Must appear after </mergeCells> and before <drawing> in worksheet XML
	 * (OOXML ss18.3.1.33).
	 *
	 * @param int $idx 0-based sheet index.
	 * @return string XML fragment.
	 * @since 1.2.0
	 */
	private function build_datavalidations( int $idx ): string {
		$vals = $this->validations[ $idx ] ?? [];
		if ( empty( $vals ) ) {
			return '';
		}
		$xml = '<dataValidations count="' . count( $vals ) . '">';
		foreach ( $vals as $v ) {
			$xml .= '<dataValidation';
			$xml .= ' type="' . self::xml_safe( $v['type'] ) . '"';
			if ( 'textLength' === $v['type'] ) {
				$xml .= ' operator="lessThanOrEqual"';
			}
			$xml .= ' allowBlank="1"';
			if ( '' !== $v['error_title'] || '' !== $v['error_msg'] ) {
				$xml .= ' showErrorMessage="1"';
				$xml .= ' errorTitle="' . self::xml_safe( $v['error_title'] ) . '"';
				$xml .= ' error="' . self::xml_safe( $v['error_msg'] ) . '"';
			}
			$xml .= ' sqref="' . self::xml_safe( $v['sqref'] ) . '"';
			$xml .= '><formula1>' . self::xml_safe_text( $v['formula1'] ) . '</formula1>';
			$xml .= '</dataValidation>';
		}
		$xml .= '</dataValidations>';
		return $xml;
	}

	/**
	 * Build <conditionalFormatting> XML blocks for a sheet.
	 *
	 * Each registered rule uses expression type and dxfId 0 (white fill defined in
	 * build_styles() <dxfs>). Must appear after <dataValidations> and before
	 * <drawing> per OOXML ss18.3.1.12.
	 *
	 * @param int $idx 0-based sheet index.
	 * @return string XML fragment, or empty string when no rules are registered.
	 * @since 1.4.0
	 */
	private function build_conditional_formatting( int $idx ): string {
		$rules = $this->conditional_formats[ $idx ] ?? [];
		if ( empty( $rules ) ) {
			return '';
		}
		$xml      = '';
		$priority = 1;
		foreach ( $rules as $rule ) {
			$xml .= '<conditionalFormatting sqref="' . self::xml_safe( $rule['sqref'] ) . '">';
			$xml .= '<cfRule type="expression" dxfId="0" priority="' . $priority . '">';
			$xml .= '<formula>' . self::xml_safe_text( $rule['formula'] ) . '</formula>';
			$xml .= '</cfRule>';
			$xml .= '</conditionalFormatting>';
			$priority++;
		}
		return $xml;
	}

	private static function xml_safe( string $v ): string {
		return htmlspecialchars( $v, ENT_XML1 | ENT_QUOTES, 'UTF-8' );
	}
}
