cunkebao_v3/Server/application/superadmin/controller/traffic/GetPoolListController.php

<?php

namespace app\superadmin\controller\traffic;

use app\common\service\TrafficPoolSystemIdentifierService;
use app\superadmin\controller\BaseController;
use library\ResponseHelper;
use think\Db;

/**
 * 超管客户池列表（V2 表，避免旧表 RIGHT JOIN 全表扫描）
 */
class GetPoolListController extends BaseController
{
    protected function formatGender($gender): string
    {
        switch ((int) $gender) {
            case 1:
                return '男';
            case 2:
                return '女';
            default:
                return '保密';
        }
    }

    protected function formatTimestamp($value): string
    {
        if ($value === null || $value === '') {
            return '';
        }
        if (is_numeric($value)) {
            return date('Y-m-d H:i:s', (int) $value);
        }
        return (string) $value;
    }

    protected function applyFilters($query)
    {
        $companyId = (int) $this->request->param('companyId/d', 0);
        $ownerWechatId = trim((string) $this->request->param('ownerWechatId/s', ''));
        $gender = trim((string) $this->request->param('gender/s', ''));

        if ($companyId > 0) {
            $query->where('tpc.companyId', $companyId);
        }

        if ($ownerWechatId !== '') {
            $query->where('tpc.ownerWechatId', $ownerWechatId);
        }

        if ($gender === '男') {
            $query->where('tp.gender', 1);
        } elseif ($gender === '女') {
            $query->where('tp.gender', 2);
        }

        return $query;
    }

    protected function buildQuery()
    {
        $keyword = trim((string) $this->request->param('keyword/s', ''));

        $query = Db::name('traffic_pool_company')->alias('tpc')
            ->join('traffic_pool tp', 'tp.id = tpc.poolId')
            ->leftJoin('company c', 'c.companyId = tpc.companyId AND c.deleteTime = 0')
            ->where('tpc.isDel', 0);

        TrafficPoolSystemIdentifierService::applyExcludeToPoolQuery($query);

        $this->applyFilters($query);

        if ($keyword !== '') {
            $like = '%' . $keyword . '%';
            $query->where(function ($q) use ($like, $keyword) {
                $q->whereLike('tp.nickname', $like)
                    ->whereOr('tp.wechatId', 'like', $like)
                    ->whereOr('tp.wechatAlias', 'like', $like)
                    ->whereOr('tp.mobile', 'like', $like)
                    ->whereOr('tpc.identifier', 'like', $like)
                    ->whereOr('tpc.realName', 'like', $like)
                    ->whereOr('tpc.phone', 'like', $like);
                if (ctype_digit($keyword)) {
                    $q->whereOr('tpc.id', (int) $keyword);
                }
            });
        }

        return $query;
    }

    protected function loadTagsMap(array $poolCompanyIds): array
    {
        if (empty($poolCompanyIds)) {
            return [];
        }

        $rows = Db::name('traffic_pool_tag')
            ->whereIn('poolCompanyId', $poolCompanyIds)
            ->where('isDel', 0)
            ->field('poolCompanyId, tagName')
            ->select();

        $map = [];
        foreach ($rows as $row) {
            $pid = (int) $row['poolCompanyId'];
            $map[$pid][] = $row['tagName'];
        }

        return $map;
    }

    protected function loadSourceMap(array $poolCompanyIds): array
    {
        if (empty($poolCompanyIds)) {
            return [];
        }

        $rows = Db::name('traffic_pool_source')
            ->whereIn('poolCompanyId', $poolCompanyIds)
            ->field('poolCompanyId, sourceName')
            ->order('id', 'desc')
            ->select();

        $map = [];
        foreach ($rows as $row) {
            $pid = (int) $row['poolCompanyId'];
            if (!isset($map[$pid])) {
                $map[$pid] = $row['sourceName'] ?: '';
            }
        }

        return $map;
    }

