<?php
/**
 * Plantilla Excel import/export para inventario de bodega.
 *
 * @package Logistica
 */

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

/**
 * Exporta e importa inventario por codigo + lote.
 */
class Logistica_Warehouse_Inventory_Excel {

	/**
	 * Columnas de la plantilla.
	 *
	 * @return array<string, string> key => etiqueta.
	 */
	public static function columns() {
		return array(
			'descripcion'       => __( 'Descripción', 'logistica' ),
			'medida'            => __( 'Medida', 'logistica' ),
			'lote'              => __( 'Lote', 'logistica' ),
			'saldo_bodega'      => __( 'Saldo bodega (Kardex)', 'logistica' ),
			'materia_prima'     => __( 'Bodega', 'logistica' ),
			'codigo'            => __( 'Código', 'logistica' ),
			'clase'             => __( 'Clase', 'logistica' ),
			'fecha_creacion'    => __( 'Fecha creación', 'logistica' ),
			'inventario_fisico' => __( 'Inventario físico', 'logistica' ),
		);
	}

	/**
	 * Descarga plantilla Excel (.xlsx) con datos actuales.
	 *
	 * @return void
	 */
	public static function download_template() {
		$rows = Logistica_Warehouse_Inventory::get_all();

		if ( class_exists( 'ZipArchive', false ) ) {
			self::download_xlsx_template( $rows );
		}

		self::download_csv_template( $rows );
	}

	/**
	 * Exporta libro .xlsx nativo (sin advertencia de extension en Excel moderno).
	 *
	 * @param array<int, array<string, mixed>> $rows Filas inventario.
	 * @return void
	 */
	private static function download_xlsx_template( $rows ) {
		$sheet_xml = self::build_xlsx_sheet_xml( $rows );
		$tmp       = wp_tempnam( 'inventario-bodega-plantilla.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"/>'
			. '</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="Inventario" 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"/>'
			. '</Relationships>'
		);
		$zip->addFromString( 'xl/worksheets/sheet1.xml', $sheet_xml );
		$zip->close();

		nocache_headers();
		header( 'Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' );
		header( 'Content-Disposition: attachment; filename="inventario-bodega-plantilla.xlsx"' );
		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;
	}

