<?php

namespace App\Http\Controllers\Api;

use App\Http\Controllers\Controller;
use App\Models\Inventory;
use App\Models\Branch;
use App\Models\Agent;
use App\Models\StockOut;
use App\Models\InventoryCategory;
use App\Models\Item;
use App\Models\Supplier;
use App\Models\SupplierBill;
use App\Models\InventorySupply;
use App\Services\InventoryTransactionService;
use Illuminate\Http\Request;
use Maatwebsite\Excel\Facades\Excel;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Str;
use Carbon\Carbon;

class InventoryImportController extends Controller
{
    public function __construct()
    {
        $this->middleware('auth:sanctum');
    }

    public function importInventory(Request $request)
    {
        $request->validate([
            'file' => 'required|mimes:xlsx,xls,csv|max:5120',
            'invoice_img' => 'nullable|image|mimes:jpeg,png,jpg,gif,pdf|max:5120',
            'supplier_id' => 'nullable|exists:suppliers,id',
        ]);

        $user = $request->user();

        // Determine restaurant_id and branch_id based on user role
        $restaurantId = null;
        $branchId = null;

        if ($user->isAdmin()) {
            if (!$request->has('restaurant_id') || !$request->restaurant_id) {
                return response()->json(['error' => 'Restaurant selection is required'], 400);
            }
            if (!$request->has('branch_id') || !$request->branch_id) {
                return response()->json(['error' => 'Branch selection is required'], 400);
            }
            $restaurantId = $request->restaurant_id;
            $branchId = $request->branch_id;
        } elseif ($user->isInventoryManager()) {
            if (!$user->restaurant_id) {
                return response()->json(['error' => 'Your account is not associated with a restaurant. Please contact administrator.'], 403);
            }
            $restaurantId = $user->restaurant_id;
            if (!$request->has('branch_id') || !$request->branch_id) {
                return response()->json(['error' => 'Branch selection is required'], 400);
            }
            $branchId = $request->branch_id;
            // Verify branch exists and belongs to their restaurant
            $branch = Branch::find($branchId);
            if (!$branch) {
                return response()->json(['error' => 'Branch not found'], 404);
            }
            if ($branch->restaurant_id != $restaurantId) {
                return response()->json(['error' => 'You do not have access to this branch'], 403);
            }
        } elseif ($user->isRestaurantOwner()) {
            if (!$user->restaurant_id) {
                return response()->json(['error' => 'Your account is not associated with a restaurant. Please contact administrator.'], 403);
            }
            $restaurantId = $user->restaurant_id;
            if (!$request->has('branch_id') || !$request->branch_id) {
                return response()->json(['error' => 'Branch selection is required'], 400);
            }
            $branchId = $request->branch_id;
            // Verify branch exists and belongs to their restaurant
            $branch = Branch::find($branchId);
            if (!$branch) {
                return response()->json(['error' => 'Branch not found'], 404);
            }
            if ($branch->restaurant_id != $restaurantId) {
                return response()->json(['error' => 'Branch does not belong to your restaurant'], 403);
            }
        } elseif ($user->isBranchManager()) {
            if (!$user->branch_id) {
                return response()->json(['error' => 'Your account is not associated with a branch. Please contact administrator.'], 403);
            }
            $branch = Branch::find($user->branch_id);
            if (!$branch) {
                return response()->json(['error' => 'Branch not found'], 404);
            }
            if (!$branch->restaurant_id) {
                return response()->json(['error' => 'Branch is not associated with a restaurant'], 400);
            }
            $restaurantId = $branch->restaurant_id;
            $branchId = $user->branch_id;
        } else {
            return response()->json(['error' => 'Unauthorized'], 403);
        }

        try {
            // Generate batch hash from file content to detect duplicates
            $file = $request->file('file');
            $fileContent = file_get_contents($file->getRealPath());
            $batchHash = hash('sha256', $fileContent);
            
            // Check if the same file was already imported today
            $today = now()->startOfDay();
            $existingBatch = Inventory::where('batch_hash', $batchHash)
                ->where('created_at', '>=', $today)
                ->first();
            
            if ($existingBatch) {
                return response()->json([
                    'error' => 'This file has already been imported today. Please use a different file or wait until tomorrow to import the same file again.'
                ], 422);
            }

            // Generate batch ID
            $batchId = 'INV-' . now()->format('Ymd') . '-' . substr($batchHash, 0, 8);

            // Verify supplier access if supplier_id is provided
            $requestSupplierId = null;
            if ($request->has('supplier_id') && $request->supplier_id) {
                $supplier = Supplier::find($request->supplier_id);
                if (!$supplier) {
                    return response()->json(['error' => 'Supplier not found'], 404);
                }
                // Verify supplier belongs to the correct restaurant
                if ($supplier->restaurant_id != $restaurantId) {
                    return response()->json(['error' => 'Supplier does not belong to the selected restaurant'], 403);
                }
                $requestSupplierId = $supplier->id;
            }

            // Handle invoice image upload for supplier bill
            $invoicePath = null;
            if ($request->hasFile('invoice_img')) {
                $invoicePath = $request->file('invoice_img')->store('supplier_bills', 'public');
            }

            $data = Excel::toArray([], $file);
            $rows = $data[0];
            
            // Skip header row
            array_shift($rows);

            DB::beginTransaction();
            $successCount = 0;
            $errorRows = [];
            $supplierBill = null;
            $currentSupplierBillId = null;
            $currentSupplierId = null;
            $totalAmount = 0;
            $firstBillDate = null; // Store first row's bill date for supplier bill

            foreach ($rows as $index => $row) {
                try {
                    if (empty($row[0])) continue; // Skip empty rows

                    // Parse values: Item Name | Category | Item Unit | Unit Price | Quantity | Total Amount | Supplier Name | Bill Date
                    $itemName = trim($row[0] ?? '');
                    $categoryName = trim($row[1] ?? '');
                    $itemUnit = trim($row[2] ?? 'pcs');
                    $unitPrice = isset($row[3]) && $row[3] !== '' ? (float) $row[3] : 0;
                    $quantity = isset($row[4]) && $row[4] !== '' ? round((float) $row[4], 2) : 0;
                    $totalAmountRow = isset($row[5]) && $row[5] !== '' ? round((float) $row[5], 2) : 0;
                    $supplierName = trim($row[6] ?? '');
                    $billDate = isset($row[7]) && $row[7] !== '' ? $row[7] : null;

                    if (empty($itemName)) {
                        $errorRows[] = 'Row ' . ($index + 2) . ': Item Name is required';
                        continue;
                    }

                    // Find or create category
                    $categoryId = null;
                    if ($categoryName) {
                        $category = InventoryCategory::where('name', $categoryName)
                            ->where(function($q) use ($restaurantId) {
                                $q->where('restaurant_id', $restaurantId)
                                  ->orWhereNull('restaurant_id'); // Include default categories
                            })
                            ->where('status', 'active')
                            ->first();
                        if ($category) {
                            $categoryId = $category->id;
                        } else {
                            $errorRows[] = 'Row ' . ($index + 2) . ': Category "' . $categoryName . '" not found';
                            continue;
                        }
                    } else {
                        $errorRows[] = 'Row ' . ($index + 2) . ': Category is required';
                        continue;
                    }

                    // Find or create item
                    $itemId = null;
                    if ($itemName) {
                        $item = Item::where('name', $itemName)
                            ->where(function($q) use ($restaurantId) {
                                $q->where('restaurant_id', $restaurantId)
                                  ->orWhereNull('restaurant_id'); // Include default items
                            })
                            ->first();
                        
                        if ($item) {
                            $itemId = $item->id;
                        } elseif ($categoryId) {
                            try {
                                $newItem = Item::create([
                                    'name' => $itemName,
                                    'category_id' => $categoryId,
                                    'status' => 'active',
                                    'restaurant_id' => $restaurantId,
                                ]);
                                $itemId = $newItem->id;
                            } catch (\Exception $e) {
                                $item = Item::where('name', $itemName)->first();
                                if ($item) {
                                    $itemId = $item->id;
                                } else {
                                    $errorRows[] = 'Row ' . ($index + 2) . ': Failed to create item - ' . $e->getMessage();
                                    continue;
                                }
                            }
                        }
                    }

                    // Calculate unit price and total amount
                    if ($totalAmountRow > 0 && $quantity > 0 && $unitPrice == 0) {
                        $unitPrice = round($totalAmountRow / $quantity, 2);
                    } elseif ($unitPrice > 0 && $quantity > 0 && $totalAmountRow == 0) {
                        $totalAmountRow = round($unitPrice * $quantity, 2);
                    } elseif ($unitPrice > 0 && $quantity > 0 && $totalAmountRow > 0) {
                        $calculatedTotal = round($unitPrice * $quantity, 2);
                        if (abs($calculatedTotal - $totalAmountRow) > 0.01) {
                            $unitPrice = round($totalAmountRow / $quantity, 2);
                        }
                    } else {
                        if ($unitPrice > 0 && $quantity > 0) {
                            $totalAmountRow = round($unitPrice * $quantity, 2);
                        }
                    }

                    // Handle supplier - Priority: Excel supplier name > Request supplier_id
                    $supplierId = null;
                    if ($supplierName) {
                        // Use supplier from Excel (highest priority)
                        $supplier = Supplier::where('name', $supplierName)
                            ->where('restaurant_id', $restaurantId)
                            ->first();
                        
                        if (!$supplier) {
                            // Auto-create supplier
                            $supplier = Supplier::create([
                                'name' => $supplierName,
                                'restaurant_id' => $restaurantId,
                                'status' => 'active',
                                'created_by' => $user->id,
                            ]);
                        }
                        $supplierId = $supplier->id;
                    } elseif ($requestSupplierId) {
                        // Use supplier from request if Excel doesn't have supplier name
                        $supplierId = $requestSupplierId;
                    }

                    // Handle supplier bill creation (if supplier exists and invoice image is provided)
                    // Create bill only once per import, use the first row's supplier and bill date
                    if ($supplierId && $invoicePath) {
                        if (!$supplierBill) {
                            // Store first bill date if available
                            if ($billDate && !$firstBillDate) {
                                $firstBillDate = $billDate;
                            }
                            // Create supplier bill with invoice image (only once)
                            $billDateValue = $firstBillDate 
                                ? Carbon::parse($firstBillDate)->toDateString() 
                                : ($billDate ? Carbon::parse($billDate)->toDateString() : now()->toDateString());
                            $supplierBill = SupplierBill::create([
                                'supplier_id' => $supplierId,
                                'bill_number' => null, // Bill number is optional
                                'bill_date' => $billDateValue,
                                'bill_file' => $invoicePath, // Save invoice image path
                                'restaurant_id' => $restaurantId,
                                'branch_id' => $branchId,
                                'created_by' => $user->id,
                            ]);
                            $currentSupplierBillId = $supplierBill->id;
                            $currentSupplierId = $supplierId;
                            $totalAmount = 0; // Reset for new bill
                        } elseif ($currentSupplierId !== $supplierId) {
                            // If supplier changes in Excel, use Excel supplier (but don't create new bill)
                            // The bill was already created with the first supplier or request supplier
                            // Just update the supplier reference for this inventory item
                        }
                        // Store first bill date if not set yet
                        if ($billDate && !$firstBillDate) {
                            $firstBillDate = $billDate;
                        }
                    }

                    // Create inventory record with transaction_type='in'
                    $inventory = Inventory::create([
                        'item_id' => $itemId,
                        'item_name' => $itemName,
                        'category_id' => $categoryId,
                        'category' => $categoryName,
                        'item_unit' => $itemUnit,
                        'unit_price' => $unitPrice,
                        'quantity' => $quantity,
                        'branch_id' => $branchId,
                        'restaurant_id' => $restaurantId,
                        'created_by' => $user->id,
                        'batch_id' => $batchId,
                        'batch_hash' => $batchHash,
                        'transaction_type' => 'in',
                        'reference_type' => $supplierBill ? 'supplier_bill' : 'addition',
                        'reference_id' => $supplierBill ? $supplierBill->id : null,
                        'supplier_id' => $supplierId,
                        'supplier_bill_id' => $supplierBill ? $supplierBill->id : null,
                        'last_transaction_at' => now(),
                    ]);

                    // Update last_transaction_at
                    $inventory->update(['last_transaction_at' => now()]);

                    // Create minimal transaction record for audit
                    InventoryTransactionService::createInTransaction($inventory, $quantity, [
                        'reference_type' => $supplierBill ? 'supplier_bill' : 'addition',
                        'reference_id' => $supplierBill ? $supplierBill->id : $inventory->id,
                        'batch_id' => $batchId,
                        'branch_id' => $branchId,
                        'restaurant_id' => $restaurantId,
                        'unit_price' => $unitPrice,
                        'created_by' => $user->id,
                    ]);

                    // Create inventory supply entry if supplier bill exists
                    if ($supplierBill) {
                        InventorySupply::create([
                            'supplier_bill_id' => $supplierBill->id,
                            'inventory_id' => $inventory->id,
                            'quantity' => $quantity,
                            'unit_price' => $unitPrice,
                            'total_amount' => $totalAmountRow,
                            'batch_id' => $batchId,
                            'restaurant_id' => $restaurantId,
                            'branch_id' => $branchId,
                        ]);
                        $totalAmount += $totalAmountRow;
                    }

                    $successCount++;
                } catch (\Exception $e) {
                    $errorRows[] = 'Row ' . ($index + 2) . ': ' . $e->getMessage();
                    Log::error("Import error on row " . ($index + 2) . ": " . $e->getMessage());
                }
            }

            // Update supplier bill total amount if bill was created
            if ($supplierBill && $totalAmount > 0) {
                $supplierBill->total_amount = $totalAmount;
                $supplierBill->save();
            }

            DB::commit();
            
            return response()->json([
                'success' => true,
                'message' => "Successfully imported {$successCount} items",
                'success_count' => $successCount,
                'error_count' => count($errorRows),
                'errors' => $errorRows,
                'supplier_bill_id' => $supplierBill ? $supplierBill->id : null,
            ], 200);
        } catch (\Exception $e) {
            DB::rollBack();
            Log::error("Import failed: " . $e->getMessage());
            return response()->json([
                'error' => 'Import failed: ' . $e->getMessage()
            ], 500);
        }
    }

