<?php
/**
 * GGM Commerce JSON Importer
 *
 * Comprehensive JSON import for full backup/migration restore
 * Imports all product-related entities with complete metadata
 *
 * @package GGM_Commerce
 * @since 1.0.0
 */

// Prevent direct access
if (!defined('ABSPATH')) {
    exit;
}

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery
// phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter

class GGM_Commerce_JSON_Importer {

    /**
     * Supported format versions
     */
    const SUPPORTED_VERSIONS = ['2.0'];

    /**
     * Import options
     * @var array
     */
    private $options = [];

    /**
     * Lookup maps for imported entities (name => id)
     * @var array
     */
    private $lookup_maps = [
        'tax_rates' => [],
        'attribute_groups' => [],
        'attributes' => [],
        'variation_attribute_groups' => [],
        'variation_attributes' => [],
        'categories' => [],
        'tags' => [],
        'products' => [], // sku => id
    ];

    /**
     * Import statistics
     * @var array
     */
    private $stats = [
        'tax_rates' => ['created' => 0, 'updated' => 0, 'skipped' => 0],
        'attribute_groups' => ['created' => 0, 'updated' => 0, 'skipped' => 0],
        'attributes' => ['created' => 0, 'updated' => 0, 'skipped' => 0],
        'variation_attribute_groups' => ['created' => 0, 'updated' => 0, 'skipped' => 0],
        'variation_attributes' => ['created' => 0, 'updated' => 0, 'skipped' => 0],
        'categories' => ['created' => 0, 'updated' => 0, 'skipped' => 0],
        'tags' => ['created' => 0, 'updated' => 0, 'skipped' => 0],
        'products' => ['created' => 0, 'updated' => 0, 'skipped' => 0],
        'variations' => ['created' => 0, 'updated' => 0, 'skipped' => 0],
    ];

    /**
     * Warnings and errors
     * @var array
     */
    private $warnings = [];
    private $errors = [];

    /**
     * Placeholder products created for kit components
     * @var array
     */
    private $placeholders = [];

    /**
     * Kit components to resolve after all products imported
     * @var array
     */
    private $pending_kit_components = [];

    /**
     * Constructor
     */
    public function __construct() {
        $this->options = [
            'duplicate_handling' => 'update', // skip, update, create
            'download_images' => true,
            'create_missing_entities' => true,
            'batch_size' => 50,
        ];
    }

    /**
     * Set import options
     *
     * @param array $options Options to set
     */
    public function set_options($options) {
        $this->options = wp_parse_args($options, $this->options);
    }

    /**
     * Parse and validate JSON file
     *
     * @param string $file_path Path to JSON file
     * @return array|WP_Error Parsed data or error
     */
    public function parse_file($file_path) {
        if (!file_exists($file_path)) {
            return new WP_Error('file_not_found', __('Import file not found', 'ggm-commerce'));
        }

        $content = file_get_contents($file_path);
        if ($content === false) {
            return new WP_Error('file_read_error', __('Failed to read import file', 'ggm-commerce'));
        }

        $data = json_decode($content, true);
        if (json_last_error() !== JSON_ERROR_NONE) {
            return new WP_Error('json_parse_error', sprintf(
                /* translators: %s: JSON error message */
                __('Invalid JSON: %s', 'ggm-commerce'),
                json_last_error_msg()
            ));
        }

        // Validate structure
        $validation = $this->validate_structure($data);
        if (is_wp_error($validation)) {
            return $validation;
        }

        return $data;
    }

    /**
     * Validate JSON structure
     *
     * @param array $data Parsed JSON data
     * @return true|WP_Error True on success or error
     */
    private function validate_structure($data) {
        if (!isset($data['meta'])) {
            return new WP_Error('missing_meta', __('Import file missing metadata section', 'ggm-commerce'));
        }

        if (!isset($data['meta']['format_version'])) {
            return new WP_Error('missing_version', __('Import file missing format version', 'ggm-commerce'));
        }

        if (!in_array($data['meta']['format_version'], self::SUPPORTED_VERSIONS)) {
            return new WP_Error('unsupported_version', sprintf(
                /* translators: 1: format version, 2: list of supported versions */
                __('Unsupported format version: %1$s. Supported versions: %2$s', 'ggm-commerce'),
                $data['meta']['format_version'],
                implode(', ', self::SUPPORTED_VERSIONS)
            ));
        }

        return true;
    }

    /**
     * Get import summary from parsed data
     *
     * @param array $data Parsed JSON data
     * @return array Summary information
     */
    public function get_summary($data) {
        return [
            'format_version' => $data['meta']['format_version'] ?? 'unknown',
            'exported_at' => $data['meta']['exported_at'] ?? 'unknown',
            'source_site' => $data['meta']['site_url'] ?? 'unknown',
            'counts' => $data['meta']['counts'] ?? [],
            'images_included' => $data['meta']['images_included'] ?? false,
        ];
    }

    /**
     * Run full import
     *
     * @param array $data Parsed JSON data
     * @return array Import results
     */
    public function import($data) {
        global $wpdb;

        // Reset state
        $this->reset_state();

        // Start transaction (if supported)
        $wpdb->query('START TRANSACTION');

        try {
            // Phase 1: Import supporting entities (in dependency order)
            $this->import_tax_rates($data['tax_rates'] ?? []);
            $this->import_attribute_groups($data['attribute_groups'] ?? []);
            $this->import_attributes($data['attributes'] ?? []);
            $this->import_variation_attribute_groups($data['variation_attribute_groups'] ?? []);
            $this->import_variation_attributes($data['variation_attributes'] ?? []);
            $this->import_categories($data['categories'] ?? []);
            $this->import_tags($data['tags'] ?? []);

            // Phase 2: Import products
            $this->import_products($data['products'] ?? []);

            // Phase 3: Resolve kit component placeholders
            $this->resolve_kit_placeholders();

            // Commit transaction
            $wpdb->query('COMMIT');

        } catch (Exception $e) {
            $wpdb->query('ROLLBACK');
            $this->errors[] = [
                'type' => 'exception',
                'message' => $e->getMessage(),
            ];
        }

        return $this->get_results();
    }

