<?php
/**
 * XLSX Multi-sheet Exporter — streams SpreadsheetML directly to disk.
 *
 * Rows are appended to per-sheet temporary files as they arrive and the final
 * workbook is assembled with ZipArchive on finish(), so memory usage stays
 * flat regardless of how many rows are exported. The previous implementation
 * built the whole workbook in memory through PhpSpreadsheet and exhausted PHP
 * memory limits on exports beyond a few thousand rows.
 *
 * @package RawTypeOE
 */

namespace RawTypeOE\Free;

// Prevent direct access.
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * Writes export data to an XLSX file using constant memory.
 *
 * Public contract (used by Export_Engine):
 * - start( $file_path, $headers, $options ): bool
 * - write_chunk( $rows ): void
 * - write_entity_chunk( $rows, $entity_name, $headers ): void
 * - finish(): array{file_path: string, size: int, row_count: int, sheet_count: int}
 */
class XLSX_Multisheet_Exporter {

	/** Maximum sheet name length (Excel limit). */
	const MAX_SHEET_NAME_LENGTH = 31;

	/** Cap on the auto-sized column width (Excel character units). */
	const MAX_COLUMN_WIDTH = 60;

	/**
	 * Absolute path of the output file.
	 *
	 * @var string
	 */
	private $file_path;

	/**
	 * Multisheet mode (single, by_group, by_entity).
	 *
	 * @var string
	 */
	private $mode;

	/**
	 * Column key used to split rows into sheets (by_group mode).
	 *
	 * @var string
	 */
	private $group_field;

	/**
	 * Injected group column to drop from output rows.
	 *
	 * @var string
	 */
	private $strip_group_field;

	/**
	 * Default header row applied to new sheets.
	 *
	 * @var array
	 */
	private $headers = array();

	/**
	 * Total data rows written across all sheets.
	 *
	 * @var int
	 */
	private $row_count = 0;

	/**
	 * Whether start() completed successfully.
	 *
	 * @var bool
	 */
	private $started = false;

	/**
	 * Per-sheet streaming state keyed by sheet title:
	 * handle (temp file resource), path (temp file), row (next row number),
	 * headers (header strings), widths (max content length per column).
	 *
	 * @var array
	 */
	private $sheets = array();

	/**
	 * Header rows keyed by sheet title (set before sheet creation).
	 *
	 * @var array
	 */
	private $sheet_headers = array();

	/**
	 * Whether the runtime can generate XLSX files.
	 *
	 * @return bool
	 */
	public static function is_supported() {
		return class_exists( '\ZipArchive' );
	}

	/**
	 * Start a multisheet XLSX export.
	 *
	 * @param string $file_path Absolute path for the output file.
	 * @param array  $headers   Array of column header strings.
	 * @param array  $options   Optional export options (multisheet_mode, group_field, strip_group_field).
	 * @return bool True on success.
	 */
	public function start( $file_path, $headers, $options = array() ) {
		$this->file_path         = $file_path;
		$this->headers           = is_array( $headers ) ? array_values( $headers ) : array();
		$this->row_count         = 0;
		$this->sheets            = array();
		$this->sheet_headers     = array();
		$this->mode              = isset( $options['multisheet_mode'] ) ? sanitize_key( $options['multisheet_mode'] ) : 'single';
		$this->group_field       = isset( $options['group_field'] ) ? Export_Engine::normalize_column_key( $options['group_field'] ) : '';
		$this->strip_group_field = isset( $options['strip_group_field'] ) ? Export_Engine::normalize_column_key( $options['strip_group_field'] ) : '';
		$this->started           = self::is_supported();

		return $this->started;
	}

