<?php

namespace App\Http\Controllers;

use App\Models\Expense;
use App\Models\ExpenseCategory;
use Illuminate\Http\Request;
use Illuminate\Http\JsonResponse;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Storage;
use Illuminate\Validation\Rule;
use Carbon\Carbon;

class ExpenseController extends Controller
{
    public function index(Request $request): JsonResponse
    {
        $query = Expense::with(['category', 'user', 'approver', 'branch'])
            ->where('restaurant_id', $request->user()->restaurant_id);

        // Filter by branch if user is not admin
        if ($request->user()->role !== 'admin' && $request->user()->branch_id) {
            $query->where('branch_id', $request->user()->branch_id);
        }

        // Apply filters
        if ($request->filled('status')) {
            $query->where('status', $request->status);
        }

        if ($request->filled('category_id')) {
            $query->where('category_id', $request->category_id);
        }

        if ($request->filled('start_date')) {
            $query->whereDate('expense_date', '>=', $request->start_date);
        }

        if ($request->filled('end_date')) {
            $query->whereDate('expense_date', '<=', $request->end_date);
        }

        if ($request->filled('search')) {
            $search = $request->search;
            $query->where(function ($q) use ($search) {
                $q->where('title', 'like', "%{$search}%")
                  ->orWhere('description', 'like', "%{$search}%")
                  ->orWhere('vendor', 'like', "%{$search}%")
                  ->orWhere('expense_number', 'like', "%{$search}%");
            });
        }

        // Sort by expense_date desc by default
        $sortBy = $request->get('sort_by', 'expense_date');
        $sortOrder = $request->get('sort_order', 'desc');
        $query->orderBy($sortBy, $sortOrder);

        $expenses = $query->paginate($request->get('per_page', 2000));

        return response()->json($expenses);
    }

    public function show(Request $request, Expense $expense): JsonResponse
    {
        // Check if user can access this expense
        if ($expense->restaurant_id !== $request->user()->restaurant_id) {
            return response()->json(['message' => 'Expense not found'], 404);
        }

        // Branch-level access control
        if ($request->user()->role !== 'admin' && $request->user()->branch_id && 
            $expense->branch_id !== $request->user()->branch_id) {
            return response()->json(['message' => 'Expense not found'], 404);
        }

        $expense->load(['category', 'user', 'approver', 'branch']);

        return response()->json($expense);
    }

