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;

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

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

    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');
    }

    /**
     * Get current season in format "2024-2025"
     */
    private function getCurrentSeason()
    {
        $now = Carbon::now();
        $currentYear = $now->year;

        // If we're in September-December, season is current year to next year
        // If we're in January-August, season is previous year to current year
        if ($now->month >= 9) {
            return $currentYear . '-' . ($currentYear + 1);
        } else {
            return ($currentYear - 1) . '-' . $currentYear;
        }
    }

    /**
     * Get available seasons for dropdown
     */
    public function getAvailableSeasons()
    {
        $seasons = [];
        $currentYear = Carbon::now()->year;
        $startYear = 2020; // Adjust based on your data

        // If current month is September or later, include next season
        $endYear = Carbon::now()->month >= 9 ? $currentYear + 1 : $currentYear;

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

        return array_reverse($seasons); // Most recent first
    }

    /**
     * Parse season string to get start and end years
     */
    private function parseSeason($season)
    {
        $parts = explode('-', $season);
        return [
            'start_year' => (int)$parts[0],
            'end_year' => (int)$parts[1]
        ];
    }

    /**
     * Get date range for a season (September 1st to August 31st)
     */
    private function getSeasonDateRange($season)
    {
        $years = $this->parseSeason($season);

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

    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;
    }

