<?php

declare(strict_types=1);

namespace BotGuard\Models;

use BotGuard\Core\Model;

/**
 * A completed verification outcome. Also exposes the aggregate queries the
 * dashboards and analytics build on.
 */
final class VerificationResult extends Model
{
    protected string $table = 'verification_results';

    /**
     * Headline counters for a user's dashboard (or global when $userId is null).
     *
     * @return array<string, int|float>
     */
    public function summary(?int $userId = null, int $sinceDays = 30): array
    {
        [$scope, $bind] = $this->scope($userId);
        $bind[] = $this->sinceDate($sinceDays);

        $row = $this->db->selectOne(
            "SELECT
                COUNT(*)                                             AS total,
                SUM(classification = 'human')                       AS humans,
                SUM(classification = 'suspicious')                  AS suspicious,
                SUM(classification = 'bot')                         AS bots,
                COALESCE(ROUND(AVG(score), 1), 0)                   AS avg_score,
                SUM(js_enabled = 0)                                 AS js_disabled,
                SUM(cookies_enabled = 0)                            AS cookies_disabled
             FROM `{$this->table}`
             WHERE {$scope} AND `created_at` >= ?",
            $bind
        ) ?? [];

        return array_map(static fn ($v) => $v === null ? 0 : (float) $v, $row);
    }

    /**
     * Count by an arbitrary column for "top X" widgets.
     *
     * @return array<int, array<string, mixed>>
     */
    public function topBy(string $column, ?int $userId = null, int $limit = 5, int $sinceDays = 30): array
    {
        $allowed = ['browser', 'os', 'device_type', 'country', 'referrer_type', 'classification'];
        if (! in_array($column, $allowed, true)) {
            return [];
        }

        [$scope, $bind] = $this->scope($userId);
        $bind[] = $this->sinceDate($sinceDays);

        return $this->db->select(
            "SELECT COALESCE(`{$column}`, 'Unknown') AS label, COUNT(*) AS total
             FROM `{$this->table}`
             WHERE {$scope} AND `created_at` >= ?
             GROUP BY label ORDER BY total DESC LIMIT " . (int) $limit,
            $bind
        );
    }

    /**
     * Daily counts per classification for the trend chart.
     *
     * @return array<int, array<string, mixed>>
     */
    public function dailyTrend(?int $userId = null, int $days = 14): array
    {
        [$scope, $bind] = $this->scope($userId);
        $bind[] = $this->sinceDate($days);

        return $this->db->select(
            "SELECT DATE(`created_at`) AS day,
                    SUM(classification = 'human') AS humans,
                    SUM(classification = 'suspicious') AS suspicious,
                    SUM(classification = 'bot') AS bots
             FROM `{$this->table}`
             WHERE {$scope} AND `created_at` >= ?
             GROUP BY day ORDER BY day",
            $bind
        );
    }

    /**
     * Recent rows for the logs table.
     *
     * @return array<int, array<string, mixed>>
     */
    public function recent(?int $userId = null, int $limit = 50): array
    {
        [$scope, $bind] = $this->scope($userId);

        return $this->db->select(
            "SELECT * FROM `{$this->table}` WHERE {$scope} ORDER BY `id` DESC LIMIT " . (int) $limit,
            $bind
        );
    }

    /**
     * @return array{0:string, 1:array<int,mixed>}
     */
    private function scope(?int $userId): array
    {
        return $userId === null ? ['1 = 1', []] : ['`user_id` = ?', [$userId]];
    }

    private function sinceDate(int $days): string
    {
        return (new \DateTimeImmutable("-{$days} days"))->format('Y-m-d H:i:s');
    }
}