    public function store(Request $request): JsonResponse
    {
        $validated = $request->validate([
            'category_id' => 'required|exists:expense_categories,id',
            'title' => 'required|string|max:255',
            'description' => 'nullable|string',
            'amount' => 'required|numeric|min:0',
            'expense_date' => 'required|date',
            'vendor' => 'nullable|string|max:255',
            'receipt_number' => 'nullable|string|max:255',
            'reference_number' => 'nullable|string|max:255',
            'payment_method' => 'required|in:cash,pay_later,mobile_money,petty_cash',
            'payment_status' => 'required|in:immediate,pay_later',
            'due_date' => 'required_if:payment_status,pay_later|nullable|date|after_or_equal:today',
            'payment_notes' => 'nullable|string|max:1000',
            'priority' => 'required|in:low,medium,high,urgent',
            'is_petty_cash' => 'boolean',
            'tags' => 'nullable|array',
            'tags.*' => 'string|max:50',
            'attachments' => 'nullable|array',
            'attachments.*' => 'file|mimes:jpg,jpeg,png,pdf,doc,docx|max:5120', // 5MB max
        ]);

        try {
            DB::beginTransaction();

            // Handle file uploads
            $attachmentPaths = [];
            if ($request->hasFile('attachments')) {
                foreach ($request->file('attachments') as $file) {
                    $path = $file->store('expenses', 'public');
                    $attachmentPaths[] = [
                        'path' => $path,
                        'original_name' => $file->getClientOriginalName(),
                        'size' => $file->getSize(),
                        'mime_type' => $file->getMimeType(),
                    ];
                }
            }

            // Determine if this is a petty cash expense
            $isPettyCash = $validated['payment_method'] === 'petty_cash' || ($validated['is_petty_cash'] ?? false);

            $expense = Expense::create([
                'restaurant_id' => $request->user()->restaurant_id,
                'branch_id' => $request->user()->branch_id,
                'user_id' => $request->user()->id,
                'category_id' => $validated['category_id'],
                'title' => $validated['title'],
                'description' => $request->input('description'),
                'amount' => $validated['amount'],
                'expense_date' => $validated['expense_date'],
                'vendor' => $request->input('vendor'),
                'receipt_number' => $request->input('receipt_number'),
                'reference_number' => $request->input('reference_number'),
                'payment_method' => $validated['payment_method'],
                'payment_status' => $validated['payment_status'],
                'due_date' => $validated['due_date'] ?? null,
                'payment_notes' => $validated['payment_notes'] ?? null,
                'priority' => $validated['priority'],
                'is_petty_cash' => $isPettyCash,
                'status' => $isPettyCash ? 'approved' : 'pending', // Auto-approve petty cash expenses
                'tags' => $request->input('tags', []),
                'attachments' => $attachmentPaths,
            ]);

            // If it's a petty cash expense and immediate payment, process the withdrawal
            if ($isPettyCash && $validated['payment_status'] === 'immediate') {
                $pettyCashBalance = \App\Models\PettyCashBalance::where('restaurant_id', $request->user()->restaurant_id)
                    ->where('branch_id', $request->user()->branch_id)
                    ->first();

                if ($pettyCashBalance && $pettyCashBalance->canWithdraw($validated['amount'])) {
                    $transaction = $pettyCashBalance->withdraw(
                        $validated['amount'],
                        $request->user(),
                        "Expense: {$validated['title']}",
                        $expense,
                        $validated['payment_notes']
                    );

                    $expense->update([
                        'petty_cash_balance_before' => $transaction->balance_before,
                        'petty_cash_balance_after' => $transaction->balance_after,
                        'status' => 'paid',
                        'paid_at' => now(),
                    ]);
                }
            }

            $expense->load(['category', 'user', 'branch']);

            DB::commit();

            return response()->json([
                'message' => 'Expense created successfully',
                'expense' => $expense
            ], 201);

        } catch (\Exception $e) {
            DB::rollBack();
            
            // Clean up uploaded files on error
            foreach ($attachmentPaths as $attachment) {
                Storage::disk('public')->delete($attachment['path']);
            }

            return response()->json([
                'message' => 'Failed to create expense',
                'error' => $e->getMessage()
            ], 500);
        }
    }

    public function update(Request $request, Expense $expense): JsonResponse
    {
        // Check permissions
        if ($expense->restaurant_id !== $request->user()->restaurant_id) {
            return response()->json(['message' => 'Expense not found'], 404);
        }

        // Only allow updates to pending expenses
        if ($expense->status !== 'pending') {
            return response()->json(['message' => 'Cannot update non-pending expense'], 422);
        }

        $validated = $request->validate([
            'category_id' => 'required|exists:expense_categories,id',
            'title' => 'required|string|max:255',
            'description' => 'nullable|string',
            'amount' => 'required|numeric|min:0',
            'expense_date' => 'required|date',
            'vendor' => 'nullable|string|max:255',
            'receipt_number' => 'nullable|string|max:255',
            'payment_method' => 'required|in:cash,pay_later,mobile_money,petty_cash',
            'tags' => 'nullable|array',
            'tags.*' => 'string|max:50',
        ]);

        try {
            $expense->update($validated);
            $expense->load(['category', 'user', 'approver', 'branch']);

            return response()->json([
                'message' => 'Expense updated successfully',
                'expense' => $expense
            ]);

        } catch (\Exception $e) {
            return response()->json([
                'message' => 'Failed to update expense',
                'error' => $e->getMessage()
            ], 500);
        }
    }

