<?php
/**
 * Widget: Empfänger-Statistik (Tabelle: recipients)
 * ---------------------------------------------------------------
 * • Gesamtzahl aller Empfänger
 * • Anzahl der Double-Opt-In-Empfänger   (status = 'active')
 * • Aufschlüsselung nach Domains großer Freemailer
 *   → zusätzlich:  jemals geöffnet  |  geöffnet in den letzten 5 Tagen
 *
 * Vorhandene Tabellen / Spalten
 *   recipients:
 *      • email            VARCHAR
 *      • last_opened_at   DATETIME  (NULL, wenn nie geöffnet)
 *   recipient_list_optin:
 *      • recipient_id     FK → recipients.id
 *      • doi_confirmed    TINYINT(1)
 *
 * Hinweise
 *   - ErrorLogger akzeptiert nur EINEN String-Parameter.
 *   - Globale PDO-Instanz $db wird vom Dashboard bereitgestellt.
 */

try {
    /* ============================================================
     * 1) Gesamtsummen & Double-Opt-In
     *    --------------------------------------------------------- */
    $stmt = $db->prepare("
        SELECT
            (SELECT COUNT(*) FROM recipients)                                        AS total,
            (SELECT COUNT(DISTINCT recipient_id)
             FROM   recipient_list_optin
             WHERE  doi_confirmed = 1)                                               AS doi
    ");
    $stmt->execute();
    $totals = $stmt->fetch(PDO::FETCH_ASSOC) ?: ['total' => 0, 'doi' => 0];

    ErrorLogger::logSqlSuccess(__FILE__ . ' | SELECT totals & DOI count');

    /* ============================================================
     * 2) Top-Freemailer inkl. Öffnungsstatistiken
     *    ---------------------------------------------------------
     *    cnt_total         : Anzahl aller Empfänger dieser Domain
     *    cnt_opened_ever   : mind. 1 Öffnung (last_opened_at IS NOT NULL)
     *    cnt_opened_5d     : Öffnung in den letzten 5 Tagen
     * ========================================================== */
    $freemailDomains = [
        'gmail.com', 'googlemail.com',
        'yahoo.com',
        'hotmail.com', 'outlook.com', 'live.com', 'hotmail.de', 'outlook.de',
        'gmx.de', 'gmx.net',
        'web.de',
        't-online.de',
        'aol.com',
        'icloud.com',
        'mail.ru'
    ];
    $in  = implode(',', array_fill(0, count($freemailDomains), '?'));

    $sql = "
        SELECT
            LOWER(SUBSTRING_INDEX(email,'@',-1))                                         AS domain,
            COUNT(*)                                                                     AS cnt_total,
            SUM(CASE WHEN last_opened_at IS NOT NULL THEN 1 ELSE 0 END)                 AS cnt_opened_ever,
            SUM(CASE WHEN last_opened_at >= DATE_SUB(NOW(), INTERVAL 5 DAY)
                      THEN 1 ELSE 0 END)                                                AS cnt_opened_5d
        FROM recipients
        WHERE LOWER(SUBSTRING_INDEX(email,'@',-1)) IN ($in)
        GROUP BY domain
        ORDER BY cnt_total DESC
    ";
    $stmt2 = $db->prepare($sql);
    $stmt2->execute($freemailDomains);
    $domainStats = $stmt2->fetchAll(PDO::FETCH_ASSOC);

    ErrorLogger::logSqlSuccess(__FILE__ . ' | SELECT domain stats incl. opens');

} catch (Throwable $e) {
    /* ============================================================
     * Fehlerbehandlung
     * ========================================================== */
    ErrorLogger::logSqlError(__FILE__ . ' | ' . $e->getMessage());
    echo "
    <div class='alert alert-danger auto-dismiss' role='alert'>
        <i class='ti ti-bug me-1'></i>
        Leider konnten die Empfängerstatistiken nicht geladen werden. Bitte später erneut versuchen.
    </div>";
    return;
}
?>

<!-- ============================================================
     3) HTML-Ausgabe (Bootstrap 5 + TML-Icons)
     ============================================================ -->
