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

<?php

namespace app\superadmin\service;

use app\common\model\User as UserModel;
use app\common\service\TrafficPoolSystemIdentifierService;
use think\Db;
use think\facade\Cache;

/**
 * 项目闲置/运营分类（超管项目管理列表）
 */
class CompanyClassificationService
{
    const IDLE_DAYS = 90;

    const TAG_NORMAL = 'normal';
    const TAG_LONG_IDLE = 'long_idle';
    const TAG_NO_DEVICE = 'no_device';
    const TAG_NO_WECHAT = 'no_wechat';
    const TAG_EMPTY = 'empty';
    const TAG_TEST = 'test_sandbox';

    /** 项目名称命中则视为测试/沙箱（与 legacy consolidate 无关的展示归类） */
    const TEST_NAME_PATTERN = '/测试|test|demo|沙箱|试用|staging/i';

    public static function tagLabels(): array
    {
        return [
            self::TAG_NORMAL     => '正常运营',
            self::TAG_TEST       => '测试/沙箱',
            self::TAG_LONG_IDLE  => '长期未使用',
            self::TAG_NO_DEVICE  => '无设备',
            self::TAG_NO_WECHAT  => '无微信号',
            self::TAG_EMPTY      => '空项目',
        ];
    }

    public static function isTestProjectName(string $name): bool
    {
        return (bool) preg_match(self::TEST_NAME_PATTERN, $name);
    }

    /**
     * @return array<int, array>
     */
    public function buildMetaMap(array $companyIds): array
    {
        if (empty($companyIds)) {
            return [];
        }

        $deviceMap = $this->countMap('device', $companyIds, 'deleteTime', 0);
        $userMap = $this->countMap('users', $companyIds, 'deleteTime', 0);
        $contentMap = $this->countMap('content_library', $companyIds, 'isDel', 0, 'companyId');

        $wechatMap = [];
        $wq = Db::name('traffic_pool_company')->alias('tpc')
            ->whereIn('tpc.companyId', $companyIds)
            ->where('tpc.isDel', 0)
            ->where('tpc.ownerWechatId', '<>', '')
            ->whereNotNull('tpc.ownerWechatId');
        TrafficPoolSystemIdentifierService::applyExcludeToCompanyQuery($wq, 'tpc');
        $wrows = $wq->field('tpc.companyId, COUNT(DISTINCT tpc.ownerWechatId) as cnt')->group('tpc.companyId')->select();
        foreach ($wrows ?: [] as $r) {
            $wechatMap[(int) $r['companyId']] = (int) $r['cnt'] > 0;
        }

        $loginMap = Db::name('users')
            ->whereIn('companyId', $companyIds)
            ->where('deleteTime', 0)
            ->where('isAdmin', UserModel::ADMIN_STP)
            ->column('updateTime', 'companyId');

        $deviceActivity = Db::name('device')
            ->whereIn('companyId', $companyIds)
            ->where('deleteTime', 0)
            ->field('companyId, MAX(updateTime) as lastUpdate')
            ->group('companyId')
            ->select();
        $deviceActivityMap = [];
        foreach ($deviceActivity ?: [] as $r) {
            $deviceActivityMap[(int) $r['companyId']] = (int) $r['lastUpdate'];
        }

        $contentActivity = Db::name('content_library')
            ->whereIn('companyId', $companyIds)
            ->where('isDel', 0)
            ->field('companyId, MAX(updateTime) as lastUpdate')
            ->group('companyId')
            ->select();
        $contentActivityMap = [];
        foreach ($contentActivity ?: [] as $r) {
            $contentActivityMap[(int) $r['companyId']] = (int) $r['lastUpdate'];
        }

        $nameMap = [];
        if (!empty($companyIds)) {
            $nameRows = Db::name('company')
                ->whereIn('companyId', $companyIds)
                ->where('deleteTime', 0)
                ->column('name', 'companyId');
            $nameMap = $nameRows ?: [];
        }

        $map = [];
        foreach ($companyIds as $cid) {
            $cid = (int) $cid;
            $deviceCnt = (int) ($deviceMap[$cid] ?? 0);
            $userCnt = (int) ($userMap[$cid] ?? 0);
            $contentCnt = (int) ($contentMap[$cid] ?? 0);
            $hasWechat = !empty($wechatMap[$cid]);
            $lastActive = max(
                (int) ($loginMap[$cid] ?? 0),
                (int) ($deviceActivityMap[$cid] ?? 0),
                (int) ($contentActivityMap[$cid] ?? 0)
            );

            $projectName = (string) ($nameMap[$cid] ?? '');
            $tags = $this->computeTags($deviceCnt, $userCnt, $contentCnt, $hasWechat, $lastActive, $projectName);
            $map[$cid] = [
                'hasDevice'      => $deviceCnt > 0,
                'hasWechat'      => $hasWechat,
                'deviceCount'    => $deviceCnt,
                'userCount'      => $userCnt,
                'contentCount'   => $contentCnt,
                'lastActiveTime' => $lastActive,
                'projectTags'    => $tags,
                'primaryTag'     => $tags[0] ?? self::TAG_NORMAL,
            ];
        }

        return $map;
    }

