<?php

namespace App\Http\Controllers\Admin;

use App\Department;
use App\Employee;
use App\Http\Controllers\Controller;
use App\LeaveHistory;
use App\LeaveType;
use App\Level;
use App\LevelHistory;
use App\Position;
use App\PositionHistory;
use App\RetirementFund;
use App\TransitionPoint;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Log;
use Maatwebsite\Excel\Facades\Excel;
use App\Imports\EmployeesImport;
use Illuminate\Support\Facades\Validator;
use DB;

class ImportController extends Controller
{
	private $allowedSheets = [
		'Employees',
		'PositionHistories',
		'LevelHistories',
		'LeaveHistories',
		'Transitions',
		'Dropouts',
	];
	
	private $recruitmentMethods = [
		'キャリア' => 1,    // Tuyển dụng có kinh nghiệm
		'新卒'     => 2,    // Tuyển dụng mới tốt nghiệp
		'中途'     => 3     // Tuyển dụng giữa kỳ
	];
	
	private $genders = [
		'男性'   => 1,  // Nam
		'女性'   => 2,  // Nữ
		'その他' => 3   // Khác
	];
	
	private $employeeTypes = [
		'正社員', '派遣社員', '出向社員', '契約社員', '臨時雇用社員', '嘱託社員', '現地法人社員'
	];
	
	public function store(Request $request)
	{
		$validator = Validator::make($request->all(), [
			'name' => ['required', 'string', 'max:255'],
			'size' => ['required', 'string', 'max:255'],
			'type' => ['required', 'string', 'max:255', 'in:.xls,.xlsx,application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'],
		]);
		
		$params = $request->all();
		$excelData = [];
		
		// Kiểm tra và đọc file Excel
		$this->validateAndReadExcel($validator, $params, $excelData);
		
		if ($validator->fails()) {
			return response()->json([
				'success' => false,
				'message' => $validator->errors()->first('file')
			], 200);
		}
		
		// Kiểm tra tất cả lỗi trước khi import
		$errors = $this->validateExcelData($excelData);
		
		if (!empty($errors)) {
			return response()->json([
				'success' => false,
				'message' => 'インポートエラーが発生しました。以下のエラーを確認してください。',
				'errors'  => $errors
			], 200);
		}
		
		// Import Data nếu không có lỗi
		try {
			DB::beginTransaction();
			$errors = [];
			
			if (isset($excelData['Employees']) && !empty($excelData['Employees'])) {
				$employeesData = $excelData['Employees'];
				array_shift($employeesData);
				$errors = array_merge($errors, $this->importEmployeesData($employeesData));
			}
			
			if (isset($excelData['PositionHistories']) && !empty($excelData['PositionHistories'])) {
				$positionHistoriesData = $excelData['PositionHistories'];
				array_shift($positionHistoriesData);
				$errors = array_merge($errors, $this->importPositionHistoriesData($positionHistoriesData));
			}
			
			if (isset($excelData['LevelHistories']) && !empty($excelData['LevelHistories'])) {
				$levelHistoriesData = $excelData['LevelHistories'];
				array_shift($levelHistoriesData);
				$errors = array_merge($errors, $this->importLevelHistoriesData($levelHistoriesData));
			}
			
			if (isset($excelData['LeaveHistories']) && !empty($excelData['LeaveHistories'])) {
				$leaveHistoriesData = $excelData['LeaveHistories'];
				array_shift($leaveHistoriesData);
				$errors = array_merge($errors, $this->importLeaveHistoriesData($leaveHistoriesData));
			}
			
			if (isset($excelData['Transitions']) && !empty($excelData['Transitions'])) {
				$transitionsData = $excelData['Transitions'];
				array_shift($transitionsData);
				$errors = array_merge($errors, $this->importTransitionsData($transitionsData));
			}
			
			if (isset($excelData['Dropouts']) && !empty($excelData['Dropouts'])) {
				$dropoutsData = $excelData['Dropouts'];
				array_shift($dropoutsData);
				$errors = array_merge($errors, $this->importDropoutsData($dropoutsData));
			}
			
			if (!empty($errors)) {
				DB::rollBack();
				return response()->json([
					'success' => false,
					'message' => 'インポート中にエラーが発生しました。以下のエラーを確認してください。',
					'errors'  => $errors
				], 200);
			}
			
			DB::commit();
			return response()->json([
				'success' => true,
				'message' => 'データを正常にインポートしました。',
				'errors'  => []
			], 200);
		} catch (\Exception $e) {
			DB::rollBack();
			Log::error("データインポートエラー: " . $e->getMessage(), ['trace' => $e->getTraceAsString()]);
			return response()->json([
				'success' => false,
				'message' => 'インポート中に予期せぬエラーが発生しました：' . $e->getMessage(),
				'errors'  => []
			], 200);
		}
	}
	