public function getMonthlyTotals()
{
    $dateRange = $this->getSeasonDateRange($this->seasonFilter);
    Log::info('Getting monthly totals for season: ' . $this->seasonFilter);

    $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);
    Log::info('Date range: ' . $dateRange['start'] . ' to ' . $dateRange['end']);

    // Get income records with detailed logging
    $incomeRecords = DB::table('records')
        ->join('records_rows', 'records.id', '=', 'records_rows.record_id')
        ->whereBetween('records.date', [$dateRange['start'], $dateRange['end']])
        ->where('records.type', 'IN')
        ->select(DB::raw('MONTH(records.date) as month_num'), DB::raw('SUM(records_rows.amount) as total'))
        ->groupBy('month_num')
        ->get();

    Log::info('Income records found: ' . $incomeRecords->count());
    foreach ($incomeRecords as $record) {
        Log::info("Income - Month: {$record->month_num}, Total: {$record->total}");
    }

    // Get expense records with detailed logging
    $expenseRecords = DB::table('records')
        ->join('records_rows', 'records.id', '=', 'records_rows.record_id')
        ->whereBetween('records.date', [$dateRange['start'], $dateRange['end']])
        ->where('records.type', 'OUT')
        ->select(DB::raw('MONTH(records.date) as month_num'), DB::raw('SUM(records_rows.amount) as total'))
        ->groupBy('month_num')
        ->get();

    Log::info('Expense records found: ' . $expenseRecords->count());
    foreach ($expenseRecords as $record) {
        Log::info("Expense - Month: {$record->month_num}, Total: {$record->total}");
    }

    // Process income records
    foreach ($incomeRecords as $record) {
        $monthIndex = array_search($record->month_num, $monthOrder);
        if ($monthIndex !== false) {
            $incomeData[$monthIndex] = $record->total;

            // Special logging for May (month 5)
            if ($record->month_num == 5) {
                Log::info("MAY INCOME DETECTED:");
                Log::info("- Month number: {$record->month_num}");
                Log::info("- Total amount: {$record->total}");
                Log::info("- Array index: {$monthIndex}");
                Log::info("- Month name: Mag");

                // Get detailed May records for debugging
                $mayIncomeDetails = DB::table('records')
                    ->join('records_rows', 'records.id', '=', 'records_rows.record_id')
                    ->whereBetween('records.date', [$dateRange['start'], $dateRange['end']])
                    ->where('records.type', 'IN')
                    ->whereRaw('MONTH(records.date) = 5')
                    ->select('records.date', 'records_rows.amount')
                    ->get();

                Log::info("May income detail records count: " . $mayIncomeDetails->count());
                foreach ($mayIncomeDetails as $detail) {
                    Log::info("May record - Date: {$detail->date}, Amount: {$detail->amount}");
                }
            }
        }
    }

    // Process expense records
    foreach ($expenseRecords as $record) {
        $monthIndex = array_search($record->month_num, $monthOrder);
        if ($monthIndex !== false) {
            $expenseData[$monthIndex] = $record->total;

            // Special logging for May (month 5)
            if ($record->month_num == 5) {
                Log::info("MAY EXPENSE DETECTED:");
                Log::info("- Month number: {$record->month_num}");
                Log::info("- Total amount: {$record->total}");
                Log::info("- Array index: {$monthIndex}");
                Log::info("- Month name: Mag");

                // Get detailed May records for debugging
                $mayExpenseDetails = DB::table('records')
                    ->join('records_rows', 'records.id', '=', 'records_rows.record_id')
                    ->whereBetween('records.date', [$dateRange['start'], $dateRange['end']])
                    ->where('records.type', 'OUT')
                    ->whereRaw('MONTH(records.date) = 5')
                    ->select('records.date', 'records_rows.amount')
                    ->get();

                Log::info("May expense detail records count: " . $mayExpenseDetails->count());
                foreach ($mayExpenseDetails as $detail) {
                    Log::info("May record - Date: {$detail->date}, Amount: {$detail->amount}");
                }
            }
        }
    }

    // Log final arrays
    Log::info('Final income data array: ' . json_encode($incomeData));
    Log::info('Final expense data array: ' . json_encode($expenseData));

    // Specifically log May position (index 8 in our academic year order)
    Log::info("May position in chart:");
    Log::info("- Income for May (index 8): " . $incomeData[8]);
    Log::info("- Expense for May (index 8): " . $expenseData[8]);

    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 getYearlySummary()
    {
        $dateRange = $this->getSeasonDateRange($this->seasonFilter);

        $totalIncome = DB::table('records')
            ->join('records_rows', 'records.id', '=', 'records_rows.record_id')
            ->whereBetween('records.date', [$dateRange['start'], $dateRange['end']])
            ->where('records.type', 'IN')
            ->sum('records_rows.amount');

        $totalExpenses = DB::table('records')
            ->join('records_rows', 'records.id', '=', 'records_rows.record_id')
            ->whereBetween('records.date', [$dateRange['start'], $dateRange['end']])
            ->where('records.type', 'OUT')
            ->sum('records_rows.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')
        )
            ->groupBy('causals.id', 'causals.name', 'causals.parent_id')
            ->orderBy('total_amount', 'desc')
            ->limit($limit)
            ->get();

        Log::info('Causals: ' . json_encode(value: $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;

            $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);

        $courses = Course::with(['level', 'type', 'frequency'])
            ->where('active', true)
            ->where(function($query) use ($seasonYears) {
                $query->where('year', 'like', '%' . $seasonYears['start_year'] . '%')
                      ->orWhere('year', 'like', '%' . $seasonYears['end_year'] . '%')
                      ->orWhere('year', 'like', '%' . $this->seasonFilter . '%');
            })
            ->orderBy('name')
            ->get()
            ->map(function ($course) {
                $type = null;
                if (!empty($course->course_type_id)) {
                    $type = \App\Models\CourseType::find($course->course_type_id);
                    if ($type) {
                        $typeName = $type->name;
                    }
                }

                $levelName = is_object($course->level) ? $course->level->name : 'No Level';
                $typeName = is_object($type) ? $type->name : 'No Type';
                $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
                ];
            })->toArray();

        return $courses;
    }

    public function getCourseMonthlyEarnings()
    {
        $courseId = $this->selectedCourse;
        Log::info('Getting earnings for course ID: ' . $courseId);

        // September to August order for academic year
        $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'
        ];

        if (empty($courseId)) {
            return [
                'labels' => array_values($monthNames),
                'datasets' => [
                    [
                        'label' => 'Pagamenti Effettuati',
                        'backgroundColor' => 'rgba(0, 184, 148, 1)',
                        'data' => array_fill(0, 12, 0),
                        'type' => 'bar',
                        'order' => 3
                    ],
                    [
                        'label' => 'Pagamenti Attesi',
                        'backgroundColor' => 'transparent',
                        'borderColor' => 'rgba(48, 51, 107, 1)',
                        'borderWidth' => 3,
                        'pointBackgroundColor' => 'rgba(48, 51, 107, 1)',
                        'pointRadius' => 5,
                        'data' => array_fill(0, 12, 0),
                        'type' => 'line',
                        'tension' => 0.2,
                        'order' => 2
                    ]
                ]
            ];
        }

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

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

        foreach ($memberCourses as $memberCourse) {
            $price = (float)($memberCourse->price ?? 0);

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

                if (is_array($monthsData)) {
                    foreach ($monthsData as $monthData) {
                        $month = $monthData['m'] ?? null;
                        $status = $monthData['status'] ?? '';

                        if ($month !== null && isset($monthlyData[$month])) {
                            $monthlyData[$month]['total'] += $price;

                            if ($status === 1) {
                                $monthlyData[$month]['earned'] += $price;
                            }

                            $monthlyData[$month]['participants']++;
                        }
                    }
                }
            }
        }

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

        foreach ($monthOrder as $month) {
            $labels[] = $monthNames[$month];
            $earnedData[] = round($monthlyData[$month]['earned'], 2);
            $totalData[] = round($monthlyData[$month]['total'], 2);
            $participantData[] = $monthlyData[$month]['participants'];
            $missingData[] = round($monthlyData[$month]['total'] - $monthlyData[$month]['earned'], 2);
        }

        return [
            'labels' => $labels,
            'datasets' => [
                [
                    'label' => 'Pagamenti Effettuati',
                    'backgroundColor' => 'rgba(0, 184, 148, 1)',
                    'data' => $earnedData,
                    'type' => 'bar',
                    'order' => 3
                ],
                [
                    'label' => 'Pagamenti Attesi',
                    'backgroundColor' => 'transparent',
                    'borderColor' => 'rgba(48, 51, 107, 1)',
                    'borderWidth' => 3,
                    'pointBackgroundColor' => 'rgba(48, 51, 107, 1)',
                    'pointRadius' => 5,
                    'data' => $totalData,
                    'type' => 'line',
                    'tension' => 0.2,
                    'order' => 2,
                    'participants' => $participantData,
                    'missing' => $missingData
                ]
            ]
        ];
    }

    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')
            ->whereNotNull('expire_date')
            ->whereNotNull('member_id')
            ->where('status', '!=', 'cancelled')
            ->whereRaw('YEAR(expire_date) >= ?', [$startYear])
            ->whereRaw('YEAR(expire_date) <= ?', [$endYear])
            ->get();

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

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

            // Determine which season this belongs to
            if ($expireMonth >= 9) {
                // September-December: belongs to season starting that year
                $seasonPeriod = $expireYear . '-' . ($expireYear + 1);
            } else {
                // January-August: belongs to season starting previous year
                $seasonPeriod = ($expireYear - 1) . '-' . $expireYear;
            }

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

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

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

        return [
            'labels' => $seasonLabels,
            'datasets' => [
                [
                    'label' => 'Membri Tesserati',
                    'data' => $memberCountData,
                    'backgroundColor' => 'rgba(54, 162, 235, 0.2)',
                    'borderColor' => 'rgba(54, 162, 235, 1)',
                    'borderWidth' => 2,
                    'pointBackgroundColor' => 'rgba(54, 162, 235, 1)',
                    'pointRadius' => 4,
                    'tension' => 0.3,
                    'fill' => true
                ]
            ]
        ];
    }
}