<?php

namespace App\Http\Controllers;

use App\Models\FinanTrn;
use App\Models\PrtyMast;
use App\Models\CoMaster;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Validator;

class FinanTrnController extends Controller
{
    public function index()
    {
        // Retrieve records sorted by vou_dt in ascending order
        $finanTrns = FinanTrn::with(['coMaster', 'branchM', 'prtyMast'])
            ->orderBy('vou_dt', 'asc')
            ->get();

        return response()->json($finanTrns, 200);
    }

    public function show($id)
    {
        $finanTrn = FinanTrn::with(['coMaster', 'branchM', 'prtyMast'])
            ->findOrFail($id);

        return response()->json($finanTrn, 200);
    }
    public function store(Request $request)
    {
        // Validation rules based on Excel schema
        $validator = Validator::make($request->all(), [
            'Co_Id' => 'required|integer|exists:co_masters,Co_Id',
            'Br_Id' => 'required|integer|exists:branch_ms,Br_Id',
            'Pr_Id' => 'nullable|integer|exists:prty_masts,Pr_Id',
            'vou_ty' => 'nullable|string|max:3',
            'vou_id' => 'nullable|integer',
            'vou_sr' => 'nullable|integer|min:1',
            'vou_ln' => 'nullable|integer|min:1',
            'vou_rf' => 'nullable|string|max:150',
            'vou_dt' => 'nullable|date',
            'inq_id' => 'nullable|numeric',
            'ag_id' => 'nullable|string|max:25',
            'inst_no' => 'nullable|integer',
            'agt_amt' => 'nullable|numeric',
            'cr_amt' => 'nullable|numeric|between:0,9999999999.99',
            'dr_amt' => 'nullable|numeric|between:0,9999999999.99',
            'ref_id' => 'nullable|numeric',
            'chq_no' => 'nullable|string|max:50',
            'chq_dt' => 'nullable|date',
            'clr_dt' => 'nullable|date',
            'bk_id' => 'nullable|integer',
            'lot_no' => 'nullable|numeric',
            'rmk1' => 'nullable|string|max:150',
            'rmk2' => 'nullable|string|max:150',
            'rmk3' => 'nullable|string|max:150',
            'rmk4' => 'nullable|string|max:150',
            'rmk5' => 'nullable|string|max:150',
            'chq_mode' => 'nullable|string|max:1',
            'r_mode' => 'nullable|string|max:2',
            'sms_stus' => 'nullable|string|max:1',
            'email_stus' => 'nullable|string|max:1',
            'sm_id' => 'nullable|integer',
            'an_id' => 'nullable|string|max:40',
            'bfr1' => 'nullable|string|max:50',
            'bfr2' => 'nullable|string|max:50',
            'op_id' => 'nullable|integer',
            'dt_tm' => 'nullable|string|max:10',
            'pay_mode' => 'nullable|string|max:5',
            'exp_ref' => 'nullable|integer',
        ]);

        // Custom validation for cr_amt or dr_amt
        $validator->after(function ($validator) use ($request) {
            if (!$request->has('cr_amt') && !$request->has('dr_amt')) {
                $validator->errors()->add('cr_amt', 'Either cr_amt or dr_amt must be provided.');
                $validator->errors()->add('dr_amt', 'Either cr_amt or dr_amt must be provided.');
            }
            if ($request->has('cr_amt') && $request->has('dr_amt') && $request->cr_amt !== null && $request->dr_amt !== null) {
                $validator->errors()->add('cr_amt', 'Only one of cr_amt or dr_amt can be provided.');
                $validator->errors()->add('dr_amt', 'Only one of cr_amt or dr_amt can be provided.');
            }
        });

        if ($validator->fails()) {
            return response()->json(['errors' => $validator->errors()], 422);
        }

        // Determine transaction type and set vou_ty
        $isCredit = $request->has('cr_amt') && $request->cr_amt !== null;
        $data = $request->all();
        $data['vou_ty'] = $isCredit ? 'PAY' : 'RCP';

        // Set fallbacks for nullable fields
        $data['vou_id'] = isset($data['vou_id']) ? $data['vou_id'] : (FinanTrn::max('vou_id') + 1 ?? 1);
        $data['vou_sr'] = isset($data['vou_sr']) ? $data['vou_sr'] : 1;
        $data['vou_ln'] = isset($data['vou_ln']) ? $data['vou_ln'] : 1;
        $data['v_mode'] = 'G'; // Hardcode v_mode to 'G'

        // Set Pr_Id to prty_mast.Pr_Id where pr_code = 'BKRBC01'
        $prtyMast = PrtyMast::where('pr_code', 'BKRBC01')->first();
        $data['Pr_Id'] = $prtyMast ? $prtyMast->Pr_Id : null;
        $data['vou_rf'] = $prtyMast ? $prtyMast->pr_code : ($data['vou_rf'] ?? null);

        // First entry
        $finanTrn = FinanTrn::create($data);

        // Second entry (same vou_ty, opposite amount, Pr_Id = null)
        $secondEntry = $data;
        $secondEntry['Pr_Id'] = null; // Blank for second entry
        $secondEntry['vou_ty'] = $isCredit ? 'PAY' : 'RCP'; // Same as first entry
        $secondEntry['vou_ln'] = $data['vou_ln'] + 1;
        $secondEntry['vou_sr'] = $data['vou_sr'] + 1;
        $secondEntry['cr_amt'] = $isCredit ? null : $data['dr_amt'];
        $secondEntry['dr_amt'] = $isCredit ? $data['cr_amt'] : null;
        FinanTrn::create($secondEntry);

        return response()->json($finanTrn->load(['coMaster', 'branchM', 'prtyMast']), 201);
    }

