<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Maatwebsite\Excel\Facades\Excel;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class ProcessMpdExcel extends Command
{
    protected $signature = 'mpd:process-excel {input=storage/app/max-power/MPD_rankingwithdomain.xls}';
    protected $description = 'Đọc file MPD_rankingwithdomain.xls, tính THOI_GIAN_SU_DUNG và xuất ra file .xlsx';

    public function handle()
    {
        $inputFile = $this->argument('input');
        $outputFile = storage_path('app/max-power/MPD_rankingwithdomain_calculated.xlsx');

        $this->info("📂 Đọc file: {$inputFile}");
        $spreadsheet = IOFactory::load($inputFile);
        $sheet = $spreadsheet->getActiveSheet();
        $rows = $sheet->toArray();

        // Header row
        $header = $rows[0];
        $header[] = 'THOI_GIAN_SU_DUNG (phút)';
        $data = [$header];

        // Index mapping để dễ đọc
        $idxLuong = array_search('LUONG_NHIEN_LIEU_SU_DUNG', $header);
        $idxRate  = array_search('CONSUMPTION_RATE', $header);

        // Nếu không tìm thấy cột
        if ($idxLuong === false || $idxRate === false) {
            $this->error('⚠️ Không tìm thấy cột LUONG_NHIEN_LIEU_SU_DUNG hoặc CONSUMPTION_RATE');
            return Command::FAILURE;
        }

        // Duyệt từng dòng (bỏ header)
        foreach (array_slice($rows, 1) as $row) {
            $luong = (float)($row[$idxLuong] ?? 0);
            $rate  = (float)($row[$idxRate] ?? 0);

            $thoiGian = ($rate > 0) ? 60 * ($luong / $rate) : 0;
            $row[] = $thoiGian;

            $data[] = $row;
        }

        // Ghi ra file mới
        $newSheet = new Spreadsheet();
        $newSheet->getActiveSheet()->fromArray($data);
        $writer = new Xlsx($newSheet);
        $writer->save($outputFile);

        $this->info("✅ Đã tạo file: {$outputFile}");

        return Command::SUCCESS;
    }
}
