<?php

namespace App\Console\Commands;

use Carbon\Carbon;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Yajny\Member\Models\Currencies;
use Yajny\Member\Models\CurrencyRate;
use Yajny\Member\Models\Member;
use Yajny\Member\Models\PayoutMethods;
use Yajny\Member\Models\UserBonus;
use Yajny\Member\Models\UserPayoutMethods;
use Yajny\Member\Models\UserSales;

class MerchantsQuarterBonusCalculator extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'quarter_bonus:calculate {quarter}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Calculate Merchants quarter bonus';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return void
     */
    public function handle()
    {
        if (!in_array($this->argument('quarter'), ['q1', 'q2', 'q3', 'q4'])) {
            $this->output->write("enter valid quarter : q1, q2, q3, q4");
            return;
        }

        $rates = Currencies::query()
            ->pluck('conversion_rate','iso_code')->toArray();
        $merchants = Member::query()
            ->select('user_id')
            ->with('moderators:user_id,parent_id')
            ->where("is_merchant", true)
            ->where("parent_id", 0)
            ->get();
        $bonusRecordsToSave = [];
        foreach ($merchants as $merchant) {
            $cashbackGroupedByMonth = $this->merchantCashbackGroupedByMonth($merchant);
            if ($cashbackGroupedByMonth->count() != 3 || $cashbackGroupedByMonth->sum('spending') < 125000) {
                $this->output->write($merchant->user_id . " is not valid for bonus.\n : total Spending : " . $cashbackGroupedByMonth->sum('spending') . "\n Months : " . $cashbackGroupedByMonth->count() . "\n");
                continue;
            }
            $lowestCategory = [];
            foreach ($cashbackGroupedByMonth as $cashbackByMonth) {
                $this->getLowestCategory($cashbackByMonth->spending, $lowestCategory);
            }
            if (count($lowestCategory) == 0) {
                continue;
            }
            $userMethod = UserPayoutMethods::query()
                ->select('id', 'method_code')
                ->where('user_id', $merchant->user_id)
                ->where('enabled', true)
                ->first();
            if (!$userMethod) {
                $this->output->write($merchant->user_id . " don't have user method.\n");
                continue;
            }
            $method = PayoutMethods::where('code', $userMethod->method_code)->first();
            $currencyRate = $rates[$method->default_currency];
            $bonusRecordsToSave[] = $this->prepareUserBonusArray($merchant, $currencyRate, $cashbackGroupedByMonth, $lowestCategory, $method);
        }
        UserBonus::insert($bonusRecordsToSave);
    }

    public function merchantCashbackGroupedByMonth($merchant)
    {
        $dates = $this->quarters($this->argument('quarter'));
        return UserSales::query()
            ->select(
                DB::raw("
                    DATE_FORMAT(created_at, '%m') as month,
                    SUM(cashback_egp) as cashback_egp,
                    SUM(cashback_sar) as cashback_sar,
                    SUM(cashback_aed) as cashback_aed,
                    SUM(cashback_kwd) as cashback_kwd,
                    SUM(cashback) as cashback,
                    SUM(order_amount) as spending
                    "))
            ->whereIn('user_id', array_merge([$merchant->user_id], $merchant->moderators->pluck('user_id')->toArray()))
            ->where('status', 'confirmed')
            ->where('is_reset', false)
            ->whereDate('created_at', '>=', $dates['start'])
            ->whereDate('created_at', '<=', $dates['end'])
            ->groupBy(['month'])
            ->having('spending', '>=', 30000)
            ->get();
    }

    private function quarters(string $quarter): array
    {
        return [
            'q1' => [
                'start' => date('Y') . '-01-01',
                'end' => date('Y') . '-03-31',
            ],
            'q2' => [
                'start' => date('Y') . '-04-01',
                'end' => date('Y') . '-06-30',
            ],
            'q3' => [
                'start' => date('Y') . '-07-01',
                'end' => date('Y') . '-09-30',
            ],
            'q4' => [
                'start' => date('Y') . '-10-01',
                'end' => date('Y') . '-12-31',
            ],
        ][$quarter];
    }

    private function getLowestCategory($monthlySpending, &$chosenMonthlyCategory)
    {
        $filteredCategories = array_values(array_filter($this->categories(), function ($category) use ($monthlySpending) {
            return $monthlySpending >= $category['month'];
        }));
        $filteredCategory = end($filteredCategories);

        if (count($chosenMonthlyCategory) == 0) {
            $chosenMonthlyCategory = $filteredCategory;
        }
        if ($filteredCategory['month'] < $chosenMonthlyCategory['month']) {
            $chosenMonthlyCategory = $filteredCategory;
        }
    }

    private function categories(): array
    {
        return [
            [
                'month' => 30000,
                'quarter' => 125000,
                'percentage' => 0.05,
            ],
            [
                'month' => 40000,
                'quarter' => 150000,
                'percentage' => 0.07,
            ],
            [
                'month' => 60000,
                'quarter' => 200000,
                'percentage' => 0.10,
            ],
            [
                'month' => 80000,
                'quarter' => 300000,
                'percentage' => 0.12,

            ],
            [
                'month' => 100000,
                'quarter' => 400000,
                'percentage' => 0.15,
            ],
            [
                'month' => 150000,
                'quarter' => 500000,
                'percentage' => 0.17,
            ],
            [
                'month' => 180000,
                'quarter' => 600000,
                'percentage' => 0.20,
            ],
            [
                'month' => 200000,
                'quarter' => 700000,
                'percentage' => 0.22,
            ],
            [
                'month' => 220000,
                'quarter' => 800000,
                'percentage' => 0.25,
            ],
            [
                'month' => 250000,
                'quarter' => 900000,
                'percentage' => 0.27,
            ],
        ];
    }

    /**
     * @param $merchant
     * @param $currencyRate
     * @param $cashbackGroupedByMonth
     * @param $lowestCategory
     * @param $method
     * @return array
     */
    public function prepareUserBonusArray($merchant, $currencyRate, $cashbackGroupedByMonth, $lowestCategory, $method): array
    {
        return [
            'user_id' => $merchant->user_id,
            'bonus_code' => 'loyalty_program',
            'awarded_on' => Carbon::now()->format('Y-m-d'),
            'expires_on' => Carbon::now()->addDays(90),
            'expiration_date' => Carbon::now()->addDays(90)->format('Y-m-d'),
            'status' => 'pending',
            'currency' => $method->default_currency,
            'currency_rate' => $currencyRate,
            'is_reset' => false,
            'qualifying_amount' => $lowestCategory['month'],
            'amount' => $cashbackGroupedByMonth->sum('cashback') * $lowestCategory['percentage'],
            'local_amount' => $this->localCashbackAmountByCurrency($method->default_currency, $cashbackGroupedByMonth) * $lowestCategory['percentage'],
            'amount_egp' => $cashbackGroupedByMonth->sum('cashback_egp') * $lowestCategory['percentage'],
            'amount_sar' => $cashbackGroupedByMonth->sum('cashback_sar') * $lowestCategory['percentage'],
            'amount_aed' => $cashbackGroupedByMonth->sum('cashback_aed') * $lowestCategory['percentage'],
            'amount_kwd' => $cashbackGroupedByMonth->sum('cashback_kwd') * $lowestCategory['percentage'],
        ];
    }

    private function localCashbackAmountByCurrency($currency, $cashbackByMonths)
    {
        $cashback = [
            'EGP' => $cashbackByMonths->sum('cashback_egp'),
            'SAR' => $cashbackByMonths->sum('cashback_sar'),
            'AED' => $cashbackByMonths->sum('cashback_aed'),
            'KWD' => $cashbackByMonths->sum('cashback_kwd'),
        ];
        return $cashback[$currency] ?? $cashbackByMonths->sum('cashback');
    }
}