<?php

namespace WPSN\Modules\AIReadiness;

defined('ABSPATH') || exit;

/**
 * Aggregated stats for Bot Blocker.
 *
 * We store only coarse, non-personal signals:
 * - day + bot_key => hit counter
 * - last_seen + last_path + last_user_agent (helpful for debugging)
 */
final class BotBlockStatsRepository
{
    private string $table;

    public function __construct()
    {
        global $wpdb;
        $this->table = $wpdb->prefix . 'wpsn_ai_botblock_stats';
    }

    public function recordHit(string $botKey, string $botLabel, string $path, string $userAgent): void
    {
        global $wpdb;

        $botKey = sanitize_key($botKey);
        if ($botKey === '') {
            return;
        }

        $botLabel = sanitize_text_field($botLabel);
        if ($botLabel === '') {
            $botLabel = $botKey;
        }

        $path = sanitize_text_field($path);
        if (function_exists('mb_strlen') && mb_strlen($path) > 2048) {
            $path = (string) mb_substr($path, 0, 2048);
        } elseif (strlen($path) > 2048) {
            $path = substr($path, 0, 2048);
        }

        $userAgent = sanitize_text_field($userAgent);
        if (function_exists('mb_strlen') && mb_strlen($userAgent) > 255) {
            $userAgent = (string) mb_substr($userAgent, 0, 255);
        } elseif (strlen($userAgent) > 255) {
            $userAgent = substr($userAgent, 0, 255);
        }

        $day = gmdate('Y-m-d');
        $now = gmdate('Y-m-d H:i:s');

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "INSERT INTO {$this->table} (day, bot_key, bot_label, hits, last_seen, last_path, last_user_agent)
                VALUES (%s, %s, %s, 1, %s, %s, %s)
                ON DUPLICATE KEY UPDATE
                    hits = hits + 1,
                    bot_label = VALUES(bot_label),
                    last_seen = VALUES(last_seen),
                    last_path = VALUES(last_path),
                    last_user_agent = VALUES(last_user_agent)";

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $wpdb->query($wpdb->prepare($sql, $day, $botKey, $botLabel, $now, $path, $userAgent));
    }

    /**
     * @return array<int,array<string,mixed>>
     */
    public function totals(int $days = 7): array
    {
        global $wpdb;

        $days = max(1, min(365, (int) $days));
        $start = gmdate('Y-m-d', time() - (($days - 1) * DAY_IN_SECONDS));

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "SELECT
                    t.bot_key,
                    MAX(t.bot_label) AS bot_label,
                    SUM(t.hits) AS hits,
                    MAX(t.last_seen) AS last_seen,
                    (
                        SELECT t2.last_path
                        FROM {$this->table} t2
                        WHERE t2.bot_key = t.bot_key AND t2.day >= %s
                        ORDER BY t2.last_seen DESC, t2.id DESC
                        LIMIT 1
                    ) AS last_path,
                    (
                        SELECT t3.last_user_agent
                        FROM {$this->table} t3
                        WHERE t3.bot_key = t.bot_key AND t3.day >= %s
                        ORDER BY t3.last_seen DESC, t3.id DESC
                        LIMIT 1
                    ) AS last_user_agent
                FROM {$this->table} t
                WHERE t.day >= %s
                GROUP BY t.bot_key
                ORDER BY hits DESC";

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $rows = $wpdb->get_results($wpdb->prepare($sql, $start, $start, $start), ARRAY_A);
        return is_array($rows) ? $rows : [];
    }

    public function totalHits(int $days = 7): int
    {
        global $wpdb;

        $days = max(1, min(365, (int) $days));
        $start = gmdate('Y-m-d', time() - (($days - 1) * DAY_IN_SECONDS));

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "SELECT SUM(hits) FROM {$this->table} WHERE day >= %s";
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $v = $wpdb->get_var($wpdb->prepare($sql, $start));
        return (int) $v;
    }

    public function purgeOlderThan(int $retentionDays): int
    {
        global $wpdb;

        $retentionDays = max(1, min(365, (int) $retentionDays));
        $cut = gmdate('Y-m-d', time() - ($retentionDays * DAY_IN_SECONDS));

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "DELETE FROM {$this->table} WHERE day < %s";
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $wpdb->query($wpdb->prepare($sql, $cut));
        return (int) $wpdb->rows_affected;
    }

    public function clearAll(): void
    {
        global $wpdb;
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $wpdb->query("TRUNCATE TABLE {$this->table}");
    }
}
