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



    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();

    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 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(CASE WHEN date = '$today' THEN CAST(REPLACE(balance, ',', '') AS DECIMAL(15,2)) ELSE 0 END) AS today_balance"),
            DB::raw("SUM(CASE WHEN date = '$yesterday' 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();

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

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