<?php
/**
 * The file that defines the speech builder class
 *
 * A class definition that includes attributes and functions used across both the
 * public-facing side of the site and the admin area.
 *
 * @link       https://mementor.no
 * @since      1.0.0
 *
 * @package    TTSWP
 * @subpackage Mementor_TTS/includes
 */

// If this file is called directly, abort.
if (!defined('ABSPATH')) {
    exit;
}

/**
 * Speech Builder Class
 * Handles the storage and retrieval of speech elements
 */
class Mementor_TTS_Speech_Builder {
    /**
     * Instance of the class
     */
    private static $instance = null;

    /**
     * Database version
     */
    private $db_version = '1.1.1';

    /**
     * Generation history database version
     */
    private $generation_history_db_version = '1.0';

    /**
     * Constructor
     */
    private function __construct() {
        // Add initialization hook
        add_action('init', array($this, 'init'));
        
        // Add activation hook for database setup
        register_activation_hook(MEMENTOR_TTS_PLUGIN_FILE, array($this, 'create_tables'));
    }

    /**
     * Initialize the builder
     */
    public function init() {
        // Check if tables need to be created or updated
        $current_version = get_option('mementor_tts_db_version', '0');
        if (version_compare($current_version, $this->db_version, '<')) {
            $this->create_tables();
        }
    }

    /**
     * Get instance of the class
     */
    public static function get_instance() {
        if (null === self::$instance) {
            self::$instance = new self();
        }
        return self::$instance;
    }

    /**
     * Create database tables
     */
    public function create_tables() {
        global $wpdb;

        $charset_collate = $wpdb->get_charset_collate();

        // Elements table
        $table_name = $wpdb->prefix . 'mementor_tts_elements';
        $sql = "CREATE TABLE IF NOT EXISTS $table_name (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            post_id bigint(20) NOT NULL,
            element_uid varchar(50) NOT NULL,
            content longtext NOT NULL,
            voice_id varchar(50) DEFAULT NULL,
            options text DEFAULT NULL,
            audio_url varchar(255) DEFAULT NULL,
            status varchar(20) DEFAULT 'pending',
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            UNIQUE KEY element_uid (post_id, element_uid),
            KEY status (status)
        ) $charset_collate;";