    /**
     * Reset importer state
     */
    private function reset_state() {
        $this->lookup_maps = [
            'tax_rates' => [],
            'attribute_groups' => [],
            'attributes' => [],
            'variation_attribute_groups' => [],
            'variation_attributes' => [],
            'categories' => [],
            'tags' => [],
            'products' => [],
        ];

        $this->stats = [
            'tax_rates' => ['created' => 0, 'updated' => 0, 'skipped' => 0],
            'attribute_groups' => ['created' => 0, 'updated' => 0, 'skipped' => 0],
            'attributes' => ['created' => 0, 'updated' => 0, 'skipped' => 0],
            'variation_attribute_groups' => ['created' => 0, 'updated' => 0, 'skipped' => 0],
            'variation_attributes' => ['created' => 0, 'updated' => 0, 'skipped' => 0],
            'categories' => ['created' => 0, 'updated' => 0, 'skipped' => 0],
            'tags' => ['created' => 0, 'updated' => 0, 'skipped' => 0],
            'products' => ['created' => 0, 'updated' => 0, 'skipped' => 0],
            'variations' => ['created' => 0, 'updated' => 0, 'skipped' => 0],
        ];

        $this->warnings = [];
        $this->errors = [];
        $this->placeholders = [];
        $this->pending_kit_components = [];

        // Pre-populate lookup maps with existing entities
        $this->preload_lookup_maps();
    }

    /**
     * Preload lookup maps with existing entities
     */
    private function preload_lookup_maps() {
        global $wpdb;

        // Tax rates
        $rates = $wpdb->get_results(
            "SELECT id, name FROM {$wpdb->prefix}ggmc_tax_rates WHERE status = 'active'",
            ARRAY_A
        );
        foreach ($rates ?: [] as $row) {
            $this->lookup_maps['tax_rates'][$row['name']] = $row['id'];
        }

        // Attribute groups
        $groups = $wpdb->get_results(
            "SELECT id, name FROM {$wpdb->prefix}ggmc_attribute_groups WHERE status = 'active'",
            ARRAY_A
        );
        foreach ($groups ?: [] as $row) {
            $this->lookup_maps['attribute_groups'][$row['name']] = $row['id'];
        }

        // Attributes
        $attrs = $wpdb->get_results(
            "SELECT id, name FROM {$wpdb->prefix}ggmc_attributes WHERE status = 'active'",
            ARRAY_A
        );
        foreach ($attrs ?: [] as $row) {
            $this->lookup_maps['attributes'][$row['name']] = $row['id'];
        }

        // Variation type groups
        $var_groups = $wpdb->get_results(
            "SELECT id, name FROM {$wpdb->prefix}ggmc_variation_type_groups WHERE status = 'active'",
            ARRAY_A
        );
        foreach ($var_groups ?: [] as $row) {
            $this->lookup_maps['variation_attribute_groups'][$row['name']] = $row['id'];
        }

        // Variation types
        $var_types = $wpdb->get_results(
            "SELECT id, name FROM {$wpdb->prefix}ggmc_variation_types WHERE status = 'active'",
            ARRAY_A
        );
        foreach ($var_types ?: [] as $row) {
            $this->lookup_maps['variation_attributes'][$row['name']] = $row['id'];
        }

        // Categories
        $cats = $wpdb->get_results(
            "SELECT id, name FROM {$wpdb->prefix}ggmc_categories WHERE status = 'active'",
            ARRAY_A
        );
        foreach ($cats ?: [] as $row) {
            $this->lookup_maps['categories'][$row['name']] = $row['id'];
        }

        // Tags
        $tags = $wpdb->get_results(
            "SELECT id, name FROM {$wpdb->prefix}ggmc_tags WHERE status = 'active'",
            ARRAY_A
        );
        foreach ($tags ?: [] as $row) {
            $this->lookup_maps['tags'][$row['name']] = $row['id'];
        }

        // Products by SKU (exclude trashed and deleted)
        $products = $wpdb->get_results(
            "SELECT id, sku FROM {$wpdb->prefix}ggmc_products WHERE status NOT IN ('deleted', 'trash')",
            ARRAY_A
        );
        foreach ($products ?: [] as $row) {
            if (!empty($row['sku'])) {
                $this->lookup_maps['products'][$row['sku']] = $row['id'];
            }
        }
    }

