<?php

namespace App\Http\Controllers;

use App\BCheque;
use App\Brands;
use App\ControlAccount;
use App\Customer;
use App\GoodReceiptDetail;
use App\GoodReceipts;
use App\GoodReceiptTruck;
use App\ProductsCategoryMain;
use App\ProductsSubCategoryChild;
use App\ProductsSubCategoryParent;
use App\Sale;
use Illuminate\Support\Collection;

use App\SalesItemDetail;
use App\Suppliers;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Crypt;
use Illuminate\Support\Facades\DB;
use PhpOffice\PhpSpreadsheet\Calculation\MathTrig\Trunc;

class ReportController extends Controller
{

    protected string $module = 'Reports Management';

    public function __construct()
    {
        $module = $this->module;
        $this->middleware("permission:$module Sale Bill")->only(['salebill']);
        $this->middleware("permission:$module Sale Register")->only(['saleRegister']);
        $this->middleware("permission:$module Receivables")->only(['receivables']);
        $this->middleware("permission:$module Stock Hand")->only(['stockInHandRegister']);
        $this->middleware("permission:$module Truck Sale Register")->only(['truckSaleRegister']);
    }



    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 saleReportDetail(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.saleReportDetail', compact('customers', 'saleReport', 'customer_id'));
    }



    // 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]);
    }

    //Ageing summary report
    // public function ageingSummary(Request $request)
    // {

    //     $business_type = "Customer";
    //     $fromdate = date('d-m-Y');
    //     $type = $request->type;
    //     $data = [];

    //     if ($request->fromdate) {

    //         $start_date = date('Y-m-d', strtotime($request->fromdate));
    //         $date30 = date("Y-m-d", strtotime('-30 days', strtotime($start_date)));
    //         $date60 = date("Y-m-d", strtotime('-60 days', strtotime($start_date)));
    //         $date90 = date("Y-m-d", strtotime('-90 days', strtotime($start_date)));

    //         // $user_cities = user_city::select('city_id')->where('user_id', '=', auth()->user()->id)->get()->toArray();

    //         // if (empty($user_cities)) {
    //             $customers = Customer::select('*')
    //                 // ->where('customers.business_id', '=', auth()->user()->business_id)
    //                 // ->whereNull('customers.deleted_at')
    //                 ->orderby('customers.customer_name', 'ASC')
    //                 ->get()
    //                 ->toArray();
    //         // } else {
    //             // $customers = Customer::select('*')
    //             //     ->where('customers.business_id', '=', auth()->user()->business_id)
    //             //     ->whereIn('city_id', $user_cities)
    //             //     ->whereNull('customers.deleted_at')
    //             //     ->orderby('customers.customer_name', 'ASC')
    //             //     ->get()
    //             //     ->toArray();
    //         // }

    //         $suppliers = Suppliers::select('*')
    //             // ->where('suppliers.business_id', '=', auth()->user()->business_id)
    //             // ->whereNull('suppliers.deleted_at')
    //             ->orderby('suppliers.supplier_name', 'ASC')
    //             ->get()
    //             ->toArray();

    //         if ($type == 'Receivable') {
    //             foreach ($customers as $customer) {

    //                 $business_name_id = $customer['id_customers'];

    //                 #between 30 days
    //                 $data_30 = getCustomerBalance($customer['id_customers'], $date30, $start_date, 0, auth()->user()->business_id, $type);

    //                 #between 60 days
    //                 $data_60 = getCustomerBalance($customer['id_customers'], $date60, $date30, 0, auth()->user()->business_id, $type);

    //                 #between 90 days
    //                 $data_90 = getCustomerBalance($customer['id_customers'], $date90, $date60, 0, auth()->user()->business_id, $type);

    //                 #over 90 days
    //                 $over_90 = getCustomerBalance($customer['id_customers'], $date90, 0, auth()->user()->business_id, $type);

    //                 $total_balance = getSingleCustomerBalance($customer['id_customers']);

    //                 //exit();
    //                 $total = $total_balance;
    //                 $inv_total_balances = $over_90 + $data_90 + $data_60 + $data_30;
    //                 $previous_balance = $total - $inv_total_balances;

    //                 $data[] = ['customer_id' => $business_name_id,
    //                     'customer_name' => $customer['customer_name'],
    //                     'data30' => $data_30,
    //                     'data60' => $data_60,
    //                     'data90' => $data_90,
    //                     'current_balance' => $total,
    //                     'data90over' => $over_90,
    //                     'opening_balance' => $previous_balance,
    //                 ];
    //             }
    //         } else {
    //             foreach ($suppliers as $customer) {
    //                 $business_name_id = $customer['id_suppliers'];

    //                 #between 30 days
    //                 $data_30 = getSupplierBalance($business_name_id, $date30, $start_date, 0, auth()->user()->business_id, $type);

    //                 #between 60 days
    //                 $data_60 = getSupplierBalance($business_name_id, $date60, $date30, 0, auth()->user()->business_id, $type);

    //                 #between 90 days
    //                 $data_90 = getSupplierBalance($business_name_id, $date90, $date60, 0, auth()->user()->business_id, $type);

    //                 #over 90 days
    //                 $over_90 = getSupplierBalance($business_name_id, $date90, 0, auth()->user()->business_id, $type);

    //                 //exit();
    //                 $total = $over_90 + $data_90 + $data_60 + $data_30;
    //                 $previous_balance = 0;

    //                 $data[] = ['customer_id' => $business_name_id,
    //                     'customer_name' => $customer['supplier_name'],
    //                     'data30' => $data_30,
    //                     'data60' => $data_60,
    //                     'data90' => $data_90,
    //                     'current_balance' => $total,
    //                     'data90over' => $over_90,
    //                     'opening_balance' => $previous_balance,
    //                 ];
    //             }
    //         }

    //         return $data;
    //     }

    //     $business = DB::table('business')->where('id_business', auth()->user()->business_id)->first();

    //     return view('reports.ageing', compact('data', 'business', 'fromdate'));
    // }
    public function ageingSummary(Request $request)
    {
        // $business = DB::table('business')->where('id_business', auth()->user()->business_id)->first();
        $fromdate = date('d-m-Y');
        return view('reports.ageing', compact('fromdate'));
    }

    // public function ageingSummaryData(Request $request)
    // {
    //     $type = $request->report_type;
    //     $fromdate = $request->fromdate ?? date('Y-m-d');
    //     $data = [];

    //     if ($fromdate) {
    //         $start_date = date('Y-m-d', strtotime($fromdate));
    //         $date30 = date("Y-m-d", strtotime('-30 days', strtotime($start_date)));
    //         $date60 = date("Y-m-d", strtotime('-60 days', strtotime($start_date)));
    //         $date90 = date("Y-m-d", strtotime('-90 days', strtotime($start_date)));

    //         $customers = Customer::select('*')->orderby('customer_name', 'ASC')->get()->toArray();
    //         $suppliers = Suppliers::select('*')->orderby('supplier_name', 'ASC')->get()->toArray();

    //         if ($type === 'Receivable') {
    //             foreach ($customers as $customer) {
    //                 $business_name_id = $customer['id_customers'];

    //                 $data_30 = getCustomerBalance($business_name_id, $date30, $start_date, 0, auth()->user()->business_id, $type);
    //                 $data_60 = getCustomerBalance($business_name_id, $date60, $date30, 0, auth()->user()->business_id, $type);
    //                 $data_90 = getCustomerBalance($business_name_id, $date90, $date60, 0, auth()->user()->business_id, $type);
    //                 $over_90 = getCustomerBalance($business_name_id, $date90, 0, auth()->user()->business_id, $type);
    //                 $total_balance = getSingleCustomerBalance($business_name_id);

    //                 $data[] = [
    //                     'customer_id' => $business_name_id,
    //                     'customer_name' => $customer['customer_name'],
    //                     'range_1_30' => $data_30,
    //                     'range_31_60' => $data_60,
    //                     'range_61_90' => $data_90,
    //                     'range_over_90' => $over_90,
    //                     'total' => $total_balance,
    //                     'previous_balance' => $total_balance - ($data_30 + $data_60 + $data_90 + $over_90),
    //                 ];
    //             }
    //         } else {
    //             foreach ($suppliers as $supplier) {
    //                 $business_name_id = $supplier['id_suppliers'];

    //                 $data_30 = getSupplierBalance($business_name_id, $date30, $start_date, 0, auth()->user()->business_id, $type);
    //                 $data_60 = getSupplierBalance($business_name_id, $date60, $date30, 0, auth()->user()->business_id, $type);
    //                 $data_90 = getSupplierBalance($business_name_id, $date90, $date60, 0, auth()->user()->business_id, $type);
    //                 $over_90 = getSupplierBalance($business_name_id, $date90, 0, auth()->user()->business_id, $type);

    //                 $data[] = [
    //                     'customer_id' => $business_name_id,
    //                     'customer_name' => $supplier['supplier_name'],
    //                     'range_1_30' => $data_30,
    //                     'range_31_60' => $data_60,
    //                     'range_61_90' => $data_90,
    //                     'range_over_90' => $over_90,
    //                     'total' => $data_30 + $data_60 + $data_90 + $over_90,
    //                     'previous_balance' => 0,
    //                 ];
    //             }
    //         }
    //     }

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

    public function ageingSummaryData(Request $request)
    {
        $type = $request->report_type;
        $fromdate = $request->fromdate ?? date('Y-m-d');
        $data = [];

        if ($fromdate) {
            $start_date = date('Y-m-d', strtotime($fromdate));
            $date30 = date("Y-m-d", strtotime('-30 days', strtotime($start_date)));
            $date60 = date("Y-m-d", strtotime('-60 days', strtotime($start_date)));
            $date90 = date("Y-m-d", strtotime('-90 days', strtotime($start_date)));

            if ($type === 'Receivable') {
                $customers = Customer::orderBy('customer_name', 'ASC')->get();

                foreach ($customers as $customer) {
                    $customerId = $customer->id_customers;

                    $data_30 = getCustomerBalance($customerId, $date30, $start_date, $type);
                    $data_60 = getCustomerBalance($customerId, $date60, $date30, $type);
                    $data_90 = getCustomerBalance($customerId, $date90, $date60, $type);
                    $over_90 = getCustomerBalance($customerId, $date90, null, $type);
                    $total_balance = getSingleCustomerBalance($customerId);

                    $data[] = [
                        'customer_id' => $customerId,
                        'customer_name' => $customer->customer_name,
                        'range_1_30' => $data_30,
                        'range_31_60' => $data_60,
                        'range_61_90' => $data_90,
                        'range_over_90' => $over_90,
                        'total' => $total_balance,
                        'previous_balance' => $total_balance - ($data_30 + $data_60 + $data_90 + $over_90),
                    ];
                }
            } else {
                $suppliers = Suppliers::orderBy('supplier_name', 'ASC')->get();

                foreach ($suppliers as $supplier) {
                    $supplierId = $supplier->id_supplier;

                    $data_30 = getSupplierBalance($supplierId, $date30, $start_date, $type);
                    $data_60 = getSupplierBalance($supplierId, $date60, $date30, $type);
                    $data_90 = getSupplierBalance($supplierId, $date90, $date60, $type);
                    $over_90 = getSupplierBalance($supplierId, $date90, null, $type);

                    $data[] = [
                        'customer_id' => $supplierId,
                        'customer_name' => $supplier->supplier_name,
                        'range_1_30' => $data_30,
                        'range_31_60' => $data_60,
                        'range_61_90' => $data_90,
                        'range_over_90' => $over_90,
                        'total' => $data_30 + $data_60 + $data_90 + $over_90,
                        'previous_balance' => 0,
                    ];
                }
            }
        }

        return response()->json($data);
    }



    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');
    }



    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 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 ==================================================================

    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 salebill(Request $request)
    {

        $productCateMain = ProductsCategoryMain::all();

        $customers = Customer::select('id_customers', 'customer_name')->get();
        return view('reports.sale_bill', compact('customers', 'productCateMain'));
    }
    // public function saleBillPrintView(Request $request)
    // {
    //     $request->validate([
    //         'date' => 'nullable|date',
    //         'customers' => 'nullable|array',
    //         'customers.*' => 'integer|exists:customers,id_customers',
    //         'productMainCate' => 'nullable|integer',
    //         'productParentCate' => 'nullable|integer',
    //         'productChildCate' => 'nullable|integer',
    //     ]);

    //     // Relationships ke saath data load karein
    //     $query = Sale::with(['salesItemsDetails.productChildCate', 'customer']);

    //     if ($request->date) {
    //         $query->whereDate('date', $request->date);
    //     }

    //     if ($request->has('customers') && is_array($request->customers)) {
    //         $query->whereIn('customer_id', $request->customers);
    //     }

    //     $query->whereHas('salesItemsDetails', function ($q) use ($request) {
    //         if ($request->productMainCate) {
    //             $q->where('items_main_cate', $request->productMainCate);
    //         }
    //         if ($request->productParentCate) {
    //             $q->where('items_parent_cate', $request->productParentCate);
    //         }
    //         if ($request->productChildCate) {
    //             $q->where('items_child_cate', $request->productChildCate);
    //         }
    //     });

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

    //     $sales = $salesData->groupBy(function ($item) {
    //         return $item->customer->customer_name_urdu ?? 'نامعلوم';
    //     })->map(function ($customerSales, $customerName) use ($customerCredits) {

    //         $customerId = optional($customerSales->first()->customer)->id_customers;

    //         return [
    //             'customer_name' => $customerName,
    //             'sales'         => $customerSales,
    //             'credit_total'  => $customerCredits[$customerId] ?? 0,
    //         ];
    //     });

    //     if ($salesData->isEmpty()) {
    //         return back()->with('error', 'No sales found for selected filters.');
    //     }

    //     // --- ASLI FIX YAHAN HAI ---
    //     // Hum sales ko Customer ke Urdu Name ke mutabiq group kar rahe hain
    //     $sales = $salesData->groupBy(function ($item) {
    //         return $item->customer->customer_name_urdu ?? 'نامعلوم';
    //     });

    //     return view('reports.sale_bill_print_view', compact('sales'));
    // }
