<?php
/**
 * WooCommerce REST API Endpoint
 *
 * Handles WooCommerce-related API requests.
 *
 * @package BoostBot
 * @since 1.0.0
 */

if ( ! defined( 'ABSPATH' ) ) {
    exit;
}

/**
 * Class BoostBot_WooCommerce_Endpoint
 */
class BoostBot_WooCommerce_Endpoint extends BoostBot_Base_Endpoint {

    /**
     * WooCommerce service instance.
     *
     * @var BoostBot_WooCommerce_Service
     */
    private $wc_service;

    /**
     * Product recommender instance.
     *
     * @var BoostBot_Product_Recommender
     */
    private $recommender;

    /**
     * Order service instance.
     *
     * @var BoostBot_Order_Service
     */
    private $order_service;

    /**
     * Preset model instance.
     *
     * @var BoostBot_WooCommerce_Preset_Model
     */
    private $preset_model;

    /**
     * Coupon model instance.
     *
     * @var BoostBot_WooCommerce_Coupon_Model
     */
    private $coupon_model;

    /**
     * Coupon selector service.
     *
     * @var BoostBot_Coupon_Selector
     */
    private $coupon_selector;

    /**
     * Constructor.
     */
    public function __construct() {
        $this->wc_service      = new BoostBot_WooCommerce_Service();
        $this->recommender     = new BoostBot_Product_Recommender();
        $this->order_service   = new BoostBot_Order_Service();
        $this->preset_model    = new BoostBot_WooCommerce_Preset_Model();
        $this->coupon_model    = new BoostBot_WooCommerce_Coupon_Model();
        $this->coupon_selector = new BoostBot_Coupon_Selector();
    }