	private function validateAndReadExcel($validator, $params, &$excelData)
	{
		$validator->after(function ($validator) use ($params, &$excelData) {
			try {
				$filePath = public_path('uploads/files/' . $params['name']);
				if (!file_exists($filePath)) {
					$validator->errors()->add('file', 'アップロードされたファイルはExcel形式（.xlsx）でないか、破損しています。正しい形式のファイルをアップロードしてください。');
					return;
				}
				
				// Kiểm tra mã hóa UTF-8 (Lỗi 11)
				/*$fileContent = file_get_contents($filePath);
				if (!mb_check_encoding($fileContent, 'UTF-8')) {
					$validator->errors()->add('file', 'Excelファイルに無効な文字が含まれています。エンコーディングエラーを防ぐため、UTF-8形式で保存してください。');
					return;
				}*/
				
				// Đọc tất cả các sheet trong file Excel
				$sheets = Excel::toArray(new EmployeesImport, $filePath);
				
				// Lấy danh sách tên các sheet
				$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
				$spreadsheet = $reader->load($filePath);
				$sheetNames = $spreadsheet->getSheetNames();
				
				// Kiểm tra xem có sheet nào hợp lệ không (Lỗi 1)
				$validSheets = array_intersect($sheetNames, $this->allowedSheets);
				if (empty($validSheets)) {
					$validator->errors()->add('file', 'Excelファイルにデータが含まれていないか、必要なシート（Employees、PositionHistoriesなど）がありません。ファイルを再度確認してください。');
					return;
				}
				
				// Lưu dữ liệu từ các sheet hợp lệ
				$excelData = [];
				foreach ($validSheets as $sheetIndex => $sheetName) {
					$sheetData = $sheets[$sheetIndex];
					
					// Lọc bỏ các dòng trống
					$filteredData = array_filter($sheetData, function ($row) {
						return !empty(array_filter($row));
					});
					
					// Reset array keys
					$excelData[$sheetName] = array_values($filteredData);
				}
				
				// Kiểm tra cột bắt buộc (Lỗi 2)
				if (isset($excelData['Employees']) && !empty($excelData['Employees'])) {
					$requiredHeaders = [
						'社員番号', '氏名', 'フリガナ', '所属コード', '(所属名1)', '(所属名2)', '(所属名3)', '(所属名4)',
						'本名', '役職', '性別', '生年月日', '年齢', '従業員区分', '採用区分', 'コース', '入社日',
						'退職日', '勤続年数', '等級', '号俸', '勤務地', 'メール', '(最終更新ユーザー)', '(最終更新日時)'
					];
					$this->validateSheetHeaders($validator, $excelData['Employees'][0], $requiredHeaders, 'Employees');
				}
				
				if (isset($excelData['PositionHistories']) && !empty($excelData['PositionHistories'])) {
					$requiredHeaders = ['社員番号', '開始日', '終了日', '役職'];
					$this->validateSheetHeaders($validator, $excelData['PositionHistories'][0], $requiredHeaders, 'PositionHistories');
				}
				
				if (isset($excelData['LevelHistories']) && !empty($excelData['LevelHistories'])) {
					$requiredHeaders = ['社員番号', '開始日', '終了日', '雇用区分', '等級', '号俸'];
					$this->validateSheetHeaders($validator, $excelData['LevelHistories'][0], $requiredHeaders, 'LevelHistories');
				}
				
				if (isset($excelData['LeaveHistories']) && !empty($excelData['LeaveHistories'])) {
					$requiredHeaders = ['社員番号', '開始日', '終了日', '休職名', 'メモ'];
					$this->validateSheetHeaders($validator, $excelData['LeaveHistories'][0], $requiredHeaders, 'LeaveHistories');
				}
				
				if (isset($excelData['Transitions']) && !empty($excelData['Transitions'])) {
					$requiredHeaders = ['社員番号', '勤続年数移行ポイント', '資格移行ポイント', '役職移行ポイント'];
					$this->validateSheetHeaders($validator, $excelData['Transitions'][0], $requiredHeaders, 'Transitions');
				}
				
				if (isset($excelData['Dropouts']) && !empty($excelData['Dropouts'])) {
					$requiredHeaders = ['社員番号', '中退共退職金'];
					$this->validateSheetHeaders($validator, $excelData['Dropouts'][0], $requiredHeaders, 'Dropouts');
				}
			} catch (\Exception $e) {
				Log::error("Excel読み込みエラー: " . $e->getMessage());
				$validator->errors()->add('file', 'アップロードされたファイルはExcel形式（.xlsx）でないか、破損しています。正しい形式のファイルをアップロードしてください。');
			}
		});
	}
	
