<?php

namespace App\Http\Controllers;

use App\Models\TargetSisaMalam;
use App\Models\Store;
use App\Models\RawMaterial;
use App\Traits\ExcelUploadTrait;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Validation\Rule;

class TargetSisaMalamController extends Controller
{
    use ExcelUploadTrait;

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

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

            $total = $query->count();

            // Sorting
            $columns = ['id', 'store_id', 'raw_material_id', 'target_qty', '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) {
                $result[] = [
                    $no++,
                    '<a href="' . route('target_sisa_malam.show', $row->id) . '">' . ($row->store->name ?? '') . '</a>',
                    ($row->rawMaterial->code ?? '') . ' - ' . ($row->rawMaterial->name ?? ''),
                    number_format($row->target_qty, 2),
                    $row->status,
                    $row->created_at->format('M jS Y, H:i'),
                ];
            }

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

        return view('m-target-sisa-malam.index', [
            'variables' => $this->variables()
        ]);
    }

    public function create()
    {
        $variables['judul'] = 'Add data';
        $variables['stores'] = Store::where('delete_flag', 0)->orderBy('name')->get();
        $variables['raw_materials'] = RawMaterial::where('delete_flag', 0)->orderBy('name')->get();
        
        return view('m-target-sisa-malam.form', [
            'data' => json_decode(json_encode(array('id' => 0, 'target_qty' => 0, 'status' => 'Active'))),
            'variables' => $this->variables($variables)
        ]);
    }

    public function edit(TargetSisaMalam $targetSisaMalam)
    {
        $variables['judul'] = 'Edit data';
        $variables['stores'] = Store::where('delete_flag', 0)->orderBy('name')->get();
        $variables['raw_materials'] = RawMaterial::where('delete_flag', 0)->orderBy('name')->get();
        
        return view('m-target-sisa-malam.form', [
            'data' => $targetSisaMalam,
            'variables' => $this->variables($variables)
        ]);
    }

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

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

        // Validasi
        $rules = [
            'store_id' => 'required|exists:m_stores,id',
            'raw_material_id' => [
                'required',
                'exists:m_raw_materials,id',
                Rule::unique('m_target_sisa_malam')
                    ->where('store_id', $request->store_id)
                    ->ignore($targetSisaMalam?->id),
            ],
            'target_qty' => 'required|numeric|min:0|max:999999.99',
            'status' => 'required|in:Active,Inactive',
        ];

        $messages = [
            'raw_material_id.unique' => 'This raw material already has a target for the selected store.',
        ];
        $saveData = $request->validate($rules, $messages);

        try {
            DB::beginTransaction();
            
            if ($targetSisaMalam) {
                $targetSisaMalam->update(array_merge($saveData, [
                    'updated_by' => $this->logged_user_id,
                ]));
                $notif = 'Updated successfully.';
            } else {
                $targetSisaMalam = TargetSisaMalam::create(array_merge($saveData, [
                    'created_by' => $this->logged_user_id,
                ]));
                $notif = 'Successfully created.';
            }

            DB::commit();
            return response()->json(['success', $notif]);
        } catch (\Exception $e) {
            DB::rollback();

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

    public function show(TargetSisaMalam $targetSisaMalam)
    {
        $targetSisaMalam->load(['creator', 'modificator', 'store', 'rawMaterial']);
        $variables['judul'] = 'View data ID#' . $targetSisaMalam->id;
        return view('m-target-sisa-malam.show', [
            'data' => $targetSisaMalam,
            'variables' => $this->variables($variables)
        ]);
    }

    public function destroy(TargetSisaMalam $targetSisaMalam)
    {
        if ($targetSisaMalam->delete_flag == 0) {
            $targetSisaMalam->update([
                'delete_flag' => 1,
                'status' => 'Inactive',
                'updated_by' => $this->logged_user_id
            ]);

            return redirect()->route('target_sisa_malam.index')
                ->with('success', 'Successfully inactive');
        } else {
            $targetSisaMalam->update([
                'delete_flag' => 0,
                'status' => 'Active',
                'updated_by' => $this->logged_user_id
            ]);

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

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

        return $e;
    }

    /**
     * Upload Excel file for Target Sisa Malam
     */
    public function uploadExcel(Request $request)
    {
        $request->validate([
            'excel_file' => 'required|file|mimes:xlsx,xls|max:10240'
        ]);

        $file = $request->file('excel_file');
        
        try {
            // Store uploaded file
            $uploadedFilePath = $this->storeUploadedExcel($file, 'target_sisa_malam');
            
            $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($file->getPathname());
            $worksheet = $spreadsheet->getActiveSheet();
            $rows = $worksheet->toArray();
            $rows = $this->trimExcelData($rows); // Trim all data to prevent whitespace issues
            
            if (empty($rows)) {
                return response()->json([
                    'success' => false,
                    'message' => 'File Excel kosong',
                    'errors' => []
                ]);
            }

            // Get header row
            $headers = array_map('trim', $rows[0]);
            
            // Validate required columns
            $requiredColumns = ['Store Code', 'Raw Material Code', 'Target Qty', 'Status'];
            $missingColumns = array_diff($requiredColumns, $headers);
            if (!empty($missingColumns)) {
                return response()->json([
                    'success' => false,
                    'message' => 'Kolom yang diperlukan tidak ditemukan: ' . implode(', ', $missingColumns),
                    'errors' => []
                ]);
            }

            // Process data rows
            $inserted = 0;
            $updated = 0;
            $errors = [];
            $totalRows = count($rows) - 1; // Exclude header
            
            for ($i = 1; $i < count($rows); $i++) {
                $row = $rows[$i];
                
                // Skip empty rows
                if (empty(array_filter($row))) {
                    continue;
                }
                
                DB::beginTransaction();
                try {
                    // Get column indices
                    $storeCodeIdx = array_search('Store Code', $headers);
                    $rmCodeIdx = array_search('Raw Material Code', $headers);
                    $targetQtyIdx = array_search('Target Qty', $headers);
                    $statusIdx = array_search('Status', $headers);
                    
                    $storeCode = trim($row[$storeCodeIdx] ?? '');
                    $rmCode = trim($row[$rmCodeIdx] ?? '');
                    $targetQty = $row[$targetQtyIdx] ?? 0;
                    $status = trim($row[$statusIdx] ?? 'Active');
                    
                    // Validate store
                    $store = Store::where('code', $storeCode)
                        ->where('delete_flag', 0)
                        ->first();
                    
                    if (!$store) {
                        throw new \Exception("Store dengan kode '{$storeCode}' tidak ditemukan");
                    }
                    
                    // Validate raw material
                    $rawMaterial = RawMaterial::where('code', $rmCode)
                        ->where('delete_flag', 0)
                        ->first();
                    
                    if (!$rawMaterial) {
                        throw new \Exception("Raw Material dengan kode '{$rmCode}' tidak ditemukan");
                    }
                    
                    // Validate target_qty
                    if (!is_numeric($targetQty) || $targetQty < 0) {
                        throw new \Exception("Target Qty harus berupa angka positif");
                    }
                    
                    // Validate status
                    if (!in_array($status, ['Active', 'Inactive'])) {
                        throw new \Exception("Status harus 'Active' atau 'Inactive'");
                    }
                    
                    // Prepare data
                    $data = [
                        'store_id' => $store->id,
                        'raw_material_id' => $rawMaterial->id,
                        'target_qty' => floatval($targetQty),
                        'status' => $status,
                        'updated_by' => auth()->id()
                    ];
                    
                    // Check if record exists (by store_id + raw_material_id combination)
                    $existing = TargetSisaMalam::where('store_id', $store->id)
                        ->where('raw_material_id', $rawMaterial->id)
                        ->where('delete_flag', 0)
                        ->first();
                    
                    if ($existing) {
                        $existing->update($data);
                        $updated++;
                    } else {
                        $data['created_by'] = auth()->id();
                        $data['delete_flag'] = 0;
                        TargetSisaMalam::create($data);
                        $inserted++;
                    }
                    
                    DB::commit();
                    
                } catch (\Exception $e) {
                    DB::rollback();
                    $errors[] = [
                        'row' => $i + 1,
                        'error' => $e->getMessage()
                    ];
                    \Illuminate\Support\Facades\Log::error("Excel upload error at row " . ($i + 1), [
                        'error' => $e->getMessage()
                    ]);
                }
            }
            
            // Log upload
            $errorCount = count($errors);
            \App\Models\ExcelUploadLog::create([
                'upload_type' => 'target_sisa_malam',
                '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(),
            ]);
            
            $totalErrors = count($errors);
            $message = "Upload selesai: {$inserted} data baru, {$updated} data diupdate";
            if ($totalErrors > 0) {
                $message .= ", {$totalErrors} error";
            }
            
            return response()->json([
                'success' => $totalErrors == 0 || ($inserted + $updated) > 0,
                'message' => $message,
                'inserted' => $inserted,
                'updated' => $updated,
                'errors' => $errors,
                'uploaded_file' => $uploadedFilePath,
            ]);
            
        } catch (\Exception $e) {
            \Illuminate\Support\Facades\Log::error('Excel upload failed', [
                'error' => $e->getMessage()
            ]);
            
            return response()->json([
                'success' => false,
                'message' => 'Gagal memproses file Excel: ' . $e->getMessage(),
                'errors' => []
            ]);
        }
    }

    /**
     * Download Excel template for Target Sisa Malam
     */
    public function downloadTemplate()
    {
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // Set header
        $sheet->setCellValue('A1', 'Store Code');
        $sheet->setCellValue('B1', 'Raw Material Code');
        $sheet->setCellValue('C1', 'Target Qty');
        $sheet->setCellValue('D1', 'Status');

        // 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:D1')->applyFromArray($headerStyle);

        // Set column width
        foreach (range('A', 'D') as $col) {
            $sheet->getColumnDimension($col)->setAutoSize(true);
        }

        // Add sample data
        $sheet->setCellValue('A2', 'ST001');
        $sheet->setCellValue('B2', 'RM001');
        $sheet->setCellValue('C2', '10.00');
        $sheet->setCellValue('D2', 'Active');

        $sheet->setCellValue('A3', 'ST002');
        $sheet->setCellValue('B3', 'RM002');
        $sheet->setCellValue('C3', '15.50');
        $sheet->setCellValue('D3', 'Active');

        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $filename = 'template_target_sisa_malam_' . 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;
    }

    /**
     * Override unique field for Target Sisa Malam
     * TSM uses combination of store_id and raw_material_id as unique
     */
    protected function getUniqueField()
    {
        return 'id'; // Will be handled in beforeSave callback
    }
}
