<?php
namespace IdeoFieldPorter;

defined( 'ABSPATH' ) || exit;

/**
 * Spreadsheet_Exporter
 *
 * Exports all published posts of a given CPT to .xlsx or .csv.
 * Columns: post title, featured image URL, taxonomies, then all ACF fields (in field group order).
 * Relationship / post_object fields are resolved to the linked post's title.
 * No external library required (native ZipArchive + SimpleXML for XLSX).
 */
class Spreadsheet_Exporter_Standard {

	private $sub_label_map = array(); // path_key => custom_label for sub-field renames

	/**
	 * Column indices (0-based, matching the exported header/row order) whose
	 * cells must always be written as text in XLSX, even when the content
	 * happens to look numeric (e.g. an ACF text field holding "1.1" or a
	 * version string) — otherwise Excel stores it as a real number and
	 * re-displays it using the user's locale decimal separator (e.g. "1,1"
	 * in French locales), silently corrupting the value from the user's
	 * point of view. Only genuinely numeric sources (Post ID, ACF
	 * number/range fields) are left out of this set.
	 *
	 * @var array<int,true>
	 */
	private $force_text_columns = array();

	/**
	 * When true, multi-value cells (taxonomy/checkbox/select/relationship/
	 * post_object/gallery) come back as arrays instead of separator-joined
	 * strings, and repeater columns collapse to a single non-expanded column
	 * listing every iteration (numbered once there's more than one) instead
	 * of one column per iteration — see get_preview_data(). Lets the live
	 * editable preview table re-join values client-side on a separator
	 * change without a round-trip.
	 *
	 * @var bool
	 */
	private $raw_mode = false;

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

	/**
	 * Stream a spreadsheet file to the browser and exit.
	 *
	 * @param string     $post_type
	 * @param string     $format          'xlsx', 'csv', or 'xml'
	 * @param array|null $columns         Ordered list of [ 'key' => ..., 'label' => ... ] from the UI.
	 * @param array      $separators      Per-column export separator overrides.
	 */
	/** @var array Per-field-key separator override for export. */
	public $export_separators = array();

	public function export( $post_type, $format = 'xlsx', $columns = null, $separators = array() ) {
		$this->export_separators = $separators;
		$post_type_obj = get_post_type_object( $post_type );
		$label         = $post_type_obj ? sanitize_title( $post_type_obj->labels->name ) : $post_type;

		// Build sub_label_map from selected columns' sub_labels.
		$this->sub_label_map = array();
		if ( is_array( $columns ) ) {
			foreach ( $columns as $col ) {
				if ( ! empty( $col['sub_labels'] ) && is_array( $col['sub_labels'] ) ) {
					foreach ( $col['sub_labels'] as $path_key => $custom_label ) {
						$this->sub_label_map[ $path_key ] = $custom_label;
					}
				}
			}
		}

		$filename = $label . '-export-' . gmdate( 'Y-m-d' );
		$data     = $this->build_data( $post_type, $columns );
		if ( 'csv' === $format ) {
			$this->stream_csv( $data, $filename );
		} elseif ( 'xml' === $format ) {
			$this->stream_xml( $data, $filename );
		} else {
			$this->stream_xlsx( $data, $filename );
		}
	}

	/**
	 * Public wrapper so Admin_Page can retrieve the field list for the column picker.
	 */
	public function get_acf_fields_public( $post_type ) {
		return $this->get_acf_fields( $post_type );
	}

	// ── Data builder ─────────────────────────────────────────────────────────

	/**
	 * Determine which column indices must be forced to text in XLSX (see
	 * $force_text_columns doc comment) for a given ordered column list.
	 *
	 * @param array $expanded_columns Final ordered column list (post-repeater-expansion).
	 * @param array $acf_fields_by_key ACF field definitions indexed by field key.
	 * @return array<int,true>
	 */
	private function compute_force_text_columns( $expanded_columns, $acf_fields_by_key ) {
		$force_text = array();
		$numeric_types = array( 'number', 'range' );
		foreach ( $expanded_columns as $idx => $col ) {
			$key = $col['key'];
			if ( '__post_id__' === $key ) continue; // genuinely numeric, fine as a real number
			if ( isset( $acf_fields_by_key[ $key ] ) && in_array( $acf_fields_by_key[ $key ]['type'], $numeric_types, true ) ) {
				continue; // genuine ACF Number/Range field, fine as a real number
			}
			$force_text[ $idx ] = true;
		}
		return $force_text;
	}

	/**
	 * Build a 2-D array (header row + data rows) for the given CPT.
	 *
	 * @param string     $post_type
	 * @param array|null $columns   Ordered/filtered column list from the UI, or null for all.
	 * @return array
	 */
	/**
	 * Return preview data as a PHP array — headers + first $limit data rows.
	 */
	public function get_preview_data( $post_type, $columns = null, $limit = 10, $extra_query_args = array(), $raw = false ) {
		$this->raw_mode = $raw;
		// Reuse build_data but limit posts first.
		$data    = $this->build_data( $post_type, $columns, $limit, $extra_query_args );
		$headers = array_shift( $data ); // first row = headers
		// found_posts from a 1-row query — a real COUNT, not a full ID dump just to count() it.
		$_total_args = array_merge( array(
			'post_type'      => $post_type,
			'post_status'    => 'publish',
			'posts_per_page' => 1,
			'fields'         => 'ids',
			'no_found_rows'  => false,
		), $extra_query_args );
		$total = (int) ( new \WP_Query( $_total_args ) )->found_posts;
		// Flatten cells to strings, except raw_mode multi-value cells which
		// stay as arrays for the JS to re-join client-side (never (string) an array).
		$rows = array();
		foreach ( $data as $row ) {
			$rows[] = array_map( function( $v ) { return is_array( $v ) ? $v : (string) $v; }, $row );
		}
		$header_color = \IdeoFieldPorter\Settings_Page::DEFAULT_COLOR;
		// Settings_Page::lighten_color() blends toward white using the OPPOSITE
		// convention of Spreadsheet_Exporter::lighten_color() (amount of white to
		// ADD, vs. ratio of original colour to KEEP) — 0.90 here matches the same
		// "mostly white" alternating-row tint as the Pro Hierarchical exporter's 0.10.
		$light_color  = \IdeoFieldPorter\Settings_Page::lighten_color( $header_color, 0.90 );
		// Each post = exactly 1 row in standard mode.
		$post_row_counts = array_fill( 0, count( $rows ), 1 );
		return array(
			'header_rows'     => array( array_map( function($h) { return array('label'=>$h,'colspan'=>1,'rowspan'=>1); }, $headers ) ),
			'rows'            => $rows,
			'total'           => $total,
			'showing'         => count( $rows ),
			'post_count'      => count( $rows ),
			'post_row_counts' => $post_row_counts,
			'color'           => $header_color,
			'light_color'     => $light_color,
		);
	}

