<?php
namespace IdeoFieldPorter;

defined( 'ABSPATH' ) || exit;

/**
 * Native XLSX reader – ZipArchive + SimpleXML only.
 * Compatible PHP 7.2+. No Composer dependency.
 *
 * Handles ACF field types properly:
 *  - checkbox / radio / select : comma-separated values split into arrays.
 *  - number / range            : cast to float.
 *  - repeater                  : each column is one iteration; sub-fields as "Label : Value" lines.
 *  - group sub-fields          : batched and written in one update_field() call.
 *  - flexible content          : JSON array of {layout, fields} blocks (see exporter).
 *  - taxonomies                : __tax_{taxonomy}__ destination key.
 *  - featured image            : __featured_image__ destination key (URL → attachment ID).
 */
class Spreadsheet_Importer {

	// ── Public entry points (used by Admin_Page) ──────────────────────────────

	public function read_xlsx_public( $file_path ) {
		return $this->read_xlsx( $file_path );
	}

	public function read_csv_public( $file_path ) {
		return $this->read_csv( $file_path );
	}

	public function read_xml_public( $file_path ) {
		return $this->read_xml( $file_path );
	}

	/**
	 * Read all sheet names from an XLSX file.
	 * Returns [ 'Sheet1' => [ [row], … ], 'Sheet2' => … ]
	 */
	public function read_xlsx_all_sheets( $file_path ) {
		if ( ! class_exists( 'ZipArchive' ) ) {
			throw new \RuntimeException( esc_html__( 'The PHP ZipArchive extension is required but not installed.', 'ideo-field-porter' ) );
		}
		$zip = new \ZipArchive();
		if ( true !== $zip->open( $file_path ) ) {
			throw new \RuntimeException( esc_html__( 'Unable to open the .xlsx file (invalid ZIP archive).', 'ideo-field-porter' ) );
		}

		$shared_strings = array();
		$ss_index       = $zip->locateName( 'xl/sharedStrings.xml' );
		if ( false !== $ss_index ) {
			$ss_xml = $zip->getFromIndex( $ss_index );
			if ( $ss_xml ) $shared_strings = $this->parse_shared_strings( $ss_xml );
		}

		// Parse workbook to get ordered sheet names + rIds.
		$wb_xml   = $zip->getFromName( 'xl/workbook.xml' );
		$rels_xml = $zip->getFromName( 'xl/_rels/workbook.xml.rels' );
		$sheets   = array(); // [ 'name' => rows_array ]

		if ( $wb_xml && $rels_xml ) {
			$wb   = @simplexml_load_string( $this->strip_namespaces( $wb_xml ) );
			$rels = @simplexml_load_string( $this->strip_namespaces( $rels_xml ) );

			if ( $wb && $rels && isset( $wb->sheets->sheet ) ) {
				// Build rId → file path map.
				$rid_to_file = array();
				foreach ( $rels->Relationship as $rel ) {
					$ra = $rel->attributes();
					if ( isset( $ra['Id'] ) && isset( $ra['Target'] ) ) {
						$target = (string) $ra['Target'];
						$file   = ( '/' === substr( $target, 0, 1 ) ) ? ltrim( $target, '/' ) : 'xl/' . $target;
						$rid_to_file[ (string) $ra['Id'] ] = $file;
					}
				}

				// Build a positional fallback list (sheet1.xml, sheet2.xml...) for when rId fails.
				$ordered_files = array();
				foreach ( $rid_to_file as $rid => $file ) {
					if ( preg_match( '/sheet(\d+)\.xml$/i', $file, $m ) ) {
						$ordered_files[ (int) $m[1] ] = $file;
					}
				}
				ksort( $ordered_files );
				$ordered_files = array_values( $ordered_files );

				$sheet_pos = 0;
				foreach ( $wb->sheets->sheet as $sheet_node ) {
					$attrs = $sheet_node->attributes();
					$name  = isset( $attrs['name'] ) ? (string) $attrs['name'] : 'Sheet';

					// strip_namespaces() strips tag prefixes but NOT attribute prefixes.
					// So r:id stays as r:id in attribute names.
					$r_id = '';
					if ( isset( $attrs['id'] ) && '' !== (string) $attrs['id'] ) {
						$r_id = (string) $attrs['id'];
					} elseif ( isset( $attrs['r:id'] ) && '' !== (string) $attrs['r:id'] ) {
						$r_id = (string) $attrs['r:id'];
					}

					// Resolve: first by rId, then by position.
					if ( '' !== $r_id && isset( $rid_to_file[ $r_id ] ) ) {
						$sheet_file = $rid_to_file[ $r_id ];
					} elseif ( isset( $ordered_files[ $sheet_pos ] ) ) {
						$sheet_file = $ordered_files[ $sheet_pos ];
					} else {
						$sheet_pos++;
						continue;
					}

					$sheet_xml = $zip->getFromName( $sheet_file );
					if ( $sheet_xml ) {
						$sheets[ $name ] = $this->parse_sheet( $sheet_xml, $shared_strings );
					}
					$sheet_pos++;
				}
			}
		}

		$zip->close();

		if ( empty( $sheets ) ) {
			throw new \RuntimeException( esc_html__( 'Unable to read the sheets of the workbook.', 'ideo-field-porter' ) );
		}

		return $sheets;
	}

	// ── Import with manual mapping ─────────────────────────────────────────────

