<?php
declare(strict_types=1);

/**
 * cron/maintenance_cleanup.php
 * -----------------------------------------------------------------------------
 * MR9 – Zentraler Pflege-Cron (Datenhygiene & Datenschutz)
 *
 * Aufgaben:
 *  1) IP-Anonymisierung (email_opens / email_link_tracking) nach 90 Tagen
 *  2) suspicious_ips löschen nach window_end + 7 Tage
 *  3) smtp_domain_sendlog älter als 24h löschen (Kurzzeit-Log)
 *  4) Orphan-Daten entfernen (opens/clicks/bounces ohne send_queue)
 *  5) Alte send_queue Einträge (sent/error) löschen
 *  6) Alte Bounce-Logs löschen
 *
 * Architektur:
 *  • Batch-Verarbeitung
 *  • MAX_BATCHES Schutz gegen Endlosschleifen
 *  • Jede Operation einzeln abgesichert (fail-safe)
 *  • Vollständiges SQL-Logging
 */

require_once __DIR__ . '/../includes/init.php';

$db = Database::getInstance();

/* =========================================================================
 * KONFIGURATION
 * ========================================================================= */

const BATCH_SIZE = 5000;
const MAX_BATCHES = 200;

const ANONYMIZE_IP_AFTER_DAYS      = 90;
const DELETE_SUSPICIOUS_AFTER_DAYS = 7;
const SMTP_SENDLOG_HOURS           = 24;

const PURGE_QUEUE_AFTER_DAYS   = 365;
const PURGE_BOUNCES_AFTER_DAYS = 365;

const CLEAN_ORPHANS   = true;
const PURGE_OLD_QUEUE = true;
const PURGE_BOUNCES   = true;

/* =========================================================================
 * HELPER LOGGING
 * ========================================================================= */
function logOk(string $action, string $sql, array $ctx = []): void
{
    ErrorLogger::logSqlSuccess('maintenance_cleanup | file: ' . __FILE__ . ' | action: ' . $action . ' | context: ' . json_encode((['sql' => $sql] + $ctx), JSON_UNESCAPED_UNICODE));
}

function logErr(string $action, Throwable $e, array $ctx = []): void
{
    ErrorLogger::logSqlError('maintenance_cleanup | file: ' . __FILE__ . ' | action: ' . $action . ' | error: ' . $e->getMessage() . ' | context: ' . json_encode($ctx, JSON_UNESCAPED_UNICODE));
}

/* =========================================================================
 * 1) IP ANONYMISIERUNG
 * ========================================================================= */
function anonymizeIps(PDO $db): void
{
    $tables = [
        ['name' => 'email_opens',         'date_col' => 'opened_at'],
        ['name' => 'email_link_tracking', 'date_col' => 'clicked_at'],
    ];

    foreach ($tables as $t) {

        $batches = 0;

        while (true) {

            if (++$batches > MAX_BATCHES) break;

            try {
                $sql = "
                    UPDATE {$t['name']}
                       SET ip_address =
                           CASE
                               WHEN ip_address LIKE '%.%' 
                                   THEN CONCAT(SUBSTRING_INDEX(ip_address, '.', 3), '.0')
                               WHEN ip_address LIKE '%:%'
                                   THEN CONCAT(SUBSTRING_INDEX(ip_address, ':', 4), ':0000:0000:0000:0000')
                               ELSE ip_address
                           END
                     WHERE ip_address IS NOT NULL
                       AND ip_address <> ''
                       AND {$t['date_col']} IS NOT NULL
                       AND {$t['date_col']} < (NOW() - INTERVAL ? DAY)
                     LIMIT " . BATCH_SIZE;

                $stmt = $db->prepare($sql);
                $stmt->execute([ANONYMIZE_IP_AFTER_DAYS]);

                $affected = $stmt->rowCount();

                logOk("IP-Anonymisierung {$t['name']}", $sql, [
                    'days' => ANONYMIZE_IP_AFTER_DAYS,
                    'affected' => $affected
                ]);

                if ($affected == 0) break;

            } catch (Throwable $e) {
                logErr("IP-Anonymisierung {$t['name']}", $e);
                break;
            }
        }
    }
}

/* =========================================================================
 * 2) suspicious_ips CLEANUP
 * ========================================================================= */
function purgeSuspiciousIps(PDO $db): void
{
    $batches = 0;

    while (true) {

        if (++$batches > MAX_BATCHES) break;

        try {
            $sql = "
                DELETE FROM suspicious_ips
                 WHERE window_end < (NOW() - INTERVAL ? DAY)
                 LIMIT " . BATCH_SIZE;

            $stmt = $db->prepare($sql);
            $stmt->execute([DELETE_SUSPICIOUS_AFTER_DAYS]);

            $affected = $stmt->rowCount();

            logOk("suspicious_ips Cleanup", $sql, [
                'days' => DELETE_SUSPICIOUS_AFTER_DAYS,
                'deleted' => $affected
            ]);

            if ($affected == 0) break;

        } catch (Throwable $e) {
            logErr("suspicious_ips Cleanup", $e);
            break;
        }
    }
}

