<?php
/**
 * Importación y exportación Excel/CSV de órdenes de compra del cuadro de pedidos.
 *
 * @package Logistica
 */

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

/**
 * Plantilla y carga de líneas de OC por estilo de tablero.
 */
class Logistica_Pedidos_Board_Excel {

	/**
	 * Columnas de la plantilla según slots de entrega del tablero.
	 *
	 * @param int $slot_count Cantidad de slots.
	 * @return array<string, string>
	 */
	public static function columns( $slot_count = null ) {
		if ( null === $slot_count ) {
			$slot_count = Logistica_Pedidos_Board::MAX_ORDEN_ENTREGA_SLOTS;
		}
		$slot_count = max( 1, min( (int) $slot_count, Logistica_Pedidos_Board::MAX_ORDEN_ENTREGA_SLOTS ) );

		$columns = array(
			'cliente'                   => __( 'Cliente', 'logistica' ),
			'oc_numero'                 => __( 'OC', 'logistica' ),
			'fecha_oc'                  => __( 'Fecha OC', 'logistica' ),
			'referencia'                => __( 'Tipo de producto', 'logistica' ),
			'descripcion'               => __( 'Referencia', 'logistica' ),
			'cantidad'                  => __( 'Cantidad solicitada', 'logistica' ),
			'cantidad_finalizada'       => __( 'Cantidad finalizada', 'logistica' ),
			'pendiente'                 => __( 'Pendiente por entrega', 'logistica' ),
			'cantidad_maxima_recibida'  => __( 'Tolerancia ± (%)', 'logistica' ),
			'precio_unit'               => __( 'Precio unit.', 'logistica' ),
			'fecha_aprobacion'          => __( 'Fecha aprobación', 'logistica' ),
			'fecha_entrega'             => __( 'Fecha entrega', 'logistica' ),
			'observaciones'             => __( 'Observaciones', 'logistica' ),
			'op'                        => __( 'OP', 'logistica' ),
			'estado'                    => __( 'Estado', 'logistica' ),
		);

		for ( $slot = 1; $slot <= $slot_count; $slot++ ) {
			$columns[ 'parcial_cantidad_' . $slot ] = sprintf(
				/* translators: %d: partial delivery slot number */
				__( 'Cant. %d', 'logistica' ),
				$slot
			);
			$columns[ 'parcial_factura_' . $slot ]  = sprintf(
				/* translators: %d: partial delivery slot number */
				__( 'Factura %d', 'logistica' ),
				$slot
			);
		}

		return $columns;
	}

	/**
	 * Descarga plantilla Excel con las líneas del tablero.
	 *
	 * @param string $board_slug Estilo de tablero.
	 * @return void
	 */
	public static function download_template( $board_slug ) {
		$board_slug = sanitize_title( (string) $board_slug );
		$slot_count = Logistica_Pedidos_Board::get_board_entrega_slot_count( $board_slug );

		if ( class_exists( 'ZipArchive', false ) ) {
			self::download_xlsx_template(
				self::build_typed_export_rows( $board_slug ),
				$board_slug,
				$slot_count
			);
		}

		self::download_csv_template( self::build_export_rows( $board_slug ), $board_slug, $slot_count );
	}

	/**
	 * Descarga historial de líneas entregadas en un periodo.
	 *
	 * @param string $board_slug Estilo de tablero.
	 * @param string $date_from  Fecha desde (Y-m-d).
	 * @param string $date_to    Fecha hasta (Y-m-d).
	 * @return void
	 */
	public static function download_historial( $board_slug, $date_from = '', $date_to = '' ) {
		$board_slug = sanitize_title( (string) $board_slug );
		if ( '' === $board_slug || ! Logistica_Pedidos_Board::get_board_sheet_config( $board_slug ) ) {
			wp_die( esc_html__( 'Estilo de tablero no válido.', 'logistica' ) );
		}

		$rows       = self::build_historial_export_rows( $board_slug, $date_from, $date_to );
		$slot_count = Logistica_Pedidos_Board::get_board_entrega_slot_count( $board_slug );
		$suffix     = 'historial-entregados';

		if ( class_exists( 'ZipArchive', false ) ) {
			self::download_xlsx_template(
				self::build_typed_export_rows( $board_slug, true, $date_from, $date_to ),
				$board_slug . '-' . $suffix,
				$slot_count,
				self::historial_columns( $slot_count ),
				__( 'Historial entregados', 'logistica' )
			);
		}

		self::download_csv_template( $rows, $board_slug . '-' . $suffix, $slot_count, self::historial_columns( $slot_count ) );
	}

	/**
	 * Columnas del export de historial (incluye fecha de cierre entregado).
	 *
	 * @param int $slot_count Cantidad de slots.
	 * @return array<string, string>
	 */
	public static function historial_columns( $slot_count = null ) {
		$columns = self::columns( $slot_count );
		$extra   = array( 'fecha_entregado' => __( 'Fecha entregado', 'logistica' ) );
		$merged  = array();
		foreach ( $columns as $key => $label ) {
			$merged[ $key ] = $label;
			if ( 'estado' === $key ) {
				$merged = array_merge( $merged, $extra );
			}
		}
		if ( ! isset( $merged['fecha_entregado'] ) ) {
			$merged['fecha_entregado'] = __( 'Fecha entregado', 'logistica' );
		}
		return $merged;
	}

