<?php

namespace App\Http\Controllers;

use App\Models\Order;
use App\Models\OrderItem;
use App\Models\Payment;
use App\Models\User;
use App\Models\MenuItem;
use App\Models\Customer;
use Illuminate\Http\Request;
use Illuminate\Http\JsonResponse;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;

class ReportsController extends Controller
{
    /**
     * Apply shift filtering to query based on shift type
     * 24hrs: 6:30 AM to 6:30 AM (next day)
     * dayshift: 6:30 AM to 6:30 PM
     * nightshift: 6:30 PM to 6:30 AM (next day)
     */
    private function applyShiftFilter($query, $startDate, $endDate, $shift = null, $tablePrefix = '', $dateColumn = 'created_at')
    {
        // Determine the column name to use
        $columnName = $tablePrefix ? $tablePrefix . '.' . $dateColumn : $dateColumn;
        
        // Check if this is a DATE column (expense_date) vs DATETIME column (created_at)
        $isDateColumn = $dateColumn === 'expense_date';
        
        if (!$shift || $shift === '24hrs') {
            // 24hrs: Use the full date range as provided
            if ($isDateColumn) {
                // For DATE columns, use whereDate for proper comparison
                $startDateOnly = $startDate->toDateString();
                $endDateOnly = $endDate->toDateString();
                return $query->whereDate($columnName, '>=', $startDateOnly)
                             ->whereDate($columnName, '<=', $endDateOnly);
            } else {
                // For DATETIME columns, use whereBetween
                return $query->whereBetween($columnName, [$startDate, $endDate]);
            }
        }

        // For shift filtering, we need to filter by date range first, then by time
        // We'll do this in PHP after fetching to ensure consistency with getShiftBreakdown
        $filteredQuery = $query->where(function ($q) use ($startDate, $endDate, $columnName, $isDateColumn) {
            if ($isDateColumn) {
                // For DATE columns, just filter by date range (shifts don't apply to DATE-only fields)
                $startDateOnly = $startDate->toDateString();
                $endDateOnly = $endDate->toDateString();
                $q->whereDate($columnName, '>=', $startDateOnly)
                  ->whereDate($columnName, '<=', $endDateOnly);
            } else {
                // For DATETIME columns, just get all records in the date range
                // The shift filtering will be done in PHP for consistency
                $q->whereBetween($columnName, [$startDate, $endDate]);
            }
        });

        return $filteredQuery;
    }

    /**
     * Get detailed shift breakdown for 24-hour reports
     */
    private function getShiftBreakdown($allOrders, $startDate, $endDate)
    {
        // Convert to Kenyan timezone for accurate shift calculations
        $kenyanTimezone = 'Africa/Nairobi';
        
        // Convert UTC dates to Kenyan timezone for comparison
        $startDateKenyan = $startDate->copy()->setTimezone($kenyanTimezone);
        $endDateKenyan = $endDate->copy()->setTimezone($kenyanTimezone);
        
        $dayShiftOrders = collect();
        $nightShiftOrders = collect();
        
        foreach ($allOrders as $order) {
            // Convert order time to Kenyan timezone
            $orderTime = $order->created_at->setTimezone($kenyanTimezone);
            
            // Filter by date range in Kenyan timezone
            if ($orderTime->lt($startDateKenyan) || $orderTime->gt($endDateKenyan)) {
                continue; // Skip orders outside the selected date range
            }
            
            $orderHour = $orderTime->hour;
            $orderMinute = $orderTime->minute;
            
            // Calculate total minutes from midnight
            $totalMinutes = ($orderHour * 60) + $orderMinute;
            
            // Day shift: 6:30 AM (390 minutes) to 6:30 PM (1110 minutes)
            // Night shift: 6:30 PM (1110 minutes) to 6:30 AM next day (390 minutes)
            // Note: Using >= 390 and < 1110 for day shift ensures 6:30 AM is included in day shift
            // and 6:30 PM is included in night shift (consistent with applyShiftFilter)
            if ($totalMinutes >= 390 && $totalMinutes < 1110) {
                // Day shift: 6:30 AM to 6:30 PM
                $dayShiftOrders->push($order);
            } else {
                // Night shift: 6:30 PM to 6:30 AM (next day)
                $nightShiftOrders->push($order);
            }
        }
        
        // Calculate detailed breakdown for each shift
        $dayShiftBreakdown = $this->calculateShiftDetails($dayShiftOrders, 'Day Shift', '6:30 AM - 6:30 PM');
        $nightShiftBreakdown = $this->calculateShiftDetails($nightShiftOrders, 'Night Shift', '6:30 PM - 6:30 AM');
        
        return [
            'day_shift' => $dayShiftBreakdown,
            'night_shift' => $nightShiftBreakdown,
            'comparison' => [
                'day_vs_night_revenue' => [
                    'day_percentage' => $dayShiftBreakdown['gross_revenue']['amount'] + $nightShiftBreakdown['gross_revenue']['amount'] > 0 
                        ? round(($dayShiftBreakdown['gross_revenue']['amount'] / ($dayShiftBreakdown['gross_revenue']['amount'] + $nightShiftBreakdown['gross_revenue']['amount'])) * 100, 2) 
                        : 0,
                    'night_percentage' => $dayShiftBreakdown['gross_revenue']['amount'] + $nightShiftBreakdown['gross_revenue']['amount'] > 0 
                        ? round(($nightShiftBreakdown['gross_revenue']['amount'] / ($dayShiftBreakdown['gross_revenue']['amount'] + $nightShiftBreakdown['gross_revenue']['amount'])) * 100, 2) 
                        : 0,
                ],
                'performance_winner' => $dayShiftBreakdown['gross_revenue']['amount'] > $nightShiftBreakdown['gross_revenue']['amount'] ? 'day' : 'night',
                'revenue_difference' => abs($dayShiftBreakdown['gross_revenue']['amount'] - $nightShiftBreakdown['gross_revenue']['amount']),
                'formatted_revenue_difference' => 'KES ' . number_format(abs($dayShiftBreakdown['gross_revenue']['amount'] - $nightShiftBreakdown['gross_revenue']['amount']), 2)
            ]
        ];
    }
    
    /**
     * Calculate detailed metrics for a specific shift
     */
    private function calculateShiftDetails($shiftOrders, $shiftName, $shiftTime)
    {
        // Separate orders by status
        $completedOrders = $shiftOrders->where('status', 'completed');
        $pendingOrders = $shiftOrders->whereIn('status', ['pending', 'confirmed', 'preparing', 'ready']);
        $cancelledOrders = $shiftOrders->where('status', 'cancelled');
        
        // Track cancelled orders that were later paid
        $cancelledButPaidOrders = $shiftOrders->where('was_cancelled', true)
            ->where('status', 'completed');
        
        // Track currently cancelled orders
        $currentlyCancelledOrders = $shiftOrders->where('status', 'cancelled');
        
        // Get payment information for completed orders
        $completedOrderIds = $completedOrders->pluck('id');
        $paidOrders = collect();
        $unpaidOrders = collect();
        
        if ($completedOrderIds->isNotEmpty()) {
            $paidOrderIds = Payment::whereIn('order_id', $completedOrderIds)
                ->where('status', 'completed')
                ->pluck('order_id')
                ->unique();
            
            $paidOrders = $completedOrders->whereIn('id', $paidOrderIds);
            $unpaidOrders = $completedOrders->whereNotIn('id', $paidOrderIds);
        }
        
        // Calculate revenue metrics
        $grossRevenue = $shiftOrders->whereNotIn('status', ['cancelled'])->sum('total_amount');
        $netRevenue = $paidOrders->sum('total_amount');
        $pendingRevenue = $pendingOrders->sum('total_amount');
        $unpaidCompletedRevenue = $unpaidOrders->sum('total_amount');
        
        return [
            'shift_info' => [
                'name' => $shiftName,
                'time_range' => $shiftTime,
                'timezone' => 'Africa/Nairobi (Kenyan Time)'
            ],
            'gross_revenue' => [
                'amount' => $grossRevenue,
                'formatted' => 'KES ' . number_format($grossRevenue, 2),
                'orders_count' => $shiftOrders->whereNotIn('status', ['cancelled'])->count(),
                'description' => $shiftName . ' total revenue (completed + pending)'
            ],
            'net_revenue' => [
                'amount' => $netRevenue,
                'formatted' => 'KES ' . number_format($netRevenue, 2),
                'orders_count' => $paidOrders->count(),
                'percentage_of_gross' => $grossRevenue > 0 ? round(($netRevenue / $grossRevenue) * 100, 2) : 0,
                'description' => $shiftName . ' completed and paid orders'
            ],
            'pending_revenue' => [
                'amount' => $pendingRevenue,
                'formatted' => 'KES ' . number_format($pendingRevenue, 2),
                'orders_count' => $pendingOrders->count(),
                'percentage_of_gross' => $grossRevenue > 0 ? round(($pendingRevenue / $grossRevenue) * 100, 2) : 0,
                'description' => $shiftName . ' orders in progress'
            ],
            'unpaid_completed_revenue' => [
                'amount' => $unpaidCompletedRevenue,
                'formatted' => 'KES ' . number_format($unpaidCompletedRevenue, 2),
                'orders_count' => $unpaidOrders->count(),
                'percentage_of_gross' => $grossRevenue > 0 ? round(($unpaidCompletedRevenue / $grossRevenue) * 100, 2) : 0,
                'description' => $shiftName . ' completed but unpaid orders'
            ],
            'order_breakdown' => [
                'total_orders' => $shiftOrders->count(),
                'completed_orders' => $completedOrders->count(),
                'pending_orders' => $pendingOrders->count(),
                'cancelled_orders' => $cancelledOrders->count(),
                'paid_orders' => $paidOrders->count(),
                'unpaid_orders' => $unpaidOrders->count(),
                'cancelled_tracking' => [
                    'currently_cancelled' => $currentlyCancelledOrders->count(),
                    'cancelled_but_later_paid' => $cancelledButPaidOrders->count(),
                    'cancelled_but_later_paid_revenue' => $cancelledButPaidOrders->sum('total_amount'),
                    'formatted_cancelled_but_later_paid_revenue' => 'KES ' . number_format($cancelledButPaidOrders->sum('total_amount'), 2)
                ]
            ],
            'order_types' => $shiftOrders->whereNotIn('status', ['cancelled'])->groupBy('type')->map(function ($typeOrders, $type) use ($grossRevenue) {
                $typeRevenue = $typeOrders->sum('total_amount');
                return [
                    'count' => $typeOrders->count(),
                    'revenue' => $typeRevenue,
                    'formatted_revenue' => 'KES ' . number_format($typeRevenue, 2),
                    'percentage' => $grossRevenue > 0 ? round(($typeRevenue / $grossRevenue) * 100, 2) : 0
                ];
            }),
            'hourly_breakdown' => $shiftOrders->whereNotIn('status', ['cancelled'])->groupBy(function ($order) {
                return $order->created_at->setTimezone('Africa/Nairobi')->format('H:00');
            })->map(function ($hourOrders) {
                return [
                    'orders' => $hourOrders->count(),
                    'revenue' => $hourOrders->sum('total_amount'),
                    'formatted_revenue' => 'KES ' . number_format($hourOrders->sum('total_amount'), 2)
                ];
            })->sortKeys(),
            'average_metrics' => [
                'average_order_value' => $shiftOrders->whereNotIn('status', ['cancelled'])->count() > 0 
                    ? $shiftOrders->whereNotIn('status', ['cancelled'])->avg('total_amount') 
                    : 0,
                'formatted_average_order_value' => $shiftOrders->whereNotIn('status', ['cancelled'])->count() > 0 
                    ? 'KES ' . number_format($shiftOrders->whereNotIn('status', ['cancelled'])->avg('total_amount'), 2) 
                    : 'KES 0.00',
                'orders_per_hour' => $shiftTime === '6:30 AM - 6:30 PM' 
                    ? round($shiftOrders->whereNotIn('status', ['cancelled'])->count() / 12, 2) // 12 hours for day shift
                    : round($shiftOrders->whereNotIn('status', ['cancelled'])->count() / 12, 2), // 12 hours for night shift
                'revenue_per_hour' => $shiftTime === '6:30 AM - 6:30 PM' 
                    ? round($grossRevenue / 12, 2) // 12 hours for day shift
                    : round($grossRevenue / 12, 2) // 12 hours for night shift
            ]
        ];
    }

    /**
     * Get shift display name
     */
    private function getShiftDisplayName($shift)
    {
        return match($shift) {
            'dayshift' => 'Day Shift (6:30 AM - 6:30 PM)',
            'nightshift' => 'Night Shift (6:30 PM - 6:30 AM)',
            '24hrs' => '24 Hours (6:30 AM - 6:30 AM)',
            default => 'All Hours'
        };
    }

