<?php

namespace App\Console\Commands;

use App\Models\Article;

class ImportMansionArticleCommand extends BaseImportCommand
{
    protected $signature = 'import:mansion';
    protected $path = "excel/200528_mansion.xlsx";
    const COMPANY_ID = 2;

    public function __construct()
    {
        parent::__construct();
    }

    public function handle()
    {
        $path = storage_path($this->path);

        $spreadsheet = $this->loadSpreadsheet($path, true);

        $worksheet = $spreadsheet->getActiveSheet();

        $highestRow = $worksheet->getHighestRow("A");

        $mapDataWithColumns = $this->mapDataWithColumns();

        $this->info("Delete Old data");
        Article::whereIn("property", [6,7])
            ->delete();
        for ($i = 2; $i <= $highestRow; $i++) {
            $dataRow = $this->getRowFromSheet($worksheet, $mapDataWithColumns, $i);
            $articleData = $this->createArticleData($dataRow);
            $article = Article::create($articleData);
            $this->info("# {$i}/{$highestRow} " . date("Y-m-d H:i:s") . " article: {$article->building_id}");
        }
    }

    private function getTraffic($name, $dataRow)
    {
        $line = $this->getTrafficLineByName(self::COMPANY_ID, $dataRow[$name.'_line']);
        $station = $this->getStationByName(optional($line)->id, $dataRow[$name.'_station']);
        return [
            'line_id' => optional($line)->id,
            'station_id' => optional($station)->id,
        ];
    }

    private function mapDataWithColumns()
    {
        return [
            'building_id' => 'A',
            'name' => 'B',
            'property' => 'C',
            'property_sub' => 'D',
            'address1' => 'E',
            'address2' => 'F',
            'address3' => 'G',
            'address4' => 'H',
            'lat' => 'I',
            'lng' => 'J',
            'main_traffic_line' => 'K',
            'main_traffic_station' => 'L',
            'main_traffic_time' => 'M',
            'sub_traffic1_line' => 'N',
            'sub_traffic1_station' => 'O',
            'sub_traffic1_time' => 'P',
            'sub_traffic2_line' => 'Q',
            'sub_traffic2_station' => 'R',
            'sub_traffic2_time' => 'S',
            'sub_traffic3_line' => 'T',
            'sub_traffic3_station' => 'U',
            'sub_traffic3_time' => 'V',
            'sub_traffic4_line' => 'W',
            'sub_traffic4_station' => 'X',
            'sub_traffic4_time' => 'Y',
            'primary_school_city' => 'Z',
            'primary_school_school' => 'AA',
            'primary_school_distance' => 'AB',
            'secondary_school_city' => 'AC',
            'secondary_school_school' => 'AD',
            'secondary_school_distance' => 'AE',
            'memo2' => 'AF',
            'place_area' => 'AG',
            'use_area' => 'AH',
            'land_use' => 'AI',
            'total_unit' => 'AJ',
            'age_year' => 'AK',
            'age_month' => 'AL',
            'construction' => 'AM',
            'floor' => 'AN',
            'underground' => 'AO',
            'land_area_val' => 'AP',
            'construction_company' => 'AQ',
            'management_company' => 'AR',
            'management_form' => 'AS',
            'comment' => 'AT',
        ];
    }