	/**
	 * Write a chunk of rows.
	 *
	 * @param array $rows Array of associative arrays (each row is field => value).
	 */
	public function write_chunk( $rows ) {
		foreach ( (array) $rows as $row ) {
			$sheet_name = $this->determine_sheet_name( $row );

			if ( ! empty( $this->strip_group_field ) && is_array( $row ) && array_key_exists( $this->strip_group_field, $row ) ) {
				unset( $row[ $this->strip_group_field ] );
			}

			$this->write_row_to_sheet( $sheet_name, array_values( (array) $row ) );
		}
	}

	/**
	 * Write a chunk of rows for a specific entity (by_entity mode).
	 *
	 * @param array  $rows        Array of associative arrays.
	 * @param string $entity_name Sheet name for this entity.
	 * @param array  $headers     Optional header row for this sheet.
	 */
	public function write_entity_chunk( $rows, $entity_name, $headers = array() ) {
		$sheet_name = $this->sanitize_sheet_name( $entity_name );
		if ( ! empty( $headers ) && is_array( $headers ) ) {
			$this->sheet_headers[ $sheet_name ] = array_values( $headers );
		}

		foreach ( (array) $rows as $row ) {
			$this->write_row_to_sheet( $sheet_name, array_values( (array) $row ) );
		}
	}

	/**
	 * Finish the export — assemble the XLSX package on disk.
	 *
	 * @return array Export metadata.
	 */
	public function finish() {
		if ( ! $this->started ) {
			return $this->build_finish_metadata( 0 );
		}

		if ( empty( $this->sheets ) ) {
			$this->create_sheet( $this->sanitize_sheet_name( __( 'Data', 'rawtype-order-exporter-woocommerce' ) ) );
		}

		$zip = new \ZipArchive();
		if ( true !== $zip->open( $this->file_path, \ZipArchive::CREATE | \ZipArchive::OVERWRITE ) ) {
			$this->cleanup_sheet_streams();
			return $this->build_finish_metadata( 0 );
		}

		$sheet_temp_files = array();
		$sheet_index      = 0;

		foreach ( $this->sheets as $sheet ) {
			++$sheet_index;
			$temp_path = $this->compose_worksheet_file( $sheet );

			if ( '' === $temp_path ) {
				continue;
			}

			$sheet_temp_files[] = $temp_path;
			$zip->addFile( $temp_path, 'xl/worksheets/sheet' . $sheet_index . '.xml' );
		}

		$zip->addFromString( '[Content_Types].xml', $this->build_content_types_xml( $sheet_index ) );
		$zip->addFromString( '_rels/.rels', $this->build_package_rels_xml() );
		$zip->addFromString( 'xl/workbook.xml', $this->build_workbook_xml() );
		$zip->addFromString( 'xl/_rels/workbook.xml.rels', $this->build_workbook_rels_xml( $sheet_index ) );
		$zip->addFromString( 'xl/styles.xml', $this->build_styles_xml() );

		$closed = $zip->close();

		foreach ( $sheet_temp_files as $temp_path ) {
			if ( file_exists( $temp_path ) ) {
				unlink( $temp_path ); // phpcs:ignore WordPress.WP.AlternativeFunctions.unlink_unlink
			}
		}

		$size = ( $closed && file_exists( $this->file_path ) ) ? filesize( $this->file_path ) : 0;

		return $this->build_finish_metadata( $size );
	}

	/**
	 * Build the finish() return payload and release sheet state.
	 *
	 * @param int $size Final file size in bytes.
	 * @return array
	 */
	private function build_finish_metadata( $size ) {
		$sheet_count = count( $this->sheets );

		$this->cleanup_sheet_streams();
		$this->sheets        = array();
		$this->sheet_headers = array();
		$this->started       = false;

		return array(
			'file_path'   => $this->file_path,
			'size'        => (int) $size,
			'row_count'   => $this->row_count,
			'sheet_count' => $sheet_count,
		);
	}

