<?php
/**
 * Importación Excel/CSV para planeación semanal (formado e impresión).
 *
 * @package Logistica
 */

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

/**
 * Parsea archivos exportados por los módulos de planeación.
 */
class Logistica_Planning_Excel_Import {

	/**
	 * @param array<string, mixed> $file   Entrada $_FILES.
	 * @param array<string, mixed> $config Columnas, mapas y sanitizador.
	 * @return array{tasks:array<int, array<string, mixed>>}|\WP_Error
	 */
	public static function import_upload( $file, $config ) {
		if ( empty( $file['tmp_name'] ) || ! is_uploaded_file( $file['tmp_name'] ) ) {
			return new WP_Error( 'upload', __( 'No se recibió un archivo válido.', 'logistica' ) );
		}

		$content = file_get_contents( $file['tmp_name'] ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_get_contents_file_get_contents
		if ( false === $content || '' === $content ) {
			return new WP_Error( 'upload', __( 'El archivo está vacío.', 'logistica' ) );
		}

		$name = sanitize_file_name( (string) ( $file['name'] ?? 'import.xlsx' ) );
		$ext  = strtolower( pathinfo( $name, PATHINFO_EXTENSION ) );

		if ( 'csv' === $ext ) {
			$matrix = self::parse_csv_matrix( $content );
		} elseif ( 'xlsx' === $ext ) {
			$matrix = self::parse_xlsx_matrix( $content );
		} elseif ( 'xls' === $ext ) {
			$matrix = self::parse_xls_matrix( $content );
		} else {
			return new WP_Error( 'format', __( 'Formato no soportado. Usa .xlsx, .xls o .csv exportado desde este módulo.', 'logistica' ) );
		}

		if ( is_wp_error( $matrix ) ) {
			return $matrix;
		}

		$tasks = self::matrix_to_tasks( $matrix, $config );
		if ( is_wp_error( $tasks ) ) {
			return $tasks;
		}

		$sanitize = $config['sanitize_tasks'] ?? null;
		if ( is_callable( $sanitize ) ) {
			$tasks = call_user_func( $sanitize, $tasks );
		}

		return array(
			'tasks' => $tasks,
			'count' => count( $tasks ),
		);
	}

	/**
	 * @param array<int, array<int, string>> $matrix Matriz de celdas.
	 * @param array<string, mixed>         $config Configuración.
	 * @return array<int, array<string, mixed>>|\WP_Error
	 */
	private static function matrix_to_tasks( $matrix, $config ) {
		$columns     = is_array( $config['columns'] ?? null ) ? $config['columns'] : array();
		$header_row  = null;
		$column_map  = null;
		$current_day = null;
		$tasks       = array();
		$sort        = 0;

		foreach ( $matrix as $row_index => $cells ) {
			$cells = self::normalize_row( $cells );
			if ( self::is_empty_row( $cells ) ) {
				continue;
			}

			if ( null === $column_map ) {
				$map = self::map_header_row( $cells, $columns );
				if ( self::map_has_key( $map, 'op' ) ) {
					$column_map = $map;
					$header_row = $row_index;
				}
				continue;
			}

			if ( null !== $header_row && $row_index <= $header_row ) {
				continue;
			}

			$day_index = self::detect_day_index( $cells, $config['day_labels'] ?? array() );
			if ( null !== $day_index ) {
				$current_day = $day_index;
				continue;
			}

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

			$row = self::cells_to_assoc( $cells, $column_map );
			if ( '' === trim( (string) ( $row['op'] ?? '' ) ) ) {
				continue;
			}

			if ( null === $current_day ) {
				continue;
			}

			$tasks[] = self::row_to_task( $row, $current_day, $sort, $config );
			++$sort;
		}

		if ( null === $column_map ) {
			return new WP_Error(
				'headers',
				__( 'No se encontró la fila de encabezados (debe incluir la columna OP). Exporta el archivo desde este módulo e impórtalo sin modificar los títulos.', 'logistica' )
			);
		}

		if ( empty( $tasks ) ) {
			return new WP_Error( 'empty', __( 'No se encontraron actividades válidas para importar.', 'logistica' ) );
		}

		return $tasks;
	}

	/**
	 * @param array<int, string>   $cells       Celdas.
	 * @param array<string, mixed> $config      Configuración.
	 * @param int                  $current_day Día actual.
	 * @param int                  $sort        Orden.
	 * @return array<string, mixed>
	 */
	private static function row_to_task( $cells, $current_day, $sort, $config ) {
		$statuses   = is_array( $config['statuses'] ?? null ) ? $config['statuses'] : array();
		$labor      = is_array( $config['labor_types'] ?? null ) ? $config['labor_types'] : array();
		$complexity = is_array( $config['complexity_labels'] ?? null ) ? $config['complexity_labels'] : array();

		$start = self::parse_time_value( $cells['hora_inicio'] ?? '' );
		$end   = self::parse_time_value( $cells['hora_fin'] ?? '' );
		$dur   = (int) preg_replace( '/\D+/', '', (string) ( $cells['duracion'] ?? '' ) );

		if ( $dur < 15 && null !== $start && null !== $end && $end > $start ) {
			$dur = $end - $start;
		}
		if ( $dur < 15 ) {
			$dur = 60;
		}

		$task = array(
			'id'               => 'task_' . wp_generate_password( 8, false, false ),
			'day'              => $current_day,
			'sort_order'       => $sort,
			'start_minutes'    => null !== $start ? $start : 420,
			'duration_minutes' => $dur,
			'op'               => sanitize_text_field( (string) ( $cells['op'] ?? '' ) ),
			'status'           => self::resolve_key_from_label( $cells['estado'] ?? '', $statuses, 'pending' ),
			'labor_type'       => self::resolve_key_from_label( $cells['labor_type'] ?? '', $labor, 'otro' ),
			'labor'            => sanitize_text_field( (string) ( $cells['labor'] ?? '' ) ),
			'tamanos'          => sanitize_text_field( (string) ( $cells['tamanos'] ?? '' ) ),
			'complexity'       => self::resolve_key_from_label( $cells['complexity'] ?? '', $complexity, 'normal' ),
			'montaje'          => sanitize_text_field( (string) ( $cells['montaje'] ?? '' ) ),
			'troquel'          => sanitize_text_field( (string) ( $cells['troquel'] ?? '' ) ),
			'pegado'           => sanitize_text_field( (string) ( $cells['pegado'] ?? '' ) ),
			'prep_tinta'       => sanitize_text_field( (string) ( $cells['prep_tinta'] ?? '' ) ),
			'planchas'         => sanitize_text_field( (string) ( $cells['planchas'] ?? '' ) ),
			'meta'             => sanitize_text_field( (string) ( $cells['meta'] ?? '' ) ),
			'tintas'           => sanitize_text_field( (string) ( $cells['tintas'] ?? '' ) ),
			'colores'          => sanitize_text_field( (string) ( $cells['colores'] ?? '' ) ),
			'papel'            => sanitize_text_field( (string) ( $cells['papel'] ?? '' ) ),
			'actual_start'     => self::parse_time_value( $cells['inicio_real'] ?? '' ),
			'actual_end'       => self::parse_time_value( $cells['fin_real'] ?? '' ),
			'notes'            => sanitize_textarea_field( (string) ( $cells['notas'] ?? '' ) ),
		);

		return $task;
	}

	/**
	 * @param string               $label    Etiqueta en Excel.
	 * @param array<string,string> $map      Mapa clave => etiqueta.
	 * @param string               $fallback Clave por defecto.
	 * @return string
	 */
	private static function resolve_key_from_label( $label, $map, $fallback ) {
		$needle = self::normalize_header( $label );
		if ( '' === $needle ) {
			return $fallback;
		}

		foreach ( $map as $key => $item_label ) {
			if ( $needle === self::normalize_header( $item_label ) ) {
				return (string) $key;
			}
		}

		foreach ( $map as $key => $item_label ) {
			if ( str_contains( self::normalize_header( $item_label ), $needle ) || str_contains( $needle, self::normalize_header( $item_label ) ) ) {
				return (string) $key;
			}
		}

		return sanitize_key( $needle ) ?: $fallback;
	}

	/**
	 * @param array<int, string>   $cells      Celdas.
	 * @param array<int, string>   $day_labels Índice => nombre día.
	 * @return int|null
	 */
	private static function detect_day_index( $cells, $day_labels ) {
		$joined = self::normalize_header( implode( ' ', array_filter( $cells ) ) );
		if ( '' === $joined ) {
			return null;
		}

		foreach ( $day_labels as $index => $label ) {
			$day_norm = self::normalize_header( $label );
			if ( str_starts_with( $joined, $day_norm ) ) {
				return (int) $index;
			}
		}

		return null;
	}

	/**
	 * @param array<int, string> $cells Celdas.
	 * @return bool
	 */
	private static function is_skip_row( $cells ) {
		$text = self::normalize_header( implode( ' ', $cells ) );
		if ( '' === $text ) {
			return true;
		}

		if ( str_contains( $text, 'sin actividades' ) ) {
			return true;
		}

		if ( str_contains( $text, 'planeacion de' ) || str_contains( $text, 'planeación de' ) ) {
			return true;
		}

		return false;
	}

	/**
	 * @param array<int, mixed> $cells Celdas.
	 * @return array<int, string>
	 */
	private static function normalize_row( $cells ) {
		return array_map(
			static function ( $cell ) {
				return trim( (string) $cell );
			},
			array_values( $cells )
		);
	}

	/**
	 * @param array<int, string> $cells Celdas.
	 * @return bool
	 */
	private static function is_empty_row( $cells ) {
		foreach ( $cells as $cell ) {
			if ( '' !== trim( (string) $cell ) ) {
				return false;
			}
		}

		return true;
	}

	/**
	 * @param array<int, string>   $cells   Encabezados.
	 * @param array<string,string> $columns Mapa clave => etiqueta.
	 * @return array<string, int>
	 */
	private static function map_header_row( $cells, $columns ) {
		$map = array();

		foreach ( $cells as $index => $label ) {
			$norm = self::normalize_header( $label );
			if ( '' === $norm ) {
				continue;
			}

			foreach ( $columns as $key => $column_label ) {
				if ( $norm === self::normalize_header( $column_label ) ) {
					$map[ $key ] = (int) $index;
					break;
				}
			}
		}

		return $map;
	}

	/**
	 * @param array<string, int> $map Mapa.
	 * @param string             $key Clave.
	 * @return bool
	 */
	private static function map_has_key( $map, $key ) {
		return isset( $map[ $key ] );
	}

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

		return $row;
	}