	/**
	 * @param string $file_path  Path to .xlsx or .csv file.
	 * @param array  $mapping    [ 'Excel column name' => '__title__' | '__featured_image__' | '__tax_{taxonomy}__' | 'field_key_acf' ]
	 * @param string $ext        File extension: 'xlsx' or 'csv'.
	 * @param string $post_type  Target CPT slug.
	 *
	 * @return array { created, skipped, duplicates, errors, created_ids }
	 */
	public function import_with_mapping( $file_path, $mapping, $ext = 'xlsx', $post_type = 'post', $separators = array(), $replace_titles = array() ) {
		$result = array(
			'created'     => 0,
			'skipped'      => 0,
			'skipped_db'   => 0,
			'skipped_dupe' => 0,
			'duplicates'   => array(),
			'errors'       => array(),
			'created_ids'  => array(),
		);

		try {
			if ( 'csv' === $ext ) {
				$rows = $this->read_csv( $file_path );
			} elseif ( 'xml' === $ext ) {
				$rows = $this->read_xml( $file_path );
			} else {
				// Read ALL sheets and merge: header from first sheet, data rows from all sheets.
				$all_sheets = $this->read_xlsx_all_sheets( $file_path );
				$rows       = array();
				$header_set = false;
				foreach ( $all_sheets as $sheet_rows ) {
					if ( empty( $sheet_rows ) ) continue;
					if ( ! $header_set ) {
						$rows[]     = $sheet_rows[0]; // header row once
						$header_set = true;
					}
					// Append data rows (skip header row of each sheet).
					for ( $s = 1; $s < count( $sheet_rows ); $s++ ) {
						$rows[] = $sheet_rows[ $s ];
					}
				}
			}
		} catch ( \Exception $e ) {
			$result['errors'][] = $e->getMessage();
			return $result;
		}

		if ( count( $rows ) < 2 ) {
			$result['errors'][] = __( 'The file contains no data rows.', 'ideo-field-porter' );
			return $result;
		}

		$headers = $rows[0];

		// Build index: col_index (int) => destination string
		$col_destinations = array();
		foreach ( $headers as $idx => $col_name ) {
			if ( isset( $mapping[ $col_name ] ) && '' !== $mapping[ $col_name ] ) {
				$col_destinations[ $idx ] = $mapping[ $col_name ];
			}
		}

		$total = count( $rows );

		// Track titles already processed in this run — a row whose title was
		// already created earlier in the same file is an intra-file duplicate,
		// not a fresh "already exists in database" hit.
		$seen_titles = array();

		// Bulk pre-check: which titles already exist in the DB? One query for the
		// whole file instead of one WP_Query per row inside create_post() — the
		// per-row query used to dominate import time on large files (thousands
		// of individual round-trips for what's otherwise a fast operation).
		$title_col_idx = null;
		foreach ( $col_destinations as $idx => $dest ) {
			if ( '__title__' === $dest ) { $title_col_idx = $idx; break; }
		}
		$all_titles = array();
		if ( null !== $title_col_idx ) {
			for ( $i = 1; $i < $total; $i++ ) {
				if ( isset( $rows[ $i ][ $title_col_idx ] ) ) {
					$t = trim( (string) $rows[ $i ][ $title_col_idx ] );
					if ( '' !== $t ) $all_titles[] = $t;
				}
			}
		}
		$existing_ids_by_title = $this->find_existing_post_ids( $post_type, $all_titles );

		for ( $i = 1; $i < $total; $i++ ) {
			$row                 = $rows[ $i ];
			$post_title          = '';
			$featured_image_url  = '';
			$tax_values          = array(); // taxonomy_name => [ 'raw'=>..., 'sep'=>... ]
			$acf_values          = array(); // field_key     => [ 'raw'=>..., 'sep'=>... ]

			foreach ( $col_destinations as $idx => $dest ) {
				$raw = isset( $row[ $idx ] ) ? trim( (string) $row[ $idx ] ) : '';
				// Get the column name to look up its separator
				$col_name = isset( $headers[ $idx ] ) ? $headers[ $idx ] : '';
				$sep      = ( $col_name !== '' && isset( $separators[ $col_name ] ) ) ? $separators[ $col_name ] : '|';

				if ( '__title__' === $dest ) {
					$post_title = $raw;

				} elseif ( '__featured_image__' === $dest ) {
					$featured_image_url = $raw;

				} elseif ( 0 === strpos( $dest, '__tax_' ) && '__' === substr( $dest, -2 ) ) {
					$tax_name              = substr( $dest, 6, -2 );
					$tax_values[ $tax_name ] = array( 'raw' => $raw, 'sep' => $sep );

				} else {
					// For repeater fields, the same $dest key appears for each #N column.
					// Accumulate as an array of iterations instead of overwriting.
					$field_obj_check = function_exists( 'acf_get_field' ) ? acf_get_field( $dest ) : false;
					$dest_type       = ( $field_obj_check && isset( $field_obj_check['type'] ) ) ? $field_obj_check['type'] : '';
					if ( 'repeater' === $dest_type ) {
						$acf_values[ $dest ]['__repeater_rows'][] = array( 'raw' => $raw, 'sep' => $sep );
					} else {
						$acf_values[ $dest ] = array( 'raw' => $raw, 'sep' => $sep );
					}
				}
			}

			if ( '' === $post_title ) {
				$result['skipped']++;
				continue;
			}

			$lc_title = mb_strtolower( $post_title );
			if ( isset( $seen_titles[ $lc_title ] ) ) {
				$result['skipped']++;
				$result['skipped_dupe']++;
				continue;
			}
			$seen_titles[ $lc_title ] = true;

			$row_overwrite = in_array( $post_title, $replace_titles, true );
			$existing_id   = isset( $existing_ids_by_title[ $post_title ] ) ? $existing_ids_by_title[ $post_title ] : 0;
			$outcome       = $this->create_post( $post_title, $tax_values, $acf_values, $post_type, $featured_image_url, $separators, $row_overwrite, $existing_id );

			if ( is_array( $outcome ) && ! empty( $outcome['duplicate'] ) ) {
				$result['skipped']++;
				$result['skipped_db']++;
				$result['duplicates'][] = $outcome['title'];
			} elseif ( is_wp_error( $outcome ) ) {
				$result['errors'][] = sprintf(
					/* translators: 1: row number, 2: post title, 3: error message */
					__( 'Row %1$d ("%2$s"): %3$s', 'ideo-field-porter' ),
					$i + 1, $post_title, $outcome->get_error_message()
				);
				$result['skipped']++;
			} else {
				$result['created']++;
				$result['created_ids'][] = $outcome;
			}
		}

		return $result;
	}