	/**
	 * Importa archivo subido para un tablero.
	 *
	 * @param string $board_slug Estilo de tablero.
	 * @param array  $file       Fila $_FILES.
	 * @return array{message:string,created:int,updated:int,skipped:int,error?:bool}
	 */
	public static function import_upload( $board_slug, $file ) {
		$board_slug = sanitize_title( (string) $board_slug );
		if ( '' === $board_slug || ! Logistica_Pedidos_Board::get_board_sheet_config( $board_slug ) ) {
			return self::error( __( 'Estilo de tablero no válido.', 'logistica' ) );
		}

		$slot_count = Logistica_Pedidos_Board::get_board_entrega_slot_count( $board_slug );
		$columns    = self::columns( $slot_count );
		$parsed     = Logistica_Warehouse_Inventory_Excel::parse_upload_columns( $file, $columns, 'oc_numero' );

		if ( is_wp_error( $parsed ) ) {
			return self::error( $parsed->get_error_message() );
		}

		$stats = array(
			'created' => 0,
			'updated' => 0,
			'skipped' => 0,
			'error'   => '',
		);

		$estado_labels = array_flip( Logistica_Pedidos_Board::orden_estado_options() );

		foreach ( $parsed as $row ) {
			$oc_numero  = trim( (string) ( $row['oc_numero'] ?? '' ) );
			$referencia = trim( (string) ( $row['referencia'] ?? '' ) );

			if ( '' === $oc_numero || '' === $referencia ) {
				++$stats['skipped'];
				continue;
			}

			$match = self::find_linea( $board_slug, $oc_numero, $referencia );
			if ( null === $match ) {
				++$stats['skipped'];
				continue;
			}

			$orden  = $match['orden'];
			$linea  = $match['linea'];
			$estado = self::parse_estado_value( (string) ( $row['estado'] ?? '' ), $estado_labels );

			$linea_payload = array(
				'id'                       => (int) ( $linea['id'] ?? 0 ),
				'orden_id'                 => (int) ( $orden['id'] ?? 0 ),
				'articulo'                 => $referencia,
				'descripcion'              => array_key_exists( 'descripcion', $row ) ? (string) $row['descripcion'] : (string) ( $linea['descripcion'] ?? '' ),
				'cantidad'                 => '' !== (string) ( $row['cantidad'] ?? '' ) ? $row['cantidad'] : ( $linea['cantidad'] ?? 0 ),
				'cantidad_maxima_recibida' => '' !== (string) ( $row['cantidad_maxima_recibida'] ?? '' ) ? Logistica_Pedidos_Board::parse_tolerancia_pct( $row['cantidad_maxima_recibida'] ) : ( $linea['cantidad_maxima_recibida'] ?? 0 ),
				'precio_unit'              => '' !== (string) ( $row['precio_unit'] ?? '' ) ? $row['precio_unit'] : ( $linea['precio_unit'] ?? 0 ),
				'fecha_entrega'            => '' !== (string) ( $row['fecha_entrega'] ?? '' ) ? $row['fecha_entrega'] : ( $linea['fecha_entrega'] ?? '' ),
				'observaciones'            => array_key_exists( 'observaciones', $row ) ? (string) $row['observaciones'] : (string) ( $linea['observaciones'] ?? '' ),
				'estado'                   => $estado ?: sanitize_key( (string) ( $linea['estado'] ?? 'sin_confirmar' ) ),
			);

			if ( Logistica_Pedidos_Board::orden_linea_allows_op_field( $linea_payload['estado'] ) && array_key_exists( 'op', $row ) ) {
				$linea_payload['op'] = (string) $row['op'];
			}

			if ( Logistica_Pedidos_Board::orden_linea_allows_fecha_aprobacion_field( $linea_payload['estado'] ) && array_key_exists( 'fecha_aprobacion', $row ) ) {
				$linea_payload['fecha_aprobacion'] = (string) $row['fecha_aprobacion'];
			}

			if ( 'finalizado' === $linea_payload['estado'] && '' !== (string) ( $row['cantidad_finalizada'] ?? '' ) ) {
				$linea_payload['cantidad_entregada'] = $row['cantidad_finalizada'];
			}

			if ( array_key_exists( 'cliente', $row ) && '' !== trim( (string) $row['cliente'] ) ) {
				Logistica_Pedidos_Board::save_orden_compra(
					array(
						'id'         => (int) ( $orden['id'] ?? 0 ),
						'board_slug' => $board_slug,
						'fecha_oc'   => Logistica_Pedidos_Board::format_date_input( $orden['fecha_oc'] ?? '' ),
						'oc_numero'  => (string) ( $orden['oc_numero'] ?? '' ),
						'cliente'    => (string) $row['cliente'],
					)
				);
			}

			$saved = Logistica_Pedidos_Board::save_orden_linea( $linea_payload );
			if ( is_wp_error( $saved ) ) {
				if ( '' === $stats['error'] ) {
					$stats['error'] = $saved->get_error_message();
				}
				++$stats['skipped'];
				continue;
			}

			$linea_id = (int) ( $saved['linea']['id'] ?? $linea['id'] ?? 0 );
			$changed  = false;

			for ( $slot = 1; $slot <= $slot_count; $slot++ ) {
				$cant_key    = 'parcial_cantidad_' . $slot;
				$factura_key = 'parcial_factura_' . $slot;
				if ( ! array_key_exists( $cant_key, $row ) && ! array_key_exists( $factura_key, $row ) ) {
					continue;
				}

				$entrega_result = Logistica_Pedidos_Board::save_orden_linea_entrega(
					array(
						'orden_linea_id' => $linea_id,
						'slot_index'     => $slot - 1,
						'cantidad'       => $row[ $cant_key ] ?? '',
						'factura'        => $row[ $factura_key ] ?? '',
					)
				);

				if ( is_wp_error( $entrega_result ) ) {
					if ( '' === $stats['error'] ) {
						$stats['error'] = $entrega_result->get_error_message();
					}
					continue;
				}

				$changed = true;
			}

			if ( $changed ) {
				++$stats['updated'];
			} else {
				++$stats['updated'];
			}
		}

		$message = sprintf(
			/* translators: 1: updated count 2: skipped count */
			__( 'Importación completada. Líneas actualizadas: %1$d. Filas omitidas: %2$d.', 'logistica' ),
			(int) $stats['updated'],
			(int) $stats['skipped']
		);

		if ( '' !== $stats['error'] ) {
			$message .= ' ' . sprintf(
				/* translators: %s: first error message */
				__( 'Motivo del primer omitido: %s', 'logistica' ),
				(string) $stats['error']
			);
		}

		return array(
			'message' => $message,
			'created' => (int) $stats['created'],
			'updated' => (int) $stats['updated'],
			'skipped' => (int) $stats['skipped'],
		);
	}