    /**
     * Import tax rates
     *
     * @param array $tax_rates Tax rates data
     */
    private function import_tax_rates($tax_rates) {
        global $wpdb;
        $table = $wpdb->prefix . 'ggmc_tax_rates';

        foreach ($tax_rates as $rate) {
            $existing_id = $this->lookup_maps['tax_rates'][$rate['name']] ?? null;

            if ($existing_id) {
                if ($this->options['duplicate_handling'] === 'skip') {
                    $this->stats['tax_rates']['skipped']++;
                    continue;
                }

                if ($this->options['duplicate_handling'] === 'update') {
                    $wpdb->update(
                        $table,
                        [
                            'slug' => sanitize_title($rate['name']),
                            'rate' => $rate['rate'],
                            'description' => $rate['description'] ?? '',
                            'is_default' => $rate['is_default'] ? 1 : 0,
                            'display_order' => $rate['display_order'] ?? 0,
                            'updated_at' => current_time('mysql'),
                        ],
                        ['id' => $existing_id],
                        ['%s', '%f', '%s', '%d', '%d', '%s'],
                        ['%d']
                    );
                    $this->stats['tax_rates']['updated']++;
                    continue;
                }
            }

            // Create new
            $wpdb->insert(
                $table,
                [
                    'name' => $rate['name'],
                    'slug' => sanitize_title($rate['name']),
                    'rate' => $rate['rate'],
                    'description' => $rate['description'] ?? '',
                    'is_default' => $rate['is_default'] ? 1 : 0,
                    'display_order' => $rate['display_order'] ?? 0,
                    'status' => 'active',
                    'created_at' => current_time('mysql'),
                    'updated_at' => current_time('mysql'),
                ],
                ['%s', '%s', '%f', '%s', '%d', '%d', '%s', '%s', '%s']
            );

            $this->lookup_maps['tax_rates'][$rate['name']] = $wpdb->insert_id;
            $this->stats['tax_rates']['created']++;
        }
    }

    /**
     * Import attribute groups
     *
     * @param array $groups Attribute groups data
     */
    private function import_attribute_groups($groups) {
        global $wpdb;
        $table = $wpdb->prefix . 'ggmc_attribute_groups';

        foreach ($groups as $group) {
            $existing_id = $this->lookup_maps['attribute_groups'][$group['name']] ?? null;

            if ($existing_id) {
                if ($this->options['duplicate_handling'] === 'skip') {
                    $this->stats['attribute_groups']['skipped']++;
                    continue;
                }

                if ($this->options['duplicate_handling'] === 'update') {
                    $wpdb->update(
                        $table,
                        [
                            'description' => $group['description'] ?? '',
                            'display_order' => $group['display_order'] ?? 0,
                            'updated_at' => current_time('mysql'),
                        ],
                        ['id' => $existing_id],
                        ['%s', '%d', '%s'],
                        ['%d']
                    );
                    $this->stats['attribute_groups']['updated']++;
                    continue;
                }
            }

            // Create new
            $wpdb->insert(
                $table,
                [
                    'name' => $group['name'],
                    'description' => $group['description'] ?? '',
                    'display_order' => $group['display_order'] ?? 0,
                    'status' => 'active',
                    'created_at' => current_time('mysql'),
                    'updated_at' => current_time('mysql'),
                ],
                ['%s', '%s', '%d', '%s', '%s', '%s']
            );

            $this->lookup_maps['attribute_groups'][$group['name']] = $wpdb->insert_id;
            $this->stats['attribute_groups']['created']++;
        }
    }

    /**
     * Import attributes
     *
     * @param array $attributes Attributes data
     */
    private function import_attributes($attributes) {
        global $wpdb;
        $table = $wpdb->prefix . 'ggmc_attributes';

        foreach ($attributes as $attr) {
            $existing_id = $this->lookup_maps['attributes'][$attr['name']] ?? null;
            $group_id = null;

            // Resolve group reference
            if (!empty($attr['group'])) {
                $group_id = $this->lookup_maps['attribute_groups'][$attr['group']] ?? null;
            }

            $options_json = !empty($attr['options']) ? wp_json_encode($attr['options']) : null;

            if ($existing_id) {
                if ($this->options['duplicate_handling'] === 'skip') {
                    $this->stats['attributes']['skipped']++;
                    continue;
                }

                if ($this->options['duplicate_handling'] === 'update') {
                    $wpdb->update(
                        $table,
                        [
                            'group_id' => $group_id,
                            'attribute_type' => $attr['attribute_type'] ?? 'text',
                            'options' => $options_json,
                            'unit' => $attr['unit'] ?? '',
                            'icon' => $attr['icon'] ?? '',
                            'display_order' => $attr['display_order'] ?? 0,
                            'updated_at' => current_time('mysql'),
                        ],
                        ['id' => $existing_id],
                        ['%d', '%s', '%s', '%s', '%s', '%d', '%s'],
                        ['%d']
                    );
                    $this->stats['attributes']['updated']++;
                    continue;
                }
            }

            // Create new
            $wpdb->insert(
                $table,
                [
                    'name' => $attr['name'],
                    'group_id' => $group_id,
                    'attribute_type' => $attr['attribute_type'] ?? 'text',
                    'options' => $options_json,
                    'unit' => $attr['unit'] ?? '',
                    'icon' => $attr['icon'] ?? '',
                    'display_order' => $attr['display_order'] ?? 0,
                    'status' => 'active',
                    'created_at' => current_time('mysql'),
                    'updated_at' => current_time('mysql'),
                ],
                ['%s', '%d', '%s', '%s', '%s', '%s', '%d', '%s', '%s', '%s']
            );

            $this->lookup_maps['attributes'][$attr['name']] = $wpdb->insert_id;
            $this->stats['attributes']['created']++;
        }
    }

