<?php
/**
 * @remark :
 * @name   :UpdateRoute.php
 * @author :lyh
 * @method :post
 * @time   :2023/11/20 15:07
 */

namespace App\Console\Commands\LyhTest;

use App\Helper\Arr;
use App\Helper\Translate;
use App\Models\Ai\AiBlog;
use App\Models\Blog\Blog;
use App\Models\Com\WordCountry;
use App\Models\CustomModule\CustomModuleContent;
use App\Models\GoogleSearch\GoogleCodeCountry;
use App\Models\Product\CategoryRelated;
use App\Models\Product\Keyword;
use App\Models\Product\Product;
use App\Models\Project\AggregateKeyword;
use App\Models\Project\AiBlogTask;
use App\Models\Project\DeployOptimize;
use App\Models\Project\Project;
use App\Models\RankData\RankDataLog;
use App\Models\RouteMap\RouteMap;
use App\Models\WebSetting\WebSetting;
use App\Models\WebSetting\WebSettingSeo;
use App\Services\ProjectServer;
use App\Utils\HttpUtils;
use GuzzleHttp\Exception\GuzzleException;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

/**
 * @remark :更新所有项目的路由
 * @name   :UpdateRoute
 * @author :lyh
 * @method :post
 * @time   :2023/11/20 15:08
 */
class UpdateRoute extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'update_route';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '更新路由';

    /**
     * @remark :统一更新路由
     * @name   :handle
     * @author :lyh
     * @method :post
     * @time   :2023/11/20 15:13
     */
    public function handle()
    {
        return $this->settingSeo();
    }

    public function keyword_actions(){
        $a = new AggregateKeyword();
//        $list = $a->formatQuery(['status'=>1])->pluck('project_id')->unique()->values()->toArray();
        $list = [2368,2168,1786,2289,3685,3008];
        foreach ($list as $v){
            if($v == 1){
                continue;
            }
            echo date('Y-m-d H:i:s').' 执行的项目id:'.$v.PHP_EOL;
            ProjectServer::useProject($v);
            $bak_lists = DB::connection('custom_mysql')->table('gl_product_keyword_bak')->where('status',1)->get()->toArray();
            $keywordModel = new Keyword();
            $keywordModel->truncate();
            $routeMapModel = new RouteMap();
            $routeMapModel->del(['source'=>'product_keyword']);
            foreach ($bak_lists as $bakV){
                $info = $keywordModel->read(['title'=>$bakV['title']],['id']);
                if($info !== false){
                    echo '当前关键词已存在，跳过的关键词'.$bakV['title'].PHP_EOL;
                    continue;
                }
                //执行新增数据
                unset($bakV['id']);
                $id = $keywordModel->addReturnId($bakV);
                $route = RouteMap::setRoute($bakV['route'],'product_keyword',$id,$v);
                $keywordModel->edit(['route'=>$route],['id'=>$id]);
                echo '执行新增----'.$bakV['title'].PHP_EOL;
            }
            DB::disconnect('custom_mysql');
        }
    }

    /**
     * @remark :清洗数据
     * @name   :_action
     * @author :lyh
     * @method :post
     * @time   :2025/4/10 17:08
     */
    public function _action(){
        $projectModel = new Project();
        $lists = $projectModel->list(['delete_status'=>0,'extend_type'=>0,'type'=>['!=',0]],'id',['id']);
        $keywordModel = new Keyword();
        foreach ($lists as $v){
            ProjectServer::useProject($v['id']);
            echo date('Y-m-d H:i:s').' 执行的项目id:'.$v['id'].PHP_EOL;
            $keywordModel->edit(['type'=>1],['id'=>['!=',0]]);
            DB::disconnect('custom_mysql');
        }
        return true;
    }
        /**
         * @remark :
         * @name   :getAiBlog
         * @author :lyh
         * @method :post
         * @time   :2025/3/21 17:45
         */
        public function getAiBlog($project_id){
            $aiBlogModel = new AiBlog();
            $lists = $aiBlogModel->list(['updated_at'=>['<=','2025-03-21 00:00:00']]);
            if(!empty($lists)){

                $aiBlogTaskModel = new AiBlogTask();
                foreach ($lists as $k => $v){
                    $aiBlogTaskModel->edit(['status'=>1],['task_id'=>$v['task_id']]);
                }
                echo date('Y-m-d H:i:s') . '有任务未执行的project_id:'.$project_id . PHP_EOL;
            }
            return true;
        }