/* =========================================================================
 * 3) smtp_domain_sendlog CLEANUP (24h)
 * ========================================================================= */
function purgeSmtpDomainSendlog(PDO $db): void
{
    $batches = 0;

    while (true) {

        if (++$batches > MAX_BATCHES) break;

        try {
            $sql = "
                DELETE FROM smtp_domain_sendlog
                 WHERE sent_at < (NOW() - INTERVAL ? HOUR)
                 LIMIT " . BATCH_SIZE;

            $stmt = $db->prepare($sql);
            $stmt->execute([SMTP_SENDLOG_HOURS]);

            $affected = $stmt->rowCount();

            logOk("smtp_domain_sendlog Cleanup", $sql, [
                'hours' => SMTP_SENDLOG_HOURS,
                'deleted' => $affected
            ]);

            if ($affected == 0) break;

        } catch (Throwable $e) {
            logErr("smtp_domain_sendlog Cleanup", $e);
            break;
        }
    }
}

/* =========================================================================
 * 4) ORPHAN CLEANUP
 * ========================================================================= */
function cleanupOrphans(PDO $db): void
{
    if (!CLEAN_ORPHANS) return;

    $queries = [

        "DELETE eo FROM email_opens eo
         LEFT JOIN send_queue sq ON sq.id = eo.queue_id
         WHERE sq.id IS NULL
         LIMIT " . BATCH_SIZE,

        "DELETE elt FROM email_link_tracking elt
         LEFT JOIN send_queue sq ON sq.id = elt.queue_id
         WHERE sq.id IS NULL
         LIMIT " . BATCH_SIZE,

        "DELETE eb FROM email_bounces eb
         LEFT JOIN send_queue sq ON sq.id = eb.queue_id
         WHERE sq.id IS NULL
         LIMIT " . BATCH_SIZE,
    ];

    foreach ($queries as $sql) {

        $stmt = $db->prepare($sql);
        $batches = 0;

        while (true) {

            if (++$batches > MAX_BATCHES) break;

            try {
                $stmt->execute();

                $affected = $stmt->rowCount();

                logOk("Orphan Cleanup", $sql, ['deleted' => $affected]);

                if ($affected == 0) break;

            } catch (Throwable $e) {
                logErr("Orphan Cleanup", $e);
                break;
            }
        }
    }
}

/* =========================================================================
 * 5) QUEUE PURGE
 * ========================================================================= */
function purgeOldQueue(PDO $db): void
{
    if (!PURGE_OLD_QUEUE) return;

    try {
        $sql = "
            DELETE FROM send_queue
             WHERE status IN ('sent','error')
               AND COALESCE(sent_at, created_at)
                   < (NOW() - INTERVAL ? DAY)
             LIMIT " . BATCH_SIZE;

        $stmt = $db->prepare($sql);
        $stmt->execute([PURGE_QUEUE_AFTER_DAYS]);

        logOk("send_queue Purge", $sql, [
            'days' => PURGE_QUEUE_AFTER_DAYS,
            'deleted' => $stmt->rowCount()
        ]);

    } catch (Throwable $e) {
        logErr("send_queue Purge", $e);
    }
}

/* =========================================================================
 * 6) BOUNCE PURGE
 * ========================================================================= */
function purgeOldBounces(PDO $db): void
{
    if (!PURGE_BOUNCES) return;

    try {
        $sql = "
            DELETE FROM email_bounces
             WHERE logged_at < (NOW() - INTERVAL ? DAY)
             LIMIT " . BATCH_SIZE;

        $stmt = $db->prepare($sql);
        $stmt->execute([PURGE_BOUNCES_AFTER_DAYS]);

        logOk("email_bounces Purge", $sql, [
            'days' => PURGE_BOUNCES_AFTER_DAYS,
            'deleted' => $stmt->rowCount()
        ]);

    } catch (Throwable $e) {
        logErr("email_bounces Purge", $e);
    }
}

/* =========================================================================
 * MAIN EXECUTION
 * ========================================================================= */
try {

    logOk("Maintenance Cron START", 'n/a', ['time' => date('Y-m-d H:i:s')]);

    anonymizeIps($db);
    purgeSuspiciousIps($db);
    purgeSmtpDomainSendlog($db);
    cleanupOrphans($db);
    purgeOldQueue($db);
    purgeOldBounces($db);

    logOk("Maintenance Cron DONE", 'n/a', ['time' => date('Y-m-d H:i:s')]);

} catch (Throwable $e) {
    logErr("Maintenance Cron GLOBAL ERROR", $e);
}