    public function storeSingle(Request $request)
    {
        // Validation rules based on Excel schema
        $validator = Validator::make($request->all(), [
            'Co_Id' => 'required|integer|exists:co_masters,Co_Id',
            'Br_Id' => 'required|integer|exists:branch_ms,Br_Id',
            'Pr_Id' => 'nullable|integer|exists:prty_masts,Pr_Id',
            'vou_ty' => 'nullable|string|max:3',
            'vou_id' => 'nullable|integer',
            'vou_sr' => 'nullable|integer|min:1',
            'vou_ln' => 'nullable|integer|min:1',
            'vou_rf' => 'nullable|string|max:150',
            'vou_dt' => 'nullable|date',
            'inq_id' => 'nullable|numeric',
            'ag_id' => 'nullable|string|max:25',
            'inst_no' => 'nullable|integer',
            'agt_amt' => 'nullable|numeric',
            'cr_amt' => 'nullable|numeric|between:0,9999999999.99',
            'dr_amt' => 'nullable|numeric|between:0,9999999999.99',
            'ref_id' => 'nullable|numeric',
            'chq_no' => 'nullable|string|max:50',
            'chq_dt' => 'nullable|date',
            'clr_dt' => 'nullable|date',
            'bk_id' => 'nullable|integer',
            'lot_no' => 'nullable|numeric',
            'rmk1' => 'nullable|string|max:150',
            'rmk2' => 'nullable|string|max:150',
            'rmk3' => 'nullable|string|max:150',
            'rmk4' => 'nullable|string|max:150',
            'rmk5' => 'nullable|string|max:150',
            'chq_mode' => 'nullable|string|max:1',
            'r_mode' => 'nullable|string|max:2',
            'sms_stus' => 'nullable|string|max:1',
            'email_stus' => 'nullable|string|max:1',
            'sm_id' => 'nullable|integer',
            'an_id' => 'nullable|string|max:40',
            'bfr1' => 'nullable|string|max:50',
            'bfr2' => 'nullable|string|max:50',
            'op_id' => 'nullable|integer',
            'dt_tm' => 'nullable|string|max:10',
            'pay_mode' => 'nullable|string|max:5',
            'exp_ref' => 'nullable|integer',
        ]);

        // Custom validation for cr_amt or dr_amt
        $validator->after(function ($validator) use ($request) {
            if (!$request->has('cr_amt') && !$request->has('dr_amt')) {
                $validator->errors()->add('cr_amt', 'Either cr_amt or dr_amt must be provided.');
                $validator->errors()->add('dr_amt', 'Either cr_amt or dr_amt must be provided.');
            }
            if ($request->has('cr_amt') && $request->has('dr_amt') && $request->cr_amt !== null && $request->dr_amt !== null) {
                $validator->errors()->add('cr_amt', 'Only one of cr_amt or dr_amt can be provided.');
                $validator->errors()->add('dr_amt', 'Only one of cr_amt or dr_amt can be provided.');
            }
        });

        if ($validator->fails()) {
            return response()->json(['errors' => $validator->errors()], 422);
        }

        // Determine transaction type and set vou_ty
        $isCredit = $request->has('cr_amt') && $request->cr_amt !== null;
        $data = $request->all();
        $data['vou_ty'] = $isCredit ? 'PAY' : 'RCP';

        // Set fallbacks for nullable fields
        $data['vou_id'] = isset($data['vou_id']) ? $data['vou_id'] : (FinanTrn::max('vou_id') + 1 ?? 1);
        $data['vou_sr'] = isset($data['vou_sr']) ? $data['vou_sr'] : 1;
        $data['vou_ln'] = isset($data['vou_ln']) ? $data['vou_ln'] : 1;
        $data['v_mode'] = 'G'; // Hardcode v_mode to 'G'

        // Set Pr_Id to prty_mast.Pr_Id where pr_code = 'BKRBC01' if not provided
        if (!$request->filled('Pr_Id')) {
            $prtyMast = PrtyMast::where('pr_code', 'BKRBC01')->first();
            $data['Pr_Id'] = $prtyMast ? $prtyMast->Pr_Id : null;
            $data['vou_rf'] = $prtyMast ? $prtyMast->pr_code : ($data['vou_rf'] ?? null);
        }

        // Create single entry
        $finanTrn = FinanTrn::create($data);

        return response()->json($finanTrn->load(['coMaster', 'branchM', 'prtyMast']), 201);
    }