    public function importStockOut(Request $request)
    {
        $request->validate([
            'file' => 'required|mimes:xlsx,xls,csv|max:5120',
        ]);

        $user = $request->user();

        // Determine restaurant_id and branch_id (source branch)
        $restaurantId = null;
        $sourceBranchId = null;

        if ($user->isAdmin()) {
            if (!$request->has('restaurant_id') || !$request->restaurant_id) {
                return response()->json(['error' => 'Restaurant selection is required'], 400);
            }
            if (!$request->has('branch_id') || !$request->branch_id) {
                return response()->json(['error' => 'Branch selection is required'], 400);
            }
            $restaurantId = $request->restaurant_id;
            $sourceBranchId = $request->branch_id;
        } elseif ($user->isInventoryManager()) {
            if (!$user->restaurant_id) {
                return response()->json(['error' => 'Your account is not associated with a restaurant. Please contact administrator.'], 403);
            }
            $restaurantId = $user->restaurant_id;
            if (!$request->has('branch_id') || !$request->branch_id) {
                return response()->json(['error' => 'Branch selection is required'], 400);
            }
            $sourceBranchId = $request->branch_id;
            // Verify branch exists and belongs to their restaurant
            $branch = Branch::find($sourceBranchId);
            if (!$branch) {
                return response()->json(['error' => 'Branch not found'], 404);
            }
            if ($branch->restaurant_id != $restaurantId) {
                return response()->json(['error' => 'You do not have access to this branch'], 403);
            }
        } elseif ($user->isRestaurantOwner()) {
            if (!$user->restaurant_id) {
                return response()->json(['error' => 'Your account is not associated with a restaurant. Please contact administrator.'], 403);
            }
            $restaurantId = $user->restaurant_id;
            if (!$request->has('branch_id') || !$request->branch_id) {
                return response()->json(['error' => 'Branch selection is required'], 400);
            }
            $sourceBranchId = $request->branch_id;
            // Verify branch exists and belongs to their restaurant
            $branch = Branch::find($sourceBranchId);
            if (!$branch) {
                return response()->json(['error' => 'Branch not found'], 404);
            }
            if ($branch->restaurant_id != $restaurantId) {
                return response()->json(['error' => 'Branch does not belong to your restaurant'], 403);
            }
        } elseif ($user->isBranchManager()) {
            if (!$user->branch_id) {
                return response()->json(['error' => 'Your account is not associated with a branch. Please contact administrator.'], 403);
            }
            $branch = Branch::find($user->branch_id);
            if (!$branch) {
                return response()->json(['error' => 'Branch not found'], 404);
            }
            if (!$branch->restaurant_id) {
                return response()->json(['error' => 'Branch is not associated with a restaurant'], 400);
            }
            $restaurantId = $branch->restaurant_id;
            $sourceBranchId = $user->branch_id;
        } else {
            return response()->json(['error' => 'Unauthorized'], 403);
        }

        try {
            // Generate batch hash from file content to detect duplicates
            $file = $request->file('file');
            $fileContent = file_get_contents($file->getRealPath());
            $batchHash = hash('sha256', $fileContent);
            
            // Check if the same file was already imported today
            $today = now()->startOfDay();
            $existingBatch = StockOut::where('batch_hash', $batchHash)
                ->where('created_at', '>=', $today)
                ->first();
            
            if ($existingBatch) {
                return response()->json([
                    'error' => 'This file has already been imported today. Please use a different file or wait until tomorrow to import the same file again.'
                ], 422);
            }

            // Generate batch ID
            $batchId = 'STO-' . now()->format('Ymd') . '-' . substr($batchHash, 0, 8);

            $data = Excel::toArray([], $file);
            $rows = $data[0];
            
            // Skip header row
            array_shift($rows);

            DB::beginTransaction();
            $successCount = 0;
            $errorRows = [];

            foreach ($rows as $index => $row) {
                try {
                    if (empty($row[0])) continue; // Skip empty rows

                    // Parse values: Item Name | Destination Branch Code/Name | Quantity | Agent Name | Agent Email | Agent Phone | Notes
                    $itemName = trim($row[0] ?? '');
                    $destinationBranchCode = trim($row[1] ?? '');
                    $quantityTransferred = isset($row[2]) && $row[2] !== '' ? round((float) $row[2], 2) : 0;
                    $agentName = trim($row[3] ?? '');
                    $agentEmail = trim($row[4] ?? '');
                    $agentPhone = trim($row[5] ?? '');
                    $notes = trim($row[6] ?? '');

                    if (empty($itemName)) {
                        $errorRows[] = 'Row ' . ($index + 2) . ': Item Name is required';
                        continue;
                    }

                    if (empty($destinationBranchCode)) {
                        $errorRows[] = 'Row ' . ($index + 2) . ': Destination Branch is required';
                        continue;
                    }

                    if ($quantityTransferred <= 0) {
                        $errorRows[] = 'Row ' . ($index + 2) . ': Quantity must be greater than 0';
                        continue;
                    }

                    // Find destination branch by code or name
                    $destinationBranch = Branch::where(function($q) use ($destinationBranchCode) {
                        $q->where('code', $destinationBranchCode)
                          ->orWhere('name', $destinationBranchCode);
                    })
                    ->where('restaurant_id', $restaurantId)
                    ->first();

                    if (!$destinationBranch) {
                        $errorRows[] = 'Row ' . ($index + 2) . ': Destination branch "' . $destinationBranchCode . '" not found';
                        continue;
                    }

                    $destinationBranchId = $destinationBranch->id;

                    // Find inventory at source branch
                    $sourceInventory = Inventory::where('item_name', $itemName)
                        ->where('branch_id', $sourceBranchId)
                        ->where('restaurant_id', $restaurantId)
                        ->first();

                    if (!$sourceInventory) {
                        $errorRows[] = 'Row ' . ($index + 2) . ': Inventory item "' . $itemName . '" not found at source branch';
                        continue;
                    }

                    // Check available stock (calculate from transactions)
                    $availableStock = Inventory::getCurrentStockForBranch($sourceInventory->item_id, $sourceBranchId);
                    if ($availableStock < $quantityTransferred) {
                        $errorRows[] = 'Row ' . ($index + 2) . ': Insufficient stock. Available: ' . $availableStock . ', Required: ' . $quantityTransferred;
                        continue;
                    }

                    // Find or create agent if provided
                    $agentId = null;
                    if ($agentName || $agentEmail) {
                        $agent = Agent::where(function($q) use ($agentEmail, $agentName) {
                            if ($agentEmail) {
                                $q->where('email', $agentEmail);
                            } else {
                                $q->where('name', $agentName);
                            }
                        })
                        ->where('branch_id', $destinationBranchId)
                        ->first();

                        if (!$agent) {
                            $agent = Agent::create([
                                'name' => $agentName ?: 'Unknown',
                                'email' => $agentEmail ?: null,
                                'phone' => $agentPhone ?: null,
                                'branch_id' => $destinationBranchId,
                                'restaurant_id' => $restaurantId,
                            ]);
                        }
                        $agentId = $agent->id;
                    }

                    // Create stock out record
                    $stockOut = StockOut::create([
                        'inventory_id' => $sourceInventory->id,
                        'branch_id' => $destinationBranchId,
                        'agent_id' => $agentId,
                        'quantity_transferred' => $quantityTransferred,
                        'unit_price' => $sourceInventory->unit_price,
                        'transferred_by' => $user->id,
                        'notes' => $notes,
                        'restaurant_id' => $restaurantId,
                        'batch_id' => $batchId,
                        'batch_hash' => $batchHash,
                        'is_return' => false,
                    ]);

                    // Create "Out" inventory record at source branch
                    $outInventory = Inventory::create([
                        'item_id' => $sourceInventory->item_id,
                        'item_name' => $sourceInventory->item_name,
                        'category_id' => $sourceInventory->category_id,
                        'category' => $sourceInventory->category,
                        'item_unit' => $sourceInventory->item_unit,
                        'unit_price' => $sourceInventory->unit_price,
                        'quantity' => $quantityTransferred,
                        'branch_id' => $sourceBranchId,
                        'restaurant_id' => $restaurantId,
                        'created_by' => $user->id,
                        'transaction_type' => 'out',
                        'reference_type' => 'transfer',
                        'reference_id' => $stockOut->id,
                        'last_transaction_at' => now(),
                    ]);

                    // Create "In" inventory record at destination branch
                    $inInventory = Inventory::create([
                        'item_id' => $sourceInventory->item_id,
                        'item_name' => $sourceInventory->item_name,
                        'category_id' => $sourceInventory->category_id,
                        'category' => $sourceInventory->category,
                        'item_unit' => $sourceInventory->item_unit,
                        'unit_price' => $sourceInventory->unit_price,
                        'quantity' => $quantityTransferred,
                        'branch_id' => $destinationBranchId,
                        'restaurant_id' => $restaurantId,
                        'created_by' => $user->id,
                        'transaction_type' => 'in',
                        'reference_type' => 'transfer',
                        'reference_id' => $stockOut->id,
                        'last_transaction_at' => now(),
                    ]);

                    // Create minimal transaction records for audit
                    InventoryTransactionService::createOutTransaction($outInventory, $quantityTransferred, [
                        'reference_type' => 'transfer',
                        'reference_id' => $stockOut->id,
                        'branch_id' => $sourceBranchId,
                        'restaurant_id' => $restaurantId,
                        'created_by' => $user->id,
                    ]);
                    
                    InventoryTransactionService::createInTransaction($inInventory, $quantityTransferred, [
                        'reference_type' => 'transfer',
                        'reference_id' => $stockOut->id,
                        'branch_id' => $destinationBranchId,
                        'restaurant_id' => $restaurantId,
                        'created_by' => $user->id,
                    ]);

                    $successCount++;
                } catch (\Exception $e) {
                    $errorRows[] = 'Row ' . ($index + 2) . ': ' . $e->getMessage();
                    Log::error("Stock out import error on row " . ($index + 2) . ": " . $e->getMessage());
                }
            }

            DB::commit();
            
            return response()->json([
                'success' => true,
                'message' => "Successfully imported {$successCount} stock out records",
                'success_count' => $successCount,
                'error_count' => count($errorRows),
                'errors' => $errorRows,
            ], 200);
        } catch (\Exception $e) {
            DB::rollBack();
            Log::error("Stock out import failed: " . $e->getMessage());
            return response()->json([
                'error' => 'Import failed: ' . $e->getMessage()
            ], 500);
        }
    }