    /**
     * Import variation type groups
     *
     * @param array $groups Variation type groups data
     */
    private function import_variation_attribute_groups($groups) {
        global $wpdb;
        $table = $wpdb->prefix . 'ggmc_variation_type_groups';

        foreach ($groups as $group) {
            $existing_id = $this->lookup_maps['variation_attribute_groups'][$group['name']] ?? null;

            if ($existing_id) {
                if ($this->options['duplicate_handling'] === 'skip') {
                    $this->stats['variation_attribute_groups']['skipped']++;
                    continue;
                }

                if ($this->options['duplicate_handling'] === 'update') {
                    $wpdb->update(
                        $table,
                        [
                            'slug' => $group['slug'] ?? sanitize_title($group['name']),
                            'description' => $group['description'] ?? '',
                            'is_suggestion' => ($group['is_suggestion'] ?? false) ? 1 : 0,
                            'display_order' => $group['display_order'] ?? 0,
                            'updated_at' => current_time('mysql'),
                        ],
                        ['id' => $existing_id],
                        ['%s', '%s', '%d', '%d', '%s'],
                        ['%d']
                    );

                    // Update members
                    $this->update_variation_group_members($existing_id, $group['attributes'] ?? []);

                    $this->stats['variation_attribute_groups']['updated']++;
                    continue;
                }
            }

            // Create new
            $wpdb->insert(
                $table,
                [
                    'name' => $group['name'],
                    'slug' => $group['slug'] ?? sanitize_title($group['name']),
                    'description' => $group['description'] ?? '',
                    'is_suggestion' => ($group['is_suggestion'] ?? false) ? 1 : 0,
                    'display_order' => $group['display_order'] ?? 0,
                    'status' => 'active',
                    'created_at' => current_time('mysql'),
                    'updated_at' => current_time('mysql'),
                ],
                ['%s', '%s', '%s', '%d', '%d', '%s', '%s', '%s']
            );

            $group_id = $wpdb->insert_id;
            $this->lookup_maps['variation_attribute_groups'][$group['name']] = $group_id;

            // Add members
            $this->update_variation_group_members($group_id, $group['attributes'] ?? []);

            $this->stats['variation_attribute_groups']['created']++;
        }
    }

    /**
     * Update variation type group members
     *
     * @param int   $group_id   Group ID
     * @param array $type_names Variation type names
     */
    private function update_variation_group_members($group_id, $type_names) {
        global $wpdb;
        $table = $wpdb->prefix . 'ggmc_variation_type_group_members';

        // Remove existing members
        $wpdb->delete($table, ['group_id' => $group_id], ['%d']);

        // Add new members
        $order = 1;
        foreach ($type_names as $type_name) {
            $type_id = $this->lookup_maps['variation_attributes'][$type_name] ?? null;
            if ($type_id) {
                $wpdb->insert(
                    $table,
                    [
                        'group_id'          => $group_id,
                        'variation_type_id' => $type_id,
                        'display_order'     => $order++,
                    ],
                    ['%d', '%d', '%d']
                );
            }
        }
    }

    /**
     * Import variation types
     *
     * @param array $types Variation types data
     */
    private function import_variation_attributes($types) {
        global $wpdb;
        $table = $wpdb->prefix . 'ggmc_variation_types';

        foreach ($types as $type) {
            $existing_id = $this->lookup_maps['variation_attributes'][$type['name']] ?? null;
            $options_json = !empty($type['options']) ? wp_json_encode($type['options']) : null;

            if ($existing_id) {
                if ($this->options['duplicate_handling'] === 'skip') {
                    $this->stats['variation_attributes']['skipped']++;
                    continue;
                }

                if ($this->options['duplicate_handling'] === 'update') {
                    $wpdb->update(
                        $table,
                        [
                            'slug' => $type['slug'] ?? sanitize_title($type['name']),
                            'input_type' => $type['input_type'] ?? $type['attribute_type'] ?? 'select',
                            'options' => $options_json,
                            'display_order' => $type['display_order'] ?? 0,
                            'updated_at' => current_time('mysql'),
                        ],
                        ['id' => $existing_id],
                        ['%s', '%s', '%s', '%d', '%s'],
                        ['%d']
                    );
                    $this->stats['variation_attributes']['updated']++;
                    continue;
                }
            }

            // Create new
            $wpdb->insert(
                $table,
                [
                    'name' => $type['name'],
                    'slug' => $type['slug'] ?? sanitize_title($type['name']),
                    'input_type' => $type['input_type'] ?? $type['attribute_type'] ?? 'select',
                    'options' => $options_json,
                    'display_order' => $type['display_order'] ?? 0,
                    'status' => 'active',
                    'created_at' => current_time('mysql'),
                    'updated_at' => current_time('mysql'),
                ],
                ['%s', '%s', '%s', '%s', '%d', '%s', '%s', '%s']
            );

            $this->lookup_maps['variation_attributes'][$type['name']] = $wpdb->insert_id;
            $this->stats['variation_attributes']['created']++;
        }
    }

    /**
     * Import categories (recursive for hierarchy)
     *
     * @param array $categories Categories data
     * @param int|null $parent_id Parent category ID
     */
    private function import_categories($categories, $parent_id = null) {
        global $wpdb;
        $table = $wpdb->prefix . 'ggmc_categories';

        foreach ($categories as $cat) {
            $existing_id = $this->lookup_maps['categories'][$cat['name']] ?? null;

            // Handle image
            $image_id = null;
            if (!empty($cat['image']) && $this->options['download_images']) {
                $image_id = $this->import_image($cat['image'], 'category-' . sanitize_title($cat['name']));
            }

            if ($existing_id) {
                if ($this->options['duplicate_handling'] === 'skip') {
                    $this->stats['categories']['skipped']++;
                } elseif ($this->options['duplicate_handling'] === 'update') {
                    $update_data = [
                        'slug' => $cat['slug'] ?? sanitize_title($cat['name']),
                        'description' => $cat['description'] ?? '',
                        'parent_id' => $parent_id,
                        'display_order' => $cat['display_order'] ?? 0,
                        'updated_at' => current_time('mysql'),
                    ];

                    if ($image_id) {
                        $update_data['image_id'] = $image_id;
                    }

                    $wpdb->update($table, $update_data, ['id' => $existing_id]);
                    $this->stats['categories']['updated']++;
                }
            } else {
                // Create new
                $wpdb->insert(
                    $table,
                    [
                        'name' => $cat['name'],
                        'slug' => $cat['slug'] ?? sanitize_title($cat['name']),
                        'description' => $cat['description'] ?? '',
                        'parent_id' => $parent_id,
                        'image_id' => $image_id,
                        'display_order' => $cat['display_order'] ?? 0,
                        'status' => 'active',
                        'created_at' => current_time('mysql'),
                        'updated_at' => current_time('mysql'),
                    ],
                    ['%s', '%s', '%s', '%d', '%d', '%d', '%s', '%s', '%s']
                );

                $this->lookup_maps['categories'][$cat['name']] = $wpdb->insert_id;
                $this->stats['categories']['created']++;
                $existing_id = $wpdb->insert_id;
            }

            // Import children recursively
            if (!empty($cat['children'])) {
                $this->import_categories($cat['children'], $existing_id);
            }
        }
    }

