<?php

namespace Hilco;

use Carbon\Carbon;
use Hilco\Models\Carrier;
use Hilco\Models\Customer;
use Hilco\Models\CustomerRewardsStatus;
use Hilco\Models\CustomerRewardsTier;
use Hilco\Models\FOB;
use Hilco\Models\Order;
use Hilco\Models\Part;
use Hilco\Models\ProductCategoryGroup;
use Hilco\Models\RewardsCarrierCode;
use Hilco\Models\RewardsProductCategoryGroupDiscount;
use Hilco\Models\RewardsTierDefinition;
use Hilco\Models\RewardTier;
use Hilco\Models\WebGroup;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Request;
use DateTime;

class RewardsHelper {

    const OPTICIAN          = 'OPTICIAN';
    const OPTOMETRIST       = 'OPTOMETRIST';
    const OPHTHALMOLOGIST   = 'OPHTHALMOLOGIST';

    protected function customer() {
        $customer = b2b()->activeCustomer();
        if ($customer) return $customer->affiliateCustomer;

        return false;
    }

    public function tierMap($id = false) {
        if ($id === false) return config('rewards.tierMap');
        return config("rewards.tierMap.$id", $id);
    }

    public function tierImage(Customer $customer = null) {
        if (is_null($customer)) $customer = $this->customer();

        $tier = $this->tier($customer);

        if ($tier == 1) {
            return '/images/rewards-logo.png';
        } else if ($tier == 2) {
            return '/images/rewards-silver.png';
        } else if ($tier == 3) {
            return '/images/rewards-gold.png';
        } else if ($tier == 4) {
            return '/images/rewards-platinum.png';
        }
        return '';
    }

    public function tierAltText(Customer $customer = null) {
        if (is_null($customer)) $customer = $this->customer();
        $tier = $this->tier($customer);

        return $this->tierAltTextX($tier);
    }

    public function tierImageX($tier) {
        if ($tier == 2) {
            return '/images/rewards-silver.png';
        } else if ($tier == 3) {
            return '/images/rewards-gold.png';
        } else if ($tier == 4) {
            return '/images/rewards-platinum.png';
        }
        return '';
    }

    public function getRewardsCategories(){
        return [
            self::OPTICIAN,
            self::OPTOMETRIST,
            self::OPHTHALMOLOGIST
        ];
    }

    public function getRewardsCategoriesForSQL(){
        $rewardsCategories = $this->getRewardsCategories();

        $sql = "('";
        $count = 0;
        foreach($rewardsCategories as $rewardsCategory){
            $sql .= $rewardsCategory;
            $count++;

            if($count < count($rewardsCategories)){
                $sql .= "', '";
            }else{
                $sql .= "')";
            }
        }

        return $sql;
    }

    public function isCustomerRewardsCategory($customerCategory){
        $rewardsCategories = $this->getRewardsCategories();

        return in_array($customerCategory, $rewardsCategories);
    }

    public function tierAltTextX($tier) {
        if ($tier == 1) {
            return "Hilco Vision Partner Rewards";
        } else if ($tier == 2) {
            return 'Silver Tier';
        } else if ($tier == 3) {
            return 'Gold Tier';
        } else if ($tier == 4) {
            return 'Platinum Tier';
        }
        return '';
    }

    public function eligibility(Customer $customer = null, $needsValidCustomerCategory = true) {
        if (is_null($customer)) {
            $customer = b2b()->activeCustomer()->with('affiliateCustomer.affiliateChildren');
        }

        $eligibility = [];

        $customerCategory = $customer->affiliateCustomer->overrideCustomerCategory;
        $custCategoryuName = is_null($customerCategory) ? null : $customerCategory->cust_category;

        $eligibleCustomersString = config('rewards.eligibleCustomerNumbers', '');
        $eligibleCustomers = [];
        if (strlen($eligibleCustomersString)) $eligibleCustomers = explode('^', $eligibleCustomersString);
        if (count($eligibleCustomers)) {
            $eligibility['validCustomer'] = in_array($customer->cust_no, $eligibleCustomers);
        }

        if($needsValidCustomerCategory) {
            $eligibility['validCustomerCategory'] = in_array($custCategoryuName, $this->getRewardsCategories());
        } else {
            $eligibility['validCustomerCategory'] = true;
        }
        $eligibility['validMemberGroup'] = !in_array($customer->member_group, config('rewards.excludedMemberGroups', []));
        $eligibility['validBilling'] = $customer->billsDirect;
        $eligibility['validPriceList'] = in_array(\Illuminate\Support\Arr::get($customer, 'activeSegment.def_price_list'), config('rewards.eligiblePriceLists', []));
        $eligibility['validCurrency'] = in_array($customer->currency, config('rewards.eligibleCurrencyCodes', []));

        $eligibility['validAffiliates'] = true;
        foreach ($customer->affiliateCustomer->affiliateChildren as $affiliate) {
            if (\Illuminate\Support\Str::startsWith($affiliate->cust_name, '*')) continue;
            if (is_null($affiliate->activeSegment)) continue;

            if (!$affiliate->billsDirect) $eligibility['validAffiliates'] = false;

            if (!in_array(\Illuminate\Support\Arr::get($affiliate, 'activeSegment.def_price_list'), config('rewards.eligiblePriceLists', []))) $eligibility['validAffiliates'] = false;


        }
        return $eligibility;
    }



    public function isEligible(Customer $customer = null, $needsValidCustomerCategory = true) {
        if (!b2b()->isRewardsEnabled()) return false;

        if (is_null($customer)) $customer = b2b()->activeCustomer();

        $eligibility = $this->eligibility($customer, $needsValidCustomerCategory);

        foreach ($eligibility as $item=>$isEligible) {
            if (!$isEligible) return false;
        }

        return true;
    }

    public function isSelfEligible(Customer $customer = null, $needsValidCustomerCategory = true) {
        if (!b2b()->isRewardsEnabled()) return false;

        if (is_null($customer)) $customer = b2b()->activeCustomer();

        $eligibility = $this->eligibility($customer, $needsValidCustomerCategory);

        foreach ($eligibility as $item=>$isEligible) {
            if ($item == 'validAffiliates') continue;
            if (!$isEligible) return false;
        }

        return true;
    }

    public function isRegistered(Customer $customer = null) {
        if (!b2b()->isRewardsEnabled()) return false;

        if (is_null($customer)) $customer = $this->customer();
        if (is_null($customer)) $customer = b2b()->activeCustomer();

        $registration = $customer->rewardsRegistration;
        return !is_null($registration);
    }
    /*
     * This entire family of functions (model, modelThresholds, threshold, normalizeMix) was modified to
     *  break away from the hardcoding while still preserving their return, since the rewards program at the time
     *  of revision was being done by someone else at the same time and this was deemed the safest way of going about.
     *  The only thing that deviates from its return heavily is model, which instead of returning an arbitrary number
     *  based on the customercategory_id, simply extracts and returns the customercategory_id which is used in
     *  the other functions.
     *  TODO: Finalize the refactoring at a later date when things aren't as pressing and possibly even hatchet some of
     *      these functions.
     */

