<?php

namespace App\Http\Controllers;

use App\Models\StoreIssueRequisition;
use App\Models\ProductVariation;
use App\Models\ReceiptVoucher;
use App\Models\LeftOverNote;
use App\Models\ProductGroup;
use App\Models\InvoiceItem;
use App\Models\CreditNote;
use App\Models\Invoice;
use App\Models\Party;
use App\Models\Batch;
use App\Models\GrnItemBatch;
use App\Models\Category;
use App\Models\ChartOfAccount;
use App\Models\CreditNoteItem;
use App\Models\GoodsReceivingItem;
use App\Models\LeftOverNoteItem;
use App\Models\PaymentVoucherItem;
use App\Models\ReceiptVoucherItem;
use App\Models\RejectionReturnNote;
use App\Models\RawMaterialItem;
use App\Models\RejectionReturnNoteItem;
use App\Models\StoreIssueRequisitionItem;
use App\Models\SubChartOfAccount;
use App\Models\Stock;
use App\Services\FinishedGoodsReceivingService;
use App\Services\StoreIssueRequisitionService;
use App\Services\ProductVariationService;
use App\Services\RawMaterialItemService;
use App\Services\FranchiseService;
use App\Services\CountryService;
use App\Services\InvoiceService;
use App\Services\StockService;
use App\Services\BatchService;
use App\Services\PartyService;
use Illuminate\Http\Request;
use Carbon\Carbon;

class ReportController extends Controller
{
    protected $finishedGoodsReceivingService;
    protected $storeIssueRequisitionService;
    protected $productVariationService;
    protected $rawMaterialItemService;
    protected $franchiseService;
    protected $invoiceService;
    protected $countryService;
    protected $stockService;
    protected $batchService;
    protected $partyService;

    public function __construct(
        StockService $stockService,
        InvoiceService $invoiceService,
        FinishedGoodsReceivingService $finishedGoodsReceivingService,
        StoreIssueRequisitionService $storeIssueRequisitionService,
        ProductVariationService $productVariationService,
        RawMaterialItemService $rawMaterialItemService,
        FranchiseService $franchiseService,
        CountryService $countryService,
        PartyService $partyService,
        BatchService $batchService,
        )
    {
        $this->finishedGoodsReceivingService = $finishedGoodsReceivingService;
        $this->storeIssueRequisitionService = $storeIssueRequisitionService;
        $this->productVariationService = $productVariationService;
        $this->rawMaterialItemService = $rawMaterialItemService;
        $this->franchiseService = $franchiseService;
        $this->invoiceService = $invoiceService;
        $this->countryService = $countryService;
        $this->stockService = $stockService;
        $this->batchService = $batchService;
        $this->partyService = $partyService;
    }

    public function balanceReport(Request $request)
    {
        $tillDate = $request->input('till_date');
        $stocks = $this->stockService->currentBalanceReport($tillDate);
        $categories = Category::all();
        $selectedCategoryId = null;
        $selectedCategoryName = null;
        return view('admin.reports.balance-report', compact('stocks', 'categories', 'selectedCategoryId', 'selectedCategoryName', 'tillDate'));
    }

    public function purchaseItems(Request $request)
    {
        // $from_date = $request->has('from_date') ? $request->input('from_date') : now()->subDays(30)->format('Y-m-d');
        // $to_date = $request->has('to_date') ? $request->input('to_date') : now()->format('Y-m-d');
        $status = $request->has('status') ? $request->input('status') : null;
        $days_filter = $request->has('days_filter') ? $request->input('days_filter') : null;
        $urgent = $request->has('urgent') ? $request->input('urgent') : null;
        $type = $request->has('type') ? $request->input('type') : null;
        $purchaseItems = $this->invoiceService->getOnlyPurchaseItems($status);
        if ($type === 'purchase-order') {
            $purchaseItems = $purchaseItems->sortBy(fn($item) => $item->invoice->code);
        }
        elseif ($type === 'items-name') {
            $purchaseItems = $purchaseItems->sortBy('title');
        }
        elseif ($type === 'vendor') {
            $purchaseItems = $purchaseItems->sortBy(fn($item) => $item->invoice->party->name);
        }
        elseif ($type === 'req-date') {
            $purchaseItems = $purchaseItems->sortBy(fn($item) => $item->invoice->required_date);
        }
        elseif ($type === 'days-remaining') {
            $purchaseItems = $purchaseItems->sortBy(fn($item) => now()->diffInDays(\Carbon\Carbon::parse($item->invoice->required_date), false));
        }

        if ($days_filter === 'overdue') {
            $purchaseItems = $purchaseItems->filter(function ($item) {
                $requiredDate = Carbon::parse($item->invoice->required_date)->startOfDay();
                $remainingDays = now()->startOfDay()->diffInDays($requiredDate, false);
                return $remainingDays < 0;
            });
        }
        elseif ($days_filter === 'remaining') {
            $purchaseItems = $purchaseItems->filter(function ($item) {
                $requiredDate = Carbon::parse($item->invoice->required_date)->startOfDay();
                $remainingDays = now()->startOfDay()->diffInDays($requiredDate, false);
                return $remainingDays >= 0;
            });
        }

        if ($urgent === '1') {
            $purchaseItems = $purchaseItems->filter(function ($item) {
                return $item->invoice->is_urgent == 1;
            });
        }
        elseif ($urgent === '0') {
            $purchaseItems = $purchaseItems->filter(function ($item) {
                return $item->invoice->is_urgent == 0;
            });
        }

        // dd($purchaseItems->toArray());
        return view('admin.reports.purchase-items', compact('purchaseItems', 'status', 'days_filter', 'type', 'urgent'));
    }

    public function purchaseOrderAppraisal(Request $request)
    {
        $from_date = $request->has('from_date') ? $request->input('from_date') : now()->subDays(30)->format('Y-m-d');
        $to_date = $request->has('to_date') ? $request->input('to_date') : now()->format('Y-m-d');
        $status = $request->has('status') ? $request->input('status') : null;
        $purchaseRequisitions = $this->invoiceService->getOnlyPurchaseRequisitions($from_date, $to_date, $status);
        return view('admin.reports.purchase-requisitions', compact('purchaseRequisitions', 'from_date', 'to_date', 'status'));
    }

    public function requirementReport(Request $request)
    {
        $min_level = $request->has('min_level') ?? false;
        // $max_level = $request->has('max_level') ?? false;
        $category_id = $request->has('category_id') ? $request->input('category_id') : null;

        $categories = Category::all();

        $requirementReport = $this->invoiceService->getFilterRequirementForInvoice($request);
        return view('admin.reports.requirement-report', compact('requirementReport', 'categories', 'min_level', 'category_id'));
    }

