<?php

namespace App\Console\Commands;

use App\Models\MstCity;
use App\Models\MstLine;
use App\Models\MstSchool;
use App\Models\MstStation;
use App\Models\MstTown;

class ImportMetaDataCommand extends BaseImportCommand
{
    protected $signature = 'import:meta';
    protected $path = "excel/metadata.xlsx";
    const COMPANY_ID = 2;

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

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

        $this->createDataCity($spreadsheet);
        $this->createDataTown($spreadsheet);
        $this->createDataLine($spreadsheet);
        $this->createDataStation($spreadsheet);
        $this->createDataSchool($spreadsheet, true);
        $this->createDataSchool($spreadsheet, false);
    }

    private function createDataCity($spreadsheet)
    {
        $this->info("Start import City");
        MstCity::where("company_id", self::COMPANY_ID)->delete();

        $worksheet = $spreadsheet->getSheet(1);
        $highestRow = $worksheet->getHighestRow("A");
        $mapDataWithColumns = $this->mapDataCityWithColumns();

        for ($i = 2; $i <= $highestRow; $i++) {
            $dataRow = $this->getRowFromSheet($worksheet, $mapDataWithColumns, $i);
            $prefectureName = $dataRow["prefecture_name"];
            $cityName = $dataRow["city_name"];

            $prefecture = $this->getPrefectureByName(self::COMPANY_ID, $prefectureName);
            $this->getCityByName(self::COMPANY_ID, $prefecture->code, $cityName);
        }

        $this->info("Success import City");
    }

    private function createDataTown($spreadsheet)
    {
        $this->info("Start import Town");
        MstTown::where("company_id", self::COMPANY_ID)->delete();

        $worksheet = $spreadsheet->getSheet(2);
        $highestRow = $worksheet->getHighestRow("A");
        $mapDataWithColumns = $this->mapDataTownWithColumns();

        for ($i = 2; $i <= $highestRow; $i++) {
            $dataRow = $this->getRowFromSheet($worksheet, $mapDataWithColumns, $i);
            $prefectureName = $dataRow["prefecture_name"];
            $cityName = $dataRow["city_name"];
            $townName = $dataRow["town_name"];

            $prefecture = $this->getPrefectureByName(self::COMPANY_ID, $prefectureName);
            $city = $this->getCityByName(self::COMPANY_ID, $prefecture->code, $cityName);
            $this->getTownByName(self::COMPANY_ID, $prefecture->code, $city->code, $townName);
        }

        $this->info("Success import Town");
    }

    private function createDataLine($spreadsheet)
    {
        $this->info("Start import Line");
        MstLine::where("company_id", self::COMPANY_ID)->delete();

        $worksheet = $spreadsheet->getSheet(3);
        $highestRow = $worksheet->getHighestRow("A");
        $mapDataWithColumns = $this->mapDataLineWithColumns();

        for ($i = 2; $i <= $highestRow; $i++) {
            $dataRow = $this->getRowFromSheet($worksheet, $mapDataWithColumns, $i);
            $lineName = $dataRow["line_name"];

            $this->getTrafficLineByName(self::COMPANY_ID, $lineName);
        }

        $this->info("Success import Line");
    }

    private function createDataStation($spreadsheet)
    {
        $this->info("Start import Station");
        MstStation::where("area_id", "not like", "14%")->delete();

        $worksheet = $spreadsheet->getSheet(4);
        $highestRow = $worksheet->getHighestRow("A");
        $mapDataWithColumns = $this->mapDataStationWithColumns();

        for ($i = 2; $i <= $highestRow; $i++) {
            $dataRow = $this->getRowFromSheet($worksheet, $mapDataWithColumns, $i);
            $lineName = $dataRow["line_name"];
            $cityName = $dataRow["city_name"];
            $stationName = $dataRow["station_name"];

            $line = $this->getTrafficLineByName(self::COMPANY_ID, $lineName);
            $city = $this->getCityByNameAndCompany(self::COMPANY_ID, $cityName);
            $this->getTrafficStationByName(self::COMPANY_ID, $line->id, $city->code, $stationName);
        }

        $this->info("Success import Station");
    }

    private function createDataSchool($spreadsheet, $isPrimary = true)
    {
        $this->info("Start import School");

        if ($isPrimary) {
            MstSchool::where("company_id", self::COMPANY_ID)->delete();

            $type = 1;
            $worksheet = $spreadsheet->getSheet(5);
        } else {
            $type = 2;
            $worksheet = $spreadsheet->getSheet(6);
        }

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

        for ($i = 2; $i <= $highestRow; $i++) {
            $dataRow = $this->getRowFromSheet($worksheet, $mapDataWithColumns, $i);
            $prefectureName = $dataRow["prefecture_name"];
            $cityName = $dataRow["city_name"];
            $schoolName = $dataRow["school_name"];

            $prefecture = $this->getPrefectureByName(self::COMPANY_ID, $prefectureName);
            $city = $this->getCityByName(self::COMPANY_ID, $prefecture->code, $cityName);

            $this->getSchoolByName(self::COMPANY_ID, $prefecture->code, $city->code, $type, $schoolName);
        }

        $this->info("Success import " . ($isPrimary ? "Primary School" : "Secondary School"));
    }

    private function mapDataCityWithColumns()
    {
        return [
            "prefecture_name" => "A",
            "city_name" => "B"
        ];
    }

    private function mapDataTownWithColumns()
    {
        return [
            "prefecture_name" => "A",
            "city_name" => "B",
            "town_name" => "C"
        ];
    }

    private function mapDataLineWithColumns()
    {
        return [
            "line_name" => "A",
        ];
    }

    private function mapDataStationWithColumns()
    {
        return [
            "line_name" => "A",
            "station_name" => "B",
            "prefecture_name" => "C",
            "city_name" => "D",
        ];
    }

    private function mapDataSchoolWithColumns()
    {
        return [
            "prefecture_name" => "A",
            "city_name" => "B",
            "school_name" => "C",
        ];
    }
}