<?php

namespace App\Exports;

use App\Employee;
use Carbon\Carbon;
use Illuminate\Support\Facades\Log;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Maatwebsite\Excel\Concerns\WithTitle;
use Maatwebsite\Excel\Concerns\WithStyles;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Events\AfterSheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;

class EmployeesExport implements FromCollection, WithHeadings, WithMapping, ShouldAutoSize, WithTitle, WithStyles, WithEvents
{
	public function title(): string
	{
		return 'Employees';
	}
	
	public function collection()
	{
		return Employee::with(['department', 'position', 'level'])
			->leftJoin('users', 'employees.updated_by', '=', 'users.id')
			->select('employees.*', 'users.email as updater_email')
			->get();
	}
	
	public function headings(): array
	{
		return [
			'社員番号',
			'氏名',
			'フリガナ',
			'所属コード',
			'(所属名1)',
			'(所属名2)',
			'(所属名3)',
			'(所属名4)',
			'本名',
			'役職',
			'性別',
			'生年月日',
			'年齢',
			'従業員区分',
			'採用区分',
			'コース',
			'入社日',
			'退職日',
			'勤続年数',
			'等級',
			'号俸',
			'勤務地',
			'メール',
			'(最終更新ユーザー)',
			'(最終更新日時)'
		];
	}
	
	public function map($row): array
	{
		$departmentList = $this->detectDepartments($row->department);
		$departments = array_reverse(array_slice($departmentList, 0, 4));
		
		// Map giới tính
		$genderMap = [
			Employee::GENDER_MALE   => '男性',
			Employee::GENDER_FEMALE => '女性',
			Employee::GENDER_OTHER  => ''
		];
		
		$recruitmentMethods = [
			1 => 'キャリア',
			2 => '新卒',
			3 => '中途'
		];
		
		$positionName = '';
		if ($row->position && $row->position->name != "なし" && $row->position->code != "99") {
			$positionName = $row->position->name;
		}
		
		$ageNumber = '';
		if ($row->date_of_birth) {
			$ageNumber = Carbon::parse($row->date_of_birth)->age;
		}
		
		$workingPeriod = '';
		if ($row->join_date) {
			try {
				$start = Carbon::createFromFormat('Y-m-d', $row->join_date);
				if ($row->resigned_date) {
					$end = Carbon::createFromFormat('Y-m-d', $row->resigned_date);
				} else {
					$end = Carbon::now();
				}
				$totalMonths = $start->diffInMonths($end);
				if ($end->day < $start->day) {
					$totalMonths--;
				}
				$years = floor($totalMonths / 12);
				$months = $totalMonths % 12;
				if ($years > 0) {
					$workingPeriod = $years . '年' . $months . 'ヵ月';
				} else {
					$workingPeriod = $months . 'ヵ月';
				}
			} catch (\Exception $e) {
				\Log::error('Error: ' . $e->getMessage());
			}
		}
		
		return [
			$row->employee_code,
			$row->last_name . " " . $row->first_name,
			$row->last_name_kana . " " . $row->first_name_kana,
			$row->department ? $row->department->code : '',
			$departments[0] ?? '',
			$departments[1] ?? '',
			$departments[2] ?? '',
			$departments[3] ?? '',
			$row->preferred_name,
			$positionName,
			$genderMap[$row->gender] ?? '',
			$this->dateFormat($row->date_of_birth),
			$ageNumber,
			$row->employee_type ?? '',
			$row->recruitment_method ? $recruitmentMethods[$row->recruitment_method] : '',
			$row->course,
			$this->dateFormat($row->join_date),
			$this->dateFormat($row->resigned_date),
			$workingPeriod,
			$row->level ? $row->level->name : '',
			$row->salary_grade,
			$row->working_location,
			$row->work_email,
			$row->updater_email ?? '',
			$this->dateFormatDetail($row->updated_at, 'Y/m/d H:i')
		];
	}
	
	public function styles(Worksheet $sheet)
	{
		return [
			1                                  => [
				'alignment' => [
					'horizontal' => Alignment::HORIZONTAL_CENTER,
					'vertical'   => Alignment::VERTICAL_CENTER,
				],
			],
			'A1:Y' . ($sheet->getHighestRow()) => [
				'borders' => [
					'allBorders' => [
						'borderStyle' => Border::BORDER_THIN,
					],
				],
			],
			'A2:A' . ($sheet->getHighestRow()) => [
				'alignment' => [
					'horizontal' => Alignment::HORIZONTAL_LEFT,
					'vertical'   => Alignment::VERTICAL_CENTER,
				],
			],
			'D1:D' . ($sheet->getHighestRow()) => [
				'alignment' => [
					'horizontal' => Alignment::HORIZONTAL_CENTER,
					'vertical'   => Alignment::VERTICAL_CENTER,
				],
			],
			'L1:L' . ($sheet->getHighestRow()) => [
				'alignment' => [
					'horizontal' => Alignment::HORIZONTAL_CENTER,
					'vertical'   => Alignment::VERTICAL_CENTER,
				],
			],
			'Q1:Q' . ($sheet->getHighestRow()) => [
				'alignment' => [
					'horizontal' => Alignment::HORIZONTAL_CENTER,
					'vertical'   => Alignment::VERTICAL_CENTER,
				],
			],
			'R1:R' . ($sheet->getHighestRow()) => [
				'alignment' => [
					'horizontal' => Alignment::HORIZONTAL_CENTER,
					'vertical'   => Alignment::VERTICAL_CENTER,
				],
			],
			'Y1:Y' . ($sheet->getHighestRow()) => [
				'alignment' => [
					'horizontal' => Alignment::HORIZONTAL_CENTER,
					'vertical'   => Alignment::VERTICAL_CENTER,
				],
			],
		];
	}
	
	public function registerEvents(): array
	{
		return [
			AfterSheet::class => function (AfterSheet $event) {
				$event->sheet->freezePane('A2');
			},
		];
	}
	
	private function dateFormat($date, $format = 'Y/m/d')
	{
		if (empty($date)) return '';
		$dateStr = is_string($date) ? $date : $date->format('Y-m-d');
		return date($format, strtotime($dateStr));
	}
	
	private function dateFormatDetail($date, $format = 'n/j/Y')
	{
		if (empty($date)) return '';
		$dateStr = is_string($date) ? $date : $date->format('Y-m-d H:i:s');
		return date($format, strtotime($dateStr));
	}
	
	private function detectDepartments($department)
	{
		$result = [];
		$result[] = $department->name;
		
		if ($department->parent) {
			$result = array_merge($result, $this->detectDepartments($department->parent));
		}
		
		return $result;
	}
}