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

/**
 * Admin Inbox & Analytics Handler
 *
 * Handles inbox conversation APIs and analytics endpoints.
 *
 * @package    Kitsune_AI_Platform
 * @subpackage Kitsune_AI_Platform/admin
 */
class Kitsune_AI_Platform_Admin_Inbox_Analytics extends Kitsune_AI_Platform_Admin_Ajax_Base {

    /**
     * Register AJAX hooks.
     */
    public function register_hooks() {
        add_action('wp_ajax_kitsune_ai_platform_inbox_get_conversations', array($this, 'ajax_inbox_get_conversations'));
        add_action('wp_ajax_kitsune_ai_platform_inbox_get_messages', array($this, 'ajax_inbox_get_messages'));
        add_action('wp_ajax_kitsune_ai_platform_inbox_delete_conversations', array($this, 'ajax_inbox_delete_conversations'));
        add_action('wp_ajax_kitsune_ai_platform_get_analytics', array($this, 'ajax_get_analytics'));
    }

    /**
     * AJAX: Get inbox conversations
     */
    public function ajax_inbox_get_conversations() {
        if (!$this->verify_admin_request('analytics')) {
            return;
        }

        $page = isset($_GET['page']) ? max(1, intval(wp_unslash($_GET['page']))) : 1;
        $per_page = isset($_GET['per_page']) ? min(100, max(1, intval(wp_unslash($_GET['per_page'])))) : 20;
        $search = isset($_GET['search']) ? sanitize_text_field(wp_unslash($_GET['search'])) : '';
        $offset = ($page - 1) * $per_page;

        global $wpdb;
        $table = $wpdb->prefix . 'kitsune_ai_platform_conversations';
        $posts_table = $wpdb->posts;

        $sql = "SELECT c.*, p.post_title AS chatbot_title
                FROM {$table} c
                LEFT JOIN {$posts_table} p ON c.chatbot_id = p.ID
                WHERE 1=1";
        $params = array();

        if (!empty($search)) {
            $like = '%' . $wpdb->esc_like($search) . '%';
            $sql .= " AND (c.messages LIKE %s OR c.user_identifier LIKE %s OR p.post_title LIKE %s)";
            $params[] = $like;
            $params[] = $like;
            $params[] = $like;
        }

        $sql .= " ORDER BY c.updated_at DESC LIMIT %d OFFSET %d";
        $params[] = $per_page;
        $params[] = $offset;

        $rows = $wpdb->get_results($wpdb->prepare($sql, $params));
        $conversations = array();
        foreach ($rows as $row) {
            $conversations[] = $this->format_conversation_row($row);
        }

        $count_sql = "SELECT COUNT(*) FROM {$table} c LEFT JOIN {$posts_table} p ON c.chatbot_id = p.ID WHERE 1=1";
        $count_params = array();
        if (!empty($search)) {
            $like = '%' . $wpdb->esc_like($search) . '%';
            $count_sql .= " AND (c.messages LIKE %s OR c.user_identifier LIKE %s OR p.post_title LIKE %s)";
            $count_params[] = $like;
            $count_params[] = $like;
            $count_params[] = $like;
        }

        if (!empty($count_params)) {
            $total = (int) $wpdb->get_var($wpdb->prepare($count_sql, $count_params));
        } else {
            $total = (int) $wpdb->get_var($count_sql);
        }

        wp_send_json_success(array(
            'conversations' => $conversations,
            'total' => $total
        ));
    }

    /**
     * AJAX: Get inbox messages
     */
    public function ajax_inbox_get_messages() {
        if (!$this->verify_admin_request('analytics')) {
            return;
        }

        $id = isset($_GET['id']) ? intval(wp_unslash($_GET['id'])) : 0;
        if (!$id) {
            wp_send_json_error(array('message' => __('Conversation ID required', 'kitsune-ai-platform')));
            return;
        }

        $page = isset($_GET['page']) ? max(1, intval(wp_unslash($_GET['page']))) : 1;
        $per_page = isset($_GET['per_page']) ? min(200, max(1, intval(wp_unslash($_GET['per_page'])))) : 50;
        $offset = ($page - 1) * $per_page;

        global $wpdb;
        $table = $wpdb->prefix . 'kitsune_ai_platform_conversations';
        $row = $wpdb->get_row($wpdb->prepare("SELECT messages, created_at FROM {$table} WHERE id = %d", $id));
        if (!$row) {
            wp_send_json_error(array('message' => __('Conversation not found', 'kitsune-ai-platform')));
            return;
        }

        $messages = $this->normalize_conversation_messages(
            !empty($row->messages) ? json_decode($row->messages, true) : array(),
            mysql2date('c', $row->created_at)
        );

        $total = count($messages);
        $paged = array_slice($messages, $offset, $per_page);

        wp_send_json_success(array(
            'messages' => $paged,
            'total' => $total
        ));
    }

