<?php
/**
 * cron/analyze_suspicious_ips.php
 * -----------------------------------------------------------------------------
 * ✅ MR9 Analyse-Cron: Verdächtige IPs aus Tracking-Daten erkennen und speichern
 *
 * Datenquellen:
 * - Klicks: email_link_tracking (queue_id, clicked_at, ip_address)
 * - Opens : email_opens        (queue_id, opened_at, ip_address)
 *
 * Ziel:
 * - IPs identifizieren, die z.B. sehr viele Klicks (Burst) oder sehr viele queue_ids
 *   im gleichen Zeitraum erzeugen (typische Scanner/Proxy/Firewall-Checks)
 * - Ergebnisse in suspicious_ips speichern (Analyse-only, keine Sperrung/Filterung)
 *
 * Logging:
 * - Jede SQL-Operation loggt Erfolg/Fehler über ErrorLogger::logSqlSuccess / logSqlError
 * - try/catch für saubere Fehlerbehandlung
 */

require_once __DIR__ . '/../includes/init.php';

$db = Database::getInstance();

/**
 * -----------------------------------------------------------------------------
 * 1) Konfiguration
 * -----------------------------------------------------------------------------
 * Zeitfenster:
 * - Standard: letzte 24 Stunden
 * - Optional: Per CLI-Parameter überschreiben, z.B.:
 *   php analyze_suspicious_ips.php 6   (für letzte 6 Stunden)
 */
$hours = 24;
if (PHP_SAPI === 'cli' && isset($argv[1]) && is_numeric($argv[1])) {
    $hours = max(1, (int)$argv[1]); // mindestens 1 Stunde
}

$windowEnd = new DateTime('now');
$windowStart = (clone $windowEnd)->modify('-' . $hours . ' hours');

/**
 * -----------------------------------------------------------------------------
 * 2) Heuristik-Schwellen (Analyse-Score, keine Sperre)
 * -----------------------------------------------------------------------------
 * Du kannst diese Werte später über Settings steuerbar machen.
 */
const THRESH_MAX_CLICKS_PER_MIN = 10;   // Burst: ab X Klicks/Minute verdächtig
const THRESH_UNIQ_QUEUE_CLICK   = 10;   // Viele unterschiedliche queue_ids pro IP
const THRESH_TOTAL_CLICKS       = 30;   // Sehr viele Klicks insgesamt im Fenster

/**
 * -----------------------------------------------------------------------------
 * 3) Hilfsfunktion: SQL sicher loggen
 * -----------------------------------------------------------------------------
 * (Nur für konsistente Log-Struktur – kein Muss, aber hält den Code sauber.)
 */
function logSqlOk(string $sql, array $ctx = []): void {
    ErrorLogger::logSqlSuccess($sql, $ctx);
}

function logSqlFail(string $sql, array $ctx, Throwable $e): void {
    ErrorLogger::logSqlError($sql, $ctx, $e->getMessage());
}

