<?php

declare(strict_types=1);

namespace plugin\saiadmin\app\service;

use plugin\saiadmin\exception\ApiException;
use plugin\saiadmin\app\model\water\WaterModel;
use plugin\saiadmin\app\model\water\WaterLedger as WaterLedgerModel;
use plugin\saiadmin\app\model\water\WaterAccount;
use plugin\saiadmin\app\model\water\WaterDelivery;
use plugin\saiadmin\app\model\water\WaterDevice;
use plugin\saiadmin\app\model\water\WaterOrder;
use plugin\saiadmin\app\model\water\WaterSku;
use plugin\saiadmin\app\model\water\WaterRoute;
use plugin\saiadmin\app\model\water\WaterAgent;
use plugin\saiadmin\app\model\water\WaterEnterprise;
use plugin\saiadmin\app\model\water\WaterEnterpriseAddress;
use plugin\saiadmin\app\model\water\WaterPackage;
use plugin\saiadmin\app\model\water\WaterContent;
use plugin\saiadmin\app\service\content\ContentInput;
use plugin\saiadmin\app\service\content\ContentPolicy;
use plugin\saiadmin\app\service\alerts\ReplenishmentPolicy;

/** 供水运营。平台人员显式授权；实物、权益、在途分别记账。 */
class WaterBusiness
{
    public static function positive($value, string $label, bool $zero = false): int
    {
        if (filter_var($value, FILTER_VALIDATE_INT) === false || (int) $value < ($zero ? 0 : 1) || (int) $value > 10000000) {
            throw new ApiException($label . '必须是有效整数');
        }

        return (int) $value;
    }

    private function word(array $data, string $key, int $max = 120, bool $required = true): string
    {
        $value = trim((string) ($data[$key] ?? ''));
        if (($required && $value === '') || mb_strlen($value) > $max) {
            throw new ApiException($key . '不能为空或超出长度限制');
        }

        return $value;
    }

    private function now(): string
    {
        return date('Y-m-d H:i:s');
    }

    private function row(string $table, int $id, bool $lock = false): array
    {
        $row = WaterModel::forKind($table)->where('id', $id)->lock($lock)->first()?->toArray();
        if (!$row) {
            throw new ApiException('记录不存在');
        }

        return $row;
    }

    private function log(string $kind, int $actor, int $sku, int $enterprise, int $quantity, string $reference, string $note = '', int $amount = 0): void
    {
        WaterLedgerModel::query()->insert([
            'kind' => $kind, 'created_by' => $actor, 'sku_id' => $sku, 'enterprise_id' => $enterprise,
            'quantity' => $quantity, 'amount_cents' => $amount, 'reference' => $reference, 'note' => $note, 'created_at' => $this->now(),
        ]);
    }

    private function account(int $enterprise, int $sku): array
    {
        // 所有修改均先锁商品行，串行建立并锁定权益账户。
        $row = WaterAccount::query()->where(['enterprise_id' => $enterprise, 'sku_id' => $sku])->lock(true)->first()?->toArray();
        if (!$row) {
            $id = WaterAccount::query()->insertGetId(['enterprise_id' => $enterprise, 'sku_id' => $sku, 'updated_at' => $this->now()]);
            $row = $this->row('account', (int) $id, true);
        }

        return $row;
    }

