<?php

namespace App\Modules\Admin\Dashboard;

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

class Transactions
{
    public function getData($payload)
    {
        $selectedYear = $payload->selectedYear;
        $unpaidApprovedInvoiceTransactions = $this->unpaidApprovedInvoiceTransactions($selectedYear);
        $partiallyPaidApprovedInvoiceTransactions = $this->partiallyPaidApprovedInvoiceTransactions($selectedYear);
        $paidApprovedInvoiceTransactions = $this->paidApprovedInvoiceTransactions($selectedYear);
        $allCompanyTransactions = $this->allCompanyTransactions($unpaidApprovedInvoiceTransactions, $partiallyPaidApprovedInvoiceTransactions, $paidApprovedInvoiceTransactions);
        return [
            'allCompanyTransactions' => $allCompanyTransactions,
            'unpaidApprovedInvoiceTransactions' => $unpaidApprovedInvoiceTransactions,
            'partiallyPaidApprovedInvoiceTransactions' => $partiallyPaidApprovedInvoiceTransactions,
            'paidApprovedInvoiceTransactions' => $paidApprovedInvoiceTransactions,
        ];
    }
    
    public function allCompanyTransactions($unpaidApprovedInvoiceTransactions, $partiallyPaidApprovedInvoiceTransactions, $paidApprovedInvoiceTransactions)
    {
        return [
            'January' => $unpaidApprovedInvoiceTransactions['January'] + $partiallyPaidApprovedInvoiceTransactions['January'] + $paidApprovedInvoiceTransactions['January'],
            'February' => $unpaidApprovedInvoiceTransactions['February'] + $partiallyPaidApprovedInvoiceTransactions['February'] + $paidApprovedInvoiceTransactions['February'],
            'March' => $unpaidApprovedInvoiceTransactions['March'] + $partiallyPaidApprovedInvoiceTransactions['March'] + $paidApprovedInvoiceTransactions['March'],
            'April' => $unpaidApprovedInvoiceTransactions['April'] + $partiallyPaidApprovedInvoiceTransactions['April'] + $paidApprovedInvoiceTransactions['April'],
            'May' => $unpaidApprovedInvoiceTransactions['May'] + $partiallyPaidApprovedInvoiceTransactions['May'] + $paidApprovedInvoiceTransactions['May'],
            'June' => $unpaidApprovedInvoiceTransactions['June'] + $partiallyPaidApprovedInvoiceTransactions['June'] + $paidApprovedInvoiceTransactions['June'],
            'July' => $unpaidApprovedInvoiceTransactions['July'] + $partiallyPaidApprovedInvoiceTransactions['July'] + $paidApprovedInvoiceTransactions['July'],
            'August' => $unpaidApprovedInvoiceTransactions['August'] + $partiallyPaidApprovedInvoiceTransactions['August'] + $paidApprovedInvoiceTransactions['August'],
            'September' => $unpaidApprovedInvoiceTransactions['September'] + $partiallyPaidApprovedInvoiceTransactions['September'] + $paidApprovedInvoiceTransactions['September'],
            'October' => $unpaidApprovedInvoiceTransactions['October'] + $partiallyPaidApprovedInvoiceTransactions['October'] + $paidApprovedInvoiceTransactions['October'],
            'November' => $unpaidApprovedInvoiceTransactions['November'] + $partiallyPaidApprovedInvoiceTransactions['November'] + $paidApprovedInvoiceTransactions['November'],
            'December' => $unpaidApprovedInvoiceTransactions['December'] + $partiallyPaidApprovedInvoiceTransactions['December'] + $paidApprovedInvoiceTransactions['December'],
        ];
    }