    public function downloadInventoryTemplate()
    {
        $headers = ['Item Name', 'Category', 'Item Unit', 'Unit Price', 'Quantity', 'Total Amount', 'Supplier Name', 'Bill Date'];
        
        // Fetch all active categories
        $categories = InventoryCategory::where('status', 'active')
            ->orderBy('name')
            ->get();
        
        $sample = [];
        
        // Sample item names for each category (fallback if no items exist)
        $categorySampleItems = [
            'Rice & Grains' => ['Basmati Rice', 'White Rice', 'Brown Rice'],
            'Spices & Masalas' => ['Red Chili Powder', 'Turmeric Powder', 'Cumin Seeds'],
            'Meat & Poultry' => ['Chicken (Whole)', 'Beef', 'Mutton'],
            'Vegetables' => ['Tomatoes', 'Onions', 'Potatoes'],
            'Dairy Products' => ['Full Cream Milk', 'Yogurt', 'Cheese'],
            'Oils & Cooking Mediums' => ['Cooking Oil', 'Ghee', 'Butter'],
            'Pulses & Legumes' => ['Red Lentils', 'Chickpeas', 'Black Beans'],
            'Breads & Roti' => ['Naan', 'Roti', 'Paratha'],
            'Condiments & Sauces' => ['Tomato Ketchup', 'Chili Sauce', 'Soy Sauce'],
            'Dry Items' => ['Sugar', 'Salt', 'Flour'],
            'Beverages' => ['Soft Drinks', 'Juice', 'Tea'],
        ];
        
        // Sample units for different item types
        $defaultUnits = ['kg', 'gram', 'liter', 'pcs', 'pack'];
        
        // Sample prices (will vary by category)
        $categoryPrices = [
            'Rice & Grains' => [450.00, 500.00, 480.00],
            'Spices & Masalas' => [800.00, 1200.00, 600.00],
            'Meat & Poultry' => [580.00, 1200.00, 1500.00],
            'Vegetables' => [250.00, 180.00, 120.00],
            'Dairy Products' => [180.00, 250.00, 350.00],
            'Oils & Cooking Mediums' => [350.00, 450.00, 280.00],
            'Pulses & Legumes' => [200.00, 250.00, 300.00],
            'Breads & Roti' => [50.00, 40.00, 60.00],
            'Condiments & Sauces' => [150.00, 200.00, 180.00],
            'Dry Items' => [100.00, 80.00, 120.00],
            'Beverages' => [120.00, 150.00, 100.00],
        ];
        
        foreach ($categories as $category) {
            $categoryName = $category->name;
            
            // Try to get an existing item for this category
            $item = Item::where('category_id', $category->id)
                ->where('status', 'active')
                ->first();
            
            if ($item) {
                // Use existing item
                $itemName = $item->name;
                $itemUnit = 'kg'; // Default unit
                
                // Try to get unit from existing inventory if available
                $inventory = Inventory::where('item_id', $item->id)
                    ->whereNotNull('item_unit')
                    ->first();
                if ($inventory && $inventory->item_unit) {
                    $itemUnit = $inventory->item_unit;
                }
            } else {
                // Use sample item name from predefined list
                $sampleItems = $categorySampleItems[$categoryName] ?? ['Sample Item'];
                $itemName = $sampleItems[0];
                $itemUnit = 'kg'; // Default unit
            }
            
            // Get sample price for this category
            $prices = $categoryPrices[$categoryName] ?? [100.00];
            $unitPrice = $prices[0];
            
            // Generate sample quantity
            $quantity = rand(10, 200);
            if (in_array($itemUnit, ['pcs', 'pack'])) {
                $quantity = rand(5, 50);
            }
            
            // Calculate total amount
            $totalAmount = round($unitPrice * $quantity, 2);
            
            // Add supplier info for some items (alternating)
            $supplierName = '';
            $billDate = '';
            
            if (count($sample) % 2 === 0) {
                $supplierName = 'ABC Suppliers';
                $billDate = now()->format('Y-m-d');
            }
            
            $sample[] = [
                $itemName,
                $categoryName,
                $itemUnit,
                number_format($unitPrice, 2, '.', ''),
                number_format($quantity, 2, '.', ''),
                number_format($totalAmount, 2, '.', ''),
                $supplierName,
                $billDate,
            ];
        }

        $data = array_merge([$headers], $sample);

        return Excel::download(
            new class($data) implements \Maatwebsite\Excel\Concerns\FromArray {
                protected $data;
                public function __construct($data) { $this->data = $data; }
                public function array(): array { return $this->data; }
            },
            'inventory_import_template.xlsx'
        );
    }

