<?php
/**
 * Player Statistics tracking system
 *
 * @link       https://mementor.no
 * @since      1.4.2
 * @package    TTSWP
 * @subpackage Mementor_TTS/includes
 */

// Exit if accessed directly
if (!defined('ABSPATH')) {
    exit;
}

class Mementor_TTS_Player_Statistics {
    /**
     * Instance of the class
     */
    private static $instance = null;
    
    /**
     * Table name for player statistics
     */
    private $table_name;
    
    /**
     * Table name for aggregated statistics
     */
    private $aggregated_table_name;
    
    /**
     * Get instance
     */
    public static function get_instance() {
        if (null === self::$instance) {
            self::$instance = new self();
        }
        return self::$instance;
    }
    
    /**
     * Constructor
     */
    private function __construct() {
        global $wpdb;
        $this->table_name = $wpdb->prefix . 'mementor_tts_player_stats';
        $this->aggregated_table_name = $wpdb->prefix . 'mementor_tts_player_stats_aggregated';
        
        // Add AJAX actions for tracking
        add_action('wp_ajax_mementor_tts_track_player_event', array($this, 'track_player_event'));
        add_action('wp_ajax_nopriv_mementor_tts_track_player_event', array($this, 'track_player_event'));
        
        // Add batch tracking actions
        add_action('wp_ajax_mementor_tts_track_player_events_batch', array($this, 'track_player_events_batch'));
        add_action('wp_ajax_nopriv_mementor_tts_track_player_events_batch', array($this, 'track_player_events_batch'));
        
        // Add manual aggregation action (admin only)
        add_action('wp_ajax_mementor_tts_manual_aggregate_stats', array($this, 'manual_aggregate_stats'));
        
        // Add reset statistics action (admin only)
        add_action('wp_ajax_mementor_tts_reset_statistics', array($this, 'reset_statistics'));
        
        // Register the cron callback (must always be hooked so WP cron can call it)
        add_action('mementor_tts_aggregate_player_stats', array($this, 'aggregate_daily_stats'));

        // Only check scheduling on admin requests to avoid overhead on every frontend page load
        if (is_admin()) {
            add_action('admin_init', array($this, 'maybe_schedule_tasks'));
        }
    }

    /**
     * Schedule cron tasks if not already scheduled (called on admin_init)
     */
    public function maybe_schedule_tasks() {
        if (!wp_next_scheduled('mementor_tts_aggregate_player_stats')) {
            wp_schedule_event(strtotime('tomorrow 3am'), 'daily', 'mementor_tts_aggregate_player_stats');
        }
    }

    /**
     * Create player statistics tables
     */
    public function create_tables() {
        global $wpdb;
        
        $charset_collate = $wpdb->get_charset_collate();
        
        // Main player statistics table - stores individual events
        $sql1 = "CREATE TABLE IF NOT EXISTS {$this->table_name} (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            post_id bigint(20) NOT NULL,
            event_type varchar(50) NOT NULL,
            session_id varchar(64) NOT NULL,
            user_id bigint(20) DEFAULT NULL,
            event_data longtext DEFAULT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_post_id (post_id),
            KEY idx_event_type (event_type),
            KEY idx_session_id (session_id),
            KEY idx_created_at (created_at)
        ) $charset_collate;";
        
