<?php

namespace ZenoChatbot\Services\Dashboard;

use ZenoChatbot\Model\Conversation;
use ZenoChatbot\Model\Message;
use ZenoChatbot\Support\Hooks;

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

class Widget
{
    /**
     * How many recent AI messages to sample for the average response time.
     */
    private const RESPONSE_TIME_SAMPLE = 100;

    /**
     * @throws \Exception
     */
    public static function getWidgets()
    {
        $widgets = [];
        $resolved = static::getResolvedConversations();
        $defaultWidgets = [
            [
                'title'    => __('Total Conversations', 'zeno-chatbot-ai'),
                'value'    => static::getTotalConversation(),
                'subtitle' => '',
                'icon'     => 'ri-chat-ai-2-fill',
                'color'    => '#2563eb',
                'order'    => 1
            ],
            [
                'title'    => __('Resolved', 'zeno-chatbot-ai'),
                'value'    => $resolved['count'],
                /* translators: %s: number of conversations resolved today */
                'subtitle' => sprintf(__('%s resolved today', 'zeno-chatbot-ai'), $resolved['today']),
                'icon'     => 'ri-checkbox-circle-line',
                'color'    => '#2563eb',
                'order'    => 2
            ],
            [
                'title'    => __('Average Response Time', 'zeno-chatbot-ai'),
                'value'    => static::calculateAverageResponseTime(),
                'subtitle' => '',
                'icon'     => 'ri-time-line',
                'color'    => '#2563eb',
                'order'    => 3
            ],
            [
                'title'    => __('Token Usage', 'zeno-chatbot-ai'),
                'value'    => static::getTokenUsage(),
                'subtitle' => '',
                'icon'     => 'ri-file-text-line',
                'color'    => '#2563eb',
                'order'    => 4
            ]
        ];

        $userWidgets = Hooks::applyFilters(Hooks::ADMIN_DASHBOARD_WIDGETS, $defaultWidgets);

        // sort by order
        usort($userWidgets, function ($a, $b) {
            return $a['order'] - $b['order'];
        });

        foreach ($userWidgets as $widget) {
            $widgets[] = WidgetBuilder::build(
                $widget['title'],
                $widget['value'],
                $widget['subtitle'] ?? '',
                $widget['icon'] ?? '',
                $widget['color'] ?? '#2563eb',
                $widget['order'] ?? 1
            );
        }
        return $widgets;
    }

    private static function getTotalConversation()
    {
        return Conversation::count();
    }

    private static function getTokenUsage()
    {
        return Message::query()->whereNotNull('token_usage')->sum('token_usage');
    }

    /**
     * Average gap between each recent AI message and the customer message it
     * answers, computed in one SQL statement (previously one extra query per
     * AI message).
     */
    private static function calculateAverageResponseTime()
    {
        global $wpdb;

        $table = esc_sql($wpdb->prefix . 'zenochatbot_messages');

        $sql = "SELECT AVG(response_seconds) FROM (
                SELECT TIMESTAMPDIFF(SECOND, (
                    SELECT MAX(c.created_at)
                    FROM {$table} c
                    WHERE c.conversation_id = ai.conversation_id
                      AND c.sender_type = 'customer'
                      AND c.created_at < ai.created_at
                ), ai.created_at) AS response_seconds
                FROM {$table} ai
                WHERE ai.sender_type = 'ai'
                ORDER BY ai.created_at DESC
                LIMIT %d
            ) recent
            WHERE response_seconds IS NOT NULL";

        // Safe: single %d placeholder via $wpdb->prepare(); table name from $wpdb->prefix, esc_sql()'d.
        // Custom stats table — admin dashboard aggregate, intentionally uncached.
        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $avg_seconds = $wpdb->get_var($wpdb->prepare($sql, static::RESPONSE_TIME_SAMPLE));
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

        if ($avg_seconds === null) {
            return 'N/A';
        }

        $avg_seconds = (float) $avg_seconds;

        if ($avg_seconds < 60) {
            return round($avg_seconds) . 's';
        }

        $minutes = floor($avg_seconds / 60);
        $seconds = (int) $avg_seconds % 60;

        return $minutes . 'm ' . round($seconds) . 's';
    }

    private static function getResolvedConversations()
    {
        return [
            'count' => Conversation::whereNotNull('ended_at')->count(),
            'today' => Conversation::whereNotNull('ended_at')
                ->where('ended_at', '>=', gmdate('Y-m-d 00:00:00'))
                ->count()
        ];
    }

}
