<?php

namespace App\Http\Controllers\SuperAdmin;

use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\DB;
use Inertia\Inertia;
use Inertia\Response;

class DashboardController extends Controller
{
    public function index(): Response
    {
        $now = now();
        $thirtyDaysLater = $now->copy()->addDays(30);
        $monthStart = $now->copy()->startOfMonth();
        $sixMonthsAgo = $now->copy()->subMonths(5)->startOfMonth();

        $licenseWarnings = DB::table('licenses')
            ->join('schools', 'schools.id', '=', 'licenses.school_id')
            ->where('licenses.status', 'active')
            ->whereBetween('licenses.expires_at', [$now->toDateString(), $thirtyDaysLater->toDateString()])
            ->orderBy('licenses.expires_at')
            ->limit(20)
            ->get(['schools.name_fa', 'licenses.expires_at'])
            ->map(function ($row) use ($now): array {
                $expiresAt = \Carbon\Carbon::parse($row->expires_at);
                $daysLeft = max(0, (int) $now->diffInDays($expiresAt, false));

                return [
                    'schoolName' => $row->name_fa,
                    'expiresAt' => $this->jalaliDate($row->expires_at),
                    'daysLeft' => $daysLeft,
                ];
            })->values();

        $recentLogins = DB::table('login_logs')
            ->leftJoin('schools', 'schools.id', '=', 'login_logs.school_id')
            ->orderByDesc('login_logs.created_at')
            ->limit(20)
            ->get(['login_logs.username', 'schools.name_fa as school_name', 'login_logs.status', 'login_logs.ip_address', 'login_logs.created_at'])
            ->map(fn ($row): array => [
                'username' => $row->username,
                'schoolName' => $row->school_name,
                'status' => $row->status,
                'ip' => $row->ip_address,
                'createdAt' => $this->jalaliDate($row->created_at),
            ])->values();

        // Monthly SMS usage (last 6 months)
        $monthlySmsSeries = DB::table('sms_wallet_transactions')
            ->where('type', 'deduct')
            ->where('created_at', '>=', $sixMonthsAgo)
            ->selectRaw("DATE_FORMAT(created_at, '%Y-%m') as month, SUM(amount) as total")
            ->groupByRaw("DATE_FORMAT(created_at, '%Y-%m')")
            ->orderBy('month')
            ->pluck('total', 'month')
            ->toArray();

        // Monthly new schools (last 6 months)
        $monthlySchoolSeries = DB::table('schools')
            ->where('created_at', '>=', $sixMonthsAgo)
            ->selectRaw("DATE_FORMAT(created_at, '%Y-%m') as month, COUNT(*) as total")
            ->groupByRaw("DATE_FORMAT(created_at, '%Y-%m')")
            ->orderBy('month')
            ->pluck('total', 'month')
            ->toArray();

        // Build 6-month labels array
        $chartMonths = [];
        for ($i = 5; $i >= 0; $i--) {
            $chartMonths[] = $now->copy()->subMonths($i)->format('Y-m');
        }

        $smsChart = array_map(fn ($m): array => [
            'label' => $m,
            'value' => (int) ($monthlySmsSeries[$m] ?? 0),
        ], $chartMonths);

        $schoolChart = array_map(fn ($m): array => [
            'label' => $m,
            'value' => (int) ($monthlySchoolSeries[$m] ?? 0),
        ], $chartMonths);

        return Inertia::render('SuperAdmin/Dashboard', [
            'metrics' => [
                'activeSchools' => DB::table('schools')->where('status', 'active')->count(),
                'inactiveSchools' => DB::table('schools')->where('status', '!=', 'active')->count(),
                'totalStudents' => DB::table('students')->count(),
                'totalTeachers' => DB::table('teachers')->count(),
                'totalUsers' => DB::table('users')->where('role_key', '!=', 'super_admin')->count(),
                'smsUsedThisMonth' => (int) DB::table('sms_wallet_transactions')
                    ->where('type', 'deduct')
                    ->where('created_at', '>=', $monthStart)
                    ->sum('amount'),
                'expiringLicenses' => DB::table('licenses')
                    ->where('status', 'active')
                    ->whereBetween('expires_at', [$now->toDateString(), $thirtyDaysLater->toDateString()])
                    ->count(),
            ],
            'smsChart' => $smsChart,
            'schoolChart' => $schoolChart,
            'licenseWarnings' => $licenseWarnings,
            'recentLogins' => $recentLogins,
        ]);
    }
}