<?php
/* ----------------------------------------------------------------
 *  PHP-Teil (Queries + Logging) bleibt unverändert
 * ---------------------------------------------------------------- */
?>

<div class="card shadow-sm h-100">
  <div class="card-body">

    <!-- Header ---------------------------------------------------- -->
    <div class="d-flex align-items-center mb-3">
      <i class="ti ti-users fs-3 me-2"></i>
      <h5 class="card-title mb-0">Empfänger</h5>
    </div>

    <!-- Kennzahlen (gesamt | DOI) --------------------------------- -->
    <div class="row text-center mb-3">
      <div class="col-6 border-end">
        <span class="fs-4 fw-bold"
              data-bs-toggle="tooltip" data-bs-placement="top"
              title="Gesamtanzahl aller Empfänger">
              <?= number_format($totals['total'],0,',','.') ?>
        </span><br>
        <small class="text-muted">gesamt</small>
      </div>
      <div class="col-6">
        <span class="fs-4 fw-bold"
              data-bs-toggle="tooltip" data-bs-placement="top"
              title="Empfänger mit bestätigtem Double-Opt-In">
              <?= number_format($totals['doi'],0,',','.') ?>
        </span><br>
        <small class="text-muted">Double Opt-In</small>
      </div>
    </div>

    <!-- Domain-Liste ---------------------------------------------- -->
    <h6 class="text-muted fw-semibold small mb-2">
      <i class="ti ti-mail-opened me-1"></i>Freemailer&nbsp;Top&nbsp;<?= count($domainStats) ?>
    </h6>

    <ul class="list-group list-group-flush small">
      <?php if ($domainStats): ?>
        <?php foreach ($domainStats as $row): ?>
          <li class="list-group-item d-flex justify-content-between align-items-center px-0">
            <span>
              <i class="ti ti-at me-1"></i><?= htmlspecialchars($row['domain']) ?>
            </span>
            <!-- Zahlen-Badges: total | ever | 5d ------------ -->
            <span class="d-flex gap-1">
              <span class="badge bg-primary-subtle text-primary fw-semibold"
                    data-bs-toggle="tooltip" data-bs-placement="top"
                    title="Empfänger dieser Domain gesamt">
                    <?= number_format($row['cnt_total'],0,',','.') ?>
              </span>
              <span class="badge bg-success-subtle text-success fw-semibold"
                    data-bs-toggle="tooltip" data-bs-placement="top"
                    title="Empfänger, die mindestens einmal geöffnet haben">
                    <?= number_format($row['cnt_opened_ever'],0,',','.') ?>
              </span>
              <span class="badge bg-warning-subtle text-warning fw-semibold"
                    data-bs-toggle="tooltip" data-bs-placement="top"
                    title="Empfänger, die in den letzten 5 Tagen geöffnet haben">
                    <?= number_format($row['cnt_opened_5d'],0,',','.') ?>
              </span>
            </span>
          </li>
        <?php endforeach; ?>
      <?php else: ?>
        <li class="list-group-item px-0">
          <em class="text-muted">Keine Freemailer gefunden</em>
        </li>
      <?php endif; ?>
    </ul>

  </div>
</div>

<!-- --------------------------------------------------------------
     Tooltip-Initialisierung (einmal pro Seite)
     ------------------------------------------------------------ -->
<script>
  document.addEventListener('DOMContentLoaded', function () {
    var tooltipTriggerList = [].slice.call(document.querySelectorAll('[data-bs-toggle="tooltip"]'));
    tooltipTriggerList.forEach(function (tooltipTriggerEl) {
      new bootstrap.Tooltip(tooltipTriggerEl);
    });
  });
</script>
