<?php


namespace App\Http\Service;


use App\Models\ActivityUser;
use App\Models\SystemConfig;
use App\Models\SystemTag;
use App\Models\User;
use App\Models\UserAction;
use Carbon\Carbon;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\DB;

class GroupDataService
{
    public function singerCount($ids)
    {
        $singerTags = explode(',', SystemConfig::query()
            ->where('identifier', 'auth_singer_tag')
            ->value('content') ?? '');
        return User::query()
            ->whereIn('id', $ids)
            ->whereHas('authTags', fn(Builder $builder) => $builder->whereIn('system_tags.id', $singerTags))
            ->count();

    }

    public function chatData($startDate, $currentDate, $query)
    {

        // 构建结果数组
        $result = [];
        $date   = $startDate;

        // 初始化结果数组，将日期和值都设置为0
        while ($date <= $currentDate) {
            $result[$date] = 0;
            $date          = Carbon::parse($date)->addDay()->toDateString();
        }

        // 将查询结果填充到结果数组中
        foreach ($query as $row) {
            $result[$row->date] = $row->value;
        }
        return [
            'time' => array_keys($result),
            'data' => array_values($result)
        ];
    }

    public function tableData($query)
    {
        // 将查询结果填充到结果数组中
        $result = [];
        foreach ($query as $row) {
            $item['id']        = $row->id;
            $item['nick_name'] = $row->nick_name;
            $item['value']     = $row->value;
            array_push($result, $item);
        }
        return $result;
    }

    public function listenChart($ids, $startDate, $currentDate, $tomorrowDate)
    {
        // 已听查询
        $query = UserAction::query()
            ->whereIn('event_name', ["listen_clip", "listen_entire"])
            ->whereIn('user_id', $ids)
            ->select(
                DB::raw('COUNT( DISTINCT CONCAT(user_id, DATE(created_at), activity_id)) as value'),
                DB::raw('DATE(created_at) as date'))
            ->whereBetween('created_at', [$startDate, $tomorrowDate])
            ->groupBy('date')
            ->orderBy('date', 'asc')
            ->get();

        $listenChart = $this->chatData($startDate, $currentDate, $query);
        return $listenChart;
    }

    public function listenTable($ids, $date, $nextDay)
    {
        $query       = UserAction::query()
            ->whereIn('event_name', ["listen_clip", "listen_entire"])
            ->whereIn('user_id', $ids)
//            ->select(DB::raw('COUNT(*) as value'),'users.nick_name','users.id')
            ->select(
                DB::raw('COUNT(DISTINCT  CONCAT(actions.user_id, DATE(actions.created_at), actions.activity_id)) as value'),
                'users.nick_name',
                'users.id'
            )
            ->whereBetween('actions.created_at', [$date, $nextDay])
            ->leftJoin('users', 'users.id', '=', 'actions.user_id')
            ->groupBy('users.nick_name', 'users.id')
            ->orderByRaw("FIELD(users.id, " . implode(',', $ids) . ")")
            ->get();
        $listenTable = $this->TableData($query);
        return $listenTable;

    }

    public function collectionChart($ids, $startDate, $currentDate, $tomorrowDate)
    {
        $query           = UserAction::query()
            ->where('event_name', 'song_like')
            ->whereIn('user_id', $ids)
//            ->select(DB::raw('COUNT(*) as value'), DB::raw('DATE(created_at) as date'))
            ->select(
                DB::raw('COUNT(DISTINCT CONCAT(user_id, DATE(created_at), activity_id)) as value'),
                DB::raw('DATE(created_at) as date'))
            ->whereBetween('created_at', [$startDate, $tomorrowDate])
            ->groupBy('date')
            ->orderBy('date', 'asc')
            ->get();
        $collectionChart = $this->chatData($startDate, $currentDate, $query);
        return $collectionChart;
    }

