<?php
/**
 * spam_report.php
 * Meldet Honeypot-Treffer (verstecktes Formularfeld befuellt) automatisiert
 * an Stop Forum Spam und sperrt den Abonnenten in phpList.
 *
 * Voraussetzungen:
 * - phpList-Attribut "Honeypot" (Text) - ID unten eintragen
 * - phpList-Attribut "Als Spam bereits gemeldet" (Checkbox) - ID unten eintragen
 * - Stop-Forum-Spam-Account + API-Key
 *
 * Aufruf: per Cron, z. B. taeglich nachts
 */

$dbHost = '127.0.0.1';   // ggf. TCP statt Socket noetig
$dbName = 'DEINE_DB';
$dbUser = 'DEIN_DB_USER';
$dbPass = 'DEIN_DB_PASSWORT';
$sfsApiKey = 'DEIN_STOPFORUMSPAM_API_KEY';

$pdo = new PDO("mysql:host=$dbHost;dbname=$dbName;charset=utf8mb4", $dbUser, $dbPass);

$attrHoneypotId = X;   // ID deines Honeypot-Attributs
$attrReportedId = Y;   // ID deines "Bereits gemeldet"-Attributs

$sql = "SELECT u.id, u.email
        FROM phplist_user_user u
        JOIN phplist_user_user_attribute uah ON uah.userid = u.id AND uah.attributeid = :honeypotId AND uah.value != ''
        LEFT JOIN phplist_user_user_attribute uar ON uar.userid = u.id AND uar.attributeid = :reportedId
        WHERE (uar.value IS NULL OR uar.value = '')";

$stmt = $pdo->prepare($sql);
$stmt->execute(['honeypotId' => $attrHoneypotId, 'reportedId' => $attrReportedId]);
$subscribers = $stmt->fetchAll(PDO::FETCH_ASSOC);

function getSubscriberIp($pdo, $userid) {
    $stmt = $pdo->prepare("SELECT ip FROM phplist_user_user_history 
                            WHERE userid = :uid AND ip IS NOT NULL AND ip != ''
                            ORDER BY date ASC LIMIT 1");
    $stmt->execute(['uid' => $userid]);
    return $stmt->fetchColumn();
}

foreach ($subscribers as $sub) {
    $botIp = getSubscriberIp($pdo, $sub['id']);
    if (!$botIp) {
        echo "Uebersprungen (keine IP in History): {$sub['email']}\n";
        continue;
    }

    $data = http_build_query([
        'email'    => $sub['email'],
        'ip_addr'  => $botIp,
        'username' => $sub['email'],
        'api_key'  => $sfsApiKey,
        'evidence' => 'Automatisch erkannt: Honeypot-Feld auf phpList-Anmeldeseite befuellt',
    ]);

    $ch = curl_init('https://www.stopforumspam.com/add.php');
    curl_setopt($ch, CURLOPT_POST, true);
    curl_setopt($ch, CURLOPT_POSTFIELDS, $data);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    $response = curl_exec($ch);
    curl_close($ch);

    if (strpos($response, 'success') !== false) {
        $upd = $pdo->prepare("INSERT INTO phplist_user_user_attribute (userid, attributeid, value) 
                               VALUES (:uid, :aid, 'on')
                               ON DUPLICATE KEY UPDATE value = 'on'");
        $upd->execute(['uid' => $sub['id'], 'aid' => $attrReportedId]);

        $blk = $pdo->prepare("UPDATE phplist_user_user SET blacklisted = 1 WHERE id = :uid");
        $blk->execute(['uid' => $sub['id']]);

        echo "Gemeldet & geblacklistet: {$sub['email']} (IP: $botIp)\n";
    } else {
        echo "Fehler bei: {$sub['email']} - $response\n";
    }
    sleep(1);
}