<?php
/**
 * TitanCart Import Service — Module 13
 *
 * Handles CSV parsing, column mapping, row validation, and batch
 * importing for products, categories, and customers.
 *
 * Processing flow:
 *   1. upload()     → save file, create tc_import_jobs row, return job_id + preview
 *   2. validate()   → dry-run with mapping, return per-row errors
 *   3. startJob()   → mark job as queued, schedule WP-Cron event
 *   4. processJob() → (cron) read rows in batches of 100, call entity writers
 *   5. getProgress()→ return job status + processed/total counts
 *
 * @package TitanCart\Modules\ImportExport
 */

namespace TitanCart\Modules\ImportExport;

defined( 'ABSPATH' ) || exit;

class ImportService {

	/** Rows processed per cron batch. */
	const BATCH_SIZE = 100;

	/** Max upload size in bytes (10 MB). */
	const MAX_FILE_SIZE = 10 * 1024 * 1024;

	/** Settings from current job, available during writeRow. */
	private array $current_job_settings = [];

	/** Allowed MIME types for upload. */
	const ALLOWED_MIMES = [ 'text/csv', 'text/plain', 'application/csv', 'application/octet-stream' ];

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

	/**
	 * Handle an uploaded CSV file.
	 *
	 * @param  array  $file     $_FILES element.
	 * @param  string $type     Entity type: products|categories|customers.
	 * @param  array  $options  Import options (update_existing, skip_errors, etc.).
	 * @return array{ job_id: int, headers: string[], preview: array[], field_map: array }
	 * @throws \RuntimeException On upload failure or parse error.
	 */
	public function upload( array $file, string $type, array $options = [] ): array {
		$this->assertValidType( $type );

		// Size guard.
		if ( $file['size'] > self::MAX_FILE_SIZE ) {
			throw new \RuntimeException( esc_html__( 'File exceeds the 10 MB limit.', 'titancart-lite' ) );
		}

		// Move to our imports directory via the WP file API (S441, .org
		// compliance: move_uploaded_file()/copy() replaced — ImageService's
		// wp_handle_upload + upload_dir-filter pattern). A temporary filter
		// pins the destination to importsDir() and unique_filename_callback
		// pins the exact safeFilename, so $dest is byte-identical for every
		// downstream consumer (readCsv, the job's file_path setting).
		// test_type=false: the CSV gate is our own ALLOWED_MIMES/extension
		// model; WP's media allowlist is the wrong gate for import files.
		$imports_dir = $this->importsDir();
		$safe_name   = $this->safeFilename( $file['name'] );
		$dest        = $imports_dir . '/' . $safe_name;

		// Non-HTTP test edge (preserved from the old copy() fallback): the
		// "tmp" file may already BE $dest — nothing to move, and
		// wp_handle_sideload's copy+unlink would DELETE it. Short-circuit.
		$already_in_place = file_exists( $dest )
			&& realpath( (string) $file['tmp_name'] ) === realpath( $dest );

		if ( ! $already_in_place ) {
			if ( ! function_exists( 'wp_handle_upload' ) ) {
				require_once ABSPATH . 'wp-admin/includes/file.php';
			}

			$dir_filter = static function ( array $dirs ) use ( $imports_dir ) {
				$dirs['path'] = $imports_dir;
				return $dirs;
			};
			$overrides = [
				'test_form'                => false,
				'test_type'                => false,
				'unique_filename_callback' => static function () use ( $safe_name ) {
					return $safe_name;
				},
			];

			add_filter( 'upload_dir', $dir_filter );
			// Real HTTP uploads → wp_handle_upload (is_uploaded_file
			// semantics); non-HTTP contexts (REST unit tests, already-moved
			// temp files elsewhere) → wp_handle_sideload (copy + unlink).
			$upload = is_uploaded_file( (string) $file['tmp_name'] )
				? wp_handle_upload( $file, $overrides )
				: wp_handle_sideload( $file, $overrides );
			remove_filter( 'upload_dir', $dir_filter );

			if ( ! isset( $upload['file'] ) ) {
				throw new \RuntimeException( esc_html__( 'Could not save uploaded file.', 'titancart-lite' ) );
			}
			$dest = (string) $upload['file'];
		}

		// Parse headers + first 5 rows for preview.
		[ $headers, $preview ] = $this->readCsv( $dest, 5 );

		if ( empty( $headers ) ) {
			wp_delete_file( $dest );
			throw new \RuntimeException( esc_html__( 'The CSV file appears to be empty or has no header row.', 'titancart-lite' ) );
		}

		// Count total data rows (excluding header).
		$total_rows = max( 0, $this->countRows( $dest ) - 1 );

		// Create the job record.
		global $wpdb;
		$table = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'import_jobs';

		$wpdb->insert( $table, [
			'type'       => $type,
			'filename'   => basename( $dest ),
			'total_rows' => $total_rows,
			'processed'  => 0,
			'errors'     => 0,
			'status'     => 'queued',
			'settings'   => wp_json_encode( array_merge( $options, [
				'file_path'   => $dest,
				'column_map'  => [],
				'update_existing' => ! empty( $options['update_existing'] ),
				'skip_errors'     => ! empty( $options['skip_errors'] ),
			] ) ),
			'created_at' => current_time( 'mysql' ),
		] );

		$job_id = (int) $wpdb->insert_id;

		return [
			'job_id'    => $job_id,
			'headers'   => $headers,
			'preview'   => $preview,
			'field_map' => $this->getFieldMap( $type ),
			'total_rows'=> $total_rows,
		];
	}

	/**
	 * Validate a mapping against a preview batch.
	 * Returns per-row error arrays; empty array = no errors.
	 *
	 * @param  int    $job_id
	 * @param  array  $column_map  [ 'csv_header' => 'tc_field', ... ]
	 * @return array{ errors: array[], valid_count: int, error_count: int }
	 */
	public function validate( int $job_id, array $column_map ): array {
		$job = $this->getJob( $job_id );
		if ( ! $job ) {
			throw new \RuntimeException( esc_html( "Import job #{$job_id} not found." ) );
		}

		$settings  = json_decode( $job->settings, true ) ?: [];
		$file_path = $settings['file_path'] ?? '';
		$type      = $job->type;

		if ( ! file_exists( $file_path ) ) {
			throw new \RuntimeException( esc_html( "Import file not found for job #{$job_id}." ) );
		}

		// Read up to 500 rows for validation preview.
		[ , $rows ] = $this->readCsv( $file_path, 500 );

		$required = $this->getRequiredFields( $type );
		$mapped   = array_values( $column_map );
		$missing  = array_diff( $required, $mapped );

		if ( ! empty( $missing ) ) {
			return [
				'errors'       => [],
				'valid_count'  => 0,
				'error_count'  => 0,
				'missing_required' => $missing,
			];
		}

		$errors      = [];
		$error_count = 0;
		$valid_count = 0;

		foreach ( $rows as $i => $row ) {
			$mapped_row = $this->mapRow( $row, $column_map );
			$row_errors = $this->validateRow( $mapped_row, $type, $i + 2 ); // +2: 1-indexed + skip header
			if ( ! empty( $row_errors ) ) {
				$errors[] = [ 'row' => $i + 2, 'errors' => $row_errors ];
				$error_count++;
			} else {
				$valid_count++;
			}
		}

		return [
			'errors'       => $errors,
			'valid_count'  => $valid_count,
			'error_count'  => $error_count,
		];
	}

	/**
	 * Persist the column map to the job and schedule the import cron event.
	 *
	 * @param  int   $job_id
	 * @param  array $column_map
	 * @return bool
	 */
	public function startJob( int $job_id, array $column_map ): bool {
		global $wpdb;
		$table = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'import_jobs';

		$job = $this->getJob( $job_id );
		if ( ! $job || $job->status !== 'queued' ) {
			return false;
		}

		$settings               = json_decode( $job->settings, true ) ?: [];
		$settings['column_map'] = $column_map;

		// Test import: limit to first 5 rows
		$total = (int) $job->total_rows;
		if ( ! empty( $settings['test_import'] ) ) {
			$total = min( 5, $total );
		}

		$wpdb->update(
			$table,
			[
				'settings'   => wp_json_encode( $settings ),
				'total_rows' => $total,
				'status'     => 'queued',
				'started_at' => current_time( 'mysql' ),
			],
			[ 'id' => $job_id ]
		);

		// Schedule a single WP-Cron event to kick off the import.
		// Each batch reschedules itself until done.
		wp_schedule_single_event( time(), 'titancart_process_import', [ $job_id ] );

		return true;
	}

	/**
	 * Process the next batch of rows for a queued/running import job.
	 * Called by WP-Cron via titancart_process_import hook.
	 *
	 * @param  int $job_id
	 */
	public function processJob( int $job_id ): void {
		global $wpdb;
		$table = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'import_jobs';

		$job = $this->getJob( $job_id );
		if ( ! $job || ! in_array( $job->status, [ 'queued', 'running' ], true ) ) {
			return;
		}

		$settings   = json_decode( $job->settings, true ) ?: [];
		$file_path  = $settings['file_path'] ?? '';
		$column_map = $settings['column_map'] ?? [];
		$offset     = (int) $job->processed; // rows already done
		$skip_err   = ! empty( $settings['skip_errors'] );
		$update_ex  = ! empty( $settings['update_existing'] );
		$this->current_job_settings = $settings;

		if ( ! file_exists( $file_path ) ) {
			$wpdb->update( $table, [ 'status' => 'failed', 'log' => 'Import file not found.' ], [ 'id' => $job_id ] );
			return;
		}

		// Mark running.
		$wpdb->update( $table, [ 'status' => 'running' ], [ 'id' => $job_id ] );

		// Read header + one batch.
		[ $headers, $rows ] = $this->readCsvBatch( $file_path, $offset, self::BATCH_SIZE );

		if ( empty( $rows ) ) {
			// Nothing left — mark complete.
			$wpdb->update( $table, [
				'status'       => 'completed',
				'completed_at' => current_time( 'mysql' ),
			], [ 'id' => $job_id ] );
			wp_delete_file( $file_path );
			return;
		}

		$processed   = 0;
		$error_count = (int) $job->errors;
		$log_lines   = $job->log ? explode( "\n", $job->log ) : [];

		foreach ( $rows as $row ) {
			$mapped = $this->mapRow( $row, $column_map );
			try {
				$this->writeRow( $mapped, $job->type, $update_ex );
				$processed++;
			} catch ( \Throwable $e ) {
				$error_count++;
				$log_lines[] = sprintf( 'Row %d: %s', $offset + $processed + 1, $e->getMessage() );
				if ( ! $skip_err ) {
					$wpdb->update( $table, [
						'status'     => 'failed',
						'processed'  => $offset + $processed,
						'errors'     => $error_count,
						'log'        => implode( "\n", array_slice( $log_lines, -200 ) ),
					], [ 'id' => $job_id ] );
					return;
				}
			}
		}

		$new_processed = $offset + $processed;

		// Update progress.
		$wpdb->update( $table, [
			'processed'  => $new_processed,
			'errors'     => $error_count,
			'log'        => implode( "\n", array_slice( $log_lines, -200 ) ),
		], [ 'id' => $job_id ] );

		// Schedule next batch if there's more to do.
		if ( $new_processed < (int) $job->total_rows ) {
			wp_schedule_single_event( time() + 5, 'titancart_process_import', [ $job_id ] );
		} else {
			$wpdb->update( $table, [
				'status'       => 'completed',
				'completed_at' => current_time( 'mysql' ),
			], [ 'id' => $job_id ] );
			wp_delete_file( $file_path );
		}
	}

	/**
	 * Fetch a job record with current progress.
	 *
	 * @param  int $job_id
	 * @return object|null
	 */
	public function getProgress( int $job_id ): ?object {
		return $this->getJob( $job_id );
	}

