<?php
/**
 * Bulk Excel template export/import for order closing.
 *
 * @package Logistica
 */

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

/**
 * Editable Excel workbook (.xlsx) and upload merge.
 */
class Logistica_Order_Closing_Bulk_Excel {
	const TEMPLATE_VERSION = '2';

	/**
	 * Base columns editable in Excel (identity columns are read-only).
	 *
	 * @return string[]
	 */
	private static function editable_base_fields() {
		return array( 'cod', 'lote', 'largo', 'ancho', 'alto', 'clave', 'gramaje', 'calibre', 'ajuste', 'rendimiento' );
	}

	/**
	 * Reference header columns editable in Excel.
	 *
	 * @return string[]
	 */
	private static function editable_reference_fields() {
		return array( 'nombre', 'unidades', 'formula_template' );
	}

	/**
	 * Line columns editable in Excel.
	 *
	 * @return string[]
	 */
	private static function editable_line_fields() {
		return array( 'label', 'paquetes', 'unidades_paquete', 'base_tipo' );
	}

	/**
	 * Downloads a multi-sheet editable template with current data.
	 *
	 * @return void
	 */
	public static function download_template() {
		if ( ! class_exists( 'ZipArchive', false ) && ! function_exists( 'fopen' ) ) {
			wp_die( esc_html__( 'No se puede generar el Excel en este servidor.', 'logistica' ) );
		}

		try {
			$config = Logistica_Order_Closing::get_config();
			$sheets = array(
				array(
					'name' => 'Instrucciones',
					'rows' => self::build_instructions_sheet_rows(),
				),
				array(
					'name' => 'Bases',
					'rows' => self::build_bases_sheet_rows( $config['bases'] ?? array() ),
				),
				array(
					'name' => 'Referencias',
					'rows' => self::build_references_sheet_rows( $config['referencias'] ?? array() ),
				),
				array(
					'name' => 'Lineas',
					'rows' => self::build_lines_sheet_rows( $config['referencias'] ?? array() ),
				),
			);

			if ( class_exists( 'ZipArchive', false ) ) {
				self::download_xlsx_workbook( $sheets );
			}

			self::download_csv_workbook( $sheets );
		} catch ( Throwable $e ) {
			while ( ob_get_level() > 0 ) {
				ob_end_clean();
			}
			wp_die(
				esc_html(
					sprintf(
						/* translators: %s: error message */
						__( 'Error al exportar Excel: %s', 'logistica' ),
						$e->getMessage()
					)
				)
			);
		}
	}

	/**
	 * @return array<int, array<int, string>>
	 */
	private static function build_instructions_sheet_rows() {
		return array(
			array( __( 'Plantilla de actualización masiva — Cierre de órdenes', 'logistica' ) ),
			array( __( '1. Edita solo las celdas amarillas en las hojas Bases, Referencias y Líneas.', 'logistica' ) ),
			array( __( '2. No cambies base_id, ref_id ni linea_tipo (identifican cada fila).', 'logistica' ) ),
			array( __( '3. Guarda el archivo y súbelo en Cierre de órdenes.', 'logistica' ) ),
			array( __( '4. Bases = códigos y medidas. Referencias = unidades producidas. Líneas = paquetes por bloque.', 'logistica' ) ),
			array( __( 'Versión plantilla', 'logistica' ), self::TEMPLATE_VERSION ),
		);
	}

	/**
	 * @param array<int, array<string, mixed>> $bases Bases list.
	 * @return array<int, array<int, string>>
	 */
	private static function build_bases_sheet_rows( $bases ) {
		$rows = array(
			array( __( 'Bases permanentes — editar celdas amarillas', 'logistica' ) ),
			array( 'base_id', 'variante', 'tipo', 'referencia', 'cod', 'lote', 'largo', 'ancho', 'alto', 'clave', 'gramaje', 'calibre', 'ajuste', 'rendimiento' ),
		);

		foreach ( $bases as $base ) {
			$rows[] = array(
				(string) ( $base['id'] ?? '' ),
				(string) ( $base['variante'] ?? '' ),
				(string) ( $base['tipo'] ?? '' ),
				(string) ( $base['referencia'] ?? '' ),
				(string) ( $base['cod'] ?? '' ),
				(string) ( $base['lote'] ?? '' ),
				(string) ( $base['largo'] ?? '' ),
				(string) ( $base['ancho'] ?? '' ),
				(string) ( $base['alto'] ?? '' ),
				(string) ( $base['clave'] ?? '' ),
				(string) ( $base['gramaje'] ?? '' ),
				(string) ( $base['calibre'] ?? '' ),
				(string) ( $base['ajuste'] ?? '' ),
				(string) ( $base['rendimiento'] ?? '' ),
			);
		}

		return $rows;
	}

	/**
	 * @param array<int, array<string, mixed>> $references References.
	 * @return array<int, array<int, string>>
	 */
	private static function build_references_sheet_rows( $references ) {
		$rows = array(
			array( __( 'Referencias — editar celdas amarillas', 'logistica' ) ),
			array( 'ref_id', 'nombre', 'variante', 'tipo_producto', 'unidad_empaque', 'unidades', 'formula_template' ),
		);

		foreach ( $references as $reference ) {
			$rows[] = array(
				(string) ( $reference['id'] ?? '' ),
				(string) ( $reference['nombre'] ?? '' ),
				(string) ( $reference['variante'] ?? '' ),
				(string) ( $reference['tipo_producto'] ?? '' ),
				(string) ( $reference['unidad_empaque'] ?? '' ),
				(string) ( $reference['unidades'] ?? '' ),
				(string) ( $reference['formula_template'] ?? '' ),
			);
		}

		return $rows;
	}