    protected function loadOwnerWechatMap(array $ownerWechatIds): array
    {
        // §6.2：优先 ck_wechat_account，回落 s2_wechat_account（减少匿名「未绑定」堆叠）
        $identity = (new \app\common\service\WechatIdentityService())->identityMap($ownerWechatIds);
        $map = [];
        foreach ($identity as $wid => $info) {
            $map[$wid] = ['wechatId' => $wid, 'nickname' => $info['nickname'] ?? '', 'alias' => $info['alias'] ?? ''];
        }
        return $map;
    }

    public function index()
    {
        $page = max(1, (int) $this->request->param('page/d', 1));
        $limit = min(100, max(1, (int) $this->request->param('limit/d', 20)));
        $keyword = trim((string) $this->request->param('keyword/s', ''));
        $gender = trim((string) $this->request->param('gender/s', ''));
        $companyId = (int) $this->request->param('companyId/d', 0);
        $allowGlobal = (int) $this->request->param('allowGlobal/d', 0) === 1;

        if ($companyId <= 0 && !$allowGlobal) {
            return ResponseHelper::error('请先选择项目后再加载客户池列表', 400);
        }

        $query = $this->buildQuery();

        // 无关键词且无性别筛选时，count 仅扫 traffic_pool_company（避免 JOIN 全表）
        if ($keyword === '' && $gender !== '男' && $gender !== '女') {
            $countQuery = Db::name('traffic_pool_company')->alias('tpc')
                ->where('tpc.isDel', 0);
            TrafficPoolSystemIdentifierService::applyExcludeToCompanyQuery($countQuery);
            $this->applyFilters($countQuery);
            $total = $countQuery->count('tpc.id');
        } else {
            $total = (clone $query)->count('tpc.id');
        }

        $rows = $query
            ->field([
                'tpc.id',
                'tpc.companyId',
                'tpc.identifier',
                'tpc.ownerWechatId',
                'tpc.createTime',
                'tp.nickname',
                'tp.wechatId',
                'tp.avatar',
                'tp.gender',
                'tp.region',
                'c.name as companyName',
            ])
            ->order('tpc.id', 'desc')
            ->page($page, $limit)
            ->select();

        $poolCompanyIds = array_column($rows, 'id');
        $ownerWechatMap = $this->loadOwnerWechatMap(array_column($rows, 'ownerWechatId'));
        $lite = (int) $this->request->param('lite/d', 1) === 1;
        $tagMap = $lite ? [] : $this->loadTagsMap($poolCompanyIds);
        $sourceMap = $lite ? [] : $this->loadSourceMap($poolCompanyIds);

        $list = [];
        foreach ($rows as $row) {
            $id = (int) $row['id'];
            $wa = $ownerWechatMap[$row['ownerWechatId'] ?? ''] ?? [];
            $ownerNickname = $wa['nickname'] ?? '';
            $ownerAlias = $wa['alias'] ?? '';
            $ownerDisplay = $ownerNickname ?: ($ownerAlias ?: ($row['ownerWechatId'] ?: '未绑定归属微信（待修复）'));
            if ($ownerNickname && $ownerAlias) {
                $ownerDisplay = $ownerNickname . ' · ' . $ownerAlias;
            }

            $list[] = [
                'id'                  => $id,
                'companyId'           => (int) ($row['companyId'] ?? 0),
                'nickname'            => $row['nickname'] ?: ($row['identifier'] ?: '未知'),
                'wechatId'            => $row['wechatId'] ?: $row['identifier'],
                'avatar'              => $row['avatar'] ?: '',
                'gender'              => $this->formatGender($row['gender'] ?? 0),
                'region'              => $row['region'] ?: '',
                'source'              => $sourceMap[$id] ?? '',
                'tags'                => $tagMap[$id] ?? [],
                'companyName'         => $row['companyName'] ?: '',
                'ownerWechatId'       => $row['ownerWechatId'] ?: '',
                'ownerWechatNickname' => $ownerNickname,
                'ownerWechatAlias'    => $ownerAlias,
                'ownerWechatDisplay'  => $ownerDisplay,
                'createTime'          => $this->formatTimestamp($row['createTime']),
                'mobile'              => 0,
            ];
        }

        return ResponseHelper::success([
            'list'  => $list,
            'total' => $total,
            'page'  => $page,
            'limit' => $limit,
        ]);
    }
}