    public function model(Customer $customer = null) {
        if(auth()->user()) {
            // All this does is look up the customer's category and determines if they're either an optics or optho
            if (is_null($customer)) $customer = $this->customer();

            // To preserve this structure, we're just going to have this return the category id and have the child functions
            // take that and build thresholds base on that.
            $customerCategory = $customer->overrideCustomerCategory;
            $customerCategoryId = is_null($customerCategory) ? null : $customerCategory->id;
            return $customerCategoryId;
        }
    }


    public function modelThresholds($model) {
        // The $model is now the categoryID (we should probably refactor that at some point).
        $rewardsTierDefinitions = RewardsTierDefinition::where('deleted_at' , '=', '0000-00-00 00:00:00')
            ->where('customercategory_id', '=', $model)
            ->get();
        $modelReturn = [];
        foreach($rewardsTierDefinitions as $rewardsTierDefinition){
            if(!isset($modelReturn[$rewardsTierDefinition->min_categories])){
                $modelReturn[$rewardsTierDefinition->min_categories] = [];
            }
            $modelReturn[$rewardsTierDefinition->min_categories][$rewardsTierDefinition->reward_tier] = $rewardsTierDefinition->min_total;
        }
        return $modelReturn;
    }

    public function normalizeMix($mix) {
        if ($mix >= 6) return 6;
        else if ($mix >= 4) return 4;
        else if ($mix >= 0) return 1;
        else return $mix;
    }

    public function threshold($model, $mix, $tier) {
        $mix = $this->normalizeMix($mix);
        $thresholds = $this->modelThresholds($model);
        $tiers = \Illuminate\Support\Arr::get($thresholds, $mix, []);
        return \Illuminate\Support\Arr::get($tiers, $tier, false);
    }

    public function calculateTierForSpending($spending, $mix, Customer $customer = null) {
        if(is_null($customer)){
            $customer = $this->customer();
        }

        $customerCategory = $customer->overrideCustomerCategory ? $customer->overrideCustomerCategory->id : $customer->customercategory_id;
        return RewardsTierDefinition::where('min_total', '<=', $spending)->where('min_categories','<=', $mix)
            ->where('customercategory_id', '=', $customerCategory)->max('reward_tier');
    }

    /**
     * These following functions (categoryBonus, categoryPercentage) might be safe for deletion.
     */
    public function categoryBonus(Customer $customer = null) {
        if (is_null($customer)) $customer = $this->customer();
        $model = $this->model($customer);

        $thresholds = $this->modelThresholds($model);

        $bonuses = [
            2 => [
                2 => ($thresholds[1][2] - $thresholds[3][2]) / 2,
                3 => ($thresholds[1][3] - $thresholds[3][3]) / 2,
                4 => ($thresholds[1][4] - $thresholds[3][4]) / 2,
            ],
            3 => [
                2 => $thresholds[1][2] - $thresholds[3][2],
                3 => $thresholds[1][3] - $thresholds[3][3],
                4 => $thresholds[1][4] - $thresholds[3][4],
            ],
            4 => [
                2 => ($thresholds[3][2] - $thresholds[5][2]) / 2,
                3 => ($thresholds[3][3] - $thresholds[5][3]) / 2,
                4 => ($thresholds[3][4] - $thresholds[5][4]) / 2,
            ],
            5 => [
                2 => $thresholds[3][2] - $thresholds[5][2],
                3 => $thresholds[3][3] - $thresholds[5][3],
                4 => $thresholds[3][4] - $thresholds[5][4],
            ],
        ];

        return \Illuminate\Support\Arr::get($bonuses, $this->qualifyingCategories() . '.' . $this->tier(), 0);
    }

    public function categoryPercentage(Customer $customer = null) {
        if (is_null($customer)) $customer = $this->customer();
        $thresholds = $this->modelThresholds($this->model($customer));
        $max = $thresholds[1][4];
        return $this->categoryBonus() / $max;
    }


    public function statusPercentage($categories = false, Customer $customer = null) {
        if (is_null($customer)) $customer = $this->customer();
        $thresholds = $this->modelThresholds($this->model($customer));
        $totalSpending = $this->totalSpending($customer);

        $mySpending = $totalSpending;
        $statusPercentage = 0;

        if ($categories === false) {
            $categories = $this->qualifyingCategories($customer);
        }

        $categories = $this->normalizeMix($categories);
        foreach (\Illuminate\Support\Arr::get($thresholds, $categories, []) as $tier => $threshold) {
            if ($mySpending > 0) {
                $percentOf = ($threshold <= 0) ? 1 : min(1, $totalSpending / $threshold);
                $mySpending -= $threshold;
                $statusPercentage += ($percentOf / 3);
            }
        }

        return $statusPercentage;
    }

    public function potential(Customer $customer = null) {
        if (is_null($customer)) $customer = $this->customer();

        $potentials = [
            1 => [
                3 => $this->statusPercentage(3, $customer) - $this->statusPercentage(1, $customer),
                5 => $this->statusPercentage(5, $customer) - $this->statusPercentage(3, $customer),
            ],
            2 => [
                3 => $this->statusPercentage(3, $customer) - $this->statusPercentage(1, $customer),
                5 => $this->statusPercentage(5, $customer) - $this->statusPercentage(3, $customer),
            ],
            3 => [
                5 => $this->statusPercentage(5, $customer) - $this->statusPercentage(3, $customer),
            ],
            4 => [
                5 => $this->statusPercentage(5, $customer) - $this->statusPercentage(3, $customer),
            ],
            5 => [

            ],
        ];

        return \Illuminate\Support\Arr::get($potentials, $this->qualifyingCategories($customer), []);
    }

    public function spendingThreshold(Customer $customer = null) {
        if (is_null($customer)) $customer = $this->customer();

        $threshold = 0;
        $model = $this->model($customer);
        $categories = $this->qualifyingCategories($customer);
        $tier = $this->tier($customer);

        if ($model == 1) {
            if ($tier >= 3) {
                if ($categories >= 6) {
                    $threshold = 800;
                } else if ($categories >= 4) {
                    $threshold = 2000;
                } else {
                    $threshold = 5000;
                }
            } else {
                if ($categories >= 6) {
                    $threshold = 0;
                } else if ($categories >= 4) {
                    $threshold = 801;
                } else {
                    $threshold = 2001;
                }
            }
        } else {
            if ($tier >= 3) {
                if ($categories >= 6) {
                    $threshold = 2500;
                } else if ($categories >= 4) {
                    $threshold = 6000;
                } else {
                    $threshold = 14000;
                }
            } else {
                if ($categories >= 6) {
                    $threshold = 0;
                } else if ($categories >= 4) {
                    $threshold = 2501;
                } else {
                    $threshold = 6001;
                }
            }
        }

        return $threshold;
    }