    public function destroy(Request $request, Expense $expense): JsonResponse
    {
        // Check permissions - Only admin can delete expenses
        if ($request->user()->role !== 'admin') {
            return response()->json(['message' => 'Only administrators can delete expenses'], 403);
        }

        if ($expense->restaurant_id !== $request->user()->restaurant_id) {
            return response()->json(['message' => 'Expense not found'], 404);
        }

        try {
            // Delete attachments
            if ($expense->attachments) {
                foreach ($expense->attachments as $attachment) {
                    Storage::disk('public')->delete($attachment['path']);
                }
            }

            $expense->delete();

            return response()->json(['message' => 'Expense deleted successfully']);

        } catch (\Exception $e) {
            return response()->json([
                'message' => 'Failed to delete expense',
                'error' => $e->getMessage()
            ], 500);
        }
    }

    public function approve(Request $request, Expense $expense): JsonResponse
    {
        // Check permissions
        if ($expense->restaurant_id !== $request->user()->restaurant_id) {
            return response()->json(['message' => 'Expense not found'], 404);
        }

        // Only admin and manager can approve expenses
        if (!in_array($request->user()->role, ['admin', 'manager'])) {
            return response()->json(['message' => 'Insufficient permissions to approve expense'], 403);
        }

        if (!$expense->canBeApproved()) {
            return response()->json(['message' => 'Expense cannot be approved'], 422);
        }

        $validated = $request->validate([
            'notes' => 'nullable|string|max:1000',
        ]);

        try {
            $success = $expense->approve($request->user(), $validated['notes'] ?? null);

            if (!$success) {
                return response()->json(['message' => 'Failed to approve expense'], 422);
            }

            $expense->load(['category', 'user', 'approver', 'branch']);

            return response()->json([
                'message' => 'Expense approved successfully',
                'expense' => $expense
            ]);

        } catch (\Exception $e) {
            return response()->json([
                'message' => 'Failed to approve expense',
                'error' => $e->getMessage()
            ], 500);
        }
    }

    public function reject(Request $request, Expense $expense): JsonResponse
    {
        // Check permissions
        if ($expense->restaurant_id !== $request->user()->restaurant_id) {
            return response()->json(['message' => 'Expense not found'], 404);
        }

        // Only admin and manager can reject expenses
        if (!in_array($request->user()->role, ['admin', 'manager'])) {
            return response()->json(['message' => 'Insufficient permissions to reject expense'], 403);
        }

        if (!$expense->canBeRejected()) {
            return response()->json(['message' => 'Expense cannot be rejected'], 422);
        }

        $validated = $request->validate([
            'notes' => 'required|string|max:1000',
        ]);

        try {
            $success = $expense->reject($request->user(), $validated['notes']);

            if (!$success) {
                return response()->json(['message' => 'Failed to reject expense'], 422);
            }

            $expense->load(['category', 'user', 'approver', 'branch']);

            return response()->json([
                'message' => 'Expense rejected successfully',
                'expense' => $expense
            ]);

        } catch (\Exception $e) {
            return response()->json([
                'message' => 'Failed to reject expense',
                'error' => $e->getMessage()
            ], 500);
        }
    }

    public function markAsPaid(Request $request, Expense $expense): JsonResponse
    {
        // Check permissions
        if ($expense->restaurant_id !== $request->user()->restaurant_id) {
            return response()->json(['message' => 'Expense not found'], 404);
        }

        if (!$expense->canBePaid()) {
            return response()->json(['message' => 'Expense cannot be marked as paid'], 422);
        }

        try {
            $success = $expense->markAsPaid();

            if (!$success) {
                return response()->json(['message' => 'Failed to mark expense as paid'], 422);
            }

            $expense->load(['category', 'user', 'approver', 'branch']);

            return response()->json([
                'message' => 'Expense marked as paid successfully',
                'expense' => $expense
            ]);

        } catch (\Exception $e) {
            return response()->json([
                'message' => 'Failed to mark expense as paid',
                'error' => $e->getMessage()
            ], 500);
        }
    }

