szkp-map-service/app/Http/Controllers/Api/DashboardController.php

<?php

namespace App\Http\Controllers\Api;

use App\Http\Controllers\Controller;
use App\Models\Blacklist;
use App\Models\Venue;
use App\Services\RealtimeCrowdService;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Illuminate\Http\JsonResponse;
use Illuminate\Http\Request;

class DashboardController extends Controller
{
    public function __construct(private readonly RealtimeCrowdService $realtimeCrowdService)
    {
    }

    public function stats(Request $request): JsonResponse
    {
        $user = $request->user();
        $allowedVenueIds = $user->isSuperAdmin()
            ? Venue::query()->pluck('id')
            : $user->venues()->pluck('venues.id');
        $ownVenueIds = $user->venues()->pluck('venues.id')->map(fn ($id) => (int) $id)->values();

        $selectedVenueId = $request->filled('venue_id') ? (int) $request->input('venue_id') : null;
        $venueIds = $allowedVenueIds;
        if ($selectedVenueId) {
            $venueIds = $venueIds->filter(fn ($id) => (int) $id === $selectedVenueId)->values();
        }

        $startDate = $request->input('start_date') ?: now()->subDays(29)->toDateString();
        $endDate = $request->input('end_date') ?: now()->toDateString();

        $base = DB::table('reservations')
            ->leftJoin('activity_days', 'activity_days.id', '=', 'reservations.activity_day_id')
            ->whereIn('reservations.venue_id', $venueIds)
            ->whereRaw("COALESCE(activity_days.activity_date, DATE(reservations.created_at)) >= ?", [$startDate])
            ->whereRaw("COALESCE(activity_days.activity_date, DATE(reservations.created_at)) <= ?", [$endDate]);

        $summary = (clone $base)
            ->selectRaw('COUNT(*) as total_count')
            ->selectRaw("SUM(CASE WHEN reservations.status = 'verified' THEN 1 ELSE 0 END) as verified_count")
            ->selectRaw("SUM(CASE WHEN reservations.status = 'cancelled' THEN 1 ELSE 0 END) as cancelled_count")
            ->selectRaw("SUM(CASE WHEN reservations.status = 'pending' THEN 1 ELSE 0 END) as pending_count")
            ->first();

        $totalCount = (int) ($summary->total_count ?? 0);
        $verifiedCount = (int) ($summary->verified_count ?? 0);
        $cancelledCount = (int) ($summary->cancelled_count ?? 0);
        $pendingCount = (int) ($summary->pending_count ?? 0);
        $effectiveCount = max(0, $totalCount - $cancelledCount);
        $verifyRate = $effectiveCount > 0 ? round($verifiedCount / $effectiveCount, 4) : 0;

        $blacklistedUniqueQuery = Blacklist::query()
            ->whereIn('venue_id', $venueIds)
            ->whereNotNull('visitor_phone')
            ->whereRaw("TRIM(visitor_phone) <> ''")
            ->whereExists(function ($q) use ($venueIds, $user) {
                $q->selectRaw('1')
                    ->from('reservations')
                    ->whereColumn('reservations.visitor_phone', 'blacklists.visitor_phone')
                    ->whereIn('reservations.venue_id', $venueIds);

                // 与用户管理(super_admin)口径对齐：仅统计可映射到 wechat_users 的用户手机号
                if ($user->isSuperAdmin() && Schema::hasTable('wechat_users')) {
                    $q->whereNotNull('reservations.wechat_user_id')
                        ->whereExists(function ($wq) {
                            $wq->selectRaw('1')
                                ->from('wechat_users')
                                ->whereColumn('wechat_users.id', 'reservations.wechat_user_id');
                        });
                }
            });
        $blacklistedUnique = $blacklistedUniqueQuery
            ->distinct('visitor_phone')
            ->count('visitor_phone');

        $activitySessions = DB::table('activities')
            ->whereIn('venue_id', $venueIds)
            ->whereNull('deleted_at')
            ->where(function ($q) use ($startDate, $endDate) {
                $q->where(function ($w) use ($startDate, $endDate) {
                    $w->whereNotNull('start_at')
                        ->whereNotNull('end_at')
                        ->whereDate('start_at', '<=', $endDate)
                        ->whereDate('end_at', '>=', $startDate);
                })->orWhere(function ($w) use ($startDate, $endDate) {
                    $w->whereNull('start_at')
                        ->whereNull('end_at')
                        ->whereDate('created_at', '>=', $startDate)
                        ->whereDate('created_at', '<=', $endDate);
                });
            })
            ->count();

        $activeVenueCount = (clone $base)
            ->distinct('reservations.venue_id')
            ->count('reservations.venue_id');

        $trends = (clone $base)
            ->selectRaw("COALESCE(activity_days.activity_date, DATE(reservations.created_at)) as date")
            ->selectRaw("COUNT(*) as total_count")
            ->selectRaw("SUM(CASE WHEN reservations.status = 'verified' THEN 1 ELSE 0 END) as verified_count")
            ->selectRaw("SUM(CASE WHEN reservations.status = 'cancelled' THEN 1 ELSE 0 END) as cancelled_count")
            ->groupBy('date')
            ->orderBy('date')
            ->get()
            ->map(function ($row) {
                $total = (int) ($row->total_count ?? 0);
                $verified = (int) ($row->verified_count ?? 0);
                $cancelled = (int) ($row->cancelled_count ?? 0);
                $effective = max(0, $total - $cancelled);
                return [
                    'date' => $row->date,
                    'total_count' => $total,
                    'verified_count' => $verified,
                    'cancelled_count' => $cancelled,
                    'verify_rate' => $effective > 0 ? round($verified / $effective, 4) : 0,
                ];
            })
            ->values();

        $compareVenues = DB::table('reservations')
            ->leftJoin('activity_days', 'activity_days.id', '=', 'reservations.activity_day_id')
            ->join('venues', 'venues.id', '=', 'reservations.venue_id')
            ->whereIn('reservations.venue_id', $venueIds)
            ->whereRaw("COALESCE(activity_days.activity_date, DATE(reservations.created_at)) >= ?", [$startDate])
            ->whereRaw("COALESCE(activity_days.activity_date, DATE(reservations.created_at)) <= ?", [$endDate])
            ->selectRaw('reservations.venue_id, venues.name as venue_name')
            ->selectRaw('COUNT(*) as total_count')
            ->selectRaw("SUM(CASE WHEN reservations.status = 'verified' THEN 1 ELSE 0 END) as verified_count")
            ->selectRaw("SUM(CASE WHEN reservations.status = 'cancelled' THEN 1 ELSE 0 END) as cancelled_count")
            ->groupBy('reservations.venue_id', 'venues.name')
            ->orderByDesc('total_count')
            ->get()
            ->map(function ($row) {
                $total = (int) ($row->total_count ?? 0);
                $verified = (int) ($row->verified_count ?? 0);
                $cancelled = (int) ($row->cancelled_count ?? 0);
                $effective = max(0, $total - $cancelled);
                $verifyRate = $effective > 0 ? round($verified / $effective, 4) : 0;
                $cancelRate = $total > 0 ? round($cancelled / $total, 4) : 0;
                $sampleEnough = $total >= 10;
                return [
                    'venue_id' => (int) $row->venue_id,
                    'venue_name' => $row->venue_name,
                    'total_count' => $total,
                    'verified_count' => $verified,
                    'cancelled_count' => $cancelled,
                    'verify_rate' => $verifyRate,
                    'cancel_rate' => $cancelRate,
                    'warning_low_verify' => $sampleEnough && $verifyRate < 0.4,
                    'warning_high_cancel' => $sampleEnough && $cancelRate > 0.3,
                ];
            })
            ->values();

        $realtimeAll = collect($this->realtimeCrowdService->venueCurrentMap($venueIds));
        if (!$user->isSuperAdmin() && $ownVenueIds->isNotEmpty()) {
            $mine = $realtimeAll->filter(fn ($v) => $ownVenueIds->contains((int) $v['venue_id']))->sortByDesc('current_count')->values();
            $others = $realtimeAll->filter(fn ($v) => !$ownVenueIds->contains((int) $v['venue_id']))->sortByDesc('current_count')->values();
            $realtimeAll = $mine->concat($others)->values();
        } else {
            $realtimeAll = $realtimeAll->sortByDesc('current_count')->values();
        }
        $realtime = [
            'city_total' => (int) $realtimeAll->sum('current_count'),
            'venues' => $realtimeAll->all(),
        ];

        return response()->json([
            'scope' => [
                'role' => $user->role,
                'venue_id' => $selectedVenueId,
                'start_date' => $startDate,
                'end_date' => $endDate,
            ],
            'summary' => [
                'total_count' => $totalCount,
                'verified_count' => $verifiedCount,
                'cancelled_count' => $cancelledCount,
                'pending_count' => $pendingCount,
                'effective_count' => $effectiveCount,
                'verify_rate' => $verifyRate,
                'blacklisted_unique' => (int) $blacklistedUnique,
                'activity_sessions' => (int) $activitySessions,
                'active_venue_count' => (int) $activeVenueCount,
            ],
            'trends' => $trends,
            'compare_venues' => $compareVenues,
            'realtime' => $realtime,
        ]);
    }
}