<?php
/**
 * Plantilla Excel import/export para catálogo de códigos de etiqueta.
 *
 * @package Logistica
 */

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

/**
 * Exporta e importa códigos de producto para etiquetas EAN-13.
 */
class Logistica_Label_Product_Catalog_Excel {

	private const DATA_START_ROW    = 3;
	private const TEMPLATE_EXTRA_ROWS = 12;

	/**
	 * Claves de columnas (orden A..U).
	 *
	 * @return string[]
	 */
	public static function column_keys() {
		return array(
			'id',
			'referencia',
			'tipo_producto',
			'mercado',
			'tamano',
			'codigo_producto',
			'observaciones',
			'pais_1',
			'pais_2',
			'pais_3',
			'mercado_d',
			'tipo_d1',
			'tipo_d2',
			'cod_d1',
			'cod_d2',
			'cod_d3',
			'cod_d4',
			'cod_d5',
			'cod_d6',
			'suma',
			'dig_ver',
			'cod_barras',
		);
	}

	/**
	 * Columnas de la plantilla (CSV y mapeo importación).
	 *
	 * @return array<string, string> key => etiqueta.
	 */
	public static function columns() {
		return array(
			'id'              => __( 'ID', 'logistica' ),
			'referencia'      => __( 'Referencia (REF)', 'logistica' ),
			'tipo_producto'   => __( 'Tipo producto', 'logistica' ),
			'mercado'         => __( 'Mercado', 'logistica' ),
			'tamano'          => __( 'Tamaño', 'logistica' ),
			'codigo_producto' => __( 'Código producto (6 dígitos)', 'logistica' ),
			'observaciones'   => __( 'Observaciones', 'logistica' ),
			'pais_1'          => __( 'País 1', 'logistica' ),
			'pais_2'          => __( 'País 2', 'logistica' ),
			'pais_3'          => __( 'País 3', 'logistica' ),
			'mercado_d'       => __( 'EXP/NAC', 'logistica' ),
			'tipo_d1'         => __( 'Tipo 1', 'logistica' ),
			'tipo_d2'         => __( 'Tipo 2', 'logistica' ),
			'cod_d1'          => __( 'Cod 1', 'logistica' ),
			'cod_d2'          => __( 'Cod 2', 'logistica' ),
			'cod_d3'          => __( 'Cod 3', 'logistica' ),
			'cod_d4'          => __( 'Cod 4', 'logistica' ),
			'cod_d5'          => __( 'Cod 5', 'logistica' ),
			'cod_d6'          => __( 'Cod 6', 'logistica' ),
			'suma'            => __( 'SUMA', 'logistica' ),
			'dig_ver'         => __( 'Dígito de verif.', 'logistica' ),
			'cod_barras'      => __( 'COD. BARRAS', 'logistica' ),
			'ean13'           => __( 'EAN-13', 'logistica' ),
		);
	}

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

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