    /**
     * Register routes.
     */
    public function register(): void {
        // Check if WooCommerce is active
        register_rest_route(
            $this->namespace,
            '/woocommerce/status',
            array(
                'methods'             => 'GET',
                'callback'            => array( $this, 'get_status' ),
                'permission_callback' => array( $this, 'public_with_api_rate_limit' ),
            )
        );

        // Search products
        register_rest_route(
            $this->namespace,
            '/woocommerce/products/search',
            array(
                'methods'             => 'GET',
                'callback'            => array( $this, 'search_products' ),
                'permission_callback' => array( $this, 'public_with_api_rate_limit' ),
                'args'                => array(
                    'q'        => array(
                        'required'          => true,
                        'sanitize_callback' => 'sanitize_text_field',
                    ),
                    'category' => array(
                        'sanitize_callback' => 'sanitize_text_field',
                    ),
                    'limit'    => array(
                        'default'           => 5,
                        'sanitize_callback' => 'absint',
                    ),
                ),
            )
        );

        // Get single product
        register_rest_route(
            $this->namespace,
            '/woocommerce/products/(?P<id>\d+)',
            array(
                'methods'             => 'GET',
                'callback'            => array( $this, 'get_product' ),
                'permission_callback' => array( $this, 'public_with_api_rate_limit' ),
            )
        );

        // Get featured products
        register_rest_route(
            $this->namespace,
            '/woocommerce/products/featured',
            array(
                'methods'             => 'GET',
                'callback'            => array( $this, 'get_featured' ),
                'permission_callback' => array( $this, 'public_with_api_rate_limit' ),
                'args'                => array(
                    'limit' => array(
                        'default'           => 5,
                        'sanitize_callback' => 'absint',
                    ),
                ),
            )
        );

        // Get on-sale products
        register_rest_route(
            $this->namespace,
            '/woocommerce/products/sale',
            array(
                'methods'             => 'GET',
                'callback'            => array( $this, 'get_on_sale' ),
                'permission_callback' => array( $this, 'public_with_api_rate_limit' ),
                'args'                => array(
                    'limit' => array(
                        'default'           => 5,
                        'sanitize_callback' => 'absint',
                    ),
                ),
            )
        );

        // Get recommendations
        register_rest_route(
            $this->namespace,
            '/woocommerce/recommendations',
            array(
                'methods'             => 'POST',
                'callback'            => array( $this, 'get_recommendations' ),
                'permission_callback' => array( $this, 'public_with_api_rate_limit' ),
            )
        );

        // Get categories
        register_rest_route(
            $this->namespace,
            '/woocommerce/categories',
            array(
                'methods'             => 'GET',
                'callback'            => array( $this, 'get_categories' ),
                'permission_callback' => array( $this, 'public_with_api_rate_limit' ),
            )
        );

        // Add to cart
        register_rest_route(
            $this->namespace,
            '/woocommerce/cart/add',
            array(
                'methods'             => 'POST',
                'callback'            => array( $this, 'add_to_cart' ),
                'permission_callback' => array( $this, 'public_with_commerce_rate_limit' ),
                'args'                => array(
                    'product_id' => array(
                        'required'          => true,
                        'sanitize_callback' => 'absint',
                    ),
                    'quantity'   => array(
                        'default'           => 1,
                        'sanitize_callback' => 'absint',
                    ),
                    'variation_id' => array(
                        'default'           => 0,
                        'sanitize_callback' => 'absint',
                    ),
                    'variation' => array(
                        'default' => array(),
                    ),
                    'language' => array(
                        'sanitize_callback' => 'sanitize_text_field',
                    ),
                ),
            )
        );

        // Get cart info
        register_rest_route(
            $this->namespace,
            '/woocommerce/cart',
            array(
                'methods'             => 'GET',
                'callback'            => array( $this, 'get_cart' ),
                'permission_callback' => array( $this, 'public_with_api_rate_limit' ),
                'args'                => array(
                    'language' => array(
                        'sanitize_callback' => 'sanitize_text_field',
                    ),
                ),
            )
        );

        // Update cart item
        register_rest_route(
            $this->namespace,
            '/woocommerce/cart/update-item',
            array(
                'methods'             => 'POST',
                'callback'            => array( $this, 'update_cart_item' ),
                'permission_callback' => array( $this, 'public_with_commerce_rate_limit' ),
                'args'                => array(
                    'item_key' => array(
                        'required'          => true,
                        'sanitize_callback' => 'sanitize_text_field',
                    ),
                    'quantity' => array(
                        'required'          => true,
                        'sanitize_callback' => static function ( $value ) {
                            return intval( $value );
                        },
                    ),
                    'language' => array(
                        'sanitize_callback' => 'sanitize_text_field',
                    ),
                ),
            )
        );

        // Remove cart item
        register_rest_route(
            $this->namespace,
            '/woocommerce/cart/remove-item',
            array(
                'methods'             => 'POST',
                'callback'            => array( $this, 'remove_cart_item' ),
                'permission_callback' => array( $this, 'public_with_commerce_rate_limit' ),
                'args'                => array(
                    'item_key' => array(
                        'required'          => true,
                        'sanitize_callback' => 'sanitize_text_field',
                    ),
                    'language' => array(
                        'sanitize_callback' => 'sanitize_text_field',
                    ),
                ),
            )
        );

        // Apply coupon to cart
        register_rest_route(
            $this->namespace,
            '/woocommerce/cart/apply-coupon',
            array(
                'methods'             => 'POST',
                'callback'            => array( $this, 'apply_coupon' ),
                'permission_callback' => array( $this, 'public_with_commerce_rate_limit' ),
                'args'                => array(
                    'code' => array(
                        'required'          => true,
                        'sanitize_callback' => 'sanitize_text_field',
                    ),
                    'language' => array(
                        'sanitize_callback' => 'sanitize_text_field',
                    ),
                ),
            )
        );

        // Remove coupon from cart
        register_rest_route(
            $this->namespace,
            '/woocommerce/cart/remove-coupon',
            array(
                'methods'             => 'POST',
                'callback'            => array( $this, 'remove_coupon' ),
                'permission_callback' => array( $this, 'public_with_commerce_rate_limit' ),
                'args'                => array(
                    'code' => array(
                        'required'          => true,
                        'sanitize_callback' => 'sanitize_text_field',
                    ),
                    'language' => array(
                        'sanitize_callback' => 'sanitize_text_field',
                    ),
                ),
            )
        );

        // Get order status
        register_rest_route(
            $this->namespace,
            '/woocommerce/orders/(?P<id>\d+)',
            array(
                'methods'             => 'GET',
                'callback'            => array( $this, 'get_order_status' ),
                'permission_callback' => array( $this, 'public_with_api_rate_limit' ),
                'args'                => array(
                    'email' => array(
                        'sanitize_callback' => 'sanitize_email',
                    ),
                ),
            )
        );

        // ========================================
        // WooCommerce Presets Routes (Week 25)
        // ========================================

        // List all presets
        register_rest_route(
            $this->namespace,
            '/woocommerce/presets',
            array(
                'methods'             => 'GET',
                'callback'            => array( $this, 'get_presets' ),
                'permission_callback' => array( $this, 'check_admin_permission' ),
            )
        );

        // Create new preset
        register_rest_route(
            $this->namespace,
            '/woocommerce/presets',
            array(
                'methods'             => 'POST',
                'callback'            => array( $this, 'create_preset' ),
                'permission_callback' => array( $this, 'check_admin_permission' ),
            )
        );

        // Get single preset
        register_rest_route(
            $this->namespace,
            '/woocommerce/presets/(?P<id>\d+)',
            array(
                'methods'             => 'GET',
                'callback'            => array( $this, 'get_preset' ),
                'permission_callback' => array( $this, 'check_admin_permission' ),
            )
        );

        // Update preset
        register_rest_route(
            $this->namespace,
            '/woocommerce/presets/(?P<id>\d+)',
            array(
                'methods'             => 'PUT',
                'callback'            => array( $this, 'update_preset' ),
                'permission_callback' => array( $this, 'check_admin_permission' ),
            )
        );

        // Delete preset
        register_rest_route(
            $this->namespace,
            '/woocommerce/presets/(?P<id>\d+)',
            array(
                'methods'             => 'DELETE',
                'callback'            => array( $this, 'delete_preset' ),
                'permission_callback' => array( $this, 'check_admin_permission' ),
            )
        );

        // Duplicate preset
        register_rest_route(
            $this->namespace,
            '/woocommerce/presets/(?P<id>\d+)/duplicate',
            array(
                'methods'             => 'POST',
                'callback'            => array( $this, 'duplicate_preset' ),
                'permission_callback' => array( $this, 'check_admin_permission' ),
            )
        );

        // ========================================
        // WooCommerce Coupons Routes (Week 25)
        // ========================================

        // Get coupons for a preset
        register_rest_route(
            $this->namespace,
            '/woocommerce/presets/(?P<preset_id>\d+)/coupons',
            array(
                'methods'             => 'GET',
                'callback'            => array( $this, 'get_preset_coupons' ),
                'permission_callback' => array( $this, 'check_admin_permission' ),
            )
        );

        // Add coupon to preset
        register_rest_route(
            $this->namespace,
            '/woocommerce/presets/(?P<preset_id>\d+)/coupons',
            array(
                'methods'             => 'POST',
                'callback'            => array( $this, 'add_coupon_to_preset' ),
                'permission_callback' => array( $this, 'check_admin_permission' ),
            )
        );

        // Update coupon in preset
        register_rest_route(
            $this->namespace,
            '/woocommerce/coupons/(?P<id>\d+)',
            array(
                'methods'             => 'PUT',
                'callback'            => array( $this, 'update_coupon' ),
                'permission_callback' => array( $this, 'check_admin_permission' ),
            )
        );

        // Delete coupon from preset
        register_rest_route(
            $this->namespace,
            '/woocommerce/coupons/(?P<id>\d+)',
            array(
                'methods'             => 'DELETE',
                'callback'            => array( $this, 'delete_coupon' ),
                'permission_callback' => array( $this, 'check_admin_permission' ),
            )
        );

        // Get available WooCommerce coupons (for selection)
        register_rest_route(
            $this->namespace,
            '/woocommerce/available-coupons',
            array(
                'methods'             => 'GET',
                'callback'            => array( $this, 'get_available_wc_coupons' ),
                'permission_callback' => array( $this, 'check_admin_permission' ),
            )
        );

        // Select best coupon for context
        register_rest_route(
            $this->namespace,
            '/woocommerce/select-coupon',
            array(
                'methods'             => 'POST',
                'callback'            => array( $this, 'select_best_coupon' ),
                'permission_callback' => array( $this, 'public_with_commerce_rate_limit' ),
            )
        );

        // Indexed products (knowledge-aware picker)
        register_rest_route(
            $this->namespace,
            '/woocommerce/indexed-products',
            array(
                'methods'             => 'GET',
                'callback'            => array( $this, 'get_indexed_products' ),
                'permission_callback' => array( $this, 'check_admin_permission' ),
                'args'                => array(
                    'q'        => array(
                        'sanitize_callback' => 'sanitize_text_field',
                    ),
                    'language' => array(
                        'sanitize_callback' => 'sanitize_text_field',
                    ),
                    'limit'    => array(
                        'default'           => 50,
                        'sanitize_callback' => 'absint',
                    ),
                ),
            )
        );

        // Indexed categories (only categories containing indexed products)
        register_rest_route(
            $this->namespace,
            '/woocommerce/indexed-categories',
            array(
                'methods'             => 'GET',
                'callback'            => array( $this, 'get_indexed_categories' ),
                'permission_callback' => array( $this, 'check_admin_permission' ),
                'args'                => array(
                    'language' => array(
                        'sanitize_callback' => 'sanitize_text_field',
                    ),
                ),
            )
        );

        // Statistics (Session D2)
        register_rest_route(
            $this->namespace,
            '/woocommerce/stats',
            array(
                'methods'             => 'GET',
                'callback'            => array( $this, 'get_stats' ),
                'permission_callback' => array( $this, 'check_admin_permission' ),
                'args'                => array(
                    'language' => array(
                        'required'          => true,
                        'sanitize_callback' => 'sanitize_text_field',
                    ),
                    'period'   => array(
                        'default'           => '30d',
                        'sanitize_callback' => 'sanitize_text_field',
                    ),
                ),
            )
        );

        // Run migration (Week 25)
        register_rest_route(
            $this->namespace,
            '/woocommerce/migrate',
            array(
                'methods'             => 'POST',
                'callback'            => array( $this, 'run_migration' ),
                'permission_callback' => array( $this, 'check_admin_permission' ),
            )
        );

        // Check migration status (Week 25)
        register_rest_route(
            $this->namespace,
            '/woocommerce/migration-status',
            array(
                'methods'             => 'GET',
                'callback'            => array( $this, 'get_migration_status' ),
                'permission_callback' => array( $this, 'check_admin_permission' ),
            )
        );
    }

