<?php

namespace App\Http\Controllers;

use App\Http\Controllers\Controller;
use App\Models\PayDetail;
use App\Models\PayVoucher;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Validator;
use Illuminate\Support\Facades\DB;
use App\Models\TenderAdjustment;

class PayVoucherController extends Controller
{
    public function show($id)
    {
        $data = PayVoucher::with([
            'department',
            'ledger',
            'subLedger',
            'workAllocation',
            'payDetails.tenderType',
            'payDetails.vendorParameter',
            'payDetails.invoiceCharge',
            'payDetails.ledger',
            'workAllocation.tender',
            'workAllocation.tender.tendertype',
            // 'action',
            'hodOfficer',
            'acctOfficer'
        ])->find($id);


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


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


    public function index(Request $request)
    {
        $query = PayVoucher::with([
            'department',
            'ledger',
            'subLedger',
            'workAllocation',
            'payDetails.tenderType',
            'payDetails.vendorParameter',
            'payDetails.invoiceCharge',
            'payDetails.ledger',
            'payDetails.challanType',
            'payDetails.ChlnvParameter',

            'workAllocation.tender',
            'workAllocation.tender.tendertype',
            // 'action',
            'hodOfficer',
            'acctOfficer'
        ]);


        // ✅ Search
        if ($request->filled('search')) {
            $search = $request->search;


            $query->where(function ($q) use ($search) {
                $q->where('PV_No', 'like', "%{$search}%")
                    ->orWhere('PV_Ty', 'like', "%{$search}%");
            });
        }

   // ✅ PV_Ty Filter (NEW)
    if ($request->filled('PV_Ty')) {
        $query->where('PV_Ty', $request->PV_Ty);
    }


        // ✅ Status Filter
        if ($request->filled('Status')) {
            $query->where('Status', $request->Status);
        }


        // ✅ Date Filter (optional — useful)
        if ($request->filled('from_date') && $request->filled('to_date')) {
            $query->whereBetween('PV_Chln_Date', [
                $request->from_date,
                $request->to_date
            ]);
        }


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


        $data = $query->latest()->paginate($perPage);


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




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

        try {

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

                // ================= PAY VOUCHER =================
                'PV_Ty' => 'nullable|in:VEND,GRNT,D_Bill,D_Chl',
                'PV_Chln_Date' => 'nullable|date',

                'Dpt_Id' => 'nullable|exists:dept_master,Dpt_id',
                'Lg_Id' => 'nullable|exists:ledger_master,Lg_Id',
                'SL_Id' => 'nullable|exists:subledg_master,SL_Id',
                'WA_Id' => 'nullable|exists:work_allocation_book,WA_Id',

                'Gross_Amt' => 'nullable|numeric',
                'Add_Amt' => 'nullable|numeric',
                'Less_Amt' => 'nullable|numeric',
                'Net_Amt' => 'nullable|numeric',

                // ================= PAY DETAILS =================
                'pay_details' => 'nullable|array',

                'pay_details.*.PD_Ty' => 'nullable|in:TAX,PENLTY',
                'pay_details.*.TnT_ID' => 'nullable|exists:tender_type,TnT_ID',
                'pay_details.*.Cht_Id' => 'nullable|exists:challan_type,Cht_Id',
                'pay_details.*.CIP_Id' => 'nullable|exists:chlnv_parameter,CIP_Id',
                'pay_details.*.PVD_Sr' => 'nullable|integer',
                'pay_details.*.VIP_Id' => 'nullable|exists:vendinv_parameter,VIP_Id',
                'pay_details.*.IC_Id' => 'nullable|exists:invoice_charge_master,IC_Id',
                'pay_details.*.Lg_Id' => 'nullable|exists:ledger_master,Lg_Id',
                'pay_details.*.Percent' => 'nullable|numeric',
                'pay_details.*.Formula' => 'nullable|string',
                'pay_details.*.IC_Amt' => 'nullable|numeric',
                'pay_details.*.Add_Dedu' => 'nullable|numeric',
            ]);

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

            // ================= CREATE PAY VOUCHER =================
            $voucher = PayVoucher::create([
                'PV_Ty' => $request->PV_Ty,
                'PV_No' => $request->PV_No,
                'PV_Chln_Date' => $request->PV_Chln_Date,
                'Dpt_Id' => $request->Dpt_Id,
                'Lg_Id' => $request->Lg_Id,
                'SL_Id' => $request->SL_Id,
                'WA_Id' => $request->WA_Id,
                'Gross_Amt' => $request->Gross_Amt,
                'Add_Amt' => $request->Add_Amt,
                'Less_Amt' => $request->Less_Amt,
                'Net_Amt' => $request->Net_Amt,

                // ✅ ADD THIS
                'Status' => 'draft',

                'Remarks' => $request->Remarks
            ]);

            // ================= CREATE PAY DETAILS =================
            foreach ($request->pay_details as $detail) {

                PayDetail::create([
                    'PD_Ty' => $detail['PD_Ty'],
                    'PV_Id' => $voucher->PV_Id, // auto set
                    'TnT_ID' => $detail['TnT_ID'] ?? null,
                    'PVD_Sr' => $detail['PVD_Sr'],
                    'VIP_Id' => $detail['VIP_Id'] ?? null,
                    'IC_Id' => $detail['IC_Id'],
                    'Cht_Id' => $detail['Cht_Id'] ?? null,
                    'CIP_Id' => $detail['CIP_Id'] ?? null,
                    'Lg_Id' => $detail['Lg_Id'],
                    'Percent' => $detail['Percent'] ?? null,
                    'Formula' => $detail['Formula'] ?? null,
                    'IC_Amt' => $detail['IC_Amt'],
                    'Add_Dedu' => $detail['Add_Dedu'],
                ]);
            }

            DB::commit();

            return response()->json([
                'success' => true,
                'message' => 'Voucher Created Successfully',
                'data' => PayVoucher::with([
                    // 'department',
                    // 'ledger',
                    // 'subLedger',
                    // 'workAllocation',
                    // 'payDetails.tenderType',
                    // 'payDetails.vendorParameter',
                    // 'payDetails.invoiceCharge',
                    // 'payDetails.ledger'
                ])->find($voucher->PV_Id)
            ]);
        } 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 {

            $voucher = PayVoucher::find($id);

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

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

                // ================= PAY VOUCHER =================
                'PV_Ty' => 'required|in:VEND,GRNT,D_Bill,D_Chl',
                'PV_Chln_Date' => 'required|date',

                'Dpt_Id' => 'nullable|exists:dept_master,Dpt_id',
                'Lg_Id' => 'nullable|exists:ledger_master,Lg_Id',
                'SL_Id' => 'nullable|exists:subledg_master,SL_Id',
                'WA_Id' => 'nullable|exists:work_allocation_book,WA_Id',
                // 'Status' => 'nullable|exists:action_master,Act_Id',

                'Gross_Amt' => 'nullable|numeric',
                'Add_Amt' => 'nullable|numeric',
                'Less_Amt' => 'nullable|numeric',
                'Net_Amt' => 'nullable|numeric',

                // ================= PAY DETAILS =================
                'pay_details' => 'nullable|array',

                'pay_details.*.PD_Ty' => 'nullable|in:TAX,PENLTY',
                'pay_details.*.TnT_ID' => 'nullable|exists:tender_type,TnT_ID',
                'pay_details.*.Cht_Id' => 'nullable|exists:challan_type,Cht_Id',
                'pay_details.*.CIP_Id' => 'nullable|exists:chlnv_parameter,CIP_Id',
                'pay_details.*.PVD_Sr' => 'nullable|integer',
                'pay_details.*.VIP_Id' => 'nullable|exists:vendinv_parameter,VIP_Id',
                'pay_details.*.IC_Id' => 'nullable|exists:invoice_charge_master,IC_Id',
                'pay_details.*.Lg_Id' => 'nullable|exists:ledger_master,Lg_Id',
                'pay_details.*.Percent' => 'nullable|numeric',
                'pay_details.*.Formula' => 'nullable|string',
                'pay_details.*.IC_Amt' => 'nullable|numeric',
                'pay_details.*.Add_Dedu' => 'nullable|numeric',
            ]);

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

            // ================= UPDATE VOUCHER =================
            $voucher->update($request->except('pay_details'));

            // ================= DELETE OLD DETAILS =================
            PayDetail::where('PV_Id', $voucher->PV_Id)->delete();

            // ================= INSERT NEW DETAILS =================
            foreach ($request->pay_details as $detail) {

                PayDetail::create([
                    'PD_Ty' => $detail['PD_Ty'],
                    'PV_Id' => $voucher->PV_Id,
                    'TnT_ID' => $detail['TnT_ID'] ?? null,
                    'PVD_Sr' => $detail['PVD_Sr'],
                    'VIP_Id' => $detail['VIP_Id'] ?? null,
                    'IC_Id' => $detail['IC_Id'],
                    'Cht_Id' => $detail['Cht_Id'] ?? null,
                    'CIP_Id' => $detail['CIP_Id'] ?? null,
                    'Lg_Id' => $detail['Lg_Id'],
                    'Percent' => $detail['Percent'] ?? null,
                    'Formula' => $detail['Formula'] ?? null,
                    'IC_Amt' => $detail['IC_Amt'],
                    'Add_Dedu' => $detail['Add_Dedu'],
                ]);
            }

            DB::commit();

            return response()->json([
                'success' => true,
                'message' => 'Voucher Updated Successfully',
                'data' => PayVoucher::with([
                    // 'department',
                    // 'ledger',
                    // 'subLedger',
                    // 'workAllocation',
                    // 'payDetails.tenderType',
                    // 'payDetails.vendorParameter',
                    // 'payDetails.invoiceCharge',
                    // 'payDetails.ledger'
                ])->find($voucher->PV_Id)
            ]);
        } catch (\Exception $e) {

            DB::rollBack();

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



    public function destroy($id)
    {
        DB::beginTransaction();


        try {


            $voucher = PayVoucher::find($id);


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


            $pvNo = $voucher->PV_No;


            // ✅ Make Bill_No NULL where matching PV_No
            TenderAdjustment::where('Bill_No', $pvNo)
                ->update([
                    'Bill_No' => null
                ]);


            // ✅ Delete Voucher
            $voucher->delete();

            DB::commit();

            return response()->json([
                'success' => true,
                'message' => 'Voucher Deleted & TenderAdjustment Updated'
            ]);
        } catch (\Exception $e) {


            DB::rollBack();


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


    public function updateStatus(Request $request, $id)
{
    $request->validate([
        'Status' => 'nullable|in:draft,pending,approved,paid,cancelled,quary,hod_app,hod_query,acco_app,acco_query',
        'HOD_Officer' => 'nullable|exists:user_master,Usr_Id',
        'Acct_Officer' => 'nullable|exists:user_master,Usr_Id',
        'Remarks' => 'nullable|string|max:500',
        'Acc_Vou_No' => 'nullable|string|max:10'
    ]);

    $voucher = PayVoucher::findOrFail($id);

    // ✅ Status Update (FIX)
    if ($request->filled('Status')) {
        $voucher->Status = $request->Status;
    }

    // ✅ HOD Officer Update
    if ($request->filled('HOD_Officer')) {
        $voucher->HOD_Officer = $request->HOD_Officer;
        $voucher->HOD_Approved = now();
    }

    // ✅ Account Officer Update
    if ($request->filled('Acct_Officer')) {
        $voucher->Acct_Officer = $request->Acct_Officer;
        $voucher->Acct_Approved = now();
    }

    // ✅ Remarks
    if ($request->filled('Remarks')) {
        $voucher->Remarks = $request->Remarks;
    }


    if ($request->filled('Acc_Vou_No')) {
        $voucher->Acc_Vou_No = $request->Acc_Vou_No;
    }

    $voucher->save();

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

    public function showByPvNo($pvNo)
    {
        try {

            // ✅ Decode full URL
            $pvNo = urldecode($pvNo);

            // ✅ Clean string
            $pvNo = trim($pvNo);

            // ✅ Normalize spaces
            $pvNo = preg_replace('/\s+/', ' ', $pvNo);

            // ✅ Fetch Data (strong match)
            $data = PayVoucher::with([
                'department',
                'ledger',
                'subLedger',
                'workAllocation',
                'payDetails' => function ($q) {
                    $q->whereNull('deleted_at');
                },
                'payDetails.tenderType',
                'payDetails.vendorParameter',
                'payDetails.invoiceCharge',
                'payDetails.ledger',
                'workAllocation.tender',
                'workAllocation.tender.tendertype',
                // 'action',
                'hodOfficer',
                'acctOfficer'
            ])
            ->whereNull('deleted_at')
            ->whereRaw('LOWER(TRIM(PV_No)) = LOWER(TRIM(?))', [$pvNo])
            ->first();

            // 🔥 fallback (agar exact match fail ho)
            if (!$data) {
                $data = PayVoucher::whereNull('deleted_at')->where('PV_No', 'like', "%{$pvNo}%")->first();
            }

            // ❌ Not Found
            if (!$data) {
                return response()->json([
                    'success' => false,
                    'message' => 'Voucher not found',
                    'received' => $pvNo
                ], 404);
            }

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

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


    public function getReport(Request $request)
{
    try {

        $where = " WHERE 1=1 ";

        // ===============================
        // FILTERS
        // ===============================
        if ($request->filled('from_date') && $request->filled('to_date')) {
            $where .= " AND cv.Ch_Date BETWEEN '{$request->from_date}' AND '{$request->to_date}' ";
        }

        if ($request->filled('dept_name')) {
            $where .= " AND dm.Dpt_Name LIKE '%{$request->dept_name}%' ";
        }

        if ($request->filled('pay_mode')) {
            $where .= " AND cv.Py_Mode LIKE '%{$request->pay_mode}%' ";
        }

        if ($request->filled('status')) {
            $where .= " AND COALESCE(am1.St_Name,'') LIKE '%{$request->status}%' ";
        }

        // =====================================================
        // IMPORTANT FIX:
        // SGST/CGST/TDS chl_voucher me nahi,
        // pay_details table me stored hai
        // =====================================================

        $sql = "

        SELECT
            dm.Dpt_Name AS Dept_Name,

            CONCAT(
                '[',
                GROUP_CONCAT(
                    JSON_OBJECT(
                        'CV_No', lg.CV_No,
                        'Acc_Chl_No', lg.Acc_Chl_No,
                        'Lg_Code', lg.Lg_Code,
                        'Lg_Name', lg.Lg_Name,
                        'Pay_Mode_Details', lg.Pay_Mode_Details,

                        'Deduction_Heads',
                        JSON_OBJECT(
                            'SGST', lg.SGST,
                            'CGST', lg.CGST,
                            'TDS', lg.TDS
                        ),

                        'Total_Income', lg.Total_Income,
                        'Total_Expense', lg.Total_Expense,
                        'Difference', lg.Difference
                    )
                    SEPARATOR ','
                ),
                ']'
            ) AS Ledger_Details,

            SUM(lg.Total_Income) AS Dept_Total_Income,
            SUM(lg.Total_Expense) AS Dept_Total_Expense,
            SUM(lg.Difference) AS Dept_Difference

        FROM (

            SELECT
                pm.Dpt_Id,
                pm.CV_No,
                pm.Acc_Chl_No,
                lm.Lg_Code,
                lm.Lg_Name,

                CONCAT(
                    '[',
                    GROUP_CONCAT(
                        JSON_OBJECT(
                            'mode', pm.Py_Mode,
                            'income', pm.income,
                            'expense', pm.expense,
                            'difference', (pm.income - pm.expense)
                        )
                        SEPARATOR ','
                    ),
                    ']'
                ) AS Pay_Mode_Details,

                /* ===========================
                   PAY DETAILS FROM PAY VOUCHER
                ============================ */

                (
                    SELECT IFNULL(SUM(pvd.IC_Amt),0)
                    FROM pay_vouchers pv
                    LEFT JOIN pay_details pvd
                        ON pv.PV_Id = pvd.PV_Id
                    LEFT JOIN invoice_charge ic
                        ON pvd.IC_Id = ic.IC_Id
                    WHERE pv.PV_No = pm.CV_No
                    AND ic.IC_Name LIKE '%SGST%'
                ) AS SGST,

                (
                    SELECT IFNULL(SUM(pvd.IC_Amt),0)
                    FROM pay_vouchers pv
                    LEFT JOIN pay_details pvd
                        ON pv.PV_Id = pvd.PV_Id
                    LEFT JOIN invoice_charge ic
                        ON pvd.IC_Id = ic.IC_Id
                    WHERE pv.PV_No = pm.CV_No
                    AND ic.IC_Name LIKE '%CGST%'
                ) AS CGST,

                (
                    SELECT IFNULL(SUM(pvd.IC_Amt),0)
                    FROM pay_vouchers pv
                    LEFT JOIN pay_details pvd
                        ON pv.PV_Id = pvd.PV_Id
                    LEFT JOIN invoice_charge ic
                        ON pvd.IC_Id = ic.IC_Id
                    WHERE pv.PV_No = pm.CV_No
                    AND ic.IC_Name LIKE '%TDS%'
                ) AS TDS,

                SUM(pm.income) AS Total_Income,
                SUM(pm.expense) AS Total_Expense,
                SUM(pm.income - pm.expense) AS Difference

            FROM (

                SELECT
                    cv.Dpt_Id,
                    cv.CV_No,
                    cv.Acc_Chl_No,
                    cv.Lg_Id,
                    cv.Py_Mode,

                    SUM(
                        CASE
                            WHEN cv.Is_income='Y'
                            THEN cv.Amount
                            ELSE 0
                        END
                    ) AS income,

                    SUM(
                        CASE
                            WHEN cv.Is_income='N'
                            THEN cv.Amount
                            ELSE 0
                        END
                    ) AS expense

                FROM chl_voucher cv
                LEFT JOIN dept_master dm
                    ON cv.Dpt_Id = dm.Dpt_id
                LEFT JOIN action_master am1
                    ON cv.Status = am1.Act_Id
                LEFT JOIN ledger_master lm
                    ON cv.Lg_Id = lm.Lg_Id

                $where

                GROUP BY
                    cv.Dpt_Id,
                    cv.CV_No,
                    cv.Acc_Chl_No,
                    cv.Lg_Id,
                    cv.Py_Mode

            ) pm

            LEFT JOIN ledger_master lm
                ON pm.Lg_Id = lm.Lg_Id

            GROUP BY
                pm.Dpt_Id,
                pm.CV_No,
                pm.Acc_Chl_No,
                lm.Lg_Id,
                lm.Lg_Code,
                lm.Lg_Name

        ) lg

        LEFT JOIN dept_master dm
            ON lg.Dpt_Id = dm.Dpt_id

        GROUP BY dm.Dpt_Name
        ORDER BY dm.Dpt_Name
        ";

        $data = DB::select($sql);

        foreach ($data as $item) {

            $item->Ledger_Details =
                json_decode($item->Ledger_Details, true) ?? [];

            foreach ($item->Ledger_Details as &$row) {
                $row['Pay_Mode_Details'] =
                    json_decode($row['Pay_Mode_Details'], true) ?? [];
            }
        }

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

    } catch (\Exception $e) {

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


public function counters(Request $request)
{
    $request->validate([
        'dept_id' => 'nullable|integer',
    ]);

    $deptId = $request->input('dept_id');

    $query = PayVoucher::where('PV_Ty', 'VEND')
        ->whereNull('deleted_at');

    if ($deptId) {
        $query->where('Dpt_Id', $deptId);
    }

    $lastNo     = $query->count();
    $nextNumber = $lastNo + 1;

    return response()->json([
        'success'     => true,
        'next_number' => $nextNumber,
        'dept_id'     => $deptId ? (int) $deptId : null,
    ]);
}

public function generateAccVouNo(Request $request)
{
    $request->validate([
        'dept_id' => 'nullable|integer',
    ]);

    $deptId = $request->input('dept_id');

    $query = PayVoucher::where('PV_Ty', 'VEND')
        ->whereNull('deleted_at')
        ->where('Status', 'paid');

    if ($deptId) {
        $query->where('Dpt_Id', $deptId);
    }

    $lastNo     = $query->count();
    $nextNumber = $lastNo + 1;

    return response()->json([
        'success'     => true,
        'acc_vou_no'  => $nextNumber,
        'dept_id'     => $deptId ? (int) $deptId : null,
    ]);
}


}
