<?php

namespace App\Http\Controllers;

use App\Models\Warehouse;
use App\Models\WarehouseCurrentStock;
use App\Models\StockLedger;
use App\Models\Item;
use App\Models\PurchaseOrder;
use App\Models\GatePass;
use App\Models\Qc;
use App\Models\Stock;
use App\Models\BOMPickList;
use App\Models\BOMPickListEmployee;
use App\Models\ManufacturingRun;
use App\Models\Account;
use App\Models\FactoryInvoice;
use App\Models\FactoryInvoiceItem;
use App\Services\StockAccountingService;
use Illuminate\Http\Request;
use Yajra\DataTables\Facades\DataTables;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Auth;

class WarehouseController extends Controller
{
    public function index()
    {
        return view('warehouses.index');
    }

    public function create()
    {
        return view('warehouses.create');
    }

    public function store(Request $request)
    {
        $request->validate([
            'code' => 'required|string|max:50|unique:warehouses,code',
            'name' => 'required|string|max:255',
            'location' => 'nullable|string|max:255',
            'type' => 'required|in:factory,warehouse,store',
            'site_id' => 'nullable|integer|min:1',
            'is_active' => 'boolean',
        ]);

        Warehouse::create($request->all());

        return redirect()->route('warehouses.index')
            ->with('success', 'Warehouse created successfully.');
    }

    public function show(Warehouse $warehouse)
    {
        $warehouse->load(['bins', 'locations', 'stockBalances', 'grns', 'stockTransfers']);
        return view('warehouses.show', compact('warehouse'));
    }

    public function edit(Warehouse $warehouse)
    {
        // Check if warehouse has linked data
        $hasLinkedData = $this->hasLinkedData($warehouse);

        if ($hasLinkedData) {
            return redirect()->route('warehouses.index')
                ->with('error', 'Cannot edit warehouse. It has linked data (bins, locations, stock balances, GRNs, or stock transfers).');
        }

        return view('warehouses.edit', compact('warehouse'));
    }

    public function update(Request $request, Warehouse $warehouse)
    {
        // Check if warehouse has linked data
        $hasLinkedData = $this->hasLinkedData($warehouse);

        if ($hasLinkedData) {
            return redirect()->route('warehouses.index')
                ->with('error', 'Cannot update warehouse. It has linked data.');
        }

        $request->validate([
            'code' => 'required|string|max:50|unique:warehouses,code,' . $warehouse->id,
            'name' => 'required|string|max:255',
            'location' => 'nullable|string|max:255',
            'type' => 'required|in:factory,warehouse,store',
            'site_id' => 'nullable|integer|min:1',
            'is_active' => 'boolean',
        ]);

        $warehouse->update($request->all());

        return redirect()->route('warehouses.index')
            ->with('success', 'Warehouse updated successfully.');
    }

    public function destroy(Warehouse $warehouse)
    {
        // Check if warehouse has related data
        $hasLinkedData = $this->hasLinkedData($warehouse);

        if ($hasLinkedData) {
            return redirect()->route('warehouses.index')
                ->with('error', 'Cannot delete warehouse. It has related data (bins, locations, stock balances, GRNs, or stock transfers).');
        }

        $warehouse->delete();

        return redirect()->route('warehouses.index')
            ->with('success', 'Warehouse deleted successfully.');
    }

    public function toggleActive(Warehouse $warehouse)
    {
        $warehouse->update(['is_active' => !$warehouse->is_active]);

        $status = $warehouse->is_active ? 'activated' : 'deactivated';
        return redirect()->route('warehouses.index')
            ->with('success', "Warehouse {$status} successfully.");
    }

    public function getData()
    {
        // dd("Ddd");
        $warehouses = Warehouse::orderBy('name')
            ->get();

        return DataTables::of($warehouses)
            ->addColumn('status_badge', function ($warehouse) {
                return $warehouse->is_active
                    ? '<span class="badge badge-success">Active</span>'
                    : '<span class="badge badge-secondary">Inactive</span>';
            })
            ->addColumn('type_label', function ($warehouse) {
                return $warehouse->type;
            })
            ->addColumn('location_display', function ($warehouse) {
                return $warehouse->location;
            })
            // ->addColumn('total_bins', function ($warehouse) {
            //     return $warehouse->bins_count;
            // })
            // ->addColumn('total_locations', function ($warehouse) {
            //     return $warehouse->locations_count;
            // })
            // ->addColumn('total_stock_balances', function ($warehouse) {
            //     return $warehouse->stock_balances_count;
            // })
            ->addColumn('actions', function ($warehouse) {
                $actions = '<div class="btn-group" role="group">';
                $actions .= '<a href="' . route('warehouses.show', $warehouse->id) . '" class="btn btn-sm btn-info">View</a>';

                // Check if warehouse has linked data
                $hasLinkedData = $this->hasLinkedData($warehouse);

                if (!$hasLinkedData) {
                    $actions .= '<a href="' . route('warehouses.edit', $warehouse->id) . '" class="btn btn-sm btn-warning">Edit</a>';
                }

                // else {

                //     $actions .= '<button class="btn btn-sm btn-secondary" disabled title="Cannot edit - warehouse has linked data">Edit</button>';
                // }

                // Toggle active status
                // $toggleText = $warehouse->is_active ? 'Deactivate' : 'Activate';
                // $toggleClass = $warehouse->is_active ? 'btn-outline-warning' : 'btn-outline-success';
                // $actions .= '<a href="' . route('warehouses.toggle-active', $warehouse->id) . '" class="btn btn-sm ' . $toggleClass . '">' . $toggleText . '</a>';

                // Only show delete if no related data
                // if (!$hasLinkedData) {
                //     $actions .= '<form method="POST" action="' . route('warehouses.destroy', $warehouse->id) . '" style="display:inline;">';
                //     $actions .= csrf_field() . method_field('DELETE');
                //     $actions .= '<button type="submit" class="btn btn-sm btn-danger" onclick="return confirm(\'Are you sure you want to delete this warehouse?\')">Delete</button>';
                //     $actions .= '</form>';
                // } else {
                //     $actions .= '<button class="btn btn-sm btn-secondary" disabled title="Cannot delete - warehouse has linked data">Delete</button>';
                // }

                $actions .= '</div>';
                return $actions;
            })
            ->rawColumns(['status_badge', 'actions'])
            ->make(true);
    }