		self::download_csv_template( $rows );
	}

	/**
	 * @return array<int, array<string, mixed>>
	 */
	private static function export_rows() {
		$type_options   = Logistica_Label_Etiqueta_Tipos::get_option_labels();
		$market_options = Logistica_Label_Barcode::market_options();
		$rows           = array();

		foreach ( Logistica_Label_Product_Catalog::get_all() as $row ) {
			$ean   = Logistica_Label_Barcode::build_ean13(
				$row['tipo_producto'],
				$row['mercado'],
				$row['codigo_producto']
			);
			$digits = self::barcode_body_digits(
				$row['tipo_producto'],
				$row['mercado'],
				$row['codigo_producto']
			);

			$export = array(
				'id'              => (int) ( $row['id'] ?? 0 ),
				'referencia'      => (string) ( $row['referencia'] ?? '' ),
				'tipo_producto'   => (string) ( $type_options[ $row['tipo_producto'] ] ?? $row['tipo_producto'] ),
				'mercado'         => (string) ( $market_options[ $row['mercado'] ] ?? $row['mercado'] ),
				'tamano'          => (string) ( $row['tamano'] ?? '' ),
				'codigo_producto' => (string) ( $row['codigo_producto'] ?? '' ),
				'observaciones'   => (string) ( $row['observaciones'] ?? '' ),
				'suma'            => '',
				'dig_ver'         => '',
				'cod_barras'      => is_wp_error( $ean ) ? '' : (string) $ean,
			);

			if ( 12 === count( $digits ) ) {
				$export['pais_1']    = $digits[0];
				$export['pais_2']    = $digits[1];
				$export['pais_3']    = $digits[2];
				$export['mercado_d'] = $digits[3];
				$export['cod_d1']    = $digits[6];
				$export['cod_d2']    = $digits[7];
				$export['cod_d3']    = $digits[8];
				$export['cod_d4']    = $digits[9];
				$export['cod_d5']    = $digits[10];
				$export['cod_d6']    = $digits[11];
			}

			$rows[] = $export;
		}

		return $rows;
	}

	/**
	 * @param string $tipo Tipo producto.
	 * @param string $mercado EXP|NAC.
	 * @param string $codigo_producto Código 6 dígitos.
	 * @return array<int, string>
	 */
	private static function barcode_body_digits( $tipo, $mercado, $codigo_producto ) {
		$type_code    = Logistica_Label_Barcode::type_code( Logistica_Label_Barcode::normalize_product_type( $tipo ) );
		$market_digit = Logistica_Label_Barcode::market_digit( strtoupper( trim( (string) $mercado ) ) );
		$product_code = Logistica_Label_Barcode::normalize_product_code( $codigo_producto );

		if ( '' === $type_code || null === $market_digit || 6 !== strlen( $product_code ) ) {
			return array();
		}

		$body = Logistica_Label_Barcode::COUNTRY_PREFIX . $market_digit . $type_code . $product_code;

		if ( 12 !== strlen( $body ) || ! ctype_digit( $body ) ) {
			return array();
		}

		return str_split( $body );
	}

	/**
	 * Importa archivo subido.
	 *
	 * @param array $file $_FILES row.
	 * @return array{message:string,created:int,updated:int,skipped: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 = array(
			'created' => 0,
			'updated' => 0,
			'skipped' => 0,
			'error'   => '',
		);

		foreach ( $rows as $row ) {
			if ( '' === trim( (string) ( $row['referencia'] ?? '' ) ) ) {
				++$stats['skipped'];
				continue;
			}

			$payload = self::row_to_save_payload( $row );

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

			$existing = self::find_existing_row( $payload );

			if ( $existing ) {
				$payload['id'] = (int) $existing['id'];
			}

			$result = Logistica_Label_Product_Catalog::save_row( $payload );

			if ( is_wp_error( $result ) || ! is_array( $result ) || empty( $result['id'] ) ) {
				++$stats['skipped'];
				if ( '' === $stats['error'] ) {
					$stats['error'] = is_wp_error( $result )
						? $result->get_error_message()
						: __( 'No se pudo guardar una fila importada.', 'logistica' );
				}
				continue;
			}

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

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

		if ( ! empty( $stats['skipped'] ) && '' !== $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'],
		);
	}

	/**
	 * @param array<string, string> $row Fila importada.
	 * @return array<string, mixed>|\WP_Error
	 */
	private static function row_to_save_payload( $row ) {
		$tipo    = Logistica_Label_Barcode::normalize_product_type( $row['tipo_producto'] ?? '' );
		$mercado = self::resolve_import_mercado( $row );
		$tamano  = Logistica_Label_Etiqueta_Tamanos::normalize_label( (string) ( $row['tamano'] ?? '' ) );

		if ( '' === $tipo ) {
			return new WP_Error( 'tipo', __( 'Tipo de producto no reconocido en una fila importada.', 'logistica' ) );
		}

		if ( ! Logistica_Label_Etiqueta_Mercados::is_valid_key( $mercado ) ) {
			return new WP_Error( 'mercado', __( 'Mercado inválido. Use un mercado del catálogo.', 'logistica' ) );
		}

		if ( '' === $tamano ) {
			return new WP_Error( 'tamano', __( 'Tamaño obligatorio en cada fila.', 'logistica' ) );
		}

		$codigo = self::resolve_codigo_producto( $row );
		if ( 6 !== strlen( $codigo ) ) {
			return new WP_Error( 'codigo', __( 'Código de producto inválido (6 dígitos). Revise la columna o los dígitos del código.', 'logistica' ) );
		}

		$id = absint( $row['id'] ?? 0 );
		if ( $id > 0 && ! Logistica_Label_Product_Catalog::get_by_id( $id ) ) {
			$id = 0;
		}

		return array(
			'id'              => $id,
			'referencia'      => sanitize_text_field( (string) ( $row['referencia'] ?? '' ) ),
			'tipo_producto'   => $tipo,
			'mercado'         => $mercado,
			'tamano'          => $tamano,
			'codigo_producto' => $codigo,
			'observaciones'   => sanitize_textarea_field( (string) ( $row['observaciones'] ?? '' ) ),
		);
	}

	/**
	 * Resuelve mercado EXP|NAC desde columna Mercado, dígito EXP/NAC o EAN-13.
	 *
	 * @param array<string, string> $row Fila importada.
	 * @return string EXP|NAC|''
	 */
	private static function resolve_import_mercado( $row ) {
		$candidates = array(
			(string) ( $row['mercado'] ?? '' ),
			(string) ( $row['mercado_d'] ?? '' ),
		);

		foreach ( $candidates as $candidate ) {
			$mercado = Logistica_Label_Barcode::normalize_market( $candidate );
			if ( Logistica_Label_Etiqueta_Mercados::is_valid_key( $mercado ) ) {
				return $mercado;
			}
		}

		$ean = preg_replace( '/\D+/', '', (string) ( $row['cod_barras'] ?? $row['ean13'] ?? '' ) );
		if ( 13 === strlen( $ean ) ) {
			$from_digit = Logistica_Label_Etiqueta_Mercados::market_key_from_digit( $ean[3] );
			if ( Logistica_Label_Etiqueta_Mercados::is_valid_key( $from_digit ) ) {
				return $from_digit;
			}
		}

		return '';
	}

	/**
	 * Resuelve código de 6 dígitos desde columna, dígitos sueltos o EAN-13.
	 *
	 * @param array<string, string> $row Fila importada.
	 * @return string
	 */
	private static function resolve_codigo_producto( $row ) {
		$code = Logistica_Label_Barcode::normalize_product_code( $row['codigo_producto'] ?? '' );
		if ( 6 === strlen( $code ) ) {
			return $code;
		}

		$parts = array();
		foreach ( array( 'cod_d1', 'cod_d2', 'cod_d3', 'cod_d4', 'cod_d5', 'cod_d6' ) as $key ) {
			$digit = preg_replace( '/\D+/', '', (string) ( $row[ $key ] ?? '' ) );
			if ( '' !== $digit ) {
				$parts[] = substr( $digit, -1 );
			}
		}

		if ( 6 === count( $parts ) ) {
			return Logistica_Label_Barcode::normalize_product_code( implode( '', $parts ) );
		}

		$ean = preg_replace( '/\D+/', '', (string) ( $row['cod_barras'] ?? $row['ean13'] ?? '' ) );
		if ( 13 === strlen( $ean ) ) {
			return Logistica_Label_Barcode::normalize_product_code( substr( $ean, 6, 6 ) );
		}

		return '';
	}

	/**
	 * @param array<string, mixed> $payload Datos normalizados.
	 * @return array<string, mixed>|null
	 */
	private static function find_existing_row( $payload ) {
		if ( is_wp_error( $payload ) ) {
			return null;
		}

		$id = (int) ( $payload['id'] ?? 0 );
		if ( $id > 0 ) {
			$row = Logistica_Label_Product_Catalog::get_by_id( $id );
			if ( $row ) {
				return $row;
			}
		}

		return Logistica_Label_Product_Catalog::find_match(
			(string) $payload['referencia'],
			(string) $payload['tipo_producto'],
			(string) $payload['mercado']
		);
	}

	/**
	 * @param array<int, array<string, mixed>> $rows Filas.
	 * @return void
	 */
	private static function download_xlsx_template( $rows ) {
		$lookup_rows     = Logistica_Label_Etiqueta_Tipos::excel_type_lookup_rows();
		$lookup_last     = max( 2, count( $lookup_rows ) + 1 );
		$mercado_labels  = array_values( Logistica_Label_Barcode::market_options() );
		$tamano_labels   = Logistica_Label_Etiqueta_Tamanos::get_options();
		$list_ranges     = array(
			'tipo_last'    => $lookup_last,
			'mercado_last' => max( 2, count( $mercado_labels ) + 1 ),
			'tamano_last'  => max( 2, count( $tamano_labels ) + 1 ),
		);
		$sheet_xml       = self::build_xlsx_sheet_xml( $rows, $lookup_last, $list_ranges );
		$types_xml       = self::build_xlsx_types_sheet_xml( $lookup_rows, $mercado_labels, $tamano_labels );
		$tmp           = wp_tempnam( 'codigos-etiqueta-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"/>'
			. '<Override PartName="/xl/worksheets/sheet2.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="Codigos etiqueta" sheetId="1" r:id="rId1"/>'
			. '<sheet name="TiposEAN" sheetId="2" r:id="rId2" state="hidden"/>'
			. '</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/worksheet" Target="worksheets/sheet2.xml"/>'
			. '<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>'
			. '</Relationships>'
		);
		$zip->addFromString( 'xl/styles.xml', self::build_styles_xml() );
		$zip->addFromString( 'xl/worksheets/sheet1.xml', $sheet_xml );
		$zip->addFromString( 'xl/worksheets/sheet2.xml', $types_xml );
		$zip->close();

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

	/**
	 * @param array<int, array<string, mixed>> $rows Filas.
	 * @return void
	 */
	private static function download_csv_template( $rows ) {
		nocache_headers();
		header( 'Content-Type: text/csv; charset=utf-8' );
		header( 'Content-Disposition: attachment; filename="codigos-etiqueta-plantilla.csv"' );

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

		$keys = self::column_keys();
		$labels = self::columns();

		fwrite( $handle, "\xEF\xBB\xBF" ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fwrite
		$header = array();
		foreach ( $keys as $key ) {
			$header[] = $labels[ $key ] ?? $key;
		}
		fputcsv( $handle, $header, ';' );

		$data_rows = empty( $rows ) ? array( array() ) : $rows;
		foreach ( $data_rows as $row ) {
			$line = array();
			foreach ( $keys as $key ) {
				if ( in_array( $key, array( 'suma', 'dig_ver', 'tipo_d1', 'tipo_d2' ), true ) ) {
					$body = self::barcode_body_digits(
						Logistica_Label_Barcode::normalize_product_type( $row['tipo_producto'] ?? '' ),
						Logistica_Label_Barcode::normalize_market( $row['mercado'] ?? '' ),
						$row['codigo_producto'] ?? ''
					);
					if ( 12 === count( $body ) && 'tipo_d1' === $key ) {
						$line[] = $body[4];
						continue;
					}
					if ( 12 === count( $body ) && 'tipo_d2' === $key ) {
						$line[] = $body[5];
						continue;
					}
					if ( 12 === count( $body ) && 'suma' === $key ) {
						$sum = 0;
						$mult = array( 1, 3, 1, 3, 1, 3, 1, 3, 1, 3, 1, 3 );
						for ( $i = 0; $i < 12; $i++ ) {
							$sum += (int) $body[ $i ] * $mult[ $i ];
						}
						$line[] = (string) $sum;
						continue;
					}
					if ( 12 === count( $body ) && 'dig_ver' === $key ) {
						$sum = 0;
						$mult = array( 1, 3, 1, 3, 1, 3, 1, 3, 1, 3, 1, 3 );
						for ( $i = 0; $i < 12; $i++ ) {
							$sum += (int) $body[ $i ] * $mult[ $i ];
						}
						$line[] = (string) ( ( 10 - ( $sum % 10 ) ) % 10 );
						continue;
					}
				}
				$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 exportadas.
	 * @return string
	 */
	private static function build_xlsx_sheet_xml( $rows, $lookup_last_row = 2, $list_ranges = array() ) {
		$keys       = self::column_keys();
		$sheet_rows = array();

		$group_row = array_fill( 0, count( $keys ), '' );
		$group_row[0]  = __( 'Datos catálogo', 'logistica' );
		$group_row[7]  = __( 'PAÍS', 'logistica' );
		$group_row[10] = __( 'EXP/NAC', 'logistica' );
		$group_row[11] = __( 'TIPO PROD.', 'logistica' );
		$group_row[13] = __( 'COD. PRODUCTO', 'logistica' );
		$group_row[19] = __( 'SUMA', 'logistica' );
		$group_row[20] = __( 'DIGITO DE VERIF.', 'logistica' );
		$group_row[21] = __( 'COD. BARRAS', 'logistica' );
		$sheet_rows[] = self::build_xlsx_row_xml( 1, $group_row, $keys, 3 );

		$labels = self::columns();
		$header = array();
		foreach ( $keys as $key ) {
			$header[] = $labels[ $key ] ?? $key;
		}
		$sheet_rows[] = self::build_xlsx_row_xml( 2, $header, $keys, 3 );

		$data_count   = max( count( $rows ), 1 );
		$total_rows   = $data_count + self::TEMPLATE_EXTRA_ROWS;
		$row_number   = self::DATA_START_ROW;

		for ( $i = 0; $i < $total_rows; $i++ ) {
			$row_data = isset( $rows[ $i ] ) ? $rows[ $i ] : array();
			$cells    = array();
			foreach ( $keys as $key ) {
				$cells[] = $row_data[ $key ] ?? '';
			}

			if ( '' === trim( (string) ( $cells[0] ?? '' ) ) && '' === trim( (string) ( $cells[1] ?? '' ) ) ) {
				if ( '' === (string) ( $cells[7] ?? '' ) ) {
					$cells[7] = '7';
				}
				if ( '' === (string) ( $cells[8] ?? '' ) ) {
					$cells[8] = '7';
				}
				if ( '' === (string) ( $cells[9] ?? '' ) ) {
					$cells[9] = '0';
				}
			}

			$sheet_rows[] = self::build_xlsx_data_row_xml( $row_number, $cells, $keys, 0 === ( $i % 2 ) ? 0 : 5, $lookup_last_row );
			++$row_number;
		}

		$merge = '<mergeCells count="4">'
			. '<mergeCell ref="A1:G1"/>'
			. '<mergeCell ref="H1:J1"/>'
			. '<mergeCell ref="L1:M1"/>'
			. '<mergeCell ref="N1:S1"/>'
			. '</mergeCells>';

		$last_data_row = self::DATA_START_ROW + $total_rows - 1;
		$validations   = self::build_xlsx_data_validations_xml( $last_data_row, $list_ranges );

		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="2" topLeftCell="A3" activePane="bottomLeft" state="frozen"/>'
			. '<selection pane="bottomLeft" activeCell="A3" sqref="A3"/>'
			. '</sheetView></sheetViews>'
			. self::build_column_widths_xml( count( $keys ) )
			. '<sheetData>' . implode( '', $sheet_rows ) . '</sheetData>'
			. $merge
			. $validations
			. '</worksheet>';
	}

	/**
	 * Validación de datos: listas desplegables y código de 6 dígitos.
	 *
	 * @param int                  $last_data_row Última fila editable.
	 * @param array<string, int>   $list_ranges   Filas finales en hoja TiposEAN.
	 * @return string
	 */
	private static function build_xlsx_data_validations_xml( $last_data_row, $list_ranges ) {
		$start = self::DATA_START_ROW;
		$last  = max( $start, (int) $last_data_row );

		if ( $last < $start ) {
			return '';
		}

		$tipo_col    = self::xlsx_column_letter( 2 );
		$mercado_col = self::xlsx_column_letter( 3 );
		$tamano_col  = self::xlsx_column_letter( 4 );
		$codigo_col  = self::xlsx_column_letter( 5 );

		$tipo_sqref    = $tipo_col . $start . ':' . $tipo_col . $last;
		$mercado_sqref = $mercado_col . $start . ':' . $mercado_col . $last;
		$tamano_sqref  = $tamano_col . $start . ':' . $tamano_col . $last;
		$codigo_sqref  = $codigo_col . $start . ':' . $codigo_col . $last;

		$tipo_last    = max( 2, (int) ( $list_ranges['tipo_last'] ?? 2 ) );
		$mercado_last = max( 2, (int) ( $list_ranges['mercado_last'] ?? 2 ) );
		$tamano_last  = max( 2, (int) ( $list_ranges['tamano_last'] ?? 2 ) );

		$validations  = '<dataValidations count="' . ( ! empty( $list_ranges['tamano_count'] ) || (int) ( $list_ranges['tamano_last'] ?? 0 ) > 2 ? '4' : '3' ) . '">';
		$validations .= self::xlsx_list_validation(
			$tipo_sqref,
			'TiposEAN!$A$2:$A$' . $tipo_last,
			__( 'Tipo producto', 'logistica' ),
			__( 'Seleccione un tipo de la lista.', 'logistica' )
		);
		$validations .= self::xlsx_list_validation(
			$mercado_sqref,
			'TiposEAN!$D$2:$D$' . $mercado_last,
			__( 'Mercado', 'logistica' ),
			__( 'Seleccione Nacional o Exportación.', 'logistica' )
		);

		if ( (int) ( $list_ranges['tamano_last'] ?? 0 ) > 2 ) {
			$validations .= self::xlsx_list_validation(
				$tamano_sqref,
				'TiposEAN!$F$2:$F$' . $tamano_last,
				__( 'Tamaño', 'logistica' ),
				__( 'Seleccione un tamaño de la lista.', 'logistica' )
			);
		}
		$validations .= '<dataValidation type="custom" allowBlank="1" showInputMessage="1" showErrorMessage="1" sqref="'
			. self::xml_escape( $codigo_sqref ) . '">'
			. '<formula1>AND(LEN(' . $codigo_col . $start . ')=6,ISNUMBER(1*' . $codigo_col . $start . '))</formula1>'
			. '<promptTitle>' . self::xml_escape( __( 'Código producto', 'logistica' ) ) . '</promptTitle>'
			. '<prompt>' . self::xml_escape( __( '6 dígitos numéricos (ej. 002563).', 'logistica' ) ) . '</prompt>'
			. '<errorTitle>' . self::xml_escape( __( 'Código inválido', 'logistica' ) ) . '</errorTitle>'
			. '<error>' . self::xml_escape( __( 'Ingrese exactamente 6 dígitos numéricos.', 'logistica' ) ) . '</error>'
			. '</dataValidation>';
		$validations .= '</dataValidations>';

		return $validations;
	}

	/**
	 * @param string $sqref      Rango celdas.
	 * @param string $list_range Referencia a lista en otra hoja.
	 * @param string $title      Título ayuda.
	 * @param string $prompt     Texto ayuda.
	 * @return string
	 */
	private static function xlsx_list_validation( $sqref, $list_range, $title, $prompt ) {
		return '<dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1" sqref="'
			. self::xml_escape( $sqref ) . '">'
			. '<formula1>' . self::xml_escape( $list_range ) . '</formula1>'
			. '<promptTitle>' . self::xml_escape( $title ) . '</promptTitle>'
			. '<prompt>' . self::xml_escape( $prompt ) . '</prompt>'
			. '<errorTitle>' . self::xml_escape( __( 'Valor no permitido', 'logistica' ) ) . '</errorTitle>'
			. '<error>' . self::xml_escape( __( 'Seleccione una opción de la lista desplegable.', 'logistica' ) ) . '</error>'
			. '</dataValidation>';
	}

	/**
	 * Hoja oculta: tipos EAN, mercados y tamaños para listas desplegables.
	 *
	 * @param array<int, array{0:string,1:string}> $lookup_rows     Filas etiqueta/código tipo.
	 * @param string[]                               $mercado_labels  Etiquetas de mercado.
	 * @param string[]                               $tamano_labels   Etiquetas de tamaño.
	 * @return string
	 */
	private static function build_xlsx_types_sheet_xml( $lookup_rows, $mercado_labels, $tamano_labels ) {
		$sheet_rows = array();
		$sheet_rows[] = '<row r="1">'
			. '<c r="A1" s="3" t="inlineStr"><is><t>' . self::xml_escape( __( 'Tipo producto', 'logistica' ) ) . '</t></is></c>'
			. '<c r="B1" s="3" t="inlineStr"><is><t>' . self::xml_escape( __( 'Código EAN tipo', 'logistica' ) ) . '</t></is></c>'
			. '<c r="D1" s="3" t="inlineStr"><is><t>' . self::xml_escape( __( 'Mercado', 'logistica' ) ) . '</t></is></c>'
			. '<c r="F1" s="3" t="inlineStr"><is><t>' . self::xml_escape( __( 'Tamaño', 'logistica' ) ) . '</t></is></c>'
			. '</row>';

		$max_rows = max( count( $lookup_rows ), count( $mercado_labels ), count( $tamano_labels ) );

		for ( $i = 0; $i < $max_rows; $i++ ) {
			$row_number = $i + 2;
			$xml        = '<row r="' . (int) $row_number . '">';

			if ( isset( $lookup_rows[ $i ] ) ) {
				$xml .= '<c r="A' . (int) $row_number . '" s="0" t="inlineStr"><is><t>' . self::xml_escape( (string) $lookup_rows[ $i ][0] ) . '</t></is></c>';
				$xml .= '<c r="B' . (int) $row_number . '" s="2" t="inlineStr"><is><t>' . self::xml_escape( (string) $lookup_rows[ $i ][1] ) . '</t></is></c>';
			}

			if ( isset( $mercado_labels[ $i ] ) ) {
				$xml .= '<c r="D' . (int) $row_number . '" s="0" t="inlineStr"><is><t>' . self::xml_escape( (string) $mercado_labels[ $i ] ) . '</t></is></c>';
			}

			if ( isset( $tamano_labels[ $i ] ) ) {
				$xml .= '<c r="F' . (int) $row_number . '" s="0" t="inlineStr"><is><t>' . self::xml_escape( (string) $tamano_labels[ $i ] ) . '</t></is></c>';
			}

			$xml         .= '</row>';
			$sheet_rows[] = $xml;
		}

		return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
			. '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'
			. '<sheetViews><sheetView workbookViewId="0"/></sheetViews>'
			. '<cols>'
			. '<col min="1" max="1" width="24" customWidth="1"/>'
			. '<col min="2" max="2" width="14" customWidth="1"/>'
			. '<col min="4" max="4" width="16" customWidth="1"/>'
			. '<col min="6" max="6" width="14" customWidth="1"/>'
			. '</cols>'
			. '<sheetData>' . implode( '', $sheet_rows ) . '</sheetData>'
			. '</worksheet>';
	}

	/**
	 * @param int                  $count Columnas.
	 * @return string
	 */
	private static function build_column_widths_xml( $count ) {
		$widths = array( 6, 18, 14, 12, 10, 14, 16, 5, 5, 5, 8, 5, 5, 5, 5, 5, 5, 5, 5, 8, 10, 16 );
		$xml    = '<cols>';

		for ( $i = 0; $i < $count; $i++ ) {
			$width = $widths[ $i ] ?? 10;
			$xml  .= '<col min="' . ( $i + 1 ) . '" max="' . ( $i + 1 ) . '" width="' . $width . '" customWidth="1"/>';
		}

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

	/**
	 * @return string
	 */
	private static function build_styles_xml() {
		return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
			. '<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'
			. '<fonts count="4">'
			. '<font><sz val="11"/><color theme="1"/><name val="Calibri"/></font>'
			. '<font><b/><sz val="11"/><color rgb="FFFFFFFF"/><name val="Calibri"/></font>'
			. '<font><b/><sz val="11"/><color rgb="FFFFFFFF"/><name val="Calibri"/></font>'
			. '<font><b/><sz val="11"/><color rgb="FFFFFFFF"/><name val="Calibri"/></font>'
			. '</fonts>'
			. '<fills count="6">'
			. '<fill><patternFill patternType="none"/></fill>'
			. '<fill><patternFill patternType="gray125"/></fill>'
			. '<fill><patternFill patternType="solid"><fgColor rgb="FF1F4E79"/><bgColor indexed="64"/></patternFill></fill>'
			. '<fill><patternFill patternType="solid"><fgColor rgb="FFED7D31"/><bgColor indexed="64"/></patternFill></fill>'
			. '<fill><patternFill patternType="solid"><fgColor rgb="FFF8FAFC"/><bgColor indexed="64"/></patternFill></fill>'
			. '<fill><patternFill patternType="solid"><fgColor rgb="FFE2EFDA"/><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="7">'
			. '<xf numFmtId="0" fontId="0" fillId="0" borderId="1" xfId="0" applyBorder="1"><alignment vertical="center" wrapText="1"/></xf>'
			. '<xf numFmtId="0" fontId="0" fillId="0" borderId="1" xfId="0" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center"/></xf>'
			. '<xf numFmtId="49" fontId="0" fillId="0" borderId="1" xfId="0" applyNumberFormat="1" applyBorder="1"><alignment horizontal="center" vertical="center"/></xf>'
			. '<xf numFmtId="0" fontId="1" fillId="2" borderId="1" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center" wrapText="1"/></xf>'
			. '<xf numFmtId="0" fontId="2" fillId="3" borderId="1" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center"/></xf>'
			. '<xf numFmtId="0" fontId="0" fillId="4" borderId="1" xfId="0" applyFill="1" applyBorder="1"><alignment vertical="center" wrapText="1"/></xf>'
			. '<xf numFmtId="0" fontId="0" fillId="5" borderId="1" xfId="0" applyFill="1" applyBorder="1"><alignment vertical="center" wrapText="1"/></xf>'
			. '</cellXfs>'
			. '</styleSheet>';
	}

	/**
	 * @param int               $row_number Fila.
	 * @param array<int, mixed> $cells Valores.
	 * @param string[]          $keys Claves.
	 * @param int               $style Style index.
	 * @return string
	 */
	private static function build_xlsx_row_xml( $row_number, $cells, $keys, $style ) {
		$xml  = '<row r="' . (int) $row_number . '">';
		$cols = count( $keys );

		for ( $index = 0; $index < $cols; $index++ ) {
			$ref   = self::xlsx_column_letter( $index ) . (int) $row_number;
			$value = $cells[ $index ] ?? '';
			$xml  .= '<c r="' . $ref . '" s="' . (int) $style . '" t="inlineStr"><is><t>' . self::xml_escape( (string) $value ) . '</t></is></c>';
		}

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

	/**
	 * Fila de datos con fórmulas EAN-13 (columnas H–V).
	 *
	 * @param int               $row_number Fila.
	 * @param array<int, mixed> $cells Valores.
	 * @param string[]          $keys Claves.
	 * @param int               $style Style base.
	 * @return string
	 */
	private static function build_xlsx_data_row_xml( $row_number, $cells, $keys, $style, $lookup_last_row = 2 ) {
		$xml        = '<row r="' . (int) $row_number . '">';
		$cols       = count( $keys );
		$digit_keys = array( 'pais_1', 'pais_2', 'pais_3', 'mercado_d', 'tipo_d1', 'tipo_d2', 'cod_d1', 'cod_d2', 'cod_d3', 'cod_d4', 'cod_d5', 'cod_d6' );
		$lookup_ref = 'TiposEAN!$A$2:$B$' . max( 2, (int) $lookup_last_row );
		$tipo_code_formula = 'TEXT(VLOOKUP($C' . $row_number . ',' . $lookup_ref . ',2,FALSE),"00")';

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

			if ( in_array( $key, $digit_keys, true ) ) {
				$s = 1;
			} elseif ( 'codigo_producto' === $key ) {
				$s = 2;
			} elseif ( 'cod_barras' === $key ) {
				$s = 4;
			}

			if ( 'suma' === $key ) {
				$formula = 'SUMPRODUCT(H' . $row_number . ':S' . $row_number . ',{1,3,1,3,1,3,1,3,1,3,1,3})';
				$xml    .= '<c r="' . $ref . '" s="1"><f>' . self::xml_escape( $formula ) . '</f></c>';
				continue;
			}

			if ( 'dig_ver' === $key ) {
				$formula = 'MOD(10-MOD(T' . $row_number . ',10),10)';
				$xml    .= '<c r="' . $ref . '" s="1"><f>' . self::xml_escape( $formula ) . '</f></c>';
				continue;
			}

			if ( 'cod_barras' === $key ) {
				$formula = 'CONCATENATE(H' . $row_number . ',I' . $row_number . ',J' . $row_number . ',K' . $row_number . ',L' . $row_number . ',M' . $row_number . ',N' . $row_number . ',O' . $row_number . ',P' . $row_number . ',Q' . $row_number . ',R' . $row_number . ',S' . $row_number . ',U' . $row_number . ')';
				$xml    .= '<c r="' . $ref . '" s="4"><f>' . self::xml_escape( $formula ) . '</f></c>';
				continue;
			}

			if ( 'mercado_d' === $key && '' === trim( (string) $value ) ) {
				$formula = 'IF($D' . $row_number . '="","",IF(ISNUMBER(SEARCH("export",LOWER($D' . $row_number . '))),2,1))';
				$xml    .= '<c r="' . $ref . '" s="1"><f>' . self::xml_escape( $formula ) . '</f></c>';
				continue;
			}

			if ( 'tipo_d1' === $key && '' === trim( (string) $value ) ) {
				$formula = 'IF($C' . $row_number . '="","",IFERROR(VALUE(MID(' . $tipo_code_formula . ',1,1)),""))';
				$xml    .= '<c r="' . $ref . '" s="1"><f>' . self::xml_escape( $formula ) . '</f></c>';
				continue;
			}

			if ( 'tipo_d2' === $key && '' === trim( (string) $value ) ) {
				$formula = 'IF($C' . $row_number . '="","",IFERROR(VALUE(MID(' . $tipo_code_formula . ',2,1)),""))';
				$xml    .= '<c r="' . $ref . '" s="1"><f>' . self::xml_escape( $formula ) . '</f></c>';
				continue;
			}

			$cod_digit_match = array(
				'cod_d1' => 1,
				'cod_d2' => 2,
				'cod_d3' => 3,
				'cod_d4' => 4,
				'cod_d5' => 5,
				'cod_d6' => 6,
			);
			if ( isset( $cod_digit_match[ $key ] ) && '' === trim( (string) $value ) ) {
				$pos     = $cod_digit_match[ $key ];
				$formula = 'IF($F' . $row_number . '="","",MID(TEXT($F' . $row_number . ',"000000"),' . $pos . ',1))';
				$xml    .= '<c r="' . $ref . '" s="1"><f>' . self::xml_escape( $formula ) . '</f></c>';
				continue;
			}

			if ( 'id' === $key && is_numeric( $value ) && '' !== (string) $value ) {
				$xml .= '<c r="' . $ref . '" s="' . (int) $s . '"><v>' . self::xml_escape( (string) $value ) . '</v></c>';
				continue;
			}

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

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

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

	/**
	 * @param string $content Contenido.
	 * @param string $filename Nombre archivo.
	 * @return array<int, array<string, string>>|\WP_Error
	 */
	private static function parse_file( $content, $filename ) {
		$extension = strtolower( pathinfo( $filename, PATHINFO_EXTENSION ) );

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

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

		if ( str_contains( $content, 'schemas-microsoft-com:office:spreadsheet' ) || str_contains( $content, '<Workbook' ) || str_contains( $content, '<table' ) || str_contains( $content, '<Table' ) ) {
			return self::parse_html_table( $content );
		}

		return self::parse_csv( $content );
	}

	/**
	 * @param string $content Contenido binario.
	 * @return array<int, array<string, string>>|\WP_Error
	 */
	private static function parse_xlsx_upload( $content ) {
		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( 'codigos-etiqueta-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' ) );
		}

		$sheet_xml = $zip->getFromName( 'xl/worksheets/sheet1.xml' );
		$shared    = $zip->getFromName( 'xl/sharedStrings.xml' );
		$zip->close();
		@unlink( $tmp ); // phpcs:ignore WordPress.PHP.NoSilencedErrors.Discouraged

		if ( false === $sheet_xml || '' === trim( $sheet_xml ) ) {
			return new WP_Error( 'xlsx', __( 'No se encontró la hoja de datos en el archivo .xlsx.', 'logistica' ) );
		}

		$shared_strings = self::parse_xlsx_shared_strings( false === $shared ? '' : $shared );
		$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' ) );
		}

		$all_rows = array();

		foreach ( $row_nodes as $row_node ) {
			$cells = self::parse_xlsx_row_cells( $row_node, $shared_strings );
			if ( ! empty( $cells ) ) {
				$all_rows[] = $cells;
			}
		}

		if ( count( $all_rows ) < 2 ) {
			return new WP_Error( 'empty', __( 'El archivo no contiene filas de datos.', 'logistica' ) );
		}

		return self::parse_rows_from_sheet( $all_rows );
	}

	/**
	 * @param array<int, array<int, string>> $all_rows Filas crudas.
	 * @return array<int, array<string, string>>|\WP_Error
	 */
	private static function parse_rows_from_sheet( $all_rows ) {
		$header = self::resolve_import_header( array_slice( $all_rows, 0, 3 ) );
		if ( is_wp_error( $header ) ) {
			return $header;
		}

		$map        = $header['map'];
		$data_start = (int) $header['data_start'];
		$rows       = array();

		for ( $i = $data_start; $i < count( $all_rows ); $i++ ) {
			$row = self::cells_to_row( $all_rows[ $i ], $map );
			if ( '' === trim( (string) ( $row['referencia'] ?? '' ) ) ) {
				continue;
			}
			$rows[] = $row;
		}

		return $rows;
	}

	/**
	 * @param string $content CSV.
	 * @return array<int, array<string, string>>|\WP_Error
	 */
	private static function parse_csv( $content ) {
		$content = preg_replace( '/^\xEF\xBB\xBF/', '', $content );
		$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], ';' ) ? ';' : ',';
		$parsed    = array();
		foreach ( $lines as $line ) {
			if ( '' === trim( $line ) ) {
				continue;
			}
			$parsed[] = str_getcsv( $line, $delimiter );
		}

		if ( count( $parsed ) < 2 ) {
			return new WP_Error( 'empty', __( 'El archivo no contiene filas de datos.', 'logistica' ) );
		}

		return self::parse_rows_from_sheet( $parsed );
	}

	/**
	 * @param string $content HTML/XLS/XML.
	 * @return array<int, array<string, string>>|\WP_Error
	 */
	private static function parse_html_table( $content ) {
		$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' );
		}

		$all_rows = array();

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

			$cells = array();
			foreach ( $tr->childNodes as $node ) {
				if ( ! $node instanceof DOMElement || ! in_array( $node->nodeName, array( 'td', 'th', 'TD', 'TH', 'Cell' ), true ) ) {
					continue;
				}

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

			if ( ! empty( $cells ) ) {
				$all_rows[] = $cells;
			}
		}

		if ( count( $all_rows ) < 2 ) {
			return new WP_Error( 'empty', __( 'El archivo no contiene filas de datos.', 'logistica' ) );
		}

		return self::parse_rows_from_sheet( $all_rows );
	}

	/**
	 * @param array<int|string, string> $map Mapa de columnas.
	 * @return true|\WP_Error
	 */
	private static function validate_header_map( $map ) {
		$labels   = self::columns();
		$required = array( 'referencia', 'tipo_producto', 'mercado', 'tamano' );

		foreach ( $required as $key ) {
			if ( in_array( $key, $map, true ) ) {
				continue;
			}

			return new WP_Error(
				'headers',
				sprintf(
					/* translators: %s: column label */
					__( 'La plantilla debe incluir la columna «%s».', 'logistica' ),
					$labels[ $key ] ?? $key
				)
			);
		}

		$has_code = in_array( 'codigo_producto', $map, true )
			|| in_array( 'cod_barras', $map, true )
			|| in_array( 'ean13', $map, true )
			|| in_array( 'cod_d1', $map, true );

		if ( ! $has_code ) {
			return new WP_Error(
				'headers',
				__( 'La plantilla debe incluir «Código producto (6 dígitos)», dígitos de código o «COD. BARRAS».', 'logistica' )
			);
		}

		return true;
	}

	/**
	 * @param array<int, array<int, string>> $candidate_rows Primeras filas del libro.
	 * @return array{map:array<int|string,string>,data_start:int}|\WP_Error
	 */
	private static function resolve_import_header( $candidate_rows ) {
		$attempts = min( 3, count( $candidate_rows ) );

		for ( $i = 0; $i < $attempts; $i++ ) {
			$map   = self::map_headers( $candidate_rows[ $i ] );
			$valid = self::validate_header_map( $map );
			if ( ! is_wp_error( $valid ) ) {
				return array(
					'map'        => $map,
					'data_start' => $i + 1,
				);
			}
		}

		return new WP_Error(
			'headers',
			__( 'No se reconocieron las columnas de la plantilla. Descarga la plantilla .xlsx del sistema e intenta de nuevo.', 'logistica' )
		);
	}

	/**
	 * @param array<int, string> $headers Encabezados.
	 * @return array<int|string, string>
	 */
	private static function map_headers( $headers ) {
		$keys    = array_merge( self::column_keys(), array( 'ean13' ) );
		$labels  = self::columns();
		$aliases = array(
			'referencia'      => array( 'referencia', 'ref', 'referencia_ref' ),
			'tipo_producto'   => array( 'tipo_producto', 'tipo', 'tipo_de_producto' ),
			'mercado'         => array( 'mercado', 'market' ),
			'tamano'          => array( 'tamano', 'tamaño', 'size' ),
			'codigo_producto' => array( 'codigo_producto', 'codigo_de_producto', 'cod_producto', 'codigo_producto_6_digitos' ),
			'observaciones'   => array( 'observaciones', 'notas', 'comentarios' ),
			'pais_1'          => array( 'pais_1', 'pais1', 'p1' ),
			'pais_2'          => array( 'pais_2', 'pais2', 'p2' ),
			'pais_3'          => array( 'pais_3', 'pais3', 'p3' ),
			'mercado_d'       => array( 'mercado_d', 'exp_nac', 'mercado_digit' ),
			'tipo_d1'         => array( 'tipo_d1', 'tipo1', 't1' ),
			'tipo_d2'         => array( 'tipo_d2', 'tipo2', 't2' ),
			'cod_d1'          => array( 'cod_d1', 'cod1', 'c1' ),
			'cod_d2'          => array( 'cod_d2', 'cod2', 'c2' ),
			'cod_d3'          => array( 'cod_d3', 'cod3', 'c3' ),
			'cod_d4'          => array( 'cod_d4', 'cod4', 'c4' ),
			'cod_d5'          => array( 'cod_d5', 'cod5', 'c5' ),
			'cod_d6'          => array( 'cod_d6', 'cod6', 'c6' ),
			'suma'            => array( 'suma' ),
			'dig_ver'         => array( 'dig_ver', 'digito_de_verif', 'digito_verif', 'digito_de_verificacion' ),
			'cod_barras'      => array( 'cod_barras', 'codigo_barras', 'cod_barra' ),
			'ean13'           => array( 'ean13', 'ean', 'ean_13' ),
			'id'              => array( 'id' ),
		);
		$map     = array();

		foreach ( $headers as $index => $header ) {
			$normalized = self::normalize_header( $header );
			if ( '' === $normalized ) {
				continue;
			}

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

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

				if ( $matches ) {
					$target = 'ean13' === $key ? 'cod_barras' : $key;
					$map[ $index ] = $target;
					break;
				}
			}
		}

		return $map;
	}

	/**
	 * @param array<int, string>        $cells Celdas.
	 * @param array<int|string,string> $map Mapa.
	 * @return array<string, string>
	 */
	private static function cells_to_row( $cells, $map ) {
		$row = array();
		foreach ( $map as $index => $key ) {
			$value = isset( $cells[ $index ] ) ? trim( (string) $cells[ $index ] ) : '';
			if ( isset( $row[ $key ] ) && '' !== $row[ $key ] && '' === $value ) {
				continue;
			}
			if ( 'mercado' === $key && isset( $row['mercado'] ) && '' !== $row['mercado'] ) {
				$label_market = Logistica_Label_Barcode::normalize_market( $row['mercado'] );
				$digit_market = Logistica_Label_Barcode::normalize_market( $value );
				if ( Logistica_Label_Etiqueta_Mercados::is_valid_key( $label_market ) && ! Logistica_Label_Etiqueta_Mercados::is_valid_key( $digit_market ) ) {
					continue;
				}
			}
			$row[ $key ] = $value;
		}
		return $row;
	}

	/**
	 * @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.
	 * @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->xpath( 'm: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 ) {
				if ( isset( $cell->is->t ) ) {
					$value = (string) $cell->is->t;
				} elseif ( isset( $cell->is->r ) ) {
					foreach ( $cell->is->r as $run ) {
						$value .= (string) ( $run->t ?? '' );
					}
				}
			} elseif ( 'str' === $type && isset( $cell->v ) ) {
				$value = (string) $cell->v;
			} 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 $value Texto.
	 * @return string
	 */
	private static function normalize_header( $value ) {
		$value = remove_accents( strtolower( trim( (string) $value ) ) );
		$value = preg_replace( '/[^a-z0-9]+/', '_', $value );
		return trim( (string) $value, '_' );
	}

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

	/**
	 * @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;
	}

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

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

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

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