mp_audio_manage/app/Services/PointsStatsService.php

<?php

namespace App\Services;

use App\Facade\Site;
use App\Libs\Utils;
use Illuminate\Support\Facades\DB;

class PointsStatsService
{
    /** group_by 合法维度 */
    private const GROUP_BY_DIMS = ['date', 'uid', 'model', 'relay'];

    /**
     * 解析 group_by 参数为合法维度列表；空/非法时回退全部维度，自动去重保序。
     */
    public static function parseGroupBy($groupBy = ''): array
    {
        if (!is_string($groupBy) || trim($groupBy) === '') {
            return self::GROUP_BY_DIMS;
        }
        $result = [];
        foreach (explode(',', $groupBy) as $part) {
            $part = trim($part);
            if (in_array($part, self::GROUP_BY_DIMS, true) && !in_array($part, $result, true)) {
                $result[] = $part;
            }
        }
        return $result ?: self::GROUP_BY_DIMS;
    }

    /**
     * 聚合指定日期明细到统计表（幂等：先删该日旧数据再批量插入）。
     *
     * @param string $date Y-m-d
     * @return int 写入行数
     */
    public function statsForDate(string $date): int
    {
        $start = $date . ' 00:00:00';
        $end   = $date . ' 23:59:59';

        // 模型名解析：charge_info.model 优先，空则 api_type，再空 unknown
        $modelExpr = "COALESCE(NULLIF(JSON_UNQUOTE(JSON_EXTRACT(d.charge_info, '$.model')), ''), NULLIF(d.api_type, ''), 'unknown')";

        $rows = DB::table('mp_user_points_details as d')
            ->leftJoin('mp_model_relay_map as m', 'm.model', '=', DB::raw($modelExpr))
            // cpid 以用户表当前所属组织为准（明细历史数据存在同 uid 不同 cpid 的脏数据）
            ->leftJoin('mp_manage_users as u', 'u.id', '=', 'd.uid')
            ->whereIn('d.type', ['chat', 'image', 'video'])
            ->where('d.created_at', '>=', $start)
            ->where('d.created_at', '<=', $end)
            ->selectRaw("DATE(d.created_at) AS stat_date, d.uid, COALESCE(u.cpid, 0) AS cpid, {$modelExpr} AS model, COALESCE(m.relay_name, 'unknown') AS relay, COALESCE(m.model_type, d.type) AS model_type, COUNT(*) AS call_count, SUM(d.tokens_consumed) AS tokens_consumed, SUM(d.points_consumed) AS points_consumed")
            ->groupByRaw("DATE(d.created_at), d.uid, {$modelExpr}, COALESCE(m.relay_name, 'unknown'), COALESCE(m.model_type, d.type)")
            ->get()
            ->map(function ($row) use ($date) {
                return [
                    'stat_date'       => $date,
                    'uid'             => (int)$row->uid,
                    'cpid'            => (int)$row->cpid,
                    'model'           => (string)$row->model,
                    'relay'           => (string)$row->relay,
                    'model_type'      => (string)$row->model_type,
                    'call_count'      => (int)$row->call_count,
                    'tokens_consumed' => (int)$row->tokens_consumed,
                    'points_consumed' => (float)$row->points_consumed,
                    'created_at'      => date('Y-m-d H:i:s'),
                    'updated_at'      => date('Y-m-d H:i:s'),
                ];
            })
            ->all();

        DB::transaction(function () use ($date, $rows) {
            DB::table('mp_points_daily_stats')->where('stat_date', $date)->delete();
            if ($rows) {
                DB::table('mp_points_daily_stats')->insert($rows);
            }
        });

        return count($rows);
    }

    /**
     * 当前角色可查看的统计范围：superadmin 全部；admin 本组织；user 仅自己；其他抛错。
     *
     * @return array ['cpid' => int 组织限定(0 不限定), 'uid' => int 用户限定(0 不限定)]
     */
    private function assertCanView(): array
    {
        $role = (string)Site::getRole();
        if ($role === 'superadmin') {
            return ['cpid' => 0, 'uid' => 0];
        }
        if ($role === 'admin') {
            return ['cpid' => (int)Site::getCpid(), 'uid' => 0];
        }
        if ($role === 'user') {
            return ['cpid' => (int)Site::getCpid(), 'uid' => (int)Site::getUid()];
        }
        Utils::throwError('1005:无权查看统计数据');
    }