    public function getCategories(Request $request): JsonResponse
    {
        $categories = ExpenseCategory::where('restaurant_id', $request->user()->restaurant_id)
            ->where('is_active', true)
            ->orderBy('name')
            ->get();

        return response()->json($categories);
    }

    public function storeCategory(Request $request): JsonResponse
    {
        // Check permissions - only admin/manager can create categories
        if (!in_array(auth()->user()->role, ['admin', 'manager'])) {
            return response()->json(['message' => 'Insufficient permissions'], 403);
        }

        $validated = $request->validate([
            'name' => 'required|string|max:255',
            'description' => 'nullable|string',
            'budget_limit' => 'nullable|numeric|min:0',
        ]);

        try {
            $category = ExpenseCategory::create([
                'restaurant_id' => $request->user()->restaurant_id,
                'name' => $validated['name'],
                'description' => $validated['description'],
                'budget_limit' => $validated['budget_limit'],
                'is_active' => true,
            ]);

            return response()->json([
                'message' => 'Category created successfully',
                'category' => $category
            ], 201);

        } catch (\Exception $e) {
            return response()->json([
                'message' => 'Failed to create category',
                'error' => $e->getMessage()
            ], 500);
        }
    }

    public function updateCategory(Request $request, ExpenseCategory $category): JsonResponse
    {
        // Check permissions
        if ($category->restaurant_id !== $request->user()->restaurant_id) {
            return response()->json(['message' => 'Category not found'], 404);
        }

        if (!in_array(auth()->user()->role, ['admin', 'manager'])) {
            return response()->json(['message' => 'Insufficient permissions'], 403);
        }

        $validated = $request->validate([
            'name' => 'required|string|max:255',
            'description' => 'nullable|string',
            'budget_limit' => 'nullable|numeric|min:0',
            'is_active' => 'boolean',
        ]);

        try {
            $category->update($validated);

            return response()->json([
                'message' => 'Category updated successfully',
                'category' => $category
            ]);

        } catch (\Exception $e) {
            return response()->json([
                'message' => 'Failed to update category',
                'error' => $e->getMessage()
            ], 500);
        }
    }

    public function destroyCategory(Request $request, ExpenseCategory $category): JsonResponse
    {
        // Check permissions - Only admin can delete categories
        if ($request->user()->role !== 'admin') {
            return response()->json(['message' => 'Only administrators can delete categories'], 403);
        }

        if ($category->restaurant_id !== $request->user()->restaurant_id) {
            return response()->json(['message' => 'Category not found'], 404);
        }

        try {
            // Check if category has any expenses
            $expenseCount = $category->expenses()->count();
            if ($expenseCount > 0) {
                return response()->json([
                    'message' => "Cannot delete category. It has {$expenseCount} associated expenses."
                ], 422);
            }

            $category->delete();

            return response()->json(['message' => 'Category deleted successfully']);

        } catch (\Exception $e) {
            return response()->json([
                'message' => 'Failed to delete category',
                'error' => $e->getMessage()
            ], 500);
        }
    }

