<?php

namespace App\Http\Controllers;

use App\BCheque;
use App\Brands;
use App\GoodReceiptDetail;
use App\GoodReceiptTruck;
use App\ProductsCategoryMain;
use App\ProductsSubCategoryChild;
use App\ProductsSubCategoryParent;
use App\Sale;
use App\SalesItemDetail;
use App\Suppliers;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use PhpOffice\PhpSpreadsheet\Calculation\MathTrig\Trunc;

class ReportController extends Controller
{
    public function saleReportDetail(Request $request)
    {
        $fromdate = date('d-m-Y');
        $todate = date('d-m-Y', strtotime('+30 days'));
        if ($request->fromdate && $request->todate) {
            $fromdate = $request->fromdate;
            $todate = $request->todate;
            $categoryid = $request->category_id;
            $brand_id = $request->brand_id;
            $product_id = $request->product_id;

            $returnNote = DB::table('sale_return_notes')
                ->select('gdn_id', 'product_id', DB::raw('IFNULL(SUM(return_qty),0) AS returned'), 'batch_id')
                ->groupBy('product_id')
                ->groupBy('gdn_id');

            $sql = [
                DB::raw('UPPER(employees.employee_name) as employee_name'),
                'products.product_name',
                'products.sku',
                'gdn_details.gdn_qty_delivered as qty',
                DB::raw('(IFNULL(returnNote.returned,0)) as returned_qty'),
                'gdn_details.gdn_unit_discount',
                'gdn_details.gdn_discount_percent',
                'gdn_details.gdn_unit_price',
                DB::raw('UPPER(goods_delivery_note.gdn_customer_name) as customer_name'),
                DB::raw('DATE_FORMAT(goods_delivery_note.gdn_created_date, "%d-%M-%Y") as transdate'),
                'goods_delivery_note.gdn_number',
                'sale_orders.sale_order_number',
                'producttypes.type_name as category_name',
                'sale_invoice.extra_discount_amount',
            ];
            $saleReport = gdn_details::select($sql)
                ->join('goods_delivery_note', 'goods_delivery_note.id_gdn', '=', 'gdn_details.gdn_id')
                ->join('sale_invoice', 'sale_invoice.gdn_id', '=', 'goods_delivery_note.id_gdn')
                ->join('sale_orders', 'goods_delivery_note.sale_order_id', '=', 'sale_orders.id_sale_orders')
                ->join('products', 'gdn_details.gdn_product_id', '=', 'products.id_products')
                //->join('employee_customers', 'goods_delivery_note.customer_id', '=', 'employee_customers.customer_id')
                ->join('employees', 'employees.id_employee', '=', 'sale_orders.saleman_id')
                ->join('producttypes', 'producttypes.type_id', '=', 'products.category_id')
                ->leftjoinSub($returnNote, 'returnNote', function ($join) {
                    $join->on('gdn_details.gdn_id', '=', 'returnNote.gdn_id');
                    $join->on('gdn_details.gdn_product_id', '=', 'returnNote.product_id');
                })
                ->where('goods_delivery_note.gdn_created_date', '>=', date('Y-m-d', strtotime($fromdate)))
                ->where('goods_delivery_note.gdn_created_date', '<=', date('Y-m-d', strtotime($todate)) . ' 23:59:59')
                ->where('products.brand_id', '=', $brand_id)
                ->where('sale_orders.status', '!=', 'Cancelled')
                ->where('sale_orders.business_id', '=', auth()->user()->business_id);
            if ($categoryid != "all") {
                $saleReport = $saleReport->where('products.category_id', '=', $categoryid);
            }
            if ($product_id != "all") {
                $saleReport = $saleReport->where('products.id_products', '=', $product_id);
            }
            $saleReport = $saleReport->orderBy('goods_delivery_note.gdn_customer_name')
                ->orderBy('sale_orders.id_sale_orders')
                ->orderBy('goods_delivery_note.gdn_created_date')
                //->groupBy('goods_delivery_note.customer_id', 'gdn_details.gdn_product_id')
                ->get()
                ->toArray();
            return $saleReport;
            exit;
        }
        $business = DB::table('business')->where('id_business', auth()->user()->business_id)->first();
        $brands = Brands::all()->where('status', '=', 'Active');
        return view('reports.saleReportDetail', compact('business', 'fromdate', 'todate', 'brands'));
    }

