<?php

namespace Hilco\Models;
use Carbon\Carbon;
use Eloquent;
use Illuminate\Database\Eloquent\Collection;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\HasMany;
use Illuminate\Database\Eloquent\Relations\HasManyThrough;
use Illuminate\Database\Eloquent\Relations\HasOne;
use Illuminate\Database\Eloquent\Relations\HasOneThrough;
use Illuminate\Database\Query\Builder;
use Illuminate\Support\Facades\DB;


/**
 * Hilco\Models\Customer
 *
 * @property integer $id
 * @property string $cust_no
 * @property string $cust_name
 * @property string $aff_cust_no
 * @property integer $aff_customer_id
 * @property string $aff_cust_name
 * @property string $addr_1
 * @property string $addr_2
 * @property string $addr_3
 * @property string $addr_4
 * @property string $addr_5
 * @property string $city
 * @property string $state
 * @property string $zip_postal
 * @property string $country
 * @property string $bill_addr_1
 * @property string $bill_addr_2
 * @property string $bill_addr_3
 * @property string $bill_addr_4
 * @property string $bill_addr_5
 * @property string $bill_city
 * @property string $bill_state
 * @property string $bill_zip_postal
 * @property string $bill_country
 * @property string $category
 * @property integer $customercategory_id
 * @property string $incept_dt
 * @property string $currency
 * @property integer $websilo_id
 * @property boolean $is_de_domestic
 * @property string $service_charge_code
 * @property string $date_created
 * @property string $date_modified
 * @property string $date_uploaded
 * @property string $deleted_at
 * @property-read CustomerSegment $customerSegment
 * @property-read Collection|Division[] $divisions
 * @property-read Collection|CustomerPhone[] $phones
 * @property-read Collection|CustomerEmail[] $emails
 * @property-read CustomerCategory $customer_category
 * @property-read Collection|Contact[] $contacts
 * @property-read Collection|CustomerDiscount[] $discounts
 * @property-read Collection|Order[] $orders
 * @property-read Collection|CustomerPharmacyInfo[] $pharmacyInfos
 * @property-read Collection|Customer_PharmacyLicense[] $customerPharmacyLicenses
 * @property-read BusinessChain_Customer $businessChainCustomer
 * @property-read BusinessChain $businessChain
 * @property-read BusinessChain $validBusinessChain
 * @property-read mixed $id_string
 * @method static Builder|Customer whereId($value)
 * @method static Builder|Customer whereCustNo($value)
 * @method static Builder|Customer whereCustName($value)
 * @method static Builder|Customer whereAffCustNo($value)
 * @method static Builder|Customer whereAffCustomerId($value)
 * @method static Builder|Customer whereAffCustName($value)
 * @method static Builder|Customer whereAddr1($value)
 * @method static Builder|Customer whereAddr2($value)
 * @method static Builder|Customer whereAddr3($value)
 * @method static Builder|Customer whereAddr4($value)
 * @method static Builder|Customer whereAddr5($value)
 * @method static Builder|Customer whereCity($value)
 * @method static Builder|Customer whereState($value)
 * @method static Builder|Customer whereZipPostal($value)
 * @method static Builder|Customer whereCountry($value)
 * @method static Builder|Customer whereCategory($value)
 * @method static Builder|Customer whereCustomercategoryId($value)
 * @method static Builder|Customer whereInceptDt($value)
 * @method static Builder|Customer whereCurrency($value)
 * @method static Builder|Customer whereDateCreated($value)
 * @method static Builder|Customer whereDateModified($value)
 * @method static Builder|Customer whereDateUploaded($value)
 * @method static Builder|Customer whereDeletedAt($value)
 * @mixin Eloquent
 */
class Customer extends Model {
	protected $table = 'Customers';
	protected $fillable = ['name', 'country', 'state', 'city', 'zip_postal', 'addr_1', 'addr_2', 'addr_3','currency','cust_name'];

	public static $currencies = [
		'USD'=>'USD',
		'NZD'=>'NZD',
		'JPY'=>'JPY',
		'GBP'=>'GBP',
		'CNY'=>'CNY',
		'CAD'=>'CAD',
		'AUD'=>'AUD'
	];
	const UPDATED_AT = 'date_modified';
	
