<?php
/**
 * class/admin.php — lógica del panel de administración.
 *
 * Configuración de OpenRouter (key en settings), consulta de gasto y catálogo
 * de modelos. Las llamadas salen del servidor con cURL; la key vive en settings.
 */
if (!defined('IN_APP')) { die('Acceso denegado'); }

class Admin extends App
{
    const OR_BASE = 'https://openrouter.ai/api/v1';

    /** GET autenticado a OpenRouter. Devuelve [http_code, array|null $data, string $error]. */
    private function orGet($path)
    {
        $key = (string)$this->getSetting('openrouter_key', '');
        if ($key === '') return array(0, null, 'no_key');

        $ch = curl_init(self::OR_BASE . $path);
        curl_setopt_array($ch, array(
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_TIMEOUT        => 15,
            CURLOPT_HTTPHEADER     => array(
                'Authorization: Bearer ' . $key,
                'HTTP-Referer: https://kamiquery.com',
                'X-Title: kamiquery',
            ),
        ));
        $body = curl_exec($ch);
        $code = (int)curl_getinfo($ch, CURLINFO_HTTP_CODE);
        $err  = curl_error($ch);
        curl_close($ch);

        $data = null;
        if (is_string($body) && $body !== '') {
            $json = json_decode($body, true);
            if (is_array($json)) $data = $json['data'] ?? $json;
        }
        return array($code, $data, $err);
    }

    /** ¿Hay key configurada? */
    public function hasKey()
    {
        return (string)$this->getSetting('openrouter_key', '') !== '';
    }

    /** Comprueba la key contra OpenRouter. Devuelve true/false. */
    public function testKey()
    {
        list($code, $data) = $this->orGet('/credits');
        return $code === 200 && $data !== null;
    }

    /** Gasto/saldo de OpenRouter: ['total_credits'=>, 'total_usage'=>] o null. */
    public function usage()
    {
        list($code, $data) = $this->orGet('/credits');
        return $code === 200 ? $data : null;
    }

    /**
     * Catálogo de modelos de OpenRouter, enriquecido para la tabla del admin:
     * id, name, company, context, modalities[], tools(bool), price_in, price_out.
     * Precios en USD por **millón** de tokens (-1 = variable, 0 = gratis). [] si falla.
     */
    public function models()
    {
        list($code, $data) = $this->orGet('/models');
        if ($code !== 200 || !is_array($data)) return array();

        $out = array();
        foreach ($data as $m) {
            if (!isset($m['id'])) continue;
            $id    = $m['id'];
            $arch  = $m['architecture'] ?? array();
            $price = $m['pricing'] ?? array();

            $inMods  = $arch['input_modalities'] ?? array();
            $outMods = $arch['output_modalities'] ?? array();
            $params  = $m['supported_parameters'] ?? array();

            // Capacidades canónicas (claves para badge + filtro).
            $caps = array();
            if (in_array('image', $inMods, true))  $caps[] = 'vision';
            if (in_array('file', $inMods, true))   $caps[] = 'file';
            if (in_array('video', $inMods, true))  $caps[] = 'video';
            if (in_array('audio', $inMods, true))  $caps[] = 'audio';
            if (in_array('image', $outMods, true)) $caps[] = 'imageout';
            if (in_array('tools', $params, true))  $caps[] = 'tools';
            if (in_array('reasoning', $params, true) || in_array('include_reasoning', $params, true)) $caps[] = 'reasoning';
            if (in_array('structured_outputs', $params, true)) $caps[] = 'json';

            $perM = function ($p) {
                if ($p === null || $p === '') return null;
                $n = (float)$p;
                if ($n < 0) return -1.0;        // variable
                return $n * 1000000.0;          // por millón de tokens
            };

            $out[] = array(
                'id'         => $id,
                'name'       => $m['name'] ?? $id,
                'company'    => $this->companyOf($id, $m['name'] ?? ''),
                'context'    => (int)($m['context_length'] ?? ($m['top_provider']['context_length'] ?? 0)),
                'caps'       => $caps,
                'price_in'   => $perM($price['prompt'] ?? null),
                'price_out'  => $perM($price['completion'] ?? null),
            );
        }
        usort($out, function ($a, $b) {
            $c = strcasecmp($a['company'], $b['company']);
            return $c !== 0 ? $c : strcasecmp($a['name'], $b['name']);
        });
        return $out;
    }

