<?php

namespace Modules\AdvancedTransfer\Http\Controllers;

use App\Brands;
use App\Category;
use App\Http\Controllers\Controller;
use App\PurchaseLine;
use App\TaxRate;
use App\Unit;
use App\Utils\ModuleUtil;
use App\Utils\ProductUtil;
use App\Utils\Util;
use Illuminate\Http\Request;
use Illuminate\Http\Response;
use Illuminate\Support\Facades\DB;
use Modules\AdvancedTransfer\Entities\NstPrepareStock;
use Modules\AdvancedTransfer\Entities\NstSetting;
use Modules\AdvancedTransfer\Entities\NstShipping;
use Modules\AdvancedTransfer\Entities\Services\CategoryService;
use Modules\AdvancedTransfer\Entities\Services\LocationsService;
use Modules\AdvancedTransfer\Entities\Services\TransactionService;
use Yajra\DataTables\Facades\DataTables;

class TpsPrepareStockController extends Controller
{
    protected $moduleUtil;
    protected $locationsService;
    protected $categoryService;
    protected $transactionService;
    protected $util;
    protected $productUtil;

    public function __construct(TransactionService $transactionService, ProductUtil $productUtil, ModuleUtil $moduleUtil, LocationsService $locationsService, CategoryService $categoryService, Util $util)
    {
        $this->transactionService = $transactionService;
        $this->categoryService = $categoryService;
        $this->locationsService = $locationsService;
        $this->util = $util;
        $this->moduleUtil = $moduleUtil;
        $this->productUtil = $productUtil;
    }

    /**
     * Prepare stock index.
     * @return Response
     */
    public function index()
    {
        if (!auth()->user()->can('tpsTransfer.access_prepare_stock_transfer')) {
            abort(403, 'Unauthorized action.');
        }

        $businessId = request()->session()->get('user.business_id');
        $is_woocommerce = $this->moduleUtil->isModuleInstalled('Woocommerce');

        $categories = Category::forDropdown($businessId, 'product');
        $brands = Brands::forDropdown($businessId);
        $units = Unit::forDropdown($businessId);
        $tax_dropdown = TaxRate::forBusinessDropdown($businessId, false);
        $taxes = $tax_dropdown['tax_rates'];

        $fromBusinessLocations = $this->locationsService->accessLocationsForUserforDropdown($businessId, 'from', false, true, 'prepare');
        $toBusinessLocations = $this->locationsService->accessLocationsForUserforDropdown($businessId, 'to', false, true, 'prepare');

        return view('tpsTransfer::prepare')->with(compact(
            'categories',
            'brands',
            'units',
            'taxes',
            'fromBusinessLocations',
            'toBusinessLocations',
            'is_woocommerce'
        ));
    }

