onboard/app/Console/Commands/FillAverageBedDaysMetric.php

<?php
// app/Console/Commands/FillAverageBedDaysMetric.php

namespace App\Console\Commands;

use App\Models\MedicalHistorySnapshot;
use App\Models\MetrikaResult;
use App\Models\Report;
use App\Services\ReportService;
use Carbon\Carbon;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class FillAverageBedDaysMetric extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'metrics:fill-bed-days
                            {--from= : Начальная дата отчетов (включительно) (Y-m-d)}
                            {--to= : Конечная дата отчетов (включительно) (Y-m-d)}
                            {--department= : ID отделения для фильтрации}
                            {--report= : ID конкретного отчета для обработки}
                            {--force : Принудительно обновить метрику, даже если она уже существует}
                            {--chunk=100 : Количество отчетов, обрабатываемых за один раз (по умолчанию 100)}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Заполняет метрику среднего койко-дня (ID=18) для существующих отчетов на основе снапшотов пациентов. Для каждого отчета анализируются выписанные и умершие пациенты из снапшотов, рассчитывается средняя длительность пребывания и сохраняется в metrika_results.';

    /**
     * Execute the console command.
     */
    public function handle(ReportService $reportService)
    {
        $this->info('Starting to fill average bed days metrics...');

        // Build query
        $query = Report::query();

        // Filter by date range
        if ($from = $this->option('from')) {
            $query->whereDate('created_at', '>=', $from);
            $this->info("Filter: from {$from}");
        }

        if ($to = $this->option('to')) {
            $query->whereDate('created_at', '<=', $to);
            $this->info("Filter: to {$to}");
        }

        // Filter by department
        if ($departmentId = $this->option('department')) {
            $query->where('rf_department_id', $departmentId);
            $this->info("Filter: department ID {$departmentId}");
        }

        // Filter by specific report
        if ($reportId = $this->option('report')) {
            $query->where('report_id', $reportId);
            $this->info("Filter: report ID {$reportId}");
        }

        // Get total count
        $totalReports = $query->count();

        if ($totalReports === 0) {
            $this->warn('No reports found matching criteria.');
            return 0;
        }

        $this->info("Found {$totalReports} reports to process");

        // Confirm if large number
        if ($totalReports > 1000 && !$this->confirm("Processing {$totalReports} reports may take a while. Continue?")) {
            $this->info('Command cancelled.');
            return 0;
        }

        $force = $this->option('force');
        $chunkSize = (int)$this->option('chunk');

        // Progress bar
        $bar = $this->output->createProgressBar($totalReports);
        $bar->start();

        $updated = 0;
        $skipped = 0;
        $errors = 0;
        $reportsWithoutSnapshots = 0;

        // Process in chunks to avoid memory issues
        $query->orderBy('report_id')->chunk($chunkSize, function ($reportsChunk) use ($reportService, $force, &$updated, &$skipped, &$errors, &$reportsWithoutSnapshots, $bar) {
            foreach ($reportsChunk as $report) {
                try {
                    $result = $this->processReport($report, $reportService, $force);

                    if ($result === 'updated') {
                        $updated++;
                    } elseif ($result === 'skipped') {
                        $skipped++;
                    } elseif ($result === 'no_snapshots') {
                        $reportsWithoutSnapshots++;
                    }

                } catch (\Exception $e) {
                    $errors++;
                    $this->warn("\nError processing report {$report->report_id}: " . $e->getMessage());
                }

                $bar->advance();
            }
        });

        $bar->finish();
        $this->newLine();
        $this->newLine();

        // Show summary
        $this->table(
            ['Status', 'Count'],
            [
                ['Updated', $updated],
                ['Skipped (already exists)', $skipped],
                ['No snapshots found', $reportsWithoutSnapshots],
                ['Errors', $errors],
                ['Total', $totalReports],
            ]
        );

        // Show sample of updated reports if any
        if ($updated > 0) {
            $this->newLine();
            $this->info('Sample of updated reports:');

            // Get a sample of recently updated reports
            $sampleQuery = Report::whereHas('metrikaResults', function($q) {
                $q->where('rf_metrika_item_id', 18);
            })
                ->orderBy('report_id', 'desc')
                ->limit(5);

            // Apply same filters to sample
            if ($from = $this->option('from')) {
                $sampleQuery->whereDate('created_at', '>=', $from);
            }
            if ($to = $this->option('to')) {
                $sampleQuery->whereDate('created_at', '<=', $to);
            }
            if ($departmentId = $this->option('department')) {
                $sampleQuery->where('rf_department_id', $departmentId);
            }

            $sample = $sampleQuery->get()->map(function($report) {
                $metric = $report->metrikaResults
                    ->where('rf_metrika_item_id', 18)
                    ->first();
                return [
                    'report_id' => $report->report_id,
                    'department' => $report->rf_department_id,
                    'date' => $report->created_at,
                    'value' => $metric ? $metric->value : 'N/A',
                ];
            });

            if ($sample->isNotEmpty()) {
                $this->table(['Report ID', 'Department', 'Date', 'Value'], $sample->toArray());
            }
        }

        $this->newLine();
        $this->info('Done!');

        return 0;
    }

    /**
     * Process a single report
     */
    private function processReport(Report $report, ReportService $reportService, bool $force): string
    {
        // Check if metric already exists
        $existingMetric = MetrikaResult::where('rf_report_id', $report->report_id)
            ->where('rf_metrika_item_id', 18)
            ->first();

        if ($existingMetric && !$force) {
            return 'skipped';
        }

        // Check if report has snapshots
        $snapshotsCount = MedicalHistorySnapshot::where('rf_report_id', $report->report_id)
            ->whereIn('patient_type', ['discharged', 'deceased'])
            ->count();

        if ($snapshotsCount === 0) {
            // Save 0 for reports without snapshots
            MetrikaResult::updateOrCreate(
                [
                    'rf_report_id' => $report->report_id,
                    'rf_metrika_item_id' => 18,
                ],
                ['value' => 0]
            );
            return 'no_snapshots';
        }

        // Calculate average bed days from snapshots
        $avgBedDays = $this->calculateAverageFromSnapshots($report->report_id);

        // Save metric
        MetrikaResult::updateOrCreate(
            [
                'rf_report_id' => $report->report_id,
                'rf_metrika_item_id' => 18,
            ],
            ['value' => $avgBedDays]
        );

        return 'updated';
    }

    /**
     * Calculate average bed days from report snapshots
     */
    private function calculateAverageFromSnapshots(int $reportId): float
    {
        $snapshots = MedicalHistorySnapshot::where('rf_report_id', $reportId)
            ->whereIn('patient_type', ['discharged', 'deceased'])
            ->distinct()
            ->with('medicalHistory')
            ->get();

        if ($snapshots->isEmpty()) {
            return 0;
        }

        $totalDays = 0;
        $validCount = 0;

        foreach ($snapshots as $snapshot) {
            $history = $snapshot->medicalHistory;

            if ($history && $history->DateRecipient && $history->DateExtract) {
                // Check if discharge date is not the special value
                if ($history->DateExtract->format('Y-m-d') === '1900-01-01') {
                    continue;
                }

                $start = Carbon::parse($history->DateRecipient);
                $end = Carbon::parse($history->DateExtract);

                if ($end->gt($start)) {
                    $days = $start->diffInDays($end);
                    $totalDays += $days;
                    $validCount++;
                }
            }
        }

        return $validCount > 0 ? round($totalDays / $validCount, 1) : 0;
    }
}