    public function categoryBrand(Request $request)
    {
        $brand_id = $request->brand_id;
        $categories = producttypes::select('producttypes.type_id', 'producttypes.type_name')
            ->distinct()
            ->join('products', 'producttypes.type_id', '=', 'products.category_id')
            ->where('brand_id', '=', $brand_id)
            ->orderBy('producttypes.type_id', 'DESC')
            ->get()->toArray();
        return $categories;
    }

    // public function showStockReportForm()
    // {
    //     $suppliers = Suppliers::all();
    //     return view('reports.stock_report', compact('suppliers'));
    // }

    // public function getTrucks(Request $request)
    // {
    //     $supplierId = $request->input('supplier_id');

    //     $trucks = DB::table('good_receipts')
    //         ->join('good_receipts_truck', 'good_receipts_truck.id_truck', '=', 'good_receipts.good_receipts_truck_id') // Fix table name
    //         ->where('good_receipts.supplier_id', $supplierId)
    //         ->select('good_receipts_truck.id_truck', 'good_receipts_truck.truck_no')
    //         ->get();


    //     return response()->json($trucks);
    // }
    // public function getMainCategories(Request $request)
    // {
    //     $supplierId = $request->input('supplier_id');

    //     $mainCategories = GoodReceiptDetail::whereHas('goodReceipt.goodReceiptTruck', function ($query) use ($supplierId) {
    //         $query->where('supplier_id', $supplierId);
    //     })
    //         ->with('productMainCate')
    //         ->get()
    //         ->pluck('productMainCate')
    //         ->unique('id_pro_cate_main');

    //     return response()->json($mainCategories);
    // }

    // public function getParentCategories(Request $request)
    // {
    //     $mainCategoryId = $request->input('main_category_id');
    //     $supplierId = $request->input('supplier_id');

    //     $parentCategories = GoodReceiptDetail::whereHas('goodReceipt.goodReceiptTruck', function ($query) use ($supplierId) {
    //         $query->where('supplier_id', $supplierId);
    //     })
    //         ->where('pro_cate_main_id', $mainCategoryId)
    //         ->with('productParentCate')
    //         ->get()
    //         ->pluck('productParentCate')
    //         ->unique('id_pro_sub_cat_parent');

    //     return response()->json($parentCategories);
    // }

    // public function getChildCategories(Request $request)
    // {
    //     $parentCategoryId = $request->input('parent_category_id');
    //     $supplierId = $request->input('supplier_id');

    //     $childCategories = GoodReceiptDetail::whereHas('goodReceipt.goodReceiptTruck', function ($query) use ($supplierId) {
    //         $query->where('supplier_id', $supplierId);
    //     })
    //         ->where('pro_cate_parent_id', $parentCategoryId)
    //         ->with('productChildCate')
    //         ->get()
    //         ->pluck('productChildCate')
    //         ->unique('id_pro_sub_cat_child');

    //     return response()->json($childCategories);
    // }

    // public function generateStockReport(Request $request)
    // {
    //     $validated = $request->validate([
    //         'main_category' => 'required',
    //         'parent_category' => 'required',
    //         'child_category' => 'required',
    //     ]);

    //     $stockData = GoodReceiptDetail::select(
    //         'good_receipts_truck.truck_no',
    //         'good_receipts_details.bori',
    //         'products_category_main.products_category_name as main_category_name',
    //         'products_sub_cat_parent.name as parent_category_name',
    //         'products_sub_cat_child.name as child_category_name'
    //     )
    //         ->join('good_receipts_truck', 'good_receipts_truck.id_truck', '=', 'good_receipts_details.truck_id')
    //         ->join('products_category_main', 'products_category_main.id_pro_cate_main', '=', 'good_receipts_details.pro_cate_main_id')
    //         ->join('products_sub_cat_parent', 'products_sub_cat_parent.id_pro_sub_cat_parent', '=', 'good_receipts_details.pro_cate_parent_id')
    //         ->join('products_sub_cat_child', 'products_sub_cat_child.id_pro_sub_cat_child', '=', 'good_receipts_details.pro_cate_child_id')
    //         ->where('good_receipts_details.pro_cate_main_id', $request->main_category)
    //         ->where('good_receipts_details.pro_cate_parent_id', $request->parent_category)
    //         ->where('good_receipts_details.pro_cate_child_id', $request->child_category)
    //         ->get();