    public function downloadStockOutTemplate()
    {
        $headers = ['Item Name', 'Destination Branch Code', 'Quantity', 'Agent Name', 'Agent Email', 'Agent Phone', 'Notes'];
        $sample = [
            ['Basmati Rice', 'BR001', 10.50, 'John Doe', 'john@example.com', '1234567890', 'Transfer to branch'],
            ['Red Chili Powder', 'BR002', 5.25, 'Jane Smith', 'jane@example.com', '0987654321', ''],
        ];

        $data = array_merge([$headers], $sample);

        return Excel::download(
            new class($data) implements \Maatwebsite\Excel\Concerns\FromArray {
                protected $data;
                public function __construct($data) { $this->data = $data; }
                public function array(): array { return $this->data; }
            },
            'stock_out_import_template.xlsx'
        );
    }

    public function downloadAllInventoryItems(Request $request)
    {
        $user = $request->user();
        $restaurantId = null;
        $branchId = null;

        // Determine restaurant_id and branch_id based on user role
        if ($user->isAdmin()) {
            if ($request->has('restaurant_id') && $request->restaurant_id) {
                $restaurantId = $request->restaurant_id;
            }
            if ($request->has('branch_id') && $request->branch_id) {
                $branchId = $request->branch_id;
            }
        } elseif ($user->isInventoryManager() && $user->restaurant_id) {
            $restaurantId = $user->restaurant_id;
            if ($request->has('branch_id') && $request->branch_id) {
                $branchId = $request->branch_id;
            }
        } elseif ($user->isRestaurantOwner() && $user->restaurant_id) {
            $restaurantId = $user->restaurant_id;
            if ($request->has('branch_id') && $request->branch_id) {
                $branchId = $request->branch_id;
            }
        } elseif ($user->isBranchManager() && $user->branch_id) {
            $branch = Branch::find($user->branch_id);
            if ($branch && $branch->restaurant_id) {
                $restaurantId = $branch->restaurant_id;
                $branchId = $user->branch_id;
            }
        }

        $headers = ['Item Name', 'Category', 'Item Unit', 'Unit Price', 'Quantity', 'Total Amount', 'Supplier Name', 'Bill Date'];
        
        $query = Inventory::select(
                'item_name',
                'category',
                'item_unit',
                'unit_price',
                'quantity',
                'total_price',
                'supplier_id',
                'supplier_bill_id'
            )
            ->whereNotNull('item_name')
            ->where('item_name', '!=', '')
            ->where('transaction_type', 'in'); // Only export "in" transactions

        if ($restaurantId) {
            $query->where('restaurant_id', $restaurantId);
        }
        if ($branchId) {
            $query->where('branch_id', $branchId);
        }

        $inventories = $query->orderBy('item_name')
            ->orderBy('created_at', 'desc')
            ->get()
            ->unique('item_name')
            ->values();

        $data = [$headers];
        
        foreach ($inventories as $inventory) {
            $supplierName = '';
            $billNumber = '';
            $billDate = '';
            
            if ($inventory->supplier_id) {
                $supplier = Supplier::find($inventory->supplier_id);
                if ($supplier) {
                    $supplierName = $supplier->name;
                }
            }
            
            if ($inventory->supplier_bill_id) {
                $bill = SupplierBill::find($inventory->supplier_bill_id);
                if ($bill) {
                    $billDate = $bill->bill_date ? $bill->bill_date->format('Y-m-d') : '';
                }
            }

            $data[] = [
                $inventory->item_name ?? '',
                $inventory->category ?? '',
                $inventory->item_unit ?? 'pcs',
                $inventory->unit_price ?? 0,
                $inventory->quantity ?? 0,
                $inventory->total_price ?? 0,
                $supplierName,
                $billDate,
            ];
        }

        return Excel::download(
            new class($data) implements \Maatwebsite\Excel\Concerns\FromArray {
                protected $data;
                public function __construct($data) { $this->data = $data; }
                public function array(): array { return $this->data; }
            },
            'all_inventory_items_' . date('Y-m-d_His') . '.xlsx'
        );
    }
}

