<?php

namespace App\Http\Controllers;

use App\Models\ItemConversion;
use App\Models\RawMaterial;
use App\Services\CacheService;
use App\Traits\ExcelUploadTrait;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Validation\Rule;

class ItemConversionsController extends Controller
{
    use ExcelUploadTrait;
    public function index(Request $request)
    {
        if ($request->ajax()) {
            $query = ItemConversion::select('m_item_conversions.*')
                ->with(['rawMaterial'])
                ->where('delete_flag', 0);

            // Searching
            if ($request->has('search') && $request->search['value'] != '') {
                $search = $request->search['value'];
                $query->where(function ($q) use ($search) {
                    $q->whereHas('rawMaterial', function ($subQ) use ($search) {
                        $subQ->where('code', 'ilike', "%{$search}%")
                             ->orWhere('name', 'ilike', "%{$search}%");
                    })
                    ->orWhere('uom_utuh', 'ilike', "%{$search}%")
                    ->orWhere('uom_pakai', 'ilike', "%{$search}%");
                });
            }

            $total = $query->count();

            // Sorting
            $columns = ['id', 'raw_material_id', 'uom_utuh', 'uom_pakai', 'conversion_factor', 'is_active'];
            $orderColumn = $columns[$request->order[0]['column']] ?? 'id';
            $orderDir = $request->order[0]['dir'] ?? 'asc';

            $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) {
                $activeBadge = $row->is_active ? '<span class="badge badge-success">Active</span>' : '<span class="badge badge-secondary">Inactive</span>';
                
                $result[] = [
                    $no++,
                    '<a href="' . route('item_conversions.show', $row->id) . '">' . ($row->rawMaterial ? $row->rawMaterial->code : '-') . '</a>',
                    $row->rawMaterial ? $row->rawMaterial->name : '-',
                    $row->uom_utuh,
                    $row->uom_pakai,
                    number_format($row->conversion_factor, 4),
                    $row->conversion_display,
                    $activeBadge,
                ];
            }

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

        // Cache view data
        $variables['item_conversions'] = CacheService::getItemConversions();
        $variables['raw_materials'] = CacheService::getRawMaterials();

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

    public function create()
    {
        $variables['judul'] = 'Add Item Conversion';
        $variables['rawMaterials'] = RawMaterial::where('delete_flag', 0)->orderBy('code')->get();
        
        return view('m-item-conversions.form', [
            'data' => json_decode(json_encode(array('id' => 0))),
            'variables' => $this->variables($variables)
        ]);
    }

    public function edit(ItemConversion $itemConversion)
    {
        $variables['judul'] = 'Edit Item Conversion';
        $variables['rawMaterials'] = RawMaterial::where('delete_flag', 0)->orderBy('code')->get();
        
        return view('m-item-conversions.form', [
            'data' => $itemConversion,
            'variables' => $this->variables($variables)
        ]);
    }

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

        $rules = [
            'raw_material_id' => 'required|exists:m_raw_materials,id',
            'uom_utuh' => 'required|string|max:2|uppercase',
            'uom_pakai' => 'required|string|max:2|uppercase',
            'conversion_factor' => 'required|numeric|min:0.0001',
            'notes' => 'nullable|string|max:500',
            'is_active' => 'boolean',
        ];

        // Unique validation for raw material (only one active conversion per raw material)
        if (!$itemConversion) {
            $rules['raw_material_id'] = [
                'required',
                'exists:m_raw_materials,id',
                Rule::unique('m_item_conversions', 'raw_material_id')
                    ->where('delete_flag', 0)
                    ->where('is_active', true)
            ];
        } else {
            $rules['raw_material_id'] = [
                'required',
                'exists:m_raw_materials,id',
                Rule::unique('m_item_conversions', 'raw_material_id')
                    ->where('delete_flag', 0)
                    ->where('is_active', true)
                    ->ignore($itemConversion->id)
            ];
        }

        $messages = [
            'raw_material_id.unique' => 'This raw material already has an active conversion. Please deactivate the existing one first.',
        ];
        
        $saveData = $request->validate($rules, $messages);

        // Auto-uppercase UOM fields and limit to 2 chars
        $saveData['uom_utuh'] = strtoupper(substr($saveData['uom_utuh'], 0, 2));
        $saveData['uom_pakai'] = strtoupper(substr($saveData['uom_pakai'], 0, 2));

        // Set default values
        $saveData['is_active'] = $request->has('is_active');

        try {
            if ($itemConversion) {
                $itemConversion->update(array_merge($saveData, [
                    'updated_by' => $this->logged_user_id,
                ]));
            } else {
                $itemConversion = ItemConversion::create(array_merge($saveData, [
                    'created_by' => $this->logged_user_id,
                ]));
            }
            
            // Clear cache
            CacheService::clearItemConversionCache($itemConversion->raw_material_id);
            CacheService::clearRawMaterialCache($itemConversion->raw_material_id);
            
            $notif = $itemConversion ? 'Updated successfully.' : 'Successfully created.';
            return response()->json(['success', $notif]);
            
        } catch (\Exception $e) {
            $notif = $this->getErrorCatch($e->getMessage());
            return response()->json(['warning', $notif]);
        }
    }

