app/Services/AnalyticsService.php

<?php

namespace App\Services;

use App\Models\AnalyticsSnapshot;
use App\Models\RevenueTracking;
use App\Models\Order;
use App\Models\User;
use App\Models\Product;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;

class AnalyticsService
{
    /**
     * Calculate real-time revenue for today using real Order data
     */
    public function getTodayRevenue(): array
    {
        $today = Carbon::today();
        $yesterday = Carbon::yesterday();

        // Use real Order data
        $todayRevenue = Order::whereDate('created_at', $today)
            ->where('status', '!=', 'cancelled')
            ->sum('total_amount');
            
        $yesterdayRevenue = Order::whereDate('created_at', $yesterday)
            ->where('status', '!=', 'cancelled')
            ->sum('total_amount');

        $percentageChange = $yesterdayRevenue > 0 
            ? (($todayRevenue - $yesterdayRevenue) / $yesterdayRevenue) * 100 
            : ($todayRevenue > 0 ? 100 : 0);

        return [
            'current' => (float) $todayRevenue,
            'previous' => (float) $yesterdayRevenue,
            'percentage_change' => round($percentageChange, 2),
            'trend' => $percentageChange >= 0 ? 'up' : 'down'
        ];
    }

    /**
     * Calculate profit data with margins using real Order data
     */
    public function getProfitData(string $period = 'today'): array
    {
        $query = Order::where('status', '!=', 'cancelled');
        $previousQuery = Order::where('status', '!=', 'cancelled');

        switch ($period) {
            case 'today':
                $query->whereDate('created_at', Carbon::today());
                $previousQuery->whereDate('created_at', Carbon::yesterday());
                break;
            case 'week':
                $query->whereBetween('created_at', [Carbon::now()->startOfWeek(), Carbon::now()->endOfWeek()]);
                $previousQuery->whereBetween('created_at', [
                    Carbon::now()->subWeek()->startOfWeek(),
                    Carbon::now()->subWeek()->endOfWeek()
                ]);
                break;
            case 'month':
                $query->whereMonth('created_at', Carbon::now()->month);
                $previousQuery->whereMonth('created_at', Carbon::now()->subMonth()->month);
                break;
        }

        $revenue = $query->sum('total_amount');
        $previousRevenue = $previousQuery->sum('total_amount');
        
        // Estimate profit as 30% of revenue (you can adjust this based on your business model)
        $estimatedProfitMargin = 0.30;
        $profit = $revenue * $estimatedProfitMargin;
        $previousProfit = $previousRevenue * $estimatedProfitMargin;
        $cost = $revenue - $profit;

        $profitMarginPercentage = $revenue > 0 ? ($profit / $revenue) * 100 : 0;
        $percentageChange = $previousProfit > 0 
            ? (($profit - $previousProfit) / $previousProfit) * 100 
            : ($profit > 0 ? 100 : 0);

        return [
            'profit' => (float) $profit,
            'revenue' => (float) $revenue,
            'cost' => (float) $cost,
            'profit_margin' => round($profitMarginPercentage, 2),
            'percentage_change' => round($percentageChange, 2),
            'trend' => $percentageChange >= 0 ? 'up' : 'down'
        ];
    }

    /**
     * Get aggregated data for different time periods
     */
    public function getAggregatedData(string $period, Carbon $date = null): array
    {
        $date = $date ?? Carbon::now();

        switch ($period) {
            case 'daily':
                return $this->getDailyHourlyData($date);
            case 'weekly':
                return $this->getWeeklyDailyData($date);
            case 'monthly':
                return $this->getMonthlyDailyData($date);
            default:
                return [];
        }
    }

    /**
     * Get hourly data for a specific day using real Order data
     */
    private function getDailyHourlyData(Carbon $date): array
    {
        $hourlyData = [];
        
        for ($hour = 0; $hour < 24; $hour++) {
            $startTime = $date->copy()->hour($hour)->minute(0)->second(0);
            $endTime = $startTime->copy()->addHour();
            
            $orders = Order::where('status', '!=', 'cancelled')
                ->whereBetween('created_at', [$startTime, $endTime])
                ->get();
                
            $revenue = $orders->sum('total_amount');
            $profit = $revenue * 0.30; // 30% profit margin estimate
            $ordersCount = $orders->count();
            $customersCount = $orders->pluck('customer_name')->unique()->count();
            
            $hourlyData[] = [
                'hour' => $hour,
                'revenue' => (float) $revenue,
                'profit' => (float) $profit,
                'orders' => $ordersCount,
                'customers' => $customersCount,
            ];
        }

        return $hourlyData;
    }

