leezard/app/Http/Livewire/PresenceReport.php

<?php

namespace App\Http\Livewire;

use Livewire\Component;
use Illuminate\Support\Carbon;
use App\Http\Middleware\TenantMiddleware;

class PresenceReport extends Component
{
    public $records = [];
    public $date;

    public $courses = [];
    public $courts = [];
    public $instructors = [];
    public $motivations = [];

    public $course_name;
    public $from;
    public $to;
    public $court_id;
    public $instructor_id;
    public $search;

    public $court_name = '';
    public $instructor_name = '';

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

    public function mount()
    {
        $this->courts = \App\Models\Court::where('enabled', true)->orderBy('name')->get();
        $this->instructors = \App\Models\User::where('level', 2)->where('enabled', true)->orderBy('name')->get();
        $this->motivations = \App\Models\Motivation::where('enabled', true)->where('type', 'del')->orderBy('name')->get();

        $this->from = "00:00:00";
        $this->to = "23:59:59";
        $this->date = date("Y-m-d");

        setlocale(LC_ALL, 'it_IT');
    }

    public function render()
    {
        setlocale(LC_ALL, 'it_IT');

        $this->records = [];

        $fromDt = Carbon::parse($this->date . ' ' . ($this->from ?: '00:00:00'));
        $toDt = Carbon::parse($this->date . ' ' . ($this->to ?: '23:59:59'));

        $this->court_name = '';
        if (!empty($this->court_id)) {
            $court = $this->courts->firstWhere('id', (int)$this->court_id);
            $this->court_name = $court?->name ?? '';
        }

        $this->instructor_name = '';
        if (!empty($this->instructor_id)) {
            $instr = $this->instructors->firstWhere('id', (int)$this->instructor_id);
            $this->instructor_name = $instr?->name ?? '';
        }

        $calendars = \App\Models\Calendar::with(['course.level'])
            ->whereBetween('from', [$fromDt->toDateTimeString(), $toDt->toDateTimeString()])
            ->orderBy('from')
            ->get();

        if ($calendars->isEmpty()) {
            $this->courses = \App\Models\Calendar::orderBy('name')->groupBy('name')->pluck('name')->toArray();
            return view('livewire.presence_report');
        }

        $calendarIds = $calendars->pluck('id')->all();

        $presencesAll = \App\Models\Presence::with([
            'member:id,first_name,last_name',
            'court:id,name',
            'user:id,name',
            'instructor:id,name',
            'motivation:id,name',
            'motivationCourse:id,name,course_level_id',
            'motivationCourse.level:id,name',
        ])
            ->whereIn('calendar_id', $calendarIds)
            ->when(!empty($this->court_id), fn($q) => $q->where('court_id', (int)$this->court_id))
            ->when(!empty($this->instructor_id), function ($q) {
                $iid = (int)$this->instructor_id;
                $q->where(function ($qq) use ($iid) {
                    $qq->where('instructor_id', $iid)->orWhere('user_id', $iid);
                });
            })
            ->when(!empty($this->search), function ($q) {
                $s = trim($this->search);
                $q->whereHas('member', function ($mq) use ($s) {
                    $mq->where(function ($qq) use ($s) {
                        $qq->whereRaw("CONCAT(TRIM(first_name), ' ', TRIM(last_name)) LIKE ?", ["%{$s}%"])
                            ->orWhereRaw("CONCAT(TRIM(last_name), ' ', TRIM(first_name)) LIKE ?", ["%{$s}%"]);
                    });
                });
            })
            ->get();

        $presenceByCalMember = [];
        $presencesByCalendar = [];

        foreach ($presencesAll as $p) {
            $presenceByCalMember[$p->calendar_id . '|' . $p->member_id] = $p;
            $presencesByCalendar[$p->calendar_id][] = $p;
        }

        $days = ['dom', 'lun', 'mar', 'mer', 'gio', 'ven', 'sab'];

        foreach ($calendars as $calendar) {
            $h = Carbon::parse($calendar->from)->format('H:i');
            $dow = (int) Carbon::parse($calendar->from)->format('w'); // 0..6
            $d = $days[$dow];

            $courseIds = \App\Models\Course::query()
                ->where('name', $calendar->name)
                ->where('date_from', '<=', $calendar->from)
                ->where('date_to', '>=', $calendar->to)
                ->when(!empty($this->course_name), fn($q) => $q->where('name', $this->course_name))
                ->pluck('id')
                ->toArray();

            if (empty($courseIds)) {
                continue;
            }

            $slotCourseIds = \App\Models\Course::query()
                ->whereIn('id', $courseIds)
                ->get(['id', 'when'])
                ->filter(fn($c) => $this->courseMatchesSlot($c->when, $d, $h))
                ->pluck('id')
                ->all();

            if (empty($slotCourseIds)) {
                continue;
            }

            $membersQuery = \App\Models\MemberCourse::query()
                ->whereIn('course_id', $slotCourseIds)
                ->whereDate('date_from', '<=', $calendar->from)
                ->whereDate('date_to', '>=', $calendar->from)
                ->with(['member', 'course.level']);

            if (!empty($this->search)) {
                $s = trim(mb_strtolower($this->search));
                $membersQuery->whereHas('member', function ($mq) use ($s) {
                    $mq->where(function ($qq) use ($s) {
                        $qq->whereRaw('LOWER(CONCAT(TRIM(first_name), " ", TRIM(last_name))) LIKE ?', ["%{$s}%"])
                            ->orWhereRaw('LOWER(CONCAT(TRIM(last_name), " ", TRIM(first_name))) LIKE ?', ["%{$s}%"]);
                    });
                });
            }

            $members = $membersQuery->get();

            $expectedMemberIds = [];

            foreach ($members as $mc) {
                $mid = $mc->member->id;
                $expectedMemberIds[] = $mid;

                $p = $presenceByCalMember[$calendar->id . '|' . $mid] ?? null;

                [$court, $instructor, $motivation, $motivation_course] = $this->presenceMeta($p);
                $status = $this->presenceStatusHtml($p, $calendar);

                $course_level = '';
                if ($mc->course && $mc->course->level) {
                    $course_level = trim($mc->course->level->name);
                }

                $this->records[$calendar->name][$h][] = [
                    'course_level' => $course_level,
                    'last_name' => $mc->member->last_name,
                    'first_name' => $mc->member->first_name,
                    'court' => $court,
                    'instructor' => $instructor,
                    'status' => $status,
                    'motivation' => $motivation,
                ];
            }

            $extras = $presencesByCalendar[$calendar->id] ?? [];

            foreach ($extras as $p) {
                if (in_array($p->member_id, $expectedMemberIds, true)) {
                    continue;
                }

                if (!empty($this->course_name)) {
                    $motCourseName = $p->motivationCourse?->name;
                    if (!$motCourseName || $motCourseName !== $this->course_name) {
                        continue;
                    }
                }

                [$court, $instructor, $motivation, $motivation_course] = $this->presenceMeta($p);
                $status = $this->presenceStatusHtml($p, $calendar);

                $course_level = '';
                if ($calendar->course && $calendar->course->level) {
                    $course_level = trim($calendar->course->level->name);
                }
                if ($motivation_course) {
                    $course_level = $motivation_course->level?->name;
                }

                $this->records[$calendar->name][$h][] = [
                    'course_level' => $course_level,
                    'last_name' => $p->member?->last_name ?? '',
                    'first_name' => $p->member?->first_name ?? '',
                    'court' => $court,
                    'instructor' => $instructor,
                    'status' => $status,
                    'motivation' => $motivation,
                ];
            }

            if (isset($this->records[$calendar->name][$h])) {
                usort($this->records[$calendar->name][$h], function ($a, $b) {
                    $course_level_compare = strcmp($a['course_level'], $b['course_level']);
                    if ($course_level_compare !== 0) return $course_level_compare;

                    $last_name_compare = strcmp($a['last_name'], $b['last_name']);
                    if ($last_name_compare !== 0) return $last_name_compare;

                    return strcmp($a['first_name'], $b['first_name']);
                });
            }
        }

        $this->courses = \App\Models\Calendar::orderBy('name')->groupBy('name')->pluck('name')->toArray();

        return view('livewire.presence_report');
    }