	const HIDDEN = 'HIDDEN';
	const GERMAN_DOMESTIC_WEB_SERVICE_CHARGE_CODE = '33';
    const GERMAN_DOMESTIC_STANDARD_SERVICE_CHARGE_CODE = '01';

    use \Hilco\SoftDeletes;

    public static $perEnvironment = true;
    public static $objectIdKey = 'cust_no';

    public function getSolrRecord() {
        $record = [
            'id' => $this->id,
            'cust_name' => $this->cust_name,
            'cust_no' => $this->cust_no,
            'legacy_cust_no' => $this->legacy_cust_no
        ];
        return $record;
    }
    
    public function solrTable()
    {
        return $this->hasMany(Customer_Solr::class, 'customer_id', 'id');
    }

    public function getSolrClassAttribute() {
        return Customer_Solr::class;
    }

    public function getSolrIDFieldAttribute() {
        return 'customer_id';
    }

	public function webUser()
	{
		return $this->hasOne(WebUser::class, 'customer_id');
	}

	public function webUsers() {
        return $this->hasMany(WebUser::class, 'customer_id');
    }

	public function shippingAddresses() {
		return $this->hasMany(CustomerShippingAddress::class, 'cust_no', 'cust_no');
	}

	public function availableShippingAddresses() {
        return $this->hasMany(CustomerShippingAddress::class, 'cust_no', 'cust_no')->hasShipToProfile();
    }

	public function contacts() {
	    return $this->hasMany(Contact::class, 'customer_id', 'id');
    }

    public function customerEmails() {
        return $this->hasMany(CustomerEmail::class, 'customer_id', 'id');
    }

    public function jobsonDatas() {
        return $this->belongsToMany(JobsonData::class, 'Customer_JobsonData', 'customer_id', 'jobsondata_id');
    }

    public function getLatestCall() {
        return Call::where('customer_id', $this->id)->orderBy('start_time', 'desc')->first();
    }

	public static $priority = [
		'A'	=> [
			'from' => 2000,
			'to'   => 9999999
		],
		'B'	=> [
			'from' => 1000,
			'to'   => 1999
		],
		'C'	=> [
			'from' => 400,
			'to'   => 999
		],
		'D'	=> [
			'from' => 0,
			'to'   => 399
		]
	];

    public function getActiveSegmentAttribute() {
        return $this->segments->where('active', 1)->first();
    }

    public function getDefaultShippingAddressAttribute() {
        $segment = $this->active_segment;
        if (is_null($segment)) {
            return null;
        } else {
            if (is_null($segment->address) || is_null($segment->address()->isActive()->first())) {
                return $segment->shipping_addresses()->isActive()->first();
            } else {
                return $segment->address;
            }
        }
    }

	public function activeSegments()
	{
		return $this->segments()->where('CustomerSegments.active', '=', 1);
	}

	public function pharmacyInfos(): HasManyThrough {
		return $this->hasManyThrough(CustomerPharmacyInfo::class, Customer_PharmacyLicense::class, 'customer_id', 'id', 'id', 'customerpharmacyinfo_id');
	}

    public function customerPharmacyLicenses(): HasMany {
        return $this->hasMany(Customer_PharmacyLicense::class, 'customer_id', 'id');
    }

	public function segments() {
		return $this->hasMany(CustomerSegment::class, 'customer_id', 'id');
	}

	public function activeDivisions()
	{
		return $this
            ->belongsToMany(Division::class, 'CustomerSegments', 'customer_id', 'division_id')
            ->where('CustomerSegments.active', '=', 1)
            ->where('CustomerSegments.deleted_at', '=', DB::raw(0));
	}

	public function divisions()
	{
		return $this->belongsToMany(Division::class, 'CustomerSegments', 'customer_id', 'division_id');
	}

	public function phones()
	{
		return $this->hasMany(CustomerPhone::class);
	}