//  public function saleBillPrintView(Request $request)
// {
//     $request->validate([
//         'date'             => 'nullable|date',
//         'customers'        => 'nullable|array',
//         'customers.*'      => 'integer|exists:customers,id_customers',
//         'productMainCate'  => 'nullable|integer',
//         'productParentCate'=> 'nullable|integer',
//         'productChildCate' => 'nullable|integer',
//     ]);

//     $selectedDate   = $request->date; // null if empty
//     $customerFilter = $request->customers ?? [];

//     // اگر date دی ہے تو next day wasooli، ورنہ null
//     $nextDate = $selectedDate
//         ? Carbon::parse($selectedDate)->addDay()->toDateString()
//         : null;

//     // ── 1. Voucher credits UP TO selected date (سابقہ بیلنس mein minus) ──
//     //    date empty ho toh sari vouchers
//     $prevVoucherQuery = DB::table('vouchers')
//         ->selectRaw('jt.adaegi_khata AS customer_id, SUM(jt.credit_amount) AS total_credit')
//         ->join(DB::raw("
//             JSON_TABLE(
//                 vouchers.adaegi_array,
//                 '\$[*]' COLUMNS (
//                     adaegi_khata  INT            PATH '\$.adaegi_khata',
//                     credit_amount DECIMAL(15,2)  PATH '\$.credit_amount'
//                 )
//             ) AS jt
//         "), DB::raw('1'), DB::raw('1'))
//         ->groupBy('jt.adaegi_khata');