    //     if ($stockData->isEmpty()) {
    //         return response()->json(['message' => 'No data found for the selected criteria'], 404);
    //     }

    //     return response()->json(['stockData' => $stockData]);
    // }

    // New Contorller code
    public function showStockReportForm()
    {
        $trucks = GoodReceiptTruck::all();
        return view('reports.stock_report', compact('trucks'));
    }

    public function getDetailsByTruck($truckId)
    {
        // Fetch suppliers
        $suppliers = GoodReceiptDetail::where('truck_id', $truckId)
            ->with('goodReceipt.supplier')
            ->get()
            ->pluck('goodReceipt.supplier')
            ->unique('id_supplier');

        // Fetch main categories
        $mainCategories = GoodReceiptDetail::where('truck_id', $truckId)
            ->with('productMainCate')
            ->get()
            ->pluck('productMainCate')
            ->unique('id_pro_cate_main');

        // Fetch parent categories
        $parentCategories = GoodReceiptDetail::where('truck_id', $truckId)
            ->with('productParentCate')
            ->get()
            ->pluck('productParentCate')
            ->unique('id_pro_sub_cat_parent');

        // Fetch child categories
        $childCategories = GoodReceiptDetail::where('truck_id', $truckId)
            ->with('productChildCate')
            ->get()
            ->pluck('productChildCate')
            ->unique('id_pro_sub_cat_child');

        return response()->json([
            'suppliers' => $suppliers,
            'mainCategories' => $mainCategories,
            'parentCategories' => $parentCategories,
            'childCategories' => $childCategories,
        ]);
    }