    /**
     * Import tags
     *
     * @param array $tags Tags data
     */
    private function import_tags($tags) {
        global $wpdb;
        $table = $wpdb->prefix . 'ggmc_tags';

        foreach ($tags as $tag) {
            $existing_id = $this->lookup_maps['tags'][$tag['name']] ?? null;

            if ($existing_id) {
                if ($this->options['duplicate_handling'] === 'skip') {
                    $this->stats['tags']['skipped']++;
                    continue;
                }

                if ($this->options['duplicate_handling'] === 'update') {
                    $wpdb->update(
                        $table,
                        [
                            'slug' => $tag['slug'] ?? sanitize_title($tag['name']),
                            'description' => $tag['description'] ?? '',
                            'updated_at' => current_time('mysql'),
                        ],
                        ['id' => $existing_id],
                        ['%s', '%s', '%s'],
                        ['%d']
                    );
                    $this->stats['tags']['updated']++;
                    continue;
                }
            }

            // Create new
            $wpdb->insert(
                $table,
                [
                    'name' => $tag['name'],
                    'slug' => $tag['slug'] ?? sanitize_title($tag['name']),
                    'description' => $tag['description'] ?? '',
                    'status' => 'active',
                    'created_at' => current_time('mysql'),
                    'updated_at' => current_time('mysql'),
                ],
                ['%s', '%s', '%s', '%s', '%s', '%s']
            );

            $this->lookup_maps['tags'][$tag['name']] = $wpdb->insert_id;
            $this->stats['tags']['created']++;
        }
    }

    /**
     * Import products
     *
     * @param array $products Products data
     */
    private function import_products($products) {
        foreach ($products as $product) {
            $this->import_single_product($product);
        }
    }

    /**
     * Import a single product
     *
     * @param array $product Product data
     */
    private function import_single_product($product) {
        global $wpdb;
        $table = $wpdb->prefix . 'ggmc_products';

        $sku = $product['sku'] ?? '';
        $existing_id = (!empty($sku) && isset($this->lookup_maps['products'][$sku]))
            ? $this->lookup_maps['products'][$sku]
            : null;

        // Resolve tax rate
        $tax_rate_id = null;
        if (!empty($product['tax_rate'])) {
            $tax_rate_id = $this->lookup_maps['tax_rates'][$product['tax_rate']] ?? null;
        }

        // Handle images — always try to match local URLs, only download external if option enabled
        $image_id = null;
        if (!empty($product['image'])) {
            $image_id = $this->import_image($product['image'], 'product-' . ($sku ?: sanitize_title($product['name'])));
        }

        $gallery_ids = null;
        if (!empty($product['gallery_images'])) {
            $gallery = [];
            $i = 1;
            foreach ($product['gallery_images'] as $img) {
                $gal_id = $this->import_image($img, 'product-' . ($sku ?: sanitize_title($product['name'])) . '-gallery-' . $i++);
                if ($gal_id) {
                    $gallery[] = $gal_id;
                }
            }
            if (!empty($gallery)) {
                $gallery_ids = wp_json_encode($gallery);
            }
        }

        $product_data = [
            'name' => $product['name'],
            'slug' => $product['slug'] ?? sanitize_title($product['name']),
            'short_description' => $product['short_description'] ?? '',
            'description' => $product['description'] ?? '',
            'price' => $product['price'],
            'sale_price' => $product['sale_price'],
            'stock' => $product['stock'],
            'stock_status' => $product['stock_status'] ?? 'instock',
            'manage_stock' => ($product['manage_stock'] ?? false) ? 1 : 0,
            'weight' => $product['weight'],
            'length' => $product['length'],
            'width' => $product['width'],
            'height' => $product['height'],
            'featured' => ($product['featured'] ?? false) ? 1 : 0,
            'status' => $product['status'] ?? 'active',
            'product_type' => $product['product_type'] ?? 'simple',
            'tax_rate_id' => $tax_rate_id,
            'requires_shipping' => ($product['requires_shipping'] ?? true) ? 1 : 0,
            'is_virtual' => ($product['is_virtual'] ?? false) ? 1 : 0,
            'is_downloadable' => ($product['is_downloadable'] ?? false) ? 1 : 0,
            'has_variations' => ($product['has_variations'] ?? false) ? 1 : 0,
            'variation_mode' => $product['variation_mode'] ?? null,
            'variation_stock_mode' => $product['variation_stock_mode'] ?? null,
            'updated_at' => current_time('mysql'),
        ];

        if ($image_id) {
            $product_data['image_id'] = $image_id;
        }
        if ($gallery_ids) {
            $product_data['gallery_ids'] = $gallery_ids;
        }

        if ($existing_id) {
            if ($this->options['duplicate_handling'] === 'skip') {
                $this->stats['products']['skipped']++;
                return;
            }

            if ($this->options['duplicate_handling'] === 'update') {
                $wpdb->update($table, $product_data, ['id' => $existing_id]);
                $this->stats['products']['updated']++;
                $product_id = $existing_id;
            } else {
                // Create duplicate — ensure unique slug to avoid constraint violation
                $product_data['slug'] = GGM_Commerce_Database::generate_unique_slug($product_data['name']);
                $product_data['sku'] = $sku;
                $product_data['created_at'] = current_time('mysql');
                $wpdb->insert($table, $product_data);
                $product_id = $wpdb->insert_id;
                $this->stats['products']['created']++;
            }
        } else {
            // Create new — ensure unique slug to avoid constraint violation
            $product_data['slug'] = GGM_Commerce_Database::generate_unique_slug($product_data['name']);
            $product_data['sku'] = $sku;
            $product_data['created_at'] = current_time('mysql');
            $wpdb->insert($table, $product_data);
            $product_id = $wpdb->insert_id;
            if (!empty($sku)) {
                $this->lookup_maps['products'][$sku] = $product_id;
            }
            $this->stats['products']['created']++;
        }

        // Import relationships
        $this->import_product_categories($product_id, $product['categories'] ?? []);
        $this->import_product_tags($product_id, $product['tags'] ?? []);
        $this->import_product_attributes($product_id, $product['attributes'] ?? []);
        $this->import_product_barcodes($product_id, $product['barcodes'] ?? []);
        $this->import_product_downloads($product_id, $product['downloads'] ?? []);

        // Import variations
        if (!empty($product['variations'])) {
            $this->import_product_variations($product_id, $product);
        }

        // Queue kit components for later resolution
        if (!empty($product['kit_components'])) {
            $this->queue_kit_components($product_id, $product);
        }
    }