    public function computeTags(int $deviceCnt, int $userCnt, int $contentCnt, bool $hasWechat, int $lastActive, string $projectName = ''): array
    {
        $tags = [];
        $idleThreshold = time() - (self::IDLE_DAYS * 86400);

        if ($projectName !== '' && self::isTestProjectName($projectName)) {
            $tags[] = self::TAG_TEST;
        }

        if ($deviceCnt === 0 && $userCnt === 0 && $contentCnt === 0) {
            $tags[] = self::TAG_EMPTY;
        }
        if ($deviceCnt === 0) {
            $tags[] = self::TAG_NO_DEVICE;
        }
        if (!$hasWechat) {
            $tags[] = self::TAG_NO_WECHAT;
        }
        if ($lastActive > 0 && $lastActive < $idleThreshold) {
            $tags[] = self::TAG_LONG_IDLE;
        } elseif ($lastActive === 0 && $deviceCnt === 0 && $userCnt <= 1) {
            $tags[] = self::TAG_LONG_IDLE;
        }

        if (empty($tags)) {
            $tags[] = self::TAG_NORMAL;
        }

        return array_values(array_unique($tags));
    }

    public function matchesTag(array $meta, string $tag): bool
    {
        if ($tag === '' || $tag === 'all') {
            return true;
        }
        return in_array($tag, $meta['projectTags'] ?? [], true);
    }

    /**
     * 全平台分类汇总（仅未删除项目）
     */
    public function summarizeAll(): array
    {
        $companyIds = Db::name('company')->where('deleteTime', 0)->column('companyId');
        $metaMap = $this->buildMetaMap($companyIds ?: []);
        $labels = self::tagLabels();
        $counts = array_fill_keys(array_keys($labels), 0);

        foreach ($metaMap as $meta) {
            foreach ($meta['projectTags'] as $t) {
                if (isset($counts[$t])) {
                    $counts[$t]++;
                }
            }
        }

        $list = [];
        foreach ($labels as $key => $label) {
            $list[] = ['tag' => $key, 'label' => $label, 'count' => $counts[$key] ?? 0];
        }

        return [
            'total'  => count($companyIds),
            'counts' => $list,
        ];
    }

    /**
     * 分类汇总缓存（REQ-SA-87）
     */
    public function summarizeAllCached(int $ttl = 300): array
    {
        $key = 'superadmin:company:classification:summary:v1';
        return Cache::remember($key, function () {
            return $this->summarizeAll();
        }, $ttl);
    }

    /**
     * 命中某分类标签的 companyId（缓存 5 分钟）
     *
     * @return int[]
     */
    public function companyIdsByTag(string $tag, int $ttl = 300): array
    {
        $tag = trim($tag);
        if ($tag === '' || $tag === 'all') {
            return [];
        }
        $key = 'superadmin:company:classification:ids:' . $tag . ':v1';
        return Cache::remember($key, function () use ($tag) {
            $companyIds = Db::name('company')->where('deleteTime', 0)->column('companyId');
            $metaMap = $this->buildMetaMap($companyIds ?: []);
            $ids = [];
            foreach ($metaMap as $cid => $meta) {
                if ($this->matchesTag($meta, $tag)) {
                    $ids[] = (int) $cid;
                }
            }
            return $ids;
        }, $ttl);
    }

    protected function countMap(string $table, array $companyIds, string $flagField, $flagValue, string $companyField = 'companyId'): array
    {
        $rows = Db::name($table)
            ->whereIn($companyField, $companyIds)
            ->where($flagField, $flagValue)
            ->field("{$companyField} as companyId, COUNT(*) as cnt")
            ->group($companyField)
            ->select();

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