<?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',
    ];

    
    private static $default_expiration = 604800;

    
    private static $hooks_initialized = false;

    
    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( '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() {
        add_submenu_page(
            'ai-eshop-optimizer',                                   
            __( 'Recommendations Cache', 'ai-eshop-optimizer' ),    
            __( '🗃️ Rec Cache', 'ai-eshop-optimizer' ),             
            'manage_woocommerce',                                    
            'aieo-recommendations-cache',                            
            array( __CLASS__, 'render_admin_page' )                 
        );
    }

    
    public static function render_admin_page() {
        $stats = self::get_stats();
        ?>
        <div class="wrap">
            <h1><?php esc_html_e( 'Recommendations Cache', 'ai-eshop-optimizer' ); ?></h1>

            <div class="card" style="max-width: 800px; padding: 20px; margin-top: 20px;">
                <h2 style="margin-top: 0;"><?php esc_html_e( 'Cache Statistics', 'ai-eshop-optimizer' ); ?></h2>

                <table class="widefat striped" style="margin-bottom: 20px;">
                    <tbody>
                        <tr>
                            <th scope="row"><?php esc_html_e( 'Object Cache', 'ai-eshop-optimizer' ); ?></th>
                            <td>
                                <?php if ( $stats['object_cache_enabled'] ) : ?>
                                    <span style="color: green;">✓ <?php echo esc_html( $stats['object_cache_type'] ); ?></span>
                                <?php else : ?>
                                    <span style="color: gray;">✗ <?php esc_html_e( 'Not available', 'ai-eshop-optimizer' ); ?></span>
                                <?php endif; ?>
                            </td>
                        </tr>
                        <tr>
                            <th scope="row"><?php esc_html_e( 'Total Cached Products', 'ai-eshop-optimizer' ); ?></th>
                            <td><strong><?php echo esc_html( number_format( $stats['total_entries'] ) ); ?></strong></td>
                        </tr>
                        <tr>
                            <th scope="row"><?php esc_html_e( 'Cache Size', 'ai-eshop-optimizer' ); ?></th>
                            <td><?php echo esc_html( $stats['size_formatted'] ); ?></td>
                        </tr>
                        <tr>
                            <th scope="row"><?php esc_html_e( 'Expired Entries', 'ai-eshop-optimizer' ); ?></th>
                            <td><?php echo esc_html( number_format( $stats['expired_entries'] ) ); ?></td>
                        </tr>
                        <tr>
                            <th scope="row"><?php esc_html_e( 'Entries by Type', 'ai-eshop-optimizer' ); ?></th>
                            <td>
                                <?php if ( ! empty( $stats['entries_by_type'] ) ) : ?>
                                    <ul style="margin: 0; padding-left: 20px;">
                                        <?php foreach ( $stats['entries_by_type'] as $type => $count ) : ?>
                                            <li><strong><?php echo esc_html( ucfirst( $type ) ); ?>:</strong> <?php echo esc_html( number_format( $count ) ); ?></li>
                                        <?php endforeach; ?>
                                    </ul>
                                <?php else : ?>
                                    <em><?php esc_html_e( 'No cached entries', 'ai-eshop-optimizer' ); ?></em>
                                <?php endif; ?>
                            </td>
                        </tr>
                    </tbody>
                </table>

                <h3><?php esc_html_e( 'Cache Actions', 'ai-eshop-optimizer' ); ?></h3>
                <p>
                    <button type="button" class="button button-secondary" id="aieo-cache-clear-expired">
                        <?php esc_html_e( 'Clear Expired Entries', 'ai-eshop-optimizer' ); ?>
                    </button>
                    <button type="button" class="button button-secondary" id="aieo-cache-clear-all" style="margin-left: 10px;">
                        <?php esc_html_e( 'Clear All Cache', 'ai-eshop-optimizer' ); ?>
                    </button>
                </p>
                <p id="aieo-cache-message" style="margin-top: 10px;"></p>
            </div>

            <div class="card" style="max-width: 800px; padding: 20px; margin-top: 20px;">
                <h2 style="margin-top: 0;"><?php esc_html_e( 'How It Works', 'ai-eshop-optimizer' ); ?></h2>
                <p><?php esc_html_e( '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' ); ?></p>
                <ul style="margin-left: 20px;">
                    <li><strong><?php esc_html_e( 'Cache Hierarchy:', 'ai-eshop-optimizer' ); ?></strong> <?php esc_html_e( 'Object Cache (Redis/Memcached) → Custom DB Table', 'ai-eshop-optimizer' ); ?></li>
                    <li><strong><?php esc_html_e( 'Automatic Invalidation:', 'ai-eshop-optimizer' ); ?></strong> <?php esc_html_e( 'Cache is automatically cleared when products are updated or deleted.', 'ai-eshop-optimizer' ); ?></li>
                    <li><strong><?php esc_html_e( 'Expiration:', 'ai-eshop-optimizer' ); ?></strong> <?php esc_html_e( 'Cache entries expire after 7 days and are automatically cleaned up.', 'ai-eshop-optimizer' ); ?></li>
                    <li><strong><?php esc_html_e( 'Recently Viewed:', 'ai-eshop-optimizer' ); ?></strong> <?php esc_html_e( 'Not cached (per-user/cookie-based, varies by visitor).', 'ai-eshop-optimizer' ); ?></li>
                </ul>
            </div>
        </div>

        <script>
        jQuery(document).ready(function($) {
            function showMessage(message, type) {
                var color = type === 'success' ? 'green' : 'red';
                $('#aieo-cache-message').html('<span style="color: ' + color + ';">' + message + '</span>');
            }

            $('#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 || 'Error', 'error');
                    }
                });
            });

            $('#aieo-cache-clear-all').on('click', function() {
                if (!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 || 'Error', 'error');
                    }
                });
            });
        });
        </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::delete( $post_id );

        
        self::invalidate_referencing( $post_id );
    }

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

        
        self::invalidate_referencing( $product_id );
    }

    
    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::delete( $post_id );

        
        self::invalidate_referencing( $post_id );
    }

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

        
        self::delete( $post_id );

        
        self::invalidate_referencing( $post_id );
    }

    
    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 = $wpdb->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 ) {
        if ( in_array( $relation_type, self::$valid_relation_types, true ) ) {
            return true;
        }
        
        
        
        
        
        
        if ( preg_match( '/^(bundle_(?:full|rec_only)_v\d+)_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' );

        
        $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 {
            

            
            
            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",
                    $product_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 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;
    }
}
