iao_team/app/Http/Livewire/Reports.php

<?php

namespace App\Http\Livewire;

use Livewire\Component;
use Illuminate\Support\Facades\Auth;
use Carbon\Carbon;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use App\Models\Course;
use App\Models\MemberCard;
use App\Http\Middleware\TenantMiddleware;

class Reports extends Component
{
    public $type = 'anagrafica';
    public $seasonFilter;

    public $courses = [];
    public $selectedCourse = null;

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

    }

    public function mount()
    {
        if (Auth::user()->level != env('LEVEL_ADMIN', 0))
            return redirect()->to('/reports');

        if (isset($_GET["type"]))
            $this->type = $_GET["type"];

        $this->seasonFilter = $this->getCurrentSeason();
        $this->courses = $this->getCoursesForSelect();
    }

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


    private function getCurrentSeason()
    {
        $now = Carbon::now();
        $currentYear = $now->year;
        if ($now->month >= 9) {
            return $currentYear . '-' . ($currentYear + 1);
        } else {
            return ($currentYear - 1) . '-' . $currentYear;
        }
    }

    public function getAvailableSeasons()
    {
        $seasons = [];
        $currentYear = Carbon::now()->year;
        $startYear = 2023;

        $endYear = Carbon::now()->month >= 9 ? $currentYear + 1 : $currentYear;

        for ($year = $startYear; $year < $endYear; $year++) {
            $seasons[] = $year . '-' . ($year + 1);
        }

        return array_reverse($seasons);
    }


    private function parseSeason($season)
    {
        $parts = explode('-', $season);
        return [
            'start_year' => (int)$parts[0],
            'end_year' => (int)$parts[1]
        ];
    }


    private function getSeasonDateRange($season)
    {
        $years = $this->parseSeason($season);

        return [
            'start' => Carbon::create($years['start_year'], 9, 1),
            'end' => Carbon::create($years['end_year'], 8, 31)
        ];
    }

    public function setSelectedCourse($courseId)
    {
        $this->selectedCourse = $courseId;
        Log::info('Selected course set to: ' . $courseId);
        return $this->getCourseMonthlyEarnings();
    }

    public function getTesseratiData()
    {
        $endYear = $this->parseSeason($this->seasonFilter)['end_year'];
        return self::getMemberCountChartData($endYear);
    }

    public function change($type)
    {
        $this->type = $type;
    }

    public function updateCharts()
    {
        $this->courses = $this->getCoursesForSelect();
        $this->emit('chartsUpdated');
        $this->dispatchBrowserEvent('chartsUpdated');
    }

    public function updateCourseChart()
    {
        $this->emit('chartsUpdated');
        $this->dispatchBrowserEvent('chartsUpdated');
    }

    public function updatedSeasonFilter()
    {
        $this->courses = $this->getCoursesForSelect();
        $this->emit('chartsUpdated');
        $this->dispatchBrowserEvent('chartsUpdated');
    }

    public function setSeasonFilter($season)
    {
        $this->seasonFilter = $season;
    }

    protected function setupTenantConnection()
    {
        $user = auth()->user();

        config(['database.connections.tenant' => [
            'driver' => 'mysql',
            'host' => '127.0.0.1',
            'port' => '3306',
            'database' => $user->tenant_database,
            'username' => $user->tenant_username,
            'password' => $user->tenant_password,
        ]]);

        config(['database.default' => 'tenant']);
        DB::purge('tenant');
        DB::reconnect('tenant');
    }
    public function getMonthlyTotals()
    {
        Log::info('=== getMonthlyTotals called ===');
        Log::info('Current seasonFilter: ' . $this->seasonFilter);

        $dateRange = $this->getSeasonDateRange($this->seasonFilter);

        Log::info('Date range start: ' . $dateRange['start']);
        Log::info('Date range end: ' . $dateRange['end']);
        $monthOrder = [9, 10, 11, 12, 1, 2, 3, 4, 5, 6, 7, 8];
        $monthNames = ['Set', 'Ott', 'Nov', 'Dic', 'Gen', 'Feb', 'Mar', 'Apr', 'Mag', 'Giu', 'Lug', 'Ago'];

        $incomeData = array_fill(0, 12, 0);
        $expenseData = array_fill(0, 12, 0);

        $pairs = [];
        $start = $dateRange['start']->copy()->startOfMonth();
        $end = $dateRange['end']->copy()->startOfMonth();
        foreach (\Carbon\CarbonPeriod::create($start, '1 month', $end) as $d) {
            $pairs[] = (string)$d->month . "-" . (string)$d->year;
        }
        $pairs = implode("|", $pairs);

        $excluded_causals = \App\Models\Causal::where('no_records', true)->orWhere('money', true)->pluck('id')->toArray();
        $excluded_members = \App\Models\Member::where('exclude_from_records', true)->pluck('id')->toArray();

        $incomeQuery = DB::table('records')
            ->join('records_rows', 'records.id', '=', 'records_rows.record_id')
            ->join('causals', function ($join) {
                $join->on('causals.id', '=', 'records_rows.causal_id')
                    ->where(function ($query) {
                        $query->where('causals.no_reports', 0)
                            ->orWhereNull('causals.no_reports');
                    });
            })
            ->whereRaw('records_rows.when REGEXP ?', [$pairs])
            ->whereNotIn('records_rows.causal_id', $excluded_causals)
            ->whereNotIn('member_id', $excluded_members)
            ->where(function ($query) {
                $query->where('deleted', false)->orWhere('deleted', null);
            })
            ->where(function ($query) {
                $query->where('financial_movement', false)->orWhere('financial_movement', null);
            })
            ->where('records.type', 'IN');
        $incomeRecords = $incomeQuery->get();

        foreach ($incomeRecords as $record) {
            $total_months = count(json_decode($record->when, true));
            $matches = [];
            if (!preg_match_all("/$pairs/", $record->when, $matches)) continue;

            $amount = $record->amount;
            $amount += getVatValue($amount, $record->vat_id);

            foreach ($matches[0] as $match) {
                $m = explode("-", $match)[0];

                $monthIndex = array_search($m, $monthOrder);
                if ($monthIndex !== false) {
                    $incomeData[$monthIndex] += $amount / $total_months;
                }
            }
        }

        $expenseQuery = DB::table('records')
            ->join('records_rows', 'records.id', '=', 'records_rows.record_id')
            ->join('causals', function ($join) {
                $join->on('causals.id', '=', 'records_rows.causal_id')
                    ->where(function ($query) {
                        $query->where('causals.no_reports', 0)
                            ->orWhereNull('causals.no_reports');
                    });
            })
            ->whereRaw('records_rows.when REGEXP ?', [$pairs])
            ->whereNotIn('records_rows.causal_id', $excluded_causals)
            ->whereNotIn('member_id', $excluded_members)
            ->where(function ($query) {
                $query->where('deleted', false)->orWhere('deleted', null);
            })
            ->where(function ($query) {
                $query->where('financial_movement', false)->orWhere('financial_movement', null);
            })
            ->where('records.type', 'OUT');
        $expenseRecords = $expenseQuery->get();

        foreach ($expenseRecords as $record) {
            $total_months = count(json_decode($record->when, true));
            $matches = [];
            if (!preg_match_all("/$pairs/", $record->when, $matches)) continue;

            $amount = $record->amount;
            $amount += getVatValue($amount, $record->vat_id);

            foreach ($matches[0] as $match) {
                $m = explode("-", $match)[0];

                $monthIndex = array_search($m, $monthOrder);
                if ($monthIndex !== false) {
                    $expenseData[$monthIndex] += $amount / $total_months;
                }
            }
        }

        Log::info('Income data: ' . json_encode($incomeData));
        Log::info('Expense data: ' . json_encode($expenseData));

        return [
            'labels' => $monthNames,
            'datasets' => [
                [
                    'label' => 'Entrate',
                    'data' => $incomeData,
                    'backgroundColor' => 'rgba(54, 162, 235, 0.5)'
                ],
                [
                    'label' => 'Uscite',
                    'data' => $expenseData,
                    'backgroundColor' => 'rgba(255, 99, 132, 0.5)'
                ],
            ]
        ];
    }

    public function getYearlyTotals()
    {
        Log::info('=== getyearlyTotals called ===');

        $excluded_causals = \App\Models\Causal::where('no_records', true)->orWhere('money', true)->pluck('id')->toArray();
        $excluded_members = \App\Models\Member::where('exclude_from_records', true)->pluck('id')->toArray();

        $incomeData = [];
        $expenseData = [];

        $years = range(2023, now()->year);
        $years_label = array_map(function ($year) {
            return $year . "/" . ($year + 1);
        }, $years);
        foreach ($years as $index => $year) {
            $incomeData[$index] = 0;
            $expenseData[$index] = 0;

            $next_year = $year + 1;

            $pairs = [];
            $start = Carbon::createFromFormat("Y-m-d", "$year-09-01")->startOfMonth();
            $end = Carbon::createFromFormat("Y-m-d", "$next_year-08-31")->startOfMonth();
            foreach (\Carbon\CarbonPeriod::create($start, '1 month', $end) as $d) {
                $pairs[] = (string)$d->month . "-" . (string)$d->year;
            }
            $pairs = implode("|", $pairs);

            $incomeQuery = DB::table('records')
                ->join('records_rows', 'records.id', '=', 'records_rows.record_id')
                ->join('causals', function ($join) {
                    $join->on('causals.id', '=', 'records_rows.causal_id')
                        ->where(function ($query) {
                            $query->where('causals.no_reports', 0)
                                ->orWhereNull('causals.no_reports');
                        });
                })
                ->whereRaw('records_rows.when REGEXP ?', [$pairs])
                ->whereNotIn('records_rows.causal_id', $excluded_causals)
                ->whereNotIn('member_id', $excluded_members)
                ->where(function ($query) {
                    $query->where('deleted', false)->orWhere('deleted', null);
                })
                ->where(function ($query) {
                    $query->where('financial_movement', false)->orWhere('financial_movement', null);
                })
                ->where('records.type', 'IN');
            $incomeRecords = $incomeQuery->get();

            foreach ($incomeRecords as $record) {
                $total_months = count(json_decode($record->when, true));
                $matches = [];
                if (!preg_match_all("/$pairs/", $record->when, $matches)) continue;

                $matching_months = count($matches[0]);
                $amount = $record->amount;
                $amount += getVatValue($amount, $record->vat_id);
                $amount *= ($matching_months / $total_months);
                
                $incomeData[$index] += $amount;
            }

            $expenseQuery = DB::table('records')
                ->join('records_rows', 'records.id', '=', 'records_rows.record_id')
                ->join('causals', function ($join) {
                    $join->on('causals.id', '=', 'records_rows.causal_id')
                        ->where(function ($query) {
                            $query->where('causals.no_reports', 0)
                                ->orWhereNull('causals.no_reports');
                        });
                })
                ->whereRaw('records_rows.when REGEXP ?', [$pairs])
                ->whereNotIn('records_rows.causal_id', $excluded_causals)
                ->whereNotIn('member_id', $excluded_members)
                ->where(function ($query) {
                    $query->where('deleted', false)->orWhere('deleted', null);
                })
                ->where(function ($query) {
                    $query->where('financial_movement', false)->orWhere('financial_movement', null);
                })
                ->where('records.type', 'OUT');
            $expenseRecords = $expenseQuery->get();

            foreach ($expenseRecords as $record) {
                $total_months = count(json_decode($record->when, true));
                $matches = [];
                if (!preg_match_all("/$pairs/", $record->when, $matches)) continue;

                $matching_months = count($matches[0]);
                $amount = $record->amount;
                $amount += getVatValue($amount, $record->vat_id);
                $amount *= ($matching_months / $total_months);
                
                $expenseData[$index] += $amount;
            }
        }

        return [
            'labels' => $years_label,
            'datasets' => [
                [
                    'label' => 'Entrate',
                    'data' => $incomeData,
                    'backgroundColor' => 'rgba(54, 162, 235, 0.5)',
                ],
                [
                    'label' => 'Uscite',
                    'data' => $expenseData,
                    'backgroundColor' => 'rgba(255, 99, 132, 0.5)',
                ],
            ],
        ];
    }

    public function getYearlySummary()
    {
        $dateRange = $this->getSeasonDateRange($this->seasonFilter);
        Log::info('=== getyearlyTotals called ===');

        $excluded_causals = \App\Models\Causal::where('no_records', true)->orWhere('money', true)->pluck('id')->toArray();
        $excluded_members = \App\Models\Member::where('exclude_from_records', true)->pluck('id')->toArray();

        $pairs = [];
        $start = $dateRange['start']->copy()->startOfMonth();
        $end = $dateRange['end']->copy()->startOfMonth();
        foreach (\Carbon\CarbonPeriod::create($start, '1 month', $end) as $d) {
            $pairs[] = (string)$d->month . "-" . (string)$d->year;
        }
        $pairs = implode("|", $pairs);

        $totalIncome = 0;
        $totalExpenses = 0;

        $incomeQuery = DB::table('records')
            ->join('records_rows', 'records.id', '=', 'records_rows.record_id')
            ->join('causals', function ($join) {
                $join->on('causals.id', '=', 'records_rows.causal_id')
                    ->where(function ($query) {
                        $query->where('causals.no_reports', 0)
                            ->orWhereNull('causals.no_reports');
                    });
            })
            ->whereRaw('records_rows.when REGEXP ?', [$pairs])
            ->whereNotIn('records_rows.causal_id', $excluded_causals)
            ->whereNotIn('member_id', $excluded_members)
            ->where(function ($query) {
                $query->where('deleted', false)->orWhere('deleted', null);
            })
            ->where(function ($query) {
                $query->where('financial_movement', false)->orWhere('financial_movement', null);
            })
            ->where('records.type', 'IN');
        $incomeRecords = $incomeQuery->get();

        foreach ($incomeRecords as $record) {
            $total_months = count(json_decode($record->when, true));
            $matches = [];
            if (!preg_match_all("/$pairs/", $record->when, $matches)) continue;

            $matching_months = count($matches[0]);
            $amount = $record->amount;
            $amount += getVatValue($amount, $record->vat_id);
            $amount *= ($matching_months / $total_months);
                
            $totalIncome += $amount;
        }

        $expenseQuery = DB::table('records')
            ->join('records_rows', 'records.id', '=', 'records_rows.record_id')
            ->join('causals', function ($join) {
                $join->on('causals.id', '=', 'records_rows.causal_id')
                    ->where(function ($query) {
                        $query->where('causals.no_reports', 0)
                            ->orWhereNull('causals.no_reports');
                    });
            })
            ->whereRaw('records_rows.when REGEXP ?', [$pairs])
            ->whereNotIn('records_rows.causal_id', $excluded_causals)
            ->whereNotIn('member_id', $excluded_members)
            ->where(function ($query) {
                $query->where('deleted', false)->orWhere('deleted', null);
            })
            ->where(function ($query) {
                $query->where('financial_movement', false)->orWhere('financial_movement', null);
            })
            ->where('records.type', 'OUT');
        $expenseRecords = $expenseQuery->get();

        foreach ($expenseRecords as $record) {
            $total_months = count(json_decode($record->when, true));
            $matches = [];
            if (!preg_match_all("/$pairs/", $record->when, $matches)) continue;

            $matching_months = count($matches[0]);
            $amount = $record->amount;
            $amount += getVatValue($amount, $record->vat_id);
            $amount *= ($matching_months / $total_months);
                
            $totalExpenses += $amount;
        }

        $delta = $totalIncome - $totalExpenses;

        return [
            'totalIncome' => $totalIncome,
            'totalExpenses' => $totalExpenses,
            'delta' => $delta
        ];
    }

    public function getTopCausalsByAmount($limit = 10)
    {
        $dateRange = $this->getSeasonDateRange($this->seasonFilter);

        $query = DB::table('records_rows')
            ->join('records', 'records_rows.record_id', '=', 'records.id')
            ->join('causals', 'records_rows.causal_id', '=', 'causals.id')
            ->whereBetween('records.date', [$dateRange['start'], $dateRange['end']]);

        $query->where('records.type', 'IN');

        Log::info('Query: ' . $query->toSql());

        $causals = $query->select(
            'causals.id',
            'causals.name',
            'causals.parent_id',
            DB::raw('SUM(records_rows.amount) as total_amount')
        )
            ->where(function ($query) {
                $query->where('causals.no_reports', '=', '0')
                    ->orWhereNull('causals.no_reports');
            })
            ->groupBy('causals.id', 'causals.name', 'causals.parent_id')
            ->orderBy('total_amount', 'desc')
            ->limit($limit)
            ->get();

        Log::info('Causals: ' . json_encode($causals));

        $inData = [];

        foreach ($causals as $causal) {
            $tempCausal = new \App\Models\Causal();
            $tempCausal->id = $causal->id;
            $tempCausal->name = $causal->name;
            $tempCausal->parent_id = $causal->parent_id;

            $treeName = $tempCausal->getTree();

            //$displayName = strlen($treeName) > 30 ? substr($treeName, 0, 27) . '...' : $treeName;
            $displayName = $treeName;
            $inData[] = [
                'label' => $displayName,
                'value' => $causal->total_amount,
                'fullName' => $treeName
            ];
        }

        usort($inData, function ($a, $b) {
            return $b['value'] <=> $a['value'];
        });

        $inData = array_slice($inData, 0, $limit);

        return [
            'inLabels' => array_column($inData, 'label'),
            'inData' => $inData,
            'datasets' => [
                [
                    'label' => 'Entrate per Causale',
                    'data' => array_column($inData, 'value'),
                ]
            ]
        ];
    }

    public function getCoursesForSelect()
    {
        $seasonYears = $this->parseSeason($this->seasonFilter);

        Log::info('Getting courses for season: ' . $this->seasonFilter);
        Log::info('Season years: ' . json_encode($seasonYears));

        $courses = Course::with(['level', 'frequency'])
            ->where('active', true)
            ->where(function ($query) use ($seasonYears) {
                $query->where('year', $this->seasonFilter)
                    ->orWhere('year', 'like', '%' . $seasonYears['start_year'] . '-' . $seasonYears['end_year'] . '%')
                    ->orWhere('year', 'like', '%' . $seasonYears['start_year'] . '%')
                    ->orWhere('year', 'like', '%' . $seasonYears['end_year'] . '%');
            })
            ->orderBy('name')
            ->get()
            ->filter(function ($course) use ($seasonYears) {
                $courseYear = $course->year;

                if ($courseYear === $this->seasonFilter) {
                    return true;
                }

                if (
                    str_contains($courseYear, $seasonYears['start_year']) &&
                    str_contains($courseYear, $seasonYears['end_year'])
                ) {
                    return true;
                }

                if ($courseYear == $seasonYears['start_year'] || $courseYear == $seasonYears['end_year']) {
                    return true;
                }

                return false;
            })
            ->map(function ($course) {
                Log::info('Processing course: ' . $course->name . ' (ID: ' . $course->id . ')' . $course);

                $levelName = is_object($course->level) ? $course->level->name : 'No Level';
                $typeName = ''; //$course->getFormattedTypeField();
                $frequencyName = is_object($course->frequency) ? $course->frequency->name : 'No Frequency';
                $year = $course->year ?? '';

                return [
                    'id' => $course->id,
                    'name' => $course->name,
                    'full_name' => "{$course->name} - {$levelName} - {$typeName} - {$frequencyName} ({$year})",
                    'level_name' => $levelName,
                    'type_name' => $typeName,
                    'frequency_name' => $frequencyName,
                    'year' => $year
                ];
            })->values()->toArray();

        Log::info('Found ' . count($courses) . ' courses for season ' . $this->seasonFilter);

        return $courses;
    }

    public function getMonthlyTotalsForSeason($season)
    {
        $originalSeason = $this->seasonFilter;
        $this->seasonFilter = $season;

        $result = $this->getMonthlyTotals();

        $this->seasonFilter = $originalSeason;
        return $result;
    }


    public function getTopCausalsByAmountForSeason($season, $limit = 10)
    {
        $originalSeason = $this->seasonFilter;
        $this->seasonFilter = $season;

        $result = $this->getTopCausalsByAmount($limit);

        $this->seasonFilter = $originalSeason;
        return $result;
    }

    public function getTesseratiDataForSeason($season)
    {
        $originalSeason = $this->seasonFilter;
        $this->seasonFilter = $season;

        $result = $this->getTesseratiData();

        $this->seasonFilter = $originalSeason;
        return $result;
    }

    public function updatedSelectedCourse()
    {
        Log::info('updatedSelectedCourse called with: ' . $this->selectedCourse);
        if ($this->selectedCourse) {
            $this->emit('courseSelected', $this->selectedCourse);
            Log::info('Event emitted with course ID: ' . $this->selectedCourse);
        }
    }
    public function getCourseData($courseId)
    {
        $this->selectedCourse = $courseId;
        return $this->getCourseMonthlyEarnings($courseId);
    }
    public function getCourseMonthlyEarnings($courseId = null)
    {
        $courseId = $courseId ?? $this->selectedCourse;
        Log::info('Getting earnings for course ID: ' . $courseId);

        if (!$courseId) {
            return [
                'labels' => [],
                'datasets' => [],
                'tableData' => [],
                'isEmpty' => true,
                'message' => 'Seleziona un corso per visualizzare i dati'
            ];
        }

        $monthOrder = [9, 10, 11, 12, 1, 2, 3, 4, 5, 6, 7, 8];
        $monthNames = [
            9 => 'Set',
            10 => 'Ott',
            11 => 'Nov',
            12 => 'Dic',
            1 => 'Gen',
            2 => 'Feb',
            3 => 'Mar',
            4 => 'Apr',
            5 => 'Mag',
            6 => 'Giu',
            7 => 'Lug',
            8 => 'Ago'
        ];
        $monthNamesExtended = [
            0 => 'Settembre',
            1 => 'Ottobre',
            2 => 'Novembre',
            3 => 'Dicembre',
            4 => 'Gennaio',
            5 => 'Febbraio',
            6 => 'Marzo',
            7 => 'Aprile',
            8 => 'Maggio',
            9 => 'Giugno',
            10 => 'Luglio',
            11 => 'Agosto'
        ];

        $monthlyData = [];
        foreach ($monthOrder as $i) {
            $monthlyData[$i] = [
                'earned' => 0,
                'total' => 0,
                'suspended' => 0,
                'participants' => 0
            ];
        }

        $member_courses = \App\Models\MemberCourse::where('course_id', $courseId)->get();

        /*$rates = \App\Models\Rate::whereHas('member_course', function ($query) use ($courseId) {
            $query->where('course_id', $courseId);
        })->with('member_course')->get();*/

        if ($member_courses->isEmpty()) {
            return [
                'labels' => [],
                'datasets' => [],
                'tableData' => [],
                'isEmpty' => true,
                'message' => 'Nessun dato disponibile per questo corso nella stagione ' . $this->seasonFilter
            ];
        }

        $hasData = false;

        foreach ($member_courses as $member_course) {

            $totalPrice = (float)($member_course->price ?? 0);
            //$totalPrice = (float)($rate->price ?? 0);

            if ($member_course->months) {
                $monthsData = json_decode($member_course->months, true);

                if (is_array($monthsData) && count($monthsData) > 0) {



                    $pricePerMonth = $totalPrice; //  / count($monthsData);

                    foreach ($monthsData as $month) {
                        $monthNumber = (int)$month["m"];

                        if (isset($monthlyData[$monthNumber])) {
                            $monthlyData[$monthNumber]['total'] += $pricePerMonth;
                            $monthlyData[$monthNumber]['participants']++;
                            $hasData = true;

                            //if (!is_null($rate->record_id) && $rate->record_id !== '') {
                            if ($month["status"] == 1) {
                                $monthlyData[$monthNumber]['participants']--;
                                $monthlyData[$monthNumber]['earned'] += $pricePerMonth;
                            }
                            // pagamenti sospesi
                            if ($month["status"] == 2) {
                                $monthlyData[$monthNumber]['participants']--;
                                $monthlyData[$monthNumber]['total'] -= $pricePerMonth;
                                $monthlyData[$monthNumber]['suspended']++;
                            }
                        }
                    }
                }
            }
        }

        if (!$hasData) {
            return [
                'labels' => [],
                'datasets' => [],
                'tableData' => [],
                'isEmpty' => true,
                'message' => 'Nessun pagamento registrato per questo corso nella stagione ' . $this->seasonFilter
            ];
        }

        $labels = [];
        $earnedData = [];
        $totalData = [];
        $participantData = [];
        $tableData = [];

        foreach ($monthOrder as $month) {
            $earned = round($monthlyData[$month]['earned'], 2);
            $total = round($monthlyData[$month]['total'], 2);
            $delta = max(0, $total - $earned);
            $participants = $monthlyData[$month]['participants'];
            $suspended = $monthlyData[$month]['suspended'];

            $labels[] = $monthNames[$month];
            $earnedData[] = $earned;
            $totalData[] = $total;
            $participantData[] = $participants;
            $suspendedData[] = $suspended;

            $percentage = $total > 0 ? round(($earned / $total) * 100, 1) : 0;

            $tableData[] = [
                'month' => $monthNames[$month],
                'participants' => $participants,
                'suspended' => $suspended,
                'earned' => $earned,
                'total' => $total,
                'delta' => $delta,
                'percentage' => $percentage
            ];
        }

        $daIncassareData = array_map(function ($tot, $inc) {
            return $tot - $inc;
        }, $totalData, $earnedData);


        return [
            'labels' => $labels,
            'datasets' => [
                [
                    'label' => 'TOT. INCASSATO',
                    'data' => $earnedData,
                    'participantData' => $participantData,
                    'suspendedData' => $suspendedData,
                    'monthNamesExtended' => $monthNamesExtended,
                ],
                [
                    'label' => 'TOT. DA INCASSARE',
                    'data' => $daIncassareData,
                    'participantData' => $participantData,
                    'suspendedData' => $suspendedData,
                    'monthNamesExtended' => $monthNamesExtended,
                ]
            ],
            'tableData' => $tableData,
            'isEmpty' => false
        ];
    }

    public static function getMemberCountChartData($endYear = null, $span = 5)
    {
        if ($endYear === null) {
            $endYear = date('Y');
        }

        $startYear = $endYear - $span + 1;

        $memberCards = MemberCard::select('member_id', 'expire_date', 'card_id')
            ->with('card:id,name')
            ->whereNotNull('expire_date')
            ->whereNotNull('member_id')
            ->whereNotNull('card_id')
            ->where('status', '!=', 'cancelled')
            ->whereRaw('YEAR(expire_date) >= ?', [$startYear])
            ->whereRaw('YEAR(expire_date) <= ?', [$endYear])
            ->get();

        $cardTypes = $memberCards->pluck('card.name')->unique()->filter()->sort()->values()->toArray();
        usort($cardTypes, function ($a, $b) {
            if ($a == $b) return 0;
            if ($a == "UISP") return 1;
            if ($b == "UISP") return 1;

            return strcmp($a, $b);
        });

        $seasonCounts = [];
        $seasonCardCounts = [];

        for ($year = $startYear; $year <= $endYear; $year++) {
            $seasonPeriod = ($year - 1) . '-' . $year;
            $seasonCounts[$seasonPeriod] = [];
            $seasonCardCounts[$seasonPeriod] = [];

            foreach ($cardTypes as $cardType) {
                $seasonCardCounts[$seasonPeriod][$cardType] = [];
            }
        }

        foreach ($memberCards as $card) {
            $expireYear = date('Y', strtotime($card->expire_date));
            $expireMonth = date('n', strtotime($card->expire_date));

            if ($expireMonth >= 9) {
                $seasonPeriod = $expireYear . '-' . ($expireYear + 1);
            } else {
                $seasonPeriod = ($expireYear - 1) . '-' . $expireYear;
            }

            if (isset($seasonCounts[$seasonPeriod])) {
                $seasonCounts[$seasonPeriod][$card->member_id] = true;

                $cardTypeName = $card->card->name ?? 'Unknown';
                if (isset($seasonCardCounts[$seasonPeriod][$cardTypeName])) {
                    $seasonCardCounts[$seasonPeriod][$cardTypeName][$card->member_id] = true;
                }
            }
        }

        $seasonLabels = [];
        $memberCountData = [];
        $cardTypeDatasets = [];

        $colors = [
            'rgba(255, 99, 132, 0.2)',
            'rgba(54, 162, 235, 0.2)',
            'rgba(255, 205, 86, 0.2)',
            'rgba(75, 192, 192, 0.2)',
            'rgba(153, 102, 255, 0.2)',
            'rgba(255, 159, 64, 0.2)',
            'rgba(199, 199, 199, 0.2)',
            'rgba(83, 102, 255, 0.2)',
        ];

        $borderColors = [
            'rgba(255, 99, 132, 1)',
            'rgba(54, 162, 235, 1)',
            'rgba(255, 205, 86, 1)',
            'rgba(75, 192, 192, 1)',
            'rgba(153, 102, 255, 1)',
            'rgba(255, 159, 64, 1)',
            'rgba(199, 199, 199, 1)',
            'rgba(83, 102, 255, 1)',
        ];

        foreach ($cardTypes as $index => $cardType) {
            $cardTypeDatasets[$cardType] = [
                'label' => $cardType,
                'data' => [],
                'backgroundColor' => $colors[$index % count($colors)],
                'borderColor' => $borderColors[$index % count($borderColors)],
                'borderWidth' => 2,
                'pointBackgroundColor' => $borderColors[$index % count($borderColors)],
                'pointRadius' => 4,
                'tension' => 0.3,
                'fill' => true
            ];
            if ($cardType != "UISP") $cardTypeDatasets[$cardType]["hidden"] = true;
        }

        foreach ($seasonCounts as $seasonPeriod => $members) {
            $seasonLabels[] = $seasonPeriod;
            $memberCountData[] = count($members);

            foreach ($cardTypes as $cardType) {
                $cardTypeCount = isset($seasonCardCounts[$seasonPeriod][$cardType])
                    ? count($seasonCardCounts[$seasonPeriod][$cardType])
                    : 0;
                $cardTypeDatasets[$cardType]['data'][] = $cardTypeCount;
            }
        }

        $datasets = [
            [
                'label' => 'Totale Membri Tesserati',
                'data' => $memberCountData,
                'backgroundColor' => '#7738fa',
                'borderColor' => '#7738fa',
                'borderWidth' => 3,
                'pointBackgroundColor' => '#7738fa',
                'pointRadius' => 6,
                'tension' => 0.3,
                'fill' => true,
                'type' => 'line',
                'hidden' => true
            ]
        ];
        foreach ($cardTypeDatasets as $dataset) {
            $datasets[] = $dataset;
        }

        return [
            'labels' => $seasonLabels,
            'datasets' => $datasets
        ];
    }
}