    public function getSummary(Request $request): JsonResponse
    {
        $restaurantId = $request->user()->restaurant_id;
        $branchId = $request->user()->branch_id;

        $query = Expense::where('expenses.restaurant_id', $restaurantId);

        // Filter by branch if user is not admin
        if ($request->user()->role !== 'admin' && $branchId) {
            $query->where('expenses.branch_id', $branchId);
        }

        // Apply date filter
        $startDate = $request->get('start_date', Carbon::now()->startOfMonth());
        $endDate = $request->get('end_date', Carbon::now()->endOfMonth());
        
        $query->whereBetween('expenses.expense_date', [$startDate, $endDate]);

        try {
            $summary = [
                'total_expenses' => $query->clone()->whereIn('expenses.status', ['approved', 'paid'])->sum('expenses.amount'),
                'pending_expenses' => $query->clone()->where('expenses.status', 'pending')->sum('expenses.amount'),
                'approved_expenses' => $query->clone()->where('expenses.status', 'approved')->sum('expenses.amount'),
                'paid_expenses' => $query->clone()->where('expenses.status', 'paid')->sum('expenses.amount'),
                'rejected_expenses' => $query->clone()->where('expenses.status', 'rejected')->sum('expenses.amount'),
                'expense_count' => $query->clone()->count(),
                'pending_count' => $query->clone()->where('expenses.status', 'pending')->count(),
            ];

            // Category breakdown
            $categoryBreakdown = $query->clone()
                ->join('expense_categories', 'expenses.category_id', '=', 'expense_categories.id')
                ->select('expense_categories.name', DB::raw('SUM(expenses.amount) as total'))
                ->whereIn('expenses.status', ['approved', 'paid'])
                ->groupBy('expense_categories.id', 'expense_categories.name')
                ->orderBy('total', 'desc')
                ->get();

            $summary['category_breakdown'] = $categoryBreakdown;

            return response()->json($summary);

        } catch (\Exception $e) {
            return response()->json([
                'message' => 'Failed to get expense summary',
                'error' => $e->getMessage()
            ], 500);
        }
    }

    public function downloadAttachment(Request $request, Expense $expense, int $attachmentIndex): JsonResponse
    {
        // Check permissions
        if ($expense->restaurant_id !== $request->user()->restaurant_id) {
            return response()->json(['message' => 'Expense not found'], 404);
        }

        if (!$expense->attachments || !isset($expense->attachments[$attachmentIndex])) {
            return response()->json(['message' => 'Attachment not found'], 404);
        }

        $attachment = $expense->attachments[$attachmentIndex];
        $filePath = storage_path('app/public/' . $attachment['path']);

        if (!file_exists($filePath)) {
            return response()->json(['message' => 'File not found'], 404);
        }

        return response()->download($filePath, $attachment['original_name']);
    }

    public function getPayLaterExpenses(Request $request): JsonResponse
    {
        $query = Expense::with(['category', 'user', 'approver', 'branch'])
            ->where('restaurant_id', $request->user()->restaurant_id)
            ->where('payment_status', 'pay_later')
            ->whereNotIn('status', ['paid', 'rejected']); // Only show unpaid pay later expenses

        // Filter by branch if user is not admin
        if ($request->user()->role !== 'admin' && $request->user()->branch_id) {
            $query->where('branch_id', $request->user()->branch_id);
        }

        // Apply date filter
        if ($request->filled('start_date') && $request->filled('end_date')) {
            $query->whereBetween('expense_date', [$request->start_date, $request->end_date]);
        }

        // Apply filters
        if ($request->filled('overdue_only') && $request->boolean('overdue_only')) {
            $query->where('due_date', '<', now());
        }

        if ($request->filled('priority') && $request->priority !== '') {
            $query->where('priority', $request->priority);
        }

        $expenses = $query->orderBy('due_date', 'asc')
            ->orderBy('priority', 'desc')
            ->paginate($request->get('per_page', 2000));

        return response()->json($expenses);
    }

    public function markPayLaterAsPaid(Request $request, Expense $expense): JsonResponse
    {
        // Check permissions
        if ($expense->restaurant_id !== $request->user()->restaurant_id) {
            return response()->json(['message' => 'Expense not found'], 404);
        }

        if (!$expense->isPayLater()) {
            return response()->json(['message' => 'This is not a pay later expense'], 422);
        }

        if ($expense->status === 'paid') {
            return response()->json(['message' => 'Expense is already marked as paid'], 422);
        }

        $validated = $request->validate([
            'payment_notes' => 'nullable|string|max:1000',
            'actual_payment_date' => 'nullable|date',
        ]);

        try {
            $expense->update([
                'status' => 'paid',
                'paid_at' => $validated['actual_payment_date'] ? 
                    Carbon::parse($validated['actual_payment_date']) : now(),
                'payment_notes' => $validated['payment_notes'] ?? $expense->payment_notes,
            ]);

            $expense->load(['category', 'user', 'approver', 'branch']);

            return response()->json([
                'message' => 'Pay later expense marked as paid successfully',
                'expense' => $expense
            ]);

        } catch (\Exception $e) {
            return response()->json([
                'message' => 'Failed to mark expense as paid',
                'error' => $e->getMessage()
            ], 500);
        }
    }

