<?php
if ( ! defined( 'ABSPATH' ) ) exit;

/**
 * Parses XLSX and CSV files into PHP arrays.
 *
 * XLSX is read using a built-in pure-PHP parser (no Composer required).
 * Reads number formats, column widths, alignments, and header styles
 * directly from the XLSX so that Excel formatting is reproduced in WordPress.
 *
 * @since 1.0.3  Full style extraction (numFmt, col widths, header styles).
 */
class CDTV_Parser {

	/* -----------------------------------------------------------------------
	 * Public entry point
	 * -------------------------------------------------------------------- */

	/**
	 * Parse an uploaded file.
	 *
	 * @param string $file_path  Absolute path.
	 * @param string $file_type  'xlsx', 'xls', or 'csv'.
	 * @param bool   $has_header Whether the first row is a header row.
	 * @return array|WP_Error
	 *   Keys: headers, rows, column_count, row_count,
	 *         col_widths (px|null per col), col_haligns, col_formats, header_styles.
	 */
	public function parse( $file_path, $file_type, $has_header = true ) {
		if ( ! file_exists( $file_path ) ) {
			return new WP_Error( 'file_not_found', esc_html__( 'File not found.', 'codede-table-viewer' ) );
		}
		if ( 'xlsx' === $file_type ) {
			return $this->parse_xlsx_native( $file_path, $has_header );
		}
		if ( 'xls' === $file_type ) {
			return $this->parse_xls_legacy( $file_path, $has_header );
		}
		if ( 'csv' === $file_type ) {
			return $this->parse_csv( $file_path, $has_header );
		}
		return new WP_Error( 'unsupported_type', esc_html__( 'Unsupported file type.', 'codede-table-viewer' ) );
	}

	/* -----------------------------------------------------------------------
	 * XLSX – Built-in pure-PHP reader (ZipArchive + XMLReader)
	 * -------------------------------------------------------------------- */

	private function parse_xlsx_native( $file_path, $has_header ) {
		if ( ! class_exists( 'ZipArchive' ) ) {
			return new WP_Error(
				'zip_missing',
				esc_html__( 'PHP ZipArchive extension is required to read .xlsx files.', 'codede-table-viewer' )
			);
		}
		$zip = new ZipArchive();
		if ( true !== $zip->open( $file_path ) ) {
			return new WP_Error( 'zip_open_failed', esc_html__( 'Could not open .xlsx file. It may be corrupted.', 'codede-table-viewer' ) );
		}

		// 1. Shared strings
		$shared_strings = array();
		$ss_xml = $zip->getFromName( 'xl/sharedStrings.xml' );
		if ( false !== $ss_xml ) {
			$shared_strings = $this->parse_shared_strings( $ss_xml );
		}

		// 2. Full styles (numFmts, fonts, fills, xfs)
		$styles_data     = array();
		$date_format_ids = array();
		$styles_xml      = $zip->getFromName( 'xl/styles.xml' );
		if ( false !== $styles_xml ) {
			$styles_data     = $this->parse_styles_full( $styles_xml );
			$date_format_ids = $this->date_xf_ids_from_styles( $styles_data );
		}

		// 3. First worksheet
		$sheet_xml = $zip->getFromName( 'xl/worksheets/sheet1.xml' );
		if ( false === $sheet_xml ) {
			for ( $i = 1; $i <= 10; $i++ ) {
				$try = $zip->getFromName( 'xl/worksheets/sheet' . $i . '.xml' );
				if ( false !== $try ) { $sheet_xml = $try; break; }
			}
		}
		$zip->close();

		if ( false === $sheet_xml ) {
			return new WP_Error( 'no_sheet', esc_html__( 'Could not find worksheet data in the .xlsx file.', 'codede-table-viewer' ) );
		}

		// 4. Parse sheet (rows + style matrix + col widths)
		// date_format_ids passed so resolve_cell_value can detect date serials.
		$sheet = $this->parse_sheet_xml( $sheet_xml, $shared_strings, $date_format_ids );
		if ( is_wp_error( $sheet ) ) return $sheet;

		// 5. Build final result with formatting metadata
		return $this->build_result( $sheet, $styles_data, $has_header, $date_format_ids );
	}

	/* -----------------------------------------------------------------------
	 * Styles parsing
	 * -------------------------------------------------------------------- */

