<?php

namespace App\Console\Commands;

use App\Models\MstCity;
use App\Models\MstLine;
use App\Models\MstPrefecture;
use App\Models\MstSchool;
use App\Models\MstStation;
use App\Models\MstTown;
use App\Models\RelationPriceHistory;
use App\Models\RelationVendorArticle;
use App\Models\Vendor;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Str;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\RichText\RichText;

class BaseImportCommand extends Command
{
    protected $signature = 'import:base';

    protected function getPrefectureByName($companyId, $prefectureName)
    {
        $prefecture =  MstPrefecture::where('company_id', $companyId)
            ->where('name', $prefectureName)
            ->first();

        if(empty($prefecture)) {
            $code = MstPrefecture::max('code') ?? 0;

            $prefecture = MstPrefecture::create([
                'company_id' => $companyId,
                'code' => $code + 1,
                'name' => $prefectureName
            ]);
        }

        return $prefecture;
    }

    protected function getCityByName($companyId, $prefectureCode, $cityName)
    {
        $city = MstCity::where('company_id', $companyId)
            ->where('pref_code', $prefectureCode)
            ->where('name', $cityName)
            ->first();

        if(empty($city)) {
            $code = MstCity::max('code') ?? 0;

            $city = MstCity::create([
                'company_id' => $companyId,
                'pref_code' => $prefectureCode,
                'code' => $code + 1,
                'name' => $cityName
            ]);
        }

        return $city;
    }

    protected function getTownByName($companyId, $prefectureCode, $cityCode, $townName)
    {
        $town = MstTown::where('company_id', $companyId)
            ->where('pref_code', $prefectureCode)
            ->where('city_code', $cityCode)
            ->where('name', $townName)
            ->first();

        if(empty($town)) {
            $code = MstTown::max('code') ?? 0;

            $town = MstTown::create([
                'company_id' => $companyId,
                'pref_code' => $prefectureCode,
                'city_code' => $cityCode,
                'code' => $code + 1,
                'name' => $townName
            ]);
        }

        return $town;
    }

    protected function getTrafficLineByName($companyId, $trafficLineName)
    {
        $line = MstLine::where('company_id', $companyId)
            ->where('name', $trafficLineName)
            ->first();

        if(empty($line)) {
            $line = MstLine::create([
                'company_id' => $companyId,
                'name' => $trafficLineName
            ]);
        }

        return $line;
    }


    protected function getTrafficStationByName($companyId, $trafficLineId, $cityCode, $trafficStationName)
    {
        $trafficLine = MstLine::find($trafficLineId);

        $city = MstCity::where('company_id', $companyId)
            ->where('code', $cityCode)
            ->first();

        $areaId = $city->pref_code.$city->code;

        $station = MstStation::where('line_id', $trafficLine->id)
            ->where('area_id', $areaId)
            ->where('name', $trafficStationName)
            ->first();

        if(empty($station)) {
            $station = MstStation::create([
                'line_id' => $trafficLine->id,
                'line' => $trafficLine->name,
                'area_id' => $areaId,
                'name' => $trafficStationName
            ]);
        }

        return $station;
    }

    protected function getStationByName($trafficLineId, $trafficStationName)
    {
        $trafficLine = MstLine::find($trafficLineId);
        $station = MstStation::where('line_id', $trafficLine->id)
            ->where('name', $trafficStationName)
            ->first();

        if(empty($station)) {
            $station = MstStation::create([
                'line_id' => $trafficLine->id,
                'line' => $trafficLine->name,
                'name' => $trafficStationName
            ]);
        }

        return $station;
    }

    protected function getESchool($dataRow, $cityName, $schoolName, $companyId)
    {
        if (empty($schoolName)) return null;

        $school = MstSchool::ESchool()
            ->where("name", "like", "%{$schoolName}%")
            ->whereHas("city", function ($query) use ($cityName) {
                $query->where("name", "like", "%{$cityName}%");
            })
            ->where("company_id", $companyId)
            ->first();

        if (empty($school)) {
            die($dataRow["building_id"]);
        }

        return $school;
    }