	public function emails()
	{
		return $this->hasMany(CustomerEmail::class);
	}

//	public function customer_category() {
//	    return $this->customerCategory;
//    }

	public function customerCategory()
	{
		return $this->belongsTo(CustomerCategory::class, 'customercategory_id');
	}


	public function discounts()
	{
		return $this->hasMany(CustomerDiscount::class);
	}

	public function customerStateTax(){
		return $this->belongsTo(StateTax::class,'state','state')
			->where(function($query){
				$query->where(function($query){
					return $query->where('effective_date','<=', date('Y-m-d',time()))
						->where('expiration_date','>=', date('Y-m-d',time()));
				})
					->orWhere('expiration_date', '000-00-00');
			});
	}

	public function customerVATTax(){

	}

	public function orders()
	{
		return $this->hasMany(Order::class, 'soldto_customer_id');
	}

	public function salesStats()
	{
		return $this->hasMany(CustomerSalesStats::class, 'customer_id');
	}

	public function stateTax() {
		return StateTax::where("state", $this->state)->inEffect()->first();
	}

	public function scopeWithEmails($query) {
		return $query->addSelect('email_raw', 'good_email', 'email_note', 'email_news', 'email_sales', 'email_ship')
			->join('CustomerEmails', 'Customers.id', '=', 'CustomerEmails.customer_id')
			;
	}

    /**
     * @return \Illuminate\Database\Eloquent\Relations\HasMany
     */
	public function webCustomerSalesSummaries() {
	    return $this->hasMany(WebCustomerSalesSummary::class, 'customer_id', 'id');
    }

    public function webCustomerLimits() {
        return $this->hasMany(WebCustomerLimit::class, 'customer_id', 'id');
    }

    public function recentOrders($count = 5) {
        return $this
            ->orders()
            ->submitted()
            ->with('orderLineSchedules.shipment')
            ->orderBy('order_date', 'desc')
            ->take($count)
            ->get();
    }

    public function topPurchasedItems($count = 5) {
        $ids = WebFamily::webVisible()
            ->join('WebPart_WebFamily', 'WebPart_WebFamily.webfamily_id', '=', 'WebFamilies.id')
            ->join('WebParts', 'WebParts.id', '=', 'WebPart_WebFamily.webpart_id')
            ->join('SalesOrderLines', function($join) {
                $join
                    ->on('SalesOrderLines.part_id', '=', 'WebParts.part_id');
            })
            ->join('SalesOrders', function($join) {
                $join
                    ->on('SalesOrders.id', '=', 'SalesOrderLines.salesorder_id');
            })
            ->leftJoin('SalesOrderLineSchedules', function($join) {
                $join
                    ->on('SalesOrderLineSchedules.salesorder_id', '=', 'SalesOrders.id')
                    ->on('SalesOrderLineSchedules.salesorderline_id', '=', 'SalesOrderLines.id');
            })
            ->where('SalesOrders.deleted_at', '=', DB::raw('0'))
            ->where('SalesOrderLines.deleted_at', '=', DB::raw('0'))
            ->where(function ($where) {
                $where
                    ->whereNull('SalesOrderLineSchedules.deleted_at')
                    ->orWhere('SalesOrderLineSchedules.deleted_at', '=', DB::raw('0'))
                ;
            })
            ->where('SalesOrders.soldto_customer_id', '=', $this->id)
            ->whereNotIn('SalesOrders.order_status', ['CANCELLED', 'DISCARDED'])
            ->groupBy('WebFamilies.id')
            ->orderBy(DB::raw('COUNT(DISTINCT SalesOrders.id)'), 'DESC')
            ->take($count)
            ->pluck('WebFamilies.id')
        ;

        return WebFamily::with(['assets', 'translations'])->with('visibleWebParts.part.rewardsFamilyExclusion')->with('visibleWebParts.part.rewardsPartExclusion')->with('visibleWebParts.part.pharmacyPartExclusion')->with('visibleWebParts.part.pharmacyFamilyExclusion')->findMany($ids);
    }

    public function affiliateCustomer() {
	    return $this->hasOne(Customer::class, 'id', 'aff_customer_id');
    }