	/**
	 * Bulk pre-check: which of these titles already exist as a published post
	 * of this type? One query for the whole list, unlike create_post()'s own
	 * per-title WP_Query (fine there since it only ever checks one title per
	 * row already being processed) — used by the duplicate-titles AJAX check
	 * before an import runs, so it doesn't do N queries for N rows.
	 *
	 * @param string $post_type
	 * @param array  $titles
	 * @return array Matched titles (subset of $titles), as found in the DB.
	 */
	public function find_existing_titles( $post_type, array $titles ) {
		global $wpdb;
		$titles = array_values( array_unique( array_filter( $titles, function( $t ) { return '' !== trim( (string) $t ); } ) ) );
		if ( empty( $titles ) ) return array();

		$placeholders = implode( ',', array_fill( 0, count( $titles ), '%s' ) );
		$query        = $wpdb->prepare(
			"SELECT DISTINCT post_title FROM {$wpdb->posts} WHERE post_type = %s AND post_status = 'publish' AND post_title IN ($placeholders)", // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $placeholders is a fixed-count %s list, not user input; {$wpdb->posts} is the table name, not user input.
			array_merge( array( $post_type ), $titles )
		);
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- $query was already built via $wpdb->prepare() above; one-off duplicate-title lookup before an import, not a hot path worth object-caching.
		return $wpdb->get_col( $query );
	}

	/**
	 * Bulk pre-check returning IDs, not just titles: which of these titles
	 * already exist as a published post of this type, and what is their ID?
	 * One query for the whole file — lets create_post() do a plain array
	 * lookup instead of its own WP_Query per row.
	 *
	 * @param string $post_type
	 * @param array  $titles
	 * @return array [ post_title => post_id ]
	 */
	private function find_existing_post_ids( $post_type, array $titles ) {
		global $wpdb;
		$titles = array_values( array_unique( array_filter( $titles, function( $t ) { return '' !== trim( (string) $t ); } ) ) );
		if ( empty( $titles ) ) return array();

		$placeholders = implode( ',', array_fill( 0, count( $titles ), '%s' ) );
		$query        = $wpdb->prepare(
			"SELECT ID, post_title FROM {$wpdb->posts} WHERE post_type = %s AND post_status = 'publish' AND post_title IN ($placeholders) ORDER BY ID ASC", // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $placeholders is a fixed-count %s list, not user input; {$wpdb->posts} is the table name, not user input.
			array_merge( array( $post_type ), $titles )
		);
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- $query was already built via $wpdb->prepare() above; one-off lookup before an import, not a hot path worth object-caching.
		$results = $wpdb->get_results( $query );
		$map     = array();
		foreach ( $results as $r ) {
			// First (lowest ID) match wins if the DB somehow has duplicate titles.
			if ( ! isset( $map[ $r->post_title ] ) ) $map[ $r->post_title ] = (int) $r->ID;
		}
		return $map;
	}

	// ── Post creation ─────────────────────────────────────────────────────────

