<?php
/**
 * Parses supplier XLSX price lists into a matrix of rows.
 *
 * @package EmberPrice
 */

namespace EmberPrice\Ingestion;

use EmberPrice\Vendor\PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use EmberPrice\Vendor\PhpOffice\PhpSpreadsheet\IOFactory;
use EmberPrice\Vendor\PhpOffice\PhpSpreadsheet\Spreadsheet;
use EmberPrice\Vendor\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

/**
 * Picks the sheet with the most data rows by default (selectable), and
 * propagates merged cell values to every cell in the merge range before
 * building the row matrix. Header row detection reuses
 * {@see HeaderRowDetector}, same as CsvParser. Pure PHP, no WordPress
 * dependency (imports Strauss-prefixed PhpSpreadsheet classes only).
 */
final class XlsxParser {

	/**
	 * Used to locate the real header row.
	 *
	 * @var HeaderRowDetector
	 */
	private HeaderRowDetector $header_row_detector;

	/**
	 * Constructor.
	 *
	 * @param HeaderRowDetector|null $header_row_detector Defaults to a new instance.
	 */
	public function __construct( ?HeaderRowDetector $header_row_detector = null ) {
		$this->header_row_detector = $header_row_detector ?? new HeaderRowDetector();
	}

	/**
	 * Result shape: `rows` (string matrix), `header_row` (index or null),
	 * `sheet` (the name of the sheet that was read).
	 *
	 * @param string          $path  Path to the XLSX file.
	 * @param string|int|null $sheet Sheet name or index to read; null (default)
	 *                               picks the sheet with the most data rows.
	 *
	 * @return array{rows: array<int, string[]>, header_row: int|null, sheet: string}
	 */
	public function parse( string $path, $sheet = null ): array {
		$spreadsheet = IOFactory::load( $path );
		$worksheet   = $this->select_worksheet( $spreadsheet, $sheet );
		$rows        = $this->extract_rows( $worksheet );

		return array(
			'rows'       => $rows,
			'header_row' => $this->header_row_detector->detect( $rows ),
			'sheet'      => $worksheet->getTitle(),
		);
	}

	/**
	 * Resolves an explicit sheet selection, falling back to auto-select.
	 *
	 * @param Spreadsheet     $spreadsheet The loaded workbook.
	 * @param string|int|null $sheet       Sheet name, index, or null for auto-select.
	 */
	private function select_worksheet( Spreadsheet $spreadsheet, $sheet ): Worksheet {
		if ( is_int( $sheet ) ) {
			return $spreadsheet->getSheet( $sheet );
		}

		if ( is_string( $sheet ) ) {
			$worksheet = $spreadsheet->getSheetByName( $sheet );

			if ( null !== $worksheet ) {
				return $worksheet;
			}
		}

		return $this->sheet_with_most_data_rows( $spreadsheet );
	}

	/**
	 * Picks the sheet with the most non-empty rows.
	 *
	 * @param Spreadsheet $spreadsheet The loaded workbook.
	 */
	private function sheet_with_most_data_rows( Spreadsheet $spreadsheet ): Worksheet {
		$best       = $spreadsheet->getSheet( 0 );
		$best_count = -1;

		foreach ( $spreadsheet->getAllSheets() as $candidate ) {
			$count = $this->count_data_rows( $candidate );

			if ( $count > $best_count ) {
				$best_count = $count;
				$best       = $candidate;
			}
		}

		return $best;
	}

	/**
	 * Counts rows with at least one non-empty cell.
	 *
	 * @param Worksheet $worksheet The sheet to count non-empty rows in.
	 */
	private function count_data_rows( Worksheet $worksheet ): int {
		$count = 0;

		foreach ( $worksheet->toArray( null, false, false, false ) as $row ) {
			$has_data = array_filter(
				$row,
				static fn( $cell ) => null !== $cell && '' !== trim( (string) $cell )
			);

			if ( ! empty( $has_data ) ) {
				++$count;
			}
		}

		return $count;
	}

	/**
	 * Propagates merged cell values, then converts the sheet to a trimmed
	 * string matrix.
	 *
	 * @param Worksheet $worksheet The sheet to extract.
	 *
	 * @return array<int, string[]>
	 */
	private function extract_rows( Worksheet $worksheet ): array {
		$this->propagate_merged_cells( $worksheet );

		$raw_rows = $worksheet->toArray( null, true, false, false );

		return array_map(
			static fn( array $row ) => array_map(
				static fn( $cell ) => trim( (string) ( $cell ?? '' ) ),
				$row
			),
			$raw_rows
		);
	}

	/**
	 * By default, only a merge range's top-left cell holds a value; the
	 * rest read as empty. Copies that value across every cell in the range
	 * so the row matrix doesn't have artificial gaps.
	 *
	 * @param Worksheet $worksheet The sheet to mutate in place.
	 */
	private function propagate_merged_cells( Worksheet $worksheet ): void {
		foreach ( $worksheet->getMergeCells() as $range ) {
			$cell_references = Coordinate::extractAllCellReferencesInRange( $range );

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

			$value = $worksheet->getCell( $cell_references[0] )->getValue();

			foreach ( $cell_references as $cell_reference ) {
				$worksheet->getCell( $cell_reference )->setValue( $value );
			}
		}
	}
}