	/**
	 * @param string $board_slug Tablero.
	 * @return array<int, array<string, string>>
	 */
	private static function build_export_rows( $board_slug ) {
		$rows = self::build_typed_export_rows( $board_slug );
		return array_map( array( self::class, 'format_row_for_csv' ), $rows );
	}

	/**
	 * @param string $board_slug Tablero.
	 * @param string $date_from  Fecha desde.
	 * @param string $date_to    Fecha hasta.
	 * @return array<int, array<string, string>>
	 */
	private static function build_historial_export_rows( $board_slug, $date_from = '', $date_to = '' ) {
		$rows = self::build_typed_export_rows( $board_slug, true, $date_from, $date_to );
		return array_map( array( self::class, 'format_row_for_csv' ), $rows );
	}

	/**
	 * Filas con valores tipados para exportación XLSX.
	 *
	 * @param string $board_slug Tablero.
	 * @param bool   $historial  Historial entregados.
	 * @param string $date_from  Fecha desde.
	 * @param string $date_to    Fecha hasta.
	 * @return array<int, array<string, mixed>>
	 */
	private static function build_typed_export_rows( $board_slug, $historial = false, $date_from = '', $date_to = '' ) {
		$ordenes = $historial
			? Logistica_Pedidos_Board::get_ordenes_compra_entregadas_historial( $board_slug, $date_from, $date_to )
			: Logistica_Pedidos_Board::get_ordenes_compra( $board_slug );
		$slot_count    = Logistica_Pedidos_Board::get_board_entrega_slot_count( $board_slug );
		$estado_labels = Logistica_Pedidos_Board::orden_estado_options();
		$rows          = array();

		foreach ( $ordenes as $orden ) {
			$lineas = isset( $orden['lineas'] ) && is_array( $orden['lineas'] ) ? $orden['lineas'] : array();
			if ( empty( $lineas ) ) {
				continue;
			}

			foreach ( $lineas as $linea ) {
				$extra = array();
				if ( $historial ) {
					$extra['fecha_entregado'] = Logistica_Pedidos_Board::format_entregado_at_iso( $linea );
				}
				$rows[] = self::map_linea_row( $orden, $linea, $slot_count, $estado_labels, $extra );
			}
		}

		return $rows;
	}

