<?php
/**
 * File Path: includes/import/class-mbs-xlsx-reader.php
 *
 * Minimal XLSX Reader
 *
 * Converts a .xlsx file into a 2-D array of string values (all values cast
 * to strings) using only PHP's built-in ZipArchive and SimpleXML extensions.
 * No third-party library or Composer dependency required.
 *
 * Supports:
 *   - Shared strings table (xl/sharedStrings.xml)
 *   - Inline strings (<is><t>…</t></is>)
 *   - Rich-text runs (<r><t>…</t></r> inside <si>)
 *   - Sparse rows (cells that skip columns are filled with empty strings)
 *   - All standard OOXML cell types: s, str, inlineStr, b, and plain numbers
 *
 * Does NOT support:
 *   - Multiple worksheets (always reads xl/worksheets/sheet1.xml or the
 *     first sheet listed in the workbook relationships)
 *   - Dates formatted as numbers (returned as raw serial numbers)
 *   - Formulas (returns the cached result stored in <v>)
 *
 * @package MyBookShowroom
 * @since   1.0.0
 */

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

class MBS_XLSX_Reader { // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedClassFound -- mbs_/MBS_ prefix is correct; scanner not configured with plugin prefix

	/** OOXML spreadsheet namespace URI. */
	const NS = 'http://schemas.openxmlformats.org/spreadsheetml/2006/main';

	// ── Public API ────────────────────────────────────────────────────────────