	private function build_data( $post_type, $columns = null, $limit = -1, $extra_query_args = array() ) {
		// Index ACF fields by key for quick lookup.
		$acf_fields_by_key = array();
		if ( function_exists( 'acf_get_field_groups' ) ) {
			foreach ( $this->get_acf_fields( $post_type ) as $f ) {
				$acf_fields_by_key[ $f['key'] ] = $f;
			}
		}

		// Index taxonomies by their __tax_{name}__ key.
		$taxonomies_by_key = array();
		foreach ( $this->get_taxonomies( $post_type ) as $tax ) {
			$taxonomies_by_key[ '__tax_' . $tax->name . '__' ] = $tax;
		}

		// If no column selection was passed, build the default ordered list.
		if ( empty( $columns ) ) {
			$columns = array();
			// Raw mode (the live-table preview) always fetches every column,
			// including ID, so its row arrays line up with the full schema
			// (get_export_columns_standard(), which also leads with ID) — the
			// JS table itself decides what's shown/excluded. The real export's
			// own no-selection fallback deliberately leaves ID out, unchanged.
			if ( $this->raw_mode ) {
				$columns[] = array( 'key' => '__post_id__', 'label' => 'ID' );
			}
			$columns[] = array( 'key' => '__title__', 'label' => 'Title' );
			foreach ( $taxonomies_by_key as $k => $tax ) {
				$columns[] = array( 'key' => $k, 'label' => $tax->labels->singular_name );
			}
			foreach ( $acf_fields_by_key as $k => $field ) {
				$columns[] = array( 'key' => $k, 'label' => $field['label'] );
			}
			$columns[] = array( 'key' => '__featured_image__', 'label' => 'Featured Image' );
		}

		// Build header row from the (ordered, filtered) column list.
		$headers = array();
		foreach ( $columns as $col ) {
			$headers[] = $col['label'];
		}
		$rows = array( $headers );

		// Fetch published posts (limited for preview, all for export).
		$_posts_args = array_merge( array(
			'post_type'              => $post_type,
			'post_status'            => 'publish',
			'posts_per_page'         => $limit > 0 ? $limit : -1,
			'orderby'                => 'title',
			'order'                  => 'ASC',
			'no_found_rows'          => true,
			'update_post_term_cache' => false,
		), $extra_query_args );

		// First pass: determine max repeater row counts across all posts,
		// so we can expand repeater columns to the right number before building headers.
		// Batched (like the row-building pass below) with the post cache cleaned
		// after each batch — otherwise WordPress's per-request meta cache keeps
		// every post touched so far in memory, and a large post type still runs
		// out of memory well before reaching the end, just more gradually than
		// a single unbounded query would.
		$repeater_keys_in_columns = array();
		foreach ( $columns as $col ) {
			if ( isset( $acf_fields_by_key[ $col['key'] ] )
				&& 'repeater' === $acf_fields_by_key[ $col['key'] ]['type'] ) {
				$repeater_keys_in_columns[] = $col['key'];
			}
		}
		$repeater_max_rows = array(); // field_key => max_count
		if ( ! empty( $repeater_keys_in_columns ) ) {
			$this->foreach_posts_batched( $_posts_args, function( $batch ) use ( $repeater_keys_in_columns, &$repeater_max_rows ) {
				foreach ( $batch as $post ) {
					foreach ( $repeater_keys_in_columns as $rep_key ) {
						// Use formatted value (true) for repeaters: ACF returns the rows array correctly.
						$val = function_exists( 'get_field' ) ? get_field( $rep_key, $post->ID ) : array();
						if ( is_array( $val ) ) {
							$repeater_max_rows[ $rep_key ] = max( isset( $repeater_max_rows[ $rep_key ] ) ? $repeater_max_rows[ $rep_key ] : 0, count( $val ) );
						}
					}
				}
			} );
		}

		// Rebuild headers expanding repeater columns to #1, #2, … — except in
		// raw_mode, where the live preview table has already built its header
		// from a static schema (one entry per repeater field, not per
		// instance): collapse to a single column (first occurrence only)
		// instead, so each row keeps exactly one cell per schema column. The
		// real (non-raw) export is untouched.
		$expanded_columns = array();
		foreach ( $columns as $col ) {
			if ( isset( $acf_fields_by_key[ $col['key'] ] )
				&& 'repeater' === $acf_fields_by_key[ $col['key'] ]['type'] ) {
				if ( $this->raw_mode ) {
					$expanded_columns[] = array_merge( $col, array( '_repeater_collapsed' => true ) );
					continue;
				}
				$max = isset( $repeater_max_rows[ $col['key'] ] ) ? $repeater_max_rows[ $col['key'] ] : 0;
				for ( $n = 1; $n <= max( $max, 1 ); $n++ ) {
					$expanded_columns[] = array_merge( $col, array(
						'label'          => $col['label'] . ' #' . $n,
						'_repeater_idx'  => $n - 1,
					) );
				}
			} else {
				$expanded_columns[] = $col;
			}
		}

		// Replace header row with expanded one.
		$headers = array();
		foreach ( $expanded_columns as $col ) {
			$headers[] = $col['label'];
		}
		$rows = array( $headers );

		$this->force_text_columns = $this->compute_force_text_columns( $expanded_columns, $acf_fields_by_key );

		$this->foreach_posts_batched( $_posts_args, function( $batch ) use ( &$rows, $expanded_columns, $taxonomies_by_key, $acf_fields_by_key ) {
			foreach ( $batch as $post ) {
				$row = array();
				foreach ( $expanded_columns as $col ) {
					$key = $col['key'];

					if ( '__post_id__' === $key ) {
						$row[] = (string) $post->ID;

					} elseif ( '__title__' === $key ) {
						$row[] = $post->post_title;

					} elseif ( '__featured_image__' === $key ) {
						$thumb_id = get_post_thumbnail_id( $post->ID );
						if ( $thumb_id ) {
							$relative = get_post_meta( $thumb_id, '_wp_attached_file', true );
							$row[]    = $relative ? self::format_media_url_from_relative( $relative ) : '';
						} else {
							$row[] = '';
						}

					} elseif ( isset( $taxonomies_by_key[ $key ] ) ) {
						$tax   = $taxonomies_by_key[ $key ];
						$terms = get_the_terms( $post->ID, $tax->name );
						$names = ( $terms && ! is_wp_error( $terms ) ) ? wp_list_pluck( $terms, 'name' ) : array();
						if ( $this->raw_mode ) {
							$row[] = $names;
						} else {
							$_tsep  = isset( $this->export_separators[ $key ] ) ? $this->export_separators[ $key ] : '|';
							$_tglue = ',' === $_tsep ? $_tsep . ' ' : ' ' . $_tsep . ' ';
							$row[]  = implode( $_tglue, $names );
						}

					} elseif ( isset( $acf_fields_by_key[ $key ] )
						&& 'group' === $acf_fields_by_key[ $key ]['type'] ) {
						$field_def  = $acf_fields_by_key[ $key ];
						$group_data = function_exists( 'get_field' ) ? get_field( $key, $post->ID ) : array();
						$row[]      = $this->format_group_value( $group_data, $field_def );

					} elseif ( isset( $acf_fields_by_key[ $key ] )
						&& 'repeater' === $acf_fields_by_key[ $key ]['type']
						&& ! empty( $col['_repeater_collapsed'] ) ) {
						// raw_mode only: the live preview's header schema has one
						// static column per repeater field (see the
						// header-expansion comment above), so every iteration has
						// to share that single cell — numbered once there's more
						// than one, so e.g. 2 phone numbers don't read as just 1.
						// "#1", "#2"... matches the real export's expanded column
						// labels ("Phones #1", "Phones #2"...) so the numbering
						// reads the same whether you're looking at the preview or
						// the downloaded file.
						$field_def = $acf_fields_by_key[ $key ];
						$all_rows  = function_exists( 'get_field' ) ? get_field( $key, $post->ID ) : array();
						$formatted = array();
						if ( is_array( $all_rows ) ) {
							foreach ( $all_rows as $n => $rep_row ) {
								$line = $this->format_repeater_row( $rep_row, $field_def );
								if ( '' === $line ) continue;
								$formatted[] = ( count( $all_rows ) > 1 ? '#' . ( $n + 1 ) . ' ' : '' ) . $line;
							}
						}
						$row[] = implode( "\n", $formatted );

					} elseif ( isset( $acf_fields_by_key[ $key ] )
						&& 'repeater' === $acf_fields_by_key[ $key ]['type']
						&& isset( $col['_repeater_idx'] ) ) {
						// One column per repeater iteration: sub-field values joined by ", ".
						$field_def = $acf_fields_by_key[ $key ];
						// Use formatted value for repeater so ACF resolves sub-field values correctly.
						$all_rows  = function_exists( 'get_field' ) ? get_field( $key, $post->ID ) : array();
						$idx       = $col['_repeater_idx'];
						if ( is_array( $all_rows ) && isset( $all_rows[ $idx ] ) ) {
							$row[] = $this->format_repeater_row( $all_rows[ $idx ], $field_def );
						} else {
							$row[] = '';
						}

					} elseif ( isset( $acf_fields_by_key[ $key ] ) ) {
						$row[] = $this->get_field_value( $post->ID, $acf_fields_by_key[ $key ] );

					} else {
						$row[] = '';
					}
				}
				$rows[] = $row;
			}
		} );

		return $rows;
	}

