<?php

namespace app\admin\controller\member;

use app\admin\service\member\MemberService;
use app\admin\validate\member\MemberValidate;
use app\model\Coupon;
use app\model\CouponDetail;
use app\model\Family;
use app\model\FamilyMember;
use app\model\Member;
use app\model\MemberAccount;
use app\model\MemberBenefit;
use app\model\MemberQuota;
use app\model\PayDetail;
use support\Db;
use support\Request;

class MemberController
{
    public function selectList(Request $request)
    {
        $keywords = $request->get('keywords');
        $isOwner = $request->get('member_is_owner', 'N');
        if (!$keywords) {
            return json_fail("查询错误");
        }

        $res = Db::table('member')
            ->leftjoin('member_cert', 'member.member_id', '=', 'member_cert.join_cert_member_id')
            ->leftjoin('member_info', 'member.member_id', '=', 'member_info.join_info_member_id')
            ->select('member.member_id', 'member.member_mobile', 'member_cert.member_cert_name', 'member_info.member_info_nickname')
            ->where('member.member_is_owner', $isOwner)
            ->where('member_status', 'ACTIVED')
            // ->where('member_mobile','<>','0000')
            ->where(function ($query) use ($keywords) {
                $query->where('member.member_mobile', 'like', '%' . $keywords . '%')
                    ->orWhere("member.member_id", 'like', '%' . $keywords . '%')
                    ->orWhere("member_cert.member_cert_name", 'like', '%' . $keywords . '%')
                    ->orWhere("member_info.member_info_nickname", 'like', '%' . $keywords . '%');
            })->get();
        $data = [];
        foreach ($res as $item) {
            // $item->member_mobile = substr($item->member_mobile,0,3).'****'.substr($item->member_mobile,7);
            $name = '';
            if (!empty($item->member_cert_name)) {
                $name = $item->member_cert_name;
            } else if (!empty($item->member_info_nickname)) {
                $name = $item->member_info_nickname;
            }
            $value = !empty($name) ? $name . '-' : '';
            if ($item->member_mobile != '0000') {
                $value .= $item->member_mobile;
            }
            if (empty($value)) {
                $value = $item->member_mobile;
            }
            $value = rtrim($value, '-');
            $data[] = [
                'key' => $item->member_id,
                'value' => $value,
            ];
        }

        return json_success('', $data);
    }

    public function exportMember(Request $request)
    {

        return MemberService::exportMember($request);
    }

    public function selectWriteOffMember()
    {
        $members = Member::with([
            'cert' => function ($query) {
                $query->select('join_cert_member_id', 'member_cert_name');
            }
        ])->whereRaw("JSON_EXTRACT(`member_json`, '$.user') IS NOT NULL")
            ->select('member_id', 'member_mobile', 'member_json')
            ->get()
            ->toArray();
        foreach ($members as &$member) {
            // $member['member_mobile'] = substr($member['member_mobile'],0,3).'****'.substr($member['member_mobile'],7);
            $member['member_json'] = json_decode($member['member_json'], true);
            $member['username'] = $member['member_mobile'];
            $member['user_id'] = $member['member_json']['user'] ? $member['member_json']['user']['user_id'] : '';
            if (isset($member['cert']['member_cert_name'])) {
                $member['username'] = $member['cert']['member_cert_name'] . '-' . $member['username'];
            }
        }

        return json_success('', $members);
    }

    public function selectFamilyMemberList(Request $request)
    {
        $keywords = $request->get('keywords');
        if (!$keywords) {
            return json_fail("查询错误");
        }
        // 创建的家庭
        $family = Family::where('join_family_creator_member_id', $request->get('memberId'))->get()->toArray();
        if (!$family) {
            return json_success('暂无数据');
        }
        $familyIds = array_column($family, 'family_id');

        $res = FamilyMember::whereIn('join_family_id', $familyIds)
            ->where("family_member_name", 'like', '%' . $keywords . '%')
            ->get()
            ->toArray();
        $data = [];
        foreach ($res as $item) {
            $data[] = [
                'key' => $item['join_family_member_id'],
                'value' => $item['family_member_name']
            ];
        }

        return json_success('', $data);
    }

