<?php
/**
 * admin/statistics/statistics_ajax.php
 *
 * Liefert Statistikdaten als JSON:
 * - KPIs (Totals)
 * - Zeitreihe pro Tag (labels + series arrays)
 * - Stundenverteilung 00-23 (hourly)
 * - Heatmap Wochentag x Stunde (Opens unique / Klicks unique)
 *
 * Filter:
 * - from (YYYY-MM-DD)
 * - to   (YYYY-MM-DD)
 * - campaign_id (optional)
 * - list_id (optional)
 *
 * Logging:
 * - Jede SQL-Operation in try/catch
 * - ErrorLogger::logSqlSuccess / logSqlError
 */

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

header('Content-Type: application/json; charset=utf-8');

$auth = new AuthController();
if (!$auth->isLoggedIn()) {
    http_response_code(403);
    echo json_encode(['success' => false, 'error' => 'Nicht autorisiert']);
    exit;
}

$db = Database::getInstance();

// ------------------------------------------------------------
// Eingaben lesen + validieren
// ------------------------------------------------------------
$from = $_POST['from'] ?? '';
$to   = $_POST['to'] ?? '';
$campaignId = $_POST['campaign_id'] ?? '';
$listId     = $_POST['list_id'] ?? '';

function isValidDate($date) {
    $d = DateTime::createFromFormat('Y-m-d', $date);
    return $d && $d->format('Y-m-d') === $date;
}

if (!isValidDate($from) || !isValidDate($to)) {
    echo json_encode(['success' => false, 'error' => 'Ungültiger Zeitraum.']);
    exit;
}

// Zeitraum als datetime (inkl. kompletter "to"-Tag)
$fromDt = $from . ' 00:00:00';
$toDt   = $to   . ' 23:59:59';

// ------------------------------------------------------------
// WHERE Builder für send_queue Filter
// ------------------------------------------------------------
$whereSq = [];
$paramsSq = [];

$whereSq[] = "1=1";

if ($campaignId !== '' && ctype_digit((string)$campaignId)) {
    $whereSq[] = "sq.campaign_id = :campaign_id";
    $paramsSq[':campaign_id'] = (int)$campaignId;
}

if ($listId !== '' && ctype_digit((string)$listId)) {
    $whereSq[] = "sq.list_id = :list_id";
    $paramsSq[':list_id'] = (int)$listId;
}

$whereSqSql = implode(' AND ', $whereSq);
$hasQueueFilters = count($paramsSq) > 0;
$openJoinSql = $hasQueueFilters ? 'INNER JOIN send_queue sq ON sq.id = eo.queue_id' : '';
$openWhereFilterSql = $hasQueueFilters ? "AND $whereSqSql" : '';
$clickJoinSql = $hasQueueFilters ? 'INNER JOIN send_queue sq ON sq.id = elt.queue_id' : '';
$clickWhereFilterSql = $hasQueueFilters ? "AND $whereSqSql" : '';
$bounceJoinSql = $hasQueueFilters ? 'INNER JOIN send_queue sq ON sq.id = eb.queue_id' : '';
$bounceWhereFilterSql = $hasQueueFilters ? "AND $whereSqSql" : '';

// ------------------------------------------------------------
// Hilfsfunktionen: Labels
// ------------------------------------------------------------
function buildDateLabels($from, $to) {
    $labels = [];
    $start = new DateTime($from);
    $end = new DateTime($to);
    $end->setTime(0,0,0);

    while ($start <= $end) {
        $labels[] = $start->format('Y-m-d');
        $start->modify('+1 day');
    }
    return $labels;
}

$labels = buildDateLabels($from, $to);
$labelIndex = array_flip($labels);

// Stundenlabels 00..23
$hourLabels = [];
for ($h = 0; $h < 24; $h++) {
    $hourLabels[] = str_pad((string)$h, 2, '0', STR_PAD_LEFT);
}

// Heatmap Day Labels (Mo..So)
// Wir mappen MySQL DAYOFWEEK(): 1=Sonntag ... 7=Samstag
// -> Wir wollen: Mo,Di,Mi,Do,Fr,Sa,So
$dayLabels = ['Mo','Di','Mi','Do','Fr','Sa','So'];

// ------------------------------------------------------------
// Datencontainer
// ------------------------------------------------------------
$kpis = [
    'sent' => 0,
    'errors' => 0,
    'opens_unique' => 0,
    'opens_total' => 0,
    'clicks_unique' => 0,
    'clicks_total' => 0,
    'bounces' => 0,
    'unsubs' => 0,
    'pdf' => 0,
];

