<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use \App\Models\traits\SetConnection;

class Facility extends Model
{
    use SetConnection;

    protected $fillable = ['company_id', 'name', 'kind', 'zip', 'address1', 'address2', 'address3', 'address4',
        'lat', 'lng', 'memo', 'file_path', 'category', 'area', 'staff', 'tag', 'campaign', 'type', 'open',
        'peak', 'holiday', 'tel', 'budget', 'reserve', 'url', 'payment', 'parking', 'access', 'menu',
        'comment', 'target', 'breadth', 'facility', 'photo1', 'p1_comment', 'photo2', 'p2_comment',
        'photo3', 'p3_comment', 'photo4', 'p4_comment', 'photo5', 'p5_comment',
        'photo6', 'p6_comment', 'kokai', 'status'
    ];
    //
    protected $appends = ['image', "type_name"];

    public function scopeLocation($builder, $latitude, $longitude, $radius)
    {
        $R = 6371e3;
        $pi = pi();
        $radius = $radius*1000;
        $distanceLat = $radius/$R*180/$pi;
        $distanceLng = $radius/$R*180/$pi/cos($latitude*$pi/180);

        $minMaxLatitude = [$latitude - $distanceLat, $latitude + $distanceLat];
        $minMaxLongitude = [$longitude - $distanceLng, $longitude + $distanceLng];

        return $builder->whereBetween('lat', $minMaxLatitude)
            ->whereBetween('lng', $minMaxLongitude);
    }

    static public function getDataByCondition($condition, $num = null, $sort = null) {
        $result = [];
        $facility = Facility::query();
        $facility->select('facilities.*', 'mst_prefectures.name as pref_name', 'mst_cities.name as city_name', 'mst_towns.name as town_name');
        $facility-> leftJoin('mst_prefectures', 'mst_prefectures.code', '=', 'facilities.address1')
//                 -> leftJoin('mst_cities', 'mst_cities.code', '=', 'facilities.address2')
                -> leftJoin('mst_cities', function($join) {
                    $join->on('mst_cities.pref_code', '=', 'facilities.address1')
                        ->on('mst_cities.code', '=', 'facilities.address2');
                })
                 -> leftJoin('mst_towns', function($join) {
                     $join->on('mst_towns.pref_code', '=', 'facilities.address1')
                        ->on('mst_towns.city_code', '=', 'facilities.address2')
                        ->on('mst_towns.code', '=', 'facilities.address3');
                 });
        $facility->where('mst_cities.company_id', '=', auth()->user()->company_id);
        if(isset($condition["search_name"]) && !empty($condition["search_name"])) {
            $facility -> where('facilities.name', 'LIKE', '%'.$condition["search_name"].'%');
        }
        if(isset($condition['search_id']) && !empty($condition['search_id'])) {
            $facility -> where('facilities.id', $condition["search_id"]);
        }
        if(isset($condition["search_property"]) && !empty($condition["search_property"])) {
            $facility -> where('kind', $condition["search_property"]);
        }
        if(isset($condition["search_address1"]) && !empty($condition["search_address1"])) {
            $facility -> where('address1', '=', $condition["search_address1"]);
        }
        if(isset($condition["search_address2"]) && !empty($condition["search_address2"])) {
            $facility -> where('address2', '=', $condition["search_address2"]);
        }
        if(isset($condition["search_address3"]) && !empty($condition["search_address3"])) {
            $facility -> where('address3', '=', $condition["search_address3"]);
        }

        if(isset($condition["address1"]) && !empty($condition["address1"])) {
            $facility -> where('address1', '=', $condition["address1"]);
        }
        if(isset($condition["address2"]) && !empty($condition["address2"])) {
            $facility -> where('address2', '=', $condition["address2"]);
        }
        if(isset($condition["address3"]) && !empty($condition["address3"])) {
            $facility -> where('address3', '=', $condition["address3"]);
        }
        if(isset($condition["kind"]) && !empty($condition["kind"])) {
            $facility -> where('facilities.kind', '=', $condition["kind"]);
        }

//        if(isset($condition["list-address3"]) && !empty($condition["list-address3"])) {
//            $facility -> where('mst_towns.name', 'LIKE', '%'.$condition["list-address3"].'%');
//        }

        if(isset($condition["id"])) {
            $facility -> where('facilities.id', '=', $condition["id"]);
            $result = $facility->first();
            return $result;
        }

        if($sort != null) {
            $facility->orderBy('kind');
        }

        $facility->groupBy('facilities.id');
        if($num == null) {
            $result = $facility->get();
        } else {
            $result = $facility->paginate($num);
        }


        return $result;

    }

