<?php

namespace App\Http\Controllers\Warehouse;

use App\Http\Controllers\Controller;
use App\Services\Warehouse\ItemService;
use App\Services\Auth\RoleBasedAccessService;
use App\Models\Warehouse\Unit;
use Illuminate\Http\Request;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;

class ItemController extends Controller
{
    protected $itemService;
    protected $accessService;

    public function __construct(ItemService $itemService, RoleBasedAccessService $accessService)
    {
        $this->itemService = $itemService;
        $this->accessService = $accessService;

        $this->middleware('warehouse.access');
        $this->middleware('warehouse.permission:warehouse.items.view')->only(['index', 'show']);
        $this->middleware('warehouse.permission:warehouse.items.create')->only(['create', 'store']);
        $this->middleware('warehouse.permission:warehouse.items.edit')->only(['edit', 'update']);
        $this->middleware('warehouse.permission:warehouse.items.delete')->only(['destroy']);
    }

    /**
     * Display a listing of the items.
     */
    public function index(Request $request)
    {
        // Authorization is handled by middleware: warehouse.permission:warehouse.items.view

        // Debug logging
        \Log::info('ItemController@index called', [
            'expectsJson' => $request->expectsJson(),
            'headers' => $request->headers->all(),
            'query_params' => $request->all()
        ]);

        // Check if this is an AJAX request for DataTables
        if ($request->expectsJson() || $request->ajax() || $request->has('draw')) {
            \Log::info('Returning JSON data for DataTables');
            return $this->getDataForTable($request);
        }

        \Log::info('Returning view for items index');
        return view('warehouse.items.index');
    }

    /**
     * Get data for DataTable.
     */
    private function getDataForTable(Request $request)
    {
        try {
            $search = $request->input('search.value');
            $goodsType = $request->get('goods_type');
            $status = $request->get('status');
            $categoryId = $request->get('category_id');

            $rows = collect();

            if (!$goodsType || $goodsType === 'direct') {
                $directQuery = \App\Models\Warehouse\Item::with('category');

                if (!empty($search)) {
                    $directQuery->where(function ($query) use ($search) {
                        $query->where('item_code', 'LIKE', "%{$search}%")
                            ->orWhere('item_description', 'LIKE', "%{$search}%")
                            ->orWhere('notes', 'LIKE', "%{$search}%")
                            ->orWhereHas('category', function ($categoryQuery) use ($search) {
                                $categoryQuery->where('category_name', 'LIKE', "%{$search}%");
                            });
                    });
                }
                if (!empty($status)) {
                    $directQuery->where('status', $status);
                }
                if (!empty($categoryId)) {
                    $directQuery->where('category_id', $categoryId);
                }

                $rows = $rows->concat($directQuery->get()->map(function ($item) {
                    return [
                        'id' => $item->id,
                        'item_code' => $item->item_code,
                        'item_description' => $item->item_description,
                        'goods_type' => 'Direct',
                        'goods_type_key' => 'direct',
                        'category_name' => $item->category ? $item->category->category_name : 'No Category',
                        'unit_of_measure' => $item->unit_of_measure ?? 'N/A',
                        'status' => $item->status,
                        'created_at' => $item->created_at ? $item->created_at->format('M d, Y H:i') : '',
                        'created_at_sort' => $item->created_at ? $item->created_at->timestamp : 0,
                        'lifo_sort' => (($item->created_at ? $item->created_at->timestamp : 0) * 1000000) + $item->id,
                        'created_by' => 'Admin',
                        'view_url' => route('warehouse.items.show', $item->id),
                        'edit_url' => route('warehouse.items.edit', $item->id),
                        'delete_url' => route('warehouse.items.destroy', $item->id),
                    ];
                }));
            }

            if (!$goodsType || $goodsType === 'indirect') {
                $indirectQuery = \App\Models\Warehouse\IndirectGoods\IndirectGood::with(['category', 'unit']);

                if (!empty($search)) {
                    $indirectQuery->where(function ($query) use ($search) {
                        $query->where('ig_code', 'LIKE', "%{$search}%")
                            ->orWhere('ig_description', 'LIKE', "%{$search}%")
                            ->orWhere('notes', 'LIKE', "%{$search}%")
                            ->orWhereHas('category', function ($categoryQuery) use ($search) {
                                $categoryQuery->where('category_name', 'LIKE', "%{$search}%");
                            });
                    });
                }
                if (!empty($status)) {
                    $indirectQuery->where('status', $status);
                }
                if (!empty($categoryId)) {
                    $indirectQuery->where('category_id', $categoryId);
                }

                $rows = $rows->concat($indirectQuery->get()->map(function ($item) {
                    return [
                        'id' => $item->id,
                        'item_code' => $item->ig_code,
                        'item_description' => $item->ig_description,
                        'goods_type' => 'Indirect',
                        'goods_type_key' => 'indirect',
                        'category_name' => $item->category ? $item->category->category_name : 'No Category',
                        'unit_of_measure' => $item->unit ? $item->unit->display_name : ($item->unit_of_measure ?? 'N/A'),
                        'status' => $item->status,
                        'created_at' => $item->created_at ? $item->created_at->format('M d, Y H:i') : '',
                        'created_at_sort' => $item->created_at ? $item->created_at->timestamp : 0,
                        'lifo_sort' => (($item->created_at ? $item->created_at->timestamp : 0) * 1000000) + $item->id,
                        'created_by' => 'Admin',
                        'view_url' => route('warehouse.indirect-goods.catalog.show', $item->id),
                        'edit_url' => route('warehouse.indirect-goods.catalog.edit', $item->id),
                        'delete_url' => route('warehouse.indirect-goods.catalog.destroy', $item->id),
                    ];
                }));
            }

            $totalRecords = $rows->count();

            if ($request->has('order')) {
                $orderColumnIndex = $request->input('order.0.column');
                $orderColumn = $request->input('columns.' . $orderColumnIndex . '.data')
                    ?: $request->input('columns.' . $orderColumnIndex . '.name')
                    ?: 'created_at';
                $orderDirection = $request->input('order.0.dir', 'desc');
                $sortKey = $orderColumn === 'created_at' ? 'lifo_sort' : $orderColumn;

                $rows = $orderDirection === 'asc'
                    ? $rows->sortBy($sortKey)
                    : $rows->sortByDesc($sortKey);
            } else {
                $rows = $rows->sortByDesc('lifo_sort');
            }

            $start = $request->input('start', 0);
            $length = $request->input('length', 10);
            $pagedRows = $length == -1 ? $rows->values() : $rows->slice($start, $length)->values();

            return response()->json([
                'draw' => $request->input('draw', 1),
                'recordsTotal' => $totalRecords,
                'recordsFiltered' => $totalRecords,
                'data' => $pagedRows->toArray()
            ]);

        } catch (\Exception $e) {
            return response()->json([
                'error' => $e->getMessage(),
                'draw' => $request->input('draw', 1),
                'recordsTotal' => 0,
                'recordsFiltered' => 0,
                'data' => []
            ], 400);
        }
    }

