<?php

namespace App\Http\Controllers\SuperAdmin;

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

class ReportsController extends Controller
{
    public function index(): Response
    {
        $now = now();
        $sixMonthsAgo = $now->copy()->subMonths(5)->startOfMonth();

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

        // SMS usage per month (all schools)
        $smsRaw = 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')")
            ->pluck('total', 'month')
            ->toArray();

        // SMS charges/top-ups per month (income proxy)
        $smsChargesRaw = DB::table('sms_wallet_transactions')
            ->where('type', 'credit')
            ->where('created_at', '>=', $sixMonthsAgo)
            ->selectRaw("DATE_FORMAT(created_at, '%Y-%m') as month, SUM(amount) as total")
            ->groupByRaw("DATE_FORMAT(created_at, '%Y-%m')")
            ->pluck('total', 'month')
            ->toArray();

        // New licenses per month
        $licensesRaw = DB::table('licenses')
            ->where('created_at', '>=', $sixMonthsAgo)
            ->selectRaw("DATE_FORMAT(created_at, '%Y-%m') as month, COUNT(*) as total")
            ->groupByRaw("DATE_FORMAT(created_at, '%Y-%m')")
            ->pluck('total', 'month')
            ->toArray();

        // New schools per month
        $schoolsRaw = 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')")
            ->pluck('total', 'month')
            ->toArray();

        $smsChart = array_map(fn ($m): array => ['label' => $m, 'value' => (int) ($smsRaw[$m] ?? 0)], $months);
        $chargeChart = array_map(fn ($m): array => ['label' => $m, 'value' => (int) ($smsChargesRaw[$m] ?? 0)], $months);
        $licenseChart = array_map(fn ($m): array => ['label' => $m, 'value' => (int) ($licensesRaw[$m] ?? 0)], $months);
        $schoolChart = array_map(fn ($m): array => ['label' => $m, 'value' => (int) ($schoolsRaw[$m] ?? 0)], $months);

        // Top schools by SMS usage
        $topSmsBySchool = DB::table('sms_wallet_transactions')
            ->join('schools', 'schools.id', '=', 'sms_wallet_transactions.school_id')
            ->where('sms_wallet_transactions.type', 'deduct')
            ->where('sms_wallet_transactions.created_at', '>=', $sixMonthsAgo)
            ->groupBy('schools.id', 'schools.name_fa')
            ->orderByDesc('total_sms')
            ->limit(10)
            ->get([
                'schools.name_fa',
                DB::raw('SUM(sms_wallet_transactions.amount) as total_sms'),
            ]);

        // Active module counts
        $moduleCounts = DB::table('school_module_settings')
            ->join('modules', 'modules.id', '=', 'school_module_settings.module_id')
            ->where('school_module_settings.is_enabled', true)
            ->groupBy('modules.id', 'modules.name_fa')
            ->orderByDesc('school_count')
            ->get([
                'modules.name_fa',
                DB::raw('COUNT(*) as school_count'),
            ]);

        // Totals
        $totals = [
            'schools' => DB::table('schools')->count(),
            'activeSchools' => DB::table('schools')->where('status', 'active')->count(),
            'activeLicenses' => DB::table('licenses')->where('status', 'active')->count(),
            'smsThisMonth' => (int) DB::table('sms_wallet_transactions')
                ->where('type', 'deduct')
                ->where('created_at', '>=', $now->copy()->startOfMonth())
                ->sum('amount'),
            'totalStudents' => DB::table('students')->count(),
            'totalTeachers' => DB::table('teachers')->count(),
        ];

        return Inertia::render('SuperAdmin/Reports', [
            'totals' => $totals,
            'smsChart' => $smsChart,
            'chargeChart' => $chargeChart,
            'licenseChart' => $licenseChart,
            'schoolChart' => $schoolChart,
            'topSmsBySchool' => $topSmsBySchool->map(fn ($r): array => [
                'school' => $r->name_fa,
                'sms' => (int) $r->total_sms,
            ])->values(),
            'moduleCounts' => $moduleCounts->map(fn ($r): array => [
                'module' => $r->name_fa,
                'schools' => (int) $r->school_count,
            ])->values(),
        ]);
    }
}
