onboard/app/Infrastructure/Reports/Services/AutoFillReportPayloadBuilder.php

<?php

namespace App\Infrastructure\Reports\Services;

use App\Domain\Reports\ValueObjects\MetrikaConfig;
use App\Models\Department;
use App\Models\MedicalHistory;
use App\Models\MigrationPatient;
use App\Models\MisMedicalHistory;
use App\Models\MisStationarBranch;
use App\Models\User;
use App\Services\DateRange;
use App\Services\PatientService;
use App\Services\UnifiedPatientService;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

class AutoFillReportPayloadBuilder
{
    public function __construct(
        private readonly UnifiedPatientService $unifiedPatientService,
        private readonly PatientService $patientService,
        private readonly CalculatedMetricsSynchronizer $calculatedMetricsSynchronizer,
    ) {}

    public function build(User $user, Department $department, DateRange $dateRange): array
    {
        $branchId = $this->getBranchId($department->rf_mis_department_id);
        $metrics = $this->buildMetrics($department, $user, $branchId, $dateRange);

        return [
            'departmentId' => $department->department_id,
            'userId' => $user->rf_lpudoctor_id ?? $user->id,
            'dates' => [
                $dateRange->startTimestamp(),
                $dateRange->endTimestamp(),
            ],
            'sent_at' => $dateRange->endSql(),
            'created_at' => $dateRange->endSql(),
            'status' => 'submitted',
            'metrics' => [
                MetrikaConfig::payloadKey(MetrikaConfig::PLAN) => $metrics['plan'],
                MetrikaConfig::payloadKey(MetrikaConfig::EMERGENCY) => $metrics['emergency'],
                MetrikaConfig::payloadKey(MetrikaConfig::RECIPIENT) => $metrics['recipient'],
                MetrikaConfig::payloadKey(MetrikaConfig::OUTCOME) => $metrics['discharged'] + $metrics['deceased'],
                MetrikaConfig::payloadKey(MetrikaConfig::CURRENT) => $metrics['current'],
                MetrikaConfig::payloadKey(MetrikaConfig::DECEASED) => $metrics['deceased'],
                MetrikaConfig::payloadKey(MetrikaConfig::EMERGENCY_SURGERY) => $metrics['emergency_surgery'],
                MetrikaConfig::payloadKey(MetrikaConfig::PLAN_SURGERY) => $metrics['plan_surgery'],
                MetrikaConfig::payloadKey(MetrikaConfig::TRANSFERRED) => $metrics['transferred'],
                MetrikaConfig::payloadKey(MetrikaConfig::OBSERVATION) => 0,
                MetrikaConfig::payloadKey(MetrikaConfig::DISCHARGED) => $metrics['discharged'],
            ],
            'observationPatients' => [],
            'unwantedEvents' => [],
        ];
    }

    private function buildMetrics(Department $department, User $user, ?int $branchId, DateRange $dateRange): array
    {
        if (! $branchId) {
            return [
                'plan' => 0,
                'emergency' => 0,
                'recipient' => 0,
                'discharged' => 0,
                'transferred' => 0,
                'deceased' => 0,
                'current' => 0,
                'plan_surgery' => 0,
                'emergency_surgery' => 0,
            ];
        }

        $manualSurgicalCount = $this->calculatedMetricsSynchronizer->getManualSurgicalCounts($department, $dateRange);
        $recipientQuery = $this->buildRecipientMedicalHistoryQuery($branchId, $dateRange);
        $dischargeCodes = [1, 11, 2, 12, 7, 18, 48];
        $deceasedCodes = [5, 6, 15, 16];
        $transferCodes = [4, 14];

        if ($this->useMaterializedViews()) {
            $planRecipient = (clone $recipientQuery)
                ->where('urgency_id', 1)
                ->distinct()
                ->count('original_id');

            $emergencyRecipient = (clone $recipientQuery)
                ->whereIn('urgency_id', [2, 4])
                ->distinct()
                ->count('original_id');

            $recipientTotal = (clone $recipientQuery)
                ->distinct()
                ->count('original_id');
        } else {
            $planRecipient = (clone $recipientQuery)
                ->where('rf_EmerSignID', 1)
                ->distinct()
                ->count('MedicalHistoryID');

            $emergencyRecipient = (clone $recipientQuery)
                ->whereIn('rf_EmerSignID', [2, 4])
                ->distinct()
                ->count('MedicalHistoryID');

            $recipientTotal = (clone $recipientQuery)
                ->distinct()
                ->count('MedicalHistoryID');
        }

        $discharged = $this->countOutcomeByVisitResultIds($branchId, $dateRange, $dischargeCodes);
        $deceased = $this->countOutcomeByVisitResultIds($branchId, $dateRange, $deceasedCodes);
        $transferred = $this->countOutcomeByVisitResultIds($branchId, $dateRange, $transferCodes);

        return [
            'plan' => $planRecipient,
            'emergency' => $emergencyRecipient,
            'recipient' => $recipientTotal,
            'discharged' => $discharged,
            'transferred' => $transferred,
            'deceased' => $deceased,
            'current' => $this->unifiedPatientService->getLivePatientCountByStatus($department, $user, 'current', $dateRange, $branchId, null, true),
            'plan_surgery' => $this->patientService->getSurgicalPatients('plan', $branchId, $dateRange, true) + ($manualSurgicalCount[1] ?? 0),
            'emergency_surgery' => $this->patientService->getSurgicalPatients('emergency', $branchId, $dateRange, true) + ($manualSurgicalCount[0] ?? 0),
        ];
    }

