<?php

namespace App\Imports;

use App\Models\Employee;
use PhpOffice\PhpSpreadsheet\IOFactory;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\DB;

class EmployeeImport
{
    protected $results = [
        'success' => 0,
        'failed' => 0,
        'errors' => [],
        'updated' => [],
        'not_found' => [],
        'skipped' => []
    ];

    public function import($filePath)
    {
        try {
            $spreadsheet = IOFactory::load($filePath);
            $worksheet = $spreadsheet->getActiveSheet();
            $rows = $worksheet->toArray();

            if (empty($rows)) {
                throw new \Exception('No data found in the file.');
            }

            // Get headers (first row)
            $headers = array_shift($rows);

            // Clean headers
            $headers = array_map(function ($header) {
                $header = trim($header);
                $header = preg_replace('/^[\xEF\xBB\xBF\x{FEFF}]/u', '', $header);
                $header = preg_replace('/[^a-zA-Z0-9_]/', '', $header);
                return $header;
            }, $headers);

            foreach ($rows as $rowIndex => $row) {
                try {
                    $data = [];
                    foreach ($headers as $index => $header) {
                        $data[$header] = $row[$index] ?? null;
                    }

                    $empId = $data['EmpId'] ?? $data['empid'] ?? $data['EMPID'] ?? null;

                    if (!$empId) {
                        $this->results['failed']++;
                        $this->results['skipped'][] = [
                            'row' => $rowIndex + 2,
                            'reason' => 'No EmpId found'
                        ];
                        continue;
                    }

                    $employee = Employee::where('EmpId', $empId)->first();

                    if (!$employee) {
                        $this->results['failed']++;
                        $this->results['not_found'][] = [
                            'EmpId' => $empId,
                            'name' => $data['FullNameEng'] ?? $data['FullName_Eng'] ?? 'Unknown',
                            'row' => $rowIndex + 2
                        ];
                        continue;
                    }

                    $updateData = [
                        'staff_code' => $data['StaffCode'] ?? $data['Staff_Code'] ?? $data['staff_code'] ?? $employee->staff_code,
                        'dept_name' => $data['Department'] ?? $data['department'] ?? $employee->dept_name,
                        'FullNamh' => $data['FullNameAmh'] ?? $data['FullName_Amh'] ?? $data['fullname_amh'] ?? $employee->FullNamh,
                        'FullNeng' => $data['FullNameEng'] ?? $data['FullName_Eng'] ?? $data['fullname_eng'] ?? $employee->FullNeng,
                        'sex' => $data['Gender'] ?? $data['gender'] ?? $employee->sex,
                        'country_name' => $data['Country'] ?? $data['country'] ?? $employee->country_name,
                        'job_title' => $data['JobTitle'] ?? $data['Job_Title'] ?? $data['job_title'] ?? $employee->job_title,
                        'sub_city' => $data['SubCity'] ?? $data['subcity'] ?? $employee->sub_city,
                        'college' => $data['College'] ?? $data['college'] ?? $employee->college,
                        'house_number' => $data['HouseNo'] ?? $data['houseno'] ?? $employee->house_number,
                        'woreda' => $data['Woreda'] ?? $data['woreda'] ?? $employee->woreda,
                        'Staff_Type' => $data['StaffType'] ?? $data['Staff_Type'] ?? $data['staff_type'] ?? $employee->Staff_Type,
                        'updated' => true,
                    ];

                    $updateData = array_filter($updateData, function ($value) {
                        return $value !== null && $value !== '';
                    });

                    if (!empty($updateData)) {
                        $employee->update($updateData);

                        $this->results['success']++;
                        $this->results['updated'][] = [
                            'EmpId' => $employee->EmpId,
                            'name' => $employee->FullNeng ?? $employee->FullNamh
                        ];
                    } else {
                        $this->results['skipped'][] = [
                            'EmpId' => $empId,
                            'row' => $rowIndex + 2,
                            'reason' => 'No new data to update'
                        ];
                    }
                } catch (\Exception $e) {
                    $this->results['failed']++;
                    $this->results['errors'][] = [
                        'row' => $rowIndex + 2,
                        'error' => $e->getMessage()
                    ];
                    Log::error('Employee import error at row ' . ($rowIndex + 2) . ': ' . $e->getMessage());
                }
            }

            $spreadsheet->disconnectWorksheets();
            unset($spreadsheet);
        } catch (\Exception $e) {
            throw new \Exception('Failed to read Excel file: ' . $e->getMessage());
        }

        return $this->results;
    }

    public function getResults()
    {
        return $this->results;
    }
}
