<?php
/**
 * admin/empfaenger/empfaenger_table_ajax.php
 * ----------------------------------------------------------------------
 * Liefert Tabelle + Pagination als HTML-Fragment für empfaenger.php (AJAX).
 * ----------------------------------------------------------------------
 * Features:
 *  • Filter (Liste, Tag, Tag-Modus has/not_has, Suche, Aktivität, Klicks, DOI, Status)
 *  • Tag-Filter sauber via EXISTS / NOT EXISTS (performant)
 *  • Logging fatal-sicher (damit AJAX nie „hart“ stirbt)
 *  • Fehlerbehandlung via try/catch
 */

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

$auth = new AuthController();
if (!$auth->isLoggedIn()) {
    http_response_code(403);
    exit('Nicht autorisiert');
}

$db = Database::getInstance();

/** Null-sicher escapen */
function e($v): string {
    return htmlspecialchars((string)($v ?? ''), ENT_QUOTES, 'UTF-8');
}

/**
 * Logging helper – fatal-sicher, falls Methoden / Klasse anders sind.
 */
function logSqlOk(string $file, string $msg): void {
    try {
        if (class_exists('ErrorLogger') && method_exists('ErrorLogger', 'logSqlSuccess')) {
            ErrorLogger::logSqlSuccess($file . ' | ' . $msg);
        }
    } catch (Throwable $t) {
        // niemals AJAX killen
    }
}

function logSqlFail(string $file, string $msg): void {
    try {
        if (class_exists('ErrorLogger') && method_exists('ErrorLogger', 'logSqlError')) {
            ErrorLogger::logSqlError($file . ' | ' . $msg);
        } elseif (class_exists('ErrorLogger') && method_exists('ErrorLogger', 'log')) {
            ErrorLogger::log($msg, 'ERROR');
        }
    } catch (Throwable $t) {
        // niemals AJAX killen
    }
}

// POST Filter (inkl. tag_mode)
$filterKeys = ['list_id','tag_id','tag_mode','search','activity_filter','click_filter','doi_filter','status_filter'];
foreach ($filterKeys as $key) {
    $$key = $_POST[$key] ?? '';
}

$page = max(1, (int)($_POST['page'] ?? 1));
$perPage = 500;
$offset = ($page - 1) * $perPage;

try {
    /* =========================================================================
     * WHERE + Params
     * ========================================================================= */
    $where = "WHERE 1";
    $params = [];

    // Liste
    if ($list_id !== '') {
        $where .= " AND EXISTS (
            SELECT 1 FROM recipient_lists rl
            WHERE rl.recipient_id = r.id AND rl.list_id = ?
        )";
        $params[] = $list_id;
    }

    // Tag + Tag-Modus (has / not_has)
    //  - has:     Empfänger hat Tag
    //  - not_has: Empfänger hat Tag NICHT
    if ($tag_id !== '') {
        $tag_mode = $tag_mode ?: 'has';
        if ($tag_mode !== 'has' && $tag_mode !== 'not_has') {
            $tag_mode = 'has';
        }

        if ($tag_mode === 'not_has') {
            $where .= " AND NOT EXISTS (
                SELECT 1 FROM recipient_tags rt
                WHERE rt.recipient_id = r.id AND rt.tag_id = ?
            )";
        } else {
            $where .= " AND EXISTS (
                SELECT 1 FROM recipient_tags rt
                WHERE rt.recipient_id = r.id AND rt.tag_id = ?
            )";
        }

        $params[] = $tag_id;
    }

    // Suche
    if ($search !== '') {
        $where .= " AND r.email LIKE ?";
        $params[] = "%{$search}%";
    }

    // Aktivität
    if ($activity_filter === '2') {
        $where .= " AND (
            r.last_opened_at >= DATE_SUB(NOW(), INTERVAL 2 DAY)
            OR r.last_clicked_at >= DATE_SUB(NOW(), INTERVAL 2 DAY)
        )";
    } elseif ($activity_filter === '90') {
        $where .= " AND (
            r.last_opened_at >= DATE_SUB(NOW(), INTERVAL 90 DAY)
            OR r.last_clicked_at >= DATE_SUB(NOW(), INTERVAL 90 DAY)
        )";        
    } elseif ($activity_filter === '180') {
        $where .= " AND (
            r.last_opened_at >= DATE_SUB(NOW(), INTERVAL 180 DAY)
            OR r.last_clicked_at >= DATE_SUB(NOW(), INTERVAL 180 DAY)
        )";
    } elseif ($activity_filter === '90_180') {
        $where .= " AND (
            (r.last_opened_at BETWEEN DATE_SUB(NOW(), INTERVAL 180 DAY) AND DATE_SUB(NOW(), INTERVAL 90 DAY))
            OR (r.last_clicked_at BETWEEN DATE_SUB(NOW(), INTERVAL 180 DAY) AND DATE_SUB(NOW(), INTERVAL 90 DAY))
        )";        
    } elseif ($activity_filter === '180_360') {
        $where .= " AND (
            (r.last_opened_at BETWEEN DATE_SUB(NOW(), INTERVAL 360 DAY) AND DATE_SUB(NOW(), INTERVAL 180 DAY))
            OR (r.last_clicked_at BETWEEN DATE_SUB(NOW(), INTERVAL 360 DAY) AND DATE_SUB(NOW(), INTERVAL 180 DAY))
        )";
    } elseif ($activity_filter === '360_720') {
        $where .= " AND (
            (r.last_opened_at BETWEEN DATE_SUB(NOW(), INTERVAL 720 DAY) AND DATE_SUB(NOW(), INTERVAL 360 DAY))
            OR (r.last_clicked_at BETWEEN DATE_SUB(NOW(), INTERVAL 720 DAY) AND DATE_SUB(NOW(), INTERVAL 360 DAY))
        )";
    } elseif ($activity_filter === 'inactive') {
        $where .= " AND r.last_opened_at IS NULL AND r.last_clicked_at IS NULL";
    }

    // Klickfilter
    $allowed = ['1d'=>1,'2d'=>2,'5d'=>5,'30d'=>30,'60d'=>60,'90d'=>90];
    if ($click_filter === 'clicked') {
        $where .= " AND r.last_clicked_at IS NOT NULL";
    } elseif ($click_filter === 'not_clicked') {
        $where .= " AND r.last_clicked_at IS NULL";
    } elseif (isset($allowed[$click_filter])) {
        $days = (int)$allowed[$click_filter];
        $where .= " AND r.last_clicked_at >= DATE_SUB(NOW(), INTERVAL {$days} DAY)";
    }

    // DOI (globaler MAX über alle Listen)
    if ($doi_filter === 'confirmed') {
        $where .= " AND opt.doi_confirmed_at IS NOT NULL";
    } elseif ($doi_filter === 'not_confirmed') {
        $where .= " AND opt.doi_confirmed_at IS NULL";
    }

    // Status
    if ($status_filter !== '') {
        $where .= " AND r.status = ?";
        $params[] = $status_filter;
    }

    /* =========================================================================
     * COUNT
     * ========================================================================= */
    $countSql = "
        SELECT COUNT(*)
        FROM recipients r
        LEFT JOIN (
            SELECT recipient_id, MAX(doi_confirmed_at) AS doi_confirmed_at
            FROM recipient_list_optin
            GROUP BY recipient_id
        ) opt ON opt.recipient_id = r.id
        $where
    ";

    $stmt = $db->prepare($countSql);
    $stmt->execute($params);
    $totalRows = (int)$stmt->fetchColumn();
    $totalPages = max(1, (int)ceil($totalRows / $perPage));
    logSqlOk(__FILE__, 'COUNT Empfänger (AJAX)');

    /* =========================================================================
     * SELECT
     * ========================================================================= */
    $selectSql = "
        SELECT r.*, opt.doi_confirmed_at
        FROM recipients r
        LEFT JOIN (
            SELECT recipient_id, MAX(doi_confirmed_at) AS doi_confirmed_at
            FROM recipient_list_optin
            GROUP BY recipient_id
        ) opt ON opt.recipient_id = r.id
        $where
        GROUP BY r.id
        ORDER BY r.id DESC
        LIMIT $perPage OFFSET $offset
    ";

    $stmt = $db->prepare($selectSql);
    $stmt->execute($params);
    $recipients = $stmt->fetchAll(PDO::FETCH_ASSOC);
    logSqlOk(__FILE__, 'SELECT Empfänger (AJAX)');

} catch (Throwable $e) {
    logSqlFail(__FILE__, 'AJAX Table Error: ' . $e->getMessage());
    $recipients = [];
    $totalRows = 0;
    $totalPages = 1;
}