	/**
	 * @param array<string, mixed>        $orden         Orden.
	 * @param array<string, mixed>        $linea         Línea.
	 * @param int                         $slot_count    Slots entrega.
	 * @param array<string, string>       $estado_labels Etiquetas estado.
	 * @param array<string, string>       $extra         Campos adicionales.
	 * @return array<string, mixed>
	 */
	private static function map_linea_row( $orden, $linea, $slot_count, $estado_labels, $extra = array() ) {
		$estado_key = sanitize_key( (string) ( $linea['estado'] ?? 'sin_confirmar' ) );
		$row        = array(
			'cliente'                  => (string) ( $orden['cliente'] ?? '' ),
			'oc_numero'                => (string) ( $orden['oc_numero'] ?? '' ),
			'fecha_oc'                 => Logistica_Pedidos_Board::format_date_input( $orden['fecha_oc'] ?? '' ),
			'referencia'               => (string) ( $linea['articulo'] ?? '' ),
			'descripcion'              => (string) ( $linea['descripcion'] ?? '' ),
			'cantidad'                 => (float) Logistica_Pedidos_Board::parse_quantity( $linea['cantidad'] ?? 0 ),
			'cantidad_finalizada'      => (float) Logistica_Pedidos_Board::parse_quantity( $linea['cantidad_entregada'] ?? 0 ),
			'pendiente'                => (float) Logistica_Pedidos_Board::parse_quantity( Logistica_Pedidos_Board::compute_orden_linea_pendiente( $linea ) ),
			'cantidad_maxima_recibida' => (float) Logistica_Pedidos_Board::parse_tolerancia_pct( $linea['cantidad_maxima_recibida'] ?? 0 ),
			'precio_unit'              => (float) Logistica_Pedidos_Board::parse_money( $linea['precio_unit'] ?? 0 ),
			'fecha_aprobacion'         => Logistica_Pedidos_Board::orden_linea_allows_fecha_aprobacion_field( $estado_key )
				? Logistica_Pedidos_Board::format_date_input( $linea['fecha_aprobacion'] ?? '' )
				: '',
			'fecha_entrega'            => Logistica_Pedidos_Board::format_date_input( $linea['fecha_prorroga'] ?? '' ) !== ''
				? Logistica_Pedidos_Board::format_date_input( $linea['fecha_prorroga'] ?? '' )
				: Logistica_Pedidos_Board::format_date_input( $linea['fecha_entrega'] ?? '' ),
			'observaciones'            => (string) ( $linea['observaciones'] ?? '' ),
			'op'                       => Logistica_Pedidos_Board::orden_linea_allows_op_field( $estado_key )
				? (string) ( $linea['op'] ?? '' )
				: '',
			'estado'                   => $estado_labels[ $estado_key ] ?? $estado_key,
		);

		$parciales_by_slot = array();
		foreach ( (array) ( $linea['entregas_parciales'] ?? array() ) as $parcial ) {
			$parciales_by_slot[ (int) ( $parcial['slot_index'] ?? 0 ) ] = $parcial;
		}

		for ( $slot = 1; $slot <= $slot_count; $slot++ ) {
			$parcial = $parciales_by_slot[ $slot - 1 ] ?? null;
			$row[ 'parcial_cantidad_' . $slot ] = $parcial
				? (float) Logistica_Pedidos_Board::parse_quantity( $parcial['cantidad'] ?? 0 )
				: null;
			$row[ 'parcial_factura_' . $slot ] = $parcial ? (string) ( $parcial['factura'] ?? '' ) : '';
		}

		foreach ( $extra as $key => $value ) {
			$row[ $key ] = $value;
		}

		return $row;
	}

	/**
	 * @param array<string, mixed> $row Fila tipada.
	 * @return array<string, string>
	 */
	private static function format_row_for_csv( $row ) {
		$formatted = array();
		foreach ( $row as $key => $value ) {
			if ( null === $value || '' === $value ) {
				$formatted[ $key ] = '';
				continue;
			}

			if ( 0 === strpos( $key, 'parcial_cantidad_' ) || in_array( $key, array( 'cantidad', 'cantidad_finalizada', 'pendiente' ), true ) ) {
				$formatted[ $key ] = Logistica_Pedidos_Board::format_quantity( $value );
				continue;
			}

			if ( 'precio_unit' === $key ) {
				$formatted[ $key ] = Logistica_Pedidos_Board::format_money( $value );
				continue;
			}

			if ( 'cantidad_maxima_recibida' === $key ) {
				$formatted[ $key ] = Logistica_Pedidos_Board::format_tolerancia_pct( $value );
				continue;
			}

			$formatted[ $key ] = (string) $value;
		}

		return $formatted;
	}

	/**
	 * @param string               $board_slug Tablero.
	 * @param string               $oc_numero  Número OC.
	 * @param string               $referencia Referencia.
	 * @return array{orden:array<string,mixed>,linea:array<string,mixed>}|null
	 */
	private static function find_linea( $board_slug, $oc_numero, $referencia ) {
		foreach ( Logistica_Pedidos_Board::get_ordenes_compra( $board_slug ) as $orden ) {
			if ( 0 !== strcasecmp( trim( (string) ( $orden['oc_numero'] ?? '' ) ), $oc_numero ) ) {
				continue;
			}

			foreach ( (array) ( $orden['lineas'] ?? array() ) as $linea ) {
				if ( 0 === strcasecmp( trim( (string) ( $linea['articulo'] ?? '' ) ), $referencia ) ) {
					return array(
						'orden' => $orden,
						'linea' => $linea,
					);
				}
			}
		}

		return null;
	}