    /**
     * Show the form for creating a new item.
     */
    public function create()
    {
        // Authorization is handled by middleware: warehouse.permission:warehouse.items.create

        $categories = \App\Models\Warehouse\ItemCategory::where('status', 'active')->get();

        return view('warehouse.items.create', compact('categories'));
    }

    /**
     * Get units for dropdown
     */
    public function getUnitsDropdown(Request $request)
    {
        $units = Unit::active()
            ->orderBy('unit_name')
            ->get()
            ->map(function ($unit) {
                return [
                    'value' => $unit->id,
                    'text' => $unit->unit_name . ' (' . $unit->unit_symbol . ')',
                    'unit_code' => $unit->unit_code,
                    'unit_symbol' => $unit->unit_symbol,
                    'unit_type' => $unit->unit_type
                ];
            });

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

    /**
     * Get items for dropdown
     */
    public function getItemsDropdown(Request $request)
    {
        $items = \App\Models\Warehouse\Item::where('status', 'active')
            ->orderBy('item_description')
            ->get()
            ->map(function ($item) {
                return [
                    'id' => $item->id,
                    'value' => $item->id,
                    'text' => $item->item_description . ' (' . $item->item_code . ')',
                    'item_code' => $item->item_code,
                    'item_description' => $item->item_description
                ];
            });

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

    /**
     * Store a newly created item in storage.
     */
    public function store(Request $request)
    {
        // Authorization is handled by middleware: warehouse.permission:warehouse.items.create
        if ($request->input('goods_type') === 'indirect') {
            if (!auth()->user()->can('warehouse.indirect-goods.create')) {
                abort(403, 'Unauthorized action.');
            }

            $validated = $request->validate([
                'ig_description' => 'nullable|string|max:255',
                'item_description' => 'nullable|string|max:255',
                'category_id' => 'required|exists:item_categories,id',
                'unit_id' => 'required|exists:units,id',
                'min_stock_level' => 'nullable|numeric|min:0',
                'max_stock_level' => 'nullable|numeric|min:0',
                'reorder_point' => 'nullable|numeric|min:0',
                'status' => 'nullable|in:active,inactive',
                'notes' => 'nullable|string|max:1000',
            ]);

            $description = $validated['ig_description'] ?? $validated['item_description'] ?? null;
            if (empty($description)) {
                return response()->json([
                    'success' => false,
                    'errors' => [
                        'item_description' => ['Indirect goods description is required.'],
                    ],
                    'message' => 'Indirect goods description is required.',
                ], 422);
            }

            $good = \App\Models\Warehouse\IndirectGoods\IndirectGood::create([
                'ig_description' => $description,
                'category_id' => $validated['category_id'],
                'unit_id' => $validated['unit_id'],
                'min_stock_level' => $validated['min_stock_level'] ?? null,
                'max_stock_level' => $validated['max_stock_level'] ?? null,
                'reorder_point' => $validated['reorder_point'] ?? null,
                'status' => $validated['status'] ?? 'active',
                'notes' => $validated['notes'] ?? null,
            ]);

            return response()->json([
                'success' => true,
                'data' => $good->load('category', 'unit'),
                'message' => 'Indirect good created successfully.',
            ], 201);
        }

        $validatedData = $request->validate([
            'item_code' => 'nullable|string|max:50|unique:items,item_code',
            'item_description' => 'required|string|max:255',
            'category_id' => 'required|exists:item_categories,id',
            'unit_id' => 'nullable|exists:units,id',
            'unit_of_measure' => 'nullable|string|max:50',
            'min_stock_level' => 'nullable|numeric|min:0',
            'max_stock_level' => 'nullable|numeric|min:0',
            'reorder_point' => 'nullable|numeric|min:0',
            'status' => 'required|in:active,inactive,discontinued',
            'notes' => 'nullable|string|max:1000',
            'opening_stock' => 'nullable|numeric|min:0',
            'opening_stock_division_id' => 'nullable|required_with:opening_stock|exists:project_divisions,id',
            'opening_stock_unit_price' => 'nullable|required_with:opening_stock|numeric|min:0'
        ]);

        $openingStock = $validatedData['opening_stock'] ?? 0;
        $openingStockDivisionId = $validatedData['opening_stock_division_id'] ?? null;
        $openingStockUnitPrice = $validatedData['opening_stock_unit_price'] ?? 0;
        unset($validatedData['opening_stock'], $validatedData['opening_stock_division_id'], $validatedData['opening_stock_unit_price']);

        // If unit_id is provided, get the unit_of_measure from the unit
        if (!empty($validatedData['unit_id'])) {
            $unit = Unit::find($validatedData['unit_id']);
            if ($unit) {
                $validatedData['unit_of_measure'] = $unit->unit_symbol ?: $unit->unit_name;
            }
        }

        // Default unit_of_measure if neither is provided
        if (empty($validatedData['unit_id']) && empty($validatedData['unit_of_measure'])) {
            $validatedData['unit_of_measure'] = 'PCS'; // Default to pieces
        }

        // Generate item code if not provided
        if (empty($validatedData['item_code'])) {
            $validatedData['item_code'] = $this->generateItemCode($validatedData['category_id']);
        }

        try {
            $item = \App\Models\Warehouse\Item::create($validatedData);

            // Create opening stock inventory and stock movement if opening stock > 0
            if ($openingStock > 0) {
                \App\Models\Warehouse\Inventory::create([
                    'item_id' => $item->id,
                    'division_id' => $openingStockDivisionId,
                    'quantity_available' => $openingStock,
                    'unit_price' => $openingStockUnitPrice,
                    'location' => 'Main Warehouse',
                    'status' => 'in_stock',
                ]);

                \App\Models\Warehouse\StockMovement::create([
                    'item_id' => $item->id,
                    'movement_type' => 'in',
                    'reference_type' => 'opening_stock',
                    'reference_id' => $item->id,
                    'quantity_before' => 0,
                    'quantity_moved' => $openingStock,
                    'quantity_after' => $openingStock,
                    'division_id' => $openingStockDivisionId,
                    'user_id' => auth()->id(),
                    'notes' => 'Opening stock',
                ]);

                // Record in division deliveries report if division is selected
                if ($openingStockDivisionId) {
                    \App\Models\Warehouse\DivisionDelivery::recordDelivery(
                        $openingStockDivisionId,
                        $item->id,
                        $openingStock,
                        $openingStockUnitPrice,
                        now()->format('Y-m-d')
                    );
                }
            }

            if ($request->expectsJson()) {
                return response()->json([
                    'success' => true,
                    'data' => $item->load('category'),
                    'message' => 'Item created successfully'
                ], 201);
            }

            return redirect()->route('warehouse.items.index')
                           ->with('success', 'Item created successfully');

        } catch (\Exception $e) {
            \Log::error('Item store error: ' . $e->getMessage(), [
                'file' => $e->getFile(),
                'line' => $e->getLine(),
            ]);
            if ($request->expectsJson()) {
                return response()->json([
                    'success' => false,
                    'message' => $e->getMessage()
                ], 400);
            }

            return back()->withErrors(['error' => $e->getMessage()])->withInput();
        }
    }

    /**
     * Generate item code based on category
     */
    private function generateItemCode($categoryId)
    {
        $category = \App\Models\Warehouse\ItemCategory::find($categoryId);
        $prefix = $category ? strtoupper(substr($category->category_name, 0, 3)) : 'ITM';
        $timestamp = now()->format('ymd');
        $sequence = str_pad(\App\Models\Warehouse\Item::where('item_code', 'LIKE', $prefix . '%')->count() + 1, 3, '0', STR_PAD_LEFT);

        return $prefix . $timestamp . $sequence;
    }

    /**
     * Display the specified item.
     */
    public function show($id)
    {
        $item = $this->itemService->findById($id);

        if (!$item) {
            abort(404, 'Item not found');
        }

        // Authorization is handled by middleware: warehouse.permission:warehouse.items.view

        $item->load(['category', 'inventories', 'stockMovements.user']);

        return view('warehouse.items.show', compact('item'));
    }

    /**
     * Show the form for editing the specified item.
     */
    public function edit($id)
    {
        $item = $this->itemService->findById($id);

        if (!$item) {
            if (request()->expectsJson()) {
                return response()->json(['error' => 'Item not found'], 404);
            }
            abort(404, 'Item not found');
        }

        // Authorization is handled by middleware: warehouse.permission:warehouse.items.edit

        $categories = \App\Models\Warehouse\ItemCategory::where('status', 'active')->get();
        $units = Unit::active()
            ->orderBy('unit_name')
            ->get()
            ->map(function ($unit) {
                return [
                    'id' => $unit->id,
                    'unit_name' => $unit->unit_name,
                    'unit_symbol' => $unit->unit_symbol,
                    'unit_code' => $unit->unit_code,
                    'unit_type' => $unit->unit_type
                ];
            });

        // Return JSON for AJAX requests
        if (request()->expectsJson()) {
            $item->load('category');
            return response()->json([
                'success' => true,
                'data' => $item,
                'categories' => $categories,
                'units' => $units
            ]);
        }

        return view('warehouse.items.edit', compact('item', 'categories', 'units'));
    }

    /**
     * Update the specified item in storage.
     */
    public function update(Request $request, $id)
    {
        $item = $this->itemService->findById($id);

        if (!$item) {
            abort(404, 'Item not found');
        }

        // Authorization is handled by middleware: warehouse.permission:warehouse.items.edit

        $validatedData = $request->validate([
            'item_code' => 'nullable|string|max:50|unique:items,item_code,' . $id,
            'item_description' => 'required|string|max:255',
            'category_id' => 'required|exists:item_categories,id',
            'unit_id' => 'nullable|exists:units,id',
            'unit_of_measure' => 'nullable|string|max:50',
            'min_stock_level' => 'nullable|numeric|min:0',
            'max_stock_level' => 'nullable|numeric|min:0',
            'reorder_point' => 'nullable|numeric|min:0',
            'status' => 'required|in:active,inactive,discontinued',
            'notes' => 'nullable|string|max:1000'
        ]);

        // If unit_id is provided, get the unit_of_measure from the unit
        if (!empty($validatedData['unit_id'])) {
            $unit = Unit::find($validatedData['unit_id']);
            if ($unit) {
                $validatedData['unit_of_measure'] = $unit->unit_symbol ?: $unit->unit_name;
            }
        }

        try {
            // Direct update instead of using ItemService to avoid complex validation
            $item->update($validatedData);

            if ($request->expectsJson()) {
                return response()->json([
                    'success' => true,
                    'data' => $item->fresh()->load('category'),
                    'message' => 'Item updated successfully'
                ]);
            }

            return redirect()->route('warehouse.items.show', $item)
                           ->with('success', 'Item updated successfully');

        } catch (\Exception $e) {
            if ($request->expectsJson()) {
                return response()->json([
                    'success' => false,
                    'message' => $e->getMessage()
                ], 400);
            }

            return back()->withErrors(['error' => $e->getMessage()])->withInput();
        }
    }

    /**
     * Remove the specified item from storage.
     */
    public function destroy($id)
    {
        $item = $this->itemService->findById($id);

        if (!$item) {
            abort(404, 'Item not found');
        }

        // Authorization is handled by middleware: warehouse.permission:warehouse.items.delete

        try {
            $this->itemService->delete($id);

            return response()->json([
                'success' => true,
                'message' => 'Item deleted successfully'
            ]);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => $e->getMessage()
            ], 400);
        }
    }