	/**
	 * Walk posts in bounded-size batches instead of fetching everything with
	 * `posts_per_page => -1`, calling $callback once per batch. WordPress
	 * caches every post/meta row it touches for the rest of the request — on
	 * a large post type, fetching all posts in one query (or even in several
	 * batched queries whose results are simply merged into one big array)
	 * still runs out of memory before the export finishes, just more
	 * gradually. Cleaning the post cache right after each batch is processed
	 * is what actually keeps memory bounded, which is why this method drives
	 * processing itself via a callback instead of returning one big array.
	 *
	 * @param array    $args      get_posts() args. 'paged' is overwritten; a
	 *                            stable secondary 'ID' sort is added so
	 *                            paging can't skip or repeat rows when the
	 *                            primary orderby has ties.
	 * @param callable $callback  Called once per batch as $callback( $batch ).
	 */
	private function foreach_posts_batched( $args, callable $callback ) {
		$batch_size = 500;
		$limit      = isset( $args['posts_per_page'] ) ? (int) $args['posts_per_page'] : -1;

		$primary_orderby       = isset( $args['orderby'] ) ? $args['orderby'] : 'date';
		$primary_order         = isset( $args['order'] ) ? $args['order'] : 'DESC';
		$args['orderby']       = is_array( $primary_orderby ) ? $primary_orderby : array( $primary_orderby => $primary_order );
		$args['orderby']['ID'] = 'ASC';
		unset( $args['order'] );

		$fetched = 0;
		$paged   = 1;
		do {
			$remaining = $limit > 0 ? ( $limit - $fetched ) : $batch_size;
			if ( $remaining <= 0 ) break;
			$per_page               = min( $batch_size, $remaining );
			$args['posts_per_page'] = $per_page;
			$args['paged']          = $paged;
			$batch = get_posts( $args );
			if ( empty( $batch ) ) break;

			$callback( $batch );

			foreach ( $batch as $post ) {
				clean_post_cache( $post );
			}

			$fetched += count( $batch );
			$paged++;
		} while ( count( $batch ) === $per_page );
	}

