<?php

namespace App\Console\Commands;

use App\Models\Article;

class ImportRoomArticleCommand extends BaseImportCommand
{
    protected $signature = 'import:room';
    protected $path = "excel/200528_room.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::where("company_id", 2)
            ->whereNotNull('mansion_id')
            ->delete();
        for ($i = 2; $i <= $highestRow; $i++) {
            $dataRow = $this->getRowFromSheet($worksheet, $mapDataWithColumns, $i);
            $articleData = $this->createArticleData($dataRow);
            $mansion = Article::where("building_id", $articleData['mansion_id'])->where("company_id", 2)->first();

            if(!$mansion) {
                $this->warn('Mansion not found: '.$articleData['mansion_id']);
                continue;
            }

            $this->mergeMansionData($articleData, $mansion);

            $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 mergeMansionData(&$room, $mansion)
    {
        $fields = [
            "zip", "address1", "address2", "address3", "address4",
            "main_traffic", "main_traffic_line", "main_traffic_line_id", "main_traffic_station", "main_traffic_station_id",
            "main_traffic_time", "main_traffic_bus_time", "main_traffic_bus", "main_traffic_bus_walk",
            "sub_traffic1", "sub_traffic1_line", "sub_traffic1_line_id", "sub_traffic1_station",
            "sub_traffic1_station_id", "sub_traffic1_time", "sub_traffic1_bus_time", "sub_traffic1_bus", "sub_traffic1_bus_walk",
            "sub_traffic2", "sub_traffic2_line", "sub_traffic2_line_id", "sub_traffic2_station",
            "sub_traffic2_station_id", "sub_traffic2_time", "sub_traffic2_bus_time", "sub_traffic2_bus", "sub_traffic2_bus_walk",
            "sub_traffic3", "sub_traffic3_line", "sub_traffic3_line_id", "sub_traffic3_station",
            "sub_traffic3_station_id", "sub_traffic3_time", "sub_traffic3_bus_time", "sub_traffic3_bus", "sub_traffic3_bus_walk",
            "primary_school_city", "primary_school_school", "primary_school_distance",
            "secondary_school_city", "secondary_school_school", "secondary_school_distance",
        ];

        foreach ($fields as $field) {
            $room[$field] = $mansion->{$field};
        }
    }

    private function mapDataWithColumns()
    {
        return [
            'mansion_id' => 'A',
            'building_id' => 'B',
            'property' => 'C',
            'property_sub' => 'D',
            'name' => 'E',
            'room_num' => 'F',
            'status' => 'G',
            'memo1' => 'H',
            'memo2' => 'I',
            'place_area' => 'J',
            'custom_field_3' => 'K',
            'custom_field_4' => 'L',
            'custom_field_5' => 'M',
            'custom_field_6' => 'N',
            'custom_field_7' => 'O',
            'created_at' => 'P',
            'company_manner' => 'Q',
            'price' => 'R',
            'history_1' => 'S',
            'history_2' => 'T',
            'history_3' => 'U',
            'history_4' => 'V',
            'history_5' => 'W',
            'history_6' => 'X',
            'history_7' => 'Y',
            'history_8' => 'Z',
            'history_9' => 'AA',
            'history_10' => 'AB',
            'history_11' => 'AC',
            'history_12' => 'AD',
            'history_13' => 'AE',
            'history_14' => 'AF',
            'management' => 'AG',
            'management_cost' => 'AH',
            'management_cost_unit' => 'AI',
            'repair' => 'AJ',
            'repair_cost' => 'AK',
            'repair_cost_unit' => 'AL',
            'parking' => 'AM',
            'parking_yes' => 'AN',
            'parking_yes_cost_from' => 'AO',
            'parking_out' => 'AP',
            'total_area' => 'AQ',
            'total_area_val' => 'AR',
            'floor_plan' => 'AS',
            'floor_plan_type' => 'AT',
            'balcony_area' => 'AU',
            'balcony_direction' => 'AV',
            'balcony_area_val' => 'AW',
            'garden_area' => 'AX',
            'current_status' => 'AY',
            'move_in' => 'AZ',
            'move_in_year' => 'BA',
            'move_in_month' => 'BB',
            'move_in_conditions' => 'BC',
            'whereabouts' => 'BD',
            'classfication' => 'BE',
            'vendor_id' => 'BF',
            'vendor_name' => 'BG',
            'vendor_tel' => 'BH',
            'vendor_manner' => 'BI',
            'vendor_fax' => 'BJ',
            'vendor_charge' => 'BK',
            'own_company' => 'BL',
            'homes' => 'BM',
            'members' => 'BN',
            'close_date' => 'BO',
            'price_closing' => 'BP',
            'conf_day' => 'BQ',
        ];
    }

    private function createArticleData($dataRow)
    {
        return [
            'mansion_id' => $dataRow['mansion_id'],
            'building_id' => $dataRow['building_id'],
            'property' => null,
            'property_sub' => null,
            'name' => $dataRow['name'],
            'room_num' => $dataRow['room_num'],
            'status' => $this->mapDataStatus($dataRow['status']),
            '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'],
            'management' => $this->mapYesNoUnit($dataRow['management']),
            'management_cost' => $dataRow['management_cost'],
            'management_cost_unit' => $this->mapDateUnit($dataRow['management_cost_unit']),
            'repair' => $this->mapYesNoUnit($dataRow['repair']),
            'repair_cost' => $dataRow['repair_cost'],
            'repair_cost_unit' => $this->mapDateUnit($dataRow['repair_cost_unit']),
            'parking' => $this->mapParking($dataRow['parking']),
            'parking_yes' => $this->mapYesNoUnit($dataRow['parking_yes']),
            'parking_yes_cost_from' => $dataRow['parking_yes_cost_from'],
            'parking_out' => $this->mapYesNoUnit($dataRow['parking_out']),
            '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']),
            'balcony_area' => $this->mapYesNoUnit($dataRow['balcony_area']),
            'balcony_direction' => $this->mapBalconyDirection($dataRow['balcony_direction']),
            'balcony_area_val' => $dataRow['balcony_area_val'],
            'garden_area' => $this->mapYesNoUnit($dataRow['garden_area']),
            'current_status' => $this->mapCurrentStatus($dataRow['current_status']),
            'move_in' => $this->mapMoveIn($dataRow['move_in']),
            'move_in_year' => $dataRow['move_in_year'],
            'move_in_month' => $dataRow['move_in_month'],
            'move_in_conditions' => $dataRow['move_in_conditions'],
            'whereabouts' => $dataRow['whereabouts'],
            '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"],
            '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["history_10"]),
            $this->getOnlyNumber($dataRow["history_11"]),
            $this->getOnlyNumber($dataRow["history_12"]),
            $this->getOnlyNumber($dataRow["history_13"]),
            $this->getOnlyNumber($dataRow["history_14"]),
            $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 mapCompanyManner($value)
    {
        $manners = [
            "売主" => 1,
            "事業主・売主" => 2,
            "仲介(一般媒介)" => 3,
            "仲介(専任媒介)" => 4,
            "仲介(専属専任)" => 5,
            "販売提携(代理)" => 6,
            "販売提携(媒介)" => 7,
            "販売提携(復代理)" => 8,
            "先物" => 9,
        ];

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

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

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

    private function mapDateUnit($value)
    {
        $unit  = [
            "月" => 1,
            "年" => 2,
        ];

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

    private function mapParking($value)
    {
        $parking = [
            "無" => 1,
            "駐車場空無" => 2,
            "駐車場空有" => 3,
            "分譲駐車場(必購入)" => 4,
            "分譲駐車場(任意購入)" => 5,
            "専用使用権付駐車場" => 6,
            "未選択" => 0,
        ];

        return $parking[$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 mapBalconyDirection($value)
    {
        $balconyDirection = [
            "北" => 1,
            "北東" => 2,
            "東" => 3,
            "南東" => 4,
            "南" => 5,
            "南西" => 6,
            "西" => 7,
            "北西" => 8
        ];

        return $balconyDirection[$value] ?? 0;


    }

    private function mapCurrentStatus($value)
    {
        $currentStatus = [
            "住居中" => 10,
            "空室" => 11,
            "賃貸中" => 12,
            "不明" => 13
        ];

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

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

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