	/**
	 * Return a paginated list of past import jobs (newest first).
	 *
	 * @param  int $limit
	 * @param  int $offset
	 * @return object[]
	 */
	public function getJobs( int $limit = 20, int $offset = 0 ): array {
		global $wpdb;
		$table = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'import_jobs';
		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT id, type, filename, total_rows, processed, errors, status, created_at, completed_at
				   FROM {$table}
				  ORDER BY id DESC
				  LIMIT %d OFFSET %d",
				$limit,
				$offset
			)
		) ?: [];
	}

	// ──────────────────────────────────────────────────────────────────────────
	// Field maps — what TC fields exist per entity type
	// ──────────────────────────────────────────────────────────────────────────

	/**
	 * Return all available import field definitions for a given entity type.
	 *
	 * @param  string $type
	 * @return array{ required: string[], optional: string[], descriptions: array<string,string> }
	 */
	public function getFieldMap( string $type ): array {
		$maps = [
			'products' => [
				'required' => [ 'product_code' ],
				'optional' => [
					'name', 'price', 'list_price', 'cost_price', 'status',
					'description', 'short_description', 'meta_title', 'meta_description',
					'slug', 'categories', 'secondary_categories',
					'quantity', 'track_inventory', 'stock_status', 'allow_backorders',
					'min_qty', 'max_qty', 'qty_step', 'out_of_stock_action',
					'weight', 'weight_unit', 'length', 'width', 'height', 'dimension_unit',
					'shipping_freight', 'free_shipping', 'requires_shipping',
					'tax_class', 'product_type', 'visibility', 'featured',
					'is_downloadable', 'images', 'position',
					'search_words', 'page_title',
					'variation_group_code', 'variation_set_as_default',
				],
				'labels' => [
					'product_code'        => 'Product Code',
					'name'                => 'Name',
					'price'               => 'Price',
					'list_price'          => 'List Price',
					'cost_price'          => 'Cost Price',
					'status'              => 'Status',
					'description'         => 'Description',
					'short_description'   => 'Short Description',
					'meta_title'          => 'Meta Title',
					'meta_description'    => 'Meta Description',
					'slug'                => 'URL Slug',
					'categories'          => 'Categories',
					'secondary_categories'=> 'Secondary Categories',
					'quantity'            => 'Quantity',
					'track_inventory'     => 'Track Inventory',
					'stock_status'        => 'Stock Status',
					'allow_backorders'    => 'Allow Backorders',
					'min_qty'             => 'Min Qty',
					'max_qty'             => 'Max Qty',
					'qty_step'            => 'Qty Step',
					'out_of_stock_action' => 'Out of Stock Action',
					'weight'              => 'Weight',
					'weight_unit'         => 'Weight Unit',
					'length'              => 'Length',
					'width'               => 'Width',
					'height'              => 'Height',
					'dimension_unit'      => 'Dimension Unit',
					'shipping_freight'    => 'Shipping Freight',
					'free_shipping'       => 'Free Shipping',
					'requires_shipping'   => 'Requires Shipping',
					'tax_class'           => 'Tax Class',
					'product_type'        => 'Product Type',
					'visibility'          => 'Visibility',
					'featured'            => 'Featured',
					'is_downloadable'     => 'Downloadable',
					'images'              => 'Images',
					'position'            => 'Position',
					'search_words'        => 'Search Words',
					'page_title'          => 'Page Title',
					'variation_group_code'      => 'Variation Group Code',
					'variation_set_as_default'  => 'Variation Set as Default',
				],
				'descriptions' => [
					'product_code'        => 'Product code (required, unique identifier for matching)',
					'name'                => 'Product name',
					'price'               => 'Selling price (numeric)',
					'list_price'          => 'List/compare-at price (numeric)',
					'cost_price'          => 'Cost/wholesale price (numeric, for profit tracking)',
					'status'              => 'active, disabled, or hidden',
					'description'         => 'Full HTML description',
					'short_description'   => 'Short description',
					'meta_title'          => 'SEO meta title',
					'meta_description'    => 'SEO meta description',
					'slug'                => 'URL slug (auto-generated from name if blank)',
					'categories'          => 'Primary category: Electronics///Laptops (use /// for path)',
					'secondary_categories'=> 'Additional categories, pipe-separated: Sale|Featured',
					'quantity'            => 'Stock quantity (integer)',
					'track_inventory'     => '1 = track inventory, 0 = do not track',
					'stock_status'        => 'in_stock, out_of_stock, or on_backorder',
					'allow_backorders'    => '1 = allow backorders, 0 = do not allow',
					'min_qty'             => 'Minimum order quantity',
					'max_qty'             => 'Maximum order quantity (0 = unlimited)',
					'qty_step'            => 'Quantity step increment (default 1)',
					'out_of_stock_action' => 'none, buy (allow), or subscribe (waitlist)',
					'weight'              => 'Weight value (numeric)',
					'weight_unit'         => 'kg, g, lb, or oz',
					'length'              => 'Package length (numeric)',
					'width'               => 'Package width (numeric)',
					'height'              => 'Package height (numeric)',
					'dimension_unit'      => 'cm, mm, or in',
					'shipping_freight'    => 'Per-product shipping surcharge (numeric)',
					'free_shipping'       => '1 = free shipping, 0 = standard',
					'requires_shipping'   => '1 = physical (needs shipping), 0 = virtual/digital',
					'tax_class'           => 'Tax class name',
					'product_type'        => 'simple, variable, downloadable, or virtual',
					'visibility'          => 'visible, catalog, search, or hidden',
					'featured'            => '1 = featured product, 0 = normal',
					'is_downloadable'     => '1 = downloadable product, 0 = no',
					'images'              => 'Image URLs or filenames, pipe-separated (e.g. img1.jpg|img2.jpg)',
					'position'            => 'Sort position (integer)',
					'search_words'        => 'Additional search terms, comma-separated',
					'page_title'          => 'Browser page title (uses meta_title if blank)',
					'variation_group_code'      => 'Group code to link products as variations (e.g. TSHIRT-001). Products sharing the same code appear as one product with selectable attributes.',
					'variation_set_as_default'  => 'Y or 1 = this variation is selected by default on the storefront',
				],
			],
			'categories' => [
				'required' => [ 'name' ],
				'optional' => [ 'parent', 'description', 'status', 'meta_title', 'meta_description', 'slug', 'position' ],
				'labels' => [
					'name'             => 'Name',
					'parent'           => 'Parent Category',
					'description'      => 'Description',
					'status'           => 'Status',
					'meta_title'       => 'Meta Title',
					'meta_description' => 'Meta Description',
					'slug'             => 'URL Slug',
					'position'         => 'Position',
				],
				'descriptions' => [
					'name'             => 'Category name (required)',
					'parent'           => 'Parent category name (leave blank for root)',
					'description'      => 'Category description',
					'status'           => 'A = Active, D = Disabled',
					'meta_title'       => 'SEO meta title',
					'meta_description' => 'SEO meta description',
					'slug'             => 'URL slug (auto-generated from name if blank)',
					'position'         => 'Sort position (integer)',
				],
			],
			'orders' => [
				'required' => [ 'order_id', 'email' ],
				'optional' => [
					'status', 'subtotal', 'discount', 'shipping_cost', 'tax_amount', 'total',
					'currency_code', 'customer_notes', 'admin_notes',
					'billing_first_name', 'billing_last_name', 'billing_company',
					'billing_address_1', 'billing_address_2', 'billing_city',
					'billing_state', 'billing_zipcode', 'billing_country', 'billing_phone',
					'shipping_first_name', 'shipping_last_name',
					'shipping_address_1', 'shipping_address_2', 'shipping_city',
					'shipping_state', 'shipping_zipcode', 'shipping_country', 'shipping_phone',
					'date',
				],
				'labels' => [
					'order_id'              => 'Order ID',
					'email'                 => 'Email',
					'status'                => 'Status',
					'subtotal'              => 'Subtotal',
					'discount'              => 'Discount',
					'shipping_cost'         => 'Shipping Cost',
					'tax_amount'            => 'Tax Amount',
					'total'                 => 'Total',
					'currency_code'         => 'Currency Code',
					'customer_notes'        => 'Customer Notes',
					'admin_notes'           => 'Admin Notes',
					'billing_first_name'    => 'Billing First Name',
					'billing_last_name'     => 'Billing Last Name',
					'billing_company'       => 'Billing Company',
					'billing_address_1'     => 'Billing Address 1',
					'billing_address_2'     => 'Billing Address 2',
					'billing_city'          => 'Billing City',
					'billing_state'         => 'Billing State',
					'billing_zipcode'       => 'Billing Zipcode',
					'billing_country'       => 'Billing Country',
					'billing_phone'         => 'Billing Phone',
					'shipping_first_name'   => 'Shipping First Name',
					'shipping_last_name'    => 'Shipping Last Name',
					'shipping_address_1'    => 'Shipping Address 1',
					'shipping_address_2'    => 'Shipping Address 2',
					'shipping_city'         => 'Shipping City',
					'shipping_state'        => 'Shipping State',
					'shipping_zipcode'      => 'Shipping Zipcode',
					'shipping_country'      => 'Shipping Country',
					'shipping_phone'        => 'Shipping Phone',
					'date'                  => 'Date',
				],
				'descriptions' => [
					'order_id'              => 'Order ID (required, used for matching)',
					'email'                 => 'Customer email (required)',
					'status'                => 'O=Open, P=Paid, C=Complete, F=Failed, D=Declined, B=Backordered, I=Cancelled',
					'subtotal'              => 'Order subtotal (numeric)',
					'discount'              => 'Discount amount (numeric)',
					'shipping_cost'         => 'Shipping cost (numeric)',
					'tax_amount'            => 'Tax amount (numeric)',
					'total'                 => 'Order total (numeric)',
					'currency_code'         => 'ISO 4217 currency code (e.g. USD)',
					'customer_notes'        => 'Notes from the customer',
					'admin_notes'           => 'Internal admin notes',
					'billing_first_name'    => 'Billing first name',
					'billing_last_name'     => 'Billing last name',
					'billing_company'       => 'Billing company',
					'billing_address_1'     => 'Billing address line 1',
					'billing_address_2'     => 'Billing address line 2',
					'billing_city'          => 'Billing city',
					'billing_state'         => 'Billing state/province',
					'billing_zipcode'       => 'Billing ZIP/postcode',
					'billing_country'       => 'Billing country code (e.g. US)',
					'billing_phone'         => 'Billing phone',
					'shipping_first_name'   => 'Shipping first name',
					'shipping_last_name'    => 'Shipping last name',
					'shipping_address_1'    => 'Shipping address line 1',
					'shipping_address_2'    => 'Shipping address line 2',
					'shipping_city'         => 'Shipping city',
					'shipping_state'        => 'Shipping state/province',
					'shipping_zipcode'      => 'Shipping ZIP/postcode',
					'shipping_country'      => 'Shipping country code (e.g. US)',
					'shipping_phone'        => 'Shipping phone',
					'date'                  => 'Order date (YYYY-MM-DD or YYYY-MM-DD HH:MM:SS)',
				],
			],
			'order_items' => [
				'required' => [ 'order_id', 'product_code' ],
				'optional' => [ 'product_name', 'quantity', 'price', 'extra' ],
				'labels' => [
					'order_id'     => 'Order ID',
					'product_code' => 'Product Code',
					'product_name' => 'Product Name',
					'quantity'     => 'Quantity',
					'price'        => 'Price',
					'extra'        => 'Extra Data',
				],
				'descriptions' => [
					'order_id'     => 'Order ID (required, must match existing order)',
					'product_code' => 'Product code (required)',
					'product_name' => 'Product name (auto-filled from product if blank)',
					'quantity'     => 'Quantity (default 1)',
					'price'        => 'Unit price (auto-filled from product if blank)',
					'extra'        => 'Extra data (JSON)',
				],
			],
			'reviews' => [
				'required' => [ 'product_code', 'rating' ],
				'optional' => [
					'name', 'email', 'title', 'content', 'status',
					'ip_address', 'date',
				],
				'labels' => [
					'product_code' => 'Product Code',
					'rating'       => 'Rating',
					'name'         => 'Name',
					'email'        => 'Email',
					'title'        => 'Title',
					'content'      => 'Content',
					'status'       => 'Status',
					'ip_address'   => 'IP Address',
					'date'         => 'Date',
				],
				'descriptions' => [
					'product_code' => 'Product code (required, links review to product)',
					'rating'       => 'Rating value 1-5 (required)',
					'name'         => 'Reviewer name',
					'email'        => 'Reviewer email',
					'title'        => 'Review title/headline',
					'content'      => 'Review text',
					'status'       => 'pending, approved, or rejected (default: approved)',
					'ip_address'   => 'Reviewer IP address',
					'date'         => 'Review date (YYYY-MM-DD)',
				],
			],
			'waitlist' => [
				'required' => [ 'email' ],
				'optional' => [ 'product_code', 'name', 'date' ],
				'labels' => [
					'email'        => 'Email',
					'product_code' => 'Product Code',
					'name'         => 'Name',
					'date'         => 'Date',
				],
				'descriptions' => [
					'email'        => 'Subscriber email (required)',
					'product_code' => 'Product code to subscribe to (blank = general subscriber)',
					'name'         => 'Subscriber name',
					'date'         => 'Subscription date (YYYY-MM-DD)',
				],
			],
			'customers' => [
				'required' => [ 'email' ],
				'optional' => [
					'first_name', 'last_name', 'phone', 'company',
					'address_1', 'address_2', 'city', 'state', 'postcode', 'country',
				],
				'labels' => [
					'email'      => 'Email',
					'first_name' => 'First Name',
					'last_name'  => 'Last Name',
					'phone'      => 'Phone',
					'company'    => 'Company',
					'address_1'  => 'Address 1',
					'address_2'  => 'Address 2',
					'city'       => 'City',
					'state'      => 'State',
					'postcode'   => 'Postcode',
					'country'    => 'Country',
				],
				'descriptions' => [
					'email'      => 'Customer email (required, must be unique)',
					'first_name' => 'First name',
					'last_name'  => 'Last name',
					'phone'      => 'Phone number',
					'company'    => 'Company name',
					'address_1'  => 'Address line 1',
					'address_2'  => 'Address line 2',
					'city'       => 'City',
					'state'      => 'State / province code',
					'postcode'   => 'Postcode / ZIP',
					'country'    => 'ISO 2-letter country code (e.g. US)',
				],
			],
		];

		$result = $maps[ $type ] ?? [ 'required' => [], 'optional' => [], 'labels' => [], 'descriptions' => [] ];

		// For products: dynamically append all product attributes as importable fields
		if ( $type === 'products' ) {
			$result = $this->appendDynamicFeatureFields( $result );
		}

		return $result;
	}

	/**
	 * The canonical set of importable entity types.
	 *
	 * ⚠️ Must mirror the top-level keys of the `$maps` array in getFieldMap()
	 * above — this is the single accessor other code (e.g. the preset REST
	 * layer, KI-34) uses to validate an entity type, so the type authority
	 * stays in this class rather than being re-listed elsewhere.
	 *
	 * @return string[]
	 */
	public function supportedTypes(): array {
		return [ 'products', 'categories', 'orders', 'order_items', 'reviews', 'customers' ];
	}

	/**
	 * Query all product features/attributes from the DB and append them
	 * as optional import fields with a `feature:` prefix.
	 */
	private function appendDynamicFeatureFields( array $map ): array {
		global $wpdb;
		$feat_table  = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'product_features';
		$trans_table = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'product_feature_translations';
		$lang        = function_exists( 'titancart_get_current_language' ) ? titancart_get_current_language() : 'en';

		$features = $wpdb->get_results( // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $feat_table/$trans_table are $wpdb->prefix . TITANCART_TABLE_PREFIX constants (never user input); values bound via placeholders.
			$wpdb->prepare(
				"SELECT f.id, f.type, f.purpose, COALESCE(t.name, '') AS name
				 FROM {$feat_table} f
				 LEFT JOIN {$trans_table} t ON t.feature_id = f.id AND t.language_code = %s
				 WHERE f.status = 'active' AND f.type != 'G'
				 ORDER BY f.position ASC, f.id ASC",
				$lang
			)
		);

		if ( $features ) {
			foreach ( $features as $f ) {
				$key  = 'feature:' . $f->id;
				$name = $f->name ?: ( 'Attribute #' . $f->id );
				$hint = $name;

				// Add type context
				$type_labels = [
					'C' => 'Yes/No', 'M' => 'Multiple values (pipe-separated)',
					'S' => 'Select (single value)', 'N' => 'Number',
					'E' => 'Color swatch', 'T' => 'Text', 'O' => 'Number field', 'D' => 'Date',
				];
				if ( isset( $type_labels[ $f->type ] ) ) {
					$hint .= ' — ' . $type_labels[ $f->type ];
				}

				$purpose_labels = [ 'brand' => 'Brand', 'variation_split' => 'Variation', 'filter' => 'Filter', 'info' => 'Info' ];
				if ( isset( $purpose_labels[ $f->purpose ] ) ) {
					$hint .= ' [' . $purpose_labels[ $f->purpose ] . ']';
				}

				$map['optional'][]          = $key;
				$map['labels'][ $key ]       = $name;
				$map['descriptions'][ $key ] = $hint;
			}
		}

		return $map;
	}

	// ──────────────────────────────────────────────────────────────────────────
	// Internal helpers
	// ──────────────────────────────────────────────────────────────────────────

	/** @return string[] */
	private function getRequiredFields( string $type ): array {
		return $this->getFieldMap( $type )['required'] ?? [];
	}

	/**
	 * Read CSV file and return [headers, rows].
	 * $max_rows = 0 means read all.
	 *
	 * @return array{ 0: string[], 1: array[] }
	 */
	private function readCsv( string $path, int $max_rows = 0 ): array {
		$handle = @fopen( $path, 'r' ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fopen -- streamed CSV parse of large import files via fgetcsv; WP_Filesystem has no streaming API.
		if ( ! $handle ) {
			throw new \RuntimeException( esc_html( "Cannot open file: {$path}" ) );
		}

		// Detect and strip UTF-8 BOM.
		$bom = fread( $handle, 3 ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fread -- BOM sniff on the streaming CSV handle.
		if ( $bom !== "\xEF\xBB\xBF" ) {
			rewind( $handle );
		}

		$headers = null;
		$rows    = [];
		$count   = 0;
		$delim   = $this->detectDelimiter( $path );

		while ( ( $line = fgetcsv( $handle, 0, $delim ) ) !== false ) {
			if ( $headers === null ) {
				$headers = array_map( 'trim', $line );
				continue;
			}
			$rows[] = $line;
			$count++;
			if ( $max_rows > 0 && $count >= $max_rows ) {
				break;
			}
		}

		fclose( $handle ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose -- closes the streaming CSV handle.
		return [ $headers ?? [], $rows ];
	}

	/**
	 * Read a slice of rows (skipping $offset data rows after the header).
	 *
	 * @return array{ 0: string[], 1: array[] }
	 */
	private function readCsvBatch( string $path, int $offset, int $limit ): array {
		$handle = @fopen( $path, 'r' ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fopen -- streamed CSV batch read via fgetcsv; WP_Filesystem has no streaming API.
		if ( ! $handle ) {
			return [ [], [] ];
		}

		$bom = fread( $handle, 3 ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fread -- BOM sniff on the streaming CSV batch handle.
		if ( $bom !== "\xEF\xBB\xBF" ) {
			rewind( $handle );
		}

		$delim   = $this->detectDelimiter( $path );
		$headers = null;
		$skipped = 0;
		$rows    = [];

		while ( ( $line = fgetcsv( $handle, 0, $delim ) ) !== false ) {
			if ( $headers === null ) {
				$headers = array_map( 'trim', $line );
				continue;
			}
			if ( $skipped < $offset ) {
				$skipped++;
				continue;
			}
			$rows[] = $line;
			if ( count( $rows ) >= $limit ) {
				break;
			}
		}

		fclose( $handle ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose -- closes the streaming CSV batch handle.
		return [ $headers ?? [], $rows ];
	}

	/** Count total lines in a CSV file (including header). */
	private function countRows( string $path ): int {
		$count  = 0;
		$handle = @fopen( $path, 'r' ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fopen -- line-count pass over large CSVs via fgets; WP_Filesystem has no streaming API.
		if ( ! $handle ) {
			return 0;
		}
		while ( fgets( $handle ) !== false ) {
			$count++;
		}
		fclose( $handle ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose -- closes the streaming handle.
		return $count;
	}

	/**
	 * Auto-detect CSV delimiter by sampling the header line.
	 * Prefers comma, falls back to semicolon or tab.
	 */
	private function detectDelimiter( string $path ): string {
		$handle = @fopen( $path, 'r' ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fopen -- single-line delimiter sniff via fgets; WP_Filesystem has no streaming API.
		if ( ! $handle ) {
			return ',';
		}
		$line = fgets( $handle );
		fclose( $handle ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose -- closes the sniff handle.

		$counts = [
			','  => substr_count( $line, ',' ),
			';'  => substr_count( $line, ';' ),
			"\t" => substr_count( $line, "\t" ),
		];
		arsort( $counts );
		return (string) key( $counts );
	}

	/**
	 * Map a raw CSV row array to an associative array using the column map.
	 * $column_map: [ 'CSV Header' => 'tc_field' ]
	 *
	 * @param  string[] $row         Raw CSV row values.
	 * @param  array    $column_map  Header-to-field mapping.
	 * @return array<string,string>  Mapped field values.
	 */
	private function mapRow( array $row, array $column_map ): array {
		// column_map is keyed by CSV column header name; we need by index.
		// The controller passes a zero-indexed map: [col_index => tc_field].
		// Support both formats.
		$mapped = [];
		foreach ( $column_map as $key => $field ) {
			if ( $field === '' || $field === null ) {
				continue;
			}
			$value = is_numeric( $key ) ? ( $row[ (int) $key ] ?? '' ) : ( $row[ $key ] ?? '' );
			$mapped[ $field ] = trim( (string) $value );
		}
		return $mapped;
	}

	/**
	 * Validate a single mapped row for a given entity type.
	 *
	 * @return string[] List of error messages. Empty = valid.
	 */
	private function validateRow( array $row, string $type, int $line_num ): array {
		$errors = [];

		if ( $type === 'products' ) {
			if ( empty( $row['product_code'] ) && empty( $row['sku'] ) ) {
				$errors[] = "Line {$line_num}: Product Code is required.";
			}
			if ( empty( $row['name'] ) ) {
				$errors[] = "Line {$line_num}: Product name is required.";
			}
			if ( isset( $row['price'] ) && $row['price'] !== '' && ! is_numeric( $row['price'] ) ) {
				$errors[] = "Line {$line_num}: Price must be numeric.";
			}
			if ( isset( $row['quantity'] ) && $row['quantity'] !== '' && ! ctype_digit( $row['quantity'] ) ) {
				$errors[] = "Line {$line_num}: Quantity must be a whole number.";
			}
			if ( isset( $row['status'] ) && $row['status'] !== '' && ! $this->normalizeStatus( $row['status'], null ) ) {
				$errors[] = "Line {$line_num}: Status must be active, disabled, or hidden.";
			}
		} elseif ( $type === 'categories' ) {
			if ( empty( $row['name'] ) ) {
				$errors[] = "Line {$line_num}: Category name is required.";
			}
		} elseif ( $type === 'customers' ) {
			if ( empty( $row['email'] ) ) {
				$errors[] = "Line {$line_num}: Customer email is required.";
			} elseif ( ! is_email( $row['email'] ) ) {
				$errors[] = "Line {$line_num}: '{$row['email']}' is not a valid email address.";
			}
		}

		return $errors;
	}

	/**
	 * Write a single mapped row to the database for the given entity type.
	 *
	 * @throws \RuntimeException On write failure.
	 */
	private function writeRow( array $row, string $type, bool $update_existing ): void {
		global $wpdb;
		$settings = $this->current_job_settings ?? [];

		if ( $type === 'products' ) {
			$this->writeProduct( $row, $update_existing, $settings );
		} elseif ( $type === 'categories' ) {
			$this->writeCategory( $row, $update_existing );
		} elseif ( $type === 'customers' ) {
			$this->writeCustomer( $row, $update_existing );
		} elseif ( $type === 'orders' ) {
			$this->writeOrder( $row, $update_existing );
		} elseif ( $type === 'order_items' ) {
			$this->writeOrderItem( $row );
		} elseif ( $type === 'reviews' ) {
			$this->writeReview( $row );
		} elseif ( $type === 'waitlist' ) {
			$this->writeWaitlist( $row );
		}
	}

	// ── Entity writers ────────────────────────────────────────────────────────

	private function writeProduct( array $row, bool $update_existing, array $settings = [] ): void {
		global $wpdb;
		$products_table = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'products';
		$trans_table    = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'product_translations';
		$images_table   = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'product_images';
		$lang           = function_exists( 'titancart_get_current_language' ) ? titancart_get_current_language() : 'en';

		$sku = trim( $row['product_code'] ?? $row['sku'] ?? '' );
		if ( ! $sku ) {
			throw new \RuntimeException( 'Product code is required.' );
		}

		// Import strategy: import_all (default), update_only, create_only
		$strategy          = $settings['import_strategy'] ?? 'import_all';
		$delete_images     = ! empty( $settings['delete_additional_images'] );
		$set_qty_zero      = ! empty( $settings['set_quantity_zero'] );

		// Look up existing product by SKU
		$existing_id = (int) $wpdb->get_var(
			$wpdb->prepare( "SELECT id FROM {$products_table} WHERE sku = %s AND deleted_at IS NULL LIMIT 1", $sku )
		);

		// Enforce import strategy
		if ( $strategy === 'update_only' && ! $existing_id ) {
			return; // skip — product doesn't exist and strategy says update only
		}
		if ( $strategy === 'create_only' && $existing_id ) {
			return; // skip — product already exists and strategy says create only
		}

		$product_data = [
			'sku'               => $sku,
			'updated_at'        => current_time( 'mysql' ),
		];

		// Only set fields that are present in the CSV row (don't overwrite with defaults on update)
		if ( isset( $row['price'] ) && $row['price'] !== '' )       $product_data['price']          = (float) $row['price'];
		if ( isset( $row['list_price'] ) && $row['list_price'] !== '' ) $product_data['list_price']  = (float) $row['list_price'];
		if ( isset( $row['status'] ) && $row['status'] !== '' )     $product_data['status']         = $this->normalizeStatus( $row['status'], 'active' );
		if ( isset( $row['weight'] ) && $row['weight'] !== '' )     $product_data['weight']         = (float) $row['weight'];
		if ( isset( $row['quantity'] ) && $row['quantity'] !== '' ) $product_data['stock_quantity']  = (int) $row['quantity'];
		if ( isset( $row['free_shipping'] ) )                       $product_data['free_shipping']  = ( $row['free_shipping'] && $row['free_shipping'] !== '0' ) ? 1 : 0;
		if ( isset( $row['min_qty'] ) && $row['min_qty'] !== '' )   $product_data['min_quantity']   = max( 1, (int) $row['min_qty'] );
		if ( isset( $row['max_qty'] ) && $row['max_qty'] !== '' )   $product_data['max_quantity']   = (int) $row['max_qty'];
		if ( isset( $row['qty_step'] ) && $row['qty_step'] !== '' ) $product_data['quantity_step']  = max( 1, (int) $row['qty_step'] );
		if ( isset( $row['product_type'] ) && $row['product_type'] !== '' ) $product_data['type']   = in_array( $row['product_type'], [ 'simple', 'variable', 'downloadable', 'virtual' ], true ) ? $row['product_type'] : 'simple';
		if ( isset( $row['position'] ) && $row['position'] !== '' ) $product_data['position']       = (int) $row['position'];

		// New expanded fields
		if ( isset( $row['cost_price'] ) && $row['cost_price'] !== '' )         $product_data['cost_price']       = (float) $row['cost_price'];
		if ( isset( $row['weight_unit'] ) && $row['weight_unit'] !== '' )       $product_data['weight_unit']      = in_array( $row['weight_unit'], ['kg','g','lb','oz'], true ) ? $row['weight_unit'] : 'kg';
		if ( isset( $row['length'] ) && $row['length'] !== '' )                 $product_data['length']           = (float) $row['length'];
		if ( isset( $row['width'] ) && $row['width'] !== '' )                   $product_data['width']            = (float) $row['width'];
		if ( isset( $row['height'] ) && $row['height'] !== '' )                 $product_data['height']           = (float) $row['height'];
		if ( isset( $row['dimension_unit'] ) && $row['dimension_unit'] !== '' ) $product_data['dimension_unit']   = in_array( $row['dimension_unit'], ['cm','mm','in'], true ) ? $row['dimension_unit'] : 'cm';
		if ( isset( $row['shipping_freight'] ) && $row['shipping_freight']!=='') $product_data['shipping_freight']= (float) $row['shipping_freight'];
		if ( isset( $row['requires_shipping'] ) )                               $product_data['requires_shipping']= ( $row['requires_shipping'] && $row['requires_shipping'] !== '0' ) ? 1 : 0;
		if ( isset( $row['is_downloadable'] ) )                                 $product_data['is_downloadable']  = ( $row['is_downloadable'] && $row['is_downloadable'] !== '0' ) ? 1 : 0;
		if ( isset( $row['track_inventory'] ) )                                 $product_data['track_inventory']  = ( $row['track_inventory'] && $row['track_inventory'] !== '0' ) ? 1 : 0;
		if ( isset( $row['allow_backorders'] ) )                                $product_data['allow_backorders'] = ( $row['allow_backorders'] && $row['allow_backorders'] !== '0' ) ? 1 : 0;
		if ( isset( $row['stock_status'] ) && $row['stock_status'] !== '' )     $product_data['stock_status']     = in_array( $row['stock_status'], ['in_stock','out_of_stock','on_backorder'], true ) ? $row['stock_status'] : 'in_stock';
		if ( isset( $row['visibility'] ) && $row['visibility'] !== '' )         $product_data['visibility']       = in_array( $row['visibility'], ['visible','catalog','search','hidden'], true ) ? $row['visibility'] : 'visible';
		if ( isset( $row['featured'] ) )                                        $product_data['featured']         = ( $row['featured'] && $row['featured'] !== '0' ) ? 1 : 0;

		// Set quantity to zero option (for all products being imported).
		// S206 fix: column is `stock_quantity` on tc_products (not the old
		// `amount` column); pre-fix the set_quantity_zero setting silently
		// failed on every CSV row that lacked an explicit `quantity` field
		// because wpdb stripped the unknown `amount` key.
		if ( $set_qty_zero && ! isset( $row['quantity'] ) ) {
			$product_data['stock_quantity'] = 0;
		}

		if ( $existing_id ) {
			$wpdb->update( $products_table, $product_data, [ 'id' => $existing_id ] );
			$product_id = $existing_id;

			// Delete additional images on re-import (option-gated).
			// Schema fix at S202: canonical table is `product_images` (not the
			// nonexistent `image_links`); "additional images" = WHERE is_main = 0.
			// The pre-S202 object_type/object_id/type triple referenced a
			// polymorphic shape that never existed in TC's schema, so this
			// DELETE silently failed on every re-import.
			if ( $delete_images ) {
				$wpdb->query(
					$wpdb->prepare(
						"DELETE FROM {$images_table} WHERE product_id = %d AND is_main = 0",
						$product_id
					)
				);
			}
		} else {
			// New product — set defaults for fields not in CSV
			if ( ! isset( $product_data['price'] ) )     $product_data['price']          = 0.00;
			if ( ! isset( $product_data['status'] ) )     $product_data['status']         = 'active';
			if ( ! isset( $product_data['track_inventory'] ) ) $product_data['track_inventory'] = 1;
			if ( ! isset( $product_data['min_quantity'] ) )    $product_data['min_quantity']   = 1;
			$product_data['created_at'] = current_time( 'mysql' );
			$wpdb->insert( $products_table, $product_data );
			$product_id = (int) $wpdb->insert_id;
			if ( ! $product_id ) {
				throw new \RuntimeException( esc_html( "Failed to insert product: {$wpdb->last_error}" ) );
			}
		}

		// Translation row (only if name or description fields present).
		$has_trans = ! empty( $row['name'] ) || ! empty( $row['description'] ) || ! empty( $row['short_description'] )
					|| ! empty( $row['meta_title'] ) || ! empty( $row['meta_description'] );

		if ( $has_trans ) {
			$name = sanitize_text_field( $row['name'] ?? '' );

			// Slug goes on the products table, not translations
			if ( ! empty( $row['slug'] ) || $name ) {
				$slug = ! empty( $row['slug'] ) ? sanitize_title( $row['slug'] ) : $this->generateSlug( $name, $products_table, 'slug', $product_id );
				$wpdb->update( $products_table, [ 'slug' => $slug ], [ 'id' => $product_id ] );
			}

			$trans_data = [
				'product_id'    => $product_id,
				'language_code' => $lang,
			];
			if ( $name )                                      $trans_data['name']          = $name;
			if ( isset( $row['description'] ) )               $trans_data['description']   = wp_kses_post( $row['description'] );
			if ( isset( $row['short_description'] ) )         $trans_data['short_desc']    = sanitize_textarea_field( $row['short_description'] );
			if ( isset( $row['meta_title'] ) )                $trans_data['meta_title']    = sanitize_text_field( $row['meta_title'] );
			if ( isset( $row['meta_description'] ) )          $trans_data['meta_desc']     = sanitize_textarea_field( $row['meta_description'] );

			$existing_trans = $wpdb->get_var(
				$wpdb->prepare(
					"SELECT id FROM {$trans_table} WHERE product_id = %d AND language_code = %s LIMIT 1",
					$product_id, $lang
				)
			);

			if ( $existing_trans ) {
				$wpdb->update( $trans_table, $trans_data, [ 'product_id' => $product_id, 'language_code' => $lang ] );
			} else {
				$wpdb->insert( $trans_table, $trans_data );
			}
		}

		// Categories (path with /// separator, pipe-separated for multiple).
		if ( ! empty( $row['categories'] ) ) {
			$this->linkProductCategories( $product_id, $row['categories'], $lang, false );
		}
		// Secondary categories (pipe-separated names, appended after primary).
		if ( ! empty( $row['secondary_categories'] ) ) {
			$this->linkProductCategories( $product_id, $row['secondary_categories'], $lang, true );
		}

		// Product attributes/features (keys starting with "feature:")
		$this->writeProductFeatureValues( $product_id, $row );

		// Product Group / Variation linking
		if ( ! empty( $row['variation_group_code'] ) ) {
			$is_default = ! empty( $row['variation_set_as_default'] )
				&& in_array( strtolower( trim( $row['variation_set_as_default'] ) ), [ '1', 'y', 'yes', 'true' ], true );
			$this->linkProductGroup( $product_id, trim( $row['variation_group_code'] ), $is_default );
		}

		// Images (pipe-separated URLs or filenames)
		if ( ! empty( $row['images'] ) ) {
			$this->importProductImages( $product_id, $row['images'], $settings );
		}
	}

	private function writeCategory( array $row, bool $update_existing ): void {
		global $wpdb;
		$cats_table  = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'categories';
		$trans_table = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'category_translations';
		$lang        = function_exists( 'titancart_get_current_language' ) ? titancart_get_current_language() : 'en';

		$name = sanitize_text_field( $row['name'] ?? '' );
		if ( ! $name ) {
			throw new \RuntimeException( 'Category name is empty.' );
		}

		// Resolve parent.
		$parent_id = 0;
		if ( ! empty( $row['parent'] ) ) {
			$parent_id = (int) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT c.id FROM {$cats_table} c
					 JOIN {$trans_table} t ON t.category_id = c.id AND t.language_code = %s
					 WHERE t.name = %s AND c.deleted_at IS NULL LIMIT 1",
					$lang, sanitize_text_field( $row['parent'] )
				)
			);
		}

		// Check for existing by name + parent (when update_existing).
		$existing_id = null;
		if ( $update_existing ) {
			$existing_id = (int) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT c.id FROM {$cats_table} c
					 JOIN {$trans_table} t ON t.category_id = c.id AND t.language_code = %s
					 WHERE c.parent_id = %d AND t.name = %s AND c.deleted_at IS NULL LIMIT 1",
					$lang, $parent_id, $name
				)
			);
		}

		$status = $this->normalizeStatus( $row['status'] ?? 'active', 'active' );
		$slug   = sanitize_title( $row['slug'] ?? $name );

		if ( $existing_id && $update_existing ) {
			$wpdb->update( $cats_table, [
				'parent_id'  => $parent_id,
				'slug'       => $slug,
				'status'     => $status,
				'updated_at' => current_time( 'mysql' ),
			], [ 'id' => $existing_id ] );
			$cat_id = $existing_id;
		} else {
			$wpdb->insert( $cats_table, [
				'parent_id'  => $parent_id,
				'slug'       => $slug,
				'status'     => $status,
				'created_at' => current_time( 'mysql' ),
				'updated_at' => current_time( 'mysql' ),
			] );
			$cat_id = (int) $wpdb->insert_id;
			if ( ! $cat_id ) {
				throw new \RuntimeException( esc_html( "Failed to insert category: {$wpdb->last_error}" ) );
			}
		}

		$trans_data = [
			'category_id'   => $cat_id,
			'language_code' => $lang,
			'name'          => $name,
			'description'   => wp_kses_post( $row['description'] ?? '' ),
			'meta_title'    => sanitize_text_field( $row['meta_title'] ?? '' ),
			'meta_desc'     => sanitize_textarea_field( $row['meta_description'] ?? '' ),
		];

		$existing_trans = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT id FROM {$trans_table} WHERE category_id = %d AND language_code = %s LIMIT 1",
				$cat_id, $lang
			)
		);

		if ( $existing_trans ) {
			$wpdb->update( $trans_table, $trans_data, [ 'category_id' => $cat_id, 'language_code' => $lang ] );
		} else {
			$wpdb->insert( $trans_table, $trans_data );
		}
	}

	private function writeCustomer( array $row, bool $update_existing ): void {
		global $wpdb;
		$customers_table  = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'customers';
		$addresses_table  = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'customer_addresses';

		$email = sanitize_email( $row['email'] ?? '' );
		if ( ! $email ) {
			throw new \RuntimeException( 'Customer email is empty.' );
		}

		// Check for existing WP user by email.
		$wp_user = get_user_by( 'email', $email );
		$user_id = 0;

		if ( $wp_user && $update_existing ) {
			$user_id = $wp_user->ID;
		} elseif ( ! $wp_user ) {
			// Create WP user (no password — they'll use "forgot password").
			$user_id = wp_insert_user( [
				'user_email'   => $email,
				'user_login'   => $email,
				'first_name'   => sanitize_text_field( $row['first_name'] ?? '' ),
				'last_name'    => sanitize_text_field( $row['last_name'] ?? '' ),
				'display_name' => trim( sanitize_text_field( ( $row['first_name'] ?? '' ) . ' ' . ( $row['last_name'] ?? '' ) ) ) ?: $email,
				'role'         => 'customer',
				'user_pass'    => wp_generate_password( 16 ),
			] );

			if ( is_wp_error( $user_id ) ) {
				throw new \RuntimeException( esc_html( "WP user creation failed for {$email}: " . $user_id->get_error_message() ) );
			}
		} else {
			// User exists but update_existing is false — skip silently.
			return;
		}

		// S203 — Upsert tc_customers row.
		// Schema (canonical): tc_customers.wp_user_id BIGINT UNSIGNED NULL.
		// Pre-S203 this used `user_id` (column does not exist — Pattern #167
		// column-name lapse, same bug class as S202's writeReview rewrite).
		$existing_tc = $wpdb->get_var(
			$wpdb->prepare( "SELECT id FROM {$customers_table} WHERE wp_user_id = %d LIMIT 1", $user_id )
		);

		// S203 — `company` column does NOT exist on tc_customers (verified
		// against Schema.php::customers L1649-1672 at S203 disk-survey). The
		// pre-S203 INSERT here would have failed silently with "Unknown column
		// 'company'" if any import row populated it. Company data flows
		// naturally to customer_addresses below (which DOES have the column).
		$customer_data = [
			'wp_user_id' => $user_id,
			'email'      => $email,
			'first_name' => sanitize_text_field( $row['first_name'] ?? '' ),
			'last_name'  => sanitize_text_field( $row['last_name'] ?? '' ),
			'phone'      => sanitize_text_field( $row['phone'] ?? '' ),
		];

		if ( $existing_tc ) {
			$wpdb->update( $customers_table, $customer_data, [ 'id' => $existing_tc ] );
			$tc_customer_id = (int) $existing_tc;
		} else {
			$customer_data['created_at'] = current_time( 'mysql' );
			$wpdb->insert( $customers_table, $customer_data );
			$tc_customer_id = (int) $wpdb->insert_id;
		}

		// S203 — Address row (if address data present).
		// Schema (canonical): tc_customer_addresses.customer_id BIGINT UNSIGNED
		// NOT NULL — FK to tc_customers.id (NOT the WP user_id). Pre-S203 this
		// inserted `user_id` which does not exist on the schema (Pattern #167
		// column-name lapse — addresses FK target mismatch). Now correctly
		// scoped to the tc_customers row captured above.
		if ( ! empty( $row['address_1'] ) && $tc_customer_id > 0 ) {
			$addr_data = [
				'customer_id' => $tc_customer_id,
				'type'        => 'billing',
				'is_default'  => 1,
				'first_name'  => sanitize_text_field( $row['first_name'] ?? '' ),
				'last_name'   => sanitize_text_field( $row['last_name'] ?? '' ),
				'company'     => sanitize_text_field( $row['company'] ?? '' ),
				'address_1'   => sanitize_text_field( $row['address_1'] ),
				'address_2'   => sanitize_text_field( $row['address_2'] ?? '' ),
				'city'        => sanitize_text_field( $row['city'] ?? '' ),
				'state'       => sanitize_text_field( $row['state'] ?? '' ),
				'postcode'    => sanitize_text_field( $row['postcode'] ?? '' ),
				'country'     => strtoupper( sanitize_text_field( $row['country'] ?? '' ) ),
			];
			$wpdb->insert( $addresses_table, $addr_data );
		}
	}

	// ── Order writer ─────────────────────────────────────────────────────────
	//
	// S206 structural rewrite. Pre-rewrite this method mirrored a legacy
	// legacy order shape — single-char status codes ('O'/'P'/'C'/'F'/'D'/
	// 'B'/'I'), JSON address blobs, and columns `email`/`tax_amount`/
	// `currency_code`/`customer_notes`/`admin_notes`/`billing_address`/
	// `shipping_address` that don't exist on TC's tc_orders. Every CSV order
	// import silently produced rows that wpdb half-stripped (unknown columns
	// dropped, known ones written with NULL defaults), making imported orders
	// invisible to the SPA. Root cause: ImportService was built early in the
	// 14-module sweep against the legacy functional reference per Rule 2;
	// that legacy column SHAPE leaked into TC's importer; as Modules 1/4/5/6 +
	// Customer Address split-out evolved canonical schema across S30-S110,
	// the importer was never re-walked.
	//
	// S206 fix delegates to OrderRepository::create_order — the canonical
	// path CheckoutManager itself uses for natively-created orders. This
	// makes imported orders byte-identical to natively-created ones, fires
	// the `titancart_order_item_data` filter so Track B marketplace can stamp
	// vendor_id on imported line items (S190 Path B1), and makes column
	// drift impossible going forward: any future schema change ripples
	// through OrderRepository to here automatically.
	//
	// Pattern #176-candidate (logged S205) graduates after S208 ExportService
	// validation: schema-dependent peripheral modules need lockstep audits at
	// canonical-schema column-rename time; delegating to the canonical path
	// instead of duplicating it is the structural defense.

	private function writeOrder( array $row, bool $update_existing ): void {
		$data = $this->mapCsvRowToOrderData( $row );
		if ( $data === null ) {
			throw new \RuntimeException( 'Order CSV row missing required billing_email or email column.' );
		}

		global $wpdb;
		$orders_table = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'orders';

		// Existence check by order_number (the human-readable UNIQUE identifier).
		// CSV `order_number` is the authoritative key; CSV `order_id` is accepted
		// as a legacy alias inside mapCsvRowToOrderData (where it lands in
		// $data['order_number']) — never used as the DB `id` (auto_increment).
		$existing_id = (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT id FROM {$orders_table} WHERE order_number = %s LIMIT 1",
			$data['order_number']
		) );

		if ( $existing_id ) {
			if ( ! $update_existing ) {
				return; // skip duplicate; operator opted not to overwrite
			}
			$this->updateExistingOrder( $existing_id, $data );
			return;
		}

		// New order — delegate to canonical insert path.
		$repo = new \TitanCart\Modules\Checkout\OrderRepository();
		$repo->create_order( $data );
	}

	/**
	 * Map a CSV row to OrderRepository::create_order()'s $data shape.
	 *
	 * Pure mapping — no DB writes. Resolves customer_id by email lookup and
	 * generates order_number/order_key when CSV doesn't supply them. The
	 * `status` value is passed through as a TC handle string ('pending',
	 * 'processing', 'completed', 'cancelled', 'refunded', 'on_hold',
	 * 'failed'); OrderRepository::resolve_status_id() resolves it to the FK
	 * id at insert time. Unknown handles resolve to 0 (defensive — never
	 * throws).
	 *
	 * Used both by writeOrder (real insert path) and by inspectOrderMapping
	 * (Pattern #170 diagnostic surface). Returns null if billing_email cannot
	 * be derived from any of the CSV column aliases.
	 *
	 * CSV header aliases accepted (backward-compat for operators with legacy
	 * legacy-platform-shaped CSVs):
	 *   - billing_email | email
	 *   - billing_postcode | billing_zipcode
	 *   - shipping_postcode | shipping_zipcode
	 *   - currency | currency_code
	 *   - tax_total | tax_amount
	 *   - customer_note | customer_notes
	 *   - order_number | order_id
	 *
	 * Shipping fields fall back to billing equivalents when blank (matches
	 * native checkout's "ship to billing address" behavior — denormalized
	 * snapshot per OrderRepository docblock L8-10).
	 *
	 * @param  array $row CSV row keyed by header (column mapping already applied).
	 * @return array|null OrderRepository::create_order() $data, or null if invalid.
	 */
	private function mapCsvRowToOrderData( array $row ): ?array {
		$email = sanitize_email( $row['billing_email'] ?? $row['email'] ?? '' );
		if ( $email === '' ) {
			return null;
		}

		$status_handle = trim( (string) ( $row['status'] ?? 'pending' ) );
		if ( $status_handle === '' ) {
			$status_handle = 'pending';
		}

		$customer_id = $this->lookupCustomerByEmail( $email );

		// Order number: prefer CSV `order_number`, fall back to legacy `order_id`,
		// fall back to auto-generation. CSV `order_id` is NEVER used as the DB
		// `id` (auto_increment) — it's accepted only as an alias for human-
		// readable order_number.
		$order_number = trim( (string) ( $row['order_number'] ?? $row['order_id'] ?? '' ) );
		if ( $order_number === '' ) {
			$repo         = new \TitanCart\Modules\Checkout\OrderRepository();
			$order_number = $repo->generate_order_number();
		}

		// Address resolution: shipping fields fall back to billing equivalents
		// when blank (precomputed once so the shipping array reads cleanly).
		$billing_first = sanitize_text_field( $row['billing_first_name'] ?? '' );
		$billing_last  = sanitize_text_field( $row['billing_last_name']  ?? '' );
		$billing_addr1 = sanitize_text_field( $row['billing_address_1']  ?? '' );
		$billing_city  = sanitize_text_field( $row['billing_city']       ?? '' );
		$billing_state = sanitize_text_field( $row['billing_state']      ?? '' );
		$billing_post  = sanitize_text_field( $row['billing_postcode'] ?? $row['billing_zipcode'] ?? '' );
		$billing_ctry  = strtoupper( sanitize_text_field( $row['billing_country'] ?? '' ) );

		return [
			// Identity
			'customer_id'  => $customer_id,
			'order_number' => $order_number,
			'order_key'    => \TitanCart\Modules\Checkout\OrderRepository::generate_order_key(),
			'status'       => $status_handle,
			'ip_address'   => sanitize_text_field( $row['ip_address'] ?? '' ),
			'user_agent'   => sanitize_text_field( $row['user_agent'] ?? '' ),

			// Billing (denormalized snapshot per OrderRepository contract)
			'billing' => [
				'first_name' => $billing_first,
				'last_name'  => $billing_last,
				'company'    => sanitize_text_field( $row['billing_company'] ?? '' ),
				'email'      => $email,
				'phone'      => sanitize_text_field( $row['billing_phone'] ?? '' ),
				'address_1'  => $billing_addr1,
				'address_2'  => sanitize_text_field( $row['billing_address_2'] ?? '' ),
				'city'       => $billing_city,
				'state'      => $billing_state,
				'postcode'   => $billing_post,
				'country'    => $billing_ctry,
			],

			// Shipping (denormalized snapshot; falls back to billing where blank)
			'shipping' => [
				'first_name' => sanitize_text_field( $row['shipping_first_name'] ?? '' ) ?: $billing_first,
				'last_name'  => sanitize_text_field( $row['shipping_last_name']  ?? '' ) ?: $billing_last,
				'company'    => sanitize_text_field( $row['shipping_company']    ?? '' ),
				'address_1'  => sanitize_text_field( $row['shipping_address_1']  ?? '' ) ?: $billing_addr1,
				'address_2'  => sanitize_text_field( $row['shipping_address_2']  ?? '' ),
				'city'       => sanitize_text_field( $row['shipping_city']       ?? '' ) ?: $billing_city,
				'state'      => sanitize_text_field( $row['shipping_state']      ?? '' ) ?: $billing_state,
				'postcode'   => sanitize_text_field( $row['shipping_postcode'] ?? $row['shipping_zipcode'] ?? '' ) ?: $billing_post,
				'country'    => strtoupper( sanitize_text_field( $row['shipping_country'] ?? '' ) ) ?: $billing_ctry,
			],

			// Financials
			'currency'          => strtoupper( sanitize_text_field( $row['currency'] ?? $row['currency_code'] ?? 'USD' ) ),
			'subtotal'          => (float) ( $row['subtotal']          ?? 0 ),
			'discount'          => (float) ( $row['discount']          ?? 0 ),
			'shipping_cost'     => (float) ( $row['shipping_cost']     ?? 0 ),
			'tax_total'         => (float) ( $row['tax_total']       ?? $row['tax_amount'] ?? 0 ),
			'payment_surcharge' => (float) ( $row['payment_surcharge'] ?? 0 ),
			'total'             => (float) ( $row['total']             ?? 0 ),

			// Payment / shipping method (nullable; CSV operators rarely supply)
			'payment_method_id'  => isset( $row['payment_method_id'] )  && $row['payment_method_id']  !== '' ? (int) $row['payment_method_id']  : null,
			'shipping_method_id' => isset( $row['shipping_method_id'] ) && $row['shipping_method_id'] !== '' ? (int) $row['shipping_method_id'] : null,

			// Extras
			'coupon_code' => ! empty( $row['coupon_code'] ) ? sanitize_text_field( $row['coupon_code'] ) : null,
			'notes'       => ! empty( $row['customer_note'] )  ? sanitize_textarea_field( $row['customer_note'] )
			              : ( ! empty( $row['customer_notes'] ) ? sanitize_textarea_field( $row['customer_notes'] ) : null ),
		];
	}

	/**
	 * Look up a customer's `id` by email (UNIQUE on tc_customers.email, 191-
	 * prefix index). Excludes soft-deleted rows. Returns null when no
	 * matching customer exists — imported order is then created with
	 * customer_id NULL (guest-style order; nullable per Schema.php::orders
	 * L1055).
	 *
	 * @param  string $email
	 * @return int|null
	 */
	private function lookupCustomerByEmail( string $email ): ?int {
		if ( $email === '' ) {
			return null;
		}
		global $wpdb;
		$customers_table = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'customers';
		$id = (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT id FROM {$customers_table} WHERE email = %s AND deleted_at IS NULL LIMIT 1",
			$email
		) );
		return $id > 0 ? $id : null;
	}

	/**
	 * Update safe-to-rewrite columns on an existing order found by
	 * order_number. Deliberately NEVER updates: order_key (security — non-
	 * enumerable token), order_number (UNIQUE constraint), totals/financials
	 * (financial record integrity), customer_id (ownership integrity),
	 * status_id (state machine lives in OrderManager::update_status which
	 * fires hooks), created_at (immutable).
	 *
	 * Only updates address snapshot + customer_note + updated_at. This matches
	 * the same "address corrections on re-import" semantics without breaking
	 * financial idempotency.
	 *
	 * @param int   $order_id Existing order row primary key.
	 * @param array $data     OrderRepository::create_order $data shape.
	 */
	private function updateExistingOrder( int $order_id, array $data ): void {
		global $wpdb;
		$orders_table = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'orders';

		$wpdb->update( $orders_table, [
			'billing_first_name'  => $data['billing']['first_name'],
			'billing_last_name'   => $data['billing']['last_name'],
			'billing_company'     => $data['billing']['company']   ?? null,
			'billing_email'       => $data['billing']['email'],
			'billing_phone'       => $data['billing']['phone']     ?? null,
			'billing_address_1'   => $data['billing']['address_1'],
			'billing_address_2'   => $data['billing']['address_2'] ?? null,
			'billing_city'        => $data['billing']['city'],
			'billing_state'       => $data['billing']['state']     ?? null,
			'billing_postcode'    => $data['billing']['postcode'],
			'billing_country'     => $data['billing']['country'],
			'shipping_first_name' => $data['shipping']['first_name'],
			'shipping_last_name'  => $data['shipping']['last_name'],
			'shipping_company'    => $data['shipping']['company']   ?? null,
			'shipping_address_1'  => $data['shipping']['address_1'],
			'shipping_address_2'  => $data['shipping']['address_2'] ?? null,
			'shipping_city'       => $data['shipping']['city'],
			'shipping_state'      => $data['shipping']['state']     ?? null,
			'shipping_postcode'   => $data['shipping']['postcode'],
			'shipping_country'    => $data['shipping']['country'],
			'customer_note'       => $data['notes'],
			'updated_at'          => current_time( 'mysql' ),
		], [ 'id' => $order_id ] );
	}

	/**
	 * Diagnostic-only inspector — returns the resolved $data dict + the
	 * resolved status_id (post lookup against tc_order_statuses) + an upsert
	 * preview (existing order id if any), WITHOUT performing any DB write.
	 *
	 * Pattern #170 standing surface (codified S196, lineage extended S205).
	 * Sean's live environment lacks shell access; diagnostic mu-plugins
	 * driven via browser URL are how we validate write paths without
	 * round-tripping through the cron'd batch processor. Companion mu-plugin:
	 * `_s206-import-diag.php` (mode=order_mapping).
	 *
	 * @param  array $row CSV row keyed by header (column mapping already applied).
	 * @return array Flat JSON-friendly dict of every column mapping decision.
	 */
	public function inspectOrderMapping( array $row ): array {
		$data = $this->mapCsvRowToOrderData( $row );
		if ( $data === null ) {
			return [
				'valid'     => false,
				'reason'    => 'billing_email/email column missing or empty',
				'input_row' => $row,
			];
		}

		// Resolve status_handle → status_id the same way OrderRepository will.
		global $wpdb;
		$statuses_table = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'order_statuses';
		$resolved_status_id = (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT id FROM {$statuses_table} WHERE handle = %s LIMIT 1",
			$data['status']
		) );

		// Upsert preview: check whether this order_number already exists.
		$orders_table = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'orders';
		$existing_id  = (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT id FROM {$orders_table} WHERE order_number = %s LIMIT 1",
			$data['order_number']
		) );

		return [
			'valid'                 => true,
			'resolved_customer_id'  => $data['customer_id'],
			'resolved_status_id'    => $resolved_status_id,
			'status_handle'         => $data['status'],
			'order_number'          => $data['order_number'],
			'order_number_auto_gen' => '' === trim( (string) ( $row['order_number'] ?? $row['order_id'] ?? '' ) ),
			'order_key'             => $data['order_key'],
			'would_upsert_existing' => $existing_id > 0 ? $existing_id : null,
			'mapped_data'           => $data,
		];
	}

	// ── Order item writer ────────────────────────────────────────────────────
	//
	// S207 structural rewrite. Pre-rewrite this method mirrored a legacy
	// legacy order-details shape — `product_code` on tc_products (canonical
	// column is `sku`), `lang_code` on tc_product_translations (canonical is
	// `language_code`), and columns `product_name`/`product_sku`/`price`/
	// `amount`/`extra_data` that don't exist on TC's tc_order_items
	// (canonical: `name`/`sku`/`unit_price`/`quantity`/`meta`). Every CSV
	// order-item import silently produced rows wpdb half-stripped (unknown
	// columns dropped, known ones written with NULL/0 defaults), making
	// imported line items invisible to the SPA + vendor portal. Same
	// architectural root cause as S206's writeOrder rewrite — the same legacy
	// column shape leaked into TC's importer; later schema evolution never
	// re-walked the importer.
	//
	// S207 fix delegates to OrderRepository::create_order_items — the
	// canonical insert path CheckoutManager uses for natively-created line
	// items. This makes imported line items byte-identical to natively-
	// created ones AND fires the `titancart_order_item_data` filter (S190
	// Path B1) so the marketplace extension's VendorOrderItemStamper stamps
	// `vendor_id` from the active tc_vendor_products junction automatically.
	// Imported items on Sam-owned products land with vendor_id=1 with zero
	// new code in ImportService — the structural antidote pays for itself
	// the moment it lands.
	//
	// Pattern #177-candidate (logged S206, second instance S207): delegate-
	// to-canonical for peripheral writers. If S208's ExportService audit
	// applies the read-path twin, Pattern #177 graduates from candidate to
	// codified default alongside Pattern #176 — the structural and
	// procedural defenses ship together.

	private function writeOrderItem( array $row ): void {
		// Required identifiers from CSV (with backward-compat aliases).
		$order_lookup = trim( (string) ( $row['order_number'] ?? $row['order_id']    ?? '' ) );
		$sku          = trim( (string) ( $row['sku']          ?? $row['product_code'] ?? '' ) );

		if ( $order_lookup === '' || $sku === '' ) {
			throw new \RuntimeException( 'Order item CSV row requires order_number (or order_id) AND sku (or product_code).' );
		}

		// Resolve parent order_id from order_number (NOT raw id — operators
		// supply human-readable numbers, never internal AUTO_INCREMENT ids).
		$order_id = $this->lookupOrderByNumber( $order_lookup );
		if ( ! $order_id ) {
			throw new \RuntimeException( esc_html( sprintf( 'Order item references unknown order_number "%s".', $order_lookup ) ) );
		}

		// Resolve product row (id + price) for hydration / fallback price.
		$product = $this->lookupProductBySku( $sku );
		if ( ! $product ) {
			throw new \RuntimeException( esc_html( sprintf( 'Order item references unknown product sku "%s".', $sku ) ) );
		}

		$line = $this->mapCsvRowToOrderItemLine( $row, $product );

		// Delegate to canonical insert path — fires `titancart_order_item_data`
		// filter so the marketplace listener stamps vendor_id automatically.
		$repo = new \TitanCart\Modules\Checkout\OrderRepository();
		$repo->create_order_items( $order_id, [ $line ] );
	}

	/**
	 * Map a CSV row to OrderRepository::create_order_items()'s line shape.
	 *
	 * Pure mapping — no DB writes. The returned array matches the hydrated-
	 * line contract OrderRepository expects (see OrderRepository L125-145):
	 * product_id, variation_id, name, sku, quantity, effective_price,
	 * line_total, discount_amount, tax_amount, meta.
	 *
	 * `meta` is passed through unchanged; OrderRepository::create_order_items
	 * normalizes it at insert time (array → wp_json_encode; JSON string →
	 * decode-then-encode; empty → NULL).
	 *
	 * CSV header aliases accepted (backward-compat for operators with legacy
	 * legacy-platform-shaped CSVs):
	 *   - sku             | product_code
	 *   - quantity        | amount
	 *   - unit_price      | price       (fallback: current product price)
	 *   - name            | product_name (fallback: product translation; last-ditch sku)
	 *   - meta            | extra_data  | extra
	 *
	 * @param  array $row      CSV row keyed by header.
	 * @param  array $product  Product row from lookupProductBySku (id + price).
	 * @return array           Hydrated line in create_order_items contract shape.
	 */
	private function mapCsvRowToOrderItemLine( array $row, array $product ): array {
		$sku      = trim( (string) ( $row['sku'] ?? $row['product_code'] ?? '' ) );
		$quantity = max( 1, (int) ( $row['quantity'] ?? $row['amount'] ?? 1 ) );

		// Unit price: CSV value wins; fall back to current product price.
		// (Defensive — operators occasionally import partial-row CSVs and
		// expect "use the current product price" semantics for missing values.)
		$unit_price_raw = $row['unit_price'] ?? $row['price'] ?? null;
		$unit_price     = ( $unit_price_raw !== null && $unit_price_raw !== '' )
		                  ? (float) $unit_price_raw
		                  : (float) $product['price'];

		// Item name: CSV value wins; fall back to product's translated name;
		// last-ditch fall back to sku (never insert a blank `name` column).
		$name = sanitize_text_field( $row['name'] ?? $row['product_name'] ?? '' );
		if ( $name === '' ) {
			$name = $this->lookupProductName( (int) $product['id'] );
		}
		if ( $name === '' ) {
			$name = $sku;
		}

		// Financials: derive subtotal from unit_price × quantity when CSV
		// doesn't supply a `line_total` column; discount/tax default to 0
		// when absent (matches OrderRepository::create_order_items L127-131).
		$line_total      = ( isset( $row['line_total'] ) && $row['line_total'] !== '' )
		                   ? (float) $row['line_total']
		                   : $unit_price * $quantity;
		$discount_amount = (float) ( $row['discount'] ?? 0 );
		$tax_amount      = (float) ( $row['tax']      ?? 0 );

		// Variation: optional CSV column for variable products. Null when
		// unspecified — schema NULLABLE, no fallback needed.
		$variation_id = ( isset( $row['variation_id'] ) && $row['variation_id'] !== '' )
		                ? (int) $row['variation_id']
		                : null;

		// Meta: pass through raw (string/array/null). OrderRepository
		// normalizes to JSON on insert. Empty string → null.
		$meta = $row['meta'] ?? $row['extra_data'] ?? $row['extra'] ?? null;
		if ( $meta === '' ) {
			$meta = null;
		}

		return [
			'product_id'      => (int) $product['id'],
			'variation_id'    => $variation_id,
			'name'            => $name,
			'sku'             => $sku,
			'quantity'        => $quantity,
			'effective_price' => $unit_price,
			'line_total'      => $line_total,
			'discount_amount' => $discount_amount,
			'tax_amount'      => $tax_amount,
			'meta'            => $meta,
		];
	}

	/**
	 * Look up an order's `id` by the human-readable `order_number`
	 * (UNIQUE on tc_orders.order_number). Returns null when no match.
	 *
	 * @param  string $order_number Authoritative parent-order identifier from CSV.
	 * @return int|null
	 */
	private function lookupOrderByNumber( string $order_number ): ?int {
		if ( $order_number === '' ) {
			return null;
		}
		global $wpdb;
		$orders_table = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'orders';
		$id = (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT id FROM {$orders_table} WHERE order_number = %s LIMIT 1",
			$order_number
		) );
		return $id > 0 ? $id : null;
	}

	/**
	 * Look up a product row (id + price) by SKU (tc_products.sku — not a
	 * UNIQUE index but conventionally distinct). Returns the row as an
	 * associative array, or null when no match. LIMIT 1 → if duplicate SKUs
	 * exist in data, the lowest-id wins (deterministic).
	 *
	 * @param  string $sku
	 * @return array|null  ['id' => string, 'price' => string] (DB cast forms) or null.
	 */
	private function lookupProductBySku( string $sku ): ?array {
		if ( $sku === '' ) {
			return null;
		}
		global $wpdb;
		$products_table = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'products';
		$row = $wpdb->get_row( $wpdb->prepare(
			"SELECT id, price FROM {$products_table} WHERE sku = %s LIMIT 1",
			$sku
		), ARRAY_A );
		return $row ?: null;
	}

	/**
	 * Look up a product's display name from tc_product_translations for the
	 * current site language. Returns empty string when no match.
	 *
	 * Note: the canonical column on tc_product_translations is `language_code`
	 * (CHAR(2)); pre-S207 writeOrderItem referenced the non-existent
	 * `lang_code` column, which caused every product-name fallback to fail.
	 *
	 * @param  int $product_id
	 * @return string
	 */
	private function lookupProductName( int $product_id ): string {
		if ( $product_id <= 0 ) {
			return '';
		}
		global $wpdb;
		$trans_table = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'product_translations';
		$lang        = function_exists( 'titancart_get_current_language' ) ? titancart_get_current_language() : 'en';
		return (string) $wpdb->get_var( $wpdb->prepare(
			"SELECT name FROM {$trans_table} WHERE product_id = %d AND language_code = %s LIMIT 1",
			$product_id,
			$lang
		) );
	}

	/**
	 * Diagnostic-only inspector — mirrors inspectOrderMapping (S206) for
	 * order-item CSV rows. Returns the resolved hydrated line + parent
	 * order_id + product_id + the marketplace vendor stamping preview
	 * (read-only — does NOT fire the filter), without performing any DB
	 * write.
	 *
	 * Pattern #170 standing surface — companion mu-plugin
	 * `_s206-import-diag.php` is extended at S207 with a second dispatch
	 * handler at `?tc_s207_diag_import_items=1`.
	 *
	 * The `expected_vendor_stamp` field is the writeOrderItem rewrite's
	 * most critical structural guarantee: when delegation to
	 * create_order_items fires the filter, the marketplace listener
	 * resolves vendor_id from tc_vendor_products. The inspector reads that
	 * same junction so the operator can confirm BEFORE insert which vendor
	 * (if any) the line will be stamped with.
	 *
	 * @param  array $row CSV row keyed by header.
	 * @return array      Flat JSON-friendly dict of every mapping decision.
	 */
	public function inspectOrderItemMapping( array $row ): array {
		$order_lookup = trim( (string) ( $row['order_number'] ?? $row['order_id']    ?? '' ) );
		$sku          = trim( (string) ( $row['sku']          ?? $row['product_code'] ?? '' ) );

		if ( $order_lookup === '' || $sku === '' ) {
			return [
				'valid'     => false,
				'reason'    => 'order_number/order_id AND sku/product_code required',
				'input_row' => $row,
			];
		}

		$order_id = $this->lookupOrderByNumber( $order_lookup );
		$product  = $this->lookupProductBySku( $sku );

		if ( ! $order_id ) {
			return [
				'valid'               => false,
				'reason'              => "parent order_number '{$order_lookup}' not found in tc_orders",
				'resolved_order_id'   => null,
				'resolved_product_id' => $product ? (int) $product['id'] : null,
				'input_row'           => $row,
			];
		}
		if ( ! $product ) {
			return [
				'valid'               => false,
				'reason'              => "product sku '{$sku}' not found in tc_products",
				'resolved_order_id'   => $order_id,
				'resolved_product_id' => null,
				'input_row'           => $row,
			];
		}

		$line = $this->mapCsvRowToOrderItemLine( $row, $product );

		// Vendor stamp preview: read what the marketplace listener WOULD
		// resolve at filter time, without firing the filter (read-only
		// SELECT against tc_vendor_products). NULL when:
		//   (a) marketplace extension not installed → table doesn't exist
		//   (b) product not currently mapped to any vendor
		//   (c) product was vendor-owned but mapping is soft-deleted
		$vendor_id_preview = $this->previewVendorStamp( (int) $product['id'] );

		return [
			'valid'                 => true,
			'resolved_order_id'     => $order_id,
			'resolved_product_id'   => (int) $product['id'],
			'product_price'         => (float) $product['price'],
			'expected_vendor_stamp' => $vendor_id_preview,
			'mapped_line'           => $line,
		];
	}

	/**
	 * Read-only preview of what the marketplace VendorOrderItemStamper
	 * listener would resolve at filter time. Returns null when the
	 * marketplace extension isn't installed (tc_vendor_products absent)
	 * or when no active mapping exists for the product.
	 *
	 * Mirrors VendorOrderItemStamper::stamp_vendor_id (L137-149) lookup
	 * shape so the preview is byte-identical to what the listener would
	 * stamp.
	 *
	 * @param  int $product_id
	 * @return int|null
	 */
	private function previewVendorStamp( int $product_id ): ?int {
		if ( $product_id <= 0 ) {
			return null;
		}
		global $wpdb;
		$vp_table = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'vendor_products';

		// Check table exists without throwing on marketplace-absent installs.
		// SHOW TABLES LIKE is cheap; result cached at MySQL level.
		$exists = (string) $wpdb->get_var( $wpdb->prepare(
			"SHOW TABLES LIKE %s",
			$vp_table
		) );
		if ( $exists === '' ) {
			return null;
		}

		$vendor_id = $wpdb->get_var( $wpdb->prepare(
			"SELECT vendor_id FROM {$vp_table} WHERE product_id = %d AND deleted_at IS NULL LIMIT 1",
			$product_id
		) );
		return $vendor_id !== null ? (int) $vendor_id : null;
	}

	// ── Review writer ────────────────────────────────────────────────────────

	private function writeReview( array $row ): void {
		global $wpdb;
		$reviews_table   = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'reviews';
		$products_table  = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'products';
		$customers_table = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'customers';

		$product_code = trim( $row['product_code'] ?? '' );
		$rating       = (int) ( $row['rating'] ?? 0 );
		if ( ! $product_code || $rating < 1 || $rating > 5 ) {
			throw new \RuntimeException( 'Product code and rating (1-5) are required for reviews.' );
		}

		$product_id = (int) $wpdb->get_var(
			$wpdb->prepare( "SELECT id FROM {$products_table} WHERE product_code = %s LIMIT 1", $product_code )
		);
		if ( ! $product_id ) {
			throw new \RuntimeException( esc_html( "Product not found for code: {$product_code}" ) );
		}

		// Customer lookup by email — best-effort per S202 policy lock:
		//   - empty email          → customer_id = NULL (legitimate guest review path,
		//                            schema permits NULL by design)
		//   - email set, matches   → customer_id = found id
		//   - email set, no match  → customer_id = NULL + log warning
		//                            (legacy migration friendly; WooCommerce and similar
		//                            exports routinely contain orphaned reviewer accounts)
		$email       = sanitize_email( $row['email'] ?? '' );
		$customer_id = null;
		if ( $email !== '' ) {
			$found = (int) $wpdb->get_var(
				$wpdb->prepare( "SELECT id FROM {$customers_table} WHERE email = %s LIMIT 1", $email )
			);
			if ( $found > 0 ) {
				$customer_id = $found;
			} else {
				titancart_log(
					"[TitanCart Import] writeReview: reviewer email '{$email}' not matched to a customer; "
					. "imported as guest review for product_code={$product_code}."
				);
			}
		}

		// Optional vendor_rating (S201 column) — pulled from CSV `vendor_rating`
		// field when present. Validate 1-5 OR NULL; values outside the range
		// degrade to NULL silently (matches REST review submission semantics).
		$vendor_rating = null;
		if ( isset( $row['vendor_rating'] ) && $row['vendor_rating'] !== '' ) {
			$vr = (int) $row['vendor_rating'];
			if ( $vr >= 1 && $vr <= 5 ) {
				$vendor_rating = $vr;
			}
		}

		// vendor_id is NOT resolved at import time — TC core stays vendor-agnostic
		// per Rule 4. Marketplace's `titancart_review_data` filter (Pattern #158
		// ninth instance, S201) stamps vendor_id at REST review submission, but
		// ImportService bypasses ReviewManager::create() for performance. Operators
		// importing vendor-attributed historical reviews can either (a) run a
		// one-off VendorReviewAggregator::recalc_for_vendor() per vendor after
		// import, or (b) plumb a CSV `vendor_id` column through here in a future
		// session if the use case emerges.

		// reviews.status ENUM at S201: pending|approved|rejected|spam.
		$status = in_array( $row['status'] ?? 'approved', [ 'pending', 'approved', 'rejected', 'spam' ], true )
			? $row['status']
			: 'approved';

		// CSV field names: prefer canonical `body` if the operator mapped to it,
		// fall back to legacy `content` for backwards compat with the pre-S202
		// (broken) column map. `title` and `ip_address` are unchanged.
		//
		// Rule 4 debt acknowledged: rest of ImportService uses raw $wpdb
		// throughout; full ImportService → titancart_db() migration belongs to a
		// dedicated future session, not this B10 broken-on-disk pass.
		$wpdb->insert( $reviews_table, [
			'product_id'    => $product_id,
			'customer_id'   => $customer_id,
			'rating'        => $rating,
			'vendor_rating' => $vendor_rating,
			'title'         => sanitize_text_field( $row['title'] ?? '' ),
			'body'          => sanitize_textarea_field( $row['body'] ?? $row['content'] ?? '' ),
			'status'        => $status,
			'ip_address'    => sanitize_text_field( $row['ip_address'] ?? '' ),
			'created_at'    => ! empty( $row['date'] ) ? sanitize_text_field( $row['date'] ) : current_time( 'mysql' ),
		] );
	}

	// ── Waitlist/subscriber writer ───────────────────────────────────────────

	private function writeWaitlist( array $row ): void {
		global $wpdb;
		// Schema fix at S202: canonical table is `product_waitlist` (not the
		// nonexistent `waitlist`). Column names already match canonical schema
		// (product_id, email, name, created_at — see Schema::product_waitlist()).
		$waitlist_table = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'product_waitlist';
		$products_table = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'products';

		$email = sanitize_email( $row['email'] ?? '' );
		if ( ! $email ) {
			throw new \RuntimeException( 'Email is required for waitlist entries.' );
		}

		$product_id = 0;
		if ( ! empty( $row['product_code'] ) ) {
			// S206 fix: DB column is `sku` on tc_products (not the old
			// `product_code`). CSV header remains `product_code` per TC vocabulary
			// discipline (user-facing label is "Product Code"); only the column
			// name in the SELECT changes. Pre-fix the lookup returned NULL on
			// every waitlist CSV row, dropping product_id silently to 0.
			$product_id = (int) $wpdb->get_var(
				$wpdb->prepare( "SELECT id FROM {$products_table} WHERE sku = %s LIMIT 1", $row['product_code'] )
			);
		}

		// Check for duplicate
		$exists = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT id FROM {$waitlist_table} WHERE email = %s AND product_id = %d LIMIT 1",
				$email, $product_id
			)
		);
		if ( $exists ) {
			return; // skip duplicate
		}

		$wpdb->insert( $waitlist_table, [
			'product_id' => $product_id,
			'email'      => $email,
			'name'       => sanitize_text_field( $row['name'] ?? '' ),
			'created_at' => ! empty( $row['date'] ) ? sanitize_text_field( $row['date'] ) : current_time( 'mysql' ),
		] );
	}

	// ── Remote image sideload (shared) ───────────────────────────────────────

	/**
	 * Download a remote image and register it in the WP Media Library.
	 *
	 * The ONLY remote-fetch → media path in the plugin (S470 survey #4). Other
	 * importers (the Prism demo importer) call THROUGH this method so the
	 * `download_url(` tripwire literal never leaves this file.
	 *
	 * @param  string $url   Absolute http(s) URL of the image.
	 * @param  array  $meta  Optional: title, alt, filename (override basename).
	 * @return int|\WP_Error  New attachment id, or WP_Error on download/sideload failure.
	 */
	public function sideloadRemoteImage( string $url, array $meta = [] ) {
		if ( strpos( $url, 'http://' ) !== 0 && strpos( $url, 'https://' ) !== 0 ) {
			return new \WP_Error( 'tc_sideload_bad_url', 'Image source must be an absolute http(s) URL.' );
		}
		if ( ! function_exists( 'media_handle_sideload' ) ) {
			require_once ABSPATH . 'wp-admin/includes/file.php';
			require_once ABSPATH . 'wp-admin/includes/media.php';
			require_once ABSPATH . 'wp-admin/includes/image.php';
		}

		$tmp_file = download_url( $url, 30 );
		if ( is_wp_error( $tmp_file ) ) {
			return $tmp_file;
		}
		$filename = trim( (string) ( $meta['filename'] ?? '' ) );
		if ( '' === $filename ) {
			$filename = basename( (string) wp_parse_url( $url, PHP_URL_PATH ) ) ?: 'import-image.jpg';
		}

		$post_data = [];
		if ( ! empty( $meta['title'] ) ) {
			$post_data['post_title'] = sanitize_text_field( (string) $meta['title'] );
		}

		$attachment_id = media_handle_sideload(
			[ 'name' => sanitize_file_name( $filename ), 'tmp_name' => $tmp_file ],
			0,
			null,
			$post_data
		);
		if ( is_wp_error( $attachment_id ) ) {
			if ( file_exists( $tmp_file ) ) {
				wp_delete_file( $tmp_file );
			}
			return $attachment_id;
		}
		if ( ! empty( $meta['alt'] ) ) {
			update_post_meta( (int) $attachment_id, '_wp_attachment_image_alt', sanitize_text_field( (string) $meta['alt'] ) );
		}
		return (int) $attachment_id;
	}

	// ── Product image importer ────────────────────────────────────────────────

	/**
	 * Import images from pipe-separated URLs or filenames.
	 *
	 * If a value has no http:// or https://, it's treated as a filename and
	 * resolved from the configured images_directory (relative to ABSPATH).
	 *
	 * Each image is sideloaded into the WP Media Library, then linked via
	 * tc_product_images (product_id, wp_attachment_id, position, is_main).
	 * Remote URLs go through sideloadRemoteImage(); local files are copied to
	 * a tmp file and sideloaded here.
	 */
	private function importProductImages( int $product_id, string $images_raw, array $settings ): void {
		global $wpdb;
		$pi_table = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'product_images';
		// Local-image base directory. Empty setting → WordPress's own uploads
		// root (wp_upload_dir(), never an assumed wp-content/uploads layout);
		// an owner-supplied value is an ABSPATH-relative path. Traversal
		// segments are stripped in both cases.
		$img_dir_setting = trim( (string) ( $settings['images_directory'] ?? '' ) );
		if ( $img_dir_setting === '' ) {
			$uploads = wp_upload_dir();
			$img_dir = rtrim( $uploads['basedir'], '/\\' ) . '/titancart/import-images';
		} else {
			$img_dir = ABSPATH . trim( str_replace( [ '..', "\0" ], '', $img_dir_setting ), '/' );
		}
		$img_dir = rtrim( $img_dir, '/' );

		// Ensure WP media functions are available (they aren't on cron/REST by default).
		if ( ! function_exists( 'media_handle_sideload' ) ) {
			require_once ABSPATH . 'wp-admin/includes/file.php';
			require_once ABSPATH . 'wp-admin/includes/media.php';
			require_once ABSPATH . 'wp-admin/includes/image.php';
		}

		$entries = array_filter( array_map( 'trim', explode( '|', $images_raw ) ) );
		if ( empty( $entries ) ) return;

		// Get current max position for this product
		$max_pos = (int) $wpdb->get_var(
			$wpdb->prepare( "SELECT MAX(position) FROM {$pi_table} WHERE product_id = %d", $product_id )
		);
		$position = $max_pos + 1;
		$is_first = ! (bool) $wpdb->get_var(
			$wpdb->prepare( "SELECT COUNT(*) FROM {$pi_table} WHERE product_id = %d", $product_id )
		);

		foreach ( $entries as $src ) {
			$is_url = ( strpos( $src, 'http://' ) === 0 || strpos( $src, 'https://' ) === 0 );
			$tmp_file = null;

			try {
				if ( $is_url ) {
					// Remote URL — shared sideload path (returns a ready attachment).
					$attachment_id = $this->sideloadRemoteImage( $src );
					if ( is_wp_error( $attachment_id ) ) {
						continue; // download/sideload failed, skip
					}
				} else {
					// Local filename — copy from images directory to tmp. basename()
					// confines the lookup to the configured directory.
					$filename = basename( str_replace( "\0", '', $src ) );
					$local    = $img_dir . '/' . $filename;
					if ( $filename === '' || ! file_exists( $local ) ) {
						continue; // file not found, skip
					}
					// Copy (never move) to a tmp file: the sideload below MOVES its
					// tmp file, which would otherwise delete the owner's source image.
					// copy() is not an AlternativeFunctions target; the WP filesystem
					// abstraction is deliberately absent from this package.
					$tmp_file = wp_tempnam( $filename );
					if ( ! copy( $local, $tmp_file ) ) {
						wp_delete_file( $tmp_file );
						continue;
					}

					// Sideload into WP Media Library
					$file_array = [
						'name'     => sanitize_file_name( $filename ),
						'tmp_name' => $tmp_file,
					];

					$attachment_id = media_handle_sideload( $file_array, 0 );

					if ( is_wp_error( $attachment_id ) ) {
						wp_delete_file( $tmp_file );
						continue;
					}
				}

				// Check this attachment isn't already linked to this product
				$exists = $wpdb->get_var(
					$wpdb->prepare(
						"SELECT id FROM {$pi_table} WHERE product_id = %d AND wp_attachment_id = %d LIMIT 1",
						$product_id, $attachment_id
					)
				);
				if ( $exists ) {
					$position++;
					continue;
				}

				// Insert into tc_product_images
				$wpdb->insert( $pi_table, [
					'product_id'       => $product_id,
					'wp_attachment_id' => $attachment_id,
					'position'         => $position,
					'is_main'          => ( $is_first && $position === ( $max_pos + 1 ) ) ? 1 : 0,
				] );

				$position++;

			} catch ( \Throwable $e ) {
				if ( $tmp_file && file_exists( $tmp_file ) ) {
					wp_delete_file( $tmp_file );
				}
				// Log but don't fail the whole row
				continue;
			}
		}
	}

	// ── Product attribute/feature value writer ────────────────────────────────

	/**
	 * Process any "feature:ID" mapped columns from the import row and
	 * write them to tc_product_feature_values. Handles variant lookups
	 * for Select/MultiCheck/Color types, and direct values for Text/Number/Date.
	 */
	private function writeProductFeatureValues( int $product_id, array $row ): void {
		global $wpdb;
		$values_table   = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'product_feature_values';
		$features_table = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'product_features';
		$variants_table = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'product_feature_variants';
		$var_trans      = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'product_feature_variant_translations';
		$lang           = function_exists( 'titancart_get_current_language' ) ? titancart_get_current_language() : 'en';

		$feature_keys = [];
		foreach ( $row as $key => $value ) {
			if ( strpos( $key, 'feature:' ) === 0 && $value !== '' ) {
				$feature_keys[ $key ] = $value;
			}
		}

		if ( empty( $feature_keys ) ) {
			titancart_log( "[TitanCart Import] writeProductFeatureValues: No feature: keys found in row for product {$product_id}. Keys present: " . implode( ', ', array_keys( $row ) ) );
			return;
		}

		titancart_log( "[TitanCart Import] writeProductFeatureValues: product_id={$product_id}, features=" . wp_json_encode( $feature_keys ) );

		foreach ( $feature_keys as $key => $value ) {
			$feature_id = (int) substr( $key, 8 ); // after "feature:"
			if ( ! $feature_id ) continue;

			// Get feature type and purpose
			$feature = $wpdb->get_row(
				$wpdb->prepare( "SELECT id, type, purpose FROM {$features_table} WHERE id = %d LIMIT 1", $feature_id )
			);
			if ( ! $feature ) {
				titancart_log( "[TitanCart Import] Feature ID {$feature_id} not found in DB." );
				continue;
			}

			titancart_log( "[TitanCart Import] Processing feature:{$feature_id} type={$feature->type} purpose={$feature->purpose} value='{$value}' for product {$product_id}" );

			// Delete existing values for this product + feature before writing new ones
			$wpdb->delete( $values_table, [ 'product_id' => $product_id, 'feature_id' => $feature_id ] );

			// Determine how to store based on feature type.
			// For variation attributes (purpose = variation_split or variation_shared),
			// T = Size Pills and needs variants — treat same as S/E.
			$is_variation = in_array( $feature->purpose, [ 'variation_split', 'variation_shared' ], true );
			$variant_types = [ 'S', 'M', 'E' ]; // Select, MultiCheck, Color swatch
			$use_variants  = in_array( $feature->type, $variant_types, true )
			              || ( $feature->type === 'T' && $is_variation ); // T + variation = Size Pills
			$number_types  = [ 'N', 'O' ];       // NumSelect, NumField

			if ( $feature->type === 'C' ) {
				// Yes/No type: store as value_int
				$bool_val = in_array( strtolower( trim( $value ) ), [ '1', 'yes', 'y', 'true' ], true ) ? 1 : 0;
				$ok = $wpdb->insert( $values_table, [
					'product_id' => $product_id,
					'feature_id' => $feature_id,
					'value_int'  => $bool_val,
				] );
				if ( $ok === false ) {
					titancart_log( "[TitanCart Import] FAILED insert C-type value for product {$product_id}, feature {$feature_id}: {$wpdb->last_error}" );
				} else {
					titancart_log( "[TitanCart Import] OK C-type value={$bool_val} for product {$product_id}, feature {$feature_id}" );
				}

			} elseif ( $use_variants ) {
				// Variant-based types: split by pipe but use only the first value
				// (a single product has one color/size; use separate rows for variants)
				$vals = array_filter( array_map( 'trim', explode( '|', $value ) ) );
				if ( $feature->type !== 'M' ) {
					$vals = array_slice( $vals, 0, 1 ); // Single-value: take first only
				}

				foreach ( $vals as $val ) {
					// Look up existing variant by name
					$variant_id = (int) $wpdb->get_var(
						$wpdb->prepare(
							"SELECT v.id FROM {$variants_table} v
							 JOIN {$var_trans} vt ON vt.variant_id = v.id AND vt.language_code = %s
							 WHERE v.feature_id = %d AND vt.name = %s LIMIT 1",
							$lang, $feature_id, $val
						)
					);

					if ( ! $variant_id ) {
						// Auto-create variant
						$max_pos = (int) $wpdb->get_var(
							$wpdb->prepare( "SELECT COALESCE(MAX(position),0) FROM {$variants_table} WHERE feature_id = %d", $feature_id )
						);
						$wpdb->insert( $variants_table, [
							'feature_id' => $feature_id,
							'position'   => $max_pos + 1,
							'status'     => 'active',
						] );
						$variant_id = (int) $wpdb->insert_id;
						if ( $variant_id ) {
							$wpdb->insert( $var_trans, [
								'variant_id'    => $variant_id,
								'language_code' => $lang,
								'name'          => sanitize_text_field( $val ),
							] );
							titancart_log( "[TitanCart Import] Auto-created variant '{$val}' (ID {$variant_id}) for feature {$feature_id}." );
						} else {
							titancart_log( "[TitanCart Import] FAILED to create variant '{$val}' for feature {$feature_id}: {$wpdb->last_error}" );
						}
					}

					if ( $variant_id ) {
						$ok = $wpdb->insert( $values_table, [
							'product_id' => $product_id,
							'feature_id' => $feature_id,
							'variant_id' => $variant_id,
							'value_str'  => sanitize_text_field( $val ),
						] );
						if ( $ok === false ) {
							titancart_log( "[TitanCart Import] FAILED insert feature value: product={$product_id}, feature={$feature_id}, variant={$variant_id}, val='{$val}': {$wpdb->last_error}" );
						} else {
							titancart_log( "[TitanCart Import] OK inserted feature value: product={$product_id}, feature={$feature_id}, variant={$variant_id}, val='{$val}'" );
						}
					}
				}

			} elseif ( in_array( $feature->type, $number_types, true ) ) {
				$ok = $wpdb->insert( $values_table, [
					'product_id'  => $product_id,
					'feature_id'  => $feature_id,
					'value_float' => (float) $value,
					'value_str'   => sanitize_text_field( $value ),
				] );
				if ( $ok === false ) {
					titancart_log( "[TitanCart Import] FAILED insert N-type value for product {$product_id}, feature {$feature_id}: {$wpdb->last_error}" );
				} else {
					titancart_log( "[TitanCart Import] OK N-type value='{$value}' for product {$product_id}, feature {$feature_id}" );
				}

			} elseif ( $feature->type === 'D' ) {
				$ok = $wpdb->insert( $values_table, [
					'product_id' => $product_id,
					'feature_id' => $feature_id,
					'value_date' => sanitize_text_field( $value ),
					'value_str'  => sanitize_text_field( $value ),
				] );
				if ( $ok === false ) {
					titancart_log( "[TitanCart Import] FAILED insert D-type value for product {$product_id}, feature {$feature_id}: {$wpdb->last_error}" );
				} else {
					titancart_log( "[TitanCart Import] OK D-type value='{$value}' for product {$product_id}, feature {$feature_id}" );
				}

			} else {
				// Text (T) or any other type — store as string
				$ok = $wpdb->insert( $values_table, [
					'product_id' => $product_id,
					'feature_id' => $feature_id,
					'value_str'  => sanitize_text_field( $value ),
					'value_text' => wp_kses_post( $value ),
				] );
				if ( $ok === false ) {
					titancart_log( "[TitanCart Import] FAILED insert T-type value for product {$product_id}, feature {$feature_id}: {$wpdb->last_error}" );
				} else {
					titancart_log( "[TitanCart Import] OK T-type value='{$value}' for product {$product_id}, feature {$feature_id}" );
				}
			}
		}
	}

	// ── Product Group / Variation linking ─────────────────────────────────────

	/**
	 * Link a product to a Product Group (variation group) by group_code.
	 *
	 * 1. Find or create the tc_product_groups row by group_code.
	 * 2. Auto-detect variation features (purpose = variation_split or variation_shared)
	 *    from the product's saved feature values and merge into the group's feature_ids.
	 * 3. Build the combination JSON from the product's feature values for those features.
	 * 4. Insert/update tc_product_group_products.
	 *
	 * @param int    $product_id  The product ID (already inserted/updated).
	 * @param string $group_code  The variation group code from CSV.
	 * @param bool   $is_default  If true, set position = 0 (default variation).
	 */
	private function linkProductGroup( int $product_id, string $group_code, bool $is_default = false ): void {
		global $wpdb;
		$t_groups   = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'product_groups';
		$t_gp       = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'product_group_products';
		$t_features = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'product_features';
		$t_fv       = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'product_feature_values';

		$group_code = sanitize_text_field( $group_code );
		if ( ! $group_code ) return;

		// ── 1. Find or create the group ──
		$group = $wpdb->get_row(
			$wpdb->prepare( "SELECT id, feature_ids FROM {$t_groups} WHERE group_code = %s LIMIT 1", $group_code )
		);

		if ( $group ) {
			$group_id      = (int) $group->id;
			$raw_fids      = json_decode( $group->feature_ids ?: '[]', true ) ?: [];
			// Purge stale feature IDs that no longer exist (deleted between imports).
			$existing_fids = [];
			if ( ! empty( $raw_fids ) ) {
				// WP.org R2 §8: generated %d placeholders, ids bound via prepare().
				$raw_fids     = array_map( 'intval', $raw_fids );
				$placeholders = implode( ',', array_fill( 0, count( $raw_fids ), '%d' ) );
				$valid = $wpdb->get_col( $wpdb->prepare( "SELECT id FROM {$t_features} WHERE id IN ({$placeholders}) AND status = 'active'", $raw_fids ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- $t_features = prefix . literal; generated %d list.
				$existing_fids = array_map( 'intval', $valid );
			}
		} else {
			$wpdb->insert( $t_groups, [
				'group_code'  => $group_code,
				'feature_ids' => '[]',
				'created_at'  => current_time( 'mysql' ),
			] );
			$group_id      = (int) $wpdb->insert_id;
			$existing_fids = [];

			if ( ! $group_id ) {
				titancart_log( "[TitanCart Import] linkProductGroup: Failed to create group '{$group_code}': {$wpdb->last_error}" );
				return;
			}
		}

		// ── 2. Get this product's variation-purpose feature values ──
		// Find feature values for this product where the feature has a variation purpose.
		$variation_values = $wpdb->get_results( // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $t_fv/$t_features are $wpdb->prefix . TITANCART_TABLE_PREFIX constants (never user input); values bound via placeholders.
			$wpdb->prepare(
				"SELECT fv.feature_id, fv.variant_id
				   FROM {$t_fv} fv
				   JOIN {$t_features} f ON f.id = fv.feature_id
				  WHERE fv.product_id = %d
				    AND f.purpose IN ('variation_split', 'variation_shared')
				    AND fv.variant_id IS NOT NULL
				    AND fv.variant_id > 0
				  ORDER BY f.id",
				$product_id
			)
		);

		// Build combination JSON: { "feature_id": variant_id, ... }
		$combination = [];
		$found_fids  = [];
		foreach ( $variation_values as $vv ) {
			$fid = (int) $vv->feature_id;
			$vid = (int) $vv->variant_id;
			$combination[ (string) $fid ] = $vid;
			$found_fids[] = $fid;
		}

		// ── 3. Merge feature_ids into the group if needed ──
		$merged_fids = $existing_fids;
		$changed     = false;
		foreach ( $found_fids as $fid ) {
			if ( ! in_array( $fid, $merged_fids, true ) ) {
				$merged_fids[] = $fid;
				$changed       = true;
			}
		}
		if ( $changed ) {
			$wpdb->update( $t_groups, [ 'feature_ids' => wp_json_encode( $merged_fids ) ], [ 'id' => $group_id ] );
		}

		// ── 4. Insert or update product_group_products ──
		$existing_member = $wpdb->get_row(
			$wpdb->prepare( "SELECT id, combination FROM {$t_gp} WHERE group_id = %d AND product_id = %d LIMIT 1", $group_id, $product_id )
		);

		if ( $existing_member ) {
			// Update combination + position if re-importing.
			$update_data = [ 'combination' => wp_json_encode( $combination ) ];
			if ( $is_default ) {
				$update_data['position'] = 0;
			}
			$wpdb->update( $t_gp, $update_data, [ 'id' => (int) $existing_member->id ] );
		} else {
			// Determine position: 0 if default, else next available.
			if ( $is_default ) {
				// Shift existing members' positions up by 1 to make room at 0.
				$wpdb->query(
					$wpdb->prepare(
						"UPDATE {$t_gp} SET position = position + 1 WHERE group_id = %d",
						$group_id
					)
				);
				$position = 0;
			} else {
				$position = (int) $wpdb->get_var(
					$wpdb->prepare( "SELECT COALESCE(MAX(position), -1) + 1 FROM {$t_gp} WHERE group_id = %d", $group_id )
				);
			}

			$wpdb->insert( $t_gp, [
				'group_id'    => $group_id,
				'product_id'  => $product_id,
				'combination' => wp_json_encode( $combination ),
				'position'    => $position,
			] );
		}

		titancart_log( "[TitanCart Import] linkProductGroup: product={$product_id} → group '{$group_code}' (id={$group_id}), combo=" . wp_json_encode( $combination ) . ", default=" . ( $is_default ? 'Y' : 'N' ) );
	}

	// ── Category linking ──────────────────────────────────────────────────────

	private function linkProductCategories( int $product_id, string $categories_raw, string $lang, bool $is_secondary = false ): void {
		global $wpdb;
		$cats_table  = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'categories';
		$trans_table = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'category_translations';
		$pivot_table = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'product_categories';

		// The categories field may contain pipe-separated entries.
		// Each entry may be a simple name ("Electronics") or a hierarchical
		// path using /// as the depth separator ("Electronics///Audio///Headphones").
		$entries = array_filter( array_map( 'trim', explode( '|', $categories_raw ) ) );
		$is_first = ! $is_secondary; // Only the first primary category gets is_main=1

		foreach ( $entries as $entry ) {
			// Resolve (or create) the full category path, return the leaf category ID.
			$cat_id = $this->resolveOrCreateCategoryPath( $entry, $lang );

			if ( ! $cat_id ) {
				continue;
			}

			// Upsert pivot — composite PK is (product_id, category_id).
			$exists = $wpdb->get_var(
				$wpdb->prepare(
					"SELECT product_id FROM {$pivot_table} WHERE product_id = %d AND category_id = %d LIMIT 1",
					$product_id, $cat_id
				)
			);
			if ( ! $exists ) {
				$wpdb->insert( $pivot_table, [
					'product_id'  => $product_id,
					'category_id' => $cat_id,
					'is_main'     => $is_first ? 1 : 0,
				] );
			}
			$is_first = false;
		}
	}

	/**
	 * Resolve a category path like "Electronics///Audio///Headphones" to the
	 * leaf category ID, creating any missing segments along the way.
	 */
	private function resolveOrCreateCategoryPath( string $path, string $lang ): int {
		global $wpdb;
		$cats_table  = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'categories';
		$trans_table = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'category_translations';

		$segments  = array_filter( array_map( 'trim', explode( '///', $path ) ) );
		$parent_id = 0;

		foreach ( $segments as $name ) {
			if ( $name === '' ) {
				continue;
			}

			// Look for existing category with this name under the current parent.
			$cat_id = (int) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT c.id FROM {$cats_table} c
					 JOIN {$trans_table} t ON t.category_id = c.id AND t.language_code = %s
					 WHERE t.name = %s AND c.parent_id = %d AND c.deleted_at IS NULL
					 LIMIT 1",
					$lang, sanitize_text_field( $name ), $parent_id
				)
			);

			if ( ! $cat_id ) {
				// Also try a broader match (any parent) in case the tree isn't perfect.
				$cat_id = (int) $wpdb->get_var(
					$wpdb->prepare(
						"SELECT c.id FROM {$cats_table} c
						 JOIN {$trans_table} t ON t.category_id = c.id AND t.language_code = %s
						 WHERE t.name = %s AND c.deleted_at IS NULL
						 LIMIT 1",
						$lang, sanitize_text_field( $name )
					)
				);
			}

			if ( ! $cat_id ) {
				// Auto-create the category.
				$slug = sanitize_title( $name );
				$wpdb->insert( $cats_table, [
					'parent_id'  => $parent_id,
					'slug'       => $slug,
					'status'     => 'active',
					'created_at' => current_time( 'mysql' ),
					'updated_at' => current_time( 'mysql' ),
				] );
				$cat_id = (int) $wpdb->insert_id;

				if ( $cat_id ) {
					$wpdb->insert( $trans_table, [
						'category_id'   => $cat_id,
						'language_code' => $lang,
						'name'          => sanitize_text_field( $name ),
					] );
				}
			}

			$parent_id = $cat_id;
		}

		return $parent_id;
	}

	// ── Slug generation ───────────────────────────────────────────────────────

	/**
	 * Generate a unique slug for a name within a table/column.
	 * If the base slug conflicts with a different record's slug, append -2, -3, etc.
	 */
	private function generateSlug( string $name, string $table, string $col, int $own_id ): string {
		global $wpdb;
		$base = sanitize_title( $name );
		$slug = $base;
		$i    = 2;

		while ( true ) {
			$conflict_id = (int) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT id FROM {$table} WHERE {$col} = %s LIMIT 1",
					$slug
				)
			);
			if ( ! $conflict_id || $conflict_id === $own_id ) {
				break;
			}
			$slug = $base . '-' . $i++;
		}

		return $slug;
	}

	// ── Filesystem ────────────────────────────────────────────────────────────

	private function importsDir(): string {
		$upload = wp_upload_dir();
		$dir    = $upload['basedir'] . '/titancart/imports';
		if ( ! is_dir( $dir ) ) {
			wp_mkdir_p( $dir );
			file_put_contents( $dir . '/.htaccess', "Order deny,allow\nDeny from all\n" ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_file_put_contents -- tiny self-owned .htaccess deny-all in the import dir; WP_Filesystem init is disproportionate and can demand FTP creds in REST context.
		}
		return $dir;
	}

	private function safeFilename( string $original ): string {
		$ext  = strtolower( pathinfo( $original, PATHINFO_EXTENSION ) );
		$name = sanitize_file_name( pathinfo( $original, PATHINFO_FILENAME ) );
		return $name . '_' . time() . '.' . ( $ext ?: 'csv' );
	}

	// ── DB helpers ────────────────────────────────────────────────────────────

	private function getJob( int $job_id ): ?object {
		global $wpdb;
		$table = $wpdb->prefix . TITANCART_TABLE_PREFIX . 'import_jobs';
		return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d LIMIT 1", $job_id ) );
	}

	private function assertValidType( string $type ): void {
		if ( ! in_array( $type, [ 'products', 'categories', 'customers', 'orders', 'order_items', 'reviews', 'waitlist' ], true ) ) {
			throw new \InvalidArgumentException( esc_html( "Invalid import type: {$type}" ) );
		}
	}

	/**
	 * Normalize status values — accepts both shorthand (A/D/H) and full names (active/disabled/hidden).
	 * Returns the full DB enum value, or $default if not recognized.
	 */
	private function normalizeStatus( string $value, ?string $default ): ?string {
		$v = strtolower( trim( $value ) );
		$map = [
			'a' => 'active', 'd' => 'disabled', 'h' => 'hidden',
			'active' => 'active', 'disabled' => 'disabled', 'hidden' => 'hidden',
			'enabled' => 'active', 'published' => 'active',
			'1' => 'active', '0' => 'disabled',
			'yes' => 'active', 'no' => 'disabled',
			'true' => 'active', 'false' => 'disabled',
			'on' => 'active', 'off' => 'disabled',
			'inactive' => 'disabled', 'draft' => 'hidden',
		];
		return $map[ $v ] ?? $default;
	}
}