    public function collectionTable($ids, $date, $nextDay)
    {
        $query           = UserAction::query()
            ->where('event_name', 'song_like')
            ->whereIn('user_id', $ids)
            ->select(DB::raw('COUNT(DISTINCT CONCAT(actions.user_id, DATE(actions.created_at), actions.activity_id)) as value'), 'users.nick_name', 'users.id')
            ->whereBetween('actions.created_at', [$date, $nextDay])
            ->leftJoin('users', 'users.id', '=', 'actions.user_id')
            ->groupBy('users.nick_name', 'users.id')
            ->orderByRaw("FIELD(users.id, " . implode(',', $ids) . ")")
            ->get();
        $collectionTable = $this->TableData($query);
        return $collectionTable;

    }

    public function singChart($ids, $startDate, $currentDate, $tomorrowDate)
    {
        $query = UserAction::query()
            ->whereIn('event_name', ["submit_piece_online", "submit_sing_offline", "submit_sing_online"])
            ->whereIn('user_id', $ids)
//            ->select(DB::raw('COUNT(*) as value'), DB::raw('DATE(created_at) as date'))
            ->select(
                DB::raw('COUNT(DISTINCT CONCAT(user_id, DATE(created_at), activity_id)) as value'),
                DB::raw('DATE(created_at) as date'))
            ->whereBetween('created_at', [$startDate, $tomorrowDate])
            ->groupBy('date')
            ->orderBy('date', 'asc')
            ->get();

        $singChart = $this->chatData($startDate, $currentDate, $query);
        return $singChart;

    }

    public function singTable($ids, $date, $nextDay)
    {
        $query     = UserAction::query()
            ->whereIn('event_name', ["submit_piece_online", "submit_sing_offline", "submit_sing_online"])
            ->whereIn('user_id', $ids)
//            ->select(DB::raw('COUNT(*) as value'), 'users.nick_name','users.id')
            ->select(
                DB::raw('COUNT(DISTINCT CONCAT(actions.user_id, DATE(actions.created_at), actions.activity_id)) as value'),
                'users.nick_name', 'users.id')
            ->whereBetween('actions.created_at', [$date, $nextDay])
            ->leftJoin('users', 'users.id', '=', 'actions.user_id')
            ->groupBy('users.nick_name', 'users.id')
            ->orderByRaw("FIELD(users.id, " . implode(',', $ids) . ")")
            ->get();
        $singTable = $this->TableData($query);
        return $singTable;
    }

    public function cooperateChart($ids, $startDate, $currentDate, $tomorrowDate)
    {
        $query          = ActivityUser::query()
            ->join('activitys', 'activity_has_users.activity_id', '=', 'activitys.id')
            ->whereIn('activity_has_users.user_id', $ids)
            ->where(['activity_has_users.type' => 'Submit', 'activity_has_users.status' => 1])
            ->select(
                DB::raw('COUNT(*) as value'),
                DB::raw('DATE(activitys.match_at) as date'))
            ->whereBetween('activitys.match_at', [$startDate, $tomorrowDate])
            ->groupBy('date')
            ->orderBy('date', 'asc')
            ->get();
        $cooperateChart = $this->chatData($startDate, $currentDate, $query);
        return $cooperateChart;
    }

    public function cooperateTable($ids, $date, $nextDay)
    {
        $query          = ActivityUser::query()
            ->join('activitys', 'activity_has_users.activity_id', '=', 'activitys.id')
            ->whereIn('activity_has_users.user_id', $ids)
            ->where(['activity_has_users.type' => 'Submit', 'activity_has_users.status' => 1])
            ->select(DB::raw('COUNT(*) as value'), 'users.nick_name', 'users.id')
            ->whereBetween('activitys.match_at', [$date, $nextDay])
            ->leftJoin('users', 'users.id', '=', 'activity_has_users.user_id')
            ->groupBy('users.nick_name', 'users.id')
            ->orderByRaw("FIELD(users.id, " . implode(',', $ids) . ")")
            ->get();
        $cooperateTable = $this->TableData($query);
        return $cooperateTable;
    }