    public function update(Request $request, $id)
    {
        $finanTrn = FinanTrn::findOrFail($id);

        // Validation rules
        $validator = Validator::make($request->all(), [
            'Co_Id' => 'required|integer|exists:co_masters,Co_Id',
            'Br_Id' => 'required|integer|exists:branch_ms,Br_Id',
            'Pr_Id' => 'nullable|integer|exists:prty_masts,Pr_Id',
            'vou_ty' => 'nullable|string|max:3',
            'vou_id' => 'nullable|integer',
            'vou_sr' => 'nullable|integer|min:1',
            'vou_ln' => 'nullable|integer|min:1',
            'vou_rf' => 'nullable|string|max:150',
            'vou_dt' => 'nullable|date',
            'inq_id' => 'nullable|numeric',
            'ag_id' => 'nullable|string|max:25',
            'inst_no' => 'nullable|integer',
            'agt_amt' => 'nullable|numeric',
            'cr_amt' => 'nullable|numeric|between:0,9999999999.99',
            'dr_amt' => 'nullable|numeric|between:0,9999999999.99',
            'ref_id' => 'nullable|numeric',
            'chq_no' => 'nullable|string|max:50',
            'chq_dt' => 'nullable|date',
            'clr_dt' => 'nullable|date',
            'bk_id' => 'nullable|integer',
            'lot_no' => 'nullable|numeric',
            'rmk1' => 'nullable|string|max:150',
            'rmk2' => 'nullable|string|max:150',
            'rmk3' => 'nullable|string|max:150',
            'rmk4' => 'nullable|string|max:150',
            'rmk5' => 'nullable|string|max:150',
            'chq_mode' => 'nullable|string|max:1',
            'r_mode' => 'nullable|string|max:2',
            'sms_stus' => 'nullable|string|max:1',
            'email_stus' => 'nullable|string|max:1',
            'sm_id' => 'nullable|integer',
            'an_id' => 'nullable|string|max:40',
            'bfr1' => 'nullable|string|max:50',
            'bfr2' => 'nullable|string|max:50',
            'op_id' => 'nullable|integer',
            'dt_tm' => 'nullable|string|max:10',
            'pay_mode' => 'nullable|string|max:5',
            'exp_ref' => 'nullable|integer',
        ]);

        // Custom validation for cr_amt and dr_amt (only if provided)
        $validator->after(function ($validator) use ($request) {
            if ($request->has('cr_amt') && $request->has('dr_amt') && $request->cr_amt !== null && $request->dr_amt !== null) {
                $validator->errors()->add('cr_amt', 'Only one of cr_amt or dr_amt can be provided.');
                $validator->errors()->add('dr_amt', 'Only one of cr_amt or dr_amt can be provided.');
            }
        });

        if ($validator->fails()) {
            return response()->json(['errors' => $validator->errors()], 422);
        }

        // Prepare data for update
        $data = $request->all();

        // Determine transaction type if cr_amt or dr_amt is provided
        $isCredit = $request->has('cr_amt') && $request->cr_amt !== null;
        if ($request->has('cr_amt') || $request->has('dr_amt')) {
            $data['vou_ty'] = $isCredit ? 'PAY' : 'RCP';
        } else {
            $data['vou_ty'] = $finanTrn->vou_ty; // Preserve existing vou_ty
        }

        // Set fallbacks for nullable fields
        $data['vou_id'] = $data['vou_id'] ?? $finanTrn->vou_id;
        $data['vou_sr'] = $data['vou_sr'] ?? ($finanTrn->vou_sr ?? 1);
        $data['vou_ln'] = $data['vou_ln'] ?? ($finanTrn->vou_ln ?? 1);
        $data['v_mode'] = 'G'; // Hardcode v_mode to 'G'

        // Set Pr_Id and vou_rf based on provided Pr_Id or fallback to BKRBC01
        if ($request->filled('Pr_Id')) {
            $prtyMast = PrtyMast::where('Pr_Id', $request->Pr_Id)->first();
            $data['Pr_Id'] = $prtyMast ? $prtyMast->Pr_Id : null;
            $data['vou_rf'] = $prtyMast ? $prtyMast->pr_code : ($data['vou_rf'] ?? null);
        } else {
            $prtyMast = PrtyMast::where('pr_code', 'BKRBC01')->first();
            $data['Pr_Id'] = $prtyMast ? $prtyMast->Pr_Id : null;
            $data['vou_rf'] = $prtyMast ? $prtyMast->pr_code : ($data['vou_rf'] ?? null);
        }

        // Update first entry
        $finanTrn->update($data);

        // Check if a second entry exists
        $secondEntry = FinanTrn::where('vou_id', $finanTrn->vou_id)
            ->where('vou_ln', $finanTrn->vou_ln + 1)
            ->first();

        if ($secondEntry) {
            // Update second entry only if it exists
            $secondEntryData = [
                'Co_Id' => $data['Co_Id'],
                'Br_Id' => $data['Br_Id'],
                'Pr_Id' => null, // Blank for second entry
                'vou_id' => $data['vou_id'],
                'vou_sr' => $data['vou_sr'] + 1,
                'vou_ln' => $data['vou_ln'] + 1,
                'v_mode' => 'G',
                'vou_rf' => $data['vou_rf'],
                'vou_ty' => $data['vou_ty'], // Same as first entry
            ];

            // Update amounts only if cr_amt or dr_amt is provided
            if ($request->has('cr_amt') || $request->has('dr_amt')) {
                $secondEntryData['cr_amt'] = $isCredit ? null : $data['dr_amt'];
                $secondEntryData['dr_amt'] = $isCredit ? $data['cr_amt'] : null;
            } else {
                $secondEntryData['cr_amt'] = $secondEntry->cr_amt; // Preserve existing
                $secondEntryData['dr_amt'] = $secondEntry->dr_amt; // Preserve existing
            }

            $secondEntry->update($secondEntryData);
        }

        // Return both entries
        $updatedFirstEntry = FinanTrn::with(['coMaster', 'branchM', 'prtyMast'])->findOrFail($id);
        $updatedSecondEntry = FinanTrn::with(['coMaster', 'branchM', 'prtyMast'])
            ->where('vou_id', $updatedFirstEntry->vou_id)
            ->where('vou_ln', $updatedFirstEntry->vou_ln + 1)
            ->first();

        $response = [
            'vou_id' => $updatedFirstEntry->vou_id,
            'vou_dt' => $updatedFirstEntry->vou_dt,
            'entries' => [
                $updatedFirstEntry,
                $updatedSecondEntry
            ]
        ];

        return response()->json($response, 200);
    }