	// ── ACF field value reader ────────────────────────────────────────────────

	/**
	 * Read and format a single ACF field value for export.
	 *
	 * @param int   $post_id
	 * @param array $field  ACF field definition array.
	 * @return string
	 */
	private function get_field_value( $post_id, $field ) {
		$_field_key = isset( $field['key'] ) ? $field['key'] : '';
		$sep        = isset( $this->export_separators[ $_field_key ] ) ? $this->export_separators[ $_field_key ] : '|';
		if ( ! function_exists( 'get_field' ) ) {
			return get_post_meta( $post_id, $field['name'], true );
		}

		$type = isset( $field['type'] ) ? $field['type'] : '';

		$parent_key  = isset( $field['parent_key'] )  ? $field['parent_key']  : '';
		$parent_type = isset( $field['parent_type'] ) ? $field['parent_type'] : '';

		// group fields are now handled entirely in build_data() via format_group_value().
		$value = get_field( $field['key'], $post_id, false );

		// true_false's "off" state is boolean false, coming straight from ACF's
		// get_field() — that's meaningful data, not an absence of value, so it
		// must be handled before the generic empty-value short-circuit below
		// (which would otherwise blank it out and lose the explicit "No"). Same
		// fix as Spreadsheet_Exporter::format_value() (PRO's Hierarchical mode).
		if ( 'true_false' === $type ) return $value ? '1' : '0';

		if ( null === $value || '' === $value ) return '';
		if ( false === $value ) return '';

		switch ( $type ) {

			case 'wysiwyg':
			case 'textarea':
				// Strip HTML tags and decode entities for clean spreadsheet output.
				$clean = wp_strip_all_tags( (string) $value );
				return html_entity_decode( $clean, ENT_QUOTES | ENT_HTML5, 'UTF-8' );

			case 'checkbox':
			case 'select':
				$choices = isset( $field['choices'] ) ? $field['choices'] : array();
				$values  = is_array( $value ) ? $value : array( $value );
				$labels  = array();
				foreach ( $values as $v ) {
					$labels[] = isset( $choices[ $v ] ) ? $choices[ $v ] : $v;
				}
				if ( $this->raw_mode ) return $labels;
				return implode( ( ',' === $sep ? $sep . ' ' : ' ' . $sep . ' ' ), $labels );

			case 'radio':
			case 'button_group':
				$choices = isset( $field['choices'] ) ? $field['choices'] : array();
				return isset( $choices[ $value ] ) ? $choices[ $value ] : (string) $value;

			case 'relationship':
				if ( ! is_array( $value ) ) $value = array( $value );
				$titles = array();
				foreach ( $value as $related_id ) {
					$related_id = (int) $related_id;
					if ( $related_id > 0 ) {
						$related_post = get_post( $related_id );
						if ( $related_post ) $titles[] = $related_post->post_title;
					}
				}
				if ( $this->raw_mode ) return $titles;
				return implode( ( ',' === $sep ? $sep . ' ' : ' ' . $sep . ' ' ), $titles );

			case 'post_object':
				$ids    = is_array( $value ) ? $value : array( $value );
				$titles = array();
				foreach ( $ids as $related_id ) {
					$related_id = (int) $related_id;
					if ( $related_id > 0 ) {
						$related_post = get_post( $related_id );
						if ( $related_post ) $titles[] = $related_post->post_title;
					}
				}
				if ( $this->raw_mode ) return $titles;
				return implode( ( ',' === $sep ? $sep . ' ' : ' ' . $sep . ' ' ), $titles );

			// repeater: handled in build_data() via format_repeater_row().

			case 'flexible_content':
				// Raw (unformatted) value, JSON-encoded as-is — this exact shape is
				// what update_field() expects back on import, so it must round-trip
				// without reshaping (see Spreadsheet_Importer::process_acf_value()).
				return is_array( $value ) ? wp_json_encode( $value, JSON_UNESCAPED_UNICODE ) : '';

			// true_false: handled above, before the empty-value short-circuit.

			case 'user':
				$fmt = get_field( $field['key'], $post_id, true );
				if ( empty( $fmt ) ) return '';
				if ( is_array( $fmt ) && isset( $fmt['display_name'] ) ) return $fmt['display_name'];
				if ( is_array( $fmt ) && isset( $fmt[0] ) && is_array( $fmt[0] ) ) {
					return implode( ( ',' === $sep ? $sep . ' ' : ' ' . $sep . ' ' ), array_column( $fmt, 'display_name' ) );
				}
				return (string) $fmt;

			case 'taxonomy':
				$fmt = get_field( $field['key'], $post_id, true );
				if ( empty( $fmt ) ) return '';
				if ( is_array( $fmt ) ) {
					$names = array();
					foreach ( $fmt as $term ) {
						if ( is_object( $term ) ) $names[] = $term->name;
						elseif ( is_array( $term ) && isset( $term['name'] ) ) $names[] = $term['name'];
					}
					if ( $this->raw_mode ) return $names;
					return implode( ( ',' === $sep ? $sep . ' ' : ' ' . $sep . ' ' ), $names );
				}
				return is_object( $fmt ) ? $fmt->name : (string) $fmt;

			case 'link':
			case 'page_link':
				if ( is_array( $value ) ) return isset( $value['url'] ) ? $value['url'] : '';
				return (string) $value;

			// Image / file → URL.
			case 'image':
			case 'file':
				if ( is_array( $value ) ) {
					return isset( $value['url'] ) ? self::format_media_url( $value['url'] ) : '';
				}
				if ( is_numeric( $value ) ) {
					$rel_std = get_post_meta( (int) $value, '_wp_attached_file', true );
					if ( $rel_std ) return self::format_media_url_from_relative( $rel_std );
					$url = wp_get_attachment_url( (int) $value );
					return $url ? self::format_media_url( $url ) : (string) $value;
				}
				return (string) $value;

			case 'gallery':
				if ( ! is_array( $value ) ) return '';
				$urls = array();
				foreach ( $value as $item ) {
					if ( is_array( $item ) && isset( $item['url'] ) ) {
						$urls[] = self::format_media_url( $item['url'] );
					} elseif ( is_numeric( $item ) ) {
						$rel_g = get_post_meta( (int) $item, '_wp_attached_file', true );
						if ( $rel_g ) { $urls[] = self::format_media_url_from_relative( $rel_g ); }
						else {
							$url = wp_get_attachment_url( (int) $item );
							if ( $url ) $urls[] = self::format_media_url( $url );
						}
					}
				}
				if ( $this->raw_mode ) return $urls;
				return implode( ( ',' === $sep ? $sep . ' ' : ' ' . $sep . ' ' ), $urls );

			case 'date_picker':
			case 'date_time_picker':
			case 'time_picker':
				$fmt = get_field( $field['key'], $post_id, true );
				return $fmt ? (string) $fmt : (string) $value;

			default:
				return is_array( $value ) ? implode( ', ', $value ) : (string) $value;
		}
	}