	/**
	 * Parse xl/styles.xml and return structured style tables.
	 *
	 * Returns:
	 *   num_fmts  array[ numFmtId => formatCode ]
	 *   fonts     array[ idx => ['bold','italic','color'] ]
	 *   fills     array[ idx => bg_color_hex_or_null ]
	 *   xfs       array[ idx => ['num_fmt_id','font_id','fill_id','halign','wrap'] ]
	 */
	private function parse_styles_full( $xml_string ) {
		// Built-in Excel number formats (ids 0-49)
		$builtin_fmts = array(
			0  => 'General',        1  => '0',              2  => '0.00',
			3  => '#,##0',          4  => '#,##0.00',        9  => '0%',
			10 => '0.00%',          11 => '0.00E+00',       12 => '# ?/?',
			13 => '# ??/??',        14 => 'm/d/yyyy',       15 => 'd-mmm-yy',
			16 => 'd-mmm',          17 => 'mmm-yy',         18 => 'h:mm AM/PM',
			19 => 'h:mm:ss AM/PM',  20 => 'h:mm',           21 => 'h:mm:ss',
			22 => 'm/d/yyyy h:mm',  37 => '#,##0_);(#,##0)',
			38 => '#,##0_);[Red](#,##0)', 39 => '#,##0.00_);(#,##0.00)',
			40 => '#,##0.00_);[Red](#,##0.00)', 45 => 'mm:ss',
			46 => '[h]:mm:ss',      47 => 'mmss.0',
			48 => '##0.0E+0',       49 => '@',
		);

		$result = array(
			'num_fmts' => $builtin_fmts,
			'fonts'    => array(),
			'fills'    => array(),
			'xfs'      => array(),
		);

		try {
			$xml = new SimpleXMLElement( $xml_string );

			// Custom numFmts
			if ( isset( $xml->numFmts ) ) {
				foreach ( $xml->numFmts->numFmt as $nf ) {
					$id   = (int) $nf['numFmtId'];
					$code = (string) $nf['formatCode'];
					$result['num_fmts'][ $id ] = $code;
				}
			}

			// Fonts
			if ( isset( $xml->fonts ) ) {
				foreach ( $xml->fonts->font as $font ) {
					$f = array( 'bold' => false, 'italic' => false, 'underline' => false, 'color' => null, 'size' => null, 'name' => null );
					if ( isset( $font->b ) )      $f['bold']      = true;
					if ( isset( $font->i ) )      $f['italic']    = true;
					if ( isset( $font->u ) )      $f['underline'] = true;
					if ( isset( $font->sz ) ) {
						$sz = (float) $font->sz['val'];
						if ( $sz > 0 ) { $f['size'] = $sz; }
					}
					if ( isset( $font->name ) ) {
						$fname = (string) $font->name['val'];
						if ( $fname ) $f['name'] = $fname;
					}
					if ( isset( $font->color ) ) {
						$rgb = (string) $font->color['rgb'];
						// ARGB hex: strip alpha channel (first 2 chars)
						if ( strlen( $rgb ) === 8 ) {
							$f['color'] = '#' . strtoupper( substr( $rgb, 2 ) );
						}
					}
					$result['fonts'][] = $f;
				}
			}

			// Fills (indices 0 & 1 are reserved none/gray in Excel spec)
			if ( isset( $xml->fills ) ) {
				foreach ( $xml->fills->fill as $fill ) {
					$bg = null;
					if ( isset( $fill->patternFill ) ) {
						$pf    = $fill->patternFill;
						$ptype = (string) $pf['patternType'];
						if ( 'solid' === $ptype && isset( $pf->fgColor ) ) {
							$rgb = (string) $pf->fgColor['rgb'];
							if ( strlen( $rgb ) === 8 ) {
								$hex = '#' . strtoupper( substr( $rgb, 2 ) );
								// Skip white/black defaults
								if ( ! in_array( $hex, array( '#FFFFFF', '#000000' ), true ) ) {
									$bg = $hex;
								}
							}
						}
					}
					$result['fills'][] = $bg;
				}
			}

			// cellXfs – one entry per style index
			if ( isset( $xml->cellXfs ) ) {
				foreach ( $xml->cellXfs->xf as $xf ) {
					$entry = array(
						'num_fmt_id' => (int) $xf['numFmtId'],
						'font_id'    => (int) $xf['fontId'],
						'fill_id'    => (int) $xf['fillId'],
						'halign'     => 'general',
						'wrap'       => false,
					);
					if ( isset( $xf->alignment ) ) {
						$halign = (string) $xf->alignment['horizontal'];
						if ( $halign ) $entry['halign'] = $halign;
						$entry['wrap'] = ( '1' === (string) $xf->alignment['wrapText'] );
					}
					$result['xfs'][] = $entry;
				}
			}
		} catch ( Exception $e ) {
			// Return defaults on any XML parse error
		}

		return $result;
	}

	/**
	 * Determine which cellXf indices represent date formats.
	 */
	private function date_xf_ids_from_styles( $styles_data ) {
		// Built-in date/time numFmtIds in Excel
		$date_num_ids = range( 14, 22 );

		// Also flag custom formats that look like dates.
		// Strip quoted literals ("...") and escape sequences (\x) first,
		// so that e.g. 0.0" mph" is NOT flagged as a date format just because
		// the letter 'm' appears inside the quoted unit suffix.
		foreach ( $styles_data['num_fmts'] as $id => $code ) {
			if ( $id >= 164 ) {
				$c          = strtolower( $code );
				$c_stripped = preg_replace( '/"[^"]*"/', '', $c );   // remove "..."
				$c_stripped = preg_replace( '/\\\\./', '', $c_stripped ); // remove \x
				if ( preg_match( '/[ymd]/', $c_stripped ) && strpos( $c_stripped, '#' ) === false ) {
					$date_num_ids[] = $id;
				}
			}
		}

		$date_ids = array();
		foreach ( $styles_data['xfs'] as $i => $xf ) {
			if ( in_array( $xf['num_fmt_id'], $date_num_ids, true ) ) {
				$date_ids[] = $i;
			}
		}
		return $date_ids;
	}

