<?php

namespace App\Filament\Widgets;

use Filament\Widgets\StatsOverviewWidget as BaseWidget;
use Filament\Widgets\StatsOverviewWidget\Stat;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Carbon;

class StudentStatsWidget extends BaseWidget
{
    protected static ?int $sort = 1;

    protected function getStats(): array
    {
        // 总学生数
        $totalStudents = DB::table('students')->count();

        // 本月新增学生
        $newStudentsThisMonth = DB::table('students')
            ->whereMonth('created_at', Carbon::now()->month)
            ->whereYear('created_at', Carbon::now()->year)
            ->count();

        // 有登录记录的学生数
        $activeStudents = DB::table('students')
            ->join('users', 'students.student_id', '=', 'users.user_id')
            ->whereNotNull('users.last_login')
            ->count();

        // 本周登录的学生数
        $weeklyActiveStudents = DB::table('students')
            ->join('users', 'students.student_id', '=', 'users.user_id')
            ->where('users.last_login', '>=', Carbon::now()->subWeek())
            ->count();

        // 按年级统计
        $gradeStats = DB::table('students')
            ->select('grade', DB::raw('count(*) as count'))
            ->groupBy('grade')
            ->orderBy('count', 'desc')
            ->get();

        // 按班级统计
        $classStats = DB::table('students')
            ->select('class_name', DB::raw('count(*) as count'))
            ->groupBy('class_name')
            ->orderBy('count', 'desc')
            ->limit(5)
            ->get();

        return [
            Stat::make('总学生数', $totalStudents)
                ->description('全平台注册学生')
                ->descriptionIcon('heroicon-m-academic-cap')
                ->color('primary')
                ->chart([7, 15, 23, 38, 45, 52, $totalStudents]),

            Stat::make('本月新增', $newStudentsThisMonth)
                ->description('较上月 ' . ($newStudentsThisMonth > 0 ? '+' : '') . $newStudentsThisMonth)
                ->descriptionIcon('heroicon-m-arrow-trending-up')
                ->color($newStudentsThisMonth > 0 ? 'success' : 'gray'),

            Stat::make('活跃学生', $activeStudents)
                ->description('至少登录过一次')
                ->descriptionIcon('heroicon-m-user')
                ->color('info'),

            Stat::make('本周活跃', $weeklyActiveStudents)
                ->description('最近7天登录')
                ->descriptionIcon('heroicon-m-clock')
                ->color($weeklyActiveStudents > 0 ? 'success' : 'warning'),
        ];
    }

    protected function getColumns(): int
    {
        return 4;
    }
}