<?php

namespace App\Http\Controllers;

use App\Models\Forecast;
use App\Models\ForecastDetail;
use App\Models\Store;
use App\Models\RawMaterial;
use App\Models\UserStore;
use App\Traits\ExcelUploadTrait;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Validation\Rule;
use Maatwebsite\Excel\Facades\Excel;
use Carbon\Carbon;

class ForecastsController extends Controller
{
    use ExcelUploadTrait;

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

            // Filter by user's accessible stores
            $userStores = UserStore::where('user_id', $this->logged_user_id)
                ->pluck('store_id')
                ->toArray();

            if (!empty($userStores)) {
                $query->whereIn('store_id', $userStores);
            } else {
                // If user has no store access, return empty result
                $query->whereRaw('1 = 0');
            }

            // 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}%");
                    })
                    ->orWhere('forecast_number', 'ilike', "%{$search}%")
                    ->orWhere('status', 'ilike', "%{$search}%")
                    ->orWhere('year', 'like', "%{$search}%")
                    ->orWhere('month', 'like', "%{$search}%");
                });
            }

            $total = $query->count();

            // Sorting
            $columns = ['id', 'store_id', 'forecast_number', 'year', 'month', 'upload_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) {
                $result[] = [
                    $no++,
                    '<a href="' . route('forecasts.show', $row->id) . '">' . $row->forecast_number . '</a>',
                    $row->store->name ?? '',
                    $row->year . '-' . str_pad($row->month, 2, '0', STR_PAD_LEFT),
                    $row->upload_date->format('d M Y'),
                    '<span class="badge badge-' . $row->status_badge_class . '">' . $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,
            ]);
        }

        // Get user's accessible stores for display
        $userStores = UserStore::where('user_id', $this->logged_user_id)
            ->with('store')
            ->get();

        return view('t-forecasts.index', [
            'variables' => $this->variables(),
            'user_stores' => $userStores
        ]);
    }

    public function create()
    {
        $variables['judul'] = 'Upload Forecast';
        
        return view('t-forecasts.upload', [
            'variables' => $this->variables($variables)
        ]);
    }

    public function show(Forecast $forecast)
    {
        $forecast->load(['creator', 'modificator', 'store', 'activeDetails.rawMaterial']);
        
        // Prepare data for horizontal display
        $forecastData = $this->prepareHorizontalForecastData($forecast);
        
        $variables['judul'] = 'View Forecast #' . $forecast->forecast_number;
        
        return view('t-forecasts.show', [
            'forecast' => $forecast,
            'forecastData' => $forecastData,
            'variables' => $this->variables($variables)
        ]);
    }

    /**
     * Prepare forecast data for horizontal display (dates as columns)
     */
    private function prepareHorizontalForecastData($forecast)
    {
        $details = $forecast->activeDetails;
        
        // Handle empty details
        if ($details->isEmpty()) {
            return [
                'dates' => collect(),
                'materials' => []
            ];
        }
        
        // Get all unique dates and sort them
        $dates = $details->pluck('forecast_date')->unique()->sort()->values();
        
        // Group details by raw material
        $materialGroups = $details->groupBy('raw_material_id');
        
        $horizontalData = [];
        
        foreach ($materialGroups as $materialId => $materialDetails) {
            $firstDetail = $materialDetails->first();
            $rawMaterial = $firstDetail->rawMaterial;
            
            $row = [
                'raw_material_code' => $rawMaterial->code,
                'raw_material_name' => $rawMaterial->name,
                'uom' => $rawMaterial->uom ?? 'PCS',
                'dates' => []
            ];
            
            // Create date-quantity mapping
            $dateQtyMap = $materialDetails->keyBy(function($detail) {
                return $detail->forecast_date->format('Y-m-d');
            });
            
            // Fill quantities for each date
            foreach ($dates as $date) {
                $dateKey = $date->format('Y-m-d');
                $row['dates'][$dateKey] = [
                    'date' => $date,
                    'qty' => isset($dateQtyMap[$dateKey]) ? $dateQtyMap[$dateKey]->forecast_qty : 0
                ];
            }
            
            $horizontalData[] = $row;
        }
        
        return [
            'dates' => $dates,
            'materials' => $horizontalData
        ];
    }

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

    /**
     * Parse date from various formats commonly found in Excel
     */
    private function parseFlexibleDate($dateString)
    {
        if (empty($dateString)) {
            return null;
        }

        $dateString = trim($dateString);

        // Try different date formats
        $formats = [
            'Y-m-d',        // 2026-01-01
            'm/d/Y',        // 1/1/2026 or 01/01/2026
            'd-m-Y',        // 01-01-2026
            'd/m/Y',        // 01/01/2026
            'Y/m/d',        // 2026/01/01
            'm-d-Y',        // 01-01-2026
        ];

        foreach ($formats as $format) {
            try {
                $date = Carbon::createFromFormat($format, $dateString);
                if ($date && $date->format($format) === $dateString) {
                    // Validate the result is reasonable (between 2020-2030)
                    if ($date->year >= 2020 && $date->year <= 2030) {
                        return $date;
                    }
                }
            } catch (\Exception $e) {
                continue;
            }
        }

        // Try to parse as Excel serial number (days since 1900-01-01)
        if (is_numeric($dateString)) {
            try {
                $serialNumber = intval($dateString);
                
                // Excel serial dates typically range from 1 (1900-01-01) to ~50000+ (2030+)
                if ($serialNumber >= 43831 && $serialNumber <= 47482) { // Roughly 2020-2030 range
                    // Excel serial date starts from 1900-01-01, but Excel incorrectly treats 1900 as a leap year
                    $excelEpoch = Carbon::create(1900, 1, 1);
                    $date = $excelEpoch->addDays($serialNumber - 2); // Adjust for Excel's leap year bug
                    
                    // Double-check the year is in valid range
                    if ($date->year >= 2020 && $date->year <= 2030) {
                        return $date;
                    }
                }
            } catch (\Exception $e) {
                // Continue to next attempt
            }
        }

        // Try Carbon's flexible parsing as last resort
        try {
            $date = Carbon::parse($dateString);
            // Validate the result is reasonable (between 2020-2030)
            if ($date->year >= 2020 && $date->year <= 2030) {
                return $date;
            }
        } catch (\Exception $e) {
            // Failed to parse
        }

        return null;
    }

    public function uploadExcel(Request $request)
    {
        $request->validate([
            'excel_file' => 'required|file|mimes:xlsx,xls,csv|max:10240',
            'year' => 'required|integer|min:2020|max:2030',
            'month' => 'required|integer|min:1|max:12',
        ]);

        // Validate backdate - tidak boleh upload untuk bulan sebelumnya
        $currentYear = now()->year;
        $currentMonth = now()->month;
        $uploadYear = $request->year;
        $uploadMonth = $request->month;
        
        if ($uploadYear < $currentYear || ($uploadYear == $currentYear && $uploadMonth < $currentMonth)) {
            return response()->json([
                'success' => false,
                'message' => 'Tidak diperbolehkan mengupload forecast untuk bulan sebelumnya. Forecast hanya boleh diupload untuk bulan berjalan atau bulan setelahnya.'
            ]);
        }

        try {
            $file = $request->file('excel_file');
            $year = $request->year;
            $month = $request->month;

            // Store uploaded file
            $uploadedFilePath = $this->storeUploadedExcel($file, 'forecast');

            // Read Excel file
            $data = Excel::toArray([], $file);
            
            if (empty($data) || empty($data[0])) {
                throw new \Exception('File Excel kosong atau tidak valid');
            }

            $rows = $data[0];
            $rows = $this->trimExcelData($rows); // Trim all data to prevent whitespace issues
            $header = array_shift($rows); // Remove and get header row

            // First column should be store_code
            if (empty($header[0]) || strtolower($header[0]) !== 'store_code') {
                throw new \Exception('Kolom pertama harus berisi "store_code"');
            }

            // Second column should be raw_material_code
            if (empty($header[1]) || strtolower($header[1]) !== 'raw_material_code') {
                throw new \Exception('Kolom kedua harus berisi "raw_material_code"');
            }

            // Extract date columns (skip first 4 columns: store_code, raw_material_code, raw_material_name, uom)
            $dateColumns = [];
            
            for ($i = 4; $i < count($header); $i++) {
                $columnValue = trim($header[$i]);
                
                // Try to parse various date formats
                $parsedDate = $this->parseFlexibleDate($columnValue);
                if ($parsedDate) {
                    $dateColumns[$i] = $parsedDate->format('Y-m-d');
                }
            }

            if (empty($dateColumns)) {
                \Log::info('Date parsing failed in uploadExcel. Headers found:', array_slice($header, 4));
                throw new \Exception('Tidak ditemukan kolom tanggal yang valid. Format yang didukung: YYYY-MM-DD, MM/DD/YYYY, DD-MM-YYYY, atau angka serial Excel');
            }

            // Validate maximum 31 forecast dates
            if (count($dateColumns) > 31) {
                throw new \Exception('Maksimal 31 tanggal forecast yang diizinkan dalam satu upload. Ditemukan ' . count($dateColumns) . ' tanggal. Silakan kurangi jumlah kolom tanggal atau bagi data menjadi beberapa periode upload.');
            }

            // Get user's accessible stores
            $userStores = UserStore::where('user_id', $this->logged_user_id)
                ->pluck('store_id')
                ->toArray();

            if (empty($userStores)) {
                throw new \Exception('User tidak memiliki akses ke store manapun. Silakan hubungi administrator.');
            }

            // Group data by store_code
            $groupedData = $this->groupDataByStore($rows, $header);

            if (empty($groupedData)) {
                throw new \Exception('Tidak ada data valid yang ditemukan dalam file Excel');
            }

            // Validate all stores are accessible by user
            $storeCodesInFile = array_keys($groupedData);
            $stores = Store::whereIn('code', $storeCodesInFile)
                ->where('delete_flag', 0)
                ->get()
                ->keyBy('code');

            $inaccessibleStores = [];
            foreach ($storeCodesInFile as $storeCode) {
                if (!isset($stores[$storeCode])) {
                    throw new \Exception("Store dengan kode '{$storeCode}' tidak ditemukan dalam database");
                }
                
                $store = $stores[$storeCode];
                if (!in_array($store->id, $userStores)) {
                    $inaccessibleStores[] = $storeCode;
                }
            }

            if (!empty($inaccessibleStores)) {
                throw new \Exception('User tidak memiliki akses ke store berikut: ' . implode(', ', $inaccessibleStores));
            }

            // Process each store group
            $aggregatedResult = [
                'stores' => [],
                'total_success' => 0,
                'total_errors' => 0,
                'total_inserted' => 0,
                'all_errors' => []
            ];

            foreach ($groupedData as $storeCode => $storeRows) {
                $result = $this->processStoreGroup($storeCode, $storeRows, $dateColumns, $year, $month, $stores[$storeCode], $uploadedFilePath);
                
                $aggregatedResult['stores'][$storeCode] = $result;
                $aggregatedResult['total_success'] += $result['success_count'];
                $aggregatedResult['total_errors'] += $result['error_count'];
                $aggregatedResult['total_inserted'] += $result['insert_count'];
                $aggregatedResult['all_errors'] = array_merge($aggregatedResult['all_errors'], $result['errors']);
            }

            // Build response message
            $message = "Upload selesai untuk " . count($groupedData) . " store.\n";
            $message .= "Total: {$aggregatedResult['total_success']} records berhasil ({$aggregatedResult['total_inserted']} inserted), {$aggregatedResult['total_errors']} errors\n\n";
            
            foreach ($aggregatedResult['stores'] as $storeCode => $result) {
                $message .= "Store {$storeCode}: {$result['success_count']} berhasil, {$result['error_count']} errors\n";
            }

            if (!empty($aggregatedResult['all_errors'])) {
                $message .= "\nErrors:\n" . implode("\n", array_slice($aggregatedResult['all_errors'], 0, 20));
            }

            return response()->json([
                'success' => true,
                'message' => $message,
                'stores' => $aggregatedResult['stores'],
                'total_success' => $aggregatedResult['total_success'],
                'total_errors' => $aggregatedResult['total_errors'],
                'total_inserted' => $aggregatedResult['total_inserted'],
                'errors' => $aggregatedResult['all_errors']
            ]);

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

    /**
     * Group Excel rows by store_code
     */
    private function groupDataByStore($rows, $header)
    {
        $grouped = [];
        
        foreach ($rows as $rowIndex => $row) {
            if (empty($row[0])) continue; // Skip empty rows
            
            $storeCode = trim($row[0]);
            
            if (!isset($grouped[$storeCode])) {
                $grouped[$storeCode] = [];
            }
            
            $grouped[$storeCode][] = [
                'row_index' => $rowIndex,
                'data' => $row
            ];
        }
        
        return $grouped;
    }

    /**
     * Process forecast data for one store
     */
    private function processStoreGroup($storeCode, $storeRows, $dateColumns, $year, $month, $store, $uploadedFilePath = null)
    {
        $result = [
            'success_count' => 0,
            'error_count' => 0,
            'insert_count' => 0,
            'errors' => [],
            'forecast_id' => null
        ];

        // Get or create forecast header (outside transaction)
        try {
            $existingHeader = Forecast::getForPeriod($store->id, $year, $month);
            
            if ($existingHeader) {
                // Use existing header - update uploaded_file if provided
                $forecastHeader = $existingHeader;
                if ($uploadedFilePath) {
                    $forecastHeader->update([
                        'uploaded_file' => $uploadedFilePath,
                        'updated_by' => $this->logged_user_id,
                    ]);
                }
            } else {
                // Create new forecast header
                DB::beginTransaction();
                try {
                    $forecastHeader = Forecast::create([
                        'store_id' => $store->id,
                        'year' => $year,
                        'month' => $month,
                        'status' => 'Draft',
                        'uploaded_file' => $uploadedFilePath,
                        'created_by' => $this->logged_user_id,
                    ]);
                    DB::commit();
                } catch (\Exception $e) {
                    DB::rollback();
                    throw $e;
                }
            }

            $result['forecast_id'] = $forecastHeader->id;

        } catch (\Exception $e) {
            $result['errors'][] = "Store {$storeCode}: Failed to create forecast header - " . $e->getMessage();
            $result['error_count']++;
            return $result;
        }

        // Process each row with individual transaction
        foreach ($storeRows as $rowData) {
            $rowIndex = $rowData['row_index'];
            $row = $rowData['data'];
            
            // Additional safety: trim the row data again (in case trimExcelData wasn't called earlier)
            $row = array_map(function($value) {
                return is_string($value) ? trim($value) : $value;
            }, $row);

            DB::beginTransaction();
            try {
                $rawMaterialCode = $row[1]; // Column index 1 is raw_material_code (already trimmed above)

                // Find raw material by code
                $rawMaterial = RawMaterial::where('code', $rawMaterialCode)
                    ->where('delete_flag', 0)
                    ->first();

                if (!$rawMaterial) {
                    $result['errors'][] = "Store {$storeCode}, Row " . ($rowIndex + 2) . ": Raw material code '{$rawMaterialCode}' not found";
                    $result['error_count']++;
                    DB::rollback();
                    continue;
                }

                // Process each date column
                foreach ($dateColumns as $columnIndex => $dateString) {
                    $forecastQty = $row[$columnIndex] ?? 0;
                    
                    // Skip if quantity is empty or zero
                    if (empty($forecastQty) || $forecastQty == 0) {
                        continue;
                    }

                    $forecastDate = Carbon::parse($dateString);

                    // Upsert forecast detail based on composite key: store_id + raw_material_id + forecast_date
                    $this->upsertForecastDetail(
                        $forecastHeader->id,
                        $store->id,
                        $rawMaterial->id,
                        $forecastDate->format('Y-m-d'),
                        $forecastQty,
                        $result
                    );
                }

                DB::commit();

            } catch (\Exception $e) {
                DB::rollback();
                $result['errors'][] = "Store {$storeCode}, Row " . ($rowIndex + 2) . ": " . $e->getMessage();
                $result['error_count']++;
            }
        }

        return $result;
    }

    /**
     * Upsert forecast detail based on composite key
     */
    private function upsertForecastDetail($forecastHeaderId, $storeId, $rawMaterialId, $forecastDate, $forecastQty, &$result)
    {
        // Check if detail already exists
        $existingDetail = ForecastDetail::where('forecast_header_id', $forecastHeaderId)
            ->where('store_id', $storeId)
            ->where('raw_material_id', $rawMaterialId)
            ->where('forecast_date', $forecastDate)
            ->first();

        if ($existingDetail) {
            // Update existing detail
            $existingDetail->update([
                'forecast_qty' => $forecastQty,
                'delete_flag' => 0, // Re-activate if was deleted
                'updated_by' => $this->logged_user_id,
            ]);
            // Count as insert for simplicity (could add update_count if needed)
        } else {
            // Create new detail
            ForecastDetail::create([
                'forecast_header_id' => $forecastHeaderId,
                'store_id' => $storeId,
                'raw_material_id' => $rawMaterialId,
                'forecast_date' => $forecastDate,
                'forecast_qty' => $forecastQty,
                'created_by' => $this->logged_user_id,
            ]);
            $result['insert_count']++;
        }

        $result['success_count']++;
    }

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

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

    public function confirm(Request $request, $forecastId = null)
    {
        $request->validate([
            'forecast_id' => 'required|integer|exists:t_forecasts,id',
        ]);

        try {
            DB::beginTransaction();

            $forecast = Forecast::findOrFail($request->forecast_id);
            
            // Check if forecast is in Draft status
            if ($forecast->status !== 'Draft') {
                return response()->json([
                    'success' => false,
                    'message' => 'Hanya forecast dengan status Draft yang dapat dikonfirmasi'
                ]);
            }
            
            // Check if forecast is not deleted
            if ($forecast->delete_flag != 0) {
                return response()->json([
                    'success' => false,
                    'message' => 'Forecast tidak ditemukan atau sudah dihapus'
                ]);
            }

            // Update status to Confirmed
            $forecast->update([
                'status' => 'Confirmed',
                'updated_by' => $this->logged_user_id,
                'updated_at' => now()
            ]);

            DB::commit();

            return response()->json([
                'success' => true,
                'message' => "Berhasil mengkonfirmasi forecast #{$forecast->forecast_number} untuk {$forecast->store->name}"
            ]);

        } catch (\Exception $e) {
            DB::rollback();
            return response()->json([
                'success' => false,
                'message' => 'Konfirmasi gagal: ' . $e->getMessage()
            ], 500);
        }
    }

    public function downloadTemplate(Request $request)
    {
        $year = $request->get('year', date('Y'));
        $month = $request->get('month', date('n'));
        
        // Generate dates for the month
        $startDate = Carbon::create($year, $month, 1);
        $endDate = $startDate->copy()->endOfMonth();
        $dates = [];
        
        for ($date = $startDate->copy(); $date <= $endDate; $date->addDay()) {
            $dates[] = $date->format('Y-m-d');
        }
        
        // Create headers: store_code + raw_material_code + raw_material_name + uom + all dates
        $headers = ['store_code', 'raw_material_code', 'raw_material_name', 'uom'];
        $headers = array_merge($headers, $dates);
        
        // Sample data
        $sampleData = [
            array_merge(['STR001', 'RM000001', 'Sample Raw Material 1', 'KG'], array_fill(0, count($dates), '100.50')),
            array_merge(['STR001', 'RM000002', 'Sample Raw Material 2', 'LITER'], array_fill(0, count($dates), '75.25')),
            array_merge(['STR002', 'RM000001', 'Sample Raw Material 1', 'KG'], array_fill(0, count($dates), '80.00')),
        ];

        $csvContent = implode(',', $headers) . "\n";
        foreach ($sampleData as $row) {
            $csvContent .= implode(',', $row) . "\n";
        }

        return response($csvContent)
            ->header('Content-Type', 'text/csv')
            ->header('Content-Disposition', 'attachment; filename="forecast_template_' . $year . '_' . str_pad($month, 2, '0', STR_PAD_LEFT) . '.csv"');
    }

    public function exportHorizontal(Request $request)
    {
        $request->validate([
            'forecast_id' => 'required|integer|exists:t_forecasts,id',
        ]);

        try {
            $forecast = Forecast::with('store')->findOrFail($request->forecast_id);
            
            // Check if forecast is not deleted
            if ($forecast->delete_flag != 0) {
                return response()->json([
                    'success' => false,
                    'message' => 'Forecast tidak ditemukan atau sudah dihapus'
                ], 404);
            }

            $store = $forecast->store;
            $year = $forecast->year;
            $month = $forecast->month;

            // Generate dates for the month
            $startDate = Carbon::create($year, $month, 1);
            $endDate = $startDate->copy()->endOfMonth();
            $dates = [];
            
            for ($date = $startDate->copy(); $date <= $endDate; $date->addDay()) {
                $dates[] = $date->format('Y-m-d');
            }

            // Get all forecast details for the header
            $forecastDetails = ForecastDetail::where('forecast_header_id', $forecast->id)
                ->where('store_id', $forecast->store_id)
                ->where('delete_flag', 0)
                ->with('rawMaterial')
                ->get();

            // Group by raw material
            $forecastsByMaterial = [];
            foreach ($forecastDetails as $detail) {
                $materialCode = $detail->rawMaterial->code;
                $forecastDate = $detail->forecast_date->format('Y-m-d');
                
                if (!isset($forecastsByMaterial[$materialCode])) {
                    $forecastsByMaterial[$materialCode] = [
                        'raw_material_code' => $materialCode,
                        'raw_material_name' => $detail->rawMaterial->name,
                        'uom' => $detail->rawMaterial->uom ?? 'PCS',
                        'dates' => []
                    ];
                }
                
                $forecastsByMaterial[$materialCode]['dates'][$forecastDate] = $detail->effective_qty;
            }

            // Create CSV content
            $headers = ['raw_material_code', 'raw_material_name', 'uom'];
            $headers = array_merge($headers, $dates);
            
            $csvContent = implode(',', $headers) . "\n";
            
            foreach ($forecastsByMaterial as $materialData) {
                $row = [
                    $materialData['raw_material_code'],
                    '"' . $materialData['raw_material_name'] . '"',
                    $materialData['uom']
                ];
                
                // Add quantities for each date
                foreach ($dates as $date) {
                    $row[] = $materialData['dates'][$date] ?? '0';
                }
                
                $csvContent .= implode(',', $row) . "\n";
            }

            $filename = "forecast_export_{$store->name}_{$year}_" . str_pad($month, 2, '0', STR_PAD_LEFT) . ".csv";
            $filename = preg_replace('/[^A-Za-z0-9_\-\.]/', '_', $filename);

            return response($csvContent)
                ->header('Content-Type', 'text/csv')
                ->header('Content-Disposition', 'attachment; filename="' . $filename . '"');
                
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Export gagal: ' . $e->getMessage()
            ], 500);
        }
    }

    public function replaceData(Request $request, Forecast $forecast)
    {
        $request->validate([
            'excel_file' => 'required|file|mimes:xlsx,xls,csv|max:10240',
            'delete_missing' => 'boolean', // Optional parameter to delete missing records
        ]);

        // Validate backdate - tidak boleh replace untuk bulan sebelumnya
        $currentYear = now()->year;
        $currentMonth = now()->month;
        $forecastYear = $forecast->year;
        $forecastMonth = $forecast->month;
        
        if ($forecastYear < $currentYear || ($forecastYear == $currentYear && $forecastMonth < $currentMonth)) {
            return response()->json([
                'success' => false,
                'message' => 'Tidak diperbolehkan mengganti data forecast untuk bulan sebelumnya. Forecast hanya boleh diubah untuk bulan berjalan atau bulan setelahnya.'
            ]);
        }

        try {
            DB::beginTransaction();

            $file = $request->file('excel_file');
            $deleteMissing = $request->get('delete_missing', false);

            // Read Excel file
            $data = Excel::toArray([], $file);
            
            if (empty($data) || empty($data[0])) {
                throw new \Exception('File Excel kosong atau tidak valid');
            }

            $rows = $data[0];
            $rows = $this->trimExcelData($rows); // Trim all data to prevent whitespace issues
            $header = array_shift($rows); // Remove and get header row

            // First column should be raw_material_code
            if (empty($header[0]) || strtolower($header[0]) !== 'raw_material_code') {
                throw new \Exception('Kolom pertama harus berisi "raw_material_code"');
            }

            // Extract date columns (skip first 3 columns: raw_material_code, raw_material_name, uom)
            $dateColumns = [];
            
            for ($i = 3; $i < count($header); $i++) {
                $columnValue = trim($header[$i]);
                
                // Try to parse various date formats
                $parsedDate = $this->parseFlexibleDate($columnValue);
                if ($parsedDate) {
                    $dateColumns[$i] = $parsedDate->format('Y-m-d');
                }
            }

            if (empty($dateColumns)) {
                // Log the actual headers for debugging
                \Log::info('Date parsing failed in replaceData. Headers found:', array_slice($header, 3));
                throw new \Exception('Tidak ditemukan kolom tanggal yang valid. Format yang didukung: YYYY-MM-DD, MM/DD/YYYY, DD-MM-YYYY, atau angka serial Excel');
            }

            // Validate maximum 31 forecast dates
            if (count($dateColumns) > 31) {
                throw new \Exception('Maksimal 31 tanggal forecast yang diizinkan dalam satu upload. Ditemukan ' . count($dateColumns) . ' tanggal. Silakan kurangi jumlah kolom tanggal atau bagi data menjadi beberapa periode upload.');
            }

            $successCount = 0;
            $errorCount = 0;
            $errors = [];
            $insertCount = 0;
            $updateCount = 0;
            $deleteCount = 0;

            // Track processed records for deletion logic
            $processedRecords = [];

            foreach ($rows as $rowIndex => $row) {
                try {
                    if (empty($row[0])) continue; // Skip empty rows

                    $rawMaterialCode = trim($row[0]);

                    // Find raw material by code
                    $rawMaterial = RawMaterial::where('code', $rawMaterialCode)
                        ->where('delete_flag', 0)
                        ->first();

                    if (!$rawMaterial) {
                        $errors[] = "Row " . ($rowIndex + 2) . ": Raw material code '{$rawMaterialCode}' not found";
                        $errorCount++;
                        continue;
                    }

                    // Process each date column
                    foreach ($dateColumns as $columnIndex => $dateString) {
                        $forecastQty = $row[$columnIndex] ?? 0;
                        
                        // Skip if quantity is empty or zero
                        if (empty($forecastQty) || $forecastQty == 0) {
                            continue;
                        }

                        $forecastDate = Carbon::parse($dateString);
                        $recordKey = $rawMaterial->id . '_' . $forecastDate->format('Y-m-d');
                        $processedRecords[] = $recordKey;

                        // Check if forecast detail already exists
                        $existingDetail = ForecastDetail::where('forecast_header_id', $forecast->id)
                            ->where('store_id', $forecast->store_id)
                            ->where('raw_material_id', $rawMaterial->id)
                            ->where('forecast_date', $forecastDate->format('Y-m-d'))
                            ->first();

                        if ($existingDetail) {
                            // Update existing detail
                            $existingDetail->update([
                                'forecast_qty' => $forecastQty,
                                'updated_by' => $this->logged_user_id,
                            ]);
                            $updateCount++;
                        } else {
                            // Create new detail
                            ForecastDetail::create([
                                'forecast_header_id' => $forecast->id,
                                'store_id' => $forecast->store_id,
                                'raw_material_id' => $rawMaterial->id,
                                'forecast_date' => $forecastDate->format('Y-m-d'),
                                'forecast_qty' => $forecastQty,
                                'created_by' => $this->logged_user_id,
                            ]);
                            $insertCount++;
                        }

                        $successCount++;
                    }

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

            // Delete records that are not in the new upload (if requested)
            if ($deleteMissing && !empty($processedRecords)) {
                $existingDetails = ForecastDetail::where('forecast_header_id', $forecast->id)
                    ->where('delete_flag', 0)
                    ->get();

                foreach ($existingDetails as $detail) {
                    $recordKey = $detail->raw_material_id . '_' . $detail->forecast_date->format('Y-m-d');
                    if (!in_array($recordKey, $processedRecords)) {
                        $detail->update(['delete_flag' => 1]);
                        $deleteCount++;
                    }
                }
            }

            // Update header
            $forecast->update([
                'upload_date' => now()->toDateString(),
                'status' => 'Draft', // Reset to draft after replacement
                'updated_by' => $this->logged_user_id,
            ]);

            DB::commit();

            $message = "Penggantian data selesai. Berhasil: {$successCount} records ({$updateCount} diupdate, {$insertCount} ditambahkan";
            if ($deleteCount > 0) {
                $message .= ", {$deleteCount} dihapus";
            }
            $message .= "), Gagal: {$errorCount}";
            
            if (!empty($errors)) {
                $message .= "\n\nErrors:\n" . implode("\n", array_slice($errors, 0, 10));
            }

            return response()->json([
                'success' => true,
                'message' => $message,
                'success_count' => $successCount,
                'error_count' => $errorCount,
                'update_count' => $updateCount,
                'insert_count' => $insertCount,
                'delete_count' => $deleteCount,
                'errors' => $errors
            ]);

        } catch (\Exception $e) {
            DB::rollback();
            return response()->json([
                'success' => false,
                'message' => 'Penggantian data gagal: ' . $e->getMessage()
            ]);
        }
    }
}