    public function calculateStatusBarPercent($categorySpending, Customer $customer = null) {
        if (is_null($customer)) $customer = $this->customer();

        $overallPct = min(1, $this->totalSpending($customer) / $this->spendingThreshold($customer));
        $categoryPct = $categorySpending / $this->totalSpending($customer);
        return ($overallPct * $categoryPct) * 100;
    }

    public function qualifyingCategories(Customer $customer = null) {
        return $this->potentialQualifyingCategories($customer);
    }

    public function totalSpending(Customer $customer = null) {
        return $this->potentialSpending($customer);
    }

    public function potentialQualifyingCategories(Customer $customer = null) {
        if (is_null($customer)) $customer = $this->customer();

        $categories = 0;
        foreach (\Illuminate\Support\Arr::get($customer, 'rewardsStatus', []) as $statusRow) {
            if ($statusRow['qualifying']) $categories++;
        }

        return $categories;
    }

    public function potentialSpending(Customer $customer = null) {
        if (is_null($customer)) $customer = $this->customer();

        $spending = 0;
        foreach (\Illuminate\Support\Arr::get($customer, 'rewardsStatus', []) as $statusRow) {
            $spending += $statusRow['spending'];
        }

        return $spending;
    }

    public function className($tier = false) {
        if ($tier === false) $tier = rewards()->tier();

        if ($tier == 2) return 'rewards-silver';
        if ($tier == 3) return 'rewards-gold';
        if ($tier == 4) return 'rewards-platinum';
        return '';
    }

    public function tier(Customer $customer = null) {
        if (is_null($customer)) $customer = $this->customer();
        if (is_null($customer)) $customer = b2b()->activeCustomer();

        if (!$this->isRegistered($customer) || !$this->isEligible($customer)) return 0;
        $rewardsTier = $customer->rewardsTier;

        return is_null($rewardsTier) ? 0 : $rewardsTier->tier;
    }

    public function tierName($tier = false, Customer $customer = null) {
        if ($tier === false) $tier = $this->tier($customer);
        return $this->tierMap($tier);
    }

    public function joinDate(Customer $customer = null) {
        if (is_null($customer)) $customer = $this->customer();

        $registration = $customer->rewardsRegistration;
        if (is_null($registration)) return false;

        return $registration->date_created->toDateString();
    }

    public function joinDateLongForm(Customer $customer = null) {
        if (is_null($customer)) $customer = $this->customer();

        $registration = $customer->rewardsRegistration;
        if (is_null($registration)) return false;

        $date = new DateTime($registration->date_created->toDateString());
        return $date->format('F j, Y');
    }

    public function discountForPart($partId, Customer $customer = null) {
        if (is_null($customer)) $customer = $this->customer();

        if (!$this->isRegistered($customer) || !b2b()->isBillingDirect($customer) || !$this->isEligible($customer, false)) return 0;
        $part = ($partId instanceof Part) ? $partId : Part::find($partId);
        $group = $part->productFamily->productCategory->productCategoryGroup;
//        $groupName = $part->productFamily->productCategory->productCategoryGroup->product_category_group;
        $currentTier = $this->tier($customer);
        if(is_null($group)) return 0;
        $groupId  = $group->id;
        $start = microtime(true);
        $rewardsProductCategoryGroupDiscounts = b2b()->rewardsProductCategoryGroupDiscounts();
        $rewardsProductCategoryGroupDiscount = $rewardsProductCategoryGroupDiscounts->filter(function($value, $key ) use ($currentTier, $groupId){
            return $value->reward_tier === $currentTier && $value->productcategorygroup_id === $groupId && $value->deleted_at === "0000-00-00 00:00:00";
        });
        $rewardsProductCategoryGroupDiscount = $rewardsProductCategoryGroupDiscount->first();

        if(isset($rewardsProductCategoryGroupDiscount)){
            $discount = $rewardsProductCategoryGroupDiscount->discount_rate;
        }else{
            $discount = 0;
        }
//        $discount = config("rewards.productCategoryGroups.$groupName.discountPercentByTier.$currentTier", 0);
        return $discount;
    }
    /*
     * This is pretty much identical to the above function, only it seems to be exclusively used when a
     *  customer is about to get an upgrade in their reward tier and necessitates a corresponding discount.
     * Not sure why this exists as a seperate function, might refactor it to the above function once it's
     *  more confidently verified that this isn't attached to anything else.
     */

    public function discountForPartTier($partId, $tier = false, Customer $customer = null) {
        if (is_null($customer)){
            $customer = $this->customer();
        }

        if (!$this->isRegistered($customer)){
            return 0;
        }

        $part = ($partId instanceof Part) ? $partId : Part::find($partId);
        if(!isset($part) || $part->isRewardsExcluded()){
            return 0;
        }

        $group = $part->productFamily->productCategory->productCategoryGroup;
        if(is_null($group)){
            return 0;
        }

//        $groupName = $part->productFamily->productCategory->productCategoryGroup->product_category_group;
        if ($tier === false){
            $tier = $this->tier($customer);
        }

        $groupId = $group->id;
        $rewardsProductCategoryGroupDiscounts = b2b()->rewardsProductCategoryGroupDiscounts();
        $rewardsProductCategoryGroupDiscount = $rewardsProductCategoryGroupDiscounts->filter(function($value, $key ) use ($tier, $groupId){
            return $value->reward_tier === $tier && $value->productcategorygroup_id === $groupId && $value->deleted_at === "0000-00-00 00:00:00";
        });
        $rewardsProductCategoryGroupDiscount = $rewardsProductCategoryGroupDiscount->first();

        if(isset($rewardsProductCategoryGroupDiscount)){
            $discount = $rewardsProductCategoryGroupDiscount->discount_rate;
        }else{
            $discount = 0;
        }
//        $discount = config("rewards.productCategoryGroups.$groupName.discountPercentByTier.$tier", 0);
        return $discount;
    }

    public function discountCode($tier = false) {

        if ($tier == 2) return 'TIER 2';
        else if ($tier == 3) return 'TIER 3';
        else if ($tier == 4) return 'TIER 4';

        if(isset($tier) && $tier != false){
            return 'TIER ' . (string) $tier;
        }

        return 'DISCOUNT';
    }

    public function freightDiscountMultiplier($orderTotal, Customer $customer = null) {
        if (is_null($customer)) $customer = $this->customer();

        if (!$this->isRegistered($customer)) return 1;
        $tier = $this->tier($customer);

        if ($tier == 4 && $orderTotal >= 100) return 0;
        else if ($tier == 3 && $orderTotal >= 150) return 0;
        else if ($tier == 2 && $orderTotal >= 150) return 0.5;
        else return 1;
    }

