<?php

namespace App\Http\Controllers;

use App\Models\ItemRequest;
use App\Models\ItemRequestDetail;
use App\Models\ItemRequestCancellation;
use App\Models\Store;
use App\Models\StockOpname;
use App\Models\ShipmentDelivery;
use App\Models\RawMaterial;
use App\Models\TargetSisaMalam;
use App\Models\Forecast;
use App\Models\StoreRawMaterial;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Storage;
use Illuminate\Validation\Rule;
use Carbon\Carbon;

class ItemRequestsController extends Controller
{

    public function index(Request $request)
    {
        if ($request->ajax()) {
            $query = ItemRequest::select('t_item_requests.*')
                ->with(['store', 'stockOpname']);

            // Searching
            if ($request->has('search') && $request->search['value'] != '') {
                $search = $request->search['value'];
                $query->where(function ($q) use ($search) {
                    $q->where('t_item_requests.spb_number', 'ilike', "%{$search}%")
                        ->orWhereHas('store', function ($sq) use ($search) {
                            $sq->where('name', 'ilike', "%{$search}%");
                        })
                        ->orWhere('t_item_requests.request_type', 'ilike', "%{$search}%")
                        ->orWhere('t_item_requests.status', 'ilike', "%{$search}%");
                });
            }

            $total = $query->count();

            // Sorting
            $columns = ['id', 'spb_number', 'store_id', 'request_type', 'request_date', 'delivery_date', 'status', 'created_at'];
            $orderColumn = $columns[$request->order[0]['column']] ?? 'created_at';
            $orderDir = $request->order[0]['dir'] ?? 'desc';

            $query->orderBy($orderColumn, $orderDir);

            // Pagination
            $start = $request->start;
            $length = $request->length;
            $data = $query->skip($start)->take($length)->get();

            // Format data
            $result = [];
            $no = $start + 1;
            foreach ($data as $row) {
                $statusBadge = $row->status == 'Draft' ? 'badge-secondary' : 
                              ($row->status == 'Submitted' ? 'badge-success' : 
                              ($row->status == 'Approved' ? 'badge-primary' : 
                              ($row->status == 'Cancelled' ? 'badge-danger' :
                              ($row->status == 'Cancellation_Requested' ? 'badge-dark' : 'badge-secondary'))));
                
                $typeBadge = $row->request_type == 'Regular' ? 'badge-info' : 'badge-dark';
                
                $result[] = [
                    $no++,
                    '<a href="' . route('item_requests.show', $row->id) . '">' . ($row->spb_number ?? 'Not Generated') . '</a>',
                    $row->store->name ?? '',
                    '<span class="badge ' . $typeBadge . '">' . $row->request_type . '</span>',
                    $row->request_date->format('d M Y'),
                    $row->delivery_date ? $row->delivery_date->format('d M Y') : '',
                    '<span class="badge ' . $statusBadge . '">' . $row->status . '</span>',
                    $row->created_at->format('M jS Y, H:i'),
                ];
            }

            return response()->json([
                "draw" => intval($request->draw),
                "recordsTotal" => $total,
                "recordsFiltered" => $total,
                "data" => $result,
            ]);
        }

        return view('t-item-requests.index', [
            'variables' => $this->variables()
        ]);
    }