?>

<div class="d-flex justify-content-between align-items-center mb-2">
  <div class="text-muted">
    <span class="badge bg-primary"><?= number_format((int)$totalRows,0,',','.') ?> Datensätze</span>
    <span class="badge bg-secondary">Seite <?= (int)$page ?> / <?= (int)$totalPages ?></span>
  </div>
</div>

<table class="table table-striped table-sm align-middle">
  <thead>
    <tr>
      <th style="width:38px;"><input type="checkbox" id="select_all"></th>
      <th>E-Mail</th>
      <th>Vorname</th>
      <th>Nachname</th>
      <th>Status</th>
      <th>Letzter Öffner</th>
      <th>Letzter Klick</th>
      <th style="width:120px;">Aktionen</th>
    </tr>
  </thead>
  <tbody>
  <?php if (!$recipients): ?>
    <tr><td colspan="8" class="text-center">Keine Datensätze gefunden</td></tr>
  <?php else: foreach ($recipients as $r): ?>
    <tr>
      <td><input type="checkbox" class="recipient-checkbox" value="<?= (int)$r['id'] ?>"></td>
      <td><?= e($r['email']) ?></td>
      <td><?= e($r['vorname']) ?></td>
      <td><?= e($r['nachname']) ?></td>
      <td><?= e($r['status']) ?></td>
      <td><?= e($r['last_opened_at']) ?></td>
      <td><?= e($r['last_clicked_at']) ?></td>
      <td class="d-flex gap-1">
        <a href="empfaenger_edit.php?id=<?= (int)$r['id'] ?>" class="btn btn-sm btn-primary" title="Bearbeiten">✏️</a>
        <a href="empfaenger_verlauf.php?id=<?= (int)$r['id'] ?>" class="btn btn-sm btn-info" title="Verlauf">📬</a>
        <a href="empfaenger_delete.php?id=<?= (int)$r['id'] ?>" class="btn btn-sm btn-danger" title="Löschen">🗑️</a>
      </td>
    </tr>
  <?php endforeach; endif; ?>
  </tbody>
</table>

<?php if ($totalPages > 1): ?>
<nav aria-label="Pagination">
  <ul class="pagination">
    <?php
      $start = max(1, $page - 3);
      $end   = min($totalPages, $page + 3);
      for ($i = $start; $i <= $end; $i++):
        $active = ($i === $page) ? 'active' : '';
    ?>
      <li class="page-item <?= $active ?>">
        <a class="page-link" href="#" onclick="loadRecipients(<?= (int)$i ?>); return false;"><?= (int)$i ?></a>
      </li>
    <?php endfor; ?>
  </ul>
</nav>
<?php endif; ?>
