<?php

namespace App\Console\Commands;

use App\Models\Article;
use App\Models\RelationPriceHistory;

class ImportHouseArticleCommand extends BaseImportCommand
{
    protected $signature = 'import:house';
    protected $path = "excel/200529_house.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", [4,5])
            ->where("company_id", 2)
            ->delete();
        for ($i = 2; $i <= $highestRow; $i++) {
            $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 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',
            'property' => 'B',
            'property_sub' => 'C',
            'name' => 'D',
            'revenue' => 'E',
            'status' => 'F',
            'zip' => 'G',
            'address1' => 'H',
            'address2' => 'I',
            'address3' => 'J',
            'address4' => 'K',
            'lat' => 'L',
            'lng' => 'M',
            'main_traffic' => 'N',
            'main_traffic_line' => 'O',
            'main_traffic_station' => 'P',
            'main_traffic_time' => 'Q',
            'main_traffic_bus_time' => 'R',
            'main_traffic_bus' => 'S',
            'main_traffic_bus_walk' => 'T',
            'sub_traffic1' => 'U',
            'sub_traffic1_line' => 'V',
            'sub_traffic1_station' => 'W',
            'sub_traffic1_time' => 'X',
            'sub_traffic1_bus_time' => 'Y',
            'sub_traffic1_bus' => 'Z',
            'sub_traffic1_bus_walk' => 'AA',
            'sub_traffic2' => 'AB',
            'sub_traffic2_line' => 'AC',
            'sub_traffic2_station' => 'AD',
            'sub_traffic2_time' => 'AE',
            'sub_traffic2_bus_time' => 'AF',
            'sub_traffic2_bus' => 'AG',
            'sub_traffic2_bus_walk' => 'AH',
            'sub_traffic3' => 'AI',
            'sub_traffic3_line' => 'AJ',
            'sub_traffic3_station' => 'AK',
            'sub_traffic3_time' => 'AL',
            'sub_traffic3_bus_time' => 'AM',
            'sub_traffic3_bus' => 'AN',
            'sub_traffic3_bus_walk' => 'AO',
            'sub_traffic4' => 'AP',
            'sub_traffic4_line' => 'AQ',
            'sub_traffic4_station' => 'AR',
            'sub_traffic4_time' => 'AS',
            'sub_traffic4_bus_time' => 'AT',
            'sub_traffic4_bus' => 'AU',
            'sub_traffic4_bus_walk' => 'AV',
            'primary_school_city' => 'AX',
            'primary_school_school' => 'AY',
            'primary_school_distance' => 'AZ',
            'secondary_school_city' => 'BA',
            'secondary_school_school' => 'BB',
            'secondary_school_distance' => 'BC',
            'memo1' => 'BD',
            'memo2' => 'BE',
            'place_area' => 'BF',
            'custom_field_3' => 'BG',
            'custom_field_4' => 'BH',
            'custom_field_5' => 'BI',
            'custom_field_6' => 'BJ',
            'custom_field_7' => 'BK',
            'created_at' => 'BL',
            'company_manner' => 'BM',
            'price' => 'BN',
            'history_1' => 'BO',
            'history_2' => 'BP',
            'history_3' => 'BQ',
            'history_4' => 'BR',
            'history_5' => 'BS',
            'history_6' => 'BT',
            'history_7' => 'BU',
            'history_8' => 'BV',
            'history_9' => 'BW',
            'land_right' => 'BX',
            'leasehold_kind' => 'BY',
            'land_rent' => 'BZ',
            'land_rent_val' => 'CA',
            'leasehold_period_year' => 'CB',
            'land_area' => 'CC',
            'land_area_val' => 'CD',
            'kokudoho' => 'CE',
            'land_not' => 'CF',
            'terrain' => 'CG',
            'building_rate' => 'CH',
            'volume_rate' => 'CI',
            'road_burden' => 'CJ',
            'road_burden_area' => 'CK',
            'road_numerator' => 'CL',
            'road_denominator' => 'CM',
            'land_direction1' => 'CN',
            'land_kind1' => 'CO',
            'road_width1' => 'CP',
            'frontage1' => 'CQ',
            'land_direction2' => 'CR',
            'land_kind2' => 'CS',
            'road_width2' => 'CT',
            'frontage2' => 'CU',
            'land_direction3' => 'CV',
            'land_kind3' => 'CW',
            'road_width3' => 'CX',
            'frontage3' => 'CY',
            'water_supply' => 'CZ',
            'sewerage' => 'DA',
            'gas' => 'DB',
            'use_area' => 'DC',
            'land_use' => 'DD',
            'city_plan' => 'DE',
            'land_note' => 'DF',
            'total_area' => 'DG',
            'total_area_val' => 'DH',
            'floor_plan' => 'DI',
            'floor_plan_type' => 'DJ',
            'total_unit' => 'DK',
            'age_year' => 'DL',
            'age_month' => 'DM',
            'completed_year' => 'DN',
            'completed_month' => 'DO',
            'move_in' => 'DP',
            'move_in_year' => 'DQ',
            'move_in_month' => 'DR',
            'current_status' => 'DS',
            'move_in_conditions' => 'DT',
            'construction' => 'DU',
            'construction_sub' => 'DV',
            'floor' => 'DW',
            'underground' => 'DX',
            'parking' => 'DY',
            'parking_num' => 'DZ',
            'architecture_no' => 'EA',
            'build_note' => 'EB',
            'other_comment' => 'EC',
            'classfication' => 'ED',
            'use_method' => 'EE',
            'use_method_text' => 'EF',
            'business_status' => 'EG',
            'investment_performance' => 'EH',
            'investment_performance_unit' => 'EI',
            'investment_interest' => 'EJ',
            'vendor_id' => 'EK',
            'vendor_name' => 'EL',
            'vendor_tel' => 'EM',
            'vendor_fax' => 'EN',
            'vendor_manner' => 'EO',
            'vendor_charge' => 'EP',
            'own_company' => 'EQ',
            'homes' => 'ER',
            'members' => 'ES',
            'close_date' => 'ET',
            'price_closing' => 'EU',
            'conf_day' => 'EV',
        ];
    }

    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'],
            'property' => $this->mapProperty($dataRow['property']),
            'property_sub' => $this->mapPropertySub($dataRow['property_sub']),
            'name' => $dataRow['name'],
            'revenue' => empty($dataRow["revenue"]) ? "" : 1,
            '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_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'],
            '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'],
            'sub_traffic1' => $this->mapDataTraffic($dataRow['sub_traffic1']),
            '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_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_traffic2' => $this->mapDataTraffic($dataRow['sub_traffic2']),
            '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_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_traffic3' => $this->mapDataTraffic($dataRow['sub_traffic3']),
            '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_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_traffic4' => $this->mapDataTraffic($dataRow['sub_traffic4']),
            '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'],
            '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'],
            '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->mapCompanyManner($dataRow['company_manner']),
            'price' => $dataRow['price'],
            'land_right' => $this->mapDataLandRight($dataRow['land_right']),
            'leasehold_kind' => $this->mapDataLeaseholdKind($dataRow['leasehold_kind']),
            'land_rent' => $this->mapLandRent($dataRow['land_rent']),
            'land_rent_val' => $dataRow['land_rent_val'],
            'leasehold_period_year' => $dataRow['leasehold_period_year'],
            '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,
            '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"],
            'road_numerator' => $dataRow["road_numerator"],
            'road_denominator' => $dataRow["road_denominator"],
            '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"]),
            'land_note' => $dataRow["land_note"],
            'total_area' => $this->mapTotalArea($dataRow["total_area"]),
            'total_area_val' => $dataRow["total_area_val"],
            'floor_plan' => $dataRow['floor_plan'],
            'floor_plan_type' => $this->mapFloorPlanType($dataRow['floor_plan_type']),
            'total_unit' => $dataRow['total_unit'],
            'age_year' => $dataRow['age_year'],
            'age_month' => $dataRow['age_month'],
            'completed_year' => $dataRow['completed_year'],
            'completed_month' => $dataRow['completed_month'],
            'move_in' => $this->mapMoveIn($dataRow['move_in']),
            'move_in_year' => $dataRow['move_in_year'],
            'move_in_month' => $dataRow['move_in_month'],
            'current_status' => $this->mapDataCurrentStatus($dataRow["current_status"]),
            'move_in_conditions' => $dataRow['move_in_conditions'],
            'construction' => $this->mapConstruction($dataRow['construction']),
            'construction_sub' => $this->mapConstruction($dataRow['construction_sub']),
            'floor' => $dataRow['floor'],
            'underground' => $dataRow['underground'],
            'parking' => $this->mapParking($dataRow['parking']),
            'parking_num' => $dataRow['parking_num'],
            'architecture_no' => $dataRow["architecture_no"],
            'build_note' => $dataRow["build_note"],
            'other_comment' => $dataRow["other_comment"],
            'classfication' => $this->mapDataClassfication($dataRow["classfication"]),
            'use_method' => $this->mapUseMethod($dataRow['use_method']),
            'use_method_text' => $dataRow["use_method_text"],
            'business_status' => $this->mapBusinessStatus($dataRow['business_status']),
            'investment_performance' => $dataRow["investment_performance"],
            'investment_performance_unit' => $this->mapInvestmentPerformanceUnit($dataRow['investment_performance_unit']),
            'investment_interest' => $dataRow["investment_interest"],
            'own_company' => $this->mapYesNo($dataRow["own_company"]),
            'homes' => $this->mapYesNo($dataRow["homes"]),
            'members' => $this->mapDataMembers($dataRow["members"]),
            'close_date' => $dataRow["close_date"],
            'price_closing' => $dataRow["price_closing"],
            'conf_day' => $dataRow["conf_day"],
            'company_id' => self::COMPANY_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["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 mapProperty($value)
    {
        $property = [
            "一戸建て" => 4,
            "テラスハウス" => 5,
        ];

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

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

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

    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;
    }

    private function mapLandRent($value)
    {
        $landRent = [
            "有" => 1,
            "無" => 0,
        ];

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

    private function mapTotalArea($value)
    {
        $areas = [
            "登記" => 1,
            "実測" => 2
        ];

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

    private function mapFloorPlanType($value)
    {
        $floorPlanType = [
            "DK" => 1,
            "LDK" => 2,
            "R" => 3,
            "K" => 4,
            "SK" => 5,
            "SDK" => 6,
            "LK" => 7,
            "SLK" => 8,
            "SLDK" => 9
        ];

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

    private function mapMoveIn($value)
    {
        $moveIn = [
            "即引渡し可" => 1,
            "相談" => 2,
            "指定有" => 3,
            "契約後" => 4,
            "未選択" => 0,
        ];

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

    private function mapParking($value)
    {
        $parking = [
            "無" => 1,
            "掘込車庫" => 8,
            "車庫" => 9,
            "地下車庫" => 10,
            "カースペース" => 11,
            "カーポート" => 12,
            "未設定" => 0,
        ];

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

    private function mapUseMethod($value)
    {
        $useMethod = [
            "店舗" => 4,
            "事務所" => 5,
            "倉庫" => 8,
            "工場" => 7,
            "その他" => 9,
            "未選択" => 0,
        ];

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

    private function mapBusinessStatus($value)
    {
        $businessStatus = [
            "造作譲渡あり" => 1,
            "造作譲渡なし" => 2,
            "設備無償譲渡" => 3,
            "スケルトン渡し" => 4,
            "未選択" => 0,
        ];

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

    private function mapInvestmentPerformanceUnit($value)
    {
        $investmentPerformanceUnit = [
            '月' => 1,
            '年' => 2,
        ];

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

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

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