cunkebao_v3/Server/application/superadmin/service/ProjectOverviewService.php

<?php

namespace app\superadmin\service;

use app\common\model\User as UserModel;
use app\common\service\TrafficPoolSystemIdentifierService;
use app\cunkebao\service\DeviceGeoHelper;
use think\Db;

/**
 * 超管项目总览 / 统一账号 / 设备同步
 */
class ProjectOverviewService
{
    public function getSummary(): array
    {
        $projectTotal = (int) Db::name('company')->count('id');
        $projectActive = (int) Db::name('company')->where('status', 1)->count('id');
        $deviceTotal = (int) Db::name('device')->where('deleteTime', 0)->count('id');
        $deviceOnline = (int) Db::name('device')->where('deleteTime', 0)->where('alive', 1)->count('id');
        $customerQuery = Db::name('traffic_pool_company')->alias('tpc')->where('tpc.isDel', 0);
        TrafficPoolSystemIdentifierService::applyExcludeToCompanyQuery($customerQuery, 'tpc');
        $customerTotal = (int) $customerQuery->count('tpc.id');

        $tokenTotal = (int) Db::name('tokens_company')->where('isAdmin', 1)->sum('tokens');
        $contentTotal = (int) Db::name('content_library')->where('isDel', 0)->count('id');

        return [
            'projectTotal'      => $projectTotal,
            'projectActive'     => $projectActive,
            'projectDisabled'   => $projectTotal - $projectActive,
            'deviceTotal'       => $deviceTotal,
            'deviceOnline'      => $deviceOnline,
            'deviceOffline'     => $deviceTotal - $deviceOnline,
            'customerTotal'     => $customerTotal,
            'tokenBalanceTotal' => $tokenTotal,
            'contentLibraryTotal' => $contentTotal,
        ];
    }

    public function getProjectRows(int $page = 1, int $limit = 20, string $keyword = ''): array
    {
        $query = Db::name('company')->alias('c')
            ->leftJoin('users u', 'u.companyId = c.companyId AND u.isAdmin = ' . UserModel::ADMIN_STP . ' AND u.deleteTime = 0')
            ->field([
                'c.id', 'c.name', 'c.status', 'c.companyId', 'c.memo', 'c.createTime',
                'u.account', 'u.phone', 'u.username', 'u.s2_accountId', 'u.status as userStatus',
            ]);

        if ($keyword !== '') {
            $like = '%' . $keyword . '%';
            $query->where(function ($q) use ($like) {
                $q->whereLike('c.name', $like)
                    ->whereOr('u.account', 'like', $like)
                    ->whereOr('u.phone', 'like', $like);
            });
        }

        $total = (clone $query)->count('c.id');
        $rows = $query->order('c.id', 'desc')->page($page, $limit)->select();

        $companyIds = array_column($rows, 'companyId');
        $deviceStats = $this->deviceStatsByCompany($companyIds);
        $customerStats = $this->customerStatsByCompany($companyIds);
        $subUserStats = $this->subUserStatsByCompany($companyIds);

        $list = [];
        foreach ($rows as $row) {
            $cid = (int) $row['companyId'];
            $deviceCount = $deviceStats[$cid]['total'] ?? 0;
            $deviceOnline = $deviceStats[$cid]['online'] ?? 0;
            $customerCount = $customerStats[$cid] ?? 0;
            $subUserCount = $subUserStats[$cid] ?? 0;
            $hasMasterPhone = !empty($row['phone']);

            $health = 'ok';
            if ((int) $row['status'] !== 1 || (int) ($row['userStatus'] ?? 0) !== 1) {
                $health = 'error';
            } elseif (!$hasMasterPhone || $deviceCount === 0) {
                $health = 'warn';
            }

            $list[] = [
                'id'            => (int) $row['id'],
                'companyId'     => $cid,
                'name'          => $row['name'],
                'status'        => (int) $row['status'],
                'account'       => $row['account'] ?: '',
                'phone'         => $row['phone'] ?: '',
                'username'      => $row['username'] ?: '',
                'deviceCount'   => $deviceCount,
                'deviceOnline'  => $deviceOnline,
                'customerCount' => $customerCount,
                'subUserCount'  => $subUserCount,
                'health'        => $health,
                'createTime'    => $this->formatTime($row['createTime']),
            ];
        }

        return [
            'list'  => $list,
            'total' => $total,
            'page'  => $page,
            'limit' => $limit,
        ];
    }