    /**
     * Get WooCommerce status.
     *
     * @param WP_REST_Request $request Request object.
     * @return WP_REST_Response
     */
    public function get_status( $request ) {
        $is_active = BoostBot_WooCommerce_Service::is_active();

        $data = array(
            'active'     => $is_active,
            'products'   => $is_active ? wp_count_posts( 'product' )->publish : 0,
            'categories' => $is_active ? wp_count_terms( 'product_cat' ) : 0,
        );

        return $this->success( $data );
    }

    /**
     * Search products.
     *
     * @param WP_REST_Request $request Request object.
     * @return WP_REST_Response
     */
    public function search_products( $request ) {
        if ( ! BoostBot_WooCommerce_Service::is_active() ) {
            return $this->error( __( 'WooCommerce לא פעיל', 'boostbot' ), 400 );
        }

        $language = $this->get_request_language( $request );

        $products = $this->wc_service->search_products(
            $request->get_param( 'q' ),
            array(
                'category' => $request->get_param( 'category' ),
                'limit'    => $request->get_param( 'limit' ),
                'lang'     => $language ?: null,
            )
        );

        return $this->success( array( 'products' => $products ) );
    }

    /**
     * Get single product.
     *
     * @param WP_REST_Request $request Request object.
     * @return WP_REST_Response
     */
    public function get_product( $request ) {
        if ( ! BoostBot_WooCommerce_Service::is_active() ) {
            return $this->error( __( 'WooCommerce לא פעיל', 'boostbot' ), 400 );
        }

        $product = $this->wc_service->get_product( $request->get_param( 'id' ) );

        if ( ! $product ) {
            return $this->error( __( 'מוצר לא נמצא', 'boostbot' ), 404 );
        }

        return $this->success( array( 'product' => $product ) );
    }

    /**
     * Get featured products.
     *
     * @param WP_REST_Request $request Request object.
     * @return WP_REST_Response
     */
    public function get_featured( $request ) {
        if ( ! BoostBot_WooCommerce_Service::is_active() ) {
            return $this->error( __( 'WooCommerce לא פעיל', 'boostbot' ), 400 );
        }

        $language = $this->get_request_language( $request );
        $products = $this->wc_service->get_featured_products( $request->get_param( 'limit' ), $language ?: null );

        return $this->success( array( 'products' => $products ) );
    }

    /**
     * Get on-sale products.
     *
     * @param WP_REST_Request $request Request object.
     * @return WP_REST_Response
     */
    public function get_on_sale( $request ) {
        if ( ! BoostBot_WooCommerce_Service::is_active() ) {
            return $this->error( __( 'WooCommerce לא פעיל', 'boostbot' ), 400 );
        }

        $language = $this->get_request_language( $request );
        $products = $this->wc_service->get_on_sale_products( $request->get_param( 'limit' ), $language ?: null );

        return $this->success( array( 'products' => $products ) );
    }

    /**
     * Get recommendations.
     *
     * @param WP_REST_Request $request Request object.
     * @return WP_REST_Response
     */
    public function get_recommendations( $request ) {
        if ( ! BoostBot_WooCommerce_Service::is_active() ) {
            return $this->error( __( 'WooCommerce לא פעיל', 'boostbot' ), 400 );
        }

        $context = $request->get_json_params();
        $language = $this->get_request_language( $request );
        if ( $language ) {
            $context['lang'] = $language;
        }
        $products = $this->recommender->get_recommendations( $context );

        return $this->success( array( 'products' => $products ) );
    }

    /**
     * Get categories.
     *
     * @param WP_REST_Request $request Request object.
     * @return WP_REST_Response
     */
    public function get_categories( $request ) {
        if ( ! BoostBot_WooCommerce_Service::is_active() ) {
            return $this->error( __( 'WooCommerce לא פעיל', 'boostbot' ), 400 );
        }

        $language = $this->get_request_language( $request );
        $categories = $this->wc_service->get_categories( true, $language ?: null );

        return $this->success( array( 'categories' => $categories ) );
    }

    /**
     * Add to cart.
     *
     * @param WP_REST_Request $request Request object.
     * @return WP_REST_Response
     */
    public function add_to_cart( $request ) {
        if ( ! BoostBot_WooCommerce_Service::is_active() ) {
            return $this->error( __( 'WooCommerce לא פעיל', 'boostbot' ), 400 );
        }

        $language = $this->get_request_language( $request );
        $result = $this->wc_service->add_to_cart(
            (int) $request->get_param( 'product_id' ),
            (int) $request->get_param( 'quantity' ),
            (int) $request->get_param( 'variation_id' ),
            (array) ( $request->get_param( 'variation' ) ?: array() ),
            $language
        );

        if ( ! $result['success'] ) {
            return $this->error( $result['message'], 400 );
        }

        return $this->success( $result );
    }

    /**
     * Get cart info.
     *
     * @param WP_REST_Request $request Request object.
     * @return WP_REST_Response
     */
    public function get_cart( $request ) {
        if ( ! BoostBot_WooCommerce_Service::is_active() ) {
            return $this->error( __( 'WooCommerce לא פעיל', 'boostbot' ), 400 );
        }

        $cart = $this->wc_service->get_cart_info( $this->get_request_language( $request ) );

        return $this->success( array( 'cart' => $cart ) );
    }

    /**
     * Update a single cart item quantity.
     *
     * @param WP_REST_Request $request Request object.
     * @return WP_REST_Response|WP_Error
     */
    public function update_cart_item( $request ) {
        if ( ! BoostBot_WooCommerce_Service::is_active() ) {
            return $this->error( __( 'WooCommerce לא פעיל', 'boostbot' ), 400 );
        }

        $result = $this->wc_service->update_cart_item_quantity(
            (string) $request->get_param( 'item_key' ),
            (int) $request->get_param( 'quantity' ),
            $this->get_request_language( $request )
        );

        if ( ! $result['success'] ) {
            return $this->error( $result['message'], 400 );
        }

        return $this->success( $result );
    }

    /**
     * Remove a single cart item.
     *
     * @param WP_REST_Request $request Request object.
     * @return WP_REST_Response|WP_Error
     */
    public function remove_cart_item( $request ) {
        if ( ! BoostBot_WooCommerce_Service::is_active() ) {
            return $this->error( __( 'WooCommerce לא פעיל', 'boostbot' ), 400 );
        }

        $result = $this->wc_service->remove_cart_item(
            (string) $request->get_param( 'item_key' ),
            $this->get_request_language( $request )
        );

        if ( ! $result['success'] ) {
            return $this->error( $result['message'], 400 );
        }

        return $this->success( $result );
    }