    public function selectMemberAllQuota(Request $request)
    {
        $memberId = $request->get('member_id', '');
        $orderId = $request->get('order_id', '');
        $goodsId = $request->get('goods_id', '');
        $person = $request->get('person', '');
        $selectType = $request->get('select_type', 'ALL');

        if (!$memberId) {
            return json_fail('参数异常');
        }
        $data = [];

        $benefit = MemberBenefit::where('join_benefit_member_id', $memberId)
            ->whereColumn('member_benefit_limit_count', '>', 'member_benefit_used_count')
            ->select('member_benefit_id as id', 'member_benefit_name as name', 'member_benefit_limit_count as total', 'member_benefit_used_count as used')
            ->when($orderId != '', function ($query) use ($orderId) {
                $query->where('join_benefit_order_id', $orderId);
            })->when($goodsId != '', function ($query) use ($goodsId) {
                $query->where('join_benefit_goods_id', $goodsId);
            })
            ->get()
            ->toArray();

        foreach ($benefit as $item) {
            if ($item['total'] - $item['used'] < $person) {
                $item['disabled'] = true;
            }
            $item['cut'] = $item['total'] - $item['used'];
            $data[] = $item;
        }
        if ($selectType == 'BENEFIT') {
            return json_success('', $data);
        }

        $quotas = MemberQuota::where('join_quota_member_id', $memberId)
            ->when($goodsId != '', function ($query) use ($goodsId) {
                $query->whereJsonContains('member_quota_json', ['goods_id' => $goodsId]);
            })
            ->selectRaw('join_quota_member_id,join_member_rule_added_component_id,COUNT(*) as total')
            ->groupBy('join_quota_member_id', 'join_member_rule_added_component_id')
            ->get()
            ->toArray();
        foreach ($quotas as $quota) {
            $usedNum = MemberQuota::where('join_quota_member_id', $quota['join_quota_member_id'])
                ->where('join_member_rule_added_component_id', $quota['join_member_rule_added_component_id'])
                ->where('member_quota_status', 'USED')
                ->count('*');
            if ($quota['total'] <= $usedNum) {
                continue;
            }
            $memberQuota = MemberQuota::where('join_quota_member_id', $quota['join_quota_member_id'])
                ->where('join_member_rule_added_component_id', $quota['join_member_rule_added_component_id'])
                ->select('member_quota_id as id', 'member_quota_name as name')
                ->first()
                ->toArray();

            $memberQuota['total'] = $quota['total'];
            $memberQuota['used'] = $usedNum;
            if ($memberQuota['total'] - $memberQuota['used'] < $person) {
                $memberQuota['disabled'] = true;
            }
            $memberQuota['cut'] = $memberQuota['total'] - $memberQuota['used'];
            $data[] = $memberQuota;
        }

        if ($selectType == 'QUOTA') {
            return json_success('', $data);
        }

        return json_success('', $data);
    }