    /**
     * Import product categories
     *
     * @param int $product_id Product ID
     * @param array $category_names Category names
     */
    private function import_product_categories($product_id, $category_names) {
        global $wpdb;
        $table = $wpdb->prefix . 'ggmc_product_categories';

        // Remove existing associations
        $wpdb->delete($table, ['product_id' => $product_id], ['%d']);

        foreach ($category_names as $name) {
            $cat_id = $this->lookup_maps['categories'][$name] ?? null;
            if ($cat_id) {
                $wpdb->insert(
                    $table,
                    ['product_id' => $product_id, 'category_id' => $cat_id],
                    ['%d', '%d']
                );
            }
        }
    }

    /**
     * Import product tags
     *
     * @param int $product_id Product ID
     * @param array $tag_names Tag names
     */
    private function import_product_tags($product_id, $tag_names) {
        global $wpdb;
        $table = $wpdb->prefix . 'ggmc_product_tags';

        // Remove existing associations
        $wpdb->delete($table, ['product_id' => $product_id], ['%d']);

        foreach ($tag_names as $name) {
            $tag_id = $this->lookup_maps['tags'][$name] ?? null;
            if ($tag_id) {
                $wpdb->insert(
                    $table,
                    ['product_id' => $product_id, 'tag_id' => $tag_id],
                    ['%d', '%d']
                );
            }
        }
    }

    /**
     * Import product attributes
     *
     * @param int $product_id Product ID
     * @param array $attributes Attribute name => value pairs
     */
    private function import_product_attributes($product_id, $attributes) {
        global $wpdb;
        $table = $wpdb->prefix . 'ggmc_product_attribute_values';

        // Remove existing associations
        $wpdb->delete($table, ['product_id' => $product_id], ['%d']);

        foreach ($attributes as $name => $value) {
            $attr_id = $this->lookup_maps['attributes'][$name] ?? null;
            if ($attr_id) {
                $wpdb->insert(
                    $table,
                    [
                        'product_id' => $product_id,
                        'attribute_id' => $attr_id,
                        'attribute_value' => $value,
                    ],
                    ['%d', '%d', '%s']
                );
            }
        }
    }

    /**
     * Import product barcodes
     *
     * @param int $product_id Product ID
     * @param array $barcodes Barcodes data
     */
    private function import_product_barcodes($product_id, $barcodes) {
        global $wpdb;
        $table = $wpdb->prefix . 'ggmc_product_barcodes';

        // Remove existing barcodes
        $wpdb->delete($table, ['product_id' => $product_id], ['%d']);

        foreach ($barcodes as $barcode) {
            $wpdb->insert(
                $table,
                [
                    'product_id' => $product_id,
                    'barcode_value' => $barcode['value'],
                    'barcode_type' => $barcode['type'] ?? 'EAN-13',
                    'is_primary' => ($barcode['is_primary'] ?? false) ? 1 : 0,
                    'created_at' => current_time('mysql'),
                ],
                ['%d', '%s', '%s', '%d', '%s']
            );
        }
    }

    /**
     * Import product downloads
     *
     * @param int $product_id Product ID
     * @param array $downloads Downloads data
     */
    private function import_product_downloads($product_id, $downloads) {
        global $wpdb;
        $table = $wpdb->prefix . 'ggmc_product_downloads';

        // Remove existing downloads
        $wpdb->delete($table, ['product_id' => $product_id], ['%d']);

        $order = 1;
        foreach ($downloads as $dl) {
            $wpdb->insert(
                $table,
                [
                    'product_id' => $product_id,
                    'name' => $dl['name'],
                    'file_url' => $dl['file_url'],
                    'file_type' => $dl['file_type'] ?? '',
                    'file_size' => $dl['file_size'] ?? 0,
                    'download_limit' => $dl['download_limit'] ?? -1,
                    'access_expiry_days' => $dl['access_expiry_days'] ?? 0,
                    'display_order' => $dl['display_order'] ?? $order++,
                    'status' => 'active',
                    'created_at' => current_time('mysql'),
                    'updated_at' => current_time('mysql'),
                ],
                ['%d', '%s', '%s', '%s', '%d', '%d', '%d', '%d', '%s', '%s', '%s']
            );
        }
    }