    /**
     * Apply a coupon to the active cart.
     *
     * @param WP_REST_Request $request Request object.
     * @return WP_REST_Response|WP_Error
     */
    public function apply_coupon( $request ) {
        if ( ! BoostBot_WooCommerce_Service::is_active() ) {
            return $this->error( __( 'WooCommerce לא פעיל', 'boostbot' ), 400 );
        }

        $result = $this->wc_service->apply_coupon_to_cart(
            (string) $request->get_param( 'code' ),
            $this->get_request_language( $request )
        );

        if ( ! $result['success'] ) {
            return $this->error( $result['reason'] ?? __( 'Could not apply coupon right now', 'boostbot' ), 400 );
        }

        return $this->success( $result );
    }

    /**
     * Remove a coupon from the active cart.
     *
     * @param WP_REST_Request $request Request object.
     * @return WP_REST_Response|WP_Error
     */
    public function remove_coupon( $request ) {
        if ( ! BoostBot_WooCommerce_Service::is_active() ) {
            return $this->error( __( 'WooCommerce לא פעיל', 'boostbot' ), 400 );
        }

        $result = $this->wc_service->remove_coupon_from_cart(
            (string) $request->get_param( 'code' ),
            $this->get_request_language( $request )
        );

        if ( ! $result['success'] ) {
            return $this->error( $result['reason'] ?? __( 'Could not remove coupon right now', 'boostbot' ), 400 );
        }

        return $this->success( $result );
    }

    /**
     * Get order status.
     *
     * @param WP_REST_Request $request Request object.
     * @return WP_REST_Response
     */
    public function get_order_status( $request ) {
        if ( ! BoostBot_WooCommerce_Service::is_active() ) {
            return $this->error( __( 'WooCommerce לא פעיל', 'boostbot' ), 400 );
        }

        $order = $this->order_service->get_order_status(
            $request->get_param( 'id' ),
            $request->get_param( 'email' )
        );

        if ( ! $order ) {
            return $this->error( __( 'הזמנה לא נמצאה', 'boostbot' ), 404 );
        }

        return $this->success( array( 'order' => $order ) );
    }

    // ========================================
    // Presets Methods (Week 25)
    // ========================================

    /**
     * Check admin permission (legacy alias for admin_access).
     *
     * @param WP_REST_Request|null $request The REST request object.
     * @return bool
     */
    public function check_admin_permission( $request = null ): bool {
        return $this->admin_access( $request );
    }

    /**
     * Get all presets.
     *
     * @param WP_REST_Request $request Request object.
     * @return WP_REST_Response
     */
    public function get_presets( $request ) {
        $language = $this->get_request_language( $request );

        if ( $language && 'all' !== $language ) {
            $presets = $this->preset_model->get_all_for_language( $language );
        } else {
            $presets = $this->preset_model->get_all();
        }

        // Add coupon count and usage stats.
        foreach ( $presets as &$preset ) {
            $coupons             = $this->coupon_model->get_by_preset( $preset->id, $preset->language ?? null );
            $preset->coupon_count = count( $coupons );
            $preset->bot_count    = $this->preset_model->count_linked_bots( $preset->id );
        }

        return $this->success( array( 'presets' => $presets ) );
    }

    /**
     * Get single preset.
     *
     * @param WP_REST_Request $request Request object.
     * @return WP_REST_Response
     */
    public function get_preset( $request ) {
        $preset = $this->preset_model->get_with_coupons( $request->get_param( 'id' ) );

        if ( ! $preset ) {
            return $this->error( __( 'פריסט לא נמצא', 'boostbot' ), 404 );
        }

        return $this->success( array( 'preset' => $preset ) );
    }

    /**
     * Create a new preset.
     *
     * Accepts flat preset fields (matching WooCommercePresetDetail form structure).
     *
     * @param WP_REST_Request $request Request object.
     * @return WP_REST_Response
     */
    public function create_preset( $request ) {
        // Check for Pro license with bundles.
        $license = new BoostBot_License_Manager();
        if ( ! $license->is_pro() || ! $license->has_bundles() ) {
            return $this->error( __( 'נדרש רישיון Pro עם חבילות', 'boostbot' ), 403 );
        }

        $data = $request->get_json_params();

        // Validate required fields.
        if ( empty( $data['name'] ) ) {
            return $this->error( __( 'שם הפריסט הוא שדה חובה', 'boostbot' ), 400 );
        }

        $language = $this->get_request_language( $request );
        if ( ! $language || 'all' === $language ) {
            $language = class_exists( 'BoostBot_Language' ) ? BoostBot_Language::get_primary() : 'he';
        }

        // Use model's sanitize to filter to fillable columns (flat schema).
        $preset_data = $this->preset_model->sanitize( $data );
        $preset_data['language'] = $language;
        $preset_data['is_active'] = isset( $data['is_active'] ) ? (int) $data['is_active'] : 1;

        $lang_check = $this->preset_model->validate_language_on_write( $preset_data );
        if ( is_wp_error( $lang_check ) ) {
            return $this->error( $lang_check->get_error_message(), 400 );
        }

        $id = $this->preset_model->create( $preset_data );

        if ( ! $id ) {
            return $this->error( __( 'שגיאה ביצירת הפריסט', 'boostbot' ), 500 );
        }

        $preset = $this->preset_model->get_with_coupons( $id );

        return $this->success( array( 'preset' => $preset ), 201 );
    }

    /**
     * Update a preset.
     *
     * Accepts flat preset fields (matching WooCommercePresetDetail form structure).
     *
     * @param WP_REST_Request $request Request object.
     * @return WP_REST_Response
     */
    public function update_preset( $request ) {
        $id     = (int) $request->get_param( 'id' );
        $preset = $this->preset_model->get( $id );

        if ( ! $preset ) {
            return $this->error( __( 'פריסט לא נמצא', 'boostbot' ), 404 );
        }

        $data = $request->get_json_params();

        // Use model's sanitize to filter to fillable columns (flat schema).
        $update_data = $this->preset_model->sanitize( $data );

        if ( ! empty( $update_data ) ) {
            $this->preset_model->update( $id, $update_data );
        }

        // Re-sync WooCommerce widgets for all bots linked to this preset.
        if ( class_exists( 'BoostBot_WooCommerce_Widget_Manager' ) ) {
            try {
                global $wpdb;
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $bot_ids = $wpdb->get_col( $wpdb->prepare(
                    // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                    "SELECT id FROM {$wpdb->prefix}boostbot_bots WHERE woocommerce_preset_id = %d",
                    $id
                ) );
                if ( ! empty( $bot_ids ) ) {
                    $manager = new BoostBot_WooCommerce_Widget_Manager();
                    foreach ( $bot_ids as $bid ) {
                        $manager->sync_bot_widgets( (int) $bid, $id );
                    }
                }
            } catch ( \Throwable $e ) {
                if ( class_exists( 'BoostBot_Logger' ) ) {
                    BoostBot_Logger::error( 'WooCommerce widget sync failed on preset update', array(
                        'preset_id' => $id,
                        'error'     => $e->getMessage(),
                    ) );
                }
            }
        }

        $preset = $this->preset_model->get_with_coupons( $id );

        return $this->success( array( 'preset' => $preset ) );
    }

    /**
     * Delete a preset.
     *
     * @param WP_REST_Request $request Request object.
     * @return WP_REST_Response
     */
    public function delete_preset( $request ) {
        $id     = $request->get_param( 'id' );
        $preset = $this->preset_model->get( $id );

        if ( ! $preset ) {
            return $this->error( __( 'פריסט לא נמצא', 'boostbot' ), 404 );
        }

        // Delete all coupons first.
        $this->coupon_model->delete_by_preset( $id );

        // Then delete the preset.
        $this->preset_model->delete( $id );

        return $this->success( array( 'deleted' => true ) );
    }