$series = [
    'labels' => $labels,
    'sent' => array_fill(0, count($labels), 0),
    'opens_unique' => array_fill(0, count($labels), 0),
    'clicks_unique' => array_fill(0, count($labels), 0),
    'bounces' => array_fill(0, count($labels), 0),
    'unsubs' => array_fill(0, count($labels), 0),
];

$hourly = [
    'labels' => $hourLabels,
    'sent' => array_fill(0, 24, 0),
    'opens_unique' => array_fill(0, 24, 0),
    'clicks_unique' => array_fill(0, 24, 0),
    'bounces' => array_fill(0, 24, 0),
    'unsubs' => array_fill(0, 24, 0),
];

// Heatmap-Matrix: 7 Tage (Mo..So) x 24 Stunden
$heatOpens = array_fill(0, 7, array_fill(0, 24, 0));
$heatClicks = array_fill(0, 7, array_fill(0, 24, 0));

/**
 * Mappt MySQL DAYOFWEEK (1=So..7=Sa) auf Index 0..6 für Mo..So
 */
function mapDayOfWeekToIndexMoSo($mysqlDayOfWeek) {
    // mysql: 1=So,2=Mo,3=Di,4=Mi,5=Do,6=Fr,7=Sa
    // target indices: 0=Mo,1=Di,2=Mi,3=Do,4=Fr,5=Sa,6=So
    switch ((int)$mysqlDayOfWeek) {
        case 2: return 0; // Mo
        case 3: return 1; // Di
        case 4: return 2; // Mi
        case 5: return 3; // Do
        case 6: return 4; // Fr
        case 7: return 5; // Sa
        case 1: return 6; // So
        default: return null;
    }
}

