<?php
/**
 * admin/tools/fake_engagement.php
 * -----------------------------------------------------------------------------
 * ✅ MR9 Tool: Empfänger mit Fake-Engagement (Scanner/Proxy) anzeigen
 *
 * Fake-Definition:
 * - Klick/Open zählt als "fake", wenn:
 *   suspicious_ips.ip_address == Event-IP (nach CONVERT auf utf8mb4)
 *   UND Event-Zeit innerhalb suspicious_ips.window_start/window_end
 *   UND suspicious_ips.suspicion_score >= min_score
 *
 * Fix für Collation/Charset:
 * - Manche IP-Spalten sind utf8mb3, andere utf8mb4.
 * - Deshalb vergleichen wir über:
 *   s.ip_address = (CONVERT(event.ip_address USING utf8mb4) COLLATE utf8mb4_unicode_ci)
 *
 * Tabellen:
 * - email_link_tracking (queue_id, clicked_at, ip_address, ...)
 * - email_opens        (queue_id, opened_at, ip_address, ...)
 * - send_queue         (id, recipient_id, ...)
 * - recipients         (id, email, ...)
 * - suspicious_ips     (ip_address, window_start, window_end, suspicion_score, ...)
 */

session_start();
require_once __DIR__ . '/../../includes/init.php';
require_once __DIR__ . '/../../auth/AuthController.php';

$auth = new AuthController();
if (!$auth->isLoggedIn()) {
    header('Location: ' . $adminLoginPath);
    exit;
}

$db = Database::getInstance();

/* =========================================================================
 * Konfiguration (bei dir final)
 * ========================================================================= */
const RECIPIENT_TABLE = 'recipients';
const RECIPIENT_EMAIL = 'email';

/* =========================================================================
 * Filter
 * ========================================================================= */
$days = max(1, min(365, (int)($_GET['days'] ?? 30)));
$minScore = max(0, (int)($_GET['min_score'] ?? 3));

$page  = max(1, (int)($_GET['page'] ?? 1));
$limit = 50;
$offset = ($page - 1) * $limit;

$notice = null;
$error  = null;
$rows   = [];
$total  = 0;