	/* -----------------------------------------------------------------------
	 * Sheet XML parsing  (XMLReader for memory efficiency)
	 * -------------------------------------------------------------------- */

	/**
	 * Parse worksheet XML.
	 *
	 * Returns:
	 *   rows            2-D flat array of raw string values
	 *   style_matrix    [ row_index => [ col_index => xf_index ] ]  (parallel to rows)
	 *   col_widths_char [ col_index => Excel char-width float ]
	 *   col_count       int
	 */
	private function parse_sheet_xml( $xml_string, $shared_strings, $date_format_ids ) {
		$rows               = array();
		$style_matrix       = array();
		$col_widths_char    = array();
		$col_default_styles = array(); // column-level style index from <col style="X">

		try {
			$reader = new XMLReader();
			if ( ! $reader->XML( $xml_string ) ) {
				return new WP_Error( 'xml_parse', esc_html__( 'Could not parse worksheet XML.', 'codede-table-viewer' ) );
			}

			$current_row        = null;
			$current_row_styles = null;
			$current_row_n      = 0;
			$current_cell       = null;
			$cell_type          = '';
			$cell_style_idx     = 0;
			$in_value           = false;
			$in_inline_str      = false;

			while ( $reader->read() ) {
				if ( XMLReader::ELEMENT === $reader->nodeType ) {
					switch ( $reader->localName ) {

						case 'col':
							// Capture column widths and column-level default styles from <cols> section
							$min        = max( 0, (int) $reader->getAttribute( 'min' ) - 1 );
							$max        = min( 500, (int) $reader->getAttribute( 'max' ) - 1 );
							$w          = (float) ( $reader->getAttribute( 'width' ) ?: 0 );
							$col_style  = (int) ( $reader->getAttribute( 'style' ) ?: 0 );
							if ( $max < 300 ) { // skip "all columns" default
								for ( $ci = $min; $ci <= $max; $ci++ ) {
									if ( $w > 0 ) {
										$col_widths_char[ $ci ] = $w;
									}
									// Store column-level style as fallback for cells without explicit style
									if ( $col_style > 0 ) {
										$col_default_styles[ $ci ] = $col_style;
									}
								}
							}
							break;

						case 'row':
							$current_row_n      = (int) $reader->getAttribute( 'r' );
							$current_row        = array();
							$current_row_styles = array();
							break;

						case 'c':
							$ref            = $reader->getAttribute( 'r' ) ?: '';
							$cell_type      = $reader->getAttribute( 't' ) ?: '';
							$cell_style_idx = (int) ( $reader->getAttribute( 's' ) ?: 0 );
							$current_cell   = $this->col_from_ref( $ref );
							$in_value       = false;
							$in_inline_str  = false;
							break;

						case 'v':
							$in_value = true;
							break;

						case 't':
							if ( 'inlineStr' === $cell_type ) $in_inline_str = true;
							break;
					}

				} elseif ( XMLReader::TEXT === $reader->nodeType || XMLReader::CDATA === $reader->nodeType ) {
					if ( ( $in_value || $in_inline_str ) && null !== $current_row && null !== $current_cell ) {
						$raw = $reader->value;
						$val = $this->resolve_cell_value( $raw, $cell_type, $cell_style_idx, $shared_strings, $date_format_ids );
						$current_row[ $current_cell ]        = $val;
						$current_row_styles[ $current_cell ] = $cell_style_idx;
					}

				} elseif ( XMLReader::END_ELEMENT === $reader->nodeType ) {
					switch ( $reader->localName ) {
						case 'v':
							$in_value = false;
							break;
						case 't':
							$in_inline_str = false;
							break;
						case 'row':
							if ( null !== $current_row ) {
								$rows[ $current_row_n - 1 ]         = $current_row;
								$style_matrix[ $current_row_n - 1 ] = $current_row_styles;
								$current_row        = null;
								$current_row_styles = null;
							}
							break;
					}
				}
			}
			$reader->close();

		} catch ( Exception $e ) {
			return new WP_Error( 'xml_error', $e->getMessage() );
		}

		if ( empty( $rows ) ) {
			return new WP_Error( 'empty_file', esc_html__( 'The file contains no data.', 'codede-table-viewer' ) );
		}

		// Normalise: fill column gaps, convert to flat arrays
		ksort( $rows );
		$col_count = 0;
		foreach ( $rows as $row ) {
			if ( ! empty( $row ) ) {
				$col_count = max( $col_count, max( array_keys( $row ) ) + 1 );
			}
		}

		$flat_rows   = array();
		$flat_styles = array();
		foreach ( $rows as $rn => $row ) {
			$flat = array();
			$fst  = array();
			for ( $c = 0; $c < $col_count; $c++ ) {
				$flat[] = isset( $row[ $c ] ) ? $row[ $c ] : '';
				$fst[]  = isset( $style_matrix[ $rn ][ $c ] ) ? $style_matrix[ $rn ][ $c ] : 0;
			}
			if ( implode( '', $flat ) !== '' ) {
				$flat_rows[]   = $flat;
				$flat_styles[] = $fst;
			}
		}

		return array(
			'rows'               => $flat_rows,
			'style_matrix'       => $flat_styles,        // parallel to rows
			'col_widths_char'    => $col_widths_char,
			'col_default_styles' => $col_default_styles, // fallback style per column
			'col_count'          => $col_count,
		);
	}