    /**
     * 构建统计查询（含权限过滤与 group_by 聚合）。
     *
     * @param array $params  筛选参数
     * @param array $groupBy 聚合维度列表
     * @return array [查询构建器, 维度=>列映射]
     */
    private function buildStatsQuery(array $params, array $groupBy)
    {
        $scope = $this->assertCanView();

        $query = DB::table('mp_points_daily_stats as s')
            ->leftJoin('mp_manage_users as u', 'u.id', '=', 's.uid');

        if ($scope['cpid'] > 0) {
            $query->where('s.cpid', $scope['cpid']);
        }
        if ($scope['uid'] > 0) {
            $query->where('s.uid', $scope['uid']);
        }

        $startDate = (string)getProp($params, 'start_date', date('Y-m-d', strtotime('-29 days')));
        $endDate = (string)getProp($params, 'end_date', date('Y-m-d'));
        if ($startDate) {
            $query->where('s.stat_date', '>=', $startDate);
        }
        if ($endDate) {
            $query->where('s.stat_date', '<=', $endDate);
        }

        $uid = (string)getProp($params, 'uid', '');
        // 普通用户仅能查看自己，忽略传入的 uid 筛选
        if ($uid !== '' && $scope['uid'] <= 0) {
            $uids = array_values(array_filter(array_map('intval', explode(',', $uid))));
            if ($uids) {
                $query->whereIn('s.uid', $uids);
            }
        }

        $nickname = trim((string)getProp($params, 'nickname', ''));
        if ($nickname !== '') {
            $query->where(function ($q) use ($nickname) {
                $q->where('u.nickname', 'like', '%' . $nickname . '%')
                    ->orWhere('u.account', 'like', '%' . $nickname . '%');
            });
        }

        $model = (string)getProp($params, 'model', '');
        if ($model !== '') {
            $query->where('s.model', $model);
        }

        $relay = (string)getProp($params, 'relay', '');
        if ($relay !== '') {
            $query->where('s.relay', $relay);
        }

        $modelType = (string)getProp($params, 'model_type', '');
        if (in_array($modelType, ['chat', 'image', 'video'], true)) {
            $query->where('s.model_type', $modelType);
        }

        $dimToCol = [
            'date'  => 's.stat_date',
            'uid'   => 's.uid',
            'model' => 's.model',
            'relay' => 's.relay',
        ];

        $cols = [];
        $groupRaw = [];
        foreach ($groupBy as $dim) {
            $cols[] = $dimToCol[$dim] . ' AS dim_' . $dim;
            $groupRaw[] = $dimToCol[$dim];
        }
        // 按 model 分组时 model_type 有确定值（模型→类型一对一），随分组输出
        if (in_array('model', $groupBy, true)) {
            $cols[] = 's.model_type AS dim_model_type';
            $groupRaw[] = 's.model_type';
        }
        $cols[] = 'SUM(s.call_count) AS call_count';
        $cols[] = 'SUM(s.tokens_consumed) AS tokens_consumed';
        $cols[] = 'SUM(s.points_consumed) AS points_consumed';

        $query->selectRaw(implode(', ', $cols));
        if ($groupRaw) {
            $query->groupByRaw(implode(', ', $groupRaw));
        }

        return [$query, $dimToCol];
    }

    /**
     * 查询统计列表（分页）。
     */
    public function getStats(array $params): array
    {
        $groupBy = self::parseGroupBy(getProp($params, 'group_by', ''));
        [$query] = $this->buildStatsQuery($params, $groupBy);

        $pageSize = (int)getProp($params, 'page_size', 15);
        if ($pageSize < 1 || $pageSize > 100) {
            $pageSize = 15;
        }

        // 按日期分组时日期倒序展示
        if (in_array('date', $groupBy, true)) {
            $query->orderByDesc('s.stat_date');
        }

        $rows = $query->paginate($pageSize)->through(function ($row) use ($groupBy) {
            $item = [];
            foreach ($groupBy as $dim) {
                $item[$dim] = $dim === 'uid' ? (int)$row->{'dim_' . $dim} : (string)$row->{'dim_' . $dim};
            }
            $item['call_count'] = (int)$row->call_count;
            $item['tokens_consumed'] = (int)$row->tokens_consumed;
            $item['points_consumed'] = (float)$row->points_consumed;
            $item['model_type'] = (string)($row->dim_model_type ?? '');
            return $item;
        });

        $list = $rows->items();
        if (in_array('uid', $groupBy, true) && $list) {
            $uids = array_unique(array_column($list, 'uid'));
            $userMap = DB::table('mp_manage_users')->whereIn('id', $uids)
                ->pluck('nickname', 'id')->map(function ($v) {
                    return (string)$v;
                })->all();
            foreach ($list as &$item) {
                $item['nickname'] = $userMap[$item['uid']] ?? '';
            }
            unset($item);
        }

        return [
            'summary' => $this->getSummary($params),
            'meta'    => getMeta($rows),
            'list'    => $list,
        ];
    }

    /**
     * 当前筛选条件下的合计（不按维度分组）。
     */
    public function getSummary(array $params): array
    {
        [$query] = $this->buildStatsQuery($params, []);
        $row = $query->first();
        return [
            'call_count'      => (int)($row->call_count ?? 0),
            'tokens_consumed' => (int)($row->tokens_consumed ?? 0),
            'points_consumed' => (float)($row->points_consumed ?? 0),
        ];
    }