	private function validateSheetHeaders($validator, $headers, $requiredHeaders, $sheetName)
	{
		foreach ($requiredHeaders as $required) {
			if (!in_array($required, $headers)) {
				$validator->errors()->add(
					'file',
					"[$sheetName]シートに必須列[$required]がありません。必要な列を追加して確認してください。"
				);
			}
		}
	}
	
	private function validateExcelData($excelData)
	{
		$errors = [];
		
		// Lỗi 4: Trùng lặp mã nhân viên trong sheet Employees
		if (isset($excelData['Employees']) && !empty($excelData['Employees'])) {
			$employeeIds = array_column(array_slice($excelData['Employees'], 1), 0);
			if (count($employeeIds) !== count(array_unique($employeeIds))) {
				$errors[] = '社員番号[社員番号]がEmployeesシート内で重複しています。各社員に一意の番号を設定してください。';
			}
		}
		
		// Lỗi 5: Mã nhân viên không tồn tại trong hệ thống (bao gồm cả soft-deleted)
		$existingEmployeeIds = Employee::withTrashed()->pluck('employee_code')->toArray();
		$employeeIdsInFile = isset($excelData['Employees']) ? array_column(array_slice($excelData['Employees'], 1), 0) : [];
		foreach ($this->allowedSheets as $sheetName) {
			if ($sheetName !== 'Employees' && isset($excelData[$sheetName]) && !empty($excelData[$sheetName])) {
				$sheetEmployeeIds = array_column(array_slice($excelData[$sheetName], 1), 0);
				foreach ($sheetEmployeeIds as $id) {
					if ($id && !in_array($id, $employeeIdsInFile) && !in_array($id, $existingEmployeeIds)) {
						$errors[] = "[$sheetName]シートの社員番号[$id]がEmployeesシートまたはシステムに存在しません。有効な社員番号を確認してください。";
					}
				}
			}
		}
		
		// Lỗi 3, 6, 7: Định dạng dữ liệu, ngày tháng không hợp lý, giá trị cố định không hợp lệ
		if (isset($excelData['Employees']) && !empty($excelData['Employees'])) {
			foreach (array_slice($excelData['Employees'], 1) as $index => $row) {
				$rowIndex = $index + 2; // Dòng bắt đầu từ 2 do bỏ header
				
				// Lỗi 3: Định dạng ngày tháng không hợp lệ
				if (!empty($row[11]) && !$this->parseExcelDate($row[11])) {
					$errors[] = "[Employees]シートの[生年月日]列のデータ形式が無効です。形式を確認してください（例：日付はYYYY/MM/DD形式）。";
				}
				if (!empty($row[16]) && !$this->parseExcelDate($row[16])) {
					$errors[] = "[Employees]シートの[入社日]列のデータ形式が無効です。形式を確認してください（例：日付はYYYY/MM/DD形式）。";
				}
				if (!empty($row[17]) && !$this->parseExcelDate($row[17])) {
					$errors[] = "[Employees]シートの[退職日]列のデータ形式が無効です。形式を確認してください（例：日付はYYYY/MM/DD形式）。";
				}
				
				// Lỗi 6: Ngày tháng không hợp lý
				if (!empty($row[16]) && !empty($row[17])) {
					$joinDate = $this->parseExcelDate($row[16]);
					$resignedDate = $this->parseExcelDate($row[17]);
					if ($joinDate && $resignedDate && $resignedDate->lt($joinDate)) {
						$errors[] = "[Employees]シートの[退職日]列の日付データが不合理です（例：終了日が開始日より前）。データを確認してください。";
					}
				}
				
				// Lỗi 7: Giá trị cố định không hợp lệ
				if (!empty($row[10]) && !array_key_exists($row[10], $this->genders)) {
					$errors[] = "[Employees]シートの[性別]列の値[値]が無効です。以下のリストから選択してください：[男性、女性、その他]。";
				}
				if (!empty($row[13]) && !in_array(str_replace(' ', '', trim($row[13])), $this->employeeTypes)) {
					$errors[] = "[Employees]シートの[従業員区分]列の値[値]が無効です。以下のリストから選択してください：[正社員、派遣社員、出向社員、契約社員、臨時雇用社員、嘱託社員、現地法人社員]。";
				}
			}
		}
		
		// Lỗi 6: Ngày tháng không hợp lý trong PositionHistories, LevelHistories, LeaveHistories
		foreach (['PositionHistories', 'LevelHistories', 'LeaveHistories'] as $sheetName) {
			if (isset($excelData[$sheetName]) && !empty($excelData[$sheetName])) {
				foreach (array_slice($excelData[$sheetName], 1) as $index => $row) {
					$rowIndex = $index + 2;
					$fromDate = $this->parseExcelDate($row[1]);
					$toDate = $this->parseExcelDate($row[2]);
					if ($fromDate && $toDate && $toDate->lt($fromDate)) {
						$errors[] = "[$sheetName]シートの[終了日]列のデータ形式が無効です。形式を確認してください（例：日付はYYYY/MM/DD形式）。";
					}
					if (!empty($row[1]) && !$fromDate) {
						$errors[] = "[$sheetName]シートの[開始日]列のデータ形式が無効です。形式を確認してください（例：日付はYYYY/MM/DD形式）。";
					}
					if (!empty($row[2]) && !$toDate) {
						$errors[] = "[$sheetName]シートの[終了日]列のデータ形式が無効です。形式を確認してください（例：日付はYYYY/MM/DD形式）。";
					}
				}
			}
		}
		
		// Lỗi 8: Số tiền âm hoặc không hợp lý trong Dropouts
		if (isset($excelData['Dropouts']) && !empty($excelData['Dropouts'])) {
			foreach (array_slice($excelData['Dropouts'], 1) as $index => $row) {
				$rowIndex = $index + 2;
				if (!empty($row[1]) && (!is_numeric($row[1]) || $row[1] < 0)) {
					$errors[] = "Dropoutsシートの退職金金額が無効です（例：負の値または非数値）。データを確認してください";
				}
			}
		}
		
		// Lỗi 10: Dữ liệu không nhất quán giữa các sheet
		if (isset($excelData['LeaveHistories']) && !empty($excelData['LeaveHistories'])) {
			foreach (array_slice($excelData['LeaveHistories'], 1) as $index => $row) {
				$rowIndex = $index + 2;
				$employeeCode = trim($row[0]);
				$toDate = $this->parseExcelDate($row[2]);
				$employee = Employee::withTrashed()->where('employee_code', $employeeCode)->first();
				if ($employee && !$employee->trashed() && $employee->resigned_date && $toDate && $toDate->gt(Carbon::parse($employee->resigned_date))) {
					$errors[] = "シート間でデータが不整合です。社員[$employeeCode]の[退職日]を[LeaveHistories]で確認してください。";
				}
			}
		}
		
		return $errors;
	}
	
