bot-28/app/Services/SiteInboxService.php

<?php

namespace App\Services;

use App\Support\LocalizedContent;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Validator;
use Illuminate\Validation\Rule;

class SiteInboxService
{
    public function listing(string $kind, int $id, array $input): array
    {
        $params = Validator::make($input, [
            'page' => ['nullable', 'integer', 'min:1'], 'limit' => ['nullable', 'integer', 'min:1', 'max:100'],
            'category' => ['nullable', Rule::in(array_keys(SiteMessageService::CATEGORIES))], 'is_read' => ['nullable', 'integer', 'in:0,1'],
        ])->validate();
        $query = $this->query($kind, $id);
        if (!empty($params['category'])) $query->where('m.category', $params['category']);
        if (isset($params['is_read'])) { if ((int) $params['is_read'] === 1) $query->whereNotNull('r.read_at'); else $query->whereNull('r.read_at'); }
        $count = (clone $query)->count();
        $rows = $query->select('m.id', 'm.category', 'm.sent_at', 't.title', 't.subtitle', 'r.language', 'r.read_at')->orderByDesc('m.sent_at')->orderByDesc('m.id')
            ->forPage((int) ($params['page'] ?? 1), (int) ($params['limit'] ?? 20))->get();
        return ['count' => $count, 'list' => $rows->map(fn ($row) => $this->dto((array) $row))->all()];
    }

    public function detail(string $kind, int $recipient, int $id): array
    {
        $row = $this->query($kind, $recipient)->where('m.id', $id)->select('m.id', 'm.category', 'm.sent_at', 't.title', 't.subtitle', 't.content', 'r.language', 'r.read_at')->first();
        if (!$row) LocalizedContent::invalid('id', '信息不存在');
        return $this->dto((array) $row);
    }

    public function unread(string $kind, int $id): array { return ['count' => $this->query($kind, $id)->whereNull('r.read_at')->count()]; }

    public function read(string $kind, int $recipient, int $id): array
    {
        return DB::transaction(function () use ($kind, $recipient, $id) {
            $message = DB::table('site_messages')->where('id', $id)->where('status', 'sent')->lockForUpdate()->first();
            if (!$message) LocalizedContent::invalid('id', '信息不存在');
            $receipt = DB::table('site_message_recipients')->where('message_id', $id)->where('recipient_kind', $kind)->where('recipient_id', $recipient)->lockForUpdate()->first();
            if (!$receipt) LocalizedContent::invalid('id', '信息不存在');
            $readAt = $receipt->read_at ?? Carbon::now('Asia/Shanghai')->format('Y-m-d H:i:s');
            if ($receipt->read_at === null) DB::table('site_message_recipients')->where('id', $receipt->id)->update(['read_at' => $readAt]);
            return ['id' => $id, 'is_read' => true, 'read_at' => $readAt];
        });
    }

    private function query(string $kind, int $id)
    {
        if (!in_array($kind, ['user', 'agent'], true) || $id < 1) LocalizedContent::invalid('recipient', '请先登录');
        return DB::table('site_message_recipients as r')->join('site_messages as m', 'm.id', '=', 'r.message_id')
            ->join('site_message_translations as t', function ($j) { $j->on('t.message_id', '=', 'r.message_id')->on('t.language', '=', 'r.language'); })
            ->where('r.recipient_kind', $kind)->where('r.recipient_id', $id)->where('m.status', 'sent');
    }
    private function dto(array $row): array { $row['id'] = (int) $row['id']; $row['category_name'] = SiteMessageService::CATEGORIES[$row['category']]; $row['is_read'] = $row['read_at'] !== null; return $row; }
}