    public function carrierCode($orderTotal, $baseRates, $tier) {
        $customer = b2b()->activeCustomer();
        $customerCategory = $customer->overrideCustomerCategory;
        $category = $customerCategory->cust_category;
        if (!$this->isRegistered($customer)) return false;
        if (!$this->isEligible($customer, false)) return false;
        if (!b2b()->isBillingDirect()) return false;

        $carrierCode = collect();

        if ($category === 'OPTICIAN' || $category === 'OPTOMETRIST' || $category === 'OPHTHALMOLOGIST') {

            $rewardsCarrierCodes =
                RewardsCarrierCode
                    ::join('DeliveryMethod_DeliveryTerm', 'deliverymethoddeliveryterm_id', '=', 'DeliveryMethod_DeliveryTerm.id')
                    ->where('DeliveryMethod_DeliveryTerm.deleted_at', '=', '0000-00-00 00:00:00')
                    ->where('b2b_total', '<=', $orderTotal)
                    ->where('reward_tier', '=', $tier)
                    ->where('customercategory_id', '=', $customerCategory->id)
                    ->get(['delivery_method_code', 'delivery_term_code', 'carrier_description', 'rate', 'base_deliverymethod'])
            ;

            foreach ($rewardsCarrierCodes as $rewardsCarrierCode){
                $deliveryMethodCode = $rewardsCarrierCode['delivery_method_code'];
                $deliveryTermCode = $rewardsCarrierCode['delivery_term_code'];
                $discount_percent = $rewardsCarrierCode['rate'];
                $baseMethodCode = $deliveryMethodCode == 'BWG' ? 'U03' : $rewardsCarrierCode['base_deliverymethod'];

                $rate = round(\Illuminate\Support\Arr::get($baseRates, $baseMethodCode, 0) * $discount_percent / 100, 2);
                $description = $rewardsCarrierCode['carrier_description'];

                $carrierCode->push([
                    'description' => $description,
                    'base_method' => $baseMethodCode,
                    'delivery_method' => $deliveryMethodCode,
                    'delivery_term' => $deliveryTermCode,
                    'discount_percent' => $discount_percent,
                    'rate' => $rate,
                ]);
            }
        }

        return $carrierCode;
    }

    public function tierNameBySpending(){
        $tier = $this->calculateTierForSpending($this->totalSpending(), $this->qualifyingCategories());

        return $this->tierName($tier);
    }

    public function nextTier($tier = null) {
        if ($tier == null) {
            $tier = $this->tier();
        }
        if ($tier == 4) {
            // Highest tier, there is no next tier
            return null;
        } else {
            return $tier + 1;
        }

    }

    public function tierTextColorHex($tier = null) {
        if ($tier == null) {
            $tier = $this->tier();
        }
        switch ($tier) {
            case 2: {
                return "#c0c0c0";
            }
            case 3: {
                return "#daa520";
            }
            case 4: {
                return "#778899";
            }
            default: {
                return "#000000";
            }
        }
    }

    public function tierWebShippingDescription($tier = null) {
        if ($tier == null) {
            $tier = $this->tier();
        }

        $customer = $this->customer();
        $customerCategory = $customer->overrideCustomerCategory;

        if ($customerCategory->cust_category == self::OPTICIAN || $customerCategory->cust_category == self::OPTOMETRIST) {
            switch ($tier) {
                case 1:
                    {
                        return "25% off";
                    }
                case 2:
                    {
                        return "50% off";
                    }
                case 3:
                    {
                        return "free";
                    }
                case 4:
                    {
                        return "free expedited";
                    }
                default:
                    {
                        return "N/A";
                    }
            }
        } else if ($customerCategory->cust_category == self::OPHTHALMOLOGIST) {
            switch ($tier) {
                case 1:
                    {
                        return "25% off";
                    }
                case 2:
                    {
                        return "free";
                    }
                case 3:
                    {
                        return "free";
                    }
                case 4:
                    {
                        return "free expedited";
                    }
                default:
                    {
                        return "N/A";
                    }
            }
        }

    }

    public function tierFreeShippingBreakWebOrders($tier = null) {
        if ($tier == null) {
            $tier = $this->tier();
        }

        $customer = $this->customer();
        $customerCategory = $customer->overrideCustomerCategory;

        if ($customerCategory->cust_category == self::OPTICIAN || $customerCategory->cust_category == self::OPTOMETRIST) {
            switch ($tier) {
                case 1:
                    {
                        return 150;
                    }
                case 2:
                    {
                        return 150;
                    }
                case 3:
                    {
                        return 150;
                    }
                case 4:
                    {
                        return 100;
                    }
                default:
                    {
                        return "N/A";
                    }
            }
        } else if ($customerCategory->cust_category == self::OPHTHALMOLOGIST) {
            switch ($tier) {
                case 1:
                    {
                        return 150;
                    }
                case 2:
                    {
                        return 300;
                    }
                case 3:
                    {
                        return 300;
                    }
                case 4:
                    {
                        return 300;
                    }
                default:
                    {
                        return "N/A";
                    }
            }
        }
    }

    public function tierNonWebShippingDescription($tier = null) {
        if ($tier == null) {
            $tier = $this->tier();
        }

        $customer = $this->customer();
        $customerCategory = $customer->overrideCustomerCategory;

        if ($customerCategory->cust_category == self::OPTICIAN || $customerCategory->cust_category == self::OPTOMETRIST) {
            switch ($tier) {
                case 2:
                    {
                        return "50% off";
                    }
                case 3:
                    {
                        return "free";
                    }
                case 4:
                    {
                        return "free expedited";
                    }
                default:
                    {
                        return "N/A";
                    }
            }
        } else if ($customerCategory->cust_category == self::OPHTHALMOLOGIST) {
            switch ($tier) {
                case 2:
                    {
                        return "free";
                    }
                case 3:
                    {
                        return "free";
                    }
                case 4:
                    {
                        return "free expedited";
                    }
                default:
                    {
                        return "N/A";
                    }
            }
        }

    }



    public function tierFreeShippingBreakNonWebOrders($tier = null) {
        if ($tier == null) {
            $tier = $this->tier();
        }

        $customer = $this->customer();
        $customerCategory = $customer->overrideCustomerCategory;

        if ($customerCategory->cust_category == self::OPTICIAN || $customerCategory->cust_category == self::OPTOMETRIST) {
            switch ($tier) {
                case 2:
                    {
                        return 200;
                    }
                case 3:
                    {
                        return 200;
                    }
                case 4:
                    {
                        return 150;
                    }
                default:
                    {
                        return "N/A";
                    }
            }
        } else if ($customerCategory->cust_category == self::OPHTHALMOLOGIST) {
            switch ($tier) {
                case 2:
                    {
                        return 400;
                    }
                case 3:
                    {
                        return 400;
                    }
                case 4:
                    {
                        return 400;
                    }
                default:
                    {
                        return "N/A";
                    }
            }
        }
    }

