<?php

namespace app\admin\controller;
use app\admin\model\OrderGoods;
use app\admin\model\OrderRecharge;
use app\admin\model\OrderYearcard;
use app\admin\model\User;
use app\common\server\UrlServer;
use think\Db;
use think\facade\Url;
use app\admin\model\Order;

class BulletinBoard extends AdminBase
{

    //看板统计
    public function index()
    {
        // 本月与上月时间范围
        $monthStart      = date('Y-m-01 00:00:00');
        $monthEnd        = date('Y-m-t 23:59:59');
        $lastMonthStart  = date('Y-m-01 00:00:00', strtotime('-1 month'));
        $lastMonthEnd    = date('Y-m-t 23:59:59', strtotime('-1 month'));
        $monthStartTs    = strtotime($monthStart);
        $monthEndTs      = strtotime($monthEnd);

        //查询商品订单
        $orderMoney = Order::where('status','<>', 0)->sum('order_amount');
        //年卡订单
        $yearCardOrderMoney = OrderYearcard::where('status', 1)->sum('pay_fee');
        //订单管理
        $orderGoods = OrderGoods::where('status', 1)->sum('total_fee');
        //充值订单
        $rechargeOrderMoney = OrderRecharge::where('pay_status', 1)->sum('order_amount');
        
        // 计算月销售总额（所有订单类型的总和）
        $monthlySales = ($orderMoney ?: 0) + ($yearCardOrderMoney ?: 0) + ($orderGoods ?: 0) + ($rechargeOrderMoney ?: 0);
        
        // 计算月订单量（本月订单数量）
        $monthlyOrders = Order::where('status','<>', 0)
            ->whereTime('create_time', 'month')
            ->count();
        $monthlyOrders += OrderYearcard::where('status', 1)
            ->whereTime('createtime', 'month')
            ->count();
        $monthlyOrders += OrderGoods::where('status', 1)
            ->whereTime('createtime', 'month')
            ->count();
        $monthlyOrders += OrderRecharge::where('pay_status', 1)
            ->whereTime('create_time', 'month')
            ->count();
        
        // 计算月客户数（本月新增用户）
        $monthlyCustomers = User::whereTime('create_time', 'month')
            ->where('del', 0)
            ->count();
        
        // 计算同比（上个月的数据）
        $lastMonthSales = Order::where('status','<>', 0)
            ->whereTime('create_time', 'last month')
            ->sum('order_amount');
        $lastMonthSales += OrderYearcard::where('status', 1)
            ->whereTime('createtime', 'last month')
            ->sum('pay_fee');
        $lastMonthSales += OrderGoods::where('status', 1)
            ->whereTime('createtime', 'last month')
            ->sum('total_fee');
        $lastMonthSales += OrderRecharge::where('pay_status', 1)
            ->whereTime('create_time', 'last month')
            ->sum('order_amount');
        
        $lastMonthOrders = Order::where('status','<>', 0)
            ->whereTime('create_time', 'last month')
            ->count();
        $lastMonthOrders += OrderYearcard::where('status', 1)
            ->whereTime('createtime', 'last month')
            ->count();
        $lastMonthOrders += OrderGoods::where('status', 1)
            ->whereTime('createtime', 'last month')
            ->count();
        $lastMonthOrders += OrderRecharge::where('pay_status', 1)
            ->whereTime('create_time', 'last month')
            ->count();
        
        $lastMonthCustomers = User::whereTime('create_time', 'last month')
            ->where('del', 0)
            ->count();
        
        // 计算同比变化率
        $salesCompare = $lastMonthSales > 0 ? round((($monthlySales - $lastMonthSales) / $lastMonthSales) * 100, 2) : 0;
        $ordersCompare = $lastMonthOrders > 0 ? round((($monthlyOrders - $lastMonthOrders) / $lastMonthOrders) * 100, 2) : 0;
        $customersCompare = $lastMonthCustomers > 0 ? round((($monthlyCustomers - $lastMonthCustomers) / $lastMonthCustomers) * 100, 2) : 0;
        
        // 统计本月客户分析（老客户 vs 新客户）
        // 本月所有用户ID列表
        $monthUsers = User::whereTime('create_time', 'month')
            ->where('del', 0)
            ->column('id');
        
        // 如果本月有用户，查询这些用户中哪些有订单（老客户）
        $oldCustomerCount = 0;
        $newCustomerCount = 0;
        if (!empty($monthUsers)) {
            // 查询本月注册的用户中，在Order表中有订单的用户（老客户）
            // 只要有订单就算老客户，不限制订单时间
            $oldCustomerIds = Order::where('status','<>', 0)
                ->where('user_id', 'in', $monthUsers)
                ->where('user_id', '>', 0)
                ->distinct(true)
                ->column('user_id');
            
            $oldCustomerCount = count($oldCustomerIds);
            // 本月注册但没下过单的（新客户）
            $newCustomerCount = count($monthUsers) - $oldCustomerCount;
        }

        // ========== 目标完成率与客服进度 ==========
        // 销售目标（可以后续做成后台可配置，这里先写死为 100000）
        $monthlyTarget = 100000;
        $overallCompletion = $monthlyTarget > 0 ? round(($monthlySales / $monthlyTarget) * 100, 2) : 0;

        // 按客服统计本月与上月销售额
        $staffCurrent = Db::name('order')
            ->alias('o')
            ->join('admin a', 'o.admin_id = a.id', 'LEFT')
            ->where('o.status', '<>', 0)
            ->whereTime('o.create_time', 'between', [$monthStart, $monthEnd])
            ->field('o.admin_id,a.name as staff_name,SUM(o.order_amount) as total')
            ->group('o.admin_id')
            ->select();

        $staffLast = Db::name('order')
            ->alias('o')
            ->join('admin a', 'o.admin_id = a.id', 'LEFT')
            ->where('o.status', '<>', 0)
            ->whereTime('o.create_time', 'between', [$lastMonthStart, $lastMonthEnd])
            ->field('o.admin_id,SUM(o.order_amount) as total')
            ->group('o.admin_id')
            ->select();

        $staffLastMap = [];
        foreach ($staffLast as $row) {
            $staffLastMap[$row['admin_id']] = $row['total'];
        }

        $staffProgress = [];
        foreach ($staffCurrent as $row) {
            $adminId     = $row['admin_id'];
            $staffSales  = (float)$row['total'];
            $lastSales   = isset($staffLastMap[$adminId]) ? (float)$staffLastMap[$adminId] : 0;
            $percent     = $monthlySales > 0 ? round(($staffSales / $monthlySales) * 100, 2) : 0;
            $yoy         = $lastSales > 0 ? round((($staffSales - $lastSales) / $lastSales) * 100, 2) : 0;

            $staffProgress[] = [
                'name'     => $row['staff_name'] ?: '未分配',
                'sales'    => $staffSales,
                'percent'  => $percent,
                'yoy'      => $yoy,
            ];
        }

        // ========== 渠道分析（当月订单数按渠道） ==========
        $channelRows = Db::name('order')
            ->alias('o')
            ->join('orderchannel c', 'o.channel_id = c.id', 'LEFT')
            ->where('o.status', '<>', 0)
            ->whereTime('o.create_time', 'month')
            ->field('c.name as channel_name, COUNT(*) as total')
            ->group('o.channel_id')
            ->select();

        $channelPie = [];
        foreach ($channelRows as $row) {
            $name  = $row['channel_name'] ?: '其他';
            $value = (int)$row['total'];
            $channelPie[] = [
                'name'  => $name,
                'value' => $value,
            ];
        }

        // ========== 每日销售趋势（仅商品订单，按天汇总） ==========
        $trendRows = Db::name('order')
            ->where('status', '<>', 0)
            ->whereTime('create_time', 'between', [$monthStart, $monthEnd])
            ->field("FROM_UNIXTIME(create_time,'%Y-%m-%d') as day, SUM(order_amount) as total")
            ->group('day')
            ->order('day asc')
            ->select();

        $trendDates  = [];
        $trendValues = [];
        $trendTotal  = 0;
        foreach ($trendRows as $row) {
            $trendDates[]  = $row['day'];
            $amount        = (float)$row['total'];
            $trendValues[] = $amount;
            $trendTotal   += $amount;
        }

        // 上月总销售额（用于计算趋势图上的“上月同比”）
        $lastTrendTotal = Db::name('order')
            ->where('status', '<>', 0)
            ->whereTime('create_time', 'between', [$lastMonthStart, $lastMonthEnd])
            ->sum('order_amount');

        $trendCompare = $lastTrendTotal > 0 ? round((($trendTotal - $lastTrendTotal) / $lastTrendTotal) * 100, 2) : 0;

        // ========== 区域月销售额（通过地址模糊匹配简单统计） ==========
        $regions      = ['南明区', '云岩区', '白云区', '乌当区', '花溪区', '龙里'];
        $regionTotals = [];
        foreach ($regions as $regionName) {
            $total = Db::name('order')
                ->where('status', '<>', 0)
                ->whereTime('create_time', 'month')
                ->whereLike('address', '%' . $regionName . '%')
                ->sum('order_amount');
            $regionTotals[] = (float)$total;
        }

        // ========== 本月销售情况（单次/年卡/次卡/其它服务） ==========
        // 复用统计图表控制器中的规则：根据 number 字段区分不同卡类型
        $salesSituation = [
            ['value' => 0, 'name' => '单次服务'],
            ['value' => 0, 'name' => '年卡销量'],
            ['value' => 0, 'name' => '次卡销量'],
            ['value' => 0, 'name' => '其它服务'],
        ];
        $kaWhere = [
            ['del', '=', 0],
            ['create_time', '>=', $monthStartTs],
            ['create_time', '<=', $monthEndTs],
        ];
        // 单次服务：number = 1
        $salesSituation[0]['value'] = Db::name('order')
            ->where($kaWhere)
            ->where('number', '=', 1)
            ->sum('total_amount');
        // 年卡：number > 6
        $salesSituation[1]['value'] = Db::name('order')
            ->where($kaWhere)
            ->where('number', '>', 6)
            ->sum('total_amount');
        // 次卡：2 <= number <= 6
        $salesSituation[2]['value'] = Db::name('order')
            ->where($kaWhere)
            ->where('number', '>=', 2)
            ->where('number', '<=', 6)
            ->sum('total_amount');
        // 其它服务：number = 0
        $salesSituation[3]['value'] = Db::name('order')
            ->where($kaWhere)
            ->where('number', '=', 0)
            ->sum('total_amount');

        // ========== 渠道 & 区域详细拆分（总额 / 年卡 / 次卡 / 单次 / 其它） ==========
        $serviceTypes = [
            'total' => [],
            'year'  => [['number', '>', 6]],
            'times' => [['number', '>=', 2], ['number', '<=', 6]],
            'single'=> [['number', '=', 1]],
            'other' => [['number', '=', 0]],
        ];

        // 渠道关键字到列 key 的映射
        $channelBuckets = [
            'meituan' => '美团',
            'gzh'     => '公众号',
            'douyin'  => '抖音',
            'staff'   => '员工',
            'yeye'    => '异业',
            'other'   => '渠道',
        ];

        $channelDetail = [];
        foreach ($serviceTypes as $typeKey => $extraWhere) {
            $totals = [
                'meituan' => 0,
                'gzh'     => 0,
                'douyin'  => 0,
                'staff'   => 0,
                'yeye'    => 0,
                'other'   => 0,
            ];

            $query = Db::name('order')
                ->alias('o')
                ->join('orderchannel c', 'o.channel_id = c.id', 'LEFT')
                ->where('o.status', '<>', 0)
                ->whereBetween('o.create_time', [$monthStartTs, $monthEndTs]);

            if (!empty($extraWhere)) {
                $query->where($extraWhere);
            }

            $rows = $query
                ->field('c.name as channel_name, SUM(o.order_amount) as total')
                ->group('o.channel_id')
                ->select();

            foreach ($rows as $row) {
                $name  = $row['channel_name'] ?? '';
                $value = (float)($row['total'] ?? 0);
                $bucket = 'other';
                if (mb_strpos($name, '美团') !== false) {
                    $bucket = 'meituan';
                } elseif (mb_strpos($name, '公众号') !== false || mb_strpos($name, '公') !== false) {
                    $bucket = 'gzh';
                } elseif (mb_strpos($name, '抖音') !== false) {
                    $bucket = 'douyin';
                } elseif (mb_strpos($name, '员工') !== false) {
                    $bucket = 'staff';
                } elseif (mb_strpos($name, '异业') !== false) {
                    $bucket = 'yeye';
                }
                $totals[$bucket] += $value;
            }

            $channelDetail[$typeKey] = $totals;
        }

        // 区域明细：每种服务类型在各区域的销售额
        $regionDetail = [];
        foreach ($serviceTypes as $typeKey => $extraWhere) {
            $regionDetail[$typeKey] = [];
            foreach ($regions as $regionName) {
                $q = Db::name('order')
                    ->where('status', '<>', 0)
                    ->whereBetween('create_time', [$monthStartTs, $monthEndTs])
                    ->whereLike('address', '%' . $regionName . '%');
                if (!empty($extraWhere)) {
                    $q->where($extraWhere);
                }
                $regionDetail[$typeKey][$regionName] = (float)$q->sum('order_amount');
            }
        }

        // 汇总仪表盘数据供前端使用
        $dashboardData = [
            'monthlyTarget'      => $monthlyTarget,
            'overallCompletion'  => $overallCompletion,
            'staffProgress'      => $staffProgress,
            'channelPie'         => $channelPie,
            'trendDates'         => $trendDates,
            'trendValues'        => $trendValues,
            'trendCompare'       => $trendCompare,
            'regions'            => $regions,
            'regionTotals'       => $regionTotals,
            'salesSituation'     => $salesSituation,
            'channelDetail'      => $channelDetail,
            'regionDetail'       => $regionDetail,
        ];
        
        // 传递数据到视图
        $this->assign([
            'monthlySales' => $monthlySales,
            'monthlyOrders' => $monthlyOrders,
            'monthlyCustomers' => $monthlyCustomers,
            'salesCompare' => $salesCompare,
            'ordersCompare' => $ordersCompare,
            'customersCompare' => $customersCompare,
            'oldCustomers' => $oldCustomerCount,  // 老客户数量（本月注册且下过单的）
            'newCustomers' => $newCustomerCount,   // 新客户数量（本月注册但没下过单的）
            'dashboardData' => json_encode($dashboardData, JSON_UNESCAPED_UNICODE),
        ]);
        
        return $this->fetch();
    }