    public function getUnifiedAccounts(int $projectId): array
    {
        $company = Db::name('company')->where('id', $projectId)->find();
        if (!$company) {
            throw new \Exception('项目不存在', 404);
        }

        $companyId = (int) $company['companyId'];
        $master = Db::name('users')
            ->where('companyId', $companyId)
            ->where('isAdmin', UserModel::ADMIN_STP)
            ->where('deleteTime', 0)
            ->find();

        $masterUserId = ProjectContextService::primaryMasterUserId($companyId);
        $subUserQuery = Db::name('users')
            ->where('companyId', $companyId)
            ->where('deleteTime', 0);
        if ($masterUserId > 0) {
            $subUserQuery->where('id', '<>', $masterUserId);
        }
        $subUsers = $subUserQuery
            ->field('id,account,username,phone,status,typeId,s2_accountId,isAdmin,createTime')
            ->select();

        $storeUsers = Db::name('users')
            ->where('companyId', $companyId)
            ->where('typeId', 2)
            ->where('deleteTime', 0)
            ->field('id,account,username,phone,status,typeId,s2_accountId,createTime')
            ->select();

        $devices = Db::name('device')
            ->where('companyId', $companyId)
            ->where('deleteTime', 0)
            ->field('id,memo,imei,phone,model,brand,alive,createTime')
            ->select();

        $endpoints = $this->loginEndpoints();
        $s2Bound = $master && !empty($master['s2_accountId']);

        return [
            'project' => [
                'id'        => (int) $company['id'],
                'companyId' => $companyId,
                'name'      => $company['name'],
                'status'    => (int) $company['status'],
            ],
            'master'  => $master ? [
                'id'           => (int) $master['id'],
                'account'      => $master['account'],
                'username'     => $master['username'],
                'phone'        => $master['phone'],
                'status'       => (int) $master['status'],
                'typeId'       => (int) $master['typeId'],
                's2_accountId' => $master['s2_accountId'] ?: '',
            ] : null,
            'subUsers'    => $subUsers ?: [],
            'storeUsers'  => $storeUsers ?: [],
            'devices'     => $devices ?: [],
            'bindings'    => [
                'cunkebao'   => (bool) $master,
                'touchkebao' => $s2Bound,
                'workphone'  => $s2Bound,
                'aiStaff'    => count($storeUsers) > 0 || count($subUsers) > 0,
            ],
            'loginUrls' => [
                'cunkebao'   => $endpoints['cunkebao'],
                'touchkebao' => $endpoints['touchkebao'],
                'superadmin' => $endpoints['superadmin'],
            ],
        ];
    }