        // Queue table
        $table_name = $wpdb->prefix . 'mementor_tts_queue';
        $sql .= "CREATE TABLE IF NOT EXISTS $table_name (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            element_id bigint(20) NOT NULL,
            priority int(11) DEFAULT 0,
            attempts int(11) DEFAULT 0,
            status varchar(20) DEFAULT 'pending',
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            UNIQUE KEY element_id (element_id),
            KEY status (status)
        ) $charset_collate;";

        // Include upgrade.php for dbDelta function (required when running outside of activation)
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);

        // Update database version
        update_option('mementor_tts_db_version', $this->db_version, false);

        // Create generation history table
        $this->create_generation_history_table();
    }

    /**
     * Create generation history table for credit usage tracking
     *
     * @since 1.9.4
     */
    private function create_generation_history_table() {
        global $wpdb;

        $table_name = $wpdb->prefix . 'mementor_tts_generation_history';
        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE $table_name (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            post_id bigint(20) NOT NULL,
            element_id bigint(20) DEFAULT NULL,
            credits_used int(11) NOT NULL DEFAULT 0,
            text_length int(11) NOT NULL DEFAULT 0,
            voice_id varchar(100) DEFAULT NULL,
            model_id varchar(50) DEFAULT NULL,
            api_type varchar(20) DEFAULT 'shared',
            file_size bigint(20) DEFAULT 0,
            generated_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            KEY idx_post_id (post_id),
            KEY idx_generated_at (generated_at)
        ) $charset_collate;";

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

        // Update generation history version
        update_option('mementor_tts_generation_history_db_version', $this->generation_history_db_version);
    }

    /**
     * Store a speech element
     */
    public function store_element($post_id, $element_uid, $content, $voice_id = '', $options = '') {
        // Sanitize inputs
        $post_id = absint($post_id);
        $element_uid = sanitize_key($element_uid);
        $content = wp_kses_post($content);
        $voice_id = sanitize_text_field($voice_id);
        $options = is_string($options) ? sanitize_text_field($options) : '';

        // Verify post exists
        if (!get_post($post_id)) {
            return false;
        }
        
        // Use a transient to prevent duplicate operations in quick succession
        $operation_key = 'mementor_tts_store_' . $post_id . '_' . $element_uid;
        $store_lock = get_transient($operation_key);
        
        if (false !== $store_lock) {
            // Operation is already in progress or was recently completed
            return $store_lock === 'success';
        }
        
        // Set a short lock to prevent duplicate operations
        set_transient($operation_key, 'in_progress', 10); // 10 seconds lock
        
        // Insert or update element
        $data = array(
            'post_id' => $post_id,
            'element_uid' => $element_uid,
            'content' => $content,
            'voice_id' => $voice_id,
            'options' => $options,
            'status' => 'pending'
        );
        $format = array('%d', '%s', '%s', '%s', '%s', '%s');

        // First check if element exists to determine if we need insert or update
        $exists = $this->element_exists($post_id, $element_uid);
        
        $result = false;
        if (!$exists) {
            // Insert new element
            $result = $this->db_insert('mementor_tts_elements', $data, $format);
        } else {
            // Update existing element
            $result = $this->db_update(
                'mementor_tts_elements',
                $data,
                array(
                    'post_id' => $post_id,
                    'element_uid' => $element_uid
                ),
                $format,
                array('%d', '%s')
            );
        }
        
        // If operation was successful, clear any related caches
        if ($result !== false) {
            // Clear single element cache
            $cache_key = 'mementor_tts_element_' . $post_id . '_' . $element_uid;
            wp_cache_delete($cache_key, 'mementor_tts');
            
            // Clear any pending elements cache for this post
            wp_cache_delete('mementor_tts_pending_elements_' . $post_id . '_5', 'mementor_tts');
            wp_cache_delete('mementor_tts_pending_elements_' . $post_id . '_10', 'mementor_tts');
            wp_cache_delete('mementor_tts_pending_elements_' . $post_id . '_20', 'mementor_tts');
            
            // Store result in transient for a short time
            set_transient($operation_key, 'success', 60); // 60 seconds
        } else {
            // Store failure result
            set_transient($operation_key, 'failed', 60); // 60 seconds
        }

        return $result !== false;
    }

    /**
     * Check if element exists in database
     * 
     * @param int    $post_id     Post ID
     * @param string $element_uid Element UID
     * @return bool True if element exists
     */
    private function element_exists($post_id, $element_uid) {
        // Generate a cache key
        $cache_key = 'mementor_tts_element_exists_' . $post_id . '_' . $element_uid;
        
        // Try to get from cache first
        $exists = wp_cache_get($cache_key, 'mementor_tts');
        
        if (false === $exists) {
            global $wpdb;
            
            // Use get_var for a simple count query rather than retrieving the whole row
            $query = $wpdb->prepare(
                "SELECT COUNT(*) FROM $wpdb->prefix" . "mementor_tts_elements WHERE post_id = %d AND element_uid = %s",
                $post_id,
                $element_uid
            );
            
            $count = $this->db_get_var($query, 3600);
            
            $exists = (int)$count > 0;
            
            // Cache the result
            wp_cache_set($cache_key, $exists, 'mementor_tts', 3600);
        }
        
        return $exists;
    }

    /**
     * Get a speech element
     */
    public function get_element($post_id, $element_uid) {
        // Sanitize inputs
        $post_id = absint($post_id);
        $element_uid = sanitize_key($element_uid);
        
        // Generate a cache key
        $cache_key = 'mementor_tts_element_' . $post_id . '_' . $element_uid;
        
        // Try to get from cache first
        $element = wp_cache_get($cache_key, 'mementor_tts');
        
        // If not in cache, get from database
        if (false === $element) {
            global $wpdb;
            
            // Get element
            $query = $wpdb->prepare(
                "SELECT * FROM $wpdb->prefix" . "mementor_tts_elements WHERE post_id = %d AND element_uid = %s",
                $post_id,
                $element_uid
            );
            
            $element = $this->db_get_row($query, OBJECT, 3600);
            
            // Cache the result (even if it's null)
            wp_cache_set($cache_key, $element, 'mementor_tts', 3600); // Cache for 1 hour
        }

        return $element;
    }

    /**
     * Update element audio URL
     */
    public function update_element_audio($post_id, $element_uid, $audio_url) {
        // Sanitize inputs
        $post_id = absint($post_id);
        $element_uid = sanitize_key($element_uid);
        $audio_url = esc_url_raw($audio_url);
        
        // Use a transient to prevent duplicate operations in quick succession
        $operation_key = 'mementor_tts_update_audio_' . $post_id . '_' . $element_uid;
        $update_lock = get_transient($operation_key);
        
        if (false !== $update_lock) {
            // Operation is already in progress or was recently completed
            return $update_lock === 'success';
        }
        
        // Set a short lock to prevent duplicate operations
        set_transient($operation_key, 'in_progress', 10); // 10 seconds lock

        // Update element
        $result = $this->db_update(
            'mementor_tts_elements',
            array(
                'audio_url' => $audio_url,
                'status' => 'completed'
            ),
            array(
                'post_id' => $post_id,
                'element_uid' => $element_uid
            ),
            array('%s', '%s'),
            array('%d', '%s')
        );
        
        // If update was successful, clear the cache
        if ($result) {
            $cache_key = 'mementor_tts_element_' . $post_id . '_' . $element_uid;
            wp_cache_delete($cache_key, 'mementor_tts');
            
            // Also clear the exists cache
            wp_cache_delete('mementor_tts_element_exists_' . $post_id . '_' . $element_uid, 'mementor_tts');
            
            // Store result in transient for a short time
            set_transient($operation_key, 'success', 60); // 60 seconds
        } else {
            // Store failure result
            set_transient($operation_key, 'failed', 60); // 60 seconds
        }
        
        return $result;
    }

    /**
     * Delete a speech element
     */
    public function delete_element($post_id, $element_uid) {
        // Sanitize inputs
        $post_id = absint($post_id);
        $element_uid = sanitize_key($element_uid);

        $debug = defined('MEMENTOR_TTS_DEBUG') && MEMENTOR_TTS_DEBUG;

        if ($debug) {
            error_log('TTS Delete: Starting deletion for post_id=' . $post_id . ', element_uid=' . $element_uid);
        }

        // Use a transient to prevent duplicate delete operations
        $operation_key = 'mementor_tts_delete_' . $post_id . '_' . $element_uid;
        $delete_lock = get_transient($operation_key);

        if (false !== $delete_lock) {
            // Operation is already in progress or was recently completed
            if (defined('MEMENTOR_TTS_DEBUG') && MEMENTOR_TTS_DEBUG) {
                error_log('TTS Delete: Found existing lock, result=' . $delete_lock);
            }
            return $delete_lock === 'success';
        }

        // Set a short lock to prevent duplicate operations
        set_transient($operation_key, 'in_progress', 10); // 10 seconds lock

        // Get element first to get audio URL
        $element = $this->get_element($post_id, $element_uid);
        if (!$element) {
            // Element doesn't exist, mark as success (already deleted)
            if (defined('MEMENTOR_TTS_DEBUG') && MEMENTOR_TTS_DEBUG) {
                error_log('TTS Delete: Element not found in database');
            }
            set_transient($operation_key, 'success', 60);
            return true;
        }

        if ($debug) {
            error_log('TTS Delete: Found element with audio_url=' . $element->audio_url);
        }

        // Delete associated Media Library attachment if one exists
        $attachment_id = get_post_meta($post_id, '_mementor_tts_attachment_id', true);
        if ($attachment_id) {
            $attachment_id = absint($attachment_id);
            // wp_delete_attachment with force=true also deletes the physical file
            wp_delete_attachment($attachment_id, true);
            delete_post_meta($post_id, '_mementor_tts_attachment_id');
            if ($debug) {
                error_log('TTS Delete: Deleted Media Library attachment #' . $attachment_id);
            }
            // Since wp_delete_attachment already deleted the physical file,
            // skip the plugin's own file deletion for local files
            $file_deleted_by_attachment = true;
        } else {
            $file_deleted_by_attachment = false;
        }

        // Delete associated audio file if it exists
        if (!empty($element->audio_url)) {
            // SaaS-hosted audio lives on our CloudFront distribution. Delete
            // through the SaaS so S3 is cleaned up AND the CDN path is
            // invalidated (BYOK / user-S3 path is handled further below).
            $is_saas_cdn = ttswp_is_saas_cdn_url($element->audio_url);
            if ($is_saas_cdn) {
                if ($debug) {
                    error_log('TTS Delete: Detected SaaS CDN URL, calling /api/v1/audio');
                }
                if (!class_exists('Mementor_TTS_Api_Client')) {
                    require_once MEMENTOR_TTS_PLUGIN_DIR . 'includes/class-mementor-tts-api-client.php';
                }
                $api_result = Mementor_TTS_Api_Client::get_instance()->delete_audio($element->audio_url);
                if ($debug) {
                    error_log('TTS SaaS Delete: ' . (is_wp_error($api_result) ? $api_result->get_error_message() : 'success'));
                }
            }
            // Check if this is an S3 URL
            elseif (strpos($element->audio_url, 'amazonaws.com') !== false || strpos($element->audio_url, '.s3.') !== false) {
                if ($debug) {
                    error_log('TTS Delete: Detected S3 URL, proceeding with S3 deletion');
                }

                // This is an S3 file - use S3 handler to delete
                if (!class_exists('Mementor_TTS_S3_Handler')) {
                    require_once MEMENTOR_TTS_PLUGIN_DIR . 'includes/class-mementor-tts-s3-handler.php';
                }

                $s3_handler = new Mementor_TTS_S3_Handler();

                if ($s3_handler->is_configured()) {
                    if (defined('MEMENTOR_TTS_DEBUG') && MEMENTOR_TTS_DEBUG) {
                        error_log('TTS Delete: S3 handler is configured');
                    }
                    // Extract S3 key from URL
                    // URL format: https://bucket.s3.region.amazonaws.com/path/file.mp3
                    $parsed_url = wp_parse_url($element->audio_url);
                    if (isset($parsed_url['path'])) {
                        // Remove leading slash to get the S3 key
                        $s3_key = ltrim($parsed_url['path'], '/');

                        if ($debug) {
                            error_log('TTS S3 Delete: Attempting to delete S3 file with key: ' . $s3_key);
                        }

                        // Pass true to use_full_key since we extracted the key from the stored URL
                        $delete_result = $s3_handler->delete_file($s3_key, true);

                        if ($debug) {
                            error_log('TTS S3 Delete: Result: ' . ($delete_result ? 'success' : 'failed'));
                        }
                    }
                } else {
                    // S3 not configured but URL is S3 - log warning
                    if (defined('MEMENTOR_TTS_DEBUG') && MEMENTOR_TTS_DEBUG) {
                        error_log('TTS S3 Delete: S3 URL detected but S3 is not configured');
                    }
                }
            } else {
                // Local file - delete from uploads directory (skip if already deleted by media attachment cleanup)
                if (!$file_deleted_by_attachment) {
                    $upload_dir = wp_upload_dir();
                    $file_path = str_replace($upload_dir['baseurl'], $upload_dir['basedir'], $element->audio_url);
                    if (file_exists($file_path)) {
                        wp_delete_file($file_path);
                    } else {
                        // Log if local file doesn't exist
                        if (defined('MEMENTOR_TTS_DEBUG') && MEMENTOR_TTS_DEBUG) {
                            error_log('TTS Delete: Local file not found: ' . $file_path);
                        }
                    }
                }
            }
        }

        // Delete element from database
        $result = $this->db_delete(
            'mementor_tts_elements',
            array(
                'post_id' => $post_id,
                'element_uid' => $element_uid
            ),
            array('%d', '%s')
        );

        if ($debug) {
            error_log('TTS Delete: Database deletion result=' . var_export($result, true));
        }

        // If deletion was successful, clear the cache
        if ($result) {
            // Clear element cache
            $cache_key = 'mementor_tts_element_' . $post_id . '_' . $element_uid;
            wp_cache_delete($cache_key, 'mementor_tts');

            // Clear existence check cache
            wp_cache_delete('mementor_tts_element_exists_' . $post_id . '_' . $element_uid, 'mementor_tts');

            // Clear pending elements cache for this post
            wp_cache_delete('mementor_tts_pending_elements_' . $post_id . '_5', 'mementor_tts');
            wp_cache_delete('mementor_tts_pending_elements_' . $post_id . '_10', 'mementor_tts');

            // Store success in transient
            set_transient($operation_key, 'success', 60); // 60 seconds

            if (defined('MEMENTOR_TTS_DEBUG') && MEMENTOR_TTS_DEBUG) {
                error_log('TTS Delete: Successfully completed deletion');
            }
        } else {
            // Store failure in transient
            set_transient($operation_key, 'failed', 60); // 60 seconds

            if (defined('MEMENTOR_TTS_DEBUG') && MEMENTOR_TTS_DEBUG) {
                error_log('TTS Delete: Database deletion FAILED');
            }
        }

        return $result;
    }

    /**
     * Get pending elements
     */
    public function get_pending_elements($post_id, $limit = 5) {
        // Sanitize inputs
        $post_id = absint($post_id);
        $limit = absint($limit);
        
        // Generate a cache key
        $cache_key = 'mementor_tts_pending_elements_' . $post_id . '_' . $limit;
        
        // Try to get from cache first
        $elements = wp_cache_get($cache_key, 'mementor_tts');
        
        // If not in cache, get from database
        if (false === $elements) {
            global $wpdb;
            
            // Get elements
            $query = $wpdb->prepare(
                "SELECT * FROM $wpdb->prefix" . "mementor_tts_elements WHERE post_id = %d AND status = %s ORDER BY id ASC LIMIT %d",
                $post_id,
                'pending',
                $limit
            );
            
            $elements = $this->db_get_results($query, OBJECT, 60);
            
            // Cache the result for a short time (pending elements change frequently)
            wp_cache_set($cache_key, $elements, 'mementor_tts', 60); // Cache for 1 minute
        }

        return $elements;
    }

    /**
     * Get speech elements for a post
     * This method extracts content from a post using the enhanced extraction logic
     */
    public function get_speech_elements($post_id) {
        // Sanitize post ID
        $post_id = absint($post_id);
        
        // Get the post
        $post = get_post($post_id);
        if (!$post) {
            return array();
        }
        
        // Get the processor instance
        $processor = Mementor_TTS_Processor::get_instance();

        // Get raw post content and strip audio/video player shortcodes BEFORE applying filters
        $raw_content = $post->post_content;

        // Remove common audio/video player shortcodes that should never be narrated
        $audio_player_shortcodes = array(
            'tp_audio_player',           // ThePlus Audio Player
            'audio',                      // WordPress default audio
            'video',                      // WordPress default video
            'embed',                      // WordPress embed
            'playlist',                   // WordPress playlist
            'soundcloud',                 // SoundCloud
            'spotify',                    // Spotify
            'youtube',                    // YouTube (if using shortcode)
            'vimeo',                      // Vimeo (if using shortcode)
            'mediaelementwrapper',       // MediaElement.js
            'fv_player',                  // FV Player
            'bp_media',                   // BuddyPress Media
            'powerpress',                 // PowerPress Podcast
        );

        // Strip these shortcodes before processing
        foreach ($audio_player_shortcodes as $shortcode) {
            // Remove both self-closing and enclosing formats
            $raw_content = preg_replace('/\[' . preg_quote($shortcode, '/') . '(\s[^\]]+)?\]/i', '', $raw_content);
            $raw_content = preg_replace('/\[' . preg_quote($shortcode, '/') . '(\s[^\]]+)?\].*?\[\/' . preg_quote($shortcode, '/') . '\]/is', '', $raw_content);
        }

        // Render via helper so Fusion Builder / Avada emit the full post body
        // in AJAX context, not just the teaser above <!--more-->.
        $post_content = ttswp_render_content_full($raw_content, $post_id);

        $ttswp_sb_rendered_len = strlen( $post_content );
        $ttswp_sb_raw_len      = strlen( $raw_content );
        $ttswp_sb_sample       = ( get_option( 'mementor_tts_remote_debug_include_content', '0' ) === '1' )
            ? mb_substr( wp_strip_all_tags( $post_content ), 0, 400 )
            : null;
        $ttswp_sb_ctx = array(
            'path'         => 'speech_builder.get_speech_elements',
            'raw_post_len' => $ttswp_sb_raw_len,
            'rendered_len' => $ttswp_sb_rendered_len,
        );
        if ( $ttswp_sb_sample !== null ) {
            $ttswp_sb_ctx['rendered_sample'] = $ttswp_sb_sample;
        }
        ttswp_send_remote_debug_event(
            'content_extracted',
            sprintf( 'speech_builder rendered length: %d (raw %d)', $ttswp_sb_rendered_len, $ttswp_sb_raw_len ),
            $ttswp_sb_ctx,
            $ttswp_sb_rendered_len < 500 && $ttswp_sb_raw_len > 1500 ? 'warning' : 'debug',
            $post_id
        );

        // IMPORTANT: Strip audio/video player HTML elements that were rendered by page builders
        // This catches Elementor widgets and other players that don't use shortcodes

        // First, remove all <audio> and <video> HTML5 tags
        $post_content = preg_replace('/<(audio|video)[^>]*>.*?<\/\1>/is', '', $post_content);

        // Remove complete Elementor audio/video widget containers (most comprehensive)
        $post_content = preg_replace('/<div[^>]*elementor-widget-tp-audio-player[^>]*>.*?<\/div>\s*<\/div>/is', '', $post_content);
        $post_content = preg_replace('/<div[^>]*data-widget_type="tp-audio-player[^"]*"[^>]*>.*?<\/div>\s*<\/div>/is', '', $post_content);

        // Remove ThePlus Audio player wrapper and all nested content
        $post_content = preg_replace('/<div[^>]*tp-audio-player-wrapper[^>]*>.*?<\/div>/is', '', $post_content);
        $post_content = preg_replace('/<div[^>]*class="[^"]*tp-player[^"]*"[^>]*>.*?<\/div>/is', '', $post_content);

        // Remove common audio player UI elements by class patterns
        $audio_ui_classes = array(
            'audio-player', 'video-player', 'media-player',
            'tp-audio', 'plus-audio', 'theplus-audio',
            'mejs-container', 'mejs-audio', 'mejs-video',
            'plyr', 'jplayer', 'flowplayer',
            'wp-audio-shortcode', 'wp-video-shortcode',
            'elementor-widget-audio', 'elementor-widget-video',
            'elementor-widget-tp-audio-player'
        );

        foreach ($audio_ui_classes as $class_pattern) {
            $post_content = preg_replace('/<div[^>]*class="[^"]*' . preg_quote($class_pattern, '/') . '[^"]*"[^>]*>.*?<\/div>/is', '', $post_content);
        }

        // Use the processor's enhanced extraction method
        $extracted_content = $processor->get_content_from_html($post_content);
        
        // If no content extracted, return empty array
        if (empty($extracted_content)) {
            return array();
        }
        
        // Create a speech element object
        $element = new stdClass();
        $element->content = $extracted_content;
        $element->post_id = $post_id;
        $element->element_uid = 'post_content_' . $post_id;
        
        return array($element);
    }
    
    /**
     * Clean up old elements
     */
    public function cleanup_elements($days = 30) {
        // Generate a cache key for this operation
        $cache_key = 'mementor_tts_cleanup_' . $days;
        
        // Use a transient to prevent redundant cleanup operations
        $operation_transient = 'mementor_tts_cleanup_running_' . $days;
        
        // Check if a cleanup is already in progress
        if (get_transient($operation_transient)) {
            return get_option($cache_key, 0); // Return last cleanup count
        }
        
        // Set a transient to indicate cleanup is in progress
        set_transient($operation_transient, true, 300); // 5 minute lock
        
        // Check if we've run this recently (once per hour is enough)
        $cleanup_run = wp_cache_get($cache_key, 'mementor_tts');
        if (false !== $cleanup_run) {
            delete_transient($operation_transient); // Release the lock
            return $cleanup_run; // Return the cached count
        }
        
        global $wpdb;
        
        // Get a list of IDs to delete rather than full elements to reduce memory usage
        $query = $wpdb->prepare(
            "SELECT post_id, element_uid FROM $wpdb->prefix" . "mementor_tts_elements 
            WHERE created_at < DATE_SUB(NOW(), INTERVAL %d DAY) LIMIT 100",
            $days
        );
        
        $ids = $this->db_get_results($query, OBJECT, 60);
        $count = 0;
        
        // Process deletion in batches to avoid timeout
        foreach ($ids as $item) {
            $result = $this->delete_element($item->post_id, $item->element_uid);
            if ($result) {
                $count++;
            }
            
            // Add a small delay to avoid database overload
            if ($count % 10 === 0) {
                usleep(100000); // 100ms pause every 10 items
            }
        }
        
        // Cache the result for 1 hour
        wp_cache_set($cache_key, $count, 'mementor_tts', 3600);
        
        // Also store as an option for persistent storage
        update_option($cache_key, $count);
        
        // Release the operation lock
        delete_transient($operation_transient);

        return $count;
    }

    /**
     * Log a generation event for credit usage tracking
     *
     * @since 1.9.4
     * @param int   $post_id      The post ID
     * @param int   $credits_used The number of credits (characters) used
     * @param array $metadata     Additional metadata (voice_id, model_id, api_type, file_size, element_id)
     * @return bool True on success, false on failure
     */
    public function log_generation($post_id, $credits_used, $metadata = array()) {
        global $wpdb;

        // Sanitize inputs
        $post_id = absint($post_id);
        $credits_used = absint($credits_used);

        // Debug logging
        if (function_exists('mementor_tts_is_debug_enabled') && mementor_tts_is_debug_enabled()) {
            error_log('[TTS Credit Usage] log_generation called: post_id=' . $post_id . ', credits=' . $credits_used);
        }

        $ttswp_merged_ctx = array(
            'credits_used' => (int) $credits_used,
            'voice_id'     => isset( $metadata['voice_id'] ) ? $metadata['voice_id'] : null,
            'api_type'     => isset( $metadata['api_type'] ) ? $metadata['api_type'] : null,
            'model_id'     => isset( $metadata['model_id'] ) ? $metadata['model_id'] : null,
            'file_size'    => isset( $metadata['file_size'] ) ? (int) $metadata['file_size'] : null,
        );
        if ( class_exists( 'Mementor_TTS_Processor' )
            && isset( Mementor_TTS_Processor::$last_gen_ctx[ $post_id ] ) ) {
            $ttswp_merged_ctx = array_merge( Mementor_TTS_Processor::$last_gen_ctx[ $post_id ], $ttswp_merged_ctx );
            unset( Mementor_TTS_Processor::$last_gen_ctx[ $post_id ] );
        }
        if ( class_exists( 'Mementor_TTS_Api_Client' ) && Mementor_TTS_Api_Client::$last_tts_payload !== null ) {
            $ttswp_merged_ctx['tts_payload_sample'] = Mementor_TTS_Api_Client::$last_tts_payload;
            $ttswp_merged_ctx['tts_payload_len']    = strlen( Mementor_TTS_Api_Client::$last_tts_payload );
            Mementor_TTS_Api_Client::$last_tts_payload = null;
        }
        $ttswp_raw_for_severity      = isset( $ttswp_merged_ctx['raw_post_len'] ) ? (int) $ttswp_merged_ctx['raw_post_len'] : 0;
        $ttswp_text_for_severity     = isset( $ttswp_merged_ctx['text_len'] ) ? (int) $ttswp_merged_ctx['text_len'] : (int) $credits_used;
        $ttswp_truncation_suspected  = ( $ttswp_text_for_severity < 500 && $ttswp_raw_for_severity > 1500 );
        ttswp_send_remote_debug_event(
            $ttswp_truncation_suspected ? 'content_truncated' : 'generate_success',
            $ttswp_truncation_suspected
                ? sprintf( 'Possible truncation — %d chars sent to TTS but raw post is %d', $ttswp_text_for_severity, $ttswp_raw_for_severity )
                : sprintf( 'Generation completed — %d credits charged', $credits_used ),
            $ttswp_merged_ctx,
            $ttswp_truncation_suspected ? 'warning' : 'info',
            $post_id
        );

        // Skip logging if no post ID or credits
        if (empty($post_id) || empty($credits_used)) {
            if (function_exists('mementor_tts_is_debug_enabled') && mementor_tts_is_debug_enabled()) {
                error_log('[TTS Credit Usage] Skipping - empty post_id or credits');
            }
            return false;
        }

        $table_name = $wpdb->prefix . 'mementor_tts_generation_history';

        // Check if table exists first
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $table_exists = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table_name));
        if ($table_exists !== $table_name) {
            // Table doesn't exist, try to create it
            if (function_exists('mementor_tts_is_debug_enabled') && mementor_tts_is_debug_enabled()) {
                error_log('[TTS Credit Usage] Table does not exist, creating...');
            }
            $this->create_generation_history_table();
        }

        // Prepare data for insertion
        $data = array(
            'post_id'      => $post_id,
            'element_id'   => isset($metadata['element_id']) ? absint($metadata['element_id']) : null,
            'credits_used' => $credits_used,
            'text_length'  => isset($metadata['text_length']) ? absint($metadata['text_length']) : $credits_used,
            'voice_id'     => isset($metadata['voice_id']) ? sanitize_text_field($metadata['voice_id']) : null,
            'model_id'     => isset($metadata['model_id']) ? sanitize_text_field($metadata['model_id']) : null,
            'api_type'     => isset($metadata['api_type']) ? sanitize_key($metadata['api_type']) : 'shared',
            'file_size'    => isset($metadata['file_size']) ? absint($metadata['file_size']) : 0,
        );

        $format = array('%d', '%d', '%d', '%d', '%s', '%s', '%s', '%d');

        // Insert the record
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $result = $wpdb->insert($table_name, $data, $format);

        if ($result === false) {
            if (function_exists('mementor_tts_is_debug_enabled') && mementor_tts_is_debug_enabled()) {
                error_log('[TTS Credit Usage] Failed to log generation: ' . $wpdb->last_error);
            }
            return false;
        }

        if (function_exists('mementor_tts_is_debug_enabled') && mementor_tts_is_debug_enabled()) {
            error_log('[TTS Credit Usage] Successfully logged generation for post ' . $post_id . ' with ' . $credits_used . ' credits');
        }

        return true;
    }

    /**
     * Get credit usage statistics grouped by post
     *
     * @since 1.9.4
     * @param string|null $date_from Start date (Y-m-d format)
     * @param string|null $date_to   End date (Y-m-d format)
     * @param int         $limit     Maximum number of results
     * @param int         $offset    Offset for pagination
     * @return array Array of usage statistics per post
     */
    public function get_credit_usage_stats($date_from = null, $date_to = null, $limit = 20, $offset = 0) {
        global $wpdb;

        $table_name = $wpdb->prefix . 'mementor_tts_generation_history';

        // Check if table exists
        $table_exists = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table_name));
        if ($table_exists !== $table_name) {
            return array();
        }

        // Build the WHERE clause
        $where_clauses = array('1=1');
        $prepare_args = array();

        if ($date_from) {
            $where_clauses[] = 'h.generated_at >= %s';
            $prepare_args[] = sanitize_text_field($date_from) . ' 00:00:00';
        }

        if ($date_to) {
            $where_clauses[] = 'h.generated_at <= %s';
            $prepare_args[] = sanitize_text_field($date_to) . ' 23:59:59';
        }

        $where = implode(' AND ', $where_clauses);

        // Add limit and offset to prepare args
        $prepare_args[] = absint($limit);
        $prepare_args[] = absint($offset);

        // Build the query with subquery to get last generation credits and model
        $query = "
            SELECT
                h.post_id,
                p.post_title,
                p.post_type,
                SUM(h.credits_used) as total_credits,
                COUNT(*) as generation_count,
                MAX(h.generated_at) as last_generated,
                (SELECT h2.credits_used FROM $table_name h2
                 WHERE h2.post_id = h.post_id
                 ORDER BY h2.generated_at DESC LIMIT 1) as last_credits,
                (SELECT h3.model_id FROM $table_name h3
                 WHERE h3.post_id = h.post_id
                 ORDER BY h3.generated_at DESC LIMIT 1) as model_id,
                (SELECT h4.text_length FROM $table_name h4
                 WHERE h4.post_id = h.post_id
                 ORDER BY h4.generated_at DESC LIMIT 1) as last_characters
            FROM $table_name h
            LEFT JOIN {$wpdb->posts} p ON h.post_id = p.ID
            WHERE $where
            GROUP BY h.post_id
            ORDER BY last_generated DESC
            LIMIT %d OFFSET %d
        ";

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared
        $results = $wpdb->get_results($wpdb->prepare($query, $prepare_args));

        return $results ? $results : array();
    }

    /**
     * Get total credit usage count (for pagination)
     *
     * @since 1.9.4
     * @param string|null $date_from Start date (Y-m-d format)
     * @param string|null $date_to   End date (Y-m-d format)
     * @return int Total count of unique posts with generations
     */
    public function get_credit_usage_count($date_from = null, $date_to = null) {
        global $wpdb;

        $table_name = $wpdb->prefix . 'mementor_tts_generation_history';

        // Check if table exists
        $table_exists = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table_name));
        if ($table_exists !== $table_name) {
            return 0;
        }

        // Build the WHERE clause
        $where_clauses = array('1=1');
        $prepare_args = array();

        if ($date_from) {
            $where_clauses[] = 'generated_at >= %s';
            $prepare_args[] = sanitize_text_field($date_from) . ' 00:00:00';
        }

        if ($date_to) {
            $where_clauses[] = 'generated_at <= %s';
            $prepare_args[] = sanitize_text_field($date_to) . ' 23:59:59';
        }

        $where = implode(' AND ', $where_clauses);

        // Build the query
        $query = "SELECT COUNT(DISTINCT post_id) FROM $table_name WHERE $where";

        if (!empty($prepare_args)) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared
            $count = $wpdb->get_var($wpdb->prepare($query, $prepare_args));
        } else {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared
            $count = $wpdb->get_var($query);
        }

        return (int) $count;
    }

    /**
     * Get aggregated credit usage totals
     *
     * @since 1.9.4
     * @param string|null $date_from Start date (Y-m-d format)
     * @param string|null $date_to   End date (Y-m-d format)
     * @return array Array with total_credits, total_generations
     */
    public function get_credit_usage_totals($date_from = null, $date_to = null) {
        global $wpdb;

        $table_name = $wpdb->prefix . 'mementor_tts_generation_history';

        // Check if table exists
        $table_exists = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table_name));
        if ($table_exists !== $table_name) {
            return array(
                'total_credits' => 0,
                'total_generations' => 0,
            );
        }

        // Build the WHERE clause
        $where_clauses = array('1=1');
        $prepare_args = array();

        if ($date_from) {
            $where_clauses[] = 'generated_at >= %s';
            $prepare_args[] = sanitize_text_field($date_from) . ' 00:00:00';
        }

        if ($date_to) {
            $where_clauses[] = 'generated_at <= %s';
            $prepare_args[] = sanitize_text_field($date_to) . ' 23:59:59';
        }

        $where = implode(' AND ', $where_clauses);

        // Build the query
        $query = "
            SELECT
                COALESCE(SUM(credits_used), 0) as total_credits,
                COUNT(*) as total_generations
            FROM $table_name
            WHERE $where
        ";

        if (!empty($prepare_args)) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared
            $result = $wpdb->get_row($wpdb->prepare($query, $prepare_args));
        } else {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared
            $result = $wpdb->get_row($query);
        }

        return array(
            'total_credits' => $result ? (int) $result->total_credits : 0,
            'total_generations' => $result ? (int) $result->total_generations : 0,
        );
    }

    /**
     * Get daily credit usage data for chart display.
     *
     * @param string $date_from Start date (Y-m-d)
     * @param string $date_to   End date (Y-m-d)
     * @return array Associative array keyed by date with credits and generations per day.
     */
    public function get_daily_credit_chart_data($date_from, $date_to) {
        global $wpdb;

        $table_name = $wpdb->prefix . 'mementor_tts_generation_history';
        $table_exists = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table_name));
        if ($table_exists !== $table_name) {
            return array();
        }

        $query = $wpdb->prepare(
            "SELECT DATE(generated_at) as day,
                    COALESCE(SUM(credits_used), 0) as credits,
                    COUNT(*) as generations
             FROM $table_name
             WHERE generated_at >= %s AND generated_at <= %s
             GROUP BY DATE(generated_at)
             ORDER BY day",
            sanitize_text_field($date_from) . ' 00:00:00',
            sanitize_text_field($date_to) . ' 23:59:59'
        );

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared
        $results = $wpdb->get_results($query);
        $data = array();
        if ($results) {
            foreach ($results as $row) {
                $data[$row->day] = $row;
            }
        }
        return $data;
    }

    /**
     * Wrapper for database insert operations with caching
     *
     * @param string $table Table name without prefix
     * @param array $data Data to insert
     * @param array $format Formats for the data
     * @return int|false The number of rows inserted, or false on error
     */
    private function db_insert($table, $data, $format) {
        global $wpdb;
        
        // Generate a cache key for this operation
        $cache_key = 'mementor_tts_db_insert_' . md5(serialize([$table, $data, $format]));
        
        // Check if we have a cached result
        $result = wp_cache_get($cache_key, 'mementor_tts');
        if (false !== $result) {
            return $result;
        }
        
        // Perform the database operation
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $result = $wpdb->insert($wpdb->prefix . $table, $data, $format);
        
        // Cache the result briefly
        wp_cache_set($cache_key, $result, 'mementor_tts', 30);
        
        return $result;
    }
    
    /**
     * Wrapper for database update operations with caching
     * 
     * @param string $table Table name without prefix
     * @param array $data Data to update
     * @param array $where Where conditions
     * @param array $format Formats for the data
     * @param array $where_format Formats for the where conditions
     * @return int|false The number of rows updated, or false on error
     */
    private function db_update($table, $data, $where, $format, $where_format) {
        global $wpdb;
        
        // Generate a cache key for this operation
        $cache_key = 'mementor_tts_db_update_' . md5(serialize([$table, $data, $where, $format, $where_format]));
        
        // Check if we have a cached result
        $result = wp_cache_get($cache_key, 'mementor_tts');
        if (false !== $result) {
            return $result;
        }
        
        // Perform the database operation
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $result = $wpdb->update($wpdb->prefix . $table, $data, $where, $format, $where_format);
        
        // Cache the result briefly
        wp_cache_set($cache_key, $result, 'mementor_tts', 30);
        
        return $result;
    }
    
    /**
     * Wrapper for database delete operations with caching
     * 
     * @param string $table Table name without prefix
     * @param array $where Where conditions
     * @param array $where_format Formats for the where conditions
     * @return int|false The number of rows deleted, or false on error
     */
    private function db_delete($table, $where, $where_format) {
        global $wpdb;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        return $wpdb->delete($wpdb->prefix . $table, $where, $where_format);
    }
    
    /**
     * Wrapper for database get_results operations with caching
     * 
     * @param string $query The query to execute
     * @param string $output Optional. The required return type. One of OBJECT, ARRAY_A, or ARRAY_N, which correspond to 
     *                       an stdClass object, an associative array, or a numeric array, respectively. Default OBJECT.
     * @param int $cache_time Optional. The time in seconds to cache the results. Default 60.
     * @return array|object|null Database query results
     */
    private function db_get_results($query, $output = OBJECT, $cache_time = 60) {
        global $wpdb;
        
        // Generate a cache key for this query
        $cache_key = 'mementor_tts_db_results_' . md5($query . $output);
        
        // Try to get from cache first
        $results = wp_cache_get($cache_key, 'mementor_tts');
        
        if (false === $results) {
            // Perform the database operation
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared
            $results = $wpdb->get_results($query, $output);
            
            // Cache the results
            wp_cache_set($cache_key, $results, 'mementor_tts', $cache_time);
        }
        
        return $results;
    }
    
    /**
     * Wrapper for database get_row operations with caching
     * 
     * @param string $query The query to execute
     * @param string $output Optional. The required return type. One of OBJECT, ARRAY_A, or ARRAY_N, which correspond to 
     *                       an stdClass object, an associative array, or a numeric array, respectively. Default OBJECT.
     * @param int $cache_time Optional. The time in seconds to cache the result. Default 3600 (1 hour).
     * @return array|object|null|void Database query result
     */
    private function db_get_row($query, $output = OBJECT, $cache_time = 3600) {
        global $wpdb;
        
        // Generate a cache key for this query
        $cache_key = 'mementor_tts_db_row_' . md5($query . $output);
        
        // Try to get from cache first
        $result = wp_cache_get($cache_key, 'mementor_tts');
        
        if (false === $result) {
            // Perform the database operation
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared
            $result = $wpdb->get_row($query, $output);
            
            // Cache the result
            wp_cache_set($cache_key, $result, 'mementor_tts', $cache_time);
        }
        
        return $result;
    }
    
    /**
     * Wrapper for database get_var operations with caching
     * 
     * @param string $query The query to execute
     * @param int $cache_time Optional. The time in seconds to cache the result. Default 3600 (1 hour).
     * @return string|null Database query result
     */
    private function db_get_var($query, $cache_time = 3600) {
        global $wpdb;
        
        // Generate a cache key for this query
        $cache_key = 'mementor_tts_db_var_' . md5($query);
        
        // Try to get from cache first
        $result = wp_cache_get($cache_key, 'mementor_tts');
        $is_null_value = wp_cache_get($cache_key . '_is_null', 'mementor_tts');
        
        if (false === $result && false === $is_null_value) {
            // Perform the database operation
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared
            $result = $wpdb->get_var($query);
            
            // Cache the result (handle NULL values specially)
            if (null === $result) {
                wp_cache_set($cache_key, '', 'mementor_tts', $cache_time); // Empty string as placeholder
                wp_cache_set($cache_key . '_is_null', true, 'mementor_tts', $cache_time); // Flag to indicate NULL
            } else {
                wp_cache_set($cache_key, $result, 'mementor_tts', $cache_time);
            }
        }
        
        // If we have a NULL value flag, return NULL
        if (true === $is_null_value) {
            return null;
        }
        
        return $result;
    }
}