    private function createArticleData($dataRow)
    {
        $prefecture = $this->getPrefectureByName(self::COMPANY_ID, $dataRow["address1"]);
        $prefectureCode = optional($prefecture)->code;

        $city = $this->getCityByName(self::COMPANY_ID, $prefectureCode, $dataRow["address2"]);
        $cityCode = optional($city)->code;

        $town = $this->getTownByName(self::COMPANY_ID, $prefectureCode, $cityCode, $dataRow["address3"]);
        $townCode = optional($town)->code;

        $traffic = $this->getTraffic("main_traffic", $dataRow);
        $traffic1 = $this->getTraffic("sub_traffic1", $dataRow);
        $traffic2 = $this->getTraffic("sub_traffic2", $dataRow);
        $traffic3 = $this->getTraffic("sub_traffic3", $dataRow);
        $traffic4 = $this->getTraffic("sub_traffic4", $dataRow);

        $primarySchool = $this->getESchool($dataRow, $dataRow["primary_school_city"], $dataRow["primary_school_school"], self::COMPANY_ID);
        $secondSchool = $this->getJSchool($dataRow, $dataRow["secondary_school_city"], $dataRow["secondary_school_school"], self::COMPANY_ID);

        return [
            "building_id" => $dataRow["building_id"],
            "name" => $dataRow["name"],
            "property" => $this->mapProperty($dataRow["property"]),
            "property_sub" => $this->mapPropertySub($dataRow["property_sub"]),
            "address1" => $prefectureCode,
            "address2" => $cityCode,
            "address3" => $dataRow["address3"],
            "address4" => $dataRow["address4"],
            "lat" => $dataRow["lat"],
            "lng" => $dataRow["lng"],
            "main_traffic_line" => $dataRow["main_traffic_line"],
            "main_traffic_line_id" => $traffic['line_id'],
            "main_traffic_station" => $dataRow["main_traffic_station"],
            "main_traffic_station_id" => $traffic['station_id'],
            "main_traffic_time" => $dataRow["main_traffic_time"],
            "sub_traffic1_line" => $dataRow["sub_traffic1_line"],
            "sub_traffic1_line_id" => $traffic1['line_id'],
            "sub_traffic1_station" => $dataRow["sub_traffic1_station"],
            "sub_traffic1_station_id" => $traffic1['station_id'],
            "sub_traffic1_time" => $dataRow["sub_traffic1_time"],
            "sub_traffic2_line" => $dataRow["sub_traffic2_line"],
            "sub_traffic2_line_id" => $traffic2['line_id'],
            "sub_traffic2_station" => $dataRow["sub_traffic2_station"],
            "sub_traffic2_station_id" => $traffic2['station_id'],
            "sub_traffic2_time" => $dataRow["sub_traffic2_time"],
            "sub_traffic3_line" => $dataRow["sub_traffic3_line"],
            "sub_traffic3_line_id" => $traffic3['line_id'],
            "sub_traffic3_station" => $dataRow["sub_traffic3_station"],
            "sub_traffic3_station_id" => $traffic3['station_id'],
            "sub_traffic3_time" => $dataRow["sub_traffic3_time"],
            "sub_traffic4_line" => $dataRow["sub_traffic4_line"],
            "sub_traffic4_line_id" => $traffic4['line_id'],
            "sub_traffic4_station" => $dataRow["sub_traffic4_station"],
            "sub_traffic4_station_id" => $traffic4['station_id'],
            "sub_traffic4_time" => $dataRow["sub_traffic4_time"],
            'primary_school_city' => $dataRow["primary_school_city"],
            'primary_school_school' => optional($primarySchool)->code,
            'primary_school_distance' => $dataRow["primary_school_distance"],
            'secondary_school_city' => $dataRow["secondary_school_city"],
            'secondary_school_school' => optional($secondSchool)->code,
            'secondary_school_distance' => $dataRow["secondary_school_distance"],
            "memo2" => $dataRow["memo2"],
            "use_area" => $this->mapDataUseArea($dataRow["use_area"]),
            "land_use" => $this->mapDataUseArea($dataRow["land_use"]),
            "place_area" => $dataRow["place_area"],
            "total_unit" => $dataRow["total_unit"],
            "age_year" => $dataRow["age_year"],
            "age_month" => $dataRow["age_month"],
            "construction" => $this->mapConstruction($dataRow["construction"]),
            "floor" => $dataRow["floor"],
            "underground" => $dataRow["underground"],
            "land_area_val" => $dataRow["land_area_val"],
            "construction_company" => $dataRow["construction_company"],
            "management_company" => $dataRow["management_company"],
            "comment" => $dataRow["comment"],
            "management_form" => $this->mapManagementForm($dataRow["management_form"]),
            "company_id" => self::COMPANY_ID
        ];
    }

    private function mapProperty($value)
    {
        $property = [
            "マンション" => 6,
        ];

        return $property[$value] ?? 6;
    }

    private function mapPropertySub($value)
    {
        $propertySub = [
            "新築" => 1,
            "中古" => 2,
        ];

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

    private function mapManagementForm($value)
    {
        $managementForm = [
            "委託(常駐)" => 1,
            "委託(通勤)" => 2,
            "委託(巡回)" => 3,
            "委託(勤務形態未定)" => 4,
            "自主管理" => 5,
            "未設定" => 0,
        ];

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

    private function mapConstruction($value)
    {
        $construction = [
            "未選択" => 0,
            "木造" => 1,
            "ブロック造" => 2,
            "鉄骨造" => 3,
            "RC造" => 4,
            "RCB造" => 11,
            "SRC造" => 5,
            "WRC造" => 10,
            "PC造" => 6,
            "HPC造" => 7,
            "軽量鉄骨造" => 8,
            "ALC造" => 9,
        ];

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