//     if ($selectedDate) {
//         $prevVoucherQuery->where('vouchers.voucher_date', '<=', $selectedDate);
//     }

//     $prevVoucherCredits = $prevVoucherQuery->pluck('total_credit', 'customer_id');

//     // ── 2. Next day wasooli (sirf jab date di ho) ─────────────────────────
//     $nextDayVoucherCredits = collect();
//     if ($nextDate) {
//         $nextDayVoucherCredits = DB::table('vouchers')
//             ->selectRaw('jt.adaegi_khata AS customer_id, SUM(jt.credit_amount) AS total_credit')
//             ->join(DB::raw("
//                 JSON_TABLE(
//                     vouchers.adaegi_array,
//                     '\$[*]' COLUMNS (
//                         adaegi_khata  INT            PATH '\$.adaegi_khata',
//                         credit_amount DECIMAL(15,2)  PATH '\$.credit_amount'
//                     )
//                 ) AS jt
//             "), DB::raw('1'), DB::raw('1'))
//             ->where('vouchers.voucher_date', '=', $nextDate)
//             ->groupBy('jt.adaegi_khata')
//             ->pluck('total_credit', 'customer_id');
//     }

//     // ── 3. Previous sales BEFORE selected date ────────────────────────────
//     //    date empty ho toh koi previous sales nahi (sab "today" mein)
//     $prevSales = collect();
//     if ($selectedDate) {
//         $prevQuery = Sale::select(
//                 'customer_id',
//                 DB::raw("SUM(CAST(REPLACE(total_amount, ',', '') AS DECIMAL(15,2))) AS prev_total_amount"),
//                 DB::raw("SUM(CAST(REPLACE(paid,         ',', '') AS DECIMAL(15,2))) AS prev_paid_amount")
//             )
//             ->where('date', '<', $selectedDate)
//             ->groupBy('customer_id');