    public function destroy($id)
    {
        $finanTrn = FinanTrn::findOrFail($id);
        // Delete the second entry if it exists
        FinanTrn::where('vou_id', $finanTrn->vou_id)
            ->where('vou_ln', $finanTrn->vou_ln + 1)
            ->delete();
        $finanTrn->delete();
        return response()->json(null, 204);
    }

   public function getFinancialSummary(Request $request)
    {
        // Validate request parameters
        $validator = Validator::make($request->all(), [
            'Co_Id' => 'nullable|integer|exists:co_masters,Co_Id',
            'Br_Id' => 'nullable|integer|exists:branch_ms,Br_Id',
            'start_date' => 'nullable|date',
            'end_date' => 'nullable|date|after_or_equal:start_date',
            'page' => 'integer|min:1',
            'per_page' => 'integer|min:1|max:100'
        ]);

        if ($validator->fails()) {
            return response()->json(['errors' => $validator->errors()], 422);
        }

        // Set pagination parameters
        $page = $request->input('page', 1);
        $perPage = $request->input('per_page', 25);
        $offset = ($page - 1) * $perPage;

        // Build the query
        $query = DB::table(DB::raw('(
        SELECT
            RPAD(LEFT(PM.Pr_Gr, 4), 8, \'0\') AS Pr_Gr_Prefix,
            FT.Co_Id,
            CM.co_nam,
            COALESCE(FT.cr_amt, 0) AS TotalCredit,
            COALESCE(FT.dr_amt, 0) AS TotalDebit
        FROM prty_masts PM
        JOIN finan_trns FT
            ON PM.Co_Id = FT.Co_Id
            AND PM.Br_Id = FT.Br_Id
            AND PM.Pr_Id = FT.Pr_Id
        LEFT JOIN co_masters CM
            ON FT.Co_Id = CM.Co_Id
        UNION ALL
        SELECT
            PM.Pr_Gr AS Pr_Gr_Prefix,
            FT.Co_Id,
            CM.co_nam,
            COALESCE(FT.cr_amt, 0) AS TotalCredit,
            COALESCE(FT.dr_amt, 0) AS TotalDebit
        FROM prty_masts PM
        JOIN finan_trns FT
            ON PM.Co_Id = FT.Co_Id
            AND PM.Br_Id = FT.Br_Id
            AND PM.Pr_Id = FT.Pr_Id
        LEFT JOIN co_masters CM
            ON FT.Co_Id = CM.Co_Id
    ) AS CD'))
            ->select(
                'CD.Pr_Gr_Prefix',
                'LG.gr_nm AS GroupName',
                'LG.gr_stt AS GroupStatus',
                'CD.Co_Id',
                'CD.co_nam',
                DB::raw('SUM(CD.TotalCredit) AS TotalCredit'),
                DB::raw('SUM(CD.TotalDebit) AS TotalDebit')
            )
            ->leftJoin('ledg_grps AS LG', 'CD.Pr_Gr_Prefix', '=', 'LG.gr_code');

        // Apply filters
        if ($request->filled('Co_Id')) {
            $query->where('CD.Co_Id', $request->Co_Id);
        }

        if ($request->filled('Br_Id')) {
            $query->whereExists(function ($subQuery) use ($request) {
                $subQuery->select(DB::raw(1))
                    ->from('prty_masts AS PM')
                    ->join('finan_trns AS FT', function ($join) {
                        $join->on('PM.Co_Id', '=', 'FT.Co_Id')
                            ->on('PM.Br_Id', '=', 'FT.Br_Id')
                            ->on('PM.Pr_Id', '=', 'FT.Pr_Id');
                    })
                    ->where('PM.Br_Id', $request->Br_Id)
                    ->whereRaw('PM.Pr_Gr = CD.Pr_Gr_Prefix OR LEFT(PM.Pr_Gr, 4) = LEFT(CD.Pr_Gr_Prefix, 4)');
            });
        }

        if ($request->filled('start_date') && $request->filled('end_date')) {
            $query->whereExists(function ($subQuery) use ($request) {
                $subQuery->select(DB::raw(1))
                    ->from('prty_masts AS PM')
                    ->join('finan_trns AS FT', function ($join) {
                        $join->on('PM.Co_Id', '=', 'FT.Co_Id')
                            ->on('PM.Br_Id', '=', 'FT.Br_Id')
                            ->on('PM.Pr_Id', '=', 'FT.Pr_Id');
                    })
                    ->whereBetween('FT.vou_dt', [$request->start_date, $request->end_date])
                    ->whereRaw('PM.Pr_Gr = CD.Pr_Gr_Prefix OR LEFT(PM.Pr_Gr, 4) = LEFT(CD.Pr_Gr_Prefix, 4)');
            });
        }

        // Group and paginate
        $results = $query->groupBy('CD.Pr_Gr_Prefix', 'LG.gr_nm', 'LG.gr_stt', 'CD.Co_Id', 'CD.co_nam')
            ->skip($offset)
            ->take($perPage)
            ->get();

        // Get total count for pagination
        $total = $query->groupBy('CD.Pr_Gr_Prefix', 'LG.gr_nm', 'LG.gr_stt', 'CD.Co_Id', 'CD.co_nam')->count();

        // Prepare response
        $response = [
            'data' => $results,
            'pagination' => [
                'current_page' => $page,
                'per_page' => $perPage,
                'total' => $total,
                'last_page' => ceil($total / $perPage)
            ]
        ];

        return response()->json($response, 200);
    }


    public function getTransactionDetails(Request $request)
    {
        // Validation rules for query parameters
        $validator = Validator::make($request->all(), [
            'Co_Id' => 'nullable|integer|exists:co_masters,Co_Id',
            'Br_Id' => 'nullable|integer|exists:branch_ms,Br_Id',
            'start_date' => 'nullable|date',
            'end_date' => 'nullable|date|after_or_equal:start_date',
            'page' => 'integer|min:1',
            'per_page' => 'integer|min:1|max:100',
        ]);

        if ($validator->fails()) {
            return response()->json(['errors' => $validator->errors()], 422);
        }

        // Set pagination parameters
        $page = $request->input('page', 1);
        $perPage = $request->input('per_page', 25);
        $offset = ($page - 1) * $perPage;

        // Build the base query for first entries (vou_ln = 1 or lowest vou_ln per vou_id)
        $query = FinanTrn::select(
            'finan_trns.vou_id',
            'finan_trns.vou_dt as Ft_Date',
            DB::raw('COALESCE(prty_masts.pr_name, finan_trns.vou_rf, "BKRBC01") as RefPrName'),
            'finan_trns.cr_amt as CrAmt',
            'finan_trns.dr_amt as DrAmt',
            DB::raw('CONCAT(COALESCE(finan_trns.rmk1, ""), " ",
                            COALESCE(finan_trns.rmk2, ""), " ",
                            COALESCE(finan_trns.rmk3, ""), " ",
                            COALESCE(finan_trns.rmk4, ""), " ",
                            COALESCE(finan_trns.rmk5, "")) as Description')
        )
            ->leftJoin('prty_masts', function ($join) {
                $join->on('finan_trns.Co_Id', '=', 'prty_masts.Co_Id')
                    ->on('finan_trns.Br_Id', '=', 'prty_masts.Br_Id')
                    ->on('finan_trns.Pr_Id', '=', 'prty_masts.Pr_Id');
            })
            ->whereIn('finan_trns.Ft_Id', function ($subQuery) {
                $subQuery->select(DB::raw('MIN(Ft_Id)'))
                    ->from('finan_trns')
                    ->groupBy('vou_id');
            });

        // Apply filters
        if ($request->filled('Co_Id')) {
            $query->where('finan_trns.Co_Id', $request->Co_Id);
        }

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

        if ($request->filled('start_date') && $request->filled('end_date')) {
            $query->whereBetween('finan_trns.vou_dt', [$request->start_date, $request->end_date]);
        }

        // Order by vou_dt for consistent balance calculation
        $query->orderBy('finan_trns.vou_dt', 'asc')
            ->orderBy('finan_trns.vou_id', 'asc');

        // Fetch transactions
        $transactions = $query->skip($offset)->take($perPage)->get();

        // Calculate running balance for each transaction
        $results = [];
        $balance = 0; // Initialize balance
        $previousPrId = null;

        foreach ($transactions as $transaction) {
            // Reset balance if Pr_Id changes (different party)
            if ($previousPrId !== $transaction->Pr_Id) {
                $balance = 0;
                // Calculate opening balance for this Pr_Id up to vou_dt
                $openingBalance = FinanTrn::where('Pr_Id', $transaction->Pr_Id)
                    ->where('vou_dt', '<', $transaction->Ft_Date)
                    ->selectRaw('SUM(COALESCE(dr_amt, 0)) - SUM(COALESCE(cr_amt, 0)) as balance')
                    ->value('balance') ?? 0;
                $balance = $openingBalance;
                $previousPrId = $transaction->Pr_Id;
            }

            // Update balance for current transaction
            $balance += ($transaction->DrAmt ?? 0) - ($transaction->CrAmt ?? 0);

            $results[] = [
                'Ft_Date' => $transaction->Ft_Date,
                'RefPrName' => $transaction->RefPrName,
                'CrAmt' => $transaction->CrAmt,
                'DrAmt' => $transaction->DrAmt,
                'Balance' => round($balance, 2),
                'Description' => trim($transaction->Description),
            ];
        }

        // Get total count for pagination
        $totalQuery = FinanTrn::whereIn('Ft_Id', function ($subQuery) {
            $subQuery->select(DB::raw('MIN(Ft_Id)'))
                ->from('finan_trns')
                ->groupBy('vou_id');
        });

        if ($request->filled('Co_Id')) {
            $totalQuery->where('Co_Id', $request->Co_Id);
        }

        if ($request->filled('Br_Id')) {
            $totalQuery->where('Br_Id', $request->Br_Id);
        }

        if ($request->filled('start_date') && $request->filled('end_date')) {
            $totalQuery->whereBetween('vou_dt', [$request->start_date, $request->end_date]);
        }

        $total = $totalQuery->count();

        // Prepare response
        $response = [
            'data' => $results,
            'pagination' => [
                'current_page' => $page,
                'per_page' => $perPage,
                'total' => $total,
                'last_page' => ceil($total / $perPage),
            ],
        ];

        return response()->json($response, 200);
    }
}