    /**
     * 预约核销用
     */
    public function selectMemberAllQuotaByAppointment(Request $request)
    {
        $memberId = $request->get('member_id', '');
        $orderId = $request->get('order_id', '');
        $goodsId = $request->get('goods_id', '');
        $person = $request->get('person', '');
        $selectType = $request->get('select_type', 'ALL');

        if (!$memberId) {
            return json_fail('参数异常');
        }
        $data = [];

        $benefit = MemberBenefit::where('join_benefit_member_id', $memberId)
            ->whereColumn('member_benefit_limit_count', '>', 'member_benefit_used_count')
            ->select('member_benefit_id as id', 'member_benefit_name as name', 'member_benefit_limit_count as total', 'member_benefit_used_count as used')
            ->when($orderId != '', function ($query) use ($orderId) {
                $query->where('join_benefit_order_id', $orderId);
            })->when($goodsId != '', function ($query) use ($goodsId) {
                $query->where('join_benefit_goods_id', $goodsId);
            })
            ->get()
            ->toArray();
        $data['benefit'] = [];
        foreach ($benefit as $item) {
            $item['benefit']['cut'] = $item['total'] - $item['used'];
            $data['benefit']['name'] = $item['name'];
            $data['benefit']['cut'] += $item['cut'];
            $data['benefit']['id'][] = $item['id'];
        }
        if ($selectType == 'BENEFIT') {
            return json_success('', $data);
        }

        $quotas = MemberQuota::where('join_quota_member_id', $memberId)
            ->when($goodsId != '', function ($query) use ($goodsId) {
                $query->whereJsonContains('member_quota_json', ['goods_id' => $goodsId]);
            })
            ->selectRaw('join_quota_member_id,join_member_rule_added_component_id,COUNT(*) as total')
            ->groupBy('join_quota_member_id', 'join_member_rule_added_component_id')
            ->get()
            ->toArray();

        $data['quota'] = [];
        foreach ($quotas as $quota) {
            $usedNum = MemberQuota::where('join_quota_member_id', $quota['join_quota_member_id'])
                ->where('join_member_rule_added_component_id', $quota['join_member_rule_added_component_id'])
                ->where('member_quota_status', 'USED')
                ->count('*');
            if ($quota['total'] <= $usedNum) {
                continue;
            }
            $memberQuota = MemberQuota::where('join_quota_member_id', $quota['join_quota_member_id'])
                ->where('join_member_rule_added_component_id', $quota['join_member_rule_added_component_id'])
                ->select('member_quota_id as id', 'member_quota_name as name')
                ->first()
                ->toArray();

            $memberQuota['total'] = $quota['total'];
            $memberQuota['used'] = $usedNum;
            if ($memberQuota['total'] - $memberQuota['used'] < $person) {
                $memberQuota['disabled'] = true;
            }
            $memberQuota['cut'] = $memberQuota['total'] - $memberQuota['used'];

            $data['quota']['name'] = $memberQuota['name'];
            $data['quota']['cut'] += $memberQuota['cut'];
            $data['quota']['id'][] = $memberQuota['id'];
            // $data[] = $memberQuota;
        }

        if ($selectType == 'QUOTA') {
            return json_success('', $data);
        }

        return json_success('', $data);
    }

    public function selectMemberAllQuotaByGoods(Request $request)
    {
        $memberId = $request->get('member_id', '');
        $orderId = $request->get('order_id', '');
        $person = $request->get('person', '');
        $selectType = $request->get('select_type', 'ALL');

        if (!$memberId) {
            return json_fail('参数异常');
        }
        $data = [];

        $benefit = MemberBenefit::where('join_benefit_member_id', $memberId)
            // ->whereColumn('member_benefit_limit_count','>','member_benefit_used_count')
            ->select('member_benefit_id as id', 'member_benefit_name as name', 'member_benefit_limit_count as total', 'member_benefit_used_count as used')
            ->when($orderId != '', function ($query) use ($orderId) {
                $query->where('join_benefit_order_id', $orderId);
            })
            ->get()
            ->toArray();


        foreach ($benefit as $item) {
            if ($item['total'] - $item['used'] < $person) {
                $item['disabled'] = true;
            }
            $item['cut'] = $item['total'] - $item['used'];
            $data[] = $item;
        }
        if ($selectType == 'BENEFIT') {
            return json_success('', $data);
        }

        $quotas = MemberQuota::where('join_quota_member_id', $memberId)
            ->selectRaw('join_quota_member_id,join_member_rule_added_component_id,COUNT(*) as total')
            ->groupBy('join_quota_member_id', 'join_member_rule_added_component_id')
            ->get()
            ->toArray();
        foreach ($quotas as $quota) {
            $usedNum = MemberQuota::where('join_quota_member_id', $quota['join_quota_member_id'])
                ->where('join_member_rule_added_component_id', $quota['join_member_rule_added_component_id'])
                ->where('member_quota_status', 'USED')
                ->count('*');
            if ($quota['total'] <= $usedNum) {
                continue;
            }
            $memberQuota = MemberQuota::where('join_quota_member_id', $quota['join_quota_member_id'])
                ->where('join_member_rule_added_component_id', $quota['join_member_rule_added_component_id'])
                ->select('member_quota_id as id', 'member_quota_name as name')
                ->first()
                ->toArray();

            $memberQuota['total'] = $quota['total'];
            $memberQuota['used'] = $usedNum;
            if ($memberQuota['total'] - $memberQuota['used'] < $person) {
                $memberQuota['disabled'] = true;
            }
            $memberQuota['cut'] = $memberQuota['total'] - $memberQuota['used'];
            $data[] = $memberQuota;
        }

        if ($selectType == 'QUOTA') {
            return json_success('', $data);
        }

        return json_success('', $data);
    }