	/* -----------------------------------------------------------------------
	 * Build final result with formatting metadata
	 * -------------------------------------------------------------------- */

	/**
	 * Turn raw parse output into the structured array stored in the CPT.
	 *
	 * Applies Excel number formats to numeric cells so that e.g. 15 decimal
	 * places are reduced to whatever the spreadsheet author specified (e.g. 2).
	 */
	private function build_result( $sheet, $styles_data, $has_header, $date_format_ids = array() ) {
		$rows         = $sheet['rows'];
		$style_matrix = $sheet['style_matrix'];
		$col_count    = $sheet['col_count'];

		if ( empty( $rows ) ) {
			return new WP_Error( 'empty_file', esc_html__( 'The file contains no data.', 'codede-table-viewer' ) );
		}

		// --- Separate header row ------------------------------------------------
		$headers          = array();
		$header_style_row = array();
		if ( $has_header ) {
			$raw_headers      = array_shift( $rows );
			$header_style_row = array_shift( $style_matrix );
			foreach ( $raw_headers as $h ) {
				$headers[] = sanitize_text_field( (string) $h );
			}
		} else {
			$header_style_row = array();
			for ( $i = 0; $i < $col_count; $i++ ) {
				$headers[] = 'Column ' . ( $i + 1 );
			}
		}

		// --- Column widths (Excel char-width → pixels) ---------------------------
		// px ≈ round(char_width × 7) + 5  (default Calibri 11 pt)
		$col_widths = array();
		for ( $c = 0; $c < $col_count; $c++ ) {
			$char_w           = $sheet['col_widths_char'][ $c ] ?? 0;
			$col_widths[ $c ] = $char_w > 0 ? max( 40, (int) round( $char_w * 7 ) + 5 ) : null;
		}

		// --- Per-column format & alignment (from first data row + col-level fallback) ---
		$col_formats        = array_fill( 0, $col_count, '' );
		$col_haligns        = array_fill( 0, $col_count, 'general' );
		$col_default_styles = $sheet['col_default_styles'] ?? array();

		if ( ! empty( $style_matrix ) && ! empty( $styles_data['xfs'] ) ) {
			foreach ( $style_matrix[0] as $c => $xf_idx ) {
				// If cell has no explicit style (xf_idx=0 = General), fall back to
				// the column-level style defined in <col style="X">.
				if ( $xf_idx === 0 && isset( $col_default_styles[ $c ] ) ) {
					$xf_idx = $col_default_styles[ $c ];
				}
				if ( isset( $styles_data['xfs'][ $xf_idx ] ) ) {
					$xf     = $styles_data['xfs'][ $xf_idx ];
					$fmt_id = $xf['num_fmt_id'];
					$fmt    = $styles_data['num_fmts'][ $fmt_id ] ?? 'General';
					$col_formats[ $c ] = ( 'General' === $fmt ) ? '' : $fmt;
					$col_haligns[ $c ] = $xf['halign'];
				}
			}
		}

		// --- Apply column-level style also for columns not covered by style_matrix[0] ---
		// (handles case where first data row has no cells in a column, OR alignment-only column style)
		foreach ( $col_default_styles as $c => $xf_idx ) {
			if ( $c >= $col_count || ! isset( $styles_data['xfs'][ $xf_idx ] ) ) continue;
			$xf = $styles_data['xfs'][ $xf_idx ];
			// Format fallback (only if not already set)
			if ( '' === $col_formats[ $c ] ) {
				$fmt_id = $xf['num_fmt_id'];
				$fmt    = $styles_data['num_fmts'][ $fmt_id ] ?? 'General';
				if ( 'General' !== $fmt ) {
					$col_formats[ $c ] = $fmt;
				}
			}
			// Alignment fallback — independent of number format
			if ( 'general' === $col_haligns[ $c ] && 'general' !== $xf['halign'] ) {
				$col_haligns[ $c ] = $xf['halign'];
			}
		}

		// --- Header cell styles --------------------------------------------------
		$header_styles = array();
		if ( $has_header && ! empty( $header_style_row ) && ! empty( $styles_data['xfs'] ) ) {
			foreach ( $header_style_row as $c => $xf_idx ) {
				$header_styles[ $c ] = $this->resolve_cell_style( $xf_idx, $styles_data );
			}
		}

		// --- First data row style (representative cell: col 0) -------------------
		// Used to seed the design config (row_bg, row_color, table_font_size).
		$data_row_style = array( 'bg' => null, 'color' => null, 'size' => null, 'bold' => false );
		if ( ! empty( $style_matrix[0] ) && ! empty( $styles_data['xfs'] ) ) {
			$first_xf = $style_matrix[0][0] ?? 0;
			if ( $first_xf === 0 && isset( $col_default_styles[0] ) ) {
				$first_xf = $col_default_styles[0];
			}
			if ( $first_xf > 0 ) {
				$resolved       = $this->resolve_cell_style( $first_xf, $styles_data );
				$data_row_style = array(
					'bg'    => $resolved['bg'],
					'color' => $resolved['color'],
					'size'  => $resolved['size'],
					'bold'  => $resolved['bold'],
				);
			}
		}

		// --- Apply number formatting to all data rows ----------------------------
		// Uses per-cell xf_idx from $style_matrix for accurate formatting when
		// different rows in the same column have different number formats
		// (e.g. some rows show integers, others show "69.6 mph").
		// Also builds cell_haligns: sparse [row][col] => halign for per-cell alignment.
		$data             = array();
		$cell_haligns_map = array(); // sparse: only cells with non-general alignment
		$cell_styles_map  = array(); // sparse: only cells with non-default visual style
		foreach ( $rows as $row_idx => $row ) {
			$clean      = array();
			$row_styles = $style_matrix[ $row_idx ] ?? array();
			foreach ( $row as $c => $val ) {
				// Resolve xf index for this cell (with column-level fallback)
				$cell_xf_idx  = $row_styles[ $c ] ?? 0;
				if ( $cell_xf_idx === 0 && isset( $col_default_styles[ $c ] ) ) {
					$cell_xf_idx = $col_default_styles[ $c ];
				}
				// Remember whether the raw value is numeric BEFORE formatting
				$val_is_numeric = ( $val !== '' && is_numeric( $val ) );

				// Always format numeric values
				if ( $val_is_numeric ) {
					$cell_fmt = $col_formats[ $c ] ?? '';
					if ( $cell_xf_idx > 0 && isset( $styles_data['xfs'][ $cell_xf_idx ] ) ) {
						$xf       = $styles_data['xfs'][ $cell_xf_idx ];
						$fmt_id   = $xf['num_fmt_id'];
						$fmt_code = $styles_data['num_fmts'][ $fmt_id ] ?? 'General';
						$cell_fmt = ( 'General' === $fmt_code ) ? '' : $fmt_code;
					}
					$val = $this->format_value_by_numfmt( $val, $cell_fmt );
				}

				// Is this cell a date? (serial number with date numFmt)
				$is_date_cell = $val_is_numeric && in_array( $cell_xf_idx, $date_format_ids, true );

				// Per-cell alignment.
				// Excel right-aligns numeric cells by default when halign='general'.
				// Date cells are an exception – they display as text so Excel keeps them
				// left-aligned when no explicit alignment is set.
				if ( $cell_xf_idx > 0 && isset( $styles_data['xfs'][ $cell_xf_idx ] ) ) {
					$halign = $styles_data['xfs'][ $cell_xf_idx ]['halign'];
					if ( 'general' !== $halign ) {
						$cell_haligns_map[ $row_idx ][ $c ] = $halign;
					} elseif ( $val_is_numeric && ! $is_date_cell ) {
						$cell_haligns_map[ $row_idx ][ $c ] = 'right';
					}
				} elseif ( $val_is_numeric && ! $is_date_cell ) {
					$cell_haligns_map[ $row_idx ][ $c ] = 'right';
				}

				// Per-cell visual style (background, font color, bold, italic, font name).
				// Only stored when at least one property differs from defaults to keep
				// the serialized data compact for large tables.
				if ( $cell_xf_idx > 0 ) {
					$cs = $this->resolve_cell_style( $cell_xf_idx, $styles_data );
					if ( $cs['bg'] !== null || $cs['color'] !== null || $cs['bold'] || $cs['italic'] || $cs['underline'] || $cs['font_name'] !== null ) {
						$cell_styles_map[ $row_idx ][ $c ] = array(
							'bg'        => $cs['bg'],
							'color'     => $cs['color'],
							'bold'      => $cs['bold'],
							'italic'    => $cs['italic'],
							'underline' => $cs['underline'],
							'font_name' => $cs['font_name'],
						);
					}
				}

				$clean[] = wp_kses( (string) $val, array() );
			}
			while ( count( $clean ) < $col_count ) $clean[] = '';
			$data[] = array_slice( $clean, 0, $col_count );
		}

		return array(
			'headers'       => $headers,
			'rows'          => $data,
			'column_count'  => $col_count,
			'row_count'     => count( $data ),
			// Formatting metadata
			'col_widths'    => $col_widths,        // [ col => px|null ]
			'col_formats'   => $col_formats,       // [ col => numFmt string ]
			'col_haligns'   => $col_haligns,       // [ col => 'left'|'right'|'center'|'general' ]
			'cell_haligns'  => $cell_haligns_map,  // sparse [ row ][ col ] => halign
			'cell_styles'   => $cell_styles_map,   // sparse [ row ][ col ] => { bg, color, bold, italic, underline, font_name }
			'header_styles' => $header_styles,     // [ col => { bold, italic, halign, bg, color, size, font_name } ]
			'data_row_style'=> $data_row_style,    // first data row representative style
		);
	}