    /**
     * 筛选项来源：用户/模型/中转站/模型类型/日期范围。
     *
     * 选项统一为 {name: 显示名称, value: 传给后端的筛选值}，前端展示 name、提交 value。
     */
    public function getFilters(array $params = []): array
    {
        $scope = $this->assertCanView();

        $users = DB::table('mp_manage_users')
            ->when($scope['cpid'] > 0, function ($q) use ($scope) {
                $q->where('cpid', $scope['cpid']);
            })
            ->when($scope['uid'] > 0, function ($q) use ($scope) {
                $q->where('id', $scope['uid']);
            })
            ->where('is_enabled', 1)
            ->where('is_deleted', 0)
            ->select('id', 'nickname', 'account')
            ->orderBy('id')
            ->get()
            ->map(function ($u) {
                $name = (string)$u->nickname;
                if ($name === '') {
                    $name = (string)$u->account;
                }
                return [
                    'name'  => $name,
                    'value' => (int)$u->id,
                ];
            })->all();

        $statsQuery = DB::table('mp_points_daily_stats as s');
        if ($scope['cpid'] > 0) {
            $statsQuery->where('s.cpid', $scope['cpid']);
        }
        if ($scope['uid'] > 0) {
            $statsQuery->where('s.uid', $scope['uid']);
        }
        $models = (clone $statsQuery)->distinct()->orderBy('s.model')->pluck('s.model')->values()->map(function ($model) {
            return ['name' => (string)$model, 'value' => (string)$model];
        })->all();
        $relays = (clone $statsQuery)->distinct()->orderBy('s.relay')->pluck('s.relay')->values()->map(function ($relay) {
            return ['name' => (string)$relay, 'value' => (string)$relay];
        })->all();
        $modelTypes = (clone $statsQuery)->distinct()->pluck('s.model_type')->values()->map(function ($type) {
            $labels = ['chat' => '文本', 'image' => '图片', 'video' => '视频'];
            return [
                'name'  => $labels[$type] ?? (string)$type,
                'value' => (string)$type,
            ];
        })->all();
        // $dateRange = (clone $statsQuery)->selectRaw('MIN(stat_date) AS min_date, MAX(stat_date) AS max_date')->first();

        return [
            'users'       => $users,
            'models'      => $models,
            'relays'      => $relays,
            'model_types' => $modelTypes,
            // 'date_range'  => [
            //     'min' => $dateRange->min_date ?? null,
            //     'max' => $dateRange->max_date ?? null,
            // ],
        ];
    }

    /**
     * 导出 CSV：列随 group_by 动态变化，末尾追加合计行。
     */
    public function exportStats(array $params): void
    {
        $groupBy = self::parseGroupBy(getProp($params, 'group_by', ''));
        [$query] = $this->buildStatsQuery($params, $groupBy);

        // 按日期分组时日期倒序导出，与列表顺序一致
        if (in_array('date', $groupBy, true)) {
            $query->orderByDesc('s.stat_date');
        }

        $rows = $query->get();

        $dimLabels = [
            'date'  => '统计日期',
            'uid'   => '用户ID',
            'model' => '模型',
            'relay' => '中转站',
        ];
        $headers = [];
        foreach ($groupBy as $dim) {
            $headers[] = $dimLabels[$dim];
        }
        if (in_array('model', $groupBy, true)) {
            $headers[] = '模型类型';
        }
        $headers[] = '调用次数';
        $headers[] = 'token消耗';
        $headers[] = '积分消耗';

        $csvRows = [];
        foreach ($rows as $row) {
            $line = [];
            foreach ($groupBy as $dim) {
                $line[] = $dim === 'uid' ? (string)(int)$row->{'dim_' . $dim} : (string)$row->{'dim_' . $dim};
            }
            if (in_array('model', $groupBy, true)) {
                $line[] = (string)($row->dim_model_type ?? '');
            }
            $line[] = (string)(int)$row->call_count;
            $line[] = (string)(int)$row->tokens_consumed;
            $line[] = number_format((float)$row->points_consumed, 1, '.', '');
            $csvRows[] = $line;
        }

        $summary = $this->getSummary($params);
        $totalRow = [];
        foreach ($groupBy as $index => $dim) {
            $totalRow[] = $index === 0 ? '合计' : '';
        }
        if (in_array('model', $groupBy, true)) {
            $totalRow[] = '';
        }
        $csvRows[] = array_merge($totalRow, [
            (string)$summary['call_count'],
            (string)$summary['tokens_consumed'],
            number_format($summary['points_consumed'], 1, '.', ''),
        ]);

        $startDate = (string)getProp($params, 'start_date', date('Y-m-d', strtotime('-29 days')));
        $endDate = (string)getProp($params, 'end_date', date('Y-m-d'));
        $filename = 'points_stats_' . str_replace('-', '', $startDate) . '_' . str_replace('-', '', $endDate);

        exportCsv($filename, $headers, $csvRows);
    }
}