<?php
/**
 * send/send_queue_cron.php
 * -----------------------------------------------------------------------------
 * Produktions-Cron: baut die Versand-Warteschlange (send_queue) auf.
 * – Geplante & Follow-up-Kampagnen (Teil 1)
 * – Transaktions-Regeln (Teil 2)
 *
 * WICHTIGE FIXES (02/2026):
 *  1) Follow-up: Pro Cron-Lauf wird pro Empfänger+Liste NUR das nächste Follow-up
 *     eingereiht (verhindert –alle FU auf einmal–).
 *  2) Kampagnenstatus: "processing" wird NUR gesetzt, wenn die Kampagne in der DB
 *     wirklich Typ "date" ist (zusätzliche SQL-Absicherung).
 *  3) Typen werden konsequent normalisiert (LOWER/TRIM), um Whitespace/Case-Bugs
 *     auszuschließen.
 * -----------------------------------------------------------------------------
 */

declare(strict_types=1);
require_once __DIR__ . '/../includes/init.php';

$db     = Database::getInstance();
$setupStatus = getSystemSetupStatus($db);
$sendProtection = getSendProtectionStatus($db, $setupStatus);
if (empty($sendProtection['allowed'])) {
    ErrorLogger::logSqlError('send_queue_cron: automatic send blocked | reasons: ' . json_encode($sendProtection['reasons'], JSON_UNESCAPED_UNICODE));
    exit;
}
$limit  = 25000;                                 // Sicherheits-Limit pro Cron-Lauf
$tz     = new DateTimeZone('Europe/Berlin');
$today  = new DateTimeImmutable('today', $tz);

/**
 * FOLLOW-UP RUN-LOCK
 * -----------------------------------------------------------------------------
 * Ziel: in EINEM Cron-Durchlauf pro Empfänger+Liste maximal EIN Follow-up einreihen.
 * Hintergrund: Der SELECT lädt sonst mehrere überfällige Follow-ups in $campaigns
 * und das Update last_follow_up_at wirkt nicht mehr auf das bereits geladene Array.
 *
 * Key-Format: "<recipient_id>:<list_id>"
 */
$followupLock = [];

