<?php

namespace App\Models;

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

class RainsDuplicateArticle extends Model
{
     protected $fillable = ['hold', 'building_id', 'company_id', 'rains_no', 'sale_no', 'mansion_id', 'property', 'property_sub', 'name', 'name_gouchi', 'name_portal', 'occupied', 'revenue', 'status', 'shop', 'zip', 'address1', 'address2', 'address3', 'address4', 'name_apartment', 'room_num', 'room_num_disp', 'lat', 'lng', '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', 'primary_school_city', 'primary_school_school', 'primary_school_distance', 'primary_school2_city', 'primary_school2_school', 'primary_school2_distance','secondary_school_city', 'secondary_school_school', 'secondary_school_distance','secondary_school2_city', 'secondary_school2_school', 'secondary_school2_distance', 'memo1', 'memo2', 'company_manner', 'price', 'price_closing', 'close_date', 'tax', 'council', 'council_presence', 'council_cost', 'council_cost_unit', 'spring', 'spring_presence', 'spring_cost', 'spring_cost_unit', 'spring_kind', 'another_cost_name1', 'another_cost1', 'another_cost_unit1', 'another_cost_name2', 'another_cost2', 'another_cost_unit2', 'broadcasting', 'broadcasting_presence', 'broadcasting_cost', 'broadcasting_fixed_presence', 'broadcasting_fixed_cost', 'broadcasting_fixed_cost_unit', 'internet', 'internet_presence', 'internet_cost', 'internet_fixed_presence', 'internet_fixed_cost', 'internet_fixed_cost_unit', 'catv', 'catv_presence', 'catv_cost', 'catv_fixed_presence', 'catv_fixed_cost', 'catv_fixed_cost_unit', 'total_unit', 'age_year', 'age_month', 'construction', 'construction_sub', 'floor', 'whereabouts', 'underground', 'maisonette', 'maisonette_from', 'maisonette_to', 'site_area', 'sales_company', 'construction_company', 'management_company', 'management_form', 'pet', 'pet_count', 'overview_comment', 'land_right', 'leasehold_kind', 'leasehold_rate', 'land_rent', 'land_rent_val', 'land_rent_unit', 'leasehold_period', 'leasehold_period_year', 'leasehold_period_month', 'right_cost', 'right_cost_val', 'deposit_cost', 'deposit_cost_val', 'security_deposit_cost', 'security_deposit_cost_val', 'other_leasehold', 'total_area', 'total_area_val', 'balcony_area', 'balcony_area_val', 'loof_balcony_area', 'loof_balcony_area_val', 'loof_balcony_area_cost', 'loof_balcony_area_unit', 'garden_area', 'garden_area_val', 'garden_area_cost', 'garden_area_unit', 'terrace_area', 'terrace_direction','terrace_area_val', 'terrace_area_cost', 'terrace_area_unit', 'land_area', 'land_area_val', 'total_area_val', 'land_condition1', 'land_condition_area1', 'land_condition_unit1', 'land_condition2', 'land_condition_area2', 'land_condition_unit2', 'land_condition3', 'land_condition_area3', 'building_condition1', 'building_condition_area1', 'building_condition2', 'building_condition_area2', 'building_condition3', 'building_condition_area3', 'building_condition4', 'building_condition_select', 'land_not', 'land_status', 'comp_year', 'comp_month', 'land_delivery', 'delivery_year', 'delivery_month', 'land_delivery_time', 'land_condition', 'land_condition_not', 'building_plan_place', 'building_plan_area', 'ground', 'ground_text', 'building_rate', 'volume_rate', 'road_burden', 'road_burden_area', 'road_numerator', 'road_denominator', 'easement', 'easement_area', 'land_kind1', 'land_direction1', 'road_width1', 'frontage1', 'land_kind2', 'land_direction2', 'road_width2', 'frontage2', 'land_kind3', 'land_direction3', 'road_width3', 'frontage3', 'water_supply', 'sewerage', 'gas', 'garage', 'set', 'set_area', 'use_area', 'land_use', 'land_use2', 'city_plan', 'city_plan_reason', 'section', 'develop_num', 'other_reason', 'other_comment', 'rebuilding', 'total_area', 'total_area_val', 'underground_area', 'underground_area_val', 'garage_area', 'garage_area_val', 'underground_garage_area', 'underground_garage_area_val', 'residence_area', 'residence_area_val', 'floor_plan', 'floor_plan_type', 'completed_year', 'completed_month', 'completed_contract_month', 'move_in', 'move_in_year', 'move_in_month', 'move_in_contract_month', 'current_status', 'current_status_year', 'current_status_month', 'building_construction', 'building_construction_sub', 'ground_unit', 'underground_unit', 'parking', 'parking_num', 'architecture_no', 'exterior', 'exterior_year', 'exterior_month', 'exterior_wall', 'exterior_roof', 'exterior_other', 'exterior_text', 'interior', 'interior_year', 'interior_month', 'interior_kitchen', 'interior_bathroom', 'interior_toilet', 'interior_wall', 'interior_floor', 'interior_all', 'interior_other', 'interior_text', 'classfication', 'use_method', 'use_method_text', 'trading_classfication', 'business_status', 'investment_status', 'investment_performance', 'investment_performance_unit', 'investment_interest', 'own_company', 'suumo', 'homes', 'athome', 'catchcopy', 'point', 'comment', 'hp_charge', 'floor_file_type', 'floor_file_path', 'floor_file_disp', 'sheet_file_path2', 'sheet_file_path3', 'sheet_file_type', 'event_category', 'event_schedule', 'event_day', 'from_event', 'to_event', 'from_hour', 'from_minute', 'to_hour', 'to_minute', 'reservation', 'event_comment', 'disp', 'provisional', 'recommend', 'recommend_num', 'repair_cost', 'repair_cost_unit', 'management_cost', 'management_cost_unit', 'original1', 'original2', 'original3', 'original4', 'original5', 'parking_yes', 'parking_yes_cost_from', 'parking_yes_cost_to', 'parking_yes_cost_range', 'parking_yes_cost_unit', 'parking_yes_cost_date', 'parking_require_cost', 'parking_require_management', 'parking_require_management_cost', 'parking_require_management_unit', 'parking_require_repair', 'parking_require_repair_cost', 'parking_require_repair_unit', 'parking_any', 'parking_any_cost_from', 'parking_any_cost_to', 'parking_any_cost_range', 'parking_any_management', 'parking_any_management_cost', 'parking_any_management_unit', 'parking_any_repair', 'parking_any_repair_cost', 'parking_any_repair_unit', 'parking_designated', 'parking_designated_cost', 'parking_designated_unit', 'parking_out', 'parking_out_cost_from', 'parking_out_cost_range', 'parking_out_cost_to', 'parking_out_cost_unit', 'parking_out_cost_date','regular_leased_registration','regular_leased_season','regular_leased_season_nen','regular_leased_cost','regular_leased_transfer','regular_leased_transfer_method','regular_leased_transfer_necessity','regular_leased_transfer_consent', 'del', 'first'];