	private function parseExcelDate($value)
	{
		if (empty($value)) {
			return null;
		}
		
		try {
			if (is_numeric($value)) {
				$unixTimestamp = ($value - 25569) * 86400;
				return Carbon::createFromTimestamp($unixTimestamp);
			}
			return Carbon::parse($value);
		} catch (\Exception $e) {
			Log::warning("日付値の解析に失敗しました: " . $value);
			return null;
		}
	}
	
	private function splitName($fullName)
	{
		$parts = explode(' ', trim($fullName));
		$firstName = array_pop($parts);
		$lastName = implode(' ', $parts);
		
		return [
			'first_name' => $firstName,
			'last_name'  => $lastName
		];
	}
	
	private function splitKanaName($fullKanaName)
	{
		$parts = explode(' ', trim($fullKanaName));
		$firstNameKana = array_pop($parts);
		$lastNameKana = implode(' ', $parts);
		
		return [
			'first_name_kana' => $firstNameKana,
			'last_name_kana'  => $lastNameKana
		];
	}
	
	private function mapEmployeeType($type)
	{
		return $this->employeeTypes[$type] ?? '0';
	}
	
	private function importEmployeesData($data)
	{
		$errors = [];
		
		try {
			foreach ($data as $index => $row) {
				$rowIndex = $index + 2; // Dòng bắt đầu từ 2 do bỏ header
				if (count($row) < 10) {
					$errors[] = "[Employees] 行 $rowIndex: 列数が不足しています。";
					continue;
				}
				if (empty($row[0])) {
					$errors[] = "[Employees] 行 $rowIndex: [社員番号]列のデータがありません。";
					continue;
				}
				if (empty($row[3])) {
					$errors[] = "[Employees] 行 $rowIndex: [所属コード]列のデータがありません。";
					continue;
				}
				
				$department = Department::where('code', trim($row[3]))->first();
				if (!$department) {
					$errors[] = "[Employees] 行 $rowIndex: [所属コード][{$row[3]}]が見つかりません。";
					continue;
				}
				
				$names = $this->splitName($row[1]);
				$kanaNames = !empty($row[2]) ? $this->splitKanaName($row[2]) : ['last_name_kana' => null, 'first_name_kana' => null];
				$departmentId = $department->id;
				
				$dateOfBirth = $this->parseExcelDate($row[11]);
				$joinDate = $this->parseExcelDate($row[16]);
				$resignedDate = $this->parseExcelDate($row[17]);
				
				$levelId = null;
				if (!empty($row[19])) {
					$level = Level::where('name', $row[19])->first();
					if ($level) {
						$levelId = $level->id;
					} else {
						$errors[] = "[Employees] 行 $rowIndex: [等級][{$row[19]}]が見つかりません。";
						continue;
					}
				}
				
				$positionName = trim($row[9]);
				if (empty($positionName)) $positionName = 'なし';
				$position = Position::where('name', $positionName)->first();
				$positionCode = 99;
				if (!$position) {
					do {
						$checkCode = Position::where('code', $positionCode)->count();
						$positionCode++;
					} while ($checkCode > 0);
					
					$position = Position::create([
						'code'         => $positionCode,
						'name'         => $positionName,
						'point_value'  => 0,
						'is_activated' => true
					]);
				}
				$positionId = $position->id;
				
				$employeeData = [
					'employee_code'      => $row[0],
					'last_name'          => $names['last_name'],
					'first_name'         => $names['first_name'],
					'last_name_kana'     => $kanaNames['last_name_kana'],
					'first_name_kana'    => $kanaNames['first_name_kana'],
					'full_name'          => str_replace(" ", "", $row[1]),
					'full_name_kana'     => !empty($row[2]) ? str_replace(" ", "", $row[2]) : null,
					'preferred_name'     => $row[8] ?? null,
					'date_of_birth'      => $dateOfBirth,
					'gender'             => !empty($row[10]) ? ($this->genders[$row[10]] ?? 3) : 3,
					'join_date'          => $joinDate,
					'department_id'      => $departmentId,
					'position_id'        => $positionId,
					'employee_type'      => !empty(str_replace(' ', '', trim($row[13]))) ? str_replace(' ', '', trim($row[13])) : null,
					'recruitment_method' => !empty($row[14]) ? ($this->recruitmentMethods[$row[14]] ?? null) : null,
					'course'             => $row[15] ?? null,
					'level_id'           => $levelId,
					'salary_grade'       => !empty($row[20]) ? intval($row[20]) : null,
					'working_location'   => $row[21] ?? null,
					'work_email'         => !empty($row[22]) ? trim($row[22]) : null,
					'resigned_date'      => $resignedDate,
					'resigned_reason'    => !empty($resignedDate) ? 0 : null,
					'employment_status'  => !empty($resignedDate) ? 2 : 1,
					'is_created'         => false,
					'updated_by'         => auth('api')->user()->id,
					'is_activated'       => true
				];
				
				// Tìm employee kể cả đã bị soft-delete
				$employee = Employee::withTrashed()->where('employee_code', trim($row[0]))->first();
				if ($employee) {
					// Cập nhật thông tin mới nhất
					$employee->update($employeeData);

					// Nếu đã bị soft-delete, restore employee và các related records
					if ($employee->trashed()) {
						$employee->restore();

						// Restore các related records
						$employee->positionHistories()->withTrashed()->restore();
						$employee->levelHistories()->withTrashed()->restore();
						$employee->leaveHistories()->withTrashed()->restore();
						$employee->transitionPoint()->withTrashed()->restore();
						$employee->retirementFund()->withTrashed()->restore();
						$employee->severancePayment()->withTrashed()->restore();

						Log::info("Employee {$row[0]} restored from soft-delete with related records.");
					}
				} else {
					$employeeData['created_by'] = auth('api')->user()->id;
					Employee::create($employeeData);
				}
			}
			
			return $errors;
		} catch (\Exception $e) {
			Log::error("社員データのインポートエラー: " . $e->getMessage(), ['trace' => $e->getTraceAsString()]);
			throw $e;
		}
	}
	