	/**
	 * Close and remove any per-sheet temporary data streams.
	 */
	private function cleanup_sheet_streams() {
		foreach ( $this->sheets as $sheet ) {
			if ( isset( $sheet['handle'] ) && is_resource( $sheet['handle'] ) ) {
				fclose( $sheet['handle'] ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose
			}
			if ( ! empty( $sheet['path'] ) && file_exists( $sheet['path'] ) ) {
				unlink( $sheet['path'] ); // phpcs:ignore WordPress.WP.AlternativeFunctions.unlink_unlink
			}
		}
	}

	/**
	 * Determine the sheet name for a row based on mode.
	 *
	 * @param array $row Data row.
	 * @return string
	 */
	private function determine_sheet_name( $row ) {
		if ( 'by_group' === $this->mode && ! empty( $this->group_field ) && is_array( $row ) ) {
			$group_value = isset( $row[ $this->group_field ] ) ? $row[ $this->group_field ] : '';
			return $this->build_group_sheet_name( $group_value );
		}

		return $this->sanitize_sheet_name( __( 'Data', 'rawtype-order-exporter-woocommerce' ) );
	}

	/**
	 * Build a stable sheet name for a grouped row.
	 *
	 * Adds a short hash whenever the raw value needs normalization/truncation so
	 * different groups cannot silently collapse onto the same worksheet title.
	 *
	 * @param mixed $group_value Raw group value.
	 * @return string
	 */
	private function build_group_sheet_name( $group_value ) {
		$raw_value = trim( (string) $group_value );
		if ( '' === $raw_value ) {
			$raw_value = __( 'Other', 'rawtype-order-exporter-woocommerce' );
		}

		$clean = preg_replace( '/[\\\\\\/*?\\[\\]:]+/', '', $raw_value );
		$clean = trim( (string) $clean );

		if ( '' === $clean ) {
			$clean = 'Sheet';
		}

		$needs_hash  = $clean !== $raw_value;
		$is_too_long = function_exists( 'mb_strlen' )
			? mb_strlen( $clean ) > self::MAX_SHEET_NAME_LENGTH
			: strlen( $clean ) > self::MAX_SHEET_NAME_LENGTH;

		if ( ! $needs_hash && ! $is_too_long ) {
			return $this->sanitize_sheet_name( $clean );
		}

		return $this->sanitize_sheet_name_with_hash( $clean, $raw_value );
	}

	/**
	 * Build a collision-resistant sheet name inside Excel's 31-char limit.
	 *
	 * @param string $clean_name Cleaned base name.
	 * @param string $raw_value  Raw source value used for hashing.
	 * @return string
	 */
	private function sanitize_sheet_name_with_hash( $clean_name, $raw_value ) {
		$hash   = substr( md5( (string) $raw_value ), 0, 6 );
		$suffix = '-' . $hash;
		$limit  = self::MAX_SHEET_NAME_LENGTH - strlen( $suffix );

		if ( $limit < 1 ) {
			$limit  = self::MAX_SHEET_NAME_LENGTH;
			$suffix = '';
		}

		if ( function_exists( 'mb_substr' ) ) {
			$clean_name = mb_substr( (string) $clean_name, 0, $limit );
		} else {
			$clean_name = substr( (string) $clean_name, 0, $limit );
		}

		$clean_name = trim( $clean_name );
		if ( '' === $clean_name ) {
			$clean_name = 'Sheet';
		}

		return $clean_name . $suffix;
	}

	/**
	 * Sanitize a sheet name for Excel compatibility.
	 *
	 * @param string $name Raw sheet name.
	 * @return string
	 */
	public function sanitize_sheet_name( $name ) {
		$clean = preg_replace( '/[\\\\\\/*?\\[\\]:]+/', '', (string) $name );
		$clean = trim( $clean );

		if ( function_exists( 'mb_substr' ) ) {
			$clean = mb_substr( $clean, 0, self::MAX_SHEET_NAME_LENGTH );
		} else {
			$clean = substr( $clean, 0, self::MAX_SHEET_NAME_LENGTH );
		}

		if ( '' === $clean ) {
			$clean = 'Sheet';
		}

		return $clean;
	}

	/**
	 * Create the streaming state for a worksheet and write its header row.
	 *
	 * @param string $sheet_name Sheet title (already sanitized).
	 * @return array|null Sheet state, or null when the temp stream cannot open.
	 */
	private function create_sheet( $sheet_name ) {
		$headers = isset( $this->sheet_headers[ $sheet_name ] ) ? $this->sheet_headers[ $sheet_name ] : $this->headers;

		$temp_path = tempnam( sys_get_temp_dir(), 'roe_sheet_' );
		if ( false === $temp_path ) {
			return null;
		}

		// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fopen
		$handle = fopen( $temp_path, 'w+b' );
		if ( false === $handle ) {
			unlink( $temp_path ); // phpcs:ignore WordPress.WP.AlternativeFunctions.unlink_unlink
			return null;
		}

		$sheet = array(
			'name'    => $sheet_name,
			'handle'  => $handle,
			'path'    => $temp_path,
			'row'     => 1,
			'headers' => array_values( $headers ),
			'widths'  => array(),
			'max_col' => max( 1, count( $headers ) ),
		);

		if ( ! empty( $headers ) ) {
			$cells = '';
			foreach ( array_values( $headers ) as $col_index => $header ) {
				$cells                        .= $this->build_inline_string_cell( $col_index, 1, (string) $header, 1 );
				$sheet['widths'][ $col_index ] = $this->estimate_width( (string) $header );
			}
			fwrite( $handle, '<row r="1">' . $cells . '</row>' ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fwrite
			$sheet['row'] = 2;
		}

		$this->sheets[ $sheet_name ] = $sheet;

		return $this->sheets[ $sheet_name ];
	}

	/**
	 * Write a single row to the target sheet's data stream.
	 *
	 * @param string $sheet_name Target sheet title.
	 * @param array  $row        Row values.
	 */
	private function write_row_to_sheet( $sheet_name, $row ) {
		// Row totals stay accurate even when the runtime cannot write (mirrors
		// the engine's expectation that counts reflect processed rows).
		++$this->row_count;

		if ( ! $this->started ) {
			return;
		}

		if ( isset( $this->sheets[ $sheet_name ] ) ) {
			$sheet = &$this->sheets[ $sheet_name ]; // phpcs:ignore Generic.Formatting.MultipleStatementAlignment.NotSameWarning
		} else {
			$created = $this->create_sheet( $sheet_name );
			if ( null === $created ) {
				return;
			}
			$sheet = &$this->sheets[ $sheet_name ]; // phpcs:ignore Generic.Formatting.MultipleStatementAlignment.NotSameWarning
		}

		$row_number = $sheet['row'];
		$cells      = '';

		foreach ( $row as $col_index => $value ) {
			$cells .= $this->build_cell_xml( $col_index, $row_number, $value );

			$width = $this->estimate_width( is_scalar( $value ) ? (string) $value : '' );
			if ( ! isset( $sheet['widths'][ $col_index ] ) || $width > $sheet['widths'][ $col_index ] ) {
				$sheet['widths'][ $col_index ] = $width;
			}
		}

		$col_count = count( $row );
		if ( $col_count > $sheet['max_col'] ) {
			$sheet['max_col'] = $col_count;
		}

		// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fwrite
		fwrite( $sheet['handle'], '<row r="' . $row_number . '">' . $cells . '</row>' );
		$sheet['row'] = $row_number + 1;
	}

	/**
	 * Build the XML for a single cell, choosing the safest data type.
	 *
	 * @param int   $col_index  Zero-based column index.
	 * @param int   $row_number One-based row number.
	 * @param mixed $value      Cell value.
	 * @return string
	 */
	private function build_cell_xml( $col_index, $row_number, $value ) {
		if ( null === $value || '' === $value ) {
			return '';
		}

		$cell_ref = $this->column_letter( $col_index ) . $row_number;

		if ( is_bool( $value ) ) {
			return '<c r="' . $cell_ref . '" t="b"><v>' . ( $value ? '1' : '0' ) . '</v></c>';
		}

		if ( $this->is_formula_injection_candidate( $value ) ) {
			// Force literal text so "=CMD()" in user data is not evaluated as a formula.
			return $this->build_inline_string_cell( $col_index, $row_number, (string) $value );
		}

		if ( $this->should_write_as_number( $value ) ) {
			$string = (string) $value;
			// Two-decimal display format for fractional amounts (style 2).
			$style = false !== strpos( $string, '.' ) ? ' s="2"' : '';

			return '<c r="' . $cell_ref . '"' . $style . '><v>' . $this->escape_xml( $string ) . '</v></c>';
		}

		return $this->build_inline_string_cell( $col_index, $row_number, $this->stringify( $value ) );
	}

	/**
	 * Build an inline-string cell (no shared-strings table, streaming friendly).
	 *
	 * @param int    $col_index  Zero-based column index.
	 * @param int    $row_number One-based row number.
	 * @param string $value      Cell text.
	 * @param int    $style      Optional style index (1 = header style).
	 * @return string
	 */
	private function build_inline_string_cell( $col_index, $row_number, $value, $style = 0 ) {
		$cell_ref  = $this->column_letter( $col_index ) . $row_number;
		$style_xml = $style > 0 ? ' s="' . (int) $style . '"' : '';
		$preserve  = trim( $value ) !== $value ? ' xml:space="preserve"' : '';

		return '<c r="' . $cell_ref . '"' . $style_xml . ' t="inlineStr"><is><t' . $preserve . '>'
			. $this->escape_xml( $value )
			. '</t></is></c>';
	}

	/**
	 * Decide whether a value can be written as a native XLSX number.
	 *
	 * Mirrors spreadsheet conventions: numeric strings with a leading zero
	 * (SKUs, phone numbers, postcodes) must stay text to avoid data loss.
	 *
	 * @param mixed $value Candidate value.
	 * @return bool
	 */
	private function should_write_as_number( $value ) {
		if ( is_int( $value ) || is_float( $value ) ) {
			return true;
		}

		if ( ! is_string( $value ) || ! is_numeric( $value ) ) {
			return false;
		}

		if ( strlen( $value ) > 1 && '0' === $value[0] && '.' !== $value[1] ) {
			return false;
		}

		// Hex/binary notations pass is_numeric() on some PHP builds — keep as text.
		if ( false !== stripos( $value, 'x' ) ) {
			return false;
		}

		return true;
	}

	/**
	 * Decide whether a value would be interpreted as a formula by Excel.
	 *
	 * Spreadsheet apps evaluate any cell whose first character is `=`, `+`,
	 * `-`, `@`, or a leading tab/CR as a formula — a classic data-exfiltration
	 * vector when exported data is opened in Excel.
	 *
	 * @param mixed $value Candidate cell value.
	 * @return bool
	 */
	private function is_formula_injection_candidate( $value ) {
		if ( ! is_string( $value ) || '' === $value ) {
			return false;
		}

		$first = $value[0];

		if ( '=' === $first || '@' === $first || "\t" === $first || "\r" === $first ) {
			return true;
		}

		// `+`/`-` is only dangerous when followed by non-numeric payload; negative prices stay numeric.
		if ( '+' === $first || '-' === $first ) {
			return ! is_numeric( $value );
		}

		return false;
	}

	/**
	 * Compose the final worksheet XML file from the streamed row data.
	 *
	 * @param array $sheet Sheet streaming state.
	 * @return string Path to the composed temp file, or '' on failure.
	 */
	private function compose_worksheet_file( $sheet ) {
		$temp_path = tempnam( sys_get_temp_dir(), 'roe_ws_' );
		if ( false === $temp_path ) {
			return '';
		}

		// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fopen
		$out = fopen( $temp_path, 'wb' );
		if ( false === $out ) {
			unlink( $temp_path ); // phpcs:ignore WordPress.WP.AlternativeFunctions.unlink_unlink
			return '';
		}

		$last_row = max( 1, $sheet['row'] - 1 );
		$last_col = $this->column_letter( max( 1, $sheet['max_col'] ) - 1 );

		$prologue  = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>';
		$prologue .= '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">';
		$prologue .= '<dimension ref="A1:' . $last_col . $last_row . '"/>';
		$prologue .= '<sheetViews><sheetView workbookViewId="0">';
		if ( ! empty( $sheet['headers'] ) ) {
			// Freeze the header row.
			$prologue .= '<pane ySplit="1" topLeftCell="A2" activePane="bottomLeft" state="frozen"/>';
		}
		$prologue .= '</sheetView></sheetViews>';
		$prologue .= '<sheetFormatPr defaultRowHeight="15"/>';
		$prologue .= $this->build_cols_xml( $sheet['widths'] );
		$prologue .= '<sheetData>';

		fwrite( $out, $prologue ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fwrite

		if ( is_resource( $sheet['handle'] ) ) {
			rewind( $sheet['handle'] );
			stream_copy_to_stream( $sheet['handle'], $out );
			fclose( $sheet['handle'] ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose
		}

		if ( ! empty( $sheet['path'] ) && file_exists( $sheet['path'] ) ) {
			unlink( $sheet['path'] ); // phpcs:ignore WordPress.WP.AlternativeFunctions.unlink_unlink
		}

		fwrite( $out, '</sheetData></worksheet>' ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fwrite
		fclose( $out ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose

		return $temp_path;
	}

	/**
	 * Build the <cols> block with approximate auto-sized widths.
	 *
	 * @param array $widths Max content length per zero-based column index.
	 * @return string
	 */
	private function build_cols_xml( $widths ) {
		if ( empty( $widths ) ) {
			return '';
		}

		$xml = '<cols>';
		foreach ( $widths as $col_index => $length ) {
			$width = min( self::MAX_COLUMN_WIDTH, max( 8, $length + 2 ) );
			$col   = $col_index + 1;
			$xml  .= '<col min="' . $col . '" max="' . $col . '" width="' . $width . '" customWidth="1"/>';
		}

		return $xml . '</cols>';
	}

	/**
	 * Estimate display width (in characters) of a cell value.
	 *
	 * @param string $value Cell text.
	 * @return int
	 */
	private function estimate_width( $value ) {
		// Multi-line content only needs to fit its longest line.
		if ( false !== strpos( $value, "\n" ) ) {
			$lines = explode( "\n", $value );
			$value = '';
			foreach ( $lines as $line ) {
				if ( strlen( $line ) > strlen( $value ) ) {
					$value = $line;
				}
			}
		}

		return function_exists( 'mb_strlen' ) ? mb_strlen( $value ) : strlen( $value );
	}

	/**
	 * Build [Content_Types].xml.
	 *
	 * @param int $sheet_count Number of worksheets in the package.
	 * @return string
	 */
	private function build_content_types_xml( $sheet_count ) {
		$xml  = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>';
		$xml .= '<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">';
		$xml .= '<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>';
		$xml .= '<Default Extension="xml" ContentType="application/xml"/>';
		$xml .= '<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>';

		for ( $i = 1; $i <= $sheet_count; $i++ ) {
			$xml .= '<Override PartName="/xl/worksheets/sheet' . $i . '.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>';
		}

		$xml .= '<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>';
		$xml .= '</Types>';

		return $xml;
	}

	/**
	 * Build the package-level _rels/.rels.
	 *
	 * @return string
	 */
	private function build_package_rels_xml() {
		return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
			. '<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/workbook.xml with the sheet index.
	 *
	 * @return string
	 */
	private function build_workbook_xml() {
		$xml  = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>';
		$xml .= '<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">';
		$xml .= '<sheets>';

		$sheet_index = 0;
		foreach ( $this->sheets as $sheet ) {
			++$sheet_index;
			$xml .= '<sheet name="' . $this->escape_xml( $sheet['name'] ) . '" sheetId="' . $sheet_index . '" r:id="rId' . $sheet_index . '"/>';
		}

		$xml .= '</sheets></workbook>';

		return $xml;
	}

	/**
	 * Build xl/_rels/workbook.xml.rels.
	 *
	 * @param int $sheet_count Number of worksheets in the package.
	 * @return string
	 */
	private function build_workbook_rels_xml( $sheet_count ) {
		$xml  = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>';
		$xml .= '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">';

		for ( $i = 1; $i <= $sheet_count; $i++ ) {
			$xml .= '<Relationship Id="rId' . $i . '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet' . $i . '.xml"/>';
		}

		$xml .= '<Relationship Id="rId' . ( $sheet_count + 1 ) . '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>';
		$xml .= '</Relationships>';

		return $xml;
	}

	/**
	 * Build xl/styles.xml.
	 *
	 * Style indexes: 0 = default, 1 = header (bold white on brand blue,
	 * centered), 2 = two-decimal number format (built-in numFmtId 4).
	 *
	 * @return string
	 */
	private function build_styles_xml() {
		return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
			. '<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'
			. '<fonts count="2">'
			. '<font><sz val="11"/><name val="Calibri"/></font>'
			. '<font><b/><color rgb="FFFFFFFF"/><sz val="11"/><name val="Calibri"/></font>'
			. '</fonts>'
			. '<fills count="3">'
			. '<fill><patternFill patternType="none"/></fill>'
			. '<fill><patternFill patternType="gray125"/></fill>'
			. '<fill><patternFill patternType="solid"><fgColor rgb="FF4472C4"/><bgColor rgb="FF4472C4"/></patternFill></fill>'
			. '</fills>'
			. '<borders count="1"><border><left/><right/><top/><bottom/><diagonal/></border></borders>'
			. '<cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs>'
			. '<cellXfs count="3">'
			. '<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>'
			. '<xf numFmtId="0" fontId="1" fillId="2" borderId="0" xfId="0" applyFont="1" applyFill="1" applyAlignment="1"><alignment horizontal="center"/></xf>'
			. '<xf numFmtId="4" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>'
			. '</cellXfs>'
			. '<cellStyles count="1"><cellStyle name="Normal" xfId="0" builtinId="0"/></cellStyles>'
			. '</styleSheet>';
	}

	/**
	 * Convert a zero-based column index to an Excel column letter (A, B, …, AA).
	 *
	 * @param int $col_index Zero-based column index.
	 * @return string
	 */
	private function column_letter( $col_index ) {
		$letter = '';
		$index  = (int) $col_index;

		while ( $index >= 0 ) {
			$letter = chr( 65 + ( $index % 26 ) ) . $letter;
			$index  = intdiv( $index, 26 ) - 1;
		}

		return $letter;
	}

	/**
	 * Escape a value for XML output and strip control characters that would
	 * corrupt the document.
	 *
	 * @param string $value Raw value.
	 * @return string
	 */
	private function escape_xml( $value ) {
		$value = preg_replace( '/[\x00-\x08\x0B\x0C\x0E-\x1F]/', '', (string) $value );

		return htmlspecialchars( $value, ENT_QUOTES | ENT_XML1, 'UTF-8' );
	}

	/**
	 * Convert a non-scalar value to a readable string for a cell.
	 *
	 * @param mixed $value Raw value.
	 * @return string
	 */
	private function stringify( $value ) {
		if ( is_scalar( $value ) ) {
			return (string) $value;
		}

		if ( is_array( $value ) || is_object( $value ) ) {
			$encoded = wp_json_encode( $value );
			return is_string( $encoded ) ? $encoded : '';
		}

		return '';
	}
}