    public function updateDueDate(Request $request, Expense $expense): JsonResponse
    {
        // Check permissions
        if ($expense->restaurant_id !== $request->user()->restaurant_id) {
            return response()->json(['message' => 'Expense not found'], 404);
        }

        // Only admin and manager can update due dates
        if (!in_array($request->user()->role, ['admin', 'manager'])) {
            return response()->json(['message' => 'Insufficient permissions'], 403);
        }

        if (!$expense->isPayLater()) {
            return response()->json(['message' => 'This is not a pay later expense'], 422);
        }

        $validated = $request->validate([
            'due_date' => 'required|date|after_or_equal:today',
            'notes' => 'nullable|string|max:1000',
        ]);

        try {
            $expense->update([
                'due_date' => $validated['due_date'],
                'payment_notes' => $validated['notes'] ?? $expense->payment_notes,
            ]);

            $expense->load(['category', 'user', 'approver', 'branch']);

            return response()->json([
                'message' => 'Due date updated successfully',
                'expense' => $expense
            ]);

        } catch (\Exception $e) {
            return response()->json([
                'message' => 'Failed to update due date',
                'error' => $e->getMessage()
            ], 500);
        }
    }

    public function getExpenseSummaryEnhanced(Request $request): JsonResponse
    {
        $restaurantId = $request->user()->restaurant_id;
        $branchId = $request->user()->branch_id;

        $query = Expense::where('expenses.restaurant_id', $restaurantId);

        // Filter by branch if user is not admin
        if ($request->user()->role !== 'admin' && $branchId) {
            $query->where('expenses.branch_id', $branchId);
        }

        // Apply date filter
        $startDate = $request->get('start_date', Carbon::now()->startOfMonth());
        $endDate = $request->get('end_date', Carbon::now()->endOfMonth());
        
        $query->whereBetween('expenses.expense_date', [$startDate, $endDate]);

        try {
            // Calculate petty cash totals first
            $pettyCashExpenses = $query->clone()->where(function($q) {
                $q->where('expenses.is_petty_cash', true)
                  ->orWhere('expenses.payment_method', 'petty_cash');
            })->whereIn('expenses.status', ['approved', 'paid'])->sum('expenses.amount');
            
            $pettyCashWithdrawals = DB::table('petty_cash_transactions')
                ->where('type', 'withdrawal')
                ->where('restaurant_id', $request->user()->restaurant_id)
                ->whereBetween('created_at', [$startDate, $endDate])
                ->sum('amount');
            
            $pettyCashCount = $query->clone()->where(function($q) {
                $q->where('expenses.is_petty_cash', true)
                  ->orWhere('expenses.payment_method', 'petty_cash');
            })->count();
            
            $pettyCashTransactionCount = DB::table('petty_cash_transactions')
                ->where('type', 'withdrawal')
                ->where('restaurant_id', $request->user()->restaurant_id)
                ->whereBetween('created_at', [$startDate, $endDate])
                ->count();
            
            // Priority breakdown
            $priorityBreakdown = $query->clone()
                ->select('priority', DB::raw('COUNT(*) as count'), DB::raw('SUM(amount) as total'))
                ->whereIn('expenses.status', ['approved', 'paid'])
                ->groupBy('priority')
                ->get()
                ->keyBy('priority');

            // Payment method breakdown
            $paymentMethodBreakdown = $query->clone()
                ->select('payment_method', DB::raw('COUNT(*) as count'), DB::raw('SUM(amount) as total'))
                ->whereIn('expenses.status', ['approved', 'paid'])
                ->groupBy('payment_method')
                ->get()
                ->keyBy('payment_method');

            // Category breakdown
            $categoryBreakdown = $query->clone()
                ->join('expense_categories', 'expenses.category_id', '=', 'expense_categories.id')
                ->select('expense_categories.name', DB::raw('SUM(expenses.amount) as total'), DB::raw('COUNT(*) as count'))
                ->whereIn('expenses.status', ['approved', 'paid'])
                ->groupBy('expense_categories.id', 'expense_categories.name')
                ->orderBy('total', 'desc')
                ->get();

            $summary = [
                // Basic totals
                'total_expenses' => $query->clone()->whereIn('expenses.status', ['approved', 'paid'])->sum('expenses.amount'),
                'pending_expenses' => $query->clone()->where('expenses.status', 'pending')->sum('expenses.amount'),
                'approved_expenses' => $query->clone()->where('expenses.status', 'approved')->sum('expenses.amount'),
                'paid_expenses' => $query->clone()->where('expenses.status', 'paid')->sum('expenses.amount'),
                'rejected_expenses' => $query->clone()->where('expenses.status', 'rejected')->sum('expenses.amount'),
                
                // Payment status breakdown
                'immediate_expenses' => $query->clone()->where('expenses.payment_status', 'immediate')->whereIn('expenses.status', ['approved', 'paid'])->sum('expenses.amount'),
                'pay_later_expenses' => $query->clone()->where('expenses.payment_status', 'pay_later')->whereIn('expenses.status', ['approved', 'paid'])->sum('expenses.amount'),
                'pay_later_unpaid' => $query->clone()->where('expenses.payment_status', 'pay_later')->whereNotIn('expenses.status', ['paid', 'rejected'])->sum('expenses.amount'),
                'overdue_expenses' => $query->clone()->where('expenses.payment_status', 'pay_later')->where('expenses.due_date', '<', now())->whereNotIn('expenses.status', ['paid', 'rejected'])->sum('expenses.amount'),
                
                // Petty cash breakdown (include both expenses and withdrawals from petty_cash_transactions)
                'petty_cash_expenses' => $pettyCashExpenses + $pettyCashWithdrawals,
                'non_petty_cash_expenses' => $query->clone()->where('expenses.is_petty_cash', false)->where('expenses.payment_method', '!=', 'petty_cash')->whereIn('expenses.status', ['approved', 'paid'])->sum('expenses.amount'),
                
                // Counts
                'expense_count' => $query->clone()->count(),
                'pending_count' => $query->clone()->where('expenses.status', 'pending')->count(),
                'pay_later_count' => $query->clone()->where('expenses.payment_status', 'pay_later')->whereNotIn('expenses.status', ['paid', 'rejected'])->count(),
                'overdue_count' => $query->clone()->where('expenses.payment_status', 'pay_later')->where('expenses.due_date', '<', now())->whereNotIn('expenses.status', ['paid', 'rejected'])->count(),
                'petty_cash_count' => $pettyCashCount + $pettyCashTransactionCount,
                
                // Breakdowns
                'priority_breakdown' => $priorityBreakdown,
                'payment_method_breakdown' => $paymentMethodBreakdown,
                'category_breakdown' => $categoryBreakdown,
            ];

            // Add formatted values
            foreach (['total_expenses', 'pending_expenses', 'approved_expenses', 'paid_expenses', 'rejected_expenses', 
                     'immediate_expenses', 'pay_later_expenses', 'pay_later_unpaid', 'overdue_expenses', 
                     'petty_cash_expenses', 'non_petty_cash_expenses'] as $key) {
                $summary['formatted_' . $key] = 'KES ' . number_format($summary[$key], 2);
            }

            return response()->json($summary);

        } catch (\Exception $e) {
            return response()->json([
                'message' => 'Failed to get expense summary',
                'error' => $e->getMessage()
            ], 500);
        }
    }
}