<?php
/**
 * @remark :
 * @name   :lyhDemo.php
 * @author :lyh
 * @method :post
 * @time   :2025/3/24 9:38
 */

namespace App\Console\Commands\LyhTest;

use App\Helper\OaGlobalsoApi;
use App\Models\Ai\AiBlog;
use App\Models\Geo\GeoLink;
use App\Models\News\News;
use App\Models\Product\Category;
use App\Models\Project\AggregateKeywordAffix;
use App\Models\Project\AiBlogTask;
use App\Models\Project\DeployBuild;
use App\Models\Project\KeywordPrefix;
use App\Models\Project\Payment;
use App\Models\Project\Project;
use App\Models\Project\ProjectAiSetting;
use App\Models\Project\ProjectWhiteHatAffix;
use App\Models\RouteMap\RouteMap;
use App\Models\SeoSetting\LinkData;
use App\Models\Template\BTemplateMain;
use App\Models\Template\TemplateTypeMain;
use App\Models\WebSetting\Translate;
use App\Models\WebSetting\TranslateData;
use App\Models\WebSetting\WebSetting;
use App\Models\WorkOrder\TicketLog;
use App\Models\WorkOrder\Tickets;
use App\Services\AiBlogService;
use App\Services\CosService;
use App\Services\Geo\GeoService;
use App\Services\MidJourneyService;
use App\Services\ProjectServer;
use Hashids\Hashids;
use Illuminate\Console\Command;
use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\DB;

