<?php

namespace App\Console\Commands;

use App\Models\DealRecord;
use App\Models\DealRecordGoods;
use App\Models\DealRecordUser;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use App\Models\School;
use App\Models\AreaProvinceModel;
use App\Models\SchoolSpecial;
use App\Models\SchoolSpecialDetail;
use App\Models\SchoolProvincescore;
use App\Models\SchoolMajorScore;
use Illuminate\Support\Facades\Http;


use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use Symfony\Component\HttpFoundation\StreamedResponse;

class BatchUpdateDealRecordStatus extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'batch:update:dealRecord:status   {begin} {end}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '批量更新交易记录状态';

    protected $logFile = '';

    protected $dealRecordModel;

    protected $dealRecordUserModel;

    protected $dealRecordGoodsModel;

    /**
     * Execute the console command.
     *
     * @return int
     */

    public function handle()
    {


        $begin = (int)$this->argument('begin');
        $end = (int)$this->argument('end');

        dump($begin, $end);

        $school = new School();
        $SchoolMajorScore = new SchoolMajorScore();
//        $SchoolProvincescore = new SchoolProvincescore();
//        $areaProvinceModel = new AreaProvinceModel();
//        $pdata = $areaProvinceModel->selectDataWithField(['country' => 1], ['id', 'name']);
//        $provinceData = array_column($pdata, 'name', 'id');

        $school::select(['id', 'school_id'])->where([['id', '>=', $begin], ['id', '<', $end], ['level_name', 'like', "本科%"]])->chunkById(10, function ($list) use (&$SchoolMajorScore) {
            $userList = $list->toArray();

            foreach ($userList as $userInfo) {


                $schoolId = $userInfo['school_id'];

                try {
                    //专业对应检索条件
                    $url = "https://static-data.gaokao.cn/www/2.0/school/{$schoolId}/dic/professionalscore.json?a=www.gaokao.cn";
                    $response = Http::withHeaders([
                        'Content-Type' => 'application/json',
                        'authority' => 'api.zjzw.cn',
                    ])->get($url, []);
                    $bodyData = $response->json(); // 如果响应是 JSON 格式，解析为数组或对象
                    if ($bodyData['code'] != '0000') {
                        continue;
                    }
                    if (!isset($bodyData['data']) || empty($bodyData['data'])) {
                        continue;
                    }

                    $yearDataRePid = $bodyData['data']['newsdata']['year'];

                } catch (\Exception $e) {
                    echo $url . ' ';
                    continue;
                }


                foreach ($yearDataRePid as $index => $item) {
                    $provinceid = $index;

                    foreach ($item as $pKey => $dVal) {

                        try {
                            $year = $dVal;
                            if($year != 2022){
                                continue;
                            }

                            sleep(3);
                            $url = "https://static-data.gaokao.cn/www/2.0/schoolspecialscore/{$schoolId}/{$year}/{$provinceid}.json?a=www.gaokao.cn";
                            $response = Http::withHeaders([
                                'Content-Type' => 'application/json',
                                'authority' => 'api.zjzw.cn',
                            ])->get($url, []);
                            $bodyData = $response->json(); // 如果响应是 JSON 格式，解析为数组或对象
                            if ($bodyData['code'] != '0000') {
                                dump($url, $bodyData);
                                continue;
                            }
                            if (!isset($bodyData['data']) || empty($bodyData['data'])) {
                                continue;
                            }

                            foreach ($bodyData['data'] as $k => $v) {

                                $ex = explode('_', $k);
                                if ($ex[2] != 0) {
                                    continue;
                                }
                                $iData = [];
                                foreach ($v['item'] as $idk => $idv) {
                                    $tmp = [
                                        'school_id' => $schoolId,
                                        'year' => $year,
                                        'province_id' => $provinceid,
                                        'local_type_id' => $idv['type'],
                                        'local_batch_id' => $idv['batch'],
                                        'min' => $idv['min'],
                                        'min_section' => $idv['min_section'],
                                        'remark' => $idv['sp_fxk'] . '-' . $idv['sp_sxk'],
                                        'sp_info' => $idv['sp_info'],
                                        'sp_name' => $idv['sp_name'],
                                        'special_id' => $idv['special_id'],
                                        'spe_id' => $idv['spe_id'],
                                        'level1_name' => $idv['level1_name'],
                                        'level2_name' => $idv['level2_name'],
                                        'level3_name' => $idv['level3_name'],
                                        'all_data' =>json_encode($idv, JSON_UNESCAPED_UNICODE),
                                    ];

                                    $iData[] = $tmp;
                                }
                                $SchoolMajorScore->insertMultiData($iData);
                            }


                        } catch (\Exception $e) {
                            echo $url. ' ';
                            dump($e->getMessage());
                            sleep(10);
                        }
                    }


                }
                echo $userInfo['id'] . ' ';
            }
        });
    }



    public function handle66999()
    {


        $begin = (int)$this->argument('begin');
        $end = (int)$this->argument('end');

        dump($begin, $end);

        $school = new School();
        $schoolData = $school->selectDataWithField([['level_name', 'like', "本科%"]], ['school_id','name']);
        $schoolDataReIds = array_column($schoolData, 'name', 'school_id');
        $SchoolMajorScore = new SchoolMajorScore();
//        $areaProvinceModel = new AreaProvinceModel();
//        $pdata = $areaProvinceModel->selectDataWithField(['country' => 1], ['id', 'name']);
//        $provinceData = array_column($pdata, 'name', 'id');
//        $yearData = [2024, 2023];


        $SchoolMajorScore::select(['*'])->where([['province_id', '=', 12]])->chunkById(1000, function ($list) use ( &$schoolDataReIds) {
            $userList = $list->toArray();

            $spreadsheet = new Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();

            $rowNumber = 2;
            $md5Arr = [];
            foreach ($userList as $userInfo) {
                $jsonData = json_decode($userInfo['all_data'], true);
                $year = $userInfo['year'] ?? "";

                //抓数据有重复的，去重一下
                $md5 = md5($year.'_'.$userInfo['school_id'].'_'. $userInfo['province_id'].'_'. $userInfo['special_id'].$jsonData['info'] ?? '');
                if(in_array($md5, $md5Arr)){
                    continue;
                }
                $md5Arr[] = $md5;


                $schoolName = $schoolDataReIds[$jsonData['school_id']] ?? "";

                // 创建一个新的 Spreadsheet 对象

                // 设置Excel文件的列头
                $sheet->setCellValue('A1', '招生学校');
                $sheet->setCellValue('B1', '地区');
                $sheet->setCellValue('C1', '招生年份');
                $sheet->setCellValue('D1', '招生类型');
                $sheet->setCellValue('E1', '专业大类');
                $sheet->setCellValue('F1', '专业大类');
                $sheet->setCellValue('G1', '最低分数');
                $sheet->setCellValue('H1', '最低位次');
                $sheet->setCellValue('I1', '选科要求');
                $sheet->setCellValue('J1', '招生专业');
                $sheet->setCellValue('K1', '备注');

                // 设置列宽
                $sheet->getColumnDimension("A")->setWidth(20);
                $sheet->getColumnDimension("B")->setWidth(10);
                $sheet->getColumnDimension("D")->setWidth(10);
                $sheet->getColumnDimension("E")->setWidth(10);
                $sheet->getColumnDimension("F")->setWidth(20);
                $sheet->getColumnDimension("G")->setWidth(10);
                $sheet->getColumnDimension("H")->setWidth(10);
                $sheet->getColumnDimension("I")->setWidth(20);
                $sheet->getColumnDimension("J")->setWidth(20);
                $sheet->getColumnDimension("K")->setWidth(20);

                // 获取单元格样式
                $styleArray = [
                    'alignment' => [
                        'horizontal' => Alignment::HORIZONTAL_CENTER, // 水平居中
                        'vertical' => Alignment::VERTICAL_CENTER, // 垂直居中
                    ],
                ];

                // 将样式应用到单元格A1
                $sheet->getStyle('A')->applyFromArray($styleArray);
                $sheet->getStyle('B')->applyFromArray($styleArray);
                $sheet->getStyle('C')->applyFromArray($styleArray);
                $sheet->getStyle('D')->applyFromArray($styleArray);
                $sheet->getStyle('E')->applyFromArray($styleArray);
                $sheet->getStyle('F')->applyFromArray($styleArray);
                $sheet->getStyle('G')->applyFromArray($styleArray);
                $sheet->getStyle('H')->applyFromArray($styleArray);
                $sheet->getStyle('I')->applyFromArray($styleArray);
                // 填充Excel数据

                $sheet->setCellValue('A' . $rowNumber, $schoolName);
                $sheet->setCellValue('B' . $rowNumber, '天津');
                $sheet->setCellValue('C' . $rowNumber, $year);
                $sheet->setCellValue('D' . $rowNumber, $jsonData['level1_name'] ?? '');
                $sheet->setCellValue('E' . $rowNumber, $jsonData['level2_name'] ??'');
                $sheet->setCellValue('F' . $rowNumber, $jsonData['level3_name'] ?? '');
                $sheet->setCellValue('G' . $rowNumber, $jsonData['min'] ?? '');
                $sheet->setCellValue('H' . $rowNumber, $jsonData['min_section'] ?? '');
                $sheet->setCellValue('I' . $rowNumber, $jsonData['sg_info'] ??'');
                $sheet->setCellValue('J' . $rowNumber, $jsonData['sp_name'] ??'');
                $sheet->setCellValue('K' . $rowNumber, $jsonData['info'] ??'');

                $rowNumber++;
                echo $rowNumber . ' ';
            }

            echo $rowNumber . ' ';


            $writer = new Xlsx($spreadsheet);
            $filePath = storage_path("app/users{$rowNumber}.xlsx");
            $writer->save($filePath);
        });


        // 创建 Excel 文件并返回下载响应
//        $writer = new Xlsx($spreadsheet);

//        $response = new StreamedResponse(function() use ($writer) {
//            $writer->save('./users1.xlsx');
//        });
//
//        // 设置响应头信息
//        $response->headers->set('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
//        $response->headers->set('Content-Disposition', 'attachment; filename="data_export.xlsx"');
//        $response->headers->set('Cache-Control', 'max-age=0');
        // $response->status = 200;
        dd(222);


    }



    public function handle4445()
    {

        $begin = (int)$this->argument('begin');
        $end = (int)$this->argument('end');

        dump($begin, $end);

        $school = new School();
        $schoolSpecial = new SchoolSpecial();
        $schoolSpecialDetail = new SchoolSpecialDetail();
        $schoolSpecial->where([['id', '>=', $begin], ['id', '<', $end]])->chunkById(50, function ($list) use (&$schoolSpecial, &$schoolSpecialDetail) {
            $userList = $list->toArray();
//            dd($userList);
            foreach ($userList as $userInfo) {

                $schoolId = $userInfo['school_id'];
                $spId = $userInfo['sp_id'];

                $findRet = $schoolSpecialDetail->findData(['school_id' => $schoolId, 'sp_id' => $spId]);
                if (!empty($findRet)) {
                    continue;
                }
                $url = "https://static-data.gaokao.cn/www/2.0/school/" . $schoolId . "/special/" . $spId . ".json?a=www.gaokao.cn";
                $response = Http::withHeaders([
                    'Content-Type' => 'application/json',
                    'authority' => 'api.zjzw.cn',
                ])->get($url, []);
                $bodyData = $response->json(); // 如果响应是 JSON 格式，解析为数组或对象

//                dd($bodyData);

                if ($bodyData['code'] != '0000') {
                    dump($spId, $bodyData);
                    continue;
                }
                if (!isset($bodyData['data'])) {
                    dump('该学校无专业信息：' . $schoolId . '-' . $spId);
                    continue;
                }
                $specialDataB = $bodyData['data'];

//                $zhuanyeData = [];
//                foreach ($specialDataB as $key => $value) {

                try {


                    $tmp = [
                        'sp_id' => $specialDataB['id'],
                        'school_id' => $specialDataB['school_id'],
                        'special_id' => $specialDataB['special_id'],
                        'content' => $specialDataB['content'] ?? '<h3>专业介绍</h3>',
                        'level1_name' => $specialDataB['level1_name'],
                        'level2_name' => $specialDataB['level2_name'],
                        'level3_name' => $specialDataB['level3_name'],
                        'all_data' => json_encode($specialDataB, JSON_UNESCAPED_UNICODE),
                    ];

//                    $zhuanyeData[] = $tmp;
//                }

                    $schoolSpecialDetail->insertMultiData($tmp);
                } catch (\Exception $e) {


                    dump($e->getMessage());
                    dump($schoolId . '-' . $spId . '-' . $userInfo['id']);
                    continue;
                }
//                dd(555);
                echo $schoolId . '-' . $spId . '-' . $userInfo['id'] . ' ';

            }
            sleep(5);
        });

    }


    public function handle99()
    {


        $school = new School();
        $schoolSpecial = new SchoolSpecial();
        $school->where([['id', '>', 2473], ['level_name', 'like', "本科%"]])->chunkById(3, function ($list) use (&$schoolSpecial, &$school) {
            $userList = $list->toArray();
//            dd($userList);
            foreach ($userList as $userInfo) {

                $schoolId = $userInfo['sid'];


                $url = "https://static-data.gaokao.cn/www/2.0/school/" . $schoolId . "/pc_special.json?a=www.gaokao.cn";
                $response = Http::withHeaders([
                    'Content-Type' => 'application/json',
                    'authority' => 'api.zjzw.cn',
                ])->get($url, []);
                $bodyData = $response->json(); // 如果响应是 JSON 格式，解析为数组或对象

                if ($bodyData['code'] != '0000') {
                    dump($schoolId, $bodyData);
                    continue;
                }
                if (!isset($bodyData['data']['special_detail'][1]) || !isset($bodyData['data']['special_detail'][2])) {
                    dump('该学校无专业信息：' . $schoolId);
                    continue;
                }
                $specialDataB = $bodyData['data']['special_detail'][1];
                $specialDataZ = $bodyData['data']['special_detail'][2];
                $special_all_type = [];


                if (!empty($specialDataB)) {
                    $zhuanyeData = [];
                    foreach ($specialDataB as $key => $value) {
                        if (!in_array($value['level3_name'], $special_all_type)) {
                            $special_all_type[] = $value['level3_name'];
                        }
                        $tmp = [
                            'sp_id' => $value['id'],
                            'school_id' => $value['school_id'],
                            'special_id' => $value['special_id'],
                            'special_name' => $value['special_name'],
                            'special_type' => $value['special_type'],
                            'type_name' => $value['type_name'],
                            'nation_feature' => $value['nation_feature'],
                            'province_feature' => $value['province_feature'],
                            'nation_first_class' => $value['nation_first_class'],
                            'is_important' => $value['is_important'],
                            'limit_year' => $value['limit_year'],
                            'year' => $value['year'],
                            'level3_weight' => $value['level3_weight'],
                            'xueke_rank' => $value['xueke_rank'],
                            'ruanke_level' => $value['ruanke_level'],
                            'level3_name' => $value['level3_name'],
                            'level3_code' => $value['level3_code'],
                            'level2_name' => $value['level2_name'],
                            'level2_id' => $value['level2_id'],
                            'level2_code' => $value['level2_code'],
                            'all_data' => json_encode($value, JSON_UNESCAPED_UNICODE),
                        ];

                        $zhuanyeData[] = $tmp;
                    }

                    $schoolSpecial->insertMultiData($zhuanyeData);
                }

                if (!empty($specialDataZ)) {
                    $zhuanyeData = [];
                    foreach ($specialDataZ as $key => $value) {
                        if (!in_array($value['level3_name'], $special_all_type)) {
                            $special_all_type[] = $value['level3_name'];
                        }
                        $tmp = [
                            'sp_id' => $value['id'],
                            'school_id' => $value['school_id'],
                            'special_id' => $value['special_id'],
                            'special_name' => $value['special_name'],
                            'special_type' => $value['special_type'],
                            'type_name' => $value['type_name'],
                            'nation_feature' => $value['nation_feature'],
                            'province_feature' => $value['province_feature'],
                            'nation_first_class' => $value['nation_first_class'],
                            'is_important' => $value['is_important'],
                            'limit_year' => $value['limit_year'],
                            'year' => $value['year'],
                            'level3_weight' => $value['level3_weight'],
                            'xueke_rank' => $value['xueke_rank'],
                            'ruanke_level' => $value['ruanke_level'],
                            'level3_name' => $value['level3_name'],
                            'level3_code' => $value['level3_code'],
                            'level2_name' => $value['level2_name'],
                            'level2_id' => $value['level2_id'],
                            'level2_code' => $value['level2_code'],
                            'all_data' => json_encode($value, JSON_UNESCAPED_UNICODE),
                        ];

                        $zhuanyeData[] = $tmp;
                    }

                    $schoolSpecial->insertMultiData($zhuanyeData);
                }

                $D = $bodyData['data']['special_detail'][3] ?? [];
                //更新进去数据
                $updateD = ['special_all_type' => json_encode($special_all_type, JSON_UNESCAPED_UNICODE), 'special_data' => json_encode($D, JSON_UNESCAPED_UNICODE)];
                $school->updateData(['sid' => $schoolId], $updateD);

                echo $schoolId . ' ';


            }
        });

    }


    public function handle444()
    {


        $school = new School();
        $areaProvinceModel = new AreaProvinceModel();
        $areaProvinceModel->chunkById(100, function ($list) use (&$areaProvinceModel, &$school) {
            $userList = $list->toArray();

            foreach ($userList as $userInfo) {

                $data = $school->findDataWithField(['province_id' => $userInfo['id']], ['province_id', 'province_name']);

                $areaProvinceModel->updateData(['id' => $userInfo['id']], ['name' => $data['province_name']]);

                echo $userInfo['id'] . '  ';
            }
        });


    }

    public function handle222()
    {


        $school = new School();


        $school->chunkById(100, function ($list) use (&$line, &$school) {
            $userList = $list->toArray();

            foreach ($userList as $userInfo) {

                $sid = $userInfo['sid'];
                $url = 'https://static-data.gaokao.cn/upload/logo/' . $sid . '.jpg';

                $result = $this->downloadImage(
                    $url,
                    './public/uploads/logo',
                    $sid
                );
                if ($result['success'] == false) {
                    dd($result, $userInfo);
                }

                $school->updateData(['id' => $userInfo['id']], ['logo' => '/uploads/logo/' . $result['url']]);

                echo $userInfo['id'] . '  ';
            }
        });


    }


    /**
     * 下载远程图片到本地服务器
     * @param string $url 远程图片URL
     * @param string $saveDir 本地保存目录
     * @param string $fileName 可选自定义文件名（不含扩展名）
     * @return array 返回操作结果数组
     */
    function downloadImage($url, $saveDir, $fileName = null)
    {
        try {
            // 验证URL格式
            if (!filter_var($url, FILTER_VALIDATE_URL)) {
                return [
                    'success' => false,
                    'error' => '无效的URL格式'
                ];
            }

            // 验证URL可达性
            $headers = @get_headers($url, 1);
            if (!$headers || strpos($headers[0], '200') === false) {
                return [
                    'success' => false,
                    'error' => '无法访问远程资源'
                ];
            }

            // 验证内容类型是否为图片
            $contentType = isset($headers['Content-Type']) ? $headers['Content-Type'] : '';
            if (!preg_match('/image\/(jpeg|png|gif|webp|bmp)/i', $contentType)) {
                return [
                    'success' => false,
                    'error' => '远程资源不是有效的图片类型'
                ];
            }

            // 创建保存目录（如果不存在）
            if (!file_exists($saveDir)) {
                if (!mkdir($saveDir, 0755, true)) {
                    return [
                        'success' => false,
                        'error' => '无法创建保存目录'
                    ];
                }
            }

            // 生成文件名
            $ext = pathinfo(parse_url($url, PHP_URL_PATH), PATHINFO_EXTENSION) ?: 'jpg';
            $cleanExt = strtolower(preg_replace('/[^a-zA-Z0-9]/', '', $ext)) ?: 'jpg';

            if ($fileName) {
                // 自定义文件名处理
                $baseName = preg_replace('/[^a-zA-Z0-9_-]/', '', $fileName);
                $saveName = $baseName . '.' . $cleanExt;
            } else {
                // 自动生成唯一文件名
                $saveName = md5(uniqid() . microtime(true)) . '.' . $cleanExt;
            }

            $savePath = rtrim($saveDir, '/') . '/' . $saveName;

            // 下载文件
            $imageData = @file_get_contents($url);
            if ($imageData === false) {
                return [
                    'success' => false,
                    'error' => '下载图片内容失败'
                ];
            }

            // 保存文件
            if (!file_put_contents($savePath, $imageData)) {
                return [
                    'success' => false,
                    'error' => '无法保存文件到目录'
                ];
            }

            // 二次验证文件有效性
            if (!@getimagesize($savePath)) {
                @unlink($savePath);
                return [
                    'success' => false,
                    'error' => '下载的文件不是有效图片'
                ];
            }

            return [
                'success' => true,
                'path' => $savePath,
                'size' => filesize($savePath),
                'url' => basename($savePath)
            ];

        } catch (\Exception $e) {
            return [
                'success' => false,
                'error' => $e->getMessage()
            ];
        }
    }


}
