breadcrumb-the-shire/modules/notifications/lib/Module/Notifications/Repository/NotificationRepository.php

<?php

namespace MintyPHP\Module\Notifications\Repository;

use MintyPHP\DB;
use MintyPHP\Repository\Support\RepositoryArrayHelper;

class NotificationRepository
{
    private const DEDUPE_WINDOW_SECONDS = 1800;

    private function unwrapList(mixed $rows): array
    {
        return RepositoryArrayHelper::unwrapList($rows, 'user_notifications');
    }

    public function create(array $data): int|false
    {
        $dedupeFingerprint = trim((string) ($data['dedupe_fingerprint'] ?? ''));
        $dedupeBucket = (int) ($data['dedupe_bucket'] ?? 0);
        $hasDedupe = $dedupeFingerprint !== '' && $dedupeBucket > 0;

        $result = DB::insert(
            ($hasDedupe ? 'insert ignore' : 'insert') .
            ' into user_notifications (recipient_user_id, tenant_id, type, title, body, link, data, dedupe_fingerprint, dedupe_bucket, dedupe_until, created) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())',
            (string) ($data['recipient_user_id'] ?? 0),
            $data['tenant_id'] !== null ? (string) $data['tenant_id'] : null,
            (string) ($data['type'] ?? ''),
            (string) ($data['title'] ?? ''),
            $data['body'] ?? null,
            $data['link'] ?? null,
            isset($data['data']) ? json_encode($data['data'], JSON_THROW_ON_ERROR) : null,
            $hasDedupe ? $dedupeFingerprint : null,
            $hasDedupe ? (string) $dedupeBucket : null,
            $hasDedupe ? gmdate('Y-m-d H:i:s', time() + self::DEDUPE_WINDOW_SECONDS) : null
        );

        return is_int($result) && $result > 0 ? $result : false;
    }

    public function listByUser(int $userId, ?int $tenantId, int $limit, int $offset): array
    {
        if ($userId <= 0) {
            return [];
        }

        $limit = max(1, min($limit, 100));
        $offset = max(0, $offset);

        $tenantParams = [];
        $rows = DB::select(
            'select id, type, title, body, link, data, is_read, created from user_notifications where recipient_user_id = ?' .
            $this->tenantScopeClause($tenantId, true, $tenantParams) .
            ' order by created desc limit ' . $limit . ' offset ' . $offset,
            (string) $userId,
            ...$tenantParams
        );

        return $this->unwrapList($rows);
    }

    public function countUnreadByUser(int $userId, ?int $tenantId): int
    {
        if ($userId <= 0) {
            return 0;
        }

        $tenantParams = [];
        $count = DB::selectValue(
            'select count(*) from user_notifications where recipient_user_id = ? and is_read = 0' .
            $this->tenantScopeClause($tenantId, true, $tenantParams),
            (string) $userId,
            ...$tenantParams
        );

        return (int) $count;
    }

    public function markRead(int $id, int $userId, ?int $tenantId): bool
    {
        if ($id <= 0 || $userId <= 0) {
            return false;
        }

        $tenantParams = [];
        $affected = DB::update(
            'update user_notifications set is_read = 1, read_at = NOW() where id = ? and recipient_user_id = ? and is_read = 0' .
            $this->tenantScopeClause($tenantId, true, $tenantParams),
            (string) $id,
            (string) $userId,
            ...$tenantParams
        );

        return is_int($affected) && $affected > 0;
    }

    public function markAllReadByUser(int $userId, ?int $tenantId): int
    {
        if ($userId <= 0) {
            return 0;
        }

        $tenantParams = [];
        $affected = DB::update(
            'update user_notifications set is_read = 1, read_at = NOW() where recipient_user_id = ? and is_read = 0' .
            $this->tenantScopeClause($tenantId, true, $tenantParams),
            (string) $userId,
            ...$tenantParams
        );

        return is_int($affected) ? $affected : 0;
    }

    public function delete(int $id, int $userId, ?int $tenantId): bool
    {
        if ($id <= 0 || $userId <= 0) {
            return false;
        }

        $tenantParams = [];
        $affected = DB::delete(
            'delete from user_notifications where id = ? and recipient_user_id = ?' .
            $this->tenantScopeClause($tenantId, true, $tenantParams),
            (string) $id,
            (string) $userId,
            ...$tenantParams
        );

        return is_int($affected) && $affected > 0;
    }

    public function deleteAllByUser(int $userId): int
    {
        if ($userId <= 0) {
            return 0;
        }

        $affected = DB::delete(
            'delete from user_notifications where recipient_user_id = ?',
            (string) $userId
        );

        return is_int($affected) ? $affected : 0;
    }

    public function purgeReadOlderThanDays(int $days): int
    {
        if ($days <= 0) {
            return 0;
        }

        $affected = DB::delete(
            'delete from user_notifications where is_read = 1 and read_at < DATE_SUB(NOW(), INTERVAL ? DAY)',
            (string) $days
        );

        return is_int($affected) ? $affected : 0;
    }

    /**
     * Builds a tenant scope SQL condition for a notification recipient query.
     * Scope always includes global notifications (`tenant_id is null`).
     *
     * @param array<int, string> $params
     */
    private function tenantScopeClause(?int $tenantId, bool $includeGlobal, array &$params): string
    {
        $scopedTenantId = (int) ($tenantId ?? 0);
        if ($scopedTenantId > 0) {
            if ($includeGlobal) {
                $params[] = (string) $scopedTenantId;
                return ' and (tenant_id is null or tenant_id = ?)';
            }
            $params[] = (string) $scopedTenantId;
            return ' and tenant_id = ?';
        }

        return $includeGlobal ? ' and tenant_id is null' : '';
    }
}