praxis-creutzburg-web/inc/impfworkflow_notifications.inc.php

<?php

if (!function_exists('impfWorkflowNotificationEnsureMetaTable')) {
    function impfWorkflowNotificationEnsureMetaTable(PDO $pdo): void
    {
        $pdo->exec("CREATE TABLE IF NOT EXISTS impf_workflow_meta (
            meta_key VARCHAR(100) NOT NULL,
            meta_value VARCHAR(255) NOT NULL DEFAULT '',
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (meta_key)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    }
}

if (!function_exists('impfWorkflowNotificationGetMeta')) {
    function impfWorkflowNotificationGetMeta(PDO $pdo, string $key): ?string
    {
        impfWorkflowNotificationEnsureMetaTable($pdo);

        $st = $pdo->prepare("SELECT meta_value
                             FROM impf_workflow_meta
                             WHERE meta_key = :meta_key
                             LIMIT 1");
        $st->execute(['meta_key' => $key]);
        $value = $st->fetchColumn();

        return ($value === false) ? null : (string)$value;
    }
}

if (!function_exists('impfWorkflowNotificationSetMeta')) {
    function impfWorkflowNotificationSetMeta(PDO $pdo, string $key, string $value): void
    {
        impfWorkflowNotificationEnsureMetaTable($pdo);

        $st = $pdo->prepare("INSERT INTO impf_workflow_meta (meta_key, meta_value)
                             VALUES (:meta_key, :meta_value)
                             ON DUPLICATE KEY UPDATE meta_value = VALUES(meta_value)");
        $st->execute([
            'meta_key' => $key,
            'meta_value' => $value,
        ]);
    }
}

if (!function_exists('impfWorkflowNotificationGetEmail')) {
    function impfWorkflowNotificationGetEmail(PDO $pdo): string
    {
        return trim((string)(impfWorkflowNotificationGetMeta($pdo, 'benachrichtigung_email') ?? ''));
    }
}

if (!function_exists('impfWorkflowNotificationSetEmail')) {
    function impfWorkflowNotificationSetEmail(PDO $pdo, string $email): void
    {
        $email = trim($email);
        impfWorkflowNotificationSetMeta($pdo, 'benachrichtigung_email', $email);
    }
}

if (!function_exists('impfWorkflowNotificationIsReady')) {
    function impfWorkflowNotificationIsReady(PDO $pdo): bool
    {
        return impfWorkflowNotificationGetEmail($pdo) !== '';
    }
}

if (!function_exists('impfWorkflowNotificationShouldTrigger')) {
    function impfWorkflowNotificationShouldTrigger(int $wartende, int $dosen): bool
    {
        if ($wartende < 5) {
            return false;
        }

        if ($dosen > 5) {
            return $wartende >= $dosen;
        }

        return true;
    }
}

if (!function_exists('impfWorkflowNotificationStateKey')) {
    function impfWorkflowNotificationStateKey(int $impfstoffId, int $zeitraumId): string
    {
        return 'notification_sent_' . $impfstoffId . '_' . $zeitraumId;
    }
}

if (!function_exists('impfWorkflowNotificationCountWaitersForPlan')) {
    function impfWorkflowNotificationCountWaitersForPlan(PDO $pdo, int $impfstoffId, int $zeitraumId): int
    {
        if ($impfstoffId <= 0 || $zeitraumId <= 0) {
            return 0;
        }

        $st = $pdo->prepare("SELECT COUNT(DISTINCT w.userid)
                             FROM warteliste w
                             WHERE w.checked = 1
                               AND (w.impfstoff = :iid OR w.impfstoff = 0)
                               AND (
                                   EXISTS (
                                       SELECT 1
                                       FROM warteliste_zeitraum wz
                                       WHERE wz.warteid = w.warteid
                                         AND wz.zeitraum_id = :zid
                                   )
                                   OR (
                                       NOT EXISTS (
                                           SELECT 1
                                           FROM warteliste_zeitraum wz_none
                                           WHERE wz_none.warteid = w.warteid
                                       )
                                       AND (w.zeitraum_id = :zid OR w.zeitraum_id IS NULL)
                                   )
                               )");
        $st->execute([
            'iid' => $impfstoffId,
            'zid' => $zeitraumId,
        ]);

        return (int)$st->fetchColumn();
    }
}

if (!function_exists('impfWorkflowNotificationSendForPlan')) {
    function impfWorkflowNotificationSendForPlan(
        PDO $pdo,
        int $impfstoffId,
        string $impfstoffName,
        int $zeitraumId,
        string $zeitraumLabel,
        int $wartende,
        int $dosen
    ): array {
        if ($impfstoffId <= 0 || $zeitraumId <= 0) {
            return [];
        }

        $stateKey = impfWorkflowNotificationStateKey($impfstoffId, $zeitraumId);
        $alreadySent = impfWorkflowNotificationGetMeta($pdo, $stateKey) === '1';
        $shouldTrigger = impfWorkflowNotificationShouldTrigger($wartende, $dosen);

        if (!$shouldTrigger) {
            if ($alreadySent) {
                impfWorkflowNotificationSetMeta($pdo, $stateKey, '0');
            }
            return [];
        }

        if ($alreadySent) {
            return [];
        }

        $email = impfWorkflowNotificationGetEmail($pdo);
        if ($email === '') {
            return [];
        }

        $thresholdText = ($dosen > 5)
            ? 'Die Flasche hat mehr als 5 Dosen, daher wird erst bei einer vollen Flasche benachrichtigt.'
            : 'Es sind mindestens 5 Interessenten fuer dieses Zeitfenster vorhanden.';

        $subject = 'Impfworkflow: Warteliste ist bereit fuer ' . $impfstoffName;
        $body = '<p>Fuer den Impfworkflow ist ein Zeitfenster benachrichtigungsreif.</p>'
            . '<p><strong>Impfstoff:</strong> ' . htmlspecialchars($impfstoffName, ENT_QUOTES, 'UTF-8') . '<br>'
            . '<strong>Zeitfenster:</strong> ' . htmlspecialchars($zeitraumLabel, ENT_QUOTES, 'UTF-8') . '<br>'
            . '<strong>Interessenten:</strong> ' . $wartende . '<br>'
            . '<strong>Dosen pro Flasche:</strong> ' . $dosen . '</p>'
            . '<p>' . htmlspecialchars($thresholdText, ENT_QUOTES, 'UTF-8') . '</p>';

        if (!SendMailMessage($pdo, $email, $subject, $body)) {
            throw new RuntimeException('Benachrichtigungs-E-Mail konnte nicht versendet werden.');
        }

        impfWorkflowNotificationSetMeta($pdo, $stateKey, '1');

        return [[
            'impfstoff_id' => $impfstoffId,
            'zeitraum_id' => $zeitraumId,
            'email' => $email,
            'impfstoff' => $impfstoffName,
            'zeitraum' => $zeitraumLabel,
            'wartende' => $wartende,
            'dosen' => $dosen,
        ]];
    }
}

if (!function_exists('impfWorkflowNotificationProcess')) {
    function impfWorkflowNotificationProcess(PDO $pdo, int $impfstoffId = 0, array $zeitraumIds = []): array
    {
        if (!function_exists('impfGetZeitraeumeByImpfstoff') || !function_exists('impfLoadZeitraumById')) {
            return [];
        }

        $zeitraumIds = array_values(array_unique(array_filter(array_map('intval', $zeitraumIds), static function (int $zeitraumId): bool {
            return $zeitraumId > 0;
        })));

        $sql = "SELECT r.impfstoff_id, r.dosen_pro_flasche, i.impfname
                FROM impfstoff_workflow r
                INNER JOIN impfstoff i ON i.impfid = r.impfstoff_id
                WHERE (i.aktiv = 1 OR i.aktivwarteliste = 1 OR i.aktivtermin = 1 OR i.aktivgrippe = 1)";
        $params = [];
        if ($impfstoffId > 0) {
            $sql .= " AND r.impfstoff_id = :iid";
            $params['iid'] = $impfstoffId;
        }
        $sql .= " ORDER BY i.impfname";

        $stRules = $pdo->prepare($sql);
        $stRules->execute($params);
        $rules = $stRules->fetchAll(PDO::FETCH_ASSOC);
        if (empty($rules)) {
            return [];
        }

        $zeitraeumeByImpfstoff = impfGetZeitraeumeByImpfstoff($pdo, true);
        $sent = [];

        foreach ($rules as $rule) {
            $currentImpfstoffId = (int)$rule['impfstoff_id'];
            $dosen = (int)$rule['dosen_pro_flasche'];
            if ($currentImpfstoffId <= 0 || $dosen <= 0 || empty($zeitraeumeByImpfstoff[$currentImpfstoffId])) {
                continue;
            }

            foreach ($zeitraeumeByImpfstoff[$currentImpfstoffId] as $zeitraum) {
                $currentZeitraumId = (int)($zeitraum['zeitraum_id'] ?? 0);
                if ($currentZeitraumId <= 0) {
                    continue;
                }
                if (!empty($zeitraumIds) && !in_array($currentZeitraumId, $zeitraumIds, true)) {
                    continue;
                }

                $wartende = impfWorkflowNotificationCountWaitersForPlan($pdo, $currentImpfstoffId, $currentZeitraumId);
                $sent = array_merge(
                    $sent,
                    impfWorkflowNotificationSendForPlan(
                        $pdo,
                        $currentImpfstoffId,
                        (string)$rule['impfname'],
                        $currentZeitraumId,
                        (string)($zeitraum['label'] ?? ''),
                        $wartende,
                        $dosen
                    )
                );
            }
        }

        return $sent;
    }
}