    /* ===================== Catálogos de otros proveedores ===================== */

    // Bases OpenAI-compatibles de cada proveedor (endpoint /models).
    const MOONSHOT_BASE = 'https://api.moonshot.ai/v1';
    const MINIMAX_BASE   = 'https://api.minimax.io/v1';

    /** ¿Hay key de Kimi/Moonshot? */
    public function hasKimiKey()    { return (string)$this->getSetting('kimi_key', '') !== ''; }
    /** ¿Hay key de MiniMax? */
    public function hasMinimaxKey() { return (string)$this->getSetting('minimax_key', '') !== ''; }

    /** GET Bearer genérico. Devuelve [http_code, array|null $data, string $error]. */
    private function providerGet($base, $key, $path)
    {
        if ((string)$key === '') return array(0, null, 'no_key');
        $ch = curl_init($base . $path);
        curl_setopt_array($ch, array(
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_TIMEOUT        => 15,
            CURLOPT_HTTPHEADER     => array('Authorization: Bearer ' . $key),
        ));
        $body = curl_exec($ch);
        $code = (int)curl_getinfo($ch, CURLINFO_HTTP_CODE);
        $err  = curl_error($ch);
        curl_close($ch);

        $data = null;
        if (is_string($body) && $body !== '') {
            $json = json_decode($body, true);
            if (is_array($json)) $data = $json['data'] ?? $json;
        }
        return array($code, $data, $err);
    }

    /**
     * Metadatos CURADOS de modelos (su API solo da el id; contexto/caps/precio no).
     * Formato: 'id' => [context_tokens, caps[], precio_in, precio_out]  (precios USD por 1M tokens; null = desconocido).
     *
     * ⚠️ PRECIOS ORIENTATIVOS — revísalos en la web del proveedor y edítalos aquí.
     * Si añaden modelos nuevos no listados, se rellenan por heurística (contexto del nombre).
     */
    private function curatedMeta($provider)
    {
        // Moonshot/Kimi — precios de https://platform.kimi.ai/docs/pricing (input = cache-miss).
        $moonshot = array(
            'kimi-k2.7-code'                  => array(262144, array('tools', 'reasoning', 'vision'), 0.95, 4.00),
            'kimi-k2.7-code-highspeed'        => array(262144, array('tools', 'reasoning', 'vision'), 1.90, 8.00),
            'kimi-k2.6'                       => array(262144, array('tools', 'reasoning'), 0.95, 4.00),
            'kimi-k2.5'                       => array(262144, array('tools', 'reasoning', 'vision'), 0.60, 3.00),
            'moonshot-v1-8k'                  => array(8192,   array('tools'), 0.20, 2.00),
            'moonshot-v1-32k'                 => array(32768,  array('tools'), 1.00, 3.00),
            'moonshot-v1-128k'                => array(131072, array('tools'), 2.00, 5.00),
            'moonshot-v1-8k-vision-preview'   => array(8192,   array('tools', 'vision'), 0.20, 2.00),
            'moonshot-v1-32k-vision-preview'  => array(32768,  array('tools', 'vision'), 1.00, 3.00),
            'moonshot-v1-128k-vision-preview' => array(131072, array('tools', 'vision'), 2.00, 5.00),
        );
        // MiniMax — precios de https://platform.minimax.io/docs/guides/pricing-paygo (tarifa estándar).
        $minimax = array(
            'MiniMax-M3'              => array(1000000, array('tools', 'reasoning'), 0.30, 1.20),
            'MiniMax-M2.7'            => array(204800,  array('tools', 'reasoning'), 0.30, 1.20),
            'MiniMax-M2.7-highspeed'  => array(204800,  array('tools', 'reasoning'), 0.60, 2.40),
            'MiniMax-M2.5'            => array(204800,  array('tools', 'reasoning'), 0.30, 1.20),
            'MiniMax-M2.5-highspeed'  => array(204800,  array('tools', 'reasoning'), 0.60, 2.40),
            'MiniMax-M2.1'            => array(204800,  array('tools', 'reasoning'), 0.30, 1.20),
            'MiniMax-M2.1-highspeed'  => array(204800,  array('tools', 'reasoning'), 0.60, 2.40),
            'MiniMax-M2'              => array(204800,  array('tools', 'reasoning'), 0.30, 1.20),
        );
        return $provider === 'minimax' ? $minimax : $moonshot;
    }

