<?php

namespace App\Http\Controllers\Warehouse\FixedAssets;

use App\Http\Controllers\Controller;
use App\Models\Warehouse\FixedAssets\FixedAsset;
use App\Models\Warehouse\FixedAssets\FixedAssetCategory;
use App\Models\Warehouse\Supplier;
use Illuminate\Http\Request;
use Illuminate\Validation\Rule;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;

class FixedAssetController extends Controller
{
    public function __construct()
    {
        $this->middleware('warehouse.access');
        $this->middleware('warehouse.permission:warehouse.fixed-assets.view')->only(['index', 'show']);
        $this->middleware('warehouse.permission:warehouse.fixed-assets.create')->only(['create', 'store', 'import']);
        $this->middleware('warehouse.permission:warehouse.fixed-assets.edit')->only(['edit', 'update']);
        $this->middleware('warehouse.permission:warehouse.fixed-assets.delete')->only(['destroy']);
    }

    /**
     * Display a listing of fixed assets, or return JSON for DataTable AJAX.
     */
    public function index(Request $request)
    {
        if ($request->expectsJson() || $request->ajax() || $request->has('draw')) {
            return $this->getDataForTable($request);
        }

        $categories = FixedAssetCategory::where('status', 'active')->orderBy('category_name')->get();

        return view('warehouse.fixed-assets.catalog.index', compact('categories'));
    }

