<?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\Border;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class GenerateExcelReportOne 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/1.xlsx');
			// Suppress XML warnings from PhpSpreadsheet when loading template
			$previousErrorReporting = error_reporting(E_ERROR | E_PARSE);
			$spreadsheet = IOFactory::load($templatePath);
			error_reporting($previousErrorReporting);
			$sheet = $spreadsheet->getActiveSheet();
			
			$employeeService = new EmployeeService();
			$payouts = Payout::select('years', 'rate')->orderBy('years')->get()->toArray();
			$pointTable = PointTable::select('years', 'points')->pluck('points', 'years')->toArray();
			$employees = Employee::with('department', '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();
			
			$row = 6;
			$totalPointYear = 0;
			$totalPointLevel = 0;
			$totalPointPosition = 0;
			$totalAmountYear = 0;
			$totalAmountLevel = 0;
			$totalAmountPosition = 0;
			$totalPoint = 0;
			$totalAmount = 0;
			$totalAmountSeverancePayment = 0;
			
			foreach ($employees as $employee) {
				$this->currentEmployeeId = $employee->id;
				$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'), '.');
				}
				
				$sheet->setCellValue('A' . $row, $employee->department->code);
				$sheet->setCellValue('B' . $row, $employee->department->name);
				$sheet->setCellValue('C' . $row, $employee->employee_code);
				$sheet->setCellValue('D' . $row, $employee->full_name);
				$sheet->setCellValue('E' . $row, $result['date_of_birth']);
				$sheet->setCellValue('F' . $row, $result['join_date']);
				$sheet->setCellValue('G' . $row, Carbon::parse($result['fiscal_date'])->format('Y/m/d')); //$sheet->setCellValue('G' . $row, $result['resigned_date']);
				$sheet->setCellValue('H' . $row, $result['tenure_year']);
				$sheet->setCellValue('I' . $row, $result['tenure_point']);
				$sheet->setCellValue('J' . $row, $result['tenure_amount']);
				$sheet->setCellValue('K' . $row, $result['level_point']);
				$sheet->setCellValue('L' . $row, $result['level_amount']);
				$sheet->setCellValue('M' . $row, $result['position_point']);
				$sheet->setCellValue('N' . $row, $result['position_amount']);
				$sheet->setCellValue('O' . $row, $result['total_point']);
				$sheet->setCellValue('P' . $row, $result['total_amount']);
				$sheet->setCellValue('Q' . $row, $paymentPercent . "%");
				$sheet->setCellValue('R' . $row, $result['final_amount']);
				
				$totalPointYear += $result['tenure_point'];
				$totalPointLevel += $result['level_point'];
				$totalPointPosition += $result['position_point'];
				
				$totalAmountYear += $result['tenure_amount'];
				$totalAmountLevel += $result['level_amount'];
				$totalAmountPosition += $result['position_amount'];
				
				$totalPoint += $result['total_point'];
				$totalAmount += $result['total_amount'];
				$totalAmountSeverancePayment += $result['final_amount'];
				
				$sheet->getStyle('A' . $row . ':R' . $row)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
				$row++;
			}
			
			$sheet->setCellValue('A' . $row, "合計");
			$sheet->setCellValue('B' . $row, "");
			$sheet->setCellValue('C' . $row, "");
			$sheet->setCellValue('D' . $row, "");
			$sheet->setCellValue('E' . $row, "");
			$sheet->setCellValue('F' . $row, "");
			$sheet->setCellValue('G' . $row, "");
			$sheet->setCellValue('H' . $row, "");
			$sheet->setCellValue('I' . $row, $totalPointYear);
			$sheet->setCellValue('J' . $row, $totalAmountYear);
			$sheet->setCellValue('K' . $row, $totalPointLevel);
			$sheet->setCellValue('L' . $row, $totalAmountLevel);
			$sheet->setCellValue('M' . $row, $totalPointPosition);
			$sheet->setCellValue('N' . $row, $totalAmountPosition);
			$sheet->setCellValue('O' . $row, $totalPoint);
			$sheet->setCellValue('P' . $row, $totalAmount);
			$sheet->setCellValue('Q' . $row, "");
			$sheet->setCellValue('R' . $row, $totalAmountSeverancePayment);
			
			$lastRow = $row;
			/*foreach (range('A', 'R') as $col) {
				$sheet->getColumnDimension($col)->setAutoSize(true);
			}*/
			
			$sheet->getStyle('A6:R' . $lastRow)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
			$sheet->setSelectedCell('A1');
			
			$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("[GenerateExcelReportOne] Generate excel report one error [ID: {$this->currentEmployeeId}]: " . $exception->getMessage());
		}
	}
}