<?php
/**
 * DemoExportService — builds a portable "demo package" for one Prism preset.
 *
 * Part of the Prism demo importer (TitanCart/DEMO_IMPORT_PLAN.md). A package is
 * the CATALOG/DESIGN layer only — per the S471 decision the package carries
 * exactly two things taken from the source store:
 *
 *   1. PRODUCTS — plus everything needed to make them whole: categories (the
 *      preset's scope subtree + ancestors), brands, attributes (features +
 *      variants), options, variation groups, tags, prices, images.
 *   2. BANNER / THEME IMAGES — every image-valued `<preset>_*` theme option.
 *
 * Nothing transactional is exported. Customers, orders, reviews and all other
 * dashboard content are GENERATED by DemoImportService at install time.
 *
 * Scope = the same category union the storefront demo gate uses
 * (`<preset>_header_cats` ∪ `<preset>_home_cats`, subtree-expanded). The
 * subtree expansion is a plugin-side nested-set helper (standard SQL, #676) —
 * the theme's tct_prism_expand_subtree() is off-limits to the engine (Rule 4).
 * Because theme options are preset-prefixed, ANY preset can be exported
 * regardless of which one is active — all seven packages ship from one run.
 *
 * Output (under uploads/titancart/exports/demos/):
 *   <preset>/package.json   the manifest (format "titancart-demo/1")
 *   <preset>/media/*        copies of every referenced attachment file
 *   <preset>.zip            the folder above, ready to unzip at
 *                           market.titancart.io/demos/<preset>/
 *
 * Identity keys — every entity carries a STABLE, human-readable key that the
 * importer uses as the `tc_demo_import_log.source_key`, so re-exporting the
 * same store yields the same keys and re-installing is an idempotent repair:
 *   category  → slug            brand   → slug
 *   feature   → feature_code, else slug(name)
 *   variant   → "<feature key>:<slug(name)>"
 *   product   → sku (Product Code), else slug
 *   group     → group_code
 *   media     → "att:<source attachment id>" (package-internal; the importer
 *               logs the media under the FILE name, which is stable)
 *
 * @package TitanCart\Modules\ImportExport
 */

namespace TitanCart\Modules\ImportExport;

use TitanCart\DB\Database;
use TitanCart\Services\SettingsService;

defined( 'ABSPATH' ) || exit;

class DemoExportService {

	/** Manifest format identifier; bump on breaking shape changes. */
	const FORMAT = 'titancart-demo/1';

	/** Theme-option keys that are preset-prefixed but NOT part of a package. */
	const SKIP_OPTION_SUFFIXES = [ 'cat_scope_enabled' ];

	/** Theme-option keys whose CSV category ids are remapped to slugs. */
	const CATEGORY_LIST_SUFFIXES = [ 'header_cats', 'home_cats' ];

	private Database $db;

	/** @var array<string,array> media key → { file, url, alt, title } */
	private array $media = [];

	/** @var array<int,string> attachment id → media key (dedupe) */
	private array $media_by_att = [];

	/** @var array<int,string> category id → slug */
	private array $cat_slug = [];

	/** @var array<int,string> feature id → key */
	private array $feature_key = [];

	/** @var array<int,string> variant id → key */
	private array $variant_key = [];

	/** @var array<int,string> product id → key */
	private array $product_key = [];

	/** @var array<int,string> brand id → slug */
	private array $brand_slug = [];

	private string $pkg_dir = '';

	public function __construct() {
		$this->db = titancart_db();
	}

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

