water/server/plugin/saiadmin/app/service/WaterAlertDashboard.php

<?php

declare(strict_types=1);

namespace plugin\saiadmin\app\service;

use Illuminate\Database\Eloquent\Builder;
use plugin\saiadmin\app\model\water\WaterModel;
use plugin\saiadmin\app\model\water\WaterAccount;
use plugin\saiadmin\app\model\water\WaterDevice;
use plugin\saiadmin\app\model\water\WaterDelivery;
use plugin\saiadmin\app\service\alerts\AlertFilters;
use plugin\saiadmin\app\service\alerts\ReplenishmentPolicy;

final class WaterAlertDashboard
{
    private readonly \DateTimeImmutable $now;
    private readonly ReplenishmentPolicy $policy;

    public function __construct(?\DateTimeImmutable $now = null)
    {
        $this->now = ($now ?? new \DateTimeImmutable())->setTimezone(new \DateTimeZone('Asia/Shanghai'));
        $this->policy = new ReplenishmentPolicy();
    }

    private function query(): Builder
    {
        return WaterAccount::query()->alias('a')
            ->join('sa_water_enterprise as e', 'e.id', '=', 'a.enterprise_id')
            ->join('sa_water_sku as s', 's.id', '=', 'a.sku_id')
            ->withActiveDelivery()
            ->where('e.status', 1)->where('s.status', 1);
    }

    private function exhaustionExpression(): string
    {
        $days = $this->policy->daysExpression();

        return "CASE WHEN $days <= 3650 THEN TIMESTAMPADD(SECOND, CEIL($days * 86400), ?) ELSE NULL END";
    }

    private function filtered(AlertFilters $filters): Builder
    {
        $query = $this->query();
        $alert = $this->policy->alertExpression();
        $urgent = $this->policy->urgentExpression();
        if ($filters->scope === 'alerts') {
            $query->whereRaw($alert);
        }
        if ($filters->severity === 'urgent') {
            $query->whereRaw("$alert AND $urgent");
        }
        if ($filters->severity === 'normal') {
            $query->whereRaw("$alert AND NOT $urgent");
        }
        if ($filters->severity === 'healthy') {
            $query->whereRaw("NOT $alert");
        }
        if ($filters->region !== '') {
            $query->where('e.region', $filters->region);
        }
        if ($filters->enterpriseId !== null) {
            $query->where('a.enterprise_id', $filters->enterpriseId);
        }
        if ($filters->keyword !== '') {
            $query->where(function ($q) use ($filters) {
                $pattern = '%' . $filters->keyword . '%';
                $q->where('e.name', 'like', $pattern)->orWhere('e.address', 'like', $pattern)
                    ->orWhere('e.region', 'like', $pattern)->orWhere('s.name', 'like', $pattern)
                    ->orWhereExists(WaterDevice::query()->selectRaw('1')->whereColumn('enterprise_id', 'a.enterprise_id')
                        ->whereColumn('sku_id', 'a.sku_id')->where(fn ($device) => $device->where('sn', 'like', $pattern)->orWhere('name', 'like', $pattern)));
            });
        }
        if ($filters->exhaustDate !== '') {
            // Guard unrealistic horizons before timestamp arithmetic; no estimates without positive usage.
            $query->whereRaw(
                'DATE(' . $this->exhaustionExpression() . ') = ?',
                [$this->now->format('Y-m-d H:i:s'), $filters->exhaustDate]
            );
        }

        return $query;
    }