	/* -----------------------------------------------------------------------
	 * Number-format application
	 * -------------------------------------------------------------------- */

	/**
	 * Apply an Excel number-format string to a raw numeric value.
	 *
	 * Always returns a string with "." as decimal separator.
	 * The front-end JS converts "." → "," when the site setting requires it.
	 */
	private function format_value_by_numfmt( $value, $fmt_string ) {
		$fmt = trim( $fmt_string );

		// Text format – return as-is
		if ( '@' === $fmt ) {
			return $value;
		}

		// General format: mimic Excel's display behaviour
		// - Integers: no decimal places
		// - Decimals: up to 9 significant digits, trailing zeros removed
		// NOTE: use '' === $fmt, NOT empty($fmt) — empty('0') is TRUE in PHP!
		if ( '' === $fmt || 'General' === $fmt ) {
			$num = (float) $value;
			// If value is a whole number, return as integer string
			if ( $num == floor( $num ) && abs( $num ) < 1.0e15 ) {
				return number_format( $num, 0, '.', '' );
			}
			// Limit to 9 significant digits, remove trailing zeros after decimal
			// 6 significant digits matches Excel's default General display width
			$formatted = sprintf( '%.6G', $num );
			// %G may use scientific notation for very large/small numbers; keep it
			// For normal ranges, strip trailing zeros: "66.8396564" not "66.839656400"
			if ( strpos( $formatted, '.' ) !== false && strpos( $formatted, 'E' ) === false ) {
				$formatted = rtrim( rtrim( $formatted, '0' ), '.' );
			}
			return $formatted;
		}

		// Date/time format: contains d/m/y but no numeric placeholder 0/#
		$lc = strtolower( $fmt );
		if ( preg_match( '/[ymd]/', $lc ) && ! preg_match( '/[0#]/', $lc ) ) {
			if ( is_numeric( $value ) ) {
				// Convert Excel serial date to a formatted string using the format code
				$serial = (float) $value;
				// Excel incorrectly treats 1900 as a leap year; adjust serial >= 60
				if ( $serial >= 60 ) $serial -= 1;
				$unix    = (int) round( ( $serial - 25569 ) * 86400 );
				$php_fmt = $this->excel_date_fmt_to_php( $fmt );
				return gmdate( $php_fmt, $unix );
			}
			return $value; // already a string (e.g. from CSV)
		}

		// Take first section of conditional format  e.g. "#,##0.00;-#,##0.00;0"
		if ( strpos( $fmt, ';' ) !== false ) {
			$parts = explode( ';', $fmt );
			$fmt   = preg_replace( '/^\[.*?\]/', '', $parts[0] ); // remove [condition]
		}

		// --- Extract quoted literal prefix/suffix (e.g. " mph", "€ ") ----------
		// Scan for the first and last unquoted 0/#, then collect quoted text
		// before them (prefix) and after them (suffix).
		$lit_prefix = '';
		$lit_suffix = '';
		{
			$in_q    = false;
			$first_n = PHP_INT_MAX;
			$last_n  = -1;
			$flen    = strlen( $fmt );
			for ( $fi = 0; $fi < $flen; $fi++ ) {
				if ( '"' === $fmt[ $fi ] ) { $in_q = ! $in_q; continue; }
				if ( $in_q ) continue;
				if ( '0' === $fmt[ $fi ] || '#' === $fmt[ $fi ] ) {
					if ( $fi < $first_n ) $first_n = $fi;
					$last_n = $fi;
				}
			}
			if ( $first_n !== PHP_INT_MAX ) {
				preg_match_all( '/"([^"]*)"/', substr( $fmt, 0, $first_n ), $pm );
				$lit_prefix = implode( '', $pm[1] );
				preg_match_all( '/"([^"]*)"/', substr( $fmt, $last_n + 1 ), $sm );
				$lit_suffix = implode( '', $sm[1] );
			}
		}

		// Strip visual-only artifacts so we can analyse the structure
		$clean = preg_replace( '/\[[^\]]*\]/', '', $fmt );   // [Red], [$€-407], …
		$clean = preg_replace( '/"[^"]*"/', '', $clean );    // "literal text"
		$clean = preg_replace( '/\\\\./', '', $clean );       // \x escape
		$clean = preg_replace( '/[_*]./', '', $clean );       // _x padding / *x fill

		$num = (float) $value;

		// Percentage
		if ( strpos( $clean, '%' ) !== false ) {
			$num     *= 100;
			$decimals = 0;
			if ( preg_match( '/\.([0#]+)/', $clean, $m ) ) $decimals = strlen( $m[1] );
			return $lit_prefix . number_format( $num, $decimals, '.', '' ) . '%' . $lit_suffix;
		}

		// Scientific notation
		if ( preg_match( '/[eE][+\-]/', $clean ) ) {
			$dec = 2;
			if ( preg_match( '/\.([0#]+)[eE]/', $clean, $m ) ) $dec = strlen( $m[1] );
			return $lit_prefix . sprintf( '%.' . $dec . 'E', $num ) . $lit_suffix;
		}

		// Count decimal places from format string
		$decimals = 0;
		if ( preg_match( '/\.([0#]+)/', $clean, $m ) ) {
			$decimals = strlen( $m[1] );
		}

		// Return formatted number with any literal prefix/suffix preserved
		return $lit_prefix . number_format( $num, $decimals, '.', '' ) . $lit_suffix;
	}