    public function search(Request $request)
    {
        $query = $request->get('q');
        $type = $request->get('type');
        $active = $request->get('active');

        $warehouses = Warehouse::query();

        if ($query) {
            $warehouses->where(function ($q) use ($query) {
                $q->where('name', 'like', "%{$query}%")
                    ->orWhere('code', 'like', "%{$query}%")
                    ->orWhere('location', 'like', "%{$query}%");
            });
        }

        if ($type) {
            $warehouses->where('type', $type);
        }

        if ($active !== null) {
            $warehouses->where('is_active', $active);
        }

        return $warehouses->limit(10)->get()->map(function ($warehouse) {
            return [
                'id' => $warehouse->id,
                'text' => $warehouse->full_name,
                'code' => $warehouse->code,
                'type' => $warehouse->type_label,
                'location' => $warehouse->location
            ];
        });
    }

    /**
     * Current Stock Management
     */
    public function currentStock()
    {
        $warehouses = Warehouse::active()->get();
        return view('warehouses.current-stock', compact('warehouses'));
    }

    public function getCurrentStockData(Request $request)
    {
        $query = Stock::with(['account', 'warehouse'])
            ->select('stocks.*');

        // Apply filters
        if ($request->filled('warehouse_id')) {
            $query->where('warehouse_id', $request->warehouse_id);
        }

        if ($request->filled('item_search')) {
            $query->whereHas('account', function ($q) use ($request) {
                $q->where('name', 'like', '%' . $request->item_search . '%')
                    ->orWhere('code', 'like', '%' . $request->item_search . '%');
            });
        }

        if ($request->filled('min_qty')) {
            $query->where('current_qty', '>=', $request->min_qty);
        }

        if ($request->filled('max_qty')) {
            $query->where('current_qty', '<=', $request->max_qty);
        }

        return DataTables::of($query)
            ->addColumn('item_name', function ($stock) {
                return $stock->account->name ?? 'N/A';
            })
            ->addColumn('item_code', function ($stock) {
                return $stock->account->code ?? 'N/A';
            })
            ->addColumn('size', function ($stock) {
                return $stock->length ? number_format($stock->length, 2) . ' ' . ($stock->unit ?? '') : 'N/A';
            })
            ->addColumn('warehouse_name', function ($stock) {
                return $stock->warehouse->name ?? 'N/A';
            })
            ->addColumn('available_qty', function ($stock) {
                return number_format($stock->quantity, 2);
            })
            ->addColumn('actions', function ($stock) {
                return '<a href="' . route('warehouses.stock-history', ['item_id' => $stock->account_id, 'warehouse_id' => $stock->warehouse_id]) . '" class="btn btn-sm btn-info">History</a>';
            })
            ->rawColumns(['actions'])
            ->make(true);
    }

    /**
     * Stock History Management
     */
    public function stockHistory(Request $request)
    {
        $warehouses = Warehouse::active()->get();
        $items = Item::orderBy('name')->get();

        return view('warehouses.stock-history', compact('warehouses', 'items'));
    }

    public function getStockHistoryData(Request $request)
    {
        // dd("dd");
        $query = StockLedger::with([
            'account',
            'bin.warehouse'
        ])
            ->select('stock_ledgers.*');

        // Apply filters
        if ($request->filled('warehouse_id')) {
            $query->where('warehouse_id', $request->warehouse_id);
        }

        if ($request->filled('item_id')) {
            $query->where('account_id', $request->item_id);
        }

        if ($request->filled('txn_type')) {
            $query->whereHas('igp', function ($q) use ($request) {
                $q->where('type', $request->txn_type);
            });
        }

        if ($request->filled('date_from')) {
            $query->whereDate('created_at', '>=', $request->date_from);
        }

        if ($request->filled('date_to')) {
            $query->whereDate('created_at', '<=', $request->date_to);
        }

        return DataTables::of($query)
            ->addColumn('item_name', function ($ledger) {
                return $ledger->account->name ?? 'N/A';
            })
            ->addColumn('item_type', function ($ledger) {
                return $ledger->account->chartOfAccount->name ?? 'N/A';
            })
            ->addColumn('po_number', function ($ledger) {
                return $ledger->po_id ? PurchaseOrder::find($ledger->po_id)->po_number : 'N/A';
            })
            ->addColumn('po_qty', function ($ledger) {
                if ($ledger->po_id) {
                    $poItem = \App\Models\PurchaseOrderItem::where('purchase_order_id', $ledger->po_id)
                        ->where('item_id', $ledger->item_id)
                        ->first();
                    return $poItem ? number_format($poItem->quantity, 2) : 'N/A';
                }
                return 'N/A';
            })
            ->addColumn('igp_number', function ($ledger) {
                return $ledger->igp_id ? GatePass::find($ledger->igp_id)->gate_pass_number : 'N/A';
            })
            ->addColumn('qc_number', function ($ledger) {
                return $ledger->qc_id ? Qc::find($ledger->qc_id)->qc_number : 'N/A';
            })
            ->addColumn('ac_qty', function ($ledger) {
                return number_format(abs($ledger->quantity), 2);
            })
            ->addColumn('ledger_qty', function ($ledger) {
                // Get all previous ledger entries for this item BEFORE current record
                $previousTotal = StockLedger::where('account_id', $ledger->item_id)
                    ->where('created_at', '<', $ledger->created_at)
                    ->orderBy('created_at', 'asc')
                    ->get()
                    ->reduce(function ($total, $entry) {
                        if ($entry->igp_id) {
                            $gatePass = GatePass::find($entry->igp_id);
                            if ($gatePass) {
                                // Add for inward, subtract for outward
                                return $total + ($gatePass->type === 'inward' ? $entry->quantity : -$entry->quantity);
                            }
                        }
                        return $total + $entry->quantity;
                    }, 0);

                return number_format($previousTotal ?? 0, 2);
            })
            // ->addColumn('ledger_qty_1', function ($ledger) {
            //     // Get all previous ledger entries for this item including current
            //     $runningTotal = StockLedger::where('item_id', $ledger->item_id)
            //         ->where('created_at', '<=', $ledger->created_at)
            //         ->orderBy('created_at', 'asc')
            //         ->get()
            //         ->reduce(function ($total, $entry) {
            //             if ($entry->igp_id) {
            //                 $gatePass = GatePass::find($entry->igp_id);
            //                 if ($gatePass) {
            //                     // Add for inward, subtract for outward
            //                     return $total + ($gatePass->type === 'inward' ? $entry->quantity : -$entry->quantity);
            //                 }
            //             }
            //             return $total + $entry->quantity;
            //         }, 0);

            //     return number_format($runningTotal, 2);
            // })
            ->addColumn('warehouse_name', function ($ledger) {
                // return $ledger->bin && $ledger->bin->warehouse ? $ledger->bin->warehouse->name : 'N/A';
                return $ledger->warehouse->name ?? 'N/A';
            })
            ->addColumn('transaction_type', function ($ledger) {
                return $ledger->transaction_type_badge;
            })
            ->addColumn('date', function ($ledger) {
                return $ledger->formatted_date;
            })
            ->rawColumns(['transaction_type'])
            ->make(true);
    }

