<?php
/**
 * admin/tools/recipients_purge.php
 * -----------------------------------------------------------------------------
 * Tool – Empfänger endgültig löschen (Purge)
 *
 * Fixes:
 *  • Keine Mischung named/positional
 *  • LIMIT/OFFSET inline als int (MariaDB/PDO)
 *
 * Neu:
 *  • Cleanup email_bounces (über recipient_id)
 */

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

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

$db = Database::getInstance();

/* =========================================================================
 * Helper: IN Platzhalter
 * ========================================================================= */
function makeInPlaceholders(int $count): string
{
    return implode(',', array_fill(0, $count, '?'));
}

/* =========================================================================
 * Filter
 * ========================================================================= */
$days = max(0, min(3650, (int)($_GET['days'] ?? 30)));
$includeBounced = isset($_GET['include_bounced']) && (string)$_GET['include_bounced'] === '1';

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

$limitSql  = (int)$limit;
$offsetSql = (int)$offset;

$msg = $_GET['msg'] ?? '';
$notice = null;
$error  = null;

if ($msg === 'deleted') {
    $deleted = (int)($_GET['deleted'] ?? 0);
    $notice = "✅ Endgültig gelöscht: {$deleted} Empfänger.";
} elseif ($msg === 'none_selected') {
    $error = "⚠️ Keine Empfänger ausgewählt.";
} elseif ($msg === 'delete_error') {
    $error = "❌ Fehler beim endgültigen Löschen (Details im Log).";
}

/* =========================================================================
 * POST: Purge ausführen
 * ========================================================================= */
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $ids = $_POST['ids'] ?? [];
    if (!is_array($ids)) $ids = [];

    $ids = array_values(array_unique(array_filter(array_map(function ($v) {
        $v = (int)$v;
        return $v > 0 ? $v : null;
    }, $ids))));

    if (count($ids) === 0) {
        header('Location: recipients_purge.php?msg=none_selected');
        exit;
    }

    $postIncludeBounced = isset($_POST['include_bounced']) && (string)$_POST['include_bounced'] === '1';

    try {
        $db->beginTransaction();

        /* --------------------------------------------------------------
         * 1) Sicherheitscheck: Nur unsubscribed (und optional bounced)
         * -------------------------------------------------------------- */
        $in = makeInPlaceholders(count($ids));
        $sqlSelect = "SELECT id, status FROM recipients WHERE id IN ($in)";
        $stmt = $db->prepare($sqlSelect);
        $stmt->execute($ids);
        ErrorLogger::logSqlSuccess('recipients_purge select recipients | SQL: ' . $sqlSelect . ' | context: ' . json_encode(['ids_count' => count($ids)], JSON_UNESCAPED_UNICODE));

        $rowsSel = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $allowed = [];
        foreach ($rowsSel as $r) {
            $rid = (int)$r['id'];
            $status = (string)$r['status'];

            if ($status === 'unsubscribed' || ($postIncludeBounced && $status === 'bounced')) {
                $allowed[] = $rid;
            }
        }

        if (count($allowed) === 0) {
            $db->rollBack();
            header('Location: recipients_purge.php?msg=none_selected');
            exit;
        }

        /* --------------------------------------------------------------
         * 2) Queue-IDs holen
         * -------------------------------------------------------------- */
        $inAllowed = makeInPlaceholders(count($allowed));
        $sqlQueue = "SELECT id FROM send_queue WHERE recipient_id IN ($inAllowed)";
        $stmt = $db->prepare($sqlQueue);
        $stmt->execute($allowed);
        ErrorLogger::logSqlSuccess('recipients_purge select queue | SQL: ' . $sqlQueue . ' | context: ' . json_encode(['allowed_count' => count($allowed)], JSON_UNESCAPED_UNICODE));

        $queueIds = array_map('intval', $stmt->fetchAll(PDO::FETCH_COLUMN));

        /* --------------------------------------------------------------
         * 3) Tracking/Queue löschen (wenn vorhanden)
         * -------------------------------------------------------------- */
        if (count($queueIds) > 0) {
            $inQ = makeInPlaceholders(count($queueIds));

            $sqlDelOpens = "DELETE FROM email_opens WHERE queue_id IN ($inQ)";
            $stmt = $db->prepare($sqlDelOpens);
            $stmt->execute($queueIds);
            ErrorLogger::logSqlSuccess('recipients_purge delete opens | SQL: ' . $sqlDelOpens . ' | context: ' . json_encode(['queue_ids' => count($queueIds)], JSON_UNESCAPED_UNICODE));

            $sqlDelClicks = "DELETE FROM email_link_tracking WHERE queue_id IN ($inQ)";
            $stmt = $db->prepare($sqlDelClicks);
            $stmt->execute($queueIds);
            ErrorLogger::logSqlSuccess('recipients_purge delete clicks | SQL: ' . $sqlDelClicks . ' | context: ' . json_encode(['queue_ids' => count($queueIds)], JSON_UNESCAPED_UNICODE));

            $sqlDelQueue = "DELETE FROM send_queue WHERE id IN ($inQ)";
            $stmt = $db->prepare($sqlDelQueue);
            $stmt->execute($queueIds);
            ErrorLogger::logSqlSuccess('recipients_purge delete queue | SQL: ' . $sqlDelQueue . ' | context: ' . json_encode(['queue_ids' => count($queueIds)], JSON_UNESCAPED_UNICODE));
        }

        /* --------------------------------------------------------------
         * 3b) NEU: Bounces löschen (email_bounces)
         * --------------------------------------------------------------
         * Wichtig:
         *  • email_bounces hat recipient_id + queue_id + email
         *  • Wir löschen über recipient_id, damit wirklich alles weg ist.
         */
        $sqlDelBounces = "DELETE FROM email_bounces WHERE recipient_id IN ($inAllowed)";
        $stmt = $db->prepare($sqlDelBounces);
        $stmt->execute($allowed);
        ErrorLogger::logSqlSuccess('recipients_purge delete bounces | SQL: ' . $sqlDelBounces . ' | context: ' . json_encode(['allowed_count' => count($allowed)], JSON_UNESCAPED_UNICODE));

        /* --------------------------------------------------------------
         * 4) Tag-Zuordnungen löschen
         * -------------------------------------------------------------- */
        $sqlDelTags = "DELETE FROM recipient_tags WHERE recipient_id IN ($inAllowed)";
        $stmt = $db->prepare($sqlDelTags);
        $stmt->execute($allowed);
        ErrorLogger::logSqlSuccess('recipients_purge delete tags | SQL: ' . $sqlDelTags . ' | context: ' . json_encode(['allowed_count' => count($allowed)], JSON_UNESCAPED_UNICODE));

        /* --------------------------------------------------------------
         * 5) Empfänger löschen
         * -------------------------------------------------------------- */
        $sqlDelRecipients = "DELETE FROM recipients WHERE id IN ($inAllowed)";
        $stmt = $db->prepare($sqlDelRecipients);
        $stmt->execute($allowed);
        $deletedCount = (int)$stmt->rowCount();
        ErrorLogger::logSqlSuccess('recipients_purge delete recipients | SQL: ' . $sqlDelRecipients . ' | context: ' . json_encode(['deleted' => $deletedCount], JSON_UNESCAPED_UNICODE));

        $db->commit();

        header('Location: recipients_purge.php?msg=deleted&deleted=' . $deletedCount);
        exit;

    } catch (Throwable $e) {
        if ($db->inTransaction()) $db->rollBack();
        ErrorLogger::logSqlError('recipients_purge delete transaction | file: ' . __FILE__ . ' | error: ' . $e->getMessage());
        header('Location: recipients_purge.php?msg=delete_error');
        exit;
    }
}