    public function productionReport(Request $request)
    {
        $year = $request->has('year') ? $request->input('year') : now()->year;
        $productionReport = $this->finishedGoodsReceivingService->getMonthlyFinishedProductReport($year);
        return view('admin.reports.production-report', compact('productionReport', 'year'));
    }

    public function consumptionReport(Request $request)
    {
   
        $from_date = $request->has('from_date') ? $request->input('from_date') : now()->subDays(30)->format('Y-m-d');
        $to_date = $request->has('to_date') ? $request->input('to_date') : now()->format('Y-m-d');
        $raw_material_item_id = $request->input('category_item_id');
        $selectedCategoryId = $request->input('balance_category_id');
        $categories = Category::all();
        $consumptionReport = $this->storeIssueRequisitionService->getConsumptionReport($from_date, $to_date, $raw_material_item_id);
        return view('admin.reports.consumption-report', compact('consumptionReport', 'from_date', 'to_date', 'raw_material_item_id', 'categories', 'selectedCategoryId'));
    }

    public function batchBalanceReport()
    {
        $batchBalanceReport = $this->batchService->getBatchesReport();
        $batchBalanceReport = array_filter($batchBalanceReport, function ($product) {
            return $product['quantity'] > 0 ||
            collect($product['batches'])->contains(function ($batch) {
                    return $batch['batch_balance'] > 0;
                }
                );
            });
        return view('admin.reports.batch-report', compact('batchBalanceReport'));
    }

    // public function batchTrackReport($id)
    // {
    //     $batchTrackReport = $this->batchService->getBatchTrackReport($id);
    //     return view('admin.reports.batch-track-report',compact('batchTrackReport'));
    // }

    public function costingVoucher($id)
    {
        $costingVoucher = $this->batchService->getCostingVoucher($id);
        $batch = Batch::findOrFail($id);
        return view('admin.reports.costing-voucher', compact('costingVoucher', 'batch'));
    }

    public function newCostingVoucher($type)
    {
        $batches = Batch::orderByDesc('batch_no')->get();
        $data = [
            'batches' => $batches,
            'type' => $type
        ];
        return view('admin.reports.costing-voucher.index', $data);
    }