    public function affiliateChildren() {
        return $this->hasMany(Customer::class, 'aff_customer_id', 'id');
    }

    public function affiliatedCustomers() {
	    return $this->hasMany(Customer::class, 'aff_customer_id', 'aff_customer_id');
    }

    /**
     * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
     */
    public function webSilo() {
        return $this->belongsTo(WebSilo::class, 'websilo_id', 'id');
    }

    public function customerWebSilo() {
        return $this->hasOne(CustomerWebSilo::class, 'customer_id', 'id');
    }

    public function getHasAffiliateAttribute() {
	    return !is_null($this->aff_customer_id);
    }

    public function croakiesAdventuresStatus() {
        return $this->hasMany(CroakiesAdventuresStatus::class, 'customer_id', 'aff_customer_id');
    }

    public function croakiesAdventuresTier() {
        return $this->hasOne(CroakiesAdventuresTiers::class, 'customer_id', 'id')
                    ->orderBy('first_qualified_date', 'desc')
                    ->orderBy('tier', 'desc')
                    ->take(1);
    }

    public function getCroakiesOrdered() {
        $statuses = $this->croakiesAdventuresStatus;
        $croakiesOrdered = 0;

        $lastDateModified = Carbon::now()->toDateString();
        foreach($statuses as $status) {
            $croakiesOrdered = $croakiesOrdered + $status->quantities;
            $lastDateModified = $status->date_modified;
        }

        $affCustomers = $this->affiliatedCustomers;
        $affCustomerIds = [];
        $index = 0;
        foreach ($affCustomers as $affCustomer) {
            $affCustomerIds[$index] = $affCustomer->id;
            $index = $index + 1;
        }

        $croakiesOrders = Order::whereIn('billto_customer_id', $affCustomerIds)
                                ->where('date_created', '>=', $lastDateModified)
                                ->whereIn('order_status', ['PENDING', 'ADDRESS_PENDING', 'PENDING_APPROVAL', 'CLOSED', 'EXPORTING', 'SENT_TO_POINTMAN'])
                                ->where('deleted_at', '=', '0000-00-00 00:00:00')
                                ->get();

        foreach ($croakiesOrders as $order) {
            $partLines = $order->partLines;
            foreach ($partLines as $partLine) {
                $quantity = $partLine->quantity;
                $partValue = 1;

                $prepackPart = CroakiesAdventuresPrepackPart::where('part_no', '=', $partLine->part->part_no)->first();
                if ($prepackPart) {
                    $partValue = $prepackPart->no_units;
                }

                $croakiesOrdered = $croakiesOrdered + ($quantity * $partValue);
            }
        }

        return $croakiesOrdered;
    }

    public function rewardsStatus() {
	    return $this->hasMany(CustomerRewardsStatus::class, 'customer_id', 'aff_customer_id');
    }

    public function rewardsTier() {
	    return $this->hasOne(CustomerRewardsTier::class, 'customer_id', 'aff_customer_id')->orderBy('first_qualified_date', 'DESC')->orderBy('tier', 'DESC')->take(1);
    }

    public function rewardsRegistration() {
	    return $this->hasOne(CustomerRewardsRegistration::class, 'customer_id', 'aff_customer_id');
    }

    public function getBillToCustomerAttribute() {
	    $activeSegment = $this->activeSegment;
	    if (is_null($activeSegment)) return null;
	    return $activeSegment->billToCustomer;
    }

    public function getBillsDirectAttribute() {
	    $activeSegment = $this->activeSegment;

	    return is_null($activeSegment) ? false : $this->activeSegment->billsDirect;
    }

    public function getRewardsTierLevelAttribute() {
        $tier = $this->rewardsTier;
        if (!is_null($tier)) {
            return $tier->tier;
        }
        return 0;
    }

    public function customer4P() {
        return $this->hasOne(Customer4P::class, 'customer_id', 'id');

    }

    public function customerOverride() {
	    return $this->hasOne(CustomerOverride::class, 'customer_id', 'aff_customer_id');
    }