    public function show(ItemConversion $itemConversion)
    {
        $itemConversion->load(['rawMaterial', 'creator', 'modificator']);
        $variables['judul'] = 'View Item Conversion: ' . ($itemConversion->rawMaterial ? $itemConversion->rawMaterial->code : 'N/A');
        return view('m-item-conversions.show', [
            'data' => $itemConversion,
            'variables' => $this->variables($variables)
        ]);
    }

    public function destroy(ItemConversion $itemConversion)
    {
        $itemConversion->update([
            'delete_flag' => 1,
            'updated_by' => $this->logged_user_id
        ]);

        // Clear cache
        CacheService::clearItemConversionCache($itemConversion->raw_material_id);
        CacheService::clearRawMaterialCache($itemConversion->raw_material_id);

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

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

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

        return $e;
    }

    /**
     * Upload Excel untuk bulk insert/update item conversions
     */
    public function uploadExcel(Request $request)
    {
        $request->validate([
            'excel_file' => 'required|mimes:xlsx,xls|max:10240',
        ]);

        try {
            $file = $request->file('excel_file');
            
            // Store uploaded file
            $uploadedFilePath = $this->storeUploadedExcel($file, 'item_conversions');
            
            $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($file->getRealPath());
            $sheet = $spreadsheet->getActiveSheet();
            $rows = $sheet->toArray();
            $rows = $this->trimExcelData($rows); // Trim all data to prevent whitespace issues

            // Remove header
            array_shift($rows);

            $inserted = 0;
            $updated = 0;
            $errors = [];
            $totalRows = count($rows);

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

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

                try {
                    // Validate required fields
                    if (empty($row[0]) || empty($row[1]) || empty($row[2]) || empty($row[3])) {
                        $errors[] = ['row' => $rowNumber, 'error' => 'Raw Material Code, UOM Utuh, UOM Pakai, dan Conversion Factor wajib diisi'];
                        continue;
                    }

                    // Find raw material by code
                    $rm = RawMaterial::where('code', $row[0])->where('delete_flag', 0)->first();
                    if (!$rm) {
                        $errors[] = ['row' => $rowNumber, 'error' => "Raw Material dengan code {$row[0]} tidak ditemukan"];
                        continue;
                    }

                    // Convert Yes/No to boolean
                    $isActive = strtolower($row[4] ?? 'yes') === 'yes';

                    // Uppercase UOM and limit to 2 characters
                    $uomUtuh = strtoupper(substr($row[1], 0, 2));
                    $uomPakai = strtoupper(substr($row[2], 0, 2));

                    $data = [
                        'raw_material_id' => $rm->id,
                        'uom_utuh' => $uomUtuh,
                        'uom_pakai' => $uomPakai,
                        'conversion_factor' => $row[3],
                        'is_active' => $isActive,
                        'notes' => $row[5] ?? null,
                        'delete_flag' => 0,
                        'updated_by' => auth()->id(),
                    ];

                    // Check if exists (by raw_material_id)
                    $existing = ItemConversion::where('raw_material_id', $rm->id)->first();

                    if ($existing) {
                        $existing->update($data);
                        $updated++;
                    } else {
                        $data['created_by'] = auth()->id();
                        ItemConversion::create($data);
                        $inserted++;
                    }

                    // Clear cache
                    CacheService::clearItemConversionsCache();
                } catch (\Exception $e) {
                    $errors[] = ['row' => $rowNumber, 'error' => $e->getMessage()];
                }
            }

            // Log upload
            $errorCount = count($errors);
            \App\Models\ExcelUploadLog::create([
                'upload_type' => 'item_conversions',
                'uploaded_file' => $uploadedFilePath,
                'original_filename' => $file->getClientOriginalName(),
                'total_rows' => $totalRows,
                'success_rows' => $inserted + $updated,
                'error_rows' => $errorCount,
                'error_details' => $errorCount > 0 ? json_encode($errors) : null,
                'status' => $errorCount > 0 ? ($inserted + $updated > 0 ? 'Partial' : 'Failed') : 'Success',
                'uploaded_by' => auth()->id(),
            ]);

            return response()->json([
                'success' => true,
                'message' => "Upload berhasil! Ditambahkan: {$inserted}, Diperbarui: {$updated}",
                'inserted' => $inserted,
                'updated' => $updated,
                'errors' => $errors,
                'uploaded_file' => $uploadedFilePath,
            ]);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Upload gagal: ' . $e->getMessage(),
            ], 500);
        }
    }
    
    /**
     * Download template Excel untuk item conversions
     */
    public function downloadTemplate()
    {
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // Set header
        $sheet->setCellValue('A1', 'Raw Material Code');
        $sheet->setCellValue('B1', 'UOM Utuh');
        $sheet->setCellValue('C1', 'UOM Pakai');
        $sheet->setCellValue('D1', 'Conversion Factor');
        $sheet->setCellValue('E1', 'Is Active');
        $sheet->setCellValue('F1', 'Notes');

        // Style header
        $headerStyle = [
            'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
            'fill' => ['fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID, 'startColor' => ['rgb' => 'F39C12']],
            'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER],
        ];
        $sheet->getStyle('A1:F1')->applyFromArray($headerStyle);

        // Set column width
        $sheet->getColumnDimension('A')->setWidth(20);
        $sheet->getColumnDimension('B')->setWidth(12);
        $sheet->getColumnDimension('C')->setWidth(12);
        $sheet->getColumnDimension('D')->setWidth(18);
        $sheet->getColumnDimension('E')->setWidth(12);
        $sheet->getColumnDimension('F')->setWidth(30);

        // Add sample data
        $sheet->setCellValue('A2', 'RM001');
        $sheet->setCellValue('B2', 'DS');
        $sheet->setCellValue('C2', 'PC');
        $sheet->setCellValue('D2', '12.0000');
        $sheet->setCellValue('E2', 'Yes');
        $sheet->setCellValue('F2', '1 Dus = 12 Pack');

        $sheet->setCellValue('A3', 'RM002');
        $sheet->setCellValue('B3', 'LT');
        $sheet->setCellValue('C3', 'ML');
        $sheet->setCellValue('D3', '1000.0000');
        $sheet->setCellValue('E3', 'Yes');
        $sheet->setCellValue('F3', '1 Liter = 1000 Mililiter');

        // Add notes
        $sheet->setCellValue('A5', 'Catatan:');
        $sheet->setCellValue('A6', '- Raw Material Code: Kode raw material yang sudah terdaftar');
        $sheet->setCellValue('A7', '- UOM Utuh: Satuan utuh (max 2 karakter, contoh: DS, LT, KG)');
        $sheet->setCellValue('A8', '- UOM Pakai: Satuan pakai (max 2 karakter, contoh: PC, ML, GR)');
        $sheet->setCellValue('A9', '- Conversion Factor: Faktor konversi (angka desimal)');
        $sheet->setCellValue('A10', '- Is Active: Yes atau No');
        $sheet->setCellValue('A11', '- Notes: Catatan tambahan (opsional)');

        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $filename = 'template_item_conversions_' . date('YmdHis') . '.xlsx';

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $filename . '"');
        header('Cache-Control: max-age=0');

        $writer->save('php://output');
        exit;
    }
    
    protected function getUniqueField()
    {
        return 'raw_material_id';
    }
}
