<?php

namespace App\Http\Controllers;

use App\Models\FinanTrn;
use App\Models\LedgerMaster;
use App\Models\InvoiceChargeMaster;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Validator;
use Carbon\Carbon;
use Exception;

class FinanTrnController extends Controller
{

public function index(Request $request)
{
    $query = FinanTrn::with([
        'organisation',
        'ledger',
        'refLedger',
        'subLedger'  
    ]);

    

    if ($request->filled('search')) {

        $search = $request->search;

        $query->where(function ($q) use ($search) {

            $q->where('Vou_Rf', 'LIKE', "%$search%")
                ->orWhere('Vou_Ty', 'LIKE', "%$search%")
                ->orWhere('Vou_Id', 'LIKE', "%$search%")
                ->orWhere('chq_no', 'LIKE', "%$search%");
        });
    }

    

    if ($request->filled('Org_Id')) {
        $query->where('Org_Id', $request->Org_Id);
    }

    if ($request->filled('Lg_Id')) {
        $query->where('Lg_Id', $request->Lg_Id);
    }

    if ($request->filled('Vou_Ty')) {
        $query->where('Vou_Ty', $request->Vou_Ty);
    }

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

    

    $perPage = $request->per_page ?? 10;

    $data = $query->orderBy('Ft_Id', 'desc')
        ->paginate($perPage);

    return response()->json([
        'success' => true,
        'data' => $data
    ]);
}

public function all(Request $request)
{
    $query = FinanTrn::with([
        'organisation',
        'ledger',
        'refLedger',
        'subLedger'
    ]);


    
    if ($request->has('Vou_Rf') && $request->Vou_Rf != '') {
        $query->where('Vou_Rf', $request->Vou_Rf);
    }


    $data = $query->orderByDesc('Ft_Id')->get();


    return response()->json([
        'success' => true,
        'data' => $data
    ]);
}





public function show($id)
{
    $data = FinanTrn::with([
        'organisation',
        'ledger.group',
        'refLedger.group',
        'agent' 
    ])->find($id);

    if (!$data) {
        return response()->json([
            'success' => false,
            'message' => 'Record not found'
        ], 404);
    }

    return response()->json([
        'success' => true,
        'data' => $data
    ]);
}

    public function findByVouRf(Request $request)
    {
        $request->validate([
            'Vou_Rf' => 'required'
        ]);

        $data = FinanTrn::where('Vou_Rf', $request->Vou_Rf)
            ->with(['organisation', 'ledger', 'agent'])
            ->first();

        if (!$data) {
            return response()->json([
                'success' => false,
                'message' => 'Voucher not found'
            ]);
        }

        return response()->json([
            'success' => true,
            'data' => $data
        ]);
    }


