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) {
            $col0 = $this->cell($row[0] ?? '');
            $col1 = $this->cell($row[1] ?? '');
            $col2 = $this->cell($row[2] ?? '');

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

            $upper0 = mb_strtoupper($col0);
            $upper1 = mb_strtoupper($col1);

            if ($upper0 === 'ESPECIALIDADE') {
                $mode = self::MODE_CONSULTAS;
                continue;
            }

            if (str_starts_with($upper0, 'LABORAT') && $upper1 === 'TELEFONE') {
                $mode = self::MODE_LABORATORIO;
                continue;
            }

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

            if ($mode === self::MODE_CONSULTAS) {
                $specialty  = $col0;
                $clinicName = $col1;
                $doctor     = $col2;
                $phone      = $this->cell($row[3] ?? '');
                $address    = $this->cell($row[4] ?? '');
                $priceRaw   = $this->cell($row[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) {
                if ($col0 === '') {
                    continue;
                }

                $labRows[] = [
                    'name'    => $col0,
                    'phone'   => $col1 ?: null,
                    'address' => $col2 ?: 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] = $this->upsertPartner($clinicName, [
                'phone'       => $clinicData['phone'],
                'address'     => $clinicData['address'],
                'category_id' => $convMedicaCategory->id,
            ]);

            $importedPartnerIds[] = $partner->id;
            $isNew ? $stats['partners_created']++ : $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] = $this->upsertPartner($labData['name'], [
                'phone'       => $labData['phone'],
                'address'     => $labData['address'],
                'category_id' => $labCategory->id,
            ]);

            $importedPartnerIds[] = $partner->id;
            $isNew ? $stats['partners_created']++ : $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]);
        }

        return $stats;
    }
}