    /**
     * Import product variations
     *
     * @param int $product_id Product ID
     * @param array $product Full product data
     */
    private function import_product_variations($product_id, $product) {
        global $wpdb;
        $var_table = $wpdb->prefix . 'ggmc_product_variations';
        $config_table = $wpdb->prefix . 'ggmc_product_variation_types';
        $val_table = $wpdb->prefix . 'ggmc_variation_type_values';

        // Clear existing variation config
        $wpdb->delete($config_table, ['product_id' => $product_id], ['%d']);

        // Set up variation config
        $order = 1;
        foreach ($product['variation_attributes_used'] ?? [] as $type_name) {
            $type_id = $this->lookup_maps['variation_attributes'][$type_name] ?? null;
            if ($type_id) {
                $wpdb->insert(
                    $config_table,
                    [
                        'product_id'        => $product_id,
                        'variation_type_id' => $type_id,
                        'display_order'     => $order++,
                    ],
                    ['%d', '%d', '%d']
                );
            }
        }

        // Import each variation
        foreach ($product['variations'] as $var) {
            // Check if variation exists by SKU
            $existing_var = null;
            if (!empty($var['sku'])) {
                $existing_var = $wpdb->get_row($wpdb->prepare(
                    "SELECT id FROM {$var_table} WHERE product_id = %d AND sku = %s",
                    $product_id,
                    $var['sku']
                ), ARRAY_A);
            }

            // Handle variation image
            $var_image_id = null;
            if (!empty($var['image']) && $this->options['download_images']) {
                $var_image_id = $this->import_image($var['image'], 'variation-' . ($var['sku'] ?? $product_id));
            }

            $var_data = [
                'product_id' => $product_id,
                'sku' => $var['sku'] ?? null,
                'price' => $var['price'],
                'sale_price' => $var['sale_price'],
                'price_type' => $var['price_type'] ?? 'absolute',
                'stock' => $var['stock'],
                'stock_status' => $var['stock_status'] ?? 'instock',
                'manage_stock' => ($var['manage_stock'] ?? false) ? 1 : 0,
                'weight' => $var['weight'],
                'is_default' => ($var['is_default'] ?? false) ? 1 : 0,
                'status' => $var['status'] ?? 'active',
                'updated_at' => current_time('mysql'),
            ];

            if ($var_image_id) {
                $var_data['image_id'] = $var_image_id;
            }

            if ($existing_var) {
                if ($this->options['duplicate_handling'] === 'skip') {
                    $this->stats['variations']['skipped']++;
                    continue;
                }

                if ($this->options['duplicate_handling'] === 'update') {
                    $wpdb->update($var_table, $var_data, ['id' => $existing_var['id']]);
                    $var_id = $existing_var['id'];
                    $this->stats['variations']['updated']++;
                }
            } else {
                $var_data['created_at'] = current_time('mysql');
                $wpdb->insert($var_table, $var_data);
                $var_id = $wpdb->insert_id;
                $this->stats['variations']['created']++;
            }

            // Clear and set variation type values
            $wpdb->delete($val_table, ['variation_id' => $var_id], ['%d']);

            foreach ($var['attributes'] ?? [] as $type_name => $option_value) {
                $type_id = $this->lookup_maps['variation_attributes'][$type_name] ?? null;
                if ($type_id) {
                    $wpdb->insert(
                        $val_table,
                        [
                            'variation_id'      => $var_id,
                            'variation_type_id' => $type_id,
                            'option_value'      => $option_value,
                        ],
                        ['%d', '%d', '%s']
                    );
                }
            }
        }
    }

    /**
     * Queue kit components for later resolution
     *
     * @param int $product_id Kit product ID
     * @param array $product Full product data
     */
    private function queue_kit_components($product_id, $product) {
        global $wpdb;
        $kits_table = $wpdb->prefix . 'ggmc_product_kits';

        // Create or update kit record
        $existing_kit = $wpdb->get_var($wpdb->prepare(
            "SELECT id FROM {$kits_table} WHERE product_id = %d",
            $product_id
        ));

        $kit_config = $product['kit_config'] ?? [];

        if ($existing_kit) {
            $wpdb->update(
                $kits_table,
                [
                    'kit_type' => $kit_config['kit_type'] ?? 'kit',
                    'auto_calculate_price' => ($kit_config['auto_calculate_price'] ?? false) ? 1 : 0,
                    'updated_at' => current_time('mysql'),
                ],
                ['id' => $existing_kit],
                ['%s', '%d', '%s'],
                ['%d']
            );
            $kit_id = $existing_kit;
        } else {
            $wpdb->insert(
                $kits_table,
                [
                    'product_id' => $product_id,
                    'kit_type' => $kit_config['kit_type'] ?? 'kit',
                    'auto_calculate_price' => ($kit_config['auto_calculate_price'] ?? false) ? 1 : 0,
                    'created_at' => current_time('mysql'),
                    'updated_at' => current_time('mysql'),
                ],
                ['%d', '%s', '%d', '%s', '%s']
            );
            $kit_id = $wpdb->insert_id;
        }

        // Queue components for resolution
        $this->pending_kit_components[] = [
            'kit_id' => $kit_id,
            'product_sku' => $product['sku'],
            'components' => $product['kit_components'],
        ];
    }