try {
    // ============================================================
    // KPI: Versendet
    // ============================================================
    $sql = "
        SELECT COUNT(*) AS cnt
        FROM send_queue sq
        WHERE $whereSqSql
          AND sq.status = 'sent'
          AND sq.sent_at BETWEEN :fromDt AND :toDt
    ";
    $params = array_merge($paramsSq, [':fromDt' => $fromDt, ':toDt' => $toDt]);
    $stmt = $db->prepare($sql);
    $stmt->execute($params);
    $kpis['sent'] = (int)$stmt->fetchColumn();
    ErrorLogger::logSqlSuccess('statistics_ajax.php:kpi_sent | SQL: ' . $sql . ' | params: ' . json_encode($params, JSON_UNESCAPED_UNICODE));

    // ============================================================
    // KPI: Fehler (status=error, Zeitpunkt created_at)
    // ============================================================
    $sql = "
        SELECT COUNT(*) AS cnt
        FROM send_queue sq
        WHERE $whereSqSql
          AND sq.status = 'error'
          AND sq.created_at BETWEEN :fromDt AND :toDt
    ";
    $params = array_merge($paramsSq, [':fromDt' => $fromDt, ':toDt' => $toDt]);
    $stmt = $db->prepare($sql);
    $stmt->execute($params);
    $kpis['errors'] = (int)$stmt->fetchColumn();
    ErrorLogger::logSqlSuccess('statistics_ajax.php:kpi_errors | SQL: ' . $sql . ' | params: ' . json_encode($params, JSON_UNESCAPED_UNICODE));

    // ============================================================
    // KPI: Opens (total + unique queue_id)
    // ============================================================
    $sql = "
        SELECT
            COUNT(*) AS total_opens,
            COUNT(DISTINCT eo.queue_id) AS unique_opens
        FROM email_opens eo
        $openJoinSql
        WHERE eo.opened_at BETWEEN :fromDt AND :toDt
          $openWhereFilterSql
    ";
    $params = array_merge($paramsSq, [':fromDt' => $fromDt, ':toDt' => $toDt]);
    $stmt = $db->prepare($sql);
    $stmt->execute($params);
    $row = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
    $kpis['opens_total']  = (int)($row['total_opens'] ?? 0);
    $kpis['opens_unique'] = (int)($row['unique_opens'] ?? 0);
    ErrorLogger::logSqlSuccess('statistics_ajax.php:kpi_opens | SQL: ' . $sql . ' | params: ' . json_encode($params, JSON_UNESCAPED_UNICODE));

    // ============================================================
    // KPI: Klicks (total + unique queue_id)
    // ============================================================
    $sql = "
        SELECT
            COUNT(*) AS total_clicks,
            COUNT(DISTINCT elt.queue_id) AS unique_clicks
        FROM email_link_tracking elt
        $clickJoinSql
        WHERE elt.clicked_at IS NOT NULL
          AND elt.clicked_at BETWEEN :fromDt AND :toDt
          $clickWhereFilterSql
    ";
    $params = array_merge($paramsSq, [':fromDt' => $fromDt, ':toDt' => $toDt]);
    $stmt = $db->prepare($sql);
    $stmt->execute($params);
    $row = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
    $kpis['clicks_total']  = (int)($row['total_clicks'] ?? 0);
    $kpis['clicks_unique'] = (int)($row['unique_clicks'] ?? 0);
    ErrorLogger::logSqlSuccess('statistics_ajax.php:kpi_clicks | SQL: ' . $sql . ' | params: ' . json_encode($params, JSON_UNESCAPED_UNICODE));

    // ============================================================
    // KPI: Bounces
    // ============================================================
    $sql = "
        SELECT COUNT(*) AS cnt
        FROM email_bounces eb
        $bounceJoinSql
        WHERE eb.logged_at BETWEEN :fromDt AND :toDt
          $bounceWhereFilterSql
    ";
    $params = array_merge($paramsSq, [':fromDt' => $fromDt, ':toDt' => $toDt]);
    $stmt = $db->prepare($sql);
    $stmt->execute($params);
    $kpis['bounces'] = (int)$stmt->fetchColumn();
    ErrorLogger::logSqlSuccess('statistics_ajax.php:kpi_bounces | SQL: ' . $sql . ' | params: ' . json_encode($params, JSON_UNESCAPED_UNICODE));

    // ============================================================
    // KPI: Abmeldungen (global)
    // ============================================================
    $sql = "
        SELECT COUNT(*) AS cnt
        FROM recipients r
        WHERE r.unsubscribed_at IS NOT NULL
          AND r.unsubscribed_at BETWEEN :fromDt AND :toDt
    ";
    $params = [':fromDt' => $fromDt, ':toDt' => $toDt];
    $stmt = $db->prepare($sql);
    $stmt->execute($params);
    $kpis['unsubs'] = (int)$stmt->fetchColumn();
    ErrorLogger::logSqlSuccess('statistics_ajax.php:kpi_unsubs | SQL: ' . $sql . ' | params: ' . json_encode($params, JSON_UNESCAPED_UNICODE));

    // ============================================================
    // KPI: PDF-Downloads (global)
    // ============================================================
    $sql = "
        SELECT COUNT(*) AS cnt
        FROM pdf_download_stats pds
        WHERE pds.downloaded_at BETWEEN :fromDt AND :toDt
    ";
    $params = [':fromDt' => $fromDt, ':toDt' => $toDt];
    $stmt = $db->prepare($sql);
    $stmt->execute($params);
    $kpis['pdf'] = (int)$stmt->fetchColumn();
    ErrorLogger::logSqlSuccess('statistics_ajax.php:kpi_pdf | SQL: ' . $sql . ' | params: ' . json_encode($params, JSON_UNESCAPED_UNICODE));

    // ============================================================
    // Zeitreihe pro Tag: Versendet
    // ============================================================
    $sql = "
        SELECT DATE(sq.sent_at) AS d, COUNT(*) AS cnt
        FROM send_queue sq
        WHERE $whereSqSql
          AND sq.status = 'sent'
          AND sq.sent_at BETWEEN :fromDt AND :toDt
        GROUP BY DATE(sq.sent_at)
    ";
    $params = array_merge($paramsSq, [':fromDt' => $fromDt, ':toDt' => $toDt]);
    $stmt = $db->prepare($sql);
    $stmt->execute($params);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    ErrorLogger::logSqlSuccess('statistics_ajax.php:series_sent | SQL: ' . $sql . ' | params: ' . json_encode($params, JSON_UNESCAPED_UNICODE));

    foreach ($rows as $r) {
        $d = $r['d'] ?? null;
        if ($d !== null && isset($labelIndex[$d])) {
            $series['sent'][$labelIndex[$d]] = (int)$r['cnt'];
        }
    }

    // ============================================================
    // Zeitreihe pro Tag: Opens unique
    // ============================================================
    $sql = "
        SELECT DATE(eo.opened_at) AS d, COUNT(DISTINCT eo.queue_id) AS cnt
        FROM email_opens eo
        $openJoinSql
        WHERE eo.opened_at BETWEEN :fromDt AND :toDt
          $openWhereFilterSql
        GROUP BY DATE(eo.opened_at)
    ";
    $params = array_merge($paramsSq, [':fromDt' => $fromDt, ':toDt' => $toDt]);
    $stmt = $db->prepare($sql);
    $stmt->execute($params);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    ErrorLogger::logSqlSuccess('statistics_ajax.php:series_opens_unique | SQL: ' . $sql . ' | params: ' . json_encode($params, JSON_UNESCAPED_UNICODE));

    foreach ($rows as $r) {
        $d = $r['d'] ?? null;
        if ($d !== null && isset($labelIndex[$d])) {
            $series['opens_unique'][$labelIndex[$d]] = (int)$r['cnt'];
        }
    }

    // ============================================================
    // Zeitreihe pro Tag: Klicks unique
    // ============================================================
    $sql = "
        SELECT DATE(elt.clicked_at) AS d, COUNT(DISTINCT elt.queue_id) AS cnt
        FROM email_link_tracking elt
        $clickJoinSql
        WHERE elt.clicked_at IS NOT NULL
          AND elt.clicked_at BETWEEN :fromDt AND :toDt
          $clickWhereFilterSql
        GROUP BY DATE(elt.clicked_at)
    ";
    $params = array_merge($paramsSq, [':fromDt' => $fromDt, ':toDt' => $toDt]);
    $stmt = $db->prepare($sql);
    $stmt->execute($params);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    ErrorLogger::logSqlSuccess('statistics_ajax.php:series_clicks_unique | SQL: ' . $sql . ' | params: ' . json_encode($params, JSON_UNESCAPED_UNICODE));

    foreach ($rows as $r) {
        $d = $r['d'] ?? null;
        if ($d !== null && isset($labelIndex[$d])) {
            $series['clicks_unique'][$labelIndex[$d]] = (int)$r['cnt'];
        }
    }

    // ============================================================
    // Zeitreihe pro Tag: Bounces
    // ============================================================
    $sql = "
        SELECT DATE(eb.logged_at) AS d, COUNT(*) AS cnt
        FROM email_bounces eb
        $bounceJoinSql
        WHERE eb.logged_at BETWEEN :fromDt AND :toDt
          $bounceWhereFilterSql
        GROUP BY DATE(eb.logged_at)
    ";
    $params = array_merge($paramsSq, [':fromDt' => $fromDt, ':toDt' => $toDt]);
    $stmt = $db->prepare($sql);
    $stmt->execute($params);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    ErrorLogger::logSqlSuccess('statistics_ajax.php:series_bounces | SQL: ' . $sql . ' | params: ' . json_encode($params, JSON_UNESCAPED_UNICODE));

    foreach ($rows as $r) {
        $d = $r['d'] ?? null;
        if ($d !== null && isset($labelIndex[$d])) {
            $series['bounces'][$labelIndex[$d]] = (int)$r['cnt'];
        }
    }

    // ============================================================
    // Zeitreihe pro Tag: Abmeldungen (global)
    // ============================================================
    $sql = "
        SELECT DATE(r.unsubscribed_at) AS d, COUNT(*) AS cnt
        FROM recipients r
        WHERE r.unsubscribed_at IS NOT NULL
          AND r.unsubscribed_at BETWEEN :fromDt AND :toDt
        GROUP BY DATE(r.unsubscribed_at)
    ";
    $params = [':fromDt' => $fromDt, ':toDt' => $toDt];
    $stmt = $db->prepare($sql);
    $stmt->execute($params);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    ErrorLogger::logSqlSuccess('statistics_ajax.php:series_unsubs | SQL: ' . $sql . ' | params: ' . json_encode($params, JSON_UNESCAPED_UNICODE));
    foreach ($rows as $r) {
        $d = $r['d'] ?? null;
        if ($d !== null && isset($labelIndex[$d])) {
            $series['unsubs'][$labelIndex[$d]] = (int)$r['cnt'];
        }
    }

    // ============================================================
    // Stundenverteilung 00-23: Versendet
    // ============================================================
    $sql = "
        SELECT HOUR(sq.sent_at) AS h, COUNT(*) AS cnt
        FROM send_queue sq
        WHERE $whereSqSql
          AND sq.status = 'sent'
          AND sq.sent_at BETWEEN :fromDt AND :toDt
        GROUP BY HOUR(sq.sent_at)
    ";
    $params = array_merge($paramsSq, [':fromDt' => $fromDt, ':toDt' => $toDt]);
    $stmt = $db->prepare($sql);
    $stmt->execute($params);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    ErrorLogger::logSqlSuccess('statistics_ajax.php:hourly_sent | SQL: ' . $sql . ' | params: ' . json_encode($params, JSON_UNESCAPED_UNICODE));

    foreach ($rows as $r) {
        $h = isset($r['h']) ? (int)$r['h'] : null;
        if ($h !== null && $h >= 0 && $h <= 23) {
            $hourly['sent'][$h] = (int)$r['cnt'];
        }
    }

    // ============================================================
    // Stundenverteilung 00-23: Opens unique
    // ============================================================
    $sql = "
        SELECT HOUR(eo.opened_at) AS h, COUNT(DISTINCT eo.queue_id) AS cnt
        FROM email_opens eo
        $openJoinSql
        WHERE eo.opened_at BETWEEN :fromDt AND :toDt
          $openWhereFilterSql
        GROUP BY HOUR(eo.opened_at)
    ";
    $params = array_merge($paramsSq, [':fromDt' => $fromDt, ':toDt' => $toDt]);
    $stmt = $db->prepare($sql);
    $stmt->execute($params);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    ErrorLogger::logSqlSuccess('statistics_ajax.php:hourly_opens_unique | SQL: ' . $sql . ' | params: ' . json_encode($params, JSON_UNESCAPED_UNICODE));

    foreach ($rows as $r) {
        $h = isset($r['h']) ? (int)$r['h'] : null;
        if ($h !== null && $h >= 0 && $h <= 23) {
            $hourly['opens_unique'][$h] = (int)$r['cnt'];
        }
    }

    // ============================================================
    // Stundenverteilung 00-23: Klicks unique
    // ============================================================
    $sql = "
        SELECT HOUR(elt.clicked_at) AS h, COUNT(DISTINCT elt.queue_id) AS cnt
        FROM email_link_tracking elt
        $clickJoinSql
        WHERE elt.clicked_at IS NOT NULL
          AND elt.clicked_at BETWEEN :fromDt AND :toDt
          $clickWhereFilterSql
        GROUP BY HOUR(elt.clicked_at)
    ";
    $params = array_merge($paramsSq, [':fromDt' => $fromDt, ':toDt' => $toDt]);
    $stmt = $db->prepare($sql);
    $stmt->execute($params);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    ErrorLogger::logSqlSuccess('statistics_ajax.php:hourly_clicks_unique | SQL: ' . $sql . ' | params: ' . json_encode($params, JSON_UNESCAPED_UNICODE));

    foreach ($rows as $r) {
        $h = isset($r['h']) ? (int)$r['h'] : null;
        if ($h !== null && $h >= 0 && $h <= 23) {
            $hourly['clicks_unique'][$h] = (int)$r['cnt'];
        }
    }

    // ============================================================
    // Stundenverteilung 00-23: Bounces
    // ============================================================
    $sql = "
        SELECT HOUR(eb.logged_at) AS h, COUNT(*) AS cnt
        FROM email_bounces eb
        $bounceJoinSql
        WHERE eb.logged_at BETWEEN :fromDt AND :toDt
          $bounceWhereFilterSql
        GROUP BY HOUR(eb.logged_at)
    ";
    $params = array_merge($paramsSq, [':fromDt' => $fromDt, ':toDt' => $toDt]);
    $stmt = $db->prepare($sql);
    $stmt->execute($params);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    ErrorLogger::logSqlSuccess('statistics_ajax.php:hourly_bounces | SQL: ' . $sql . ' | params: ' . json_encode($params, JSON_UNESCAPED_UNICODE));

    foreach ($rows as $r) {
        $h = isset($r['h']) ? (int)$r['h'] : null;
        if ($h !== null && $h >= 0 && $h <= 23) {
            $hourly['bounces'][$h] = (int)$r['cnt'];
        }
    }

    // ============================================================
    // Stundenverteilung 00-23: Abmeldungen (global)
    // ============================================================
    $sql = "
        SELECT HOUR(r.unsubscribed_at) AS h, COUNT(*) AS cnt
        FROM recipients r
        WHERE r.unsubscribed_at IS NOT NULL
          AND r.unsubscribed_at BETWEEN :fromDt AND :toDt
        GROUP BY HOUR(r.unsubscribed_at)
    ";
    $params = [':fromDt' => $fromDt, ':toDt' => $toDt];
    $stmt = $db->prepare($sql);
    $stmt->execute($params);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    ErrorLogger::logSqlSuccess('statistics_ajax.php:hourly_unsubs | SQL: ' . $sql . ' | params: ' . json_encode($params, JSON_UNESCAPED_UNICODE));

    foreach ($rows as $r) {
        $h = isset($r['h']) ? (int)$r['h'] : null;
        if ($h !== null && $h >= 0 && $h <= 23) {
            $hourly['unsubs'][$h] = (int)$r['cnt'];
        }
    }
    // ============================================================
    // HEATMAP: Opens unique nach Wochentag x Stunde
    // DAYOFWEEK: 1=So..7=Sa -> wir mappen auf Mo..So
    // ============================================================
    $sql = "
        SELECT DAYOFWEEK(eo.opened_at) AS dow, HOUR(eo.opened_at) AS h, COUNT(DISTINCT eo.queue_id) AS cnt
        FROM email_opens eo
        $openJoinSql
        WHERE eo.opened_at BETWEEN :fromDt AND :toDt
          $openWhereFilterSql
        GROUP BY DAYOFWEEK(eo.opened_at), HOUR(eo.opened_at)
    ";
    $params = array_merge($paramsSq, [':fromDt' => $fromDt, ':toDt' => $toDt]);
    $stmt = $db->prepare($sql);
    $stmt->execute($params);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    ErrorLogger::logSqlSuccess('statistics_ajax.php:heat_opens | SQL: ' . $sql . ' | params: ' . json_encode($params, JSON_UNESCAPED_UNICODE));

    foreach ($rows as $r) {
        $dow = isset($r['dow']) ? (int)$r['dow'] : null;
        $h = isset($r['h']) ? (int)$r['h'] : null;
        $cnt = isset($r['cnt']) ? (int)$r['cnt'] : 0;

        $dIndex = ($dow !== null) ? mapDayOfWeekToIndexMoSo($dow) : null;
        if ($dIndex !== null && $h !== null && $h >= 0 && $h <= 23) {
            $heatOpens[$dIndex][$h] = $cnt;
        }
    }

    // ============================================================
    // HEATMAP: Klicks unique nach Wochentag x Stunde
    // ============================================================
    $sql = "
        SELECT DAYOFWEEK(elt.clicked_at) AS dow, HOUR(elt.clicked_at) AS h, COUNT(DISTINCT elt.queue_id) AS cnt
        FROM email_link_tracking elt
        $clickJoinSql
        WHERE elt.clicked_at IS NOT NULL
          AND elt.clicked_at BETWEEN :fromDt AND :toDt
          $clickWhereFilterSql
        GROUP BY DAYOFWEEK(elt.clicked_at), HOUR(elt.clicked_at)
    ";
    $params = array_merge($paramsSq, [':fromDt' => $fromDt, ':toDt' => $toDt]);
    $stmt = $db->prepare($sql);
    $stmt->execute($params);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    ErrorLogger::logSqlSuccess('statistics_ajax.php:heat_clicks | SQL: ' . $sql . ' | params: ' . json_encode($params, JSON_UNESCAPED_UNICODE));

    foreach ($rows as $r) {
        $dow = isset($r['dow']) ? (int)$r['dow'] : null;
        $h = isset($r['h']) ? (int)$r['h'] : null;
        $cnt = isset($r['cnt']) ? (int)$r['cnt'] : 0;

        $dIndex = ($dow !== null) ? mapDayOfWeekToIndexMoSo($dow) : null;
        if ($dIndex !== null && $h !== null && $h >= 0 && $h <= 23) {
            $heatClicks[$dIndex][$h] = $cnt;
        }
    }

    // ------------------------------------------------------------
    // Response
    // ------------------------------------------------------------
    echo json_encode([
        'success' => true,
        'kpis' => $kpis,
        'series' => $series,
        'hourly' => $hourly,
        'heatmap' => [
            'labels_hours' => $hourLabels,
            'labels_days' => $dayLabels,
            'matrix_opens' => $heatOpens,
            'matrix_clicks' => $heatClicks
        ]
    ]);
    exit;

} catch (Exception $e) {
    ErrorLogger::logSqlError('statistics_ajax.php:global | SQL: multiple | params: ' . json_encode($paramsSq, JSON_UNESCAPED_UNICODE) . ' | error: ' . $e->getMessage());
    echo json_encode(['success' => false, 'error' => 'Fehler beim Erzeugen der Statistik.']);
    exit;
}
