<?php

namespace App\Imports;

use App\Models\BankAccount;
use App\Models\Department;
use App\Models\Employee;
use App\Models\PensionAccount;
use Carbon\Carbon;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use PhpOffice\PhpSpreadsheet\Shared\Date as ExcelDate;
use Throwable;

/**
 * Header-based bulk employee import for setting up a new institution.
 * Keyed on File Number (the primary identifier); IPPIS is optional.
 * Idempotent: re-uploading updates existing employees by file number.
 */
class EmployeeBulkImport implements ToCollection, WithHeadingRow
{
    public int $created = 0;
    public int $updated = 0;
    public int $skipped = 0;

    /** @var array<int,array{row:int,file:string,messages:array<int,string>}> */
    public array $errors = [];

    public function collection(Collection $rows): void
    {
        Employee::disableAuditing();
        BankAccount::disableAuditing();
        PensionAccount::disableAuditing();
        Department::disableAuditing();

        $deptCache = [];
        $seenFile = [];
        $seenIppis = [];

        foreach ($rows as $i => $row) {
            $line = $i + 2; // heading row + 1-based
            $file = $this->clean($row['file_number'] ?? null);
            $ippis = $this->clean($row['ippis_number'] ?? null);
            $surname = $this->clean($row['surname'] ?? $row['last_name'] ?? $row['full_name'] ?? null);

            $errs = [];
            if ($file === null) {
                $errs[] = 'File Number is required';
            } elseif (isset($seenFile[$file])) {
                $errs[] = "Duplicate File Number within the file ({$file})";
            }
            if ($surname === null) {
                $errs[] = 'Surname / Name is required';
            }
            if ($ippis !== null && isset($seenIppis[strtoupper($ippis)])) {
                $errs[] = "Duplicate IPPIS within the file ({$ippis})";
            }

            // Skip a completely blank row silently.
            if ($file === null && $surname === null && $ippis === null && $this->rowIsEmpty($row)) {
                continue;
            }

            if ($errs !== []) {
                $this->skipped++;
                $this->errors[] = ['row' => $line, 'file' => $file ?? '', 'messages' => $errs];

                continue;
            }

            try {
                $deptName = $this->cleanDepartment($row['department'] ?? null);
                $departmentId = $deptName !== null
                    ? ($deptCache[$deptName] ??= Department::firstOrCreate(['name' => $deptName])->id)
                    : null;

                [$grade, $structure] = [$this->upper($row['grade'] ?? null), $this->upper($row['salary_structure'] ?? null)];

                $employee = Employee::updateOrCreate(
                    ['file_number' => $file],
                    [
                        'employee_code' => $ippis,
                        'first_name' => $this->clean($row['first_name'] ?? null),
                        'last_name' => $surname,
                        'department_id' => $departmentId,
                        'salary_structure' => $structure,
                        'grade_level' => $grade,
                        'salary_step' => $this->intOrNull($row['step'] ?? null),
                        'phone' => $this->normPhone($row['phone'] ?? null),
                        'date_of_birth' => $this->parseDate($row['date_of_birth'] ?? null),
                        'gender' => $this->normGender($row['gender'] ?? null),
                        'marital_status' => $this->upper($row['marital_status'] ?? null),
                        'union_name' => $this->upper($row['union'] ?? null),
                        'date_of_join' => $this->parseDate($row['date_of_join'] ?? null),
                        'employment_status' => $this->clean($row['employment_status'] ?? null) ?? 'Active Employee',
                    ]
                );

                $employee->wasRecentlyCreated ? $this->created++ : $this->updated++;
                $seenFile[$file] = true;
                if ($ippis !== null) {
                    $seenIppis[strtoupper($ippis)] = true;
                }

                $bank = array_filter([
                    'bank_name' => $this->clean($row['bank_name'] ?? null),
                    'branch_bank' => $this->clean($row['bank_branch'] ?? null),
                    'account_type' => $this->clean($row['account_type'] ?? null),
                    'account_number' => $this->normDigits($row['account_number'] ?? null),
                ], fn ($v) => $v !== null);
                if ($bank !== []) {
                    BankAccount::updateOrCreate(['employee_id' => $employee->id, 'is_primary' => true], $bank);
                }

                $pension = array_filter([
                    'pension_house' => $this->clean($row['pension_house'] ?? null),
                    'pen_number' => $this->clean($row['rsa_pin'] ?? null),
                ], fn ($v) => $v !== null);
                if ($pension !== []) {
                    PensionAccount::updateOrCreate(['employee_id' => $employee->id], $pension);
                }
            } catch (Throwable $e) {
                $this->skipped++;
                $this->errors[] = ['row' => $line, 'file' => $file, 'messages' => [$this->friendly($e->getMessage())]];
            }
        }
    }

    private function rowIsEmpty($row): bool
    {
        foreach ($row as $v) {
            if ($this->clean($v) !== null) {
                return false;
            }
        }

        return true;
    }

    private function friendly(string $msg): string
    {
        if (str_contains($msg, 'employees_file_number_unique')) {
            return 'File Number already used by another employee';
        }
        if (str_contains($msg, 'employees_employee_code_unique')) {
            return 'IPPIS Number already used by another employee';
        }

        return 'Row could not be saved';
    }

    private function clean(mixed $v): ?string
    {
        if ($v === null) {
            return null;
        }
        $s = trim(trim((string) $v), "\"'");
        $s = trim($s);

        return $s === '' ? null : $s;
    }

    private function upper(mixed $v): ?string
    {
        $s = $this->clean($v);

        return $s === null ? null : strtoupper($s);
    }

    private function cleanDepartment(mixed $v): ?string
    {
        $s = $this->clean($v);
        if ($s === null) {
            return null;
        }

        return in_array(strtoupper($s), ['NOT AVAILABLE', 'UNSPECIFIED', 'N/A', 'NA', 'NIL', 'NONE'], true) ? null : $s;
    }

    private function intOrNull(mixed $v): ?int
    {
        return is_numeric($v) ? (int) round((float) $v) : null;
    }

    private function normGender(mixed $v): ?string
    {
        $s = $this->clean($v);
        if ($s === null) {
            return null;
        }
        $u = strtoupper($s);

        return str_starts_with($u, 'M') ? 'Male' : (str_starts_with($u, 'F') ? 'Female' : null);
    }

    private function normDigits(mixed $v): ?string
    {
        if ($v === null || $v === '') {
            return null;
        }
        // Drop an Excel float's trailing ".0" without casting to float (which
        // would strip leading zeros from account numbers), then keep digits.
        $s = preg_replace('/\.0+$/', '', (string) $v);
        $d = preg_replace('/\D/', '', $s);

        return $d === '' ? null : $d;
    }

    private function normPhone(mixed $v): ?string
    {
        $d = $this->normDigits($v);
        if ($d === null) {
            return null;
        }

        return strlen($d) === 10 ? '0'.$d : $d;
    }

    private function parseDate(mixed $v): ?string
    {
        if ($v === null || $v === '') {
            return null;
        }
        if (is_numeric($v)) {
            try {
                return ExcelDate::excelToDateTimeObject((float) $v)->format('Y-m-d');
            } catch (Throwable) {
                return null;
            }
        }
        try {
            return Carbon::parse((string) $v)->format('Y-m-d');
        } catch (Throwable) {
            return null;
        }
    }
}