    public function list(Request $request)
    {
        return MemberService::list($request);
    }

    public function info(Request $request)
    {
        $memberId = $request->get('member_id', '');
        if (!$memberId) {
            return json_fail("参数错误");
        }
        return MemberService::info($memberId);
    }

    public function vipInfo(Request $request)
    {
        $memberId = $request->get('member_id', '');
        if (!$memberId) {
            return json_fail("参数错误");
        }

        return MemberService::vipInfo($request);
    }

    public function welfareInfo(Request $request)
    {
        $memberId = $request->get('member_id');
        if (!$memberId) {
            return json_fail("参数错误");
        }

        return MemberService::welfareInfo($request);
    }

    public function commissionList(Request $request)
    {
        $memberId = $request->get('member_id');
        if (!$memberId) {
            return json_fail("参数错误");
        }

        return MemberService::commissionList($request);
    }

    /**
     * @Desc 我的粉丝列表
     * @Author Gorden
     * @Date 2024/9/20 10:22
     *
     * @param Request $request
     * @return \support\Response
     */
    public function fansList(Request $request)
    {
        $memberId = $request->get('member_id');
        if (!$memberId) {
            return json_fail("参数错误");
        }

        return MemberService::fansList($request);
    }

    public function clearWelfare(Request $request)
    {
        $memberId = $request->post('member_id', '');
        if (!$memberId) {
            return json_fail("参数错误");
        }

        return MemberService::clearWelfare($request);
    }

    public function balanceInfo(Request $request)
    {
        $memberId = $request->get('member_id', '');
        if (!$memberId) {
            return json_fail("参数错误");
        }

        return MemberService::balanceInfo($request);
    }

    public function pointInfo(Request $request)
    {
        $memberId = $request->get('member_id', '');
        if (!$memberId) {
            return json_fail("参数错误");
        }

        return MemberService::pointInfo($memberId);
    }

    /**
     * @Desc 添加会员
     * @Author Gorden
     * @Date 2024/3/25 13:40
     *
     * @param Request $request
     * @return \support\Response
     */
    public function add(Request $request)
    {
        $validate = new MemberValidate();
        if (!$validate->scene('add')->check($request->post())) {
            return json_fail($validate->getError());
        }

        _syslog("添加用户", "用户【" . $request->post('member_cert_name') . '】');

        return MemberService::add($request->post());
    }

    public function update(Request $request)
    {
        $validate = new MemberValidate();
        if (!$validate->scene('update')->check($request->post())) {
            return json_fail($validate->getError());
        }

        _syslog("编辑用户", "用户【" . $request->post('member_cert_name') ?? $request->post('member_id') . '】');

        return MemberService::update($request->post());
    }

    /**
     * @Desc 绑定的设备
     * @Author Gorden
     * @Date 2024/4/7 10:20
     *
     * @param $id
     * @return \support\Response
     */
    public function device($id)
    {
        return MemberService::deviceList($id);
    }