    //地图上显示注册用户位置
    public function map()
    {
        //查询商品订单
        $orderMoney = Order::where('status','<>', 0)->sum('order_amount');
        //年卡订单
        $yearCardOrderMoney = OrderYearcard::where('status', 1)->sum('pay_fee');
        //订单管理
        $orderGoods = OrderGoods::where('status', 1)->sum('total_fee');
        //充值订单
        $rechargeOrderMoney = OrderRecharge::where('pay_status', 1)->sum('order_amount');
        return $this->fetch();
    }

    public function getUserMap()
    {
        // 查询有经纬度的用户
        $userArray = User::where('latitude', '<>', '')
            ->where('longitude', '<>', '')
            ->field('id,nickname,mobile,avatar,longitude,latitude')
            ->select();
        // 格式化数据，转换为前端期望的字段名
        $result = [];
        foreach ($userArray as $user) {
            // 处理头像URL，使用代理接口避免CORS问题
            $avatar = '';
            if (!empty($user['avatar'])) {
                $originalUrl = UrlServer::getFileUrl($user['avatar']);
                // 去掉JSON编码时产生的转义反斜杠
                $originalUrl = str_replace('\\/', '/', $originalUrl);
                
                // 如果URL是跨域的，使用代理接口
                $currentDomain = $this->request->domain();
                if (strpos($originalUrl, $currentDomain) === false) {
                    // 跨域，使用代理（手动构建URL避免双重编码）
                    $avatar = $this->request->domain() . '/admin/bulletin_board/proxyImage?url=' . rawurlencode($originalUrl);
                } else {
                    // 同域，直接使用
                    $avatar = $originalUrl;
                }
            }
            
            $result[] = [
                'id' => $user['id'] ?? 0,
                'lng' => floatval($user['longitude'] ?? 0),
                'lat' => floatval($user['latitude'] ?? 0),
                'name' => $user['nickname'] ?? '',
                'contact' => $user['nickname'] ?? '', // 兼容两种字段名
                'mobile' => $user['mobile'] ?? '',
                'phone' => $user['mobile'] ?? '', // 兼容 telephone/phone
                'telephone' => $user['mobile'] ?? '',
                'avatar' => $avatar
            ];
        }

        return $this->_success('获取成功', $result);
    }