    /**
     * Prepare stock table.
     * @return Response
     */
    public function table()
    {
        if (!auth()->user()->can('tpsTransfer.access_prepare_stock_transfer')) {
            abort(403, 'Unauthorized action.');
        }

        $businessId = request()->session()->get('user.business_id');
        //Filter by location
        $from_location_id = request()->get('from_location_id', null);
        $to_location_id = request()->get('to_location_id', null);

        $query = NstPrepareStock::query()
            ->where('nst_prepare_products.status', 'prepare')
            ->where('nst_prepare_products.business_id', $businessId)
            ->leftJoin('users', 'users.id', '=', 'nst_prepare_products.created_by')
            ->leftJoin('business_locations as from_business_locations', 'from_business_locations.id', '=', 'nst_prepare_products.from_location_id')
            ->leftJoin('products', 'products.id', '=', 'nst_prepare_products.product_id')
            ->leftJoin('categories as c1', 'products.category_id', '=', 'c1.id')
            ->leftJoin('categories as c2', 'products.sub_category_id', '=', 'c2.id')
            ->leftJoin('brands', 'products.brand_id', '=', 'brands.id')
            ->join('units', 'products.unit_id', '=', 'units.id')
            ->leftJoin('tax_rates', 'products.tax', '=', 'tax_rates.id')
            ->leftJoin('variations', 'variations.id', '=', 'nst_prepare_products.variation_id')
            ->join('product_variations as pv', 'pv.id', '=', 'variations.product_variation_id')
            ->leftJoin('purchase_lines', 'purchase_lines.id', '=', 'nst_prepare_products.purchase_lines_id')
            ->leftJoin('business_locations as to_business_locations', 'to_business_locations.id', '=', 'nst_prepare_products.to_location_id')
            ->whereNull('nst_prepare_products.deleted_at')
            ->leftJoin('variation_location_details as to_vld', function ($join) {
                $join->on('variations.id', '=', 'to_vld.variation_id')
                    ->whereRaw('to_vld.location_id = to_business_locations.id');
            })
            ->leftJoin('variation_location_details as from_vld', function ($join) {
                $join->on('variations.id', '=', 'from_vld.variation_id')
                    ->whereRaw('from_vld.location_id = from_business_locations.id');
            });

        if (!empty($from_location_id) && $from_location_id != 'none') {
            $query->where('nst_prepare_products.from_location_id', '=', $from_location_id);
        } else {
            $query->whereRaw('1 = 2');
        }

        if (!empty($to_location_id) && $to_location_id != 'none') {
            $query->where('nst_prepare_products.to_location_id', '=', $to_location_id);
        }

        $query = $query->select(
            'nst_prepare_products.id',
            'nst_prepare_products.transaction_date',
            'nst_prepare_products.ref_no',
            'users.id as user_id',
            'users.surname as user_surname',
            'from_location_id',
            'from_business_locations.name as from_location_name',
            'products.id as product_id',
            'products.name as product_name',
            'products.sku',
            'products.type',
            'c1.name as category',
            'c2.name as sub_category',
            'units.actual_name as unit',
            'brands.name as brand',
            'tax_rates.name as tax',
            'variations.id as variation_id',
            'variations.name as variation_name',
            'pv.name as product_variation_name',
            'purchase_lines.id as purchase_line_id',
            'purchase_lines.lot_number',
            'purchase_lines.purchase_price',
            DB::raw('(purchase_lines.quantity - purchase_lines.quantity_sold - purchase_lines.quantity_adjusted - purchase_lines.quantity_returned + purchase_lines.mfg_quantity_used) as purchase_lines_quantity'),
            'purchase_lines.purchase_price_inc_tax',
            'nst_prepare_products.transfer_quantity',
            'to_location_id',
            'to_business_locations.name as to_location_name',
            'nst_prepare_products.is_important',
            'nst_prepare_products.note',
            'nst_prepare_products.created_at',
            DB::raw('to_vld.qty_available as to_qty_available'),
            DB::raw('from_vld.qty_available as from_qty_available')
        );
        if (!request()->get('order')) {
            $query
                ->orderBy('nst_prepare_products.is_important', 'DESC')
                ->orderByRaw('ISNULL(nst_prepare_products.note), nst_prepare_products.note ASC')
                ->orderBy('to_qty_available', 'ASC')
                ->orderBy('products.id', 'ASC');
        }

        $type = request()->get('type', null);
        if (!empty($type)) {
            $query->where('products.type', $type);
        }

        $category_id = request()->get('category_id', null);
        if (!empty($category_id)) {
            $query->where('products.category_id', $category_id);
        }

        $categoryId = request()->get('category_id', null);
        $subCategoryId = request()->get('sub_category_id', null);

        if ($subCategoryId && $subCategoryId != 'all') {
            if ($subCategoryId) {
                $query->where('products.sub_category_id', $subCategoryId);
            }
            if ($categoryId) {
                $query->where('products.category_id', $categoryId);
            }
        }

        $brand_id = request()->get('brand_id', null);
        if (!empty($brand_id)) {
            $query->where('products.brand_id', $brand_id);
        }

        $lotDataBatch = collect();
        $variationIds = (clone $query)->pluck('variation_id')->unique()->toArray();
        $locationIds = (clone $query)->pluck('from_location_id')->unique()->filter()->toArray();
        if (!empty($variationIds) && !empty($locationIds)) {
            $lotDataBatch = $this->transactionService->getLotDataBatch($variationIds, $businessId, $locationIds);
        }

        return Datatables::of($query)
            ->editColumn('created_at', '{{@format_datetime($created_at)}}')
            ->addColumn('data-href', function ($row) {
                return action('\\App\Http\Controllers\ProductController@view', [$row->product_id]);
            })
            ->addColumn('row_checkbox', function ($row) {
                return "<input type='checkbox' class='product-row-input' value='1' data-id='{$row->id}' data-name='accepted'> ";
            })
            ->editColumn('from_location_name', function ($row) {
                $from_qty_available = $row->from_qty_available;
                if($row->purchase_line_id){
                    $from_qty_available = $row->purchase_lines_quantity;
                }
                return $row->from_location_name . ' (' . intval($from_qty_available) . ')';
            })
            ->editColumn('to_location_name', function ($row) {
                return $row->to_location_name . ' (' . intval($row->to_qty_available) . ')';
            })
            ->editColumn('from_qty_available', function ($row) {
                if ($row->purchase_line_id) {
                    return $row->purchase_lines_quantity;
                }
                return $row->from_qty_available;
            })
            ->addColumn('transfer_quantity_input', function ($row) {
                $from_qty_available = $row->from_qty_available;
                if ($row->purchase_line_id) {
                    $from_qty_available = $row->purchase_lines_quantity;
                }

                $html = "<input id='tQty_{$row->id}' data-id='{$row->id}' data-name='transfer_quantity' class='form-control product-row-input transfer_quantity row-select' style='width:100%' type='number' min='1' max='" . intval($from_qty_available) . "' value='" . intval($row->transfer_quantity) . "'>";

                return $html;
            })//from_qty_available, purchase_lines_quantity
            ->addColumn('actions', function ($row) {
                $class = is_null($row->note) ? '' : 'btn-success';
                $html = "
                <div class='btn-group'>
                    <div class='dropdown actions-list'>
                        <button class='btn btn-primary {$class} dropdown-toggle' type='button' data-toggle='dropdown' id='actions_btn_{$row->id}'>" . __('messages.actions') . "</button>
                        <div class='dropdown-menu dropdown-menu-right'>
                        <li><a target='_blank' href='" . action('\Modules\AdvancedTransfer\Http\Controllers\TpsLabelsController@show', ['product_id' => $row->product_id, 'qty' => intval($row->transfer_quantity), 'purchase_line_id' => intval($row->purchase_line_id)]) . "' data-toggle='tooltip' title='" . __('lang_v1.label_help') . "'><i class='fa fa-barcode'></i> " . __('barcode.labels') . "</a></li>
                        <li><a data-toggle='modal' data-target='#add_notes' id='note_{$row->id}' data-id='{$row->id}' class='btn dropdown-item add_notes' type='button'>" . __('tpsTransfer::lang.note') . "</a></li>";
                if (auth()->user()->can('tpsTransfer.can_delete_nst_row')) {
                    $html .= "<li><a data-toggle='modal' data-target='#delete_confirm' id='delete_{$row->id}' data-id='{$row->id}' class='btn dropdown-item delete_row_button' type='button'>" . __('messages.delete') . "</a></li>";
                }
                $html .= "
                        </div>
                    </div>
                </div>";
                return $html;
            })
            ->editColumn('category', '{{$category}} @if(!empty($sub_category))<br/> -- {{$sub_category}}@endif')
            ->editColumn('product', function ($row) {
                $name = $row->product;
                if ($row->variation_name && $row->variation_name != 'DUMMY') {
                    $name .= ' (' . $row->variation_name . ')';
                }
                return $name;
            })
            ->editColumn('image', function ($row) {
                return '<div style="display: flex;"><img src="' . $row->image_url . '" alt="Product image" class="product-thumbnail-small"></div>';
            })
            ->editColumn('lot_number', function ($row) {
                // Match the add-page convention: when a lot has no lot_number,
                // show the purchase price prefixed with "(P) " so the row is
                // still identifiable instead of appearing blank.
                if (!empty($row->lot_number)) {
                    return $row->lot_number;
                }
                if (!is_null($row->purchase_price)) {
                    return '(P) ' . round($row->purchase_price, 2);
                }
                return '';
            })
            ->editColumn('purchase_price', function ($row) use ($businessId, $lotDataBatch) {
                if ($row->purchase_price) {
                    return round($row->purchase_price, 2);
                }

                //add lot select list
                $avgPurchasePrice = 0;
                $count = 0;

                $lotNumbers = $lotDataBatch->get($row->variation_id . '_' . $row->from_location_id, collect());

                if ($lotNumbers->isNotEmpty()) {
                    foreach ($lotNumbers as $lotNumber) {
                        $avgPurchasePrice += $lotNumber->purchase_price * $lotNumber->qty_available;
                        $count += $lotNumber->qty_available;
                    }
                    if ($count) {
                        $avgPurchasePrice = $avgPurchasePrice / $count;
                    }
                }
                return round($avgPurchasePrice, 2);
            })
            ->editColumn('type', '@lang("lang_v1." . $type)')
            ->editColumn('transfer_quantity', function ($row) {
                return intval($row->transfer_quantity);
            })
            ->filterColumn('products.sku', function ($query, $keyword) {
                $query->whereHas('product.variations', function ($q) use ($keyword) {
                    $q->where('sub_sku', 'like', "%{$keyword}%");
                })
                    ->orWhere('products.sku', 'like', "%{$keyword}%");
            })
            ->setRowAttr([
                'data-href' => function ($row) {
                    return action('\Modules\AdvancedTransfer\Http\Controllers\TpsController@showInfoModal', [$row->id]);
                },
                'class' => function ($row) {
                    if ($row->to_qty_available == 0) {
                        return 'bg-red';
                    }
                    return '';
                },
            ])
            ->rawColumns(['actions', 'row_checkbox', 'product', 'selling_price', 'purchase_price', 'category', 'transfer_quantity_input'])
            ->make(true);
    }

