<?php

namespace Modules\PurchaseRequisition\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Routing\Controller;
use Modules\PurchaseRequisition\Entities\PrRequest;
use Modules\PurchaseRequisition\Entities\PrProduct;
use Modules\PurchaseRequisition\Entities\PrSetting;
use Modules\PurchaseRequisition\Entities\Services\CalculationService;
use Modules\PurchaseRequisition\Entities\Services\LocationService;
use Yajra\DataTables\Facades\DataTables;
use App\Product;
use App\Variation;
use App\Brands;
use App\Category;
use App\Utils\ProductUtil;
use DB;

class PrGenerateController extends Controller
{
    protected $calculationService;
    protected $locationService;
    protected $productUtil;

    public function __construct(
        CalculationService $calculationService,
        LocationService $locationService,
        ProductUtil $productUtil
    ) {
        $this->calculationService = $calculationService;
        $this->locationService = $locationService;
        $this->productUtil = $productUtil;
    }

    /**
     * Display generation page.
     *
     * @return \Illuminate\View\View
     */
    public function index()
    {
        if (!auth()->user()->can('purchase_requisition.create')) {
            abort(403, 'Unauthorized action.');
        }

        $businessId = request()->session()->get('user.business_id');

        // Get settings
        $settings = PrSetting::getSettings($businessId);

        // Get brands, categories for filters
        $brands = Brands::forDropdown($businessId);
        $categories = Category::forDropdown($businessId, 'product');

        return view('purchaseRequisition::generate.index', compact('settings', 'brands', 'categories'));
    }

