<?php

namespace App\Http\Controllers\Api\Reports;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use App\Models\User;
use Illuminate\Support\Facades\DB;
use App\Service\AccountsTransactionService;
use Carbon\Carbon;
use Log;

class PayableReportController extends Controller
{
    public function get_all_vendors_summary(Request $request){

        $endDate = $request->input('end_date') ?: Carbon::today()->format('Y-m-d');
        $startDate = $request->input('start_date') ?: Carbon::today()->startOfMonth()->format('Y-m-d') ;
        
        //Object to get accounts details
        $transaction_accounts=new AccountsTransactionService;
        $payable=$transaction_accounts->get_accounts('accounts_payable');

        Log::info($payable['account_name']." == ".$payable['id']);

        // Assuming $[payables] contains the required account ID
        $vendors = User::whereHas('vendor_transactions', function ($query) use ($payable, $startDate, $endDate) {
                $query->where('account_id', $payable['id'])
                    ->whereBetween('transaction_date', [$startDate, $endDate]);
            })
        
            ->with(['vendor_transactions' => function ($query) use ($payable, $startDate, $endDate) {
                $query->where('account_id', $payable['id'])
                    ->select(
                        'vendor_id',
                        DB::raw('SUM(CASE WHEN transaction_date BETWEEN ? AND ? THEN debit ELSE 0 END) as total_debit'),
                        DB::raw('SUM(CASE WHEN transaction_date BETWEEN ? AND ? THEN credit ELSE 0 END) as total_credit')
                    )
                    ->addBinding([$startDate, $endDate, $startDate, $endDate], 'select')
                    ->groupBy('vendor_id');
            }])
            ->where('is_vendor',1)
            ->where('status', 1)
            ->select('id', 'display_name', 'balance')
            ->orderBy('display_name')
            ->get();

        // Calculate Total Paybles (Sum of Total Debit - Total Credit)
        $totalPaybles = $vendors->sum(function ($vendor) {
            $totalDebit = $vendor->vendor_transactions->sum('total_debit');
            $totalCredit = $vendor->vendor_transactions->sum('total_credit');
            return $totalCredit - $totalDebit; // Net payble per vendor
        });


        $response=[
            'success' => true,
            'message' => 'Vendors Summary Report Generated successfully',
            'total_paybles' => $totalPaybles,
            'startDate'=>$startDate,
            'endDate'=>$endDate,
            'account_name'=>$payable['account_name'],
            'account_id'=>$payable['id'],
            'data' => $vendors
        ];

        // Return a view or JSON response
        return response()->json($response);
    }
}