	/* -----------------------------------------------------------------------
	 * Style-resolution helpers
	 * -------------------------------------------------------------------- */

	/**
	 * Resolve a cellXf index to a display-style array.
	 *
	 * @return array { bold, italic, halign, bg, color, size }
	 */
	private function resolve_cell_style( $xf_idx, $styles_data ) {
		$style = array(
			'bold'      => false,
			'italic'    => false,
			'underline' => false,
			'halign'    => 'general',
			'bg'        => null,
			'color'     => null,
			'size'      => null,
			'font_name' => null,
		);

		$xfs = $styles_data['xfs'] ?? array();
		if ( ! isset( $xfs[ $xf_idx ] ) ) return $style;

		$xf            = $xfs[ $xf_idx ];
		$style['halign'] = $xf['halign'];

		$fonts = $styles_data['fonts'] ?? array();
		if ( isset( $fonts[ $xf['font_id'] ] ) ) {
			$font                 = $fonts[ $xf['font_id'] ];
			$style['bold']        = $font['bold'];
			$style['italic']      = $font['italic'];
			$style['underline']   = $font['underline'] ?? false;
			$style['color']       = $font['color'];
			$style['size']        = $font['size'] ?? null;
			$style['font_name']   = $font['name'] ?? null;
		}

		$fills = $styles_data['fills'] ?? array();
		if ( isset( $fills[ $xf['fill_id'] ] ) ) {
			$style['bg'] = $fills[ $xf['fill_id'] ];
		}

		return $style;
	}