    /**
     * Get daily data for a week using real Order data
     */
    private function getWeeklyDailyData(Carbon $date): array
    {
        $startOfWeek = $date->copy()->startOfWeek();
        $dailyData = [];
        
        for ($day = 0; $day < 7; $day++) {
            $currentDate = $startOfWeek->copy()->addDays($day);
            
            $orders = Order::where('status', '!=', 'cancelled')
                ->whereDate('created_at', $currentDate)
                ->get();
                
            $revenue = $orders->sum('total_amount');
            $profit = $revenue * 0.30;
            $ordersCount = $orders->count();
            $customersCount = $orders->pluck('customer_name')->unique()->count();
            
            $dailyData[] = [
                'date' => $currentDate->format('Y-m-d'),
                'day_name' => $currentDate->format('l'),
                'revenue' => (float) $revenue,
                'profit' => (float) $profit,
                'orders' => $ordersCount,
                'customers' => $customersCount,
            ];
        }

        return $dailyData;
    }

    /**
     * Get daily data for a month using real Order data
     */
    private function getMonthlyDailyData(Carbon $date): array
    {
        $daysInMonth = $date->daysInMonth;
        $dailyData = [];
        
        for ($day = 1; $day <= $daysInMonth; $day++) {
            $currentDate = $date->copy()->day($day);
            
            $orders = Order::where('status', '!=', 'cancelled')
                ->whereDate('created_at', $currentDate)
                ->get();
                
            $revenue = $orders->sum('total_amount');
            $profit = $revenue * 0.30;
            $ordersCount = $orders->count();
            $customersCount = $orders->pluck('customer_name')->unique()->count();
            
            $dailyData[] = [
                'date' => $currentDate->format('Y-m-d'),
                'day' => $day,
                'revenue' => (float) $revenue,
                'profit' => (float) $profit,
                'orders' => $ordersCount,
                'customers' => $customersCount,
            ];
        }

        return $dailyData;
    }

    /**
     * Update analytics snapshots (called by scheduled job)
     */
    public function updateAnalyticsSnapshots(): void
    {
        $this->updateHourlySnapshots();
        $this->updateDailySnapshots();
    }

    /**
     * Update hourly snapshots for today
     */
    private function updateHourlySnapshots(): void
    {
        $today = Carbon::today();
        $currentHour = Carbon::now()->hour;

        for ($hour = 0; $hour <= $currentHour; $hour++) {
            $startTime = $today->copy()->hour($hour);
            $endTime = $startTime->copy()->addHour();

            $revenue = RevenueTracking::whereBetween('created_at', [$startTime, $endTime])->sum('amount');
            $cost = RevenueTracking::whereBetween('created_at', [$startTime, $endTime])->sum('cost');
            $profit = $revenue - $cost;
            $ordersCount = Order::whereBetween('created_at', [$startTime, $endTime])->count();
            $customersCount = Order::whereBetween('created_at', [$startTime, $endTime])
                                  ->distinct('user_id')->count('user_id');

            AnalyticsSnapshot::updateOrCreate(
                ['date' => $today->format('Y-m-d'), 'hour' => $hour],
                [
                    'revenue' => $revenue,
                    'profit' => $profit,
                    'orders_count' => $ordersCount,
                    'customers_count' => $customersCount,
                    'avg_order_value' => $ordersCount > 0 ? $revenue / $ordersCount : 0,
                    'conversion_rate' => 0 // Will be calculated separately
                ]
            );
        }
    }