//         if (!empty($customerFilter)) {
//             $prevQuery->whereIn('customer_id', $customerFilter);
//         }

//         $prevSales = $prevQuery->get()->keyBy('customer_id');
//     }

//     // ── 4. Today's sales (with items) ─────────────────────────────────────
//     $query = Sale::with([
//         'salesItemsDetails.productChildCate',
//         'customer',
//     ]);

//     // date di ho toh filter karo, warna sab records
//     if ($selectedDate) {
//         $query->whereDate('date', $selectedDate);
//     }

//     if (!empty($customerFilter)) {
//         $query->whereIn('customer_id', $customerFilter);
//     }

//     // Sirf tab whereHas lagao jab koi category filter diya ho
//     if ($request->productMainCate || $request->productParentCate || $request->productChildCate) {
//         $query->whereHas('salesItemsDetails', function ($q) use ($request) {
//             if ($request->productMainCate) {
//                 $q->where('items_main_cate', $request->productMainCate);
//             }
//             if ($request->productParentCate) {
//                 $q->where('items_parent_cate', $request->productParentCate);
//             }
//             if ($request->productChildCate) {
//                 $q->where('items_child_cate', $request->productChildCate);
//             }
//         });
//     } else {
//         // Koi filter nahi - sirf ensure karo ke items hain
//         $query->has('salesItemsDetails');
//     }

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

//     if ($salesData->isEmpty()) {
//         return back()->with('error', 'No sales found for selected filters.');
//     }

//     // ── 5. Group by customer and attach balances ───────────────────────────
//     $sales = $salesData
//         ->groupBy(function ($sale) {
//             return $sale->customer->customer_name_urdu ?? 'نامعلوم';
//         })
//         ->map(function ($customerSales, $customerName) use (
//             $prevSales, $prevVoucherCredits, $nextDayVoucherCredits
//         ) {
//             $customerId = optional($customerSales->first()->customer)->id_customers;

//             $prev = $prevSales[$customerId] ?? null;

//             // سابقہ بیلنس = pehle ki sales - pehle ki paid - vouchers
//             $prevTotalAmt    = $prev ? (float) $prev->prev_total_amount : 0;
//             $prevPaidAmt     = $prev ? (float) $prev->prev_paid_amount  : 0;
//             $prevVoucher     = (float) ($prevVoucherCredits[$customerId] ?? 0);
//             $previousBalance = $prevTotalAmt - $prevPaidAmt - $prevVoucher;

//             // وصولی = next day ke vouchers (0 if no date selected)
//             $recovery = (float) ($nextDayVoucherCredits[$customerId] ?? 0);

//             return [
//                 'customer_name'    => $customerName,
//                 'customer_id'      => $customerId,
//                 'sales'            => $customerSales,
//                 'previous_balance' => $previousBalance,
//                 'recovery'         => $recovery,
//             ];
//         });

//     return view('reports.sale_bill_print_view', compact('sales', 'selectedDate'));
// }