    /**
     * Check if warehouse has linked data
     */
    private function hasLinkedData(Warehouse $warehouse): bool
    {
        return $warehouse->bins()->exists() ||
            $warehouse->locations()->exists() ||
            $warehouse->stockBalances()->exists() ||
            $warehouse->grns()->exists();
    }

    /**
     * Get warehouses for API (simple JSON response)
     */
    public function getWarehousesForApi()
    {
        // dd("Ddd");
        $warehouses = Warehouse::where('is_active', true)
            ->orderBy('name')
            ->get(['id', 'name', 'code']);

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

    /**
     * BOM Pick List Management
     */
    public function bomPickListIndex()
    {
        return view('warehouses.bom-pick-list.index');
    }

    public function bomPickListCreate()
    {
        return view('warehouses.bom-pick-list.create');
    }

    public function getManufacturingRunDetails(Request $request)
    {
        $request->validate([
            'm_number' => 'required|string|exists:manufacturing_runs,m_number'
        ]);

        $manufacturingRun = ManufacturingRun::with([
            'manufacturingBom.finishedGoods',
            'manufacturingBom.ingredients.ingredientAccount',
            'manufacturingBomHistory.ingredients.ingredientAccount'
        ])->where('m_number', $request->m_number)->first();

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

        return response()->json([
            'success' => true,
            'data' => [
                'manufacturing_run' => $manufacturingRun,
                'bom' => $manufacturingRun->manufacturingBom,
                'ingredients' => $manufacturingRun->manufacturingBomHistory->ingredients ?? $manufacturingRun->manufacturingBom->ingredients
            ]
        ]);
    }

    public function searchEmployees(Request $request)
    {
        $query = $request->get('q');

        $employees = Account::whereIn('model_type', ['employee', 'worker'])
            ->where('is_active', true)
            ->where(function ($q) use ($query) {
                $q->where('name', 'like', "%{$query}%")
                    ->orWhere('code', 'like', "%{$query}%")
                    ->orWhere('email', 'like', "%{$query}%");
            })
            ->limit(20)
            ->get();

        return response()->json([
            'results' => $employees->map(function ($employee) {
                return [
                    'id' => $employee->id,
                    'text' => $employee->name . ' (' . $employee->code . ')',
                    'employee_type' => $employee->employee_type,
                    'team_name' => $employee->team_name
                ];
            })
        ]);
    }

    public function storeBomPickList(Request $request)
    {
        $request->validate([
            'm_number' => 'required|string|exists:manufacturing_runs,m_number',
            'employee_id' => 'required|exists:accounts,id',
            'notes' => 'nullable|string|max:1000'
        ]);

        try {
            DB::beginTransaction();

            $manufacturingRun = ManufacturingRun::where('m_number', $request->m_number)->first();

            // Create BOM pick list
            $bomPickList = BOMPickList::create([
                'm_number' => $request->m_number,
                'manufacturing_run_id' => $manufacturingRun->id,
                'manufacturing_bom_id' => $manufacturingRun->manufacturing_bom_id,
                'status' => BOMPickList::STATUS_PENDING,
                'notes' => $request->notes,
                'created_by' => Auth::id(),
            ]);

            // Assign employee
            BOMPickListEmployee::create([
                'bom_pick_list_id' => $bomPickList->id,
                'employee_id' => $request->employee_id,
                'is_checked' => false,
                'assigned_by' => Auth::id(),
            ]);

            // Process stock movement for ingredients
            $this->processStockMovement($bomPickList, $manufacturingRun);

            DB::commit();

            return response()->json([
                'success' => true,
                'message' => 'BOM pick list created successfully',
                'data' => $bomPickList
            ]);
        } catch (\Exception $e) {
            DB::rollBack();
            return response()->json([
                'success' => false,
                'message' => 'Error creating BOM pick list: ' . $e->getMessage()
            ], 500);
        }
    }

    protected function processStockMovement(BOMPickList $bomPickList, ManufacturingRun $manufacturingRun)
    {
        $stockAccountingService = app(StockAccountingService::class);

        // Get ingredients from BOM history or BOM
        $ingredients = $manufacturingRun->manufacturingBomHistory->ingredients ??
            $manufacturingRun->manufacturingBom->ingredients;

        foreach ($ingredients as $ingredient) {
            // Calculate required quantity based on scaling factor
            $requiredQty = $ingredient->quantity_required * $manufacturingRun->scaling_factor;

            // Process stock movement (outward for manufacturing)
            $stockAccountingService->processStockMovement(
                $ingredient->ingredient_account_id,
                1, // Default warehouse ID - should be configurable
                'outward',
                $requiredQty,
                'manufacturing',
                $bomPickList->id,
                'BOM Pick List: ' . $bomPickList->m_number
            );
        }
    }

    public function getBomPickListData()
    {
        $query = BOMPickList::with([
            'manufacturingRun',
            'manufacturingBom.finishedGoods',
            'employees.employee',
            'createdBy'
        ]);

        return DataTables::of($query)
            ->addColumn('m_number', function ($pickList) {
                return $pickList->m_number;
            })
            ->addColumn('finished_goods', function ($pickList) {
                return $pickList->manufacturingBom->finishedGoods->name ?? 'N/A';
            })
            ->addColumn('status_badge', function ($pickList) {
                return '<span class="badge bg-' . $pickList->status_badge . '">' . $pickList->status_text . '</span>';
            })
            ->addColumn('assigned_employees', function ($pickList) {
                return $pickList->employees->map(function ($emp) {
                    return $emp->employee->name . ' (' . $emp->employee->code . ')';
                })->join(', ');
            })
            ->addColumn('created_by', function ($pickList) {
                return $pickList->createdBy->name ?? 'N/A';
            })
            ->addColumn('created_at', function ($pickList) {
                return $pickList->created_at->format('M d, Y H:i');
            })
            ->addColumn('actions', function ($pickList) {
                $actions = '<div class="btn-group" role="group">';
                $actions .= '<a href="' . route('warehouses.bom-pick-list.show', $pickList->id) . '" class="btn btn-sm btn-info">View</a>';

                if ($pickList->status === BOMPickList::STATUS_PENDING) {
                    $actions .= '<button class="btn btn-sm btn-success" onclick="updateStatus(' . $pickList->id . ', \'in_progress\')">Start</button>';
                } elseif ($pickList->status === BOMPickList::STATUS_IN_PROGRESS) {
                    $actions .= '<button class="btn btn-sm btn-primary" onclick="updateStatus(' . $pickList->id . ', \'completed\')">Complete</button>';
                }

                $actions .= '</div>';
                return $actions;
            })
            ->rawColumns(['status_badge', 'actions'])
            ->make(true);
    }

    public function showBomPickList(BOMPickList $bomPickList)
    {
        $bomPickList->load([
            'manufacturingRun',
            'manufacturingBom.finishedGoods',
            'manufacturingBom.ingredients.ingredientAccount',
            'employees.employee',
            'createdBy'
        ]);

        return view('warehouses.bom-pick-list.show', compact('bomPickList'));
    }

    public function updateBomPickListStatus(Request $request, BOMPickList $bomPickList)
    {
        $request->validate([
            'status' => 'required|in:pending,in_progress,completed,cancelled'
        ]);

        $bomPickList->update([
            'status' => $request->status,
            'updated_by' => Auth::id()
        ]);

        return response()->json([
            'success' => true,
            'message' => 'Status updated successfully'
        ]);
    }

    /**
     * Planned Manufacturing Runs (Warehouse Confirmation)
     */
    public function plannedManufacturingIndex()
    {
        return view('warehouses.planned-manufacturing.index');
    }

    public function getPlannedManufacturingData(Request $request)
    {
        $runs = ManufacturingRun::with(['manufacturingBom.finishedGoods', 'machine', 'supervisorEmployee', 'creator', 'warehouse'])
            ->where('status', ManufacturingRun::STATUS_PLANNED)
            ->select('manufacturing_runs.*');

        return DataTables::of($runs)
            ->addColumn('bom_name', function ($run) {
                return $run->manufacturingBom ? $run->manufacturingBom->name : 'N/A';
            })
            ->addColumn('finished_goods', function ($run) {
                return $run->manufacturingBom && $run->manufacturingBom->finishedGoods
                    ? $run->manufacturingBom->finishedGoods->name
                    : 'N/A';
            })
            ->addColumn('machine_name', function ($run) {
                return $run->machine ? $run->machine->name . ' (' . $run->machine->code . ')' : 'N/A';
            })
            ->addColumn('supervisor_name', function ($run) {
                return $run->supervisorEmployee ? $run->supervisorEmployee->name : 'N/A';
            })
            ->addColumn('warehouse_name', function ($run) {
                return $run->warehouse ? $run->warehouse->name : 'N/A';
            })
            ->addColumn('planned_output_qty', function ($run) {
                return number_format($run->planned_output_qty, 4) . ' ' . $run->unit_of_measurement;
            })
            ->addColumn('status_badge', function ($run) {
                return '<span class="badge ' . $run->status_badge . '">' . $run->status_label . '</span>';
            })
            ->addColumn('actions', function ($run) {
                $actions = '<div class="btn-group" role="group">';
                $actions .= '<button type="button" class="btn btn-sm btn-success start-manufacturing-btn" data-id="' . $run->id . '" data-m-number="' . $run->m_number . '">
                    <i class="fas fa-play"></i> Start Manufacturing
                </button>';
                $actions .= '</div>';
                return $actions;
            })
            ->rawColumns(['status_badge', 'actions'])
            ->make(true);
    }

    public function startPlannedManufacturing(Request $request, ManufacturingRun $manufacturingRun)
    {
        // Validate that the run is in planned status
        if ($manufacturingRun->status !== ManufacturingRun::STATUS_PLANNED) {
            $message = 'This manufacturing run is not in planned status.';
            if ($request->ajax()) {
                return response()->json(['success' => false, 'message' => $message]);
            }
            return redirect()->back()->with('error', $message);
        }

        try {
            DB::beginTransaction();

            // Get ingredients from BOM history snapshot (preferred) or BOM
            $ingredients = $manufacturingRun->bomHistory->ingredients ??
                ($manufacturingRun->manufacturingBom ? $manufacturingRun->manufacturingBom->ingredients : collect([]));

            if ($ingredients->isEmpty()) {
                throw new \Exception("No ingredients found for this manufacturing run BOM.");
            }

            $warehouseId = $manufacturingRun->warehouse_id ?? 1;
            $insufficientItems = [];
            $scalingFactor = $manufacturingRun->scaling_factor ?? 1;

            // 1. Availability Check
            foreach ($ingredients as $ingredient) {
                $requiredQty = $ingredient->quantity_required * $scalingFactor;
                $accountId = $ingredient->ingredient_account_id;

                $stock = Stock::where('account_id', $accountId)
                    ->where('warehouse_id', $warehouseId)
                    ->lockForUpdate()
                    ->first();

                if (!$stock || $stock->quantity < $requiredQty) {
                    $available = $stock ? $stock->quantity : 0;
                    $itemName = $ingredient->ingredient_name ?? ($ingredient->ingredient ? $ingredient->ingredient->name : 'Unknown Item');
                    $insufficientItems[] = "{$itemName} (Required: {$requiredQty}, Available: {$available})";
                }
            }

            if (!empty($insufficientItems)) {
                DB::rollBack();
                if ($request->ajax()) {
                    return response()->json([
                        'success' => false,
                        'message' => 'Insufficient stock for ingredients.',
                        'insufficientItems' => $insufficientItems
                    ]);
                }
                return redirect()->back()
                    ->with('error', 'Insufficient stock for ingredients.')
                    ->with('insufficientItems', $insufficientItems);
            }

            // 2. Stock Deduction and Ledger Recording
            foreach ($ingredients as $ingredient) {
                $requiredQty = $ingredient->quantity_required * $scalingFactor;
                $accountId = $ingredient->ingredient_account_id;

                // Update Stocks table
                $stock = Stock::where('account_id', $accountId)
                    ->where('warehouse_id', $warehouseId)
                    ->first();

                $stock->decrement('quantity', $requiredQty);

                // Create Stock Ledger Entry
                StockLedger::create([
                    'account_id' => $accountId,
                    'warehouse_id' => $warehouseId,
                    // 'txn_type' => 'production_issue',
                    'reference' => $manufacturingRun->m_number,
                    'piece_quantity' => $requiredQty,
                    // 'qty_out' => $requiredQty,
                    'quantity' => -$requiredQty,
                    'manufacturing_run_id' => $manufacturingRun->id,
                    // 'unit' => $ingredient->unit,
                    'remarks' => 'Manufacturing Start: ' . $manufacturingRun->m_number,
                    // 'timestamp' => now(),
                    'timestamp' => now(),
                    // 'created_by' => auth()->id(),
                ]);
            }

            // Update the status to in progress
            $manufacturingRun->update([
                'status' => ManufacturingRun::STATUS_IN_PROGRESS,
                'updated_by' => Auth::id()
            ]);

            DB::commit();

            $successMessage = 'Manufacturing run started and raw material stock updated successfully!';
            if ($request->ajax()) {
                return response()->json(['success' => true, 'message' => $successMessage]);
            }
            return redirect()->back()->with('success', $successMessage);
        } catch (\Exception $e) {
            DB::rollBack();
            \Log::error('Failed to start planned manufacturing: ' . $e->getMessage(), [
                'run_id' => $manufacturingRun->id,
                'trace' => $e->getTraceAsString()
            ]);
            $errorMessage = 'Failed to update manufacturing run status: ' . $e->getMessage();
            if ($request->ajax()) {
                return response()->json(['success' => false, 'message' => $errorMessage]);
            }
            return redirect()->back()->with('error', $errorMessage);
        }
    }

    /**
     * Current Stock - Finished Goods
     */
    public function currentStockFinishedGoods()
    {
        $warehouses = Warehouse::active()->get();
        return view('warehouses.current-stock-finished-goods', compact('warehouses'));
    }

    // public function getCurrentStockFinishedGoodsData(Request $request)
    // {
    //     $query = Stock::with(['account.uom', 'warehouse'])
    //         ->whereHas('account', function ($q) {
    //             $q->where('model_type', 'Item')
    //               ->where('is_shop_item', 0); // Exclude shop items
    //         })
    //         ->whereHas('account.chartOfAccount', function ($q) {
    //             $q->where('chart_of_account_id', 53); // Finished Goods
    //         })
    //         ->select('stocks.*');

    //     // Apply filters
    //     if ($request->filled('warehouse_id')) {
    //         $query->where('warehouse_id', $request->warehouse_id);
    //     }

    //     if ($request->filled('item_search')) {
    //         $query->whereHas('account', function ($q) use ($request) {
    //             $q->where('name', 'like', '%' . $request->item_search . '%')
    //                 ->orWhere('code', 'like', '%' . $request->item_search . '%');
    //         });
    //     }

    //     if ($request->filled('min_qty')) {
    //         $query->where('quantity', '>=', $request->min_qty);
    //     }

    //     if ($request->filled('max_qty')) {
    //         $query->where('quantity', '<=', $request->max_qty);
    //     }
    //     $query->orderByDesc('id');

    //     return DataTables::of($query)
    //         ->addColumn('id', fn($row) => $row->id)
    //         ->addColumn('item_name', function ($stock) {
    //             return $stock->account->name ?? 'N/A';
    //         })
    //         ->addColumn('item_code', function ($stock) {
    //             return $stock->account->code ?? 'N/A';
    //         })
    //         // ->addColumn('size', function ($stock) {
    //         //     return $stock->length ? number_format($stock->length, 2) . ' ' . ($stock->unit ?? '') : 'N/A';
    //         // })
    //         ->addColumn('size', function ($stock) {
    //             $unit = $stock->account && $stock->account->uom
    //                 ? $stock->account->uom->name
    //                 : '';
    //             return $stock->length
    //                 ? number_format($stock->length, 2) . ' ' . $unit
    //                 : 'N/A';
    //         })

    //         ->addColumn('warehouse_name', function ($stock) {
    //             return $stock->warehouse->name ?? 'N/A';
    //         })
    //         ->addColumn('available_qty', function ($stock) {
    //             return number_format($stock->quantity, 2);
    //         })
    //         ->addColumn('actions', function ($stock) {
    //             return '<a href="' . route('warehouses.stock-history-finished-goods', ['item_id' => $stock->account_id, 'warehouse_id' => $stock->warehouse_id]) . '" class="btn btn-sm btn-info">History</a>';
    //         })
    //         ->rawColumns(['actions'])
    //         ->make(true);
    // }

    public function getCurrentStockFinishedGoodsData(Request $request)
    {
        $query = Stock::with(['account.uom', 'warehouse'])
            ->whereHas('account', function ($q) {
                $q->where('model_type', 'Item')
                ->where('is_shop_item', 0)
                ->where('chart_of_account_id', 53);
            })
            ->select('stocks.*');

        // ✅ Custom item_search filter
        if ($request->filled('item_search')) {
            $search = $request->item_search;
            $query->whereHas('account', function ($q) use ($search) {
                $q->where(function ($sub) use ($search) {
                    $sub->where('name', 'like', '%' . $search . '%')
                        ->orWhere('code', 'like', '%' . $search . '%')
                        ->orWhere('sku',  'like', '%' . $search . '%');
                });
            });
        }

        if ($request->filled('warehouse_id')) {
            $query->where('warehouse_id', $request->warehouse_id);
        }

        if ($request->filled('min_qty')) {
            $query->where('quantity', '>=', $request->min_qty);
        }

        if ($request->filled('max_qty')) {
            $query->where('quantity', '<=', $request->max_qty);
        }

        $query->orderByDesc('id');

        return DataTables::of($query)
            ->addColumn('item_name',      fn($s) => $s->account->name ?? 'N/A')
            ->addColumn('item_code',      fn($s) => $s->account->code ?? 'N/A')
            ->addColumn('warehouse_name', fn($s) => $s->warehouse->name ?? 'N/A')
            ->addColumn('available_qty',  fn($s) => number_format($s->quantity, 2))
            ->addColumn('size', function ($s) {
                $unit = optional($s->account->uom)->name ?? '';
                return !empty($s->length)
                    ? number_format($s->length, 2) . ' ' . $unit
                    : ($unit ?: 'N/A');
            })
            ->addColumn('actions', function ($s) {
                return '<a href="' . route('warehouses.stock-history-finished-goods', [
                    'item_id'      => $s->account_id,
                    'warehouse_id' => $s->warehouse_id,
                ]) . '" class="btn btn-sm btn-info">History</a>';
            })

            // ✅ Fix DataTables global searchbox for virtual columns
            ->filterColumn('item_name', function ($query, $keyword) {
                $query->whereHas('account', fn($q) => $q->where('name', 'like', "%{$keyword}%"));
            })
            ->filterColumn('item_code', function ($query, $keyword) {
                $query->whereHas('account', fn($q) => $q->where('code', 'like', "%{$keyword}%"));
            })
            ->filterColumn('warehouse_name', function ($query, $keyword) {
                $query->whereHas('warehouse', fn($q) => $q->where('name', 'like', "%{$keyword}%"));
            })

            ->rawColumns(['actions'])
            ->make(true);
    }


    /**
     * Current Stock - Raw Materials
     */
    public function currentStockRawMaterials()
    {
        $warehouses = Warehouse::active()->get();
        return view('warehouses.current-stock-raw-materials', compact('warehouses'));
    }

    // public function getCurrentStockRawMaterialsData(Request $request)
    // {
    //     $query = Stock::with(['account.uom', 'warehouse'])
    //         ->whereHas('account.chartOfAccount', function ($q) {
    //             $q->where('chart_of_account_id', 52); // Raw Materials
    //         })
    //         ->select('stocks.*');

    //     // Apply filters
    //     if ($request->filled('warehouse_id')) {
    //         $query->where('warehouse_id', $request->warehouse_id);
    //     }

    //     if ($request->filled('item_search')) {
    //         $query->whereHas('account', function ($q) use ($request) {
    //             $q->where('name', 'like', '%' . $request->item_search . '%')
    //                 ->orWhere('code', 'like', '%' . $request->item_search . '%');
    //         });
    //     }

    //     if ($request->filled('min_qty')) {
    //         $query->where('quantity', '>=', $request->min_qty);
    //     }

    //     if ($request->filled('max_qty')) {
    //         $query->where('quantity', '<=', $request->max_qty);
    //     }
    //     $query->orderByDesc('id');

    //     return DataTables::of($query)
    //         ->addColumn('id', fn($row) => $row->id)

    //         ->addColumn('item_name', function ($stock) {
    //             return $stock->account->name ?? 'N/A';
    //         })
    //         ->addColumn('item_code', function ($stock) {
    //             return $stock->account->code ?? 'N/A';
    //         })
    //         // ->addColumn('size', function ($stock) {
    //         //     return $stock->length ? number_format($stock->length, 2) . ' ' . ($stock->unit ?? '') : 'N/A';
    //         // })
    //         // ->addColumn('size', function ($stock) {
    //         //     $unit = $stock->account && $stock->account->uom
    //         //         ? $stock->account->uom->name
    //         //         : '';

    //         //     return $stock->length
    //         //         ? number_format($stock->length, 2) . ' ' . $unit
    //         //         : 'N/A';
    //         // })
    //         ->addColumn('size', function ($stock) {
    //             // Safely get the UOM name from related account → uom relation
    //             $unit = optional($stock->account->uom)->name ?? 'N/A';

    //             // If the stock has a length value, show it with unit (e.g. "15.00 Meter")
    //             if (!empty($stock->length)) {
    //                 return number_format($stock->length, 2) . ' ' . $unit;
    //             }

    //             // If no length, still show the unit if available
    //             return $unit !== 'N/A' ? $unit : 'N/A';
    //         })


    //         ->addColumn('warehouse_name', function ($stock) {
    //             return $stock->warehouse->name ?? 'N/A';
    //         })
    //         ->addColumn('available_qty', function ($stock) {
    //             return number_format($stock->quantity, 2);
    //         })
    //         ->addColumn('actions', function ($stock) {
    //             return '<a href="' . route('warehouses.stock-history-raw-materials', ['item_id' => $stock->account_id, 'warehouse_id' => $stock->warehouse_id]) . '" class="btn btn-sm btn-info">History</a>';
    //         })
    //         ->rawColumns(['actions'])
    //         ->make(true);
    // }

    public function getCurrentStockRawMaterialsData(Request $request)
    {
        $search = $request->input('search.value');

        $query = Stock::with(['account.uom', 'warehouse'])
            ->whereHas('account', function ($q) use ($search) {
                $q->where('chart_of_account_id', 52);

                if (!empty($search)) {
                    $q->where(function ($sub) use ($search) {
                        $sub->where('name', 'like', '%' . $search . '%')
                            ->orWhere('code', 'like', '%' . $search . '%')
                            ->orWhere('sku',  'like', '%' . $search . '%');
                    });
                }
            })
            ->select('stocks.*');

        if ($request->filled('warehouse_id')) {
            $query->where('warehouse_id', $request->warehouse_id);
        }

        if ($request->filled('min_qty')) {
            $query->where('quantity', '>=', $request->min_qty);
        }

        if ($request->filled('max_qty')) {
            $query->where('quantity', '<=', $request->max_qty);
        }

        $query->orderByDesc('id');

        return DataTables::of($query)
            ->addColumn('item_name',      fn($s) => $s->account->name ?? 'N/A')
            ->addColumn('item_code',      fn($s) => $s->account->code ?? 'N/A')
            ->addColumn('warehouse_name', fn($s) => $s->warehouse->name ?? 'N/A')
            ->addColumn('available_qty',  fn($s) => number_format($s->quantity, 2))
            ->addColumn('size', function ($s) {
                $unit = optional($s->account->uom)->name ?? '';
                return !empty($s->length)
                    ? number_format($s->length, 2) . ' ' . $unit
                    : ($unit ?: 'N/A');
            })
            ->addColumn('actions', function ($s) {
                return '<a href="' . route('warehouses.stock-history-raw-materials', [
                    'item_id'      => $s->account_id,
                    'warehouse_id' => $s->warehouse_id,
                ]) . '" class="btn btn-sm btn-info">History</a>';
            })
            ->rawColumns(['actions'])
            ->make(true);
    }





    /**
     * Stock History - Finished Goods
     */
    public function stockHistoryFinishedGoods(Request $request)
    {
        $itemID = $request->item_id;
        $warehouseID = $request->warehouse_id;

        $selectedItem = null;
        if ($itemID) {
            $selectedItem = Account::find($itemID);
        }

        $warehouses = Warehouse::active()->get();
        return view('warehouses.stock-history-finished-goods', compact('warehouses', 'itemID', 'warehouseID', 'selectedItem'));
    }

    public function getStockHistoryFinishedGoodsData(Request $request)
    {
        $query = StockLedger::with(['account.uom', 'warehouse', 'manufacturingRun', 'factoryInvoice'])
            ->whereHas('account.chartOfAccount', function ($q) {
                // Ensure we get all finished goods items
                $q->where('chart_of_account_id', 53)
                  ->orWhere('parent_id', 53);
            })
            ->select('stock_ledgers.*');

        // Apply filters
        if ($request->filled('warehouse_id')) {
            $query->where('warehouse_id', $request->warehouse_id);
        }

        if ($request->filled('item_id')) {
            $query->where('account_id', $request->item_id);
        }

        if ($request->filled('txn_type')) {
            if ($request->txn_type === 'inward') {
                $query->where('quantity', '>', 0);
            } elseif ($request->txn_type === 'outward') {
                $query->where('quantity', '<', 0);
            }
        }

        if ($request->filled('date_from')) {
            $query->whereDate('created_at', '>=', $request->date_from);
        }

        if ($request->filled('date_to')) {
            $query->whereDate('created_at', '<=', $request->date_to);
        }

        return DataTables::of($query)
            ->addColumn('date', function ($ledger) {
                return $ledger->created_at ? $ledger->created_at->format('Y-m-d H:i') : 'N/A';
            })
            ->addColumn('txn_marker', function ($ledger) {
                if ($ledger->manufacturing_run_id) {
                    return '<span class="badge bg-info">Manufactured</span>';
                } elseif ($ledger->factory_invoice_id) {
                    return '<span class="badge bg-success">Sold</span>';
                }
                return '<span class="badge bg-secondary">Other</span>';
            })
            ->addColumn('item_name', function ($ledger) {
                return $ledger->account->name ?? 'N/A';
            })
            ->addColumn('item_type', function ($ledger) {
                return 'Finished Goods';
            })
            ->addColumn('size', function ($ledger) {
                $length = $ledger->length ?? $ledger->lenght ?? null;
                $unit = $ledger->account && $ledger->account->uom
                    ? $ledger->account->uom->name
                    : '';
                return $length
                    ? number_format($length, 2) . ' ' . $unit
                    : 'N/A';
            })
            ->addColumn('m_number', function ($ledger) {
                if ($ledger->manufacturing_run_id && $ledger->manufacturingRun) {
                    $url = route('manufacturing.runs.show', $ledger->manufacturing_run_id);
                    return '<a href="' . $url . '">' . $ledger->manufacturingRun->m_number . '</a>';
                }
                return 'N/A';
            })
            ->addColumn('invoice_number', function ($ledger) {
                if ($ledger->factory_invoice_id && $ledger->factoryInvoice) {
                    $url = route('factory.pos.show', $ledger->factory_invoice_id);
                    return '<a href="' . $url . '">' . $ledger->factoryInvoice->invoice_number . '</a>';
                }
                return 'N/A';
            })
            ->addColumn('ac_qty', function ($ledger) {
                return number_format(abs($ledger->quantity), 2);
            })
            ->addColumn('ledger_qty', function ($ledger) {
                return number_format($ledger->quantity, 2);
            })
            ->addColumn('warehouse_name', function ($ledger) {
                return $ledger->warehouse->name ?? 'N/A';
            })
            ->rawColumns(['txn_marker', 'm_number', 'invoice_number'])
            ->make(true);
    }

    /**
     * Stock History - Raw Materials
     */
    public function stockHistoryRawMaterials(Request $request)
    {
        $itemID = $request->item_id;
        $warehouseID = $request->warehouse_id;

        $selectedItem = null;
        if ($itemID) {
            $selectedItem = Account::find($itemID);
        }

        $warehouses = Warehouse::active()->get();
        return view('warehouses.stock-history-raw-materials', compact('warehouses', 'itemID', 'warehouseID', 'selectedItem'));
    }

    public function getStockHistoryRawMaterialsData(Request $request)
    {
        $query = StockLedger::with(['account', 'warehouse', 'manufacturingRun'])
            ->whereHas('account.chartOfAccount', function ($q) {
                $q->where('chart_of_account_id', 52); // Raw Materials
            })
            ->select('stock_ledgers.*');

        // Apply filters
        if ($request->filled('warehouse_id')) {
            $query->where('warehouse_id', $request->warehouse_id);
        }

        if ($request->filled('item_id')) {
            $query->where('account_id', $request->item_id);
        }

        if ($request->filled('txn_type')) {
            if ($request->txn_type === 'inward') {
                $query->where('qty_in', '>', 0);
            } elseif ($request->txn_type === 'outward') {
                $query->where('qty_out', '>', 0);
            }
        }

        if ($request->filled('date_from')) {
            // $query->whereDate('occurred_at', '>=', $request->date_from);
            $query->whereDate('created_at', '>=', $request->date_from);
        }

        if ($request->filled('date_to')) {
            // $query->whereDate('occurred_at', '<=', $request->date_to);
            $query->whereDate('created_at', '<=', $request->date_to);
        }

        return DataTables::of($query)
            ->addColumn('date', function ($ledger) {
                // return $ledger->occurred_at ? $ledger->occurred_at->format('Y-m-d H:i') : 'N/A';
                return $ledger->created_at ? $ledger->created_at->format('Y-m-d H:i') : 'N/A';
            })
            ->addColumn('item_name', function ($ledger) {
                return $ledger->account->name ?? 'N/A';
            })
            ->addColumn('item_type', function ($ledger) {
                return 'Raw Materials';
            })
            ->addColumn('txn_marker', function ($ledger) {
                if ($ledger->igp_id || $ledger->qc_id || $ledger->po_id) {
                    return '<span class="badge bg-success">Purchase</span>';
                } elseif ($ledger->manufacturing_run_id) {
                    return '<span class="badge bg-info">Manufacturing</span>';
                }
                return '<span class="badge bg-secondary">Other</span>';
            })
            ->addColumn('po_number', function ($ledger) {
                return $ledger->po_id ? 'PO-' . $ledger->po_id : 'N/A';
            })
            ->editColumn('po_qty', function ($ledger) {
                return $ledger->po_id ? ($ledger->purchaseOrderItem->quantity ?? 'N/A') : 'N/A';
            })
            ->addColumn('igp_number', function ($ledger) {
                return $ledger->igp_id ? 'IGP-' . $ledger->igp_id : 'N/A';
            })
            ->addColumn('qc_number', function ($ledger) {
                return $ledger->qc_id ? 'QC-' . $ledger->qc_id : 'N/A';
            })
            ->addColumn('m_number', function ($ledger) {
                if ($ledger->manufacturing_run_id && $ledger->manufacturingRun) {
                    $url = route('manufacturing.runs.show', $ledger->manufacturing_run_id);
                    return '<a href="' . $url . '">' . $ledger->manufacturingRun->m_number . '</a>';
                }
                return 'N/A';
            })
            ->addColumn('usage_qty', function ($ledger) {
                if ($ledger->manufacturing_run_id) {
                    return number_format($ledger->qty_out ?: $ledger->piece_quantity, 2);
                }
                return 'N/A';
            })
            ->addColumn('ac_qty', function ($ledger) {
                return number_format($ledger->qty_in, 2);
            })
            ->addColumn('ledger_qty', function ($ledger) {
                return number_format($ledger->qty_in - $ledger->qty_out, 2);
            })
            ->addColumn('warehouse_name', function ($ledger) {
                return $ledger->warehouse->name ?? 'N/A';
            })
            ->rawColumns(['txn_marker', 'm_number'])
            ->make(true);
    }

    // ===== FACTORY INVOICE PICKING METHODS =====

    /**
     * Show pending factory invoices for warehouse picking
     */
    public function pendingFactoryInvoices()
    {
        return view('warehouse.factory-invoices.index');
    }

    /**
     * Get pending factory invoices data
     */
    public function getPendingFactoryInvoices()
    {
        $invoices = FactoryInvoice::where('status', 'warehouse_pending')
                                 ->with(['customer', 'supervisingEmployee', 'items.item'])
                                 ->orderBy('created_at', 'desc')
                                 ->get();

        return DataTables::of($invoices)
            ->addColumn('customer_name', function ($invoice) {
                return $invoice->customer->name ?? 'N/A';
            })
            ->addColumn('supervisor', function ($invoice) {
                return $invoice->supervisingEmployee->name ?? 'N/A';
            })
            ->addColumn('item_count', function ($invoice) {
                return $invoice->items->count();
            })
            ->addColumn('total_quantity', function ($invoice) {
                return $invoice->items->sum('ordered_quantity');
            })
            ->addColumn('actions', function ($invoice) {
                return '<a href="' . route('warehouse.factory-invoices.confirm', $invoice) . '" class="btn btn-sm btn-primary">Confirm Picking</a>';
            })
            ->rawColumns(['actions'])
            ->make(true);
    }

    /**
     * Show picking confirmation form for a factory invoice
     */
    public function confirmFactoryInvoicePicking(FactoryInvoice $invoice)
    {
        if (!$invoice->isPendingWarehouse()) {
            abort(403, 'Invoice is not pending warehouse action');
        }

        $invoice->load(['customer', 'supervisingEmployee', 'items.item']);

        return view('warehouse.factory-invoices.confirm-picking', compact('invoice'));
    }

    /**
     * Process warehouse picking confirmation
     */
    public function processFactoryInvoicePicking(Request $request, FactoryInvoice $invoice)
    {
        if (!$invoice->isPendingWarehouse()) {
            abort(403, 'Invoice is not pending warehouse action');
        }

        $request->validate([
            'items' => 'required|array',
            'items.*.id' => 'required|exists:factory_invoice_items,id',
            'items.*.picked_quantity' => 'required|numeric|min:0',
        ]);

        DB::transaction(function () use ($request, $invoice) {
            foreach ($request->items as $itemData) {
                $invoiceItem = FactoryInvoiceItem::find($itemData['id']);

                // Ensure the item belongs to this invoice
                if ($invoiceItem->factory_invoice_id !== $invoice->id) {
                    continue;
                }

                // Update picked quantity (may be different from ordered if out of stock)
                $invoiceItem->update([
                    'picked_quantity' => $itemData['picked_quantity']
                ]);
            }

            // Update invoice status and metadata
            $invoice->update([
                'status' => 'picked',
                'warehouse_picked_at' => now(),
                'warehouse_picked_by' => auth()->id(),
            ]);
        });

        return redirect()->route('warehouse.factory-invoices.index')
                        ->with('success', 'Factory invoice picking confirmed successfully');
    }
}
