<?php
if (!defined('ABSPATH')) exit;

class Botami_Chat_Messages {
    private static $instance = null;
    private $table_name;
    private $cache_group = 'botamichat_messages';
    private $messages_cache_time = 3600; // 1 hour cache

    public function __construct() {
        global $wpdb;
        $this->table_name = $wpdb->prefix . 'botamichat_messages';
        wp_cache_add_non_persistent_groups($this->cache_group);
    }

    public static function getInstance() {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        return self::$instance;
    }

    public function create_table() {
        global $wpdb;
        $charset_collate = $wpdb->get_charset_collate();
        
        $sql = "CREATE TABLE IF NOT EXISTS {$this->table_name} (
                id BIGINT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                user_id BIGINT(20) UNSIGNED DEFAULT NULL,
                session_id VARCHAR(40) DEFAULT NULL,
                source ENUM('user', 'assistant') NOT NULL,
                message TEXT NOT NULL,
                timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                KEY idx_user_id (user_id),
                KEY idx_session_id (session_id)
            ) {$charset_collate}";

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

    public function save_chat_message($message, $source = 'user') {
        try {
            global $wpdb;
            
            $data = [
                'source' => $source,
                'message' => $message,
                'timestamp' => current_time('mysql'),
            ];
            
            $format = [
                'source' => '%s',
                'message' => '%s',
                'timestamp' => '%s'
            ];
            
            if (is_user_logged_in()) {
                $data['user_id'] = get_current_user_id();
                $format['user_id'] = '%d';
                $cache_key = 'user_' . $data['user_id'];
            } else {
                $session_handler = Botami_Session_Handler::getInstance();
                $data['session_id'] = $session_handler->get_session_id();
                $format['session_id'] = '%s';
                $cache_key = 'session_' . $data['session_id'];
            }
            
            $result = $wpdb->insert($this->table_name, $data, $format);
            
            if ($result === false) {
                throw new Exception($wpdb->last_error);
            }

            // Clear cache for this user/session
            wp_cache_delete($cache_key, $this->cache_group);
            
            return $wpdb->insert_id;
            
        } catch (Exception $e) {
            if (defined('WP_DEBUG') && WP_DEBUG) {
                error_log('Botami_Chat_Messages save_chat_message error: ' . $e->getMessage());
            }
            throw $e;
        }
    }

    public function get_chat_messages() {
        global $wpdb;
        
        if (is_user_logged_in()) {
            $user_id = get_current_user_id();
            $cache_key = 'user_' . $user_id;
            
            // Try to get from cache first
            $messages = wp_cache_get($cache_key, $this->cache_group);
            
            if ($messages === false) {
                $query = $wpdb->prepare(
                    "SELECT source, message, timestamp 
                    FROM {$this->table_name} 
                    WHERE user_id = %d 
                    ORDER BY timestamp ASC",
                    $user_id
                );
                
                $messages = $wpdb->get_results($query);
                
                // Cache the results
                wp_cache_set($cache_key, $messages, $this->cache_group, $this->messages_cache_time);
            }
        } else {
            $session_handler = Botami_Session_Handler::getInstance();
            $session_id = $session_handler->get_session_id();
            $cache_key = 'session_' . $session_id;
            
            // Try to get from cache first
            $messages = wp_cache_get($cache_key, $this->cache_group);
            
            if ($messages === false) {
                $query = $wpdb->prepare(
                    "SELECT source, message, timestamp 
                    FROM {$this->table_name} 
                    WHERE session_id = %s 
                    ORDER BY timestamp ASC",
                    $session_id
                );
                
                $messages = $wpdb->get_results($query);
                
                // Cache the results
                wp_cache_set($cache_key, $messages, $this->cache_group, $this->messages_cache_time);
            }
        }
        
        return $messages;
    }

    public function has_messages() {
        global $wpdb;
        
        if (is_user_logged_in()) {
            $user_id = get_current_user_id();
            $cache_key = 'has_messages_user_' . $user_id;
            
            // Try to get from cache first
            $has_messages = wp_cache_get($cache_key, $this->cache_group);
            
            if ($has_messages === false) {
                $query = $wpdb->prepare(
                    "SELECT COUNT(*) FROM {$this->table_name} WHERE user_id = %d",
                    $user_id
                );
                
                $count = $wpdb->get_var($query);
                $has_messages = $count > 0;
                
                // Cache the result
                wp_cache_set($cache_key, $has_messages, $this->cache_group, $this->messages_cache_time);
            }
        } else {
            $session_handler = Botami_Session_Handler::getInstance();
            $session_id = $session_handler->get_session_id();
            $cache_key = 'has_messages_session_' . $session_id;
            
            // Try to get from cache first
            $has_messages = wp_cache_get($cache_key, $this->cache_group);
            
            if ($has_messages === false) {
                $query = $wpdb->prepare(
                    "SELECT COUNT(*) FROM {$this->table_name} WHERE session_id = %s",
                    $session_id
                );
                
                $count = $wpdb->get_var($query);
                $has_messages = $count > 0;
                
                // Cache the result
                wp_cache_set($cache_key, $has_messages, $this->cache_group, $this->messages_cache_time);
            }
        }
        
        return $has_messages;
    }