    public function create()
    {
        $variables['judul'] = 'Tambah SPB Non-Regular (Additional)';
        $variables['stores'] = Store::where('delete_flag', 0)->orderBy('name')->get();
        $variables['stock_opnames'] = StockOpname::where('delete_flag', 0)
            ->where('status', 'Submitted')
            ->with('store')
            ->orderBy('so_date', 'desc')
            ->get();
        $variables['shipment_deliveries'] = ShipmentDelivery::where('delete_flag', 0)
            ->where('status', 'Active')
            ->with(['store', 'armadaType'])
            ->orderBy('day_name')
            ->get();
        $variables['raw_materials'] = collect(); // Will be loaded via AJAX based on selected store
        
        // Auto-select SO if coming from SO page
        $selectedSO = request()->get('so_id');
        if ($selectedSO) {
            $stockOpname = StockOpname::find($selectedSO);
            if ($stockOpname) {
                // Business rule: Check if Regular SPB already exists for this SO
                if (ItemRequest::hasRegularSPBForSO($selectedSO)) {
                    return redirect()->route('item_requests.index')
                        ->with('error', 'Regular SPB already exists for this Stock Opname. Only one Regular SPB per SO is allowed.');
                }
                
                $variables['selected_so'] = $stockOpname;
                // Load raw materials for the selected SO's store (SPB enabled only)
                $variables['raw_materials'] = StoreRawMaterial::getRawMaterialsForSPB($stockOpname->store_id);
            }
        }
        
        return view('t-item-requests.form', [
            'data' => json_decode(json_encode(array(
                'id' => 0, 
                'request_date' => date('Y-m-d'),
                'request_type' => 'Regular',
                'status' => 'Draft',
                'stock_opname_id' => $selectedSO
            ))),
            'variables' => $this->variables($variables)
        ]);
    }

    public function edit(ItemRequest $itemRequest)
    {
        if (!$itemRequest->canBeEdited()) {
            return redirect()->route('item_requests.show', $itemRequest->id)
                ->with('error', 'Cannot edit SPB in current status: ' . $itemRequest->status);
        }

        $itemRequest->load('details.rawMaterial');
        $variables['judul'] = 'Edit SPB #' . $itemRequest->spb_number . ' (' . $itemRequest->request_type . ')';
        $variables['stores'] = Store::where('delete_flag', 0)->orderBy('name')->get();
        $variables['stock_opnames'] = StockOpname::where('delete_flag', 0)
            ->where('status', 'Submitted')
            ->with('store')
            ->orderBy('so_date', 'desc')
            ->get();
        $variables['shipment_deliveries'] = ShipmentDelivery::where('delete_flag', 0)
            ->where('status', 'Active')
            ->with(['store', 'armadaType'])
            ->orderBy('day_name')
            ->get();
        // Get raw materials for the selected store (SPB enabled only)
        $variables['raw_materials'] = StoreRawMaterial::getRawMaterialsForSPB($itemRequest->store_id);
        
        return view('t-item-requests.form', [
            'data' => $itemRequest,
            'variables' => $this->variables($variables)
        ]);
    }

    private function variables($variables = null)
    {
        $variables['header_routes'] = ' 
								<li class="breadcrumb-item">Transaction</li>  
								<li class="breadcrumb-item active">Item Request (SPB)</li>
							 ';
        return $variables;
    }