    public function tierAverageAnnualSavings($tier = null) {
        if ($tier == null) {
            $tier = $this->tier();
        }

        switch ($tier) {
            case 2: {
                return 5;
            }
            case 3: {
                return 7;
            }
            case 4: {
                return 10;
            }
            default: {
                return 0;
            }
        }
    }

    public function calculateSpendingToUpgrade(Customer $customer = null) {
        if(is_null($customer)) $customer = $this->customer();
        $tier = min($this->tier($customer) + 1, 4);
        $currentSpending = $this->totalSpending($customer);
        $customerCategory = $customer->overrideCustomerCategory ? $customer->overrideCustomerCategory->id : $customer->customercategory_id;
        $needSpending = RewardsTierDefinition::where('customercategory_id', '=', $customerCategory)
            ->where('reward_tier', '=', $tier)
            ->where('deleted_at', '=', '0000-00-00 00:00:00')->max('min_total');


        $leftSpending = $needSpending - $currentSpending;

        return $leftSpending;
    }

    public function calculateSpendingToMaintainTier(){
        if(auth()->user()) {
            $customer = $this->customer();
            $tier = $this->tier($customer);
            $customerCategory = $customer->overrideCustomerCategory ? $customer->overrideCustomerCategory->id : $customer->customercategory_id;
            $needSpending = RewardsTierDefinition::where('customercategory_id', '=', $customerCategory)
                ->where('reward_tier', '=', $tier)
                ->where('deleted_at', '=', '0000-00-00 00:00:00')->max('min_total');
            $currentSpending = $this->totalSpending();

            return $needSpending - $currentSpending;
        }
    }

    public function calculateCategoriesToUpgrade(Customer $customer = null) {
        if(is_null($customer)) $customer = $this->customer();

        // Get the customer category id
        $customerCategory = $customer->overrideCustomerCategory ? $customer->overrideCustomerCategory->id : $customer->customercategory_id;

        $currentSpending = $this->totalSpending($customer);
        $upgradeTier = min($this->tier($customer) + 1, 4);
        if($upgradeTier == 1){
            return 0;
        }

        $mix = $this->normalizeMix($this->qualifyingCategories());
        $thresholds = $this->modelThresholds($this->model($customer));
        $maxCategories = 0;
        $applicableRewardsTierDefinitions = RewardsTierDefinition::where('deleted_at', '=', "000-00-00 00:00:00")
            ->where('reward_tier', '=', $upgradeTier)
            ->where('min_total', '<=', $currentSpending)
            ->where('customercategory_id', '=', $customerCategory)
            ->orderBy('min_categories', 'ASC')
            ->first();
        if(!isset($applicableRewardsTierDefinitions)){// THe current spending is too low for even the bare minimum.
            $applicableRewardsTierDefinitions = RewardsTierDefinition::where('deleted_at', '=', "0000-00-00 00:00:00")
                ->where('reward_tier', '=', $upgradeTier)
                ->where('customercategory_id', '=', $customerCategory)
                ->orderBy('min_total', 'ASC')
                ->first();
        }

        $neededCategories = $applicableRewardsTierDefinitions->min_categories;
        $actualCategories = rewards()->qualifyingCategories($customer);
        $buyTheseCategories = $neededCategories - $actualCategories;
        return $buyTheseCategories;
    }

    public function calculateCategoriesToMaintainTier(){
        if(auth()->user()) {
            $customer = $this->customer();
            $customerCategory = $customer->overrideCustomerCategory ? $customer->overrideCustomerCategory->id : $customer->customercategory_id;

            $currentSpending = $this->totalSpending();

            $tier = $this->tier($customer);
            if($tier == 0 ){
                return 0;
            }
            $mix = $this->normalizeMix(rewards()->qualifyingCategories());
            $thresholds = $this->modelThresholds($this->model($customer));
            $actualCategories = rewards()->qualifyingCategories();

            // first, we look up the RewardsTierDefinitions that currently apply given this min discount and reward tier.
            $applicableRewardsTierDefinitions = RewardsTierDefinition::where('deleted_at', '=', "000-00-00 00:00:00")
                ->where('reward_tier', '=', $tier)
                ->where('min_total', '<=', $currentSpending)
                ->where('min_categories', '>=', $actualCategories)
                ->where('customercategory_id', '=', $customerCategory)
                ->orderBy('min_categories', 'ASC')
                ->first();
            if(!isset($applicableRewardsTierDefinitions)){// THe current spending is too low for even the bare minimum.
                $applicableRewardsTierDefinitions = RewardsTierDefinition::where('deleted_at', '=', "0000-00-00 00:00:00")
                    ->where('reward_tier', '=', $tier)
                    ->where('customercategory_id', '=', $customerCategory)
                    ->orderBy('min_total', 'ASC')
                    ->first();
            }
            $neededCategories = $applicableRewardsTierDefinitions->min_categories;
            $buyTheseCategories = $neededCategories - $actualCategories;
            return $buyTheseCategories;
        }
        return null;
    }

    public function qualifiedForTier(){
        $customer = $this->customer();
        if($this->calculateTierForSpending($this->totalSpending(), $this->qualifyingCategories()) < min($this->tier($customer), 4)){
            return false;
        }
        return true;
    }

    public function downgradeDate(){
        if(auth()->user()) {
            $customer = $this->customer();
            $date = new \DateTime(CustomerRewardsTier::where('customer_id', '=', $customer->id)->where('deleted_at', '=', '0000-00-00 00:00:00')
                ->orderBy('latest_qualified_date', 'desc')->pluck('latest_qualified_date')->first());
            $date = date_add($date, date_interval_create_from_date_string('6 Months'));
            return date_format($date, 'm/d/Y');
        }
    }

    public function calculateUpgradeSpending($currentOrderSpending, Customer $customer = null) {
        if (is_null($customer)) $customer = $this->customer();
        $currentTier = $this->tier($customer);
        if($currentTier === 4){
            return 0;
        }

        $nextTier = min($currentTier + 1, 4);
        $newSpending = 0;
        $newQualifying = 0;
        $newCategories = [];

        $productCategoryGroupSpending = $this->productCategoryGroups($customer);
        $todaysOrders = $this->getCurrentDayOrders($customer);
        foreach ($todaysOrders as $order) {
            $partLines = $order->partLines;
            foreach ($partLines as $partLine) {
                $part = $partLine->part;
                $groupId = $part->productFamily->productCategory->productCategoryGroup->id;
                $data = $productCategoryGroupSpending->get($groupId);
                $data['spending'] = $data['spending'] + $partLine->getDiscountUnitPriceExtension();
                $productCategoryGroupSpending->put($groupId, $data);
            }
        }

        foreach ($productCategoryGroupSpending as $productCategoryGroup) {
            $id = $productCategoryGroup->productcategorygroup_id;
            $categorySpending = $productCategoryGroup->spending;
            $categorySpending += \Illuminate\Support\Arr::get($currentOrderSpending['productCategoryTotals'], $id, 0);

            if ($categorySpending >= 200) $newQualifying++;

            $newCategories[$id] = $categorySpending;
            $newSpending += $categorySpending;
        }

        arsort($newCategories);

        $newTier = $this->calculateTierForSpending($newSpending, $newQualifying, $customer);
        if ($newTier > $currentTier) return $currentTier - $newTier;

        $needSpending = $this->threshold($this->model($customer), $newQualifying, $nextTier);
        return  $needSpending - $newSpending;
    }