    /**
     * 图片代理接口，解决CORS跨域问题
     */
    public function proxyImage()
    {
        $url = $this->request->get('url', '');
        if (empty($url)) {
            header('HTTP/1.1 404 Not Found');
            exit;
        }

        // 解码URL
        $url = urldecode($url);
        
        // 验证URL格式
        if (!filter_var($url, FILTER_VALIDATE_URL)) {
            header('HTTP/1.1 400 Bad Request');
            exit;
        }

        // 只允许图片格式
        $allowedExtensions = ['jpg', 'jpeg', 'png', 'gif', 'webp'];
        $extension = strtolower(pathinfo(parse_url($url, PHP_URL_PATH), PATHINFO_EXTENSION));
        if (!in_array($extension, $allowedExtensions)) {
            header('HTTP/1.1 403 Forbidden');
            exit;
        }

        // 获取图片
        $context = stream_context_create([
            'http' => [
                'method' => 'GET',
                'timeout' => 10,
                'header' => [
                    'User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36'
                ]
            ]
        ]);

        $imageData = @file_get_contents($url, false, $context);
        
        if ($imageData === false) {
            header('HTTP/1.1 404 Not Found');
            exit;
        }

        // 设置响应头
        header('Content-Type: image/' . ($extension === 'jpg' ? 'jpeg' : $extension));
        header('Cache-Control: public, max-age=3600');
        header('Access-Control-Allow-Origin: *');
        header('Access-Control-Allow-Methods: GET');
        
        // 输出图片
        echo $imageData;
        exit;
    }
}