sfp_api_laravel_serprati/app/Services/AssociadoImportService.php

<?php

namespace App\Services;

use App\Enums\UserStatusEnum;
use App\Enums\UserTypeEnum;
use App\Imports\AssociadoImport;
use App\Models\User;
use Carbon\Carbon;
use Illuminate\Support\Facades\Hash;
use Illuminate\Support\Str;
use Maatwebsite\Excel\Facades\Excel;

class AssociadoImportService
{
    public function syncFromExcel(string $filePath): array
    {
        $import = new AssociadoImport();
        Excel::import($import, $filePath);
        $rows = $import->rows ?? collect();

        $today = Carbon::today()->toDateString();

        $existing = User::where('type', UserTypeEnum::ASSOCIADO)
            ->whereNotNull('registration')
            ->get()
            ->keyBy('registration');

        $importedRegistrations = [];
        $seenRegistrations     = [];
        $created    = 0;
        $updated    = 0;

        foreach ($rows as $row) {
            $registration = $row['registration'];
            $name         = $row['name'];

            if (isset($seenRegistrations[$registration])) {
                continue;
            }
            $seenRegistrations[$registration] = true;

            $importedRegistrations[] = $registration;

            if ($existing->has($registration)) {
                $user = $existing->get($registration);
                $changed = false;

                if ($user->name !== $name) {
                    $user->name = $name;
                    $changed = true;
                }

                if ($user->status !== UserStatusEnum::ACTIVE) {
                    $user->status      = UserStatusEnum::ACTIVE;
                    $user->excluded_at = null;
                    $changed = true;
                }

                if ($changed) {
                    $user->save();
                    $updated++;
                }
            } else {
                $firstName = $this->extractFirstName($name);
                $email     = "{$firstName}_{$registration}@serprati.com";
                $password  = "{$firstName}2026";

                User::create([
                    'name'           => $name,
                    'email'          => $email,
                    'password'       => Hash::make($password),
                    'type'           => UserTypeEnum::ASSOCIADO,
                    'status'         => UserStatusEnum::ACTIVE,
                    'registration'   => $registration,
                    'admission_date' => $today,
                ]);

                $created++;
            }
        }

        $inactivated = User::where('type', UserTypeEnum::ASSOCIADO)
            ->where('status', UserStatusEnum::ACTIVE)
            ->whereNotNull('registration')
            ->whereNotIn('registration', $importedRegistrations)
            ->update([
                'status'      => UserStatusEnum::INACTIVE,
                'excluded_at' => now(),
            ]);

        return [
            'created'     => $created,
            'updated'     => $updated,
            'inactivated' => $inactivated,
        ];
    }

    private function extractFirstName(string $fullName): string
    {
        $firstName = explode(' ', trim($fullName))[0];
        return strtolower(Str::ascii($firstName));
    }
}