    public function store(Request $request, $prm_id)
    {
        $itemRequest = null;
        if ($prm_id)
            $itemRequest = ItemRequest::findOrFail($prm_id);

        // Validasi header
        $rules = [
            'store_id' => 'required|exists:m_stores,id',
            'stock_opname_id' => 'nullable|exists:t_stock_opnames,id',
            'request_type' => 'required|in:Regular,Additional',
            'request_date' => 'required|date',
            'delivery_date' => 'nullable|date|after_or_equal:request_date',
            'shipment_schedule_id' => 'nullable|exists:m_shipment_deliveries,id',
            'notes' => 'nullable|string',
            'status' => 'required|in:Draft,Submitted,Approved',
        ];

        // Business rule validation for Regular SPB
        if ($request->request_type == 'Regular' && $request->stock_opname_id) {
            // Check if Regular SPB already exists for this SO (except current record)
            $existingRegularSPB = ItemRequest::where('stock_opname_id', $request->stock_opname_id)
                ->where('request_type', 'Regular')
                ->where('delete_flag', 0)
                ->whereNotIn('status', [ItemRequest::STATUS_CANCELLED]);
            
            if ($itemRequest) {
                $existingRegularSPB->where('id', '!=', $itemRequest->id);
            }
            
            if ($existingRegularSPB->exists()) {
                return response()->json(['warning', 'Regular SPB already exists for this Stock Opname. Only one Regular SPB per SO is allowed.']);
            }
        }

        // Business rule: Additional SPB should not have stock_opname_id
        if ($request->request_type == 'Additional' && $request->stock_opname_id) {
            return response()->json(['warning', 'Additional SPB should not be linked to Stock Opname.']);
        }

        // Validasi detail items
        if ($request->has('items')) {
            $rules['items.*.raw_material_id'] = 'required|exists:m_raw_materials,id';
            $rules['items.*.forecast_qty'] = 'nullable|numeric|min:0';
            $rules['items.*.so_qty'] = 'nullable|numeric|min:0';
            $rules['items.*.tsm_qty'] = 'nullable|numeric|min:0';
            $rules['items.*.suggested_qty'] = 'required|numeric|min:0';
            $rules['items.*.adjusted_qty'] = 'nullable|numeric|min:0';
        }

        $messages = [];
        $saveData = $request->validate($rules, $messages);

        try {
            DB::beginTransaction();
            
            if ($itemRequest) {
                $itemRequest->update(array_merge($saveData, [
                    'updated_by' => $this->logged_user_id,
                ]));
                
                // Delete existing details
                ItemRequestDetail::where('item_request_id', $itemRequest->id)->delete();
            } else {
                $itemRequest = ItemRequest::create(array_merge($saveData, [
                    'created_by' => $this->logged_user_id,
                ]));
            }

            // Save details
            if ($request->has('items')) {
                foreach ($request->items as $item) {
                    if (!empty($item['raw_material_id'])) {
                        ItemRequestDetail::create([
                            'item_request_id' => $itemRequest->id,
                            'raw_material_id' => $item['raw_material_id'],
                            'forecast_qty' => $item['forecast_qty'] ?? 0,
                            'so_qty' => $item['so_qty'] ?? 0,
                            'tsm_qty' => $item['tsm_qty'] ?? 0,
                            'suggested_qty' => $item['suggested_qty'] ?? 0,
                            'adjusted_qty' => $item['adjusted_qty'] ?? 0,
                            'notes' => $item['notes'] ?? null,
                        ]);
                    }
                }
            }

            DB::commit();
            
            $notif = $itemRequest->wasRecentlyCreated ? 'Successfully created.' : 'Updated successfully.';
            return response()->json(['success', $notif]);
            
        } catch (\Exception $e) {
            DB::rollback();

            $notif = $this->getErrorCatch($e->getMessage());
            return response()->json(['warning', $notif]);
        }
    }

    public function show(ItemRequest $itemRequest)
    {
        $itemRequest->load(['creator', 'modificator', 'store', 'stockOpname', 'ShipmentDelivery.armadaType', 'details.rawMaterial', 'cancellations.requestedBy', 'cancelledBy']);
        $variables['judul'] = 'Detail SPB #' . $itemRequest->spb_number . ' (' . $itemRequest->request_type . ')';
        return view('t-item-requests.show', [
            'data' => $itemRequest,
            'variables' => $this->variables($variables)
        ]);
    }

    public function destroy(ItemRequest $itemRequest)
    {
        if (!in_array($itemRequest->status, [ItemRequest::STATUS_DRAFT, ItemRequest::STATUS_CANCELLED])) {
            return redirect()->route('item_requests.index')
                ->with('error', 'Cannot delete SPB in current status: ' . $itemRequest->status);
        }

        $itemRequest->update([
            'delete_flag' => 1,
            'updated_by' => $this->logged_user_id
        ]);

        return redirect()->route('item_requests.index')
            ->with('success', 'Successfully deleted');
    }