    public function syncDevicesForCompany(int $companyId): int
    {
        $accountId = (int) Db::name('users')
            ->where('companyId', $companyId)
            ->where('isAdmin', UserModel::ADMIN_STP)
            ->where('deleteTime', 0)
            ->value('s2_accountId');

        if ($accountId <= 0) {
            throw new \Exception('项目未绑定工作手机账号 s2_accountId', 400);
        }

        $existing = Db::name('device')->where('companyId', $companyId)->where('deleteTime', 0)->column('id') ?: [0];
        $ids = implode(',', array_map('intval', $existing));

        $before = (int) Db::name('device')->where('companyId', $companyId)->where('deleteTime', 0)->count('id');

        $sql = "INSERT INTO ck_device(`id`, `imei`, `model`, phone, operatingSystem, memo, alive, brand, rooted, xPosed, softwareVersion, extra, createTime, updateTime, deleteTime, companyId)  
                SELECT 
                    d.id, d.imei, d.model, d.phone, d.operatingSystem, d.memo, d.alive, d.brand, d.rooted, d.xPosed, d.softwareVersion, d.extra, d.createTime, d.lastUpdateTime, d.deleteTime, a.departmentId AS companyId
                FROM s2_device d 
                    JOIN s2_company_account a ON d.currentAccountId = a.id 
                WHERE isDeleted = 0 AND deletedAndStop = 0 AND d.id NOT IN ({$ids}) AND a.departmentId = {$companyId}
                ON DUPLICATE KEY UPDATE 
                    imei = VALUES(imei), model = VALUES(model), phone = VALUES(phone),
                    operatingSystem = VALUES(operatingSystem), memo = VALUES(memo), alive = VALUES(alive),
                    brand = VALUES(brand), updateTime = VALUES(updateTime)";

        Db::query($sql);

        $after = (int) Db::name('device')->where('companyId', $companyId)->where('deleteTime', 0)->count('id');
        return max(0, $after - $before);
    }

    /**
     * 超管：从 s2_device 全量 upsert 到 ck_device（含各项目历史绑定过的设备）
     */
    public function syncAllDevicesFromS2(): array
    {
        $beforeActive = (int) Db::name('device')->where('deleteTime', 0)->count('id');
        $beforeAll = (int) Db::name('device')->count('id');

        // LEFT JOIN：无 currentAccountId 的设备也入库（companyId=0 进「未归属」）
        $sql = "INSERT INTO ck_device(`id`, `imei`, `model`, phone, operatingSystem, memo, alive, brand, rooted, xPosed, softwareVersion, extra, createTime, updateTime, deleteTime, companyId)  
                SELECT 
                    d.id, d.imei, d.model, d.phone, d.operatingSystem, d.memo, d.alive, d.brand, d.rooted, d.xPosed, d.softwareVersion, d.extra, d.createTime, d.lastUpdateTime, d.deleteTime,
                    COALESCE(NULLIF(a.departmentId, 0), 0) AS companyId
                FROM s2_device d 
                    LEFT JOIN s2_company_account a ON d.currentAccountId = a.id AND a.isDeleted = 0
                WHERE d.isDeleted = 0 AND d.deletedAndStop = 0
                ON DUPLICATE KEY UPDATE 
                    imei = VALUES(imei), model = VALUES(model), phone = VALUES(phone),
                    operatingSystem = VALUES(operatingSystem), memo = VALUES(memo), alive = VALUES(alive),
                    brand = VALUES(brand), updateTime = VALUES(updateTime), deleteTime = VALUES(deleteTime),
                    companyId = VALUES(companyId)";

        Db::query($sql);

        $afterActive = (int) Db::name('device')->where('deleteTime', 0)->count('id');
        $afterAll = (int) Db::name('device')->count('id');
        $archived = (int) Db::name('device')->where('deleteTime', '>', 0)->count('id');
        $s2Eligible = $this->countS2EligibleDevices();

        return [
            'synced'        => max(0, $afterActive - $beforeActive),
            'syncedAll'     => max(0, $afterAll - $beforeAll),
            'totalActive'   => $afterActive,
            'totalArchived' => $archived,
            'totalAll'      => $afterAll,
            's2Eligible'    => $s2Eligible,
        ];
    }

    protected function countS2EligibleDevices(): int
    {
        try {
            return (int) Db::table('s2_device')
                ->where('isDeleted', 0)
                ->where('deletedAndStop', 0)
                ->count('id');
        } catch (\Throwable $e) {
            return 0;
        }
    }