    /**
     * Get products for DataTable or Auto Generate with calculations.
     * Uses optimized batch queries for performance.
     *
     * @param Request $request
     * @return \Illuminate\Http\JsonResponse
     */
    public function getProducts(Request $request)
    {
        try {
            if (!auth()->user()->can('purchase_requisition.create')) {
                return response()->json(['error' => 'Unauthorized'], 403);
            }

            $businessId = request()->session()->get('user.business_id');

            // Load saved settings for avg_cost_period
            $savedSettings = PrSetting::getSettings($businessId);

            $settings = [
                'selling_interval' => $request->selling_interval ?? 365,
                'stock_cover_interval' => $request->stock_cover_interval ?? 180,
                'avg_cost_period' => $savedSettings['avg_cost_period'] ?? 'all',
            ];

            // Simple query without complex JOINs for better performance
            $query = Variation::join('products', 'variations.product_id', '=', 'products.id')
                ->leftJoin('brands', 'products.brand_id', '=', 'brands.id')
                ->leftJoin('categories', 'products.category_id', '=', 'categories.id')
                ->leftJoin('categories as sub_cat', 'products.sub_category_id', '=', 'sub_cat.id')
                ->where('products.business_id', $businessId)
                ->where('products.type', '!=', 'modifier')
                ->where('products.is_inactive', 0)
                ->select([
                    'variations.id as variation_id',
                    'variations.product_id',
                    'variations.name as variation_name',
                    'products.name as product_name',
                    'products.type as product_type',
                    'products.sku',
                    'variations.sub_sku',
                    'variations.sell_price_inc_tax',
                    'variations.dpp_inc_tax',
                    'brands.name as brand_name',
                    'categories.name as category_name',
                    'sub_cat.name as sub_category_name',
                    'products.product_custom_field4',
                ]);

            // Apply filters
            if ($request->brands && !empty($request->brands)) {
                $query->whereIn('products.brand_id', $request->brands);
            }

            if ($request->categories && !empty($request->categories)) {
                $query->whereIn('products.category_id', $request->categories);
            }

            if ($request->subcategories && !empty($request->subcategories)) {
                $query->whereIn('products.sub_category_id', $request->subcategories);
            }

            // Filter by specific variation IDs (for inserting selected products from modal)
            if ($request->variation_ids && !empty($request->variation_ids)) {
                $query->whereIn('variations.id', $request->variation_ids);
            }

            // If auto_generate flag is set, use CHUNKED LOADING for better performance
            if ($request->auto_generate) {
                $startDate = now()->subDays($settings['selling_interval'])->format('Y-m-d');
                $sellingInterval = (int) $settings['selling_interval'];
                $stockCoverInterval = (int) $settings['stock_cover_interval'];

                // Pagination parameters for chunked loading
                $offset = (int) ($request->offset ?? 0);
                $limit = (int) ($request->limit ?? 200); // Default chunk size
                $countOnly = $request->count_only ?? false;

                // Build filter conditions for WHERE clause
                $filterConditions = ["products.business_id = ?", "products.type != 'modifier'", "products.is_inactive = 0"];
                $whereBindings = [$businessId];

                if ($request->brands && !empty($request->brands)) {
                    $placeholders = implode(',', array_fill(0, count($request->brands), '?'));
                    $filterConditions[] = "products.brand_id IN ({$placeholders})";
                    $whereBindings = array_merge($whereBindings, $request->brands);
                }
                if ($request->categories && !empty($request->categories)) {
                    $placeholders = implode(',', array_fill(0, count($request->categories), '?'));
                    $filterConditions[] = "products.category_id IN ({$placeholders})";
                    $whereBindings = array_merge($whereBindings, $request->categories);
                }
                if ($request->subcategories && !empty($request->subcategories)) {
                    $placeholders = implode(',', array_fill(0, count($request->subcategories), '?'));
                    $filterConditions[] = "products.sub_category_id IN ({$placeholders})";
                    $whereBindings = array_merge($whereBindings, $request->subcategories);
                }
                if ($request->variation_ids && !empty($request->variation_ids)) {
                    $placeholders = implode(',', array_fill(0, count($request->variation_ids), '?'));
                    $filterConditions[] = "v.id IN ({$placeholders})";
                    $whereBindings = array_merge($whereBindings, $request->variation_ids);
                }

                $whereClause = implode(' AND ', $filterConditions);

                // If count_only, just return the total count (fast query)
                if ($countOnly) {
                    $countSql = "
                        SELECT COUNT(*) as total
                        FROM variations v
                        INNER JOIN products ON products.id = v.product_id
                        WHERE {$whereClause}
                    ";
                    $countResult = DB::select($countSql, $whereBindings);
                    $total = $countResult[0]->total ?? 0;

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

                // STEP 1: Get variation IDs for this chunk ONLY (fast query)
                $idsSql = "
                    SELECT v.id as variation_id
                    FROM variations v
                    INNER JOIN products ON products.id = v.product_id
                    WHERE {$whereClause}
                    ORDER BY products.name, v.name
                    LIMIT {$limit} OFFSET {$offset}
                ";
                $idsResult = DB::select($idsSql, $whereBindings);
                $variationIds = array_map(function($row) { return $row->variation_id; }, $idsResult);

                if (empty($variationIds)) {
                    return response()->json([
                        'success' => true,
                        'data' => [],
                        'offset' => $offset,
                        'limit' => $limit,
                        'count' => 0
                    ]);
                }

                // STEP 2: Get basic product info for these variation IDs
                $varIdPlaceholders = implode(',', array_fill(0, count($variationIds), '?'));
                $productsSql = "
                    SELECT
                        v.id as variation_id,
                        v.product_id,
                        v.name as variation_name,
                        products.name as product_name,
                        products.type as product_type,
                        products.sku,
                        v.sub_sku,
                        v.sell_price_inc_tax,
                        v.dpp_inc_tax,
                        brands.name as brand_name,
                        categories.name as category_name,
                        sub_cat.name as sub_category_name,
                        products.product_custom_field4
                    FROM variations v
                    INNER JOIN products ON products.id = v.product_id
                    LEFT JOIN brands ON brands.id = products.brand_id
                    LEFT JOIN categories ON categories.id = products.category_id
                    LEFT JOIN categories sub_cat ON sub_cat.id = products.sub_category_id
                    WHERE v.id IN ({$varIdPlaceholders})
                    ORDER BY products.name, v.name
                ";
                $products = DB::select($productsSql, $variationIds);

                // STEP 3: Batch queries for calculations (only for these variation IDs)
                // Stock data
                $stockData = DB::table('variation_location_details')
                    ->join('business_locations', 'business_locations.id', '=', 'variation_location_details.location_id')
                    ->whereIn('variation_location_details.variation_id', $variationIds)
                    ->where('business_locations.business_id', $businessId)
                    ->groupBy('variation_location_details.variation_id')
                    ->selectRaw('variation_location_details.variation_id, SUM(variation_location_details.qty_available) as stock')
                    ->pluck('stock', 'variation_id')
                    ->toArray();

                // Pending purchase data
                $pendingData = DB::table('purchase_lines')
                    ->join('transactions', 'transactions.id', '=', 'purchase_lines.transaction_id')
                    ->join('business_locations', 'business_locations.id', '=', 'transactions.location_id')
                    ->whereIn('purchase_lines.variation_id', $variationIds)
                    ->where('business_locations.business_id', $businessId)
                    ->where('transactions.type', 'purchase')
                    ->whereIn('transactions.status', ['ordered', 'pending', 'partial'])
                    ->groupBy('purchase_lines.variation_id')
                    ->selectRaw('purchase_lines.variation_id, SUM(purchase_lines.quantity - purchase_lines.quantity_returned) as pending')
                    ->pluck('pending', 'variation_id')
                    ->toArray();

                // Sales data
                $salesData = DB::table('transaction_sell_lines')
                    ->join('transactions', 'transactions.id', '=', 'transaction_sell_lines.transaction_id')
                    ->join('business_locations', 'business_locations.id', '=', 'transactions.location_id')
                    ->whereIn('transaction_sell_lines.variation_id', $variationIds)
                    ->where('business_locations.business_id', $businessId)
                    ->where('transactions.type', 'sell')
                    ->where('transactions.status', 'final')
                    ->where('transactions.transaction_date', '>=', $startDate)
                    ->groupBy('transaction_sell_lines.variation_id')
                    ->selectRaw('transaction_sell_lines.variation_id, SUM(transaction_sell_lines.quantity) - SUM(COALESCE(transaction_sell_lines.quantity_returned, 0)) as net_sold')
                    ->pluck('net_sold', 'variation_id')
                    ->toArray();

                // Avg cost data - Weighted average based on quantity
                // Formula: SUM(price * net_qty) / SUM(net_qty)
                // where net_qty = quantity - quantity_returned
                // Period: based on setting (all time or last 365 days)
                $avgCostQuery = DB::table('purchase_lines')
                    ->join('transactions', 'transactions.id', '=', 'purchase_lines.transaction_id')
                    ->join('business_locations', 'business_locations.id', '=', 'transactions.location_id')
                    ->whereIn('purchase_lines.variation_id', $variationIds)
                    ->where('business_locations.business_id', $businessId)
                    ->where('transactions.type', 'purchase')
                    ->where('transactions.status', 'received');

                // Apply period filter if set to year_ago
                if ($settings['avg_cost_period'] === 'year_ago') {
                    $avgCostQuery->where('transactions.transaction_date', '>=', now()->subDays(365)->format('Y-m-d'));
                }

                $avgCostData = $avgCostQuery
                    ->groupBy('purchase_lines.variation_id')
                    ->selectRaw('
                        purchase_lines.variation_id,
                        CASE
                            WHEN SUM(purchase_lines.quantity - COALESCE(purchase_lines.quantity_returned, 0)) > 0
                            THEN SUM(purchase_lines.purchase_price_inc_tax * (purchase_lines.quantity - COALESCE(purchase_lines.quantity_returned, 0))) / SUM(purchase_lines.quantity - COALESCE(purchase_lines.quantity_returned, 0))
                            ELSE 0
                        END as avg_cost
                    ')
                    ->pluck('avg_cost', 'variation_id')
                    ->toArray();

                // Has previous purchase
                $hasPurchaseData = DB::table('purchase_lines')
                    ->join('transactions', 'transactions.id', '=', 'purchase_lines.transaction_id')
                    ->whereIn('purchase_lines.variation_id', $variationIds)
                    ->where('transactions.type', 'purchase')
                    ->where('transactions.status', 'received')
                    ->distinct()
                    ->pluck('purchase_lines.variation_id')
                    ->flip()
                    ->toArray();

                // Has opening stock
                $hasOpeningStockData = DB::table('purchase_lines')
                    ->join('transactions', 'transactions.id', '=', 'purchase_lines.transaction_id')
                    ->whereIn('purchase_lines.variation_id', $variationIds)
                    ->where('transactions.type', 'opening_stock')
                    ->distinct()
                    ->pluck('purchase_lines.variation_id')
                    ->flip()
                    ->toArray();

                // Last purchased qty - get quantity from most recent purchase for each variation
                $lastPurchasedQtyData = DB::table('purchase_lines')
                    ->join('transactions', 'transactions.id', '=', 'purchase_lines.transaction_id')
                    ->join('business_locations', 'business_locations.id', '=', 'transactions.location_id')
                    ->whereIn('purchase_lines.variation_id', $variationIds)
                    ->where('business_locations.business_id', $businessId)
                    ->where('transactions.type', 'purchase')
                    ->whereIn('transactions.status', ['received', 'delivered'])
                    ->select('purchase_lines.variation_id',
                            DB::raw('(purchase_lines.quantity - COALESCE(purchase_lines.quantity_returned, 0)) as net_qty'),
                            'transactions.transaction_date')
                    ->orderBy('transactions.transaction_date', 'desc')
                    ->get()
                    ->groupBy('variation_id')
                    ->map(function($group) {
                        return $group->first()->net_qty ?? 0;
                    })
                    ->toArray();

                // Last purchased adjusted qty - get quantity_adjusted from most recent purchase
                $lastPurchasedAdjustedQtyData = DB::table('purchase_lines')
                    ->join('transactions', 'transactions.id', '=', 'purchase_lines.transaction_id')
                    ->join('business_locations', 'business_locations.id', '=', 'transactions.location_id')
                    ->whereIn('purchase_lines.variation_id', $variationIds)
                    ->where('business_locations.business_id', $businessId)
                    ->where('transactions.type', 'purchase')
                    ->whereIn('transactions.status', ['received', 'delivered'])
                    ->select('purchase_lines.variation_id',
                            DB::raw('COALESCE(purchase_lines.quantity_adjusted, 0) as adjusted_qty'),
                            'transactions.transaction_date')
                    ->orderBy('transactions.transaction_date', 'desc')
                    ->get()
                    ->groupBy('variation_id')
                    ->map(function($group) {
                        return $group->first()->adjusted_qty ?? 0;
                    })
                    ->toArray();

                // Year ago sold qty - total quantity sold in the past 365 days
                $yearAgoDate = now()->subDays(365)->format('Y-m-d');
                $yearAgoSoldData = DB::table('transaction_sell_lines')
                    ->join('transactions', 'transactions.id', '=', 'transaction_sell_lines.transaction_id')
                    ->join('business_locations', 'business_locations.id', '=', 'transactions.location_id')
                    ->whereIn('transaction_sell_lines.variation_id', $variationIds)
                    ->where('business_locations.business_id', $businessId)
                    ->where('transactions.type', 'sell')
                    ->where('transactions.status', 'final')
                    ->where('transactions.transaction_date', '>=', $yearAgoDate)
                    ->groupBy('transaction_sell_lines.variation_id')
                    ->selectRaw('transaction_sell_lines.variation_id, SUM(transaction_sell_lines.quantity) - SUM(COALESCE(transaction_sell_lines.quantity_returned, 0)) as year_sold')
                    ->pluck('year_sold', 'variation_id')
                    ->toArray();

                // STEP 4: Process results - merge batch data with PHP array lookups (O(1))
                $data = [];
                foreach ($products as $product) {
                    $varId = $product->variation_id;

                    // Get pre-calculated values using fast array lookup
                    $currentStock = (float) ($stockData[$varId] ?? 0);
                    $pendingPurchaseQty = (float) ($pendingData[$varId] ?? 0);
                    $netSold = (float) ($salesData[$varId] ?? 0);
                    $avgUnitCost = round((float) ($avgCostData[$varId] ?? 0), 2);
                    $hasPreviousPurchase = isset($hasPurchaseData[$varId]);
                    $hasOpeningStock = isset($hasOpeningStockData[$varId]);
                    $lastPurchasedQty = (float) ($lastPurchasedQtyData[$varId] ?? 0);
                    $lastPurchasedAdjustedQty = (float) ($lastPurchasedAdjustedQtyData[$varId] ?? 0);
                    $yearAgoSoldQty = (float) ($yearAgoSoldData[$varId] ?? 0);

                    // Calculate covered qty and required qty
                    $dailySalesRate = $sellingInterval > 0 ? $netSold / $sellingInterval : 0;
                    $coveredQty = max(0, round($dailySalesRate * $stockCoverInterval, 2));
                    $requiredQty = max(0, ceil($coveredQty - ($currentStock + $pendingPurchaseQty)));

                    $data[] = [
                        'variation_id' => $varId,
                        'product_id' => $product->product_id,
                        'product_name' => $product->product_name,
                        'variation_name' => $product->variation_name,
                        'product_type' => $product->product_type,
                        'sku' => $product->sku,
                        'sub_sku' => $product->sub_sku,
                        'brand_name' => $product->brand_name,
                        'category_name' => $product->category_name,
                        'sub_category_name' => $product->sub_category_name,
                        'product_custom_field4' => $product->product_custom_field4,
                        'current_stock' => $currentStock,
                        'covered_qty' => $coveredQty,
                        'pending_purchase_qty' => $pendingPurchaseQty,
                        'required_quantity' => $requiredQty,
                        'avg_unit_cost' => $avgUnitCost,
                        'last_purchase_price' => $product->dpp_inc_tax ?? 0,
                        'last_purchased_qty' => $lastPurchasedQty,
                        'last_purchased_adjusted_qty' => $lastPurchasedAdjustedQty,
                        'year_ago_sold_qty' => $yearAgoSoldQty,
                        'selling_price' => $product->sell_price_inc_tax ?? 0,
                        'has_previous_purchase' => $hasPreviousPurchase,
                        'has_opening_stock' => $hasOpeningStock,
                        'notes' => null,
                    ];
                }

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

            // For DataTable (modal selection) - simplified for performance
            return DataTables::of($query)
                ->addColumn('current_stock', function ($row) use ($businessId) {
                    $stock = DB::table('variation_location_details')
                        ->join('business_locations', 'business_locations.id', '=', 'variation_location_details.location_id')
                        ->where('variation_location_details.variation_id', $row->variation_id)
                        ->where('business_locations.business_id', $businessId)
                        ->sum('variation_location_details.qty_available');
                    return number_format((float)$stock, 2, '.', '');
                })
                ->addColumn('product_display', function ($row) {
                    $name = $row->product_name ?? '';
                    if ($row->product_type !== 'single' && $row->variation_name && $row->variation_name !== 'DUMMY') {
                        $name .= ' - ' . $row->variation_name;
                    }
                    return $name;
                })
                ->addColumn('sku', function ($row) {
                    return $row->sub_sku ?: ($row->sku ?? '');
                })
                ->filterColumn('sku', function ($query, $keyword) {
                    $query->where(function ($q) use ($keyword) {
                        $q->where('variations.sub_sku', 'like', "%{$keyword}%")
                          ->orWhere('products.sku', 'like', "%{$keyword}%");
                    });
                })
                ->rawColumns(['product_display'])
                ->make(true);

        } catch (\Exception $e) {
            \Log::error('PrGenerateController::getProducts Error: ' . $e->getMessage() . ' at ' . $e->getFile() . ':' . $e->getLine());
            return response()->json([
                'error' => true,
                'message' => 'Error loading products: ' . $e->getMessage()
            ], 500);
        }
    }

    /**
     * Calculate all data for a product.
     *
     * @param object $product
     * @param int $businessId
     * @param array $settings
     * @return array
     */
    private function calculateProductData($product, $businessId, $settings)
    {
        $currentStock = $this->calculationService->getCurrentStockAllLocations($product->variation_id, $businessId);
        $coveredQty = $this->calculationService->calculateCoveredQuantityAllLocations(
            $product->product_id,
            $product->variation_id,
            $businessId,
            $settings
        );
        $pendingPurchaseQty = $this->calculationService->getPendingPurchaseQtyAllLocations($product->variation_id, $businessId);
        $requiredQty = $this->calculationService->calculateRequiredQuantity($coveredQty, $currentStock, $pendingPurchaseQty);

        return [
            'variation_id' => $product->variation_id,
            'product_id' => $product->product_id,
            'product_name' => $product->product_name,
            'variation_name' => $product->variation_name,
            'product_type' => $product->product_type,
            'sku' => $product->sku,
            'sub_sku' => $product->sub_sku,
            'brand_name' => $product->brand_name,
            'category_name' => $product->category_name,
            'sub_category_name' => $product->sub_category_name,
            'current_stock' => $currentStock,
            'covered_qty' => $coveredQty,
            'pending_purchase_qty' => $pendingPurchaseQty,
            'required_quantity' => $requiredQty,
            'avg_unit_cost' => $this->calculationService->getAverageUnitCostAllLocations($product->variation_id, $businessId),
            'last_purchase_price' => $product->dpp_inc_tax ?? 0,
            'selling_price' => $product->sell_price_inc_tax ?? 0,
            'has_previous_purchase' => $this->calculationService->hasPreviousPurchase($product->product_id, $product->variation_id),
            'has_opening_stock' => $this->calculationService->hasOpeningStock($product->product_id, $product->variation_id),
            'notes' => null,
        ];
    }

    /**
     * Store generated requisition.
     *
     * @param Request $request
     * @return \Illuminate\Http\JsonResponse
     */
    public function store(Request $request)
    {
        if (!auth()->user()->can('purchase_requisition.create')) {
            abort(403, 'Unauthorized action.');
        }

        try {
            DB::beginTransaction();

            $businessId = request()->session()->get('user.business_id');

            // Decode products JSON
            $products = json_decode($request->products, true);

            if (empty($products)) {
                return response()->json([
                    'success' => false,
                    'msg' => __('purchaseRequisition::lang.please_add_products')
                ]);
            }

            // Generate reference number
            $refNo = $this->generateReferenceNumber($businessId);

            // Determine status based on action
            $status = ($request->action === 'submit') ? 'draft' : 'generating';

            // Create PR request
            $prRequest = PrRequest::create([
                'business_id' => $businessId,
                'ref_no' => $refNo,
                'transaction_date' => now(),
                'status' => $status,
                'notes' => $request->general_notes,
                'selling_interval' => $request->selling_interval ?? 365,
                'stock_cover_interval' => $request->stock_cover_interval ?? 180,
                'created_by' => auth()->user()->id,
            ]);

            // Add products
            foreach ($products as $productData) {
                if (isset($productData['required_quantity']) && $productData['required_quantity'] > 0) {
                    PrProduct::create([
                        'business_id' => $businessId,
                        'pr_request_id' => $prRequest->id,
                        'product_id' => $productData['product_id'],
                        'variation_id' => $productData['variation_id'],
                        'current_stock' => $productData['current_stock'] ?? 0,
                        'covered_qty' => $productData['covered_qty'] ?? 0,
                        'pending_purchase_qty' => $productData['pending_purchase_qty'] ?? 0,
                        'required_quantity' => $productData['required_quantity'],
                        'avg_unit_cost' => $productData['avg_unit_cost'] ?? 0,
                        'last_purchase_price' => $productData['last_purchase_price'] ?? 0,
                        'last_purchased_qty' => $productData['last_purchased_qty'] ?? 0,
                        'last_purchased_adjusted_qty' => $productData['last_purchased_adjusted_qty'] ?? 0,
                        'year_ago_sold_qty' => $productData['year_ago_sold_qty'] ?? 0,
                        'selling_price' => $productData['selling_price'] ?? 0,
                        'notes' => $productData['notes'] ?? null,
                        'has_previous_purchase' => $productData['has_previous_purchase'] ?? true,
                    ]);
                }
            }

            DB::commit();

            $msg = ($status === 'draft')
                ? __('purchaseRequisition::lang.pr_submitted_successfully')
                : __('purchaseRequisition::lang.pr_created_successfully');

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

        } catch (\Exception $e) {
            DB::rollBack();
            \Log::emergency('File:' . $e->getFile() . ' Line:' . $e->getLine() . ' Message:' . $e->getMessage());

            return response()->json([
                'success' => false,
                'msg' => __('messages.something_went_wrong')
            ]);
        }
    }

    /**
     * Edit an existing generating PR.
     *
     * @param int $id
     * @return \Illuminate\View\View
     */
    public function edit($id)
    {
        if (!auth()->user()->can('purchase_requisition.create') && !auth()->user()->can('purchase_requisition.edit')) {
            abort(403, 'Unauthorized action.');
        }

        $businessId = request()->session()->get('user.business_id');

        $prRequest = PrRequest::with(['products.product', 'products.variation'])
            ->where('business_id', $businessId)
            ->whereIn('status', ['generating', 'draft'])
            ->findOrFail($id);

        // Get settings
        $settings = PrSetting::getSettings($businessId);

        // Get brands, categories for filters
        $brands = Brands::forDropdown($businessId);
        $categories = Category::forDropdown($businessId, 'product');

        // Prepare existing products data for JavaScript
        $existingProducts = [];
        foreach ($prRequest->products as $prProduct) {
            $product = $prProduct->product;
            $variation = $prProduct->variation;

            $productName = $product->name;
            if ($product->type == 'variable' && $variation && !in_array(strtoupper(trim($variation->name)), ['DUMMY', ''])) {
                $productName .= ' ' . $variation->name;
            }

            $existingProducts[] = [
                'variation_id' => $prProduct->variation_id,
                'product_id' => $prProduct->product_id,
                'product_name' => $product->name,
                'variation_name' => $variation->name ?? '',
                'product_type' => $product->type,
                'sku' => $product->sku,
                'sub_sku' => $variation->sub_sku ?? '',
                'brand_name' => $product->brand->name ?? '',
                'category_name' => $product->category->name ?? '',
                'sub_category_name' => $product->subCategory->name ?? '',
                'product_custom_field4' => $product->product_custom_field4,
                'current_stock' => (float) $prProduct->current_stock,
                'covered_qty' => (float) $prProduct->covered_qty,
                'pending_purchase_qty' => (float) $prProduct->pending_purchase_qty,
                'required_quantity' => (float) $prProduct->required_quantity,
                'avg_unit_cost' => (float) $prProduct->avg_unit_cost,
                'last_purchase_price' => (float) $prProduct->last_purchase_price,
                'last_purchased_qty' => (float) $prProduct->last_purchased_qty,
                'last_purchased_adjusted_qty' => (float) $prProduct->last_purchased_adjusted_qty,
                'year_ago_sold_qty' => (float) $prProduct->year_ago_sold_qty,
                'selling_price' => (float) $prProduct->selling_price,
                'has_previous_purchase' => (bool) $prProduct->has_previous_purchase,
                'has_opening_stock' => $this->calculationService->hasOpeningStock($prProduct->product_id, $prProduct->variation_id),
                'notes' => $prProduct->notes,
            ];
        }

        return view('purchaseRequisition::generate.index', compact('settings', 'brands', 'categories', 'prRequest', 'existingProducts'));
    }

    /**
     * Update an existing generating PR.
     *
     * @param Request $request
     * @param int $id
     * @return \Illuminate\Http\JsonResponse
     */
    public function update(Request $request, $id)
    {
        if (!auth()->user()->can('purchase_requisition.create') && !auth()->user()->can('purchase_requisition.edit')) {
            abort(403, 'Unauthorized action.');
        }

        try {
            DB::beginTransaction();

            $businessId = request()->session()->get('user.business_id');

            $prRequest = PrRequest::where('business_id', $businessId)
                ->whereIn('status', ['generating', 'draft'])
                ->findOrFail($id);

            // Decode products JSON
            $products = json_decode($request->products, true);

            if (empty($products)) {
                return response()->json([
                    'success' => false,
                    'msg' => __('purchaseRequisition::lang.please_add_products')
                ]);
            }

            // Determine status based on action
            $status = ($request->action === 'submit') ? 'draft' : 'generating';

            // Update PR request
            $prRequest->update([
                'status' => $status,
                'notes' => $request->general_notes,
                'selling_interval' => $request->selling_interval ?? 365,
                'stock_cover_interval' => $request->stock_cover_interval ?? 180,
            ]);

            // Delete old products and insert new ones
            PrProduct::where('pr_request_id', $prRequest->id)->delete();

            foreach ($products as $productData) {
                if (isset($productData['required_quantity']) && $productData['required_quantity'] > 0) {
                    PrProduct::create([
                        'business_id' => $businessId,
                        'pr_request_id' => $prRequest->id,
                        'product_id' => $productData['product_id'],
                        'variation_id' => $productData['variation_id'],
                        'current_stock' => $productData['current_stock'] ?? 0,
                        'covered_qty' => $productData['covered_qty'] ?? 0,
                        'pending_purchase_qty' => $productData['pending_purchase_qty'] ?? 0,
                        'required_quantity' => $productData['required_quantity'],
                        'avg_unit_cost' => $productData['avg_unit_cost'] ?? 0,
                        'last_purchase_price' => $productData['last_purchase_price'] ?? 0,
                        'last_purchased_qty' => $productData['last_purchased_qty'] ?? 0,
                        'last_purchased_adjusted_qty' => $productData['last_purchased_adjusted_qty'] ?? 0,
                        'year_ago_sold_qty' => $productData['year_ago_sold_qty'] ?? 0,
                        'selling_price' => $productData['selling_price'] ?? 0,
                        'notes' => $productData['notes'] ?? null,
                        'has_previous_purchase' => $productData['has_previous_purchase'] ?? true,
                    ]);
                }
            }

            DB::commit();

            $msg = ($status === 'draft')
                ? __('purchaseRequisition::lang.pr_submitted_successfully')
                : __('purchaseRequisition::lang.pr_updated_successfully');

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

        } catch (\Exception $e) {
            DB::rollBack();
            \Log::emergency('File:' . $e->getFile() . ' Line:' . $e->getLine() . ' Message:' . $e->getMessage());

            return response()->json([
                'success' => false,
                'msg' => __('messages.something_went_wrong')
            ]);
        }
    }

    /**
     * Generate unique reference number.
     *
     * @param int $businessId
     * @return string
     */
    private function generateReferenceNumber($businessId)
    {
        $prefix = 'PR';
        $year = date('Y');
        $month = date('m');

        // Get last reference number
        $lastPR = PrRequest::where('business_id', $businessId)
            ->where('ref_no', 'LIKE', "{$prefix}-{$year}{$month}%")
            ->orderBy('id', 'desc')
            ->first();

        if ($lastPR) {
            // Extract number and increment
            $lastNumber = (int) substr($lastPR->ref_no, -4);
            $newNumber = str_pad($lastNumber + 1, 4, '0', STR_PAD_LEFT);
        } else {
            $newNumber = '0001';
        }

        return "{$prefix}-{$year}{$month}{$newNumber}";
    }

    /**
     * Get subcategories for multiple parent categories, grouped by category.
     *
     * @param Request $request
     * @return \Illuminate\Http\Response
     */
    public function getSubCategories(Request $request)
    {
        $categoryIds = $request->input('cat_id');

        if (empty($categoryIds)) {
            return response('');
        }

        // Ensure it's an array of integers
        if (!is_array($categoryIds)) {
            $categoryIds = [$categoryIds];
        }
        // Convert all IDs to integers for consistency
        $categoryIds = array_map('intval', $categoryIds);

        $businessId = $request->session()->get('user.business_id');

        // Get parent categories for grouping
        $parentCategories = Category::where('business_id', $businessId)
            ->whereIn('id', $categoryIds)
            ->pluck('name', 'id')
            ->toArray();

        // Get subcategories for all selected categories
        $subCategories = Category::where('business_id', $businessId)
            ->whereIn('parent_id', $categoryIds)
            ->select(['id', 'name', 'parent_id'])
            ->orderBy('parent_id')
            ->orderBy('name')
            ->get();

        // Group by parent_id manually to ensure integer keys
        $groupedSubs = [];
        foreach ($subCategories as $sub) {
            $parentId = (int) $sub->parent_id;
            if (!isset($groupedSubs[$parentId])) {
                $groupedSubs[$parentId] = [];
            }
            $groupedSubs[$parentId][] = $sub;
        }

        $html = '';

        foreach ($categoryIds as $catId) {
            $catName = $parentCategories[$catId] ?? 'Category ' . $catId;
            $subs = $groupedSubs[$catId] ?? [];

            if (count($subs) > 0) {
                $html .= '<optgroup label="' . htmlspecialchars($catName) . '">';
                foreach ($subs as $sub) {
                    $html .= '<option value="' . $sub->id . '">' . htmlspecialchars($sub->name) . '</option>';
                }
                $html .= '</optgroup>';
            }
        }

        return response($html);
    }
}
