<?php

namespace App\Jobs;

use App\Employee;
use App\Payout;
use App\PointTable;
use App\ReportJob;
use App\Services\EmployeeService;
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 GenerateExcelReportThree implements ShouldQueue
{
	use Batchable, Dispatchable, InteractsWithQueue, Queueable, SerializesModels;
	
	protected $reportJob;
	protected $filters;
	protected $currentEmployeeId;
	
	/**
	 * 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 $filters
	 */
	public function __construct(ReportJob $reportJob, $filters)
	{
		$this->onQueue(config('queue.connections.redis.queue'));
		$this->reportJob = $reportJob;
		$this->filters = $filters;
	}
	
	/**
	 * @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);
			
			$employeeService = new EmployeeService();
			$payouts = Payout::select('years', 'rate')->orderBy('years')->get()->toArray();
			$pointTable = PointTable::select('years', 'points')->pluck('points', 'years')->toArray();
			$employees = Employee::with('transitionPoint', 'levelHistories', 'positionHistories')->select('*')->whereNotNull('join_date');
			
			/*if (isset($this->filters['years']) && $this->filters['years'] !== null && $this->filters['years'] !== "") {
				$startFiscalYear = Carbon::parse($this->filters['years'] . "-04-01");
				$endFiscalYear = Carbon::parse($this->filters['years'] . "-03-31")->addYear(1);
				$currentDate = Carbon::now();
				
				if ($currentDate->greaterThan($endFiscalYear)) {
					// Đã kết thúc năm tài chính.
					$employees->where(function ($query) use ($startFiscalYear, $endFiscalYear) {
						$query->where('employment_status', Employee::STATUS_RESIGNED)
							->where('resigned_date', '>=', $startFiscalYear->format('Y-m-d'))
							->where('resigned_date', '<=', $endFiscalYear->format('Y-m-d'));
					});
				} else {
					// Chưa kết thúc năm tài chính.
					$employees->where(function ($query) use ($startFiscalYear, $endFiscalYear) {
						$query->where('employment_status', Employee::STATUS_WORKING)
							->orWhere(function ($subQuery) use ($startFiscalYear, $endFiscalYear) {
								$subQuery->where('employment_status', Employee::STATUS_RESIGNED)
									->where('resigned_date', '>=', $startFiscalYear->format('Y-m-d'))
									->where('resigned_date', '<=', $endFiscalYear->format('Y-m-d'));
							});
					});
				}
			}*/
			
			if (isset($this->filters['employee_name']) && $this->filters['employee_name'] != null && $this->filters['employee_name'] != "") {
				$employees->where('full_name', 'like', '%' . $this->filters['employee_name'] . '%');
			}
			
			if (isset($this->filters['status']) && in_array($this->filters['status'], [1, 2, 3, 4])) {
				if ($this->filters['status'] == 2) {
					$employees->resigned();
				} elseif ($this->filters['status'] == 3) {
					$employees->leave();
				} elseif ($this->filters['status'] == 4) {
					$currentYear = Carbon::now()->year;
					$retirementAge = (integer)config('settings.retirement_age');
					$retirementAge = (!$retirementAge || $retirementAge < 10 || $retirementAge > 100) ? 60 : $retirementAge;
					$retirementBirthYear = $currentYear - ($retirementAge - 1);
					$startDate = Carbon::createFromDate($retirementBirthYear, 1, 1)->startOfDay();
					$endDate = Carbon::createFromDate($retirementBirthYear, 12, 31)->endOfDay();
					$employees->working()
						->whereNotNull('date_of_birth')
						->whereBetween('date_of_birth', [$startDate->format('Y-m-d'), $endDate->format('Y-m-d')]);
				} else {
					$employees->working();
				}
			}
			
			$employees = $employees
				->orderBy('id')
				->get();
			
			foreach ($employees as $employee) {
				$this->currentEmployeeId = $employee->id;
				
				$spreadsheet->createSheet();
				$newSheet = $spreadsheet->getSheet($spreadsheet->getSheetCount() - 1);
				$employeeCode = $employee->employee_code;
				$newSheet->setTitle($employeeCode);
				
				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());
					}
				}
				
				$endFiscalYear = null;
				$calculateFiscalYear = Carbon::now()->year;
				
				if (isset($this->filters['years']) && $this->filters['years'] !== null && $this->filters['years'] !== "") {
					$endFiscalYear = (int) $this->filters['years'];
				}
				
				if ($employee->employment_status == Employee::STATUS_RESIGNED && $employee->resigned_date != null && $employee->resigned_date != "") {
					$resignedDate = Carbon::parse($employee->resigned_date);
					$fiscalResignedYear = Carbon::create($resignedDate->year, 4, 1);
					if ($resignedDate->greaterThan($fiscalResignedYear)) {
						$calculateFiscalYear = $resignedDate->addYear(1)->year;
					} else {
						$calculateFiscalYear = $resignedDate->year;
					}
					
					if ($endFiscalYear != null && $endFiscalYear < $calculateFiscalYear) {
						$calculateFiscalYear = $endFiscalYear;
					}
				} else {
					if ($endFiscalYear != null) $calculateFiscalYear = $endFiscalYear;
				}
				
				$result = $employeeService->calculateSeverancePayment($employee, $payouts, $pointTable, $calculateFiscalYear);
				$paymentPercent = $result['payment_percent'];
				if (is_numeric($paymentPercent)) {
					$formatted = sprintf('%.2f', $paymentPercent);
					$paymentPercent = rtrim(rtrim($formatted, '0'), '.');
				}
				
				$newSheet->setCellValue('A1', $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', $employee->full_name);
				$newSheet->setCellValue('B6', $employee->date_of_birth ? Carbon::parse($employee->date_of_birth)->format('Y/m/d') : '');
				$newSheet->setCellValue('C6', $employee->join_date ? Carbon::parse($employee->join_date)->format('Y/m/d') : '');
				$newSheet->setCellValue('D6', Carbon::parse($result['fiscal_date'])->format('Y/m/d')); //$newSheet->setCellValue('D6', $employee->resigned_date ? Carbon::parse($employee->resigned_date)->format('Y/m/d') : '');
				$newSheet->setCellValue('E6', $result['tenure_year']);
				$newSheet->setCellValue('F6', $result['tenure_point']);
				$newSheet->setCellValue('G6', $result['tenure_amount']);
				$newSheet->setCellValue('H6', $result['level_point']);
				$newSheet->setCellValue('I6', $result['level_amount']);
				$newSheet->setCellValue('J6', $result['position_point']);
				$newSheet->setCellValue('K6', $result['position_amount']);
				$newSheet->setCellValue('L6', $result['total_point']);
				$newSheet->setCellValue('M6', $result['total_amount']);
				$newSheet->setCellValue('N6', $paymentPercent . "%");
				$newSheet->setCellValue('O6', $result['final_amount']);
				
				/* =================== Xử lý dữ liệu Table 2 =================== */
				$startRow = 11;
				$newSheet->setCellValue('F10', $result['level']['transition_points']);
				$currentRow = $startRow;
				foreach ($result['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("[GenerateExcelReportThree] 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, $result['level']['total_points']);
				$newSheet->getRowDimension($currentRow)->setRowHeight($row10Height);
				
				try {
					$newSheet->mergeCells('F' . $currentRow . ':G' . $currentRow);
				} catch (\Exception $e) {
					Log::error("[GenerateExcelReportThree] 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, $result['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 ($result['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, $result['position']['total_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("[GenerateExcelReportThree] 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("[GenerateExcelReportThree] 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());
					}
				}
				
				// Giải phóng bộ nhớ sau khi xử lý mỗi sheet
				//$spreadsheet->disconnectWorksheets();
				//gc_collect_cycles();
			}
			
			$spreadsheet->removeSheetByIndex(0);
			if ($employees->count() > 0) {
				$spreadsheet->setActiveSheetIndex(0);
				foreach ($spreadsheet->getAllSheets() as $sheet) {
					$sheet->setSelectedCell('D1');
				}
			}
			
			// Save
			$savePath = public_path('report/' . $this->reportJob->code . '.xlsx');
			$writer = new Xlsx($spreadsheet);
			$writer->save($savePath);
			
			// Set Status
			$this->reportJob->update([
				'file'        => $savePath,
				'total_sheet' => $employees->count(),
				'status'      => 'success',
				'updated_at'  => Carbon::now(),
			]);
		} catch (\Exception $exception) {
			$this->reportJob->update([
				'status'     => 'failed',
				'updated_at' => Carbon::now(),
			]);
			Log::error("[GenerateExcelReportThree] Generate excel report three error [ID: {$this->currentEmployeeId}]: " . $exception->getMessage());
		}
	}
}