<?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;
use Yasumi\Yasumi;

class GenerateExcelReportTwo 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/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);
			
			$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', 'N') 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'), '.');
				}
				
				/* =================== Xử lý dữ liệu Table =================== */
				$newSheet->setCellValue('A12', $employee->full_name);
				$newSheet->setCellValue('B12', $employee->date_of_birth ? Carbon::parse($employee->date_of_birth)->format('Y/m/d') : '');
				$newSheet->setCellValue('C12', $employee->join_date ? Carbon::parse($employee->join_date)->format('Y/m/d') : '');
				$newSheet->setCellValue('D12', $result['tenure_year']);
				$newSheet->setCellValue('E12', $result['tenure_point']);
				$newSheet->setCellValue('F12', $result['tenure_amount']);
				$newSheet->setCellValue('G12', $result['level_point']);
				$newSheet->setCellValue('H12', $result['level_amount']);
				$newSheet->setCellValue('I12', $result['position_point']);
				$newSheet->setCellValue('J12', $result['position_amount']);
				$newSheet->setCellValue('K12', $result['total_point']);
				$newSheet->setCellValue('L12', $result['total_amount']);
				$newSheet->setCellValue('M12', $paymentPercent . "%");
				$newSheet->setCellValue('N12', $result['final_amount']);
				$newSheet->setCellValue('C14', $result['retirement_fund_amount']);
				$newSheet->setCellValue('C15', $result['severance_amount']);
				$newSheet->getStyle('C14')->getNumberFormat()->setFormatCode('¥#,##0');
				$newSheet->getStyle('C15')->getNumberFormat()->setFormatCode('¥#,##0');
				
				$rsDate = Carbon::parse($result['fiscal_date']);
				$calculateFiscalDate = $rsDate->year . '年' . $rsDate->month . '月' . $rsDate->day . '日';
				$newSheet->setCellValue('A8', $calculateFiscalDate . '付けで退職の');
				
				$paymentDate = $this->getPaymentDate($rsDate);
				$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);
			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("[GenerateExcelReportTwo] Generate excel report two error [ID: {$this->currentEmployeeId}]: " . $exception->getMessage());
		}
	}
	
	private function getPaymentDate(Carbon $rsDate): Carbon
	{
		$paymentDate = $rsDate->copy()->firstOfMonth()->addMonth()->day(25);
		$holidays = Yasumi::create('Japan', $paymentDate->year, 'ja_JP');
		while ($paymentDate->isWeekend() || $holidays->isHoliday($paymentDate)) {
			$paymentDate->addDay();
		}
		unset($holidays);
		
		return $paymentDate;
	}
}