sfp_api_laravel_serprati/app/Services/ParceirosImportService.php

<?php

namespace App\Services;

use App\Enums\PartnerAgreementStatusEnum;
use App\Imports\ParceirosImport;
use App\Models\Category;
use App\Models\PartnerAgreement;
use App\Traits\ImportsPartners;
use Maatwebsite\Excel\Facades\Excel;

class ParceirosImportService
{
    use ImportsPartners;

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

        $created             = 0;
        $updated             = 0;
        $importedPartnerIds  = [];
        $processedCategoryIds = [];
        $currentCategoryId   = null;

        foreach ($rows as $row) {
            $col0 = $this->cell($row[0] ?? '');
            $col1 = $this->cell($row[1] ?? '');
            $col2 = $this->cell($row[2] ?? '');

            if ($col0 === '' && $col1 === '' && $col2 === '') {
                continue;
            }

            $upper0 = mb_strtoupper($col0);

            if (str_contains($upper0, 'LISTA DE PARCEIROS')) {
                continue;
            }

            if ($upper0 === 'EMPRESA') {
                if ($col1 === '') {
                    continue;
                }

                $category = $this->findOrCreateCategory($col1);
                $currentCategoryId = $category->id;
                $processedCategoryIds[$currentCategoryId] = true;
                continue;
            }

            if ($currentCategoryId === null || $col0 === '') {
                continue;
            }

            [$partner, $isNew, $wasModified] = $this->upsertPartner($col0, [
                'description' => $col1 ?: null,
                'phone'       => $col2 ?: null,
                'category_id' => $currentCategoryId,
            ], 'partner');

            $importedPartnerIds[] = $partner->id;
            if ($isNew) {
                $created++;
            } elseif ($wasModified) {
                $updated++;
            }
        }

        $inactivated = 0;
        if (!empty($processedCategoryIds) && !empty($importedPartnerIds)) {
            $inactivated = PartnerAgreement::whereIn('category_id', array_keys($processedCategoryIds))
                ->where('status', PartnerAgreementStatusEnum::ACTIVE)
                ->whereNotIn('id', $importedPartnerIds)
                ->update(['status' => PartnerAgreementStatusEnum::INACTIVE]);
        }

        return [
            'total'       => $rows->count(),
            'created'     => $created,
            'updated'     => $updated,
            'inactivated' => $inactivated,
        ];
    }

    private function findOrCreateCategory(string $rawName): Category
    {
        $name = mb_convert_case(mb_strtolower($rawName), MB_CASE_TITLE, 'UTF-8');

        $category = Category::whereRaw(
            'LOWER(TRIM(name)) = LOWER(TRIM(?)) AND type = ?',
            [$name, 'partner']
        )->first();

        return $category ?? Category::create([
            'name'   => $name,
            'type'   => 'partner',
            'active' => true,
        ]);
    }
}