    use SetConnection;
   //
    public static function getMaxNo($cid) {
        $result = RainsDuplicateArticle::where('company_id', $cid)->max('building_id');

        return $result;
    }

    public static function getData() {
        $article = RainsDuplicateArticle::query();
        $result = $article -> get();

        return $result;
    }

    public static function getCountHoldData($cid, $sid=null) {
        $article = RainsDuplicateArticle::query();
        $article -> where('company_id', '=', $cid);
        $article -> where('hold', '=', 1);
        if(!is_null($sid)) {
            $article -> where('shop', '=', $sid);
        }

        $result = $article->count();

        return $result;
    }

    public static function getCountData($cid) {
        $article = RainsDuplicateArticle::query();
        $result = $article -> where('company_id', '=', $cid)->count();

        return $result;
    }

    public static function getCountDuplicateData($cid, $shop=null) {
        $article = RainsDuplicateArticle::query();
        $article->where('company_id', '=', $cid);
        if(!is_null($shop)) {
            $article->where('shop', '=', $shop);
        }
        $result = $article->count();
        return $result;
    }

    public static function getDuplicateData($cid, $num, $hold = null, $sid=null) {
        if ($sid === "0") $sid = null;

        $article = RainsDuplicateArticle::query();
        $article -> select('rains_duplicate_articles.id', 'rains_no', 'rains_duplicate_articles.name as article_name', 'rains_duplicate_articles.name_gouchi', 'rains_duplicate_articles.property', 'rains_duplicate_articles.property_sub',
            'rains_duplicate_articles.address1', 'rains_duplicate_articles.address2', 'rains_duplicate_articles.address3', 'rains_duplicate_articles.address4', 'rains_duplicate_articles.name_apartment','rains_duplicate_articles.room_num',
            'mst_prefectures.name as pref_name', 'mst_cities.name as city_name', 'mst_towns.name as town_name', 'rains_duplicate_articles.address4 as address',
            'price', 'land_area_val', 'rains_duplicate_articles.floor_plan', 'rains_duplicate_articles.floor_plan_type', 'mst_floor_types.name as floor_type_text',
            'rains_duplicate_articles.main_traffic', 'rains_duplicate_articles.main_traffic_bus_time', 'rains_duplicate_articles.main_traffic_bus_walk', 'rains_duplicate_articles.main_traffic_bus',
            'rains_duplicate_articles.main_traffic_line', 'rains_duplicate_articles.main_traffic_station', 'rains_duplicate_articles.main_traffic_time', 'rains_duplicate_articles.company_manner',
            'rains_duplicate_articles.age_year', 'rains_duplicate_articles.age_month', 'rains_duplicate_articles.total_area_val',
            'rains_duplicate_articles.floor', 'rains_duplicate_articles.whereabouts',
            'rains_duplicate_vendors.name as vendor_name', 'rains_duplicate_vendors.vendor_charge as vendor_charge', 'rains_duplicate_vendors.vendor_tel1', 'rains_duplicate_vendors.vendor_fax'
        );

        $article -> leftJoin('relation_duplicate_vendor_articles', 'rains_duplicate_articles.rains_no', '=', 'relation_duplicate_vendor_articles.article_id')
                 -> leftJoin(env('DB_DATABASE').'.rains_duplicate_vendors', 'relation_duplicate_vendor_articles.vendor_id', '=', 'rains_duplicate_vendors.id')
                 -> leftJoin('mst_prefectures', 'mst_prefectures.code', '=', 'rains_duplicate_articles.address1')
                 -> leftJoin('mst_cities', function($join) {
                             $join->on('mst_cities.pref_code', '=', 'rains_duplicate_articles.address1')
                                ->on('mst_cities.code', '=', 'rains_duplicate_articles.address2');
                 })
                 -> leftJoin('mst_towns', function($join) {
                     $join->on('mst_towns.pref_code', '=', 'rains_duplicate_articles.address1')
                        ->on('mst_towns.city_code', '=', 'rains_duplicate_articles.address2')
                        ->on('mst_towns.code', '=', 'rains_duplicate_articles.address3');
                 })
                 -> leftJoin(env('DB_DATABASE').'.mst_floor_types', 'rains_duplicate_articles.floor_plan_type', '=', 'mst_floor_types.code');

        $holdValue = getConstant('RAIN_DUPLICATE_ARTICLE_HOLD');
        if($hold == null) {
            $article->where(function ($q) use ($holdValue) {
                $q->where('hold', '!=', $holdValue)->orWhereNull('hold');
            });
        } else {
            $article->where('hold', '=', $holdValue);
        }

        $article->where('rains_duplicate_articles.company_id', '=', $cid);
        $article->where('mst_cities.company_id', '=', $cid);

        if(!is_null($sid)) {
            $article -> where('shop', '=', $sid);
        } else {
            $article -> where('shop', '>', MstStore::max("code"));
        }

        $result = $article->orderBy('id', 'DESC')->paginate($num);

        return $result;
    }

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

    public function city()
    {
        return $this->belongsTo(MstCity::class, 'address2',  'code');
    }

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

    public function getNameWithGouchi(): ?string
    {
        $name = !empty($this->article_name) ? $this->article_name : $this->name;

        return getBukkenNameWithGouchi($name, $this->name_gouchi);
    }
}