<?php
/**
 * A table's export (0.76.0, CONTRACT §8.5 "The export"): the request an export link carries, parsed into one
 * validated shape (request()), and the two writers that stream the file (xlsx(), csv(); write_xlsx() and write_csv()
 * write to any stream). The owner's two options, opt-in per table through Ui::table()'s `export` (2 Nov 2026):
 * `columns => 'visible'` (the file follows the person's Columns choice) and `views => 'sheets'` (the XLSX holds every
 * view of the table's view pills, one sheet each); without them an export is what it was, every matching row of the
 * view with every column.
 *
 * Credits. The XLSX writer is Rooster LMS's (rooster-lms/includes/Spreadsheet/XlsxWriter.php and ZipWriter.php,
 * themselves ported from LMS RM 2.0.0's LMS_RM_Xlsx_Stream), the better of the ecosystem's two: each sheet's rows
 * spooled to php://temp as they come (2 MB in memory, then disk), inline strings (no shared-strings table in memory),
 * its own streaming ZIP (deflate_init() and a CRC-32 context; no ZipArchive, no Zip64, no data descriptors), so memory
 * stays flat whatever the row count; a bold header on a light fill, the column widths from the longest value. What
 * this one adds: the header bold, frozen and filtered on EVERY sheet (the LMS's froze and filtered sheet 1 only),
 * Excel's sheet name rules with unique names, and Rooster One's lessons (ai-eshop-optimizer's AIEO_Report_Export and
 * AIEO_DMM_XLSX_Writer, B16 and B17): a cell's type is its PHP type or its column's declared one, never sniffed from
 * its text (a SKU 0012 or a postcode stays text), an integer past Excel's 15 digits stays text, the output buffers
 * are dropped before the file so nothing stray lands in it. The CSV writer is the LMS's CsvWriter: UTF-8 with a BOM
 * (Excel opens Greek right), and a text cell that starts with = + - @, a tab or a carriage return written with a
 * leading apostrophe (formula injection; a number never is). Rooster One's report export writes neither guard (its
 * BOM is declared per report, since an import API breaks on one), so `bom` is an option here.
 *
 * 0.77.0, the formats (the owner, 2 Nov 2026, for Rooster One's eleven exports on AIEO_DMM_XLSX_Writer): a column's
 * `format`, Ui::table()'s own (its totals'), shows its numbers in the XLSX as the table says them: 'money' the shop's
 * currency (WooCommerce's symbol, position and decimals, a loss in red: that writer's numFmt 164), {prefix, suffix,
 * decimals} the rest (a count is {decimals: 0}, a rate {suffix: ' %', decimals: 1}); a `date` column's ISO text is
 * an Excel date (it filters by year and month). The cell stays a number, the CSV its plain value.
 *
 * No product code: the product's handler checks its nonce and its permission, reads the request with request(),
 * fetches each view's rows (an iterable: a generator keeps them out of memory) and hands them to xlsx() or csv(),
 * then exits. A long export needs the product's own time limit: the library never changes PHP's settings.
 *
 * @package RoosterAdmin
 */

// phpcs:disable WordPress.WP.AlternativeFunctions -- the file is written to php://output and spooled to php://temp, not the WordPress filesystem.

namespace Rooster\Admin;

defined( 'ABSPATH' ) || exit;

/**
 * Static.
 */
final class Export {

	/**
	 * The formats a table exports.
	 */
	public const FORMATS = array( 'csv', 'xlsx' );

	/**
	 * The request's names, each under the table's prefix (Ui::table()'s local or remote `prefix`), as the glue names
	 * the ticked rows (table-local.js's `keys.ids`): the ticked rows' ids (a comma list), every match, the columns to
	 * write (a comma list, in their order), the views as sheets (an XLSX's flag), the current view.
	 */
	public const KEYS = array(
		'ids'     => 'ids',
		'all'     => 'all',
		'columns' => 'columns',
		'sheets'  => 'sheets',
		'view'    => 'view',
	);

	/**
	 * The longest sheet name Excel takes.
	 */
	public const SHEET_NAME_MAX = 31;

	/**
	 * The longest cell Excel takes (characters).
	 */
	public const CELL_MAX = 32767;

	/**
	 * The digits from which an integer is written as text: Excel keeps 15 significant digits, so a longer number (a
	 * barcode, an SSCC) would lose its last ones.
	 */
	public const DIGITS_MAX = 15;

	/**
	 * A date column's Excel formats: a date, and a date with its time (the seconds kept in the value).
	 */
	public const DATES = array(
		'date'     => 'yyyy-mm-dd',
		'datetime' => 'yyyy-mm-dd hh:mm',
	);

	/**
	 * A row id's longest length.
	 */
	private const ID_MAX = 191;

	/**
	 * How much of a spooled part stays in memory before it goes to disk.
	 */
	private const SPOOL = 'php://temp/maxmemory:2097152';

	/* ── The request ───────────────────────────────────────────────────── */

