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

<?php

declare(strict_types=1);

namespace plugin\saiadmin\app\service;

use Illuminate\Database\Eloquent\Builder;
use OpenSpout\Common\Entity\Row;
use OpenSpout\Writer\XLSX\Writer;
use plugin\saiadmin\app\model\water\WaterModel;
use plugin\saiadmin\app\model\water\WaterEnterprise;
use plugin\saiadmin\app\model\water\WaterEnterpriseAddress;
use plugin\saiadmin\app\model\water\WaterAccount;
use plugin\saiadmin\app\model\water\WaterAgent;
use plugin\saiadmin\app\model\water\WaterOrder;
use plugin\saiadmin\app\model\water\WaterDevice;
use plugin\saiadmin\app\model\water\WaterDeviceUsage;
use plugin\saiadmin\app\model\water\WaterDelivery;
use plugin\saiadmin\app\model\water\WaterLedger;
use plugin\saiadmin\app\service\enterprise\EnterpriseFilters;
use plugin\saiadmin\app\service\enterprise\EnterpriseReplenishmentPolicy;
use plugin\saiadmin\app\service\device\DeviceStatusPolicy;
use plugin\saiadmin\exception\ApiException;

/** Application facade: model-backed reads, status policy and transactional batch commands. */
final class WaterEnterpriseDashboard
{
    private readonly \DateTimeImmutable $now;
    private readonly EnterpriseReplenishmentPolicy $replenishment;

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