    /**
     * Duplicate a preset.
     *
     * @param WP_REST_Request $request Request object.
     * @return WP_REST_Response
     */
    public function duplicate_preset( $request ) {
        $id     = $request->get_param( 'id' );
        $preset = $this->preset_model->get( $id );

        if ( ! $preset ) {
            return $this->error( __( 'פריסט לא נמצא', 'boostbot' ), 404 );
        }

        // Create new preset — inherit language from the source.
        $new_data = array(
            'name'             => $preset->name . ' (' . __( 'Copy', 'boostbot' ) . ')',
            'description'      => $preset->description,
            'bundle_settings'  => $preset->bundle_settings,
            'coupon_settings'  => $preset->coupon_settings,
            'display_settings' => $preset->display_settings,
            'is_active'        => 0,
            'language'         => $preset->language ?? 'he',
        );

        $new_id = $this->preset_model->create( $new_data );

        if ( ! $new_id ) {
            return $this->error( __( 'שגיאה בשכפול הפריסט', 'boostbot' ), 500 );
        }

        // Duplicate coupons.
        $coupons = $this->coupon_model->get_by_preset( $id, $preset->language ?? null );
        foreach ( $coupons as $coupon ) {
            $this->coupon_model->create( array(
                'preset_id'             => $new_id,
                'wc_coupon_id'          => $coupon->wc_coupon_id,
                'display_name'          => $coupon->display_name,
                'description'           => $coupon->description,
                'keywords'              => $coupon->keywords,
                'priority'              => $coupon->priority,
                'min_cart_total'        => $coupon->min_cart_total,
                'max_cart_total'        => $coupon->max_cart_total,
                'applicable_categories' => $coupon->applicable_categories,
                'is_active'             => $coupon->is_active,
                'for_widget'            => $coupon->for_widget ?? 1,
                'for_bundle'            => $coupon->for_bundle ?? 1,
                'language'              => $coupon->language ?? ( $preset->language ?? 'he' ),
            ) );
        }

        $new_preset = $this->preset_model->get( $new_id );

        return $this->success( array( 'preset' => $new_preset ), 201 );
    }

    // ========================================
    // Coupons Methods (Week 25)
    // ========================================

    /**
     * Get coupons for a preset.
     *
     * @param WP_REST_Request $request Request object.
     * @return WP_REST_Response
     */
    public function get_preset_coupons( $request ) {
        $preset_id = $request->get_param( 'preset_id' );
        $preset    = $this->preset_model->get( (int) $preset_id );
        $language  = $this->get_request_language( $request );
        if ( ! $language || 'all' === $language ) {
            $language = $preset->language ?? null;
        }

        $coupons = $this->coupon_model->get_by_preset( (int) $preset_id, $language );

        return $this->success( array( 'coupons' => $coupons ) );
    }

    /**
     * Add coupon to preset.
     *
     * @param WP_REST_Request $request Request object.
     * @return WP_REST_Response
     */
    public function add_coupon_to_preset( $request ) {
        $preset_id = $request->get_param( 'preset_id' );
        $data      = $request->get_json_params();

        // Validate preset exists.
        $preset = $this->preset_model->get( $preset_id );
        if ( ! $preset ) {
            return $this->error( __( 'פריסט לא נמצא', 'boostbot' ), 404 );
        }

        // Validate WooCommerce coupon.
        if ( empty( $data['wc_coupon_id'] ) ) {
            return $this->error( __( 'יש לבחור קופון WooCommerce', 'boostbot' ), 400 );
        }

        // Check if coupon already in preset.
        if ( $this->coupon_model->exists_in_preset( $preset_id, (int) $data['wc_coupon_id'] ) ) {
            return $this->error( __( 'קופון זה כבר קיים בפריסט', 'boostbot' ), 400 );
        }

        $language = $this->get_request_language( $request );
        if ( ! $language || 'all' === $language ) {
            $language = $preset->language ?? BoostBot_Settings::get( 'primary_language', 'he' );
        }

        $coupon_data = array(
            'preset_id'             => $preset_id,
            'wc_coupon_id'          => (int) $data['wc_coupon_id'],
            'display_name'          => sanitize_text_field( $data['display_name'] ?? '' ),
            'description'           => sanitize_textarea_field( $data['description'] ?? '' ),
            'keywords'              => sanitize_text_field( $data['keywords'] ?? '' ),
            'priority'              => (int) ( $data['priority'] ?? 0 ),
            'min_cart_total'        => isset( $data['min_cart_total'] ) ? (float) $data['min_cart_total'] : null,
            'max_cart_total'        => isset( $data['max_cart_total'] ) ? (float) $data['max_cart_total'] : null,
            'applicable_categories' => $data['applicable_categories'] ?? array(),
            'is_active'             => 1,
            'for_widget'            => array_key_exists( 'for_widget', $data ) ? (int) ! empty( $data['for_widget'] ) : 1,
            'for_bundle'            => array_key_exists( 'for_bundle', $data ) ? (int) ! empty( $data['for_bundle'] ) : 1,
            'language'              => $language,
        );

        $id = $this->coupon_model->create( $coupon_data );

        if ( ! $id ) {
            return $this->error( __( 'שגיאה בהוספת הקופון', 'boostbot' ), 500 );
        }

        $coupon = $this->coupon_model->get( $id );

        return $this->success( array( 'coupon' => $coupon ), 201 );
    }

    /**
     * Update a coupon.
     *
     * @param WP_REST_Request $request Request object.
     * @return WP_REST_Response
     */
    public function update_coupon( $request ) {
        $id     = $request->get_param( 'id' );
        $coupon = $this->coupon_model->get( $id );

        if ( ! $coupon ) {
            return $this->error( __( 'קופון לא נמצא', 'boostbot' ), 404 );
        }

        $data = $request->get_json_params();

        $update_data = array();

        if ( isset( $data['display_name'] ) ) {
            $update_data['display_name'] = sanitize_text_field( $data['display_name'] );
        }

        if ( isset( $data['description'] ) ) {
            $update_data['description'] = sanitize_textarea_field( $data['description'] );
        }

        if ( isset( $data['keywords'] ) ) {
            $update_data['keywords'] = sanitize_text_field( $data['keywords'] );
        }

        if ( isset( $data['priority'] ) ) {
            $update_data['priority'] = (int) $data['priority'];
        }

        if ( array_key_exists( 'min_cart_total', $data ) ) {
            $update_data['min_cart_total'] = $data['min_cart_total'] !== null ? (float) $data['min_cart_total'] : null;
        }

        if ( array_key_exists( 'max_cart_total', $data ) ) {
            $update_data['max_cart_total'] = $data['max_cart_total'] !== null ? (float) $data['max_cart_total'] : null;
        }

        if ( isset( $data['applicable_categories'] ) ) {
            $update_data['applicable_categories'] = $data['applicable_categories'];
        }

        if ( isset( $data['is_active'] ) ) {
            $update_data['is_active'] = (int) $data['is_active'];
        }

        if ( array_key_exists( 'for_widget', $data ) ) {
            $update_data['for_widget'] = ! empty( $data['for_widget'] ) ? 1 : 0;
        }

        if ( array_key_exists( 'for_bundle', $data ) ) {
            $update_data['for_bundle'] = ! empty( $data['for_bundle'] ) ? 1 : 0;
        }

        if ( ! empty( $update_data ) ) {
            $this->coupon_model->update( $id, $update_data );
        }

        $coupon = $this->coupon_model->get( $id );

        return $this->success( array( 'coupon' => $coupon ) );
    }

