<?php

namespace App\Jobs;

use App\ReportJob;
use App\SeverancePayment;
use Carbon\Carbon;
use Illuminate\Bus\Queueable;
use Illuminate\Bus\Batchable;
use Illuminate\Contracts\Queue\ShouldBeUnique;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Queue\SerializesModels;
use Illuminate\Support\Facades\Log;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class GenerateExcelSingleReportTwo implements ShouldQueue
{
	use Batchable, Dispatchable, InteractsWithQueue, Queueable, SerializesModels;
	
	protected $reportJob;
	protected $severancePayment;
	
	/**
	 * The number of seconds the job can run before timing out.
	 *
	 * @var int
	 */
	public $timeout = 3600;
	
	/**
	 * Indicate if the job should be marked as failed on timeout.
	 *
	 * @var bool
	 */
	public $failOnTimeout = false;
	
	/**
	 * Create a new job instance.
	 *
	 * @param ReportJob $reportJob
	 * @param SeverancePayment $severancePayment
	 */
	public function __construct(ReportJob $reportJob, SeverancePayment $severancePayment)
	{
		$this->onQueue(config('queue.connections.redis.queue'));
		$this->reportJob = $reportJob;
		$this->severancePayment = $severancePayment;
	}
	
	/**
	 * @return bool
	 */
	public function isFailOnTimeout(): bool
	{
		return $this->failOnTimeout;
	}
	
	/**
	 * Execute the job.
	 *
	 * @return void
	 */
	public function handle()
	{
		try {
			ini_set('max_execution_time', 300);
			ini_set('memory_limit', '512M');
			
			$this->reportJob->update([
				'status'     => 'processing',
				'updated_at' => Carbon::now(),
			]);
			
			$templatePath = public_path('template/2.xlsx');
			// Suppress XML warnings from PhpSpreadsheet when loading template
			$previousErrorReporting = error_reporting(E_ERROR | E_PARSE);
			$spreadsheet = IOFactory::load($templatePath);
			error_reporting($previousErrorReporting);
			$templateSheet = $spreadsheet->getSheet(0);
			$spreadsheet->createSheet();
			$newSheet = $spreadsheet->getSheet($spreadsheet->getSheetCount() - 1);
			$newSheet->setTitle($this->severancePayment->employee->employee_code);
			
			for ($row = 1; $row <= 20; $row++) {
				foreach (range('A', 'N') as $column) {
					$cell = $templateSheet->getCell($column . $row);
					$newSheet->getCell($column . $row)
						->setValue($cell->getValue())
						->setXfIndex($cell->getXfIndex());
				}
			}
			
			$retirementFundAmount = '0.00';
			if ($this->severancePayment->employee->retirementFund) {
				$retirementFundAmount = number_format($this->severancePayment->employee->retirementFund->amount, 2, '.', '');
			}
			
			$finalAmount = number_format($this->severancePayment->final_amount, 2, '.', '');
			$severanceAmount = bcsub($finalAmount, $retirementFundAmount, 2);
			
			/* =================== Xử lý dữ liệu Table =================== */
			$newSheet->setCellValue('A12', $this->severancePayment->employee->full_name);
			$newSheet->setCellValue('B12', $this->severancePayment->employee->date_of_birth ? Carbon::parse($this->severancePayment->employee->date_of_birth)->format('Y/m/d') : '');
			$newSheet->setCellValue('C12', $this->severancePayment->employee->join_date ? Carbon::parse($this->severancePayment->employee->join_date)->format('Y/m/d') : '');
			$newSheet->setCellValue('D12', $this->severancePayment->tenure_year);
			$newSheet->setCellValue('E12', $this->severancePayment->tenure_points);
			$newSheet->setCellValue('F12', $this->severancePayment->tenure_amount);
			$newSheet->setCellValue('G12', $this->severancePayment->level_points);
			$newSheet->setCellValue('H12', $this->severancePayment->level_amount);
			$newSheet->setCellValue('I12', $this->severancePayment->position_points);
			$newSheet->setCellValue('J12', $this->severancePayment->position_amount);
			$newSheet->setCellValue('K12', number_format($this->severancePayment->total_points, 2, '.', ','));
			$newSheet->setCellValue('L12', $this->severancePayment->total_amount);
			$newSheet->setCellValue('M12', $this->severancePayment->payment_percent . "%");
			$newSheet->setCellValue('N12', $this->severancePayment->final_amount);
			$newSheet->setCellValue('C14', $retirementFundAmount);
			$newSheet->setCellValue('C15', $severanceAmount);
			$newSheet->getStyle('C14')->getNumberFormat()->setFormatCode('¥#,##0');
			$newSheet->getStyle('C15')->getNumberFormat()->setFormatCode('¥#,##0');
			
			$rsDate = Carbon::parse($this->severancePayment->calculation_date);
			$calculateFiscalDate = $rsDate->year . '年' . $rsDate->month . '月' . $rsDate->day . '日';
			$newSheet->setCellValue('A8', $calculateFiscalDate . '付けで退職の');
			
			$paymentDate = Carbon::parse($this->severancePayment->payment_date);
			$newSheet->setCellValue('B16', $paymentDate->year . '年' . $paymentDate->month . '月' . $paymentDate->day . '日');
			
			/* =================== Xử lý Merge Cell =================== */
			$textA10 = $newSheet->getCell('A10')->getValue();
			$textB10 = $newSheet->getCell('B10')->getValue();
			$textC10 = $newSheet->getCell('C10')->getValue();
			$textD10 = $newSheet->getCell('D10')->getValue();
			$textG10 = $newSheet->getCell('G10')->getValue();
			$textI10 = $newSheet->getCell('I10')->getValue();
			$textK10 = $newSheet->getCell('K10')->getValue();
			$textL10 = $newSheet->getCell('L10')->getValue();
			$textM10 = $newSheet->getCell('M10')->getValue();
			$textN10 = $newSheet->getCell('N10')->getValue();
			
			$mergePairs = [
				'A10:A11' => $textA10,
				'B10:B11' => $textB10,
				'C10:C11' => $textC10,
				'K10:K11' => $textK10,
				'L10:L11' => $textL10,
				'M10:M11' => $textM10,
				'N10:N11' => $textN10,
			];
			
			foreach ($mergePairs as $range => $value) {
				try {
					$newSheet->mergeCells($range);
					$newSheet->setCellValue(explode(':', $range)[0], $value);
					$newSheet->getStyle($range)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER)->setVertical(Alignment::VERTICAL_CENTER);
				} catch (\Exception $e) {
					Log::error("[GenerateExcelReportTwo] Merge cell two lines error: " . $e->getMessage());
					continue;
				}
			}
			
			$mergeGroups = [
				'D10:F10' => $textD10,
				'G10:H10' => $textG10,
				'I10:J10' => $textI10,
			];
			
			foreach ($mergeGroups as $range => $value) {
				try {
					$newSheet->mergeCells($range);
					$newSheet->setCellValue(explode(':', $range)[0], $value);
					$newSheet->getStyle($range)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER)->setVertical(Alignment::VERTICAL_CENTER);
				} catch (\Exception $e) {
					Log::error("[GenerateExcelReportTwo] Merge cell one line error: " . $e->getMessage());
					continue;
				}
			}
			
			/* =================== Style =================== */
			$newSheet->getStyle('C7:J7')->getBorders()->getBottom()->setBorderStyle(Border::BORDER_MEDIUM);
			
			foreach (range('A', 'N') as $column) {
				$width = $templateSheet->getColumnDimension($column)->getWidth();
				$newSheet->getColumnDimension($column)->setWidth($width);
			}
			
			for ($rowId = 1; $rowId <= 20; $rowId++) {
				$rowDimension = $templateSheet->getRowDimension($rowId);
				if ($rowDimension->getRowHeight() !== null) {
					$newSheet->getRowDimension($rowId)->setRowHeight($rowDimension->getRowHeight());
				}
			}
			
			$spreadsheet->removeSheetByIndex(0);
			$spreadsheet->setActiveSheetIndex(0);
			
			foreach ($spreadsheet->getAllSheets() as $sheet) {
				$sheet->setSelectedCell('D1');
			}
			
			$savePath = public_path('report/' . $this->reportJob->code . '.xlsx');
			$writer = new Xlsx($spreadsheet);
			$writer->save($savePath);
			
			// Set Status
			$this->reportJob->update([
				'file'        => $savePath,
				'total_sheet' => 1,
				'status'      => 'success',
				'updated_at'  => Carbon::now(),
			]);
		} catch (\Exception $exception) {
			$this->reportJob->update([
				'status'     => 'failed',
				'updated_at' => Carbon::now(),
			]);
			Log::error("[GenerateExcelSingleReportTwo] Generate excel single report two error [ID: {$this->severancePayment->id}]: " . $exception->getMessage());
		}
	}
}