	private function importPositionHistoriesData($data)
	{
		$errors = [];
		
		try {
			$processedRecords = [];
			
			foreach ($data as $index => $row) {
				$rowIndex = $index + 2;
				if (count($row) < 3) {
					$errors[] = "[PositionHistories] 行 $rowIndex: 列数が不足しています。";
					continue;
				}
				if (empty($row[0]) || empty($row[1]) || empty($row[3])) {
					$errors[] = "[PositionHistories] 行 $rowIndex: [社員番号]、[開始日]、[役職]列のデータがありません。";
					continue;
				}
				
				$employeeCode = trim($row[0]);
				$employee = Employee::withTrashed()->where('employee_code', $employeeCode)->first();
				$positionName = trim($row[3]);
				$position = Position::where('name', $positionName)->first();

				if (!$employee) {
					$errors[] = "[PositionHistories] 行 $rowIndex: 社員番号[$employeeCode]が[Employees]シートまたはシステムに存在しません。";
					continue;
				}

				// Nếu employee đã bị soft-delete, restore trước khi thêm history
				if ($employee->trashed()) {
					$employee->restore();
					Log::info("Employee {$employeeCode} restored for PositionHistories import.");
				}
				
				if (!$position) {
					$errors[] = "[PositionHistories] 行 $rowIndex: [役職][{$positionName}]が見つかりません。";
					continue;
				}
				
				$fromDate = $this->parseExcelDate($row[1]);
				$toDate = $this->parseExcelDate($row[2]);
				if (!$fromDate) {
					$errors[] = "[PositionHistories]シートの[開始日]列のデータ形式が無効です。形式を確認してください（例：日付はYYYY/MM/DD形式）。";
					continue;
				}
				
				$recordKey = $employee->id . '|' . $fromDate->toDateString();
				if (isset($processedRecords[$recordKey])) {
					$errors[] = "[PositionHistories] 行 $rowIndex: 社員[$employeeCode]のレコードが重複しています（同じ開始日）。";
					continue;
				}

				// Tìm record theo employee_id + from_date (kể cả soft-deleted)
				// Approach: Match by from_date, update to_date và các fields khác
				$existingRecord = PositionHistory::withTrashed()
					->where('employee_id', $employee->id)
					->where('from_date', $fromDate)
					->first();

				if ($existingRecord) {
					// Restore nếu đã soft-delete
					if ($existingRecord->trashed()) {
						$existingRecord->restore();
						Log::info("PositionHistory restored for employee {$employeeCode}, from_date: {$fromDate}");
					}
					// Update to_date và position
					$existingRecord->update([
						'position_id' => $position->id,
						'to_date'     => $toDate
					]);
				} else {
					// Tạo mới nếu không tìm thấy
					PositionHistory::create([
						'employee_id' => $employee->id,
						'position_id' => $position->id,
						'from_date'   => $fromDate,
						'to_date'     => $toDate
					]);
				}

				$processedRecords[$recordKey] = true;
			}
			
			return $errors;
		} catch (\Exception $e) {
			Log::error("役職履歴データのインポートエラー: " . $e->getMessage(), ['trace' => $e->getTraceAsString()]);
			throw $e;
		}
	}
	