    public function addDevice(Request $request)
    {
        return MemberService::addDevice($request);
    }

    public function updateDevice(Request $request)
    {
        return MemberService::updateDevice($request);
    }

    public function deleteDevice(Request $request)
    {
        return MemberService::deleteDevice($request);
    }

    /**
     * @Desc 卡券数量
     * @Author Gorden
     * @Date 2024/8/22 18:51
     *
     * @param Request $request
     * @return \support\Response
     */
    public function couponCount(Request $request)
    {
        $memberId = $request->get('member_id', '');
        $coupons = CouponDetail::leftJoin('coupon', 'coupon.coupon_id', '=', 'coupon_detail.join_detail_coupon_id')
            ->where('join_coupon_detail_member_id', $memberId)
            ->select('coupon.coupon_name', 'coupon.coupon_classify','coupon.coupon_value')
            ->get()
            ->toArray();
        $data = ['manjian' => 0, 'diyong' => 0, 'zhekou' => 0, 'lijian' => 0, 'zengpin' => 0, 'fuli' => 0, 'nianka' => 0, 'jika' => 0, 'yueka' => 0];
        foreach ($coupons as $item) {
            if ($item['coupon_classify'] == '满减券') {
                $data['manjian'] += 1;
            } elseif ($item['coupon_classify'] == '抵用券') {
                $data['diyong'] += 1;
            } elseif ($item['coupon_classify'] == '折扣券') {
                $data['zhekou'] += 1;
            } elseif ($item['coupon_classify'] == '立减券') {
                $data['lijian'] += 1;
            } elseif ($item['coupon_classify'] == '赠品券') {
                $data['zengpin'] += 1;
            } elseif ($item['coupon_classify'] == '福利券') {
                $data['fuli'] += 1;
            } elseif ($item['coupon_classify'] == '年卡') {
                $data['nianka'] += 1;
            } elseif ($item['coupon_classify'] == '季卡') {
                $data['jika'] += 1;
            } elseif ($item['coupon_classify'] == '月卡') {
                $data['yueka'] += 1;
            }
        }
        $data['total'] = $data['manjian'] + $data['diyong'] + $data['zhekou'] + $data['lijian'] + $data['zengpin'] + $data['fuli'] + $data['nianka'] + $data['jika'] + $data['yueka'];

        return json_success('', $data);
    }