    public function generateCostingVoucher(Request $request)
    {
        if ($request->filled('batch_id')) {
            $batchId = $request->batch_id;

            if (!$batchId) {
                return response()->json(['success' => false, 'message' => 'Batch ID missing']);
            }
            $batch = Batch::with([
                'chart_of_account:id,code',
                'masterFormulation.mfItems.rawItem.chart_of_account', // Formulation items with item details
                'productionStoreIssueRequisitions.items.raw_material_item', // Actual issued items
                'leftOverNotes.items',
                'rejectionReturnNotes.items',
                'product',
            ])->find($batchId);

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

            if ($request->type == 'costing_report') {
                $formulationItems = $batch->masterFormulation->mfItems ?? collect([]);
                $batchProduction = $batch->actual_production ?? 0;
                $baseQty = $batch->masterFormulation->batch_size ?? 1;

                $reportItems = [];

                foreach ($formulationItems as $fItem) {
                    $itemId = $fItem->raw_material_item_id;
                    $itemName = $fItem->rawItem->name ?? 'N/A';
                    $itemCode = $fItem->rawItem->chart_of_account->code ?? '-';

                    // 1️⃣ Expected (Formulation)
                    $expectedQty = $fItem->quantity; // / $baseQty) * $batchProduction;
                    $expectedPrice = 0; // As per your note
                    $expectedAmt = 0;

                    // 2️⃣ Actual (From SIR)
                    $issuedQty = StoreIssueRequisitionItem::whereHas('store_issue_requisition', function ($q) use ($batchId) {
                        $q->where('batch_id', $batchId)->where('is_production', '1');
                    })->where('raw_material_item_id', $itemId)->sum('quantity');
                    // dd($issuedQty);

                    // 3️⃣ Leftover & Rejection adjustments
                    $leftOverQty = LeftOverNoteItem::whereHas('leftOverNote', function ($q) use ($batchId) {
                        $q->where('batch_id', $batchId);
                    })
                        // where('batch_id', $batchId)
                        ->where('raw_material_item_id', $itemId)
                        ->sum('quantity');

                    $rejectionQty = RejectionReturnNoteItem::whereHas('rejectionReturnNote', function ($q) use ($batchId) {
                        $q->where('batch_id', $batchId);
                    })
                        // where('batch_id', $batchId)
                        ->where('raw_material_item_id', $itemId)
                        ->sum('quantity');

                    $actualQty = $issuedQty - $leftOverQty + $rejectionQty;

                    // Rate from latest GRN (if available)
                    $latestGRNRate = GoodsReceivingItem::where('raw_material_item_id', $itemId)
                        ->orderByDesc('id')
                        ->value('unit_price') ?? 0;

                    $actualPrice = $latestGRNRate;
                    $actualAmt = $actualQty * $actualPrice;

                    // 4️⃣ Discrepancy
                    $discQty = $actualQty - $expectedQty;
                    $discQty = $discQty > 0 ? '+' . $discQty : $discQty;
                    $discPrice = $actualPrice - $expectedPrice;
                    $discAmt = ($discQty * $discPrice);

                    $reportItems[] = [
                        'code' => $itemCode,
                        'name' => $itemName,

                        'expected_qty' => ($expectedQty),
                        'expected_price' => ($expectedPrice),
                        'expected_amt' => ($expectedAmt),

                        'actual_qty' => ($actualQty),
                        'actual_price' => ($actualPrice),
                        'actual_amt' => ($actualAmt),

                        'disc_qty' => ($discQty),
                        'disc_price' => ($discPrice),
                        'disc_amt' => ($discAmt),
                    ];
                }

                $data = [
                    'batch' => $batch,
                    'reportItems' => $reportItems,
                    'type' => $request->type,
                ];

                // Render partial view
                $view = view('admin.reports.costing-voucher.table', $data)->render();

                return response()->json(['success' => true, 'view' => $view]);
            }
            elseif ($request->type == 'track_report') {
                // Fetch and key by created_at
                $sirs = StoreIssueRequisition::where('is_production', 1)
                    ->where('batch_id', $batchId)
                    ->get()
                    ->keyBy('created_at')
                    ->toArray();

                $rrns = RejectionReturnNote::where('is_production', 1)
                    ->where('batch_id', $batchId)
                    ->get()
                    ->keyBy('created_at')
                    ->toArray();

                $lons = LeftOverNote::where('batch_id', $batchId)
                    ->get()
                    ->keyBy('created_at')
                    ->toArray();

                // Merge all three arrays
                $allDocuments = array_merge($sirs, $rrns, $lons);

                // Sort by created_at key (ascending)
                ksort($allDocuments);

                // dd($sirs, $rrns, $lons, $allDocuments);

                $data = [
                    'batch' => $batch,
                    'type' => $request->type,
                    'allDocuments' => $allDocuments,
                    // 'batchBalanceReport' => $batchTrackReport,
                ];

                // Render partial view
                $view = view('admin.reports.costing-voucher.track-table', $data)->render();

                return response()->json(['success' => true, 'view' => $view]);
            }
            elseif ($request->type == 'costing_voucher') {
                // $sirs = StoreIssueRequisitionItem::whereHas('store_issue_requisition', function ($q) use ($batchId) {
                //     $q->where('batch_id', $batchId)->where('is_production', 1);
                // })
                //     ->with('rawMaterialItem', 'store_issue_requisition')
                //     ->get()
                //     ->keyBy('created_at')
                //     ->toArray();

                $sirs = StoreIssueRequisition::where('is_production', 1)
                    ->where('batch_id', $batchId)
                    ->with('items.rawMaterialItem')
                    ->get()
                    ->keyBy('created_at')
                    ->toArray();

                $lons = LeftOverNote::where('batch_id', $batchId)
                    ->with('items.rawMaterialItem')
                    ->get()
                    ->keyBy('created_at')
                    ->toArray();

                // Merge all three arrays
                $allDocuments = array_merge($sirs, $lons);

                // Sort by created_at key (ascending)
                ksort($allDocuments);

                $data = [
                    'batch' => $batch,
                    'type' => $request->type,
                    'allDocuments' => $allDocuments,
                ];

                $view = view('admin.reports.costing-voucher.costing-report', $data)->render();

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

            return response()->json([
                'success' => false,
                'message' => 'Invalid Report Type !'
            ]);
        }
        else {
            return response()->json([
                'success' => false,
                'message' => 'Invalid Request !'
            ]);
        }
    }

    public function stockBinCard(Request $request)
    {
        $productParam = $request->input('product');
        $from_date = $request->has('from_date') ? $request->input('from_date') : now()->subDays(30)->format('Y-m-d');
        $to_date = $request->has('to_date') ? $request->input('to_date') : now()->format('Y-m-d');
        $rawMaterials = $this->rawMaterialItemService->getAllRawMaterialItems();
        $finalProducts = $this->productVariationService->getAllProductVariations();
        $stockBinCards = null;
        $productType = null;
        $product = null;
        $product_id = $request->product_id ?? null;
        $categories = Category::select('id', 'name', 'prefix', )
            ->when(!auth()->user()->hasRole('Superadmin'), function ($q) {
            $q->where('company_id', auth()->user()->employee->company_id);
        })
            ->orderBy('name')
            ->get();

        $selectedCategoryId = $request->category_id ?? null;
        $type = $request->type ?? null;
        if ($type && $selectedCategoryId && !$product_id) {
            $stockBinCards = $this->stockService->stockBinCardByCategory($selectedCategoryId, $type, $from_date, $to_date);
        }
        elseif ($type && $selectedCategoryId && $product_id) {
            $stockBinCards = $this->stockService->stockBinCardByCategory($selectedCategoryId, $type, $from_date, $to_date, $product_id);
        }

        // if (str_starts_with($productParam, 'raw_')) {
        //     $productType = 0;
        //     $id = str_replace('raw_', '', $productParam);
        //     $product = $this->rawMaterialItemService->getRawMaterialItemById($id);
        //     $stockBinCards = $this->stockService->stockBinCard(null, $id, null, $from_date, $to_date);
        // } elseif (str_starts_with($productParam, 'product_')) {
        //     $productType = 1;
        //     $id = str_replace('product_', '', $productParam);
        //     $product = $this->productVariationService->getProductVariationById($id);
        //     $stockBinCards = $this->stockService->stockBinCard(null, null, $id, $from_date, $to_date);
        // }

        return view('admin.reports.bin-card', compact('stockBinCards', 'product', 'from_date', 'to_date', 'rawMaterials', 'finalProducts', 'productType', 'categories', 'type', 'selectedCategoryId'));
    }

    public function batchReport(Request $request)
    {
        $products = $this->productVariationService->getAllProductVariations();
        $finalProduct = $request->has('product')
            ? $this->productVariationService->getProductVariationById($request->input('product'))
            : $this->productVariationService->getAllProductVariations()->first();

        $from_date = $request->has('from_date') ? $request->input('from_date') : now()->subDays(30)->format('Y-m-d');
        $to_date = $request->has('to_date') ? $request->input('to_date') : now()->format('Y-m-d');

        $array = [];

        foreach ($finalProduct->product->batches as $key => $batch) {
            // $query = StoreIssueRequisition::where('batch_id', $batch->id)->where('product_id', $finalProduct->product_id)->whereHas('items');
            $result = StoreIssueRequisition::where('batch_id', $batch->id)
                // ->where('product_id', $finalProduct->product_id)
                ->get();

            $totalItems = $result->sum(function ($requisition) {
                return $requisition->items->sum('total'); // Assuming 'total' is the field in the 'items' table
            });
            $totalItemRows = $result->sum(function ($requisition) {
                return $requisition->items->count(); // Counting rows in the 'items' relationship
            });
            $leftover = LeftOverNote::where('batch_id', $batch->id)
                ->where('product_id', $finalProduct->product_id)
                ->get();
            $totalleftover = $leftover->sum(function ($requisition) {
                return $requisition->items->sum('total'); // Assuming 'total' is the field in the 'items' table
            });

            $temp = $totalItems - $totalleftover;
            if ($temp > 0 && $totalItemRows > 0) {
                $avg = $temp / $totalItemRows; // Perform division if both are greater than 0
            }
            else {
                $avg = 0; // Default or alternative value when the condition is not met
            }


            $array[] = [
                "batch_id" => $batch->id,
                "product_id" => $finalProduct->product_id,
                "total" => $totalItems,
                "rows" => $totalItemRows,
                "leftover" => $totalleftover,
                "avgCost" => $avg,
            ];
        }

        $items = $finalProduct->product->batches->toArray();

        return view('admin.reports.product-variant-batch-report', compact('items', 'from_date', 'to_date', 'array', 'products', 'finalProduct'));
    }

    public function financialLedger(Request $request)
    {
        $franchisers = $request->has('franchiser') && $request->input('franchiser')
            ? collect([$this->franchiseService->getFranchiseById($request->input('franchiser'))])
            : $this->franchiseService->getAllFranchises();

        $selectedFY = $request->input('financial_year');

        if ($selectedFY) {
            $years = explode('-', $selectedFY);
            if (count($years) === 2) {
                $from_date = $years[0] . '-07-01';
                $to_date = $years[1] . '-06-30';
            }
        }
        else {
            $from_date = $request->has('from_date')
                ? $request->input('from_date')
                : now()->subDays(30)->format('Y-m-d');
            $to_date = $request->has('to_date')
                ? $request->input('to_date')
                : now()->format('Y-m-d');
        }

        $parties = $this->franchiseService->getAllFranchises();

        // Get COA parameters for view (not used for filtering)
        $coa_id = $request->coa_id ?? null;
        $sub_coa_id = $request->sub_coa_id ?? null;

        $report_type = $request->input('report_type', 'customer'); // 'customer' or 'product'

        $financial_ledger = [];

        if ($report_type === 'product') {
            $productParam = $request->input('product_id'); // From category filter dropdown
            if (!$productParam) {
                $productParam = $request->input('product'); // Fallback to all products dropdown
            }

            $productType = null;
            $productId = null;
            $rawMaterialItemId = null;
            $productVariationId = null;

            if (str_starts_with($productParam, 'raw_')) {
                $productType = 0;
                $rawMaterialItemId = str_replace('raw_', '', $productParam);
                $productId = $rawMaterialItemId;
            }

            $type = 'raw_items';
            $selectedCategoryId = $request->input('category_id');

            if ($type && $selectedCategoryId) {
                // Fetch stock entries via Category Service
                $stockEntries = $this->stockService->stockBinCardByCategory($selectedCategoryId, $type, $from_date, $to_date, $productId);
            }
            elseif ($productParam) {
                // Fetch stock entries via general product simulated ledger (Fallback)
                $stockEntries = $this->stockService->stockBinCard($productId, $rawMaterialItemId, $productVariationId, $from_date, $to_date);
            }
            else {
                $stockEntries = [];
            }

            if (!empty($stockEntries)) {
                foreach ($stockEntries as $entry) {
                    // Map stock entries to ledger format
                    $financial_ledger[] = [
                        'vr' => $entry['doc'],
                        'payment_method' => null,
                        'type' => $entry['type'] == 'received' ? 'debit' : 'credit', // Inward is Debit, Outward is Credit
                        'particular' => $entry['bill_no'] . '-' . $entry['item_name'] ?? 'Stock Update',
                        'amount' => $entry['total'],
                        'balance' => $entry['balance_total'],
                        'created_at' => $entry['date'],
                        'franchiser_id' => null,
                        'franchiser_name' => null,
                    ];
                }
            }

        }
        else {
            // Original Customer/Franchiser logic - optimized

            // Pre-fetch all needed data outside the loop to avoid N+1 issues
            $franchiserIds = $franchisers->pluck('id')->toArray();

            $allCommercialInvoices = Invoice::where('type', 'sale')
                ->whereIn('party_id', $franchiserIds)
                ->whereBetween('created_at', [$from_date, $to_date])->get()->groupBy('party_id');

            $allCreditNotes = CreditNote::whereIn('party_id', $franchiserIds)
                ->whereBetween('created_at', [$from_date, $to_date])->get()->groupBy('party_id');

            $allReceiptVouchers = ReceiptVoucher::whereIn('party_id', $franchiserIds)
                ->whereBetween('created_at', [$from_date, $to_date])->get()->groupBy('party_id');

            foreach ($franchisers as $franchiser) {
                $franchiser_transactions = [];
                $currentBalance = $franchiser->getBalanceBeforeDate($from_date);

                // Opening Balance
                $financial_ledger[] = [
                    'vr' => 'OB0000',
                    'payment_method' => null,
                    'type' => 'balance',
                    'particular' => 'Opening Balance - ' . $franchiser->name,
                    'amount' => $currentBalance,
                    'balance' => $currentBalance, // We'll recalculate this properly below
                    'created_at' => Carbon::parse($from_date)->format('d M, Y H:i:s'),
                    'is_opening' => true,
                    'franchiser_id' => $franchiser->id,
                    'franchiser_name' => $franchiser->code . ' ' . $franchiser->name,
                ];

                // Commercial Invoices
                $commercial_invoices = $allCommercialInvoices->get($franchiser->id, collect());
                foreach ($commercial_invoices as $invoice) {
                    $franchiser_transactions[] = [
                        'vr' => $invoice->code,
                        'payment_method' => null,
                        'type' => 'debit',
                        'particular' => $invoice->party->name,
                        'amount' => $invoice->total_amount,
                        'created_at' => clone $invoice->created_at,
                        'is_opening' => false,
                        'franchiser_id' => $franchiser->id,
                        'franchiser_name' => $franchiser->code . ' ' . $franchiser->name,
                        'operation' => 'add', // Debits increase customer balance
                    ];
                }

                // Credit Notes
                $general_invoices = $allCreditNotes->get($franchiser->id, collect());
                foreach ($general_invoices as $invoice) {
                    $franchiser_transactions[] = [
                        'vr' => $invoice->code,
                        'payment_method' => null,
                        'type' => 'credit', // Credit notes historically decrease balance
                        'particular' => $invoice->party->name,
                        'amount' => $invoice->total,
                        'created_at' => clone $invoice->created_at,
                        'is_opening' => false,
                        'franchiser_id' => $franchiser->id,
                        'franchiser_name' => $franchiser->code . ' ' . $franchiser->name,
                        'operation' => 'subtract',
                    ];
                }

                // Receipt Vouchers
                $credit_vouchers = $allReceiptVouchers->get($franchiser->id, collect());
                foreach ($credit_vouchers as $voucher) {
                    $franchiser_transactions[] = [
                        'vr' => $voucher->code,
                        'payment_method' => $voucher->payment_method,
                        'type' => 'credit',
                        'particular' => $voucher->party->name,
                        'amount' => $voucher->total,
                        'created_at' => clone $voucher->created_at,
                        'is_opening' => false,
                        'franchiser_id' => $franchiser->id,
                        'franchiser_name' => $franchiser->code . ' ' . $franchiser->name,
                        'operation' => 'subtract', // Receipts decrease customer balance
                    ];
                }

                // Sort transactions for this franchiser chronologically
                usort($franchiser_transactions, function ($a, $b) {
                    return $a['created_at']->timestamp - $b['created_at']->timestamp;
                });

                // Now calculate the running balance sequentially in chronological order
                foreach ($franchiser_transactions as $transaction) {
                    if ($transaction['operation'] === 'add') {
                        $currentBalance += $transaction['amount'];
                    }
                    else {
                        $currentBalance -= $transaction['amount'];
                    }

                    $transaction['balance'] = $currentBalance;
                    $transaction['created_at'] = $transaction['created_at']->format('d M, Y'); // Format for view
                    // Remove internal fields
                    unset($transaction['operation']);

                    $financial_ledger[] = $transaction;
                }
            }
        }

        if ($report_type === 'customer') {
            usort($financial_ledger, function ($a, $b) {
                // Opening balance entries should ALWAYS be at the BEGINNING (Top)
                $aIsBalance = ($a['is_opening'] ?? false);
                $bIsBalance = ($b['is_opening'] ?? false);

                if ($aIsBalance && !$bIsBalance) {
                    return -1; // $a comes before $b
                }
                if (!$aIsBalance && $bIsBalance) {
                    return 1; // $b comes before $a
                }

                // For non-opening balances, rely on the pre-sorted chronological order we established above
                // or if we needed to sort everything here globally we would but it breaks running balance sequences 
                // if we don't group by franchiser.
                return 0;
            });
        }

        $coas = ChartOfAccount::orderBy('code')->get();
        $sub_coas = SubChartOfAccount::orderBy('id')->get()->groupBy('chart_of_account_id');

        // Get selected franchiser ID for frontend
        $selected_franchiser_id = $request->input('franchiser');

        // Set filtered_parties to all parties (no filtering in backend)
        $filtered_parties = $parties;

        // For backward compatibility, set $franchiser to first one if single, or null if multiple
        $franchiser = $franchisers->count() === 1 ? $franchisers->first() : null;

        // Product specific data
        $rawMaterials = $this->rawMaterialItemService->getAllRawMaterialItems();
        $selected_product = $request->input('product_id');
        if (!$selected_product) {
            $selected_product = $request->input('product');
        }

        $type = $request->input('type');
        $selectedCategoryId = $request->input('category_id');

        $categories = Category::select('id', 'name', 'prefix', )
            ->when(!auth()->user()->hasRole('Superadmin'), function ($q) {
            $q->where('company_id', auth()->user()->employee->company_id);
        })
            ->orderBy('name')
            ->get();

        return view('admin.reports.financial-ledger', compact(
            'franchiser',
            'franchisers',
            'from_date',
            'to_date',
            'financial_ledger',
            'parties',
            'filtered_parties',
            'coas',
            'sub_coas',
            'coa_id',
            'sub_coa_id',
            'selected_franchiser_id',
            'selectedFY',
            'report_type',
            'rawMaterials',
            'selected_product',
            'categories',
            'type',
            'selectedCategoryId'
        ));
    }

    public function trialBalance(Request $request)
    {
        $data = [];
        return view('admin.reports.trial-balance.index', $data);
    }

    public function generateTrialBalance(Request $request)
    {
        // -----------------------------------------------------------
        // 1. PREP INPUT
        // -----------------------------------------------------------
        $startDate = Carbon::parse($request->start_date);
        $endDate = Carbon::parse($request->end_date);

        // All COA IDs
        $allCoaIds = ChartOfAccount::pluck('id')->toArray();

        // Prepare empty dataset for safe usage
        $openingDebitCreditNotes = [];
        $openingCreditReceipts = [];
        $openingDebitInvoices = [];

        $activityDebitCreditNotes = [];
        $activityCreditReceipts = [];
        $activityDebitInvoices = [];

        // -----------------------------------------------------------
        // 2. LOAD ALL TRANSACTIONS (using parent created_at dates)
        // -----------------------------------------------------------

        // ---------- CREDIT NOTES (Debit = dr , Credit = cr) ----------
        $creditNotes = CreditNoteItem::with('creditNote')
            ->whereHas('creditNote', function ($q) use ($endDate) {
            $q->where('created_at', '<', $endDate); // For opening + activity
        })
            ->get();

        // dd($creditNotes->toArray());

        // Split into opening & activity
        foreach ($creditNotes as $item) {
            $coaId = $item->chart_of_account_id;
            $date = $item->creditNote->created_at;

            // Initialize arrays if not set
            $openingDebitCreditNotes[$coaId] = $openingDebitCreditNotes[$coaId] ?? collect([]);
            $activityDebitCreditNotes[$coaId] = $activityDebitCreditNotes[$coaId] ?? collect([]);

            if ($date < $startDate) {
                // Opening
                $openingDebitCreditNotes[$coaId]->push($item);
            }
            else {
                // Activity
                $activityDebitCreditNotes[$coaId]->push($item);
            }
        }

        // dd($openingDebitCreditNotes, $activityDebitCreditNotes);

        // ---------- RECEIPT VOUCHERS (Credit side) ----------
        $receiptItems = ReceiptVoucherItem::with('receiptVoucher')
            ->whereHas('receiptVoucher', function ($q) use ($endDate) {
            $q->where('created_at', '<', $endDate);
        })
            ->get();

        foreach ($receiptItems as $item) {
            $coaId = $item->chart_of_account_id;
            $date = $item->receiptVoucher->created_at;

            $openingCreditReceipts[$coaId] = $openingCreditReceipts[$coaId] ?? collect([]);
            $activityCreditReceipts[$coaId] = $activityCreditReceipts[$coaId] ?? collect([]);

            if ($date < $startDate) {
                $openingCreditReceipts[$coaId]->push($item);
            }
            else {
                $activityCreditReceipts[$coaId]->push($item);
            }
        }


        // ---------- PAYMENT VOUCHERS (Debit side) ----------
        $paymentItems = PaymentVoucherItem::with('paymentVoucher')
            ->whereHas('paymentVoucher', function ($q) use ($endDate) {
            $q->where('created_at', '<', $endDate);
        })
            ->get();

        foreach ($paymentItems as $item) {
            $coaId = $item->chart_of_account_id;
            $date = $item->paymentVoucher->created_at;

            $openingDebitInvoices[$coaId] = $openingDebitInvoices[$coaId] ?? collect([]);
            $activityDebitInvoices[$coaId] = $activityDebitInvoices[$coaId] ?? collect([]);

            if ($date < $startDate) {
                $openingDebitInvoices[$coaId]->push($item);
            }
            else {
                $activityDebitInvoices[$coaId]->push($item);
            }
        }

        // -----------------------------------------------------------
        // 3. BUILD TRIAL BALANCE ROWS
        // -----------------------------------------------------------
        $trialData = [];

        foreach ($allCoaIds as $coaId) {

            // ---------------- Opening ----------------
            $openingDebit =
                (array_key_exists($coaId, $openingDebitCreditNotes) ? $openingDebitCreditNotes[$coaId]->sum('debit') ?? 0 : 0) +
                (array_key_exists($coaId, $openingDebitInvoices) ? $openingDebitInvoices[$coaId]->sum('debit') ?? 0 : 0);

            $openingCredit =
                (array_key_exists($coaId, $openingCreditReceipts) ? $openingCreditReceipts[$coaId]->sum('credit') ?? 0 : 0);


            // ---------------- Activity ----------------
            $activityDebit =
                (array_key_exists($coaId, $activityDebitCreditNotes) ? $activityDebitCreditNotes[$coaId]->sum('debit') ?? 0 : 0) +
                (array_key_exists($coaId, $activityDebitInvoices) ? $activityDebitInvoices[$coaId]->sum('debit') ?? 0 : 0);

            $activityCredit =
                (array_key_exists($coaId, $activityCreditReceipts) ? $activityCreditReceipts[$coaId]->sum('credit') ?? 0 : 0);


            // ---------------- Closing ----------------
            $net = ($openingDebit + $activityDebit) - ($openingCredit + $activityCredit);

            $closingDebit = $net > 0 ? $net : 0;
            $closingCredit = $net < 0 ? abs($net) : 0;


            // COA Info
            $coa = ChartOfAccount::find($coaId);

            $trialData[] = [
                'coa_id' => $coaId,
                'coa_code' => $coa->code,
                'coa_desc' => $coa->particular,
                'opening_debit' => $openingDebit,
                'opening_credit' => $openingCredit,
                'activity_debit' => $activityDebit,
                'activity_credit' => $activityCredit,
                'closing_debit' => $closingDebit,
                'closing_credit' => $closingCredit,
            ];
        }

        // -----------------------------------------------------------
        // 4. RETURN FINAL TRIAL BALANCE
        // -----------------------------------------------------------
        // dd($trialData);
        $data = [
            'trialData' => $trialData,
            'startDate' => $startDate,
            'endDate' => $endDate,
            'level' => $request->level ?? 1,
        ];

        return response()->json([
            'success' => true,
            'view' => view('admin.reports.trial-balance.view', $data)->render(),
        ]);
    }

    public function customerAging(Request $request)
    {
        $party = $request->has('party')
            ? $this->partyService->getPartyById($request->input('party'))
            : $this->partyService->getAllParties()->first();

        $from_date = $request->has('from_date')
            ? $request->input('from_date')
            : now()->subDays(30)->format('Y-m-d');

        $to_date = $request->has('to_date')
            ? $request->input('to_date')
            : now()->format('Y-m-d');

        $invoices = Invoice::where('party_id', $party->id)->orderBy('payment_deadline', 'asc')->get();

        $partyList = $this->partyService->getAllParties();

        return view('admin.reports.customer-aging', compact('party', 'from_date', 'to_date', 'invoices', 'partyList'));
    }

    public function franchiserSales(Request $request)
    {
        $franchiser = $request->has('franchiser')
            ? $this->franchiseService->getFranchiseById($request->input('franchiser'))
            : $this->franchiseService->getAllFranchises()->first();

        $from_date = $request->has('from_date')
            ? $request->input('from_date')
            : now()->subDays(30)->format('Y-m-d');

        $to_date = $request->has('to_date')
            ? $request->input('to_date')
            : now()->format('Y-m-d');

        // Check if the date range is greater than 12 months
        $fromDate = Carbon::parse($from_date);
        $toDate = Carbon::parse($to_date);
        $monthDiff = $fromDate->diffInMonths($toDate);

        if ($monthDiff > 12) {
            return back()->withErrors(['date_range' => 'The date range cannot exceed 12 months.']);
        }

        $parties = $this->franchiseService->getAllFranchises();

        // Fetching the invoices
        $invoices = InvoiceItem::whereHas('invoice', function ($query) use ($franchiser, $from_date, $to_date) {
            $query->where('party_id', $franchiser->id)
                ->whereBetween('invoice_date', [$from_date, $to_date]);
        })->get();

        $product_group_sales = []; // To store the required data structure

        // Generate all months between from_date and to_date
        $all_months = [];
        $current_date = Carbon::parse($from_date);

        while ($current_date->lte($to_date)) {
            $all_months[] = $current_date->format('m-Y');
            $current_date->addMonth();
        }

        foreach ($invoices as $invoice) {
            // Get the month in the format MM-YYYY
            $month = Carbon::parse($invoice->invoice->invoice_date)->format('m-Y');
            $product_group_id = $invoice->product_group_id;
            $product_group_name = $invoice->productGroup->name ?? null;
            $quantity = $invoice->quantity;

            // Initialize the product_group_id if it doesn't exist in the product_group_sales array
            if (!isset($product_group_sales[$product_group_id])) {
                $product_group_sales[$product_group_id] = [
                    'product_group_name' => $product_group_name, // Store product group name
                    'monthly_quantity' => [] // Initialize the array for monthly quantities
                ];
            }

            // Check if the month already exists for this product group, if not add it
            $found = false;
            foreach ($product_group_sales[$product_group_id]['monthly_quantity'] as &$month_data) {
                if ($month_data['month'] == $month) {
                    // Add the quantity to the existing month for this product group
                    $month_data['quantity'] += $quantity;
                    $found = true;
                    break; // Exit the loop once the month is found
                }
            }

            // If the month was not found, add it to the monthly_quantity array
            if (!$found) {
                $product_group_sales[$product_group_id]['monthly_quantity'][] = [
                    'month' => $month,
                    'quantity' => $quantity
                ];
            }
        }

        // Ensure that every product group has data for all months in the range (even if quantity is 0)
        foreach ($product_group_sales as $product_group_id => &$sales_data) {
            // Sort monthly quantities by month in ascending order
            usort($sales_data['monthly_quantity'], function ($a, $b) {
                return strtotime($a['month']) - strtotime($b['month']);
            });

            // Fill missing months with 0 quantity
            foreach ($all_months as $month) {
                $month_found = false;
                foreach ($sales_data['monthly_quantity'] as &$month_data) {
                    if ($month_data['month'] == $month) {
                        $month_found = true;
                        break;
                    }
                }
                // If the month does not exist for this product group, add it with 0 quantity
                if (!$month_found) {
                    $sales_data['monthly_quantity'][] = [
                        'month' => $month,
                        'quantity' => 0
                    ];
                }
            }

            // Sort again after filling missing months to keep the order consistent
            usort($sales_data['monthly_quantity'], function ($a, $b) {
                return strtotime($a['month']) - strtotime($b['month']);
            });
        }

        return view('admin.reports.franchiser-sales', compact('franchiser', 'from_date', 'to_date', 'parties', 'all_months', 'product_group_sales'));
    }

    public function countryQuantitySales(Request $request)
    {
        $countries = $this->countryService->getAllCountries();

        $country = $request->has('country')
            ? $this->countryService->getCountryById($request->input('country'))
            : $this->countryService->getAllCountries()->first();

        $franchisers = Party::where('is_distributor', '1')
            ->where('country_id', $country->id)
            ->get();

        $from_date = $request->has('from_date')
            ? $request->input('from_date')
            : now()->subDays(30)->format('Y-m-d');

        $to_date = $request->has('to_date')
            ? $request->input('to_date')
            : now()->format('Y-m-d');

        // Check if the date range is greater than 12 months
        $fromDate = Carbon::parse($from_date);
        $toDate = Carbon::parse($to_date);
        $monthDiff = $fromDate->diffInMonths($toDate);

        if ($monthDiff > 12) {
            return back()->withErrors(['date_range' => 'The date range cannot exceed 12 months.']);
        }

        $franchiser_sales = []; // To store the required data structure
        $all_product_groups = []; // To store unique product groups

        foreach ($franchisers as $franchiser) {
            $invoices = InvoiceItem::whereHas('invoice', function ($query) use ($franchiser, $from_date, $to_date) {
                $query->where('party_id', $franchiser->id)
                    ->whereBetween('invoice_date', [$from_date, $to_date]);
            })->get();

            $product_group_sales = [];

            foreach ($invoices as $invoice) {
                $product_group_id = $invoice->product_group_id;
                $product_group_name = $invoice->productGroup->name ?? null;
                $quantity = $invoice->quantity;

                // Add product group to the list of all unique product groups
                if (!in_array($product_group_id, $all_product_groups)) {
                    $all_product_groups[] = $product_group_id;
                }

                if (!isset($product_group_sales[$product_group_id])) {
                    $product_group_sales[$product_group_id] = [
                        'product_group_id' => $product_group_id,
                        'product_group_name' => $product_group_name,
                        'quantity' => 0,
                    ];
                }

                $product_group_sales[$product_group_id]['quantity'] += $quantity;
            }

            // Append franchiser data
            $franchiser_sales[] = [
                'franchiser_id' => $franchiser->id,
                'franchiser_name' => $franchiser->name,
                'franchiser_sales' => array_values($product_group_sales), // Reset array keys
            ];
        }

        // Get all product groups' data in the right order
        $all_product_groups = ProductGroup::whereIn('id', $all_product_groups)->get();

        return view('admin.reports.country-quantity-sales', compact('country', 'from_date', 'to_date', 'franchiser_sales', 'countries', 'all_product_groups'));
    }

    public function countryAmountSales(Request $request)
    {
        $countries = $this->countryService->getAllCountries();

        $country = $request->has('country')
            ? $this->countryService->getCountryById($request->input('country'))
            : $this->countryService->getAllCountries()->first();

        $franchisers = Party::where('is_distributor', '1')
            ->where('country_id', $country->id)
            ->get();

        $from_date = $request->has('from_date')
            ? $request->input('from_date')
            : now()->subDays(30)->format('Y-m-d');

        $to_date = $request->has('to_date')
            ? $request->input('to_date')
            : now()->format('Y-m-d');

        // Check if the date range is greater than 12 months
        $fromDate = Carbon::parse($from_date);
        $toDate = Carbon::parse($to_date);
        $monthDiff = $fromDate->diffInMonths($toDate);

        if ($monthDiff > 12) {
            return back()->withErrors(['date_range' => 'The date range cannot exceed 12 months.']);
        }

        $franchiser_sales = []; // To store the required data structure
        $all_product_groups = []; // To store unique product groups

        foreach ($franchisers as $franchiser) {
            $invoices = InvoiceItem::whereHas('invoice', function ($query) use ($franchiser, $from_date, $to_date) {
                $query->where('party_id', $franchiser->id)
                    ->whereBetween('invoice_date', [$from_date, $to_date]);
            })->get();

            $product_group_sales = [];

            foreach ($invoices as $invoice) {
                $product_group_id = $invoice->product_group_id;
                $product_group_name = $invoice->productGroup->name ?? null;
                $amount = $invoice->total_amount;

                // Add product group to the list of all unique product groups
                if (!in_array($product_group_id, $all_product_groups)) {
                    $all_product_groups[] = $product_group_id;
                }

                if (!isset($product_group_sales[$product_group_id])) {
                    $product_group_sales[$product_group_id] = [
                        'product_group_id' => $product_group_id,
                        'product_group_name' => $product_group_name,
                        'amount' => 0,
                    ];
                }

                $product_group_sales[$product_group_id]['amount'] += $amount;
            }

            // Append franchiser data
            $franchiser_sales[] = [
                'franchiser_id' => $franchiser->id,
                'franchiser_name' => $franchiser->name,
                'franchiser_sales' => array_values($product_group_sales), // Reset array keys
            ];
        }

        // Get all product groups' data in the right order
        $all_product_groups = ProductGroup::whereIn('id', $all_product_groups)->get();

        return view('admin.reports.country-amount-sales', compact('country', 'from_date', 'to_date', 'franchiser_sales', 'countries', 'all_product_groups'));
    }

    // Get all the products for stock bin card

    public function categoryProducts(Request $request, $id)
    {

        $stockBinCards = null;
        $selectedCategoryId = $id;
        if ($selectedCategoryId) {
            $stockBinCards = RawMaterialItem::select('id', 'name')->where('category_id', $selectedCategoryId)->get();
        }

        return response()->json($stockBinCards);

    }

    // current balance item

    public function currentBalanceItem($id)
    {
        $selectedCategoryId = $id;
        $items = [];
        $categoryName = '';
        if ($selectedCategoryId) {
            $category = Category::find($selectedCategoryId);
            $categoryName = $category ? $category->name : '';
            $items = RawMaterialItem::select('id', 'name')->where('category_id', $selectedCategoryId)->get();
        }

        return response()->json([
            'items' => $items,
            'category_name' => $categoryName
        ]);
    }

    //current balance filter
    public function currentBalanceFilter(Request $request)
    {
        // dd($request);
        // Validate input
        $request->validate([
            'balance_category_id' => 'integer||nullable',
            'category_item_id' => 'nullable|array',
            'till_date' => 'date|nullable',
            // 'category_item_id.*' => 'integer',
        ]);

        $productIds = $request->category_item_id ?? [];
        $selectAll = in_array('all', $productIds);

        if ((empty($productIds) || $selectAll) && $request->filled('balance_category_id')) {

            $categoryId = $request->balance_category_id;

            $productIds = array_merge(
                RawMaterialItem::where('category_id', $categoryId)->pluck('id')->toArray(),
                ProductVariation::where('category_id', $categoryId)->pluck('id')->toArray(),
            );

        }

        $tillDate = $request->input('till_date');

        if (empty($productIds)) {

            $stocks = $this->stockService->currentBalanceReport($tillDate);
        }
        else {

            $stocks = Stock::currentBalance($tillDate);

            // Filter by selected product/raw material/item IDs
            $stocks = collect($stocks)->filter(function ($balance) use ($productIds) {
                return in_array($balance['product_id'] ?? null, $productIds)
                || in_array($balance['raw_material_item_id'] ?? null, $productIds)
                || in_array($balance['product_variation_id'] ?? null, $productIds);
            })->values()->toArray();
        }


        $productDetailsQuery = Stock::leftJoin('products', 'stocks.product_id', '=', 'products.id')
            ->leftJoin('raw_material_items', 'stocks.raw_material_item_id', '=', 'raw_material_items.id')
            ->leftJoin('product_variations', 'stocks.product_variation_id', '=', 'product_variations.id')
            ->leftJoin('products as variation_products', 'product_variations.product_id', '=', 'variation_products.id')
            ->leftJoin('packing_types', 'product_variations.packing_type_id', '=', 'packing_types.id')
            ->leftJoin('pack_sizes', 'product_variations.pack_size_id', '=', 'pack_sizes.id')
            ->leftJoin('categories as raw_categories', 'raw_material_items.category_id', '=', 'raw_categories.id')
            ->leftJoin('categories as pv_categories', 'product_variations.category_id', '=', 'pv_categories.id')
            ->select(
            'stocks.id',
            'stocks.company_id',
            'stocks.product_id',
            'stocks.raw_material_item_id',
            'stocks.product_variation_id',
            'stocks.unit',
            'stocks.unit_price',
            'products.name as product_name',
            'raw_material_items.name as raw_material_item_name',
            'variation_products.name as product_variation_name',
            'packing_types.name as packing_type_name',
            'pack_sizes.name as pack_size_name',
            'products.code as product_code',
            'raw_material_items.code as raw_material_item_code',
            'variation_products.code as product_variation_code',
            'raw_categories.name as raw_category_name',
            'pv_categories.name as pv_category_name'
        )
            ->where(function ($query) {
            $query->whereNotNull('stocks.product_id')
                ->orWhereNotNull('stocks.raw_material_item_id')
                ->orWhereNotNull('stocks.product_variation_id');
        })
            ->whereIn('stocks.id', function ($query) {
            $query->selectRaw('MAX(id)')
                ->from('stocks')
                ->groupBy('product_id', 'raw_material_item_id', 'product_variation_id');
        });

        if (!empty($productIds)) {
            $productDetailsQuery->where(function ($q) use ($productIds) {
                $q->whereIn('stocks.product_id', $productIds)
                    ->orWhereIn('stocks.raw_material_item_id', $productIds)
                    ->orWhereIn('stocks.product_variation_id', $productIds);
            });
        }

        $productDetails = $productDetailsQuery->get()->keyBy(function ($item) {
            return ($item->product_id ?? 'null') . '_' .
            ($item->raw_material_item_id ?? 'null') . '_' .
            ($item->product_variation_id ?? 'null');
        });


        if (!auth()->user()->hasRole('Superadmin')) {
            $companyId = auth()->user()->employee->company_id;
            $productDetails = $productDetails->filter(function ($item) use ($companyId) {
                return $item->company_id == $companyId;
            });
        }

        foreach ($stocks as &$balance) {
            $key = ($balance['product_id'] ?? 'null') . '_' .
                ($balance['raw_material_item_id'] ?? 'null') . '_' .
                ($balance['product_variation_id'] ?? 'null');

            $productDetail = $productDetails->get($key);

            $balance['product_name'] = $productDetail->product_name ?? null;
            $balance['raw_material_item_name'] = $productDetail->raw_material_item_name ?? null;
            $balance['product_variation_name'] = $productDetail->product_variation_name ?? null;
            $balance['packing_type_name'] = $productDetail->packing_type_name ?? null;
            $balance['pack_size_name'] = $productDetail->pack_size_name ?? null;
            $balance['product_code'] = $productDetail->product_code ?? null;
            $balance['raw_material_item_code'] = $productDetail->raw_material_item_code ?? null;
            $balance['product_variation_code'] = $productDetail->product_variation_code ?? null;
            $balance['category_name'] = $productDetail->raw_category_name ?? $productDetail->pv_category_name ?? null;
            $balance['unit'] = $productDetail->unit ?? null;
            $balance['unit_price'];
            $balance['total'] = ($balance['balance'] ?? 0) * ($balance['unit_price'] ?? 0);
        }

        unset($balance);

        $categories = Category::all();
        $selectedCategoryId = $request->balance_category_id;
        $selectedCategoryName = null;
        if ($selectedCategoryId) {
            $category = Category::find($selectedCategoryId);
            $selectedCategoryName = $category ? $category->name : null;
        }

        return view('admin.reports.balance-report', compact('stocks', 'categories', 'selectedCategoryId', 'selectedCategoryName', 'tillDate'));
    }

    public function fifoReport(Request $request)
    {
        $request->validate([
            'balance_category_id' => 'integer|nullable',
            'category_item_id' => 'required|array',
            'till_date' => 'date|nullable',
        ]);

        $itemIds = $request->category_item_id;
        $tillDate = $request->till_date;

        if (in_array('all', $itemIds)) {
            $categoryId = $request->balance_category_id;
            $itemIds = array_merge(
                RawMaterialItem::where('category_id', $categoryId)->pluck('id')->toArray(),
                ProductVariation::where('category_id', $categoryId)->pluck('id')->toArray()
            );
        }

        // Fetch GRN batches for selected items
        $batches = GrnItemBatch::with([
                'grn', 
                'grnItem.rawMaterialItem', 
                'grnItem.product', 
            ])
            ->whereHas('grnItem', function ($query) use ($itemIds) {
                $query->whereIn('raw_material_item_id', $itemIds)
                    ->orWhereIn('product_id', $itemIds);
            })
            ->whereHas('grn', function ($query) use ($tillDate) {
                if ($tillDate) {
                    $query->where('created_at', '<=', $tillDate . ' 23:59:59');
                }
            })
            ->orderBy('created_at', 'asc') // Oldest first for FIFO flow
            ->get();

        // For each batch, fetch its issues (SIRs)
        foreach ($batches as $batch) {
            $batch->issues = StoreIssueRequisitionItem::with('store_issue_requisition')
                ->where('batch_id', $batch->id)
                ->whereHas('store_issue_requisition', function ($q) use ($tillDate) {
                    if ($tillDate) {
                        $q->where('created_at', '<=', $tillDate . ' 23:59:59');
                    }
                })
                ->get();
            
            $batch->total_consumed = $batch->issues->sum('quantity');
            $batch->leftover = $batch->receiving_quantity - $batch->total_consumed;
        }

        // Sort alphabetically by item name first, then by created_at (FIFO)
        $batches = $batches->sortBy(function ($batch) {
            $itemName = strtolower($batch->grnItem->rawMaterialItem->name ?? $batch->grnItem->product->name ?? 'zzzz');
            $timestamp = $batch->created_at ? $batch->created_at->timestamp : 0;
            return sprintf('%s_%010d', $itemName, $timestamp);
        })->values();

        $selectedCategoryName = null;
        if ($request->balance_category_id) {
            $selectedCategoryName = Category::find($request->balance_category_id)->name ?? null;
        }

        return view('admin.reports.fifo-report', compact('batches', 'selectedCategoryName', 'tillDate'));
    }
}
