leezard/app/Http/Livewire/DynamicReport.php

<?php

namespace App\Http\Livewire;

use App\Http\Middleware\TenantMiddleware;
use Livewire\Component;
use Carbon\Carbon;
use Carbon\CarbonPeriod;

class DynamicReport extends Component
{
    public $filters = [
        'courses' => [],
        'levels' => [],
        // 'types' => [],
        'seasons' => [],
        'months' => [],
    ];

    public $filter_options = [
        'courses' => [],
        'levels' => [],
        // 'types' => [],
        'seasons' => [],
        'months' => [],
    ];

    public $chart = [
        'labels' => [],
        'datasets' => [],
    ];

    public function boot()
    {
        app(TenantMiddleware::class)->setupTenantConnection();
    }

    public function mount()
    {
        $this->resetReport();
        $this->initFiltersOptions();
    }

    public function resetReport()
    {
        $this->chart = [
            'labels' => [],
            'datasets' => []
        ];
    }

    public function initFiltersOptions()
    {
        // courses
        $this->filter_options['courses'] = \App\Models\Course::query()
            ->select('name')
            ->where('enabled', true)
            ->whereNull('deleted_at')
            ->groupBy('name')
            ->orderBy('name')
            ->pluck('name')
            ->values()
            ->toArray();

        // levels
        $this->filter_options['levels'] = \App\Models\CourseLevel::query()
            ->where('enabled', true)
            ->orderBy('name')
            ->get(['id', 'name'])
            ->toArray();

        // types
        // $this->filter_options['types'] = \App\Models\CourseType::query()
        //     ->where('enabled', true)
        //     ->orderBy('name')
        //     ->get(['id', 'name'])
        //     ->toArray();

        // seasons
        $this->filter_options['seasons'] = $this->deriveSeasonOptionsFromCourseDateFrom();

        // months
        $this->filter_options['months'] = array_map(
            fn($m) => ['id' => $m, 'name' => $this->monthLabels()[$m]],
            $this->fiscalMonths()
        );
    }

    public function applyFilters()
    {
        $monthsToShow = $this->fiscalMonths();
        if (!empty($this->filters['months'])) {
            $selected = array_map('intval', $this->filters['months']);
            $monthsToShow = array_values(array_filter(
                $monthsToShow,
                fn($m) => in_array($m, $selected, true)
            ));
        }

        $labels = array_map(fn($m) => $this->monthLabels()[$m], $monthsToShow);

        $selectedSeasons = $this->filters['seasons'] ?? [];
        $selectedSeasons = array_values(array_filter($selectedSeasons));

        $rows = \App\Models\MemberCourse::query()
            ->join('courses', 'courses.id', '=', 'member_courses.course_id')
            ->leftJoin('course_levels', 'course_levels.id', '=', 'courses.course_level_id')
            // ->leftJoin('course_types', 'course_types.id', '=', 'courses.course_type_id')
            ->whereNull('courses.deleted_at')
            ->where('courses.enabled', 1)
            ->when(!empty($this->filters['courses']), fn($q) => $q->whereIn('courses.name', $this->filters['courses']))
            ->when(!empty($this->filters['levels']), fn($q) => $q->whereIn('courses.course_level_id', $this->filters['levels']))
            // ->when(!empty($this->filters['types']), fn($q) => $q->whereIn('courses.course_type_id', $this->filters['types']))
            ->select([
                'member_courses.id as member_course_id',
                'member_courses.member_id',
                'member_courses.months',
                'member_courses.date_from as member_date_from',
                'member_courses.date_to as member_date_to',
                'courses.id as course_id',
                'courses.name as course_name',
                'courses.date_from',
                'courses.date_to',
                'course_levels.name as level_name',
                // 'course_types.name as type_name',
            ])
            ->get();

        // dd($rows->toSql(), $rows->getBindings());

        $bucket = [];
        foreach ($rows as $r) {
            $seasonLabel = $this->fiscalSeasonFromDates($r->date_from, $r->date_to);

            if (!empty($selectedSeasons)) {
                if (!$seasonLabel || !in_array($seasonLabel, $selectedSeasons, true)) {
                    continue;
                }
            }

            $activeMonths = [];
            $member_date_from = Carbon::parse($r->member_date_from);
            $member_date_to = Carbon::parse($r->member_date_to);
            $period = CarbonPeriod::create($member_date_from->startOfMonth(), '1 month', $member_date_to->startOfMonth());
            foreach ($period as $date) {
                $activeMonths[] = $date->month;
            }
            if (empty($activeMonths)) continue;

            if (!empty($this->filters['months'])) {
                $activeMonths = array_values(array_intersect($activeMonths, array_map('intval', $this->filters['months'])));
                if (empty($activeMonths)) continue;
            }

            $seriesKey = $this->makeSeriesKey($r, $seasonLabel);

            foreach ($activeMonths as $m) {
                if (!in_array($m, $monthsToShow, true)) continue;
                $bucket[$seriesKey][$m][$r->member_course_id] = true;
            }
        }

        $datasets = [];
        foreach ($bucket as $seriesKey => $monthsMap) {
            $data = [];
            foreach ($monthsToShow as $m) {
                $data[] = isset($monthsMap[$m]) ? count($monthsMap[$m]) : 0;
            }
            $datasets[] = [
                'label' => $seriesKey,
                'data'  => $data,
            ];
        }

        $this->chart = [
            'labels'   => $labels,
            'datasets' => $datasets,
        ];

        $this->dispatchBrowserEvent('dynamic-report:updated', [
            'chart' => $this->chart,
        ]);
    }