	/**
	 * Export one preset.
	 *
	 * @param  string $preset  Prism preset id (e.g. 'apparel').
	 * @return array{ preset:string, dir:string, zip:string, zip_url:string, counts:array<string,int> }
	 * @throws \RuntimeException
	 */
	public function exportPreset( string $preset ): array {
		$preset = preg_replace( '/[^a-z0-9_]/', '', strtolower( $preset ) );
		if ( '' === $preset ) {
			throw new \InvalidArgumentException( 'Invalid preset id.' );
		}

		$this->reset();

		$options = $this->presetOptions( $preset );
		if ( empty( $options ) ) {
			throw new \RuntimeException( esc_html( "No theme options found for preset '{$preset}' — is it configured on this store?" ) );
		}

		$header = $this->csvIds( $options['header_cats'] ?? '' );
		$home   = $this->csvIds( $options['home_cats'] ?? '' );
		$scope  = $this->expandSubtree( array_values( array_unique( array_merge( $header, $home ) ) ) );
		if ( empty( $scope ) ) {
			throw new \RuntimeException( esc_html( "Preset '{$preset}' has no header/home categories selected — nothing to export." ) );
		}

		$product_ids = $this->scopedProductIds( $scope );
		if ( empty( $product_ids ) ) {
			throw new \RuntimeException( esc_html( "Preset '{$preset}' scope contains no products." ) );
		}

		$this->pkg_dir = $this->packageDir( $preset );

		// Order matters: later sections reference keys assigned by earlier ones.
		$brands     = $this->exportBrands( $product_ids );
		$categories = $this->exportCategories( $scope, $product_ids );
		$features   = $this->exportFeatures( $product_ids, array_keys( $this->cat_slug ) );
		$products   = $this->exportProducts( $product_ids );
		$groups     = $this->exportGroups( $product_ids );
		$theme      = $this->exportThemeOptions( $options );

		$package = [
			'format'       => self::FORMAT,
			'preset'       => $preset,
			'generated_at' => gmdate( 'c' ),
			'source'       => home_url( '/' ),
			'scope'        => [
				'header_cats' => $this->slugsFor( $header ),
				'home_cats'   => $this->slugsFor( $home ),
			],
			'counts'       => [
				'brands'     => count( $brands ),
				'categories' => count( $categories ),
				'features'   => count( $features ),
				'products'   => count( $products ),
				'groups'     => count( $groups ),
				'media'      => count( $this->media ),
			],
			'brands'        => $brands,
			'categories'    => $categories,
			'features'      => $features,
			'products'      => $products,
			'groups'        => $groups,
			'theme_options' => $theme,
			'media'         => $this->media,
		];

		$json = wp_json_encode( $package, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT );
		if ( false === $json ) {
			throw new \RuntimeException( 'Could not encode the demo package as JSON.' );
		}
		file_put_contents( $this->pkg_dir . '/package.json', $json ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_file_put_contents -- self-owned export file under uploads/titancart/exports; WP_Filesystem init is disproportionate in REST context.

		$zip = $this->zipPackage( $preset );

		$upload = wp_upload_dir();
		return [
			'preset'  => $preset,
			'dir'     => $this->pkg_dir,
			'zip'     => $zip,
			'zip_url' => $zip ? str_replace( $upload['basedir'], $upload['baseurl'], $zip ) : '',
			'counts'  => $package['counts'],
		];
	}

	/**
	 * Export several presets in one call (the "all 7 in one run" path).
	 *
	 * @param  string[] $presets
	 * @return array<string,array>  preset → exportPreset() result, or { error }
	 */
	public function exportPresets( array $presets ): array {
		$out = [];
		foreach ( $presets as $preset ) {
			try {
				$out[ $preset ] = $this->exportPreset( (string) $preset );
			} catch ( \Throwable $e ) {
				$out[ $preset ] = [ 'error' => $e->getMessage() ];
			}
		}
		return $out;
	}

	/**
	 * Preset ids present in theme_options (every distinct `<id>_header_cats`
	 * key), so callers can discover what is exportable without the theme.
	 *
	 * @return string[]
	 */
	public function discoverPresets(): array {
		$ids = [];
		foreach ( array_keys( SettingsService::section( 'theme_options' ) ) as $key ) {
			if ( preg_match( '/^([a-z0-9_]+)_header_cats$/', (string) $key, $m ) ) {
				$ids[] = $m[1];
			}
		}
		sort( $ids );
		return $ids;
	}

	// ──────────────────────────────────────────────────────────────────────────
	// Scope
	// ──────────────────────────────────────────────────────────────────────────

	/**
	 * Expand category ids to every descendant via the nested set (lft/rgt).
	 * Plugin-side twin of the theme's helper — standard SQL only (#676).
	 *
	 * @param  int[] $ids
	 * @return int[]  chosen ids + all descendants, deduped
	 */
	public function expandSubtree( array $ids ): array {
		$ids = array_values( array_unique( array_filter( array_map( 'intval', $ids ) ) ) );
		if ( empty( $ids ) ) {
			return [];
		}
		$cats   = $this->db->table( 'categories' );
		$bounds = $this->db->getArray(
			"SELECT lft, rgt FROM {$cats} WHERE id IN (?a) AND deleted_at IS NULL",
			$ids
		);

		$out = $ids;
		foreach ( $bounds as $b ) {
			$l = (int) $b['lft'];
			$r = (int) $b['rgt'];
			if ( $l > 0 && $r > $l ) {
				$rows = $this->db->getColumn(
					"SELECT id FROM {$cats} WHERE lft >= ?i AND rgt <= ?i AND deleted_at IS NULL",
					$l,
					$r
				);
				foreach ( $rows as $id ) {
					$out[] = (int) $id;
				}
			}
		}
		return array_values( array_unique( array_filter( $out ) ) );
	}

	/**
	 * Products living in any scoped category, closed over variation groups so a
	 * group is never exported half-empty (members in other categories come too).
	 *
	 * @param  int[] $category_ids
	 * @return int[]
	 */
	private function scopedProductIds( array $category_ids ): array {
		$p   = $this->db->table( 'products' );
		$pc  = $this->db->table( 'product_categories' );
		$ids = $this->db->getColumn(
			"SELECT DISTINCT p.id FROM {$p} p
			   JOIN {$pc} pc ON pc.product_id = p.id
			  WHERE pc.category_id IN (?a) AND p.deleted_at IS NULL",
			$category_ids
		);
		$ids = array_map( 'intval', $ids );
		if ( empty( $ids ) ) {
			return [];
		}

		$gp        = $this->db->table( 'product_group_products' );
		$group_ids = $this->db->getColumn( "SELECT DISTINCT group_id FROM {$gp} WHERE product_id IN (?a)", $ids );
		if ( ! empty( $group_ids ) ) {
			$members = $this->db->getColumn(
				"SELECT DISTINCT gp.product_id FROM {$gp} gp JOIN {$p} p ON p.id = gp.product_id
				  WHERE gp.group_id IN (?a) AND p.deleted_at IS NULL",
				array_map( 'intval', $group_ids )
			);
			$ids = array_merge( $ids, array_map( 'intval', $members ) );
		}

		$ids = array_values( array_unique( $ids ) );
		sort( $ids );
		return $ids;
	}

	// ──────────────────────────────────────────────────────────────────────────
	// Sections
	// ──────────────────────────────────────────────────────────────────────────

	private function exportBrands( array $product_ids ): array {
		$p   = $this->db->table( 'products' );
		$ids = $this->db->getColumn(
			"SELECT DISTINCT brand_id FROM {$p} WHERE id IN (?a) AND brand_id IS NOT NULL AND brand_id > 0",
			$product_ids
		);
		if ( empty( $ids ) ) {
			return [];
		}
		$rows = $this->db->getArray(
			"SELECT * FROM {$this->db->table('brands')} WHERE id IN (?a) ORDER BY position ASC, id ASC",
			array_map( 'intval', $ids )
		);
		$out = [];
		foreach ( $rows as $r ) {
			$tr   = $this->translations( 'brand_translations', 'brand_id', (int) $r['id'], [ 'name', 'description' ] );
			$slug = $this->stableSlug( (string) $r['slug'], $tr, 'brand-' . $r['id'] );
			$this->brand_slug[ (int) $r['id'] ] = $slug;
			$out[] = [
				'key'          => $slug,
				'slug'         => $slug,
				'image'        => $this->mediaKey( $r['image_id'] ),
				'position'     => (int) $r['position'],
				'status'       => (string) $r['status'],
				'translations' => $tr,
			];
		}
		return $out;
	}

	/**
	 * Scope categories + their ancestors (so the importer can rebuild the tree
	 * with correct parents), in nested-set order so parents precede children.
	 */
	private function exportCategories( array $scope_ids, array $product_ids ): array {
		$cats = $this->db->table( 'categories' );
		$pc   = $this->db->table( 'product_categories' );

		// Any category a scoped product is linked to also travels (a product
		// may live in a second, out-of-scope category — keep its links whole).
		$linked = $this->db->getColumn( "SELECT DISTINCT category_id FROM {$pc} WHERE product_id IN (?a)", $product_ids );
		$want   = array_values( array_unique( array_merge( $scope_ids, array_map( 'intval', $linked ) ) ) );

		// Ancestors via nested set.
		$bounds    = $this->db->getArray( "SELECT id, lft, rgt FROM {$cats} WHERE id IN (?a) AND deleted_at IS NULL", $want );
		$ancestors = [];
		foreach ( $bounds as $b ) {
			$l = (int) $b['lft'];
			$r = (int) $b['rgt'];
			if ( $l > 0 && $r > $l ) {
				$rows = $this->db->getColumn(
					"SELECT id FROM {$cats} WHERE lft < ?i AND rgt > ?i AND deleted_at IS NULL",
					$l,
					$r
				);
				foreach ( $rows as $id ) {
					$ancestors[] = (int) $id;
				}
			}
		}
		$all = array_values( array_unique( array_merge( $want, $ancestors ) ) );

		$rows = $this->db->getArray(
			"SELECT * FROM {$cats} WHERE id IN (?a) AND deleted_at IS NULL ORDER BY lft ASC, id ASC",
			$all
		);

		// First pass: slugs (parents need to be resolvable).
		foreach ( $rows as $r ) {
			$tr = $this->translations( 'category_translations', 'category_id', (int) $r['id'], [ 'name' ] );
			$this->cat_slug[ (int) $r['id'] ] = $this->stableSlug( (string) $r['slug'], $tr, 'category-' . $r['id'] );
		}

		$out = [];
		foreach ( $rows as $r ) {
			$id  = (int) $r['id'];
			$pid = (int) $r['parent_id'];
			$tr  = $this->translations( 'category_translations', 'category_id', $id, [ 'name', 'description', 'meta_title', 'meta_desc' ] );
			$out[] = [
				'key'            => $this->cat_slug[ $id ],
				'slug'           => $this->cat_slug[ $id ],
				'parent'         => ( $pid > 0 && isset( $this->cat_slug[ $pid ] ) ) ? $this->cat_slug[ $pid ] : null,
				'image'          => $this->mediaKey( $r['image_id'] ),
				'position'       => (int) $r['position'],
				'status'         => (string) $r['status'],
				'age_restricted' => (int) $r['age_restricted'],
				'age_limit'      => (int) $r['age_limit'],
				'in_scope'       => in_array( $id, $scope_ids, true ),
				'translations'   => $tr,
			];
		}
		return $out;
	}

	/**
	 * Attributes referenced by scoped products (+ their parent groups), each
	 * with ALL of its variants (choice lists stay complete for filters/swatches)
	 * and its category assignments limited to exported categories.
	 */
	private function exportFeatures( array $product_ids, array $category_ids ): array {
		$f   = $this->db->table( 'product_features' );
		$fv  = $this->db->table( 'product_feature_values' );
		$fvr = $this->db->table( 'product_feature_variants' );
		$fc  = $this->db->table( 'product_feature_categories' );

		$ids = array_map( 'intval', $this->db->getColumn( "SELECT DISTINCT feature_id FROM {$fv} WHERE product_id IN (?a)", $product_ids ) );
		if ( empty( $ids ) ) {
			return [];
		}
		// Parent groups (type G) of referenced features.
		$parents = array_map( 'intval', $this->db->getColumn( "SELECT DISTINCT parent_id FROM {$f} WHERE id IN (?a) AND parent_id > 0", $ids ) );
		$ids     = array_values( array_unique( array_merge( $ids, $parents ) ) );

		$rows = $this->db->getArray( "SELECT * FROM {$f} WHERE id IN (?a) ORDER BY parent_id ASC, position ASC, id ASC", $ids );

		// Keys first (parents referenced by children).
		foreach ( $rows as $r ) {
			$tr  = $this->translations( 'product_feature_translations', 'feature_id', (int) $r['id'], [ 'name' ] );
			$key = trim( (string) $r['feature_code'] );
			if ( '' === $key ) {
				$key = $this->stableSlug( '', $tr, 'feature-' . $r['id'] );
			}
			$this->feature_key[ (int) $r['id'] ] = $key;
		}

		$out = [];
		foreach ( $rows as $r ) {
			$id   = (int) $r['id'];
			$tr   = $this->translations( 'product_feature_translations', 'feature_id', $id, [ 'name', 'description' ] );
			$vars = [];
			foreach ( $this->db->getArray( "SELECT * FROM {$fvr} WHERE feature_id = ?i ORDER BY position ASC, id ASC", $id ) as $v ) {
				$vtr  = $this->translations( 'product_feature_variant_translations', 'variant_id', (int) $v['id'], [ 'name' ] );
				$vkey = $this->feature_key[ $id ] . ':' . $this->stableSlug( '', $vtr, 'variant-' . $v['id'] );
				$this->variant_key[ (int) $v['id'] ] = $vkey;
				$vars[] = [
					'key'          => $vkey,
					'color_hex'    => $v['color_hex'] ?: null,
					'position'     => (int) $v['position'],
					'status'       => (string) $v['status'],
					'translations' => $vtr,
				];
			}
			$cat_ids = empty( $category_ids ) ? [] : $this->db->getColumn(
				"SELECT category_id FROM {$fc} WHERE feature_id = ?i AND category_id IN (?a)",
				$id,
				$category_ids
			);
			$pid = (int) $r['parent_id'];
			$out[] = [
				'key'          => $this->feature_key[ $id ],
				'parent'       => ( $pid > 0 && isset( $this->feature_key[ $pid ] ) ) ? $this->feature_key[ $pid ] : null,
				'type'         => (string) $r['type'],
				'filter_type'  => (string) $r['filter_type'],
				'purpose'      => (string) $r['purpose'],
				'feature_code' => (string) $r['feature_code'],
				'position'     => (int) $r['position'],
				'filterable'   => (int) $r['filterable'],
				'compareable'  => (int) $r['compareable'],
				'show_on_pdp'  => (int) $r['show_on_pdp'],
				'sort_mode'    => (string) ( $r['sort_mode'] ?? 'manual' ),
				'status'       => (string) $r['status'],
				'translations' => $tr,
				'variants'     => $vars,
				'categories'   => $this->slugsFor( array_map( 'intval', $cat_ids ) ),
			];
		}
		return $out;
	}

	private function exportProducts( array $product_ids ): array {
		$p   = $this->db->table( 'products' );
		$pc  = $this->db->table( 'product_categories' );
		$pi  = $this->db->table( 'product_images' );
		$pt  = $this->db->table( 'product_tags' );
		$t   = $this->db->table( 'tags' );
		$pp  = $this->db->table( 'product_prices' );
		$fv  = $this->db->table( 'product_feature_values' );

		$rows = $this->db->getArray( "SELECT * FROM {$p} WHERE id IN (?a) AND deleted_at IS NULL ORDER BY position ASC, id ASC", $product_ids );

		// Keys first (groups + variation links reference products).
		foreach ( $rows as $r ) {
			$tr  = $this->translations( 'product_translations', 'product_id', (int) $r['id'], [ 'name' ] );
			$key = trim( (string) $r['sku'] );
			if ( '' === $key ) {
				$key = $this->stableSlug( (string) $r['slug'], $tr, 'product-' . $r['id'] );
			}
			$this->product_key[ (int) $r['id'] ] = $key;
		}

		// Columns copied verbatim from tc_products (everything except identity,
		// FK ids remapped below, derived stats, and timestamps).
		$copy = [
			'sku', 'slug', 'price', 'zero_price_action', 'list_price', 'cost_price', 'weight', 'weight_unit',
			'unit_name', 'units_in_product', 'show_price_per', 'length', 'width', 'height', 'dimension_unit',
			'stock_quantity', 'stock_status', 'out_of_stock_actions', 'track_inventory', 'allow_backorders',
			'min_quantity', 'max_quantity', 'quantity_step', 'shipping_freight', 'free_shipping',
			'age_restricted', 'age_limit', 'is_downloadable', 'download_ttl_hours', 'download_max_count',
			'subscriptions_only', 'rp_allow_payment', 'rp_override_per', 'rp_price', 'rp_override_earn',
			'rp_earn_all', 'rp_earn_all_type', 'rp_earn_guest', 'rp_earn_guest_type', 'rp_earn_reg', 'rp_earn_reg_type',
			'requires_shipping', 'type', 'options_type', 'exceptions_type', 'status', 'visibility', 'featured',
			'position', 'details_view', 'search_words', 'promo_text', 'videos',
		];

		$out = [];
		foreach ( $rows as $r ) {
			$id   = (int) $r['id'];
			$item = [ 'key' => $this->product_key[ $id ] ];
			foreach ( $copy as $col ) {
				if ( array_key_exists( $col, $r ) ) {
					$item[ $col ] = $r[ $col ];
				}
			}
			$item['brand']        = ( $r['brand_id'] && isset( $this->brand_slug[ (int) $r['brand_id'] ] ) ) ? $this->brand_slug[ (int) $r['brand_id'] ] : null;
			$item['translations'] = $this->translations( 'product_translations', 'product_id', $id, [ 'name', 'description', 'short_desc', 'meta_title', 'meta_desc' ] );

			$item['categories'] = [];
			foreach ( $this->db->getArray( "SELECT category_id, is_main, position FROM {$pc} WHERE product_id = ?i ORDER BY position ASC", $id ) as $c ) {
				$cid = (int) $c['category_id'];
				if ( isset( $this->cat_slug[ $cid ] ) ) {
					$item['categories'][] = [ 'category' => $this->cat_slug[ $cid ], 'is_main' => (int) $c['is_main'], 'position' => (int) $c['position'] ];
				}
			}

			$item['tags'] = $this->db->getColumn(
				"SELECT t.name FROM {$pt} pt JOIN {$t} t ON t.id = pt.tag_id WHERE pt.product_id = ?i ORDER BY t.name ASC",
				$id
			);

			$item['images'] = [];
			foreach ( $this->db->getArray( "SELECT * FROM {$pi} WHERE product_id = ?i ORDER BY position ASC, id ASC", $id ) as $img ) {
				$mk = $this->mediaKey( $img['wp_attachment_id'] );
				if ( $mk ) {
					$item['images'][] = [ 'media' => $mk, 'position' => (int) $img['position'], 'is_main' => (int) $img['is_main'], 'alt_text' => $img['alt_text'] ?? '' ];
				}
			}

			$item['prices'] = [];
			foreach ( $this->db->getArray( "SELECT * FROM {$pp} WHERE product_id = ?i ORDER BY usergroup_id ASC, min_quantity ASC", $id ) as $pr ) {
				$item['prices'][] = [
					'usergroup_id'   => (int) $pr['usergroup_id'],
					'min_quantity'   => (int) $pr['min_quantity'],
					'price'          => $pr['price'],
					'discount_type'  => $pr['discount_type'],
					'discount_value' => $pr['discount_value'],
				];
			}

			$item['feature_values'] = [];
			foreach ( $this->db->getArray( "SELECT * FROM {$fv} WHERE product_id = ?i ORDER BY id ASC", $id ) as $val ) {
				$fid = (int) $val['feature_id'];
				if ( ! isset( $this->feature_key[ $fid ] ) ) {
					continue;
				}
				$vid = (int) $val['variant_id'];
				$item['feature_values'][] = [
					'feature'     => $this->feature_key[ $fid ],
					'variant'     => ( $vid > 0 && isset( $this->variant_key[ $vid ] ) ) ? $this->variant_key[ $vid ] : null,
					'value_int'   => $val['value_int'],
					'value_float' => $val['value_float'],
					'value_str'   => $val['value_str'],
					'value_text'  => $val['value_text'],
					'value_date'  => $val['value_date'],
					'image'       => $this->mediaKey( $val['image_id'] ),
				];
			}

			$item['options'] = $this->exportOptions( $id );

			$out[] = $item;
		}
		return $out;
	}

	/** Add-on services ("Options") for one product, with variants + dependencies. */
	private function exportOptions( int $product_id ): array {
		$o   = $this->db->table( 'product_options' );
		$ov  = $this->db->table( 'product_option_variants' );
		$od  = $this->db->table( 'product_option_dependencies' );
		$oe  = $this->db->table( 'product_option_exceptions' );

		$rows = $this->db->getArray( "SELECT * FROM {$o} WHERE product_id = ?i ORDER BY position ASC, id ASC", $product_id );
		if ( empty( $rows ) ) {
			return [];
		}

		$opt_key = [];
		$var_key = [];
		$options = [];
		foreach ( $rows as $i => $r ) {
			$oid = (int) $r['id'];
			$tr  = $this->translations( 'product_option_translations', 'option_id', $oid, [ 'name', 'comment' ] );
			$key = 'opt' . ( $i + 1 ) . '-' . $this->stableSlug( '', $tr, 'option-' . $oid );
			$opt_key[ $oid ] = $key;

			$vars = [];
			foreach ( $this->db->getArray( "SELECT * FROM {$ov} WHERE option_id = ?i ORDER BY position ASC, id ASC", $oid ) as $j => $v ) {
				$vtr  = $this->translations( 'product_option_variant_translations', 'variant_id', (int) $v['id'], [ 'name' ] );
				$vkey = $key . ':' . $this->stableSlug( '', $vtr, 'variant-' . $v['id'] );
				$var_key[ (int) $v['id'] ] = $vkey;
				$vars[] = [
					'key'             => $vkey,
					'position'        => (int) $v['position'],
					'modifier'        => $v['modifier'],
					'modifier_type'   => $v['modifier_type'],
					'weight_modifier' => $v['weight_modifier'],
					'image'           => $this->mediaKey( $v['image_id'] ),
					'status'          => (string) $v['status'],
					'translations'    => $vtr,
				];
			}
			$options[] = [
				'key'              => $key,
				'type'             => (string) $r['type'],
				'position'         => (int) $r['position'],
				'required'         => (int) $r['required'],
				'compound_pricing' => (int) $r['compound_pricing'],
				'status'           => (string) $r['status'],
				'translations'     => $tr,
				'variants'         => $vars,
				'dependencies'     => [],
			];
		}

		// Sequential-option edges, remapped to keys.
		$option_ids = array_keys( $opt_key );
		foreach ( $this->db->getArray( "SELECT * FROM {$od} WHERE option_id IN (?a)", $option_ids ) as $d ) {
			$oid = (int) $d['option_id'];
			$po  = (int) $d['parent_option_id'];
			$pv  = (int) $d['parent_variant_id'];
			if ( ! isset( $opt_key[ $po ], $var_key[ $pv ] ) ) {
				continue;
			}
			foreach ( $options as &$opt ) {
				if ( $opt['key'] === $opt_key[ $oid ] ) {
					$opt['dependencies'][] = [ 'parent_option' => $opt_key[ $po ], 'parent_variant' => $var_key[ $pv ] ];
				}
			}
			unset( $opt );
		}

		// Exceptions are stored as {option_id: variant_id} combos — remap to keys.
		$exceptions = [];
		foreach ( $this->db->getArray( "SELECT * FROM {$oe} WHERE product_id = ?i", $product_id ) as $e ) {
			$combo = json_decode( (string) $e['combination'], true );
			if ( ! is_array( $combo ) ) {
				continue;
			}
			$mapped = [];
			foreach ( $combo as $oid => $vid ) {
				if ( isset( $opt_key[ (int) $oid ], $var_key[ (int) $vid ] ) ) {
					$mapped[ $opt_key[ (int) $oid ] ] = $var_key[ (int) $vid ];
				}
			}
			if ( $mapped ) {
				$exceptions[] = [ 'type' => (string) $e['type'], 'combination' => $mapped ];
			}
		}
		if ( $exceptions ) {
			$options[0]['_product_exceptions'] = $exceptions; // carried on the first option; importer hoists it
		}

		return $options;
	}

	/** Variation groups whose members are all exported products. */
	private function exportGroups( array $product_ids ): array {
		$g  = $this->db->table( 'product_groups' );
		$gp = $this->db->table( 'product_group_products' );

		$group_ids = $this->db->getColumn( "SELECT DISTINCT group_id FROM {$gp} WHERE product_id IN (?a)", $product_ids );
		if ( empty( $group_ids ) ) {
			return [];
		}
		$out = [];
		foreach ( $this->db->getArray( "SELECT * FROM {$g} WHERE id IN (?a) ORDER BY id ASC", array_map( 'intval', $group_ids ) ) as $grp ) {
			$fids = json_decode( (string) $grp['feature_ids'], true );
			$fkeys = [];
			foreach ( (array) $fids as $fid ) {
				if ( isset( $this->feature_key[ (int) $fid ] ) ) {
					$fkeys[] = $this->feature_key[ (int) $fid ];
				}
			}
			$members = [];
			foreach ( $this->db->getArray( "SELECT * FROM {$gp} WHERE group_id = ?i ORDER BY position ASC, id ASC", (int) $grp['id'] ) as $m ) {
				$pid = (int) $m['product_id'];
				if ( ! isset( $this->product_key[ $pid ] ) ) {
					continue;
				}
				$combo  = json_decode( (string) $m['combination'], true );
				$mapped = [];
				foreach ( (array) $combo as $fid => $vid ) {
					if ( isset( $this->feature_key[ (int) $fid ], $this->variant_key[ (int) $vid ] ) ) {
						$mapped[ $this->feature_key[ (int) $fid ] ] = $this->variant_key[ (int) $vid ];
					}
				}
				$members[] = [ 'product' => $this->product_key[ $pid ], 'combination' => $mapped, 'position' => (int) $m['position'] ];
			}
			if ( empty( $members ) ) {
				continue;
			}
			$out[] = [
				'key'               => (string) $grp['group_code'],
				'group_code'        => (string) $grp['group_code'],
				'features'          => $fkeys,
				'display_as_single' => (int) $grp['display_as_single'],
				'display_name'      => (string) $grp['display_name'],
				'members'           => $members,
			];
		}
		return $out;
	}

	/**
	 * The preset's theme options with the prefix stripped. Category-id lists
	 * become slug lists; image URLs become "{{media:key}}" placeholders when
	 * they resolve to a local attachment (bundled theme assets stay as-is).
	 */
	private function exportThemeOptions( array $options ): array {
		$out = [];
		foreach ( $options as $bare => $value ) {
			if ( in_array( $bare, self::SKIP_OPTION_SUFFIXES, true ) ) {
				continue;
			}
			if ( in_array( $bare, self::CATEGORY_LIST_SUFFIXES, true ) ) {
				$out[ $bare ] = implode( ',', $this->slugsFor( $this->csvIds( $value ) ) );
				continue;
			}
			$value = (string) $value;
			if ( preg_match( '#^https?://#i', $value ) && preg_match( '/\.(jpe?g|png|gif|webp|avif|svg)(\?.*)?$/i', $value ) ) {
				$att_id = (int) attachment_url_to_postid( preg_replace( '/-\d+x\d+(?=\.[a-z]+$)/i', '', strtok( $value, '?' ) ) );
				if ( $att_id > 0 ) {
					$mk = $this->mediaKey( $att_id );
					if ( $mk ) {
						$out[ $bare ] = '{{media:' . $mk . '}}';
						continue;
					}
				}
			}
			$out[ $bare ] = $value;
		}
		ksort( $out );
		return $out;
	}

	// ──────────────────────────────────────────────────────────────────────────
	// Helpers
	// ──────────────────────────────────────────────────────────────────────────

	/** All `<preset>_*` keys from theme_options with the prefix stripped. */
	private function presetOptions( string $preset ): array {
		$prefix = $preset . '_';
		$len    = strlen( $prefix );
		$out    = [];
		foreach ( SettingsService::section( 'theme_options' ) as $key => $value ) {
			if ( 0 === strpos( (string) $key, $prefix ) ) {
				$out[ substr( (string) $key, $len ) ] = $value;
			}
		}
		return $out;
	}

	/**
	 * Register an attachment in the media map (copying its file into the
	 * package) and return its key. Null when the id is empty or the file is
	 * missing — callers then simply omit the image.
	 */
	private function mediaKey( $attachment_id ): ?string {
		$attachment_id = (int) $attachment_id;
		if ( $attachment_id <= 0 ) {
			return null;
		}
		if ( isset( $this->media_by_att[ $attachment_id ] ) ) {
			return $this->media_by_att[ $attachment_id ];
		}
		$file = get_attached_file( $attachment_id );
		if ( ! $file || ! is_readable( $file ) ) {
			return null;
		}
		$url = wp_get_attachment_url( $attachment_id );
		$key = 'att:' . $attachment_id;

		$base = sanitize_file_name( basename( $file ) );
		$rel  = 'media/' . $attachment_id . '-' . $base;
		$dest = $this->pkg_dir . '/' . $rel;
		if ( ! file_exists( $dest ) ) {
			copy( $file, $dest ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_copy -- copying a local upload into the self-owned export package dir.
		}

		$this->media[ $key ] = [
			'file'  => $rel,
			'url'   => $url ?: '',
			'title' => get_the_title( $attachment_id ),
			'alt'   => (string) get_post_meta( $attachment_id, '_wp_attachment_image_alt', true ),
			'mime'  => (string) get_post_mime_type( $attachment_id ),
		];
		$this->media_by_att[ $attachment_id ] = $key;
		return $key;
	}

	/**
	 * Translation rows for one entity, keyed by language code.
	 *
	 * @return array<string,array<string,string|null>>
	 */
	private function translations( string $table, string $fk, int $id, array $cols ): array {
		$sel  = implode( ', ', array_map( fn( $c ) => "`{$c}`", array_merge( [ 'language_code' ], $cols ) ) );
		$rows = $this->db->getArray( "SELECT {$sel} FROM {$this->db->table( $table )} WHERE `{$fk}` = ?i ORDER BY language_code ASC", $id );
		$out  = [];
		foreach ( $rows as $r ) {
			$lang = (string) $r['language_code'];
			unset( $r['language_code'] );
			$out[ $lang ] = $r;
		}
		return $out;
	}

	/** Existing slug, else slug of the first translated name, else fallback. */
	private function stableSlug( string $slug, array $translations, string $fallback ): string {
		$slug = trim( $slug );
		if ( '' !== $slug ) {
			return $slug;
		}
		$name = '';
		if ( isset( $translations['en']['name'] ) && '' !== trim( (string) $translations['en']['name'] ) ) {
			$name = (string) $translations['en']['name'];
		} else {
			foreach ( $translations as $tr ) {
				if ( ! empty( $tr['name'] ) ) {
					$name = (string) $tr['name'];
					break;
				}
			}
		}
		$s = sanitize_title( $name );
		return '' !== $s ? $s : $fallback;
	}

	private function csvIds( $csv ): array {
		$parts = is_array( $csv ) ? $csv : ( ( (string) $csv === '' ) ? [] : explode( ',', (string) $csv ) );
		return array_values( array_unique( array_filter( array_map( 'intval', $parts ) ) ) );
	}

	/** Category ids → exported slugs (unknown ids dropped). */
	private function slugsFor( array $ids ): array {
		$out = [];
		foreach ( $ids as $id ) {
			if ( isset( $this->cat_slug[ (int) $id ] ) ) {
				$out[] = $this->cat_slug[ (int) $id ];
			}
		}
		return $out;
	}

	private function reset(): void {
		$this->media        = [];
		$this->media_by_att = [];
		$this->cat_slug     = [];
		$this->feature_key  = [];
		$this->variant_key  = [];
		$this->product_key  = [];
		$this->brand_slug   = [];
		$this->pkg_dir      = '';
	}

	/** Fresh `exports/demos/<preset>/` (+ media/) — wiped before each export. */
	private function packageDir( string $preset ): string {
		$upload = wp_upload_dir();
		$root   = $upload['basedir'] . '/titancart/exports';
		if ( ! is_dir( $root ) ) {
			wp_mkdir_p( $root );
			file_put_contents( $root . '/.htaccess', "Options -Indexes\n" ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_file_put_contents -- tiny self-owned .htaccess in the exports dir.
		}
		$dir = $root . '/demos/' . $preset;
		if ( is_dir( $dir ) ) {
			$this->rrmdir( $dir );
		}
		wp_mkdir_p( $dir . '/media' );
		if ( ! is_dir( $dir . '/media' ) ) {
			throw new \RuntimeException( esc_html( "Could not create demo package directory: {$dir}" ) );
		}
		return $dir;
	}

	private function rrmdir( string $dir ): void {
		foreach ( scandir( $dir ) ?: [] as $entry ) {
			if ( '.' === $entry || '..' === $entry ) {
				continue;
			}
			$path = $dir . '/' . $entry;
			if ( is_dir( $path ) ) {
				$this->rrmdir( $path );
			} else {
				wp_delete_file( $path );
			}
		}
		rmdir( $dir ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_rmdir -- self-owned export scratch dir.
	}

	/** Zip `<preset>/` → `<preset>.zip` beside it. Empty string when ZipArchive is unavailable. */
	private function zipPackage( string $preset ): string {
		if ( ! class_exists( '\ZipArchive' ) ) {
			return '';
		}
		$zip_path = dirname( $this->pkg_dir ) . '/' . $preset . '.zip';
		if ( file_exists( $zip_path ) ) {
			wp_delete_file( $zip_path );
		}
		$zip = new \ZipArchive();
		if ( true !== $zip->open( $zip_path, \ZipArchive::CREATE ) ) {
			return '';
		}
		$zip->addFile( $this->pkg_dir . '/package.json', $preset . '/package.json' );
		foreach ( glob( $this->pkg_dir . '/media/*' ) ?: [] as $file ) {
			$zip->addFile( $file, $preset . '/media/' . basename( $file ) );
		}
		$zip->close();
		return $zip_path;
	}
}
