<?php
/**
 * GGM Commerce CSV Exporter
 *
 * Handles CSV export functionality for products
 *
 * @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_CSV_Exporter {

    /**
     * Core product fields for export
     * @var array
     */
    private $core_fields = [
        'id', 'name', 'slug', 'sku', 'description', 'short_description',
        'price', 'sale_price', 'stock', 'stock_status', 'manage_stock',
        'weight', 'length', 'width', 'height', 'featured', 'status',
        'product_type', 'tax_rate_id', 'requires_shipping', 'is_virtual',
        'is_downloadable', 'has_variations', 'variation_mode'
    ];

    /**
     * Export products to CSV
     *
     * @param array $options Export options
     * @return array|WP_Error Result with file path or error
     */
    public function export_products($options = []) {
        $defaults = [
            'format' => 'simple', // simple or advanced
            'product_ids' => [], // Empty means all
            'category_id' => 0,
            'status' => '',
            'include_categories' => true,
            'include_tags' => true,
            'include_attributes' => true,
            'include_variations' => true,
            'include_barcodes' => true,
            'include_kits' => true,
            'include_downloads' => true,
        ];

        $options = wp_parse_args($options, $defaults);

        // Get products to export
        $products = $this->get_products_for_export($options);

        if (empty($products)) {
            return new WP_Error('no_products', __('No products found to export', 'ggm-commerce'));
        }

        if ($options['format'] === 'advanced') {
            return $this->export_advanced($products, $options);
        }

        return $this->export_simple($products, $options);
    }

    /**
     * Export products in simple format (single CSV with JSON for complex data)
     *
     * @param array $products Products to export
     * @param array $options Export options
     * @return array Result with file path
     */
    public function export_simple($products, $options) {
        global $wpdb;

        // Build headers
        $headers = $this->core_fields;

        // Add relationship fields
        if ($options['include_categories']) {
            $headers[] = 'categories';
        }
        if ($options['include_tags']) {
            $headers[] = 'tags';
        }

        // Image fields
        $headers[] = 'image';
        $headers[] = 'gallery_images';

        // Get all attributes for dynamic columns
        $attributes = [];
        if ($options['include_attributes']) {
            $attributes = $wpdb->get_results(
                "SELECT id, name FROM {$wpdb->prefix}ggmc_attributes WHERE status = 'active' ORDER BY name"
            );
            foreach ($attributes as $attr) {
                $headers[] = 'attr:' . $attr->name;
            }
        }

        // Premium fields
        if ($options['include_barcodes']) {
            $headers[] = 'barcode_primary';
            $headers[] = 'barcode_type';
            $headers[] = 'barcodes_additional';
        }

        if ($options['include_kits']) {
            $headers[] = 'kit_type';
            $headers[] = 'kit_auto_price';
            $headers[] = 'kit_components';
        }

        if ($options['include_downloads']) {
            $headers[] = 'downloads';
        }

        if ($options['include_variations']) {
            $headers[] = 'variations';
        }

        // Build CSV rows
        $rows = [];
        foreach ($products as $product) {
            $row = $this->build_product_row($product, $options, $attributes);
            $rows[] = $row;
        }

        // Generate CSV file
        $filename = 'ggm-products-export-' . gmdate('Y-m-d-His') . '.csv';
        $filepath = $this->get_export_directory() . $filename;

        $this->write_csv($filepath, $headers, $rows);

        return [
            'success' => true,
            'file' => $filepath,
            'filename' => $filename,
            'count' => count($rows),
            'format' => 'simple'
        ];
    }

    /**
     * Export products in advanced format (multiple CSV files in ZIP)
     *
     * @param array $products Products to export
     * @param array $options Export options
     * @return array Result with file path
     */
    public function export_advanced($products, $options) {
        global $wpdb;

        $files = [];
        $export_dir = $this->get_export_directory();
        $timestamp = gmdate('Y-m-d-His');

        // 1. Main products CSV
        $product_headers = array_merge($this->core_fields, ['categories', 'tags', 'image', 'gallery_images']);
        $product_rows = [];

        foreach ($products as $product) {
            $row = [];
            foreach ($this->core_fields as $field) {
                $row[$field] = isset($product->$field) ? $product->$field : '';
            }

            // Categories and tags
            $row['categories'] = $this->get_product_categories($product->id);
            $row['tags'] = $this->get_product_tags($product->id);
            $row['image'] = $this->get_image_url($product->image_id);
            $row['gallery_images'] = $this->get_gallery_urls($product->gallery_ids);

            $product_rows[] = $row;
        }

        $products_file = $export_dir . 'products-' . $timestamp . '.csv';
        $this->write_csv($products_file, $product_headers, $product_rows);
        $files['products'] = $products_file;

        // 2. Variations CSV (if any products have variations)
        if ($options['include_variations']) {
            $variations_data = $this->get_all_variations(array_column($products, 'id'));
            if (!empty($variations_data['rows'])) {
                $variations_file = $export_dir . 'variations-' . $timestamp . '.csv';
                $this->write_csv($variations_file, $variations_data['headers'], $variations_data['rows']);
                $files['variations'] = $variations_file;
            }
        }

        // 3. Barcodes CSV
        if ($options['include_barcodes']) {
            $barcodes_data = $this->get_all_barcodes(array_column($products, 'id'));
            if (!empty($barcodes_data)) {
                $barcodes_file = $export_dir . 'barcodes-' . $timestamp . '.csv';
                $this->write_csv($barcodes_file, ['product_sku', 'barcode_value', 'barcode_type', 'is_primary'], $barcodes_data);
                $files['barcodes'] = $barcodes_file;
            }
        }

        // 4. Kit components CSV
        if ($options['include_kits']) {
            $kits_data = $this->get_all_kit_components(array_column($products, 'id'));
            if (!empty($kits_data)) {
                $kits_file = $export_dir . 'kit_components-' . $timestamp . '.csv';
                $this->write_csv($kits_file, ['kit_sku', 'component_sku', 'quantity', 'display_order'], $kits_data);
                $files['kit_components'] = $kits_file;
            }
        }

        // 5. Downloads CSV
        if ($options['include_downloads']) {
            $downloads_data = $this->get_all_downloads(array_column($products, 'id'));
            if (!empty($downloads_data)) {
                $downloads_file = $export_dir . 'downloads-' . $timestamp . '.csv';
                $this->write_csv($downloads_file, ['product_sku', 'file_name', 'file_url', 'download_limit', 'access_expiry_days'], $downloads_data);
                $files['downloads'] = $downloads_file;
            }
        }

        // 6. Attributes CSV
        if ($options['include_attributes']) {
            $attributes_data = $this->get_all_product_attributes(array_column($products, 'id'));
            if (!empty($attributes_data)) {
                $attributes_file = $export_dir . 'attributes-' . $timestamp . '.csv';
                $this->write_csv($attributes_file, ['product_sku', 'attribute_name', 'attribute_value'], $attributes_data);
                $files['attributes'] = $attributes_file;
            }
        }

        // Create ZIP file
        $zip_filename = 'ggm-products-export-' . $timestamp . '.zip';
        $zip_filepath = $export_dir . $zip_filename;

        $zip = new ZipArchive();
        if ($zip->open($zip_filepath, ZipArchive::CREATE) === true) {
            foreach ($files as $name => $path) {
                $zip->addFile($path, basename($path));
            }
            $zip->close();

            // Clean up individual files
            foreach ($files as $path) {
                @unlink($path);
            }

            return [
                'success' => true,
                'file' => $zip_filepath,
                'filename' => $zip_filename,
                'count' => count($products),
                'format' => 'advanced',
                'files_included' => array_keys($files)
            ];
        }

        return new WP_Error('zip_error', __('Failed to create ZIP file', 'ggm-commerce'));
    }

    /**
     * Generate a blank CSV template
     *
     * @param string $format Template format (simple or advanced)
     * @return array Result with file path
     */
    public function generate_template($format = 'simple') {
        global $wpdb;

        $export_dir = $this->get_export_directory();
        $timestamp = gmdate('Y-m-d-His');

        if ($format === 'advanced') {
            return $this->generate_advanced_template();
        }

        // Simple format template
        $headers = $this->core_fields;
        $headers[] = 'categories';
        $headers[] = 'tags';
        $headers[] = 'image';
        $headers[] = 'gallery_images';

        // Get all attributes for dynamic columns
        $attributes = $wpdb->get_results(
            "SELECT name FROM {$wpdb->prefix}ggmc_attributes WHERE status = 'active' ORDER BY name"
        );
        foreach ($attributes as $attr) {
            $headers[] = 'attr:' . $attr->name;
        }

        // Premium fields
        $headers[] = 'barcode_primary';
        $headers[] = 'barcode_type';
        $headers[] = 'barcodes_additional';
        $headers[] = 'kit_type';
        $headers[] = 'kit_auto_price';
        $headers[] = 'kit_components';
        $headers[] = 'downloads';
        $headers[] = 'variations';

        // Example row
        $example_row = [
            'id' => '',
            'name' => 'Example Product',
            'slug' => 'example-product',
            'sku' => 'EX-001',
            'description' => 'Full product description here',
            'short_description' => 'Short description',
            'price' => '29.99',
            'sale_price' => '24.99',
            'stock' => '100',
            'stock_status' => 'instock',
            'manage_stock' => '1',
            'weight' => '0.5',
            'length' => '10',
            'width' => '5',
            'height' => '2',
            'featured' => '0',
            'status' => 'active',
            'product_type' => 'simple',
            'tax_rate_id' => '',
            'requires_shipping' => '1',
            'is_virtual' => '0',
            'is_downloadable' => '0',
            'has_variations' => '0',
            'variation_mode' => 'combined',
            'categories' => 'Category 1,Category 2',
            'tags' => 'tag1,tag2',
            'image' => 'https://example.com/image.jpg',
            'gallery_images' => 'https://example.com/image2.jpg,https://example.com/image3.jpg',
        ];

        // Add attribute placeholders
        foreach ($attributes as $attr) {
            $example_row['attr:' . $attr->name] = '';
        }

        // Add premium field examples
        $example_row['barcode_primary'] = '1234567890123';
        $example_row['barcode_type'] = 'EAN-13';
        $example_row['barcodes_additional'] = '[]';
        $example_row['kit_type'] = '';
        $example_row['kit_auto_price'] = '';
        $example_row['kit_components'] = '[]';
        $example_row['downloads'] = '[]';
        $example_row['variations'] = '[]';

        $filename = 'ggm-products-template.csv';
        $filepath = $export_dir . $filename;

        $this->write_csv($filepath, $headers, [$example_row]);

        return [
            'success' => true,
            'file' => $filepath,
            'filename' => $filename,
            'format' => 'simple'
        ];
    }

    /**
     * Generate advanced format templates (ZIP with multiple CSVs)
     *
     * @return array Result with file path
     */
    private function generate_advanced_template() {
        global $wpdb;

        $files = [];
        $export_dir = $this->get_export_directory();

        // 1. Products template
        $product_headers = array_merge($this->core_fields, ['categories', 'tags', 'image', 'gallery_images']);
        $product_example = [
            'id' => '',
            'name' => 'Example Product',
            'slug' => 'example-product',
            'sku' => 'EX-001',
            'description' => 'Full description',
            'short_description' => 'Short desc',
            'price' => '29.99',
            'sale_price' => '',
            'stock' => '100',
            'stock_status' => 'instock',
            'manage_stock' => '1',
            'weight' => '',
            'length' => '',
            'width' => '',
            'height' => '',
            'featured' => '0',
            'status' => 'active',
            'product_type' => 'simple',
            'tax_rate_id' => '',
            'requires_shipping' => '1',
            'is_virtual' => '0',
            'is_downloadable' => '0',
            'has_variations' => '0',
            'variation_mode' => 'combined',
            'categories' => 'Category 1,Category 2',
            'tags' => 'tag1,tag2',
            'image' => 'https://example.com/image.jpg',
            'gallery_images' => '',
        ];
        $products_file = $export_dir . 'products_template.csv';
        $this->write_csv($products_file, $product_headers, [$product_example]);
        $files['products'] = $products_file;

        // 2. Variations template
        $variation_headers = ['parent_sku', 'variation_sku', 'price', 'sale_price', 'stock', 'stock_status', 'image', 'is_default'];

        // Get variation types for dynamic columns
        $var_attributes = $wpdb->get_results(
            "SELECT name FROM {$wpdb->prefix}ggmc_variation_types WHERE status = 'active' ORDER BY name"
        );
        foreach ($var_attributes as $attr) {
            $variation_headers[] = 'attr:' . $attr->name;
        }

        $variation_example = [
            'parent_sku' => 'EX-001',
            'variation_sku' => 'EX-001-RED-L',
            'price' => '29.99',
            'sale_price' => '',
            'stock' => '50',
            'stock_status' => 'instock',
            'image' => '',
            'is_default' => '0',
        ];
        foreach ($var_attributes as $attr) {
            $variation_example['attr:' . $attr->name] = '';
        }

        $variations_file = $export_dir . 'variations_template.csv';
        $this->write_csv($variations_file, $variation_headers, [$variation_example]);
        $files['variations'] = $variations_file;

        // 3. Barcodes template
        $barcodes_file = $export_dir . 'barcodes_template.csv';
        $this->write_csv($barcodes_file,
            ['product_sku', 'barcode_value', 'barcode_type', 'is_primary'],
            [['product_sku' => 'EX-001', 'barcode_value' => '1234567890123', 'barcode_type' => 'EAN-13', 'is_primary' => '1']]
        );
        $files['barcodes'] = $barcodes_file;

        // 4. Kit components template
        $kits_file = $export_dir . 'kit_components_template.csv';
        $this->write_csv($kits_file,
            ['kit_sku', 'component_sku', 'quantity', 'display_order'],
            [['kit_sku' => 'KIT-001', 'component_sku' => 'COMP-001', 'quantity' => '2', 'display_order' => '1']]
        );
        $files['kit_components'] = $kits_file;

        // 5. Downloads template
        $downloads_file = $export_dir . 'downloads_template.csv';
        $this->write_csv($downloads_file,
            ['product_sku', 'file_name', 'file_url', 'download_limit', 'access_expiry_days'],
            [['product_sku' => 'DL-001', 'file_name' => 'Manual.pdf', 'file_url' => 'https://example.com/files/manual.pdf', 'download_limit' => '-1', 'access_expiry_days' => '-1']]
        );
        $files['downloads'] = $downloads_file;

        // 6. Attributes template
        $attributes_file = $export_dir . 'attributes_template.csv';
        $this->write_csv($attributes_file,
            ['product_sku', 'attribute_name', 'attribute_value'],
            [['product_sku' => 'EX-001', 'attribute_name' => 'Color', 'attribute_value' => 'Red']]
        );
        $files['attributes'] = $attributes_file;

        // Create ZIP
        $zip_filename = 'ggm-products-templates.zip';
        $zip_filepath = $export_dir . $zip_filename;

        $zip = new ZipArchive();
        if ($zip->open($zip_filepath, ZipArchive::CREATE) === true) {
            foreach ($files as $name => $path) {
                $zip->addFile($path, basename($path));
            }
            $zip->close();

            // Clean up individual files
            foreach ($files as $path) {
                @unlink($path);
            }

            return [
                'success' => true,
                'file' => $zip_filepath,
                'filename' => $zip_filename,
                'format' => 'advanced'
            ];
        }

        return new WP_Error('zip_error', __('Failed to create ZIP file', 'ggm-commerce'));
    }

    /**
     * Get products for export based on options
     *
     * @param array $options Filter options
     * @return array Products
     */
    private function get_products_for_export($options) {
        global $wpdb;

        $where = ['1=1'];
        $join = '';

        // Exclude deleted and trashed by default
        if (!empty($options['include_trashed'])) {
            $where[] = "p.status != 'deleted'";
        } else {
            $where[] = "p.status NOT IN ('deleted', 'trash')";
        }

        // Filter by specific product IDs
        if (!empty($options['product_ids'])) {
            $ids = array_map('intval', $options['product_ids']);
            $where[] = 'p.id IN (' . implode(',', $ids) . ')';
        }

        // Filter by category
        if (!empty($options['category_id'])) {
            $join = " INNER JOIN {$wpdb->prefix}ggmc_product_categories pc ON p.id = pc.product_id";
            $where[] = $wpdb->prepare('pc.category_id = %d', $options['category_id']);
        }

        // Filter by status (additional filter)
        if (!empty($options['status'])) {
            $where[] = $wpdb->prepare('p.status = %s', $options['status']);
        }

        $sql = "SELECT p.* FROM {$wpdb->prefix}ggmc_products p {$join} WHERE " . implode(' AND ', $where) . " ORDER BY p.id ASC";

        return $wpdb->get_results($sql);
    }

    /**
     * Build a product row for CSV export
     *
     * @param object $product Product object
     * @param array $options Export options
     * @param array $attributes Available attributes
     * @return array Row data
     */
    private function build_product_row($product, $options, $attributes) {
        global $wpdb;

        $row = [];

        // Core fields
        foreach ($this->core_fields as $field) {
            $row[$field] = isset($product->$field) ? $product->$field : '';
        }

        // Categories
        if ($options['include_categories']) {
            $row['categories'] = $this->get_product_categories($product->id);
        }

        // Tags
        if ($options['include_tags']) {
            $row['tags'] = $this->get_product_tags($product->id);
        }

        // Images
        $row['image'] = $this->get_image_url($product->image_id);
        $row['gallery_images'] = $this->get_gallery_urls($product->gallery_ids);

        // Attributes
        if ($options['include_attributes']) {
            $product_attrs = $this->get_product_attributes($product->id);
            foreach ($attributes as $attr) {
                $key = 'attr:' . $attr->name;
                $row[$key] = isset($product_attrs[$attr->id]) ? $product_attrs[$attr->id] : '';
            }
        }

        // Barcodes
        if ($options['include_barcodes']) {
            $barcodes = $this->get_product_barcodes($product->id);
            $row['barcode_primary'] = '';
            $row['barcode_type'] = '';
            $row['barcodes_additional'] = '[]';

            if (!empty($barcodes)) {
                foreach ($barcodes as $barcode) {
                    if ($barcode->is_primary) {
                        $row['barcode_primary'] = $barcode->barcode_value;
                        $row['barcode_type'] = $barcode->barcode_type;
                    }
                }
                // Additional barcodes as JSON
                $additional = array_filter($barcodes, function($b) { return !$b->is_primary; });
                if (!empty($additional)) {
                    $row['barcodes_additional'] = wp_json_encode(array_values(array_map(function($b) {
                        return ['value' => $b->barcode_value, 'type' => $b->barcode_type];
                    }, $additional)));
                }
            }
        }

        // Kits
        if ($options['include_kits']) {
            $kit = $this->get_product_kit($product->id);
            $row['kit_type'] = '';
            $row['kit_auto_price'] = '';
            $row['kit_components'] = '[]';

            if ($kit) {
                $row['kit_type'] = $kit->kit_type;
                $row['kit_auto_price'] = $kit->auto_calculate_price;
                $components = $this->get_kit_components($kit->id);
                $row['kit_components'] = wp_json_encode($components);
            }
        }

        // Downloads
        if ($options['include_downloads']) {
            $downloads = $this->get_product_downloads($product->id);
            $row['downloads'] = wp_json_encode($downloads);
        }

        // Variations
        if ($options['include_variations']) {
            $variations = $this->get_product_variations($product->id);
            $row['variations'] = wp_json_encode($variations);
        }

        return $row;
    }

    /**
     * Get product categories as comma-separated string
     */
    private function get_product_categories($product_id) {
        global $wpdb;

        $categories = $wpdb->get_col($wpdb->prepare(
            "SELECT c.name
             FROM {$wpdb->prefix}ggmc_categories c
             INNER JOIN {$wpdb->prefix}ggmc_product_categories pc ON c.id = pc.category_id
             WHERE pc.product_id = %d
             ORDER BY c.name",
            $product_id
        ));

        return implode(',', $categories);
    }

    /**
     * Get product tags as comma-separated string
     */
    private function get_product_tags($product_id) {
        global $wpdb;

        $tags = $wpdb->get_col($wpdb->prepare(
            "SELECT t.name
             FROM {$wpdb->prefix}ggmc_tags t
             INNER JOIN {$wpdb->prefix}ggmc_product_tags pt ON t.id = pt.tag_id
             WHERE pt.product_id = %d
             ORDER BY t.name",
            $product_id
        ));

        return implode(',', $tags);
    }

    /**
     * Get image URL from media ID
     */
    private function get_image_url($image_id) {
        if (empty($image_id)) {
            return '';
        }

        $url = wp_get_attachment_url($image_id);
        return $url ? $url : '';
    }

    /**
     * Get gallery URLs from gallery IDs
     */
    private function get_gallery_urls($gallery_ids) {
        if (empty($gallery_ids)) {
            return '';
        }

        $ids = is_string($gallery_ids) ? json_decode($gallery_ids, true) : $gallery_ids;
        if (empty($ids) || !is_array($ids)) {
            return '';
        }

        $urls = [];
        foreach ($ids as $id) {
            $url = wp_get_attachment_url($id);
            if ($url) {
                $urls[] = $url;
            }
        }

        return implode(',', $urls);
    }

    /**
     * Get product attributes
     */
    private function get_product_attributes($product_id) {
        global $wpdb;

        $results = $wpdb->get_results($wpdb->prepare(
            "SELECT pav.attribute_id, pav.attribute_value
             FROM {$wpdb->prefix}ggmc_product_attribute_values pav
             WHERE pav.product_id = %d",
            $product_id
        ));

        $attrs = [];
        foreach ($results as $row) {
            $attrs[$row->attribute_id] = $row->attribute_value;
        }

        return $attrs;
    }

    /**
     * Get product barcodes
     */
    private function get_product_barcodes($product_id) {
        global $wpdb;

        return $wpdb->get_results($wpdb->prepare(
            "SELECT barcode_value, barcode_type, is_primary
             FROM {$wpdb->prefix}ggmc_product_barcodes
             WHERE product_id = %d
             ORDER BY is_primary DESC",
            $product_id
        ));
    }

    /**
     * Get product kit info
     */
    private function get_product_kit($product_id) {
        global $wpdb;

        return $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$wpdb->prefix}ggmc_product_kits WHERE product_id = %d",
            $product_id
        ));
    }

    /**
     * Get kit components
     */
    private function get_kit_components($kit_id) {
        global $wpdb;

        $components = $wpdb->get_results($wpdb->prepare(
            "SELECT p.sku, kc.quantity
             FROM {$wpdb->prefix}ggmc_kit_components kc
             INNER JOIN {$wpdb->prefix}ggmc_products p ON kc.component_product_id = p.id
             WHERE kc.kit_id = %d
             ORDER BY kc.display_order",
            $kit_id
        ));

        return array_map(function($c) {
            return ['sku' => $c->sku, 'qty' => (int)$c->quantity];
        }, $components);
    }

    /**
     * Get product downloads
     */
    private function get_product_downloads($product_id) {
        global $wpdb;

        $downloads = $wpdb->get_results($wpdb->prepare(
            "SELECT name, file_url, download_limit, access_expiry_days
             FROM {$wpdb->prefix}ggmc_product_downloads
             WHERE product_id = %d AND status = 'active'
             ORDER BY display_order",
            $product_id
        ));

        return array_map(function($d) {
            return [
                'name' => $d->name,
                'url' => $d->file_url,
                'limit' => (int)$d->download_limit,
                'expiry_days' => (int)$d->access_expiry_days
            ];
        }, $downloads);
    }

    /**
     * Get product variations
     */
    private function get_product_variations($product_id) {
        global $wpdb;

        $variations = $wpdb->get_results($wpdb->prepare(
            "SELECT v.*,
                    GROUP_CONCAT(CONCAT(vt.name, ':', vtv.option_value) SEPARATOR '|') as attributes
             FROM {$wpdb->prefix}ggmc_product_variations v
             LEFT JOIN {$wpdb->prefix}ggmc_variation_type_values vtv ON v.id = vtv.variation_id
             LEFT JOIN {$wpdb->prefix}ggmc_variation_types vt ON vtv.variation_type_id = vt.id
             WHERE v.product_id = %d AND v.status = 'active'
             GROUP BY v.id
             ORDER BY v.id",
            $product_id
        ));

        return array_map(function($v) {
            $attrs = [];
            if (!empty($v->attributes)) {
                foreach (explode('|', $v->attributes) as $attr) {
                    list($name, $value) = explode(':', $attr, 2);
                    $attrs[$name] = $value;
                }
            }

            return [
                'sku' => $v->sku,
                'price' => $v->price,
                'sale_price' => $v->sale_price,
                'stock' => $v->stock,
                'stock_status' => $v->stock_status,
                'is_default' => (int)$v->is_default,
                'image' => $this->get_image_url($v->image_id),
                'attributes' => $attrs
            ];
        }, $variations);
    }

    /**
     * Get all variations for multiple products (advanced export)
     */
    private function get_all_variations($product_ids) {
        global $wpdb;

        if (empty($product_ids)) {
            return ['headers' => [], 'rows' => []];
        }

        $ids_str = implode(',', array_map('intval', $product_ids));

        // Get variation types used
        $var_attrs = $wpdb->get_results(
            "SELECT DISTINCT vt.id, vt.name
             FROM {$wpdb->prefix}ggmc_variation_types vt
             WHERE vt.status = 'active'
             ORDER BY vt.name"
        );

        $headers = ['parent_sku', 'variation_sku', 'price', 'sale_price', 'stock', 'stock_status', 'image', 'is_default'];
        foreach ($var_attrs as $attr) {
            $headers[] = 'attr:' . $attr->name;
        }

        $variations = $wpdb->get_results(
            "SELECT v.*, p.sku as parent_sku
             FROM {$wpdb->prefix}ggmc_product_variations v
             INNER JOIN {$wpdb->prefix}ggmc_products p ON v.product_id = p.id
             WHERE v.product_id IN ({$ids_str}) AND v.status = 'active'
             ORDER BY p.sku, v.id"
        );

        $rows = [];
        foreach ($variations as $v) {
            $row = [
                'parent_sku' => $v->parent_sku,
                'variation_sku' => $v->sku,
                'price' => $v->price,
                'sale_price' => $v->sale_price,
                'stock' => $v->stock,
                'stock_status' => $v->stock_status,
                'image' => $this->get_image_url($v->image_id),
                'is_default' => $v->is_default
            ];

            // Get type values for this variation
            $attr_values = $wpdb->get_results($wpdb->prepare(
                "SELECT vt.name, vtv.option_value
                 FROM {$wpdb->prefix}ggmc_variation_type_values vtv
                 INNER JOIN {$wpdb->prefix}ggmc_variation_types vt ON vtv.variation_type_id = vt.id
                 WHERE vtv.variation_id = %d",
                $v->id
            ));

            $attr_map = [];
            foreach ($attr_values as $av) {
                $attr_map[$av->name] = $av->option_value;
            }

            foreach ($var_attrs as $attr) {
                $row['attr:' . $attr->name] = isset($attr_map[$attr->name]) ? $attr_map[$attr->name] : '';
            }

            $rows[] = $row;
        }

        return ['headers' => $headers, 'rows' => $rows];
    }

    /**
     * Get all barcodes for multiple products (advanced export)
     */
    private function get_all_barcodes($product_ids) {
        global $wpdb;

        if (empty($product_ids)) {
            return [];
        }

        $ids_str = implode(',', array_map('intval', $product_ids));

        return $wpdb->get_results(
            "SELECT p.sku as product_sku, b.barcode_value, b.barcode_type, b.is_primary
             FROM {$wpdb->prefix}ggmc_product_barcodes b
             INNER JOIN {$wpdb->prefix}ggmc_products p ON b.product_id = p.id
             WHERE b.product_id IN ({$ids_str})
             ORDER BY p.sku, b.is_primary DESC",
            ARRAY_A
        );
    }

    /**
     * Get all kit components for multiple products (advanced export)
     */
    private function get_all_kit_components($product_ids) {
        global $wpdb;

        if (empty($product_ids)) {
            return [];
        }

        $ids_str = implode(',', array_map('intval', $product_ids));

        return $wpdb->get_results(
            "SELECT pk_p.sku as kit_sku, comp_p.sku as component_sku, kc.quantity, kc.display_order
             FROM {$wpdb->prefix}ggmc_kit_components kc
             INNER JOIN {$wpdb->prefix}ggmc_product_kits pk ON kc.kit_id = pk.id
             INNER JOIN {$wpdb->prefix}ggmc_products pk_p ON pk.product_id = pk_p.id
             INNER JOIN {$wpdb->prefix}ggmc_products comp_p ON kc.component_product_id = comp_p.id
             WHERE pk.product_id IN ({$ids_str})
             ORDER BY pk_p.sku, kc.display_order",
            ARRAY_A
        );
    }

    /**
     * Get all downloads for multiple products (advanced export)
     */
    private function get_all_downloads($product_ids) {
        global $wpdb;

        if (empty($product_ids)) {
            return [];
        }

        $ids_str = implode(',', array_map('intval', $product_ids));

        return $wpdb->get_results(
            "SELECT p.sku as product_sku, d.name as file_name, d.file_url, d.download_limit, d.access_expiry_days
             FROM {$wpdb->prefix}ggmc_product_downloads d
             INNER JOIN {$wpdb->prefix}ggmc_products p ON d.product_id = p.id
             WHERE d.product_id IN ({$ids_str}) AND d.status = 'active'
             ORDER BY p.sku, d.display_order",
            ARRAY_A
        );
    }

    /**
     * Get all product attributes for multiple products (advanced export)
     */
    private function get_all_product_attributes($product_ids) {
        global $wpdb;

        if (empty($product_ids)) {
            return [];
        }

        $ids_str = implode(',', array_map('intval', $product_ids));

        return $wpdb->get_results(
            "SELECT p.sku as product_sku, a.name as attribute_name, pav.attribute_value
             FROM {$wpdb->prefix}ggmc_product_attribute_values pav
             INNER JOIN {$wpdb->prefix}ggmc_products p ON pav.product_id = p.id
             INNER JOIN {$wpdb->prefix}ggmc_attributes a ON pav.attribute_id = a.id
             WHERE pav.product_id IN ({$ids_str})
             ORDER BY p.sku, a.name",
            ARRAY_A
        );
    }

    /**
     * Get export directory path
     */
    private function get_export_directory() {
        $upload_dir = wp_upload_dir();
        $export_dir = $upload_dir['basedir'] . '/ggm-commerce-exports/';

        if (!file_exists($export_dir)) {
            wp_mkdir_p($export_dir);

            // Add index.php to prevent directory listing
            file_put_contents($export_dir . 'index.php', '<?php // Silence is golden');

            // Add .htaccess to protect files
            file_put_contents($export_dir . '.htaccess', "deny from all\n");
        }

        return $export_dir;
    }

    /**
     * Write CSV file
     *
     * @param string $filepath File path
     * @param array $headers CSV headers
     * @param array $rows Data rows
     */
    private function write_csv($filepath, $headers, $rows) {
        $handle = fopen($filepath, 'w');

        // Add BOM for Excel UTF-8 compatibility
        fwrite($handle, "\xEF\xBB\xBF");

        // Write headers
        fputcsv($handle, $headers);

        // Write rows
        foreach ($rows as $row) {
            // Ensure row values are in header order
            $ordered_row = [];
            foreach ($headers as $header) {
                $ordered_row[] = isset($row[$header]) ? $row[$header] : '';
            }
            fputcsv($handle, $ordered_row);
        }

        fclose($handle);
    }
}