        // Aggregated statistics table
        $sql2 = "CREATE TABLE IF NOT EXISTS {$this->aggregated_table_name} (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            post_id bigint(20) NOT NULL,
            date date NOT NULL,
            player_initiated int(11) NOT NULL DEFAULT 0,
            play_clicks int(11) NOT NULL DEFAULT 0,
            pause_clicks int(11) NOT NULL DEFAULT 0,
            completion_count int(11) NOT NULL DEFAULT 0,
            downloads int(11) NOT NULL DEFAULT 0,
            total_play_time float NOT NULL DEFAULT 0,
            unique_sessions int(11) NOT NULL DEFAULT 0,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY idx_post_date (post_id, date),
            KEY idx_date (date)
        ) $charset_collate;";
        
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql1);
        dbDelta($sql2);
        
        // Store version for future updates
        update_option('mementor_tts_player_stats_db_version', '1.0', false);
    }
    
    /**
     * Track player event via AJAX
     */
    public function track_player_event() {
        // Check if statistics tracking is enabled
        if (!$this->is_statistics_enabled()) {
            wp_send_json_error('Statistics tracking is disabled');
        }
        
        // Verify nonce
        if (!isset($_POST['nonce']) || !wp_verify_nonce(sanitize_key(wp_unslash($_POST['nonce'])), 'mementor_tts_player_stats')) {
            wp_send_json_error('Invalid nonce');
        }

        // Get and validate data
        $post_id = isset($_POST['post_id']) ? absint($_POST['post_id']) : 0;
        $event_type = isset($_POST['event_type']) ? sanitize_text_field($_POST['event_type']) : '';
        $session_id = isset($_POST['session_id']) ? sanitize_text_field($_POST['session_id']) : '';
        $event_data = isset($_POST['event_data']) ? array_map('sanitize_text_field', (array) wp_unslash($_POST['event_data'])) : array();

        if (!$post_id || !$event_type || !$session_id) {
            wp_send_json_error('Missing required parameters');
        }
        
        // Validate event type
        $valid_events = array('player_init', 'play', 'pause', 'ended', 'download', 'progress', 'error');
        if (!in_array($event_type, $valid_events)) {
            wp_send_json_error('Invalid event type');
        }
        
        // Get user ID if logged in
        $user_id = is_user_logged_in() ? get_current_user_id() : null;
        
        // Log the event
        $this->log_event($post_id, $event_type, $session_id, $user_id, $event_data);
        
        wp_send_json_success('Event tracked');
    }
    
    /**
     * Track multiple player events in batch via AJAX
     */
    public function track_player_events_batch() {
        // Check if statistics tracking is enabled
        if (!$this->is_statistics_enabled()) {
            wp_send_json_error('Statistics tracking is disabled');
        }
        
        // Verify nonce
        if (!isset($_POST['nonce']) || !wp_verify_nonce(sanitize_key(wp_unslash($_POST['nonce'])), 'mementor_tts_player_stats')) {
            wp_send_json_error('Invalid nonce');
        }

        // Get events data
        $events_json = isset($_POST['events']) ? wp_unslash($_POST['events']) : '';
        if (empty($events_json)) {
            wp_send_json_error('No events provided');
        }
        
        // Decode events
        $events = json_decode($events_json, true);
        if (!is_array($events) || empty($events)) {
            wp_send_json_error('Invalid events data');
        }
        
        // Process each event
        $processed = 0;
        $errors = 0;
        
        foreach ($events as $event) {
            // Validate required fields
            if (!isset($event['post_id']) || !isset($event['event_type']) || !isset($event['session_id'])) {
                $errors++;
                continue;
            }
            
            $post_id = absint($event['post_id']);
            $event_type = sanitize_text_field($event['event_type']);
            $session_id = sanitize_text_field($event['session_id']);
            $event_data = isset($event['event_data']) ? array_map('sanitize_text_field', (array) $event['event_data']) : array();

            // Validate event type
            $valid_events = array('player_init', 'play', 'pause', 'ended', 'download', 'progress', 'error');
            if (!in_array($event_type, $valid_events)) {
                $errors++;
                continue;
            }
            
            // Get user ID if logged in
            $user_id = is_user_logged_in() ? get_current_user_id() : null;
            
            // Log the event
            $this->log_event($post_id, $event_type, $session_id, $user_id, $event_data);
            $processed++;
        }
        
        // Return response
        if ($processed > 0) {
            wp_send_json_success(array(
                'processed' => $processed,
                'errors' => $errors
            ));
        } else {
            wp_send_json_error('No events could be processed');
        }
    }
    
    /**
     * Log a player event
     */
    private function log_event($post_id, $event_type, $session_id, $user_id = null, $event_data = array()) {
        global $wpdb;
        
        // Prepare event data
        $data = array(
            'post_id' => $post_id,
            'event_type' => $event_type,
            'session_id' => $session_id,
            'user_id' => $user_id,
            'event_data' => wp_json_encode($event_data),
            'created_at' => current_time('mysql')
        );
        
        // Insert into database
        $result = $wpdb->insert(
            $this->table_name,
            $data,
            array('%d', '%s', '%s', '%d', '%s', '%s')
        );
        
        // Clear related caches if insert was successful
        if ($result !== false) {
            $this->clear_statistics_cache($post_id);
        }
    }
    
    /**
     * Clear statistics cache for a specific post or all posts
     */
    private function clear_statistics_cache($post_id = null) {
        global $wpdb;
        
        // Clear transients with wildcard pattern
        if ($post_id) {
            // Clear transients related to this specific post
            $wpdb->query($wpdb->prepare(
                "DELETE FROM {$wpdb->options} 
                WHERE option_name LIKE %s 
                OR option_name LIKE %s
                OR option_name LIKE %s",
                '_transient_mementor_tts_overall_stats_%',
                '_transient_mementor_tts_detailed_stats_%',
                '_transient_mementor_tts_stats_count_%'
            ));
        } else {
            // Clear all statistics transients
            $wpdb->query($wpdb->prepare(
                "DELETE FROM {$wpdb->options}
                WHERE option_name LIKE %s
                OR option_name LIKE %s
                OR option_name LIKE %s",
                '_transient_mementor_tts_overall_stats_%',
                '_transient_mementor_tts_detailed_stats_%',
                '_transient_mementor_tts_stats_count_%'
            ));
        }
    }
    
    /**
     * Get statistics for a specific post
     */
    public function get_post_statistics($post_id, $date_from = null, $date_to = null) {
        global $wpdb;
        
        $where_conditions = array("post_id = %d");
        $where_values = array($post_id);
        
        if ($date_from) {
            $where_conditions[] = "date >= %s";
            $where_values[] = $date_from;
        }
        
        if ($date_to) {
            $where_conditions[] = "date <= %s";
            $where_values[] = $date_to;
        }
        
        $where_clause = implode(' AND ', $where_conditions);
        
        // Get aggregated stats
        $stats = $wpdb->get_row($wpdb->prepare(
            "SELECT 
                SUM(player_initiated) as total_player_initiated,
                SUM(play_clicks) as total_play_clicks,
                SUM(pause_clicks) as total_pause_clicks,
                SUM(completion_count) as total_completion_count,
                SUM(downloads) as total_downloads,
                SUM(total_play_time) as total_play_time,
                SUM(unique_sessions) as total_unique_sessions
             FROM {$this->aggregated_table_name}
             WHERE {$where_clause}",
            ...$where_values
        ));
        
        // Calculate derived metrics
        if ($stats) {
            $stats->play_rate = $stats->total_player_initiated > 0 
                ? round(($stats->total_play_clicks / $stats->total_player_initiated) * 100, 2) 
                : 0;
                
            $stats->completion_rate = $stats->total_player_initiated > 0 
                ? round(($stats->total_completion_count / $stats->total_player_initiated) * 100, 2) 
                : 0;
                
            $stats->average_listening_time = $stats->total_play_clicks > 0 
                ? round($stats->total_play_time / $stats->total_play_clicks, 2) 
                : 0;
        }
        
        return $stats;
    }
    
    /**
     * Get overall statistics
     */
    public function get_overall_statistics($date_from = null, $date_to = null) {
        global $wpdb;
        
        // Generate cache key based on parameters
        $cache_key = 'mementor_tts_overall_stats_' . md5(serialize(array($date_from, $date_to)));
        
        // Try to get from cache first
        $cached_stats = get_transient($cache_key);
        if ($cached_stats !== false) {
            return $cached_stats;
        }
        
        $where_conditions = array("1=1");
        $where_values = array();
        
        if ($date_from) {
            $where_conditions[] = "date >= %s";
            $where_values[] = $date_from;
        }
        
        if ($date_to) {
            $where_conditions[] = "date <= %s";
            $where_values[] = $date_to;
        }
        
        $where_clause = implode(' AND ', $where_conditions);
        
        // Get overall stats
        $query = "SELECT 
                SUM(player_initiated) as total_player_initiated,
                SUM(play_clicks) as total_play_clicks,
                SUM(pause_clicks) as total_pause_clicks,
                SUM(completion_count) as total_completion_count,
                SUM(downloads) as total_downloads,
                SUM(total_play_time) as total_play_time,
                SUM(unique_sessions) as total_unique_sessions
             FROM {$this->aggregated_table_name}
             WHERE {$where_clause}";
        
        if (!empty($where_values)) {
            $stats = $wpdb->get_row($wpdb->prepare($query, ...$where_values));
        } else {
            $stats = $wpdb->get_row($query);
        }
        
        // Calculate derived metrics
        if ($stats) {
            $stats->play_rate = $stats->total_player_initiated > 0 
                ? round(($stats->total_play_clicks / $stats->total_player_initiated) * 100, 2) 
                : 0;
                
            $stats->completion_rate = $stats->total_player_initiated > 0 
                ? round(($stats->total_completion_count / $stats->total_player_initiated) * 100, 2) 
                : 0;
                
            $stats->average_listening_time = $stats->total_play_clicks > 0 
                ? round($stats->total_play_time / $stats->total_play_clicks, 2) 
                : 0;
        }
        
        // Cache for 15 minutes
        set_transient($cache_key, $stats, 15 * MINUTE_IN_SECONDS);
        
        return $stats;
    }
    
    /**
     * Get daily aggregated stats for chart rendering.
     *
     * @param string $date_from Y-m-d
     * @param string $date_to   Y-m-d
     * @return array Rows keyed by date with player_initiated, play_clicks, completion_count.
     */
    public function get_daily_chart_data($date_from, $date_to) {
        global $wpdb;

        $results = $wpdb->get_results($wpdb->prepare(
            "SELECT date,
                    SUM(player_initiated) as player_initiated,
                    SUM(play_clicks) as play_clicks,
                    SUM(completion_count) as completion_count
             FROM {$this->aggregated_table_name}
             WHERE date >= %s AND date <= %s
             GROUP BY date
             ORDER BY date ASC",
            $date_from,
            $date_to
        ));

        $data = array();
        if ($results) {
            foreach ($results as $row) {
                $data[$row->date] = $row;
            }
        }
        return $data;
    }

    /**
     * Get detailed statistics by post
     */
    public function get_detailed_statistics($limit = 10, $offset = 0, $date_from = null, $date_to = null) {
        global $wpdb;
        
        // Generate cache key based on parameters
        $cache_key = 'mementor_tts_detailed_stats_' . md5(serialize(array($limit, $offset, $date_from, $date_to)));
        
        // Try to get from cache first
        $cached_results = get_transient($cache_key);
        if ($cached_results !== false) {
            return $cached_results;
        }
        
        $where_conditions = array("1=1");
        $where_values = array();
        
        if ($date_from) {
            $where_conditions[] = "s.date >= %s";
            $where_values[] = $date_from;
        }
        
        if ($date_to) {
            $where_conditions[] = "s.date <= %s";
            $where_values[] = $date_to;
        }
        
        $where_clause = implode(' AND ', $where_conditions);
        
        // Get detailed stats grouped by post
        $query = "SELECT 
                s.post_id,
                p.post_title,
                SUM(s.player_initiated) as total_player_initiated,
                SUM(s.play_clicks) as total_play_clicks,
                SUM(s.pause_clicks) as total_pause_clicks,
                SUM(s.completion_count) as total_completion_count,
                SUM(s.downloads) as total_downloads,
                SUM(s.total_play_time) as total_play_time,
                SUM(s.unique_sessions) as total_unique_sessions
             FROM {$this->aggregated_table_name} s
             LEFT JOIN {$wpdb->posts} p ON s.post_id = p.ID
             WHERE {$where_clause}
             GROUP BY s.post_id
             ORDER BY total_play_clicks DESC
             LIMIT %d OFFSET %d";
        
        $where_values[] = $limit;
        $where_values[] = $offset;
        
        $results = $wpdb->get_results($wpdb->prepare($query, ...$where_values));
        
        // Check if the speeches table exists once before the loop
        $speeches_table = $wpdb->prefix . 'mementor_tts_speeches';
        $speeches_table_exists = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $speeches_table)) === $speeches_table;

        // Calculate derived metrics for each post
        foreach ($results as $result) {
            $result->average_listening_time = $result->total_play_clicks > 0
                ? $this->format_time($result->total_play_time / $result->total_play_clicks)
                : '0:00';

            $result->total_play_time_formatted = $this->format_time($result->total_play_time);

            if ($speeches_table_exists) {
                $audio_exists = $wpdb->get_var($wpdb->prepare(
                    "SELECT COUNT(*) FROM {$speeches_table} WHERE post_id = %d",
                    $result->post_id
                ));
                $result->has_audio = $audio_exists > 0;
            } else {
                $result->has_audio = false;
            }
        }
        
        // Cache for 15 minutes
        set_transient($cache_key, $results, 15 * MINUTE_IN_SECONDS);
        
        return $results;
    }
    
    /**
     * Format seconds to time string
     */
    private function format_time($seconds) {
        $seconds = (int) $seconds; // Cast to integer to avoid deprecation warnings
        if ($seconds < 60) {
            return sprintf('0:%02d', $seconds);
        } elseif ($seconds < 3600) {
            return sprintf('%d:%02d', floor($seconds / 60), $seconds % 60);
        } else {
            $hours = floor($seconds / 3600);
            $minutes = floor(($seconds % 3600) / 60);
            $seconds = $seconds % 60;
            return sprintf('%d:%02d:%02d', $hours, $minutes, $seconds);
        }
    }
    
    /**
     * Aggregate daily stats
     */
    public function aggregate_daily_stats() {
        global $wpdb;
        
        // Get yesterday's date
        $yesterday = date('Y-m-d', strtotime('-1 day'));
        
        // Check if already aggregated
        $exists = $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$this->aggregated_table_name} WHERE date = %s",
            $yesterday
        ));
        
        if ($exists > 0) {
            return; // Already aggregated
        }
        
        // Aggregate stats for each post
        $posts = $wpdb->get_results($wpdb->prepare(
            "SELECT DISTINCT post_id FROM {$this->table_name} 
             WHERE DATE(created_at) = %s",
            $yesterday
        ));
        
        foreach ($posts as $post) {
            $stats = $this->calculate_daily_stats_for_post($post->post_id, $yesterday);
            
            if ($stats) {
                $wpdb->insert(
                    $this->aggregated_table_name,
                    array(
                        'post_id' => $post->post_id,
                        'date' => $yesterday,
                        'player_initiated' => $stats['player_initiated'],
                        'play_clicks' => $stats['play_clicks'],
                        'pause_clicks' => $stats['pause_clicks'],
                        'completion_count' => $stats['completion_count'],
                        'downloads' => $stats['downloads'],
                        'total_play_time' => $stats['total_play_time'],
                        'unique_sessions' => $stats['unique_sessions']
                    ),
                    array('%d', '%s', '%d', '%d', '%d', '%d', '%d', '%f', '%d')
                );
            }
        }
        
        // Clean up old raw data (keep 30 days)
        $this->cleanup_old_data();
    }
    
    /**
     * Calculate daily stats for a specific post
     */
    private function calculate_daily_stats_for_post($post_id, $date) {
        global $wpdb;
        
        $stats = array(
            'player_initiated' => 0,
            'play_clicks' => 0,
            'pause_clicks' => 0,
            'completion_count' => 0,
            'downloads' => 0,
            'total_play_time' => 0,
            'unique_sessions' => 0
        );
        
        // Get event counts
        $events = $wpdb->get_results($wpdb->prepare(
            "SELECT 
                event_type,
                COUNT(*) as count,
                COUNT(DISTINCT session_id) as unique_sessions
             FROM {$this->table_name}
             WHERE post_id = %d AND DATE(created_at) = %s
             GROUP BY event_type",
            $post_id, $date
        ));
        
        foreach ($events as $event) {
            switch ($event->event_type) {
                case 'player_init':
                    $stats['player_initiated'] = $event->count;
                    $stats['unique_sessions'] = $event->unique_sessions;
                    break;
                case 'play':
                    $stats['play_clicks'] = $event->count;
                    break;
                case 'pause':
                    $stats['pause_clicks'] = $event->count;
                    break;
                case 'ended':
                    $stats['completion_count'] = $event->count;
                    break;
                case 'download':
                    $stats['downloads'] = $event->count;
                    break;
            }
        }
        
        // Calculate total play time from pause and ended events
        $play_time = $wpdb->get_var($wpdb->prepare(
            "SELECT SUM(
                CASE 
                    WHEN JSON_VALID(event_data) AND JSON_EXTRACT(event_data, '$.duration') IS NOT NULL
                    THEN CAST(JSON_EXTRACT(event_data, '$.duration') AS DECIMAL(10,2))
                    ELSE 0
                END
            )
             FROM {$this->table_name}
             WHERE post_id = %d AND DATE(created_at) = %s AND event_type IN ('pause', 'ended')",
            $post_id, $date
        ));
        
        $stats['total_play_time'] = $play_time ?: 0;
        
        return $stats;
    }
    
    /**
     * Clean up old raw data
     */
    private function cleanup_old_data() {
        global $wpdb;
        
        // Keep raw data for 30 days
        $cutoff_date = date('Y-m-d H:i:s', strtotime('-30 days'));
        
        $wpdb->query($wpdb->prepare(
            "DELETE FROM {$this->table_name} WHERE created_at < %s",
            $cutoff_date
        ));
        
        // Keep aggregated data for 1 year
        $yearly_cutoff = date('Y-m-d', strtotime('-1 year'));
        
        $wpdb->query($wpdb->prepare(
            "DELETE FROM {$this->aggregated_table_name} WHERE date < %s",
            $yearly_cutoff
        ));
    }
    
    // Scheduling moved to constructor + maybe_schedule_tasks()
    
    /**
     * Get count of detailed statistics
     */
    public function get_detailed_statistics_count($date_from = null, $date_to = null) {
        global $wpdb;
        
        // Generate cache key based on parameters
        $cache_key = 'mementor_tts_stats_count_' . md5(serialize(array($date_from, $date_to)));
        
        // Try to get from cache first
        $cached_count = get_transient($cache_key);
        if ($cached_count !== false) {
            return $cached_count;
        }
        
        $where_conditions = array("1=1");
        $where_values = array();
        
        if ($date_from) {
            $where_conditions[] = "date >= %s";
            $where_values[] = $date_from;
        }
        
        if ($date_to) {
            $where_conditions[] = "date <= %s";
            $where_values[] = $date_to;
        }
        
        $where_clause = implode(' AND ', $where_conditions);
        
        $query = "SELECT COUNT(DISTINCT post_id) FROM {$this->aggregated_table_name} WHERE {$where_clause}";
        
        if (!empty($where_values)) {
            $count = $wpdb->get_var($wpdb->prepare($query, ...$where_values));
        } else {
            $count = $wpdb->get_var($query);
        }
        
        // Cache for 15 minutes
        set_transient($cache_key, $count, 15 * MINUTE_IN_SECONDS);
        
        return $count;
    }
    
    /**
     * Check if statistics tracking is enabled
     * 
     * @return bool
     */
    private function is_statistics_enabled() {
        return get_option('mementor_tts_enable_statistics', 'no') === 'yes';
    }
    
    /**
     * Manually trigger statistics aggregation via AJAX
     */
    public function manual_aggregate_stats() {
        // Check nonce
        if (!isset($_POST['nonce']) || !wp_verify_nonce(sanitize_key(wp_unslash($_POST['nonce'])), 'mementor_tts_admin_nonce')) {
            wp_send_json_error('Invalid nonce');
        }

        // Check user capabilities
        if (!current_user_can('manage_options')) {
            wp_send_json_error('Insufficient permissions');
        }

        // Aggregate today's stats instead of yesterday's for immediate results
        $this->aggregate_stats_for_date(date('Y-m-d'));
        
        // Clear all statistics caches
        $this->clear_statistics_cache();
        
        wp_send_json_success(array(
            'message' => __('Statistics have been updated successfully.', 'text-to-speech-tts')
        ));
    }
    
    /**
     * Aggregate stats for a specific date
     */
    public function aggregate_stats_for_date($date) {
        global $wpdb;
        
        // Check if already aggregated
        $exists = $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$this->aggregated_table_name} WHERE date = %s",
            $date
        ));
        
        if ($exists > 0) {
            // Delete existing aggregation to re-aggregate
            $wpdb->delete($this->aggregated_table_name, array('date' => $date), array('%s'));
        }
        
        // Aggregate stats for each post
        $posts = $wpdb->get_results($wpdb->prepare(
            "SELECT DISTINCT post_id FROM {$this->table_name} 
             WHERE DATE(created_at) = %s",
            $date
        ));
        
        foreach ($posts as $post) {
            $stats = $this->calculate_daily_stats_for_post($post->post_id, $date);
            
            if ($stats) {
                $wpdb->insert(
                    $this->aggregated_table_name,
                    array(
                        'post_id' => $post->post_id,
                        'date' => $date,
                        'player_initiated' => $stats['player_initiated'],
                        'play_clicks' => $stats['play_clicks'],
                        'pause_clicks' => $stats['pause_clicks'],
                        'completion_count' => $stats['completion_count'],
                        'downloads' => $stats['downloads'],
                        'total_play_time' => $stats['total_play_time'],
                        'unique_sessions' => $stats['unique_sessions']
                    ),
                    array('%d', '%s', '%d', '%d', '%d', '%d', '%d', '%f', '%d')
                );
            }
        }
    }
    
    /**
     * Reset all statistics data via AJAX
     */
    public function reset_statistics() {
        // Check nonce
        if (!isset($_POST['nonce']) || !wp_verify_nonce(sanitize_key(wp_unslash($_POST['nonce'])), 'mementor_tts_admin_nonce')) {
            wp_send_json_error('Invalid nonce');
        }

        // Check user capabilities
        if (!current_user_can('manage_options')) {
            wp_send_json_error('Insufficient permissions');
        }

        global $wpdb;

        // Clear all data from statistics tables. Table names are class properties built from $wpdb->prefix + constant suffix — not user input.
        // TRUNCATE does not support placeholders for identifiers, and caching/schema-change warnings don't apply to an admin-triggered cleanup action.
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
        $wpdb->query("TRUNCATE TABLE {$this->table_name}");
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
        $wpdb->query("TRUNCATE TABLE {$this->aggregated_table_name}");
        
        // Clear all statistics caches
        $this->clear_statistics_cache();
        
        // Clear all transients with statistics data
        $wpdb->query($wpdb->prepare(
            "DELETE FROM {$wpdb->options}
            WHERE option_name LIKE %s
            OR option_name LIKE %s",
            '_transient_mementor_tts_%stats%',
            '_transient_timeout_mementor_tts_%stats%'
        ));
        
        wp_send_json_success(array(
            'message' => __('All statistics have been reset successfully.', 'text-to-speech-tts')
        ));
    }
}