<?php

namespace App\Jobs;

use App\Models\BtsRanking;
use App\Models\BtsRankingDetail;
use App\Models\Bts;
use Filament\Notifications\Notification;
use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Queue\SerializesModels;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Storage;
use App\Helpers\FloatPrecisionHelper;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Illuminate\Support\Facades\DB;
class ProcessBtsRankingJob implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;

    protected int $btsRankingId;
    protected string $filePath;
    protected int $userId;

    public function __construct(int $btsRankingId, string $filePath, int $userId)
    {
        $this->btsRankingId = $btsRankingId;
        $this->filePath = $filePath;
        $this->userId = $userId;
    }

    public function handle()
    {
        try {
            ini_set('memory_limit', '-1'); // -1 = vô hạn
            set_time_limit(0); // cho phép chạy không giới hạn thời gian
            
            $btsRanking = BtsRanking::find($this->btsRankingId);
            if (!$btsRanking) {
                Log::error("BtsRanking record not found: {$this->btsRankingId}");
                return;
            }

            // Cập nhật trạng thái đang xử lý
            $btsRanking->update(['status' => 'processing']);

            // Sửa path để đảm bảo đúng format - Filament lưu vào private
            $fullPath = storage_path('app/private/' . $this->filePath);
            
            // Log để debug
            Log::info("Processing file: {$this->filePath}");
            Log::info("Full path: {$fullPath}");
            Log::info("File exists: " . (file_exists($fullPath) ? 'YES' : 'NO'));
            
            // Đọc file Excel
            $spreadsheet = IOFactory::load($fullPath);
            $worksheet = $spreadsheet->getActiveSheet();
            $data = $worksheet->toArray();
            
            if (empty($data) || count($data) < 2) {
                throw new \Exception('File không có dữ liệu hoặc chỉ có header');
            }

            // Lấy header
            $headers = array_shift($data);
            $headers = array_map('trim', $headers);
            
            // Validate headers
            $requiredHeaders = [
                'STT', 'SITE_ID', 'OBJ_ID', 'MPD_ID', 'NHAN_HIEU_MPD', 'CONG_SUAT_MPD',
                'LUONG_NHIEN_LIEU_SU_DUNG (lít/ngày)', 'CONG_SUAT_DINH_MUC', 'CONG_SUAT_TOI_DA',
                'CONSUMPTION_RATE_FULLLOAD', 'LOAI_MAY_PHAT_DIEN', 'NHIEN_LIEU', 'THOI_GIAN_SU_DUNG (phút)'
            ];
            
            $missingHeaders = array_diff($requiredHeaders, $headers);
            if (!empty($missingHeaders)) {
                throw new \Exception('Thiếu các cột bắt buộc: ' . implode(', ', $missingHeaders));
            }

            // Tạo mapping cột
            $columnMapping = array_flip($headers);
            
            // Xử lý dữ liệu theo chunk
            $chunkSize = 1000;
            $detailChunkSize = 1000; // Chunk size cho việc lưu detail
            $processedCount = 0;
            $results = [];
            $detailBatch = [];
            
            DB::transaction(function () use ($data, $chunkSize, $columnMapping, $btsRanking, &$processedCount, &$results, &$detailBatch, $detailChunkSize) {
                foreach (array_chunk($data, $chunkSize) as $chunk) {
                    $chunkResults = $this->processChunk($chunk, $columnMapping);
                    $results = array_merge($results, $chunkResults);
                    $processedCount += count($chunk);
                    
                    // Chuẩn bị dữ liệu cho detail table
                    foreach ($chunkResults as $result) {
                        $originalData = $result['original_data'];
                        $detailRow = [
                            'bts_ranking_id' => $btsRanking->id,
                            'site_id' => $originalData[$columnMapping['SITE_ID']] ?? null,
                            'obj_id' => $originalData[$columnMapping['OBJ_ID']] ?? null,
                            'mpd_id' => $originalData[$columnMapping['MPD_ID']] ?? null,
                            'mpd_brand' => $originalData[$columnMapping['NHAN_HIEU_MPD']] ?? null,
                            'mpd_capacity' => $this->getNumericValue($originalData, $columnMapping, 'CONG_SUAT_MPD'),
                            'fuel_used' => $this->getNumericValue($originalData, $columnMapping, 'LUONG_NHIEN_LIEU_SU_DUNG (lít/ngày)'),
                            'rated_capacity' => $this->getNumericValue($originalData, $columnMapping, 'CONG_SUAT_DINH_MUC'),
                            'max_capacity' => $this->getNumericValue($originalData, $columnMapping, 'CONG_SUAT_TOI_DA'),
                            'consumption_rate_fullload' => $this->getNumericValue($originalData, $columnMapping, 'CONSUMPTION_RATE_FULLLOAD'),
                            'mpd_type' => $originalData[$columnMapping['LOAI_MAY_PHAT_DIEN']] ?? null,
                            'fuel_type' => $originalData[$columnMapping['NHIEN_LIEU']] ?? null,
                            'usage_time' => $this->getNumericValue($originalData, $columnMapping, 'THOI_GIAN_SU_DUNG (phút)'),
                            'consumption_rate' => $result['consumption_rate'],
                            'usage_time_avg_year' => $result['thoi_gian_su_dung_tbnam'],
                            'service_load_score' => $result['serving_load_score'],
                            'mpd_class' => $result['mpd_class'],
                        ];
                        
                        $detailBatch[] = $detailRow;
                        
                        // Lưu detail theo chunk
                        if (count($detailBatch) >= $detailChunkSize) {
                            BtsRankingDetail::insert($detailBatch);
                            Log::info("Inserted {$detailChunkSize} detail records for BtsRanking {$this->btsRankingId}");
                            $detailBatch = [];
                        }
                    }
                    
                    Log::info("Processed {$processedCount} rows for BTS ranking {$this->btsRankingId}");
                }
                
                // Lưu detail batch cuối cùng
                if (!empty($detailBatch)) {
                    BtsRankingDetail::insert($detailBatch);
                    Log::info("Inserted " . count($detailBatch) . " final detail records for BtsRanking {$this->btsRankingId}");            
                }
            });

            // Cập nhật dữ liệu trực tiếp vào file gốc
            $this->updateOriginalFile($spreadsheet, $results, $headers, $columnMapping);
            
            // Cập nhật trạng thái hoàn thành
            $btsRanking->update([
                'status' => 'completed',
                'processed_at' => now(),
                'processed_count' => $processedCount
            ]);

            Notification::make()
                ->title('Xử lý file BTS ranking MPĐ hoàn tất')
                ->body('Bạn có thể tải xuống tệp của mình tại đây.')
                ->success()
                ->actions([
                    \Filament\Notifications\Actions\Action::make('Tải xuống')
                    ->url(url('/bts-ranking/download/' . $this->btsRankingId))
                    ->openUrlInNewTab(),
                ])
                ->sendToDatabase(\App\Models\User::find($this->userId));
            Log::info("BTS ranking processing completed for file: {$this->filePath}");

        } catch (\Exception $e) {
            Log::error("Error processing BTS ranking file: " . $e->getMessage());
            
            $btsRanking = BtsRanking::find($this->btsRankingId);
            if ($btsRanking) {
                $btsRanking->update(['status' => 'failed']);
            }
            
            throw $e;
        }
    }

    private function processChunk(array $chunk, array $columnMapping): array
    {
        $results = [];
        
        foreach ($chunk as $row) {
            if (empty(array_filter($row))) continue; // Bỏ qua dòng trống
            
            try {
                // Lấy dữ liệu từ các cột
                $luongNhienLieu = $this->getNumericValue($row, $columnMapping, 'LUONG_NHIEN_LIEU_SU_DUNG (lít/ngày)');
                $thoiGianSuDung = $this->getNumericValue($row, $columnMapping, 'THOI_GIAN_SU_DUNG (phút)');
                $consumptionRateFullLoad = $this->getNumericValue($row, $columnMapping, 'CONSUMPTION_RATE_FULLLOAD');
                $congSuatMPD = $this->getNumericValue($row, $columnMapping, 'CONG_SUAT_MPD');
                
                // Tính toán CONSUMPTION_RATE (lít/giờ)
                $consumptionRate = 0;
                if ($thoiGianSuDung > 0) {
                    $consumptionRate = $luongNhienLieu / ($thoiGianSuDung / 60);
                }
                
                // Tính toán THOI_GIAN_SU_DUNG_TBNAM (giờ)
                $thoiGianSuDungTBNAM = ($thoiGianSuDung / 60) * 365;
                
                // Tính toán SERVING_LOAD_SCORE
                $servingLoadScore = 0;
                if ($consumptionRateFullLoad > 0) {
                    $servingLoadScore = $consumptionRate / $consumptionRateFullLoad;
                }
                
                // Phân loại theo ngưỡng
                $usageLevel = $thoiGianSuDungTBNAM >= 6 ? 'HIGH' : 'LOW';
                $loadLevel = $this->categorizeLoadLevel($servingLoadScore);
                
                // Tính toán MPD_CLASS dựa trên THOI_GIAN_SU_DUNG_TBNAM và SERVING_LOAD_SCORE
                $mpdClass = $this->calculateMpdClass($thoiGianSuDungTBNAM, $servingLoadScore);
                
                $results[] = [
                    'original_data' => $row,
                    'consumption_rate' => $consumptionRate,
                    'thoi_gian_su_dung_tbnam' => $thoiGianSuDungTBNAM,
                    'serving_load_score' => $servingLoadScore,
                    'mpd_class' => $mpdClass
                ];
                
            } catch (\Exception $e) {
                Log::warning("Error processing row: " . $e->getMessage());
                continue;
            }
        }
        
        return $results;
    }

    private function getNumericValue(array $row, array $columnMapping, string $columnName): float
    {
        $index = $columnMapping[$columnName] ?? null;
        if ($index === null) {
            throw new \Exception("Column not found: {$columnName}");
        }
        
        $value = $row[$index] ?? 0;
        return is_numeric($value) ? (float)$value : 0;
    }

    private function categorizeLoadLevel(float $servingLoadScore): string
    {
        if ($servingLoadScore <= 0.65) {
            return 'LOW';
        } elseif ($servingLoadScore <= 1) {
            return 'HIGH';
        } elseif ($servingLoadScore <= 2) {
            return 'HIGH_ABNORMAL';
        } else {
            return 'VERYHIGH_ABNORMAL';
        }
    }

    private function getCategory(string $usageLevel, string $loadLevel, float $servingLoadScore): string
    {
        $category = "usage{$usageLevel}_load{$loadLevel}";
        
        if ($servingLoadScore > 1) {
            $category .= "_abnormal";
        }
        
        return $category;
    }

    private function calculateMpdClass(?float $thoiGianSuDungTBNAM, ?float $servingLoadScore): string
    {
        // Chuẩn hóa giá trị null / NaN
        $thoiGianSuDungTBNAM = (is_nan($thoiGianSuDungTBNAM) || $thoiGianSuDungTBNAM === null) ? 0 : $thoiGianSuDungTBNAM;
        $servingLoadScore = (is_nan($servingLoadScore) || $servingLoadScore === null) ? 0 : $servingLoadScore;

        // Phân loại
        if ($thoiGianSuDungTBNAM < 6) {
            // Usage LOW
            if ($servingLoadScore <= 0.65) {
                return 'usageLOW_loadLOW';
            } elseif ($servingLoadScore <= 1) {
                return 'usageLOW_loadHIGH';
            } elseif ($servingLoadScore <= 2) {
                return 'usageLOW_loadHIGH_abnormal';
            } else {
                return 'usageLOW_loadVERYHIGH_abnormal';
            }
        } else {
            // Usage HIGH
            if ($servingLoadScore <= 0.65) {
                return 'usageHIGH_loadLOW';
            } elseif ($servingLoadScore <= 1) {
                return 'usageHIGH_loadHIGH';
            } elseif ($servingLoadScore <= 2) {
                return 'usageHIGH_loadHIGH_abnormal';
            } else {
                return 'usageHIGH_loadVERYHIGH_abnormal';
            }
        }
    }

    private function updateOriginalFile($spreadsheet, array $results, array $headers, array $columnMapping)
    {
        $worksheet = $spreadsheet->getActiveSheet();
        
        // Đếm số cột có dữ liệu thực sự (không trống)
        $totalColumns = 0;
        foreach ($headers as $column) {
            if (!empty(trim($column))) {
                $totalColumns++;
            }
        }
        
        // Thêm headers mới ngay sau các cột có dữ liệu
        $newHeaders = [
            'CONSUMPTION_RATE (lít/giờ)',
            'THOI_GIAN_SU_DUNG_TBNAM (giờ)',
            'SERVING_LOAD_SCORE',
            'MPD_CLASS'
        ];
        
        // Thêm headers mới (chỉ thêm 1 lần cho dòng đầu)
        $col = $totalColumns + 1;
        foreach ($newHeaders as $header) {
            $columnLetter = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col);
            $worksheet->setCellValue($columnLetter . '1', $header);
            $col++;
        }
        
        // Cập nhật dữ liệu cho từng dòng
        $row = 2;
        foreach ($results as $result) {
            $col = $totalColumns + 1;
            
            // Ghi dữ liệu tính toán
            $worksheet->setCellValue(\PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col) . $row, $result['consumption_rate']);
            $col++;
            $worksheet->setCellValue(\PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col) . $row, $result['thoi_gian_su_dung_tbnam']);
            $col++;
            $worksheet->setCellValue(\PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col) . $row, $result['serving_load_score']);
            $col++;
            $worksheet->setCellValue(\PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col) . $row, $result['mpd_class']);
            
            $row++;
        }
        
        // Auto size columns mới
        for ($i = $totalColumns + 1; $i <= $totalColumns + count($newHeaders); $i++) {
            $columnLetter = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($i);
            $worksheet->getColumnDimension($columnLetter)->setAutoSize(true);
        }
        
        // Lưu file đã cập nhật
        $writer = new Xlsx($spreadsheet);
        $writer->save(storage_path('app/private/' . $this->filePath));
        
        Log::info("Original file updated with calculated data: {$this->filePath}");
    }

}