    /**
     * Delete a coupon.
     *
     * @param WP_REST_Request $request Request object.
     * @return WP_REST_Response
     */
    public function delete_coupon( $request ) {
        $id     = $request->get_param( 'id' );
        $coupon = $this->coupon_model->get( $id );

        if ( ! $coupon ) {
            return $this->error( __( 'קופון לא נמצא', 'boostbot' ), 404 );
        }

        $this->coupon_model->delete( $id );

        return $this->success( array( 'deleted' => true ) );
    }

    /**
     * Get available WooCommerce coupons for selection.
     *
     * @param WP_REST_Request $request Request object.
     * @return WP_REST_Response
     */
    public function get_available_wc_coupons( $request ) {
        if ( ! BoostBot_WooCommerce_Service::is_active() ) {
            return $this->error( __( 'WooCommerce לא פעיל', 'boostbot' ), 400 );
        }

        $language = $this->get_request_language( $request );
        $args = array(
            'post_type'      => 'shop_coupon',
            'posts_per_page' => 100,
            'post_status'    => 'publish',
        );

        if ( $language && 'all' !== $language ) {
            $args['lang'] = $language;
        }

        $coupons = get_posts( $args );

        if ( $language && 'all' !== $language && function_exists( 'pll_get_post_language' ) ) {
            $coupons = array_values(
                array_filter(
                    $coupons,
                    static function ( $post ) use ( $language ) {
                        return pll_get_post_language( $post->ID ) === $language;
                    }
                )
            );
        }

        $result = array();
        foreach ( $coupons as $post ) {
            $wc_coupon = new WC_Coupon( $post->ID );

            $result[] = array(
                'id'              => $post->ID,
                'code'            => $wc_coupon->get_code(),
                'discount_type'   => $wc_coupon->get_discount_type(),
                'discount_amount' => $wc_coupon->get_amount(),
                'description'     => $post->post_excerpt,
                'expiry_date'     => $wc_coupon->get_date_expires() ? $wc_coupon->get_date_expires()->format( 'Y-m-d' ) : null,
                'usage_count'     => $wc_coupon->get_usage_count(),
                'usage_limit'     => $wc_coupon->get_usage_limit(),
            );
        }

        return $this->success( array( 'coupons' => $result ) );
    }

    /**
     * Select best coupon for context.
     *
     * @param WP_REST_Request $request Request object.
     * @return WP_REST_Response
     */
    public function select_best_coupon( $request ) {
        $data = $request->get_json_params();

        if ( empty( $data['preset_id'] ) ) {
            return $this->error( __( 'חסר preset_id', 'boostbot' ), 400 );
        }

        $cart_total   = (float) ( $data['cart_total'] ?? 0 );
        $conversation = sanitize_textarea_field( $data['conversation'] ?? '' );
        $language     = $this->get_request_language( $request );

        $best_coupon = $this->coupon_selector->get_best_for_preset(
            (int) $data['preset_id'],
            $cart_total,
            $conversation,
            $language
        );

        return $this->success( array( 'coupon' => $best_coupon ) );
    }

    /**
     * Run WooCommerce migration.
     *
     * @param WP_REST_Request $request Request object.
     * @return WP_REST_Response
     */
    public function run_migration( $request ) {
        $migration = new BoostBot_WooCommerce_Migration();

        if ( ! $migration->needs_migration() ) {
            return $this->success( array(
                'message' => __( 'המיגרציה כבר הושלמה', 'boostbot' ),
                'already_complete' => true,
            ) );
        }

        $results = $migration->run();

        return $this->success( array(
            'message' => __( 'המיגרציה הושלמה בהצלחה', 'boostbot' ),
            'results' => $results,
        ) );
    }

    /**
     * Get migration status.
     *
     * @param WP_REST_Request $request Request object.
     * @return WP_REST_Response
     */
    public function get_migration_status( $request ) {
        $migration = new BoostBot_WooCommerce_Migration();

        return $this->success( array(
            'needs_migration' => $migration->needs_migration(),
            'migration_date'  => get_option( 'boostbot_woocommerce_migration_date', null ),
        ) );
    }

    // ========================================
    // Indexed Products & Categories (Session R3)
    // ========================================

    /**
     * Get indexed products (from knowledge base) in a picker-friendly format.
     *
     * @param WP_REST_Request $request Request object.
     * @return WP_REST_Response
     */
    public function get_indexed_products( $request ) {
        if ( ! BoostBot_WooCommerce_Service::is_active() ) {
            return $this->error( __( 'WooCommerce לא פעיל', 'boostbot' ), 400 );
        }

        global $wpdb;

        $query    = sanitize_text_field( $request->get_param( 'q' ) ?? '' );
        $language = $this->normalize_index_language( $this->get_request_language( $request ) );
        $limit    = absint( $request->get_param( 'limit' ) ?? 50 );

        $index_table = self::sanitize_identifier( $wpdb->prefix . 'boostbot_knowledge_index' );

        $where  = 'ki.content_type = %s AND ki.is_enabled = 1';
        $params = array( 'product' );

        if ( $language && 'all' !== $language ) {
            $where   .= ' AND ki.language = %s';
            $params[] = $language;
        }

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $index_table sanitized via sanitize_identifier(); $where has dynamic %s placeholders; values bind via prepare().
        $indexed_ids = $wpdb->get_col( $wpdb->prepare(
            "SELECT content_id FROM {$index_table} ki WHERE {$where}",
            ...$params
        ) );
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter

        if ( empty( $indexed_ids ) ) {
            return $this->success( array( 'products' => array(), 'total' => 0 ) );
        }

        $args = array(
            'include' => array_map( 'absint', $indexed_ids ),
            'limit'   => -1,
            'status'  => 'publish',
        );

        if ( $language && 'all' !== $language ) {
            $args['lang'] = $language;
        }

        $products = wc_get_products( $args );

        if ( $query ) {
            $products = array_filter( $products, function ( $p ) use ( $query ) {
                return stripos( $p->get_name(), $query ) !== false;
            } );
        }

        $total_products = count( $products );
        $products = array_slice( array_values( $products ), 0, $limit );

        $result = array();
        foreach ( $products as $product ) {
            $cats = wp_get_post_terms( $product->get_id(), 'product_cat', array( 'fields' => 'all' ) );
            $result[] = array(
                'id'              => $product->get_id(),
                'name'            => $product->get_name(),
                'price'           => $product->get_price(),
                'formatted_price' => wc_price( $product->get_price() ),
                'image'           => wp_get_attachment_url( $product->get_image_id() ),
                'categories'      => array_map( function ( $cat ) {
                    return array( 'id' => $cat->term_id, 'name' => $cat->name );
                }, is_array( $cats ) ? $cats : array() ),
                'indexed'         => true,
            );
        }

        return $this->success( array(
            'products' => $result,
            'total'    => $total_products,
        ) );
    }

