<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class ChlStatusSummaryReportController extends Controller
{


public function deptWiseReport(Request $request)
{
    try {

        DB::statement("SET SESSION group_concat_max_len = 10000000");

        $query = DB::table('chl_voucher as cv')
            ->leftJoin('dept_master as dm', 'cv.Dpt_Id', '=', 'dm.Dpt_Id')
            ->leftJoin('ledger_master as lm', 'cv.Lg_Id', '=', 'lm.Lg_Id')
            ->leftJoin('action_master as am', 'cv.Status', '=', 'am.Act_Id');

        if ($request->filled('Dpt_Id')) {
            $query->where('cv.Dpt_Id', $request->Dpt_Id);
        }

        if ($request->filled('Is_income')) {
            $query->where('cv.Is_income', $request->Is_income);
        }

        if ($request->filled('Status')) {
            $query->where('am.St_Name', 'like', '%' . $request->Status . '%');
        }

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

        $subQuery = $query->select(
            'dm.Dpt_Id',
            'dm.Dpt_Name',
            'cv.CV_No',

            DB::raw('MAX(cv.CV_Sr) as CV_Sr'),
            DB::raw('MAX(cv.Ch_Date) as Ch_Date'),
            DB::raw('MAX(cv.Is_income) as Is_income'),
            DB::raw('MAX(lm.Lg_Name) as Lg_Name'),
            DB::raw('MAX(cv.Total_Amount) as Total_Amount'),
            DB::raw('MAX(cv.SL_Nm) as SL_Nm'),
            DB::raw('MAX(cv.GST_No) as GST_No'),
            DB::raw('MAX(cv.FileNo) as FileNo'),
            DB::raw('MAX(cv.Remarks) as Remarks'),
            DB::raw('MAX(am.St_Name) as Status'),
            DB::raw('MAX(cv.Bank_Name) as Bank_Name'),
            DB::raw('MAX(cv.Acc_No) as Acc_No'),
            DB::raw('MAX(cv.Chq_No) as Chq_No'),
            DB::raw('MAX(cv.IFSC_Code) as IFSC_Code'),

            DB::raw("
                CONCAT(
                    '[',
                    GROUP_CONCAT(
                        CONCAT(
                            '{\"Py_Mode\":\"', cv.Py_Mode,
                            '\",\"Amount\":', cv.Amount,
                            ',\"Chq_No\":',
                            IF(cv.Py_Mode='Cheque',
                                CONCAT('\"', cv.Chq_No, '\"'),
                                'null'
                            ),
                            '}'
                        )
                    ),
                    ']'
                ) as Payment_Details
            ")
        )
        ->groupBy('dm.Dpt_Id', 'dm.Dpt_Name', 'cv.CV_No');

        $data = DB::table(DB::raw("({$subQuery->toSql()}) as t"))
            ->mergeBindings($subQuery)
            ->select(
                'Dpt_Id',
                'Dpt_Name',

                DB::raw("
                    CONCAT(
                        '[',
                        GROUP_CONCAT(
                            CONCAT(
                                '{\"CV_No\":\"', CV_No,
                                '\",\"CV_Sr\":', CV_Sr,
                                ',\"SL_Nm\":\"', IFNULL(SL_Nm,''), '\"',
                                ',\"Acc_No\":\"', IFNULL(Acc_No,''), '\"',
                                ',\"Chq_No\":', IF(Chq_No IS NULL,'null',CONCAT('\"',Chq_No,'\"')),
                                ',\"FileNo\":\"', IFNULL(FileNo,''), '\"',
                                ',\"GST_No\":\"', IFNULL(GST_No,''), '\"',
                                ',\"Status\":\"', IFNULL(Status,''), '\"',
                                ',\"Ch_Date\":\"', Ch_Date, '\"',
                                ',\"Lg_Name\":\"', IFNULL(Lg_Name,''), '\"',
                                ',\"Remarks\":\"', IFNULL(Remarks,''), '\"',
                                ',\"Bank_Name\":\"', IFNULL(Bank_Name,''), '\"',
                                ',\"IFSC_Code\":\"', IFNULL(IFSC_Code,''), '\"',
                                ',\"Is_income\":\"', IFNULL(Is_income,''), '\"',
                                ',\"Total_Amount\":', IFNULL(Total_Amount,'null'),
                                ',\"Payment_Details\":', Payment_Details,
                                '}'
                            )
                        ),
                        ']'
                    ) as Voucher_List
                ")
            )
            ->groupBy('Dpt_Id', 'Dpt_Name')
            ->get();

        $data->transform(function ($item) {
            $item->Voucher_List = json_decode($item->Voucher_List, true);
            return $item;
        });

        return response()->json([
            'status' => true,
            'message' => 'Department Wise Report',
            'data' => $data
        ]);

    } catch (\Exception $e) {
        return response()->json([
            'status' => false,
            'message' => 'Error',
            'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage()
        ], 500);
    }
}

}