	/**
	 * @param string $title              Post title.
	 * @param array  $tax_values         [ taxonomy_name => 'Term A, Term B' ].
	 * @param array  $acf_values         [ field_key => raw_string_value ].
	 * @param string $post_type          Target CPT slug.
	 * @param string $featured_image_url URL of the featured image (empty = none).
	 *
	 * @param int    $existing_id        ID of the already-published post with this
	 *                                  title, precomputed once for the whole file
	 *                                  by find_existing_post_ids() — 0 if none.
	 * @return int|array|\WP_Error  Post ID on success, duplicate array, or WP_Error.
	 */
	private function create_post( $title, $tax_values, $acf_values, $post_type = 'post', $featured_image_url = '', $separators = array(), $overwrite = false, $existing_id = 0 ) {
		if ( $existing_id ) {
			if ( ! $overwrite ) {
				return array( 'duplicate' => true, 'title' => $title );
			}
			// Overwrite mode — update existing post's mapped fields only.
			$post_id = $existing_id;
		} else {
			$post_id = wp_insert_post( array(
				'post_title'  => $title,
				'post_status' => 'publish',
				'post_type'   => $post_type,
			), true );

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

		// ── Featured Image ────────────────────────────────────────────────────
		if ( '' !== $featured_image_url ) {
			$attachment_id = $this->resolve_featured_image( $featured_image_url );
			if ( $attachment_id ) {
				set_post_thumbnail( $post_id, $attachment_id );
			}
		}

		// ── Taxonomies ────────────────────────────────────────────────────────
		foreach ( $tax_values as $tax_name => $entry ) {
			$raw = is_array( $entry ) ? $entry['raw'] : $entry;
			$sep = is_array( $entry ) ? $entry['sep'] : '|';
			if ( '' === $raw ) continue;
			$term_ids = array();
			foreach ( explode( $sep, $raw ) as $term_name ) {
				$term_name = trim( $term_name );
				if ( '' === $term_name ) continue;
				$term = get_term_by( 'name', $term_name, $tax_name );
				if ( $term && ! is_wp_error( $term ) ) {
					$term_ids[] = $term->term_id;
				}
			}
			if ( ! empty( $term_ids ) ) {
				wp_set_object_terms( $post_id, $term_ids, $tax_name, false );
			}
		}

		// ── ACF fields ────────────────────────────────────────────────────────
		if ( ! empty( $acf_values ) && function_exists( 'update_field' ) ) {
			$root_fields     = array();
			$group_fields    = array();
			// repeater_fields: [ repeater_key => [ sub_name => [ row0_val, row1_val, ... ] ] ]
			$repeater_fields = array();

			foreach ( $acf_values as $field_key => $entry ) {
				$field_obj   = function_exists( 'acf_get_field' ) ? acf_get_field( $field_key ) : false;
				$type        = ( $field_obj && isset( $field_obj['type'] ) ) ? $field_obj['type'] : '';

				// Repeater entries use __repeater_rows structure — handle before reading raw/sep.
				if ( 'repeater' === $type ) {
					// Each column = one iteration. Cell format: "subfield_name : value\nsubfield_name : value..."
					$iterations = isset( $entry['__repeater_rows'] ) ? $entry['__repeater_rows'] : array( array( 'raw' => '' ) );
					$sub_fields = array();
					if ( $field_obj && ! empty( $field_obj['sub_fields'] ) ) {
						$sub_fields = $field_obj['sub_fields'];
					}
					foreach ( $iterations as $iteration ) {
						$iter_raw = is_array( $iteration ) && isset( $iteration['raw'] ) ? $iteration['raw'] : (string) $iteration;
						if ( '' === trim( $iter_raw ) ) continue;

						$row_data = array();
						// Split cell into lines, each line = "subfield_name : value"
						$lines = preg_split( '/\r?\n/', $iter_raw );
						foreach ( $lines as $line ) {
							$line = trim( $line );
							if ( '' === $line ) continue;
							// Support both non-breaking space colon (Â :) and regular colon.
							if ( preg_match( '/^(.+?)\s*:\s*(.*)$/', $line, $m ) ) {
								$row_data[ trim( $m[1] ) ] = trim( $m[2] );
							}
						}

						// If no "name : value" pairs found, fall back to positional (plain text).
						if ( empty( $row_data ) && ! empty( $sub_fields ) ) {
							$parts = array_map( 'trim', explode( ',', $iter_raw ) );
							foreach ( $sub_fields as $i => $sub ) {
								$row_data[ $sub['name'] ] = isset( $parts[ $i ] ) ? $parts[ $i ] : '';
							}
						}

						if ( ! empty( $row_data ) ) {
							// Convert label keys → ACF field names (export now uses labels).
							$row_by_name = array();
							foreach ( $row_data as $parsed_key => $parsed_val ) {
								$acf_name    = $parsed_key;
								$matched_sub = null;
								foreach ( $sub_fields as $sub ) {
									if ( $sub['label'] === $parsed_key || $sub['name'] === $parsed_key ) {
										$acf_name    = $sub['name'];
										$matched_sub = $sub;
										break;
									}
								}
								$row_by_name[ $acf_name ] = $this->resolve_sub_field_value( $parsed_val, $matched_sub );
							}
							$repeater_fields[ $field_key ][] = $row_by_name;
						}
					}
					continue;
				}

				// Non-repeater fields: read raw/sep normally.
				$raw_value   = is_array( $entry ) && isset( $entry['raw'] ) ? $entry['raw'] : ( is_array( $entry ) ? '' : $entry );
				$sep         = is_array( $entry ) && isset( $entry['sep'] ) ? $entry['sep'] : '|';
				$parent      = ( $field_obj && ! empty( $field_obj['parent'] ) ) ? $field_obj['parent'] : '';
				$parent_obj  = ( $parent && function_exists( 'acf_get_field' ) ) ? acf_get_field( $parent ) : false;
				$parent_type = ( $parent_obj && isset( $parent_obj['type'] ) ) ? $parent_obj['type'] : '';

				if ( 'group' === $type ) {
					// Cell format: "subfield_name\xa0: value\nsubfield_name\xa0: value..."
					$sub_fields = ( $field_obj && ! empty( $field_obj['sub_fields'] ) ) ? $field_obj['sub_fields'] : array();
					$group_data = array();
					$lines      = preg_split( '/\r?\n/', $raw_value );
					foreach ( $lines as $line ) {
						$line = trim( $line );
						if ( '' === $line ) continue;
						if ( preg_match( '/^(.+?)\s*:\s*(.*)$/', $line, $m ) ) {
							$group_data[ trim( $m[1] ) ] = trim( $m[2] );
						}
					}
					// Fallback positional if no name:value pairs found.
					if ( empty( $group_data ) && ! empty( $sub_fields ) ) {
						$parts = array_map( 'trim', explode( ',', $raw_value ) );
						foreach ( $sub_fields as $i => $sub ) {
							$group_data[ $sub['name'] ] = isset( $parts[ $i ] ) ? $parts[ $i ] : '';
						}
					}
					if ( ! empty( $group_data ) ) {
						// Convert label keys → ACF field names.
						$group_by_name = array();
						foreach ( $group_data as $parsed_key => $parsed_val ) {
							$acf_name  = $parsed_key;
							$matched_sub = null;
							foreach ( $sub_fields as $sub ) {
								if ( $sub['label'] === $parsed_key || $sub['name'] === $parsed_key ) {
									$acf_name    = $sub['name'];
									$matched_sub = $sub;
									break;
								}
							}
							$group_by_name[ $acf_name ] = $this->resolve_sub_field_value( $parsed_val, $matched_sub );
						}
						update_field( $field_key, $group_by_name, $post_id );
					}
					continue;
				}

				$processed = $this->process_acf_value( $raw_value, $type, $field_obj, $sep );

				if ( 'group' === $parent_type ) {
					$sub_name = $field_obj['name'];
					$group_fields[ $parent ][ $sub_name ] = $processed;
				} else {
					$root_fields[ $field_key ] = $processed;
				}
			}

			foreach ( $root_fields as $field_key => $value ) {
				if ( null === $value ) continue;
				update_field( $field_key, $value, $post_id );
			}

			foreach ( $group_fields as $group_key => $sub_values ) {
				$existing_group = get_field( $group_key, $post_id );
				if ( is_array( $existing_group ) ) {
					$sub_values = array_merge( $existing_group, $sub_values );
				}
				update_field( $group_key, $sub_values, $post_id );
			}

			// Save repeater: each entry in $repeater_fields[$key] is already one row array.
			foreach ( $repeater_fields as $repeater_key => $rows ) {
				// Filter out fully-empty rows.
				$rows = array_values( array_filter( $rows, function( $row ) {
					return ! empty( array_filter( (array) $row, function( $v ) { return '' !== trim( (string) $v ); } ) );
				} ) );
				if ( ! empty( $rows ) ) {
					update_field( $repeater_key, $rows, $post_id );
				}
			}

		} elseif ( ! empty( $acf_values ) ) {
			foreach ( $acf_values as $field_key => $raw_value ) {
				update_post_meta( $post_id, $field_key, $raw_value );
			}
		}

		return $post_id;
	}

	// ── Featured Image resolver ───────────────────────────────────────────────

	/**
	 * Resolve a URL string to a WordPress attachment ID.
	 *
	 * Strategy (mirrors the export: URL ↔ wp_get_attachment_url):
	 *  1. attachment_url_to_postid() — exact URL match in the media library.
	 *  2. Fallback: strip query string and try again (some CDN/cache URLs).
	 *
	 * No sideload: the image must already exist in the media library.
	 * This keeps the import idempotent and side-effect-free.
	 *
	 * @param string $url  Absolute URL as exported by the exporter.
	 * @return int         Attachment ID, or 0 if not found.
	 */
	protected function resolve_featured_image( $value ) {
		$value = trim( $value );
		if ( '' === $value ) return 0;

		// Query _wp_attached_file directly (relative path stored by WordPress).
		// More reliable than attachment_url_to_postid() which reconstructs the
		// full URL and fails on HTTP/HTTPS mismatches or domain changes.
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- No WP API exists to query postmeta by value; result varies per import row so caching is not meaningful.
		$id = (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT post_id FROM {$wpdb->postmeta}
			 WHERE meta_key = '_wp_attached_file'
			 AND meta_value = %s
			 LIMIT 1",
			$value
		) );

		return $id;
	}