try {

    /* =====================================================================
     * COUNT (für Pagination)
     * ===================================================================== */
    $sqlCount = "
        SELECT COUNT(*) FROM (
            -- Fake Klick-Empfänger
            SELECT r." . RECIPIENT_EMAIL . " AS email
            FROM email_link_tracking c
            INNER JOIN send_queue q ON q.id = c.queue_id
            INNER JOIN " . RECIPIENT_TABLE . " r ON r.id = q.recipient_id
            INNER JOIN suspicious_ips s
                ON s.ip_address = (CONVERT(c.ip_address USING utf8mb4) COLLATE utf8mb4_unicode_ci)
               AND c.clicked_at BETWEEN s.window_start AND s.window_end
               AND s.suspicion_score >= :minScore
            WHERE c.clicked_at >= (NOW() - INTERVAL :days DAY)
              AND c.ip_address IS NOT NULL AND c.ip_address != ''
            GROUP BY email

            UNION

            -- Fake Open-Empfänger
            SELECT r." . RECIPIENT_EMAIL . " AS email
            FROM email_opens o
            INNER JOIN send_queue q ON q.id = o.queue_id
            INNER JOIN " . RECIPIENT_TABLE . " r ON r.id = q.recipient_id
            INNER JOIN suspicious_ips s
                ON s.ip_address = (CONVERT(o.ip_address USING utf8mb4) COLLATE utf8mb4_unicode_ci)
               AND o.opened_at BETWEEN s.window_start AND s.window_end
               AND s.suspicion_score >= :minScore
            WHERE o.opened_at >= (NOW() - INTERVAL :days DAY)
              AND o.ip_address IS NOT NULL AND o.ip_address != ''
            GROUP BY email
        ) t
    ";

    try {
        $stmt = $db->prepare($sqlCount);
        $stmt->bindValue(':minScore', $minScore, PDO::PARAM_INT);
        $stmt->bindValue(':days', $days, PDO::PARAM_INT);
        $stmt->execute();
        $total = (int)$stmt->fetchColumn();

        ErrorLogger::logSqlSuccess('fake_engagement count | SQL: ' . $sqlCount . ' | context: ' . json_encode(['minScore' => $minScore, 'days' => $days, 'total' => $total], JSON_UNESCAPED_UNICODE));
    } catch (Throwable $e) {
        ErrorLogger::logSqlError('fake_engagement count | SQL: ' . $sqlCount . ' | context: ' . json_encode(['minScore' => $minScore, 'days' => $days], JSON_UNESCAPED_UNICODE) . ' | error: ' . $e->getMessage());
        throw $e;
    }

    /* =====================================================================
     * LISTE: Aggregation pro Empfänger
     * ===================================================================== */
    $sqlList = "
        SELECT
            x.email,
            SUM(x.fake_clicks) AS fake_clicks,
            SUM(x.fake_opens)  AS fake_opens,
            MAX(x.last_fake_click_at) AS last_fake_click_at,
            MAX(x.last_fake_open_at)  AS last_fake_open_at
        FROM (
            -- Fake Klicks
            SELECT
                r." . RECIPIENT_EMAIL . " AS email,
                COUNT(*) AS fake_clicks,
                0 AS fake_opens,
                MAX(c.clicked_at) AS last_fake_click_at,
                NULL AS last_fake_open_at
            FROM email_link_tracking c
            INNER JOIN send_queue q ON q.id = c.queue_id
            INNER JOIN " . RECIPIENT_TABLE . " r ON r.id = q.recipient_id
            INNER JOIN suspicious_ips s
                ON s.ip_address = (CONVERT(c.ip_address USING utf8mb4) COLLATE utf8mb4_unicode_ci)
               AND c.clicked_at BETWEEN s.window_start AND s.window_end
               AND s.suspicion_score >= :minScore
            WHERE c.clicked_at >= (NOW() - INTERVAL :days DAY)
              AND c.ip_address IS NOT NULL AND c.ip_address != ''
            GROUP BY email

            UNION ALL

            -- Fake Opens
            SELECT
                r." . RECIPIENT_EMAIL . " AS email,
                0 AS fake_clicks,
                COUNT(*) AS fake_opens,
                NULL AS last_fake_click_at,
                MAX(o.opened_at) AS last_fake_open_at
            FROM email_opens o
            INNER JOIN send_queue q ON q.id = o.queue_id
            INNER JOIN " . RECIPIENT_TABLE . " r ON r.id = q.recipient_id
            INNER JOIN suspicious_ips s
                ON s.ip_address = (CONVERT(o.ip_address USING utf8mb4) COLLATE utf8mb4_unicode_ci)
               AND o.opened_at BETWEEN s.window_start AND s.window_end
               AND s.suspicion_score >= :minScore
            WHERE o.opened_at >= (NOW() - INTERVAL :days DAY)
              AND o.ip_address IS NOT NULL AND o.ip_address != ''
            GROUP BY email
        ) x
        GROUP BY x.email
        ORDER BY (SUM(x.fake_clicks) + SUM(x.fake_opens)) DESC, x.email ASC
        LIMIT :limit OFFSET :offset
    ";

    try {
        $stmt = $db->prepare($sqlList);
        $stmt->bindValue(':minScore', $minScore, PDO::PARAM_INT);
        $stmt->bindValue(':days', $days, PDO::PARAM_INT);
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        ErrorLogger::logSqlSuccess('fake_engagement list | SQL: ' . $sqlList . ' | context: ' . json_encode(['minScore' => $minScore, 'days' => $days, 'limit' => $limit, 'offset' => $offset, 'rows' => count($rows)], JSON_UNESCAPED_UNICODE));

        $notice = "Anzeige: " . count($rows) . " von " . $total .
                  " Empfängern mit Fake-Events (letzte {$days} Tage, Score ≥ {$minScore}).";
    } catch (Throwable $e) {
        ErrorLogger::logSqlError('fake_engagement list | SQL: ' . $sqlList . ' | context: ' . json_encode(['minScore' => $minScore, 'days' => $days, 'limit' => $limit, 'offset' => $offset], JSON_UNESCAPED_UNICODE) . ' | error: ' . $e->getMessage());
        throw $e;
    }

} catch (Throwable $e) {
    $error = "Fehler: " . $e->getMessage();
}

/* =========================================================================
 * Rendering via Template
 * ========================================================================= */
