<?php
/**
 * Spreadsheet Reader — CSV and XLSX parser for admin autonomy.
 *
 * Parses uploaded CSV and XLSX files into normalized row arrays.
 * XLSX reading uses ZipArchive + SimpleXML (no external dependency).
 *
 * @package Niwa_AI
 * @since   0.10.56
 */

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

class Niwa_AI_Admin_Autonomy_Spreadsheet_Reader {

	const MAX_ROWS    = 1000;
	const MAX_COLUMNS = 30;
	const MAX_ARCHIVE_ENTRIES = 2000;
	const MAX_ARCHIVE_UNCOMPRESSED_BYTES = 67108864;
	const MAX_ARCHIVE_ENTRY_BYTES = 16777216;
	const MAX_ARCHIVE_COMPRESSION_RATIO = 200;

	/**
	 * Known column name aliases for auto-detection.
	 *
	 * @var array<string, string[]>
	 */
	private static $column_aliases = array(
		'product_id'    => array( 'product_id', 'id', 'ID', 'post_id', 'woo_id' ),
		'variation_id'  => array( 'variation_id', 'var_id' ),
		'sku'           => array( 'sku', 'SKU', 'sifra', 'šifra', 'artikal' ),
		'name'          => array( 'name', 'naziv', 'product_name', 'ime', 'title', 'naslov', 'proizvod' ),
		'regular_price' => array( 'regular_price', 'price', 'cena', 'cijena', 'redovna_cena', 'nova_cena', 'new_price' ),
		'sale_price'    => array( 'sale_price', 'akcijska_cena', 'popust_cena', 'sale', 'akcija', 'snizena_cena' ),
	);

	/* ================================================================
	 * Public API
	 * ============================================================= */

	/**
	 * Parse a file (CSV or XLSX) into normalized rows.
	 *
	 * @param string $file_path Absolute path to the file.
	 * @param array  $options   Optional: max_rows, sheet_name.
	 * @return array {success, headers, rows, column_map, total_rows, format}
	 */
	public function parse_file( $file_path, $options = array() ) {
		$file_path = (string) $file_path;
		if ( '' === $file_path || ! file_exists( $file_path ) ) {
			return array( 'success' => false, 'error' => 'Fajl ne postoji: ' . basename( $file_path ) );
		}

		$ext      = strtolower( pathinfo( $file_path, PATHINFO_EXTENSION ) );
		$max_rows = isset( $options['max_rows'] ) ? max( 1, min( self::MAX_ROWS, intval( $options['max_rows'] ) ) ) : self::MAX_ROWS;

		if ( 'csv' === $ext || 'txt' === $ext ) {
			return $this->parse_csv( $file_path, $max_rows );
		}

		if ( 'xlsx' === $ext ) {
			$sheet_name = isset( $options['sheet_name'] ) ? (string) $options['sheet_name'] : '';
			return $this->parse_xlsx( $file_path, $sheet_name, $max_rows );
		}

		if ( 'xls' === $ext ) {
			return array( 'success' => false, 'error' => 'Stari .xls format nije podržan. Sačuvaj fajl kao .xlsx ili .csv pa pošalji ponovo.' );
		}

		return array( 'success' => false, 'error' => 'Nepodržan format fajla: .' . $ext );
	}

	/* ================================================================
	 * CSV Parser
	 * ============================================================= */

	private function parse_csv( $file_path, $max_rows ) {
		$handle = fopen( $file_path, 'r' );
		if ( ! $handle ) {
			return array( 'success' => false, 'error' => 'Ne mogu da otvorim CSV fajl.' );
		}

		// Read first line to detect delimiter.
		$first_line = fgets( $handle );
		rewind( $handle );
		if ( false === $first_line || '' === trim( $first_line ) ) {
			fclose( $handle );
			return array( 'success' => false, 'error' => 'CSV fajl je prazan.' );
		}

		$delimiter = $this->detect_csv_delimiter( $first_line );

		// Read header row.
		$raw_headers = fgetcsv( $handle, 0, $delimiter );
		if ( ! is_array( $raw_headers ) || empty( $raw_headers ) ) {
			fclose( $handle );
			return array( 'success' => false, 'error' => 'CSV nema header red.' );
		}

		$headers = array_map( function ( $h ) {
			return trim( (string) $h );
		}, array_slice( $raw_headers, 0, self::MAX_COLUMNS ) );

		$column_map = $this->detect_column_mapping( $headers );

		// Read data rows.
		$rows       = array();
		$total_rows = 0;
		while ( ( $row = fgetcsv( $handle, 0, $delimiter ) ) !== false ) {
			$total_rows++;
			if ( count( $rows ) >= $max_rows ) {
				continue; // Keep counting total but stop storing.
			}

			$mapped = array();
			foreach ( $headers as $i => $header ) {
				$value = isset( $row[ $i ] ) ? trim( (string) $row[ $i ] ) : '';
				$mapped[ $header ] = $value;
			}
			$rows[] = $mapped;
		}

		fclose( $handle );

		return array(
			'success'    => true,
			'format'     => 'csv',
			'headers'    => $headers,
			'column_map' => $column_map,
			'rows'       => $rows,
			'total_rows' => $total_rows,
		);
	}