    /**
     * Resolve kit component placeholders
     */
    private function resolve_kit_placeholders() {
        global $wpdb;
        $components_table = $wpdb->prefix . 'ggmc_kit_components';

        foreach ($this->pending_kit_components as $kit) {
            // Clear existing components
            $wpdb->delete($components_table, ['kit_id' => $kit['kit_id']], ['%d']);

            foreach ($kit['components'] as $component) {
                $component_sku = $component['component_sku'];
                $component_id = $this->lookup_maps['products'][$component_sku] ?? null;

                if (!$component_id) {
                    // Create placeholder product
                    $component_id = $this->create_placeholder_product($component_sku);
                    $this->warnings[] = [
                        'type' => 'kit_component_missing',
                        'kit_sku' => $kit['product_sku'],
                        'component_sku' => $component_sku,
                        'message' => sprintf(
                            /* translators: 1: kit product SKU, 2: component SKU */
                            __('Kit "%1$s" references component SKU "%2$s" which was not found. A placeholder product was created.', 'ggm-commerce'),
                            $kit['product_sku'],
                            $component_sku
                        ),
                    ];
                }

                $wpdb->insert(
                    $components_table,
                    [
                        'kit_id' => $kit['kit_id'],
                        'component_product_id' => $component_id,
                        'quantity' => $component['quantity'] ?? 1,
                        'display_order' => $component['display_order'] ?? 0,
                    ],
                    ['%d', '%d', '%d', '%d']
                );
            }
        }
    }

    /**
     * Create a placeholder product for missing kit components
     *
     * @param string $sku SKU of missing product
     * @return int Product ID
     */
    private function create_placeholder_product($sku) {
        global $wpdb;
        $table = $wpdb->prefix . 'ggmc_products';

        $wpdb->insert(
            $table,
            [
                'sku' => $sku,
                'name' => '[Placeholder] ' . $sku,
                'slug' => sanitize_title('placeholder-' . $sku),
                'price' => 0,
                'status' => 'draft',
                'product_type' => 'simple',
                'created_at' => current_time('mysql'),
                'updated_at' => current_time('mysql'),
            ],
            ['%s', '%s', '%s', '%f', '%s', '%s', '%s', '%s']
        );

        $product_id = $wpdb->insert_id;
        $this->lookup_maps['products'][$sku] = $product_id;
        $this->placeholders[] = $sku;

        return $product_id;
    }

    /**
     * Import an image from URL
     *
     * @param string $url Image URL or relative path
     * @param string $filename_prefix Prefix for filename
     * @return int|null Attachment ID or null on failure
     */
    private function import_image($url, $filename_prefix) {
        if (empty($url)) {
            return null;
        }

        // Check if it's already a local media library URL (match by URL or filename)
        $upload_dir = wp_upload_dir();
        $site_url = site_url();

        // Check if the URL is from this site (covers both baseurl and site_url patterns)
        $is_local = (strpos($url, $upload_dir['baseurl']) === 0)
                 || (strpos($url, $site_url) === 0 && strpos($url, '/wp-content/uploads/') !== false);

        if ($is_local) {
            // Try to find existing attachment by URL
            $attachment_id = attachment_url_to_postid($url);
            if ($attachment_id) {
                return $attachment_id;
            }

            // Fallback: try matching by filename in wp_postmeta
            $filename = basename(parse_url($url, PHP_URL_PATH));
            if (!empty($filename)) {
                global $wpdb;
                $attachment_id = $wpdb->get_var($wpdb->prepare(
                    "SELECT post_id FROM {$wpdb->postmeta}
                     WHERE meta_key = '_wp_attached_file' AND meta_value LIKE %s
                     LIMIT 1",
                    '%' . $wpdb->esc_like($filename)
                ));
                if ($attachment_id) {
                    return (int) $attachment_id;
                }
            }
        }

        // Skip if URL is a relative path (from image package - not handled here)
        if (strpos($url, 'http') !== 0) {
            return null;
        }

        // Only download external images if the option is enabled
        if (empty($this->options['download_images'])) {
            return null;
        }

        // Download and import
        require_once(ABSPATH . 'wp-admin/includes/media.php');
        require_once(ABSPATH . 'wp-admin/includes/file.php');
        require_once(ABSPATH . 'wp-admin/includes/image.php');

        // Download file
        $tmp = download_url($url, 60);
        if (is_wp_error($tmp)) {
            $this->warnings[] = [
                'type' => 'image_download_failed',
                'url' => $url,
                'message' => $tmp->get_error_message(),
            ];
            return null;
        }

        // Get file info
        $file_array = [
            'name' => basename($url),
            'tmp_name' => $tmp,
        ];

        // Import to media library
        $attachment_id = media_handle_sideload($file_array, 0, $filename_prefix);

        // Clean up temp file
        @unlink($tmp);

        if (is_wp_error($attachment_id)) {
            $this->warnings[] = [
                'type' => 'image_import_failed',
                'url' => $url,
                'message' => $attachment_id->get_error_message(),
            ];
            return null;
        }

        return $attachment_id;
    }

    /**
     * Get import results
     *
     * @return array Results summary
     */
    public function get_results() {
        return [
            'success' => empty($this->errors),
            'stats' => $this->stats,
            'warnings' => $this->warnings,
            'errors' => $this->errors,
            'placeholders' => $this->placeholders,
            'totals' => [
                'created' => array_sum(array_column($this->stats, 'created')),
                'updated' => array_sum(array_column($this->stats, 'updated')),
                'skipped' => array_sum(array_column($this->stats, 'skipped')),
            ],
        ];
    }

    /**
     * Get current lookup maps (for debugging)
     *
     * @return array Lookup maps
     */
    public function get_lookup_maps() {
        return $this->lookup_maps;
    }
}
