@php
    $statusLabels = [
        1 => 'W przygotowaniu',
        2 => 'Zatwierdzone',
        3 => 'Zakonczone',
        4 => 'Anulowane',
    ];

    $koDictionary = DB::table('sw020_komorki_organizacyjne')
        ->select('id', 'nazwa_pelna', 'nazwa_skrot')
        ->orderBy('nazwa_skrot', 'asc')
        ->get()
        ->keyBy('id');

    $participantsSub = DB::table('t052_szkolenie_osoba')
        ->select([
            'id_szkolenie',
            DB::raw('COUNT(*) as uczestnicy_count'),
            DB::raw('SUM(CASE WHEN COALESCE(uczestnictwo, 0) = 1 THEN 1 ELSE 0 END) as uczestnicy_obecni_count'),
        ])
        ->groupBy('id_szkolenie');

    $testSub = DB::table('t057_testy')
        ->select('id_szkolenie', DB::raw('MIN(id) as test_id'))
        ->groupBy('id_szkolenie');

    $materialsTableReady = \Illuminate\Support\Facades\Schema::hasTable('t056_szkolenie_materialy');
    $materialsStatusColumnReady = $materialsTableReady
        && \Illuminate\Support\Facades\Schema::hasColumn('t056_szkolenie_materialy', 'status_aktywny');

    $trainingsQuery = DB::table('t051_szkolenia as s')
        ->leftJoinSub($participantsSub, 'tp', function ($join) {
            $join->on('tp.id_szkolenie', '=', 's.id');
        })
        ->leftJoinSub($testSub, 'tt', function ($join) {
            $join->on('tt.id_szkolenie', '=', 's.id');
        })
        ->select([
            's.id',
            's.status',
            's.szkolenie_dla_ko_id',
            DB::raw('COALESCE(tp.uczestnicy_count, 0) as uczestnicy_count'),
            DB::raw('COALESCE(tp.uczestnicy_obecni_count, 0) as uczestnicy_obecni_count'),
            DB::raw('CASE WHEN tt.test_id IS NULL THEN 0 ELSE 1 END as has_test'),
        ]);

    if ($materialsTableReady) {
        $materialsSub = DB::table('t056_szkolenie_materialy')
            ->select('id_szkolenie', DB::raw('COUNT(*) as materialy_count'));

        if ($materialsStatusColumnReady) {
            $materialsSub->where('status_aktywny', 1);
        }

        $materialsSub->groupBy('id_szkolenie');

        $trainingsQuery
            ->leftJoinSub($materialsSub, 'tm', function ($join) {
                $join->on('tm.id_szkolenie', '=', 's.id');
            })
            ->addSelect(DB::raw('COALESCE(tm.materialy_count, 0) as materialy_count'));
    } else {
        $trainingsQuery->addSelect(DB::raw('0 as materialy_count'));
    }

    $trainingRows = $trainingsQuery->get();

    $koStats = [];

    foreach ($trainingRows as $row) {
        $koId = (int) ($row->szkolenie_dla_ko_id ?? 0);

        if (!isset($koStats[$koId])) {
            $koModel = $koDictionary->get($koId);
            $koStats[$koId] = [
                'ko_id' => $koId,
                'ko_short' => trim((string) optional($koModel)->nazwa_skrot) !== '' ? (string) $koModel->nazwa_skrot : 'ORG',
                'ko_name' => trim((string) optional($koModel)->nazwa_pelna) !== '' ? (string) $koModel->nazwa_pelna : 'Brak przypisanej KO',
                'total' => 0,
                'status_1' => 0,
                'status_2' => 0,
                'status_3' => 0,
                'status_4' => 0,
                'uczestnicy_total' => 0,
                'uczestnicy_obecni_total' => 0,
                'with_test_total' => 0,
                'with_materials_total' => 0,
            ];
        }

        $statusCode = (int) ($row->status ?? 1);
        if (!in_array($statusCode, [1, 2, 3, 4], true)) {
            $statusCode = 1;
        }

        $participantsCount = (int) ($row->uczestnicy_count ?? 0);
        $participantsPresentCount = (int) ($row->uczestnicy_obecni_count ?? 0);
        $materialsCount = (int) ($row->materialy_count ?? 0);
        $hasTest = (int) ($row->has_test ?? 0) === 1;

        $koStats[$koId]['total']++;
        $koStats[$koId]['status_'.$statusCode]++;
        $koStats[$koId]['uczestnicy_total'] += $participantsCount;
        $koStats[$koId]['uczestnicy_obecni_total'] += $participantsPresentCount;
        if ($hasTest) {
            $koStats[$koId]['with_test_total']++;
        }
        if ($materialsCount > 0) {
            $koStats[$koId]['with_materials_total']++;
        }
    }

    $koRows = collect(array_values($koStats))
        ->filter(function (array $row) {
            return (int) ($row['total'] ?? 0) > 0;
        })
        ->sortByDesc('total')
        ->values();

    $orgSummary = [
        'total' => (int) $koRows->sum('total'),
        'status_1' => (int) $koRows->sum('status_1'),
        'status_2' => (int) $koRows->sum('status_2'),
        'status_3' => (int) $koRows->sum('status_3'),
        'status_4' => (int) $koRows->sum('status_4'),
        'uczestnicy_total' => (int) $koRows->sum('uczestnicy_total'),
        'uczestnicy_obecni_total' => (int) $koRows->sum('uczestnicy_obecni_total'),
        'with_test_total' => (int) $koRows->sum('with_test_total'),
        'with_materials_total' => (int) $koRows->sum('with_materials_total'),
    ];

    $chartLabels = $koRows->pluck('ko_short')->values();
    $chartStatus1 = $koRows->pluck('status_1')->values();
    $chartStatus2 = $koRows->pluck('status_2')->values();
    $chartStatus3 = $koRows->pluck('status_3')->values();
    $chartStatus4 = $koRows->pluck('status_4')->values();
@endphp