<?php

namespace App\Modules\Admin\Dashboard;

use App\Models\CompanySubscriptionPayments;
use App\Models\LoanBorrowList;
use Illuminate\Support\Facades\DB;

class Earnings
{
    public function getData($payload)
    {
        $selectedYear = $payload->selectedYear;
        $subscriptionFeeEarningsPerMonth = $this->subscriptionFeeEarningsPerMonth($selectedYear);
        $serviceChargeEarningsPerMonth = $this->serviceChargeEarningsPerMonth($selectedYear);
        $totalEarningsPerMonth = $this->totalEarningsPerMonth($subscriptionFeeEarningsPerMonth, $serviceChargeEarningsPerMonth);
        return [
            'subscriptionFeeEarningsPerMonth' => $subscriptionFeeEarningsPerMonth,
            'serviceChargeEarningsPerMonth' => $serviceChargeEarningsPerMonth,
            'totalEarningsPerMonth' => $totalEarningsPerMonth,
        ];
    }

    public function subscriptionFeeEarningsPerMonth($selectedYear)
    {
        return CompanySubscriptionPayments::whereYear('created_at', $selectedYear)
            ->select(
                DB::raw('
                    IFNULL(SUM(CASE MONTH(created_at) WHEN  1 THEN company_subscription_payments.amount_paid END), 0) AS January,
                    IFNULL(SUM(CASE MONTH(created_at) WHEN  2 THEN company_subscription_payments.amount_paid END), 0) AS February,
                    IFNULL(SUM(CASE MONTH(created_at) WHEN  3 THEN company_subscription_payments.amount_paid END), 0) AS March,
                    IFNULL(SUM(CASE MONTH(created_at) WHEN  4 THEN company_subscription_payments.amount_paid END), 0) AS April,
                    IFNULL(SUM(CASE MONTH(created_at) WHEN  5 THEN company_subscription_payments.amount_paid END), 0) AS May,
                    IFNULL(SUM(CASE MONTH(created_at) WHEN  6 THEN company_subscription_payments.amount_paid END), 0) AS June,
                    IFNULL(SUM(CASE MONTH(created_at) WHEN  7 THEN company_subscription_payments.amount_paid END), 0) AS July,
                    IFNULL(SUM(CASE MONTH(created_at) WHEN  8 THEN company_subscription_payments.amount_paid END), 0) AS August,
                    IFNULL(SUM(CASE MONTH(created_at) WHEN  9 THEN company_subscription_payments.amount_paid END), 0) AS September,
                    IFNULL(SUM(CASE MONTH(created_at) WHEN 10 THEN company_subscription_payments.amount_paid END), 0) AS October,
                    IFNULL(SUM(CASE MONTH(created_at) WHEN 11 THEN company_subscription_payments.amount_paid END), 0) AS November,
                    IFNULL(SUM(CASE MONTH(created_at) WHEN 12 THEN company_subscription_payments.amount_paid END), 0) AS December
                ')
            )
            ->first();
    }

    public function serviceChargeEarningsPerMonth($selectedYear)
    {
        $serviceChargeEarningsPerMonth = [
            'January' => 0,
            'February' => 0,
            'March' => 0,
            'April' => 0,
            'May' => 0,
            'June' => 0,
            'July' => 0,
            'August' => 0,
            'September' => 0,
            'October' => 0,
            'November' => 0,
            'December' => 0,
        ];

        $loanBorrowListData = LoanBorrowList::select(
            'loan_borrow_lists.id',
            'loan_borrow_lists.original_rate',
            'loan_borrow_lists.rate',
            'loan_borrow_lists.tax',
            'IBR.days',
            'IBR.other_expense',
            'IBR.cutoff_date')
            ->join('outbound_billing_requests AS OBR', 'OBR.loan_borrow_lists_id', '=', 'loan_borrow_lists.id')
            ->join('inbound_billing_requests AS IBR', 'IBR.loan_borrow_lists_id', '=', 'loan_borrow_lists.id')
            ->where('loan_borrow_lists.status', 'Approved')
            ->whereYear('IBR.cutoff_date', $selectedYear)
            ->get();

        foreach ($loanBorrowListData as $loanBorrowList) {
            $originalRate = $loanBorrowList->original_rate;
            $rate = $loanBorrowList->rate;
            $days = json_decode($loanBorrowList->days, true);
            $cutoffDate = $loanBorrowList->cutoff_date;
            $cutoffDateMonth = date('m', strtotime($cutoffDate));

            $subtotalOriginalRate = 0;
            $subtotalNewRate = 0;
            $serviceCharge = 0;

            foreach ($days as $day) {
                $hours = $day['hours'];

                $subtotalOriginalRate = $subtotalOriginalRate + ($hours * $originalRate);
                $subtotalNewRate = $subtotalNewRate + ($hours * $rate);
            }

            $serviceCharge = $subtotalNewRate - $subtotalOriginalRate;

            if ($cutoffDateMonth == 1) {
                $serviceChargeEarningsPerMonth['January'] = $serviceChargeEarningsPerMonth['January'] + $serviceCharge;
            } else if ($cutoffDateMonth == 2) {
                $serviceChargeEarningsPerMonth['February'] = $serviceChargeEarningsPerMonth['February'] + $serviceCharge;
            } else if ($cutoffDateMonth == 3) {
                $serviceChargeEarningsPerMonth['March'] = $serviceChargeEarningsPerMonth['March'] + $serviceCharge;
            } else if ($cutoffDateMonth == 4) {
                $serviceChargeEarningsPerMonth['April'] = $serviceChargeEarningsPerMonth['April'] + $serviceCharge;
            } else if ($cutoffDateMonth == 5) {
                $serviceChargeEarningsPerMonth['May'] = $serviceChargeEarningsPerMonth['May'] + $serviceCharge;
            } else if ($cutoffDateMonth == 6) {
                $serviceChargeEarningsPerMonth['June'] = $serviceChargeEarningsPerMonth['June'] + $serviceCharge;
            } else if ($cutoffDateMonth == 7) {
                $serviceChargeEarningsPerMonth['July'] = $serviceChargeEarningsPerMonth['July'] + $serviceCharge;
            } else if ($cutoffDateMonth == 8) {
                $serviceChargeEarningsPerMonth['August'] = $serviceChargeEarningsPerMonth['August'] + $serviceCharge;
            } else if ($cutoffDateMonth == 9) {
                $serviceChargeEarningsPerMonth['September'] = $serviceChargeEarningsPerMonth['September'] + $serviceCharge;
            } else if ($cutoffDateMonth == 10) {
                $serviceChargeEarningsPerMonth['October'] = $serviceChargeEarningsPerMonth['October'] + $serviceCharge;
            } else if ($cutoffDateMonth == 11) {
                $serviceChargeEarningsPerMonth['November'] = $serviceChargeEarningsPerMonth['November'] + $serviceCharge;
            } else if ($cutoffDateMonth == 12) {
                $serviceChargeEarningsPerMonth['December'] = $serviceChargeEarningsPerMonth['December'] + $serviceCharge;
            }
        }

        return $serviceChargeEarningsPerMonth;
    }

    public function totalEarningsPerMonth($companySubscriptionPayments, $serviceChargeEarningsPerMonth)
    {
        return [
            'January' => $companySubscriptionPayments->January + $serviceChargeEarningsPerMonth['January'],
            'February' => $companySubscriptionPayments->February + $serviceChargeEarningsPerMonth['February'],
            'March' => $companySubscriptionPayments->March + $serviceChargeEarningsPerMonth['March'],
            'April' => $companySubscriptionPayments->April + $serviceChargeEarningsPerMonth['April'],
            'May' => $companySubscriptionPayments->May + $serviceChargeEarningsPerMonth['May'],
            'June' => $companySubscriptionPayments->June + $serviceChargeEarningsPerMonth['June'],
            'July' => $companySubscriptionPayments->July + $serviceChargeEarningsPerMonth['July'],
            'August' => $companySubscriptionPayments->August + $serviceChargeEarningsPerMonth['August'],
            'September' => $companySubscriptionPayments->September + $serviceChargeEarningsPerMonth['September'],
            'October' => $companySubscriptionPayments->October + $serviceChargeEarningsPerMonth['October'],
            'November' => $companySubscriptionPayments->November + $serviceChargeEarningsPerMonth['November'],
            'December' => $companySubscriptionPayments->December + $serviceChargeEarningsPerMonth['December'],
        ];
    }
}