    /**
     * Update daily snapshots
     */
    private function updateDailySnapshots(): void
    {
        $yesterday = Carbon::yesterday();

        $revenue = RevenueTracking::whereDate('created_at', $yesterday)->sum('amount');
        $cost = RevenueTracking::whereDate('created_at', $yesterday)->sum('cost');
        $profit = $revenue - $cost;
        $ordersCount = Order::whereDate('created_at', $yesterday)->count();
        $customersCount = Order::whereDate('created_at', $yesterday)
                              ->distinct('user_id')->count('user_id');

        AnalyticsSnapshot::updateOrCreate(
            ['date' => $yesterday->format('Y-m-d'), 'hour' => null],
            [
                'revenue' => $revenue,
                'profit' => $profit,
                'orders_count' => $ordersCount,
                'customers_count' => $customersCount,
                'avg_order_value' => $ordersCount > 0 ? $revenue / $ordersCount : 0,
                'conversion_rate' => 0 // Will be calculated separately
            ]
        );
    }

    /**
     * Get top selling products using real Product and Order data
     */
    public function getTopProducts(int $limit = 10): array
    {
        // Get all products with their sales data
        $products = Product::select('id', 'name', 'price', 'Amount')
            ->get()
            ->map(function ($product) {
                // Calculate estimated sales based on stock reduction
                // This is a simple estimation - you might want to implement proper sales tracking
                $estimatedSold = max(0, 100 - $product->Amount); // Assuming initial stock was 100
                $totalRevenue = $estimatedSold * $product->price;
                
                return [
                    'product_name' => $product->name,
                    'total_quantity' => $estimatedSold,
                    'total_revenue' => (float) $totalRevenue,
                    'current_stock' => $product->Amount,
                    'price' => (float) $product->price
                ];
            })
            ->sortByDesc('total_revenue')
            ->take($limit)
            ->values()
            ->toArray();

        return $products;
    }

    /**
     * Get customer analytics using real User and Order data
     */
    public function getCustomerAnalytics(): array
    {
        $totalCustomers = User::count();
        $newCustomersToday = User::whereDate('created_at', Carbon::today())->count();
        
        // Count unique customers who made orders today
        $customersWithOrdersToday = Order::whereDate('created_at', Carbon::today())
            ->distinct('customer_name')
            ->count('customer_name');
            
        // Total orders today
        $ordersToday = Order::whereDate('created_at', Carbon::today())->count();

        return [
            'total_customers' => $totalCustomers,
            'new_today' => $newCustomersToday,
            'active_today' => $customersWithOrdersToday,
            'orders_today' => $ordersToday,
            'new_percentage' => $totalCustomers > 0 ? ($newCustomersToday / $totalCustomers) * 100 : 0,
            'conversion_rate' => $customersWithOrdersToday > 0 ? ($ordersToday / $customersWithOrdersToday) * 100 : 0
        ];
    }

    /**
     * Get real-time order statistics
     */
    public function getOrderStats(): array
    {
        $today = Carbon::today();
        $yesterday = Carbon::yesterday();

        $todayOrders = Order::whereDate('created_at', $today)->count();
        $yesterdayOrders = Order::whereDate('created_at', $yesterday)->count();
        
        $percentageChange = $yesterdayOrders > 0 
            ? (($todayOrders - $yesterdayOrders) / $yesterdayOrders) * 100 
            : ($todayOrders > 0 ? 100 : 0);

        return [
            'current' => $todayOrders,
            'previous' => $yesterdayOrders,
            'percentage_change' => round($percentageChange, 2),
            'trend' => $percentageChange >= 0 ? 'up' : 'down',
            'total_orders' => Order::count(),
            'pending_orders' => Order::where('status', 'pending')->count(),
            'completed_orders' => Order::where('status', 'completed')->count()
        ];
    }

    /**
     * Get real-time product statistics
     */
    public function getProductStats(): array
    {
        $totalProducts = Product::count();
        $lowStockProducts = Product::where('Amount', '<', 10)->count();
        $outOfStockProducts = Product::where('Amount', 0)->count();
        
        return [
            'total_products' => $totalProducts,
            'low_stock' => $lowStockProducts,
            'out_of_stock' => $outOfStockProducts,
            'in_stock' => $totalProducts - $outOfStockProducts,
            'average_price' => (float) Product::avg('price')
        ];
    }
}