	/* -----------------------------------------------------------------------
	 * Low-level helpers
	 * -------------------------------------------------------------------- */

	/** Parse sharedStrings.xml → flat indexed string array. */
	private function parse_shared_strings( $xml_string ) {
		$strings = array();
		try {
			$xml = new SimpleXMLElement( $xml_string );
			foreach ( $xml->si as $si ) {
				if ( isset( $si->t ) ) {
					$strings[] = (string) $si->t;
					continue;
				}
				$text = '';
				foreach ( $si->r as $r ) {
					if ( isset( $r->t ) ) $text .= (string) $r->t;
				}
				$strings[] = $text;
			}
		} catch ( Exception $e ) {}
		return $strings;
	}

	/** Convert cell reference (e.g. "B3") to zero-based column index. */
	private function col_from_ref( $ref ) {
		preg_match( '/^([A-Z]+)/i', $ref, $m );
		if ( empty( $m[1] ) ) return 0;
		$col   = strtoupper( $m[1] );
		$index = 0;
		$len   = strlen( $col );
		for ( $i = 0; $i < $len; $i++ ) {
			$index = $index * 26 + ( ord( $col[ $i ] ) - ord( 'A' ) + 1 );
		}
		return $index - 1;
	}

	/** Resolve a raw cell value (shared string / boolean / date / numeric). */
	private function resolve_cell_value( $raw, $type, $style_id, $shared_strings, $date_format_ids ) {
		switch ( $type ) {
			case 's':
				$idx = (int) $raw;
				return isset( $shared_strings[ $idx ] ) ? $shared_strings[ $idx ] : '';
			case 'b':
				return $raw ? 'TRUE' : 'FALSE';
			case 'inlineStr':
			case 'str':
			case 'e':
				return $raw;
			default:
				// Return the raw serial for date cells – actual formatting (including
				// applying the Excel format code like "DD.MM.YYYY") is done later in
				// build_result() where we have access to the numFmt string.
				return $raw;
		}
	}

	/** Excel serial date number → YYYY-MM-DD string (fallback only). */
	private function excel_date_to_string( $serial ) {
		if ( $serial < 1 ) return (string) $serial;
		$unix = ( $serial - 25569 ) * 86400;
		return gmdate( 'Y-m-d', (int) $unix );
	}

	/**
	 * Convert an Excel date format string to a PHP date() format string.
	 *
	 * Handles common tokens: yyyy/yy, mmmm/mmm/mm/m, dddd/ddd/dd/d, hh/h, ss/s, am/pm.
	 * Quoted literals ("text") are passed through as-is.
	 */
	private function excel_date_fmt_to_php( $excel_fmt ) {
		// Strip locale/colour codes like [$-407], [Red]
		$f = preg_replace( '/\[[^\]]*\]/', '', $excel_fmt );

		// Token map: Excel token (lowercase) → PHP date() char
		$tokens = array(
			'yyyy' => 'Y',  'yyy'  => 'Y',  'yy'   => 'y',
			'mmmm' => 'F',  'mmm'  => 'M',  'mm'   => 'm',  'm' => 'n',
			'dddd' => 'l',  'ddd'  => 'D',  'dd'   => 'd',  'd' => 'j',
			'hh'   => 'H',  'h'    => 'G',
			'ss'   => 's',  's'    => 's',
			'am/pm'=> 'A',  'a/p'  => 'a',
		);
		// Sort by token length descending so longer tokens match first
		$sorted_tokens = array_keys( $tokens );
		usort( $sorted_tokens, static function ( $a, $b ) { return strlen( $b ) - strlen( $a ); } );

		$result = '';
		$i      = 0;
		$len    = strlen( $f );

		while ( $i < $len ) {
			// Pass quoted literals through unchanged
			if ( '"' === $f[ $i ] ) {
				$j = strpos( $f, '"', $i + 1 );
				if ( false !== $j ) {
					$result .= substr( $f, $i + 1, $j - $i - 1 );
					$i       = $j + 1;
					continue;
				}
			}

			// Try to match a known token
			$matched = false;
			foreach ( $sorted_tokens as $tk ) {
				$tlen = strlen( $tk );
				if ( $i + $tlen <= $len && 0 === strcasecmp( substr( $f, $i, $tlen ), $tk ) ) {
					$result .= $tokens[ $tk ];
					$i      += $tlen;
					$matched = true;
					break;
				}
			}

			if ( ! $matched ) {
				$ch = $f[ $i ];
				// Escape characters that PHP date() interprets as format codes
				if ( preg_match( '/[A-Za-z]/', $ch ) ) {
					$result .= '\\' . $ch;
				} else {
					$result .= $ch;
				}
				$i++;
			}
		}

		return $result ?: 'Y-m-d'; // fallback
	}