    public function generateStockReport(Request $request)
    {
        // Validate the request
        $validated = $request->validate([
            'truck_id' => 'required|integer',
            'supplier_id' => 'nullable|integer',
            'main_category_id' => 'nullable|integer',
            'parent_category_id' => 'nullable|integer',
            'child_category_id' => 'nullable|integer',
        ]);

        // Query the data
        $query = GoodReceiptDetail::query()
            ->where('truck_id', $validated['truck_id']);

        if ($validated['supplier_id']) {
            $query->whereHas('goodReceipt.supplier', function ($q) use ($validated) {
                $q->where('id_supplier', $validated['supplier_id']);
            });
        }

        if ($validated['main_category_id']) {
            $query->where('pro_cate_main_id', $validated['main_category_id']);
        }

        if ($validated['parent_category_id']) {
            $query->where('pro_cate_parent_id', $validated['parent_category_id']);
        }

        if ($validated['child_category_id']) {
            $query->where('pro_cate_child_id', $validated['child_category_id']);
        }

        $data = $query->with([
            'goodReceipt.supplier',
            'productMainCate',
            'productParentCate',
            'productChildCate',
            'salesItemDetail', // Keep the relation for sales details
        ])->get()->map(function ($item) {
            // Aggregate 'bori' from related SalesItemDetail
            // $soldBori = $item->salesItemDetail->sum('bori') ?? 0;
            $soldBori = $item->salesItemDetail->first()->bori ?? 0;

            return [
                'date' => $item->created_at->format('Y-m-d'),
                'builty_number' => $item->goodReceipt->builty_no ?? 'N/A',
                'truck_number' => $item->goodReceiptTruck->truck_no ?? 'N/A',
                'supplier_name' => $item->goodReceipt->supplier->supplier_name ?? 'N/A',
                'main_product' => $item->productMainCate->products_category_name ?? 'N/A',
                'parent_product' => $item->productParentCate->name ?? 'N/A',
                'child_product' => $item->productChildCate->name ?? 'N/A',
                'marka' => $item->marka ?? 'N/A',
                'total_received_bori' => $item->bori + $soldBori ?? 0,
                'sold_bori' => $soldBori,
                // 'remaining_bori' => ($item->bori ?? 0) - $soldBori,
                'remaining_bori' => $item->bori,
            ];
        });

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


    public function ageing()
    {
        // Fetch any necessary data here, like suppliers, products, etc.
        return view('reports.ageing');
    }


    public function customerWiseReport(Request $request)
    {
        $customer_id = $request->customer_id ?? 0;
        $customers = DB::table('customers')->select('id_customers', 'customer_name')->get();
        $saleReport = [];

        if ($customer_id) {
            $sql = [
                'sales_items_details.*',
                'products_category_main.products_category_name',
                'products_sub_cat_parent.name as sub_name',
                'products_sub_cat_child.name as child_name',
                'sales_items_details.bori',
                'sales_items_details.weight',
                'sales_items_details.amount',
                'sales.total_amount',
                'sales.paid',
                'sales.balance',
                'sales.date',
                'customers.customer_name',
            ];

            $saleReport = Sale::select($sql)
                ->join('sales_items_details', 'sales_items_details.sales_id', '=', 'sales.id_sales')
                ->join('products_category_main', 'sales_items_details.items_main_cate', '=', 'products_category_main.id_pro_cate_main')
                ->join('products_sub_cat_parent', 'sales_items_details.items_parent_cate', '=', 'products_sub_cat_parent.id_pro_sub_cat_parent')
                ->join('products_sub_cat_child', 'sales_items_details.items_child_cate', '=', 'products_sub_cat_child.id_pro_sub_cat_child')
                ->join('customers', 'customers.id_customers', '=', 'sales.customer_id')
                ->where('sales.customer_id', '=', $customer_id)
                ->get()
                ->toArray();
        }

        return view('reports.customerWise', compact('customers', 'saleReport', 'customer_id'));
    }


    public function profit()
    {
        return view('reports.profit_in_loss');
    }

    // Controller method
    public function fetchCustomerData(Request $request)
    {
        $customer_id = $request->customer_id;

        // Fetch Truck Nos
        $trucks = DB::table('good_receipts_truck')
            ->join('sales', 'sales.good_receipts_truck_id', '=', 'good_receipts_truck.id_truck')
            ->where('sales.customer_id', $customer_id)
            ->select('good_receipts_truck.truck_no', 'good_receipts_truck.id_truck')
            ->distinct()
            ->get();

        // Fetch Products
        $products = DB::table('sales_items_details')
            ->join('sales', 'sales_items_details.sales_id', '=', 'sales.id_sales')
            ->join('products_category_main', 'sales_items_details.items_main_cate', '=', 'products_category_main.id_pro_cate_main')
            ->join('products_sub_cat_parent', 'sales_items_details.items_parent_cate', '=', 'products_sub_cat_parent.id_pro_sub_cat_parent')
            ->join('products_sub_cat_child', 'sales_items_details.items_child_cate', '=', 'products_sub_cat_child.id_pro_sub_cat_child')
            ->where('sales.customer_id', $customer_id)
            ->select(
                'products_category_main.products_category_name',
                'products_sub_cat_parent.name as sub_name',
                'products_sub_cat_child.name as child_name'
            )
            ->distinct()
            ->get();

        return response()->json([
            'trucks' => $trucks,
            'products' => $products,
        ]);
    }


    public function fetchReportData(Request $request)
    {
        try {
            $query = DB::table('sales')
                ->join('customers', 'sales.customer_id', '=', 'customers.id_customers')
                ->join('good_receipts_truck', 'sales.good_receipts_truck_id', '=', 'good_receipts_truck.id_truck')
                ->join('sales_items_details', 'sales_items_details.sales_id', '=', 'sales.id_sales')
                ->join('products_category_main', 'sales_items_details.items_main_cate', '=', 'products_category_main.id_pro_cate_main')
                ->join('products_sub_cat_parent', 'sales_items_details.items_parent_cate', '=', 'products_sub_cat_parent.id_pro_sub_cat_parent')
                ->join('products_sub_cat_child', 'sales_items_details.items_child_cate', '=', 'products_sub_cat_child.id_pro_sub_cat_child')
                ->select(
                    'sales.id_sales',
                    'customers.customer_name',
                    'sales.date as date',
                    'good_receipts_truck.truck_no',
                    'products_category_main.products_category_name',
                    'products_sub_cat_parent.name as sub_name',
                    'products_sub_cat_child.name as child_name',
                    'sales_items_details.bori',
                    'sales_items_details.weight',
                    'sales_items_details.rate',
                    DB::raw('(sales_items_details.weight * sales_items_details.rate) as amount'),
                    'sales.paid',
                    DB::raw('(sales_items_details.weight * sales_items_details.rate - sales.paid) as balance')
                );

            // Filter by customer_id if provided
            if (!empty($request->customer_id)) {
                $query->where('sales.customer_id', $request->customer_id);
            }

            // Filter by truck_no if provided
            if (!empty($request->truck_no)) {
                $query->where('good_receipts_truck.truck_no', $request->truck_no);
            }

            // Filter by product if provided
            if (!empty($request->product)) {
                $query->where('products_category_main.products_category_name', $request->product);
            }

            // Filter by date range (from_date and to_date)
            if (!empty($request->from_date) && !empty($request->to_date)) {
                $query->whereBetween('sales.date', [$request->from_date, $request->to_date]);
            }

            $data = $query->get();

            return response()->json(['data' => $data]);
        } catch (\Exception $e) {
            Log::error('Error fetching report data: ' . $e->getMessage());
            return response()->json(['error' => 'Failed to fetch report data.'], 500);
        }
    }



    public function salebill(Request $request)
    {

        $customer_id = $request->customer_id ?? 0;
        $customers = DB::table('customers')->select('id_customers', 'customer_name')->get();
        $saleReport = [];

        if ($customer_id) {
            $sql = [
                'sales_items_details.*',
                'products_category_main.products_category_name',
                'products_sub_cat_parent.name as sub_name',
                'products_sub_cat_child.name as child_name',
                'sales_items_details.bori',
                'sales_items_details.weight',
                'sales_items_details.amount',
                'sales.total_amount',
                'sales.paid',
                'sales.balance',
                'sales.date',
                'customers.customer_name',
                'good_receipts_truck.truck_no',
            ];

            $saleReport = Sale::select($sql)
                ->join('sales_items_details', 'sales_items_details.sales_id', '=', 'sales.id_sales')
                ->join('products_category_main', 'sales_items_details.items_main_cate', '=', 'products_category_main.id_pro_cate_main')
                ->join('products_sub_cat_parent', 'sales_items_details.items_parent_cate', '=', 'products_sub_cat_parent.id_pro_sub_cat_parent')
                ->join('products_sub_cat_child', 'sales_items_details.items_child_cate', '=', 'products_sub_cat_child.id_pro_sub_cat_child')
                ->join('good_receipts_truck', 'sales.good_receipts_truck_id', '=', 'good_receipts_truck.id_truck')
                ->join('customers', 'customers.id_customers', '=', 'sales.customer_id')
                ->where('sales.customer_id', '=', $customer_id)
                ->get()
                ->toArray();
        }

        return view('reports.saleBill', compact('customers', 'saleReport', 'customer_id'));
    }

    public function dailysales(Request $request)
    {
        $customer_id = 0;
        //$customer = user_customer::select()->where('user_id', auth()->user()->id)->get();
        $saleReport = [];
        $saledate = date('d-m-Y');
        //$todate = date('d-m-Y', strtotime('+30 days'));
        if ($request->saledate) {

            $saledate = $request->saledate;
            // $todate = $request->todate;
            $_saledate = date('Y-m-d', strtotime($request->saledate));
            // $_todate = date('Y-m-d', strtotime($request->todate));

            // $returnNote = DB::table('sale_return_notes')
            // ->select('gdn_id','product_id', DB::raw('IFNULL(SUM(return_qty),0) AS returned'),'batch_id')
            // ->groupBy('product_id')
            // ->groupBy('gdn_id');

            $sql = [
                'sales_items_details.*',
                'products_category_main.products_category_name',
                'products_sub_cat_parent.name as sub_name',
                'products_sub_cat_child.name as child_name',
                'sales.total_amount',
                'sales.paid',
                'sales.balance',
                'sales.date',
                'customers.customer_name',
                // DB::raw('debit as pending'),
                // DB::raw('credit as recieved'),
                // DB::raw('ifnull(sale_order_number, "Custom Invoice") as sale_order_number'),

            ];
            // ifnull(so.sale_order_number,'Custom Invoice' )
            $saleReport = Sale::select($sql)
                //->join('sale_order_details', 'sale_order_details.sale_order_id', '=', 'sale_orders.id_sale_orders')
                ->join('sales_items_details', 'sales_items_details.sales_id', '=', 'sales.id_sales')
                ->join('products_category_main', 'sales_items_details.items_main_cate', '=', 'products_category_main.id_pro_cate_main')
                ->join('products_sub_cat_parent', 'sales_items_details.items_parent_cate', '=', 'products_sub_cat_parent.id_pro_sub_cat_parent')
                ->join('products_sub_cat_child', 'sales_items_details.items_child_cate', '=', 'products_sub_cat_child.id_pro_sub_cat_child')
                // ->join('sale_invoice', 'sale_invoice.id_sale_invoice', '=', 'account_voucher_details.invoice_number')
                // ->leftJoin('sale_orders', 'sale_orders.id_sale_orders', '=', 'sale_invoice.sale_order_id')
                // ->leftjoinSub($returnNote, 'returnNote', function ($join) {
                //     $join->on('gdn_details.gdn_id', '=', 'returnNote.gdn_id');
                //     $join->on('gdn_details.gdn_product_id', '=', 'returnNote.product_id');
                // })
                ->join('customers', 'customers.id_customers', '=', 'sales.customer_id')
                ->where('sales.date', '=', date('Y-m-d', strtotime($saledate)))
                // ->where('transaction_account_id', '=', getTransactionAccountId('RECEIVABLES FROM CUSTOMER'))
                // ->where('sale_invoice.business_id', '=', auth()->user()->business_id)
                // if ($customer_id != 0) {
                //     $saleReport = $saleReport->where('sale_orders.customer_id', '=', $customer_id);
                // }
                //$saleReport = $saleReport->groupBy('goods_delivery_note.customer_id')
                // ->orderBy('credit')
                ->get()->toArray();
            //->toSql(); echo $saleReport;exit;
            // return $saleReport;
            // echo $saleReport;
            // exit;
        }
        // $business = DB::table('business')->where('id_business', auth()->user()->business_id)->first();
        return view('reports.dailySale', compact('saledate', 'saleReport'));
    }



    // public function purchaseReport()
    // {
    //     $suppliers = Suppliers::all();
    //     return view('reports.purchaseReport', compact('suppliers'));
    // }

    public function purchaseReportShow()
    {
        $suppliers = Suppliers::all();
        return view('reports.purchaseReport', compact('suppliers'));
    }

    // Do not remove this code ==================================================================
    // public function generatepurchaseReport(Request $request)
    // {
    //     // Validate the request
    //     $validated = $request->validate([
    //         'report_date' => 'required|date',
    //         'supplier_id' => 'required|exists:suppliers,id_supplier',
    //     ]);

    //     // Extract filters
    //     $reportDate = $validated['report_date'];
    //     $supplierId = $validated['supplier_id'];

    //     $data = GoodReceiptDetail::query()
    //         ->whereHas('goodReceipt', function ($q) use ($supplierId, $reportDate) {
    //             $q->where('supplier_id', $supplierId)
    //                 ->whereDate('created_at', $reportDate);
    //         })
    //         ->with([
    //             'goodReceipt.supplier',
    //             'goodReceiptTruck',
    //             'productMainCate',
    //             'productParentCate',
    //             'productChildCate',
    //         ])
    //         ->get()
    //         ->map(function ($item) {
    //             return [
    //                 'date' => $item->created_at->format('Y-m-d'),
    //                 'truck_number' => $item->goodReceiptTruck->truck_no ?? 'N/A',
    //                 'supplier_name' => $item->goodReceipt->supplier->supplier_name ?? 'N/A',
    //                 'main_product' => $item->productMainCate->products_category_name ?? 'N/A',
    //                 'parent_product' => $item->productParentCate->name ?? 'N/A',
    //                 'child_product' => $item->productChildCate->name ?? 'N/A',
    //                 'marka' => $item->marka ?? 'N/A',
    //                 'bori' => $item->bori ?? 'N/A',
    //                 'weight' => $item->weight ?? 'N/A',
    //                 'rate' => $item->rate ?? 'N/A',
    //                 'amount' => $item->amount ?? 'N/A',
    //             ];
    //         });

    //     return response()->json(['data' => $data]);
    // }
    // Do not remove this code ==================================================================

    // Working
    // public function generatePurchaseReport(Request $request)
    // {
    //     // Validate the request
    //     $validated = $request->validate([
    //         'report_date' => 'required|date',
    //         'supplier_id' => 'required|exists:suppliers,id_supplier',
    //     ]);

    //     // Extract filters
    //     $reportDate = $validated['report_date'];
    //     $supplierId = $validated['supplier_id'];

    //     $data = BCheque::query()
    //         ->where('supplier_id', $supplierId)
    //         ->whereDate('date', $reportDate)
    //         ->with(['items', 'details', 'supplier'])
    //         ->get()
    //         ->flatMap(function ($bCheque) {
    //             return $bCheque->items->map(function ($item) use ($bCheque) {
    //                 return [
    //                     'date' => $bCheque->date,
    //                     'supplier_name' => $bCheque->supplier->supplier_name ?? 'N/A',
    //                     'main_cate' => $item->main_cate ?? 'N/A',
    //                     'parent_cate' => $item->parent_cate ?? 'N/A',
    //                     'child_cate' => $item->child_cate ?? 'N/A',
    //                     'marka' => $item->marka ?? 'N/A',
    //                     'bori' => $item->bori ?? 'N/A',
    //                     'weight' => $item->weight ?? 'N/A',
    //                     'rate' => $item->rate ?? 'N/A',
    //                     'amount' => $item->amount ?? 'N/A',
    //                     'labour_amount' => $bCheque->details->labour_amount ?? 'N/A',
    //                 ];
    //             });
    //         });

    //     return response()->json(['data' => $data]);
    // }
    public function generatePurchaseReport(Request $request)
    {
        // Validate the request
        $validated = $request->validate([
            'report_date_from' => 'required|date',
            'report_date_to' => 'required|date|after_or_equal:report_date_from',
            'supplier_id' => 'required|exists:suppliers,id_supplier',
        ]);

        // Extract filters
        $dateFrom = $validated['report_date_from'];
        $dateTo = $validated['report_date_to'];
        $supplierId = $validated['supplier_id'];

        $data = BCheque::query()
            ->where('supplier_id', $supplierId)
            ->whereBetween('date', [$dateFrom, $dateTo])
            ->with(['items', 'details', 'supplier'])
            ->get()
            ->flatMap(function ($bCheque) {
                return $bCheque->items->map(function ($item) use ($bCheque) {
                    return [
                        'date' => $bCheque->date,
                        'supplier_name' => $bCheque->supplier->supplier_name ?? 'N/A',
                        'main_cate' => $item->main_cate ?? 'N/A',
                        'parent_cate' => $item->parent_cate ?? 'N/A',
                        'child_cate' => $item->child_cate ?? 'N/A',
                        'marka' => $item->marka ?? 'N/A',
                        'bori' => $item->bori ?? 'N/A',
                        'weight' => $item->weight ?? 'N/A',
                        'rate' => $item->rate ?? 'N/A',
                        'amount' => $item->amount ?? 'N/A',
                        'labour_amount' => $bCheque->details->labour_amount ?? 'N/A',
                    ];
                });
            });

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





    // public function generatepurchaseReport(Request $request)
    // {
    //     $request->validate([
    //         'supplier_id' => 'required', // Supplier is mandatory
    //     ]);

    //     $query = GoodReceiptDetail::select(
    //         'good_receipts_truck.truck_no',
    //         'good_receipts_details.bori',
    //         'products_category_main.products_category_name as main_category_name',
    //         'products_sub_cat_parent.name as parent_category_name',
    //         'products_sub_cat_child.name as child_category_name'
    //     )
    //         ->join('good_receipts_truck', 'good_receipts_truck.id_truck', '=', 'good_receipts_details.truck_id')
    //         ->join('products_category_main', 'products_category_main.id_pro_cate_main', '=', 'good_receipts_details.pro_cate_main_id')
    //         ->join('products_sub_cat_parent', 'products_sub_cat_parent.id_pro_sub_cat_parent', '=', 'good_receipts_details.pro_cate_parent_id')
    //         ->join('products_sub_cat_child', 'products_sub_cat_child.id_pro_sub_cat_child', '=', 'good_receipts_details.pro_cate_child_id')
    //         ->join('good_receipts', 'good_receipts.id_good_rec', '=', 'good_receipts_details.good_receipts_id') // Join to link the supplier
    //         ->where('good_receipts.supplier_id', $request->supplier_id); // Filter by supplier


    //     // if ($request->has('from_date') && $request->has('to_date')) {
    //     $query->whereBetween('good_receipts.date', [$request->from_date, $request->to_date]);
    //     // }

    //     if ($request->filled('main_category')) {
    //         $query->where('good_receipts_details.pro_cate_main_id', $request->main_category);
    //     }

    //     if ($request->filled('parent_category')) {
    //         $query->where('good_receipts_details.pro_cate_parent_id', $request->parent_category);
    //     }

    //     if ($request->filled('child_category')) {
    //         $query->where('good_receipts_details.pro_cate_child_id', $request->child_category);
    //     }

    //     $stockData = $query->get();

    //     if ($stockData->isEmpty()) {
    //         return response()->json(['message' => 'No data found for the selected criteria'], 404);
    //     }
    //     return response()->json(['stockData' => $stockData]);
    // }

    //     public function generatepurchaseReport(Request $request)
    //     {

    //         // Validate the request
    //         $request->validate([
    //             'supplier_id' => 'required', // Supplier is mandatory
    //             'from_date' => 'nullable|date', // From date is required and must be a valid date
    //             'to_date' => 'nullable|date', // To date is required, must be a valid date and after or equal to from_date
    //         ]);

    //         // Initialize the query for fetching the stock data
    //         $query = GoodReceiptDetail::select(
    //             'good_receipts_truck.truck_no',
    //             'good_receipts_details.bori',
    //             'products_category_main.products_category_name as main_category_name',
    //             'products_sub_cat_parent.name as parent_category_name',
    //             'products_sub_cat_child.name as child_category_name'
    //         )
    //             ->join('good_receipts_truck', 'good_receipts_truck.id_truck', '=', 'good_receipts_details.truck_id')
    //             ->join('products_category_main', 'products_category_main.id_pro_cate_main', '=', 'good_receipts_details.pro_cate_main_id')
    //             ->join('products_sub_cat_parent', 'products_sub_cat_parent.id_pro_sub_cat_parent', '=', 'good_receipts_details.pro_cate_parent_id')
    //             ->join('products_sub_cat_child', 'products_sub_cat_child.id_pro_sub_cat_child', '=', 'good_receipts_details.pro_cate_child_id')
    //             ->join('good_receipts', 'good_receipts.id_good_rec', '=', 'good_receipts_details.good_receipts_id') // Join to link the supplier
    //             ->where('good_receipts.supplier_id', $request->supplier_id); // Filter by supplier

    //         // Filter by the provided date range
    //         if ($request->has('from_date') && $request->has('to_date')) {
    //             $query->whereBetween('good_receipts.date', [$request->from_date, $request->to_date]);
    //         } elseif ($request->has('from_date')) {
    //             $query->where('good_receipts.date', '>=', $request->from_date);
    //         } elseif ($request->has('to_date')) {
    //             $query->where('good_receipts.date', '<=', $request->to_date);
    //         }
    //         // Apply filters for categories if they are provided
    //         if ($request->filled('main_category')) {
    //             $query->where('good_receipts_details.pro_cate_main_id', $request->main_category);
    //         }

    //         if ($request->filled('parent_category')) {
    //             $query->where('good_receipts_details.pro_cate_parent_id', $request->parent_category);
    //         }

    //         if ($request->filled('child_category')) {
    //             $query->where('good_receipts_details.pro_cate_child_id', $request->child_category);
    //         }

    //         // Get the stock data
    //         $stockData = $query->get();

    //         // Check if any data was found and return the appropriate response
    //         if ($stockData->isEmpty()) {
    //             return response()->json(['message' => 'No data found for the selected criteria'], 404);
    //         }

    //         // Return the stock data in the response
    //         return response()->json(['stockData' => $stockData]);
    //     }
}