	private function importLevelHistoriesData($data)
	{
		$errors = [];
		
		try {
			$processedRecords = [];
			
			foreach ($data as $index => $row) {
				$rowIndex = $index + 2;
				if (count($row) < 3) {
					$errors[] = "[LevelHistories] 行 $rowIndex: 列数が不足しています。";
					continue;
				}
				if (empty($row[0]) || empty($row[1]) || empty($row[2]) || empty($row[4])) {
					$errors[] = "[LevelHistories] 行 $rowIndex: [社員番号]、[開始日]、[終了日]、[等級]列のデータがありません。";
					continue;
				}
				
				$employeeCode = trim($row[0]);
				$employee = Employee::withTrashed()->where('employee_code', $employeeCode)->first();
				$levelName = trim($row[4]);
				$level = Level::where('name', $levelName)->first();

				if (!$employee) {
					$errors[] = "[LevelHistories] 行 $rowIndex: 社員番号[$employeeCode]が[Employees]シートまたはシステムに存在しません。";
					continue;
				}

				// Nếu employee đã bị soft-delete, restore trước khi thêm history
				if ($employee->trashed()) {
					$employee->restore();
					Log::info("Employee {$employeeCode} restored for LevelHistories import.");
				}
				
				if (!$level) {
					$errors[] = "[LevelHistories] 行 $rowIndex: [等級][{$levelName}]が見つかりません。";
					continue;
				}
				
				$fromDate = $this->parseExcelDate($row[1]);
				$toDate = $this->parseExcelDate($row[2]);
				if (!$fromDate) {
					$errors[] = "[LevelHistories]シートの[開始日]列のデータ形式が無効です。形式を確認してください（例：日付はYYYY/MM/DD形式）。";
					continue;
				}
				
				$recordKey = $employee->id . '|' . $fromDate->toDateString();
				if (isset($processedRecords[$recordKey])) {
					$errors[] = "[LevelHistories] 行 $rowIndex: 社員[$employeeCode]のレコードが重複しています（同じ開始日）。";
					continue;
				}

				// Tìm record theo employee_id + from_date (kể cả soft-deleted)
				// Approach: Match by from_date, update to_date và các fields khác
				$existingRecord = LevelHistory::withTrashed()
					->where('employee_id', $employee->id)
					->where('from_date', $fromDate)
					->first();

				if ($existingRecord) {
					// Restore nếu đã soft-delete
					if ($existingRecord->trashed()) {
						$existingRecord->restore();
						Log::info("LevelHistory restored for employee {$employeeCode}, from_date: {$fromDate}");
					}
					// Update to_date và các fields khác
					$existingRecord->update([
						'level_id'     => $level->id,
						'to_date'      => $toDate,
						'salary_grade' => $row[5] ?? null,
					]);
				} else {
					// Tạo mới nếu không tìm thấy
					LevelHistory::create([
						'employee_id'  => $employee->id,
						'level_id'     => $level->id,
						'from_date'    => $fromDate,
						'to_date'      => $toDate,
						'salary_grade' => $row[5] ?? null,
					]);
				}

				$processedRecords[$recordKey] = true;
			}
			
			return $errors;
		} catch (\Exception $e) {
			Log::error("等級履歴データのインポートエラー: " . $e->getMessage(), ['trace' => $e->getTraceAsString()]);
			throw $e;
		}
	}
	