    public function store(Request $request)
    {
        if (!auth()->user()->can('tpsTransfer.access_prepare_stock_transfer')) {
            abort(403, 'Unauthorized action.');
        }

        // manualTpsData / autoTpsData are sent as JSON strings to avoid PHP's
        // max_input_vars limit. Decode them back into the request so all
        // downstream request()->get('manualTpsData') calls work unchanged.
        if (is_string($request->manualTpsData)) {
            $request->merge(['manualTpsData' => json_decode($request->manualTpsData, true) ?: []]);
        }
        if (is_string($request->autoTpsData)) {
            $request->merge(['autoTpsData' => json_decode($request->autoTpsData, true) ?: []]);
        }

        if (!$request->manualTpsData || count($request->manualTpsData) == 0) {
            return response()->json(['status' => 'error']);
        }

        try {
            DB::beginTransaction();

            $businessId = request()->session()->get('user.business_id');
            $transactionDate = $this->productUtil->uf_date(request()->get('transaction_date', null), true);
            $createdBy = auth()->user()->id;
            $createdAt = now();
            $updatedAt = now();
            $isShippingCalculated = false;
            $nstShippingId = null;

            $manualTpsData = collect(request()->get('manualTpsData', []));
            $variationIds = $manualTpsData->pluck('variation_id')->filter()->unique()->toArray();
            $locationIds = $manualTpsData->pluck('from_location_id')->filter()->unique()->toArray();
            $lotDataBatch = $this->transactionService->getLotDataBatch($variationIds, $businessId, $locationIds);

            foreach (array_chunk(request()->get('manualTpsData', null), 500, true) as $chunkedData) {
                $extraData = [];
                foreach ($chunkedData as $index => $data) {
                    $lotNumbers = $lotDataBatch->get(
                        $chunkedData[$index]['variation_id'] . '_' . $chunkedData[$index]['from_location_id'], collect()
                    );
                    $chunkedData[$index]['business_id'] = $businessId;
                    $chunkedData[$index]['transaction_date'] = $transactionDate;
                    $chunkedData[$index]['created_by'] = $createdBy;
                    $chunkedData[$index]['created_at'] = $createdAt;
                    $chunkedData[$index]['updated_at'] = $updatedAt;
                    $chunkedData[$index]['is_shipping_calculated'] = $isShippingCalculated;
                    $chunkedData[$index]['nst_shipping_id'] = $nstShippingId;
                    unset($chunkedData[$index]['custom_field']);

                    if ($lotNumbers->count() > 1 && $chunkedData[$index]['transfer_quantity'] > $lotNumbers->first()->qty_available) {
                        $remainingQuantity = $chunkedData[$index]['transfer_quantity'] - $lotNumbers->first()->qty_available;
                        $chunkedData[$index]['transfer_quantity'] = $lotNumbers->first()->qty_available;
                        $chunkedData[$index]['purchase_lines_id'] = $lotNumbers->first()->purchase_line_id;

                        $productData = array_merge([], $chunkedData[$index]);
                        foreach ($lotNumbers->skip(1) as $lotNumber) {
                            $qty = min($lotNumber->qty_available, $remainingQuantity);
                            $extraData[] = array_merge($productData, [
                                'transfer_quantity' => $qty,
                                'purchase_lines_id' => $lotNumber->purchase_line_id,
                            ]);
                            $remainingQuantity -= $qty;
                            if ($remainingQuantity <= 0)
                                break;
                        }
                    } elseif (empty($chunkedData[$index]['purchase_lines_id']) && $lotNumbers->count() > 0) {
                        // The add-page lot dropdown defaults to "all" (empty data-lot-id),
                        // so if the user did not pick a specific lot we default to the
                        // first available lot (FIFO). This keeps the prepare page's lot
                        // column populated and matches historical behavior where every
                        // nst_prepare_products row had a purchase_lines_id.
                        $chunkedData[$index]['purchase_lines_id'] = $lotNumbers->first()->purchase_line_id;
                    }
                }


                $nstPrepareStockCreation = NstPrepareStock::insert(array_merge(array_values($chunkedData), $extraData));

                if ($nstPrepareStockCreation) {
                    continue;
                }
                DB::rollBack();
                return response()->json(['status' => 'error']);
            }

            DB::commit();
            return response()->json(['status' => 'success']);

        } catch (\Exception $e) {
            DB::rollBack();
            \Log::error('TPS Prepare failed: ' . $e->getMessage());
            return response()->json(['status' => 'error', 'msg' => __('messages.something_went_wrong')]);
        }
    }

