<?php
/**
 * Plantilla Excel import/export para caracteristicas por codigo.
 *
 * @package Logistica
 */

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

/**
 * Exporta e importa caracteristicas por codigo.
 */
class Logistica_Order_Closing_Characteristics_Excel {

	/**
	 * Columnas de la plantilla.
	 *
	 * @return array<string, string> key => etiqueta.
	 */
	public static function columns() {
		return array(
			'codigo'      => __( 'Código', 'logistica' ),
			'descripcion' => __( 'Descripción', 'logistica' ),
			'tipo'        => __( 'Base', 'logistica' ),
			'largo'       => __( 'Largo', 'logistica' ),
			'ancho'       => __( 'Ancho', 'logistica' ),
			'alto'        => __( 'Alto (mm)', 'logistica' ),
			'clave'       => __( 'Clave', 'logistica' ),
			'calibre'     => __( 'Calibre (mm)', 'logistica' ),
		);
	}

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

		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 ) {
			$codigo = (string) ( $row['codigo'] ?? '' );
			if ( '' === trim( $codigo ) ) {
				++$stats['skipped'];
				continue;
			}

			$existing = Logistica_Order_Closing_Characteristics::get_by_codigo( $codigo );
			$result   = Logistica_Order_Closing_Characteristics::save_row( $row );

			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'],
		);
	}

	/**
	 * @param array<int, array<string, mixed>> $rows Filas.
	 * @return void
	 */
	private static function download_xlsx_template( $rows ) {
		$sheet_xml = self::build_xlsx_sheet_xml( $rows );
		$tmp       = wp_tempnam( 'caracteristicas-por-codigo-plantilla.xlsx' );

		if ( ! $tmp ) {
			return;
		}

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

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

		nocache_headers();
		header( 'Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' );
		header( 'Content-Disposition: attachment; filename="caracteristicas-por-codigo-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="caracteristicas-por-codigo-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, mixed>> $rows Filas.
	 * @return string
	 */
	private static function build_xlsx_sheet_xml( $rows ) {
		$keys        = array_keys( self::columns() );
		$sheet_rows  = array();
		$sheet_rows[] = self::build_xlsx_row_xml( 1, array_values( self::columns() ), $keys, true );

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

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

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

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

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

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

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

	/**
	 * @param 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( 'caracteristicas-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 Características 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 $index => $row_node ) {
			$cells = self::parse_xlsx_row_cells( $row_node, $shared_strings );
			if ( empty( $cells ) ) {
				continue;
			}

			if ( 0 === (int) $index ) {
				$map = self::map_headers( $cells );
				if ( ! in_array( 'codigo', $map, true ) ) {
					return new WP_Error( 'headers', __( 'La plantilla debe incluir la columna Código.', 'logistica' ) );
				}
				continue;
			}

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

		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( 'codigo', $map, true ) ) {
			return new WP_Error( 'headers', __( 'La plantilla debe incluir la columna Código.', 'logistica' ) );
		}

		$rows = array();
		foreach ( $lines as $line ) {
			if ( '' === trim( $line ) ) {
				continue;
			}
			$row = self::cells_to_row( str_getcsv( $line, $delimiter ), $map );
			if ( '' !== ( $row['codigo'] ?? '' ) ) {
				$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 ( '' !== ( $row['codigo'] ?? '' ) ) {
				$rows[] = self::normalize_import_row( $row );
			}
		}

		return $rows;
	}

	/**
	 * @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', 'base', 'tipo base' ),
			'largo'   => array( 'largo', 'largo_mm', 'largo_pulg', 'largo_bolsa' ),
			'ancho'   => array( 'ancho', 'ancho_mm', 'ancho_pulg', 'ancho_bolsa' ),
			'calibre' => array( 'calibre', 'calibre_mm' ),
		);
		$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'] );
		}

		return $row;
	}

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

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

		return sanitize_key( $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' );
	}
}