	/* -----------------------------------------------------------------------
	 * XLS (legacy binary) – via PhpSpreadsheet if available
	 * -------------------------------------------------------------------- */

	private function parse_xls_legacy( $file_path, $has_header ) {
		$vendor_autoload = WPEXIE_PLUGIN_DIR . 'vendor/autoload.php';
		if ( ! file_exists( $vendor_autoload ) ) {
			return new WP_Error(
				'xls_not_supported',
				esc_html__( 'Legacy .xls files require the PhpSpreadsheet library. Please save your file as .xlsx or .csv and re-upload.', 'codede-table-viewer' )
			);
		}
		require_once $vendor_autoload;
		try {
			$reader      = \PhpOffice\PhpSpreadsheet\IOFactory::createReaderForFile( $file_path );
			$reader->setReadDataOnly( true );
			$spreadsheet = $reader->load( $file_path );
			$rows        = $spreadsheet->getActiveSheet()->toArray( null, true, true, false );
		} catch ( \Exception $e ) {
			return new WP_Error( 'parse_error', $e->getMessage() );
		}
		return $this->process_rows_simple( $rows, $has_header );
	}

	/* -----------------------------------------------------------------------
	 * CSV – native PHP, no library needed
	 * -------------------------------------------------------------------- */

	private function parse_csv( $file_path, $has_header ) {
		// phpcs:ignore WordPress.WP.AlternativeFunctions.file_get_contents_file_get_contents -- local file read, WP_Filesystem not suitable for binary-safe encoding detection
		$content = file_get_contents( $file_path ); // phpcs:ignore
		$encoding = mb_detect_encoding( $content, array( 'UTF-8', 'ISO-8859-1', 'Windows-1252' ), true );
		if ( $encoding && 'UTF-8' !== $encoding ) {
			$content = mb_convert_encoding( $content, 'UTF-8', $encoding );
		}
		$content   = ltrim( $content, "\xEF\xBB\xBF" );
		$delimiter = $this->detect_delimiter( $content );
		$rows      = array();
		foreach ( explode( "\n", $content ) as $line ) {
			$line = rtrim( $line, "\r" );
			if ( '' === $line ) continue;
			$rows[] = str_getcsv( $line, $delimiter );
		}
		return $this->process_rows_simple( $rows, $has_header );
	}

	private function detect_delimiter( $content ) {
		$delimiters = array( ',', ';', "\t", '|' );
		$counts     = array();
		$first_line = strtok( $content, "\n" );
		foreach ( $delimiters as $d ) {
			$counts[ $d ] = substr_count( $first_line, $d );
		}
		arsort( $counts );
		return array_key_first( $counts );
	}

	/**
	 * Simple row processor for CSV and XLS (no style information available).
	 */
	private function process_rows_simple( $rows, $has_header ) {
		if ( empty( $rows ) ) {
			return new WP_Error( 'empty_file', esc_html__( 'The file contains no data.', 'codede-table-viewer' ) );
		}
		$headers = array();
		if ( $has_header ) {
			$raw = array_shift( $rows );
			foreach ( $raw as $h ) $headers[] = sanitize_text_field( (string) $h );
		} else {
			$col_count = count( $rows[0] ?? array() );
			for ( $i = 0; $i < $col_count; $i++ ) $headers[] = 'Column ' . ( $i + 1 );
		}
		$data = array();
		foreach ( $rows as $row ) {
			$clean = array();
			foreach ( $row as $cell ) $clean[] = wp_kses( (string) $cell, array() );
			while ( count( $clean ) < count( $headers ) ) $clean[] = '';
			$data[] = array_slice( $clean, 0, count( $headers ) );
		}
		return array(
			'headers'       => $headers,
			'rows'          => $data,
			'column_count'  => count( $headers ),
			'row_count'     => count( $data ),
			'col_widths'    => array(),
			'col_formats'   => array(),
			'col_haligns'   => array(),
			'header_styles' => array(),
		);
	}
}