    /**
     * Saldo disponible (USD) por proveedor. null = no se pudo leer o no hay API.
     *   - OpenRouter: /credits (total_credits - total_usage).
     *   - Moonshot:   /users/me/balance (available_balance).
     *   - MiniMax:    no expone saldo por API (solo panel web) -> null.
     */
    public function providerBalances()
    {
        $out = array('openrouter' => null, 'moonshot' => null, 'minimax' => null);

        list($c, $d) = $this->orGet('/credits');
        if ($c === 200 && is_array($d) && isset($d['total_credits'])) {
            $out['openrouter'] = (float)$d['total_credits'] - (float)($d['total_usage'] ?? 0);
        }

        $kimi = (string)$this->getSetting('kimi_key', '');
        if ($kimi !== '') {
            list($c, $d) = $this->providerGet(self::MOONSHOT_BASE, $kimi, '/users/me/balance');
            if ($c === 200 && is_array($d) && isset($d['available_balance'])) {
                $out['moonshot'] = (float)$d['available_balance'];
            }
        }

        return $out;
    }

    /**
     * Info de tarifas por proveedor: enlace a la página oficial y fecha de la última
     * actualización manual de precios (se muestra en la pestaña del proveedor).
     */
    public function providerPricing($provider)
    {
        if ($provider === 'minimax') {
            return array('url' => 'https://platform.minimax.io/docs/guides/pricing-paygo', 'updated' => '2026-06-19');
        }
        return array('url' => 'https://platform.kimi.ai/docs/pricing/chat-k27-code', 'updated' => '2026-06-19');
    }

    /** Metadatos de un modelo: de la tabla curada o, si no está, por heurística del id. */
    private function providerMeta($provider, $id)
    {
        $map = $this->curatedMeta($provider);
        if (isset($map[$id])) return $map[$id];
        // Heurística: contexto desde el nombre (…-128k), caps básicas.
        $ctx = 0;
        if (preg_match('/(\d+)k\b/i', $id, $mm)) $ctx = (int)$mm[1] * 1024;
        $caps = array('tools');
        if (stripos($id, 'vision') !== false) $caps[] = 'vision';
        return array($ctx, $caps, null, null);
    }

    /**
     * Catálogo OpenAI-compatible enriquecido con metadatos curados.
     * Mismo formato que models() (id, name, company, context, caps, price_in, price_out).
     * [] si falla o no hay key.
     */
    private function openaiCatalog($base, $key, $provider)
    {
        list($code, $data) = $this->providerGet($base, $key, '/models');
        if ($code !== 200 || !is_array($data)) return array();
        $company = $provider === 'minimax' ? 'MiniMax' : 'Moonshot';
        $out = array();
        foreach ($data as $m) {
            if (!is_array($m) || !isset($m['id'])) continue;
            $id = (string)$m['id'];
            list($ctx, $caps, $pin, $pout) = $this->providerMeta($provider, $id);
            $out[] = array(
                'id'        => $id,
                'name'      => $id,
                'company'   => $company,
                'context'   => (int)$ctx,
                'caps'      => $caps,
                'price_in'  => $pin,
                'price_out' => $pout,
            );
        }
        usort($out, function ($a, $b) { return strcasecmp($a['id'], $b['id']); });
        return $out;
    }

    /** Catálogo de modelos de Moonshot (Kimi). [] si falla. */
    public function moonshotModels()
    {
        return $this->openaiCatalog(self::MOONSHOT_BASE, (string)$this->getSetting('kimi_key', ''), 'moonshot');
    }

    /** Catálogo de modelos de MiniMax. [] si falla (su API puede no exponer /models). */
    public function minimaxModels()
    {
        return $this->openaiCatalog(self::MINIMAX_BASE, (string)$this->getSetting('minimax_key', ''), 'minimax');
    }

    /* ===================== Sobrenombres de modelos (rebranding) ===================== */

    /** Mapa ['id_modelo' => 'sobrenombre']. Persiste aunque el modelo se desactive. */
    public function brands()
    {
        $this->ensureSchema('model_brands');
        $rows = $this->db->query("SELECT model, brand FROM model_brands")->fetch_all(MYSQLI_ASSOC);
        $map = array();
        foreach ($rows as $r) $map[$r['model']] = $r['brand'];
        return $map;
    }