    public function unpaidApprovedInvoiceTransactions($selectedYear)
    {
        return LoanBorrowList::select(
            DB::raw('
                IFNULL(COUNT(CASE MONTH(IBR.cutoff_date) WHEN 1 THEN loan_borrow_lists.id END), 0) AS January,
                IFNULL(COUNT(CASE MONTH(IBR.cutoff_date) WHEN 2 THEN loan_borrow_lists.id END), 0) AS February,
                IFNULL(COUNT(CASE MONTH(IBR.cutoff_date) WHEN 3 THEN loan_borrow_lists.id END), 0) AS March,
                IFNULL(COUNT(CASE MONTH(IBR.cutoff_date) WHEN 4 THEN loan_borrow_lists.id END), 0) AS April,
                IFNULL(COUNT(CASE MONTH(IBR.cutoff_date) WHEN 5 THEN loan_borrow_lists.id END), 0) AS May,
                IFNULL(COUNT(CASE MONTH(IBR.cutoff_date) WHEN 6 THEN loan_borrow_lists.id END), 0) AS June,
                IFNULL(COUNT(CASE MONTH(IBR.cutoff_date) WHEN 7 THEN loan_borrow_lists.id END), 0) AS July,
                IFNULL(COUNT(CASE MONTH(IBR.cutoff_date) WHEN 8 THEN loan_borrow_lists.id END), 0) AS August,
                IFNULL(COUNT(CASE MONTH(IBR.cutoff_date) WHEN 9 THEN loan_borrow_lists.id END), 0) AS September,
                IFNULL(COUNT(CASE MONTH(IBR.cutoff_date) WHEN 10 THEN loan_borrow_lists.id END), 0) AS October,
                IFNULL(COUNT(CASE MONTH(IBR.cutoff_date) WHEN 11 THEN loan_borrow_lists.id END), 0) AS November,
                IFNULL(COUNT(CASE MONTH(IBR.cutoff_date) WHEN 12 THEN loan_borrow_lists.id END), 0) AS December
            '))
            ->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')
            ->where('OBR.conx_status', 'Unpaid')
            ->whereYear('IBR.cutoff_date', $selectedYear)
            ->first();
    }

    public function partiallyPaidApprovedInvoiceTransactions($selectedYear)
    {
        return LoanBorrowList::select(
            DB::raw('
                IFNULL(COUNT(CASE MONTH(IBR.cutoff_date) WHEN 1 THEN loan_borrow_lists.id END), 0) AS January,
                IFNULL(COUNT(CASE MONTH(IBR.cutoff_date) WHEN 2 THEN loan_borrow_lists.id END), 0) AS February,
                IFNULL(COUNT(CASE MONTH(IBR.cutoff_date) WHEN 3 THEN loan_borrow_lists.id END), 0) AS March,
                IFNULL(COUNT(CASE MONTH(IBR.cutoff_date) WHEN 4 THEN loan_borrow_lists.id END), 0) AS April,
                IFNULL(COUNT(CASE MONTH(IBR.cutoff_date) WHEN 5 THEN loan_borrow_lists.id END), 0) AS May,
                IFNULL(COUNT(CASE MONTH(IBR.cutoff_date) WHEN 6 THEN loan_borrow_lists.id END), 0) AS June,
                IFNULL(COUNT(CASE MONTH(IBR.cutoff_date) WHEN 7 THEN loan_borrow_lists.id END), 0) AS July,
                IFNULL(COUNT(CASE MONTH(IBR.cutoff_date) WHEN 8 THEN loan_borrow_lists.id END), 0) AS August,
                IFNULL(COUNT(CASE MONTH(IBR.cutoff_date) WHEN 9 THEN loan_borrow_lists.id END), 0) AS September,
                IFNULL(COUNT(CASE MONTH(IBR.cutoff_date) WHEN 10 THEN loan_borrow_lists.id END), 0) AS October,
                IFNULL(COUNT(CASE MONTH(IBR.cutoff_date) WHEN 11 THEN loan_borrow_lists.id END), 0) AS November,
                IFNULL(COUNT(CASE MONTH(IBR.cutoff_date) WHEN 12 THEN loan_borrow_lists.id END), 0) AS December
            '))
            ->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')
            ->where('OBR.conx_status', 'Partial')
            ->whereYear('IBR.cutoff_date', $selectedYear)
            ->first();
    }

    public function paidApprovedInvoiceTransactions($selectedYear)
    {
        return LoanBorrowList::select(
            DB::raw('
                IFNULL(COUNT(CASE MONTH(IBR.cutoff_date) WHEN 1 THEN loan_borrow_lists.id END), 0) AS January,
                IFNULL(COUNT(CASE MONTH(IBR.cutoff_date) WHEN 2 THEN loan_borrow_lists.id END), 0) AS February,
                IFNULL(COUNT(CASE MONTH(IBR.cutoff_date) WHEN 3 THEN loan_borrow_lists.id END), 0) AS March,
                IFNULL(COUNT(CASE MONTH(IBR.cutoff_date) WHEN 4 THEN loan_borrow_lists.id END), 0) AS April,
                IFNULL(COUNT(CASE MONTH(IBR.cutoff_date) WHEN 5 THEN loan_borrow_lists.id END), 0) AS May,
                IFNULL(COUNT(CASE MONTH(IBR.cutoff_date) WHEN 6 THEN loan_borrow_lists.id END), 0) AS June,
                IFNULL(COUNT(CASE MONTH(IBR.cutoff_date) WHEN 7 THEN loan_borrow_lists.id END), 0) AS July,
                IFNULL(COUNT(CASE MONTH(IBR.cutoff_date) WHEN 8 THEN loan_borrow_lists.id END), 0) AS August,
                IFNULL(COUNT(CASE MONTH(IBR.cutoff_date) WHEN 9 THEN loan_borrow_lists.id END), 0) AS September,
                IFNULL(COUNT(CASE MONTH(IBR.cutoff_date) WHEN 10 THEN loan_borrow_lists.id END), 0) AS October,
                IFNULL(COUNT(CASE MONTH(IBR.cutoff_date) WHEN 11 THEN loan_borrow_lists.id END), 0) AS November,
                IFNULL(COUNT(CASE MONTH(IBR.cutoff_date) WHEN 12 THEN loan_borrow_lists.id END), 0) AS December
            '))
            ->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')
            ->where('OBR.conx_status', 'Paid')
            ->whereYear('IBR.cutoff_date', $selectedYear)
            ->first();
    }
}