<?php

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

class AIEO_Recommendations_Cache {

    
    private static $table_name = 'aieo_product_recommendations';

    
    private static $object_cache_group = 'aieo_recommendations';

    
    private static $object_cache_available = null;

    
    private static $table_exists_cache = null;

    
    private static $valid_relation_types = [
        'upsells',
        'crosssells',
        'related',
        'complementary',   
        'same_needs',      
        
        
        
        
        
        'bundle_full',
        'bundle_rec_only',
        
        
        
        
        
        'bundle_full_v2',
        'bundle_rec_only_v2',
        
        
        
        
        
        'bundle_full_v3',
        'bundle_rec_only_v3',
        
        
        
        'bundle_full_v4',
        'bundle_rec_only_v4',
        
        
        'bundle_full_v5',
        'bundle_rec_only_v5',
        
        
        
        'bundle_full_v6',
        'bundle_rec_only_v6',
        
        
        
        
        
        'bundle_full_v7',
        'bundle_rec_only_v7',
        
        
        
        'bundle_full_v8',
        'bundle_rec_only_v8',
        
        
        'bundle_full_v9',
        'bundle_rec_only_v9',
        
        
        
        
        'bundle_full_v10',
        'bundle_rec_only_v10',
        
        
        
        
        
        
        
        'semantic_recos_v1',
    ];

    
    private static $default_expiration = 604800;

    
    private static $hooks_initialized = false;

    
    private static $pending_price_invalidations = array();

    
    private static $storm_mode = false;

    
    private static $shutdown_flush_registered = false;

    
    private static $synced_this_request = array();

    
    public static function init_hooks() {
        if ( self::$hooks_initialized ) {
            return;
        }

        
        add_action( 'save_post_product', array( __CLASS__, 'on_product_save' ), 20, 3 );

        
        add_action( 'woocommerce_update_product', array( __CLASS__, 'on_product_update' ), 20, 2 );

        
        add_action( 'woocommerce_process_product_meta', array( __CLASS__, 'on_product_meta_update' ), 20, 2 );

        
        add_action( 'before_delete_post', array( __CLASS__, 'on_product_delete' ), 10, 2 );

        
        add_action( 'wp_trash_post', array( __CLASS__, 'on_product_trash' ), 10, 1 );

        
        
        
        
        
        
        add_action( 'updated_post_meta', array( __CLASS__, 'on_price_meta_write' ), 10, 4 );
        add_action( 'added_post_meta', array( __CLASS__, 'on_price_meta_write' ), 10, 4 );
        add_action( 'deleted_post_meta', array( __CLASS__, 'on_price_meta_write' ), 10, 4 );

        
        
        
        
        add_action( 'wc_after_products_starting_sales', array( __CLASS__, 'on_scheduled_sales' ), 10, 1 );
        add_action( 'wc_after_products_ending_sales', array( __CLASS__, 'on_scheduled_sales' ), 10, 1 );

        
        
        
        add_action( 'woocommerce_update_product_variation', array( __CLASS__, 'on_variation_update' ), 20, 1 );

        
        add_action( 'aieo_cleanup_expired_recommendations', array( __CLASS__, 'delete_expired' ) );

        
        if ( ! wp_next_scheduled( 'aieo_cleanup_expired_recommendations' ) ) {
            wp_schedule_event( time(), 'daily', 'aieo_cleanup_expired_recommendations' );
        }

        
        add_action( 'wp_ajax_aieo_cache_get_stats', array( __CLASS__, 'ajax_get_stats' ) );
        add_action( 'wp_ajax_aieo_cache_clear_all', array( __CLASS__, 'ajax_clear_all' ) );
        add_action( 'wp_ajax_aieo_cache_clear_expired', array( __CLASS__, 'ajax_clear_expired' ) );

        
        add_action( 'admin_menu', array( __CLASS__, 'add_admin_menu' ), 80 );

        self::$hooks_initialized = true;
    }

    
    public static function add_admin_menu() {
        
        
        $hook = add_submenu_page(
            '',                                                     
            __( 'Recommendations Cache', 'ai-eshop-optimizer' ),    
            __( 'Recommendations Cache', 'ai-eshop-optimizer' ),    
            'manage_woocommerce',                                    
            'aieo-recommendations-cache',                            
            array( __CLASS__, 'render_admin_page' )                 
        );
        if ( $hook ) {
            add_action( 'load-' . $hook, static function () {
                $GLOBALS['title'] = __( 'Recommendations Cache', 'ai-eshop-optimizer' );
            } );
        }
    }

    
    public static function render_admin_page() {
        
        if ( class_exists( 'AIEO_Chrome' ) && AIEO_Chrome::on() && method_exists( '\Rooster\Admin\Board', 'page' ) ) {
            \Rooster\Admin\Board::page( array( __CLASS__, 'render_body' ) );
            return;
        }
        self::render_body();
    }

    
    public static function render_body() {
        $stats = self::get_stats();
        $types = array();
        foreach ( (array) $stats['entries_by_type'] as $type => $count ) {
            $types[] = ucfirst( (string) $type ) . ': ' . number_format_i18n( (int) $count );
        }
        echo '<div class="wrap">';
        $head = class_exists( 'AIEO_Chrome' ) ? AIEO_Chrome::page_head( 'aieo-recommendations-cache', __( 'Recommendations Cache', 'ai-eshop-optimizer' ), 'database', 'aieo-recommendations-cache' ) : '';
        echo '' !== $head ? $head : '<h1>' . esc_html__( 'Recommendations Cache', 'ai-eshop-optimizer' ) . '</h1><hr class="wp-header-end">'; 
        $board = class_exists( '\Rooster\Admin\Board' ) && \Rooster\Admin\Board::start( 'aieo-recommendations-cache' );

        list( $open, $close ) = AIEO_DMM_UI::card( array(
            'anchor' => 'rec-cache-stats',
            'icon'   => 'database',
            'title'  => __( 'Cache Statistics', 'ai-eshop-optimizer' ),
            'footer' => \Rooster\Admin\Ui::button( __( 'Clear Expired Entries', 'ai-eshop-optimizer' ), array( 'attrs' => array( 'id' => 'aieo-cache-clear-expired' ) ) )
                . ' ' . \Rooster\Admin\Ui::button( __( 'Clear All Cache', 'ai-eshop-optimizer' ), array( 'attrs' => array( 'id' => 'aieo-cache-clear-all' ) + \Rooster\Admin\States::confirm_attrs( 'aieo-cache-clear-all-confirm' ) ) )
                . ' ' . \Rooster\Admin\States::outcome( '', '', array( 'id' => 'aieo-cache-message' ) ),
        ) );
        echo $open; 
        echo \Rooster\Admin\Ui::facts( array( 
            array(
                'label' => __( 'Object Cache', 'ai-eshop-optimizer' ),
                'value' => $stats['object_cache_enabled'] ? (string) $stats['object_cache_type'] : __( 'Not available', 'ai-eshop-optimizer' ),
                'tone'  => $stats['object_cache_enabled'] ? 'success' : '',
            ),
            array( 'label' => __( 'Total Cached Products', 'ai-eshop-optimizer' ), 'value' => number_format_i18n( (int) $stats['total_entries'] ) ),
            array( 'label' => __( 'Cache Size', 'ai-eshop-optimizer' ), 'value' => (string) $stats['size_formatted'] ),
            array( 'label' => __( 'Expired Entries', 'ai-eshop-optimizer' ), 'value' => number_format_i18n( (int) $stats['expired_entries'] ) ),
            array( 'label' => __( 'Entries by Type', 'ai-eshop-optimizer' ), 'value' => $types ? implode( ' · ', $types ) : __( 'No cached entries', 'ai-eshop-optimizer' ) ),
        ) );
        echo $close; 

        list( $open, $close ) = AIEO_DMM_UI::card( array(
            'anchor'      => 'rec-cache-how',
            'icon'        => 'info',
            'title'       => __( 'How It Works', 'ai-eshop-optimizer' ),
            'description' => __( 'The recommendations cache stores pre-rendered HTML for upsells, cross-sells, and related products. This eliminates expensive database queries during AJAX requests, significantly improving page load performance.', 'ai-eshop-optimizer' ),
        ) );
        echo $open; 
        $plain = static function ( $label ) {
            return rtrim( (string) $label, ': ' );
        };
        echo \Rooster\Admin\Ui::facts( array( 
            array( 'label' => $plain( __( 'Cache Hierarchy:', 'ai-eshop-optimizer' ) ), 'value' => __( 'Object Cache (Redis/Memcached) → Custom DB Table', 'ai-eshop-optimizer' ) ),
            array( 'label' => $plain( __( 'Automatic Invalidation:', 'ai-eshop-optimizer' ) ), 'value' => __( 'Cache is automatically cleared when products are updated or deleted.', 'ai-eshop-optimizer' ) ),
            array( 'label' => $plain( __( 'Expiration:', 'ai-eshop-optimizer' ) ), 'value' => __( 'Cache entries expire after 7 days and are automatically cleaned up.', 'ai-eshop-optimizer' ) ),
            array( 'label' => $plain( __( 'Recently Viewed:', 'ai-eshop-optimizer' ) ), 'value' => __( 'Not cached (per-user/cookie-based, varies by visitor).', 'ai-eshop-optimizer' ) ),
        ) );
        echo $close; 
        if ( $board ) {
            \Rooster\Admin\Board::end();
        }
        
        echo \Rooster\Admin\States::confirm( 
            'aieo-cache-clear-all-confirm',
            'reset',
            __( 'Are you sure you want to clear all cached recommendations?', 'ai-eshop-optimizer' ),
            '<p>' . esc_html__( 'Every cached recommendation is removed; each is built again the next time its product is viewed.', 'ai-eshop-optimizer' ) . '</p>',
            array( 'confirm' => __( 'Clear All Cache', 'ai-eshop-optimizer' ) )
        );
        echo '</div>';
        ?>
        <script>
        jQuery(document).ready(function($) {
            // The answer in the library's outcome line: its words and its tone (data-tone), no colour of the script's own.
            function showMessage(message, tone) {
                var line = document.getElementById('aieo-cache-message');
                if (!line) { return; }
                line.textContent = message || '';
                if (tone) { line.dataset.tone = tone; } else { delete line.dataset.tone; }
            }

            $('#aieo-cache-clear-expired').on('click', function() {
                var $btn = $(this);
                $btn.prop('disabled', true);
                $.post(ajaxurl, {
                    action: 'aieo_cache_clear_expired',
                    _wpnonce: '<?php echo esc_js( wp_create_nonce( 'aieo_cache_admin' ) ); ?>'
                }, function(response) {
                    $btn.prop('disabled', false);
                    if (response.success) {
                        showMessage(response.data.message, 'success');
                        location.reload();
                    } else {
                        showMessage(response.data.message || '<?php echo esc_js( __( 'Error', 'ai-eshop-optimizer' ) ); ?>', 'destructive');
                    }
                });
            });

            // Its click reaches here once the library's dialog is confirmed; the browser asks only without the dialog.
            $('#aieo-cache-clear-all').on('click', function() {
                var asked = this.hasAttribute('data-ra-confirm') && document.getElementById('aieo-cache-clear-all-confirm') && window.roosterAdmin && window.roosterAdmin.states;
                if (!asked && !confirm('<?php echo esc_js( __( 'Are you sure you want to clear all cached recommendations?', 'ai-eshop-optimizer' ) ); ?>')) {
                    return;
                }
                var $btn = $(this);
                $btn.prop('disabled', true);
                $.post(ajaxurl, {
                    action: 'aieo_cache_clear_all',
                    _wpnonce: '<?php echo esc_js( wp_create_nonce( 'aieo_cache_admin' ) ); ?>'
                }, function(response) {
                    $btn.prop('disabled', false);
                    if (response.success) {
                        showMessage(response.data.message, 'success');
                        location.reload();
                    } else {
                        showMessage(response.data.message || '<?php echo esc_js( __( 'Error', 'ai-eshop-optimizer' ) ); ?>', 'destructive');
                    }
                });
            });
        });
        </script>
        <?php
    }

    
    public static function ajax_get_stats() {
        check_ajax_referer( 'aieo_cache_admin', '_wpnonce' );

        if ( ! current_user_can( 'manage_woocommerce' ) ) {
            wp_send_json_error( array( 'message' => __( 'Permission denied', 'ai-eshop-optimizer' ) ) );
        }

        $stats = self::get_stats();
        wp_send_json_success( $stats );
    }

    
    public static function ajax_clear_all() {
        check_ajax_referer( 'aieo_cache_admin', '_wpnonce' );

        if ( ! current_user_can( 'manage_woocommerce' ) ) {
            wp_send_json_error( array( 'message' => __( 'Permission denied', 'ai-eshop-optimizer' ) ) );
        }

        $deleted = self::delete_all();
        wp_send_json_success( array(
            'message' => sprintf(
                
                __( 'Cleared %d cache entries.', 'ai-eshop-optimizer' ),
                $deleted
            ),
            'deleted' => $deleted,
        ) );
    }

    
    public static function ajax_clear_expired() {
        check_ajax_referer( 'aieo_cache_admin', '_wpnonce' );

        if ( ! current_user_can( 'manage_woocommerce' ) ) {
            wp_send_json_error( array( 'message' => __( 'Permission denied', 'ai-eshop-optimizer' ) ) );
        }

        $deleted = self::delete_expired();
        wp_send_json_success( array(
            'message' => sprintf(
                
                __( 'Cleared %d expired entries.', 'ai-eshop-optimizer' ),
                $deleted
            ),
            'deleted' => $deleted,
        ) );
    }

    
    public static function on_product_save( $post_id, $post, $update ) {
        if ( defined( 'DOING_AUTOSAVE' ) && DOING_AUTOSAVE ) {
            return;
        }

        if ( $post->post_type !== 'product' ) {
            return;
        }

        
        
        
        
        
        self::invalidate_referencing( $post_id );

        
        
        self::delete( $post_id );

        self::$synced_this_request[ (int) $post_id ] = true;
    }

    
    public static function on_product_update( $product_id, $product = null ) {
        
        self::invalidate_referencing( $product_id );
        self::delete( $product_id );

        self::$synced_this_request[ (int) $product_id ] = true;
    }

    
    public static function on_product_meta_update( $post_id, $post = null ) {
        
        self::delete( $post_id );
    }

    
    public static function on_product_delete( $post_id, $post = null ) {
        if ( $post && $post->post_type !== 'product' ) {
            return;
        }

        
        self::invalidate_referencing( $post_id );
        self::delete( $post_id );

        self::$synced_this_request[ (int) $post_id ] = true;
    }

    
    public static function on_product_trash( $post_id ) {
        $post = get_post( $post_id );
        if ( ! $post || $post->post_type !== 'product' ) {
            return;
        }

        
        self::invalidate_referencing( $post_id );
        self::delete( $post_id );

        self::$synced_this_request[ (int) $post_id ] = true;
    }

    
    
    

    
    public static function on_price_meta_write( $meta_id, $object_id, $meta_key, $meta_value = null ) {
        static $price_keys = array(
            '_price'                 => 1,
            '_sale_price'            => 1,
            '_regular_price'         => 1,
            '_sale_price_dates_from' => 1,
            '_sale_price_dates_to'   => 1,
        );
        if ( ! isset( $price_keys[ $meta_key ] ) ) {
            return;
        }
        if ( self::$storm_mode ) {
            return; 
        }
        $pid = self::normalize_to_parent_product_id( (int) $object_id );
        if ( $pid > 0 ) {
            self::queue_price_invalidation( $pid );
        }
    }

    
    public static function on_scheduled_sales( $product_ids ) {
        foreach ( (array) $product_ids as $id ) {
            $pid = self::normalize_to_parent_product_id( (int) $id );
            if ( $pid > 0 ) {
                self::queue_price_invalidation( $pid );
            }
        }
    }

    
    public static function on_variation_update( $variation_id ) {
        $pid = self::normalize_to_parent_product_id( (int) $variation_id );
        if ( $pid > 0 ) {
            self::queue_price_invalidation( $pid );
        }
    }

    
    private static function normalize_to_parent_product_id( $object_id ) {
        if ( $object_id <= 0 ) {
            return 0;
        }
        
        $post = get_post( $object_id );
        if ( ! $post ) {
            return 0;
        }
        if ( 'product' === $post->post_type ) {
            return (int) $object_id;
        }
        if ( 'product_variation' === $post->post_type ) {
            return (int) $post->post_parent;
        }
        return 0;
    }

    
    public static function queue_price_invalidation( $product_id ) {
        $product_id = (int) $product_id;
        if ( $product_id <= 0 ) {
            return;
        }

        
        
        unset( self::$synced_this_request[ $product_id ] );

        if ( self::$storm_mode ) {
            return;
        }

        self::$pending_price_invalidations[ $product_id ] = true;

        if ( ! self::$shutdown_flush_registered ) {
            add_action( 'shutdown', array( __CLASS__, 'flush_price_invalidations' ), 5 );
            self::$shutdown_flush_registered = true;
        }

        
        $threshold = (int) apply_filters( 'aieo_reco_cache_storm_threshold', 100 );
        if ( count( self::$pending_price_invalidations ) > $threshold ) {
            self::$storm_mode = true;
            self::$pending_price_invalidations = array(); 
        }
    }

    
    public static function flush_price_invalidations() {
        if ( self::$storm_mode ) {
            self::delete_all();
            self::$storm_mode = false;

            
            do_action( 'aieo_reco_cache_price_storm_flushed' );
            return;
        }

        if ( empty( self::$pending_price_invalidations ) ) {
            return;
        }

        $pids = array_keys( self::$pending_price_invalidations );
        self::$pending_price_invalidations = array();

        $processed = array();
        foreach ( $pids as $pid ) {
            if ( isset( self::$synced_this_request[ $pid ] ) ) {
                continue; 
            }
            
            self::invalidate_referencing( $pid );
            self::delete( $pid );
            $processed[] = $pid;
        }

        if ( ! empty( $processed ) ) {
            
            do_action( 'aieo_reco_cache_price_invalidated', $processed );
        }
    }

    
    public static function get_table_name() {
        global $wpdb;
        return $wpdb->prefix . self::$table_name;
    }

    
    public static function is_object_cache_available() {
        if ( self::$object_cache_available === null ) {
            if ( function_exists( 'wp_using_ext_object_cache' ) ) {
                self::$object_cache_available = wp_using_ext_object_cache();
            } else {
                self::$object_cache_available = file_exists( WP_CONTENT_DIR . '/object-cache.php' );
            }
        }

        return self::$object_cache_available;
    }

    
    private static function get_current_locale() {
        
        if ( defined( 'ICL_LANGUAGE_CODE' ) ) {
            return ICL_LANGUAGE_CODE;
        }

        
        if ( function_exists( 'pll_current_language' ) ) {
            $lang = pll_current_language();
            if ( $lang ) {
                return $lang;
            }
        }

        
        
        return get_locale();
    }

    
    private static function get_cache_key( $product_id, $relation_type ) {
        $locale = self::get_current_locale();
        return 'aieo_rec_' . $product_id . '_' . $relation_type . '_' . $locale;
    }

    
    private static function get_localized_relation_type( $relation_type ) {
        $locale = self::get_current_locale();
        return $relation_type . '_' . $locale;
    }

    
    public static function create_table() {
        global $wpdb;

        $table_name = self::get_table_name();
        $charset_collate = aieo_get_charset_collate();

        if ( self::table_exists() ) {
            return false;
        }

        $sql = "CREATE TABLE {$table_name} (
            id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            product_id bigint(20) UNSIGNED NOT NULL,
            relation_type varchar(50) NOT NULL,
            recommended_products longtext DEFAULT NULL,
            created datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            modified datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            expires_at datetime DEFAULT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY idx_product_relation (product_id, relation_type),
            KEY idx_relation_type (relation_type),
            KEY idx_expires_at (expires_at)
        ) $charset_collate;";