    public function update(Request $request)
    {
        if (!auth()->user()->can('tpsTransfer.access_prepare_stock_transfer')) {
            abort(403, 'Unauthorized action.');
        }

        if (!in_array($request->status, ['prepare', 'receive'])) {
            return false;
        }

        $redirect = "/tps-product/{$request->status}-stock";

        foreach ($request->prepareDoneTpsData as $id => $data) {
            unset($data['accepted']);
            $data['status'] = $request->status;

            $nstPrepareStock = NstPrepareStock::findOrFail($id);

            // Check the validity of the quantity from Location X to Location Y
            $checkStatus = $this->transactionService->checkQuantityMismatch($nstPrepareStock, \Arr::get($data, 'transfer_quantity'));

            if (!$checkStatus) {
                return response()->json(['status' => 'error', 'msg' => 'check quantity mismatch']);
            }

            $nstPrepareStockBefore = $nstPrepareStock->replicate();
            $nstPrepareStock->update($data);

            if (!$nstPrepareStock) {
                return response()->json(['status' => 'error']);
            }
            $this->util->activityLog($nstPrepareStock, 'edited', $nstPrepareStockBefore);
        }
        return response()->json(['status' => 'success', 'redirect' => $redirect]);
    }

    public function destroy(Request $request)
    {
        if (!auth()->user()->can('tpsTransfer.access_prepare_stock_transfer')) {
            abort(403, 'Unauthorized action.');
        }

        $nstPrepareStock = NstPrepareStock::findOrFail($request->get('id'));
        if ($nstPrepareStock->destroy($request->get('id'))) {
            $this->util->activityLog($nstPrepareStock, 'deleted');
            return response()->json(['status' => 'success']);
        }
        return response()->json(['status' => 'error']);
    }