	// ── ACF value processing ──────────────────────────────────────────────────

	protected function process_acf_value( $raw, $type, $field_obj, $sep = '|' ) {
		switch ( $type ) {

			case 'checkbox':
				$choices = ( $field_obj && ! empty( $field_obj['choices'] ) ) ? $field_obj['choices'] : array();
				return $this->resolve_choice_values( $raw, $choices, $sep );

			case 'select':
				$choices  = ( $field_obj && ! empty( $field_obj['choices'] ) ) ? $field_obj['choices'] : array();
				$values   = $this->resolve_choice_values( $raw, $choices, $sep );
				$multiple = $field_obj && ! empty( $field_obj['multiple'] );
				return $multiple ? $values : ( ! empty( $values ) ? $values[0] : null );

			case 'radio':
			case 'button_group':
				$choices = ( $field_obj && ! empty( $field_obj['choices'] ) ) ? $field_obj['choices'] : array();
				$values  = $this->resolve_choice_values( $raw, $choices, $sep );
				return ! empty( $values ) ? $values[0] : null;

			case 'number':
			case 'range':
				return $this->to_float( $raw );

			case 'relationship':
			case 'post_object':
				$allowed_post_types = array();
				if ( $field_obj && ! empty( $field_obj['post_type'] ) ) {
					$allowed_post_types = (array) $field_obj['post_type'];
				}
				$ids = $this->resolve_relationship_values( $raw, $allowed_post_types, $sep );
				if ( 'post_object' === $type ) {
					$multiple = $field_obj && ! empty( $field_obj['multiple'] );
					return $multiple ? $ids : ( ! empty( $ids ) ? $ids[0] : null );
				}
				return $ids;

			case 'flexible_content':
				// Exported as a JSON string — decode and pass the array/value directly
				// to update_field() so ACF can reconstruct the layouts properly.
				if ( '' === trim( $raw ) ) return null;
				$decoded = json_decode( $raw, true );
				return ( JSON_ERROR_NONE === json_last_error() ) ? $decoded : $raw;

			// Note: 'repeater' is handled earlier in create_post(), before this
			// method is ever called for that type — see the __repeater_rows
			// handling near the top of the ACF fields loop.

			// Image / file → ACF attend un attachment ID.
			// L'export produit une URL absolue ; on la résout en ID via _wp_attached_file.
			case 'image':
			case 'file':
				return $this->resolve_attachment_by_url( $raw );

			case 'gallery':
				$ids = array();
				foreach ( explode( $sep, $raw ) as $url ) {
					$url = trim( $url );
					if ( '' === $url ) continue;
					$id = $this->resolve_attachment_by_url( $url );
					if ( $id ) $ids[] = $id;
				}
				return ! empty( $ids ) ? $ids : null;

			case 'google_map':
				// New format from exporter: "lat|lng|address"
				// Old format (pre v4.2.7): "address, lat, lng" (last two parts are coords)
				if ( '' === trim( $raw ) ) return null;
				if ( strpos( $raw, '|' ) !== false ) {
					// New pipe format.
					$parts = explode( '|', $raw, 3 );
					return array(
						'lat'     => isset( $parts[0] ) ? trim( $parts[0] ) : '',
						'lng'     => isset( $parts[1] ) ? trim( $parts[1] ) : '',
						'address' => isset( $parts[2] ) ? trim( $parts[2] ) : '',
					);
				}
				// JSON fallback.
				$decoded = json_decode( $raw, true );
				if ( is_array( $decoded ) && isset( $decoded['lat'] ) ) return $decoded;
				// Old comma format: last two comma-segments are lat, lng; rest is address.
				$parts = array_map( 'trim', explode( ',', $raw ) );
				if ( count( $parts ) >= 2 ) {
					$lng     = array_pop( $parts );
					$lat     = array_pop( $parts );
					$address = implode( ', ', $parts );
					// Validate: coordinates are numeric (allow negative values).
					if ( preg_match( '/^-?\d+(\.\d+)?$/', $lat ) && preg_match( '/^-?\d+(\.\d+)?$/', $lng ) ) {
						return array( 'lat' => $lat, 'lng' => $lng, 'address' => $address );
					}
				}
				return null;

			default:
				return $raw;
		}
	}