    /** Sobrenombre público de un modelo (o '' si no tiene). */
    public function brandOf($model)
    {
        $this->ensureSchema('model_brands');
        $stmt = $this->db->prepare("SELECT brand FROM model_brands WHERE model = ? LIMIT 1");
        $stmt->bind_param('s', $model);
        $stmt->execute();
        $row = $stmt->get_result()->fetch_assoc();
        $stmt->close();
        return $row ? $row['brand'] : '';
    }

    /** Fija (o borra si vacío) el sobrenombre de un modelo. */
    public function setBrand($model, $brand)
    {
        $this->ensureSchema('model_brands');
        $model = trim((string)$model);
        $brand = trim((string)$brand);
        if ($model === '') return;
        if ($brand === '') {
            $stmt = $this->db->prepare("DELETE FROM model_brands WHERE model = ?");
            $stmt->bind_param('s', $model);
        } else {
            $stmt = $this->db->prepare("INSERT INTO model_brands (model, brand) VALUES (?,?) ON DUPLICATE KEY UPDATE brand = VALUES(brand)");
            $stmt->bind_param('ss', $model, $brand);
        }
        $stmt->execute();
        $stmt->close();
    }

    /* ===================== Sesiones (conversaciones) de KamiCode ===================== */

    /** Sesiones de un usuario con nº de mensajes y uso agregado (sin coste aquí). */
    public function userSessions($id)
    {
        $this->ensureSchema('kamicode_sessions');
        $id = (int)$id;
        $sql = "SELECT s.id_session, s.model, s.created_at, s.updated_at,
                       (SELECT COUNT(*) FROM kamicode_sessions_messages m WHERE m.id_session = s.id_session) AS msgs,
                       (SELECT COUNT(*) FROM kamicode_usage u WHERE u.id_session = s.id_session) AS req,
                       (SELECT COALESCE(SUM(u.tokens_in),0)  FROM kamicode_usage u WHERE u.id_session = s.id_session) AS tin,
                       (SELECT COALESCE(SUM(u.tokens_out),0) FROM kamicode_usage u WHERE u.id_session = s.id_session) AS tout
                FROM kamicode_sessions s
                WHERE s.id_user = $id
                ORDER BY s.updated_at DESC";
        return $this->db->query($sql)->fetch_all(MYSQLI_ASSOC);
    }

    /** Mensajes de una sesión, verificando que pertenece al usuario. */
    public function sessionMessages($idSession, $idUser)
    {
        $this->ensureSchema('kamicode_sessions');
        $idSession = (int)$idSession; $idUser = (int)$idUser;
        $own = $this->db->query("SELECT 1 FROM kamicode_sessions WHERE id_session=$idSession AND id_user=$idUser LIMIT 1")->fetch_row();
        if (!$own) return array();
        return $this->db->query("SELECT role, content, created_at FROM kamicode_sessions_messages WHERE id_session=$idSession ORDER BY id_message ASC")->fetch_all(MYSQLI_ASSOC);
    }

    /* ===================== Estado del gateway ===================== */

    /** Estado del gateway KamiCode a partir de su PING (settings). */
    public function gatewayStatus()
    {
        $ver = (string)$this->getSetting('gateway_version', '');
        $hb  = (int)$this->getSetting('gateway_heartbeat', 0);
        $age = $hb > 0 ? (time() - $hb) : null;
        return array(
            'version' => $ver,
            'seconds' => $age,                            // segundos desde el último ping
            'online'  => ($age !== null && $age <= 90),   // online si pingó hace <= 90s
        );
    }

    /* ===================== Uso de KamiCode ===================== */

    /** Totales del mes en curso y de todo el histórico (tokens in/out y nº de sesiones). */
    public function usageSummary()
    {
        $this->ensureSchema('kamicode_usage');
        $start = date('Y-m-01 00:00:00');
        $cols  = "COUNT(*) req, COUNT(DISTINCT id_session) sess, COALESCE(SUM(tokens_in),0) tin, COALESCE(SUM(tokens_out),0) tout, COALESCE(SUM(tokens_cached),0) tcached, COALESCE(SUM(cost),0) cost";
        $month = $this->db->query("SELECT $cols FROM kamicode_usage WHERE created_at >= '$start'")->fetch_assoc();
        $all   = $this->db->query("SELECT $cols FROM kamicode_usage")->fetch_assoc();
        return array('month' => $month, 'all' => $all);
    }