    protected function getJSchool($dataRow, $cityName, $schoolName, $companyId)
    {
        if (empty($schoolName)) return null;

        $school = MstSchool::JSchool()
            ->where("name", "like", "%{$schoolName}%")
            ->whereHas("city", function ($query) use ($cityName) {
                $query->where("name", "like", "%{$cityName}%");
            })
            ->where("company_id", $companyId)
            ->first();

        if (empty($school)) {
            die($dataRow["building_id"]);
        }

        return $school;
    }

    protected function getSchoolByName($companyId, $prefCode, $cityCode, $type, $schoolName)
    {
        $school = MstSchool::where('type', $type)
            ->where('company_id', $companyId)
            ->where('pref_id', $prefCode)
            ->where('city_id', $cityCode)
            ->where('name', $schoolName)
            ->first();

        if (empty($school)) {
            $code = MstSchool::max('code') ?? 0;

            if (empty($code)) {
                $code = 0;
            }

            $school = new MstSchool();
            $school->fill([
                "pref_id" => $prefCode,
                "city_id" => $cityCode,
                "code" => $code + 1,
                "name" => $schoolName,
                "type" => $type,
                "company_id" => $companyId
            ]);
            $school->save();
        }

        return $school;
    }

    protected function getRowFromSheet($worksheet, $properties, $rowIndex)
    {
        $row = [];
        $dateColumns = ["created_at", "close_date", "conf_day"];

        foreach ($properties as $property => $colIndex) {
            $cell = $worksheet->getCell($colIndex.$rowIndex);
            $value = $cell->getValue();
            $isDateColumn = in_array($property, $dateColumns);

            if (empty($value)) {
                $row[$property] = null;
                continue;
            }

            if ($isDateColumn && \PHPExcel_Shared_Date::isDateTime($cell)) {
                $dateTimeObject = \PHPExcel_Shared_Date::ExcelToPHPObject($cell->getValue());
                $row[$property] = $dateTimeObject->format('Y-m-d H:i:s');
                continue;
            }

            if ($property == "address4" && \PHPExcel_Shared_Date::isDateTime($cell)) {
                try {
                    $dateTimeObject = \PHPExcel_Shared_Date::ExcelToPHPObject($cell->getValue());
                    $row[$property] = $dateTimeObject->format('d-m');
                } catch (\Error $error) {
                    $row[$property] = $value;
                } catch (\Exception $exception) {
                    $row[$property] = $value;
                }
                continue;
            }

            if($value instanceof RichText) {
                $row[$property] = $value->getPlainText();
            } else {
                $row[$property] = $value;
            }

            $row[$property] = trim($row[$property]);
        }

        return $row;
    }

    protected function loadSpreadsheet($path, $isPHPExcel = false)
    {
        $this->line('Loading excel file: '.$path);

        if ($isPHPExcel) {
            $excel = \PHPExcel_IOFactory::createReaderForFile($path);

            return $excel->load($path);
        }

        return IOFactory::load($path);
    }

    protected function saveSpreadsheet($spreadsheet, $path)
    {
        $pathParts = pathinfo($path);
        $extension = Str::studly($pathParts['extension']);

        $writer = IOFactory::createWriter($spreadsheet, $extension);

        return $writer->save($path);
    }

    protected function getCityByNameAndCompany($companyId, $cityName)
    {
        return MstCity::where('company_id', $companyId)
            ->where('name', $cityName)
            ->first();
    }

    // ステータス
    protected function mapDataStatus($value)
    {
        $status = [
            "販売中" => 1,
            "商談中" => 2,
            "契約予定" => 3,
            "契約済" => 4,
            "売りやめ" => 5,
            "保留" => 6
        ];

        return $status[$value] ?? 1;
    }

    protected function mapDataTraffic($value)
    {
        if ($value == "バス") return 2;

        return 1;
    }

    // 取引態様
    protected function mapDataCompanyManner($value)
    {
        $manners = [
            "売主" => 1,
            "事業主・売主" => 2,
            "仲介(一般媒介)" => 3,
            "仲介(専任媒介)" => 4,
            "仲介(専属専任)" => 5,
            "販売提携(代理)" => 6,
            "販売提携(媒介)" => 7,
            "販売提携(復代理)" => 8,
            "先物" => 9,
        ];

        return $manners[$value] ?? "";
    }