    // Generate SPB from Stock Opname
    public function generateFromSO(Request $request)
    {
        $soId = $request->stock_opname_id;
        $stockOpname = StockOpname::with(['details.rawMaterial', 'store'])->findOrFail($soId);
        
        // Get Target Sisa Malam for this store
        $targets = TargetSisaMalam::where('store_id', $stockOpname->store_id)
            ->where('status', 'Active')
            ->where('delete_flag', 0)
            ->with('rawMaterial')
            ->get()
            ->keyBy('raw_material_id');

        // Get current month forecast for this store
        $currentMonth = date('n');
        $currentYear = date('Y');
        $forecasts = Forecast::where('store_id', $stockOpname->store_id)
            ->where('month', $currentMonth)
            ->where('year', $currentYear)
            ->where('status', 'Active')
            ->where('delete_flag', 0)
            ->get()
            ->keyBy('raw_material_id');

        $suggestions = [];
        
        foreach ($stockOpname->details as $detail) {
            $target = $targets->get($detail->raw_material_id);
            $targetQty = $target ? $target->target_qty : 0;
            
            $forecast = $forecasts->get($detail->raw_material_id);
            $forecastQty = $forecast ? $forecast->forecast_qty : 0;
            
            // Simple calculation: suggested = target - current stock
            $suggestedQty = max(0, $targetQty - $detail->qty_total);
            
            if ($suggestedQty > 0) {
                $suggestions[] = [
                    'raw_material_id' => $detail->raw_material_id,
                    'raw_material' => $detail->rawMaterial,
                    'so_qty' => $detail->qty_total,
                    'tsm_qty' => $targetQty,
                    'forecast_qty' => $forecastQty,
                    'suggested_qty' => $suggestedQty,
                ];
            }
        }

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

    // Get forecast comparison for item
    public function getForecastComparison(Request $request)
    {
        $storeId = $request->store_id;
        $rawMaterialId = $request->raw_material_id;
        $requestedQty = $request->requested_qty;
        
        if (!$storeId || !$rawMaterialId || !$requestedQty) {
            return response()->json(['success' => false, 'message' => 'Missing parameters']);
        }

        // Get current month forecast
        $currentMonth = date('n');
        $currentYear = date('Y');
        
        $forecast = Forecast::where('store_id', $storeId)
            ->where('raw_material_id', $rawMaterialId)
            ->where('month', $currentMonth)
            ->where('year', $currentYear)
            ->where('status', 'Active')
            ->where('delete_flag', 0)
            ->first();

        if (!$forecast) {
            return response()->json([
                'success' => true,
                'status' => 'no_forecast',
                'message' => 'No forecast data available',
                'alert_class' => 'warning',
                'forecast_qty' => 0
            ]);
        }

        $forecastQty = $forecast->forecast_qty;
        $requestedQty = floatval($requestedQty);
        
        // Calculate variance percentage
        $variance = $forecastQty > 0 ? (($requestedQty - $forecastQty) / $forecastQty) * 100 : 0;
        
        // Determine status and alert
        if ($requestedQty > $forecastQty) {
            if ($variance > 20) {
                $status = 'over_forecast_high';
                $message = 'Request exceeds forecast by ' . number_format($variance, 1) . '% (High Risk)';
                $alertClass = 'danger';
            } else {
                $status = 'over_forecast_low';
                $message = 'Request exceeds forecast by ' . number_format($variance, 1) . '%';
                $alertClass = 'warning';
            }
        } elseif ($requestedQty < $forecastQty) {
            if (abs($variance) > 20) {
                $status = 'under_forecast_high';
                $message = 'Request below forecast by ' . number_format(abs($variance), 1) . '% (Check if sufficient)';
                $alertClass = 'info';
            } else {
                $status = 'under_forecast_low';
                $message = 'Request below forecast by ' . number_format(abs($variance), 1) . '%';
                $alertClass = 'secondary';
            }
        } else {
            $status = 'match_forecast';
            $message = 'Request matches forecast exactly';
            $alertClass = 'success';
        }

        return response()->json([
            'success' => true,
            'status' => $status,
            'message' => $message,
            'alert_class' => $alertClass,
            'forecast_qty' => $forecastQty,
            'requested_qty' => $requestedQty,
            'variance' => $variance
        ]);
    }

    /**
     * Generate CSV files from SPB
     * Group items by tipe_dokumen from store-item mapping and generate separate CSV files
     */
    public function generateCsv($id)
    {
        try {
            $spb = ItemRequest::with(['details.rawMaterial', 'store'])->findOrFail($id);

            // Check if SPB is approved
            if ($spb->status !== 'Approved') {
                return response()->json([
                    'success' => false,
                    'message' => 'SPB must be approved before generating CSV'
                ], 400);
            }

            $result = $this->generateCsvFromSPB($spb);

            // Update SPB with CSV generation info
            $spb->update([
                'csv_generated_at' => now(),
                'csv_generated_by' => auth()->id(),
                'updated_by' => auth()->id()
            ]);

            Log::info('CSV generated successfully', [
                'spb_id' => $spb->id,
                'spb_number' => $spb->spb_number,
                'files' => $result['files'],
                'user_id' => auth()->id()
            ]);

            return response()->json([
                'success' => true,
                'message' => 'CSV files generated successfully',
                'data' => $result
            ]);

        } catch (\Exception $e) {
            Log::error('CSV generation failed', [
                'spb_id' => $id,
                'error' => $e->getMessage(),
                'user_id' => auth()->id()
            ]);

            return response()->json([
                'success' => false,
                'message' => 'Failed to generate CSV: ' . $e->getMessage()
            ], 500);
        }
    }

    /**
     * Download CSV file
     */
    public function downloadCsv(Request $request, $id)
    {
        try {
            $spb = ItemRequest::findOrFail($id);
            $filepath = $request->get('file');

            if (!$filepath) {
                return response()->json([
                    'success' => false,
                    'message' => 'File path is required'
                ], 400);
            }
            
            Log::info('CSV download requested', [
                'spb_id' => $spb->id,
                'file' => $filepath,
                'user_id' => auth()->id()
            ]);

            return $this->downloadCsvFile($filepath);

        } catch (\Exception $e) {
            Log::error('CSV download failed', [
                'spb_id' => $id,
                'error' => $e->getMessage(),
                'user_id' => auth()->id()
            ]);

            return response()->json([
                'success' => false,
                'message' => 'Failed to download CSV: ' . $e->getMessage()
            ], 500);
        }
    }

    /**
     * Get list of generated CSV files for SPB
     */
    public function listCsvFiles($id)
    {
        try {
            $spb = ItemRequest::findOrFail($id);
            $files = $this->getGeneratedCsvFiles($spb);

            return response()->json([
                'success' => true,
                'data' => [
                    'spb_number' => $spb->spb_number,
                    'files' => $files,
                    'total_files' => count($files)
                ]
            ]);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Failed to list CSV files: ' . $e->getMessage()
            ], 500);
        }
    }

    /**
     * Delete CSV files for SPB
     */
    public function deleteCsvFiles($id)
    {
        try {
            $spb = ItemRequest::findOrFail($id);
            $deletedCount = $this->deleteCsvFilesForSPB($spb);

            // Update SPB
            $spb->update([
                'csv_generated_at' => null,
                'csv_generated_by' => null,
                'updated_by' => auth()->id()
            ]);

            Log::info('CSV files deleted', [
                'spb_id' => $spb->id,
                'files_deleted' => $deletedCount,
                'user_id' => auth()->id()
            ]);

            return response()->json([
                'success' => true,
                'message' => "Successfully deleted {$deletedCount} CSV file(s)"
            ]);

        } catch (\Exception $e) {
            Log::error('CSV deletion failed', [
                'spb_id' => $id,
                'error' => $e->getMessage(),
                'user_id' => auth()->id()
            ]);

            return response()->json([
                'success' => false,
                'message' => 'Failed to delete CSV files: ' . $e->getMessage()
            ], 500);
        }
    }

    // ========================================================================
    // PRIVATE METHODS - CSV Generation Logic
    // ========================================================================

    /**
     * Generate CSV files from SPB
     * Returns array of generated file paths grouped by document type
     */
    private function generateCsvFromSPB(ItemRequest $spb)
    {
        Log::info('Starting CSV generation from SPB', [
            'spb_id' => $spb->id,
            'spb_number' => $spb->spb_number,
            'user_id' => auth()->id()
        ]);

        // Group items by tipe_dokumen from store-item mapping
        $itemsByType = $spb->details->groupBy(function($detail) use ($spb) {
            return \App\Models\StoreRawMaterial::getTipeDocumen($spb->store_id, $detail->raw_material_id);
        });

        $csvFiles = [];
        $errors = [];

        try {
            // Generate CSV DO
            if ($itemsByType->has('DO')) {
                $csvFiles['DO'] = $this->generateDOCsv($spb, $itemsByType['DO']);
                Log::info('CSV DO generated', ['file' => $csvFiles['DO'], 'items_count' => $itemsByType['DO']->count()]);
            }

            // Generate CSV PO
            if ($itemsByType->has('PO')) {
                $csvFiles['PO'] = $this->generatePOCsv($spb, $itemsByType['PO']);
                Log::info('CSV PO generated', ['file' => $csvFiles['PO'], 'items_count' => $itemsByType['PO']->count()]);
            }

            // Generate CSV PR
            if ($itemsByType->has('PR')) {
                $csvFiles['PR'] = $this->generatePRCsv($spb, $itemsByType['PR']);
                Log::info('CSV PR generated', ['file' => $csvFiles['PR'], 'items_count' => $itemsByType['PR']->count()]);
            }

            Log::info('CSV generation completed', [
                'spb_id' => $spb->id,
                'files_generated' => count($csvFiles),
                'types' => array_keys($csvFiles)
            ]);

        } catch (\Exception $e) {
            Log::error('CSV generation failed', [
                'spb_id' => $spb->id,
                'error' => $e->getMessage(),
                'trace' => $e->getTraceAsString()
            ]);
            throw $e;
        }

        // Format files array for response
        $formattedFiles = [];
        foreach ($csvFiles as $type => $filepath) {
            $fullPath = storage_path('app/' . $filepath);
            $itemCount = $itemsByType->has($type) ? $itemsByType[$type]->count() : 0;
            
            $formattedFiles[] = [
                'type' => $type,
                'filename' => basename($filepath),
                'path' => $filepath,
                'records' => $itemCount,
                'size' => file_exists($fullPath) ? $this->formatFileSize(filesize($fullPath)) : 'N/A'
            ];
        }

        return [
            'success' => true,
            'files' => $formattedFiles,
            'total_records' => $spb->details->count(),
            'errors' => $errors,
            'summary' => [
                'total_files' => count($csvFiles),
                'do_items' => $itemsByType->has('DO') ? $itemsByType['DO']->count() : 0,
                'po_items' => $itemsByType->has('PO') ? $itemsByType['PO']->count() : 0,
                'pr_items' => $itemsByType->has('PR') ? $itemsByType['PR']->count() : 0,
            ]
        ];
    }

    /**
     * Generate Delivery Order CSV
     * Format: 12 columns
     */
    private function generateDOCsv(ItemRequest $spb, $items)
    {
        $timestamp = Carbon::now()->format('YmdHis');
        $filename = "DO_{$spb->spb_number}_{$timestamp}.csv";
        $filepath = "exports/csv/{$filename}";

        // Ensure directory exists
        Storage::makeDirectory('exports/csv');

        $csvContent = [];

        foreach ($items as $detail) {
            $row = [
                $this->generateDONumber($spb),                          // Order No (8 char)
                config('qad.warehouse_code', '101'),                    // Ship From (8 char)
                $spb->store->code ?? '',                                // Ship To (8 char)
                $spb->request_date->format('n/j/Y'),                   // Order Date
                $spb->delivery_date ? $spb->delivery_date->format('n/j/Y') : '', // Due Date
                $this->formatRemarks($spb),                            // Remarks (40 char)
                $detail->rawMaterial->code ?? '',                      // Item Number
                $detail->rawMaterial->name ?? '',                      // Item Name
                $detail->final_qty ?? 0,                               // Qty Ordered (from SPB!)
                $detail->rawMaterial->uom ?? '',                       // UoM
                config('qad.transport_id', 'INTGMS'),                  // Transport ID (8 char)
                $detail->notes ?? ''                                   // Keterangan (100 char)
            ];

            $csvContent[] = $row;
        }

        // Write CSV with pipe delimiter
        $handle = fopen(storage_path("app/{$filepath}"), 'w');
        foreach ($csvContent as $row) {
            fputcsv($handle, $row, '|');
        }
        fclose($handle);

        return $filepath;
    }

    /**
     * Generate Purchase Order CSV
     * Format: 18 columns
     */
    private function generatePOCsv(ItemRequest $spb, $items)
    {
        $timestamp = Carbon::now()->format('YmdHis');
        $filename = "PO_{$spb->spb_number}_{$timestamp}.csv";
        $filepath = "exports/csv/{$filename}";

        // Ensure directory exists
        Storage::makeDirectory('exports/csv');

        $csvContent = [];
        $lineNumber = 1;

        foreach ($items as $detail) {
            $row = [
                $this->generatePONumber($spb),                          // Purchase Order (8 char)
                $detail->rawMaterial->supplier_code ?? 'GMS',          // Supplier (8 char)
                $spb->request_date->format('n/j/Y'),                   // Order Date
                $spb->delivery_date ? $spb->delivery_date->format('n/j/Y') : '', // Due Date
                $this->formatRemarks($spb),                            // Remarks (40 char)
                $spb->store->site_code ?? '0A111',                     // Site (8 char)
                'IDR',                                                  // Currency (3 char)
                $detail->rawMaterial->credit_terms ?? '',              // Credit Terms (8 char)
                $lineNumber++,                                          // Line (Integer)
                $detail->rawMaterial->code ?? '',                      // Item Number (18 char)
                $detail->rawMaterial->name ?? '',                      // Item Name
                $detail->final_qty ?? 0,                               // Qty Ordered (from SPB!)
                $detail->rawMaterial->uom ?? '',                       // UoM (2 char)
                $detail->rawMaterial->last_price ?? '',                // Unit Cost (Decimal)
                $detail->rawMaterial->is_taxable ? 'Yes' : 'No',      // Taxable (Yes/No)
                $detail->rawMaterial->tax_class ?? '',                 // Tax Class (3 char)
                $detail->rawMaterial->tax_included ? 'Yes' : 'No',    // Tax In (Yes/No)
                $detail->notes ?? ''                                   // Keterangan (100 char)
            ];

            $csvContent[] = $row;
        }

        // Write CSV with pipe delimiter
        $handle = fopen(storage_path("app/{$filepath}"), 'w');
        foreach ($csvContent as $row) {
            fputcsv($handle, $row, '|');
        }
        fclose($handle);

        return $filepath;
    }

    /**
     * Generate Purchase Request CSV
     * Format: Similar to PO but with approval workflow
     */
    private function generatePRCsv(ItemRequest $spb, $items)
    {
        $timestamp = Carbon::now()->format('YmdHis');
        $filename = "PR_{$spb->spb_number}_{$timestamp}.csv";
        $filepath = "exports/csv/{$filename}";

        // Ensure directory exists
        Storage::makeDirectory('exports/csv');

        $csvContent = [];
        $lineNumber = 1;

        foreach ($items as $detail) {
            // PR format similar to PO but may have additional approval fields
            $row = [
                $this->generatePRNumber($spb),                          // PR Number (8 char)
                auth()->user()->name ?? 'System',                      // Requester
                $spb->request_date->format('n/j/Y'),                   // Request Date
                $spb->delivery_date ? $spb->delivery_date->format('n/j/Y') : '', // Required Date
                $this->formatRemarks($spb),                            // Remarks (40 char)
                $spb->store->site_code ?? '0A111',                     // Site (8 char)
                $lineNumber++,                                          // Line (Integer)
                $detail->rawMaterial->code ?? '',                      // Item Number
                $detail->rawMaterial->name ?? '',                      // Item Name
                $detail->final_qty ?? 0,                               // Qty Requested (from SPB!)
                $detail->rawMaterial->uom ?? '',                       // UoM
                $detail->notes ?? ''                                   // Keterangan
            ];

            $csvContent[] = $row;
        }

        // Write CSV with pipe delimiter
        $handle = fopen(storage_path("app/{$filepath}"), 'w');
        foreach ($csvContent as $row) {
            fputcsv($handle, $row, '|');
        }
        fclose($handle);

        return $filepath;
    }

    /**
     * Generate DO Number
     * Format: DM + Store Code + Date + Sequence
     */
    private function generateDONumber(ItemRequest $spb)
    {
        $storeCode = substr($spb->store->code ?? 'XXX', 0, 3);
        $date = $spb->request_date->format('dm');
        $sequence = str_pad($spb->id % 100, 2, '0', STR_PAD_LEFT);
        
        return 'DM' . $storeCode . $date . $sequence . 'R';
    }

    /**
     * Generate PO Number
     * Format: PR + Supplier Code + Date + Sequence
     */
    private function generatePONumber(ItemRequest $spb)
    {
        $supplierCode = 'MG';
        $date = $spb->request_date->format('dm');
        $sequence = str_pad($spb->id % 100, 2, '0', STR_PAD_LEFT);
        
        return 'PR' . $supplierCode . $date . $sequence . 'YR';
    }

    /**
     * Generate PR Number
     * Format: RQ + Store Code + Date + Sequence
     */
    private function generatePRNumber(ItemRequest $spb)
    {
        $storeCode = substr($spb->store->code ?? 'XXX', 0, 2);
        $date = $spb->request_date->format('dm');
        $sequence = str_pad($spb->id % 100, 2, '0', STR_PAD_LEFT);
        
        return 'RQ' . $storeCode . $date . $sequence;
    }

    /**
     * Format remarks field
     * Format: HH:MM/PIC Name (max 40 char)
     */
    private function formatRemarks(ItemRequest $spb)
    {
        $time = $spb->request_date->format('H:i');
        $pic = auth()->user()->name ?? 'System';
        $remarks = "{$time}/{$pic}";
        
        return substr($remarks, 0, 40);
    }

    /**
     * Get list of generated CSV files for SPB
     */
    private function getGeneratedCsvFiles(ItemRequest $spb)
    {
        $files = Storage::files('exports/csv');
        
        $spbFiles = array_filter($files, function($file) use ($spb) {
            return strpos($file, $spb->spb_number) !== false;
        });

        return array_values($spbFiles);
    }

    /**
     * Download CSV file
     */
    private function downloadCsvFile($filepath)
    {
        if (!Storage::exists($filepath)) {
            throw new \Exception("File not found: {$filepath}");
        }

        return Storage::download($filepath);
    }

    /**
     * Delete CSV files for SPB
     */
    private function deleteCsvFilesForSPB(ItemRequest $spb)
    {
        $files = $this->getGeneratedCsvFiles($spb);
        
        foreach ($files as $file) {
            Storage::delete($file);
        }

        Log::info('CSV files deleted', [
            'spb_id' => $spb->id,
            'files_deleted' => count($files)
        ]);

        return count($files);
    }

    /**
     * Format file size in human readable format
     */
    private function formatFileSize($bytes)
    {
        if ($bytes >= 1048576) {
            return number_format($bytes / 1048576, 2) . ' MB';
        } elseif ($bytes >= 1024) {
            return number_format($bytes / 1024, 2) . ' KB';
        } else {
            return $bytes . ' bytes';
        }
    }

    private function getErrorCatch($e)
    {
        if (strpos($e, '#error#') !== false) {
            $e = explode('#error#', $e);
            $e = $e[1];
        }

        return $e;
    }
}