    /** Uso del mes agrupado por usuario (tokens in/out, sesiones, coste). */
    public function usageByUser()
    {
        $this->ensureSchema('kamicode_usage');
        $start = date('Y-m-01 00:00:00');
        $sql = "SELECT k.id_user, u.name, u.email,
                       COUNT(*) req, COUNT(DISTINCT k.id_session) sess,
                       COALESCE(SUM(k.tokens_in),0) tin, COALESCE(SUM(k.tokens_out),0) tout, COALESCE(SUM(k.tokens_cached),0) tcached, COALESCE(SUM(k.cost),0) cost
                FROM kamicode_usage k LEFT JOIN users u ON u.id_user = k.id_user
                WHERE k.created_at >= '$start' GROUP BY k.id_user ORDER BY cost DESC";
        return $this->db->query($sql)->fetch_all(MYSQLI_ASSOC);
    }

    /** Uso del mes agrupado por modelo (tokens in/out, coste). */
    public function usageByModel()
    {
        $this->ensureSchema('kamicode_usage');
        $start = date('Y-m-01 00:00:00');
        $sql = "SELECT model, COUNT(*) req,
                       COALESCE(SUM(tokens_in),0) tin, COALESCE(SUM(tokens_out),0) tout, COALESCE(SUM(tokens_cached),0) tcached, COALESCE(SUM(cost),0) cost
                FROM kamicode_usage WHERE created_at >= '$start' GROUP BY model ORDER BY cost DESC";
        return $this->db->query($sql)->fetch_all(MYSQLI_ASSOC);
    }

    /** Uso del mes en curso agrupado por día (filas), más reciente primero. */
    public function usageByDay()
    {
        $this->ensureSchema('kamicode_usage');
        $start = date('Y-m-01 00:00:00');
        $sql = "SELECT DATE(created_at) d, COUNT(*) req, COUNT(DISTINCT id_session) sess,
                       COALESCE(SUM(tokens_in),0) tin, COALESCE(SUM(tokens_out),0) tout, COALESCE(SUM(tokens_cached),0) tcached, COALESCE(SUM(cost),0) cost
                FROM kamicode_usage WHERE created_at >= '$start' GROUP BY DATE(created_at) ORDER BY d DESC";
        return $this->db->query($sql)->fetch_all(MYSQLI_ASSOC);
    }

    /* ---- Detalle por día ---- */

    /** Totales de un día (YYYY-MM-DD). */
    public function dayStats($day)
    {
        $this->ensureSchema('kamicode_usage');
        $stmt = $this->db->prepare("SELECT COUNT(*) req, COUNT(DISTINCT id_session) sess, COALESCE(SUM(tokens_in),0) tin, COALESCE(SUM(tokens_out),0) tout, COALESCE(SUM(tokens_cached),0) tcached, COALESCE(SUM(cost),0) cost FROM kamicode_usage WHERE DATE(created_at) = ?");
        $stmt->bind_param('s', $day);
        $stmt->execute();
        $row = $stmt->get_result()->fetch_assoc();
        $stmt->close();
        return $row;
    }