    // 土地権利
    protected function mapDataLandRight($value)
    {
        $landRights = [
            "所有権" => 1,
            "借地権のみ" => 2,
            "所有権・借地権混在" => 3,
            "未選択" => 0,
        ];

        return $landRights[$value] ?? 0;
    }

    // 借地権種別
    protected function mapDataLeaseholdKind($value)
    {
        $kinds = [
            "旧法賃借権" => 1,
            "普通賃借権" => 2,
            "一般定期賃借権" => 3,
            "建物譲渡特約付き定期賃借権" => 4,
            "旧法地上権" => 5,
            "普通地上権" => 6,
            "一般定期地上権" => 7,
            "建物譲渡特約付き定期地上権" => 8,
        ];

        return $kinds[$value] ?? 0;
    }

    // 土地面積
    protected function mapDataLandArea($value)
    {
        $areas = [
            "登記" => 1,
            "実測" => 2
        ];

        return $areas[$value] ?? 0;
    }

    // 国土法
    protected function mapDataKokudoho($value)
    {
        $kokudohos = [
            "届出要" => 1,
            "届出不要" => 2
        ];

        return $kokudohos[$value] ?? 0;
    }

    // 現況
    protected function mapDataCurrentStatus($value)
    {
        $status = [
            "更地" => 1,
            "古家有" => 2,
            "未造成" => 3,
            "居住中" => 4,
            "空家" => 5,
            "賃貸中" => 6,
            "建築中" => 7,
            "完成済" => 8,
            "未着工" => 9
        ];

        return $status[$value] ?? 0;
    }

    // 引渡し
    protected function mapDataLandDelivery($value)
    {
        $deliveries = [
            "即可能" => 1,
            "要相談" => 2,
            "指定有" => 3
        ];

        return $deliveries[$value] ?? 0;
    }

    // 引渡し（上中下旬）
    protected function mapDataLandDeliveryTime($value)
    {
        $times = [
            "初旬" => 1,
            "上旬" => 2,
            "中旬" => 3,
            "下旬" => 4,
            "末" => 5,
        ];

        return $times[$value] ?? 0;
    }

    // 建築条件
    protected function mapDataLandCondition($value)
    {
        $conditions = [
            "条件なし" => 0,
            "建築条件付き" => 1,
        ];

        return $conditions[$value] ?? 2;
    }

    // 地目
    protected function mapDataGround($value)
    {
        $conditions = [
            "宅地" => 1,
            "田" => 2,
            "畑" => 3,
            "山林" => 4,
            "雑種地" => 5,
            "原野" => 6,
            "その他" => 7,
        ];

        return $conditions[$value] ?? 0;
    }

    // 地勢
    protected function mapDataTerrain($value)
    {
        $terrains = [
            "平坦" => 1,
            "高台" => 2,
            "低地" => 3,
            "ひな壇" => 4,
            "傾斜地" => 5,
            "その他" => 9,
        ];

        return $terrains[$value] ?? 0;
    }

    // 私道負担
    protected function mapDataRoadBurden($value)
    {
        $terrains = [
            "無" => 0,
            "有" => 1,
            "共有" => 2,
        ];

        return $terrains[$value] ?? 3;
    }

    // 向き
    protected function mapDataLandDirection($value)
    {
        $directions = [
            "北" => 1,
            "北東" => 2,
            "東" => 3,
            "南東" => 4,
            "南" => 5,
            "南西" => 6,
            "西" => 7,
            "北西" => 8,
        ];

        return $directions[$value] ?? 0;
    }

    // 通路 側
    protected function mapDataLandKind($value)
    {
        $kinds = [
            "公道" => 1,
            "私道" => 2
        ];

        return $kinds[$value] ?? 0;
    }

    // 上水道
    protected function mapDataWaterSupply($value)
    {
        $waters = [
            "公営水道" => 1,
            "私設水道" => 2,
            "井戸" => 3
        ];

        return $waters[$value] ?? 0;
    }

    // 下水道
    protected function mapDataSewerage($value)
    {
        $sewerages = [
            "本下水" => 1,
            "集中浄化槽" => 2,
            "個別浄化槽" => 3
        ];

        return $sewerages[$value] ?? 0;
    }