    public function export_messages_to_csv($start_date, $end_date) {
        global $wpdb;
        
        $start_datetime = $this->normalize_export_date($start_date);
        $end_datetime = $this->normalize_export_date($end_date);

        if (!$start_datetime || !$end_datetime) {
            return false;
        }
        
        // Format dates for MySQL
        $start_date = $start_datetime->format('Y-m-d') . ' 00:00:00';
        $end_date = $end_datetime->format('Y-m-d') . ' 23:59:59';
        
        // First, get unique conversations with incremental IDs
        $conversations_query = $wpdb->prepare(
            "SELECT 
                @row_number:=@row_number+1 as conversation_id,
                user_id,
                session_id,
                MIN(timestamp) as start_time
            FROM {$this->table_name}, 
            (SELECT @row_number:=0) AS r
            WHERE timestamp BETWEEN %s AND %s
            GROUP BY 
                CASE 
                    WHEN user_id IS NOT NULL THEN user_id
                    ELSE session_id
                END
            ORDER BY start_time ASC",
            $start_date,
            $end_date
        );
        
        // Create a temporary table for conversation IDs
        $wpdb->query("CREATE TEMPORARY TABLE IF NOT EXISTS temp_conversations " . $conversations_query);
        
        // Get all messages with conversation IDs
        $query = $wpdb->prepare(
            "SELECT 
                tc.conversation_id,
                m.source,
                m.message,
                m.timestamp
            FROM {$this->table_name} m
            JOIN temp_conversations tc ON 
                CASE 
                    WHEN m.user_id IS NOT NULL THEN m.user_id = tc.user_id
                    ELSE m.session_id = tc.session_id
                END
            WHERE m.timestamp BETWEEN %s AND %s
            ORDER BY tc.conversation_id ASC, m.timestamp ASC",
            $start_date,
            $end_date
        );
        
        $results = $wpdb->get_results($query, ARRAY_A);
        
        // Clean up temporary table
        $wpdb->query("DROP TEMPORARY TABLE IF EXISTS temp_conversations");
        
        if (empty($results)) {
            return false;
        }

        // Use WP_Filesystem for file operations
        require_once(ABSPATH . 'wp-admin/includes/file.php');
        WP_Filesystem();
        global $wp_filesystem;

        // Create temporary file using WP_Filesystem
        $temp_dir = get_temp_dir();
        $temp_file = $temp_dir . 'chat_export_' . uniqid() . '.csv';

        // Prepare CSV content with original format
        $csv_content = "";
        
        // Add headers in original format
        $headers = array('Conversation ID', 'Source', 'Message', 'Timestamp');
        $csv_content .= implode(',', array_map(array($this, 'escape_csv'), $headers)) . "\n";
        
        // Add data rows in original format
        foreach ($results as $row) {
            $csv_content .= implode(',', array_map(array($this, 'escape_csv'), array(
                $row['conversation_id'],
                $row['source'],
                $row['message'],
                $row['timestamp']
            ))) . "\n";
        }

        // Write to file using WP_Filesystem
        $wp_filesystem->put_contents($temp_file, $csv_content, FS_CHMOD_FILE);

        // Read the content back
        $final_content = $wp_filesystem->get_contents($temp_file);

        // Clean up
        $wp_filesystem->delete($temp_file);

        return $final_content;
    }

    private function escape_csv($field) {
        $field = str_replace('"', '""', $field); 
        return '"' . $field . '"';
    }

    private function normalize_export_date($date_string) {
        $date_string = trim((string) $date_string);
        if ($date_string === '') {
            return null;
        }

        $formats = ['m/d/Y', 'Y-m-d', 'd/m/Y'];
        foreach ($formats as $format) {
            $date = DateTime::createFromFormat($format, $date_string);
            if ($date instanceof DateTime && $date->format($format) === $date_string) {
                return $date;
            }
        }

        return null;
    }
}