try {
    /**
     * -----------------------------------------------------------------------------
     * 4) Aggregation: Klicks pro IP (Totals + uniq queue_ids)
     * -----------------------------------------------------------------------------
     */
    $sqlClickAgg = "
        SELECT
            ip_address AS ip,
            COUNT(*) AS clicks_total,
            COUNT(DISTINCT queue_id) AS uniq_queue_click
        FROM email_link_tracking
        WHERE clicked_at >= :ws
          AND clicked_at <  :we
          AND ip_address IS NOT NULL
          AND ip_address != ''
        GROUP BY ip_address
    ";

    $clickAgg = [];
    try {
        $stmt = $db->prepare($sqlClickAgg);
        $stmt->execute([
            ':ws' => $windowStart->format('Y-m-d H:i:s'),
            ':we' => $windowEnd->format('Y-m-d H:i:s'),
        ]);
        $clickAgg = $stmt->fetchAll(PDO::FETCH_ASSOC);
        logSqlOk($sqlClickAgg, [
            'rows' => count($clickAgg),
            'window_start' => $windowStart->format('Y-m-d H:i:s'),
            'window_end' => $windowEnd->format('Y-m-d H:i:s'),
        ]);
    } catch (Throwable $e) {
        logSqlFail($sqlClickAgg, ['ws' => $windowStart->format('c'), 'we' => $windowEnd->format('c')], $e);
        throw $e; // Ohne Klickdaten macht die Analyse wenig Sinn
    }

    /**
     * -----------------------------------------------------------------------------
     * 5) Burst: max Klicks pro Minute je IP
     * -----------------------------------------------------------------------------
     * Wir zählen Klicks pro IP und Minute und nehmen pro IP das Maximum.
     */
    $sqlBurst = "
        SELECT ip, MAX(clicks_in_minute) AS max_clicks_per_minute
        FROM (
            SELECT
                ip_address AS ip,
                DATE_FORMAT(clicked_at, '%Y-%m-%d %H:%i') AS minute_bucket,
                COUNT(*) AS clicks_in_minute
            FROM email_link_tracking
            WHERE clicked_at >= :ws
              AND clicked_at <  :we
              AND ip_address IS NOT NULL
              AND ip_address != ''
            GROUP BY ip_address, minute_bucket
        ) t
        GROUP BY ip
    ";

    $burstMap = []; // ip => max_clicks_per_minute
    try {
        $stmt = $db->prepare($sqlBurst);
        $stmt->execute([
            ':ws' => $windowStart->format('Y-m-d H:i:s'),
            ':we' => $windowEnd->format('Y-m-d H:i:s'),
        ]);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        foreach ($rows as $r) {
            $burstMap[$r['ip']] = (int)$r['max_clicks_per_minute'];
        }

        logSqlOk($sqlBurst, ['rows' => count($rows)]);
    } catch (Throwable $e) {
        // Burst ist wichtig, aber nicht zwingend – wir loggen und laufen weiter.
        logSqlFail($sqlBurst, [], $e);
    }

    /**
     * -----------------------------------------------------------------------------
     * 6) Opens pro IP (Totals + uniq queue_ids)
     * -----------------------------------------------------------------------------
     * Opens sind ein Signal – je nach Client können sie fehlen oder vorgezogen werden.
     * Trotzdem gut als Kontext.
     */
    $sqlOpenAgg = "
        SELECT
            ip_address AS ip,
            COUNT(*) AS opens_total,
            COUNT(DISTINCT queue_id) AS uniq_queue_open
        FROM email_opens
        WHERE opened_at >= :ws
          AND opened_at <  :we
          AND ip_address IS NOT NULL
          AND ip_address != ''
        GROUP BY ip_address
    ";

    $openMap = [];     // ip => opens_total
    $openUniqMap = []; // ip => uniq_queue_open
    try {
        $stmt = $db->prepare($sqlOpenAgg);
        $stmt->execute([
            ':ws' => $windowStart->format('Y-m-d H:i:s'),
            ':we' => $windowEnd->format('Y-m-d H:i:s'),
        ]);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        foreach ($rows as $r) {
            $ip = (string)$r['ip'];
            $openMap[$ip] = (int)$r['opens_total'];
            $openUniqMap[$ip] = (int)$r['uniq_queue_open'];
        }

        logSqlOk($sqlOpenAgg, ['rows' => count($rows)]);
    } catch (Throwable $e) {
        // Open-Tabelle existiert und passt bei dir – falls doch mal Fehler: nicht hart abbrechen.
        logSqlFail($sqlOpenAgg, [], $e);
    }

    /**
     * -----------------------------------------------------------------------------
     * 7) Insert in suspicious_ips (nur wenn Score > 0)
     * -----------------------------------------------------------------------------
     * Wichtig:
     * - Wir speichern NICHT "alle IPs", sondern nur verdächtige.
     * - Dadurch bleibt die Tabelle klein und aussagekräftig.
     */
    $sqlInsert = "
        INSERT INTO suspicious_ips (
            ip_address, window_start, window_end,
            clicks_total, uniq_queue_click, max_clicks_per_minute,
            opens_total, uniq_queue_open,
            suspicion_score, reasons
        ) VALUES (
            :ip, :ws, :we,
            :clicks_total, :uniq_queue_click, :max_clicks_per_minute,
            :opens_total, :uniq_queue_open,
            :score, :reasons
        )
    ";

    $inserted = 0;

    try {
        $stmtIns = $db->prepare($sqlInsert);

        foreach ($clickAgg as $row) {
            $ip = (string)$row['ip'];

            $clicksTotal = (int)$row['clicks_total'];
            $uniqQueueClick = (int)$row['uniq_queue_click'];
            $maxPerMinute = $burstMap[$ip] ?? 0;

            $opensTotal = $openMap[$ip] ?? 0;
            $uniqQueueOpen = $openUniqMap[$ip] ?? 0;

            // -----------------------------
            // Score + Reasons (Analyse-only)
            // -----------------------------
            $score = 0;
            $reasons = [];

            if ($maxPerMinute >= THRESH_MAX_CLICKS_PER_MIN) {
                $score += 3;
                $reasons[] = "Burst: max_clicks_per_minute={$maxPerMinute}";
            }

            if ($uniqQueueClick >= THRESH_UNIQ_QUEUE_CLICK) {
                $score += 2;
                $reasons[] = "Viele queue_ids (click): uniq_queue_click={$uniqQueueClick}";
            }

            if ($clicksTotal >= THRESH_TOTAL_CLICKS) {
                $score += 1;
                $reasons[] = "Viele Klicks gesamt: clicks_total={$clicksTotal}";
            }

            // Optionales Signal:
            // viele Klicks, aber 0 Opens im gleichen Fenster (kann Pixel-Blocking sein, kann Scanner sein)
            if ($clicksTotal >= 10 && $opensTotal === 0) {
                $score += 1;
                $reasons[] = "Klicks ohne Opens im Fenster";
            }

            // Nur speichern, wenn verdächtig
            if ($score <= 0) {
                continue;
            }

            $stmtIns->execute([
                ':ip' => $ip,
                ':ws' => $windowStart->format('Y-m-d H:i:s'),
                ':we' => $windowEnd->format('Y-m-d H:i:s'),

                ':clicks_total' => $clicksTotal,
                ':uniq_queue_click' => $uniqQueueClick,
                ':max_clicks_per_minute' => (int)$maxPerMinute,

                ':opens_total' => $opensTotal,
                ':uniq_queue_open' => $uniqQueueOpen,

                ':score' => $score,
                ':reasons' => implode(' | ', $reasons),
            ]);

            $inserted++;
        }

        logSqlOk($sqlInsert, [
            'inserted' => $inserted,
            'window_hours' => $hours
        ]);
    } catch (Throwable $e) {
        logSqlFail($sqlInsert, ['window_hours' => $hours], $e);
        throw $e;
    }

    echo "OK: suspicious_ips analysis done. Inserted rows: {$inserted} | Window: {$windowStart->format('Y-m-d H:i:s')} - {$windowEnd->format('Y-m-d H:i:s')}\n";
    exit(0);

} catch (Throwable $e) {
    echo "ERROR: " . $e->getMessage() . "\n";
    exit(1);
}