    public function store(Request $request)
    {
        DB::beginTransaction();

        try {

            $validator = Validator::make($request->all(), [

                'Org_Id' => 'required|exists:org_master,Org_Id',
                'Lg_Id' => 'required|exists:ledger_master,Lg_Id',
                'Vou_Dt' => 'required|date',

                'Cr_Amt' => 'nullable|numeric',
                'Dr_Amt' => 'nullable|numeric',

            ]);

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

            $data = $request->all();

            $data['Vou_Id'] = $data['Vou_Id'] ?? (FinanTrn::max('Vou_Id') + 1 ?? 1);
            $data['Vou_Sr'] = $data['Vou_Sr'] ?? 1;
            $data['Vou_Ln'] = $data['Vou_Ln'] ?? 1;
            $data['V_Mode'] = $data['V_Mode'] ?? 'G';

            $first = FinanTrn::create($data);

            DB::commit();

            return response()->json([
                'success' => true,
                'data' => $first
            ], 201);

        } catch (Exception $e) {

            DB::rollBack();

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

    public function update(Request $request, $id)
    {
        DB::beginTransaction();

        try {

            $data = FinanTrn::findOrFail($id);

            $data->update($request->all());

            DB::commit();

            return response()->json([
                'success' => true,
                'data' => $data
            ]);

        } catch (Exception $e) {

            DB::rollBack();

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

    public function destroy($id)
    {
        $data = FinanTrn::findOrFail($id);

        FinanTrn::where('Vou_Id', $data->Vou_Id)->delete();

        return response()->json([
            'success' => true,
            'message' => 'Deleted successfully'
        ]);
    }


    public function getReport(Request $request)
    {
        $start = $request->start_date ?? Carbon::now()->subMonth();
        $end = $request->end_date ?? Carbon::now();

        $results = DB::table('finan_trns')
            ->select(
                'Lg_Id',
                DB::raw('SUM(Dr_Amt) as debit'),
                DB::raw('SUM(Cr_Amt) as credit')
            )
            ->whereBetween('Vou_Dt', [$start, $end])
            ->groupBy('Lg_Id')
            ->get();

        return response()->json([
            'success' => true,
            'data' => $results
        ]);
    }


 public function getFinancialSummary()
    {


       $data = DB::table('finan_trns as ft')
        ->join('ledger_master as lm', 'ft.Lg_Id', '=', 'lm.Lg_Id')
        ->join('ledg_grps as lg', 'lm.Gr_Id', '=', 'lg.Gr_Id') 
        ->select(
            'lg.Gr_Cd as GroupCode',
            'lg.Gr_Nm as GroupName',
            'lg.Gr_St as GroupStatus',
            'ft.Org_Id',
            DB::raw('COALESCE(SUM(ft.Cr_Amt),0) as TotalCredit'),
            DB::raw('COALESCE(SUM(ft.Dr_Amt),0) as TotalDebit')
        )
        ->groupBy(
            'lg.Gr_Cd',
            'lg.Gr_Nm',
            'lg.Gr_St',
            'ft.Org_Id'
        )
        ->get();

    return response()->json([
        'success' => true,
        'data' => $data
    ]);
    }

    public function getTransactionDetails(Request $request)
    {
        $perPage = $request->per_page ?? 20;

        $data = FinanTrn::paginate($perPage);

        return response()->json([
            'success' => true,
            'data' => $data
        ]);
    }

public function voucherWiseList(Request $request)
{
    try {

        $query = FinanTrn::with([
            'organisation',
            'ledger',
            'refLedger',
            'subLedger'
        ]);

        if ($request->filled('Vou_Ty')) {
            $query->where('Vou_Ty', $request->Vou_Ty);
        }

        if ($request->filled('Vou_Id')) {
            $query->where('Vou_Id', $request->Vou_Id);
        }

        if ($request->filled('V_Mode')) {
            $query->where('V_Mode', $request->V_Mode);
        }

        $records = $query
            ->orderBy('Vou_Id')
            ->get()
            ->groupBy('Vou_Id');

        $data = [];

        foreach ($records as $Vou_Id => $rows) {

            $first = $rows->first(); 

            
            $sumCr = $rows->sum(function ($row) {
                return (float) ($row->Cr_Amt ?? 0);
            });

            $sumDr = $rows->sum(function ($row) {
                return (float) ($row->Dr_Amt ?? 0);
            });

            $data[] = [
                'Vou_Id'   => $Vou_Id,
                'Vou_Ty'   => $first->Vou_Ty,
                'Vou_Dt'   => $first->Vou_Dt,
                'Vou_Ln'   => $first->Vou_Ln ?? null,

                'sum_cr'   => $sumCr,
                'sum_dr'   => $sumDr,

                'voucher_rows' => $rows
            ];
        }

        return response()->json([
            'success' => true,
            'count'   => count($data),
            'data'    => $data
        ], 200);

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

        public function deleteByVoucher(Request $request)
{
    $validator = Validator::make($request->all(), [
        'Vou_Id' => 'required|integer',
        'Vou_Ty' => 'required|string',
    ]);

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

    try {
        DB::beginTransaction();

        $Vou_Id = $request->Vou_Id;
        $Vou_Ty = $request->Vou_Ty;

        $deletedCount = FinanTrn::where('Vou_Id', $Vou_Id)
            ->where('Vou_Ty', $Vou_Ty)
            ->delete();

        DB::commit();

        return response()->json([
            'success' => true,
            'message' => "Voucher deleted successfully",
            'Vou_Id' => $Vou_Id,
            'Vou_Ty' => $Vou_Ty,
            'deleted_records' => $deletedCount
        ], 200);

    } catch (\Exception $e) {
        DB::rollBack();

        return response()->json([
            'success' => false,
            'message' => 'Failed to delete voucher',
            'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage()
        ], 500);
    }
}


public function compareWithInvoiceCharges(Request $request)
{
    try {

        

        $request->validate([

            'Lg_Id' => 'required|integer',

            'SL_Id' => 'nullable|integer',

            'start_date' => 'nullable|date',

            'end_date' => 'nullable|date'
        ]);



        $query = FinanTrn::with(['subLedger', 'invoiceCharge'])

            ->where('V_Mode', 'S')

            ->whereHas('invoiceCharge')

            ->where('Lg_Id', $request->Lg_Id)

            ->whereNull('Ref_Vou_Id');


        if ($request->filled('SL_Id')) {

            $query->where('SL_Id', $request->SL_Id);
        }


        if (
            $request->filled('start_date') &&
            $request->filled('end_date')
        ) {

            $query->whereBetween('Vou_Dt', [

                $request->start_date,

                $request->end_date
            ]);
        }

        $transactions = $query->orderBy('Ft_Id', 'asc')->get();

        if ($transactions->isEmpty()) {

            return response()->json([

                'success' => true,

                'message' => 'No unpaid records found for this Lg_Id',

                'data' => []

            ], 200);
        }


        $chargeInfo = $transactions->first()->invoiceCharge;

        $percentage = floatval($chargeInfo->Percentage ?? 0);


        $records = [];

        $totalCr = 0;

        $totalDr = 0;

        foreach ($transactions as $transaction) {


            $subLedgerName = 'Unknown';

            if (
                $transaction->subLedger &&
                $transaction->subLedger->SL_Name
            ) {

                $subLedgerName = $transaction->subLedger->SL_Name;
            }


            $creditAmount = floatval($transaction->Cr_Amt ?? 0);

            $debitAmount = floatval($transaction->Dr_Amt ?? 0);

            $difference = $creditAmount - $debitAmount;


            $records[] = [

                'Ft_Id' => $transaction->Ft_Id,

                'subledger_id' => $transaction->SL_Id,

                'subledger_name' => $subLedgerName,

                'credit_amount' => $creditAmount,

                'debit_amount' => $debitAmount,

                'difference' => $difference,

                'Ref_Vou_Id' => $transaction->Ref_Vou_Id
            ];

            $totalCr += $creditAmount;

            $totalDr += $debitAmount;
        }


        $totalDifference = $totalCr - $totalDr;


        return response()->json([

            'success' => true,

            'data' => [

                'ledger_summary' => [

                    'Lg_Id' => $request->Lg_Id,

                    'Ledger_Name' =>
                        $chargeInfo
                        ? $chargeInfo->IC_Name
                        : 'N/A',

                    'Percentage' => $percentage . '%',

                    'total_records' => count($records),

                    'total_credit' => $totalCr,

                    'total_debit' => $totalDr,

                    'total_difference' => $totalDifference
                ],

                'records' => $records
            ]

        ], 200);

    } catch (\Exception $e) {

        return response()->json([

            'success' => false,

            'message' => 'Failed to compare data',

            'error' => env('Development_Mode') == 'Prod' 
        ? 'Something went wrong. Please try again later.' 
        : $e->getMessage()

        ], 500);
    }
}


public function paySubledgers(Request $request)
{
    DB::beginTransaction();

    try {

        $request->validate([

            'subledgers' => 'required|array|min:1',

            'subledgers.*.Ft_Id' => 'required|integer',
        ]);

        $refVouId = (FinanTrn::max('Ref_Vou_Id') ?? 0) + 1;

        $updatedRecords = [];

        foreach ($request->subledgers as $item) {

            $ftId = $item['Ft_Id'];


            $updated = FinanTrn::where('Ft_Id', $ftId)

                ->whereNull('Ref_Vou_Id')

                ->update([

                    'Ref_Vou_Id' => $refVouId
                ]);

            $updatedRecords[] = [

                'Ft_Id' => $ftId,

                'updated_rows' => $updated
            ];
        }

        DB::commit();

        return response()->json([

            'success' => true,

            'message' => 'Payment completed successfully',

            'Ref_Vou_Id' => $refVouId,

            'updated_subledgers' => $updatedRecords

        ], 200);

    } catch (\Exception $e) {

        DB::rollBack();

        return response()->json([

            'success' => false,

            'message' => 'Something went wrong. Please try again later.'

        ], 500);
    }
}

}