    // ガス・オール電化
    protected function mapDataGas($value)
    {
        $gas = [
            "都市ガス" => 1,
            "集中LPG" => 2,
            "個別ＬＰＧ" => 3,
            "オール電化" => 4,
        ];

        return $gas[$value] ?? 0;
    }

    // 用途地域1
    protected function mapDataUseArea($name)
    {
        $area = [
            "1種低層" => 1,
            "2種低層" => 10,
            "1種中高" => 11,
            "2種中高" => 2,
            "1種住居" => 12,
            "2種住居" => 3,
            "準住居" => 13,
            "近隣商業" => 4,
            "商業" => 5,
            "準工業" => 6,
            "工業" => 7,
            "工専" => 8,
            "田園住居地域" => 14,
            "無" => 99,
        ];

        return $area[$name] ?? 99;
    }

    // 都市計画
    protected function mapDataCityPlan($name)
    {
        $plans = [
            "市街化区域" => 1,
            "調整区域" => 2,
            "非線引区域" => 3,
            "区域外" => 4,
            "準都市区域" => 5,
            "市街化調整区域" => 2,
            "未線引区域" => 3
        ];

        return $plans[$name] ?? 1;
    }

    // 区分
    protected function mapDataClassfication($name)
    {
        $class = [
            "居住用" => 1,
            "事業用" => 2,
        ];

        return $class[$name] ?? 1;
    }

    protected function mapYesNo($value)
    {
        if ($value == "未") return 1;
        if ($value == "入稿") return 2;

        return 1;
    }

    // 会員物件
    protected function mapDataMembers($name)
    {
        $members = [
            "全員に公開" => 0,
            "会員限定" => 1,
        ];

        return $members[$name] ?? 0;
    }

    protected function createDataVendor($article, $dataRow)
    {
        $vendorId = $dataRow["vendor_id"];
        $article->relationVendors()->delete();

        if (empty($vendorId)) {
            return null;
        }

        $vendor = Vendor::where("no", $vendorId)
            ->where("company_id", $article->company_id)
            ->first();

        if (empty($vendor)) {
            Log::error("Sai-kyoto: vendor empty {$vendorId}");
            return null;
        }

        $relation = RelationVendorArticle::where("article_id", $dataRow["building_id"])
            ->where("vendor_id", $vendor->id)
            ->where("company_id", $article->company_id)
            ->first();

        $vendorRelation = [];
        $vendorRelation['article_id'] = $dataRow["building_id"];
        $vendorRelation['vendor_id'] = $vendor->id;
        $vendorRelation['company_id'] = $article->company_id;
        $vendorRelation['charge'] = $dataRow["vendor_charge"];
        $vendorRelation['manner'] = $this->mapVendorManner($dataRow["vendor_manner"]);

        if (is_null($relation)) {
            $vendor_obj = new RelationVendorArticle;
            $vendor_obj->fill($vendorRelation)->save();
        } else {
            $relation->update($vendorRelation);
        }
    }

    private function mapVendorManner($value)
    {
        $manners = [
            "売主" => 1,
            "販売提携(代理)" => 6,
            "仲介(一般媒介)" => 3,
            "仲介(専任媒介)" => 4,
            "仲介(専属専任)" => 5,
        ];

        return $manners[$value] ?? null;
    }

    protected function createCustomField($article, $fields)
    {
        $article->customs()->delete();

        $data = [
            "type" => 1,
            "code" => 1,
            "contents" => "",
        ];

        foreach ($fields as $key => $field) {
            if (empty($field)) {
                continue;
            }

            $data["code"] = $key + 1;
            $data["contents"] = $field;
            $data["company_id"] = $article->company_id;
            $article->customs()->create($data);
        }
    }

    protected function getOnlyNumber($string)
    {
        preg_match_all('/\d+/', $string, $matches);
        return $matches[0][0] ?? "";
    }

    protected function createPriceHistories($article, $dataRow, $prices)
    {
        $article->prices()->delete();

        foreach ($prices as $price) {
            if (empty($price)) {
                continue;
            }

            $history = new RelationPriceHistory();
            $history->fill([
                "article_id" => $dataRow["building_id"],
                "price" => $price,
                "company_id" => $article->company_id
            ]);

            $history->save();
        }
    }
}