<?php
// app/Http/Controllers/ChlVoucherReportController.php

namespace App\Http\Controllers;

use App\Models\ChlVoucher;
use App\Models\ActionMaster;
use Illuminate\Http\Request;
use Illuminate\Http\JsonResponse;
use Illuminate\Support\Facades\DB;

class ChlVoucherReportController extends Controller
{

    public function getReport(Request $request)
    {
        try {

            $query = DB::table('chl_voucher as cv')
                ->leftJoin('dept_master as dm', 'cv.Dpt_Id', '=', 'dm.Dpt_id')
                ->leftJoin('action_master as am1', 'cv.Status', '=', 'am1.Act_Id')
                ->select(
                    'dm.Dpt_Name as Dept_Name',

                    DB::raw("
                JSON_ARRAYAGG(
                    JSON_OBJECT(
                        'mode', cv.Py_Mode,
                        'amount', cv.Amount,
                        'type', cv.Is_income
                    )
                ) as Pay_Mode_Details
            "),

                    DB::raw("SUM(CASE WHEN cv.Is_income = 'Y' THEN cv.Amount ELSE 0 END) as Total_Income"),

                    DB::raw("SUM(CASE WHEN cv.Is_income = 'N' THEN cv.Amount ELSE 0 END) as Total_Expense"),

                    DB::raw("
                SUM(CASE WHEN cv.Is_income = 'Y' THEN cv.Amount ELSE 0 END)
                -
                SUM(CASE WHEN cv.Is_income = 'N' THEN cv.Amount ELSE 0 END)
                as Difference
            ")
                );


            if ($request->filled('from_date') && $request->filled('to_date')) {
                $query->whereBetween('cv.Ch_Date', [$request->from_date, $request->to_date]);
            }

            if ($request->filled('dept_name')) {
                $query->where('dm.Dpt_Name', 'like', '%' . $request->dept_name . '%');
            }

            if ($request->filled('pay_mode')) {
                $query->where('cv.Py_Mode', 'like', '%' . $request->pay_mode . '%');
            }

            if ($request->filled('amount_min')) {
                $query->whereRaw('ABS(cv.Amount) >= ?', [$request->amount_min]);
            }

            if ($request->filled('amount_max')) {
                $query->whereRaw('ABS(cv.Amount) <= ?', [$request->amount_max]);
            }

            if ($request->filled('status')) {
                $query->whereRaw("COALESCE(am1.St_Name, 'N/A') LIKE ?", ['%' . $request->status . '%']);
            }

            if ($request->filled('type')) {
                if ($request->type == 'income') {
                    $query->where('cv.Is_income', 'Y');
                } elseif ($request->type == 'expense') {
                    $query->where('cv.Is_income', 'N');
                }
            }

            $query->groupBy('dm.Dpt_Name')
                ->orderBy('dm.Dpt_Name');

            $data = $query->get();

            $data->transform(function ($item) {

                $details = json_decode($item->Pay_Mode_Details, true);

                $grouped = [];

                foreach ($details as $d) {

                    $mode = $d['mode'] ?? 'N/A';
                    $amount = $d['amount'] ?? 0;
                    $type = $d['type'] ?? 'N';

                    if (!isset($grouped[$mode])) {
                        $grouped[$mode] = [
                            'mode' => $mode,
                            'income' => 0,
                            'expense' => 0,
                            'difference' => 0
                        ];
                    }

                    if ($type == 'Y') {
                        $grouped[$mode]['income'] += $amount;
                    } else {
                        $grouped[$mode]['expense'] += $amount;
                    }
                }

                foreach ($grouped as &$g) {
                    $g['difference'] = $g['income'] - $g['expense'];
                }

                $item->Pay_Mode_Details = array_values($grouped);

                return $item;
            });

            return response()->json([
                'success' => true,
                'data' => $data
            ]);
        } catch (\Exception $e) {

            return response()->json([
                'success' => false,
                'message' => 'Something went wrong. Please try again later.'
            ], 500);
        }
    }


    public function getDepartmentCashLedgers(Request $request): JsonResponse
    {
        try {

            $request->validate([
                'Dpt_Id' => 'required|exists:dept_master,Dpt_id',
                'from_date' => 'nullable|date',
                'to_date' => 'nullable|date|after_or_equal:from_date'
            ]);

            $departmentId = $request->Dpt_Id;

            $baseQuery = ChlVoucher::join('ledger_master', 'chl_voucher.Lg_Id', '=', 'ledger_master.Lg_Id')
                ->where('chl_voucher.Dpt_Id', $departmentId)
                ->where('chl_voucher.Py_Mode', 'Cash')
                ->whereNull('chl_voucher.deleted_at');

            if ($request->filled('from_date')) {
                $baseQuery->whereDate('chl_voucher.Ch_Date', '>=', $request->from_date);
            }

            if ($request->filled('to_date')) {
                $baseQuery->whereDate('chl_voucher.Ch_Date', '<=', $request->to_date);
            }

            $results = (clone $baseQuery)->select(
                'chl_voucher.Lg_Id',
                'ledger_master.Lg_Name',
                'ledger_master.Lg_Code',
                DB::raw('SUM(chl_voucher.Amount) as total_amount'),
                DB::raw('COUNT(chl_voucher.CV_Id) as transaction_count')
            )
                ->groupBy('chl_voucher.Lg_Id', 'ledger_master.Lg_Name', 'ledger_master.Lg_Code')
                ->orderBy('ledger_master.Lg_Name')
                ->get();

            $grandTotal = $results->sum('total_amount');

            $department = DB::table('dept_master')
                ->where('Dpt_id', $departmentId)
                ->select('Dpt_id', 'Dpt_Name')
                ->first();

            $detailedQuery = ChlVoucher::select(
                'chl_voucher.CV_Id',
                'chl_voucher.CV_No',
                'chl_voucher.Lg_Id',
                'ledger_master.Lg_Name',
                'ledger_master.Lg_Code',
                'chl_voucher.Amount',
                'chl_voucher.SL_Nm',
                'chl_voucher.Ch_Date',
                'chl_voucher.FileNo',
                'chl_voucher.Remarks'
            )
                ->join('ledger_master', 'chl_voucher.Lg_Id', '=', 'ledger_master.Lg_Id')
                ->where('chl_voucher.Dpt_Id', $departmentId)
                ->where('chl_voucher.Py_Mode', 'Cash')
                ->whereNull('chl_voucher.deleted_at');

            if ($request->filled('from_date')) {
                $detailedQuery->whereDate('chl_voucher.Ch_Date', '>=', $request->from_date);
            }

            if ($request->filled('to_date')) {
                $detailedQuery->whereDate('chl_voucher.Ch_Date', '<=', $request->to_date);
            }

            $detailedTransactions = $detailedQuery->orderBy('chl_voucher.Ch_Date', 'desc')->get();

            $transactionsByLedger = $detailedTransactions->groupBy('Lg_Id');

            $response = [
                "success" => true,
                "message" => "Department wise cash payment ledgers fetched successfully",
                "data" => [
                    "department" => [
                        "Dpt_Id" => $department->Dpt_id ?? null,
                        "Dpt_Name" => $department->Dpt_Name ?? null
                    ],
                    "payment_mode" => "Cash",
                    "filters" => [
                        "from_date" => $request->from_date,
                        "to_date" => $request->to_date
                    ],
                    "ledgers" => $results->map(function ($item) use ($transactionsByLedger) {
                        $formattedName = $item->Lg_Code . ' - ' . $item->Lg_Name;

                        $ledgerTransactions = $transactionsByLedger->get($item->Lg_Id, collect());

                        return [
                            "Lg_Id" => $item->Lg_Id,
                            "Lg_Name" => $formattedName,
                            "total_amount" => (float) $item->total_amount,
                            "transaction_count" => (int) $item->transaction_count,
                            "transactions" => $ledgerTransactions->map(function ($transaction) {
                                $formattedName = $transaction->Lg_Code . ' - ' . $transaction->Lg_Name;
                                return [
                                    "CV_Id" => $transaction->CV_Id,
                                    "CV_No" => $transaction->CV_No,
                                    "Lg_Name" => $formattedName,
                                    "Amount" => (float) $transaction->Amount,
                                    "SL_Nm" => $transaction->SL_Nm,
                                    "Ch_Date" => $transaction->Ch_Date,
                                    "FileNo" => $transaction->FileNo,
                                    "Remarks" => $transaction->Remarks
                                ];
                            })->values()
                        ];
                    })->values(),
                    "grand_total" => (float) $grandTotal
                ]
            ];

            return response()->json($response);
        } catch (\Illuminate\Validation\ValidationException $e) {
            return response()->json([
                "success" => false,
                "message" => "Validation failed",
                "errors" => $e->errors()
            ], 422);
        } catch (\Exception $e) {
            return response()->json([
                "success" => false,
                "message" => "Error fetching department cash ledgers",
                "error" => 'Something went wrong. Please try again later.'
            ], 500);
        }
    }
}