        require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
        dbDelta( $sql );

        self::$table_exists_cache = true;

        return true;
    }

    
    public static function table_exists( $force_check = false ) {
        if ( self::$table_exists_cache !== null && ! $force_check ) {
            return self::$table_exists_cache;
        }

        global $wpdb;
        $table_name = self::get_table_name();
        self::$table_exists_cache = $wpdb->get_var( "SHOW TABLES LIKE '{$table_name}'" ) === $table_name;

        return self::$table_exists_cache;
    }

    
    private static function ensure_table_exists() {
        if ( ! self::table_exists() ) {
            self::create_table();
            return true;
        }
        return false;
    }

    
    private static function is_valid_relation_type( $relation_type ) {
        
        
        
        
        
        
        $relation_type = preg_replace( '/_a[a-z0-9]{2,16}$/', '', $relation_type, 1 );

        if ( in_array( $relation_type, self::$valid_relation_types, true ) ) {
            return true;
        }
        
        
        
        
        
        
        
        
        
        
        
        
        if ( preg_match( '/^(.+)_r[0-9a-f]{6,32}$/', $relation_type, $m )
            && in_array( $m[1], self::$valid_relation_types, true ) ) {
            return true;
        }
        return false;
    }

    
    public static function get( $product_id, $relation_type ) {
        global $wpdb;

        $product_id = absint( $product_id );
        if ( ! $product_id || ! self::is_valid_relation_type( $relation_type ) ) {
            return false;
        }

        $cache_key = self::get_cache_key( $product_id, $relation_type );

        
        if ( self::is_object_cache_available() ) {
            $found = false;
            $value = wp_cache_get( $cache_key, self::$object_cache_group, false, $found );

            if ( $found && $value !== false ) {
                return $value;
            }
        }

        
        $table_name = self::get_table_name();

        if ( ! self::table_exists() ) {
            return false;
        }

        
        $localized_relation_type = self::get_localized_relation_type( $relation_type );

        $row = $wpdb->get_row( $wpdb->prepare(
            "SELECT recommended_products, expires_at FROM {$table_name}
             WHERE product_id = %d AND relation_type = %s",
            $product_id,
            $localized_relation_type
        ) );

        if ( ! $row || empty( $row->recommended_products ) ) {
            return false;
        }

        
        if ( $row->expires_at && strtotime( $row->expires_at ) < time() ) {
            
            self::delete_by_localized_type( $product_id, $localized_relation_type );
            return false;
        }

        $data = json_decode( $row->recommended_products, true );
        if ( ! is_array( $data ) ) {
            return false;
        }

        
        if ( self::is_object_cache_available() ) {
            $remaining_ttl = $row->expires_at ? max( 0, strtotime( $row->expires_at ) - time() ) : 0;
            wp_cache_set( $cache_key, $data, self::$object_cache_group, $remaining_ttl );
        }

        return $data;
    }

    
    public static function set( $product_id, $relation_type, $recommended_products, $expiration = 0 ) {
        global $wpdb;

        $product_id = absint( $product_id );
        if ( ! $product_id || ! self::is_valid_relation_type( $relation_type ) ) {
            return false;
        }

        if ( ! is_array( $recommended_products ) ) {
            return false;
        }

        $cache_key = self::get_cache_key( $product_id, $relation_type );
        $expiration = $expiration > 0 ? $expiration : self::$default_expiration;
        $expires_at = gmdate( 'Y-m-d H:i:s', time() + $expiration );

        
        if ( self::is_object_cache_available() ) {
            wp_cache_set( $cache_key, $recommended_products, self::$object_cache_group, $expiration );
        }

        
        $table_name = self::get_table_name();

        self::ensure_table_exists();

        
        $localized_relation_type = self::get_localized_relation_type( $relation_type );

        $json_data = wp_json_encode( $recommended_products, JSON_UNESCAPED_UNICODE );
        
        
        
        
        
        $current_time = current_time( 'mysql', true );

        
        $sql = $wpdb->prepare(
            "INSERT INTO {$table_name} (product_id, relation_type, recommended_products, created, modified, expires_at)
            VALUES (%d, %s, %s, %s, %s, %s)
            ON DUPLICATE KEY UPDATE
                recommended_products = VALUES(recommended_products),
                modified = VALUES(modified),
                expires_at = VALUES(expires_at)",
            $product_id,
            $localized_relation_type,
            $json_data,
            $current_time,
            $current_time,
            $expires_at
        );

        
        $result = $wpdb->query( $sql );

        
        
        
        
        
        
        
        if ( $result !== false && self::reverse_table_ready() ) {
            $cache_entry_id = (int) $wpdb->get_var( $wpdb->prepare(
                "SELECT id FROM {$table_name} WHERE product_id = %d AND relation_type = %s",
                $product_id,
                $localized_relation_type
            ) );
            if ( $cache_entry_id > 0 ) {
                self::sync_reverse_refs( $cache_entry_id, $recommended_products );
            }
        }

        return $result !== false;
    }

    
    public static function delete( $product_id, $relation_type = null ) {
        global $wpdb;

        $product_id = absint( $product_id );
        if ( ! $product_id ) {
            return false;
        }

        
        if ( self::is_object_cache_available() ) {
            if ( $relation_type !== null ) {
                
                $cache_key = self::get_cache_key( $product_id, $relation_type );
                wp_cache_delete( $cache_key, self::$object_cache_group );
            } else {
                
                
                
                
                
                $locales = self::get_common_locales();
                foreach ( self::$valid_relation_types as $type ) {
                    foreach ( $locales as $locale ) {
                        $cache_key = 'aieo_rec_' . $product_id . '_' . $type . '_' . $locale;
                        wp_cache_delete( $cache_key, self::$object_cache_group );
                    }
                }
            }
        }

        
        $table_name = self::get_table_name();

        if ( ! self::table_exists() ) {
            return false;
        }

        if ( $relation_type !== null ) {
            
            $localized_relation_type = self::get_localized_relation_type( $relation_type );

            
            
            if ( self::reverse_table_ready() ) {
                self::purge_reverse_refs_for_cache_ids( $wpdb->get_col( $wpdb->prepare(
                    "SELECT id FROM {$table_name} WHERE product_id = %d AND relation_type = %s",
                    $product_id,
                    $localized_relation_type
                ) ) );
            }

            $result = $wpdb->delete(
                $table_name,
                [
                    'product_id' => $product_id,
                    'relation_type' => $localized_relation_type,
                ],
                [ '%d', '%s' ]
            );
        } else {
            
            
            
            $rows = $wpdb->get_results( $wpdb->prepare(
                "SELECT id, relation_type FROM {$table_name} WHERE product_id = %d",
                $product_id
            ) );

            if ( ! empty( $rows ) ) {
                if ( self::is_object_cache_available() ) {
                    foreach ( $rows as $row ) {
                        
                        
                        
                        
                        
                        $cache_key = 'aieo_rec_' . $product_id . '_' . $row->relation_type;
                        wp_cache_delete( $cache_key, self::$object_cache_group );
                    }
                }

                
                
                if ( self::reverse_table_ready() ) {
                    self::purge_reverse_refs_for_cache_ids( wp_list_pluck( $rows, 'id' ) );
                }
            }

            $result = $wpdb->delete(
                $table_name,
                [ 'product_id' => $product_id ],
                [ '%d' ]
            );
        }

        return $result !== false;
    }

    
    private static function delete_by_localized_type( $product_id, $localized_relation_type ) {
        global $wpdb;

        $product_id = absint( $product_id );
        if ( ! $product_id ) {
            return false;
        }

        
        if ( self::is_object_cache_available() ) {
            $cache_key = 'aieo_rec_' . $product_id . '_' . $localized_relation_type;
            wp_cache_delete( $cache_key, self::$object_cache_group );
        }

        
        $table_name = self::get_table_name();

        if ( ! self::table_exists() ) {
            return false;
        }

        
        if ( self::reverse_table_ready() ) {
            self::purge_reverse_refs_for_cache_ids( $wpdb->get_col( $wpdb->prepare(
                "SELECT id FROM {$table_name} WHERE product_id = %d AND relation_type = %s",
                $product_id,
                $localized_relation_type
            ) ) );
        }

        $result = $wpdb->delete(
            $table_name,
            [
                'product_id' => $product_id,
                'relation_type' => $localized_relation_type,
            ],
            [ '%d', '%s' ]
        );

        return $result !== false;
    }

    
    private static function get_common_locales() {
        $locales = [ get_locale() ]; 

        
        if ( function_exists( 'icl_get_languages' ) ) {
            $languages = icl_get_languages( 'skip_missing=0' );
            if ( is_array( $languages ) ) {
                foreach ( $languages as $lang ) {
                    $locales[] = $lang['code'];
                }
            }
        }

        
        if ( function_exists( 'pll_languages_list' ) ) {
            $pll_langs = pll_languages_list( [ 'fields' => 'slug' ] );
            if ( is_array( $pll_langs ) ) {
                $locales = array_merge( $locales, $pll_langs );
            }
        }

        return array_unique( $locales );
    }

    
    public static function delete_expired() {
        global $wpdb;

        $table_name = self::get_table_name();

        if ( ! self::table_exists() ) {
            return 0;
        }

        
        
        
        
        
        $cutoff = $wpdb->get_var( "SELECT UTC_TIMESTAMP()" );

        
        if ( self::is_object_cache_available() ) {
            $expired = $wpdb->get_results( $wpdb->prepare(
                "SELECT product_id, relation_type FROM {$table_name}
                 WHERE expires_at IS NOT NULL AND expires_at < %s",
                $cutoff
            ) );

            foreach ( $expired as $row ) {
                
                
                $cache_key = 'aieo_rec_' . $row->product_id . '_' . $row->relation_type;
                wp_cache_delete( $cache_key, self::$object_cache_group );
            }
        }

        
        if ( self::reverse_table_ready() ) {
            self::purge_reverse_refs_for_cache_ids( $wpdb->get_col( $wpdb->prepare(
                "SELECT id FROM {$table_name}
                 WHERE expires_at IS NOT NULL AND expires_at < %s",
                $cutoff
            ) ) );
        }

        $result = $wpdb->query( $wpdb->prepare(
            "DELETE FROM {$table_name}
             WHERE expires_at IS NOT NULL AND expires_at < %s",
            $cutoff
        ) );

        return $result !== false ? $result : 0;
    }

    
    public static function delete_all() {
        global $wpdb;

        $table_name = self::get_table_name();

        
        if ( self::is_object_cache_available() ) {
            if ( function_exists( 'wp_cache_flush_group' ) ) {
                wp_cache_flush_group( self::$object_cache_group );
            } else {
                
                if ( self::table_exists() ) {
                    $entries = $wpdb->get_results(
                        "SELECT product_id, relation_type FROM {$table_name}"
                    );

                    foreach ( $entries as $row ) {
                        
                        
                        $cache_key = 'aieo_rec_' . $row->product_id . '_' . $row->relation_type;
                        wp_cache_delete( $cache_key, self::$object_cache_group );
                    }
                }
            }
        }

        
        if ( ! self::table_exists() ) {
            return 0;
        }

        $result = $wpdb->query( "TRUNCATE TABLE {$table_name}" );

        
        
        if ( self::reverse_table_ready() ) {
            $rev = self::reverse_table_name();
            
            $wpdb->query( "TRUNCATE TABLE {$rev}" );
        }

        return $result !== false ? $result : 0;
    }

    
    public static function flush_all_cache() {
        return self::delete_all();
    }

    
    public static function invalidate_referencing( $product_id ) {
        global $wpdb;

        $product_id = absint( $product_id );
        if ( ! $product_id ) {
            return 0;
        }

        $table_name = self::get_table_name();

        if ( ! self::table_exists() ) {
            return 0;
        }

        
        
        
        
        
        if ( self::reverse_table_ready() ) {
            $rev = self::reverse_table_name();
            $entry_ids = $wpdb->get_col( $wpdb->prepare(
                "SELECT cache_entry_id FROM {$rev} WHERE referenced_product_id = %d",
                $product_id
            ) );

            if ( empty( $entry_ids ) ) {
                return 0;
            }

            $entry_ids = array_values( array_filter( array_map( 'intval', $entry_ids ) ) );
            $ph = implode( ', ', array_fill( 0, count( $entry_ids ), '%d' ) );

            
            
            $referencing = $wpdb->get_results( $wpdb->prepare(
                "SELECT id, product_id, relation_type FROM {$table_name} WHERE id IN ({$ph})",
                ...$entry_ids
            ) );

            
            
            
            $wpdb->query( $wpdb->prepare(
                "DELETE FROM {$rev} WHERE cache_entry_id IN ({$ph})",
                ...$entry_ids
            ) );
        } else {
            
            $search_pattern = '%"product_id":' . $product_id . '%';
            $alt_pattern = '%"product_id": ' . $product_id . '%';

            $referencing = $wpdb->get_results( $wpdb->prepare(
                "SELECT id, product_id, relation_type FROM {$table_name}
                 WHERE recommended_products LIKE %s OR recommended_products LIKE %s",
                $search_pattern,
                $alt_pattern
            ) );
        }

        if ( empty( $referencing ) ) {
            return 0;
        }

        
        if ( self::is_object_cache_available() ) {
            foreach ( $referencing as $row ) {
                
                
                $cache_key = 'aieo_rec_' . $row->product_id . '_' . $row->relation_type;
                wp_cache_delete( $cache_key, self::$object_cache_group );
            }
        }

        
        $ids = array_map( fn( $row ) => $row->id, $referencing );

        $placeholders = implode( ', ', array_fill( 0, count( $ids ), '%d' ) );
        
        $result = $wpdb->query( $wpdb->prepare(
            "DELETE FROM {$table_name} WHERE id IN ({$placeholders})",
            ...$ids
        ) );
        

        return $result !== false ? $result : 0;
    }

    
    
    

    
    private static function reverse_table_name() {
        global $wpdb;
        return $wpdb->prefix . 'aieo_reco_reverse_refs';
    }

    
    private static function reverse_table_ready() {
        static $ready = null;
        if ( $ready !== null ) {
            return $ready;
        }
        if ( get_option( 'aieo_reco_reverse_refs_ready' ) !== '1' ) {
            $ready = false;
            return $ready;
        }
        global $wpdb;
        $t = self::reverse_table_name();
        $ready = ( (string) $wpdb->get_var( $wpdb->prepare(
            "SELECT table_name FROM information_schema.TABLES
             WHERE table_schema = DATABASE() AND table_name = %s",
            $t
        ) ) === $t );
        return $ready;
    }

    
    private static function extract_referenced_ids( $recommended ) {
        $ids = array();
        self::collect_product_ids( $recommended, $ids );
        return array_keys( $ids );
    }

    
    private static function collect_product_ids( $node, array &$ids ) {
        if ( ! is_array( $node ) && ! is_object( $node ) ) {
            return;
        }
        foreach ( (array) $node as $key => $val ) {
            if ( 'product_id' === $key
                && ( is_int( $val ) || ( is_string( $val ) && ctype_digit( $val ) ) ) ) {
                $pid = (int) $val;
                if ( $pid > 0 ) {
                    $ids[ $pid ] = true;
                }
            }
            if ( is_array( $val ) || is_object( $val ) ) {
                self::collect_product_ids( $val, $ids );
            }
        }
    }

    
    private static function write_reverse_refs( $cache_entry_id, array $ids ) {
        global $wpdb;
        $cache_entry_id = (int) $cache_entry_id;
        if ( $cache_entry_id <= 0 ) {
            return;
        }
        $rev = self::reverse_table_name();

        
        $wpdb->query( $wpdb->prepare( "DELETE FROM {$rev} WHERE cache_entry_id = %d", $cache_entry_id ) );

        $values = array();
        $args   = array();
        foreach ( $ids as $pid ) {
            $pid = (int) $pid;
            if ( $pid <= 0 ) {
                continue;
            }
            $values[] = '(%d, %d)';
            $args[]   = $pid;
            $args[]   = $cache_entry_id;
        }
        if ( empty( $values ) ) {
            return;
        }
        
        $wpdb->query( $wpdb->prepare(
            "INSERT IGNORE INTO {$rev} (referenced_product_id, cache_entry_id) VALUES " . implode( ', ', $values ),
            ...$args
        ) );
    }

    
    private static function sync_reverse_refs( $cache_entry_id, $recommended ) {
        if ( ! self::reverse_table_ready() ) {
            return;
        }
        self::write_reverse_refs( $cache_entry_id, self::extract_referenced_ids( $recommended ) );
    }

    
    private static function purge_reverse_refs_for_cache_ids( $cache_ids ) {
        if ( empty( $cache_ids ) || ! self::reverse_table_ready() ) {
            return;
        }
        global $wpdb;
        $rev = self::reverse_table_name();
        $cache_ids = array_values( array_filter( array_unique( array_map( 'intval', (array) $cache_ids ) ) ) );
        if ( empty( $cache_ids ) ) {
            return;
        }
        $ph = implode( ', ', array_fill( 0, count( $cache_ids ), '%d' ) );
        
        $wpdb->query( $wpdb->prepare(
            "DELETE FROM {$rev} WHERE cache_entry_id IN ({$ph})",
            ...$cache_ids
        ) );
    }

    
    public static function backfill_reverse_refs( $start_id = 0, $max_rows = 0, $time_budget_seconds = 0 ) {
        global $wpdb;
        $table_name = self::get_table_name();
        $rev        = self::reverse_table_name();

        $start_id = (int) $start_id;

        
        if ( $wpdb->get_var( "SHOW TABLES LIKE '{$table_name}'" ) !== $table_name
            || $wpdb->get_var( "SHOW TABLES LIKE '{$rev}'" ) !== $rev ) {
            return array( 'last_id' => $start_id, 'done' => true, 'processed' => 0 );
        }

        $last_id    = $start_id;
        $processed  = 0;
        $batch_size = 500;
        $started    = microtime( true );

        do {
            $rows = $wpdb->get_results( $wpdb->prepare(
                "SELECT id, recommended_products FROM {$table_name}
                 WHERE id > %d ORDER BY id ASC LIMIT %d",
                $last_id,
                $batch_size
            ) );

            if ( empty( $rows ) ) {
                return array( 'last_id' => $last_id, 'done' => true, 'processed' => $processed );
            }

            $cache_ids = array();
            $values    = array();
            $args      = array();
            foreach ( $rows as $row ) {
                $cid         = (int) $row->id;
                $last_id     = $cid;
                $cache_ids[] = $cid;
                $decoded     = json_decode( (string) $row->recommended_products, true );
                foreach ( self::extract_referenced_ids( $decoded ) as $pid ) {
                    $values[] = '(%d, %d)';
                    $args[]   = (int) $pid;
                    $args[]   = $cid;
                }
                ++$processed;
            }

            
            $cph = implode( ', ', array_fill( 0, count( $cache_ids ), '%d' ) );
            
            $wpdb->query( $wpdb->prepare( "DELETE FROM {$rev} WHERE cache_entry_id IN ({$cph})", ...$cache_ids ) );
            if ( ! empty( $values ) ) {
                
                $wpdb->query( $wpdb->prepare(
                    "INSERT IGNORE INTO {$rev} (referenced_product_id, cache_entry_id) VALUES " . implode( ', ', $values ),
                    ...$args
                ) );
            }

            if ( $max_rows > 0 && $processed >= $max_rows ) {
                return array( 'last_id' => $last_id, 'done' => false, 'processed' => $processed );
            }
            if ( $time_budget_seconds > 0 && ( microtime( true ) - $started ) >= $time_budget_seconds ) {
                return array( 'last_id' => $last_id, 'done' => false, 'processed' => $processed );
            }
        } while ( count( $rows ) === $batch_size );

        return array( 'last_id' => $last_id, 'done' => true, 'processed' => $processed );
    }

    
    public static function get_stats() {
        global $wpdb;

        $table_name = self::get_table_name();

        
        $object_cache_enabled = self::is_object_cache_available();
        $object_cache_type = 'None';

        if ( $object_cache_enabled ) {
            if ( class_exists( 'Redis' ) && defined( 'WP_REDIS_DISABLED' ) === false ) {
                $object_cache_type = 'Redis';
            } elseif ( class_exists( 'Memcached' ) ) {
                $object_cache_type = 'Memcached';
            } elseif ( class_exists( 'Memcache' ) ) {
                $object_cache_type = 'Memcache';
            } elseif ( class_exists( 'RedisCachePro\\ObjectCaches\\ObjectCache' ) ) {
                $object_cache_type = 'Object Cache Pro (Redis)';
            } else {
                $object_cache_type = 'External (Unknown)';
            }
        }

        if ( ! self::table_exists() ) {
            return [
                'total_entries' => 0,
                'total_size' => 0,
                'size_formatted' => '0 B',
                'entries_by_type' => [],
                'expired_entries' => 0,
                'object_cache_enabled' => $object_cache_enabled,
                'object_cache_type' => $object_cache_type,
            ];
        }

        
        $total = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table_name}" );

        
        $size = (int) $wpdb->get_var( "SELECT SUM(LENGTH(recommended_products)) FROM {$table_name}" );

        
        $by_type = $wpdb->get_results(
            "SELECT relation_type, COUNT(*) as count
             FROM {$table_name}
             GROUP BY relation_type",
            ARRAY_A
        );

        $entries_by_type = [];
        foreach ( $by_type as $row ) {
            $entries_by_type[ $row['relation_type'] ] = (int) $row['count'];
        }

        
        $expired = (int) $wpdb->get_var(
            "SELECT COUNT(*) FROM {$table_name}
             WHERE expires_at IS NOT NULL AND expires_at < UTC_TIMESTAMP()"
        );

        return [
            'total_entries' => $total,
            'total_size' => $size,
            'size_formatted' => size_format( $size ),
            'entries_by_type' => $entries_by_type,
            'expired_entries' => $expired,
            'object_cache_enabled' => $object_cache_enabled,
            'object_cache_type' => $object_cache_type,
        ];
    }

    
    public static function warm_cache( $product_id, $fetcher ) {
        $product_id = absint( $product_id );
        if ( ! $product_id || ! is_callable( $fetcher ) ) {
            return [];
        }

        $results = [];
        $relation_types = [ 'upsells', 'crosssells', 'related' ];

        foreach ( $relation_types as $relation_type ) {
            try {
                $recommendations = call_user_func( $fetcher, $product_id, $relation_type );

                if ( is_array( $recommendations ) && ! empty( $recommendations ) ) {
                    $success = self::set( $product_id, $relation_type, $recommendations );
                    $results[ $relation_type ] = [
                        'success' => $success,
                        'count' => count( $recommendations ),
                    ];
                } else {
                    $results[ $relation_type ] = [
                        'success' => true,
                        'count' => 0,
                        'skipped' => true,
                    ];
                }
            } catch ( Exception $e ) {
                $results[ $relation_type ] = [
                    'success' => false,
                    'error' => $e->getMessage(),
                ];
            }
        }

        return $results;
    }

    
    public static function get_valid_relation_types() {
        return self::$valid_relation_types;
    }
}