class lyhDemo extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'lyh_demo';

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

    public function handle(){
       return $this->_actionRoute();
    }

    public function _actionDa()
    {
        $geoLinkModel = new GeoLink();
        $lists = $geoLinkModel->list(['da'=>0,'time'=>null]);
        $geoService = new GeoService();
        foreach ($lists as $info){
            if(!empty($info['time'])){
                $start = Carbon::parse($info['time']);
                $end   = Carbon::parse(date('Y-m-d'));
                $diff = $start->diffInDays($end);
                if($diff >= 60){
                    $host = $this->getDomainWithWWW($info['url']);
                    $result = $geoService->daResult($host);
                }else{
                    continue;
                }
            }else{
                $host = $this->getDomainWithWWW($info['url']);
                $result = $geoService->daResult($host);
            }
            if(!isset($result['data']) || empty($result['data'])){
                $this->model->edit(['time'=>date('Y-m-d')], ['id'=>$info['id']]);
                continue;
            }
            $info['da'] = (int)$result['data']['mozDA'];//获取数据中的da值
            $this->model->edit(['time'=>date('Y-m-d'),'da'=>$info['da']], ['id'=>$info['id']]);
        }
    }

    public function getDomainWithWWW($url) {
        // 获取 host
        $host = parse_url($url, PHP_URL_HOST);
        // 去掉端口号等情况
        $host = preg_replace('/:\d+$/', '', $host);
        // 分割域名
        $parts = explode('.', $host);
        // 判断是几段
        $count = count($parts);
        // 如果只有两段，比如 fox8.com、theamericawatch.com，就拼接 www.
        if ($count === 2) {
            return 'www.' . $host;
        }
        return $host;
    }

    /**
     * @remark :查看路由是否为空
     * @name   :_actionRoute
     * @author :lyh
     * @method :post
     * @time   :2025/7/22 15:14
     */
    public function _actionRoute(){
        $projectModel = new Project();
        $lists = $projectModel->list(['type'=>['in',[1,2,3,4,6]]], 'id', ['id']);
        $geoLinkModel = new GeoLink();
        foreach ($lists as $item){
            echo date('Y-m-d H:i:s') . '开始--项目的id:'. $item['id'] . PHP_EOL;
            ProjectServer::useProject($item['project_id']);
            $linkDataModel = new LinkData();
            $linkList = $linkDataModel->list();
            foreach ($linkList as $link){
                $geoLinkModel->add([
                    'project_id'=>$item['id'],
                    'url'=>$link['url'],
                    'type'=>2,
                    'da'=>$link['da_values'],
                    'send_time'=>$link['send_time'],
                    'time'=>$link['time'],
                        ]);
            }
            DB::disconnect('custom_mysql');
        }
        return true;
    }

    public function _actionTemplateMain(){
        $data = [];
        $projectModel = new Project();
        $lists = $projectModel->list(['delete_status' => 0,'project_type'=>0,'extend_type'=>0,'type'=>['in',[2,3,4,6]]], 'id', ['id']);
        foreach ($lists as $val){
            echo date('Y-m-d H:i:s') . '开始--项目的id:'. $val['id'] . PHP_EOL;
            ProjectServer::useProject($val['id']);
            $bTemplateMainModel = new BTemplateMain();
            $info = $bTemplateMainModel->read(['type'=>2,'is_list'=>1],['id']);
            $categoryModel = new Category();
            $count = $categoryModel->counts(['id'=>['>',0]]);
            if(($info === false) && ($count > 0)){
                $mainModel = new TemplateTypeMain();
                $mainInfo = $mainModel->read(['type'=>2,'is_list'=>1]);
                $main_html = $mainInfo['main_html'];
                $main_css = "<style id='globalsojs-styles'></style>";
                //写入一条初始数据
                $bTemplateMainModel->addReturnId(['type'=>2,'is_list'=>1,'is_custom'=>0,'project_id'=>$val['id'],'main_html'=>$main_html,'main_css'=>$main_css]);
                $data[] = $val['id'];
            }
            DB::disconnect('custom_mysql');
            echo date('Y-m-d H:i:s') . '结束--项目的id:'. $val['id'] . PHP_EOL;
        }
        dd($data);
    }

    public function _action_ai_blog(){
        $projectModel = new Project();
        $lists = $projectModel->list(['delete_status' => 0,'extend_type'=>0,'type'=>['in',[2,3,4,6]]], 'id', ['id']);
        foreach ($lists as $val) {
            $aiSettingInfo = $this->getSetting($val['id']);
            if($aiSettingInfo === false){
                echo '当前项目未注册。'.$val['id'].PHP_EOL;
                continue;
            }
            $aiBlogTaskModel = new AiBlogTask();
            $blog_lists = $aiBlogTaskModel->list(['type'=>2,'status'=>2,'project_id'=>$val['id']]);
            if(empty($blog_lists)){
                echo '未获取到ao_blog'.PHP_EOL;
                continue;
            }
            echo date('Y-m-d H:i:s') . '开始--项目的id:'. $val['id'] . PHP_EOL;
            ProjectServer::useProject($val['id']);
            $aiBlogService = new AiBlogService($val['id']);
            foreach ($blog_lists as $item) {
                $aiBlogModel = new AiBlog();
                $aiBlogInfo = $aiBlogModel->read(['task_id' => $item['task_id']], ['id']);
                if ($aiBlogInfo === false) {
                    continue;
                }
                try {
                    $aiBlogService->task_id = $item['task_id'];
                    //拉取文章数据
                    $result = $aiBlogService->getDetail();
                    if (isset($result) && ($result['status'] == 200)) {
                        $aiBlogModel->edit(['seo_description' => $result['data']['description']], ['task_id' => $item['task_id']]);
                    }
                } catch (\Exception $e) {
                    echo '跳过。' . PHP_EOL;
                    continue;
                }
            }
            DB::disconnect('custom_mysql');
            echo date('Y-m-d H:i:s') . '结束--项目的id:'. $val['id'] . PHP_EOL;
        }
        return true;
    }

    public function _action(){
        $aiBlogTaskModel = new AiBlogTask();
        $lists = $aiBlogTaskModel->list(['type'=>2,'status'=>2,'project_id'=>467]);
        foreach ($lists as $item){
            echo date('Y-m-d H:i:s') . '开始--项目的id:'. $item['project_id'] . PHP_EOL;
            ProjectServer::useProject($item['project_id']);
            $aiBlogModel = new AiBlog();
            $aiBlogInfo = $aiBlogModel->read(['task_id'=>$item['task_id']],['id','route']);
            if($aiBlogInfo === false){
                continue;
            }
            try {
                $aiBlogService = new AiBlogService($item['project_id']);
                $aiBlogService->task_id = $item['task_id'];
                //拉取文章数据
                $result = $aiBlogService->getDetail();
                if(isset($result) && ($result['status'] == 200)){
                    $aiBlogModel->edit(['seo_description'=>$result['data']['description']],['task_id'=>$item['task_id']]);
                }
            }catch (\Exception $e){
                echo '跳过。'.PHP_EOL;
            }
            DB::disconnect('custom_mysql');
            echo date('Y-m-d H:i:s') . '结束--项目的id:'. $item['project_id'] . PHP_EOL;
        }
    }

    public function getSetting($project_id)
    {
        $projectAiSettingModel = new ProjectAiSetting();
        $aiSettingInfo = $projectAiSettingModel->read(['project_id'=>$project_id]);
        return $aiSettingInfo;
    }


    public function _actions() {
        echo '开始执行...' . PHP_EOL;
        ProjectServer::useProject(565);
        $table = 'gl_product_category';
        $connection = DB::connection('custom_mysql');
        $dbName = $connection->getDatabaseName();
        $brandsStr = 'Trimos,insize,Fowler,Mahr,Starrett,Tesa,Mitutoyo,Wixey,Shars,Fisso,Spi,Grizzly,Accumaster,Accuremote,Baker,Sharpe,Compac,Earth,Etalon,Federal,Fell,Fisso,Fowler,Gem,Grizzly,Husky,iGaging,Insize,Kanetec,Magnetics,Mighty,Mitutoyo,Noga,Pittsburgh,Procheck,Saker,Scherr Tumico,Shars,Shinwa,SPI,Starrett,Tesa,Technidea,Trimos,Verdict,Wyler,Wixey';
        $brands = array_unique(array_map('trim', explode(',', $brandsStr)));
        // 获取文本字段
        $columns = $connection->table('information_schema.columns')
            ->where('table_schema', $dbName)
            ->where('table_name', $table)
            ->whereIn('data_type', ['varchar', 'text', 'mediumtext', 'longtext'])
            ->pluck('column_name')
            ->toArray();
        if (empty($columns)) {
            echo "未找到文本字段，退出。" . PHP_EOL;
            return;
        }
        foreach ($columns as $field) {
            $sqlField = "`$field`";
            // 构造第一轮REPLACE表达式（原样品牌）
            $replaceExprRaw = $sqlField;
            foreach ($brands as $brand) {
                $safeBrand = addslashes($brand);
                $replaceExprRaw = "REPLACE(REPLACE($replaceExprRaw, '$safeBrand-', ''), '$safeBrand', '')";
            }
            // 构造第二轮REPLACE表达式（小写品牌）
            $lowerBrands = array_map('strtolower', $brands);
            $replaceExprLower = $sqlField;
            foreach ($lowerBrands as $brand) {
                $safeBrand = addslashes($brand);
                $replaceExprLower = "REPLACE(REPLACE($replaceExprLower, '$safeBrand-', ''), '$safeBrand', '')";
            }
            // 先执行原样品牌替换
            $sqlRaw = "UPDATE `$table` SET $sqlField = $replaceExprRaw WHERE $sqlField IS NOT NULL AND $sqlField != ''";
            echo "执行字段（品牌原样替换）：$field" . PHP_EOL;
            try {
                $connection->update($sqlRaw);
            } catch (\Exception $e) {
                echo "字段 $field 原样替换出错：" . $e->getMessage() . PHP_EOL;
            }
            // 再执行小写品牌替换
            $sqlLower = "UPDATE `$table` SET $sqlField = $replaceExprLower WHERE $sqlField IS NOT NULL AND $sqlField != ''";
            echo "执行字段（品牌小写替换）：$field" . PHP_EOL;
            try {
                $connection->update($sqlLower);
            } catch (\Exception $e) {
                echo "字段 $field 小写替换出错：" . $e->getMessage() . PHP_EOL;
            }
        }
        echo '执行结束' . PHP_EOL;
        DB::disconnect('custom_mysql');
    }

    public function translate_action()
    {
        ProjectServer::useProject(655);
        $this->model = new Translate();
        $lists = DB::connection('custom_mysql')->table('gl_translate_copy1')->where('url','!=','All')->get()->toArray();
        foreach ($lists as $item) {
            $item = (array)$item;
            $sourceInfo = $this->getRouteSource($item['url']);
            $info = $this->model->read(['language_id'=>$item['language_id'],'source_id'=>$sourceInfo['source_id'],'url'=>$item['url'],'project_id'=>655,'type'=>$item['type']]);
            if($info === false){
                $param = [
                    'type'=>$item['type'] ?? 1,
                    'project_id'=>655,
                    'url'=>$item['url'],
                    'language_id'=>$item['language_id'],
                    'alias'=>$item['alias'],
                    'source'=>$sourceInfo['source'],
                    'source_id'=>$sourceInfo['source_id'],
                    'is_list'=>$sourceInfo['is_list'],
                    'is_custom'=>$sourceInfo['is_custom'],
                    'page'=>$sendData['page'] ?? 0
                ];
                $id = $this->model->addReturnId($param);
                TranslateData::insert(['trans_id'=>$id,'data'=>$item['data']]);
            }else{
                TranslateData::where(['trans_id'=>$info['id']])->update(['data'=>$item['data']]);
            }
        }
        DB::disconnect('custom_mysql');
    }

    public function getRouteSource($route){
        $routes = $route;
        $data = ['source'=>0,'source_id'=>0,'is_list'=>0,'is_custom'=>0,'page'=>0];
        if(strtolower($route) == 'all'){
            return $data;
        }
        if($route == 'index' || $route == '/'){
            $data['source'] = 1;
            return $data;
        }
        $route = basename($route);
        $page = 0;
        if (is_numeric($route)) {
            $arr = explode('/',$routes);
            $page = $route;
            $route = $arr[0];
        }
        $routeModel = new RouteMap();
        $routeInfo = $routeModel->read(['route'=>$route]);
        if($routeInfo === false){
            if(in_array($route,['products','news','blog'])){
                //固定路由
                $data['page'] = $page;
                $data['is_list'] = 1;
                if($route == 'products'){
                    $data['source'] = 2;
                }elseif ($route == 'news'){
                    $data['source'] = 4;
                }else{
                    $data['source'] = 3;
                }
                return $data;
            }
            return $data;
        }
        $data = $this->resultData($routeInfo,$data);
        $data['page'] = $page;
        return $data;
    }

    public function resultData($routeInfo,$data){
        if($routeInfo['source'] == RouteMap::SOURCE_PAGE){
            if($routeInfo['source_id']){
                $data = ['source'=>9,'source_id'=>$routeInfo['source_id'],'is_list'=>0,'is_custom'=>0];
            }
        }
        if($routeInfo['source'] == RouteMap::SOURCE_PRODUCT){
            if($routeInfo['source_id']){
                $data = ['source'=>2,'source_id'=>$routeInfo['source_id'],'is_list'=>0,'is_custom'=>0];
            }
        }
        if($routeInfo['source'] == RouteMap::SOURCE_PRODUCT_CATE){
            if($routeInfo['source_id']){
                $data = ['source'=>2,'source_id'=>$routeInfo['source_id'],'is_list'=>1,'is_custom'=>0];
            }
        }
        if($routeInfo['source'] == RouteMap::SOURCE_BLOG){
            if($routeInfo['source_id']){
                $data = ['source'=>3,'source_id'=>$routeInfo['source_id'],'is_list'=>0,'is_custom'=>0];
            }
        }
        if($routeInfo['source'] == RouteMap::SOURCE_BLOG_CATE){
            if($routeInfo['source_id']){
                $data = ['source'=>3,'source_id'=>$routeInfo['source_id'],'is_list'=>1,'is_custom'=>0];
            }
        }
        if($routeInfo['source'] == RouteMap::SOURCE_NEWS){
            if($routeInfo['source_id']){
                $data = ['source'=>4,'source_id'=>$routeInfo['source_id'],'is_list'=>0,'is_custom'=>0];
            }
        }
        if($routeInfo['source'] == RouteMap::SOURCE_NEWS_CATE){
            if($routeInfo['source_id']){
                $data = ['source'=>4,'source_id'=>$routeInfo['source_id'],'is_list'=>1,'is_custom'=>0];
            }
        }
        if($routeInfo['source'] == RouteMap::SOURCE_PRODUCT_KEYWORD){
            if($routeInfo['source_id']){
                $data = ['source'=>8,'source_id'=>$routeInfo['source_id'],'is_list'=>0,'is_custom'=>0];
            }
        }
        if($routeInfo['source'] == RouteMap::SOURCE_MODULE){
            if($routeInfo['source_id']){
                $data = ['source'=>7,'source_id'=>$routeInfo['source_id'],'is_list'=>0,'is_custom'=>1];
            }
        }
        if($routeInfo['source'] == RouteMap::SOURCE_MODULE_CATE){
            if($routeInfo['source_id']){
                $data = ['source'=>7,'source_id'=>$routeInfo['source_id'],'is_list'=>1,'is_custom'=>1];
            }
        }
        return $data;
    }

}