<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use App\Models\Article;
use App\Models\MstStore;
use App\Models\MstPrefecture;
use App\Models\MstCity;
use App\Models\MstTown;
use App\Models\MstStation;
use App\Models\MstSchool;
use Exception;

class insertMansionData extends Command
{
    protected $csvFilePath;

    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'batch:insertMansion';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Command description';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        try {
            // csv読み込み
            $file = $this->readCsv();
            foreach ($file as $line) {
                mb_convert_variables('UTF-8', 'sjis-win', $line);

                // 保存データの整形
                $article = new Article;
                $data = $this->createSaveData($line);
                $result = $article->fill($data)->save();
            }


        } catch (Exception  $e) {
            //DB::rollback();
            // dump($e);
            throw $e;
        }
    }

    public function readCsv() {
        // ファイルの読み込み
        $file = new \SplFileObject(storage_path('/csv/test2.csv'));
        $file->setFlags(
        \SplFileObject::READ_CSV |           // CSV 列として行を読み込む
        \SplFileObject::READ_AHEAD |       // 先読み/巻き戻しで読み出す。
        \SplFileObject::SKIP_EMPTY |        // 空行は読み飛ばす
        \SplFileObject::DROP_NEW_LINE   // 行末の改行を読み飛ばす
        );
        $file->seek(PHP_INT_MAX);
        $count = $file->key() + 1;
        dump($count);
        return $file;
    }

    public function createSaveData($line) {
        $cid = 1;
        $data = [];
        $building_id = Article::getMaxNo(1);
        if(empty($building_id)) {
            $building_id = 0;
        }
        $building_id++;

        $data['company_id'] = $cid;
        $data['property'] = 6;
        $data['building_id'] = $building_id;
        $data['name'] = $line[1];
        $data['name_apartment'] = $line[1];
        $shop_info = MstStore::getIdByName($cid, $line[2]);
        $shop = $shop_info['code']??null;
        $data['shop'] = $shop;
        $address_info = MstTown::getDataByAddress($line[5]);
        $address = $line[3].$line[4].$line[5];
        $zip = $this->getZipCode($address);
        $address1 = 0;
        $address2 = 0;
        $address3 = 0;
        if(isset($address_info[0]['pref_code'])) {
            $address1 = intval($address_info[0]['pref_code']);
        }
        if(isset($address_info[0]['pref_code'])) {
            $address2 = intval($address_info[0]['city_code']);
        }
        if(isset($address_info[0]['pref_code'])) {
            $address3 = intval($address_info[0]['code']);
        }
        $data['zip'] = $zip;
        $data['address1'] = $address1;
        $data['address2'] = $address2;
        $data['address3'] = $address3;
        $data['address4'] = $line[6];
        $data['lat'] = $line[7];
        $data['lng'] = $line[8];


        if($line[9] != "" && $line[10] != "") {
            if($line[9] == '横浜市営地下鉄ブルーライン') {
                $line[9] = '横浜市営ブルーライン';
            }
            $station_info = MstStation::getDataByName($line[9], $line[10]);
            $line_id = $station_info->line_id??null;
            $station_id = $station_info->id??null;

            if($line_id == null) {
                $line_name = null;
            } else {
                $line_name = $line[9];
            }
            if($station_id == null) {
                $station = null;
            } else {
                $station = $line[10];
            }
            
        } else {
            $line_name = null;
            $line_id = null;
            $station = null;
            $station_id = null;
        }
        
        $data['main_traffic_line'] = $line_name;
        $data['main_traffic_line_id'] = $line_id;
        $data['main_traffic_station'] = $station;
        $data['main_traffic_station_id'] = $station_id;
        $data['main_traffic_time'] = $line[11]??null;

        $data['main_traffic_bus_time'] = $line[12]??null;
        $data['main_traffic_bus'] = $line[13]??null;
        $data['main_traffic_bus_walk'] = $line[14]??null;

        if($line[15] != "" && $line[16] != "") {
            if($line[15] == '横浜市営地下鉄ブルーライン') {
                $line[15] = '横浜市営ブルーライン';
            }
            $station_info = MstStation::getDataByName($line[15], $line[16]);
            $line_id = $station_info->line_id??null;
            $station_id = $station_info->id??null;

            if($line_id == null) {
                $line_name = null;
            } else {
                $line_name = $line[15];
            }
            if($station_id == null) {
                $station = null;
            } else {
                $station = $line[16];
            }
        } else {
            $line_name = null;
            $line_id = null;
            $station = null;
            $station_id = null;
        }
        $data['sub_traffic1_line'] = $line_name;
        $data['sub_traffic1_line_id'] = $line_id;
        $data['sub_traffic1_station'] = $station;
        $data['sub_traffic1_station_id'] = $station_id;
        $data['sub_traffic1_time'] = $line[17]??null;

        $data['sub_traffic1_bus_time'] = $line[18]??null;
        $data['sub_traffic1_bus'] = $line[19]??null;
        $data['sub_traffic1_bus_walk'] = $line[20]??null;


        if($line[21] != "") {
            $school = MstSchool::getCodeByName($line[21]);
            $data['primary_school_school'] = $school->code??null;
        }
        if($line[22] != "") {
            $school = MstSchool::getCodeByName($line[22]);
            $data['primary_school2_school'] = $school->code??null;
        }
        if($line[23] != "") {
            $school = MstSchool::getCodeByName($line[23]);
            $data['secondary_school_school'] = $school->code??null;
        }
        if($line[24] != "") {
            $school = MstSchool::getCodeByName($line[24]);
            $data['secondary_school2_school'] = $school->code??null;
        }

        $land_right = null;
        if($line[25] == '所有権') {
            $land_right = 1;
        } elseif($line[25] == '借地権のみ') {
            $land_right = 2;
        } elseif($line[25] == '所有権・借地権混在') {
            $land_right = 3;
        }
        $data['land_right'] = $land_right;
        $data['land_area_val'] = $line[26]??null;
        $data['use_area'] = $line[27]??null;
        $data['land_use'] = $line[28]??null;
        $data['land_use2'] = $line[29]??null;
        $data['total_unit'] = $line[30]??null;
        preg_match('/(.*?)年(.*?)月/', $line[31], $match);
        // dump($match);
        $data['age_year'] = $match[1]??null;
        $data['age_month'] = $match[2]??null;
        $data['construction'] = $line[32]??null;
        $data['construction_sub'] = $line[33]??null;
        $data['floor'] = $line[34]??null;
        $data['underground'] = $line[35]??null;
        $data['sales_company'] = $line[36]??null;
        $data['construction_company'] = $line[37]??null;
        $data['management_company'] = $line[38]??null;
        $data['management_form'] = $line[39]??null;
        $pet = null;
        if($line[40] != "") {
            $pet = 1;
        }
        $data['pet'] = $pet;
        $data['pet_count'] = $line[41]??null;
        $data['comment'] = $line[42]??null;
        $data['provisional'] = 1;
        $data['primary_school_city'] = $line[43]??null;
        $data['primary_school2_city'] = $line[44]??null;
        $data['secondary_school_city'] = $line[45]??null;
        $data['secondary_school2_city'] = $line[46]??null;
        return $data;
    }

    public function getZipCode($address) {
        $res = "";
        $baseurl = "https://zipcoda.net/api/"; 
        
        $ch = curl_init(); //1.初期化
        curl_setopt($ch, CURLOPT_URL,$baseurl . '?address=' . $address); //2.URLをセット
        curl_setopt($ch, CURLOPT_CUSTOMREQUEST, 'GET'); //3.HTTP リクエストをセット（GET,POST等)
        curl_setopt($ch, CURLOPT_RETURNTRANSFER,true); //文字列で返す
        //curl_setopt($ch, CURLOPT_HEADER, true);   // ヘッダーも出力する
        
         //..4.その他必要オプション
        $result = curl_exec($ch); //5.実行して、レスポンスを取得
        // var_dump($result); // 出力
        // print("-------------------------------");
        $resultarr = json_decode($result,true); //jsonを配列に
        // var_dump($resultarr); //配列で出力
        
        curl_close($ch); // 6.終了

        if($resultarr['status'] == 'success') {
            if(isset($resultarr['items'][0]['zipcode'])) {
                $tmp = $resultarr['items'][0]['zipcode'];
                $res = substr($tmp, 0, 3).'-'.substr($tmp, 3);
            }
        }

        return $res;
        
    }
}