    public static function getDataById($id) {
        $article = Facility::query();
        //$result = $article->where('id', '=', $id)->where('company_id', '=', auth()->user()->company_id)->first();
        $result = $article->where('id', '=', $id)->first();
        return $result;
    }

    public static function getRelatonFacility($id) {
        $result = [];
        $facility = Facility::query();
        $facility->select('facilities.name', 'facilities.file_path', 'relation_article_facilities.distance', 'relation_article_facilities.time');
        $facility-> leftJoin('relation_article_facilities', 'relation_article_facilities.facility_id', '=', 'facilities.id');
        $facility -> where('relation_article_facilities.article_id', '=', $id);
        $result = $facility->get();

        return $result;
    }

    public static function getData($code='')
    {
        if($code==''){
            $bloods = Facility::get();

            $arrResult = array();
            foreach($bloods as $blood){
                 $arrResult[] = ['code' => $blood->id, 'name' => $blood->name];
            }
            return $arrResult;
        } else {
            $bloods = Facility::where('code', '=', $code)->get();

            $arrResult = array();
            foreach($bloods as $blood){
                 $arrResult[] = ['code' => $blood->id, 'name' => $blood->name];
            }
            return $arrResult;
        }
    }

    public static function getFacilityArea($cid) {
        $res = [];
        $facility = Facility::query();
        $facility->select('facilities.address2 as code', 'mst_cities.name as name');
        $facility->where('facilities.company_id', '=', $cid);
        $facility->where('mst_cities.company_id', '=', $cid);
        $facility->whereNotNull('facilities.address2');
        $facility->leftJoin('mst_cities', 'mst_cities.code', '=', 'facilities.address2');
        $facility->groupBy('facilities.address2');
        $results = $facility->get();

        foreach($results as $row){
            $res[] = ['code' => $row->code, 'name' => $row->name];
       }

       return $res;
    }

    public static function getFacilityProperty($cid, $area) {
        $res = [];
        $facility = Facility::query();
        $facility->select('facilities.kind as code', 'mst_facility_types.name as name');
        $facility->leftJoin(env('DB_DATABASE').'.mst_facility_types', 'mst_facility_types.code', '=', 'facilities.kind');
        //$facility->where('facilities.company_id', '=', $cid);
        $facility->where('facilities.address2', '=', $area);
        $facility->whereNotNull('facilities.kind');

        $facility->groupBy('facilities.kind');
        $res = $facility->get();
        $arrResult = array();
        foreach($res as $r){
            $arrResult[] = ['code' => $r->code, 'name' => $r->name];
        }

        /*$types = MstFacilityType::query();
        $types->where(function ($query) {
            $query->orWhere("id", "=", 14);
            $query->orWhere("id", "=", 15);
            $query->orWhere("id", "=", 16);
            $query->orWhere("id", "=", 17);
            $query->orWhere("id", "=", 19);
        });
        foreach($types->get() as $type){
            $arrResult[] = ['code' => $type->code, 'name' => $type->name];
        }*/
       return $arrResult;
    }

    public function getImageAttribute()
    {
        $image = $this->attributes["file_path"] ?? "";

        if (empty($image)) return "";

        return str_replace('public', '/storage', $image);
    }

    public function getTypeNameAttribute()
    {
        $kind = $this->attributes["kind"] ?? "";

        if (empty($kind)) return "";

        return optional(MstFacilityType::where("code", $kind)->first())->name;
    }

    public function prefecture()
    {
        return $this->belongsTo(MstPrefecture::class, 'address1',  'code');
    }

    public function city()
    {
        return $this->belongsTo(MstCity::class, 'address2',  'code')
            ->where('pref_code', $this->attributes["address1"]);
    }

    public function town()
    {
        return $this->belongsTo(MstTown::class, 'address3',  'code')
            ->where('pref_code', $this->attributes["address1"])
            ->where('city_code', $this->attributes["address2"]);
    }

    public function getAddressInfoAttribute(){
        $address = "";
        if (isset($this->prefecture)) {
            $address .= $this->prefecture->name;
        }
        if (isset($this->city)) {
            $address .= $this->city->name;
        }
        if (isset($this->town)) {
            $address .= $this->town->name.$this->town->chome_name.$this->town->koaza_name;
        }
        if (isset($this->address4)) {
            $address .= $this->address4;
        }
        if ($address === ''){
            $address = '-/-';
        }
        return $address;
    }

}