    public function destroySelected(Request $request)
    {
        if (!auth()->user()->can('tpsTransfer.access_prepare_stock_transfer')) {
            abort(403, 'Unauthorized action.');
        }

        $nstPrepareStocks = NstPrepareStock::whereIn('id', array_keys($request->prepareDoneTpsData))->get();

        foreach ($nstPrepareStocks as $nstPrepareStock) {
            if ($nstPrepareStock->delete()) {
                $this->util->activityLog($nstPrepareStock, 'deleted');
                continue;
            }
            return response()->json(['status' => 'error', 'msg' => 'there is problem in delete some rows..']);
        }
        return response()->json(['status' => 'success']);
    }

    public function addShippingModal(Request $request)
    {
        $tpsData = $request->tpsData;
        $businessId = request()->session()->get('user.business_id');
        $setting = NstSetting::where('business_id', $businessId)->first()?->value ?? [];

        $payment_types = $this->productUtil->payment_types(null, true, $businessId);
        $payment_line = ['method' => 'cash', 'amount' => 0, 'note' => '', 'card_transaction_number' => '', 'card_number' => '', 'card_type' => '', 'card_holder_name' => '', 'card_month' => '', 'card_year' => '', 'card_security' => '', 'cheque_number' => '', 'bank_account_number' => '', 'is_return' => 0, 'transaction_no' => ''];

        $shippingMethods = [
            'as_charge' => 'keep it as shipping charge',
            'different_ratio' => 'divided by different ratio',
            'equal_ratio' => 'divided equal ratio',
        ];

        return view('tpsTransfer::shipping_modal')->with(compact(
            'shippingMethods', 'payment_types', 'payment_line', 'setting', 'tpsData'
        ));
    }

