<?php
/**
 * Plantilla Excel import/export para catálogo de rótulos de rollos.
 *
 * @package Logistica
 */

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

/**
 * Exporta e importa anchos de rótulos de rollos.
 */
class Logistica_Rotulo_Rollos_Catalog_Excel {

	/** Fila donde comienzan los datos editables (1=título, 2=subtítulo, 3=encabezados). */
	private const DATA_START_ROW = 4;

	/** Filas en blanco extra para nuevos registros en la plantilla. */
	private const TEMPLATE_EXTRA_ROWS = 25;
	/**
	 * Columnas de la plantilla.
	 *
	 * @return array<string, string> key => etiqueta.
	 */
	public static function columns() {
		return array(
			'id'            => __( 'ID', 'logistica' ),
			'tipo'          => __( 'Tipo', 'logistica' ),
			'tamano'        => __( 'Tamaño', 'logistica' ),
			'observaciones' => __( 'Observaciones', 'logistica' ),
			'ancho'         => __( 'Ancho (referencia)', '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 );
	}

	/**
	 * 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 ) {
			$id     = absint( $row['id'] ?? 0 );
			$tipo   = trim( (string) ( $row['tipo'] ?? '' ) );
			$tamano = trim( (string) ( $row['tamano'] ?? '' ) );

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

			$existing = $id > 0 ? Logistica_Rotulo_Rollos_Catalog::get_by_id( $id ) : null;
			$result   = Logistica_Rotulo_Rollos_Catalog::save_row(
				array(
					'id'            => $id,
					'tipo'          => $tipo,
					'tamano'        => $tamano,
					'observaciones' => (string) ( $row['observaciones'] ?? '' ),
				)
			);

			if ( is_wp_error( $result ) ) {
				++$stats['skipped'];
				if ( '' === $stats['error'] ) {
					$stats['error'] = $result->get_error_message();
				}
				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'],
		);
	}

	/**
	 * @return array<int, array<string, string>>
	 */
	private static function export_rows() {
		return array_map(
			static function ( $row ) {
				return array(
					'id'            => (string) ( $row['id'] ?? '' ),
					'tipo'          => (string) ( $row['tipo_label'] ?? $row['tipo'] ?? '' ),
					'tamano'        => (string) ( $row['tamano'] ?? '' ),
					'observaciones' => (string) ( $row['observaciones'] ?? '' ),
					'ancho'         => (string) ( $row['ancho_label'] ?? '' ),
				);
			},
			Logistica_Rotulo_Rollos_Catalog::get_all()
		);
	}

	/**
	 * @param array<int, array<string, string>> $rows Filas.
	 * @return void
	 */
	private static function download_xlsx_template( $rows ) {
		$tipo_labels   = array_values( Logistica_Rotulo_Rollos_Catalog::tipo_options() );
		$tipo_last_row = max( 1, count( $tipo_labels ) );
		$sheet_xml     = self::build_xlsx_sheet_xml( $rows, $tipo_last_row );
		$listas_xml    = self::build_listas_sheet_xml( $tipo_labels );
		$tmp           = wp_tempnam( 'rotulos-rollos-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="Rotulos rollos" sheetId="1" r:id="rId1"/>'
			. '<sheet name="Listas" 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', $listas_xml );
		$zip->close();

		nocache_headers();
		header( 'Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' );
		header( 'Content-Disposition: attachment; filename="rotulos-rollos-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, string>> $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="rotulos-rollos-plantilla.csv"' );

		$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( self::columns() ), ';' );

		$data_rows = empty( $rows ) ? array( array() ) : $rows;
		foreach ( $data_rows as $row ) {
			$line = array();
			foreach ( array_keys( self::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, string>> $rows          Filas.
	 * @param int                               $tipo_last_row Última fila de tipos en hoja Listas.
	 * @return string
	 */
	private static function build_xlsx_sheet_xml( $rows, $tipo_last_row = 2 ) {
		$columns      = self::columns();
		$keys         = array_keys( $columns );
		$last_col     = self::xlsx_column_letter( count( $keys ) - 1 );
		$data_count   = max( 1, count( $rows ) );
		$total_slots  = $data_count + self::TEMPLATE_EXTRA_ROWS;
		$last_row     = self::DATA_START_ROW + $total_slots - 1;
		$user         = wp_get_current_user();
		$user_label   = $user && $user->exists() ? $user->display_name : __( 'Usuario', 'logistica' );
		$title        = __( 'RÓTULOS DE ROLLOS — JB Planeación', 'logistica' );
		$subtitle     = sprintf(
			/* translators: 1: export date/time 2: record count 3: user name */
			__( 'Exportado el %1$s · %2$d registro(s) · %3$s · La columna Ancho es solo referencia al importar.', 'logistica' ),
			wp_date( 'd/m/Y H:i' ),
			count( $rows ),
			$user_label
		);
		$sheet_rows   = array();
		$header_style = array_fill( 0, count( $keys ), 3 );

		$sheet_rows[] = self::build_styled_row_xml( 1, array( $title ), array( 1 ), $keys, 28 );
		$sheet_rows[] = self::build_styled_row_xml( 2, array( $subtitle ), array( 2 ), $keys, 20 );
		$sheet_rows[] = self::build_styled_row_xml( 3, array_values( $columns ), $header_style, $keys, 24 );

		$row_number = self::DATA_START_ROW;
		foreach ( $rows as $index => $row ) {
			$cells  = array();
			$styles = array();
			foreach ( $keys as $key ) {
				$cells[]  = $row[ $key ] ?? '';
				$styles[] = self::data_cell_style( $key, 0 === ( $index % 2 ) );
			}
			$sheet_rows[] = self::build_styled_row_xml( $row_number, $cells, $styles, $keys, 20 );
			++$row_number;
		}

		for ( $blank = count( $rows ); $blank < $total_slots; ++$blank ) {
			$styles = array();
			foreach ( $keys as $key ) {
				$styles[] = self::data_cell_style( $key, 0 === ( $blank % 2 ) );
			}
			$sheet_rows[] = self::build_styled_row_xml( $row_number, array_fill( 0, count( $keys ), '' ), $styles, $keys, 20 );
			++$row_number;
		}

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

		$validations = self::build_xlsx_data_validations_xml( $last_row, $tipo_last_row );

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

	/**
	 * @param string $key       Clave columna.
	 * @param bool   $even_row  Fila par (rayado).
	 * @return int
	 */
	private static function data_cell_style( $key, $even_row ) {
		if ( 'ancho' === $key ) {
			return 5;
		}
		if ( 'id' === $key ) {
			return $even_row ? 6 : 7;
		}
		return $even_row ? 4 : 0;
	}

	/**
	 * @param string[] $tipo_labels Etiquetas de tipo.
	 * @return string
	 */
	private static function build_listas_sheet_xml( $tipo_labels ) {
		$rows = array(
			self::build_styled_row_xml( 1, array( __( 'Tipo', 'logistica' ) ), array( 3 ), array( 'tipo' ), 20 ),
		);
		$row_number = 2;
		foreach ( $tipo_labels as $label ) {
			$rows[] = self::build_styled_row_xml( $row_number, array( $label ), array( 0 ), array( 'tipo' ), 18 );
			++$row_number;
		}

		return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
			. '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'
			. '<cols><col min="1" max="1" width="18" customWidth="1"/></cols>'
			. '<sheetData>' . implode( '', $rows ) . '</sheetData>'
			. '</worksheet>';
	}

	/**
	 * @param int $last_data_row Última fila editable.
	 * @param int $tipo_last_row Última fila de tipos.
	 * @return string
	 */
	private static function build_xlsx_data_validations_xml( $last_data_row, $tipo_last_row ) {
		$start    = self::DATA_START_ROW;
		$last     = max( $start, (int) $last_data_row );
		$tipo_col = self::xlsx_column_letter( 1 );
		$sqref    = $tipo_col . $start . ':' . $tipo_col . $last;

		return '<dataValidations count="1">'
			. self::xlsx_list_validation(
				$sqref,
				'Listas!$A$2:$A$' . max( 2, (int) $tipo_last_row ),
				__( 'Tipo', 'logistica' ),
				__( 'Seleccione Rollo o Fondos.', 'logistica' )
			)
			. '</dataValidations>';
	}

	/**
	 * @param string $sqref      Rango celdas.
	 * @param string $list_range Referencia lista.
	 * @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 válido', 'logistica' ) ) . '</errorTitle>'
			. '<error>' . self::xml_escape( __( 'Seleccione un valor de la lista.', 'logistica' ) ) . '</error>'
			. '</dataValidation>';
	}

	/**
	 * @return string
	 */
	private static function build_column_widths_xml() {
		$widths = array(
			'id'            => 8,
			'tipo'          => 14,
			'tamano'        => 14,
			'observaciones' => 32,
			'ancho'         => 22,
		);
		$xml    = '<cols>';
		$index  = 0;

		foreach ( array_keys( self::columns() ) as $key ) {
			$width = $widths[ $key ] ?? 16;
			$xml  .= '<col min="' . ( $index + 1 ) . '" max="' . ( $index + 1 ) . '" width="' . $width . '" customWidth="1"/>';
			++$index;
		}

		$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"/><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="FF13776D"/><bgColor indexed="64"/></patternFill></fill>'
			. '<fill><patternFill patternType="solid"><fgColor rgb="FFECFDF5"/><bgColor indexed="64"/></patternFill></fill>'
			. '<fill><patternFill patternType="solid"><fgColor rgb="FF0F5F57"/><bgColor indexed="64"/></patternFill></fill>'
			. '<fill><patternFill patternType="solid"><fgColor rgb="FFF1F5F9"/><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="8">'
			. '<xf numFmtId="0" fontId="0" fillId="0" borderId="1" xfId="0" applyFill="1" applyBorder="1" applyAlignment="1"><alignment vertical="center" wrapText="1"/></xf>'
			. '<xf numFmtId="0" fontId="1" fillId="2" borderId="0" xfId="0" applyFont="1" applyFill="1" applyAlignment="1"><alignment horizontal="center" 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>'
			. '<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="0" fontId="0" fillId="5" borderId="1" xfId="0" applyFill="1" applyBorder="1" applyAlignment="1"><alignment 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 vertical="center" wrapText="1"/></xf>'
			. '<xf numFmtId="0" fontId="0" fillId="5" borderId="1" xfId="0" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center"/></xf>'
			. '<xf numFmtId="0" fontId="0" fillId="0" borderId="1" xfId="0" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center"/></xf>'
			. '</cellXfs>'
			. '</styleSheet>';
	}

	/**
	 * @param int               $row_number Fila.
	 * @param array<int, mixed> $cells      Valores.
	 * @param array<int, int>   $styles     Estilos por celda.
	 * @param string[]          $keys       Claves columna.
	 * @param int               $height     Altura fila (pt).
	 * @return string
	 */
	private static function build_styled_row_xml( $row_number, $cells, $styles, $keys, $height = 0 ) {
		$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 = $cells[ $index ] ?? ( 0 === $index && 1 === count( $cells ) ? ( $cells[0] ?? '' ) : '' );
			$style = (int) ( $styles[ $index ] ?? ( $styles[0] ?? 0 ) );
			$ref   = self::xlsx_column_letter( $index ) . (int) $row_number;
			$xml  .= self::build_styled_cell_xml( $ref, $value, $style, $key );
		}

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

	/**
	 * @param string $ref   Referencia celda.
	 * @param mixed  $value Valor.
	 * @param int    $style Índice estilo.
	 * @param string $key   Clave columna.
	 * @return string
	 */
	private static function build_styled_cell_xml( $ref, $value, $style, $key ) {
		if ( 'id' === $key && is_numeric( $value ) && '' !== (string) $value ) {
			return '<c r="' . $ref . '" s="' . (int) $style . '"><v>' . self::xml_escape( (string) $value ) . '</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 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( 'rotulos-rollos-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 RotulosRollos 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' ) );
		}

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

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

			if ( empty( $map ) ) {
				$candidate = self::map_headers( $cells );
				if ( in_array( 'tipo', $candidate, true ) && in_array( 'tamano', $candidate, true ) ) {
					$map = $candidate;
				}
				continue;
			}

			$row = self::cells_to_row( $cells, $map );
			if ( self::row_has_data( $row ) ) {
				$rows[] = self::normalize_import_row( $row );
			}
		}

		if ( empty( $map ) ) {
			return new WP_Error( 'headers', __( 'La plantilla debe incluir las columnas Tipo y Tamaño.', '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.
	 * @param string[]         $shared_strings Tabla compartida.
	 * @return string[]
	 */
	private static function parse_xlsx_row_cells( $row_node, $shared_strings ) {
		$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 $content CSV.
	 * @return array<int, array<string, string>>|\WP_Error
	 */
	private static function parse_csv( $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], ';' ) ? ';' : ',';
		$header    = str_getcsv( array_shift( $lines ), $delimiter );
		$map       = self::map_headers( $header );

		if ( ! in_array( 'tipo', $map, true ) || ! in_array( 'tamano', $map, true ) ) {
			return new WP_Error( 'headers', __( 'La plantilla debe incluir las columnas Tipo y Tamaño.', 'logistica' ) );
		}

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

		return $rows;
	}

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

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

		foreach ( $trs as $index => $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 ) ) {
				continue;
			}

			if ( 0 === $index ) {
				$map = self::map_headers( $cells );
				continue;
			}

			$row = self::cells_to_row( $cells, $map );
			if ( self::row_has_data( $row ) ) {
				$rows[] = self::normalize_import_row( $row );
			}
		}

		return $rows;
	}

	/**
	 * @param array<string, string> $row Fila.
	 * @return bool
	 */
	private static function row_has_data( $row ) {
		return '' !== trim( (string) ( $row['tipo'] ?? '' ) ) || '' !== trim( (string) ( $row['tamano'] ?? '' ) );
	}

	/**
	 * @param array<int, string> $headers Encabezados.
	 * @return array<int|string, string>
	 */
	private static function map_headers( $headers ) {
		$keys    = array_keys( self::columns() );
		$labels  = array_values( self::columns() );
		$aliases = array(
			'tipo'   => array( 'tipo', 'material' ),
			'tamano' => array( 'tamano', 'tamaño', 'size' ),
			'ancho'  => array( 'ancho', 'ancho_referencia', 'ancho_referencia' ),
		);
		$map     = array();

		foreach ( $headers as $index => $header ) {
			$normalized = self::normalize_header( $header );
			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.
	 * @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 array<string, string> $row Fila importada.
	 * @return array<string, string>
	 */
	private static function normalize_import_row( $row ) {
		if ( isset( $row['tipo'] ) ) {
			$row['tipo'] = self::normalize_tipo( $row['tipo'] );
		}

		if ( isset( $row['id'] ) ) {
			$row['id'] = (string) absint( $row['id'] );
		}

		return $row;
	}

	/**
	 * @param string $value Tipo.
	 * @return string
	 */
	private static function normalize_tipo( $value ) {
		$value      = trim( (string) $value );
		$normalized = self::normalize_header( $value );

		foreach ( Logistica_Rotulo_Rollos_Catalog::tipo_options() as $key => $label ) {
			if ( $normalized === self::normalize_header( $key ) || $normalized === self::normalize_header( $label ) ) {
				return (string) $key;
			}
		}

		return strtoupper( sanitize_text_field( $value ) );
	}

	/**
	 * @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;
		$length  = strlen( $letters );

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

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

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