<?php
/**
 * GGM Commerce Product Management Tools
 *
 * MCP Tools for full product CRUD operations
 * Tools are executable actions that AI can invoke
 *
 * @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_Product_Management_Tools {

    /**
     * MCP Server instance
     * @var GGM_Commerce_MCP_Server
     */
    private $server;

    /**
     * Constructor
     *
     * @param GGM_Commerce_MCP_Server $server MCP server instance
     */
    public function __construct($server) {
        $this->server = $server;
        $this->register_tools();
    }

    /**
     * Register all product management tools
     */
    private function register_tools() {
        // Create product
        $this->server->register_tool(
            'create_product',
            'Create a new product with all details',
            [
                'type' => 'object',
                'properties' => [
                    'name' => [
                        'type' => 'string',
                        'description' => 'Product name (required)'
                    ],
                    'sku' => [
                        'type' => 'string',
                        'description' => 'Product SKU (unique identifier)'
                    ],
                    'description' => [
                        'type' => 'string',
                        'description' => 'Full product description'
                    ],
                    'short_description' => [
                        'type' => 'string',
                        'description' => 'Short product description'
                    ],
                    'price' => [
                        'type' => 'number',
                        'description' => 'Regular price',
                        'minimum' => 0
                    ],
                    'sale_price' => [
                        'type' => 'number',
                        'description' => 'Sale price (must be less than regular price)',
                        'minimum' => 0
                    ],
                    'stock' => [
                        'type' => 'integer',
                        'description' => 'Stock quantity',
                        'minimum' => 0,
                        'default' => 0
                    ],
                    'low_stock_threshold' => [
                        'type' => 'integer',
                        'description' => 'Low stock alert threshold',
                        'minimum' => 0,
                        'default' => 10
                    ],
                    'restock_date' => [
                        'type' => 'string',
                        'description' => 'Expected restock date in YYYY-MM-DD format (shown to customers when out of stock)'
                    ],
                    'weight' => [
                        'type' => 'number',
                        'description' => 'Product weight',
                        'minimum' => 0
                    ],
                    'featured' => [
                        'type' => 'boolean',
                        'description' => 'Whether product is featured',
                        'default' => false
                    ],
                    'status' => [
                        'type' => 'string',
                        'description' => 'Product status',
                        'enum' => ['active', 'draft', 'archived'],
                        'default' => 'active'
                    ],
                    'product_type' => [
                        'type' => 'string',
                        'description' => 'Product type (e.g. simple, variable, digital, virtual, kit, build, gift_card)',
                        'default' => 'simple'
                    ],
                    'category_ids' => [
                        'type' => 'array',
                        'description' => 'Array of category IDs to assign',
                        'items' => ['type' => 'integer']
                    ],
                    'tag_ids' => [
                        'type' => 'array',
                        'description' => 'Array of tag IDs to assign',
                        'items' => ['type' => 'integer']
                    ]
                ],
                'required' => ['name']
            ],
            [$this, 'create_product']
        );

        // Update product
        $this->server->register_tool(
            'update_product',
            'Update an existing product (any field)',
            [
                'type' => 'object',
                'properties' => [
                    'product_id' => [
                        'type' => 'integer',
                        'description' => 'Product ID to update (required)',
                        'minimum' => 1
                    ],
                    'name' => [
                        'type' => 'string',
                        'description' => 'Product name'
                    ],
                    'sku' => [
                        'type' => 'string',
                        'description' => 'Product SKU'
                    ],
                    'description' => [
                        'type' => 'string',
                        'description' => 'Full product description'
                    ],
                    'short_description' => [
                        'type' => 'string',
                        'description' => 'Short product description'
                    ],
                    'price' => [
                        'type' => 'number',
                        'description' => 'Regular price',
                        'minimum' => 0
                    ],
                    'sale_price' => [
                        'type' => 'number',
                        'description' => 'Sale price (must be less than regular price)',
                        'minimum' => 0
                    ],
                    'stock' => [
                        'type' => 'integer',
                        'description' => 'Stock quantity',
                        'minimum' => 0
                    ],
                    'low_stock_threshold' => [
                        'type' => 'integer',
                        'description' => 'Low stock alert threshold',
                        'minimum' => 0
                    ],
                    'restock_date' => [
                        'type' => 'string',
                        'description' => 'Expected restock date in YYYY-MM-DD format (shown to customers when out of stock). Pass empty string to clear.'
                    ],
                    'weight' => [
                        'type' => 'number',
                        'description' => 'Product weight',
                        'minimum' => 0
                    ],
                    'featured' => [
                        'type' => 'boolean',
                        'description' => 'Whether product is featured'
                    ],
                    'status' => [
                        'type' => 'string',
                        'description' => 'Product status',
                        'enum' => ['active', 'draft', 'archived']
                    ],
                    'product_type' => [
                        'type' => 'string',
                        'description' => 'Product type (e.g. simple, variable, digital, virtual, kit, build, gift_card)'
                    ]
                ],
                'required' => ['product_id']
            ],
            [$this, 'update_product']
        );

        // Delete product
        $this->server->register_tool(
            'delete_product',
            'Delete a product (soft delete - sets status to archived)',
            [
                'type' => 'object',
                'properties' => [
                    'product_id' => [
                        'type' => 'integer',
                        'description' => 'Product ID to delete',
                        'minimum' => 1
                    ],
                    'permanent' => [
                        'type' => 'boolean',
                        'description' => 'If true, permanently delete instead of archiving',
                        'default' => false
                    ]
                ],
                'required' => ['product_id']
            ],
            [$this, 'delete_product']
        );

        // Assign categories
        $this->server->register_tool(
            'assign_product_categories',
            'Assign categories to a product (replaces existing categories)',
            [
                'type' => 'object',
                'properties' => [
                    'product_id' => [
                        'type' => 'integer',
                        'description' => 'Product ID',
                        'minimum' => 1
                    ],
                    'category_ids' => [
                        'type' => 'array',
                        'description' => 'Array of category IDs to assign',
                        'items' => ['type' => 'integer']
                    ],
                    'append' => [
                        'type' => 'boolean',
                        'description' => 'If true, add to existing categories instead of replacing',
                        'default' => false
                    ]
                ],
                'required' => ['product_id', 'category_ids']
            ],
            [$this, 'assign_product_categories']
        );

        // Assign tags
        $this->server->register_tool(
            'assign_product_tags',
            'Assign tags to a product (replaces existing tags)',
            [
                'type' => 'object',
                'properties' => [
                    'product_id' => [
                        'type' => 'integer',
                        'description' => 'Product ID',
                        'minimum' => 1
                    ],
                    'tag_ids' => [
                        'type' => 'array',
                        'description' => 'Array of tag IDs to assign',
                        'items' => ['type' => 'integer']
                    ],
                    'append' => [
                        'type' => 'boolean',
                        'description' => 'If true, add to existing tags instead of replacing',
                        'default' => false
                    ]
                ],
                'required' => ['product_id', 'tag_ids']
            ],
            [$this, 'assign_product_tags']
        );

        // Set featured status
        $this->server->register_tool(
            'set_product_featured',
            'Set or unset a product as featured',
            [
                'type' => 'object',
                'properties' => [
                    'product_id' => [
                        'type' => 'integer',
                        'description' => 'Product ID',
                        'minimum' => 1
                    ],
                    'featured' => [
                        'type' => 'boolean',
                        'description' => 'Whether the product should be featured'
                    ]
                ],
                'required' => ['product_id', 'featured']
            ],
            [$this, 'set_product_featured']
        );

        // Duplicate product
        $this->server->register_tool(
            'duplicate_product',
            'Create a copy of an existing product',
            [
                'type' => 'object',
                'properties' => [
                    'product_id' => [
                        'type' => 'integer',
                        'description' => 'Product ID to duplicate',
                        'minimum' => 1
                    ],
                    'new_name' => [
                        'type' => 'string',
                        'description' => 'Name for the duplicated product (optional, defaults to "Copy of [original name]")'
                    ],
                    'new_sku' => [
                        'type' => 'string',
                        'description' => 'SKU for the duplicated product (optional)'
                    ]
                ],
                'required' => ['product_id']
            ],
            [$this, 'duplicate_product']
        );
    }

    /**
     * Create a new product
     *
     * @param array $args Tool arguments
     * @return array Result
     */
    public function create_product($args) {
        global $wpdb;

        $name = sanitize_text_field($args['name']);
        if (empty($name)) {
            return [
                'success' => false,
                'error' => 'Product name is required'
            ];
        }

        // Check for duplicate SKU if provided
        if (!empty($args['sku'])) {
            $existing = $wpdb->get_var($wpdb->prepare(
                "SELECT id FROM {$wpdb->prefix}ggmc_products WHERE sku = %s",
                sanitize_text_field($args['sku'])
            ));
            if ($existing) {
                return [
                    'success' => false,
                    'error' => 'A product with this SKU already exists'
                ];
            }
        }

        // Validate sale price
        if (isset($args['sale_price']) && isset($args['price'])) {
            if ($args['sale_price'] >= $args['price']) {
                return [
                    'success' => false,
                    'error' => 'Sale price must be less than regular price'
                ];
            }
        }

        // Prepare product data
        $stock = isset($args['stock']) ? intval($args['stock']) : 0;
        $stock_status = $stock > 0 ? 'instock' : 'outofstock';

        $product_data = [
            'name' => $name,
            'slug' => sanitize_title($name),
            'sku' => isset($args['sku']) ? sanitize_text_field($args['sku']) : null,
            'description' => isset($args['description']) ? sanitize_textarea_field($args['description']) : '',
            'short_description' => isset($args['short_description']) ? sanitize_textarea_field($args['short_description']) : '',
            'price' => isset($args['price']) ? floatval($args['price']) : 0,
            'sale_price' => isset($args['sale_price']) ? floatval($args['sale_price']) : null,
            'stock' => $stock,
            'stock_status' => $stock_status,
            'low_stock_threshold' => isset($args['low_stock_threshold']) ? intval($args['low_stock_threshold']) : 10,
            'restock_date' => !empty($args['restock_date']) ? sanitize_text_field($args['restock_date']) : null,
            'weight' => isset($args['weight']) ? floatval($args['weight']) : null,
            'featured' => isset($args['featured']) && $args['featured'] ? 1 : 0,
            'product_type' => isset($args['product_type']) ? sanitize_text_field($args['product_type']) : 'simple',
            'status' => isset($args['status']) ? sanitize_text_field($args['status']) : 'active',
            'created_at' => current_time('mysql'),
            'updated_at' => current_time('mysql')
        ];

        $inserted = $wpdb->insert("{$wpdb->prefix}ggmc_products", $product_data);

        if (!$inserted) {
            return [
                'success' => false,
                'error' => 'Failed to create product: ' . $wpdb->last_error
            ];
        }

        $product_id = $wpdb->insert_id;

        // Assign categories if provided
        if (!empty($args['category_ids']) && is_array($args['category_ids'])) {
            $this->assign_categories_to_product($product_id, $args['category_ids']);
        }

        // Assign tags if provided
        if (!empty($args['tag_ids']) && is_array($args['tag_ids'])) {
            $this->assign_tags_to_product($product_id, $args['tag_ids']);
        }

        return [
            'success' => true,
            'message' => 'Product created successfully',
            'product_id' => $product_id,
            'product' => $this->get_product_summary($product_id)
        ];
    }

    /**
     * Update an existing product
     *
     * @param array $args Tool arguments
     * @return array Result
     */
    public function update_product($args) {
        global $wpdb;

        $product_id = intval($args['product_id']);

        // Check product exists
        $product = $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$wpdb->prefix}ggmc_products WHERE id = %d",
            $product_id
        ));

        if (!$product) {
            return [
                'success' => false,
                'error' => 'Product not found'
            ];
        }

        // Check for duplicate SKU if changing
        if (!empty($args['sku']) && $args['sku'] !== $product->sku) {
            $existing = $wpdb->get_var($wpdb->prepare(
                "SELECT id FROM {$wpdb->prefix}ggmc_products WHERE sku = %s AND id != %d",
                sanitize_text_field($args['sku']),
                $product_id
            ));
            if ($existing) {
                return [
                    'success' => false,
                    'error' => 'A product with this SKU already exists'
                ];
            }
        }

        // Build update data
        $update_data = [];
        $changes = [];

        if (isset($args['name'])) {
            $update_data['name'] = sanitize_text_field($args['name']);
            $update_data['slug'] = sanitize_title($args['name']);
            $changes['name'] = ['old' => $product->name, 'new' => $update_data['name']];
        }

        if (isset($args['sku'])) {
            $update_data['sku'] = sanitize_text_field($args['sku']);
            $changes['sku'] = ['old' => $product->sku, 'new' => $update_data['sku']];
        }

        if (isset($args['description'])) {
            $update_data['description'] = sanitize_textarea_field($args['description']);
            $changes['description'] = 'updated';
        }

        if (isset($args['short_description'])) {
            $update_data['short_description'] = sanitize_textarea_field($args['short_description']);
            $changes['short_description'] = 'updated';
        }

        if (isset($args['price'])) {
            $update_data['price'] = floatval($args['price']);
            $changes['price'] = ['old' => (float)$product->price, 'new' => $update_data['price']];
        }

        if (isset($args['sale_price'])) {
            $price_to_check = isset($args['price']) ? floatval($args['price']) : (float)$product->price;
            if ($args['sale_price'] >= $price_to_check) {
                return [
                    'success' => false,
                    'error' => 'Sale price must be less than regular price'
                ];
            }
            $update_data['sale_price'] = floatval($args['sale_price']);
            $changes['sale_price'] = ['old' => $product->sale_price, 'new' => $update_data['sale_price']];
        }

        if (isset($args['stock'])) {
            $update_data['stock'] = intval($args['stock']);
            $update_data['stock_status'] = $update_data['stock'] > 0 ? 'instock' : 'outofstock';
            $changes['stock'] = ['old' => (int)$product->stock, 'new' => $update_data['stock']];
        }

        if (isset($args['low_stock_threshold'])) {
            $update_data['low_stock_threshold'] = intval($args['low_stock_threshold']);
            $changes['low_stock_threshold'] = ['old' => $product->low_stock_threshold, 'new' => $update_data['low_stock_threshold']];
        }

        if (isset($args['restock_date'])) {
            $update_data['restock_date'] = !empty($args['restock_date']) ? sanitize_text_field($args['restock_date']) : null;
            $changes['restock_date'] = ['old' => $product->restock_date ?? null, 'new' => $update_data['restock_date']];
        }

        if (isset($args['weight'])) {
            $update_data['weight'] = floatval($args['weight']);
            $changes['weight'] = ['old' => $product->weight, 'new' => $update_data['weight']];
        }

        if (isset($args['featured'])) {
            $update_data['featured'] = $args['featured'] ? 1 : 0;
            $changes['featured'] = ['old' => (bool)$product->featured, 'new' => (bool)$update_data['featured']];
        }

        if (isset($args['status'])) {
            $update_data['status'] = sanitize_text_field($args['status']);
            $changes['status'] = ['old' => $product->status, 'new' => $update_data['status']];
        }

        if (isset($args['product_type'])) {
            $update_data['product_type'] = sanitize_text_field($args['product_type']);
            $changes['product_type'] = ['old' => $product->product_type ?? 'simple', 'new' => $update_data['product_type']];
        }

        if (empty($update_data)) {
            return [
                'success' => false,
                'error' => 'No fields to update'
            ];
        }

        $update_data['updated_at'] = current_time('mysql');

        $updated = $wpdb->update(
            "{$wpdb->prefix}ggmc_products",
            $update_data,
            ['id' => $product_id]
        );

        if ($updated === false) {
            return [
                'success' => false,
                'error' => 'Failed to update product: ' . $wpdb->last_error
            ];
        }

        return [
            'success' => true,
            'message' => 'Product updated successfully',
            'product_id' => $product_id,
            'changes' => $changes,
            'product' => $this->get_product_summary($product_id)
        ];
    }

    /**
     * Delete a product
     *
     * @param array $args Tool arguments
     * @return array Result
     */
    public function delete_product($args) {
        global $wpdb;

        $product_id = intval($args['product_id']);
        $permanent = isset($args['permanent']) && $args['permanent'];

        // Check product exists
        $product = $wpdb->get_row($wpdb->prepare(
            "SELECT id, name, sku FROM {$wpdb->prefix}ggmc_products WHERE id = %d",
            $product_id
        ));

        if (!$product) {
            return [
                'success' => false,
                'error' => 'Product not found'
            ];
        }

        if ($permanent) {
            // Permanently delete
            // First remove category and tag relationships
            $wpdb->delete("{$wpdb->prefix}ggmc_product_categories", ['product_id' => $product_id]);
            $wpdb->delete("{$wpdb->prefix}ggmc_product_tags", ['product_id' => $product_id]);
            $wpdb->delete("{$wpdb->prefix}ggmc_product_barcodes", ['product_id' => $product_id]);

            // Delete the product
            $deleted = $wpdb->delete("{$wpdb->prefix}ggmc_products", ['id' => $product_id]);

            if ($deleted === false) {
                return [
                    'success' => false,
                    'error' => 'Failed to delete product'
                ];
            }

            return [
                'success' => true,
                'message' => 'Product permanently deleted',
                'product_id' => $product_id,
                'product_name' => $product->name,
                'action' => 'permanent_delete'
            ];
        } else {
            // Soft delete - archive the product
            $updated = $wpdb->update(
                "{$wpdb->prefix}ggmc_products",
                [
                    'status' => 'archived',
                    'updated_at' => current_time('mysql')
                ],
                ['id' => $product_id]
            );

            if ($updated === false) {
                return [
                    'success' => false,
                    'error' => 'Failed to archive product'
                ];
            }

            return [
                'success' => true,
                'message' => 'Product archived (can be restored by changing status)',
                'product_id' => $product_id,
                'product_name' => $product->name,
                'action' => 'archived'
            ];
        }
    }

    /**
     * Assign categories to a product
     *
     * @param array $args Tool arguments
     * @return array Result
     */
    public function assign_product_categories($args) {
        global $wpdb;

        $product_id = intval($args['product_id']);
        $category_ids = array_map('intval', $args['category_ids']);
        $append = isset($args['append']) && $args['append'];

        // Check product exists
        $product = $wpdb->get_row($wpdb->prepare(
            "SELECT id, name FROM {$wpdb->prefix}ggmc_products WHERE id = %d",
            $product_id
        ));

        if (!$product) {
            return [
                'success' => false,
                'error' => 'Product not found'
            ];
        }

        // Validate categories exist
        $valid_categories = [];
        foreach ($category_ids as $cat_id) {
            $exists = $wpdb->get_var($wpdb->prepare(
                "SELECT id FROM {$wpdb->prefix}ggmc_categories WHERE id = %d AND status = 'active'",
                $cat_id
            ));
            if ($exists) {
                $valid_categories[] = $cat_id;
            }
        }

        if (empty($valid_categories)) {
            return [
                'success' => false,
                'error' => 'No valid categories provided'
            ];
        }

        // If not appending, remove existing categories
        if (!$append) {
            $wpdb->delete("{$wpdb->prefix}ggmc_product_categories", ['product_id' => $product_id]);
        }

        // Assign categories
        $assigned = $this->assign_categories_to_product($product_id, $valid_categories, $append);

        // Get current categories
        $current_categories = $wpdb->get_results($wpdb->prepare(
            "SELECT c.id, 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",
            $product_id
        ));

        return [
            'success' => true,
            'message' => $append ? 'Categories added to product' : 'Product categories updated',
            'product_id' => $product_id,
            'product_name' => $product->name,
            'categories' => $current_categories
        ];
    }

    /**
     * Assign tags to a product
     *
     * @param array $args Tool arguments
     * @return array Result
     */
    public function assign_product_tags($args) {
        global $wpdb;

        $product_id = intval($args['product_id']);
        $tag_ids = array_map('intval', $args['tag_ids']);
        $append = isset($args['append']) && $args['append'];

        // Check product exists
        $product = $wpdb->get_row($wpdb->prepare(
            "SELECT id, name FROM {$wpdb->prefix}ggmc_products WHERE id = %d",
            $product_id
        ));

        if (!$product) {
            return [
                'success' => false,
                'error' => 'Product not found'
            ];
        }

        // Validate tags exist
        $valid_tags = [];
        foreach ($tag_ids as $tag_id) {
            $exists = $wpdb->get_var($wpdb->prepare(
                "SELECT id FROM {$wpdb->prefix}ggmc_tags WHERE id = %d AND status = 'active'",
                $tag_id
            ));
            if ($exists) {
                $valid_tags[] = $tag_id;
            }
        }

        if (empty($valid_tags)) {
            return [
                'success' => false,
                'error' => 'No valid tags provided'
            ];
        }

        // If not appending, remove existing tags
        if (!$append) {
            $wpdb->delete("{$wpdb->prefix}ggmc_product_tags", ['product_id' => $product_id]);
        }

        // Assign tags
        $this->assign_tags_to_product($product_id, $valid_tags, $append);

        // Get current tags
        $current_tags = $wpdb->get_results($wpdb->prepare(
            "SELECT t.id, 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",
            $product_id
        ));

        return [
            'success' => true,
            'message' => $append ? 'Tags added to product' : 'Product tags updated',
            'product_id' => $product_id,
            'product_name' => $product->name,
            'tags' => $current_tags
        ];
    }

    /**
     * Set product featured status
     *
     * @param array $args Tool arguments
     * @return array Result
     */
    public function set_product_featured($args) {
        global $wpdb;

        $product_id = intval($args['product_id']);
        $featured = (bool) $args['featured'];

        // Check product exists
        $product = $wpdb->get_row($wpdb->prepare(
            "SELECT id, name, featured FROM {$wpdb->prefix}ggmc_products WHERE id = %d",
            $product_id
        ));

        if (!$product) {
            return [
                'success' => false,
                'error' => 'Product not found'
            ];
        }

        $was_featured = (bool) $product->featured;

        if ($was_featured === $featured) {
            return [
                'success' => true,
                'message' => $featured ? 'Product is already featured' : 'Product is already not featured',
                'product_id' => $product_id,
                'product_name' => $product->name,
                'featured' => $featured
            ];
        }

        $updated = $wpdb->update(
            "{$wpdb->prefix}ggmc_products",
            [
                'featured' => $featured ? 1 : 0,
                'updated_at' => current_time('mysql')
            ],
            ['id' => $product_id]
        );

        if ($updated === false) {
            return [
                'success' => false,
                'error' => 'Failed to update featured status'
            ];
        }

        return [
            'success' => true,
            'message' => $featured ? 'Product is now featured' : 'Product is no longer featured',
            'product_id' => $product_id,
            'product_name' => $product->name,
            'was_featured' => $was_featured,
            'featured' => $featured
        ];
    }

    /**
     * Duplicate a product
     *
     * @param array $args Tool arguments
     * @return array Result
     */
    public function duplicate_product($args) {
        global $wpdb;

        $product_id = intval($args['product_id']);

        // Get original product
        $product = $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$wpdb->prefix}ggmc_products WHERE id = %d",
            $product_id
        ));

        if (!$product) {
            return [
                'success' => false,
                'error' => 'Product not found'
            ];
        }

        // Prepare new product name
        $new_name = isset($args['new_name']) && !empty($args['new_name'])
            ? sanitize_text_field($args['new_name'])
            : 'Copy of ' . $product->name;

        // Prepare new SKU
        $new_sku = isset($args['new_sku']) && !empty($args['new_sku'])
            ? sanitize_text_field($args['new_sku'])
            : ($product->sku ? $product->sku . '-copy' : null);

        // Check SKU uniqueness
        if ($new_sku) {
            $sku_exists = $wpdb->get_var($wpdb->prepare(
                "SELECT id FROM {$wpdb->prefix}ggmc_products WHERE sku = %s",
                $new_sku
            ));
            if ($sku_exists) {
                $new_sku = $new_sku . '-' . time();
            }
        }

        // Create new product
        $new_product_data = [
            'name' => $new_name,
            'slug' => sanitize_title($new_name),
            'sku' => $new_sku,
            'description' => $product->description,
            'short_description' => $product->short_description,
            'price' => $product->price,
            'sale_price' => $product->sale_price,
            'stock' => $product->stock,
            'stock_status' => $product->stock_status,
            'low_stock_threshold' => $product->low_stock_threshold,
            'restock_date' => $product->restock_date ?? null,
            'weight' => $product->weight,
            'image_id' => $product->image_id,
            'featured' => 0, // Don't copy featured status
            'status' => 'draft', // Set as draft for review
            'created_at' => current_time('mysql'),
            'updated_at' => current_time('mysql')
        ];

        $inserted = $wpdb->insert("{$wpdb->prefix}ggmc_products", $new_product_data);

        if (!$inserted) {
            return [
                'success' => false,
                'error' => 'Failed to duplicate product'
            ];
        }

        $new_product_id = $wpdb->insert_id;

        // Copy categories
        $categories = $wpdb->get_col($wpdb->prepare(
            "SELECT category_id FROM {$wpdb->prefix}ggmc_product_categories WHERE product_id = %d",
            $product_id
        ));
        if (!empty($categories)) {
            $this->assign_categories_to_product($new_product_id, $categories);
        }

        // Copy tags
        $tags = $wpdb->get_col($wpdb->prepare(
            "SELECT tag_id FROM {$wpdb->prefix}ggmc_product_tags WHERE product_id = %d",
            $product_id
        ));
        if (!empty($tags)) {
            $this->assign_tags_to_product($new_product_id, $tags);
        }

        return [
            'success' => true,
            'message' => 'Product duplicated successfully',
            'original_product_id' => $product_id,
            'original_product_name' => $product->name,
            'new_product_id' => $new_product_id,
            'new_product' => $this->get_product_summary($new_product_id)
        ];
    }

    /**
     * Helper: Assign categories to a product
     */
    private function assign_categories_to_product($product_id, $category_ids, $append = false) {
        global $wpdb;

        // Get existing categories if appending
        $existing = [];
        if ($append) {
            $existing = $wpdb->get_col($wpdb->prepare(
                "SELECT category_id FROM {$wpdb->prefix}ggmc_product_categories WHERE product_id = %d",
                $product_id
            ));
        }

        $assigned = 0;
        foreach ($category_ids as $category_id) {
            // Skip if already exists
            if (in_array($category_id, $existing)) {
                continue;
            }

            $inserted = $wpdb->insert(
                "{$wpdb->prefix}ggmc_product_categories",
                [
                    'product_id' => $product_id,
                    'category_id' => $category_id
                ]
            );
            if ($inserted) {
                $assigned++;
            }
        }

        return $assigned;
    }

    /**
     * Helper: Assign tags to a product
     */
    private function assign_tags_to_product($product_id, $tag_ids, $append = false) {
        global $wpdb;

        // Get existing tags if appending
        $existing = [];
        if ($append) {
            $existing = $wpdb->get_col($wpdb->prepare(
                "SELECT tag_id FROM {$wpdb->prefix}ggmc_product_tags WHERE product_id = %d",
                $product_id
            ));
        }

        $assigned = 0;
        foreach ($tag_ids as $tag_id) {
            // Skip if already exists
            if (in_array($tag_id, $existing)) {
                continue;
            }

            $inserted = $wpdb->insert(
                "{$wpdb->prefix}ggmc_product_tags",
                [
                    'product_id' => $product_id,
                    'tag_id' => $tag_id
                ]
            );
            if ($inserted) {
                $assigned++;
            }
        }

        return $assigned;
    }

    /**
     * Helper: Get product summary
     */
    private function get_product_summary($product_id) {
        global $wpdb;

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

        if (!$product) {
            return null;
        }

        // Get categories
        $categories = $wpdb->get_results($wpdb->prepare(
            "SELECT c.id, 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",
            $product_id
        ));

        // Get tags
        $tags = $wpdb->get_results($wpdb->prepare(
            "SELECT t.id, 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",
            $product_id
        ));

        return [
            'id' => (int) $product->id,
            'name' => $product->name,
            'sku' => $product->sku,
            'price' => (float) $product->price,
            'sale_price' => $product->sale_price ? (float) $product->sale_price : null,
            'stock' => (int) $product->stock,
            'stock_status' => $product->stock_status,
            'restock_date' => $product->restock_date ?? null,
            'featured' => (bool) $product->featured,
            'status' => $product->status,
            'categories' => $categories,
            'tags' => $tags
        ];
    }
}