	/**
	 * Resolve an attachment URL (absolute or relative path) to its WordPress attachment ID.
	 *
	 * The exporter writes absolute URLs for ACF image/file fields (wp_get_attachment_url).
	 * We strip the uploads base URL to obtain the relative path, then query _wp_attached_file
	 * directly — the same robust approach used for the featured image.
	 *
	 * @param string $value  Absolute URL or relative path (e.g. 2025/01/photo.jpg).
	 * @return int  Attachment ID, or 0 if not found.
	 */
	protected function resolve_attachment_by_url( $value ) {
		$value = trim( $value );
		if ( '' === $value ) return 0;

		// If it looks like an absolute URL, strip the uploads base URL to get the relative path.
		if ( filter_var( $value, FILTER_VALIDATE_URL ) ) {
			$upload_dir   = wp_upload_dir();
			$base_url     = trailingslashit( $upload_dir['baseurl'] );
			if ( 0 === strpos( $value, $base_url ) ) {
				$value = substr( $value, strlen( $base_url ) );
			} else {
				// Try stripping just the path component (handles HTTP↔HTTPS or domain mismatches).
				$base_path = wp_parse_url( $base_url, PHP_URL_PATH );
				$val_path  = wp_parse_url( $value,    PHP_URL_PATH );
				if ( $base_path && $val_path && 0 === strpos( $val_path, $base_path ) ) {
					$value = substr( $val_path, strlen( $base_path ) );
				}
			}
		}

		// Query _wp_attached_file directly with the relative path.
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- No WP API exists to query postmeta by value; result varies per import row so caching is not meaningful.
		$id = (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT post_id FROM {$wpdb->postmeta}
			 WHERE meta_key = '_wp_attached_file'
			 AND meta_value = %s
			 LIMIT 1",
			$value
		) );