    public function listDevices(int $page, int $limit, int $companyId = 0, string $keyword = '', int $alive = -1, bool $groupByProject = false, bool $includeArchived = false): array
    {
        if ($groupByProject) {
            return $this->listDevicesGrouped($keyword, $alive, $includeArchived);
        }

        $query = Db::name('device')->alias('d')
            ->leftJoin('company c', 'c.companyId = d.companyId')
            ->where('d.deleteTime', 0);

        if ($companyId > 0) {
            $query->where('d.companyId', $companyId);
        }
        if ($alive >= 0) {
            $query->where('d.alive', $alive);
        }
        if ($keyword !== '') {
            $like = '%' . $keyword . '%';
            $query->where(function ($q) use ($like) {
                $q->whereLike('d.memo', $like)
                    ->whereOr('d.imei', 'like', $like)
                    ->whereOr('d.phone', 'like', $like)
                    ->whereOr('c.name', 'like', $like);
            });
        }

        $total = (clone $query)->count('d.id');
        $rows = $query
            ->field('d.id,d.memo,d.imei,d.phone,d.model,d.brand,d.alive,d.companyId,d.createTime,c.name as projectName,c.id as projectId')
            ->order('d.alive', 'desc')
            ->order('d.updateTime', 'desc')
            ->page($page, $limit)
            ->select();

        return [
            'list'  => $rows ?: [],
            'total' => $total,
            'page'  => $page,
            'limit' => $limit,
        ];
    }

