<?php

namespace App\Console\Commands;

use App\Models\Article;
use App\Models\MstTown;

class ImportLandArticleCommand extends BaseImportCommand
{
    protected $signature = 'import:land';
    protected $path = "excel/200528_land.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", [1,2,3])
            ->where("company_id", 2)
            ->delete();

        for ($i = 2; $i <= $highestRow; $i++) {
            if (in_array($i, [133, 1377])) {
                continue;
            }
            $dataRow = $this->getRowFromSheet($worksheet, $mapDataWithColumns, $i);
            $articleData = $this->createArticleData($dataRow);
            $article = Article::create($articleData);
            if ($article) {
                $pricesData = $this->getDataPriceHistory($dataRow);
                $this->createPriceHistories($article, $dataRow, $pricesData);

                $fields = $this->getDataCustomField($dataRow);
                $this->createCustomField($article, $fields);

                $this->createDataVendor($article, $dataRow);
            }
            $this->info("# {$i}/{$highestRow} " . date("Y-m-d H:i:s") . " article: {$article->building_id}");
        }
    }

    private function getDataPriceHistory($dataRow)
    {
        return [
            $this->getOnlyNumber($dataRow["history_1"]),
            $this->getOnlyNumber($dataRow["history_2"]),
            $this->getOnlyNumber($dataRow["history_3"]),
            $this->getOnlyNumber($dataRow["history_4"]),
            $this->getOnlyNumber($dataRow["history_5"]),
            $this->getOnlyNumber($dataRow["history_6"]),
            $this->getOnlyNumber($dataRow["history_7"]),
            $this->getOnlyNumber($dataRow["history_8"]),
            $this->getOnlyNumber($dataRow["history_9"]),
            $this->getOnlyNumber($dataRow["history_10"]),
            $this->getOnlyNumber($dataRow["price"])
        ];
    }

    private function getDataCustomField($dataRow)
    {
        return [
            "",
            "",
            $dataRow["custom_field_3"],
            $dataRow["custom_field_4"],
            $dataRow["custom_field_5"],
            $dataRow["custom_field_6"],
            $dataRow["custom_field_7"],
        ];
    }

    private function mapDataWithColumns()
    {
        return [
            'building_id' => 'A',
            'property' => 'B',
            'name' => 'C',
            'status' => 'D',
            'zip' => 'E',
            'address1' => 'F',
            'address2' => 'G',
            'address3' => 'H',
            'address4' => 'I',
            'lat' => 'J',
            'lng' => 'K',
            'main_traffic' => 'L',
            'main_traffic_line' => 'M',
            'main_traffic_station' => 'N',
            'main_traffic_time' => 'O',
            'main_traffic_bus_time' => 'P',
            'main_traffic_bus' => 'Q',
            'main_traffic_bus_walk' => 'R',
            'sub_traffic1' => 'S',
            'sub_traffic1_line' => 'T',
            'sub_traffic1_station' => 'U',
            'sub_traffic1_time' => 'V',
            'sub_traffic1_bus_time' => 'W',
            'sub_traffic1_bus' => 'X',
            'sub_traffic1_bus_walk' => 'Y',
            'sub_traffic2' => 'Z',
            'sub_traffic2_line' => 'AA',
            'sub_traffic2_station' => 'AB',
            'sub_traffic2_time' => 'AC',
            'sub_traffic2_bus_time' => 'AD',
            'sub_traffic2_bus' => 'AE',
            'sub_traffic2_bus_walk' => 'AF',
            'sub_traffic3' => 'AG',
            'sub_traffic3_line' => 'AH',
            'sub_traffic3_station' => 'AI',
            'sub_traffic3_time' => 'AJ',
            'sub_traffic3_bus_time' => 'AK',
            'sub_traffic3_bus' => 'AL',
            'sub_traffic3_bus_walk' => 'AM',
            'sub_traffic4' => 'AN',
            'sub_traffic4_line' => 'AO',
            'sub_traffic4_station' => 'AP',
            'sub_traffic4_time' => 'AQ',
            'sub_traffic4_bus_time' => 'AR',
            'sub_traffic4_bus' => 'AS',
            'sub_traffic4_bus_walk' => 'AT',
            'primary_school_city' => 'AU',
            'primary_school_school' => 'AV',
            'primary_school_distance' => 'AW',
            'secondary_school_city' => 'AX',
            'secondary_school_school' => 'AY',
            'secondary_school_distance' => 'AZ',
            'memo1' => 'BA',
            'memo2' => 'BB',
            'place_area' => 'BC',
            'custom_field_3' => 'BD',
            'custom_field_4' => 'BE',
            'custom_field_5' => 'BF',
            'custom_field_6' => 'BG',
            'custom_field_7' => 'BH',
            'created_at' => 'BI',
            'company_manner' => 'BJ',
            'price' => 'BK',
            'history_1' => 'BL',
            'history_2' => 'BM',
            'history_3' => 'BN',
            'history_4' => 'BO',
            'history_5' => 'BP',
            'history_6' => 'BQ',
            'history_7' => 'BR',
            'history_8' => 'BS',
            'history_9' => 'BT',
            'history_10' => 'BU',
            'land_right' => 'BV',
            'leasehold_kind' => 'BW',
            'land_area' => 'BX',
            'land_area_val' => 'BY',
            'kokudoho' => 'BZ',
            'land_not' => 'CA',
            'current_status' => 'CB',
            'land_delivery' => 'CC',
            'delivery_year' => 'CD',
            'delivery_month' => 'CE',
            'land_delivery_time' => 'CF',
            'move_in_conditions' => 'CG',
            'land_condition' => 'CH',
            'ground' => 'CI',
            'terrain' => 'CJ',
            'building_rate' => 'CK',
            'volume_rate' => 'CL',
            'road_burden' => 'CM',
            'road_burden_area' => 'CN',
            'land_direction1' => 'CO',
            'land_kind1' => 'CP',
            'road_width1' => 'CQ',
            'frontage1' => 'CR',
            'land_direction2' => 'CS',
            'land_kind2' => 'CT',
            'road_width2' => 'CU',
            'frontage2' => 'CV',
            'land_direction3' => 'CW',
            'land_kind3' => 'CX',
            'road_width3' => 'CY',
            'frontage3' => 'CZ',
            'water_supply' => 'DA',
            'sewerage' => 'DB',
            'gas' => 'DC',
            'use_area' => 'DD',
            'land_use' => 'DE',
            'city_plan' => 'DF',
            'section' => 'DG',
            'land_note' => 'DH',
            'other_comment' => 'DI',
            'classfication' => 'DJ',
            'vendor_id' => 'DK',
            'vendor_name' => 'DL',
            'vendor_manner' => 'DN',
            'vendor_charge' => 'DP',
            'own_company' => 'DQ',
            'homes' => 'DR',
            'members' => 'DS',
            'close_date' => 'DT',
            'conf_day' => 'DV',
            'price_closing' => 'DU',
        ];
    }

    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;

        $mainTrafficLine = $this->getTrafficLineByName(self::COMPANY_ID, $dataRow["main_traffic_line"]);
        $mainTrafficStation = $this->getStationByName(optional($mainTrafficLine)->id, $dataRow["main_traffic_station"]);

        $subTraffic1Line = $this->getTrafficLineByName(self::COMPANY_ID, $dataRow["sub_traffic1_line"]);
        $subTraffic1Station = $this->getStationByName(optional($subTraffic1Line)->id, $dataRow["sub_traffic1_station"]);

        $subTraffic2Line = $this->getTrafficLineByName(self::COMPANY_ID, $dataRow["sub_traffic2_line"]);
        $subTraffic2Station = $this->getStationByName(optional($subTraffic2Line)->id, $dataRow["sub_traffic2_station"]);

        $subTraffic3Line = $this->getTrafficLineByName(self::COMPANY_ID, $dataRow["sub_traffic3_line"]);
        $subTraffic3Station = $this->getStationByName(optional($subTraffic3Line)->id, $dataRow["sub_traffic3_station"]);

        $subTraffic4Line = $this->getTrafficLineByName(self::COMPANY_ID, $dataRow["sub_traffic4_line"]);
        $subTraffic4Station = $this->getStationByName(optional($subTraffic4Line)->id, $dataRow["sub_traffic4_station"]);

        $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 [
            "company_id" => self::COMPANY_ID,
            "building_id" => $dataRow["building_id"],
            'property' => 1,
            'name' => $dataRow["name"],
            'status' => $this->mapDataStatus($dataRow["status"]),
            'zip' => $dataRow["zip"],
            'address1' => $prefectureCode,
            'address2' => $cityCode,
            'address3' => $dataRow["address3"],
            'address4' => $dataRow["address4"],
            'lat' => $dataRow["lat"],
            'lng' => $dataRow["lng"],
            'main_traffic' => $this->mapDataTraffic($dataRow["main_traffic"]),
            'main_traffic_line' => $dataRow["main_traffic_line"],
            'main_traffic_station' => $dataRow["main_traffic_station"],
            'main_traffic_time' => $dataRow["main_traffic_time"],
            'main_traffic_bus_time' => $dataRow["main_traffic_bus_time"],
            'main_traffic_bus' => $dataRow["main_traffic_bus"],
            'main_traffic_bus_walk' => $dataRow["main_traffic_bus_walk"],
            'main_traffic_line_id' => optional($mainTrafficLine)->id,
            'main_traffic_station_id' => optional($mainTrafficStation)->id,
            'sub_traffic1' => $this->mapDataTraffic($dataRow["sub_traffic1"]),
            'sub_traffic1_line' => $dataRow["sub_traffic1_line"],
            'sub_traffic1_station' => $dataRow["sub_traffic1_station"],
            'sub_traffic1_time' => $dataRow["sub_traffic1_time"],
            'sub_traffic1_bus_time' => $dataRow["sub_traffic1_bus_time"],
            'sub_traffic1_bus' => $dataRow["sub_traffic1_bus"],
            'sub_traffic1_bus_walk' => $dataRow["sub_traffic1_bus_walk"],
            'sub_traffic1_line_id' => optional($subTraffic1Line)->id,
            'sub_traffic1_station_id' => optional($subTraffic1Station)->id,
            'sub_traffic2' => $this->mapDataTraffic($dataRow["sub_traffic2"]),
            'sub_traffic2_line' => $dataRow["sub_traffic2_line"],
            'sub_traffic2_station' => $dataRow["sub_traffic2_station"],
            'sub_traffic2_time' => $dataRow["sub_traffic2_time"],
            'sub_traffic2_bus_time' => $dataRow["sub_traffic2_bus_time"],
            'sub_traffic2_bus' => $dataRow["sub_traffic2_bus"],
            'sub_traffic2_bus_walk' => $dataRow["sub_traffic2_bus_walk"],
            'sub_traffic2_line_id' => optional($subTraffic2Line)->id,
            'sub_traffic2_station_id' => optional($subTraffic2Station)->id,
            'sub_traffic3' => $this->mapDataTraffic($dataRow["sub_traffic3"]),
            'sub_traffic3_line' => $dataRow["sub_traffic3_line"],
            'sub_traffic3_station' => $dataRow["sub_traffic3_station"],
            'sub_traffic3_time' => $dataRow["sub_traffic3_time"],
            'sub_traffic3_bus_time' => $dataRow["sub_traffic3_bus_time"],
            'sub_traffic3_bus' => $dataRow["sub_traffic3_bus"],
            'sub_traffic3_bus_walk' => $dataRow["sub_traffic3_bus_walk"],
            'sub_traffic3_line_id' => optional($subTraffic3Line)->id,
            'sub_traffic3_station_id' => optional($subTraffic3Station)->id,
            'sub_traffic4' => $this->mapDataTraffic($dataRow["sub_traffic4"]),
            'sub_traffic4_line' => $dataRow["sub_traffic4_line"],
            'sub_traffic4_station' => $dataRow["sub_traffic4_station"],
            'sub_traffic4_time' => $dataRow["sub_traffic4_time"],
            'sub_traffic4_bus_time' => $dataRow["sub_traffic4_bus_time"],
            'sub_traffic4_bus' => $dataRow["sub_traffic4_bus"],
            'sub_traffic4_bus_walk' => $dataRow["sub_traffic4_bus_walk"],
            'sub_traffic4_line_id' => optional($subTraffic4Line)->id,
            'sub_traffic4_station_id' => optional($subTraffic4Station)->id,
            '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"],
            'memo1' => $dataRow["memo1"],
            'memo2' => $dataRow["memo2"],
            'place_area' => $dataRow["place_area"],
            'created_at' => $dataRow["created_at"],
            'company_manner' => $this->mapDataCompanyManner($dataRow["company_manner"]),
            'price' => $dataRow["price"],
            'land_right' => $this->mapDataLandRight($dataRow["land_right"]),
            'leasehold_kind' => $this->mapDataLeaseholdKind($dataRow["leasehold_kind"]),
            'land_area' => $this->mapDataLandArea($dataRow["land_area"]),
            'land_area_val' => $dataRow["land_area_val"],
            'kokudoho' => $this->mapDataKokudoho($dataRow["kokudoho"]),
            'land_not' => empty($dataRow["land_not"]) ? "" : 1,
            'current_status' => $this->mapDataCurrentStatus($dataRow["current_status"]),
            'land_delivery' => $this->mapDataLandDelivery($dataRow["land_delivery"]),
            'delivery_year' => $dataRow["delivery_year"],
            'delivery_month' => $dataRow["delivery_month"],
            'land_delivery_time' => $this->mapDataLandDeliveryTime($dataRow["land_delivery_time"]),
            'move_in_conditions' => $dataRow["move_in_conditions"],
            'land_condition' => $this->mapDataLandCondition($dataRow["land_condition"]),
            'ground' => $this->mapDataGround($dataRow["ground"]),
            'terrain' => $this->mapDataTerrain($dataRow["terrain"]),
            'building_rate' => $dataRow["building_rate"],
            'volume_rate' => $dataRow["volume_rate"],
            'road_burden' => $this->mapDataRoadBurden($dataRow["road_burden"]),
            'road_burden_area' => $dataRow["road_burden_area"],
            'land_direction1' => $this->mapDataLandDirection($dataRow["land_direction1"]),
            'land_kind1' => $this->mapDataLandKind($dataRow["land_kind1"]),
            'road_width1' => $dataRow["road_width1"],
            'frontage1' => $dataRow["frontage1"],
            'land_direction2' => $this->mapDataLandDirection($dataRow["land_direction2"]),
            'land_kind2' => $this->mapDataLandKind($dataRow["land_kind2"]),
            'road_width2' => $dataRow["road_width2"],
            'frontage2' => $dataRow["frontage2"],
            'land_direction3' => $this->mapDataLandDirection($dataRow["land_direction3"]),
            'land_kind3' => $this->mapDataLandKind($dataRow["land_kind3"]),
            'road_width3' => $dataRow["road_width3"],
            'frontage3' => $dataRow["frontage3"],
            'water_supply' => $this->mapDataWaterSupply($dataRow["water_supply"]),
            'sewerage' => $this->mapDataSewerage($dataRow["sewerage"]),
            'gas' => $this->mapDataGas($dataRow["gas"]),
            'use_area' => $this->mapDataUseArea($dataRow["use_area"]),
            'land_use' => $this->mapDataUseArea($dataRow["land_use"]),
            'city_plan' => $this->mapDataCityPlan($dataRow["city_plan"]),
            'section' => $dataRow["section"],
            'land_note' => $dataRow["land_note"],
            'other_comment' => $dataRow["other_comment"],
            'classfication' => $this->mapDataClassfication($dataRow["classfication"]),
            'own_company' => $this->mapYesNo($dataRow["own_company"]),
            'homes' => $this->mapYesNo($dataRow["homes"]),
            'members' => $this->mapDataMembers($dataRow["members"]),
            'close_date' => $dataRow["close_date"],
            'conf_day' => $dataRow["conf_day"],
            'price_closing' => $dataRow["price_closing"],
        ];
    }
}