	/**
	 * @param array<int, array<string, mixed>> $references References with lines.
	 * @return array<int, array<int, string>>
	 */
	private static function build_lines_sheet_rows( $references ) {
		$rows = array(
			array( __( 'Bloques por referencia — editar celdas amarillas', 'logistica' ) ),
			array( 'ref_id', 'linea_tipo', 'label', 'paquetes', 'unidades_paquete', 'base_tipo', 'base_link_id' ),
		);

		foreach ( $references as $reference ) {
			$ref_id = (string) ( $reference['id'] ?? '' );
			foreach ( $reference['lineas'] ?? array() as $line ) {
				$rows[] = array(
					$ref_id,
					(string) ( $line['tipo'] ?? '' ),
					(string) ( $line['label'] ?? '' ),
					(string) ( $line['paquetes'] ?? '' ),
					(string) ( $line['unidades_paquete'] ?? '' ),
					(string) ( $line['base_tipo'] ?? '' ),
					(string) ( $line['base_link_id'] ?? '' ),
				);
			}
		}

		return $rows;
	}

	/**
	 * @param array<int, array{name:string, rows:array<int, array<int, string>>}> $sheets Workbook sheets.
	 * @return void
	 */
	private static function download_xlsx_workbook( $sheets ) {
		$tmp = wp_tempnam( 'cierre-ordenes-plantilla.xlsx' );
		if ( ! $tmp ) {
			return;
		}

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

		$content_types = '<?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"/>';

		$workbook_sheets = '';
		$workbook_rels   = '';
		foreach ( $sheets as $index => $sheet ) {
			$sheet_id   = $index + 1;
			$sheet_name = (string) ( $sheet['name'] ?? ( 'Hoja' . $sheet_id ) );
			$sheet_rows = isset( $sheet['rows'] ) && is_array( $sheet['rows'] ) ? $sheet['rows'] : array();

			$content_types .= '<Override PartName="/xl/worksheets/sheet' . $sheet_id . '.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>';
			$workbook_sheets .= '<sheet name="' . self::xml_cell_value( $sheet_name ) . '" sheetId="' . $sheet_id . '" r:id="rId' . $sheet_id . '"/>';
			$workbook_rels   .= '<Relationship Id="rId' . $sheet_id . '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet' . $sheet_id . '.xml"/>';
			$zip->addFromString( 'xl/worksheets/sheet' . $sheet_id . '.xml', self::build_xlsx_sheet_xml( $sheet_rows ) );
		}

		$content_types .= '</Types>';

		$zip->addFromString( '[Content_Types].xml', $content_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>' . $workbook_sheets . '</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">'
			. $workbook_rels
			. '</Relationships>'
		);
		$zip->close();

		self::send_download_headers(
			'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
			'cierre-ordenes-plantilla.xlsx',
			(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{name:string, rows:array<int, array<int, string>>}> $sheets Workbook sheets.
	 * @return void
	 */
	private static function download_csv_workbook( $sheets ) {
		self::send_download_headers( 'text/csv; charset=utf-8', 'cierre-ordenes-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

		foreach ( $sheets as $sheet ) {
			$sheet_key = self::sheet_key_from_name( (string) ( $sheet['name'] ?? '' ) );
			if ( '' === $sheet_key ) {
				continue;
			}

			fputcsv( $handle, array( $sheet_key ), ';' );
			foreach ( (array) ( $sheet['rows'] ?? array() ) as $row ) {
				fputcsv( $handle, array_map( array( __CLASS__, 'cell_to_string' ), (array) $row ), ';' );
			}
			fputcsv( $handle, array( '' ), ';' );
		}

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

	/**
	 * Limpia buffers y envía cabeceras de descarga limpia.
	 *
	 * @param string      $content_type MIME.
	 * @param string      $filename     Nombre archivo.
	 * @param string|null $length       Content-Length opcional.
	 * @return void
	 */
	private static function send_download_headers( $content_type, $filename, $length = null ) {
		while ( ob_get_level() > 0 ) {
			ob_end_clean();
		}

		nocache_headers();
		header( 'Content-Type: ' . $content_type );
		header( 'Content-Disposition: attachment; filename="' . $filename . '"' );
		if ( null !== $length && '' !== $length ) {
			header( 'Content-Length: ' . $length );
		}
	}

	/**
	 * @param mixed $value Celda.
	 * @return string
	 */
	private static function cell_to_string( $value ) {
		if ( is_array( $value ) || is_object( $value ) ) {
			return '';
		}

		$value = (string) $value;
		// OOXML / XML 1.0: eliminar controles ilegales.
		$value = preg_replace( '/[\x00-\x08\x0B\x0C\x0E-\x1F]/', '', $value );

		return is_string( $value ) ? $value : '';
	}

	/**
	 * @param array<int, array<int, string>> $rows Matrix rows.
	 * @return string
	 */
	private static function build_xlsx_sheet_xml( $rows ) {
		$sheet_rows = array();
		$row_number = 1;

		foreach ( $rows as $row ) {
			$sheet_rows[] = self::build_xlsx_row_xml( $row_number, (array) $row );
			++$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 Row number.
	 * @param array<int, string>     $cells Cell values.
	 * @return string
	 */
	private static function build_xlsx_row_xml( $row_number, $cells ) {
		$xml = '<row r="' . (int) $row_number . '">';

		foreach ( array_values( $cells ) as $index => $value ) {
			$ref  = self::xlsx_column_letter( $index ) . (int) $row_number;
			$xml .= '<c r="' . $ref . '" t="inlineStr"><is><t>' . self::xml_cell_value( $value ) . '</t></is></c>';
		}

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

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

	/**
	 * Imports an uploaded workbook and merges into stored config.
	 *
	 * @param array $file $_FILES row.
	 * @return array{message:string,bases:int,referencias:int,lineas:int,skipped:int}
	 */
	public static function import_upload( $file ) {
		if ( empty( $file ) || ! is_array( $file ) ) {
			return self::import_error_result(
				__( 'No se recibió ningún archivo. Elige un archivo .xls, .xlsx o .csv antes de subir.', 'logistica' )
			);
		}

		if ( isset( $file['error'] ) && UPLOAD_ERR_OK !== (int) $file['error'] ) {
			return self::import_error_result( self::upload_error_message( (int) $file['error'] ) );
		}

		if ( empty( $file['tmp_name'] ) || ! is_uploaded_file( $file['tmp_name'] ) ) {
			return self::import_error_result(
				__( 'No se recibió ningún archivo. Si el archivo es muy grande, aumenta upload_max_filesize en PHP o prueba de nuevo.', 'logistica' )
			);
		}

		$parsed = self::parse_upload_file( $file['tmp_name'], (string) ( $file['name'] ?? '' ) );

		if ( is_wp_error( $parsed ) ) {
			return array(
				'message'     => $parsed->get_error_message(),
				'bases'       => 0,
				'referencias' => 0,
				'lineas'      => 0,
				'skipped'     => 0,
				'error'       => true,
			);
		}

		$config = Logistica_Order_Closing::get_config();
		$stats  = self::merge_import_into_config( $config, $parsed );

		$updated_total = (int) $stats['bases'] + (int) $stats['referencias'] + (int) $stats['lineas'];
		if ( $updated_total <= 0 ) {
			return array(
				'message'     => sprintf(
					/* translators: %d: skipped row count */
					__( 'No se aplicó ningún cambio (%d filas no coincidieron). No modifiques base_id, ref_id ni linea_tipo. Si guardaste en Excel, usa formato .xlsx o la plantilla descargada del sistema.', 'logistica' ),
					(int) $stats['skipped']
				),
				'bases'       => 0,
				'referencias' => 0,
				'lineas'      => 0,
				'skipped'     => (int) $stats['skipped'],
				'error'       => true,
			);
		}

		Logistica_Order_Closing::save_config(
			array(
				'bases'       => $config['bases'],
				'referencias' => $config['referencias'],
			)
		);

		return array(
			'message'     => sprintf(
				/* translators: 1: bases updated, 2: references, 3: lines, 4: skipped rows */
				__( 'Importación completada: %1$d bases, %2$d referencias, %3$d líneas actualizadas (%4$d filas ignoradas).', 'logistica' ),
				(int) $stats['bases'],
				(int) $stats['referencias'],
				(int) $stats['lineas'],
				(int) $stats['skipped']
			),
			'bases'       => (int) $stats['bases'],
			'referencias' => (int) $stats['referencias'],
			'lineas'      => (int) $stats['lineas'],
			'skipped'     => (int) $stats['skipped'],
			'error'       => false,
		);
	}

	/**
	 * @param string $message Error message.
	 * @return array{message:string,bases:int,referencias:int,lineas:int,skipped:int,error:bool}
	 */
	private static function import_error_result( $message ) {
		return array(
			'message'     => $message,
			'bases'       => 0,
			'referencias' => 0,
			'lineas'      => 0,
			'skipped'     => 0,
			'error'       => true,
		);
	}

	/**
	 * @param int $code PHP upload error code.
	 * @return string
	 */
	private static function upload_error_message( $code ) {
		switch ( $code ) {
			case UPLOAD_ERR_INI_SIZE:
			case UPLOAD_ERR_FORM_SIZE:
				return __( 'El archivo supera el tamaño máximo permitido por el servidor.', 'logistica' );
			case UPLOAD_ERR_PARTIAL:
				return __( 'La subida se interrumpió. Vuelve a intentarlo.', 'logistica' );
			case UPLOAD_ERR_NO_FILE:
				return __( 'No se recibió ningún archivo. Selecciona un archivo antes de subir.', 'logistica' );
			default:
				return __( 'No se pudo subir el archivo. Vuelve a intentarlo.', 'logistica' );
		}
	}

	/**
	 * @param array $config Config (by reference).
	 * @param array $parsed Parsed sheets.
	 * @return array{bases:int,referencias:int,lineas:int,skipped:int}
	 */
	private static function merge_import_into_config( &$config, $parsed ) {
		$stats = array(
			'bases'       => 0,
			'referencias' => 0,
			'lineas'      => 0,
			'skipped'     => 0,
		);

		$bases_index = self::build_entity_index( $config['bases'] ?? array(), 'id' );
		$bases_key_index = array();
		foreach ( $config['bases'] ?? array() as $index => $base ) {
			$key = self::base_row_match_key( $base );
			if ( '' !== $key ) {
				$bases_key_index[ $key ] = $index;
			}
		}

		foreach ( $parsed['bases'] ?? array() as $row ) {
			$index = self::resolve_entity_index( $bases_index, $row['base_id'] ?? '' );
			if ( null === $index ) {
				$index = self::resolve_entity_index( $bases_key_index, self::base_row_match_key( $row ) );
			}
			if ( null === $index ) {
				++$stats['skipped'];
				continue;
			}
			foreach ( self::editable_base_fields() as $field ) {
				if ( array_key_exists( $field, $row ) ) {
					$config['bases'][ $index ][ $field ] = sanitize_text_field( (string) $row[ $field ] );
				}
			}
			++$stats['bases'];
		}

		$refs_index = self::build_entity_index( $config['referencias'] ?? array(), 'id' );

		foreach ( $parsed['referencias'] ?? array() as $row ) {
			$index = self::resolve_entity_index( $refs_index, $row['ref_id'] ?? '' );
			if ( null === $index ) {
				++$stats['skipped'];
				continue;
			}
			foreach ( self::editable_reference_fields() as $field ) {
				if ( array_key_exists( $field, $row ) ) {
					$config['referencias'][ $index ][ $field ] = sanitize_text_field( (string) $row[ $field ] );
				}
			}
			++$stats['referencias'];
		}

		$lines_index = array();
		foreach ( $config['referencias'] ?? array() as $ref_index => $reference ) {
			$ref_id = trim( (string) ( $reference['id'] ?? '' ) );
			foreach ( $reference['lineas'] ?? array() as $line_index => $line ) {
				$tipo = sanitize_key( $line['tipo'] ?? '' );
				if ( '' !== $tipo && '' !== $ref_id ) {
					foreach ( self::id_lookup_keys( $ref_id . '|' . $tipo ) as $lookup_key ) {
						$lines_index[ $lookup_key ] = array( $ref_index, $line_index );
					}
				}
			}
		}

		foreach ( $parsed['lineas'] ?? array() as $row ) {
			$linea_tipo = sanitize_key( $row['linea_tipo'] ?? '' );
			$line_match = null;
			foreach ( self::id_lookup_keys( ( $row['ref_id'] ?? '' ) . '|' . $linea_tipo ) as $lookup_key ) {
				if ( isset( $lines_index[ $lookup_key ] ) ) {
					$line_match = $lines_index[ $lookup_key ];
					break;
				}
			}

			if ( null === $line_match || '' === $linea_tipo ) {
				++$stats['skipped'];
				continue;
			}

			list( $ref_index, $line_index ) = $line_match;
			foreach ( self::editable_line_fields() as $field ) {
				if ( array_key_exists( $field, $row ) ) {
					$config['referencias'][ $ref_index ]['lineas'][ $line_index ][ $field ] = sanitize_text_field( (string) $row[ $field ] );
				}
			}
			++$stats['lineas'];
		}

		return $stats;
	}

	/**
	 * @param string $path Temp file path.
	 * @param string $filename Original filename.
	 * @return array|WP_Error
	 */
	private static function parse_upload_file( $path, $filename ) {
		$content = file_get_contents( $path );
		if ( false === $content ) {
			return new WP_Error( 'logistica_import_read', __( 'No se pudo leer el archivo subido.', 'logistica' ) );
		}

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

		if ( self::is_legacy_binary_xls( $content ) ) {
			return new WP_Error(
				'logistica_import_binary_xls',
				__( 'Este .xls es formato antiguo de Excel. En Excel usa Guardar como y elige .xlsx, o vuelve a descargar la plantilla .xlsx del sistema.', 'logistica' )
			);
		}

		if ( in_array( $extension, array( 'xlsx', 'xlsm' ), true ) ) {
			$parsed = self::parse_xlsx_workbook( $path );
			if ( ! is_wp_error( $parsed ) ) {
				return $parsed;
			}
		}

		if ( false !== stripos( $content, 'schemas-microsoft-com:office:spreadsheet' ) || false !== stripos( $content, '<Worksheet' ) ) {
			$parsed = self::parse_spreadsheetml_workbook( $content );
			if ( ! is_wp_error( $parsed ) ) {
				return $parsed;
			}
		}

		if ( in_array( $extension, array( 'xls', 'html', 'htm' ), true ) || false !== stripos( $content, '<table' ) ) {
			$html_parsed = self::parse_html_workbook( $content );
			if ( ! is_wp_error( $html_parsed ) ) {
				return $html_parsed;
			}
		}

		if ( in_array( $extension, array( 'csv', 'txt' ), true ) ) {
			return self::parse_csv_workbook( $content );
		}

		return new WP_Error(
			'logistica_import_format',
			__( 'Formato no reconocido. Usa la plantilla descargada (.xlsx) o guarda el libro como .xlsx antes de subirlo.', 'logistica' )
		);
	}

	/**
	 * @param string $binary File header bytes.
	 * @return bool
	 */
	private static function is_legacy_binary_xls( $binary ) {
		return strlen( $binary ) >= 4 && "\xD0\xCF\x11\xE0" === substr( $binary, 0, 4 );
	}

	/**
	 * @param array  $items Entity list.
	 * @param string $id_key ID field name.
	 * @return array<string, int>
	 */
	private static function build_entity_index( $items, $id_key ) {
		$index = array();

		foreach ( $items as $position => $item ) {
			if ( ! is_array( $item ) ) {
				continue;
			}

			foreach ( self::id_lookup_keys( $item[ $id_key ] ?? '' ) as $lookup_key ) {
				$index[ $lookup_key ] = (int) $position;
			}
		}

		return $index;
	}

	/**
	 * @param array<string, int> $index Lookup table.
	 * @param string             $raw_id Raw ID from Excel.
	 * @return int|null
	 */
	private static function resolve_entity_index( $index, $raw_id ) {
		foreach ( self::id_lookup_keys( $raw_id ) as $lookup_key ) {
			if ( isset( $index[ $lookup_key ] ) ) {
				return (int) $index[ $lookup_key ];
			}
		}

		return null;
	}

	/**
	 * @param string $raw_id Raw identifier.
	 * @return string[]
	 */
	private static function id_lookup_keys( $raw_id ) {
		$raw = trim( (string) $raw_id );
		if ( '' === $raw ) {
			return array();
		}

		$keys = array( $raw, strtolower( $raw ) );
		$slug = sanitize_title( $raw );
		if ( '' !== $slug && ! in_array( $slug, $keys, true ) ) {
			$keys[] = $slug;
		}

		return array_values( array_unique( $keys ) );
	}

	/**
	 * @param array $row Base row.
	 * @return string
	 */
	private static function base_row_match_key( $row ) {
		$variante   = strtolower( trim( (string) ( $row['variante'] ?? '' ) ) );
		$tipo       = sanitize_key( $row['tipo'] ?? '' );
		$referencia = strtolower( trim( (string) ( $row['referencia'] ?? '' ) ) );

		if ( '' === $variante || '' === $tipo || '' === $referencia ) {
			return '';
		}

		return $variante . '|' . $tipo . '|' . $referencia;
	}

	/**
	 * @param SimpleXMLElement $row_node Spreadsheet row.
	 * @param string[]         $shared_strings Shared string table.
	 * @return string[]
	 */
	private static function parse_xlsx_row_cells( $row_node, $shared_strings ) {
		$cells = array();

		foreach ( $row_node->c as $cell ) {
			$ref   = (string) $cell['r'];
			$col   = preg_replace( '/[0-9]+/', '', $ref );
			$index = '' !== $col ? self::column_index_from_letters( $col ) : count( $cells );
			$value = '';
			$type  = (string) $cell['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 SimpleXMLElement $row_node SpreadsheetML row.
	 * @return string[]
	 */
	private static function parse_spreadsheetml_row_cells( $row_node ) {
		$cells = array();

		foreach ( $row_node->Cell as $cell ) {
			$index = count( $cells );
			$attrs = $cell->attributes( 'urn:schemas-microsoft-com:office:spreadsheet' );
			if ( isset( $attrs['Index'] ) ) {
				$index = max( 0, (int) $attrs['Index'] - 1 );
			}

			while ( count( $cells ) < $index ) {
				$cells[] = '';
			}

			$cells[ $index ] = trim( (string) ( $cell->Data ?? '' ) );
		}

		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 $html File contents.
	 * @return array|WP_Error
	 */
	private static function parse_html_workbook( $html ) {
		$previous = libxml_use_internal_errors( true );
		$dom      = new DOMDocument();
		$loaded   = $dom->loadHTML( '<?xml encoding="UTF-8">' . $html, LIBXML_NOERROR | LIBXML_NOWARNING );
		libxml_clear_errors();
		libxml_use_internal_errors( $previous );

		if ( ! $loaded ) {
			return new WP_Error( 'logistica_import_html', __( 'El archivo Excel no contiene tablas válidas.', 'logistica' ) );
		}

		$parsed = array(
			'bases'       => array(),
			'referencias' => array(),
			'lineas'      => array(),
		);

		$tables = $dom->getElementsByTagName( 'table' );
		foreach ( $tables as $table ) {
			if ( ! $table instanceof DOMElement ) {
				continue;
			}

			$sheet = $table->getAttribute( 'data-logistica-sheet' );
			$rows  = self::table_to_rows( $table );

			if ( '' === $sheet && ! empty( $rows[0] ) ) {
				$sheet = self::detect_sheet_from_headers( $rows[0] );
			}

			if ( empty( $rows ) ) {
				continue;
			}

			$sheet  = '';
			$header = array();
			while ( ! empty( $rows ) ) {
				$header = array_shift( $rows );
				$sheet  = self::detect_sheet_from_headers( $header );
				if ( '' !== $sheet ) {
					break;
				}
			}

			if ( '' === $sheet || empty( $header ) ) {
				continue;
			}

			$map = self::header_map_for_sheet( $sheet );

			foreach ( $rows as $row ) {
				$assoc = self::row_to_assoc( $header, $row, $map );
				if ( self::row_is_empty( $assoc ) ) {
					continue;
				}
				$parsed[ $sheet ][] = $assoc;
			}
		}

		if ( empty( $parsed['bases'] ) && empty( $parsed['referencias'] ) && empty( $parsed['lineas'] ) ) {
			return new WP_Error( 'logistica_import_empty', __( 'No se encontraron datos en el Excel. Usa la plantilla descargada sin borrar las columnas ID.', 'logistica' ) );
		}

		return $parsed;
	}

	/**
	 * @param string $path XLSX path.
	 * @return array|WP_Error
	 */
	private static function parse_xlsx_workbook( $path ) {
		if ( ! class_exists( 'ZipArchive' ) ) {
			return new WP_Error( 'logistica_import_zip', __( 'El servidor no puede leer archivos .xlsx. Guarda el libro como CSV o habilita ZipArchive en PHP.', 'logistica' ) );
		}

		$zip = new ZipArchive();
		if ( true !== $zip->open( $path ) ) {
			return new WP_Error( 'logistica_import_xlsx', __( 'No se pudo abrir el archivo .xlsx.', 'logistica' ) );
		}

		$shared_strings = array();
		$shared_xml     = $zip->getFromName( 'xl/sharedStrings.xml' );
		if ( $shared_xml ) {
			$shared = new SimpleXMLElement( $shared_xml );
			foreach ( $shared->si as $si ) {
				if ( isset( $si->t ) ) {
					$shared_strings[] = (string) $si->t;
				} elseif ( isset( $si->r ) ) {
					$text = '';
					foreach ( $si->r as $run ) {
						$text .= (string) $run->t;
					}
					$shared_strings[] = $text;
				}
			}
		}

		$parsed = array(
			'bases'       => array(),
			'referencias' => array(),
			'lineas'      => array(),
		);

		for ( $sheet_number = 1; $sheet_number <= 6; $sheet_number++ ) {
			$sheet_xml = $zip->getFromName( 'xl/worksheets/sheet' . $sheet_number . '.xml' );
			if ( ! $sheet_xml ) {
				continue;
			}

			$sheet  = new SimpleXMLElement( $sheet_xml );
			$rows   = array();
			$ns     = $sheet->getNamespaces( true );
			$main   = isset( $ns[''] ) ? $ns[''] : 'http://schemas.openxmlformats.org/spreadsheetml/2006/main';
			$sheet->registerXPathNamespace( 'm', $main );

			foreach ( $sheet->sheetData->row as $row ) {
				$line = self::parse_xlsx_row_cells( $row, $shared_strings );
				if ( ! empty( $line ) ) {
					$rows[] = $line;
				}
			}

			if ( count( $rows ) < 2 ) {
				continue;
			}

			$target_sheet = '';
			$header       = array();
			while ( ! empty( $rows ) ) {
				$header       = array_shift( $rows );
				$target_sheet = self::detect_sheet_from_headers( $header );
				if ( '' !== $target_sheet ) {
					break;
				}
			}

			if ( '' === $target_sheet || empty( $header ) ) {
				continue;
			}

			$map = self::header_map_for_sheet( $target_sheet );

			foreach ( $rows as $row ) {
				$assoc = self::row_to_assoc( $header, $row, $map );
				if ( self::row_is_empty( $assoc ) ) {
					continue;
				}
				$parsed[ $target_sheet ][] = $assoc;
			}
		}

		$zip->close();

		if ( empty( $parsed['bases'] ) && empty( $parsed['referencias'] ) && empty( $parsed['lineas'] ) ) {
			return new WP_Error( 'logistica_import_xlsx_empty', __( 'No se encontraron hojas Bases, Referencias o Líneas en el .xlsx.', 'logistica' ) );
		}

		return $parsed;
	}

	/**
	 * @param string $letters Column letters.
	 * @return int
	 */
	private static function column_index_from_letters( $letters ) {
		$letters = strtoupper( $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 CSV text.
	 * @return array
	 */
	private static function parse_csv_workbook( $content ) {
		$content = preg_replace( '/^\xEF\xBB\xBF/', '', $content );
		$lines   = preg_split( '/\r\n|\r|\n/', $content );
		$parsed  = array(
			'bases'       => array(),
			'referencias' => array(),
			'lineas'      => array(),
		);

		$current_sheet = '';
		$header        = array();

		foreach ( $lines as $line ) {
			if ( '' === trim( $line ) ) {
				continue;
			}

			$row = str_getcsv( $line );
			if ( empty( $row ) ) {
				continue;
			}

			$first = strtolower( trim( (string) ( $row[0] ?? '' ) ) );
			if ( in_array( $first, array( 'bases', 'referencias', 'lineas' ), true ) && 1 === count( array_filter( $row ) ) ) {
				$current_sheet = $first;
				$header        = array();
				continue;
			}

			if ( '' === $current_sheet ) {
				continue;
			}

			if ( empty( $header ) ) {
				$header = $row;
				continue;
			}

			$map  = self::header_map_for_sheet( $current_sheet );
			$assoc = self::row_to_assoc( $header, $row, $map );
			if ( ! self::row_is_empty( $assoc ) ) {
				$parsed[ $current_sheet ][] = $assoc;
			}
		}

		return $parsed;
	}

	/**
	 * @param string[] $header Header row.
	 * @return string
	 */
	private static function detect_sheet_from_headers( $header ) {
		$normalized = array_map(
			static function ( $value ) {
				return self::normalize_header_key( $value );
			},
			$header
		);

		if ( in_array( 'base_id', $normalized, true ) ) {
			return 'bases';
		}

		if ( in_array( 'linea_tipo', $normalized, true ) ) {
			return 'lineas';
		}

		if ( in_array( 'ref_id', $normalized, true ) ) {
			return 'referencias';
		}

		return '';
	}

	/**
	 * @param string $sheet Sheet key.
	 * @return array<string, string>
	 */
	private static function header_map_for_sheet( $sheet ) {
		if ( 'bases' === $sheet ) {
			return array(
				'base_id'    => 'base_id',
				'id_base'    => 'base_id',
				'id'         => 'base_id',
				'base'       => 'base_id',
				'variante'   => 'variante',
				'tipo'       => 'tipo',
				'referencia' => 'referencia',
				'cod'        => 'cod',
				'lote'       => 'lote',
				'largo'      => 'largo',
				'ancho'      => 'ancho',
				'alto'       => 'alto',
				'clave'      => 'clave',
				'gramaje'    => 'gramaje',
				'calibre'    => 'calibre',
				'ajuste'     => 'ajuste',
				'rendimiento'=> 'rendimiento',
			);
		}

		if ( 'referencias' === $sheet ) {
			return array(
				'ref_id'           => 'ref_id',
				'id_referencia'    => 'ref_id',
				'id_ref'           => 'ref_id',
				'referencia_id'    => 'ref_id',
				'nombre'           => 'nombre',
				'variante'         => 'variante',
				'tipo_producto'    => 'tipo_producto',
				'unidad_empaque'   => 'unidad_empaque',
				'unidades'         => 'unidades',
				'formula_template' => 'formula_template',
			);
		}

		return array(
			'ref_id'            => 'ref_id',
			'id_referencia'     => 'ref_id',
			'linea_tipo'        => 'linea_tipo',
			'tipo_linea'        => 'linea_tipo',
			'label'             => 'label',
			'etiqueta'          => 'label',
			'paquetes'          => 'paquetes',
			'unidades_paquete'  => 'unidades_paquete',
			'base_tipo'         => 'base_tipo',
			'base_link_id'      => 'base_link_id',
		);
	}

	/**
	 * @param string[]           $header Header cells.
	 * @param string[]           $row    Data cells.
	 * @param array<string,string> $map  Header map.
	 * @return array<string, string>
	 */
	private static function row_to_assoc( $header, $row, $map ) {
		$assoc = array();

		foreach ( $header as $index => $label ) {
			$key = self::normalize_header_key( $label );
			if ( ! isset( $map[ $key ] ) ) {
				continue;
			}

			$assoc[ $map[ $key ] ] = isset( $row[ $index ] ) ? trim( (string) $row[ $index ] ) : '';
		}

		return $assoc;
	}

	/**
	 * @param array<string, string> $row Row.
	 * @return bool
	 */
	private static function row_is_empty( $row ) {
		foreach ( $row as $value ) {
			if ( '' !== trim( (string) $value ) ) {
				return false;
			}
		}

		return true;
	}

	/**
	 * @param string $label Header label.
	 * @return string
	 */
	private static function normalize_header_key( $label ) {
		$key = strtolower( remove_accents( wp_strip_all_tags( (string) $label ) ) );
		$key = preg_replace( '/[^a-z0-9]+/', '_', $key );

		return trim( $key, '_' );
	}

	/**
	 * @param DOMElement $table Table node.
	 * @return array<int, array<int, string>>
	 */
	private static function table_to_rows( DOMElement $table ) {
		$rows = array();

		foreach ( $table->getElementsByTagName( 'tr' ) as $tr ) {
			$cells = array();
			foreach ( $tr->childNodes as $cell ) {
				if ( ! $cell instanceof DOMElement ) {
					continue;
				}
				if ( ! in_array( strtolower( $cell->tagName ), array( 'td', 'th' ), true ) ) {
					continue;
				}
				$cells[] = trim( wp_strip_all_tags( $cell->textContent ) );
			}

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

		return $rows;
	}

	/**
	 * @param string $value Cell value.
	 * @return string
	 */
	private static function xml_cell_value( $value ) {
		return htmlspecialchars( self::cell_to_string( $value ), ENT_XML1 | ENT_QUOTES, 'UTF-8' );
	}

	/**
	 * @param string[] $values  Cell values.
	 * @param string[] $styles Style IDs per cell.
	 * @return void
	 */
	private static function xml_row( $values, $styles = array() ) {
		echo '<Row>';
		foreach ( $values as $index => $value ) {
			$style = isset( $styles[ $index ] ) ? $styles[ $index ] : 'Default';
			echo '<Cell ss:StyleID="' . esc_attr( $style ) . '"><Data ss:Type="String">' . self::xml_cell_value( $value ) . '</Data></Cell>';
		}
		echo '</Row>';
	}

	/**
	 * @param string $sheet_name Worksheet name.
	 * @return void
	 */
	private static function xml_worksheet_open( $sheet_name ) {
		echo '<Worksheet ss:Name="' . self::xml_cell_value( $sheet_name ) . '"><Table>';
	}

	/**
	 * @return void
	 */
	private static function xml_worksheet_close() {
		echo '</Table></Worksheet>';
	}

	/**
	 * @param string $sheet_name Worksheet name.
	 * @return string
	 */
	private static function sheet_key_from_name( $sheet_name ) {
		$name = strtolower( remove_accents( (string) $sheet_name ) );

		if ( false !== strpos( $name, 'base' ) ) {
			return 'bases';
		}

		if ( false !== strpos( $name, 'referen' ) ) {
			return 'referencias';
		}

		if ( false !== strpos( $name, 'linea' ) ) {
			return 'lineas';
		}

		return '';
	}

	/**
	 * @param string $content SpreadsheetML XML.
	 * @return array|WP_Error
	 */
	private static function parse_spreadsheetml_workbook( $content ) {
		$previous = libxml_use_internal_errors( true );
		$xml      = simplexml_load_string( $content, 'SimpleXMLElement', LIBXML_NOERROR | LIBXML_NOWARNING );
		libxml_clear_errors();
		libxml_use_internal_errors( $previous );

		if ( ! $xml ) {
			return new WP_Error( 'logistica_import_ssxml', __( 'No se pudo leer el libro Excel XML.', 'logistica' ) );
		}

		$namespaces = $xml->getNamespaces( true );
		$ss_ns      = $namespaces[''] ?? 'urn:schemas-microsoft-com:office:spreadsheet';
		$xml->registerXPathNamespace( 'ss', $ss_ns );

		$parsed = array(
			'bases'       => array(),
			'referencias' => array(),
			'lineas'      => array(),
		);

		$worksheets = $xml->xpath( '//ss:Worksheet' );
		if ( empty( $worksheets ) ) {
			$worksheets = $xml->Worksheet;
		}

		foreach ( $worksheets as $worksheet ) {
			$sheet_name = (string) ( $worksheet['Name'] ?? $worksheet->attributes( $ss_ns )->Name ?? '' );
			$sheet_key  = self::sheet_key_from_name( $sheet_name );

			if ( '' === $sheet_key ) {
				continue;
			}

			$worksheet->registerXPathNamespace( 'ss', $ss_ns );
			$row_nodes = $worksheet->xpath( './/ss:Table/ss:Row' );
			if ( empty( $row_nodes ) ) {
				$row_nodes = $worksheet->Table->Row ?? array();
			}

			$rows = array();
			foreach ( $row_nodes as $row_node ) {
				$line = self::parse_spreadsheetml_row_cells( $row_node );
				if ( ! empty( $line ) ) {
					$rows[] = $line;
				}
			}

			$header = array();
			while ( ! empty( $rows ) ) {
				$header = array_shift( $rows );
				if ( '' !== self::detect_sheet_from_headers( $header ) ) {
					break;
				}
				$header = array();
			}

			if ( empty( $header ) ) {
				continue;
			}

			$map = self::header_map_for_sheet( $sheet_key );
			foreach ( $rows as $row ) {
				$assoc = self::row_to_assoc( $header, $row, $map );
				if ( ! self::row_is_empty( $assoc ) ) {
					$parsed[ $sheet_key ][] = $assoc;
				}
			}
		}

		if ( empty( $parsed['bases'] ) && empty( $parsed['referencias'] ) && empty( $parsed['lineas'] ) ) {
			return new WP_Error( 'logistica_import_ssxml_empty', __( 'Las hojas Bases, Referencias y Líneas están vacías o sin encabezados válidos.', 'logistica' ) );
		}

		return $parsed;
	}

	/**
	 * @param array $bases Bases list.
	 * @return void
	 */
	private static function render_bases_sheet_xml( $bases ) {
		self::xml_worksheet_open( 'Bases' );
		self::xml_row(
			array( __( 'Bases permanentes — editar celdas amarillas', 'logistica' ) ),
			array( 'Header' )
		);
		self::xml_row(
			array( 'base_id', 'variante', 'tipo', 'referencia', 'cod', 'lote', 'largo', 'ancho', 'alto', 'clave', 'gramaje', 'calibre', 'ajuste', 'rendimiento' ),
			array( 'ReadOnly', 'ReadOnly', 'ReadOnly', 'ReadOnly', 'Editable', 'Editable', 'Editable', 'Editable', 'Editable', 'Editable', 'Editable', 'Editable', 'Editable', 'Editable' )
		);

		foreach ( $bases as $base ) {
			self::xml_row(
				array(
					$base['id'] ?? '',
					$base['variante'] ?? '',
					$base['tipo'] ?? '',
					$base['referencia'] ?? '',
					$base['cod'] ?? '',
					$base['lote'] ?? '',
					$base['largo'] ?? '',
					$base['ancho'] ?? '',
					$base['alto'] ?? '',
					$base['clave'] ?? '',
					$base['gramaje'] ?? '',
					$base['calibre'] ?? '',
					$base['ajuste'] ?? '',
					$base['rendimiento'] ?? '',
				),
				array( 'ReadOnly', 'ReadOnly', 'ReadOnly', 'ReadOnly', 'Editable', 'Editable', 'Editable', 'Editable', 'Editable', 'Editable', 'Editable', 'Editable', 'Editable', 'Editable' )
			);
		}

		self::xml_worksheet_close();
	}

	/**
	 * @param array $references References.
	 * @return void
	 */
	private static function render_references_sheet_xml( $references ) {
		self::xml_worksheet_open( 'Referencias' );
		self::xml_row( array( __( 'Referencias — editar celdas amarillas', 'logistica' ) ), array( 'Header' ) );
		self::xml_row(
			array( 'ref_id', 'nombre', 'variante', 'tipo_producto', 'unidad_empaque', 'unidades', 'formula_template' ),
			array( 'ReadOnly', 'Editable', 'ReadOnly', 'ReadOnly', 'ReadOnly', 'Editable', 'Editable' )
		);

		foreach ( $references as $reference ) {
			self::xml_row(
				array(
					$reference['id'] ?? '',
					$reference['nombre'] ?? '',
					$reference['variante'] ?? '',
					$reference['tipo_producto'] ?? '',
					$reference['unidad_empaque'] ?? '',
					$reference['unidades'] ?? '',
					$reference['formula_template'] ?? '',
				),
				array( 'ReadOnly', 'Editable', 'ReadOnly', 'ReadOnly', 'ReadOnly', 'Editable', 'Editable' )
			);
		}

		self::xml_worksheet_close();
	}

	/**
	 * @param array $references References with lines.
	 * @return void
	 */
	private static function render_lines_sheet_xml( $references ) {
		self::xml_worksheet_open( 'Lineas' );
		self::xml_row( array( __( 'Bloques por referencia — editar celdas amarillas', 'logistica' ) ), array( 'Header' ) );
		self::xml_row(
			array( 'ref_id', 'linea_tipo', 'label', 'paquetes', 'unidades_paquete', 'base_tipo', 'base_link_id' ),
			array( 'ReadOnly', 'ReadOnly', 'Editable', 'Editable', 'Editable', 'Editable', 'ReadOnly' )
		);

		foreach ( $references as $reference ) {
			$ref_id = $reference['id'] ?? '';
			foreach ( $reference['lineas'] ?? array() as $line ) {
				self::xml_row(
					array(
						$ref_id,
						$line['tipo'] ?? '',
						$line['label'] ?? '',
						$line['paquetes'] ?? '',
						$line['unidades_paquete'] ?? '',
						$line['base_tipo'] ?? '',
						$line['base_link_id'] ?? '',
					),
					array( 'ReadOnly', 'ReadOnly', 'Editable', 'Editable', 'Editable', 'Editable', 'ReadOnly' )
				);
			}
		}

		self::xml_worksheet_close();
	}

	/**
	 * @return void
	 */
	private static function render_instructions_sheet_xml() {
		self::xml_worksheet_open( 'Instrucciones' );
		self::xml_row( array( __( 'Plantilla de actualización masiva — Cierre de órdenes', 'logistica' ) ), array( 'Header' ) );
		self::xml_row( array( __( '1. Edita solo las celdas amarillas en las hojas Bases, Referencias y Líneas.', 'logistica' ) ), array( 'Info' ) );
		self::xml_row( array( __( '2. No cambies base_id, ref_id ni linea_tipo (identifican cada fila).', 'logistica' ) ), array( 'Info' ) );
		self::xml_row( array( __( '3. Guarda el archivo y súbelo en Cierre de órdenes.', 'logistica' ) ), array( 'Info' ) );
		self::xml_row( array( __( '4. Bases = códigos y medidas. Referencias = unidades producidas. Líneas = paquetes por bloque.', 'logistica' ) ), array( 'Info' ) );
		self::xml_row( array( __( 'Versión plantilla', 'logistica' ), self::TEMPLATE_VERSION ), array( 'ReadOnly', 'ReadOnly' ) );
		self::xml_worksheet_close();
	}
}