    public function listDevicesGrouped(string $keyword = '', int $alive = -1, bool $includeArchived = false): array
    {
        $imeiDupes = $this->loadDuplicateImeiMap();

        $query = Db::name('device')->alias('d')
            ->leftJoin('company c', 'c.companyId = d.companyId AND c.deleteTime = 0');

        if (!$includeArchived) {
            $query->where('d.deleteTime', 0);
        }

        if ($alive >= 0) {
            $query->where('d.alive', $alive);
        }
        if ($keyword !== '') {
            $like = '%' . $keyword . '%';
            $query->where(function ($q) use ($like) {
                $q->whereLike('d.memo', $like)
                    ->whereOr('d.imei', 'like', $like)
                    ->whereOr('d.phone', 'like', $like)
                    ->whereOr('c.name', 'like', $like);
            });
        }

        $rows = $query
            ->field('d.id,d.memo,d.imei,d.phone,d.model,d.brand,d.alive,d.companyId,d.createTime,d.updateTime,d.deleteTime,d.extra,c.name as projectName,c.id as projectId')
            ->order('d.alive', 'desc')
            ->order('d.updateTime', 'desc')
            ->order('d.memo', 'asc')
            ->select();

        // §6.2 设备登录微信号（昵称/alias/avatar）
        $deviceIds = [];
        foreach ($rows ?: [] as $r) {
            $deviceIds[] = (int) (is_array($r) ? $r['id'] : $r['id']);
        }
        $wechatsMap = (new \app\common\service\WechatIdentityService())->deviceWechatsMap($deviceIds);

        $groups = [];
        $abnormalCount = 0;
        $unassignedCount = 0;

        foreach ($rows ?: [] as $row) {
            $row = is_array($row) ? $row : $row->toArray();
            $cid = (int) $row['companyId'];
            $groupKey = $cid > 0 ? $cid : 0;
            $geo = DeviceGeoHelper::parse($row['extra'] ?? '');
            $deviceTags = $this->computeDeviceTags($row, $imeiDupes);
            $isAbnormal = $this->isAbnormalDeviceTag($deviceTags);

            if ($cid <= 0) {
                $unassignedCount++;
            }
            if ($isAbnormal) {
                $abnormalCount++;
            }

            if (!isset($groups[$groupKey])) {
                $groups[$groupKey] = [
                    'companyId'    => $groupKey,
                    'projectId'    => $groupKey > 0 ? (int) ($row['projectId'] ?? 0) : 0,
                    'projectName'  => $groupKey > 0
                        ? ($row['projectName'] ?: "项目#{$groupKey}")
                        : '未归属',
                    'deviceTotal'  => 0,
                    'deviceOnline' => 0,
                    'devices'      => [],
                ];
            }

            $groups[$groupKey]['deviceTotal']++;
            if ((int) $row['alive'] === 1) {
                $groups[$groupKey]['deviceOnline']++;
            }

            $groups[$groupKey]['devices'][] = [
                'id'         => (int) $row['id'],
                'memo'       => $row['memo'] ?: '',
                'imei'       => $row['imei'] ?: '',
                'phone'      => $row['phone'] ?: '',
                'model'      => $row['model'] ?: '',
                'brand'      => $row['brand'] ?: '',
                'alive'      => (int) $row['alive'],
                'companyId'  => $cid,
                'createTime' => $this->formatTime($row['createTime']),
                'updateTime' => $this->formatTime($row['updateTime']),
                'location'   => $geo['location'] ?: '',
                'city'       => $geo['city'] ?: '',
                'province'   => $geo['province'] ?: '',
                'localTime'  => $geo['localTime'] ?: '',
                'deviceTags' => $deviceTags,
                'wechats'    => $wechatsMap[(int) $row['id']] ?? [],
            ];
        }

        $companyIds = array_values(array_filter(array_map(function ($g) {
            return (int) ($g['companyId'] ?? 0);
        }, $groups), function ($cid) {
            return $cid > 0;
        }));
        $classifier = new CompanyClassificationService();
        $metaMap = $classifier->buildMetaMap($companyIds);
        $tagLabels = CompanyClassificationService::tagLabels();

        foreach ($groups as $key => $g) {
            $cid = (int) $g['companyId'];
            if ($cid <= 0) {
                $groups[$key]['projectTags'] = [];
                $groups[$key]['primaryTag'] = '';
                $groups[$key]['isSandbox'] = false;
                continue;
            }
            $meta = $metaMap[$cid] ?? [];
            $tags = $meta['projectTags'] ?? [];
            $groups[$key]['projectTags'] = $tags;
            $groups[$key]['primaryTag'] = $meta['primaryTag'] ?? '';
            $groups[$key]['projectTagLabels'] = array_map(function ($t) use ($tagLabels) {
                return $tagLabels[$t] ?? $t;
            }, $tags);
            $groups[$key]['isSandbox'] = in_array(CompanyClassificationService::TAG_TEST, $tags, true)
                || in_array(CompanyClassificationService::TAG_LONG_IDLE, $tags, true)
                || in_array(CompanyClassificationService::TAG_EMPTY, $tags, true);
        }

        $groupList = array_values($groups);
        usort($groupList, function ($a, $b) {
            if (!empty($a['isSandbox']) && empty($b['isSandbox'])) {
                return 1;
            }
            if (empty($a['isSandbox']) && !empty($b['isSandbox'])) {
                return -1;
            }
            if ($a['companyId'] === 0) {
                return 1;
            }
            if ($b['companyId'] === 0) {
                return -1;
            }
            if ($b['deviceTotal'] !== $a['deviceTotal']) {
                return $b['deviceTotal'] <=> $a['deviceTotal'];
            }
            return $b['deviceOnline'] <=> $a['deviceOnline'];
        });

        $totalDevices = count($rows ?: []);
        $onlineDevices = 0;
        $offlineDevices = 0;
        $archivedDevices = 0;
        foreach ($rows ?: [] as $row) {
            $row = is_array($row) ? $row : $row->toArray();
            if ((int) ($row['deleteTime'] ?? 0) > 0) {
                $archivedDevices++;
            } elseif ((int) $row['alive'] === 1) {
                $onlineDevices++;
            } else {
                $offlineDevices++;
            }
        }

        return [
            'groups'  => $groupList,
            'summary' => [
                'total'          => $totalDevices,
                'online'         => $onlineDevices,
                'offline'        => $offlineDevices,
                'archived'       => $archivedDevices,
                's2Eligible'     => $this->countS2EligibleDevices(),
                'projects'       => count(array_filter($groupList, fn ($g) => $g['companyId'] > 0)),
                'abnormal'       => $abnormalCount,
                'unassigned'     => $unassignedCount,
                'multiBindImeis' => count($imeiDupes),
            ],
        ];
    }