    private function query(?EnterpriseFilters $filters = null): Builder
    {
        // Aggregate each one-to-many source before joining: no duplicated rights or stock.
        $alert = $this->replenishment->alertExpression();
        $urgent = $this->replenishment->urgentExpression();
        $accounts = WaterAccount::query()->alias('a')->join('sa_water_sku as s', 's.id', '=', 'a.sku_id')
            ->withActiveDelivery('task')
            ->selectRaw("a.enterprise_id, SUM(a.entitlement) AS entitlement, SUM(a.frozen) AS frozen,
                SUM(a.full_qty) AS full_qty, SUM(a.empty_qty) AS empty_qty,
                SUM(s.status = 1) AS enabled_accounts,
                SUM(s.status = 1 AND $alert AND $urgent AND task.id IS NULL) AS urgent_count,
                SUM(s.status = 1 AND $alert AND task.id IS NULL) AS pending_count")
            ->groupBy('a.enterprise_id');
        $deviceState = (new DeviceStatusPolicy($this->now))->expression();
        $devices = WaterDevice::query()->alias('d')->selectRaw("d.enterprise_id, COUNT(*) AS device_count,
            SUM(($deviceState) = 'online') AS online_count,
            SUM(($deviceState) = 'unconnected') AS unconnected_count")->groupBy('d.enterprise_id');
        $tasks = WaterDelivery::query()->whereNotNull('active_key')
            ->selectRaw('enterprise_id, COUNT(*) AS active_deliveries')->groupBy('enterprise_id');
        $query = WaterEnterprise::query()->alias('e')
            ->leftJoin('sa_water_agent as agent', 'agent.id', '=', 'e.agent_id')
            ->leftJoinSub($accounts, 'account_totals', 'account_totals.enterprise_id', '=', 'e.id')
            ->leftJoinSub($devices, 'device_totals', 'device_totals.enterprise_id', '=', 'e.id')
            ->leftJoinSub($tasks, 'task_totals', 'task_totals.enterprise_id', '=', 'e.id')
            ->selectRaw('e.*, agent.name AS agent_name, COALESCE(account_totals.entitlement,0) AS entitlement,
                COALESCE(account_totals.frozen,0) AS frozen, COALESCE(account_totals.full_qty,0) AS full_qty,
                COALESCE(account_totals.empty_qty,0) AS empty_qty,
                COALESCE(account_totals.entitlement - account_totals.frozen,0) AS available_entitlement,
                COALESCE(account_totals.enabled_accounts,0) AS enabled_accounts,
                COALESCE(account_totals.urgent_count,0) AS urgent_count,
                COALESCE(account_totals.pending_count,0) AS pending_count,
                COALESCE(device_totals.device_count,0) AS device_count,
                COALESCE(device_totals.online_count,0) AS online_count,
                COALESCE(device_totals.unconnected_count,0) AS unconnected_count,
                COALESCE(task_totals.active_deliveries,0) AS active_deliveries');
        foreach (['created_at' => 'latest_order_at', 'amount_cents' => 'latest_order_amount'] as $column => $alias) {
            $query->selectSub(WaterOrder::query()->whereColumn('enterprise_id', 'e.id')
                ->where('created_at', '<=', $this->now->format('Y-m-d H:i:s'))
                ->orderByDesc('created_at')->orderByDesc('id')->limit(1)->select($column), $alias);
        }
        if ($filters) {
            if ($filters->keyword !== '') {
                $query->matching('e.name|e.contact|e.phone', '%' . $filters->keyword . '%');
            }
            if ($filters->status !== '') {
                $query->where('e.status', (int) $filters->status);
            }
            if ($filters->agentId !== null) {
                $query->where('e.agent_id', $filters->agentId);
            }
            $balance = 'COALESCE(account_totals.entitlement - account_totals.frozen,0)';
            if ($filters->balance === 'empty') {
                $query->whereRaw("$balance <= 0");
            }
            if ($filters->balance === 'low') {
                $query->whereRaw("$balance > 0 AND $balance <= 10");
            }
            if ($filters->balance === 'sufficient') {
                $query->whereRaw("$balance > 10");
            }
        }

        return $query;
    }

    private function present(array $row): array
    {
        foreach (['id', 'agent_id', 'status', 'entitlement', 'frozen', 'full_qty', 'empty_qty', 'available_entitlement',
            'enabled_accounts', 'urgent_count', 'pending_count', 'device_count', 'online_count', 'unconnected_count', 'active_deliveries'] as $key) {
            $row[$key] = (int) $row[$key];
        }
        $row['employee_count'] = $row['employee_count'] === null ? null : (int) $row['employee_count'];
        $row['latest_order_amount'] = $row['latest_order_amount'] === null ? null : (int) $row['latest_order_amount'];
        $row['replenishment_status'] = $this->replenishment->resolve($row);

        return $row;
    }

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

        return WaterModel::transaction(function () use ($filters) {
            $list = $this->query($filters)->orderByDesc('e.id')->pageResult($filters->page, $filters->limit);
            $list['data'] = array_map($this->present(...), $list['data']);

            return ['list' => $list, 'summary' => $this->summary(), 'trend' => $this->trend(), 'funnel' => $this->funnel(),
                'agents' => WaterAgent::query()->orderBy('name')->get(['id', 'name', 'status'])->toArray(),
                'updated_at' => $this->now->format('Y-m-d H:i:s')];
        });
    }

    private function repurchase(\DateTimeImmutable $start, \DateTimeImmutable $end): array
    {
        $orders = WaterOrder::query()->whereBetween('created_at', [$start->format('Y-m-d H:i:s'), $end->format('Y-m-d H:i:s')]);
        $buyers = (clone $orders)->distinct()->count('enterprise_id');
        $repeatIds = WaterOrder::query()->where('created_at', '<=', $end->format('Y-m-d H:i:s'))
            ->select('enterprise_id')->groupBy('enterprise_id')->havingRaw('COUNT(*) >= 2');
        $repeat = (clone $orders)->whereIn('enterprise_id', $repeatIds)->distinct()->count('enterprise_id');

        return ['buyers' => $buyers, 'repeat' => $repeat, 'rate' => $buyers ? round(100 * $repeat / $buyers, 1) : null];
    }

    private function summary(): array
    {
        $start = $this->now->modify('first day of this month')->setTime(0, 0);
        $month = $this->repurchase($start, $this->now);
        $previous = $this->repurchase($start->modify('-1 month'), $start->modify('-1 second'));
        $since = $this->now->modify('-29 days')->setTime(0, 0)->format('Y-m-d H:i:s');
        $telemetry = WaterDeviceUsage::query()->alias('usage')->join('sa_water_device as d', 'd.id', '=', 'usage.device_id')
            ->whereBetween('usage.day', [substr($since, 0, 10), $this->now->format('Y-m-d')])->where('usage.liters', '>', 0)->select('d.enterprise_id');
        $exchanges = WaterLedger::query()->where('kind', 'exchange')->where('quantity', '<', 0)
            ->whereBetween('created_at', [$since, $this->now->format('Y-m-d H:i:s')])->select('enterprise_id');
        $active = WaterEnterprise::query()->where('status', 1)->where(function (Builder $q) use ($telemetry, $exchanges) {
            $q->whereIn('id', $telemetry)->orWhereIn('id', $exchanges);
        })->count();

        return ['total' => WaterEnterprise::query()->count(),
            'new_month' => WaterEnterprise::query()->whereBetween('created_at', [$start->format('Y-m-d H:i:s'), $this->now->format('Y-m-d H:i:s')])->count(),
            'active' => $active, 'entitlement' => (int) WaterAccount::query()->sum('entitlement'),
            'repurchase_rate' => $month['rate'], 'repurchase_buyers' => $month['buyers'], 'repeat_buyers' => $month['repeat'],
            'repurchase_change' => $month['rate'] !== null && $previous['rate'] !== null ? round($month['rate'] - $previous['rate'], 1) : null];
    }

    private function trend(): array
    {
        $start = $this->now->modify('-29 days')->setTime(0, 0);
        $base = WaterEnterprise::query()->where('created_at', '<', $start->format('Y-m-d H:i:s'))->count();
        $daily = WaterEnterprise::query()->whereBetween('created_at', [$start->format('Y-m-d H:i:s'), $this->now->format('Y-m-d H:i:s')])
            ->selectRaw('DATE(created_at) AS day, COUNT(*) AS total')->groupByRaw('DATE(created_at)')->pluck('total', 'day');
        $rows = [];
        for ($i = 0;$i < 30;$i++) {
            $day = $start->modify("+$i days")->format('Y-m-d');
            $new = (int) ($daily[$day] ?? 0);
            $base += $new;
            $rows[] = ['day' => $day, 'new' => $new, 'total' => $base];
        }

        return $rows;
    }

    private function funnel(): array
    {
        // One acquisition cohort, so conversion counts are nested and comparable.
        $cohort = WaterEnterprise::query()->whereBetween('created_at', [$this->now->format('Y-m-01 00:00:00'), $this->now->format('Y-m-d H:i:s')]);
        $orders = WaterOrder::query()->where('created_at', '<=', $this->now->format('Y-m-d H:i:s'))
            ->select('enterprise_id')->groupBy('enterprise_id');

        return ['registered' => (clone $cohort)->count(),
            'purchased' => (clone $cohort)->whereIn('id', (clone $orders)->havingRaw('COUNT(*) >= 1'))->count(),
            'repeated' => (clone $cohort)->whereIn('id', (clone $orders)->havingRaw('COUNT(*) >= 2'))->count()];
    }

    public function detail(int $id): array
    {
        return WaterModel::transaction(function () use ($id) {
            $row = $this->query()->where('e.id', $id)->first();
            if (!$row) {
                throw new ApiException('企业不存在');
            }

            return ['enterprise' => $this->present($row->toArray()),
                'addresses' => WaterEnterpriseAddress::query()->where('enterprise_id', $id)->orderByDesc('is_default')->orderBy('id')->get()->map(fn ($address) => [
                    'id' => (int) $address->id, 'label' => $address->label, 'contact' => $address->contact, 'phone' => $address->phone,
                    'region' => $address->region, 'address' => $address->address, 'longitude' => $address->longitude === null ? null : (float) $address->longitude,
                    'latitude' => $address->latitude === null ? null : (float) $address->latitude, 'is_default' => (int) $address->is_default === 1,
                    'status' => (int) $address->status, 'updated_at' => $address->updated_at,
                ])->all(),
                'accounts' => WaterAccount::query()->where('enterprise_id', $id)->with('sku:id,name,liters,status')->orderBy('id')->get()->toArray(),
                'orders' => WaterOrder::query()->where('enterprise_id', $id)->orderByDesc('created_at')->orderByDesc('id')->limit(10)->get()->toArray()];
        });
    }

    public function batchStatus(array $input, int $actor): array
    {
        $ids = $input['ids'] ?? null;
        if (!is_array($ids) || !array_is_list($ids) || !$ids || count($ids) > 100) {
            throw new ApiException('请选择1至100家企业');
        }
        $ids = array_values(array_unique(array_map(fn ($id) => WaterBusiness::positive($id, '企业'), $ids)));
        sort($ids);
        $status = WaterBusiness::positive($input['status'] ?? null, '客户状态');
        if (!in_array($status, [1, 2], true)) {
            throw new ApiException('客户状态不正确');
        }

        return WaterModel::transaction(function () use ($ids, $status, $actor) {
            $rows = WaterEnterprise::query()->whereIn('id', $ids)->orderBy('id')->lockForUpdate()->get();
            if (count($rows) !== count($ids)) {
                throw new ApiException('部分企业已不存在，请刷新列表');
            }
            $changed = 0;
            foreach ($rows as $row) {
                if ((int) $row->status === $status) {
                    continue;
                }
                $row->status = $status;
                $row->updated_at = $this->now->format('Y-m-d H:i:s');
                $row->save();
                WaterLedger::query()->create(['kind' => 'archive', 'created_by' => $actor, 'sku_id' => 0, 'enterprise_id' => $row->id,
                    'quantity' => 0, 'reference' => 'enterprise:' . $row->id, 'note' => $status === 1 ? '批量启用企业' : '批量停用企业',
                    'created_at' => $this->now->format('Y-m-d H:i:s')]);
                $changed++;
            }

            return ['changed' => $changed];
        });
    }

    public function export(array $input): string
    {
        $rows = $this->query(new EnterpriseFilters($input))->orderByDesc('e.id')->limit(50001)->get();
        if (count($rows) > 50000) {
            throw new ApiException('导出超过50000条，请缩小筛选范围');
        }
        $path = tempnam(sys_get_temp_dir(), 'water-enterprises-');
        if ($path === false) {
            throw new ApiException('无法创建导出文件');
        }
        $writer = new Writer();
        $opened = false;
        try {
            $writer->openToFile($path);
            $opened = true;
            $writer->addRow(Row::fromValues(['企业', '联系人', '手机', '区域', '代理', '供水地址', '客户状态', '套餐剩余权益/桶', '冻结权益/桶', '可用权益/桶', '现场满桶', '现场空桶', '设备数', '在线设备', '最近订单时间', '最近订单金额/元', '补货状态']));
            foreach ($rows as $model) {
                $r = $this->present($model->toArray());
                $values = [$r['name'], $r['contact'], $r['phone'], $r['region'], $r['agent_id'] ? ($r['agent_name'] ?? '代理不存在') : '直营客户',
                    $r['address'], $r['status'] === 1 ? '启用' : '停用', $r['entitlement'], $r['frozen'], $r['available_entitlement'], $r['full_qty'], $r['empty_qty'],
                    $r['device_count'], $r['online_count'], $r['latest_order_at'], $r['latest_order_amount'] === null ? null : $r['latest_order_amount'] / 100,
                    EnterpriseReplenishmentPolicy::LABELS[$r['replenishment_status']]];
                $writer->addRow(Row::fromValues(array_map(fn ($v) => is_string($v) && preg_match('/^[=+@\-\t\r\n]/u', $v) ? "'" . $v : $v, $values)));
            }
            $writer->close();
            $opened = false;
            $content = file_get_contents($path);
            if ($content === false) {
                throw new ApiException('读取客户导出失败');
            }

            return $content;
        } finally {
            try {
                if ($opened) {
                    $writer->close();
                }
            } finally {
                if (is_file($path)) {
                    unlink($path);
                }
            }
        }
    }
}