	/**
	 * @param string              $value         Valor importado.
	 * @param array<string,string> $label_to_key Mapa etiqueta => clave.
	 * @return string
	 */
	private static function parse_estado_value( $value, $label_to_key ) {
		$value = trim( $value );
		if ( '' === $value ) {
			return '';
		}

		$key = sanitize_key( $value );
		if ( array_key_exists( $key, Logistica_Pedidos_Board::orden_estado_options() ) ) {
			return $key;
		}

		if ( isset( $label_to_key[ $value ] ) ) {
			return sanitize_key( (string) $label_to_key[ $value ] );
		}

		foreach ( $label_to_key as $label => $label_key ) {
			if ( 0 === strcasecmp( $label, $value ) ) {
				return sanitize_key( (string) $label_key );
			}
		}

		return '';
	}

	/**
	 * @param array<int, array<string, mixed>> $rows       Filas tipadas.
	 * @param string                            $board_slug Tablero.
	 * @param int                               $slot_count Slots.
	 * @param array<string, string>|null        $columns    Columnas opcionales.
	 * @param string                            $sheet_name Nombre de hoja XLSX.
	 * @return void
	 */
	private static function download_xlsx_template( $rows, $board_slug, $slot_count, $columns = null, $sheet_name = 'Ordenes' ) {
		$columns    = is_array( $columns ) ? $columns : self::columns( $slot_count );
		$sheet_name = sanitize_text_field( (string) $sheet_name );
		if ( '' === $sheet_name ) {
			$sheet_name = 'Ordenes';
		}

		$sheet_xml = self::build_formatted_xlsx_sheet_xml( $rows, $columns, $sheet_name );
		$tmp       = wp_tempnam( 'pedidos-ordenes-export.xlsx' );

		if ( ! $tmp ) {
			return;
		}

		$zip = new ZipArchive();
		if ( true !== $zip->open( $tmp, ZipArchive::OVERWRITE ) ) {
			@unlink( $tmp ); // phpcs:ignore WordPress.PHP.NoSilencedErrors.Discouraged
			return;
		}

		$zip->addFromString(
			'[Content_Types].xml',
			'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
			. '<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">'
			. '<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>'
			. '<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>'
			. '<Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>'
			. '<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>'
			. '</Types>'
		);
		$zip->addFromString(
			'_rels/.rels',
			'<?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>'
		);
		$zip->addFromString(
			'xl/workbook.xml',
			'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
			. '<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">'
			. '<sheets><sheet name="' . esc_attr( $sheet_name ) . '" sheetId="1" r:id="rId1"/></sheets>'
			. '</workbook>'
		);
		$zip->addFromString(
			'xl/_rels/workbook.xml.rels',
			'<?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/worksheet" Target="worksheets/sheet1.xml"/>'
			. '<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>'
			. '</Relationships>'
		);
		$zip->addFromString( 'xl/styles.xml', self::build_xlsx_styles_xml() );
		$zip->addFromString( 'xl/worksheets/sheet1.xml', $sheet_xml );
		$zip->close();

		$filename = sanitize_file_name( 'pedidos-' . $board_slug . '.xlsx' );

		nocache_headers();
		header( 'Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' );
		header( 'Content-Disposition: attachment; filename="' . $filename . '"' );
		header( 'Content-Length: ' . (string) filesize( $tmp ) );
		readfile( $tmp ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_readfile
		@unlink( $tmp ); // phpcs:ignore WordPress.PHP.NoSilencedErrors.Discouraged
		exit;
	}

	/**
	 * @param array<int, array<string, string>> $rows       Filas.
	 * @param string                            $board_slug Tablero.
	 * @param int                               $slot_count Slots.
	 * @param array<string, string>|null        $columns    Columnas opcionales.
	 * @return void
	 */
	private static function download_csv_template( $rows, $board_slug, $slot_count, $columns = null ) {
		$columns  = is_array( $columns ) ? $columns : self::columns( $slot_count );
		$filename = sanitize_file_name( 'pedidos-' . $board_slug . '-ordenes.csv' );

		nocache_headers();
		header( 'Content-Type: text/csv; charset=utf-8' );
		header( 'Content-Disposition: attachment; filename="' . $filename . '"' );

		$handle = fopen( 'php://output', 'w' ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fopen
		if ( false === $handle ) {
			exit;
		}

		fwrite( $handle, "\xEF\xBB\xBF" ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fwrite
		fputcsv( $handle, array_values( $columns ), ';' );

		$data_rows = empty( $rows ) ? array( array() ) : $rows;
		foreach ( $data_rows as $row ) {
			$line = array();
			foreach ( array_keys( $columns ) as $key ) {
				$line[] = $row[ $key ] ?? '';
			}
			fputcsv( $handle, $line, ';' );
		}

		fclose( $handle ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose
		exit;
	}

	/**
	 * @param array<int, array<string, mixed>> $rows       Filas tipadas.
	 * @param array<string, string>             $columns    Columnas.
	 * @param string                            $sheet_name Título exportación.
	 * @return string
	 */
	private static function build_formatted_xlsx_sheet_xml( $rows, $columns, $sheet_name ) {
		$keys     = array_keys( $columns );
		$last_col = self::xlsx_column_letter( count( $keys ) - 1 );
		$count    = count( $rows );
		$user     = wp_get_current_user();
		$subtitle = sprintf(
			/* translators: 1: export date/time 2: record count 3: user name */
			__( 'Exportado el %1$s · %2$d línea(s) · %3$s', 'logistica' ),
			wp_date( 'd/m/Y H:i' ),
			$count,
			$user && $user->exists() ? $user->display_name : __( 'Usuario', 'logistica' )
		);

		$sheet_rows   = array();
		$sheet_rows[] = self::build_formatted_xlsx_row_xml( 1, array( $sheet_name ), $keys, array_fill( 0, count( $keys ), 11 ), 26, true );
		$sheet_rows[] = self::build_formatted_xlsx_row_xml( 2, array( $subtitle ), $keys, array_fill( 0, count( $keys ), 12 ), 20, true );

		$header_cells = array_values( $columns );
		$sheet_rows[] = self::build_formatted_xlsx_row_xml( 3, $header_cells, $keys, array_fill( 0, count( $keys ), 2 ), 24, true );

		$row_number = 4;
		foreach ( $rows as $index => $row ) {
			$cells  = array();
			$styles = array();
			foreach ( $keys as $key ) {
				$cells[]  = $row[ $key ] ?? '';
				$styles[] = self::xlsx_data_style_index( $key, 0 === ( $index % 2 ) );
			}
			$sheet_rows[] = self::build_formatted_xlsx_row_xml( $row_number, $cells, $keys, $styles, 18 );
			++$row_number;
		}

		if ( empty( $rows ) ) {
			$sheet_rows[] = self::build_formatted_xlsx_row_xml( 4, array(), $keys, array_fill( 0, count( $keys ), 0 ), 18 );
		}

		$merge = '<mergeCells count="2">'
			. '<mergeCell ref="A1:' . $last_col . '1"/>'
			. '<mergeCell ref="A2:' . $last_col . '2"/>'
			. '</mergeCells>';

		return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
			. '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'
			. '<sheetViews><sheetView workbookViewId="0">'
			. '<pane ySplit="3" topLeftCell="A4" activePane="bottomLeft" state="frozen"/>'
			. '<selection pane="bottomLeft" activeCell="A4" sqref="A4"/>'
			. '</sheetView></sheetViews>'
			. self::build_xlsx_column_widths_xml( $keys )
			. '<sheetData>' . implode( '', $sheet_rows ) . '</sheetData>'
			. $merge
			. '</worksheet>';
	}

	/**
	 * @param string[] $keys Claves de columna.
	 * @return string
	 */
	private static function build_xlsx_column_widths_xml( $keys ) {
		$widths = array(
			'cliente'                  => 18,
			'oc_numero'                => 12,
			'fecha_oc'                 => 13,
			'referencia'               => 22,
			'descripcion'              => 20,
			'cantidad'                 => 16,
			'cantidad_finalizada'      => 16,
			'pendiente'                => 16,
			'cantidad_maxima_recibida' => 14,
			'precio_unit'              => 13,
			'fecha_aprobacion'         => 14,
			'fecha_entrega'            => 13,
			'observaciones'            => 24,
			'op'                       => 10,
			'estado'                   => 16,
			'fecha_entregado'          => 14,
		);
		$xml   = '<cols>';
		$index = 0;

		foreach ( $keys as $key ) {
			$width = $widths[ $key ] ?? ( 0 === strpos( $key, 'parcial_cantidad_' ) ? 11 : ( 0 === strpos( $key, 'parcial_factura_' ) ? 14 : 14 ) );
			$xml  .= '<col min="' . ( $index + 1 ) . '" max="' . ( $index + 1 ) . '" width="' . $width . '" customWidth="1"/>';
			++$index;
		}

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

	/**
	 * @return string
	 */
	private static function build_xlsx_styles_xml() {
		return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
			. '<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'
			. '<numFmts count="4">'
			. '<numFmt numFmtId="164" formatCode="#,##0"/>'
			. '<numFmt numFmtId="165" formatCode="$#,##0"/>'
			. '<numFmt numFmtId="166" formatCode="dd/mm/yyyy"/>'
			. '<numFmt numFmtId="167" formatCode="0"/>'
			. '</numFmts>'
			. '<fonts count="4">'
			. '<font><sz val="11"/><color theme="1"/><name val="Calibri"/><family val="2"/></font>'
			. '<font><b/><sz val="15"/><color rgb="FFFFFFFF"/><name val="Calibri"/><family val="2"/></font>'
			. '<font><sz val="10"/><color rgb="FF0F766E"/><name val="Calibri"/><family val="2"/></font>'
			. '<font><b/><sz val="11"/><color rgb="FFFFFFFF"/><name val="Calibri"/><family val="2"/></font>'
			. '</fonts>'
			. '<fills count="6">'
			. '<fill><patternFill patternType="none"/></fill>'
			. '<fill><patternFill patternType="gray125"/></fill>'
			. '<fill><patternFill patternType="solid"><fgColor rgb="FF0F766E"/><bgColor indexed="64"/></patternFill></fill>'
			. '<fill><patternFill patternType="solid"><fgColor rgb="FFECFDF5"/><bgColor indexed="64"/></patternFill></fill>'
			. '<fill><patternFill patternType="solid"><fgColor rgb="FF0B5E58"/><bgColor indexed="64"/></patternFill></fill>'
			. '<fill><patternFill patternType="solid"><fgColor rgb="FFF8FAFC"/><bgColor indexed="64"/></patternFill></fill>'
			. '</fills>'
			. '<borders count="2">'
			. '<border><left/><right/><top/><bottom/><diagonal/></border>'
			. '<border>'
			. '<left style="thin"><color rgb="FFCBD5E1"/></left>'
			. '<right style="thin"><color rgb="FFCBD5E1"/></right>'
			. '<top style="thin"><color rgb="FFCBD5E1"/></top>'
			. '<bottom style="thin"><color rgb="FFCBD5E1"/></bottom>'
			. '<diagonal/>'
			. '</border>'
			. '</borders>'
			. '<cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs>'
			. '<cellXfs count="13">'
			. '<xf numFmtId="0" fontId="0" fillId="0" borderId="1" xfId="0" applyFill="1" applyBorder="1" applyAlignment="1"><alignment vertical="top" wrapText="1"/></xf>'
			. '<xf numFmtId="0" fontId="0" fillId="5" borderId="1" xfId="0" applyFill="1" applyBorder="1" applyAlignment="1"><alignment vertical="top" wrapText="1"/></xf>'
			. '<xf numFmtId="0" fontId="3" fillId="4" borderId="1" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center" wrapText="1"/></xf>'
			. '<xf numFmtId="166" fontId="0" fillId="0" borderId="1" xfId="0" applyNumberFormat="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="top"/></xf>'
			. '<xf numFmtId="166" fontId="0" fillId="5" borderId="1" xfId="0" applyNumberFormat="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="top"/></xf>'
			. '<xf numFmtId="164" fontId="0" fillId="0" borderId="1" xfId="0" applyNumberFormat="1" applyBorder="1" applyAlignment="1"><alignment horizontal="right" vertical="top"/></xf>'
			. '<xf numFmtId="164" fontId="0" fillId="5" borderId="1" xfId="0" applyNumberFormat="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="right" vertical="top"/></xf>'
			. '<xf numFmtId="165" fontId="0" fillId="0" borderId="1" xfId="0" applyNumberFormat="1" applyBorder="1" applyAlignment="1"><alignment horizontal="right" vertical="top"/></xf>'
			. '<xf numFmtId="165" fontId="0" fillId="5" borderId="1" xfId="0" applyNumberFormat="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="right" vertical="top"/></xf>'
			. '<xf numFmtId="167" fontId="0" fillId="0" borderId="1" xfId="0" applyNumberFormat="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="top"/></xf>'
			. '<xf numFmtId="167" fontId="0" fillId="5" borderId="1" xfId="0" applyNumberFormat="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="top"/></xf>'
			. '<xf numFmtId="0" fontId="1" fillId="2" borderId="0" xfId="0" applyFont="1" applyFill="1" applyAlignment="1"><alignment horizontal="left" vertical="center" wrapText="1"/></xf>'
			. '<xf numFmtId="0" fontId="2" fillId="3" borderId="0" xfId="0" applyFont="1" applyFill="1" applyAlignment="1"><alignment horizontal="left" vertical="center" wrapText="1"/></xf>'
			. '</cellXfs>'
			. '</styleSheet>';
	}

	/**
	 * @param string $key    Clave columna.
	 * @param bool   $zebra  Fila alterna.
	 * @return int
	 */
	private static function xlsx_data_style_index( $key, $zebra ) {
		$type = self::xlsx_column_cell_type( $key );
		if ( 'date' === $type ) {
			return $zebra ? 4 : 3;
		}
		if ( 'number' === $type ) {
			return $zebra ? 6 : 5;
		}
		if ( 'money' === $type ) {
			return $zebra ? 8 : 7;
		}
		if ( 'integer' === $type ) {
			return $zebra ? 10 : 9;
		}
		return $zebra ? 1 : 0;
	}

	/**
	 * @param string $key Clave columna.
	 * @return string
	 */
	private static function xlsx_column_cell_type( $key ) {
		if ( in_array( $key, array( 'fecha_oc', 'fecha_entrega', 'fecha_aprobacion', 'fecha_entregado' ), true ) ) {
			return 'date';
		}
		if ( in_array( $key, array( 'cantidad', 'cantidad_finalizada', 'pendiente' ), true ) || 0 === strpos( $key, 'parcial_cantidad_' ) ) {
			return 'number';
		}
		if ( 'precio_unit' === $key ) {
			return 'money';
		}
		if ( 'cantidad_maxima_recibida' === $key ) {
			return 'integer';
		}
		return 'text';
	}

	/**
	 * @param int                    $row_number Fila.
	 * @param array<int, mixed>      $cells      Valores.
	 * @param string[]               $keys       Claves columna.
	 * @param array<int, int>        $styles     Estilos por celda.
	 * @param int                    $height     Altura fila.
	 * @param bool                   $text_only  Forzar texto (títulos y encabezados).
	 * @return string
	 */
	private static function build_formatted_xlsx_row_xml( $row_number, $cells, $keys, $styles, $height = 0, $text_only = false ) {
		$height_attr = $height > 0 ? ' ht="' . (int) $height . '" customHeight="1"' : '';
		$xml         = '<row r="' . (int) $row_number . '"' . $height_attr . '>';
		$cols        = count( $keys );

		for ( $index = 0; $index < $cols; $index++ ) {
			$key   = $keys[ $index ];
			$value = array_key_exists( $index, $cells ) ? $cells[ $index ] : ( 0 === $index && isset( $cells[0] ) ? $cells[0] : '' );
			$style = (int) ( $styles[ $index ] ?? ( $styles[0] ?? 0 ) );
			$ref   = self::xlsx_column_letter( $index ) . (int) $row_number;
			$xml  .= self::build_formatted_xlsx_cell_xml( $ref, $value, $key, $style, $text_only );
		}

		$xml .= '</row>';
		return $xml;
	}

	/**
	 * @param string $ref       Referencia celda.
	 * @param mixed  $value     Valor.
	 * @param string $key       Clave columna.
	 * @param int    $style     Índice estilo.
	 * @param bool   $text_only Forzar texto.
	 * @return string
	 */
	private static function build_formatted_xlsx_cell_xml( $ref, $value, $key, $style, $text_only = false ) {
		if ( $text_only ) {
			if ( null === $value || '' === $value ) {
				return '<c r="' . $ref . '" s="' . (int) $style . '"/>';
			}
			return '<c r="' . $ref . '" s="' . (int) $style . '" t="inlineStr"><is><t xml:space="preserve">' . self::xml_escape( (string) $value ) . '</t></is></c>';
		}

		$type = self::xlsx_column_cell_type( $key );

		if ( 'date' === $type ) {
			$serial = self::xlsx_date_serial( $value );
			if ( '' === $serial ) {
				return '<c r="' . $ref . '" s="' . (int) $style . '"/>';
			}
			return '<c r="' . $ref . '" s="' . (int) $style . '"><v>' . self::xml_escape( $serial ) . '</v></c>';
		}

		if ( in_array( $type, array( 'number', 'money', 'integer' ), true ) ) {
			if ( null === $value || '' === $value ) {
				return '<c r="' . $ref . '" s="' . (int) $style . '"/>';
			}
			$number = is_numeric( $value ) ? (float) $value : 0.0;
			return '<c r="' . $ref . '" s="' . (int) $style . '"><v>' . self::xml_escape( (string) $number ) . '</v></c>';
		}

		return '<c r="' . $ref . '" s="' . (int) $style . '" t="inlineStr"><is><t xml:space="preserve">' . self::xml_escape( (string) $value ) . '</t></is></c>';
	}

	/**
	 * @param mixed $iso_date Fecha Y-m-d.
	 * @return string
	 */
	private static function xlsx_date_serial( $iso_date ) {
		$iso_date = trim( (string) $iso_date );
		if ( '' === $iso_date || '0000-00-00' === $iso_date ) {
			return '';
		}

		$timestamp = strtotime( $iso_date . ' UTC' );
		if ( ! $timestamp ) {
			return '';
		}

		$excel_epoch = strtotime( '1899-12-30 UTC' );
		return (string) (int) floor( ( $timestamp - $excel_epoch ) / DAY_IN_SECONDS );
	}

	/**
	 * @param int $index Índice base 0.
	 * @return string
	 */
	private static function xlsx_column_letter( $index ) {
		$index  = max( 0, (int) $index );
		$letter = '';

		do {
			$letter = chr( 65 + ( $index % 26 ) ) . $letter;
			$index  = (int) floor( $index / 26 ) - 1;
		} while ( $index >= 0 );

		return $letter;
	}

	/**
	 * @param string $value Texto.
	 * @return string
	 */
	private static function xml_escape( $value ) {
		return htmlspecialchars( (string) $value, ENT_XML1 | ENT_QUOTES, 'UTF-8' );
	}

	/**
	 * @param string $message Mensaje.
	 * @return array{message:string,created:int,updated:int,skipped:int,error:bool}
	 */
	private static function error( $message ) {
		return array(
			'message' => (string) $message,
			'created' => 0,
			'updated' => 0,
			'skipped' => 0,
			'error'   => true,
		);
	}
}
