<?php

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

/**
 * Read-only aggregate queries over token usage, for the Token Usage tab and
 * the dashboard. The chat statistics behind the Analytics tab are part of
 * the Pro version.
 */
class RSVL_Analytics
{
    /**
     * Sum of chat tokens used within an inclusive date range.
     *
     * @param string $since Start date (Y-m-d), inclusive.
     * @param string $until End date (Y-m-d), inclusive.
     */
    public static function get_token_usage_total(string $since, string $until): int
    {
        global $wpdb;
        $table  = RSVL_Schema::token_usage_table();
        $bounds = resolver_utc_bounds($since, $until);

        return (int) $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COALESCE(SUM(total_tokens), 0) FROM %i
                 WHERE type = 'chat' AND created_at BETWEEN %s AND %s",
                $table,
                $bounds[0],
                $bounds[1]
            )
        );
    }

    /**
     * Get daily chat token usage within an inclusive date range.
     *
     * @param string $since Start date (Y-m-d), inclusive.
     * @param string $until End date (Y-m-d), inclusive.
     * @return array Each row: { day, prompt_tokens, completion_tokens, total_tokens }
     */
    public static function get_daily_token_usage(string $since, string $until): array
    {
        global $wpdb;
        $table      = RSVL_Schema::token_usage_table();
        $bounds     = resolver_utc_bounds($since, $until);
        $local_date = resolver_sql_local_date('created_at', $since, $until);

        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $local_date is fixed SQL, from resolver_sql_local_date().
        $rows = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT {$local_date} AS day,
                        CAST(SUM(prompt_tokens) AS UNSIGNED) AS prompt_tokens,
                        CAST(SUM(completion_tokens) AS UNSIGNED) AS completion_tokens,
                        CAST(SUM(total_tokens) AS UNSIGNED) AS total_tokens
                 FROM %i
                 WHERE type = 'chat' AND created_at BETWEEN %s AND %s
                 GROUP BY {$local_date}
                 ORDER BY day ASC",
                $table,
                $bounds[0],
                $bounds[1]
            ),
            ARRAY_A
        );
        // phpcs:enable

        return $rows ?: [];
    }

    /**
     * Get daily indexing token usage within an inclusive date range.
     *
     * @param string $since Start date (Y-m-d), inclusive.
     * @param string $until End date (Y-m-d), inclusive.
     * @return array Each row: { day, total_tokens }
     */
    public static function get_daily_indexing_token_usage(string $since, string $until): array
    {
        global $wpdb;
        $table      = RSVL_Schema::token_usage_table();
        $bounds     = resolver_utc_bounds($since, $until);
        $local_date = resolver_sql_local_date('created_at', $since, $until);

        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $local_date is fixed SQL, from resolver_sql_local_date().
        $rows = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT {$local_date} AS day,
                        CAST(SUM(total_tokens) AS UNSIGNED) AS total_tokens
                 FROM %i
                 WHERE type = 'index' AND created_at BETWEEN %s AND %s
                 GROUP BY {$local_date}
                 ORDER BY day ASC",
                $table,
                $bounds[0],
                $bounds[1]
            ),
            ARRAY_A
        );
        // phpcs:enable

        return $rows ?: [];
    }

    /**
     * Get indexing token usage totals and per-provider breakdown.
     *
     * @return array { total, breakdown[] }
     */
    public static function get_indexing_token_usage(): array
    {
        global $wpdb;
        $table = RSVL_Schema::token_usage_table();

        $total = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COALESCE(SUM(total_tokens), 0) FROM %i WHERE type = 'index'",
            $table
        ));

        $breakdown = $wpdb->get_results($wpdb->prepare(
            "SELECT provider, model,
                    CAST(SUM(total_tokens) AS UNSIGNED) AS total_tokens,
                    COUNT(*) AS calls
             FROM %i
             WHERE type = 'index'
             GROUP BY provider, model
             ORDER BY total_tokens DESC",
            $table
        ), ARRAY_A);

        return [
            'total'     => $total,
            'breakdown' => $breakdown ?: [],
        ];
    }
}