	private function importLeaveHistoriesData($data)
	{
		$errors = [];
		
		try {
			$processedRecords = [];
			
			foreach ($data as $index => $row) {
				$rowIndex = $index + 2;
				if (count($row) < 3) {
					$errors[] = "[LeaveHistories] 行 $rowIndex: 列数が不足しています。";
					continue;
				}
				if (empty($row[0]) || empty($row[1]) || empty($row[2]) || empty($row[3])) {
					$errors[] = "[LeaveHistories] 行 $rowIndex: [社員番号]、[開始日]、[終了日]、[休職名]列のデータがありません。";
					continue;
				}
				
				$employeeCode = trim($row[0]);
				$employee = Employee::withTrashed()->where('employee_code', $employeeCode)->first();
				$leaveTypeName = trim($row[3]);
				$leaveType = LeaveType::where('name', $leaveTypeName)->first();

				if (!$employee) {
					$errors[] = "[LeaveHistories] 行 $rowIndex: 社員番号[$employeeCode]が[Employees]シートまたはシステムに存在しません。";
					continue;
				}

				// Nếu employee đã bị soft-delete, restore trước khi thêm history
				if ($employee->trashed()) {
					$employee->restore();
					Log::info("Employee {$employeeCode} restored for LeaveHistories import.");
				}
				
				if (!$leaveType) {
					$errors[] = "[LeaveHistories] 行 $rowIndex: [休職名][$leaveTypeName]が見つかりません。";
					continue;
				}
				
				$fromDate = $this->parseExcelDate($row[1]);
				$toDate = $this->parseExcelDate($row[2]);
				if (!$fromDate) {
					$errors[] = "[LeaveHistories]シートの[開始日]列のデータ形式が無効です。形式を確認してください（例：日付はYYYY/MM/DD形式）。";
					continue;
				}
				
				$recordKey = $employee->id . '|' . $fromDate->toDateString();
				if (isset($processedRecords[$recordKey])) {
					$errors[] = "[LeaveHistories] 行 $rowIndex: 社員[$employeeCode]のレコードが重複しています（同じ開始日）。";
					continue;
				}

				// Tìm record theo employee_id + from_date (kể cả soft-deleted)
				// Approach: Match by from_date, update to_date và các fields khác
				$existingRecord = LeaveHistory::withTrashed()
					->where('employee_id', $employee->id)
					->where('from_date', $fromDate)
					->first();

				if ($existingRecord) {
					// Restore nếu đã soft-delete
					if ($existingRecord->trashed()) {
						$existingRecord->restore();
						Log::info("LeaveHistory restored for employee {$employeeCode}, from_date: {$fromDate}");
					}
					// Update to_date và các fields khác
					$existingRecord->update([
						'leave_type_id' => $leaveType->id,
						'to_date'       => $toDate,
						'description'   => $row[4] ?? null,
					]);
				} else {
					// Tạo mới nếu không tìm thấy
					LeaveHistory::create([
						'employee_id'   => $employee->id,
						'leave_type_id' => $leaveType->id,
						'from_date'     => $fromDate,
						'to_date'       => $toDate,
						'total_days'    => 0,
						'description'   => $row[4] ?? null,
					]);
				}

				$processedRecords[$recordKey] = true;
			}
			
			return $errors;
		} catch (\Exception $e) {
			Log::error("休職履歴データのインポートエラー: " . $e->getMessage(), ['trace' => $e->getTraceAsString()]);
			throw $e;
		}
	}
	