    public function overview($ids)
    {
        return [
            'member_count' => count($ids),
            'singer_count' => $this->singerCount($ids)
        ];
    }

    public function memberStatistics($ids)
    {
        $yesterday = Carbon::yesterday()->toDateString();
        //1.昨日活跃
        $login_ids = UserAction::query()
            ->whereIn('user_id',$ids)
            ->whereDate('created_at', $yesterday)
            ->distinct('user_id')
            ->pluck('user_id')
            ->toArray();
        //2.昨日试听
        $listen_ids = UserAction::query()
            ->whereIn('user_id', $ids)
            ->whereIn('event_name', ["listen_clip", "listen_entire"])
            ->whereDate('created_at', $yesterday)
            ->distinct()
            ->pluck('user_id')
            ->toArray();
        //3.昨日试唱
        $sing_ids = UserAction::query()
            ->whereIn('user_id', $ids)
            ->whereIn('event_name', ["submit_piece_online", "submit_sing_offline", "submit_sing_online"])
            ->whereDate('created_at', $yesterday)
            ->distinct()
            ->pluck('user_id')
            ->toArray();
        //4.昨日合作
        $cooperate_ids = ActivityUser::query()
            ->join('activitys', 'activity_has_users.activity_id', '=', 'activitys.id')
            ->whereDate('activitys.match_at', $yesterday)
            ->whereIn('activity_has_users.user_id', $ids)
            ->where(['activity_has_users.type' => 'Submit', 'activity_has_users.status' => 1])
            ->distinct()->pluck('activity_has_users.user_id')
            ->toArray();
        return [
            'login'     => [
                'member_count' => count($login_ids),
                'singer_count' => $this->singerCount($login_ids)
            ],
            'listen'    => [
                'member_count' => count($listen_ids),
                'singer_count' => $this->singerCount($listen_ids)
            ],
            'sing'      => [
                'member_count' => count($sing_ids),
                'singer_count' => $this->singerCount($sing_ids)
            ],
            'cooperate' => [
                'member_count' => count($cooperate_ids),
                'singer_count' => $this->singerCount($cooperate_ids)
            ],
        ];
    }

    public function auth($ids)
    {
        $tag_list  = SystemTag::query()
            ->where('type', 4)->select('id', 'name')
            ->orderBy('weight', 'desc')
            ->orderBy('created_at', 'desc')
            ->get();
        $auth_info = [];
        foreach ($tag_list as $tag) {
            $count = User::query()
                ->whereIn('id', $ids)
                ->whereHas('authTags', fn(Builder $builder) => $builder->whereIn('system_tags.id', [$tag->id]))
                ->count();
            if ($count) {
                $item['tag']   = $tag->name;
                $item['value'] = $count;
                array_push($auth_info, $item);
            }
        }
        return $auth_info;
    }

    public function trend($ids)
    {
        $currentDate  = Carbon::now()->toDateString();
        $tomorrowDate = Carbon::now()->subDays(-1)->toDateString();  //取值得完全包含今天的数据，所以取明天的开始时间
        $startDate    = Carbon::now()->subDays(6)->toDateString();
        //1.已听查询
        $listen = $this->listenChart($ids, $startDate, $currentDate, $tomorrowDate);

        // 2.收藏查询
        $collection = $this->collectionChart($ids, $startDate, $currentDate, $tomorrowDate);

        // 3.试唱查询
        $sing = $this->singChart($ids, $startDate, $currentDate, $tomorrowDate);

        // 4.合作查询
        $cooperate = $this->cooperateChart($ids, $startDate, $currentDate, $tomorrowDate);

        return [
            'listen'     => $listen,
            'collection' => $collection,
            'sing'       => $sing,
            'cooperate'  => $cooperate
        ];
    }
}