    public function productCategoryGroups(Customer $customer = null) {
        if (is_null($customer)) $customer = $this->customer();

        $groupsConfig = config('rewards.productCategoryGroups');
        $map = collect();
        foreach ($groupsConfig as $name => $info) {
            $map->put($info['webGroupSlug'], $name);
        }

        $status = $customer->rewardsStatus;
        $groups = ProductCategoryGroup::orderBy('product_category_group')->get()->keyBy('id');
        $webGroups = WebGroup::whereIn('slug', $map->keys())->get();
        $rewardsProductCategoryGroupDiscounts = b2b()->rewardsProductCategoryGroupDiscounts();

        $data = collect();
        foreach ($webGroups as $webGroup) {
            $groupName = $map[$webGroup->slug];
            $group = $groups->where('productcategorygroup_description', $groupName)->first();
            if (is_null($group)) continue;
            $theStatus = $status->where('productcategorygroup_id', $group->id)->first();
            if (is_null($theStatus)) {
                $theStatus = new CustomerRewardsStatus();
                $theStatus->productcategorygroup_id = $group->id;
                $theStatus->spending = 0;

            }
            $theStatus['displayName'] = $webGroup->name;
            $theStatus['webGroupId'] = $webGroup->id;
            $theStatus['webGroupSlug'] = $webGroup->slug;
            $theStatus['spendingPercent'] = 100 * (min(200, $theStatus['spending']) / 200);
            $discounts = [];
            foreach([1, 2, 3, 4] as $tier){
                $rewardsProductCategoryGroupDiscount = $rewardsProductCategoryGroupDiscounts->filter(function($value, $key ) use ($webGroup, $tier){
                    return ($value->productcategorygroup_id === $webGroup->id && $value->reward_tier === $tier);
                })->first();
                $discounts[$tier] = !is_null($rewardsProductCategoryGroupDiscount) ? $rewardsProductCategoryGroupDiscount->discount_rate : 0;
            }
            $theStatus['discounts'] = $discounts;
            //$theStatus['discounts'] = \Illuminate\Support\Arr::get($groupsConfig, "$groupName.discountPercentByTier");
            $data->put($group->id, $theStatus);
        }
        return $data;
    }

    public function getCurrentDayOrders(Customer $customer = null) {
        if (is_null($customer)) $customer = b2b()->activeCustomer();

        if($customer->rewardsStatus->first()){
            $dateModified = $customer->rewardsStatus->first()->date_modified;
        }else{
            $dateModified = Carbon::now()->toDateTimeString();
        }

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

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

        return $todaysOrders;
    }

    function getCarrierCodeByTier($customercategory_id, $tier, $carrierCodes, $isB2B, $priorTotalPairs){
        if(is_null($tier)){
            return null;
        }
        $carrierCode = $carrierCodes->filter(function($value, $key) use ($customercategory_id, $tier, $isB2B, $priorTotalPairs){
            if($isB2B){
                $discountTotalPair = [$value->b2b_total, $value->rate];
                return $value->customercategory_id === $customercategory_id && $value->reward_tier === $tier && $value->deleted_at === '0000-00-00 00:00:00' && $value->b2b_total > 0 && !in_array($discountTotalPair, $priorTotalPairs);
            }else{
                $discountTotalPair = [$value->cst_total, $value->rate];
                return $value->customercategory_id === $customercategory_id && $value->reward_tier === $tier && $value->deleted_at === '0000-00-00 00:00:00' && $value->cst_total > 0 && !in_array($discountTotalPair, $priorTotalPairs);
            }
        });
        return $isB2B ? $carrierCode->sortBy('b2b_total') : $carrierCode->sortBy('cst_total');
    }

    function buildShippingStringHelper($carrierCode, $isB2B){
        if(is_null($carrierCode)){
            return "Error searching.";
        }
        if($carrierCode->rate >= 100){
            $discountOff = "Free Shipping on ";
        }else if($carrierCode->rate <= 0){
            return "";
        }else{
            $discountOff = $carrierCode->rate . "% off Shipping on ";
        }

        if($isB2B){
            $min_total = $carrierCode->b2b_total;
        }else{
            $min_total = $carrierCode->cst_total;
        }
        if($min_total <=0){
            return "";
        }else{
            $minTotalMessage = "$". $min_total . " orders";
        }
        if($carrierCode->base_code === 1100){
            return $discountOff . $minTotalMessage . " expedited!";
        }else{
            return $discountOff . $minTotalMessage;
        }

    }

    public function redirectToWelcome(Customer $customer = null) {
        if (is_null($customer)) $customer = $this->customer();
        if (!$customer) return false;
        if (!config('rewards.rewardsEnabled')) return false;
        if(!rewards()->isEligible($customer)) return false;


        $registration = $customer->rewardsRegistration;
        if (\Illuminate\Support\Arr::get($registration, 'hasAnsweredSurvey', 1) == 0) {
            $isVisionSource = $customer->member_group == '70714255';
            if ($isVisionSource) {
                return redirect()->route('rewards.welcomeVS');
            } else {
                return redirect()->route('rewards.welcome');
            }
        }
        return false;
    }

    public function info(Customer $customer) {
        $productCategoryGroups = ProductCategoryGroup::get()->keyBy('id');
        $tierLevel = 0;
        $tierName = rewards()->tierMap(0);

        $registration = $customer->rewardsRegistration;
        $isRegistered = !is_null($registration);

        $rewardsStatus = $customer->rewardsStatus;
        if (!is_null($rewardsStatus)) {
            $spending = $rewardsStatus;
        } else {
            $spending = $productCategoryGroups->map(function ($item) {
                $item->spending = 0;
                $item->qualifying = 0;
            });
        }

        $rewardsTier = $customer->rewardsTier;
        if (!is_null($rewardsTier)) {
            $tierLevel = $rewardsTier->tier;
            $tierName = rewards()->tierMap($rewardsTier->tier);
        }

        return [
            'tierLevel' => $tierLevel,
            'tierName' => $tierName,
            'registered' => $isRegistered,
            'eligible' => $customer->rewardsEligibility == Customer::REWARDS_ELIGIBLE,
            'qualified' => $tierLevel > 1,
            'spending' => $spending,
        ];
    }