    /**
     * @Desc 我的卡券
     * @Author Gorden
     * @Date 2024/8/22 18:45
     *
     * @param Request $request
     * @return \support\Response
     */
    public function coupon(Request $request)
    {
        $memberId = $request->get('member_id', '');
        $classify = $request->get('classify', '');
        $page = $request->get('page', 1);
        $pageSize = $request->get('pageSize', 20);

        $coupons = CouponDetail::with([
            'member' => function ($query) {
                $query->select('member_id', 'member_mobile');
            },
            'cert' => function ($query) {
                $query->select('join_cert_member_id', 'member_cert_name');
            },
            'info' => function ($query) {
                $query->select('join_info_member_id', 'member_info_nickname');
            }
        ])
            ->leftJoin('coupon', 'coupon.coupon_id', '=', 'coupon_detail.join_detail_coupon_id')
            ->where('join_coupon_detail_member_id', $memberId)
            ->where('coupon.coupon_classify', $classify)
            ->groupBy('join_coupon_detail_member_id', 'join_detail_coupon_id');
//        $total = $coupons->count();
        $totalModel = Db::select("select count(a.join_detail_coupon_id) as total from (select join_detail_coupon_id from app_coupon_detail as d left join app_coupon as c ON d.join_detail_coupon_id = c.coupon_id where d.join_coupon_detail_member_id='" . $memberId . "' and c.coupon_classify='" . $classify . "' group by d.join_coupon_detail_member_id,d.join_detail_coupon_id) as a");
        $total = 0;
        if (!empty($totalModel) && !empty($totalModel[0])) {
            $total = $totalModel[0]->total;
        }
        $rows = $coupons->select('coupon_detail.join_coupon_detail_member_id', 'coupon_detail.join_detail_coupon_id'
            , 'coupon.coupon_name', 'coupon.coupon_classify', 'coupon.coupon_value')
            ->selectRaw('COUNT(1) as total, 
                COUNT(IF(app_coupon_detail.coupon_detail_status="USED",1,NULL)) as used_total, 
                COUNT(IF(app_coupon_detail.coupon_detail_status="ACTIVED" or app_coupon_detail.coupon_detail_status="WAITING",1,NULL)) as unused_total,
                MAX(app_coupon_detail.coupon_detail_deadline_datetime) as deadline_datetime')
            ->orderBy('deadline_datetime', 'DESC')
            ->forPage($page, $pageSize)
            ->get()
            ->toArray();

        foreach ($rows as &$item) {
            $mobile = $certName = $nickname = '';
            if (!empty($item['member']) && !empty($item['member']['member_mobile'])) {
                $mobile = $item['member']['member_mobile'];
            }
            if (!empty($item['cert']) && !empty($item['cert']['member_cert_name'])) {
                $certName = $item['cert']['member_cert_name'];
            }
            if (!empty($item['info']) && !empty($item['info']['member_info_nickname'])) {
                $nickname = $item['info']['member_info_nickname'];
            }
            $item['member_name'] = MemberService::getMemberName($mobile, $certName, $nickname);
            unset($item['member'], $item['cert'], $item['info']);

            $item['invalid_total'] = $item['total'] - $item['used_total'] - $item['unused_total'];
            // 优惠券分类
            $coupon = Coupon::with('category')->where('coupon_id', $item['join_detail_coupon_id'])
                ->select('coupon_id', 'join_coupon_category_id')
                ->first();
            if (!empty($coupon) && !empty($coupon->category)) {
                $item['category_name'] = $coupon->category->category_name;
            }
        }

        return json_success('', compact('rows', 'page', 'pageSize', 'total'));
    }

    /**
     * @Desc 我的卡券
     * @Author Gorden
     * @Date 2024/8/22 18:45
     *
     * @param Request $request
     * @return \support\Response
     */
    public function allCoupon(Request $request)
    {
        $memberId = $request->get('member_id', '');
        $page = $request->get('page', 1);
        $pageSize = $request->get('pageSize', 20);

        $coupons = CouponDetail::with([
            'member' => function ($query) {
                $query->select('member_id', 'member_mobile');
            },
            'cert' => function ($query) {
                $query->select('join_cert_member_id', 'member_cert_name');
            },
            'info' => function ($query) {
                $query->select('join_info_member_id', 'member_info_nickname');
            }
        ])
            ->leftJoin('coupon', 'coupon.coupon_id', '=', 'coupon_detail.join_detail_coupon_id')
            ->where('join_coupon_detail_member_id', $memberId)
            ->groupBy('join_coupon_detail_member_id', 'join_detail_coupon_id');
//        $total = $coupons->count();
        $totalModel = Db::select("select count(a.join_detail_coupon_id) as total from (select join_detail_coupon_id from app_coupon_detail as d left join app_coupon as c ON d.join_detail_coupon_id = c.coupon_id where d.join_coupon_detail_member_id='" . $memberId . "' group by d.join_coupon_detail_member_id,d.join_detail_coupon_id) as a");
        $total = 0;
        if (!empty($totalModel) && !empty($totalModel[0])) {
            $total = $totalModel[0]->total;
        }
        $rows = $coupons->select('coupon_detail.join_coupon_detail_member_id', 'coupon_detail.join_detail_coupon_id'
            , 'coupon.coupon_name', 'coupon.coupon_classify', 'coupon.coupon_value')
            ->selectRaw('COUNT(1) as total, 
                COUNT(IF(app_coupon_detail.coupon_detail_status="USED",1,NULL)) as used_total, 
                COUNT(IF(app_coupon_detail.coupon_detail_status="ACTIVED" or app_coupon_detail.coupon_detail_status="WAITING",1,NULL)) as unused_total,
                MAX(app_coupon_detail.coupon_detail_deadline_datetime) as deadline_datetime')
            ->orderBy('deadline_datetime', 'DESC')
            ->forPage($page, $pageSize)
            ->get()
            ->toArray();

        foreach ($rows as &$item) {
            $mobile = $certName = $nickname = '';
            if (!empty($item['member']) && !empty($item['member']['member_mobile'])) {
                $mobile = $item['member']['member_mobile'];
            }
            if (!empty($item['cert']) && !empty($item['cert']['member_cert_name'])) {
                $certName = $item['cert']['member_cert_name'];
            }
            if (!empty($item['info']) && !empty($item['info']['member_info_nickname'])) {
                $nickname = $item['info']['member_info_nickname'];
            }
            $item['member_name'] = MemberService::getMemberName($mobile, $certName, $nickname);
            unset($item['member'], $item['cert'], $item['info']);

            $item['invalid_total'] = $item['total'] - $item['used_total'] - $item['unused_total'];
            // 优惠券分类
            $coupon = Coupon::with('category')->where('coupon_id', $item['join_detail_coupon_id'])
                ->select('coupon_id', 'join_coupon_category_id')
                ->first();
            if (!empty($coupon) && !empty($coupon->category)) {
                $item['category_name'] = $coupon->category->category_name;
            }
        }

        return json_success('', compact('rows', 'page', 'pageSize', 'total'));
    }

    /**
     * @Desc 我的卡券 领取记录
     * @Author Gorden
     * @Date 2024/8/22 18:45
     *
     * @param Request $request
     * @return \support\Response
     */
    public function couponDetail(Request $request)
    {
        $memberId = $request->get('member_id', '');
        $couponId = $request->get('coupon_id', '');
        $page = $request->get('page', 1);
        $pageSize = $request->get('pageSize', 20);

        $coupons = CouponDetail::with([
            'member' => function ($query) {
                $query->select('member_id', 'member_mobile');
            },
            'cert' => function ($query) {
                $query->select('join_cert_member_id', 'member_cert_name');
            },
            'info' => function ($query) {
                $query->select('join_info_member_id', 'member_info_nickname');
            }
        ])
            ->leftJoin('coupon', 'coupon.coupon_id', '=', 'coupon_detail.join_detail_coupon_id')
            ->where('join_coupon_detail_member_id', $memberId)
            ->where('coupon.coupon_id', $couponId);
        $total = $coupons->count();
        $rows = $coupons->select('coupon_detail.*', 'coupon.coupon_name', 'coupon.coupon_classify', 'coupon.coupon_value')
            ->orderBy('coupon_detail_deadline_datetime', 'desc')
            ->forPage($page, $pageSize)
            ->get()
            ->toArray();

        foreach ($rows as &$item) {
            $mobile = $certName = $nickname = '';
            if (!empty($item['member']) && !empty($item['member']['member_mobile'])) {
                $mobile = $item['member']['member_mobile'];
            }
            if (!empty($item['cert']) && !empty($item['cert']['member_cert_name'])) {
                $certName = $item['cert']['member_cert_name'];
            }
            if (!empty($item['info']) && !empty($item['info']['member_info_nickname'])) {
                $nickname = $item['info']['member_info_nickname'];
            }
            $item['member_name'] = MemberService::getMemberName($mobile, $certName, $nickname);
            unset($item['member'], $item['cert'], $item['info']);
            if (!empty($coupon) && !empty($coupon->category)) {
                $item['category_name'] = $coupon->category->category_name;
            }
        }

        return json_success('', compact('rows', 'page', 'pageSize', 'total'));
    }
}