sfp_api_laravel_serprati/app/Services/ConveniosMedicosImportService.php

<?php

namespace App\Services;

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

class ConveniosMedicosImportService
{
    use ImportsPartners;

    private const MODE_CONSULTAS   = 'consultas';
    private const MODE_LABORATORIO = 'laboratorio';

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

        $mode         = null;
        $clinicGroups = [];
        $labRows      = [];

        foreach ($rows as $row) {
            $cells = collect($row)
                ->map(fn ($value) => $this->cell($value))
                ->values()
                ->all();

            $filled = array_values(array_filter($cells, fn ($value) => $value !== ''));

            if (empty($filled)) {
                continue;
            }

            $upperFilled = array_map(fn ($value) => mb_strtoupper($value), $filled);

            if ($upperFilled[0] === 'ESPECIALIDADE') {
                $mode = self::MODE_CONSULTAS;
                continue;
            }

            if (str_starts_with($upperFilled[0], 'LABORAT') && in_array('TELEFONE', $upperFilled, true)) {
                $mode = self::MODE_LABORATORIO;
                continue;
            }

            if ($mode === null) {
                continue;
            }

            if ($mode === self::MODE_CONSULTAS) {
                $specialty  = $cells[0] ?? '';
                $clinicName = $cells[1] ?? '';
                $doctor     = $cells[2] ?? '';
                $phone      = $cells[3] ?? '';
                $address    = $cells[4] ?? '';
                $priceRaw   = $cells[5] ?? '';

                if ($clinicName === '' || $specialty === '') {
                    continue;
                }

                if (!isset($clinicGroups[$clinicName])) {
                    $clinicGroups[$clinicName] = [
                        'phone'    => null,
                        'address'  => null,
                        'services' => [],
                    ];
                }

                if ($phone && !$clinicGroups[$clinicName]['phone']) {
                    $clinicGroups[$clinicName]['phone'] = $phone;
                }
                if ($address && !$clinicGroups[$clinicName]['address']) {
                    $clinicGroups[$clinicName]['address'] = $address;
                }

                $serviceName = $doctor
                    ? "{$specialty} - {$doctor}"
                    : $specialty;

                $clinicGroups[$clinicName]['services'][] = [
                    'name'  => $serviceName,
                    'price' => $this->parsePrice($priceRaw),
                ];
            } elseif ($mode === self::MODE_LABORATORIO) {
                $rest = array_slice($filled, 1);

                $phones = array_values(array_filter($rest, fn ($value) => $this->looksLikePhone($value)));
                $others = array_values(array_filter($rest, fn ($value) => !$this->looksLikePhone($value)));

                $labRows[] = [
                    'name'    => $filled[0],
                    'phone'   => $phones[0] ?? null,
                    'address' => $others[0] ?? null,
                ];
            }
        }

        $convMedicaCategory = Category::firstOrCreate(
            ['name' => 'Convênios Médicos', 'type' => 'partner'],
            ['active' => true]
        );

        $labCategory = Category::firstOrCreate(
            ['name' => 'Laboratório', 'type' => 'partner'],
            ['active' => true]
        );

        $stats = [
            'partners_created'    => 0,
            'partners_updated'    => 0,
            'partners_inactivated'=> 0,
            'services_created'    => 0,
            'services_updated'    => 0,
            'services_inactivated'=> 0,
        ];

        $importedPartnerIds = [];

        foreach ($clinicGroups as $clinicName => $clinicData) {
            [$partner, $isNew, $wasModified] = $this->upsertPartner($clinicName, [
                'phone'       => $clinicData['phone'],
                'address'     => $clinicData['address'],
                'category_id' => $convMedicaCategory->id,
            ], 'agreement');

            $importedPartnerIds[] = $partner->id;
            if ($isNew) {
                $stats['partners_created']++;
            } elseif ($wasModified) {
                $stats['partners_updated']++;
            }

            $processedServiceIds = [];

            foreach ($clinicData['services'] as $svcData) {
                $service = PartnerAgreementService::withTrashed()
                    ->where('partner_agreement_id', $partner->id)
                    ->whereRaw('LOWER(TRIM(name)) = LOWER(TRIM(?))', [$svcData['name']])
                    ->first();

                if ($service) {
                    if ($service->trashed()) {
                        $service->restore();
                    }

                    $changed = false;

                    if ($svcData['price'] !== null && (float) $service->associate_price !== (float) $svcData['price']) {
                        $service->associate_price = $svcData['price'];
                        $changed = true;
                    }

                    if ($service->status !== PartnerAgreementServiceStatusEnum::ACTIVE) {
                        $service->status = PartnerAgreementServiceStatusEnum::ACTIVE;
                        $changed = true;
                    }

                    if ($changed) {
                        $service->save();
                        $stats['services_updated']++;
                    }

                    $processedServiceIds[] = $service->id;
                } else {
                    $created = PartnerAgreementService::create([
                        'partner_agreement_id' => $partner->id,
                        'name'                 => $svcData['name'],
                        'associate_price'      => $svcData['price'],
                        'status'               => PartnerAgreementServiceStatusEnum::ACTIVE,
                    ]);

                    $processedServiceIds[] = $created->id;
                    $stats['services_created']++;
                }
            }

            if (!empty($processedServiceIds)) {
                $stats['services_inactivated'] += PartnerAgreementService::where('partner_agreement_id', $partner->id)
                    ->where('status', PartnerAgreementServiceStatusEnum::ACTIVE)
                    ->whereNotIn('id', $processedServiceIds)
                    ->update(['status' => PartnerAgreementServiceStatusEnum::INACTIVE]);
            }
        }

        foreach ($labRows as $labData) {
            [$partner, $isNew, $wasModified] = $this->upsertPartner($labData['name'], [
                'phone'       => $labData['phone'],
                'address'     => $labData['address'],
                'category_id' => $labCategory->id,
            ], 'agreement');

            $importedPartnerIds[] = $partner->id;
            if ($isNew) {
                $stats['partners_created']++;
            } elseif ($wasModified) {
                $stats['partners_updated']++;
            }
        }

        if (!empty($importedPartnerIds)) {
            $stats['partners_inactivated'] = PartnerAgreement::whereIn('category_id', [
                $convMedicaCategory->id,
                $labCategory->id,
            ])
                ->where('status', PartnerAgreementStatusEnum::ACTIVE)
                ->whereNotIn('id', $importedPartnerIds)
                ->update(['status' => PartnerAgreementStatusEnum::INACTIVE]);
        }

        $stats['total'] = $rows->count();

        return $stats;
    }

    private function looksLikePhone(string $value): bool
    {
        if (preg_match('/\p{L}/u', $value)) {
            return false;
        }

        return strlen(preg_replace('/\D/', '', $value)) >= 8;
    }
}