	private function detect_csv_delimiter( $line ) {
		$semicolons = substr_count( $line, ';' );
		$commas     = substr_count( $line, ',' );
		$tabs       = substr_count( $line, "\t" );

		if ( $tabs >= $semicolons && $tabs >= $commas && $tabs > 0 ) {
			return "\t";
		}
		if ( $semicolons > $commas ) {
			return ';';
		}
		return ',';
	}

	/* ================================================================
	 * XLSX Parser (ZipArchive + SimpleXML)
	 * ============================================================= */

	private function parse_xlsx( $file_path, $sheet_name, $max_rows ) {
		if ( ! class_exists( 'ZipArchive' ) ) {
			return array( 'success' => false, 'error' => 'PHP ZipArchive ekstenzija nije dostupna. Pošalji CSV umesto XLSX.' );
		}

		$zip = new ZipArchive();
		if ( true !== $zip->open( $file_path ) ) {
			return array( 'success' => false, 'error' => 'Ne mogu da otvorim XLSX fajl.' );
		}
		$archive_error = $this->validate_xlsx_archive( $zip );
		if ( '' !== $archive_error ) {
			$zip->close();
			return array( 'success' => false, 'error' => $archive_error );
		}

		$shared_strings = $this->read_xlsx_shared_strings( $zip );
		$sheet_path     = $this->resolve_xlsx_sheet_path( $zip, $sheet_name );

		if ( '' === $sheet_path ) {
			$zip->close();
			return array( 'success' => false, 'error' => 'XLSX nema sheet podatke.' );
		}

		$result = $this->read_xlsx_sheet_data( $zip, $sheet_path, $shared_strings, $max_rows );
		$zip->close();

		return $result;
	}

	private function validate_xlsx_archive( $zip ) {
		if ( $zip->numFiles > self::MAX_ARCHIVE_ENTRIES ) {
			return 'XLSX sadrzi previse archive unosa.';
		}

		$total = 0;
		for ( $i = 0; $i < $zip->numFiles; $i++ ) {
			$stat = $zip->statIndex( $i );
			if ( ! is_array( $stat ) ) {
				continue;
			}

			$size      = max( 0, intval( $stat['size'] ?? 0 ) );
			$comp_size = max( 0, intval( $stat['comp_size'] ?? 0 ) );
			$total    += $size;
			if (
				$size > self::MAX_ARCHIVE_ENTRY_BYTES
				|| $total > self::MAX_ARCHIVE_UNCOMPRESSED_BYTES
				|| ( $size > 1048576 && $comp_size > 0 && ( $size / $comp_size ) > self::MAX_ARCHIVE_COMPRESSION_RATIO )
			) {
				return 'XLSX archive expansion prelazi bezbedan limit.';
			}
		}

		return '';
	}

	private function read_xlsx_shared_strings( $zip ) {
		$strings = array();
		$xml     = $zip->getFromName( 'xl/sharedStrings.xml' );
		if ( false === $xml || '' === $xml ) {
			return $strings;
		}

		libxml_use_internal_errors( true );
		$doc = simplexml_load_string( $xml );
		if ( false === $doc ) {
			return $strings;
		}

		foreach ( $doc->si as $si ) {
			// Handle both <t> direct text and <r> rich text runs.
			if ( isset( $si->t ) ) {
				$strings[] = (string) $si->t;
			} elseif ( isset( $si->r ) ) {
				$text = '';
				foreach ( $si->r as $run ) {
					$text .= (string) $run->t;
				}
				$strings[] = $text;
			} else {
				$strings[] = '';
			}
		}

		return $strings;
	}

	private function resolve_xlsx_sheet_path( $zip, $sheet_name ) {
		// Try direct sheet1.xml first.
		if ( '' === $sheet_name && false !== $zip->getFromName( 'xl/worksheets/sheet1.xml' ) ) {
			return 'xl/worksheets/sheet1.xml';
		}

		// Parse workbook.xml to find sheet by name.
		$workbook_xml = $zip->getFromName( 'xl/workbook.xml' );
		if ( false === $workbook_xml ) {
			return false !== $zip->getFromName( 'xl/worksheets/sheet1.xml' ) ? 'xl/worksheets/sheet1.xml' : '';
		}

		libxml_use_internal_errors( true );
		$workbook = simplexml_load_string( $workbook_xml );
		if ( false === $workbook ) {
			return 'xl/worksheets/sheet1.xml';
		}

		$sheet_index = 1;
		if ( '' !== $sheet_name && isset( $workbook->sheets->sheet ) ) {
			foreach ( $workbook->sheets->sheet as $i => $sheet ) {
				$name = (string) $sheet['name'];
				if ( strtolower( $name ) === strtolower( $sheet_name ) ) {
					$sheet_index = $i + 1;
					break;
				}
			}
		}

		$path = 'xl/worksheets/sheet' . $sheet_index . '.xml';
		return false !== $zip->getFromName( $path ) ? $path : '';
	}