    public function tierOverride($customer, $newTierLevel, $reason = 'orderUpgrade') {
        $newTier = new CustomerRewardsTier();
        $newTier->id = generateUUID();
        $newTier->customer_id = $customer->id;
        $newTier->first_qualified_date = Carbon::now()->toDateString();
        $newTier->latest_qualified_date = Carbon::now()->toDateString();
        $newTier->tier = $newTierLevel;
        $newTier->total_spending = $this->totalSpending($customer);
        $newTier->category_mix = $this->qualifyingCategories($customer);
        $newTier->upgrade_reason = $reason;
        $newTier->commit_sequence = -1;
        $newTier->save();
    }

    public function tierUpgradeSQL($singleAccount = false, $upgradeReason = 'dailyUpgrade') {
        $singleAccountString = '';
        if ($singleAccount) {
            $singleAccountString = ' AND Customers.id = :customer_id';
        }
        $sql = <<<SQLEND
INSERT IGNORE INTO CustomerRewardsTiers (id, customer_id, first_qualified_date, latest_qualified_date, tier, total_spending, category_mix, commit_sequence)
SELECT
  UuidToBin(UUID()),
  newTier.customer_id,
  IF(
	newTier.tier = (select tier from CustomerRewardsTiers where customer_id = newTier.customer_id and CustomerRewardsTiers.deleted_at = 0 order by first_qualified_date desc limit 1),
	(select first_qualified_date from CustomerRewardsTiers where customer_id = newTier.customer_id and CustomerRewardsTiers.deleted_at = 0 order by first_qualified_date desc, tier desc limit 1),
	DATE(
	  NOW()
	)
   ) as first_qualified_date,
  DATE(NOW()) as latest_qualified_date,
  newTier.tier as tier,
  newTier.total_spending as total_spending,
  newTier.category_mix as category_mix,
  :commit_sequence as commit_sequence
FROM
    (SELECT
        CustomerRewardsStatus.customer_id,
        CASE
        WHEN COALESCE(CustomerOverrides.customercategory_id, Customers.customercategory_id) IN (
          Select distinct(customercategory_id) From RewardsTierDefinitions 
          WHERE RewardsTierDefinitions.deleted_at = '0000-00-00 00:00:00'
        ) 
          THEN (
                GREATEST(
                  COALESCE(
                    (Select max(reward_tier) 
                    from RewardsTierDefinitions
                      where (coalesce(CustomerOverrides.customercategory_id, Customers.customercategory_id) = RewardsTierDefinitions.customercategory_id) and 
                        RewardsTierDefinitions.min_categories <= sum(qualifying) and
                        RewardsTierDefinitions.min_total <= greatest(sum(spending), 0) and 
                        RewardsTierDefinitions.deleted_at = '0000-00-00 00:00:00'
                    )
                    ,
                    0
                  )
                    ,
                    COALESCE(
                        (Select max(reward_tier) 
                        FROM RewardsMemberGroupOverrides
                        Where RewardsMemberGroupOverrides.member_group = Customers.member_group and 
                        RewardsMemberGroupOverrides.deleted_at = '0000-00-00 00:00:00')
                        , 
                        0
                    )
                )
          )
        ELSE 0
        END AS tier,
        CASE
        WHEN COALESCE(CustomerOverrides.customercategory_id, Customers.customercategory_id) IN (
          Select distinct(customercategory_id) From RewardsTierDefinitions 
          WHERE RewardsTierDefinitions.deleted_at = '0000-00-00 00:00:00'
        ) 
          THEN SUM(spending)
        ELSE 0
        END AS total_spending,
        CASE
        WHEN COALESCE(CustomerOverrides.customercategory_id, Customers.customercategory_id) IN (
          Select distinct(customercategory_id) From RewardsTierDefinitions 
          WHERE RewardsTierDefinitions.deleted_at = '0000-00-00 00:00:00'
        ) 
          THEN SUM(qualifying)
        ELSE 0
        END AS category_mix,
        CASE WHEN CustomerRewardsRegistration.id IS NOT NULL
          THEN 1
        ELSE 0 END AS isRegistered,
        COALESCE(qualifiedCustomers.qualified, 0) AS isQualified
      FROM CustomerRewardsStatus
        JOIN Customers ON (Customers.id = CustomerRewardsStatus.customer_id)
        JOIN CustomerSegments ON (Customers.id = CustomerSegments.customer_id)
        LEFT JOIN CustomerOverrides ON (CustomerOverrides.customer_id = Customers.id)
        LEFT JOIN CustomerRewardsRegistration ON (CustomerRewardsRegistration.customer_id = CustomerRewardsStatus.customer_id)
        LEFT JOIN (
                    SELECT
                      Customers.id AS customer_id,
                      CASE WHEN SUM(affiliateGroups.notDirectBilled) = 0
                        THEN 1
                      ELSE 0 END AS qualified
                    FROM Customers
                      JOIN CustomerRewardsRegistration ON (CustomerRewardsRegistration.customer_id = Customers.id)
                      JOIN CustomerSegments ON (CustomerSegments.customer_id = Customers.id)
                      LEFT JOIN CustomerOverrides ON (CustomerOverrides.customer_id = Customers.id)
                      LEFT JOIN (
                                  SELECT
                                    aff_customer_id,
                                    SUM(CASE WHEN CustomerSegments.billto_customer_id != CustomerSegments.customer_id
                                      THEN 1
                                        ELSE 0 END) notDirectBilled
                                  FROM Customers
                                    JOIN CustomerSegments ON (CustomerSegments.customer_id = Customers.id)
                                  WHERE
                                    CustomerSegments.billto_customer_id = CustomerSegments.customer_id
                                  GROUP BY aff_customer_id
                               ) affiliateGroups ON (affiliateGroups.aff_customer_id = COALESCE(Customers.aff_customer_id, Customers.id))
                    WHERE
                      Customers.id = Customers.aff_customer_id AND
                      CustomerSegments.billto_customer_id = CustomerSegments.customer_id AND
                      COALESCE(CustomerOverrides.customercategory_id, Customers.customercategory_id) IN (
                      Select distinct(customercategory_id) From RewardsTierDefinitions 
                      WHERE RewardsTierDefinitions.deleted_at = '0000-00-00 00:00:00'
                    )  AND
                      CustomerSegments.def_price_list IN ('303') AND
                      Customers.currency = 'USD'$singleAccountString
                    GROUP BY Customers.id
                  ) qualifiedCustomers ON (qualifiedCustomers.customer_id = Customers.id)
      GROUP BY CustomerRewardsStatus.customer_id) newTier
WHERE
  newTier.isQualified = 1 AND
 (
    (select tier from CustomerRewardsTiers where customer_id = newTier.customer_id and CustomerRewardsTiers.deleted_at = 0 order by first_qualified_date desc, tier desc limit 1) IS NULL OR 
    (select tier from CustomerRewardsTiers where customer_id = newTier.customer_id and CustomerRewardsTiers.deleted_at = 0 order by first_qualified_date desc, tier desc limit 1) <= newTier.tier
 )
ON DUPLICATE KEY UPDATE
  latest_qualified_date = DATE(NOW()),
  tier = VALUES(tier),
  total_spending = VALUES(total_spending),
  category_mix = VALUES(category_mix),
  commit_sequence = VALUES(commit_sequence)
;
SQLEND;
        return $sql;
    }