	private function importTransitionsData($data)
	{
		$errors = [];
		
		try {
			foreach ($data as $index => $row) {
				$rowIndex = $index + 2;
				if (count($row) < 1) {
					$errors[] = "[Transitions] 行 $rowIndex: 列数が不足しています。";
					continue;
				}
				if (empty($row[0])) {
					$errors[] = "[Transitions] 行 $rowIndex: [社員番号]列のデータがありません。";
					continue;
				}
				
				$employeeCode = trim($row[0]);
				$employee = Employee::withTrashed()->where('employee_code', $employeeCode)->first();

				if (!$employee) {
					$errors[] = "[Transitions] 行 $rowIndex: 社員番号[$employeeCode]が[Employees]シートまたはシステムに存在しません。";
					continue;
				}

				// Nếu employee đã bị soft-delete, restore trước khi thêm transition
				if ($employee->trashed()) {
					$employee->restore();
					Log::info("Employee {$employeeCode} restored for Transitions import.");
				}

				$transitionPointData = [
					'employee_id'     => $employee->id,
					'tenure_points'   => $row[1] ?? 0,
					'level_points'    => $row[2] ?? 0,
					'position_points' => $row[3] ?? 0,
					'created_by'      => auth('api')->user()->id
				];
				
				// Tìm transition point kể cả soft-deleted
				$transitionPoint = TransitionPoint::withTrashed()->where('employee_id', $employee->id)->first();
				if ($transitionPoint) {
					// Restore nếu đã soft-delete
					if ($transitionPoint->trashed()) {
						$transitionPoint->restore();
					}
					$transitionPoint->update($transitionPointData);
				} else {
					TransitionPoint::create($transitionPointData);
				}
			}
			
			return $errors;
		} catch (\Exception $e) {
			Log::error("転換ポイントデータのインポートエラー: " . $e->getMessage(), ['trace' => $e->getTraceAsString()]);
			throw $e;
		}
	}
	
	private function importDropoutsData($data)
	{
		$errors = [];
		
		try {
			foreach ($data as $index => $row) {
				$rowIndex = $index + 2;
				if (count($row) < 1) {
					$errors[] = "[Dropouts] 行 $rowIndex: 列数が不足しています。";
					continue;
				}
				if (empty($row[0])) {
					$errors[] = "[Dropouts] 行 $rowIndex: [社員番号]列のデータがありません。";
					continue;
				}
				
				$employeeCode = trim($row[0]);
				$employee = Employee::withTrashed()->where('employee_code', $employeeCode)->first();

				if (!$employee) {
					$errors[] = "[Dropouts] 行 $rowIndex: 社員番号[$employeeCode]が[Employees]シートまたはシステムに存在しません。";
					continue;
				}

				// Nếu employee đã bị soft-delete, restore trước khi thêm dropout
				if ($employee->trashed()) {
					$employee->restore();
					Log::info("Employee {$employeeCode} restored for Dropouts import.");
				}

				$retirementFundData = [
					'employee_id' => $employee->id,
					'amount'      => $row[1] ?? 0,
					'created_by'  => auth('api')->user()->id
				];

				// Tìm retirement fund kể cả soft-deleted
				$retirementFund = RetirementFund::withTrashed()->where('employee_id', $employee->id)->first();
				if ($retirementFund) {
					// Restore nếu đã soft-delete
					if ($retirementFund->trashed()) {
						$retirementFund->restore();
						Log::info("RetirementFund restored for employee {$employeeCode}");
					}
					$retirementFund->update($retirementFundData);
				} else {
					RetirementFund::create($retirementFundData);
				}
			}
			
			return $errors;
		} catch (\Exception $e) {
			Log::error("退職金データのインポートエラー: " . $e->getMessage(), ['trace' => $e->getTraceAsString()]);
			throw $e;
		}
	}
}