	private function read_xlsx_sheet_data( $zip, $sheet_path, $shared_strings, $max_rows ) {
		$xml = $zip->getFromName( $sheet_path );
		if ( false === $xml || '' === $xml ) {
			return array( 'success' => false, 'error' => 'XLSX sheet je prazan.' );
		}

		libxml_use_internal_errors( true );
		$sheet = simplexml_load_string( $xml );
		if ( false === $sheet ) {
			return array( 'success' => false, 'error' => 'XLSX sheet XML nije validan.' );
		}

		if ( ! isset( $sheet->sheetData->row ) ) {
			return array( 'success' => false, 'error' => 'XLSX sheet nema redove.' );
		}

		$all_rows   = array();
		$row_count  = 0;
		foreach ( $sheet->sheetData->row as $row ) {
			$cells = array();
			if ( ! isset( $row->c ) ) {
				continue;
			}
			foreach ( $row->c as $cell ) {
				$col_letter = preg_replace( '/[0-9]+/', '', (string) $cell['r'] );
				$col_index  = $this->xlsx_column_to_index( $col_letter );
				if ( $col_index >= self::MAX_COLUMNS ) {
					continue;
				}

				$value = $this->xlsx_cell_value( $cell, $shared_strings );
				$cells[ $col_index ] = $value;
			}
			$all_rows[] = $cells;
			$row_count++;
		}

		if ( empty( $all_rows ) ) {
			return array( 'success' => false, 'error' => 'XLSX sheet nema podatke.' );
		}

		// First row = headers.
		$header_cells = array_shift( $all_rows );
		$headers      = array();
		$max_col      = empty( $header_cells ) ? 0 : max( array_keys( $header_cells ) );
		for ( $i = 0; $i <= $max_col && $i < self::MAX_COLUMNS; $i++ ) {
			$headers[ $i ] = isset( $header_cells[ $i ] ) ? trim( (string) $header_cells[ $i ] ) : 'col_' . $i;
		}

		$column_map = $this->detect_column_mapping( $headers );

		$rows       = array();
		$total_rows = count( $all_rows );
		foreach ( array_slice( $all_rows, 0, $max_rows ) as $raw_row ) {
			$mapped = array();
			foreach ( $headers as $i => $header ) {
				$mapped[ $header ] = isset( $raw_row[ $i ] ) ? trim( (string) $raw_row[ $i ] ) : '';
			}
			$rows[] = $mapped;
		}

		return array(
			'success'    => true,
			'format'     => 'xlsx',
			'headers'    => array_values( $headers ),
			'column_map' => $column_map,
			'rows'       => $rows,
			'total_rows' => $total_rows,
		);
	}

	private function xlsx_cell_value( $cell, $shared_strings ) {
		$type  = isset( $cell['t'] ) ? (string) $cell['t'] : '';
		$value = isset( $cell->v ) ? (string) $cell->v : '';

		if ( 's' === $type && isset( $shared_strings[ intval( $value ) ] ) ) {
			return $shared_strings[ intval( $value ) ];
		}

		if ( 'inlineStr' === $type && isset( $cell->is->t ) ) {
			return (string) $cell->is->t;
		}

		return $value;
	}

	private function xlsx_column_to_index( $col_letter ) {
		$col_letter = strtoupper( (string) $col_letter );
		$index      = 0;
		$len        = strlen( $col_letter );
		for ( $i = 0; $i < $len; $i++ ) {
			$index = $index * 26 + ( ord( $col_letter[ $i ] ) - ord( 'A' ) + 1 );
		}
		return $index - 1;
	}

	/* ================================================================
	 * Column Auto-Detection
	 * ============================================================= */

	private function detect_column_mapping( $headers ) {
		$map  = array();
		$used = array();

		foreach ( self::$column_aliases as $canonical => $aliases ) {
			foreach ( $headers as $header ) {
				if ( isset( $used[ $header ] ) ) {
					continue;
				}
				$normalized = $this->normalize_header( $header );
				foreach ( $aliases as $alias ) {
					if ( $normalized === $this->normalize_header( $alias ) ) {
						$map[ $canonical ] = $header;
						$used[ $header ]   = true;
						break 2;
					}
				}
			}
		}

		return $map;
	}

	private function normalize_header( $text ) {
		$text = strtolower( trim( (string) $text ) );
		$text = remove_accents( $text );
		$text = preg_replace( '/[^a-z0-9]/', '_', $text );
		$text = preg_replace( '/_+/', '_', $text );
		return trim( $text, '_' );
	}
}