	/**
	 * An export request, parsed and validated: the format; the rows ticked (the glue's `<prefix>ids`, a comma list,
	 * else every match, `<prefix>all`, else the view); the columns to write (the visible ones the request carries,
	 * `<prefix>columns`, in their order, each one the table declares, else every column); the views to write (the
	 * current one, or, for an XLSX of a table that opted into sheets (`<prefix>sheets`) and no row ticked, every
	 * declared view's key, in their order).
	 *
	 * The library never reads a request without a nonce: give the values the product's handler read once its own
	 * nonce was checked (`source`), or the action of the nonce the export link carries as `_wpnonce` (wp_nonce_url()),
	 * checked here first (`nonce`). Without either, nothing is read and the answer is the default: CSV, the current
	 * view, every column.
	 *
	 * @param array<int|string, mixed> $columns The table's columns: Ui::table()'s ({key, label, type}: an `actions`
	 *                                          column, or one marked `export` false, is never written), or key =>
	 *                                          label.
	 * @param array<int|string, mixed> $views   The table's views (its view pills), in their order: key => label, or a
	 *                                          list of keys. None: a table without views.
	 * @param array<string, mixed>     $opts    source (the request's values, unslashed: `wp_unslash( $_REQUEST )` after
	 *                                          the product's nonce), nonce (the export link's nonce action, read from
	 *                                          `_wpnonce`), prefix (the table's), keys (a name other than prefix +
	 *                                          KEYS: ids, all, columns, sheets, view), format (the format when the
	 *                                          product's route says it; else the request's `format`), view (the current
	 *                                          view as the product read it; else the request's `<prefix>view`), sheets
	 *                                          (false: the table did not opt into views as sheets, whatever the request
	 *                                          says).
	 * @return array{format: string, ids: list<string>, all: bool, columns: array<string, string>, views: list<string>, view: string, sheets: bool}
	 */
	public static function request( array $columns, array $views = array(), array $opts = array() ): array {
		$src    = is_array( $opts['source'] ?? null ) ? $opts['source'] : ( '' !== (string) ( $opts['nonce'] ?? '' ) ? self::source( (string) $opts['nonce'] ) : array() );
		$keys   = self::keys( (string) ( $opts['prefix'] ?? '' ), is_array( $opts['keys'] ?? null ) ? $opts['keys'] : array() );
		$format = strtolower( isset( $opts['format'] ) ? (string) $opts['format'] : self::scalar( $src, 'format' ) );
		$format = 'xlsx' === $format ? 'xlsx' : 'csv';
		$ids    = array();
		foreach ( self::listed( $src[ $keys['ids'] ] ?? null ) as $id ) {
			$id = sanitize_text_field( $id );
			if ( '' !== $id && strlen( $id ) <= self::ID_MAX && ! in_array( $id, $ids, true ) ) {
				$ids[] = $id;
			}
		}
		$all      = array() === $ids && in_array( strtolower( self::scalar( $src, $keys['all'] ) ), array( '1', 'true' ), true );
		$declared = self::declared( $columns );
		$picked   = array();
		foreach ( self::listed( $src[ $keys['columns'] ] ?? null ) as $key ) {
			if ( isset( $declared[ $key ] ) && ! isset( $picked[ $key ] ) ) {
				$picked[ $key ] = $declared[ $key ];
			}
		}
		$named   = self::views( $views );
		$current = isset( $opts['view'] ) ? (string) $opts['view'] : self::scalar( $src, $keys['view'] );
		$current = isset( $named[ $current ] ) ? $current : (string) array_key_first( $named );
		$sheets  = 'xlsx' === $format && array() === $ids && count( $named ) > 0 && false !== ( $opts['sheets'] ?? true ) && in_array( strtolower( self::scalar( $src, $keys['sheets'] ) ), array( '1', 'true' ), true );
		return array(
			'format'  => $format,
			'ids'     => $ids,
			'all'     => $all,
			'columns' => array() !== $picked ? $picked : $declared,
			'views'   => $sheets ? array_map( 'strval', array_keys( $named ) ) : ( '' !== $current ? array( $current ) : array() ),
			'view'    => $current,
			'sheets'  => $sheets,
		);
	}

	/**
	 * The request's names under a table's prefix: KEYS' words after the prefix, a name of `keys` in place of its own.
	 *
	 * @param string               $prefix The table's prefix.
	 * @param array<string, mixed> $keys   Names given in place of the prefix's.
	 * @return array<string, string>
	 */
	public static function keys( string $prefix = '', array $keys = array() ): array {
		$prefix = (string) preg_replace( '/[^A-Za-z0-9_-]/', '', $prefix );
		$out    = array();
		foreach ( self::KEYS as $key => $word ) {
			$given       = is_string( $keys[ $key ] ?? null ) ? (string) preg_replace( '/[^A-Za-z0-9_\[\]-]/', '', $keys[ $key ] ) : '';
			$out[ $key ] = '' !== $given ? $given : $prefix . $word;
		}
		return $out;
	}

	/**
	 * The request's values once the export link's nonce (`_wpnonce`, wp_nonce_url()'s) is checked; none without it.
	 *
	 * @param string $action The nonce's action.
	 * @return array<string, mixed>
	 */
	private static function source( string $action ): array {
		if ( ! isset( $_REQUEST['_wpnonce'] ) || ! wp_verify_nonce( sanitize_key( wp_unslash( $_REQUEST['_wpnonce'] ) ), $action ) ) {
			return array();
		}
		$values = wp_unslash( $_REQUEST ); // phpcs:ignore WordPress.Security.ValidatedSanitizedInput.InputNotSanitized -- each value request() reads is validated there: against the declared columns and views, sanitize_text_field() for an id.
		return is_array( $values ) ? $values : array();
	}

	/**
	 * A request's value as a line of text ('' when it is missing or not a scalar).
	 *
	 * @param array<string, mixed> $src  The request's values.
	 * @param string               $name The name.
	 */
	private static function scalar( array $src, string $name ): string {
		return isset( $src[ $name ] ) && is_scalar( $src[ $name ] ) ? trim( (string) $src[ $name ] ) : '';
	}

	/**
	 * A comma list (or a list of values: a form's `name[]`) as its trimmed, non-empty items.
	 *
	 * @param mixed $value The request's value.
	 * @return list<string>
	 */
	private static function listed( $value ): array {
		$items = is_array( $value ) ? array_filter( $value, 'is_scalar' ) : ( is_scalar( $value ) ? explode( ',', (string) $value ) : array() );
		return array_values( array_filter( array_map( static fn( $item ): string => trim( (string) $item ), $items ), static fn( string $item ): bool => '' !== $item ) );
	}

	/**
	 * The columns a file may hold: key => label, in their order; an `actions` column and one marked `export` false
	 * left out.
	 *
	 * @param array<int|string, mixed> $columns Ui::table()'s columns, or key => label.
	 * @return array<string, string>
	 */
	private static function declared( array $columns ): array {
		$out = array();
		foreach ( self::spec( $columns ) as $col ) {
			$out[ $col['key'] ] = $col['label'];
		}
		return $out;
	}