		return $id;
	}

	protected function resolve_relationship_values( $raw, $allowed_post_types = array(), $sep = '|' ) {
		$ids        = array();
		$post_types = ! empty( $allowed_post_types )
			? $allowed_post_types
			: get_post_types( array( 'public' => true ), 'names' );

		foreach ( explode( $sep, $raw ) as $title ) {
			$title = trim( $title );
			if ( '' === $title ) continue;

			$query = new \WP_Query( array(
				'post_type'              => array_values( $post_types ),
				'title'                  => $title,
				'post_status'            => 'publish',
				'posts_per_page'         => 1,
				'fields'                 => 'ids',
				'no_found_rows'          => true,
				'update_post_meta_cache' => false,
				'update_post_term_cache' => false,
			) );

			if ( ! empty( $query->posts ) ) {
				$ids[] = (int) $query->posts[0];
				continue;
			}

				foreach ( $post_types as $pt ) {
				$fallback_q = new \WP_Query( array(
					'post_type'              => $pt,
					'title'                  => $title,
					'post_status'            => 'publish',
					'posts_per_page'         => 1,
					'fields'                 => 'ids',
					'no_found_rows'          => true,
					'update_post_meta_cache' => false,
					'update_post_term_cache' => false,
				) );
				if ( ! empty( $fallback_q->posts ) ) {
					$ids[] = (int) $fallback_q->posts[0];
					break;
				}
			}
		}

		return $ids;
	}

	protected function resolve_choice_values( $raw, $choices, $sep = '|' ) {
		$resolved     = array();
		$lower_keys   = array();
		$lower_labels = array();
		foreach ( $choices as $key => $label ) {
			$lower_keys[ strtolower( (string) $key ) ]     = (string) $key;
			$lower_labels[ strtolower( (string) $label ) ] = (string) $key;
		}

		foreach ( explode( $sep, $raw ) as $item ) {
			$item = trim( $item );
			if ( '' === $item ) continue;

			if ( empty( $choices ) ) {
				$resolved[] = $item;
				continue;
			}

			$lc = strtolower( $item );

			if ( isset( $choices[ $item ] ) ) {
				$resolved[] = $item;
			} elseif ( isset( $lower_keys[ $lc ] ) ) {
				$resolved[] = $lower_keys[ $lc ];
			} elseif ( isset( $lower_labels[ $lc ] ) ) {
				$resolved[] = $lower_labels[ $lc ];
			}
		}

		return $resolved;
	}

	/**
	 * Resolve a Group/Repeater sub-field's parsed "Label : Value" cell text
	 * back to the value(s) ACF actually expects to store, for choice-based
	 * sub-field types (select/checkbox/radio/button_group). Export writes the
	 * human-readable label(s) there (see Spreadsheet_Exporter_Standard::
	 * format_group_value()/format_repeater_row()), joined by ", " for
	 * multi-value checkbox fields — without this, the raw label text would be
	 * stored as-is and no longer match any valid choice.
	 *
	 * Non-choice sub-field types are returned unchanged.
	 *
	 * @param string     $value_text Parsed cell text for this sub-field.
	 * @param array|null $sub_field  The ACF sub-field definition, if known.
	 * @return string|array Resolved value (array for checkbox, string otherwise).
	 */
	private function resolve_sub_field_value( $value_text, $sub_field ) {
		if ( ! $sub_field || empty( $sub_field['type'] ) ) return $value_text;
		if ( ! in_array( $sub_field['type'], array( 'select', 'checkbox', 'radio', 'button_group' ), true ) ) {
			return $value_text;
		}
		$choices = ! empty( $sub_field['choices'] ) ? $sub_field['choices'] : array();
		$resolved = $this->resolve_choice_values( $value_text, $choices, ',' );
		if ( 'checkbox' === $sub_field['type'] ) return $resolved;
		return ! empty( $resolved ) ? $resolved[0] : $value_text;
	}

	// ── Native XML reader ─────────────────────────────────────────────────────

	private function read_xml( $file_path ) {
		if ( ! file_exists( $file_path ) ) {
			throw new \RuntimeException( esc_html__( 'XML file not found.', 'ideo-field-porter' ) );
		}

		libxml_use_internal_errors( true );
		$xml = simplexml_load_file( $file_path );
		if ( ! $xml ) {
			throw new \RuntimeException( esc_html__( 'Unable to parse the XML file. Make sure it is a valid XML exported by this plugin.', 'ideo-field-porter' ) );
		}

		$rows    = array();
		$headers = array();

		// Each direct child of the root = one post record.
		foreach ( $xml->children() as $post_node ) {
			$row = array();
			foreach ( $post_node->children() as $tag => $value ) {
				// Build headers from the first record.
				if ( ! in_array( $tag, $headers, true ) ) {
					$headers[] = $tag;
				}
				$row[ $tag ] = (string) $value;
			}
			$rows[] = $row;
		}

		if ( empty( $rows ) ) {
			return array( array() ); // Empty file — just headers row
		}

		// Normalize to 2-D array with a header row (same format as xlsx/csv).
		$result = array( $headers );
		foreach ( $rows as $assoc_row ) {
			$flat = array();
			foreach ( $headers as $h ) {
				$flat[] = isset( $assoc_row[ $h ] ) ? $assoc_row[ $h ] : '';
			}
			$result[] = $flat;
		}

		return $result;
	}

	// ── Native CSV reader ─────────────────────────────────────────────────────

	private function read_csv( $file_path ) {
		if ( ! file_exists( $file_path ) ) {
			throw new \RuntimeException( esc_html__( 'CSV file not found.', 'ideo-field-porter' ) );
		}
		// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fopen -- WP_Filesystem::get_contents() loads the whole file into memory; fgetcsv() line-by-line streaming is required for large CSV imports.
		$handle = fopen( $file_path, 'r' );
		if ( ! $handle ) {
			throw new \RuntimeException( esc_html__( 'Unable to open the CSV file.', 'ideo-field-porter' ) );
		}

		// Read first line to detect BOM and delimiter.
		$first_line = fgets( $handle );

		// Strip UTF-8 BOM (EF BB BF) if present — Excel adds it, fgetcsv reads it as text.
		$bom      = "\xEF\xBB\xBF";
		$has_bom  = ( substr( $first_line, 0, 3 ) === $bom );
		if ( $has_bom ) {
			$first_line = substr( $first_line, 3 );
		}

		$delimiter = ( substr_count( $first_line, ';' ) >= substr_count( $first_line, ',' ) ) ? ';' : ',';

		// If the file has a UTF-8 BOM it is definitely UTF-8 — skip encoding detection
		// to avoid mis-converting already-valid UTF-8 sequences (e.g. é → TÃ©).
		$encoding = 'UTF-8';
		if ( ! $has_bom && function_exists( 'mb_detect_encoding' ) ) {
			$detected = mb_detect_encoding( $first_line, array( 'UTF-8', 'ISO-8859-1', 'Windows-1252' ), true );
			if ( $detected && 'UTF-8' !== $detected ) {
				$encoding = $detected;
			}
		}

		// Parse the first line manually (already read, BOM stripped).
		$rows = array();
		$first_row = str_getcsv( $first_line, $delimiter );
		if ( 'UTF-8' !== $encoding && function_exists( 'mb_convert_encoding' ) ) {
			$first_row = array_map( function( $cell ) use ( $encoding ) {
				return mb_convert_encoding( $cell, 'UTF-8', $encoding );
			}, $first_row );
		}
		if ( array_filter( $first_row, function( $v ) { return '' !== trim( (string) $v ); } ) ) {
			$rows[] = $first_row;
		}

		// Read remaining lines normally.
		while ( ( $row = fgetcsv( $handle, 0, $delimiter ) ) !== false ) {
			if ( 'UTF-8' !== $encoding && function_exists( 'mb_convert_encoding' ) ) {
				$row = array_map( function( $cell ) use ( $encoding ) {
					return mb_convert_encoding( $cell, 'UTF-8', $encoding );
				}, $row );
			}
			if ( array_filter( $row, function( $v ) { return '' !== trim( (string) $v ); } ) ) {
				$rows[] = $row;
			}
		}
		// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose -- Closing the file handle opened above with fopen.
		fclose( $handle );
		return $rows;
	}

	// ── Native XLSX reader ────────────────────────────────────────────────────

	private function read_xlsx( $file_path ) {
		if ( ! class_exists( 'ZipArchive' ) ) {
			throw new \RuntimeException( esc_html__( 'The PHP ZipArchive extension is required but not installed.', 'ideo-field-porter' ) );
		}

		$zip = new \ZipArchive();
		if ( true !== $zip->open( $file_path ) ) {
			throw new \RuntimeException( esc_html__( 'Unable to open the .xlsx file (invalid ZIP archive).', 'ideo-field-porter' ) );
		}

		$shared_strings = array();
		$ss_index       = $zip->locateName( 'xl/sharedStrings.xml' );
		if ( false !== $ss_index ) {
			$ss_xml = $zip->getFromIndex( $ss_index );
			if ( $ss_xml ) {
				$shared_strings = $this->parse_shared_strings( $ss_xml );
			}
		}

		$sheet_file = $this->resolve_first_sheet( $zip );
		$sheet_xml  = $zip->getFromName( $sheet_file );
		$zip->close();

		if ( ! $sheet_xml ) {
			throw new \RuntimeException( esc_html__( 'Unable to read the first sheet of the workbook.', 'ideo-field-porter' ) );
		}

		return $this->parse_sheet( $sheet_xml, $shared_strings );
	}

	protected function parse_shared_strings( $xml_content ) {
		$strings = array();
		$xml     = @simplexml_load_string( $this->strip_namespaces( $xml_content ) );
		if ( ! $xml ) return $strings;

		foreach ( $xml->si as $si ) {
			if ( isset( $si->t ) ) { $strings[] = (string) $si->t; continue; }
			$text = '';
			if ( isset( $si->r ) ) { foreach ( $si->r as $r ) { if ( isset( $r->t ) ) $text .= (string) $r->t; } }
			$strings[] = $text;
		}
		return $strings;
	}

	private function resolve_first_sheet( $zip ) {
		$fallback = 'xl/worksheets/sheet1.xml';
		$wb_xml   = $zip->getFromName( 'xl/workbook.xml' );
		$rels_xml = $zip->getFromName( 'xl/_rels/workbook.xml.rels' );
		if ( ! $wb_xml || ! $rels_xml ) return $fallback;

		$wb   = @simplexml_load_string( $this->strip_namespaces( $wb_xml ) );
		$rels = @simplexml_load_string( $this->strip_namespaces( $rels_xml ) );
		if ( ! $wb || ! $rels || ! isset( $wb->sheets->sheet ) ) return $fallback;

		$attrs = $wb->sheets->sheet->attributes();
		$r_id  = isset( $attrs['id'] ) ? (string) $attrs['id'] : '';
		if ( '' === $r_id && isset( $attrs['r:id'] ) ) $r_id = (string) $attrs['r:id'];

		foreach ( $rels->Relationship as $rel ) {
			$ra = $rel->attributes();
			if ( isset( $ra['Id'] ) && (string) $ra['Id'] === $r_id ) {
				$target = (string) $ra['Target'];
				return ( '/' === substr( $target, 0, 1 ) ) ? ltrim( $target, '/' ) : 'xl/' . $target;
			}
		}
		return $fallback;
	}

	protected function parse_sheet( $xml_content, $shared_strings ) {
		$xml = @simplexml_load_string( $this->strip_namespaces( $xml_content ) );
		if ( ! $xml ) return array();

		$grid = array();
		foreach ( $xml->sheetData->row as $row_node ) {
			$row_idx = (int) $row_node->attributes()['r'] - 1;
			foreach ( $row_node->c as $cell ) {
				$ca        = $cell->attributes();
				$cell_type = isset( $ca['t'] ) ? (string) $ca['t'] : '';
				$indexes   = $this->cell_ref_to_indexes( isset( $ca['r'] ) ? (string) $ca['r'] : '' );
				$raw       = isset( $cell->v ) ? (string) $cell->v : '';

				if ( 's' === $cell_type ) {
					$value = isset( $shared_strings[ (int) $raw ] ) ? $shared_strings[ (int) $raw ] : '';
				} elseif ( 'inlineStr' === $cell_type ) {
					$value = isset( $cell->is->t ) ? (string) $cell->is->t : $raw;
				} else {
					$value = $raw;
				}
				$grid[ $row_idx ][ $indexes[0] ] = $value;
			}
		}

		if ( empty( $grid ) ) return array();

		$max_col = 0;
		foreach ( $grid as $row ) {
			$m = max( array_keys( $row ) );
			if ( $m > $max_col ) $max_col = $m;
		}

		$rows = array();
		ksort( $grid );
		foreach ( $grid as $row ) {
			$normalized = array();
			for ( $c = 0; $c <= $max_col; $c++ ) $normalized[] = isset( $row[ $c ] ) ? $row[ $c ] : '';
			$rows[] = $normalized;
		}
		return $rows;
	}

	protected function cell_ref_to_indexes( $ref ) {
		preg_match( '/^([A-Z]+)(\d+)$/', strtoupper( $ref ), $m );
		if ( ! $m ) return array( 0, 0 );
		$col = 0;
		foreach ( str_split( $m[1] ) as $ch ) $col = $col * 26 + ( ord( $ch ) - 64 );
		return array( $col - 1, (int) $m[2] - 1 );
	}

	protected function strip_namespaces( $xml ) {
		$xml = preg_replace( '/\s+xmlns(?::\w+)?="[^"]*"/', '', $xml );
		$xml = preg_replace( '/<(\/?)\w+:/', '<$1', $xml );
		return $xml;
	}

	protected function to_float( $value ) {
		if ( null === $value || '' === (string) $value ) return null;
		$n = filter_var( str_replace( ',', '.', $value ), FILTER_VALIDATE_FLOAT );
		return ( false !== $n ) ? $n : null;
	}
}