	/**
	 * @param string $value Hora HH:MM.
	 * @return int|null
	 */
	private static function parse_time_value( $value ) {
		$value = trim( (string) $value );
		if ( '' === $value ) {
			return null;
		}

		if ( preg_match( '/^(\d{1,2}):(\d{2})$/', $value, $matches ) ) {
			return max( 0, ( (int) $matches[1] * 60 ) + (int) $matches[2] );
		}

		return null;
	}

	/**
	 * @param string $value Texto.
	 * @return string
	 */
	private static function normalize_header( $value ) {
		$value = remove_accents( wp_strip_all_tags( (string) $value ) );
		$value = strtolower( trim( $value ) );
		$value = preg_replace( '/[^a-z0-9]+/', ' ', $value );

		return trim( (string) $value );
	}

	/**
	 * @param string $content CSV.
	 * @return array<int, array<int, string>>|\WP_Error
	 */
	private static function parse_csv_matrix( $content ) {
		$content = preg_replace( '/^\xEF\xBB\xBF/', '', $content );
		$lines   = preg_split( '/\r\n|\r|\n/', (string) $content ) ?: array();
		$matrix  = array();

		foreach ( $lines as $line ) {
			if ( '' === trim( $line ) ) {
				continue;
			}
			$matrix[] = str_getcsv( $line, ';' );
		}

		if ( empty( $matrix ) ) {
			return new WP_Error( 'csv', __( 'El archivo CSV está vacío.', 'logistica' ) );
		}

		return $matrix;
	}