	/**
	 * Convert an XLSX file to a temporary CSV file and return the CSV path.
	 *
	 * Writes a UTF-8 BOM so Excel on Windows opens the file without garbling
	 * accented characters (mirrors the behavior of mbs_download_csv_template).
	 *
	 * @param string $xlsx_path  Absolute path to the source XLSX file.
	 * @param string $temp_dir   Directory where the temp CSV will be written.
	 * @return string|WP_Error   Absolute path to the CSV, or WP_Error on failure.
	 */
	public static function to_csv_path( string $xlsx_path, string $temp_dir ): string|WP_Error {
		$rows = self::get_rows( $xlsx_path );
		if ( is_wp_error( $rows ) ) {
			return $rows;
		}

		if ( empty( $rows ) ) {
			return new WP_Error( 'empty_xlsx', 'The XLSX file appears to be empty.' );
		}

		// ST-022 fix: user ID prefix added so MBS_CSV_Temp_Manager::cleanup_stale_temp_files()
		// only cleans up files belonging to the current admin (prevents cross-admin race).
		$csv_path = trailingslashit( $temp_dir ) . 'xlsx_' . get_current_user_id() . '_' . wp_generate_password( 12, false ) . '.csv';
		// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fopen
		$fh = fopen( $csv_path, 'wb' );
		if ( ! $fh ) {
			return new WP_Error( 'file_write', 'Could not create temporary CSV file from XLSX.' );
		}

		// UTF-8 BOM — prevents garbled accented characters in Excel on Windows.
		fwrite( $fh, "\xEF\xBB\xBF" ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fwrite -- WP_Filesystem migration deferred to PC-FILESYS sprint; PHP native file ops are safe here (temp files in get_temp_dir(), never inside wp-content)

		foreach ( $rows as $row ) {
			fputcsv( $fh, $row );
		}

		fclose( $fh ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose -- WP_Filesystem migration deferred to PC-FILESYS sprint; PHP native file ops are safe here (temp files in get_temp_dir(), never inside wp-content)

		return $csv_path;
	}

	/**
	 * Parse an XLSX file and return its first worksheet as a 2-D array.
	 *
	 * Each element of the outer array is a row; each element of a row is a
	 * string value. All rows have the same length (padded with empty strings).
	 *
	 * @param string $xlsx_path Absolute path to the XLSX file.
	 * @return array|WP_Error   2-D string array, or WP_Error on failure.
	 */
	public static function get_rows( string $xlsx_path ): array|WP_Error {
		if ( ! class_exists( 'ZipArchive' ) ) {
			return new WP_Error( 'no_zip', 'PHP ZipArchive extension is required to read XLSX files.' );
		}

		// V7-B-002: Reject XLSX files exceeding 50 MB to prevent memory exhaustion.
		$_max_bytes = apply_filters( 'mbs_import_max_xlsx_bytes', 50 * 1024 * 1024 ); // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedHooknameFound -- mbs_/MBS_ prefix is correct; scanner not configured with plugin prefix
		if ( ! file_exists( $xlsx_path ) ) {
			return new WP_Error( 'xlsx_not_found', 'XLSX file does not exist.' );
		}
		if ( filesize( $xlsx_path ) > $_max_bytes ) {
			return new WP_Error( 'xlsx_too_large', 'XLSX file exceeds the maximum allowed size of 50 MB.' );
		}

		$zip = new ZipArchive();
		$opened = $zip->open( $xlsx_path );
		if ( true !== $opened ) {
			return new WP_Error( 'zip_open', 'Could not open XLSX file (invalid or corrupted). Error code: ' . $opened );
		}

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

		// ── 2. Locate the first worksheet via relationships ───────────────────
		$sheet_entry = self::find_first_sheet_entry( $zip );

		$sheet_xml = $zip->getFromName( $sheet_entry );

		$zip->close();

		if ( false === $sheet_xml || '' === $sheet_xml ) {
			return new WP_Error( 'no_sheet', 'Could not find worksheet data in XLSX file (tried: ' . esc_html( $sheet_entry ) . ').' );
		}

		// ── 3. Parse the worksheet ────────────────────────────────────────────
		return self::parse_sheet( $sheet_xml, $shared_strings );
	}

	// ── Multi-sheet public API (1.3.0) ─────────────────────────────────────

	/**
	 * Parse all worksheets in an XLSX file, keyed by sheet name.
	 *
	 * @param string $xlsx_path Absolute path to the XLSX file.
	 * @return array|WP_Error  ['Sheet Name' => [[row],...]] in workbook order.
	 * @since 1.3.0
	 */
	public static function get_sheets( string $xlsx_path ): array|WP_Error {
		if ( ! class_exists( 'ZipArchive' ) ) {
			return new WP_Error( 'no_zip', 'PHP ZipArchive extension is required to read XLSX files.' );
		}
		// V7-B-002: Reject XLSX files exceeding 50 MB to prevent memory exhaustion.
		$_max_bytes = apply_filters( 'mbs_import_max_xlsx_bytes', 50 * 1024 * 1024 ); // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedHooknameFound -- mbs_/MBS_ prefix is correct; scanner not configured with plugin prefix
		if ( ! file_exists( $xlsx_path ) ) {
			return new WP_Error( 'xlsx_not_found', 'XLSX file does not exist.' );
		}
		if ( filesize( $xlsx_path ) > $_max_bytes ) {
			return new WP_Error( 'xlsx_too_large', 'XLSX file exceeds the maximum allowed size of 50 MB.' );
		}

		$zip    = new ZipArchive();
		$opened = $zip->open( $xlsx_path );
		if ( true !== $opened ) {
			return new WP_Error( 'zip_open', 'Could not open XLSX file. Error code: ' . $opened );
		}
		$shared_strings = array();
		$ss_xml         = $zip->getFromName( 'xl/sharedStrings.xml' );
		if ( false !== $ss_xml ) {
			$shared_strings = self::parse_shared_strings( $ss_xml );
		}
		$wb_xml      = $zip->getFromName( 'xl/workbook.xml' );
		$sheet_names = false !== $wb_xml ? self::parse_workbook_sheet_names( $wb_xml ) : array();
		$rels_xml    = $zip->getFromName( 'xl/_rels/workbook.xml.rels' );
		$sheet_paths = false !== $rels_xml ? self::parse_workbook_rels( $rels_xml ) : array();
		$sheets = array();
		foreach ( $sheet_names as $rid => $name ) {
			$entry = $sheet_paths[ $rid ] ?? null;
			if ( null === $entry ) { continue; }
			$sheet_xml = $zip->getFromName( $entry );
			if ( false === $sheet_xml || '' === $sheet_xml ) { continue; }
			$rows = self::parse_sheet( $sheet_xml, $shared_strings );
			if ( is_wp_error( $rows ) ) { $zip->close(); return $rows; }
			$sheets[ $name ] = $rows;
		}
		$zip->close();
		return $sheets;
	}

	/**
	 * Return true if the XLSX file contains more than one worksheet.
	 *
	 * @param string $xlsx_path Absolute path to the XLSX file.
	 * @return bool
	 * @since 1.3.0
	 */
	public static function is_multi_sheet( string $xlsx_path ): bool {
		if ( ! class_exists( 'ZipArchive' ) ) { return false; }
		$zip = new ZipArchive();
		if ( true !== $zip->open( $xlsx_path ) ) { return false; }
		$rels_xml = $zip->getFromName( 'xl/_rels/workbook.xml.rels' );
		$zip->close();
		if ( false === $rels_xml ) { return false; }
		return substr_count( $rels_xml, '/worksheet"' ) > 1;
	}

	/**
	 * Parse xl/workbook.xml to extract [rId => sheet_name] in sheet order.
	 *
	 * @param string $xml Raw workbook.xml content.
	 * @return array<string,string>
	 */
	private static function parse_workbook_sheet_names( string $xml ): array {
		$map  = array();
		$prev = libxml_use_internal_errors( true );
		$wb   = simplexml_load_string( $xml, 'SimpleXMLElement', LIBXML_NONET | LIBXML_NOENT );
		libxml_use_internal_errors( $prev );
		if ( ! $wb ) { return $map; }
		$r_ns = 'http://schemas.openxmlformats.org/officeDocument/2006/relationships';
		foreach ( $wb->sheets->sheet ?? array() as $sheet ) {
			$rid  = (string) $sheet->attributes( $r_ns )['id'];
			$name = (string) $sheet['name'];
			if ( '' !== $rid ) { $map[ $rid ] = $name; }
		}
		return $map;
	}

	/**
	 * Parse xl/_rels/workbook.xml.rels to extract [rId => zip_entry_path].
	 *
	 * @param string $xml Raw workbook.xml.rels content.
	 * @return array<string,string>
	 */
	private static function parse_workbook_rels( string $xml ): array {
		$map  = array();
		$prev = libxml_use_internal_errors( true );
		$rels = simplexml_load_string( $xml, 'SimpleXMLElement', LIBXML_NONET | LIBXML_NOENT );
		libxml_use_internal_errors( $prev );
		if ( ! $rels ) { return $map; }
		foreach ( $rels->Relationship ?? array() as $rel ) {
			if ( ! str_ends_with( (string) $rel['Type'], '/worksheet' ) ) { continue; }
			$id     = (string) $rel['Id'];
			$target = (string) $rel['Target'];
			if ( str_starts_with( $target, '/' ) ) {
				// Absolute path (e.g. /xl/worksheets/sheet1.xml from openpyxl):
				// ZipArchive entries are relative -- strip the leading slash.
				$target = ltrim( $target, '/' );
			} elseif ( ! str_contains( $target, '://' ) ) {
				// Relative path (e.g. worksheets/sheet1.xml): prepend xl/ prefix.
				$target = 'xl/' . ltrim( $target, '/' );
			}
			$map[ $id ] = $target;
		}
		return $map;
	}

	// ── Private helpers ───────────────────────────────────────────────────────

	/**
	 * Locate the first worksheet entry path within an open ZipArchive.
	 *
	 * Tries xl/workbook.xml → xl/_rels/workbook.xml.rels to find the correct
	 * sheet file. Falls back to xl/worksheets/sheet1.xml if relationships
	 * cannot be parsed.
	 *
	 * @param ZipArchive $zip Open archive handle.
	 * @return string         Path inside the ZIP (e.g. "xl/worksheets/sheet1.xml").
	 */
	private static function find_first_sheet_entry( ZipArchive $zip ): string {
		$fallback = 'xl/worksheets/sheet1.xml';

		$rels_xml = $zip->getFromName( 'xl/_rels/workbook.xml.rels' );
		if ( false === $rels_xml ) {
			return $fallback;
		}

		$prev = libxml_use_internal_errors( true );
		$rels  = simplexml_load_string( $rels_xml, 'SimpleXMLElement', LIBXML_NONET | LIBXML_NOENT );
		libxml_use_internal_errors( $prev );

		if ( ! $rels ) {
			return $fallback;
		}

		// Find the first Relationship whose Type ends in "/worksheet".
		foreach ( $rels->Relationship as $rel ) {
			$type = (string) $rel['Type'];
			if ( str_ends_with( $type, '/worksheet' ) ) {
				$target = (string) $rel['Target'];
				// Target may be relative (e.g. "worksheets/sheet1.xml") or absolute.
				if ( ! str_starts_with( $target, '/' ) && ! str_contains( $target, '://' ) ) {
					$target = 'xl/' . ltrim( $target, '/' );
				}
				return $target;
			}
		}

		return $fallback;
	}

	/**
	 * Parse xl/sharedStrings.xml into a flat array indexed by shared-string index.
	 *
	 * @param string $xml Raw XML content.
	 * @return string[]   Flat array of string values.
	 */
	private static function parse_shared_strings( string $xml ): array {
		$strings = array();

		$prev = libxml_use_internal_errors( true );
		$sst  = simplexml_load_string( $xml, 'SimpleXMLElement', LIBXML_NONET | LIBXML_NOENT );
		libxml_use_internal_errors( $prev );

		if ( ! $sst ) {
			return $strings;
		}

		$sst->registerXPathNamespace( 'x', self::NS );
		$si_nodes = $sst->xpath( '//x:si' );

		// Fallback for files without a namespace declaration on the root.
		if ( empty( $si_nodes ) ) {
			$si_nodes = $sst->si ?? array();
		}

		foreach ( $si_nodes as $si ) {
			$strings[] = self::extract_si_text( $si );
		}

		return $strings;
	}

	/**
	 * Concatenate all text from an <si> element.
	 *
	 * Handles both simple <t> and rich-text <r><t>…</t></r> forms.
	 *
	 * @param SimpleXMLElement $si The <si> node.
	 * @return string
	 */
	private static function extract_si_text( SimpleXMLElement $si ): string {
		// Simple string: <si><t>value</t></si>
		if ( isset( $si->t ) ) {
			return (string) $si->t;
		}

		// Rich text: <si><r><t>part1</t></r><r><t>part2</t></r></si>
		$text = '';
		foreach ( $si->r ?? array() as $r ) {
			if ( isset( $r->t ) ) {
				$text .= (string) $r->t;
			}
		}

		return $text;
	}

	/**
	 * Convert an XLSX cell reference (e.g. "B3", "AA5") to a 0-based column index.
	 *
	 * @param string $ref  Cell reference such as "A1" or "BC42".
	 * @return int         0-based column index (A=0, B=1, …, Z=25, AA=26, …).
	 */
	private static function col_ref_to_index( string $ref ): int {
		$letters = rtrim( $ref, '0123456789' );
		$n       = 0;
		$len     = strlen( $letters );
		for ( $i = 0; $i < $len; $i++ ) {
			$n = $n * 26 + ( ord( $letters[ $i ] ) - ord( 'A' ) + 1 );
		}
		return max( 0, $n - 1 );
	}

	/**
	 * Parse a worksheet XML string into a 2-D array of string values.
	 *
	 * @param string   $xml            Raw sheet XML.
	 * @param string[] $shared_strings Shared strings lookup array.
	 * @return array|WP_Error          2-D array on success, WP_Error on failure.
	 */
	private static function parse_sheet( string $xml, array $shared_strings ): array|WP_Error {
		$prev = libxml_use_internal_errors( true );
		$ws   = simplexml_load_string( $xml, 'SimpleXMLElement', LIBXML_NONET | LIBXML_NOENT );
		libxml_use_internal_errors( $prev );

		if ( ! $ws ) {
			return new WP_Error( 'xml_parse', 'Could not parse XLSX worksheet XML.' );
		}

		$ws->registerXPathNamespace( 'x', self::NS );

		// Try namespace-qualified XPath first; fall back to direct children.
		$row_nodes = $ws->xpath( '//x:sheetData/x:row' );
		if ( false === $row_nodes || empty( $row_nodes ) ) {
			$row_nodes = $ws->sheetData->row ?? array();
		}

		$raw_rows = array(); // row_index (0-based) → [ col_index => value ]
		$max_col  = 0;

		foreach ( $row_nodes as $row_node ) {
			// Row index attribute is 1-based.
			$row_idx = isset( $row_node['r'] ) ? ( (int) $row_node['r'] - 1 ) : count( $raw_rows );

			$row_node->registerXPathNamespace( 'x', 'http://schemas.openxmlformats.org/spreadsheetml/2006/main' );
			$c_nodes = $row_node->xpath( 'x:c' );
			if ( false === $c_nodes || empty( $c_nodes ) ) {
				$c_nodes = $row_node->c ?? array();
			}

			$cells = array();

			foreach ( $c_nodes as $cell ) {
				$ref     = (string) ( $cell['r'] ?? '' );
				$col_idx = self::col_ref_to_index( $ref );
				$type    = (string) ( $cell['t'] ?? '' );

				switch ( $type ) {
					case 's':
						// Shared string — <v> holds the index.
						$idx = (int) (string) ( $cell->v ?? 0 );
						$val = $shared_strings[ $idx ] ?? '';
						break;

					case 'inlineStr':
						// Inline string — value inside <is><t>…
						$val = isset( $cell->is->t ) ? (string) $cell->is->t : '';
						break;

					case 'b':
						// Boolean.
						$val = ( '1' === (string) ( $cell->v ?? '' ) ) ? '1' : '0';
						break;

					case 'str':
					default:
						// Formula result or plain number.
						$val = isset( $cell->v ) ? (string) $cell->v : '';
						break;
				}

				$cells[ $col_idx ] = $val;

				if ( $col_idx > $max_col ) {
					$max_col = $col_idx;
				}
			}

			$raw_rows[ $row_idx ] = $cells;
		}

		if ( empty( $raw_rows ) ) {
			return array();
		}

		// Sort by row index and build dense rows (pad sparse cells with '').
		ksort( $raw_rows );

		$rows = array();
		foreach ( $raw_rows as $cells ) {
			$dense = array();
			for ( $c = 0; $c <= $max_col; $c++ ) {
				$dense[] = $cells[ $c ] ?? '';
			}
			$rows[] = $dense;
		}

		return $rows;
	}
}