    public function addShippingTable(Request $request)
    {
        $avgPurchasePrice = [];
        $businessId = request()->session()->get('user.business_id');

        $query = NstPrepareStock::query()
            ->whereIn('nst_prepare_products.id', array_keys((array)$request->tpsData))
            ->where('nst_prepare_products.business_id', $businessId)
            ->where('nst_prepare_products.is_shipping_calculated', '0')
            ->select(
                'nst_prepare_products.id',
                'products.name As products_name',
                'products.type As type',
                'variations.id As variation_id',
                'variations.name As variation_name',
                'business_locations.id As from_location_id',
                'business_locations.name As from_location_name',
                'nst_prepare_products.transfer_quantity',
                'purchase_lines.purchase_price',
                DB::raw("(
                    SELECT COALESCE(SUM(npp.transfer_quantity), 0)
                    FROM nst_prepare_products npp
                    WHERE npp.product_id = nst_prepare_products.product_id
                      AND npp.variation_id = nst_prepare_products.variation_id
                      AND npp.from_location_id = nst_prepare_products.from_location_id
                      AND npp.status IN ('prepare', 'receive')
                      AND npp.deleted_at IS NULL
                      AND npp.id != nst_prepare_products.id
                ) as committed_qty")
            )
            ->join('variations', 'nst_prepare_products.variation_id', '=', 'variations.id')
            ->join('business_locations', 'nst_prepare_products.from_location_id', '=', 'business_locations.id')
            ->leftJoin('purchase_lines', 'nst_prepare_products.purchase_lines_id', '=', 'purchase_lines.id')
            ->join('products', 'nst_prepare_products.product_id', '=', 'products.id');

        $lotDataBatch = collect();
        $variationIds = (clone $query)->pluck('variation_id')->unique()->toArray();
        $locationIds = (clone $query)->pluck('from_location_id')->unique()->filter()->toArray();
        if (!empty($variationIds) && !empty($locationIds)) {
            $lotDataBatch = $this->transactionService->getLotDataBatch($variationIds, $businessId, $locationIds);
        }

        $shipping_method = $request->shipping_method;
        $shipping_charges = $request->shipping_charges;

        $totalTransferQuantity = NstPrepareStock::query()
            ->whereIn('nst_prepare_products.id', array_keys((array)$request->tpsData))
            ->where('nst_prepare_products.is_shipping_calculated', '0')
            ->where('nst_prepare_products.business_id', $businessId)
            ->sum('nst_prepare_products.transfer_quantity');

        if ($totalTransferQuantity == 0) {
            $isDisabled = 'disabled';
            $shippingValuePerUnit = 0;
        } elseif ($shipping_method == 'equal_ratio') {
            $isDisabled = 'disabled';
            $shippingValuePerUnit = $shipping_charges / $totalTransferQuantity;
        } elseif ($shipping_method == 'as_charge') {
            $isDisabled = 'disabled';
            $shippingValuePerUnit = 0;
        } elseif ($shipping_method == 'different_ratio') {
            $isDisabled = '';
            $shippingValuePerUnit = 0;
        }

        return Datatables::of($query)
            ->addColumn('total_price_before_shipping', function ($row) use ($businessId, &$avgPurchasePrice, $lotDataBatch) {
                if ($row->purchase_price) {
                    return round($row->purchase_price * $row->transfer_quantity, 2);
                }

                if (!isset($avgPurchasePrice[$row->id])) {
                    $avgPurchasePrice[$row->id] = [];
                }

                $avgPurchasePrice[$row->id]['total_purchase_price'] = 0;
                //add lot select list
                $count = 0;

                $lotNumbers = $lotDataBatch->get($row->variation_id . '_' . $row->from_location_id, collect());

                if ($lotNumbers->isNotEmpty()) {
                    foreach ($lotNumbers as $lotNumber) {
                        $avgPurchasePrice[$row->id]['total_purchase_price'] += $lotNumber->purchase_price * $lotNumber->qty_available;
                        $count += $lotNumber->qty_available;
                    }
                    if ($count) {
                        $avgPurchasePrice[$row->id]['purchase_price'] = round($avgPurchasePrice[$row->id]['total_purchase_price'] / $count, 2);
                    }
                }
                return "<span class='total_price_before_shipping' id='total_price_before_shipping_{$row->id}'>(avg) " . ($avgPurchasePrice[$row->id]['purchase_price'] * $row->transfer_quantity) . "</span>";
            })
            ->addColumn('shipping_ratio', function ($row) use ($shippingValuePerUnit, $totalTransferQuantity, $isDisabled, &$avgPurchasePrice) {
                if ($row->purchase_price) {
                    $purchasePrice = round($row->purchase_price, 2);
                } else {
                    $purchasePrice = round($avgPurchasePrice[$row->id]['purchase_price'], 2);
                }

                $shippingRatio = 0;
                if ($shippingValuePerUnit) {
                    $shippingRatio = ($row->transfer_quantity / $totalTransferQuantity) * 100;
                }
                return "<input id='shipping_ratio_{$row->id}' class='form-control shipping-inputs shipping_ratio' data-id='{$row->id}' data-transfer-quantity='{$row->transfer_quantity}' data-purchase-price='{$purchasePrice}' data-total-transfer-quantity='{$totalTransferQuantity}' style='width:100%' type='number' min='0' value='{$shippingRatio}' {$isDisabled}>";
            })
            ->addColumn('shipping_value_per_unit', function ($row) use ($shippingValuePerUnit) {
                return "<span class='shipping-inputs shipping_value_per_unit' id='shipping_value_per_unit_{$row->id}'> $shippingValuePerUnit </span>";
            })
            ->editColumn('purchase_price', function ($row) use (&$avgPurchasePrice) {
                if ($row->purchase_price) {
                    return round($row->purchase_price, 2);
                }
                return '(avg) ' . $avgPurchasePrice[$row->id]['purchase_price'];
            })
            ->addColumn('unit_price_after_shipping', function ($row) use (&$avgPurchasePrice, $shippingValuePerUnit) {
                if ($row->purchase_price) {
                    $purchasePrice = round($row->purchase_price, 2);
                } else {
                    $purchasePrice = round($avgPurchasePrice[$row->id]['purchase_price'], 2);
                }

                $unitPriceAfterShipping = round(($purchasePrice) + ($shippingValuePerUnit), 2);
                $avgPurchasePrice[$row->id]['unit_price_after_shipping'] = $unitPriceAfterShipping;
                return "<span class='unit_price_after_shipping' id='unit_price_after_shipping_{$row->id}'> " . $unitPriceAfterShipping . " </span>";
            })
            ->addColumn('total_price_after_shipping', function ($row) use (&$avgPurchasePrice, $shippingValuePerUnit) {
                if ($row->purchase_price) {
                    $purchasePrice = round($row->purchase_price, 2);
                } else {
                    $purchasePrice = round($avgPurchasePrice[$row->id]['purchase_price'], 2);
                }

                $unitPriceAfterShipping = round(($purchasePrice * intval($row->transfer_quantity)) + ($shippingValuePerUnit * intval($row->transfer_quantity)), 2);
                $avgPurchasePrice[$row->id]['unit_price_after_shipping'] = $unitPriceAfterShipping;
                return "<span class='total_price_after_shipping' id='total_price_after_shipping_{$row->id}'> " . $unitPriceAfterShipping . " </span>";
            })
            ->editColumn('products_name', function ($row) {
                $name = $row->products_name;
                if ($row->variation_name && $row->variation_name != 'DUMMY') {
                    $name .= ' (' . $row->variation_name . ')';
                }
                return $name;
            })
            ->editColumn('transfer_quantity', function ($row) {
                return intval($row->transfer_quantity);
            })
            ->addColumn('committed_qty_display', function ($row) {
                if (isset($row->committed_qty) && $row->committed_qty > 0) {
                    return '<span class="label label-warning" title="' . __('tpsTransfer::lang.committed_to_other_transfers') . '">'
                        . __('tpsTransfer::lang.in_transit') . ': '
                        . $row->committed_qty
                        . '</span>';
                }
                return '';
            })
            ->rawColumns(['shipping_ratio', 'shipping_value_per_unit', 'shipping_value_per_unit', 'total_price_before_shipping', 'unit_price_after_shipping', 'total_price_after_shipping', 'committed_qty_display'])
            ->make(true);
    }