/* =========================================================================
 * GET: Liste laden
 * ========================================================================= */
$rows = [];
$total = 0;

try {
    $statuses = $includeBounced ? ["unsubscribed", "bounced"] : ["unsubscribed"];
    $statusPlaceholders = implode(',', array_fill(0, count($statuses), '?'));

    // COUNT
    $dateCond = "";
    $paramsCount = $statuses;
    if ($days > 0) {
        $dateCond = " AND unsubscribed_at IS NOT NULL AND unsubscribed_at <= (NOW() - INTERVAL ? DAY)";
        $paramsCount[] = $days;
    }

    $sqlCount = "
        SELECT COUNT(*)
          FROM recipients
         WHERE status IN ($statusPlaceholders)
         $dateCond
    ";
    $stmt = $db->prepare($sqlCount);
    $stmt->execute($paramsCount);
    $total = (int)$stmt->fetchColumn();
    ErrorLogger::logSqlSuccess('recipients_purge count | SQL: ' . $sqlCount . ' | context: ' . json_encode(['days' => $days, 'includeBounced' => $includeBounced, 'total' => $total], JSON_UNESCAPED_UNICODE));

    // LIST
    $dateCondList = "";
    $paramsList = $statuses;
    if ($days > 0) {
        $dateCondList = " AND unsubscribed_at IS NOT NULL AND unsubscribed_at <= (NOW() - INTERVAL ? DAY)";
        $paramsList[] = $days;
    }

    $sqlList = "
        SELECT id, email, vorname, nachname, status, unsubscribed_at, created_at
          FROM recipients
         WHERE status IN ($statusPlaceholders)
         $dateCondList
         ORDER BY COALESCE(unsubscribed_at, created_at) ASC
         LIMIT $limitSql OFFSET $offsetSql
    ";

    $stmt = $db->prepare($sqlList);
    $stmt->execute($paramsList);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    ErrorLogger::logSqlSuccess('recipients_purge list | SQL: ' . $sqlList . ' | context: ' . json_encode(['rows' => count($rows)], JSON_UNESCAPED_UNICODE));

} catch (Throwable $e) {
    $error = "Fehler: " . $e->getMessage();
    ErrorLogger::logSqlError('recipients_purge load list | file: ' . __FILE__ . ' | error: ' . $e->getMessage());
}

