<?php

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

class AIEO_Chat_Analytics {

    
    const STATS_CACHE_OPTION = 'aieo_chat_analytics_stats_cache';
    const STATS_REFRESH_HOOK = 'aieo_chat_analytics_stats_refresh';

    
    public static function get_cached_stats() {
        $c = get_option(self::STATS_CACHE_OPTION, null);
        return (is_array($c) && isset($c['overview']) && is_array($c['overview'])) ? $c : null;
    }

    
    public static function refresh_stats_cache() {
        $payload = array(
            'overview'     => self::build_overview(),
            'products'     => self::build_top_products(50),
            'conversions'  => self::build_conversions(),
            'generated_at' => current_time('mysql', true), 
        );
        update_option(self::STATS_CACHE_OPTION, $payload, false);

        
        
        
        if (!class_exists('AIEO_SEO_Content_Gaps') && defined('AIEO_DIR')) {
            require_once AIEO_DIR . 'includes/seo/class-aieo-seo-content-gaps.php';
        }
        if (class_exists('AIEO_SEO_Content_Gaps')) {
            AIEO_SEO_Content_Gaps::rebuild();
            
            
            
            
            
            
            
            
            if (method_exists('AIEO_SEO_Content_Gaps', 'resolve_mappings')) {
                AIEO_SEO_Content_Gaps::resolve_mappings();
            }
        }
        return $payload;
    }

    
    
    

    
    private static function build_overview() {
        global $wpdb;
        $h   = self::chat_table();
        $out = array(
            'turns' => 0, 'conversations' => 0, 'sessions' => 0,
            'logged_in' => 0, 'guest' => 0, 'first_at' => '', 'last_at' => '',
            'avg_rating' => null, 'rated_turns' => 0, 'zero_product_turns' => 0,
            'avg_tokens_in' => null, 'avg_tokens_out' => null, 'max_tokens_out' => null,
            'truncated_turns' => 0,
            'trend' => array(),
        );
        if (!self::table_exists($h)) { return $out; }

        
        
        
        if (!class_exists('AI_eShop_Chat_Database_Manager') && defined('AIEO_DIR')) {
            require_once AIEO_DIR . 'includes/chat/class-database-manager.php';
        }
        if (class_exists('AI_eShop_Chat_Database_Manager')) {
            AI_eShop_Chat_Database_Manager::ensure_chat_history_columns();
        }

        $row = $wpdb->get_row(
            "SELECT
                COUNT(*)                                                                     AS turns,
                COUNT(DISTINCT conversation_id)                                              AS conversations,
                COUNT(DISTINCT session_id)                                                   AS sessions,
                SUM(CASE WHEN user_id > 0 THEN 1 ELSE 0 END)                                 AS logged_in,
                SUM(CASE WHEN user_id IS NULL OR user_id = 0 THEN 1 ELSE 0 END)              AS guest,
                MIN(created_at)                                                              AS first_at,
                MAX(created_at)                                                              AS last_at,
                AVG(user_rating)                                                             AS avg_rating,
                SUM(CASE WHEN user_rating IS NOT NULL THEN 1 ELSE 0 END)                     AS rated_turns,
                SUM(CASE WHEN products_found IS NULL OR products_found = '' OR products_found = '[]' THEN 1 ELSE 0 END) AS zero_product_turns,
                AVG(tokens_in)                                                               AS avg_tokens_in,
                AVG(tokens_out)                                                              AS avg_tokens_out,
                MAX(tokens_out)                                                              AS max_tokens_out,
                SUM(CASE WHEN stop_reason IN ('max_tokens','length') THEN 1 ELSE 0 END)      AS truncated_turns
             FROM {$h}",
            ARRAY_A
        );
        if (is_array($row)) {
            $out['turns']              = (int) $row['turns'];
            $out['conversations']      = (int) $row['conversations'];
            $out['sessions']           = (int) $row['sessions'];
            $out['logged_in']          = (int) $row['logged_in'];
            $out['guest']              = (int) $row['guest'];
            $out['first_at']           = (string) $row['first_at'];
            $out['last_at']            = (string) $row['last_at'];
            $out['rated_turns']        = (int) $row['rated_turns'];
            $out['zero_product_turns'] = (int) $row['zero_product_turns'];
            $out['avg_rating']         = ($row['avg_rating'] !== null && (int) $row['rated_turns'] > 0)
                ? round((float) $row['avg_rating'], 1) : null;
            
            
            $out['avg_tokens_in']  = isset($row['avg_tokens_in'])  && $row['avg_tokens_in']  !== null ? (int) round((float) $row['avg_tokens_in'])  : null;
            $out['avg_tokens_out'] = isset($row['avg_tokens_out']) && $row['avg_tokens_out'] !== null ? (int) round((float) $row['avg_tokens_out']) : null;
            $out['max_tokens_out'] = isset($row['max_tokens_out']) && $row['max_tokens_out'] !== null ? (int) $row['max_tokens_out'] : null;
            $out['truncated_turns'] = isset($row['truncated_turns']) ? (int) $row['truncated_turns'] : 0;
        }

        
        $since = gmdate('Y-m-d 00:00:00', time() - 29 * DAY_IN_SECONDS);
        $trend_rows = $wpdb->get_results($wpdb->prepare(
            "SELECT DATE(created_at) AS d, COUNT(*) AS n FROM {$h} WHERE created_at >= %s GROUP BY DATE(created_at) ORDER BY d ASC",
            $since
        ), ARRAY_A);
        $by_day = array();
        foreach ((array) $trend_rows as $r) { $by_day[(string) $r['d']] = (int) $r['n']; }
        $trend = array();
        for ($i = 29; $i >= 0; $i--) {
            $d = gmdate('Y-m-d', time() - $i * DAY_IN_SECONDS);
            $trend[] = array('d' => $d, 'n' => isset($by_day[$d]) ? $by_day[$d] : 0);
        }
        $out['trend'] = $trend;

        return $out;
    }

    
    private static function build_top_products($limit = 50) {
        global $wpdb;
        $h = self::chat_table();
        if (!self::table_exists($h)) { return array(); }

        $counts = array();
        $offset = 0;
        $chunk  = 2000;
        $guard  = 0;
        do {
            $rows = $wpdb->get_col($wpdb->prepare(
                "SELECT products_found FROM {$h}
                 WHERE products_found IS NOT NULL AND products_found <> '' AND products_found <> '[]'
                 ORDER BY id ASC LIMIT %d OFFSET %d",
                $chunk, $offset
            ));
            foreach ((array) $rows as $json) {
                $ids = json_decode((string) $json, true);
                if (is_array($ids)) {
                    foreach ($ids as $pid) {
                        $pid = (int) $pid;
                        if ($pid > 0) { $counts[$pid] = isset($counts[$pid]) ? $counts[$pid] + 1 : 1; }
                    }
                }
            }
            $offset += $chunk;
            $guard++;
        } while (is_array($rows) && count($rows) === $chunk && $guard < 500);

        if (empty($counts)) { return array(); }
        arsort($counts);
        $top = array_slice($counts, 0, $limit, true);

        $products = array();
        foreach ($top as $pid => $cnt) {
            $title = get_the_title($pid);
            if ($title === '' || $title === null) { $title = '#' . $pid; }
            $products[] = array('post_id' => $pid, 'title' => $title, 'recommended' => (int) $cnt);
        }
        return $products;
    }

    
    private static function build_conversions() {
        global $wpdb;
        $out = array('available' => false, 'chat_sessions' => 0, 'converted' => 0, 'revenue' => 0.0, 'aov' => 0.0, 'rate' => 0.0);
        $h  = self::chat_table();
        $ss = self::statics_table();
        if (!self::table_exists($h) || !self::table_exists($ss)) { return $out; }
        $out['available'] = true;

        
        $out['chat_sessions'] = (int) $wpdb->get_var("SELECT COUNT(DISTINCT session_id) FROM {$h} WHERE session_id <> ''");

        
        
        
        $h_coll  = self::column_collation($h, 'session_id');
        $ss_coll = self::column_collation($ss, 'session_id');
        $cast    = '';
        if ($h_coll !== '' && $ss_coll !== '' && $h_coll !== $ss_coll && preg_match('/^[A-Za-z0-9_]+$/', $ss_coll)) {
            $cast = ' COLLATE ' . $ss_coll;
        }

        
        
        $wpdb->suppress_errors(true);
        $row = $wpdb->get_row(
            "SELECT COUNT(DISTINCT ss.session_id) AS converted, COALESCE(SUM(ss.order_total), 0) AS revenue
             FROM (SELECT DISTINCT session_id FROM {$h} WHERE session_id <> '') ch
             JOIN {$ss} ss ON ss.session_id = ch.session_id{$cast}
             WHERE ss.order_id IS NOT NULL",
            ARRAY_A
        );
        $wpdb->suppress_errors(false);

        if (is_array($row)) {
            $out['converted'] = (int) $row['converted'];
            $out['revenue']   = (float) $row['revenue'];
            $out['aov']       = $out['converted'] > 0 ? round($out['revenue'] / $out['converted'], 2) : 0.0;
            $out['rate']      = $out['chat_sessions'] > 0 ? round(100 * $out['converted'] / $out['chat_sessions'], 1) : 0.0;
        }
        return $out;
    }

    
    
    

    private static function chat_table()    { global $wpdb; return $wpdb->prefix . 'aieo_chat_history'; }
    private static function statics_table() { global $wpdb; return $wpdb->prefix . 'aieo_sessions_statics'; }

    private static function table_exists($name) {
        global $wpdb;
        static $cache = array();
        if (!array_key_exists($name, $cache)) {
            $cache[$name] = ((string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $name)) === $name);
        }
        return $cache[$name];
    }

    
    private static function column_collation($table, $col) {
        global $wpdb;
        $v = $wpdb->get_var($wpdb->prepare(
            "SELECT COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = %s",
            DB_NAME, $table, $col
        ));
        return $v ? (string) $v : '';
    }
}