    private function courseMatchesSlot(?string $whenJson, string $day, string $hhmm): bool
    {
        if (!$whenJson) return false;

        $when = json_decode($whenJson, true);
        if (!is_array($when)) return false;

        foreach ($when as $period) {
            $days = $period['day'] ?? [];
            $from = $period['from'] ?? null;

            if (!$from || empty($days)) continue;

            $from = substr((string)$from, 0, 5);

            if ($from === $hhmm && in_array($day, $days, true)) {
                return true;
            }
        }

        return false;
    }

    protected function presenceStatusHtml($presence, $calendar): string
    {
        if ($calendar->status == 99) {
            return "<span class='fw-bold' style='color:gray'>Annullata</span>";
        }

        if ($presence) {
            if ((int)$presence->status === 99) {
                return "<span class='fw-bold' style='color:gray'>Annullata</span>";
            }
            return "<span class='fw-bold' style='color:#0c6197'>Presente</span>";
        }

        if (Carbon::now()->format('Ymd') > Carbon::parse($calendar->from)->format('Ymd')) {
            return "<span class='fw-bold' style='color:red'>Assente</span>";
        }

        return '';
    }

    protected function presenceMeta($presence): array
    {
        if (!$presence) return ['', '', '', null];

        $court = $presence->court?->name ?? '';

        $instructorParts = [
            $presence->user?->name ?? '',
            ($presence->instructor && $presence->user && $presence->instructor->id !== $presence->user->id)
                ? $presence->instructor->name
                : ($presence->instructor?->name ?? ''),
        ];
        $instructor = implode(', ', array_values(array_filter(array_unique($instructorParts))));

        $motivation = $presence->motivation?->name ?? '';

        $motivation_course = $presence->motivationCourse ?? null;

        return [$court, $instructor, $motivation, $motivation_course];
    }

    public function prev()
    {
        $this->date = date("Y-m-d", strtotime("-1 day", strtotime($this->date)));
    }

    public function next()
    {
        $this->date = date("Y-m-d", strtotime("+1 day", strtotime($this->date)));
    }

    public function today()
    {
        $this->date = date("Y-m-d");
    }
}