/* =========================================================================
 * Render
 * ========================================================================= */
$title = 'Tools – Empfänger endgültig löschen';
ob_start();
?>

<div class="page-header h2">
  🧰 Tools &raquo; Empfänger endgültig löschen (Purge)
</div>

<?php if ($notice): ?>
  <div class="alert alert-success 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 mb-0">
      <div class="col-6 col-md-2">
        <label class="form-label mb-1">Abmeldung älter als (Tage)</label>
        <input type="number" name="days" class="form-control" value="<?= (int)$days; ?>" min="0" max="3650">
      </div>

      <div class="col-6 col-md-3">
        <label class="form-label mb-1">Optionen</label>
        <div class="form-check">
          <input class="form-check-input" type="checkbox" name="include_bounced" value="1" id="include_bounced"
                 <?= $includeBounced ? 'checked' : ''; ?>>
          <label class="form-check-label" for="include_bounced">
            bounced mit anzeigen (und löschbar)
          </label>
        </div>
      </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-5">
        <div class="text-muted small">
          <strong>Sicher:</strong> Standardmäßig nur <code>unsubscribed</code>. Purge entfernt zusätzlich Queue/Tracking/Tags/Bounces.
        </div>
      </div>
    </form>
  </div>
</div>

<form method="post" onsubmit="return confirm('Wirklich endgültig löschen? Dies kann nicht rückgängig gemacht werden.');">
  <?php if ($includeBounced): ?>
    <input type="hidden" name="include_bounced" value="1">
  <?php endif; ?>

  <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 style="width:40px;"><input type="checkbox" id="checkAll"></th>
              <th>E-Mail</th>
              <th>Name</th>
              <th>Status</th>
              <th>Unsubscribed</th>
              <th>Created</th>
            </tr>
          </thead>
          <tbody>
          <?php if (!$rows): ?>
            <tr><td colspan="6" class="text-center text-muted p-4">Keine Einträge gefunden.</td></tr>
          <?php else: ?>
            <?php foreach ($rows as $r): ?>
              <tr>
                <td><input type="checkbox" name="ids[]" value="<?= (int)$r['id']; ?>" class="rowcheck"></td>
                <td><code><?= htmlspecialchars($r['email']); ?></code></td>
                <td><?= htmlspecialchars(trim(($r['vorname'] ?? '') . ' ' . ($r['nachname'] ?? ''))); ?></td>
                <td><span class="badge bg-secondary"><?= htmlspecialchars($r['status']); ?></span></td>
                <td class="text-muted small"><?= htmlspecialchars((string)($r['unsubscribed_at'] ?? '')); ?></td>
                <td class="text-muted small"><?= htmlspecialchars((string)($r['created_at'] ?? '')); ?></td>
              </tr>
            <?php endforeach; ?>
          <?php endif; ?>
          </tbody>
        </table>
      </div>
    </div>

    <div class="card-footer d-flex align-items-center justify-content-between">
      <div class="text-muted small">
        Treffer: <?= (int)$total; ?> · Seite <?= (int)$page; ?>
      </div>
      <button type="submit" class="btn btn-danger">🗑️ Endgültig löschen</button>
    </div>
  </div>
</form>

<?php
$totalPages = (int)ceil($total / $limit);
if ($totalPages > 1):
  $params = $_GET;
  $prev = max(1, $page - 1);
  $next = min($totalPages, $page + 1);
?>
<nav class="mt-3">
  <ul class="pagination">
    <?php $params['page'] = $prev; ?>
    <li class="page-item <?= $page <= 1 ? 'disabled' : ''; ?>">
      <a class="page-link" href="recipients_purge.php?<?= htmlspecialchars(http_build_query($params)); ?>">«</a>
    </li>
    <li class="page-item disabled"><span class="page-link"><?= (int)$page; ?> / <?= (int)$totalPages; ?></span></li>
    <?php $params['page'] = $next; ?>
    <li class="page-item <?= $page >= $totalPages ? 'disabled' : ''; ?>">
      <a class="page-link" href="recipients_purge.php?<?= htmlspecialchars(http_build_query($params)); ?>">»</a>
    </li>
  </ul>
</nav>
<?php endif; ?>

<script>
(function () {
  // Auto-dismiss Alerts
  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) {}

  // CheckAll
  try {
    var all = document.getElementById('checkAll');
    if (!all) return;
    all.addEventListener('change', function () {
      document.querySelectorAll('.rowcheck').forEach(function (cb) {
        cb.checked = all.checked;
      });
    });
  } catch (e) {}
})();
</script>

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