    /**
     * Generate barcode for item.
     */
    public function generateBarcode($id)
    {
        $item = $this->itemService->findById($id);

        if (!$item) {
            abort(404, 'Item not found');
        }

        // Authorization is handled by middleware: warehouse.permission:warehouse.items.create

        try {
            $barcodeUrl = $this->itemService->generateBarcode($item);

            return response()->json([
                'success' => true,
                'barcode_url' => $barcodeUrl,
                'message' => 'Barcode generated successfully'
            ]);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => $e->getMessage()
            ], 400);
        }
    }

    /**
     * Generate QR code for item.
     */
    public function generateQrCode($id)
    {
        $item = $this->itemService->findById($id);

        if (!$item) {
            abort(404, 'Item not found');
        }

        // Authorization is handled by middleware: warehouse.permission:warehouse.items.create

        try {
            $qrCodeUrl = $this->itemService->generateQrCode($item);

            return response()->json([
                'success' => true,
                'qr_code_url' => $qrCodeUrl,
                'message' => 'QR code generated successfully'
            ]);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => $e->getMessage()
            ], 400);
        }
    }

    /**
     * Bulk import items.
     */
    public function bulkImport(Request $request)
    {
        // Authorization is handled by middleware: warehouse.permission:warehouse.items.create

        $request->validate([
            'items' => 'required|array|min:1',
            'items.*.name' => 'required|string|max:255',
            'items.*.category_id' => 'nullable|exists:item_categories,id',
            'items.*.unit' => 'required|string|max:50',
        ]);

        try {
            $results = $this->itemService->bulkImport($request->input('items'));

            return response()->json([
                'success' => true,
                'data' => $results,
                'message' => "Successfully imported {$results['success']} items"
            ]);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => $e->getMessage()
            ], 400);
        }
    }

    /**
     * Export items.
     */
    public function export(Request $request)
    {
        // Authorization is handled by middleware: warehouse.permission:warehouse.items.view

        $filters = $request->only([
            'category_id', 'status', 'location', 'low_stock', 'out_of_stock',
            'created_from', 'created_to'
        ]);

        try {
            $itemsData = $this->itemService->exportItems($filters);

            return response()->json([
                'success' => true,
                'data' => $itemsData,
                'message' => 'Items exported successfully'
            ]);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => $e->getMessage()
            ], 400);
        }
    }

    /**
     * Download the item import template.
     */
    public function downloadTemplate()
    {
        // Authorization is handled by middleware: warehouse.permission:warehouse.items.view

        try {
            $filePath = $this->generateImportTemplate();
            $filename = 'items_import_template_updated.xlsx';

            return response()->download($filePath, $filename)->deleteFileAfterSend(true);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => $e->getMessage()
            ], 400);
        }
    }

    /**
     * Import items from Excel file.
     */
    public function import(Request $request)
    {
        // Authorization is handled by middleware: warehouse.permission:warehouse.items.create

        $request->validate([
            'file' => 'required|file|mimes:xlsx,xls,csv|max:10240' // 10MB max
        ]);

        try {
            $results = $this->importFromExcel($request->file('file'));

            return response()->json([
                'success' => true,
                'imported' => $results['success'],
                'updated' => 0, // Currently we only create new items
                'skipped' => count($results['errors']),
                'data' => $results,
                'message' => "Import completed. {$results['success']} items imported successfully."
            ]);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => $e->getMessage()
            ], 422);
        }
    }

    /**
     * Generate the import template Excel file (without category column).
     */
    private function generateImportTemplate()
    {
        $spreadsheet = new Spreadsheet();

        // Data Sheet
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Data');

        // Headers with new format including category information and units
        $headers = [
            'A1' => 'item_code',
            'B1' => 'item_description',
            'C1' => 'type_of_goods',
            'D1' => 'category_code',
            'E1' => 'category_description',
            'F1' => 'unit_of_measurement',
            'G1' => 'min_stock_level',
            'H1' => 'max_stock_level',
            'I1' => 'reorder_point',
            'J1' => 'status',
            'K1' => 'notes'
        ];

        foreach ($headers as $cell => $header) {
            $sheet->setCellValue($cell, $header);
            $style = $sheet->getStyle($cell);
            $style->getFont()->setBold(true)->setSize(11);
            $style->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                  ->getStartColor()->setRGB('E8F4FD'); // Light blue background
            $style->getBorders()->getAllBorders()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
        }

        // Sample data with diverse examples
        $sampleData = [
            2 => [
                'A' => 'ELE001',
                'B' => 'LED Light Bulb 10W',
                'C' => 'Finished Goods',
                'D' => 'ELE',
                'E' => 'Electronics',
                'F' => 'pieces',
                'G' => '10',
                'H' => '100',
                'I' => '20',
                'J' => 'active',
                'K' => 'Energy efficient LED bulb'
            ],
            3 => [
                'A' => 'PLU001',
                'B' => 'PVC Pipe 6 inch',
                'C' => 'Raw Material',
                'D' => 'PLU',
                'E' => 'Plumbing',
                'F' => 'meters',
                'G' => '5',
                'H' => '50',
                'I' => '10',
                'J' => 'active',
                'K' => 'High-pressure PVC pipe'
            ],
            4 => [
                'A' => '', // Empty code will be auto-generated
                'B' => 'Office Paper A4',
                'C' => 'Consumables',
                'D' => 'STA',
                'E' => 'Stationery',
                'F' => 'reams',
                'G' => '2',
                'H' => '20',
                'I' => '5',
                'J' => 'active',
                'K' => 'Standard office printing paper'
            ],
            5 => [
                'A' => 'FIT001',
                'B' => 'Elbow Joint 90 degrees',
                'C' => 'Hardware',
                'D' => 'FIT',
                'E' => 'Fittings',
                'F' => 'pcs',
                'G' => '20',
                'H' => '200',
                'I' => '50',
                'J' => 'active',
                'K' => 'Stainless steel elbow fitting'
            ]
        ];

        foreach ($sampleData as $row => $data) {
            foreach ($data as $col => $value) {
                $sheet->setCellValue($col . $row, $value);
                // Add light gray background to sample data
                $style = $sheet->getStyle($col . $row);
                $style->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                      ->getStartColor()->setRGB('F8F9FA'); // Light gray
                $style->getBorders()->getAllBorders()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
            }
        }

        // Freeze header row
        $sheet->freezePane('A2');

        // Add comprehensive comments/notes
        $sheet->getComment('A1')->getText()->createTextRun('✅ ITEM CODE: Leave empty for auto-generation (format: CATEGORY_PREFIX + YYMMDD + SEQUENCE). Must be unique if provided.');
        $sheet->getComment('B1')->getText()->createTextRun('🔥 REQUIRED: Item description/name (max 255 characters). Be descriptive and specific.');
        $sheet->getComment('C1')->getText()->createTextRun('🔥 REQUIRED: Type of goods. Use: Raw Material, Finished Goods, Consumables, or Hardware');
        $sheet->getComment('D1')->getText()->createTextRun('🔥 REQUIRED: Category code (3-4 characters). Must match existing category code in system.');
        $sheet->getComment('E1')->getText()->createTextRun('🔥 REQUIRED: Category description/name. Must match existing category name exactly.');
        $sheet->getComment('F1')->getText()->createTextRun('🔥 REQUIRED: Unit of measurement. Examples: pieces, meters, kg, liters. Auto-created if not exists.');
        $sheet->getComment('G1')->getText()->createTextRun('✅ OPTIONAL: Minimum stock level (numeric, can be decimal). Leave empty if not needed.');
        $sheet->getComment('H1')->getText()->createTextRun('✅ OPTIONAL: Maximum stock level (numeric, can be decimal). Leave empty if not needed.');
        $sheet->getComment('I1')->getText()->createTextRun('✅ OPTIONAL: Reorder point threshold (numeric, can be decimal). When to reorder stock.');
        $sheet->getComment('J1')->getText()->createTextRun('🔥 REQUIRED: Status. Must be exactly: active, inactive, or discontinued (lowercase).');
        $sheet->getComment('K1')->getText()->createTextRun('✅ OPTIONAL: Additional notes, specifications, or details about the item.');

        // Auto-size columns
        foreach (range('A', 'K') as $col) {
            $sheet->getColumnDimension($col)->setAutoSize(true);
        }

        // Instructions Sheet
        $instructionsSheet = $spreadsheet->createSheet();
        $instructionsSheet->setTitle('Instructions');

        $instructions = [
            'WAREHOUSE ITEMS IMPORT TEMPLATE - UPDATED FORMAT',
            '',
            '🔥 IMPORTANT: This template matches your Excel format with updated column structure!',
            '',
            '1. TEMPLATE STRUCTURE:',
            '   ✅ item_code - Auto-generated if left empty',
            '   ✅ item_description - Item name (REQUIRED)',
            '   ✅ type_of_goods - Raw Material/Finished Goods/Consumables/Hardware (REQUIRED)',
            '   ✅ category_code - 3-4 character code for category matching (REQUIRED)',
            '   ✅ category_description - Full category name for matching (REQUIRED)',
            '   ✅ unit_of_measurement - Unit name, auto-created if not exists (REQUIRED)',
            '   ✅ min_stock_level - Minimum inventory level (Optional)',
            '   ✅ max_stock_level - Maximum inventory level (Optional)',
            '   ✅ reorder_point - Reorder threshold (Optional)',
            '   ✅ status - active/inactive/discontinued (REQUIRED)',
            '   ✅ notes - Additional item information (Optional)',
            '',
            '2. CATEGORY MATCHING LOGIC:',
            '   - System matches categories using ALL 3 fields: type_of_goods + category_code + category_description',
            '   - Example: "Raw Material" + "PLU" + "Plumbing" = matches Plumbing category',
            '   - If no match found, the row will be SKIPPED during import',
            '   - Check existing categories in the system before importing',
            '',
            '3. UNIT HANDLING:',
            '   - System checks if unit exists by exact name match',
            '   - If unit doesn\'t exist, it will be AUTO-CREATED with:',
            '     * unit_name = your unit_of_measurement value',
            '     * unit_code = same as unit_name',
            '     * unit_symbol = same as unit_name',
            '     * unit_type = "General"',
            '   - Examples: "pieces", "meters", "kilograms", "liters", "boxes"',
            '',
            '4. ITEM CODE AUTO-GENERATION:',
            '   - Leave item_code column empty for auto-generation',
            '   - Format: [CATEGORY_PREFIX][YYMMDD][SEQUENCE]',
            '   - Example: PLU241209001 (Plumbing category, Dec 9, 2024, sequence 001)',
            '   - If provided, must be unique across all items',
            '',
            '5. VALIDATION RULES:',
            '   ❌ Blank required fields will cause row to be skipped',
            '   ❌ Invalid status values (must be: active, inactive, discontinued)',
            '   ❌ Non-numeric values in stock level fields',
            '   ❌ Item descriptions longer than 255 characters',
            '   ❌ Duplicate item codes (if manually provided)',
            '',
            '6. SAMPLE DATA EXPLANATION:',
            '   Row 2: Electronics item with auto item code',
            '   Row 3: Plumbing raw material with meters unit',
            '   Row 4: Stationery consumable with empty item_code (will auto-generate)',
            '   Row 5: Hardware fitting with pieces unit',
            '',
            '7. BEFORE IMPORTING:',
            '   ✅ Delete all sample data rows (keep only headers)',
            '   ✅ Fill in your actual item data',
            '   ✅ Verify category codes exist in your system',
            '   ✅ Use consistent unit names (lowercase recommended)',
            '   ✅ Save file as .xlsx format',
            '',
            '8. IMPORT PROCESS:',
            '   1. Open the Import Modal from Items page',
            '   2. Choose your filled template file',
            '   3. System will process and show results',
            '   4. Review any skipped rows and fix issues',
            '   5. Re-import if necessary',
            '',
            '9. TROUBLESHOOTING:',
            '   - Row skipped? Check category matching fields',
            '   - Unit issues? Ensure unit name is spelled correctly',
            '   - Import fails? Check file format and required fields',
            '   - Need help? Contact system administrator'
        ];

        foreach ($instructions as $index => $instruction) {
            $instructionsSheet->setCellValue('A' . ($index + 1), $instruction);
            if ($index === 0) {
                $instructionsSheet->getStyle('A1')->getFont()->setBold(true)->setSize(14);
            }
        }

        $instructionsSheet->getColumnDimension('A')->setWidth(80);

        // Set active sheet back to data
        $spreadsheet->setActiveSheetIndex(0);

        // Save file
        $tempDir = storage_path('app/temp');
        if (!file_exists($tempDir)) {
            mkdir($tempDir, 0755, true);
        }

        $fileName = 'items_import_template_updated.xlsx';
        $filePath = $tempDir . '/' . $fileName;

        $writer = new Xlsx($spreadsheet);
        $writer->save($filePath);

        return $filePath;
    }

    /**
     * Import items from Excel file with category from dropdown.
     */
    private function importFromExcel($file, $categoryId = null)
    {
        \Log::info('🔄 IMPORT STARTED', [
            'file_name' => $file->getClientOriginalName(),
            'file_size' => $file->getSize(),
            'category_id' => $categoryId
        ]);

        $spreadsheet = IOFactory::load($file->getPathname());
        $sheet = $spreadsheet->getActiveSheet();
        $rows = $sheet->toArray();

        \Log::info('📊 FILE LOADED', [
            'total_rows_including_header' => count($rows),
            'first_row_sample' => $rows[0] ?? 'No rows found'
        ]);

        // Remove header row
        array_shift($rows);

        \Log::info('📋 PROCESSING ROWS', [
            'data_rows_count' => count($rows),
            'sample_first_data_row' => $rows[0] ?? 'No data rows'
        ]);

        $imported = 0;
        $errors = [];

        foreach ($rows as $index => $row) {
            $rowNumber = $index + 2; // +2 because we removed header and arrays are 0-indexed

            \Log::info("🔍 PROCESSING ROW {$rowNumber}", [
                'raw_row_data' => $row,
                'row_has_data' => !empty(array_filter($row))
            ]);

            // Skip empty rows
            if (empty(array_filter($row))) {
                \Log::info("⏭️ SKIPPING EMPTY ROW {$rowNumber}");
                continue;
            }

            try {
                // Map columns with new format
                $itemData = [
                    'item_code' => trim($row[0] ?? ''),
                    'item_description' => trim($row[1] ?? ''),
                    'type_of_goods' => trim($row[2] ?? ''),
                    'category_code' => trim($row[3] ?? ''),
                    'category_description' => trim($row[4] ?? ''),
                    'unit_of_measurement' => trim($row[5] ?? ''),
                    'min_stock_level' => is_numeric($row[6]) ? (float)$row[6] : 0,
                    'max_stock_level' => is_numeric($row[7]) ? (float)$row[7] : 0,
                    'reorder_point' => is_numeric($row[8]) ? (float)$row[8] : 0,
                    'status' => trim($row[9] ?? 'active'),
                    'notes' => trim($row[10] ?? '')
                ];

                \Log::info("📝 MAPPED DATA FOR ROW {$rowNumber}", [
                    'mapped_data' => $itemData
                ]);

                // Validate required fields
                \Log::info("🔎 VALIDATING REQUIRED FIELDS FOR ROW {$rowNumber}");

                if (empty($itemData['item_description'])) {
                    \Log::warning("❌ VALIDATION FAILED: item_description is empty for row {$rowNumber}");
                    $errors[] = "Row {$rowNumber}: item_description is required";
                    continue;
                }

                if (empty($itemData['type_of_goods'])) {
                    \Log::warning("❌ VALIDATION FAILED: type_of_goods is empty for row {$rowNumber}");
                    $errors[] = "Row {$rowNumber}: type_of_goods is required";
                    continue;
                }

                if (empty($itemData['category_code'])) {
                    \Log::warning("❌ VALIDATION FAILED: category_code is empty for row {$rowNumber}");
                    $errors[] = "Row {$rowNumber}: category_code is required";
                    continue;
                }

                if (empty($itemData['category_description'])) {
                    \Log::warning("❌ VALIDATION FAILED: category_description is empty for row {$rowNumber}");
                    $errors[] = "Row {$rowNumber}: category_description is required";
                    continue;
                }

                if (empty($itemData['unit_of_measurement'])) {
                    \Log::warning("❌ VALIDATION FAILED: unit_of_measurement is empty for row {$rowNumber}");
                    $errors[] = "Row {$rowNumber}: unit_of_measurement is required";
                    continue;
                }

                \Log::info("✅ ALL REQUIRED FIELDS VALID FOR ROW {$rowNumber}");

                // Find category by type_of_goods, category_code, and category_description
                \Log::info("🔍 SEARCHING FOR CATEGORY FOR ROW {$rowNumber}", [
                    'search_criteria' => [
                        'type_of_goods' => $itemData['type_of_goods'],
                        'category_code' => $itemData['category_code'],
                        'category_name' => $itemData['category_description'],
                        'status' => 'active'
                    ]
                ]);

                $category = \App\Models\Warehouse\ItemCategory::where('type_of_goods', $itemData['type_of_goods'])
                    ->where('category_code', $itemData['category_code'])
                    ->where('category_name', $itemData['category_description'])
                    ->where('status', 'active')
                    ->first();

                if (!$category) {
                    \Log::warning("❌ CATEGORY NOT FOUND FOR ROW {$rowNumber}", [
                        'search_criteria' => [
                            'type_of_goods' => $itemData['type_of_goods'],
                            'category_code' => $itemData['category_code'],
                            'category_name' => $itemData['category_description']
                        ],
                        'available_categories' => \App\Models\Warehouse\ItemCategory::select('type_of_goods', 'category_code', 'category_name', 'status')->get()->toArray()
                    ]);
                    $errors[] = "Row {$rowNumber}: Category not found with type_of_goods='{$itemData['type_of_goods']}', category_code='{$itemData['category_code']}', category_description='{$itemData['category_description']}'";
                    continue;
                }

                \Log::info("✅ CATEGORY FOUND FOR ROW {$rowNumber}", [
                    'category' => [
                        'id' => $category->id,
                        'category_name' => $category->category_name,
                        'category_code' => $category->category_code,
                        'type_of_goods' => $category->type_of_goods
                    ]
                ]);

                // Find or create unit (comprehensive search)
                \Log::info("🔍 SEARCHING FOR UNIT FOR ROW {$rowNumber}", [
                    'unit_name' => $itemData['unit_of_measurement']
                ]);

                // Search by multiple criteria to find existing unit
                $unit = Unit::where('unit_name', $itemData['unit_of_measurement'])
                    ->orWhere('unit_symbol', $itemData['unit_of_measurement'])
                    ->orWhere('unit_code', strtoupper(substr($itemData['unit_of_measurement'], 0, 3)))
                    ->first();

                if (!$unit) {
                    \Log::info("🆕 CREATING NEW UNIT FOR ROW {$rowNumber}", [
                        'unit_name' => $itemData['unit_of_measurement']
                    ]);

                    // Check if unit exists by unit_code or unit_symbol to avoid duplicates
                    $unitCode = strtoupper(substr($itemData['unit_of_measurement'], 0, 3));
                    $existingUnitByCode = Unit::where('unit_code', $unitCode)
                        ->orWhere('unit_symbol', $itemData['unit_of_measurement'])
                        ->first();

                    if ($existingUnitByCode) {
                        \Log::info("✅ FOUND EXISTING UNIT BY CODE/SYMBOL FOR ROW {$rowNumber}", [
                            'found_unit_id' => $existingUnitByCode->id,
                            'found_unit_name' => $existingUnitByCode->unit_name,
                            'found_unit_code' => $existingUnitByCode->unit_code,
                            'searched_unit_name' => $itemData['unit_of_measurement']
                        ]);
                        $unit = $existingUnitByCode;
                    } else {
                        // Create new unit with unique code
                        try {
                            $unit = Unit::create([
                                'unit_code' => $unitCode,
                                'unit_name' => $itemData['unit_of_measurement'],
                                'unit_symbol' => $itemData['unit_of_measurement'],
                                'unit_type' => 'general',
                                'description' => 'Auto-created during item import',
                                'status' => 'active'
                            ]);

                            \Log::info("✅ NEW UNIT CREATED FOR ROW {$rowNumber}", [
                                'unit_id' => $unit->id,
                                'unit_name' => $unit->unit_name,
                                'unit_code' => $unit->unit_code
                            ]);
                        } catch (\Exception $unitException) {
                            \Log::warning("⚠️ UNIT CREATION FAILED, SEARCHING FOR EXISTING FOR ROW {$rowNumber}", [
                                'error' => $unitException->getMessage()
                            ]);

                            // If creation fails, try to find existing unit by code/symbol again
                            $unit = Unit::where('unit_code', $unitCode)
                                ->orWhere('unit_symbol', $itemData['unit_of_measurement'])
                                ->orWhere('unit_name', 'LIKE', '%' . $itemData['unit_of_measurement'] . '%')
                                ->first();

                            if (!$unit) {
                                throw new \Exception("Could not create or find unit for '{$itemData['unit_of_measurement']}'");
                            }

                            \Log::info("✅ FALLBACK UNIT FOUND FOR ROW {$rowNumber}", [
                                'unit_id' => $unit->id,
                                'unit_name' => $unit->unit_name
                            ]);
                        }
                    }
                } else {
                    \Log::info("✅ EXISTING UNIT FOUND FOR ROW {$rowNumber}", [
                        'unit_id' => $unit->id,
                        'unit_name' => $unit->unit_name
                    ]);
                }

                // Validate and normalize status (case insensitive)
                $originalStatus = $itemData['status'];
                $normalizedStatus = strtolower(trim($itemData['status']));

                \Log::info("🔍 VALIDATING STATUS FOR ROW {$rowNumber}", [
                    'original_status' => $originalStatus,
                    'normalized_status' => $normalizedStatus,
                    'valid_statuses' => ['active', 'inactive', 'discontinued']
                ]);

                if (!in_array($normalizedStatus, ['active', 'inactive', 'discontinued'])) {
                    \Log::warning("❌ INVALID STATUS FOR ROW {$rowNumber}", [
                        'provided_status' => $originalStatus,
                        'normalized_status' => $normalizedStatus
                    ]);
                    $errors[] = "Row {$rowNumber}: status must be 'active', 'inactive', or 'discontinued' (case insensitive)";
                    continue;
                }

                // Use normalized status
                $itemData['status'] = $normalizedStatus;
                \Log::info("✅ STATUS NORMALIZED FOR ROW {$rowNumber}", [
                    'original' => $originalStatus,
                    'normalized' => $normalizedStatus
                ]);

                // Generate item code if empty
                if (empty($itemData['item_code'])) {
                    \Log::info("🔢 GENERATING ITEM CODE FOR ROW {$rowNumber}");
                    $itemData['item_code'] = $this->generateItemCode($category->id);
                    \Log::info("✅ ITEM CODE GENERATED FOR ROW {$rowNumber}", [
                        'generated_item_code' => $itemData['item_code']
                    ]);
                } else {
                    \Log::info("🔍 CHECKING EXISTING ITEM CODE FOR ROW {$rowNumber}", [
                        'provided_item_code' => $itemData['item_code']
                    ]);

                    // Check for duplicate item code
                    if (\App\Models\Warehouse\Item::where('item_code', $itemData['item_code'])->exists()) {
                        \Log::warning("❌ DUPLICATE ITEM CODE FOR ROW {$rowNumber}", [
                            'duplicate_item_code' => $itemData['item_code']
                        ]);
                        $errors[] = "Row {$rowNumber}: Item code '{$itemData['item_code']}' already exists";
                        continue;
                    }
                    \Log::info("✅ ITEM CODE IS UNIQUE FOR ROW {$rowNumber}");
                }

                // Prepare final item data for creation
                $finalItemData = [
                    'item_code' => $itemData['item_code'],
                    'item_description' => $itemData['item_description'],
                    'category_id' => $category->id,
                    'unit_id' => $unit->id,
                    'unit_of_measure' => $itemData['unit_of_measurement'], // Keep for backward compatibility
                    'min_stock_level' => $itemData['min_stock_level'],
                    'max_stock_level' => $itemData['max_stock_level'],
                    'reorder_point' => $itemData['reorder_point'],
                    'status' => $itemData['status'],
                    'notes' => $itemData['notes']
                ];

                \Log::info("💾 CREATING ITEM FOR ROW {$rowNumber}", [
                    'final_item_data' => $finalItemData
                ]);

                // Create item
                $createdItem = \App\Models\Warehouse\Item::create($finalItemData);
                $imported++;

                \Log::info("✅ ITEM CREATED SUCCESSFULLY FOR ROW {$rowNumber}", [
                    'item_id' => $createdItem->id,
                    'item_code' => $createdItem->item_code,
                    'item_description' => $createdItem->item_description
                ]);

            } catch (\Exception $e) {
                \Log::error("💥 EXCEPTION FOR ROW {$rowNumber}", [
                    'exception_message' => $e->getMessage(),
                    'exception_file' => $e->getFile(),
                    'exception_line' => $e->getLine(),
                    'exception_trace' => $e->getTraceAsString()
                ]);
                $errors[] = "Row {$rowNumber}: " . $e->getMessage();
            }
        }

        \Log::info("🏁 IMPORT COMPLETED", [
            'total_rows_processed' => count($rows),
            'successfully_imported' => $imported,
            'errors_count' => count($errors),
            'errors' => $errors
        ]);

        return [
            'success' => $imported,
            'errors' => $errors
        ];
    }
}