public function saleBillPrintView(Request $request)
{
    $request->validate([
        'date'             => 'nullable|date',
        'customers'        => 'nullable|array',
        'customers.*'      => 'integer|exists:customers,id_customers',
        'productMainCate'  => 'nullable|integer',
        'productParentCate'=> 'nullable|integer',
        'productChildCate' => 'nullable|integer',
    ]);

    $selectedDate   = $request->date; // null if empty
    $customerFilter = $request->customers ?? [];

    // ── 1. Voucher credits UP TO AND INCLUDING selected date ──────────────
    //    yeh سابقہ بیلنس mein minus honge
    $prevVoucherQuery = DB::table('vouchers')
        ->selectRaw('jt.adaegi_khata AS customer_id, SUM(jt.credit_amount) AS total_credit')
        ->join(DB::raw("
            JSON_TABLE(
                vouchers.adaegi_array,
                '\$[*]' COLUMNS (
                    adaegi_khata  INT            PATH '\$.adaegi_khata',
                    credit_amount DECIMAL(15,2)  PATH '\$.credit_amount'
                )
            ) AS jt
        "), DB::raw('1'), DB::raw('1'))
        ->groupBy('jt.adaegi_khata');

    if ($selectedDate) {
        // <= selectedDate: selected date ka voucher bhi سابقہ بیلنس mein minus hoga
        $prevVoucherQuery->where('vouchers.voucher_date', '<=', $selectedDate);
    }

    $prevVoucherCredits = $prevVoucherQuery->pluck('total_credit', 'customer_id');

    // ── 2. Previous sales BEFORE selected date (سابقہ بیلنس) ─────────────
    $prevSales = collect();
    if ($selectedDate) {
        $prevQuery = Sale::select(
                'customer_id',
                DB::raw("SUM(CAST(REPLACE(total_amount, ',', '') AS DECIMAL(15,2))) AS prev_total_amount"),
                DB::raw("SUM(CAST(REPLACE(paid,         ',', '') AS DECIMAL(15,2))) AS prev_paid_amount")
            )
            ->where('date', '<', $selectedDate)
            ->groupBy('customer_id');

        if (!empty($customerFilter)) {
            $prevQuery->whereIn('customer_id', $customerFilter);
        }

        $prevSales = $prevQuery->get()->keyBy('customer_id');
    }

    // ── 3. Today's sales with items ───────────────────────────────────────
    $query = Sale::with([
        'salesItemsDetails.productChildCate',
        'customer',
    ]);

    if ($selectedDate) {
        $query->whereDate('date', $selectedDate);
    }

    if (!empty($customerFilter)) {
        $query->whereIn('customer_id', $customerFilter);
    }

    if ($request->productMainCate || $request->productParentCate || $request->productChildCate) {
        $query->whereHas('salesItemsDetails', function ($q) use ($request) {
            if ($request->productMainCate) {
                $q->where('items_main_cate', $request->productMainCate);
            }
            if ($request->productParentCate) {
                $q->where('items_parent_cate', $request->productParentCate);
            }
            if ($request->productChildCate) {
                $q->where('items_child_cate', $request->productChildCate);
            }
        });
    } else {
        $query->has('salesItemsDetails');
    }

    $salesData = $query->get();

    if ($salesData->isEmpty()) {
        return back()->with('error', 'No sales found for selected filters.');
    }

    // ── 4. Group by customer and attach balances ──────────────────────────
    $sales = $salesData
        ->groupBy(function ($sale) {
            return $sale->customer->customer_name_urdu ?? 'نامعلوم';
        })
        ->map(function ($customerSales, $customerName) use (
            $prevSales, $prevVoucherCredits
        ) {
            $customerId = optional($customerSales->first()->customer)->id_customers;

            $prev = $prevSales[$customerId] ?? null;

            // سابقہ بیلنس = pehle ki sales - pehle ki paid - selected date tak ke vouchers
            $prevTotalAmt    = $prev ? (float) $prev->prev_total_amount : 0;
            $prevPaidAmt     = $prev ? (float) $prev->prev_paid_amount  : 0;
            $prevVoucher     = (float) ($prevVoucherCredits[$customerId] ?? 0);
            $previousBalance = $prevTotalAmt - $prevPaidAmt - $prevVoucher;

            return [
                'customer_name'    => $customerName,
                'customer_id'      => $customerId,
                'sales'            => $customerSales,
                'previous_balance' => $previousBalance,
            ];
        });

    return view('reports.sale_bill_print_view', compact('sales', 'selectedDate'));
}
    public function receivables(Request $request)
    {

        $customers = Customer::select('id_customers', 'customer_name')->get();
        return view('reports.receivables', compact('customers'));
    }
    // public function receivablesPrintView(Request $request)
    // {

    //     $request->validate([
    //         'from_date' => 'nullable|date',
    //         'to_date' => 'nullable|date',
    //         'customers' => 'nullable|array',
    //         'customers.*' => 'integer|exists:customers,id_customers',
    //     ]);

    //     $today = Carbon::today();
    //     $yesterday = Carbon::yesterday();

    //     $query = Sale::select(
    //         'customer_id',
    //         DB::raw("SUM(CAST(REPLACE(balance, ',', '') AS DECIMAL(15,2))) AS total_balance"),
    //         DB::raw("SUM(CAST(REPLACE(paid, ',', '') AS DECIMAL(15,2))) AS total_paid_amount"),
    //         DB::raw("SUM(CASE WHEN date < '$today' THEN CAST(REPLACE(total_amount, ',', '') AS DECIMAL(15,2)) ELSE 0 END) AS today_balance"),
    //         DB::raw("SUM(CASE WHEN date < '$today' THEN CAST(REPLACE(balance, ',', '') AS DECIMAL(15,2)) ELSE 0 END) AS yesterday_balance")

    //     )
    //         ->groupBy('customer_id');

    //     // dd($query);




    //     if ($request->from_date && $request->to_date) {
    //         $query->whereBetween('date', [$request->from_date, $request->to_date]);
    //     } elseif ($request->from_date) {
    //         $query->where('date', '>=', $request->from_date);
    //     } elseif ($request->to_date) {
    //         $query->where('date', '<=', $request->to_date);
    //     }

    //     // if ($request->date) {
    //     //     $query->whereDate('date', $request->date);
    //     // }

    //     if (!empty($request->customers)) {
    //         $query->whereIn('customer_id', $request->customers);
    //     }


    //     $query->having('total_balance', '>', 0);

    //     $sales = $query->get();
    //     // dd($sales);

    //     if ($sales->isEmpty()) {
    //         return back()->with('error', 'No customers with outstanding balance found.');
    //     }


    //     $dates = [
    //         'from_date' => $request->from_date,
    //         'to_date' => $request->to_date
    //     ];

    //     if ($sales->isEmpty()) {
    //         return back()->with('error', 'No customers with outstanding balance found.');
    //     }

    //     return view('reports.receivables_print_view', compact('sales', 'dates'));
    // }
    public function receivablesPrintView(Request $request)
    {
        $request->validate([
            'date'        => 'nullable|date',
            'customers'   => 'nullable|array',
            'customers.*' => 'integer|exists:customers,id_customers',
        ]);

        $selectedDate   = $request->date ?? Carbon::today()->toDateString();
        $customerFilter = $request->customers ?? [];


        $nextDate = Carbon::parse($selectedDate)->addDay()->toDateString();


        $prevVoucherCredits = DB::table('vouchers')
            ->selectRaw('jt.adaegi_khata AS customer_id, SUM(jt.credit_amount) AS total_credit')
            ->join(DB::raw("
            JSON_TABLE(
                vouchers.adaegi_array,
                '\$[*]' COLUMNS (
                    adaegi_khata  INT            PATH '\$.adaegi_khata',
                    credit_amount DECIMAL(15,2)  PATH '\$.credit_amount'
                )
            ) AS jt
        "), DB::raw('1'), DB::raw('1'))
            ->where('vouchers.voucher_date', '<=', $selectedDate)
            ->groupBy('jt.adaegi_khata')
            ->pluck('total_credit', 'customer_id');


        $todayVoucherCredits = DB::table('vouchers')
            ->selectRaw('jt.adaegi_khata AS customer_id, SUM(jt.credit_amount) AS total_credit')
            ->join(DB::raw("
            JSON_TABLE(
                vouchers.adaegi_array,
                '\$[*]' COLUMNS (
                    adaegi_khata  INT            PATH '\$.adaegi_khata',
                    credit_amount DECIMAL(15,2)  PATH '\$.credit_amount'
                )
            ) AS jt
        "), DB::raw('1'), DB::raw('1'))
            ->where('vouchers.voucher_date', '=', $nextDate) // ✅ next day ki wasooli
            ->groupBy('jt.adaegi_khata')
            ->pluck('total_credit', 'customer_id');


        $prevQuery = Sale::with('customer')
            ->select(
                'customer_id',
                DB::raw("SUM(CAST(REPLACE(total_amount, ',', '') AS DECIMAL(15,2))) AS prev_total_amount"),
                DB::raw("SUM(CAST(REPLACE(paid,         ',', '') AS DECIMAL(15,2))) AS prev_paid_amount")
            )
            ->where('date', '<', $selectedDate)
            ->groupBy('customer_id');

        if (!empty($customerFilter)) {
            $prevQuery->whereIn('customer_id', $customerFilter);
        }

        $prevSales = $prevQuery->get()->keyBy('customer_id');


        $todayQuery = Sale::with('customer')
            ->select(
                'customer_id',
                DB::raw("SUM(CAST(REPLACE(total_amount, ',', '') AS DECIMAL(15,2))) AS today_sale_amount")
            )
            ->where('date', '=', $selectedDate)
            ->groupBy('customer_id');

        if (!empty($customerFilter)) {
            $todayQuery->whereIn('customer_id', $customerFilter);
        }

        $todaySales = $todayQuery->get()->keyBy('customer_id');


        $allCustomerIds = collect($prevSales->keys())
            ->merge(collect($todaySales->keys()))
            ->merge(collect($prevVoucherCredits->keys()))
            ->merge(collect($todayVoucherCredits->keys()))
            ->unique()
            ->values();


        $sales = $allCustomerIds->map(function ($customerId) use (
            $prevSales,
            $todaySales,
            $prevVoucherCredits,
            $todayVoucherCredits
        ) {
            $prev       = $prevSales[$customerId]  ?? null;
            $today      = $todaySales[$customerId] ?? null;
            $saleRecord = $today ?? $prev;


            $prevTotalAmt    = $prev ? (float) $prev->prev_total_amount : 0;
            $prevPaidAmt     = $prev ? (float) $prev->prev_paid_amount  : 0;
            $prevVoucher     = (float) ($prevVoucherCredits[$customerId] ?? 0);
            $previousBalance = $prevTotalAmt - $prevPaidAmt - $prevVoucher;


            $todaySaleAmt = $today ? (float) $today->today_sale_amount : 0;

            $recovery = (float) ($todayVoucherCredits[$customerId] ?? 0);


            $remainingBalance = $previousBalance + $todaySaleAmt - $recovery;


            $customer = $saleRecord?->customer;
            if (!$customer) {
                $customer = Customer::find($customerId);
            }

            return (object) [
                'customer_id'      => $customerId,
                'customer'         => $customer,
                'previous_balance' => $previousBalance,
                'today_sale'       => $todaySaleAmt,
                'recovery'         => $recovery,
                'remaining'        => $remainingBalance,
            ];
        })
            ->filter(
                fn($s) =>
                round($s->previous_balance, 2) != 0 ||
                    round($s->today_sale, 2)       != 0 ||
                    round($s->recovery, 2)         != 0
            )
            ->values();

        if ($sales->isEmpty()) {
            return back()->with('error', 'No customers with outstanding balance found.');
        }

        return view('reports.receivables_print_view', compact('sales', 'selectedDate'));
    }

    public function saleRegister(Request $request)
    {

        $customers = Customer::select('id_customers', 'customer_name')->get();
        return view('reports.sale_register', compact('customers'));
    }
    public function saleRegisterPrintView(Request $request)
    {

        $request->validate([
            'date' => 'nullable|date',
            'customers' => 'nullable|array',
            'customers.*' => 'integer|exists:customers,id_customers',
        ]);
        $query = Sale::with(['salesItemsDetails', 'customer']);


        if ($request->date) {
            $query->whereDate('date', $request->date);
        }


        if ($request->has('customers') && is_array($request->customers)) {
            $query->whereIn('customer_id', $request->customers);
        }



        $sales = $query->get();

        $salesByCustomer = $sales->groupBy('customer_id');

        $customerTotals = Sale::select(
            'customer_id',
            DB::raw("SUM(CAST(REPLACE(paid, ',', '') AS DECIMAL(15,2))) as total_paid"),
            DB::raw("SUM(CAST(REPLACE(balance, ',', '') AS DECIMAL(15,2))) as total_balance")
        )
            ->when($request->date, fn($q) => $q->whereDate('date', $request->date))
            ->when(
                $request->has('customers') && is_array($request->customers),
                fn($q) => $q->whereIn('customer_id', $request->customers)
            )
            ->groupBy('customer_id')
            ->get()
            ->keyBy('customer_id');

        // dd($customerTotals);



        if ($sales->isEmpty()) {
            return back()->with('error', 'No sales found for selected filters.');
        }
        return view('reports.sale_register_print_view', compact('salesByCustomer', 'customerTotals'));
    }

    // Truck Sale Register Report
    // public function truckSaleRegister(Request $request)
    // {

    //     $customers = Customer::select('id_customers', 'customer_name')->get();
    //     return view('reports.truck_sale_register', compact('customers'));
    // }
    public function truckSaleRegister(Request $request)
    {
        $customers = Customer::select('id_customers', 'customer_name')->get();
        $trucks    = GoodReceiptTruck::select('id_truck', 'truck_no')->orderBy('truck_no')->get();

        return view('reports.truck_sale_register', compact('customers', 'trucks'));
    }
    // public function truckSaleRegisterPrintView(Request $request)
    // {

    //     $request->validate([
    //         'date' => 'nullable|date',
    //         'customers' => 'nullable|array',
    //         'customers.*' => 'integer|exists:customers,id_customers',
    //     ]);
    //     $query = Sale::with(['salesItemsDetails', 'customer']);


    //     if ($request->date) {
    //         $query->whereDate('date', $request->date);
    //     }


    //     if ($request->has('customers') && is_array($request->customers)) {
    //         $query->whereIn('customer_id', $request->customers);
    //     }



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

    //     $salesByCustomer = $sales->groupBy('customer_id');

    //     $customerTotals = Sale::select(
    //         'customer_id',
    //         DB::raw("SUM(CAST(REPLACE(paid, ',', '') AS DECIMAL(15,2))) as total_paid"),
    //         DB::raw("SUM(CAST(REPLACE(balance, ',', '') AS DECIMAL(15,2))) as total_balance")
    //     )
    //         ->when($request->date, fn($q) => $q->whereDate('date', $request->date))
    //         ->when(
    //             $request->has('customers') && is_array($request->customers),
    //             fn($q) => $q->whereIn('customer_id', $request->customers)
    //         )
    //         ->groupBy('customer_id')
    //         ->get()
    //         ->keyBy('customer_id');

    //     // dd($customerTotals);



    //     if ($sales->isEmpty()) {
    //         return back()->with('error', 'No sales found for selected filters.');
    //     }
    //     return view('reports.truck_sale_register_print_view', compact('salesByCustomer', 'customerTotals'));
    // }

    public function truckSaleRegisterPrintView(Request $request)
    {
        $request->validate([
            'date'        => 'nullable|date',
            'truck_id'    => 'nullable|integer|exists:good_receipts_truck,id_truck',
            'customers'   => 'nullable|array',
            'customers.*' => 'integer|exists:customers,id_customers',
        ]);

        // ===== SALES QUERY =====
        $query = Sale::with([
            'salesItemsDetails.goodReceiptDetail.goodReceipt.supplier',
            'salesItemsDetails.goodReceiptDetail.goodReceipt.goodReceiptTruck',
            'salesItemsDetails.productChildCate',
            'customer',
        ]);

        if ($request->date) {
            $query->whereDate('date', $request->date);
        }

        if ($request->truck_id) {
            $query->whereHas('salesItemsDetails.goodReceiptDetail.goodReceipt', function ($q) use ($request) {
                $q->where('good_receipts_truck_id', $request->truck_id);
            });
        }

        if ($request->has('customers') && is_array($request->customers)) {
            $query->whereIn('customer_id', $request->customers);
        }

        $sales = $query->get();

        if ($sales->isEmpty()) {
            return back()->with('error', 'No sales found for selected filters.');
        }

        // ===== GROUP BY CUSTOMER =====
        $salesByCustomer = $sales->groupBy('customer_id');

        // ===== CUSTOMER TOTALS (paid & balance from sales) =====
        $customerTotals = Sale::select(
            'customer_id',
            DB::raw("SUM(CAST(REPLACE(paid, ',', '') AS DECIMAL(15,2))) as total_paid"),
            DB::raw("SUM(CAST(REPLACE(balance, ',', '') AS DECIMAL(15,2))) as total_balance")
        )
            ->when($request->date, fn($q) => $q->whereDate('date', $request->date))
            ->when($request->truck_id, fn($q) => $q->whereHas(
                'salesItemsDetails.goodReceiptDetail.goodReceipt',
                fn($q2) => $q2->where('good_receipts_truck_id', $request->truck_id)
            ))
            ->when(
                $request->has('customers') && is_array($request->customers),
                fn($q) => $q->whereIn('customer_id', $request->customers)
            )
            ->groupBy('customer_id')
            ->get()
            ->keyBy('customer_id');

        // ===== CUSTOMER CREDITS FROM VOUCHERS =====
        $customerIds = $sales->pluck('customer_id')->unique()->values()->toArray();

        $customerCredits = DB::table('vouchers')
            ->selectRaw('
            jt.adaegi_khata AS customer_id,
            SUM(jt.credit_amount) AS total_credit
        ')
            ->join(DB::raw("
            JSON_TABLE(
                vouchers.adaegi_array,
                '\$[*]' COLUMNS (
                    adaegi_khata INT PATH '\$.adaegi_khata',
                    credit_amount DECIMAL(15,2) PATH '\$.credit_amount'
                )
            ) AS jt
        "), DB::raw('1'), DB::raw('1'))
            ->whereIn('jt.adaegi_khata', $customerIds)
            ->groupBy('jt.adaegi_khata')
            ->pluck('total_credit', 'customer_id');



        // ===== TRUCK & SUPPLIER =====
        $firstItem   = $sales->first()?->salesItemsDetails->first();
        $goodReceipt = $firstItem?->goodReceiptDetail?->goodReceipt;
        $truckNumber = $goodReceipt?->goodReceiptTruck?->truck_no ?? '—';
        $supplier    = $goodReceipt?->supplier;

        return view('reports.truck_sale_register_print_view', compact(
            'salesByCustomer',
            'customerTotals',
            'customerCredits',
            'truckNumber',
            'supplier'
        ));
    }
    // Ledger Report

    public function generalLedger(Request $request)
    {

        $accounts  = ControlAccount::select('id_control_account', 'control_account_name')->get();
        return view('reports.general_ledger', compact('accounts'));
    }
    public function generalLedgerPrintView(Request $request)
    {

        $request->validate([
            'date' => 'nullable|date',
            'customers' => 'nullable|array',
            'customers.*' => 'integer|exists:customers,id_customers',
        ]);
        $query = Sale::with(['salesItemsDetails', 'customer']);


        if ($request->date) {
            $query->whereDate('date', $request->date);
        }


        if ($request->has('customers') && is_array($request->customers)) {
            $query->whereIn('customer_id', $request->customers);
        }



        $sales = $query->get();

        // $salesByCustomer = $sales->groupBy('customer_id');

        $customerTotals = Sale::select(
            'customer_id',
            DB::raw("SUM(CAST(REPLACE(paid, ',', '') AS DECIMAL(15,2))) as total_paid"),
            DB::raw("SUM(CAST(REPLACE(balance, ',', '') AS DECIMAL(15,2))) as total_balance")
        )
            ->when($request->date, fn($q) => $q->whereDate('date', $request->date))
            ->when(
                $request->has('customers') && is_array($request->customers),
                fn($q) => $q->whereIn('customer_id', $request->customers)
            )
            ->groupBy('customer_id')
            ->get()
            ->keyBy('customer_id');

        // dd($customerTotals);



        if ($sales->isEmpty()) {
            return back()->with('error', 'No sales found for selected filters.');
        }
        return view('reports.general_ledger_print_view', compact('sales', 'customerTotals'));
    }
    // Stock In Hand Register Report

    public function stockInHandRegister(Request $request)
    {
        $suppliers  = Suppliers::all();
        $productCateMain = ProductsCategoryMain::all();
        return view('reports.stock_in_hand_register', compact('suppliers', 'productCateMain'));
    }
    public function stockInHandRegisterPrintView(Request $request)
    {

        $request->validate([
            'date' => 'nullable|date',
            'suppliers' => 'nullable|array',
            'suppliers.*' => 'integer|exists:suppliers,id_supplier',
        ]);

        $query = GoodReceiptDetail::with(['goodReceipt.supplier', 'goodReceiptTruck', 'productChildCate', 'salesItemDetail']);

        if ($request->from_date) {
            $query->whereHas('goodReceipt', function ($q) use ($request) {
                $q->whereBetween('date', [
                    $request->from_date,
                    $request->to_date
                ]);
            });
        }

        if ($request->has('suppliers') && is_array($request->suppliers)) {
            $query->whereHas('goodReceipt', function ($q) use ($request) {
                $q->whereIn('supplier_id', $request->suppliers);
            });
        }

        if ($request->productMainCate) {
            $query->where('pro_cate_main_id', $request->productMainCate);
        }

        if ($request->productParentCate) {
            $query->where('pro_cate_parent_id', $request->productParentCate);
        }

        if ($request->productChildCate) {
            $query->where('pro_cate_child_id', $request->productChildCate);
        }


        $stockHand = $query->get();


        if ($stockHand->isEmpty()) {
            return back()->with('error', 'No sales found for selected filters.');
        }
        return view('reports.stock_in_hand_register_print_view', compact('stockHand'));
    }
}