    /**
     * AJAX: Delete inbox conversations
     */
    public function ajax_inbox_delete_conversations() {
        if (!$this->verify_admin_request('analytics')) {
            return;
        }

        $ids = (array) $this->get_post_payload('ids', (array) ($_POST['ids'] ?? array()));
        $ids = array_filter(array_map('intval', $ids));

        if (empty($ids)) {
            wp_send_json_error(array('message' => __('IDs required', 'kitsune-ai-platform')));
            return;
        }

        global $wpdb;
        $table = $wpdb->prefix . 'kitsune_ai_platform_conversations';
        $placeholders = implode(',', array_fill(0, count($ids), '%d'));
        $wpdb->query($wpdb->prepare("DELETE FROM {$table} WHERE id IN ({$placeholders})", $ids));
        delete_transient($this->get_dashboard_stats_cache_key());

        wp_send_json_success(array('message' => __('Conversations deleted', 'kitsune-ai-platform')));
    }

    /**
     * AJAX: Get analytics
     */
    public function ajax_get_analytics() {
        if (!$this->verify_admin_request('analytics')) {
            return;
        }

        $period = isset($_GET['period']) ? sanitize_text_field(wp_unslash($_GET['period'])) : '30days';
        $start_date = isset($_GET['start_date']) ? sanitize_text_field(wp_unslash($_GET['start_date'])) : '';
        $end_date = isset($_GET['end_date']) ? sanitize_text_field(wp_unslash($_GET['end_date'])) : '';

        global $wpdb;
        $api_logs_table = $wpdb->prefix . 'kitsune_ai_platform_api_logs';
        $conversations_table = $wpdb->prefix . 'kitsune_ai_platform_conversations';

        $where_sql = "1=1";
        $params = array();

        // Handle date filtering
        $date_field = 'created_at';
        if ($period === '7days') {
            $where_sql .= " AND $date_field >= %s";
            $params[] = date('Y-m-d H:i:s', strtotime('-7 days'));
        } elseif ($period === '30days') {
            $where_sql .= " AND $date_field >= %s";
            $params[] = date('Y-m-d H:i:s', strtotime('-30 days'));
        } elseif ($period === '90days') {
            $where_sql .= " AND $date_field >= %s";
            $params[] = date('Y-m-d H:i:s', strtotime('-90 days'));
        } elseif ($period === 'thisMonth') {
            $where_sql .= " AND $date_field >= %s";
            $params[] = date('Y-m-01 00:00:00');
        } elseif ($period === 'lastMonth') {
            $where_sql .= " AND $date_field >= %s AND $date_field <= %s";
            $params[] = date('Y-m-01 00:00:00', strtotime('first day of last month'));
            $params[] = date('Y-m-t 23:59:59', strtotime('last day of last month'));
        } elseif ($period === 'thisYear') {
            $where_sql .= " AND $date_field >= %s";
            $params[] = date('Y-01-01 00:00:00');
        }

        $start_ts = !empty($start_date) ? strtotime($start_date) : false;
        if (!empty($start_ts)) {
            $where_sql .= " AND $date_field >= %s";
            $params[] = date('Y-m-d 00:00:00', $start_ts);
        }

        $end_ts = !empty($end_date) ? strtotime($end_date) : false;
        if (!empty($end_ts)) {
            $where_sql .= " AND $date_field <= %s";
            $params[] = date('Y-m-d 23:59:59', $end_ts);
        }


        // --- Usage Over Time (Chart Data) ---
        $chart_labels = array();
        $chart_counts = array();
        $chart_results = $wpdb->get_results(
            $this->prepare_optional(
                "SELECT DATE(created_at) as d, COUNT(*) as c FROM {$api_logs_table} WHERE {$where_sql} GROUP BY d ORDER BY d ASC LIMIT 90", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $params
            )
        );
        foreach ($chart_results as $row) {
            $chart_labels[] = date('M d', strtotime($row->d));
            $chart_counts[] = (int)$row->c;
        }

        if ($period === 'dashboard') {
            $cache_key = $this->get_dashboard_stats_cache_key();
            $cached_stats = get_transient($cache_key);
            
            $start_of_month = date('Y-m-01 00:00:00', current_time('timestamp'));

            $total_requests = (int) $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT COUNT(*) FROM {$api_logs_table} WHERE created_at >= %s",
                    $start_of_month
                )
            );