    private function page(AlertFilters $filters): array
    {
        $alert = $this->policy->alertExpression();
        $urgent = $this->policy->urgentExpression();
        $days = $this->policy->daysExpression();
        $query = $this->filtered($filters)->selectRaw('a.*, e.name AS enterprise_name, e.region, e.address, e.employee_count,
            s.name AS sku_name, s.liters, s.available AS warehouse_available, d.id AS delivery_id, d.status AS delivery_status')
            ->selectRaw($this->exhaustionExpression() . ' AS exhausts_at', [$this->now->format('Y-m-d H:i:s')])
            ->selectSub(WaterDevice::query()->whereColumn('enterprise_id', 'a.enterprise_id')->whereColumn('sku_id', 'a.sku_id')->selectRaw('COUNT(*)'), 'device_count')
            ->orderByRaw("CASE WHEN $alert AND $urgent THEN 0 WHEN $alert THEN 1 ELSE 2 END")
            ->orderByRaw("($days IS NULL) ASC")->orderByRaw("$days ASC")->orderByDesc('a.id');
        $page = $query->pageResult($filters->page, $filters->limit);
        $page['data'] = array_map(function ($row) {
            foreach (['id', 'enterprise_id', 'sku_id', 'entitlement', 'frozen', 'full_qty', 'empty_qty', 'threshold_qty', 'warehouse_available', 'device_count'] as $key) {
                $row[$key] = (int) $row[$key];
            }
            $row['delivery_id'] = $row['delivery_id'] === null ? null : (int) $row['delivery_id'];
            $row['daily_liters'] = $row['daily_liters'] === null ? null : (float) $row['daily_liters'];
            $row['liters'] = (float) $row['liters'];
            $row = array_merge($row, $this->policy->evaluate($row));
            unset($row['raw_days_left']);
            $row['blocked_reason'] = $row['delivery_id'] ? '已有有效配送任务' : (!$row['is_alert'] ? '当前无需补货'
                : ($row['available_entitlement'] <= 0 ? '可用套餐权益不足' : ($row['warehouse_available'] <= 0 ? '仓库可用库存不足' : '')));
            $row['can_create'] = $row['blocked_reason'] === '';

            return $row;
        }, $page['data']);

        return $page;
    }

    public function listing(array $input): array
    {
        return $this->page(new AlertFilters($input));
    }

    public function dashboard(array $input): array
    {
        $filters = new AlertFilters($input);

        return WaterModel::transaction(function () use ($filters) {
            $alert = $this->policy->alertExpression();
            $urgent = $this->policy->urgentExpression();
            $counts = $this->query()->selectRaw("COUNT(*) AS accounts, COALESCE(SUM($alert),0) AS alerts,
                COALESCE(SUM($alert AND $urgent),0) AS urgent, COALESCE(SUM($alert AND NOT $urgent),0) AS normal,
                COALESCE(SUM($alert AND d.id IS NOT NULL),0) AS ordered")->first();
            $created = WaterDelivery::query()->where('status', '<>', 'cancelled')
                ->whereBetween('created_at', [$this->now->format('Y-m-d 00:00:00'), $this->now->format('Y-m-d H:i:s')])
                ->selectRaw('COUNT(*) AS count, COALESCE(SUM(quantity),0) AS quantity')->first();
            $days = WaterDelivery::query()->where('status', '<>', 'cancelled')
                ->whereBetween('created_at', [$this->now->modify('-6 days')->format('Y-m-d 00:00:00'), $this->now->format('Y-m-d H:i:s')])
                ->selectRaw('DATE(created_at) AS day, COUNT(*) AS count, SUM(quantity) AS quantity')->groupByRaw('DATE(created_at)')->get()->keyBy('day');
            $trend = [];
            for ($offset = 6; $offset >= 0; $offset--) {
                $date = $this->now->modify('-' . $offset . ' days')->format('Y-m-d');
                $record = $days->get($date);
                $trend[] = ['day' => $date, 'count' => (int) ($record->count ?? 0), 'quantity' => (int) ($record->quantity ?? 0)];
            }

            return ['list' => $this->page($filters), 'summary' => ['accounts' => (int) $counts->accounts, 'alerts' => (int) $counts->alerts,
                'urgent' => (int) $counts->urgent, 'normal' => (int) $counts->normal, 'ordered' => (int) $counts->ordered,
                'created_today' => (int) $created->count, 'quantity_today' => (int) $created->quantity, 'accuracy' => null],
                'regions' => $this->query()->where('e.region', '<>', '')->distinct()->orderBy('e.region')->pluck('e.region')->all(),
                'trend' => $trend, 'trend_kind' => 'delivery_created',
                'policy' => $this->policy->settings(),
                'updated_at' => $this->now->format('Y-m-d H:i:s')];
        });
    }
}