	// ── ACF & taxonomy helpers ────────────────────────────────────────────────

	/**
	 * Format a single repeater row as a comma-separated string of sub-field values.
	 * Empty sub-fields are represented by an empty slot (not skipped) to preserve
	 * positional alignment on re-import.
	 *
	 * @param array $row       Associative array of sub-field values for one iteration.
	 * @param array $field_def The repeater field definition (with 'sub_fields').
	 * @return string
	 */
	/**
	 * Format a group field value as "subfield_name\xa0: value" per line.
	 *
	 * @param array|null $group_data  Associative array of sub-field values.
	 * @param array      $field_def   The group field definition (with 'sub_fields').
	 * @return string
	 */
	private function format_group_value( $group_data, $field_def ) {
		if ( empty( $group_data ) || ! is_array( $group_data ) ) return '';

		$sep_char     = " : ";
		$sub_fields   = ! empty( $field_def['sub_fields'] ) ? $field_def['sub_fields'] : array();
		$parent_label = isset( $field_def['label'] ) ? $field_def['label'] : '';

		$normalize = function( $v, $sub_def = null ) {
			// Choice-based sub-fields (select/checkbox/radio/button_group): resolve
			// the raw stored value(s) to their human-readable label(s), same as the
			// top-level export logic — otherwise the raw internal value/slug leaks
			// into the spreadsheet instead of what the user actually sees in ACF.
			if ( $sub_def && in_array( $sub_def['type'], array( 'select', 'checkbox', 'radio', 'button_group' ), true ) ) {
				$choices = ! empty( $sub_def['choices'] ) ? $sub_def['choices'] : array();
				$items   = is_array( $v ) ? $v : array( $v );
				$labels  = array();
				foreach ( $items as $item ) {
					if ( is_scalar( $item ) ) $labels[] = isset( $choices[ $item ] ) ? $choices[ $item ] : (string) $item;
				}
				return implode( ', ', $labels );
			}
			if ( is_array( $v ) ) {
				if ( isset( $v['url'] ) )   return (string) $v['url'];
				if ( isset( $v['label'] ) ) return (string) $v['label'];
				if ( isset( $v['name'] ) )  return (string) $v['name'];
				return '';
			}
			return is_scalar( $v ) ? (string) $v : '';
		};

		$lines = array();
		if ( ! empty( $sub_fields ) ) {
			foreach ( $sub_fields as $sub ) {
				$sub_name  = $sub['name'];
				$val       = array_key_exists( $sub_name, $group_data ) ? $normalize( $group_data[ $sub_name ], $sub ) : '';
				if ( '' === $val ) continue;
				$path_key  = $parent_label . '|||' . $sub['label'];
				$sub_label = isset( $this->sub_label_map[ $path_key ] ) ? $this->sub_label_map[ $path_key ] : $sub['label'];
				$lines[]   = $sub_label . $sep_char . $val;
			}
		} else {
			foreach ( $group_data as $k => $v ) {
				$normalized = $normalize( $v );
				if ( '' === $normalized ) continue;
				$lines[] = $k . $sep_char . $normalized;
			}
		}

		return implode( "
", $lines );
	}

	private function format_repeater_row( $row, $field_def ) {
		// Format: one line per sub-field → "subfield_name : value"
		// The non-breaking space before the colon (Â ) matches the uploaded reference format.
		$sep_char = " : "; // colon with spaces

		$sub_fields = array();
		if ( ! empty( $field_def['sub_fields'] ) ) {
			foreach ( $field_def['sub_fields'] as $sub ) {
				$sub_fields[] = $sub;
			}
		}

		$normalize = function( $v, $sub_def = null ) {
			// Choice-based sub-fields (select/checkbox/radio/button_group): resolve
			// the raw stored value(s) to their human-readable label(s), same as the
			// top-level export logic — otherwise the raw internal value/slug leaks
			// into the spreadsheet instead of what the user actually sees in ACF.
			if ( $sub_def && in_array( $sub_def['type'], array( 'select', 'checkbox', 'radio', 'button_group' ), true ) ) {
				$choices = ! empty( $sub_def['choices'] ) ? $sub_def['choices'] : array();
				$items   = is_array( $v ) ? $v : array( $v );
				$labels  = array();
				foreach ( $items as $item ) {
					if ( is_scalar( $item ) ) $labels[] = isset( $choices[ $item ] ) ? $choices[ $item ] : (string) $item;
				}
				return implode( ', ', $labels );
			}
			if ( is_array( $v ) ) {
				if ( isset( $v['url'] ) )   return (string) $v['url'];
				if ( isset( $v['label'] ) ) return (string) $v['label'];
				if ( isset( $v['name'] ) )  return (string) $v['name'];
				return '';
			}
			return is_scalar( $v ) ? (string) $v : '';
		};

		if ( empty( $sub_fields ) ) {
			// No sub-field definitions: just output values line by line.
			$lines = array();
			foreach ( $row as $v ) {
				$lines[] = $normalize( $v );
			}
			return implode( "
", $lines );
		}

		// Match values by sub-field name; fall back to positional if no match.
		$lines      = array();
		$has_values = false;
		foreach ( $sub_fields as $idx => $sub ) {
			$sub_name = $sub['name'];
			$cell     = '';
			if ( array_key_exists( $sub_name, $row ) ) {
				$cell = $normalize( $row[ $sub_name ], $sub );
			} elseif ( isset( array_values( $row )[ $idx ] ) ) {
				$cell = $normalize( array_values( $row )[ $idx ], $sub );
			}
			if ( '' === $cell ) continue;
			$has_values = true;
			$rep_label = isset( $field_def['label'] ) ? $field_def['label'] : '';
			$rep_path  = $rep_label . '|||' . $sub['label'];
			$s_label   = isset( $this->sub_label_map[ $rep_path ] ) ? $this->sub_label_map[ $rep_path ] : $sub['label'];
			$lines[] = $s_label . $sep_char . $cell;
		}

		if ( ! $has_values ) return '';

		return implode( "
", $lines );
	}

	private function get_acf_fields( $post_type ) {
		$fields = array();
		if ( ! function_exists( 'acf_get_field_groups' ) ) return $fields;

		$groups = acf_get_field_groups( array( 'post_type' => $post_type ) );
		foreach ( $groups as $group ) {
			$group_fields = acf_get_fields( $group['key'] );
			if ( ! $group_fields ) continue;
			foreach ( $group_fields as $field ) {
				$this->collect_field( $field, $fields );
			}
		}
		return $fields;
	}

	private function collect_field( $field, &$fields, $prefix = '', $parent_key = '', $parent_type = '' ) {
		// Layout/UI-only field types — no data, skip entirely (same list PRO's
		// Hierarchical exporter uses for the same purpose).
		$layout_only = array( 'tab', 'accordion', 'message', 'divider' );
		if ( in_array( $field['type'], $layout_only, true ) ) return;

		$label = $prefix ? $prefix . ' › ' . $field['label'] : $field['label'];

		if ( 'group' === $field['type'] ) {
			// One column for the whole group: sub-fields formatted as "name\xa0: value" per line.
			$fields[] = array_merge( $field, array(
				'label'       => $label,
				'parent_key'  => $parent_key,
				'parent_type' => $parent_type,
			) );
		} elseif ( 'repeater' === $field['type'] ) {
			// One column per repeater iteration (numbered from 1).
			// We need to know the max row count: we'll generate placeholder columns
			// and let build_data() fill them dynamically via get_field_value_repeater().
			// We store the repeater field itself with type='repeater' and its sub_fields.
			$fields[] = array_merge( $field, array(
				'label'       => $label,
				'parent_key'  => $parent_key,
				'parent_type' => $parent_type,
			) );
		} else {
			$fields[] = array_merge( $field, array(
				'label'       => $label,
				'parent_key'  => $parent_key,
				'parent_type' => $parent_type,
			) );
		}
	}

	private function get_taxonomies( $post_type ) {
		$names  = get_object_taxonomies( $post_type, 'names' );
		$result = array();
		foreach ( $names as $name ) {
			$obj = get_taxonomy( $name );
			if ( $obj && ! is_wp_error( $obj ) ) $result[] = $obj;
		}
		return $result;
	}

	// ── XML streamer ──────────────────────────────────────────────────────────

	private function stream_xml( $data, $filename ) {
		if ( empty( $data ) ) {
			wp_die( esc_html__( 'No data to export.', 'ideo-field-porter' ) );
		}

		$headers = $data[0];

		// Build safe XML tag names from column headers.
		// Replace spaces/special chars with underscores, ensure valid XML tag.
		$tags = array();
		foreach ( $headers as $h ) {
			$tag = preg_replace( '/[^a-zA-Z0-9_\-.]/', '_', $h );
			$tag = ltrim( $tag, '0123456789.-' );
			if ( '' === $tag ) $tag = 'field';
			$tags[] = $tag;
		}

		$xml = new \SimpleXMLElement( '<?xml version="1.0" encoding="UTF-8"?><posts/>' );

		for ( $i = 1, $total = count( $data ); $i < $total; $i++ ) {
			$row_node = $xml->addChild( 'post' );
			foreach ( $tags as $idx => $tag ) {
				$value = isset( $data[ $i ][ $idx ] ) ? (string) $data[ $i ][ $idx ] : '';
				$child = $row_node->addChild( $tag );
				// Use CDATA for values that contain special chars.
				if ( '' !== $value ) {
					$dom_node = dom_import_simplexml( $child );
					$dom      = $dom_node->ownerDocument;
					$dom_node->appendChild( $dom->createCDATASection( $value ) );
				}
			}
		}

		$xml_output = $xml->asXML();

		header( 'Content-Type: application/xml; charset=UTF-8' );
		header( 'Content-Disposition: attachment; filename="' . $filename . '.xml"' );
		header( 'Content-Length: ' . strlen( $xml_output ) );
		header( 'Cache-Control: no-cache, no-store, must-revalidate' );
		header( 'Pragma: no-cache' );
		header( 'Expires: 0' );

		// phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped -- Raw XML file download; escaping would corrupt the binary content.
		echo $xml_output;
		exit;
	}

	// ── CSV streamer ──────────────────────────────────────────────────────────

	private function stream_csv( $data, $filename ) {
		header( 'Content-Type: text/csv; charset=UTF-8' );
		header( 'Content-Disposition: attachment; filename="' . $filename . '.csv"' );
		header( 'Cache-Control: no-cache, no-store, must-revalidate' );
		header( 'Pragma: no-cache' );
		header( 'Expires: 0' );

		// UTF-8 BOM so Excel opens it correctly.
		// phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped -- Raw CSV BOM byte sequence; escaping would corrupt the file.
		echo "\xEF\xBB\xBF";

		// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fopen -- php://output is a stream wrapper, not a filesystem path; WP_Filesystem does not support it.
		$output = fopen( 'php://output', 'w' );
		foreach ( $data as $row ) {
			fputcsv( $output, $row, ',' );
		}
		// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose -- Closing the php://output stream opened above.
		fclose( $output );
		exit;
	}

	// ── XLSX streamer (native, no library) ───────────────────────────────────

	private function stream_xlsx( $data, $filename ) {
		if ( ! class_exists( 'ZipArchive' ) ) {
			wp_die( esc_html__( 'ZipArchive PHP extension is required to generate .xlsx files.', 'ideo-field-porter' ) );
		}

		$tmp = sys_get_temp_dir() . '/' . uniqid( 'ideo_fp_export_', true ) . '.xlsx';

		$zip = new \ZipArchive();
		if ( true !== $zip->open( $tmp, \ZipArchive::CREATE | \ZipArchive::OVERWRITE ) ) {
			wp_die( esc_html__( 'Unable to create temporary file for export.', 'ideo-field-porter' ) );
		}

		$string_index   = array();
		$string_list    = array();
		$sheet_rows_xml = '';
		$row_num        = 1;

		$data_row_idx = 0;
		foreach ( $data as $row ) {
			$cols_xml = '';
			$col_num  = 0;
			$is_even  = ( $data_row_idx % 2 === 1 ); // alternating from row 2
			$row_s    = $is_even ? ' s="3"' : ' s="2"'; // s=3 even, s=2 odd

			foreach ( $row as $cell ) {
				$col_letter = $this->col_letter( $col_num );
				$cell_ref   = $col_letter . $row_num;
				$cell_str   = (string) $cell;

				if ( $row_num === 1 ) {
					if ( ! isset( $string_index[ $cell_str ] ) ) {
						$string_index[ $cell_str ] = count( $string_list );
						$string_list[]              = $cell_str;
					}
					$si        = $string_index[ $cell_str ];
					$cols_xml .= '<c r="' . $cell_ref . '" t="s" s="1"><v>' . $si . '</v></c>';
				} elseif ( is_numeric( $cell_str ) && '' !== $cell_str && empty( $this->force_text_columns[ $col_num ] ) ) {
					$cols_xml .= '<c r="' . $cell_ref . '"' . $row_s . '><v>' . htmlspecialchars( $cell_str, ENT_XML1 ) . '</v></c>';
				} else {
					if ( ! isset( $string_index[ $cell_str ] ) ) {
						$string_index[ $cell_str ] = count( $string_list );
						$string_list[]              = $cell_str;
					}
					$si        = $string_index[ $cell_str ];
					// wrapText style when cell contains newlines.
					$cell_s = ( strpos( $cell_str, "\n" ) !== false ) ? ( $is_even ? ' s="5"' : ' s="4"' ) : $row_s;
					$cols_xml .= '<c r="' . $cell_ref . '" t="s"' . $cell_s . '><v>' . $si . '</v></c>';
				}
				$col_num++;
			}

			$sheet_rows_xml .= '<row r="' . $row_num . '">' . $cols_xml . '</row>';
			$row_num++;
			$data_row_idx++;
		}

		$col_widths_xml = '';
		if ( ! empty( $data[0] ) ) {
			$col_widths_xml = '<cols>';
			foreach ( $data[0] as $i => $header ) {
				$header_len = mb_strlen( (string) $header ) + 4;
				$data_max   = 0;
				foreach ( $data as $row_scan ) {
					if ( ! isset( $row_scan[ $i ] ) ) continue;
					foreach ( explode( "\n", (string) $row_scan[ $i ] ) as $line ) {
						$data_max = max( $data_max, mb_strlen( $line ) + 2 );
					}
				}
				$width           = min( max( max( $header_len, $data_max ), 12 ), 41 );
				$n               = $i + 1;
				$col_widths_xml .= '<col min="' . $n . '" max="' . $n . '" width="' . $width . '" customWidth="1"/>';
			}
			$col_widths_xml .= '</cols>';
		}

		$zip->addFromString( '[Content_Types].xml', '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
  <Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>
  <Default Extension="xml"  ContentType="application/xml"/>
  <Override PartName="/xl/workbook.xml"                    ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>
  <Override PartName="/xl/worksheets/sheet1.xml"           ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
  <Override PartName="/xl/sharedStrings.xml"               ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"/>
  <Override PartName="/xl/styles.xml"                      ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>
</Types>' );

		$zip->addFromString( '_rels/.rels', '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
  <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>
</Relationships>' );

		$zip->addFromString( 'xl/_rels/workbook.xml.rels', '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
  <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"     Target="worksheets/sheet1.xml"/>
  <Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Target="sharedStrings.xml"/>
  <Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles"        Target="styles.xml"/>
</Relationships>' );

		$zip->addFromString( 'xl/workbook.xml', '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
          xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
  <sheets>
    <sheet name="Export" sheetId="1" r:id="rId1"/>
  </sheets>
</workbook>' );

		$zip->addFromString( 'xl/styles.xml', $this->build_styles_xml() );

		$ss_items = '';
		foreach ( $string_list as $s ) {
			$ss_items .= '<si><t xml:space="preserve">' . htmlspecialchars( $s, ENT_XML1, 'UTF-8' ) . '</t></si>';
		}
		$zip->addFromString( 'xl/sharedStrings.xml',
			'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
			. '<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="' . count( $string_list ) . '" uniqueCount="' . count( $string_list ) . '">'
			. $ss_items . '</sst>'
		);

		$zip->addFromString( 'xl/worksheets/sheet1.xml',
			'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
			. '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">' . '<sheetViews><sheetView workbookViewId="0"/></sheetViews>'
			. $col_widths_xml
			. '<sheetData>' . $sheet_rows_xml . '</sheetData>'
			. '</worksheet>'
		);

		$zip->close();

		header( 'Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' );
		header( 'Content-Disposition: attachment; filename="' . $filename . '.xlsx"' );
		header( 'Content-Length: ' . filesize( $tmp ) );
		header( 'Cache-Control: no-cache, no-store, must-revalidate' );
		header( 'Pragma: no-cache' );
		header( 'Expires: 0' );

		// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_readfile -- Streaming a temp file to the browser for download; WP_Filesystem has no equivalent for output streaming.
		readfile( $tmp );
		wp_delete_file( $tmp );
		exit;
	}

	// ── Utilities ─────────────────────────────────────────────────────────────

	/**
	 * Public wrapper so Admin_Page can retrieve the taxonomy list.
	 */
	public function get_taxonomies_public( $post_type ) {
		return $this->get_taxonomies( $post_type );
	}

	private function build_styles_xml() {
		$color   = \IdeoFieldPorter\Settings_Page::DEFAULT_COLOR;
		$light   = \IdeoFieldPorter\Settings_Page::lighten_color( $color, 0.90 );
		$bg      = \IdeoFieldPorter\Settings_Page::hex_to_xlsx( $color );
		$even    = \IdeoFieldPorter\Settings_Page::hex_to_xlsx( $light );
		$white   = 'FFFFFFFF';

		return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
			. '<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'
			. '<fonts count="2">'
			. '<font><sz val="11"/><name val="Calibri"/></font>'
			. '<font><b/><sz val="11"/><color rgb="' . $white . '"/><name val="Calibri"/></font>'
			. '</fonts>'
			. '<fills count="4">'
			. '<fill><patternFill patternType="none"/></fill>'
			. '<fill><patternFill patternType="gray125"/></fill>'
			. '<fill><patternFill patternType="solid"><fgColor rgb="' . $bg . '"/><bgColor indexed="64"/></patternFill></fill>'
			. '<fill><patternFill patternType="solid"><fgColor rgb="' . $even . '"/><bgColor indexed="64"/></patternFill></fill>'
			. '</fills>'
			. '<borders count="3">'
			. '<border><left/><right/><top/><bottom/><diagonal/></border>'
			. '<border>'
			. '<left style="thin"><color rgb="' . $white . '"/></left>'
			. '<right style="thin"><color rgb="' . $white . '"/></right>'
			. '<top style="thin"><color rgb="' . $white . '"/></top>'
			. '<bottom style="thin"><color rgb="' . $white . '"/></bottom>'
			. '<diagonal/></border>'
			. '<border>'
			. '<left style="thin"><color rgb="FFCCCCCC"/></left>'
			. '<right style="thin"><color rgb="FFCCCCCC"/></right>'
			. '<top style="thin"><color rgb="FFCCCCCC"/></top>'
			. '<bottom style="thin"><color rgb="FFCCCCCC"/></bottom>'
			. '<diagonal/></border>'
			. '</borders>'
			. '<cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs>'
			. '<cellXfs count="6">'
			. '<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>'
			. '<xf numFmtId="0" fontId="1" fillId="2" borderId="1" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center" wrapText="1"/></xf>'
			. '<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" applyFill="1" applyAlignment="1"><alignment horizontal="left" vertical="top"/></xf>'
			. '<xf numFmtId="0" fontId="0" fillId="3" borderId="2" xfId="0" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="left" vertical="top"/></xf>'
			. '<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" applyAlignment="1"><alignment horizontal="left" vertical="top" wrapText="1"/></xf>'
			. '<xf numFmtId="0" fontId="0" fillId="3" borderId="2" xfId="0" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="left" vertical="top" wrapText="1"/></xf>'
			. '</cellXfs>'
			. '</styleSheet>';
	}

	private function col_letter( $index ) {
		$letter = '';
		$index++;
		while ( $index > 0 ) {
			$index--;
			$letter = chr( 65 + ( $index % 26 ) ) . $letter;
			$index  = (int) ( $index / 26 );
		}
		return $letter;
	}

	// ── Media URL helpers ───────────────────────────────────────────────────────

	/**
	 * Format a full absolute URL according to the media_url_format setting.
	 */
	public static function format_media_url( $absolute_url ) {
		if ( 'relative' === \IdeoFieldPorter\Settings_Page::get_media_url_format() ) {
			$upload_dir = wp_upload_dir();
			$base_url   = trailingslashit( $upload_dir['baseurl'] );
			if ( 0 === strpos( $absolute_url, $base_url ) ) {
				return substr( $absolute_url, strlen( $base_url ) );
			}
			// Handle HTTP↔HTTPS mismatch.
			$base_path = wp_parse_url( $base_url, PHP_URL_PATH );
			$val_path  = wp_parse_url( $absolute_url, PHP_URL_PATH );
			if ( $base_path && $val_path && 0 === strpos( $val_path, $base_path ) ) {
				return substr( $val_path, strlen( $base_path ) );
			}
			return $absolute_url; // fallback: return as-is
		}
		return $absolute_url;
	}

	/**
	 * Format a relative path (from _wp_attached_file) according to the setting.
	 */
	public static function format_media_url_from_relative( $relative_path ) {
		if ( 'absolute' === \IdeoFieldPorter\Settings_Page::get_media_url_format() ) {
			$upload_dir = wp_upload_dir();
			return trailingslashit( $upload_dir['baseurl'] ) . $relative_path;
		}
		return $relative_path;
	}
}