	/**
	 * Respaldo CSV si ZipArchive no esta disponible en PHP.
	 *
	 * @param array<int, array<string, mixed>> $rows Filas inventario.
	 * @return void
	 */
	private static function download_csv_template( $rows ) {
		nocache_headers();
		header( 'Content-Type: text/csv; charset=utf-8' );
		header( 'Content-Disposition: attachment; filename="inventario-bodega-plantilla.csv"' );

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

		// BOM UTF-8 para que Excel abra acentos correctamente.
		fwrite( $handle, "\xEF\xBB\xBF" ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fwrite
		fputcsv( $handle, array_values( self::columns() ), ';' );

		$data_rows = empty( $rows ) ? array( array() ) : $rows;
		foreach ( $data_rows as $row ) {
			$line = array();
			foreach ( array_keys( self::columns() ) as $key ) {
				if ( 'fecha_creacion' === $key ) {
					$line[] = $row['fecha_creacion_label'] ?? ( $row['created_at'] ?? '' );
				} else {
					$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 inventario.
	 * @return string
	 */
	private static function build_xlsx_sheet_xml( $rows ) {
		$keys        = array_keys( self::columns() );
		$sheet_rows  = array();
		$sheet_rows[] = self::build_xlsx_row_xml( 1, array_values( self::columns() ), $keys, true );

		if ( empty( $rows ) ) {
			$sheet_rows[] = self::build_xlsx_row_xml( 2, array(), $keys, false );
		} else {
			$row_number = 2;
			foreach ( $rows as $row ) {
				$cells = array();
				foreach ( $keys as $key ) {
					if ( 'fecha_creacion' === $key ) {
						$cells[] = $row['fecha_creacion_label'] ?? ( $row['created_at'] ?? '' );
					} else {
						$cells[] = $row[ $key ] ?? '';
					}
				}
				$sheet_rows[] = self::build_xlsx_row_xml( $row_number, $cells, $keys, false );
				++$row_number;
			}
		}

		return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
			. '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'
			. '<sheetData>' . implode( '', $sheet_rows ) . '</sheetData>'
			. '</worksheet>';
	}

	/**
	 * @param int                    $row_number Numero de fila (1-based).
	 * @param array<int, mixed>      $cells Valores.
	 * @param string[]               $keys Claves de columna.
	 * @param bool                   $is_header Encabezado.
	 * @return string
	 */
	private static function build_xlsx_row_xml( $row_number, $cells, $keys, $is_header ) {
		$xml  = '<row r="' . (int) $row_number . '">';
		$cols = count( $keys );

		for ( $index = 0; $index < $cols; $index++ ) {
			$ref   = self::xlsx_column_letter( $index ) . (int) $row_number;
			$key   = $keys[ $index ];
			$value = $cells[ $index ] ?? '';

			if ( ! $is_header && in_array( $key, array( 'saldo_bodega', 'inventario_fisico' ), true ) && is_numeric( $value ) ) {
				$xml .= '<c r="' . $ref . '"><v>' . self::xml_escape( (string) $value ) . '</v></c>';
				continue;
			}

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

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

	/**
	 * @param int $index Indice 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;
	}

	/**
	 * Importa archivo subido.
	 *
	 * @param array $file $_FILES row.
	 * @return array{message:string,created:int,updated:int,skipped:int,mejorados:int,error?:bool}
	 */
	public static function import_upload( $file ) {
		if ( empty( $file['tmp_name'] ) || ! is_uploaded_file( $file['tmp_name'] ) ) {
			return self::error( __( 'No se recibió ningún archivo.', 'logistica' ) );
		}

		$content  = file_get_contents( $file['tmp_name'] ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_get_contents_file_get_contents
		$filename = strtolower( (string) ( $file['name'] ?? '' ) );

		if ( false === $content ) {
			return self::error( __( 'No se pudo leer el archivo.', 'logistica' ) );
		}

		$rows = self::parse_file( $content, $filename );
		if ( is_wp_error( $rows ) ) {
			return self::error( $rows->get_error_message() );
		}

		$stats = Logistica_Warehouse_Inventory::merge_import_rows( $rows );

		$message = sprintf(
			/* translators: 1: created 2: updated 3: improved 4: skipped */
			__( 'Importación completada. Nuevos: %1$d. Actualizados: %2$d. Mejorados: %3$d. Omitidos: %4$d.', 'logistica' ),
			(int) $stats['created'],
			(int) $stats['updated'],
			(int) $stats['mejorados'],
			(int) $stats['skipped']
		);

		if ( ! empty( $stats['skipped'] ) && ! empty( $stats['error'] ) ) {
			$message .= ' ' . sprintf(
				/* translators: %s: first database/import error */
				__( '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'],
			'mejorados' => (int) $stats['mejorados'],
		);
	}

	/**
	 * Parsea un archivo subido usando un mapa de columnas arbitrario.
	 *
	 * @param array  $file           $_FILES row.
	 * @param array  $columns        key => label.
	 * @param string $required_key   Clave obligatoria en cabecera.
	 * @return array<int, array<string, string>>|\WP_Error
	 */
	public static function parse_upload_columns( $file, array $columns, $required_key ) {
		if ( empty( $file['tmp_name'] ) || ! is_uploaded_file( $file['tmp_name'] ) ) {
			return new WP_Error( 'upload', __( 'No se recibió ningún archivo.', 'logistica' ) );
		}

		$content  = file_get_contents( $file['tmp_name'] ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_get_contents_file_get_contents
		$filename = strtolower( (string) ( $file['name'] ?? '' ) );

		if ( false === $content ) {
			return new WP_Error( 'read', __( 'No se pudo leer el archivo.', 'logistica' ) );
		}

		return self::parse_file( $content, $filename, $columns, $required_key );
	}

	/**
	 * Expone construcción de fila xlsx para otras plantillas del plugin.
	 *
	 * @param int                    $row_number Numero de fila.
	 * @param array<int, mixed>      $cells      Valores.
	 * @param string[]               $keys       Claves.
	 * @param bool                   $is_header  Encabezado.
	 * @return string
	 */
	public static function build_xlsx_row_xml_public( $row_number, $cells, $keys, $is_header ) {
		return self::build_xlsx_row_xml( $row_number, $cells, $keys, $is_header );
	}

	/**
	 * @param string $content Contenido.
	 * @param string $filename Nombre archivo.
	 * @return array<int, array<string, string>>|\WP_Error
	 */
	private static function parse_file( $content, $filename, $columns = null, $required_key = 'codigo' ) {
		if ( ! is_array( $columns ) || empty( $columns ) ) {
			$columns = self::columns();
		}

		$extension = strtolower( pathinfo( $filename, PATHINFO_EXTENSION ) );

		if ( in_array( $extension, array( 'xlsx', 'xlsm' ), true ) || self::is_xlsx_zip_content( $content ) ) {
			return self::parse_xlsx_upload( $content, $columns, $required_key );
		}

		if ( str_contains( $content, 'schemas-microsoft-com:office:spreadsheet' ) || str_contains( $content, '<Workbook' ) ) {
			return self::parse_spreadsheetml_table( $content, $columns, $required_key );
		}

		if ( self::is_binary_xls( $content ) ) {
			return self::parse_xls_upload( $content, $columns, $required_key );
		}

		if ( str_ends_with( $filename, '.csv' ) ) {
			return self::parse_csv( $content, $columns, $required_key );
		}

		if ( str_contains( $content, '<table' ) || str_contains( $content, '<Table' ) ) {
			return self::parse_html_table( $content, $columns, $required_key );
		}

		return self::parse_csv( $content, $columns, $required_key );
	}

	/**
	 * @param string $content Contenido binario xlsx.
	 * @return array<int, array<string, string>>|\WP_Error
	 */
	private static function parse_xlsx_upload( $content, $columns = null, $required_key = 'codigo' ) {
		if ( ! is_array( $columns ) || empty( $columns ) ) {
			$columns = self::columns();
		}
		if ( ! class_exists( 'ZipArchive', false ) ) {
			return new WP_Error( 'zip', __( 'El servidor no puede leer archivos .xlsx. Sube un CSV o guarda como .xml.', 'logistica' ) );
		}

		$tmp = wp_tempnam( 'warehouse-import.xlsx' );
		if ( ! $tmp || false === file_put_contents( $tmp, $content ) ) { // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_file_put_contents
			return new WP_Error( 'tmp', __( 'No se pudo procesar el archivo subido.', 'logistica' ) );
		}

		$zip = new ZipArchive();
		if ( true !== $zip->open( $tmp ) ) {
			@unlink( $tmp ); // phpcs:ignore WordPress.PHP.NoSilencedErrors.Discouraged
			return new WP_Error( 'xlsx', __( 'No se pudo abrir el archivo .xlsx.', 'logistica' ) );
		}

		$shared         = $zip->getFromName( 'xl/sharedStrings.xml' );
		$shared_strings = self::parse_xlsx_shared_strings( false === $shared ? '' : $shared );
		$sheets         = self::prioritize_xlsx_sheets( self::list_xlsx_worksheets( $zip ) );
		$rows           = null;
		$last_error     = null;

		foreach ( $sheets as $sheet ) {
			$sheet_xml = $zip->getFromName( $sheet['path'] );
			if ( false === $sheet_xml || '' === trim( $sheet_xml ) ) {
				continue;
			}

			$result = self::parse_xlsx_sheet_xml( $sheet_xml, $shared_strings, $columns, $required_key );
			if ( is_wp_error( $result ) ) {
				if ( 'headers' !== $result->get_error_code() ) {
					$last_error = $result;
				}
				continue;
			}

			$rows = $result;
			break;
		}

		$zip->close();
		@unlink( $tmp ); // phpcs:ignore WordPress.PHP.NoSilencedErrors.Discouraged

		if ( null !== $rows ) {
			return $rows;
		}

		if ( $last_error instanceof WP_Error ) {
			return $last_error;
		}

		return new WP_Error(
			'headers',
			sprintf(
				/* translators: %s: required column key */
				__( 'La plantilla debe incluir la columna %s.', 'logistica' ),
				$required_key
			)
		);
	}

	/**
	 * @param string               $sheet_xml      XML de una hoja.
	 * @param string[]             $shared_strings Cadenas compartidas.
	 * @param array<string,string> $columns        Mapa columnas.
	 * @param string               $required_key   Clave obligatoria.
	 * @return array<int, array<string, string>>|\WP_Error
	 */
	private static function parse_xlsx_sheet_xml( $sheet_xml, $shared_strings, $columns, $required_key ) {
		$sheet = simplexml_load_string( $sheet_xml );
		if ( false === $sheet ) {
			return new WP_Error( 'xlsx', __( 'No se pudo interpretar la hoja del archivo .xlsx.', 'logistica' ) );
		}

		$sheet->registerXPathNamespace( 'm', 'http://schemas.openxmlformats.org/spreadsheetml/2006/main' );
		$row_nodes = $sheet->xpath( '//m:sheetData/m:row' );
		if ( empty( $row_nodes ) ) {
			return new WP_Error( 'xlsx', __( 'El archivo .xlsx no contiene filas de datos.', 'logistica' ) );
		}

		$map  = null;
		$rows = array();

		foreach ( $row_nodes as $row_node ) {
			$cells = self::parse_xlsx_row_cells( $row_node, $shared_strings );
			if ( self::is_empty_row_cells( $cells ) ) {
				continue;
			}

			if ( null === $map ) {
				$map = self::map_headers( $cells, $columns );
				if ( ! self::map_has_column( $map, $required_key ) ) {
					return new WP_Error(
						'headers',
						sprintf(
							/* translators: %s: required column key */
							__( 'La plantilla debe incluir la columna %s.', 'logistica' ),
							$required_key
						)
					);
				}
				continue;
			}

			$row = self::cells_to_row( $cells, $map );
			if ( '' !== ( $row[ $required_key ] ?? '' ) ) {
				$rows[] = $row;
			}
		}

		if ( null === $map ) {
			return new WP_Error( 'xlsx', __( 'El archivo .xlsx no contiene filas de datos.', 'logistica' ) );
		}

		return $rows;
	}

	/**
	 * @param ZipArchive $zip Archivo xlsx abierto.
	 * @return array<int, array{name:string,path:string}>
	 */
	private static function list_xlsx_worksheets( $zip ) {
		$workbook_xml = $zip->getFromName( 'xl/workbook.xml' );
		$rels_xml     = $zip->getFromName( 'xl/_rels/workbook.xml.rels' );
		if ( false === $workbook_xml || false === $rels_xml ) {
			return array(
				array(
					'name' => '',
					'path' => 'xl/worksheets/sheet1.xml',
				),
			);
		}

		$rels = array();
		$rels_doc = simplexml_load_string( $rels_xml );
		if ( false !== $rels_doc ) {
			foreach ( $rels_doc->Relationship as $rel ) {
				$attrs  = $rel->attributes();
				$id     = (string) ( $attrs['Id'] ?? '' );
				$target = (string) ( $attrs['Target'] ?? '' );
				if ( '' === $id || '' === $target ) {
					continue;
				}
				$rels[ $id ] = str_starts_with( $target, 'xl/' ) ? $target : 'xl/' . $target;
			}
		}

		$sheets = array();
		$wb     = simplexml_load_string( $workbook_xml );
		if ( false !== $wb && isset( $wb->sheets->sheet ) ) {
			foreach ( $wb->sheets->sheet as $sheet ) {
				$attrs   = $sheet->attributes();
				$name    = (string) ( $attrs['name'] ?? '' );
				$r_attrs = $sheet->attributes( 'r', true );
				$rid     = (string) ( $r_attrs['id'] ?? '' );
				$path    = $rels[ $rid ] ?? '';
				if ( '' !== $path ) {
					$sheets[] = array(
						'name' => $name,
						'path' => $path,
					);
				}
			}
		}

		if ( empty( $sheets ) ) {
			$sheets[] = array(
				'name' => '',
				'path' => 'xl/worksheets/sheet1.xml',
			);
		}

		return $sheets;
	}

	/**
	 * Prioriza hojas cuyo nombre sugiere datos (p. ej. "minutas").
	 *
	 * @param array<int, array{name:string,path:string}> $sheets Hojas del libro.
	 * @return array<int, array{name:string,path:string}>
	 */
	private static function prioritize_xlsx_sheets( $sheets ) {
		usort(
			$sheets,
			static function ( $left, $right ) {
				$left_score  = self::xlsx_sheet_priority_score( (string) ( $left['name'] ?? '' ) );
				$right_score = self::xlsx_sheet_priority_score( (string) ( $right['name'] ?? '' ) );
				return $right_score <=> $left_score;
			}
		);

		return $sheets;
	}

	/**
	 * @param string $name Nombre de hoja.
	 * @return int
	 */
	private static function xlsx_sheet_priority_score( $name ) {
		$normalized = self::normalize_header( $name );
		if ( str_contains( $normalized, 'minuta' ) && ! str_contains( $normalized, 'revision' ) ) {
			return 100;
		}
		if ( str_contains( $normalized, 'inventario' ) ) {
			return 80;
		}
		if ( str_contains( $normalized, 'minuta' ) ) {
			return 60;
		}
		return 0;
	}

	/**
	 * @param string               $content      Contenido binario .xls.
	 * @param array<string,string> $columns      Mapa columnas.
	 * @param string               $required_key Clave obligatoria.
	 * @return array<int, array<string, string>>|\WP_Error
	 */
	private static function parse_xls_upload( $content, $columns = null, $required_key = 'codigo' ) {
		if ( ! is_array( $columns ) || empty( $columns ) ) {
			$columns = self::columns();
		}

		self::load_simple_xls_reader();

		$xls = \Shuchkin\SimpleXLS::parseData( $content );
		if ( ! $xls ) {
			$error = \Shuchkin\SimpleXLS::parseError();
			return new WP_Error(
				'xls',
				$error ? $error : __( 'No se pudo leer el archivo .xls.', 'logistica' )
			);
		}

		$sheet_names = $xls->sheetNames();
		if ( empty( $sheet_names ) ) {
			return new WP_Error( 'xls', __( 'El archivo .xls no contiene hojas.', 'logistica' ) );
		}

		$rows       = null;
		$last_error = null;

		foreach ( self::prioritize_sheet_indexes_by_name( $sheet_names ) as $sheet_index ) {
			$matrix = $xls->rows( (int) $sheet_index );
			if ( ! is_array( $matrix ) || empty( $matrix ) ) {
				continue;
			}

			$result = self::parse_matrix_rows( $matrix, $columns, $required_key );
			if ( is_wp_error( $result ) ) {
				if ( 'headers' !== $result->get_error_code() ) {
					$last_error = $result;
				}
				continue;
			}

			$rows = $result;
			break;
		}

		if ( null !== $rows ) {
			return $rows;
		}

		if ( $last_error instanceof WP_Error ) {
			return $last_error;
		}

		return new WP_Error(
			'headers',
			sprintf(
				/* translators: %s: required column key */
				__( 'La plantilla debe incluir la columna %s.', 'logistica' ),
				$required_key
			)
		);
	}

	/**
	 * Carga la librería SimpleXLS si aún no está disponible.
	 *
	 * @return void
	 */
	private static function load_simple_xls_reader() {
		if ( class_exists( 'Shuchkin\\SimpleXLS', false ) ) {
			return;
		}

		require_once LOGISTICA_PLUGIN_DIR . 'includes/lib/SimpleXLS.php';
	}

	/**
	 * @param array<int, string> $sheet_names Nombres de hojas indexados.
	 * @return array<int, int>
	 */
	private static function prioritize_sheet_indexes_by_name( $sheet_names ) {
		$indexes = array_keys( $sheet_names );
		usort(
			$indexes,
			static function ( $left, $right ) use ( $sheet_names ) {
				$left_score  = self::xlsx_sheet_priority_score( (string) ( $sheet_names[ $left ] ?? '' ) );
				$right_score = self::xlsx_sheet_priority_score( (string) ( $sheet_names[ $right ] ?? '' ) );
				return $right_score <=> $left_score;
			}
		);

		return $indexes;
	}

	/**
	 * @param array<int, array<int, mixed>> $matrix       Filas y celdas.
	 * @param array<string, string>         $columns      Mapa columnas.
	 * @param string                        $required_key Clave obligatoria.
	 * @return array<int, array<string, string>>|\WP_Error
	 */
	private static function parse_matrix_rows( $matrix, $columns, $required_key ) {
		$map  = null;
		$rows = array();

		foreach ( $matrix as $cells ) {
			if ( ! is_array( $cells ) ) {
				continue;
			}

			$cells = array_map(
				static function ( $cell ) {
					return trim( (string) $cell );
				},
				array_values( $cells )
			);

			if ( self::is_empty_row_cells( $cells ) ) {
				continue;
			}

			if ( null === $map ) {
				$map = self::map_headers( $cells, $columns );
				if ( ! self::map_has_column( $map, $required_key ) ) {
					return new WP_Error(
						'headers',
						sprintf(
							/* translators: %s: required column key */
							__( 'La plantilla debe incluir la columna %s.', 'logistica' ),
							$required_key
						)
					);
				}
				continue;
			}

			$row = self::cells_to_row( $cells, $map );
			if ( '' !== ( $row[ $required_key ] ?? '' ) ) {
				$rows[] = $row;
			}
		}

		if ( null === $map ) {
			return new WP_Error( 'empty', __( 'El archivo no contiene filas de datos.', 'logistica' ) );
		}

		return $rows;
	}

	/**
	 * @param string $xml sharedStrings.xml.
	 * @return string[]
	 */
	private static function parse_xlsx_shared_strings( $xml ) {
		if ( '' === trim( $xml ) ) {
			return array();
		}

		$doc = simplexml_load_string( $xml );
		if ( false === $doc ) {
			return array();
		}

		$doc->registerXPathNamespace( 'm', 'http://schemas.openxmlformats.org/spreadsheetml/2006/main' );
		$items = $doc->xpath( '//m:si' );
		$out   = array();

		foreach ( $items as $item ) {
			$text = '';
			if ( isset( $item->t ) ) {
				$text = (string) $item->t;
			} elseif ( isset( $item->r ) ) {
				foreach ( $item->r as $run ) {
					$text .= (string) ( $run->t ?? '' );
				}
			}
			$out[] = $text;
		}

		return $out;
	}

	/**
	 * @param SimpleXMLElement $row_node Fila xlsx.
	 * @param string[]         $shared_strings Tabla compartida.
	 * @return string[]
	 */
	private static function parse_xlsx_row_cells( $row_node, $shared_strings ) {
		$row_node->registerXPathNamespace( 'm', 'http://schemas.openxmlformats.org/spreadsheetml/2006/main' );
		$cells = array();

		foreach ( $row_node->c as $cell ) {
			$attrs = $cell->attributes();
			$ref   = isset( $attrs['r'] ) ? (string) $attrs['r'] : '';
			$col   = preg_replace( '/[0-9]+/', '', $ref );
			$index = '' !== $col ? self::xlsx_column_index( $col ) : count( $cells );
			$value = '';
			$type  = isset( $attrs['t'] ) ? (string) $attrs['t'] : '';

			if ( 's' === $type ) {
				$position = (int) $cell->v;
				$value    = $shared_strings[ $position ] ?? '';
			} elseif ( 'inlineStr' === $type && isset( $cell->is->t ) ) {
				$value = (string) $cell->is->t;
			} elseif ( isset( $cell->v ) ) {
				$value = (string) $cell->v;
			}

			$cells[ $index ] = trim( $value );
		}

		if ( empty( $cells ) ) {
			return array();
		}

		$max = max( array_keys( $cells ) );
		$row = array();
		for ( $i = 0; $i <= $max; $i++ ) {
			$row[] = $cells[ $i ] ?? '';
		}

		return $row;
	}

	/**
	 * @param string $letters Columna Excel.
	 * @return int
	 */
	private static function xlsx_column_index( $letters ) {
		$letters = strtoupper( (string) $letters );
		$index   = 0;
		$length  = strlen( $letters );

		for ( $i = 0; $i < $length; $i++ ) {
			$index = ( $index * 26 ) + ( ord( $letters[ $i ] ) - 64 );
		}

		return max( 0, $index - 1 );
	}

	/**
	 * @param string $content SpreadsheetML.
	 * @return array<int, array<string, string>>|\WP_Error
	 */
	private static function parse_spreadsheetml_table( $content, $columns = null, $required_key = 'codigo' ) {
		return self::parse_html_table( $content, $columns, $required_key );
	}

	/**
	 * @param string $content CSV.
	 * @param array|null $columns Columnas.
	 * @param string $required_key Clave obligatoria.
	 * @return array<int, array<string, string>>|\WP_Error
	 */
	private static function parse_csv( $content, $columns = null, $required_key = 'codigo' ) {
		if ( ! is_array( $columns ) || empty( $columns ) ) {
			$columns = self::columns();
		}
		$lines = preg_split( '/\r\n|\r|\n/', $content );
		if ( ! is_array( $lines ) || count( $lines ) < 2 ) {
			return new WP_Error( 'empty', __( 'El archivo no contiene filas de datos.', 'logistica' ) );
		}

		$delimiter = str_contains( (string) $lines[0], ';' ) ? ';' : ',';
		$header    = str_getcsv( array_shift( $lines ), $delimiter );
		if ( isset( $header[0] ) ) {
			$header[0] = preg_replace( '/^\xEF\xBB\xBF/u', '', (string) $header[0] );
		}
		$map       = self::map_headers( $header, $columns );

		if ( ! self::map_has_column( $map, $required_key ) ) {
			return new WP_Error(
				'headers',
				sprintf(
					/* translators: %s: required column key */
					__( 'La plantilla debe incluir la columna %s.', 'logistica' ),
					$required_key
				)
			);
		}

		$rows = array();
		foreach ( $lines as $line ) {
			if ( '' === trim( $line ) ) {
				continue;
			}
			$cells = str_getcsv( $line, $delimiter );
			$row   = self::cells_to_row( $cells, $map );
			if ( '' !== ( $row[ $required_key ] ?? '' ) ) {
				$rows[] = $row;
			}
		}

		return $rows;
	}

	/**
	 * @param string $content HTML/XLS.
	 * @return array<int, array<string, string>>|\WP_Error
	 */
	private static function parse_html_table( $content, $columns = null, $required_key = 'codigo' ) {
		if ( ! is_array( $columns ) || empty( $columns ) ) {
			$columns = self::columns();
		}
		$dom = new DOMDocument();
		libxml_use_internal_errors( true );
		$loaded = $dom->loadHTML( '<?xml encoding="UTF-8">' . $content );
		libxml_clear_errors();

		if ( ! $loaded ) {
			return new WP_Error( 'parse', __( 'No se pudo interpretar el archivo Excel.', 'logistica' ) );
		}

		$tables = $dom->getElementsByTagName( 'table' );
		if ( 0 === $tables->length ) {
			$tables = $dom->getElementsByTagName( 'Table' );
		}
		if ( 0 === $tables->length ) {
			return new WP_Error( 'parse', __( 'No se encontró tabla en el archivo.', 'logistica' ) );
		}

		$table = $tables->item( 0 );
		if ( ! $table instanceof DOMElement ) {
			return new WP_Error( 'parse', __( 'No se encontró tabla en el archivo.', 'logistica' ) );
		}

		$trs = $table->getElementsByTagName( 'tr' );
		if ( 0 === $trs->length ) {
			$trs = $table->getElementsByTagName( 'Row' );
		}

		$header_cells = array();
		$rows         = array();
		$map          = null;

		foreach ( $trs as $tr ) {
			if ( ! $tr instanceof DOMElement ) {
				continue;
			}

			$cells = array();
			foreach ( $tr->childNodes as $node ) {
				if ( ! $node instanceof DOMElement ) {
					continue;
				}

				if ( ! in_array( $node->nodeName, array( 'td', 'th', 'TD', 'TH', 'Cell' ), true ) ) {
					continue;
				}

				$text = '';
				$data = $node->getElementsByTagName( 'Data' );
				$text = $data->length ? $data->item( 0 )->textContent : $node->textContent;
				$cells[] = trim( (string) $text );
			}

			if ( self::is_empty_row_cells( $cells ) ) {
				continue;
			}

			if ( null === $map ) {
				$header_cells = $cells;
				$map          = self::map_headers( $header_cells, $columns );
				if ( ! self::map_has_column( $map, $required_key ) ) {
					return new WP_Error(
						'headers',
						sprintf(
							/* translators: %s: required column key */
							__( 'La plantilla debe incluir la columna %s.', 'logistica' ),
							$required_key
						)
					);
				}
				continue;
			}

			$row = self::cells_to_row( $cells, $map );
			if ( '' !== ( $row[ $required_key ] ?? '' ) ) {
				$rows[] = $row;
			}
		}

		if ( null === $map ) {
			return new WP_Error( 'parse', __( 'No se encontró tabla en el archivo.', 'logistica' ) );
		}

		return $rows;
	}

	/**
	 * @param array<int, string> $headers Encabezados.
	 * @return array<int|string, string>
	 */
	private static function map_headers( $headers, $columns = null ) {
		if ( ! is_array( $columns ) || empty( $columns ) ) {
			$columns = self::columns();
		}

		$keys    = array_keys( $columns );
		$labels  = array_values( $columns );
		$aliases = array(
			'materia_prima' => array( 'materia_prima', 'bodega', 'materia prima' ),
		);
		$map     = array();

		foreach ( $headers as $index => $header ) {
			$normalized = self::normalize_header( $header );
			if ( isset( $columns[ $normalized ] ) ) {
				$map[ $index ] = $normalized;
				continue;
			}

			foreach ( $keys as $key_index => $key ) {
				$matches = $normalized === self::normalize_header( $key )
					|| $normalized === self::normalize_header( $labels[ $key_index ] );

				if ( ! $matches && isset( $aliases[ $key ] ) ) {
					foreach ( $aliases[ $key ] as $alias ) {
						if ( $normalized === self::normalize_header( $alias ) ) {
							$matches = true;
							break;
						}
					}
				}

				if ( $matches ) {
					$map[ $index ] = $key;
					break;
				}
			}
		}

		return $map;
	}

	/**
	 * @param array<int, string>         $cells Celdas.
	 * @param array<int|string, string>  $map Mapa columnas.
	 * @return array<string, string>
	 */
	private static function cells_to_row( $cells, $map ) {
		$row = array();
		foreach ( $map as $index => $key ) {
			$row[ $key ] = isset( $cells[ $index ] ) ? trim( (string) $cells[ $index ] ) : '';
		}
		return $row;
	}

	/**
	 * @param string $value Texto.
	 * @return string
	 */
	private static function normalize_header( $value ) {
		$value = (string) $value;
		$value = preg_replace( '/^\xEF\xBB\xBF/u', '', $value );
		$value = preg_replace( '/[\x{FEFF}\x{200B}]/u', '', $value );
		$value = remove_accents( strtolower( trim( $value ) ) );
		$value = preg_replace( '/[^a-z0-9]+/', '_', $value );
		return trim( (string) $value, '_' );
	}

	/**
	 * @param array<int, string> $cells Celdas de una fila.
	 * @return bool
	 */
	private static function is_empty_row_cells( $cells ) {
		if ( ! is_array( $cells ) || empty( $cells ) ) {
			return true;
		}

		foreach ( $cells as $cell ) {
			if ( '' !== trim( (string) $cell ) ) {
				return false;
			}
		}

		return true;
	}

	/**
	 * @param array<int|string, string> $map        Mapa de columnas.
	 * @param string                    $column_key Clave buscada.
	 * @return bool
	 */
	private static function map_has_column( $map, $column_key ) {
		return is_array( $map ) && in_array( $column_key, $map, true );
	}

	/**
	 * @param string $content Contenido del archivo.
	 * @return bool
	 */
	private static function is_binary_xls( $content ) {
		return is_string( $content )
			&& strlen( $content ) >= 4
			&& "\xD0\xCF\x11\xE0" === substr( $content, 0, 4 );
	}

	/**
	 * @param string $content Contenido del archivo.
	 * @return bool
	 */
	private static function is_xlsx_zip_content( $content ) {
		return is_string( $content )
			&& strlen( $content ) >= 2
			&& 'PK' === substr( $content, 0, 2 );
	}

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

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