    public function storeShipping(Request $request)
    {
        if (!auth()->user()->can('tpsTransfer.access_prepare_stock_transfer')) {
            abort(403, 'Unauthorized action.');
        }

        $data = [];
        parse_str($request->input('data'), $data);
        $from_location_id = (int) $request->input('from_location_id');
        $to_location_id = (int) $request->input('to_location_id');

        if (!$request->tpsShippingCalculation || count($request->tpsShippingCalculation) == 0 || !\Arr::get($data, 'shipping_method')) {
            return response()->json(['status' => 'error', 'msg' => 'main inputs error or mismatch']);
        }

        try {
            DB::beginTransaction();
            $businessId = request()->session()->get('user.business_id');
            $createdBy = auth()->user()->id;

            $nstShipping = NstShipping::create([
                'business_id' => $businessId,
                'shipping_details' => $data['shipping_details'],
                'shipping_method' => $data['shipping_method'],
                'shipping_charges' => $data['shipping_charges'],
                'additional_expense_key_1' => $data['additional_expense_key_1'],
                'additional_expense_value_1' => $data['additional_expense_value_1'],
                'additional_expense_key_2' => $data['additional_expense_key_2'],
                'additional_expense_value_2' => $data['additional_expense_value_2'],
                'additional_expense_key_3' => $data['additional_expense_key_3'],
                'additional_expense_value_3' => $data['additional_expense_value_3'],
                'additional_expense_key_4' => $data['additional_expense_key_4'],
                'additional_expense_value_4' => $data['additional_expense_value_4'],
                'created_by' => $createdBy,
            ]);
            $nstShipping->ref_num = 'NST' . \Carbon::now()->year . '-' . $nstShipping->id;
            $nstShipping->save();

            foreach (array_chunk(request()->get('tpsShippingCalculation', null), 500, true) as $chunkedData) {
                foreach ($chunkedData as $index => $data) {
                    $unitPriceAfterShipping = 0;
                    $nstStock = NstPrepareStock::findOrFail($index);
                    // Validate the location from and to
                    if ((int) $nstStock->from_location_id !== $from_location_id || (int) $nstStock->to_location_id !== $to_location_id) {
                        DB::rollBack();
                        return response()->json(['status' => 'error', 'msg' => 'The location must be same selected locations',]);
                    }
                    if ($nstStock->purchase_lines_id) {
                        $purchaseLine = PurchaseLine::findOrFail($nstStock->purchase_lines_id);

                        $available_qty = $purchaseLine->quantity
                            - ($purchaseLine->quantity_sold ?? 0)
                            - ($purchaseLine->quantity_adjusted ?? 0)
                            - ($purchaseLine->quantity_returned ?? 0);

                        if ($available_qty < $nstStock->transfer_quantity) {
                            DB::rollBack();
                            return response()->json(['status' => 'error', 'msg' => 'Transfer quantity must be less than available lot quantity ',]);
                        }

                        $unitPriceAfterShipping = floatval($purchaseLine->purchase_price) + floatval($data['value_per_unit']);
                    } else {
                        $unitPrices = $this->transactionService->getPurchasePriceFromVariationByQuantity($nstStock->transfer_quantity, $nstStock->variation_id, $businessId, $nstStock->from_location_id);

                        foreach ($unitPrices as $unitPrice) {
                            $unitPriceAfterShipping += floatval($unitPrice['purchase_price']) * intval($unitPrice['quantity']);
                        }

                        $unitPriceAfterShipping = ($unitPriceAfterShipping / intval($nstStock->transfer_quantity)) + floatval($data['value_per_unit']);
                    }

                    $nstStock->shipping_ratio = $data['shipping_ratio'];
                    $nstStock->value_per_unit = $data['value_per_unit'];
                    $nstStock->unit_price_after_shipping = $unitPriceAfterShipping;
                    $nstStock->nst_shipping_id = $nstShipping->id;
                    //Check if this NST row is already have shipping calculation
                    if ($nstStock->is_shipping_calculated == 1) {
                        DB::rollBack();
                        return response()->json(['status' => 'error', 'msg' => 'product already have shipping calculation.']);
                    }
                    $nstStock->is_shipping_calculated = true;
                    $nstStock->status = 'receive';
                    $nstStock->save();
                    if (!$nstStock) {
                        DB::rollBack();
                        return response()->json(['status' => 'error', 'msg' => 'unexpected error.']);
                    }
                }
            }
            DB::commit();
            return response()->json(['status' => 'success', 'redirect' => "/tps-product/receive-stock"]);

        } catch (\Exception $e) {
            DB::rollBack();
            \Log::error('TPS Prepare Shipping failed: ' . $e->getMessage());
            return response()->json(['status' => 'error', 'msg' => __('messages.something_went_wrong')]);
        }
    }

    public function getSqlWithBindings($query)
    {
        return vsprintf(str_replace('?', '%s', $query->toSql()), collect($query->getBindings())->map(function ($binding) {
            return is_numeric($binding) ? $binding : "'{$binding}'";
        })->toArray());
    }

}
