<?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 GenerateExcelSingleReportThree 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/3.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', 'O') as $column) {
					$cell = $templateSheet->getCell($column . $row);
					$newSheet->getCell($column . $row)
						->setValue($cell->getValue())
						->setXfIndex($cell->getXfIndex());
				}
			}
			
			$newSheet->setCellValue('A1', $this->severancePayment->employee->full_name . ' 殿');
			$row10Height = $templateSheet->getRowDimension(10)->getRowHeight();
			$row9Height = $templateSheet->getRowDimension(9)->getRowHeight();
			$borderStyle = [
				'borders' => [
					'allBorders' => [
						'borderStyle' => Border::BORDER_THIN,
						'color'       => ['rgb' => '000000'],
					],
				],
			];
			
			/* =================== Xử lý dữ liệu Table 1 =================== */
			$newSheet->setCellValue('A6', $this->severancePayment->employee->full_name);
			$newSheet->setCellValue('B6', $this->severancePayment->employee->date_of_birth ? Carbon::parse($this->severancePayment->employee->date_of_birth)->format('Y/m/d') : '');
			$newSheet->setCellValue('C6', $this->severancePayment->employee->join_date ? Carbon::parse($this->severancePayment->employee->join_date)->format('Y/m/d') : '');
			$newSheet->setCellValue('D6', $this->severancePayment->employee->resigned_date ? Carbon::parse($this->severancePayment->employee->resigned_date)->format('Y/m/d') : '');
			$newSheet->setCellValue('E6', $this->severancePayment->tenure_year);
			$newSheet->setCellValue('F6', $this->severancePayment->tenure_points);
			$newSheet->setCellValue('G6', $this->severancePayment->tenure_amount);
			$newSheet->setCellValue('H6', $this->severancePayment->level_points);
			$newSheet->setCellValue('I6', $this->severancePayment->level_amount);
			$newSheet->setCellValue('J6', $this->severancePayment->position_points);
			$newSheet->setCellValue('K6', $this->severancePayment->position_amount);
			$newSheet->setCellValue('L6', number_format($this->severancePayment->total_points, 2, '.', ','));
			$newSheet->setCellValue('M6', $this->severancePayment->total_amount);
			$newSheet->setCellValue('N6', $this->severancePayment->payment_percent . "%");
			$newSheet->setCellValue('O6', $this->severancePayment->final_amount);
			
			/* =================== Xử lý dữ liệu Table 2 =================== */
			$startRow = 11;
			$newSheet->setCellValue('F10', $this->severancePayment->level_transition_points);
			$currentRow = $startRow;
			foreach ($this->severancePayment->calculation_detail['level']['details'] as $item) {
				$newSheet->setCellValue('A' . $currentRow, $item['from_date']);
				$newSheet->setCellValue('B' . $currentRow, $item['to_date']);
				$newSheet->setCellValue('C' . $currentRow, $item['parent_name']);
				$newSheet->setCellValue('D' . $currentRow, $item['name']);
				$newSheet->setCellValue('E' . $currentRow, $item['salary_grade']);
				$newSheet->setCellValue('F' . $currentRow, $item['point']);
				$newSheet->getRowDimension($currentRow)->setRowHeight($row10Height);
				
				try {
					$newSheet->mergeCells('F' . $currentRow . ':G' . $currentRow);
				} catch (\Exception $e) {
					Log::error("[GenerateExcelSingleReportThree] Table 2: Set of line height and Merge column F and G error: " . $e->getMessage());
					continue;
				}
				
				$newSheet->getStyle('A' . $currentRow . ':G' . $currentRow)->applyFromArray($borderStyle);
				$newSheet->getStyle('A' . $currentRow . ':B' . $currentRow)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
				$newSheet->getStyle('C' . $currentRow . ':D' . $currentRow)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
				$newSheet->getStyle('E' . $currentRow . ':G' . $currentRow)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
				$newSheet->getStyle('F' . $currentRow)->getNumberFormat()->setFormatCode('0.00');
				$newSheet->getStyle('A' . $currentRow . ':G' . $currentRow)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
				
				$currentRow++;
			}
			
			$newSheet->setCellValue('A' . $currentRow, "合計");
			$newSheet->setCellValue('F' . $currentRow, $this->severancePayment->level_points);
			$newSheet->getRowDimension($currentRow)->setRowHeight($row10Height);
			
			try {
				$newSheet->mergeCells('F' . $currentRow . ':G' . $currentRow);
			} catch (\Exception $e) {
				Log::error("[GenerateExcelSingleReportThree] Table 2: Merge cells F and G error: " . $e->getMessage());
			}
			
			$newSheet->getStyle('A' . $currentRow . ':G' . $currentRow)->applyFromArray($borderStyle);
			$newSheet->getStyle('A' . $currentRow)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
			$newSheet->getStyle('F' . $currentRow . ':G' . $currentRow)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
			$newSheet->getStyle('A' . $currentRow . ':G' . $currentRow)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
			$newSheet->getStyle('F' . $currentRow)->getNumberFormat()->setFormatCode('0.00');
			
			/* =================== Xử lý dữ liệu Table 3 =================== */
			$startRow3 = $currentRow + 3;
			$headers = ['開始日', '終了日', '役職', '役職ポイント'];
			foreach ($headers as $index => $header) {
				$col = chr(65 + $index);
				$newSheet->setCellValue($col . $startRow3, $header);
				$newSheet->getStyle($col . $startRow3)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER)->setVertical(Alignment::VERTICAL_CENTER);
			}
			$newSheet->getRowDimension($startRow3)->setRowHeight($row9Height);
			$newSheet->getStyle('A' . $startRow3 . ':D' . $startRow3)->applyFromArray($borderStyle);
			
			$currentRow = $startRow3 + 1;
			$newSheet->setCellValue('A' . $currentRow, "移行時");
			$newSheet->setCellValue('D' . $currentRow, $this->severancePayment->position_transition_points);
			$newSheet->getRowDimension($currentRow)->setRowHeight($row10Height);
			$newSheet->getStyle('A' . $currentRow . ':D' . $currentRow)->applyFromArray($borderStyle);
			$newSheet->getStyle('A' . $currentRow)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER)->setVertical(Alignment::VERTICAL_CENTER);
			$newSheet->getStyle('D' . $currentRow)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT)->setVertical(Alignment::VERTICAL_CENTER);
			$newSheet->getStyle('D' . $currentRow)->getNumberFormat()->setFormatCode('0.00');
			
			$currentRow++;
			foreach ($this->severancePayment->calculation_detail['position']['details'] as $item) {
				$newSheet->setCellValue('A' . $currentRow, $item['from_date']);
				$newSheet->setCellValue('B' . $currentRow, $item['to_date']);
				$newSheet->setCellValue('C' . $currentRow, $item['name']);
				$newSheet->setCellValue('D' . $currentRow, $item['point']);
				$newSheet->getRowDimension($currentRow)->setRowHeight($row10Height);
				$newSheet->getStyle('A' . $currentRow . ':D' . $currentRow)->applyFromArray($borderStyle);
				$newSheet->getStyle('A' . $currentRow . ':B' . $currentRow)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
				$newSheet->getStyle('C' . $currentRow)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
				$newSheet->getStyle('D' . $currentRow)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
				$newSheet->getStyle('D' . $currentRow)->getNumberFormat()->setFormatCode('0.00');
				$newSheet->getStyle('A' . $currentRow . ':D' . $currentRow)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
				
				$currentRow++;
			}
			
			$newSheet->setCellValue('A' . $currentRow, "合計");
			$newSheet->setCellValue('D' . $currentRow, $this->severancePayment->position_points);
			$newSheet->getRowDimension($currentRow)->setRowHeight($row10Height);
			$newSheet->getStyle('A' . $currentRow . ':D' . $currentRow)->applyFromArray($borderStyle);
			$newSheet->getStyle('A' . $currentRow)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
			$newSheet->getStyle('D' . $currentRow)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
			$newSheet->getStyle('D' . $currentRow)->getNumberFormat()->setFormatCode('0.00');
			$newSheet->getStyle('A' . $currentRow . ':D' . $currentRow)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
			
			/* =================== Xử lý Merge Cell =================== */
			$textA2 = $newSheet->getCell('A2')->getValue();
			$textA4 = $newSheet->getCell('A4')->getValue();
			$textB4 = $newSheet->getCell('B4')->getValue();
			$textC4 = $newSheet->getCell('C4')->getValue();
			$textD4 = $newSheet->getCell('D4')->getValue();
			$textE4 = $newSheet->getCell('E4')->getValue();
			$textH4 = $newSheet->getCell('H4')->getValue();
			$textJ4 = $newSheet->getCell('J4')->getValue();
			$textL4 = $newSheet->getCell('L4')->getValue();
			$textM4 = $newSheet->getCell('M4')->getValue();
			$textN4 = $newSheet->getCell('N4')->getValue();
			$textO4 = $newSheet->getCell('O4')->getValue();
			$textF9 = $newSheet->getCell('F9')->getValue();
			$textF10 = $newSheet->getCell('F10')->getValue();
			
			$mergePairs = [
				'A4:A5' => $textA4,
				'B4:B5' => $textB4,
				'C4:C5' => $textC4,
				'D4:D5' => $textD4,
				'L4:L5' => $textL4,
				'M4:M5' => $textM4,
				'N4:N5' => $textN4,
				'O4:O5' => $textO4,
			];
			
			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("[GenerateExcelSingleReportThree] Merge cell two lines error: " . $e->getMessage());
					continue;
				}
			}
			
			$mergeGroups = [
				'A2:O2'   => $textA2,
				'E4:G4'   => $textE4,
				'H4:I4'   => $textH4,
				'J4:K4'   => $textJ4,
				'F9:G9'   => $textF9,
				'F10:G10' => $textF10,
			];
			
			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("[GenerateExcelSingleReportThree] Merge cell one line error: " . $e->getMessage());
					continue;
				}
			}
			
			$newSheet->getStyle('F10:G10')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT)->setVertical(Alignment::VERTICAL_CENTER);
			$newSheet->getStyle('F10:G10')->getNumberFormat()->setFormatCode('0.00');
			
			foreach (range('A', 'O') as $column) {
				$width = $templateSheet->getColumnDimension($column)->getWidth();
				$newSheet->getColumnDimension($column)->setWidth($width);
			}
			
			for ($rowId = 1; $rowId <= 50; $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("[GenerateExcelSingleReportThree] Generate excel report three error [ID: {$this->severancePayment->id}]: " . $exception->getMessage());
		}
	}
}