<?php
/**
 * TitanCart WooCommerce Import Service
 *
 * Detects WooCommerce data, counts entities, and migrates them
 * into TitanCart custom tables in batches.
 *
 * @package TitanCart\Modules\WooImport
 */

namespace TitanCart\Modules\WooImport;

use TitanCart\DB\Database;

defined( 'ABSPATH' ) || exit;

// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter -- File-wide by design: every flagged query interpolates ONLY the WP/Woo table-name prefix ($p = $wpdb->prefix, a wpdb constant, never user input) to read WooCommerce's OWN posts/postmeta/order tables during one-time migration; all row values are bound via $wpdb->prepare() placeholders at each site. See docs/WPORG-REVIEWER-NOTES.md.

class WooImportService {

    private Database $db;
    private string   $lang = 'en';

    /** Transient key for progress tracking. */
    const PROGRESS_KEY = 'titancart_woo_import_progress';

    /** Batch size for each import chunk. */
    const BATCH = 50;

    public function __construct() {
        $this->db   = new Database();
        $this->lang = substr( get_locale(), 0, 2 ) ?: 'en';
    }

    /* ───────────────────────────────────────────────────────────────────
     *  DETECTION
     * ─────────────────────────────────────────────────────────────────── */

    /**
     * Check if WooCommerce tables / data exist on this site.
     */
    public function detect(): array {
        global $wpdb;

        $wc_active  = class_exists( 'WooCommerce' ) || in_array( 'woocommerce/woocommerce.php', (array) get_option( 'active_plugins', [] ), true );
        $wc_version = get_option( 'woocommerce_version', '' );

        // Check for HPOS (High-Performance Order Storage).
        $hpos_table = $wpdb->prefix . 'wc_orders';
        $hpos_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $hpos_table ) ) === $hpos_table;
        $hpos_enabled = $hpos_exists && get_option( 'woocommerce_custom_orders_table_enabled', 'no' ) === 'yes';

        return [
            'wc_active'    => $wc_active,
            'wc_version'   => $wc_version,
            'hpos_enabled' => $hpos_enabled,
            'counts'       => $this->count_entities( $hpos_enabled ),
        ];
    }

    /**
     * Count all WooCommerce entities available for import.
     */
    private function count_entities( bool $hpos ): array {
        global $wpdb;

        $products   = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_type = 'product' AND post_status IN ('publish','draft','private')" );
        $categories = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->term_taxonomy} WHERE taxonomy = 'product_cat'" );
        $tags       = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->term_taxonomy} WHERE taxonomy = 'product_tag'" );
        $reviews    = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->comments} WHERE comment_type = 'review' AND comment_approved IN ('1','0')" );
        $coupons    = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_type = 'shop_coupon' AND post_status = 'publish'" );

        if ( $hpos ) {
            $hpos_table = $wpdb->prefix . 'wc_orders';
            $orders = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$hpos_table} WHERE type = 'shop_order'" );
        } else {
            $orders = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_type = 'shop_order' AND post_status != 'trash'" );
        }

        $customers = (int) $wpdb->get_var(
            "SELECT COUNT(DISTINCT u.ID)
             FROM {$wpdb->users} u
             INNER JOIN {$wpdb->usermeta} um ON u.ID = um.user_id
             WHERE um.meta_key = '{$wpdb->prefix}capabilities'
               AND um.meta_value LIKE '%\"customer\"%'"
        );

        return compact( 'products', 'categories', 'tags', 'orders', 'customers', 'reviews', 'coupons' );
    }

    /* ───────────────────────────────────────────────────────────────────
     *  PROGRESS TRACKING
     * ─────────────────────────────────────────────────────────────────── */

    private function init_progress( array $entities ): void {
        $progress = [
            'status'   => 'running',
            'entities' => $entities,
            'current'  => '',
            'step'     => 0,
            'total'    => 0,
            'done'     => 0,
            'log'      => [],
            'errors'   => [],
            'started'  => current_time( 'mysql' ),
        ];
        set_transient( self::PROGRESS_KEY, $progress, HOUR_IN_SECONDS );
    }

    private function update_progress( array $fields ): void {
        $progress = get_transient( self::PROGRESS_KEY ) ?: [];
        $progress = array_merge( $progress, $fields );
        set_transient( self::PROGRESS_KEY, $progress, HOUR_IN_SECONDS );
    }

    private function log( string $msg ): void {
        $progress = get_transient( self::PROGRESS_KEY ) ?: [];
        $progress['log'][] = '[' . current_time( 'H:i:s' ) . '] ' . $msg;
        set_transient( self::PROGRESS_KEY, $progress, HOUR_IN_SECONDS );
    }

    private function log_error( string $msg ): void {
        $progress = get_transient( self::PROGRESS_KEY ) ?: [];
        $progress['errors'][] = $msg;
        $progress['log'][]    = '[' . current_time( 'H:i:s' ) . '] ERROR: ' . $msg;
        set_transient( self::PROGRESS_KEY, $progress, HOUR_IN_SECONDS );
    }

    public function get_progress(): array {
        return get_transient( self::PROGRESS_KEY ) ?: [ 'status' => 'idle' ];
    }

    /* ───────────────────────────────────────────────────────────────────
     *  MAIN IMPORT ORCHESTRATOR
     * ─────────────────────────────────────────────────────────────────── */

    /**
     * Run full import.  Called synchronously (admin REST call with long timeout).
     *
     * @param array $entities Which entities to import: ['categories','products','orders',...]
     * @return array Final progress snapshot.
     */
    public function run( array $entities ): array {
        // Increase limits for long import.
        if ( ! defined( 'WP_CLI' ) ) {
            @set_time_limit( 600 ); // phpcs:ignore Squiz.PHP.DiscouragedFunctions.Discouraged -- long-running Woo migration run; raising the limit is the point, and there is no WP API for execution time.
            wp_raise_memory_limit( 'admin' );
        }

        $detection = $this->detect();
        $hpos      = $detection['hpos_enabled'];

        $this->init_progress( $entities );

        // ID maps for cross-referencing.
        $cat_map     = []; // WC term_id → TC category_id
        $product_map = []; // WC post_id  → TC product_id
        $status_map  = $this->build_order_status_map();

        // Import in dependency order.
        if ( in_array( 'categories', $entities, true ) ) {
            $cat_map = $this->import_categories();
        }

        if ( in_array( 'tags', $entities, true ) ) {
            $this->import_tags();
        }

        if ( in_array( 'products', $entities, true ) ) {
            $product_map = $this->import_products( $cat_map );
        }

        if ( in_array( 'customers', $entities, true ) ) {
            $this->import_customers();
        }

        if ( in_array( 'orders', $entities, true ) ) {
            $this->import_orders( $hpos, $product_map, $status_map );
        }

        if ( in_array( 'reviews', $entities, true ) ) {
            $this->import_reviews( $product_map );
        }

        if ( in_array( 'coupons', $entities, true ) ) {
            $this->import_coupons();
        }

        $this->update_progress( [ 'status' => 'complete', 'finished' => current_time( 'mysql' ) ] );
        $this->log( 'Import complete.' );

        return $this->get_progress();
    }

    /* ───────────────────────────────────────────────────────────────────
     *  CATEGORIES
     * ─────────────────────────────────────────────────────────────────── */

    private function import_categories(): array {
        global $wpdb;
        $this->update_progress( [ 'current' => 'categories', 'step' => 0 ] );
        $this->log( 'Importing categories...' );

        $rows = $wpdb->get_results(
            "SELECT t.term_id, t.name, t.slug, tt.parent, tt.description, tt.count
             FROM {$wpdb->terms} t
             INNER JOIN {$wpdb->term_taxonomy} tt ON t.term_id = tt.term_id
             WHERE tt.taxonomy = 'product_cat'
             ORDER BY tt.parent ASC, t.name ASC"
        );

        $total = count( $rows );
        $this->update_progress( [ 'total' => $total ] );

        $map   = []; // wc_term_id => tc_id
        $db    = $this->db;
        $p     = $db->prefix;
        $done  = 0;

        foreach ( $rows as $row ) {
            $parent_tc = 0;
            if ( $row->parent && isset( $map[ $row->parent ] ) ) {
                $parent_tc = $map[ $row->parent ];
            }

            // Check for existing slug to avoid duplicates.
            $existing = $wpdb->get_var( $wpdb->prepare(
                "SELECT id FROM {$p}categories WHERE slug = %s AND deleted_at IS NULL",
                $row->slug
            ) );

            if ( $existing ) {
                $map[ $row->term_id ] = (int) $existing;
                $done++;
                $this->update_progress( [ 'done' => $done ] );
                continue;
            }

            $wpdb->insert( "{$p}categories", [
                'parent_id'  => $parent_tc,
                'slug'       => $row->slug,
                'position'   => 0,
                'status'     => 'active',
                'created_at' => current_time( 'mysql' ),
                'updated_at' => current_time( 'mysql' ),
            ] );

            $tc_id = (int) $wpdb->insert_id;
            $map[ $row->term_id ] = $tc_id;

            // Translation row.
            $wpdb->insert( "{$p}category_translations", [
                'category_id'   => $tc_id,
                'language_code' => $this->lang,
                'name'          => $row->name,
                'description'   => $row->description ?: null,
            ] );

            // SEO meta from Yoast / Rank Math if available.
            $meta_title = get_term_meta( $row->term_id, '_yoast_wpseo_title', true )
                       ?: get_term_meta( $row->term_id, 'rank_math_title', true );
            $meta_desc  = get_term_meta( $row->term_id, '_yoast_wpseo_metadesc', true )
                       ?: get_term_meta( $row->term_id, 'rank_math_description', true );

            if ( $meta_title || $meta_desc ) {
                $wpdb->update( "{$p}category_translations",
                    array_filter( [ 'meta_title' => $meta_title ?: null, 'meta_desc' => $meta_desc ?: null ] ),
                    [ 'category_id' => $tc_id, 'language_code' => $this->lang ]
                );
            }

            $done++;
            $this->update_progress( [ 'done' => $done ] );
        }

        // Rebuild nested set (lft/rgt/depth) after all categories inserted.
        $this->rebuild_category_tree( $p );

        $this->log( "Categories: {$done} imported." );
        return $map;
    }

    /**
     * Rebuild lft/rgt/depth for the nested-set tree.
     */
    private function rebuild_category_tree( string $p ): void {
        global $wpdb;

        $all = $wpdb->get_results( "SELECT id, parent_id FROM {$p}categories WHERE deleted_at IS NULL ORDER BY position ASC, id ASC" );
        $children = [];
        foreach ( $all as $row ) {
            $pid = (int) $row->parent_id;
            $children[ $pid ][] = (int) $row->id;
        }

        $counter = 0;
        $walk = function( int $parent_id, int $depth ) use ( &$walk, &$counter, &$children, $wpdb, $p ) {
            if ( empty( $children[ $parent_id ] ) ) return;
            foreach ( $children[ $parent_id ] as $id ) {
                $lft = ++$counter;
                $walk( $id, $depth + 1 );
                $rgt = ++$counter;
                $wpdb->update( "{$p}categories", [ 'lft' => $lft, 'rgt' => $rgt, 'depth' => $depth ], [ 'id' => $id ] );
            }
        };
        $walk( 0, 0 );
    }

    /* ───────────────────────────────────────────────────────────────────
     *  TAGS
     * ─────────────────────────────────────────────────────────────────── */

    private function import_tags(): void {
        global $wpdb;
        $this->update_progress( [ 'current' => 'tags', 'step' => 0, 'done' => 0 ] );
        $this->log( 'Importing tags...' );

        $p = $this->db->prefix;

        $rows = $wpdb->get_results(
            "SELECT t.term_id, t.name, t.slug
             FROM {$wpdb->terms} t
             INNER JOIN {$wpdb->term_taxonomy} tt ON t.term_id = tt.term_id
             WHERE tt.taxonomy = 'product_tag'
             ORDER BY t.name ASC"
        );

        $this->update_progress( [ 'total' => count( $rows ) ] );
        $done = 0;

        foreach ( $rows as $row ) {
            $existing = $wpdb->get_var( $wpdb->prepare(
                "SELECT id FROM {$p}tags WHERE slug = %s", $row->slug
            ) );
            if ( ! $existing ) {
                $wpdb->insert( "{$p}tags", [
                    'slug'       => $row->slug,
                    'name'       => $row->name,
                    'created_at' => current_time( 'mysql' ),
                ] );
            }
            $done++;
            $this->update_progress( [ 'done' => $done ] );
        }

        $this->log( "Tags: {$done} imported." );
    }

    /* ───────────────────────────────────────────────────────────────────
     *  PRODUCTS
     * ─────────────────────────────────────────────────────────────────── */

    private function import_products( array $cat_map ): array {
        global $wpdb;
        $this->update_progress( [ 'current' => 'products', 'step' => 0, 'done' => 0 ] );
        $this->log( 'Importing products...' );

        $p = $this->db->prefix;

        $total = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_type = 'product' AND post_status IN ('publish','draft','private')" );
        $this->update_progress( [ 'total' => $total ] );

        $map    = [];
        $offset = 0;
        $done   = 0;

        while ( $offset < $total ) {
            $products = $wpdb->get_results( $wpdb->prepare(
                "SELECT ID, post_title, post_content, post_excerpt, post_name, post_status, post_date
                 FROM {$wpdb->posts}
                 WHERE post_type = 'product' AND post_status IN ('publish','draft','private')
                 ORDER BY ID ASC LIMIT %d OFFSET %d",
                self::BATCH, $offset
            ) );

            foreach ( $products as $wc ) {
                $tc_id = $this->import_single_product( $wc, $cat_map );
                if ( $tc_id ) {
                    $map[ $wc->ID ] = $tc_id;
                }
                $done++;
                $this->update_progress( [ 'done' => $done ] );
            }

            $offset += self::BATCH;
        }

        $this->log( "Products: {$done} imported." );
        return $map;
    }

    private function import_single_product( object $wc, array $cat_map ): ?int {
        global $wpdb;
        $p = $this->db->prefix;

        // Get all meta in one query.
        $raw_meta = $wpdb->get_results( $wpdb->prepare(
            "SELECT meta_key, meta_value FROM {$wpdb->postmeta} WHERE post_id = %d", $wc->ID
        ) );
        $meta = [];
        foreach ( $raw_meta as $m ) {
            $meta[ $m->meta_key ] = $m->meta_value;
        }

        $sku = $meta['_sku'] ?? '';

        // Skip duplicates by SKU if non-empty (exclude soft-deleted).
        if ( $sku !== '' ) {
            $existing = $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$p}products WHERE sku = %s AND deleted_at IS NULL", $sku ) );
            if ( $existing ) return (int) $existing;
        }

        // Map status.
        $status_map = [ 'publish' => 'active', 'draft' => 'disabled', 'private' => 'hidden' ];
        $status     = $status_map[ $wc->post_status ] ?? 'disabled';

        // Map product type from WC taxonomy.
        $wc_type = $this->get_wc_product_type( $wc->ID );
        $tc_type = 'simple';
        if ( $wc_type === 'downloadable' || ( $meta['_downloadable'] ?? 'no' ) === 'yes' ) $tc_type = 'downloadable';
        if ( ( $meta['_virtual'] ?? 'no' ) === 'yes' && $tc_type === 'simple' ) $tc_type = 'virtual';

        // ── Variable products → Product Groups ───────────────────────────
        // Each WC variation becomes a standalone TC product linked via a
        // product group.  The shared description / images / categories are
        // copied from the WC parent to every member.
        if ( $wc_type === 'variable' ) {
            return $this->import_variable_as_group( $wc, $meta, $cat_map, $status );
        }

        $regular_price = (float) ( $meta['_regular_price'] ?? 0 );
        $sale_price    = (float) ( $meta['_sale_price']    ?? 0 );
        $price         = $sale_price > 0 ? $sale_price : $regular_price;
        $list_price    = ( $sale_price > 0 && $regular_price > $sale_price ) ? $regular_price : 0;

        $stock_status_wc = $meta['_stock_status'] ?? 'instock';
        $stock_map = [ 'instock' => 'in_stock', 'outofstock' => 'out_of_stock', 'onbackorder' => 'on_backorder' ];

        $wpdb->insert( "{$p}products", [
            'sku'              => $sku,
            'slug'             => $wc->post_name,
            'price'            => $price,
            'list_price'       => $list_price,
            'weight'           => (float) ( $meta['_weight'] ?? 0 ),
            'length'           => ( $meta['_length'] ?? '' ) !== '' ? (float) $meta['_length'] : null,
            'width'            => ( $meta['_width']  ?? '' ) !== '' ? (float) $meta['_width']  : null,
            'height'           => ( $meta['_height'] ?? '' ) !== '' ? (float) $meta['_height'] : null,
            'stock_quantity'   => (int) ( $meta['_stock'] ?? 0 ),
            'stock_status'     => $stock_map[ $stock_status_wc ] ?? 'in_stock',
            'track_inventory'  => ( $meta['_manage_stock'] ?? 'no' ) === 'yes' ? 1 : 0,
            'allow_backorders' => ( $meta['_backorders'] ?? 'no' ) !== 'no' ? 1 : 0,
            'is_downloadable'  => ( $meta['_downloadable'] ?? 'no' ) === 'yes' ? 1 : 0,
            'requires_shipping'=> ( $meta['_virtual'] ?? 'no' ) === 'yes' ? 0 : 1,
            'free_shipping'    => 0,
            'type'             => $tc_type,
            'status'           => $status,
            'featured'         => ( $meta['_featured'] ?? 'no' ) === 'yes' ? 1 : 0,
            'rating_avg'       => (float) ( $meta['_wc_average_rating'] ?? 0 ),
            'rating_count'     => (int) ( $meta['_wc_review_count'] ?? 0 ),
            'sales_count'      => (int) ( $meta['total_sales'] ?? 0 ),
            'created_at'       => $wc->post_date,
            'updated_at'       => current_time( 'mysql' ),
        ] );

        $tc_id = (int) $wpdb->insert_id;
        if ( ! $tc_id ) {
            $this->log_error( "Failed to insert product WC#{$wc->ID} ({$wc->post_title})" );
            return null;
        }

        // Translation.
        $meta_title = $meta['_yoast_wpseo_title']    ?? ( $meta['rank_math_title'] ?? '' );
        $meta_desc  = $meta['_yoast_wpseo_metadesc'] ?? ( $meta['rank_math_description'] ?? '' );

        $wpdb->insert( "{$p}product_translations", [
            'product_id'    => $tc_id,
            'language_code' => $this->lang,
            'name'          => $wc->post_title,
            'description'   => $wc->post_content ?: null,
            'short_desc'    => $wc->post_excerpt ?: null,
            'meta_title'    => $meta_title ?: null,
            'meta_desc'     => $meta_desc  ?: null,
        ] );

        // Category links.
        $wc_cats = $wpdb->get_col( $wpdb->prepare(
            "SELECT tt.term_id
             FROM {$wpdb->term_relationships} tr
             INNER JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
             WHERE tr.object_id = %d AND tt.taxonomy = 'product_cat'",
            $wc->ID
        ) );
        $first = true;
        foreach ( $wc_cats as $wc_cat_id ) {
            $tc_cat = $cat_map[ $wc_cat_id ] ?? null;
            if ( $tc_cat ) {
                $wpdb->insert( "{$p}product_categories", [
                    'product_id'  => $tc_id,
                    'category_id' => $tc_cat,
                    'is_main'     => $first ? 1 : 0,
                ] );
                $first = false;
            }
        }

        // Tag links.
        $wc_tags = $wpdb->get_results( $wpdb->prepare(
            "SELECT t.name, t.slug
             FROM {$wpdb->term_relationships} tr
             INNER JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
             INNER JOIN {$wpdb->terms} t ON tt.term_id = t.term_id
             WHERE tr.object_id = %d AND tt.taxonomy = 'product_tag'",
            $wc->ID
        ) );
        foreach ( $wc_tags as $tag ) {
            $tag_id = $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$p}tags WHERE slug = %s", $tag->slug ) );
            if ( $tag_id ) {
                $wpdb->replace( "{$p}product_tags", [
                    'product_id' => $tc_id,
                    'tag_id'     => (int) $tag_id,
                ] );
            }
        }

        // Images — main + gallery.
        $this->import_product_images( $wc->ID, $tc_id, $meta );

        return $tc_id;
    }

    /**
     * Get WooCommerce product type taxonomy term.
     */
    private function get_wc_product_type( int $post_id ): string {
        global $wpdb;
        $type = $wpdb->get_var( $wpdb->prepare(
            "SELECT t.slug
             FROM {$wpdb->term_relationships} tr
             INNER JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
             INNER JOIN {$wpdb->terms} t ON tt.term_id = t.term_id
             WHERE tr.object_id = %d AND tt.taxonomy = 'product_type'",
            $post_id
        ) );
        return $type ?: 'simple';
    }

    /**
     * Import product images — reuses WordPress attachment IDs.
     */
    private function import_product_images( int $wc_id, int $tc_id, array $meta ): void {
        global $wpdb;
        $p = $this->db->prefix;

        $thumb_id = (int) ( $meta['_thumbnail_id'] ?? 0 );
        $gallery  = array_filter( array_map( 'intval', explode( ',', $meta['_product_image_gallery'] ?? '' ) ) );

        $all_ids  = [];
        if ( $thumb_id ) $all_ids[] = $thumb_id;
        $all_ids = array_merge( $all_ids, $gallery );
        $all_ids = array_unique( $all_ids );

        $pos = 0;
        foreach ( $all_ids as $att_id ) {
            // Verify attachment exists.
            if ( ! wp_get_attachment_url( $att_id ) ) continue;

            $alt = get_post_meta( $att_id, '_wp_attachment_image_alt', true );

            $wpdb->insert( "{$p}product_images", [
                'product_id'      => $tc_id,
                'wp_attachment_id' => $att_id,
                'position'        => $pos,
                'is_main'         => ( $pos === 0 ) ? 1 : 0,
                'alt_text'        => $alt ?: null,
            ] );
            $pos++;
        }
    }

    /* ───────────────────────────────────────────────────────────────────
     *  VARIABLE PRODUCT → PRODUCT GROUP
     *
     *  Each WC variation becomes a standalone TC product.  All are linked
     *  via a product group so the storefront shows them as one page with
     *  attribute selectors.
     * ─────────────────────────────────────────────────────────────────── */

    /**
     * Import a WC variable product by creating one TC product per variation
     * and grouping them via the Product Groups system.
     *
     * @return int|null  TC product ID of the first (default) variation.
     */
    private function import_variable_as_group( object $wc, array $meta, array $cat_map, string $status ): ?int {
        global $wpdb;
        $p = $this->db->prefix;

        // ── 1. Parse WC attributes from parent meta ──────────────────────
        $wc_attrs = maybe_unserialize( $meta['_product_attributes'] ?? '' );
        if ( ! is_array( $wc_attrs ) || empty( $wc_attrs ) ) {
            // No attributes — fall back to importing as a simple product.
            $this->log( "  Variable #{$wc->ID} has no attributes — importing as simple." );
            return null;
        }

        // ── 2. Create / find TC features + variants for each WC attribute ─
        // $feature_map: { 'pa_color' => { 'feature_id' => 42, 'variants' => { 'white' => 101, 'black' => 102 } } }
        $feature_map = [];
        foreach ( $wc_attrs as $taxonomy => $attr_data ) {
            if ( empty( $attr_data['is_variation'] ) ) continue;

            $attr_label = $attr_data['name'] ?? $taxonomy;
            // For taxonomy-based attributes, label comes from WC attribute table.
            if ( ! empty( $attr_data['is_taxonomy'] ) ) {
                $db_label = $wpdb->get_var( $wpdb->prepare(
                    "SELECT attribute_label FROM {$wpdb->prefix}woocommerce_attribute_taxonomies WHERE attribute_name = %s",
                    str_replace( 'pa_', '', $taxonomy )
                ) );
                if ( $db_label ) {
                    $attr_label = $db_label;
                } else {
                    // KI-111 (S468): the attribute-taxonomy row can be absent on
                    // CSV-imported / partially-seeded stores (Playground Woo demo
                    // blueprint). Never ship the raw taxonomy slug as the
                    // storefront axis label — prettify 'pa_color' → 'Color'.
                    $attr_label = ucwords( trim( strtr( preg_replace( '/^pa_/', '', $attr_label ), [ '-' => ' ', '_' => ' ' ] ) ) );
                }
            }

            $feature_id = $this->find_or_create_feature( $attr_label, $taxonomy );
            if ( ! $feature_id ) continue;

            // Get the terms (variant values) for this attribute on this product.
            $variant_map = [];
            if ( ! empty( $attr_data['is_taxonomy'] ) ) {
                // Taxonomy-based: get terms assigned to this product.
                $terms = $wpdb->get_results( $wpdb->prepare(
                    "SELECT t.slug, t.name
                     FROM {$wpdb->term_relationships} tr
                     INNER JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
                     INNER JOIN {$wpdb->terms} t ON tt.term_id = t.term_id
                     WHERE tr.object_id = %d AND tt.taxonomy = %s",
                    $wc->ID, $taxonomy
                ) );
                foreach ( $terms as $term ) {
                    $variant_id = $this->find_or_create_feature_variant( $feature_id, $term->name, $term->slug );
                    if ( $variant_id ) {
                        $variant_map[ $term->slug ] = $variant_id;
                    }
                }
            } else {
                // Custom (pipe-separated values stored in $attr_data['value']).
                $vals = array_map( 'trim', explode( '|', $attr_data['value'] ?? '' ) );
                foreach ( $vals as $val ) {
                    if ( $val === '' ) continue;
                    $slug = sanitize_title( $val );
                    $variant_id = $this->find_or_create_feature_variant( $feature_id, $val, $slug );
                    if ( $variant_id ) {
                        $variant_map[ $slug ] = $variant_id;
                    }
                }
            }

            // KI-111 (S468): the parent's term_relationships can be EMPTY even
            // though every variation carries its value in its own meta
            // (attribute_pa_color = 'blue') — seen on the Playground Woo demo
            // blueprint, whose products arrive via CSV without global attribute
            // terms. The variation meta is the authoritative record of which
            // values are in play, so union it in; otherwise the axis imports
            // hollow (0 variants → no feature values → feature never merges
            // into the group → empty "choose option" dropdown on the PDP, and
            // checkout accepts the unresolved selection).
            $meta_vals = $wpdb->get_col( $wpdb->prepare(
                "SELECT DISTINCT pm.meta_value
                 FROM {$wpdb->postmeta} pm
                 INNER JOIN {$wpdb->posts} v ON v.ID = pm.post_id
                 WHERE v.post_parent = %d AND v.post_type = 'product_variation'
                   AND pm.meta_key = %s AND pm.meta_value != ''",
                $wc->ID, 'attribute_' . $taxonomy
            ) );
            foreach ( $meta_vals as $mv ) {
                $slug = sanitize_title( $mv );
                if ( $slug === '' || isset( $variant_map[ $slug ] ) ) continue;
                // Prefer the term's display name when the term row exists;
                // otherwise prettify the raw value ('deep-blue' → 'Deep Blue').
                $term_name = '';
                if ( ! empty( $attr_data['is_taxonomy'] ) ) {
                    $term_name = (string) $wpdb->get_var( $wpdb->prepare(
                        "SELECT t.name
                         FROM {$wpdb->terms} t
                         INNER JOIN {$wpdb->term_taxonomy} tt ON tt.term_id = t.term_id
                         WHERE t.slug = %s AND tt.taxonomy = %s",
                        $slug, $taxonomy
                    ) );
                }
                $vname = $term_name !== ''
                    ? $term_name
                    : ucwords( trim( strtr( $mv, [ '-' => ' ', '_' => ' ' ] ) ) );
                $variant_id = $this->find_or_create_feature_variant( $feature_id, $vname, $slug );
                if ( $variant_id ) {
                    $variant_map[ $slug ] = $variant_id;
                }
            }

            $feature_map[ $taxonomy ] = [
                'feature_id' => $feature_id,
                'variants'   => $variant_map,  // slug → tc_variant_id
            ];
        }

        if ( empty( $feature_map ) ) {
            $this->log( "  Variable #{$wc->ID} — no variation attributes mapped." );
            return null;
        }

        // ── 3. Get WC variations ─────────────────────────────────────────
        $variations = $wpdb->get_results( $wpdb->prepare(
            "SELECT ID, post_title, post_name, post_status, post_date, menu_order
             FROM {$wpdb->posts}
             WHERE post_type = 'product_variation' AND post_parent = %d
               AND post_status IN ('publish','private')
             ORDER BY menu_order ASC, ID ASC",
            $wc->ID
        ) );

        if ( empty( $variations ) ) {
            $this->log( "  Variable #{$wc->ID} has no published variations." );
            return null;
        }

        // Group code = parent SKU (or parent post ID as fallback).
        $group_code = ( $meta['_sku'] ?? '' ) ?: 'wc-' . $wc->ID;

        // Parent shared data.
        $parent_desc      = $wc->post_content ?: null;
        $parent_short     = $wc->post_excerpt ?: null;
        $parent_meta_t    = $meta['_yoast_wpseo_title']    ?? ( $meta['rank_math_title'] ?? '' );
        $parent_meta_d    = $meta['_yoast_wpseo_metadesc'] ?? ( $meta['rank_math_description'] ?? '' );

        // Parent categories.
        $wc_cats = $wpdb->get_col( $wpdb->prepare(
            "SELECT tt.term_id
             FROM {$wpdb->term_relationships} tr
             INNER JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
             WHERE tr.object_id = %d AND tt.taxonomy = 'product_cat'",
            $wc->ID
        ) );

        // Parent tags.
        $wc_tags = $wpdb->get_results( $wpdb->prepare(
            "SELECT t.slug
             FROM {$wpdb->term_relationships} tr
             INNER JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
             INNER JOIN {$wpdb->terms} t ON tt.term_id = t.term_id
             WHERE tr.object_id = %d AND tt.taxonomy = 'product_tag'",
            $wc->ID
        ) );

        $first_tc_id = null;
        $var_count   = 0;

        // S469: Woo parent title = the group's storefront card name (#677).
        $group_display_name = trim( wp_strip_all_tags( (string) $wc->post_title ) );

        // ── 4. Import each variation as a standalone TC product ──────────
        foreach ( $variations as $vi => $var ) {
            // Get variation meta.
            $raw_vm = $wpdb->get_results( $wpdb->prepare(
                "SELECT meta_key, meta_value FROM {$wpdb->postmeta} WHERE post_id = %d", $var->ID
            ) );
            $vm = [];
            foreach ( $raw_vm as $m ) $vm[ $m->meta_key ] = $m->meta_value;

            $var_sku = $vm['_sku'] ?? '';

            // Duplicate by SKU → re-use the existing product, but STILL rewrite
            // feature values + group linkage before skipping the inserts.
            // KI-111 (S468): the old path `continue`d before steps 5–6, so a
            // variation imported by a broken earlier run stayed unlinked
            // forever — re-running the import is now the repair path.
            if ( $var_sku !== '' ) {
                $existing = $wpdb->get_var( $wpdb->prepare(
                    "SELECT id FROM {$p}products WHERE sku = %s AND deleted_at IS NULL", $var_sku
                ) );
                if ( $existing ) {
                    if ( ! $first_tc_id ) $first_tc_id = (int) $existing;
                    $var_count++;
                    $this->apply_variation_features( (int) $existing, $vm, $feature_map );
                    $this->link_product_group( (int) $existing, $group_code, $vi === 0, $group_display_name );
                    continue;
                }
            }

            // Prices.
            $reg = (float) ( $vm['_regular_price'] ?? 0 );
            $sal = (float) ( $vm['_sale_price']    ?? 0 );
            $vp  = $sal > 0 ? $sal : $reg;
            $vlp = ( $sal > 0 && $reg > $sal ) ? $reg : 0;

            $stock_status_wc = $vm['_stock_status'] ?? 'instock';
            $stock_map = [ 'instock' => 'in_stock', 'outofstock' => 'out_of_stock', 'onbackorder' => 'on_backorder' ];

            // Build name from attribute values: "Classic Crew Neck T-Shirt — White / M"
            $attr_parts = [];
            foreach ( $vm as $k => $v ) {
                if ( strpos( $k, 'attribute_' ) === 0 && $v !== '' ) {
                    $attr_parts[] = ucfirst( $v );
                }
            }
            $var_name = $wc->post_title . ( $attr_parts ? ' — ' . implode( ' / ', $attr_parts ) : '' );

            // Insert product.
            $wpdb->insert( "{$p}products", [
                'sku'              => $var_sku,
                'slug'             => $var->post_name ?: sanitize_title( $var_name ),
                'price'            => $vp,
                'list_price'       => $vlp,
                'weight'           => (float) ( $vm['_weight'] ?? ( $meta['_weight'] ?? 0 ) ),
                'length'           => ( $vm['_length'] ?? $meta['_length'] ?? '' ) !== '' ? (float) ( $vm['_length'] ?? $meta['_length'] ) : null,
                'width'            => ( $vm['_width']  ?? $meta['_width']  ?? '' ) !== '' ? (float) ( $vm['_width']  ?? $meta['_width']  ) : null,
                'height'           => ( $vm['_height'] ?? $meta['_height'] ?? '' ) !== '' ? (float) ( $vm['_height'] ?? $meta['_height'] ) : null,
                'stock_quantity'   => (int) ( $vm['_stock'] ?? 0 ),
                'stock_status'     => $stock_map[ $stock_status_wc ] ?? 'in_stock',
                'track_inventory'  => ( $vm['_manage_stock'] ?? 'no' ) === 'yes' ? 1 : 0,
                'allow_backorders' => ( $vm['_backorders'] ?? 'no' ) !== 'no' ? 1 : 0,
                'type'             => 'simple',
                'status'           => $status,
                'featured'         => ( $meta['_featured'] ?? 'no' ) === 'yes' ? 1 : 0,
                'sales_count'      => (int) ( $meta['total_sales'] ?? 0 ),
                'position'         => (int) $var->menu_order,
                'created_at'       => $var->post_date,
                'updated_at'       => current_time( 'mysql' ),
            ] );

            $tc_id = (int) $wpdb->insert_id;
            if ( ! $tc_id ) continue;

            if ( ! $first_tc_id ) $first_tc_id = $tc_id;
            $var_count++;

            // Translation — copy parent's description to each variation.
            $wpdb->insert( "{$p}product_translations", [
                'product_id'    => $tc_id,
                'language_code' => $this->lang,
                'name'          => $var_name,
                'description'   => $parent_desc,
                'short_desc'    => $parent_short,
                'meta_title'    => $parent_meta_t ?: null,
                'meta_desc'     => $parent_meta_d ?: null,
            ] );

            // Categories (same as parent).
            $cat_first = true;
            foreach ( $wc_cats as $wc_cat_id ) {
                $tc_cat = $cat_map[ $wc_cat_id ] ?? null;
                if ( $tc_cat ) {
                    $wpdb->insert( "{$p}product_categories", [
                        'product_id'  => $tc_id,
                        'category_id' => $tc_cat,
                        'is_main'     => $cat_first ? 1 : 0,
                    ] );
                    $cat_first = false;
                }
            }

            // Tags (same as parent).
            foreach ( $wc_tags as $tag ) {
                $tag_id = $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$p}tags WHERE slug = %s", $tag->slug ) );
                if ( $tag_id ) {
                    $wpdb->replace( "{$p}product_tags", [
                        'product_id' => $tc_id,
                        'tag_id'     => (int) $tag_id,
                    ] );
                }
            }

            // Images — use parent's images for every variation.
            $this->import_product_images( $wc->ID, $tc_id, $meta );

            // Variation-specific thumbnail overrides parent's if set.
            $var_thumb = (int) ( $vm['_thumbnail_id'] ?? 0 );
            if ( $var_thumb && wp_get_attachment_url( $var_thumb ) ) {
                // Check if it's different from the parent's main image.
                $parent_thumb = (int) ( $meta['_thumbnail_id'] ?? 0 );
                if ( $var_thumb !== $parent_thumb ) {
                    // Make variation's thumbnail the main image (position 0).
                    $alt = get_post_meta( $var_thumb, '_wp_attachment_image_alt', true );
                    // Clear is_main on all existing images for this product.
                    $wpdb->query( $wpdb->prepare(
                        "UPDATE {$p}product_images SET is_main = 0 WHERE product_id = %d", $tc_id
                    ) );
                    $wpdb->insert( "{$p}product_images", [
                        'product_id'       => $tc_id,
                        'wp_attachment_id'  => $var_thumb,
                        'position'         => 0,
                        'is_main'          => 1,
                        'alt_text'         => $alt ?: null,
                    ] );
                }
            }

            // ── 5. Set feature values on this product ─────────────────────
            $this->apply_variation_features( $tc_id, $vm, $feature_map );

            // ── 6. Link to product group ──────────────────────────────────
            $is_default = ( $vi === 0 ); // First variation = default.
            $this->link_product_group( $tc_id, $group_code, $is_default, $group_display_name );
        }

        $this->log( "  → {$var_count} variations grouped under '{$group_code}' for WC#{$wc->ID}" );
        return $first_tc_id;
    }

    /**
     * Write this variation product's feature values (upsert — one row per
     * feature). Shared by the fresh-import and the KI-111 repair paths so a
     * re-import can heal rows written by an earlier broken run.
     */
    private function apply_variation_features( int $tc_id, array $vm, array $feature_map ): void {
        global $wpdb;
        $p = $this->db->prefix;

        foreach ( $feature_map as $taxonomy => $fdata ) {
            $feature_id = (int) $fdata['feature_id'];
            $variants   = $fdata['variants']; // slug → variant_id

            // The variation's selected value for this attribute.
            $val_slug = $vm[ 'attribute_' . $taxonomy ] ?? '';
            if ( $val_slug === '' ) continue; // "Any" / not set.

            $val_slug   = sanitize_title( $val_slug );
            $variant_id = $variants[ $val_slug ] ?? null;
            if ( ! $variant_id ) continue;

            $existing = $wpdb->get_var( $wpdb->prepare(
                "SELECT id FROM {$p}product_feature_values WHERE product_id = %d AND feature_id = %d",
                $tc_id, $feature_id
            ) );
            if ( $existing ) {
                $wpdb->update(
                    "{$p}product_feature_values",
                    [ 'variant_id' => $variant_id, 'value_str' => $val_slug ],
                    [ 'id' => (int) $existing ]
                );
            } else {
                $wpdb->insert( "{$p}product_feature_values", [
                    'product_id' => $tc_id,
                    'feature_id' => $feature_id,
                    'variant_id' => $variant_id,
                    'value_str'  => $val_slug,
                ] );
            }
        }
    }

    /**
     * Find or create a TC feature (attribute) for a WC attribute.
     * Returns the feature ID.
     */
    private function find_or_create_feature( string $label, string $wc_taxonomy ): int {
        global $wpdb;
        $p = $this->db->prefix;

        $feature_code = 'wc_' . sanitize_title( str_replace( 'pa_', '', $wc_taxonomy ) );

        // Check existing by feature_code.
        $existing = $wpdb->get_var( $wpdb->prepare(
            "SELECT id FROM {$p}product_features WHERE feature_code = %s AND status = 'active'",
            $feature_code
        ) );
        if ( $existing ) {
            // KI-111 (S468): a broken earlier run could have stored the RAW
            // taxonomy slug ('pa_color') as the display name. Heal exactly
            // that stale value on re-import; a user-edited name never matches
            // the raw taxonomy and is never touched.
            if ( $label !== '' && $label !== $wc_taxonomy ) {
                $wpdb->update(
                    "{$p}product_feature_translations",
                    [ 'name' => $label ],
                    [ 'feature_id' => (int) $existing, 'language_code' => $this->lang, 'name' => $wc_taxonomy ]
                );
            }
            return (int) $existing;
        }

        // Create new feature.
        $wpdb->insert( "{$p}product_features", [
            'parent_id'    => 0,
            'type'         => 'S',  // Select dropdown.
            'filter_type'  => 'checkbox',
            'purpose'      => 'variation_split',
            'feature_code' => $feature_code,
            'position'     => 0,
            'filterable'   => 1,
            'status'       => 'active',
        ] );
        $fid = (int) $wpdb->insert_id;
        if ( ! $fid ) return 0;

        // Translation.
        $wpdb->insert( "{$p}product_feature_translations", [
            'feature_id'    => $fid,
            'language_code' => $this->lang,
            'name'          => $label,
        ] );

        return $fid;
    }

    /**
     * Find or create a TC feature variant for a WC attribute term.
     * Returns the variant ID.
     */
    private function find_or_create_feature_variant( int $feature_id, string $name, string $slug ): int {
        global $wpdb;
        $p = $this->db->prefix;

        // Check existing by name match for this feature.
        $existing = $wpdb->get_var( $wpdb->prepare(
            "SELECT fv.id
             FROM {$p}product_feature_variants fv
             INNER JOIN {$p}product_feature_variant_translations fvt
                ON fvt.variant_id = fv.id AND fvt.language_code = %s
             WHERE fv.feature_id = %d AND fvt.name = %s AND fv.status = 'active'",
            $this->lang, $feature_id, $name
        ) );
        if ( $existing ) return (int) $existing;

        // Create new variant.
        $position = (int) $wpdb->get_var( $wpdb->prepare(
            "SELECT MAX(position) FROM {$p}product_feature_variants WHERE feature_id = %d",
            $feature_id
        ) );

        $wpdb->insert( "{$p}product_feature_variants", [
            'feature_id' => $feature_id,
            'position'   => $position + 1,
            'status'     => 'active',
        ] );
        $vid = (int) $wpdb->insert_id;
        if ( ! $vid ) return 0;

        // Translation.
        $wpdb->insert( "{$p}product_feature_variant_translations", [
            'variant_id'    => $vid,
            'language_code' => $this->lang,
            'name'          => $name,
        ] );

        return $vid;
    }

    /**
     * Link a product to a product group (replicates ImportService::linkProductGroup logic).
     */
    private function link_product_group( int $product_id, string $group_code, bool $is_default = false, string $display_name = '' ): void {
        global $wpdb;
        $p = $this->db->prefix;

        $t_groups   = "{$p}product_groups";
        $t_gp       = "{$p}product_group_products";
        $t_features = "{$p}product_features";
        $t_fv       = "{$p}product_feature_values";

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

        if ( $group ) {
            $group_id      = (int) $group->id;
            // S469: backfill an EMPTY display_name from the Woo parent title so
            // the single-card grid shows "Hoodie", not the representative
            // member's "Hoodie — Blue / Yes" (core's de-suffix fallback only
            // strips short single-token tags, so multi-axis suffixes leak).
            // Backfill-only — a non-empty display_name is the store owner's and
            // is never overwritten by a re-import (#677 repair, CREATE-parity
            // with the display_as_single never-override rule).
            if ( $display_name !== '' && trim( (string) ( $group->display_name ?? '' ) ) === '' ) {
                $wpdb->update( $t_groups, [ 'display_name' => $display_name ], [ 'id' => $group_id ] );
            }
            $raw_fids      = json_decode( $group->feature_ids ?: '[]', true ) ?: [];
            // Purge stale feature IDs that no longer exist (e.g. 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' => '[]',
                // Woo parity (S468, Sean): a WC variable product is ONE card in
                // the shop grid. Default new imported groups to single-card
                // display; set on CREATE only, so a store owner who unchecks it
                // later is never overridden by a re-import.
                'display_as_single' => 1,
                // S469: the Woo parent's title is the authoritative group name
                // (#677) — without it the grid card falls back to the
                // representative member's suffixed name.
                'display_name'      => $display_name,
                'created_at'  => current_time( 'mysql' ),
            ] );
            $group_id      = (int) $wpdb->insert_id;
            $existing_fids = [];
            if ( ! $group_id ) return;
        }

        // 2. Build combination from this product's variation-purpose feature values.
        $variation_values = $wpdb->get_results( $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
        ) );

        $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.
        $merged = $existing_fids;
        $changed = false;
        foreach ( $found_fids as $fid ) {
            if ( ! in_array( $fid, $merged, true ) ) {
                $merged[] = $fid;
                $changed  = true;
            }
        }
        if ( $changed ) {
            $wpdb->update( $t_groups, [ 'feature_ids' => wp_json_encode( $merged ) ], [ 'id' => $group_id ] );
        }

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

        if ( $existing_member ) {
            $update = [ 'combination' => wp_json_encode( $combination ) ];
            if ( $is_default ) $update['position'] = 0;
            $wpdb->update( $t_gp, $update, [ 'id' => (int) $existing_member->id ] );
        } else {
            $position = $is_default ? 0 : ( (int) $wpdb->get_var( $wpdb->prepare(
                "SELECT MAX(position) FROM {$t_gp} WHERE group_id = %d", $group_id
            ) ) + 1 );

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

    /* ───────────────────────────────────────────────────────────────────
     *  CUSTOMERS
     * ─────────────────────────────────────────────────────────────────── */

    private function import_customers(): void {
        global $wpdb;
        $this->update_progress( [ 'current' => 'customers', 'step' => 0, 'done' => 0 ] );
        $this->log( 'Importing customers...' );

        $p = $this->db->prefix;

        $users = $wpdb->get_results(
            "SELECT DISTINCT u.ID, u.user_email, u.display_name, u.user_registered
             FROM {$wpdb->users} u
             INNER JOIN {$wpdb->usermeta} um ON u.ID = um.user_id
             WHERE um.meta_key = '{$wpdb->prefix}capabilities'
               AND um.meta_value LIKE '%\"customer\"%'
             ORDER BY u.ID ASC"
        );

        $this->update_progress( [ 'total' => count( $users ) ] );
        $done = 0;

        foreach ( $users as $u ) {
            // Check if customer already exists by wp_user_id.
            $exists = $wpdb->get_var( $wpdb->prepare(
                "SELECT id FROM {$p}customers WHERE wp_user_id = %d AND deleted_at IS NULL", $u->ID
            ) );

            $tc_customer_id = (int) $exists;

            if ( ! $exists ) {
                $first = get_user_meta( $u->ID, 'billing_first_name', true ) ?: get_user_meta( $u->ID, 'first_name', true );
                $last  = get_user_meta( $u->ID, 'billing_last_name',  true ) ?: get_user_meta( $u->ID, 'last_name',  true );
                $phone = get_user_meta( $u->ID, 'billing_phone', true );

                $wpdb->insert( "{$p}customers", [
                    'wp_user_id'  => $u->ID,
                    'email'       => $u->user_email,
                    'first_name'  => $first ?: '',
                    'last_name'   => $last  ?: '',
                    'phone'       => $phone ?: null,
                    'created_at'  => $u->user_registered,
                ] );
                $tc_customer_id = (int) $wpdb->insert_id;
            }

            // Import saved addresses.
            if ( $tc_customer_id ) {
                $this->import_customer_addresses( $u->ID, $tc_customer_id );
            }

            $done++;
            $this->update_progress( [ 'done' => $done ] );
        }

        $this->log( "Customers: {$done} imported." );
    }

    /**
     * Import WooCommerce saved billing/shipping addresses for a customer.
     */
    private function import_customer_addresses( int $wp_user_id, int $tc_customer_id ): void {
        global $wpdb;
        $p = $this->db->prefix;

        foreach ( [ 'billing', 'shipping' ] as $type ) {
            $first = get_user_meta( $wp_user_id, "{$type}_first_name", true );
            $last  = get_user_meta( $wp_user_id, "{$type}_last_name",  true );
            if ( ! $first && ! $last ) continue;

            // Check existing.
            $exists = $wpdb->get_var( $wpdb->prepare(
                "SELECT id FROM {$p}customer_addresses WHERE customer_id = %d AND type = %s",
                $tc_customer_id, $type
            ) );
            if ( $exists ) continue;

            $wpdb->insert( "{$p}customer_addresses", [
                'customer_id' => $tc_customer_id,
                'type'        => $type,
                'is_default'  => 1,
                'first_name'  => $first,
                'last_name'   => $last,
                'company'     => get_user_meta( $wp_user_id, "{$type}_company",   true ) ?: null,
                'address_1'   => get_user_meta( $wp_user_id, "{$type}_address_1", true ),
                'address_2'   => get_user_meta( $wp_user_id, "{$type}_address_2", true ) ?: null,
                'city'        => get_user_meta( $wp_user_id, "{$type}_city",      true ),
                'state'       => get_user_meta( $wp_user_id, "{$type}_state",     true ) ?: null,
                'postcode'    => get_user_meta( $wp_user_id, "{$type}_postcode",  true ),
                'country'     => get_user_meta( $wp_user_id, "{$type}_country",   true ) ?: '',
                'phone'       => get_user_meta( $wp_user_id, "{$type}_phone",     true ) ?: null,
            ] );
        }
    }

    /* ───────────────────────────────────────────────────────────────────
     *  ORDERS
     * ─────────────────────────────────────────────────────────────────── */

    private function import_orders( bool $hpos, array $product_map, array $status_map ): void {
        global $wpdb;
        $this->update_progress( [ 'current' => 'orders', 'step' => 0, 'done' => 0 ] );
        $this->log( 'Importing orders...' );

        $p = $this->db->prefix;

        if ( $hpos ) {
            $total = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->prefix}wc_orders WHERE type = 'shop_order'" );
        } else {
            $total = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_type = 'shop_order' AND post_status != 'trash'" );
        }

        $this->update_progress( [ 'total' => $total ] );
        $offset = 0;
        $done   = 0;

        while ( $offset < $total ) {
            if ( $hpos ) {
                $orders = $wpdb->get_results( $wpdb->prepare(
                    "SELECT id, status, date_created_gmt, total_amount, customer_id
                     FROM {$wpdb->prefix}wc_orders
                     WHERE type = 'shop_order'
                     ORDER BY id ASC LIMIT %d OFFSET %d",
                    self::BATCH, $offset
                ) );
                foreach ( $orders as $o ) {
                    $this->import_single_order_hpos( $o, $product_map, $status_map );
                    $done++;
                    $this->update_progress( [ 'done' => $done ] );
                }
            } else {
                $orders = $wpdb->get_results( $wpdb->prepare(
                    "SELECT ID, post_status, post_date
                     FROM {$wpdb->posts}
                     WHERE post_type = 'shop_order' AND post_status != 'trash'
                     ORDER BY ID ASC LIMIT %d OFFSET %d",
                    self::BATCH, $offset
                ) );
                foreach ( $orders as $o ) {
                    $this->import_single_order_legacy( $o, $product_map, $status_map );
                    $done++;
                    $this->update_progress( [ 'done' => $done ] );
                }
            }

            $offset += self::BATCH;
        }

        $this->log( "Orders: {$done} imported." );
    }

    /**
     * Import a single order from legacy (wp_posts + wp_postmeta) storage.
     */
    private function import_single_order_legacy( object $wc, array $product_map, array $status_map ): void {
        global $wpdb;
        $p = $this->db->prefix;

        // Check existing by order_number.
        $order_num = (string) $wc->ID;
        $existing  = $wpdb->get_var( $wpdb->prepare(
            "SELECT id FROM {$p}orders WHERE order_number = %s AND deleted_at IS NULL", $order_num
        ) );
        if ( $existing ) return;

        // Bulk-fetch all postmeta.
        $raw = $wpdb->get_results( $wpdb->prepare(
            "SELECT meta_key, meta_value FROM {$wpdb->postmeta} WHERE post_id = %d", $wc->ID
        ) );
        $meta = [];
        foreach ( $raw as $m ) $meta[ $m->meta_key ] = $m->meta_value;

        // Map WC status (wc-processing → processing → TC status_id).
        $wc_status  = str_replace( 'wc-', '', $wc->post_status );
        $tc_status  = $status_map[ $wc_status ] ?? ( $status_map['processing'] ?? 1 );

        $wpdb->insert( "{$p}orders", [
            'order_number'        => $order_num,
            'customer_id'         => ( $meta['_customer_user'] ?? 0 ) ?: null,
            'status_id'           => $tc_status,
            'currency'            => $meta['_order_currency'] ?? 'USD',
            'subtotal'            => (float) ( $meta['_cart_total'] ?? ( $meta['_order_total'] ?? 0 ) ),
            'discount'            => (float) ( $meta['_cart_discount'] ?? 0 ),
            'shipping_cost'       => (float) ( $meta['_order_shipping'] ?? 0 ),
            'tax_total'           => (float) ( $meta['_order_tax'] ?? 0 ),
            'total'               => (float) ( $meta['_order_total'] ?? 0 ),
            'billing_first_name'  => $meta['_billing_first_name']  ?? '',
            'billing_last_name'   => $meta['_billing_last_name']   ?? '',
            'billing_company'     => $meta['_billing_company']     ?? null,
            'billing_address_1'   => $meta['_billing_address_1']   ?? '',
            'billing_address_2'   => $meta['_billing_address_2']   ?? null,
            'billing_city'        => $meta['_billing_city']        ?? '',
            'billing_state'       => $meta['_billing_state']       ?? null,
            'billing_postcode'    => $meta['_billing_postcode']    ?? '',
            'billing_country'     => $meta['_billing_country']     ?? '',
            'billing_email'       => $meta['_billing_email']       ?? '',
            'billing_phone'       => $meta['_billing_phone']       ?? null,
            'shipping_first_name' => $meta['_shipping_first_name'] ?? '',
            'shipping_last_name'  => $meta['_shipping_last_name']  ?? '',
            'shipping_company'    => $meta['_shipping_company']    ?? null,
            'shipping_address_1'  => $meta['_shipping_address_1']  ?? '',
            'shipping_address_2'  => $meta['_shipping_address_2']  ?? null,
            'shipping_city'       => $meta['_shipping_city']       ?? '',
            'shipping_state'      => $meta['_shipping_state']      ?? null,
            'shipping_postcode'   => $meta['_shipping_postcode']   ?? '',
            'shipping_country'    => $meta['_shipping_country']    ?? '',
            'customer_note'       => $wc->post_excerpt ?? null,
            'ip_address'          => $meta['_customer_ip_address'] ?? null,
            'user_agent'          => $meta['_customer_user_agent'] ?? null,
            'created_at'          => $wc->post_date,
            'updated_at'          => current_time( 'mysql' ),
        ] );

        $tc_order_id = (int) $wpdb->insert_id;
        if ( ! $tc_order_id ) return;

        // Order items from WC order_items table.
        $this->import_order_items( $wc->ID, $tc_order_id, $product_map );
    }

    /**
     * Import a single order from HPOS storage.
     */
    private function import_single_order_hpos( object $wc, array $product_map, array $status_map ): void {
        global $wpdb;
        $p = $this->db->prefix;

        $order_num = (string) $wc->id;
        $existing  = $wpdb->get_var( $wpdb->prepare(
            "SELECT id FROM {$p}orders WHERE order_number = %s AND deleted_at IS NULL", $order_num
        ) );
        if ( $existing ) return;

        // Get addresses from wc_order_addresses.
        $addrs_table = $wpdb->prefix . 'wc_order_addresses';
        $billing = $wpdb->get_row( $wpdb->prepare(
            "SELECT * FROM {$addrs_table} WHERE order_id = %d AND address_type = 'billing'", $wc->id
        ) );
        $shipping = $wpdb->get_row( $wpdb->prepare(
            "SELECT * FROM {$addrs_table} WHERE order_id = %d AND address_type = 'shipping'", $wc->id
        ) );

        // Get operational data.
        $op_table = $wpdb->prefix . 'wc_order_operational_data';
        $op = $wpdb->get_row( $wpdb->prepare(
            "SELECT * FROM {$op_table} WHERE order_id = %d", $wc->id
        ) );

        // Meta for extra fields.
        $meta_table = $wpdb->prefix . 'wc_orders_meta';
        $raw_meta = $wpdb->get_results( $wpdb->prepare(
            "SELECT meta_key, meta_value FROM {$meta_table} WHERE order_id = %d", $wc->id
        ) );
        $meta = [];
        foreach ( $raw_meta as $m ) $meta[ $m->meta_key ] = $m->meta_value;

        $wc_status = str_replace( 'wc-', '', $wc->status );
        $tc_status = $status_map[ $wc_status ] ?? ( $status_map['processing'] ?? 1 );

        $wpdb->insert( "{$p}orders", [
            'order_number'        => $order_num,
            'customer_id'         => $wc->customer_id ?: null,
            'status_id'           => $tc_status,
            'currency'            => $meta['_order_currency'] ?? 'USD',
            'subtotal'            => (float) ( $meta['_cart_total'] ?? $wc->total_amount ),
            'discount'            => (float) ( $meta['_cart_discount'] ?? 0 ),
            'shipping_cost'       => (float) ( $meta['_order_shipping'] ?? 0 ),
            'tax_total'           => (float) ( $meta['_order_tax'] ?? 0 ),
            'total'               => (float) $wc->total_amount,
            'billing_first_name'  => $billing->first_name  ?? '',
            'billing_last_name'   => $billing->last_name   ?? '',
            'billing_company'     => $billing->company     ?? null,
            'billing_address_1'   => $billing->address_1   ?? '',
            'billing_address_2'   => $billing->address_2   ?? null,
            'billing_city'        => $billing->city        ?? '',
            'billing_state'       => $billing->state       ?? null,
            'billing_postcode'    => $billing->postcode    ?? '',
            'billing_country'     => $billing->country     ?? '',
            'billing_email'       => $billing->email       ?? '',
            'billing_phone'       => $billing->phone       ?? null,
            'shipping_first_name' => $shipping->first_name ?? '',
            'shipping_last_name'  => $shipping->last_name  ?? '',
            'shipping_company'    => $shipping->company    ?? null,
            'shipping_address_1'  => $shipping->address_1  ?? '',
            'shipping_address_2'  => $shipping->address_2  ?? null,
            'shipping_city'       => $shipping->city       ?? '',
            'shipping_state'      => $shipping->state      ?? null,
            'shipping_postcode'   => $shipping->postcode   ?? '',
            'shipping_country'    => $shipping->country    ?? '',
            'customer_note'       => $meta['_customer_note'] ?? null,
            'ip_address'          => $meta['_customer_ip_address'] ?? null,
            'user_agent'          => $meta['_customer_user_agent'] ?? null,
            'created_at'          => $wc->date_created_gmt,
            'updated_at'          => current_time( 'mysql' ),
        ] );

        $tc_order_id = (int) $wpdb->insert_id;
        if ( ! $tc_order_id ) return;

        $this->import_order_items( $wc->id, $tc_order_id, $product_map );
    }

    /**
     * Import WC order line items.
     */
    private function import_order_items( int $wc_order_id, int $tc_order_id, array $product_map ): void {
        global $wpdb;
        $p = $this->db->prefix;

        $wc_items_table = $wpdb->prefix . 'woocommerce_order_items';
        $wc_meta_table  = $wpdb->prefix . 'woocommerce_order_itemmeta';

        $items = $wpdb->get_results( $wpdb->prepare(
            "SELECT order_item_id, order_item_name
             FROM {$wc_items_table}
             WHERE order_id = %d AND order_item_type = 'line_item'",
            $wc_order_id
        ) );

        foreach ( $items as $item ) {
            // Get item meta.
            $im_raw = $wpdb->get_results( $wpdb->prepare(
                "SELECT meta_key, meta_value FROM {$wc_meta_table} WHERE order_item_id = %d",
                $item->order_item_id
            ) );
            $im = [];
            foreach ( $im_raw as $m ) $im[ $m->meta_key ] = $m->meta_value;

            $wc_product_id = (int) ( $im['_product_id'] ?? 0 );
            $tc_product_id = $product_map[ $wc_product_id ] ?? 0;
            $qty           = (int) ( $im['_qty'] ?? 1 );
            $line_total    = (float) ( $im['_line_total'] ?? 0 );
            $line_tax      = (float) ( $im['_line_tax'] ?? 0 );
            $unit_price    = $qty > 0 ? round( $line_total / $qty, 4 ) : 0;

            $wpdb->insert( "{$p}order_items", [
                'order_id'   => $tc_order_id,
                'product_id' => $tc_product_id ?: 0,
                'sku'        => $im['_sku'] ?? '',
                'name'       => $item->order_item_name,
                'quantity'   => $qty,
                'unit_price' => $unit_price,
                'subtotal'   => $line_total,
                'discount'   => (float) ( $im['_line_subtotal'] ?? $line_total ) - $line_total,
                'tax'        => $line_tax,
                'total'      => $line_total + $line_tax,
            ] );
        }
    }

    /**
     * Build a map of WC status slugs → TC order_status IDs.
     */
    private function build_order_status_map(): array {
        global $wpdb;
        $p = $this->db->prefix;

        // TC statuses.
        $rows = $wpdb->get_results( "SELECT id, handle FROM {$p}order_statuses WHERE type = 'order'" );
        $tc_map = [];
        foreach ( $rows as $r ) $tc_map[ $r->handle ] = (int) $r->id;

        // WC status → TC handle mapping.
        $wc_to_tc = [
            'pending'    => 'pending',
            'processing' => 'processing',
            'on-hold'    => 'on_hold',
            'completed'  => 'complete',
            'cancelled'  => 'cancelled',
            'refunded'   => 'refunded',
            'failed'     => 'failed',
        ];

        $result = [];
        foreach ( $wc_to_tc as $wc => $tc_handle ) {
            $result[ $wc ] = $tc_map[ $tc_handle ] ?? ( $tc_map['processing'] ?? 1 );
        }

        return $result;
    }

    /* ───────────────────────────────────────────────────────────────────
     *  REVIEWS
     * ─────────────────────────────────────────────────────────────────── */

    private function import_reviews( array $product_map ): void {
        global $wpdb;
        $this->update_progress( [ 'current' => 'reviews', 'step' => 0, 'done' => 0 ] );
        $this->log( 'Importing reviews...' );

        $p = $this->db->prefix;

        $rows = $wpdb->get_results(
            "SELECT c.comment_ID, c.comment_post_ID, c.user_id, c.comment_author,
                    c.comment_author_email, c.comment_content, c.comment_date, c.comment_approved,
                    cm.meta_value AS rating
             FROM {$wpdb->comments} c
             LEFT JOIN {$wpdb->commentmeta} cm ON c.comment_ID = cm.comment_id AND cm.meta_key = 'rating'
             WHERE c.comment_type = 'review'
               AND c.comment_approved IN ('1','0')
             ORDER BY c.comment_ID ASC"
        );

        $this->update_progress( [ 'total' => count( $rows ) ] );
        $done = 0;

        foreach ( $rows as $r ) {
            $tc_product = $product_map[ $r->comment_post_ID ] ?? null;
            if ( ! $tc_product ) {
                $done++;
                $this->update_progress( [ 'done' => $done ] );
                continue;
            }

            // Resolve TC customer_id from WP user_id.
            $tc_customer = null;
            if ( $r->user_id ) {
                $tc_customer = $wpdb->get_var( $wpdb->prepare(
                    "SELECT id FROM {$p}customers WHERE wp_user_id = %d AND deleted_at IS NULL", $r->user_id
                ) );
            }

            // Check existing by product + customer + date.
            // Two explicit branches: a concatenated-clause + array_filter() combo can
            // desync placeholder/arg counts on falsy edge values (PHP 8 ValueError)
            // and is unverifiable for Plugin Check (ReplacementsWrongNumber, S441).
            if ( $tc_customer ) {
                $exists = $wpdb->get_var( $wpdb->prepare(
                    "SELECT id FROM {$p}reviews WHERE product_id = %d AND created_at = %s AND customer_id = %d",
                    $tc_product, $r->comment_date, $tc_customer
                ) );
            } else {
                $exists = $wpdb->get_var( $wpdb->prepare(
                    "SELECT id FROM {$p}reviews WHERE product_id = %d AND created_at = %s AND customer_id IS NULL",
                    $tc_product, $r->comment_date
                ) );
            }
            if ( $exists ) { $done++; $this->update_progress( [ 'done' => $done ] ); continue; }

            $wpdb->insert( "{$p}reviews", [
                'product_id'  => $tc_product,
                'customer_id' => $tc_customer ?: null,
                'rating'      => max( 1, min( 5, (int) ( $r->rating ?: 5 ) ) ),
                'title'       => null,
                'body'        => $r->comment_content,
                'status'      => $r->comment_approved === '1' ? 'approved' : 'pending',
                'created_at'  => $r->comment_date,
            ] );

            $done++;
            $this->update_progress( [ 'done' => $done ] );
        }

        $this->log( "Reviews: {$done} imported." );
    }

    /* ───────────────────────────────────────────────────────────────────
     *  COUPONS
     * ─────────────────────────────────────────────────────────────────── */

    private function import_coupons(): void {
        global $wpdb;
        $this->update_progress( [ 'current' => 'coupons', 'step' => 0, 'done' => 0 ] );
        $this->log( 'Importing coupons...' );

        $p = $this->db->prefix;

        $rows = $wpdb->get_results(
            "SELECT ID, post_title, post_excerpt, post_date
             FROM {$wpdb->posts}
             WHERE post_type = 'shop_coupon' AND post_status = 'publish'
             ORDER BY ID ASC"
        );

        $this->update_progress( [ 'total' => count( $rows ) ] );
        $done = 0;

        foreach ( $rows as $c ) {
            $code = strtoupper( $c->post_title );

            // Check existing coupon code.
            $exists = $wpdb->get_var( $wpdb->prepare(
                "SELECT id FROM {$p}coupon_codes WHERE code = %s", $code
            ) );
            if ( $exists ) { $done++; $this->update_progress( [ 'done' => $done ] ); continue; }

            // Get coupon meta.
            $raw = $wpdb->get_results( $wpdb->prepare(
                "SELECT meta_key, meta_value FROM {$wpdb->postmeta} WHERE post_id = %d", $c->ID
            ) );
            $meta = [];
            foreach ( $raw as $m ) $meta[ $m->meta_key ] = $m->meta_value;

            $discount_type = $meta['discount_type'] ?? 'fixed_cart';
            $amount        = (float) ( $meta['coupon_amount'] ?? 0 );
            $free_shipping = ( $meta['free_shipping'] ?? 'no' ) === 'yes';
            $min_amount    = (float) ( $meta['minimum_amount'] ?? 0 );
            $usage_limit   = (int) ( $meta['usage_limit'] ?? 0 );
            $usage_count   = (int) ( $meta['usage_count'] ?? 0 );
            $expiry        = $meta['date_expires'] ?? null;
            if ( $expiry && is_numeric( $expiry ) ) $expiry = gmdate( 'Y-m-d H:i:s', (int) $expiry );

            // Create promotion.
            $wpdb->insert( "{$p}promotions", [
                'type'         => 'cart',
                'priority'     => 0,
                'uses_coupons' => 1,
                'usage_limit'  => $usage_limit ?: null,
                'usage_count'  => $usage_count,
                'valid_from'   => $c->post_date,
                'valid_to'     => $expiry,
                'status'       => 'active',
                'created_at'   => $c->post_date,
            ] );

            $promo_id = (int) $wpdb->insert_id;
            if ( ! $promo_id ) { $done++; $this->update_progress( [ 'done' => $done ] ); continue; }

            // Promotion translation.
            $wpdb->insert( "{$p}promotion_translations", [
                'promotion_id'  => $promo_id,
                'language_code' => $this->lang,
                'name'          => "Coupon: {$code}",
                'description'   => $c->post_excerpt ?: null,
            ] );

            // Conditions (separate table rows).
            if ( $min_amount > 0 ) {
                $wpdb->insert( "{$p}promotion_conditions", [
                    'promotion_id' => $promo_id,
                    'type'         => 'subtotal_gte',
                    'operator'     => 'gte',
                    'value'        => wp_json_encode( $min_amount ),
                    'position'     => 0,
                ] );
            }

            // Bonuses (separate table rows).
            if ( $free_shipping ) {
                $wpdb->insert( "{$p}promotion_bonuses", [
                    'promotion_id' => $promo_id,
                    'type'         => 'free_shipping',
                    'value'        => wp_json_encode( true ),
                    'position'     => 0,
                ] );
            }
            if ( $amount > 0 ) {
                $bonus_type = ( $discount_type === 'percent' ) ? 'order_discount_percent' : 'order_discount_fixed';
                $wpdb->insert( "{$p}promotion_bonuses", [
                    'promotion_id' => $promo_id,
                    'type'         => $bonus_type,
                    'value'        => wp_json_encode( $amount ),
                    'position'     => 1,
                ] );
            }

            // Create coupon code linked to promotion.
            $wpdb->insert( "{$p}coupon_codes", [
                'promotion_id' => $promo_id,
                'code'         => $code,
                'usage_limit'  => $usage_limit ?: null,
                'usage_count'  => $usage_count,
                'expires_at'   => $expiry,
                'status'       => 'active',
            ] );

            $done++;
            $this->update_progress( [ 'done' => $done ] );
        }

        $this->log( "Coupons: {$done} imported." );
    }
}