    /**
     * Get categories that contain at least one indexed product.
     *
     * @param WP_REST_Request $request Request object.
     * @return WP_REST_Response
     */
    public function get_indexed_categories( $request ) {
        if ( ! BoostBot_WooCommerce_Service::is_active() ) {
            return $this->error( __( 'WooCommerce לא פעיל', 'boostbot' ), 400 );
        }

        global $wpdb;

        $language    = $this->normalize_index_language( $this->get_request_language( $request ) );
        $index_table = self::sanitize_identifier( $wpdb->prefix . 'boostbot_knowledge_index' );

        $lang_clause = '';
        $params      = array();

        if ( $language && 'all' !== $language ) {
            $lang_clause = 'AND ki.language = %s';
            $params[]    = $language;
        }

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "
            SELECT t.term_id, t.name, t.slug, COUNT(DISTINCT ki.content_id) AS indexed_count
            FROM {$index_table} ki
            JOIN {$wpdb->prefix}term_relationships tr ON tr.object_id = ki.content_id
            JOIN {$wpdb->prefix}term_taxonomy tt ON tt.term_taxonomy_id = tr.term_taxonomy_id AND tt.taxonomy = 'product_cat'
            JOIN {$wpdb->prefix}terms t ON t.term_id = tt.term_id
            WHERE ki.content_type = 'product' AND ki.is_enabled = 1 {$lang_clause}
            GROUP BY t.term_id, t.name, t.slug
            ORDER BY t.name ASC
        ";

        if ( ! empty( $params ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $index_table sanitized via sanitize_identifier(); values bind via prepare().
            $categories = $wpdb->get_results( $wpdb->prepare( $sql, ...$params ) );
        } else {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $index_table sanitized via sanitize_identifier(); no user input.
            $categories = $wpdb->get_results( $sql );
        }

        $result = array_map( function ( $cat ) {
            return array(
                'id'            => (int) $cat->term_id,
                'name'          => $cat->name,
                'slug'          => $cat->slug,
                'count'         => (int) $cat->indexed_count,
                'indexed_count' => (int) $cat->indexed_count,
            );
        }, $categories ?: array() );

        return $this->success( array( 'categories' => $result ) );
    }

    /**
     * Normalize REST language values for indexed-product/category lookups.
     *
     * Admin/UI contexts sometimes pass full locale strings (en_US, he_IL).
     * The index stores short language slugs (en/he), so normalize before SQL.
     *
     * @param mixed $language Raw language value.
     * @return string
     */
    private function normalize_index_language( $language ): string {
        if ( ! is_string( $language ) ) {
            return '';
        }

        $language = sanitize_text_field( trim( $language ) );
        if ( '' === $language || 'all' === $language ) {
            return $language;
        }

        $normalized = preg_split( '/[-_]/', $language );
        $base       = $normalized[0] ?? $language;

        return strtolower( $base );
    }

    // ========================================
    // Statistics (Session D2)
    // ========================================

    /**
     * Get WooCommerce statistics.
     *
     * @param WP_REST_Request $request Request object.
     * @return WP_REST_Response
     */
    public function get_stats( $request ) {
        global $wpdb;

        $language = $this->get_request_language( $request );
        if ( ! $language || 'all' === $language ) {
            $language = class_exists( 'BoostBot_Language' ) ? BoostBot_Language::get_primary() : 'he';
        }

        $period    = $request->get_param( 'period' ) ?: '30d';
        $since     = $this->resolve_period_start( $period );
        $bundles_t = $wpdb->prefix . 'boostbot_bundles';
        $wu_t      = $wpdb->prefix . 'boostbot_widget_usage';
        $w_t       = $wpdb->prefix . 'boostbot_widgets';

        $bundles      = $this->get_bundle_stats( $bundles_t, $language, $since );
        $triggers     = $this->get_widget_trigger_stats( $wu_t, $w_t, $language );
        $top_products = $this->get_top_bundle_products( $bundles_t, $language );
        $wc_attr      = $this->get_wc_attribution( $wpdb );

        return $this->success( array(
            'bundles'         => $bundles,
            'widget_triggers' => $triggers,
            'top_products'    => $top_products,
            'wc_attribution'  => $wc_attr,
        ) );
    }

    /**
     * Convert period string to a datetime string.
     *
     * @param string $period Period identifier (7d, 30d, 90d, all).
     * @return string|null Y-m-d H:i:s or null for 'all'.
     */
    private function resolve_period_start( string $period ): ?string {
        $map = array(
            '7d'  => 7,
            '30d' => 30,
            '90d' => 90,
        );

        if ( ! isset( $map[ $period ] ) ) {
            return null;
        }

        return gmdate( 'Y-m-d H:i:s', strtotime( '-' . $map[ $period ] . ' days' ) );
    }

    /**
     * Bundle counts and conversion metrics.
     *
     * @param string      $table   Table name.
     * @param string      $lang    Language code.
     * @param string|null $since   Period start datetime or null.
     * @return array
     */
    private function get_bundle_stats( string $table, string $lang, ?string $since ): array {
        global $wpdb;

        $table = self::sanitize_identifier( $table );

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table sanitized via sanitize_identifier(); value bound via prepare().
        $table_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) );
        if ( ! $table_exists ) {
            return array(
                'total'           => 0,
                'suggested'       => 0,
                'accepted'        => 0,
                'conversion_rate' => 0,
                'avg_discount'    => 0,
                'period_total'    => 0,
                'period_accepted' => 0,
            );
        }

        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table sanitized via sanitize_identifier(); $where has %s placeholders; values bind via prepare().
        $totals = $wpdb->get_row( $wpdb->prepare(
            "SELECT
                COUNT(*) AS total,
                SUM( CASE WHEN status = 'suggested' THEN 1 ELSE 0 END ) AS suggested,
                SUM( CASE WHEN status = 'accepted' THEN 1 ELSE 0 END ) AS accepted,
                AVG( discount_amount ) AS avg_discount
            FROM {$table}
            WHERE language = %s",
            $lang
        ) );

        $total    = (int) ( $totals->total ?? 0 );
        $suggested = (int) ( $totals->suggested ?? 0 );
        $accepted  = (int) ( $totals->accepted ?? 0 );
        $conv_rate = $total > 0 ? round( ( $accepted / $total ) * 100, 1 ) : 0;

        $period_total    = 0;
        $period_accepted = 0;

        if ( $since ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table sanitized via sanitize_identifier(); values bind via prepare().
            $period = $wpdb->get_row( $wpdb->prepare(
                "SELECT
                    COUNT(*) AS period_total,
                    SUM( CASE WHEN status = 'accepted' THEN 1 ELSE 0 END ) AS period_accepted
                FROM {$table}
                WHERE language = %s AND created_at >= %s",
                $lang,
                $since
            ) );
            $period_total    = (int) ( $period->period_total ?? 0 );
            $period_accepted = (int) ( $period->period_accepted ?? 0 );
        } else {
            $period_total    = $total;
            $period_accepted = $accepted;
        }
        // phpcs:enable