	/**
	 * @param string $content Binario xlsx.
	 * @return array<int, array<int, string>>|\WP_Error
	 */
	private static function parse_xlsx_matrix( $content ) {
		if ( ! class_exists( 'ZipArchive', false ) ) {
			return new WP_Error( 'zip', __( 'El servidor no puede leer archivos .xlsx. Guarda como .csv o habilita ZipArchive en PHP.', 'logistica' ) );
		}

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

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

		$shared         = $zip->getFromName( 'xl/sharedStrings.xml' );
		$shared_strings = self::parse_xlsx_shared_strings( false === $shared ? '' : $shared );
		$sheet_xml      = $zip->getFromName( 'xl/worksheets/sheet1.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 principal del Excel.', 'logistica' ) );
		}

		return self::parse_xlsx_sheet_matrix( $sheet_xml, $shared_strings );
	}

	/**
	 * @param string $content Binario xls.
	 * @return array<int, array<int, string>>|\WP_Error
	 */
	private static function parse_xls_matrix( $content ) {
		if ( ! class_exists( 'Shuchkin\\SimpleXLS', false ) ) {
			require_once LOGISTICA_PLUGIN_DIR . 'includes/lib/SimpleXLS.php';
		}

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

		$matrix = $xls->rows( 0 );
		if ( ! is_array( $matrix ) || empty( $matrix ) ) {
			return new WP_Error( 'xls', __( 'El archivo .xls no contiene filas.', 'logistica' ) );
		}

		return $matrix;
	}

	/**
	 * @param string   $sheet_xml      XML hoja.
	 * @param string[] $shared_strings Cadenas compartidas.
	 * @return array<int, array<int, string>>
	 */
	private static function parse_xlsx_sheet_matrix( $sheet_xml, $shared_strings ) {
		$sheet = simplexml_load_string( $sheet_xml );
		if ( false === $sheet ) {
			return array();
		}

		$sheet->registerXPathNamespace( 'm', 'http://schemas.openxmlformats.org/spreadsheetml/2006/main' );
		$row_nodes = $sheet->xpath( '//m:sheetData/m:row' );
		$matrix    = array();

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

		return $matrix;
	}

	/**
	 * @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 Cadenas.
	 * @return array<int, 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 $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 );
	}
}