    /** Uso de un día agrupado por usuario. */
    public function dayByUser($day)
    {
        $this->ensureSchema('kamicode_usage');
        $stmt = $this->db->prepare("SELECT k.id_user, u.name, u.email, COUNT(*) req, COUNT(DISTINCT k.id_session) sess,
                       COALESCE(SUM(k.tokens_in),0) tin, COALESCE(SUM(k.tokens_out),0) tout, COALESCE(SUM(k.tokens_cached),0) tcached, COALESCE(SUM(k.cost),0) cost
                FROM kamicode_usage k LEFT JOIN users u ON u.id_user = k.id_user
                WHERE DATE(k.created_at) = ? GROUP BY k.id_user ORDER BY cost DESC");
        $stmt->bind_param('s', $day);
        $stmt->execute();
        $rows = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
        $stmt->close();
        return $rows;
    }

    /** Uso de un día agrupado por modelo. */
    public function dayByModel($day)
    {
        $this->ensureSchema('kamicode_usage');
        $stmt = $this->db->prepare("SELECT model, COUNT(*) req, COALESCE(SUM(tokens_in),0) tin, COALESCE(SUM(tokens_out),0) tout, COALESCE(SUM(tokens_cached),0) tcached, COALESCE(SUM(cost),0) cost FROM kamicode_usage WHERE DATE(created_at) = ? GROUP BY model ORDER BY cost DESC");
        $stmt->bind_param('s', $day);
        $stmt->execute();
        $rows = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
        $stmt->close();
        return $rows;
    }

    /** Totales (histórico) de un usuario. */
    public function userStats($id)
    {
        $this->ensureSchema('kamicode_usage');
        $id = (int)$id;
        return $this->db->query("SELECT COUNT(*) req, COUNT(DISTINCT id_session) sess, COALESCE(SUM(tokens_in),0) tin, COALESCE(SUM(tokens_out),0) tout, COALESCE(SUM(tokens_cached),0) tcached, COALESCE(SUM(cost),0) cost FROM kamicode_usage WHERE id_user=$id")->fetch_assoc();
    }

    /** Uso de un usuario por modelo (histórico). */
    public function userByModel($id)
    {
        $this->ensureSchema('kamicode_usage');
        $id = (int)$id;
        return $this->db->query("SELECT model, COUNT(*) req, COALESCE(SUM(tokens_in),0) tin, COALESCE(SUM(tokens_out),0) tout, COALESCE(SUM(tokens_cached),0) tcached, COALESCE(SUM(cost),0) cost FROM kamicode_usage WHERE id_user=$id GROUP BY model ORDER BY cost DESC")->fetch_all(MYSQLI_ASSOC);
    }

    /** Uso por día de un usuario (heatmap). ['Y-m-d'=>['req','sess','tin','tout','cached']]. */
    public function userDaily($id, $days = 371)
    {
        $this->ensureSchema('kamicode_usage');
        $id = (int)$id; $days = (int)$days;
        $start = date('Y-m-d', time() - $days * 86400);
        $rows = $this->db->query("SELECT DATE(created_at) d, COUNT(*) req, COUNT(DISTINCT id_session) sess, COALESCE(SUM(tokens_in),0) tin, COALESCE(SUM(tokens_out),0) tout, COALESCE(SUM(tokens_cached),0) cached FROM kamicode_usage WHERE id_user=$id AND created_at >= '$start' GROUP BY DATE(created_at)")->fetch_all(MYSQLI_ASSOC);
        $map = array();
        foreach ($rows as $r) $map[$r['d']] = $r;
        return $map;
    }

    /** Uso por día (filas) de un usuario en los últimos N días, más reciente primero. */
    public function userDailyRows($id, $days = 30)
    {
        $this->ensureSchema('kamicode_usage');
        $id = (int)$id;
        $start = date('Y-m-d', time() - (int)$days * 86400);
        return $this->db->query("SELECT DATE(created_at) d, COUNT(*) req, COUNT(DISTINCT id_session) sess, COALESCE(SUM(tokens_in),0) tin, COALESCE(SUM(tokens_out),0) tout, COALESCE(SUM(tokens_cached),0) tcached, COALESCE(SUM(cost),0) cost FROM kamicode_usage WHERE id_user=$id AND created_at >= '$start' GROUP BY DATE(created_at) ORDER BY d DESC")->fetch_all(MYSQLI_ASSOC);
    }

    /* ---- Detalle por modelo ---- */

    public function modelStats($model)
    {
        $this->ensureSchema('kamicode_usage');
        $stmt = $this->db->prepare("SELECT COUNT(*) req, COUNT(DISTINCT id_session) sess, COALESCE(SUM(tokens_in),0) tin, COALESCE(SUM(tokens_out),0) tout, COALESCE(SUM(tokens_cached),0) tcached, COALESCE(SUM(cost),0) cost FROM kamicode_usage WHERE model = ?");
        $stmt->bind_param('s', $model);
        $stmt->execute();
        $row = $stmt->get_result()->fetch_assoc();
        $stmt->close();
        return $row;
    }

    public function modelByUser($model)
    {
        $this->ensureSchema('kamicode_usage');
        $stmt = $this->db->prepare("SELECT k.id_user, u.name, u.email, COUNT(*) req, COUNT(DISTINCT k.id_session) sess,
                       COALESCE(SUM(k.tokens_in),0) tin, COALESCE(SUM(k.tokens_out),0) tout, COALESCE(SUM(k.tokens_cached),0) tcached, COALESCE(SUM(k.cost),0) cost
                FROM kamicode_usage k LEFT JOIN users u ON u.id_user = k.id_user
                WHERE k.model = ? GROUP BY k.id_user ORDER BY cost DESC");
        $stmt->bind_param('s', $model);
        $stmt->execute();
        $rows = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
        $stmt->close();
        return $rows;
    }

    public function modelDaily($model, $days = 371)
    {
        $this->ensureSchema('kamicode_usage');
        $start = date('Y-m-d', time() - (int)$days * 86400);
        $stmt = $this->db->prepare("SELECT DATE(created_at) d, COUNT(*) req, COUNT(DISTINCT id_session) sess, COALESCE(SUM(tokens_in),0) tin, COALESCE(SUM(tokens_out),0) tout, COALESCE(SUM(tokens_cached),0) cached FROM kamicode_usage WHERE model = ? AND created_at >= ? GROUP BY DATE(created_at)");
        $stmt->bind_param('ss', $model, $start);
        $stmt->execute();
        $rows = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
        $stmt->close();
        $map = array();
        foreach ($rows as $r) $map[$r['d']] = $r;
        return $map;
    }

    /* ===================== Planes ===================== */

    public function listPlans()
    {
        $this->ensureSchema('plans');
        $sql = 'SELECT p.id_plan, p.name, p.monthly_usd, p.created_at,
                       (SELECT COUNT(*) FROM users u WHERE u.id_plan = p.id_plan) AS users
                FROM plans p ORDER BY p.name ASC';
        return $this->db->query($sql)->fetch_all(MYSQLI_ASSOC);
    }

    public function getPlan($id)
    {
        $stmt = $this->db->prepare('SELECT id_plan, name, monthly_usd FROM plans WHERE id_plan = ? LIMIT 1');
        $stmt->bind_param('i', $id);
        $stmt->execute();
        $row = $stmt->get_result()->fetch_assoc();
        $stmt->close();
        return $row ?: null;
    }

    public function createPlan($name, $monthly)
    {
        $this->ensureSchema('plans');
        $name = trim($name);
        if ($name === '') { $this->_error = App::t('plan.invalid'); return false; }
        $monthly = (float)$monthly;
        $stmt = $this->db->prepare('INSERT INTO plans (name, monthly_usd, created_at) VALUES (?,?,NOW())');
        $stmt->bind_param('sd', $name, $monthly);
        $ok = $stmt->execute();
        $stmt->close();
        if (!$ok) { $this->_error = App::t('plan.name_taken'); return false; }
        return true;
    }

    public function updatePlan($id, $name, $monthly)
    {
        $name = trim($name);
        if ($name === '') { $this->_error = App::t('plan.invalid'); return false; }
        $monthly = (float)$monthly;
        $stmt = $this->db->prepare('UPDATE plans SET name = ?, monthly_usd = ? WHERE id_plan = ?');
        $stmt->bind_param('sdi', $name, $monthly, $id);
        $ok = $stmt->execute();
        $stmt->close();
        return $ok;
    }

    /** Borra un plan solo si no tiene usuarios asignados. */
    public function deletePlan($id)
    {
        $n = (int)($this->db->query('SELECT COUNT(*) c FROM users WHERE id_plan = ' . (int)$id)->fetch_assoc()['c']);
        if ($n > 0) { $this->_error = App::t('plan.has_users'); return false; }
        $stmt = $this->db->prepare('DELETE FROM plans WHERE id_plan = ?');
        $stmt->bind_param('i', $id);
        $stmt->execute();
        $stmt->close();
        return true;
    }

    /* ===================== Usuarios ===================== */

    public function listUsers()
    {
        $this->ensureOrgColumns();
        $sql = 'SELECT u.id_user, u.name, u.email, u.is_admin, u.is_active, u.is_org, u.org_id, u.created_at, u.last_login_at,
                       p.name AS plan_name, o.name AS org_name
                FROM users u
                LEFT JOIN plans p ON p.id_plan = u.id_plan
                LEFT JOIN users o ON o.id_user = u.org_id
                ORDER BY u.id_user ASC';
        return $this->db->query($sql)->fetch_all(MYSQLI_ASSOC);
    }

    public function getUser($id)
    {
        $this->ensureOrgColumns();
        $stmt = $this->db->prepare('SELECT id_user, name, email, is_admin, is_active, is_org, max_accounts, id_plan FROM users WHERE id_user = ? LIMIT 1');
        $stmt->bind_param('i', $id);
        $stmt->execute();
        $row = $stmt->get_result()->fetch_assoc();
        $stmt->close();
        return $row ?: null;
    }

    public function createUser($name, $email, $password, $id_plan, $is_admin, $is_org = false, $max_accounts = 0)
    {
        $this->ensureOrgColumns();
        $name = trim($name); $email = trim($email);
        if ($name === '' || !filter_var($email, FILTER_VALIDATE_EMAIL) || strlen((string)$password) < 8) {
            $this->_error = App::t('user.invalid'); return false;
        }
        $hash = password_hash((string)$password, PASSWORD_DEFAULT);
        $adm  = $is_admin ? 1 : 0;
        $org  = $is_org ? 1 : 0;
        $maxa = max(0, (int)$max_accounts);
        $plan = $id_plan ? (int)$id_plan : null;
        $stmt = $this->db->prepare('INSERT INTO users (name, email, pass_hash, locale, is_admin, is_active, is_org, max_accounts, id_plan, created_at) VALUES (?,?,?,?,?,1,?,?,?,NOW())');
        $loc = App::locale();
        $stmt->bind_param('ssssiiii', $name, $email, $hash, $loc, $adm, $org, $maxa, $plan);
        $ok = $stmt->execute();
        $stmt->close();
        if (!$ok) { $this->_error = App::t('auth.email_taken'); return false; }
        return true;
    }

    public function updateUser($id, $name, $email, $id_plan, $is_admin, $password = '', $is_org = false, $max_accounts = 0)
    {
        $this->ensureOrgColumns();
        $name = trim($name); $email = trim($email);
        if ($name === '' || !filter_var($email, FILTER_VALIDATE_EMAIL)) { $this->_error = App::t('user.invalid'); return false; }
        $adm  = $is_admin ? 1 : 0;
        $org  = $is_org ? 1 : 0;
        $maxa = max(0, (int)$max_accounts);
        $plan = $id_plan ? (int)$id_plan : null;
        $stmt = $this->db->prepare('UPDATE users SET name=?, email=?, is_admin=?, is_org=?, max_accounts=?, id_plan=? WHERE id_user=?');
        $stmt->bind_param('ssiiiii', $name, $email, $adm, $org, $maxa, $plan, $id);
        $ok = $stmt->execute();
        $stmt->close();
        if ($ok && strlen((string)$password) >= 8) {
            $hash = password_hash((string)$password, PASSWORD_DEFAULT);
            $st = $this->db->prepare('UPDATE users SET pass_hash=? WHERE id_user=?');
            $st->bind_param('si', $hash, $id);
            $st->execute();
            $st->close();
        }
        return $ok;
    }

    /** Suspende (0) o reactiva (1) un usuario; al suspender, revoca sus sesiones. */
    public function setUserActive($id, $active)
    {
        $a = $active ? 1 : 0;
        $stmt = $this->db->prepare('UPDATE users SET is_active=? WHERE id_user=?');
        $stmt->bind_param('ii', $a, $id);
        $stmt->execute();
        $stmt->close();
        if (!$a) $this->db->query('UPDATE users_sessions SET revoked=1 WHERE id_user=' . (int)$id);
    }

    /** Elimina un usuario y sus datos asociados (sesiones, keys, invitaciones). */
    public function deleteUser($id)
    {
        $id = (int)$id;
        $this->db->query('DELETE FROM users_sessions WHERE id_user=' . $id);
        $this->db->query('DELETE FROM users_api_keys WHERE id_user=' . $id);
        $this->db->query('DELETE FROM users WHERE id_user=' . $id);
    }

    /** Deriva la empresa del id (prefijo antes de '/') con nombres bonitos. */
    private function companyOf($id, $name)
    {
        $slug = strpos($id, '/') !== false ? substr($id, 0, strpos($id, '/')) : '';
        $map = array(
            'openai' => 'OpenAI', 'anthropic' => 'Anthropic', 'google' => 'Google',
            'meta-llama' => 'Meta', 'mistralai' => 'Mistral', 'x-ai' => 'xAI',
            'deepseek' => 'DeepSeek', 'qwen' => 'Qwen', 'cohere' => 'Cohere',
            'microsoft' => 'Microsoft', 'amazon' => 'Amazon', 'nvidia' => 'NVIDIA',
            'perplexity' => 'Perplexity', 'moonshotai' => 'Moonshot', 'openrouter' => 'OpenRouter',
        );
        if ($slug === '') return '—';
        return $map[$slug] ?? ucfirst($slug);
    }
}