    /**
     * @return array<string, int>
     */
    protected function loadDuplicateImeiMap(): array
    {
        $dupRows = Db::name('device')
            ->where('deleteTime', 0)
            ->where('imei', '<>', '')
            ->field('imei, COUNT(DISTINCT companyId) as bindCount')
            ->group('imei')
            ->having('bindCount > 1')
            ->select();

        $map = [];
        foreach ($dupRows ?: [] as $r) {
            $map[(string) $r['imei']] = (int) $r['bindCount'];
        }
        return $map;
    }

    /**
     * @param array<string, int> $imeiDupes
     * @return array<int, array{key:string,label:string,variant:string}>
     */
    protected function computeDeviceTags(array $row, array $imeiDupes): array
    {
        $tags = [];
        $imei = trim((string) ($row['imei'] ?? ''));
        $companyId = (int) ($row['companyId'] ?? 0);
        $alive = (int) ($row['alive'] ?? 0);
        $updateTime = (int) ($row['updateTime'] ?? 0);
        $deleteTime = (int) ($row['deleteTime'] ?? 0);
        $activeThreshold = time() - (7 * 86400);

        if ($deleteTime > 0) {
            $tags[] = ['key' => 'archived', 'label' => '已归档', 'variant' => 'secondary'];
        }
        if ($companyId <= 0) {
            $tags[] = ['key' => 'unassigned', 'label' => '未归属', 'variant' => 'destructive'];
        }
        if ($imei === '') {
            $tags[] = ['key' => 'no_imei', 'label' => '无IMEI', 'variant' => 'destructive'];
        }
        if ($imei !== '' && isset($imeiDupes[$imei])) {
            $tags[] = ['key' => 'multi_bind', 'label' => '多项目绑定', 'variant' => 'destructive'];
        }

        $hasAbnormal = !empty(array_intersect(
            array_column($tags, 'key'),
            ['unassigned', 'no_imei', 'multi_bind']
        ));

        if (!$hasAbnormal) {
            if ($alive === 1 || $updateTime >= $activeThreshold) {
                $tags[] = ['key' => 'active', 'label' => '现网', 'variant' => 'default'];
            } else {
                $tags[] = ['key' => 'historical', 'label' => '历史', 'variant' => 'secondary'];
            }
        }

        return $tags;
    }

    /**
     * @param array<int, array{key:string,label:string,variant:string}> $deviceTags
     */
    protected function isAbnormalDeviceTag(array $deviceTags): bool
    {
        foreach ($deviceTags as $tag) {
            if (in_array($tag['key'], ['unassigned', 'no_imei', 'multi_bind'], true)) {
                return true;
            }
        }
        return false;
    }

    /** 内容库高频调用阈值（>= 视为高频） */
    const CONTENT_HIGH_FREQ = 10;

    /**
     * 内容库标签
     *
     * @return array<int, array{key:string,label:string,variant:string}>
     */
    protected function computeLibraryTags(int $itemCount, int $callCount, int $status): array
    {
        $tags = [];
        if ($itemCount === 0) {
            $tags[] = ['key' => 'empty', 'label' => '空库', 'variant' => 'destructive'];
        }
        if ($callCount === 0) {
            $tags[] = ['key' => 'zero_call', 'label' => '零调用', 'variant' => 'secondary'];
        } elseif ($callCount >= self::CONTENT_HIGH_FREQ) {
            $tags[] = ['key' => 'high_freq', 'label' => '高频', 'variant' => 'default'];
        }
        if ($status !== 1) {
            $tags[] = ['key' => 'disabled', 'label' => '禁用', 'variant' => 'secondary'];
        }
        return $tags;
    }