ob_start();
?>
<div class="container-fluid">
  <div class="d-flex align-items-center justify-content-between mb-3">
    <h3 class="mb-0">🛡️ Empfänger mit Fake-Engagement</h3>
    <div class="text-muted small">Quelle: suspicious_ips (Analyse)</div>
  </div>

  <?php if ($notice): ?>
    <div class="alert alert-info alert-dismissible fade show" role="alert" data-autodismiss="9000">
      <?= htmlspecialchars($notice); ?>
      <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Schließen"></button>
    </div>
  <?php endif; ?>

  <?php if ($error): ?>
    <div class="alert alert-danger alert-dismissible fade show" role="alert" data-autodismiss="12000">
      <?= htmlspecialchars($error); ?>
      <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Schließen"></button>
    </div>
  <?php endif; ?>

  <div class="card mb-3">
    <div class="card-body">
      <form method="get" class="row g-2 align-items-end">
        <div class="col-6 col-md-2">
          <label class="form-label mb-1">Zeitraum (Tage)</label>
          <input type="number" name="days" class="form-control" value="<?= (int)$days; ?>" min="1" max="365">
        </div>
        <div class="col-6 col-md-2">
          <label class="form-label mb-1">Min. Score</label>
          <input type="number" name="min_score" class="form-control" value="<?= (int)$minScore; ?>" min="0" max="50">
        </div>
        <div class="col-12 col-md-2 d-grid">
          <button class="btn btn-primary" type="submit">Filtern</button>
        </div>
        <div class="col-12 col-md-6">
          <div class="text-muted small">
            Hinweis: Diese Liste hilft bei Listenpflege, weil Fake-Engagement nicht als „aktiv“ zählen sollte.
          </div>
        </div>
      </form>
    </div>
  </div>

  <div class="card">
    <div class="card-body p-0">
      <div class="table-responsive">
        <table class="table table-striped table-hover mb-0 align-middle">
          <thead class="table-light">
            <tr>
              <th>E-Mail</th>
              <th class="text-end">Fake Klicks</th>
              <th class="text-end">Fake Opens</th>
              <th>Letzter Fake Klick</th>
              <th>Letzter Fake Open</th>
            </tr>
          </thead>
          <tbody>
          <?php if (!$rows): ?>
            <tr>
              <td colspan="5" class="text-center text-muted p-4">Keine Einträge gefunden.</td>
            </tr>
          <?php else: ?>
            <?php foreach ($rows as $r): ?>
              <tr>
                <td><code><?= htmlspecialchars((string)$r['email']); ?></code></td>
                <td class="text-end"><?= (int)$r['fake_clicks']; ?></td>
                <td class="text-end"><?= (int)$r['fake_opens']; ?></td>
                <td class="text-muted small"><?= htmlspecialchars((string)($r['last_fake_click_at'] ?? '')); ?></td>
                <td class="text-muted small"><?= htmlspecialchars((string)($r['last_fake_open_at'] ?? '')); ?></td>
              </tr>
            <?php endforeach; ?>
          <?php endif; ?>
          </tbody>
        </table>
      </div>
    </div>

    <?php
      $totalPages = (int)ceil($total / $limit);
      if ($totalPages > 1):
        $prev = max(1, $page - 1);
        $next = min($totalPages, $page + 1);
        $params = $_GET;
    ?>
    <div class="card-footer d-flex align-items-center justify-content-between">
      <div class="text-muted small">
        Seite <?= (int)$page; ?> / <?= (int)$totalPages; ?> (<?= (int)$total; ?> Empfänger)
      </div>
      <nav>
        <ul class="pagination mb-0">
          <?php $params['page'] = $prev; ?>
          <li class="page-item <?= $page <= 1 ? 'disabled' : ''; ?>">
            <a class="page-link" href="<?= $page <= 1 ? '#' : 'fake_engagement.php?' . htmlspecialchars(http_build_query($params)); ?>">«</a>
          </li>
          <li class="page-item disabled"><span class="page-link"><?= (int)$page; ?></span></li>
          <?php $params['page'] = $next; ?>
          <li class="page-item <?= $page >= $totalPages ? 'disabled' : ''; ?>">
            <a class="page-link" href="<?= $page >= $totalPages ? '#' : 'fake_engagement.php?' . htmlspecialchars(http_build_query($params)); ?>">»</a>
          </li>
        </ul>
      </nav>
    </div>
    <?php endif; ?>
  </div>
</div>

<script>
/**
 * Auto-Dismiss (Bootstrap Alerts)
 */
(function () {
  try {
    document.querySelectorAll('.alert[data-autodismiss]').forEach(function (el) {
      var ms = parseInt(el.getAttribute('data-autodismiss'), 10);
      if (!ms || ms < 1000) return;
      setTimeout(function () {
        if (window.bootstrap && bootstrap.Alert) {
          bootstrap.Alert.getOrCreateInstance(el).close();
        } else {
          el.style.display = 'none';
        }
      }, ms);
    });
  } catch (e) {}
})();
</script>

<?php
$pageContent = ob_get_clean();
include_once __DIR__ . '/../template.php';