    public function getOverrideCustomerCategoryAttribute() {
        $relation = null;
	    $override = $this->customerOverride;
	    if (is_null($override)) {
            $relation = $this->customerCategory;
        } else {
            $relation = $override->customerCategory;
        }
        if(is_null($relation)){
	        return $this->customerCategory;
        }
        return $relation;
    }

    public function getProfileAttribute() {
        $profile = null;
        $override = $this->customerOverride;
        if (!is_null($override)) {
            $profile = $this->customerOverride->profile;
        }
        return $profile;
    }

    public function getDefaultPriceListAttribute() {
	    $activeSegment = $this->activeSegment;
	    if ($activeSegment) {
	        return $activeSegment->def_price_list;
        }

        return null;
    }

    /**
     * @return Customer|object|null
     */
    public function getDummyCustomer() {
        // Check if there is a specific customer for this currency and facility
        $dummyCustomer = DummyCustomer::where('currency', $this->currency)->where('facility', $this->activeSegment->facility_id)->first();

        // If null, Check if there is a default for the currency
        if ($dummyCustomer == null) {

            $dummyCustomer = DummyCustomer::where('currency', $this->currency)->whereNull('facility')->first();
        }

        // If still null, Default USD
        if ($dummyCustomer == null) {
            $dummyCustomer = DummyCustomer::where('currency', 'USD')->whereNull('facility')->first();
        }
        
        return Customer::where('cust_no', $dummyCustomer->cust_no)->first();
    }

    /**
     * @return mixed
     */
    public function getDivisionByFacility() {
        $facility = $this->activeSegment->facilityDivision;
        return $facility->division()->first();
    }

    /**
     * @return string
     */
    public function getServiceChargeCodeForWeb(): string {
        if ($this->service_charge_code == self::GERMAN_DOMESTIC_STANDARD_SERVICE_CHARGE_CODE) {
            return Customer::GERMAN_DOMESTIC_WEB_SERVICE_CHARGE_CODE;
        } else {
            return $this->service_charge_code;
        }
    }

    /**
     * @return HasOne
     */
    public function customerSegment(): HasOne {
        return $this->hasOne(CustomerSegment::class, "customer_id", "id");
    }

    public function currentCustomerLifecycle() {
        return $this->hasOne(CustomerLifecycle::class)->whereRaw('NOT EXISTS (SELECT 1 FROM CustomerLifecycles b WHERE b.customer_id = CustomerLifecycles.customer_id AND b.transfer_date > CustomerLifecycles.transfer_date)');
    }

    public function getCurrency() {
        return $this->currency;
    }

    public function getCustNo() {
        return $this->cust_no;
    }

    /**
     * @return HasOne
     */
    public function businessChainCustomer(): HasOne {
        return $this->hasOne(BusinessChain_Customer::class, "customer_id", "id");
    }

    /**
     * @return HasOneThrough
     */
    public function businessChain(): HasOneThrough {
        return $this->hasOneThrough(BusinessChain::class, BusinessChain_Customer::class, "customer_id", "id", "id", "businesschain_id");
    }

    /**
     * Same as businessChain() relation except the valid_x_date columns are checked in BusinessChain_Customer
     * @return HasOneThrough
     */
    public function validBusinessChain(): HasOneThrough {
        return $this->hasOneThrough(BusinessChain::class, BusinessChain_Customer::class, "customer_id", "id", "id", "businesschain_id")
            ->where(function ($query) {
                return $query->where(function ($where1) {
                    return $where1->whereNull("BusinessChain_Customer.valid_from_date")
                        ->orWhere("BusinessChain_Customer.valid_from_date", "=", DB::raw("0"))
                        ->orWhere("BusinessChain_Customer.valid_from_date", "<", DB::raw("DATE(NOW())"));
                })->where(function ($where2) {
                    return $where2->whereNull("BusinessChain_Customer.valid_to_date")
                        ->orWhere("BusinessChain_Customer.valid_to_date", "=", DB::raw("0"))
                        ->orWhere("BusinessChain_Customer.valid_to_date", ">", DB::raw("DATE(NOW())"));
                });
            });
    }
}