    private function buildRecipientMedicalHistoryQuery(int $branchId, DateRange $dateRange)
    {
        if ($this->useMaterializedViews()) {
            return MedicalHistory::query()
                ->whereHas('migrations', fn ($query) => $query
                    ->where('stationar_branch_id', $branchId)
                    ->admitted($dateRange->startSql(), $dateRange->endSql()));
        }

        $startAt = $dateRange->start()->copy()->subDay()->format('Y-m-d H:i:s');
        $endAt = $dateRange->end()->copy()->addDay()->format('Y-m-d H:i:s');

        if ($dateRange->isOneDay) {
            $startAt = $dateRange->startSql();
            $endAt = $dateRange->endSql();
        }

        return MisMedicalHistory::query()
            ->where('MedicalHistoryID', '<>', 0)
            ->whereExists(function ($query) use ($branchId, $startAt, $endAt) {
                $query->select(DB::raw(1))
                    ->from('stt_migrationpatient as mp')
                    ->whereColumn('mp.rf_MedicalHistoryID', 'stt_medicalhistory.MedicalHistoryID')
                    ->where('mp.rf_StationarBranchID', $branchId)
                    ->where('mp.DateIngoing', '>', $startAt)
                    ->where('mp.DateIngoing', '<=', $endAt);
            });
    }

    private function buildTreatedMedicalHistoryQuery(int $branchId, DateRange $dateRange)
    {
        if ($this->useMaterializedViews()) {
            return MedicalHistory::query()
                ->whereHas('migrations', fn ($query) => $query
                    ->where('stationar_branch_id', $branchId)
                    ->dateRange($dateRange->startSql(), $dateRange->endSql()));
        }

        $query = MisMedicalHistory::query()
            ->where('MedicalHistoryID', '<>', 0)
            ->whereExists(function ($query) use ($branchId) {
                $query->select(DB::raw(1))
                    ->from('stt_migrationpatient as mp')
                    ->whereColumn('mp.rf_MedicalHistoryID', 'stt_medicalhistory.MedicalHistoryID')
                    ->where('mp.rf_StationarBranchID', $branchId);
            });

        if ($dateRange->isOneDay) {
            return $query
                ->where('DateExtract', '>', $dateRange->startSql())
                ->where('DateExtract', '<=', $dateRange->endSql());
        }

        $startAt = $dateRange->startSql();
        $endDate = $dateRange->end()->toDateString();

        return $query
            ->where('DateExtract', '>', $startAt)
            ->whereDate('DateExtract', '<=', $endDate);
    }

    private function countOutcomeByVisitResultIds(int $branchId, DateRange $dateRange, array $visitResultIds): int
    {
        if ($this->useMaterializedViews()) {
            $query = MigrationPatient::query()
                ->where('stationar_branch_id', $branchId);

            if ($visitResultIds === [1, 11, 2, 12, 7, 18, 48]) {
                $query->discharged($dateRange->startSql(), $dateRange->endSql());
            } elseif ($visitResultIds === [5, 6, 15, 16]) {
                $query->deceased($dateRange->startSql(), $dateRange->endSql());
            } elseif ($visitResultIds === [4, 14]) {
                $query->transferred($dateRange->startSql(), $dateRange->endSql());
            } else {
                $query->whereIn('visit_result_id', $visitResultIds)
                    ->dateRange($dateRange->startSql(), $dateRange->endSql());
            }

            return $query->distinct('medical_history_id')->count('medical_history_id');
        }

        return $this->buildTreatedMedicalHistoryQuery($branchId, $dateRange)
            ->whereExists(function ($query) use ($branchId, $visitResultIds) {
                $query->select(DB::raw(1))
                    ->from('stt_migrationpatient as mp')
                    ->whereColumn('mp.rf_MedicalHistoryID', 'stt_medicalhistory.MedicalHistoryID')
                    ->where('mp.rf_StationarBranchID', $branchId)
                    ->whereIn('mp.rf_kl_VisitResultID', $visitResultIds);
            })
            ->distinct()
            ->count('MedicalHistoryID');
    }

    private function getBranchId(int $misDepartmentId): ?int
    {
        return MisStationarBranch::where('rf_DepartmentID', $misDepartmentId)
            ->value('StationarBranchID');
    }

    private function useMaterializedViews(): bool
    {
        return Schema::hasTable('mv_medicalhistory_summary') && Schema::hasTable('mv_migrationpatient_details');
    }
}