    public function salesSummary(Request $request): JsonResponse
    {
        $request->validate([
            'start_date' => 'required|date',
            'start_time' => 'nullable|date_format:H:i',
            'end_date' => 'required|date|after_or_equal:start_date',
            'end_time' => 'nullable|date_format:H:i',
            'branch_id' => 'nullable|exists:branches,id',
            'shift' => 'nullable|in:24hrs,dayshift,nightshift',
        ]);

        $shift = $request->shift ?? '24hrs';
        
        // Enhanced date parsing with time support and Kenyan timezone
        // Smart time handling based on shift selection
        
        // Determine if we should use shift-based times or user-provided times
        $useShiftTimes = !$request->start_time && !$request->end_time;
        
        if ($useShiftTimes) {
            // No times provided - use shift-appropriate defaults
            if ($shift === 'dayshift') {
                // Day shift: 6:30 AM to 6:30 PM on the same day
                $startTime = '06:30';
                $endTime = '18:30';
            } elseif ($shift === 'nightshift') {
                // Night shift: 6:30 PM to 6:30 AM next day
                $startTime = '18:30';
                $endTime = '06:30';
                // Adjust end date to next day for night shift
                $endDate = Carbon::parse($request->end_date)->addDay()->format('Y-m-d');
                $request->merge(['end_date' => $endDate]);
            } elseif ($shift === '24hrs') {
                // 24hrs: 6:30 AM to 6:30 AM next day
                $startTime = '06:30';
                $endTime = '06:30';
                // Adjust end date to next day for 24hrs
                $endDate = Carbon::parse($request->end_date)->addDay()->format('Y-m-d');
                $request->merge(['end_date' => $endDate]);
            } else {
                // No shift specified, use full day
                $startTime = '00:00';
                $endTime = '23:59';
            }
        } else {
            // Use provided times (manual entry takes precedence)
            $startTime = $request->start_time ?? '00:00';
            $endTime = $request->end_time ?? '23:59';
        }
        
        // Create dates in Kenyan timezone
        $startDate = Carbon::createFromFormat('Y-m-d H:i', $request->start_date . ' ' . $startTime, 'Africa/Nairobi');
        $endDate = Carbon::createFromFormat('Y-m-d H:i', $request->end_date . ' ' . $endTime, 'Africa/Nairobi');
        
        // IMPORTANT: Ensure end date is after start date
        // If they're the same and we have a shift filter, this is likely a UI issue
        // The shift filter will handle the time-based filtering
        if ($startDate->equalTo($endDate)) {
            // If start and end are exactly the same, add 1 second to end to create a valid range
            $endDate->addSecond();
        }
        
        // Note: Database stores times in Africa/Nairobi timezone (app.timezone config)
        // So we keep dates in Kenyan timezone for queries
        
        $branchId = $request->branch_id ?? $request->user()->branch_id;

        // Get all orders with shift filtering
        $allOrdersQuery = Order::where('restaurant_id', $request->user()->restaurant_id);
        
        if ($branchId) {
            $allOrdersQuery->where('branch_id', $branchId);
        }

        // Apply shift filtering
        $allOrdersQuery = $this->applyShiftFilter($allOrdersQuery, $startDate, $endDate, $shift);
        $allOrders = $allOrdersQuery->get();
        
        // Only apply PHP shift filtering if:
        // 1. A specific shift is selected (not 24hrs), AND
        // 2. User provided custom times (not using auto-adjusted shift times)
        // This prevents double-filtering when auto-adjusted times are used
        if ($shift && $shift !== '24hrs' && !$useShiftTimes) {
            $allOrders = $allOrders->filter(function($order) use ($shift) {
                $orderTime = $order->created_at->setTimezone('Africa/Nairobi');
                $totalMinutes = ($orderTime->hour * 60) + $orderTime->minute;
                
                if ($shift === 'dayshift') {
                    // Day shift: 6:30 AM (390 min) to 6:30 PM (1110 min)
                    return $totalMinutes >= 390 && $totalMinutes < 1110;
                } elseif ($shift === 'nightshift') {
                    // Night shift: before 6:30 AM or after 6:30 PM
                    return $totalMinutes < 390 || $totalMinutes >= 1110;
                }
                
                return true;
            });
        }

        // Separate orders by status for detailed revenue breakdown
        $completedOrders = $allOrders->where('status', 'completed');
        $pendingOrders = $allOrders->whereIn('status', ['pending', 'confirmed', 'preparing', 'ready']);
        $cancelledOrders = $allOrders->where('status', 'cancelled');
        
        // Track cancelled orders that were later paid
        $cancelledButPaidOrders = $allOrders->where('was_cancelled', true)
            ->where('status', 'completed');
        
        // Track currently cancelled orders
        $currentlyCancelledOrders = $allOrders->where('status', 'cancelled');

        // Get payment information for completed orders
        $completedOrderIds = $completedOrders->pluck('id');
        $paidOrders = collect();
        $unpaidOrders = collect();

        if ($completedOrderIds->isNotEmpty()) {
            $paidOrderIds = Payment::whereIn('order_id', $completedOrderIds)
                ->where('status', 'completed')
                ->pluck('order_id')
                ->unique();
            
            $paidOrders = $completedOrders->whereIn('id', $paidOrderIds);
            $unpaidOrders = $completedOrders->whereNotIn('id', $paidOrderIds);
        }

        // Calculate revenue breakdowns
        $grossRevenue = $allOrders->whereNotIn('status', ['cancelled'])->sum('total_amount');
        $netRevenue = $paidOrders->sum('total_amount');
        $pendingRevenue = $pendingOrders->sum('total_amount');
        $unpaidCompletedRevenue = $unpaidOrders->sum('total_amount');

        $summary = [
            // Shift Information
            'shift_filter' => [
                'shift' => $shift,
                'shift_display' => $this->getShiftDisplayName($shift),
                'date_range' => [
                    'start' => $startDate->format('Y-m-d'),
                    'end' => $endDate->format('Y-m-d')
                ]
            ],

            // Enhanced Revenue Breakdown
            'revenue_breakdown' => [
                'gross_revenue' => [
                    'amount' => $grossRevenue,
                    'formatted' => 'KES ' . number_format($grossRevenue, 2),
                    'description' => 'All sales (dine-in, takeaway, delivery) - completed paid + unpaid + pending',
                    'orders_count' => $allOrders->whereNotIn('status', ['cancelled'])->count(),
                    'breakdown' => [
                        'completed_paid' => [
                            'amount' => $paidOrders->sum('total_amount'),
                            'formatted' => 'KES ' . number_format($paidOrders->sum('total_amount'), 2),
                            'orders_count' => $paidOrders->count(),
                            'percentage' => $grossRevenue > 0 ? round(($paidOrders->sum('total_amount') / $grossRevenue) * 100, 2) : 0
                        ],
                        'completed_unpaid' => [
                            'amount' => $unpaidCompletedRevenue,
                            'formatted' => 'KES ' . number_format($unpaidCompletedRevenue, 2),
                            'orders_count' => $unpaidOrders->count(),
                            'percentage' => $grossRevenue > 0 ? round(($unpaidCompletedRevenue / $grossRevenue) * 100, 2) : 0
                        ],
                        'pending' => [
                            'amount' => $pendingRevenue,
                            'formatted' => 'KES ' . number_format($pendingRevenue, 2),
                            'orders_count' => $pendingOrders->count(),
                            'percentage' => $grossRevenue > 0 ? round(($pendingRevenue / $grossRevenue) * 100, 2) : 0
                        ]
                    ]
                ],
                'net_revenue' => [
                    'amount' => $netRevenue,
                    'formatted' => 'KES ' . number_format($netRevenue, 2),
                    'description' => 'All completed and paid orders',
                    'orders_count' => $paidOrders->count(),
                    'percentage_of_gross' => $grossRevenue > 0 ? round(($netRevenue / $grossRevenue) * 100, 2) : 0
                ],
                'pending_revenue' => [
                    'amount' => $pendingRevenue,
                    'formatted' => 'KES ' . number_format($pendingRevenue, 2),
                    'description' => 'Orders still being processed',
                    'orders_count' => $pendingOrders->count(),
                    'percentage_of_gross' => $grossRevenue > 0 ? round(($pendingRevenue / $grossRevenue) * 100, 2) : 0,
                    'breakdown_by_status' => $pendingOrders->groupBy('status')->map(function ($statusOrders, $status) {
                        return [
                            'count' => $statusOrders->count(),
                            'amount' => $statusOrders->sum('total_amount'),
                            'formatted' => 'KES ' . number_format($statusOrders->sum('total_amount'), 2),
                            'status_display' => ucfirst($status)
                        ];
                    })
                ],
                'unpaid_completed_revenue' => [
                    'amount' => $unpaidCompletedRevenue,
                    'formatted' => 'KES ' . number_format($unpaidCompletedRevenue, 2),
                    'description' => 'Completed orders awaiting payment',
                    'orders_count' => $unpaidOrders->count(),
                    'percentage_of_gross' => $grossRevenue > 0 ? round(($unpaidCompletedRevenue / $grossRevenue) * 100, 2) : 0
                ]
            ],

            // Detailed Order Type Revenue Breakdown
            'order_type_revenue_breakdown' => $this->getOrderTypeRevenueBreakdown($allOrders, $grossRevenue),

            // Detailed Shift Breakdown (only for 24hrs reports)
            'shift_breakdown' => $shift === '24hrs' ? $this->getShiftBreakdown($allOrders, $startDate, $endDate) : null,

            // Traditional metrics (for backward compatibility)
            'total_orders' => $allOrders->count(),
            'total_revenue' => $grossRevenue, // Gross revenue as total
            'net_revenue' => $netRevenue,
            'pending_revenue' => $pendingRevenue,
            'total_tax' => $completedOrders->sum('tax_amount'),
            'total_discounts' => $completedOrders->sum('discount_amount'),
            'average_order_value' => $completedOrders->count() > 0 ? $completedOrders->avg('total_amount') : 0,
            
            // Order status breakdown
            'order_status_breakdown' => [
                'completed' => [
                    'count' => $completedOrders->count(),
                    'revenue' => $completedOrders->sum('total_amount'),
                    'formatted_revenue' => 'KES ' . number_format($completedOrders->sum('total_amount'), 2),
                    'paid_count' => $paidOrders->count(),
                    'unpaid_count' => $unpaidOrders->count()
                ],
                'pending' => [
                    'count' => $pendingOrders->count(),
                    'revenue' => $pendingRevenue,
                    'formatted_revenue' => 'KES ' . number_format($pendingRevenue, 2)
                ],
                'cancelled' => [
                    'count' => $cancelledOrders->count(),
                    'revenue' => $cancelledOrders->sum('total_amount'),
                    'formatted_revenue' => 'KES ' . number_format($cancelledOrders->sum('total_amount'), 2),
                    'currently_cancelled' => [
                        'count' => $currentlyCancelledOrders->count(),
                        'revenue' => $currentlyCancelledOrders->sum('total_amount'),
                        'formatted_revenue' => 'KES ' . number_format($currentlyCancelledOrders->sum('total_amount'), 2)
                    ],
                    'cancelled_but_later_paid' => [
                        'count' => $cancelledButPaidOrders->count(),
                        'revenue' => $cancelledButPaidOrders->sum('total_amount'),
                        'formatted_revenue' => 'KES ' . number_format($cancelledButPaidOrders->sum('total_amount'), 2),
                        'description' => 'Orders that were cancelled but later completed and paid'
                    ]
                ]
            ],
            
            'order_types' => $allOrders->whereNotIn('status', ['cancelled'])->groupBy('type')->map(function ($typeOrders, $type) {
                return [
                    'count' => $typeOrders->count(),
                    'revenue' => $typeOrders->sum('total_amount'),
                    'percentage' => 0, // Will be calculated below
                ];
            }),
            
            'payment_methods' => $this->getDetailedPaymentMethods($completedOrderIds),
            
            'hourly_sales' => $completedOrders->groupBy(function ($order) {
                return $order->created_at->format('H:00');
            })->map(function ($hourOrders) {
                return [
                    'orders' => $hourOrders->count(),
                    'revenue' => $hourOrders->sum('total_amount'),
                ];
            })->sortKeys(),
            
            'daily_sales' => $completedOrders->groupBy(function ($order) {
                return $order->created_at->format('Y-m-d');
            })->map(function ($dayOrders) {
                return [
                    'orders' => $dayOrders->count(),
                    'revenue' => $dayOrders->sum('total_amount'),
                ];
            })->sortKeys(),

            // Collection efficiency metrics
            'collection_metrics' => [
                'collection_rate' => $completedOrders->count() > 0 ? 
                    round(($paidOrders->count() / $completedOrders->count()) * 100, 2) : 0,
                'average_paid_order_value' => $paidOrders->count() > 0 ? 
                    round($paidOrders->avg('total_amount'), 2) : 0,
                'average_unpaid_order_value' => $unpaidOrders->count() > 0 ? 
                    round($unpaidOrders->avg('total_amount'), 2) : 0,
                'formatted_avg_paid' => $paidOrders->count() > 0 ? 
                    'KES ' . number_format($paidOrders->avg('total_amount'), 2) : 'KES 0.00',
                'formatted_avg_unpaid' => $unpaidOrders->count() > 0 ? 
                    'KES ' . number_format($unpaidOrders->avg('total_amount'), 2) : 'KES 0.00'
            ]
        ];

        // Calculate percentages for order types
        if ($grossRevenue > 0) {
            $orderTypes = $summary['order_types']->toArray();
            foreach ($orderTypes as $type => $data) {
                $orderTypes[$type]['percentage'] = 
                    round(($data['revenue'] / $grossRevenue) * 100, 2);
            }
            $summary['order_types'] = $orderTypes;
        }

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

    public function topSellingItems(Request $request): JsonResponse
    {
        $request->validate([
            'start_date' => 'required|date',
            'start_time' => 'nullable|date_format:H:i',
            'end_date' => 'required|date|after_or_equal:start_date',
            'end_time' => 'nullable|date_format:H:i',
            'branch_id' => 'nullable|exists:branches,id',
            'shift' => 'nullable|in:24hrs,dayshift,nightshift',
            'status_filter' => 'nullable|in:all,completed,pending',
        ]);

        // Enhanced date parsing with time support and Kenyan timezone
        $startTime = $request->start_time ?? '00:00';
        $endTime = $request->end_time ?? '23:59';
        
        $startDate = Carbon::createFromFormat('Y-m-d H:i', $request->start_date . ' ' . $startTime, 'Africa/Nairobi');
        $endDate = Carbon::createFromFormat('Y-m-d H:i', $request->end_date . ' ' . $endTime, 'Africa/Nairobi');
        
        // Convert to UTC for database queries
        $startDate = $startDate->utc();
        $endDate = $endDate->utc();
        
        $branchId = $request->branch_id ?? $request->user()->branch_id;
        $shift = $request->shift ?? '24hrs';
        $statusFilter = $request->status_filter ?? 'all';

        $query = OrderItem::join('orders', 'order_items.order_id', '=', 'orders.id')
            ->join('menu_items', 'order_items.menu_item_id', '=', 'menu_items.id')
            ->join('categories', 'menu_items.category_id', '=', 'categories.id')
            ->where('orders.restaurant_id', $request->user()->restaurant_id);

        // Apply status filtering
        if ($statusFilter === 'completed') {
            $query->where('orders.status', 'completed');
        } elseif ($statusFilter === 'pending') {
            $query->whereIn('orders.status', ['pending', 'confirmed', 'preparing', 'ready']);
        } else {
            // 'all' - include both completed and pending orders (exclude cancelled)
            $query->whereNotIn('orders.status', ['cancelled']);
        }

        if ($branchId) {
            $query->where('orders.branch_id', $branchId);
        }

        // Apply shift filtering
        $query = $this->applyShiftFilter($query, $startDate, $endDate, $shift, 'orders');

        $allItems = $query->selectRaw('
                menu_items.id,
                menu_items.name,
                categories.name as category_name,
                SUM(order_items.quantity) as total_quantity,
                SUM(order_items.total_price) as total_revenue,
                AVG(order_items.item_price) as average_price,
                COUNT(DISTINCT orders.id) as order_count
            ')
            ->groupBy('menu_items.id', 'menu_items.name', 'categories.name')
            ->orderBy('total_quantity', 'desc')
            ->get();

        // Add status filter info to response
        $response = [
            'status_filter' => $statusFilter,
            'status_display' => $this->getStatusFilterDisplay($statusFilter),
            'items' => $allItems->map(function ($item) {
                return [
                    'id' => $item->id,
                    'name' => $item->name,
                    'category_name' => $item->category_name,
                    'total_quantity' => $item->total_quantity,
                    'total_revenue' => $item->total_revenue,
                    'average_price' => $item->average_price,
                    'order_count' => $item->order_count,
                    'formatted_revenue' => 'KES ' . number_format($item->total_revenue, 2),
                    'formatted_average_price' => 'KES ' . number_format($item->average_price, 2),
                ];
            }),
            'summary' => [
                'total_items' => $allItems->count(),
                'total_quantity_sold' => $allItems->sum('total_quantity'),
                'total_revenue' => $allItems->sum('total_revenue'),
                'total_orders' => $allItems->sum('order_count'),
                'formatted_total_revenue' => 'KES ' . number_format($allItems->sum('total_revenue'), 2),
            ]
        ];

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

    /**
     * Get display name for status filter
     */
    private function getStatusFilterDisplay($statusFilter)
    {
        return match($statusFilter) {
            'completed' => 'Completed Orders Only',
            'pending' => 'Pending Orders Only (Pending, Confirmed, Preparing, Ready)',
            'all' => 'All Orders (Completed + Pending)',
            default => 'All Orders'
        };
    }

    public function staffPerformance(Request $request): JsonResponse
    {
        $request->validate([
            'start_date' => 'required|date',
            'start_time' => 'nullable|date_format:H:i',
            'end_date' => 'required|date|after_or_equal:start_date',
            'end_time' => 'nullable|date_format:H:i',
            'branch_id' => 'nullable|exists:branches,id',
            'shift' => 'nullable|in:24hrs,dayshift,nightshift',
        ]);

        // Enhanced date parsing with time support and Kenyan timezone
        $startTime = $request->start_time ?? '00:00';
        $endTime = $request->end_time ?? '23:59';
        
        $startDate = Carbon::createFromFormat('Y-m-d H:i', $request->start_date . ' ' . $startTime, 'Africa/Nairobi');
        $endDate = Carbon::createFromFormat('Y-m-d H:i', $request->end_date . ' ' . $endTime, 'Africa/Nairobi');
        
        // Convert to UTC for database queries
        $startDate = $startDate->utc();
        $endDate = $endDate->utc();
        $branchId = $request->branch_id ?? $request->user()->branch_id;
        $shift = $request->shift ?? '24hrs';

        $query = Order::join('users', 'orders.user_id', '=', 'users.id')
            ->where('orders.restaurant_id', $request->user()->restaurant_id)
            ->where('orders.status', 'completed');

        if ($branchId) {
            $query->where('orders.branch_id', $branchId);
        }

        // Apply shift filtering
        $query = $this->applyShiftFilter($query, $startDate, $endDate, $shift, 'orders');

        $performance = $query->selectRaw('
                users.id,
                users.name,
                users.role,
                COUNT(orders.id) as total_orders,
                SUM(orders.total_amount) as total_sales,
                AVG(orders.total_amount) as average_order_value,
                SUM(orders.total_amount) / COUNT(orders.id) as efficiency_score
            ')
            ->groupBy('users.id', 'users.name', 'users.role')
            ->orderBy('total_sales', 'desc')
            ->get();

        // Add formatted values and additional metrics
        $performance = $performance->map(function ($staff) use ($startDate, $endDate) {
            // Get payment data for this staff member
            $payments = Payment::whereHas('order', function ($q) use ($staff, $startDate, $endDate) {
                $q->where('user_id', $staff->id)
                  ->where('status', 'completed')
                  ->whereBetween('created_at', [$startDate, $endDate]);
            })->where('status', 'completed')->get();

            // Calculate additional metrics
            $totalPayments = $payments->sum('amount');
            $paymentMethods = $payments->groupBy('method')->map(function ($methodPayments) {
                return [
                    'count' => $methodPayments->count(),
                    'total' => $methodPayments->sum('amount')
                ];
            });

            // Get orders by day for trend analysis
            $dailyOrders = Order::where('user_id', $staff->id)
                ->where('status', 'completed')
                ->whereBetween('created_at', [$startDate, $endDate])
                ->selectRaw('DATE(created_at) as date, COUNT(*) as orders, SUM(total_amount) as sales')
                ->groupBy('date')
                ->orderBy('date')
                ->get();

            return [
                'id' => $staff->id,
                'name' => $staff->name,
                'role' => $staff->role,
                'total_orders' => $staff->total_orders,
                'total_sales' => $staff->total_sales,
                'average_order_value' => $staff->average_order_value,
                'efficiency_score' => $staff->efficiency_score,
                'formatted_sales' => 'KES ' . number_format($staff->total_sales, 2),
                'formatted_average' => 'KES ' . number_format($staff->average_order_value, 2),
                'total_payments' => $totalPayments,
                'formatted_payments' => 'KES ' . number_format($totalPayments, 2),
                'payment_methods' => $paymentMethods,
                'daily_performance' => $dailyOrders,
                'performance_rating' => $this->calculatePerformanceRating($staff->total_orders, $staff->total_sales, $staff->average_order_value),
            ];
        });

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

    private function calculatePerformanceRating($orders, $sales, $avgOrderValue)
    {
        $score = 0;
        
        // Orders volume (40% weight)
        if ($orders >= 50) $score += 40;
        elseif ($orders >= 30) $score += 30;
        elseif ($orders >= 15) $score += 20;
        elseif ($orders >= 5) $score += 10;
        
        // Sales volume (40% weight)
        if ($sales >= 100000) $score += 40;
        elseif ($sales >= 50000) $score += 30;
        elseif ($sales >= 25000) $score += 20;
        elseif ($sales >= 10000) $score += 10;
        
        // Average order value (20% weight)
        if ($avgOrderValue >= 2000) $score += 20;
        elseif ($avgOrderValue >= 1500) $score += 15;
        elseif ($avgOrderValue >= 1000) $score += 10;
        elseif ($avgOrderValue >= 500) $score += 5;
        
        if ($score >= 80) return 'Excellent';
        elseif ($score >= 60) return 'Good';
        elseif ($score >= 40) return 'Average';
        elseif ($score >= 20) return 'Below Average';
        else return 'Needs Improvement';
    }

    public function customerAnalytics(Request $request): JsonResponse
    {
        $request->validate([
            'start_date' => 'required|date',
            'start_time' => 'nullable|date_format:H:i',
            'end_date' => 'required|date|after_or_equal:start_date',
            'end_time' => 'nullable|date_format:H:i',
            'branch_id' => 'nullable|exists:branches,id',
            'shift' => 'nullable|in:24hrs,dayshift,nightshift',
        ]);

        // Enhanced date parsing with time support and Kenyan timezone
        $startTime = $request->start_time ?? '00:00';
        $endTime = $request->end_time ?? '23:59';
        
        $startDate = Carbon::createFromFormat('Y-m-d H:i', $request->start_date . ' ' . $startTime, 'Africa/Nairobi');
        $endDate = Carbon::createFromFormat('Y-m-d H:i', $request->end_date . ' ' . $endTime, 'Africa/Nairobi');
        
        // Convert to UTC for database queries
        $startDate = $startDate->utc();
        $endDate = $endDate->utc();
        $branchId = $request->branch_id ?? $request->user()->branch_id;
        $shift = $request->shift ?? '24hrs';

        $query = Order::where('restaurant_id', $request->user()->restaurant_id)
            ->where('status', 'completed');

        if ($branchId) {
            $query->where('branch_id', $branchId);
        }

        // Apply shift filtering
        $query = $this->applyShiftFilter($query, $startDate, $endDate, $shift);

        $orders = $query->get();

        $analytics = [
            'total_customers' => $orders->whereNotNull('customer_id')->unique('customer_id')->count(),
            'new_customers' => Customer::where('restaurant_id', $request->user()->restaurant_id)
                ->whereBetween('created_at', [$startDate, $endDate])
                ->count(),
            
            'customer_types' => Customer::where('restaurant_id', $request->user()->restaurant_id)
                ->selectRaw('
                    CASE 
                        WHEN total_orders >= 50 THEN "VIP"
                        WHEN total_orders >= 20 THEN "Loyal"
                        WHEN total_orders >= 5 THEN "Regular"
                        ELSE "New"
                    END as customer_type,
                    COUNT(*) as count,
                    SUM(total_spent) as total_spent
                ')
                ->groupBy('customer_type')
                ->get()
                ->keyBy('customer_type'),
            
            'repeat_customers' => $orders->whereNotNull('customer_id')
                ->groupBy('customer_id')
                ->filter(function ($customerOrders) {
                    return $customerOrders->count() > 1;
                })
                ->count(),
            
            'average_customer_value' => $orders->whereNotNull('customer_id')
                ->groupBy('customer_id')
                ->map(function ($customerOrders) {
                    return $customerOrders->sum('total_amount');
                })
                ->avg(),
        ];

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

    public function inventoryReport(Request $request): JsonResponse
    {
        $request->validate([
            'branch_id' => 'nullable|exists:branches,id',
        ]);

        $branchId = $request->branch_id ?? $request->user()->branch_id;

        $query = \App\Models\InventoryItem::where('restaurant_id', $request->user()->restaurant_id);

        if ($branchId) {
            $query->where(function ($q) use ($branchId) {
                $q->where('branch_id', $branchId)->orWhereNull('branch_id');
            });
        }

        $items = $query->with(['stockMovements' => function ($q) {
            $q->whereDate('created_at', today());
        }])->get();

        $report = [
            'summary' => [
                'total_items' => $items->count(),
                'total_value' => $items->sum('stock_value'),
                'low_stock_items' => $items->where('is_low_stock', true)->count(),
                'out_of_stock_items' => $items->where('is_out_of_stock', true)->count(),
                'expiring_items' => $items->where('is_expiring_soon', true)->count(),
            ],
            
            'by_category' => $items->groupBy('category')->map(function ($categoryItems, $category) {
                return [
                    'count' => $categoryItems->count(),
                    'value' => $categoryItems->sum('stock_value'),
                    'low_stock' => $categoryItems->where('is_low_stock', true)->count(),
                ];
            }),
            
            'top_value_items' => $items->sortByDesc('stock_value')->take(10)->values(),
            'low_stock_items' => $items->where('is_low_stock', true)->values(),
            'expiring_items' => $items->where('is_expiring_soon', true)->values(),
            
            'todays_movements' => $items->flatMap(function ($item) {
                return $item->stockMovements->map(function ($movement) use ($item) {
                    return [
                        'item_name' => $item->name,
                        'type' => $movement->type_display,
                        'direction' => $movement->direction_display,
                        'quantity' => $movement->formatted_quantity,
                        'created_at' => $movement->created_at,
                    ];
                });
            })->sortByDesc('created_at')->values(),
        ];

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

    public function financialSummary(Request $request): JsonResponse
    {
        $request->validate([
            'start_date' => 'required|date',
            'start_time' => 'nullable|date_format:H:i',
            'end_date' => 'required|date|after_or_equal:start_date',
            'end_time' => 'nullable|date_format:H:i',
            'branch_id' => 'nullable|exists:branches,id',
            'shift' => 'nullable|in:24hrs,dayshift,nightshift',
        ]);

        // Enhanced date parsing with time support and Kenyan timezone
        $startTime = $request->start_time ?? '00:00';
        $endTime = $request->end_time ?? '23:59';
        
        $startDate = Carbon::createFromFormat('Y-m-d H:i', $request->start_date . ' ' . $startTime, 'Africa/Nairobi');
        $endDate = Carbon::createFromFormat('Y-m-d H:i', $request->end_date . ' ' . $endTime, 'Africa/Nairobi');
        
        // Convert to UTC for database queries
        $startDate = $startDate->utc();
        $endDate = $endDate->utc();
        $branchId = $request->branch_id ?? $request->user()->branch_id;
        $shift = $request->shift ?? '24hrs';

        // For expense_date filtering, use date-only comparison since expense_date is a DATE field
        $startDateOnly = $startDate->toDateString();
        $endDateOnly = $endDate->toDateString();

        $query = Order::where('restaurant_id', $request->user()->restaurant_id);

        if ($branchId) {
            $query->where('branch_id', $branchId);
        }

        // Apply shift filtering
        $query = $this->applyShiftFilter($query, $startDate, $endDate, $shift);

        $completedOrders = $query->clone()->where('status', 'completed')->get();
        $allOrders = $query->clone()->get();

        // Get expense data for the same period - use whereDate for DATE field comparison
        $expenseQuery = \App\Models\Expense::where('restaurant_id', $request->user()->restaurant_id)
            ->whereDate('expense_date', '>=', $startDateOnly)
            ->whereDate('expense_date', '<=', $endDateOnly);

        if ($branchId) {
            $expenseQuery->where('branch_id', $branchId);
        }

        // Use database aggregations for expenses instead of loading all into memory
        $totalExpenses = (clone $expenseQuery)->whereIn('status', ['approved', 'paid'])->sum('amount');
        $pendingExpenses = (clone $expenseQuery)->where('status', 'pending')->sum('amount');
        $paidExpenses = (clone $expenseQuery)->where('status', 'paid')->sum('amount');
        $payLaterUnpaid = (clone $expenseQuery)->where('payment_status', 'pay_later')->whereNotIn('status', ['paid', 'rejected'])->sum('amount');
        $payLaterOverdue = (clone $expenseQuery)->where('payment_status', 'pay_later')->where('due_date', '<', now())->whereNotIn('status', ['paid', 'rejected'])->sum('amount');
        $pettyCashExpenses = (clone $expenseQuery)->where('is_petty_cash', true)->whereIn('status', ['approved', 'paid'])->sum('amount');
        $regularExpenses = (clone $expenseQuery)->where('is_petty_cash', false)->whereIn('status', ['approved', 'paid'])->sum('amount');
        $expenseCount = (clone $expenseQuery)->count();
        $payLaterCount = (clone $expenseQuery)->where('payment_status', 'pay_later')->whereNotIn('status', ['paid', 'rejected'])->count();
        $overdueCount = (clone $expenseQuery)->where('payment_status', 'pay_later')->where('due_date', '<', now())->whereNotIn('status', ['paid', 'rejected'])->count();

        // Get petty cash transactions
        $pettyCashQuery = \App\Models\PettyCashTransaction::where('restaurant_id', $request->user()->restaurant_id)
            ->whereBetween('created_at', [$startDate, $endDate]);

        if ($branchId) {
            $pettyCashQuery->where('branch_id', $branchId);
        }

        $pettyCashTransactions = $pettyCashQuery->get();

        $summary = [
            'revenue' => [
                'gross_sales' => $completedOrders->sum('subtotal'),
                'tax_collected' => $completedOrders->sum('tax_amount'),
                'discounts_given' => $completedOrders->sum('discount_amount'),
                'net_sales' => $completedOrders->sum('total_amount'),
                'formatted_gross_sales' => 'KES ' . number_format($completedOrders->sum('subtotal'), 2),
                'formatted_tax_collected' => 'KES ' . number_format($completedOrders->sum('tax_amount'), 2),
                'formatted_discounts_given' => 'KES ' . number_format($completedOrders->sum('discount_amount'), 2),
                'formatted_net_sales' => 'KES ' . number_format($completedOrders->sum('total_amount'), 2),
            ],
            
            'orders' => [
                'total_orders' => $allOrders->count(),
                'completed_orders' => $completedOrders->count(),
                'cancelled_orders' => $allOrders->where('status', 'cancelled')->count(),
                'pending_orders' => $allOrders->whereIn('status', ['pending', 'confirmed', 'preparing', 'ready'])->count(),
                'average_order_value' => $completedOrders->count() > 0 ? $completedOrders->avg('total_amount') : 0,
                'formatted_average_order_value' => $completedOrders->count() > 0 ? 'KES ' . number_format($completedOrders->avg('total_amount'), 2) : 'KES 0.00',
            ],
            
            'expenses' => [
                'total_expenses' => $totalExpenses,
                'pending_expenses' => $pendingExpenses,
                'paid_expenses' => $paidExpenses,
                'pay_later_unpaid' => $payLaterUnpaid,
                'pay_later_overdue' => $payLaterOverdue,
                'petty_cash_expenses' => $pettyCashExpenses,
                'regular_expenses' => $regularExpenses,
                'expense_count' => $expenseCount,
                'pay_later_count' => $payLaterCount,
                'overdue_count' => $overdueCount,
                'formatted_total_expenses' => 'KES ' . number_format($totalExpenses, 2),
                'formatted_pending_expenses' => 'KES ' . number_format($pendingExpenses, 2),
                'formatted_pay_later_unpaid' => 'KES ' . number_format($payLaterUnpaid, 2),
                'formatted_pay_later_overdue' => 'KES ' . number_format($payLaterOverdue, 2),
                'formatted_petty_cash_expenses' => 'KES ' . number_format($pettyCashExpenses, 2),
            ],

            'petty_cash' => [
                'total_issued' => $pettyCashTransactions->where('type', 'withdrawal')->sum('amount'),
                'transaction_count' => $pettyCashTransactions->count(),
                'issues_count' => $pettyCashTransactions->where('type', 'withdrawal')->count(),
                'formatted_total_issued' => 'KES ' . number_format($pettyCashTransactions->where('type', 'withdrawal')->sum('amount'), 2),
            ],

            'profitability' => [
                'gross_profit' => $completedOrders->sum('total_amount') - $totalExpenses,
                'net_profit_margin' => $completedOrders->sum('total_amount') > 0 ? 
                    (($completedOrders->sum('total_amount') - $totalExpenses) / $completedOrders->sum('total_amount')) * 100 : 0,
                'expense_ratio' => $completedOrders->sum('total_amount') > 0 ? 
                    ($totalExpenses / $completedOrders->sum('total_amount')) * 100 : 0,
                'formatted_gross_profit' => 'KES ' . number_format($completedOrders->sum('total_amount') - $totalExpenses, 2),
            ],
            
            'payments' => Payment::whereIn('order_id', $completedOrders->pluck('id'))
                ->where('status', 'completed')
                ->selectRaw('method, SUM(amount) as total, COUNT(*) as count')
                ->groupBy('method')
                ->get()
                ->keyBy('method')
                ->map(function ($payment) {
                    return [
                        'total' => $payment->total,
                        'count' => $payment->count,
                        'formatted_total' => 'KES ' . number_format($payment->total, 2),
                    ];
                }),
            
            'trends' => $completedOrders->groupBy(function ($order) {
                return $order->created_at->format('Y-m-d');
            })->map(function ($dayOrders, $date) use ($expenseQuery) {
                // Query expenses for this specific date
                $dayExpensesTotal = (clone $expenseQuery)
                    ->whereDate('expense_date', $date)
                    ->whereIn('status', ['approved', 'paid'])
                    ->sum('amount');

                return [
                    'date' => $date,
                    'orders' => $dayOrders->count(),
                    'revenue' => $dayOrders->sum('total_amount'),
                    'expenses' => $dayExpensesTotal,
                    'profit' => $dayOrders->sum('total_amount') - $dayExpensesTotal,
                    'average_order_value' => $dayOrders->avg('total_amount'),
                    'formatted_revenue' => 'KES ' . number_format($dayOrders->sum('total_amount'), 2),
                    'formatted_expenses' => 'KES ' . number_format($dayExpensesTotal, 2),
                    'formatted_profit' => 'KES ' . number_format($dayOrders->sum('total_amount') - $dayExpensesTotal, 2),
                ];
            })->sortKeys(),
        ];

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

    public function waiterPerformance(Request $request): JsonResponse
    {
        $request->validate([
            'start_date' => 'required|date',
            'start_time' => 'nullable|date_format:H:i',
            'end_date' => 'required|date|after_or_equal:start_date',
            'end_time' => 'nullable|date_format:H:i',
            'branch_id' => 'nullable|exists:branches,id',
            'waiter_id' => 'nullable|exists:users,id',
            'shift' => 'nullable|in:24hrs,dayshift,nightshift',
        ]);

        // Enhanced date parsing with time support and Kenyan timezone
        $startTime = $request->start_time ?? '00:00';
        $endTime = $request->end_time ?? '23:59';
        
        $startDate = Carbon::createFromFormat('Y-m-d H:i', $request->start_date . ' ' . $startTime, 'Africa/Nairobi');
        $endDate = Carbon::createFromFormat('Y-m-d H:i', $request->end_date . ' ' . $endTime, 'Africa/Nairobi');
        
        // Convert to UTC for database queries
        $startDate = $startDate->utc();
        $endDate = $endDate->utc();
        $branchId = $request->branch_id ?? $request->user()->branch_id;
        $waiterId = $request->waiter_id;
        $shift = $request->shift ?? '24hrs';

        $query = Order::join('users', 'orders.user_id', '=', 'users.id')
            ->where('orders.restaurant_id', $request->user()->restaurant_id)
            ->where('orders.status', 'completed')
            ->whereIn('users.role', ['waiter', 'admin', 'manager']);

        if ($branchId) {
            $query->where('orders.branch_id', $branchId);
        }

        if ($waiterId) {
            $query->where('users.id', $waiterId);
        }

        // Apply shift filtering
        $query = $this->applyShiftFilter($query, $startDate, $endDate, $shift, 'orders');

        $waiters = $query->selectRaw('
                users.id,
                users.name,
                users.role,
                COUNT(orders.id) as total_orders,
                SUM(orders.total_amount) as total_sales,
                AVG(orders.total_amount) as average_order_value,
                MIN(orders.created_at) as first_order_date,
                MAX(orders.created_at) as last_order_date
            ')
            ->groupBy('users.id', 'users.name', 'users.role')
            ->orderBy('total_sales', 'desc')
            ->get();

        $waiterPerformance = $waiters->map(function ($waiter) use ($startDate, $endDate) {
            // Get detailed order information
            $orders = Order::where('user_id', $waiter->id)
                ->where('status', 'completed')
                ->whereBetween('created_at', [$startDate, $endDate])
                ->get();

            // Calculate payment method breakdown
            $payments = Payment::whereIn('order_id', $orders->pluck('id'))
                ->where('status', 'completed')
                ->get();

            $paymentBreakdown = $payments->groupBy('method')->map(function ($methodPayments) {
                return [
                    'count' => $methodPayments->count(),
                    'total' => $methodPayments->sum('amount'),
                    'formatted_total' => 'KES ' . number_format($methodPayments->sum('amount'), 2)
                ];
            });

            // Calculate order type breakdown
            $orderTypeBreakdown = $orders->groupBy('type')->map(function ($typeOrders) {
                return [
                    'count' => $typeOrders->count(),
                    'total' => $typeOrders->sum('total_amount'),
                    'formatted_total' => 'KES ' . number_format($typeOrders->sum('total_amount'), 2)
                ];
            });

            // Calculate daily performance
            $dailyPerformance = $orders->groupBy(function ($order) {
                return $order->created_at->format('Y-m-d');
            })->map(function ($dayOrders) {
                return [
                    'orders' => $dayOrders->count(),
                    'sales' => $dayOrders->sum('total_amount'),
                    'formatted_sales' => 'KES ' . number_format($dayOrders->sum('total_amount'), 2),
                    'avg_order_value' => $dayOrders->avg('total_amount'),
                    'formatted_avg' => 'KES ' . number_format($dayOrders->avg('total_amount'), 2)
                ];
            })->sortKeys();

            // Calculate hourly performance
            $hourlyPerformance = $orders->groupBy(function ($order) {
                return $order->created_at->format('H:00');
            })->map(function ($hourOrders) {
                return [
                    'orders' => $hourOrders->count(),
                    'sales' => $hourOrders->sum('total_amount'),
                    'formatted_sales' => 'KES ' . number_format($hourOrders->sum('total_amount'), 2)
                ];
            })->sortKeys();

            // Calculate performance metrics
            $totalDays = $startDate->diffInDays($endDate) + 1;
            $ordersPerDay = $waiter->total_orders / $totalDays;
            $salesPerDay = $waiter->total_sales / $totalDays;

            return [
                'id' => $waiter->id,
                'name' => $waiter->name,
                'role' => $waiter->role,
                'total_orders' => $waiter->total_orders,
                'total_sales' => $waiter->total_sales,
                'average_order_value' => $waiter->average_order_value,
                'formatted_sales' => 'KES ' . number_format($waiter->total_sales, 2),
                'formatted_average' => 'KES ' . number_format($waiter->average_order_value, 2),
                'orders_per_day' => round($ordersPerDay, 2),
                'sales_per_day' => round($salesPerDay, 2),
                'formatted_sales_per_day' => 'KES ' . number_format($salesPerDay, 2),
                'payment_breakdown' => $paymentBreakdown,
                'order_type_breakdown' => $orderTypeBreakdown,
                'daily_performance' => $dailyPerformance,
                'hourly_performance' => $hourlyPerformance,
                'performance_rating' => $this->calculatePerformanceRating($waiter->total_orders, $waiter->total_sales, $waiter->average_order_value),
                'first_order_date' => $waiter->first_order_date,
                'last_order_date' => $waiter->last_order_date,
                'total_days_active' => $totalDays,
            ];
        });

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

    public function paymentMethodDetails(Request $request): JsonResponse
    {
        $request->validate([
            'start_date' => 'required|date',
            'start_time' => 'nullable|date_format:H:i',
            'end_date' => 'required|date|after_or_equal:start_date',
            'end_time' => 'nullable|date_format:H:i',
            'method' => 'required|string|in:cash,mpesa,card,bank_transfer',
            'branch_id' => 'nullable|exists:branches,id',
            'shift' => 'nullable|in:24hrs,dayshift,nightshift',
        ]);

        // Enhanced date parsing with time support and Kenyan timezone
        $startTime = $request->start_time ?? '00:00';
        $endTime = $request->end_time ?? '23:59';
        
        $startDate = Carbon::createFromFormat('Y-m-d H:i', $request->start_date . ' ' . $startTime, 'Africa/Nairobi');
        $endDate = Carbon::createFromFormat('Y-m-d H:i', $request->end_date . ' ' . $endTime, 'Africa/Nairobi');
        
        // Convert to UTC for database queries
        $startDate = $startDate->utc();
        $endDate = $endDate->utc();
        $branchId = $request->branch_id ?? $request->user()->branch_id;
        $method = $request->method;
        $shift = $request->shift ?? '24hrs';

        $query = Payment::join('orders', 'payments.order_id', '=', 'orders.id')
            ->join('users', 'orders.user_id', '=', 'users.id')
            ->where('orders.restaurant_id', $request->user()->restaurant_id)
            ->where('orders.status', 'completed')
            ->where('payments.status', 'completed')
            ->where('payments.method', $method);

        if ($branchId) {
            $query->where('orders.branch_id', $branchId);
        }

        // Apply shift filtering
        $query = $this->applyShiftFilter($query, $startDate, $endDate, $shift, 'payments', 'created_at');

        $payments = $query->select([
                'payments.id',
                'payments.amount',
                'payments.created_at',
                'payments.mpesa_phone_number',
                'payments.mpesa_receipt_number',
                'payments.card_last_four',
                'payments.card_type',
                'payments.received_amount',
                'payments.change_amount',
                'payments.notes',
                'orders.order_number',
                'orders.type as order_type',
                'orders.total_amount as order_total',
                'users.name as staff_name',
                'users.role as staff_role'
            ])
            ->orderBy('payments.created_at', 'desc')
            ->get();

        // Group by date for daily breakdown
        $dailyBreakdown = $payments->groupBy(function ($payment) {
            return $payment->created_at->format('Y-m-d');
        })->map(function ($dayPayments) {
            return [
                'count' => $dayPayments->count(),
                'total' => $dayPayments->sum('amount'),
                'formatted_total' => 'KES ' . number_format($dayPayments->sum('amount'), 2),
                'payments' => $dayPayments->values()
            ];
        })->sortKeys();

        // Group by hour for hourly breakdown
        $hourlyBreakdown = $payments->groupBy(function ($payment) {
            return $payment->created_at->format('H:00');
        })->map(function ($hourPayments) {
            return [
                'count' => $hourPayments->count(),
                'total' => $hourPayments->sum('amount'),
                'formatted_total' => 'KES ' . number_format($hourPayments->sum('amount'), 2)
            ];
        })->sortKeys();

        // Group by staff member
        $staffBreakdown = $payments->groupBy('staff_name')->map(function ($staffPayments) {
            return [
                'count' => $staffPayments->count(),
                'total' => $staffPayments->sum('amount'),
                'formatted_total' => 'KES ' . number_format($staffPayments->sum('amount'), 2),
                'role' => $staffPayments->first()->staff_role
            ];
        })->sortByDesc('total');

        $summary = [
            'method' => $method,
            'method_display' => $method === 'mpesa' ? 'M-Pesa' : ucfirst(str_replace('_', ' ', $method)),
            'total_transactions' => $payments->count(),
            'total_amount' => $payments->sum('amount'),
            'formatted_total' => 'KES ' . number_format($payments->sum('amount'), 2),
            'average_transaction' => $payments->count() > 0 ? $payments->avg('amount') : 0,
            'formatted_average' => 'KES ' . number_format($payments->count() > 0 ? $payments->avg('amount') : 0, 2),
            'date_range' => [
                'start' => $startDate->format('Y-m-d'),
                'end' => $endDate->format('Y-m-d')
            ],
            'daily_breakdown' => $dailyBreakdown,
            'hourly_breakdown' => $hourlyBreakdown,
            'staff_breakdown' => $staffBreakdown,
            'recent_transactions' => $payments->take(20)->map(function ($payment) {
                return [
                    'id' => $payment->id,
                    'order_number' => $payment->order_number,
                    'amount' => $payment->amount,
                    'formatted_amount' => 'KES ' . number_format($payment->amount, 2),
                    'created_at' => $payment->created_at->format('Y-m-d H:i:s'),
                    'formatted_date' => $payment->created_at->format('M j, Y g:i A'),
                    'staff_name' => $payment->staff_name,
                    'staff_role' => $payment->staff_role,
                    'order_type' => $payment->order_type,
                    'order_total' => $payment->order_total,
                    'formatted_order_total' => 'KES ' . number_format($payment->order_total, 2),
                    'mpesa_phone' => $payment->mpesa_phone_number,
                    'mpesa_receipt' => $payment->mpesa_receipt_number,
                    'card_info' => $payment->card_last_four ? 
                        ucfirst($payment->card_type) . ' ****' . $payment->card_last_four : null,
                    'cash_details' => $payment->received_amount ? [
                        'received' => $payment->received_amount,
                        'change' => $payment->change_amount,
                        'formatted_received' => 'KES ' . number_format($payment->received_amount, 2),
                        'formatted_change' => 'KES ' . number_format($payment->change_amount, 2)
                    ] : null,
                    'notes' => $payment->notes
                ];
            })
        ];

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

    public function waiterDetails(Request $request): JsonResponse
    {
        $request->validate([
            'start_date' => 'required|date',
            'start_time' => 'nullable|date_format:H:i',
            'end_date' => 'required|date|after_or_equal:start_date',
            'end_time' => 'nullable|date_format:H:i',
            'waiter_id' => 'required|exists:users,id',
            'branch_id' => 'nullable|exists:branches,id',
            'shift' => 'nullable|in:24hrs,dayshift,nightshift',
        ]);

        // Enhanced date parsing with time support and Kenyan timezone
        $startTime = $request->start_time ?? '00:00';
        $endTime = $request->end_time ?? '23:59';
        
        $startDate = Carbon::createFromFormat('Y-m-d H:i', $request->start_date . ' ' . $startTime, 'Africa/Nairobi');
        $endDate = Carbon::createFromFormat('Y-m-d H:i', $request->end_date . ' ' . $endTime, 'Africa/Nairobi');
        
        // Convert to UTC for database queries
        $startDate = $startDate->utc();
        $endDate = $endDate->utc();
        $branchId = $request->branch_id ?? $request->user()->branch_id;
        $waiterId = $request->waiter_id;
        $shift = $request->shift ?? '24hrs';

        // Get waiter info
        $waiter = User::findOrFail($waiterId);

        // Get orders for this waiter
        $query = Order::where('user_id', $waiterId)
            ->where('restaurant_id', $request->user()->restaurant_id)
            ->where('status', 'completed');

        if ($branchId) {
            $query->where('branch_id', $branchId);
        }

        // Apply shift filtering
        $query = $this->applyShiftFilter($query, $startDate, $endDate, $shift);

        $orders = $query->with(['items.menuItem.category', 'payments', 'table'])->get();

        // Calculate basic metrics
        $totalOrders = $orders->count();
        $totalSales = $orders->sum('total_amount');
        $averageOrderValue = $totalOrders > 0 ? $totalSales / $totalOrders : 0;
        $totalDays = $startDate->diffInDays($endDate) + 1;
        $dailyAverage = $totalSales / $totalDays;

        // Daily performance breakdown
        $dailyPerformance = $orders->groupBy(function ($order) {
            return $order->created_at->format('Y-m-d');
        })->map(function ($dayOrders) {
            return [
                'orders' => $dayOrders->count(),
                'sales' => $dayOrders->sum('total_amount'),
                'formatted_sales' => 'KES ' . number_format($dayOrders->sum('total_amount'), 2),
                'avg_order_value' => $dayOrders->avg('total_amount'),
                'formatted_avg' => 'KES ' . number_format($dayOrders->avg('total_amount'), 2)
            ];
        })->sortKeys();

        // Order types breakdown
        $orderTypes = $orders->groupBy('type')->map(function ($typeOrders) {
            return [
                'count' => $typeOrders->count(),
                'total' => $typeOrders->sum('total_amount'),
                'formatted_total' => 'KES ' . number_format($typeOrders->sum('total_amount'), 2),
                'percentage' => 0 // Will be calculated below
            ];
        });

        // Calculate percentages for order types
        if ($totalSales > 0) {
            $orderTypes = $orderTypes->map(function ($data) use ($totalSales) {
                $data['percentage'] = round(($data['total'] / $totalSales) * 100, 2);
                return $data;
            });
        }

        // Top items sold by this waiter
        $topItems = $orders->flatMap(function ($order) {
            return $order->items;
        })->groupBy('menu_item_id')->map(function ($items) {
            $firstItem = $items->first();
            return [
                'id' => $firstItem->menu_item_id,
                'name' => $firstItem->menuItem->name,
                'category' => $firstItem->menuItem->category->name,
                'quantity' => $items->sum('quantity'),
                'revenue' => $items->sum('total_price'),
                'formatted_revenue' => 'KES ' . number_format($items->sum('total_price'), 2),
                'avg_price' => $items->avg('item_price'),
                'formatted_avg_price' => 'KES ' . number_format($items->avg('item_price'), 2)
            ];
        })->sortByDesc('quantity')->take(10)->values();

        // Recent orders
        $recentOrders = $orders->sortByDesc('created_at')->take(15)->map(function ($order) {
            return [
                'id' => $order->id,
                'order_number' => $order->order_number,
                'total' => $order->total_amount,
                'formatted_total' => 'KES ' . number_format($order->total_amount, 2),
                'created_at' => $order->created_at->format('Y-m-d H:i:s'),
                'formatted_date' => $order->created_at->format('M j, Y g:i A'),
                'table_name' => $order->table ? $order->table->name : 'N/A',
                'order_type' => $order->type,
                'items_count' => $order->items->count(),
                'payment_method' => $order->payments->first()?->method ?? 'N/A',
                'status' => $order->status
            ];
        })->values();

        // Payment methods breakdown
        $paymentMethods = $orders->flatMap(function ($order) {
            return $order->payments;
        })->where('status', 'completed')->groupBy('method')->map(function ($payments) {
            return [
                'count' => $payments->count(),
                'total' => $payments->sum('amount'),
                'formatted_total' => 'KES ' . number_format($payments->sum('amount'), 2)
            ];
        });

        // Hourly performance
        $hourlyPerformance = $orders->groupBy(function ($order) {
            return $order->created_at->format('H:00');
        })->map(function ($hourOrders) {
            return [
                'orders' => $hourOrders->count(),
                'sales' => $hourOrders->sum('total_amount'),
                'formatted_sales' => 'KES ' . number_format($hourOrders->sum('total_amount'), 2)
            ];
        })->sortKeys();

        // Tables served
        $tablesServed = $orders->whereNotNull('table_id')->unique('table_id')->count();

        // Performance metrics
        $ordersPerDay = $totalOrders / $totalDays;
        $performanceRating = $this->calculatePerformanceRating($totalOrders, $totalSales, $averageOrderValue);

        $details = [
            'waiter_id' => $waiterId,
            'waiter_name' => $waiter->name,
            'waiter_role' => $waiter->role,
            'date_range' => [
                'start' => $startDate->format('Y-m-d'),
                'end' => $endDate->format('Y-m-d')
            ],
            
            // Summary metrics
            'total_orders' => $totalOrders,
            'total_sales' => $totalSales,
            'average_order_value' => $averageOrderValue,
            'daily_average' => $dailyAverage,
            'formatted_total_sales' => 'KES ' . number_format($totalSales, 2),
            'formatted_average_order' => 'KES ' . number_format($averageOrderValue, 2),
            'formatted_daily_average' => 'KES ' . number_format($dailyAverage, 2),
            
            // Performance metrics
            'orders_per_day' => round($ordersPerDay, 2),
            'tables_served' => $tablesServed,
            'performance_rating' => $performanceRating,
            'efficiency_rating' => $this->calculateEfficiencyRating($ordersPerDay, $averageOrderValue),
            'customer_satisfaction' => $this->calculateCustomerSatisfaction($orders),
            
            // Detailed breakdowns
            'daily_performance' => $dailyPerformance,
            'order_types' => $orderTypes,
            'top_items' => $topItems,
            'recent_orders' => $recentOrders,
            'payment_methods' => $paymentMethods,
            'hourly_performance' => $hourlyPerformance,
            
            // Additional stats
            'busiest_day' => $dailyPerformance->sortByDesc('orders')->keys()->first(),
            'highest_sales_day' => $dailyPerformance->sortByDesc('sales')->keys()->first(),
            'average_items_per_order' => $orders->avg(function ($order) {
                return $order->items->sum('quantity');
            }),
        ];

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

    private function calculateEfficiencyRating($ordersPerDay, $averageOrderValue)
    {
        $score = 0;
        
        // Orders per day efficiency
        if ($ordersPerDay >= 20) $score += 50;
        elseif ($ordersPerDay >= 15) $score += 40;
        elseif ($ordersPerDay >= 10) $score += 30;
        elseif ($ordersPerDay >= 5) $score += 20;
        elseif ($ordersPerDay >= 2) $score += 10;
        
        // Average order value efficiency
        if ($averageOrderValue >= 2000) $score += 50;
        elseif ($averageOrderValue >= 1500) $score += 40;
        elseif ($averageOrderValue >= 1000) $score += 30;
        elseif ($averageOrderValue >= 500) $score += 20;
        elseif ($averageOrderValue >= 200) $score += 10;
        
        if ($score >= 80) return 'Excellent';
        elseif ($score >= 60) return 'Good';
        elseif ($score >= 40) return 'Average';
        elseif ($score >= 20) return 'Below Average';
        else return 'Needs Improvement';
    }

    private function calculateCustomerSatisfaction($orders)
    {
        // This is a simplified calculation based on order patterns
        // In a real system, you'd have customer feedback data
        
        $totalOrders = $orders->count();
        if ($totalOrders === 0) return 'N/A';
        
        $repeatCustomers = $orders->whereNotNull('customer_id')
            ->groupBy('customer_id')
            ->filter(function ($customerOrders) {
                return $customerOrders->count() > 1;
            })->count();
        
        $satisfactionScore = $totalOrders > 0 ? ($repeatCustomers / $totalOrders) * 100 : 0;
        
        if ($satisfactionScore >= 80) return 'Excellent (80%+)';
        elseif ($satisfactionScore >= 60) return 'Good (60-79%)';
        elseif ($satisfactionScore >= 40) return 'Average (40-59%)';
        elseif ($satisfactionScore >= 20) return 'Below Average (20-39%)';
        else return 'Needs Improvement (<20%)';
    }

    public function exportSales(Request $request): JsonResponse
    {
        // This would typically generate and return a downloadable file
        // For now, we'll return the data that would be exported
        
        $salesData = $this->salesSummary($request);
        
        return response()->json([
            'message' => 'Export functionality would be implemented here',
            'data' => $salesData->getData(),
            'format' => 'This would be exported as Excel/PDF',
        ]);
    }

    public function expenseSummary(Request $request): JsonResponse
    {
        $request->validate([
            'start_date' => 'required|date',
            'start_time' => 'nullable|date_format:H:i',
            'end_date' => 'required|date|after_or_equal:start_date',
            'end_time' => 'nullable|date_format:H:i',
            'branch_id' => 'nullable|exists:branches,id',
            'category_id' => 'nullable|exists:expense_categories,id',
            'status' => 'nullable|in:pending,approved,rejected,paid',
            'shift' => 'nullable|in:24hrs,dayshift,nightshift',
        ]);

        // Enhanced date parsing with time support and Kenyan timezone
        $startTime = $request->start_time ?? '00:00';
        $endTime = $request->end_time ?? '23:59';
        
        $startDate = Carbon::createFromFormat('Y-m-d H:i', $request->start_date . ' ' . $startTime, 'Africa/Nairobi');
        $endDate = Carbon::createFromFormat('Y-m-d H:i', $request->end_date . ' ' . $endTime, 'Africa/Nairobi');
        
        // Convert to UTC for database queries
        $startDate = $startDate->utc();
        $endDate = $endDate->utc();
        $branchId = $request->branch_id ?? $request->user()->branch_id;
        $shift = $request->shift ?? '24hrs';

        // For expense_date filtering, use date-only comparison since expense_date is a DATE field
        $startDateOnly = $startDate->toDateString();
        $endDateOnly = $endDate->toDateString();

        // Build base query with simple date filtering (no shift filtering for performance)
        $query = \App\Models\Expense::where('restaurant_id', $request->user()->restaurant_id)
            ->whereDate('expense_date', '>=', $startDateOnly)
            ->whereDate('expense_date', '<=', $endDateOnly);

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

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

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

        // Use database aggregations for better performance
        $baseQuery = clone $query;
        
        // Get aggregated data using database queries
        $totalExpenses = (clone $baseQuery)->whereIn('status', ['approved', 'paid'])->sum('amount');
        $pendingExpenses = (clone $baseQuery)->where('status', 'pending')->sum('amount');
        $approvedExpenses = (clone $baseQuery)->where('status', 'approved')->sum('amount');
        $paidExpenses = (clone $baseQuery)->where('status', 'paid')->sum('amount');
        $rejectedExpenses = (clone $baseQuery)->where('status', 'rejected')->sum('amount');
        
        $expenseCount = (clone $baseQuery)->count();
        $pendingCount = (clone $baseQuery)->where('status', 'pending')->count();
        $approvedCount = (clone $baseQuery)->where('status', 'approved')->count();
        $paidCount = (clone $baseQuery)->where('status', 'paid')->count();
        $rejectedCount = (clone $baseQuery)->where('status', 'rejected')->count();
        
        $averageExpense = (clone $baseQuery)->whereIn('status', ['approved', 'paid'])->avg('amount') ?? 0;
        
        // Get only necessary expenses for detailed operations (limit for performance)
        $expenses = (clone $query)->with(['category'])->latest('expense_date')->limit(100)->get();

        // Get petty cash transactions for the same period
        $pettyCashQuery = \App\Models\PettyCashTransaction::where('restaurant_id', $request->user()->restaurant_id)
            ->whereBetween('created_at', [$startDate, $endDate]);

        if ($branchId) {
            $pettyCashQuery->where('branch_id', $branchId);
        }

        $pettyCashTransactions = $pettyCashQuery->get();

        $summary = [
            'overview' => [
                'total_expenses' => $totalExpenses,
                'pending_expenses' => $pendingExpenses,
                'approved_expenses' => $approvedExpenses,
                'paid_expenses' => $paidExpenses,
                'rejected_expenses' => $rejectedExpenses,
                'expense_count' => $expenseCount,
                'pending_count' => $pendingCount,
                'approved_count' => $approvedCount,
                'paid_count' => $paidCount,
                'rejected_count' => $rejectedCount,
                'average_expense' => $averageExpense,
                'formatted_total_expenses' => 'KES ' . number_format($totalExpenses, 2),
                'formatted_pending_expenses' => 'KES ' . number_format($pendingExpenses, 2),
                'formatted_approved_expenses' => 'KES ' . number_format($approvedExpenses, 2),
                'formatted_paid_expenses' => 'KES ' . number_format($paidExpenses, 2),
                'formatted_average_expense' => 'KES ' . number_format($averageExpense, 2),
            ],
            
            // Enhanced Pay Later Breakdown - using database aggregations
            'pay_later' => [
                'total_amount' => (clone $baseQuery)->where('payment_status', 'pay_later')->whereIn('status', ['approved', 'paid'])->sum('amount'),
                'unpaid_amount' => (clone $baseQuery)->where('payment_status', 'pay_later')->whereNotIn('status', ['paid', 'rejected'])->sum('amount'),
                'paid_amount' => (clone $baseQuery)->where('payment_status', 'pay_later')->where('status', 'paid')->sum('amount'),
                'overdue_amount' => (clone $baseQuery)->where('payment_status', 'pay_later')->where('due_date', '<', now())->whereNotIn('status', ['paid', 'rejected'])->sum('amount'),
                'total_count' => (clone $baseQuery)->where('payment_status', 'pay_later')->count(),
                'unpaid_count' => (clone $baseQuery)->where('payment_status', 'pay_later')->whereNotIn('status', ['paid', 'rejected'])->count(),
                'paid_count' => (clone $baseQuery)->where('payment_status', 'pay_later')->where('status', 'paid')->count(),
                'overdue_count' => (clone $baseQuery)->where('payment_status', 'pay_later')->where('due_date', '<', now())->whereNotIn('status', ['paid', 'rejected'])->count(),
                'formatted_total_amount' => 'KES ' . number_format((clone $baseQuery)->where('payment_status', 'pay_later')->whereIn('status', ['approved', 'paid'])->sum('amount'), 2),
                'formatted_unpaid_amount' => 'KES ' . number_format((clone $baseQuery)->where('payment_status', 'pay_later')->whereNotIn('status', ['paid', 'rejected'])->sum('amount'), 2),
                'formatted_paid_amount' => 'KES ' . number_format((clone $baseQuery)->where('payment_status', 'pay_later')->where('status', 'paid')->sum('amount'), 2),
                'formatted_overdue_amount' => 'KES ' . number_format((clone $baseQuery)->where('payment_status', 'pay_later')->where('due_date', '<', now())->whereNotIn('status', ['paid', 'rejected'])->sum('amount'), 2),
                'upcoming_due' => (clone $baseQuery)->where('payment_status', 'pay_later')
                    ->whereNotIn('status', ['paid', 'rejected'])
                    ->where('due_date', '>=', now())
                    ->where('due_date', '<=', now()->addDays(7))
                    ->limit(10)
                    ->get()
                    ->map(function ($expense) {
                        return [
                            'id' => $expense->id,
                            'title' => $expense->title,
                            'amount' => $expense->amount,
                            'due_date' => $expense->due_date->format('Y-m-d'),
                            'days_until_due' => now()->diffInDays($expense->due_date, false),
                            'formatted_amount' => 'KES ' . number_format($expense->amount, 2),
                        ];
                    })->sortBy('due_date')->values(),
            ],

            // Enhanced Petty Cash Summary
            'petty_cash' => [
                'total_issued' => $pettyCashTransactions->where('type', 'withdrawal')->sum('amount'),
                'transaction_count' => $pettyCashTransactions->count(),
                'issues_count' => $pettyCashTransactions->where('type', 'withdrawal')->count(),
                'expense_count' => (clone $baseQuery)->where('is_petty_cash', true)->count(),
                'expense_amount' => (clone $baseQuery)->where('is_petty_cash', true)->whereIn('status', ['approved', 'paid'])->sum('amount'),
                'formatted_total_issued' => 'KES ' . number_format($pettyCashTransactions->where('type', 'withdrawal')->sum('amount'), 2),
                'formatted_expense_amount' => 'KES ' . number_format((clone $baseQuery)->where('is_petty_cash', true)->whereIn('status', ['approved', 'paid'])->sum('amount'), 2),
                'recent_issues' => $pettyCashTransactions->where('type', 'withdrawal')
                    ->sortByDesc('created_at')
                    ->take(5)
                    ->map(function ($transaction) {
                        return [
                            'id' => $transaction->id,
                            'amount' => $transaction->amount,
                            'reason' => $transaction->reason,
                            'date' => $transaction->created_at->format('Y-m-d H:i'),
                            'formatted_amount' => 'KES ' . number_format($transaction->amount, 2),
                        ];
                    })->values(),
            ],

            // Regular vs Petty Cash Expenses - using database aggregations
            'expense_types' => [
                'regular_expenses' => (clone $baseQuery)->where('is_petty_cash', false)->whereIn('status', ['approved', 'paid'])->sum('amount'),
                'petty_cash_expenses' => (clone $baseQuery)->where('is_petty_cash', true)->whereIn('status', ['approved', 'paid'])->sum('amount'),
                'regular_count' => (clone $baseQuery)->where('is_petty_cash', false)->count(),
                'petty_cash_count' => (clone $baseQuery)->where('is_petty_cash', true)->count(),
                'formatted_regular_expenses' => 'KES ' . number_format((clone $baseQuery)->where('is_petty_cash', false)->whereIn('status', ['approved', 'paid'])->sum('amount'), 2),
                'formatted_petty_cash_expenses' => 'KES ' . number_format((clone $baseQuery)->where('is_petty_cash', true)->whereIn('status', ['approved', 'paid'])->sum('amount'), 2),
            ],
            
            // Simplified category breakdown - limit to top 10
            'category_breakdown' => (clone $baseQuery)
                ->whereIn('status', ['approved', 'paid'])
                ->selectRaw('category_id, COUNT(*) as count, SUM(amount) as total, AVG(amount) as average')
                ->groupBy('category_id')
                ->orderByDesc('total')
                ->limit(10)
                ->get()
                ->mapWithKeys(function ($item) {
                    $category = \App\Models\ExpenseCategory::find($item->category_id);
                    $categoryName = $category ? $category->name : 'Uncategorized';
                    return [
                        $categoryName => [
                            'count' => $item->count,
                            'total' => $item->total,
                            'formatted_total' => 'KES ' . number_format($item->total, 2),
                            'average' => $item->average,
                            'formatted_average' => 'KES ' . number_format($item->average, 2),
                        ]
                    ];
                }),

            // Simplified payment method breakdown
            'payment_method_breakdown' => (clone $baseQuery)
                ->whereIn('status', ['approved', 'paid'])
                ->selectRaw('payment_method, COUNT(*) as count, SUM(amount) as total, AVG(amount) as average')
                ->groupBy('payment_method')
                ->orderByDesc('total')
                ->get()
                ->mapWithKeys(function ($item) {
                    return [
                        $item->payment_method ?? 'N/A' => [
                            'count' => $item->count,
                            'total' => $item->total,
                            'formatted_total' => 'KES ' . number_format($item->total, 2),
                            'average' => $item->average,
                            'formatted_average' => 'KES ' . number_format($item->average, 2),
                        ]
                    ];
                }),

            // Simplified status breakdown
            'status_breakdown' => (clone $baseQuery)
                ->selectRaw('status, COUNT(*) as count, SUM(amount) as total')
                ->groupBy('status')
                ->get()
                ->mapWithKeys(function ($item) use ($expenseCount, $totalExpenses, $pendingExpenses, $approvedExpenses, $paidExpenses, $rejectedExpenses) {
                    $allExpensesTotal = $totalExpenses + $pendingExpenses + $rejectedExpenses;
                    $percentage = $expenseCount > 0 ? ($item->count / $expenseCount) * 100 : 0;
                    $amountPercentage = $allExpensesTotal > 0 ? ($item->total / $allExpensesTotal) * 100 : 0;
                    
                    return [
                        $item->status => [
                            'count' => $item->count,
                            'total' => $item->total,
                            'formatted_total' => 'KES ' . number_format($item->total, 2),
                            'percentage' => round($percentage, 1),
                            'amount_percentage' => round($amountPercentage, 1),
                        ]
                    ];
                }),

            // Recent expenses - limit to 10
            'recent_expenses' => $expenses->sortByDesc('expense_date')->take(10)->map(function ($expense) {
                return [
                    'id' => $expense->id,
                    'title' => $expense->title,
                    'amount' => $expense->amount,
                    'formatted_amount' => 'KES ' . number_format($expense->amount, 2),
                    'category' => $expense->category->name ?? 'N/A',
                    'status' => $expense->status,
                    'payment_status' => $expense->payment_status,
                    'is_petty_cash' => $expense->is_petty_cash,
                    'expense_date' => $expense->expense_date->format('Y-m-d'),
                    'due_date' => $expense->due_date ? $expense->due_date->format('Y-m-d') : null,
                ];
            })->values(),
        ];

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

    public function expensesByCategory(Request $request): JsonResponse
    {
        $request->validate([
            'start_date' => 'required|date',
            'start_time' => 'nullable|date_format:H:i',
            'end_date' => 'required|date|after_or_equal:start_date',
            'end_time' => 'nullable|date_format:H:i',
            'branch_id' => 'nullable|exists:branches,id',
            'shift' => 'nullable|in:24hrs,dayshift,nightshift',
        ]);

        // Enhanced date parsing with time support and Kenyan timezone
        $startTime = $request->start_time ?? '00:00';
        $endTime = $request->end_time ?? '23:59';
        
        $startDate = Carbon::createFromFormat('Y-m-d H:i', $request->start_date . ' ' . $startTime, 'Africa/Nairobi');
        $endDate = Carbon::createFromFormat('Y-m-d H:i', $request->end_date . ' ' . $endTime, 'Africa/Nairobi');
        
        // Convert to UTC for database queries
        $startDate = $startDate->utc();
        $endDate = $endDate->utc();
        $branchId = $request->branch_id ?? $request->user()->branch_id;
        $shift = $request->shift ?? '24hrs';

        // For expense_date filtering, use date-only comparison since expense_date is a DATE field
        $startDateOnly = $startDate->toDateString();
        $endDateOnly = $endDate->toDateString();

        $query = \App\Models\ExpenseCategory::with(['expenses' => function ($q) use ($startDateOnly, $endDateOnly, $branchId, $shift, $request) {
            $q->whereIn('status', ['approved', 'paid']);
            
            if ($branchId) {
                $q->where('branch_id', $branchId);
            }
            
            // Apply date filtering to expenses - use whereDate for DATE field comparison
            $q->whereDate('expense_date', '>=', $startDateOnly)
              ->whereDate('expense_date', '<=', $endDateOnly);
        }])->where('restaurant_id', $request->user()->restaurant_id);

        $categories = $query->get();

        $categoryReport = $categories->map(function ($category) {
            $expenses = $category->expenses;
            $totalSpent = $expenses->sum('amount');
            $budgetUtilization = $category->budget_limit ? 
                ($totalSpent / $category->budget_limit) * 100 : 0;
            
            return [
                'id' => $category->id,
                'name' => $category->name,
                'description' => $category->description,
                'budget_limit' => $category->budget_limit,
                'formatted_budget_limit' => $category->budget_limit ? 
                    'KES ' . number_format($category->budget_limit, 2) : 'No Limit',
                'total_spent' => $totalSpent,
                'formatted_total_spent' => 'KES ' . number_format($totalSpent, 2),
                'remaining_budget' => $category->budget_limit ? 
                    $category->budget_limit - $totalSpent : null,
                'formatted_remaining_budget' => $category->budget_limit ? 
                    'KES ' . number_format($category->budget_limit - $totalSpent, 2) : null,
                'budget_utilization' => round($budgetUtilization, 2),
                'is_over_budget' => $category->budget_limit && $totalSpent > $category->budget_limit,
                'expense_count' => $expenses->count(),
                'average_expense' => $expenses->count() > 0 ? $expenses->avg('amount') : 0,
                'formatted_average_expense' => $expenses->count() > 0 ? 
                    'KES ' . number_format($expenses->avg('amount'), 2) : 'KES 0.00',
                'recent_expenses' => $expenses->sortByDesc('expense_date')->take(5)->map(function ($expense) {
                    return [
                        'id' => $expense->id,
                        'title' => $expense->title,
                        'amount' => $expense->amount,
                        'formatted_amount' => 'KES ' . number_format($expense->amount, 2),
                        'expense_date' => $expense->expense_date->format('Y-m-d'),
                        'formatted_date' => $expense->expense_date->format('M j, Y'),
                        'vendor' => $expense->vendor,
                        'status' => $expense->status,
                    ];
                })->values(),
            ];
        })->sortByDesc('total_spent');

        return response()->json($categoryReport->values());
    }

    public function expenseTrends(Request $request): JsonResponse
    {
        $request->validate([
            'period' => 'required|in:daily,weekly,monthly',
            'start_date' => 'required|date',
            'start_time' => 'nullable|date_format:H:i',
            'end_date' => 'required|date|after_or_equal:start_date',
            'end_time' => 'nullable|date_format:H:i',
            'branch_id' => 'nullable|exists:branches,id',
            'category_id' => 'nullable|exists:expense_categories,id',
            'shift' => 'nullable|in:24hrs,dayshift,nightshift',
        ]);

        // Enhanced date parsing with time support and Kenyan timezone
        $startTime = $request->start_time ?? '00:00';
        $endTime = $request->end_time ?? '23:59';
        
        $startDate = Carbon::createFromFormat('Y-m-d H:i', $request->start_date . ' ' . $startTime, 'Africa/Nairobi');
        $endDate = Carbon::createFromFormat('Y-m-d H:i', $request->end_date . ' ' . $endTime, 'Africa/Nairobi');
        
        // Convert to UTC for database queries
        $startDate = $startDate->utc();
        $endDate = $endDate->utc();
        $branchId = $request->branch_id ?? $request->user()->branch_id;
        $period = $request->period;
        $shift = $request->shift ?? '24hrs';

        $query = \App\Models\Expense::where('restaurant_id', $request->user()->restaurant_id)
            ->whereIn('status', ['approved', 'paid']);

        if ($branchId) {
            $query->where('branch_id', $branchId);
        }

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

        // Apply shift filtering
        $query = $this->applyShiftFilter($query, $startDate, $endDate, $shift, '', 'expense_date');

        $expenses = $query->get();

        // Group by period
        $groupBy = match($period) {
            'daily' => function ($expense) {
                return $expense->expense_date->format('Y-m-d');
            },
            'weekly' => function ($expense) {
                return $expense->expense_date->startOfWeek()->format('Y-m-d');
            },
            'monthly' => function ($expense) {
                return $expense->expense_date->format('Y-m');
            },
        };

        $trends = $expenses->groupBy($groupBy)->map(function ($periodExpenses, $period) {
            return [
                'period' => $period,
                'count' => $periodExpenses->count(),
                'total' => $periodExpenses->sum('amount'),
                'formatted_total' => 'KES ' . number_format($periodExpenses->sum('amount'), 2),
                'average' => $periodExpenses->avg('amount'),
                'formatted_average' => 'KES ' . number_format($periodExpenses->avg('amount'), 2),
                'categories' => $periodExpenses->groupBy('category.name')->map(function ($catExpenses) {
                    return [
                        'count' => $catExpenses->count(),
                        'total' => $catExpenses->sum('amount'),
                        'formatted_total' => 'KES ' . number_format($catExpenses->sum('amount'), 2),
                    ];
                })->sortByDesc('total'),
            ];
        })->sortKeys();

        // Calculate period-over-period changes
        $trendsWithChanges = $trends->values()->map(function ($current, $index) use ($trends) {
            $previous = $trends->values()->get($index - 1);
            
            $change = null;
            $changePercentage = null;
            
            if ($previous) {
                $change = $current['total'] - $previous['total'];
                $changePercentage = $previous['total'] > 0 ? 
                    (($change / $previous['total']) * 100) : 0;
            }
            
            return array_merge($current, [
                'change_from_previous' => $change,
                'formatted_change' => $change ? 'KES ' . number_format(abs($change), 2) : null,
                'change_percentage' => $changePercentage ? round($changePercentage, 2) : null,
                'trend_direction' => $change > 0 ? 'up' : ($change < 0 ? 'down' : 'stable'),
            ]);
        });

        return response()->json([
            'period' => $period,
            'date_range' => [
                'start' => $startDate->format('Y-m-d'),
                'end' => $endDate->format('Y-m-d'),
            ],
            'trends' => $trendsWithChanges,
            'summary' => [
                'total_periods' => $trends->count(),
                'highest_period' => $trends->sortByDesc('total')->first(),
                'lowest_period' => $trends->sortBy('total')->first(),
                'average_per_period' => $trends->avg('total'),
                'formatted_average_per_period' => 'KES ' . number_format($trends->avg('total'), 2),
            ],
        ]);
    }

    /**
     * Get detailed revenue breakdown by order type
     */
    private function getOrderTypeRevenueBreakdown($allOrders, $grossRevenue)
    {
        $orderTypes = ['dine_in', 'takeaway', 'delivery'];
        $breakdown = [];

        foreach ($orderTypes as $type) {
            // Get all orders of this type (excluding cancelled)
            $typeOrders = $allOrders->where('type', $type)->whereNotIn('status', ['cancelled']);
            
            if ($typeOrders->isEmpty()) {
                continue; // Skip if no orders of this type
            }

            // Separate by status
            $typeCompletedOrders = $typeOrders->where('status', 'completed');
            $typePendingOrders = $typeOrders->whereIn('status', ['pending', 'confirmed', 'preparing', 'ready']);

            // Get payment information for completed orders of this type
            $typeCompletedOrderIds = $typeCompletedOrders->pluck('id');
            $typePaidOrders = collect();
            $typeUnpaidOrders = collect();

            if ($typeCompletedOrderIds->isNotEmpty()) {
                $typePaidOrderIds = Payment::whereIn('order_id', $typeCompletedOrderIds)
                    ->where('status', 'completed')
                    ->pluck('order_id')
                    ->unique();
                
                $typePaidOrders = $typeCompletedOrders->whereIn('id', $typePaidOrderIds);
                $typeUnpaidOrders = $typeCompletedOrders->whereNotIn('id', $typePaidOrderIds);
            }

            // Calculate totals for this order type
            $typeGrossRevenue = $typeOrders->sum('total_amount');
            $typeNetRevenue = $typePaidOrders->sum('total_amount');
            $typePendingRevenue = $typePendingOrders->sum('total_amount');
            $typeUnpaidCompletedRevenue = $typeUnpaidOrders->sum('total_amount');

            $breakdown[$type] = [
                'type_display' => ucfirst(str_replace('_', ' ', $type)),
                'gross_revenue' => [
                    'amount' => $typeGrossRevenue,
                    'formatted' => 'KES ' . number_format($typeGrossRevenue, 2),
                    'orders_count' => $typeOrders->count(),
                    'percentage_of_total' => $grossRevenue > 0 ? round(($typeGrossRevenue / $grossRevenue) * 100, 2) : 0,
                    'description' => 'All ' . strtolower(str_replace('_', '-', $type)) . ' orders (completed + pending)'
                ],
                'net_revenue' => [
                    'amount' => $typeNetRevenue,
                    'formatted' => 'KES ' . number_format($typeNetRevenue, 2),
                    'orders_count' => $typePaidOrders->count(),
                    'percentage_of_type' => $typeGrossRevenue > 0 ? round(($typeNetRevenue / $typeGrossRevenue) * 100, 2) : 0,
                    'percentage_of_total' => $grossRevenue > 0 ? round(($typeNetRevenue / $grossRevenue) * 100, 2) : 0,
                    'description' => 'Completed and paid ' . strtolower(str_replace('_', '-', $type)) . ' orders'
                ],
                'pending_revenue' => [
                    'amount' => $typePendingRevenue,
                    'formatted' => 'KES ' . number_format($typePendingRevenue, 2),
                    'orders_count' => $typePendingOrders->count(),
                    'percentage_of_type' => $typeGrossRevenue > 0 ? round(($typePendingRevenue / $typeGrossRevenue) * 100, 2) : 0,
                    'percentage_of_total' => $grossRevenue > 0 ? round(($typePendingRevenue / $grossRevenue) * 100, 2) : 0,
                    'description' => 'Pending ' . strtolower(str_replace('_', '-', $type)) . ' orders'
                ],
                'unpaid_completed_revenue' => [
                    'amount' => $typeUnpaidCompletedRevenue,
                    'formatted' => 'KES ' . number_format($typeUnpaidCompletedRevenue, 2),
                    'orders_count' => $typeUnpaidOrders->count(),
                    'percentage_of_type' => $typeGrossRevenue > 0 ? round(($typeUnpaidCompletedRevenue / $typeGrossRevenue) * 100, 2) : 0,
                    'percentage_of_total' => $grossRevenue > 0 ? round(($typeUnpaidCompletedRevenue / $grossRevenue) * 100, 2) : 0,
                    'description' => 'Completed but unpaid ' . strtolower(str_replace('_', '-', $type)) . ' orders'
                ],
                'detailed_breakdown' => [
                    'completed_paid' => [
                        'amount' => $typeNetRevenue,
                        'formatted' => 'KES ' . number_format($typeNetRevenue, 2),
                        'orders_count' => $typePaidOrders->count(),
                        'percentage' => $typeGrossRevenue > 0 ? round(($typeNetRevenue / $typeGrossRevenue) * 100, 2) : 0
                    ],
                    'completed_unpaid' => [
                        'amount' => $typeUnpaidCompletedRevenue,
                        'formatted' => 'KES ' . number_format($typeUnpaidCompletedRevenue, 2),
                        'orders_count' => $typeUnpaidOrders->count(),
                        'percentage' => $typeGrossRevenue > 0 ? round(($typeUnpaidCompletedRevenue / $typeGrossRevenue) * 100, 2) : 0
                    ],
                    'pending' => [
                        'amount' => $typePendingRevenue,
                        'formatted' => 'KES ' . number_format($typePendingRevenue, 2),
                        'orders_count' => $typePendingOrders->count(),
                        'percentage' => $typeGrossRevenue > 0 ? round(($typePendingRevenue / $typeGrossRevenue) * 100, 2) : 0
                    ]
                ],
                'status_breakdown' => $typeOrders->groupBy('status')->map(function ($statusOrders, $status) use ($typeGrossRevenue) {
                    $statusRevenue = $statusOrders->sum('total_amount');
                    return [
                        'count' => $statusOrders->count(),
                        'amount' => $statusRevenue,
                        'formatted' => 'KES ' . number_format($statusRevenue, 2),
                        'percentage' => $typeGrossRevenue > 0 ? round(($statusRevenue / $typeGrossRevenue) * 100, 2) : 0,
                        'status_display' => ucfirst($status)
                    ];
                })
            ];
        }

        return $breakdown;
    }

    /**
     * Get detailed payment methods information with order details
     */
    private function getDetailedPaymentMethods($completedOrderIds)
    {
        if (empty($completedOrderIds) || $completedOrderIds->isEmpty()) {
            return [];
        }

        // Get all completed payments
        $payments = Payment::whereIn('order_id', $completedOrderIds)
            ->where('status', 'completed')
            ->with(['order' => function($query) {
                $query->with('user:id,name');
            }])
            ->get();

        // Group by payment method
        $paymentMethods = [];
        
        foreach ($payments as $payment) {
            $method = $payment->method;
            
            if (!isset($paymentMethods[$method])) {
                $paymentMethods[$method] = [
                    'method' => $method,
                    'method_display' => $this->getPaymentMethodDisplay($method),
                    'count' => 0,
                    'total' => 0,
                    'formatted_total' => 'KES 0.00',
                    'orders' => []
                ];
            }
            
            $paymentMethods[$method]['count']++;
            $paymentMethods[$method]['total'] += $payment->amount;
            
            // Add detailed order information for Cash and M-Pesa
            if (in_array($method, ['cash', 'mpesa'])) {
                $orderInfo = [
                    'order_number' => $payment->order->order_number ?? 'N/A',
                    'order_id' => $payment->order_id,
                    'amount' => $payment->amount,
                    'formatted_amount' => 'KES ' . number_format($payment->amount, 2),
                    'payment_reference' => $payment->payment_reference,
                    'processed_at' => $payment->processed_at ? $payment->processed_at->format('Y-m-d H:i:s') : null,
                    'formatted_processed_at' => $payment->processed_at ? $payment->processed_at->setTimezone('Africa/Nairobi')->format('d M Y, h:i A') : null,
                    'order_type' => $payment->order->type ?? null,
                    'order_type_display' => $payment->order ? ucfirst(str_replace('_', ' ', $payment->order->type)) : null,
                    'cashier' => $payment->order->user->name ?? 'N/A'
                ];
                
                // Add M-Pesa specific details
                if ($method === 'mpesa') {
                    $orderInfo['mpesa_receipt_number'] = $payment->mpesa_receipt_number;
                    $orderInfo['mpesa_phone_number'] = $payment->mpesa_phone_number;
                    $orderInfo['mpesa_transaction_id'] = $payment->mpesa_transaction_id;
                }
                
                // Add Cash specific details
                if ($method === 'cash') {
                    $orderInfo['received_amount'] = $payment->received_amount;
                    $orderInfo['formatted_received_amount'] = 'KES ' . number_format($payment->received_amount, 2);
                    $orderInfo['change_amount'] = $payment->change_amount;
                    $orderInfo['formatted_change_amount'] = 'KES ' . number_format($payment->change_amount, 2);
                }
                
                $paymentMethods[$method]['orders'][] = $orderInfo;
            }
        }
        
        // Format totals and sort orders by processed_at descending
        foreach ($paymentMethods as $method => &$data) {
            $data['formatted_total'] = 'KES ' . number_format($data['total'], 2);
            $data['average_transaction'] = $data['count'] > 0 ? $data['total'] / $data['count'] : 0;
            $data['formatted_average_transaction'] = 'KES ' . number_format($data['average_transaction'], 2);
            
            // Sort orders by processed_at (most recent first)
            if (!empty($data['orders'])) {
                usort($data['orders'], function($a, $b) {
                    return strtotime($b['processed_at'] ?? '1970-01-01') - strtotime($a['processed_at'] ?? '1970-01-01');
                });
            }
        }
        
        return $paymentMethods;
    }

    /**
     * Get payment method display name
     */
    private function getPaymentMethodDisplay($method)
    {
        return match($method) {
            'cash' => 'Cash',
            'mpesa' => 'M-Pesa',
            'card' => 'Card',
            'bank_transfer' => 'Bank Transfer',
            default => ucfirst(str_replace('_', ' ', $method))
        };
    }
}