    /** 全平台内容库 · 按项目分组 */
    public function listContentLibrariesGrouped(string $keyword = '', string $classification = ''): array
    {
        $query = Db::name('content_library')->alias('cl')
            ->leftJoin('company c', 'c.companyId = cl.companyId')
            ->where('cl.isDel', 0);

        if ($keyword !== '') {
            $like = '%' . $keyword . '%';
            $query->where(function ($q) use ($like) {
                $q->whereLike('cl.name', $like)->whereOr('c.name', 'like', $like);
            });
        }

        $rows = $query
            ->field('cl.id,cl.name,cl.status,cl.companyId,cl.createTime,c.name as projectName,c.id as projectId')
            ->order('cl.id', 'desc')
            ->select();

        $libraryIds = [];
        foreach ($rows ?: [] as $row) {
            $libraryIds[] = (int) $row['id'];
        }
        $itemCounts = [];
        $callStats = [];
        $assignedMap = (new ContentLibraryAssignService())->assignedCountMap($libraryIds);
        if (!empty($libraryIds)) {
            $counts = Db::name('content_item')
                ->field('libraryId, COUNT(*) as count')
                ->whereIn('libraryId', $libraryIds)
                ->where('isDel', 0)
                ->group('libraryId')
                ->select();
            foreach ($counts ?: [] as $c) {
                $itemCounts[(int) $c['libraryId']] = (int) $c['count'];
            }

            try {
                $calls = Db::name('content_library_external_log')
                    ->field('libraryId, COUNT(*) as callCount, MAX(createTime) as lastCallTime')
                    ->whereIn('libraryId', $libraryIds)
                    ->group('libraryId')
                    ->select();
                foreach ($calls ?: [] as $c) {
                    $callStats[(int) $c['libraryId']] = [
                        'callCount'    => (int) $c['callCount'],
                        'lastCallTime' => (int) $c['lastCallTime'],
                    ];
                }
            } catch (\Throwable $e) {
                // 迁移未执行时忽略
            }
        }

        $groups = [];
        foreach ($rows ?: [] as $row) {
            $cid = (int) $row['companyId'];
            if (!isset($groups[$cid])) {
                $groups[$cid] = [
                    'companyId'   => $cid,
                    'projectId'   => (int) ($row['projectId'] ?? 0),
                    'projectName' => $row['projectName'] ?: "项目#{$cid}",
                    'libTotal'    => 0,
                    'libEnabled'  => 0,
                    'libraries'   => [],
                ];
            }
            $groups[$cid]['libTotal']++;
            if ((int) $row['status'] === 1) {
                $groups[$cid]['libEnabled']++;
            }
            $libId = (int) $row['id'];
            $call = $callStats[$libId] ?? ['callCount' => 0, 'lastCallTime' => 0];
            $itemCount = $itemCounts[$libId] ?? 0;
            $callCount = (int) ($call['callCount'] ?? 0);
            $status = (int) $row['status'];
            $libTags = $this->computeLibraryTags($itemCount, $callCount, $status);
            $groups[$cid]['libraries'][] = [
                'id'           => $libId,
                'name'         => $row['name'],
                'status'       => $status,
                'itemCount'    => $itemCount,
                'callCount'    => $callCount,
                'lastCallTime' => !empty($call['lastCallTime'])
                    ? date('Y-m-d H:i:s', (int) $call['lastCallTime']) : '',
                'createTime'   => $this->formatTime($row['createTime']),
                'libTags'      => $libTags,
                'assignedCount' => $assignedMap[$libId] ?? 0,
            ];
        }

        // 分类筛选（库级标签命中即保留该库；组内无库则剔除该组）
        if ($classification !== '' && $classification !== 'all') {
            foreach ($groups as $cid => $group) {
                $kept = array_values(array_filter($group['libraries'], function ($lib) use ($classification) {
                    foreach ($lib['libTags'] as $t) {
                        if ($t['key'] === $classification) {
                            return true;
                        }
                    }
                    return false;
                }));
                if (empty($kept)) {
                    unset($groups[$cid]);
                } else {
                    $groups[$cid]['libraries'] = $kept;
                }
            }
        }

        // 组内按调用次数降序
        $zeroCallTotal = 0;
        $emptyLibTotal = 0;
        $highFreqTotal = 0;
        $libTotalAll = 0;
        foreach ($groups as $cid => $group) {
            usort($groups[$cid]['libraries'], fn ($a, $b) => $b['callCount'] <=> $a['callCount']);
            foreach ($groups[$cid]['libraries'] as $lib) {
                $libTotalAll++;
                if ($lib['callCount'] === 0) {
                    $zeroCallTotal++;
                }
                if ($lib['itemCount'] === 0) {
                    $emptyLibTotal++;
                }
                if ($lib['callCount'] >= self::CONTENT_HIGH_FREQ) {
                    $highFreqTotal++;
                }
            }
        }

        $groupList = array_values($groups);
        usort($groupList, fn ($a, $b) => $b['libTotal'] <=> $a['libTotal']);

        return [
            'groups'  => $groupList,
            'summary' => [
                'projects'  => count($groupList),
                'libraries' => $libTotalAll,
                'zeroCall'  => $zeroCallTotal,
                'emptyLib'  => $emptyLibTotal,
                'highFreq'  => $highFreqTotal,
            ],
        ];
    }

