<?php

namespace App\Services;

use App\Models\AttendanceRecord;
use App\Models\Expense;
use App\Models\FixedAsset;
use App\Models\GradebookEntry;
use App\Models\InventoryItem;
use App\Models\Invoice;
use App\Models\PayrollItem;
use App\Models\Receipt;
use App\Models\ReportCard;
use App\Models\ResultsCompilation;
use App\Models\Staff;
use App\Models\Student;
use App\Models\User;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;

class AnalyticsService
{
    /**
     * @return array<string, mixed>
     */
    public function financial(): array
    {
        return [
            'total_invoiced' => (float) Invoice::query()->sum('total_amount'),
            'total_collected' => (float) Receipt::query()->sum('amount'),
            'outstanding' => (float) Invoice::query()->sum('balance_due'),
            'expenses' => (float) Expense::query()->sum('amount'),
            'net_position' => (float) Receipt::query()->sum('amount') - (float) Expense::query()->sum('amount'),
            'invoices_by_status' => Invoice::query()
                ->select('status', DB::raw('COUNT(*) as count'), DB::raw('SUM(total_amount) as total'))
                ->groupBy('status')
                ->get(),
        ];
    }

    /**
     * @return array<string, mixed>
     */
    public function revenueCollections(): array
    {
        $monthExpr = $this->monthExpression('payment_date');
        $invoiceMonthExpr = $this->monthExpression('issue_date');

        $monthlyReceipts = Receipt::query()
            ->select(DB::raw("{$monthExpr} as month"), DB::raw('SUM(amount) as total'))
            ->groupBy('month')
            ->orderBy('month')
            ->limit(12)
            ->get();

        $monthlyInvoices = Invoice::query()
            ->select(DB::raw("{$invoiceMonthExpr} as month"), DB::raw('SUM(total_amount) as total'))
            ->groupBy('month')
            ->orderBy('month')
            ->limit(12)
            ->get();

        return [
            'monthly_receipts' => $monthlyReceipts,
            'monthly_invoices' => $monthlyInvoices,
            'collection_rate' => $this->collectionRate(),
            'overdue_count' => Invoice::query()->where('status', 'overdue')->count(),
            'paid_count' => Invoice::query()->where('balance_due', '<=', 0)->count(),
        ];
    }

    /**
     * @return array<string, mixed>
     */
    public function student(): array
    {
        return [
            'total' => Student::query()->count(),
            'active' => Student::query()->where('status', 'active')->count(),
            'by_gender' => Student::query()
                ->select('gender', DB::raw('COUNT(*) as count'))
                ->groupBy('gender')
                ->get(),
            'by_status' => Student::query()
                ->select('status', DB::raw('COUNT(*) as count'))
                ->groupBy('status')
                ->get(),
            'new_admissions' => Student::query()
                ->where('admission_date', '>=', now()->startOfYear())
                ->count(),
        ];
    }

    /**
     * @return array<string, mixed>
     */
    public function academic(): array
    {
        return [
            'assessments_count' => DB::table('assessments')->count(),
            'gradebook_entries' => GradebookEntry::query()->count(),
            'results_compiled' => ResultsCompilation::query()->count(),
            'report_cards_published' => ReportCard::query()->whereNotNull('published_at')->count(),
            'average_score' => (float) (ResultsCompilation::query()->avg('average_score') ?? 0),
            'top_performers' => ResultsCompilation::query()
                ->with('student')
                ->orderByDesc('average_score')
                ->limit(5)
                ->get(),
        ];
    }

    /**
     * @return array<string, mixed>
     */
    public function staff(?User $user = null): array
    {
        $data = [
            'total' => Staff::query()->count(),
            'active' => Staff::query()->where('status', 'active')->count(),
            'by_department' => Staff::query()
                ->select('department', DB::raw('COUNT(*) as count'))
                ->groupBy('department')
                ->get(),
            'payroll_total' => (float) PayrollItem::query()->sum('net_pay'),
        ];

        if ($user?->can('finance.view')) {
            $data['average_salary'] = (float) (Staff::query()->whereNotNull('salary')->avg('salary') ?? 0);
        }

        return $data;
    }

    /**
     * @return array<string, mixed>
     */
    public function attendance(): array
    {
        $totals = AttendanceRecord::query()
            ->select('status', DB::raw('COUNT(*) as count'))
            ->groupBy('status')
            ->pluck('count', 'status');

        $total = $totals->sum();

        return [
            'total_records' => $total,
            'present' => (int) ($totals['present'] ?? 0),
            'absent' => (int) ($totals['absent'] ?? 0),
            'late' => (int) ($totals['late'] ?? 0),
            'excused' => (int) ($totals['excused'] ?? 0),
            'attendance_rate' => $total > 0
                ? round((($totals['present'] ?? 0) + ($totals['late'] ?? 0)) / $total * 100, 1)
                : 0,
            'daily_trend' => AttendanceRecord::query()
                ->join('attendances', 'attendance_records.attendance_id', '=', 'attendances.id')
                ->select('attendances.date', DB::raw("SUM(CASE WHEN attendance_records.status = 'present' THEN 1 ELSE 0 END) as present"))
                ->groupBy('attendances.date')
                ->orderByDesc('attendances.date')
                ->limit(14)
                ->get(),
        ];
    }

    /**
     * @return array<string, mixed>
     */
    public function assetInsurance(): array
    {
        $assets = FixedAsset::query()->get();

        return [
            'fixed_assets_count' => $assets->count(),
            'total_purchase_cost' => (float) $assets->sum('purchase_cost'),
            'total_book_value' => (float) $assets->sum('book_value'),
            'total_depreciation' => (float) $assets->sum('accumulated_depreciation'),
            'by_category' => FixedAsset::query()
                ->select('category', DB::raw('COUNT(*) as count'), DB::raw('SUM(book_value) as value'))
                ->groupBy('category')
                ->get(),
            'inventory_items' => InventoryItem::query()->count(),
            'inventory_value' => (float) InventoryItem::query()
                ->selectRaw('SUM(quantity_on_hand * unit_cost) as total')
                ->value('total'),
            'insured_categories' => InventoryItem::query()
                ->where('category', 'like', '%insurance%')
                ->orWhere('category', 'like', '%insured%')
                ->count(),
        ];
    }

    /**
     * @return array<string, Collection<int, mixed>>
     */
    public function all(string $tab = 'financial'): array
    {
        return match ($tab) {
            'revenue' => ['revenue' => $this->revenueCollections()],
            'student' => ['student' => $this->student()],
            'academic' => ['academic' => $this->academic()],
            'staff' => ['staff' => $this->staff()],
            'attendance' => ['attendance' => $this->attendance()],
            'assets' => ['assets' => $this->assetInsurance()],
            default => ['financial' => $this->financial()],
        };
    }

    private function collectionRate(): float
    {
        $invoiced = (float) Invoice::query()->sum('total_amount');

        if ($invoiced <= 0) {
            return 0;
        }

        $collected = (float) Receipt::query()->sum('amount');

        return round($collected / $invoiced * 100, 1);
    }

    private function monthExpression(string $column): string
    {
        return DB::connection()->getDriverName() === 'sqlite'
            ? "strftime('%Y-%m', {$column})"
            : "DATE_FORMAT({$column}, '%Y-%m')";
    }
}