//        $this->updateProjectOp();
//        $projectModel = new Project();
//        $lists = $projectModel->list(['delete_status'=>0],'id',['id']);
//        foreach ($lists as $v){
//            echo date('Y-m-d H:i:s') . 'project_id:'.$v['id'] . PHP_EOL;
//            ProjectServer::useProject($v['id']);
//            $webSettingModel = new WebSetting();
//            $settingInfo = $webSettingModel->read(['project_id'=>$v['id']]);
//            if($settingInfo === false){
//                echo date('Y-m-d H:i:s') . '当前项目的设置数据不存在:'.$v['id'] . PHP_EOL;
//                //新增一条数据
//                $data = [
//                    'anchor_setting'=>'["3","4","5"]',
//                    'anchor_is_enable'=>1,
//                    'anchor_page_num'=>1,
//                    'anchor_num'=>2,
//                    'anchor_keyword_is_enable'=>1,
//                    'project_id'=>$v['id']
//                ];
//                $webSettingModel->add($data);
//            }
//            DB::disconnect('custom_mysql');
//        }
//        echo date('Y-m-d H:i:s') . 'end' . PHP_EOL;
//    }

    /**
     * @remark :更新产品
     * @name   :updateProduct
     * @author :lyh
     * @method :post
     * @time   :2024/11/11 13:59
     */
    public function updateProduct(){
        $productModel = new Product();
        $list = $productModel->list(['created_at'=>['like','2024-11-11%']],'id',['id']);
        $productModel->edit(['category_id'=>',14,'],['created_at'=>['like','2024-11-11%']]);
        $data = [];
        foreach ($list as $k => $v){
            $data[] = [
                'product_id'=>$v['id'],
                'cate_id'=>14,
                'created_at'=>date('Y-m-d H:i:s'),
                'updated_at'=>date('Y-m-d H:i:s')
            ];
        }
        $relaModel = new CategoryRelated();
        $relaModel->insert($data);
    }

    /**
     * @remark :同步擴展模塊數據到blogs
     * @name   :custom_to_blogs
     * @author :lyh
     * @method :post
     * @time   :2024/10/28 15:45
     */
    public function custom_to_blogs(){
        $customContentModel = new CustomModuleContent();
        $lists = $customContentModel->list(['module_id'=>8]);
        foreach ($lists as $k => $v){
            $data = [
                'name'=>$v['name'],
                'category_id'=>',1,2,',
                'remark'=>$v['remark'],
                'text'=>$v['content'],
                'url'=>$v['route'],
                'image'=>$v['image'],
                'seo_title'=>$v['seo_title'],
                'seo_description'=>$v['seo_description'],
                'seo_keywords'=>$v['seo_keywords'],
                'project_id'=>1462,
                'operator_id'=>$v['operator_id'],
                'create_id'=>$v['operator_id'],
                'created_at'=>$v['created_at'],
                'updated_at'=>$v['updated_at'],
                'release_at'=>$v['release_at']
            ];
            $blogModel = new Blog();
            $id = $blogModel->insertGetId($data);
        }
        return true;
    }

    public function editProductAlt(){
        $productModel = new Product();
        $lists = $productModel->list(['status'=>1],'id',['id','route','seo_mate']);
        foreach ($lists as $k => $v){
            $seo_mate = $v['seo_mate'];
            $seo_mate['title'] = '';
            echo date('Y-m-d H:i:s') . ':'.json_encode($seo_mate) . PHP_EOL;
            $productModel->edit(['seo_mate'=>json_encode($seo_mate,true)],['id'=>$v['id']]);
        }
        return true;
    }

    public function setCustomRoute($project_id){
        $customModel = new CustomModuleContent();
        $list = $customModel->list();
        foreach ($list as $v){
            echo date('Y-m-d H:i:s') . '扩展模块id:'.$v['id'] . PHP_EOL;
            $route = RouteMap::setRoute($v['name'], RouteMap::SOURCE_MODULE, $v['id'], $project_id);
            $customModel->edit(['route'=>$route],['id'=>$v['id']]);
        }
    }

    public function delProductKeyword(){
        $keywordModel = new Keyword();
        $lists = $keywordModel->list(['status'=>1],'id',['id','title']);
        foreach ($lists as $v){
            echo date('Y-m-d H:i:s') . 'id:'.$v['id'] . PHP_EOL;
            $info = $keywordModel->read(['id'=>['!=',$v['id'],'title'=>$v['title']]]);
            if($info !== false){
                $keywordModel->del(['id'=>$v['id']]);
            }
        }
    }

    /**
     * @remark :写入
     * @name   :getProductKeyword
     * @author :lyh
     * @method :post
     * @time   :2023/12/21 14:37
     */
    public function setProductKeyword(){
        $keywordModel = new Keyword();
        $lists = $keywordModel->list();
        if(!empty($lists)){
            foreach ($lists as $v){
                if(!empty($v['route'])){
//                    echo date('Y-m-d H:i:s') . 'route :'.$v['id'] . PHP_EOL;
//                    $route = $this->setRoute($v['route'], RouteMap::SOURCE_PRODUCT_KEYWORD, $v['id'], $v['project_id']);
//                    $keywordModel->edit(['route'=>$route],['id'=>$v['id']]);
                    continue;
                }else{
                    echo date('Y-m-d H:i:s') . 'route :'.$v['id'] . PHP_EOL;
                    $route = Translate::tran($v['title'], 'en');
                    if(!empty($route)){
                        echo date('Y-m-d H:i:s') . $route . PHP_EOL;
                        $route = $this->setRoute($route, RouteMap::SOURCE_PRODUCT_KEYWORD, $v['id'], $v['project_id']);
                        $keywordModel->edit(['route'=>$route],['id'=>$v['id']]);
                    }
                }
            }
        }
    }

    /**
     * @param $title
     * @param $source
     * @param $source_id
     * @param int $project_id
     * @return bool
     * @throws \Exception
     * @author zbj
     * @date 2023/4/17
     */
    public static function setRoute($title, $source, $source_id, $project_id = 0){
        $route = self::generateRoute($title, $source, $source_id, $project_id);
        if(!$route){
            throw new \Exception('路由生成失败');
        }
        try {
            $route_map = RouteMap::where('project_id', $project_id)->where('source_id', $source_id)->where('source', $source)->first();
            if(!$route_map){
                $route_map = new RouteMap();
                $route_map->source = $source;
                $route_map->source_id = $source_id;
                $route_map->project_id = $project_id;
            }
            $route_map->route = $route;
            $route_map->save();
        }catch (\Exception $e){
            throw new \Exception('路由映射失败');
        }
        return $route;
    }

    /**
     * 生成路由标识
     * @param $title
     * @param $source
     * @param $source_id
     * @param $project_id
     * @return string
     * @author zbj
     * @date 2023/4/17
     */
    public static function generateRoute($title, $source, $source_id, $project_id){
        $i=1;
        $sign = generateRoute($title);
        $length = strlen($sign);
        if($length > 100){
            $sign = trim(mb_substr($sign, 0, 100, 'UTF-8'),'-');
        }
        $info = RouteMap::where(['project_id' => $project_id, 'source' => $source, 'source_id'=>$source_id])->first();
        $suffix = '';
        if(empty($info)){
            if($source == 'product'){
                $suffix = '-product';
            }
        }
        $route = $sign.$suffix;
        while(RouteMap::isExist($route, $source_id, $project_id)){
            $route = $sign .'-'.$i.$suffix;
            $i++;
        }
        return $route;
    }

    /**
     * @remark :产品关键字
     * @name   :getProductKeyword
     * @author :lyh
     * @method :post
     * @time   :2023/12/8 11:13
     */
    public function getProductKeyword(){
        $keywordModel = new Keyword();
        $lists = $keywordModel->list(['status'=>1,'route'=>'']);
        if(!empty($lists)){
            foreach ($lists as $v){
//                $tag = "-tag";
//                if (!(substr($v['route'], -strlen($tag)) === $tag)) {
//                    echo date('Y-m-d H:i:s') . '拼接'.$tag . PHP_EOL;
                    $route = $v['title'];
                    // 如果不是以 '-tag' 结尾，则拼接上 '-tag'
                    $routeModel = new RouteMap();
                    $routeModel->edit(['route'=>$route],['source'=>RouteMap::SOURCE_PRODUCT_KEYWORD,'source_id'=>$v['id']]);
                    $keywordModel->edit(['route'=>$route],['id'=>$v['id']]);
                    echo date('Y-m-d H:i:s') . 'end'.$v['id'] . PHP_EOL;
//                }
            }
        }
    }

    public function getProduct(){
        $productModel = new Product();
        $lists = $productModel->list();
        if(!empty($lists)){
            foreach ($lists as $v){
                if(!empty($v['route'])){
                    $tag = "-product";
                    if (!(substr($v['route'], -strlen($tag)) === $tag)) {
                        echo date('Y-m-d H:i:s') . '拼接'.$tag . PHP_EOL;
                        $route = $v['route'].$tag;
                        // 如果不是以 '-product' 结尾，则拼接上 '-product'
                        $route = RouteMap::setRoute($route, RouteMap::SOURCE_PRODUCT, $v['id'], $v['project_id']);
                        $productModel->edit(['route'=>$route],['id'=>$v['id']]);
//                    }else{
//                        echo date('Y-m-d H:i:s') . 'id :'.$v['id'] . PHP_EOL;
//                        $route = RouteMap::setRoute($v['title'], RouteMap::SOURCE_PRODUCT, $v['id'], $v['project_id']);
//                        $productModel->edit(['route'=>$route],['id'=>$v['id']]);
                    }
//                    $route = RouteMap::setRoute($v['route'], RouteMap::SOURCE_PRODUCT, $v['id'], $v['project_id']);
//                    $productModel->edit(['route'=>$route],['id'=>$v['id']]);
//                    continue;
                }else{
                    echo date('Y-m-d H:i:s') . 'id :'.$v['id'] . PHP_EOL;
                    $v['title'] = Translate::tran($v['title'], 'en');
                    $v['title'] = str_replace('.','',$v['title']);
                    $route = RouteMap::setRoute($v['title'], RouteMap::SOURCE_PRODUCT, $v['id'], $v['project_id']);
                    $productModel->edit(['route'=>$route],['id'=>$v['id']]);
                }
            }
        }
    }

    /**
     * @remark :升级项目同步路由
     * @name   :getRouteMap
     * @author :lyh
     * @method :post
     * @time   :2024/1/4 11:53
     */
    public function getRouteMap(){
        $routeMapModel = new RouteMap();
        $list = $routeMapModel->list(['source'=>'product','project_id'=>627]);
        foreach ($list as $k => $v){
            $product = new Product();
            $product->edit(['route'=>$v['route']],['id'=>$v['source_id']]);
            echo date('Y-m-d H:i:s') . '产品id:'.$v['source_id'] . PHP_EOL;
        }
    }

    /**
     * @remark :更新产品关联表
     * @name   :getProductCategory
     * @author :lyh
     * @method :post
     * @time   :2024/1/4 14:48
     */
    public function getProductCategory(){
        $product = new Product();
        $num = 30;
        while ($num > 0){
            $list = $product->lists(['status'=>1],$num,1000);
            foreach ($list['list'] as $k => $v){
                if(empty($v['category_id'])){
                    continue;
                }
                $category_ids = $v['category_id'];
                //产品分类关联
                echo date('Y-m-d H:i:s') . '产品id--1:'.$v['id'] . PHP_EOL;
                $this->saveRelated($v['id'], $category_ids);
                echo date('Y-m-d H:i:s') . '产品id:'.$v['id'] . PHP_EOL;
            }
            $num--;
        }
    }

    /**
     * 关联产品分类
     * @param $product_id
     * @param $cate_ids
     * @author zbj
     * @date 2023/4/21
     */
    public function saveRelated($product_id, $cate_ids)
    {
        if(!is_array($cate_ids)){
            $cate_ids = array_filter(Arr::splitFilterToArray($cate_ids), 'intval');
        }
        //先删除
        CategoryRelated::where('product_id', $product_id)->delete();
        //批量保存
        $data = [];
        if(!empty($cate_ids)){
            foreach ($cate_ids as $cate_id){
                $data[] = [
                    'product_id' => $product_id,
                    'cate_id' => $cate_id,
                    'created_at'=>date('Y-m-d H:i:s'),
                    'updated_at'=>date('Y-m-d H:i:s')
                ];
            }
            CategoryRelated::insert($data);
        }
        return true;
    }

    /**
     * @remark :删除路由通知C端
     * @name   :curlDelRoute
     * @author :lyh
     * @method :post
     * @time   :2023/11/30 14:43
     */
    public function curlDelRoute($data){
        $data['project_id'] = 75;
        $str = http_build_query($data);
        $url = 'https://www.zrutong.com/api/delHtml/?'.$str;
        curlGet($url);
        return true;
    }

    public function delRouteMap(){
        $productKeywordModel = new Keyword();
        $list = $productKeywordModel->list();
        foreach ($list as $k=>$v){
            RouteMap::setRoute($v['route'],'product_keyword',$v['id'],569);
        }
    }

    public function copyTable()
    {
        $sourceTable = 'gl_route_map';
        $destinationTable = 'gl_route_map_copy';

        if (!Schema::connection('custom_mysql')->hasTable($sourceTable)) {
            $this->error("Source table {$sourceTable} does not exist.");
            return false;
        }
        if (Schema::connection('custom_mysql')->hasTable($destinationTable)) {
            Schema::connection('custom_mysql')->dropIfExists($destinationTable);
        }
        $columns = DB::connection('custom_mysql')->select(DB::raw("SHOW COLUMNS FROM {$sourceTable}"));
        $columnsDefinition = collect($columns)->map(function($column) {
            $columnDefinition = $column->Field . ' ' . $column->Type .
                ($column->Null === 'NO' ? ' NOT NULL' : '') .
                ($column->Default !== null ? " DEFAULT '" . $column->Default . "'" : '') .
                ($column->Extra ? ' ' . $column->Extra : '');
            return $columnDefinition;
        })->implode(', ');
        // Add PRIMARY KEY to the id column
        $primaryKey = collect($columns)->firstWhere('Field', 'id') ? 'PRIMARY KEY (id)' : '';
        $columnsDefinition = $columnsDefinition . ($primaryKey ? ', ' . $primaryKey : '');
        DB::connection('custom_mysql')->statement("CREATE TABLE {$destinationTable} ({$columnsDefinition})");
        DB::connection('custom_mysql')->statement("INSERT INTO {$destinationTable} SELECT * FROM {$sourceTable}");
        $this->info("Table {$sourceTable} has been copied to {$destinationTable} successfully.");
    }


    /**
     * @remark :routeMap去重
     * @name   :routeMapDeduplication
     * @author :lyh
     * @method :post
     * @time   :2024/5/28 11:31
     */
    public function routeMapDeduplication(){
        $routeMapModel = new RouteMap();
        $lists = $routeMapModel->list(['source'=>'product_keyword'],'id',['id'],'asc');
        foreach ($lists as $v){
            echo date('Y-m-d H:i:s') . '关键字id:'.$v['id'] . PHP_EOL;
            //查询当前数据详情
            $info = $routeMapModel->read(['id'=>$v['id']],['id','source_id']);
            if($info !== false){
                //删除其他一样的数据
                $routeMapModel->del(['id'=>['!=',$info['id']],'source'=>'product_keyword','source_id'=>$info['source_id']]);
            }
        }
        return true;
    }

    public function getBlog(){
        $blogModel = new Blog();
        $lists = $blogModel->list();
        foreach ($lists as $v){
            $route = RouteMap::setRoute($v['name'], RouteMap::SOURCE_BLOG, $v['id'], 1462);
            $blogModel->edit(['url'=>$route],['id'=>$v['id']]);
        }
        return true;
    }

    /**
     * @remark :更新优化设置表
     * @name   :updateProjectOp
     * @author :lyh
     * @method :post
     * @time   :2025/3/14 10:29
     */
    public function updateProjectOp(){
        //查询优化中台数据
        $projectModel = new Project();
        $list = $projectModel->list(['type'=>['in',[2,4]]],['id']);
        $rankDataModel = new RankDataLog();
        $opModel = new DeployOptimize();
        foreach ($list as $v){
            $rankInfo = $rankDataModel->where('project_id', $v['id'])->where('is_compliance',1)->where('lang','')->orderBy('created_at', 'asc')->select('id', 'date' ,'project_id','is_compliance' ,'created_at')->first();
            if(!empty($rankInfo)){
                echo date('Y-m-d H:i:s') . '项目id:'.$v['id'] .'首次达标时间'. $rankInfo['date'] . PHP_EOL;
                $opModel->edit(['first_compliance_time'=>$rankInfo['date']],['project_id'=>$v['id']]);
            }
        }
        return true;
    }

    /**
     * @remark :设置seo_前后缀
     * @name   :settingSeo
     * @author :lyh
     * @method :post
     * @time   :2025/4/15 15:14
     */
    public function settingSeo(){
        $list = (new Project())->formatQuery(['delete_status'=>0,'type'=>[2,4]])->pluck('id')->unique()->values()->toArray();
        foreach ($list as $val){
            ProjectServer::useProject($val);
            $seoModel = new WebSettingSeo();
            $results = $seoModel->whereRaw('product_category_prefix = product_category_suffix')->get();
            if(!empty($results)){
                echo '项目id:'.$val.PHP_EOL;
            }
            DB::disconnect('custom_mysql');
        }

    }
}