    public function save(string $kind, array $data, int $actor): array
    {
        $id = isset($data['id']) ? self::positive($data['id'], 'ID', true) : 0;

        return WaterModel::transaction(function () use ($kind, $data, $actor, $id) {
            if ($id) {
                $this->row($kind, $id, true);
            }
            $status = (int) ($data['status'] ?? 1);
            if (!in_array($status, [1, 2], true)) {
                throw new ApiException('状态不支持');
            }
            $values = ['updated_at' => $this->now()];
            if ($kind === 'agent') {
                $values += ['name' => $this->word($data, 'name'), 'contact' => $this->word($data, 'contact', 60),
                    'phone' => $this->word($data, 'phone', 30), 'region' => $this->word($data, 'region', 160, false),
                    'address' => $this->word($data, 'address', 255, false), 'status' => $status];
            } elseif ($kind === 'enterprise') {
                $values += ['name' => $this->word($data, 'name'), 'contact' => $this->word($data, 'contact', 60),
                    'phone' => $this->word($data, 'phone', 30), 'address' => $this->word($data, 'address', 255),
                    'region' => $this->word($data, 'region', 80, false), 'status' => $status];
                if (array_key_exists('longitude', $data) || array_key_exists('latitude', $data)) {
                    $values += WaterDispatch::coordinates($data);
                }
                if (array_key_exists('employee_count', $data)) {
                    if ($data['employee_count'] !== null && !is_int($data['employee_count']) && !is_string($data['employee_count'])) {
                        throw new ApiException('公司人数必须是有效整数');
                    }
                    $values['employee_count'] = $data['employee_count'] === null || $data['employee_count'] === ''
                        ? null : self::positive($data['employee_count'], '公司人数');
                }
                if (array_key_exists('agent_id', $data)) {
                    $agentId = self::positive($data['agent_id'], '所属代理', true);
                    if ($agentId) {
                        $agent = $this->row('agent', $agentId, true);
                        $oldAgentId = $id ? (int) $this->row('enterprise', $id)['agent_id'] : 0;
                        if ((int) $agent['status'] !== 1 && $agentId !== $oldAgentId) {
                            throw new ApiException('不能分配给已停用代理');
                        }
                    }
                    $values['agent_id'] = $agentId;
                }
                // 基础版本一企业一个供水地址。有在途任务时禁止改地址。
                if ($id && WaterDelivery::query()->where('enterprise_id', $id)->whereNotNull('active_key')->count()) {
                    $old = $this->row('enterprise', $id);
                    if ($old['address'] !== $values['address']) {
                        throw new ApiException('存在未完成配送，请完成或取消后修改地址');
                    }
                }
            } elseif ($kind === 'sku') {
                $liters = $data['liters'] ?? null;
                if (!is_numeric($liters) || (float) $liters <= 0 || (float) $liters > 1000) {
                    throw new ApiException('规格容量必须在 0 到 1000 升之间');
                }
                $values += ['name' => $this->word($data, 'name'), 'liters' => round((float) $liters, 2),
                    'safety_stock' => self::positive($data['safety_stock'] ?? 0, '安全库存', true), 'status' => $status];
                if ($id && (float) $this->row('sku', $id)['liters'] !== (float) $values['liters'] && WaterAccount::query()->where('sku_id', $id)->count()) {
                    throw new ApiException('已使用的规格不能修改容量，请新增规格');
                }
            } elseif ($kind === 'package') {
                $sku = $this->row('sku', self::positive($data['sku_id'] ?? null, '商品规格'));
                $values += ['name' => $this->word($data, 'name'), 'sku_id' => $sku['id'],
                    'buckets' => self::positive($data['buckets'] ?? null, '套餐桶数'),
                    'price_cents' => self::positive($data['price_cents'] ?? null, '套餐金额（分）'), 'status' => $status];
            } elseif ($kind === 'device') {
                $enterprise = $this->row('enterprise', self::positive($data['enterprise_id'] ?? null, '企业'));
                $sku = $this->row('sku', self::positive($data['sku_id'] ?? null, '商品规格'));
                $sn = $this->word($data, 'sn', 80);
                if (WaterDevice::query()->where('sn', $sn)->where('id', '<>', $id)->count()) {
                    throw new ApiException('设备 SN 已存在');
                }
                $values += ['sn' => $sn, 'name' => $this->word($data, 'name'), 'enterprise_id' => $enterprise['id'],
                    'sku_id' => $sku['id'], 'firmware' => $this->word($data, 'firmware', 40, false)];
            } elseif ($kind === 'content') {
                $values += ContentInput::values($data);
            } else {
                throw new ApiException('不支持的业务类型');
            }
            if ($id) {
                WaterModel::forKind($kind)->where('id', $id)->update($values);
            } else {
                $idNew = WaterModel::forKind($kind)->insertGetId($values + ['created_at' => $this->now()]);
            }
            $resultId = $id ?: (int) $idNew;
            $this->log('archive', $actor, 0, 0, 0, $kind . ':' . $resultId, $id ? '修改档案' : '新增档案');

            return ['id' => $resultId];
        });
    }