            $total_tokens_used = (int) $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT COALESCE(SUM(tokens_input + tokens_output), 0) FROM {$api_logs_table} WHERE created_at >= %s",
                    $start_of_month
                )
            );

            $chatbot_conversations = (int) $wpdb->get_var(
                "SELECT COUNT(*) FROM {$conversations_table}" // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            );

            $content_model = new Kitsune_AI_Platform_Content_Model();
            $chatbot_model = new Kitsune_AI_Platform_Chatbot_Model();

            $payload = array(
                'period' => $period,
                'total_requests' => $total_requests,
                'total_tokens_used' => $total_tokens_used,
                'total_content' => (int) $content_model->count_all(),
                'total_chatbots' => (int) $chatbot_model->count_all(),
                'chatbot_conversations' => $chatbot_conversations,
                'stats' => array(
                    'total_usage' => $total_tokens_used,
                    'total_cost' => 0,
                    'requests' => $total_requests,
                ),
                'chart_data' => array(
                    'labels' => $chart_labels,
                    'data' => $chart_counts,
                )
            );

            set_transient($cache_key, $payload, 5 * MINUTE_IN_SECONDS);
            wp_send_json_success($payload);
        }

        // --- Usage & Cost Breakdown ---
        $cost_results = $wpdb->get_results(
            $this->prepare_optional(
                "SELECT provider, model, COUNT(*) as requests, SUM(tokens_input) as input_tokens, SUM(tokens_output) as output_tokens FROM {$api_logs_table} WHERE {$where_sql} GROUP BY provider, model ORDER BY requests DESC", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $params
            )
        );
        $cost_breakdown = array();
        foreach ($cost_results as $row) {
            $cost_breakdown[] = array(
                'provider' => ucfirst($row->provider ?: 'Unknown'),
                'model' => $row->model,
                'requests' => (int) $row->requests,
                'input_tokens' => (int) $row->input_tokens,
                'output_tokens' => (int) $row->output_tokens,
            );
        }

        $totals = $wpdb->get_row(
            $this->prepare_optional(
                "SELECT COUNT(*) as total_requests, COALESCE(SUM(tokens_input + tokens_output), 0) as total_tokens FROM {$api_logs_table} WHERE {$where_sql}", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $params
            )
        );
        $total_requests = isset($totals->total_requests) ? (int) $totals->total_requests : 0;
        $total_tokens = isset($totals->total_tokens) ? (int) $totals->total_tokens : 0;

        // --- Performance Metrics ---
        // Trend for Latency and Error Rate
        $latency = array_fill(0, 24, 0); // 24 points (hours)
        $error_rate = array_fill(0, 24, 0);
        $trend_results = $wpdb->get_results(
            $this->prepare_optional(
                "SELECT HOUR(created_at) as h, AVG(duration) as avg_d, SUM(CASE WHEN status != 'success' THEN 1 ELSE 0 END) as errs, COUNT(*) as total FROM {$api_logs_table} WHERE {$where_sql} GROUP BY h", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $params
            )
        );
        foreach ($trend_results as $row) {
            $idx = (int)$row->h;
            // Scale latency for UI: let's treat 2000ms as 100% height max
            $perc = min(100, max(1, round(($row->avg_d / 2000) * 100)));
            $latency[$idx] = $perc;
            if ($row->total > 0) {
                $error_rate[$idx] = round((((float)$row->errs) / $row->total) * 100, 1);
            }
        }

        // Slowest requests
        $slowest_results = $wpdb->get_results(
            $this->prepare_optional(
                "SELECT feature as endpoint, 'POST' as method, duration, created_at FROM {$api_logs_table} WHERE {$where_sql} ORDER BY duration DESC LIMIT 10", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $params
            )
        );
        $slowest_requests = array();
        foreach ($slowest_results as $row) {
            $slowest_requests[] = array(
                'endpoint' => $row->endpoint ?: 'General Request',
                'method' => $row->method,
                'duration' => $row->duration . 'ms',
                'time' => date('h:i A', strtotime($row->created_at)),
            );
        }

        $performance_metrics = array(
            'latency' => $latency,
            'error_rate' => $error_rate,
            'slowest_requests' => $slowest_requests,
        );

        // --- User Activity Logs ---
        $logs_results = $wpdb->get_results(
            $this->prepare_optional(
                "SELECT user_id, feature as action, model as resource, (tokens_input + tokens_output) as tokens, created_at FROM {$api_logs_table} WHERE {$where_sql} ORDER BY created_at DESC LIMIT 50", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $params
            )
        );
        $logs = array();
        foreach ($logs_results as $row) {
            $user_info = get_userdata($row->user_id);
            $name = $user_info ? $user_info->display_name : 'Guest User';
            $initials = strtoupper(substr($name, 0, 2));

            $logs[] = array(
                'user' => array('initials' => $initials, 'name' => $name),
                'action' => ucfirst(str_replace('_', ' ', $row->action ?: 'Request')),
                'resource' => $row->resource ?: 'N/A',
                'tokens' => (int) $row->tokens,
                'time' => human_time_diff(strtotime($row->created_at), current_time('timestamp')) . ' ago',
            );
        }
        $total_logs = (int) $wpdb->get_var(
            $this->prepare_optional(
                "SELECT COUNT(*) FROM {$api_logs_table} WHERE {$where_sql}", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $params
            )
        );
        $user_activity = array(
            'total' => $total_logs,
            'logs' => $logs,
        );

        wp_send_json_success(array(
            'period' => $period,
            'stats' => array(
                'total_usage' => $total_tokens,
                'total_cost' => 0,
                'requests' => $total_requests,
            ),
            'cost_breakdown' => $cost_breakdown,
            'performance_metrics' => $performance_metrics,
            'user_activity' => $user_activity,
            'chart_data' => array(
                'labels' => $chart_labels,
                'data' => $chart_counts,
            ),
            'start_date' => $start_date,
            'end_date' => $end_date
        ));
    }

    /**
     * Normalize and validate conversation messages.
     *
     * @param mixed  $messages Raw messages payload.
     * @param string $fallback_time Timestamp when message timestamp is missing.
     * @return array
     */
    private function normalize_conversation_messages($messages, $fallback_time = '') {
        if (!is_array($messages)) {
            return array();
        }

        $normalized = array();
        foreach ($messages as $index => $message) {
            if (!is_array($message)) {
                continue;
            }

            $role = sanitize_key($message['role'] ?? '');
            if (!in_array($role, array('system', 'user', 'assistant'), true)) {
                continue;
            }

            $content = sanitize_textarea_field($message['content'] ?? '');
            if ($content === '') {
                continue;
            }

            $created_at = sanitize_text_field($message['created_at'] ?? '');
            if ($created_at === '') {
                $created_at = $fallback_time ?: current_time('c');
            }

            $message_id = sanitize_text_field($message['id'] ?? '');
            if ($message_id === '') {
                $message_id = (string) ((int) $index + 1);
            }

            $normalized[] = array(
                'id' => $message_id,
                'role' => $role,
                'content' => $content,
                'created_at' => $created_at,
            );
        }

        return $normalized;
    }

    /**
     * Prepare SQL only when placeholders are present.
     *
     * @param string $sql SQL query.
     * @param array  $params Placeholder values.
     * @return string
     */
    private function prepare_optional($sql, $params = array()) {
        global $wpdb;

        if (empty($params)) {
            return $sql;
        }

        return $wpdb->prepare($sql, $params);
    }

    /**
     * Build transient key for dashboard stats cache.
     *
     * @return string
     */
    private function get_dashboard_stats_cache_key() {
        return 'kitsune_ai_platform_dashboard_stats_' . date('Ym', current_time('timestamp'));
    }

    /**
     * Format conversation DB row for inbox list response.
     *
     * @param object $row Row from ai_platform_conversations.
     * @return array
     */
    private function format_conversation_row($row) {
        $messages = $this->normalize_conversation_messages(
            !empty($row->messages) ? json_decode($row->messages, true) : array(),
            mysql2date('c', $row->created_at)
        );

        $last_message = '';
        $last_message_at = mysql2date('c', $row->updated_at);
        if (!empty($messages)) {
            $last = end($messages);
            $last_message = sanitize_text_field($last['content'] ?? '');
            $last_message_at = sanitize_text_field($last['created_at'] ?? $last_message_at);
        }

        $chatbot_name = $row->chatbot_title ?? '';
        if (!$chatbot_name && !empty($row->chatbot_id)) {
            $chatbot_name = get_the_title($row->chatbot_id);
        }

        $user_name = null;
        if (!empty($row->user_identifier) && strpos($row->user_identifier, 'conv_') !== 0) {
            $user_name = $row->user_identifier;
        }

        return array(
            'id' => (int) $row->id,
            'user_name' => $user_name,
            'chatbot_name' => $chatbot_name ?: __('Chatbot', 'kitsune-ai-platform'),
            'last_message' => $last_message,
            'last_message_at' => $last_message_at,
            'message_count' => count($messages),
        );
    }

}