    public function tierDowngradeSQL($singleAccount = false, $upgradeReason = 'dailyDowngrade') {
        $singleAccountString = '';
        if ($singleAccount) {
            $singleAccountString = ' AND Customers.id = :customer_id';
        }

        $sql = <<<SQLEND
INSERT INTO CustomerRewardsTiers (id, customer_id, first_qualified_date, latest_qualified_date, tier, total_spending, category_mix, commit_sequence)
  SELECT
    UuidToBin(UUID()),
    newTier.customer_id,
    DATE(NOW()) as first_qualified_date,
    DATE(NOW()) as latest_qualified_date,
    newTier.tier as tier,
    newTier.total_spending as total_spending,
    newTier.category_mix as category_mix,
    :commit_sequence as commit_sequence
  FROM 
    (SELECT
        CustomerRewardsStatus.customer_id,
        CASE
        WHEN COALESCE(CustomerOverrides.customercategory_id, Customers.customercategory_id) IN (
          Select distinct(customercategory_id) From RewardsTierDefinitions 
          WHERE RewardsTierDefinitions.deleted_at = '0000-00-00 00:00:00'
        ) 
          THEN (
                GREATEST(
                  COALESCE(
                    (Select max(reward_tier) 
                    from RewardsTierDefinitions
                      where (coalesce(CustomerOverrides.customercategory_id, Customers.customercategory_id) = RewardsTierDefinitions.customercategory_id) and 
                        RewardsTierDefinitions.min_categories <= sum(qualifying) and
                        RewardsTierDefinitions.min_total <= greatest(sum(spending), 0) and 
                        RewardsTierDefinitions.deleted_at = '0000-00-00 00:00:00'
                    )
                    ,
                    0
                  )
                    ,
                    COALESCE(
                        (Select max(reward_tier) 
                        FROM RewardsMemberGroupOverrides
                        Where RewardsMemberGroupOverrides.member_group = Customers.member_group and 
                        RewardsMemberGroupOverrides.deleted_at = '0000-00-00 00:00:00')
                        , 
                        0
                    )
                )
          )
        ELSE 0
        END                                       AS tier,
        CASE
        WHEN COALESCE(CustomerOverrides.customercategory_id, Customers.customercategory_id) IN (
          Select distinct(customercategory_id) From RewardsTierDefinitions 
          WHERE RewardsTierDefinitions.deleted_at = '0000-00-00 00:00:00'
        ) 
          THEN SUM(spending)
        ELSE 0
        END                                       AS total_spending,
        CASE
        WHEN COALESCE(CustomerOverrides.customercategory_id, Customers.customercategory_id) IN (
          Select distinct(customercategory_id) From RewardsTierDefinitions 
          WHERE RewardsTierDefinitions.deleted_at = '0000-00-00 00:00:00'
        ) 
          THEN SUM(qualifying)
        ELSE 0
        END                                       AS category_mix,
        CASE WHEN CustomerRewardsRegistration.id IS NOT NULL
          THEN 1
        ELSE 0 END                                AS isRegistered,
        COALESCE(qualifiedCustomers.qualified, 0) AS isQualified,
        IFNULL(CustomerRewardsGracePeriods.days_converted, 180) as gracePeriod
      FROM CustomerRewardsStatus
        JOIN Customers ON (Customers.id = CustomerRewardsStatus.customer_id and Customers.deleted_at = 0)
        JOIN CustomerRewardsRegistration ON (CustomerRewardsRegistration.customer_id = CustomerRewardsStatus.customer_id and CustomerRewardsRegistration.deleted_at = 0)
        LEFT JOIN CustomerRewardsGracePeriods ON (CustomerRewardsGracePeriods.customer_id = CustomerRewardsStatus.customer_id and CustomerRewardsGracePeriods.deleted_at = 0)
        LEFT JOIN CustomerOverrides ON (CustomerOverrides.customer_id = Customers.id and CustomerOverrides.deleted_at = 0)
        LEFT JOIN (
                    SELECT
                      Customers.id AS customer_id,
                      CASE WHEN SUM(affiliateGroups.notDirectBilled) = 0
                        THEN 1
                      ELSE 0 END   AS qualified
                    FROM Customers
                      JOIN CustomerSegments ON (CustomerSegments.customer_id = Customers.id)
                      LEFT JOIN CustomerOverrides ON (CustomerOverrides.customer_id = Customers.id)
                      LEFT JOIN (
                                  SELECT
                                    aff_customer_id,
                                    SUM(CASE WHEN CustomerSegments.billto_customer_id != CustomerSegments.customer_id
                                      THEN 1
                                        ELSE 0 END) notDirectBilled
                                  FROM Customers
                                    JOIN CustomerSegments ON (CustomerSegments.customer_id = Customers.id)
                                  WHERE
                                    CustomerSegments.billto_customer_id = CustomerSegments.customer_id
                                  GROUP BY aff_customer_id
                                ) affiliateGroups
                        ON (affiliateGroups.aff_customer_id = COALESCE(Customers.aff_customer_id, Customers.id))
                    WHERE
                      CustomerSegments.billto_customer_id = CustomerSegments.customer_id AND
                      COALESCE(CustomerOverrides.customercategory_id, Customers.customercategory_id) IN (
                      Select distinct(customercategory_id) From RewardsTierDefinitions 
                      WHERE RewardsTierDefinitions.deleted_at = '0000-00-00 00:00:00'
                      )  AND
                      CustomerSegments.def_price_list = '303' AND
                      Customers.currency = 'USD'$singleAccountString
                    GROUP BY Customers.id
                  ) qualifiedCustomers ON (qualifiedCustomers.customer_id = Customers.id)
        WHERE CustomerRewardsStatus.deleted_at = 0
      GROUP BY CustomerRewardsStatus.customer_id) newTier
JOIN CustomerRewardsTiers currentTier ON (
      currentTier.customer_id = newTier.customer_id AND currentTier.deleted_at = 0 AND 
      CONCAT(currentTier.first_qualified_date,'_',currentTier.tier) = (SELECT CONCAT(maxCR.first_qualified_date,'_',tier) FROM CustomerRewardsTiers maxCR WHERE maxCR.customer_id = newTier.customer_id and maxCR.deleted_at = 0 order by first_qualified_date desc, tier desc limit 1)
    )
WHERE
  currentTier.tier > newTier.tier
  AND currentTier.latest_qualified_date <= DATE(DATE_SUB(NOW(), INTERVAL gracePeriod DAY))
;
SQLEND;
        return $sql;
    }
}