	/**
	 * The declared views: key => label, in their order (a list of keys is its own labels).
	 *
	 * @param array<int|string, mixed> $views The views.
	 * @return array<string, string>
	 */
	private static function views( array $views ): array {
		$out = array();
		foreach ( $views as $key => $label ) {
			if ( is_int( $key ) && is_string( $label ) ) {
				$key = $label;
			} elseif ( is_array( $label ) ) {
				$label = (string) ( $label['label'] ?? $key ); // a view pill's own shape: {label, count, url}
			}
			$key = (string) $key;
			if ( '' !== $key ) {
				$out[ $key ] = (string) $label;
			}
		}
		return $out;
	}

	/* ── The writers ───────────────────────────────────────────────────── */

	/**
	 * Streams one XLSX download (the headers, then the workbook to php://output); the caller exits after it. One sheet
	 * per entry, in order: a bold header row, frozen, with a filter, the column widths from the longest value.
	 *
	 * @param string                           $filename The file's name (sanitised; `.xlsx` added when missing).
	 * @param array<int, array<string, mixed>> $sheets   Each {name (raw: made a valid, unique sheet name), columns
	 *                                                   (key => label, or key => {label, type}, or Ui::table()'s
	 *                                                   columns: type `number` writes a numeric string as a number,
	 *                                                   `text` a number as text; Ui::table()'s `num` is number, its
	 *                                                   `code` text, its `date` an Excel date; a `format` (Ui::table()'s:
	 *                                                   'money' or {prefix, suffix, decimals}) a number shown so), rows
	 *                                                   (an iterable of rows: key => value, an object's properties too)}.
	 */
	public static function xlsx( string $filename, array $sheets ): void {
		self::send( self::filename( $filename, 'xlsx' ), 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' );
		$out = fopen( 'php://output', 'wb' );
		if ( false === $out ) {
			return;
		}
		self::write_xlsx( $out, $sheets );
		fclose( $out );
	}

	/**
	 * Streams one CSV download (the headers, then the file to php://output); the caller exits after it.
	 *
	 * @param string                   $filename The file's name (sanitised; `.csv` added when missing).
	 * @param array<int|string, mixed> $columns  As a sheet's columns (xlsx()).
	 * @param iterable                 $rows     The rows: key => value (an object's properties too).
	 * @param array<string, mixed>     $opts     bom (false: no byte order mark, for a file another program imports;
	 *                                           true by default, so Excel reads UTF-8).
	 */
	public static function csv( string $filename, array $columns, iterable $rows, array $opts = array() ): void {
		self::send( self::filename( $filename, 'csv' ), 'text/csv; charset=utf-8' );
		$out = fopen( 'php://output', 'wb' );
		if ( false === $out ) {
			return;
		}
		self::write_csv( $out, $columns, $rows, $opts );
		fclose( $out );
	}

	/**
	 * Writes a workbook to a stream (xlsx() without the headers: a file, a test).
	 *
	 * @param resource                         $out    A writable stream.
	 * @param array<int, array<string, mixed>> $sheets As xlsx()'s.
	 */
	public static function write_xlsx( $out, array $sheets ): void {
		$parts = array();
		$names = array();
		$codes = array(); // the workbook's number formats, in the order the sheets ask for them
		foreach ( array_values( $sheets ) as $i => $sheet ) {
			$sheet   = is_array( $sheet ) ? $sheet : array();
			$spec    = self::spec( is_array( $sheet['columns'] ?? null ) ? $sheet['columns'] : array() );
			$name    = self::sheet_name( (string) ( $sheet['name'] ?? '' ), $names, $i + 1 );
			$names[] = $name;
			$rows    = is_iterable( $sheet['rows'] ?? null ) ? $sheet['rows'] : array();
			$parts[] = self::spool( $name, $spec, $rows, $codes );
		}
		if ( array() === $parts ) {
			$parts[] = self::spool( self::sheet_name( '', array(), 1 ), array(), array(), $codes ); // a workbook holds a sheet
		}
		$zip   = array(
			'out'     => $out,
			'offset'  => 0,
			'central' => array(),
			'stamp'   => self::dos_time(),
		);
		$count = count( $parts );
		self::zip_entry( $zip, '[Content_Types].xml', array( self::content_types( $count ) ) );
		self::zip_entry( $zip, '_rels/.rels', array( self::root_rels() ) );
		self::zip_entry( $zip, 'xl/workbook.xml', array( self::workbook( $parts ) ) );
		self::zip_entry( $zip, 'xl/_rels/workbook.xml.rels', array( self::workbook_rels( $count ) ) );
		self::zip_entry( $zip, 'xl/styles.xml', array( self::styles( $codes ) ) );
		foreach ( $parts as $index => $part ) {
			self::zip_entry( $zip, 'xl/worksheets/sheet' . ( $index + 1 ) . '.xml', array( self::sheet_head( $part, 0 === $index ), $part['body'], self::sheet_tail( $part ) ) );
			fclose( $part['body'] );
		}
		self::zip_finish( $zip );
	}

	/**
	 * Writes a CSV to a stream (csv() without the headers): the BOM, the header row, a line per row, RFC 4180's line
	 * ends; a text cell that a spreadsheet would read as a formula is protected (protect()).
	 *
	 * @param resource                 $out     A writable stream.
	 * @param array<int|string, mixed> $columns As a sheet's columns.
	 * @param iterable                 $rows    The rows.
	 * @param array<string, mixed>     $opts    bom (false: none).
	 */
	public static function write_csv( $out, array $columns, iterable $rows, array $opts = array() ): void {
		$spec = self::spec( $columns );
		if ( false !== ( $opts['bom'] ?? true ) ) {
			fwrite( $out, "\xEF\xBB\xBF" );
		}
		fputcsv( $out, array_map( static fn( array $col ): string => self::protect( $col['label'] ), $spec ), ',', '"', '', "\r\n" );
		foreach ( $rows as $row ) {
			$line = array();
			foreach ( $spec as $col ) {
				[ $kind, $text ] = self::cell( self::value_of( $row, $col['key'] ), $col['type'], false );
				$line[]          = 's' === $kind ? self::protect( $text ) : $text;
			}
			fputcsv( $out, $line, ',', '"', '', "\r\n" );
		}
	}

	/**
	 * A text cell made safe from formula injection (OWASP's CSV injection): one that starts with = + - @, a tab or a
	 * carriage return gets a leading apostrophe, so a spreadsheet shows it as text; a number ("-12.5") stays as it is.
	 *
	 * @param string $text The cell.
	 */
	public static function protect( string $text ): string {
		return '' !== $text && in_array( $text[0], array( '=', '+', '-', '@', "\t", "\r" ), true ) && ! is_numeric( $text ) ? "'" . $text : $text;
	}

	/**
	 * A valid, unique sheet name (Excel's rules): none of [ ] : * ? / \, no apostrophe at either end, 31 characters at
	 * most, unique whatever the case ("History" is Excel's own); "Sheet <n>" when nothing is left.
	 *
	 * @param string             $name  The wanted name (raw).
	 * @param array<int, string> $taken The names already given.
	 * @param int                $n     The sheet's number.
	 */
	public static function sheet_name( string $name, array $taken = array(), int $n = 1 ): string {
		$name  = (string) preg_replace( '/\s+/u', ' ', str_replace( array( '[', ']', ':', '*', '?', '/', '\\' ), ' ', self::utf8( $name ) ) );
		$name  = trim( self::fit( trim( $name, " '" ), self::SHEET_NAME_MAX ), " '" );
		$name  = '' !== $name ? $name : 'Sheet ' . $n;
		$lower = array_map( array( self::class, 'lower' ), array_merge( $taken, array( 'History' ) ) );
		$base  = $name;
		$i     = 1;
		while ( in_array( self::lower( $name ), $lower, true ) ) {
			$tail = ' (' . ( ++$i ) . ')';
			$name = rtrim( self::fit( $base, self::SHEET_NAME_MAX - strlen( $tail ) ), " '" ) . $tail;
		}
		return $name;
	}

	/* ── Cells ─────────────────────────────────────────────────────────── */

	/**
	 * A sheet's columns as the writers read them: {key, label, type ('number', 'text', 'date' or ''), code (the number
	 * format's, '' none), pad (the characters the format adds), decimals}, in order; an `actions` column and one marked
	 * `export` false left out. A `format` makes a column a number column, except a text one (a SKU is never formatted)
	 * and a date one.
	 *
	 * @param array<int|string, mixed> $columns key => label, key => {label, type, format}, or Ui::table()'s columns.
	 * @return list<array{key: string, label: string, type: string, code: string, pad: int, decimals: int}>
	 */
	private static function spec( array $columns ): array {
		$out  = array();
		$seen = array();
		foreach ( $columns as $key => $col ) {
			$listed = is_int( $key ) && is_array( $col ) && isset( $col['key'] ); // Ui::table()'s
			$key    = $listed ? (string) $col['key'] : (string) $key;
			$type   = is_array( $col ) ? (string) ( $col['type'] ?? '' ) : '';
			if ( '' === $key || isset( $seen[ $key ] ) || 'actions' === $type || ( is_array( $col ) && false === ( $col['export'] ?? true ) ) ) {
				continue;
			}
			$seen[ $key ] = true;
			$type         = in_array( $type, array( 'number', 'num' ), true ) ? 'number' : ( 'code' === $type || ( ! $listed && 'text' === $type ) ? 'text' : ( 'date' === $type ? 'date' : '' ) );
			$shown        = in_array( $type, array( 'text', 'date' ), true ) || ! is_array( $col ) ? null : self::number_code( $col['format'] ?? null );
			$out[]        = array(
				'key'      => $key,
				'label'    => is_array( $col ) ? (string) ( $col['label'] ?? $key ) : (string) $col,
				'type'     => null !== $shown ? 'number' : $type,
				'code'     => $shown['code'] ?? '',
				'pad'      => $shown['pad'] ?? 0,
				'decimals' => $shown['decimals'] ?? 0,
			);
		}
		return $out;
	}

	/**
	 * A number column's Excel format from its `format` (Ui::table()'s, §8.2's figures): 'money' the shop's currency
	 * (WooCommerce's symbol and position, its decimals, a loss in red; without WooCommerce two decimals and no symbol),
	 * {prefix, suffix, decimals (2 when not named)} the rest; null for none. The separators are the reader's (Excel
	 * shows a code's `,` and `.` in its own language), so a format's `point` and `thousands` are the screen's only.
	 *
	 * @param mixed $format The column's `format`.
	 * @return array{code: string, pad: int, decimals: int}|null
	 */
	private static function number_code( $format ): ?array {
		if ( 'money' === $format ) {
			$symbol   = function_exists( 'get_woocommerce_currency_symbol' ) ? html_entity_decode( (string) get_woocommerce_currency_symbol(), ENT_QUOTES, 'UTF-8' ) : '';
			$pos      = (string) get_option( 'woocommerce_currency_pos', 'left' );
			$space    = in_array( $pos, array( 'left_space', 'right_space' ), true ) && '' !== $symbol ? ' ' : '';
			$prefix   = in_array( $pos, array( 'right', 'right_space' ), true ) ? '' : $symbol . $space;
			$suffix   = in_array( $pos, array( 'right', 'right_space' ), true ) ? $space . $symbol : '';
			$decimals = function_exists( 'wc_get_price_decimals' ) ? (int) wc_get_price_decimals() : 2;
			$red      = true;
		} elseif ( is_array( $format ) ) {
			$prefix   = (string) ( $format['prefix'] ?? '' );
			$suffix   = (string) ( $format['suffix'] ?? '' );
			$decimals = isset( $format['decimals'] ) ? (int) $format['decimals'] : 2;
			$red      = false;
		} else {
			return null;
		}
		$decimals = max( 0, min( 10, $decimals ) );
		$body     = self::literal( $prefix ) . '#,##0' . ( $decimals > 0 ? '.' . str_repeat( '0', $decimals ) : '' ) . self::literal( $suffix );
		return array(
			'code'     => $red ? $body . ';[Red]-' . $body : $body,
			'pad'      => self::width( $prefix . $suffix ) + 1, // and a minus
			'decimals' => $decimals,
		);
	}

	/**
	 * A text as a format code's literal: in quotes, without the quote and the backslash a code cannot carry there.
	 *
	 * @param string $text The text.
	 */
	private static function literal( string $text ): string {
		$text = str_replace( array( '"', '\\' ), '', self::utf8( $text ) );
		return '' === $text ? '' : '"' . $text . '"';
	}

	/**
	 * The workbook's style for a number format: 2 and up (0 is the default, 1 the header), the format added once.
	 *
	 * @param array<int, string> $codes The workbook's formats so far.
	 * @param string             $code  The format.
	 */
	private static function style( array &$codes, string $code ): int {
		$at = array_search( $code, $codes, true );
		if ( false === $at ) {
			$codes[] = $code;
			$at      = count( $codes ) - 1;
		}
		return 2 + (int) $at;
	}

	/**
	 * An ISO date or date-time ('Y-m-d', 'Y-m-d H:i', 'Y-m-d H:i:s', a T for the space) as Excel's serial number (days
	 * since 30 Dec 1899; the time the fraction), from 1 Mar 1900 (Excel's 1900 is a leap year) to 9999; the wall
	 * clock as written (no time zone moves it). Null for anything else.
	 *
	 * @param string $text The value.
	 * @return array{0: string, 1: string}|null The serial and which of DATES shows it.
	 */
	private static function serial( string $text ): ?array {
		if ( 1 !== preg_match( '/^(\d{4})-(\d{2})-(\d{2})(?:[ T](\d{2}):(\d{2})(?::(\d{2}))?)?$/', trim( $text ), $m ) ) {
			return null;
		}
		list( $year, $month, $day ) = array( (int) $m[1], (int) $m[2], (int) $m[3] );
		$time                       = isset( $m[4] ) ? array( (int) $m[4], (int) $m[5], (int) ( $m[6] ?? 0 ) ) : null;
		if ( ! checkdate( $month, $day, $year ) || $year * 10000 + $month * 100 + $day < 19000301 || ( null !== $time && ( $time[0] > 23 || $time[1] > 59 || $time[2] > 59 ) ) ) {
			return null;
		}
		$days = intdiv( gmmktime( 0, 0, 0, $month, $day, $year ), 86400 ) + 25569;
		if ( null === $time ) {
			return array( (string) $days, 'date' );
		}
		$serial = rtrim( rtrim( sprintf( '%.10F', $days + ( $time[0] * 3600 + $time[1] * 60 + $time[2] ) / 86400 ), '0' ), '.' );
		return array( $serial, 'datetime' );
	}

	/**
	 * A row's value for a key (an array's, an object's property; null when it has none).
	 *
	 * @param mixed  $row The row.
	 * @param string $key The column's key.
	 * @return mixed
	 */
	private static function value_of( $row, string $key ) {
		if ( is_array( $row ) || $row instanceof \ArrayAccess ) {
			return $row[ $key ] ?? null;
		}
		return is_object( $row ) && isset( $row->$key ) ? $row->$key : null;
	}

	/**
	 * A cell: its kind ('n' a number, 's' text, '' empty), its text and, for a date, which of DATES shows it. A boolean
	 * is 1 or 0, a list its items joined, a date its ISO form (it sorts); an integer of more than 15 digits, or any
	 * value of a `text` column, text; a numeric string of a `number` column a number (declared, never sniffed); in the
	 * XLSX, a `date` column's ISO text Excel's serial number (anything else there stays text).
	 *
	 * @param mixed  $value The value.
	 * @param string $type  The column's type: number, text, date or ''.
	 * @param bool   $xml   For XML (the XLSX): the characters XML cannot carry dropped, the text cut at CELL_MAX, a
	 *                      date a serial number.
	 * @return array{0: string, 1: string, 2: string}
	 */
	private static function cell( $value, string $type, bool $xml ): array {
		if ( $value instanceof \DateTimeInterface ) {
			$value = $value->format( 'Y-m-d H:i:s' );
		} elseif ( is_array( $value ) ) {
			$value = implode( ', ', array_map( static fn( $item ): string => is_scalar( $item ) ? (string) $item : '', $value ) );
		} elseif ( is_object( $value ) ) {
			$value = method_exists( $value, '__toString' ) ? (string) $value : '';
		} elseif ( is_bool( $value ) ) {
			$value = $value ? 1 : 0;
		}
		if ( 'number' === $type && is_string( $value ) && is_numeric( trim( $value ) ) ) {
			$value = 0 + trim( $value );
		}
		if ( is_int( $value ) ) {
			return array( 'text' !== $type && strlen( (string) abs( $value ) ) <= self::DIGITS_MAX ? 'n' : 's', (string) $value, '' );
		}
		if ( is_float( $value ) ) {
			return is_finite( $value ) ? array( 'text' === $type ? 's' : 'n', sprintf( '%.15H', $value ), '' ) : array( '', '', '' ); // %H: no locale's decimal comma
		}
		$text = self::utf8( is_scalar( $value ) ? (string) $value : '' );
		if ( $xml && 'date' === $type ) {
			$serial = self::serial( $text );
			if ( null !== $serial ) {
				return array( 'n', $serial[0], $serial[1] );
			}
		}
		if ( $xml ) {
			$text = self::fit( (string) preg_replace( '/[^\x{9}\x{A}\x{D}\x{20}-\x{D7FF}\x{E000}-\x{FFFD}\x{10000}-\x{10FFFF}]/u', '', $text ), self::CELL_MAX );
		}
		return '' === $text ? array( '', '', '' ) : array( 's', $text, '' );
	}

	/**
	 * Text as valid UTF-8: a broken sequence replaced (mb_scrub()), never the whole text lost.
	 *
	 * @param string $text The text.
	 */
	private static function utf8( string $text ): string {
		if ( '' === $text || 1 === preg_match( '//u', $text ) ) {
			return $text;
		}
		return function_exists( 'mb_scrub' ) ? mb_scrub( $text, 'UTF-8' ) : wp_check_invalid_utf8( $text, true );
	}

	/**
	 * Text cut to a length in characters.
	 *
	 * @param string $text The text.
	 * @param int    $max  Its longest length.
	 */
	private static function fit( string $text, int $max ): string {
		return function_exists( 'mb_substr' ) ? mb_substr( $text, 0, $max, 'UTF-8' ) : substr( $text, 0, $max );
	}

	/**
	 * Text in lower case, for a comparison that ignores the case.
	 *
	 * @param string $text The text.
	 */
	private static function lower( string $text ): string {
		return function_exists( 'mb_strtolower' ) ? mb_strtolower( $text, 'UTF-8' ) : strtolower( $text );
	}

	/**
	 * The width a text needs: its longest line, in characters.
	 *
	 * @param string $text The text.
	 */
	private static function width( string $text ): int {
		$longest = 0;
		foreach ( explode( "\n", $text ) as $line ) {
			$longest = max( $longest, function_exists( 'mb_strlen' ) ? mb_strlen( $line, 'UTF-8' ) : strlen( $line ) );
		}
		return $longest;
	}

	/* ── The workbook's parts ──────────────────────────────────────────── */

	/**
	 * A sheet's rows spooled as XML (the header first, style 1), with what its head and tail need; a formatted
	 * column's numbers and a date column's dates in their style, as wide as Excel shows them.
	 *
	 * @param string                    $name  The sheet's name.
	 * @param list<array<string, mixed>> $spec  Its columns (spec()'s).
	 * @param iterable                  $rows  Its rows.
	 * @param array<int, string>        $codes The workbook's number formats (styles()'s), added to.
	 * @return array{name: string, body: resource, rows: int, columns: int, widths: array<int, int>}
	 */
	private static function spool( string $name, array $spec, iterable $rows, array &$codes ): array {
		$body   = fopen( self::SPOOL, 'w+b' );
		$widths = array();
		$labels = array();
		$styles = array();
		foreach ( $spec as $c => $col ) {
			$labels[ $c ] = self::cell( $col['label'], 'text', true );
			if ( '' !== $col['code'] ) {
				$styles[ $c ] = self::style( $codes, $col['code'] );
			} elseif ( 'date' === $col['type'] ) {
				foreach ( self::DATES as $which => $code ) {
					$styles[ $c ][ $which ] = self::style( $codes, $code );
				}
			}
		}
		$r = 0;
		if ( array() !== $spec ) {
			fwrite( $body, self::row( ++$r, $labels, $widths, 1 ) );
		}
		foreach ( $rows as $row ) {
			$cells = array();
			foreach ( $spec as $c => $col ) {
				$cell = self::cell( self::value_of( $row, $col['key'] ), $col['type'], true );
				if ( 'n' === $cell[0] && '' !== $cell[2] ) { // a date: as wide as 2026-11-02 or 2026-11-02 18:40
					$cell = array( 'n', $cell[1], $styles[ $c ][ $cell[2] ], 'date' === $cell[2] ? 10 : 16 );
				} elseif ( 'n' === $cell[0] && isset( $styles[ $c ] ) && is_int( $styles[ $c ] ) ) {
					$cell = array( 'n', $cell[1], $styles[ $c ], strlen( number_format( abs( (float) $cell[1] ), $col['decimals'] ) ) + $col['pad'] );
				}
				$cells[ $c ] = $cell;
			}
			fwrite( $body, self::row( ++$r, $cells, $widths ) );
		}
		return array(
			'name'    => $name,
			'body'    => $body,
			'rows'    => $r,
			'columns' => count( $spec ),
			'widths'  => $widths,
		);
	}

	/**
	 * One row's XML; each column's widest text noted.
	 *
	 * @param int                                $r      The row's number.
	 * @param array<int, array<int, string|int>> $cells  Its cells (cell()'s kind and text; spool()'s style and width
	 *                                                   for a formatted number or a date).
	 * @param array<int, int>                    $widths The columns' widest text so far.
	 * @param int                                $style  The style of the cells without their own (1: the header).
	 */
	private static function row( int $r, array $cells, array &$widths, int $style = 0 ): string {
		$xml = '<row r="' . $r . '">';
		foreach ( $cells as $c => $cell ) {
			list( $kind, $text ) = array( (string) $cell[0], (string) $cell[1] );
			if ( '' === $kind ) {
				continue;
			}
			$own          = is_int( $cell[2] ?? null ) ? $cell[2] : $style;
			$s            = $own > 0 ? ' s="' . $own . '"' : '';
			$ref          = self::column( $c ) . $r;
			$xml         .= 'n' === $kind
				? '<c r="' . $ref . '"' . $s . '><v>' . $text . '</v></c>'
				: '<c r="' . $ref . '"' . $s . ' t="inlineStr"><is><t xml:space="preserve">' . htmlspecialchars( $text, ENT_XML1 | ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8' ) . '</t></is></c>';
			$widths[ $c ] = max( $widths[ $c ] ?? 0, is_int( $cell[3] ?? null ) ? $cell[3] : self::width( $text ) );
		}
		return $xml . '</row>';
	}

	/**
	 * A column's letters: 0 is A, 25 Z, 26 AA.
	 *
	 * @param int $index The column, from 0.
	 */
	public static function column( int $index ): string {
		$letters = '';
		for ( $n = $index + 1; $n > 0; $n = intdiv( $n - 1, 26 ) ) {
			$letters = chr( 65 + ( $n - 1 ) % 26 ) . $letters;
		}
		return $letters;
	}

	/**
	 * A sheet's range: A1 to its last column and row.
	 *
	 * @param array{rows: int, columns: int} $part The spooled sheet.
	 */
	private static function range( array $part ): string {
		return 'A1:' . self::column( max( 1, $part['columns'] ) - 1 ) . max( 1, $part['rows'] );
	}

	/**
	 * A worksheet's XML before its rows: its range, the header frozen (every sheet), the columns' widths.
	 *
	 * @param array{rows: int, columns: int, widths: array<int, int>} $part  The spooled sheet.
	 * @param bool                                                    $first Whether it is the sheet shown first.
	 */
	private static function sheet_head( array $part, bool $first ): string {
		$cols = '';
		for ( $c = 0; $c < $part['columns']; $c++ ) {
			$width = min( 80, max( 8, (int) ceil( ( $part['widths'][ $c ] ?? 0 ) * 1.1 ) + 2 ) );
			$cols .= '<col min="' . ( $c + 1 ) . '" max="' . ( $c + 1 ) . '" width="' . $width . '" customWidth="1"/>';
		}
		return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . "\n"
			. '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">'
			. '<dimension ref="' . self::range( $part ) . '"/>'
			. '<sheetViews><sheetView' . ( $first ? ' tabSelected="1"' : '' ) . ' workbookViewId="0"><pane ySplit="1" topLeftCell="A2" activePane="bottomLeft" state="frozen"/><selection pane="bottomLeft" activeCell="A2" sqref="A2"/></sheetView></sheetViews>'
			. '<sheetFormatPr defaultRowHeight="15"/>' . ( '' !== $cols ? '<cols>' . $cols . '</cols>' : '' ) . '<sheetData>';
	}

	/**
	 * A worksheet's XML after its rows: the header's filter.
	 *
	 * @param array{rows: int, columns: int} $part The spooled sheet.
	 */
	private static function sheet_tail( array $part ): string {
		return '</sheetData>' . ( $part['columns'] > 0 ? '<autoFilter ref="' . self::range( $part ) . '"/>' : '' ) . '<pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/></worksheet>';
	}

	/**
	 * The [Content_Types].xml part.
	 *
	 * @param int $sheets The sheets.
	 */
	private static function content_types( int $sheets ): string {
		$overrides = '';
		for ( $i = 1; $i <= $sheets; $i++ ) {
			$overrides .= '<Override PartName="/xl/worksheets/sheet' . $i . '.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>';
		}
		return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . "\n"
			. '<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">'
			. '<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>'
			. '<Default Extension="xml" ContentType="application/xml"/>'
			. '<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>'
			. $overrides
			. '<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>'
			. '</Types>';
	}

	/**
	 * The _rels/.rels part.
	 */
	private static function root_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>';
	}

	/**
	 * The xl/_rels/workbook.xml.rels part: rId1 to rIdN the sheets, rIdN+1 the styles.
	 *
	 * @param int $sheets The sheets.
	 */
	private static function workbook_rels( int $sheets ): string {
		$rels = '';
		for ( $i = 1; $i <= $sheets; $i++ ) {
			$rels .= '<Relationship Id="rId' . $i . '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet' . $i . '.xml"/>';
		}
		return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . "\n"
			. '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'
			. $rels
			. '<Relationship Id="rId' . ( $sheets + 1 ) . '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>'
			. '</Relationships>';
	}

	/**
	 * The xl/workbook.xml part: the sheets, and each one's filter range (Excel's hidden _FilterDatabase name).
	 *
	 * @param list<array{name: string, rows: int, columns: int}> $parts The spooled sheets.
	 */
	private static function workbook( array $parts ): string {
		$sheets = '';
		$names  = '';
		foreach ( $parts as $index => $part ) {
			$sheets .= '<sheet name="' . htmlspecialchars( $part['name'], ENT_XML1 | ENT_QUOTES, 'UTF-8' ) . '" sheetId="' . ( $index + 1 ) . '" r:id="rId' . ( $index + 1 ) . '"/>';
			if ( $part['columns'] > 0 ) {
				$names .= '<definedName name="_xlnm._FilterDatabase" localSheetId="' . $index . '" hidden="1">' . htmlspecialchars( "'" . str_replace( "'", "''", $part['name'] ) . "'!" . preg_replace( '/([A-Z]+)(\d+)/', '\$$1\$$2', self::range( $part ) ), ENT_XML1 | ENT_QUOTES, 'UTF-8' ) . '</definedName>';
			}
		}
		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">'
			. '<bookViews><workbookView activeTab="0"/></bookViews>'
			. '<sheets>' . $sheets . '</sheets>'
			. ( '' !== $names ? '<definedNames>' . $names . '</definedNames>' : '' )
			. '</workbook>';
	}

	/**
	 * The xl/styles.xml part: style 0 the default, style 1 the header (bold on a light grey, the LMS's), then one style
	 * per number format the sheets asked for (style 2 + n, Excel's custom format 164 + n).
	 *
	 * @param array<int, string> $codes The number formats, in their styles' order.
	 */
	private static function styles( array $codes = array() ): string {
		$formats = '';
		$xfs     = '';
		foreach ( array_values( $codes ) as $n => $code ) {
			$formats .= '<numFmt numFmtId="' . ( 164 + $n ) . '" formatCode="' . htmlspecialchars( $code, ENT_XML1 | ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8' ) . '"/>';
			$xfs     .= '<xf numFmtId="' . ( 164 + $n ) . '" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>';
		}
		return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . "\n"
			. '<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'
			. ( '' !== $formats ? '<numFmts count="' . count( $codes ) . '">' . $formats . '</numFmts>' : '' )
			. '<fonts count="2"><font><sz val="11"/><name val="Calibri"/><family val="2"/></font><font><b/><sz val="11"/><name val="Calibri"/><family val="2"/></font></fonts>'
			. '<fills count="3"><fill><patternFill patternType="none"/></fill><fill><patternFill patternType="gray125"/></fill><fill><patternFill patternType="solid"><fgColor rgb="FFEDEDED"/><bgColor indexed="64"/></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="' . ( 2 + count( $codes ) ) . '"><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"/>' . $xfs . '</cellXfs>'
			. '<cellStyles count="1"><cellStyle name="Normal" xfId="0" builtinId="0"/></cellStyles>'
			. '</styleSheet>';
	}

	/* ── The ZIP (the LMS's ZipWriter) ─────────────────────────────────── */

	/**
	 * The entries' DOS time and date: now, in the site's time zone.
	 *
	 * @return array{0: int, 1: int}
	 */
	private static function dos_time(): array {
		$now = new \DateTimeImmutable( 'now', wp_timezone() );
		return array(
			( (int) $now->format( 'G' ) << 11 ) | ( (int) $now->format( 'i' ) << 5 ) | intdiv( (int) $now->format( 's' ), 2 ),
			( ( max( 1980, (int) $now->format( 'Y' ) ) - 1980 ) << 9 ) | ( (int) $now->format( 'n' ) << 5 ) | (int) $now->format( 'j' ),
		);
	}

	/**
	 * One ZIP entry from its parts (strings and readable streams, in order): deflated into a spool (memory up to 2 MB,
	 * then disk) with its CRC-32, then its local header and its data written out; stored as it is when PHP has no
	 * zlib.
	 *
	 * @param array<string, mixed>        $zip   The writer's state: out, offset, central, stamp.
	 * @param string                      $name  The entry's name.
	 * @param array<int, string|resource> $parts Its content.
	 */
	private static function zip_entry( array &$zip, string $name, array $parts ): void {
		$crc     = hash_init( 'crc32b' );
		$deflate = function_exists( 'deflate_init' ) ? deflate_init( ZLIB_ENCODING_RAW, array( 'level' => 6 ) ) : false;
		$buffer  = fopen( self::SPOOL, 'w+b' );
		$size    = 0;
		foreach ( $parts as $part ) {
			if ( is_resource( $part ) ) {
				rewind( $part );
			}
			while ( true ) {
				$chunk = is_resource( $part ) ? fread( $part, 65536 ) : (string) $part;
				if ( false === $chunk || '' === $chunk ) {
					break;
				}
				$size += strlen( $chunk );
				hash_update( $crc, $chunk );
				fwrite( $buffer, false !== $deflate ? (string) deflate_add( $deflate, $chunk, ZLIB_NO_FLUSH ) : $chunk );
				if ( ! is_resource( $part ) ) {
					break;
				}
			}
		}
		if ( false !== $deflate ) {
			fwrite( $buffer, (string) deflate_add( $deflate, '', ZLIB_FINISH ) );
		}
		$compressed = (int) ftell( $buffer );
		rewind( $buffer );
		$crc32            = (int) hexdec( hash_final( $crc ) );
		$method           = false !== $deflate ? 8 : 0;
		[ $time, $date ]  = $zip['stamp'];
		$zip['central'][] = pack( 'VvvvvvvVVVvvvvvVV', 0x02014b50, 20, 20, 0x0800, $method, $time, $date, $crc32, $compressed, $size, strlen( $name ), 0, 0, 0, 0, 0, $zip['offset'] ) . $name;
		self::zip_write( $zip, pack( 'VvvvvvVVVvv', 0x04034b50, 20, 0x0800, $method, $time, $date, $crc32, $compressed, $size, strlen( $name ), 0 ) . $name );
		while ( ! feof( $buffer ) ) {
			$chunk = fread( $buffer, 65536 );
			if ( false === $chunk || '' === $chunk ) {
				break;
			}
			self::zip_write( $zip, $chunk );
		}
		fclose( $buffer );
	}

	/**
	 * The central directory and the end record.
	 *
	 * @param array<string, mixed> $zip The writer's state.
	 */
	private static function zip_finish( array &$zip ): void {
		$start = $zip['offset'];
		$dir   = implode( '', $zip['central'] );
		self::zip_write( $zip, $dir );
		self::zip_write( $zip, pack( 'VvvvvVVv', 0x06054b50, 0, 0, count( $zip['central'] ), count( $zip['central'] ), strlen( $dir ), $start, 0 ) );
		fflush( $zip['out'] );
	}

	/**
	 * Bytes out, counted.
	 *
	 * @param array<string, mixed> $zip   The writer's state.
	 * @param string               $bytes The bytes.
	 */
	private static function zip_write( array &$zip, string $bytes ): void {
		fwrite( $zip['out'], $bytes );
		$zip['offset'] += strlen( $bytes );
	}

	/* ── The download ──────────────────────────────────────────────────── */

	/**
	 * A download's file name: sanitised, the extension added when missing.
	 *
	 * @param string $name The name.
	 * @param string $ext  The extension.
	 */
	public static function filename( string $name, string $ext ): string {
		$name = sanitize_file_name( $name );
		$name = '' !== $name ? $name : 'export';
		return str_ends_with( strtolower( $name ), '.' . $ext ) ? $name : $name . '.' . $ext;
	}

	/**
	 * The download's headers, once nothing stray is buffered (a notice printed before would land inside the file): no
	 * cache, the type, the file's name (its UTF-8 form too), no sniffing. Nothing when the headers are gone already or
	 * on the command line.
	 *
	 * @param string $name The file's name.
	 * @param string $type Its content type.
	 */
	private static function send( string $name, string $type ): void {
		if ( headers_sent() || 'cli' === PHP_SAPI ) {
			return;
		}
		while ( ob_get_level() > 0 ) {
			$status = ob_get_status();
			if ( ! ( (int) ( $status['flags'] ?? 0 ) & PHP_OUTPUT_HANDLER_REMOVABLE ) || ! ob_end_clean() ) {
				break;
			}
		}
		nocache_headers();
		header( 'Content-Type: ' . $type );
		header( 'Content-Disposition: attachment; filename="' . preg_replace( '/[^\x20-\x7E]|["\\\\]/', '_', $name ) . '"; filename*=UTF-8\'\'' . rawurlencode( $name ) );
		header( 'X-Content-Type-Options: nosniff' );
	}
}