    protected function deviceStatsByCompany(array $companyIds): array
    {
        if (empty($companyIds)) {
            return [];
        }
        $rows = Db::name('device')
            ->whereIn('companyId', $companyIds)
            ->where('deleteTime', 0)
            ->field('companyId, COUNT(*) as total, SUM(alive=1) as online')
            ->group('companyId')
            ->select();

        $map = [];
        foreach ($rows as $row) {
            $map[(int) $row['companyId']] = [
                'total'  => (int) $row['total'],
                'online' => (int) $row['online'],
            ];
        }
        return $map;
    }

    protected function customerStatsByCompany(array $companyIds): array
    {
        if (empty($companyIds)) {
            return [];
        }
        $rows = Db::name('traffic_pool_company')->alias('tpc')
            ->whereIn('tpc.companyId', $companyIds)
            ->where('tpc.isDel', 0);
        TrafficPoolSystemIdentifierService::applyExcludeToCompanyQuery($rows, 'tpc');
        $rows = $rows->field('tpc.companyId, COUNT(*) as cnt')->group('tpc.companyId')->select();

        $map = [];
        foreach ($rows as $row) {
            $map[(int) $row['companyId']] = (int) $row['cnt'];
        }
        return $map;
    }

    protected function subUserStatsByCompany(array $companyIds): array
    {
        if (empty($companyIds)) {
            return [];
        }
        $masterMap = [];
        foreach ($companyIds as $cid) {
            $masterMap[(int) $cid] = ProjectContextService::primaryMasterUserId((int) $cid);
        }
        $rows = Db::name('users')
            ->whereIn('companyId', $companyIds)
            ->where('deleteTime', 0)
            ->field('companyId, id')
            ->select();

        $map = [];
        foreach ($companyIds as $cid) {
            $map[(int) $cid] = 0;
        }
        foreach ($rows ?: [] as $row) {
            $cid = (int) $row['companyId'];
            $masterId = $masterMap[$cid] ?? 0;
            if ($masterId > 0 && (int) $row['id'] === $masterId) {
                continue;
            }
            if (isset($map[$cid])) {
                $map[$cid]++;
            }
        }
        return $map;
    }

    protected function loginEndpoints(): array
    {
        return [
            'cunkebao'   => 'http://localhost:3100',
            'touchkebao' => 'http://localhost:3101',
            'superadmin' => 'http://localhost:3103',
        ];
    }

    protected function formatTime($value): string
    {
        if (empty($value)) {
            return '';
        }
        return is_numeric($value) ? date('Y-m-d H:i:s', (int) $value) : (string) $value;
    }
}