$queueExistsStmt = $db->prepare("SELECT 1 FROM send_queue WHERE campaign_id = ? AND recipient_id = ? LIMIT 1");
$insertQueueStmt = $db->prepare("
    INSERT INTO send_queue (
        campaign_id, recipient_id, template_id, view_token,
        scheduled_at, status, created_at, priority
    ) VALUES (?, ?, ?, ?, NOW(), 'pending', NOW(), ?)
");
$updateFollowupStmt = $db->prepare("
    UPDATE recipient_lists
       SET last_follow_up_at = DATE_ADD(subscribed_at, INTERVAL ? DAY)
     WHERE recipient_id = ?
       AND list_id      = ?
");
$updateDateCampaignStmt = $db->prepare("
    UPDATE campaigns
       SET status = 'processing'
     WHERE id = ?
       AND LOWER(TRIM(type)) = 'date'
");
$insertTransactionQueueStmt = $db->prepare("
    INSERT IGNORE INTO send_queue (
        campaign_id, recipient_id, template_id, view_token,
        scheduled_at, status, created_at, priority
    ) VALUES (?, ?, ?, ?, NOW(), 'pending', NOW(), ?)
");



/* =============================================================================
 * 1) Scheduled- & Follow-up-Kampagnen **nur für aktive Empfänger** selektieren
 * -----------------------------------------------------------------------------
 *  – Join auf recipients zur Statusprüfung.
 *  – Typ wird im SELECT normalisiert (LOWER/TRIM) => PHP vergleicht stabil.
 * ========================================================================== */
try {
    $sqlCampaigns = "
        SELECT 
            c.id                    AS campaign_id,
            c.template_id,          -- E-Mail-Vorlage
            c.filter_type,          -- aktuell nur 'list' implementiert
            c.filter_value,         -- ID der Liste
            c.schedule_at,          -- Sendezeitpunkt für 'date'-Kampagnen
            c.followup_days,        -- Offset in Tagen für Follow-Up
            c.priority,
            LOWER(TRIM(c.type))     AS type,        -- – normalisiert: 'date'|'followup'
            rl.recipient_id,
            rl.list_id              AS list_id,
            rl.subscribed_at,
            rl.last_follow_up_at
        FROM campaigns       c
        JOIN recipient_lists rl 
          ON (c.filter_type = 'list' AND rl.list_id = c.filter_value)
        JOIN recipients      r  
          ON (r.id = rl.recipient_id AND r.status = 'active')  -- nur aktive
        WHERE c.status = 'scheduled'
          AND (
                /* -----------------------------------------------
                 * 1) Date-Kampagne – einfacher Zeitstempel-Check
                 * --------------------------------------------- */
                (LOWER(TRIM(c.type)) = 'date' 
                 AND c.schedule_at <= NOW())

                /* ------------------------------------------------------------
                 * 2) Follow-Up-Kampagne – Abo-Datum + Offset ist heute (oder älter)
                 *    UND Empfänger hat dieses Follow-Up noch nicht erhalten
                 * ---------------------------------------------------------- */
             OR (LOWER(TRIM(c.type)) = 'followup' 
                 AND DATE_ADD(rl.subscribed_at, INTERVAL c.followup_days DAY) <= NOW()
                 AND (rl.last_follow_up_at IS NULL 
                      OR rl.last_follow_up_at < DATE_ADD(rl.subscribed_at, INTERVAL c.followup_days DAY))
             )
          )
        LIMIT :limit
    ";

    $stmt = $db->prepare($sqlCampaigns);
    $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
    $stmt->execute();
    $campaigns = $stmt->fetchAll(PDO::FETCH_ASSOC);

    ErrorLogger::logSqlSuccess('send_queue_cron: load active campaigns/recipients | context: ' . json_encode(['count' => count($campaigns), 'limit' => $limit], JSON_UNESCAPED_UNICODE));

} catch (Throwable $e) {
    ErrorLogger::logSqlError('send_queue_cron: load active campaigns/recipients failed | error: ' . $e->getMessage());
    exit;
}


/* =============================================================================
 * 2) Kampagnen iterieren & Empfänger in send_queue eintragen
 * -----------------------------------------------------------------------------
 *  – Dedupe: Empfänger bereits in Queue -> skip
 *  – Fix: FollowupLock -> pro Empfänger+Liste nur EIN Follow-up pro Cron-Lauf
 *  – Atomar: INSERT send_queue + Update last_follow_up_at
 *  – Date-Kampagne: zusätzlich Kampagnenstatus auf processing (DB-gesichert)
 * ========================================================================== */
foreach ($campaigns as $c) {

    /* ---------------------------------------------------------------------
     * Typ stabil normalisieren (zusätzliche Sicherheit, obwohl SQL schon normiert)
     * ------------------------------------------------------------------- */
    $type = strtolower(trim((string)($c['type'] ?? '')));

    /* ---------------------------------------------------------------------
     * FIX 1: Follow-up nur „nächster Schritt“ je Empfänger+Liste pro Cron-Lauf
     * ------------------------------------------------------------------- */
    if ($type === 'followup') {
        $lockKey = (string)$c['recipient_id'] . ':' . (string)$c['list_id'];
        if (!empty($followupLock[$lockKey])) {
            // Schon ein FU für diesen Empfänger+Liste in diesem Run geplant -> skip
            ErrorLogger::log(
                "Follow-up Lock aktiv – skip recipient={$c['recipient_id']} list={$c['list_id']} campaign={$c['campaign_id']}",
                'INFO'
            );
            continue;
        }
        $followupLock[$lockKey] = true;
    }

    /* ---------------------------------------------------------------------
     * a) Bereits in Queue? – überspringen
     * ------------------------------------------------------------------- */
    try {
        $queueExistsStmt->execute([$c['campaign_id'], $c['recipient_id']]);
        if ($queueExistsStmt->fetchColumn() !== false) {
            ErrorLogger::log(
                "Empfänger {$c['recipient_id']} bereits in Queue für Kampagne {$c['campaign_id']}",
                'INFO'
            );
            continue;
        }
        ErrorLogger::logSqlSuccess('send_queue_cron: queue dedupe checked | context: ' . json_encode(['campaign_id' => $c['campaign_id'], 'recipient_id' => $c['recipient_id']], JSON_UNESCAPED_UNICODE));
    } catch (Throwable $e) {
        ErrorLogger::logSqlError('send_queue_cron: queue dedupe failed | context: ' . json_encode(['campaign_id' => $c['campaign_id']], JSON_UNESCAPED_UNICODE) . ' | error: ' . $e->getMessage());
        continue;
    }

    /* ---------------------------------------------------------------------
     * b) Atomar: INSERT send_queue (+ Update last_follow_up_at / campaign status)
     * ------------------------------------------------------------------- */
    $db->beginTransaction();
    try {
        // Einmaligen Tracking-Token generieren
        $viewToken = bin2hex(random_bytes(16));

        /* --------------------------------------------------------------
         * 1) Empfang in Warteschlange eintragen
         * ------------------------------------------------------------ */
        $insertQueueStmt->execute([
            $c['campaign_id'],
            $c['recipient_id'],
            $c['template_id'],
            $viewToken,
            $c['priority'] ?? 3
        ]);
        ErrorLogger::logSqlSuccess('send_queue_cron: insert send_queue row | context: ' . json_encode(['campaign_id' => $c['campaign_id'], 'recipient_id' => $c['recipient_id']], JSON_UNESCAPED_UNICODE));

        /* --------------------------------------------------------------
         * 2) Follow-Up-Marker setzen (nur für Typ followup)
         * ------------------------------------------------------------ */
        if ($type === 'followup') {
            $updateFollowupStmt->execute([
                (int)$c['followup_days'],
                (int)$c['recipient_id'],
                (int)$c['list_id']
            ]);
            ErrorLogger::logSqlSuccess('send_queue_cron: update recipient_lists follow-up marker | context: ' . json_encode(['recipient_id' => $c['recipient_id'], 'list_id' => $c['list_id'], 'followup_days' => $c['followup_days']], JSON_UNESCAPED_UNICODE));
        }

        /* --------------------------------------------------------------
         * 3) Date-Kampagne nach Befüllung auf processing
         * ------------------------------------------------------------ */
        if ($type === 'date') {
            $updateDateCampaignStmt->execute([(int)$c['campaign_id']]);

            ErrorLogger::logSqlSuccess('send_queue_cron: set campaign status processing (date only) | context: ' . json_encode(['campaign_id' => $c['campaign_id'], 'rowcount' => $updateDateCampaignStmt->rowCount()], JSON_UNESCAPED_UNICODE));
        }

        $db->commit();

    } catch (Throwable $e) {
        $db->rollBack();
        ErrorLogger::logSqlError('send_queue_cron: campaign-recipient processing failed | context: ' . json_encode(['campaign_id' => $c['campaign_id'], 'recipient_id' => $c['recipient_id']], JSON_UNESCAPED_UNICODE) . ' | error: ' . $e->getMessage());
        continue;
    }
}

/* =============================================================================
 * 3) Abschluss-Log
 * ========================================================================== */
ErrorLogger::log("send_queue_cron.php abgeschlossen – alle passenden Empfänger geprüft.", 'INFO');



/* =============================================================================
 * 4) Transaktions-Regeln (robustes Datums-Parsing)
 * -----------------------------------------------------------------------------
 * Unverändert (außer Logging leicht konsistent gehalten).
 * ========================================================================== */
try {
    $rules = $db->query("
            SELECT tr.*, c.template_id, c.priority
              FROM transaction_rules tr
              JOIN campaigns c ON c.id = tr.campaign_id
             WHERE c.status IN ('active','processing','scheduled')
        ")->fetchAll(PDO::FETCH_ASSOC);

    ErrorLogger::logSqlSuccess('Transaktions Mail suchen | context: ' . json_encode(['count' => count($rules)], JSON_UNESCAPED_UNICODE));
} catch (Throwable $e) {
    ErrorLogger::logSqlError('Transaktions Mail suchen Fehler | error: ' . $e->getMessage());
    $rules = [];
}
$transactionRecipientStmtCache = [];

foreach ($rules as $r) {
    try {
        // ---------------------------------------------------- Ziel-Datum + Operator
        $target   = $today->modify(($r['date_offset_days'] ?? 0) . ' days');
        $targetMD = $target->format('d-m');
        $op = match ($r['date_compare_mode']) {
            'before' => '<',
            'after'  => '>',
            default  => '=',
        };

        // ---------------------------------------------------- Selektions-SQL
        $sql = "SELECT rec.id
                   FROM recipients rec
                   JOIN recipient_custom_fields cf ON cf.recipient_id = rec.id
                  WHERE cf.field_key = :fkey
                    AND DATE_FORMAT(
                          STR_TO_DATE(REPLACE(TRIM(cf.field_value),'.','-'), '%d-%m-%Y'),
                          '%d-%m') $op :md";

        $params = [':fkey' => $r['field_key'], ':md' => $targetMD];

        // optionaler Feldwert-Vergleich (nur wenn sinnvoller Inhalt gesetzt)
        if (!empty($r['field_value']) && $r['field_value'] !== 'NULL') {
            $sql .= " AND cf.field_value = :fval";
            $params[':fval'] = $r['field_value'];
        }

        // optionaler Tag-Filter
        if (!empty($r['required_tag_id'])) {
            $sql .= " AND EXISTS (SELECT 1 FROM recipient_tags rt
                                   WHERE rt.recipient_id = rec.id
                                     AND rt.tag_id = :tag)";
            $params[':tag'] = $r['required_tag_id'];
        }
        if (!isset($transactionRecipientStmtCache[$sql])) {
            $transactionRecipientStmtCache[$sql] = $db->prepare($sql);
        }

        $st = $transactionRecipientStmtCache[$sql];
        $st->execute($params);

        $recipientCount = 0;
        while (($rid = $st->fetchColumn()) !== false) {
            $insertTransactionQueueStmt->execute([
                $r['campaign_id'],
                $rid,
                $r['template_id'],
                bin2hex(random_bytes(16)),
                $r['priority'] ?? 5
            ]);
            $recipientCount++;
            ErrorLogger::logSqlSuccess('send_queue_cron: transaction queue insert | context: ' . json_encode(['campaign_id' => $r['campaign_id'], 'recipient_id' => $rid], JSON_UNESCAPED_UNICODE));
        }

        ErrorLogger::logSqlSuccess('Transaktions Mail geladen | context: ' . json_encode(['count' => $recipientCount], JSON_UNESCAPED_UNICODE));

    } catch (Throwable $e) {
        ErrorLogger::logSqlError('RULE_PROC | error: ' . $e->getMessage());
    }
}