    public function deletePackage(array $data, int $actor): array
    {
        $id = self::positive($data['id'] ?? null, '套餐ID');

        return WaterModel::transaction(function () use ($id, $actor) {
            $package = $this->row('package', $id, true);
            WaterPackage::query()->where('id', $id)->delete();
            $this->log('archive', $actor, (int) $package['sku_id'], 0, 0, 'package:' . $id, '删除套餐：' . $package['name']);

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

    public function purchase(array $data, int $actor): array
    {
        $reference = $this->word($data, 'reference', 100);
        $enterpriseId = self::positive($data['enterprise_id'] ?? null, '企业');
        $packageId = self::positive($data['package_id'] ?? null, '套餐');

        return WaterModel::transaction(function () use ($enterpriseId, $packageId, $reference, $actor) {
            // 收款凭据全局唯一；重试不重复增加权益。
            $package = $this->row('package', $packageId, true);
            $sku = $this->row('sku', (int) $package['sku_id'], true);
            $enterprise = $this->row('enterprise', $enterpriseId, true);
            $existing = WaterOrder::query()->where('reference', $reference)->first()?->toArray();
            if ($existing) {
                if ((int) $existing['enterprise_id'] !== $enterpriseId || (int) $existing['package_id'] !== $packageId) {
                    throw new ApiException('该收款凭据已被其他订单使用');
                }

                return ['id' => (int) $existing['id'], 'replayed' => true];
            }
            if ((int) $package['status'] !== 1 || (int) $sku['status'] !== 1 || (int) $enterprise['status'] !== 1) {
                throw new ApiException('企业、商品或套餐已停用');
            }

            return (new WaterPurchaseReceipt())->issue($enterpriseId, $package, $reference, $actor);
        });
    }

    public function stock(array $data, int $actor): array
    {
        $skuId = self::positive($data['sku_id'] ?? null, '商品规格');
        $enterpriseId = self::positive($data['enterprise_id'] ?? 0, '企业', true);
        $quantity = self::positive($data['quantity'] ?? null, '数量', true);
        $mode = $data['mode'] ?? '';
        if (!in_array($mode, ['inbound', 'warehouse_count', 'site_count', 'exchange'], true)) {
            throw new ApiException('库存操作不支持');
        }
        $note = $this->word($data, 'note', 255);
        $reference = $this->word($data, 'reference', 100);

        return WaterModel::transaction(function () use ($skuId, $enterpriseId, $quantity, $mode, $note, $reference, $actor) {
            $sku = $this->row('sku', $skuId, true);
            $previous = WaterLedgerModel::query()->where(['sku_id' => $skuId, 'kind' => $mode, 'reference' => $reference])->first()?->toArray();
            if ($previous) {
                throw new ApiException('该操作凭据已登记，请刷新查看库存');
            }
            if (in_array($mode, ['inbound', 'warehouse_count'], true)) {
                $target = $mode === 'inbound' ? (int) $sku['available'] + $quantity : $quantity;
                $delta = $target - (int) $sku['available'];
                WaterSku::query()->where('id', $skuId)->update(['available' => $target, 'updated_at' => $this->now()]);
            } else {
                $this->row('enterprise', $enterpriseId, true);
                $account = $this->account($enterpriseId, $skuId);
                $delta = $mode === 'exchange' ? -$quantity : $quantity - (int) $account['full_qty'];
                if ((int) $account['full_qty'] + $delta < 0) {
                    throw new ApiException('现场满桶不足');
                }
                WaterAccount::query()->where('id', $account['id'])->update([
                    'full_qty' => (int) $account['full_qty'] + $delta,
                    'empty_qty' => (int) $account['empty_qty'] + ($mode === 'exchange' ? $quantity : 0), 'updated_at' => $this->now(),
                ]);
            }
            $this->log($mode, $actor, $skuId, $enterpriseId, $delta, $reference, $note);

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

    /** Save platform-wide parameters; legacy enterprise overrides remain unsupported. */
    public function saveAlertPolicy(array $data, int $actor): array
    {
        return (new \plugin\saiadmin\app\service\alerts\AlertSettings())->save($data, $actor);
    }

    /** 整批事务复用单笔冻结规则；固定加锁顺序减少并发批次死锁。 */
    public function batchCreateDelivery(array $data, int $actor): array
    {
        $items = $data['items'] ?? null;
        if (!is_array($items) || !array_is_list($items) || !$items || count($items) > 50) {
            throw new ApiException('请选择 1 到 50 条补货记录');
        }
        $tasks = [];
        foreach ($items as $item) {
            if (!is_array($item)) {
                throw new ApiException('补货记录格式不正确');
            }
            $id = self::positive($item['account_id'] ?? null, '权益账户');
            if (isset($tasks[$id])) {
                throw new ApiException('同一权益账户不能重复选择');
            }
            $account = $this->row('account', $id);
            $tasks[$id] = ['account_id' => $id, 'enterprise_id' => (int) $account['enterprise_id'],
                'sku_id' => (int) $account['sku_id'], 'quantity' => self::positive($item['quantity'] ?? null, '配送桶数')];
        }
        usort($tasks, fn ($a, $b) => [$a['sku_id'], $a['enterprise_id']] <=> [$b['sku_id'], $b['enterprise_id']]);

        return WaterModel::transaction(function () use ($tasks, $actor) {
            $created = [];
            foreach ($tasks as $task) {
                try {
                    $created[] = $this->createDelivery($task, $actor) + ['account_id' => $task['account_id']];
                } catch (ApiException $e) {
                    throw new ApiException('账户 #' . $task['account_id'] . '：' . $e->getMessage() . '；本批次未创建任何任务');
                }
            }

            return ['created' => $created, 'count' => count($created)];
        });
    }

    public function createDelivery(array $data, int $actor): array
    {
        $enterpriseId = self::positive($data['enterprise_id'] ?? null, '企业');
        $skuId = self::positive($data['sku_id'] ?? null, '商品规格');
        $quantity = self::positive($data['quantity'] ?? null, '配送桶数');

        return WaterModel::transaction(function () use ($enterpriseId, $skuId, $quantity, $actor, $data) {
            $sku = $this->row('sku', $skuId, true);
            $enterprise = $this->row('enterprise', $enterpriseId, true);
            if ((int) $sku['status'] !== 1 || (int) $enterprise['status'] !== 1) {
                throw new ApiException('企业或商品已停用');
            }
            $addressId = self::positive($data['address_id'] ?? 0, '配送地址', true);
            $address = $addressId
                ? WaterEnterpriseAddress::query()->where('enterprise_id', $enterpriseId)->where('status', 1)->find($addressId)
                : WaterEnterpriseAddress::query()->where('enterprise_id', $enterpriseId)->where('status', 1)->where('is_default', 1)->orderBy('id')->first();
            if ($addressId && !$address) {
                throw new ApiException('配送地址不存在或已停用');
            }
            if (WaterDelivery::query()->where('enterprise_id', $enterpriseId)->where('sku_id', $skuId)->whereNotNull('active_key')->exists()) {
                throw new ApiException('该企业及规格已有有效配送任务');
            }
            $key = $enterpriseId . ':' . ($address?->id ?? 0) . ':' . $skuId;
            $account = $this->account($enterpriseId, $skuId);
            if ((int) $account['entitlement'] - (int) $account['frozen'] < $quantity) {
                throw new ApiException('可用套餐权益不足');
            }
            if ((int) $sku['available'] < $quantity) {
                throw new ApiException('仓库可用库存不足');
            }
            WaterSku::query()->where('id', $skuId)->update([
                'available' => (int) $sku['available'] - $quantity, 'locked' => (int) $sku['locked'] + $quantity, 'updated_at' => $this->now(),
            ]);
            WaterAccount::query()->where('id', $account['id'])->update(['frozen' => (int) $account['frozen'] + $quantity, 'updated_at' => $this->now()]);
            $id = WaterDelivery::query()->insertGetId([
                'enterprise_id' => $enterpriseId, 'address_id' => $address?->id, 'sku_id' => $skuId, 'quantity' => $quantity,
                'address' => $address?->address ?? $enterprise['address'], 'region' => $address?->region ?? $enterprise['region'],
                'active_key' => $key, 'created_by' => $actor, 'created_at' => $this->now(), 'updated_at' => $this->now(),
                'scheduled_date' => WaterDispatch::date($data['date'] ?? date('Y-m-d')),
                'longitude' => $address?->longitude ?? ($enterprise['longitude'] ?? null), 'latitude' => $address?->latitude ?? ($enterprise['latitude'] ?? null),
            ]);
            $this->log('reserve', $actor, $skuId, $enterpriseId, $quantity, 'delivery:' . $id, '冻结库存及权益');

            return ['id' => (int) $id];
        });
    }

    public function transition(array $data, int $actor): array
    {
        $id = self::positive($data['id'] ?? null, '配送任务');
        $action = $data['action'] ?? '';
        if (!in_array($action, ['assign', 'depart', 'complete', 'cancel'], true)) {
            throw new ApiException('配送操作不支持');
        }
        $preview = $this->row('delivery', $id);
        if ($action === 'complete') {
            if ($preview['status'] === 'completed' && !$preview['received_at']) {
                return ['id' => $id, 'replayed' => true];
            }

            return (new WaterFulfillment())->receive(['id' => $id, 'accepted_qty' => (int) $preview['quantity'],
                'empty_returned' => $data['empty_returned'] ?? 0, 'receiver' => $data['receiver'] ?? '后台确认', 'reason' => ''], $actor);
        }

        return WaterModel::transaction(function () use ($preview, $id, $action, $data, $actor) {
            $skuId = (int) $preview['sku_id'];
            $enterpriseId = (int) $preview['enterprise_id'];
            $sku = $this->row('sku', $skuId, true);
            $task = $this->row('delivery', $id, true);
            $account = $this->account($enterpriseId, $skuId);
            $quantity = (int) $task['quantity'];
            $next = ['assign' => 'assigned', 'depart' => 'transit', 'complete' => 'completed', 'cancel' => 'cancelled'][$action];
            if ($task['status'] === $next) {
                return ['id' => $id, 'replayed' => true];
            }
            $allowed = ['assign' => ['pending'], 'depart' => ['assigned'], 'complete' => ['transit'], 'cancel' => ['pending', 'assigned']];
            if (!in_array($task['status'], $allowed[$action], true)) {
                throw new ApiException('当前配送状态不允许此操作');
            }
            $values = ['status' => $next, 'updated_at' => $this->now()];
            if ($action === 'assign') {
                $values += ['driver' => $this->word($data, 'driver', 60), 'driver_phone' => $this->word($data, 'driver_phone', 30)];
            } elseif ($action === 'depart') {
                if ($task['route_id'] ?? null) {
                    $routeDate = WaterRoute::query()->where('id', $task['route_id'])->value('work_date');
                    if ($routeDate > date('Y-m-d')) {
                        throw new ApiException('配送日期未到，不能提前出库');
                    }
                }
                if ((int) $sku['locked'] < $quantity) {
                    throw new ApiException('锁定库存异常，请核对');
                }
                WaterSku::query()->where('id', $skuId)->update(['locked' => (int) $sku['locked'] - $quantity, 'transit' => (int) $sku['transit'] + $quantity]);
            } else {
                if ((int) $sku['locked'] < $quantity || (int) $account['frozen'] < $quantity) {
                    throw new ApiException('冻结数据异常，请核对');
                }
                WaterSku::query()->where('id', $skuId)->update(['locked' => (int) $sku['locked'] - $quantity, 'available' => (int) $sku['available'] + $quantity]);
                WaterAccount::query()->where('id', $account['id'])->update(['frozen' => (int) $account['frozen'] - $quantity, 'updated_at' => $this->now()]);
                $values['active_key'] = null;
            }
            WaterDelivery::query()->where('id', $id)->update($values);
            $this->log($action, $actor, $skuId, $enterpriseId, $quantity, 'delivery:' . $id);

            return ['id' => $id, 'status' => $next];
        });
    }

    public function listing(string $module, array $filters): array
    {
        $keyword = trim((string) ($filters['keyword'] ?? ''));
        if (mb_strlen($keyword) > 120) {
            throw new ApiException('搜索内容过长');
        }
        $page = max(1, (int) ($filters['page'] ?? 1));
        $limit = min(100, max(1, (int) ($filters['limit'] ?? 20)));
        switch ($module) {
            case 'agents':
                $query = WaterAgent::query()->alias('a')->selectRaw('a.id,a.user_id,a.name,a.contact,a.phone,a.region,a.address,a.status,a.created_at,a.updated_at');
                $search = 'a.name|a.contact|a.phone|a.region';
                $order = 'a.id';
                break;
            case 'enterprises':
                $query = WaterEnterprise::query()->alias('e')->selectRaw('e.*');
                $search = 'e.name|e.contact|e.phone';
                $order = 'e.id';
                break;
            case 'inventory':
                $query = WaterSku::query()->alias('s')->selectRaw('s.*');
                $search = 's.name';
                $order = 's.id';
                break;
            case 'packages':
                $query = WaterPackage::query()->alias('p')->leftJoin('sa_water_sku as s', 's.id', '=', 'p.sku_id')->selectRaw('p.*,s.name AS sku_name,s.liters');
                $search = 'p.name|s.name';
                $order = 'p.id';
                break;
            case 'dispatch':
                $query = WaterDelivery::query()->alias('d')->leftJoin('sa_water_enterprise as e', 'e.id', '=', 'd.enterprise_id')->leftJoin('sa_water_sku as s', 's.id', '=', 'd.sku_id')->selectRaw('d.*,e.name AS enterprise_name,s.name AS sku_name');
                $search = 'e.name|d.driver|d.region';
                $order = 'd.id';
                if (!empty($filters['status'])) {
                    $query->where('d.status', (string) $filters['status']);
                }
                break;
            case 'devices':
                $query = WaterDevice::query()->alias('d')->leftJoin('sa_water_enterprise as e', 'e.id', '=', 'd.enterprise_id')->leftJoin('sa_water_sku as s', 's.id', '=', 'd.sku_id')->selectRaw('d.*,e.name AS enterprise_name,s.name AS sku_name');
                $search = 'd.sn|d.name|e.name';
                $order = 'd.id';
                break;
            case 'finance':
                $query = WaterOrder::query()->alias('o')->leftJoin('sa_water_enterprise as e', 'e.id', '=', 'o.enterprise_id')->selectRaw('o.*,e.name AS enterprise_name');
                $search = 'o.reference|e.name|o.package_name';
                $order = 'o.id';
                break;
            case 'content':
                $query = WaterContent::query()->alias('c')->selectRaw('c.*');
                $search = 'c.title|c.region';
                $order = 'c.sort';
                break;
            case 'alerts':
                return (new WaterAlertDashboard())->listing($filters);
            case 'accounts':
                $query = 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()
                    ->selectRaw('a.*,e.name AS enterprise_name,e.region,e.address,e.employee_count,s.name AS sku_name,s.liters,d.id AS delivery_id,d.status AS delivery_status');
                if (!empty($filters['enterprise_id'])) {
                    $query->where('a.enterprise_id', (int) $filters['enterprise_id']);
                }
                $search = 'e.name|e.region|s.name';
                $order = 'a.id';
                break;
            case 'ledger':
                return (new WaterLedger())->listing($filters);
            default: throw new ApiException('不支持的业务模块');
        }
        if ($keyword !== '') {
            $query->matching($search, '%' . $keyword . '%');
        }
        $result = $query->orderBy($order, 'desc')->pageResult($page, $limit);
        $policy = $module === 'accounts' ? new ReplenishmentPolicy() : null;
        foreach ($result['data'] as &$row) {
            if (in_array($module, ['alerts', 'accounts'], true)) {
                $row = array_merge($row, $policy->evaluate($row));
                unset($row['raw_days_left']);
            }
            if ($module === 'devices') {
                $row['connection_status'] = !$row['last_seen'] ? 'unconnected' : (strtotime($row['last_seen']) < time() - 900 ? 'offline' : 'online');
                if ($row['telemetry_date'] !== date('Y-m-d')) {
                    $row['today_liters'] = null;
                }
            }
            if ($module === 'content') {
                $row['delivery_status'] = ContentPolicy::status($row, $this->now());
            }
        }

        return $result;
    }

    public function overview(): array
    {
        $today = date('Y-m-d 00:00:00');
        $counts = WaterDelivery::query()->selectRaw('status,COUNT(*) AS total')->groupByRaw('status')->get()->toArray();
        $states = array_fill_keys(['pending', 'assigned', 'transit', 'returning', 'returned', 'completed', 'cancelled'], 0);
        foreach ($counts as $count) {
            $states[$count['status']] = (int) $count['total'];
        }
        $todayStates = WaterDelivery::query()->where('created_at', '>=', $today)->selectRaw('status,COUNT(*) AS total')->groupByRaw('status')->get()->toArray();
        $fulfilment = ['completed' => 0, 'transit' => 0, 'pending' => 0, 'returning' => 0, 'returned' => 0];
        foreach ($todayStates as $count) {
            if ($count['status'] === 'cancelled') {
                continue;
            }
            $key = in_array($count['status'], ['pending', 'assigned']) ? 'pending' : $count['status'];
            $fulfilment[$key] += (int) $count['total'];
        }
        $stock = WaterSku::query()->selectRaw('COALESCE(SUM(available),0) AS available, COALESCE(SUM(locked),0) AS locked, COALESCE(SUM(transit),0) AS transit, COALESCE(SUM(empty_qty),0) AS empty_qty')->first()->toArray();
        $trend = WaterOrder::query()->where('created_at','>=',date('Y-m-d 00:00:00',strtotime('-29 days')))->selectRaw('DATE(created_at) AS day,SUM(amount_cents) AS amount_cents')->groupByRaw('DATE(created_at)')->get()->toArray();
        $online = WaterDevice::query()->where('last_seen','>=',date('Y-m-d H:i:s',time() - 900))->count();

        return ['today_cents' => (int) WaterOrder::query()->where('created_at','>=',$today)->sum('amount_cents'),
            'enterprise_count' => (int) WaterEnterprise::query()->count(),
            'device_count' => (int) WaterDevice::query()->count(), 'online_count' => (int) $online,
            'states' => $states, 'fulfilment' => $fulfilment, 'stock' => $stock, 'trend' => $trend,
            'alerts' => $this->listing('alerts',['limit' => 5]),
        ];
    }
}