        return array(
            'total'           => $total,
            'suggested'       => $suggested,
            'accepted'        => $accepted,
            'conversion_rate' => $conv_rate,
            'avg_discount'    => round( (float) ( $totals->avg_discount ?? 0 ), 2 ),
            'period_total'    => $period_total,
            'period_accepted' => $period_accepted,
        );
    }

    /**
     * Widget trigger counts grouped by widget type.
     *
     * @param string $wu_table Widget usage table.
     * @param string $w_table  Widgets table.
     * @param string $lang     Language code.
     * @return array
     */
    private function get_widget_trigger_stats( string $wu_table, string $w_table, string $lang ): array {
        global $wpdb;

        $wu_table = self::sanitize_identifier( $wu_table );
        $w_table  = self::sanitize_identifier( $w_table );

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $wu_table sanitized via sanitize_identifier(); value bound via prepare().
        $wu_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wu_table ) );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $w_table sanitized via sanitize_identifier(); value bound via prepare().
        $w_exists  = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $w_table ) );

        if ( ! $wu_exists || ! $w_exists ) {
            return array(
                'bundle_builder' => 0,
                'coupon'         => 0,
                'product_card'   => 0,
            );
        }

        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $wu_table, $w_table sanitized via sanitize_identifier(); value bound via prepare().
        $rows = $wpdb->get_results( $wpdb->prepare(
            "SELECT w.type, SUM( wu.usage_count ) AS total_triggers
            FROM {$wu_table} wu
            JOIN {$w_table} w ON wu.widget_id = w.id
            WHERE wu.language = %s
              AND w.type IN ( 'bundle_builder', 'coupon', 'product_card' )
            GROUP BY w.type",
            $lang
        ) );
        // phpcs:enable

        $result = array(
            'bundle_builder' => 0,
            'coupon'         => 0,
            'product_card'   => 0,
        );

        foreach ( $rows as $row ) {
            if ( isset( $result[ $row->type ] ) ) {
                $result[ $row->type ] = (int) $row->total_triggers;
            }
        }

        return $result;
    }

    /**
     * Top products appearing in bundles.
     *
     * @param string $table Bundles table name.
     * @param string $lang  Language code.
     * @return array
     */
    private function get_top_bundle_products( string $table, string $lang ): array {
        global $wpdb;

        $table = self::sanitize_identifier( $table );

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table sanitized via sanitize_identifier(); value bound via prepare().
        $table_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) );
        if ( ! $table_exists ) {
            return array();
        }

        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table sanitized via sanitize_identifier(); value bound via prepare().
        $bundles = $wpdb->get_results( $wpdb->prepare(
            "SELECT products FROM {$table} WHERE language = %s AND products IS NOT NULL",
            $lang
        ) );
        // phpcs:enable

        $product_counts = array();
        foreach ( $bundles as $bundle ) {
            $products = json_decode( $bundle->products, true );
            if ( ! is_array( $products ) ) {
                continue;
            }
            foreach ( $products as $product ) {
                $pid = $product['id'] ?? $product['product_id'] ?? null;
                if ( $pid ) {
                    $pid = (int) $pid;
                    $product_counts[ $pid ] = ( $product_counts[ $pid ] ?? 0 ) + 1;
                }
            }
        }

        arsort( $product_counts );
        $top = array_slice( $product_counts, 0, 5, true );

        $result = array();
        foreach ( $top as $pid => $count ) {
            $name = $pid;
            if ( function_exists( 'wc_get_product' ) ) {
                $wc_product = wc_get_product( $pid );
                if ( $wc_product ) {
                    $name = $wc_product->get_name();
                }
            }
            $result[] = array(
                'product_id'   => $pid,
                'product_name' => $name,
                'appearances'  => $count,
            );
        }

        return $result;
    }

    /**
     * WooCommerce coupon attribution data (future-ready).
     *
     * @param wpdb $wpdb WordPress database instance.
     * @return array
     */
    private function get_wc_attribution( $wpdb ): array {
        $coupon_orders  = 0;
        $coupon_revenue = 0;
        $wc_available   = false;

        // All three table names are derived from $wpdb->prefix plus a
        // hard-coded literal, but we still sanitize each identifier to the
        // allow-list before it is interpolated into a query.
        $lookup_table      = self::sanitize_identifier( $wpdb->prefix . 'wc_order_coupon_lookup' );
        $bb_coupons_table  = self::sanitize_identifier( $wpdb->prefix . 'boostbot_woocommerce_coupons' );
        $stats_table       = self::sanitize_identifier( $wpdb->prefix . 'wc_order_stats' );

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $lookup_table sanitized via sanitize_identifier(); value bound via prepare().
        $lookup_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $lookup_table ) );

        if ( $lookup_exists ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $bb_coupons_table sanitized via sanitize_identifier(); value bound via prepare().
            $bb_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $bb_coupons_table ) );

            if ( $bb_exists ) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $bb_coupons_table sanitized via sanitize_identifier(); no user input.
                $bb_coupon_ids = $wpdb->get_col( "SELECT wc_coupon_id FROM `{$bb_coupons_table}`" );

                if ( ! empty( $bb_coupon_ids ) ) {
                    // Build a positional placeholder list so every coupon id
                    // is passed through $wpdb->prepare() rather than inlined.
                    $coupon_ids  = array_map( 'absint', $bb_coupon_ids );
                    $in_holders  = implode( ', ', array_fill( 0, count( $coupon_ids ), '%d' ) );

                    // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $lookup_table sanitized via sanitize_identifier(); %d placeholder list built from array_fill; values bind via prepare().
                    $coupon_orders = (int) $wpdb->get_var(
                        $wpdb->prepare(
                            "SELECT COUNT( DISTINCT order_id ) FROM `{$lookup_table}` WHERE coupon_id IN ({$in_holders})",
                            $coupon_ids
                        )
                    );
                    // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter

                    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $stats_table sanitized via sanitize_identifier(); value bound via prepare().
                    $stats_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $stats_table ) );

                    if ( $stats_exists ) {
                        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $stats_table, $lookup_table sanitized via sanitize_identifier(); %d placeholder list built from array_fill; values bind via prepare().
                        $coupon_revenue = (float) $wpdb->get_var(
                            $wpdb->prepare(
                                "SELECT COALESCE( SUM( os.net_total ), 0 )
                                FROM `{$stats_table}` os
                                JOIN `{$lookup_table}` ocl ON os.order_id = ocl.order_id
                                WHERE ocl.coupon_id IN ({$in_holders})",
                                $coupon_ids
                            )
                        );
                        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter
                    }

                    $wc_available = true;
                }
            }
        }

        return array(
            'coupon_orders'  => $coupon_orders,
            'coupon_revenue' => $coupon_revenue,
            'available'      => $wc_available,
        );
    }

    /**
     * Restrict a SQL identifier to [A-Za-z0-9_] characters.
     *
     * $wpdb->prepare() does not support placeholders for table/column names
     * on all supported WordPress versions, so anywhere we must interpolate an
     * identifier into a query we first pass it through this allow-list.
     *
     * @param string $identifier Raw identifier.
     * @return string Identifier with disallowed characters removed.
     */
    private static function sanitize_identifier( $identifier ) {
        return preg_replace( '/[^A-Za-z0-9_]/', '', (string) $identifier );
    }
}