    /**
     * Return paginated, filtered JSON data for DataTables.
     */
    private function getDataForTable(Request $request)
    {
        try {
            $search = $request->input('search.value');

            $query = FixedAsset::with(['category', 'supplier', 'currentProject']);

            // Search
            if (!empty($search)) {
                $query->where(function ($q) use ($search) {
                    $q->where('asset_code', 'LIKE', "%{$search}%")
                      ->orWhere('asset_name', 'LIKE', "%{$search}%")
                      ->orWhere('asset_tag', 'LIKE', "%{$search}%")
                      ->orWhere('serial_number', 'LIKE', "%{$search}%")
                      ->orWhere('notes', 'LIKE', "%{$search}%")
                      ->orWhereHas('category', function ($cq) use ($search) {
                          $cq->where('category_name', 'LIKE', "%{$search}%");
                      });
                });
            }

            // Filters
            if ($request->filled('category_id')) {
                $query->where('category_id', $request->get('category_id'));
            }
            if ($request->filled('condition')) {
                $query->where('condition', $request->get('condition'));
            }
            if ($request->filled('lifecycle_status')) {
                $query->where('lifecycle_status', $request->get('lifecycle_status'));
            }
            if ($request->filled('status')) {
                $query->where('status', $request->get('status'));
            }

            $totalRecords = FixedAsset::count();
            $filteredRecords = $query->count();

            // Ordering
            if ($request->has('order')) {
                $orderColumnIndex = $request->input('order.0.column');
                $orderColumn = $request->input('columns.' . $orderColumnIndex . '.data');
                $orderDirection = $request->input('order.0.dir', 'asc');

                switch ($orderColumn) {
                    case 'asset_code':
                        $query->orderBy('asset_code', $orderDirection);
                        break;
                    case 'asset_name':
                        $query->orderBy('asset_name', $orderDirection);
                        break;
                    case 'lifecycle_status':
                        $query->orderBy('lifecycle_status', $orderDirection);
                        break;
                    case 'condition':
                        $query->orderBy('condition', $orderDirection);
                        break;
                    case 'purchase_price':
                        $query->orderBy('purchase_price', $orderDirection);
                        break;
                    case 'current_book_value':
                        $query->orderBy('current_book_value', $orderDirection);
                        break;
                    case 'status':
                        $query->orderBy('status', $orderDirection);
                        break;
                    default:
                        $query->orderBy('created_at', 'desc');
                        break;
                }
            } else {
                $query->orderBy('created_at', 'desc');
            }

            // Pagination
            $start = $request->input('start', 0);
            $length = $request->input('length', 10);

            if ($length != -1) {
                $query->skip($start)->take($length);
            }

            $items = $query->get();

            return response()->json([
                'draw' => intval($request->input('draw', 1)),
                'recordsTotal' => $totalRecords,
                'recordsFiltered' => $filteredRecords,
                'data' => $items->map(function ($item) {
                    return [
                        'id' => $item->id,
                        'asset_code' => $item->asset_code,
                        'asset_name' => $item->asset_name,
                        'asset_tag' => $item->asset_tag,
                        'serial_number' => $item->serial_number,
                        'category_name' => $item->category ? $item->category->category_name : 'No Category',
                        'condition' => $item->condition,
                        'lifecycle_status' => $item->lifecycle_status,
                        'current_project' => $item->currentProject ? $item->currentProject->project_name : '-',
                        'current_location' => $item->currentProject ? $item->currentProject->project_name : ($item->current_location ?: 'Warehouse'),
                        'current_book_value' => $item->current_book_value,
                        'purchase_price' => $item->purchase_price,
                        'status' => $item->status,
                        'created_at' => $item->created_at ? $item->created_at->format('M d, Y H:i') : '',
                    ];
                })->toArray()
            ]);
        } catch (\Exception $e) {
            \Log::error('FixedAssetController@getDataForTable error: ' . $e->getMessage(), [
                'file' => $e->getFile(),
                'line' => $e->getLine(),
            ]);

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

    /**
     * Show the form for creating a new fixed asset.
     */
    public function create()
    {
        $categories = FixedAssetCategory::where('status', 'active')->orderBy('category_name')->get();
        $suppliers = Supplier::where('status', 'active')->orderBy('name')->get();

        return view('warehouse.fixed-assets.catalog.create', compact('categories', 'suppliers'));
    }

    /**
     * Store a newly created fixed asset.
     */
    public function store(Request $request)
    {
        $validated = $request->validate([
            'asset_name' => 'required|string|max:255',
            'category_id' => 'required|exists:fixed_asset_categories,id',
            'supplier_id' => 'nullable|exists:suppliers,id',
            'asset_tag' => 'nullable|string|max:100|unique:fixed_assets,asset_tag',
            'serial_number' => 'nullable|string|max:100',
            'manufacturer' => 'nullable|string|max:100',
            'model_number' => 'nullable|string|max:100',
            'description' => 'nullable|string|max:2000',
            'purchase_order_number' => 'nullable|string|max:100',
            'invoice_number' => 'nullable|string|max:100',
            'invoice_date' => 'nullable|date',
            'purchase_date' => 'nullable|date',
            'purchase_price' => 'nullable|numeric|min:0',
            'currency' => 'nullable|string|max:10',
            'warranty_start_date' => 'nullable|date',
            'warranty_end_date' => 'nullable|date|after_or_equal:warranty_start_date',
            'warranty_terms' => 'nullable|string|max:500',
            'useful_life_years' => 'nullable|integer|min:1|max:100',
            'salvage_value' => 'nullable|numeric|min:0',
            'depreciation_method' => 'nullable|in:straight_line,declining_balance',
            'condition' => 'nullable|in:new,good,fair,poor,damaged,non_functional,written_off',
            'lifecycle_status' => 'nullable|in:acquired,in_warehouse,in_use,returned,under_maintenance,disposed',
            'status' => 'nullable|in:active,inactive',
            'notes' => 'nullable|string|max:2000',
            'photo' => 'nullable|image|max:5120',
        ]);

        if (!isset($validated['status'])) {
            $validated['status'] = 'active';
        }
        if (!isset($validated['condition'])) {
            $validated['condition'] = 'new';
        }
        if (!isset($validated['lifecycle_status'])) {
            $validated['lifecycle_status'] = 'in_warehouse';
        }
        if (empty($validated['current_location'])) {
            $validated['current_location'] = 'Warehouse';
        }
        if (!isset($validated['currency'])) {
            $validated['currency'] = 'AED';
        }

        // Set current_book_value to purchase_price initially
        if (isset($validated['purchase_price'])) {
            $validated['current_book_value'] = $validated['purchase_price'];
        }

        if ($request->hasFile('photo')) {
            $validated['photo'] = $request->file('photo')->store('fixed-assets/photos', 'public');
        }

        try {
            $asset = FixedAsset::create($validated);

            if ($request->expectsJson() || $request->ajax()) {
                return response()->json([
                    'success' => true,
                    'data' => $asset->load('category', 'supplier'),
                    'message' => 'Fixed asset created successfully.'
                ], 201);
            }

            return redirect()->route('warehouse.fixed-assets.catalog.index')
                ->with('success', 'Fixed asset created successfully.');
        } catch (\Exception $e) {
            \Log::error('FixedAssetController@store error: ' . $e->getMessage(), [
                'file' => $e->getFile(),
                'line' => $e->getLine(),
            ]);

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

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

    /**
     * Display the specified fixed asset.
     */
    public function show($id)
    {
        $asset = FixedAsset::with([
            'category',
            'supplier',
            'currentProject',
        ])->findOrFail($id);

        $movements = $asset->movements()->with(['fromProject', 'toProject', 'performedBy'])->latest()->take(20)->get();
        $maintenanceRecords = $asset->maintenanceRecords()->latest()->take(20)->get();
        $depreciationRecords = $asset->depreciationRecords()->latest()->take(20)->get();

        return view('warehouse.fixed-assets.catalog.show', compact('asset', 'movements', 'maintenanceRecords', 'depreciationRecords'));
    }

    /**
     * Show the form for editing the specified fixed asset.
     */
    public function edit($id)
    {
        $asset = FixedAsset::findOrFail($id);
        $categories = FixedAssetCategory::where('status', 'active')->orderBy('category_name')->get();
        $suppliers = Supplier::where('status', 'active')->orderBy('name')->get();

        if (request()->expectsJson()) {
            $asset->load('category', 'supplier');
            return response()->json([
                'success' => true,
                'data' => $asset,
                'categories' => $categories,
                'suppliers' => $suppliers,
            ]);
        }

        return view('warehouse.fixed-assets.catalog.edit', compact('asset', 'categories', 'suppliers'));
    }

    /**
     * Update the specified fixed asset.
     */
    public function update(Request $request, $id)
    {
        $asset = FixedAsset::findOrFail($id);

        $request->merge([
            'asset_code' => strtoupper(trim((string) $request->input('asset_code'))),
        ]);

        $validated = $request->validate([
            'asset_code' => [
                'required',
                'string',
                'max:50',
                Rule::unique('fixed_assets', 'asset_code')->ignore($asset->id),
            ],
            'asset_name' => 'required|string|max:255',
            'category_id' => 'required|exists:fixed_asset_categories,id',
            'supplier_id' => 'nullable|exists:suppliers,id',
            'asset_tag' => 'nullable|string|max:100|unique:fixed_assets,asset_tag,' . $id,
            'serial_number' => 'nullable|string|max:100',
            'brand' => 'nullable|string|max:100',
            'model' => 'nullable|string|max:100',
            'description' => 'nullable|string|max:2000',
            'purchase_date' => 'nullable|date',
            'purchase_price' => 'nullable|numeric|min:0',
            'warranty_expiry_date' => 'nullable|date',
            'useful_life_years' => 'nullable|integer|min:1|max:100',
            'salvage_value' => 'nullable|numeric|min:0',
            'depreciation_method' => 'nullable|in:straight_line,declining_balance',
            'condition' => 'required|in:new,good,fair,poor,damaged,written_off',
            'lifecycle_status' => 'required|in:acquired,in_warehouse,in_use,returned,under_maintenance,disposed',
            'status' => 'required|in:active,inactive',
            'notes' => 'nullable|string|max:2000',
        ]);

        try {
            $asset->update($validated);

            if ($request->expectsJson() || $request->ajax()) {
                return response()->json([
                    'success' => true,
                    'data' => $asset->fresh()->load('category', 'supplier'),
                    'message' => 'Fixed asset updated successfully.'
                ]);
            }

            return redirect()->route('warehouse.fixed-assets.catalog.show', $asset->id)
                ->with('success', 'Fixed asset updated successfully.');
        } catch (\Exception $e) {
            \Log::error('FixedAssetController@update error: ' . $e->getMessage(), [
                'file' => $e->getFile(),
                'line' => $e->getLine(),
            ]);

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

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

    /**
     * Remove the specified fixed asset.
     */
    public function destroy($id)
    {
        $asset = FixedAsset::findOrFail($id);

        // Only allow deletion if lifecycle_status is 'acquired' and no movements
        if ($asset->lifecycle_status !== 'acquired') {
            return response()->json([
                'success' => false,
                'message' => 'Cannot delete this asset. Only assets with lifecycle status "acquired" can be deleted.'
            ], 422);
        }

        $movementCount = $asset->movements()->count();
        if ($movementCount > 0) {
            return response()->json([
                'success' => false,
                'message' => "Cannot delete this asset because it has {$movementCount} movement record(s). Please clear the movements first."
            ], 422);
        }

        try {
            $asset->delete();

            return response()->json([
                'success' => true,
                'message' => 'Fixed asset deleted successfully.'
            ]);
        } catch (\Exception $e) {
            \Log::error('FixedAssetController@destroy error: ' . $e->getMessage());

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

    /**
     * AJAX dropdown for Select2 - returns active assets.
     */
    public function getDropdown(Request $request)
    {
        $search = $request->input('search', '');

        $query = FixedAsset::where('status', 'active');

        if (!empty($search)) {
            $query->where(function ($q) use ($search) {
                $q->where('asset_code', 'LIKE', "%{$search}%")
                  ->orWhere('asset_name', 'LIKE', "%{$search}%")
                  ->orWhere('asset_tag', 'LIKE', "%{$search}%")
                  ->orWhere('serial_number', 'LIKE', "%{$search}%");
            });
        }

        $assets = $query->orderBy('asset_name')
            ->limit(50)
            ->get()
            ->map(function ($item) {
                return [
                    'id' => $item->id,
                    'asset_code' => $item->asset_code,
                    'asset_name' => $item->asset_name,
                    'asset_tag' => $item->asset_tag,
                    'serial_number' => $item->serial_number,
                    'text' => $item->asset_name . ' (' . $item->asset_code . ')',
                ];
            });

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

    /**
     * AJAX dropdown for Select2 - returns assets available for allocation.
     */
    public function getAvailableAssets(Request $request)
    {
        $search = $request->input('search', '');

        $query = FixedAsset::availableForAllocation();

        if (!empty($search)) {
            $query->where(function ($q) use ($search) {
                $q->where('asset_code', 'LIKE', "%{$search}%")
                  ->orWhere('asset_name', 'LIKE', "%{$search}%")
                  ->orWhere('asset_tag', 'LIKE', "%{$search}%")
                  ->orWhere('serial_number', 'LIKE', "%{$search}%");
            });
        }

        $assets = $query->orderBy('asset_name')
            ->limit(50)
            ->get()
            ->map(function ($item) {
                return [
                    'id' => $item->id,
                    'asset_code' => $item->asset_code,
                    'asset_name' => $item->asset_name,
                    'asset_tag' => $item->asset_tag,
                    'serial_number' => $item->serial_number,
                    'condition' => $item->condition,
                    'current_book_value' => $item->current_book_value,
                    'text' => $item->asset_name . ' (' . $item->asset_code . ') - ' . ucfirst($item->condition),
                ];
            });

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

    /**
     * Export fixed assets to Excel.
     */
    public function export()
    {
        try {
            $assets = FixedAsset::with(['category', 'supplier', 'currentProject'])->orderBy('asset_code')->get();

            $spreadsheet = new Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();
            $sheet->setTitle('Fixed Assets');

            // Headers
            $headers = [
                'A1' => 'Asset Code',
                'B1' => 'Asset Name',
                'C1' => 'Asset Tag',
                'D1' => 'Serial Number',
                'E1' => 'Category',
                'F1' => 'Brand',
                'G1' => 'Model',
                'H1' => 'Supplier',
                'I1' => 'Purchase Date',
                'J1' => 'Purchase Price',
                'K1' => 'Current Book Value',
                'L1' => 'Useful Life (Years)',
                'M1' => 'Salvage Value',
                'N1' => 'Depreciation Method',
                'O1' => 'Condition',
                'P1' => 'Lifecycle Status',
                'Q1' => 'Current Project',
                'R1' => 'Status',
                'S1' => 'Notes',
                'T1' => 'Created At',
            ];

            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');
                $style->getBorders()->getAllBorders()
                    ->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
            }

            // Data rows
            $row = 2;
            foreach ($assets as $asset) {
                $sheet->setCellValue('A' . $row, $asset->asset_code);
                $sheet->setCellValue('B' . $row, $asset->asset_name);
                $sheet->setCellValue('C' . $row, $asset->asset_tag);
                $sheet->setCellValue('D' . $row, $asset->serial_number);
                $sheet->setCellValue('E' . $row, $asset->category ? $asset->category->category_name : '');
                $sheet->setCellValue('F' . $row, $asset->brand);
                $sheet->setCellValue('G' . $row, $asset->model);
                $sheet->setCellValue('H' . $row, $asset->supplier ? $asset->supplier->name : '');
                $sheet->setCellValue('I' . $row, $asset->purchase_date ? $asset->purchase_date->format('Y-m-d') : '');
                $sheet->setCellValue('J' . $row, $asset->purchase_price);
                $sheet->setCellValue('K' . $row, $asset->current_book_value);
                $sheet->setCellValue('L' . $row, $asset->useful_life_years);
                $sheet->setCellValue('M' . $row, $asset->salvage_value);
                $sheet->setCellValue('N' . $row, $asset->depreciation_method ? ucfirst(str_replace('_', ' ', $asset->depreciation_method)) : '');
                $sheet->setCellValue('O' . $row, ucfirst(str_replace('_', ' ', $asset->condition ?? '')));
                $sheet->setCellValue('P' . $row, ucfirst(str_replace('_', ' ', $asset->lifecycle_status ?? '')));
                $sheet->setCellValue('Q' . $row, $asset->currentProject ? $asset->currentProject->project_name : '');
                $sheet->setCellValue('R' . $row, ucfirst($asset->status));
                $sheet->setCellValue('S' . $row, $asset->notes);
                $sheet->setCellValue('T' . $row, $asset->created_at ? $asset->created_at->format('Y-m-d H:i') : '');

                // Add thin borders
                foreach (range('A', 'T') as $col) {
                    $sheet->getStyle($col . $row)->getBorders()->getAllBorders()
                        ->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
                }

                $row++;
            }

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

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

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

            $fileName = 'fixed_assets_export_' . date('Ymd_His') . '.xlsx';
            $filePath = $tempDir . '/' . $fileName;

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

            return response()->download($filePath, $fileName)->deleteFileAfterSend(true);
        } catch (\Exception $e) {
            \Log::error('FixedAssetController@export error: ' . $e->getMessage());

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

    /**
     * Download import template.
     */
    public function downloadTemplate()
    {
        try {
            $filePath = $this->generateImportTemplate();
            $filename = 'fixed_assets_import_template.xlsx';

            return response()->download($filePath, $filename)->deleteFileAfterSend(true);
        } catch (\Exception $e) {
            \Log::error('FixedAssetController@downloadTemplate error: ' . $e->getMessage());

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

    /**
     * Import fixed assets from Excel file.
     */
    public function import(Request $request)
    {
        $request->validate([
            'file' => 'required|file|mimes:xlsx,xls,csv|max:10240'
        ]);

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

            return response()->json([
                'success' => true,
                'imported' => $results['success'],
                'skipped' => count($results['errors']),
                'data' => $results,
                'message' => "Import completed. {$results['success']} fixed assets imported successfully."
            ]);
        } catch (\Exception $e) {
            \Log::error('FixedAssetController@import error: ' . $e->getMessage());

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

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

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

        $headers = [
            'A1' => 'asset_name',
            'B1' => 'category_code',
            'C1' => 'asset_tag',
            'D1' => 'serial_number',
            'E1' => 'brand',
            'F1' => 'model',
            'G1' => 'purchase_date',
            'H1' => 'purchase_price',
            'I1' => 'useful_life_years',
            'J1' => 'salvage_value',
            'K1' => 'depreciation_method',
            'L1' => 'condition',
            'M1' => 'status',
            'N1' => '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');
            $style->getBorders()->getAllBorders()
                ->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
        }

        // Sample data
        $sampleData = [
            2 => [
                'A' => 'Dell Laptop Latitude 5520',
                'B' => 'IT',
                'C' => 'IT-LAP-001',
                'D' => 'SN123456789',
                'E' => 'Dell',
                'F' => 'Latitude 5520',
                'G' => '2026-01-15',
                'H' => '5000',
                'I' => '5',
                'J' => '500',
                'K' => 'straight_line',
                'L' => 'new',
                'M' => 'active',
                'N' => 'Issued to IT department',
            ],
            3 => [
                'A' => 'Toyota Hilux Pickup',
                'B' => 'VEH',
                'C' => 'VEH-TRK-001',
                'D' => 'VIN987654321',
                'E' => 'Toyota',
                'F' => 'Hilux 2.4D',
                'G' => '2025-06-01',
                'H' => '120000',
                'I' => '10',
                'J' => '20000',
                'K' => 'declining_balance',
                'L' => 'good',
                'M' => 'active',
                'N' => 'Site vehicle',
            ],
        ];

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

        $sheet->freezePane('A2');

        // Comments
        $sheet->getComment('A1')->getText()->createTextRun('REQUIRED: Name/description of the fixed asset (max 255 chars).');
        $sheet->getComment('B1')->getText()->createTextRun('REQUIRED: Category code. Must match an existing active category.');
        $sheet->getComment('C1')->getText()->createTextRun('OPTIONAL: Unique asset tag identifier.');
        $sheet->getComment('D1')->getText()->createTextRun('OPTIONAL: Manufacturer serial number.');
        $sheet->getComment('E1')->getText()->createTextRun('OPTIONAL: Brand/manufacturer name.');
        $sheet->getComment('F1')->getText()->createTextRun('OPTIONAL: Model name/number.');
        $sheet->getComment('G1')->getText()->createTextRun('OPTIONAL: Purchase date (YYYY-MM-DD format).');
        $sheet->getComment('H1')->getText()->createTextRun('OPTIONAL: Purchase price (numeric).');
        $sheet->getComment('I1')->getText()->createTextRun('OPTIONAL: Useful life in years (integer).');
        $sheet->getComment('J1')->getText()->createTextRun('OPTIONAL: Salvage/residual value (numeric).');
        $sheet->getComment('K1')->getText()->createTextRun('OPTIONAL: Depreciation method (straight_line or declining_balance).');
        $sheet->getComment('L1')->getText()->createTextRun('OPTIONAL: Condition (new, good, fair, poor, damaged, written_off).');
        $sheet->getComment('M1')->getText()->createTextRun('REQUIRED: Status must be "active" or "inactive".');
        $sheet->getComment('N1')->getText()->createTextRun('OPTIONAL: Additional notes.');

        foreach (range('A', 'N') as $col) {
            $sheet->getColumnDimension($col)->setAutoSize(true);
        }

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

        $instructions = [
            'FIXED ASSETS IMPORT TEMPLATE',
            '',
            'COLUMN DESCRIPTIONS:',
            '',
            '1. asset_name (REQUIRED) - Name/description of the fixed asset, max 255 characters.',
            '2. category_code (REQUIRED) - Must match an existing active category code exactly.',
            '3. asset_tag (OPTIONAL) - Unique asset tag identifier. Must be unique across all assets.',
            '4. serial_number (OPTIONAL) - Manufacturer serial number.',
            '5. brand (OPTIONAL) - Brand or manufacturer name.',
            '6. model (OPTIONAL) - Model name or number.',
            '7. purchase_date (OPTIONAL) - Date of purchase in YYYY-MM-DD format.',
            '8. purchase_price (OPTIONAL) - Purchase price, numeric value.',
            '9. useful_life_years (OPTIONAL) - Expected useful life in years.',
            '10. salvage_value (OPTIONAL) - Expected residual value at end of useful life.',
            '11. depreciation_method (OPTIONAL) - Method: "straight_line" or "declining_balance".',
            '12. condition (OPTIONAL) - Asset condition: new, good, fair, poor, damaged, written_off.',
            '13. status (REQUIRED) - Must be exactly "active" or "inactive".',
            '14. notes (OPTIONAL) - Any additional notes or details.',
            '',
            'IMPORTANT NOTES:',
            '- The Asset Code will be auto-generated based on the category.',
            '- Delete all sample data rows before importing your data.',
            '- Keep the header row intact.',
            '- Supported file formats: .xlsx, .xls, .csv',
            '- Maximum file size: 10MB',
            '',
            'VALIDATION:',
            '- Rows with missing required fields will be skipped.',
            '- Rows with invalid category references will be skipped.',
            '- Duplicate asset_tag values will cause the row to be skipped.',
            '- Check the import results summary for any errors.',
        ];

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

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

        $spreadsheet->setActiveSheetIndex(0);

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

        $filePath = $tempDir . '/fixed_assets_import_template.xlsx';

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

        return $filePath;
    }

    /**
     * Process the Excel file for import.
     */
    private function importFromExcel($file)
    {
        $spreadsheet = IOFactory::load($file->getPathname());
        $sheet = $spreadsheet->getActiveSheet();
        $rows = $sheet->toArray();

        // Remove header row
        array_shift($rows);

        $imported = 0;
        $errors = [];

        foreach ($rows as $index => $row) {
            $rowNumber = $index + 2;

            // Skip empty rows
            if (empty(array_filter($row))) {
                continue;
            }

            try {
                $data = [
                    'asset_name' => trim($row[0] ?? ''),
                    'category_code' => trim($row[1] ?? ''),
                    'asset_tag' => trim($row[2] ?? ''),
                    'serial_number' => trim($row[3] ?? ''),
                    'brand' => trim($row[4] ?? ''),
                    'model' => trim($row[5] ?? ''),
                    'purchase_date' => trim($row[6] ?? ''),
                    'purchase_price' => is_numeric($row[7]) ? (float) $row[7] : null,
                    'useful_life_years' => is_numeric($row[8]) ? (int) $row[8] : null,
                    'salvage_value' => is_numeric($row[9]) ? (float) $row[9] : null,
                    'depreciation_method' => trim($row[10] ?? ''),
                    'condition' => strtolower(trim($row[11] ?? 'new')),
                    'status' => strtolower(trim($row[12] ?? 'active')),
                    'notes' => trim($row[13] ?? ''),
                ];

                // Validate required fields
                if (empty($data['asset_name'])) {
                    $errors[] = "Row {$rowNumber}: asset_name is required.";
                    continue;
                }
                if (empty($data['category_code'])) {
                    $errors[] = "Row {$rowNumber}: category_code is required.";
                    continue;
                }
                if (!in_array($data['status'], ['active', 'inactive'])) {
                    $errors[] = "Row {$rowNumber}: status must be 'active' or 'inactive'.";
                    continue;
                }

                // Find category
                $category = FixedAssetCategory::where('category_code', $data['category_code'])
                    ->where('status', 'active')
                    ->first();

                if (!$category) {
                    $errors[] = "Row {$rowNumber}: Category not found (code='{$data['category_code']}').";
                    continue;
                }

                // Check for duplicate asset tag
                if (!empty($data['asset_tag'])) {
                    $existingTag = FixedAsset::where('asset_tag', $data['asset_tag'])->first();
                    if ($existingTag) {
                        $errors[] = "Row {$rowNumber}: Asset tag '{$data['asset_tag']}' already exists.";
                        continue;
                    }
                }

                // Validate condition
                $validConditions = ['new', 'good', 'fair', 'poor', 'damaged', 'written_off'];
                if (!in_array($data['condition'], $validConditions)) {
                    $data['condition'] = 'new';
                }

                // Validate depreciation method
                $validMethods = ['straight_line', 'declining_balance'];
                if (!empty($data['depreciation_method']) && !in_array($data['depreciation_method'], $validMethods)) {
                    $data['depreciation_method'] = $category->depreciation_method ?? 'straight_line';
                }

                // Create fixed asset
                FixedAsset::create([
                    'asset_name' => $data['asset_name'],
                    'category_id' => $category->id,
                    'asset_tag' => !empty($data['asset_tag']) ? $data['asset_tag'] : null,
                    'serial_number' => !empty($data['serial_number']) ? $data['serial_number'] : null,
                    'brand' => !empty($data['brand']) ? $data['brand'] : null,
                    'model' => !empty($data['model']) ? $data['model'] : null,
                    'purchase_date' => !empty($data['purchase_date']) ? $data['purchase_date'] : null,
                    'purchase_price' => $data['purchase_price'],
                    'current_book_value' => $data['purchase_price'],
                    'useful_life_years' => $data['useful_life_years'] ?? $category->default_useful_life_years,
                    'salvage_value' => $data['salvage_value'],
                    'depreciation_method' => !empty($data['depreciation_method']) ? $data['depreciation_method'] : ($category->depreciation_method ?? 'straight_line'),
                    'condition' => $data['condition'],
                    'lifecycle_status' => 'acquired',
                    'status' => $data['status'],
                    'notes' => !empty($data['notes']) ? $data['notes'] : null,
                ]);

                $imported++;
            } catch (\Exception $e) {
                $errors[] = "Row {$rowNumber}: " . $e->getMessage();
            }
        }

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