    private function fiscalSeasonFromDates(?string $dateFrom, ?string $dateTo): ?string
    {
        if (!$dateFrom) return null;

        $from = \Carbon\Carbon::parse($dateFrom);
        $to   = $dateTo ? \Carbon\Carbon::parse($dateTo) : null;

        // sicurezza: se date_to esiste ed è prima di date_from, ignoriamo date_to
        if ($to && $to->lessThan($from)) {
            $to = null;
        }

        /**
         * Strategia:
         * - la stagione è determinata dal "cuore" del corso
         * - se date_to esiste, prendiamo il punto medio
         * - altrimenti usiamo date_from
         */
        if ($to) {
            $midTimestamp = (int) (($from->timestamp + $to->timestamp) / 2);
            $ref = \Carbon\Carbon::createFromTimestamp($midTimestamp);
        } else {
            $ref = $from;
        }

        $startYear = ($ref->month >= 9) ? $ref->year : ($ref->year - 1);

        return $startYear . '-' . ($startYear + 1);
    }


    private function makeSeriesKey($r, ?string $seasonLabel): string
    {
        $parts = [];

        if (!empty($this->filters['courses'])) $parts[] = $r->course_name ?? '';
        if (!empty($this->filters['levels']))  $parts[] = $r->level_name ?? '';
        // if (!empty($this->filters['types']))   $parts[] = $r->type_name ?? '';

        if (!empty($this->filters['seasons']) && $seasonLabel) $parts[] = $seasonLabel;

        if (empty(array_filter($parts))) {
            $parts[] = $r->course_name ?? 'Totale';
        }

        $parts = array_values(array_filter($parts, fn($p) => trim($p) !== ''));
        return implode(' - ', $parts);
    }

    private function deriveSeasonOptionsFromCourseDateFrom()
    {
        $dates = \App\Models\Course::query()
            ->where('enabled', 1)
            ->whereNull('deleted_at')
            ->whereNotNull('date_from')
            ->whereNotNull('date_to')
            ->pluck('date_to', 'date_from')
            ->all();

        $seasons = [];
        foreach ($dates as $d_from => $d_to) {
            $s = $this->fiscalSeasonFromDates($d_from, $d_to);
            if ($s) $seasons[$s] = true;
        }

        $out = array_keys($seasons);
        rsort($out);

        return $out;
    }

    private function fiscalMonths()
    {
        return [9, 10, 11, 12, 1, 2, 3, 4, 5, 6, 7, 8];
    }

    private function monthLabels()
    {
        return [
            1 => 'Gen',
            2 => 'Feb',
            3 => 'Mar',
            4 => 'Apr',
            5 => 'Mag',
            6 => 'Giu',
            7 => 'Lug',
            8 => 'Ago',
            9 => 'Set',
            10 => 'Ott',
            11 => 'Nov',
            12 => 'Dic',
        ];
    }

    public function render()
    {
        return view('livewire.dynamic_report');
    }
}